Code Conversion
This chapter covers the following topics:
- Code Conversion
- Defining Code Conversion Categories
- Assigning Categories
- Predefined Code Categories
- Defining Code Conversion Values
Code Conversion
The Oracle e-Commerce Gateway Code Conversion Module is a flexible and extremely powerful feature used to reconcile (convert) values from your trading partner's application or industry standard to values stored within Oracle E-Business Suite.
Code conversion is a common problem in any integration scenario where coded values such as Unit of Measure Code or Currency Code are represented differently in each of the applications being integrated. Many times, rules for performing these conversions can become quite complicated.
If you are integrating with trading partners using multiple applications and/or standards, a simple lookup table with “to” and “from” columns will not be sufficient. Advanced code conversion rules based on trading partner, industry standard, or transaction direction are required to support these complex application integration relationships. Code conversion rules in Oracle e-Commerce Gateway are defined as a set of ”to” and ”from” values plus additional attributes to determine specific code conversion processing behavior.
Key features of the Oracle e-Commerce Gateway Code Conversion Module include:
-
User defined code conversion categories (e.g. UOM, Currency Code).
-
User defined activation/assignment of code conversion for specific data elements.
-
Code conversion based on a user defined multi-level hierarchy.
-
Code conversion based on trading partner standard (e.g. ASC X12, EDIFACT).
-
Code conversion based on direction of transaction (inbound, outbound, or both).
-
One-to-Many, Many-to-One conversions.
To enable code conversion for specific data elements within a transactions, there are three easy setup steps:
-
Define Code Conversion Category
-
Define Code Conversion Values
-
Assign Code Conversion Category
Define Code Conversion Category allows you to enter all of the categories applicable to your business processing environment. A category is defined as a group of code conversion rules applicable to a single data element. Many predefined categories are included in Oracle e-Commerce Gateway. You may choose to implement these predefined categories or define your own. See: Defining Code Conversion Categories.
Define Code Conversion Values is where simple, or advanced code conversion rules along with their corresponding internal/external values are defined. Internal values are defined as code values stored in Oracle E-Business Suite. External values are code values in your trading partner's application or industry standard. In this form you can define advanced rules such as direction-specific, industry-standard-specific, one-to-many, and multi-level hierarchy code conversion rules. Every code conversion rule is associated with a code conversion category. See: Defining Code Conversion Values.
Assign Code Conversion Category associates a category (including all rules associated with that category) with a specific data element in a transaction. This assignment will enable code conversion for the transaction/data element. See: Assigning Categories.
If you use the code conversion by trading partner standard feature, an additional setup step to enter document standard in the Trading Partner Details form is required. See: Oracle e-Commerce Gateway Implementation Manual.
Code Conversion Based on Direction
Direction-specific code conversion is used in situations where a trading partner or group of trading partners have code conversion requirements that apply to only inbound or outbound code conversions. This feature is used in conjunction with the multiple key feature of Code Conversion to provide maximum flexibility and configuration of your unique code conversion requirements.
An example of how this feature can be applied is:
An Oracle E-Business Suite trading partner both receives purchase orders and sends invoices electronically. They have separate Order Entry and Accounts Receivable applications, each with its own unique set of UOM codes. Separate code conversion rules must be defined for the same trading partner based on transaction direction.
Direction-specific code conversion rules are defined in the Define Code Conversion Values form of Oracle e-Commerce Gateway. For each code conversion rule, you can also specify if the rule applies to inbound transactions, outbound transactions, or both.
In most situations, you will accept the default value of ”Both”. However, if you have complex code conversion requirements that require direction-specific code conversion rules, you will specify direction on this form.
Use of Keys in Code Conversion
In many situations, one set of code conversion rules will not apply to all of your trading partners. Code conversion rules may apply to specific trading partners, specific industry standards, or any number of key attributes defined in Oracle E-Business Suite.
These rules may be complex and involve a hierarchy of key attributes.
For example, trading partner and trading partner location may be defined as two key attributes. The logical hierarchy structure will look like this:
Code Conversion Category
Trading Partner (Key 1)
Trading Partner Location (Key 2)
During code conversion processing, if a code conversion rule is defined for the specific trading partner, trading partner location combination, that code conversion rule will be applied. Otherwise, processing will default to the trading partner level and apply any code conversion rules for that trading partner. If a rule still cannot be found, processing will default to the category level and apply any code conversion rules that apply to the entire category.
Keys and key hierarchies are user defined and apply to a specific code conversion category. Any data element that appears on the Interface File Definition form can be defined as a key attribute. In addition, Document Standard, defined in the Trading Partner Details form, can also be defined as a key attribute.
Multiple keys are used to construct a logical hierarchy used by code conversion processing to determine which code conversion rules to apply. Up to five levels of keys can be defined for a single code conversion category. See Processing Keys (1-5) for additional details on how multiple keys are processed.
If keys are used for code conversion, setup is required in each of the three code conversion forms.
-
In the Define Code Conversion Category form, indicate the number of keys to enable for the selected category.
-
In the Assign Code Conversion Category form, define the key(s) by indicating the data element associated with each key (e.g. Key 1 = Trading Partner for Category UOM).
-
In the Define Code Conversion Values form, enter specific values for each key combination requiring a unique set of code conversion rules (e.g. Key 1 = ABC Company).
The use of keys can also be combined with direction-specific code conversion rules to provide maximum flexibility and configuration of your unique code conversion requirements. See Code Conversion Based on Direction for additional information on direction-specific code conversion.
Code Conversion Processing
The Oracle e-Commerce Gateway Code Conversion Module is designed to process very basic or very complex code conversion rules.
Key setup data elements used in code conversion processing are:
-
Code Conversion Category
-
Direction
-
Key (1-5)
-
Internal Value
-
External Value (1-5)
For inbound code conversion, values for each of these data elements (except Internal Value) are used to derive Internal Value.
For outbound code conversion, values for each of these data elements (except External Value 1-5) are used to derive External Value (1-5).
Inbound Code Conversion Processing
During inbound transaction processing, inbound files are processed based on the interface file definition in Oracle e-Commerce Gateway. The interface file definition, as defined in the Interface File Definition form, will determine file layout and structure. It will also identify External Values (1-5) used for inbound code conversion processing.
Code conversion category data entered in the Assign Code Conversion Category form determine which fields require inbound code conversion processing. Key values are also derived from data in this form.
Inbound code conversion processing uses:
-
Code Conversion Category based on Assign Code Conversion Category definitions.
-
Direction based on transaction type.
-
Key (1-5) values from the interface file.
-
External Values (1-5) from the interface file.
All of these values are used to find a record in the Code Conversion Values table that matches the above values. If found, convert External Values (1-5) to Internal Value based on this record definition.
If no match is found, continue processing key values (if keys are used).
If no match is found, locate a record in the Code Conversion Values table with no key values defined and convert External Values (1-5) to Internal Values based on this record definition.
If no match is found, default External Value 1 into the Internal Value field.
Note: If an Internal Value is present in the inbound interface file, inbound code conversion processing will not overwrite the Inbound Value in the file.
Note: It is recommended to use unique identifiers, such as translator code, location code, or the combination of both, which are available at the time of code conversion as keys to identify the customer, supplier, or bank for code conversion.
Outbound Code Conversion Processing
During outbound transaction processing, code conversion category data entered in the Assign Code Conversion Category form determine which fields require outbound code conversion processing. Key values are also derived from data in this form.
Outbound code conversion processing uses:
-
Code Conversion Category based on Assign Code Conversion Category definitions.
-
Direction based on transaction type.
-
Key (1-5) values from the transaction record.
-
Internal Values from the transaction record.
All of these values are used to find a record in the Code Conversion Values table that matches the above values. If found, convert Internal Value to the External Value (1-5) based on this record definition.
If no match is found, continue processing key values (if keys are used).
If no match is found, locate a record in the Code Conversion Values table with no key values defined and convert Internal Value to External Value (1-5) based on this record definition.
If no match is found, default Internal Value into the External Value 1 field.
Processing Keys (1-5)
In Oracle e-Commerce Gateway, keys are used to construct a logical hierarchy of key attributes. Code conversion processing derives key values based on data entered in the Assign Code Conversion Category form.
Once key values are derived from the interface file (inbound) or transaction record (outbound), a composite key is constructed with values for all key levels that apply for the code conversion category.
Code conversion processing will use this composite key to locate records in the Code Conversion Values table with exactly the same composite key plus matching attributes for the other critical data elements in code conversion processing.
Failure to located a matching record will cause code conversion processing to construct a new composite key by using values for all key levels except the highest key level. This is effectively the same as stepping up one level in the hierarchy of key attributes.
For example, trading partner and trading partner location may be defined as two key attributes. The logical hierarchy structure will look like this:
Code Conversion Category = ”UOM”.
Trading Partner (Key 1) = ”Company ABC”.
Trading Partner Location (Key 2) = ”CA”
The initial composite key will contain values ”Company ABC” and ”CA”. Any records in the Code Conversion Values table that have matching Key 1 and Key 2 values, plus meet all other matching criteria, will be used to convert internal or external values.
If a matching record is not found, the new composite key will contain the value ”Company ABC” (”CA” has been removed from the composite key). Now, code conversion processing is looking for a match on Trading Partner (Key 1) only.
This cycle of stepping up the hierarchy of key attributes to construct new composite keys is repeated until the composite key contains no values. At this point, code conversion processing attempts to locate a match based on Code Conversion Category only.
Processing External Values (1-5)
External Values (1-5) are processed as one, composite external value. Code conversion processing does not view External Values (1-5) as a hierarchy or multiple composite values.
During outbound code conversion processing, any Code Conversion Values record that contains multiple External Values will convert all External Values and write them to the interface file (if the Code Conversion Values record matches all other criteria).
During inbound code conversion processing, all External Values plus other matching criteria must be contained in the Code Conversion Values record in order for that record to qualify for code conversion.
Note: For additional information, see:
Code Conversion, Oracle e-Commerce Gateway Implementation Manual
Defining Code Conversion Categories
Defining Code Conversion Categories
A category is a label assigned to a group of code conversion rules in the Code Conversion Values window. For example, the UOM category contains all unit of measure code conversion rules.
All desired categories, such as unit of measure or carrier code, must be defined. Any category can be used in several transactions, or several times within a single transaction.
You can use a code conversion category from the e-Commerce Gateway's predefined list or you can define your own code conversion category.
Once defined, code conversion categories must be assigned to table columns to activate the code conversion for the specific data element. You do this in the Assign Categories window.
To define additional code conversion categories:
-
Navigate to the Code Conversion Categories window.
-
Enter the category code and description.
-
To assign code conversion values to key attributes (such as a trading partner), enable one or more search keys.
For example, if you want to set the first search key to ”X12,” you must enable Search Key 1. The actual search criteria are entered in the Assign Categories window. The actual code conversion values and search key values are defined in the Code Conversion Values window.
Note: For additional information, see:
Code Conversion, Oracle e-Commerce Gateway Implementation Manual
Defining Code Conversion Values
Assigning Categories
For a given transaction, use the Assign Categories window to activate code conversion for a transaction data field. If search keys are enabled when you defined code conversion categories, use this window to identify the data elements associated with each key.
Prerequisites
-
Define code conversion categories. See: Defining Code Conversion Categories.
To assign code conversion categories:
-
Navigate to the Assign Categories window.
-
Select a transaction.
-
Select a map ID.
-
Select an output level that represents a level in the hierarchy of data for that transaction.
The column names for fields in the transaction identified by the e-Commerce Gateway for code conversion are displayed.
All the data elements identified as code conversion candidates for that level are displayed.
-
Assign a code conversion category to activate code conversion for the view or application open interface column. If no code category is assigned, code conversion is not performed. See: Predefined Code Categories.
-
Specify a column (Key 1 through Key 5) to define each key. This is used during code conversion processing to derive key values.
If you set a document standard when you defined the trading partner, you can select DOCUMENT_STANDARD in the list to assign to search key 1.
Note: For additional information, see:
Code Conversion, Oracle e-Commerce Gateway Implementation Manual
Defining Code Conversion Categories
Defining Code Conversion Values
Predefined Code Categories
The following table displays valid code categories pre-seeded by Oracle e-Commerce Gateway and their descriptions. You may use these codes or add new code categories for use when assigning categories. See: Assigning Categories.
| Category Code | Description |
|---|---|
| AC_HANDL | Allowance and Charge Handling |
| AC_SPECC | Allowance and Charge Special Charges |
| AC_SPECS | Allowance and Charge Special Services |
| ACTION_TYPE | Action Type |
| AETC_RESP | AETC Responsibility |
| AETC_RSN | AETC Reason |
| AGREEMENT | Agreement |
| BANK_ACCT_TYPE | Bank Account Type |
| BANK_BRCH_ID | Bank Branch ID |
| BANK_BRCH_TYPE | Bank Branch Type |
| BILL_TO_LOCATION | Bill-To Location |
| BUYER_ITEM | Buyer Item (Product) |
| BUYER_PRODUCT | Buyer Product |
| CARRIER | Freight Carrier |
| COMPANY_CODE | Company |
| CONTAINER_CODE | Container |
| CONTAINER_TYPE | Container Type |
| CONTAINER_TYPE_CODE | Container Type |
| COUNTRY | Country |
| CURR_CONV_TYPE | Currency Conversion Type |
| CURRENCY | Currency |
| CUSTOMER_CODE | Customer |
| DOCUMENT_TYPE | Document Type |
| EQUIPMENT | Equipment |
| FOB | FOB |
| FOB_PAYMENT | FOB Payment |
| FREIGHT_TERMS | Freight Terms |
| HAZ_CLASS | Hazardous Class |
| HAZ_MAT | Hazard Material |
| INVOICE_SITE | Invoice Site |
| INVOICE_TYPE | Invoice Type |
| ITEM_CATEGORY | Item Category |
| ITEM_TYPE | Item Type |
| LOC_BANK_BRCH_SITE | Location Bank Branch Site |
| LOC_VENDOR_SITE | Location Vendor Site |
| ORDER_TYPE | Order Type |
| PAY_FMT | Payment Format |
| PAY_METH | Payment Method |
| PAY_TERMS | Payment Terms |
| PAY_TYPE | Payment Type |
| PRICE_BASIS | Price Basis |
| PROVINCE | Province |
| REGION1 | Region 1 |
| REGION2 | Region 2 (for state in certain address tables) |
| REGION3 | Region 3 |
| SHIP_ORDER_STAT | Shipment Order Status |
| SHIP_PRIOR | Shipment Priority |
| SHIP_TO_LOCATION | Ship To Location |
| SHIP_TO_ORG | Ship To Organization |
| SHIP_TO_SITE | Ship To Site |
| SHIP_VIA | Ship Via (carrier) |
| SS_DESCRIPTOR | Supplier Scheduling Descriptor |
| STATE | State |
| SUPPLIER_NUMBER | Supplier Number |
| SUPPLIER_SITE | Supplier Site |
| TAX_CODE | Tax Code |
| TAX_EX | Tax Exempt Flag |
| TAX_EXRSN | Tax Exemption Reason |
| TAX_JURS | Tax Jurisdiction |
| TRANS_TERM | Transportation Terms |
| TRX_HANDL | Transaction Handling |
| TRX_PURP | Transaction Purpose |
| UOM | Unit of Measurement |
| TAX_NAME | Tax name. |
| VENDOR | Supplier Code |
| VENDOR_BANK_ACCT_TYPE | Supplier Bank Account Type |
| VENDOR_BANK_BRANCH_TYPE | Supplier bank branch type. |
| VENDOR_NAME | Supplier Name |
| VENDOR_NUMBER | Supplier Number |
| VENDOR_SITE | Supplier Site |
| WAREHOUSE | Warehouse |
| WAREHOUSE_SITE | Warehouse Site Code |
| X12_MAPPING | X12 Mapping |
Note: For additional information, see:
Defining Code Conversion Categories
Defining Code Conversion Values
Defining Code Conversion Values
Depending upon the EDI standard, user requirements, and the application data, a single Oracle E-Business Suite application internal data element may require one to five external data elements for the interface data file to satisfy the data requirements for the EDI standard or the trading partner.
Prerequisites
-
Define code conversion categories. See: Defining Code Conversion Categories.
-
Assign Code Conversion Categories. See: Assigning Categories.
To define code conversion values:
-
Navigate to the Code Conversion Values folder window
-
Enter the category code.
-
Select a direction from the List of Values.
This can be BOTH, IN, or OUT depending whether you want to convert different external values to the same internal values for inbound transactions. Or, if you want to convert different internal values to the same external value for outbound transactions.
The following validation is performed on your entries:
-
External values must be unique among IN and BOTH directions for the same search keys.
-
Internal values must be unique within OUT and BOTH directions for the same search keys.
-
The internal value and external values must be unique for all directions for the same search keys.
-
-
Enter search key values (1 - 5) for the set of internal / external values.
If you set a document standard when you defined the trading partner and want to perform code conversion based on document standard, enter that document standard in Search Key 1.
Use this only if search keys are enabled in the Code Conversion Categories window and columns are identified in the Assign Categories window.
-
Enter the internal and external codes (1 to 5) for one to many conversions.
The internal codes are the values defined in the base Oracle application. The external codes are the values defined in the trading partner application or the industry standard being used.
Note: For additional information, see:
Code Conversion, Oracle e-Commerce Gateway Implementation Manual