[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.5 2011/12/26 13:37:30 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
54 |
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.
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
277 sql_stmt := 'select distinct msi.inventory_item_id, ' ||
274 msi.creation_date > ( trunc(sysdate) - p_num_of_days ))
275 ORDER BY 1; */
276 --Bugfix 5907413: Adding Dynamic SQL.
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 --Bugfix 12957444
290 ' and msi.costing_enabled_flag = ''Y'' ';
291
292 IF p_model_id is not null THEN
293 sql_stmt := sql_stmt || ' and msi.base_item_id = :p_model_id ';
294 flag := flag + 1;
295 END IF;
296
297 IF p_config_item_id is not null THEN
298 sql_stmt := sql_stmt || ' and msi.inventory_item_id = :p_config_item_id ';
299 flag := flag + 2;
300 END IF;
301
302 IF p_org_id is not null THEN
303 sql_stmt := sql_stmt || ' and msi.organization_id = :p_org_id ';
304 flag := flag + 4;
305 END IF;
306
307 IF p_num_of_days is not null THEN
308 sql_stmt := sql_stmt || ' and msi.creation_date > ( trunc(sysdate) - :p_num_of_days ) ';
309 flag := flag + 8;
310 END IF;
311
312 sql_stmt := sql_stmt || ' ORDER BY 1 ';
313
314 WriteToLog('SQL: ' || substr(sql_stmt,1, 1500));
315 WriteToLog(substr(sql_stmt,1501,3000));
316 WriteToLog(substr(sql_stmt,3001,4500));
317 WriteToLog(substr(sql_stmt,4501,5000));
318 WriteToLog('flag = '||flag );
319
320 CASE flag
321
322 WHEN 0 then --No (optional) parameter is passed
323 OPEN config_cv FOR sql_stmt;
324
325 WHEN 1 then --Only Model_Id is passed
326 OPEN config_cv FOR sql_stmt USING p_model_id;
327
328 WHEN 2 then --Only Config_item_id is passed
329 OPEN config_cv FOR sql_stmt USING p_config_item_id;
330
331 WHEN 3 then --Model_id and config_item_id is passed
332 OPEN config_cv FOR sql_stmt USING p_model_id, p_config_item_id;
333
334 WHEN 4 then --Only organization_id is passed
335 OPEN config_cv FOR sql_stmt USING p_org_id;
336
337 WHEN 5 then --model_id and organization_id are passed
338 OPEN config_cv FOR sql_stmt USING p_model_id, p_org_id;
339
340 WHEN 6 then --config_item_id and organization_id are passed
341 OPEN config_cv FOR sql_stmt USING p_config_item_id, p_org_id;
342
343 WHEN 7 then --model_id, config_item_id and org_id are passed
344 OPEN config_cv FOR sql_stmt USING p_model_id, p_config_item_id, p_org_id;
345
346 WHEN 8 then --Only num_of_days is passed
347 OPEN config_cv FOR sql_stmt USING p_num_of_days;
348
349 WHEN 9 then --model_id and num_of_days are passed
350 OPEN config_cv FOR sql_stmt USING p_model_id, p_num_of_days;
351
352 WHEN 10 then --config_item_id and num_of_days are passed
353 OPEN config_cv FOR sql_stmt USING p_config_item_id, p_num_of_days;
354
355 WHEN 11 then --model_id, config_item_id and num_of_days are passed
356 OPEN config_cv FOR sql_stmt USING p_model_id, p_config_item_id, p_num_of_days;
357
358 WHEN 12 then --org_id and num_of_days are passed
359 OPEN config_cv FOR sql_stmt USING p_org_id, p_num_of_days;
360
361 WHEN 13 then --model_id, org_id and num_of_days are passed
362 OPEN config_cv FOR sql_stmt USING p_model_id, p_org_id, p_num_of_days;
363
364 WHEN 14 then --config_item_id, org_id and num_of_days are passed
365 OPEN config_cv FOR sql_stmt USING p_config_item_id, p_org_id, p_num_of_days;
366
367 WHEN 15 then --model_id, config_item_id, org_id and num_of_days (All) are passed
368 OPEN config_cv FOR sql_stmt USING p_model_id, p_config_item_id, p_org_id, p_num_of_days;
369
370 END CASE;
371 --Bugfix 5907413: End of Dynamic SQL
372
373 elsif p_upgrade= '1' then
374
375 if p_calc_costrollup = '2' then
376 WriteToLog(' Not doing cost rollup since preform cost rollup parameter is NO', 2);
377 return;
378 else
379 OPEN config_cv FOR
380 select distinct msi.inventory_item_id,
381 nvl( msi_b.config_orgs , '1')
385 where bcol_upg.line_id = bcol_upg.ato_line_id /* bugfix 3777922 */
382 from bom_cto_order_lines_upg bcol_upg,
383 mtl_system_items msi,
384 mtl_system_items msi_b
386 and bcol_upg.config_item_id = msi.inventory_item_id
387 and msi.base_item_id is not null
388 and msi.base_item_id = msi_b.inventory_item_id
389 and msi.organization_id = msi_b.organization_id
390 and bcol_upg.inventory_item_id = msi_b.inventory_item_id
391 and msi.inventory_item_status_code <>
392 ( select bom_delete_status_code
393 from bom_parameters bp
394 where bp.organization_id = msi.organization_id
395 )
396 ORDER BY 1;
397 end if;
398
399 end if;
400
401
402
403 << beginloop>>
404
405 LOOP
406
407 l_stat_num := 30;
408
409 SAVEPOINT CTOCCR;
410
411 FETCH config_cv into l_config_item,l_config_orgs;
412 EXIT when config_cv%notfound;
413
414
415 WriteToLog('-------------------------------------------------------------------');
416 WriteToLog('Processing Config Id : '||l_config_item||' Attrib: '||l_config_orgs);
417 WriteToLog('-------------------------------------------------------------------');
418
419
420 delete from bom_cto_src_orgs_gt ;
421
422
423 WriteToLog('deleted from bom_cto_src_orgs_gt : '|| sql%rowcount );
424 /*
425
426
427
428 -- check if l_config_item is in array already. Then we dont need to process this item again
429
430 if cfg_item_arr_cum.count > 0 then
431 -- check if config id exist
432 -- if exist then goto beginloop;
433 for l in cfg_item_arr_cum.FIRST .. cfg_item_arr_cum.LAST
434 loop
435 if cfg_item_arr_cum(l).cfg_item_id = l_config_item then
436 WriteToLog('Config Id : '||l_config_item||' already processed. ');
437 goto beginloop;
438 end if;
439 end loop;
440 end if;
441
442
443 */
444
445
446
447 -- Ideally, we should get the sourcing org for the parent config and populate
448 -- the array. But, we will defer it because, we need to get the child configs for this parent.
449 -- If get_config_details errors out for any reason, we should not populate the parent config
450 -- details in the array.
451
452
453 --
454 -- We will first try to get the child configs for the parent configuration
455 --
456
457 WriteToLog ('calling cto_transfer_price_pk.get_config_details ');
458
459 cto_transfer_price_pk.get_config_details(
460 p_item_id => l_config_item
461 , p_mode_id => 3 -- 'BOTH'
462 , p_configs_only => 'Y'
463 , x_group_id => l_group_id
464 , x_msg_count => l_msg_count
465 , x_msg_data => l_msg_data
466 , x_return_status => l_return_status ) ;
467
468 if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
469 WriteToLog('get_config_details: ' || 'get_config_details returned unexp error.');
470 ROLLBACK TO CTOCCR;
471 raise FND_API.G_EXC_UNEXPECTED_ERROR;
472
473 elsif (l_return_status = FND_API.G_RET_STS_ERROR) then
474 retcode := 1; -- Set this for conc request to end in WARNING
475 WriteToLog('get_config_details: ' || 'get_config_details returned expected error.');
476 ROLLBACK TO CTOCCR;
477 goto beginloop;
478
479 end if;
480
481 WriteToLog('get_config_details: ' || 'Success in get_config_details ', 5);
482 WriteToLog('get_config_details: ' || 'Group Id : '|| l_group_id, 5);
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 -- now, populate the parent config details in the array
489
490 -- if l_config_orgs = 3 ,
491 -- cost rollup in all org for parents
492 -- get all_orgs where l_config_item exist
493
494 WriteToLog ('Going to populate details for parent config..');
495
496 if ( l_config_orgs = '3' )
497 OR
498 ( l_config_orgs in ('1' , '2') AND p_org_id is null )
499 OR
500 (p_upgrade = '1' ) -- bugfix 3777922
501 then
502 open cfg_org_cur (l_config_item);
503 LOOP
504 WriteToLog ('Inside cfg_org_cur loop.. ' );
505
506 fetch cfg_org_cur into l_org_id;
507 EXIT WHEN cfg_org_cur%NOTFOUND;
508 -- populate cfg_item_arr with parent config details
509
510
514 if cfg_item_arr.count > 0 then
511
512 l_record_exists := FALSE ;
513
515 -- check if config id exist
516 -- if exist then goto beginloop;
517 for l in cfg_item_arr.FIRST .. cfg_item_arr.LAST
518 loop
519 if cfg_item_arr(l).cfg_item_id = l_config_item AND
520 cfg_item_arr(l).cfg_org_id = l_org_id then
521
522 WriteToLog('Config Id : '||l_config_item|| ' org id ' || l_org_id
523 || ' already processed. ');
524
525 l_record_exists := TRUE ;
526 exit ;
527 end if;
528 end loop;
529 end if;
530
531 if( l_record_exists = FALSE ) then
532 cfg_item_arr(i).cfg_item_id := l_config_item;
533 cfg_item_arr(i).cfg_org_id := l_org_id;
534 WriteToLog('Index: ('||i||') -> Config item id: '||cfg_item_arr(i).cfg_item_id||
535 ' Org Id: '||cfg_item_arr(i).cfg_org_id , 5);
536
537 i := i + 1;
538 end if;
539 END LOOP;
540
541 close cfg_org_cur;
542
543 elsif ( l_config_orgs in ('1' , '2') AND p_org_id is not null AND p_upgrade = '2') then -- bugfix 3777922
544
545 --
546 -- get sourcing orgs for p_org_id and l_config_item /* sajani */
547 -- load cfg_item_arr with p_org_id + sourcing orgs for l_config_item
548 --
549
550 --
551 -- following proc populates temp table bcso_gt
552 -- with config item id and relevant sourcing org
553 --
554
555 WriteToLog ('Calling get_sourcing org..');
556
557 get_sourcing_org (p_config_item_id => l_config_item ,
558 p_organization_id => p_org_id,
559 p_return_status => l_return_status );
560
561 WriteToLog ('** l_return_status = '|| l_return_status );
562
563 if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
564 WriteToLog('get_sourcing_org: ' || 'raised Unexpected error.');
565 ROLLBACK TO CTOCCR;
566 raise FND_API.G_EXC_UNEXPECTED_ERROR;
567
568 elsif (l_return_status = FND_API.G_RET_STS_ERROR) then
569 retcode := 1; -- Set this for conc request to end in WARNING
570 WriteToLog('get_sourcing_org: ' || 'raised Expected error.');
571 ROLLBACK TO CTOCCR;
572 goto beginloop;
573 else
574 WriteToLog('get_sourcing_org: SUCCESSFULLL !!!! ');
575
576
577 end if;
578
579
580 WriteToLog('get_sourcing_org: Adding dummy record for ship org. ');
581
582 insert into bom_cto_src_orgs_gt
583 (
584 config_item_id,
585 organization_id,
586 rcv_org_id,
587 creation_date,
588 created_by,
589 last_update_date,
590 last_updated_by,
591 last_update_login,
592 program_application_id,
593 program_id,
594 program_update_date
595 )
596 select
597 l_config_item,
598 p_org_id,
599 null , /* this is intentionally null to indicate origin 100%transfer from org */
600 sysdate, -- creation_date
601 gUserId, -- created_by
602 sysdate, -- last_update_date
603 gUserId, -- last_updated_by
604 gLoginId, -- last_update_login
605 null, -- program_application_id,??
606 null, -- program_id,??
607 sysdate -- program_update_date
608 from dual
609 where NOT EXISTS
610 (select NULL
611 from bom_cto_src_orgs_gt
612 where rcv_org_id = p_org_id
613 and organization_id = p_org_id
614 and config_item_id = l_config_item );
615
616
617 --
618 -- query the bom_cto_src_orgs_gt table and get all sourcing orgs for parent config.
619 --
620 open cfg_src_org_cur (l_config_item); -- bugfix 3777622
621 LOOP
622 WriteToLog ('inside cfg_src_org_cur loop for parent.');
623
624 fetch cfg_src_org_cur into l_org_id;
625 EXIT WHEN cfg_src_org_cur%NOTFOUND;
626
627 --
628 -- populate cfg_item_arr with parent config and its sourcing orgs
632
629 --
630
631
633 l_record_exists := FALSE ;
634
635 if cfg_item_arr.count > 0 then
636 -- check if config id exist
637 -- if exist then goto beginloop;
638 for l in cfg_item_arr.FIRST .. cfg_item_arr.LAST
639 loop
640 if cfg_item_arr(l).cfg_item_id = l_config_item AND
641 cfg_item_arr(l).cfg_org_id = l_org_id then
642
643 WriteToLog('Config Id : '||l_config_item|| ' org id ' || l_org_id
644 || ' already processed. ');
645
646 l_record_exists := TRUE ;
647 exit ;
648
649 end if;
650 end loop;
651 end if;
652
653 if( l_record_exists = FALSE ) then
654 cfg_item_arr(i).cfg_item_id := l_config_item;
655 cfg_item_arr(i).cfg_org_id := l_org_id;
656 WriteToLog('Index: ('||i||') -> Config item id: '||cfg_item_arr(i).cfg_item_id||
657 ' Org Id: '||cfg_item_arr(i).cfg_org_id);
658
659 i := i + 1;
660
661 end if;
662
663 END LOOP;
664
665 close cfg_src_org_cur;
666
667 end if; /* l_config_orgs check */
668
669 --
670 -- At this point structure is loaded with parent configs, child configs and relevant orgs
671 --
672 --
673 -- Now, get all child for this grp id
674 --
675
676 l_stat_num := 31;
677
678 open child_config_cur(l_group_id) ;
679 loop
680 WriteToLog ('inside child_config_cur loop..' );
681 fetch child_config_cur into l_child_config_item, l_child_config_orgs, l_plan_level;
682
683 exit when child_config_cur%NOTFOUND;
684
685 WriteToLog( ' fetched ' || l_child_config_item || ' Orgs ' || l_child_config_orgs
686 || ' p_upgrade ' || p_upgrade , 5 ) ;
687
688 if ( l_child_config_orgs = '3' )
689 OR
690 ( l_child_config_orgs in ('1' , '2') AND p_org_id is null )
691 OR
692 (p_upgrade = '1') -- bugfix 3777922
693 then
694 l_stat_num := 32;
695 WriteToLog ('inside child condition 1 .. ');
696
697 open cfg_org_cur ( l_child_config_item);
698
699 LOOP
700
701 WriteToLog ('inside cfg_org_cur loop.. ');
702 fetch cfg_org_cur into l_child_org_id;
703
704 EXIT WHEN cfg_org_cur%NOTFOUND;
705
706
707
708
709 -- check if l_config_item is in array already. Then we dont need to process this item again
710
711 l_record_exists := FALSE ;
712
713 if cfg_item_arr.count > 0 then
714 -- check if config id exist
715 -- if exist then goto beginloop;
716 for l in cfg_item_arr.FIRST .. cfg_item_arr.LAST
717 loop
718 if cfg_item_arr(l).cfg_item_id = l_child_config_item AND
719 cfg_item_arr(l).cfg_org_id = l_child_org_id then
720
721 WriteToLog('Config Id : '|| l_child_config_item|| ' org id ' || l_child_org_id
722 || ' already processed. ');
723
724 l_record_exists := TRUE ;
725 exit ;
726
727 end if;
728 end loop;
729 end if;
730
731
732 if( l_record_exists = FALSE ) then
733 --
734 -- populate cfg_item_arr with child config details
735 --
736
737 cfg_item_arr(i).cfg_item_id := l_child_config_item;
738 cfg_item_arr(i).cfg_org_id := l_child_org_id;
739 WriteToLog('Index: ('||i||') -> Child Config item id: '||cfg_item_arr(i).cfg_item_id||
740 ' Org Id: '||cfg_item_arr(i).cfg_org_id, 5);
741
742 i := i + 1;
743
744 end if;
745
746
747 END LOOP;
748
749 close cfg_org_cur;
750
751 elsif ( l_child_config_orgs in ('1' , '2') AND p_org_id is not null AND p_upgrade = '2' ) then -- bugfix 3777922
752
753 WriteToLog ('inside child condition 2 .. ');
754 -- get sourcing orgs for p_org_id and child_config_orgs
755 -- load cfg_item_arr with p_org_id + sourcing orgs for child_config_orgs
756
757 -- following proc populates temp table bcso_gt
761
758 -- with config item id and relevant sourcing org
759
760
762 open cfg_mfg_org_cur (l_config_item); -- bugfix 3777622
763 LOOP
764 WriteToLog ('inside cfg_src_org_cur loop for parent.');
765
766 fetch cfg_mfg_org_cur into l_parent_org_id;
767 EXIT WHEN cfg_mfg_org_cur%NOTFOUND;
768
769
770 WriteToLog ('calling get_sourcing_org..' || l_child_config_item
771 || ' org ' || l_parent_org_id);
772
773 get_sourcing_org (p_config_item_id => l_child_config_item ,
774 p_organization_id => l_parent_org_id ,
775 p_return_status => l_return_status );
776
777 if (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
778 WriteToLog('get_sourcing_org: ' || 'raised Unexpected error.');
779 ROLLBACK TO CTOCCR;
780 raise FND_API.G_EXC_UNEXPECTED_ERROR;
781 elsif (l_return_status = FND_API.G_RET_STS_ERROR) then
782 retcode := 1; -- Set this for conc request to end in WARNING
783 WriteToLog('get_sourcing_org: ' || 'raised Expected error.');
784 ROLLBACK TO CTOCCR;
785 goto beginloop;
786 end if;
787
788
789 WriteToLog('get_sourcing_org: Adding dummy record for ship org. ');
790
791 insert into bom_cto_src_orgs_gt
792 (
793 config_item_id,
794 organization_id,
795 rcv_org_id,
796 creation_date,
797 created_by,
798 last_update_date,
799 last_updated_by,
800 last_update_login,
801 program_application_id,
802 program_id,
803 program_update_date
804 )
805 select
806 l_child_config_item,
807 l_parent_org_id, -- bugfix 3777922
808 null , -- bugfix 3777922 /* this is intentionally null for origin 100% transfer from org*/
809 sysdate, -- creation_date
810 gUserId, -- created_by
811 sysdate, -- last_update_date
812 gUserId, -- last_updated_by
813 gLoginId, -- last_update_login
814 null, -- program_application_id,??
815 null, -- program_id,??
816 sysdate -- program_update_date
817 from dual
818 where NOT EXISTS
819 (select NULL
820 from bom_cto_src_orgs_gt
821 where rcv_org_id = l_parent_org_id -- bugfix 3777922
822 and organization_id = l_parent_org_id -- bugfix 3777922
823 and config_item_id = l_child_config_item ) ; -- bugfix 3941383
824
825
826
827 end loop ;
828
829 close cfg_mfg_org_cur ;
830
831 --
832 -- query the bom_cto_src_orgs_gt table and get all sourcing orgs for child config.
833 --
834 /* old call open cfg_src_org_cur (l_child_config_item,p_org_id); */
835
836
837 open cfg_src_org_cur (l_child_config_item); -- bugfix 3777922
838 LOOP
839 WriteToLog ('inside cfg_src_org_cur loop for child');
840
841 fetch cfg_src_org_cur into l_org_id;
842 EXIT WHEN cfg_src_org_cur%NOTFOUND;
843
844 -- populate cfg_item_arr with parent config sourcing
845
846 l_record_exists := FALSE ;
847
848 if cfg_item_arr.count > 0 then
849 -- check if config id exist
850 -- if exist then goto beginloop;
851 for l in cfg_item_arr.FIRST .. cfg_item_arr.LAST
852 loop
853 if cfg_item_arr(l).cfg_item_id = l_child_config_item AND
854 cfg_item_arr(l).cfg_org_id = l_org_id then
855
856 WriteToLog('Config Id : '||l_child_config_item|| ' org id ' || l_org_id
857 || ' already processed. ');
858
859 l_record_exists := TRUE ;
860 exit ;
861
862 end if;
863 end loop;
864 end if;
865
866 if( l_record_exists = FALSE ) then
867 cfg_item_arr(i).cfg_item_id := l_child_config_item;
868 cfg_item_arr(i).cfg_org_id := l_org_id;
869 WriteToLog('Index: ('||i||') -> Child Config item id: '||cfg_item_arr(i).cfg_item_id||
870 ' Org Id: '||cfg_item_arr(i).cfg_org_id, 5);
871
872 i := i + 1;
873 end if;
874
875 END LOOP;
876
877 close cfg_src_org_cur;
878
879 end if; /* end of child_config_orgs check */
880
881 end loop; /* end of child_config_cur cursor loop */
882
883 close child_config_cur ;
884
885 WriteToLog(' Closed child_config_cur ' ) ;
886
887 if( cfg_item_arr.count >= 100 ) then /* Start bugfix 3784283 */
891
888 --
889 -- Passing array to the cost rollup API
890 --
892 --Bugfix 12957444: This savepoint is not needed. Rollback being done
893 --inside Cost_Roll_Up_ML.
894 --SavePoint S1;
895
896 WriteToLog('Calling CTO_CONFIG_COST_PK.Cost_Roll_Up_ML.. ', 5);
897 WriteToLog('Calling CTO_CONFIG_COST_PK.Cost_Roll_Up_ML.. ' || cfg_item_arr.count , 5);
898 WriteToLog ('==============================================');
899 WriteToLog (' Collected Data for '|| config_cv%ROWCOUNT || ' parent configs or processing .');
900 WriteToLog ('==============================================');
901
902 l_status := CTO_CONFIG_COST_PK.Cost_Roll_Up_ML(
903 p_cfg_itm_tbl => cfg_item_arr,
904 x_msg_count => l_msg_count,
905 x_msg_data => l_msg_data
906 );
907
908 l_stat_num := 231;
909
910 if (l_status = 0) then
911 WriteToLog('Cost_Roll_Up_ML failed for some configs in this batch. Return status::'||l_msg_data);
912
913 /*Commenting this as part of bugfix 12957444
914 WriteToLog('Following batch of records could not be processed successfully ..');
915
916 for curr_count in 1..cfg_item_arr.count
917 loop
918 fail_count := fail_item_arr.count + 1;
919 fail_item_arr(fail_count).cfg_item_id := cfg_item_arr(curr_count).cfg_item_id ;
920 fail_item_arr(fail_count).cfg_org_id := cfg_item_arr(curr_count).cfg_org_id ;
921
922 WriteToLog('Config Id: ' || cfg_item_arr(curr_count).cfg_item_id ||
923 ' Org Id: ' || cfg_item_arr(curr_count).cfg_org_id , 5);
924 end loop;
925
926 WriteToLog('Total records that could not be processed successfully .. ' || cfg_item_arr.count , 5);
927
928 --This rollback is not needed. The API Cost_Roll_Up_ML has been redesigned to skip a config
929 --that's in error and process remaining ones in a batch. Because of this rollback, even if
930 --one config of the batch encounters an error and the remaining are processed successfully,
931 --everything would be rolled back. Commented the savepoint S1 also.
932
933 Rollback TO S1 ;
934 */
935
936 v_error_encountered := TRUE ;
937 else
938 WriteToLog('Cost_Roll_Up_ML succeeded for all configs in this batch.');
939 /*Commenting this as part of bugfix 12957444
940 for curr_count in 1..cfg_item_arr.count
941 loop
942 succ_count := succ_item_arr.count + 1 ;
943 succ_item_arr(succ_count).cfg_item_id := cfg_item_arr(curr_count).cfg_item_id ;
944 succ_item_arr(succ_count).cfg_org_id := cfg_item_arr(curr_count).cfg_org_id ;
945 end loop;
946 */
947 end if;
948
949 --Bugfix 12957444: Getting the failed and processed configs in arrays.
950 for curr_count in 1..cfg_item_arr.count
951 loop
952 if g_error_cache.exists(cfg_item_arr(curr_count).cfg_item_id || '-' || cfg_item_arr(curr_count).cfg_org_id)
953 then
954 fail_count := fail_item_arr.count + 1;
955 fail_item_arr(fail_count).cfg_item_id := cfg_item_arr(curr_count).cfg_item_id;
956 fail_item_arr(fail_count).cfg_org_id := cfg_item_arr(curr_count).cfg_org_id;
957 else
958 succ_count := succ_item_arr.count + 1;
959 succ_item_arr(succ_count).cfg_item_id := cfg_item_arr(curr_count).cfg_item_id;
960 succ_item_arr(succ_count).cfg_org_id := cfg_item_arr(curr_count).cfg_org_id;
961 end if;
962 end loop;
963
964 /*
965 WriteToLog('Following configs of this batch could not be processed successfully..');
966 for curr_count in 1..fail_item_arr.count loop
967 WriteToLog('Config Id::' || fail_item_arr(curr_count).cfg_item_id ||
968 ' Org Id::' || fail_item_arr(curr_count).cfg_org_id);
969 end loop;
970 */
971
972 cfg_item_arr.delete ;
973
974 i := 1 ; /* fix for bug 3975083 */
975
976 end if ; /* cfg_item_arr.count >= 100 */ /* end bugfix 3784283 */
977
978 END LOOP; /* parent config loop */ /* bugfix 3777922 */
979
980
981 if config_cv%ROWCOUNT = 0 then
982 WriteToLog ('Nothing to process.');
983 return;
984 else
985 WriteToLog ('==============================================');
986 WriteToLog ('Total Processed '|| config_cv%ROWCOUNT || ' parent configs.');
987 WriteToLog ('==============================================');
988 end if;
989
990 l_stat_num := 331;
991
992 /* Process remaining records < 100 */
993 if( cfg_item_arr.count > 0 ) then -- bugfix 3784283
994
995
996 --
997 -- Passing array to the cost rollup API
998 --
999
1000 --Bugfix 12957444: This savepoint is not needed. Rollback being done
1001 --inside Cost_Roll_Up_ML.
1002 --SavePoint S1;
1003
1004 WriteToLog('Calling CTO_CONFIG_COST_PK.Cost_Roll_Up_ML.. ', 5);
1005 WriteToLog('Calling CTO_CONFIG_COST_PK.Cost_Roll_Up_ML.. ' || cfg_item_arr.count , 5);
1006
1007 WriteToLog ('==============================================');
1008 WriteToLog (' Collected Data for '|| config_cv%ROWCOUNT || ' parent configs or processing .');
1009 WriteToLog ('==============================================');
1010
1011 l_status := CTO_CONFIG_COST_PK.Cost_Roll_Up_ML(
1012 p_cfg_itm_tbl => cfg_item_arr,
1013 x_msg_count => l_msg_count,
1014 x_msg_data => l_msg_data
1015 );
1016
1017 if (l_status = 0) then
1018
1019 WriteToLog('Cost_Roll_Up_ML failed for some configs in this batch. Return status::'||l_msg_data);
1020 /*Commenting this as part of bugfix 12957444
1021 WriteToLog ('====================================================================', 1);
1022 WriteToLog('following batch of records could not be processed successfully .. ', 1);
1023 WriteToLog ('====================================================================', 1);
1024
1025 for curr_count in 1..cfg_item_arr.count
1026 loop
1027 fail_count := fail_item_arr.count + 1 ;
1028 fail_item_arr(fail_count).cfg_item_id := cfg_item_arr(curr_count).cfg_item_id ;
1029 fail_item_arr(fail_count).cfg_org_id := cfg_item_arr(curr_count).cfg_org_id ;
1030
1031 WriteToLog('Config Id: ' || cfg_item_arr(curr_count).cfg_item_id ||
1032 ' Org Id: ' || cfg_item_arr(curr_count).cfg_org_id , 1);
1033 end loop;
1034
1035 WriteToLog ('====================================================================', 1);
1036 WriteToLog('Total records for this batch that could not be processed successfully .. ' || cfg_item_arr.count , 1);
1037 WriteToLog ('====================================================================', 1);
1038
1039
1040 Rollback TO S1 ;
1041 */
1042
1043 v_error_encountered := TRUE ;
1044
1045 else
1046 WriteToLog('Cost_Roll_Up_ML succeeded for all configs in this batch.');
1047 /*Commenting this as part of bugfix 12957444
1048 for curr_count in 1..cfg_item_arr.count
1049 loop
1050 succ_count := succ_item_arr.count + 1 ;
1051 succ_item_arr(succ_count).cfg_item_id := cfg_item_arr(curr_count).cfg_item_id ;
1052 succ_item_arr(succ_count).cfg_org_id := cfg_item_arr(curr_count).cfg_org_id ;
1053 end loop;
1054 */
1055 end if;
1056
1057 --Bugfix 12957444: Getting the failed and processed configs in arrays.
1058 for curr_count in 1..cfg_item_arr.count
1059 loop
1060 if g_error_cache.exists(cfg_item_arr(curr_count).cfg_item_id || '-' || cfg_item_arr(curr_count).cfg_org_id)
1061 then
1062 fail_count := fail_item_arr.count + 1;
1063 fail_item_arr(fail_count).cfg_item_id := cfg_item_arr(curr_count).cfg_item_id;
1064 fail_item_arr(fail_count).cfg_org_id := cfg_item_arr(curr_count).cfg_org_id;
1065 else
1066 succ_count := succ_item_arr.count + 1;
1067 succ_item_arr(succ_count).cfg_item_id := cfg_item_arr(curr_count).cfg_item_id;
1068 succ_item_arr(succ_count).cfg_org_id := cfg_item_arr(curr_count).cfg_org_id;
1069 end if;
1070 end loop;
1071
1072 /*
1073 WriteToLog('Following configs of this batch could not be processed successfully..');
1074 for curr_count in 1..fail_item_arr.count loop
1075 WriteToLog('Config Id::' || fail_item_arr(curr_count).cfg_item_id ||
1076 ' Org Id::' || fail_item_arr(curr_count).cfg_org_id);
1077 end loop;
1078 */
1079
1080 end if ; /* cfg_item_arr.count > 0 */ -- bugfix 3784283
1081
1082 l_stat_num := 431;
1083
1084 WriteToLog ('====================================================================', 1);
1085 WriteToLog('following records have been processed successfully .. ', 1);
1086 WriteToLog ('====================================================================', 1);
1087 for curr_count in 1..succ_item_arr.count
1088 loop
1089
1090 l_stat_num := 441;
1091
1092 l_config_description := 'N/A' ;
1093 l_org_code := 'N/A' ;
1094
1095 l_stat_num := 451;
1096
1097 begin
1098 SELECT substrb(kfv.concatenated_segments,1,35),
1099 mp.organization_code
1100 INTO l_config_description, l_org_code
1101 FROM mtl_system_items_kfv kfv, mtl_parameters mp
1102 WHERE kfv.inventory_item_id = succ_item_arr(curr_count).cfg_item_id
1103 AND kfv.organization_id = succ_item_arr(curr_count).cfg_org_id
1104 AND kfv.organization_id = mp.organization_id;
1105
1106 exception
1107 when others then
1108
1109 null ;
1110 end ;
1111
1112
1113 l_stat_num := 461;
1114 WriteToLog('Config Id: ' || succ_item_arr(curr_count).cfg_item_id ||
1115 ' Org Id: ' || succ_item_arr(curr_count).cfg_org_id ||
1116 ' Item Name ' || l_config_description ||
1117 ' Org Code ' || l_org_code
1118 , 1);
1119
1120 end loop;
1121 WriteToLog ('====================================================================', 1);
1122 WriteToLog('Total records processed successfully .. ' || succ_item_arr.count , 1);
1123 WriteToLog ('====================================================================', 1);
1124
1125 commit ;
1126
1127
1128 if( v_error_encountered ) then
1129 WriteToLog ('====================================================================', 1);
1130 WriteToLog('following records have not been processed successfully .. ', 1);
1131 WriteToLog ('====================================================================', 1);
1132 for curr_count in 1..fail_item_arr.count
1133 loop
1134 l_config_description := 'N/A' ;
1135 l_org_code := 'N/A' ;
1136
1137 begin
1138 SELECT substrb(kfv.concatenated_segments,1,35),
1139 mp.organization_code
1140 INTO l_config_description, l_org_code
1141 FROM mtl_system_items_kfv kfv, mtl_parameters mp
1142 WHERE kfv.inventory_item_id = fail_item_arr(curr_count).cfg_item_id
1143 AND kfv.organization_id = fail_item_arr(curr_count).cfg_org_id
1144 AND kfv.organization_id = mp.organization_id;
1145
1146 exception
1147 when others then
1148 null ;
1149 end ;
1150
1151
1152 WriteToLog('Config Id: ' || fail_item_arr(curr_count).cfg_item_id ||
1153 ' Org Id: ' || fail_item_arr(curr_count).cfg_org_id ||
1154 ' Item Name ' || l_config_description ||
1155 ' Org Code ' || l_org_code
1156 , 1);
1157
1158 end loop;
1159 WriteToLog ('====================================================================', 1);
1160 WriteToLog('Total records not been processed successfully .. ' || fail_item_arr.count , 1);
1161 WriteToLog ('====================================================================', 1);
1162 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1163 end if;
1164
1165 if config_cv%ISOPEN then
1166 CLOSE config_cv;
1167 end if;
1168
1169 EXCEPTION
1170 when FND_API.G_EXC_ERROR then
1171 WriteToLog('cto_cost_rollup: ' || 'EXPECTED ERROR:' || to_char(l_stat_num),1);
1172 retcode := 1;--completes with warning status -- Bug Fix 5527848
1173 l_return_status := FND_API.G_RET_STS_ERROR;
1174
1175
1176 when FND_API.G_EXC_UNEXPECTED_ERROR then
1177 WriteToLog('cto_cost_rollup: ' || 'UNEXPECTED ERROR:' || to_char(l_stat_num),1);
1178 retcode := 1;--completes with warning status --Bug Fix 5527848
1179 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1180
1181
1182 when OTHERS then
1183 WriteToLog('OTHERS excpn in cto_cost_rollup: '||to_char(l_stat_num)||'::'||sqlerrm);
1184 errbuf := 'Completed with error';
1185 retcode := 2;--completes with error status
1186 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1187
1188 END cto_cost_rollup;
1189
1190 PROCEDURE get_sourcing_org (
1191 p_config_item_id number
1192 , p_organization_id number
1193 , p_return_status OUT NOCOPY varchar2 )
1194 is
1195
1196 v_t_sourcing_info CTO_MSUTIL_PUB.sourcing_info;
1197 v_buy_traversed boolean := false ;
1198 v_source_type mrp_sources_v.source_type%type ;
1199 l_make_buy_code mtl_system_items.planning_make_buy_code%type ;
1200
1201 l_curr_src_org mrp_sources_v.source_organization_id%type ;
1202 l_source_type mrp_sources_v.source_type%type ;
1206
1203 l_curr_assg_type mrp_sources_v.assignment_type%type ;
1204 l_curr_rank mrp_sources_v.rank%type ;
1205 v_sourcing_rule_exists varchar2(10) ;
1207
1208 x_exp_error_code NUMBER ;
1209 x_return_status varchar2(100);
1210
1211 lStmtNum number ;
1212 x_msg_data varchar2(250) ;
1213 x_msg_count number ;
1214 v_bcso_count number ;
1215 l_circular_src varchar2(1);
1216 BEGIN
1217
1218 WriteToLog( 'Entered get_sourcing_org to find sourcing chain with item id: ' || p_config_item_id ||
1219 ' and orgn_id: ' || p_organization_id) ;
1220
1221 lStmtNum := 0 ;
1222
1223 p_return_status := FND_API.G_RET_STS_SUCCESS;
1224
1225 v_buy_traversed := FALSE ;
1226
1227 WriteToLog( 'calling query sourcing org ') ;
1228
1229 CTO_MSUTIL_PUB.query_sourcing_org_ms(
1230 p_inventory_item_id => p_config_item_id
1231 ,p_organization_id => p_organization_id
1232 ,p_sourcing_rule_exists => v_sourcing_rule_exists
1233 ,p_source_type => v_source_type
1234 ,p_t_sourcing_info => v_t_sourcing_info
1235 ,x_exp_error_code => x_exp_error_code
1236 ,x_return_status => x_return_status );
1237
1238 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1239
1240 WriteToLog(' Error in query_sourcing_org_ms.. ');
1241 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1242
1243 END IF;
1244
1245
1246 WriteToLog( 'output query sourcing org rule ' || v_t_sourcing_info.sourcing_rule_id.count) ;
1247 WriteToLog( 'output query sourcing org src org ' || v_t_sourcing_info.source_organization_id.count) ;
1248 WriteToLog( 'output query sourcing org src type' || v_t_sourcing_info.source_type.count) ;
1249
1250
1251
1252 if( v_t_sourcing_info.source_type.count > 0 ) then
1253
1254 FOR i in 1..v_t_sourcing_info.source_type.count
1255 LOOP
1256
1257 WriteToLog( 'output query sourcing org type ' || v_t_sourcing_info.source_type(i)) ;
1258
1259 if( v_t_sourcing_info.source_type(i) in ( 1, 2 ) ) then
1260 /* 1 = Transfer From, 2 = Make At */
1261
1262 WriteToLog( ' came into type 1,2 ') ;
1263
1264 begin
1265 lStmtNum := 1 ;
1266 l_curr_src_org := v_t_sourcing_info.source_organization_id(i) ;
1267
1268 lStmtNum := 2 ;
1269 l_source_type := v_t_sourcing_info.source_type(i) ;
1270
1271 lStmtNum := 3 ;
1272 l_curr_assg_type := v_t_sourcing_info.assignment_type(i) ;
1273
1274 lStmtNum := 4 ;
1275 l_curr_rank := v_t_sourcing_info.rank(i) ;
1276
1277 exception
1278
1279 when others then
1280
1281 WriteToLog( ' errored into type 1,2 at ' || lStmtNum || ' err ' || SQLERRM ) ;
1282 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1283
1284 end ;
1285
1286
1287 if( l_source_type = 1) then
1288 WriteToLog ( ' Check Circular Sourcing ..');
1289
1290 lStmtNum := 8;
1291
1292 begin
1293
1294 select distinct 'Y'
1295 into l_circular_src
1296 from bom_cto_src_orgs_gt
1297 where config_item_id = p_config_item_id
1298 and rcv_org_id = l_curr_src_org;
1299
1300 exception
1301 when no_data_found then
1302 l_circular_src := 'N';
1303 end;
1304
1305 lStmtNum := 9;
1306 IF l_circular_src = 'Y' THEN
1307 WriteToLog( ' Circular Sourcing detected ..');
1308 raise FND_API.G_EXC_ERROR;
1309 END IF;
1310
1311
1312 end if;
1313
1314
1315 WriteToLog( 'going to insert bcso for type 1,2 ') ;
1316
1317 lStmtNum := 10 ;
1318
1319 begin
1320
1321
1322 insert into bom_cto_src_orgs_gt
1323 (
1324 config_item_id,
1325 organization_id,
1326 rcv_org_id,
1327 creation_date,
1328 created_by,
1329 last_update_date,
1330 last_updated_by,
1331 last_update_login,
1332 program_application_id,
1333 program_id,
1334 program_update_date
1335 )
1336 select
1337 p_config_item_id,
1338 l_curr_src_org,
1339 p_organization_id,
1340 sysdate, -- creation_date
1341 gUserId, -- created_by
1342 sysdate, -- last_update_date
1343 gUserId, -- last_updated_by
1344 gLoginId, -- last_update_login
1345 null, -- program_application_id,??
1346 null, -- program_id,??
1347 sysdate -- program_update_date
1348 from dual
1349 where NOT EXISTS
1350 (select NULL
1351 from bom_cto_src_orgs_gt
1352 where rcv_org_id = p_organization_id
1353 and organization_id = l_curr_src_org
1354 and config_item_id = p_config_item_id );
1355
1356 exception
1357
1361 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1358 when others then
1359
1360 WriteToLog( ' errored inserting at ' || lStmtNum || ' err ' || SQLERRM ) ;
1362
1363 end ;
1364
1365
1366 WriteToLog( 'inserted' || sql%rowcount || 'records in bcso for type 1,2.') ;
1367 WriteToLog( 'inserted bcso for type 1,2 rcv_org = ' || p_organization_id || ', src_org = ' || l_curr_src_org) ;
1368
1369
1370 elsif( v_t_sourcing_info.source_type(i) = 3 and NOT v_buy_traversed ) then
1371 /* 3 = Buy From */
1372
1373 v_buy_traversed := TRUE ;
1374
1375 WriteToLog( ' came into type 3 ' , 1 ) ;
1376
1377 begin
1378 lStmtNum := 21 ;
1379 l_curr_src_org := nvl( v_t_sourcing_info.source_organization_id(i) , p_organization_id ) ;
1380 lStmtNum := 22 ;
1381 l_source_type := v_t_sourcing_info.source_type(i) ;
1382
1383 lStmtNum := 23 ;
1384 l_curr_assg_type := v_t_sourcing_info.assignment_type(i) ;
1385
1386 lStmtNum := 24 ;
1387 l_curr_rank := v_t_sourcing_info.rank(i) ;
1388
1389 exception
1390
1391 when others then
1392
1393 WriteToLog( ' errored into type 3 at ' || lStmtNum || ' err ' || SQLERRM) ;
1394 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1395
1396 end ;
1397
1398
1399 lStmtNum := 30 ;
1400
1401
1402 begin
1403
1404
1405 insert into bom_cto_src_orgs_gt
1406 (
1407 config_item_id,
1408 organization_id,
1409 rcv_org_id,
1410 creation_date,
1411 created_by,
1412 last_update_date,
1413 last_updated_by,
1414 last_update_login,
1415 program_application_id,
1416 program_id,
1417 program_update_date
1418 )
1419 select
1420 p_config_item_id,
1421 l_curr_src_org,
1422 p_organization_id,
1423 sysdate, -- creation_date
1424 gUserId, -- created_by
1425 sysdate, -- last_update_date
1426 gUserId, -- last_updated_by
1427 gLoginId, -- last_update_login
1428 null, -- program_application_id,??
1429 null, -- program_id,??
1430 sysdate -- program_update_date
1431 from dual
1432 where NOT EXISTS
1433 (select NULL
1434 from bom_cto_src_orgs_gt
1435 where rcv_org_id = p_organization_id
1436 and organization_id = l_curr_src_org
1437 and config_item_id = p_config_item_id );
1438
1439 exception
1440
1441 when others then
1442
1443 WriteToLog( ' errored inserting at ' || lStmtNum || ' err ' || SQLERRM ) ;
1444 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1445
1446 end ;
1447
1448
1449
1450 WriteToLog( 'inserted' || sql%rowcount || 'records in bcso for type 3.') ;
1451 WriteToLog( 'inserted bcso for type 3 rcv_org = ' || p_organization_id || ', src_org = ' || l_curr_src_org) ;
1452
1453
1454 end if;
1455
1456
1457 lStmtNum := 40 ;
1458 if( v_t_sourcing_info.source_type(i) = 1 ) then
1459
1460 WriteToLog( 'calling process sourcing chain recursive ') ;
1461
1462 lStmtNum := 50 ;
1463
1464 get_sourcing_org( p_config_item_id
1465 , v_t_sourcing_info.source_organization_id(i)
1466 , x_return_status
1467 );
1468 if (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) then
1469 WriteToLog('get_sourcing_org: ' || 'raised Unexpected error.');
1470 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1471
1472 elsif (x_return_status = FND_API.G_RET_STS_ERROR) then
1473 WriteToLog('get_sourcing_org: ' || 'raised Expected error.');
1474 raise FND_API.G_EXC_ERROR;
1475
1476 end if;
1477
1478 end if;
1479
1480
1481 END LOOP ;
1482
1483
1484 else
1485
1486 -- When there is no sourcing rule defined we need to check for the make_buy_type of the
1487 -- item to determine the buy model
1488
1489
1490 WriteToLog('get_sourcing_org : ' || 'No sourcing rule defined..');
1491
1492 lStmtNum := 57;
1493
1494 -- When the item is not defined in the sourcing org it needs to be
1495 -- treated as INVALID sourcing
1496
1497 BEGIN
1498
1499 SELECT planning_make_buy_code
1500 INTO l_make_buy_code
1501 FROM MTL_SYSTEM_ITEMS
1502 WHERE inventory_item_id = p_config_item_id
1503 AND organization_id = p_organization_id ;
1504
1505 EXCEPTION
1506 WHEN NO_DATA_FOUND THEN
1507
1508 WriteToLog('get_sourcing_org: ' || 'ERROR::The item is not defined in the sourcing org');
1509 -- The following message handling is modified by Renga Kannan
1510 -- We need to give the add for once to FND function and other
1511 -- to OE, in both cases we need to set the message again
1512 -- This is because if we not set the token once again the
1513 -- second add will not get the message.
1514
1515 cto_msg_pub.cto_message('BOM','CTO_INVALID_SOURCING');
1516 raise FND_API.G_EXC_ERROR;
1517
1518 END;
1519
1520 l_curr_src_org := p_organization_id ;
1521
1522 if( l_make_buy_code = 2) then
1523
1524 l_source_type := 3 ;
1525 else
1526 l_source_type := 2 ;
1527 end if;
1528
1529 l_curr_rank := null ;
1530
1531
1532 begin
1533
1534 insert into bom_cto_src_orgs_gt
1535 (
1536 config_item_id,
1537 rcv_org_id,
1538 organization_id,
1539 creation_date,
1540 created_by,
1541 last_update_date,
1542 last_updated_by,
1543 last_update_login,
1544 program_application_id,
1545 program_id,
1546 program_update_date
1547 )
1548 select p_config_item_id ,
1549 p_organization_id, -- will work for end of chain source or no source
1550 p_organization_id,
1551 sysdate, -- creation_date
1552 gUserId, -- created_by
1553 sysdate, -- last_update_date
1554 gUserId, -- last_updated_by
1555 gLoginId, -- last_update_login
1556 null, -- program_application_id,??
1557 null, -- program_id,??
1558 sysdate -- program_update_date
1559 from dual
1560 where NOT EXISTS
1561 (select NULL
1562 from bom_cto_src_orgs_gt
1563 where rcv_org_id = p_organization_id
1564 and organization_id =p_organization_id
1565 and config_item_id = p_config_item_id );
1566
1567 exception
1568
1569 when others then
1570
1571 WriteToLog( ' errored inserting at ' || lStmtNum || ' err ' || SQLERRM ) ;
1572 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1573
1574 end ;
1575
1576
1577 WriteToLog( 'inserted bcso for end of chain ' || SQL%rowcount) ;
1578
1579 end if;
1580
1581 EXCEPTION
1582 when FND_API.G_EXC_ERROR then
1583 WriteToLog('get_sourcing_org: ' || 'EXPECTED ERROR:' || to_char(lStmtNum),1);
1584 p_return_status := FND_API.G_RET_STS_ERROR;
1585
1586
1587 when FND_API.G_EXC_UNEXPECTED_ERROR then
1588 WriteToLog('get_sourcing_org: ' || 'UNEXPECTED ERROR:' || to_char(lStmtNum),1);
1589 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1590
1591
1592 when OTHERS then
1593 WriteToLog('OTHERS excpn in get_sourcing_org: '||to_char(lStmtNum)||'::'||sqlerrm);
1594 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1595
1596
1597 END get_sourcing_org;
1598
1599
1600 PROCEDURE WriteToLog (p_message in varchar2 default null,
1601 p_level in number default 0) is
1602 begin
1603 if gDebugLevel >= p_level then
1604 /* fnd_file.put_line (fnd_file.log, p_message); */
1605 oe_debug_pub.add (p_message);
1606 end if;
1607 end WriteToLog;
1608
1609 END CTO_COST_ROLLUP_CONC_PK;
1610