CREATE TABLE DLAB_PROGRAMADATOSVPC.HM_UNIVERSO_1_BPE as(
		    select CASE WHEN a.Cod_Tipo_Documento='2' AND a.Nro_Documento LIKE '10%' THEN SUBSTRING(a.Nro_Documento, 3, 8) ELSE a.Nro_Documento END NUMDOC
			, cast('1.- MERCADO BPE' as varchar(50)) CATEGORIA
			, case when A.Banca_Fin_Val<>'BPE' then 1 else 0 end FLG_ADI
			, a.logica_banca_mes_val
			, a.logica_banca_fin_val
			, A.Banca_Mes_Val
			, A.Banca_Fin_Val
			, case 
				when Banca_Fin_Val='BPE' then case when Logica_Banca_Fin_Val='ES PERSISTENTE' then Logica_Banca_Mes_Val else Logica_Banca_Fin_Val end
				when Banca_Mes_Val='BPE' then Logica_Banca_Mes_Val
				end LOGICA
			, 1 FLG_MERCADO_BPE
			, 0 FLG_RESCATE_BPE
			, 0 FLG_RESCATE_MICRO
			, 0 FLG_PJ
		    , 0 FLG_CTA_NEG
		    , 0 FLG_PROVEEDORES
		    , 0 FLG_CLIENTES
			, 0 FLG_RESCATE_BE
		    , 0 FLG_BARRIDO_TLV_BPE
			, 0 FLG_PNCN
			--select max(Fecha_SBS_Dt)
			FROM E_DW_VIEWS.V_VPC_SEGMENTACION_MERC A
			where A.Fecha_SBS_Dt = ADD_MONTHS(CAST('202209'||'01' AS DATE FORMAT 'YYYYMMDD'),1)-1
			and (A.Banca_Fin_Val='BPE' or A.Banca_Mes_Val='BPE')
			
		UNION ALL
		--2.- RESCATE BPE
		--3.- RESCATE MICRO --8.- RESCATE BE
		select  NUMDOC, CATEGORIA, 0 FLG_ADI, a.logica_banca_mes_val, a.logica_banca_fin_val, A.Banca_Mes_Val,A.Banca_Fin_Val, ''
			, 0 FLG_MERCADO_BPE
			, a.FLG_RESCATE_BPE FLG_RESCATE_BPE
			, a.FLG_RESCATE_MICRO FLG_RESCATE_MICRO
			, 0 FLG_PJ
		    , 0 FLG_CTA_NEG
		    , 0 FLG_PROVEEDORES
		    , 0 FLG_CLIENTES
			, a.FLG_RESCATE_BE FLG_RESCATE_BE
		    , 0 FLG_BARRIDO_TLV_BPE
			, 0 FLG_PNCN
				--select *
			from (select
							row_number() over(partition by a.Cod_Tipo_Documento, a.Nro_Documento order by A.Fecha_SBS_Dt desc) seq
							, CASE WHEN  a.Cod_Tipo_Documento='2' AND a.Nro_Documento LIKE '10%' THEN SUBSTRING(a.Nro_Documento, 3, 8) ELSE a.Nro_Documento END NUMDOC
							, case when A.Banca_Fin_Val='BPE' then cast('2.- RESCATE BPE' as varchar(50))
							  	when A.Banca_Fin_Val='MICRO' then cast('3.- RESCATE MICRO' as varchar(50))
							  	when (A.Banca_Fin_Val='BE' and a.Segmento_Fin_Val='S2') then cast('8.- RESCATE BE' as varchar(50))
								end CATEGORIA
							, case when A.Banca_Fin_Val='BPE' then 1 else 0 end FLG_RESCATE_BPE
							, case when A.Banca_Fin_Val='MICRO' then 1 else 0 end FLG_RESCATE_MICRO
							, case when (A.Banca_Fin_Val='BE' and a.Segmento_Fin_Val='S2') then 1 else 0 end FLG_RESCATE_BE
							, a.*
						FROM E_DW_VIEWS.V_VPC_SEGMENTACION_MERC a
						where A.Fecha_SBS_Dt>ADD_MONTHS(CAST('202209'||'01' AS DATE FORMAT 'YYYYMMDD'),-11)-1
							and A.Fecha_SBS_Dt<= ADD_MONTHS(CAST('202209'||'01' AS DATE FORMAT 'YYYYMMDD'),1)-1
							and (A.Banca_Fin_Val='BPE' or A.Banca_Fin_Val='MICRO' or (A.Banca_Fin_Val='BE' and a.Segmento_Fin_Val='S2'))
					) a
			left join E_DW_VIEWS.V_FACT_VPC_IRCC_MAX_DEUDA c
			on c.TIPO_DOCUMENTO_CD = a.Cod_Tipo_Documento
			and c.NUMERO_DOCUMENTO_VAL = a.Nro_Documento
			and c.FECHA_SBS_DT = ADD_MONTHS(CAST('202209'||'01' AS DATE FORMAT 'YYYYMMDD'),1)-1
			where a.seq=1
				and ( (a.Banca_Fin_Val='MICRO'  and  c.MAX_SALDO_AMT>=10000) 
						or (a.Fecha_SBS_Dt<ADD_MONTHS(CAST('202209'||'01' AS DATE FORMAT 'YYYYMMDD'),1)-1  and a.Banca_Fin_Val='BPE')
						or (a.Fecha_SBS_Dt<ADD_MONTHS(CAST('202209'||'01' AS DATE FORMAT 'YYYYMMDD'),1)-1  and A.Banca_Fin_Val='BE' and a.Segmento_Fin_Val='S2'  and  c.MAX_SALDO_AMT<=1000000)
						)
			) WITH DATA
			PRIMARY INDEX (NUMDOC);