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 Applications 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.
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.
Additional Information: For additional information, see: