CREATE OR REPLACE VIEW V_BULKPUTUP AS
SELECT PACKINGNOTE.PACKINGNOTE_ID PACKINGNOTEID,STATUS.STATUSTYPE,  ROUTECONTROL.DRIVINGDATE, DEPOT.CODE DEPOTCODE, DEPOT.DESCRIPTION DEPOTDESCRIPTION,
 PACKINGNOTE.NOTENUMBER, CUSTOMER.CUSTOMERNUMBER, CUSTOMER.NAME CUSTOMERNAME, CUSTOMER.LEGALNAME CUSTOMERLEGALNAME,
 ADDRESS.STREET, ADDRESS.STREET2, ADDRESS.CITY, ADDRESS.STATE, ADDRESS.ZIPCODE, ADDRESS.COUNTY,
 DEPARTMENT.DEPARTMENTNUMBER, DEPARTMENT.DESCRIPTION DEPARTMENTDESCRIPTION,
 DISTRIBUTIONPOINT.DISTRIBUTIONPOINTNUMBER, DISTRIBUTIONPOINT.DESCRIPTION DISTRIBUTIONPOINTDESCRIPTION,
  DELIVERYPOINT.CODE DELIVERYPOINTCODE, DELIVERYPOINT.DESCRIPTION DELIVERYPOINTDESCRIPTION,
 PACKINGNOTE.SEQUENCENUMBER, PLANTDEPARTMENT.CODE PACKINGDEPARTMENTCODE, PLANTDEPARTMENT.DESCRIPTION PACKINGDEPARTMENTDESCRIPTION,
 TRANSPORTARTICLE.CODE TRANSPORTARTICLECODE,
 (SELECT A.DESCRIPTION FROM TRANSPORTARTICLE_DESC A WHERE A.TRANSPORTARTICLE_ID = PACKINGNOTE.TRANSPORTARTICLE_ID AND A.LANGUAGE_ID = LANGUAGES.LANGUAGE_ID ) TRANSPORTARTICLEDESCRIPTION,
 PACKINGNOTE.OCCUPANCY, EMPLOYEE.EMPLOYEENUMBER, EMPLOYEE.NAME EMPLOYEENAME,
 PACKINGNOTE.PRODUCTIONDATE,
 PACKINGNOTE.DELIVERYDATE,
 STATUS.CODE STATUSCODE,
 (SELECT S.DESCRIPTION FROM STATUS_DESC S WHERE S.STATUS_ID = PACKINGNOTE.STATUS_ID AND S.LANGUAGE_ID = LANGUAGES.LANGUAGE_ID) STATUSDESCRIPTION,
 PACKINGNOTE.PNREMARK REMARK, PROCESSBU.CODE PROCESSBUCODE, PROCESSBU.DESCRIPTION PROCESSBUDESCRIPTION,
 INVOICEBU.CODE INVOICEBUCODE, INVOICEBU.DESCRIPTION INVOICEBUDESCRIPTION,
 ROUTE.ROUTENUMBER, ROUTE.DESCRIPTION ROUTEDESCRIPTION, ROUTE.LOADINGDOCK,
 PACKINGNOTE.CLEANRETURN,
 PACKINGNOTE.DEADLINE,
 GETTIMEASNUMERIC(PACKINGNOTE.DEADLINE) DEADLINEASNUMERIC,
 CONSIGNMENTNOTE.CONSIGNMENTNOTENUMBER,
 PACKINGNOTE.SYSTEMUSER_ID, PACKINGNOTE.TIMESTAMP,PACKINGNOTE.CREATIONTIMESTAMP,
 PACKINGNOTE.ENDDATEFORUSE,
 (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(2)) WEEK, LANGUAGES.CODE LANGUAGECODE,
  DRIVER.NAME DRIVERNAME,
  DRIVER.EMPLOYEENUMBER DRIVERNUMBER
  , CASE WHEN CUSTOMER.DELNOTEFORBULKPUTUPITEMS = 0 THEN  'N' ELSE 'Y' END BULKPUTUPONPACKINGNOTE,
  PACKINGNOTE.YEAR YEAR
  , (SELECT SS.VALUE FROM SYSTEMSETTING SS WHERE SS.PLANT_ID = PROCESSBU.PLANT_ID
            AND SS.NAME = 'PACKINGNOTECOLUMNS') PACKINGNOTECOLUMNS
  , (SELECT COUNT(*) FROM CUSTOMERDELIVERYDAY CDD WHERE CUSTOMER.CUSTOMER_ID = CDD.CUSTOMER_ID
        AND CDD.DAY_ID = PACKINGNOTE.DAYNUMBER AND CDD.TYPEOFMERCHADISE =2) DELIVERGARMENTS,
        PACKINGNOTE.STOPNUMBER, ROUTE.PUTUPREPORTPERCUSTOMER,
		CUSTOMER.EXTRANAME1, CUSTOMER.LEGACYCUSTOMERNUMBER, DI.CODE DISTRICTDESC, DI.DESCRIPTION DISTRICTCODE, ROUTECONTROL.ROUTECONTROL_ID
FROM CUSTOMER, DEPARTMENT, DISTRIBUTIONPOINT, PLANTDEPARTMENT,
       TRANSPORTARTICLE,STATUS, DELIVERYPOINT, ADDRESS,
       BUSINESSUNIT PROCESSBU, BUSINESSUNIT INVOICEBU,
       PACKINGNOTE
       LEFT OUTER JOIN EMPLOYEE ON (PACKINGNOTE.EMPLOYEE_ID = EMPLOYEE.EMPLOYEE_ID)
       LEFT OUTER JOIN ROUTECONTROL ON (PACKINGNOTE.ROUTECONTROL_ID = ROUTECONTROL.ROUTECONTROL_ID )
       LEFT OUTER JOIN CONSIGNMENTNOTE ON (PACKINGNOTE.CONSIGNMENTNOTE_ID = CONSIGNMENTNOTE.CONSIGNMENTNOTE_ID)
       LEFT OUTER JOIN ROUTE
              LEFT OUTER JOIN DEPOT ON DEPOT.DEPOT_ID = ROUTE.DEPOT_ID
              LEFT OUTER JOIN EMPLOYEE DRIVER ON DRIVER.EMPLOYEE_ID = ROUTE.DRIVER_ID
              INNER JOIN DISTRICT DI ON DI.DISTRICT_ID = ROUTE.DISTRICT_ID
        ON PACKINGNOTE.ROUTE_ID = ROUTE.ROUTE_ID,
       LANGUAGES
 	WHERE
      PACKINGNOTE.TRANSPORTARTICLE_ID = TRANSPORTARTICLE.TRANSPORTARTICLE_ID
  AND PACKINGNOTE.STATUS_ID = STATUS.STATUS_ID
  AND STATUS.STATUSTYPE = 'PN'
  AND STATUS.CODE IN ('10','20','30')
  AND PACKINGNOTE.DELIVERYPOINT_ID = DELIVERYPOINT.DELIVERYPOINT_ID
  AND DELIVERYPOINT.DELIVERYADDRESS_ID = ADDRESS.ADDRESS_ID
	AND PACKINGNOTE.PLANTDEPARTMENT_ID = PLANTDEPARTMENT.PLANTDEPARTMENT_ID
  AND PACKINGNOTE.PROCESSBUSINESSUNIT_ID = PROCESSBU.BUSINESSUNIT_ID
	AND PACKINGNOTE.INVOICEBUSINESSUNIT_ID = INVOICEBU.BUSINESSUNIT_ID
  AND PACKINGNOTE.DISTRIBUTIONPOINT_ID = DISTRIBUTIONPOINT.DISTRIBUTIONPOINT_ID
	AND DISTRIBUTIONPOINT.DEPARTMENT_ID = DEPARTMENT.DEPARTMENT_ID
	AND DEPARTMENT.CUSTOMER_ID = CUSTOMER.CUSTOMER_ID;
/