1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94
| SELECT "NUMLIGNE",
"MODULE",
"ENTREE",
"SORTIE",
"CONTRAT",
"DATE RELEVE",
(SELECT "DATA2"."INDEX RELEVE"
FROM (SELECT ROWNUM "NUMLIGNE", KCPCPT.LKLO "MODULE",
KCPIND.CNCT "CONTRAT",
KCNT.CNDCT "LOCATAIRE",
KCPCPT.CPIDF "NUM CPT",
DECODE(KCPCPT.TCTC,'F','F FROID','C','C CHAUD') "TYPE CPT",
DECODE(KCPIND.COACTIF,0,'HISTORIEE',1,'A TRAITER',KCPIND.COACTIF) "ETAT CONSO",
KCPIND.CODP "DATE RELEVE",
DECODE(NVL(KCPIND.CONM,0),0,KCPIND.CONI,KCPIND.CONM) "INDEX RELEVE",
(SELECT DECODE(NVL(KTARCPT.PKPR,1),1,(SELECT KTARCPT3.TATARIF FROM KTARCPT KTARCPT3 WHERE KTARCPT3.PKPR IS NULL),
KTARCPT.TATARIF) FROM KTARCPT
WHERE (KTARCPT.PKPR=KLOGEMT.PKPR OR KTARCPT.PKPR IS NULL)
AND (KTARCPT.TCTC=KCPCPT.TCTC)
AND (KTARCPT.ROWID = (SELECT MAX(KTARCPT2.ROWID) FROM KTARCPT KTARCPT2 WHERE (KTARCPT.PKPR=KTARCPT2.PKPR OR KTARCPT2.PKPR IS NULL) AND ( KTARCPT2.TCTC=KTARCPT.TCTC))
)
) "P.U",
DECODE (NVL(KCPIND.CONM,0),0,KCPIND.COCONSO,'')"CONSO"
FROM KCPIND, KCPCPT, KCNTLOC, KCNT, KLOGEMT
WHERE ( KCPIND.CNCT=KCNTLOC.CNCT AND KCPIND.CTCT=KCNTLOC.CTCT )
AND (KCNTLOC.CNCT=KCNT.CNCT AND KCNTLOC.CTCT=KCNT.CTCT AND KCNTLOC.LKLO=KCNT.LKLO)
AND ( KCNT.LKLO=KLOGEMT.LKLO AND KCNT.SCSO=KLOGEMT.SCSO )
AND ( KCPIND.CPIDF=KCPCPT.CPIDF AND KCPIND.SCSO=KCPCPT.SCSO)
AND ( KCPCPT.LKLO = KCNTLOC.LKLO)
AND (KLOGEMT.SCSO='PU')
AND (KLOGEMT.ATC IS NOT NULL)
AND (KCPIND.CODP >= '01/07/2005')
ORDER BY 1,2,5, 7, KCPIND.CODP DESC ) "DATA2"
WHERE ("DATA"."MODULE"="DATA2"."MODULE" AND "DATA"."CONTRAT"="DATA2"."CONTRAT")
AND (("DATA"."NUMLIGNE"+ 1) ="DATA2"."NUMLIGNE") )
||' - ' ||
(SELECT "DATA3"."INDEX RELEVE"
FROM (SELECT ROWNUM "NUMLIGNE", KCPCPT.LKLO "MODULE",
KCPIND.CNCT "CONTRAT",
KCNT.CNDCT "LOCATAIRE",
KCPCPT.CPIDF "NUM CPT",
DECODE(KCPCPT.TCTC,'F','F FROID','C','C CHAUD') "TYPE CPT",
DECODE(KCPIND.COACTIF,0,'HISTORIEE',1,'A TRAITER',KCPIND.COACTIF) "ETAT CONSO",
KCPIND.CODP "DATE RELEVE",
DECODE(NVL(KCPIND.CONM,0),0,KCPIND.CONI,KCPIND.CONM) "INDEX RELEVE",
(SELECT DECODE(NVL(KTARCPT.PKPR,1),1,(SELECT KTARCPT3.TATARIF FROM KTARCPT KTARCPT3 WHERE KTARCPT3.PKPR IS NULL),
KTARCPT.TATARIF) FROM KTARCPT
WHERE (KTARCPT.PKPR=KLOGEMT.PKPR OR KTARCPT.PKPR IS NULL)
AND (KTARCPT.TCTC=KCPCPT.TCTC)
AND (KTARCPT.ROWID = (SELECT MAX(KTARCPT2.ROWID) FROM KTARCPT KTARCPT2 WHERE (KTARCPT.PKPR=KTARCPT2.PKPR OR KTARCPT2.PKPR IS NULL) AND ( KTARCPT2.TCTC=KTARCPT.TCTC))
)
) "P.U",
DECODE (NVL(KCPIND.CONM,0),0,KCPIND.COCONSO,'')"CONSO"
FROM KCPIND, KCPCPT, KCNTLOC, KCNT, KLOGEMT
WHERE ( KCPIND.CNCT=KCNTLOC.CNCT AND KCPIND.CTCT=KCNTLOC.CTCT )
AND (KCNTLOC.CNCT=KCNT.CNCT AND KCNTLOC.CTCT=KCNT.CTCT AND KCNTLOC.LKLO=KCNT.LKLO)
AND ( KCNT.LKLO=KLOGEMT.LKLO AND KCNT.SCSO=KLOGEMT.SCSO )
AND ( KCPIND.CPIDF=KCPCPT.CPIDF AND KCPIND.SCSO=KCPCPT.SCSO)
AND ( KCPCPT.LKLO = KCNTLOC.LKLO)
AND (KLOGEMT.SCSO='PU')
AND (KLOGEMT.ATC IS NOT NULL)
AND (KCPIND.CODP >= '01/07/2005')
ORDER BY 1,2,5, 7, KCPIND.CODP DESC ) "DATA3"
WHERE ("DATA"."MODULE"="DATA3"."MODULE" AND "DATA"."CONTRAT"="DATA3"."CONTRAT")
AND ("DATA"."NUMLIGNE"="DATA3"."NUMLIGNE") ) "CONSO"
FROM (SELECT ROWNUM "NUMLIGNE", KCPCPT.LKLO "MODULE",
TO_CHAR(KCNTLOC.CDECL,'DD-MM-YYYY') "ENTREE",
TO_CHAR(KCNTLOC.CDSCL,'DD-MM-YYYY') "SORTIE",
KCPIND.CNCT "CONTRAT",
KCNT.CNDCT "LOCATAIRE",
KCPCPT.CPIDF "NUM CPT",
DECODE(KCPCPT.TCTC,'F','F FROID','C','C CHAUD') "TYPE CPT",
DECODE(KCPIND.COACTIF,0,'HISTORIEE',1,'A TRAITER',KCPIND.COACTIF) "ETAT CONSO",
KCPIND.CODP "DATE RELEVE",
DECODE(NVL(KCPIND.CONM,0),0,KCPIND.CONI,KCPIND.CONM) "INDEX RELEVE",
(SELECT DECODE(NVL(KTARCPT.PKPR,1),1,(SELECT KTARCPT3.TATARIF FROM KTARCPT KTARCPT3 WHERE KTARCPT3.PKPR IS NULL),
KTARCPT.TATARIF) FROM KTARCPT
WHERE (KTARCPT.PKPR=KLOGEMT.PKPR OR KTARCPT.PKPR IS NULL)
AND (KTARCPT.TCTC=KCPCPT.TCTC)
AND (KTARCPT.ROWID = (SELECT MAX(KTARCPT2.ROWID) FROM KTARCPT KTARCPT2 WHERE (KTARCPT.PKPR=KTARCPT2.PKPR OR KTARCPT2.PKPR IS NULL) AND ( KTARCPT2.TCTC=KTARCPT.TCTC))
)
) "P.U",
DECODE (NVL(KCPIND.CONM,0),0,KCPIND.COCONSO,'')"CONSO"
FROM KCPIND, KCPCPT, KCNTLOC, KCNT, KLOGEMT
WHERE ( KCPIND.CNCT=KCNTLOC.CNCT AND KCPIND.CTCT=KCNTLOC.CTCT )
AND (KCNTLOC.CNCT=KCNT.CNCT AND KCNTLOC.CTCT=KCNT.CTCT AND KCNTLOC.LKLO=KCNT.LKLO)
AND ( KCNT.LKLO=KLOGEMT.LKLO AND KCNT.SCSO=KLOGEMT.SCSO )
AND ( KCPIND.CPIDF=KCPCPT.CPIDF AND KCPIND.SCSO=KCPCPT.SCSO)
AND ( KCPCPT.LKLO = KCNTLOC.LKLO)
AND (KLOGEMT.SCSO='PU')
AND (KLOGEMT.ATC IS NOT NULL)
AND (KCPIND.CODP >= '01/07/2005')
ORDER BY 1,2,5, 7, KCPIND.CODP DESC ) "DATA"
WHERE "DATA"."DATE RELEVE" >= TO_DATE('2005-11-01','YYYY-MM-DD') |
Partager