| Oracle® Application Integration Architecture Agile Product Lifecycle Management Integration Pack for Oracle E-Business Suite: Design to Release Implementation Guide Release 11.2 Part Number E36185-03 |
|
|
PDF · Mobi · ePub |
| Oracle® Application Integration Architecture Agile Product Lifecycle Management Integration Pack for Oracle E-Business Suite: Design to Release Implementation Guide Release 11.2 Part Number E36185-03 |
|
|
PDF · Mobi · ePub |
This chapter provides an overview of the integration flow for initial load of items and discusses:
Solution assumptions and constraints
Integration flow for initial load of items
Understanding the item batch load extract process
Understanding the item batch load process
Oracle E-Business Suite interfaces
Agile Product Lifecycle Management (PLM) interfaces
Integration services
The Agile Product Lifecycle Management (PLM) Integration Pack for Oracle E-Business Suite integrates product content information between Agile PLM and Oracle E-Business Suite. The process integration for initial load of items enables you to:
Perform a one-time initial load of item data from Oracle E-Business Suite into Agile PLM in bulk. You must make sure that the data in these two systems is continuously synchronized after the initial load by using other integration flows.
Enhance or enrich the item data along with other contents from other sources such as another system or, web sites, internal spreadsheets and so on.
Establish a cross-reference between the synchronized entities.
To load the item data from Oracle E-Business Suite into Agile PLM, you must perform the following:
Extract the data from Oracle E-Business Suite into a XML file.
The item extract process invokes Oracle E-Business Suite application programming interface (API) to download the item data from Oracle E-Business Suite and transforms this item data into an XML file, which is understood by Agile.
Additionally, you can also choose to enrich this item data by modifying the generated XML file.
Load the items from the XML file into Agile PLM.
The item load process reads the XML file as an input and then loads its content into Agile PLM using the Agile item web service.
The following are the solution assumptions and constraints for this integration:
This process integration design is intended for initial loading of items and not for incremental loading.
The only filtering criteria to filter Oracle E-Business Suite data is the Org ID. More filtering can be achieved by modifying the Ebiz event generation stored procedure.
Items extracts are available only in XML format. Extension of the file is ".xml"
You can edit the xml files to add or modify the data in any way. However, make sure that the structure of the XML is intact.
Item number coming from Oracle E-Business Suite concatenated with Org ID is sent as Item ID to Agile.
No translatable elements are published to Agile.
If PIM installed UDA attributes are published from Oracle E-Business Suite, then this process integration will publish only current item attribute. However, no AML or category assignment information will be moved.
In Agile controlled systems, the lifecyclePhase of an item cannot be created or updated by using the initial load Create or UpdateObject operations.
This section describes the high level implementation details of the services involved in the integration of the initial load of items from Oracle E-Business Suite to Agile PLM.
The following diagram shows the process integration for initial load of items.
The process integration for initial load of items includes the following steps:
An event generation process raises a business event in Oracle E-Business Suite which is based on the following three input parameters:
OrgID: An item in Oracle E-Business Suite is associated with an OrgID. This input parameter is used for filtering item data for a specific organization.
Batch size: Specifies the number of items per batch. Default batch size is 20.
Max Batch size: Specifies the total number of batches to generate. Default maximum batch size is null which means that it produces batches until all items are loaded.
The item extract process takes note of this business event and queries in Oracle E-Business Suite to find the associated item numbers of this event.
The list of item numbers fetched in step 2 are passed over to the Oracle E-Business Suite API to retrieve the item details that are to be loaded in Agile PLM.
The Oracle E-Business Suite API responds with a message, which includes the item details retrieved from Oracle E-Business Suite.
The BPEL process transforms the Oracle E-Business Suite API output to the Agile schema, which is understood by Agile PLM. Finally, the BPEL process writes the item data into a XML file.
The number of XML files generated by the Oracle E-Business Suite API is determined by the number of records that needs to be loaded divided by the batch size.
Note:
This item extract process does not populate every element of Agile PLM schema from Oracle E-Business Suite. It populates only the minimum information available from Oracle E-Business Suite. The out-of- the-box (OOTB) transformation contains only the bare minimum data. If you have to, you can extend this transformation by adding other elements of data.
Generally, this OOTB transformation process is executed only during an initial load and on a demand basis. We do not recommend that you to use this for incremental synchronization of items.
The execution may start from Oracle E-Business Suite where an event can be raised to run this process. When you perform an initial load of items, we recommend that you run this OOTB process. However, when you invoke this process, you must temporarily switch off the existing item synchronization flow.
You can manually enhance or enrich the item data in the XML file along with other contents from other sources such as another system, or web site or, internal spreadsheets, and so on. To edit the XML file, you can use a standard editor or any other mechanism to map elements from other sources.
The item load process reads the XML file as having an Agile schema as an input file and loads it into the Agile PLM using the Agile web service. It also updates the item cross-reference tables.
If you have large data volumes to be extracted and loaded from Oracle E-Business Suite to Agile PLM, you can analyze the data to be loaded and then segment this data in batches based on the time that you have allocated to complete the load. Data should be organized and loaded based on the priority in which the data is used by end-users.
Batch loading of item data is a one-way process that loads data in bulk from Oracle E-Business Suite into Agile PLM. To use item data that exists in your Oracle E-Business Suite, Oracle recommends that you plan an initial data load strategy.
This section describes the PL/SQL script, EGO_EBI_ITEM_LOAD in Oracle E-Business Suite database that you use to perform the initial batch load of data from Oracle E-Business Suite to Agile PLM. The PL/SQL script includes these procedures:
GENERATE_EVENTS
PURGE_ EVENTLOG
The procedure, EGO_EBI_ITEM_LOAD.GENERATE_EVENTS is used to perform the batch load and synchronization of item data from Oracle E-Business Suite to Agile PLM.
You must run GENERATE_EVENTS to generate events for the item data to be loaded during the initial batch load. The script uses filter criteria (which can be customized) to identify the records to be loaded, then generates a business event for each set of records as determined by the batch size. When each event is generated, the Event ID and the primary keys of the processed records are logged to a log table. The subscriber, created during the installation of the prebuilt integrations, retrieves the records using the provided API or view using the Event ID. The log table is also used to filter out records that are processed in case the script must be restarted.
The following table shows the parameters that are used in the GENERATE_EVENTS:
Table 10-1 Parameters in Generate_Events procedure
| Parameter | Value | Description |
|---|---|---|
|
Org_ID |
Number |
Specifies the organization identifier for the items that are loaded. |
|
BatchSize |
Number |
Specifies the number of records to include in each batch event. You should not specify a batch size greater than 20. The default batch size is also 20. |
|
MaxEventsNumber |
Number |
Specifies the maximum number of batch events to generate. If a value is not specified, events are raised until all records returned by the query are processed. |
Tip:
You can also run this procedure to restart the batch load from the beginning.
The following table shows the structure of the log table:
Table 10-2 Parameters in Generate_Events procedure
| Name | Description |
|---|---|
|
Key column(s) |
Stores the primary key of the top-level entity. The column names match the name of the entity. A unique index is created for these columns. |
|
Event-ID |
ID of the event that was raised for this entity. Because multiple entities are included in each event, this is not unique. |
Each row in the log table represents one entity record. The keys for all the records included in an initial load are inserted at the front of the log table. As each event is raised, the Event ID column is populated for the processed records.
A separate log table is created the first time, the initial load script is run for the item batch load. The table can be deleted after the initial load is completed. However, to use the batch load process for any future bulk load of account or item data, do not delete the log table; instead, run PURGE_EVENTLOG after the initial batch load is completed.
This section describes the item batch load extract process. The following diagram shows the item batch load process to extract item data from Oracle E-Business Suite.
When you initiate the Item batch load process, the following events occur:
Run the EGO_EBI_ITEM_LOAD.GENERATE_EVENTS procedure once for each organization for which you want to synchronize item data from Oracle E-Business Suite to Agile PLM, specifying values for the BatchSize and MaxEvents parameters.
All active items for a given organization from Oracle E-Business Suite are selected.
To select a subset of the items in an organization for batch data load, modify the PL/SQL filter clause shown in the PL/SQL filter clause example in the EGO_EBI_ITEM_ LOAD.GENERATE_EVENTS procedure:
Example 10-1 PL/SQL Filter Clause
SELECT INVENTORY_ITEM_ID, ORGANIZATION_ID, NULL FROM MTL_SYSTEM_ITEMS_B WHERE ORGANIZATION_ID = p_organization_id AND bom_item_type in (1, 2, 4) AND customer_order_flag = 'Y' AND customer_order_enabled_flag = 'Y';
The procedure publishes items from the organization provided in the parameter p_organization_id. The p_organization_id is the ID of the inventory validation organization.
When you run the EGO_EBI_ITEM_LOAD.GENERATE_EVENTS procedure for the first time, all the eligible Item IDs are entered into the EGO_EBI_ITEM_LOAD_LOG file and the number of events specified in the Max_Events parameter are generated. Each event contains the number of items specified by the Batch_Size parameter.
After all the events are raised and consumed successfully, run the EGO_EBI_ ITEM_LOAD.PURGE_EVENTLOG procedure to purge all the records from the EGO_EBI_ITEM_LOAD_LOG.
The ProcessEbizItemBatchLoadConsumer listens to business events and receives the WF_EVENT_T_message event payload for the batch itemLoad event, and then it routes the complete event payload to ProcessItemListInitialLoadEBizABF
ProcessItemListInitialLoadEBizABF queries EGO_EBI_ITEM_LOAD_LOG table with the data from WF_EVENT_T msg to populate a list of Item IDs.
ProcessItemListInitialLoadEBizABF calls the API NV_EBI_ITEM_PUB.get_item_attributes procedure to populate item details for items populated in step 3.
Finally, the item details are transformed into a Agile format and written into an XML file.
To publish items from another organization, repeat steps 1 through 3. You can use the script in example listed here for loading batches of item data. Copy the script and edit as appropriate for your implementation:
Example 10-2 PL/SQL script to load batches of item data
DECLARE l_BATCH_SIZE NUMBER; l_MAX_EVENTS NUMBER; l_ERR_MSG VARCHAR2(200); p_org_id NUMBER; BEGIN l_BATCH_SIZE := 20; l_MAX_EVENTS := 50; l_ERR_MSG := NULL; p_org_id := 204; EGO_EBI_ITEM_LOAD.GENERATE_EVENTS(p_organization_id=> p_org_id, p_batch_size => l_BATCH_SIZE, p_max_events => l_MAX_EVENTS, X_ERR_MSG => l_ERR_MSG ); END;
This section describes the item batch load process. The item batch load process reads an XML file having an Agile schema as an input file and loads it into the Agile PLM by using the Agile web service. The following diagram shows the item batch load sequence.
The following steps lists the process involved in managing files when you perform an initial item batch load.
The item extract process generates XML files in a specific input folder in the SOA server. You can specify the folder name during the installation. Make sure that you have granted Read and Write permissions to this folder. In the item extract process, one XML file per Event ID gets generated and the file name contains an event identifier for that batch. For each event, a separate BPEL instance is invoked and can be seen in the enterprise manager (EM) console.
The item batch load process reads the XML files located in a specific input folder in the SOA server through a file adapter. For each XML file in the input folder, a separate BPEL instance is instantiated. The input folder name is configured during installation time. Make sure that you have granted Read and Write permissions to this folder.
The BPEL process, ProcessItemListInitialLoadAgileABF consumes the XML file, loads every item available in the XML file and then sends it to the Agile web service. The item data sent to Agile is in form of item application business message (ABM).
The processed XML file is then moved to the archived folder.
If an error occurs when one or more items in the input file are added or updated, then an error file is created which contains only erroneous item rows. The process continues with the remaining correct items in the file so that the processed file is archived. The BPEL process is marked as successful in the EM console. The folder paths for both error file and archived file are specified during the installation.
When a file is written into a folder that contains error files then it means that the file has one or more erroneous items. The XML error file with erroneous item rows must be corrected, and resubmitted, depending on the kind of error.
Note:
We strongly recommend you to have a separate folder for the output file generated from the item extract process and a separate folder for the input file for item loading process. This way, you can enhance the item data and also have a process streamlined for copying the files from the output folder of an item extract process into the input folder of an item loading process after the item enhancement process is completed.
The folder names are passed as design time parameters in the file adapter for the item extract and loading process. During installation, you can modify the folder names. The modified parameters are then added and the processes are deployed. Therefore, if you want to change these values later, then you have to customize the code.
The following table lists Oracle E-Business Suite web services: Inbound web services:
The following table lists Agile PLM web services: Inbound web services
Table 10-4 Inbound web services
| Name | Description |
|---|---|
|
Item Service:
|
Defines the item service and item schema and is used by ProcessItemListInitalLoadAgileABF service. |
|
Business Object Service:
|
Creates item object to be used by ProcessItemListInitialLoadEBizABF service |
The integration services for this integration are as follows:
ProcessEbizItemBatchLoadEventConsumer
ProcessItemListIntitalLoadAgileABF
ProcessItemListInitialLoadEbizABF
The ProcessEbizItembatchLoadEventConsumer is triggered when you run the PL/SQL item batch load script for extracting the EVENT_IDs based on the batch size, max batch limit and the ORG_ID and sends these EVENT_IDs to the ProcessItemListInitialLoadEbizABF.
The following table lists the Web Services Definition Language (WSDL) files used in this service:
The ProcessItemListInitialLoadEbizABF service sends the UpdateItemList XML file to the ProcessItemListInitialLoadAgileABF to synchronize the item data with Agile PLM. Based on the EVENT_ID it receives as a part of the batch size, the ProcessItemListInitialLoadEbizABF service queries the Oracle E-Business Suite DB for ORG_ID and INV_ITEM_ID details.
The ORG_ID and INV_ITEM_ID details are sent to a PL/SQL API to retrieve item data. The retrieved item data is then transformed into Agile format.
The following table lists the Web Services Definition Language (WSDL) files used in this service:
The following table lists the schema definition (XSD) files that are used in this service:
| Service | Interface | Description |
|---|---|---|
|
ProcessItemListInitialLoadEbizABF |
ProcessItemListInitialLoadEbizABF.xsd Query_EGO_EBI_ITEM_LOAD_DBAdaptor_table.xsd APPS_INV_EBI_ITEM_PUB_GET_ITEM_ATTRIBUTES.xsd |
Contains Event IDs details of the items extracted from Oracle E-Business Suite DB Contains ORG_ID, INVENTORY_ID and the EVENT_ID details. Used to extract item data from Oracle E-Business Suite DB. |
The following table lists the transformations for ProcessItemListInitialLoadEbizABF service:
| Transformation | Description |
|---|---|
|
Transform_QueryItemLoad_AppsAPI.xsl |
Used for converting PL/SQL query details into the PL/SQL API schema and extract item load details from Oracle E-Business Suite DB based on the ORG_ID,INV_ID and the EVEDNT_ID. |
|
Transform_APPS_To_ProcessItemListInitialLoadAgileFile.xsl |
Used for converting the data from the PL/SQL API to AgileUpdateItemList and write the item data into an XML file. |
The ProcessItemListInitialLoadAgileABF is BPEL service consumes the XML file generated by Oracle E-Business Suite service and then invokes the Agile web service. This service loads every item available in the XML file to the Agile PLM. After successfully loading the items, it populates ITEM_ITEMID XRef table.
While loading item data into Agile PLM, if an error occurs when one or more items in the input file are added or updated, then an error file is created which contains only erroneous item rows. The XML file with erroneous item rows must be corrected, and resubmitted, depending on the kind of error.
The following table lists the Web Services Definition Language (WSDL) files that are used in this service:
| Service | Interface | Description |
|---|---|---|
|
ProcessItemListIntitalLoadAgileABF |
ProcessItemListInitialLoadAgileFile.wsdl RuntimeFault.wsdl |
Reads the XML file by using the read operation of the file adapter. This is the input file for the ProcessItemListInitalLoadAgileABF service. Contains runtime fault details. |
The following table lists the schema definition (XSD) files that are used in this service:
| Service | Interface | Description |
|---|---|---|
|
ProcessItemListIntitalLoadAgileABF |
ItemABM.xsd MetaData.xsd bpel-common.xsd |
Contains the item details that need to be synchronized into Agile PLM. Contains core common elements such as fault details and so on. Contains parameters that are used in BPEL service. |
The following table lists the transformations for ProcessItemListIntitalLoadAgileABF service:
| Transformation | Description |
|---|---|
|
Xform_PopulateXrefs.xsl |
This transformation is used for populating the ITEM_ITEMID XRef table with the EBIZ ID, COMMON and AGILE ID columns. At first, the EBIZ and COMMON columns are populated and then based on the value of COMMON column the AGILE ID column is populated. If it is an update operation, then the lookupXref function is used to populate the values. |
|
Xform_PopulateXrefs_CreateObject.xsl |
This transformation is used to populate the ITEM_ITEMID cross reference table for the create operation flow.It populates the EBIZ,COMMON and AGILE columns. |
|
Xform_UpdateItemList_To_BusinessObjectSchema.xsl |
To create the items in Agile, this transformation converts the UpdateItemList to BusinessObjectSchema of the Agile web service. |
|
Xform_UpdateItemList_To_FailedMsgFileAdapter.xsl |
This transformation is used to write failed messages into the file adapter |
The following table lists the standard service-level AIA configuration service properties used for configuring application system instance identification, routing and other nonstandard properties used in this service:
Table 10-14 AIA service configuration properties
| Property | Default value | Description |
|---|---|---|
|
Default.SystemID |
AGILE_01 |
Agile Web Service Instance ID |
|
Source.SystemID |
EBIZ_01 |
EBS system instance ID |
|
Routing.ItemABSService.AGILE_01.EndpointURI |
http://${participatingapplications.agile.host}:${participatingapplications.agile.port}/${participatingapplications.agile.path}/integration/services/ItemABS |
EndpointURI of the Agile web service |
|
 Copyright © 2001, 2013, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|
