--05.CONSOLIDAR UNIVERSOS
IF OBJECT_ID('T_GESCOM_UNIVERSO_FN_01','U') IS NOT NULL DROP TABLE T_GESCOM_UNIVERSO_FN_01
SELECT *
INTO T_GESCOM_UNIVERSO_FN_01
FROM 
(
SELECT 'PPA' FUENTE,'ECOSISTEMAS IBK' TIPO_ORIGEN,BANCA_ORD,CU_ORD,NUM_RUC AS RUC_BEN,BANCA_BEN,'PROVEEDOR' AS TIPO
FROM T_GESCOM_PPA_FN_01
UNION ALL
SELECT 'HUBS' FUENTE,'ECOSISTEMAS IBK',B.BANCA BANCA_ORD,CU_ORD,RUC_PROV AS RUC_BEN,CAST( NULL AS VARCHAR(10) )BANCA_BEN,RELACION AS TIPO --SELECT *
FROM T_GESOM_BASE_HUBS_ECO_FN_01 A
LEFT JOIN ODS.MD_VPC_CLIENTE B ON A.CU_ORD=B.CODUNICOCLI
UNION ALL
SELECT 'HUBS' FUENTE,'ECOSISTEMAS IBK',B.BANCA BANCA_ORD,CU_ORD,RUC_BEN AS RUC_BEN,CAST( NULL AS VARCHAR(10) )BANCA_BEN, TIPO --SELECT *
FROM T_GESOM_BASE_HUBS_BPE_FN_01 A
LEFT JOIN ODS.MD_VPC_CLIENTE B ON A.CU_ORD=B.CODUNICOCLI
UNION ALL
SELECT 'AFILIACIONES' FUENTE,'ECOSISTEMAS IBK',B.BANCA BANCA_ORD,CU_ORD,A.NUM_RUC AS RUC_BEN,CAST( NULL AS VARCHAR(10) )BANCA_BEN,'PROVEEDOR' TIPO --SELECT *
--SELECT *
FROM T_GESCOM_AFILIACIONES A
LEFT JOIN ODS.MD_VPC_CLIENTE B ON A.CU_ORD=B.CODUNICOCLI
UNION ALL
SELECT 'SUNAT' FUENTE,'SUNAT PJ-BANK','SB' BANCA_ORD,NULL CU_ORD,NUM_RUC AS RUC_BEN,CAST( NULL AS VARCHAR(10) )BANCA_BEN,'SIN DATO' --SELECT *
FROM T_GESCOM_SUNAT_PJ_BANK
) A