-- ABS-57622/ ABS-79953

-- EDI_O_INVOICE_810 TABLE --
CREATE TABLE EDI_O_INVOICE_810
(
  TRANSFER_ID      NUMBER NOT NULL,
  TRANSFER_STATUS  NUMBER DEFAULT 0 NOT NULL,
  PLANTCODE        VARCHAR2(6) NOT NULL,
  INVOICENUMBER    VARCHAR2(25) NOT NULL,
  INVOICEDATE      DATE NOT NULL,
  CUSTOMERNUMBER   NUMBER NULL,
  DEBTORNUMBER     NUMBER NULL,
  BUSINESSUNITCODE VARCHAR2(6) NULL,
  FILENAME         VARCHAR2(255) NULL,
  EMAILDOCUMENT_ID NUMBER NULL,
  EXPORT_STATUS    NUMBER DEFAULT 0 NOT NULL CONSTRAINT EXPORT_STATUS_VALID2
				   CHECK (EXPORT_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 CONSTRAINT ISA05_VALID1
				   CHECK (ISA05 IN ('01','08','12','14','ZZ')),
  ISA06            VARCHAR2(15) NULL,
  ISA07            VARCHAR2(2) NULL CONSTRAINT ISA07_VALID1
			       CHECK (ISA07 IN ('01','08','12','14','ZZ')),
  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 CONSTRAINT ISA15_VALID1
			       CHECK (ISA15 IN ('P','NE','I')),
  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,
  BIG01            VARCHAR2(8) NULL,
  BIG02            VARCHAR2(22) NULL,
  BIG03            VARCHAR2(8) NULL,
  BIG04            VARCHAR2(22) NULL,
  BIG05            VARCHAR2(30) NULL,
  BIG06            VARCHAR2(8) NULL,
  BIG07            VARCHAR2(2) NULL,
  CUR01            VARCHAR2(3) NULL,
  CUR02            VARCHAR2(3) NULL,
  REF01            VARCHAR2(3) NULL CONSTRAINT REF01_VALID1
				   CHECK (REF01 IN ('BM','CO','CN','MB','PK','ZZ'))  ,
  REF02            VARCHAR2(30) NULL,
  ITD01            VARCHAR2(2) NULL CONSTRAINT ITD01_VALID1
				   CHECK (ITD01 IN ('01','05','08')),
  ITD02            VARCHAR2(2) NULL CONSTRAINT ITD02_VALID1
				   CHECK (ITD02 IN ('3','09')),
  ITD03            VARCHAR2(6) NULL,
  ITD04            VARCHAR2(8) NULL,
  ITD05            VARCHAR2(3) NULL,
  ITD06            VARCHAR2(8) NULL,
  ITD07            VARCHAR2(3) NULL,
  ITD08            VARCHAR2(10) NULL,
  ITD12            VARCHAR2(80) NULL,
  DTM01            VARCHAR2(3) NULL CONSTRAINT DTM01_VALID1
				   CHECK (DTM01 IN ('002','011')),
  DTM02            VARCHAR2(8) NULL,
  TDS01            VARCHAR2(15) NOT NULL,
  TDS02            VARCHAR2(15) NULL,
  TDS03            VARCHAR2(15) NULL,
  TDS04            VARCHAR2(15) 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_INVOICE_810.PLANTCODE
  IS 'Plant code where the process was run that imports the csv-file';
COMMENT ON COLUMN EDI_O_INVOICE_810.INVOICENUMBER
  IS 'Invoice number as used in ABS';
COMMENT ON COLUMN EDI_O_INVOICE_810.CUSTOMERNUMBER
  IS 'Customer number in ABS';
COMMENT ON COLUMN EDI_O_INVOICE_810.DEBTORNUMBER
  IS 'Customer number of debtor in ABS';
COMMENT ON COLUMN EDI_O_INVOICE_810.BUSINESSUNITCODE
  IS 'Business unit in ABS';
COMMENT ON COLUMN EDI_O_INVOICE_810.FILENAME
  IS 'File name of the export file';
COMMENT ON COLUMN EDI_O_INVOICE_810.EMAILDOCUMENT_ID
  IS 'Link to the sent email if any';
COMMENT ON COLUMN EDI_O_INVOICE_810.EXPORT_STATUS
  IS 'Indicates if an invoice (810) has been exported
0 = Not applicable/Not sent yet
1 = Invoice sent
';
COMMENT ON COLUMN EDI_O_INVOICE_810.MESSAGETYPE
  IS 'Transaction set type (same as ST01), most likely `810'' for Intertrade';
COMMENT ON COLUMN EDI_O_INVOICE_810.VERSIONNUMBER
  IS 'Version number (same as GS08), filled with `00410'' for Intertrade';
COMMENT ON COLUMN EDI_O_INVOICE_810.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_INVOICE_810.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). Must be filled with (trailing) spaces, to its full length';
COMMENT ON COLUMN EDI_O_INVOICE_810.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_INVOICE_810.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) Must be filled with (trailing) spaces, to its full length';
COMMENT ON COLUMN EDI_O_INVOICE_810.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 #
"ZZ" = mutually defined
';
COMMENT ON COLUMN EDI_O_INVOICE_810.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_INVOICE_810.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_INVOICE_810.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_INVOICE_810.ISA09
  IS 'ISA09 - Date of the interchange Format: YYMMDD';
COMMENT ON COLUMN EDI_O_INVOICE_810.ISA10
  IS 'ISA10 - Timestamp of the interchange; format HHMM';
COMMENT ON COLUMN EDI_O_INVOICE_810.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_INVOICE_810.ISA12
  IS 'ISA12 - Code specifying the version number of the interchange control segments (e.g. "00401")';
COMMENT ON COLUMN EDI_O_INVOICE_810.ISA13
  IS 'ISA13 - Interchange control number assigned by the interchange sender';
COMMENT ON COLUMN EDI_O_INVOICE_810.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_INVOICE_810.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_INVOICE_810.ISA16
  IS 'ISA16  VARCHAR2(1)  Y  Null  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_INVOICE_810.GS01
  IS 'GS01 - Functional identifier code for a group of application related transaction sets (e.g. "IN)';
COMMENT ON COLUMN EDI_O_INVOICE_810.GS02
  IS 'GS02 - Code to identify the party sending the transmission; codes agreed to by trading partners';
COMMENT ON COLUMN EDI_O_INVOICE_810.GS03
  IS 'GS03 - Code to identify the party receiving the transmission; codes agreed to by trading partners';
COMMENT ON COLUMN EDI_O_INVOICE_810.GS04
  IS 'GS04 - Date of the interchange
Format: CCYYMMDD';
COMMENT ON COLUMN EDI_O_INVOICE_810.GS05
  IS 'GS05 - Timestamp of the interchange; format HHMM, HHMMSS or HHMMSSDD';
COMMENT ON COLUMN EDI_O_INVOICE_810.GS06
  IS 'GS06 - Group control number assigned, originated and maintained by sender';
COMMENT ON COLUMN EDI_O_INVOICE_810.GS07
  IS 'GS07 - Code identifying the issuer of the standard; this code is used in conjunction with data element 480 (e.g. "X")';
COMMENT ON COLUMN EDI_O_INVOICE_810.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_INVOICE_810.ST01
  IS 'ST01 - Code uniquely identifying a transaction set (e.g. "810" Invoice)';
COMMENT ON COLUMN EDI_O_INVOICE_810.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_INVOICE_810.BIG01
  IS 'BIG01 - Invoice date, format: CCYYMMDD ';
COMMENT ON COLUMN EDI_O_INVOICE_810.BIG02
  IS 'BIG02 - Invoice number in ABS';
COMMENT ON COLUMN EDI_O_INVOICE_810.BIG03
  IS 'BIG03 - Purchase order date
Format: CCYYMMDD ';
COMMENT ON COLUMN EDI_O_INVOICE_810.BIG04
  IS 'BIG04 - Purchase order number in ABS';
COMMENT ON COLUMN EDI_O_INVOICE_810.BIG05
  IS 'BIG05 - Release number, number identifying a release against a purchase order placed by the parties involved in the transaction
Not used for Intertrade';
COMMENT ON COLUMN EDI_O_INVOICE_810.BIG06
  IS 'BIG06 - Change Order Sequence Number, number assigned by the orderer, identifying a specific change or revision to a previously transmitted transaction set
Not used for Intertrade';
COMMENT ON COLUMN EDI_O_INVOICE_810.BIG07
  IS 'BIG07 - Transaction type code, only used to further define the type of invoice when needed (e.g. "DI" = Debit Invoice) ';
COMMENT ON COLUMN EDI_O_INVOICE_810.CUR01
  IS 'CUR01 - Code identifying an organizational entity, a physical location or an individual  (e.g. "SE" = Selling Party)';
COMMENT ON COLUMN EDI_O_INVOICE_810.CUR02
  IS 'CUR02 - Currency code (standard ISO) for country in whose currency the charges are specified';
COMMENT ON COLUMN EDI_O_INVOICE_810.REF01
  IS 'REF01 - Reference identification qualifier: code to identify the reference identification (e.g. "PK" Packing list number) 
Possible values when filled:
"BM" = Bill of lading number
"CO" = Customer order
"CN" = Carrier reference number
"MB" = Master Bill of Lading
"PK" = Packing list number
"ZZ" = Mutually defined';
COMMENT ON COLUMN EDI_O_INVOICE_810.REF02
  IS 'REF02 - Reference information as defined for a specific transaction set or as specified by the reference identification qualifier (e.g. consignment note number in ABS)';
COMMENT ON COLUMN EDI_O_INVOICE_810.ITD01
  IS 'ITD01 - Term type code, code identifying type of payment terms
Possible values when filled:
"01" = Basic
"05" = Discount not applicable
"08" = Basic discount offered
';
COMMENT ON COLUMN EDI_O_INVOICE_810.ITD02
  IS 'ITD02 - Terms basic date code, code identifying the beginning of the terms period
Possible values when filled:
"3" = Invoice date
"09" = Purchase order date
';
COMMENT ON COLUMN EDI_O_INVOICE_810.ITD03
  IS 'ITD03 - Terms discount percent, terms discount percentage expressed as a percentage available to the purchaser if an invoice is paid on or before the Terms Discount Due Date';
COMMENT ON COLUMN EDI_O_INVOICE_810.ITD04
  IS 'ITD04 - Terms discount due date, date payment is due if discount is to be earned expressed in format CCYYMMDD';
COMMENT ON COLUMN EDI_O_INVOICE_810.ITD05
  IS 'ITD05 - terms discount days due, number of days in the terms discount period by which payment is due if terms discount is earned';
COMMENT ON COLUMN EDI_O_INVOICE_810.ITD06
  IS 'ITD06 - Terms net due date, date when total invoice amount becomes due expressed in format CCYYMMDD';
COMMENT ON COLUMN EDI_O_INVOICE_810.ITD07
  IS 'ITD07 - Terms net days, number of days until total invoice amount is due (discount not applicable';
COMMENT ON COLUMN EDI_O_INVOICE_810.ITD08
  IS 'ITD08 - Terms discount amount, total amount of terms discount';
COMMENT ON COLUMN EDI_O_INVOICE_810.ITD12
  IS 'ITD12 - Free form description to clarify the related data elements and their content';
COMMENT ON COLUMN EDI_O_INVOICE_810.DTM01
  IS 'DTM01 - Code specifying type of date or time, or both date and time
Possible values when filled:
"002" = Delivery requested
"011" = Shipped';
COMMENT ON COLUMN EDI_O_INVOICE_810.DTM02
  IS 'DTM02 - Requested delivery date/shipping date (depending on value of DTM01)
Format: CCYYMMDD
';
COMMENT ON COLUMN EDI_O_INVOICE_810.TDS01
  IS 'TDS01 - Monetary amount, is the total amount of invoice (including charges, less allowances) before terms discount (if discount is applicable)';
COMMENT ON COLUMN EDI_O_INVOICE_810.TDS02
  IS 'TDS02 - Monetary amount, indicates the amount upon which the terms discount amount is calculated';
COMMENT ON COLUMN EDI_O_INVOICE_810.TDS03
  IS 'TDS03 - Monetary amount, is the amount of invoice due if paid by terms discount due date (total invoice or installment amount less cash discount)';
COMMENT ON COLUMN EDI_O_INVOICE_810.TDS04
  IS 'TDS04 - Monetary amount, indicates the total amount of terms discount.';
COMMENT ON COLUMN EDI_O_INVOICE_810.CTT01
  IS 'CTT01 - Total number of line items in the transaction set';
COMMENT ON COLUMN EDI_O_INVOICE_810.CTT02
  IS 'CTT02 - Sum of values of the specified data element
Not used for Intertrade';
COMMENT ON COLUMN EDI_O_INVOICE_810.SE01
  IS 'SE01 - Total number of segments included in a transaction set including ST and SE segments';
COMMENT ON COLUMN EDI_O_INVOICE_810.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_INVOICE_810.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_INVOICE_810.GE02
  IS 'GE02 - Group control number, assigned, generated and maintained by the sender';
COMMENT ON COLUMN EDI_O_INVOICE_810.IEA01
  IS 'IEA01 - A count of the number of functional groups included in an interchange';
COMMENT ON COLUMN EDI_O_INVOICE_810.IEA02
  IS 'A control number assigned by the interchange sender';

ALTER TABLE EDI_O_INVOICE_810
  ADD CONSTRAINT XPKEDI_O_INVOICE_810 PRIMARY KEY (TRANSFER_ID);

CREATE INDEX XIE1EDI_O_INVOICE_810 ON EDI_O_INVOICE_810 (PLANTCODE ASC);
CREATE INDEX XIE2EDI_O_INVOICE_810 ON EDI_O_INVOICE_810 (INVOICENUMBER ASC);
CREATE INDEX XIE3EDI_O_INVOICE_810 ON EDI_O_INVOICE_810 (CUSTOMERNUMBER ASC);
CREATE INDEX XIE4EDI_O_INVOICE_810 ON EDI_O_INVOICE_810 (DEBTORNUMBER ASC);
--section break for pack_Script
-- EDI_O_INVOICE_810_TAX TABLE --
CREATE TABLE EDI_O_INVOICE_810_TAX
(
  TRANSFER_ID       NUMBER NOT NULL,
  TRANSFER_STATUS   NUMBER DEFAULT 0 NOT NULL,
  MESSAGELINENUMBER NUMBER NOT NULL,
  TXI01             VARCHAR2(2) NOT NULL CONSTRAINT TXI01_VALID1
					CHECK(TXI01 IN ('GS','PS')),
  TXI02             VARCHAR2(18) NULL,
  TXI03             VARCHAR2(10) NULL,
  TXI04             VARCHAR2(2) NULL CONSTRAINT TXI04_VALID1
					CHECK(TXI04 IN ('CD','VD','VE')),
  TXI05             VARCHAR2(10) NULL,
  TXI06             VARCHAR2(1) NULL,
  TXI07             VARCHAR2(1) NULL,
  TXI08             VARCHAR2(9) NULL,
  TXI09             VARCHAR2(20) NULL,
  TXI10             VARCHAR2(20) NULL,
  TAXTYPE           NUMBER DEFAULT 0 NOT NULL CONSTRAINT TAXTYPE_VALID1
					CHECK(TAXTYPE IN (0,1,2,3)),
  VENDORTAXNUMBER   VARCHAR2(20) NULL
)
;

COMMENT ON COLUMN EDI_O_INVOICE_810_TAX.MESSAGELINENUMBER
  IS 'Generated as last used line number + 1 for current transfer_id';
COMMENT ON COLUMN EDI_O_INVOICE_810_TAX.TXI01
  IS 'TXI01 - Tax type code
Possible values when filled:
"GS" = Goods and services tax 
"PS" = State or Provincial tax on services 
';
COMMENT ON COLUMN EDI_O_INVOICE_810_TAX.TXI02
  IS 'TXI02 - Monetary amount';
COMMENT ON COLUMN EDI_O_INVOICE_810_TAX.TXI03
  IS 'TXI03 - Tax percentage';
COMMENT ON COLUMN EDI_O_INVOICE_810_TAX.TXI04
  IS 'TXI04 - Tax Jurisdiction Code Qualifier, code identifying the source of the data used in tax jurisdiction code
Possible values when filled:
"CD" = Customer defined
"VD" = Vendor defined
"VE" =  Vertex
Not used for Intertrade
';
COMMENT ON COLUMN EDI_O_INVOICE_810_TAX.TXI05
  IS 'TXI05 - Tax Jurisdiction Code, code identifying the taxing jurisdiction
Not used for Intertrade
';
COMMENT ON COLUMN EDI_O_INVOICE_810_TAX.TXI06
  IS 'TXI06 - Tax Exempt Code, code identifying exemption status from sales and use tax';
COMMENT ON COLUMN EDI_O_INVOICE_810_TAX.TXI07
  IS 'TXI07 - Relationship code, code indicating the relationship between entities
Not used for Intertrade
';
COMMENT ON COLUMN EDI_O_INVOICE_810_TAX.TXI08
  IS 'TXI08 - Dollar basis for percent, dollar basis to be used in the percent calculation of the allowance, charge or tax
Not used for Intertrade
';
COMMENT ON COLUMN EDI_O_INVOICE_810_TAX.TXI09
  IS 'TXI09 - Tax Identification Number, number assigned to a purchaser (buyer, orderer) by a taxing jurisdiction (state, county, etc.) often called a tax exemption number or certificate number.
Not used for Intertrade';
COMMENT ON COLUMN EDI_O_INVOICE_810_TAX.TXI10
  IS 'TXI10 - Assigned identification, alphanumeric characters for different within a transaction set.Not used for Intertrade';
COMMENT ON COLUMN EDI_O_INVOICE_810_TAX.TAXTYPE
  IS 'Tax types as used at ABS.
Possible values:
US/CA:
0 = Not applicable
1 = Sales
2 = Rent
3 = Miscellaneous
EU:
0 = Not applicable
1 = Define tax';
COMMENT ON COLUMN EDI_O_INVOICE_810_TAX.VENDORTAXNUMBER
  IS 'Tax identification number assigned to a vendor by a taxing jurisdiction (state, county etc.) for tax type code';
 
CREATE INDEX XIF1EDI_O_INVOICE_810_TAX ON EDI_O_INVOICE_810_TAX (TRANSFER_ID ASC);
 
ALTER TABLE EDI_O_INVOICE_810_TAX
  ADD CONSTRAINT XPKEDI_O_INVOICE_810_TAX PRIMARY KEY (TRANSFER_ID, MESSAGELINENUMBER);
  
ALTER TABLE EDI_O_INVOICE_810_TAX
  ADD CONSTRAINT EDI_O_INVOICE_810_TAX_EDI810 FOREIGN KEY (TRANSFER_ID)
  REFERENCES EDI_O_INVOICE_810 (TRANSFER_ID) ON DELETE CASCADE;

-- EDI_O_INVOICE_810_COM TABLE --
CREATE TABLE EDI_O_INVOICE_810_COM
(
  TRANSFER_ID       NUMBER NOT NULL,
  TRANSFER_STATUS   NUMBER DEFAULT 0 NOT NULL,
  MESSAGELINENUMBER NUMBER NOT NULL,
  PER01             VARCHAR2(2) NOT NULL CONSTRAINT PER01_VALID1
					CHECK(PER01 IN ('BD')),
  PER02             VARCHAR2(60) NULL,
  PER03             VARCHAR2(2) NULL CONSTRAINT PER03_VALID1
					CHECK(PER03 IN('TE','FX','EM')),
  PER04             VARCHAR2(80) NULL
)
;

COMMENT ON COLUMN EDI_O_INVOICE_810_COM.MESSAGELINENUMBER
  IS 'Generated as last used line number + 1 for current transfer_id';
COMMENT ON COLUMN EDI_O_INVOICE_810_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_O_INVOICE_810_COM.PER02
  IS 'PER02 - Free form name';
COMMENT ON COLUMN EDI_O_INVOICE_810_COM.PER03
  IS 'PER03 - Communication number qualifier: code identifying the type of communication number 
Possible values when filled:
"TE" = Telephone 
"FX" = Fax 
"EM" = Email
';
COMMENT ON COLUMN EDI_O_INVOICE_810_COM.PER04
  IS 'PER04 - Communication number: complete communications number including country or area code when applicable';

CREATE INDEX XIF1EDI_O_INVOICE_810_COM ON EDI_O_INVOICE_810_COM (TRANSFER_ID ASC);

ALTER TABLE EDI_O_INVOICE_810_COM
  ADD CONSTRAINT XPKEDI_O_INVOICE_810_COM PRIMARY KEY (TRANSFER_ID, MESSAGELINENUMBER);
  
ALTER TABLE EDI_O_INVOICE_810_COM
  ADD CONSTRAINT EDI_O_INVOICE_810_COM_EDI810 FOREIGN KEY (TRANSFER_ID)
  REFERENCES EDI_O_INVOICE_810 (TRANSFER_ID) ON DELETE CASCADE;

-- EDI_O_INVOICE_810_NAME TABLE --
CREATE TABLE EDI_O_INVOICE_810_NAME
(
  TRANSFER_ID       NUMBER NOT NULL,
  TRANSFER_STATUS   NUMBER DEFAULT 0 NOT NULL,
  MESSAGELINENUMBER NUMBER NOT NULL,
  N101              VARCHAR2(3) NOT NULL CONSTRAINT N101_VALID1
					CHECK(N101 IN ('ST','BT','VN')),
  N102              VARCHAR2(60) NULL,
  N103              VARCHAR2(2) NULL CONSTRAINT N103_VALID1
					CHECK (N103 IN ('91','92','ZZ')),
  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_INVOICE_810_NAME.TRANSFER_ID
  IS 'Transfer id from edi_o_invoice_810';
COMMENT ON COLUMN EDI_O_INVOICE_810_NAME.MESSAGELINENUMBER
  IS 'Generated as last used line number + 1 for current transfer_id';
COMMENT ON COLUMN EDI_O_INVOICE_810_NAME.N101
  IS 'N101- Entity identifier code: code identifying an organizational entity, a physical location, property or an individual
Possible values when filled:
"ST" = Ship To
"BT" = Bill To
"VN" = Vendor
';
COMMENT ON COLUMN EDI_O_INVOICE_810_NAME.N102
  IS 'N102 - Name: free form name';
COMMENT ON COLUMN EDI_O_INVOICE_810_NAME.N103
  IS 'N103 - Identification code qualifier: code designating the system/method of code structure used for Identification Code (67)
Possible values when filled:
"91" = Assigned by Seller or Seller"s Agent
"92" = Assigned by Buyer or Buyer"s Agent
"ZZ" = Mutually defined
';
COMMENT ON COLUMN EDI_O_INVOICE_810_NAME.N104
  IS 'N104 - Identification code: code identifying a party or other code (Consumption point in ABS, identified via fixed combination of "Customer#-Department#-Consumption Point#")';
COMMENT ON COLUMN EDI_O_INVOICE_810_NAME.N301
  IS 'N301 - Address information';
COMMENT ON COLUMN EDI_O_INVOICE_810_NAME.N302
  IS 'N302 - Address information';
COMMENT ON COLUMN EDI_O_INVOICE_810_NAME.N401
  IS 'N401 - Free form text for city';
COMMENT ON COLUMN EDI_O_INVOICE_810_NAME.N402
  IS 'N402 - Code (Standard State/Province) as defined by appropriate government agency';
COMMENT ON COLUMN EDI_O_INVOICE_810_NAME.N403
  IS 'N403 - Code defining international postal zone code excluding punctuation and blanks (zip code for United States';
COMMENT ON COLUMN EDI_O_INVOICE_810_NAME.N404
  IS 'N404 - Code identifying the country';

CREATE INDEX XIF1EDI_O_INVOICE_810_NAME ON EDI_O_INVOICE_810_NAME (TRANSFER_ID ASC);

ALTER TABLE EDI_O_INVOICE_810_NAME
  ADD CONSTRAINT XPKEDI_O_INVOICE_810_NAME PRIMARY KEY (TRANSFER_ID, MESSAGELINENUMBER);
  
ALTER TABLE EDI_O_INVOICE_810_NAME
  ADD CONSTRAINT EDI_O_INVOICE_810_NAME_EDI810 FOREIGN KEY (TRANSFER_ID)
  REFERENCES EDI_O_INVOICE_810 (TRANSFER_ID) ON DELETE CASCADE;

-- EDI_O_INVOICELINE_810 TABLE --
CREATE TABLE EDI_O_INVOICELINE_810
(
  TRANSFER_ID         NUMBER NOT NULL,
  TRANSFER_STATUS     NUMBER DEFAULT 0 NOT NULL,
  INVOICELINENUMBER   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,
  UNITOFMEASURECODE   VARCHAR2(3) NULL,
  IT101               VARCHAR2(20) NULL,
  IT102               VARCHAR2(10) NULL,
  IT103               VARCHAR2(2) NULL CONSTRAINT IT103_VALID1
					  CHECK(IT103 IN ('EA','CA','PC')),
  IT104               VARCHAR2(17) NULL,
  IT105               VARCHAR2(2) NULL,
  IT106               VARCHAR2(2) NULL CONSTRAINT IT106_VALID1
					  CHECK(IT106 IN ('VC','VN')),
  IT107               VARCHAR2(48) NULL,
  IT108               VARCHAR2(2) NULL CONSTRAINT IT108_VALID1
					  CHECK(IT108 IN ('IN')),
  IT109               VARCHAR2(48) NULL,
  IT110               VARCHAR2(2) NULL CONSTRAINT IT110_VALID1
					  CHECK(IT110 IN ('SN','LT','CR')),
  IT111               VARCHAR2(48) NULL,
  IT112               VARCHAR2(2) NULL CONSTRAINT IT112_VALID1
					  CHECK(IT112 IN ('SN','LT','CR')),
  IT113               VARCHAR2(48) NULL,
  IT114               VARCHAR2(2) NULL CONSTRAINT IT114_VALID1
					  CHECK(IT114 IN('SN','LT','CR')),
  IT115               VARCHAR2(48) NULL,
  PID01               VARCHAR2(1) NULL,
  PID02               VARCHAR2(3) NULL,
  PID03               VARCHAR2(2) NULL,
  PID04               VARCHAR2(12) NULL,
  PID05               VARCHAR2(80) NULL
)
;

COMMENT ON COLUMN EDI_O_INVOICELINE_810.TRANSFER_ID
  IS 'Transfer id from edi_o_invoice_810';
COMMENT ON COLUMN EDI_O_INVOICELINE_810.INVOICELINENUMBER
  IS 'Generated as last used line number + 1 for current transfer_id (or copied from originating purchase order line)';
COMMENT ON COLUMN EDI_O_INVOICELINE_810.IT101
  IS 'IT101 - Assigned identification: alphanumeric characters assigned for differentiation within a transaction set (e.g. originating purchase order line)';
COMMENT ON COLUMN EDI_O_INVOICELINE_810.IT102
  IS 'IT102 - Quantity invoiced, number of units invoiced (supplier units)';
COMMENT ON COLUMN EDI_O_INVOICELINE_810.IT103
  IS 'IT103 - Unit or basis for measurement code: 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
"PC" = Piece
';
COMMENT ON COLUMN EDI_O_INVOICELINE_810.IT104
  IS 'IT104 - Unit price, price per unit of product, service, commodity, etc';
COMMENT ON COLUMN EDI_O_INVOICELINE_810.IT105
  IS 'IT105 - Basis of unit price code, Code identifying the type of unit price for an item (e.g. "PE" Price per each)';
COMMENT ON COLUMN EDI_O_INVOICELINE_810.IT106
  IS 'IT106 - Product/service ID qualifier: 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_INVOICELINE_810.IT107
  IS 'IT107  VARCHAR2(48)  Y  Null  IT107 -  Product/service ID: Identifying number for a product or service';
COMMENT ON COLUMN EDI_O_INVOICELINE_810.IT108
  IS 'IT108 - Product/service ID qualifier: 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_INVOICELINE_810.IT109
  IS 'IT109 - Product/Service ID: Identifying number for a product or service (e.g. buyer item number)';
COMMENT ON COLUMN EDI_O_INVOICELINE_810.IT110
  IS 'IT110 - 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_INVOICELINE_810.IT111
  IS 'IT111 - Identifying number for a product or service (e.g. contract number)';
COMMENT ON COLUMN EDI_O_INVOICELINE_810.IT112
  IS 'IT112 - 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_INVOICELINE_810.IT113
  IS 'IT113 a product or service (e.g. serial number)';
COMMENT ON COLUMN EDI_O_INVOICELINE_810.IT114
  IS 'IT114 - Identifying number for a product or service
Possible values when filled
"SN" = Serial Number
"LT" = Lot Number
"CR" = Contract number
';
--section break for pack_Script
COMMENT ON COLUMN EDI_O_INVOICELINE_810.IT115
  IS 'IT115 - Identifying number for a product or service (e.g. lot number)';
COMMENT ON COLUMN EDI_O_INVOICELINE_810.PID01
  IS 'PID01 - Item description type: code indicating the format of a description (e.g. "F")';
COMMENT ON COLUMN EDI_O_INVOICELINE_810.PID02
  IS 'PID02 - Code identifying the general class of a product or process characteristic
Not used for Intertrade
';
COMMENT ON COLUMN EDI_O_INVOICELINE_810.PID03
  IS 'PID03 - Code identifying the agency assigning the code values Not used for Intertrade';
COMMENT ON COLUMN EDI_O_INVOICELINE_810.PID04
  IS 'PID04 - Industry specific product description code
Not used for Intertrade
';
COMMENT ON COLUMN EDI_O_INVOICELINE_810.PID05
  IS 'PID05 - A free-form description to clarify the related data elements and their content';

CREATE INDEX XIF1EDI_O_INVOICELINE_810 ON EDI_O_INVOICELINE_810 (TRANSFER_ID ASC);

ALTER TABLE EDI_O_INVOICELINE_810
  ADD CONSTRAINT XPKEDI_O_INVOICELINE_810 PRIMARY KEY (TRANSFER_ID, INVOICELINENUMBER);
  
ALTER TABLE EDI_O_INVOICELINE_810
  ADD CONSTRAINT EDI_O_INVOICELINE_810_EDI810 FOREIGN KEY (TRANSFER_ID)
  REFERENCES EDI_O_INVOICE_810 (TRANSFER_ID) ON DELETE CASCADE;

-- EDI_O_INVOICELINE_810_TAX TABLE --
CREATE TABLE EDI_O_INVOICELINE_810_TAX
(
  TRANSFER_ID       NUMBER NOT NULL,
  TRANSFER_STATUS   NUMBER NOT NULL,
  INVOICELINENUMBER NUMBER NOT NULL,
  MESSAGELINENUMBER NUMBER NOT NULL,
  TXI01             VARCHAR2(2) NOT NULL CONSTRAINT TXI01_VALID2
					CHECK (TXI01 IN ('GS', 'PS')),
  TXI02             VARCHAR2(18) NULL,
  TXI03             VARCHAR2(10) NULL,
  TXI04             VARCHAR2(2) NULL CONSTRAINT TXI04_VALID
					CHECK (TXI04 IN ('CD', 'VD', 'VE')),
  TXI05             VARCHAR2(10) NULL,
  TXI06             VARCHAR2(1) NULL,
  TXI07		  	    VARCHAR2(1) NULL,
  TXI08             VARCHAR2(9) NULL,
  TXI09             VARCHAR2(20) NULL,
  TXI10             VARCHAR2(20) NULL,
  TAXTYPE           NUMBER DEFAULT 0 NOT NULL CONSTRAINT TAXTYPE_VALID
					CHECK (TAXTYPE IN (0, 1, 2, 3)),
  VENDORTAXNUMBER   VARCHAR2(20) NULL
)
;

COMMENT ON COLUMN EDI_O_INVOICELINE_810_TAX.TRANSFER_ID
  IS 'Transfer id from edi_o_invoice_810';
COMMENT ON COLUMN EDI_O_INVOICELINE_810_TAX.INVOICELINENUMBER
  IS 'InvoiceLineNumber from edi_o_invoiceline_810';
COMMENT ON COLUMN EDI_O_INVOICELINE_810_TAX.MESSAGELINENUMBER
  IS 'Generated as last used line number + 1 for current transfer_id';
COMMENT ON COLUMN EDI_O_INVOICELINE_810_TAX.TXI01
  IS 'TXI01 - Tax type code
Possible values when filled:
"GS" = Goods and services tax 
"PS" = State or Provincial tax on services 
';
COMMENT ON COLUMN EDI_O_INVOICELINE_810_TAX.TXI02
  IS 'TXI02 - Monetary amount';
COMMENT ON COLUMN EDI_O_INVOICELINE_810_TAX.TXI03
  IS 'TXI03 - Tax percentage';
COMMENT ON COLUMN EDI_O_INVOICELINE_810_TAX.TXI04
  IS 'TXI04 - Tax Jurisdiction Code Qualifier, code identifying the source of the data used in tax jurisdiction code
Possible values when filled:
"CD" = Customer defined
"VD" = Vendor defined
"VE" =  Vertex
Not used for Intertrade
';
COMMENT ON COLUMN EDI_O_INVOICELINE_810_TAX.TXI05
  IS 'TXI05 - Tax Jurisdiction Code, c ode identifying the taxing jurisdiction
Not used for Intertrade
';
COMMENT ON COLUMN EDI_O_INVOICELINE_810_TAX.TXI06
  IS 'TXI06 - Tax Exempt Code, code identifying exemption status from sales and use tax';
COMMENT ON COLUMN EDI_O_INVOICELINE_810_TAX.TXI07
  IS 'TXI07 - Relationship code, code indicating the relationship between entities
Not used for Intertrade
';
COMMENT ON COLUMN EDI_O_INVOICELINE_810_TAX.TXI08
  IS 'TXI08 - Dollar basis for percent, dollar basis to be used in the percent calculation of the allowance, charge or tax
Not used for Intertrade
';
COMMENT ON COLUMN EDI_O_INVOICELINE_810_TAX.TXI09
  IS 'TXI09 - Tax Identification Number, number assigned to a purchaser (buyer, orderer) by a taxing jurisdiction (state, county, etc.) often called a tax exemption number or certificate number
Not used for Intertrade
';
COMMENT ON COLUMN EDI_O_INVOICELINE_810_TAX.TXI10
  IS 'TXI10 - Assigned identification, alphanumeric characters assigned for differentiation within a transaction set
Not used for Intertrade
';
COMMENT ON COLUMN EDI_O_INVOICELINE_810_TAX.TAXTYPE
  IS 'Tax types as used at ABS.
Possible values:
US/CA:
0 = Not applicable
1 = Sales
2 = Rent
3 = Miscellaneous
EU:
0 = Not applicable
1 = Define tax
';
COMMENT ON COLUMN EDI_O_INVOICELINE_810_TAX.VENDORTAXNUMBER
  IS 'Tax identification number assigned to a vendor by a taxing jurisdiction (state, county etc.) for tax type code';

ALTER TABLE EDI_O_INVOICELINE_810_TAX
  ADD CONSTRAINT XPKEDI_O_INVOICELINE_810_TAX PRIMARY KEY (TRANSFER_ID, INVOICELINENUMBER, MESSAGELINENUMBER);
  
CREATE INDEX XIF1EDI_O_INVOICELINE_810_TAX ON EDI_O_INVOICELINE_810_TAX (TRANSFER_ID ASC);
CREATE INDEX XIF2EDI_O_INVOICELINE_810_TAX ON EDI_O_INVOICELINE_810_TAX (TRANSFER_ID ASC, INVOICELINENUMBER ASC);
 
ALTER TABLE EDI_O_INVOICELINE_810_TAX
  ADD CONSTRAINT EDIOINVLINE810TAX_EDIINVLINE FOREIGN KEY (TRANSFER_ID, INVOICELINENUMBER)
  REFERENCES EDI_O_INVOICELINE_810 (TRANSFER_ID, INVOICELINENUMBER) ON DELETE CASCADE;
  
ALTER TABLE EDI_O_INVOICELINE_810_TAX
  ADD CONSTRAINT EDIOINVLINE810TAX_EDIINV FOREIGN KEY (TRANSFER_ID)
  REFERENCES EDI_O_INVOICE_810 (TRANSFER_ID) ON DELETE CASCADE;

-- EDI_O_INVLINECHARGE_810 TABLE --
CREATE TABLE EDI_O_INVLINECHARGE_810
(
  TRANSFER_ID         NUMBER NOT NULL,
  TRANSFER_STATUS     NUMBER DEFAULT 0 NOT NULL,
  INVOICELINENUMBER   NUMBER NOT NULL,
  INVLINECHARGENUMBER NUMBER NOT NULL,
  CODE                VARCHAR2(6) NULL,
  DESCRIPTION         VARCHAR2(40) NULL,
  SAC01               VARCHAR2(1) NULL CONSTRAINT SAC01_VALID2
                      CHECK (SAC01 IN ('A', 'C')),
  SAC02               VARCHAR2(4) NULL CONSTRAINT SAC02_VALID2
                      CHECK (SAC02 IN ('C040', 'D500', 'F050', 'F920', 'G830', 'H090')),
  SAC03               VARCHAR2(2) NULL,
  SAC04               VARCHAR2(10) NULL,
  SAC05               VARCHAR2(15) NULL,
  SAC06               VARCHAR2(1) NULL CONSTRAINT SAC06_VALID2
                      CHECK (SAC06 IN ('1', '2', '3', '4', '5', '6', '7', '8', '9', 'A', 'Z')),
  SAC07               VARCHAR2(6) NULL,
  SAC08               VARCHAR2(9) NULL,
  SAC09               VARCHAR2(2) NULL,
  SAC10               VARCHAR2(15) NULL,
  SAC11               VARCHAR2(15) NULL,
  SAC12               VARCHAR2(2) NULL CONSTRAINT SAC12_VALID2
                      CHECK (SAC12 IN ('01', '20', '03', '04', '05', '06', '08', '09', '10', 'CC', 'PP')),
  SAC15               VARCHAR2(80) NULL
)
;

COMMENT ON COLUMN EDI_O_INVLINECHARGE_810.TRANSFER_ID
  IS 'Transfer id from edi_o_invoice_810';
COMMENT ON COLUMN EDI_O_INVLINECHARGE_810.INVOICELINENUMBER
  IS 'InvoiceLineNumber from edi_o_invoiceline_810';
COMMENT ON COLUMN EDI_O_INVLINECHARGE_810.INVLINECHARGENUMBER
  IS 'Generated as last used line number + 1 for current transfer_id and invoice line number';
COMMENT ON COLUMN EDI_O_INVLINECHARGE_810.CODE
  IS 'Special charge code as used in ABS or "-" in case of miscellaneous charge';
COMMENT ON COLUMN EDI_O_INVLINECHARGE_810.DESCRIPTION
  IS 'Description of charge';
COMMENT ON COLUMN EDI_O_INVLINECHARGE_810.SAC01
  IS 'SAC01 - Allowance or Charge Indicator, code which indicates an allowance or charge for the service specified
Possible values when filled:
"A" = Allowance
"C" = Charge
';
COMMENT ON COLUMN EDI_O_INVLINECHARGE_810.SAC02
  IS 'SAC02 - Service, Promotion, Allowance, or Charge Code, code identifying the service, promotion, allowance, or charge
Possible values when filled:
"C040" = Delivery Charge
"D500" = Handling
"F050" = Miscellaneous
"F920" = Quantity Surcharge
"G830" = Discount/Rebate
"H090" = Special Handling
';
COMMENT ON COLUMN EDI_O_INVLINECHARGE_810.SAC03
  IS 'SAC03 - Agency Qualifier Code, Code identifying the agency assigning the code values';
COMMENT ON COLUMN EDI_O_INVLINECHARGE_810.SAC04
  IS 'SAC04 - Agency Service, Promotion, Allowance, or Charge Code, agency maintained code identifying the service, promotion, allowance, or charge';
COMMENT ON COLUMN EDI_O_INVLINECHARGE_810.SAC05
  IS 'SAC05 - Monetary amount';
COMMENT ON COLUMN EDI_O_INVLINECHARGE_810.SAC06
  IS 'SAC06 - Allowance/Charge Percent Qualifier, code indicating on what basis allowance or charge percent is calculated
Possible values when filled:
"1" = Item List Cost
"2" = Item Net Cost
"3" = Discount/Gross
"4" = Discount/Net
"5" = Base Price per Unit
"6" = Base Price Amount
"7" = Base Price Amount Less Previous Discount
"8" = Net Monthly On All Invoices Past Due
"9" = Late Payment Charge Base Amount
"A" = Fuel Rate
"Z" = Mutually Defined
';
COMMENT ON COLUMN EDI_O_INVLINECHARGE_810.SAC07
  IS 'SAC07 - Percentage';
COMMENT ON COLUMN EDI_O_INVLINECHARGE_810.SAC08
  IS 'SAC08 - Rate, rate expressed in the standard monetary denomination for the currency specified';
COMMENT ON COLUMN EDI_O_INVLINECHARGE_810.SAC09
  IS 'SAC09 - Unit or Basis for Measurement Code, code specifying the units in which a value is being expressed, or manner in which a measurement has been taken';
COMMENT ON COLUMN EDI_O_INVLINECHARGE_810.SAC10
  IS 'SAC10 - Quantity, numeric value of quantity';
COMMENT ON COLUMN EDI_O_INVLINECHARGE_810.SAC11
  IS 'SAC11 - Quantity, numeric value of quantity
Will only be used to indicate a range in combination with SAC10
';
COMMENT ON COLUMN EDI_O_INVLINECHARGE_810.SAC12
  IS 'SAC12 - Allowance or Charge Method of Handling Code, c ode indicating method of handling for an allowance or charge 
Possible values when filled:
"01" = Bill Back
"20" = Off Invoice
"03" = Vendor Check to Customer
"04" = Credit Customer Account
"05" = Charge to be Paid by Vendor
"06" = Charge to be Paid by Customer
"08" = Off Gross Quantity Invoiced
"09" = Allowance To Be Issued by Vendor
"10" = Allowance To Be Issued by Reseller
"CC" = Collect
"PP"  = Prepaid
';
COMMENT ON COLUMN EDI_O_INVLINECHARGE_810.SAC15
  IS 'SAC15 -  A free-form description to clarify the related data elements and their content';

ALTER TABLE EDI_O_INVLINECHARGE_810
  ADD CONSTRAINT XPKEDI_O_INVLINECHARGE_810 PRIMARY KEY (TRANSFER_ID, INVOICELINENUMBER, INVLINECHARGENUMBER);
  
CREATE INDEX XIF1EDI_O_INVLINECHARGE_810 ON EDI_O_INVLINECHARGE_810 (TRANSFER_ID ASC);
CREATE INDEX XIF2EDI_O_INVLINECHARGE_810 ON EDI_O_INVLINECHARGE_810 (TRANSFER_ID ASC, INVOICELINENUMBER ASC);

ALTER TABLE EDI_O_INVLINECHARGE_810
  ADD CONSTRAINT EDIOINVLINECH810_EDIINV FOREIGN KEY (TRANSFER_ID)
  REFERENCES EDI_O_INVOICE_810 (TRANSFER_ID) ON DELETE CASCADE;
  
ALTER TABLE EDI_O_INVLINECHARGE_810
  ADD CONSTRAINT EDIOINVLINECH810_EDIINVLINE FOREIGN KEY (TRANSFER_ID, INVOICELINENUMBER)
  REFERENCES EDI_O_INVOICELINE_810 (TRANSFER_ID, INVOICELINENUMBER) ON DELETE CASCADE;

-- EDI_O_INVLINECHARGE_810_TAX TABLE --
CREATE TABLE EDI_O_INVLINECHARGE_810_TAX
(
  TRANSFER_ID         NUMBER NOT NULL,
  TRANSFER_STATUS     NUMBER DEFAULT 0 NOT NULL,
  INVOICELINENUMBER   NUMBER NOT NULL,
  INVLINECHARGENUMBER NUMBER NOT NULL,
  MESSAGELINENUMBER   NUMBER NOT NULL,
  TXI01               VARCHAR2(2) NOT NULL CONSTRAINT TXI01_VALID3
                      CHECK (TXI01 IN ('GS', 'PS')),
  TXI02               VARCHAR2(18) NULL,
  TXI03               VARCHAR2(10) NULL,
  TXI04               VARCHAR2(2) NULL CONSTRAINT TXI04_VALID2
                      CHECK (TXI04 IN ('CD', 'VD', 'VE')),
  TXI05               VARCHAR2(10) NULL,
  TXI06               VARCHAR2(1) NULL,
  TXI07               VARCHAR2(1) NULL,
  TXI08               VARCHAR2(9) NULL,
  TXI09               VARCHAR2(20) NULL,
  TXI10               VARCHAR2(20) NULL,
  TAXTYPE             NUMBER DEFAULT 0 NOT NULL CONSTRAINT TAXTYPE_VALID2
					  CHECK (TAXTYPE IN (0,1,2,3)),
  VENDORTAXNUMBER     VARCHAR2(20) NULL
)
;

COMMENT ON COLUMN EDI_O_INVLINECHARGE_810_TAX.TRANSFER_ID
  IS 'Transfer id from edi_o_invoice_810';
COMMENT ON COLUMN EDI_O_INVLINECHARGE_810_TAX.INVOICELINENUMBER
  IS 'InvoiceLineNumber from edi_o_invoiceline_810';
COMMENT ON COLUMN EDI_O_INVLINECHARGE_810_TAX.INVLINECHARGENUMBER
  IS 'InvLineChargeNumber from edi_o_invlinecharge_810';
COMMENT ON COLUMN EDI_O_INVLINECHARGE_810_TAX.MESSAGELINENUMBER
  IS 'Generated as last used line number + 1 for current transfer_id, invoice line number and invoice line charge number';
COMMENT ON COLUMN EDI_O_INVLINECHARGE_810_TAX.TXI01
  IS 'TXI01 - Tax type code
Possible values when filled:
"GS" = Goods and services tax 
"PS" = State or Provincial tax on services 
';
COMMENT ON COLUMN EDI_O_INVLINECHARGE_810_TAX.TXI02
  IS 'TXI02 - Monetary amount';
COMMENT ON COLUMN EDI_O_INVLINECHARGE_810_TAX.TXI03
  IS 'TXI03 - Tax percentage';
COMMENT ON COLUMN EDI_O_INVLINECHARGE_810_TAX.TXI04
  IS 'TXI04 - Tax Jurisdiction Code Qualifier, code identifying the source of the data used in tax jurisdiction code
Possible values when filled:
"CD" = Customer defined
"VD" = Vendor defined
"VE" =  Vertex
Not used for Intertrade
';
--section break for pack_Script
COMMENT ON COLUMN EDI_O_INVLINECHARGE_810_TAX.TXI05
  IS 'TXI05 - Tax Jurisdiction Code, c ode identifying the taxing jurisdiction
Not used for Intertrade
';
COMMENT ON COLUMN EDI_O_INVLINECHARGE_810_TAX.TXI06
  IS 'TXI06 - Tax Exempt Code, code identifying exemption status from sales and use tax';
COMMENT ON COLUMN EDI_O_INVLINECHARGE_810_TAX.TXI07
  IS 'TXI07 - Relationship code, code indicating the relationship between entities
Not used for Intertrade
';
COMMENT ON COLUMN EDI_O_INVLINECHARGE_810_TAX.TXI08
  IS 'TXI08 - Dollar basis for percent, dollar basis to be used in the percent calculation of the allowance, charge or tax
Not used for Intertrade
';
COMMENT ON COLUMN EDI_O_INVLINECHARGE_810_TAX.TXI09
  IS 'TXI09 - Tax Identification Number, number assigned to a purchaser (buyer, orderer) by a taxing jurisdiction (state, county, etc.) often called a tax exemption number or certificate number
Not used for Intertrade
';
COMMENT ON COLUMN EDI_O_INVLINECHARGE_810_TAX.TXI10
  IS 'TXI10 - Assigned identification, alphanumeric characters assigned for differentiation within a transaction set
Not used for Intertrade
';
COMMENT ON COLUMN EDI_O_INVLINECHARGE_810_TAX.TAXTYPE
  IS 'Tax types as used at ABS.
Possible values:
US/CA:
0 = Not applicable
1 = Sales
2 = Rent
3 = Miscellaneous
EU:
0 = Not applicable
1 = Define tax
';
COMMENT ON COLUMN EDI_O_INVLINECHARGE_810_TAX.VENDORTAXNUMBER
  IS 'Tax types as used at ABS.
Possible values:
US/CA:
0 = Not applicable
1 = Sales
2 = Rent
3 = Miscellaneous
EU:
0 = Not applicable
1 = Define tax
';

ALTER TABLE EDI_O_INVLINECHARGE_810_TAX
  ADD CONSTRAINT XPKEDI_O_INVLINECHARGE_810_TAX PRIMARY KEY (TRANSFER_ID, INVOICELINENUMBER, INVLINECHARGENUMBER, MESSAGELINENUMBER);
  
CREATE INDEX XIF1EDIOINVLINECHARGE810_TAX ON EDI_O_INVLINECHARGE_810_TAX (TRANSFER_ID ASC);
CREATE INDEX XIF2EDIOINVLINECHARGE810_TAX ON EDI_O_INVLINECHARGE_810_TAX (TRANSFER_ID ASC, INVOICELINENUMBER ASC, INVLINECHARGENUMBER ASC);

ALTER TABLE EDI_O_INVLINECHARGE_810_TAX
  ADD CONSTRAINT EDIOINVLINECH810TAX_EDIINV FOREIGN KEY (TRANSFER_ID)
  REFERENCES EDI_O_INVOICE_810 (TRANSFER_ID) ON DELETE CASCADE;
  
ALTER TABLE EDI_O_INVLINECHARGE_810_TAX
  ADD CONSTRAINT EDIOINVLINECH810TAX_EDIINVCH FOREIGN KEY (TRANSFER_ID, INVOICELINENUMBER, INVLINECHARGENUMBER)
  REFERENCES EDI_O_INVLINECHARGE_810 (TRANSFER_ID, INVOICELINENUMBER, INVLINECHARGENUMBER) ON DELETE CASCADE;
  
-- EDI_O_INVOICECHARGE_810 TABLE --
CREATE TABLE EDI_O_INVOICECHARGE_810
(
  TRANSFER_ID         NUMBER NOT NULL,
  TRANSFER_STATUS     NUMBER DEFAULT 0 NOT NULL,
  INVOICECHARGENUMBER NUMBER NOT NULL,
  CODE                VARCHAR2(6) NULL,
  DESCRIPTION         VARCHAR2(40) NULL,
  SAC01               VARCHAR2(1) NULL CONSTRAINT SAC01_VALID3
                      CHECK (SAC01 IN ('A', 'C')),
  SAC02               VARCHAR2(4) NULL CONSTRAINT SAC02_VALID3
                      CHECK (SAC02 IN ('A010', 'C030', 'C040', 'D240', 'D500', 'F050', 'F920', 'G830', 'H090')),
  SAC03               VARCHAR2(2) NULL,
  SAC04               VARCHAR2(10) NULL,
  SAC05               VARCHAR2(15) NULL,
  SAC06               VARCHAR2(1) NULL CONSTRAINT SAC06_VALID3
                      CHECK (SAC06 IN ('1', '2', '3', '4', '5', '6', '7', '8', '9', 'A', 'Z')),
  SAC07               VARCHAR2(6) NULL,
  SAC08               VARCHAR2(9) NULL,
  SAC09               VARCHAR2(2) NULL,
  SAC10               VARCHAR2(15) NULL,
  SAC11               VARCHAR2(15) NULL,
  SAC12               VARCHAR2(2) NULL CONSTRAINT SAC12_VALID3
                      CHECK (SAC12 IN ('01', '02', '03', '04', '05', '06', '08', '09', '10', 'CC', 'PP')),
  SAC15               VARCHAR2(80) NULL
)
;
 
COMMENT ON COLUMN EDI_O_INVOICECHARGE_810.TRANSFER_ID
  IS 'Transfer id from edi_o_invoice_810';
COMMENT ON COLUMN EDI_O_INVOICECHARGE_810.INVOICECHARGENUMBER
  IS 'Generated as last used line number + 1 for current transfer_id (or copied from originating invoice line)';
COMMENT ON COLUMN EDI_O_INVOICECHARGE_810.CODE
  IS 'Special charge code as used in ABS or "-" in case of miscellaneous charge';
COMMENT ON COLUMN EDI_O_INVOICECHARGE_810.DESCRIPTION
  IS 'Description of charge';
COMMENT ON COLUMN EDI_O_INVOICECHARGE_810.SAC01
  IS 'SAC01 - Allowance or Charge Indicator, code which indicates an allowance or charge for the service specified
Possible values when filled:
"A" = Allowance
"C" = Charge
';
COMMENT ON COLUMN EDI_O_INVOICECHARGE_810.SAC02
  IS 'SAC02 - Service, Promotion, Allowance, or Charge Code, code identifying the service, promotion, allowance, or charge 
Possible values when filled:
"A010" = Absolute Minimum Charge
"C030" = Drop Ship
"C040" = Delivery Charge
"D240" = Freight
"D500" = Handling
"F050" = Miscellaneous
"F920" = Quantity Surcharge
"G830" = Discount/Rebate
"H090" = Special Handling
';
COMMENT ON COLUMN EDI_O_INVOICECHARGE_810.SAC03
  IS 'SAC03 - Agency Qualifier Code, Code identifying the agency assigning the code values';
COMMENT ON COLUMN EDI_O_INVOICECHARGE_810.SAC04
  IS 'SAC04 - Agency Service, Promotion, Allowance, or Charge Code, agency maintained code identifying the service, promotion, allowance, or charge';
COMMENT ON COLUMN EDI_O_INVOICECHARGE_810.SAC05
  IS 'SAC05 - Monetary amount';
COMMENT ON COLUMN EDI_O_INVOICECHARGE_810.SAC06
  IS 'SAC06 - Allowance/Charge Percent Qualifier, code indicating on what basis allowance or charge percent is calculated
Possible values when filled:
"1" = Item List Cost
"2" = Item Net Cost
"3" = Discount/Gross
"4" = Discount/Net
"5" = Base Price per Unit
"6" = Base Price Amount
"7" = Base Price Amount Less Previous Discount
"8" = Net Monthly On All Invoices Past Due
"9" = Late Payment Charge Base Amount
"A" = Fuel Rate
"Z" = Mutually Defined
';
COMMENT ON COLUMN EDI_O_INVOICECHARGE_810.SAC07
  IS 'SAC07 - Percentage';
COMMENT ON COLUMN EDI_O_INVOICECHARGE_810.SAC08
  IS 'SAC08 - Rate, rate expressed in the standard monetary denomination for the currency specified';
COMMENT ON COLUMN EDI_O_INVOICECHARGE_810.SAC09
  IS 'SAC09 - Unit or Basis for Measurement Code, code specifying the units in which a value is being expressed, or manner in which a measurement has been taken';
COMMENT ON COLUMN EDI_O_INVOICECHARGE_810.SAC10
  IS 'SAC10 - Quantity, numeric value of quantity';
COMMENT ON COLUMN EDI_O_INVOICECHARGE_810.SAC11
  IS 'SAC11 - Quantity, numeric value of quantity
Will only be used to indicate a range in combination with SAC10
';
COMMENT ON COLUMN EDI_O_INVOICECHARGE_810.SAC12
  IS 'SAC12 - Allowance or Charge Method of Handling Code, c ode indicating method of handling for an allowance or charge
Possible values when filled:
"01" = Bill Back
"02" = Off Invoice
"03" = Vendor Check to Customer
"04" = Credit Customer Account
"05" = Charge to be Paid by Vendor
"06" = Charge to be Paid by Customer
"08" = Off Gross Quantity Invoiced
"09" = Allowance To Be Issued by Vendor
"10" = Allowance To Be Issued by Reseller
"CC" = Collect
"PP"  = Prepaid
';
COMMENT ON COLUMN EDI_O_INVOICECHARGE_810.SAC15
  IS 'SAC15 -  A free-form description to clarify the related data elements and their content';

ALTER TABLE EDI_O_INVOICECHARGE_810
  ADD CONSTRAINT XPKEDI_O_INVOICECHARGE_810 PRIMARY KEY (TRANSFER_ID, INVOICECHARGENUMBER);
  
CREATE INDEX XIF1EDI_O_INVOICECHARGE_810 ON EDI_O_INVOICECHARGE_810 (TRANSFER_ID ASC);
  
ALTER TABLE EDI_O_INVOICECHARGE_810
  ADD CONSTRAINT EDI_O_INVOICECHARGE_810_EDIINV FOREIGN KEY (TRANSFER_ID)
  REFERENCES EDI_O_INVOICE_810 (TRANSFER_ID) ON DELETE CASCADE;

  -- EDI_O_INVOICECHARGE_810_TAX TABLE --
CREATE TABLE EDI_O_INVOICECHARGE_810_TAX
(
  TRANSFER_ID         NUMBER NOT NULL,
  TRANSFER_STATUS     NUMBER DEFAULT 0 NOT NULL,
  INVOICECHARGENUMBER NUMBER NOT NULL,
  MESSAGELINENUMBER   NUMBER NOT NULL,
  TXI01               VARCHAR2(2) NOT NULL CONSTRAINT TXI01_VALID4
                      CHECK (TXI01 IN ('GS', 'PS')),
  TXI02               VARCHAR2(18) NULL,
  TXI03               VARCHAR2(10) NULL,
  TXI04               VARCHAR2(2) NULL CONSTRAINT TXI04_VALID3
                      CHECK (TXI04 IN ('CD', 'VD', 'VE')),
  TXI05               VARCHAR2(10) NULL,
  TXI06               VARCHAR2(1) NULL,
  TXI07               VARCHAR2(1) NULL,
  TXI08               VARCHAR2(9) NULL,
  TXI09               VARCHAR2(20) NULL,
  TXI10               VARCHAR2(20) NULL,
  TAXTYPE             NUMBER DEFAULT 0 NOT NULL CONSTRAINT TAXTYPE_VALID3
                      CHECK (TAXTYPE IN (0 ,1, 2, 3)),
  VENDORTAXNUMBER     VARCHAR2(20) NULL
)
;

COMMENT ON COLUMN EDI_O_INVOICECHARGE_810_TAX.TRANSFER_ID
  IS 'Transfer id from edi_o_invoice_810';
COMMENT ON COLUMN EDI_O_INVOICECHARGE_810_TAX.INVOICECHARGENUMBER
  IS 'InvoiceChargeNumber from edi_o_invoicecharge';
COMMENT ON COLUMN EDI_O_INVOICECHARGE_810_TAX.MESSAGELINENUMBER
  IS 'Generated as last used line number + 1 for current transfer_id';
COMMENT ON COLUMN EDI_O_INVOICECHARGE_810_TAX.TXI01
  IS 'TXI01 - Tax type code
Possible values when filled:
"GS" = Goods and services tax 
"PS" = State or Provincial tax on services 
';
COMMENT ON COLUMN EDI_O_INVOICECHARGE_810_TAX.TXI02
  IS 'TXI02 - Monetary amount';
COMMENT ON COLUMN EDI_O_INVOICECHARGE_810_TAX.TXI03
  IS 'TXI03 - Tax percentage';
COMMENT ON COLUMN EDI_O_INVOICECHARGE_810_TAX.TXI04
  IS 'TXI04 - Tax Jurisdiction Code Qualifier, code identifying the source of the data used in tax jurisdiction code
Possible values when filled:
"CD" = Customer defined
"VD" = Vendor defined
"VE" =  Vertex
Not used for Intertrade
';
COMMENT ON COLUMN EDI_O_INVOICECHARGE_810_TAX.TXI05
  IS 'TXI05 - Tax Jurisdiction Code, c ode identifying the taxing jurisdiction
Not used for Intertrade
';
COMMENT ON COLUMN EDI_O_INVOICECHARGE_810_TAX.TXI06
  IS 'TXI06 - Tax Exempt Code, code identifying exemption status from sales and use tax';
COMMENT ON COLUMN EDI_O_INVOICECHARGE_810_TAX.TXI07
  IS 'TXI07 - Relationship code, code indicating the relationship between entities
Not used for Intertrade
';
COMMENT ON COLUMN EDI_O_INVOICECHARGE_810_TAX.TXI08
  IS 'TXI08 - Dollar basis for percent, dollar basis to be used in the percent calculation of the allowance, charge or tax Not used for Intertrade';
COMMENT ON COLUMN EDI_O_INVOICECHARGE_810_TAX.TXI09
  IS 'TXI09 - Tax Identification Number, number assigned to a purchaser (buyer, orderer) by a taxing jurisdiction (state, county, etc.) often called a tax exemption number or certificate number
Not used for Intertrade
';
COMMENT ON COLUMN EDI_O_INVOICECHARGE_810_TAX.TXI10
  IS 'TXI10 - Assigned identification, alphanumeric characters assigned for differentiation within a transaction set
Not used for Intertrade
';
COMMENT ON COLUMN EDI_O_INVOICECHARGE_810_TAX.TAXTYPE
  IS 'Tax types as used at ABS.
Possible values:
US/CA:
0 = Not applicable
1 = Sales
2 = Rent
3 = Miscellaneous
EU:
0 = Not applicable
1 = Define tax
';
COMMENT ON COLUMN EDI_O_INVOICECHARGE_810_TAX.VENDORTAXNUMBER
  IS 'Tax identification number assigned to a vendor by a taxing jurisdiction (state, county etc.) for tax type code';

ALTER TABLE EDI_O_INVOICECHARGE_810_TAX
  ADD CONSTRAINT XPKEDI_O_INVOICECHARGE_810_TAX PRIMARY KEY (TRANSFER_ID, INVOICECHARGENUMBER, MESSAGELINENUMBER);
  
CREATE INDEX XIF1EDIOINVOICECHARGE810TAX ON EDI_O_INVOICECHARGE_810_TAX (TRANSFER_ID ASC);
CREATE INDEX XIF2EDIOINVOICECHARGE810TAX ON EDI_O_INVOICECHARGE_810_TAX (TRANSFER_ID ASC, INVOICECHARGENUMBER ASC);
  
ALTER TABLE EDI_O_INVOICECHARGE_810_TAX
  ADD CONSTRAINT EDIOINVOICECH810TAX_EDIINV FOREIGN KEY (TRANSFER_ID)
  REFERENCES EDI_O_INVOICE_810 (TRANSFER_ID) ON DELETE CASCADE;
  
ALTER TABLE EDI_O_INVOICECHARGE_810_TAX
  ADD CONSTRAINT EDIOINVOICECH810TAX_EDIINVCH FOREIGN KEY (TRANSFER_ID, INVOICECHARGENUMBER)
  REFERENCES EDI_O_INVOICECHARGE_810 (TRANSFER_ID, INVOICECHARGENUMBER) ON DELETE CASCADE;

  -- EDI_O_INVOICE_810_LOG TABLE --
CREATE TABLE EDI_O_INVOICE_810_LOG
(
  TRANSFER_ID     NUMBER NOT NULL,
  TRANSFER_STATUS NUMBER DEFAULT 0 NOT NULL CONSTRAINT TRANSFER_STATUS_VALID59
                  CHECK (TRANSFER_STATUS IN (0, 1)),
  TEXT            VARCHAR2(1000) NULL,
  PROCESS_STATUS  NUMBER DEFAULT 0 NOT NULL CONSTRAINT PROCESS_STATUS_VALID2
                  CHECK (PROCESS_STATUS IN (0 , 1)),
  TIMESTAMP       DATE DEFAULT SYSDATE NOT NULL
)
;

COMMENT ON COLUMN EDI_O_INVOICE_810_LOG.TRANSFER_ID
  IS 'Transfer id from edi_o_invoice_810';
COMMENT ON COLUMN EDI_O_INVOICE_810_LOG.TRANSFER_STATUS
  IS '0 = ready to be read, 1 = read correctly';
COMMENT ON COLUMN EDI_O_INVOICE_810_LOG.PROCESS_STATUS
  IS '0 = processed correctly, 1 = process stopped with errors';

ALTER TABLE EDI_O_INVOICE_810_LOG
  ADD CONSTRAINT XPKEDI_O_INVOICE_810_LOG PRIMARY KEY (TRANSFER_ID);
  
ALTER TABLE EDI_O_INVOICE_810_LOG
  ADD CONSTRAINT EDI_O_INVOICE_810_LOG_EDIINV FOREIGN KEY (TRANSFER_ID)
  REFERENCES EDI_O_INVOICE_810 (TRANSFER_ID) ON DELETE CASCADE;

-- EDI_TRANSACTION_LINK TABLE --
CREATE TABLE EDI_TRANSACTION_LINK
(
	EDI_TABLENAME       VARCHAR2(32) NOT NULL,
	EDI_PROCEDURECALL   VARCHAR2(128) NOT NULL,
	TIMESTAMP           DATE DEFAULT SYSDATE NOT NULL
)
;
COMMENT ON COLUMN EDI_TRANSACTION_LINK.EDI_TABLENAME
  IS 'LOG Table name of EDI base table';
  
COMMENT ON COLUMN EDI_TRANSACTION_LINK.EDI_PROCEDURECALL
  IS 'Procedure name to be executed';

ALTER TABLE EDI_TRANSACTION_LINK
  ADD CONSTRAINT XPKEDI_TRANSACTION_LINK PRIMARY KEY (EDI_TABLENAME,EDI_PROCEDURECALL);