Code Conversion
This chapter contains the code conversion information about Oracle e-Commerce Gateway implementation.
This chapter covers the following topics:
- Code Conversion
- Concatenated Search Key for Inbound Transactions
- Concatenated Search Key for Outbound Transactions
Code Conversion
The Oracle e-Commerce Gateway code conversion function provides a method by which codes used by Trading Partners and electronic data standards can be converted from and to codes used in the Oracle E-Business Suite. 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 (i.e., unit of measure, currency, freight carriers, etc.), but XYZ is required to return ABC's original codes. In this way, the Trading Partner that created the original transaction receives response transactions containing their own internal codes.
Code conversion enables you to:
-
Convert Trading Partner and electronic data standards external data to their equivalent internal Oracle E-Business Suite application data and vice versa.
-
Identify up to five levels of keys to uniquely associate codes to a specific Trading Partner or other entity. For example, a Trading Partner has multiple ship-to locations which each have unique carrier codes. Each set of carrier codes can be entered into the code conversion value table to be used only for the specific Trading Partner site.
Definitions
Code
The value of a data element, field, or database table column. For purposes of code conversion, a code is typically a freight carrier, a unit of measure. For example, the value of a carrier code may be FED.
Code Conversion Category
A label for a set of codes. For example, SHIP_VIA, UOM, ORDER_TYPE, PAY_TERMS are code conversion categories.
Data Element
The smallest unit of a record. Each record contains many data elements, including but not limited to carrier code, and unit of measure. Data elements correspond to fields in the application windows or database tables.
External Code
A code in the transaction's transaction interface file, regardless if it is an inbound or outbound transaction, that represents data in the Trading Partner's perspective. Internal codes, in contrast, are found in Oracle E-Business Suite.
Internal Code
A code defined in the Oracle E-Business Suite, regardless if it is an inbound or outbound transaction. External codes, in contrast, are found in the transaction interface file.
Key n (1-5) Value
A value contained in a key column (also 1-5), that when concatenated with other keys, comprise a search key. Keys are concatenated beginning with 1 and continuing through all defined keys, up to a maximum of 5, during the code conversion table search.
Key n (1-5) Column
Table or view columns that contain values used as part of the search key.
Search Key
Accessing the code conversion table includes a concatenated search key consisting of the 1-5 user-defined search keys. If all five search keys have data then the table entries are very restricted to whom the codes apply. Data in Key 1 is less restricted on access and may apply to several Trading Partners. If all five search keys are blank, the table entries will be read for all Trading Partners' transactions for that code category.
The maximum number of search keys that you enabled in the Assign Code Conversion Categories window are concatenated for the first search. If a code conversion table entry is not found, the highest order key is removed then a subsequent search of the code conversion table is made. Removing the highest order search key of the remaining search keys and accessing the table with the modified key continues until a table entry is found or no table entry is found. This modification of the key continues until the last access is made with all five search keys set to blanks. Table entries with the 1-5 search keys set to blanks means that the table entry is applicable to all Trading Partners.
Code Conversion Process
While the setup steps for code conversion are identical for inbound and outbound transactions, the specific process of code conversion differs.
Inbound Transactions
An inbound transaction arrives in a transaction interface file that is then processed by the Oracle e-Commerce Gateway. The import program reads the transaction interface file, stores the data in memory, and performs code conversion.
For each data element activated for code conversion, a search key, made up of up to five concatenated values defined in the code conversion windows, is searched for in the transaction columns specified in the Assign Categories window.
Any number of values from 1 to 5 can be specified when you define code conversion values in the Code Conversion Values window. These values are concatenated to form the search key. Multiple searches are performed, first using all defined values. If no match is found, the last value is dropped from the search key and the search is performed again using the remaining concatenated values. This process is performed again until either a match is found or until all values are exhausted.
If a match is found using the external value(s), an internal value from the Code Conversion value table is passed to the application open interface table, if no transaction exceptions are found by the Oracle e-Commerce Gateway .
If a match is not found, a null value is returned and the external-1 field is passed to the application open interface table, if no transaction exceptions are found by the Oracle e-Commerce Gateway.
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 Transactions
An outbound transaction begins when data is extracted from Oracle E-Business Suite. The Oracle e-Commerce Gateway performs code conversion, where applicable.
For each data element activated for code conversion, a search key, made up of up to five concatenate values defined in the Code Conversion windows, is searched for in the transaction columns specified in the Assign Categories window.
Any number of values from 1 to 5 can be specified when you define code conversion values in the Define Code Conversion Values window. These values are concatenated to form the search key. Multiple searches are performed, first using all defined values. If no match is found, the last value is dropped from the search key and the search is performed again using the remaining concatenated values. This is performed until either a match is found or until all values are exhausted.
If a match is found using the external value(s), an internal value from the Code Conversion value table is written to the transaction interface file.
If a match is not found, a null value is returned and the internal value copied to the external-1 field then written to the transaction interface file.
Code Conversion Windows
There are three windows used in code conversion. The purpose of each is summarized in the following table.
| Code Conversion Windows | Purpose |
|---|---|
| Define Code Conversion Category | Define a code conversion category that identifies a subset of code conversion values Indicates how many search keys will be examined during actual code conversion. |
| Assign Code Conversion Categories | Enable code conversion for a data element in a given transaction. Indicate the columns that have the code values to use in the search key. |
| Define Code Conversion Value | Lists the actual code conversion values to cross-reference the internal and 1-5 external codes |
Code Conversion Categories Window
A code conversion category is a label for a set of entries in the code conversion table that contains the internal codes and external codes that you defined. During code conversion, only the code conversion table entries with the assigned category are accessed for the given data element.
The Code Conversion Categories window lists predefined categories or new categories that you created. The code categories are used to enable a data element for code conversion in the Assign Categories window.
You also indicate in this window how many search keys you will use in Code Conversion Values window for that category of data. A search key is a data element that limits the use of the code conversion table entry to a specific Trading Partner, Trading Partner site, or other entity that you define. For example, customer ACME their Chicago site has its own list of carrier codes. The search keys would have the first key value to represent Acme Corporation, and the second search key value to represent their Chicago site.
Assign Categories Window
The Assign Categories window lists which data elements in the transaction are candidates for code conversion. These are the only data elements in a transaction that you can enable for code conversion. A data element is enabled for code conversion by entering a code category next to the data element in this window.
You will also indicate the 1-5 source column names from the transaction that contains the actual data that you want reviewed as the 1-5 search keys, if you use keys.
In the previous window, Code Conversion Categories window, you also enable the corresponding 1-5 search keys by checking the appropriate boxes to correspond to the number of column entries you made in this Assign Categories window. This tells the program the maximum number of keys to use for that category.
In the Assign Categories window, the Key 1-5 column names for the search keys are in a list of values. These column names presented are all the column names in the current level (table) being reviewed plus all the levels (tables) above it. For example, if a data element at the item level is activated, column names from both the header level and item level are in the list of values. Once you selected the source column of the data, the actual values that you would find in those columns for the given transaction are used as search key entries when the code conversion value table is read.
The source columns may be a customer name, customer ID, location code, site name, or whatever you choose. You just scroll through the columns in the List of Values that have been defined for that transaction.
Enabling Code Conversion
The code conversion table will be accessed only if the data element in the transaction is activated for code conversion. This is done by assigning a code conversion category to the specific data element in a transaction using the Assign Categories window.
Source of Data for Search Keys
Data to indicate a specific Trading Partner or Trading Partner site to be used, as the search keys may be data found in several places in the transaction.
For example, they may be one of the following:
-
In the detail of the transaction,
-
Derived Trading Partner data from the Trading Partner site on transaction's Control Record (0010), or
-
Data found on the transaction's Control Record (0010).
Control Record Data as Search Keys
For inbound transactions, the Trading Partner reference 1 and reference 2 data does not have to match the Trading Partner reference 1 and reference 2 data defined in the Trading Partner window. You could move data from the electronic envelope or any data you choose into Trading Partner reference 1 or reference 2 so they can be used as search keys during code conversion if you desire. Use the Assign Categories window to assign the Trading Partner reference 1 or reference 2 column to be search keys like any other column assignment if you choose to use these fields.
Code Conversion Values Window
The Code Conversion Values window is where the actual internal codes and 1-5 external codes to be converted are entered plus any search keys that apply to the entries.
-
Internal codes are the codes defined or recognized in the base Oracle E-Business Suite application.
-
External codes are the codes defined or recognized by external sources such as your Trading Partners or transaction standards.
When Not to Use Keys 1-5:
If the internal and external code entries apply universally to all Trading Partners, the codes in the code conversion tables do not have keys 1-5 for the entry.
When to Use Keys 1-5:
Besides the internal and external codes, you can limit the applicability of a table entry to a specific Trading Partner or any other entity that you chose by entering values in the search keys. The search key will be data of your choice that identifies that Trading Partner or other entity.
If internal and external code entries apply to specific Trading Partners or a group of Trading Partners, one to five limiting search keys plus the internal and external codes must be entered into the code conversion table.
To use Keys 1-5, set ups are required in the Code Conversion Categories window and the Assign Categories window previously discussed. Users must indicate what the full search key will be during code conversion set up. For example, it may be the sales channel, customer code, and customer site. Users select what columns to examine from columns in the specific transaction tables in the Oracle e-Commerce Gateway. These columns contain the actual code values examined during code conversion.
Relationship of the Code Conversion Windows
The following illustrations show the relationship of the five keys across the three code conversion windows.
-
Code Conversion Categories window:
In the example, only two keys are turned on, so a maximum of two search keys will be read during code conversion.
-
Assign Categories window:
In the Assign Categories window the columns that contain the data in the transaction to be used as the search keys in the code conversion value table are identified. Because the example uses only two keys, only Key1 and Key2 are populated.
-
Code Conversion Values window:
In the Code Conversions Values window the actual data values are entered. In the example, the value_1 of 1000 is the customer code, and the value_2 of 2000 is the customer site number to be used as part of the full search key during code conversion.
Relationship of the Code Conversion Windows
The following illustration shows the relationship of the five keys across the three code conversion windows with actual data.
Relationship of the Five Keys Across the Three Code Conversion Windows
Reading the Code Conversion Values Table
Outbound and inbound transactions use different full search keys to access data in the code conversion table. The following must be considered when creating the full search keys for successful code conversion in the code conversion table.
To create any full search key in the code conversion value window, the data elements that comprise it come from the sources shown in the following table:
| Full Search Key Component | Source |
|---|---|
| Code Conversion Category | Defined in the Code Conversion Categories window. Then user assigned to a specific data element in a transaction in the Assign Categories window. |
| Direction | This is the transaction direction that is accessing the code conversion table entry. It is part of the full search key. It determines if the table entry will be read for the inbound or outbound transactions or transactions in both directions. The values are IN for inbound transactions; OUT for outbound transactions; and BOTH for both inbound and outbound transactions. |
| Keys 1-5 | User determined data that limits to what the code conversion value applies. Usually it is limited to a Trading Partner site. No keys indicated means that the table entry applies to all Trading Partners. |
| Internal code (inbound transactions) | This is the data defined or recognized by Oracle E-Business Suite. It is usually data found in the code conversion value table that will be written to the application open interface table. |
| Internal code (outbound transactions) | Data found on the base application document or derived by the Oracle e-Commerce Gateway. This data will be written to the transaction interface file. |
| External codes 1-5 (inbound transactions) | Data found on the transaction interface file or derived by the Oracle e-Commerce Gateway. This data is used to derive the Oracle internal code for the application open interface table. |
| External codes 1-5 (outbound transactions) | Data found in the code conversion table given the internal code then written to the transaction interface file. |
| External codes 1-5 (outbound transactions) | Data found in the code conversion table given the internal code then written to the transaction interface file. |
The code conversion value table has a data element called "Direction" meaning transaction direction. The transaction direction determines if the table entry is accessed during the code conversion process. Direction is in the table to allow additional flexibility to code conversion values and eliminate repeating entries for multiple Trading Partners as search keys.
The transaction Direction field in the code conversion tables allows table entries to be entered with duplicate internal codes or duplicate external codes depending on the value of the Direction code.
For inbound transactions, duplicate external codes can be entered in the table, which converts to the same internal code.
For outbound transactions, duplicate internal codes can be entered in the table as long as the 1-5 external codes are unique.
If the Direction is BOTH, the entire search key including the internal and external codes are unique.
The Define Code Conversion Values window does not allow you to create duplicate table entries even across entries using the other directions: IN, OUT, and BOTH. You may need to remove an entry for another direction in order to accommodate a table entry that uses direction BOTH.
Transaction direction is discussed further after we understand how the code conversion value table is read in general for inbound and outbound transactions as illustrated in the following two tables.
Full Search Key for Outbound Transaction:
Goal: Create a key including the known internal code from the Oracle E-Business Suite application to find all the external codes so they may be written to the transaction interface file.
Keys 1-5 are optional. Keys are used when the table entry does not apply to all Trading Partners.
The following table illustrates a full search key search performed for an outbound transaction. The columns Category, Direction, Keys 1 thru 5, and Internal Code represent the entire search key. The columns External Code 1 through External Code 5 represent data retrieved for the transaction interface file.
| Category | Direction | Keys 1 thru 5 | Internal Code | External Code 1 | External Code 2 | External Code 3 | External Code 4 | External Code 5 |
|---|---|---|---|---|---|---|---|---|
| UOM | OUT | Each | EA | PC | ||||
| UOM | OUT | Box | BX | BX |
Full Search Key for Inbound Transaction:
Goal: Create a key including the known 1-5 external codes on the transaction interface file to find the internal code that is needed for the base application open interface table.
Keys 1-5 are optional. Keys are used when the table entry does not apply to all Trading Partners.
The following table illustrates a full search key search performed for an inbound transaction. The columns Category, Direction, Keys 1 thru 5, and External Code 1 through External Code 5 represent the entire search key. The columns Internal Code represents the data retrieved to write to the application Open Interface Tables.
| Category | Direction | Keys 1 thru 5 | External Code 1 | External Code 2 | External Code 3 | External Code 4 | External Code 5 | Internal Code |
|---|---|---|---|---|---|---|---|---|
| UOM | IN | EA | Each | |||||
| UOM | IN | PC | Each |
Understanding Code Conversion for Outbound Transaction using Direction OUT
For outbound transactions, the entire search key to access the table entries consists of the following.
-
Category
-
Direction
-
Keys 1-5
-
Internal Code
This full search key is used to find a table entry to return the external codes 1-5 that can be copied to the transaction interface files.
During code conversion for outbound transactions, code conversion table entries marked with the direction OUT and BOTH are read. See section below on the direction BOTH.
Entering the direction OUT allows you to enter the entire search key for outbound transactions, yet allow duplicate data to be entered for the external codes 1-5.
If code conversion is enabled and a table entry is not found, then internal code is also copied to the external 1 data element to be written on the transaction interface file.
The following tables have illustrations using only 2 of the 5 allowable keys and 2 of the 5 external codes for simplicity. Use as many keys and external codes, as necessary for your business needs. Though the illustrations have separate samples by the direction (IN, OUT, BOTH), all the table entries reside in one table.
The columns Category, Direction, Key 1, Key 2, and Internal Code are supplied by the outbound transaction. These five pieces of data comprise the entire search key and the key must be unique. Columns External Code 1 and External Code 2 are data that are retrieved for the transaction interface file.
| note | Category | Direction | Key 1 | Key 2 | Internal Code | External Code 1 | External Code 2 |
|---|---|---|---|---|---|---|---|
| (1) | UOM | OUT | Each | EA | PC | ||
| (1) | UOM | OUT | Box | EA | PC | ||
| (2) | UOM | OUT | EDIFACT | Each | PC | ||
| (2) | UOM | OUT | X12 | Each | EA | ||
| (3) | SHIP_VIA | OUT | 1004 | 1110 | Truck-air | TRUCK | A |
| (3) | SHIP_VIA | OUT | 1004 | 1110 | Truck-motor | TRUCK | J |
| (3) | SHIP_VIA | OUT | 1004 | Truck-air/motor | TRUCK | J | |
| (3) | SHIP_VIA | OUT | 2010 | 1005 | Alpha-air | ALPHA | A |
| (3) | SHIP_VIA | OUT | 2010 | 1005 | Alpha-ground | ALPHA | J |
| (1)(4) | SHIP_VIA | OUT | Beta-Overnight | BETA | A | ||
| (1)(4) | SHIP_VIA | OUT | Beta-Ground | BETA | J |
-
Since keys 1-5 are blank, these table entries may be retrieved for all data elements assigned code category UOM for inbound transactions whose internal codes are listed above. You may use external 1 for the X12 codes and external 2 for the EDIFACT codes. External 3-5 may contain alternative codes for one of those standards or other standards. Given the Trading Partner, the translation data map will choose the desired internal and external data elements to use.
Since key 1 has the Document Standard Code, only Trading Partners given the specific Document Standard Code showing in key 1 will access those records. The default Document Standard Code is assigned to a Trading Partner's transaction via the Detail tab in the Define Trading Partner.
-
Since keys 1-2 are entered in the code conversion table, those table entries are found only when the transaction has the values found in key 1 and 2.
What data elements are represented by keys 1-5 are specified in the Assign Code Conversion Categories window, for example, they may be customer number and customer site number. Select your column values based on the data elements that are available to you in that transaction. In the illustration, the customer number and customer site numbers were available in the transaction so they could be used in the code conversion value tables.
-
Since keys 1-5 are blank and the (3) table entries did not apply, all code conversion enabled data elements will find the (4) table entries given the external codes as part of the key.
Not finding a value in the code conversion table 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 or desired.
Understanding Code Conversion for Inbound Transactions using Direction IN
For inbound transactions, the entire search key to access the table consists of the following.
-
Category
-
Direction
-
Keys 1-5
-
External Codes 1-5
This full search key is used to find a table entry for the internal code that can be copied to the base Oracle E-Business Suite application open interface table.
During code conversion for inbound transactions, code conversion table entries marked with the direction IN and BOTH are read. See section below on the direction BOTH.
Code conversion table entries, which are given the direction IN, are accessed during code conversion for inbound transactions only.
Entering the direction IN allows you to enter the entire search key for inbound transactions, yet allows duplicate data to be entered for the Internal codes.
-
Inbound Transaction: Entire Search Key (Must be unique)
-
Retrieved Data for the Application Open Interface Tables
Example Inbound Transaction: The Search Key Must Be Unique (Columns Category, Direction, Key 1, Key 2, and External Code 1 and External Code 2)
| Note | Category | Direction | Key 1 | Key 2 | External Code 1 | External Code 2 | Internal Code |
|---|---|---|---|---|---|---|---|
| (1) | UOM | IN | EA | Each | |||
| (1) | UOM | IN | PC | Each | |||
| (2) | UOM | IN | EDIFACT | PC | Each | ||
| (2) | UOM | IN | X12 | PC | Each | ||
| (2) | UOM | IN | X12 | EA | Each | ||
| (3) | SHIP_VIA | IN | 1004 | 1110 | TRUCK | A | Truck-air |
| (3) | SHIP_VIA | IN | 1004 | 1110 | TRUCK | J | Truck-motor |
| (3) | SHIP_VIA | IN | 1004 | TRUCK | J | Truck-air/motor | |
| (3) | SHIP_VIA | IN | 2010 | 1005 | ALPHA | A | Alpha-air |
| (3) | SHIP_VIA | IN | 2010 | 1005 | ALPHA | J | Alpha-ground |
| (1)(4) | SHIP_VIA | IN | BETA | A | Beta-Overnight | ||
| (1)(4) | SHIP_VIA | IN | BETA | J | Beta-Ground |
-
Since keys 1-5 are blank, these table entries may be retrieved for all data elements looking for code category UOM for inbound transactions whose external codes 1-2 are listed above. The external 1 may be a mixture of EDI standard codes expected from the transaction. For example, the EA may be the expected X12 code, while the PC may be the expected EDIFACT code.
-
Since key 1 has the document standard code, only Trading Partners given the specific document standard showing in key 1 will access those records. The default document standard, which is used for this code conversion value table, is assigned to a Trading Partner's transaction via the Detail tab in the Define Trading Partner window. Remember that the Document Standard Code applies to the entire transaction for the Trading Partner. It does not change per data element.
-
Since keys 1-2 are entered in the code conversion table, these entries apply only to the entities whose values are entered in keys 1-2.
-
Since keys 1-5 are blank and the (3) table entries did not apply, all code conversion enabled data elements will find the (4) table entries given the external codes as part of the key.
If code conversion is enabled and a table entry is not found, the external 1 code is written to the application open interface tables. They may fail data validation when the application open interface is executed if not entered properly in the code conversion tables. The data should be visible for the applications regular error handling procedures.
Understanding Code Conversion for Inbound and Outbound Transactions using Direction BOTH
Table entries that are given the direction BOTH are accessed during code conversion for both inbound and outbound transactions. All the rules specified above for inbound and outbound transactions apply to the entries with direction BOTH.
You should be able to enter the direction BOTH for most table entries, if there is a one-to-one correspondence between the Oracle internal code and its set of external 1-5 codes.
For an outbound transaction, the asterisked table entries in the table below cannot be entered into the tables. This is because the single internal code "Each" would access multiple table entries for the external codes, one with the external code EA and the other with the external code PC. To be a successful table search, only one table entry can be found. An error message is displayed when the entry is attempted.
Even creating table entries using the document standard in key 1 may still cause a conflict within a standard. For example, this may happen when EA and PC are values both within X12 and within EDIFACT. If separating the codes by standards still causes this problem, you can select document standard "Other" for the exceptions and assign "Other" to those Trading Partners to retrieve the proper code for their transactions.
In the table below, the columns Category, Direction, Key 1, and Key 2 are part of the search key for both inbound and outbound transactions. The columns External 1 and External 2 are part of the key for inbound transactions to find the Internal code. The column Internal is part of the key for outbound transactions to find the external codes 1 - 5.
| note | Category | Direction | Key 1 | Key 2 | External 1 (Inbound transaction key) | External 2 (Inbound transaction key) | Internal (Outbound transaction key) |
|---|---|---|---|---|---|---|---|
| (1) | UOM | BOTH | EA | Each | |||
| (2) | UOM | BOTH | PC | Each* | |||
| (1) | UOM | BOTH | EDIFACT | PC | Each | ||
| (1) | UOM | BOTH | X12 | EA | Each | ||
| (2) | UOM | BOTH | X12 | PC | Each* | ||
| (1) | SHIP_VIA | BOTH | 1004 | 1110 | TRUCK | A | Truck-air |
| (1) | SHIP_VIA | BOTH | 1004 | 1110 | TRUCK | J | Truck-motor |
| (1) | SHIP_VIA | BOTH | 1004 | TRUCK | J | Truck-air/motor | |
| (1) | SHIP_VIA | BOTH | 2010 | 1005 | ALPHA | A | Alpha-air |
| (1) | SHIP_VIA | BOTH | 2010 | 1005 | ALPHA | J | Alpha-ground |
| (1) | SHIP_VIA | BOTH | BETA | A | Beta-Overnight | ||
| (1) | SHIP_VIA | BOTH | BETA | J | Beta-Ground |
Note: * There would not be a unique search key for outbound transaction if the table entry were allowed. An alternate code conversion scheme must be chosen.
-
Simple table entry exists.
-
Items marked (2) will not be allowed as table entries since the full search key for outbound transactions will not be unique.
Document Standard as Part of the Search Key
Document standard is a code to represent the common EDI standards such as X12 and EDIFACT. Its purpose is to use the selected code as a search key in the code conversion value table.
The document standard may be set for a Trading Partner for a specific transaction in the Define Trading Partner Detail tab. Follow the usual code conversion set up through the three code conversion windows.
The following table shows examples of Document Standards used in the search key:
| note | Category | Direction | Key 1 | Key 2 | Internal Code | External Code 1 | External Code 2 |
|---|---|---|---|---|---|---|---|
| (1) | UOM | OUT | Each | EA | PC | ||
| (2) | UOM | BOTH | X12 | Each | EA | ||
| (2) | UOM | BOTH | X12 | Piece | PC | ||
| (2) | UOM | BOTH | EDIFACT | Each | EA | ||
| (2) | UOM | BOTH | EDIFACT | Piece | PC |
-
Assume that you entered X12 codes in external code 1 and EDIFACT in external code 2.
This method allows you to enter just one table entry to have the internal code set to ‘Each' then return the external codes ‘EA' and ‘PC' for outbound transactions. This entry cannot be used for inbound transactions, since only the X12 or only the EDIFACT code is in the transaction or message, but not both codes in a given transaction. You need a separate set up for the inbound transactions.
-
Enter separate table entries by document standard if it is an alternate method for (1). Since key 1 is the document standard, only the table entries with key 1 set to one of the standards (X12, EDIFACT, etc.) are retrieved for a given Trading Partner if they are assigned a document standard for that transaction. If a Trading Partner's transaction is not given a document standard, then the entry (1) will be read for the outbound transactions.
Planning the Use of Direction in Code Conversion
Review your code conversion needs and develop a plan for your code conversion value table entries.
The values that you enter in the table are case sensitive.
Not all scenarios of code conversion table entries can be documented. This information illustrates how the code conversion value table is accessed. Use it to develop your code conversion strategy.
The following tables provide some considerations to help you develop that strategy.
Planning for Direction OUT for Outbound Transactions
The following table shows an example in which all entries are not feasible for the direction OUT. Columns External Code 1 and External Code 2 are retrieved for the transaction interface file.
| entry | Category | Direction | Key 1 | Key 2 | Internal Code | External Code 1 | External Code 2 | Note |
|---|---|---|---|---|---|---|---|---|
| (1) | UOM | OUT | Each | EA | PC | |||
| (2) | UOM | OUT | Each | PC | EA | Entire Search Key is duplicate to (1) | ||
| (3) | UOM | OUT | EDIFACT | Each | PC | |||
| (4) | UOM | OUT | X12 | Each | EA |
Entry items (1) (2): Internal code is part of the entire search key. For direction OUT, both these entries (1) and (2) are not feasible, because the entire search key is not unique. One entry can be made where you define the external 1 code to have X12 and external 2 code to have EDIFACT. Your EDI translator data map will determine which field to use for the Trading Partner. If needed alternative entries may be where external 1 and 2 may both have X12 codes and external 3 and 4 may both have EDIFACT codes since each standard has multiple codes meaning each to the Oracle E-Business Suite. Again you will rely on the EDI translator data map to choose the correct external field for the transaction.
Entry items (3) (4): If a document standard is entered for the Trading Partner (in the Trading Partner Detail tab) and the a data element is assigned the column DOCUMENT_STANDARD (in the Code Conversion Assignment tab), these table entries will be accessed in the code conversion process before the global entries (1) are accessed.
Planning for Direction IN for Inbound Transactions
The following table shows an example in which all entries are feasible for the direction in. The column Internal Code is retrieved for the application Open Interface tables, the rest of the columns represent the search key. For the direction in, the external code is part of the entire search key. The search key must be unique.
| Item | Category | Direction | Key 1 | Key 2 | External Code 1 | External Code 2 | Internal Code |
|---|---|---|---|---|---|---|---|
| (1) | UOM | IN | EA | Each | |||
| (1) | UOM | IN | PC | Each | |||
| (2) | UOM | IN | EDIFACT | EA | Each | ||
| (2) | UOM | IN | EDIFACT | PC | Each | ||
| (2) | UOM | IN | X12 | EA | Each | ||
| (2) | UOM | IN | X12 | PC | Each |
In the example above, external codes are part of the entire search key. For direction IN, all the entries in the table are feasible, even without the document standard, because the entire search key is unique. It does not matter what the internal codes are since they are not part of the entire search key.
Items (2): If a document standard is entered for the Trading Partner (in the Trading Partner Detail tab) and the a data element is assigned the column DOCUMENT_STANDARD (in the Code Conversion Assignment tab), these table entries will be accessed in the code conversion process before the entries (1) are accessed by all Trading Partners.
Planning for Direction BOTH for Inbound and Outbound Transactions
The following table illustrates an example in which all entries are not feasible for the direction BOTH.
| Category | Direction | Key 1 | Key 2 | External Code 1 (Part Key for Inbound) | External Code 2 (Part Key for Inbound) | Internal (Part Key for Outbound) |
|
|---|---|---|---|---|---|---|---|
| 1 | UOM | BOTH | EA | Each | |||
| 2 | UOM | BOTH | PC | Each | |||
| 3 | UOM | BOTH | X12 | EA | Each | ||
| 4 | UOM | BOTH | X12 | PC | Each | ||
| 5 | UOM | BOTH | EDIFACT | EA | Each | ||
| 6 | UOM | BOTH | EDIFACT | PC | Each |
All Entries Not Feasible for Direction BOTH
Though you may wish to use the data in the code conversion value for both inbound and outbound transactions, the entry in (2) will not be accepted into the code conversion value table after (1) is entered. The reason that entries (1) and (2) are not feasible at the same time is that outbound transactions are using the Internal codes as part of the entire search key. When the value Each is found in the transaction, (even if the Trading Partner was given a default standard to use for code conversion), the internal code Each cannot determine whether EA or PC should be written to the transaction interface file. Similar reasoning applies to (3)/(4), and (5)/(6) when items (4) and (6) are attempted to be entered.
Multiple Standard Codes Convert to a Single Internal Code
There may be code conversion entries needed where multiple codes in a single standard may need to be converted to a single code in Oracle E-Business Suite. The following table illustrates this case. In the table, columns Category, Direction, Key 1, Key 2, Key 3, and Internal Code comprise the outbound transaction's entire search key. This search key must be unique. The columns External Code 1 and External Code 2 are data retrieved for the Transaction Interface File.
| Category (search key) | Direction (search key) | Key 1 (search key | Key 2 (search key) | Key 3 (search key) | Internal Code (search key) | External Code 1 (retrieved data) | External Code 2 (retrieved data) | Note: | |
|---|---|---|---|---|---|---|---|---|---|
| (1) | UOM | OUT | X12 | Acme | Denver | Each | EA | Trading Partner site specified will have EA on the file. | |
| (2) | UOM | OUT | X12 | Acme | Chicago | Each | BX | Trading Partner site specified will have BX on the file. | |
| (3) | UOM | OUT | X12 | Acme | Each | PC | Trading Partner site specified will have PC on the file. | ||
| (4) | UOM | OUT | X12 | Each | EA | Majority of Trading Partners want EA. They were assigned the Document Standard X12. | |||
| (5) | UOM | OUT | OTHER | Each | PC | Other Trading Partners want PC. They were assigned the Document Standard OTHER to allow this entry in the table. | |||
| (6) | UOM | OUT | Each | EA | PC | Default: write two codes to the file; EDI translator will choose one based on the data mapping rule for the given Trading Partner. This avoids making entries for each Trading Partner needing the PC on the file. |
In table above, items (1)-(5): The exact code for the data map is in external code 1 on the file given the Trading Partner data in the search key. You chose not to have the translator choose between EA or PC by accessing item (5).
Items (1) through (3) is Trading Partner specific. If there are many Trading Partners needing separate entries, it may be desirable to assign another document standard to group set of Trading Partners though it is not really their true document standard. Recall that Document Standard Code exist to facilitate the code conversion table entries.
If you choose the strategy in the table below, you need a set of code conversion value codes with the direction IN for inbound transactions since direction BOTH could not be used because of duplicate internal codes. In the table below the columns Category, Direction, Key 1, Key 2, External Code 1, and External Code 2 comprise the search key for the inbound transaction. This search key must be unique. The column Internal Code is the data retrieved for the Oracle E-Business Suite application Open Interface Tables.
| Category (search key) | Direction (search key) | Key 1 (search key) | Key 2 (search key) | External Code 1 (search key) | External Code 2 (search key) | Internal Code (retrieved data) |
|
|---|---|---|---|---|---|---|---|
| (1) | UOM | IN | EA | Each | |||
| (2) | UOM | IN | PC | Each | |||
| (3) | UOM | IN | X12 | EA | Each | ||
| (4) | UOM | IN | X12 | PC | Each |
Note: For direction IN, the External code is part of the entire search key. Multiple Codes within a Standard converting to the Same External Code
In the table above, there is no need for table entries with OTHER in search key 1. For the inbound transactions, the actual document standard X12 suffices since the external codes are unique.
Both Internal Code and External Codes on the Transaction Interface File
You could set up Column Rules on the internal column in the transaction though the internal value is not found on the transaction interface file. This may occur when the application Open Interface file may require a value in certain columns, but the values are derived by the Oracle e-Commerce Gateway. This assumes that code conversion is performed on the data element using the internal and external values on the files that are associated with that data element.
Code conversion is performed before any column rule validation is performed on a column (data element) defined in the transaction. The code conversion process associates (in memory) the internal code found in the code conversion table to the internal field that has the assigned Column Rule. The value of that internal field is validated against all the Column Rules enabled for that field.
Code Conversion is performed before Column Rules are applied.
Consider the example shown in the following table to satisfy the data requirement if code conversion is performed on a field.
| Application Open Interface Column | Oracle e-Commerce Gateway Columns | Sample Record Number | Sample Position Number | Code Conversion Performed | No Code Conversion Performed |
|---|---|---|---|---|---|
| UOM_CODE (Some value must be moved here by one of the following methods: (1) Determined in the Oracle e-Commerce Gateway through code conversion (2) Directly from the file if it is present.) |
UOM_CODE_INT (If data is in this field on the file, code conversion is not performed. In this case, the value is moved directly into the application Open Interface tables.) |
2010 | 100 | The Internal code is derived in code conversion given the codes in the External 1-5 fields. The internal code is moved to the Open Interface Table if found during code conversion. if no code conversion value is found, external 1 is copied to the application open Interface Table. Note that the derived Internal value is not written to the inbound file. |
If placed in the UOM_CODE_INT field by a translator and there is no code conversion performed, this value is copied to the application Open Interface Table. |
| UOM_CODE_EXT1 | 2010 | 110 | Place on the file by a translator to be used for code conversion. | If data is placed in the External codes by a translator and there is no code conversion performed, this code is copied to the application Open Interface Table. | |
| UOM_CODE_EXT2 | not activated | not activated | Used as part of a code conversion if properly enabled | ||
| UOM_CODE_EXT3 | not activated | not activated | |||
| UOM_CODE_EXT4 | not activated | not activated | |||
| UOM_CODE_EXT5 | not activated | not activated |
Activated External Data Fields on the Transaction Interface File
Though there may be data in all five external codes in the code conversion tables, the only external codes to actually be copied to the transaction interface file are the ones that are activated for the file. They are activated if the data element shows a record number, position and width on the data element in the Interface File Definition window or the Transaction Layout Definition report. If data elements are not activated but you wish to use them in the transaction interface file, activate them by entering a record number, position, and width using the Interface File Definition window.
For outbound transactions, both the internal code and external codes are written to the transaction interface file. They are available on the file for mapping to the EDI standard transaction by an EDI translator.
For inbound transactions, the external codes are expected in the file for code conversion or to be passed directly to the application open interface table if an internal code is not derived by code conversion. If another process determines the internal code and writes it to the transaction interface file, that internal code is passed to the application open interface table.
Note: Code conversion is not performed on the data element by the Oracle e-Commerce Gateway if data is already found in the internal field for that data element.
Concatenated Search Key for Inbound Transactions
The following example illustrates the use of the concatenated search key in several attempts to find code conversion table entries for an inbound transaction.
The Oracle e-Commerce Gateway moves data from the transaction interface file into its transaction tables for processing. The data may come directly in the transaction from your Trading Partner or be derived by the Oracle e-Commerce Gateway. Some date elements need code conversion on 1-5 external codes to the internal code defined in the base application. The external codes are defined by Trading Partners or standard transactions.
You may activate code conversion only on the data elements (columns) listed in the Assign Categories window. By entering search keys, the table entry does not apply to all Trading Partners. Trading Partner codes and Trading Partner site codes are often search keys in the code conversion value tables since Trading Partners often have site specific codes.
This example steps through a code conversion table searching for a table entry where the value ‘AIR' in the CARRIER_EXT1 (external 1) column along with its search keys with the value GAMMA in CUSTOMER_NAME in Key 1 and value 9999 in CUSTOMER_SITE in Key 2 are in the transaction. The intent is to find the corresponding internal code given the 1-5 external CARRIER_EXT1 through CARRIER_EXT5 codes so they may be written to Oracle E-Business Suite application open interface tables.
The code conversion value table is read with the following parameters and conditions:
-
Access code conversion values table entries with the SHIP_VIA Category, since SHIP_VIA was assigned to the CARRIER_INT column via the Assign Code Conversion window.
-
The Oracle e-Commerce Gateway moves the transaction values GAMMA into the CUSTOMER NAME column, and the value 9999 into the CUSTOMER_SITE column, which are the column names for the search keys. In this sample, only Key 1 and Key 2 were enabled for the customer name and customer site respectively. (The Oracle e-Commerce Gateway derived the customer name given the customer site 9999 in the transaction.)
-
The external code is part of the full search key for inbound transactions.
-
Since this is an inbound transaction, the process accesses table entries only with transaction direction IN and BOTH.
Code Conversion Setups
The following code conversion table setups were done for this example.
The Code Conversion Categories window has two of the potential five search keys enabled for Category SHIP_VIA to accommodate the customer name and customer site search keys in the other code conversion windows.
The Assign Code Conversion window enables code conversion on a column and specifies which columns to examine for the actual search key values. You decide which columns are used as the Key1 through Key5. The following were set up for this transaction:
-
Column is CARRIER_INT.
-
The Category SHIP_VIA is assigned to CARRIER_INT to enable code conversion.
-
Key 1 is assigned the CUSTOMER_NAME column in the transaction table.
-
Key 2 is assigned the CUSTOMER_SITE column in the transaction table. You selected the keys from a list of values.
The full search key to access the code conversion value table for inbound transactions include the code category, direction, Key 1, Key 2, and the 1-5 external code. For example, the first entry in the table includes the following search key values: (See the sample Code Conversion Value Table for all entries.)
Key 1 has value ‘ALPHA' for the CUSTOMER_NAME.
Key 2 has value ‘1006' for the CUSTOMER_SITE.
The Code Conversion Value window includes the search key values applicable to the internal and external codes for all the Trading Partners and all transactions. The Category that you assign to a column limits the access to just those table entries that have been assigned that Category.
Creating the Search Key
The table below has data found in the transaction or derived by the Oracle e-Commerce Gateway. The data was moved to the transaction columns in the Oracle e-Commerce Gateway for processing.
| Data | Data | Transaction Data | |
|---|---|---|---|
| Description | Customer | Customer Site | Carrier Code |
| Transaction Data | GAMMA | 9999 | AIR |
| Transaction Column | CUSTOMER_NAME | CUSTOMER_SITE | CARRIER_EXT1 |
Specific Transaction Data
Listed below are the full search key parameters given the data found in the transaction or data derived by the Oracle e-Commerce Gateway. Use this data to find a code conversion value table entry to retrieve the external code.
| Code Category: | SHIP_VIA This code category was assigned to a data element in the transaction via the Assign Category window. |
| Direction: | IN and BOTH Determined by the inbound transaction to be processed |
| Key 1 (Customer): | GAMMA Source column for this key 1 is CUSTOMER_NAME. The value GAMMA was derived by the Oracle e-Commerce Gateway given the Trading Partner for this transaction. |
| Key 2 (Customer Site) | 9999 Source column for this ke 2 is CUSTOMER_SITE. The value 9999 was derived by the Oracle e-Commerce Gateway given the Trading Partner for this transaction. |
| Key 3 | not used |
| Key 4 | not used |
| Key 5 | not used |
| External 1 Code: | AIR "AIR" is the value in CARRIER_EXT1 in the transaction interface file. |
The table below shows the three attempts to access the code conversion value table using each search key. The number of attempts is always one more than the number of Key boxes that you enabled in the Code Conversion Categories window. In the example, the source column for Key 1 "Customer" is CUSTOMER_NAME and the source column for Key 2 "Customer Site" is CUSTOMER_SITE.
| Search Order | Code Category |
Direction | Key 1 (Customer) |
Key 2 (Customer Site) |
Key 3 |
Key 4 |
Key 5 |
External 1 Code |
|---|---|---|---|---|---|---|---|---|
| First Search | SHIP_VIA | OUT | GAMMA | 9999 | AIR | |||
| Second Search | SHIP_VIA | BOTH | GAMMA | AIR | ||||
| Third Search | SHIP_VIA | OUT | AIR | |||||
| Source Column | CUSTOMER_ NAME |
CUSTOMER_SITE |
Searching the Code Conversion Value Table
The code conversion value table is read using the search keys in the table above. Three accesses to the code conversion value table may be attempted. The order of the search and the constructed keys are shown in the table.
The three search keys will now be used to search the following example code conversion values table:
| Table Entry | Code Category | Direction | Key 1 |
Key 2 |
Key 3-5 |
External 1 Code |
Internal Code |
|---|---|---|---|---|---|---|---|
| Customer | Customer Site | ||||||
| 1 | SHIP_VIA | IN | ALPHA | 1006 | Alpha-Air | Alpha-Air | |
| 2 | SHIP_VIA | IN | ALPHA | 1099 | Alpha-Air | Alpha-Fast | |
| 3 | SHIP_VIA | IN | ALPHA | Alpha-Air | Alpha-Quick | ||
| 4 | SHIP_VIA | IN | BETA | 1099 | Beta-Air | Beta-Travel | |
| 5 | SHIP_VIA | IN | BETA | 1100 | Beta-Air | Beta-Faster | |
| 6 | SHIP_VIA | IN | BETA | Beta-Air | Beta-Quicker | ||
| 7 * | SHIP_VIA | IN | AIR | Over Night | |||
| Source Column |
CUSTOMER_NAME | CUSTOMER_SITE |
In the example above, the first search has the full key including the external 1 code "AIR". Since there was no table entry for CUSTOMER_NAME with value GAMMA and CUSTOMER_SITE with value 9999, the highest order key is removed for the second search attempt. In this case, the customer site 9999 was removed from the search key parameters. KEY2 with CUSTOMER_SITE was the highest number search key at this time.
The second search with just CUSTOMER_NAME with value GAMMA and the external 1 code AIR also did not find a table entry. So the current highest order key is removed for the next search attempt. In this case, the customer GAMMA was removed from the search key parameter. KEY1 with CUSTOMER_NAME was the highest number search key at this time.
The third search is made with all blank search keys. The internal code is code AIR. This search found a table entry. It is shown in sample code converstion table entry 7.
Consequently, the internal value "Over Night" from the code conversion value table is copied to CARRIER_INT column in the transaction table. If no table entry is found, then the CARRIER_EXT1 (external 1) value is moved into the column CARRIER_INT field in the transaction table. Over Night is the valid value for the application open interface.
Concatenated Search Key for Outbound Transactions
The following example illustrates the use of the concatenated search key in several attempts to find code conversion table entries for an outbound transaction.
The Oracle e-Commerce Gateway extracts data from Oracle E-Business Suite application tables and optional extension tables then ultimately writes the data to the transaction interface file. Some date elements need code conversion from the internal code defined in the base Oracle E-Business Suite application to external codes that are required by the Trading Partners or standard transactions.
You may activate code conversion only on the data elements (columns) listed in the Assign Categories window. By entering search keys, the table entry does not apply to all Trading Partners. Trading Partner codes and Trading Partner site codes are often search keys in the code conversion value tables since Trading Partners often have site specific codes.
This example steps through a code conversion table searching for a table entry where the value "Over Night" in the CARRIER_INT (internal) column along with its search keys with the value GAMMA in CUSTOMER_NAME in Key 1 and value 9999 in CUSTOMER_SITE in Key 2 are set up for the transaction. The intent is to find its corresponding 1-5 external CARRIER_EXT1 through CARRIER_EXT5 codes so they may be written to the transaction interface file.
The code conversion value table is read with the following parameters and conditions. It is used to derive the 1-5 external codes desired for the transaction interface file.
-
Access code conversion values table entries with the SHIP_VIA Category, since SHIP_VIA was assigned to the CARRIER_INT column via the Assign Code Conversion window.
-
The Oracle e-Commerce Gateway moves the transaction values GAMMA into the CUSTOMER NAME column, and the value 9999 into the CUSTOMER_SITE column, which are the column names for the search keys. In this sample, only Key 1 and Key 2 were enabled for the customer name and customer site respectively. (The Oracle e-Commerce Gateway derived the customer name given the customer site 9999 in the transaction.)
-
The internal code is part of the full search key for outbound transactions.
-
‘Over Night' is moved into the CARRIER_INT (internal field) on the transaction interface file. Any one of the CARRIER_EXT1 through CARRIER_EXT5 external codes is written to the transaction interface file if a record number, record position, and field length are assigned to the particular field.
-
Since this is an outbound transaction, the process accesses table entries only with transaction direction OUT and BOTH.
Code Conversion Set Ups
The following code conversion table setups were done for this example.
The Code Conversion Categories window has two of the potential five search keys enabled for Category SHIP_VIA to accommodate the customer name and customer site search keys in the other code conversion windows.
The Assign Code Conversion window enables code conversion on a column and specifies which columns to examine for the actual search key values. You decide which columns are used as the Key1 through Key5. The following were set up for this transaction:
-
Column is CARRIER_INT.
-
The Category SHIP_VIA is assigned to CARRIER_INT to enable code conversion.
-
Key 1 is assigned the CUSTOMER_NAME column in the transaction table.
-
Key 2 is assigned the CUSTOMER_SITE column in the transaction table. You selected the keys from a list of values.
The full search key to access the code conversion value table for outbound transactions include the code category, direction, Key 1, Key 2, and the internal code. For example, the first entry in the table includes the following search key values: (See the sample Code Conversion Value Table for all entries.)
-
Key 1 has value ‘ALPHA' for the CUSTOMER_NAME.
-
Key 2 has value ‘1006' for the CUSTOMER_SITE.
The Code Conversion Value window includes the search key values applicable to the internal and external codes for all the Trading Partners and all transactions. The Category that you assign to a column limits the access to just those table entries that have been assigned that Category.
Creating the Search Key
The table below has data found in the transaction or derived by the Oracle e-Commerce Gateway. The data was moved to the transaction columns in the Oracle e-Commerce Gateway process.
| Data | Data | Transaction Data | |
|---|---|---|---|
| Description | Customer | Customer Site | Carrier Code |
| Transaction Data | GAMMA | 9999 | Over Night |
| Transaction Column | CUSTOMER_NAME | CUSTOMER_SITE | CARRIER_INT |
Listed below are the full search key parameters given the data found in the transaction or data derived by the Oracle e-Commerce Gateway. Use this data to find a code conversion value table entry to retrieve the internal code.
| Code Category: | SHIP_VIA This code category was assigned to a data element in the transaction via the Assign Category window. |
| Direction: | OUT and BOTH Determined by the inbound transaction to be processed. |
| Key 1 (Customer): | GAMMA Sourece column for this key 1 is CUSTOMER_NAME. The value GAMMA was derived by the Oracle e-Commerce Gateway given the Trading Partner for the transaction. |
| Key 2 (Customer Site): | 999 Source Column for this key 2 is CUSTOMER_SITE. The value 9999 was derived by the Oracle e-Commerce Gateway given the Trading Partner site for this transaction. |
| Key 3 | not used |
| Key 4 | not used |
| Key 5 | not used |
| Internal Code: | Over Night "Over Night" was the value in CARRIER_INT from the base application. |
The table below shows the three sets of full search keys to access the code conversion value table for the three attempts to read the table. The number of attempts is always one more than the number of Key boxes that you enabled in the Code Conversion Categories window. The source column for Key 1, Customer, is CUSTOMER_NAME. The source column for Key 2, Customer Site, is CUSTOMER_SITE.
| Search Order | Code Category |
Direction | Key 1 (Customer) |
Key 2 (Customer Site) |
Key 3 |
Key 4 |
Key 5 |
Internal Code |
|---|---|---|---|---|---|---|---|---|
| First Search | SHIP_VIA | OUT | GAMMA | 9999 | Over Night | |||
| Second Search | SHIP_VIA | BOTH | GAMMA | Over Night | ||||
| Third Search | SHIP_VIA | OUT | Over Night |
Searching the Code Conversion Value Table
The code conversion value table is read using the search keys shown above. Three accesses to the code conversion value table may be attempted. The order of the search and the constructed keys are shown in the table. These keys will be used to search the following example of a code conversion values table:
| Table Entry | Code Category | Direction | Key 1 |
Key 2 |
Key 3-5 |
Internal Code | External 1 Code |
|---|---|---|---|---|---|---|---|
| Customer | Customer Site | ||||||
| 1 | SHIP_VIA | IN | ALPHA | 1006 | Alpha-Air | Alpha-Air | |
| 2 | SHIP_VIA | IN | ALPHA | 1099 | Alpha-Fast | Alpha-Air | |
| 3 | SHIP_VIA | IN | ALPHA | Alpha-Quick | Alpha-Air | ||
| 4 | SHIP_VIA | IN | BETA | 1099 | Beta-Travel | Beta-Air | |
| 5 | SHIP_VIA | IN | BETA | 1100 | Beta-Faster | Beta-Air | |
| 6 | SHIP_VIA | IN | BETA | Beta-Quicker | Beta-Air | ||
| 7 * | SHIP_VIA | IN | Over Night | AIR |
-
In the search key algorithm table, the first search has the full key including the internal code Over Night. Since there was no table entry for CUSTOMER_NAME with value GAMMA and CUSTOMER_SITE with value 9999 in the code converstion values table, the highest order key is removed for the second search attempt. In this case, the customer site 9999 was removed from the search key parameters. KEY2 with CUSTOMER_SITE was the highest number search key at this time.
-
The second search with just CUSTOMER_NAME with value GAMMA and the internal code Over Night also did not find a table entry. So the current highest order key is removed for the next search attempt. In this case, the customer GAMMA was removed from the search key parameter. KEY1 with CUSTOMER_NAME was the highest number search key at this time.
-
The third search is made with all blank search keys. The internal code is code Over Night. This search found a table entry. It is shown in entry 7.
Consequently, the External-1 value AIR from the code conversion value table is copied to CARRIER_EXT1 column in the transaction table. If no table entry is found, then the CARRIER_INT value is copied to the CARRIER_EXT1 column in the transaction table. Ultimately, the data in CARRIER_INT and CARRIER_EXT1 appears in the transaction interface file, if they are assigned a record number, record position, and length respectively. These record assignments are displayed in the Transaction Interface Definition window.