This chapter contains Oracle e-Commerce Gateway implementation details.
This chapter covers the following topics:
The System Administrator must assign the Oracle e-Commerce Gateway responsibility to the intended users of the Oracle e-Commerce Gateway database and windows.
Responsibility: System Administrator
Task: Assign Oracle e-Commerce Gateway responsibility to user(s)
The Database Administrator (DBA) must define the UTL_FILE_DIR parameter in the INIT.ORA file.
To use Oracle e-Commerce Gateway, you must first create directories where data files, for both inbound and outbound transactions are stored. Oracle e-Commerce Gateway uses the UTL_FILE package to read and write the ASCII transaction interface flat files on the server.
UTL_FILE can only write to accessible directories. The directories are defined by the utl_file_dir parameter in the init<SID>.ora file. This file is usually found in the $ORACLE_HOME/dbs directory. Within this file, each accessible directory is indicated by a line similar to:
utl_file_dir = directory_name
The specification of directory_name will vary, depending on the operating system. If the operating system is case-sensitive, then directory_name is case sensitive. The value for directory_name must be a physical directory as opposed to a variable, logical or an alias. In addition, the value for directory_name must match the value defined in the Oracle e-Commerce Gateway profile for ECE: Inbound File Path and ECE: Output File Path. Refer to Set Up Profiles for the details.
For example, the following entries are legal for a UNIX system, assuming the directories specified exist:
utl_file_dir = /tmpdirectory_name
utl_file_dir = /home/oracle/output_files
In addition to this form of database security, operating system security must also be considered. The file I/O operations performed with UTL_FILE will be done by the Oracle user (the Oracle user is the owner of the files that are used to run the database, and also the owner of the processes that make up a database instance). Consequently, the Oracle user must have operating system privileges to read from and write to all of the accessible directories. If the Oracle user does not have privileges for an accessible directory, then any operations in that directory will be prohibited by the operating system.
To ensure that operating system security allows the Oracle user to create, delete, rename, read and write files in the specified directories, the DBA must grant directory and file access privileges by issuing the CHMOD 777 command at the operating system level. This is a UNIX example only, so use operating system appropriate commands for your environment.
The Oracle instance must be brought down and back up for the changes in the init<SID>.ora file to be effective.
This chapter provides general information about topics to consider while planning the upgrade to Release 12 or later.
To facilitate a successful upgrade, make sure that the information contained in the Oracle E-Business Suite Upgrade Guide: Release 11i to Release 12.1.1 manual is understood by everyone involved with the upgrade process. Apply Oracle E-Business Suite Release 12.1.delta.2 Release Update Pack (12.1 RUP 2) to upgrade your system to 12.1 RUP 2 release. See "Oracle E-Business Suite Release Update Pack Readme, Release 12.1.delta.2 (12.1 RUP 2)", My Oracle Support Knowledge Document 949406.1 for details.
Note the following regarding the upgrade process:
Plan the upgrade by reading all the steps that apply to your products before beginning. This allows the user to determine the most efficient way to prepare for and finish the process given the unique combination of products.
Failure to complete the upgrade preparation and upgrade finishing steps correctly can adversely affect the upgrade process.
You must be at release 11.5.7, 11.5.8, 11.5.9 CU2, or 11.5.10 CU2 of the Oracle E-Business Suite to upgrade to Release 12. If you are on any earlier release must upgrade to 11.5.10 CU2 before you can upgrade to R12. See Oracle E-Business Suite Upgrade Guide: Release 11i to Release 12.1.1 for details.
Pay close attention to all warnings that indicate where and when you run upgrade steps. Carefully coordinating your upgrade preparation work across different products will avoid errors.
If you are an Automotive customer, review the Release 12 Automotive Upgrade documentation to insure that all pre upgrade and post upgrade steps are performed.
For existing Oracle Order Entry users, it is important to note that Oracle Order Entry is not included in the initial Release 12 and, thereby, the inbound Purchase Orders (850/ORDERS) and the outbound Departure-based Shipping Notice/Manifest (856/DESADV) transactions will not be available for use until the new Oracle Order Management product is released.
It is important that customers stay current on the latest patch sets provided by Oracle e-Commerce Gateway. This insures that they have the most recent bug fixes and added functionality. Information on the latest patch sets available can be obtained from Oracle Support Services or their web sites.
Prior to running the Release 12 upgrade process, it is important to remember to run the two report scripts that are provided. These report scripts help identify customizations that may have been made to the existing transaction record layouts, any table updates to accommodate extension tables, and identifies new code conversion categories that may have been added.
If you have made any customizations to the seeded data including extension tables or program routines, execute the report scripts both before and after you apply the upgrade. The before report identifies the current definitions, the after report identifies the new definitions after the upgrade has been applied. The differences between the two reports identify the customizations that must be evaluated. If necessary, re-implemented your customizations after you apply the upgrade. In addition, make certain you have complete documentation of software modifications before applying the patch. This allows you to easily re-apply your changes, and make additional changes if the seed data reconciliation could not restore that transaction layout or set up.
Detailed information on running these report scripts can be found the Release 12 Upgrade manual.
Seed data is generally defined as any data delivered with a standard installation of Oracle E-Business Suite. This includes menu definitions, concurrent manager definitions, list of values, etc. In this chapter, the definition of seed data is limited to the data used for:
Definition of transaction record layouts
Assignment of code categories to a transaction record layout
Definition of process rules associated with a transaction
Definition of column rules associated with a transaction
Whenever a transaction-specific patch is applied, there may be changes or additions to the transaction record layout. Code category assignments, column rules, and process rules are closely linked to the transaction record layout so these definitions may also be impacted by a change to the transaction record layout.
The Release 12 Seed Data Reconciliation process allows you to reapply the preexisting transaction record layout, if a patch updated the transaction record layout, or accept the new transaction record layout. Retaining the preexisting transaction record layout gives you a record layout that minimizes or eliminates transaction data map changes for any process that uses your transaction such as the Translator.
In addition to reconciling transaction record layouts, this process also performs reconciliation on the code conversion assignment, column rules, and process rules associated with the transaction record layout changes.
For detailed information on how the Seed Data Reconciliation process works, refer to the Oracle e-Commerce Gateway User's Guide.
Recognizing that no two businesses operate in the exact same manner, Oracle e-Commerce Gateway provides users with profile options to configure their implementation. This allows the user to set up an ERP environment that matches their business environment.
Oracle e-Commerce Gateway supports two types of profile options; system level profile options which are used by all transactions and transaction level profile options used by a specific transaction. System level profiles are usually done as an initial set up whereas transaction level profile options are done as you implement a transaction.
The following table lists the three system profile options. The table also shows profile description, category, whether the profile option is required or optional, and its default value:
| System Profile Option | Description | Category | Required | Default Value |
|---|---|---|---|---|
| ECE:OUT_FILE_PATH | Indicates the directory to which outbound data files are written. This value must match the actual directory on disk and is designated in the INIT.ORA file. | Deployment | Yes | No Default |
| ECE: ATT_SPLIT_WORD_ALLOWED | This profile option is used for attachments to the Purchase Order Outbound and Purchase Order Change Outbound transactions. A value of "Yes" indicates words in attachments are split when the segment size is reached. "No" indicates that words are not split. A new line is created after a space or a punctuation mark. | Functional | Yes | No Default |
| ECE: IN_FILE_PATH | Indicates the directory where inbound data files are expected. This value must match the actual directory on the disk and that is designated in the INIT.ORA file | Deployment | Yes | No Default |
For more profile category information, see Oracle E-Business Suite System Administrator's Guide - Maintenance for details.
Note: For system profile options related to directory paths, refer to Define UTL_FILE_DIR Parameter in INIT.ORA File for the details.
Transaction profiles are categorized as follows:
Address precedence for each inbound transaction
Enable/disable flag for each transaction
Transaction specific options
| Transaction Profile Option | Description | Category | Required | Default Value |
|---|---|---|---|---|
| ECE_XXX_ADDRESS_PRECEDENCE | Enable Address Precedence for transaction where XXX represents a specific inbound transaction | Functional | Yes | LOC |
| ECE_XXX_ENABLED | Enables the transaction where xxx represents a specific transaction | Functional | Yes | Yes |
For more profile category information, see Oracle E-Business Suite System Administrator's Guide - Maintenance for details.
For each transaction supported by Oracle e-Commerce Gateway, there is a transaction enabled flag, (i.e.: ECE_XXX_ENABLED, where XXX represents a specific transaction). This flag indicates if a transaction will be enabled or disabled at the system level. The setting determines Oracle e-Commerce Gateway's ability to process a given transaction. If the flag is disabled, the transaction will not be processed by Oracle e-Commerce Gateway. You still need to enable the flag in the Detail tab of the Define Trading Partner window to enable the transaction at the trading partner level.
All inbound transactions have an address derivation precedence profile option. The address derivation derives addresses based on certain codes provided to the Oracle e-Commerce Gateway in inbound transaction interface files.
The address precedence has three categories of components. They have unique address ids, location code/translator codes, location names, and physical addresses. These addresses are verified and validated as transactions are processed.
The address precedence profile option has the following list of values:
LOC (Default): Address components and/or location code combination
The address derivation uses the combination of location code and address components to derive the address. If there is only one value provided, either location code or address components, the address will be derived based on the provided value. If both location code and address components are provided, then it will be based on the combination of both.
Note: If no address is found for both location code and address components or if the populated address components and location code do not match, then a message 'Unable to derive ship to address' will appear.
If both location code and physical address are provided, then it will derive address through location code and compare it with that derived through physical address.
LTC: Location code/translator code combination
A trading partner setup is required for the address to be derived.
LPA: Location code/physical address/party details combination
The address derivation uses the combination of location code, customer name, customer number, and address components to derive the address. If there is only a partial set of values provided, the address will be derived based on the provided values.
This feature is available only for address type "CUSTOMER".
Additional profiles exist for the Purchase Order transactions, for the handling of attachment data to suppliers. These profiles require the user to define if attachments are to be enabled at various levels of a transaction. The purpose of these profile options is to make the transaction interface file size manageable. The available values for the profile names listed below are Yes or No. Yes will enable the attachments, and No will disable the attachments. If using attachments you also need to define the attachment segment size profile option.
Attachments are supported at different levels:
Header Level: header attachment
Inventory Item level: master item attachment and inventory item attachment
PO Line Level: line attachment
Shipment level: shipment attachment
Below is a table showing the purchase order-specific transaction profile options, their descriptions, profile categories, an indicator of required or optional, and their default values:
| Purchase Order-Specific Transaction Profile Option | Description | Category | Required | Default Value |
|---|---|---|---|---|
| ECE:POO_ATTACHMENT_SEGMENT_SIZE | Sets the attachment segment size for the outbound Purchase Order transaction. Attachments can be split into segments to accommodate their insertion into the data file. The segment size is expressed in bytes | Functional | Yes | 400 |
| ECE:POO_HEADER_ATTACHMENT_ENABLED | Enables the header attachment for the outbound Purchase Order transaction | Functional | Yes | No |
| ECE:POO_INVENTORY_MITEM_ATTACHMENT | Enables the master inventory item attachment for the outbound Purchase Order Request transactions | Functional | Yes | No |
| ECE:POO_INVENTORY_ITEM_ATTACHMENT | Enables the inventory item attachment for the outbound Purchase Request transactions | Functional | Yes | No |
| ECE:POO_LINE_ATTACHMENT_ENABLED | Enables the line attachment for the outbound Purchase Order transactions | Functional | Yes | No |
| ECE:POO_SHIPMENT_ATTACHMENT_ENABLED | Enables the shipment attachment for the outbound Purchase Order transactions | Functional | Yes | No |
Note: The same options apply to the outbound Purchase Order Change Request transaction.
For more profile category information, see Oracle E-Business Suite System Administrator's Guide - Maintenance for details.
Since the purpose of the planning schedule is to work closely with your suppliers to communicate forecast information and to get the right materials to the right place at the right time, one additional profile is used to prevent zero schedule from being written to the file.
| Supplier Scheduling Specific Transaction Profile Option | Description | Category | Required | Default Value |
|---|---|---|---|---|
| ECE_SPSO_EXCLUDE_ZERO_SCHEDULE_FROM_FF | Sets the profile value to prevent zero schedule from being written to flat file | Functional | No | No |
For more profile category information, see Oracle E-Business Suite System Administrator's Guide - Maintenance for details.
The ECE_POO_ATT_SEG_SIZE sets the attachment segment size for the outbound purchase order transaction. Attachments can be split into segments to accommodate their insertion into the interface file.
The size of the attachment should be synchronized in three places. The following table illustrates where to change it:
| Setup for Text Length | Note | Original | Change | Use |
|---|---|---|---|---|
| Profile Options window: Transaction Profile Setup |
Sets the attachment size for the transaction. Attachments can be split into segments to accommodate their insertion into the interface data file. The segment size is expressed in bytes. The default is 400. | 400 | 80 | 80 |
| Interface File Definition window | Set to the same length as the attachment. If the data element on the file is excessively long, the data element will have many trailing blanks. If the data element on the file is not long enough, the data may be truncated. | 800 | 80 | 80 |
| Translator Data Map | The translator will map each data element to a field that is your specified length. You do not need to manipulate text size to fit into a standard data element if the data was divided into several record in the transaction interface file. | 80 |
The use of attachments is not required. If not used, consider disabling the attachment record defined for the transaction. Refer to Modify Transaction Interface File for more details.
The following are common set up errors related to system/transaction level profiles:
Did not define system level profile options
Concurrent Manager log file reflects an error indicating transaction is not enabled
Errors regarding address derivation occurred
Refer to Troubleshooting chapter for details.
Oracle e-Commerce Gateway's exception handler is designed to provide quick and easy analysis of any exception detected during inbound processing. It is a tool to prevalidate incoming data before the data is imported into Oracle E-Business Suite application Open Interface tables for processing into application base tables.
The exception handler uses tree style navigation and color-coded icons to help you quickly identify the exception. Dynamic windows and drill downs provide summary and detailed information to help you analyze and diagnose the cause of any exception. Once the cause of an exception is identified and resolved, you have the option to resubmit the transaction for processing, ignore the exception or delete the transaction.
To take advantage of Oracle e-Commerce Gateway's exception handler, you must first define the validation rules. There are two types of validation rules; one at the process level for the overall transaction and the second at the column level for the individual data elements within a transaction. You define the validation rules using the Assign Process Rules or Assign Column Rules window accessible via the Interface File Definition window.
Process rules are defined at the transaction level and apply to the entire transaction. Process rules are required and should not be changed although the Assign Process Rules window does not prevent it. Oracle e-Commerce Gateway's exception handler supports three process rules as follows:
Invalid Trading Partner
This rule checks if the incoming translator code/location code combination (on the interface file, control record) is defined for a trading partner. Additionally, the transaction type identified on the incoming interface file must be enabled for the trading partner identified.
Test/Production Discrepancy
This rule checks if the incoming test flag (on the control record 0010 in the transaction interface file) matches the flag setting for the Trading Partner identified by the Translator Code/EDI Location Code combination (in the Trading Partner setup). If they are different, then this rule is violated.
Invalid Document Address
There are several procedures, which validate and derive different types of addresses (Ship-to, Bill-to, Sold-To, etc.). If one of these procedures are unable to derive a unique address site, then this rule is violated. This validation occurs for all the key address sites associated with the transaction except for the location code on the control record.
Seeded Process Rules
Each inbound transaction is seeded with the three process rules outlined above. In addition, each rule is seeded with a default rule action of “Skip Document” that tells Oracle e-Commerce Gateway to skip the current document and proceed to the next document if a process rule exception is detected. You can change the corresponding process rule action if the default behavior is not desired (see below regarding Rule Actions).
Column rules are defined at the column level and apply to a specific data element of a given transaction. Except for the seeded column rules (see below regarding Seeded Column Rules), column rules are not required. If they are used, you must define an associated rule action that tells Oracle e-Commerce Gateway how to proceed if a column rule exception is detected. The same set of rule actions available for process rules are used for column rules (see below regarding Rule Actions).
Oracle e-Commerce Gateway's exception handler supports seven column rules as follows:
Datatype Checking
Examples of data types are alphanumeric, numeric, and date. This rule compares the data type defined in the ece_interface_columns table with the data type of the data in the incoming interface file. If the data types do not match, then this rule is violated.
Default If Null
This rule is used to put a value into the column if it is null (blank) in the incoming interface file. This is the only rule that modifies the incoming data as a value is placed in the Oracle E-Business Suite application Open Interface table. The interface file is not updated. Use the ‘Log Only' rule action for this column rule.
Null Dependency
This rule is used to create complex comparisons for a column within the document. Null Dependency allows the user to set up conditional expressions that indicate whether the assigned column must be null or cannot be null depending on the values in other columns.
Predefined List
This rule is used to validate the incoming data for a given column against a user defined list. For example, transaction_method "Must Equal" EDI implies the value "EDI" is on the user defined list. If "Cannot Equal" is checked, then transaction_method cannot equal "EDI."
Simple Lookup
This rule requires you to identify the specific table and column containing a list of valid values. The table and column names are used to dynamically build a select statement that is used to select data to compare to the assigned column. The tables may be user-defined tables outside of the Oracle E-Business Suite. For example of a Simple Lookup, the dynamically built select statement is "select lookup_code from ece_lookup_values." If the incoming transaction value does not exist in the list of selected values, then this rule is violated. No List of Value (LOV) is provided for the table, column or condition field. Instead a "Validate" button may be used to verify the syntax of the dynamically built select statement.
Valueset Lookup
This rule is used to compare the value in the assigned column to an Oracle Application Valueset.
Value is Required
This rule checks if the incoming data for a given column has a non null value. If it is null, then it violates the rule.
General guidelines regarding column rule definitions are as follows:
A column may have more than one rule defined
A column may have the same rule defined multiple times each with different conditions
The same rule may apply to multiple columns of a given transaction
Different rules for a given column can have different rule actions (see below regarding Rule Action Precedence)
Each inbound transaction is seeded with column rules for required fields, date fields, and numeric fields. The default settings for column rule and rule action are shown in the following table:
| Field Types | Column Rule | Rule Action |
|---|---|---|
| Required Fields | Value is Required | Skip Document |
| Date Fields | Data type Checking | Skip Document |
| Numeric Fields | Data type Checking | Skip Document |
A column is identified as required if it is required by Oracle e-Commerce Gateway or the Oracle E-Business Suite application Open Interface to successfully import an interface file. Examples include values to key fields or parameters that identify a business rule to be used when validating incoming data. Refer to the Application Transaction Detail chapter for a list of required fields relevant to the transaction being implemented.
The Assign Column Rules window allows you to change or delete the column rules for required, date or numeric fields but this type of change is not recommended unless the potential exception can be resolved in the application Open Interface. You can change the corresponding column rule action if the default behavior is not desired.
Whether you use Oracle e-Commerce Gateway's exception handler to pre-validate incoming data or not is dependent on your business rules and the accuracy of your trading partner's interface files. If you do not define any column rules at all, you are assured that at minimum the seeded column rules are executed.
Given the variety of ways in which Oracle E-Business Suite application modules support error detection, error reporting and error research tools, it is easier to do as much prevalidation as possible via Oracle e-Commerce Gateway so that you can centralize the error research effort. This way all errors may be reviewed and resolved at the same time allowing the transaction to be reprocessed in a timely manner.
Oracle e-Commerce Gateway applies code conversion prior to applying the column rules, therefore you can define a column rule for a column allocated for the internal value although the actual value is not placed on the transaction interface file but is derived during code conversion. This derived value will be validated using the column rule and then placed in the application Open Interface table if valid or staged if invalid. Refer to Set Up Code Conversion for more details.
Each process or column rule requires a rule action that tells Oracle e-Commerce Gateway how to proceed if an exception is detected. There are four rule actions as follows:
Skip Document
This action skips the current document and continues processing the next document. You can view the detected exceptions for this document via the View Staged Documents window.
Log Only
This action writes a message to the log file. It treats the document as a successful document and continues processing the remaining documents.
Abort Run
This action rolls back the entire run and sets the concurrent request status to ‘Error'.
Disabled
This action temporarily disables the rule until you reactivate it by assigning an appropriate rule action.
Since a given data column can have more than one rule defined for it, it implies that there can be more than one rule action defined for it. If this occurs, the exception handler applies the rule actions beginning with most conservative to least conservative as follows:
Abort Run
Skip Document
Log Only
Once the validation rules and associated actions are defined and the process is initiated, the run-time execution engine proceeds according to your validation rules.
The status of a transaction process and any detected exceptions may be viewed using a single “workbench style” window known as the View Staged Documents window. Summary or detailed inquiries are available to help you analyze the cause of an exception.
If necessary, a user-activated run-time execution log is available to assist with trouble shooting. This diagnostic tool supports multiple levels of trace details for both technical and non-technical analysts.
Refer to Troubleshooting chapter for details on how to use the Run-time Execution Log and View Staged Documents window(s) to research any detected exceptions.
Once the cause of an exception is identified and resolved, you have the option to resubmit the transaction for processing, ignore the exception or delete the transaction.
If you agree that a detected exception is an error, you must proceed to get the error corrected.
Since the documents being transmitted between you and your trading partner represent legal documents, Oracle e-Commerce Gateway does not allow you to correct any errors, so the errors must be corrected at the source.
If you cannot resolve the exception using the appropriate Oracle E-Business Suite setup, you can bypass the validation in Oracle e-Commerce Gateway by enabling the ‘IGNORE' check box via the View Staged Documents window. Alternately, you can delete the transaction and ask your trading partner to resend a corrected transaction.
If the error originated in Oracle E-Business Suite, you must correct the error in the appropriate Oracle E-Business Suite application module and then resubmit the transaction using the View Staged Documents window. There is no need to re-import the interface file from the sender.
If you determine that a detected exception is really a warning that does not adversely affect the process, you can do one of the following:
Un-assign the column rule using the Assign Column Rules window. This change applies to all documents of the same transaction type until you reassign the rule.
Assign a less restrictive column rule or column rule action using the Assign Column Rules window. This change applies to all documents of the same transaction type until you reset the rule.
Request that Oracle e-Commerce Gateway ignore the validation by enabling the IGNORE flag via the View Staged Documents window
Delete the transaction using the View Staged Documents window
Once you processed the warning exception, you can resubmit the transaction using the View Staged Documents window for processing into the Oracle E-Business Suite application Open Interface tables. When the import process of loading data into the application Open Interface tables completes successfully, you must resubmit the request to execute the application Open Interface process so the data can be imported into the Oracle E-Business Suite application base tables.
Oracle e-Commerce Gateway's exception handler is designed to validate the entire inbound file and report all exceptions to you so that you can make the necessary corrections all at once. However, if there are errors at multiple levels of the transaction, Oracle e-Commerce Gateway's exception handler proceeds as far as it can and then continue processing only after the necessary corrections are made and the transaction is resubmitted for processing. In the best case scenario, all exceptions are reported the first time. In the worse case scenario, this is an iterative process requiring your attention at each iteration until all exceptions are resolved.
The Assign Process Rules and Assign Column Rules window prevents you from defining invalid or incomplete validation rules. However, if you define an incorrect rule or the rule is assigned incorrectly, the expected pre-validation may not be performed. The following are errors related to defining validation rules:
Deleted or changed a required process rule
Deleted a column rule for a required field
Changed a column rule for a date or numeric field
Assigned a column rule that does not match the type of data expected for the data element
Did not activate a rule defined with a ‘Disabled' rule action
Did not provide a list of valid values for ‘Predefined List' column rule
Did not provide a valid Oracle E-Business Suite valueset for ‘Valueset Lookup' column rule
Did not provide a valid table, column, or condition for ‘Simple Lookup' column rule
Refer to Troubleshooting chapter for details.
The information presented in this section is of an advisory nature and assumes you have attended an Oracle e-Commerce Gateway training course and are familiar with standard Oracle features such as Flexfields.
Oracle e-Commerce Gateway provides several methods to add data elements to the transactions that are not defined in the base Oracle E-Business Suite data model. You may wish to add data elements to obtain data that is missing from the inbound file or that is required by multiple organizations. The following table lists these methods and whether they apply to inbound or outbound transactions.
| Method | Applies to Outbound Transactions | Applies to Inbound Transactions |
|---|---|---|
| Trading Partner Header Reference fields | Yes | No |
| Descriptive Flexfields in the Oracle e-Commerce Gateway | Yes | Yes |
| Descriptive Flexfields in the base Oracle E-Business Suite | Yes | No |
| Oracle e-Commerce Gateway Extensible Architecture | Yes | No |
| Interface File Definition | Yes | Yes |
| Interface Data File processing | No | Yes |
There are two Trading Partner reference fields on the Trading Partner header defined in the Define Trading Partner window as TP_REFERENCE_EXT1 and TP_REFERENCE_EXT2.
These fields are written to the control record 0010 in every outbound transaction.
This data can be mapped to a standard transaction field by the Translator if required
These fields are not examined by the Oracle e-Commerce Gateway for inbound transactions.
Oracle e-Commerce Gateway standard mapping contains a selection of flexfields that allow for the transmission of additional data outbound/inbound if that data has its origin or destination in activated flexfields within the Oracle E-Business Suite.
The availability and transaction level - header, line, shipment etc. - of these flexfields in the Oracle e-Commerce Gateway is dependent on the transaction involved. It cannot be assumed that every transaction will always have all flexfields available to be mapped, the mapping should therefore be verified on a case by case basis.
Unlike the extensible architecture the data that can be mapped into the flexfields is limited to the number of flexfields that have been activated within Oracle E-Business Suite, and is also limited by the scope of the application Open Interface for a given inbound transaction.
Any additional data that is passed via flexfields must have a suitable corresponding position within the fields available in the message standard being used.
Subject to the comments mentioned in the previous section, data contained within the flexfields activated in the Oracle E-Business Suite can be transmitted outbound via the Oracle e-Commerce Gateway. Most additional data transmission requirements can be met using mapped flexfields where the extra data required is to be found within the relevant Oracle E-Business Suite applications.
The Oracle e-Commerce Gateway Extensible Architecture feature is used when data from an outside application must be merged with Oracle E-Business Suite data to create a single outbound transaction. This feature affords great flexibility to customize outbound transactions based on individual business needs. For example, there may be data contained in legacy systems tables or files that must be included in a transmission along with data extracted from the base Oracle E-Business Suite application. Data that is stored in additional columns in application tables, which are not in the standard Oracle E-Business Suite or Oracle e-Commerce Gateway configuration, may also be written to the extension tables.
Refer to Extensible Architecture chapter for details.
The size, location and relative order of the fields processed by Oracle e-Commerce Gateway can be modified using the Interface File Definition window. The width of a field may be increased or decreased to suit customer or standards requirements. The relative position of a field within the interface file record may be changed, or a field may be activated to appear in the transaction interface file by allocating it a record number, relative position, and column width. Alternatively fields may be inactivated if they are not required, for example to reduce inbound/outbound file sizes, though it is recommended that the reasons for deactivating fields, other than not used flexfields, be well understood.
External (code conversion) fields may be renumbered (changing the position numbers) to allow them to be inserted into, or read from, the interface file in a different order to their position in the Interface File Definition display - refer to the Oracle e-Commerce Gateway User's Guide. However, changing the position number impacts the data map in the Translator. Newly activated columns may be positioned after the seeded columns on a record or even written to another record to minimize the impact on existing data maps. The Internal (code conversion) field numbered ‘0' must not be altered. The ‘0' is an indicator that this field is the Internal code for the Oracle E-Business Suite.
Any changes to the interface file definition must be synchronized with the mapping to the Translator.
Removing fields is a simple action, but the implications of finding that the fields are required at a later date will affect the Translator mapping and also may affect the Transaction Specification agreement with Trading Partners.
Refer to Modify Transaction Interface File for guidelines on changing transaction interface file definitions.
The file produced by Oracle e-Commerce Gateway for outbound transactions, and imported by it for inbound transactions, is a flat text file. It may be manipulated by standard editor or programming languages such as SQL and COBOL. Because of this the file may, for example, be padded in the case of transmission via software that requires fixed length fields. For inbound transmissions, data may be added by reference to the Oracle E-Business Suite, or third party tables in the case where data is missing from the inbound transactions, or data such as tax coding needs to be added based upon specific geographic supply sources.
Oracle e-Commerce Gateway files may also be easily read, or test files easily created, using standard editors. It should be noted, however, that because the file may extend to a width of 1024 characters the editor should be set to minimum margins with fixed, non-proportional width font to ensure properly formatted display without wrapped data.
Oracle e-Commerce Gateway creates outbound and receives inbound transaction interface files. The transaction record layouts (a.k.a. file formats) are predefined by Oracle e-Commerce Gateway and stored in a data repository. The seeded transaction layout definitions may be used as is or customized to match the data you transmit to or receive from your trading partner. You may want to change the seeded transaction layout definitions for the following reasons:
Change file structure
Change record layout
Change column attributes
Delete unused data elements and records
Activate additional external fields for code conversion
Activate unmapped data elements
Extend Oracle e-Commerce Gateway supported transactions
Use the Transaction Layout Definition Report to review the seeded definitions including any modifications you may have entered. Use the Interface File Definitions window to make any necessary definition changes. Since the transaction layout definitions are stored in a data repository, all changes entered using the Interface File Definition window are effective at run-time without any code modifications. Once the necessary changes are entered, re-run the Transaction Layout Definition Report and use the report output to synchronize your new transaction layout definitions with the Translator.
Refer to the Oracle e-Commerce Gateway User's Guide for details on how to use the Transaction Layout Definition Report and the Interface File Definitions window.
The following table reviews some of the terms discussed in this section:
| Term | Definition |
|---|---|
| Data Element | The smallest component of a transaction interface file |
| Record | A collection of data elements |
| Common Key | The first 100 bytes of every record containing key data about the Trading Partner and document |
| Control Record (Record 0010) | The first record of every transaction containing key data about the transaction and trading partner |
| Transaction Interface File | A collection of records containing transaction-specific data |
Refer to the chapter on Transaction Interface File Architecture for a detailed description of Oracle e-Commerce Gateway's transaction interface file structure.
The structure of a transaction interface file is defined during the design process and is based on how the data will be mapped per the message standards. The objective is to group the data in logical groups so that it can be easily mapped by the Translator.
The following are some guidelines regarding file structure changes:
Due to the one-to-many relationship between header and detail level data, do not move detail level data to the header level.
If you move header level data to the detail level, make sure the header level aggregated data is accurately represented as de-aggregated data at the detail level. This may require some re-mapping or custom coding to accomplish.
Do not use the same record number across different data levels
Moving records around within the same data level is acceptable
Each data element is defined to a relative position within a record. The relative position indicates the order the data elements are written to the transaction interface file. Except for the control record, the relative position of a data element in a record may be changed as necessary.
The following are some guidelines regarding record layout changes:
Do not change the record layout of the common key in any record. The Interface File Definition window prevents this so this guideline applies to custom coding only.
Do not change the record layout of the control record.
If you move a data element from one record to another, make sure the total length of the record does not exceed 1024 bytes with the first 100 bytes reserved for the common key.
Aside from being defined to a relative position within the record, each data element has seeded column attributes for record number, position, width, sequence, layout code, layout qualifier, process rules, and column rules. Any one of these column attributes may be changed as necessary with the following guidelines:
Do not change the column attributes of the common key in any record. The Interface File Definition window prevents this so this guideline applies to custom coding only.
Do not change the column attributes of the control record.
Do not use detail level record numbers in header records.
Do not repeat record numbers within or outside the same data level.
Do not repeat position numbers within the same record.
Define a column width less than or equal what Oracle E-Business Suite supports to prevent data from being truncated.
Make sure the total length of the record does not exceed 1024 bytes with the first 100 bytes reserved for the common key.
Do not use sequence number 0 as it is reserved for the data element's internal value.
Make sure the external fields defined for code conversion are uniquely sequenced from 1 to 5.
If you change a layout code, make sure you make the same change for every data element in the record. The layout code from the first data element in the record is written to the transaction interface file.
If you change a layout qualifier, make sure you make the same change for the related data elements in the record. The layout qualifier from the first data element in the record is written to the transaction interface file.
Do not change the seeded process rules, these are required. You may change the process rule action as necessary.
Do not change the seeded column rules for data elements that are required by Oracle e-Commerce Gateway or Oracle E-Business Suite Open Interface. These data elements have the ‘Value is Required' column rule defined for it. Refer to the Application Transaction Detail chapter for a list of required fields by transaction.
During the design process, the transaction interface file is defined to accommodate every relevant data element from Oracle E-Business Suite including descriptive flexfields. However, not all data elements are mapped or used because either the message standards or the trading partner did not require it. To maximize efficiency, delete all unused data elements and records using the following guidelines:
Delete all unused Oracle e-Commerce Gateway and Oracle E-Business Suite descriptive flexfields.
Delete all unused Oracle E-Business Suite free form text fields such as attachment, notes and descriptions
Retain at minimum one of the five allocated external fields in case you want to activate code conversion for the data element in the future. Delete all other unused external fields.
Do not delete the control record or any data elements in the record. This record is required.
Do not delete any data elements in the common key. These data elements are required.
Do not delete data elements containing a process rule, as process rules are required.
Do not delete data elements required by Oracle e-Commerce Gateway or Oracle E-Business Suite Open Interface. These data elements have the ‘Value is Required' column rule defined for it. Refer to the Application Transaction Detail chapter for a list of required fields by transaction.
During the design process, five external fields are defined for each data element identified as a candidate for code conversion. However, the actual number of external fields that are activated are based on the requirements of the receiving system. Examples of the receiving system's requirements are the EDI standards, ISO 9000 standards (i.e. UOM codes or currency codes) or specific trading partner requirements. Any of the five external fields that are not activated are available for you. You can activate these unmapped external fields by assigning the appropriate column attributes including record number, position number, width, sequence, layout code, layout qualifier and column rules. The following are guidelines for activating unmapped external fields:
Use the same record number as the other related external fields if the maximum does not exceed 1024 bytes with the first 100 bytes reserved for the common key.
To minimize re-mapping of existing data maps, use a position number that will position this data element to the end of the record.
Define a column width less than or equal what Oracle E-Business Suite supports to prevent data from being truncated.
Review the sequence number of the other related external fields and assign a sequence number not already used by the other fields. If you need to re-sequence the existing external fields to accommodate the new external field, make sure they are uniquely sequenced from 1 to 5.
Do not use sequence number 0 as it is reserved for the data element's internal value.
Use the same layout code and qualifier as the other related external fields in the record.
Assign a column rule for the external field as necessary.
Similar to additional external fields for code conversion, there are other Oracle e-Commerce Gateway or Oracle E-Business Suite fields which were included in the transaction design but not mapped to the transaction interface file. You can activate these unmapped fields to add new data elements to existing transactions by assigning the appropriate column attributes. The guidelines are similar to those stated above for activating unmapped external fields for code conversion although these data elements are not limited to supporting code conversion requirements only.
Oracle e-Commerce Gateway provides several methods to extend currently supported transactions through the use of trading partner reference fields, descriptive flexfields, and Oracle e-Commerce Gateway Extensible Architecture. Refer to Enable Additional Transaction Data for details on when and how to use each method.
Once the necessary changes are entered, re-run the Transaction Layout Definition Report and use the report output to synchronize your new transaction layout definitions with the translator.
Moving data elements from the line level to the header level
Changing the record layout or column attributes of the control record
Deleting the control record
Incorrect usage of external fields for code conversion
Exceeding maximum record length of 1024 bytes
Deleting data element containing a process rule
Deleting data elements that are required by Oracle e-Commerce Gateway or Oracle E-Business Suite
Refer to Troubleshooting chapter for details.
The steps for setting up a Trading Partner are detailed below. First read the chapter on the Trading Partner for important details about the Trading Partner. Reference the Oracle e-Commerce Gateway User's Guide for details to complete the windows.
Define Trading Partner Group
Enter a new Trading Partner Group or access an existing Trading Partner Group. Select an operating unit for the Trading Partner Group.
Enter a new Trading Partner name or position your cursor on an existing Trading Partner name. Select the appropriate New or Open button. All Trading Partners within the group are all associated with the specified operating unit defined for the group.
Define Trading Partner - Assignment
From the Define Trading Partner window, select the Assignment tab. Select the name and site from the list of values. Only the names and sites from the operating unit specified in the Trading Partner Groups window are presented in the list of values.
When multiple operating units are associated with your responsibility, if you want to associate a different set of addresses for the same Trading Partner group and same Trading Partner, then you have to reselect the operating unit in the Trading Partner Groups form first and then change the setup for the underlying Trading Partners. This way, the associated Trading Partner addresses of another operating unit can be viewed for selection when defining Trading Partners for the group. The operating unit is relevant to the underlying addresses only and not to the Trading Partner groups or Trading Partner headers.
The EDI Location Code is returned from the base Oracle E-Business Suite application where the site is defined.
Define Trading Partner - Details
From the Define Trading Partner window, select the Details tab. Enter the transactions, the transaction types, the translator code, and the document standard for code conversion for the trading partner. You will also enable the document (transaction) for processing, and set the Test indicator to flag the transaction as test or production.
Define Trading Partner - Contact (Optional)
From the Define Trading Partner window, select the Contact tab. The information on the contact tab is optional. It contains contact data for the specified trading partner. It may be used by the EDI Coordinator for the trading partner's EDI Coordinator's contact data. This data is for reference only. It is not used by Oracle e-Commerce Gateway.
The following are common Trading Partner setup errors:
Invalid Trading Partner/EDI Location Code
Transaction not enabled
Invalid Trading Partner
Trading Partner Group already exists
This location already has a different Trading Partner value. Overwrite?
INVALID_OPERATION error occurred while writing to the output file
Refer to Troubleshooting chapter for details.
The steps for setting up a Code Conversion are detailed below. First read the section on Code Conversion Detail for important details about Code Conversion. Refer to the Oracle e-Commerce Gateway User's Guide for details to complete the forms.
Review/Define Code Conversion Categories
Review the list of seeded Code Conversion Categories. Add additional Code Conversion Categories if needed for your code conversion value table entries.
Enable the number of search keys that you will use in the code conversion value table for each Code Conversion Category if you are using search keys.
Enable Code Conversion
Locate the transaction, format map, and transaction level that has the column (data element) name that you need to enable for code conversion. You enable a column for code conversion by entering a code conversion category in the Category column. When this is done, only code conversion value table entries with the code conversion category will be accessed during code conversion for that column.
If you use search keys, enter the column name that contains the value to be used as part of the search key.
Define Code Conversion Values
Enter all the data for the code conversion value tables: Category, Description, Direction, Search Key 1 - Key 5, Oracle internal code, External code 1 - external code 5.
The Translator is a third-party software application designed to transform a formatted file into any other format. Traditionally, it transforms proprietary formats to EDI transactions according to the designated standard (e.g., X12, EDIFACT). It considers both the data required by the chosen standard and the data required by the trading partner. It maps the data from the transaction interface file to the required file format according to the requirements. A Translator data map may be defined to produce a transaction according to the recommendations of any industry guideline such as UCS, EIDX, or AIAG. A single data map may accommodate the data requirements of a single trading partner or many trading partners.
The Translator identifies each trading partner's transactions with a unique code, and then invokes a communication method (ftp, e-mail, fax, etc.) to transmit this file either to the trading partner directly or to a third party for further processing or communication. The process is reversed (received communication of standard EDI file through translator to interface file) for inbound transactions.
The following should be considered for interfacing with a Translator:
Accurate Translator Code
Available reports
Changes in Record Layout due to upgrade or patch.
Predefined record layouts and data maps in the Translator.
Synchronize record layouts in the Oracle e-Commerce Gateway and the Translator
Create data maps in the Translator for inbound transaction
Create data maps in the Translator for outbound transactions
Detecting Files
Transferring Files
Communications
The Translator Code is used to identify Trading Partners between the Oracle e-Commerce Gateway and the Translator. The Translator Code is first defined in the Translator. In the Translator, it is used to access trading partner specific maps, codes, data, and their entire profile. It is copied to the Translator Code field in the Trading Partner Detail tab in the Define Trading Partner window in the Oracle e-Commerce Gateway. It must be accurate. The Translator Code along with the EDI Location Code are used to derive the trading partner site in the base Oracle E-Business Suite application. It is written on every Control Record 0010 at the start of each transaction in the transaction interface file. If the Translator Code is wrong, then the data may be mapped to the wrong data map, and/or sent to the wrong Trading Partner.
These reports should be present in one form or another on a Translator. Some Translators provide additional reports, the ability to develop different reports and the ability to execute user-defined queries on Oracle and other databases to produce other reports.
Transaction Interface File Definition Report
The record layout expected for outbound transactions and produced for inbound transactions.
Transaction Data File Report
Used to validate a data file against its record layout.
Trading Partner Definition Report
Shows the data entered for each Trading Partner, such as contacts, Translator Code, and method of communication.
Trading Partner Map Report
Show the translation map for a given Trading Partner or set of trading partners.
The Oracle E-Business Suite and the Oracle e-Commerce Gateway may change the layout or actual data contained in the transaction interface file because of an upgrade, or a patch. They may also change because of your customization to meet your own or a Trading Partner's requirements.
Many Translator vendors provide pre-defined data maps in standard EDI formats for the most common transactions, such as purchase orders and invoices. They may even provide pre-defined base maps for particular large vendors, however, these maps will almost always need further definition for your particular situation.
The Translator must know where to look for data to map to a given standard transaction and format. They rely on the Record Number on each record in the transaction interface file to identify the data on that record, given the transaction definitions in the Oracle e-Commerce Gateway. At the same time, a Translator may have a preference for getting certain data elements earlier in the outbound transaction interface file so that it can use that data in multiple places. In an inbound file, it may be easier for the Translator to process the incoming data into a certain order. Adjusting the layouts between the Oracle e-Commerce Gateway and the Translator can optimize the processing for both.
Some trading partners may want to use different data segments of the EDI standard or use other standard formats such as XML for inbound transactions. The Translator stores this layout and formatting information in a file called a data map or simply a map. This allows the translator to know where to look in the inbound transaction file for the data needed to populate the EDI segment. The translator can also use this map to perform code conversions from the values in the Oracle E-Business Suite to the EDI standard for weight, for example. Deciding which code conversions can be done on the Oracle e-Commerce Gateway and which on the translator is part of the Synchronization process above. One map can serve multiple trading partners.
Some trading partners may want to use different data segments of the EDI standard or use other standard formats such as XML for outbound transactions. The Translator stores this layout and formatting information in a file, called a data map, or simply a map. This allows the Translator to know where to put the data from the EDI segment in the output transaction file. The Translator can also use this map to perform code conversions from the values the EDI standard uses those the Oracle E-Business Suite application expects for weight, for example. Deciding which code conversions can be done on the Oracle e-Commerce Gateway and which on the Translator is part of the Synchronization process above. One map can serve multiple Trading Partners.
The Translator must recognize when it has data waiting to be translated or communicated. Normally this is done manually by the user or by the presence of a file in a given directory. This is why the files built by the Oracle e-Commerce Gateway are in one directory, moved to another directory to be translated, and once formatted to the desired standard moved into a third directory to be communicated.
The link between the Translator and the Gateway must also be considered, i.e. are they on the same server, on different UNIX boxes, or is the Translator on a mainframe. This affects both the file structures going back and forth - e.g. variable for UNIX but fixed width for mainframe transfers - and (possibly) the functionality of the Translator. For example the mainframe based Translator may use an older architecture which means it is potentially less flexible in set-up terms than its (newer) UNIX cousin.
The Translator identifies and uses the communication method specified for each trading partner. This can be any method supported by the hardware and software of the system.
The goal of archiving or backing up data is to preserve data at each stage of its creation and transmission so that it can be recovered if it is lost or deleted. The first level is the backup of the Oracle database performed by the DBA, and makes sure that changes made to the applications in the database, from any source, are preserved for any needed recovery. The second level is the backup of the entire system, performed by the system administrator. This backs up the files from the operating system level, whether they are part of the database or not. The strategy you choose for these functions is the first step to determine how much archiving needs to be done.
The Oracle e-Commerce Gateway often needs another level of archiving. Once the data has been extracted from the base application into a transaction interface file, and again once that transaction interface file has been processed into a transaction file by the Translator, these files should be preserved. The first reason is to save them as an audit trail, showing what data was sent through the process to the trading partner. This audit trail also serves to identify the transformation done by the Oracle e-Commerce Gateway and the Translator, so that you can correct the conversions they do. The second reason is that there may be legal constraints in the trading partner's country and/or industry which require commercial documents to be kept for a period of months if not years. It is also possible that the trading partner's industry sector may deal in huge value or high risk goods, e.g. aerospace, in which case secure archiving of transactions is essential in the case of disputes, investigations, or legal action. The other purpose of archiving is to be able to re-transmit outbound transactions to trading partners if necessary. But whatever the reason, or the transaction(s) involved, archiving should apply to both inbound and outbound transactions.
Transactions may be processed through the Oracle e-Commerce Gateway several times per day. Each time the extraction process is executed, an output file name must be provided to the Concurrent Request. To preserve each of these output files separately, a unique name is generated with each run. The application generates a default file name that starts with the transaction code, or you may enter your own file name.
Another place the archiving is handled is in the translator. Almost all translators archive the files they create either immediately after creation or after an attempted transmission whether successful or not. Some translators may handle archiving the input file from the Oracle e-Commerce Gateway. However, this only removes the need for the preceding step if the translator is run immediately after the extraction every time. For outbound transactions, it may also be necessary to designate the directories where a file will be placed by the Oracle e-Commerce Gateway, archived from the Oracle e-Commerce Gateway, picked up by the translator for processing, output by the translator, archived from the translator, or picked up by the communication process. For inbound transactions, the directories must be the directory the file is deposited into by the communication process; the directory the translator picks up the file from, the directory the pre-translation file is archived into, the directory the Translator writes the translated interface file into, and the directory the Oracle e-Commerce Gateway reads the file from.
A sample set of directories and files are listed below.
D:\outbound_flat_file:
Directory the Oracle e-Commerce Gateway places outbound transaction files. It should be defined in UTL_FILE_DIR in the init.ora file and the Oracle e-Commerce Gateway profile ece:outbound file path.
D:\outbound_flat_file\archive:
Directory the files from the Oracle e-Commerce Gateway are copied into after generation. The reason for having this directory and the next one separate the source of file generation, so the translator cannot attempt to open a file the Oracle e-Commerce Gateway is generating.
D:\outbound_flat_file\Translator_input:
Directory the Translator reads its input file from. Although this can be the same as the Oracle e-Commerce Gateway archive directory, it generally should not be. This is because the translator usually looks in the outbound directory and reads every file in it. It assumes that if the file is in this directory, it should be processed.
D:\outbound_flat_file\Translator_input\archive:
Directory the translator archives each file as it successfully translates it.
D:\outbound_flat_file\Translator_output:
This is where the translator writes its outbound transaction files.
D:\outbound_flat_file\communication:
This is where the communication process looks for files that are ready to be sent.
D:\outbound_flat_file\communication\archive:
This is where the communication process copies files it has successfully transmitted.
A sample set of directories and files are listed below.
D:\inbound_flat_file\communication:
This is where the receiving communication process deposits the incoming file. Once the communication process finishes, the file(s) are copied to the following file.
D:\inbound_flat_file\translator_input:
This is the directory where the translator looks for its input. Again, the translator generally gets all files in the directory. Once it successfully translates a given file, it copies it to the following file: D:\inbound_flat_file\translator_input\archive:
D:\inbound_flat_file\translator_output:
This is where the translator writes the translated file. When the translation run is complete for all files in the translator_input directory, the files are copied into the following file.
D:\inbound_flat_file\Gateway_input:
This is where the Oracle e-Commerce Gateway looks for transaction interface files to process. It should be defined in UTL_FILE_DIR in the init.ora file. Once it processes a file, that file is copied into the final directory below.
D:\inbound_flat_file\Gateway_input\archive:
Obviously, mechanisms such as UNIX shell scripts or Windows batch files need to be defined to move these files between some of these directories. Execution in some cases can be set up in the translator; the movements from D:\outbound_flat_file\ to D:\outbound_flat_file\archive and D:\inbound_flat_file\Gateway_input to D:\inbound_flat_file\Gateway_input\archive will have to be set up to execute in Concurrent Processing as part of a Process Set running the Oracle e-Commerce processing.
A degree of operating system expertise will be required for successful implementation. For example, a UNIX cron job needs to be set up to run the communication process (ftp, or mail file processing) and then move the files to the other directories at certain times of the day.
If this process is followed, you will be able to examine the files created at each step. They will also be able to process without contention, however, there are eventually going to be a lot of files out there. Even if disk space is fairly cheap, eventually performance will force the removal of older files. You may want to consider removing the files after you make the operating system backup. Once they are preserved on a separate medium, they can always be recovered as needed.