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
|
FUNCTION F_EXPORT_CONSO_VESSEL(pYear1 IN VARCHAR2,pMonth1 IN VARCHAR2,pVessel LO_VESSEL.VESSEL_uid%TYPE)
RETURN CURSOR_EXPORT
IS
cValuationReportMonth CURSOR_EXPORT;
v_SQLYEARMONTH varchar2(4000);
BEGIN
---- par Annnee
v_SQLYEARMONTH :='select
MONTHS_BETWEEN (LAST_DAY(TO_DATE ('':pYear1''||'':pMonth1'', ''YYMM'')), LAST_DAY(TO_DATE(va.month, ''YYMM'')))+1 as MYEAR
,sum(NVL(va.AMOUNT_USED,0)) AS AMOUNT_z01
,sum(NVL(va.QUANTITY_USED,0)) AS QUANTITY_z01
FROM
lo_vessel ve
,LO_VALUATION va
WHERE
(to_char(TO_DATE(va.month, ''YYMM''), ''YY'') = :pYear1 )
and va.VESSEL_UID=ve.VESSEL_UID
and ve.vessel_uid=:pVessel
GROUP BY TO_DATE(va.month, ''YYMM'')
ORDER BY TO_DATE(va.month, ''YYMM'')';
-- la boucle par mois
OPEN cValuationReportMonth FOR v_SQLYEARMONTH USING pYear1,pMonth1,pVessel;
RETURN cValuationReportMonth;
END F_EXPORT_CONSO_VESSEL; |
Partager