CREATE OR REPLACE VIEW V_WEIGHINGTICKET AS
SELECT WEIGHINGTICKET.SOILTICKET, WEIGHINGTICKET.TICKETNUMBER, CUSTOMER.CUSTOMERNUMBER, CUSTOMER.NAME customername, customer.customer_id,
  ADDRESS.STREET customervisitaddress_street, ADDRESS.ZIPCODE customervisitaddresszipcode,
  ADDRESS.CITY customervisitaddresscity, WEIGHINGTICKET.WEIGHINGDATE, STATUS.CODE statuscode,
  (SELECT DESCRIPTION FROM STATUS_DESC S
     WHERE S.STATUS_ID = WEIGHINGTICKET.STATUS_ID AND S.LANGUAGE_ID = LANGUAGES.LANGUAGE_ID ) STATUSDESCRIPTION,
  EMPLOYEE.EMPLOYEENUMBER, EMPLOYEE.NAME employeename, PROCESSBUSINESSUNIT.CODE processbusinessunitnr,
  PROCESSBUSINESSUNIT.DESCRIPTION processbusinessunitname, INVOICEBUSINESSUNIT.CODE invoicebusinessunitnr,
  INVOICEBUSINESSUNIT.DESCRIPTION invoicebusinessunitname, CONSIGNMENTNOTE.CONSIGNMENTNOTENUMBER,
  weighingticket.systemuser_id username, WEIGHINGTICKET.TIMESTAMP,
  PROCESSBUSINESSUNIT.PLANT_ID processbusinessunitplant_id, WEIGHINGTICKET.WEIGHINGTICKET_ID, WEIGHINGTICKET.DAYNUMBER,
  (SELECT DESCRIPTION FROM DAY_DESC
    WHERE DAY_DESC.LANGUAGE_ID = LANGUAGES.LANGUAGE_ID AND DAY_DESC.DAY_ID = WEIGHINGTICKET.DAYNUMBER) DAYDESCRIPTION,
 	LANGUAGES.CODE LANGUAGECODE, customerlanguage.code customerlanguagecode,
  WEIGHINGTICKET.REMARK, WEIGHINGTICKET.CORRECTEDNOTENUMBER, WEIGHINGTICKET.DELIVERYDATE,
	WEIGHINGTICKET.WEIGHINGYEAR, WEIGHINGTICKET.WEIGHINGWEEK,
  SS.VALUE WEIGHTUNIT, SS1.VALUE CONTAINERRECORDINGACTIVE,(select su.username from systemuser su where weighingticket.systemuser_id = su.systemuser_id) user_name,
  WEIGHINGTICKET.Origin
FROM  CUSTOMER, ADDRESS, STATUS,
  BUSINESSUNIT PROCESSBUSINESSUNIT
    LEFT OUTER JOIN (PLANT PL left OUTER JOIN SYSTEMSETTING SS ON PL.PLANT_ID = SS.PLANT_ID AND SS.NAME = 'WEIGHTUNIT')
      ON PROCESSBUSINESSUNIT.PLANT_ID = PL.PLANT_ID
    LEFT OUTER JOIN (PLANT PL1 left OUTER JOIN SYSTEMSETTING SS1 ON PL1.PLANT_ID = SS1.PLANT_ID AND SS1.NAME = 'CONTAINERRECORDINGACTIVE')
      ON PROCESSBUSINESSUNIT.PLANT_ID = PL1.PLANT_ID,
  BUSINESSUNIT INVOICEBUSINESSUNIT,
--    LEFT OUTER JOIN (PLANT PL left OUTER JOIN SYSTEMSETTING SS ON PL.PLANT_ID = SS.PLANT_ID AND SS.NAME = 'WEIGHTUNIT')
--      ON INVOICEBUSINESSUNIT.PLANT_ID = PL.PLANT_ID
--    LEFT OUTER JOIN (PLANT PL1 left OUTER JOIN SYSTEMSETTING SS1 ON PL1.PLANT_ID = SS1.PLANT_ID AND SS1.NAME = 'CONTAINERRECORDINGACTIVE')
--      ON INVOICEBUSINESSUNIT.PLANT_ID = PL1.PLANT_ID,
  WEIGHINGTICKET
    LEFT OUTER JOIN EMPLOYEE ON (WEIGHINGTICKET.EMPLOYEE_ID = EMPLOYEE.EMPLOYEE_ID)
    LEFT OUTER JOIN CONSIGNMENTNOTE ON (WEIGHINGTICKET.CONSIGNMENTNOTE_ID = CONSIGNMENTNOTE.CONSIGNMENTNOTE_ID),
  LANGUAGES customerlanguage, languages --systemuser su
WHERE WEIGHINGTICKET.CUSTOMER_ID = CUSTOMER.CUSTOMER_ID
  AND CUSTOMER.VISITADDRESS_ID = ADDRESS.ADDRESS_ID
  AND WEIGHINGTICKET.STATUS_ID = STATUS.STATUS_ID
  AND WEIGHINGTICKET.PROCESSBUSINESSUNIT_ID = PROCESSBUSINESSUNIT.BUSINESSUNIT_ID
  AND WEIGHINGTICKET.INVOICEBUSINESSUNIT_ID = INVOICEBUSINESSUNIT.BUSINESSUNIT_ID
  and customer.language_id = customerlanguage.language_id
  --and weighingticket.systemuser_id = su.systemuser_id
;
/