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 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142
| SELECT
'f' TYPELIGNE,
imp.cdprj CDPRJ,
imp.CDWPK CDWPK,
plg.lidep LIDEP,
plg.cdmat CDMAT,
plg.cdposte CDPOSTE,
PLG.TYRESS TYRESS,
plq.dtmoisco DTMOISCO,
PLG.TYLIGNE TYLIGNE,
DECODE (HWP.INVALSIM, '0', 'Non Valide', '1', 'Valide', '2',
'Clos', '3', 'Stand By', 'Non valide') INVALSIM,
DECODE (HWP.INVALWPM, '0', 'Non Valide', '1', 'Valide', 'Non valide') INVALWPM,
fixed(sum(plq.qtprev),6,2) QtPREV
FROM FF_PLG PLG, FD_IMP IMP, FF_PLQ PLQ, FD_WPK WPK, FF_HWP HWP, FD_TRG TRG
Where
PLG.CDORG = '".$VALEUR_CDORG."'
AND IMP.CDORG = PLG.CDORG
AND IMP.TYIMPU = PLG.TYIMPU
AND IMP.CDIMPU = PLG.CDIMPU
AND PLQ.CDORG = PLG.CDORG
AND PLQ.TYIMPU = PLG.TYIMPU
AND PLQ.CDIMPU = PLG.CDIMPU
AND PLQ.NULIGNE = PLG.NULIGNE
AND PLQ.DTMOISCO > ".$MOISCOD."
AND WPK.CDPRJ = IMP.CDPRJ
AND WPK.CDWPK = IMP.CDWPK
AND HWP.CDPRJ (+) = WPK.CDPRJ
AND HWP.CDWPK (+) = WPK.CDWPK
AND (HWP.CDPRJ IS NULL
OR HWP.DTMOISDHD = ".$MOISCOD." )
AND TRG.CDORG (+) = PLG.CDORG
AND TRG.CDPOSTE (+) = PLG.CDPOSTE
".$WHERE_RESTRICTION."
GROUP BY
IMP.CDPRJ,
IMP.CDWPK,
PLG.LIDEP,
PLG.CDMAT,
PLG.CDPOSTE,
PLG.TYRESS,
PLG.TYLIGNE,
PLQ.DTMOISCO,
DECODE (HWP.INVALSIM, '0', 'Non Valide', '1', 'Valide', '2',
'Clos', '3', 'Stand By', 'Non valide'),
DECODE (HWP.INVALWPM, '0', 'Non Valide', '1', 'Valide', 'Non valide')
UNION
SELECT
'd' TYPELIGNE,
NVL(TRIM(IMP.CDPRJ),DECODE(TRIM(IMP.CDWPK),NULL,'ABSENCES',NULL)) CDPRJ,
NVL(TRIM(IMP.CDWPK),DECODE(TRIM(IMP.CDPRJ),NULL,'ABSENCES',NULL)) CDWPK,
max(DWL.LIMATC) as LIMATC ,
DWL.CDMAT,
NULL,
NULL,
DWL.DTMOISR ,
NULL,
DECODE (HWP.INVALSIM, '0', 'Non Valide', '1', 'Valide', '2',
'Clos', '3', 'Stand By', 'Non valide') INVALSIM,
DECODE (HWP.INVALWPM, '0', 'Non Valide', '1', 'Valide', 'Non valide') INVALWPM,
sum(DWL.QTHDEPCT )/150 as QTHDEPCT
FROM ff_DWL DWL,FD_IMP IMP, FF_HWP HWP
WHERE
DWL.CDORG='".$VALEUR_CDORG."'
AND DWL.CDMAT IN
(SELECT PLG.CDMAT
FROM FF_PLG PLG, FD_IMP IMP, FF_PLQ PLQ, FD_WPK WPK, FF_HWP HWP, FD_TRG TRG
Where
PLG.CDORG = '".$VALEUR_CDORG."'
AND IMP.CDORG = PLG.CDORG
AND IMP.TYIMPU = PLG.TYIMPU
AND IMP.CDIMPU = PLG.CDIMPU
AND PLQ.CDORG = PLG.CDORG
AND PLQ.TYIMPU = PLG.TYIMPU
AND PLQ.CDIMPU = PLG.CDIMPU
AND PLQ.NULIGNE = PLG.NULIGNE
AND PLQ.DTMOISCO > ".$MOISCOD."
AND WPK.CDPRJ = IMP.CDPRJ
AND WPK.CDWPK = IMP.CDWPK
AND HWP.CDPRJ (+) = WPK.CDPRJ
AND HWP.CDWPK (+) = WPK.CDWPK
AND (HWP.CDPRJ IS NULL
OR HWP.DTMOISDHD = ".$MOISCOD." )
AND TRG.CDORG (+) = PLG.CDORG
AND TRG.CDPOSTE (+) = PLG.CDPOSTE
".$WHERE_RESTRICTION.")
AND DWL.DTMOISR >= ".$MOISDEBDEP."
AND DWL.DTMOISR <= ".$MOISFINDEP."
AND IMP.CDORG = DWL.CDORG
AND IMP.CDIMPU = DWL.CDIMPU
AND IMP.CDPRJ = HWP.CDPRJ (+)
AND IMP.CDWPK = HWP.CDWPK (+)
AND (HWP.CDPRJ IS NULL OR HWP.DTMOISDHD = ".$MOISCOD." )
group by NVL(TRIM(IMP.CDPRJ),DECODE(TRIM(IMP.CDWPK),NULL,'ABSENCES',NULL)),
NVL(TRIM(IMP.CDWPK),DECODE(TRIM(IMP.CDPRJ),NULL,'ABSENCES',NULL)),DWL.CDMAT,DWL.DTMOISR,
DECODE (HWP.INVALSIM, '0', 'Non Valide', '1', 'Valide', '2',
'Clos', '3', 'Stand By', 'Non valide'),
DECODE (HWP.INVALWPM, '0', 'Non Valide', '1', 'Valide', 'Non valide')
UNION
SELECT
'c' TYPELIGNE,
'CONGES' CDPRJ,
'CONGES' CDWPK,
MAT.CDTITRE || ' ' || MAT.NMPMAT || ' ' || MAT.NMMAT LIDEP,
rec.cdmat CDMAT,
NULL,
NULL,
rec.dtmois DTMOISCO,
NULL,
NULL,
NULL,
fixed(sum(rec.qtmois),5,3)/150 QtPREV
FROM FD_REC REC, HD_MAT MAT
WHERE
REC.CDORG = '".$VALEUR_CDORG."'
AND REC.CDMAT IN
(SELECT PLG.CDMAT
FROM FF_PLG PLG, FD_IMP IMP, FF_PLQ PLQ, FD_WPK WPK, FF_HWP HWP, FD_TRG TRG
Where
PLG.CDORG = '".$VALEUR_CDORG."'
AND IMP.CDORG = PLG.CDORG
AND IMP.TYIMPU = PLG.TYIMPU
AND IMP.CDIMPU = PLG.CDIMPU
AND PLQ.CDORG = PLG.CDORG
AND PLQ.TYIMPU = PLG.TYIMPU
AND PLQ.CDIMPU = PLG.CDIMPU
AND PLQ.NULIGNE = PLG.NULIGNE
AND PLQ.DTMOISCO > ".$MOISCOD."
AND WPK.CDPRJ = IMP.CDPRJ
AND WPK.CDWPK = IMP.CDWPK
AND HWP.CDPRJ (+) = WPK.CDPRJ
AND HWP.CDWPK (+) = WPK.CDWPK
AND (HWP.CDPRJ IS NULL
OR HWP.DTMOISDHD = ".$MOISCOD." )
AND TRG.CDORG (+) = PLG.CDORG
AND TRG.CDPOSTE (+) = PLG.CDPOSTE
".$WHERE_RESTRICTION.")
AND REC.DTMOIS >= ".$MOISDEBUTAFFICHAGE."
and MAT.CDORG = REC.CDORG
AND MAT.CDMAT = REC.CDMAT
GROUP BY REC.CDMAT, REC.DTMOIS, MAT.CDTITRE || ' ' || MAT.NMPMAT || ' ' || MAT.NMMAT
order by 5,2,3,6,1 desc |
Partager