[Home] [Help]
PACKAGE BODY: APPS.INV_ITEM_EVENTS_PVT
Source
1 PACKAGE BODY INV_ITEM_EVENTS_PVT AS
2 /* $Header: INVVEVEB.pls 120.12 2007/12/28 06:34:58 pgandhik ship $ */
3
4 ------------------------------------------------------------------------
5 --Package for debugging purposes
6 ------------------------------------------------------------------------
7 procedure insert_log(msg varchar2,dt date:=SYSDATE) IS
8 PRAGMA AUTONOMOUS_TRANSACTION;
9 BEGIN
10 -- INSERT INTO LUX_events(event_name) VALUES(MSG);
11 -- COMMIT;
12 NULL;
13 END INSERT_LOG;
14
15 -- ----------------------------------------------------------------------
16 -- API Name: Get organization info
17 --
18 -- Type: Private
19 --
20 -- Description: Get orgnization Code and Master Org Flag
21 -------------------------------------------------------------------------
22 Procedure get_Org_Info(
23 p_organization_id IN NUMBER
24 ,x_organization_code OUT NOCOPY VARCHAR2
25 ,x_master_org_flag OUT NOCOPY VARCHAR2) IS
26 BEGIN
27 SELECT DECODE(master_organization_id, p_organization_id, 'Y', 'N'),
28 organization_code
29 INTO x_master_org_flag,
30 x_organization_code
31 FROM MTL_PARAMETERS
32 WHERE organization_id = p_organization_id;
33
34 END get_Org_Info;
35
36
37 -- ----------------------------------------------------------------------
38 -- API Name: Get item info
39 --
40 -- Type: Private
41 --
42 -- Description: Get Item name and description
43 -------------------------------------------------------------------------
44
45 Procedure get_Item_Info(
46 p_inventory_item_id IN NUMBER
47 ,p_organization_id IN NUMBER
48 ,x_item_description OUT NOCOPY VARCHAR2
49 ,x_item_number OUT NOCOPY VARCHAR2) IS
50 BEGIN
51 SELECT CONCATENATED_SEGMENTS, DESCRIPTION
52 INTO x_item_number, x_item_description
53 FROM MTL_SYSTEM_ITEMS_KFV
54 WHERE inventory_item_id = p_inventory_item_id
55 AND organization_id = p_organization_id;
56 END get_Item_Info;
57
58 -- ----------------------------------------------------------------------
59 -- API Name: Get Category info
60 --
61 -- Type: Private
62 --
63 -- Description: Get Category Name
64 -------------------------------------------------------------------------
65
66 Procedure get_Category_Info(
67 p_category_id IN NUMBER
68 ,x_category_name OUT NOCOPY VARCHAR2) IS
69 BEGIN
70 SELECT CONCATENATED_SEGMENTS
71 INTO x_category_name
72 FROM MTL_CATEGORIES_KFV
73 WHERE category_id = p_category_id;
74
75 END get_Category_Info;
76
77
78 -- ----------------------------------------------------------------------
79 -- API Name: Raise Business Event
80 --
81 -- Type: Private
82 --
83 -- Description: Raise Business Event
84 --
85 -- Parameters:
86 -- IN:
87 -- p_commit IN BOOLEAN DEFAULT FALSE
88 -- p_xset_id IN NUMBER DEFAULT -999
89 -- p_request_id IN NUMBER NULL
90 -- p_event_name IN VARCHAR2
91 -- p_dml_type IN VARCHAR2
92 -- {INSERT, UPDATE, DELETE, BULK}
93 -- p_inventory_item_id IN NUMBER DEFAULT NULL
94 -- p_item_number IN VARCHAR2 DEFAULT NULL
95 -- p_organization_id IN NUMBER DEFAULT NULL
96 -- p_organization_code IN VARCHAR2 DEFAULT NULL
97 -- p_revision_id IN NUMBER DEFAULT NULL
98 -- p_item_description IN VARCHAR2 DEFAULT NULL
99 -- p_category_set_id IN NUMBER DEFAULT NULL
100 -- p_category_id IN NUMBER DEFAULT NULL
101 -- p_catalog_id IN NUMBER DEFAULT NULL
102 -- p_attr_group_name IN VARCHAR2 DEFAULT NULL
103 -- p_extension_id IN NUMBER DEFAULT NULL
104 -- p_manufacturer_id IN NUMBER DEFAULT NULL
105 -- p_mfg_part_num IN VARCHAR2 DEFAULT NULL
106 -- p_cross_reference_type IN VARCHAR2 DEFAULT NULL
107 -- p_cross_reference IN VARCHAR2 DEFAULT NULL
108 -- p_customer_item_id IN NUMBER DEFAULT NULL
109 -- p_related_item_id IN NUMBER DEFAULT NULL
110 -- p_relationship_type_id IN NUMBER DEFAULT NULL
111 -- p_role_id IN NUMBER DEFAULT NULL
112 -- p_party_type IN VARCHAR2 DEFAULT NULL
113 -- p_party_id IN NUMBER DEFAULT NULL
114 -- p_start_date IN DATE DEFAULT NULL
115 -- p_category_name IN VARCHAR2 DEFAULT NULL
116 --
117 -- ----------------------------------------------------------------------
118
119 Procedure Raise_Events (
120 p_commit IN BOOLEAN DEFAULT FALSE
121 ,p_xset_id IN NUMBER DEFAULT -999
122 ,p_request_id IN NUMBER DEFAULT NULL
123 ,p_event_name IN VARCHAR2 DEFAULT NULL
124 ,p_dml_type IN VARCHAR2
125 ,p_inventory_item_id IN NUMBER DEFAULT NULL
126 ,p_item_number IN VARCHAR2 DEFAULT NULL
127 ,p_organization_id IN NUMBER DEFAULT NULL
128 ,p_organization_code IN VARCHAR2 DEFAULT NULL
129 ,p_revision_id IN NUMBER DEFAULT NULL
130 ,p_item_description IN VARCHAR2 DEFAULT NULL
131 ,p_category_set_id IN NUMBER DEFAULT NULL
132 ,p_category_id IN NUMBER DEFAULT NULL
133 ,p_catalog_id IN NUMBER DEFAULT NULL
134 ,p_attr_group_name IN VARCHAR2 DEFAULT NULL
135 ,p_extension_id IN NUMBER DEFAULT NULL
136 ,p_manufacturer_id IN NUMBER DEFAULT NULL
137 ,p_mfg_part_num IN VARCHAR2 DEFAULT NULL
138 ,p_cross_reference_type IN VARCHAR2 DEFAULT NULL
139 ,p_cross_reference IN VARCHAR2 DEFAULT NULL
140 ,p_customer_item_id IN NUMBER DEFAULT NULL
141 ,p_related_item_id IN NUMBER DEFAULT NULL
142 ,p_relationship_type_id IN NUMBER DEFAULT NULL
143 ,p_role_id IN NUMBER DEFAULT NULL
144 ,p_party_type IN VARCHAR2 DEFAULT NULL
145 ,p_party_id IN NUMBER DEFAULT NULL
146 ,p_start_date IN DATE DEFAULT NULL
147 ,p_category_name IN VARCHAR2 DEFAULT NULL) IS
148
149 /* Cursors to check if a concurrent request resulted in atleast one
150 update to the tables by checking the records with process_flag=7 */
151
152 CURSOR c_raise_item_change_event(cp_request_id NUMBER
153 ,cp_xset_id NUMBER) IS
154 SELECT 'x' FROM dual
155 WHERE EXISTS( SELECT NULL
156 FROM mtl_system_items_interface
157 WHERE set_process_id = cp_xset_id
158 AND process_flag = 7
159 AND request_id = cp_request_id
160 AND transaction_type in ('UPDATE','CREATE'));
161
162 CURSOR c_raise_revision_event(cp_request_id NUMBER
163 ,cp_xset_id NUMBER) IS
164 SELECT 'x' FROM dual
165 WHERE EXISTS(SELECT NULL
166 FROM mtl_item_revisions_interface
167 WHERE set_process_id = cp_xset_id
168 AND process_flag = 7
169 AND request_id = cp_request_id);
170
171 CURSOR c_raise_item_categories_event(cp_request_id NUMBER
172 ,cp_xset_id NUMBER) IS
173 SELECT 'x' FROM dual
174 WHERE EXISTS(SELECT NULL
175 FROM mtl_item_categories_interface
176 WHERE set_process_id = cp_xset_id
177 AND process_flag = 7
178 AND request_id = cp_request_id);
179
180 l_msg_data VARCHAR2(2000);
181 l_raise_event VARCHAR2(1);
182 l_ret_status VARCHAR2(1);
183 l_organization_code VARCHAR2(2000);
184 l_item_description VARCHAR2(2000);
185 l_item_number VARCHAR2(2000);
186 l_category_name VARCHAR2(2000);
187 l_master_org_flag VARCHAR2(1);
188 l_item_entity_bus_event VARCHAR2(1);
189 l_is_master_attr_modified VARCHAR2(1) := INV_ITEM_PVT.Get_Is_Master_Attr_Modified ;
190 /*Bug 6407303 Added the attribute to get whether teh master attribute is modified */
191
192 BEGIN
193 CASE
194 WHEN p_event_name = 'EGO_WF_WRAPPER_PVT.G_AML_CHANGE_EVENT' AND
195 EGO_WF_WRAPPER_PVT.Get_PostAml_Change_Event = FND_API.g_true
196 THEN
197 EXECUTE IMMEDIATE
198 'BEGIN '||
199 ' EGO_WF_WRAPPER_PVT.Raise_AML_Event( '||
200 ' p_event_name => EGO_WF_WRAPPER_PVT.G_AML_CHANGE_EVENT '||
201 ' ,p_dml_type => :p_dml_type '||
202 ' ,p_Inventory_Item_Id => :p_inventory_item_id '||
203 ' ,p_Organization_Id => :p_organization_id '||
204 ' ,p_Manufacturer_Id => :p_manufacturer_id '||
205 ' ,p_Mfg_Part_Num => :p_mfg_part_num '||
206 ' ,x_msg_data => :l_msg_data '||
207 ' ,x_return_status => :l_ret_status); '||
208 'END;'
209 USING IN p_dml_type,
210 IN p_inventory_item_id,
211 IN p_organization_id,
212 IN p_manufacturer_id,
213 IN p_mfg_part_num,
214 OUT l_msg_data,
215 OUT l_ret_status;
216 WHEN p_event_name = 'EGO_WF_WRAPPER_PVT.G_ITEM_APPROVED_EVENT' THEN
217 EXECUTE IMMEDIATE
218 'BEGIN '||
219 ' EGO_WF_WRAPPER_PVT.Raise_Item_Event( '||
220 ' p_event_name => EGO_WF_WRAPPER_PVT.G_ITEM_APPROVED_EVENT '||
221 ' ,p_Inventory_Item_Id => :p_inventory_item_id '||
222 ' ,p_Organization_Id => :p_organization_id '||
223 ' ,x_msg_data => :l_msg_data '||
224 ' ,x_return_status => :l_ret_status); '||
225 'END;'
226 USING IN p_inventory_item_id,
227 IN p_organization_id,
228 OUT l_msg_data,
229 OUT l_ret_status;
230
231 WHEN p_event_name = 'EGO_WF_WRAPPER_PVT.G_REV_CHANGE_EVENT' AND
232 EGO_WF_WRAPPER_PVT.Get_Rev_Change_Bus_Event = FND_API.g_true
233 THEN
234 IF p_request_id IS NOT NULL THEN
235 OPEN c_raise_revision_event(p_request_id, p_xset_id);
236 FETCH c_raise_revision_event INTO l_raise_event;
237 CLOSE c_raise_revision_event;
238 END IF;
239 IF p_request_id IS NULL OR l_raise_event = 'x' THEN
240 EXECUTE IMMEDIATE
241 'BEGIN '||
242 ' EGO_WF_WRAPPER_PVT.Raise_Item_Event( '||
243 ' p_event_name => EGO_WF_WRAPPER_PVT.G_REV_CHANGE_EVENT '||
244 ' ,p_dml_type => :p_dml_type '||
245 ' ,p_Inventory_Item_Id => :p_inventory_item_id '||
246 ' ,p_Organization_Id => :p_organization_id '||
247 ' ,p_Revision_id => :p_revision_id '||
248 ' ,p_request_id => :p_request_id '||
249 ' ,x_msg_data => :l_msg_data '||
250 ' ,x_return_status => :l_ret_status); '||
251 'END;'
252 USING IN p_dml_type,
253 IN p_inventory_item_id,
254 IN p_organization_id,
255 IN p_revision_id,
256 IN p_request_id,
257 OUT l_msg_data,
258 OUT l_ret_status;
259 END IF;
260 WHEN p_event_name = 'EGO_WF_WRAPPER_PVT.G_ITEM_UPDATE_EVENT' THEN
261 -- Populate Item and Orgnanization Information
262 get_Org_Info(
263 p_organization_id => p_organization_id
264 ,x_organization_code => l_organization_code
265 ,x_master_org_flag => l_master_org_flag);
266 get_Item_Info(
267 p_inventory_item_id
268 ,p_organization_id
269 ,l_item_description
270 ,l_item_number);
271 -- Populate Item and Orgnanization Information
272
273 EXECUTE IMMEDIATE
274 'BEGIN '||
275 ' EGO_WF_WRAPPER_PVT.Raise_Item_Create_Update_Event( '||
276 ' p_event_name => EGO_WF_WRAPPER_PVT.G_ITEM_UPDATE_EVENT '||
277 ' ,p_request_id => :p_request_id '||
278 ' ,p_Organization_Id => :p_organization_id '||
279 ' ,p_organization_code => :l_organization_code '||
280 ' ,p_Inventory_Item_Id => :p_inventory_item_id '||
281 ' ,p_item_number => :l_item_number '||
282 ' ,p_item_description => :l_item_description '||
283 ' ,p_is_master_attr_modified => :l_is_master_attr_modified '|| /* Added for bug 6407303*/
284 ' ,x_msg_data => :l_msg_data '||
285 ' ,x_return_status => :l_ret_status); '||
286 'END;'
287 USING IN p_request_id,
288 IN p_organization_id,
289 IN l_organization_code,
290 IN p_inventory_item_id,
291 IN l_item_number,
292 IN l_item_description,
293 IN l_is_master_attr_modified, /* Added for bug 6407303*/
294 OUT l_msg_data,
295 OUT l_ret_status;
296 INV_ITEM_PVT.Set_Is_Master_Attr_Modified('N'); /*Added for bug 6407303*/
297 WHEN p_event_name = 'EGO_WF_WRAPPER_PVT.G_ITEM_CREATE_EVENT' THEN
298 -- Populate Item and Orgnanization Information
299 get_Org_Info(
300 p_organization_id => p_organization_id
301 ,x_organization_code => l_organization_code
302 ,x_master_org_flag => l_master_org_flag);
303 get_Item_Info(
304 p_inventory_item_id
305 ,p_organization_id
306 ,l_item_description
307 ,l_item_number);
308 -- Populate Item and Orgnanization Information
309
310 EXECUTE IMMEDIATE
311 'BEGIN '||
312 ' EGO_WF_WRAPPER_PVT.Raise_Item_Create_Update_Event( '||
313 ' p_event_name => EGO_WF_WRAPPER_PVT.G_ITEM_CREATE_EVENT '||
314 ' ,p_request_id => :p_request_id '||
315 ' ,p_Organization_Id => :p_organization_id '||
316 ' ,p_organization_code => :l_organization_code '||
317 ' ,p_Inventory_Item_Id => :p_inventory_item_id '||
318 ' ,p_item_number => :l_item_number '||
319 ' ,p_item_description => :l_item_description '||
320 ' ,x_msg_data => :l_msg_data '||
321 ' ,x_return_status => :l_ret_status); '||
322 'END;'
323 USING IN p_request_id,
324 IN p_organization_id,
325 IN l_organization_code,
329 OUT l_msg_data,
326 IN p_inventory_item_id,
327 IN l_item_number,
328 IN l_item_description,
330 OUT l_ret_status;
331 WHEN p_event_name = 'EGO_WF_WRAPPER_PVT.G_ITEM_CAT_ASSIGN_EVENT' AND
332 EGO_WF_WRAPPER_PVT.Get_Category_Assign_Bus_Event = FND_API.g_true
333 THEN
334 IF p_request_id IS NOT NULL THEN
335 OPEN c_raise_item_categories_event(p_request_id, p_xset_id );
336 FETCH c_raise_item_categories_event INTO l_raise_event;
337 CLOSE c_raise_item_categories_event;
338 END IF;
339 IF p_request_id IS NULL OR l_raise_event = 'x' THEN
340 EXECUTE IMMEDIATE
341 'BEGIN '||
342 ' EGO_WF_WRAPPER_PVT.Raise_Item_Event( '||
343 ' p_event_name => EGO_WF_WRAPPER_PVT.G_ITEM_CAT_ASSIGN_EVENT '||
344 ' ,p_dml_type => :p_dml_type '||
345 ' ,p_request_id => :p_request_id '||
346 ' ,p_Inventory_Item_Id => :p_inventory_item_id '||
347 ' ,p_Organization_Id => :p_organization_id '||
348 ' ,p_catalog_id => :p_category_set_id '||
349 ' ,p_category_id => :p_category_id '||
350 ' ,x_msg_data => :l_msg_data '||
351 ' ,x_return_status => :l_ret_status); '||
352 'END;'
353 USING IN p_dml_type,
354 IN p_request_id,
355 IN p_inventory_item_id,
356 IN p_organization_id,
357 IN p_category_set_id,
358 IN p_category_id,
359 OUT l_msg_data,
360 OUT l_ret_status;
361 END IF;
362 WHEN p_event_name = 'EGO_WF_WRAPPER_PVT.G_PRE_ATTR_CHANGE_EVENT' THEN
363 NULL;
364 WHEN p_event_name = 'EGO_WF_WRAPPER_PVT.G_GTIN_ATTR_CHANGE_EVENT' THEN
365 NULL;
366
367 WHEN p_event_name = 'EGO_WF_WRAPPER_PVT.G_Xref_CHANGE_EVENT' THEN
368 EXECUTE IMMEDIATE
369 'BEGIN '||
370 ' EGO_WF_WRAPPER_PVT.Raise_Item_Event( '||
371 ' p_event_name => EGO_WF_WRAPPER_PVT.G_Xref_CHANGE_EVENT '||
372 ' ,p_dml_type => :p_dml_type '||
373 ' ,p_Inventory_Item_Id => :p_inventory_item_id '||
374 ' ,p_Organization_Id => :p_organization_id '||
375 ' ,p_cross_reference_type => :p_cross_reference_type '||
376 ' ,p_cross_reference => :p_cross_reference '||
377 ' ,x_msg_data => :l_msg_data '||
378 ' ,x_return_status => :l_ret_status); '||
379 'END;'
380 USING IN p_dml_type,
381 IN p_inventory_item_id,
382 IN p_organization_id,
383 IN p_cross_reference_type,
384 IN p_cross_reference,
385 OUT l_msg_data,
386 OUT l_ret_status;
387 WHEN p_event_name = 'EGO_WF_WRAPPER_PVT.G_CUST_ITEM_XREF_CHANGE_EVENT' THEN
388 NULL;
389 /* Commenting the call to hide the event
390 EXECUTE IMMEDIATE
391 'BEGIN '||
392 ' EGO_WF_WRAPPER_PVT.Raise_Item_Event( '||
393 ' p_event_name => EGO_WF_WRAPPER_PVT.G_CUST_ITEM_XREF_CHANGE_EVENT '||
394 ' ,p_dml_type => :p_dml_type '||
395 ' ,p_Inventory_Item_Id => :p_inventory_item_id '||
396 ' ,p_Organization_Id => :p_organization_id '||
397 ' ,p_customer_item_id => :p_customer_item_id '||
398 ' ,x_msg_data => :l_msg_data '||
399 ' ,x_return_status => :l_ret_status); '||
400 'END;'
401 USING IN p_dml_type,
402 IN p_inventory_item_id,
403 IN p_organization_id,
404 IN p_customer_item_id,
405 OUT l_msg_data,
406 OUT l_ret_status;
407 */
408
409 WHEN p_event_name = 'EGO_WF_WRAPPER_PVT.G_REL_ITEM_CHANGE_EVENT' THEN
410 NULL;
411 /* Commenting the call to hide the event
412 EXECUTE IMMEDIATE
413 'BEGIN '||
414 ' EGO_WF_WRAPPER_PVT.Raise_Item_Event( '||
415 ' p_event_name => EGO_WF_WRAPPER_PVT.G_REL_ITEM_CHANGE_EVENT '||
416 ' ,p_dml_type => :p_dml_type '||
417 ' ,p_Inventory_Item_Id => :p_inventory_item_id '||
418 ' ,p_Organization_Id => :p_organization_id '||
419 ' ,p_related_item_id => :p_related_item_id '||
420 ' ,p_relationship_type_id => :p_relationship_type_id '||
421 ' ,x_msg_data => :l_msg_data '||
422 ' ,x_return_status => :l_ret_status); '||
423 'END;'
424 USING IN p_dml_type,
425 IN p_inventory_item_id,
429 OUT l_msg_data,
426 IN p_organization_id,
427 IN p_related_item_id,
428 IN p_relationship_type_id,
430 OUT l_ret_status;
431 */
432
433 WHEN p_event_name = 'EGO_WF_WRAPPER_PVT.G_ITEM_ROLE_CHANGE_EVENT' THEN
434 EXECUTE IMMEDIATE
435 'BEGIN '||
436 ' EGO_WF_WRAPPER_PVT.Raise_Item_Event( '||
437 ' p_event_name => EGO_WF_WRAPPER_PVT.G_ITEM_ROLE_CHANGE_EVENT '||
438 ' ,p_dml_type => :p_dml_type '||
439 ' ,p_Inventory_Item_Id => :p_inventory_item_id '||
440 ' ,p_Organization_Id => :p_organization_id '||
441 ' ,p_role_id => :p_role_id '||
442 ' ,p_party_type => :p_party_type '||
443 ' ,p_party_id => :p_party_id '||
444 ' ,p_start_date => :p_start_date '||
445 ' ,x_msg_data => :l_msg_data '||
446 ' ,x_return_status => :l_ret_status); '||
447 'END;'
448 USING IN p_dml_type,
449 IN p_inventory_item_id,
450 IN p_organization_id,
451 IN p_role_id,
452 IN p_party_type,
453 IN p_party_id,
454 IN p_start_date,
455 OUT l_msg_data,
456 OUT l_ret_status;
457 WHEN p_event_name = 'EGO_WF_WRAPPER_PVT.G_VALID_CAT_CHANGE_EVENT' THEN
458 EXECUTE IMMEDIATE
459 'BEGIN '||
460 ' EGO_WF_WRAPPER_PVT.Raise_Categories_Event( '||
461 ' p_event_name => EGO_WF_WRAPPER_PVT.G_VALID_CHANGE_EVENT '||
462 ' ,p_dml_type => :p_dml_type '||
463 ' ,p_category_set_id => :p_category_set_id '||
464 ' ,p_category_id => :p_category_id '||
465 ' ,x_msg_data => :l_msg_data '||
466 ' ,x_return_status => :l_ret_status); '||
467 'END;'
468 USING IN p_dml_type,
469 IN p_category_set_id,
470 IN p_category_id,
471 OUT l_msg_data,
472 OUT l_ret_status;
473 WHEN p_event_name = 'EGO_WF_WRAPPER_PVT.G_CAT_CATEGORY_CHANGE_EVENT' THEN
474 -- Populate Category Information
475 get_category_info(
476 p_category_id => p_category_id
477 ,x_category_name => l_category_name);
478 -- Populate Category Information
479
480 EXECUTE IMMEDIATE
481 'BEGIN '||
482 ' EGO_WF_WRAPPER_PVT.Raise_Categories_Event( '||
483 ' p_event_name => EGO_WF_WRAPPER_PVT.G_CAT_CATEGORY_CHANGE_EVENT '||
484 ' ,p_dml_type => :p_dml_type '||
485 ' ,p_category_name => :l_category_name '||
486 ' ,p_category_id => :p_category_id '||
487 ' ,x_msg_data => :l_msg_data '||
488 ' ,x_return_status => :l_ret_status); '||
489 'END;'
490 USING IN p_dml_type,
491 IN l_category_name,
492 IN p_category_id,
493 OUT l_msg_data,
494 OUT l_ret_status;
495 WHEN p_event_name = 'EGO_WF_WRAPPER_PVT.G_ITEM_BULKLOAD_EVENT' AND
496 EGO_WF_WRAPPER_PVT.Get_Item_Bulkload_Bus_Event = FND_API.g_true
497 THEN
498 OPEN c_raise_item_change_event(p_request_id, p_xset_id);
499 FETCH c_raise_item_change_event INTO l_raise_event;
500 close c_raise_item_change_event;
501 IF l_raise_event = 'x' THEN
502 EXECUTE IMMEDIATE
503 'BEGIN '||
504 ' EGO_WF_WRAPPER_PVT.Raise_Item_Event( '||
505 ' p_event_name => EGO_WF_WRAPPER_PVT.G_ITEM_BULKLOAD_EVENT '||
506 ' ,p_request_id => :p_request_id '||
507 ' ,x_msg_data => :l_msg_data '||
508 ' ,x_return_status => :l_ret_status); '||
509 'END;'
510 USING IN p_request_id,
511 OUT l_msg_data,
512 OUT l_ret_status;
513 END IF;
514 END CASE;
515
516 EXCEPTION
517 WHEN OTHERS THEN
518 NULL;
519
520 END Raise_Events;
521
522
523 -- ----------------------------------------------------------------------
524 -- API Name: Call ICX APIs
525 --
526 -- Type: Private
527 --
528 -- Description: Call ICX APIs
529 --
530 -- Parameters:
531 -- IN:
532 -- p_commit IN BOOLEAN DEFAULT FALSE
533 -- p_xset_id IN NUMBER DEFAULT -999
534 -- p_request_id IN NUMBER NULL
535 -- p_entity_type IN VARCHAR2
539 -- p_item_number IN VARCHAR2 DEFAULT NULL
536 -- p_dml_type IN VARCHAR2
537 -- {'INSERT', 'UPDATE', 'DELETE', 'BULK'}
538 -- p_inventory_item_id IN NUMBER DEFAULT NULL
540 -- p_organization_id IN NUMBER DEFAULT NULL
541 -- p_organization_code IN VARCHAR2 DEFAULT NULL
542 -- p_master_org_flag IN VARCHAR2 DEFAULT NULL
543 -- {'Y', 'N'}
544 -- p_item_description IN VARCHAR2 DEFAULT NULL
545 -- p_category_set_id IN NUMBER DEFAULT NULL
546 -- p_category_id IN NUMBER DEFAULT NULL
547 -- p_old_category_id IN NUMBER DEFAULT NULL
548 -- p_category_name IN VARCHAR2 DEFAULT NULL
549 -- p_structure_id IN NUMBER DEFAULT NULL
550 --
551 --
552 -- ----------------------------------------------------------------------
553
554 Procedure Invoke_ICX_APIs ( p_commit IN BOOLEAN DEFAULT FALSE
555 ,p_xset_id IN NUMBER DEFAULT -999
556 ,p_request_id IN NUMBER DEFAULT NULL
557 ,p_entity_type IN VARCHAR2
558 ,p_dml_type IN VARCHAR2
559 ,p_inventory_item_id IN NUMBER DEFAULT NULL
560 ,p_item_number IN VARCHAR2 DEFAULT NULL
561 ,p_organization_id IN NUMBER DEFAULT NULL
562 ,p_organization_code IN VARCHAR2 DEFAULT NULL
563 ,p_master_org_flag IN VARCHAR2 DEFAULT NULL
564 ,p_item_description IN VARCHAR2 DEFAULT NULL
565 ,p_category_set_id IN NUMBER DEFAULT NULL
566 ,p_category_id IN NUMBER DEFAULT NULL
567 ,p_old_category_id IN NUMBER DEFAULT NULL
568 ,p_category_name IN VARCHAR2 DEFAULT NULL
569 ,p_structure_id IN NUMBER DEFAULT NULL) IS
570
571 /* Cursors to check if a concurrent request resulted in atleast one
572 update to the tables by checking the records with process_flag=7 */
573
574 CURSOR c_raise_item_change_event(cp_request_id NUMBER
575 ,cp_xset_id NUMBER) IS
576 SELECT 'x' FROM dual
577 WHERE EXISTS( SELECT NULL
578 FROM mtl_system_items_interface
579 WHERE set_process_id = cp_xset_id
580 AND process_flag = 7
581 AND request_id = cp_request_id
582 AND transaction_type in ('UPDATE','CREATE'));
583
584 CURSOR c_raise_item_categories_event(cp_request_id NUMBER
585 ,cp_xset_id NUMBER) IS
586 SELECT 'x' FROM dual
587 WHERE EXISTS(SELECT NULL
588 FROM mtl_item_categories_interface
589 WHERE set_process_id = cp_xset_id
590 AND process_flag = 7
591 AND request_id = cp_request_id);
592
593 l_raise_event VARCHAR2(1);
594 l_ret_status VARCHAR2(1);
595 l_organization_code VARCHAR2(2000);
596 l_master_org_flag VARCHAR2(1);
597 l_item_description VARCHAR2(2000);
598 l_item_number VARCHAR2(2000);
599 l_category_name VARCHAR2(2000);
600 l_commit VARCHAR2(1);
601 l_icx_migrp_exists VARCHAR2(1);
602 l_icx_catggrp_exists VARCHAR2(1);
603
604 BEGIN
605 --6531763: Adding ICX install check.
606 IF INV_ITEM_UTIL.Appl_Inst_ICX = 0 THEN
607 RETURN;
608 END IF;
609
610 IF p_commit = TRUE THEN
611 l_commit := FND_API.G_TRUE;
612 ELSE
613 l_commit := FND_API.G_FALSE;
614 END IF;
615
616 IF (INV_ITEM_UTIL.Object_Exists(
617 p_object_type => 'PACKAGE',
618 p_object_name => 'ICX_CAT_POPULATE_MI_GRP') = 'Y') THEN
619 l_icx_migrp_exists := 'Y';
620 ELSE
621 l_icx_migrp_exists := 'N';
622 END IF;
623
624 IF (INV_ITEM_UTIL.Object_Exists(
625 p_object_type => 'PACKAGE',
626 p_object_name => 'ICX_CAT_POPULATE_CATG_GRP') = 'Y') THEN
627 l_icx_catggrp_exists := 'Y';
628 ELSE
629 l_icx_catggrp_exists := 'N';
630 END IF;
631
632 CASE
633 WHEN p_entity_type = 'ITEM' AND l_icx_migrp_exists = 'Y' THEN
634 IF p_dml_type = 'BULK' THEN
635 OPEN c_raise_item_change_event(p_request_id, p_xset_id );
636 FETCH c_raise_item_change_event INTO l_raise_event;
637 CLOSE c_raise_item_change_event;
638 IF l_raise_event = 'x' THEN
639 EXECUTE IMMEDIATE
640 ' BEGIN '||
641 ' ICX_CAT_POPULATE_MI_GRP.populateBulkItemChange( '||
642 ' P_API_VERSION => 1.0 '||
643 ' ,P_COMMIT => :l_commit '||
644 ' ,P_INIT_MSG_LIST => NULL '||
645 ' ,P_VALIDATION_LEVEL => NULL '||
646 ' ,P_REQUEST_ID => :p_request_id '||
650 USING IN l_commit, IN p_request_id, IN p_entity_type
647 ' ,P_ENTITY_TYPE => :p_entity_type '||
648 ' ,X_RETURN_STATUS => :l_ret_status ); '||
649 ' END;'
651 ,OUT l_ret_status;
652 END IF;
653 ELSE
654 -- Populate Item and Orgnanization Information
655 get_Org_Info(
656 p_organization_id => p_organization_id
657 ,x_organization_code => l_organization_code
658 ,x_master_org_flag => l_master_org_flag);
659
660 get_Item_Info(
661 p_inventory_item_id
662 ,p_organization_id
663 ,l_item_description
664 ,l_item_number);
665 -- Populate Item and Orgnanization Information
666
667 EXECUTE IMMEDIATE
668 'BEGIN '||
669 ' ICX_CAT_POPULATE_MI_GRP.populateItemChange( '||
670 ' P_API_VERSION => 1.0 '||
671 ' ,P_COMMIT => :l_commit '||
672 ' ,P_INIT_MSG_LIST => NULL '||
673 ' ,P_VALIDATION_LEVEL => NULL '||
674 ' ,P_DML_TYPE => :p_dml_type '||
675 ' ,P_INVENTORY_ITEM_ID => :p_inventory_item_id '||
676 ' ,P_ITEM_NUMBER => :l_item_number '||
677 ' ,P_ORGANIZATION_ID => :p_organization_id '||
678 ' ,P_ORGANIZATION_CODE => :l_organization_code '||
679 ' ,P_MASTER_ORG_FLAG => :l_master_org_flag '||
680 ' ,P_ITEM_DESCRIPTION => :l_item_description '||
681 ' ,X_RETURN_STATUS => :l_ret_status); '||
682 ' END; '
683 USING IN l_commit, IN p_dml_type, IN p_inventory_item_id,
684 IN l_item_number, IN p_organization_id,
685 IN l_organization_code, IN l_master_org_flag,
686 IN l_item_description, OUT l_ret_status;
687 END IF;
688
689 WHEN p_entity_type = 'ITEM_CATEGORY' AND l_icx_migrp_exists = 'Y' THEN
690 IF p_dml_type = 'BULK' THEN
691 OPEN c_raise_item_categories_event(p_request_id, p_xset_id );
692 FETCH c_raise_item_categories_event INTO l_raise_event;
693 CLOSE c_raise_item_categories_event;
694 IF l_raise_event = 'x' THEN
695 EXECUTE IMMEDIATE
696 ' BEGIN '||
697 ' ICX_CAT_POPULATE_MI_GRP.populateBulkItemChange( '||
698 ' P_API_VERSION => 1.0 '||
699 ' ,P_COMMIT => :l_commit '||
700 ' ,P_INIT_MSG_LIST => NULL '||
701 ' ,P_VALIDATION_LEVEL => NULL '||
702 ' ,P_REQUEST_ID => :p_request_id '||
703 ' ,P_ENTITY_TYPE => :p_entity_type '||
704 ' ,X_RETURN_STATUS => :l_ret_status ); '||
705 ' END;'
706 USING IN l_commit, IN p_request_id, IN p_entity_type
707 ,OUT l_ret_status;
708 END IF;
709 ELSE
710 -- Populate Item and Orgnanization Information
711 get_Item_Info(
712 p_inventory_item_id
713 ,p_organization_id
714 ,l_item_description
715 ,l_item_number);
716 get_Org_Info(
717 p_organization_id => p_organization_id
718 ,x_organization_code => l_organization_code
719 ,x_master_org_flag => l_master_org_flag);
720 -- Populate Item Information
721
722 EXECUTE IMMEDIATE
723 'BEGIN '||
724 ' ICX_CAT_POPULATE_MI_GRP.populateItemCategoryChange( '||
725 ' P_API_VERSION => 1.0 '||
726 ' ,P_COMMIT => :l_commit '||
727 ' ,P_INIT_MSG_LIST => NULL '||
728 ' ,P_VALIDATION_LEVEL => NULL '||
729 ' ,P_DML_TYPE => :p_dml_type '||
730 ' ,P_INVENTORY_ITEM_ID => :p_inventory_item_id '||
731 ' ,P_ITEM_NUMBER => :l_item_number '||
732 ' ,P_ORGANIZATION_ID => :p_organization_id '||
733 ' ,P_CATEGORY_SET_ID => :p_category_set_id '||
734 ' ,P_CATEGORY_ID => :p_category_id '||
735 ' ,P_MASTER_ORG_FLAG => :l_master_org_flag '||
736 ' ,X_RETURN_STATUS => :l_ret_status); '||
737 ' END; '
738 USING IN l_commit, IN p_dml_type, IN p_inventory_item_id,
739 IN l_item_number, IN p_organization_id,
740 IN p_category_set_id, IN p_category_id,
741 IN l_master_org_flag, OUT l_ret_status;
742 END IF;
743
744 WHEN p_entity_type = 'CATEGORY' AND l_icx_catggrp_exists = 'Y' THEN
745 -- Populate Category Information
746 get_category_info(
747 p_category_id => p_category_id
751 EXECUTE IMMEDIATE
748 ,x_category_name => l_category_name);
749 -- Populate Category Information
750
752 'BEGIN '||
753 ' ICX_CAT_POPULATE_CATG_GRP.populateCategoryChange( '||
754 ' P_API_VERSION => 1.0 '||
755 ' ,P_COMMIT => :l_commit '||
756 ' ,P_INIT_MSG_LIST => NULL '||
757 ' ,P_VALIDATION_LEVEL => NULL '||
758 ' ,P_DML_TYPE => :p_dml_type '||
759 ' ,P_STRUCTURE_ID => :p_structure_id '||
760 ' ,P_CATEGORY_NAME => :l_category_name '||
761 ' ,P_CATEGORY_ID => :p_category_id '||
762 ' ,X_RETURN_STATUS => :l_ret_status); '||
763 'END; '
764 USING IN l_commit, IN p_dml_type,
765 IN p_structure_id, IN l_category_name,
766 IN p_category_id, OUT l_ret_status;
767
768 WHEN p_entity_type = 'VALID_CATEGORY' AND l_icx_catggrp_exists = 'Y' THEN
769 CASE
770 WHEN p_dml_type = 'CREATE' THEN
771 EXECUTE IMMEDIATE
772 'BEGIN '||
773 ' ICX_CAT_POPULATE_CATG_GRP.populateValidCategorySetInsert( '||
774 ' P_API_VERSION => 1.0 '||
775 ' ,P_COMMIT => :l_commit '||
776 ' ,P_INIT_MSG_LIST => NULL '||
777 ' ,P_VALIDATION_LEVEL => NULL '||
778 ' ,P_CATEGORY_SET_ID => :p_category_set_id '||
779 ' ,P_CATEGORY_ID => :p_category_id '||
780 ' ,X_RETURN_STATUS => :l_ret_status); '||
781 'END; '
782 USING IN l_commit, IN p_category_set_id,
783 IN p_category_id, OUT l_ret_status;
784
785 WHEN p_dml_type = 'UPDATE' THEN
786 EXECUTE IMMEDIATE
787 'BEGIN '||
788 ' ICX_CAT_POPULATE_CATG_GRP.populateValidCategorySetDelete( '||
789 ' P_API_VERSION => 1.0 '||
790 ' ,P_COMMIT => :l_commit '||
791 ' ,P_INIT_MSG_LIST => NULL '||
792 ' ,P_VALIDATION_LEVEL => NULL '||
793 ' ,P_CATEGORY_SET_ID => :p_category_set_id '||
794 ' ,P_NEW_CATEGORY_ID => :p_category_id '||
795 ' ,P_OLD_CATEGORY_ID => :p_old_category_id '||
796 ' ,X_RETURN_STATUS => :l_ret_status); '||
797 'END; '
798 USING IN l_commit, IN p_category_set_id,
799 IN p_category_id, IN p_old_category_id,
800 OUT l_ret_status;
801
802 WHEN p_dml_type = 'DELETE' THEN
803 EXECUTE IMMEDIATE
804 'BEGIN '||
805 ' ICX_CAT_POPULATE_CATG_GRP.populateValidCategorySetDelete( '||
806 ' P_API_VERSION => 1.0 '||
807 ' ,P_COMMIT => :l_commit '||
808 ' ,P_INIT_MSG_LIST => NULL '||
809 ' ,P_VALIDATION_LEVEL => NULL '||
810 ' ,P_CATEGORY_SET_ID => :p_category_set_id '||
811 ' ,P_CATEGORY_ID => :p_category_id '||
812 ' ,X_RETURN_STATUS => :l_ret_status); '||
813 'END; '
814 USING IN l_commit, IN p_category_set_id,
815 IN p_category_id, OUT l_ret_status;
816
817 END CASE; -- p_dml_type
818 END CASE; --p_entity_type
819
820 EXCEPTION
821 WHEN OTHERS THEN
822 NULL;
823
824 END Invoke_ICX_APIs;
825
826 -- ----------------------------------------------------------------------
827 -- API Name: Call ICX APIs
828 --
829 -- Type: Private
830 --
831 -- Description: Wrapper on Invoke_ICX_APIs. This procedure just
832 -- converts the p_commit INT parameter to boolean
833 -- Added so that p_commit can be passed from java layer
834 -- INTEGER p_commit = 1 >> TRUE = p_commit BOOLEAN
835 --
836 -- Parameters: Same as Invoke_ICX_APIs except p_commit is INTEGER
837
838 Procedure Invoke_ICX_wrapper (
839 p_commit IN INTEGER DEFAULT 1
840 ,p_xset_id IN NUMBER DEFAULT -999
841 ,p_request_id IN NUMBER DEFAULT NULL
842 ,p_entity_type IN VARCHAR2 DEFAULT NULL
843 ,p_dml_type IN VARCHAR2
844 ,p_inventory_item_id IN NUMBER DEFAULT NULL
845 ,p_item_number IN VARCHAR2 DEFAULT NULL
846 ,p_organization_id IN NUMBER DEFAULT NULL
847 ,p_organization_code IN VARCHAR2 DEFAULT NULL
848 ,p_master_org_flag IN VARCHAR2 DEFAULT NULL
849 ,p_item_description IN VARCHAR2 DEFAULT NULL
850 ,p_category_set_id IN NUMBER DEFAULT NULL
851 ,p_category_id IN NUMBER DEFAULT NULL
855 l_commit BOOLEAN;
852 ,p_old_category_id IN NUMBER DEFAULT NULL
853 ,p_category_name IN VARCHAR2 DEFAULT NULL
854 ,p_structure_id IN NUMBER DEFAULT NULL) IS
856 BEGIN
857 IF p_commit = 1 THEN
858 l_commit := TRUE;
859 ELSE
860 l_commit := FALSE;
861 END IF;
862 Invoke_ICX_APIs (
863 p_commit => l_commit
864 ,p_xset_id => p_xset_id
865 ,p_request_id => p_request_id
866 ,p_entity_type => p_entity_type
867 ,p_dml_type => p_dml_type
868 ,p_inventory_item_id => p_inventory_item_id
869 ,p_item_number => p_item_number
870 ,p_organization_id => p_organization_id
871 ,p_organization_code => p_organization_code
872 ,p_master_org_flag => p_master_org_flag
873 ,p_item_description => p_item_description
874 ,p_category_set_id => p_category_set_id
875 ,p_category_id => p_category_id
876 ,p_old_category_id => p_old_category_id
877 ,p_category_name => p_category_name
878 ,p_structure_id => p_structure_id);
879
880 END Invoke_ICX_wrapper;
881
882
883 -- ----------------------------------------------------------------------
884 -- API Name: Invoke_JAI_API
885 --
886 -- Type: Private
887 --
888 -- Description: Call India Localization API
889 --
890 -- Parameters:
891 -- IN:
892 -- pv_action_type IN VARCHAR2 {'COPY','ASSIGN','IMPORT','DELETE'}
893 -- pt_item_data IN DBMS_UTILITY.UNCL_ARRAY
894 -- pn_set_process_id IN NUMBER
895 -- pv_called_from IN VARCHAR2
896 --
897 -- ----------------------------------------------------------------------
898
899 Procedure Invoke_JAI_API (
900 p_action_type IN VARCHAR2
901 ,p_organization_id IN MTL_SYSTEM_ITEMS.ORGANIZATION_ID%TYPE
902 ,p_inventory_item_id IN MTL_SYSTEM_ITEMS.INVENTORY_ITEM_ID%TYPE
903 ,p_source_organization_id IN MTL_SYSTEM_ITEMS.ORGANIZATION_ID%TYPE
904 ,p_source_inventory_item_id IN MTL_SYSTEM_ITEMS.INVENTORY_ITEM_ID%TYPE
905 ,p_set_process_id IN NUMBER
906 ,p_called_from IN VARCHAR2 ) IS
907
908 l_country_code VARCHAR2(4) := NULL;
909 l_product_code VARCHAR2(4) := NULL;
910 l_jai_object VARCHAR2(1);
911
912 BEGIN
913 --Bug: 4880971
914 --l_country_code := fnd_profile.value('JGZZ_COUNTRY_CODE');
915 --l_product_code := fnd_profile.value('JGZZ_PRODUCT_CODE');
916 l_jai_object := INV_ITEM_UTIL.Object_Exists(
917 p_object_type => 'PACKAGE'
918 ,p_object_name => 'JAI_INV_ITEMS_PKG');
919
920
921 --Bug: 4880971
922 Invoke_JG_ZZ_API( p_organization_id => p_organization_id
923 ,p_country_code => l_country_code
924 ,p_product_code => l_product_code);
925
926 IF l_product_code = 'JA'
927 AND l_country_code = 'IN'
928 AND l_jai_object = 'Y'
929 THEN
930
931 EXECUTE IMMEDIATE
932 'BEGIN '||
933 ' JAI_INV_ITEMS_PKG.PROPAGATE_ITEM_ACTION ( '||
934 ' pv_action_type => :p_action_type '||
935 ' , pn_organization_id => :p_organization_id '||
936 ' , pn_inventory_item_id => :p_inventory_item_id '||
937 ' , pn_source_organization_id => :p_source_organization_id '||
938 ' , pn_source_inventory_item_id => :p_source_inventory_item_id '||
939 ' , pn_set_process_id => :p_set_process_id '||
940 ' , pv_called_from => :p_called_from '||
941 ' ); '||
942 'END; '
943 USING IN p_action_type
944 , IN p_organization_id
945 , IN p_inventory_item_id
946 , IN p_source_organization_id
947 , IN p_source_inventory_item_id
948 , IN p_set_process_id
949 , IN p_called_from ;
950
951 END IF;
952 EXCEPTION
953 WHEN OTHERS THEN
954 NULL;
955 END Invoke_JAI_API;
956
957 -- -------------------------------------------------------------------------
958 -- API Name: Sync IP Intermedia Index
959 --
960 -- Type: Private
961 --
962 -- Description: Calls IProcurement Intermedia index rebuild after
963 -- commiting as it is a DDL.
964 -----------------------------------------------------------------------------
965
966 Procedure Sync_IP_IM_Index IS
967 l_api_version NUMBER;
968 x_ret_status VARCHAR2(1);
969
970 l_inv_debug_level NUMBER := INVPUTLI.get_debug_level; --Bug: 4667452
971 BEGIN
972 --6531763: Adding ICX install check.
973 IF ((INV_ITEM_UTIL.Appl_Inst_ICX <> 0)
974 AND (INV_ITEM_UTIL.Object_Exists(
975 p_object_type => 'PACKAGE',
976 p_object_name => 'ICX_CAT_POPULATE_ITEM_GRP') = 'Y'))
977 THEN
978
979 l_api_version := 1.0;
980 EXECUTE IMMEDIATE
981 ' BEGIN '||
982 ' ICX_CAT_POPULATE_ITEM_GRP.rebuildIPIntermediaIndex( '||
983 ' p_api_version => :l_api_version '||
984 ' ,x_return_status => :x_ret_status); '||
985 ' END;'
986 USING l_api_version, OUT x_ret_status;
987 END IF;
988 EXCEPTION
989 WHEN OTHERS THEN
990 IF l_inv_debug_level IN(101, 102) THEN
991 INVPUTLI.info('INVVEVEB: Exception in Sync_IP_IM_Index');
992 END IF;
993
994 END Sync_IP_IM_Index;
995
996 --Bug: 4880971
997 PROCEDURE Invoke_JG_ZZ_API(
998 p_organization_id IN MTL_SYSTEM_ITEMS.ORGANIZATION_ID%TYPE
999 ,p_country_code OUT NOCOPY VARCHAR2
1000 ,p_product_code OUT NOCOPY VARCHAR2)
1001 IS
1002 l_jg_zz_object VARCHAR2(1);
1003 l_operating_unit ORG_ORGANIZATION_DEFINITIONS.OPERATING_UNIT%TYPE;
1004 BEGIN
1005 l_jg_zz_object := INV_ITEM_UTIL.Object_Exists(
1006 p_object_type => 'PACKAGE',
1007 p_object_name => 'JG_ZZ_SHARED_PKG');
1008
1009 IF l_jg_zz_object = 'Y' THEN
1010
1011 --Perf Issue : Replaced org_organizations_definitions view.
1012 select DECODE(ORG_INFORMATION_CONTEXT,
1013 'Accounting Information',
1014 TO_NUMBER(ORG_INFORMATION3),
1015 TO_NUMBER(NULL))
1016 into l_operating_unit
1017 from hr_organization_information
1018 where organization_id = p_organization_id
1019 and (org_information_context|| '') ='Accounting Information';
1020
1021 EXECUTE IMMEDIATE
1022 'BEGIN '||
1023 ' :p_country_code := JG_ZZ_SHARED_PKG.GET_COUNTRY( p_org_id => :l_operating_unit ); '||
1024 'END; '
1025 USING OUT p_country_code, IN l_operating_unit;
1026
1027 EXECUTE IMMEDIATE
1028 'BEGIN '||
1029 ' :p_product_code := JG_ZZ_SHARED_PKG.GET_PRODUCT( p_org_id => :l_operating_unit ); '||
1030 'END; '
1031 USING OUT p_product_code, IN l_operating_unit;
1032
1033 END IF;
1034 EXCEPTION
1035 WHEN OTHERS THEN
1036 NULL;
1037 END Invoke_JG_ZZ_API;
1038
1039
1040 END inv_item_events_pvt;