SELECT DISTINCT
NVL(TLO.POD, 'NULL') AS POD,
NVL(TLO.ENELTEL, 'NULL') AS CLIENTE,
NVL(CASE WHEN PMS.CODE_STATO_PM = 'O' THEN 'ACF' ELSE 'ASF' END, 'NULL') AS STATO_CLIENTE,
NVL(MATRICOLA_MISURATORE, 'NULL') AS MATRICOLA,
NVL(CODICE_MISURATORE, 'NULL') AS CODICE,
NVL(TO_CHAR(RUTA), 'NULL') AS RUTA,
NVL(TO_CHAR(TLO.DT_LETT_ACT,'dd/MM/yyyy'), 'NULL') as DT_LETT_ACT,
NVL(TLO.CLAVE_LECTURA, 'NULL') AS CLAVE_LECTURA,
(SELECT NVL(DESCRIZIONE, 'NULL') FROM DEM.ANAG_CLAVE_LECTURA ACL WHERE LPAD(ACL.CLAVE_LECTURA,3,'0') = LPAD(TLO.CLAVE_LECTURA,3,'0') AND COD_SOCIETA = 'CC01' AND GRUPPO = TLI.GRUPPO_CLIENTI and rownum = 1) AS DESCRIPCION,
NVL(CASE WHEN SUBSTR(CODICE_MISURATORE,1,3) = '002' THEN 'S' ELSE 'N' END, 'NULL') AS FACTURADO_EN_TERRENO,
NVL(CASE WHEN SUBSTR(CODICE_MISURATORE,1,3) = '002' THEN 'IMEDIATO'
WHEN SUBSTR(CODICE_MISURATORE,1,3) = '001' THEN 'TELEMEDIDO' ELSE 'OUTROS' END, 'NULL') TIPO_CLIENTE,
NVL(TLI.GRUPPO_CLIENTI, 'NULL') AS GRUPPO_CLIENTI,
NVL(TO_CHAR(TLI.SECTOR), 'NULL') AS SECTOR,
NVL(TO_CHAR(TLO.DT_LETT_ANTE,'dd/MM/yyyy'), 'NULL') as DT_LETT_ANTE,
--LECTURAS ATIVAS
TLO.LETT_ENER_ATT_HP_PREL_ACT_F2 AS LETT_ENER_ATT_HP_PREL_ACT_F2,
TLO.LETT_ENER_ATT_FP_PREL_ACT_F3 AS LETT_ENER_ATT_FP_PREL_ACT_F3,
TLO.LETT_ENER_ATT_HI_PREL_ACT_F4 AS LETT_ENER_ATT_HI_PREL_ACT_F4,
TLO.LETT_ENER_ATT_HR_PREL_ACT_F1 AS LETT_ENER_ATT_HR_PREL_ACT_F1,
--LEITURAS REATIVAS
TLO.LETT_ENER_UFER_HP_REAT_ACT_F1 AS LETT_ENER_UFER_HP_REAT_ACT_F1,
TLO.LETT_ENER_UFER_FP_REAT_ACT_F3 AS LETT_ENER_UFER_FP_REAT_ACT_F3,
NULL AS LETT_ENER_UFER_HI_REAT_ACT_F4, -- NÃO TEM
TLO.LETT_ENER_UFER_HR_REAT_ACT_F5 AS LETT_ENER_UFER_HR_REAT_ACT_F5,
--LEITURAS POTENZA
TLO.LETT_POT_ATT_HP_PREL_ACT_F2 AS LETT_POT_ATT_HP_PREL_ACT_F2,
TLO.LETT_POT_ATT_FP_PREL_ACT_F3 AS LETT_POT_ATT_FP_PREL_ACT_F3,
NULL AS LETT_POT_ATT_HI_PREL_ACT_F4, --NÃO TEM
TLO.LETT_POT_ATT_HR_PREL_ACT_F1 AS LETT_POT_ATT_HR_PREL_ACT_F1,
--LEITURAS POTENZA REATIVA
TLO.LETT_ENER_DMCR_HP_REAT_ACT_F2 AS LETT_ENER_DMCR_HP_REAT_ACT_F2,
TLO.LETT_ENER_DMCR_FP_REAT_ACT_F4 AS LETT_ENER_DMCR_FP_REAT_ACT_F4,
NULL AS LETT_ENER_DMCR_HI_REAT_ACT_F4, --NÃO TEM
TLO.LETT_ENER_DMCR_HR_REAT_ACT_F6 AS LETT_ENER_DMCR_HR_REAT_ACT_F6,
--CONSUMO ATIVO LEIDO
TLO.ENER_ATT_HP_PREL_ACT_F2_MIS AS ENER_ATT_HP_PREL_ACT_F2_MIS,
TLO.ENER_ATT_FP_PREL_ACT_F3_MIS AS ENER_ATT_FP_PREL_ACT_F3_MIS,
TLO.ENER_ATT_HI_PREL_ACT_F4_MIS AS ENER_ATT_HI_PREL_ACT_F4_MIS,
TLO.ENER_ATT_HR_PREL_ACT_F1_MIS AS ENER_ATT_HR_PREL_ACT_F1_MIS,
--CONSUMO REATIVO LEIDO
TLO.ENER_UFER_HP_REAT_ACT_F1_MIS AS ENER_UFER_HP_REAT_ACT_F1_MIS,
TLO.ENER_UFER_FP_REAT_ACT_F3_MIS AS ENER_UFER_FP_REAT_ACT_F3_MIS,
NULL AS ENER_UFER_HI_REAT_ACT_F4_MIS , -- NÃO TEM
TLO.ENER_UFER_HR_REAT_ACT_F5_MIS AS ENER_UFER_HR_REAT_ACT_F5_MIS,
--CONSUMO POTENZA LEIDO
TLO.POT_ATT_HP_PREL_ACT_F2_MIS AS POT_ATT_HP_PREL_ACT_F2_MIS,
TLO.POT_ATT_FP_PREL_ACT_F3_MIS AS POT_ATT_FP_PREL_ACT_F3_MIS,
NULL AS POT_ATT_HI_PREL_ACT_F4_MIS , --NÃO TEM
TLO.POT_ATT_HR_PREL_ACT_F1_MIS AS POT_ATT_HR_PREL_ACT_F1_MIS,
--CONSUMO POTENZA REATIVA LEIDO
TLO.ENER_DMCR_HP_REAT_ACT_F2_MIS AS ENER_DMCR_HP_REAT_ACT_F2_MIS,
TLO.ENER_DMCR_FP_REAT_ACT_F4_MIS AS ENER_DMCR_FP_REAT_ACT_F4_MIS,
NULL AS ENER_DMCR_HI_REAT_ACT_F4_MIS , --NÃO TEM
TLO.ENER_DMCR_HR_REAT_ACT_F6_MIS AS ENER_DMCR_HR_REAT_ACT_F6_MIS,
--CONSUMO ATIVO FACTURADO
TLO.ENER_ATT_HP_PREL_ACT_F2 AS ENER_ATT_HP_PREL_ACT_F2,
TLO.ENER_ATT_FP_PREL_ACT_F3 AS ENER_ATT_FP_PREL_ACT_F3,
TLO.ENER_ATT_HI_PREL_ACT_F4 AS ENER_ATT_HI_PREL_ACT_F4,
TLO.ENER_ATT_HR_PREL_ACT_F1 AS ENER_ATT_HR_PREL_ACT_F1,
--CONSUMO REATIVO FACTURADO
TLO.ENER_UFER_HP_REAT_ACT_F1 AS ENER_UFER_HP_REAT_ACT_F1,
TLO.ENER_UFER_FP_REAT_ACT_F3 AS ENER_UFER_FP_REAT_ACT_F3,
NULL AS ENER_UFER_HI_REAT_ACT_F4 , -- NÃO TEM
TLO.ENER_UFER_HR_REAT_ACT_F5 AS ENER_UFER_HR_REAT_ACT_F5,
--CONSUMO POTENZA FACTURADO
TLO.POT_ATT_HP_PREL_ACT_F2 AS POT_ATT_HP_PREL_ACT_F2,
TLO.POT_ATT_FP_PREL_ACT_F3 AS POT_ATT_FP_PREL_ACT_F3,
NULL AS POT_ATT_HI_PREL_ACT_F4, --NÃO TEM
TLO.POT_ATT_HR_PREL_ACT_F1 AS POT_ATT_HR_PREL_ACT_F1,
--CONSUMO POTENZA REATIVA FACTURADO
TLO.ENER_DMCR_HP_REAT_ACT_F2 AS ENER_DMCR_HP_REAT_ACT_F2,
TLO.ENER_DMCR_FP_REAT_ACT_F4 AS ENER_DMCR_FP_REAT_ACT_F4,
NULL AS ENER_DMCR_HI_REAT_ACT_F4, --NÃO TEM
TLO.ENER_DMCR_HR_REAT_ACT_F6 AS ENER_DMCR_HR_REAT_ACT_F6,
--CONSTANTE LECTURA ATIVA
TLI.COST_ENER_HP_ATT_PREL_F2 AS COST_ENER_HP_ATT_PREL_F2,
TLI.COST_ENER_FP_ATT_PREL_F3 AS COST_ENER_FP_ATT_PREL_F3,
TLI.COST_ENER_HI_ATT_PREL_F4 AS COST_ENER_HI_ATT_PREL_F4,
TLI.COST_ENER_HR_ATT_PREL_F1 AS COST_ENER_HR_ATT_PREL_F1,
--CONSTANTE LECTURA REATIVA
TLI.COST_ENER_UFER_HP_REAT_F1 AS COST_ENER_UFER_HP_REAT_F1,
TLI.COST_ENER_UFER_FP_REAT_F3 AS COST_ENER_UFER_FP_REAT_F3,
TLI.COST_ENER_UFER_HI_REAT_F7 AS COST_ENER_UFER_HI_REAT_F7,
TLI.COST_ENER_UFER_HR_REAT_F5 AS COST_ENER_UFER_HR_REAT_F5,
--CONSTANTE POTENZA ATIVA
TLI.COST_POT_HP_ATT_PREL_F2 AS COST_POT_HP_ATT_PREL_F2,
TLI.COST_POT_FP_ATT_PREL_F3 AS COST_POT_FP_ATT_PREL_F3,
TLI.COST_POT_HI_ATT_PREL_F4 AS COST_POT_HI_ATT_PREL_F4,
TLI.COST_POT_HR_ATT_PREL_F1 AS COST_POT_HR_ATT_PREL_F1,
--CONSTANTE PONTENZA REATIVA
TLI.COST_ENER_DMCR_HP_REAT_F2 AS COST_ENER_DMCR_HP_REAT_F2,
TLI.COST_ENER_DMCR_FP_REAT_F4 AS COST_ENER_DMCR_FP_REAT_F4,
TLI.COST_ENER_DMCR_HI_REAT_F8 AS COST_ENER_DMCR_HI_REAT_F8,
TLI.COST_ENER_DMCR_HR_REAT_F6 AS COST_ENER_DMCR_HR_REAT_F6,
NVL(TO_CHAR(TLI.COD_SOCIETA), 'NULL') AS COD_SOCIETA,
NVL(TO_CHAR(TLI.ANNOMESE), 'NULL') AS ANNOMESE,
(case when (SELECT 1 FROM sam.tg_letture_ko_ctrlc_syn
WHERE id_punto_misura = tli.id_punto_misura
AND dt_lettura = tli.dt_lett_act
AND dt_fine_validita is not null
and rownum = 1) = 1 then 1 else 0 end
) as CRITICADA
FROM
DEM.TAB_LECTU_IN TLI
INNER JOIN DEM.TAB_LECTU_OUT TLO
ON TLI.ID_PUNTO_MISURA = TLO.ID_PUNTO_MISURA
INNER JOIN TG.PUNTI_MISURA_SYN PMS
ON PMS.ID_PUNTO_MISURA = TLI.ID_PUNTO_MISURA
WHERE TLI.COD_SOCIETA = 'CC01'
AND TLI.ANNOMESE = 202005
AND TLI.GRUPPO_CLIENTI = 'B'
AND TLI.SECTOR = 5
AND TLI.DT_FINE_VAL > SYSDATE
AND TLI.ENELTEL = 'C00030402'
AND TLO.COD_SOCIETA = 'CC01'
AND TLO.ANNOMESE = 202005
;
