Overview of Code Conversion (Oracle EDI Gateway Help)


	Previous	Next		Contents	Index	Navigation	Glossary	Library

Overview of Code Conversion

The Oracle EDI Gateway code conversion function provides a method by which trading partner codes can be converted from and to codes used in Oracle Applications. 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 internal codes, but they are required to return the ABC Corporation's original codes. In this way, the trading partner that created the original transaction receives response transactions in their own internal codes.

The code conversion function enables you to:

Store trading partner's external data that is equivalent to internal application data.

Store and translate different categories of data, including unit of measure, carrier code, and location codes such as ship-to, bill-to, and remit-to locations.

Store and read key information from the EDI translator to validate inbound transactions. For outbound transactions, this information is used to pass meaningful information to the EDI translator.

Identify unique conversion codes at the customer, customer site, or up to five levels of a search key: for example, a customer with multiple ship-to locations, each of which has unique carrier codes, all of which must be converted to internal carrier codes.

Each data element in the data file contains data as defined in the sender's or in the receiver's application. The type of data that is typically converted includes location codes, item (product) codes, carrier codes, and unit of measurements.

For example, Purchasing defines buyer product codes and locations while Order Entry defines supplier product codes and customer locations. A code conversion or cross reference must be established before the correct product can be shipped to the correct location.

There are several types of codes that need converting and they may be converted in two places.

Item Code Conversion

Buyer product codes and supplier product codes are likely to be different. Some trading partners may send both the buyer and supplier product codes. Others may send only one. Often the product code must be converted to the internally defined product code even if both product codes are sent. An Oracle Application internal code sent from a trading partner may not be reliable.

Generally, it is appropriate that the application retain both the buyer and supplier product codes to avoid future code conversions. If this is not done, consider using an API within the application to convert the codes. Alternately, if the product fields are identified in the EDI Gateway as candidates for general code conversion as assigned in the Code Conversion Assignment window, EDI Gateway can convert the codes if a code category is assigned.

For inbound transactions, item (product) code conversions are performed by the application open interface, when possible.

For outbound transactions, item (product) code conversions are performed by APIs called by the EDI Gateway during the transaction extract when possible.

Alternately, item code conversion can be performed by the EDI Gateway general code conversion tables, if not addressed by the Oracle Applications.

Both the internal and external items should be in the data files.

Location Code Conversion

Trading partners have address location codes generated by their respective applications to represent the same physical address. For example, the trading partner location site code ABC, which is externally defined to your application, must be converted to code 123 before your application recognizes the location site.

You can perform most location code conversions by properly setting up trading partners in the EDI Gateway. See: Defining Trading Partner Information.

Location code conversion does not use the general code conversions except where noted in a specific transaction. Some types of locations may need a code conversion through the general code conversion set up if the particular location application address table does not accommodate the link to the EDI Gateway.

General Code Conversion

Like trading partner location codes, many general codes need converting. These codes usually are found in two categories:

Oracle application codes to EDI or ISO standard codes

Oracle application codes to trading partner-specific codes.

Oracle Application Codes to EDI Standard Codes or ISO Codes

Codes like unit of measurement, currency, and country codes have been defined by standards organizations such as ASC X12 and EDIFACT. However, these codes may not be the codes recognized by the Oracle application where many fields are user-defined. Hence, any codes of this class in the standard transaction must be converted to data that the Oracle application can recognize.

Oracle Application Codes to Trading Partner-specific Codes

Codes like carrier codes may be defined in the trading partner's application. Like standard defined codes, these codes must be converted to internal codes to be recognized by the Oracle application.

A response transaction may be required to return the codes exactly as they were received in the original transaction. Either the application retained both the external and internal codes or code conversion must be performed to reverse the original code conversion.

The EDI Gateway code conversion function performs the general code conversion.

During code conversion:

external data is converted to internal application data for inbound transactions

internal application data is converted to external data for the trading partner for outbound transactions.

General code conversions for inbound and outbound transactions can be accommodated with the EDI Gateway or the EDI translator. You decide which application is the best for your environment, user responsibilities, and application access.

Inbound transactions can cross-reference the data during the EDI Gateway processing. The inbound transaction files are not updated by the EDI Gateway with the internal codes, although they are defined on the data file record. The retrieved codes are loaded into the interface tables for the API without writing the codes to the data files.

The EDI Gateway only uses the data defined as internal data for the application interface tables. Either the EDI Gateway or the EDI translator must determine the internal code given the external data found in the transaction.

For inbound transactions, if the code conversion for a data element is enabled in the Assign Categories window, the code conversion takes place even if it was already done in the EDI translator if there is data in the external field. For outbound transactions, code conversion always occurs if the data element is enabled for code conversion.

Data that needs converting has at least two data elements defined on an outbound record on the Oracle data file. One data element contains the data as defined in the Oracle application (internal data), and the other data element has the data as requested by the trading partner or required by the standards (external data).

Each internal code may cross-reference one to five external fields.

The EDI translator should use the external fields in their general data maps, if populated. If no values exist in the external fields, then, use the internal fields.

Code Conversion Categories

A code conversion category is a code that represents a subset of entries in the general code conversion table. For example, table entries under category "UOM" cover unit of measurement code conversion; table entries under category "SHIP VIA" cover carrier / ship via code conversions. See: Defining Code Conversion Categories.

Oracle EDI Gateway provides a set of seeded code categories. However, you can define your own code category names. You could use a different category per application. For example, UOM_OE and UOM_PO could be used for unit of measurements in Order Entry and Purchasing, respectively.

A single code category can be used with many data elements within a transaction and across many transactions.

If the entries for code conversion apply to all trading partners, do not enable key search fields when assigning categories. These fields are used to indicate how many search keys to use to find table entries for a specific trading partner. For example, if Key 1 is used, one data field may be used in a search key to find entries with a specific value in one key. If Key 2 is also used, a second data field is concatenated to the first search key to narrow the table entry to a more specific business entity. Up to five keys may be used as a search key for the table entries. See: Assigning Categories.

The actual code conversion values for a specific trading partner are entered when you define code conversion values. From one to five search keys can be entered to narrow the table search to specific trading partners or any entity identified in the data file. See: Defining Code Conversion Values.

Note: If a category is used across many transactions and the code conversion table is trading partner or trading partner site-specific (i.e., does not apply globally), verify that each transaction has the same trading partner or trading partner site data in the transactions for entries in the code conversion value tables.

For example, assume the SHIP_VIA is used in the Order Entry inbound purchase order and outbound invoice transactions. Assume also that the purchase order transaction has supplier codes, but the invoice transaction has supplier numbers. In this case, the code conversion values entered require different table entries: one set for the supplier number as key; the other set for the supplier code as the key.

Search Keys

Code conversion for a given trading partner may have one to five keys to search a table for their specific codes. For example, carrier codes may be set up for a specific destination (ship to) location, a specific customer (over several ship to locations), or be a generic code across all customers. To limit a code conversion table entry to any entity, search keys must be established; otherwise, the internal and external codes in the table entry apply to all trading partners.

When a search of the code conversion table begins, all defined keys (up to five) are concatenated and treated as a single search key. If the search is unsuccessful, the last key is removed and the search is performed again with all remaining keys. The removal of one key continues until a table entry is found. If no entry is found, the default is used. If the default is not found, null is returned.

An accurate number of keys must be enabled for the data element to avoid superfluous access to the code conversion table. For example, if five keys are enabled, but only two keys are entered in the code conversion values, the table is accessed three times without benefit. Similarly, if the code conversion value table has data in search keys 1 and 2, but no keys are enabled, only a global, generic search is made and the keys are ignored.

The data elements used in the search key must be defined in the tables and columns assigned to the specific transaction in the Assign Categories window.

In addition, the number of search keys used must be enabled in the Code Conversion Categories window to use the keys specified in the Assign Categories Window.

Example

In this illustration, only the first two search keys were enabled in the Code Conversion Categories window:

Search Order Category Key 1 Key 2 Keys 3-5

First Search SHIP_VIA CUSTOMER CODE SHIP TO SITE CODE

Second Search SHIP_VIA CUSTOMER CODE

Third Search SHIP_VIA No specific key provided.
The third search will be a generic search with no specific key.

Fourth Search
(none)

Fifth Search
(none)

Table 1 - 5. (Page 1 of 1)

Search Order	Category	Key 1	Key 2
First Search	SHIP_VIA	CUSTOMER CODE	SHIP TO SITE CODE
Second Search	SHIP_VIA	CUSTOMER CODE
Third Search	SHIP_VIA	No specific key provided. The third search will be a generic search with no specific key.
Fourth Search (none)
Fifth Search (none)
Table 1 - 5. (Page 1 of 1)

Code Conversion Category Setup

In the Code Conversion Categories window, two Key Used check boxes were enabled for code conversion category SHIP_VIA. In this case, the search keys contain the customer code and the ship-to site code.

In the Assign Categories window, assume the following:

transaction = Purchase Order Inbound

output level = header

view name = SO_HEADERS_INTERFACE

reference view column = SHIP_VIA

Assume the category SHIP_VIA and the column name where the actual customer code is found is entered under Key 1 View Column. Use CUSTOMER_NUMBER.

The column name where the actual ship to site code is found is entered under Key 2 View Column. Use SHIP_TO_SITE_NUMBER.

Code Conversion Values Setup

The following table shows the data as set up in the Code Conversion Values window (the code conversion category is SHIP_VIA):

Internal Code Key 1 Key 2 Ext 1 Ext 2

UPS-BLUE 1004 1010 UPS A

FED_EXP 1004 1010 FEDEX L

FED-EXP FED L

Table 1 - 6. (Page 1 of 1)

Internal Code	Key 1	Key 2	Ext 1	Ext 2
UPS-BLUE	1004	1010	UPS	A
FED_EXP	1004	1010	FEDEX	L
FED-EXP			FED	L
Table 1 - 6. (Page 1 of 1)

In the Code Conversion Value window, the actual customer code for the specific trading partner is entered under Key 1. Use `1004' for their specific customer code.

The actual ship-to site code for the specific trading partner is entered under Key 2. Use `1010' for their specific ship to site number.

First, the two keys, customer code and the ship-to site number, are concatenated to 1004+1010. The search is performed using this concatenated key (1004 + 1010). If no value is found, a second search by the customer code alone (1004) is made. If no value is found, a default code conversion value (no key specified) is used. If no default is found, null is returned.

During the execution of the EDI Gateway of inbound transactions, the internal data found during code conversion is written to the application interface tables. For outbound transactions, the internal and external codes are written to the data file according to the data file definition defined in the Interface File Definition window. The EDI translator should use the external fields in their general data maps if populated; otherwise, use the internal fields.

Code Conversion Values

The actual codes to be converted are defined in the Code Conversion Values window.

Inbound Transactions

The transaction data file may have data in the External Code 1 through External Code 5 from the EDI translator data mapping. For example, the carrier code may be in external code 1 and transportation method (air, land, sea) may be moved to external code 2. These two codes together determine the internal code used by the Oracle Application.

If the EDI translator does the code conversion, the resulting code is moved to the internal code.

If the EDI Gateway does the code conversion, the derived internal code is passed to the application open interface tables and not written to the data file.

Outbound Transactions

The transaction data files have the Oracle Application internal code, and External Code 1 through External Code 5 (as many as indicated for this transaction field in the Interface File Definition window).

When the EDI translator does the data map, it maps data from the external fields if they are populated. Otherwise, they map the internal codes to their position in the standard.

The following is an example of the code conversion table:

Meaning Code Conversion Category Internal Code Ext 1 Ext 2

Freight Carrier code CARRIER UPS-BLUE UPS A

FOB Terms FOB ORIGIN OR

Payment Terms PAY_TERMS NET 10, 3O DAYS .10 30

Tax Exempt Flag TAX_EX N 0

Transaction Purpose Code TRX_PURP CHANGE 04

Unit of Measurement UOM EACH EA

Table 1 - 7. (Page 1 of 1)

Meaning	Code Conversion Category	Internal Code	Ext 1	Ext 2
Freight Carrier code	CARRIER	UPS-BLUE	UPS	A
FOB Terms	FOB	ORIGIN	OR
Payment Terms	PAY_TERMS	NET 10, 3O DAYS	.10	30
Tax Exempt Flag	TAX_EX	N	0
Transaction Purpose Code	TRX_PURP	CHANGE	04
Unit of Measurement	UOM	EACH	EA
Table 1 - 7. (Page 1 of 1)

Note the following:

The Oracle data files have one data field for the Oracle-defined internal codes and one to five data element fields for their external counterparts. An internal code is a code defined in the transaction host Oracle Application regardless of the direction of the transaction.

For inbound data, the data from the standard transactions that need code conversions are moved to the external data elements in the data files by the EDI translators. The EDI Gateway can determine the internal data elements. If the EDI translator determines the Oracle internal code, it is written to the data elements internal code field in addition to the external values.

For outbound data, the Oracle application data is stored in the internal field. If the internal data is cross-referenced, the results of the code conversion are stored in the external data elements. The EDI Translators should usually move the data in the external fields to the standard transactions. If no data is present in the external fields, the internal fields should be mapped to the standard.

Most code conversions match one internal code to one external code. In this case, only one external field is activated on the data file. You can choose to activate the external fields 2-5 if necessary for the transaction. Data field conversions that are known by the application to convert to more than one external code are indicated in the predefined data files.

Use of Two Standards in Code Conversion

There are three options to accommodate code conversion to two or more standards in the EDI Gateway:

one constant standard code in the data file in a single external record

multiple standard codes in the data file in a single external field

multiple standard codes in multiple external fields in the data file

One Constant Standard Code in the Data File in a Single External Field

Let the EDI Translator convert the code on the file to a second standard.

EDI Gateway EDI Translator

Internal Code External Code 1 External Code 1 Second Standard

PER HUNDRED PH PH PERH

Table 1 - 8. (Page 1 of 1)

EDI Gateway	EDI Translator
Internal Code	External Code 1	External Code 1	Second Standard
PER HUNDRED	PH	PH	PERH
Table 1 - 8. (Page 1 of 1)

There is the option that one standard (such as ASC X12) be maintained in the EDI Gateway and written to the data file. If some trading partners require an alternative standard (such as EDIFACT), the first standard on the data file can be converted to the second standard (such as ASC X12 to EDIFACT) by the EDI translator for specific trading partners.

This second standard code conversion is minimized in the EDI translator if the predominant standard is converted in the EDI Gateway and the second standard is converted in the EDI translator.

Multiple Standard Codes in the Data File in a Single External Field

Use a search key to a specific trading partner or group of trading partners.

EDI Gateway

Category Search Key 1 Internal Code External Code 1

UOM Alpha Company PER HUNDRED PH

UOM Beta Company PER HUNDRED PERH

Table 1 - 9. (Page 1 of 1)

EDI Gateway
Category	Search Key 1	Internal Code	External Code 1
UOM	Alpha Company	PER HUNDRED	PH
UOM	Beta Company	PER HUNDRED	PERH
Table 1 - 9. (Page 1 of 1)

The EDI Gateway code conversion has the option of specifying code values within a Code Category to a specific trading partner or other search criteria. This method may accommodate code conversion in some categories. However, this method requires numerous code conversion table entries if the search key is customer-specific.

Multiple Standard Codes in Multiple External Fields in the Data File.

EDI Gateway
Category	Search Key 1	Internal Code	External Code 1	External Code 2
UOM	(optional blank)	PER HUNDRED	PH	PERH
Table 1 - 10. (Page 1 of 1)

Most code conversions are likely to have one internal code to one external code. Consequently, most data elements have just one anticipated external field defined in the EDI Gateway-defined data files.

However, the EDI Gateway code conversion process can write two standards codes, one in External code 1, the other in External code 2, to the data file by doing the following:

In the Code Conversion Categories window:

enabling the corresponding Used Key 2 fields

indicating one standard in the external 1 field, and the second standard in the external 2 field for the same table entry.

In the Interface File Definition window:

Add an external 2 field to the data file by assigning it a record number, position, width, conversion sequence, record layout, record layout qualifier, and so on to ensure that the additional fields are written to all desired records in the data file.

Note: Any record within the transaction must be 600 characters or less. If the activated external value 2 forced the record beyond 600 characterss, you can move the external value 2 to another record within the data level of that transaction. Data levels indicate header, item, or other detail level tables of that transaction. Alternatively, other data elements can be moved to other records to make room for external value 2.

If you change interface records over time, but continue to archive transactions to the same historical file, you may not be able to recreate the standard records in the EDI translator. Different data file record layouts may have the same modified map name.

Unused external codes 2-5 may be found at the end of the interface tables in the Code Conversion Values window since they were not assigned a record number, position, width, conversion sequence, record layout code, record layout qualifier and other data file data by the EDI Gateway.

If the external 2 code was not at the end of the tables, this method cannot be used on that specific data field. Contact World Wide Support to report the required data element from the EDI Gateway interface table.

Note: Changes you make to all interface records require changes to the EDI Translator data map.

The EDI Translator data maps or templates need changes to recognize the additional standard code in the additional external fields, and use the correct standard code for the appropriate trading partner.