Przesyłam. Jak jakieś nazwy będą niejasne to mogę opisać. Przykład dla jednego materiału.
SELECT DISTINCT DI.MATNR,
MARA.NAZWA_MATERIALU,
(CASE WHEN DI.KRAJ_ZAK ='X' THEN DI.CHARG ELSE NULL END) PARTIA,
(CASE WHEN DI.KRAJ_ZAK ='X' THEN STANY.BISMT ELSE NULL END) INDEKS,
DI.MEINS,
COUNT( DISTINCT DI.MAG_KONSYG) OVER (PARTITION BY DI.MATNR,(CASE WHEN DI.KRAJ_ZAK ='X' THEN STANY.BISMT ELSE NULL END)) ILMAT,
(CASE WHEN (CASE WHEN DI.KRAJ_ZAK ='X' THEN STANY.BISMT ELSE NULL END) IS NOT NULL THEN SUM(STANY.STAN_MAG_0) OVER(PARTITION BY STANY.MATNR,(CASE WHEN DI.KRAJ_ZAK ='X' THEN STANY.BISMT ELSE NULL END))
ELSE (max(STANY.STAN_0_MAT) over (PARTITION BY stany.matnr))
END) STAN_0
FROM OLAP_DANE.VKONSYG_DEFINICJE_INDEKSOW DI
LEFT OUTER JOIN
(
SELECT OLAP_DANE.MV_SAP_MARA.MATERIAL,OLAP_DANE.MV_SAP_MARA.NAZWA_GRUPY_ASORTYMENTOWEJ,OLAP_DANE.MV_SAP_MARA.GRUPA_ASORTYMENTOWA,OLAP_DANE.MV_SAP_MARA.PODGRUPA_ASORTYMENTOWA,
OLAP_DANE.MV_SAP_MARA.NAZWA_MATERIALU,OLAP_DANE.MV_SAP_MARA.OPIEKUN_ZAKUPY,OLAP_DANE.MV_SAP_MARA.MAABC
FROM OLAP_DANE.MV_SAP_MARA
)MARA
ON MARA.MATERIAL = DI.MATNR
LEFT OUTER JOIN
(
SELECT * FROM DWS1.SKLADY
)SKLADY
ON SKLADY.NUMER = DI.MAG_KONSYG
LEFT OUTER JOIN
(
SELECT DISTINCT MATNR, CHARG,BISMT,
SUM(CASE WHEN LGORT = '0' THEN QTY ELSE 0 END) STAN_MAG_0,
(CASE WHEN LGORT = '0' THEN sum(QTY) OVER (PARTITION BY MATNR,LGORT) ELSE 0 END) STAN_0_MAT,
SUM(CASE WHEN LGORT = '7' THEN QTY ELSE 0 END) STAN_MAG_7,
(CASE WHEN LGORT = '7' THEN sum(QTY) OVER (PARTITION BY MATNR,LGORT) ELSE 0 END) STAN_7_MAT,
SUM(CASE WHEN LGORT = '20' THEN QTY ELSE 0 END) STAN_MAG_20,
(CASE WHEN LGORT = '20' THEN sum(QTY) OVER (PARTITION BY MATNR,LGORT) ELSE 0 END) STAN_20_MAT,
SUM(CASE WHEN LGORT = '21' THEN QTY ELSE 0 END) STAN_MAG_21,
(CASE WHEN LGORT = '21' THEN sum(QTY) OVER (PARTITION BY MATNR,LGORT) ELSE 0 END) STAN_21_MAT,
SUM(CASE WHEN LGORT = '8' THEN QTY ELSE 0 END) STAN_MAG_8,
(CASE WHEN LGORT = '8' THEN sum(QTY) OVER (PARTITION BY MATNR,LGORT) ELSE 0 END) STAN_8_MAT
FROM OLAP_DANE.STAN_ZATP0
where matnr = '1403080BD9021K'
GROUP BY MATNR, CHARG,BISMT,LGORT,QTY
)STANY
ON STANY.MATNR = DI.MATNR AND STANY.CHARG = DI.CHARG
LEFT OUTER JOIN
(
select
zam.material,
zam.DATA_REALIZACJI,
zam.CENA_NABYCIA_PLN,
WALUTA,
SUM(zam.do_realizacji) Ilezam,
RANK () OVER (PARTITION BY zam.MATERIAL ORDER BY zam.DATA_REALIZACJI,zam.NR_ZAMOW,rowid) lp
FROM OLAP_DANE.MV_SAP_ZAMOW zam
WHERE zam.DATA_REALIZACJI >= SYSDATE -7
GROUP BY zam.material,
zam.DATA_REALIZACJI,
zam.CENA_NABYCIA_PLN,
WALUTA,
zam.NR_ZAMOW,rowid
having SUM(zam.do_realizacji) > 0
)ZAMOW
ON ZAMOW.MATERIAL = DI.MATNR
LEFT OUTER JOIN
(SELECT INDEKS_MARCOPOL INDEKS,SUM(STAN) STANKLIENTOW
FROM OLAP_DANE.VKONSYGNACJA_STANY
WHERE MAG_KONSYG NOT IN '2'
GROUP BY INDEKS_MARCOPOL) STANKONS
on STANKONS.INDEKS = STANY.BISMT
WHERE DI.MAG_KONSYG NOT IN '6'
AND DI.AKTYWNY IN 'X'
AND DI.MATNR = '1403080BD9021K'
GROUP BY
DI.MATNR,MARA.NAZWA_MATERIALU,MARA.GRUPA_ASORTYMENTOWA,MARA.NAZWA_GRUPY_ASORTYMENTOWEJ,MARA.PODGRUPA_ASORTYMENTOWA,MARA.OPIEKUN_ZAKUPY,MARA.MAABC,
DI.KRAJ_ZAK,
DI.STANMIN,DI.STANMAX,DI.CENA,DI.MAG_KONSYG, SKLADY.SKLAD,
(CASE WHEN DI.KRAJ_ZAK ='X' THEN DI.CHARG ELSE NULL END),
(CASE WHEN DI.KRAJ_ZAK ='X' THEN STANY.BISMT ELSE NULL END) ,
STANY.MATNR,
STANY.STAN_0_MAT,
DI.MEINS,
STANKONS.STANKLIENTOW,
STANY.STAN_MAG_0,STANY.STAN_MAG_7,STANY.STAN_MAG_20,STANY.STAN_MAG_21,STANY.STAN_MAG_8,STAN_0_MAT
ORDER BY 1 aSC