Extensible Architecture
This chapter contains extensible architecture information about Oracle e-Commerce Gateway implementation.
This chapter covers the following topics:
Customizing EDI Transactions
The Oracle e-Commerce Gateway provides two methods to add data elements to transactions that are not defined in the Oracle E-Business Suite data model. The two methods are:
-
Descriptive Flexfields
-
Extensible Architecture
Either method may be used separately or both methods may be used to customize a specific transaction to meet the business needs. Descriptive flexfields may be used for both inbound and outbound transactions, while the Extensible Architecture feature applies only to outbound transactions.
The transactions in the following table use the Extensible Architecture described below. Refer to Oracle Support's Web site for documentation for the other outbound transactions using another method of Extensible Architecture, which does not use transaction-specific ECE_ tables.
| Oracle Product | Transaction Code | ASCX 12 | EDIFACT | Description |
|---|---|---|---|---|
| Oracle Payables | PYO | 820 | PAYORD/ REMADV | Payment/Remittance Advice |
| Oracle Receivables | INO | 810 | INVOIC | Invoice |
| Oracle Purchasing | POO | 850 | ORDERS | Purchase Orders |
| Oracle Purchasing | POCO | 860 | ORDCHG | Purchase Order Changes |
| Oracle Supplier Scheduling | SPSO | 830 | DELFOR | Planning Schedule |
| Oracle Supplier Scheduling | SSSO | 862 | DELJIT | Shipping Schedule |
| Oracle Receivables and Purchasing | ADVO | 824 | APERAK | Application Advice |
Descriptive Flexfields
The Descriptive Flexfields feature of the Oracle E-Business Suite provides a flexible method for adding implementation-specific data elements to any of the applications without programming. These data elements are stored in the ATTRIBUTE* columns in the base applications tables. All of the ATTRIBUTE* columns associated with the relevant application base tables for a specific transaction are included in the Oracle e-Commerce Gateway interface tables for outbound transactions and in the Oracle E-Business Suite application open interface tables for inbound transactions.
Like all application base tables, the Oracle e-Commerce Gateway table ECE_TP_HEADERS and ECE_TP_DETAILS contains ATTRIBUTE* columns. The ATTRIBUTE* columns in ECE_TP_HEADERS and ECE_TP_DETAILS may be used to include additional Trading Partner-specific data in the interface file given the trading partner definition used with the transaction.
Use of the Descriptive Flexfields feature in Oracle E-Business Suite requires no additional setup for inclusion in Oracle e-Commerce Gateway transactions. Once the desired flexfields are set up and defined in Oracle E-Business Suite, any data stored in the system is automatically included in the relevant Oracle e-Commerce Gateway transaction.
Reference the Oracle E-Business Suite Flexfields Guide for details.
Extensible Architecture
The Extensible Architecture feature of the Oracle e-Commerce Gateway provides a powerful and flexible method to include additional data for outbound transactions. While most business needs for additional data can be accommodated by the use of the Descriptive Flexfields feature in Oracle E-Business Suite, the Extensible Architecture feature is useful when:
-
More data elements are required than are allowed by the use of Descriptive Flexfields
-
Data elements need to be extracted from custom Oracle E-Business Suite application tables
-
Data elements need to be extracted from outside the Oracle E-Business Suite
Each outbound transaction in the Oracle e-Commerce Gateway contains a series of interface tables to hold the data that is extracted and denormalized from the relevant application base tables before being written to the outbound interface file.
Every interface table in the Oracle e-Commerce Gateway also has an associated extension table, which may be customized to fit the business needs. Each record in an interface table is linked to the corresponding record in the associated extension table. Consequently, when the interface table record is written to the outbound transaction interface file, the corresponding extension table record is written as well.
The source data to be copied to the extension tables may come from a standard or custom Oracle database table, an external (non-Oracle) relational database or a file. Collectively call these tables and files external tables or external source data in this chapter. The external table must contain the unique key, which is a data element also found in the Oracle e-Commerce Gateway interface table. If the data is not already in the Oracle e-Commerce Gateway transaction interface table, the code needs customization to add that unique identifier to the interface tables in order to become a key to the records in the external source data.
The illustration below shows the ECE_PO_INTERFACE_LINES interface table and its corresponding extension table, ECE_PO_INTERFACE_LINES_X. The ECE_PO_INTERFACE_LINES table contains the element Item ID which is used as the unique key for a custom Oracle E-Business Suite application table called MATERIAL_ANALYST. The data from the MATERIAL_ANALYST table is used to populate the ECE_PO_INTERFACE_LINES_X table.
Relationship Diagram Between Oracle e-Commerce Gateway Tables and Oracle E-Business Suite Application Custom Table
The following process is performed to bring all the data together into the common interface tables before writing the records to the output file.
-
The Oracle e-Commerce Gateway extracts and denormalizes all the relevant Oracle E-Business Suite base application data (including Trading Partner data) into the transaction-specific interface tables.
-
Any enabled code conversions are performed after the data is extracted from the base application tables but before the data is written to the interface tables.
-
Using a unique identifier from the interface tables as search keys, data elements are selected from the appropriate external source and written to the extension tables.
-
All data elements from both the interface tables and their corresponding extension tables are formatted into 1024-byte (or less) records, sequenced and written to the transaction-common ECE_OUTPUT table.
-
The formatted records in the ECE_OUTPUT table are written in the proper sequence to the output file.
-
The transaction records are deleted from the interface tables, extension tables and the output table.
Using the outbound Purchase Order transaction as an example, the process flow described above is illustrated as follows:
Outbound Purchase Order Transaction Process Flow
Steps for Extensible Architecture
The Oracle e-Commerce Gateway uses interface tables, extension tables, packaged procedures and setup tables to create interface data files for outbound EDI transactions. The setup process to use the Extensible Architecture in the Oracle e-Commerce Gateway is a three-step process, as follows:
-
Define the columns in the extension table.
Task: Add the desired new column(s) to the appropriate extension table.
-
Define data positions in the outbound interface file.
Task: For each new column added to the extension table (step 1 above), insert a record into ECE_INTERFACE_COLUMNS to position the data element in the interface file.
-
Modify procedures to move the external source data to the extension tables.
Task: Modify the appropriate procedure in the extension package body to populate the new column(s) with data.
Check the Oracle Support web site for updates in these procedures.
Important: The steps for extensible architecture mentioned here are valid for the following transactions which are not based on Generic Outbound Engine:
-
Outbound Purchase Order (POO)
-
Outbound Purchase Order Change (POCO)
-
Outbound Invoice (INO)
-
Outbound Planning Schedule (SPSO)
-
Outbound Shipping Schedule (SSSO)
-
Outbound Application Advice (ADVO)
-
Outbound Movement Statistics (MVSTO)
The implementation details for the following transactions that are based on Generic Outbound Engine, refer to Oracle e-Commerce Gateway Implementation of Extension Columns, Release 12, My Oracle Support Knowledge Document 732007.1.
-
Outbound Credit Memo/Debit Memo (CDMO)
-
Outbound Shipment Notice/Manifest (DSNO)
-
Outbound Purchase Order Acknowledgement (POAO)
-
Outbound Purchase Order Change Acknowledgement (POCAO)
-
Outbound OPM's Shipment Notice/ (GASNO)
-
Outbound OPM's Purchase Order Acknowledgement (GPOAO)
-
Outbound Payment (PYO)
Note: In Release 12, the Outbound Payment transaction (PYO) uses the Generic Outbound Engine and the extensible code is in the package EC_PYO_EXT. The procedure names are pyo_populate_ext_lev01 and pyo_populate_ext_lev02.
Define Columns in the Extension Table
Task: Add columns to the appropriate extension table.
When the Oracle e-Commerce Gateway is installed, all extension tables are created. There is one extension table for each interface table and each extension table has only column: TRANSACTION_RECORD_ID. This column corresponds to the TRANSACTION_ RECORD_ID column in the associated interface table. It is used to maintain the link between the two tables. Simple SQL DDL statements like the ALTER TABLE command may be used to add columns to the desired extension table.
For example, suppose a business assigns a material analyst to each purchased item to determine data like item specifications, tolerances and quality standards. Further suppose that the business has created a custom database table to hold the data about the material analyst, calling it: MATERIAL_ANALYSTS. The business wishes to send contact data from this custom table with each Purchase Order line in the event that the vendor has any item-related questions; specifically, data from the following columns in the custom table:
-
MATERIAL_ANALYST_CODE
-
MATERIAL_ANALYST_LAST_NAME
-
MATERIAL_ANALYST_FIRST_NAME
-
MATERIAL_ANALYST_PHONE
-
MATERIAL_ANALYST_EMAIL
First, locate the proper extension table in the database. The Oracle e-Commerce Gateway interface table that holds Purchase Order line data is ECE_PO_INTERFACE_LINES, and the corresponding extension table is ECE_PO_INTERFACE_LINES_X. As installed, the interface and extension tables may be illustrated as shown below. The ECE_PO_INTERFACE_LINES_X table has only the Transaction Record ID column.
Oracle e-Commerce Gateway Interface and Extension Tables for Purchase Order Line Data
Determine Table Ownership:
Since database tables may only be altered either by their owner or by a user that has been granted this privilege, it is necessary to determine the owner (or schema) for the extension table. By default, all Oracle e-Commerce Gateway tables are owned by the user “EC” when the Oracle E-Business Suite are installed, while the “APPS” user is usually created as a privileged account. To verify the owner of the extension table, type the following from any SQL*Plus session:
SQL> select owner
from all_tables
where table_name = ‘ECE_PO_INTERFACE_LINES_X';
OWNER
______
EC
1 row selected
Add Columns to Extension Table:
To add the desired columns to the extension table, log into SQL*Plus either as the owner (in this case, the user “EC”) or as a privileged user (such as “APPS”) and type the following:
SQL> alter table EC.ECE_PO_INTERFACE_LINES_X
add ( Material_Analyst_Code VARCHAR2(30),
Material_Analyst_Last_Name VARCHAR2(40),
Material_Analyst_First_Name VARCHAR2(20),
Material_Analyst_Phone VARCHAR2(30),
Material_Analyst_Email VARCHAR2(30) );
Table altered
The new extension table structure can be verified by typing the following:
SQL> describe EC.ECE_PO_INTERFACE_LINES_X;
ECE_PO_INTERFACE_LINES_X
_________________________________________________________
TRANSACTION_RECORD_ID NOT NULL NUMBER
MATERIAL_ANALYST_CODE VARCHAR2(30)
MATERIAL_ANALYST_LAST_NAME VARCHAR2(40)
MATERIAL_ANALYST_FIRST_NAME VARCHAR2(20)
MATERIAL_ANALYST_PHONE VARCHAR2(30)
MATERIAL_ANALYST_EMAIL VARCHAR2(30)
The extension table has now been modified to fit the business requirements, and the modifications described above are shown below:
Oracle e-Commerce Gateway Interface Table and Modified Extension Table for Purchase Order Line Data
Define Data Positions in the Outbound Interface File
Task: Add records to the ECE_INTERFACE_COLUMNS table
The Oracle e-Commerce Gateway employs a series of tables that function as a data dictionary. When the Oracle e-Commerce Gateway is installed, these tables are seeded with all the data necessary to support the standard transactions. For outbound transactions, the table ECE_INTERFACE_COLUMNS contains the data that informs the extract process which data elements to write to the interface file, as well as where in the output file to position them. Only the data elements that appear in this table will be written to the interface file for a given outbound transaction. Consequently, records for the user-defined data in the extension tables must be added to ECE_INTERFACE_COLUMNS.
Continuing the earlier example, for each column that was added to ECE_PO_INTERFACE_LINES_X, a record must be inserted into ECE_INTERFACE_COLUMNS for the new data to appear in the output file.
Log into a SQL*Plus session either as the owner of ECE_INTERFACE_COLUMNS (typically “EC”) or as a privileged user (such as “APPS”), and type the following command to insert the first column of the new extension table data. Do this for each column that was previously defined.
INSERT Statement follows:
INSERT INTO ece_interface_columns(
interface_column_id,
interface_table_id,
interface_column_name,
table_name,
column_name,
record_number,
position,
width,
data_type,
conversion_sequence,
record_layout_code,
record_layout_qualifier,
conversion_group_id,
xref_category_allowed,
element_tag_name,
external_level,
map_id,
staging_column,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login)
SELECT
ece_interface_column_id_s.NEXTVAL,
(SELECT eit.interface_table_id
FROM ece_interface_tables eit,
ece_mappings em
WHERE em.map_code = 'EC_POO_FF' AND
eit.output_level = '5' AND
em.map_id = eit.map_id),
NULL,
NULL,
'MATERIAL_ANALYST_CODE',
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
'N',
NULL,
5,
(SELECT map_id FROM ece_mappings WHERE map_code = 'EC_POO_FF')
'FIELD500',
SYSDATE,
1,
SYSDATE,
1,
1
FROM DUAL;
One INSERT statement is required for each column that was added to the extension table.
Note: For the extensible architecture columns,
-
The column_name should be populated (and not base_column_name). Table_name is not mandatory and it can be null.
-
Interface_column_name, interface_table_name, base_table_name, and base_column_name should be null in ece_interface_columns for extensible columns.
-
Record number, position, width, layout code, and qualifier should be populated if the column has to be printed on the flat file.
-
The insert statement should be run after seed data reconciliation is run for the transaction.
FIELDnnn Numbers:
In the INSERT statement above you define your specific field to a generic FIELDnnn column in the Oracle e-Commerce Gateway. Five hundred generic FIELD names (FIELD1 through FIELD500) are predefined for any table within a transaction.
It is recommended that you start using FIELD500 then decrease the field number by one for each of your fields. The highest number fields are not likely to be used by the provided transaction. There is a potential problem if you need more columns than are available for the given table. You will lose data if you reuse FIELDnnn columns already used by the Oracle e-Commerce Gateway provided transaction. The method for assigning field numbers to your fields is illustrated in the following table:
| Transaction: Purchase Order Outbound Interface Table Name | Starting FIELD number | Ending FIELD number |
|---|---|---|
| Oracle e-Commerce Gateway Transaction | 1 | n |
| Available fields for transaction changes | N+1 | 500 |
| Your new fields | Use numbers moving backwards from 500. | 500 |
Record Numbers:
The value for Record_Number may be any value within the range of the corresponding interface table values. For example, the Purchase Order Outbound (POO) transaction has the typical Header-Line-Line Detail output structure, with the ranges shown in the following table:
| Transaction: Purchase Order Outbound Interface Table Name |
Record Number Range |
|---|---|
| ECE_PO_INTERFACE_HEADERS | 1000 - 1999 |
| ECE_PO_INTERFACE_LINES | 2000 - 2999 |
| ECE_PO_INTERFACE_SHIPMENTS | 3000 - 3999 |
Although extension table data elements may be interwoven with data elements from the corresponding interface table (provided the total number of bytes for any record does not exceed 1024), it is mandatory to begin the extension table data with the “x900” record. The value for Position determines the relative output order of data elements within the specified Record_Number.
Important: Extension table data elements may only be mapped within the range of the corresponding interface table, and no single record number may contain more than 1024 bytes of data.
Modify Procedures to Move Data to the Extension Tables
Task: Modify the package body code
Just as the Extensible Architecture of the Oracle e-Commerce Gateway provides extension tables that may be customized to fit the business needs, it also provides packaged procedures that may be customized to populate the extension tables with data. There is one extension package for each outbound transaction, and each extension package contains one procedure for every extension table in that transaction. Extension packages consist of two source code files, a package specification, and a package body, with file names in the respective window:
-
ECExxxxXS.pls
-
ECExxxxXB.pls
where xxxx denotes the unique three or four letter identifier for a given transaction. These source code files are usually found in the directory:
-
$APPL_TOP/ec/admin/sql
and may be modified with any text editor. Only the package body requires modification; the package specification file should not be altered.
For example, the Purchase Order Outbound (POO) transaction has the corresponding extension package body “ECEPOOXB.pls” containing the following procedures:
-
Populate_Ext_Header
-
Populate_Ext_Line
-
Populate_Ext_Shipment
To populate the new columns added to ECE_PO_INTERFACE_LINES_X in the earlier example, the procedure “Populate_Ext_Line” requires modification. As installed, the entire procedure reads as follows:
Procedure follows:
PROCEDURE Populate_Ext_Line
(l_fkey IN NUMBER,
l_plsql_table IN ece_flatfile_pvt.interface_tbl_type)
IS
BEGIN
NULL;
END PROCEDURE Populate_Ext_Line;
which does absolutely nothing, although the procedure is called each time a record is inserted into ECE_PO_INTERFACE_LINES. The procedure takes two parameters: a 'key' value, and a PL/SQL table. The 'key' value is the TRANSACTION_RECORD_ID value, which is the primary key for both the interface table and the extension table.
'interface_tbl_type' is a PL/SQL table typedef with the following structure:
base_table_name VARCHAR2(50) -- Application table name base_column_name VARCHAR2(50) -- Application column name interface_table_name VARCHAR2(50) -- Interface table name interface_column_name VARCHAR2(50) -- Interface column name record_num NUMBER -- Interface File Record Num position NUMBER -- Interface File Position data_length NUMBER -- Data length data_type VARCHAR2(50) -- Data type value VARCHAR2(400) -- Interface table value layout_code VARCHAR2(2) -- Layout Code layout_qualifier VARCHAR2(3) -- Layout Qualifier interface_column_id NUMBER -- Interface Column Id conversion_sequence NUMBER -- Conversion Sequence xref_category_id NUMBER -- Cross-reference category conversion_group_id NUMBER -- Conversion Group Id xref_key1_source_column VARCHAR2(50) -- Cross-reference source 1 xref_key2_source_column VARCHAR2(50) -- Cross-reference source 2 xref_key3_source_column VARCHAR2(50) -- Cross-reference source 3 xref_key4_source_column VARCHAR2(50) -- Cross-reference source 4 xref_key5_source_column VARCHAR2(50) -- Cross-reference source 5 ext_val1 VARCHAR2(80) -- Cross-reference value 1 ext_val2 VARCHAR2(80) -- Cross-reference value 2 ext_val3 VARCHAR2(80) -- Cross-reference value 3 ext_val4 VARCHAR2(80) -- Cross-reference value 4 ext_val5 VARCHAR2(80) -- Cross-reference value 5
Each record in the PL/SQL table represents a single column in the interface table, and there is one record in the PL/SQL table for every column in the interface table. (In other words, the PL/SQL table can be viewed as an array of records, with each record having the above structure.) The 'int_val' attribute holds the value stored in the interface table column (converted to VARCHAR2).
Important: It is important to note that the PL/SQL table is built using the COLUMN_NAMEs from the associated outbound transaction view, and not from the associated outbound interface table. Therefore, when using the ece_flatfile_pvt package procedures and functions, make sure that the COLUMN_NAME value that is passed matches the column name in the associated view.
To complete the earlier example, suppose that the custom table MATERIAL_ANALYSTS contains the column INVENTORY_ITEM_ID to associate the material analyst contact data with a specific purchasable item. The following modifications to the “Populate_Ext_Lines” procedure will extract the necessary data from the custom database table and insert it into the extension table.
The Procedure (POO and POCO Transactions Only):
PROCEDURE Populate_Ext_Line
(l_fkey IN NUMBER,
l_plsql_table IN ece_flatfile_PVT.interface_tbl_type)
IS
/*
**
** Variable definitions.
**
*/
v_ItemID INTEGER;
v_ItemIDPosition INTEGER;
v_mat_code VARCHAR2(500);
v_mat_last_name VARCHAR2(500);
v_mat_first_name VARCHAR2(500);
v_mat_phone VARCHAR2(500);
v_mat_email VARCHAR2(500);
temp_position INTEGER;
old_value VARCHAR2(32767);
BEGIN
/*
** Find the position of the ITEM_ID in the PL/SQL table. Then
** use the value stored in that position to select the necessary
** contact data from the MATERIAL_ANALYSTS custom table.
**
*/
ece_flatfile_PVT.find_pos ( l_plsql_table,
‘ITEM_ID',
v_ItemIDPosition );
/*
** Every value in the PL/SQL table is stored as VARCHAR2,
** so convert the value to a number.
**
*/
v_ItemID := TO_NUMBER(l_plsql_table(v_ItemIDPosition));
/*
** Get the necessary data from MATERIAL_ANALYSTS and
** store the values in temporary variables.
*/
SELECT
Material_Analyst_Code,
Material_Analyst_Last_Name,
Material_Analyst_First_Name,
Material_Analyst_Phone,
Material_Analyst_Email
into
v_mat_code,
v_mat_last_name,
v_mat_first_name,
v_mat_phone,
v_mat_email
FROM material_analysts
WHERE inventory_item_id = v_ItemID;
/*
** Get the position of extensible column from pl/sql table.
** The position is stored in temp_position.
*/
ece_extract_utils_pub.ext_get_value
(l_plsql_table,
'Material_Analyst_Code',
temp_position,
old_value);
/*
** Store the external value in pl/sql table in the position
** derived above.
*/
ece_extract_utils_pub.ext_insert_value
(l_plsql_table,
temp_position,
v_mat_code);
/*
** Repeat the last two steps for all the extensible columns.
*/
END PROCEDURE Populate_Ext_Line;
Important: This procedure is particularly used for POO and POCO transactions. The differences between POO/POCO and other types of transactions are highlighted as follows:
-
Get the position in the pl/sql table:
ece_extract_utils_pub.ext_get_value (l_plsql_table, 'extension_column_name', p_position, o_value);-
l_plsql_table is the name of pl/sql table (this comes as a parameter to extension procedure such as ece_poo_x.populate_ext_line).
-
extension_column_name is the extension column name.
-
p_position stores the position of the column in the pl/sql table (number datatype).
-
o_value is the value in the column (varchar2 datatype). It is not used, but to pass a dummy variable.
-
-
Use the p_position from previous procedure to insert value into the pl/sql table:
ece_extract_utils_pub.ext_insert_value (l_plsql_table, p_position, p_value);-
l_plsql_table is the name of the pl/sql table.
-
p_position is the position found from the ext_get_value.
-
p_value is the value to be inserted (varchar2 datatype). Please convert p_value to varchar2 datatype before inserting the value.
-
The Procedure (For Transactions Other Than POO and POCO):
PROCEDURE Populate_Extension_Line
(l_fkey IN NUMBER,
l_plsql_table IN ece_flatfile_PVT.interface_tbl_type)
IS
/*
**
** Variable definitions.
**
*/
v_ItemID INTEGER;
v_ItemIDPosition INTEGER;
BEGIN
/*
** Find the position of the ITEM_ID in the PL/SQL table. Then
** use the value stored in that position to select the necessary
** contact data from the MATERIAL_ANALYSTS custom table.
**
*/
ece_flatfile_PVT.find_pos ( l_plsql_table,
‘ITEM_ID',
v_ItemIDPosition );
/*
** Every value in the PL/SQL table is stored as VARCHAR2,
** so convert the value to a number.
**
*/
v_ItemID := TO_NUMBER(l_plsql_table(v_ItemIDPosition).value);
/*
** Get the necessary data from MATERIAL_ANALYSTS and insert it
** into, for example, ECE_AR_TRX_LINES_X.
*/
INSERT INTO ece_ar_trx_lines_x( Transaction_Record_ID,
Material_Analyst_Code,
Material_Analyst_Last_Name,
Material_Analyst_First_Name,
Material_Analyst_Phone,
Material_Analyst_Email )
SELECT l_fkey,
material_analyst_code,
material_analyst_last_name,
material_analyst_first_name,
material_analyst_phone,
material_analyst_email
FROM material_analysts
WHERE inventory_item_id = v_ItemID;
END PROCEDURE Populate_Extension_Line;
Once the procedure has been modified, it must be recompiled for the changes to take effect. Log into SQL*Plus as the “APPS” user, and issue the following command:
SQL> @ECEPOOXB.pls Package body created.
The customization process is complete; each subsequent execution of the outbund Purchase Order transaction will include the new extension table data elements in the output transaction interface file.