The Oracle XML Gateway code conversion function provides a method to cross-reference the codes defined in Oracle E-Business Suite to codes used by trading partners, the XML standard, or other standard codes in the transactions.
For example, assume that the ABC Corporation transmits a purchase order to the XYZ Corporation. The XYZ Corporation processes the incoming data using its own Oracle values (for example, unit of measure, currency, or freight carriers), but XYZ is required to return ABC's codes. In this way, the trading partner that created the original transaction receives response transactions that use their own values.
The Code Conversion features include the following:
.Define XML standard code conversion values defined by the XML standard. These codes are used by all trading partners.
Define universally used code conversion values that are defined by other standard organizations such as ISO, X12, and EDIFACT. These codes are used by all trading partners.
Define trading partner-specific code conversion values. The trading partner-specific code may override an XML standard or the other universally used code conversion values.
In the Define XML Standards form, Standard Codes such as OAG are entered to represent a message standard. There is a special purpose Standard Code called "UNIVERSAL" that is described below. See: Define XML Standards Form.
Entries from the Define XML Standards form appear in a list of values in the Define Transactions form that defines transactions to the XML Gateway. In this form, one Standard Code is associated with each transaction listed under External Processes. See: Define Transactions Form.
The actual code conversion values are entered in the Standard Code Conversion form and the Trading Partner Code Conversion form. The code conversion form provides a one-to-one code conversion from the Oracle values to an external value. The external code may be the XML standard codes such as OAG's code, the universal code such as ISO codes, or trading partner-specific codes. These are discussed below.
The trading partner code conversion values are accessed through the Trading Partner Setup form. Refer to Trading Partner Setup for details. Press the Code Conversion button to access the Trading Partner Code Conversion form from the Trading Partner Setup form.
The values associated with the trading partner's code values are the first set of codes to be examined during code conversion.
Before making entries in the trading partner specific table, first update the Standard Code Conversion values if necessary. This is important because the entries in the Standard Code Conversion tables are displayed and potentially overridden in the Trading Partner Code Conversion form.
The following table illustrates the linking of a Trading Partner's message maps to Code Conversion:
| Trading Partner | Message Map | Code Conversion Values are created for the Trading Partner? | The Message Map has a Unit of Measurement (UOM) field? | Accessed Trading Partner Code Conversion Values when the Message is Processed |
|---|---|---|---|---|
| Acme Corp, Atlanta | PROCESS_PO | Yes, for Acme, Atlanta | Yes, So Category UOM is entered for Code Conversion on the field | For UOM Category Code, the Acme, Atlanta code conversion values are accessed for the message map PROCESS_PO |
| Acme Corp, Chicago | PROCESS_PO | Yes, for Acme, Chicago | Yes, So Category UOM is entered for Code Conversion on the field | For UOM Category Code, the Acme, Chicago code conversion values are accessed for the message map PROCESS_PO |
| Acme Corp, Atlanta | ADD_INVOICE | Yes, for Acme, Atlanta | Yes, So Category UOM is entered for Code Conversion on the field | For UOM Category Code, the Acme, Atlanta code conversion values are accessed for the message map ADD_INVOICE |
| Acme Corp, Chicago | ADD_INVOICE | Yes, for Acme, Chicago | Yes, So Category UOM is entered for Code Conversion on the field | For UOM Category Code, the Acme, Chicago code conversion values are accessed for the message map ADD_INVOICE |
Message Map is assigned to this trading partner site in the Trading Partner Setup form (under Trading Partner Details)
Access Code Conversion through the Trading Partner Setup form
Data field is assigned the Category Code when the message map is created in the Message Designer
Since duplicate Oracle Values cannot be entered into the code conversion tables, the trading partner must always use the same "To Trading Partner Value" for all the transactions that it has enabled. The following table illustrates this rule:
| Oracle Value | Description | From Trading Partner Value | To Trading Partner Value |
|---|---|---|---|
| Each | Each | EA | EA |
| Piece | Piece | PC | PC |
| Each | Each | PC | PC |
Oracle Value - Cannot key on multiple occurrences of the value "EACH"
From Trading Partner Value - Cannot key on multiple occurrences of the value "PC"
To Trading Partner Value - Cannot key on multiple occurrences of the value "PC"
Given the Code Conversion setup discussed above, there is a standard code associated with each message. The standard code is the source organization for the code value, such as OAG for the given XML message to be processed.
The values associated with that standard organization's code values are the second set of codes to be examined during code conversion.
All other standards except the one that defined the XML message are marked collectively as UNIVERSAL. The XML Gateway does not store the other code conversion values under each standard such as the ISO codes or the X12 codes. It does not know which order the user wishes to access those code sets. For example, should the ISO codes be accessed before the X12 Codes, or the reverse? Hence, they are stored under a generic name.
The values associated with the UNIVERSAL code values are the third set of codes to be examined during code conversion.
The table below displays samples of ISO Country Codes that can be used across all trading partners and entered only once in the Standard Code Conversion forms.
| Standard Code | Oracle Value | Description | From Trading Partner Value | To Trading Partner Value |
|---|---|---|---|---|
| UNIVERSAL | United States | United States | US | US |
| UNIVERSAL | United Kingdom | United Kingdom | UK | UK |
If there is a conflict between two codes that must be entered as UNIVERSAL, it may be resolved by entering the conflicting codes under the Trading Partner code conversion for each trading partner needing that code.
Only the conflicting or duplicate code values need to be entered under each trading partner. The entries that do not cause conflicts can be entered under UNIVERSAL at the same time.
A Category Code is a label for a set of entries in the code conversion table. For example, CARRIER is the category code for code conversion values associated with the carrier.
During transaction processing, only the code conversion table entries with an assigned category code are accessed for the given data element.
See Seeded Code Categories for the seeded list.
Outbound and inbound transactions use different keys in the code conversion tables to access the codes.
For outbound transactions, "Oracle Value" and the Standard Code are keys to access this table to determine the "To Trading Partner Value" to write in the transaction.
For inbound transactions, "From Trading Partner Value" and the Standard Code are keys to access the table to determine the "Oracle Value" to pass to Oracle E-Business Suite application tables.
The following table search order is performed for all transactions until a code conversion value table entry is found:
Access the Trading Partner code conversion table.
If the code is not found in this table, perform a second search.
Access the Standard code conversion table using the XML message's Standard Code, such as OAG. This Standard Code is associated with the transaction in the transaction table as defined via the Define Transactions form.
If the code is not found in this table, perform a third search.
Access the Standard code conversion table using the Standard Code UNIVERSAL. The universal entries may represent ISO codes or other standards.
For inbound transactions: If the code is not found in the tables after the three searches described above, then the "From Trading Partner Value" is copied to the target field in the message map.
For outbound transactions: If the code is not found in the tables after the three searches described above, then the "Oracle Value" is copied to the target field in the message map.
Attention: If a code conversion value is not found in the table, it is not an error. There may be cases where only select values for a data element need code conversion. To require all values to have a code conversion table entry may cause you to do an excessive number of code conversion entries that are not necessary.
The following table illustrates the order that the tables and Standard Code Values are searched:
| Search Order | Code Conversion Form | STANDARD CODE | Purpose |
|---|---|---|---|
| 1 | Trading Partner Code Conversion | CUSTOM | Define the trading partner-specific code conversion values. This includes any overrides to any standard's or universal code conversion values that are displayed from the Standard Code Conversion form. The trading partner code conversion values are used by all transactions for that trading partner. |
| 2 | Standard Code Conversion | For example: OAG ROSETTANET (ROS) | Define standard code conversion values for a given XML standard that are used across all trading partners. These values can be overridden for a specific trading partner in the Trading Partner Code Conversion form. The Standard Code field is the default XML standard associated with the XML message. |
| 3 | Standard Code Conversion | UNIVERSAL | Define standard code conversion values that are used across all trading partners and not associated with an XML Standard. These values can be overridden for a specific trading partner in the Trading Partner Code Conversion form. Those entries accommodate universally used code lists such as ISO, X12, EDIFACT, that can be used by all XML Messages. |
For outbound transactions, the "Oracle Value" and the Standard Code in the code conversion tables are keys to retrieve the "To Trading Partner Value" to place it in the transaction. First the trading partner codes are searched. If an entry is not found, then the Standard code conversion table is searched for the standard codes and the universal codes.
If the code is not found in the tables after the three searches described above, then the Oracle Value is copied to the target field in the message map. Otherwise, the "To Trading Partner Value" will be copied to the target field in the message map.
The following table illustrates the code conversion concepts for outbound transactions using the category code UOM. The Outbound Search Key is comprised of the values for Conversion Table, Standard Code, and Oracle Value. The To Trading Partner Value is the data retrieved.
| Conversion Table | Standard Code | Oracle Value | Description | From Trading Partner Value | To Trading Partner Value |
|---|---|---|---|---|---|
| Trading Partner or Standard | Appropriate Code for the first, second, third search | Each | Each | (ignore the value here - not relevant for Outbound Transactions) | EA (derive this code to place in the transaction) |
The following table illustrates a sample outbound transaction code conversion for the category code UOM. The Outbound Search Key is comprised of the values for Conversion Table, Standard Code, and Oracle Value. The To Trading Partner Value is the data retrieved.
| Conversion Table | Standard Code | Oracle Value | Description | From Trading Partner Value | To Trading Partner Value |
|---|---|---|---|---|---|
| Trading Partner | Box | Box | (ignore the value here) | BX | |
| Trading Partner | CUSTOM | Each | Each | (ignore the value here) | EA |
| Standard | OAG | Box | Box | (ignore the value here) | BX |
| Standard | OAG | Each | Each | (ignore the value here) | EA |
| Standard | UNIVERSAL | Box | Box | (ignore the value here) | BX |
For inbound transactions, the "From Trading Partner Value" and the Standard Code in the code conversion tables are keys to retrieve the "Oracle Value." First the trading partner codes are searched. If an entry is not found, the Standard code conversion table is searched for the standard codes and then the universal codes.
If the code is not found in the tables after the three searches described above, then the "From Trading Partner Value" is copied to the target field in the message map. Otherwise, the "Oracle Value" will be copied to the target field in the message map.
The following table illustrates the code conversion concepts for inbound transactions using the category code UOM. The Inbound Search Key-1 is comprised of the values for Conversion Table and Standard Code. The Oracle Value is the retrieved data. The From Trading Partner Value is the Inbound Search Key-2.
| Conversion Table (Inbound Search Key-1) | Standard Code (Inbound Search Key-1) | Oracle Value (Retrieve) | Description | From Trading Partner Value (Inbound Search Key-2) | To Trading Partner Value |
|---|---|---|---|---|---|
| Trading Partner or Standard | Appropriate Code for the first, second, third search | Each (Derive this code to place in the Oracle table) | Each | EA | (Ignore the value here - Not relevant for Inbound Transactions) |
The following table illustrates a sample inbound transaction code conversion for the category code UOM. The Inbound Search Key-1 is comprised of the values for Conversion Table and Standard Code. The Oracle Value is the data retrieved. The From Trading Partner Value is the Inbound Search Key-2.
| Conversion Table (Inbound Search Key-1) | Standard Code (Inbound Search Key-1) | Oracle Value (Retrieve) | Description | From Trading Partner Value (Inbound Search Key-2) | To Trading Partner Value |
|---|---|---|---|---|---|
| Trading Partner | Box | Box | BX | (Ignore the value here) | |
| Trading Partner | CUSTOM | Each | Each | EA | (Ignore the value here) |
| Standard | OAG | Box | Box | BX | (Ignore the value here) |
| Standard | OAG | Each | Each | EA | (Ignore the value here) |
| Standard | UNIVERSAL | Box | Box | BX | (Ignore the value here) |
| Standard | UNIVERSAL | Each | Each | EA | (Ignore the value here) |
You can use the Action Assign Variable Value in Message Designer to assign multiple external codes given the single Oracle internal code by using conditions on the internal code. You must update the message in Message Designer as new codes are needed.
For example, the single Oracle internal carrier code stored in the SHIP_VIA column may need to cross-reference to two external codes: the carrier and the transportation mode. For example,
If SHIP_VIA = 'TRUCK-LAND'
move 'TRUCK' to the CARRIER.
If SHIP_VIA = 'TRUCK-LAND'
move 'L' to the TRANSPORTATION_METHOD.
If SHIP_VIA = 'TRUCK-AIR',
move 'TRUCK' to the CARRIER.
If SHIP_VIA = 'TRUCK-AIR'
move 'A' to the TRANSPORTATION_METHOD.
Note: For Outbound Transactions:
If you have the SHIP_VIA source data written twice to the file, then code conversion could be performed to convert the two fields separately: Once to derive the carrier code and once to derive the transportation method.