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
| With PVDTIT_AGG AS
(
SELECT TITIDT
, MAX(CASE WHEN PVDIDT = 2 THEN PVDTITCOD END) AS PVDTITCOD_EQ_2
, MAX(CASE WHEN PVDIDT <> 2 THEN PVDTITCOD END) AS PVDTITCOD_DF_2
FROM PVDTIT
GROUP BY TITIDT
)
SELECT DISTINCT
sRef.TITIDT AS TITRefIDT
, sCpt.TITIDT AS TITCptIDT
, coalesce(aRef.RefREFEXT, tRef.RefCOD2, ptRef.PVDTITCOD) AS CODRef
, ptCpt.PVDTITCOD AS CODCpt
, ptRef.PVDTITLIB AS LIBRef
, ptCpt.PVDTITLIB AS LIBCpt
, r.DATVL
, crsRef.CRSVLO AS CRSRef
, crsCpt.CRSVLO AS CRSCpt
, sRef.PLACOTCOD AS PLACOTRef
, sCpt.PLACOTCOD AS PLACOTCpt
FROM RPM r
INNER JOIN LIGRPM lr
ON lr.RPMIDT = r.RPMIDT
LEFT OUTER JOIN LIGRPMINVLIG lrl
ON lrl.LIGRPMIDT = lr.LIGRPMIDT
LEFT OUTER JOIN INVLIG ilRef
ON ilRef.INVLIGIDT = lrl.INVLIGIDT
AND ilRef.PVDIDT = 2
AND ilRef.FLGLIQ = 'F'
LEFT OUTER JOIN STK sRef
ON sRef.INVLIGIDT = ilRef.INVLIGIDT
LEFT OUTER JOIN PVDTIT_AGG ptaRef
ON ptaRef.TITIDT = = sRef.TITIDT
LEFT OUTER JOIN PVDTIT ptRef
ON ptRef.TITIDT = ptaRef.TITIDT
AND ptRef.PVDIDT = 2
AND ptRef.PVDTITCOD = ptaRef.PVDTITCOD_EQ_2
LEFT OUTER JOIN RefTIT tRef
ON tRef.TITIDT = sRef.TITIDT
AND tRef.STALIGIDT IN (SELECT st.STALIGIDT
FROM STALIG st
WHERE st.PVDIDT = r.PVD_PVDIDT
AND st.STALIGCOD = case when sRef.QTETIT > 0 then 'RECU' else 'VERSE' end)
LEFT OUTER JOIN RefREF aRef
ON aRef.TITIDT = tRef.TITIDT
AND aRef.RefTITIDT = tRef.RefTITIDT
AND aRef.PERTPSDATDEB = (SELECT max(t1.PERTPSDATDEB)
FROM RefREF t1
WHERE t1.PVDIDT <> 2
AND t1.RefTITIDT = aRef.RefTITIDT
AND t1.PERTPSDATDEB <= R.DATVL)
LEFT OUTER JOIN CRS crsRef
ON crsRef.CRSIDT = sRef.CRSIDT
LEFT OUTER JOIN INVLIG ilCpt
ON ilCpt.INVLIGIDT = lrl.INVLIGIDT
AND ilCpt.PVDIDT = r.PVD_PVDIDT
AND ilCpt.FLGLIQ = 'F'
LEFT OUTER JOIN STK sCpt
ON sCpt.INVLIGIDT = ilCpt.INVLIGIDT
LEFT OUTER JOIN PVDTIT_AGG ptasCpt
ON ptasCpt.TITIDT = = sCpt.TITIDT
LEFT OUTER JOIN PVDTIT ptCpt
ON ptCpt.TITIDT = ptasCpt.TITIDT
AND ptCpt.PVDIDT = ilCpt.PVDIDT
AND ptCpt.PVDTITCOD = ptasCpt.PVDTITCOD_DF_2
LEFT OUTER JOIN CRS crsCpt
ON crsCpt.CRSIDT = sCpt.CRSIDT
WHERE r.DATVL = to_date('26/12/2011', 'DD/MM/YYYY')
AND (crsRef.CRSVLO IS NOT NULL
OR crsCpt.CRSVLO IS NOT NULL)
ORDER BY ptRef.PVDTITLIB ASC; |
Partager