Previous  Next          Contents  Index  Navigation  Glossary  Library

Extensible Architecture Example

As an example, suppose you want to send a Ship Notice / Manifest (856) out via EDI. Most of the required information is stored in the application database. However, you may want to send other information, such as truck number, container number, the name of the driver, driver license number, driving record, and so on.

Application Base Data			Additional Data
	Supplier Name				Truck Number
	Client Name				Container Number
	Purchase Order Number		Name of Driver
	Product Number			License Number
	Quantity Shipped			Driving record status
	Date Shipped
	Expected Arrival Date
	Carrier

I. Modify the Transaction Specific Extension Table

You must first modify the extension table to store additional data for the transaction.

ALTER TABLE ECE_ASN_HEADERS_X
	ADD (	Truck_Num		VARCHAR2(30),
		Container_Num		VARCHAR2(30),
			Driver_Name		VARCHAR2(30),   ....... )

II. Insert data into the Extension table

Modify the ECASNOXB.pls file to insert records into the extension tables at execution time:

Procedure populate_extension_header(l_fkey	IN NUMBER,
    l_plsql_tbl	IN ece_flatfile_pvt.Interface_tbl_type)
is
--Declare new variables
	xPONumber	NUMBER;
	xTruckNum	NUMBER;
	xContainerNum	NUMBER;
	xDriverName	VARCHAR2(30);
begin
--Get PO number from the PL/SQL table
ece_flatfile_pvt.find_pos(l_plsql_tbl,'PO_NUMBER',xPONumber);
--Go to legacy system and retrieve added info
--and assign it to the variables based on xPONumber.
--xTruckNum		= legacy data where po_num = xPONumber
--xContainerNum	= legacy data where po_num = xPONumber
--xDriverName	= legacy data whree po_num = xPONumber
--Insert this data into the extension table
insert into ECE_OE_SHIPPING_HEADERS_X
  (transaction_record_id,
   truck_num,
   container_num,
   driver_num)
  values
   (l_fkey,
    xTruckNum,
    xContainerNum,
    xDriverName);
end populate_extension_header;

III. Insert data into ECE_INTERFACE_COLUMNS table

You need to insert new data column name into the ECE_INTERFACE_COLUMNS table so that they can be included in the output file, for example:

insert into ECE_INTERFACE_COLUMNS
 (interface_column_id,
  interface_table_id,
  interface_column_name,
  record_number,
  position,
  width,
  data_type,
  created_by,
  creation_date,
  last_update_login,
  last_update_date,
  last_update_by)
 select
  ece_interface_column_id_s.nextval,
  eit.interface_table_id,
  'TRUCK_NUM',
  1999,
  10,
  30,
  'NUMBER',
  1, 
  sysdate,
  1,
  sysdate,
  1
from ece_interface_tables eit
where eit.transaction_type = 'ASNO' and
  eit.interface_table_name = 'ECE_OE_SHIPPING_HEADERS';

Trans_method Type Qty Supplier_Name Client_Name PO_Num Trans_Record_ID
EDI ASNO 20 ABC NBC 14938 101
EDI ASNO 10 ABC UPS 23456 201
Table 1 - 93. ECE_ASN_HEADERS Table (Page 1 of 1)

Trans_Record_ID Truck_Num Container_Num Driver_Name
101 2A G768 SAM
Table 1 - 94. ECE_ASN_HEADERS_X Table (Page 1 of 1)

Type Interface_Table_Name Start_Number Extension_Table_Name
856O ECE_ASN_HEADER 10 ECE_ASN_HEADER_X
856O ECE_ASN_LINE 30 ECE_ASN_LINE_X
Table 1 - 95. ECE_INTERFACE_TABLES Table (Page 1 of 1)

Type Interface_Table_Name Table_Name Column_Name Position
856O ECE_ASN_HEADER ECE_ASN_HEADER Client_Name 1
856O ECE_ASN_HEADER ECE_ASN_HEADER_X Truck_Num 9
Table 1 - 96. ECE_INTERFACE_COLUMNS Table (Page 1 of 1)

Data Extraction

The extensible architecture allows the user to fetch data from sources other than Oracle Applications and store them in the extension table. Then you can produce, for example, the ASN with data from the transaction specific interface table and the extension table.

In the above example, based on the Position column in the INTERFACE_COLUMNS table, the Client_Name is the first data in the output file, Truck_Num is the ninth field in the output file.

The record identifier in this section of the output file starts with "10" based on the Start_Num column in the ECE_INTERFACE_TABLES table.

The SELECT statement:

Select ECE_ASN_HEADER.Client_Name, ECE_ASN_HEADER.PO_Num,
ECE_ASN_HEADER_X.Truck_Num, ECE_ASN_HEADER_X.Container_Num
from ECE_ASN_HEADER, ECE_ASN_HEADER_X
where ECE_ASN_HEADER.Transaction_Record_ID (+) =
ECE_ASN_HEADER_X.Transaction_Record_ID
and [other criteria]

Output

The output file produced by the EDI Gateway looks similar to the following file:

10NBC		14938	ABC	20		300 Beltway		Washington
*11DC		77781	2A		G768	SAM
*12...
...
*30...
10UPS			23456	ABC	10		214 Temple		Lincoln
*11NE		88832
*12...
...
*30...

where "NBC" is the Client Name, Position 1, and "2A" is the Truck Num in Position 9.

See Also

Overview of Extensible Architecture

Technical Overview

         Previous  Next          Contents  Index  Navigation  Glossary  Library