[Home] [Help]
PACKAGE BODY: APPS.EGO_WF_WRAPPER_PVT
Source
1 PACKAGE BODY EGO_WF_WRAPPER_PVT AS
2 /* $Header: EGOVWFWB.pls 120.15.12020000.2 2012/07/13 01:42:40 mshirkol ship $ */
3
4
5
6 ------------------------------
7 -- Private Global Variables --
8 ------------------------------
9
10 G_PKG_NAME CONSTANT VARCHAR2(30) := 'EGO_WF_WRAPPER_PVT';--R12C Batch Level Business Event switches
11 --R12C Entity level Business events switch
12 G_ITEM_BULKLOAD_BUS_EVENT VARCHAR2(1) := FND_API.G_TRUE;
13 G_REV_CHANGE_BUS_EVENT VARCHAR2(1) := FND_API.G_TRUE;
14 G_CAT_ASSIGN_BUS_EVENT VARCHAR2(1) := FND_API.G_TRUE;
15 G_POST_ATTR_BUS_EVENT VARCHAR2(1) := FND_API.G_TRUE;
16 G_POST_AML_BUS_EVENT VARCHAR2(1) := FND_API.G_TRUE;
17 G_ITEM_PEOPLE_BUS_EVENT VARCHAR2(1) := FND_API.G_TRUE;
18
19 ----------------------------------------------------------------------
20
21
22 -----------------------
23 -- Public Procedures --
24 -----------------------
25
26
27 ----------------------------------------------------------------------
28
29
30
31 PROCEDURE Raise_WF_Business_Event (
32 p_event_name IN VARCHAR2
33 ,p_event_key IN VARCHAR2
34 ,p_pre_event_flag IN VARCHAR2 DEFAULT NULL --Not null when used to raise pre event
35 ,p_request_id IN VARCHAR2 DEFAULT NULL
36 ,p_dml_type IN VARCHAR2 DEFAULT NULL
37 ,p_attr_group_name IN VARCHAR2 DEFAULT NULL
38 ,p_extension_id IN NUMBER DEFAULT NULL
39 ,p_primary_key_1_col_name IN VARCHAR2 DEFAULT NULL
40 ,p_primary_key_1_value IN VARCHAR2 DEFAULT NULL
41 ,p_primary_key_2_col_name IN VARCHAR2 DEFAULT NULL
42 ,p_primary_key_2_value IN VARCHAR2 DEFAULT NULL
43 ,p_primary_key_3_col_name IN VARCHAR2 DEFAULT NULL
44 ,p_primary_key_3_value IN VARCHAR2 DEFAULT NULL
45 ,p_primary_key_4_col_name IN VARCHAR2 DEFAULT NULL
46 ,p_primary_key_4_value IN VARCHAR2 DEFAULT NULL
47 ,p_primary_key_5_col_name IN VARCHAR2 DEFAULT NULL
48 ,p_primary_key_5_value IN VARCHAR2 DEFAULT NULL
49 ,p_data_level_id IN NUMBER DEFAULT NULL
50 ,p_data_level_1_col_name IN VARCHAR2 DEFAULT NULL
51 ,p_data_level_1_value IN VARCHAR2 DEFAULT NULL
52 ,p_data_level_2_col_name IN VARCHAR2 DEFAULT NULL
53 ,p_data_level_2_value IN VARCHAR2 DEFAULT NULL
54 ,p_data_level_3_col_name IN VARCHAR2 DEFAULT NULL
55 ,p_data_level_3_value IN VARCHAR2 DEFAULT NULL
56 ,p_data_level_4_col_name IN VARCHAR2 DEFAULT NULL
57 ,p_data_level_4_value IN VARCHAR2 DEFAULT NULL
58 ,p_data_level_5_col_name IN VARCHAR2 DEFAULT NULL
59 ,p_data_level_5_value IN VARCHAR2 DEFAULT NULL
60 ,p_user_row_identifier IN VARCHAR2 DEFAULT NULL
61 ,p_attr_name_val_tbl IN EGO_ATTR_TABLE DEFAULT NULL
62 ,p_entity_id IN VARCHAR2 DEFAULT NULL
63 ,p_entity_index IN NUMBER DEFAULT NULL
64 ,p_entity_code IN VARCHAR2 DEFAULT NULL
65 ,p_add_errors_to_fnd_stack IN VARCHAR2 DEFAULT NULL
66 ) IS
67
68 l_api_name CONSTANT VARCHAR2(30) := 'Raise_WF_Business_Event';
69
70 l_parameter_list WF_PARAMETER_LIST_T := WF_PARAMETER_LIST_T();
71 l_parameter_t WF_PARAMETER_T:= WF_PARAMETER_T(null, null);
72 l_index NUMBER;
73
74 BEGIN
75 IF p_request_id IS NOT NULL THEN --Not null in case of PostAttributeChange event from Bulk Load
76 l_parameter_t.SetName('REQUEST_ID');
77 l_parameter_t.SetValue(p_request_id);
78 l_parameter_list.EXTEND();
79 l_parameter_list(l_parameter_list.LAST()) := l_parameter_t;
80 ELSE
81 ------------------------------------------------------------
82 -- We start our parameter list with the basic information --
83 ------------------------------------------------------------
84 l_parameter_t.SetName('DML_TYPE');
85 l_parameter_t.SetValue(p_dml_type);
86 l_parameter_list.EXTEND();
87 l_parameter_list(l_parameter_list.LAST()) := l_parameter_t;
88 l_parameter_t.SetName('ATTR_GROUP_NAME');
89 l_parameter_t.SetValue(p_attr_group_name);
90 l_parameter_list.EXTEND();
91 l_parameter_list(l_parameter_list.LAST()) := l_parameter_t;
92
93 l_parameter_t.SetName('DATA_LEVEL_ID');
94 l_parameter_t.SetValue(p_data_level_id);
95 l_parameter_list.EXTEND();
96 l_parameter_list(l_parameter_list.LAST()) := l_parameter_t;
97
98 l_parameter_t.SetName('EXTENSION_ID');
99 IF p_pre_event_flag IS NOT NULL AND p_dml_type='CREATE' THEN --while raising preAttrbuteChange for CREATE
100 l_parameter_t.SetValue(null);
101 ELSE
102 l_parameter_t.SetValue(p_extension_id);
103 END IF;
104
105 l_parameter_list.EXTEND();
106 l_parameter_list(l_parameter_list.LAST()) := l_parameter_t;
107
108 ---------------------------------------------------------------
109 -- We add as many Primary Key names/values as were passed... --
110 ---------------------------------------------------------------
111 IF (p_primary_key_1_col_name IS NOT NULL) THEN
112
113 l_parameter_t.SetName(p_primary_key_1_col_name);
114 l_parameter_t.SetValue(p_primary_key_1_value);
115 l_parameter_list.EXTEND();
116 l_parameter_list(l_parameter_list.LAST()) := l_parameter_t;
117
118 IF (p_primary_key_2_col_name IS NOT NULL) THEN
119
120 l_parameter_t.SetName(p_primary_key_2_col_name);
121 l_parameter_t.SetValue(p_primary_key_2_value);
122 l_parameter_list.EXTEND();
123 l_parameter_list(l_parameter_list.LAST()) := l_parameter_t;
124
125 IF (p_primary_key_3_col_name IS NOT NULL) THEN
126
127 l_parameter_t.SetName(p_primary_key_3_col_name);
128 l_parameter_t.SetValue(p_primary_key_3_value);
129 l_parameter_list.EXTEND();
130 l_parameter_list(l_parameter_list.LAST()) := l_parameter_t;
131
132 IF (p_primary_key_4_col_name IS NOT NULL) THEN
133
134 l_parameter_t.SetName(p_primary_key_4_col_name);
135 l_parameter_t.SetValue(p_primary_key_4_value);
136 l_parameter_list.EXTEND();
137 l_parameter_list(l_parameter_list.LAST()) := l_parameter_t;
138
139 IF (p_primary_key_5_col_name IS NOT NULL) THEN
140
141 l_parameter_t.SetName(p_primary_key_5_col_name);
142 l_parameter_t.SetValue(p_primary_key_5_value);
143 l_parameter_list.EXTEND();
144 l_parameter_list(l_parameter_list.LAST()) := l_parameter_t;
145
146 END IF;
147 END IF;
148 END IF;
149 END IF;
150 END IF;
151
152 --------------------------------------------------------
153 -- ... and we do the same for Data Level names/values --
154 --------------------------------------------------------
155 IF (p_data_level_1_col_name IS NOT NULL) THEN
156
157 l_parameter_t.SetName(p_data_level_1_col_name);
158 l_parameter_t.SetValue(p_data_level_1_value);
159 l_parameter_list.EXTEND();
160 l_parameter_list(l_parameter_list.LAST()) := l_parameter_t;
161
162 IF (p_data_level_2_col_name IS NOT NULL) THEN
163
164 l_parameter_t.SetName(p_data_level_2_col_name);
165 l_parameter_t.SetValue(p_data_level_2_value);
166 l_parameter_list.EXTEND();
167 l_parameter_list(l_parameter_list.LAST()) := l_parameter_t;
168
169 IF (p_data_level_3_col_name IS NOT NULL) THEN
170
171 l_parameter_t.SetName(p_data_level_3_col_name);
172 l_parameter_t.SetValue(p_data_level_3_value);
173 l_parameter_list.EXTEND();
174 l_parameter_list(l_parameter_list.LAST()) := l_parameter_t;
175
176 IF (p_data_level_4_col_name IS NOT NULL) THEN
177
178 l_parameter_t.SetName(p_data_level_4_col_name);
179 l_parameter_t.SetValue(p_data_level_4_value);
180 l_parameter_list.EXTEND();
181 l_parameter_list(l_parameter_list.LAST()) := l_parameter_t;
182
183 IF (p_data_level_5_col_name IS NOT NULL) THEN
184
185 l_parameter_t.SetName(p_data_level_5_col_name);
186 l_parameter_t.SetValue(p_data_level_5_value);
187 l_parameter_list.EXTEND();
188 l_parameter_list(l_parameter_list.LAST()) := l_parameter_t;
189
190 END IF;
191 END IF;
192 END IF;
193 END IF;
194 END IF;
195
196 IF p_pre_event_flag IS NOT NULL AND --in case of pre event
197 p_attr_name_val_tbl.COUNT > 0 THEN
198 l_index := p_attr_name_val_tbl.FIRST;
199 WHILE l_index <= p_attr_name_val_tbl.LAST LOOP
200 l_parameter_t.SetName(p_attr_name_val_tbl(l_index).attr_name);
201 l_parameter_t.SetValue(p_attr_name_val_tbl(l_index).attr_value);
202 l_parameter_list.EXTEND();
203 l_parameter_list(l_parameter_list.LAST()) := l_parameter_t;
204 l_index := p_attr_name_val_tbl.NEXT(l_index);
205 END LOOP;
206 END IF;
207
208 END IF; --Request_ID
209 BEGIN
210
211 WF_EVENT.Raise(p_event_name => p_event_name
212 ,p_event_key => p_event_key
213 ,p_parameters => l_parameter_list);
214 EXCEPTION
215 WHEN OTHERS THEN
216 --if pre event only then raise the user defined exception, put SQLERRM in to the stack
217 -- Bug 6376745 #commenting out the below check
218 -- IF p_pre_event_flag IS NOT NULL THEN
219 --for pre event only we expect the user to have a Synchronous subscriptions (phase 0-99)
220 DECLARE
221 l_token_table ERROR_HANDLER.Token_Tbl_Type;
222 BEGIN
223 -- Uncommenting the below block as part of fix
224 l_token_table(1).TOKEN_NAME := 'PKG_NAME';
225 l_token_table(1).TOKEN_VALUE := G_PKG_NAME;
226 l_token_table(2).TOKEN_NAME := 'API_NAME';
227 l_token_table(2).TOKEN_VALUE := l_api_name;
228 l_token_table(3).TOKEN_NAME := 'SQL_ERR_MSG';
229 l_token_table(3).TOKEN_VALUE := SQLERRM;
230
231 ERROR_HANDLER.Add_Error_Message(
232 p_message_name => 'EGO_EVENT_SUBSCR'
233 ,p_application_id => 'EGO'
234 --,p_token_tbl => l_token_table ---parameters Commented for bug 6518941
235 ,p_message_type => FND_API.G_RET_STS_ERROR
236 --,p_row_identifier => p_user_row_identifier
237 --,p_entity_id => p_entity_id
238 --,p_entity_index => p_entity_index
239 --,p_entity_code => p_entity_code
240 ,p_addto_fnd_stack => 'Y' --bug 14095844, we should add the error to stack
241 );
242 raise EGO_USER_ATTRS_COMMON_PVT.G_SUBSCRIPTION_EXC;
243 END;
244 --ELSE --for post event do the default handling
245 -- raise;
246 --END IF;
247 END;
248
249 EXCEPTION
250 -----------------------------------------------------------
251 ---Exception raised by Subscription
252 -----------------------------------------------------------
253 WHEN EGO_USER_ATTRS_COMMON_PVT.G_SUBSCRIPTION_EXC THEN
254 raise EGO_USER_ATTRS_COMMON_PVT.G_SUBSCRIPTION_EXC;
255
256 -----------------------------------------------------------
257 ---for unexpected exceptions-
258 -----------------------------------------------------------
259 WHEN OTHERS THEN
260 DECLARE
261 l_token_table ERROR_HANDLER.Token_Tbl_Type;
262 BEGIN
263 l_token_table(1).TOKEN_NAME := 'PKG_NAME';
264 l_token_table(1).TOKEN_VALUE := G_PKG_NAME;
265 l_token_table(2).TOKEN_NAME := 'API_NAME';
266 l_token_table(2).TOKEN_VALUE := l_api_name;
267 l_token_table(3).TOKEN_NAME := 'SQL_ERR_MSG';
268 l_token_table(3).TOKEN_VALUE := SQLERRM;
269
270 ERROR_HANDLER.Add_Error_Message(
271 p_message_name => 'EGO_PLSQL_ERR'
272 ,p_application_id => 'EGO'
273 ,p_token_tbl => l_token_table
274 ,p_message_type => FND_API.G_RET_STS_ERROR
275 ,p_row_identifier => p_user_row_identifier
276 ,p_entity_id => p_entity_id
277 ,p_entity_index => p_entity_index
278 ,p_entity_code => p_entity_code
279 ,p_addto_fnd_stack => p_add_errors_to_fnd_stack
280 );
281 END;
282
283 END Raise_WF_Business_Event;
284
285
286 /*-------------------------------------------------------
287 Requirement : To riase business event postAMLChange
288 -------------------------------------------------------*/
289 PROCEDURE Raise_AML_Event (
290 p_event_name IN VARCHAR2
291 ,p_dml_type IN VARCHAR2
292 ,p_Inventory_Item_Id IN NUMBER
293 ,p_Organization_Id IN NUMBER
294 ,p_Manufacturer_Id IN NUMBER
295 ,p_Mfg_Part_Num IN VARCHAR2
296 ,x_msg_data OUT NOCOPY VARCHAR2
297 ,x_return_status OUT NOCOPY VARCHAR2
298 )
299 IS
300 l_parameter_list WF_PARAMETER_LIST_T := WF_PARAMETER_LIST_T();
301 l_parameter_t WF_PARAMETER_T:= WF_PARAMETER_T(null, null);
302 l_event_name VARCHAR2(240);
303 l_event_key VARCHAR2(240);
304 l_event_num NUMBER;
305
306 BEGIN
307
308 l_event_name := p_event_name ;
309 --l_event_key := SUBSTRB(l_event_name, 1, 225) || '-' || TO_CHAR(SYSDATE, 'J.SSSSS');
310
311 SELECT MTL_BUSINESS_EVENTS_S.NEXTVAL into l_event_num FROM dual; --Bug: 5606011
312 l_event_key := SUBSTRB(l_event_name, 1, 255) || '-' || l_event_num;
313
314
315 --Adding the parameters
316 wf_event.AddParameterToList(p_name => 'INVENTORY_ITEM_ID'
317 ,p_value => p_Inventory_Item_Id
318 ,p_ParameterList => l_parameter_List);
319 wf_event.AddParameterToList(p_name => 'ORGANIZATION_ID'
320 ,p_value => p_Organization_Id
321 ,p_ParameterList => l_parameter_List);
322 wf_event.AddParameterToList(p_name => 'DML_TYPE'
323 ,p_value => p_dml_type
324 ,p_ParameterList => l_parameter_List);
325 wf_event.AddParameterToList(p_name => 'MANUFACTURER_ID'
326 ,p_value => p_Manufacturer_Id
327 ,p_ParameterList => l_parameter_List);
328 wf_event.AddParameterToList(p_name => 'MFG_PART_NUM'
329 ,p_value => p_Mfg_Part_Num
330 ,p_ParameterList => l_parameter_List);
331
332 /*R12: Business Events*/
333 WF_EVENT.Raise(p_event_name => l_event_name
334 ,p_event_key => l_event_key
335 ,p_parameters => l_parameter_list);
336
337 l_parameter_list.DELETE;
338 x_return_status :=FND_API.G_RET_STS_SUCCESS;
339
340 EXCEPTION
341 -----------------------------------------------------------
342 -- There are no expected errors in this procedure, so... --
343 -----------------------------------------------------------
344 WHEN OTHERS THEN
345 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
346 x_msg_data := p_event_name ||' '|| SQLERRM;
347 END Raise_AML_Event;
348
349
350 ------------------------------------------------------------------
351 -- Start : 4105841 : Business Event Enhancement
352 --Procedure to raise the event for Item Create and update --
353 --------------------------------------------------------------------
354 /*Added the new parameter p_is_master_attr_modified */
355 PROCEDURE Raise_Item_Create_Update_Event (
356 p_event_name IN VARCHAR2
357 ,p_request_id IN NUMBER DEFAULT NULL
358 ,p_Organization_Id IN NUMBER DEFAULT NULL
359 ,p_organization_code IN VARCHAR2 DEFAULT NULL
360 ,p_Inventory_Item_Id IN NUMBER DEFAULT NULL
361 ,p_item_number IN VARCHAR2 DEFAULT NULL
362 ,p_item_description IN VARCHAR2 DEFAULT NULL
363 ,p_is_master_attr_modified IN VARCHAR2 DEFAULT 'N'
364 ,x_msg_data OUT NOCOPY VARCHAR2
365 ,x_return_status OUT NOCOPY VARCHAR2
366 )
367 IS
368 l_parameter_list WF_PARAMETER_LIST_T := WF_PARAMETER_LIST_T();
369 l_parameter_t WF_PARAMETER_T:= WF_PARAMETER_T(null, null);
370 l_event_name VARCHAR2(240);
371 l_event_key VARCHAR2(240);
372 l_event_num NUMBER;
373 l_org_id NUMBER;
374
375 Cursor c_get_org_id(p_organization_code VARCHAR2) is
376 Select organization_id
377 from org_organization_definitions
378 where organization_code = p_organization_code;
379 BEGIN
380
381 l_event_name := p_event_name ;
382 --l_event_key := SUBSTRB(l_event_name, 1, 225) || '-' || TO_CHAR(SYSDATE, 'J.SSSSS');
383
384 SELECT MTL_BUSINESS_EVENTS_S.NEXTVAL into l_event_num FROM dual; --Bug: 5606011
385 l_event_key := SUBSTRB(l_event_name, 1, 255) || '-' || l_event_num;
386
387
388 --Adding the parameters
389 IF p_request_id IS NOT NULL THEN --in case of Bulk Load
390 wf_event.AddParameterToList( p_name => 'REQUEST_ID'
391 ,p_value => p_request_id
392 ,p_ParameterList => l_parameter_List);
393 ELSE
394 -- Fix for bug#8474046
395 IF (p_organization_id = FND_API.G_MISS_NUM and p_organization_code is not null) THEN
396 -- Derive the Organization_id from Organization Code
397 OPEN c_get_org_id(p_organization_code);
398 FETCH c_get_org_id INTO l_org_id;
399 CLOSE c_get_org_id;
400 ELSE
401 l_org_id := p_organization_id;
402 END IF;
403
404 wf_event.AddParameterToList( p_name => 'INVENTORY_ITEM_ID'
405 ,p_value => p_Inventory_Item_Id
406 ,p_ParameterList => l_parameter_List);
407 wf_event.AddParameterToList( p_name => 'ORGANIZATION_ID'
408 ,p_value => l_org_id -- fix for bug#8474046 p_Organization_Id
409 ,p_ParameterList => l_parameter_List);
410 wf_event.AddParameterToList( p_name => 'ORGANIZATION_CODE'
411 ,p_value => p_organization_code
412 ,p_ParameterList => l_parameter_List);
413 wf_event.AddParameterToList( p_name => 'ITEM_NUMBER'
414 ,p_value => p_item_number
415 ,p_ParameterList => l_parameter_List);
416 wf_event.AddParameterToList( p_name => 'ITEM_DESCRIPTION'
417 ,p_value => p_item_description
418 ,p_ParameterList => l_parameter_List);
419 wf_event.AddParameterToList( p_name => 'IS_MASTER_ATTR_MODIFIED'
420 ,p_value => p_is_master_attr_modified
421 ,p_ParameterList => l_parameter_List);
422
423 END IF;
424
425 /*R12: Business Events*/
426 WF_EVENT.Raise(p_event_name => l_event_name
427 ,p_event_key => l_event_key
428 ,p_parameters => l_parameter_list);
429
430
431 l_parameter_list.DELETE;
432 x_return_status := FND_API.G_RET_STS_SUCCESS;
433 EXCEPTION
434 WHEN Others THEN
435 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
436 x_msg_data := SQLERRM ;
437 END Raise_Item_Create_Update_Event;
438
439
440 /* -------------------------------------------------------------------
441 Procedure : Raise_Item_Event
442 Purpose : Raise Work flow event
443 ------------------------------------------------------------------*/
444 PROCEDURE Raise_Item_Event(
445 p_event_name IN VARCHAR2
446 ,p_dml_type IN VARCHAR2 DEFAULT NULL
447 ,p_request_id IN VARCHAR2 DEFAULT NULL
448 ,p_Inventory_Item_Id IN NUMBER DEFAULT NULL
449 ,p_Organization_Id IN NUMBER DEFAULT NULL
450 ,p_Revision_id IN NUMBER DEFAULT NULL
451 ,p_category_id IN VARCHAR2 DEFAULT NULL
452 ,p_catalog_id IN VARCHAR2 DEFAULT NULL
453 ,p_old_category_id IN NUMBER DEFAULT NULL --add 8310065 with base bug 8351807
454 ,p_cross_reference_type IN VARCHAR2 DEFAULT NULL --r12
455 ,p_cross_reference IN VARCHAR2 DEFAULT NULL --r12
456 ,p_customer_item_id IN NUMBER DEFAULT NULL --r12
457 ,p_related_item_id IN NUMBER DEFAULT NULL --r12
458 ,p_relationship_type_id IN NUMBER DEFAULT NULL --r12
459 ,p_role_id IN NUMBER DEFAULT NULL --r12
460 ,p_party_type IN VARCHAR2 DEFAULT NULL --r12
461 ,p_party_id IN NUMBER DEFAULT NULL --r12
462 ,p_start_date IN DATE DEFAULT NULL --r12
463 ,x_msg_data OUT NOCOPY VARCHAR2
464 ,x_return_status OUT NOCOPY VARCHAR2)
465 IS
466 l_parameter_list WF_PARAMETER_LIST_T := WF_PARAMETER_LIST_T();
467 l_parameter_t WF_PARAMETER_T := WF_PARAMETER_T(null, null);
468 l_event_name VARCHAR2(240);
469 l_event_key VARCHAR2(240);
470 l_event_num NUMBER;
471 l_raise_item_cat_assign VARCHAR2(2);
472 l_raise_alt_cat_hier_chg VARCHAR2(2);
473 l_raise_catalog_cat_chg VARCHAR2(2);
474 BEGIN
475 l_event_name := p_event_name ;
476 --l_event_key := SUBSTRB(l_event_name, 1, 225) || '-' || TO_CHAR(SYSDATE, 'J.SSSSS');
477
478 SELECT MTL_BUSINESS_EVENTS_S.NEXTVAL into l_event_num FROM dual; --Bug: 5606011
479 l_event_key := SUBSTRB(l_event_name, 1, 255) || '-' || l_event_num;
480
481 IF p_request_id IS NOT NULL THEN --in case of Bulk Load
482 wf_event.AddParameterToList( p_name => 'REQUEST_ID'
483 ,p_value => p_request_id
484 ,p_ParameterList => l_parameter_List);
485 ELSE
486 wf_event.AddParameterToList( p_name => 'INVENTORY_ITEM_ID'
487 , p_value => p_Inventory_Item_Id
488 , p_ParameterList => l_parameter_List);
489 wf_event.AddParameterToList( p_name => 'ORGANIZATION_ID'
490 , p_value => p_Organization_Id
491 , p_ParameterList => l_parameter_List);
492
493 IF p_dml_type IS NOT NULL THEN
494 wf_event.AddParameterToList( p_name => 'DML_TYPE'
495 , p_value => p_dml_type
496 , p_ParameterList => l_parameter_List);
497 END IF;
498
499 IF p_Revision_id IS NOT NULL THEN
500 wf_event.AddParameterToList( p_name => 'REVISION_ID'
501 , p_value => p_Revision_id
502 , p_ParameterList => l_parameter_List);
503 END IF;
504
505 IF p_catalog_id IS NOT NULL THEN
506 wf_event.AddParameterToList( p_name => 'CATALOG_ID'
507 ,p_value => p_catalog_id
508 ,p_ParameterList => l_parameter_List);
509 END IF;
510 IF p_category_id IS NOT NULL THEN
511 wf_event.AddParameterToList( p_name => 'CATEGORY_ID'
512 ,p_value => p_category_id
513 ,p_ParameterList => l_parameter_List);
514 END IF;
515 --add 8310065 with base bug 8351807
516 IF p_old_category_id IS NOT NULL THEN
517 wf_event.AddParameterToList( p_name => 'OLD_CATEGORY_ID'
518 ,p_value => p_old_category_id
519 ,p_ParameterList => l_parameter_List);
520 END IF;
521
522 IF p_cross_reference_type IS NOT NULL THEN
523 wf_event.AddParameterToList( p_name => 'CROSS_REFERENCE_TYPE'
524 ,p_value => p_cross_reference_type
525 ,p_ParameterList => l_parameter_List);
526 END IF;
527
528 IF p_cross_reference IS NOT NULL THEN
529 wf_event.AddParameterToList( p_name => 'CROSS_REFERENCE'
530 ,p_value => p_cross_reference
531 ,p_ParameterList => l_parameter_List);
532 END IF;
533 /***
534 These parameters are commented as the associated events are not routed
535 through this package currently.
536 IF p_role_id IS NOT NULL THEN
537 wf_event.AddParameterToList( p_name => 'ROLE_ID'
538 ,p_value => p_role_id
539 ,p_ParameterList => l_parameter_List);
540 END IF;
541
542 IF p_party_type IS NOT NULL THEN
543 wf_event.AddParameterToList( p_name => 'PARTY_TYPE'
544 ,p_value => p_party_type
545 ,p_ParameterList => l_parameter_List);
546 END IF;
547
548 IF p_party_id IS NOT NULL THEN
549 wf_event.AddParameterToList( p_name => 'PARTY_ID'
550 ,p_value => p_party_id
551 ,p_ParameterList => l_parameter_List);
552 END IF;
553
554 IF p_start_date IS NOT NULL THEN
555 wf_event.AddParameterToList( p_name => 'START_DATE'
556 ,p_value => p_start_date
557 ,p_ParameterList => l_parameter_List);
558 END IF;
559
560 IF p_customer_item_id IS NOT NULL THEN
561 wf_event.AddParameterToList( p_name => 'CUSTOMER_ITEM_ID'
562 ,p_value => p_customer_item_id
563 ,p_ParameterList => l_parameter_List);
564 END IF;
565
566 IF p_related_item_id IS NOT NULL THEN
567 wf_event.AddParameterToList( p_name => 'RELATED_ITEM_ID'
568 ,p_value => p_related_item_id
569 ,p_ParameterList => l_parameter_List);
570 END IF;
571
572 IF p_relationship_type_id IS NOT NULL THEN
573 wf_event.AddParameterToList( p_name => 'RELATIONSHIP_TYPE_ID'
574 ,p_value => p_relationship_type_id
575 ,p_ParameterList => l_parameter_List);
576 END IF;
577 ***/
578 END IF;
579
580 /*R12: Business Events*/
581 IF p_catalog_id IS NOT NULL THEN
582 BEGIN
583 SELECT raise_item_cat_assign_event, raise_alt_cat_hier_chg_event, raise_catalog_cat_chg_event
584 INTO l_raise_item_cat_assign, l_raise_alt_cat_hier_chg, l_raise_catalog_cat_chg
585 FROM mtl_category_sets_b
586 WHERE category_set_id = p_catalog_id;
587
588 IF l_event_name = 'oracle.apps.ego.item.postCatalogAssignmentChange' AND l_raise_item_cat_assign = 'Y' THEN
589 WF_EVENT.Raise( p_event_name => l_event_name
590 ,p_event_key => l_event_key
591 ,p_parameters => l_parameter_list);
592 END IF;
593 IF l_event_name = 'oracle.apps.ego.item.postCatalogCategoryChange' AND l_raise_catalog_cat_chg = 'Y' THEN
594 WF_EVENT.Raise( p_event_name => l_event_name
595 ,p_event_key => l_event_key
596 ,p_parameters => l_parameter_list);
597 END IF;
598 IF l_event_name = 'oracle.apps.ego.item.postValidCategoryChange' AND l_raise_alt_cat_hier_chg = 'Y' THEN
599 WF_EVENT.Raise( p_event_name => l_event_name
600 ,p_event_key => l_event_key
601 ,p_parameters => l_parameter_list);
602 END IF;
603 END;
604 ELSE
605
606 WF_EVENT.Raise( p_event_name => l_event_name
607 ,p_event_key => l_event_key
608 ,p_parameters => l_parameter_list);
609 END IF;
610
611 l_parameter_list.DELETE;
612 x_return_status := FND_API.G_RET_STS_SUCCESS;
613
614 EXCEPTION
615 WHEN Others THEN
616 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
617 x_msg_data := SQLERRM;
618 END Raise_Item_Event;
619 --End : 4105841 : Business Event Enhancement
620
621 --R12 Business Event Enhancement
622 PROCEDURE Raise_Categories_Event(
623 p_event_name IN VARCHAR2
624 ,p_dml_type IN VARCHAR2 DEFAULT NULL
625 ,p_category_set_id IN NUMBER DEFAULT NULL
626 ,p_category_id IN NUMBER DEFAULT NULL
627 ,p_category_name IN VARCHAR2 DEFAULT NULL
628 ,x_msg_data OUT NOCOPY VARCHAR2
629 ,x_return_status OUT NOCOPY VARCHAR2) IS
630
631 l_parameter_list WF_PARAMETER_LIST_T := WF_PARAMETER_LIST_T();
632 l_parameter_t WF_PARAMETER_T := WF_PARAMETER_T(null, null);
633 l_event_name VARCHAR2(240);
634 l_event_key VARCHAR2(240);
635 l_event_num NUMBER;
636 l_raise_item_cat_assign VARCHAR2(2);
637 l_raise_alt_cat_hier_chg VARCHAR2(2);
638 l_raise_catalog_cat_chg VARCHAR2(2);
639 BEGIN
640 l_event_name := p_event_name ;
641 --l_event_key := SUBSTRB(l_event_name, 1, 225) || '-' || TO_CHAR(SYSDATE, 'J.SSSSS');
642
643 SELECT MTL_BUSINESS_EVENTS_S.NEXTVAL into l_event_num FROM dual; --Bug: 5606011
644 l_event_key := SUBSTRB(l_event_name, 1, 255) || '-' || l_event_num;
645
646 IF p_dml_type IS NOT NULL THEN
647 wf_event.AddParameterToList( p_name => 'DML_TYPE'
648 , p_value => p_dml_type
649 , p_ParameterList => l_parameter_List);
650 END IF;
651
652 IF p_category_set_id IS NOT NULL THEN
653 wf_event.AddParameterToList( p_name => 'CATEGORY_SET_ID'
654 ,p_value => p_category_set_id
655 ,p_ParameterList => l_parameter_List);
656 END IF;
657
658
659 IF p_category_id IS NOT NULL THEN
660 wf_event.AddParameterToList( p_name => 'CATEGORY_ID'
661 ,p_value => p_category_id
662 ,p_ParameterList => l_parameter_List);
663 END IF;
664
665 IF p_category_name IS NOT NULL THEN
666 wf_event.AddParameterToList( p_name => 'CATEGORY_NAME'
667 ,p_value => p_category_name
668 ,p_ParameterList => l_parameter_List);
669 END IF;
670
671 IF p_category_set_id IS NOT NULL THEN
672 BEGIN
673 SELECT raise_item_cat_assign_event, raise_alt_cat_hier_chg_event, raise_catalog_cat_chg_event
674 INTO l_raise_item_cat_assign, l_raise_alt_cat_hier_chg, l_raise_catalog_cat_chg
675 FROM mtl_category_sets_b
676 WHERE category_set_id = p_category_set_id;
677
678 IF l_event_name = 'oracle.apps.ego.item.postCatalogAssignmentChange' AND l_raise_item_cat_assign = 'Y' THEN
679 WF_EVENT.Raise( p_event_name => l_event_name
680 ,p_event_key => l_event_key
681 ,p_parameters => l_parameter_list);
682 END IF;
683 IF l_event_name = 'oracle.apps.ego.item.postCatalogCategoryChange' AND l_raise_catalog_cat_chg = 'Y' THEN
684 WF_EVENT.Raise( p_event_name => l_event_name
685 ,p_event_key => l_event_key
686 ,p_parameters => l_parameter_list);
687 END IF;
688 IF l_event_name = 'oracle.apps.ego.item.postValidCategoryChange' AND l_raise_alt_cat_hier_chg = 'Y' THEN
689 WF_EVENT.Raise( p_event_name => l_event_name
690 ,p_event_key => l_event_key
691 ,p_parameters => l_parameter_list);
692 END IF;
693 END;
694 ELSE
695 WF_EVENT.Raise( p_event_name => l_event_name
696 ,p_event_key => l_event_key
697 ,p_parameters => l_parameter_list);
698 END IF;
699
700 l_parameter_list.DELETE;
701 x_return_status := FND_API.G_RET_STS_SUCCESS;
702
703 EXCEPTION
704 WHEN Others THEN
705 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
706 x_msg_data := SQLERRM;
707 END Raise_Categories_Event;
708
709 --R12C Raise Post Process Message Event for Orchestration
710 PROCEDURE Raise_Post_Process_Msg_Event(
711 p_event_name IN VARCHAR2
712 ,p_entity_name IN VARCHAR2
713 ,p_pk1_value IN VARCHAR2
714 ,p_pk2_value IN VARCHAR2
715 ,p_pk3_value IN VARCHAR2
716 ,p_pk4_value IN VARCHAR2
717 ,p_pk5_value IN VARCHAR2
718 ,p_processing_type IN VARCHAR2
719 ,p_language_code IN VARCHAR2
720 ,p_last_update_date IN VARCHAR2
721 ,x_msg_data OUT NOCOPY VARCHAR2
722 ,x_return_status OUT NOCOPY VARCHAR2
723 )
724 IS
725
726 l_parameter_list WF_PARAMETER_LIST_T := WF_PARAMETER_LIST_T();
727 l_event_name VARCHAR2(240);
728 l_event_key VARCHAR2(240);
729
730 BEGIN
731
732 l_event_name := p_event_name ;
733
734 SELECT TO_CHAR( SYSTIMESTAMP, 'dd-mon-yyyy hh24:mi:ss:ffff' )
735 INTO l_event_key
736 FROM DUAL;
737
738 WF_EVENT.AddParameterToList( p_name => 'ENTITY_NAME'
739 ,p_value => p_entity_name
740 ,p_ParameterList => l_parameter_List);
741
742
743 WF_EVENT.AddParameterToList( p_name => 'PK1_VALUE'
744 ,p_value => p_pk1_value
745 ,p_ParameterList => l_parameter_List);
746
747 WF_EVENT.AddParameterToList( p_name => 'PK2_VALUE'
748 ,p_value => p_pk2_value
749 ,p_ParameterList => l_parameter_List);
750
751 WF_EVENT.AddParameterToList( p_name => 'PK3_VALUE'
752 ,p_value => p_pk3_value
753 ,p_ParameterList => l_parameter_List);
754
755 WF_EVENT.AddParameterToList( p_name => 'PK4_VALUE'
756 ,p_value => p_pk4_value
757 ,p_ParameterList => l_parameter_List);
758
759 WF_EVENT.AddParameterToList( p_name => 'PK5_VALUE'
760 ,p_value => p_pk5_value
761 ,p_ParameterList => l_parameter_List);
762
763 WF_EVENT.AddParameterToList( p_name => 'PROCESSING_TYPE'
764 ,p_value => p_processing_type
765 ,p_ParameterList => l_parameter_List);
766
767 WF_EVENT.AddParameterToList( p_name => 'LANGUAGE_CODE'
768 ,p_value => p_language_code
769 ,p_ParameterList => l_parameter_List);
770
771 WF_EVENT.AddParameterToList( p_name => 'LAST_UPDATE_DATE'
772 ,p_value => p_last_update_date
773 ,p_ParameterList => l_parameter_List);
774
775 WF_EVENT.Raise( p_event_name => l_event_name
776 ,p_event_key => l_event_key
777 ,p_parameters => l_parameter_list);
778
779 l_parameter_list.DELETE;
780
781 x_return_status := FND_API.G_RET_STS_SUCCESS;
782 x_msg_data := NULL;
783
784 EXCEPTION
785 WHEN OTHERS THEN
786 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
787 x_msg_data := SQLERRM;
788
789 END Raise_Post_Process_Msg_Event;
790
791 --R12C Setters for Entity Level Business events
792 PROCEDURE Set_Item_Bulkload_Bus_Event(p_true_false IN VARCHAR2)
793 IS
794 l_true_false VARCHAR2(1);
795 BEGIN
796 l_true_false := p_true_false;
797 IF p_true_false NOT IN (FND_API.g_true, FND_API.g_false) THEN
798 l_true_false := FND_API.g_true;
799 END IF;
800 G_ITEM_BULKLOAD_BUS_EVENT := l_true_false;
801 END Set_Item_Bulkload_Bus_Event;
802
803 PROCEDURE Set_Rev_Change_Bus_Event(p_true_false IN VARCHAR2)
804 IS
805 l_true_false VARCHAR2(1);
806 BEGIN
807 l_true_false := p_true_false;
808 IF p_true_false NOT IN (FND_API.g_true, FND_API.g_false) THEN
809 l_true_false := FND_API.g_true;
810 END IF;
811 G_REV_CHANGE_BUS_EVENT := l_true_false;
812 END Set_Rev_Change_Bus_Event;
813
814 PROCEDURE Set_Category_Assign_Bus_Event(p_true_false IN VARCHAR2)
815 IS
816 l_true_false VARCHAR2(1);
817 BEGIN
818 l_true_false := p_true_false;
819 IF p_true_false NOT IN (FND_API.g_true, FND_API.g_false) OR p_true_false IS NULL THEN
820 l_true_false := FND_API.g_true;
821 END IF;
822 G_CAT_ASSIGN_BUS_EVENT := l_true_false;
823 END Set_Category_Assign_Bus_Event;
824
825 PROCEDURE Set_PostAttr_Change_Event(p_true_false IN VARCHAR2)
826 IS
827 l_true_false VARCHAR2(1);
828 BEGIN
829 l_true_false := p_true_false;
830 IF p_true_false NOT IN (FND_API.g_true, FND_API.g_false) THEN
831 l_true_false := FND_API.g_true;
832 END IF;
833 G_POST_ATTR_BUS_EVENT := l_true_false;
834 END Set_PostAttr_Change_Event;
835
836 PROCEDURE Set_PostAml_Change_Event(p_true_false IN VARCHAR2)
837 IS
838 l_true_false VARCHAR2(1);
839 BEGIN
840 l_true_false := p_true_false;
841 IF p_true_false NOT IN (FND_API.g_true, FND_API.g_false) THEN
842 l_true_false := FND_API.g_true;
843 END IF;
844 G_POST_AML_BUS_EVENT := l_true_false;
845 END Set_PostAml_Change_Event;
846
847 PROCEDURE Set_Item_People_Event(p_true_false IN VARCHAR2)
848 IS
849 l_true_false VARCHAR2(1);
850 BEGIN
851 l_true_false := p_true_false;
852 IF p_true_false NOT IN (FND_API.g_true, FND_API.g_false) THEN
853 l_true_false := FND_API.g_true;
854 END IF;
855 G_ITEM_PEOPLE_BUS_EVENT := l_true_false;
856 END Set_Item_People_Event;
857
858 --R12C Getters for Entity Level Business events
859 FUNCTION Get_Item_Bulkload_Bus_Event RETURN VARCHAR2
860 IS
861 BEGIN
862 RETURN(G_ITEM_BULKLOAD_BUS_EVENT );
863 END Get_Item_Bulkload_Bus_Event;
864
865 FUNCTION Get_Rev_Change_Bus_Event RETURN VARCHAR2
866 IS
867 BEGIN
868 RETURN(G_REV_CHANGE_BUS_EVENT );
869 END Get_Rev_Change_Bus_Event;
870
871 FUNCTION Get_Category_Assign_Bus_Event RETURN VARCHAR2
872 IS
873 BEGIN
874 RETURN(G_CAT_ASSIGN_BUS_EVENT );
875 END Get_Category_Assign_Bus_Event;
876
877 FUNCTION Get_PostAttr_Change_Event RETURN VARCHAR2
878 IS
879 BEGIN
880 RETURN(G_POST_ATTR_BUS_EVENT );
881 END Get_PostAttr_Change_Event;
882
883 FUNCTION Get_PostAml_Change_Event RETURN VARCHAR2
884 IS
885 BEGIN
886 RETURN(G_POST_AML_BUS_EVENT );
887 END Get_PostAml_Change_Event;
888
889 FUNCTION Get_Item_People_Event RETURN VARCHAR2
890 IS
891 BEGIN
892 RETURN(G_ITEM_PEOPLE_BUS_EVENT );
893 END Get_Item_People_Event;
894
895 END EGO_WF_WRAPPER_PVT;