Receive Customer Item Cross Reference Service

This service is in the WmOAMFG1227 package and has the following fully-qualified service name: inventory1227.intoOA.CICrossRef:receiveCICrossRef.

This service imports Custom Item Cross-References into Oracle Inventory.

You can import Customer Item Cross References at three levels: Customer-level, Address Category-level, and Address-level. The level is specified in the Item Definition Level column. An item imported at the Customer level is visible to all organizations. You can also import items with an inactive status. It is possible to define multiple references between a customer item and several inventory items. In this case, a preference ranking is established for processing in Oracle Inventory when a customer item is demanded.

While submitting the concurrent program Customer Item Cross References Import to load the Customer Item cross reference records from interface tables to production tables, it is not possible to restrict the processing to only the records uploaded by the IS flow instance. The program will process all unprocessed records that exist in the interface table at the time of execution. After executing the concurrent program, errors will be retrieved for all processed records that might not be uploaded by this flow instance. We suggest that you keep the interface table free of error records, to minimize ambiguity.

Database Scripts

This service uses the following database scripts:

Script Description
wm_install_into_cicrossref.sql Runs the scripts listed below, except the uninstall script.
wm_into_cicrossref_pkg.sql
Installs
WM_INV_CICREF_IMP_HANDLER_PKG.
WM_HANDLE_CICREF, which calls the
WM_CONC_REQUEST.
WM_REQUEST_SUBMIT
procedure to submit the Customer Item
Import process. The process in Oracle Inventory
module is Import Customer Item Cross References.
wm_drop_into_cicrossref.sql Uninstalls the components created by wm_install_into_cicrossref.sql.

For more information about using database scripts, see Database Scripts.

Supporting Transaction Definitions

This service was built from the configured services defined in the following transaction definitions:

  • setCICrossRefTxn1227.txp
  • CICrossRefTxn1227.txp

For information about using the transaction definition files to customize this service, see Transaction Definitions.

Flow Control

This service uses the following flow steps to import customer item cross references:

  • specifyDefaultSettings specifies the default parameter settings required for service execution. You should change these settings accordingly.
  • specifyConcProgParams specifies the default parameter settings required for concurrent program execution. You should change these settings accordingly.
  • bizDocMapping maps the incoming business document structure to the required Oracle Applications data structures (the interface tables).
    • getOracleAppsUserId is a transformer for mapping the business document IData structure to the interface table IData structure. It takes ORACLE_APPS_USER_NAME as the input parameter from the business document, and queries the table FND_USER to get the USER_ID. The USER_ID information is required for insertion into the interface tables.
    • getOASystemDateObject returns the Oracle Applications system date as a date object.
  • setCICrossRefTxn inserts data into the interface table. It extracts data from the IData structure that results from the bizDocMapping service, and puts the data into the interface table in Oracle Applications for CICrossRef.
  • importCICrossRef imports data to the production table from the interface table. It calls the services execCICrossRefConcProg, checkCICrossRefImportStatus, and getCICrossRefImport_ERR to execute the corresponding concurrent program that inserts data into the production table, and to generate the error/acknowledgement message. If the status of the execution is SUCCESS (returned by the service execCICrossRefConcProg), it checks for rejected records. If any are found, it then indicates an error during import. In this case, this service calls getCICrossRefImport_ERR to retrieve the errors. If no records are found, it comes out of the flow, indicating success of the data import process. If the status of the execution is FAILED, it comes out of execution.
    • execCICrossRefConcProg invokes the stored procedure WM_INV_CICRREF_IMP_HANDLER_PKG.WM_HANDLE_CICRREF. The procedure calls the corresponding concurrent subroutine to execute the data import process for Customer Item Cross Reference into Oracle Applications. This service returns Status ID, Request ID, Execution Status Message (for normal concurrent program completion), and database Stored Procedure error message (if an exception occurs in the Stored Procedure execution).
    • checkCICrossRefImportStatus checks the status of the execution by checking the Interface Table for rejected records. If the query does not return rows, it indicates a successful import. If the query returns rows, it indicates that the concurrent program could not import data successfully in the production tables of Oracle Applications. The Interface table is not updated with the REQUEST_ID; there is no way to check errors corresponding to only the current bizDoc import. Therefore, the error document will show any old errors, as well.
    • getCICrossRefImport_ERR gets the error message that occurs during the data import to the production table from the interface table. It scans the MTL_CI_XREFS_INTERFACE table to get the rejected records. The Interface table is not updated with the REQUEST_ID; there is no way to check errors corresponding to only the current bizDoc import. Therefore, the error document will show any old errors as well.

For more details on receive transactions, see Overview of Receive Service Transaction Processing.

Business Document Structure

This service uses the business document CICrossRefBizDoc. Its structure is as follows:

  • CUST_ITEM_CROSS_REF
CUST_ITEM_CROSS_REF (Maps to MTL_CI_XREFS_INTERFACE)
Field Name Map to Column Description
CUSTOMER_NAME CUSTOMER_NAME Required.
CUSTOMER_CATEGORY_ CODE
CUSTOMER_
CATEGORY_CODE
 Address category code of the customer.
ADDRESS1 ADDRESS1  
ADDRESS2 ADDRESS2  
ADDRESS3 ADDRESS3  
ADDRESS4 ADDRESS4  
CITY CITY  
STATE STATE  
COUNTY COUNTY  
COUNTRY COUNTRY  
POSTAL_CODE POSTAL_CODE  
CUSTOMER_ITEM_NUMBER
CUSTOMER_
ITEM_NUMBER
 Required.
ITEM_DEFINITION_LEVEL
ITEM_
DEFINITION_LEVEL
 Item definition level for import. Use:
  • 1 for Customer.
  • 2 for Address Category.
  • 3 for Address.
Note: If you use 2, Customer and Address Category fields are required. If you use 3, Customer and Address fields are required
INVENTORY_ITEM INVENTORY_ITEM Item number as defined in Oracle Inventory for the item to be used for cross reference.
MASTER_ORGANIZATION_
NAME
MASTER_
ORGANIZATION_
NAME
 Name of the master inventory organization.
PREFERENCE_NUMBER
PREFERENCE_
NUMBER
 Required. Preference ranking for multiple references to the same item.
INACTIVE_FLAG INACTIVE_FLAG Required. Use:
  • 1 for Yes
  • 2 for No