SELECT substr(replace(cast(fecha_desemb_dt as varchar), '-', ''), 1, 6) periodo_desembolso,
              substr(replace(cast(fecha_solicitud_dt as varchar), '-', ''), 1, 6) periodo_solicitud,
              numero_documento_val,
              max(CASE WHEN colocacion_neta > 0 then 1 else 0 end) flg_desembolso
        FROM (
            select ZZ.*, 
             ROW_NUMBER() OVER(PARTITION BY ZZ.credito_cd ORDER BY ZZ.fecha_solicitud_dt ASC, ZZ.periodo_val ASC, ZZ.fecinformacion_dt ASC) ORDEN
             FROM (
                    SELECT CC.*,
                           CASE WHEN CC.campana_val like '%Recurrente%' and GG.flg_recurrente = 0 AND flg_nuevos_ap = 1 then 1 else 0 end flg_rectificado_nuevos_ap,
                           CASE WHEN CC.campana_val like '%Recurrente%' and GG.flg_recurrente = 0 AND flg_nuevos_pre = 1 then 1 else 0 end flg_rectificado_nuevos_pre
                    FROM e_perm_aws.t_fact_vpc_colnetas CC
                    LEFT JOIN (
                          SELECT gestion , num_ruc_autocompletado, 
                                 MAX(CASE WHEN campania = 'RECURRENTE' then 1 else 0 end) flg_recurrente,
                                 MAX(CASE WHEN campania = 'NUEVOS PRE' OR lower(nom_lista) like '%dig_c%' then 1 else 0 end) flg_nuevos_pre,
                                 MAX(CASE WHEN campania in ('NUEVOS AP', 'ECOSISTEMAS') OR lower(nom_lista) like '%dig_b%' then 1 else 0 end) flg_nuevos_ap,
                                 SUM(FLG_GESTIONADO_ESTRICTO) gestion_total,
                                 SUM(flg_ce) ce_total,
                                 SUM(flg_acepta_campana) ac_total
                          FROM  e_perm_aws.ds_csfedtlv_pso
                          WHERE 1 = 1
                          AND (
                             flg_bpe = 1 
                             OR lower(nom_lista) like '%bpe%'
                          )
                          AND (
                              CAMPANIA IN ('NUEVOS AP', 'ECOSISTEMAS', 'NUEVOS PRE', 'RECURRENTE') 
                              OR lower(nom_lista) like '%dig_c%' 
                              OR lower(nom_lista) like '%dig_b%'
                          )
                          AND flg_gestionado_estricto = 1
                          GROUP BY gestion , num_ruc_autocompletado
                    ) GG
                    ON substr(replace(cast(CC.fecha_solicitud_dt as varchar), '-', ''), 1, 6) = GG.gestion
                    AND CC.numero_documento_val = GG.num_ruc_autocompletado
                    WHERE 1 = 1
                    AND CC.colocacion_neta > 0
                    AND CC.tipo_colocacion NOT LIKE '%Cima%'
                    AND (
                        CC.tipo_colocacion in ('1. Campaña Nuevos', '103.Ecosistemas', '5. Campaña Express', '4. EN – Express') OR
                        lower(CC.campana_val) like '%nuevos pre%' OR lower(CC.campana_val) like '%nvo pre%' OR lower(CC.campana_val) like '%nvopre%' OR
                        lower(CC.campana_val) like '%nuevos ap%' OR lower(CC.campana_val) like '%nvo ap%' OR lower(CC.campana_val) like '%nvoap%' OR
                        (lower(CC.campana_val) like '%recurrente%' and GG.flg_recurrente = 0 AND GG.flg_nuevos_ap = 1) OR
                        (lower(CC.campana_val) like '%recurrente%' and GG.flg_recurrente = 0 AND GG.flg_nuevos_pre = 1) OR
                        lower(CC.campana_val) like '%nuevo pre%' OR
                        lower(CC.campana_val) like 'nuevo ap%' OR
                        CC.tipo_colocacion LIKE '%1.%' OR
                        CC.tipo_colocacion LIKE '%4.%' OR
                        CC.tipo_colocacion LIKE '%5.%' OR
                        CC.tipo_colocacion LIKE '%103.%'
                    )
                    AND CC.fecinformacion_dt in (
                        select fecinformacion_dt from (
                            SELECT periodo_val,
                            max(fecinformacion_dt) fecinformacion_dt
                            FROM e_perm_aws.t_fact_vpc_colnetas
                            GROUP BY periodo_val
                        )
                    )
            ) ZZ
        ) AA
        WHERE AA.ORDEN = 1
        AND (
            (lower(campana_val) like '%recurrente%' AND flg_rectificado_nuevos_pre = 1) OR
            (lower(campana_val) not like '%recurrente%' AND (tipo_colocacion in ('1. Campaña Nuevos') 
                                                            OR tipo_colocacion LIKE '%1.%'
                                                            OR lower(campana_val) like '%nuevo pre%' 
                                                            OR lower(campana_val) like '%nuevos pre%' 
                                                            OR lower(campana_val) like '%nvo pre%' 
                                                            OR lower(campana_val) like '%nvos pre%' 
                                                            OR lower(campana_val) like '%nvospre%' 
                                                            OR lower(campana_val) like '%nvopre%')
            ) OR
            (lower(campana_val) like '%recurrente%' AND flg_rectificado_nuevos_ap = 1) OR
            (lower(campana_val) not like '%recurrente%' AND (tipo_colocacion in ( '103.Ecosistemas', '5. Campaña Express', '4. EN – Express') 
                                                            OR tipo_colocacion LIKE '%103.%'
                                                            OR tipo_colocacion LIKE '%4.%'
                                                            OR tipo_colocacion LIKE '%5.%'
                                                            OR lower(campana_val) like '%nuevo ap%' 
                                                            OR lower(campana_val) like '%nuevos ap%' 
                                                            OR lower(campana_val) like '%nvo ap%' 
                                                            OR lower(campana_val) like '%nvos ap%' 
                                                            OR lower(campana_val) like '%nvosap%' 
                                                            OR lower(campana_val) like '%nvoap%')
            )
        )
        GROUP BY substr(replace(cast(fecha_desemb_dt as varchar), '-', ''), 1, 6),
                 substr(replace(cast(fecha_solicitud_dt as varchar), '-', ''), 1, 6),
                 numero_documento_val

Add a code snippet to your website: www.paste.org