SELECT LU.DELIVERY_ID "Nr dostawy",LU.DATE_Z "Data zak. real",LU.LOADUNITS "Ilość nośników",K.KARTONY "Ilość kartonów",LU.FIRM_NR FROM

(SELECT PCG.DELIVERY_ID,SUM(PCG.KARTON) AS KARTONY FROM

(SELECT DI.DELIVERY_ID,DI.PACKAGE_ID,SUM(DI.BU_QUANTITY) AS SZTUKA,CEIL(SUM(DI.BU_QUANTITY)/PL.BU_QUANTITY) AS KARTON ,PL.BU_QUANTITY

FROM DELIVERY_ITEMS DI LEFT JOIN QCM_PACKAGE_LEVELS PL ON DI.PACKAGE_ID=PL.PACKAGE_ID

WHERE PL.LEVEL_NR='1' AND DI.STATUS='FI'

GROUP BY DI.DELIVERY_ID,PL.LEVEL_NR,DI.PACKAGE_ID,PL.BU_QUANTITY)PCG

GROUP BY PCG.DELIVERY_ID)K

LEFT JOIN

(SELECT TO_CHAR(D.LM_DATE,'YYYY-MM-DD') AS DATE_Z,DI.DELIVERY_ID,COUNT(DI.LOADUNIT_ID) AS LOADUNITS,F.FIRM_NR

FROM (SELECT DISTINCT DELIVERY_ID,LOADUNIT_ID FROM DELIVERY_ITEMS) DI LEFT JOIN DELIVERIES D ON DI.DELIVERY_ID=D.DELIVERY_ID LEFT JOIN QCM_FIRMS F ON D.FIRM_ID=F.FIRM_ID

WHERE D.STATUS='FI' GROUP BY TO_CHAR(D.LM_DATE,'YYYY-MM-DD'),DI.DELIVERY_ID,D.STATUS,F.FIRM_NR) LU

ON K.DELIVERY_ID=LU.DELIVERY_ID