--Order 20012126.80

----DELIVERYTERM----

CREATE SEQUENCE SEQ_DELIVERYTERM
START WITH 1
INCREMENT BY 1;

CREATE TABLE DELIVERYTERM
(
	DELIVERYTERM_ID      NUMBER NOT NULL ,
	CODE                 VARCHAR2(12) NOT NULL ,
	CURRENCY_ID          NUMBER NOT NULL ,
	MAXNETORDERVALUE     NUMBER(15,6) NOT NULL ,
	ACTIVE               VARCHAR2(1) DEFAULT  'Y'  NOT NULL  CONSTRAINT  YN_ValidationField71 CHECK (ACTIVE IN ('Y', 'N')),
	DEFAULTDELIVERYTERM  VARCHAR2(1) DEFAULT  'N'  NOT NULL  CONSTRAINT  YN_ValidationField72 CHECK (DEFAULTDELIVERYTERM IN ('Y', 'N')),
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
);

CREATE UNIQUE INDEX XAK1DELIVERYTERM ON DELIVERYTERM
(CODE   ASC);

CREATE  INDEX XIF1DELIVERYTERM ON DELIVERYTERM
(CURRENCY_ID   ASC);

CREATE  INDEX XIE1DELIVERYTERM ON DELIVERYTERM
(ACTIVE   ASC);

CREATE  INDEX XIE2DELIVERYTERM ON DELIVERYTERM
(DEFAULTDELIVERYTERM   ASC);

ALTER TABLE DELIVERYTERM
ADD CONSTRAINT  XPKDELIVERYTERM PRIMARY KEY (DELIVERYTERM_ID);

ALTER TABLE DELIVERYTERM
ADD CONSTRAINT DELIVERYTERM_CURRENCY FOREIGN KEY (CURRENCY_ID) REFERENCES CURRENCY (CURRENCY_ID);

----DELIVERYTERM_DESC----

CREATE TABLE DELIVERYTERM_DESC
(
	DELIVERYTERM_ID      NUMBER NOT NULL ,
	LANGUAGE_ID          NUMBER NOT NULL ,
	DESCRIPTION          VARCHAR2(40) NOT NULL ,
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
);

CREATE  INDEX XIF1DELIVERYTERM_DESC ON DELIVERYTERM_DESC
(DELIVERYTERM_ID   ASC);

CREATE  INDEX XIF2DELIVERYTERM_DESC ON DELIVERYTERM_DESC
(LANGUAGE_ID   ASC);

ALTER TABLE DELIVERYTERM_DESC
ADD CONSTRAINT  XPKDELIVERYTERM_DESC PRIMARY KEY (DELIVERYTERM_ID,LANGUAGE_ID);

ALTER TABLE DELIVERYTERM_DESC
ADD CONSTRAINT DELIVERYTERM_DESC_DELVERYTERM FOREIGN KEY (DELIVERYTERM_ID) REFERENCES DELIVERYTERM (DELIVERYTERM_ID) ON DELETE CASCADE;

ALTER TABLE DELIVERYTERM_DESC
ADD CONSTRAINT DELIVERYTERM_DESC_LANGUAGES FOREIGN KEY (LANGUAGE_ID) REFERENCES LANGUAGES (LANGUAGE_ID) ON DELETE CASCADE;

----DELIVERYTERMPRICE----

CREATE SEQUENCE SEQ_DELIVERYTERMPRICE
START WITH 1
INCREMENT BY 1;

CREATE TABLE DELIVERYTERMPRICE
(
	DELIVERYTERMPRICE_ID NUMBER NOT NULL ,
	DELIVERYTERM_ID      NUMBER NOT NULL ,
	STARTDATE            DATE NOT NULL ,
	FROMWEIGHT           NUMBER(8,2) DEFAULT  0.00  NOT NULL ,
	UNITPRICE            NUMBER(15,6) NOT NULL ,
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
);

CREATE  INDEX XIF1DELIVERYTERMPRICE ON DELIVERYTERMPRICE
(DELIVERYTERM_ID   ASC);

CREATE UNIQUE INDEX XAK1DELIVERYTERMPRICE ON DELIVERYTERMPRICE
(DELIVERYTERM_ID   ASC,STARTDATE   ASC,FROMWEIGHT   ASC);

ALTER TABLE DELIVERYTERMPRICE
ADD CONSTRAINT  XPKDELIVERYTERMPRICE PRIMARY KEY (DELIVERYTERMPRICE_ID);

ALTER TABLE DELIVERYTERMPRICE
ADD CONSTRAINT DELIVERYTERMPRICE_DELIVERYTERM FOREIGN KEY (DELIVERYTERM_ID) REFERENCES DELIVERYTERM (DELIVERYTERM_ID) ON DELETE CASCADE;

----SHIPMENTTYPE----

CREATE SEQUENCE SEQ_SHIPMENTTYPE
START WITH 1
INCREMENT BY 1;

CREATE TABLE SHIPMENTTYPE
(
	SHIPMENTTYPE_ID      NUMBER NOT NULL ,
	CODE                 VARCHAR2(12) NOT NULL ,
	ACTIVE               VARCHAR2(1) DEFAULT  'Y'  NOT NULL  CONSTRAINT  YN_ValidationField73 CHECK (ACTIVE IN ('Y', 'N')),
	DEFAULTSHIPMENTTYPE  VARCHAR2(1) DEFAULT  'N'  NOT NULL  CONSTRAINT  YN_ValidationField74 CHECK (DEFAULTSHIPMENTTYPE IN ('Y', 'N')),
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
);

CREATE UNIQUE INDEX XAK1SHIPMENTTYPE ON SHIPMENTTYPE
(CODE   ASC);

CREATE  INDEX XIE1SHIPMENTTYPE ON SHIPMENTTYPE
(ACTIVE   ASC);

CREATE  INDEX XIE2SHIPMENTTYPE ON SHIPMENTTYPE
(DEFAULTSHIPMENTTYPE   ASC);

ALTER TABLE SHIPMENTTYPE
ADD CONSTRAINT  XPKSHIPMENTTYPE PRIMARY KEY (SHIPMENTTYPE_ID);

----SHIPMENTTYPE_DESC----

CREATE TABLE SHIPMENTTYPE_DESC
(
	SHIPMENTTYPE_ID      NUMBER NOT NULL ,
	LANGUAGE_ID          NUMBER NOT NULL ,
	DESCRIPTION          VARCHAR2(40) NOT NULL ,
	SYSTEMUSER_ID        NUMBER NOT NULL ,
	TIMESTAMP            DATE NOT NULL 
);


CREATE  INDEX XIF1SHIPMENTTYPE_DESC ON SHIPMENTTYPE_DESC
(SHIPMENTTYPE_ID   ASC);

CREATE  INDEX XIF2SHIPMENTTYPE_DESC ON SHIPMENTTYPE_DESC
(LANGUAGE_ID   ASC);

ALTER TABLE SHIPMENTTYPE_DESC
ADD CONSTRAINT  XPKSHIPMENTTYPE_DESC PRIMARY KEY (SHIPMENTTYPE_ID,LANGUAGE_ID);

ALTER TABLE SHIPMENTTYPE_DESC
ADD CONSTRAINT SHIPMENTTYPE_DESC_SHIPMENTTYPE FOREIGN KEY (SHIPMENTTYPE_ID) REFERENCES SHIPMENTTYPE (SHIPMENTTYPE_ID) ON DELETE CASCADE;

ALTER TABLE SHIPMENTTYPE_DESC
ADD CONSTRAINT SHIPMENTTYPE_DESC_LANGUAGES FOREIGN KEY (LANGUAGE_ID) REFERENCES LANGUAGES (LANGUAGE_ID) ON DELETE CASCADE;

----WORKORDER----

ALTER TABLE WORKORDER
	DROP CONSTRAINT SUBTYPE_valid;

ALTER TABLE WORKORDER ADD CONSTRAINT SUBTYPE_valid  CHECK (SUBTYPE IN (0, 1, 2));

COMMENT ON COLUMN WORKORDER.SUBTYPE IS '0 = Standard
1 = Stock Delivery Order (Quick Scan)
2 = Sample Order
';

ALTER TABLE WORKORDER
	ADD (DEBTOR_ID NUMBER NULL);

ALTER TABLE WORKORDER
	ADD (CONTACTPERSON_ID NUMBER NULL);

ALTER TABLE WORKORDER
	ADD (COMMUNICATIONMETHOD_ID NUMBER NULL);

ALTER TABLE WORKORDER
	ADD (SHIPMENTTYPE_ID NUMBER NULL);

ALTER TABLE WORKORDER
	ADD (DELIVERYTERM_ID NUMBER);

ALTER TABLE WORKORDER
	ADD (APPROVALUSER_ID NUMBER NULL);

ALTER TABLE WORKORDER
	ADD (APPROVALTIMESTAMP DATE NULL);

ALTER TABLE WORKORDER
	ADD (RESPLAUNDRYEMPLOYEE_ID NUMBER NULL);

ALTER TABLE WORKORDER
	ADD (OFFER VARCHAR2(1)  DEFAULT 'N' NOT NULL CONSTRAINT YN_ValidationField75 CHECK (OFFER IN ('Y', 'N')));

ALTER TABLE WORKORDER
	ADD (BLOCKED VARCHAR2(1)  DEFAULT 'N' NOT NULL CONSTRAINT YN_ValidationField76 CHECK (BLOCKED IN ('Y', 'N')));

ALTER TABLE WORKORDER
	ADD (BLOCKEDFORINVOICING VARCHAR2(1)  DEFAULT 'N' NOT NULL CONSTRAINT YN_ValidationField77 CHECK (BLOCKEDFORINVOICING IN ('Y', 'N')));

ALTER TABLE WORKORDER
	ADD (ALLOWPARTIALINVOICING VARCHAR2(1)  DEFAULT 'N' NOT NULL CONSTRAINT YN_ValidationField78 CHECK (ALLOWPARTIALINVOICING IN ('Y', 'N')));

ALTER TABLE WORKORDER
	ADD (EXTERNALREMARK CLOB NULL);

ALTER TABLE WORKORDER
	ADD (DISCOUNTPERCENTAGE NUMBER(5,2)  DEFAULT 0.00 NOT NULL);

CREATE  INDEX XIF26WORKORDER ON WORKORDER
(DEBTOR_ID   ASC);

CREATE  INDEX XIF27WORKORDER ON WORKORDER
(CONTACTPERSON_ID   ASC);

CREATE  INDEX XIF28WORKORDER ON WORKORDER
(COMMUNICATIONMETHOD_ID   ASC);

CREATE  INDEX XIF29WORKORDER ON WORKORDER
(SHIPMENTTYPE_ID   ASC);

CREATE  INDEX XIF30WORKORDER ON WORKORDER
(DELIVERYTERM_ID   ASC);

CREATE  INDEX XIF31WORKORDER ON WORKORDER
(APPROVALUSER_ID   ASC);

CREATE  INDEX XIF32WORKORDER ON WORKORDER
(RESPLAUNDRYEMPLOYEE_ID   ASC);

ALTER TABLE WORKORDER
ADD CONSTRAINT WORKORDER_CUSTOMER FOREIGN KEY (DEBTOR_ID) REFERENCES CUSTOMER (CUSTOMER_ID);

ALTER TABLE WORKORDER
ADD CONSTRAINT WORKORDER_CONTACTPERSON FOREIGN KEY (CONTACTPERSON_ID) REFERENCES CONTACTPERSON (CONTACTPERSON_ID) ON DELETE SET NULL;

ALTER TABLE WORKORDER
ADD CONSTRAINT WORKORDER_COMMUNICMETHOD FOREIGN KEY (COMMUNICATIONMETHOD_ID) REFERENCES COMMUNICATIONMETHOD (COMMUNICATIONMETHOD_ID) ON DELETE SET NULL;

ALTER TABLE WORKORDER
ADD CONSTRAINT WORKORDER_SHIPMENTTYPE FOREIGN KEY (SHIPMENTTYPE_ID) REFERENCES SHIPMENTTYPE (SHIPMENTTYPE_ID) ON DELETE SET NULL;

ALTER TABLE WORKORDER
ADD CONSTRAINT WORKORDER_DELIVERYTERM FOREIGN KEY (DELIVERYTERM_ID) REFERENCES DELIVERYTERM (DELIVERYTERM_ID) ON DELETE SET NULL;

ALTER TABLE WORKORDER
ADD CONSTRAINT WORKORDER_SYSTEMUSER FOREIGN KEY (APPROVALUSER_ID) REFERENCES SYSTEMUSER (SYSTEMUSER_ID) ON DELETE SET NULL;

ALTER TABLE WORKORDER
ADD CONSTRAINT WORKORDER_EMPLOYEE FOREIGN KEY (RESPLAUNDRYEMPLOYEE_ID) REFERENCES EMPLOYEE (EMPLOYEE_ID) ON DELETE SET NULL;

----WORKORDERLINE----

ALTER TABLE WORKORDERLINE
	ADD (SALESPRICEORIGIN NUMBER  DEFAULT 0 NOT NULL CONSTRAINT SalesPriceOrigin_valid1_1 CHECK (SALESPRICEORIGIN IN (0, 1, 2)));

ALTER TABLE WORKORDERLINE
	ADD (MANUALPRICECHANGE NUMBER  DEFAULT 0 NOT NULL CONSTRAINT ManualPriceChange_valid1_1 CHECK (MANUALPRICECHANGE IN (0, 1)));

COMMENT ON COLUMN WORKORDERLINE.SALESPRICEORIGIN IS '0 = No price defined (default)
1 = General Sales Price List
2 = PLPC';

COMMENT ON COLUMN WORKORDERLINE.MANUALPRICECHANGE IS '0 = Automatically determined from application (default)
1 = Price was entered manually';

----GARMENTSALESHISTORY----

ALTER TABLE GARMENTSALESHISTORY
	ADD (PACKINGNOTELINE_ID NUMBER NULL);

ALTER TABLE GARMENTSALESHISTORY
	ADD (WORKORDERLINEEMBLEM_ID NUMBER NULL);

CREATE  INDEX XIF15GARMENTSALESHISTORY ON GARMENTSALESHISTORY
(PACKINGNOTELINE_ID   ASC);

CREATE  INDEX XIF16GARMENTSALESHISTORY ON GARMENTSALESHISTORY
(WORKORDERLINEEMBLEM_ID   ASC);

ALTER TABLE GARMENTSALESHISTORY
ADD CONSTRAINT GARMENTSALESHISTORY_PNLINE FOREIGN KEY (PACKINGNOTELINE_ID) REFERENCES PACKINGNOTELINE (PACKINGNOTELINE_ID) ON DELETE SET NULL;

ALTER TABLE GARMENTSALESHISTORY
ADD CONSTRAINT GARMENTSALESHISTORY_WOLINEEMBL FOREIGN KEY (WORKORDERLINEEMBLEM_ID) REFERENCES WORKORDERLINEEMBLEM (WORKORDERLINEEMBLEM_ID) ON DELETE SET NULL;

----EMBLEMHISTORY----

ALTER TABLE EMBLEMHISTORY
	ADD (GARMENTSALESHISTORY_ID NUMBER NULL);

CREATE  INDEX XIF15EMBLEMHISTORY ON EMBLEMHISTORY
(GARMENTSALESHISTORY_ID   ASC);

ALTER TABLE EMBLEMHISTORY
ADD CONSTRAINT EMBLEMHISTORY_GARMENTSALESHIST FOREIGN KEY (GARMENTSALESHISTORY_ID) REFERENCES GARMENTSALESHISTORY (GARMENTSALESHISTORY_ID) ON DELETE SET NULL;

----CUSTOMER----

ALTER TABLE CUSTOMER
	ADD (DELIVERYTERM_ID NUMBER NULL);

ALTER TABLE CUSTOMER
	ADD (ALLOWPARTIALINVOICING VARCHAR2(1)  DEFAULT 'N' NOT NULL CONSTRAINT YN_ValidationField79 CHECK (ALLOWPARTIALINVOICING IN ('Y', 'N')));

CREATE  INDEX XIF42CUSTOMER ON CUSTOMER
(DELIVERYTERM_ID   ASC);

ALTER TABLE CUSTOMER
ADD CONSTRAINT CUSTOMER_DELIVERYTERM FOREIGN KEY (DELIVERYTERM_ID) REFERENCES DELIVERYTERM (DELIVERYTERM_ID);

----PACKINGNOTE----

COMMENT ON COLUMN PACKINGNOTE.ORIGIN IS '0 = Generated based on Order
1 = Manually added
2 = Generated based on Outscan
3 = Quantity Correction Ticket
4 = Stock Delivery Note
5 = Special Order
6 = Bulk Note
7 = Bin Note
8 = Transfer Weights
9 = Import from DW
10 = Mounting Order
11 = Sales Order
12 = Credit Note';

ALTER TABLE PACKINGNOTE
	ADD (SALESNOTE_ID NUMBER NULL);

ALTER TABLE PACKINGNOTE
	ADD (PARCELTRACECODE VARCHAR2(30) NULL);

ALTER TABLE PACKINGNOTE
	ADD (CALCULATETRANSPORTCOSTS VARCHAR2(1)  DEFAULT 'N' NOT NULL CONSTRAINT YN_ValidationField80 CHECK (CALCULATETRANSPORTCOSTS IN ('Y', 'N')));

CREATE  INDEX XIF16PACKINGNOTE ON PACKINGNOTE
(SALESNOTE_ID   ASC);

ALTER TABLE PACKINGNOTE
ADD CONSTRAINT PACKINGNOTE_WORKORDER FOREIGN KEY (SALESNOTE_ID) REFERENCES WORKORDER (WORKORDER_ID) ON DELETE SET NULL;

----PACKINGNOTELINE----

COMMENT ON COLUMN PACKINGNOTELINE.ORIGIN IS '0 = Generated based on Order
1 = Manually added
2 = Generated based on Outscan
3 = Quantity Correction Ticket
4 = Stock Delivery Note
9 = Import from DW
10 = Mounting Order
11 = Sales Order
12 = Credit Note';

----INVOICELINESUBTYPE----

insert into invoicelinesubtype i
      (i.invoicelinesubtype_id,
       subtype,
       invoicelinetype_id,
       factorydescription,
       systemuser_id,
       timestamp)
      select seq_invoicelinesubtype.nextval,
             2,
             16,
             'Postage & Transport costs',
             1,
             sysdate
        from dual;
commit;

insert into invoicelinesubtype i
      (i.invoicelinesubtype_id,
       subtype,
       invoicelinetype_id,
       factorydescription,
       systemuser_id,
       timestamp)
      select seq_invoicelinesubtype.nextval,
             5,
             5,
             'General Sales Order Discount',
             1,
             sysdate
        from dual;
commit;

----REASONCODEDIALOGS----

INSERT INTO REASONCODEDIALOGS
  (REASONCODE_ID, DIALOG_ID, SELECTABLE, SYSTEMUSER_ID, TIMESTAMP)
  SELECT R.REASONCODE_ID,
         D.DIALOG_ID,
         'Y',
         GETMINSYSTEMUSER(),
         GETCURRENTDATETIME()
    FROM REASONCODE R,
         DIALOGS    D WHERE D.CODE IN ('SalesOrder');

COMMIT;

----EMPLOYEEFUNCTIONDIALOG----

INSERT INTO EMPLOYEEFUNCTIONDIALOG
  (EMPLOYEEFUNCTION_ID, DIALOG_ID, SELECTABLE, SYSTEMUSER_ID, TIMESTAMP)
  SELECT E.EMPLOYEEFUNCTION_ID,
         D.DIALOG_ID,
         'N',
         GETMINSYSTEMUSER(),
         GETCURRENTDATETIME()
    FROM EMPLOYEEFUNCTION E, DIALOGS D
   WHERE D.CODE IN ('SalesOrder');

COMMIT;