Psst.. new poll here.
[email protected] webmail now available. Want one? Go here.
Cannot use outlook/hotmail/live here to register as they blocking our mail servers. #microsoftdeez
Obey the Epel!
Paste
Pasted as Plain Text by registered user vvillacorta ( 3 years ago )
SELECT A.FECCARGA, TRIM(A.NUMRUC) NUMRUC,
CASE WHEN SUBSTR(NUMRUC,1,2) = '10' AND (TRIM(A.CODDOCRELE) LIKE '' OR A.CODDOCRELE IS NULL) THEN TRIM(SUBSTR(NUMRUC,3,8))
ELSE TRIM(A.CODDOCRELE) END CODDOCRELE,
MAX(CASE WHEN S.ORDER_CODSBS = 1 THEN S.CODSBS ELSE NULL END ) CODSBS_EMPRESA_1,
MAX(CASE WHEN S.ORDER_CODSBS = 2 THEN S.CODSBS ELSE NULL END ) CODSBS_EMPRESA_2,
MAX(CASE WHEN S.ORDER_CODSBS = 3 THEN S.CODSBS ELSE NULL END ) CODSBS_EMPRESA_3,
MAX(CASE WHEN S.ORDER_CODSBS = 4 THEN S.CODSBS ELSE NULL END ) CODSBS_EMPRESA_4,
MAX(CASE WHEN S.ORDER_CODSBS = 5 THEN S.CODSBS ELSE NULL END ) CODSBS_EMPRESA_5,
MAX(CASE WHEN S.ORDER_CODSBS = 6 THEN S.CODSBS ELSE NULL END ) CODSBS_EMPRESA_6,
MAX(CASE WHEN R.RRLL_CODSBS = 1 THEN R.CODSBS ELSE NULL END ) CODSBS_RRLL_1,
MAX(CASE WHEN R.RRLL_CODSBS = 2 THEN R.CODSBS ELSE NULL END ) CODSBS_RRLL_2,
MAX(CASE WHEN R.RRLL_CODSBS = 3 THEN R.CODSBS ELSE NULL END ) CODSBS_RRLL_3,
MAX(CASE WHEN R.RRLL_CODSBS = 4 THEN R.CODSBS ELSE NULL END ) CODSBS_RRLL_4,
MAX(CASE WHEN R.RRLL_CODSBS = 5 THEN R.CODSBS ELSE NULL END ) CODSBS_RRLL_5,
MAX(CASE WHEN R.RRLL_CODSBS = 6 THEN R.CODSBS ELSE NULL END ) CODSBS_RRLL_6,
MAX(S.CODUNICOCLI) COD_UNICO,
MAX(RAZONSOCIAL) RAZONSOCIAL,
MAX(TIPCONTRIBUYENTE) TIPCONTRIBUYENTE,
MAX(TIPPERSONA) TIPPERSONA,
MAX(CIIU) CIIU,
MAX(UBIGEO) UBIGEO,
CASE WHEN MAX(NUMTRABAJADORES) LIKE '' THEN 0
WHEN MAX(NUMTRABAJADORES) LIKE 'NE' THEN 0
ELSE CAST(MAX(NUMTRABAJADORES) AS INTEGER)
END NUMTRABAJADORES,
CASE WHEN MAX(RANGOVENTAS) LIKE '' OR MAX(RANGOVENTAS) IS NULL THEN 0 ELSE MAX(RANGOVENTAS) END RANGOVENTAS,
MAX(CONDICIONDOMICILIO) CONDICIONDOMICILIO,
MAX(ESTADOCONTRIBUYENTE) ESTADOCONTRIBUYENTE,
MAX(FECALTA) FECALTA,
CASE WHEN MAX(FECALTA) IS NULL THEN NULL
ELSE MONTHS_BETWEEN(CAST(FECCARGA AS DATE FORMAT 'YYYYMM'),CAST(MAX(FECALTA) AS DATE FORMAT 'YYYYMM')) / 12 END TIEMPO_ALTA,
MAX(FECBAJA) FECBAJA,
CASE WHEN MAX(FECBAJA) IS NULL THEN 0
ELSE MONTHS_BETWEEN(CAST(FECCARGA AS DATE FORMAT 'YYYYMM'),CAST(MAX(FECBAJA) AS DATE FORMAT 'YYYYMM')) / 12 END TIEMPO_BAJA,
MAX(CONTABILIDAD) CONTABILIDAD,
MAX(FACTURACION) FACTURACION,
MAX(COMERCIOEXTERIOR) COMERCIOEXTERIOR,
MAX(ESTADO) ESTADO,
MAX(CASE WHEN TIENEDIR = 'SI' THEN 1 ELSE 0 END) FLG_TIENEDIR,
MAX(CASE WHEN TIENETELEF = 'SI' THEN 1 ELSE 0 END) FLG_TIENETELEF,
MAX(CASE WHEN SUBSTR(NUMRUC,1,2) = '10' THEN 1 ELSE CAST(TIPDOCRELE AS INTEGER) END) TIPDOCRELE,
MAX(ESTADORELE) ESTADORELE,
MAX(CASE WHEN SUBSTR(NUMRUC,1,2) = '10' AND (TRIM(A.CODDOCRELE) LIKE '' OR A.CODDOCRELE IS NULL) THEN FECALTA ELSE FECVIGENCIARELE END) FECVIGENCIARELE,
MAX(CASE WHEN SUBSTR(NUMRUC,1,2) = '10' AND (TRIM(A.CODDOCRELE) LIKE '' OR A.CODDOCRELE IS NULL) THEN (
CASE WHEN FECALTA IS NULL THEN NULL
ELSE MONTHS_BETWEEN(CAST(FECCARGA AS DATE FORMAT 'YYYYMM'),CAST(FECALTA AS DATE FORMAT 'YYYYMM')) / 12
END
)
WHEN FECVIGENCIARELE IS NULL THEN NULL
ELSE MONTHS_BETWEEN(CAST(FECCARGA AS DATE FORMAT 'YYYYMM'),CAST(FECVIGENCIARELE AS DATE FORMAT 'YYYYMM')) / 12
END) TIEMPO_VIGENCIARELE,
MAX(B.EST_CIVIL) EST_CIVIL,
MAX(B.FEC_NACIMIENTO) FECHA_NACIMIENTO,
MAX(CASE WHEN B.FEC_NACIMIENTO IS NULL THEN NULL
ELSE MONTHS_BETWEEN(CAST(FECCARGA AS DATE FORMAT 'YYYYMM'),CAST(B.FEC_NACIMIENTO AS DATE FORMAT 'YYYYMM')) / 12
END) EDAD_RRLL,
--MAX(( CURRENT_DATE - B.FEC_NACIMIENTO)/360) EDAD_RRLL,
MAX(CASE WHEN EST_CIVIL = 'D' THEN 1 ELSE 0 END) EST_CIVIL_DIVORCIADO,
MAX(CASE WHEN EST_CIVIL = 'S' THEN 1 ELSE 0 END) EST_CIVIL_SOLTERO,
MAX(CASE WHEN EST_CIVIL ='C' THEN 1 ELSE 0 END) EST_CIVIL_CASADO,
MAX(CASE WHEN EST_CIVIL = 'V' THEN 1 ELSE 0 END) EST_CIVIL_VIUDO,
MAX(GENERO) GENERO,
MAX(CASE WHEN GENERO = 'F' THEN 1 ELSE 0 END) EST_CIVIL_FEMENINO,
MAX(CASE WHEN GENERO = 'M' THEN 1 ELSE 0 END) EST_CIVIL_MASCULINO,
MAX(CASE WHEN DON_ORGANOS = 'SI' THEN 1 ELSE 0 END) DON_ORGANOS,
MAX(CASE WHEN DEP_UBI_DOMICILIO IN ( 'LIMA' , 'CALLAO' ) THEN 'LIMA y CALLAO'
WHEN DEP_UBI_DOMICILIO IN ( 'AMAZONAS', 'ANCASH' ,'APURIMAC' , 'AREQUIPA', 'AYACUCHO' , 'CAJAMARCA', 'CUSCO', 'HUANCAVELICA' , 'HUANUCO' , 'ICA','JUNIN',
'LA LIBERTAD' , 'LAMBAYEQUE', 'LORETO' , 'MADRE DE DIOS', 'MOQUEGUA', 'PASCO' , 'PIURA', 'PUNO', 'SAN MARTIN', 'TACNA', 'TUMBES' , 'UCAYALI') THEN 'PROVINCIA'
ELSE 'EXTRANJERO' END) LUGAR_RESIDENCIA,
MAX(CASE WHEN DEP_UBI_DOMICILIO IN ( 'LIMA' , 'CALLAO' ) THEN 1 ELSE 0 END) RESIDENCIA_LIMA_CALLAO,
MAX(CASE WHEN DEP_UBI_DOMICILIO IN ( 'AMAZONAS', 'ANCASH' ,'APURIMAC' , 'AREQUIPA', 'AYACUCHO' , 'CAJAMARCA', 'CUSCO', 'HUANCAVELICA' , 'HUANUCO' , 'ICA','JUNIN',
'LA LIBERTAD' , 'LAMBAYEQUE', 'LORETO' , 'MADRE DE DIOS', 'MOQUEGUA', 'PASCO' , 'PIURA', 'PUNO', 'SAN MARTIN', 'TACNA', 'TUMBES' , 'UCAYALI') THEN 1 ELSE 0 END) RESIDENCIA_PROVICNCIA,
MAX(CASE WHEN DEP_UBI_DOMICILIO NOT IN ('LIMA' , 'CALLAO', 'AMAZONAS', 'ANCASH' ,'APURIMAC' , 'AREQUIPA', 'AYACUCHO' , 'CAJAMARCA', 'CUSCO', 'HUANCAVELICA' , 'HUANUCO' , 'ICA','JUNIN',
'LA LIBERTAD' , 'LAMBAYEQUE', 'LORETO' , 'MADRE DE DIOS', 'MOQUEGUA', 'PASCO' , 'PIURA', 'PUNO', 'SAN MARTIN', 'TACNA', 'TUMBES' , 'UCAYALI') THEN 1 ELSE 0 END) RESIDENCIA_EXTRANJERA,
COALESCE(MAX(V.FLG_GESTIONABLE),0) FLG_GESTIONABLE_RETAIL,
COALESCE(MAX(V.FLG_PRINCIPALIDAD),0) FLG_PRINCIPALIDAD_RETAIL,
COALESCE(MAX(V.FLG_FOCO),0) FLG_FOCO_RETAIL,
COALESCE(MAX(V.FLG_FALLECIDO),0) FLG_FALLECIDO,
COALESCE(MAX(V.FLG_CASTIGADO),0) FLG_CASTIGADO,
COALESCE(MAX(V.FLG_COLABORADOR),0) FLG_COLABORADOR,
COALESCE(MAX(V.PRODUCTOS),0) PRODUCTOS_RETAIL,
COALESCE(MAX(V.TENENCIA),0) TENENCIA_RETAIL,
COALESCE(MAX(V.SEGMENTO),'SV') SEGMENTO_RETAIL,
COALESCE(MAX(V.FLG_CONSUMO_TC),0) FLG_CONSUMO_TC,
COALESCE(MAX(V.MESES_BLOQUEO_TC),0) MESES_BLOQUEO_TC,
COALESCE(MAX(V.FLG_PA),0) FLG_PA,
COALESCE(MAX(V.FLG_CLIENTE_VEHICULAR),0) FLG_CLIENTE_VEHICULAR,
COALESCE(MAX(V.FLG_CLIENTE_HIPOTECARIO),0) FLG_CLIENTE_HIPOTECARIO,
COALESCE(MAX(V.FLG_CLIENTE_CONVENIO),0) FLG_CLIENTE_CONVENIO,
COALESCE(MAX(V.FLG_CLIENTE_TC),0) FLG_CLIENTE_TC,
COALESCE(MAX(V.FLG_CLIENTE_LIBRE_DISPON),0) FLG_CLIENTE_LIBRE_DISPON,
COALESCE(MAX(V.FLG_CLIENTE_MILLONARIA),0) FLG_CLIENTE_MILLONARIA,
COALESCE(MAX(V.FLG_CLIENTE_AHOR_CRED),0) FLG_CLIENTE_AHOR_CRED,
COALESCE(MAX(V.FLG_CLIENTE_CRED_PREF),0) FLG_CLIENTE_CRED_PREF,
COALESCE(MAX(V.FLG_CLIENTE_PRE_FACIL),0) FLG_CLIENTE_PRE_FACIL,
COALESCE(MAX(V.FLG_CLIENTE_INVERSION),0) FLG_CLIENTE_INVERSION,
COALESCE(MAX(V.FLG_CLIENTE_PLAZO_FIJO),0) FLG_CLIENTE_PLAZO_FIJO,
COALESCE(MAX(V.FLG_CLIENTE_TXS),0) FLG_CLIENTE_TXS,
COALESCE(MAX(V.RANGO_INGRESO),'SV') RANGO_INGRESO,
COALESCE(MAX(V.FLG_CLIENTE_TUNKI),0) FLG_CLIENTE_TUNKI,
'202109' PERIODO_VAL -- MES PARAMETRO (mes actual - 1 , a que se debe correr el 1ero de cada mes)
FROM ...............V_CONSOLIDADO_SUNAT_HIST A
LEFT JOIN ...............V_MAESTRA_RENIEC B
ON ( CASE WHEN A.NUMRUC LIKE '10%' THEN A.CODDOC ELSE A.CODDOCRELE END) = B.CODDOC
LEFT JOIN (
SELECT ROW_NUMBER() OVER (PARTITION BY
(CASE WHEN NUMDOCTRIB LIKE '.' OR NUMDOCTRIB LIKE '' OR NUMDOCTRIB IS NULL THEN NUMDOCTRIB_TRDT ELSE NUMDOCTRIB END)
ORDER BY NUMDOCTRIB DESC, TIPPERSONA ASC, CODSBS) ORDER_CODSBS,
CODSBS, NUMDOCTRIB_TRDT, NUMDOCTRIB, CODDOC_TRDT, CODDOC, CODUNICOCLI
FROM ...............V_RSK_FCT_MAEPERSONA_RCC
) S
ON A.NUMRUC = (CASE WHEN S.NUMDOCTRIB LIKE '.' OR S.NUMDOCTRIB LIKE '' OR S.NUMDOCTRIB IS NULL THEN S.NUMDOCTRIB_TRDT ELSE S.NUMDOCTRIB END)
LEFT JOIN(
SELECT ROW_NUMBER() OVER (PARTITION BY
(CASE WHEN CODDOC LIKE '.' OR CODDOC LIKE '' OR CODDOC IS NULL THEN CODDOC_TRDT ELSE CODDOC END)
ORDER BY NUMDOCTRIB DESC, TIPPERSONA ASC, CODSBS) RRLL_CODSBS,
CODSBS, NUMDOCTRIB_TRDT, NUMDOCTRIB, CODDOC_TRDT, CODDOC, CODUNICOCLI
FROM ...............V_RSK_FCT_MAEPERSONA_RCC
WHERE CODDOC NOT LIKE '.' OR CODDOC_TRDT NOT LIKE '.'
) R
ON A.CODDOCRELE = (CASE WHEN R.CODDOC LIKE '.' OR R.CODDOC LIKE '' OR R.CODDOC IS NULL THEN R.CODDOC_TRDT ELSE R.CODDOC END)
LEFT JOIN ...............V_360_CLIENTE V
ON ( CASE WHEN A.NUMRUC LIKE '10%' THEN TRIM(A.CODDOC) ELSE TRIM(A.CODDOCRELE) END) = TRIM(V.NRO_DOCUMENTO)
WHERE 1 = 1
AND ESTADORELE <> 9
AND SUBSTR(NUMRUC,1,2) IN ('15','17','20','10')
AND A.FECCARGA = '202107' -- 'PERIODO_A_COPIAR'
AND V.COD_MES = '202109' -- MES PARAMETRO (mes actual - 1 , a que se debe correr el 1ero de cada mes)
AND V.FRECUENCIA = 1
--AND A.NUMRUC IN (
-- '20194658371',
-- '20390455080',
-- '20207967158',
-- '20339968455',
-- '20121142148',
-- '20602767915',
-- '20100915783',
-- '10700199849',
-- '20553853355',
-- '10069686716',
-- '10101719451',
-- '20600847300'
-- )
GROUP BY A.FECCARGA, TRIM(A.NUMRUC),
CASE WHEN SUBSTR(NUMRUC,1,2) = '10' AND (TRIM(A.CODDOCRELE) LIKE '' OR A.CODDOCRELE IS NULL) THEN TRIM(SUBSTR(NUMRUC,3,8))
ELSE TRIM(A.CODDOCRELE) END
Revise this Paste