--01.FUENTE PPA
IF OBJECT_ID('T_GESCOM_PPA_FN_01','U') IS NOT NULL DROP TABLE T_GESCOM_PPA_FN_01
select BANCA_ORD, CU_ORD,NUM_RUC,NUM_DOC,TIP_DOC,BANCA_BEN,
       SUM(VOLUMEN) AS VOLUMEN,
       COUNT(DISTINCT  PERIODO ) AS FRECUENCIA,
       --SUM(VOLUMEN)/6 AS VOLUMEN_PROM_6M,
     MAX(CASE WHEN PERIODO=@CODMES_D2 THEN 1 ELSE 0 END) AS TRX_UM,
     SUM(CASE WHEN PERIODO=@CODMES_D2 THEN VOLUMEN ELSE 0 END) AS VOLUMEN_UM,
     MAX(PERIODO) AS MAX_PERIODO,
     MIN(PERIODO) AS MIN_PERIODO
INTO T_GESCOM_PPA_FN_01 --SELECT DISTINCT BANCA_BEN
FROM T_GESCOM_ECO_PROVEEDORES_DIARIOS
WHERE PRODUCTO NOT IN ('Pagos Varios') 
AND FLG_VALIDO =1 -- RETIRA PASARELAS DE PAGO
AND FAMILIA_PRODUCTO IN ('PAGOS MASIVOS','FINANCIAMIENTO DE VENTAS')
AND PERIODO>='202101'
GROUP BY BANCA_ORD,CU_ORD,NUM_RUC,NUM_DOC,TIP_DOC,BANCA_BEN

EXECUTE ODS.SP_LOG_PROCESOS '[PROCESO INPUTS MERCADO BPE RIESGOS]','UNIVERSO FACTURA NEGOCIABLE BPE',NULL,'1.PPA',NULL,@@ROWCOUNT

--02.FUENTE HUBS (MAR)
IF OBJECT_ID('T_GESOM_BASE_HUBS_ECO_FN_01','U') IS NOT NULL DROP TABLE T_GESOM_BASE_HUBS_ECO_FN_01
SELECT *
INTO T_GESOM_BASE_HUBS_ECO_FN_01
FROM (
SELECT A.* ,
YEAR(CAST(FECHA_REGISTRO AS DATE))*100+MONTH(CAST(FECHA_REGISTRO AS DATE)) AS PERIODO_BASE,
ROW_NUMBER()OVER(PARTITION BY CU_ORD,RUC_PROV ORDER BY YEAR(CAST(FECHA_REGISTRO AS DATE))*100+MONTH(CAST(FECHA_REGISTRO AS DATE))) AS ORDEN 
 --SELECT *
FROM T_GESCOM_ECOSISTEMA_CARGA_BASES_BANCA A ---GOBERNADA MAR - JORGE BLAS
) 
A WHERE ORDEN=1


--03.FUENTE HUBS (BPE)
IF OBJECT_ID('T_GESOM_BASE_HUBS_BPE_FN_01','U') IS NOT NULL DROP TABLE T_GESOM_BASE_HUBS_BPE_FN_01
SELECT *
INTO T_GESOM_BASE_HUBS_BPE_FN_01
FROM(
SELECT B.BANCA BANCA_ORD,RIGHT('0000000000'+[CU HUB],10 ) CU_ORD,RUC RUC_BEN,TIPO,
ROW_NUMBER()OVER(PARTITION BY RIGHT('0000000000'+[CU HUB],10) , RUC ORDER BY PERIODO_INGRESO DESC ) AS ORDEN --SELECT *
FROM ODS.MM_BASE_ECOSISTEMAS A
INNER JOIN 
ODS.MD_VPC_CLIENTE B ON RIGHT('0000000000'+[CU HUB],10 )=B.CODUNICOCLI
) A 
WHERE ORDEN=1