[Home] [Help]
PACKAGE BODY: APPS.CTO_COST_ROLLUP_CONC_PK
Source
1 PACKAGE BODY CTO_COST_ROLLUP_CONC_PK as
2 /* $Header: CTOCRCNB.pls 120.2.12000000.2 2007/03/09 12:38:35 abhissri ship $*/
3
4
5 /*
6 *=========================================================================*
7 | |
8 | Copyright (c) 2001, Oracle Corporation, Redwood Shores, California, USA |
9 | All rights reserved. |
10 | |
11 *=========================================================================*
12 | |
13 | NAME |
14 | CTO Cost rollup package body |
15 | |
16 | DESCRIPTION |
17 | PL/SQL package body containing the routine for cost rollup |
18 | of configuration items. |
19 | For different combination of parameters passed , code gets the parent |
20 | config item and its child and insert them into cst_sc_lists |
21 | |
22 | |
23 | ARGUMENTS |
24 | Input : Config Item : Select this config item |
25 | Base Model Item : All configs for this base model |
26 | Item Created Days Ago : All configs created in last "n" |
27 | days. |
28 | Organization : Calculate cost for all configs in|
29 | this org. |
30 | HISTORY |
31 | Date Author Comments |
32 | --------- -------- ---------------------------------------------------- |
33 | 10/27/2003 KSARKAR creation of body CTO_COST_ROLLUP_CONC_PK |
34 | |
35 | 08/06/2004 Sushant Sawant Modified |
36 | Bugfix 3777922 |
37 | Changed code to process sourcing for parent before |
38 | child config item. This will ensure child sourcing |
39 | starts from the end manufacturing org of the parent config |
40 | Only Top config items will be picked from main cursor for |
41 | upgrade scenario as get_config_details will return child |
42 | configurations |
43 |
44 | Bugfix 3784283
45 | Cost Rollup will be performed in batches of approx 100 records
46 | Total# of records should be >= 100 to be considered as a batch
47 | A batch will consist of parent configs and all their children.
48 | A logical break will consider parents and all their children
49 | A savepoint will be created after each batch is processed.
50 | A rollback to the previous savepoint will be performed for an
51 | erroneous batch. Processing will continue for remaining records.
52 | A summary of successful/failed configuration items will be
53 | provided at the end of the program.
54 |
55 | |
56 | |
57 | |
58 | 11/23/2004 Sushant Sawant Modified |
59 | bugfix 3941383 |
60 | cost rollup for child configuration is not performed in |
61 | the root sourcing org if child model has 100% transfer from
62 | sourcing rule.
63 | |
64 | |
65 | |
66 | 11/23/2004 Sushant Sawant Modified |
67 | bugfix 3975083 |
68 | Optional cost rollup process fails when processing multiple |
69 | batches. |
70 | Modified the code to reinitialize the index variable to |
71 | collect data for next batch in cfg_item_array array. |
72 | |
73 | |
74 *==========================================================================================*/
75
76 gMrpAssignmentSet number ;
77
78 gUserId number := nvl(fnd_global.user_id, -1);
79 gLoginId number := nvl(fnd_global.login_id, -1);
80
81 -- Forward declaration
82 PROCEDURE WriteToLog (p_message in varchar2 default null,
83 p_level in number default 0);
84
85 -- Forward decalration
86
87 PROCEDURE get_sourcing_org (
88 p_config_item_id number
89 , p_organization_id number
90 , p_return_status out NOCOPY varchar2 );
91
92
93 /**********************************************************************************
94 Procedure body: CTO_COST_ROLLUP_CONC_PK :
95 This a stored PL/SQL concurrent program that rolls up config item cost based on
96 different criteria.
97
98 INPUT arguments:
99 p_config_item_id : Configuration Item.
100 p_model_id : Configs with this base Model Item.
101 p_num_of_days : Configs created in the last "n" days.
102 p_org_id : Oreganization Id
103 p_upgrade : If this is for upgrade
104 p_calc_costrollup : If costrollup is needed with upgrade
105 ***********************************************************************************/
106 PROCEDURE cto_cost_rollup
107 (
108 errbuf OUT NOCOPY VARCHAR2,
109 retcode OUT NOCOPY VARCHAR2,
110 p_org_id IN NUMBER,
111 p_dummy IN NUMBER,
112 p_config_item_id IN NUMBER,
113 p_dummy2 IN NUMBER,
114 p_model_id IN NUMBER,
115 p_num_of_days IN NUMBER,
116 p_upgrade IN VARCHAR2,
117 p_calc_costrollup IN VARCHAR2
118
119 )
120 IS
121
122 l_request_id NUMBER;
123 l_program_appl_id NUMBER;
124 l_program_id NUMBER;
125
126 l_stat_num NUMBER := 0;
127 l_status INTEGER;
128 l_group_id NUMBER;
129 l_return_status VARCHAR2(100);
130 l_msg_count NUMBER;
131 l_msg_data VARCHAR2(100);
132 loop_counter NUMBER;
133 l_config_item NUMBER;
134 l_child_config_item NUMBER;
135 l_org_id NUMBER;
136 l_child_org_id NUMBER;
137 l_config_orgs VARCHAR2(30);
138 l_child_config_orgs VARCHAR2(30);
139 l_plan_level bom_explosion_temp.plan_level%TYPE;
140
141
142 TYPE ConfigCurTyp is REF CURSOR ;
143
144 config_cv ConfigCurTyp;
145
146 cursor cfg_org_cur ( x_config_item number ) is
147 select msi.organization_id
148 from mtl_system_items msi
149 where msi.inventory_item_id = x_config_item
150 and msi.inventory_item_status_code <>
151 ( select nvl(bom_delete_status_code,'-99') -- bug fix 5276658
152 from bom_parameters bp
153 where bp.organization_id =msi.organization_id);
154
155
156 -- rkaza. 04/28/2005.
157 -- adding organization_id join between bet and msi for perf improvement
158 cursor child_config_cur(xgrp_id bom_explosion_temp.group_id%TYPE ) is
159 select distinct bet.component_item_id,msi_b.config_orgs,bet.plan_level
160 from bom_explosion_temp bet
161 , mtl_system_items msi
162 , mtl_system_items msi_b
163 where bet.group_id = xgrp_id
164 and bet.component_item_id = msi.inventory_item_id
165 and bet.organization_id = msi.organization_id
166 and msi_b.inventory_item_id = msi.base_item_id
167 and msi_b.organization_id = msi.organization_id
168 ORDER BY plan_level asc;
169
170 /*
171 bugfix 3777922
172
173 old definition cursor cfg_src_org_cur ( x_config_item number,
174 x_org_id number ) is
175 */
176 cursor cfg_src_org_cur ( x_config_item number) is
177 select distinct organization_id
178 from bom_cto_src_orgs_gt
179 where config_item_id = x_config_item ;
180 /* commented and rcv_org_id = x_org_id; */ -- bugfix 3777922
181
182
183
184 /*
185 bugfix 3777922
186 cursor called only for child configurations with CIB 1 and 2.
187 This cursor accepts parent config item id and provides end manufacturing orgs.
188 The sourcing chain for child configs should be traversed from the end manufacturing org
189 of the parent config.
190 */
191 cursor cfg_mfg_org_cur ( x_config_item number) is
192 select distinct organization_id
193 from bom_cto_src_orgs_gt
194 where config_item_id = x_config_item
195 and rcv_org_id = organization_id ;
196
197
198 i number := 1;
199 j number := 1;
200
201
202
203 l_record_exists boolean := FALSE ;
204
205 l_parent_org_id number ;
206
207
208 succ_item_arr t_cfg_item ;
209 fail_item_arr t_cfg_item ;
210 v_error_encountered boolean ;
211 fail_count number ;
212 succ_count number ;
213
214
215 l_config_description varchar2(50);
216 l_org_code varchar2(50);
217 change_record boolean ;
218
219 sql_stmt varchar2(5000); --Bugfix 5907413
220 flag number := 0; --Bugfix 5907413: TO check what parameters are passed to SQL
221
222 BEGIN
223
224
225 WriteToLog(' Begin Cost Rollup process with Debug Level: '||gDebugLevel);
226 WriteToLog(' Parameters passed..');
227 WriteToLog(' Organization Id : '||p_org_id);
228 WriteToLog(' Config Item Id : '||p_Config_item_Id);
229 WriteToLog(' Base Model Id : '||p_Model_Id);
230 WriteToLog(' Transacted number of days ago : '||p_num_of_days);
231 WriteToLog(' Upgrade : '||p_upgrade);
232 WriteToLog(' Perform Cost calculation : '||p_calc_costrollup);
233
234
235 l_stat_num :=10;
236
237
238 l_request_id := FND_GLOBAL.CONC_REQUEST_ID;
239 l_program_appl_id := FND_GLOBAL.PROG_APPL_ID;
240 l_program_id := FND_GLOBAL.CONC_PROGRAM_ID;
241
242
243 WriteToLog('request_id => '||l_request_id);
244 WriteToLog('program_appl_id => '||l_program_appl_id);
245 WriteToLog('program_id => '||l_program_id);
246
247
248 l_stat_num :=20;
249
250 if p_upgrade= '2' then
251 /* Bugfix 5907413: Commenting this SQL as it will no longer be used.
252 This SQL takes too long to populate config_cv so the CTO Calculate Cost
253 Rollup Program has performance issues. */
254 /*OPEN config_cv FOR
255 select distinct msi.inventory_item_id,
256 nvl(msi_b.config_orgs,'1')
257 from mtl_system_items msi,
258 mtl_system_items msi_b
259 where msi.base_item_id is not null
260 and msi.base_item_id = msi_b.inventory_item_id
261 and msi.organization_id = msi_b.organization_id
262 and msi.inventory_item_status_code <>
263 ( select bom_delete_status_code
264 from bom_parameters bp
265 where bp.organization_id =msi.organization_id
266 )
267 and (p_model_id is null or
268 msi.base_item_id = p_model_id)
269 and (p_config_item_id is null or
270 msi.inventory_item_id = p_config_item_id)
271 and (p_org_id is null or
272 msi.organization_id = p_org_id)
273 and (p_num_of_days is null or
274 msi.creation_date > ( trunc(sysdate) - p_num_of_days ))
275 ORDER BY 1; */
276 --Bugfix 5907413: Adding Dynamic SQL.
277 sql_stmt := 'select distinct msi.inventory_item_id, ' ||
278 ' nvl(msi_b.config_orgs,''1'') ' ||
279 ' from mtl_system_items msi, ' ||
280 ' mtl_system_items msi_b ' ||
281 ' where msi.base_item_id is not null ' ||
282 ' and msi.base_item_id = msi_b.inventory_item_id ' ||
283 ' and msi.organization_id = msi_b.organization_id ' ||
284 ' and msi.inventory_item_status_code <> ' ||
285 ' ( select bom_delete_status_code ' ||
286 ' from bom_parameters bp ' ||
287 ' where bp.organization_id =msi.organization_id ' ||
288 ' )';
289
290 IF p_model_id is not null THEN
291 sql_stmt := sql_stmt || ' and msi.base_item_id = :p_model_id ';
292 flag := flag + 1;
293 END IF;
294
295 IF p_config_item_id is not null THEN
296 sql_stmt := sql_stmt || ' and msi.inventory_item_id = :p_config_item_id ';
297 flag := flag + 2;
298 END IF;
299
300 IF p_org_id is not null THEN
301 sql_stmt := sql_stmt || ' and msi.organization_id = :p_org_id ';
302 flag := flag + 4;
303 END IF;
304
305 IF p_num_of_days is not null THEN
306 sql_stmt := sql_stmt || ' and msi.creation_date > ( trunc(sysdate) - :p_num_of_days ) ';
307 flag := flag + 8;
308 END IF;
309
310 sql_stmt := sql_stmt || ' ORDER BY 1 ';
311
312 WriteToLog('SQL: ' || substr(sql_stmt,1, 1500));
313 WriteToLog(substr(sql_stmt,1501,3000));
314 WriteToLog(substr(sql_stmt,3001,4500));
315 WriteToLog(substr(sql_stmt,4501,5000));
316 WriteToLog('flag = '||flag );
317
318 CASE flag
319
320 WHEN 0 then --No (optional) parameter is passed
321 OPEN config_cv FOR sql_stmt;
322
323 WHEN 1 then --Only Model_Id is passed
324 OPEN config_cv FOR sql_stmt USING p_model_id;
325
326 WHEN 2 then --Only Config_item_id is passed
327 OPEN config_cv FOR sql_stmt USING p_config_item_id;
328
329 WHEN 3 then --Model_id and config_item_id is passed
330 OPEN config_cv FOR sql_stmt USING p_model_id, p_config_item_id;
331
332 WHEN 4 then --Only organization_id is passed
333 OPEN config_cv FOR sql_stmt USING p_org_id;
334
335 WHEN 5 then --model_id and organization_id are passed
336 OPEN config_cv FOR sql_stmt USING p_model_id, p_org_id;
337
338 WHEN 6 then --config_item_id and organization_id are passed
339 OPEN config_cv FOR sql_stmt USING p_config_item_id, p_org_id;
340
341 WHEN 7 then --model_id, config_item_id and org_id are passed
342 OPEN config_cv FOR sql_stmt USING p_model_id, p_config_item_id, p_org_id;
343
344 WHEN 8 then --Only num_of_days is passed
345 OPEN config_cv FOR sql_stmt USING p_num_of_days;
346
347 WHEN 9 then --model_id and num_of_days are passed
348 OPEN config_cv FOR sql_stmt USING p_model_id, p_num_of_days;
349
350 WHEN 10 then --config_item_id and num_of_days are passed
351 OPEN config_cv FOR sql_stmt USING p_config_item_id, p_num_of_days;
352
353 WHEN 11 then --model_id, config_item_id and num_of_days are passed
354 OPEN config_cv FOR sql_stmt USING p_model_id, p_config_item_id, p_num_of_days;
355
356 WHEN 12 then --org_id and num_of_days are passed
357 OPEN config_cv FOR sql_stmt USING p_org_id, p_num_of_days;
358
359 WHEN 13 then --model_id, org_id and num_of_days are passed
360 OPEN config_cv FOR sql_stmt USING p_model_id, p_org_id, p_num_of_days;
361
362 WHEN 14 then --config_item_id, org_id and num_of_days are passed
363 OPEN config_cv FOR sql_stmt USING p_config_item_id, p_org_id, p_num_of_days;
364
365 WHEN 15 then --model_id, config_item_id, org_id and num_of_days (All) are passed
366 OPEN config_cv FOR sql_stmt USING p_model_id, p_config_item_id, p_org_id, p_num_of_days;
367
368 END CASE;
369 --Bugfix 5907413: End of Dynamic SQL
370
371 elsif p_upgrade= '1' then
372
373 if p_calc_costrollup = '2' then
374 WriteToLog(' Not doing cost rollup since preform cost rollup parameter is NO', 2);
375 return;
376 else
377 OPEN config_cv FOR
378 select distinct msi.inventory_item_id,
379 nvl( msi_b.config_orgs , '1')
380 from bom_cto_order_lines_upg bcol_upg,
381 mtl_system_items msi,
382 mtl_system_items msi_b
383 where bcol_upg.line_id = bcol_upg.ato_line_id /* bugfix 3777922 */
384 and bcol_upg.config_item_id = msi.inventory_item_id
385 and msi.base_item_id is not null
386 and msi.base_item_id = msi_b.inventory_item_id
387 and msi.organization_id = msi_b.organization_id
388 and bcol_upg.inventory_item_id = msi_b.inventory_item_id
389 and msi.inventory_item_status_code <>
390 ( select bom_delete_status_code
391 from bom_parameters bp
392 where bp.organization_id = msi.organization_id
393 )
394 ORDER BY 1;
395 end if;
396
397 end if;
398
399
400
401 << beginloop>>
402
403 LOOP
404
405 l_stat_num := 30;
406
407 SAVEPOINT CTOCCR;
408
409 FETCH config_cv into l_config_item,l_config_orgs;
410 EXIT when config_cv%notfound;
411
412
413 WriteToLog('-------------------------------------------------------------------');
414 WriteToLog('Processing Config Id : '||l_config_item||' Attrib: '||l_config_orgs);
415 WriteToLog('-------------------------------------------------------------------');
416
417
418 delete from bom_cto_src_orgs_gt ;
419
420
421 WriteToLog('deleted from bom_cto_src_orgs_gt : '|| sql%rowcount );
422 /*
423
424
425
426 -- check if l_config_item is in array already. Then we dont need to process this item again
427
428 if cfg_item_arr_cum.count > 0 then
429 -- check if config id exist
430 -- if exist then goto beginloop;
431 for l in cfg_item_arr_cum.FIRST .. cfg_item_arr_cum.LAST
432 loop
433 if cfg_item_arr_cum(l).cfg_item_id = l_config_item then
434 WriteToLog('Config Id : '||l_config_item||' already processed. ');
435 goto beginloop;
436 end if;
437 end loop;
438 end if;
439
440
441 */
442
443
444
445 -- Ideally, we should get the sourcing org for the parent config and populate
446 -- the array. But, we will defer it because, we need to get the child configs for this parent.
447 -- If get_config_details errors out for any reason, we should not populate the parent config
448 -- details in the array.
449
450
451 --
452 -- We will first try to get the child configs for the parent configuration
453 --
454
455 WriteToLog ('calling cto_transfer_price_pk.get_config_details ');
456
457 cto_transfer_price_pk.get_config_details(
458 p_item_id => l_config_item
459 , p_mode_id => 3 -- 'BOTH'
460 , p_configs_only => 'Y'
461 , x_group_id => l_group_id
462 , x_msg_count => l_msg_count
463 , x_msg_data => l_msg_data
464 , x_return_status => l_return_status ) ;
465
466 if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
467 WriteToLog('get_config_details: ' || 'get_config_details returned unexp error.');
468 ROLLBACK TO CTOCCR;
469 raise FND_API.G_EXC_UNEXPECTED_ERROR;
470
471 elsif (l_return_status = FND_API.G_RET_STS_ERROR) then
472 retcode := 1; -- Set this for conc request to end in WARNING
473 WriteToLog('get_config_details: ' || 'get_config_details returned expected error.');
474 ROLLBACK TO CTOCCR;
475 goto beginloop;
476
477 end if;
478
479 WriteToLog('get_config_details: ' || 'Success in get_config_details ', 5);
480 WriteToLog('get_config_details: ' || 'Group Id : '|| l_group_id, 5);
481
482
483
484
485 -- Parent Sourcing should be populated before child sourcing as child sourcing starts
486 -- from the organizations where parent sourcing ends.
487
488
489
490
491
492
493
494
495
496 -- now, populate the parent config details in the array
497
498 -- if l_config_orgs = 3 ,
499 -- cost rollup in all org for parents
500 -- get all_orgs where l_config_item exist
501
502 WriteToLog ('Going to populate details for parent config..');
503
504 if ( l_config_orgs = '3' )
505 OR
506 ( l_config_orgs in ('1' , '2') AND p_org_id is null )
507 OR
508 (p_upgrade = '1' ) -- bugfix 3777922
509 then
510
511 open cfg_org_cur (l_config_item);
512
513 LOOP
514 WriteToLog ('Inside cfg_org_cur loop.. ' );
515
516 fetch cfg_org_cur into l_org_id;
517 EXIT WHEN cfg_org_cur%NOTFOUND;
518 -- populate cfg_item_arr with parent config details
519
520
521
522 l_record_exists := FALSE ;
523
524 if cfg_item_arr.count > 0 then
525 -- check if config id exist
526 -- if exist then goto beginloop;
527 for l in cfg_item_arr.FIRST .. cfg_item_arr.LAST
528 loop
529 if cfg_item_arr(l).cfg_item_id = l_config_item AND
530 cfg_item_arr(l).cfg_org_id = l_org_id then
531
532 WriteToLog('Config Id : '||l_config_item|| ' org id ' || l_org_id
533 || ' already processed. ');
534
535 l_record_exists := TRUE ;
536 exit ;
537
538 end if;
539 end loop;
540 end if;
541
542
543
544
545 if( l_record_exists = FALSE ) then
546 cfg_item_arr(i).cfg_item_id := l_config_item;
547 cfg_item_arr(i).cfg_org_id := l_org_id;
548 WriteToLog('Index: ('||i||') -> Config item id: '||cfg_item_arr(i).cfg_item_id||
549 ' Org Id: '||cfg_item_arr(i).cfg_org_id , 5);
550
551 i := i + 1;
552
553
554 end if;
555
556
557 END LOOP;
558 close cfg_org_cur;
559
560 elsif ( l_config_orgs in ('1' , '2') AND p_org_id is not null AND p_upgrade = '2') then -- bugfix 3777922
561
562 --
563 -- get sourcing orgs for p_org_id and l_config_item /* sajani */
564 -- load cfg_item_arr with p_org_id + sourcing orgs for l_config_item
565 --
566
567 --
568 -- following proc populates temp table bcso_gt
569 -- with config item id and relevant sourcing org
570 --
571
572 WriteToLog ('Calling get_sourcing org..');
573
574 get_sourcing_org (p_config_item_id => l_config_item ,
575 p_organization_id => p_org_id,
576 p_return_status => l_return_status );
577
578 WriteToLog ('** l_return_status = '|| l_return_status );
579
580 if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
581 WriteToLog('get_sourcing_org: ' || 'raised Unexpected error.');
582 ROLLBACK TO CTOCCR;
583 raise FND_API.G_EXC_UNEXPECTED_ERROR;
584
585 elsif (l_return_status = FND_API.G_RET_STS_ERROR) then
586 retcode := 1; -- Set this for conc request to end in WARNING
587 WriteToLog('get_sourcing_org: ' || 'raised Expected error.');
588 ROLLBACK TO CTOCCR;
589 goto beginloop;
590 else
591 WriteToLog('get_sourcing_org: SUCCESSFULLL !!!! ');
592
593
594 end if;
595
596
597 WriteToLog('get_sourcing_org: Adding dummy record for ship org. ');
598
599 insert into bom_cto_src_orgs_gt
600 (
601 config_item_id,
602 organization_id,
603 rcv_org_id,
604 creation_date,
605 created_by,
606 last_update_date,
607 last_updated_by,
608 last_update_login,
609 program_application_id,
610 program_id,
611 program_update_date
612 )
613 select
614 l_config_item,
615 p_org_id,
616 null , /* this is intentionally null to indicate origin 100%transfer from org */
617 sysdate, -- creation_date
618 gUserId, -- created_by
619 sysdate, -- last_update_date
620 gUserId, -- last_updated_by
621 gLoginId, -- last_update_login
622 null, -- program_application_id,??
623 null, -- program_id,??
624 sysdate -- program_update_date
625 from dual
626 where NOT EXISTS
627 (select NULL
628 from bom_cto_src_orgs_gt
629 where rcv_org_id = p_org_id
630 and organization_id = p_org_id
631 and config_item_id = l_config_item );
632
633
634 --
635 -- query the bom_cto_src_orgs_gt table and get all sourcing orgs for parent config.
636 --
637 open cfg_src_org_cur (l_config_item); -- bugfix 3777622
638 LOOP
639 WriteToLog ('inside cfg_src_org_cur loop for parent.');
640
641 fetch cfg_src_org_cur into l_org_id;
642 EXIT WHEN cfg_src_org_cur%NOTFOUND;
643
644 --
645 -- populate cfg_item_arr with parent config and its sourcing orgs
646 --
647
648
649
650 l_record_exists := FALSE ;
651
652 if cfg_item_arr.count > 0 then
653 -- check if config id exist
654 -- if exist then goto beginloop;
655 for l in cfg_item_arr.FIRST .. cfg_item_arr.LAST
656 loop
657 if cfg_item_arr(l).cfg_item_id = l_config_item AND
658 cfg_item_arr(l).cfg_org_id = l_org_id then
659
660 WriteToLog('Config Id : '||l_config_item|| ' org id ' || l_org_id
661 || ' already processed. ');
662
663 l_record_exists := TRUE ;
664 exit ;
665
666 end if;
667 end loop;
668 end if;
669
670
671 if( l_record_exists = FALSE ) then
672 cfg_item_arr(i).cfg_item_id := l_config_item;
673 cfg_item_arr(i).cfg_org_id := l_org_id;
674 WriteToLog('Index: ('||i||') -> Config item id: '||cfg_item_arr(i).cfg_item_id||
675 ' Org Id: '||cfg_item_arr(i).cfg_org_id);
676
677 i := i + 1;
678
679 end if;
680
681
682 END LOOP;
683 close cfg_src_org_cur;
684
685 end if; /* l_config_orgs check */
686
687 --
688 -- At this point structure is loaded with parent configs, child configs and relevant orgs
689 --
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704 --
705 -- Now, get all child for this grp id
706 --
707
708 l_stat_num := 31;
709
710 open child_config_cur(l_group_id) ;
711 loop
712 WriteToLog ('inside child_config_cur loop..' );
713 fetch child_config_cur into l_child_config_item, l_child_config_orgs, l_plan_level;
714
715 exit when child_config_cur%NOTFOUND;
716
717 WriteToLog( ' fetched ' || l_child_config_item || ' Orgs ' || l_child_config_orgs
718 || ' p_upgrade ' || p_upgrade , 5 ) ;
719
720 if ( l_child_config_orgs = '3' )
721 OR
722 ( l_child_config_orgs in ('1' , '2') AND p_org_id is null )
723 OR
724 (p_upgrade = '1') -- bugfix 3777922
725 then
726 l_stat_num := 32;
727 WriteToLog ('inside child condition 1 .. ');
728
729 open cfg_org_cur ( l_child_config_item);
730
731 LOOP
732
733 WriteToLog ('inside cfg_org_cur loop.. ');
734 fetch cfg_org_cur into l_child_org_id;
735
736 EXIT WHEN cfg_org_cur%NOTFOUND;
737
738
739
740
741 -- check if l_config_item is in array already. Then we dont need to process this item again
742
743 l_record_exists := FALSE ;
744
745 if cfg_item_arr.count > 0 then
746 -- check if config id exist
747 -- if exist then goto beginloop;
748 for l in cfg_item_arr.FIRST .. cfg_item_arr.LAST
749 loop
750 if cfg_item_arr(l).cfg_item_id = l_child_config_item AND
751 cfg_item_arr(l).cfg_org_id = l_child_org_id then
752
753 WriteToLog('Config Id : '|| l_child_config_item|| ' org id ' || l_child_org_id
754 || ' already processed. ');
755
756 l_record_exists := TRUE ;
757 exit ;
758
759 end if;
760 end loop;
761 end if;
762
763
764
765
766 if( l_record_exists = FALSE ) then
767 --
768 -- populate cfg_item_arr with child config details
769 --
770
771 cfg_item_arr(i).cfg_item_id := l_child_config_item;
772 cfg_item_arr(i).cfg_org_id := l_child_org_id;
773 WriteToLog('Index: ('||i||') -> Child Config item id: '||cfg_item_arr(i).cfg_item_id||
774 ' Org Id: '||cfg_item_arr(i).cfg_org_id, 5);
775
776 i := i + 1;
777
778 end if;
779
780
781 END LOOP;
782
783 close cfg_org_cur;
784
785 elsif ( l_child_config_orgs in ('1' , '2') AND p_org_id is not null AND p_upgrade = '2' ) then -- bugfix 3777922
786
787 WriteToLog ('inside child condition 2 .. ');
788 -- get sourcing orgs for p_org_id and child_config_orgs
789 -- load cfg_item_arr with p_org_id + sourcing orgs for child_config_orgs
790
791 -- following proc populates temp table bcso_gt
792 -- with config item id and relevant sourcing org
793
794
795
796 open cfg_mfg_org_cur (l_config_item); -- bugfix 3777622
797 LOOP
798 WriteToLog ('inside cfg_src_org_cur loop for parent.');
799
800 fetch cfg_mfg_org_cur into l_parent_org_id;
801 EXIT WHEN cfg_mfg_org_cur%NOTFOUND;
802
803
804 WriteToLog ('calling get_sourcing_org..' || l_child_config_item
805 || ' org ' || l_parent_org_id);
806 get_sourcing_org (p_config_item_id => l_child_config_item ,
807 p_organization_id => l_parent_org_id ,
808 p_return_status => l_return_status );
809
810 if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
811 WriteToLog('get_sourcing_org: ' || 'raised Unexpected error.');
812 ROLLBACK TO CTOCCR;
813 raise FND_API.G_EXC_UNEXPECTED_ERROR;
814
815 elsif (l_return_status = FND_API.G_RET_STS_ERROR) then
816 retcode := 1; -- Set this for conc request to end in WARNING
817 WriteToLog('get_sourcing_org: ' || 'raised Expected error.');
818 ROLLBACK TO CTOCCR;
819 goto beginloop;
820
821 end if;
822
823
824 WriteToLog('get_sourcing_org: Adding dummy record for ship org. ');
825
826 insert into bom_cto_src_orgs_gt
827 (
828 config_item_id,
829 organization_id,
830 rcv_org_id,
831 creation_date,
832 created_by,
833 last_update_date,
834 last_updated_by,
835 last_update_login,
836 program_application_id,
837 program_id,
838 program_update_date
839 )
840 select
841 l_child_config_item,
842 l_parent_org_id, -- bugfix 3777922
843 null , -- bugfix 3777922 /* this is intentionally null for origin 100% transfer from org*/
844 sysdate, -- creation_date
845 gUserId, -- created_by
846 sysdate, -- last_update_date
847 gUserId, -- last_updated_by
848 gLoginId, -- last_update_login
849 null, -- program_application_id,??
850 null, -- program_id,??
851 sysdate -- program_update_date
852 from dual
853 where NOT EXISTS
854 (select NULL
855 from bom_cto_src_orgs_gt
856 where rcv_org_id = l_parent_org_id -- bugfix 3777922
857 and organization_id = l_parent_org_id -- bugfix 3777922
858 and config_item_id = l_child_config_item ) ; -- bugfix 3941383
859
860
861
862 end loop ;
863
864
865 close cfg_mfg_org_cur ;
866
867
868
869
870 --
871 -- query the bom_cto_src_orgs_gt table and get all sourcing orgs for child config.
872 --
873 /* old call open cfg_src_org_cur (l_child_config_item,p_org_id); */
874
875
876 open cfg_src_org_cur (l_child_config_item); -- bugfix 3777922
877 LOOP
878 WriteToLog ('inside cfg_src_org_cur loop for child');
879
880 fetch cfg_src_org_cur into l_org_id;
881 EXIT WHEN cfg_src_org_cur%NOTFOUND;
882
883 -- populate cfg_item_arr with parent config sourcing
884
885
886 l_record_exists := FALSE ;
887
888 if cfg_item_arr.count > 0 then
889 -- check if config id exist
890 -- if exist then goto beginloop;
891 for l in cfg_item_arr.FIRST .. cfg_item_arr.LAST
892 loop
893 if cfg_item_arr(l).cfg_item_id = l_child_config_item AND
894 cfg_item_arr(l).cfg_org_id = l_org_id then
895
896 WriteToLog('Config Id : '||l_child_config_item|| ' org id ' || l_org_id
897 || ' already processed. ');
898
899 l_record_exists := TRUE ;
900 exit ;
901
902 end if;
903 end loop;
904 end if;
905
906
907
908
909
910
911
912
913
914 if( l_record_exists = FALSE ) then
915 cfg_item_arr(i).cfg_item_id := l_child_config_item;
916 cfg_item_arr(i).cfg_org_id := l_org_id;
917 WriteToLog('Index: ('||i||') -> Child Config item id: '||cfg_item_arr(i).cfg_item_id||
918 ' Org Id: '||cfg_item_arr(i).cfg_org_id, 5);
919
920 i := i + 1;
921
922
923 end if;
924
925
926 END LOOP;
927
928 close cfg_src_org_cur;
929
930 end if; /* end of child_config_orgs check */
931
932
933
934
935
936
937
938 end loop; /* end of child_config_cur cursor loop */
939
940 close child_config_cur ;
941
942
943 WriteToLog(' Closed child_config_cur ' ) ;
944
945
946
947 if( cfg_item_arr.count >= 100 ) then /* Start bugfix 3784283 */
948
949
950 --
951 -- Passing array to the cost rollup API
952 --
953
954
955
956 SavePoint S1;
957
958 WriteToLog('Calling CTO_CONFIG_COST_PK.Cost_Roll_Up_ML.. ', 5);
959 WriteToLog('Calling CTO_CONFIG_COST_PK.Cost_Roll_Up_ML.. ' || cfg_item_arr.count , 5);
960
961 WriteToLog ('==============================================');
962 WriteToLog (' Collected Data for '|| config_cv%ROWCOUNT || ' parent configs or processing .');
963 WriteToLog ('==============================================');
964
965 l_status := CTO_CONFIG_COST_PK.Cost_Roll_Up_ML(
966 p_cfg_itm_tbl => cfg_item_arr,
967 x_msg_count => l_msg_count,
968 x_msg_data => l_msg_data
969 );
970
971 l_stat_num := 231;
972
973 if (l_status = 0) then
974
975 WriteToLog('CTO_CONFIG_COST_PK.Cost_Roll_Up_ML returned error. '||l_msg_data);
976
977
978 WriteToLog('following batch of records could not be processed successfully .. ', 5);
979
980 for curr_count in 1..cfg_item_arr.count
981 loop
982 fail_count := fail_item_arr.count + 1;
983 fail_item_arr(fail_count).cfg_item_id := cfg_item_arr(curr_count).cfg_item_id ;
984 fail_item_arr(fail_count).cfg_org_id := cfg_item_arr(curr_count).cfg_org_id ;
985
986 WriteToLog('Config Id: ' || cfg_item_arr(curr_count).cfg_item_id ||
987 ' Org Id: ' || cfg_item_arr(curr_count).cfg_org_id , 5);
988 end loop;
989
990 WriteToLog('Total records that could not be processed successfully .. ' || cfg_item_arr.count , 5);
991
992
993 Rollback TO S1 ;
994
995 v_error_encountered := TRUE ;
996
997
998 else
999
1000 for curr_count in 1..cfg_item_arr.count
1001 loop
1002 succ_count := succ_item_arr.count + 1 ;
1003 succ_item_arr(succ_count).cfg_item_id := cfg_item_arr(curr_count).cfg_item_id ;
1004 succ_item_arr(succ_count).cfg_org_id := cfg_item_arr(curr_count).cfg_org_id ;
1005 end loop;
1006
1007
1008 end if;
1009
1010
1011 cfg_item_arr.delete ;
1012
1013 i := 1 ; /* fix for bug 3975083 */
1014
1015 end if ; /* cfg_item_arr.count >= 100 */ /* end bugfix 3784283 */
1016
1017
1018
1019
1020
1021
1022 END LOOP; /* parent config loop */ /* bugfix 3777922 */
1023
1024
1025
1026
1027 if config_cv%ROWCOUNT = 0 then
1028 WriteToLog ('Nothing to process.');
1029 return;
1030 else
1031 WriteToLog ('==============================================');
1032 WriteToLog ('Total Processed '|| config_cv%ROWCOUNT || ' parent configs.');
1033 WriteToLog ('==============================================');
1034 end if;
1035
1036
1037
1038 l_stat_num := 331;
1039
1040
1041 /* Process remaining records < 100 */
1042 if( cfg_item_arr.count > 0 ) then -- bugfix 3784283
1043
1044
1045 --
1046 -- Passing array to the cost rollup API
1047 --
1048
1049
1050
1051 SavePoint S1;
1052
1053 WriteToLog('Calling CTO_CONFIG_COST_PK.Cost_Roll_Up_ML.. ', 5);
1054 WriteToLog('Calling CTO_CONFIG_COST_PK.Cost_Roll_Up_ML.. ' || cfg_item_arr.count , 5);
1055
1056 WriteToLog ('==============================================');
1057 WriteToLog (' Collected Data for '|| config_cv%ROWCOUNT || ' parent configs or processing .');
1058 WriteToLog ('==============================================');
1059
1060 l_status := CTO_CONFIG_COST_PK.Cost_Roll_Up_ML(
1061 p_cfg_itm_tbl => cfg_item_arr,
1062 x_msg_count => l_msg_count,
1063 x_msg_data => l_msg_data
1064 );
1065
1066 if (l_status = 0) then
1067
1068 WriteToLog('CTO_CONFIG_COST_PK.Cost_Roll_Up_ML returned error. '||l_msg_data);
1069
1070
1071 WriteToLog ('====================================================================', 1);
1072 WriteToLog('following batch of records could not be processed successfully .. ', 1);
1073 WriteToLog ('====================================================================', 1);
1074
1075 for curr_count in 1..cfg_item_arr.count
1076 loop
1077 fail_count := fail_item_arr.count + 1 ;
1078 fail_item_arr(fail_count).cfg_item_id := cfg_item_arr(curr_count).cfg_item_id ;
1079 fail_item_arr(fail_count).cfg_org_id := cfg_item_arr(curr_count).cfg_org_id ;
1080
1081 WriteToLog('Config Id: ' || cfg_item_arr(curr_count).cfg_item_id ||
1082 ' Org Id: ' || cfg_item_arr(curr_count).cfg_org_id , 1);
1083 end loop;
1084
1085 WriteToLog ('====================================================================', 1);
1086 WriteToLog('Total records for this batch that could not be processed successfully .. ' || cfg_item_arr.count , 1);
1087 WriteToLog ('====================================================================', 1);
1088
1089
1090 Rollback TO S1 ;
1091
1092 v_error_encountered := TRUE ;
1093
1094
1095 else
1096
1097 for curr_count in 1..cfg_item_arr.count
1098 loop
1099 succ_count := succ_item_arr.count + 1 ;
1100 succ_item_arr(succ_count).cfg_item_id := cfg_item_arr(curr_count).cfg_item_id ;
1101 succ_item_arr(succ_count).cfg_org_id := cfg_item_arr(curr_count).cfg_org_id ;
1102 end loop;
1103
1104
1105 end if;
1106
1107
1108 end if ; /* cfg_item_arr.count > 0 */ -- bugfix 3784283
1109
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119 l_stat_num := 431;
1120
1121
1122
1123 WriteToLog ('====================================================================', 1);
1124 WriteToLog('following records have been processed successfully .. ', 1);
1125 WriteToLog ('====================================================================', 1);
1126 for curr_count in 1..succ_item_arr.count
1127 loop
1128
1129 l_stat_num := 441;
1130
1131 l_config_description := 'N/A' ;
1132 l_org_code := 'N/A' ;
1133
1134 l_stat_num := 451;
1135
1136 begin
1137 SELECT substrb(kfv.concatenated_segments,1,35),
1138 mp.organization_code
1139 INTO l_config_description, l_org_code
1140 FROM mtl_system_items_kfv kfv, mtl_parameters mp
1141 WHERE kfv.inventory_item_id = succ_item_arr(curr_count).cfg_item_id
1142 AND kfv.organization_id = succ_item_arr(curr_count).cfg_org_id
1143 AND kfv.organization_id = mp.organization_id;
1144
1145 exception
1146 when others then
1147
1148 null ;
1149 end ;
1150
1151
1152 l_stat_num := 461;
1153 WriteToLog('Config Id: ' || succ_item_arr(curr_count).cfg_item_id ||
1154 ' Org Id: ' || succ_item_arr(curr_count).cfg_org_id ||
1155 ' Item Name ' || l_config_description ||
1156 ' Org Code ' || l_org_code
1157 , 1);
1158
1159 end loop;
1160 WriteToLog ('====================================================================', 1);
1161 WriteToLog('Total records processed successfully .. ' || succ_item_arr.count , 1);
1162 WriteToLog ('====================================================================', 1);
1163
1164 commit ;
1165
1166
1167 if( v_error_encountered ) then
1168 WriteToLog ('====================================================================', 1);
1169 WriteToLog('following records have not been processed successfully .. ', 1);
1170 WriteToLog ('====================================================================', 1);
1171 for curr_count in 1..fail_item_arr.count
1172 loop
1173
1174
1175 l_config_description := 'N/A' ;
1176 l_org_code := 'N/A' ;
1177
1178
1179 begin
1180 SELECT substrb(kfv.concatenated_segments,1,35),
1181 mp.organization_code
1182 INTO l_config_description, l_org_code
1183 FROM mtl_system_items_kfv kfv, mtl_parameters mp
1184 WHERE kfv.inventory_item_id = fail_item_arr(curr_count).cfg_item_id
1185 AND kfv.organization_id = fail_item_arr(curr_count).cfg_org_id
1186 AND kfv.organization_id = mp.organization_id;
1187
1188 exception
1189 when others then
1190 null ;
1191 end ;
1192
1193
1194 WriteToLog('Config Id: ' || fail_item_arr(curr_count).cfg_item_id ||
1195 ' Org Id: ' || fail_item_arr(curr_count).cfg_org_id ||
1196 ' Item Name ' || l_config_description ||
1197 ' Org Code ' || l_org_code
1198 , 1);
1199
1200 end loop;
1201 WriteToLog ('====================================================================', 1);
1202 WriteToLog('Total records not been processed successfully .. ' || fail_item_arr.count , 1);
1203 WriteToLog ('====================================================================', 1);
1204 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1205 end if;
1206
1207
1208
1209
1210
1211 if config_cv%ISOPEN then
1212 CLOSE config_cv;
1213 end if;
1214
1215 EXCEPTION
1216 when FND_API.G_EXC_ERROR then
1217 WriteToLog('cto_cost_rollup: ' || 'EXPECTED ERROR:' || to_char(l_stat_num),1);
1218 retcode := 1;--completes with warning status -- Bug Fix 5527848
1219 l_return_status := FND_API.G_RET_STS_ERROR;
1220
1221
1222 when FND_API.G_EXC_UNEXPECTED_ERROR then
1223 WriteToLog('cto_cost_rollup: ' || 'UNEXPECTED ERROR:' || to_char(l_stat_num),1);
1224 retcode := 1;--completes with warning status --Bug Fix 5527848
1225 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1226
1227
1228 when OTHERS then
1229 WriteToLog('OTHERS excpn in cto_cost_rollup: '||to_char(l_stat_num)||'::'||sqlerrm);
1230 errbuf := 'Completed with error';
1231 retcode := 2;--completes with error status
1232 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1233
1234 END cto_cost_rollup;
1235
1236 PROCEDURE get_sourcing_org (
1237 p_config_item_id number
1238 , p_organization_id number
1239 , p_return_status OUT NOCOPY varchar2 )
1240 is
1241
1242 v_t_sourcing_info CTO_MSUTIL_PUB.sourcing_info;
1243 v_buy_traversed boolean := false ;
1244 v_source_type mrp_sources_v.source_type%type ;
1245 l_make_buy_code mtl_system_items.planning_make_buy_code%type ;
1246
1247 l_curr_src_org mrp_sources_v.source_organization_id%type ;
1248 l_source_type mrp_sources_v.source_type%type ;
1249 l_curr_assg_type mrp_sources_v.assignment_type%type ;
1250 l_curr_rank mrp_sources_v.rank%type ;
1251 v_sourcing_rule_exists varchar2(10) ;
1252
1253
1254 x_exp_error_code NUMBER ;
1255 x_return_status varchar2(100);
1256
1257 lStmtNum number ;
1258 x_msg_data varchar2(250) ;
1259 x_msg_count number ;
1260 v_bcso_count number ;
1261 l_circular_src varchar2(1);
1262 BEGIN
1263
1264 WriteToLog( 'Entered get_sourcing_org to find sourcing chain with item id: ' || p_config_item_id ||
1265 ' and orgn_id: ' || p_organization_id) ;
1266
1267 lStmtNum := 0 ;
1268
1269 p_return_status := FND_API.G_RET_STS_SUCCESS;
1270
1271 v_buy_traversed := FALSE ;
1272
1273 WriteToLog( 'calling query sourcing org ') ;
1274
1275 CTO_MSUTIL_PUB.query_sourcing_org_ms(
1276 p_inventory_item_id => p_config_item_id
1277 ,p_organization_id => p_organization_id
1278 ,p_sourcing_rule_exists => v_sourcing_rule_exists
1279 ,p_source_type => v_source_type
1280 ,p_t_sourcing_info => v_t_sourcing_info
1281 ,x_exp_error_code => x_exp_error_code
1282 ,x_return_status => x_return_status );
1283
1284 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1285
1286 WriteToLog(' Error in query_sourcing_org_ms.. ');
1287 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1288
1289 END IF;
1290
1291
1292 WriteToLog( 'output query sourcing org rule ' || v_t_sourcing_info.sourcing_rule_id.count) ;
1293 WriteToLog( 'output query sourcing org src org ' || v_t_sourcing_info.source_organization_id.count) ;
1294 WriteToLog( 'output query sourcing org src type' || v_t_sourcing_info.source_type.count) ;
1295
1296
1297
1298 if( v_t_sourcing_info.source_type.count > 0 ) then
1299
1300 FOR i in 1..v_t_sourcing_info.source_type.count
1301 LOOP
1302
1303 WriteToLog( 'output query sourcing org type ' || v_t_sourcing_info.source_type(i)) ;
1304
1305 if( v_t_sourcing_info.source_type(i) in ( 1, 2 ) ) then
1306 /* 1 = Transfer From, 2 = Make At */
1307
1308 WriteToLog( ' came into type 1,2 ') ;
1309
1310 begin
1311 lStmtNum := 1 ;
1312 l_curr_src_org := v_t_sourcing_info.source_organization_id(i) ;
1313
1314 lStmtNum := 2 ;
1315 l_source_type := v_t_sourcing_info.source_type(i) ;
1316
1317 lStmtNum := 3 ;
1318 l_curr_assg_type := v_t_sourcing_info.assignment_type(i) ;
1319
1320 lStmtNum := 4 ;
1321 l_curr_rank := v_t_sourcing_info.rank(i) ;
1322
1323 exception
1324
1325 when others then
1326
1327 WriteToLog( ' errored into type 1,2 at ' || lStmtNum || ' err ' || SQLERRM ) ;
1328 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1329
1330 end ;
1331
1332
1333 if( l_source_type = 1) then
1334 WriteToLog ( ' Check Circular Sourcing ..');
1335
1336 lStmtNum := 8;
1337
1338 begin
1339
1340 select distinct 'Y'
1341 into l_circular_src
1342 from bom_cto_src_orgs_gt
1343 where config_item_id = p_config_item_id
1344 and rcv_org_id = l_curr_src_org;
1345
1346 exception
1347 when no_data_found then
1348 l_circular_src := 'N';
1349 end;
1350
1351 lStmtNum := 9;
1352 IF l_circular_src = 'Y' THEN
1353 WriteToLog( ' Circular Sourcing detected ..');
1354 raise FND_API.G_EXC_ERROR;
1355 END IF;
1356
1357
1358 end if;
1359
1360
1361 WriteToLog( 'going to insert bcso for type 1,2 ') ;
1362
1363 lStmtNum := 10 ;
1364
1365 begin
1366
1367
1368 insert into bom_cto_src_orgs_gt
1369 (
1370 config_item_id,
1371 organization_id,
1372 rcv_org_id,
1373 creation_date,
1374 created_by,
1375 last_update_date,
1376 last_updated_by,
1377 last_update_login,
1378 program_application_id,
1379 program_id,
1380 program_update_date
1381 )
1382 select
1383 p_config_item_id,
1384 l_curr_src_org,
1385 p_organization_id,
1386 sysdate, -- creation_date
1387 gUserId, -- created_by
1388 sysdate, -- last_update_date
1389 gUserId, -- last_updated_by
1390 gLoginId, -- last_update_login
1391 null, -- program_application_id,??
1392 null, -- program_id,??
1393 sysdate -- program_update_date
1394 from dual
1395 where NOT EXISTS
1396 (select NULL
1397 from bom_cto_src_orgs_gt
1398 where rcv_org_id = p_organization_id
1399 and organization_id = l_curr_src_org
1400 and config_item_id = p_config_item_id );
1401
1402 exception
1403
1404 when others then
1405
1406 WriteToLog( ' errored inserting at ' || lStmtNum || ' err ' || SQLERRM ) ;
1407 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1408
1409 end ;
1410
1411
1412 WriteToLog( 'inserted' || sql%rowcount || 'records in bcso for type 1,2.') ;
1413 WriteToLog( 'inserted bcso for type 1,2 rcv_org = ' || p_organization_id || ', src_org = ' || l_curr_src_org) ;
1414
1415
1416 elsif( v_t_sourcing_info.source_type(i) = 3 and NOT v_buy_traversed ) then
1417 /* 3 = Buy From */
1418
1419 v_buy_traversed := TRUE ;
1420
1421 WriteToLog( ' came into type 3 ' , 1 ) ;
1422
1423 begin
1424 lStmtNum := 21 ;
1425 l_curr_src_org := nvl( v_t_sourcing_info.source_organization_id(i) , p_organization_id ) ;
1426 lStmtNum := 22 ;
1427 l_source_type := v_t_sourcing_info.source_type(i) ;
1428
1429 lStmtNum := 23 ;
1430 l_curr_assg_type := v_t_sourcing_info.assignment_type(i) ;
1431
1432 lStmtNum := 24 ;
1433 l_curr_rank := v_t_sourcing_info.rank(i) ;
1434
1435 exception
1436
1437 when others then
1438
1439 WriteToLog( ' errored into type 3 at ' || lStmtNum || ' err ' || SQLERRM) ;
1440 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1441
1442 end ;
1443
1444
1445 lStmtNum := 30 ;
1446
1447
1448 begin
1449
1450
1451 insert into bom_cto_src_orgs_gt
1452 (
1453 config_item_id,
1454 organization_id,
1455 rcv_org_id,
1456 creation_date,
1457 created_by,
1458 last_update_date,
1459 last_updated_by,
1460 last_update_login,
1461 program_application_id,
1462 program_id,
1463 program_update_date
1464 )
1465 select
1466 p_config_item_id,
1467 l_curr_src_org,
1468 p_organization_id,
1469 sysdate, -- creation_date
1470 gUserId, -- created_by
1471 sysdate, -- last_update_date
1472 gUserId, -- last_updated_by
1473 gLoginId, -- last_update_login
1474 null, -- program_application_id,??
1475 null, -- program_id,??
1476 sysdate -- program_update_date
1477 from dual
1478 where NOT EXISTS
1479 (select NULL
1480 from bom_cto_src_orgs_gt
1481 where rcv_org_id = p_organization_id
1482 and organization_id = l_curr_src_org
1483 and config_item_id = p_config_item_id );
1484
1485 exception
1486
1487 when others then
1488
1489 WriteToLog( ' errored inserting at ' || lStmtNum || ' err ' || SQLERRM ) ;
1490 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1491
1492 end ;
1493
1494
1495
1496 WriteToLog( 'inserted' || sql%rowcount || 'records in bcso for type 3.') ;
1497 WriteToLog( 'inserted bcso for type 3 rcv_org = ' || p_organization_id || ', src_org = ' || l_curr_src_org) ;
1498
1499
1500 end if;
1501
1502
1503 lStmtNum := 40 ;
1504 if( v_t_sourcing_info.source_type(i) = 1 ) then
1505
1506 WriteToLog( 'calling process sourcing chain recursive ') ;
1507
1508 lStmtNum := 50 ;
1509
1510 get_sourcing_org( p_config_item_id
1511 , v_t_sourcing_info.source_organization_id(i)
1512 , x_return_status
1513 );
1514 if (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
1515 WriteToLog('get_sourcing_org: ' || 'raised Unexpected error.');
1516 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1517
1518 elsif (x_return_status = FND_API.G_RET_STS_ERROR) then
1519 WriteToLog('get_sourcing_org: ' || 'raised Expected error.');
1520 raise FND_API.G_EXC_ERROR;
1521
1522 end if;
1523
1524 end if;
1525
1526
1527 END LOOP ;
1528
1529
1530 else
1531
1532 -- When there is no sourcing rule defined we need to check for the make_buy_type of the
1533 -- item to determine the buy model
1534
1535
1536 WriteToLog('get_sourcing_org : ' || 'No sourcing rule defined..');
1537
1538 lStmtNum := 57;
1539
1540 -- When the item is not defined in the sourcing org it needs to be
1541 -- treated as INVALID sourcing
1542
1543 BEGIN
1544
1545 SELECT planning_make_buy_code
1546 INTO l_make_buy_code
1547 FROM MTL_SYSTEM_ITEMS
1548 WHERE inventory_item_id = p_config_item_id
1549 AND organization_id = p_organization_id ;
1550
1551 EXCEPTION
1552 WHEN NO_DATA_FOUND THEN
1553
1554 WriteToLog('get_sourcing_org: ' || 'ERROR::The item is not defined in the sourcing org');
1555 -- The following message handling is modified by Renga Kannan
1556 -- We need to give the add for once to FND function and other
1557 -- to OE, in both cases we need to set the message again
1558 -- This is because if we not set the token once again the
1559 -- second add will not get the message.
1560
1561 cto_msg_pub.cto_message('BOM','CTO_INVALID_SOURCING');
1562 raise FND_API.G_EXC_ERROR;
1563
1564 END;
1565
1566 l_curr_src_org := p_organization_id ;
1567
1568 if( l_make_buy_code = 2) then
1569
1570 l_source_type := 3 ;
1571 else
1572 l_source_type := 2 ;
1573 end if;
1574
1575 l_curr_rank := null ;
1576
1577
1578 begin
1579
1580 insert into bom_cto_src_orgs_gt
1581 (
1582 config_item_id,
1583 rcv_org_id,
1584 organization_id,
1585 creation_date,
1586 created_by,
1587 last_update_date,
1588 last_updated_by,
1589 last_update_login,
1590 program_application_id,
1591 program_id,
1592 program_update_date
1593 )
1594 select p_config_item_id ,
1595 p_organization_id, -- will work for end of chain source or no source
1596 p_organization_id,
1597 sysdate, -- creation_date
1598 gUserId, -- created_by
1599 sysdate, -- last_update_date
1600 gUserId, -- last_updated_by
1601 gLoginId, -- last_update_login
1602 null, -- program_application_id,??
1603 null, -- program_id,??
1604 sysdate -- program_update_date
1605 from dual
1606 where NOT EXISTS
1607 (select NULL
1608 from bom_cto_src_orgs_gt
1609 where rcv_org_id = p_organization_id
1610 and organization_id =p_organization_id
1611 and config_item_id = p_config_item_id );
1612
1613 exception
1614
1615 when others then
1616
1617 WriteToLog( ' errored inserting at ' || lStmtNum || ' err ' || SQLERRM ) ;
1618 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1619
1620 end ;
1621
1622
1623 WriteToLog( 'inserted bcso for end of chain ' || SQL%rowcount) ;
1624
1625 end if;
1626
1627 EXCEPTION
1628 when FND_API.G_EXC_ERROR then
1629 WriteToLog('get_sourcing_org: ' || 'EXPECTED ERROR:' || to_char(lStmtNum),1);
1630 p_return_status := FND_API.G_RET_STS_ERROR;
1631
1632
1633 when FND_API.G_EXC_UNEXPECTED_ERROR then
1634 WriteToLog('get_sourcing_org: ' || 'UNEXPECTED ERROR:' || to_char(lStmtNum),1);
1635 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1636
1637
1638 when OTHERS then
1639 WriteToLog('OTHERS excpn in get_sourcing_org: '||to_char(lStmtNum)||'::'||sqlerrm);
1640 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1641
1642
1643 END get_sourcing_org;
1644
1645
1646 PROCEDURE WriteToLog (p_message in varchar2 default null,
1647 p_level in number default 0) is
1648 begin
1649 if gDebugLevel >= p_level then
1650 /* fnd_file.put_line (fnd_file.log, p_message); */
1651 oe_debug_pub.add (p_message);
1652 end if;
1653 end WriteToLog;
1654
1655 END CTO_COST_ROLLUP_CONC_PK;
1656