1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
| SELECT to_char(f.ms_date,'dd/mm/yyyy') "Date dispo", f.part_no "Reference",
s.catalog_desc "Designation",
SUM(f.forecast_lev1) "Somme Prevision glogale",
SUM(f.consumed_forecast) "Somme Cdes saisies"
FROM ((SELECT '01' TYPE_LIGNE, to_char(f.ms_date,'dd/mm/yyyy') "Date dispo",
f.part_no "Reference", s.catalog_desc "Designation"
FROM ifsapp.level_1_forecast f, ifsapp.sales_part s
WHERE f.part_no = s.part_no
AND f.forecast_lev1 <> 0
AND s.catalog_group like upper('&Famille%')
AND f.part_no like upper('&Reference%')
UNION ALL
SELECT ('02' TYPE_LIGNE, TO_CHAR(f.ms_date,'dd/mm/yyyy') "Mois dispo" ,
NULL, TOTAL POUR ' || to_char(f.ms_date,'mm/yyyy') "Mois dispo",
SUM(f.forecast_lev1) "Somme Prevision glogale",
SUM(f.consumed_forecast) "Somme Cdes saisies",
SUM(f.unconsumed_forecast) "Somme Prevision dispo"
FROM ifsapp.level_1_forecast f, ifsapp.sales_part s
WHERE f.part_no = s.part_no
AND f.forecast_lev1 <> 0
AND s.catalog_group like upper('&Famille%')
AND f.part_no like upper('&Reference%')
GROUP BY to_char(f.ms_date,'mm/yyyy') "Mois dispo", f.part_no, s.catalog_desc)))
ORDER BY "Reference", to_char(f.ms_date,'dd/mm/yyyy') |
Partager