-- ABS-70820 / ABS-72025
--- CONTRACT ---
ALTER TABLE CONTRACT ADD (STORENUMBERMASTER VARCHAR2(1) DEFAULT 'N' NOT NULL
			  	CONSTRAINT YN_VALIDATIONFIELD451 CHECK (STORENUMBERMASTER IN ('Y','N')));

--- CONTRACTPURCHASEORDERLINE ---
ALTER TABLE CONTRACTPURCHASEORDERLINE ADD (INVOICEDAMOUNT NUMBER DEFAULT 0 NOT NULL,
					   SERVICETYPE NUMBER DEFAULT 0 NOT NULL
					   	CONSTRAINT SERVICETYPE_VALID2 CHECK (SERVICETYPE BETWEEN 0 AND 4),
					   CANCELLED VARCHAR2(1) DEFAULT 'N' NOT NULL
					   	CONSTRAINT YN_VALIDATIONFIELD452 CHECK (CANCELLED IN ('Y','N')));

COMMENT ON COLUMN CONTRACTPURCHASEORDERLINE.SERVICETYPE IS '0 = no service
1 = weekly service
2 = 2 weekly service
3 = 4 weekly service
4 = loss charge';

--- PRODUCTSERVICECATALOGUE ---
CREATE SEQUENCE SEQ_PRODUCTSERVICECATALOGUE
START WITH 1
INCREMENT BY 1;

CREATE TABLE PRODUCTSERVICECATALOGUE
(
	PRODUCTSERVICECATALOGUE_ID NUMBER NOT NULL,
	ITEMNUMBER VARCHAR2(20) NOT NULL,
	ITEMDESCRIPTION VARCHAR2(200) NOT NULL,
	PRODUCT_LINK_ID NUMBER NOT NULL,
	SERVICETYPE NUMBER NOT NULL CONSTRAINT SERVICETYPE_VALID3 CHECK (SERVICETYPE BETWEEN 0 AND 4)
);

COMMENT ON COLUMN PRODUCTSERVICECATALOGUE.ITEMNUMBER IS 'USPS product ID';

COMMENT ON COLUMN PRODUCTSERVICECATALOGUE.SERVICETYPE IS '0 = no service
1 = weekly service
2 = 2 weekly service
3 = 4 weekly service
4 = loss charge';

ALTER TABLE PRODUCTSERVICECATALOGUE ADD CONSTRAINT XPKPRODUCTSERVICECATALOGUE PRIMARY KEY (PRODUCTSERVICECATALOGUE_ID);

CREATE UNIQUE INDEX XAK1PRODUCTSERVICECATALOGUE ON PRODUCTSERVICECATALOGUE (ITEMNUMBER ASC);
CREATE UNIQUE INDEX XAK2PRODUCTSERVICECATALOGUE ON PRODUCTSERVICECATALOGUE (PRODUCT_LINK_ID ASC, SERVICETYPE ASC);
CREATE INDEX XIF1PRODUCTSERVICECATALOGUE ON PRODUCTSERVICECATALOGUE (PRODUCT_LINK_ID ASC);

ALTER TABLE PRODUCTSERVICECATALOGUE ADD CONSTRAINT PRODUCTSERVICECATALOGUE_PID FOREIGN KEY (PRODUCT_LINK_ID)
REFERENCES PRODUCT_LINK (PRODUCT_LINK_ID);

--- EDI_I_CONTRACTPO ---
CREATE SEQUENCE SEQ_EDI_I_CONTRACTPO
START WITH 1
INCREMENT BY 1;

CREATE TABLE EDI_I_CONTRACTPO
(
	EDI_I_CONTRACTPO_ID NUMBER NOT NULL,
	SUPPLIERID NUMBER NOT NULL,
	TRANSACTIONSETID NUMBER NOT NULL,
	RECORDTYPEID NUMBER NOT NULL,
	ITEMNUMBER VARCHAR2(20) NOT NULL,
	TRANSPORTATIONMETHOD VARCHAR2(1) NOT NULL,
	USPSORDERNUMBER VARCHAR2(40) NOT NULL,
	USPSORDERLINE NUMBER NOT NULL,
	REQUISITIONERID VARCHAR2(30) NOT NULL,
	AGGREGATIONCODE VARCHAR2(30) NULL,
	POENTRYDATE NUMBER NOT NULL,
	REQUIREDDATE NUMBER NOT NULL,
	ORDERPRIORITY NUMBER NOT NULL,
	ORDERQTY NUMBER NOT NULL,
	UNITOFMEASURE VARCHAR2(2) NOT NULL,
	ITEMAGREEDPRICE NUMBER NOT NULL,
	CUSTOMERNAME VARCHAR2(40) NOT NULL,
	CUSTOMERADDRESSLINE1 VARCHAR2(30) NOT NULL,
	CUSTOMERADDRESSLINE2 VARCHAR2(30) NOT NULL,
	CUSTOMERADDRESSLINE3 VARCHAR2(30) NOT NULL,
	CUSTOMERCITY VARCHAR2(30) NOT NULL,
	CUSTOMERSTATE VARCHAR2(20) NOT NULL,
	CUSTOMERZIPCODE VARCHAR2(15) NOT NULL,
	REQUISITIONERPHONE VARCHAR2(30) NOT NULL,
	REQUISITIONERFAX VARCHAR2(30) NULL,
	REQUISITIONEREMAIL VARCHAR2(300) NOT NULL,
	ALTERNATEADDRCD VARCHAR2(1) NOT NULL,
	REQUISITIONERNAME VARCHAR2(100) NOT NULL,
	FEDSTRIPNUMBER VARCHAR2(10) NOT NULL,
	PAYMENTMETHOD VARCHAR2(2) NOT NULL,
	PAYMENTNUMBER NUMBER NOT NULL,
	CONTRACTNUMBER VARCHAR2(30) NOT NULL,
	CONTRACTLINENUMBER NUMBER NULL,
	ATTACHMENTFILENAME VARCHAR2(50) NULL,
	ORDERNOTES VARCHAR2(200) NULL,
	ORDERLINENOTES VARCHAR2(200) NULL,
	TRANSACTIONSTATUS VARCHAR2(1) NOT NULL,
	FILENAME VARCHAR2(200) NULL,
	PROCESSED VARCHAR2(1) DEFAULT 'N' NOT NULL,
	CREATEDDATE DATE NOT NULL,
	SYSTEMUSER_ID NUMBER NOT NULL,
	TIMESTAMP DATE NOT NULL
);

ALTER TABLE EDI_I_CONTRACTPO ADD CONSTRAINT XPKEDI_I_CONTRACTPO PRIMARY KEY (EDI_I_CONTRACTPO_ID);

CREATE UNIQUE INDEX XAK1EDI_I_CONTRACTPO ON EDI_I_CONTRACTPO (USPSORDERNUMBER ASC, ITEMNUMBER ASC);

--- EDI_O_CONTRACTPOACK ---
CREATE SEQUENCE SEQ_EDI_O_CONTRACTPOACK
START WITH 1
INCREMENT BY 1;

CREATE TABLE EDI_O_CONTRACTPOACK
(
	EDI_O_CONTRACTPOACK_ID NUMBER NOT NULL,
	SUPPLIERID NUMBER NOT NULL,
	TRANSACTIONSETID NUMBER DEFAULT 855 NOT NULL,
	RECORDTYPEID NUMBER DEFAULT '01' NOT NULL,
	FEDSTRIPNUMBER VARCHAR2(10) NOT NULL,
	SUPPLIERORDERNUMBER VARCHAR2(40) NOT NULL,
	CUSTOMERORDERNUMBER VARCHAR2(40) NOT NULL,
	CUSTOMERORDERLINE NUMBER NULL,
	POENTRYDATE NUMBER NOT NULL,
	REQUIREDDATE NUMBER NOT NULL,
	ORDERMETHOD VARCHAR2(1) DEFAULT 'E' NOT NULL,
	PAYMENTMETHOD VARCHAR2(2) DEFAULT 'FN' NOT NULL,
	PAYMENTNUMBER NUMBER NOT NULL,
	CONTRACTNUMBER VARCHAR2(30) NOT NULL,
	CONTRACTLINENUMBER NUMBER NULL,
	ITEMNUMBER VARCHAR2(20) NOT NULL,
	ORDERQTY NUMBER NOT NULL,
	UNITOFMEASURE VARCHAR2(2) DEFAULT 'EA' NOT NULL,
	REPLACEMENTITEM VARCHAR2(30) NULL,
	CUSTOMERNAME VARCHAR2(90) NOT NULL,
	CUSTOMERADDRESSLINE1 VARCHAR2(30) NOT NULL,
	CUSTOMERADDRESSLINE2 VARCHAR2(30) NOT NULL,
	CUSTOMERADDRESSLINE3 VARCHAR2(30) NOT NULL,
	CUSTOMERCITY VARCHAR2(30) NOT NULL,
	CUSTOMERSTATE VARCHAR2(20) NOT NULL,
	CUSTOMERZIPCODE VARCHAR2(15) NOT NULL,
	TRANSPORTATIONMETHOD VARCHAR2(1) DEFAULT 'F' NOT NULL,
	REQUISITIONERNAME VARCHAR2(90) NOT NULL,
	REQUISITIONERPHONE VARCHAR2(30) NOT NULL,
	POLINESTATUSCODE VARCHAR2(2) DEFAULT 'IA' NOT NULL,
	POLINESTATUSQTY NUMBER NOT NULL,
	POLINESTATUSUOM VARCHAR2(2) DEFAULT 'EA' NOT NULL,
	POLINESTATUSCODE2 VARCHAR2(1) NULL,
	POLINESTATUSQTY2 NUMBER NULL,
	POLINESTATUSUOM2 VARCHAR2(2) NULL,
	PLANNEDSHIPDATE NUMBER NULL,
	AGGREGATIONCODE VARCHAR2(30) NULL,
	ORDERNOTES VARCHAR2(200) NULL,
	ORDERLINENOTES VARCHAR2(200) NULL,
	TRANSACTIONSTATUS VARCHAR2(1) DEFAULT 'P' NOT NULL,
	FILENAME VARCHAR2(200) NULL,
	SYSTEMUSER_ID NUMBER NOT NULL,
	TIMESTAMP DATE NOT NULL
);

ALTER TABLE EDI_O_CONTRACTPOACK ADD CONSTRAINT XPKEDI_O_CONTRACTPOACK PRIMARY KEY (EDI_O_CONTRACTPOACK_ID);

CREATE UNIQUE INDEX XAK1EDI_O_CONTRACTPOACK ON EDI_O_CONTRACTPOACK (CUSTOMERORDERNUMBER ASC, ITEMNUMBER ASC);

--- EDI_O_CONTRACTPOCANC ---
CREATE SEQUENCE SEQ_EDI_O_CONTRACTPOCANC
START WITH 1
INCREMENT BY 1;

CREATE TABLE EDI_O_CONTRACTPOCANC
(
	EDI_O_CONTRACTPOCANC_ID NUMBER NOT NULL,
	SUPPLIERID NUMBER NOT NULL,
	TRANSACTIONSETID NUMBER DEFAULT 870 NOT NULL,
	RECORDTYPEID NUMBER DEFAULT '01' NOT NULL,
	USPSORDERNUMBER VARCHAR2(40) NOT NULL,
	SUPPLIERORDERNUMBER VARCHAR2(40) NOT NULL,
	USPSORDERLINE NUMBER NOT NULL,
	ITEMNUMBER VARCHAR2(20) NOT NULL,
	STATUSITEMNUMBER VARCHAR2(30) NULL,
	STATUSDATE NUMBER NOT NULL,
	PLANNEDSHIPDATE NUMBER NULL,
	ORDERPRIORITY NUMBER DEFAULT 0 NOT NULL,
	POLINESTATUSCODE VARCHAR2(2) DEFAULT 'CK' NOT NULL,
	POLINESTATUSQTY NUMBER NOT NULL,
	POLINESTATUSUOM VARCHAR2(2) DEFAULT 'EA' NOT NULL,
	NOTES VARCHAR2(200) DEFAULT 'Closed' NOT NULL,
	TRANSACTIONSTATUS VARCHAR2(1) DEFAULT 'P' NOT NULL,
	FILENAME VARCHAR2(200) NULL,
	SYSTEMUSER_ID NUMBER NOT NULL,
	TIMESTAMP DATE NOT NULL
);

ALTER TABLE EDI_O_CONTRACTPOCANC ADD CONSTRAINT XPKEDI_O_CONTRACTPOCANC PRIMARY KEY (EDI_O_CONTRACTPOCANC_ID);

CREATE UNIQUE INDEX XAK1EDI_O_CONTRACTPOCANC ON EDI_O_CONTRACTPOCANC (USPSORDERNUMBER ASC, USPSORDERLINE ASC);

--- EDI_O_INVOICE ---
CREATE SEQUENCE SEQ_EDI_O_INVOICE
START WITH 1
INCREMENT BY 1;

CREATE TABLE EDI_O_INVOICE
(
	EDI_O_INVOICE_ID NUMBER NOT NULL,
	SUPPLIERID NUMBER NOT NULL,
	TRANSACTIONSETID NUMBER DEFAULT 857 NOT NULL,
	RECORDTYPEID NUMBER NOT NULL CONSTRAINT RECORDTYPEID_VALID1 CHECK (RECORDTYPEID IN ('01','02')),
	SHIPMENTID NUMBER NOT NULL,
	SHIPMENTCITY VARCHAR2(30) NOT NULL,
	SHIPMENTSTATE VARCHAR2(20) NOT NULL,
	SHIPMENTZIPCODE VARCHAR2(15) NOT NULL,
	TRACKINGNUMBER VARCHAR2(30) NULL,
	BILLOFLADINGNUMBER VARCHAR2(30) NULL,
	CARRIERINVOICENUMBER VARCHAR2(30) NULL,
	CARRIERID VARCHAR2(30) NULL,
	SHIPDATE NUMBER NOT NULL,
	FEDSTRIPNUMBER VARCHAR2(10) NOT NULL,
	TRANSACTIONSTATUS VARCHAR2(1) DEFAULT 'P' NOT NULL,
	FILENAME VARCHAR2(200) NULL,
	INVOICENUMBER NUMBER NOT NULL,
	INVOICELINE NUMBER NOT NULL,
	CONTRACTNUMBER VARCHAR2(30) NOT NULL,
	CONTRACTLINENUMBER NUMBER NULL,
	USPSORDERNUMBER VARCHAR2(40) NOT NULL,
	SUPPLIERORDERNUMBER VARCHAR2(40) NOT NULL,
	USPSORDERLINE NUMBER NOT NULL,
	ITEMNUMBER VARCHAR2(20) NOT NULL,
	UNITOFMEASURE VARCHAR2(2) DEFAULT 'EA' NOT NULL,
	INVOICEQUANTITY NUMBER NOT NULL,
	INVOICEAMOUNT NUMBER NOT NULL,
	SHIPMENTQUANTITY NUMBER NOT NULL,
	SHIPMENTTAX NUMBER DEFAULT 0 NOT NULL,
	SHIPMENTFREIGHT NUMBER DEFAULT 0 NOT NULL,
	ITEMSERIALNUMBER VARCHAR2(30) NULL,
	PROPERTYCONTROLNO VARCHAR2(30) NULL,
	SYSTEMUSER_ID NUMBER NOT NULL,
	TIMESTAMP DATE NOT NULL
);

COMMENT ON COLUMN EDI_O_INVOICE.RECORDTYPEID IS '01 = header, 02 = detail line';

COMMENT ON COLUMN EDI_O_INVOICE.SHIPMENTID IS 'ABSSolute Invoice Number';

COMMENT ON COLUMN EDI_O_INVOICE.SHIPDATE IS 'ABSSolute Invoice date';

COMMENT ON COLUMN EDI_O_INVOICE.FEDSTRIPNUMBER IS 'Store Number';

COMMENT ON COLUMN EDI_O_INVOICE.INVOICENUMBER IS 'ABSSolute Invoice Number';

COMMENT ON COLUMN EDI_O_INVOICE.INVOICELINE IS 'Invoice Line';

ALTER TABLE EDI_O_INVOICE ADD CONSTRAINT XPKEDI_O_INVOICE PRIMARY KEY (EDI_O_INVOICE_ID);