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