aia ordertocash productsync orgsetup and subscription
TRANSCRIPT
Abstract: The objective of this note is to guide Order to Cash PIP (Siebel CRM Integration Pack for Oracle Order Management) implementations in setting the organizations and event subscription appropriately for the Product Synchronization between Oracle E-Business Suite and Siebel CRM.
2
Table of Contents
A. Organization Data Setup for Product Synchronization ......................................................................... 3
Background ............................................................................................................................................... 3
Organization definitions and relationships in the participating Applications .......................................... 3
Oracle E-Business Suite ......................................................................................................................... 3
Siebel CRM ............................................................................................................................................ 4
AIA – Organization Mapping ................................................................................................................. 4
AIA – Inventory Location Mapping ....................................................................................................... 4
Order-to-Cash Product Synchronization Behavior .................................................................................... 5
Scenario #1: Each Operating Unit has a distinct (non-shared) Item Validation Org ............................ 6
Scenario #2: The Item Validation Org is shared across multiple Operating Units ............................... 8
Scenario #3: Item Master Org is the Item Validation Org, and shared across Operating Units ......... 10
B. Custom Subscription PL-SQL for Product Sync Events ........................................................................ 13
Background ............................................................................................................................................. 13
Recommendation .................................................................................................................................... 13
Steps to Register the PLSQL Subscription ............................................................................................... 13
PLSQL Code ............................................................................................................................................. 14
Table of Figures
Figure 1: Product Integration: Oracle E-Biz to Siebel Logical Data Map ....................................................... 5
3
A. Organization Data Setup for Product Synchronization
Background As part of business flow of the Order-to-Cash PIP, the primary objective of syncing Items from Oracle E-Business Suite (E-Biz) to Products in Siebel is to enable a CSR to place Order in Siebel for these products, and for the Order flow to submit this Order in Oracle E-Biz via Process Order API. For the Product Sync from Oracle E-Biz to Siebel to execute per design, certain entities need to be established appropriately in the participating applications.
Organization definitions and relationships in the participating Applications
Oracle E-Business Suite
“Operating Unit” is a logical organization within a company that the company management decides to operate. Order transactions are owned by the “Operating Unit” organizations. The transactions for an Operating Unit are restricted to using the reference data for that same operating unit. Viz. all the sales orders (transactional entity) are not only owned by the Operating Unit on the transaction side, but the reference data is also owned (viz. customer accounts) or associated(viz. Items). “Inventory Organizations” represent manufacturing and storage facilities. Each inventory organization belongs to one Parent Operating Unit. Oracle implements storage facilities, warehouses and distribution centers in inventory organizations. There can be many Inventory Organizations in Oracle E-Biz, and one of them is identified with the role of “Item Master Organization” (it is a best practice in Oracle E-Biz implementation to have only one Master Organization, though technically it may be possible to have more than one). Items are first setup and defined in this “Item Master Organization”. An Item Master Organization holds a single definition of items that can be shared across many inventory organizations. The unique key to identify an Item in E-Biz is Inventory Item Id, and Inventory Organization. In the Order Management module, there is a system parameter called “Item Validation Organization” defined at the operating level. This is the Inventory Organization used by Order Management to validate items when orders are placed against that Operating Unit. Thus when the Items are defined, they must be associated to this Inventory Organization. The “OE: Item Validation Organization” is also called “Item Validation Org”.
Since Master Organization is an Inventory Organization, it is possible that Master Organization can also be identified as Item Validation Org for an Operating Unit.
The same Inventory Organization can be the Item Validation Org for Multiple Operating Units.
The Operating Unit to Item Validation Org relationship is different from Parent Operating Unit to Inventory Org relationship. Though in certain implementations, the customers might simply choose to designate an Inventory Org as the Item Validation Org for an Operating Unit (OU), where this Operating Unit happens to be the Parent Operating Unit for this Inventory Org as well.
Only the Items that are associated with Inventory Organization which are also established as Item validation org are visible in the respective Operating Unit when an order is placed in the same.
4
Siebel CRM
The “Business Unit” Organization in Siebel allows the implementation company to partition itself into logical groups. Then the information associated the business unit can be displayed to the end users associated to that business unit (BU) only. The transaction data in Siebel (viz. Sales Order) is always associated to a business unit (the primary business unit). In Siebel, though an Order would be associated to a specific Business Unit, products from different business units can be associated on the Order lines. In other words, unlike Oracle E-Biz, the reference data for a transaction can belong to a different organization in Siebel. A Product in Siebel is always associated with a Business Unit, which becomes its Primary Business Unit. Multiple Business Units can be associated with a product as well. The unique key to product in Siebel is Product Name, Business Unit, and Vendor Account (optional - not mapped). “Inventory Locations” in Siebel are used to identify where products are stored, and the source from which the product will be fulfilled. An inventory location may be a warehouse, a field office, or it may be virtual location. An Inventory Location is also associated to a Business Unit. It should be noted that in Siebel there is no equivalent of E-Biz’s “Item Master Organization” or “Item Validation Organization”.
AIA – Organization Mapping
The concepts of Business Unit in Siebel, and Operating Unit in Oracle E-Biz map directly, and thus in the Order to Cash PIP, one-to-one mapping for them across the applications is assumed. An organization X-Ref mapping is setup as described in Chapter 13 of the Order-to-Cash implementation guide.
Siebel
Business Unit
Oracle E-Biz
Operating Unit
AIA
S_BU1 O_OU_1 AIA_1
S_BU2 O_OU_2 AIA_2
S_BU3 O_OU_3 AIA_3
It is possible that there could be many Operating Units defined in Oracle E-Biz, or multiple Business Units in Siebel. From an Order processing perspective only specific business units and operating units will need to be mapped in the X-Ref where Order capture would be done in Siebel, and corresponding fulfillment in Oracle E-Biz.
AIA – Inventory Location Mapping
There could be multiple Inventory Organizations in Oracle E-Biz for an Item. But from Order capture perspective, only those Inventory Organizations that are associated as Item Validation Organizations for the Item in Oracle E-Biz should be defined as Inventory Locations in Siebel.
5
The Business Unit of the Inventory Location being created in Siebel should be mapped to the Operating Unit of the Item Validation Org in E-Biz. In the Roll-up patch (RUP) # 9543544 for the AIA Order to Cash PIP 2.5, a new XREF column “SEBL_01_INVLOC_BU” has been added to the INVENTORY_LOCATION_ID XREF to capture the Primary BU in Siebel for a given Inventory Location. It is optional, and needs to be setup only if the Item Validation Organization is shared by multiple Operating Units. In such case(s) the primary Business Unit for the Inventory Location needs to be defined. Scenario #2 and #3 provided in this document illustrate this use case.
Figure 1: Product Integration: Oracle E-Biz to Siebel Logical Data Map
Order-to-Cash Product Synchronization Behavior An Item from Oracle E-Biz will be synced as Product in Siebel:
For every Item ID and Inventory Org combination in Oracle E-Biz only where Inventory Organization also happens to be an Item Validation Organization. Thus not all combinations of Item ID and Inventory Org ID from Oracle E-Biz will create a unique Product in Siebel.
The Siebel product’s Business Unit will correspond to the E-Biz Operating Unit that has the Inventory Organization as the Item Validation Organization.
If in E-Biz the Item is associated to multiple Operating Units by distinct (non-shared) Item Validation Orgs, then the Item records associated with the Item Validation Orgs in all these multiple Operating
6
Units will be synced, and X-refs created. Thus, in Siebel same product will be created for different Business Units individually.
Since the Item Validation Org could be shared across multiple Operating Units in E-Biz, thus all the corresponding Business units may have to be associated to the product in Siebel. It would depend on how product visibility is setup in Siebel. If Catalog based (Enterprise level) visibility is setup in Siebel, then associating additional Business Units may not be required. However, if Organization based visibility is setup in Siebel for products, then the additional non-primary (Multi-Org) business unit would have to be manually associated to the product.
If the Master Org is also the Item Validation Org, then those item records with the Master Org IDs will be synchronized, and X-Ref created. If the Master Org is not the Item Validation Org, then the item records with the master org are ignored for creating X-Ref.
Based on the above there can be various combinations of Organization setup and Item/Product definition across the two applications. In this document, few best practice scenarios are provided on how to setup the Organization X-Ref mappings -- and based on this setup how Product sync to Siebel will take place, and the Product X-Ref will be established is also described. (Note: it is possible that there may be other setup options that customers make that may not be supported, which may need customization.)
Scenario #1: Each Operating Unit has a distinct (non-shared) Item Validation Org
The scenario description is as following: a) Only one Master Organization exists in E-Biz. All Items are defined in this Master Org. b) Each Operating Unit is associated with a distinct Item Validation Organization. In other words, item
Validation Org is not shared across Operating Units. c) The Item Master Organization is not associated as the Item Validation Org to any of the Operating
Units in which an Order can be placed. d) The Parent Operating Unit of the Inventory Organization may (or may not) be an Operating Unit in
which Orders can be placed. Operating Units (OU) in Oracle E-Biz
Name ID Order Capture Operating Unit
OE Item Validation Org
Comment
1 United States OU 504 No n/a
2 Purple OU 505 Yes Beta IO
3 Red OU 506 Yes Gamma IO
4 Yellow OU 507 Yes Delta IO
5 White OU 509 No n/a
6 Orange OU 510 No n/a
7 Green OU 511 No n/a
8 Blue OU 512 No n/a
7
Inventory Organizations (IO) in Oracle E-Biz
Name ID Parent Operating Unit
Item Master Org
Does IO serve as Item Validation Org?
1 Alpha IO (Item Master Org)
750 United States OU Yes No
2 Beta IO 751 White OU No Yes
3 Gamma IO 752 Orange OU No Yes
4 Delta IO 753 Green OU No Yes
5 Phi IO 754 Blue OU No No
6 Kappa IO 755 Purple OU No No
Business Units (BU) in Siebel
Name ROW_ID Used for Order Capture
Comment
Purple BU 1-AC Yes
Red BU 1-AD Yes
Yellow BU 1-AE Yes
Brown BU 1-AG No
Inventory Locations (IL) in Siebel (The Item Validation Orgs from E-Biz)
Name ID Primary Business Unit
Comment
Beta IL 1-XB Purple BU
Gamma IL 1-XC Red BU
Delta IL 1-XD Yellow BU
AIA ORGANIZATION_ID X-Ref
Oracle E-Biz ID Common Siebel ID Comment
505 (Purple OU) C1 1-AC (Purple BU)
506 (Red OU) C2 1-AD (Red BU)
507 (Yellow OU) C3 1-AE (Yellow BU) Note: The values in parenthesis are just for explanation, they do not go into X-Ref.
AIA INVENTORY_LOCATION_ID X-Ref
Oracle E-Biz ID Common Siebel ID Comment
751 (Beta IL) C51 1-XB (Beta IL)
752 (Gamma IL) C52 1-XC (Gamma IL)
753 (Delta IL) C53 1-XD (Delta IL) Note: The values in parenthesis are just for explanation, they do not go into X-Ref.
Based on above Organization setup, the E-Biz Item will be synced in the following manner as Siebel Product.
8
Item Definition in Oracle E-Biz
Item Name Item ID Inv Org ID Parent OU Comment
Item A 10 750 (Alpha IO) 504 (US OU)
Item A 10 751 (Beta IO) 509 (White OU)
Item A 10 752 (Gamma IO) 510 (Orange OU)
Item A 10 753 (Phi IO) 512 (Blue OU)
Item B 20 751 (Beta IO) 509 (White OU)
Item B 20 755 (Kappa IO) 505 (Purple OU)
Product Definition in Siebel
Product Name Row ID Primary Business Unit ID
Secondary Business Unit ID
Comment
Item A 1-PD1 1-AC (Purple BU) n/a
Item A 1-PD2 1-AD (Red BU) n/a
Item B 1-PD3 1-AC (Purple BU) n/a
The AIA X-Ref will contain the following information for this Product Synchronization scenario:
Type Siebel ID E-Biz ID Common
ITEM_ITEMID 1-PD1 10::751::509 C101
ITEM_ITEMID 1-PD2 10::752::510 C102
ITEM_ITEMID 1-PD3 20::751:509 C103
Scenario #2: The Item Validation Org is shared across multiple Operating Units
The scenario description is as following: a) Only one Master Organization in E-Biz. All Items are defined in this Master Org. b) The same ‘Inventory Organization’ is specified as the ‘Item Validation Organization’ in the profile
option across multiple Order capturing Operating Units. i. It implies that there would be single corresponding ‘Inventory Location’ created in Siebel.
However, the Business Unit to be specified for this single Inventory Location in Siebel could be corresponding to any of the Operating Units (for that Item Validation Org).
ii. Whichever Business Unit is chosen to be specified for the Inventory Location, it would become the ‘Primary Business Unit’ on the Product created on synchronization.
iii. All other related Business Units for the given Product will have to be manually associated within Siebel Product Definition. This might be only necessary if Organization based visibility is setup in Siebel for the users (if default Catalog based visibility is used, associating the other Business Units manually may not be required).
c) The Master organization could be this Item validation Org across multiple Operating Units in which order can be placed.
d) The Parent OU associated with Inventory Organization also may (or may not) be the Operating Unit against which Orders are going to be placed
9
Operating Units (OU) in Oracle E-Biz
Name ID Order Capture Operating Unit
OE Item Validation Org
Comment
1 United States OU 504 No n/a
2 Purple OU 505 Yes Beta IO
3 Red OU 506 Yes Beta IO
4 Yellow OU 507 Yes Beta IO
5 White OU 509 No n/a
6 Orange OU 510 No n/a
7 Green OU 511 No n/a
8 Blue OU 512 No n/a
Inventory Organizations (IO) in Oracle E-Biz
Name ID Parent Operating Unit
Item Master Org
Does IO serve as Item Validation Org?
1 Alpha IO (Item Master Org)
750 United States OU Yes No
2 Beta IO 751 White OU No Yes
3 Gamma IO 752 Orange OU No No
4 Delta IO 753 Green OU No No
5 Phi IO 754 Blue OU No No
6 Kappa IO 755 Purple OU No No
Business Units (BU) in Siebel
Name ROW_ID Used for Order Capture
Comment
Purple BU 1-AC Yes
Red BU 1-AD Yes
Yellow BU 1-AE Yes
Brown BU 1-AG No
Inventory Locations (IL) in Siebel (The Item Validation Orgs from E-Biz)
Name ID Primary Business Unit
Comment
Beta IL 1-XB Purple BU
AIA ORGANIZATION_ID X-Ref
Oracle E-Biz ID Common Siebel ID Comment
505 (Purple OU) C1 1-AC (Purple BU)
506 (Red OU) C2 1-AD (Red BU)
507 (Yellow OU) C3 1-AE (Yellow BU) Note: The values in parenthesis are just for explanation, they do not go into X-Ref.
10
AIA INVENTORY_LOCATION_ID X-Ref
Oracle E-Biz ID Common SEBL_01 SEBL01_INVLOC_BU
751 (Beta IL) C51 1-XB (Beta IL) 1-AC (Purple BU)
Note: The values in parenthesis are just for explanation, they do not go into X-Ref.
Based on above Organization setup, the Item will be synced in the following manner to Siebel Product. Item Definition in Oracle E-Biz
Item Name Item ID Inv Org ID Parent OU Comment
Item A 10 750 (Alpha IO) 504 (US OU)
Item A 10 751 (Beta IO) 509 (White OU)
Item A 10 752 (Gamma IO) 510 (Orange OU)
Item A 10 753 (Phi IO) 512 (Blue OU)
Item B 20 750 (Alpha IO) 504 (US OU)
Item B 20 751 (Beta IO) 509 (White OU)
Item B 20 755 (Kappa IO) 505 (Purple OU)
Product Definition in Siebel
Product Name Row ID
Primary Business Unit ID
Non-primary Business Unit ID (optionally)
Comment
Item A 1-PD1 1-AC (Purple BU) 1-AD (Red BU),
1-AE (Yellow BU)
Item B 1-PD3 1-AC (Purple BU) 1-AD (Red BU),
1-AE (Yellow BU)
If required, the non-primary Business Unit (multi-org) would have to be manually setup for the product in Siebel. The AIA X-Ref will contain the following information for this Product Synchronization scenario:
Type Siebel ID E-Biz ID Common
ITEM_ITEMID 1-PD1 10::751::509 C101
ITEM_ITEMID 1-PD3 20::751::509 C103
Scenario #3: Item Master Org is the Item Validation Org, and shared across Operating Units
The scenario description is as following: a) Only one Item Master Organization in E-Biz. All Items are defined in this Master Org. b) The Item Master Organization also serves as the ‘Item Validation Organization’ in the profile option
across multiple Order capturing Operating Units. i. It implies that there would be single corresponding ‘Inventory Location’ created in Siebel.
However, the Business Unit to be specified for this single Inventory Location in Siebel could be corresponding to any of the Operating Units (for that Item Validation Org).
ii. Whichever Business Unit is chosen to be specified for the Inventory Location, it would become the ‘Primary Business Unit’ on the Product created on synchronization.
iii. All other related Business Units for the given Product will have to be manually associated within Siebel Product Definition. This might be only necessary if Organization based visibility
11
is setup in Siebel for the users (if default Catalog based visibility is used, associating the other Business Units manually may not be required).
c) The Parent OU associated with Inventory Organization also may (or may not) be the Operating Unit against which Orders are going to be placed
Operating Units (OU) in Oracle E-Biz
Name ID Order Capture Operating Unit
OE Item Validation Org
Comment
1 United States OU 504 No n/a
2 Purple OU 505 Yes Alpha IO
3 Red OU 506 Yes Alpha IO
4 Yellow OU 507 Yes Alpha IO
5 White OU 509 No n/a
6 Orange OU 510 No n/a
7 Green OU 511 No n/a
8 Blue OU 512 No n/a
Inventory Organizations (IO) in Oracle E-Biz
Name ID Parent Operating Unit
Item Master Org
Does IO serve as Item Validation Org?
1 Alpha IO (Item Master Org)
750 United States OU Yes Yes
2 Beta IO 751 White OU No No
3 Gamma IO 752 Orange OU No No
4 Delta IO 753 Green OU No No
5 Phi IO 754 Blue OU No No
6 Kappa IO 755 Purple OU No No
Business Units (BU) in Siebel
Name ROW_ID Used for Order Capture
Comment
Purple BU 1-AC Yes
Red BU 1-AD Yes
Yellow BU 1-AE Yes
Brown BU 1-AG No
Inventory Locations (IL) in Siebel (The Item Validation Orgs from E-Biz)
Name ID Primary Business Unit
Comment
Alpha IL 1-XM Purple BU
12
AIA ORGANIZATION_ID X-Ref
Oracle E-Biz ID Common Siebel ID Comment
505 (Purple OU) C1 1-AC (Purple BU)
506 (Red OU) C2 1-AD (Red BU)
507 (Yellow OU) C3 1-AE (Yellow BU) Note: The values in parenthesis are just for explanation, they do not go into X-Ref.
AIA INVENTORY_LOCATION_ID X-Ref
Oracle E-Biz ID Common Siebel ID SEBL01_INVLOC_BU
750 (Alpha IL) C51 1-XM (Alpha IL) 1-AC (Purple BU)
Note: The values in parenthesis are just for explanation, they do not go into X-Ref.
Based on above Organization setup, the Item will be synced in the following manner to Siebel Product. Item Definition in Oracle E-Biz
Item Name Item ID Inv Org ID Parent OU Comment
Item A 10 750 (Alpha IO) 504 (US OU)
Item A 10 751 (Beta IO) 509 (White OU)
Item A 10 752 (Gamma IO) 510 (Orange OU)
Item A 10 753 (Phi IO) 512 (Blue OU)
Item B 20 750 (Alpha IO) 504 (US OU)
Item B 20 751 (Beta IO) 509 (White OU)
Item B 20 755 (Kappa IO) 505 (Purple OU)
Product Definition in Siebel
Product Name Row ID
Primary Business Unit ID
Secondary Business Unit ID (optionally)
Comment
Item A 1-PD1 1-AC (Purple BU) 1-AD (Red BU),
1-AE (Yellow BU)
Item B 1-PD3 1-AC (Purple BU) 1-AD (Red BU),
1-AE (Yellow BU)
If required, the non-primary Business Unit (multi-org) would have to be manually setup for the product in Siebel. The AIA X-Ref will contain the following information for this Product Synchronization scenario:
Type Siebel ID E-Biz ID Common
ITEM_ITEMID 1-PD1 10::750::504 C101
ITEM_ITEMID 1-PD3 20::750::504 C103
13
B. Custom Subscription PL-SQL for Product Sync Events
Background In Oracle E-Business Suite all Items are created in Master Organization, and assigned to Inventory
Organizations from the Item Master Org. Out of the box there are default subscriptions created for two
events raised for item create and update, that are subscribed to by the Product Sync flow in the PIP:
“oracle.apps.ego.item.postItemUpdate”, and “oracle.apps.ego.item.postItemCreate”.
These subscriptions will trigger the item sync flow for every item created or updated in any Inventory
Organization. The Product Sync flow in the PIP will only sync those Items to Siebel for Inventory Orgs
that are defined in the INVENTORY_LOCATIONS XREF table.
While the out of the box subscriptions will work fine for most of the scenarios, there are two issues with
it for which we recommend setting up a more optimal custom subscription:
1) As seen in the Organization setup section, in an Order-to-Cash PIP implementation not all the
Inventory Organizations are needed to be setup in Siebel. Thus, subscription to events relating to
the “Non Item Validation Orgs” is not necessary for the Order-to-Cash implementation.
2) Secondly, most of the Item attributes are managed at the Item Master Organization level, and
certain ones at Inventory Org level only. When updates are made to master org attributes, the
propagation of those updates to items in inventory orgs that have been synched to Siebel will not
work, when the item validation org is not a master org.
Recommendation
To resolve these two issues, it is recommended that the implementers create a custom subscription to
the item related events in Oracle E-Biz. A sample PLSQL subscription that will address the above two
issues is described in this section.
The behavior of the custom subscription is as following:
If the Inventory Organization is an “Item Validation Org” then the event is sent to the queue, and
If the “Inventory Org” is “Master Org”, then event are sent to the outbound queue for each Item
Validation Org.
Else, the event is ignored.
Steps to Register the PLSQL Subscription
1. Review the sample PLSQL code below for the package: “xx_custom_event”
In that, replace the variables “l_master_organization_id” to the ID of your master organization,
and “l_itemValidationOrgs” with the list of your item validations organizations.
2. Compile the PLSQL package in the APPS schema of the E-business Suite instance.
14
3. Register the above PLSQL function as the subscription rule function for the events
“oracle.apps.ego.item.postItemUpdate” and “oracle.apps.ego.item.postItemCreate”. The steps
for it are as follows:
a. Login to Oracle Applications, and choose the “Workflow Administrator Web
Application” responsibility.
b. Navigate to “Administrator Workflow” -> “Business Events”.
c. Search for the event “oracle.apps.ego.item.postItemCreate”, and click on the
“Subscription” icon from the results table.
d. You should see a subscription with the following values:
Agent: WF_BPEL_QAGENT@<instance_name>
Function: WF_RULE.DEFAULT_RULE
e. Edit it, and change the rule-function to: xx_custom_event.xx_item_event.
f. Repeat the steps ‘c’, ‘d’, and ‘e’ for the event “oracle.apps.ego.item.postItemUpdate” as
well.
PLSQL Code
create or replace PACKAGE xx_custom_event AS
FUNCTION xx_item_event(
p_subscription_guid in raw
,p_eventP in out NOCOPY wf_event_t) RETURN VARCHAR2;
END xx_custom_event;
/
create or replace PACKAGE BODY xx_custom_event AS
FUNCTION xx_item_event(
p_subscription_guid in raw
,p_eventP in out NOCOPY wf_event_t) return varchar2
IS
x_event_parameter_list wf_parameter_list_t;
x_event_src_parameter_list wf_parameter_list_t;
x_param wf_parameter_t;
x_index NUMBER := 0;
TYPE NumberList is TABLE of NUMBER(15) INDEX BY BINARY_INTEGER;
l_InvId NUMBER;
l_OrgId NUMBER;
l_InvIdExist NUMBER := NULL;
l_OrgCode VARCHAR2(800);
l_InvItemDesc VARCHAR2(800);
l_return_status VARCHAR2(240);
l_param_name VARCHAR2(240);
l_param_value VARCHAR2(2000);
l_master_organization_id NUMBER;
l_itemValidationOrgs NumberList;
BEGIN
-- REPLACE THIS. List of Item validation Orgs which needs event consideration.
l_itemValidationOrgs(1):=911; -- Visions operations
l_itemValidationOrgs(2):=207; -- Seattle manufacturing
-- REPLACE THIS. Id of Master Organization
l_master_organization_id:=204;
l_InvId :=
to_number(wf_event.getValueForParameter('INVENTORY_ITEM_ID',p_eventP.Parameter_List));
15
l_OrgId :=
to_number(wf_event.getValueForParameter('ORGANIZATION_ID',p_eventP.Parameter_List));
l_OrgCode :=
wf_event.getValueForParameter('ORGANIZATION_CODE',p_eventP.Parameter_List);
l_InvItemDesc :=
wf_event.getValueForParameter('ITEM_DESCRIPTION',p_eventP.Parameter_List);
-- If the Organization Id in the event for the product is same as Master Organization
raise an event for every Item Validation Org
x_event_src_parameter_list := p_eventP.GETPARAMETERLIST();
IF l_OrgId=l_master_organization_id THEN
-- Raise Event for each itemValidationOrg
FOR I in 1..l_itemValidationOrgs.COUNT LOOP
x_index:=0;
x_event_parameter_list := wf_parameter_list_t();
select inventory_item_id into l_InvIdExist from ego_item_sync_v ego where
inventory_item_id=l_InvId and organization_id=l_itemValidationOrgs(I);
IF l_InvIdExist IS NOT NULL THEN
FOR J IN x_event_src_parameter_list.FIRST..x_event_src_parameter_list.LAST
LOOP
l_param_name := x_event_src_parameter_list(J).getname;
l_param_value := x_event_src_parameter_list(J).getvalue;
x_param := wf_parameter_t(NULL,NULL);
x_event_parameter_list.EXTEND;
IF ((l_param_name = 'INVENTORY_ITEM_ID') ) THEN
x_param.setname('INVENTORY_ITEM_ID');
x_param.setvalue(l_InvId);
ELSIF ((l_param_name = 'ORGANIZATION_ID') ) THEN
x_param.setname('ORGANIZATION_ID');
x_param.setvalue(l_itemValidationOrgs(I));
ELSIF ((l_param_name = 'ITEM_DESCRIPTION') ) THEN
x_param.setname('ITEM_DESCRIPTION');
x_param.setvalue(l_InvItemDesc);
ELSIF ((l_param_name = 'ORGANIZATION_CODE') ) THEN
x_param.setname('ORGANIZATION_CODE');
x_param.setvalue(l_OrgCode);
ELSE x_param.setname(l_param_name);
x_param.setvalue(l_param_value);
END IF;
x_index := x_index + 1;
x_event_parameter_list(x_index) := x_param;
END LOOP;
p_eventP.setParameterList(x_event_parameter_list);
l_return_status:=WF_RULE.DEFAULT_RULE(p_subscription_guid=>p_subscription_guid,p_event=>p_eve
ntP);
COMMIT;
END IF;
END LOOP;
ELSE
-- Check if the Org id in the Event is Item Validation Org (If found in the list of
Item Validation Org), raise an event for the Org.
FOR I in 1..l_itemValidationOrgs.COUNT LOOP -- Iterate through the above declared
list of Item Validation Organizations
IF l_OrgId = l_itemValidationOrgs(I) THEN
x_index:=0;
x_event_parameter_list := wf_parameter_list_t();
select inventory_item_id into l_InvIdExist from ego_item_sync_v ego where
inventory_item_id=l_InvId and organization_id=l_itemValidationOrgs(I);
IF l_InvIdExist IS NOT NULL THEN
FOR J IN
x_event_src_parameter_list.FIRST..x_event_src_parameter_list.LAST LOOP
l_param_name := x_event_src_parameter_list(J).getname;
16
l_param_value := x_event_src_parameter_list(J).getvalue;
x_param := wf_parameter_t(NULL,NULL);
x_event_parameter_list.EXTEND;
IF ((l_param_name = 'INVENTORY_ITEM_ID') ) THEN
x_param.setname('INVENTORY_ITEM_ID');
x_param.setvalue(l_InvId);
ELSIF ((l_param_name = 'ORGANIZATION_ID') ) THEN
x_param.setname('ORGANIZATION_ID');
x_param.setvalue(l_itemValidationOrgs(I));
ELSIF ((l_param_name = 'ITEM_DESCRIPTION') ) THEN
x_param.setname('ITEM_DESCRIPTION');
x_param.setvalue(l_InvItemDesc);
ELSIF ((l_param_name = 'ORGANIZATION_CODE') ) THEN
x_param.setname('ORGANIZATION_CODE');
x_param.setvalue(l_OrgCode);
ELSE x_param.setname(l_param_name);
x_param.setvalue(l_param_value);
END IF;
x_index := x_index + 1;
x_event_parameter_list(x_index) := x_param;
END LOOP;
p_eventP.setParameterList(x_event_parameter_list);
l_return_status:=WF_RULE.DEFAULT_RULE(p_subscription_guid=>p_subscription_guid,p_event=>p_eve
ntP);
COMMIT;
END IF;
END IF;
END LOOP;
END IF;
rETURN l_return_status;
END xx_item_event;
END xx_custom_event;