-- ABS-57622 (Flat File/.CSV) Interface 'Intertrade' and ABSSolute 
-- 1.6.4.1 EDI_I_CUSTPURCHASEORDER --
CREATE TABLE EDI_I_CUSTPURCHASEORDER
(
  TRANSFER_ID         NUMBER NOT NULL,
  TRANSFER_STATUS     NUMBER DEFAULT 0 NOT NULL
                        CONSTRAINT EDI_I_CUSPO_TRANSF_STAT_VAL1
                        CHECK (TRANSFER_STATUS IN (-1, 0, 1)),
  PLANTCODE           VARCHAR2(6) NOT NULL,
  PURCHASEORDERNUMBER VARCHAR2(25) NOT NULL,
  ORDERDATE           DATE DEFAULT SYSDATE NOT NULL,
  CUSTOMERNUMBER      NUMBER NULL,
  FILENAME            VARCHAR2(255) NULL,
  EMAILDOCUMENT_ID    NUMBER NULL,
  ACK_STATUS          NUMBER DEFAULT 0 NOT NULL
                        CONSTRAINT EDI_I_CUSPO_ACK_STATUS_VAL2
                        CHECK (ACK_STATUS IN (0, 1)),
  MESSAGETYPE         VARCHAR2(3) NULL,
  VERSIONNUMBER       VARCHAR2(12) NULL,
  ISA01               VARCHAR2(2) NULL,
  ISA02               VARCHAR2(10) NULL,
  ISA03               VARCHAR2(2) NULL,
  ISA04               VARCHAR2(10) NULL,
  ISA05               VARCHAR2(2) NULL,  
  ISA06               VARCHAR2(15) NULL,
  ISA07               VARCHAR2(2) NULL,
  ISA08               VARCHAR2(15) NULL,
  ISA09               VARCHAR2(6) NULL,
  ISA10               VARCHAR2(4) NULL,
  ISA11               VARCHAR2(1) NULL,
  ISA12               VARCHAR2(5) NULL,
  ISA13               VARCHAR2(9) NULL,
  ISA14               VARCHAR2(1) NULL,
  ISA15               VARCHAR2(1) NULL,
  ISA16               VARCHAR2(1) NULL,
  GS01                VARCHAR2(2) NULL,
  GS02                VARCHAR2(15) NULL,
  GS03                VARCHAR2(15) NULL,
  GS04                VARCHAR2(8) NULL,
  GS05                VARCHAR2(8) NULL,
  GS06                VARCHAR2(9) NULL,
  GS07                VARCHAR2(2) NULL,
  GS08                VARCHAR2(12) NULL,
  ST01                VARCHAR2(3) NULL,
  ST02                VARCHAR2(9) NULL,
  BEG01               VARCHAR2(2) NULL,
  BEG02               VARCHAR2(6) NULL,
  BEG03               VARCHAR2(22) NULL,
  BEG05               VARCHAR2(8) DEFAULT TO_CHAR(SYSDATE,'YYYYMMDD') NULL,
  REF01               VARCHAR2(2) NULL,
  REF02               VARCHAR2(30) NULL,
  DTM01               VARCHAR2(3) NULL,
  DTM02               VARCHAR2(8) NULL,
  CTT01               VARCHAR2(6) NULL,
  CTT02               VARCHAR2(10) NULL,
  SE01                VARCHAR2(10) NULL,
  SE02                VARCHAR2(9) NULL,
  GE01                VARCHAR2(6) NULL,
  GE02                VARCHAR2(9) NULL,
  IEA01               VARCHAR2(5) NULL,
  IEA02               VARCHAR2(9) NULL
);
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER.TRANSFER_ID
  IS 'Generated';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER.TRANSFER_STATUS
  IS '0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER.PLANTCODE
  IS 'Plant code where the process was run that imports the csv-file';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER.PURCHASEORDERNUMBER
  IS 'Purchase order number as used at customer. Max 15 characters in ABS';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER.ORDERDATE
  IS 'Order date, default current date';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER.CUSTOMERNUMBER
  IS 'Customer number in ABS';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER.FILENAME
  IS 'File name of the import file (if a file is ued)';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER.EMAILDOCUMENT_ID
  IS 'Link to the sent (exception) email';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER.ACK_STATUS
  IS 'Indicates if an Acknowledgement (855) has been prepared to be exported
0 = Not applicable/Not prepared
1 = Acknowledgment prepared';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER.MESSAGETYPE
  IS 'Transaction set type (same as ST01), most likely ''850'' for Intertade';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER.VERSIONNUMBER
  IS 'Version number (same as GS08), filled with ''00410'' for Intertrade';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER.ISA01
  IS 'ISA01 - Code to identify the type of information in the Authorization Information (e.g. ''00'' when not applicable )';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER.ISA02
  IS 'ISA02 - Information used for additional identification or authorization of the interchange sender or the data in the interchange; the type of information is set by the Authorization Information Qualifier (I01)';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER.ISA03
  IS 'ISA03 - Code to identify the type of information in the Security Information (e.g. ''00'' when not applicable )';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER.ISA04
  IS 'ISA04 - This is used for identifying the security information about the interchange sender or the data in the interchange; the type of information is set by the Security Information Qualifier (I03)';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER.ISA05
  IS 'ISA05 - Qualifier to designate the system/method of code structure used to designate the sender or receiver ID element being qualified
Possible values when filled:
"01" = Duns #
"08" =UCC EDI #
"12" =phone #
"14" = Duns + suffix #';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER.ISA06
  IS 'ISA06 - Identification code published by the sender for other parties to use as the receiver ID to route data to them; the sender always codes this value in the sender ID element';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER.ISA07
  IS 'ISA07 - Qualifier to designate the system/method of code structure used to designate the sender or receiver ID element being qualified
Possible values when filled:
"01" = Duns #
"08" =UCC EDI #
"12" =phone #
"14" = Duns + suffix #
"ZZ" = mutually defined';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER.ISA08
  IS 'ISA08 - Identification code published by the receiver of the data; When sending, it is used by the sender as their sending ID, thus other parties sending to them will use this as a receiving ID to route data to them';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER.ISA09
  IS 'ISA09 - Date of the interchange Format: YYMMDD';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER.ISA10
  IS 'ISA10 - Timestamp of the interchange; format HHMM';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER.ISA11
  IS 'ISA11- Code to identify the agency responsible for the control standard used by the message that is enclosed by the interchange header and trailer (e.g. ''U'')';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER.ISA12
  IS 'ISA12 - Code specifying the version number of the interchange control segments (e.g. ''00401'')';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER.ISA13
  IS 'ISA13 - Interchange control number assigned by the interchange sender';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER.ISA14
  IS 'ISA14 - Code sent by the sender to request an interchange acknowledgment (TA1, e.g. ''0'' when not applicable)';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER.ISA15
  IS 'ISA15 - Code to indicate whether data enclosed by this interchange envelope is test, production or information
Possible values when filled:
"P" = Production
"NE" = Test
"I" = Information';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER.ISA16
  IS 'ISA16 - this field provides the delimiter used to separate component data elements within a composite data structure; this value must be different than the data element separator and the segment terminator (e.g. ''|'')';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER.GS01
  IS 'GS01 - Functional identifier code for a group of application related transaction sets (e.g. ''PO'')';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER.GS02
  IS 'GS02 - Code to identify the party sending the transmission; codes agreed to by trading partners';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER.GS03
  IS 'GS03 - Code to identify the party receiving the transmission; codes agreed to by trading partners';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER.GS04
  IS 'GS04 - Date of the interchange Format: YYYYMMDD';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER.GS05
  IS 'GS05 - Timestamp of the interchange; format HHMI, HHMISS or HHMISSFF';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER.GS06
  IS 'GS06 - Group control number assigned, originated and maintained by sender';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER.GS07
  IS 'GS07 - Code identifying the issuer of the standard; this code is used in conjunction with data element 480';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER.GS08
  IS 'GS08 - Code indicating the version, release, sub release and industry identifier of the EDI standard being used, including the GS and GE segments
(e.g. ''004010'')';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER.ST01
  IS 'ST01 - Code uniquely identifying a transaction set (e.g. ''850'' Purchase Order)';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER.ST02
  IS 'ST02 - Identifying control number that must be unique within the transaction set functional group assigned by the originator for a transaction group';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER.BEG01
  IS 'BEG01 - Code identifying purpose of transaction set (e.g. ''00'' = Original)';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER.BEG02
  IS 'BEG02 - Code specifying the type of purchase order, Possible values when filled:
"DS" = Drop Ship
"NE" = New Order
"RO" = Rush Order
"SA" = Stand Alone';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER.BEG03
  IS 'BEG03 - Purchase order number as used at customer';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER.BEG05
  IS 'BEG05 - Order Date Format: YYYYMMDD, default current date';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER.REF01
  IS 'REF01 - Code to identify the type of reference (e.g. ''28'' Employee identification number)';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER.REF02
  IS 'REF02 - Reference information as defined for a specific transaction set or as specified by the reference identification qualifier';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER.DTM01
  IS 'DTM01 - Code specifying type of date or time, or both date and time
Possible values when filled: ''002'' = Delivery';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER.DTM02
  IS 'DTM02 - Requested delivery date Format: YYYYMMDD';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER.CTT01
  IS 'CTT01 - Total number of line items in the transaction set';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER.CTT02
  IS 'CTT02 - Sum of values of the specified data element';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER.SE01
  IS 'SE01 - Total number of segments included in a transaction set including ST and SE segments';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER.SE02
  IS 'SE02 - Identifying control number that must be unique within the transaction set functional group assigned by the originator for a transaction set';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER.GE01
  IS 'GE01 - Total number of transaction sets included in the functional group or interchange (transmission) group terminated by the trailer containing this data element';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER.GE02
  IS 'GE02 - Group control number, assigned, generated and maintained by the sender';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER.IEA01
  IS 'IEA01 - A count of the number of functional groups included in an interchange';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER.IEA02
  IS 'A control number assigned by the interchange sender'; 
ALTER TABLE EDI_I_CUSTPURCHASEORDER
  ADD CONSTRAINT PK_EDI_I_CUSTPURCHASEORDER PRIMARY KEY (TRANSFER_ID);
CREATE INDEX XIE1_EDI_I_CUSTPURCHASEORDER ON EDI_I_CUSTPURCHASEORDER(PLANTCODE);
CREATE INDEX XIE2_EDI_I_CUSTPURCHASEORDER ON EDI_I_CUSTPURCHASEORDER(PURCHASEORDERNUMBER);  
CREATE INDEX XIE3_EDI_I_CUSTPURCHASEORDER ON EDI_I_CUSTPURCHASEORDER(CUSTOMERNUMBER);

-- 1.6.4.2 EDI_I_CUSTPURCHASEORDER_MSG --
CREATE TABLE EDI_I_CUSTPURCHASEORDER_MSG
(
  TRANSFER_ID         NUMBER NOT NULL,
  TRANSFER_STATUS     NUMBER DEFAULT 0 NOT NULL
                        CONSTRAINT EDI_I_CUSTPURORD_MSG_TS_VAL1
                        CHECK (TRANSFER_STATUS IN (-1, 0, 1)),
  PURCHASEORDERNUMBER VARCHAR2(25) NOT NULL,            
  MESSAGELINENUMBER   NUMBER NOT NULL,
  MSG01               VARCHAR2(264) NOT NULL
);
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER_MSG.TRANSFER_ID
  IS 'Transfer id from edi_i_custpurchaseorder';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER_MSG.TRANSFER_STATUS
  IS '0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER_MSG.PURCHASEORDERNUMBER
  IS 'Purchase order number as used at customer. Max 15 characters in ABS';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER_MSG.MESSAGELINENUMBER
  IS 'Generated as last used line number + 1 for current transfer_id';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER_MSG.MSG01
  IS 'MSG01 - Free form message text';
ALTER TABLE EDI_I_CUSTPURCHASEORDER_MSG
  ADD CONSTRAINT PK_EDI_I_CUSTPURCHASEORDR_MSG PRIMARY KEY (TRANSFER_ID, PURCHASEORDERNUMBER, MESSAGELINENUMBER);
CREATE INDEX XIE1_EDI_I_CUSTPURCORD_MSG ON EDI_I_CUSTPURCHASEORDER_MSG (TRANSFER_ID);
CREATE INDEX XIE2_EDI_I_CUSTPURCORD_MSG ON EDI_I_CUSTPURCHASEORDER_MSG (PURCHASEORDERNUMBER);
ALTER TABLE EDI_I_CUSTPURCHASEORDER_MSG
  ADD CONSTRAINT  FK01_EDI_I_CUSTPURCHORD_MSG FOREIGN KEY (TRANSFER_ID)
  REFERENCES EDI_I_CUSTPURCHASEORDER (TRANSFER_ID) ON DELETE CASCADE; 
  
-- 1.6.4.3 EDI_I_CUSTPURCHASEORDER_COM --
CREATE TABLE EDI_I_CUSTPURCHASEORDER_COM
(
  TRANSFER_ID         NUMBER NOT NULL,
  TRANSFER_STATUS     NUMBER DEFAULT 0 NOT NULL
                        CONSTRAINT EDI_I_CUSPURORD_COM_TS_VAL1
                        CHECK (TRANSFER_STATUS IN (-1, 0, 1)),
  PURCHASEORDERNUMBER VARCHAR2(25) NOT NULL,            
  MESSAGELINENUMBER   NUMBER NOT NULL,
  PER01               VARCHAR2(3) NOT NULL,
  PER02               VARCHAR2(60) NULL,
  PER03               VARCHAR2(2) NULL,
  PER04               VARCHAR2(80) NULL
);
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER_COM.TRANSFER_ID
  IS 'Transfer id from edi_i_custpurchaseorder';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER_COM.TRANSFER_STATUS
  IS '0 = ready to be read, 1 = read correctly';  
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER_COM.PURCHASEORDERNUMBER
  IS 'Purchase order number as used at customer. Max 15 characters in ABS';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER_COM.MESSAGELINENUMBER
  IS 'Generated as last used line number + 1 for current transfer_id';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER_COM.PER01
  IS 'PER01- Code identifying the major duty or responsibility of the person or group named
Possible values when filled:
"BD"= Buyer name or Department';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER_COM.PER02
  IS 'PER02 - Free form name';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER_COM.PER03
  IS 'PER03 - Code identifying the type of communication number 
Possible values when filled:
"TE" = Telephone 
"FX" = Fax 
"EM" = Email';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER_COM.PER04
  IS 'PER04 - Complete communications number including country or area code when applicable';
ALTER TABLE EDI_I_CUSTPURCHASEORDER_COM
  ADD CONSTRAINT PK_EDI_I_CUSTPURCHASEORDR_COM PRIMARY KEY (TRANSFER_ID, PURCHASEORDERNUMBER, MESSAGELINENUMBER);
CREATE INDEX XIE1_EDI_I_CUSTPURCORD_COM ON EDI_I_CUSTPURCHASEORDER_COM (TRANSFER_ID); 
CREATE INDEX XIE2_EDI_I_CUSTPURCORD_COM ON EDI_I_CUSTPURCHASEORDER_COM (PURCHASEORDERNUMBER); 
ALTER TABLE EDI_I_CUSTPURCHASEORDER_COM
  ADD CONSTRAINT  FK01_EDI_I_CUSTPURCHORD_COM FOREIGN KEY (TRANSFER_ID)
  REFERENCES EDI_I_CUSTPURCHASEORDER (TRANSFER_ID) ON DELETE CASCADE; 
  
-- 1.6.4.4 EDI_I_CUSTPURCHASEORDER_NAME --
CREATE TABLE EDI_I_CUSTPURCHASEORDER_NAME
(
  TRANSFER_ID         NUMBER NOT NULL,
  TRANSFER_STATUS     NUMBER DEFAULT 0 NOT NULL
                        CONSTRAINT EDI_I_CUSPURORD_NAME_TS_VAL1
                        CHECK (TRANSFER_STATUS IN (-1, 0, 1)),
  PURCHASEORDERNUMBER VARCHAR2(25) NOT NULL,            
  MESSAGELINENUMBER   NUMBER NOT NULL,
  N101                VARCHAR2(3) NOT NULL,
  N102                VARCHAR2(60) NULL,
  N103                VARCHAR2(2) NULL,
  N104                VARCHAR2(80) NULL,
  N301                VARCHAR2(55) NULL,
  N302                VARCHAR2(55) NULL,  
  N401                VARCHAR2(30) NULL,
  N402                VARCHAR2(2) NULL,
  N403                VARCHAR2(15) NULL,
  N404                VARCHAR2(3) NULL
);
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER_NAME.TRANSFER_ID
  IS 'Transfer id from edi_i_custpurchaseorder';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER_NAME.TRANSFER_STATUS
  IS '0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER_NAME.PURCHASEORDERNUMBER
  IS 'Purchase order number as used at customer. Max 15 characters in ABS';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER_NAME.MESSAGELINENUMBER
  IS 'Generated as last used line number + 1 for current transfer_id';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER_NAME.N101
  IS 'N101- Code identifying an organizational entity, a physical location, property or an individual
Possible values when filled:
"BT" = Bill-to-Party
"ST" = Ship To
"VN"= Vendor';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER_NAME.N102
  IS 'N102 - Free form name';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER_NAME.N103
  IS 'N103 - Code designating the system/method of code structure used for Identification Code (e.g. ''ZZ'')
Possible values when filled:
"91" = Assigned by Seller or Seller''s Agent
"92" = Assigned by Buyer or Buyer''s Agent
"ZZ" = Mutually defined "ZZ" = Mutually defined';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER_NAME.N104
  IS 'N104 - Code identifying a party or other code (Consumption point in ABS)';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER_NAME.N301
  IS 'N301 - Address information';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER_NAME.N302
  IS 'N302 - Address information';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER_NAME.N401
  IS 'N401 - Free form text for city';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER_NAME.N402
  IS 'N402 - Code (Standard State/Province) as defined by appropriate government agency';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER_NAME.N403
  IS 'N403 - Code defining international postal zone code excluding punctuation and blanks (zip code for United States';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER_NAME.N404
  IS 'N404 - Code identifying the country';
ALTER TABLE EDI_I_CUSTPURCHASEORDER_NAME
  ADD CONSTRAINT PK_EDI_I_CUSTPURCHASEORD_NAME PRIMARY KEY (TRANSFER_ID, PURCHASEORDERNUMBER, MESSAGELINENUMBER);
CREATE INDEX XIE1_EDI_I_CUSTPURCORD_NAME ON EDI_I_CUSTPURCHASEORDER_NAME (TRANSFER_ID);   
CREATE INDEX XIE2_EDI_I_CUSTPURCORD_NAME ON EDI_I_CUSTPURCHASEORDER_NAME (PURCHASEORDERNUMBER);
ALTER TABLE EDI_I_CUSTPURCHASEORDER_NAME
  ADD CONSTRAINT  FK01_EDI_I_CUSTPURCHORD_NAME FOREIGN KEY (TRANSFER_ID)
  REFERENCES EDI_I_CUSTPURCHASEORDER (TRANSFER_ID) ON DELETE CASCADE;
  
-- 1.6.4.5	EDI_I_CUSTPURCHASEORDERLINE. Contains also TEXT+Process status for support/export results --
CREATE TABLE EDI_I_CUSTPURCHASEORDERLINE
(
  TRANSFER_ID               NUMBER NOT NULL,
  TRANSFER_STATUS           NUMBER DEFAULT 0 NOT NULL
                              CONSTRAINT EDI_I_CUSTPURORDLINE_TS_VAL1
                              CHECK (TRANSFER_STATUS IN(-1, 0, 1)),
  PURCHASEORDERNUMBER       VARCHAR2(25) NOT NULL,            
  PURCHASEORDERLINENUMBER   NUMBER NOT NULL,
  CUSTOMERNUMBER		    NUMBER NULL,
  CUSTOMERPRODUCTCODE	    VARCHAR2(16) NULL,
  CUSTOMERPRODUCTDESC	    VARCHAR2(30) NULL,
  PRODUCTCODE	            VARCHAR2(12) NULL,
  SIZECODE	                VARCHAR2(12) NULL,
  FINISHINGMETHODCODE	    VARCHAR2(12) NULL,
  PRODUCTLISTPERCUSTOMER_ID	NUMBER NULL,
  DELIVERYVARIATIONS_ID	    NUMBER NULL,
  PO101	                    VARCHAR2(20) NULL,
  PO102	                    VARCHAR2(15) NULL,
  PO103                    	VARCHAR2(2) NULL,
  PO104                    	VARCHAR2(17) NULL,
  PO105                    	VARCHAR2(2) NULL,
  PO106                    	VARCHAR2(2) NULL,
  PO107                    	VARCHAR2(48) NULL,
  PO108                    	VARCHAR2(2) NULL,
  PO109                    	VARCHAR2(48) NULL,
  PO110                    	VARCHAR2(2) NULL,
  PO111                    	VARCHAR2(48) NULL,
  PO112                    	VARCHAR2(2) NULL,
  PO113                    	VARCHAR2(48) NULL,
  PO114                     	VARCHAR2(2) NULL,
  PO115                     	VARCHAR2(48) NULL,
  PID01                    	VARCHAR2(1) NULL,
  PID02                    	VARCHAR2(3) NULL,
  PID03                    	VARCHAR2(2) NULL,
  PID04                    	VARCHAR2(12) NULL,
  PID05                    	VARCHAR2(80) NULL,
  TEXT                      VARCHAR2(1000) NULL,  
  PROCESS_STATUS            NUMBER DEFAULT 0 NOT NULL
                              CONSTRAINT EDI_I_CUSTPURORDLINE_PS_VAL2
                              CHECK (PROCESS_STATUS IN (0, 1))
);
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDERLINE.TRANSFER_ID
  IS 'Transfer id from edi_i_custpurchaseorder';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDERLINE.TRANSFER_STATUS
  IS '0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDERLINE.PURCHASEORDERNUMBER
  IS 'Purchase order number as used at customer. Max 15 characters in ABS';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDERLINE.PURCHASEORDERLINENUMBER
  IS 'Generated as last used line number + 1 for current transfer_id';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDERLINE.CUSTOMERPRODUCTCODE
  IS 'PLPC.CustomCode';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDERLINE.CUSTOMERPRODUCTDESC
  IS 'PLPC.CustomDescription';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDERLINE.PRODUCTCODE
  IS 'Link to product code';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDERLINE.SIZECODE
  IS 'Link to size code';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDERLINE.FINISHINGMETHODCODE
  IS 'Link to finishing method code';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDERLINE.PRODUCTLISTPERCUSTOMER_ID
  IS 'Link to PLPC';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDERLINE.DELIVERYVARIATIONS_ID
  IS 'Link to DeliveryVariations';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDERLINE.PO101
  IS 'PO101 - Alphanumeric characters assigned for differentiation within a transaction set';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDERLINE.PO102
  IS 'PO102 - Quantity ordered';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDERLINE.PO103
  IS 'PO103 - Code specifying the units in which a value is being expressed, or manner in
which a measurement has been taken
Possible values when filled:
"EA" = Each
"CA" = Case';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDERLINE.PO104
  IS 'PO104 - Price per unit of product, service, commodity, etc.';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDERLINE.PO105
  IS 'PO105 - Code identifying the type of unit price for an item
Possible values when filled:
"HP" = Price per Hundred
"NC" = No Charge
"PE"=  PE Price per Each
"PF" = Price Per Foot
"PP" = Price per Pound
Not used for Intertrade';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDERLINE.PO106
  IS 'PO106 - Code identifying the type/source of the descriptive number used in Product/Service ID (234)
Possible values when filled
"VC" = Vendor''s (Seller''s) Catalog Number
"VN" = Vendor''s (Seller''s) Item Number';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDERLINE.PO107
  IS 'PO107 -  Identifying number for a product or service';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDERLINE.PO108
  IS 'PO108 - Code identifying the type/source of the descriptive number used in
Product/Service ID (234)
Possible values when filled:
"IN" = Buyer''s Item Number';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDERLINE.PO109
  IS 'PO109 - Identifying number for a product or service';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDERLINE.PO110
  IS 'PO110 - Code identifying the type/source of the descriptive number used in
Product/Service ID (234)
Possible values when filled
"SN" = Serial Number
"LT" = Lot Number
"CR"= Contract number';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDERLINE.PO111
  IS 'PO111 - Identifying number for a product or service';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDERLINE.PO112
  IS 'PO112 - Code identifying the type/source of the descriptive number used in
Product/Service ID (234)
Possible values when filled
"SN" = Serial Number
"LT" = Lot Number
"CR"= Contract number';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDERLINE.PO113
  IS 'PO113 - Identifying number for a product or service (e.g. serial number)';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDERLINE.PO114
  IS 'PO114 - Identifying number for a product or service
Possible values when filled
"SN" = Serial Number
"LT" = Lot Number
"CR"= Contract number';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDERLINE.PO115
  IS 'PO115 - Identifying number for a product or service (e.g. lot number)';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDERLINE.PID01
  IS 'PID01 - Code indicating the format of a description';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDERLINE.PID02
  IS 'PID02 - Code identifying the general class of a product or process characteristic, Not used for Intertrade';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDERLINE.PID03
  IS 'PID03 - Code identifying the agency assigning the code values. Not used for Intertrade';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDERLINE.PID04
  IS 'PID04 - A code from an industry code list which provides specific data about a product characteristic. Not used for Intertrade';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDERLINE.PID05
  IS 'PID05 - A free-form description to clarify the related data elements and their content';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDERLINE.TEXT
  IS 'Optional (error) message related to process_status';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDERLINE.PROCESS_STATUS
  IS '0 = processed correctly, 1 = process stopped with errors';
ALTER TABLE EDI_I_CUSTPURCHASEORDERLINE
  ADD CONSTRAINT PK_EDI_I_CUSTPURORDLINE PRIMARY KEY (TRANSFER_ID, PURCHASEORDERNUMBER, PURCHASEORDERLINENUMBER);
CREATE INDEX XIE1_EDI_I_CUSTPURORDERLINE ON EDI_I_CUSTPURCHASEORDERLINE (TRANSFER_ID);   
ALTER TABLE EDI_I_CUSTPURCHASEORDERLINE
  ADD CONSTRAINT  FK01_EDI_I_CUSTPURORDLINE FOREIGN KEY (TRANSFER_ID)
  REFERENCES EDI_I_CUSTPURCHASEORDER (TRANSFER_ID) ON DELETE CASCADE;  
  
-- 1.6.4.6  EDI_I_CUSTPURCHASEORDER_LOG --
CREATE TABLE EDI_I_CUSTPURCHASEORDER_LOG
(
  TRANSFER_ID         NUMBER NOT NULL,
  TRANSFER_STATUS     NUMBER DEFAULT 0 NOT NULL
                        CONSTRAINT EDI_I_CUSTPURCORD_LOG_TS_VAL1
                        CHECK (TRANSFER_STATUS IN(-1, 0, 1)),
  ACK_STATUS          NUMBER DEFAULT 0 NOT NULL
                        CONSTRAINT EDI_I_CUSPURORD_LOG_ACKST_VAL2
                        CHECK (ACK_STATUS IN(0, 1)),
  TEXT                VARCHAR2(1000) NULL,
  PROCESS_STATUS      NUMBER DEFAULT 0 NOT NULL
                        CONSTRAINT EDI_I_CUSTPURCORD_LOG_PS_VAL3
                        CHECK (PROCESS_STATUS IN(0, 1)),
  TIMESTAMP           DATE DEFAULT SYSDATE NOT NULL
);  
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER_LOG.TRANSFER_ID
  IS 'Transfer id from edi_i_custpurchaseorder';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER_LOG.TRANSFER_STATUS
  IS '0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER_LOG.ACK_STATUS
  IS 'Indicates if an Acknowledgement (855) has been prepared to be exported
0 = Not applicable/Not prepared
1 = Acknowledgment prepared';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER_LOG.TEXT
  IS 'In case if errors PROCESS_STATUS=1, displays the error, or the ok message';
COMMENT ON COLUMN EDI_I_CUSTPURCHASEORDER_LOG.PROCESS_STATUS
  IS '0 = processed correctly, 1 = process stopped with errors';
ALTER TABLE EDI_I_CUSTPURCHASEORDER_LOG
  ADD CONSTRAINT PK_EDI_I_CUSTPURCHASEORD_LOG PRIMARY KEY (TRANSFER_ID);
ALTER TABLE EDI_I_CUSTPURCHASEORDER_LOG
  ADD CONSTRAINT FK01_EDI_I_CUSTPURCHORD_LOG FOREIGN KEY (TRANSFER_ID)
  REFERENCES EDI_I_CUSTPURCHASEORDER (TRANSFER_ID) ON DELETE CASCADE;    