Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- DO $$ BEGIN INSERT INTO comisv2.EVT_BAJA (cod_evt_6470_pk,IDCABEVENTO,FIRMA ,CANT_BAJAS,CARTERA,COD_APROB,COD_CLCO,COD_OPER,CONTRATO,DIAS_ACTIV,FDV,FEC_ALTA,FEC_BAJA,LINEA,MES_ALTA,MES_APRO,MES_BAJA,MONTO_FACT,MTO_PLAN_DAT,MTO_PLAN_LL,MTO_PLAN_SMS,PENALIZACION,PLAN_DAT,PLAN_LL,PLAN_SMS,PRODUCTO,TIP_MOVI,VALOR_PARTICION,VENDEDOR) SELECT NEXTVAL('sq_pk_evt_6470'), 500383,'BAJAS_DIR' ,CANT_BAJAS,CARTERA,COD_APROB,COD_CLCO,COD_OPER,CONTRATO,DIAS_ACTIV,FDV,FEC_ALTA,FEC_BAJA,LINEA,MES_ALTA,MES_APRO,MES_BAJA,MONTO_FACT,MTO_PLAN_DAT,MTO_PLAN_LL,MTO_PLAN_SMS,PENALIZACION,PLAN_DAT,PLAN_LL,PLAN_SMS,PRODUCTO,TIP_MOVI,VALOR_PARTICION,VENDEDOR FROM (SELECT q.* FROM (--POSTGRES BAJAS_DIR 25/11/2019
- SELECT d.str_codigo AS FDV,
- (TO_CHAR (TO_TIMESTAMP('02/01/2020 00:00:00', 'DD-MM-YYYY HH24:MI:SS'), 'YYYYMM'))::NUMERIC||
- (CASE d.str_codigo
- WHEN 'GCL' THEN 'GCL'
- WHEN 'VG' THEN 'GCL'
- WHEN 'BK' THEN 'GCL'
- WHEN 'TV' THEN 'TV'
- WHEN 'CCI' THEN 'CCI'
- WHEN 'RAC' THEN 'RAC'
- WHEN 'PYM' THEN 'PYM'
- WHEN 'JPY' THEN 'JPY'
- WHEN 'SPY' THEN 'SPY'
- WHEN 'JPV' THEN 'JPV'
- WHEN 'PV' THEN 'PV'
- WHEN 'PVJ' THEN 'PVJ'
- WHEN 'SPV' THEN 'SPV'
- WHEN 'NN' THEN 'NN'
- WHEN 'IM' THEN 'IM'
- WHEN 'VDA' THEN 'VDA'
- WHEN 'FL' THEN 'FL'
- WHEN 'FPPO' THEN 'FPP'
- WHEN 'PON' THEN 'PON'
- WHEN 'CC' THEN 'CC' --Contact Center
- WHEN 'LCC' THEN 'LCC' --Lider Contact Center
- WHEN 'SCC' THEN 'SCC' --Supervisor Contact Center
- WHEN 'RAC' THEN 'RAC'
- WHEN 'VR' THEN 'VR' --Vendedor RAP
- WHEN 'SRP' THEN 'SRP' --Supervisor RAP
- WHEN 'BKA' THEN 'BKA' --Backoffice ACI
- WHEN 'CST' THEN 'CST' --Ejecutivo cuentas soluciones tecnologicas
- ELSE 'X'
- END) AS VALOR_PARTICION,
- e.cod_aprob,
- e.cod_clco,
- e.cod_oper,
- e.contrato,
- e.dias_activ,
- e.fec_alta,
- e.fec_baja,
- e.linea,
- e.mes_alta,
- e.mes_apro,
- e.mes_baja,
- e.mto_plan_dat,
- e.mto_plan_ll,
- e.mto_plan_sms,
- e.penalizacion,
- e.plan_dat,
- e.plan_ll,
- e.plan_sms,
- e.producto,
- e.tip_movi,
- e.vendedor,
- 0.0::NUMERIC AS MONTO_FACT,
- 0::NUMERIC AS CANT_BAJAS,
- e.CARTERA AS CARTERA,
- e.COD_USUA
- FROM evt_etl_baja e
- join cabeventos o ON o.cod_cabevento_pk = e.idcabevento
- join perproccabevt t ON t.cod_cabevento = o.cod_cabevento_pk
- join periodoprocesos p ON t.cod_perproc = p.cod_perproc_pk
- join instfdv i ON i.str_nombre = e.vendedor
- join instfdvatrib f ON f.cod_instfdv = i.cod_instfdv_pk
- join tipofdvatrib g ON g.cod_tipofdvatrib_pk = f.cod_tipofdvatrib
- join tipofdv j ON j.cod_tipofdv_pk = g.cod_tipofdv
- join listacerradadet d ON d.cod_listacerradadet_pk = f.ldc_valor
- WHERE g.str_nombre = 'FDV'
- AND j.str_nombre = 'DIRECTAS'
- AND o.str_confirmado = 'S'
- AND o.str_firma = 'ETL_BAJAS'
- AND p.mes_anho = (TO_CHAR(TO_TIMESTAMP ('02/01/2020 00:00:00', 'DD-MM-YYYY HH24:MI:SS'), 'MM')||'/'||TO_CHAR(TO_TIMESTAMP ('02/01/2020 00:00:00', 'DD-MM-YYYY HH24:MI:SS'), 'YYYY'))
- AND i.fecha_desde <= TO_TIMESTAMP ('02/01/2020 00:00:00', 'DD-MM-YYYY HH24:MI:SS')
- AND COALESCE(i.fecha_hasta, now()) >= TO_TIMESTAMP ('02/01/2020 00:00:00', 'DD-MM-YYYY HH24:MI:SS')
- AND i.enum_activo = 'S') q WHERE VALOR_PARTICION = '202001PYM') AS query; END$$ ;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement