aq

SELECT "Codigo_Producto",
            "Poliza",
            "Codigo_Cobertura",
            CASE
                WHEN "Codigo_Producto" IN (904,910) THEN 'Principal'
                ELSE "Tipo_Cobertura"
            END AS "Tipo_Cobertura",
            "Rol",
            "Rut",
            "Tipo_Calculo",
            "Reserva",
            "Canal_Comision",
            "Frecuencia_Pago_Primas",
            "Fecha_Inicio_Vigencia",
            CASE
                WHEN "Codigo_Producto" = 336 THEN "Fecha_Nacimiento" + 680000
                WHEN "Codigo_Producto" IN (380,360)  AND "Fecha_Fin_Vigencia" <= "Fecha_Inicio_Vigencia"+50000  THEN "Fecha_Inicio_Vigencia"+50000
                WHEN "Codigo_Producto" IN (910,904)  THEN "Fecha_Nacimiento" + 700000
                WHEN "Codigo_Producto" = 294 AND MONTHS_BETWEEN(TO_DATE( "Fecha_Nacimiento" + 660000,'YYYY-MM-DD'),TO_DATE("Fecha_Inicio_Vigencia",'YYYY-MM-DD')) >= 60 THEN "Fecha_Nacimiento" + 650000
                WHEN "Codigo_Producto" = 294 AND MONTHS_BETWEEN(TO_DATE( "Fecha_Nacimiento" + 660000,'YYYY-MM-DD'),TO_DATE("Fecha_Inicio_Vigencia", 'YYYY-MM-DD')) < 60 THEN "Fecha_Inicio_Vigencia" + 50000
                ELSE TO_NUMBER("Fecha_Fin_Vigencia")
            END AS  "Fecha_Fin_Vigencia",
            CASE
                WHEN "Codigo_Producto" = 336 THEN    MONTHS_BETWEEN(TO_DATE("Fecha_Nacimiento"+690000,'YYYY-MM-DD'), TO_DATE( "Fecha_Fin_Vigencia",'YYYY-MM-DD'))
                WHEN "Codigo_Producto" IN (380,360)  AND "Periodo_Pago_Primas" <60 THEN 60
                WHEN "Codigo_Producto" IN (910,904) THEN  MONTHS_BETWEEN(TO_DATE("Fecha_Nacimiento"+710000,'YYYY-MM-DD'), TO_DATE( "Fecha_Fin_Vigencia",'YYYY-MM-DD'))
                ELSE "Periodo_Pago_Primas"
            END AS "Periodo_Pago_Primas",
            "Recargo_Multiplicativo",
            "Recargo_Aditivo",
            "Capital_Asegurado",
            "Capital_Cedido",
            "Fecha_Nacimiento",
            "Genero",
            "Fumador",
            "Tasa_Reserva",
            CASE
                WHEN "Codigo_Producto" IN (910, 904) THEN "Prima_Basica_Anual"
                ELSE "Prima_Anual"
            END AS "Prima_Anual",
            "Plan",
            "Modalidad_Inversion",
            "Tasa_Minima_Garantizada",
            CASE
                WHEN  "Codigo_Producto" = 380 THEN 0.1
                ELSE "Prima_Basica_Anual"
            END AS "Prima_Basica_Anual",
            "Prima_Segura_Anual",
            "Prima_Proyectada",
            "Recaudacion_Acumulada",
            "Rescates_Acumulados",
            "NYEARMONTH",
            "Tipo_Moneda",
            "NACQUIS_UNDER30",
            "Valor_Fondo",
            "Fondo_57BIS",
            "Monto_Prestamo",
            "Tasa_Interes_Prestamo",
            "Bono",
            "Renta",
            "Numero_Rentas",
            "Tasa_Interes_Inversion_Gold",
            "Segmento_Resultados_Adicional",
            "Archivo_Parametros",
            "Aplica_Recargo",
            "Porc_Recargo",
            "Factor_RA",
            "Factor_RM",
            "Antiguedad_Agente",
            "NGRACE_PERIOD",
            "NAGENCY",
            "SSELLCHANNEL_AGENCIES"
    FROM (
        SELECT
                POL.NPRODUCT AS "Codigo_Producto",
                CV.NPOLICY AS "Poliza",
                CV.NCOVER AS "Codigo_Cobertura",
                CASE
                    WHEN cv.NCOVER IN (6,800,900,2) OR pol.NPRODUCT IN (904, 910) THEN 'Principal' ELSE 'Adicional'
                END AS "Tipo_Cobertura",
                rv.NROLE AS "Rol",
                TO_NUMBER(rv.SCLIENT) AS "Rut",
                CASE
                    WHEN pol.NPRODUCT IN (294, 360, 380, 600, 650) THEN 'VF'
                    WHEN pol.NPRODUCT IN (336, 904, 910) THEN 'RC'
                    WHEN pol.NPRODUCT IN (290,291,801) AND cv.NCOVER = 6 THEN 'M'ELSE 'RC'
                END AS "Tipo_Calculo",
                1 AS "Reserva",
                CASE
                    WHEN pol.SSELLCHANNEL = 'AGENCIAL' THEN 'Agencial'
                    WHEN pol.SSELLCHANNEL = 'AGENCIAL NVO' THEN'Agencial_Nuevo'
                    WHEN pol.SSELLCHANNEL = 'CORREDORES' THEN 'Corredor'
                    WHEN pol.SSELLCHANNEL = 'DIRECTO' THEN 'Directo'
                    WHEN pol.SSELLCHANNEL = 'MANTENCION' THEN 'Mantencion'
                    WHEN pol.SSELLCHANNEL = 'OTRO' THEN 'Directo'
                    WHEN pol.SSELLCHANNEL = 'LIBRE LYON' THEN 'Agentes_Libre_Lyon'
                END AS "Canal_Comision",
                pol.NPAYFREQ AS "Frecuencia_Pago_Primas",
                TO_NUMBER(EXTRACT(YEAR FROM POL.DDATE_ORIGI))||
                CASE
                    WHEN TO_NUMBER(EXTRACT(MONTH FROM POL.DDATE_ORIGI)) <10 THEN 0||TO_NUMBER(EXTRACT(MONTH FROM POL.DDATE_ORIGI))
                    ELSE TO_CHAR(EXTRACT(MONTH FROM POL.DDATE_ORIGI))
                END||
                CASE
                    WHEN TO_NUMBER(EXTRACT(DAY FROM POL.DDATE_ORIGI)) <10 THEN 0||TO_NUMBER(EXTRACT(DAY FROM POL.DDATE_ORIGI))
                    ELSE TO_CHAR(EXTRACT(DAY FROM POL.DDATE_ORIGI))
                END AS "Fecha_Inicio_Vigencia",
                TO_NUMBER(EXTRACT(YEAR FROM POL.DEXPIRDAT))||
                CASE
                    WHEN TO_NUMBER(EXTRACT(MONTH FROM POL.DEXPIRDAT)) <10 THEN 0||TO_NUMBER(EXTRACT(MONTH FROM POL.DEXPIRDAT))
                    ELSE TO_CHAR(EXTRACT(MONTH FROM POL.DEXPIRDAT))
                END||
                CASE
                    WHEN TO_NUMBER(EXTRACT(DAY FROM POL.DEXPIRDAT)) <10 THEN 0||TO_NUMBER(EXTRACT(DAY FROM POL.DEXPIRDAT))
                    ELSE TO_CHAR(EXTRACT(DAY FROM POL.DEXPIRDAT))
                END AS "Fecha_Fin_Vigencia",
                (TO_NUMBER(EXTRACT(YEAR FROM pol.DEXPIRDAT)) - TO_NUMBER(EXTRACT(YEAR FROM pol.DSTARTDATE)))*12
                +(TO_NUMBER(EXTRACT(MONTH FROM pol.DEXPIRDAT))
                - TO_NUMBER(EXTRACT(MONTH FROM pol.DSTARTDATE))) AS "Periodo_Pago_Primas",
                CASE
                    WHEN CV.NRECAMOUNT_M IS NULL THEN 0 ELSE CV.NRECAMOUNT_M
                END AS "Recargo_Multiplicativo",
                CASE
                    WHEN CV.NRECAMOUNT_A IS NULL THEN 0 ELSE CV.NRECAMOUNT_A
                END AS "Recargo_Aditivo",
                CV.NCAPITAL_INSU AS "Capital_Asegurado",
                CV.NCAPITAL_REIN AS "Capital_Cedido",
                TO_NUMBER(rv.NDB_YYYY) ||
                CASE
                    WHEN rv.NDB_MM<10 THEN 0 || rv.NDB_MM ELSE TO_CHAR(rv.NDB_MM)
                END||
                CASE
                    WHEN rv.NDB_DD<10 THEN 0 || rv.NDB_DD ELSE TO_CHAR(rv.NDB_DD)
                END AS "Fecha_Nacimiento",
                CASE
                    WHEN rv.SSEXCLIEN = '1' THEN 'F' ELSE 'M'
                END AS "Genero",
                CASE
                    WHEN rv.SSMOKING = '1' THEN 'F' ELSE 'NF'
                END AS "Fumador",
                CASE
                    WHEN pol.DSTARTDATE <= TO_DATE('31/12/80', 'DD/MM/YYYY') THEN 5 ELSE 3
                END AS "Tasa_Reserva",
                CASE
                    WHEN pol.NPRODUCT NOT IN (904,910, 336) THEN CV.NPREMIUM_NET + CV.NPREMIUM_EXT -CVC.NDESCAMOUNT
                    ELSE cvc.PRIMA_BASICA_COV
                END AS "Prima_Anual",
                CASE
                    WHEN pol.NOPTION = 1 THEN '187'
                    WHEN pol.NOPTION = 2 THEN '188'
                    WHEN pol.NOPTION = 0 THEN '0'
                END AS "Plan",
                CASE
                WHEN pol.NTYPEINVEST IS NULL THEN 0 ELSE pol.NTYPEINVEST
                END AS "Modalidad_Inversion",
                CASE
                    WHEN pol.NWARMINIT IS NULL THEN 0 ELSE NWARMINIT
                END AS "Tasa_Minima_Garantizada",
                cvc.PRIMA_BASICA_COV  AS "Prima_Basica_Anual",
                CVC.PRIMA_SEGURA  AS "Prima_Segura_Anual",
                CASE
                    WHEN pol.NAMOUNTDEP IS NULL THEN 0
                    WHEN cv.NCOVER NOT IN (6,800,900,2) AND pol.NPRODUCT NOT IN (904, 910) THEN 0 -- 0 A LOS ADICIONALES
                    ELSE pol.NAMOUNTDEP
                END AS "Prima_Proyectada",
                CASE
                    WHEN  cv.NCOVER NOT IN (6,800,900,2) AND pol.NPRODUCT NOT IN (904, 910) THEN 0
                    ELSE pol.REC_ACUMULADA
                END AS "Recaudacion_Acumulada",
                CASE
                    WHEN cv.NCOVER NOT IN (6,800,900,2) AND pol.NPRODUCT NOT IN (904, 910) THEN 0 -- 0 A LOS ADICIONALES
                    ELSE pol.NAMOUNT_RESC
                END AS "Rescates_Acumulados",
                CV.NYEARMONTH,
                CASE
                    WHEN CV.NACQUIS_OVER30 IS NULL THEN 0 ELSE CV.NACQUIS_OVER30
                END AS NACQUIS_UNDER30,
                CASE
                    WHEN pol.VALOR_POLIZA_P IS NULL OR cv.NCOVER NOT IN (6,800,900,2) AND pol.NPRODUCT NOT IN (904, 910) THEN 0 -- asignar 0 a los adicionales
                    WHEN pol.NPRODUCT IN (294,297) THEN 0
                    WHEN pol.NPRODUCT = 360 AND pol.NPAYFREQ = 6 THEN 0
                    WHEN pol.NPRODUCT = 360 THEN pol.VALOR_POLIZA_P*2
                    ELSE pol.VALOR_POLIZA_P
                END AS "Valor_Fondo",
                CASE
                    WHEN pol.VALOR_POLIZA_A IS NULL OR cv.NCOVER NOT IN (6,800,900,2) AND pol.NPRODUCT NOT IN (904, 910) THEN 0 -- 0 A LOS ADICIONALES
                    ELSE pol.VALOR_POLIZA_A  --
                END AS "Fondo_57BIS",
                CASE
                    WHEN cv.NCOVER NOT IN (6,800,900,2) AND pol.NPRODUCT NOT IN (904, 910) THEN 0  -- 0 A LOS ADICIONALES
                    WHEN pol.NPRODUCT = 248 OR pol.NPRODUCT = 48 OR pol.NPRODUCT = 49 THEN cv.NCAPITAL_INSU ELSE pol.NAMOUNT_LOAN
                END AS "Monto_Prestamo",
                CASE
                    WHEN cv.NCOVER NOT IN (6,800,900,2) AND pol.NPRODUCT NOT IN (904, 910) THEN 0 -- 0 A LOS ADICIONALES
                    ELSE pol.NINT_CREDIT
                END AS "Tasa_Interes_Prestamo",
                0 AS "Bono",
                0 AS "Renta",
                0 AS "Numero_Rentas",
                CASE
                    WHEN pol.NWARMINIT IS NULL THEN 0 ELSE pol.NWARMINIT
                END AS "Tasa_Interes_Inversion_Gold",
                'Segmento' AS "Segmento_Resultados_Adicional",
                'Parametros' AS "Archivo_Parametros",
                pol.NCURRENCY AS "Tipo_Moneda",
                0 AS "Aplica_Recargo",
                0 AS "Porc_Recargo",
                cvc.FACTOR_RECARGO_ADITIVO AS "Factor_RA",
                cvc.FACTOR_RECARGO_MULTIPLICATIVO AS "Factor_RM",
                pol.NMONTHS_INTERMED AS "Antiguedad_Agente",
                CASE
                    WHEN pol.NGRACE_PERIOD IS NULL THEN 0 ELSE pol.NGRACE_PERIOD
                END AS NGRACE_PERIOD,
                0 AS "NAGENCY",
                pol.SSELLCHANNEL_AGENCIES
        FROM INSUDB.COV_VIL900 cv
        INNER JOIN INSUDB.COV_VIL900_CENSO cvc
            ON  cv.SCERTYPE =cvc.SCERTYPE
            AND cv.NBRANCH =cvc.NBRANCH
            AND cv.NPRODUCT =cvc.NPRODUCT
            AND cv.NPOLICY =cvc.NPOLICY
            AND cv.NCERTIF =cvc.NCERTIF
            AND cv.NMODULEC =cvc.NMODULEC
            AND cv.NCOVER =cvc.NCOVER
            AND cv.SCLIENT =cvc.SCLIENT
            AND cv.NYEARMONTH =cvc.NYEARMONTH
        INNER JOIN INSUDB.ROL_VIL900 rv
            ON  cv.SCERTYPE =rv.SCERTYPE
            AND cv.NBRANCH =rv.NBRANCH
            AND cv.NPRODUCT =rv.NPRODUCT
            AND cv.NPOLICY =rv.NPOLICY
            AND cv.NCERTIF =rv.NCERTIF
            AND cv.SCLIENT =rv.SCLIENT
            AND cv.NYEARMONTH =rv.NYEARMONTH
            AND cv.NROLE =rv.NROLE
        INNER JOIN (SELECT
                            pl.NPOLICY,
                            pl.SCERTYPE,
                            pl.NCERTIF,
                            pl.NYEARMONTH,
                            pl.NPRODUCT,
                            pl.NBRANCH,
                            pl.NPAYFREQ,
                            pl.DSTARTDATE,
                            pl.NOPTION,
                            pl.NWARMINIT,
                            pl.NAMOUNTDEP,
                            plc.REC_ACUMULADA,
                            pl.NAMOUNT_RESC,
                            pl.VALOR_POLIZA_P,
                            pl.VALOR_POLIZA_A,
                            pl.NAMOUNT_LOAN,
                            pl.NINT_CREDIT,
                            pl.NCURRENCY,
                            pl.NMONTHS_INTERMED,
                            pl.NGRACE_PERIOD,
                            plc.SSELLCHANNEL_AGENCIES,
                            pl.NTYPEINVEST,
                            pl.SSELLCHANNEL,
                            pl.DDATE_ORIGI,
                            pl.DEXPIRDAT
                            FROM
                                INSUDB.POL_VIL900 PL
                                INNER JOIN INSUDB.POL_VIL900_CENSO plc
                                ON pl.NPOLICY = plc.NPOLICY
                                AND pl.SCERTYPE = plc.SCERTYPE
                                AND pl.NBRANCH = plc.NBRANCH
                                AND pl.NPRODUCT = plc.NPRODUCT
                                AND pl.NCERTIF = plc.NCERTIF
                                AND pl.DRESPERDATE =plc.DRESPERDATE
                                AND pl.NYEARMONTH =plc.NYEARMONTH
                            WHERE
                                pl.SSITUATION_H = 'V'
                                AND (pl.DNULLDATE IS NULL OR pl.DNULLDATE >= TO_DATE('01/05/2021','DD/MM/YYYY'))
                                AND pl.NBRANCH IN (1, 6)
                                AND pl.SCERTYPE =2
                                AND pl.NPRODUCT <> 750
                                AND pl.NYEARMONTH = 202105
                                AND pl.NPRODUCT IN (904,290,291,336,360,380,600,650,801, 294, 910)
                                AND EXTRACT(YEAR FROM pl.DDATE_ORIGI) = 2021
                                AND EXTRACT(MONTH FROM pl.DDATE_ORIGI) = 5) pol
            ON  cv.SCERTYPE = pol.SCERTYPE
            AND cv.NBRANCH = pol.NBRANCH
            AND cv.NPRODUCT = pol.NPRODUCT
            AND cv.NPOLICY = pol.NPOLICY
            AND cv.NCERTIF = pol.NCERTIF
            AND cv.NYEARMONTH = pol.NYEARMONTH) inp