[Home] [Help]
PACKAGE BODY: APPS.ITG_SYNCITEMINBOUND_PVT
Source
1 PACKAGE BODY ITG_SyncItemInbound_PVT AS
2 /* ARCS: $Header: itgvsiib.pls 120.5 2006/01/23 03:48:14 bsaratna noship $
3 * CVS: itgvsiib.pls,v 1.29 2002/12/23 21:20:30 ecoe Exp
4 */
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'ITG_SyncItemInbound_PVT';
7 l_debug_level NUMBER := to_number(nvl(fnd_profile.value('CLN_DEBUG_LEVEL'), '5'));
8 g_action VARCHAR2(100) := 'Item Synchronization';
9
10 /* Private functions and procedures */
11
12
13 /* Apps Business Object API call: ITG_SyncItemInbound_PVT.Sync_Item */
14 PROCEDURE Sync_Item(
15 x_return_status OUT NOCOPY VARCHAR2,
16 x_msg_count OUT NOCOPY NUMBER,
17 x_msg_data OUT NOCOPY VARCHAR2,
18
19 /* See original code/comment in poisibio.sql (bug #1672639). */
20 p_syncind IN VARCHAR2, /* (1) */
21 p_org_id IN NUMBER, /* poentity */
22 p_hazrdmatl IN VARCHAR2,
23 p_create_date IN DATE := NULL,
24 p_item IN VARCHAR2,
25 p_uom IN VARCHAR2,
26 p_itemdesc IN VARCHAR2,
27 p_itemstatus IN VARCHAR2,
28 p_itemtype IN VARCHAR2,
29 p_rctrout IN VARCHAR2, /* ref_rctrout */
30 p_commodity1 IN VARCHAR2,
31 p_commodity2 IN VARCHAR2
32 )
33 IS
34 PRAGMA AUTONOMOUS_TRANSACTION; /* enable commit/rollback */
35 /* Business object constants. */
36 l_api_name CONSTANT VARCHAR2(30) := 'Sync_Item';
37 l_api_version CONSTANT NUMBER := 1.0;
38
39 l_itemarray FND_FLEX_EXT.SegmentArray;
40 l_hazard_class_id po_hazard_classes.hazard_class_id%TYPE;
41 l_nested_exception EXCEPTION;
42
43
44 l_create_date DATE;
45
46 l_num NUMBER;
47 l_set_process_id NUMBER;
48 l_org_tmp NUMBER;
49 l_ccm_request_id NUMBER;
50
51 l_sii_rowid ROWID;
52 l_ici_rowid ROWID;
53
54 l_bool BOOLEAN;
55
56 l_syncind VARCHAR2(1);
57 l_sync_tmp VARCHAR2(1);
58 l_phase VARCHAR2(400);
59 l_status VARCHAR2(400);
60 l_dev_phase VARCHAR2(400);
61 l_dev_status VARCHAR2(400);
62 l_reap_status VARCHAR2(20);
63 l_errmsg VARCHAR2(4000);
64 BEGIN
65 /* Initialize return status */
66 x_return_status := FND_API.G_RET_STS_SUCCESS;
67 g_action := 'Item synchronization';
68
69 IF (l_Debug_Level <= 2) THEN
70 itg_debug_pub.Add('--- Entering Sync_Item ---' ,2);
71 END IF;
72
73 BEGIN
74 ITG_Debug.setup(
75 p_reset => TRUE,
76 p_pkg_name => G_PKG_NAME,
77 p_proc_name => l_api_name
78 );
79
80
81 -- in wrapper now > FND_MSG_PUB.Initialize;
82 IF (l_Debug_Level <= 1) THEN
83 itg_debug_pub.Add('SI - Top of procedure.' ,1);
84 itg_debug_pub.Add('SI - p_syncind' ||p_syncind,1);
85 itg_debug_pub.Add('SI - p_org_id' ||p_org_id,1);
86 itg_debug_pub.Add('SI - p_hazrdmatl'||p_hazrdmatl,1);
87 itg_debug_pub.Add('SI - p_create_date'||p_create_date,1);
88 itg_debug_pub.Add('SI - p_item' ||p_item,1);
89 itg_debug_pub.Add('SI - p_uom' ||p_uom,1);
90 itg_debug_pub.Add('SI - p_itemdesc' ||p_itemdesc,1);
91 itg_debug_pub.Add('SI - p_itemstatus'||p_itemstatus,1);
92 itg_debug_pub.Add('SI - p_itemtype' ||p_itemtype,1);
93 itg_debug_pub.Add('SI - p_rctrout' ||p_rctrout,1);
94 itg_debug_pub.Add('SI - p_commodity1'||p_commodity1,1);
95 itg_debug_pub.Add('SI - p_commodity2'||p_commodity2,1);
96 END IF;
97
98
99 l_syncind := UPPER(p_syncind);
100
101 /* Validation block */
102 DECLARE
103 l_param_name VARCHAR2(30) := NULL;
104 l_param_value VARCHAR2(2000) := 'NULL';
105 l_cnt NUMBER;
106 BEGIN
107 g_action := 'Item-sync parameter validation';
108
109 IF p_item IS NULL THEN
110 l_param_name := 'ITEM';
111 ELSIF l_syncind NOT IN ('A', 'C') THEN
112 l_param_name := 'SYNCIND';
113 l_param_value := l_syncind;
114 ELSIF p_uom IS NULL THEN
115 l_param_name := 'UOM';
116 END IF;
117
118 IF l_param_name IS NOT NULL THEN
119 ITG_MSG.missing_element_value(l_param_name, l_param_value);
120 RAISE FND_API.G_EXC_ERROR;
121 END IF;
122
123 SELECT count(1)
124 INTO l_cnt
125 FROM mtl_units_of_measure_vl
126 WHERE UPPER(uom_code) = UPPER(p_uom);
127
128 IF l_cnt = 0 THEN
129 ITG_MSG.uom_not_found(p_uom);
130 RAISE FND_API.G_EXC_ERROR;
131 END IF;
132 END;
133
134 IF (l_Debug_Level <= 1) THEN
135 itg_debug_pub.Add('SI - Completed validations' ,1);
136 END IF;
137
138 /* TBD: bug comment from original */
139
140 /* Bug #1672639 (Forward port of 11.0 bug#1588435). Work around
141 until we can retrieve proper delimiter from flex meta data.
142 Treats all items as single segment. This code was replaced:
143
144 l_num := fnd_flex_ext.breakup_segments(
145 :new.itemheader.item, '-', l_itemarray);
146
147 with the following 2 lines:
148 */
149
150 l_itemarray(1) := p_item;
151 l_num := 1;
152
153 /* Fill out rest of array with NULLs (or insert will fail) */
154 FOR i IN 1 .. 20 LOOP
155 IF i > l_num THEN
156 l_itemarray(i) := NULL;
157 END IF;
158 END LOOP;
159
160 IF p_hazrdmatl IS NOT NULL THEN
161 IF (l_Debug_Level <= 1) THEN
162 itg_debug_pub.Add('SI - Loading hazard class ID',1 );
163 END IF;
164
165 BEGIN
166 SELECT hazard_class_id
167 INTO l_hazard_class_id
168 FROM po_hazard_classes
169 WHERE upper(hazard_class) = upper(p_hazrdmatl);
170 EXCEPTION
171 WHEN NO_DATA_FOUND THEN
172 IF (l_Debug_Level <= 1) THEN
173 itg_debug_pub.Add('SI - No hazard class ID found',1);
174 END IF;
175
176 itg_msg.no_hazard_class(p_hazrdmatl);
177 RAISE FND_API.G_EXC_ERROR;
178 END;
179 END IF;
180
181 IF (p_commodity1 is null and p_commodity2 is not null)
182 or (p_commodity2 is null and p_commodity1 is not null) THEN
183 itg_msg.item_commodity_ign;
184 -- continue processing.
185 END IF;
186
187 l_create_date := NVL(p_create_date, SYSDATE);
188
189 IF (l_Debug_Level <= 1) THEN
190 itg_debug_pub.Add('SI - Calling Get_ProcessSetId', 1);
191 END IF;
192
193 -- commented call to ITG_BatchManagement_PVT.Get_ProcessSetId
194 l_set_process_id := Get_NextProcessSetId;
195 IF (l_Debug_Level <= 1) THEN
196 itg_debug_pub.Add('l_set_process_id' ||l_set_process_id, 1);
197 itg_debug_pub.Add( 'Inserting into mtl_system_items_interface',1);
198 END IF;
199
200 g_action := 'Item-interface insert';
201
202 INSERT INTO mtl_system_items_interface (
203 set_process_id,
204 creation_date,
205 last_update_date,
206 last_updated_by,
207 hazard_class_id,
208 transaction_type,
209 process_flag,
210 primary_uom_code,
211 description,
212 segment1,
213 segment2,
214 segment3,
215 segment4,
216 segment5,
217 segment6,
218 segment7,
219 segment8,
220 segment9,
221 segment10,
222 segment11,
223 segment12,
224 segment13,
225 segment14,
226 segment15,
227 segment16,
228 segment17,
229 segment18,
230 segment19,
231 segment20,
232 organization_id,
233 inventory_item_status_code,
234 template_id,
235 receiving_routing_id,
236 item_type
237 ) VALUES (
238 l_set_process_id,
239 l_create_date,
240 l_create_date,
241 FND_GLOBAL.user_id,
242 l_hazard_class_id,
243 DECODE(l_syncind, 'A','CREATE', 'C','UPDATE'),
244 1,
245 UPPER(p_uom),
246 p_itemdesc,
247 l_itemarray(1),
248 l_itemarray(2),
249 l_itemarray(3),
250 l_itemarray(4),
251 l_itemarray(5),
252 l_itemarray(6),
253 l_itemarray(7),
254 l_itemarray(8),
255 l_itemarray(9),
256 l_itemarray(10),
257 l_itemarray(11),
258 l_itemarray(12),
259 l_itemarray(13),
260 l_itemarray(14),
261 l_itemarray(15),
262 l_itemarray(16),
263 l_itemarray(17),
264 l_itemarray(18),
265 l_itemarray(19),
266 l_itemarray(20),
267 p_org_id,
268 p_itemstatus,
269 p_itemtype,
270 p_rctrout,
271 NULL
272 ) RETURNING rowid INTO l_sii_rowid;
273
274 g_action := 'Item-categories interface insert';
275
276 IF p_commodity1 IS NOT NULL AND
277 p_commodity2 IS NOT NULL THEN
278
279 IF (l_Debug_Level <= 1) THEN
280 itg_debug_pub.Add('SI - Inserting into mtl_items_categories_interface', 1);
281 END IF;
282
283 INSERT INTO mtl_item_categories_interface(
284 set_process_id,
285 item_number,
286 creation_date,
287 last_update_date,
288 last_updated_by,
289 organization_id,
290 transaction_type,
291 category_name,
292 category_set_name,
293 process_flag
294 ) VALUES (
295 l_set_process_id,
296 p_item,
297 l_create_date,
298 l_create_date,
299 FND_GLOBAL.user_id,
300 p_org_id,
301 'CREATE',
302 p_commodity1,
303 p_commodity2,
304 1
305 )RETURNING rowid INTO l_ici_rowid;
306 END IF;
307
308 IF (l_Debug_Level <= 1) THEN
309 itg_debug_pub.Add('Committing item interfaces insert.', 1);
310 END IF;
311 COMMIT;
312
313 -- comment call to ITG_BatchManagement_PVT.Added_RequestItem
314 -- move code from Batch mgmt to here ..
315 g_action := 'Item import program execution';
316 l_ccm_request_id:= Start_BatchProcess(l_set_process_id,l_syncind,p_org_id);
317 COMMIT;
318
319
320
321 IF l_ccm_request_id <= 0 THEN
322 itg_msg.inv_cp_fail('NONE','NONE');
323 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
324 END IF;
325
326 -- This was pulled from the original sync item code
327 IF (l_Debug_Level <= 1) THEN
328 itg_debug_pub.Add( 'Waiting for concurrent request.', 1);
329 itg_debug_pub.Add( 'l_ccm_request_id'||l_ccm_request_id,1);
330 END IF;
331
332 l_bool := FND_CONCURRENT.wait_for_request(l_ccm_request_id, 30, 600,
333 l_phase, l_status, l_dev_phase, l_dev_status, l_errmsg);
334
335 IF (l_Debug_Level <= 1) THEN
336 itg_debug_pub.Add( 'Results from concurrent request.', 1);
337 itg_debug_pub.Add( 'l_phase' ||l_phase,1);
338 itg_debug_pub.Add( 'l_status' ||l_status,1);
339 itg_debug_pub.Add( 'l_dev_phase'||l_dev_phase,1);
340 itg_debug_pub.Add( 'l_dev_status'||l_dev_status,1);
341 itg_debug_pub.Add( 'l_errmsg' ||l_errmsg,1);
342 END IF;
343
344 l_reap_status := FND_API.G_RET_STS_SUCCESS;
345
346 -- concurrent program has not completed
347 -- error the collaboration, loggin ccm_id to the
348 -- TODO: What are all the statuses and how do we react.
349 IF upper(l_dev_phase) <> 'COMPLETE' THEN
350 itg_msg.item_import_pending(l_ccm_request_id, l_dev_status, l_dev_phase);
351 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
352 END IF;
353
354 IF upper(l_dev_status) <> 'NORMAL' THEN
355 itg_msg.inv_cp_fail(l_dev_status,l_dev_phase);
356 -- do,not raise exception, try to move ahead and find error records
357 END IF;
358
359 IF l_bool THEN
360 l_reap_status := Reap_BatchResults(l_ccm_request_id,l_sii_rowid,l_ici_rowid);
361 ELSE
362 g_action := 'Item import program status-retrival';
363 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
364 END IF;
365
366 IF l_reap_status <> FND_API.G_RET_STS_SUCCESS THEN
367 IF (l_Debug_Level <= 1) THEN
368 itg_debug_pub.Add('SI - failure in Items Import concurrent request',1);
369 END IF;
370
371 RAISE FND_API.G_EXC_ERROR;
372 END IF;
373
374 IF (l_Debug_Level <= 1) THEN
375 itg_debug_pub.Add('Committing work.',1);
376 END IF;
377 COMMIT;
378
379 IF (l_Debug_Level <= 2) THEN
380 itg_debug_pub.Add('--- Exiting Sync_Item ---',2);
381 END IF;
382 EXCEPTION
383 WHEN FND_API.G_EXC_ERROR THEN
384 ROLLBACK;
385 x_return_status := FND_API.G_RET_STS_ERROR;
386 ITG_msg.checked_error(g_action);
387 IF (l_Debug_Level <= 6) THEN
388 itg_debug_pub.Add('--- Exiting Sync_Item ---ERROR',6);
389 END IF;
390
391 WHEN OTHERS THEN
392 ROLLBACK;
393 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
394 ITG_Debug.msg('Unexpected error (Exchange-rate sync) - ' || substr(SQLERRM,1,255),true);
395 ITG_msg.unexpected_error(g_action);
396 IF (l_Debug_Level <= 6) THEN
397 itg_debug_pub.Add('--- Exiting Sync_Item ---OTHER ERROR',6);
398 END IF;
399 END;
400 -- Removed FND_MSG_PUB.Count_And_Get
401 END Sync_Item;
402
403
404 FUNCTION Get_NextProcessSetId RETURN NUMBER IS
405 l_process_set_id NUMBER;
406 BEGIN
407 SELECT mtl_system_items_intf_sets_s.nextval
408 INTO l_process_set_id
409 FROM dual;
410 RETURN l_process_set_id;
411 END Get_NextProcessSetId;
412
413
414
415 /* Returns CCM request id. */
416 -- decode syncind, set org conbtext
417 FUNCTION Start_BatchProcess(
418 p_process_set_id NUMBER,
419 p_syncind VARCHAR2,
420 p_org_id VARCHAR2
421 ) RETURN NUMBER IS
422 l_upd_flag NUMBER;
423 BEGIN
424 -- removed call to get process set info
425 -- get the parameters directly
426 -- removed call to batch mgmt.
427
428 IF (l_Debug_Level <= 2) THEN
429 itg_debug_pub.Add('--- Entering Start_BatchProcess ---',2);
430 END IF;
431
432 IF p_syncind = 'A' THEN
433 l_upd_flag := 1;
434 ELSE
435 l_upd_flag := 2;
436 END IF;
437
438 BEGIN
439 FND_Client_Info.set_org_context(p_org_id);
440 MO_GLOBAL.set_policy_context('S', p_org_id); -- MOAC
441 EXCEPTION
442 WHEN OTHERS THEN
443 ITG_Debug.add_exc_error(G_PKG_NAME, 'Start_BatchProcess');
444 itg_msg.invalid_org(p_org_id);
445 RAISE FND_API.G_EXC_ERROR;
446 END;
447
448 IF (l_Debug_Level <= 2) THEN
449 itg_debug_pub.Add('--- Exting Start_BatchProcess ---',2);
450 END IF;
451
452 RETURN FND_REQUEST.submit_request(
453 application => 'INV',
454 program => 'INCOIN',
455 argument1 => p_org_id, /* org_id */
456 argument2 => 2, /* all_org (1 = all, 2 = org_id) */
457 argument3 => 1, /* val_item_flag */
458 argument4 => 1, /* pro_item_flag */
459 argument5 => 2, /* del_rec_flag */
460 argument6 => p_process_set_id, /* process_set */
461 argument7 => l_upd_flag /* create_update (1 = cr, 2 = up) */
462 );
463 END Start_BatchProcess;
464
465
466 --Assuming following process_flag
467 --Tested with INV:Txn processing mode as immediate
468 --Need to test with other modes
469 -- > import pending (1,2)
470 -- > import success (6,7)
471 -- > import failure (3,4,5?)
472 FUNCTION Reap_BatchResults(
473 p_request_id NUMBER,
474 p_msii_rid ROWID,
475 p_mici_rid ROWID) RETURN VARCHAR2 IS
476 l_process_flag VARCHAR2(30);
477 BEGIN
478 IF (l_Debug_Level <= 2) THEN
479 itg_debug_pub.Add('--- Entering Reap_BatchResults ---',2);
480 END IF;
481
482 IF (l_Debug_Level <= 1) THEN
483 itg_debug_pub.Add( 'p_request_id' ||p_request_id ,1);
484 itg_debug_pub.Add( 'p_msii_rid' ||p_msii_rid,1);
485 itg_debug_pub.Add( 'p_mici_rid' ||p_mici_rid,1);
486 END IF;
487
488 BEGIN
489 SELECT process_flag
490 INTO l_process_flag
491 FROM mtl_system_items_interface
492 WHERE rowid = p_msii_rid;
493 EXCEPTION
494 WHEN OTHERS THEN
495 l_process_flag := null;
496 END;
497
498 IF (l_Debug_Level <= 1) THEN
499 itg_debug_pub.Add('process_flag '||l_process_flag ,1);
500 END IF;
501
502 -- if item procssing not complete, log appropriate message and return
503 IF l_process_flag IN ('1','2') THEN
504 itg_msg.item_import_pending(p_request_id,null,null);
505 return FND_API.G_RET_STS_ERROR;
506 END IF;
507
508 IF l_process_flag NOT IN ('6','7') OR l_process_flag IS NULL THEN
509 ITG_Debug.msg('Item-import errored out, request-id:' || p_request_id,TRUE);
510 itg_msg.item_import_errors;
511 error_transactions(p_request_id,'MTL_SYSTEM_ITEMS_INTERFACE');
512 return FND_API.G_RET_STS_ERROR;
513 END IF;
514
515 IF p_mici_rid IS NOT NULL THEN
516 BEGIN
517 SELECT process_flag
518 INTO l_process_flag
519 FROM mtl_item_categories_interface
520 WHERE rowid = p_mici_rid;
521 EXCEPTION
522 WHEN OTHERS THEN
523 l_process_flag := null;
524 END;
525
526 -- if item category procssing not complete, log appropriate message and return
527 -- since item interface import is success, make the txn/collaboration succesful
528 IF (l_Debug_Level <= 1) THEN
529 itg_debug_pub.Add('process_flag' || l_process_flag ,1);
530 END IF;
531
532 IF l_process_flag IN ('1','2') THEN
533 itg_msg.itemcat_import_pending(p_request_id);
534 return FND_API.G_RET_STS_SUCCESS;
535 END IF;
536
537 IF l_process_flag NOT IN ('6','7') OR l_process_flag IS NULL THEN
538 ITG_Debug.msg('Item-category import errored out, request-id:' || p_request_id,TRUE);
539 itg_msg.mici_only_failed;
540 error_transactions(p_request_id,'MTL_ITEM_CATEGORIES_INTERFACE');
541 return FND_API.G_RET_STS_SUCCESS;
542 END IF;
543 END IF;
544 IF (l_Debug_Level <= 2) THEN
545 itg_debug_pub.Add('--- Exting Reap_BatchResults ---',2);
546 END IF;
547 -- code reaches here iff both item and category import are success
548 return FND_API.G_RET_STS_SUCCESS;
549 END;
550
551 --TODO: What are the process_flag values for
552 -- > Is txn id always returned??
553 -- > Should we use request-id, table name combination instead?
554 -- > import failure
555 -- > message_name is it directly translatable?
556 PROCEDURE error_transactions(
557 p_request_id VARCHAR2,
558 p_table_name VARCHAR2
559 )
560 IS
561 CURSOR error_messages(p_request_id VARCHAR2,p_table_name VARCHAR2)
562 IS
563 SELECT message_name, substr(error_message,1,2000)
564 FROM mtl_interface_errors
565 WHERE request_id = p_request_id
566 AND upper(table_name) = upper(p_table_name);
567
568 l_err_msg VARCHAR2(4000);
569 l_msg_name VARCHAR2(100);
570 BEGIN
571
572 IF (l_Debug_Level <= 2) THEN
573 itg_debug_pub.Add('--- Entering error_transactions ---',2);
574 END IF;
575
576 select count(*) into l_err_msg
577 from mtl_interface_errors
578 where request_id = p_request_id and
579 upper(table_name) = upper(p_table_name);
580
581 OPEN error_messages(p_request_id,p_table_name);
582
583 LOOP
584 FETCH error_messages INTO l_msg_name, l_err_msg;
585 EXIT WHEN error_messages%NOTFOUND;
586
587 IF (l_Debug_Level <= 1) THEN
588 itg_debug_pub.Add('Found an error record' ,1);
589 itg_debug_pub.Add('l_msg_name' || l_msg_name ,1);
590 itg_debug_pub.Add('l_err_msg ' || l_err_msg ,1);
591 END IF;
592
593 IF l_err_msg IS NOT NULL THEN
594 itg_debug.msg(l_err_msg, TRUE);
595 END IF;
596
597 IF l_msg_name IS NOT NULL AND
598 length(itg_x_utils.getCBODDescMsg) < 1000 THEN
599 itg_x_utils.addCBODDescMsg(
600 p_msg_app => 'INV',
601 p_msg_code => l_msg_name,
602 p_token_vals => null,
603 p_translatable => TRUE,
604 p_reset => FALSE
605 );
606 END IF;
607 END LOOP;
608 IF (l_Debug_Level <= 2) THEN
609 itg_debug_pub.Add('--- Exiting error_transactions ---',2);
610 END IF;
611 /* no value in doing this, this table can be purged periodically */
612 --DELETE FROM mtl_interface_errors
613 --WHERE request_id = p_request_id
614
615 EXCEPTION
616 WHEN OTHERS THEN
617 itg_debug.msg('Error retrieving errors in item-import',TRUE);
618 IF (l_Debug_Level <= 6) THEN
619 itg_debug_pub.Add('--- Exiting error_transactions ---OTHER ERROR',6);
620 END IF;
621 -- No value in propogating execption
622 END;
623
624 END ITG_SyncItemInbound_PVT;