-- ABS-57622 (Flat File/.CSV) Interface 'Intertrade' and ABSSolute 
-- 1.6.5.1	EDI_O_PURCHASEORDERACK --
CREATE TABLE EDI_O_PURCHASEORDERACK
(
  TRANSFER_ID         NUMBER NOT NULL,
  TRANSFER_STATUS     NUMBER DEFAULT 0 NOT NULL
                        CONSTRAINT EDI_O_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_O_CUSPO_ACK_STATUS_VAL2
                        CHECK (ACK_STATUS IN (0, 1)),
  MESSAGETYPE         VARCHAR2(3) NULL,
  VERSIONNUMBER       VARCHAR2(12) NULL,
  INCOMINGTRANSFER_ID NUMBER 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,
  BAK01               VARCHAR2(2) NULL,
  BAK02               VARCHAR2(6) NULL,
  BAK03               VARCHAR2(22) NULL,
  BAK04               VARCHAR2(8) NULL,
  BAK05               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_O_PURCHASEORDERACK.TRANSFER_ID
  IS 'Generated';
COMMENT ON COLUMN EDI_O_PURCHASEORDERACK.TRANSFER_STATUS
  IS '0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN EDI_O_PURCHASEORDERACK.PLANTCODE
  IS 'Plant code where the process was run that imports the csv-file';
COMMENT ON COLUMN EDI_O_PURCHASEORDERACK.PURCHASEORDERNUMBER
  IS 'Purchase order number as used at customer. Max 15 characters in ABS';
COMMENT ON COLUMN EDI_O_PURCHASEORDERACK.ORDERDATE
  IS 'Order date, default current date';
COMMENT ON COLUMN EDI_O_PURCHASEORDERACK.CUSTOMERNUMBER
  IS 'Customer number in ABS';
COMMENT ON COLUMN EDI_O_PURCHASEORDERACK.FILENAME
  IS 'File name of the import file (if a file is ued)';
COMMENT ON COLUMN EDI_O_PURCHASEORDERACK.EMAILDOCUMENT_ID
  IS 'Link to the sent (exception) email';
COMMENT ON COLUMN EDI_O_PURCHASEORDERACK.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_O_PURCHASEORDERACK.MESSAGETYPE
  IS 'Transaction set type (same as ST01), most likely ''850'' for Intertade';
COMMENT ON COLUMN EDI_O_PURCHASEORDERACK.VERSIONNUMBER
  IS 'Version number (same as GS08), filled with ''00410'' for Intertrade';
COMMENT ON COLUMN EDI_O_PURCHASEORDERACK.INCOMINGTRANSFER_ID
  IS 'Transfer id from incoming edi_i_PURCHASEORDACKer for which the acknowledgement is made';
COMMENT ON COLUMN EDI_O_PURCHASEORDERACK.ISA01
  IS 'ISA01 - Code to identify the type of information in the Authorization Information (e.g. ''00'' when not applicable )';
COMMENT ON COLUMN EDI_O_PURCHASEORDERACK.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_O_PURCHASEORDERACK.ISA03
  IS 'ISA03 - Code to identify the type of information in the Security Information (e.g. ''00'' when not applicable )';
COMMENT ON COLUMN EDI_O_PURCHASEORDERACK.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_O_PURCHASEORDERACK.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_O_PURCHASEORDERACK.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_O_PURCHASEORDERACK.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_O_PURCHASEORDERACK.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_O_PURCHASEORDERACK.ISA09
  IS 'ISA09 - Date of the interchange Format: YYMMDD';
COMMENT ON COLUMN EDI_O_PURCHASEORDERACK.ISA10
  IS 'ISA10 - Timestamp of the interchange; format HHMM';
COMMENT ON COLUMN EDI_O_PURCHASEORDERACK.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_O_PURCHASEORDERACK.ISA12
  IS 'ISA12 - Code specifying the version number of the interchange control segments (e.g. ''00401'')';
COMMENT ON COLUMN EDI_O_PURCHASEORDERACK.ISA13
  IS 'ISA13 - Interchange control number assigned by the interchange sender';
COMMENT ON COLUMN EDI_O_PURCHASEORDERACK.ISA14
  IS 'ISA14 - Code sent by the sender to request an interchange acknowledgment (TA1, e.g. ''0'' when not applicable)';
COMMENT ON COLUMN EDI_O_PURCHASEORDERACK.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_O_PURCHASEORDERACK.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_O_PURCHASEORDERACK.GS01
  IS 'GS01 - Functional identifier code for a group of application related transaction sets (e.g. ''PO'')';
COMMENT ON COLUMN EDI_O_PURCHASEORDERACK.GS02
  IS 'GS02 - Code to identify the party sending the transmission; codes agreed to by trading partners';
COMMENT ON COLUMN EDI_O_PURCHASEORDERACK.GS03
  IS 'GS03 - Code to identify the party receiving the transmission; codes agreed to by trading partners';
COMMENT ON COLUMN EDI_O_PURCHASEORDERACK.GS04
  IS 'GS04 - Date of the interchange Format: YYYYMMDD';
COMMENT ON COLUMN EDI_O_PURCHASEORDERACK.GS05
  IS 'GS05 - Timestamp of the interchange; format HHMI, HHMISS or HHMISSFF';
COMMENT ON COLUMN EDI_O_PURCHASEORDERACK.GS06
  IS 'GS06 - Group control number assigned, originated and maintained by sender';
COMMENT ON COLUMN EDI_O_PURCHASEORDERACK.GS07
  IS 'GS07 - Code identifying the issuer of the standard; this code is used in conjunction with data element 480';
COMMENT ON COLUMN EDI_O_PURCHASEORDERACK.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_O_PURCHASEORDERACK.ST01
  IS 'ST01 - Code uniquely identifying a transaction set (e.g. ''850'' Purchase Order)';
COMMENT ON COLUMN EDI_O_PURCHASEORDERACK.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_O_PURCHASEORDERACK.BAK01
  IS 'BAK01 - Code identifying purpose of transaction set (e.g. ''00'' = Original)';
COMMENT ON COLUMN EDI_O_PURCHASEORDERACK.BAK02
  IS 'BAK02 - 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_O_PURCHASEORDERACK.BAK03
  IS 'BAK03 - Purchase order number as used at customer';
COMMENT ON COLUMN EDI_O_PURCHASEORDERACK.BAK04
  IS 'BAK04 - Order Date of purchase order Format: YYYYMMDD, default current date';
COMMENT ON COLUMN EDI_O_PURCHASEORDERACK.BAK05
  IS 'BAK05 - Number identifying a release against a purchase order placed by the parties involved';
COMMENT ON COLUMN EDI_O_PURCHASEORDERACK.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_O_PURCHASEORDERACK.DTM02
  IS 'DTM02 - Requested delivery date Format: YYYYMMDD';
COMMENT ON COLUMN EDI_O_PURCHASEORDERACK.CTT01
  IS 'CTT01 - Total number of line items in the transaction set';
COMMENT ON COLUMN EDI_O_PURCHASEORDERACK.CTT02
  IS 'CTT02 - Sum of values of the specified data element';
COMMENT ON COLUMN EDI_O_PURCHASEORDERACK.SE01
  IS 'SE01 - Total number of segments included in a transaction set including ST and SE segments';
COMMENT ON COLUMN EDI_O_PURCHASEORDERACK.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_O_PURCHASEORDERACK.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_O_PURCHASEORDERACK.GE02
  IS 'GE02 - Group control number, assigned, generated and maintained by the sender';
COMMENT ON COLUMN EDI_O_PURCHASEORDERACK.IEA01
  IS 'IEA01 - A count of the number of functional groups included in an interchange';
COMMENT ON COLUMN EDI_O_PURCHASEORDERACK.IEA02
  IS 'A control number assigned by the interchange sender'; 
ALTER TABLE EDI_O_PURCHASEORDERACK
  ADD CONSTRAINT PK_EDI_O_PURCHASEORDERACK PRIMARY KEY (TRANSFER_ID);
CREATE INDEX XIE1_EDI_O_PURCHASEORDERACK ON EDI_O_PURCHASEORDERACK(PLANTCODE);
CREATE INDEX XIE2_EDI_O_PURCHASEORDERACK ON EDI_O_PURCHASEORDERACK(PURCHASEORDERNUMBER);  
CREATE INDEX XIE3_EDI_O_PURCHASEORDERACK ON EDI_O_PURCHASEORDERACK(CUSTOMERNUMBER);

-- 1.6.5.2	EDI_O_PURCHASEORDERACK_MSG --
CREATE TABLE EDI_O_PURCHASEORDERACK_MSG
(
  TRANSFER_ID         NUMBER NOT NULL,
  TRANSFER_STATUS     NUMBER DEFAULT 0 NOT NULL
                        CONSTRAINT EDI_O_PURORDACK_MSG_TS_VAL1
                        CHECK (TRANSFER_STATUS IN (-1, 0, 1)),
  PURCHASEORDERNUMBER VARCHAR2(25) NOT NULL,            
  MESSAGELINENUMBER   NUMBER NOT NULL,
  N901                VARCHAR2(3) NOT NULL,
  N902                VARCHAR2(30) NOT NULL,
  MSG01               VARCHAR2(264) NOT NULL
);
COMMENT ON COLUMN EDI_O_PURCHASEORDERACK_MSG.TRANSFER_ID
  IS 'Transfer id from EDI_O_PURCHASEORDERACK';
COMMENT ON COLUMN EDI_O_PURCHASEORDERACK_MSG.TRANSFER_STATUS
  IS '0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN EDI_O_PURCHASEORDERACK_MSG.PURCHASEORDERNUMBER
  IS 'Purchase order number as used at customer. Max 15 characters in ABS';
COMMENT ON COLUMN EDI_O_PURCHASEORDERACK_MSG.MESSAGELINENUMBER
  IS 'Generated as last used line number + 1 for current transfer_id';
COMMENT ON COLUMN EDI_O_PURCHASEORDERACK_MSG.N901
  IS 'N901 - Code qualifying the Reference Identification
Possible values when filled:
"28" = Employee identification number
"ZZ" = mutually defined';
COMMENT ON COLUMN EDI_O_PURCHASEORDERACK_MSG.N902
  IS 'N902 - Reference information as defined as defined for a particular Transaction Set or as specified by the Reference Identification Qualifier';  
COMMENT ON COLUMN EDI_O_PURCHASEORDERACK_MSG.MSG01
  IS 'MSG01 - Free form message text';
ALTER TABLE EDI_O_PURCHASEORDERACK_MSG
  ADD CONSTRAINT PK_EDI_O_PURCHASEORDRACK_MSG PRIMARY KEY (TRANSFER_ID, PURCHASEORDERNUMBER, MESSAGELINENUMBER);
CREATE INDEX XIE1_EDI_O_PURCORDACK_MSG ON EDI_O_PURCHASEORDERACK_MSG (TRANSFER_ID); 
CREATE INDEX XIE2_EDI_O_PURCORDACK_MSG ON EDI_O_PURCHASEORDERACK_MSG (PURCHASEORDERNUMBER);   
ALTER TABLE EDI_O_PURCHASEORDERACK_MSG
  ADD CONSTRAINT FK01_EDI_O_PURCHORDACK_MSG FOREIGN KEY (TRANSFER_ID)
  REFERENCES EDI_O_PURCHASEORDERACK (TRANSFER_ID) ON DELETE CASCADE;  
  
-- 1.6.5.3	EDI_O_PURCHASEORDERACK_NAME --
CREATE TABLE EDI_O_PURCHASEORDERACK_NAME
(
  TRANSFER_ID         NUMBER NOT NULL,
  TRANSFER_STATUS     NUMBER DEFAULT 0 NOT NULL
                        CONSTRAINT EDI_O_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_O_PURCHASEORDERACK_NAME.TRANSFER_ID
  IS 'Transfer id from EDI_O_PURCHASEORDERACK';
COMMENT ON COLUMN EDI_O_PURCHASEORDERACK_NAME.TRANSFER_STATUS
  IS '0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN EDI_O_PURCHASEORDERACK_NAME.PURCHASEORDERNUMBER
  IS 'Purchase order number as used at customer. Max 15 characters in ABS';
COMMENT ON COLUMN EDI_O_PURCHASEORDERACK_NAME.MESSAGELINENUMBER
  IS 'Generated as last used line number + 1 for current transfer_id';
COMMENT ON COLUMN EDI_O_PURCHASEORDERACK_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_O_PURCHASEORDERACK_NAME.N102
  IS 'N102 - Free form name';
COMMENT ON COLUMN EDI_O_PURCHASEORDERACK_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_O_PURCHASEORDERACK_NAME.N104
  IS 'N104 - Code identifying a party or other code (Consumption point in ABS)';
COMMENT ON COLUMN EDI_O_PURCHASEORDERACK_NAME.N301
  IS 'N301 - Address information';
COMMENT ON COLUMN EDI_O_PURCHASEORDERACK_NAME.N302
  IS 'N302 - Address information';
COMMENT ON COLUMN EDI_O_PURCHASEORDERACK_NAME.N401
  IS 'N401 - Free form text for city';
COMMENT ON COLUMN EDI_O_PURCHASEORDERACK_NAME.N402
  IS 'N402 - Code (Standard State/Province) as defined by appropriate government agency';
COMMENT ON COLUMN EDI_O_PURCHASEORDERACK_NAME.N403
  IS 'N403 - Code defining international postal zone code excluding punctuation and blanks (zip code for United States';
COMMENT ON COLUMN EDI_O_PURCHASEORDERACK_NAME.N404
  IS 'N404 - Code identifying the country';
ALTER TABLE EDI_O_PURCHASEORDERACK_NAME
  ADD CONSTRAINT PK_EDI_O_PURCHASEORDACK_NAME PRIMARY KEY (TRANSFER_ID, PURCHASEORDERNUMBER, MESSAGELINENUMBER);
CREATE INDEX XIE1_EDI_O_PURCORDACK_NAME ON EDI_O_PURCHASEORDERACK_NAME (TRANSFER_ID);
CREATE INDEX XIE2_EDI_O_PURCORDACK_NAME ON EDI_O_PURCHASEORDERACK_NAME (PURCHASEORDERNUMBER);
ALTER TABLE EDI_O_PURCHASEORDERACK_NAME
  ADD CONSTRAINT  FK01_EDI_O_PURCHORDACK_NAME FOREIGN KEY (TRANSFER_ID)
  REFERENCES EDI_O_PURCHASEORDERACK (TRANSFER_ID) ON DELETE CASCADE;
  
-- 1.6.5.4	EDI_O_PURCHASEORDERLINEACK --
CREATE TABLE EDI_O_PURCHASEORDERLINEACK
(
  TRANSFER_ID               NUMBER NOT NULL,
  TRANSFER_STATUS           NUMBER DEFAULT 0 NOT NULL
                              CONSTRAINT EDI_O_PURORDLINEACK_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,
  CATALOGUECODE             VARCHAR2(12) NULL,
  SIZECODE	                VARCHAR2(12) NULL,
  FINISHINGMETHODCODE	    VARCHAR2(12) NULL,
  ACKPRICE                  VARCHAR2(17) 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(48) 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,
  ACK01                    	VARCHAR2(2) NOT NULL,
  ACK02                    	VARCHAR2(15) NOT NULL,
  ACK03                    	VARCHAR2(2) NOT NULL,
  ACK04                    	VARCHAR2(3) NULL,
  ACK05                    	VARCHAR2(8) NULL,
  ACK07                    	VARCHAR2(2) NOT NULL,
  ACK08                    	VARCHAR2(48) NULL,
  ACK09                    	VARCHAR2(2) NULL,
  ACK10                    	VARCHAR2(48) NULL
);
COMMENT ON COLUMN EDI_O_PURCHASEORDERLINEACK.TRANSFER_ID
  IS 'Transfer id from EDI_O_PURCHASEORDERACK';
COMMENT ON COLUMN EDI_O_PURCHASEORDERLINEACK.TRANSFER_STATUS
  IS '0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN EDI_O_PURCHASEORDERLINEACK.PURCHASEORDERNUMBER
  IS 'Purchase order number as used at customer. Max 15 characters in ABS';
COMMENT ON COLUMN EDI_O_PURCHASEORDERLINEACK.PURCHASEORDERLINENUMBER
  IS 'Generated as last used line number + 1 for current transfer_id';
COMMENT ON COLUMN EDI_O_PURCHASEORDERLINEACK.CUSTOMERPRODUCTCODE
  IS 'PLPC.CustomCode';
COMMENT ON COLUMN EDI_O_PURCHASEORDERLINEACK.CUSTOMERPRODUCTDESC
  IS 'PLPC.CustomDescription';
COMMENT ON COLUMN EDI_O_PURCHASEORDERLINEACK.PRODUCTCODE
  IS 'Link to product code';
COMMENT ON COLUMN EDI_O_PURCHASEORDERLINEACK.CATALOGUECODE
  IS 'Link to catalogue code';  
COMMENT ON COLUMN EDI_O_PURCHASEORDERLINEACK.SIZECODE
  IS 'Link to size code';
COMMENT ON COLUMN EDI_O_PURCHASEORDERLINEACK.FINISHINGMETHODCODE
  IS 'Link to finishing method code';
COMMENT ON COLUMN EDI_O_PURCHASEORDERLINEACK.ACKPRICE
  IS 'Determined price in ABS at delivery date';
COMMENT ON COLUMN EDI_O_PURCHASEORDERLINEACK.PRODUCTLISTPERCUSTOMER_ID
  IS 'Link to PLPC';
COMMENT ON COLUMN EDI_O_PURCHASEORDERLINEACK.DELIVERYVARIATIONS_ID
  IS 'Link to DeliveryVariations';
COMMENT ON COLUMN EDI_O_PURCHASEORDERLINEACK.PO101
  IS 'PO101 - Alphanumeric characters assigned for differentiation within a transaction set, unique line item identification';
COMMENT ON COLUMN EDI_O_PURCHASEORDERLINEACK.PO102
  IS 'PO102 - Quantity ordered';
COMMENT ON COLUMN EDI_O_PURCHASEORDERLINEACK.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_O_PURCHASEORDERLINEACK.PO104
  IS 'PO104 - Price per unit of product, service, commodity, etc.';
COMMENT ON COLUMN EDI_O_PURCHASEORDERLINEACK.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_O_PURCHASEORDERLINEACK.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_O_PURCHASEORDERLINEACK.PO107
  IS 'PO107 -  Identifying number for a product or service';
COMMENT ON COLUMN EDI_O_PURCHASEORDERLINEACK.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_O_PURCHASEORDERLINEACK.PO109
  IS 'PO109 - Identifying number for a product or service';
COMMENT ON COLUMN EDI_O_PURCHASEORDERLINEACK.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_O_PURCHASEORDERLINEACK.PO111
  IS 'PO111 - Identifying number for a product or service';
COMMENT ON COLUMN EDI_O_PURCHASEORDERLINEACK.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_O_PURCHASEORDERLINEACK.PO113
  IS 'PO113 - Identifying number for a product or service (e.g. serial number)';
COMMENT ON COLUMN EDI_O_PURCHASEORDERLINEACK.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_O_PURCHASEORDERLINEACK.PO115
  IS 'PO115 - Identifying number for a product or service (e.g. lot number)';
COMMENT ON COLUMN EDI_O_PURCHASEORDERLINEACK.PID01
  IS 'PID01 - Code indicating the format of a description';
COMMENT ON COLUMN EDI_O_PURCHASEORDERLINEACK.PID02
  IS 'PID02 - Code identifying the general class of a product or process characteristic, Not used for Intertrade';
COMMENT ON COLUMN EDI_O_PURCHASEORDERLINEACK.PID03
  IS 'PID03 - Code identifying the agency assigning the code values. Not used for Intertrade';
COMMENT ON COLUMN EDI_O_PURCHASEORDERLINEACK.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_O_PURCHASEORDERLINEACK.PID05
  IS 'PID05 - A free-form description to clarify the related data elements and their content';
COMMENT ON COLUMN EDI_O_PURCHASEORDERLINEACK.ACK01
  IS 'ACK01 - Code specifying the action taken by the seller on a line item requested by the buyer
Possible values when filled
"AC" = Item accepted and shipped
"AR" = Item accepted and released for shipment
"BP"= Item accepted, partial shipment, balance backordered
"IA" = Item accepted
"IB" = Item backordered
"IC" = Item accepted, changes made
"ID" = Item deleted
"IF" = Item on hold, incomplete description
"IP" = Item accepted, price changed
"IQ" = Item accepted, quantity changed
"IR" = Item rejected
"IS" = Item accepted, substitution made
"R1"= Item rejected, not a contract item
"R2" = Item rejected, invalid item product number
"R3" = Item rejected, invalid unit of issue
"R4"= Item rejected, contract item not available';
COMMENT ON COLUMN EDI_O_PURCHASEORDERLINEACK.ACK02
  IS 'ACK02 - Numeric value of quantity';
COMMENT ON COLUMN EDI_O_PURCHASEORDERLINEACK.ACK03
  IS 'ACK03 - 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_O_PURCHASEORDERLINEACK.ACK04
  IS 'ACK04 - Code specifying the type of date or time, or both day and time 
Possible values when filled:
"07" = Estimated Delivery
"68" = Current schedule ship
Not used for Intertrade';
COMMENT ON COLUMN EDI_O_PURCHASEORDERLINEACK.ACK05
  IS 'ACK05 - Date expressed as YYYYMMDD';
COMMENT ON COLUMN EDI_O_PURCHASEORDERLINEACK.ACK07
  IS 'ACK07 - Code identifying the type/source of the descriptive number used in
Product/Service ID (234)
Possible values when filled
"VC" = Vendors (Sellers) Catalog Number
 "VN" = Vendors (Sellers) Item Number';
COMMENT ON COLUMN EDI_O_PURCHASEORDERLINEACK.ACK08
  IS 'ACK08 - Identifying number fora product or service';
COMMENT ON COLUMN EDI_O_PURCHASEORDERLINEACK.ACK09
  IS 'ACK09 - 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_O_PURCHASEORDERLINEACK.ACK10
  IS 'ACK10 - Identifying number fora product or service';  
ALTER TABLE EDI_O_PURCHASEORDERLINEACK
  ADD CONSTRAINT PK_EDI_O_PURORDLINEACK PRIMARY KEY (TRANSFER_ID, PURCHASEORDERNUMBER, PURCHASEORDERLINENUMBER);
CREATE INDEX XIE1_EDI_O_PURORDLINEACK ON EDI_O_PURCHASEORDERLINEACK (TRANSFER_ID);  
ALTER TABLE EDI_O_PURCHASEORDERLINEACK
  ADD CONSTRAINT FK01_EDI_O_PURORDLINEACK FOREIGN KEY (TRANSFER_ID)
  REFERENCES EDI_O_PURCHASEORDERACK (TRANSFER_ID) ON DELETE CASCADE;  

-- 1.6.5.5	EDI_O_PURCHASEORDERACK_LOG --
CREATE TABLE EDI_O_PURCHASEORDERACK_LOG
(
  TRANSFER_ID         NUMBER NOT NULL,
  TRANSFER_STATUS     NUMBER DEFAULT 0 NOT NULL
                        CONSTRAINT EDI_O_PURCORDACK_LOG_TS_VAL1
                        CHECK (TRANSFER_STATUS IN(-1, 0, 1)),
  TEXT                VARCHAR2(1000) NULL,
  PROCESS_STATUS      NUMBER DEFAULT 0 NOT NULL
                        CONSTRAINT EDI_O_PURCORDACK_LOG_PS_VAL3
                        CHECK (PROCESS_STATUS IN(0, 1)),
  TIMESTAMP           DATE DEFAULT SYSDATE NOT NULL
);  
COMMENT ON COLUMN EDI_O_PURCHASEORDERACK_LOG.TRANSFER_ID
  IS 'Transfer id from EDI_O_PURCHASEORDERACK';
COMMENT ON COLUMN EDI_O_PURCHASEORDERACK_LOG.TRANSFER_STATUS
  IS '0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN EDI_O_PURCHASEORDERACK_LOG.TEXT
  IS 'In case if errors PROCESS_STATUS=1, displays the error, or the ok message';
COMMENT ON COLUMN EDI_O_PURCHASEORDERACK_LOG.PROCESS_STATUS
  IS '0 = processed correctly, 1 = process stopped with errors';
ALTER TABLE EDI_O_PURCHASEORDERACK_LOG
  ADD CONSTRAINT PK_EDI_O_PURCHASEORDACK_LOG PRIMARY KEY (TRANSFER_ID);
ALTER TABLE EDI_O_PURCHASEORDERACK_LOG
  ADD CONSTRAINT FK01_EDI_O_PURCHORDACK_LOG FOREIGN KEY (TRANSFER_ID)
  REFERENCES EDI_O_PURCHASEORDERACK (TRANSFER_ID) ON DELETE CASCADE;