CREATE OR REPLACE VIEW V_DELIVERYNOTELINE
(PACKINGNOTENUMBER, PRODUCTCODE, PRODUCTDESCRIPTION, PRODUCTGROUPDESCRIPTION, PGCUSTOMDESCRIPTION, PRODUCTCUSTOMCODE, PRODUCTCUSTOMDESCRIPTION, SIZECODE, SIZEDESCRIPTION, FINISHINGMETHODDESCRIPTION, PACKINGSEQUENCE, STANDARDAMOUNT, VARIATIONAMOUNT, DELIVEREDAMOUNT, TOBEDELIVEREDAMOUNT, REMARKFORPACKINGINSTRUCTIONS, USERNAME, TIMESTAMP, LANGUAGECODE, TYPEOFMERCHANDISE, FREEOFCHARGEREASON, FREEOFCHARGEREASON_DESC, NOCHARGEFIRSTISSUE, NOCHARGEACTIVESEASON, NOCHARGENOTCOG, LINE_ORIGIN, PRICE, AMOUNT, TAXGROUPCODE, REGUNIT, OLD_QTY, PRICECOG, SEQUENCENUMBER, FINISHINGMETHODCODE, NOCHARGEPREVSHORTAGE, NOCHARGEPREVREJECTED, PACKINGNOTELINE_ID, DELIVERYSCHEME, DELIVERYNOTELAYOUT)
AS
SELECT PACKINGNOTE.NOTENUMBER PACKINGNOTENUMBER, PRODUCT.CODE PRODUCTCODE,
       (SELECT DESCRIPTION
         FROM   PRODUCT_DESC P
         WHERE  P.PRODUCT_ID = PACKINGNOTELINE.PRODUCT_ID AND P.LANGUAGE_ID = LANGUAGES.LANGUAGE_ID)
PRODUCTDESCRIPTION,
       (SELECT DESCRIPTION
         FROM   PRODUCTGROUP_DESC A
         WHERE  A.PRODUCTGROUP_ID = PRODUCT.PRODUCTGROUP_ID AND A.LANGUAGE_ID = LANGUAGES.LANGUAGE_ID)
PRODUCTGROUPDESCRIPTION,
       (SELECT PGPC.CUSTOMDESCRITPION
         FROM   PRODUCTGROUPPERCUSTOMER PGPC, DISTRIBUTIONPOINT DP, DEPARTMENT DEP
         WHERE  PGPC.PRODUCTGROUP_ID = PRODUCT.PRODUCTGROUP_ID AND PGPC.CUSTOMER_ID = DEP.CUSTOMER_ID
                AND DEP.DEPARTMENT_ID = DP.DEPARTMENT_ID AND DP.DISTRIBUTIONPOINT_ID = PACKINGNOTE.DISTRIBUTIONPOINT_ID)
PGCUSTOMDESCRIPTION,
       PACKINGNOTELINE.CUSTOMCODE, PACKINGNOTELINE.CUSTOMDESCRIPTION, SIZEDEFINITION.CODE,
       (SELECT DESCRIPTION
         FROM   SIZEDEFINITION_DESC S
         WHERE  S.SIZEDEFINITION_ID = PACKINGNOTELINE.SIZEDEFINITION_ID AND
                S.LANGUAGE_ID = LANGUAGES.LANGUAGE_ID) SIZEDESCRIPTION,
       (SELECT DESCRIPTION
         FROM   FINISHINGMETHOD_DESC F
         WHERE  F.FINISHINGMETHOD_ID = PACKINGNOTELINE.FINISHINGMETHOD_ID AND
                F.LANGUAGE_ID = LANGUAGES.LANGUAGE_ID) FINISHINGMETHODDESCRIPTION,
       PACKINGNOTELINE.PACKINGSEQUENCE, PACKINGNOTELINE.STANDARDAMOUNT,
       PACKINGNOTELINE.VARIATIONAMOUNT, PACKINGNOTELINE.DELIVEREDAMOUNT,
       PACKINGNOTELINE.TOBEDELIVEREDAMOUNT, PACKINGNOTELINE.REMARKFORPACKINGINSTRUCTIONS,
       PACKINGNOTELINE.SYSTEMUSER_ID, PACKINGNOTELINE.TIMESTAMP, LANGUAGES.CODE,
       PRODUCT.TYPEOFMERCHANDISE,
       (SELECT R.CODE
         FROM   REASONCODE R
         WHERE  R.REASONCODE_ID = PACKINGNOTELINE.FREEOFCHARGEREASON_ID) FREEOFCHARGEREASON,
       (SELECT DESCRIPTION
         FROM   REASONCODE_DESC RD
         WHERE  RD.REASONCODE_ID = PACKINGNOTELINE.FREEOFCHARGEREASON_ID AND
                RD.LANGUAGE_ID = LANGUAGES.LANGUAGE_ID) FREEOFCHARGEREASON_DESC,
       PACKINGNOTELINE.NOCHARGEFIRSTISSUE, PACKINGNOTELINE.NOCHARGEACTIVESEASON,
       PACKINGNOTELINE.DELIVEREDCOGQTY, PACKINGNOTELINE.ORIGIN LINE_ORIGIN,
       (CASE WHEN PACKINGNOTELINE.PRICE IS NULL THEN 0 ELSE PACKINGNOTELINE.PRICE END) PRICE,
             PACKINGNOTELINE.AMOUNT, PACKINGNOTELINE.TAXGROUPCODE,
       (SELECT RG.CODE
         FROM   REGISTRATIONUNIT RG
         WHERE  RG.REGISTRATIONUNIT_ID = PRODUCT.REGISTRATIONUNIT_ID) REGUNIT,
       (CASE
          WHEN PACKINGNOTE.CORRECTEDNOTENUMBER = 0 THEN
           0
          ELSE
           (SELECT SUM(PAL.DELIVEREDAMOUNT)
            FROM   PACKINGNOTE PA, PACKINGNOTELINE PAL
            WHERE  PA.PACKINGNOTE_ID = PAL.PACKINGNOTE_ID AND
                   PAL.PRODUCT_ID = PACKINGNOTELINE.PRODUCT_ID AND
                   PAL.SIZEDEFINITION_ID = PACKINGNOTELINE.SIZEDEFINITION_ID AND
                   PAL.FINISHINGMETHOD_ID = PACKINGNOTELINE.FINISHINGMETHOD_ID AND
                   PA.NOTENUMBER = PACKINGNOTE.CORRECTEDNOTENUMBER)
        END) OLD_QTY,
         (CASE WHEN PACKINGNOTELINE.PRICECOG IS NULL THEN 0 ELSE PACKINGNOTELINE.PRICECOG END) PRICECOG,
   (SELECT MAX(PSZ.SEQUENCENUMBER) FROM PRODUCTSIZE PSZ WHERE PSZ.PRODUCT_ID = PRODUCT.PRODUCT_ID AND PSZ.SIZEDEFINITION_ID = SIZEDEFINITION.SIZEDEFINITION_ID) SEQUENCENUMBER
  , (SELECT CODE FROM FINISHINGMETHOD F
         WHERE  F.FINISHINGMETHOD_ID = PACKINGNOTELINE.FINISHINGMETHOD_ID) FINISHINGMETHODCODE
, (PACKINGNOTELINE.NOCHARGEPREVSHORTAGE + COALESCE((SELECT SUM (DV.AMOUNT) FROM DELIVERYVARIATIONS DV
WHERE DV.CAUSENOTE_ID = PACKINGNOTELINE.PACKINGNOTE_ID AND DV.PRODUCT_ID = PACKINGNOTELINE.PRODUCT_ID
AND DV.SIZEDEFINITION_ID = PACKINGNOTELINE.SIZEDEFINITION_ID AND DV.FINISHINGMETHOD_ID = PACKINGNOTELINE.FINISHINGMETHOD_ID
AND DV.ORIGIN = 1 AND DV.SOILCOUNTSUBTYPE = 2), 0))  NOCHARGEPREVSHORTAGE
, (PACKINGNOTELINE.NOCHARGEPREVREJECTED + COALESCE((SELECT SUM (DV.AMOUNT) FROM DELIVERYVARIATIONS DV
WHERE DV.CAUSENOTE_ID = PACKINGNOTELINE.PACKINGNOTE_ID AND DV.PRODUCT_ID = PACKINGNOTELINE.PRODUCT_ID
AND DV.SIZEDEFINITION_ID = PACKINGNOTELINE.SIZEDEFINITION_ID AND DV.FINISHINGMETHOD_ID = PACKINGNOTELINE.FINISHINGMETHOD_ID
AND DV.ORIGIN = 1 AND DV.SOILCOUNTSUBTYPE = 3), 0)) NOCHARGEPREVREJECTED, PACKINGNOTELINE.PACKINGNOTELINE_ID,
nvl((SELECT MIN(DS.CODE) FROM PRODUCTLISTPERDISTRIBPOINT PLPD
        JOIN STANDARDDELIVERYLINE SDL ON PLPD.PRODUCTLISTPERDISTRIBPOINT_ID = SDL.PRODUCTLISTPERDISTRIBPOINT_ID
        JOIN DELIVERYSCHEME DS ON SDL.DELIVERYSCHEME_ID = DS.DELIVERYSCHEME_ID
        JOIN SEASONWEEK SW ON SW.SEASON_ID = SDL.SEASON_ID
        JOIN STANDARDDELIVERYAMOUNT SDA ON SDA.STANDARDDELIVERYLINE_ID = SDL.STANDARDDELIVERYLINE_ID
        WHERE PLPD.DISTRIBUTIONPOINT_ID = PACKINGNOTE.DISTRIBUTIONPOINT_ID
        AND PLPD.PRODUCT_ID = PACKINGNOTELINE.PRODUCT_ID
        AND PLPD.SIZEDEFINITION_ID = PACKINGNOTELINE.SIZEDEFINITION_ID
        AND PLPD.FINISHINGMETHOD_ID = PACKINGNOTELINE.FINISHINGMETHOD_ID
        AND SDL.DATEACTIVE < PACKINGNOTE.DELIVERYDATE
        AND SDL.DATEINACTIVE > PACKINGNOTE.DELIVERYDATE
         AND SW.WEEKNUMBER = GETISOWEEK(PACKINGNOTE.DELIVERYDATE)
        AND SDA.DAY_ID = GETDAYNUMBER(PACKINGNOTE.DELIVERYDATE)),
        (SELECT min(DS.CODE) FROM PRODUCTLISTPERDISTRIBPOINT PLPD
        JOIN STANDARDDELIVERYLINE SDL ON PLPD.PRODUCTLISTPERDISTRIBPOINT_ID = SDL.PRODUCTLISTPERDISTRIBPOINT_ID
        LEFT OUTER JOIN DELIVERYSCHEME DS ON SDL.DELIVERYSCHEME_ID = DS.DELIVERYSCHEME_ID
        WHERE PLPD.DISTRIBUTIONPOINT_ID = PACKINGNOTE.DISTRIBUTIONPOINT_ID
        AND PLPD.PRODUCT_ID = PACKINGNOTELINE.PRODUCT_ID
        AND PLPD.SIZEDEFINITION_ID = PACKINGNOTELINE.SIZEDEFINITION_ID
        AND PLPD.FINISHINGMETHOD_ID = PACKINGNOTELINE.FINISHINGMETHOD_ID
        AND SDL.DATEACTIVE < PACKINGNOTE.DELIVERYDATE
        AND SDL.DATEINACTIVE > PACKINGNOTE.DELIVERYDATE)) DELIVERYSCHEME, 
(SELECT FL.CODE
         FROM FORMLAYOUT FL, CUSTOMER C, DISTRIBUTIONPOINT DP, DEPARTMENT DEP
         WHERE  FL.FORMLAYOUT_ID = C.DELIVERYNOTELAYOUT_ID AND C.CUSTOMER_ID = DEP.CUSTOMER_ID
                AND DEP.DEPARTMENT_ID = DP.DEPARTMENT_ID AND DP.DISTRIBUTIONPOINT_ID = PACKINGNOTE.DISTRIBUTIONPOINT_ID) DELIVERYNOTELAYOUT       
FROM   PACKINGNOTELINE, PACKINGNOTE, PRODUCT, SIZEDEFINITION, LANGUAGES
WHERE  PACKINGNOTELINE.PACKINGNOTE_ID = PACKINGNOTE.PACKINGNOTE_ID AND
       PACKINGNOTELINE.PRODUCT_ID = PRODUCT.PRODUCT_ID AND
       PACKINGNOTELINE.SIZEDEFINITION_ID = SIZEDEFINITION.SIZEDEFINITION_ID;
/
