CREATE OR REPLACE VIEW V_PACKINGNOTELINE AS
SELECT PACKINGNOTELINE.PACKINGNOTE_ID PACKINGNOTEID,--PACKINGNOTE.NOTENUMBER,
  PRODUCTGROUP.CODE PRODUCTGROUPCODE, PRODUCT.CODE PRODUCTCODE,
  (SELECT P.DESCRIPTION FROM PRODUCT_DESC P WHERE P.PRODUCT_ID = PACKINGNOTELINE.PRODUCT_ID
     AND P.LANGUAGE_ID = LANGUAGES.LANGUAGE_ID) PRODUCTDESCRIPTION, 
  ACTIVITY.CODE ACTIVITYCODE,
  (SELECT AD.DESCRIPTION FROM ACTIVITY_DESC AD WHERE AD.ACTIVITY_ID = ACTIVITY.ACTIVITY_ID
     AND AD.LANGUAGE_ID = LANGUAGES.LANGUAGE_ID)  ACTIVITYDESCRIPTION,
  PACKINGNOTELINE.CUSTOMCODE PRODUCTCUSTOMCODE, PACKINGNOTELINE.CUSTOMDESCRIPTION PRODUCTCUSTOMDESCRIPTION,
  SIZEDEFINITION.CODE SIZECODE,
  (SELECT S.DESCRIPTION FROM SIZEDEFINITION_DESC S WHERE S.SIZEDEFINITION_ID = PACKINGNOTELINE.SIZEDEFINITION_ID
     AND S.LANGUAGE_ID = LANGUAGES.LANGUAGE_ID)  SIZEDESCRIPTION,
  (SELECT F.DESCRIPTION FROM FINISHINGMETHOD_DESC F WHERE F.FINISHINGMETHOD_ID = PACKINGNOTELINE.FINISHINGMETHOD_ID
     AND F.LANGUAGE_ID = LANGUAGES.LANGUAGE_ID)  FINISHINGMETHODDESCRIPTION,
  FINISHINGMETHOD.CODE FINISHINGMETHODCODE, PACKINGNOTELINE.PACKINGSEQUENCE, PRODUCT.PACKINGAMOUNT,
  CASE WHEN PSZ.THEORETICALWEIGHT = 0 THEN PRODUCT.THEORETICALWEIGHT
	 ELSE PSZ.THEORETICALWEIGHT END THEORETICALWEIGHT, PACKINGNOTELINE.STANDARDAMOUNT, PACKINGNOTELINE.VARIATIONAMOUNT,
  PACKINGNOTELINE.DELIVEREDAMOUNT, PACKINGNOTELINE.TOBEDELIVEREDAMOUNT, PACKINGNOTELINE.NODELIVERYAMOUNT,
  (CASE WHEN PACKINGNOTELINE.PRICE IS NULL THEN 0 ELSE PACKINGNOTELINE.PRICE END) PRICE, PACKINGNOTELINE.DISCOUNT, PACKINGNOTELINE.LOCATION,
  PACKINGNOTELINE.REMARKFORPACKINGINSTRUCTIONS, PACKINGNOTELINE.SYSTEMUSER_ID, PACKINGNOTELINE.TIMESTAMP,
  CASE WHEN PACKINGNOTELINE.PUTUPTYPE = 2 THEN 'Y' ELSE 'N' END BULKPUTUP, PACKINGNOTELINE.PUTUPTYPE,
  PACKINGNOTELINE.PACKINGNOTELINE_ID, PSZ.PACKINGAMOUNT PACKINGAMOUNTPSZ,
  PACKINGNOTELINE.BUNDLEQTY, PRODUCT.PRODUCT_ID, PRODUCT.PACK ISPACK,
  (CASE WHEN PACKINGNOTELINE.PRICECOG IS NULL THEN 0 ELSE PACKINGNOTELINE.PRICECOG END) PRICECOG, 
  PSZ.SEQUENCENUMBER, PACKINGNOTELINE.NOCHARGEPREVSHORTAGE, PACKINGNOTELINE.NOCHARGEPREVREJECTED, LANGUAGES.CODE LANGUAGECODE,  
  PACKINGNOTELINE.REPLACEMENTQTY, PACKINGNOTELINE.REPLACEMENTLOSSQTY, PRODUCT.PACKINGSEQUENCE PROD_PACKINGSEQUENCE, 
  coalesce((select PPS.Packinggroup from Plantproductsettings PPS, PACKINGNOTE PN, businessunit bu
  where pn.packingnote_id = PACKINGNOTELINE.Packingnote_Id and bu.businessunit_id = pn.processbusinessunit_id
  and pps.plant_id = bu.plant_id and pps.product_id = product.product_id), PRODUCT.PACKINGGROUP) PACKINGGROUP
FROM PACKINGNOTELINE,-- PACKINGNOTE,
     PRODUCT, PRODUCTGROUP, ACTIVITY, SIZEDEFINITION, FINISHINGMETHOD, PRODUCTSIZE PSZ, LANGUAGES
WHERE PACKINGNOTELINE.PRODUCT_ID = PRODUCT.PRODUCT_ID
  AND PACKINGNOTELINE.SIZEDEFINITION_ID = SIZEDEFINITION.SIZEDEFINITION_ID
  AND PRODUCTGROUP.PRODUCTGROUP_ID = PRODUCT.PRODUCTGROUP_ID
  AND FINISHINGMETHOD.FINISHINGMETHOD_ID = PACKINGNOTELINE.FINISHINGMETHOD_ID
      AND PRODUCT.PRODUCT_ID = PSZ.PRODUCT_ID
          AND PSZ.SIZEDEFINITION_ID = SIZEDEFINITION.SIZEDEFINITION_ID
  AND PRODUCTGROUP.ACTIVITY_ID = ACTIVITY.ACTIVITY_ID
;
/