--ABS-73805

------------ DISPATCHADVICEHEADER ------------
CREATE SEQUENCE SEQ_DISPATCHADVICEHEADER START WITH 1 INCREMENT BY 1;

CREATE TABLE DISPATCHADVICEHEADER
( DISPATCHADVICEHEADER_ID NUMBER NOT NULL,
  SUPPLIER_ID             NUMBER NULL,
  DELIVERYDATE            DATE NULL,
  SYSTEMUSER_ID           NUMBER DEFAULT 0 NOT NULL,
  TIMESTAMP               DATE DEFAULT SYSDATE NOT NULL);

COMMENT ON COLUMN DISPATCHADVICEHEADER.DELIVERYDATE
  IS 'ERP_I_SHIPMENTNOTIFICATION.supplierdeliverynotedate   ';

ALTER TABLE DISPATCHADVICEHEADER
  ADD CONSTRAINT PK_DISPATCHADVICEHEADER PRIMARY KEY (DISPATCHADVICEHEADER_ID);


------------ DISPATCHADVICELINE ------------
CREATE SEQUENCE SEQ_DISPATCHADVICELINE START WITH 1 INCREMENT BY 1;

CREATE TABLE DISPATCHADVICELINE
( DISPATCHADVICELINE_ID      NUMBER NOT NULL,
  DISPATCHADVICEHEADER_ID    NUMBER NOT NULL,
  SUPPLIERDELIVERYNOTENUMBER NUMBER NOT NULL,
  SUPPLIERDELIVERYNOTEDATE   DATE NOT NULL,
  SUPPLIERORDERNUMBER        NUMBER NOT NULL,
  LINESEQUENTIALNUMBER       NUMBER NOT NULL,
  PURCHASEORDERLINE_ID       NUMBER NOT NULL,
  SUPPLIERPRODUCTCODE        VARCHAR2(30) NOT NULL,
  SUPPLIERSIZECODE           VARCHAR2(20) NOT NULL,
  LAUNDRYPRODUCT_ID          NUMBER NULL,
  LAUNDRYSIZE_ID             NUMBER NULL,
  LAUNDRYGRADE_ID            NUMBER NULL,
  ACTUALQUANTITYDELIVERED    NUMBER NOT NULL,
  REGISTRATIONUNIT_ID        NUMBER NOT NULL,
  PALETT                     VARCHAR2(64) NULL,
  TRANSPORTUNIT              VARCHAR2(20) NULL,
  COUNTRYOFORIGIN            VARCHAR2(3) NOT NULL,
  COMMODITYCODE              VARCHAR2(35) NOT NULL,
  REMARK                     VARCHAR2(300) NULL,
  SHIPPEDQUANTITY            NUMBER DEFAULT 0 NOT NULL,
  SYSTEMUSER_ID              NUMBER DEFAULT 0 NOT NULL,
  TIMESTAMP                  DATE DEFAULT SYSDATE NOT NULL);

COMMENT ON COLUMN DISPATCHADVICELINE.SUPPLIERDELIVERYNOTENUMBER
  IS 'I_SHIPMENTNOTIFICATION.SUPPLIERDELIVERYNOTENUMBER';
COMMENT ON COLUMN DISPATCHADVICELINE.SUPPLIERDELIVERYNOTEDATE
  IS 'I_SHIPMENTNOTIFICATION.SUPPLIERDELIVERYNOTEDATE';
COMMENT ON COLUMN DISPATCHADVICELINE.SUPPLIERORDERNUMBER
  IS 'I_SHIPMENTNOTIFICATION.PURCHASEORDERNUMBER';
COMMENT ON COLUMN DISPATCHADVICELINE.LINESEQUENTIALNUMBER
  IS 'I_SHIPMENTNOTIFICATION.LINESEQUENTIALNUMBER';
COMMENT ON COLUMN DISPATCHADVICELINE.ACTUALQUANTITYDELIVERED
  IS 'I_SHIPMENTNOTIFICATION.ACTUALQUANTITYDELIVERED';
COMMENT ON COLUMN DISPATCHADVICELINE.REGISTRATIONUNIT_ID
  IS 'via I_SHIPMENTNOTIFICATION.UNITOMEASURE_ACTQTYDEL';
COMMENT ON COLUMN DISPATCHADVICELINE.PALETT
  IS 'I_SHIPMENTNOTIFICATION.PALETT';
COMMENT ON COLUMN DISPATCHADVICELINE.TRANSPORTUNIT
  IS 'I_SHIPMENTNOTIFICATION.TRANSPORTUNIT';
COMMENT ON COLUMN DISPATCHADVICELINE.COUNTRYOFORIGIN
  IS 'I_SHIPMENTNOTIFICATION.COUNTRYOFORIGIN';
COMMENT ON COLUMN DISPATCHADVICELINE.COMMODITYCODE
  IS 'I_SHIPMENTNOTIFICATION.COMMODITYCODE';
COMMENT ON COLUMN DISPATCHADVICELINE.REMARK
  IS 'I_SHIPMENTNOTIFICATION.REMARK';

ALTER TABLE DISPATCHADVICELINE
  ADD CONSTRAINT PK_DISPATCHADVICELINE_ID PRIMARY KEY (DISPATCHADVICELINE_ID);
  
CREATE INDEX XIF1DISPATCHADVICELINE ON DISPATCHADVICELINE (LAUNDRYGRADE_ID ASC);
CREATE INDEX XIF2DISPATCHADVICELINE ON DISPATCHADVICELINE (DISPATCHADVICEHEADER_ID ASC);
CREATE INDEX XIF3DISPATCHADVICELINE ON DISPATCHADVICELINE (PURCHASEORDERLINE_ID ASC);
CREATE INDEX XIF4DISPATCHADVICELINE ON DISPATCHADVICELINE (LAUNDRYPRODUCT_ID ASC);
CREATE INDEX XIF5DISPATCHADVICELINE ON DISPATCHADVICELINE (REGISTRATIONUNIT_ID ASC);
CREATE INDEX XIF6DISPATCHADVICELINE ON DISPATCHADVICELINE (LAUNDRYSIZE_ID ASC);

ALTER TABLE DISPATCHADVICELINE
  ADD CONSTRAINT FK_DISPATCHADVICELINE_GRADE FOREIGN KEY (LAUNDRYGRADE_ID)
  REFERENCES QUALITYGRADE (QUALITYGRADE_ID);
ALTER TABLE DISPATCHADVICELINE
  ADD CONSTRAINT FK_DISPATCHADVICELINE_HEADER FOREIGN KEY (DISPATCHADVICEHEADER_ID)
  REFERENCES DISPATCHADVICEHEADER (DISPATCHADVICEHEADER_ID) ON DELETE CASCADE;
ALTER TABLE DISPATCHADVICELINE
  ADD CONSTRAINT FK_DISPATCHADVICELINE_POL FOREIGN KEY (PURCHASEORDERLINE_ID)
  REFERENCES PURCHASEORDERLINE (PURCHASEORDERLINE_ID) ON DELETE SET NULL;
ALTER TABLE DISPATCHADVICELINE
  ADD CONSTRAINT FK_DISPATCHADVICELINE_PROD FOREIGN KEY (LAUNDRYPRODUCT_ID)
  REFERENCES PRODUCT_LINK (PRODUCT_LINK_ID);
ALTER TABLE DISPATCHADVICELINE
  ADD CONSTRAINT FK_DISPATCHADVICELINE_REG FOREIGN KEY (REGISTRATIONUNIT_ID)
  REFERENCES REGISTRATIONUNIT (REGISTRATIONUNIT_ID);
ALTER TABLE DISPATCHADVICELINE
  ADD CONSTRAINT FK_DISPATCHADVICELINE_SIZE FOREIGN KEY (LAUNDRYSIZE_ID)
  REFERENCES SIZEDEFINITION_LINK (SIZEDEFINITION_LINK_ID);

------------ ERP_O_PURCHASEORDERHEADER ------------
ALTER TABLE ERP_O_PURCHASEORDERHEADER ADD (EXECUTEDBY VARCHAR2(1) DEFAULT 1 NULL,
										   ORDERPRIORITY VARCHAR2(12) NULL);

------------ ERP_O_PURCHASEORDERLINE ------------										   
ALTER TABLE ERP_O_PURCHASEORDERLINE ADD (TYPEOFMERCHANDISE NUMBER NULL,
										 LINESEQUENTIALNUMBER NUMBER NULL);

------------ ERP_O_PURCHASEORDERLINE_EMBLEM ------------		
ALTER TABLE ERP_O_PURCHASEORDERLINE_EMBLEM DROP CONSTRAINT EMBLEMTYPE_VALID1;
						 
CREATE TABLE ERP_O_PURCHASEORDERLINE_EMBLEM
( TRANSFER_ID            NUMBER NOT NULL);

ALTER TABLE ERP_O_PURCHASEORDERLINE_EMBLEM ADD (
  PURCHASEORDERLINE_ID   NUMBER NOT NULL,
  TRANSFER_STATUS        NUMBER DEFAULT 0 NOT NULL,
  WEARERNUMBER           NUMBER NOT NULL,
  CUSTOMEREMPLOYEENUMBER VARCHAR2(10) NULL,
  FULLNAME               VARCHAR2(40) NOT NULL,
  SEARCHNAME             VARCHAR2(20) NOT NULL,
  FIRSTNAME              VARCHAR2(24) NULL,
  SEX                    VARCHAR2(1) NOT NULL,
  EMBLEMTYPE             NUMBER NOT NULL CONSTRAINT EMBLEMTYPE_VALID1 CHECK (EMBLEMTYPE IN (1,2,3)),
  QTYREQUESTED           NUMBER NOT NULL,
  QTYPERGARMENT          NUMBER DEFAULT 1 NOT NULL,
  EMBLEMNAME             VARCHAR2(40) NULL);

COMMENT ON COLUMN ERP_O_PURCHASEORDERLINE_EMBLEM.TRANSFER_ID
  IS 'Foreign key to ERP_O_PURCHASEORDERLINE';
COMMENT ON COLUMN ERP_O_PURCHASEORDERLINE_EMBLEM.PURCHASEORDERLINE_ID
  IS 'Foreign key to ERP_O_PURCHASEORDERLINE';
COMMENT ON COLUMN ERP_O_PURCHASEORDERLINE_EMBLEM.WEARERNUMBER
  IS 'WEARER.WEARERNUMBER ';
COMMENT ON COLUMN ERP_O_PURCHASEORDERLINE_EMBLEM.CUSTOMEREMPLOYEENUMBER
  IS 'WEARER.CUSTOMEREMPLOYEENUMBER ';
COMMENT ON COLUMN ERP_O_PURCHASEORDERLINE_EMBLEM.FULLNAME
  IS 'WEARER.FULLNAME ';
COMMENT ON COLUMN ERP_O_PURCHASEORDERLINE_EMBLEM.SEARCHNAME
  IS 'WEARER.SEARCHNAME';
COMMENT ON COLUMN ERP_O_PURCHASEORDERLINE_EMBLEM.FIRSTNAME
  IS 'WEARER.FIRSTNAME';
COMMENT ON COLUMN ERP_O_PURCHASEORDERLINE_EMBLEM.SEX
  IS 'WEARER.SEX';
COMMENT ON COLUMN ERP_O_PURCHASEORDERLINE_EMBLEM.EMBLEMTYPE
  IS '1= Company
2 = Name
3 = Other/ Not Defined';
COMMENT ON COLUMN ERP_O_PURCHASEORDERLINE_EMBLEM.QTYREQUESTED
  IS 'WORKORDERLINEEMBLEM.QTYREQUESTED';
COMMENT ON COLUMN ERP_O_PURCHASEORDERLINE_EMBLEM.QTYPERGARMENT
  IS 'EMBLEMTEMPLATELINE.QTYPERGARMENT  (when it does not exist 1)';
COMMENT ON COLUMN ERP_O_PURCHASEORDERLINE_EMBLEM.EMBLEMNAME
  IS 'WEARER.EMBLEMNAME when EMBLEMTYPE=2';

CREATE INDEX XIF1ERP_O_PURORDERLINE_EMBLEM ON ERP_O_PURCHASEORDERLINE_EMBLEM (TRANSFER_ID ASC, PURCHASEORDERLINE_ID ASC);
 
ALTER TABLE ERP_O_PURCHASEORDERLINE_EMBLEM
  ADD CONSTRAINT FK_ERP_O_PURORDERLINE_EMBLEM FOREIGN KEY (TRANSFER_ID, PURCHASEORDERLINE_ID)
  REFERENCES ERP_O_PURCHASEORDERLINE (TRANSFER_ID, PURCHASEORDERLINE_ID) ON DELETE CASCADE;