CREATE TABLE d_mdl_vpc_disc.T1_SALDO_MERCADO_RCC
WITH ( format = 'Parquet', 
	   parquet_compression = 'SNAPPY', 
	   partitioned_by = ARRAY['PERIODO'], 
	   external_location= 's3://sagemaker-us-east-1-058528764918/vpc/propension/athena/T1_SALDO_MERCADO_RCC/'
	 )
AS (
SELECT 
	  A.cod_sbs_val AS CODSBS,
	  A.producto_rcc_desc AS PRODUCTO,
	  A.situacion_rcc_desc AS SITUACION,
	  A.empresa_cd AS COD_EMPRESA,
	  CASE WHEN coalesce(A.tipoempresafinanc_desc,'') ='BANCOS' AND coalesce(A.estadoempresafinanc_desc,'') ='EN OPERACION' THEN 'BANCOS' ELSE 'CAJAS & FINANC.' END AS TIPO_ENTIDAD,
	  CASE WHEN coalesce(A.tipoempresafinanc_desc,'') ='BANCOS' AND coalesce(A.estadoempresafinanc_desc,'') ='EN OPERACION' THEN
	  A.nomcortoempresafinanc_desc ELSE 'CAJAS & FINANC.' END AS BANCO,
	  A.tipo_producto_rcc_desc TIPO_PRODUCTO,
	  SUM(coalesce(A.saldo_amt, 0)) MTO_SALDO,
      MAX(coalesce(A.flg_lin_mercado, 'N')) flg_lin_mercado,
	  MAX(coalesce(A.condicion_val, 0)) MAX_DIAS_ATRASO,
      MAX(CASE WHEN coalesce(A.clasificacion_deudor_cd, '8') in ('0', '8') then 1 else 0 END) FLG_TIENE_CLASIF_NORMAL,
      MAX(CASE WHEN coalesce(A.clasificacion_deudor_cd, '8') = '1' then 1 else 0 END) FLG_TIENE_CLASIF_CPP,
      MAX(CASE WHEN coalesce(A.clasificacion_deudor_cd, '8') = '2' then 1 else 0 END) FLG_TIENE_CLASIF_DEFICIENTE,
      MAX(CASE WHEN coalesce(A.clasificacion_deudor_cd, '8') = '3' then 1 else 0 END) FLG_TIENE_CLASIF_DUDOSO,
      MAX(CASE WHEN coalesce(A.clasificacion_deudor_cd, '8') not in ('0','1', '2', '3', '8') then 1 else 0 END) FLG_TIENE_CLASIF_PERDIDA,
      CASE WHEN cod_sbs_val in (select cod_sbs_val from e_perm_aws.t_vpc_segmentacion_merc where banca_fin_val = 'BPE' or banca_mes_val = 'BPE') THEN 1 ELSE 0 END FLG_BPE_TIMELINE,
      CASE WHEN cod_sbs_val in (select cod_sbs_val from e_perm_aws.t_vpc_segmentacion_merc where banca_fin_val = 'BE' or banca_mes_val = 'BE') THEN 1 ELSE 0 END FLG_BE_TIMELINE,
      CASE WHEN cod_sbs_val in (select cod_sbs_val from e_perm_aws.t_vpc_segmentacion_merc where banca_fin_val = 'BC' or banca_mes_val = 'BC') THEN 1 ELSE 0 END FLG_BC_TIMELINE,
      CASE WHEN cod_sbs_val in (select cod_sbs_val from e_perm_aws.t_vpc_segmentacion_merc where banca_fin_val = 'MICRO' or banca_mes_val = 'MICRO') THEN 1 ELSE 0 END FLG_MICRO_TIMELINE,
	  periodo_val AS PERIODO
   FROM e_perm_aws.T_VPC_FACT_SALDOS_RCC A
   LEFT JOIN e_perm_aws.t_dim_vpc_cta_ctble_rcc B
   ON A.Cuenta_RCC_Val = B.Cuenta_Ctble_RCC_Cd
   WHERE 1 = 1
   AND date_parse(cast(periodo_val as varchar),'%Y%m') >= (SELECT date_add('month', -18, date_parse(cast(MAX(periodo_val) as varchar),'%Y%m')) FROM e_perm_aws.T_VPC_FACT_SALDOS_RCC)
   AND A.tipo_producto_rcc_desc IN (
	 'COL. DIRECTAS','REACTIVA','COL. INDIRECTAS','FAE','INMOBILIARIO', 'CREDITOS CASTIGADOS', 'GARANTIA_FAE', 'GARANTIA_REACTIVA', 'GARANTIAS', 'REPROGRAMADOS'
   )
   AND 
   (
	  (
		  A.flg_lin_mercado = 'S' AND
		  (
			A.producto_rcc_desc NOT IN ('OTROS') 
			OR 
			(
			  A.producto_rcc_desc ='OTROS' AND 
			  B.Cuenta_Ctble_RCD_Desc IN ('FINANCIACION DE PROYECTOS')
			)
		  )
	  )					
	  OR A.tipo_producto_rcc_desc IN ('REACTIVA','FAE','INMOBILIARIO', 'CREDITOS CASTIGADOS', 'GARANTIA_FAE', 'GARANTIA_REACTIVA', 'GARANTIAS', 'REPROGRAMADOS')
  )
  GROUP BY 
	  A.periodo_val,
	  A.cod_sbs_val,
	  A.producto_rcc_desc,
	  A.situacion_rcc_desc,
	  A.empresa_cd,
	  CASE WHEN coalesce(A.tipoempresafinanc_desc,'') ='BANCOS' AND coalesce(A.estadoempresafinanc_desc,'') ='EN OPERACION' THEN 'BANCOS' ELSE 'CAJAS & FINANC.' END,
	  CASE WHEN coalesce(A.tipoempresafinanc_desc,'') ='BANCOS' AND coalesce(A.estadoempresafinanc_desc,'') ='EN OPERACION' THEN
	  A.nomcortoempresafinanc_desc ELSE 'CAJAS & FINANC.' END,
	  A.tipo_producto_rcc_desc
  
)