----ABS-57622/ABS-79741
----------EDI_O_PURCHASEORDERACK------------------
ALTER TABLE EDI_O_PURCHASEORDERACK ADD (N201 VARCHAR2(60) NULL, N202 VARCHAR2(60) NULL);
COMMENT ON COLUMN EDI_O_PURCHASEORDERACK.N201
 IS 'N201 - Free form name';
COMMENT ON COLUMN EDI_O_PURCHASEORDERACK.N202 	
 IS 'N202 - Free form name';

------------------EDI_O_ADVSHIPNOTICE----------------------
CREATE TABLE EDI_O_ADVSHIPNOTICE
(  TRANSFER_ID 		  NUMBER NOT NULL,
  TRANSFER_STATUS     NUMBER DEFAULT 0 NOT NULL,
  PLANTCODE           VARCHAR2(6) NOT NULL,
  DOCUMENTNUMBER	  VARCHAR2(25) NOT NULL,
  DELIVERYDATE		  DATE NOT NULL,
  DOCUMENTTTYPE		  NUMBER DEFAULT 0 NOT NULL
				      CONSTRAINT DOCUMENTTTYPE_VALID1
					  CHECK(DOCUMENTTTYPE IN (0,1,2)),
  CUSTOMERNUMBER	  NUMBER NULL,
  SUPPLIERNUMBER	  NUMBER NULL,
  BUSINESSUNITCODE    VARCHAR2(6) NULL,
  FILENAME			  VARCHAR2(255) NULL,
  EMAILDOCUMENT_ID	  NUMBER NULL,
  EXPORT_STATUS 	  NUMBER DEFAULT 0 NOT NULL
					  CONSTRAINT EXPORT_STATUS_VALID1
					  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,  
  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,
  BSN01				  VARCHAR2(2) NULL,
  BSN02	              VARCHAR2(30)NULL,
  BSN03	              VARCHAR2(22)NULL,
  BSN04	              VARCHAR2(8) NULL,
  BSN05	              VARCHAR2(30)NULL,
  HL01 				  VARCHAR2(12)NULL,
  HL02                VARCHAR2(12)NULL,
  HL03	 			  VARCHAR2(2)NULL,
  REF01				  VARCHAR2(3)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_O_ADVSHIPNOTICE.PLANTCODE
	IS 'Plant code where the process was run that imports the csv-file';

COMMENT ON COLUMN EDI_O_ADVSHIPNOTICE.DOCUMENTNUMBER
	IS 'Document number as used in ABS.';

COMMENT ON COLUMN EDI_O_ADVSHIPNOTICE.DELIVERYDATE
	IS 'Delivery date of delivery document';

COMMENT ON COLUMN EDI_O_ADVSHIPNOTICE.DOCUMENTTTYPE
	IS 'Document type in ABS.  Possible values:
0 = Not applicable
1 = Consignment Note
2 = Shipment List';

COMMENT ON COLUMN EDI_O_ADVSHIPNOTICE.CUSTOMERNUMBER
	IS 'Customer number in ABS';

COMMENT ON COLUMN EDI_O_ADVSHIPNOTICE.SUPPLIERNUMBER
	IS 'Supplier number in ABS';

COMMENT ON COLUMN EDI_O_ADVSHIPNOTICE.BUSINESSUNITCODE
	IS 'Business unit in ABS';

COMMENT ON COLUMN EDI_O_ADVSHIPNOTICE.FILENAME
	IS 'File name of the export file';

COMMENT ON COLUMN EDI_O_ADVSHIPNOTICE.EMAILDOCUMENT_ID
	IS 'Link to the sent email if any';

COMMENT ON COLUMN EDI_O_ADVSHIPNOTICE.EXPORT_STATUS
	IS 'Indicates if an Advanced Shipping Notice (855) has been exported
0 = Not applicable/Not sent yet
1 = Adv Shipping Notice sent';

COMMENT ON COLUMN EDI_O_ADVSHIPNOTICE.MESSAGETYPE
	IS 'Transaction set type (same as ST01), most likely "856" for Intertade';

COMMENT ON COLUMN EDI_O_ADVSHIPNOTICE.VERSIONNUMBER
	IS 'Version number (same as GS08), filled with "00410" for Intertrade';

COMMENT ON COLUMN EDI_O_ADVSHIPNOTICE.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_ADVSHIPNOTICE.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_ADVSHIPNOTICE.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_ADVSHIPNOTICE.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_ADVSHIPNOTICE.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_ADVSHIPNOTICE.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_ADVSHIPNOTICE.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_ADVSHIPNOTICE.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_ADVSHIPNOTICE.ISA09
	IS 'ISA09 - Date of the interchange Format: YYMMDD';

COMMENT ON COLUMN EDI_O_ADVSHIPNOTICE.ISA10
	IS 'ISA10 - Timestamp of the interchange; format HHMM';

COMMENT ON COLUMN EDI_O_ADVSHIPNOTICE.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_ADVSHIPNOTICE.ISA12
	IS 'ISA12 - Code specifying the version number of the interchange control segments (e.g. "00401")';

COMMENT ON COLUMN EDI_O_ADVSHIPNOTICE.ISA13
	IS 'ISA13 - Interchange control number assigned by the interchange sender';

COMMENT ON COLUMN EDI_O_ADVSHIPNOTICE.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_ADVSHIPNOTICE.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_ADVSHIPNOTICE.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_ADVSHIPNOTICE.GS01
	IS 'GS01 - Functional identifier code for a group of application related transaction sets (e.g. "SH")';

COMMENT ON COLUMN EDI_O_ADVSHIPNOTICE.GS02
	IS 'GS02 - Code to identify the party sending the transmission; codes agreed to by trading partners';

COMMENT ON COLUMN EDI_O_ADVSHIPNOTICE.GS03
	IS 'GS03 - Code to identify the party receiving the transmission; codes agreed to by trading partners';

COMMENT ON COLUMN EDI_O_ADVSHIPNOTICE.GS04
	IS 'GS04 - Date of the interchange Format: CCYYMMDD';

COMMENT ON COLUMN EDI_O_ADVSHIPNOTICE.GS05
	IS 'GS05 - Timestamp of the interchange; format HHMM, HHMMSS or HHMMSSDD';

COMMENT ON COLUMN EDI_O_ADVSHIPNOTICE.GS06
	IS 'GS06 - Group control number assigned, originated and maintained by sender';

COMMENT ON COLUMN EDI_O_ADVSHIPNOTICE.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_ADVSHIPNOTICE.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_ADVSHIPNOTICE.ST01
	IS 'ST01 - Code uniquely identifying a transaction set (e.g. "856" Ship notice/Manifest)';

COMMENT ON COLUMN EDI_O_ADVSHIPNOTICE.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_ADVSHIPNOTICE.BSN01
	IS 'BSN01 - Code identifying purpose of transaction set (e.g. "00" = Original)
Possible values when filled:
"00" = Original
"14" = Advance Notification';

COMMENT ON COLUMN EDI_O_ADVSHIPNOTICE.BSN02
	IS 'BSN02 - Unique control number assigned by the original shipper to identify a specific shipment
(e.g. consignment note number in ABS)';

COMMENT ON COLUMN EDI_O_ADVSHIPNOTICE.BSN03
	IS 'BSN03 - Date the shipment is created Format: CCYYMMDD';

COMMENT ON COLUMN EDI_O_ADVSHIPNOTICE.BSN04
	IS 'BSN04 - Timestamp the shipment is created; format HHMM';

COMMENT ON COLUMN EDI_O_ADVSHIPNOTICE.BSN05
	IS 'BSN05 - Code indicating the hierarchical application structure of a transaction set that utilizes the HL segment to identify the structure of the transaction set (e.g. "0004" = Shipment, Order, Item)';

COMMENT ON COLUMN EDI_O_ADVSHIPNOTICE.HL01
	IS 'HL01 - Hierarchical ID Number: unique number assigned by the sender to identify a particular data segment in a hierarchical structure (e.g. "1" since this is our 1st segment)';

COMMENT ON COLUMN EDI_O_ADVSHIPNOTICE.HL02
	IS 'HL02 - Hierarchical parent ID Number: identification number of the next higher hierarchical data segment that the data segment is subordinate to (e.g. null since this is our top level)';

COMMENT ON COLUMN EDI_O_ADVSHIPNOTICE.HL03
	IS 'HL03 - Hierarchical level code: code defining the characteristic of a level in a hierarchical structure (e.g. "S" = Shipment)';

COMMENT ON COLUMN EDI_O_ADVSHIPNOTICE.REF01
	IS 'REF01 - Reference identification qualifier: code to identify the reference identification 
Possible values when filled:
"BM"= Bill of lading number
"CN"= Carrier reference number
"MB"= Master Bill of Lading
"PK"= Packing list number
"ZZ"= Mutually defined';

COMMENT ON COLUMN EDI_O_ADVSHIPNOTICE.REF02
	IS 'REF02 - Reference information as defined for a specific transaction set or as specified by the reference identification qualifier';

COMMENT ON COLUMN EDI_O_ADVSHIPNOTICE.DTM01
	IS 'DTM01 - Code specifying type of date or time, or both date and time
Possible values when filled:
"002"= Delivery requested
"011"= Shipped
"017"= Estimated delivery
"067"= Current schedule delivery
"068"= Current schedule ship';

COMMENT ON COLUMN EDI_O_ADVSHIPNOTICE.DTM02
	IS 'DTM02 - Requested delivery date Format: CCYYMMDD';

COMMENT ON COLUMN EDI_O_ADVSHIPNOTICE.CTT01
	IS 'CTT01 - Total number of line items in the transaction set';
--section break for pack_Script
COMMENT ON COLUMN EDI_O_ADVSHIPNOTICE.CTT02
	IS 'CTT02 - Sum of values of the specified data element
Not used for Intertrade';

COMMENT ON COLUMN EDI_O_ADVSHIPNOTICE.SE01
	IS 'SE01 - Total number of segments included in a transaction set including ST and SE segments';

COMMENT ON COLUMN EDI_O_ADVSHIPNOTICE.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_ADVSHIPNOTICE.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_ADVSHIPNOTICE.GE02
	IS 'GE02 - Group control number, assigned, generated and maintained by the sender';

COMMENT ON COLUMN EDI_O_ADVSHIPNOTICE.IEA01
	IS 'IEA01 - A count of the number of functional groups included in an interchange';

COMMENT ON COLUMN EDI_O_ADVSHIPNOTICE.IEA02
	IS 'A control number assigned by the interchange sender';

ALTER TABLE EDI_O_ADVSHIPNOTICE
  ADD CONSTRAINT XPKEDI_O_ADVSHIPNOTICE PRIMARY KEY (TRANSFER_ID);

CREATE INDEX XIE1EDI_O_ADVSHIPNOTICE ON EDI_O_ADVSHIPNOTICE(PLANTCODE ASC);

CREATE INDEX XIE2EDI_O_ADVSHIPNOTICE ON EDI_O_ADVSHIPNOTICE(DOCUMENTNUMBER ASC);

CREATE INDEX XIE3EDI_O_ADVSHIPNOTICE ON EDI_O_ADVSHIPNOTICE(CUSTOMERNUMBER ASC);

CREATE INDEX XIE4EDI_O_ADVSHIPNOTICE ON EDI_O_ADVSHIPNOTICE(SUPPLIERNUMBER ASC);

--------------EDI_O_ADVSHIPNOTICE_PRF-------------------------

CREATE TABLE EDI_O_ADVSHIPNOTICE_PRF
(
TRANSFER_ID 		NUMBER NOT NULL,
TRANSFER_STATUS     NUMBER DEFAULT 0 NOT NULL,
MESSAGELINENUMBER	NUMBER NOT NULL,
HL01				VARCHAR2(12) NULL,
HL02				VARCHAR2(12) NULL,
HL03				VARCHAR2(2) NULL,
PRF01				VARCHAR2(22) NULL,
PRF02				VARCHAR2(30) NULL,
PRF04				VARCHAR2(8) NOT NULL
);

COMMENT ON COLUMN EDI_O_ADVSHIPNOTICE_PRF.HL01
	IS 'HL01 - Hierarchical ID Number: unique number assigned by the sender to identify a particular data segment in a hierarchical structure (e.g. same as message line number)';

COMMENT ON COLUMN EDI_O_ADVSHIPNOTICE_PRF.HL02
	IS 'HL02 - Hierarchical parent ID Number: identification number of the next higher hierarchical data segment that the data segment is subordinate to (e.g. 1 since this is our 2nd level)';

COMMENT ON COLUMN EDI_O_ADVSHIPNOTICE_PRF.HL03
	IS 'HL03 - Hierarchical level code: code defining the characteristic of a level in a hierarchical structure (e.g. "O" = Order)
';

COMMENT ON COLUMN EDI_O_ADVSHIPNOTICE_PRF.PRF01
	IS 'PRF01 - Identifying number for Purchase Order assigned by the purchaser/orderer';

COMMENT ON COLUMN EDI_O_ADVSHIPNOTICE_PRF.PRF02
	IS 'PRF02 - Number identifying a release against a purchase order placed by the parties involved Not used for Intertrade';

COMMENT ON COLUMN EDI_O_ADVSHIPNOTICE_PRF.PRF04
	IS 'PRF04 - Date assigned by the purchaser to the purchase order. Format: CCYYMMDD';

ALTER TABLE EDI_O_ADVSHIPNOTICE_PRF
  ADD CONSTRAINT XPKEDI_O_ADVSHIPNOTICE_PRF PRIMARY KEY (TRANSFER_ID,MESSAGELINENUMBER);

CREATE INDEX XIF1EDI_O_ADVSHIPNOTICE_PRF ON EDI_O_ADVSHIPNOTICE_PRF(TRANSFER_ID);

ALTER TABLE EDI_O_ADVSHIPNOTICE_PRF
  ADD CONSTRAINT  EDI_O_ADVSHIPNOTICE_PRF_TRANSF FOREIGN KEY (TRANSFER_ID) REFERENCES EDI_O_ADVSHIPNOTICE (TRANSFER_ID); 

----------------EDI_O_ADVSHIPNOTICE_COM-------------------

CREATE TABLE EDI_O_ADVSHIPNOTICE_COM
(
TRANSFER_ID			NUMBER NOT NULL,
TRANSFER_STATUS     NUMBER DEFAULT 0 NOT NULL,
MESSAGELINENUMBER	NUMBER NOT NULL,
PER01				VARCHAR2(2) NOT NULL,
PER02				VARCHAR2(60) NULL,
PER03				VARCHAR2(2) NULL,
PER04				VARCHAR2(80) NULL
);

COMMENT ON COLUMN EDI_O_ADVSHIPNOTICE_COM.PER01
	IS 'PER01- Contact function code: code identifying the major duty or responsibility of the person or group named
Possible values when filled:
"DI"= Delivery Instructions Contact
';

COMMENT ON COLUMN EDI_O_ADVSHIPNOTICE_COM.PER02
	IS 'ER02 - Free form name';

COMMENT ON COLUMN EDI_O_ADVSHIPNOTICE_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_ADVSHIPNOTICE_COM.PER04
	IS 'PER04 - Communication number: complete communications number including country or area code when applicable';

ALTER TABLE EDI_O_ADVSHIPNOTICE_COM
  ADD CONSTRAINT XPKEDI_O_ADVSHIPNOTICE_COM PRIMARY KEY (TRANSFER_ID,MESSAGELINENUMBER);
  
CREATE INDEX XIF1EDI_O_ADVSNOTICE_COM ON EDI_O_ADVSHIPNOTICE_COM(TRANSFER_ID ASC);

ALTER TABLE EDI_O_ADVSHIPNOTICE_COM
  ADD CONSTRAINT  EDI_O_ADVSHIPNOTICE_COM_TRANSF FOREIGN KEY (TRANSFER_ID) REFERENCES EDI_O_ADVSHIPNOTICE (TRANSFER_ID); 

---------------EDI_O_ADVSHIPNOTICE_NAME------------------

CREATE TABLE EDI_O_ADVSHIPNOTICE_NAME
(
TRANSFER_ID			NUMBER NOT NULL,
TRANSFER_STATUS     NUMBER DEFAULT 0 NOT NULL,
MESSAGELINENUMBER	NUMBER NOT NULL,
N101				VARCHAR2(3)NOT NULL
					CONSTRAINT EDI_O_ADVSHIPNAME_VALID1
					  CHECK(N101 IN ('ST','VN')),
N102				VARCHAR2(60)NULL,
N103				VARCHAR2(2)NULL
					CONSTRAINT EDI_O_ADVSHIPNAME_VALID2 
					  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_ADVSHIPNOTICE_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
"VN"= Vendor';

COMMENT ON COLUMN EDI_O_ADVSHIPNOTICE_NAME.N102
	IS 'N102 - Name: free form name';

COMMENT ON COLUMN EDI_O_ADVSHIPNOTICE_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_ADVSHIPNOTICE_NAME.N104
	IS 'N104 - Identification code: code identifying a party or other code (Consumption point in ABS)';

COMMENT ON COLUMN EDI_O_ADVSHIPNOTICE_NAME.N301
	IS 'N301 - Address information';

COMMENT ON COLUMN EDI_O_ADVSHIPNOTICE_NAME.N302
	IS 'N302 - Address information';

COMMENT ON COLUMN EDI_O_ADVSHIPNOTICE_NAME.N401
	IS 'N401 - Free form text for city';

COMMENT ON COLUMN EDI_O_ADVSHIPNOTICE_NAME.N402
	IS 'N402 - Code (Standard State/Province) as defined by appropriate government agency';

COMMENT ON COLUMN EDI_O_ADVSHIPNOTICE_NAME.N403
	IS 'N403 - Code defining international postal zone code excluding punctuation and blanks (zip code for United States';

COMMENT ON COLUMN EDI_O_ADVSHIPNOTICE_NAME.N404
	IS 'N404 - Code identifying the country';

ALTER TABLE EDI_O_ADVSHIPNOTICE_NAME
  ADD CONSTRAINT XPKEDI_O_ADVSHIPNOT_NAME PRIMARY KEY (TRANSFER_ID,MESSAGELINENUMBER);

CREATE INDEX XIF1EDI_O_ADVSNOTICE_NAME ON EDI_O_ADVSHIPNOTICE_NAME (TRANSFER_ID ASC);

ALTER TABLE EDI_O_ADVSHIPNOTICE_NAME
  ADD CONSTRAINT  EDI_O_ADVSHIPNNAME_TRANSF FOREIGN KEY (TRANSFER_ID)
REFERENCES EDI_O_ADVSHIPNOTICE (TRANSFER_ID); 

--------------EDI_O_ADVSHIPNOTICELINE-----------------

CREATE TABLE EDI_O_ADVSHIPNOTICELINE
(
TRANSFER_ID				NUMBER NOT NULL,
TRANSFER_STATUS     	NUMBER DEFAULT 0 NOT NULL,
MESSAGELINENUMBER		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,
HL01					VARCHAR2(12) NULL,
HL02					VARCHAR2(12) NULL,
HL03					VARCHAR2(2) NULL,
LIN01					VARCHAR2(20) NULL,
LIN02					VARCHAR2(2) NULL
						CONSTRAINT EDI_O_ADVSHIPNLINE_VALID1 
							CHECK(LIN02 IN ('VC','VN')),
LIN03					VARCHAR2(48) NULL,
LIN04					VARCHAR2(2) NULL,
LIN05					VARCHAR2(48) NULL,
SN101					VARCHAR2(20) NULL,
SN102					VARCHAR2(10) NULL,
SN103					VARCHAR2(2) NULL
						CONSTRAINT EDI_O_ADVSHIPNLINE_VALID2 
							CHECK(SN103 IN ('EA','CA')),
SN104					VARCHAR2(15) NULL,
SN105					VARCHAR2(15) NULL,
SN106					VARCHAR2(2) NULL
						CONSTRAINT EDI_O_ADVSHIPNLINE_VALID3 
							CHECK(SN106 IN ('EA','CA')),
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_ADVSHIPNOTICELINE.PRODUCTCODE
	IS 'Link to product code';

COMMENT ON COLUMN EDI_O_ADVSHIPNOTICELINE.SIZECODE
	IS 'Link to size code';

COMMENT ON COLUMN EDI_O_ADVSHIPNOTICELINE.SIZECODE
	IS 'Link to size code';

COMMENT ON COLUMN EDI_O_ADVSHIPNOTICELINE.FINISHINGMETHODCODE
	IS 'Link to finishing method code';

COMMENT ON COLUMN EDI_O_ADVSHIPNOTICELINE.HL01
	IS 'HL01 - Hierarchical ID Number: unique number assigned by the sender to identify a particular data segment in a hierarchical structure (e.g. maximum message line number for EDI_O_ADVSHIPNOTICE_PRF  + current message line number)';

COMMENT ON COLUMN EDI_O_ADVSHIPNOTICELINE.HL02
	IS 'HL02 - Hierarchical parent ID Number: identification number of the next higher hierarchical data segment that the data segment is subordinate to (e.g. 2 since this is our 3rd level)';

COMMENT ON COLUMN EDI_O_ADVSHIPNOTICELINE.HL03
	IS 'HL03 - Hierarchical level code: code defining the characteristic of a level in a hierarchical structure (e.g. "I" = Item)';

COMMENT ON COLUMN EDI_O_ADVSHIPNOTICELINE.LIN01
	IS 'LIN01 - Assigned identification: alphanumeric characters assigned for differentiation within a transaction set';

COMMENT ON COLUMN EDI_O_ADVSHIPNOTICELINE.LIN02
	IS 'LIN02 - 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_ADVSHIPNOTICELINE.LIN03
	IS 'LIN03 -  Product/service ID: Identifying number for a product or service';

COMMENT ON COLUMN EDI_O_ADVSHIPNOTICELINE.LIN04
	IS 'LIN04 - 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_ADVSHIPNOTICELINE.LIN05
	IS 'LIN05 - Product/Service ID: Identifying number for a product or service';
--section break for pack_Script
COMMENT ON COLUMN EDI_O_ADVSHIPNOTICELINE.SN101
	IS 'SN101 - Ship notice line item identification';

COMMENT ON COLUMN EDI_O_ADVSHIPNOTICELINE.SN102
	IS 'SN102 - Quantity shipped: numeric value of units shipped in manufacturer"s shipping units for a line item or transaction set';

COMMENT ON COLUMN EDI_O_ADVSHIPNOTICELINE.SN103
	IS 'SN103 - 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';

COMMENT ON COLUMN EDI_O_ADVSHIPNOTICELINE.SN104
	IS 'SN104 - Quantity shipped to date, including this shipment
Not used for Intertrade';

COMMENT ON COLUMN EDI_O_ADVSHIPNOTICELINE.SN105
	IS 'SN105 - Quantity ordered';

COMMENT ON COLUMN EDI_O_ADVSHIPNOTICELINE.SN106
	IS 'SN106 - 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';

COMMENT ON COLUMN EDI_O_ADVSHIPNOTICELINE.PID01
	IS 'PID01 - Item description type: code indicating the format of a description (e.g. "F")';

COMMENT ON COLUMN EDI_O_ADVSHIPNOTICELINE.PID02
	IS 'PID02 - Code identifying the general class of a product or process characteristic
Not used for Intertrade';

COMMENT ON COLUMN EDI_O_ADVSHIPNOTICELINE.PID03
	IS 'PID03 - Code identifying the agency assigning the code values
Not used for Intertrade';

COMMENT ON COLUMN EDI_O_ADVSHIPNOTICELINE.PID04
	IS 'PID04 - Industry specific product description code
Not used for Intertrade';

COMMENT ON COLUMN EDI_O_ADVSHIPNOTICELINE.PID05
	IS 'PID05 - A free-form description to clarify the related data elements and their content';

ALTER TABLE EDI_O_ADVSHIPNOTICELINE
  ADD CONSTRAINT XPKEDI_O_ADVSHIPNOTLINE PRIMARY KEY (TRANSFER_ID,MESSAGELINENUMBER);

CREATE INDEX XIF1EDI_O_ADVSNOTICELINE ON EDI_O_ADVSHIPNOTICELINE(TRANSFER_ID);

ALTER TABLE EDI_O_ADVSHIPNOTICELINE
  ADD CONSTRAINT  EDI_O_ADVSHIPNOTICELINE_TRANSF FOREIGN KEY (TRANSFER_ID) REFERENCES EDI_O_ADVSHIPNOTICE (TRANSFER_ID); 

----------------EDI_O_ADVSHIPNOTICE_LOG------------------

CREATE TABLE EDI_O_ADVSHIPNOTICE_LOG
(
TRANSFER_ID				NUMBER NOT NULL,
TRANSFER_STATUS     	NUMBER DEFAULT 0 NOT NULL
						CONSTRAINT EDI_O_ADVSHIPNLOG_VAL1
							CHECK(TRANSFER_STATUS IN (0,1)),
TEXT					VARCHAR2(1000) NULL,
PROCESS_STATUS			NUMBER DEFAULT 0 NOT NULL
						CONSTRAINT EDI_O_ADVSHIPNLOG_VAL2
							CHECK(PROCESS_STATUS IN (0,1)),
TIMESTAMP				DATE DEFAULT SYSDATE NOT NULL
);

COMMENT ON COLUMN EDI_O_ADVSHIPNOTICE_LOG.TRANSFER_STATUS
	IS '0 = ready to be read, 1 = read correctly';

COMMENT ON COLUMN EDI_O_ADVSHIPNOTICE_LOG.PROCESS_STATUS
	IS '0 = processed correctly, 1 = process stopped with errors';

ALTER TABLE EDI_O_ADVSHIPNOTICE_LOG
  ADD CONSTRAINT XPKEDI_O_ADVSHIPNOT_LOG PRIMARY KEY (TRANSFER_ID);

ALTER TABLE EDI_O_ADVSHIPNOTICE_LOG
  ADD CONSTRAINT  EDI_O_ADVSHIPNOTICE_LOG_TRANSF FOREIGN KEY (TRANSFER_ID) REFERENCES EDI_O_ADVSHIPNOTICE (TRANSFER_ID);