Interface Data File
This chapter covers the following topics:
Changing the Interface Data File Record Layout
Both inbound and outbound interface data file formats are predefined by Oracle e-Commerce Gateway The interface data file format may be used as defined or customized to match your specific business requirements.
Some of the reasons to modify the predefined interface data file format include:
-
Exclude unused columns and unused records.
-
Change column size.
-
Rearrange interface data file records within the same interface table.
-
Activate data elements in the transaction tables.
-
Change sequence of the five external fields resulting from code conversion.
-
Change the record layout code.
-
Change the record layout qualifier.
By making the interface data file formats table-driven, your format changes take effect immediately without any code changes.
Note: You cannot use this window to add data elements to the interface data file, unless they are already defined in the e-Commerce Gateway transaction tables. At the end of each table (indicated by level at the top of the window), there are data elements without record numbers, positions, and lengths indicated. You can activate these data elements to appear in the interface file by assigning a record number, position, and length. You cannot move data elements to another table level by assigning a record number in the other table's record number range.
To change the interface data file:
-
Navigate to the Interface File Definition window.
-
Select the transaction type. See: EDI Transaction Support.
-
Select the transaction data level.
This identifies the level of data within the transaction defined in the e-Commerce Gateway for outbound transactions, and in the application open interface tables for inbound transactions. The levels may differ when compared to the base application tables as data is denormalized.
All the records for the data level are displayed. The data format for outbound transactions is displayed by e-Commerce Gateway interface column. The data format for inbound transactions is displayed by application open interface column. You cannot change the data in these two columns.
-
Change the record number.
You can change the record number to another number within the same table level or enter blanks to exclude the record from the interface data file. The record number indicates the relative position of a group on that record in the file. No record can exceed 600 bytes.
Note: To activate data elements, you must define the record number, record position number, and the width of the data element. Each level requires at least one data element active.
-
Change the record position number.
You can change the position number to another number within the record or enter blanks to exclude the column from the interface data file.
The position number identifies the relative position of a data item in a record.
-
Change the width of the data element.
Most data elements have lengths based on standards. Those lengths are usually longer in Oracle E-Business Suite. You may increase the length up to the length in the application as needed per data element.
-
Change the sequence of external codes.
This identifies which external code (1 - 5) in the code conversion values table is placed on this data element in the transaction interface data file. The default is that external code 1 in the code conversion values table is moved to the external 1 code in the interface table, external code 2 in the code conversion values table is moved to the external 2 code in the interface table, and so on.
For example, placing a sequence 5 next to data element external 1 means that the external code from the code conversion values table is moved into the data element external 1 in the transaction interface data file.
A conversion sequence of 0 is reserved for the Oracle internal value. Do not change.
-
Change the record layout code.
This identifies the data content of a data element. These codes are predefined for the transaction. Standard record layouts for addresses and flexfields are defined and used across all transactions. Other record layouts are defined for a specific transaction. You may modify the predefined value. See: Record Layout Codes, Oracle e-Commerce Gateway Implementation Manual.
These codes have no significant data mapping in business processes. They are used for reference for reviewing the file for research.
Only the record layout code and record layout qualifier from the lowest numbered record position is used in the interface data file.
-
Change the record layout qualifier.
This identifies the type of data in the record. For example, a generic record layout code for addresses may have a record layout qualifier that identifies the address as a ship-to, bill-to, or remit-to address type. You may modify the predefined value.
These codes have no significant data mapping in business processes. They are used for reference for reviewing the file for research.
Only the record layout code and record layout qualifier from the lowest numbered record position is used in the interface data file.
-
If you want to define process rules, choose Process Rules. If you want to define column rules, choose Column Rules. The number of rules is displayed for reference.
For additional information, see:
Interface Data File Structure, Oracle e-Commerce Gateway Implementation Manual
Assigning Process Rules
Note: Process rules apply to inbound transactions only.
Use the Assign Process Rules window to assign data validation rules and the action(s) to follow if the rule is violated for any transaction. You may set the action to Disabled so that a particular rule is not enforced.
Note: Unlike column rules, process rules apply to the entire transaction, not data elements.
The Assign Process Rules window shows the current transaction and all associated process rules. These fields are read-only. For each of the process rules (Invalid Document Address, Invalid Trading Partner, and Test Production Discrepancy), select the appropriate action. The process rules are:
-
Invalid Document Address
Any address referenced in an inbound interface data file (ship-to, bill-to, remit-to) must correspond to an address or location record in Oracle E-Business Suite. A common address derivation function uses location codes, location IDs, and physical address data to establish this link between address data on the interface data file and an address in Oracle E-Business Suite. If an address or location record cannot be derived by this function, a process rule violation occurs for the specified transaction.
-
Invalid Trading Partner
Checks if the incoming translator code and location code (on control record 0010) match those in the trading partner setup. If there is not a match, the customer or supplier for this transaction could not be derived based on the data in the control record. The rule is also violated if the transaction is not enabled.
-
Test/Production Discrepancy
Checks if the incoming flag is the same as in the trading partner setup.
To assign process rules:
-
Navigate to the Assign Process Rules window. Do this by choosing Process Rules from the Interface File Definition window.
-
For each predefined column rule, select an action:
Abort Run: stops the processing of the document and sets status to Error.
Disabled: Prevents the rule from being enforced.
Log Only: Places a message in the log file and continues processing.
Skip Document: Skip current document and processes the next.
-
Save your work.
Note: For additional information, see:
Changing the Interface Data File Record Layout
Assigning Column Rules
Use the Assign Column Rules window to assign one to six data validation rules to any data element. You can assign multiple column rules to any column. You can also assign the same column rule type multiple times.
In a few transactions, required data fields may have been seeded with the required data element rule. You can remove that requirement, but the application's open interface may fail its required data edit.
If needed, you can disable a column rule.
The Assign Column Rules window shows a column in the selected transaction and all associated column rules. These fields are read-only. For each of the column rules, select the appropriate action. Certain column rules require additional parameters that appear in other windows.
The column rules are listed below:
| Column Rule | Description |
Additional Parameters Needed |
|---|---|---|
| Datatype Checking | Compare the datatypes of the data in the file to the datatype defined in the e-Commerce Gateway transaction table ECE_INTERFACE_COLUMNS. | None required. |
| Default if Null | Move a default value to the staging table column if the field is null (blank) in the interface data file. | The default can either be a literal (a constant) or from a value in another column. Check either Literal Value or Column Name to indicate the source of the default value. Then enter the value of the column name depending on the box chosen. |
| Null Dependency | Checks if the incoming data for a given column has a null or not null dependency based on the following conditions. More than one set of condition parameters can be entered at the same time. |
Check boxes for Must be Null or Cannot be Null for the data in the column to be reviewed. Then enter the Column Name, the Dependency, and Value for the condition. The dependency must be Null, Not Null, or Equal. Multiple dependencies are read as AND conditions. For example, set the check box to Cannot be Null and enter the following conditions: TRADING_PARTNER Is Not Null; SHIP_TO_SITE Is Not Null; COUNTRY Equal USA. This means that the column being reviewed cannot be null if the three listed conditions are true. |
| Predefined List | Checks if the incoming data for a given column is equal to or not equal to a value in a predefined list in the e-Commerce Gateway. | Check must be either Must Equal or Not Equal to indicate the comparison condition. The incoming value must match one of the values in the list. |
| Simple Lookup | Compares the value in the assigned column with the results of a SQL statement that is dynamically created with this column rule. The assigned column value must match one of the values returned by the SQL statement for this column rule to pass data validation during inbound processing. Specify any table and column and optionally define a condition (a WHERE clause) that will be appended to the dynamic SQL. Then validate the dynamic SQL using the Validate button. |
Enter a table name, a column, and a condition where the condition is a SQL WHERE clause. Perform validation to confirm that the table, column, and condition are valid. For example, the table name is ECE_LOOKUP_VALUES, the column is LOOKUP_CODES, and the condition is LOOKUP_TYPE='DOCUMENT'. |
| Valueset Lookup | Compares the value in the assigned column to an Oracle E-Business Suite Valueset. You must select a valueset from the List of Values | Select a standard Oracle valueset from a list of values. |
| Value is Required | Checks if the incoming data for a given column has a non null value. If it is null, the rule is violated. | None required. |
Whenever a column rule is violated, the transaction is marked with a violation. These are displayed in the View Staged Documents window.
To assign column rules:
-
Navigate to the Assign Column Rules window. Do this by choosing Column Rules from the Interface File Definition window.
-
Select a column rule from the List of Values.
Depending on what column rule you select, additional fields display and may require additional data associated with the column rule. See the list of column rules above to determine if and what additional data is required.
-
Select a rule action to assign to the column rule:
Abort Run: stops the processing of the document and sets status to Error.
Disabled: Prevents the rule from being enforced.
Log Only: Places a message in the log file and continues processing.
Skip Document: Skip current document and processes the next.
-
Save your work.
Note: For additional information, see: