[Home] [Help]
PACKAGE BODY: APPS.INV_COPY_ITEM_CP
Source
1 PACKAGE BODY INV_COPY_ITEM_CP AS
2 /* $Header: INVITCPB.pls 120.8.12000000.2 2007/09/27 08:53:30 ajmittal 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
621 BEGIN
622 Init;
623 IF (g_proc_level >= g_debug_level)
624 THEN
625 FND_LOG.string(g_proc_level
626 , G_MODULE_NAME || l_procedure_name || '.begin'
627 ,'enter procedure'
628 );
629 END IF;
630
631 x_return_status := FND_API.G_RET_STS_SUCCESS;
632
633 -- Find number of items from source organizations based on input
634 -- parameters p_copy_base_models and p_copy_eng_items
635 SELECT COUNT(*)
636 INTO l_count
637 FROM mtl_system_items_b
638 WHERE organization_id = p_source_org_id
639 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'))
640 AND eng_item_flag = p_copy_eng_items;
641
642 IF (g_statement_level >= g_debug_level)
643 THEN
644 FND_LOG.string(g_statement_level
645 , G_MODULE_NAME || l_procedure_name
646 ,'p_copy_eng_items : '||p_copy_eng_items
647 );
648 FND_LOG.string(g_statement_level
649 , G_MODULE_NAME || l_procedure_name
650 ,'p_copy_base_models : '||p_copy_base_models
651 );
652 FND_LOG.string(g_statement_level
653 , G_MODULE_NAME || l_procedure_name
654 ,'No of items in the Model Org : '||l_count
655 );
656 END IF;
657
658 -- Process only if item count in source organization is more than 0
659 IF l_count > 0
660 THEN
661 IF p_validate = 'Y'
662 THEN
663 l_validate_items := 1;
664 l_process_flag := 1; --myerrams, Bug: 4892069
665 ELSE
666 l_validate_items := 2;
667 l_process_flag := 4; --myerrams, Bug: 4892069
668 END IF;
669
670 -- get the max batch size from the profile option;
671 -- default it to 1000 if the profile option is not defined.
672 l_max_batch_size := NVL( TO_NUMBER( FND_PROFILE.Value('INV_CCEOI_COMMIT_POINT'))
673 ,1000
674 );
675 -- get the max no. of workers for IOI from the profile option;
676 -- default it to 10 if the profile option is not defined.
677 -- myerrams, Modified the IOI workers Profile option from INV_IOI_WORKERS to INV_CCEOI_WORKERS.
678 l_request_count := NVL( TO_NUMBER( FND_PROFILE.Value('INV_CCEOI_WORKERS'))
679 ,10
680 );
681
682 l_min_index := 1;
683 IF l_count > l_max_batch_size
684 THEN
685 l_max_index := l_max_batch_size;
686 ELSE
687 l_max_index := l_count;
688 END IF;
689
690 -- Retrieve account details from target organization
691 SELECT organization_id
692 , cost_of_sales_account
693 , encumbrance_account
694 , sales_account
695 , expense_account
696 INTO l_organization_id
697 , l_cost_of_sales_account
698 , l_encumbrance_account
699 , l_sales_account
700 , l_expense_Account
701 FROM mtl_parameters
702 WHERE organization_id = p_target_org_id;
703
704 IF (g_statement_level >= g_debug_level)
705 THEN
706 FND_LOG.string(g_statement_level
707 , G_MODULE_NAME || l_procedure_name
708 ,'Value of various accounts are : '||l_expense_Account
709 ||' '||l_sales_account||' '||l_cost_of_sales_account
710 ||' '||l_encumbrance_account
711 );
712 END IF;
713
714 --myerrams, Bug: 5509589.
715 OPEN c_item_cursor;
716 LOOP
717 FETCH c_item_cursor BULK COLLECT INTO l_mtl_system_items_tbl LIMIT l_max_batch_size;
718 l_counter := 1; --myerrams, Bug 5509589. Reset the l_mtl_system_items_tbl counter.
719
720 IF (g_statement_level >= g_debug_level)
721 THEN
722 FND_LOG.string(g_statement_level
723 , G_MODULE_NAME || l_procedure_name
724 ,'Inside First Loop'
725 );
726 END IF;
727
728 -- Get next sequece value for SetProcessId
729 SELECT mtl_system_items_intf_sets_s.NEXTVAL
730 INTO l_set_process_id
731 FROM dual;
732
733 IF(g_statement_level >= g_debug_level)
734 THEN
735 FND_LOG.string(g_statement_level
736 , G_MODULE_NAME || l_procedure_name
737 ,'The process_id is : '||l_set_process_id
738 );
739 END IF;
740
741 -- Bulk collect item related required information from source organization
742
743 FOR l_Idx IN l_min_index..l_max_index
744 LOOP
745
746 -- ===========================================================
747 -- Insert into Items interface table
748 -- ===========================================================
749 INSERT INTO mtl_system_items_interface
750 ( process_flag
751 , set_process_id
752 , transaction_type
753 , inventory_item_id
754 , organization_id
755 , cost_of_sales_account
756 , encumbrance_account
757 , sales_account
758 , expense_account
759 , last_update_date
760 , last_updated_by
761 , creation_date
762 , created_by
763 , last_update_login
764 , request_id
765 , program_application_id
766 , program_id
767 , program_update_date
768 , purchasing_enabled_flag
769 , CUSTOMER_ORDER_ENABLED_FLAG
770 , INTERNAL_ORDER_ENABLED_FLAG
771 , MTL_TRANSACTIONS_ENABLED_FLAG
772 , STOCK_ENABLED_FLAG
773 , BOM_ENABLED_FLAG
774 , BUILD_IN_WIP_FLAG
775 , invoice_enabled_flag
776 , source_organization_id --myerrams, Bug: 5964347
777 , source_subinventory --myerrams, Bug: 5964347
778 )
779 VALUES
780 ( l_process_flag --myerrams, Bug: 4892069
781 , l_set_process_id
782 , 'CREATE'
783 , l_mtl_system_items_tbl(l_counter).inventory_item_id
784 , p_target_org_id
785 , l_cost_of_sales_account
786 , l_encumbrance_account
787 , l_sales_account
788 , l_expense_account
789 , SYSDATE
790 , FND_GLOBAL.user_id
791 , SYSDATE
792 , FND_GLOBAL.user_id
793 , FND_GLOBAL.login_id
794 , FND_GLOBAL.conc_request_id
795 , FND_GLOBAL.prog_appl_id
796 , FND_GLOBAL.conc_program_id
797 , SYSDATE
798 , l_mtl_system_items_tbl(l_counter).purchasing_enabled_flag
799 , l_mtl_system_items_tbl(l_counter).customer_order_enabled_flag
800 , l_mtl_system_items_tbl(l_counter).internal_order_enabled_flag
801 , l_mtl_system_items_tbl(l_counter).mtl_transactions_enabled_flag
802 , l_mtl_system_items_tbl(l_counter).stock_enabled_flag
803 , l_mtl_system_items_tbl(l_counter).bom_enabled_flag
804 , l_mtl_system_items_tbl(l_counter).build_in_wip_flag
805 , l_mtl_system_items_tbl(l_counter).invoice_enabled_flag
806 , l_mtl_system_items_tbl(l_counter).source_organization_id --myerrams, Bug: 5964347
807 , l_mtl_system_items_tbl(l_counter).source_subinventory --myerrams, Bug: 5964347
808 );
809
810
811 -- Get rowid from items interface table
812 SELECT rowid
813 INTO l_rowid
814 FROM mtl_system_items_interface
815 WHERE set_process_id = l_set_process_id
816 AND inventory_item_id = l_mtl_system_items_tbl(l_counter).inventory_item_id
817 AND organization_id = p_target_org_id;
818
819 -- Assign Master Defaults
820 l_return_code := INVPUTLI.Assign_master_defaults
821 (Tran_id => NULL
822 ,Item_id => l_mtl_system_items_tbl(l_counter).inventory_item_id
823 ,Org_id => p_target_org_id
824 ,Master_org_id => p_source_org_id
825 ,Status_default => NULL
826 ,Uom_default => NULL
827 ,Allow_item_desc_flag => NULL
828 ,Req_required_flag => NULL
829 ,p_rowid => l_rowid
830 ,Err_text => x_return_message
831 );
832
833
834 -- error while assigning master defaults
835 IF l_return_code <> 0 THEN
836 x_return_status := FND_API.G_RET_STS_ERROR;
837 RETURN;
838 END IF;
839
840 -- Assign Status attributes
841 l_return_code := INVPULI4.assign_status_attributes
842 (item_id => l_mtl_system_items_tbl(l_counter).inventory_item_id
843 ,org_id => p_target_org_id
844 ,Err_text => x_return_message
845 ,p_rowid => l_rowid
846 );
847
848 -- error while assigning status attributes
849 IF l_return_code <> 0 THEN
850 x_return_status := FND_API.G_RET_STS_ERROR;
851 RETURN;
852 END IF;
853 l_counter := l_counter + 1;
854
855 END LOOP; -- end loop for that range of index
856
857 -- ===========================================================
858 -- Set value of product_family_item_id as NULL
859 -- ===========================================================
860 UPDATE mtl_system_items_interface set
861 product_family_item_id = NULL
862 WHERE set_process_id = l_set_process_id;
863
864
865 -- ===========================================================
866 -- Insert into Revisions interface table
867 -- ===========================================================
868 INSERT INTO mtl_item_revisions_interface
869 ( inventory_item_id
870 , organization_id
871 , revision
872 , revision_label --myerrams, Bug: 4892069
873 , implementation_date
874 , effectivity_date
875 , process_flag --myerrams, Bug: 4892069
876 , transaction_type
877 , set_process_id
878 , last_update_date
879 , last_updated_by
880 , creation_date
881 , created_by
882 , last_update_login
883 , request_id
884 , program_application_id
885 , program_id
886 , program_update_date
887 )
888 SELECT msi.inventory_item_id
889 ,p_target_org_id
890 ,mir.REVISION
891 ,mir.revision_label --myerrams, Bug: 4892069
892 ,mir.implementation_date
893 ,mir.effectivity_date
894 ,l_process_flag --myerrams, Bug: 4892069
895 ,'CREATE'
896 ,l_set_process_id
897 ,SYSDATE
898 ,FND_GLOBAL.user_id
899 ,SYSDATE
900 ,FND_GLOBAL.user_id
901 ,FND_GLOBAL.login_id
902 ,FND_GLOBAL.conc_request_id
903 ,FND_GLOBAL.prog_appl_id
904 ,FND_GLOBAL.conc_program_id
905 ,SYSDATE
906 FROM mtl_item_revisions_b mir
907 ,mtl_system_items_interface msi
908 WHERE mir.inventory_item_id = msi.inventory_item_id
909 and mir.organization_id = p_source_org_id
910 and msi.organization_id = p_target_org_id
911 and msi.set_process_id = l_set_process_id
912 ORDER BY mir.REVISION,effectivity_date ;
913
914 /*myerrams,
915 Following code is to update mtl_item_revisions_interface table with Revision Id, which is generated using
916 the sequence 'MTL_ITEM_REVISIONS_B_S';
917 Bug: 4892069
918 */
919 IF p_validate = 'N'
920 THEN
921 IF(g_statement_level >= g_debug_level)
922 THEN
923 FND_LOG.string(g_statement_level
924 , G_MODULE_NAME || l_procedure_name
925 ,'The Set Process Id that is used to execute the c_item_rev_update_cursor is: '|| l_set_process_id
926 );
927 END IF;
928 --myerrams, Bug: 5624219. Modified the cursor to use Cursor Parameter l_set_process_id
929 OPEN c_item_rev_update_cursor(l_set_process_id);
930 LOOP
931 FETCH c_item_rev_update_cursor into l_org_id, l_inv_item_id, l_revision;
932 EXIT WHEN c_item_rev_update_cursor%NOTFOUND;
933
934 --Get the new Revision Id from Sequence.
935 SELECT MTL_ITEM_REVISIONS_B_S.NEXTVAL into l_revision_id from dual;
936
937 --Update Inferface table with mandatroy Revision Id Attribute.
938 UPDATE mtl_item_revisions_interface
939 SET revision_id = l_revision_id
940 WHERE ORGANIZATION_ID = l_org_id
941 AND INVENTORY_ITEM_ID = l_inv_item_id
942 AND REVISION = l_revision;
943 END LOOP;
944 CLOSE c_item_rev_update_cursor;
945 END IF;
946 /*myerrams, end */
947 -- ===========================================================
948 -- Insert into Categories interface table
949 -- ===========================================================
950
951
952 INSERT INTO mtl_item_categories_interface
953 ( inventory_item_id
954 , organization_id
955 , CATEGORY_SET_ID
956 , CATEGORY_ID
957 , process_flag
958 , transaction_type
959 , set_process_id
960 , last_update_date
961 , last_updated_by
962 , creation_date
963 , created_by
964 , last_update_login
965 , request_id
966 , program_application_id
967 , program_id
968 , program_update_date
969 )
970 SELECT msi.inventory_item_id
971 ,p_target_org_id
972 ,mic.CATEGORY_SET_ID
973 ,mic.CATEGORY_ID
974 -- ,l_process_flag --myerrams, Bug: 4892069
975 ,1 --myerrams, Bug: 5624219; ProcessFlag for Item Categories has to be 1 irrespective of validate items option.
976 ,'CREATE'
980 ,SYSDATE
977 ,l_set_process_id
978 ,SYSDATE
979 ,FND_GLOBAL.user_id
981 ,FND_GLOBAL.user_id
982 ,FND_GLOBAL.login_id
983 ,FND_GLOBAL.conc_request_id
984 ,FND_GLOBAL.prog_appl_id
985 ,FND_GLOBAL.conc_program_id
986 ,SYSDATE
987 FROM mtl_category_sets_b mcs
988 ,mtl_system_items_interface msi
989 ,mtl_item_categories mic
990 WHERE msi.inventory_item_id = mic.inventory_item_id
991 AND msi.organization_id = p_target_org_id
992 AND mic.organization_id = p_source_org_id
993 AND msi.set_process_id = l_set_process_id
994 AND mcs.category_set_id = mic.category_set_id
995 AND mcs.control_level = 2
996 AND mcs.category_set_id NOT IN
997 ( SELECT mdc.category_set_id
998 FROM mtl_default_category_sets mdc
999 WHERE mdc.functional_area_id = DECODE( msi.INVENTORY_ITEM_FLAG, 'Y', 1, 0 )
1000 OR mdc.functional_area_id = DECODE( msi.PURCHASING_ITEM_FLAG, 'Y', 2, 0 )
1001 OR mdc.functional_area_id = DECODE( msi.INTERNAL_ORDER_FLAG, 'Y', 2, 0 )
1002 OR mdc.functional_area_id = DECODE( msi.MRP_PLANNING_CODE, 6, 0, 3 )
1003 OR mdc.functional_area_id = DECODE( msi.SERVICEABLE_PRODUCT_FLAG, 'Y', 4, 0 )
1004 OR mdc.functional_area_id = DECODE( msi.COSTING_ENABLED_FLAG, 'Y', 5, 0 )
1005 OR mdc.functional_area_id = DECODE( msi.ENG_ITEM_FLAG, 'Y', 6, 0 )
1006 OR mdc.functional_area_id = DECODE( msi.CUSTOMER_ORDER_FLAG, 'Y', 7, 0 )
1007 OR mdc.functional_area_id = DECODE( NVL(msi.EAM_ITEM_TYPE, 0), 0, 0, 9 )
1008 OR mdc.functional_area_id =
1009 DECODE( msi.CONTRACT_ITEM_TYPE_CODE,
1010 'SERVICE' , 10,
1011 'WARRANTY' , 10,
1012 'SUBSCRIPTION' , 10,
1013 'USAGE' , 10, 0 )
1014 -- These Contract Item types also imply an item belonging to the Service functional area
1015 OR mdc.functional_area_id =
1016 DECODE( msi.CONTRACT_ITEM_TYPE_CODE,
1017 'SERVICE' , 4,
1018 'WARRANTY' , 4, 0 )
1019 OR mdc.functional_area_id = DECODE( msi.INTERNAL_ORDER_FLAG, 'Y', 11, 0 )
1020 OR mdc.functional_area_id = DECODE( msi.CUSTOMER_ORDER_FLAG, 'Y', 11, 0 )
1021 );
1022
1023 -- Submit concurrent request for INCOIN
1024 Submit_Item_Import
1025 ( p_organization_id => p_target_org_id
1026 , p_source_org_id => p_source_org_id --myerrams, Bug: 5964347
1027 , p_set_process_id => l_set_process_id
1028 , p_validate_items => l_validate_items
1029 , x_workers => l_workers_tbl
1030 , x_return_status => x_return_status
1031 , x_return_message => x_return_message
1032 , p_request_count => l_request_count
1033 );
1034
1035 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1036 RETURN;
1037 END IF;
1038
1039 l_min_index := l_max_index + 1;
1040 IF l_count > (l_max_index + l_max_batch_size)
1041 THEN
1042 l_max_index := l_max_index + l_max_batch_size;
1043 ELSE
1044 l_max_index := l_count;
1045 END IF;
1046
1047 -- Exit when all items are processed
1048 -- myerrams, Bug: 5509589. Exit condition for Bulk Collect and close c_item_cursor.
1049 EXIT WHEN c_item_cursor%NOTFOUND;
1050 END LOOP;
1051 CLOSE c_item_cursor;
1052
1053 -- Wait for completion of all workers
1054 Wait_For_All_Workers(p_workers => l_workers_tbl);
1055
1056 x_return_message := 'Submitted Item Copy request successfully';
1057
1058 END IF; -- End of IF for l_count > 0
1059 IF (g_proc_level >= g_debug_level)
1060 THEN
1061 FND_LOG.string(g_proc_level
1062 , G_MODULE_NAME || l_procedure_name || '.end'
1063 ,'exit procedure'
1064 );
1065 END IF;
1066 EXCEPTION
1067 WHEN OTHERS THEN
1068 x_return_status := FND_API.G_RET_STS_ERROR;
1069 x_return_message := FND_MSG_PUB.Get(p_encoded => FND_API.G_FALSE);
1070 x_return_message := x_return_message || FND_GLOBAL.NewLine || SQLCODE || ' : ' || SQLERRM;
1071
1072 IF(g_statement_level >= g_debug_level)
1073 THEN
1074 FND_LOG.string(g_statement_level
1075 , G_MODULE_NAME || l_procedure_name
1076 ,'x_return_message: '|| x_return_message
1077 );
1078 END IF;
1079
1080 END Copy_Items;
1081
1082 END INV_COPY_ITEM_CP;