SELECT
nom,
rut,
COALESCE(SUM(ingreso_man),0) AS ingreso_man,
COALESCE(SUM(ingreso_com),0) AS ingreso_com,
COALESCE(SUM(comision_man),0) AS comision_man,
COALESCE(SUM(comision_com),0) AS comision_com,
COALESCE(AVG((SELECT SUM(cp.nmontocierre) AS MONTCIERREPATMAN FROM cierre_patrimonios cp WHERE IDGRUPO IN (SELECT DISTINCT IDGRUPO FROM ASIGNACIONES_HISTORICAS ah
WHERE ah.BPCLIENTE = ${Cliente} AND ah.BPMANT = (SELECT DISTINCT ah.bpmant FROM asignaciones_historicas ah,usuarios u WHERE ah.BPMANT = u.bp AND u.usuario = ${env::username}) AND ah.periodo=TO_CHAR(CURRENT_DATE, 'yyyyMM') )AND cp.periodo =TO_CHAR(CURRENT_DATE, 'yyyyMM'))),0) AS PAT_MAN, -- APLICAR VARIABLE DE EJECUTIVO AL BPMAN Y BPCOM
COALESCE(AVG((SELECT SUM(cp.nmontocierre) AS MONTCIERREPATCOM FROM cierre_patrimonios cp WHERE IDGRUPO IN (SELECT DISTINCT IDGRUPO FROM ASIGNACIONES_HISTORICAS ah
WHERE ah.BPCLIENTE = ${Cliente} AND ah.BPCOM = (SELECT DISTINCT ah.bpmant FROM asignaciones_historicas ah,usuarios u WHERE ah.BPMANT = u.bp AND u.usuario = ${env::username}) AND ah.periodo=TO_CHAR(CURRENT_DATE, 'yyyyMM') )AND cp.periodo =TO_CHAR(CURRENT_DATE, 'yyyyMM'))),0) AS PAT_COM, -- APLICAR VARIABLE DE EJECUTIVO AL BPMAN Y BPCOM
COALESCE(AVG((SELECT SUM(mh.montot) FROM movimientos_historicos mh WHERE mh.idgrupo IN( SELECT DISTINCT IDGRUPO FROM ASIGNACIONES_HISTORICAS ah
WHERE ah.BPCLIENTE = ${Cliente} AND ah.BPMANT = (SELECT DISTINCT ah.bpmant FROM asignaciones_historicas ah,usuarios u WHERE ah.BPMANT = u.bp AND u.usuario = ${env::username}) AND ah.periodo=TO_CHAR(CURRENT_DATE, 'yyyyMM') )AND mh.periodo =TO_CHAR(CURRENT_DATE, 'yyyyMM'))),0) AS APN_MAN, -- APLICAR VARIABLE DE EJECUTIVO AL BPMAN Y BPCOM
COALESCE(AVG((SELECT SUM(mh.montot) FROM movimientos_historicos mh WHERE mh.idgrupo IN( SELECT DISTINCT IDGRUPO FROM ASIGNACIONES_HISTORICAS ah
WHERE ah.BPCLIENTE = ${Cliente} AND ah.BPCOM = (SELECT DISTINCT ah.bpmant FROM asignaciones_historicas ah,usuarios u WHERE ah.BPMANT = u.bp AND u.usuario = ${env::username}) AND ah.periodo=TO_CHAR(CURRENT_DATE, 'yyyyMM') )AND mh.periodo =TO_CHAR(CURRENT_DATE, 'yyyyMM'))),0) AS APN_COM -- APLICAR VARIABLE DE EJECUTIVO AL BPMAN Y BPCOM
FROM (
SELECT
nom,
rut,
relacion,
CASE WHEN relacion = 'Mantencion' THEN ingreso ELSE 0 END AS ingreso_man,
CASE WHEN relacion = 'Comercial' THEN ingreso ELSE 0 END AS ingreso_com,
CASE WHEN relacion = 'Mantencion' THEN comision ELSE 0 END AS comision_man,
CASE WHEN relacion = 'Comercial' THEN comision ELSE 0 END AS comision_com,
ingreso AS ingreso,
comision AS comision
FROM BI_PENTAHO.ingcom_diaria
WHERE bpejecutivo = (SELECT DISTINCT ah.bpmant FROM asignaciones_historicas ah,usuarios u WHERE ah.BPMANT = u.bp AND u.usuario = ${env::username})
AND periodo = TO_CHAR(CURRENT_DATE, 'yyyyMM')
AND bp_id = ${Cliente}
) A GROUP BY nom,rut