CREATE OR REPLACE VIEW V_DELIVERYNOTE AS
SELECT
PACKINGNOTE.CORRECTEDNOTENUMBER,
(SELECT CN.DELIVERYDATE FROM PACKINGNOTE CN WHERE PACKINGNOTE.CORRECTEDNOTENUMBER = CN.NOTENUMBER
  AND PACKINGNOTE.CORRECTEDNOTENUMBER<>0) CORRECTIONDATE,
PACKINGNOTE.NOTENUMBER, CUSTOMER.CUSTOMERNUMBER, CUSTOMER.NAME CUSTOMERNAME, CUSTOMER.LEGALNAME,
ADDRESS.STREET CUSTOMERSTREET, ADDRESS.STREET2 CUSTOMERSTREET2,  ADDRESS.ZIPCODE CUSTOMERZIP, ADDRESS.CITY CUSTOMERCITY, ADDRESS.STATE CUSTOMERSTATE,
C1.CODE CUSTCOUNTRY, C1.DESCRIPTION CUSTCOUNTRY_DESC, ADDRESS.COUNTY CUSTOMERCOUNTY,
DEPARTMENT.DEPARTMENTNUMBER, DEPARTMENT.DESCRIPTION DEPARTMENTDESCRIPTION,
DISTRIBUTIONPOINT.DISTRIBUTIONPOINTNUMBER, DISTRIBUTIONPOINT.DESCRIPTION DISTRIBUTIONPOINTDESCRIPTION,
DELIVERYPOINT.CODE DELIVERYPOINTCODE, DELIVERYPOINT.DESCRIPTION DELIVERYPOINTDESCRIPTION,
PACKINGNOTE.SEQUENCENUMBER,
(SELECT DESCRIPTION FROM TRANSPORTARTICLE_DESC  A
        WHERE A.TRANSPORTARTICLE_ID = PACKINGNOTE.TRANSPORTARTICLE_ID
              AND A.LANGUAGE_ID = LANGUAGES.LANGUAGE_ID ) TRANSPORTARTICLEDESCRIPTION,
PACKINGNOTE.OCCUPANCY, EMPLOYEE.NAME EMPLOYEENAME, PACKINGNOTE.DELIVERYDATE,
PACKINGNOTE.ORIGINALDELIVERYDATE, --CAST(
PACKINGNOTE.REMARK --AS VARCHAR2(60) ) REMARK
, PROCESSBU.DESCRIPTION PROCESSBUDESCRIPTION,
PLANT.CODE PLANTCODE, PLANT.NAME PLANTNAME, PLANT.WEBSITE PLANTWEBSITE, PLANT.EMAIL PLANTEMAIL, --ORDER 501604
PLANTADDRESS.STREET PLANTSTREET, PLANTADDRESS.ZIPCODE PLANTZIPCODE,
PLANTADDRESS.CITY PLANTCITY, PLANTADDRESS.STATE PLANTSTATE, PLANT.PHONENO PLANTPHONENO, PLANT.FAXNO PLANTFAXNO,
C2.CODE PLANTCOUNTRY_CODE, C2.DESCRIPTION PLANTCOUNTRY_DESC,
cast (SS.VALUE as varchar2(60)) WEIGHTUNIT,
cast (SS1.VALUE as varchar2(1)) PRINTDELNOTEPERCONTAINER,
cast (SS2.VALUE as varchar2(1)) CONTAINERRECORDINGACTIVE,
cast (SS3.VALUE as varchar2(1)) PRINTDELNOTEZEROLINES,
ROUTE.ROUTENUMBER, ROUTE.DESCRIPTION ROUTEDESCRIPTION, ROUTE.LOADINGDOCK, PACKINGNOTE.STOPNUMBER,
PACKINGNOTE.SYSTEMUSER_ID, PACKINGNOTE.TIMESTAMP,PACKINGNOTE.CREATIONTIMESTAMP,
PACKINGNOTE.PACKINGNOTE_ID, PACKINGNOTE.ENDDATEFORUSE, PACKINGNOTE.ORIGIN,
FORMLAYOUT.CODE DELIVERYLAYOUTCODE,
 (SELECT DESCRIPTION FROM DAY_DESC
         WHERE DAY_DESC.LANGUAGE_ID = LANGUAGES.LANGUAGE_ID
         AND DAY_DESC.DAY_ID = PACKINGNOTE.DAYNUMBER) DAYDESCRIPTION
, CAST(PACKINGNOTE.WEEK AS VARCHAR2(60)) WEEK
,LANGUAGES.CODE LANGUAGECODE, CUSTOMERLANGUAGE.CODE CUSTOMERLANGUAGECODE, PACKINGNOTE.PRODUCTIONDATE PRODUCTIONDATE,
CUSTOMER.SCANSONDELIVERYNOTE,
(CASE WHEN CLM.CUSTOMER_ID = NULL
      THEN 'N'
      ELSE 'Y'
END)  HASLOCKERANAGEMENT,
CLM.CHARGESERVICE, CLM.CHARGERENT, CLM.USELOCKEROBLIGATED, CLM.FIXEDNUMBERBANK,
PACKINGNOTE.NUMBEROFGARMENTSINLOCKER, PACKINGNOTE.NUMBEROFLOCKERS,
coalesce((SELECT count(PNC.PACKINGNOTE_ID) FROM PACKINGNOTECONTAINER PNC
   LEFT OUTER JOIN CONTAINER C ON C.CONTAINER_ID = PNC.CONTAINER_ID
   LEFT OUTER JOIN CONTAINERTYPE CT ON CT.CONTAINERTYPE_ID = PNC.CONTAINERTYPE_ID
   LEFT OUTER JOIN WEIGHINGCATEGORY W ON W.WEIGHINGCATEGORY_ID = PNC.WEIGHINGCATEGORY_ID
 WHERE PNC.PACKINGNOTE_ID = PACKINGNOTE.PACKINGNOTE_ID),0) NOOFCONTAINERS,
PLANTDEPARTMENT.CODE PLANT_DEP_CODE, PLANTDEPARTMENT.DESCRIPTION PLANT_DEP_DESC,
PACKINGNOTE.FREEOFCHARGEREASON_ID,
(SELECT R.CODE FROM REASONCODE R
        WHERE R.REASONCODE_ID = PACKINGNOTE.FREEOFCHARGEREASON_ID) FREE_REASON_CODE,
(SELECT RD.DESCRIPTION FROM REASONCODE_DESC RD
        WHERE RD.REASONCODE_ID = PACKINGNOTE.FREEOFCHARGEREASON_ID
        AND RD.LANGUAGE_ID = LANGUAGES.LANGUAGE_ID) FREE_REASON_CODE_DESC
, coalesce((select max(ds.packingnote_id)
                 from   deliveryscan ds
                 where  ds.packingnote_id = packingnote.packingnote_id),
                 (select max(o.packingnote_id)
                   from   outofcirculationhistory o
                   where  o.packingnote_id = packingnote.packingnote_id), 0) numberofpossiblelineswithscans
, PACKINGNOTE.YEAR YEAR
, PACKINGNOTE.PRICESCALCULATED, PACKINGNOTE.TOTALAMOUNT, PACKINGNOTE.TOTALAMOUNTEXCLUDINGVAT
, PACKINGNOTE.WEIGHINGTICKET_ID, CU.DECIMALS, CU.INVOICEAMOUNTDECIMALS, CU.CODE CURRENCYCODE,
INVOICEBU.VATNUMBER VATNUMBER
, PACKINGNOTE.REFERENCENUMBER
, INVOICEBU.INTERNALVATNUMBER
, CUSTOMER.SEARCHNAME, PROCESSBU.CODE PROCESSBUCODE
, ROUTE.PUTUPREPORTPERCUSTOMER
, customer.signaturerequired
, dsig.personidentification personidentification
, dsig.signature signature
, (select rc.drivingdate from routecontrol rc where rc.routecontrol_id = stop.routecontrol_id) signaturedate
, to_char(stop.departuretime,'fmhhfm:MI:AM') signaturetime
, to_char(stop.departuretime,'fmhh24fm:MI') signaturetime24
, packingnote.distributionpoint_id
, packingnote.processbusinessunit_id
, DELIVERYPOINT. SIGNATUREBARCODE
, cast ((SELECT SS.VALUE FROM SYSTEMSETTING SS WHERE SS.PLANT_ID=PLANT.PLANT_ID AND SS.NAME ='CONTAINERTYPEUNQBRCLABEL')  as varchar2(60)) containertypedescription
, (select max(wo.referencenumber) from deliveryscan ds, issuehistory ih, workorder wo
where ds.uniqueitem_link_id = ih.uniqueitem_link_id
and ih.issuehistory_id = (select max(ih2.issuehistory_id) from issuehistory ih2 where ih2.uniqueitem_link_id = ds.uniqueitem_link_id)
and ih.workorder_id = wo.workorder_id
and ds.packingnote_id = packingnote.packingnote_id
and wo.deliverfrom=2
) worefnumber,
(select dnrd.description from deliverynoteremark_desc dnrd where dnrd.deliverynoteremark_id = packingnote.deliverynoteremark_id
        and dnrd.language_id = customer.language_id) deliverynoteremarkdesc
FROM  CUSTOMER
        LEFT OUTER JOIN FORMLAYOUT ON CUSTOMER.DELIVERYNOTELAYOUT_ID = FORMLAYOUT.FORMLAYOUT_ID
        LEFT OUTER JOIN CUSTOMERLOCKERMANAGEMENT CLM ON CUSTOMER.CUSTOMER_ID = CLM.CUSTOMER_ID,
        ADDRESS, DEPARTMENT, DISTRIBUTIONPOINT, PLANT, ADDRESS PLANTADDRESS,
        BUSINESSUNIT PROCESSBU,
        BUSINESSUNIT INVOICEBU,
        DELIVERYPOINT, SYSTEMSETTING SS, SYSTEMSETTING SS1,
        SYSTEMSETTING SS2, SYSTEMSETTING SS3,
        STATUS, COUNTRY C1, COUNTRY C2, PLANTDEPARTMENT, CURRENCY CU,
        PACKINGNOTE
          LEFT OUTER JOIN EMPLOYEE ON (PACKINGNOTE.EMPLOYEE_ID = EMPLOYEE.EMPLOYEE_ID)
          LEFT OUTER JOIN ROUTE ON (PACKINGNOTE.ROUTE_ID = ROUTE.ROUTE_ID)
          LEFT OUTER JOIN STOP ON (PACKINGNOTE.STOP_ID = STOP.STOP_ID)
          left outer join deliverysignature dsig on (dsig.stop_id = packingnote.stop_id and dsig.stop_id=packingnote.deliverysignature_id),
        LANGUAGES CUSTOMERLANGUAGE, LANGUAGES
  WHERE PACKINGNOTE.DISTRIBUTIONPOINT_ID = DISTRIBUTIONPOINT.DISTRIBUTIONPOINT_ID
  AND DISTRIBUTIONPOINT.DEPARTMENT_ID = DEPARTMENT.DEPARTMENT_ID
  AND DEPARTMENT.CUSTOMER_ID = CUSTOMER.CUSTOMER_ID
  AND DELIVERYPOINT.DELIVERYADDRESS_ID = ADDRESS.ADDRESS_ID
  AND PACKINGNOTE.DELIVERYPOINT_ID = DELIVERYPOINT.DELIVERYPOINT_ID
  AND PACKINGNOTE.STATUS_ID = STATUS.STATUS_ID
  AND STATUS.STATUSTYPE = 'PN' AND (STATUS.CODE = 30 OR STATUS.CODE = 20 or (packingnote.origin = 14 and status.code = 40))
  AND PACKINGNOTE.PROCESSBUSINESSUNIT_ID = PROCESSBU.BUSINESSUNIT_ID
  AND PROCESSBU.PLANT_ID = PLANT.PLANT_ID
  AND PLANT.ADDRESS_ID = PLANTADDRESS.ADDRESS_ID
  AND SS.PLANT_ID = PLANT.PLANT_ID
  AND SS.NAME = 'WEIGHTUNIT'
  AND SS1.PLANT_ID = PLANT.PLANT_ID
  AND SS1.NAME = 'PRINTDELNOTEPERCONTAINER'
  AND SS2.PLANT_ID = PLANT.PLANT_ID
  AND SS2.NAME = 'CONTAINERRECORDINGACTIVE'
  AND SS3.PLANT_ID = PLANT.PLANT_ID
  AND SS3.NAME = 'PRINTDELNOTEZEROLINES'
  AND CUSTOMER.COUNTRY_ID = C1.COUNTRY_ID
  AND PLANT.COUNTRY_ID = C2.COUNTRY_ID
  AND CUSTOMER.LANGUAGE_ID = CUSTOMERLANGUAGE.LANGUAGE_ID
  AND PACKINGNOTE.PLANTDEPARTMENT_ID = PLANTDEPARTMENT.PLANTDEPARTMENT_ID
  AND CU.CURRENCY_ID = CUSTOMER.CURRENCY_ID
  AND PACKINGNOTE.INVOICEBUSINESSUNIT_ID = INVOICEBU.BUSINESSUNIT_ID
;
/