CREATE OR REPLACE VIEW V_QTYREJECTED AS
SELECT 
    CN.CONSIGNMENTNOTE_ID, PRD.CODE PRODUCTCODE
  , (SELECT PD.DESCRIPTION FROM PRODUCT_DESC PD WHERE PD.PRODUCT_ID = PRD.PRODUCT_ID
    AND PD.LANGUAGE_ID = LANGUAGES.LANGUAGE_ID) PRODUCTDESCRIPTION
  , LANGUAGES.CODE LANGUAGECODE
  , SUM(DV.AMOUNT) REJECTEDQTY
FROM
  CONSIGNMENTNOTE CN, PRODUCT PRD, LANGUAGES, DELIVERYVARIATIONS DV, PACKINGNOTE PN
WHERE
    CN.CONSIGNMENTNOTE_ID = PN.CONSIGNMENTNOTE_ID
    AND DV.PACKINGNOTE_ID = PN.PACKINGNOTE_ID
    AND DV.PRODUCT_ID = PRD.PRODUCT_ID
    AND DV.SOILCOUNTSUBTYPE = 3
    AND DV.ORIGIN = 1
GROUP BY CN.CONSIGNMENTNOTENUMBER, CN.CONSIGNMENTNOTE_ID
, PRD.CODE, PRD.PRODUCT_ID, LANGUAGES.LANGUAGE_ID, LANGUAGES.CODE;
/