[Home] [Help]
PACKAGE BODY: APPS.INV_COPY_ITEM_CP
Source
1 PACKAGE BODY INV_COPY_ITEM_CP AS
2 /* $Header: INVITCPB.pls 120.12 2011/06/01 09:09:07 maychen ship $ */
3 --======================================================================
4 -- GLOBALS
5 --======================================================================
6 TYPE g_request_tbl_type IS TABLE OF NUMBER
7 INDEX BY BINARY_INTEGER;
8
9 G_SLEEP_TIME NUMBER := 15;
10
11 G_MODULE_NAME CONSTANT VARCHAR2(100) := 'inv.plsql.INV_COPY_ITEM_CP.';
12
13 --=============================================
14 -- GLOBAL VARIABLES
15 --=============================================
16
17 g_debug_level NUMBER := NULL;
18 g_proc_level NUMBER := NULL;
19 g_unexp_level NUMBER := NULL;
20 g_excep_level NUMBER := NULL;
21 g_statement_level NUMBER := NULL;
22
23 PROCEDURE Init;
24
25 --========================================================================
26 -- PROCEDURE : Copy_Items
27 -- TYPE : PRIVATE
28 -- PARAMETERS: p_organization_id IN an organization
29 -- p_set_process_id IN Set process ID
30 -- x_workers IN OUT NOCOPY workers' request ID
31 -- p_request_count IN max worker number
32 -- p_copy_eng_items IN 'Y' or 'N' flag for
33 -- coping engineering items
34 -- p_copy_base_models IN 'Y' or 'N' flag for
35 -- coping base models
36 -- COMMENT : This procedure submits the Item Import concurrent program.
37 -- Before submitting the request, it verifies that there are
38 -- enough workers available and wait for the completion of one
39 -- if necessary.
40 --=========================================================================
41 PROCEDURE Copy_Items
42 (x_return_message OUT NOCOPY VARCHAR2
43 ,x_return_status OUT NOCOPY VARCHAR2
44 ,p_source_org_id IN NUMBER
45 ,p_target_org_id IN NUMBER
46 ,p_validate IN VARCHAR2
47 ,p_copy_eng_items IN VARCHAR2
48 ,p_copy_base_models IN VARCHAR2
49 );
50
51 --======================================================================
52 --START OF PACKAGE BODY.
53 --======================================================================
54
55
56 --========================================================================
57 -- FUNCTION : Has_Worker_Completed PRIVATE
58 -- PARAMETERS: p_request_id IN NUMBER
59 -- RETURNS : BOOLEAN
60 -- COMMENT : Accepts a request ID. TRUE if the corresponding worker
61 -- has completed; FALSE otherwise
62 --=========================================================================
63 FUNCTION Has_Worker_Completed
64 ( p_request_id IN NUMBER
65 )
66 RETURN BOOLEAN
67 IS
68 l_count NUMBER;
69 l_result BOOLEAN;
70 l_function_name CONSTANT VARCHAR2(30) := 'Has_Worker_Completed';
71 BEGIN
72 Init;
73 IF (g_proc_level >= g_debug_level)
74 THEN
75 FND_LOG.string(g_proc_level
76 , G_MODULE_NAME || l_function_name || '.begin'
77 ,'enter procedure'
78 );
79 END IF;
80 SELECT COUNT(*)
81 INTO l_count
82 FROM fnd_concurrent_requests
83 WHERE request_id = p_request_id
84 AND phase_code = 'C';
85
86 IF l_count = 1
87 THEN
88 l_result := TRUE;
89 ELSE
90 l_result := FALSE;
91 END IF;
92 IF (g_proc_level >= g_debug_level)
93 THEN
94 FND_LOG.string(g_proc_level
95 , G_MODULE_NAME || l_function_name || '.end'
96 ,'exit function.'
97 );
98 END IF;
99 RETURN l_result;
100 END Has_Worker_Completed;
101
102 --========================================================================
103 -- PROCEDURE : Wait_For_Worker PRIVATE
104 -- PARAMETERS: p_workers IN workers' request ID
105 -- x_worker_idx OUT position in p_workers of the
106 -- completed worked
107 -- COMMENT : This procedure polls the submitted workers and suspend
108 -- the program till the completion of one of them; it returns
109 -- the completed worker through x_worker_idx
110 --=========================================================================
111 PROCEDURE Wait_For_Worker
112 ( p_workers IN g_request_tbl_type
113 , x_worker_idx OUT NOCOPY BINARY_INTEGER
114 )
115 IS
116 l_done BOOLEAN;
117 l_procedure_name CONSTANT VARCHAR2(30) := 'Wait_For_Worker';
118 BEGIN
119 Init;
120 IF (g_proc_level >= g_debug_level)
121 THEN
122 FND_LOG.string(g_proc_level
123 , G_MODULE_NAME || l_procedure_name || '.begin'
124 ,'enter procedure'
125 );
126 END IF;
127 l_done := FALSE;
128 WHILE (NOT l_done)
129 LOOP
130
131 FOR l_Idx IN 1..p_workers.COUNT
132
133 LOOP
134 IF Has_Worker_Completed(p_workers(l_Idx))
135 THEN
136 l_done := TRUE;
137 x_worker_idx := l_Idx;
138 EXIT;
139 END IF;
140
141 END LOOP;
142
143 IF (NOT l_done)
144 THEN
145 DBMS_LOCK.sleep(G_SLEEP_TIME);
146 END IF;
147
148 END LOOP;
149 IF (g_proc_level >= g_debug_level)
150 THEN
151 FND_LOG.string(g_proc_level
152 , G_MODULE_NAME || l_procedure_name || '.end'
153 ,'exit procedure.'
154 );
155 END IF;
156 END Wait_For_Worker;
157
158 --========================================================================
159 -- PROCEDURE : Wait_For_All_Workers PRIVATE
160 -- PARAMETERS: p_workers IN workers' request ID
161 -- COMMENT : This procedure polls the submitted workers and suspend
162 -- the program till the completion of all of them.
163 --=========================================================================
164 PROCEDURE Wait_For_All_Workers
165 ( p_workers IN g_request_tbl_type)
166 IS
167 l_done BOOLEAN;
168 l_procedure_name CONSTANT VARCHAR2(30) := 'Wait_For_All_Workers';
169 BEGIN
170 Init;
171 IF (g_proc_level >= g_debug_level)
172 THEN
173 FND_LOG.string(g_proc_level
174 , G_MODULE_NAME || l_procedure_name || '.begin'
175 ,'enter procedure'
176 );
177 END IF;
178
179 l_done := FALSE;
180 WHILE (NOT l_done)
181 LOOP
182 l_done := TRUE;
183
184 FOR l_Idx IN 1..p_workers.COUNT
185 LOOP
186 IF NOT Has_Worker_Completed(p_workers(l_Idx))
187 THEN
188 l_done := FALSE;
189 EXIT;
190 END IF;
191 END LOOP;
192
193 IF (NOT l_done)
194 THEN
195 DBMS_LOCK.sleep(G_SLEEP_TIME);
196 END IF;
197 END LOOP;
198 IF (g_proc_level >= g_debug_level)
199 THEN
200 FND_LOG.string(g_proc_level
201 , G_MODULE_NAME || l_procedure_name || '.end'
202 ,'exit procedure.'
203 );
204 END IF;
205 END Wait_For_All_Workers;
206
207 --========================================================================
208 -- PROCEDURE : Submit_Item_Import PRIVATE
209 -- PARAMETERS: p_organization_id IN an organization
210 -- p_set_process_id IN Set process ID
211 -- x_workers IN OUT NOCOPY workers' request ID
212 -- p_request_count IN max worker number
213 -- COMMENT : This procedure submits the Item Import concurrent program.
214 -- Before submitting the request, it verifies that there are
215 -- enough workers available and wait for the completion of one
216 -- if necessary.
217 -- The list of workers' request ID is updated.
218 --=========================================================================
219 PROCEDURE Submit_Item_Import
220 ( p_organization_id IN NUMBER
221 --myerrams, Bug: 5964347. Added source org id parameter to Submit_Item_Import Procedure.
222 --This parameter will be passed as an additional parameter to INCOIN
223 --in order to avoid the call to INVPUTLI.assign_master_defaults in INCOIN.
224 , p_source_org_id IN NUMBER
225 , p_set_process_id IN NUMBER
226 , p_validate_items IN VARCHAR2
227 , x_workers IN OUT NOCOPY g_request_tbl_type
228 , x_return_status OUT NOCOPY VARCHAR2
229 , x_return_message OUT NOCOPY VARCHAR2
230 , p_request_count IN NUMBER
231 )
232 IS
233 l_worker_idx BINARY_INTEGER;
234 l_request_id NUMBER;
235
236 l_procedure_name CONSTANT VARCHAR2(30) := 'Submit_Item_Import';
237 l_submit_failure_exc EXCEPTION;
238 BEGIN
239 Init;
240 IF (g_proc_level >= g_debug_level)
241 THEN
242 FND_LOG.string(g_proc_level
243 , G_MODULE_NAME || l_procedure_name || '.begin'
244 ,'enter procedure'
245 );
246 END IF;
247
248 IF x_workers.COUNT < p_request_count
249 THEN
250 -- number of workers submitted so far does not exceed the maximum
251 -- number of workers allowed
252 l_worker_idx := x_workers.COUNT + 1;
253 ELSE
254 -- need to wait for a submitted worker to finish
255 Wait_For_Worker
256 ( p_workers => x_workers
257 , x_worker_idx => l_worker_idx
258 );
259 END IF;
260 IF NOT FND_REQUEST.Set_Options
261 ( implicit => 'WARNING'
262 , protected => 'YES'
263 )
264 THEN
265 RAISE l_submit_failure_exc;
266 END IF;
267
268 IF (g_statement_level >= g_debug_level)
269 THEN
270 FND_LOG.string(g_statement_level
271 , G_MODULE_NAME || l_procedure_name
272 ,'Before Calling INCOIN '
273 );
274 END IF;
275 commit;
276
277 x_workers(l_worker_idx):= FND_REQUEST.Submit_Request
278 ( application => 'INV'
279 , program => 'INCOIN'
280 , argument1 => p_organization_id
281 , argument2 => 1
282 , argument3 => p_validate_items
283 , argument4 => 1
284 , argument5 => 1
285 , argument6 => p_set_process_id
286 , argument7 => 1
287 , argument8 => p_source_org_id --myerrams, Bug: 5964347
288 );
289
290 IF (g_statement_level >= g_debug_level)
291 THEN
292 FND_LOG.string(g_statement_level
293 , G_MODULE_NAME || l_procedure_name
294 ,'After Calling INCOIN '
295 );
296 END IF;
297
298 IF x_workers(l_worker_idx) = 0
299 THEN
300 IF (g_excep_level >= g_debug_level)
301 THEN
302 FND_LOG.string(g_excep_level
303 , G_MODULE_NAME || l_procedure_name
304 ,'Item Open Interface Program (INCOIN) failed '
305 );
306 END IF;
307 RAISE l_submit_failure_exc;
308 END IF;
309 COMMIT;
310
311 x_return_status := FND_API.G_RET_STS_SUCCESS;
312 x_return_message := 'Submitted Item Copy request successfully';
313
314 IF (g_proc_level >= g_debug_level)
315 THEN
316 FND_LOG.string(g_proc_level
317 , G_MODULE_NAME || l_procedure_name || '.end'
318 ,'exit procedure.'
319 );
320 END IF;
321
322 EXCEPTION
323 WHEN l_submit_failure_exc THEN
324 FND_MESSAGE.Set_Name('INV', 'INV_UNABLE_TO_SUBMIT_CONC');
325 x_return_message := SUBSTR(FND_MESSAGE.Get, 1, 255);
326 x_return_status := FND_API.G_RET_STS_ERROR;
327 END Submit_Item_Import;
328
329 --========================================================================
330 -- PROCEDURE : Copy_Org_Items
331 -- PARAMETERS: p_organization_id IN an organization
332 -- p_set_process_id IN Set process ID
333 -- x_workers IN OUT NOCOPY workers' request ID
334 -- p_request_count IN max worker number
335 -- COMMENT : It calls Copy_Items in four steps to copy items:
336 -- 1. Copy Base Models of engineering items
337 -- 2. Copy children of Base Models engineering items
338 -- 3. Copy Base Models of non-engineering items
339 -- 4. Copy children of Base Models non-engineering items
340 -- Copy_Items further submits the Item Import concurrent program
341 -- after populating interface table for IOI.
342 --=========================================================================
343
344 PROCEDURE Copy_Org_Items
345 (x_return_message OUT NOCOPY VARCHAR2
346 ,x_return_status OUT NOCOPY VARCHAR2
347 ,p_source_org_id IN NUMBER
348 ,p_target_org_id IN NUMBER
349 ,p_validate IN VARCHAR2
350 )
351 IS
352 l_procedure_name CONSTANT VARCHAR2(30) := 'Copy_Org_Items';
353 BEGIN
354 Init;
355
356 IF (g_proc_level >= g_debug_level)
357 THEN
358 FND_LOG.string(g_proc_level
359 , G_MODULE_NAME || l_procedure_name || '.begin'
360 ,'enter procedure'
361 );
362 END IF;
363
364 IF (g_statement_level >= g_debug_level)
365 THEN
366 FND_LOG.string(g_statement_level
367 , G_MODULE_NAME || l_procedure_name
368 ,'Parameters : '
369 );
370 FND_LOG.string(g_statement_level
371 , G_MODULE_NAME || l_procedure_name
372 ,'p_source_org_id : '||p_source_org_id
373 );
374 FND_LOG.string(g_statement_level
375 , G_MODULE_NAME || l_procedure_name
376 ,'p_target_org_id : '||p_target_org_id
377 );
378 FND_LOG.string(g_statement_level
379 , G_MODULE_NAME || l_procedure_name
380 ,'p_validate : '||p_validate
381 );
382 END IF;
383
384 -- Copy Base Models of engineering items.
385 Copy_Items (x_return_message
386 ,x_return_status
387 ,p_source_org_id
388 ,p_target_org_id
389 ,p_validate
390 ,'Y'
391 ,'Y'
392 );
393
394 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
395 RETURN;
396 END IF;
397 IF (g_statement_level >= g_debug_level)
398 THEN
399 FND_LOG.string(g_statement_level
400 , G_MODULE_NAME || l_procedure_name
401 ,'Engineering Items copied where BaseItemId is NULL'
402 );
403 END IF;
404 -- Copy children of Base Models engineering items.
405 Copy_Items (x_return_message
406 ,x_return_status
407 ,p_source_org_id
408 ,p_target_org_id
409 ,p_validate
410 ,'Y'
411 ,'N'
412 );
413 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
414 RETURN;
415 END IF;
416 IF (g_statement_level >= g_debug_level)
417 THEN
418 FND_LOG.string(g_statement_level
419 , G_MODULE_NAME || l_procedure_name
420 ,'Engineering Items copied where BaseItemId is not NULL'
421 );
422 END IF;
423 -- Copy Base Models of non-engineering items.
424 Copy_Items (x_return_message
425 ,x_return_status
426 ,p_source_org_id
427 ,p_target_org_id
428 ,p_validate
429 ,'N'
430 ,'Y'
431 );
432 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
433 RETURN;
434 END IF;
435 IF (g_statement_level >= g_debug_level)
436 THEN
437 FND_LOG.string(g_statement_level
438 , G_MODULE_NAME || l_procedure_name
439 ,'Non Engineering Items copied where BaseItemId is NULL'
440 );
441 END IF;
442 -- Copy children of Base Models non-engineering items.
443 Copy_Items (x_return_message
444 ,x_return_status
445 ,p_source_org_id
446 ,p_target_org_id
447 ,p_validate
448 ,'N'
449 ,'N'
450 );
451 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
452 RETURN;
453 END IF;
454 IF (g_statement_level >= g_debug_level)
455 THEN
456 FND_LOG.string(g_statement_level
457 , G_MODULE_NAME || l_procedure_name
458 ,'Non Engineering Items copied where BaseItemId is not NULL'
459 );
460 END IF;
461
462 IF (g_proc_level >= g_debug_level)
463 THEN
464 FND_LOG.string(g_proc_level
465 , G_MODULE_NAME || l_procedure_name || '.end'
466 ,'exit procedure.'
467 );
468 END IF;
469
470 END Copy_Org_Items;
471
472
473 /* Private Helper Functions/Procedures */
474
475 --=============================================================================
476 -- PROCEDURE NAME: Init
477 -- TYPE : PRIVATE
478 -- PARAMETERS : None
479 -- DESCRIPTION : Initializes Global Variables.
480 -- EXCEPTIONS : None
481 --=============================================================================
482
483 PROCEDURE Init
484 IS
485 BEGIN
486
487 -- initializes the global variables for FND Log
488
489 IF g_proc_level IS NULL
490 THEN
491 g_proc_level := FND_LOG.LEVEL_PROCEDURE;
492 END IF; /* IF g_proc_level IS NULL */
493
494 IF g_unexp_level IS NULL
495 THEN
496 g_unexp_level := FND_LOG.LEVEL_UNEXPECTED;
497 END IF; /* IF g_unexp_level IS NULL */
498
499 IF g_excep_level IS NULL
500 THEN
501 g_excep_level := FND_LOG.LEVEL_EXCEPTION;
502 END IF; /* IF g_excep_level IS NULL */
503
504 IF g_statement_level IS NULL
505 THEN
506 g_statement_level := FND_LOG.LEVEL_STATEMENT;
507 END IF; /* IF g_statement_level IS NULL */
508
509 g_debug_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
510
511
512 END Init;
513
514 --========================================================================
515 -- PROCEDURE : Copy_Items
516 -- TYPE : PRIVATE
517 -- PARAMETERS: p_organization_id IN an organization
518 -- p_set_process_id IN Set process ID
519 -- x_workers IN OUT NOCOPY workers' request ID
520 -- p_request_count IN max worker number
521 -- p_copy_eng_items IN 'Y' or 'N' flag for
522 -- coping engineering items
523 -- p_copy_base_models IN 'Y' or 'N' flag for
524 -- coping base models
525 -- COMMENT : This procedure submits the Item Import concurrent program.
526 -- Before submitting the request, it verifies that there are
527 -- enough workers available and wait for the completion of one
528 -- if necessary.
529 -- The list of workers' request ID is updated.
530 --=========================================================================
531
532 PROCEDURE Copy_Items
533 (x_return_message OUT NOCOPY VARCHAR2
534 ,x_return_status OUT NOCOPY VARCHAR2
535 ,p_source_org_id IN NUMBER
536 ,p_target_org_id IN NUMBER
537 ,p_validate IN VARCHAR2
538 ,p_copy_eng_items IN VARCHAR2
539 ,p_copy_base_models IN VARCHAR2
540 )
541 IS
542 l_procedure_name CONSTANT VARCHAR2(30) := 'Copy_Items';
543 l_max_batch_size NUMBER;
544 l_workers_tbl g_request_tbl_type;
545 l_min_index BINARY_INTEGER;
546 l_max_index BINARY_INTEGER;
547 l_count NUMBER;
548 l_request_count NUMBER;
549 l_return_code INTEGER;
550 l_counter NUMBER := 1;
551 l_validate_items NUMBER;
552 l_rowid ROWID;
553 l_set_process_id mtl_system_items_interface.set_process_id%TYPE;
554 l_organization_id mtl_parameters.organization_id%TYPE;
555 l_expense_Account mtl_parameters.expense_Account%TYPE;
556 l_sales_account mtl_parameters.sales_account%TYPE;
557 l_cost_of_sales_account mtl_parameters.cost_of_sales_account%TYPE;
558 l_encumbrance_account mtl_parameters.encumbrance_account%TYPE;
559 l_process_flag mtl_item_revisions_interface.process_flag%TYPE; --myerrams, Bug: 4892069
560
561 -- Define a record type to contain required details from mtl_system_items_b.
562 TYPE mtl_system_items_rec IS RECORD
563 ( inventory_item_id mtl_system_items_b.inventory_item_id%Type
564 , purchasing_enabled_flag mtl_system_items_b.purchasing_enabled_flag%Type
565 , customer_order_enabled_flag mtl_system_items_b.customer_order_enabled_flag%Type
566 , internal_order_enabled_flag mtl_system_items_b.internal_order_enabled_flag%Type
567 , mtl_transactions_enabled_flag mtl_system_items_b.mtl_transactions_enabled_flag%Type
568 , stock_enabled_flag mtl_system_items_b.stock_enabled_flag%Type
569 , bom_enabled_flag mtl_system_items_b.bom_enabled_flag%Type
570 , build_in_wip_flag mtl_system_items_b.build_in_wip_flag%Type
571 , invoice_enabled_flag mtl_system_items_b.invoice_enabled_flag%Type
572 , source_organization_id mtl_system_items_b.source_organization_id%Type --myerrams, Bug: 5964347
573 , source_subinventory mtl_system_items_b.source_subinventory%Type --myerrams, Bug: 5964347
574 );
575
576 -- Define a table of above record type
577 TYPE mtl_system_items_tbl IS TABLE OF mtl_system_items_rec
578 INDEX BY BINARY_INTEGER;
579 l_mtl_system_items_tbl mtl_system_items_tbl;
580
581
582 -- Cursor to retrieve all the inventory items from the mtl_system_items_b
583 -- for the source organization id.
584 CURSOR c_item_cursor
585 IS
586 SELECT inventory_item_id
587 , purchasing_enabled_flag
588 , customer_order_enabled_flag
589 , internal_order_enabled_flag
590 , mtl_transactions_enabled_flag
591 , stock_enabled_flag
592 , bom_enabled_flag
593 , build_in_wip_flag
594 , invoice_enabled_flag
595 , source_organization_id --myerrams, Bug: 5964347
596 , source_subinventory --myerrams, Bug: 5964347
597 FROM mtl_system_items_b
598 WHERE organization_id = p_source_org_id
599 AND ((base_item_id is null and p_copy_base_models = 'Y') or (base_item_id is not null and p_copy_base_models = 'N'))
600 AND eng_item_flag = p_copy_eng_items;
601
602 /* myerrams,
603 Following Query is added to update mtl_item_revisions_interface table with Revision Id when ValidateItems is No.
604 Bug: 4892069
605 */
606 --myerrams, Bug: 5624219.
607 CURSOR c_item_rev_update_cursor(c_set_process_id_in NUMBER)
608 IS
609 SELECT organization_id
610 , inventory_item_id
611 , revision
612 FROM mtl_item_revisions_interface
613 WHERE set_process_id = c_set_process_id_in;
614
615 l_org_id NUMBER;
616 l_inv_item_id NUMBER;
617 l_revision_id NUMBER;
618 l_revision VARCHAR2(3);
619 /* myerrams, end */
620 -- serial_tagging enh -- bug 9913552
621 x_ret_sts VARCHAR2(1);
622
623 BEGIN
624 Init;
625 IF (g_proc_level >= g_debug_level)
626 THEN
627 FND_LOG.string(g_proc_level
628 , G_MODULE_NAME || l_procedure_name || '.begin'
629 ,'enter procedure'
630 );
631 END IF;
632
633 x_return_status := FND_API.G_RET_STS_SUCCESS;
634
635 -- Find number of items from source organizations based on input
636 -- parameters p_copy_base_models and p_copy_eng_items
637 SELECT COUNT(*)
638 INTO l_count
639 FROM mtl_system_items_b
640 WHERE organization_id = p_source_org_id
641 AND ((base_item_id is null and p_copy_base_models = 'Y') or (base_item_id is not null and p_copy_base_models = 'N'))
642 AND eng_item_flag = p_copy_eng_items;
643
644 IF (g_statement_level >= g_debug_level)
645 THEN
646 FND_LOG.string(g_statement_level
647 , G_MODULE_NAME || l_procedure_name
648 ,'p_copy_eng_items : '||p_copy_eng_items
649 );
650 FND_LOG.string(g_statement_level
651 , G_MODULE_NAME || l_procedure_name
652 ,'p_copy_base_models : '||p_copy_base_models
653 );
654 FND_LOG.string(g_statement_level
655 , G_MODULE_NAME || l_procedure_name
656 ,'No of items in the Model Org : '||l_count
657 );
658 END IF;
659
660 -- Process only if item count in source organization is more than 0
661 IF l_count > 0
662 THEN
663 IF p_validate = 'Y'
664 THEN
665 l_validate_items := 1;
666 l_process_flag := 1; --myerrams, Bug: 4892069
667 ELSE
668 l_validate_items := 2;
669 l_process_flag := 4; --myerrams, Bug: 4892069
670 END IF;
671
672 -- get the max batch size from the profile option;
673 -- default it to 1000 if the profile option is not defined.
674 l_max_batch_size := NVL( TO_NUMBER( FND_PROFILE.Value('INV_CCEOI_COMMIT_POINT'))
675 ,1000
676 );
677 -- get the max no. of workers for IOI from the profile option;
678 -- default it to 10 if the profile option is not defined.
679 -- myerrams, Modified the IOI workers Profile option from INV_IOI_WORKERS to INV_CCEOI_WORKERS.
680 l_request_count := NVL( TO_NUMBER( FND_PROFILE.Value('INV_CCEOI_WORKERS'))
681 ,10
682 );
683
684 l_min_index := 1;
685 IF l_count > l_max_batch_size
686 THEN
687 l_max_index := l_max_batch_size;
688 ELSE
689 l_max_index := l_count;
690 END IF;
691
692 -- Retrieve account details from target organization
693 SELECT organization_id
694 , cost_of_sales_account
695 , encumbrance_account
696 , sales_account
697 , expense_account
698 INTO l_organization_id
699 , l_cost_of_sales_account
700 , l_encumbrance_account
701 , l_sales_account
702 , l_expense_Account
703 FROM mtl_parameters
704 WHERE organization_id = p_target_org_id;
705
706 IF (g_statement_level >= g_debug_level)
707 THEN
708 FND_LOG.string(g_statement_level
709 , G_MODULE_NAME || l_procedure_name
710 ,'Value of various accounts are : '||l_expense_Account
711 ||' '||l_sales_account||' '||l_cost_of_sales_account
712 ||' '||l_encumbrance_account
713 );
714 END IF;
715
716 --myerrams, Bug: 5509589.
717 OPEN c_item_cursor;
718 LOOP
719 FETCH c_item_cursor BULK COLLECT INTO l_mtl_system_items_tbl LIMIT l_max_batch_size;
720 l_counter := 1; --myerrams, Bug 5509589. Reset the l_mtl_system_items_tbl counter.
721
722 IF (g_statement_level >= g_debug_level)
723 THEN
724 FND_LOG.string(g_statement_level
725 , G_MODULE_NAME || l_procedure_name
726 ,'Inside First Loop'
727 );
728 END IF;
729
730 -- Get next sequece value for SetProcessId
731 SELECT mtl_system_items_intf_sets_s.NEXTVAL
732 INTO l_set_process_id
733 FROM dual;
734
735 IF(g_statement_level >= g_debug_level)
736 THEN
737 FND_LOG.string(g_statement_level
738 , G_MODULE_NAME || l_procedure_name
739 ,'The process_id is : '||l_set_process_id
740 );
741 END IF;
742
743 -- Bulk collect item related required information from source organization
744
745 FOR l_Idx IN l_min_index..l_max_index
746 LOOP
747
748 -- ===========================================================
749 -- Insert into Items interface table
750 -- ===========================================================
751 INSERT INTO mtl_system_items_interface
752 ( process_flag
753 , set_process_id
754 , transaction_type
755 , inventory_item_id
756 , organization_id
757 , cost_of_sales_account
758 , encumbrance_account
759 , sales_account
760 , expense_account
761 , last_update_date
762 , last_updated_by
763 , creation_date
764 , created_by
765 , last_update_login
766 , request_id
767 , program_application_id
768 , program_id
769 , program_update_date
770 , purchasing_enabled_flag
771 , CUSTOMER_ORDER_ENABLED_FLAG
772 , INTERNAL_ORDER_ENABLED_FLAG
773 , MTL_TRANSACTIONS_ENABLED_FLAG
774 , STOCK_ENABLED_FLAG
775 , BOM_ENABLED_FLAG
776 , BUILD_IN_WIP_FLAG
777 , invoice_enabled_flag
778 , source_organization_id --myerrams, Bug: 5964347
779 , source_subinventory --myerrams, Bug: 5964347
780 )
781 VALUES
782 ( l_process_flag --myerrams, Bug: 4892069
783 , l_set_process_id
784 , 'CREATE'
785 , l_mtl_system_items_tbl(l_counter).inventory_item_id
786 , p_target_org_id
787 , l_cost_of_sales_account
788 , l_encumbrance_account
789 , l_sales_account
790 , l_expense_account
791 , SYSDATE
792 , FND_GLOBAL.user_id
793 , SYSDATE
794 , FND_GLOBAL.user_id
795 , FND_GLOBAL.login_id
796 , FND_GLOBAL.conc_request_id
797 , FND_GLOBAL.prog_appl_id
798 , FND_GLOBAL.conc_program_id
799 , SYSDATE
800 , l_mtl_system_items_tbl(l_counter).purchasing_enabled_flag
801 , l_mtl_system_items_tbl(l_counter).customer_order_enabled_flag
802 , l_mtl_system_items_tbl(l_counter).internal_order_enabled_flag
803 , l_mtl_system_items_tbl(l_counter).mtl_transactions_enabled_flag
804 , l_mtl_system_items_tbl(l_counter).stock_enabled_flag
805 , l_mtl_system_items_tbl(l_counter).bom_enabled_flag
806 , l_mtl_system_items_tbl(l_counter).build_in_wip_flag
807 , l_mtl_system_items_tbl(l_counter).invoice_enabled_flag
808 , l_mtl_system_items_tbl(l_counter).source_organization_id --myerrams, Bug: 5964347
809 , l_mtl_system_items_tbl(l_counter).source_subinventory --myerrams, Bug: 5964347
810 );
811
812
813 -- Get rowid from items interface table
814 SELECT rowid
815 INTO l_rowid
816 FROM mtl_system_items_interface
817 WHERE set_process_id = l_set_process_id
818 AND inventory_item_id = l_mtl_system_items_tbl(l_counter).inventory_item_id
819 AND organization_id = p_target_org_id;
820
821 -- Assign Master Defaults
822 l_return_code := INVPUTLI.Assign_master_defaults
823 (Tran_id => NULL
824 ,Item_id => l_mtl_system_items_tbl(l_counter).inventory_item_id
825 ,Org_id => p_target_org_id
826 ,Master_org_id => p_source_org_id
827 ,Status_default => NULL
828 ,Uom_default => NULL
829 ,Allow_item_desc_flag => NULL
830 ,Req_required_flag => NULL
831 ,p_rowid => l_rowid
832 ,Err_text => x_return_message
833 );
834
835
836 -- error while assigning master defaults
837 IF l_return_code <> 0 THEN
838 x_return_status := FND_API.G_RET_STS_ERROR;
839 RETURN;
840 END IF;
841
842 -- Serial Tagging Enh -- bug 9913552
843 IF INV_SERIAL_NUMBER_PUB.is_serial_tagged(p_inventory_item_id => l_mtl_system_items_tbl(l_counter).inventory_item_id,
844 p_organization_id => p_source_org_id)=2 THEN
845
846 INV_SERIAL_NUMBER_PUB.copy_serial_tag_assignments(
847 p_from_item_id =>l_mtl_system_items_tbl(l_counter).inventory_item_id,
848 p_from_org_id =>p_source_org_id,
849 p_to_item_id =>l_mtl_system_items_tbl(l_counter).inventory_item_id ,
850 p_to_org_id =>p_target_org_id,
851 x_return_status => x_ret_sts);
852
853
854
855 IF x_ret_sts <>FND_API.G_RET_STS_SUCCESS THEN
856 x_return_status:= FND_API.G_RET_STS_ERROR;
857 return;
858 END IF;
859 END IF;
860 -- Assign Status attributes
861 l_return_code := INVPULI4.assign_status_attributes
862 (item_id => l_mtl_system_items_tbl(l_counter).inventory_item_id
863 ,org_id => p_target_org_id
864 ,Err_text => x_return_message
865 ,p_rowid => l_rowid
866 );
867
868 -- error while assigning status attributes
869 IF l_return_code <> 0 THEN
870 x_return_status := FND_API.G_RET_STS_ERROR;
871 RETURN;
872 END IF;
873 l_counter := l_counter + 1;
874
875 END LOOP; -- end loop for that range of index
876
877 -- ===========================================================
878 -- Set value of product_family_item_id as NULL
879 -- ===========================================================
880 UPDATE mtl_system_items_interface set
881 product_family_item_id = NULL
882 WHERE set_process_id = l_set_process_id;
883
884
885 -- ===========================================================
886 -- Insert into Revisions interface table
887 -- ===========================================================
888 INSERT INTO mtl_item_revisions_interface
889 ( inventory_item_id
890 , organization_id
891 , revision
892 , revision_label --myerrams, Bug: 4892069
893 , implementation_date
894 , effectivity_date
895 , process_flag --myerrams, Bug: 4892069
896 , transaction_type
897 , set_process_id
898 , last_update_date
899 , last_updated_by
900 , creation_date
901 , created_by
902 , last_update_login
903 , request_id
904 , program_application_id
905 , program_id
906 , program_update_date
907 )
908 SELECT msi.inventory_item_id
909 ,p_target_org_id
910 ,mir.REVISION
911 ,mir.revision_label --myerrams, Bug: 4892069
912 ,mir.implementation_date
913 ,mir.effectivity_date
914 ,l_process_flag --myerrams, Bug: 4892069
915 ,'CREATE'
916 ,l_set_process_id
917 ,SYSDATE
918 ,FND_GLOBAL.user_id
919 ,SYSDATE
920 ,FND_GLOBAL.user_id
921 ,FND_GLOBAL.login_id
922 ,FND_GLOBAL.conc_request_id
923 ,FND_GLOBAL.prog_appl_id
924 ,FND_GLOBAL.conc_program_id
925 ,SYSDATE
926 FROM mtl_item_revisions_b mir
927 ,mtl_system_items_interface msi
928 WHERE mir.inventory_item_id = msi.inventory_item_id
929 and mir.organization_id = p_source_org_id
930 and msi.organization_id = p_target_org_id
931 and msi.set_process_id = l_set_process_id
932 ORDER BY mir.REVISION,effectivity_date ;
933
934 /*myerrams,
935 Following code is to update mtl_item_revisions_interface table with Revision Id, which is generated using
936 the sequence 'MTL_ITEM_REVISIONS_B_S';
937 Bug: 4892069
938 */
939 IF p_validate = 'N'
940 THEN
941 IF(g_statement_level >= g_debug_level)
942 THEN
943 FND_LOG.string(g_statement_level
944 , G_MODULE_NAME || l_procedure_name
945 ,'The Set Process Id that is used to execute the c_item_rev_update_cursor is: '|| l_set_process_id
946 );
947 END IF;
948 --myerrams, Bug: 5624219. Modified the cursor to use Cursor Parameter l_set_process_id
949 OPEN c_item_rev_update_cursor(l_set_process_id);
950 LOOP
951 FETCH c_item_rev_update_cursor into l_org_id, l_inv_item_id, l_revision;
952 EXIT WHEN c_item_rev_update_cursor%NOTFOUND;
953
954 --Get the new Revision Id from Sequence.
955 SELECT MTL_ITEM_REVISIONS_B_S.NEXTVAL into l_revision_id from dual;
956
957 --Update Inferface table with mandatroy Revision Id Attribute.
958 UPDATE mtl_item_revisions_interface
959 SET revision_id = l_revision_id
960 WHERE ORGANIZATION_ID = l_org_id
961 AND INVENTORY_ITEM_ID = l_inv_item_id
962 AND REVISION = l_revision;
963 END LOOP;
964 CLOSE c_item_rev_update_cursor;
965 END IF;
966 /*myerrams, end */
967 -- ===========================================================
968 -- Insert into Categories interface table
969 -- ===========================================================
970
971
972 INSERT INTO mtl_item_categories_interface
973 ( inventory_item_id
974 , organization_id
975 , CATEGORY_SET_ID
976 , CATEGORY_ID
977 , process_flag
978 , transaction_type
979 , set_process_id
980 , last_update_date
981 , last_updated_by
982 , creation_date
983 , created_by
984 , last_update_login
985 , request_id
986 , program_application_id
987 , program_id
988 , program_update_date
989 )
990 SELECT msi.inventory_item_id
991 ,p_target_org_id
992 ,mic.CATEGORY_SET_ID
993 ,mic.CATEGORY_ID
994 -- ,l_process_flag --myerrams, Bug: 4892069
995 ,1 --myerrams, Bug: 5624219; ProcessFlag for Item Categories has to be 1 irrespective of validate items option.
996 ,'CREATE'
997 ,l_set_process_id
998 ,SYSDATE
999 ,FND_GLOBAL.user_id
1000 ,SYSDATE
1001 ,FND_GLOBAL.user_id
1002 ,FND_GLOBAL.login_id
1003 ,FND_GLOBAL.conc_request_id
1004 ,FND_GLOBAL.prog_appl_id
1005 ,FND_GLOBAL.conc_program_id
1006 ,SYSDATE
1007 FROM mtl_category_sets_b mcs
1008 ,mtl_system_items_interface msi
1009 ,mtl_item_categories mic
1010 WHERE msi.inventory_item_id = mic.inventory_item_id
1011 AND msi.organization_id = p_target_org_id
1012 AND mic.organization_id = p_source_org_id
1013 AND msi.set_process_id = l_set_process_id
1014 AND mcs.category_set_id = mic.category_set_id
1015 AND mcs.control_level = 2
1016 AND mcs.category_set_id NOT IN
1017 ( SELECT mdc.category_set_id
1018 FROM mtl_default_category_sets mdc
1019 WHERE mdc.functional_area_id = DECODE( msi.INVENTORY_ITEM_FLAG, 'Y', 1, 0 )
1020 OR mdc.functional_area_id = DECODE( msi.PURCHASING_ITEM_FLAG, 'Y', 2, 0 )
1021 OR mdc.functional_area_id = DECODE( msi.INTERNAL_ORDER_FLAG, 'Y', 2, 0 )
1022 OR mdc.functional_area_id = DECODE( msi.MRP_PLANNING_CODE, 6, 0, 3 )
1023 OR mdc.functional_area_id = DECODE( msi.SERVICEABLE_PRODUCT_FLAG, 'Y', 4, 0 )
1024 OR mdc.functional_area_id = DECODE( msi.COSTING_ENABLED_FLAG, 'Y', 5, 0 )
1025 OR mdc.functional_area_id = DECODE( msi.ENG_ITEM_FLAG, 'Y', 6, 0 )
1026 OR mdc.functional_area_id = DECODE( msi.CUSTOMER_ORDER_FLAG, 'Y', 7, 0 )
1027 OR mdc.functional_area_id = DECODE( NVL(msi.EAM_ITEM_TYPE, 0), 0, 0, 9 )
1028 OR mdc.functional_area_id =
1029 DECODE( msi.CONTRACT_ITEM_TYPE_CODE,
1030 'SERVICE' , 10,
1031 'WARRANTY' , 10,
1032 'SUBSCRIPTION' , 10,
1033 'USAGE' , 10, 0 )
1034 -- These Contract Item types also imply an item belonging to the Service functional area
1035 OR mdc.functional_area_id =
1036 DECODE( msi.CONTRACT_ITEM_TYPE_CODE,
1037 'SERVICE' , 4,
1038 'WARRANTY' , 4, 0 )
1039 OR mdc.functional_area_id = DECODE( msi.INTERNAL_ORDER_FLAG, 'Y', 11, 0 )
1040 OR mdc.functional_area_id = DECODE( msi.CUSTOMER_ORDER_FLAG, 'Y', 11, 0 )
1041 );
1042
1043 -- Submit concurrent request for INCOIN
1044 Submit_Item_Import
1045 ( p_organization_id => p_target_org_id
1046 , p_source_org_id => p_source_org_id --myerrams, Bug: 5964347
1047 , p_set_process_id => l_set_process_id
1048 , p_validate_items => l_validate_items
1049 , x_workers => l_workers_tbl
1050 , x_return_status => x_return_status
1051 , x_return_message => x_return_message
1052 , p_request_count => l_request_count
1053 );
1054
1055 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1056 RETURN;
1057 END IF;
1058
1059 l_min_index := l_max_index + 1;
1060 IF l_count > (l_max_index + l_max_batch_size)
1061 THEN
1062 l_max_index := l_max_index + l_max_batch_size;
1063 ELSE
1064 l_max_index := l_count;
1065 END IF;
1066
1067 -- Exit when all items are processed
1068 -- myerrams, Bug: 5509589. Exit condition for Bulk Collect and close c_item_cursor.
1069 EXIT WHEN c_item_cursor%NOTFOUND;
1070 END LOOP;
1071 CLOSE c_item_cursor;
1072
1073 -- Wait for completion of all workers
1074 Wait_For_All_Workers(p_workers => l_workers_tbl);
1075
1076 x_return_message := 'Submitted Item Copy request successfully';
1077
1078 END IF; -- End of IF for l_count > 0
1079 IF (g_proc_level >= g_debug_level)
1080 THEN
1081 FND_LOG.string(g_proc_level
1082 , G_MODULE_NAME || l_procedure_name || '.end'
1083 ,'exit procedure'
1084 );
1085 END IF;
1086 EXCEPTION
1087 WHEN OTHERS THEN
1088 x_return_status := FND_API.G_RET_STS_ERROR;
1089 x_return_message := FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE);
1090 x_return_message := x_return_message || FND_GLOBAL.NewLine || SQLCODE || ' : ' || SQLERRM;
1091
1092 IF(g_statement_level >= g_debug_level)
1093 THEN
1094 FND_LOG.string(g_statement_level
1095 , G_MODULE_NAME || l_procedure_name
1096 ,'x_return_message: '|| x_return_message
1097 );
1098 END IF;
1099
1100 END Copy_Items;
1101
1102 END INV_COPY_ITEM_CP;