CREATE OR REPLACE VIEW V_DELIVEREDPRODUCTS AS
SELECT RP.REPORTPARAM_ID, PROCESSBU.BUSINESSUNIT_ID PROCESSBU_ID,
       CUSTOMER_LINK.CUSTOMERNUMBER, CUSTOMER_LINK.NAME customername,
       DEPARTMENT_LINK.DEPARTMENTNUMBER, DEPARTMENT_LINK.DESCRIPTION departmentdescription,
       DISTRIBUTIONPOINT_LINK.DISTRIBUTIONPOINTNUMBER, DISTRIBUTIONPOINT_LINK.DESCRIPTION distributionpointdescription,
       PRODUCTGROUP.CODE productgroupcode,
       (SELECT A.DESCRIPTION FROM PRODUCTGROUP_DESC A WHERE A.PRODUCTGROUP_ID = PRODUCTGROUP.PRODUCTGROUP_ID
                AND A.LANGUAGE_ID = LANGUAGES.LANGUAGE_ID) productgroupdescription,
       PRODUCT_LINK.CODE productcode,
       (SELECT B.DESCRIPTION FROM PRODUCT_DESC B WHERE B.PRODUCT_ID = DELIVERYHISTORY.PRODUCT_LINK_ID AND B.LANGUAGE_ID = LANGUAGES.LANGUAGE_ID) productdescription,
       parentproduct.CODE parentproductcode,
       (SELECT E.DESCRIPTION FROM PRODUCT_LINK_DESC E WHERE E.PRODUCT_LINK_ID = DELIVERYHISTORY.PARENTPRODUCT_LINK_ID AND E.LANGUAGE_ID = LANGUAGES.LANGUAGE_ID) parentproductdescription,
       DELIVERYHISTORY.PACKINDICATION,
       pack.CODE packcode,
       (SELECT F.DESCRIPTION FROM PRODUCT_LINK_DESC F WHERE F.PRODUCT_LINK_ID = DELIVERYHISTORY.PARENTPACKPRODUCT_LINK_ID AND F.LANGUAGE_ID = LANGUAGES.LANGUAGE_ID) packdescription,
       (SELECT F.CODE FROM ACTIVITY F WHERE F.ACTIVITY_ID = PRODUCTGROUP.ACTIVITY_ID) ACTIVITYCODE,
       PRODUCT.THEORETICALWEIGHT, PRODUCT.typeofmerchandise, PRODUCT.MODEL,
       PRODUCTSIZE.THEORETICALWEIGHT PRODSIZE_THEORETICALWEIGHT,
       (select plpc.soilweight from productlistpercustomer plpc where PLPC.CUSTOMER_ID=CUSTOMER_LINK.CUSTOMER_LINK_ID AND plpc.product_id=product_link.product_link_id
       and plpc.sizedefinition_id=SIZEDEFINITION_LINK.Sizedefinition_Id and plpc.finishingmethod_id=FINISHINGMETHOD.Finishingmethod_Id
       AND PLPC.DATEINACTIVE >=SYSDATE
       ) theoreticalweight_plpc,
       SIZEDEFINITION_LINK.CODE sizecode,
       (SELECT C.DESCRIPTION FROM SIZEDEFINITION_LINK_DESC C WHERE C.SIZEDEFINITION_LINK_ID = DELIVERYHISTORY.SIZEDEFINITION_LINK_ID AND C.LANGUAGE_ID = LANGUAGES.LANGUAGE_ID) SIZEdescription,
       FINISHINGMETHOD.CODE finishingmethodcode,
       (SELECT D.DESCRIPTION FROM FINISHINGMETHOD_DESC D WHERE D.FINISHINGMETHOD_ID = DELIVERYHISTORY.FINISHINGMETHOD_ID AND D.LANGUAGE_ID = LANGUAGES.LANGUAGE_ID) FINISHINGMETHODdescription,
       DELIVERYHISTORY.DELIVERYDATE, DELIVERYHISTORY.year*100 + DELIVERYHISTORY.week YEARWEEK,
       DELIVERYHISTORY.WEEK, DELIVERYHISTORY.YEAR,
       month(DELIVERYHISTORY.deliverydate) realmonth,
       year(DELIVERYHISTORY.deliverydate) realyear,
       DELIVERYHISTORY.DAYNUMBER,
       DELIVERYHISTORY.AMOUNTORDERED, DELIVERYHISTORY.AMOUNTDELIVERED,
       DELIVERYHISTORY.AMOUNTSHORTAGE, --DELIVERYHISTORY.PRICE,
       DELIVERYHISTORY.INTERNALPRICE, DELIVERYHISTORY.DISCOUNT,
       DELIVERYHISTORY.PARENTPACKPRODUCT_LINK_ID, DELIVERYHISTORY.PARENTPRODUCT_LINK_ID,
       PROCESSBU.CODE processbucode, PROCESSBU.DESCRIPTION processbudescription, INVOICEBU.CODE invoicebucode,
       INVOICEBU.DESCRIPTION invoicebudescription, DELIVERYHISTORY.SYSTEMUSER_ID, DELIVERYHISTORY.TIMESTAMP,
       F_GETDELPRODPRICE(rp.number_value, cu.customer_id, product.product_id, SIZEDEFINITION_LINK.SIZEDEFINITION_ID,
         FINISHINGMETHOD.FINISHINGMETHOD_ID, rp.date_value1/*deliveryhistory.deliverydate*/,product.typeofmerchandise) price,
       F_GETVAT(rp.number_value, (CASE WHEN cu.taxable = 'Y' then cu.customer_id ELSE null END)
       , product.product_id, /*rp.number_value1*/ (CASE WHEN rp.number_value1 = -1 then cu.country_id ELSE rp.number_value1  END), cu.country_id, rp.date_value1/*deliveryhistory.deliverydate*/) vatrate,
       cur.decimals, cur.invoiceamountdecimals,
       cu.taxable, bu.businessunit_id defaultbusinessunit_id,
       CUSTOMER_LINK.LANGUAGECODE CUSTOMERLANGUAGECODE, LANGUAGES.CODE LANGUAGECODE,
       F_GETDELPRODPRICEWITHDISC(cu.customer_id, product.product_id, SIZEDEFINITION_LINK.SIZEDEFINITION_ID,
         FINISHINGMETHOD.FINISHINGMETHOD_ID, deliveryhistory.deliverydate, product.typeofmerchandise) PRICEWITHDISCOUNT,
       pack_price.Wash_Discount(cu.customer_id, product.product_id, SIZEDEFINITION_LINK.SIZEDEFINITION_ID,
         FINISHINGMETHOD.FINISHINGMETHOD_ID,deliveryhistory.deliverydate) DISCOUNTPERCENTAGE
         , rp.number_value q , cu.customer_id  w, product.product_id e, SIZEDEFINITION_LINK.SIZEDEFINITION_ID r,
         FINISHINGMETHOD.FINISHINGMETHOD_ID t, deliveryhistory.deliverydate y ,product.typeofmerchandise u
, pl.code Plant_Code, pl.name Plant_Name,
SIZEDEFINITION.WIDTH WIDTH_S,SIZEDEFINITION.LENGTH LENGTH_S--CSW
 FROM DELIVERYHISTORY
        LEFT OUTER JOIN PRODUCT ON (DELIVERYHISTORY.PRODUCT_LINK_ID = PRODUCT.PRODUCT_ID)
        LEFT OUTER JOIN SIZEDEFINITION ON (DELIVERYHISTORY.SIZEDEFINITION_LINK_ID = SIZEDEFINITION.SIZEDEFINITION_ID),
      PRODUCTSIZE,
      CUSTOMER_LINK
        left outer join customer cu          --left join country ctr on cu.country_id = ctr.country_id)
        on cu.customer_id = CUSTOMER_LINK.CUSTOMER_ID,
      DEPARTMENT_LINK, DISTRIBUTIONPOINT_LINK,
      SIZEDEFINITION_LINK,
      BUSINESSUNIT PROCESSBU, BUSINESSUNIT INVOICEBU, FINISHINGMETHOD, LANGUAGES,
      PRODUCT_LINK parentproduct, PRODUCT_LINK pack,
      PRODUCT_LINK
        LEFT OUTER JOIN PRODUCTGROUP ON (PRODUCT_LINK.PRODUCTGROUPCODE = PRODUCTGROUP.CODE),
      REPORTPARAM RP, currency cur, Businessunit BU, plant pl
 WHERE DELIVERYHISTORY.DISTRIBUTIONPOINT_LINK_ID = DISTRIBUTIONPOINT_LINK.DISTRIBUTIONPOINT_LINK_ID
  AND DISTRIBUTIONPOINT_LINK.DEPARTMENT_LINK_ID = DEPARTMENT_LINK.DEPARTMENT_LINK_ID
  AND DEPARTMENT_LINK.CUSTOMER_LINK_ID = CUSTOMER_LINK.CUSTOMER_LINK_ID
  AND DELIVERYHISTORY.PRODUCT_LINK_ID = PRODUCT_LINK.PRODUCT_LINK_ID
  AND DELIVERYHISTORY.PARENTPRODUCT_LINK_ID = parentproduct.PRODUCT_LINK_ID
  AND DELIVERYHISTORY.PARENTPACKPRODUCT_LINK_ID = pack.PRODUCT_LINK_ID
  AND DELIVERYHISTORY.SIZEDEFINITION_LINK_ID = SIZEDEFINITION_LINK.SIZEDEFINITION_LINK_ID
  AND DELIVERYHISTORY.PROCESSBUSINESSUNIT_ID = PROCESSBU.BUSINESSUNIT_ID
  AND DELIVERYHISTORY.INVOICEBUSINESSUNIT_ID = INVOICEBU.BUSINESSUNIT_ID
  AND FINISHINGMETHOD.FINISHINGMETHOD_ID = DELIVERYHISTORY.FINISHINGMETHOD_ID
  AND PRODUCT.PRODUCT_ID = PRODUCTSIZE.PRODUCT_ID
  AND SIZEDEFINITION.SIZEDEFINITION_ID = PRODUCTSIZE.SIZEDEFINITION_ID
  and DELIVERYHISTORY.Currency_Id = cur.currency_id
  and (exists (select processbusinessunit_id
                from   customeractivities ca
                where  ca.customer_id = cu.customer_id
                and    ca.processbusinessunit_id = bu.businessunit_id)
        or
        bu.businessunit_id = cu.defaultbusinessunit_id)
  and PROCESSBU.plant_id = pl.plant_id
  and exists (select 1 from businessunitsperusergroup bug,  systemuser su where bug.businessunit_id=bu.businessunit_id
  and bug.usergroup_id= su.usergroup_id
  and su.systemuser_id=rp.systemuser_id)
/
