let vMaxDay = '=day(max(F_IFR))';
let vMaxDate = '=max(F_IFR)'
let vMaxMonth = '=month(max(F_IFR))';
let vMaxYear = '=max(Year)';
let vPriorMonth = '=month(addmonths(max(F_IFR),-1))';
let vPriorMonthYear = '=Year(addmonths(max(F_IFR),-1))';
let vPriorYear = '=vMaxYear-1';
let vPriorYearDate = '=date(addyears(max(F_IFR),-1),' & chr(39) & 'DD MMM YYYY' & chr(39) & ')';
[QLK_BUDGET]:
SELECT to_char(PERIODO,'YYYYMM') as PERIODO,
"COD_UNICOM",
"META_MES" AS BUDGET_MONTH,
"META_ACUM" AS BUDGET_YTD
FROM QLK_BUDGET_RECUP
where to_char(PERIODO,'YYYYMM')>='20150101' and to_char(PERIODO,'YYYYMM')<='20180801';
[TR150]:
Load
COD_UNICOM,
TOTAL_KWH AS KWH,
"TOTAL_($)" AS MONTO,
FPTACOBRO AS F_IFR,
Year([FPTACOBRO]) as Year,
Month(FPTACOBRO) as Month,
Day(FPTACOBRO) as Day,
Date(monthstart(FPTACOBRO), 'MM-YYYY') as MonthYear;
SELECT R.COD_UNICOM ,
decode(r.TIP_FACT, 'FT024','Refactura', 'FT032', 'Refactura', 'Factura') as tipo,
R.COD_TAR AS "TARIFA",
R.TIP_REC,
R.TIP_CLI AS "TIPCLIENTE",
r.nic,
R.NIS_RAD AS "NISRAD",
TO_CHAR(R.F_FACT, 'DD/MM/YYYY') AS FFACTURA,
R.F_PUESTA_COBRO AS FPTACOBRO,
SUM(DECODE(I.CO_CONCEPTO, 'CC221', I.CSMO_FACT,
'CC250',I.CSMO_FACT, 'CC270', I.CSMO_FACT,
'CC280', I.CSMO_FACT, 0.0)) AS TOTAL_KWH,
(SUM(I.IMP_CONCEPTO)) AS "TOTAL_($)",
max((Select max(desc_cod)
From TRABPEND_FR@OPENPROD F, codigos@OPENPROD c
Where r.nis_rad = f.nis_rad
and r.sec_nis = f.sec_nis
and r.imp_tot_rec = f.imp_fra
and c.cod = f.cod_clase)) as "clasificacion",
max((Select max(desc_tipo)
From fraudes_concepto@OPENPROD a, tipos@OPENPROD t, TRABPEND_FR@OPENPROD F
Where r.nis_rad = f.nis_rad
and r.sec_nis = f.sec_nis
and r.imp_tot_rec = f.imp_fra
and a.NUM_FR = F.NUM_FR
and t.tipo = a.tip_fra)) as tipo_fra ,
max((Select max(f.num_fr)
From fraudes_concepto@OPENPROD a, tipos@OPENPROD t, TRABPEND_FR@OPENPROD F
Where r.nis_rad = f.nis_rad
and r.sec_nis = f.sec_nis
and r.imp_tot_rec = f.imp_fra
and a.NUM_FR = F.NUM_FR
and t.tipo = a.tip_fra)) as num_fr
FROM IMP_CONCEPTO@OPENPROD I,
RECIBOS@OPENPROD R,
CODIGOS@OPENPROD G
WHERE /*R.COD_UNICOM BETWEEN 2000 AND 2999
AND */R.f_puesta_cobro >= To_date('20180701','YYYYMMDD') //and R.f_puesta_cobro <= To_date('20180731','YYYYMMDD')
AND I.CO_CONCEPTO = G.COD
-- AND I.CO_CONCEPTO IN ('CC100','CC101','CC105','CC220','CC221','CC250','CC270','CC280','CC420','CC320','CC290','CC291','CC106','VA100', 'VA109')
-- AND R.TIP_FACT NOT IN ('FT024','FT032')
-- AND R.SIE_ERROR = 'AI777'
--AND R.TIP_REC IN ('TR010','TR014','TR050')
AND R.TIP_REC ='TR050'
AND r.est_act <> 'ER600'
AND r.est_act <> 'ER610'
AND r.est_act <> 'ER625'
AND R.F_FACT = I.F_FACT
AND R.SEC_REC = I.SEC_REC
AND R.SEC_NIS = I.SEC_NIS
AND R.NIS_RAD = I.NIS_RAD
GROUP BY
R.COD_UNICOM,
r.tip_fact,
R.COD_TAR,
R.TIP_REC,
R.TIP_CLI,
r.nic,
R.NIS_RAD,
R.SEC_NIS,
R.SEC_REC,
R.F_FACT,
R.F_PUESTA_COBRO;
