[Home] [Help]
PACKAGE BODY: APPS.CTO_UPDATE_CONFIGS_PK
Source
1 package body CTO_UPDATE_CONFIGS_PK as
2 /* $Header: CTOUCFGB.pls 120.6.12010000.4 2008/09/10 17:54:22 appldev ship $*/
3 /*----------------------------------------------------------------------------+
4 | Copyright (c) 1993 Oracle Corporation Belmont, California, USA
5 | All rights reserved.
6 | Oracle Manufacturing
7 |
8 |FILE NAME : CTOUCFGB.pls
9 |
10 |DESCRIPTION : Contains modules to :
11 |
12 |HISTORY : Created on 9-SEP-2003 by Sajani Sheth
13 |
14 |
15 | 01/13/2004 Kiran Konada
16 | bugfix 3368052
17 |
18 | 01/13/2004 Kiran Konada
19 | bugfix 3371155
20 | This is done not to look at configs present in bcol
21 | for de-linked orders as they dont belong to open
22 | order Lines
23 |
24 | 01/20/2004 Kiran Konada
25 |
26 | bugfix 3377963
27 | MOdel with cib attributes 1 or 2 present as child
28 | under a model with CIB attribute 3 is a invalid setup
29 | 01/23/04 Renga Kannan
30 | Added the implementation to update the atp attributes for the
31 | existing configs
32 |
33 | 01/27/04 Kiran Konada
34 | bugfix 3397123
35 | Changed the signature of Update_Configs
36 | To take in new parameters
37 | p_category_set_id
38 | p_dummy3
39 | The above two parameters are NOt used in the code,
40 | they had to be in teh signature as they are in Conc
41 | program definition
42 |
43 | Modified : 02-MAR-2004 Sushant Sawant
44 | Fixed Bug 3472654
45 | upgrades for matched config from CIB = 1 or 2 to 3 were not performed properly.
46 | data was not transformed to bcmo.
47 | perform_match is now inherited from bcol while populating bcol_upg
48 |
49 | Modified : 16-MAR-2004 Sushant Sawant
50 | Fixed Bug 3567693
51 | upgrades for matched config from CIB = 1 or 2 to 3 with no orders pointing
52 | to the config item were not performed properly.
53
54 |
55 | Modified : 29-APR-2004 Sushant Sawant
56 | Fixed Bug 3599397. Added check for config linked to oe in populate_cat_models code.
57 |
58 |
59 | Modified : 03-MAY-2004 Sushant Sawant
60 | Fixed Bug 3602292. Defaulted option_specific to N in bcol_upg
61 |
62 | Modified : 18-AUG-2004 Kiran Konada
63 | bugfix #3841575
64 |
65 | Modified : 20-AUG-2004 Kiran Konada
66 | bugfix # 3845686
67 | 1.moved the EXIT to be immediately after fetch
68 | 2.got the config_orgs attribute for BASE_MODEL_ID
69 | 3.added nvl and to_char to select column
70 | nvl(to_char(msi.option_specific_sourced),'N')
71 |
72 +-----------------------------------------------------------------------------*/
73
74 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CTO_UPDATE_CONFIGS_PK';
75 PG_DEBUG Number := NVL(FND_PROFILE.value('ONT_DEBUG_LEVEL'), 0);
76
77 --forward declaration
78 PROCEDURE Check_invalid_configurations(
79 x_return_status out NOCOPY varchar2);
80
81 /***********************************************************************
82 This procedure is called by the Update Existing Configurations batch
83 progam. It does the following:
84 1. Call procedures to populate bcol_upg based on the input params
85 2. Delete sourcing for canned configs not to be processed
86 3. Update bcol_upg with sequence numbers for batch processing
87 4. Call procedure to update items and sourcing
88 ***********************************************************************/
89 PROCEDURE Update_Configs
90 (
91 errbuf OUT NOCOPY varchar2,
92 retcode OUT NOCOPY varchar2,
93 p_item IN number,
94 p_dummy IN varchar2,
95 p_dummy2 IN varchar2,
96 p_category_set_id IN number, --bugfix3397123
97 p_dummy3 IN number, --bugfix3397123
98 p_cat_id IN number,
99 p_config_id IN number,
100 p_changed_src IN varchar2,
101 p_open_lines IN varchar2,
102 p_upgrade_mode In Number
103 ) IS
104
105
106
107 /*
108 Redundant cursor
109
110 CURSOR c_seq(l_seq number) IS
111 select distinct sequence
112 from bom_cto_order_lines_upg
113 where sequence = l_seq;
114 */
115
116 l_return_status varchar2(1);
117 l_msg_count number;
118 l_msg_data varchar2(240);
119 l_seq NUMBER := 0;
120 l_status NUMBER;
121 l_stmt_num number := 0;
122 l_req_data varchar2(10);
123 l_request_id number;
124 l_exists varchar2(10);
125 l_bcolu_count number;
126 l_mrp_aset_id number;
127 l_cto_aset_id number;
128
129 Cursor Attachment_cur is
130 select distinct ato_line_id
131 from bom_cto_order_lines_upg
132 where status = 'CTO_SRC'
133 and line_id = ato_line_id;
134
135
136 x_return_status Varchar2(1);
137 x_msg_count Number;
138 x_msg_data Varchar2(1000);
139 BEGIN
140
141 retcode := 0;
142
143 --
144 -- processing if 'all models' is selected for upgrade
145 --
146 WriteToLog('Begin Update Existing Configurations with Debug Level: '||gDebugLevel);
147 WriteToLog('Parameters passed:');
148 WriteToLog(' Items: '||p_item);
149 WriteToLog(' Item Category: '||p_cat_id);
150 WriteToLog(' Configuration Item: '||p_config_id);
151 WriteToLog(' Sourced configurations: '||p_changed_src);
152 WriteToLog(' Process existing order lines: '||p_open_lines);
153 WriteToLog(' Upgrade Mode : '||to_char(p_upgrade_mode));
154
155 l_req_data := fnd_conc_global.request_data;
156 WriteToLog('l_req_data: '||l_req_data);
157
158 IF (l_req_data = 'CTO') THEN
159 GOTO RESTART;
160 END IF;
161
162
163 If p_upgrade_mode = 3 then
164 update_atp_attributes(
165 p_item => p_item,
166 p_cat_id => p_cat_id,
167 p_config_id => p_config_id,
168 x_return_status => x_return_status,
169 x_msg_data => x_msg_data,
170 x_msg_count => x_msg_count);
171 WriteToLog('+++++++++++++++++++++++++++++++++++++++++++++++++++');
172 WriteToLog('Update Existing Configurations completed with SUCCESS');
173 WriteToLog('+++++++++++++++++++++++++++++++++++++++++++++++++++');
174 errbuf := 'Program completed successfully.';
175 return;
176
177 End if;
178 --
179 -- delete from bcol_upg and bcso_b
180 --
181 l_stmt_num := 10;
182 delete from bom_cto_src_orgs_b
183 where line_id in (
184 select bcolu.line_id
185 from bom_cto_order_lines_upg bcolu
186 where bcolu.config_item_id is not null
187 and not exists (
188 select 'exists'
189 from oe_order_lines_all oel
190 where oel.line_id = bcolu.line_id));
191 WriteToLog('Rows deleted from bcso_b::'|| sql%rowcount, 1);
192
193 l_stmt_num := 15;
194 delete from bom_cto_order_lines_upg;
195 WriteToLog('Rows deleted from bcol_upg::'|| sql%rowcount, 1);
196
197 BEGIN
198 select assignment_set_id
199 into l_cto_aset_id
200 from mrp_assignment_sets
201 where assignment_set_name = 'CTO Configuration Updates';
202
203 WriteToLog('CTO Seeded Assignment Set Id::'||l_cto_aset_id, 2);
204
205 EXCEPTION
206 WHEN no_data_found THEN
207 WriteToLog('ERROR: CTO seeded assignment set not found', 1);
208
209 --start bugfix 3368052
210 --it has been decided not to seed assignment
211 --instead create programatically when it is not found
212 --during the first run of the program
213 --reason : this is not a fnd object to create through ldt
214 --and creating using a sql script requires giving lot of answers to
215 --release team
216
217 WriteToLog('Hence creating a assigment set', 1);
218
219 l_stmt_num := 16;
220 INSERT INTO mrp_assignment_sets
221 (assignment_set_id ,
222 assignment_set_name,
223 description,
224 created_by,
225 last_updated_by,
226 creation_date,
227 last_update_date
228 )
229 VALUES
230 ( MRP_ASSIGNMENT_SETS_S.nextval,
231 'CTO Configuration Updates',
232 'Exclusively for use by CTO. Used during Upgrade Concurrent programs',
233 FND_GLOBAL.USER_ID,
234 FND_GLOBAL.USER_ID,
235 sysdate,
236 sysdate
237 )
238 returning assignment_set_id INTO l_cto_aset_id;
239
240 WriteToLog('Created Assignment set with assignment set id::'||l_cto_aset_id, 2);
241 WriteToLog('Assignment set name::'|| 'CTO Configuration Updates', 2);
242
243 --end bugfix 3368052
244
245 END;
246
247 --
248 -- Delete all assignments from CTO Default Assignment Set
249 --
250 delete from mrp_sr_assignments
251 where assignment_set_id = l_cto_aset_id;
252
253 WriteToLog('Rows deleted from cto assignment set::'|| sql%rowcount, 1);
254
255 l_stmt_num := 18;
256 IF (p_item = 1) THEN
257 l_stmt_num := 20;
258 l_mrp_aset_id := to_number(FND_PROFILE.VALUE('MRP_DEFAULT_ASSIGNMENT_SET'));
259 WriteToLog('MRP Assignment Set Id::'||l_mrp_aset_id, 2);
260
261 l_stmt_num := 22;
262 populate_all_models(
263 p_changed_src,
264 p_open_lines,
265 l_return_status,
266 l_msg_count,
267 l_msg_data);
268
269 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
270 WriteToLog('ERROR: Populate_all_models returned unexpected error');
271 raise FND_API.G_EXC_UNEXPECTED_ERROR;
272 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
273 WriteToLog('ERROR: Populate_all_models returned expected error');
274 raise FND_API.G_EXC_ERROR;
275 ELSE
276 WriteToLog('Populate_all_models returned success', 3);
277 END IF;
278
279 --
280 -- Delete sourcing for canned configurations not being upgraded
281 -- (config_creation = 1 or 2) and not linked on open order lines
282 -- Sourcing should not be deleted for pre-configured items in
283 -- the match tables.
284 --
285
286 -- Modified by Renga Kannan on 06/06/06
287 -- Fixed for bug 5263027
288 -- Added a conidtion to check for open order with config items linked to the sales order
289
290 l_stmt_num := 25;
291 delete from mrp_sr_assignments
292 where assignment_set_id = l_mrp_aset_id
293 and inventory_item_id in
294 (select config_item_id
295 from bom_ato_configurations bac
296 where not exists
297 (select 'exists'
298 from bom_cto_order_lines_upg bcolu
299 where bcolu.config_item_id = bac.config_item_id)
300 -- and not on open order lines
301 and not exists
302 (select 'exists'
303 from oe_order_lines_all oel,
304 bom_cto_order_lines bcol
305 where bcol.config_item_id = bac.config_item_id
306 and bcol.ato_line_id = oel.ato_line_id
307 and nvl(oel.open_flag, 'N') = 'Y'
308 and oel.item_type_code='CONFIG')
309 -- and item is not pre-configured
310 and not exists
311 (select 'pc'
312 from mtl_system_items msi
313 where msi.inventory_item_id = bac.config_item_id
314 and nvl(msi.auto_created_config_flag,'N') = 'N'));
315
316 WriteToLog('Sourcing deleted::'||sql%rowcount, 2);
317
318 ELSIF (p_item = 2) THEN
319 l_stmt_num := 30;
320 l_mrp_aset_id := to_number(FND_PROFILE.VALUE('MRP_DEFAULT_ASSIGNMENT_SET'));
321 WriteToLog('MRP Assignment Set Id::'||l_mrp_aset_id, 2);
322
323 l_stmt_num := 34;
324 populate_cat_models(
325 p_cat_id,
326 p_changed_src,
327 p_open_lines,
328 l_return_status,
329 l_msg_count,
330 l_msg_data);
331
332 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
333 WriteToLog('ERROR: Populate_cat_models returned unexpected error');
334 raise FND_API.G_EXC_UNEXPECTED_ERROR;
335 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
336 WriteToLog('ERROR: Populate_cat_models returned expected error');
337 raise FND_API.G_EXC_ERROR;
338 ELSE
339 WriteToLog('Populate_cat_models returned success', 3);
340 END IF;
341
342 --
343 -- Delete sourcing for canned configurations not being upgraded
344 -- (config_creation = 1 or 2) and not on open order lines
345 -- Sourcing should not be deleted for pre-configured items in the
346 -- match tables.
347 --
348 l_stmt_num := 36;
349
350 -- Modified by Renga Kannan on 06/06/06
351 -- Fixed for bug 5263027
352 -- Added a conidtion to check for open order with config items linked to the sales order
353 /* SQL Rewritten as part of performance fix 3641207 */
354 delete from mrp_sr_assignments
355 where assignment_set_id = l_mrp_aset_id
356 and inventory_item_id in
357 (
358 select config_item_id
359 from bom_ato_configurations bac,
360 mtl_item_categories mcat
361 where bac.base_model_id = mcat.inventory_item_id
362 and mcat.category_id = p_cat_id
363 and not exists
364 (select 'exists'
365 from bom_cto_order_lines_upg bcolu
366 where bcolu.config_item_id = bac.config_item_id
367 )
368 and bac.config_item_id not in
369 (select nvl( bcol.config_item_id,-1)
370 from oe_order_lines_all oel,
371 bom_cto_order_lines bcol
372 where bcol.ato_line_id = oel.ato_line_id
373 and oel.item_type_code = 'CONFIG'
374 and open_flag = 'Y'
375 )
376 and not exists
377 (select 'pc'
378 from mtl_system_items msi
379 where msi.inventory_item_id = bac.config_item_id
380 and msi.auto_created_config_flag = 'N'
381 )
382 );
383
384
385 WriteToLog('Sourcing deleted::'||sql%rowcount, 2);
386
387 ELSE
388 l_stmt_num := 40;
389 l_mrp_aset_id := to_number(FND_PROFILE.VALUE('MRP_DEFAULT_ASSIGNMENT_SET'));
390 WriteToLog('MRP Assignment Set Id::'||l_mrp_aset_id, 2);
391
392 l_stmt_num := 45;
393
394 populate_config(
395 p_changed_src,
396 p_open_lines,
397 p_config_id,
398 l_return_status,
399 l_msg_count,
400 l_msg_data);
401
402 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
403 WriteToLog('ERROR: Populate_configs returned unexpected error');
404 raise FND_API.G_EXC_UNEXPECTED_ERROR;
405 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
406 WriteToLog('ERROR: Populate_configs returned expected error');
407 raise FND_API.G_EXC_ERROR;
408 ELSE
409 WriteToLog('Populate_configs returned success', 3);
410 END IF;
411
412 l_stmt_num := 45;
413
414 --
415 -- Delete sourcing if this configuration is not being upgraded
416 -- (config_creation = 1 or 2) and is not on open order lines.
417 -- Sourcing should not be deleted if it is a pre-configured item.
418 --
419
420 delete from mrp_sr_assignments
421 where assignment_set_id = l_mrp_aset_id
422 and inventory_item_id = p_config_id
423 -- not being upgraded
424 and not exists
425 (select 'exists'
426 from bom_cto_order_lines_upg bcolu
427 where bcolu.config_item_id = p_config_id)
428 -- and not on open order lines
429 and not exists /* bug 3399310 sushant changed the query to identify config item exists */
430 (select 'exists'
431 from oe_order_lines_all oel,
432 bom_cto_order_lines bcol
433 where bcol.config_item_id = p_config_id
434 and bcol.line_id = oel.ato_line_id
435 and oel.item_type_code = 'CONFIG'
436 and nvl(oel.open_flag, 'N') = 'Y')
437 -- and item is not pre-configured
438 and not exists
439 (select 'pc'
440 from mtl_system_items msi
441 where msi.inventory_item_id = p_config_id
442 and nvl(msi.auto_created_config_flag,'N') = 'N');
443
444 WriteToLog('Sourcing deleted::'||sql%rowcount, 2);
445
446 END IF;
447
448 --
449 -- if no rows populated into bcol, return
450 --
451 select count(*)
452 into l_bcolu_count
453 from bom_cto_order_lines_upg;
454 WriteToLog('Rows populated in bcol_upg::'||l_bcolu_count, 1);
455
456 IF l_bcolu_count = 0 THEN
457 WriteToLog('+++++++++++++++++++++++++++++++++++++++', 1);
458 WriteToLog('No configuration items to be processed.', 1);
459 WriteToLog('+++++++++++++++++++++++++++++++++++++++', 1);
460 return;
461 END IF;
462
463 --start bugfix 3377963
464
465 --an model with CIB attribute 1 or 2 cannot be as a child of
466 --model whose CIB attribute is 3
467 --perfoming above validation by calling following API
468 l_stmt_num := 50;
469 Check_invalid_configurations(l_return_status);
470
471 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
472 WriteToLog('Check_invalid_configurations returned unexpected error', 1);
473 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
474 ELSE
475 WriteToLog('Check_invalid_configurations returned success', 3);
476 END IF;
477
478 l_stmt_num := 51;
479
480 select count(*)
481 into l_bcolu_count
482 from bom_cto_order_lines_upg
483 where status <>'ERROR';
484
485 WriteToLog('Rows populated in bcol_upg and NOT in error status::'||l_bcolu_count, 1);
486
487 IF l_bcolu_count = 0 THEN
488
489 WriteToLog('+++++++++++++++++++++++++++++++++++++++', 1);
490 WriteToLog('No configuration items to be processed.', 1);
491 WriteToLog('+++++++++++++++++++++++++++++++++++++++', 1);
492
493 -- Write status of all config items processed to log file
494 --
495
496 Write_Config_Status(l_return_status);
497
498 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
499 WriteToLog('Write_Config_Status returned unexpected error', 1);
500 ELSE
501 WriteToLog('Write_Config_Status returned success', 3);
502 END IF;
503
504
505 return;
506 END IF;
507 --bugfix 3377963
508
509 --
510 -- update all cfgs to be upgraded with a sequence number
511 --
512 l_stmt_num := 60;
513 WHILE (TRUE) LOOP
514
515 l_seq := l_seq + 1;
516
517 update bom_cto_order_lines_upg bcolu
518 set bcolu.sequence = l_seq
519 where bcolu.ato_line_id in
520 (select ato_line_id
521 from bom_cto_order_lines_upg bcolu2
522 where bcolu2.ato_line_id = bcolu2.line_id
523 and bcolu2.status IN ('UPG')
524 and rownum < G_BATCH_SIZE + 1
525 and bcolu2.sequence is null);
526
527 IF sql%notfound THEN
528 exit;
529 END IF;
530
531 END LOOP;
532
533 WriteToLog('Done updating sequence in bcol_upg', 4);
534
535 --
536 -- create items, populate bcso and create sourcing
537 --
538 l_stmt_num := 70;
539
540 Cto_Update_Items_Pk.Update_Items_And_Sourcing(
541 p_changed_src => p_changed_src
542 , p_cat_id => p_cat_id
543 , p_upgrade_mode => p_upgrade_mode
544 , xReturnStatus => l_return_status
545 , xMsgCount => l_msg_count
546 , xMsgData => l_msg_data);
547
548 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
549 WriteToLog('ERROR: Update_items_and_sourcing returned unexpected error');
550 raise FND_API.G_EXC_UNEXPECTED_ERROR;
551 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
552 WriteToLog('ERROR: Update_items_and_sourcing returned expected error');
553 --raise FND_API.G_EXC_ERROR;
554 ELSE
555 WriteToLog('Update_items_and_sourcing returned success', 3);
556 END IF;
557
558
559 --
560 -- Added by Renga on 01/20/04 . Added a call to create item attachments
561 --
562 WriteToLog('Before creating Attachments', 3);
563 For attachment_rec in attachment_cur
564 loop
565
566 CTO_UTILITY_PK.create_item_attachments(p_ato_line_id => attachment_rec.ato_line_id,
567 x_return_status => x_return_status,
568 x_msg_count => x_msg_count,
569 x_msg_data => x_msg_data);
570 End loop;
571 WriteToLog('After creating Attachments', 3);
572
573 /* End of addition by Renga */
574
575 --
576 -- Launch child request to create BOM for each sequence
577 --
578
579 -- rkaza. bug 4524248. bom structure import enhancements. 11/05/05.
580 l_stmt_num := 75;
581
582 WriteToLog('update_configs: About to generate bom batch ID', 5);
583
584 cto_msutil_pub.set_bom_batch_id(x_return_status => l_return_status);
585
586 if l_return_status <> fnd_api.G_RET_STS_SUCCESS then
587 WriteToLog('update_configs: ' || 'Failed in set_bom_batch_id with unexp error.', 1);
588 raise FND_API.G_EXC_UNEXPECTED_ERROR;
589 end if;
590
591 l_stmt_num := 80;
592 l_seq := 0;
593
594 WHILE TRUE LOOP
595
596 l_seq := l_seq + 1;
597 BEGIN
598 select 'exists'
599 into l_exists
600 from bom_cto_order_lines_upg
601 where sequence = l_seq
602 and rownum = 1;
603
604 EXCEPTION
605 WHEN no_data_found THEN
606 exit;
607 END; -- sub block
608
609 WriteToLog('going to call CTOUPBOM with l_seq :: '||to_char(l_seq));
610 WriteToLog('and p_changed_src:: '||p_changed_src );
611
612 -- Added by Renga Kannan 03/30/06
613 -- This is a wrapper API to call PLM team's to sync up item media index
614 -- With out this sync up the item cannot be searched in Simple item search page
615 -- This is fixed for bug 4656048
616
617 CTO_MSUTIL_PUB.syncup_item_media_index;
618
619 l_stmt_num := 90;
620 l_request_id := fnd_request.submit_request(
621 'BOM',
622 'CTOUPBOM',
623 null,
624 null,
625 TRUE, -- should be TRUE, but inactive mgr issue
626 l_seq,
627 p_changed_src);
628
629 WriteToLog('l_request_id:: '||to_char(l_request_id));
630
631 IF (l_request_id = 0) THEN
632 WriteToLog('ERROR: Error launching child request for BOM creation.', 1);
633 raise FND_API.G_EXC_UNEXPECTED_ERROR;
634 END IF;
635
636 END LOOP;
637
638 fnd_conc_global.set_req_globals(
639 conc_status => 'PAUSED',
640 request_data => 'CTO'
641 );
642 return;
643
644 << RESTART >>
645 --
646 -- The program will restart from this point after child requests complete
647 -- We should not rely on any variables that were initialized in the earlier
648 -- part of this procedure, as they would be reset
649 --
650 l_stmt_num := 100;
651 WriteToLog('Program restarted.');
652
653 --
654 -- Removing from CTO category
655 --
656 IF (p_item = 2) THEN
657 delete from mtl_item_categories
658 where category_id = p_cat_id;
659
660 WriteToLog('Rows deleted from category::'||sql%rowcount, 2);
661 END IF;
662
663
664 --
665 -- Write status of all config items processed to log file
666 --
667 Write_Config_Status(l_return_status);
668
669 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
670 WriteToLog('Write_Config_Status returned unexpected error', 1);
671 ELSE
672 WriteToLog('Write_Config_Status returned success', 3);
673 END IF;
674
675 WriteToLog('+++++++++++++++++++++++++++++++++++++++++++++++++++');
676 WriteToLog('Update Existing Configurations completed with SUCCESS');
677 WriteToLog('+++++++++++++++++++++++++++++++++++++++++++++++++++');
678 errbuf := 'Program completed successfully.';
679
680 EXCEPTION
681
682 WHEN FND_API.G_EXC_ERROR THEN
683 WriteToLog('ERROR: Exp error in CTO_Update_Configs_Pk.Update_Configs:: '|| l_stmt_num ||'::'||sqlerrm);
684 WriteToLog('+++++++++++++++++++++++++++++++++++++++++++++++++++');
685 WriteToLog('Update Existing Configurations completed with ERROR.', 1);
686 WriteToLog('Please contact the system administrator.', 1);
687 WriteToLog('+++++++++++++++++++++++++++++++++++++++++++++++++++');
688 errbuf := 'Program completed with error';
689 retcode := 2; --exits with error
690
691 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
692 WriteToLog('ERROR: Unexp error in CTO_Update_Configs_Pk.Update_Configs:: '|| l_stmt_num ||'::'||sqlerrm, 1);
693 WriteToLog('+++++++++++++++++++++++++++++++++++++++++++++++++++');
694 WriteToLog('Update Existing Configurations completed with ERROR');
695 WriteToLog('+++++++++++++++++++++++++++++++++++++++++++++++++++');
696 errbuf := 'Program completed with error';
697 retcode := 2; --exits with error
698
699 WHEN OTHERS THEN
700 WriteToLog('ERROR: Others error in CTO_Update_Configs_Pk.Update_Configs:: '|| l_stmt_num ||'::'||sqlerrm, 1);
701 WriteToLog('+++++++++++++++++++++++++++++++++++++++++++++++++++');
702 WriteToLog('Update Existing Configurations completed with ERROR.', 1);
703 WriteToLog('Please contact the system administrator.', 1);
704 WriteToLog('+++++++++++++++++++++++++++++++++++++++++++++++++++');
705 errbuf := 'Progam completed with error';
706 retcode := 2; --exits with error
707
708 END update_configs;
709
710
711 PROCEDURE populate_all_models(
712 p_changed_src IN varchar2,
713 p_open_lines IN varchar2,
714 x_return_status out NOCOPY varchar2,
715 x_msg_count out NOCOPY number,
716 x_msg_data out NOCOPY varchar2)
717
718 IS
719
720 --
721 -- cursor to select all individual (not top level) config
722 -- items in bac having item attribute = 3 and not in bcol_upg
723 --
724 CURSOR c_bac IS
725 -- individual configs not in bcol and having item attribute 3
726 select distinct bac.config_item_id config_id
727 from bom_ato_configurations bac,
728 mtl_system_items msi
729 where NOT EXISTS
730 (select 'exists'
731 from bom_cto_order_lines_upg bcolu
732 where bcolu.config_item_id = bac.config_item_id)
733 and bac.base_model_id = msi.inventory_item_id
734 and bac.organization_id = msi.organization_id
735 and nvl(msi.config_orgs, '1') = '3';
736
737
738 --
739 -- cursor to select all top level config
740 -- items in bac having item attribute = 3 and not in bcol_upg
741 --
742 CURSOR c_bac_top IS
743 -- individual configs not in bcol and having item attribute 3
744 select distinct bac.config_item_id config_id
745 from bom_ato_configurations bac,
746 mtl_system_items msi
747 -- item attribute is 3
748 where bac.base_model_id = msi.inventory_item_id
749 and bac.organization_id = msi.organization_id
750 and nvl(msi.config_orgs, '1') = '3'
751 -- and is top parent with attribute 3
752 and NOT EXISTS
753 (select 'exists'
754 from bom_ato_configurations bac2
755 , mtl_system_items msi2
756 where bac.config_item_id = bac2.component_item_id
757 and bac2.base_model_id = msi2.inventory_item_id
758 and bac2.organization_id = msi2.organization_id
759 and nvl(msi2.config_orgs, '1') = '3')
760 -- and not already in bcol_upg
761 and NOT EXISTS
762 (select 'exists'
763 from bom_cto_order_lines_upg bcolu
764 where bcolu.config_item_id = bac.config_item_id);
765
766 l_match NUMBER;
767 l_exists varchar2(1);
768 l_return_status varchar2(1);
769 l_msg_count number;
770 l_msg_data varchar2(240);
771 l_stmt_num number := 0;
772
773 l_count number;
774
775 BEGIN
776 x_return_status := FND_API.G_RET_STS_SUCCESS;
777 WriteToLog('Entering populate_all_models', 1);
778
779 l_stmt_num := 10;
780 l_match := fnd_profile.value('BOM:MATCH_CONFIG');
781 WriteToLog('l_match is: '|| l_match, 1);
782
783 l_stmt_num := 20;
784 IF ((l_match = 2) AND (p_open_lines = 'N')) THEN
785 -- match is off and open lines not to be upgraded
786 WriteToLog('+++++++++++++++++++++++++++++++++++++++++++++');
787 WriteToLog('Match profile is No and you chose not to update existing configurations. No configurations will be updated.');
788 WriteToLog('+++++++++++++++++++++++++++++++++++++++++++++');
789 return;
790 END IF;
791
792
793 l_stmt_num := 40;
794 IF (p_changed_src = 'N') THEN
795 -- sourcing has not changed
796 WriteToLog('No changed sourcing', 1);
797 l_stmt_num := 50;
798 IF p_open_lines = 'Y' THEN
799 --
800 -- select all open order lines having config items with attribute in (2,3)
801 -- populate into bcol_upg
802 -- mark as UPG
803 --
804 WriteToLog('sql 1', 3);
805
806 select count(line_id)
807 into l_count
808 from bom_cto_order_lines_upg;
809 WriteToLog('kiran cont in bcol_upgs is =>'||l_count);
810
811 l_stmt_num := 60;
812 insert into bom_cto_order_lines_upg
813 (
814 ATO_LINE_ID
815 , BATCH_ID
816 , BOM_ITEM_TYPE
817 , COMPONENT_CODE
818 , COMPONENT_SEQUENCE_ID
819 , CONFIG_ITEM_ID
820 , INVENTORY_ITEM_ID
821 , ITEM_TYPE_CODE
822 , LINE_ID
823 , LINK_TO_LINE_ID
824 , ORDERED_QUANTITY
825 , ORDER_QUANTITY_UOM
826 , PARENT_ATO_LINE_ID
827 , PERFORM_MATCH
828 , PLAN_LEVEL
829 , SCHEDULE_SHIP_DATE
830 , SHIP_FROM_ORG_ID
831 , TOP_MODEL_LINE_ID
832 , WIP_SUPPLY_TYPE
833 , HEADER_ID
834 , LAST_UPDATE_DATE
835 , LAST_UPDATED_BY
836 , CREATION_DATE
837 , CREATED_BY
838 , LAST_UPDATE_LOGIN
839 , REQUEST_ID
840 , PROGRAM_APPLICATION_ID
841 , PROGRAM_ID
842 , PROGRAM_UPDATE_DATE
843 , OPTION_SPECIFIC
844 , REUSE_CONFIG
845 , QTY_PER_PARENT_MODEL
846 , STATUS
847 , config_creation
848 )
849 select distinct
850 bcol2.ATO_LINE_ID
851 , bcol2.BATCH_ID
852 , bcol2.BOM_ITEM_TYPE
853 , bcol2.COMPONENT_CODE
854 , bcol2.COMPONENT_SEQUENCE_ID
855 , bcol2.CONFIG_ITEM_ID
856 , bcol2.INVENTORY_ITEM_ID
857 , bcol2.ITEM_TYPE_CODE
858 , bcol2.LINE_ID
859 , bcol2.LINK_TO_LINE_ID
860 , bcol2.ORDERED_QUANTITY
861 , bcol2.ORDER_QUANTITY_UOM
862 , bcol2.PARENT_ATO_LINE_ID
863 , bcol2.PERFORM_MATCH --7201878
864 --, 'N' --PERFORM_MATCH
865 , bcol2.PLAN_LEVEL
866 , bcol2.SCHEDULE_SHIP_DATE
867 , bcol2.SHIP_FROM_ORG_ID
868 , bcol2.TOP_MODEL_LINE_ID
869 , bcol2.WIP_SUPPLY_TYPE
870 , bcol2.HEADER_ID
871 , sysdate --LAST_UPDATE_DATE
872 , bcol2.LAST_UPDATED_BY
873 , sysdate --CREATION_DATE
874 , bcol2.CREATED_BY
875 , bcol2.LAST_UPDATE_LOGIN
876 , bcol2.REQUEST_ID
877 , bcol2.PROGRAM_APPLICATION_ID
878 , bcol2.PROGRAM_ID
879 , sysdate --PROGRAM_UPDATE_DATE
880 , bcol2.OPTION_SPECIFIC
881 , 'N' --REUSE_CONFIG
882 , bcol2.QTY_PER_PARENT_MODEL
883 , 'UPG' --STATUS
884 , nvl(mtl.config_orgs, '1')
885 --changed the where clause to use a subquery
886 --bugfix 3841575
887 from bom_cto_order_lines bcol2
888 , mtl_system_items mtl
889 -- select entire configuration
890 where mtl.inventory_item_id = bcol2.inventory_item_id
891 and mtl.organization_id = bcol2.ship_from_org_id
892 and bcol2.ato_line_id in
893 (select distinct bcol1.ato_line_id
894 from bom_cto_order_lines bcol1
895 , oe_order_lines_all oel
896 , mtl_system_items msi
897 -- for configs whose models have attr=2,3
898 where bcol1.config_item_id is not null
899 and bcol1.inventory_item_id = msi.inventory_item_id
900 and bcol1.ship_from_org_id = msi.organization_id
901 and nvl(msi.config_orgs, '1') in ('2', '3')
902 -- and are on open order lines
903 and bcol1.line_id = oel.line_id
904 and nvl(oel.open_flag, 'N') = 'Y');
905
906
907 WriteToLog('Rows inserted::'|| to_char(sql%rowcount), 3);
908
909 ELSE /* p_open_lines = 'N' */
910 --
911 -- select all open order lines having canned config items with attribute = 3
912 -- populate into bcol_upg
913 -- mark as UPG
914 --
915 WriteToLog('sql 2', 3);
916 l_stmt_num := 70;
917 insert into bom_cto_order_lines_upg
918 (
919 ATO_LINE_ID
920 , BATCH_ID
921 , BOM_ITEM_TYPE
922 , COMPONENT_CODE
923 , COMPONENT_SEQUENCE_ID
924 , CONFIG_ITEM_ID
925 , INVENTORY_ITEM_ID
926 , ITEM_TYPE_CODE
927 , LINE_ID
928 , LINK_TO_LINE_ID
929 , ORDERED_QUANTITY
930 , ORDER_QUANTITY_UOM
931 , PARENT_ATO_LINE_ID
932 , PERFORM_MATCH
933 , PLAN_LEVEL
934 , SCHEDULE_SHIP_DATE
935 , SHIP_FROM_ORG_ID
936 , TOP_MODEL_LINE_ID
937 , WIP_SUPPLY_TYPE
938 , HEADER_ID
939 , LAST_UPDATE_DATE
940 , LAST_UPDATED_BY
941 , CREATION_DATE
942 , CREATED_BY
943 , LAST_UPDATE_LOGIN
944 , REQUEST_ID
945 , PROGRAM_APPLICATION_ID
946 , PROGRAM_ID
947 , PROGRAM_UPDATE_DATE
948 , OPTION_SPECIFIC
949 , REUSE_CONFIG
950 , QTY_PER_PARENT_MODEL
951 , STATUS
952 , config_creation
953 )
954 select distinct
955 bcol2.ATO_LINE_ID
956 , bcol2.BATCH_ID
957 , bcol2.BOM_ITEM_TYPE
958 , bcol2.COMPONENT_CODE
959 , bcol2.COMPONENT_SEQUENCE_ID
960 , bcol2.CONFIG_ITEM_ID
961 , bcol2.INVENTORY_ITEM_ID
962 , bcol2.ITEM_TYPE_CODE
963 , bcol2.LINE_ID
964 , bcol2.LINK_TO_LINE_ID
965 , bcol2.ORDERED_QUANTITY
966 , bcol2.ORDER_QUANTITY_UOM
967 , bcol2.PARENT_ATO_LINE_ID
968 , bcol2.PERFORM_MATCH --7201878
969 --, 'Y' --PERFORM_MATCH /* Sushant Made changes for identifying matched items */
970 , bcol2.PLAN_LEVEL
971 , bcol2.SCHEDULE_SHIP_DATE
972 , bcol2.SHIP_FROM_ORG_ID
973 , bcol2.TOP_MODEL_LINE_ID
974 , bcol2.WIP_SUPPLY_TYPE
975 , bcol2.HEADER_ID
976 , sysdate --LAST_UPDATE_DATE
977 , bcol2.LAST_UPDATED_BY
978 , sysdate --CREATION_DATE
979 , bcol2.CREATED_BY
980 , bcol2.LAST_UPDATE_LOGIN
981 , bcol2.REQUEST_ID
982 , bcol2.PROGRAM_APPLICATION_ID
983 , bcol2.PROGRAM_ID
984 , sysdate --PROGRAM_UPDATE_DATE
985 , bcol2.OPTION_SPECIFIC
986 , 'N' --REUSE_CONFIG
987 , bcol2.QTY_PER_PARENT_MODEL
988 , 'UPG' --STATUS
989 , nvl(msi.config_orgs, '1')
990 from bom_cto_order_lines bcol1
991 , bom_cto_order_lines bcol2
992 , bom_ato_configurations bac
993 , oe_order_lines_all oel
994 , mtl_system_items msi
995 -- base model has item attr = 3
996 where bac.base_model_id = msi.inventory_item_id
997 and bac.organization_id = msi.organization_id
998 and nvl(msi.config_orgs, '1') = '3'
999 -- and exists in bcol
1000 and bac.config_item_id = bcol1.config_item_id
1001 -- on open order lines
1002 and bcol1.line_id = oel.line_id
1003 and nvl(oel.open_flag, 'N') = 'Y'
1004 and bcol2.ato_line_id = bcol1.ato_line_id;
1005 WriteToLog('Rows inserted::'|| to_char(sql%rowcount), 3);
1006
1007 END IF; /* p_open_lines = 'Y' */
1008
1009 IF l_match = 1 THEN
1010 --
1011 -- select additional config items with attribute = 3 on closed order lines
1012 -- populate into bcol_upg
1013 -- mark as UPG
1014 --
1015 WriteToLog('sql 3', 3);
1016 l_stmt_num := 80;
1017 insert into bom_cto_order_lines_upg
1018 (
1019 ATO_LINE_ID
1020 , BATCH_ID
1021 , BOM_ITEM_TYPE
1022 , COMPONENT_CODE
1023 , COMPONENT_SEQUENCE_ID
1024 , CONFIG_ITEM_ID
1025 , INVENTORY_ITEM_ID
1026 , ITEM_TYPE_CODE
1027 , LINE_ID
1028 , LINK_TO_LINE_ID
1029 , ORDERED_QUANTITY
1030 , ORDER_QUANTITY_UOM
1031 , PARENT_ATO_LINE_ID
1032 , PERFORM_MATCH
1033 , PLAN_LEVEL
1034 , SCHEDULE_SHIP_DATE
1035 , SHIP_FROM_ORG_ID
1036 , TOP_MODEL_LINE_ID
1037 , WIP_SUPPLY_TYPE
1038 , HEADER_ID
1039 , LAST_UPDATE_DATE
1040 , LAST_UPDATED_BY
1041 , CREATION_DATE
1042 , CREATED_BY
1043 , LAST_UPDATE_LOGIN
1044 , REQUEST_ID
1045 , PROGRAM_APPLICATION_ID
1046 , PROGRAM_ID
1047 , PROGRAM_UPDATE_DATE
1048 , OPTION_SPECIFIC
1049 , REUSE_CONFIG
1050 , QTY_PER_PARENT_MODEL
1051 , STATUS
1052 , config_creation
1053 )
1054 select distinct
1055 bcol.ATO_LINE_ID
1056 , bcol.BATCH_ID
1057 , bcol.BOM_ITEM_TYPE
1058 , bcol.COMPONENT_CODE
1059 , bcol.COMPONENT_SEQUENCE_ID
1060 , bcol.CONFIG_ITEM_ID
1061 , bcol.INVENTORY_ITEM_ID
1062 , bcol.ITEM_TYPE_CODE
1063 , bcol.LINE_ID
1064 , bcol.LINK_TO_LINE_ID
1065 , bcol.ORDERED_QUANTITY
1066 , bcol.ORDER_QUANTITY_UOM
1067 , bcol.PARENT_ATO_LINE_ID
1068 , bcol.PERFORM_MATCH --7201878
1069 --, 'Y' --PERFORM_MATCH /* Sushant made changes to identify matched items */
1070 , bcol.PLAN_LEVEL
1071 , bcol.SCHEDULE_SHIP_DATE
1072 , bcol.SHIP_FROM_ORG_ID
1073 , bcol.TOP_MODEL_LINE_ID
1074 , bcol.WIP_SUPPLY_TYPE
1075 , bcol.HEADER_ID
1076 , sysdate --LAST_UPDATE_DATE
1077 , bcol.LAST_UPDATED_BY
1078 , sysdate --CREATION_DATE
1079 , bcol.CREATED_BY
1080 , bcol.LAST_UPDATE_LOGIN
1081 , bcol.REQUEST_ID
1082 , bcol.PROGRAM_APPLICATION_ID
1083 , 99 -- matched item on closed line
1084 , bcol.PROGRAM_UPDATE_DATE
1085 , bcol.OPTION_SPECIFIC
1086 , 'N' --REUSE_CONFIG
1087 , bcol.QTY_PER_PARENT_MODEL
1088 , 'UPG' --STATUS
1089 , nvl(msi.config_orgs, '1')
1090 from bom_ato_configurations bac
1091 , bom_cto_order_lines bcol
1092 , mtl_system_items msi
1093 -- base model has item attr = 3
1094 where bac.base_model_id = msi.inventory_item_id
1095 and bac.organization_id = msi.organization_id
1096 and nvl(msi.config_orgs, '1') = '3'
1097 -- and not already in bcol_upg
1098 and NOT EXISTS
1099 (select 'exists'
1100 from bom_cto_order_lines_upg bcolu
1101 where bcolu.config_item_id = bac.config_item_id)
1102 -- select first ato_line_id in bcol
1103 and bcol.ato_line_id =
1104 (select bcol1.ato_line_id
1105 from bom_cto_order_lines bcol1
1106 where bcol1.config_item_id = bac.config_item_id
1107 -- pick up only if config is at top level
1108 and bcol1.line_id = bcol1.ato_line_id
1109 and rownum = 1)
1110 ;
1111 WriteToLog('Rows inserted::'|| to_char(sql%rowcount), 3);
1112
1113 --
1114 -- select all individual (not top level) config items in bac having item attribute = 3 and not in bcol_upg
1115 -- populate into bcol_upg from bcol or bac
1116 -- mark as UPG
1117 -- mark with program_id = 99 to indicate that it was populated from bac
1118 --
1119 WriteToLog('sql 3', 2);
1120 l_stmt_num := 90;
1121 FOR v_bac IN c_bac LOOP
1122 --
1123 -- check to see if not already populated as part of parent
1124 --
1125 WriteToLog('Item being populated from bac::'|| to_char(v_bac.config_id), 4);
1126 BEGIN
1127 select 'Y'
1128 into l_exists
1129 from bom_cto_order_lines_upg
1130 where config_item_id = v_bac.config_id
1131 and rownum = 1;
1132 WriteToLog('Item::'|| to_char(v_bac.config_id)||' already exists in bcolu', 4);
1133
1134 EXCEPTION
1135 WHEN no_data_found THEN
1136 WriteToLog('Populating from bac Item::'|| to_char(v_bac.config_id), 4);
1137 populate_bcolu_from_bac(
1138 v_bac.config_id
1139 , l_return_status
1140 , l_msg_count
1141 , l_msg_data);
1142 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1143 WriteToLog('ERROR: Populate_bcolu_from_bac returned unexp error');
1144 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1145 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1146 WriteToLog('ERROR: Populate_bcolu_from_bac returned expected error');
1147 raise FND_API.G_EXC_ERROR;
1148 END IF;
1149 END; -- sub block
1150 END LOOP;
1151
1152 END IF; /* l_match = 1 */
1153 ELSE
1154 WriteToLog('Changed sourcing', 1);
1155
1156 -- srcing has changed
1157 l_stmt_num := 100;
1158 IF p_open_lines = 'Y' THEN
1159 --
1160 -- select all open order lines
1161 -- populate into bcol_upg
1162 -- mark as UPG
1163 -- TEST THIS!!
1164 --
1165 WriteToLog('sql 5', 3);
1166 l_stmt_num := 110;
1167 insert into bom_cto_order_lines_upg
1168 (
1169 ATO_LINE_ID
1170 , BATCH_ID
1171 , BOM_ITEM_TYPE
1172 , COMPONENT_CODE
1173 , COMPONENT_SEQUENCE_ID
1174 , CONFIG_ITEM_ID
1175 , INVENTORY_ITEM_ID
1176 , ITEM_TYPE_CODE
1177 , LINE_ID
1178 , LINK_TO_LINE_ID
1179 , ORDERED_QUANTITY
1180 , ORDER_QUANTITY_UOM
1181 , PARENT_ATO_LINE_ID
1182 , PERFORM_MATCH
1183 , PLAN_LEVEL
1184 , SCHEDULE_SHIP_DATE
1185 , SHIP_FROM_ORG_ID
1186 , TOP_MODEL_LINE_ID
1187 , WIP_SUPPLY_TYPE
1188 , HEADER_ID
1189 , LAST_UPDATE_DATE
1190 , LAST_UPDATED_BY
1191 , CREATION_DATE
1192 , CREATED_BY
1193 , LAST_UPDATE_LOGIN
1194 , REQUEST_ID
1195 , PROGRAM_APPLICATION_ID
1196 , PROGRAM_ID
1197 , PROGRAM_UPDATE_DATE
1198 , OPTION_SPECIFIC
1199 , REUSE_CONFIG
1200 , QTY_PER_PARENT_MODEL
1201 , STATUS
1202 , CONFIG_CREATION
1203 )
1204 select distinct
1205 bcol.ATO_LINE_ID
1206 , bcol.BATCH_ID
1207 , bcol.BOM_ITEM_TYPE
1208 , bcol.COMPONENT_CODE
1209 , bcol.COMPONENT_SEQUENCE_ID
1210 , bcol.CONFIG_ITEM_ID
1211 , bcol.INVENTORY_ITEM_ID
1212 , bcol.ITEM_TYPE_CODE
1213 , bcol.LINE_ID
1214 , bcol.LINK_TO_LINE_ID
1215 , bcol.ORDERED_QUANTITY
1216 , bcol.ORDER_QUANTITY_UOM
1217 , bcol.PARENT_ATO_LINE_ID
1218 , bcol.PERFORM_MATCH --7201878
1219 --, 'N' --PERFORM_MATCH
1220 , bcol.PLAN_LEVEL
1221 , bcol.SCHEDULE_SHIP_DATE
1222 , bcol.SHIP_FROM_ORG_ID
1223 , bcol.TOP_MODEL_LINE_ID
1224 , bcol.WIP_SUPPLY_TYPE
1225 , bcol.HEADER_ID
1226 , sysdate --LAST_UPDATE_DATE
1227 , bcol.LAST_UPDATED_BY
1228 , sysdate --CREATION_DATE
1229 , bcol.CREATED_BY
1230 , bcol.LAST_UPDATE_LOGIN
1231 , bcol.REQUEST_ID
1232 , bcol.PROGRAM_APPLICATION_ID
1233 , bcol.PROGRAM_ID
1234 , sysdate --PROGRAM_UPDATE_DATE
1235 , bcol.OPTION_SPECIFIC
1236 , 'N' --REUSE_CONFIG
1237 , bcol.QTY_PER_PARENT_MODEL
1238 , 'UPG' --STATUS
1239 , nvl(msi.CONFIG_ORGS, '1')
1240 from bom_cto_order_lines bcol
1241 , oe_order_lines_all oel
1242 , mtl_system_items msi
1243 -- select all configs on open order lines
1244 where bcol.ato_line_id = oel.ato_line_id
1245 and nvl(oel.open_flag, 'N') = 'Y'
1246 and bcol.inventory_item_id = msi.inventory_item_id
1247 and bcol.ship_from_org_id = msi.organization_id
1248 and oel.item_type_code = 'CONFIG' ; /* added condition for bug 3599397 */
1249
1250 WriteToLog('Rows inserted::'|| to_char(sql%rowcount), 3);
1251
1252 ELSE /* p_open_lines = 'N' */
1253 --
1254 -- select all open order lines having canned config items with attribute = 3
1255 -- populate into bcol_upg
1256 -- mark as UPG
1257 --
1258 WriteToLog('sql 6', 3);
1259 l_stmt_num := 120;
1260 insert into bom_cto_order_lines_upg
1261 (
1262 ATO_LINE_ID
1263 , BATCH_ID
1264 , BOM_ITEM_TYPE
1265 , COMPONENT_CODE
1266 , COMPONENT_SEQUENCE_ID
1267 , CONFIG_ITEM_ID
1268 , INVENTORY_ITEM_ID
1269 , ITEM_TYPE_CODE
1270 , LINE_ID
1271 , LINK_TO_LINE_ID
1272 , ORDERED_QUANTITY
1273 , ORDER_QUANTITY_UOM
1274 , PARENT_ATO_LINE_ID
1275 , PERFORM_MATCH
1276 , PLAN_LEVEL
1277 , SCHEDULE_SHIP_DATE
1278 , SHIP_FROM_ORG_ID
1279 , TOP_MODEL_LINE_ID
1280 , WIP_SUPPLY_TYPE
1281 , HEADER_ID
1282 , LAST_UPDATE_DATE
1283 , LAST_UPDATED_BY
1284 , CREATION_DATE
1285 , CREATED_BY
1286 , LAST_UPDATE_LOGIN
1287 , REQUEST_ID
1288 , PROGRAM_APPLICATION_ID
1289 , PROGRAM_ID
1290 , PROGRAM_UPDATE_DATE
1291 , OPTION_SPECIFIC
1292 , REUSE_CONFIG
1293 , QTY_PER_PARENT_MODEL
1294 , STATUS
1295 , CONFIG_CREATION
1296 )
1297 select distinct
1298 bcol2.ATO_LINE_ID
1299 , bcol2.BATCH_ID
1300 , bcol2.BOM_ITEM_TYPE
1301 , bcol2.COMPONENT_CODE
1302 , bcol2.COMPONENT_SEQUENCE_ID
1303 , bcol2.CONFIG_ITEM_ID
1304 , bcol2.INVENTORY_ITEM_ID
1305 , bcol2.ITEM_TYPE_CODE
1306 , bcol2.LINE_ID
1307 , bcol2.LINK_TO_LINE_ID
1308 , bcol2.ORDERED_QUANTITY
1309 , bcol2.ORDER_QUANTITY_UOM
1310 , bcol2.PARENT_ATO_LINE_ID
1311 , bcol2.PERFORM_MATCH --7201878
1312 --, 'Y' --PERFORM_MATCH /* Sushant made changes to identify matched items */
1313 , bcol2.PLAN_LEVEL
1314 , bcol2.SCHEDULE_SHIP_DATE
1315 , bcol2.SHIP_FROM_ORG_ID
1316 , bcol2.TOP_MODEL_LINE_ID
1317 , bcol2.WIP_SUPPLY_TYPE
1318 , bcol2.HEADER_ID
1319 , sysdate --LAST_UPDATE_DATE
1320 , bcol2.LAST_UPDATED_BY
1321 , sysdate --CREATION_DATE
1322 , bcol2.CREATED_BY
1323 , bcol2.LAST_UPDATE_LOGIN
1324 , bcol2.REQUEST_ID
1325 , bcol2.PROGRAM_APPLICATION_ID
1326 , bcol2.PROGRAM_ID
1327 , sysdate --PROGRAM_UPDATE_DATE
1328 , bcol2.OPTION_SPECIFIC
1329 , 'N' --REUSE_CONFIG
1330 , bcol2.QTY_PER_PARENT_MODEL
1331 , 'UPG' --STATUS
1332 , nvl(msi.CONFIG_ORGS, '1')
1333 from bom_cto_order_lines bcol1
1334 , bom_cto_order_lines bcol2
1335 , bom_ato_configurations bac
1336 , oe_order_lines_all oel
1337 , mtl_system_items msi
1338 -- base model has item attr = 3
1339 where bac.base_model_id = msi.inventory_item_id
1340 and bac.organization_id = msi.organization_id
1341 and nvl(msi.config_orgs, '1') = '3'
1342 -- and exists in bcol
1343 and bac.config_item_id = bcol1.config_item_id
1344 -- on open order lines
1345 and bcol1.line_id = oel.line_id
1346 and nvl(oel.open_flag, 'N') = 'Y'
1347 and bcol2.ato_line_id = bcol1.ato_line_id
1348 ;
1349
1350 WriteToLog('Rows inserted::'|| to_char(sql%rowcount), 3);
1351
1352 END IF; /* p_open_lines = 'Y' */
1353
1354 IF l_match = 1 THEN
1355 --
1356 -- select additional TOP LEVEL config items with attribute = 3 on closed order lines
1357 -- populate into bcol_upg
1358 -- mark as UPG
1359 --
1360 WriteToLog('sql 7', 3);
1361 l_stmt_num := 130;
1362 insert into bom_cto_order_lines_upg
1363 (
1364 ATO_LINE_ID
1365 , BATCH_ID
1366 , BOM_ITEM_TYPE
1367 , COMPONENT_CODE
1368 , COMPONENT_SEQUENCE_ID
1369 , CONFIG_ITEM_ID
1370 , INVENTORY_ITEM_ID
1371 , ITEM_TYPE_CODE
1372 , LINE_ID
1373 , LINK_TO_LINE_ID
1374 , ORDERED_QUANTITY
1375 , ORDER_QUANTITY_UOM
1376 , PARENT_ATO_LINE_ID
1377 , PERFORM_MATCH
1378 , PLAN_LEVEL
1379 , SCHEDULE_SHIP_DATE
1380 , SHIP_FROM_ORG_ID
1381 , TOP_MODEL_LINE_ID
1382 , WIP_SUPPLY_TYPE
1383 , HEADER_ID
1384 , LAST_UPDATE_DATE
1385 , LAST_UPDATED_BY
1386 , CREATION_DATE
1387 , CREATED_BY
1388 , LAST_UPDATE_LOGIN
1389 , REQUEST_ID
1390 , PROGRAM_APPLICATION_ID
1391 , PROGRAM_ID
1392 , PROGRAM_UPDATE_DATE
1393 , OPTION_SPECIFIC
1394 , REUSE_CONFIG
1395 , QTY_PER_PARENT_MODEL
1396 , STATUS
1397 , CONFIG_CREATION
1398 )
1399 select distinct
1400 bcol.ATO_LINE_ID
1401 , bcol.BATCH_ID
1402 , bcol.BOM_ITEM_TYPE
1403 , bcol.COMPONENT_CODE
1404 , bcol.COMPONENT_SEQUENCE_ID
1405 , bcol.CONFIG_ITEM_ID
1406 , bcol.INVENTORY_ITEM_ID
1407 , bcol.ITEM_TYPE_CODE
1408 , bcol.LINE_ID
1409 , bcol.LINK_TO_LINE_ID
1410 , bcol.ORDERED_QUANTITY
1411 , bcol.ORDER_QUANTITY_UOM
1412 , bcol.PARENT_ATO_LINE_ID
1413 , bcol.PERFORM_MATCH --7201878
1414 --, 'N' --PERFORM_MATCH /* Sushant made changes to identify matched items */
1415 , bcol.PLAN_LEVEL
1416 , bcol.SCHEDULE_SHIP_DATE
1417 , bcol.SHIP_FROM_ORG_ID
1418 , bcol.TOP_MODEL_LINE_ID
1419 , bcol.WIP_SUPPLY_TYPE
1420 , bcol.HEADER_ID
1421 , sysdate --LAST_UPDATE_DATE
1422 , bcol.LAST_UPDATED_BY
1423 , sysdate --CREATION_DATE
1424 , bcol.CREATED_BY
1425 , bcol.LAST_UPDATE_LOGIN
1426 , bcol.REQUEST_ID
1427 , bcol.PROGRAM_APPLICATION_ID
1428 , 99 -- matched item on closed line
1429 , bcol.PROGRAM_UPDATE_DATE
1430 , bcol.OPTION_SPECIFIC
1431 , 'N' --REUSE_CONFIG
1432 , bcol.QTY_PER_PARENT_MODEL
1433 , 'UPG' --STATUS
1434 , nvl(msi.CONFIG_ORGS, '1')
1435 from bom_ato_configurations bac
1436 , bom_cto_order_lines bcol
1437 , mtl_system_items msi
1438 -- base model has item attr = 3
1439 where bac.base_model_id = msi.inventory_item_id
1440 and bac.organization_id = msi.organization_id
1441 and nvl(msi.config_orgs, '1') = '3'
1442 -- and is top parent with attribute 3
1443 and NOT EXISTS
1444 (select 'exists'
1445 from bom_ato_configurations bac2
1446 , mtl_system_items msi2
1447 where bac.config_item_id = bac2.component_item_id
1448 and bac2.base_model_id = msi2.inventory_item_id
1449 and bac2.organization_id = msi2.organization_id
1450 and nvl(msi2.config_orgs, '1') = '3')
1451 -- and not already in bcol_upg
1452 and NOT EXISTS
1453 (select 'exists'
1454 from bom_cto_order_lines_upg bcolu
1455 where bcolu.config_item_id = bac.config_item_id)
1456 -- select first ato_line_id in bcol
1457 and bcol.ato_line_id =
1458 (select bcol1.ato_line_id
1459 from bom_cto_order_lines bcol1
1460 where bcol1.config_item_id = bac.config_item_id
1461 -- pick up only if config is at top level
1462 and bcol1.line_id = bcol1.ato_line_id
1463 and rownum = 1)
1464 ;
1465
1466 WriteToLog('Rows inserted::'|| to_char(sql%rowcount), 3);
1467
1468 --
1469 -- select all top level config items in bac having item attribute = 3 and not in bcol_upg
1470 -- populate into bcol_upg from bcol or bac
1471 -- mark as UPG
1472 -- mark with program_id = 99 to indicate that it was populated from bac
1473 --
1474 WriteToLog('sql 8', 3);
1475 l_stmt_num := 140;
1476 FOR v_bac_top IN c_bac_top LOOP
1477 WriteToLog('Item being populated from bac::'|| to_char(v_bac_top.config_id), 4);
1478 BEGIN
1479 select 'exists'
1480 into l_exists
1481 from bom_cto_order_lines_upg bcolu
1482 where bcolu.config_item_id = v_bac_top.config_id
1483 and rownum = 1;
1484 WriteToLog('Item::'|| to_char(v_bac_top.config_id)||' already exists in bcolu', 4);
1485 EXCEPTION
1486 WHEN NO_DATA_FOUND THEN
1487 WriteToLog('Populating from bac Item::'|| to_char(v_bac_top.config_id), 4);
1488 populate_bcolu_from_bac(
1489 v_bac_top.config_id
1490 , l_return_status
1491 , l_msg_count
1492 , l_msg_data);
1493 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1494 WriteToLog('ERROR: Populate_bcolu_from_bac returned unexp error');
1495 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1496 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1497 WriteToLog('ERROR: Populate_bcolu_from_bac returned expected error');
1498 raise FND_API.G_EXC_ERROR;
1499 END IF;
1500 END; -- sub-block
1501 END LOOP;
1502
1503 END IF; /* l_match = 1 */
1504
1505 END IF; /* sourcing not changed */
1506
1507 WriteToLog('Done populate_all_models.', 1);
1508
1509 EXCEPTION
1510 WHEN FND_API.G_EXC_ERROR THEN
1511 WriteToLog('ERROR: Expected error in Populate_All_Models::'||to_char(l_stmt_num)||'::'||sqlerrm);
1512 x_return_status := FND_API.G_RET_STS_ERROR;
1513 CTO_MSG_PUB.Count_And_Get
1514 (p_msg_count => x_msg_count
1515 ,p_msg_data => x_msg_data
1516 );
1517
1518 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1519 WriteToLog('ERROR: Unexpected error in Populate_All_Models::'||to_char(l_stmt_num)||'::'||sqlerrm);
1520 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1521 CTO_MSG_PUB.Count_And_Get
1522 (p_msg_count => x_msg_count
1523 ,p_msg_data => x_msg_data
1524 );
1525
1526 WHEN OTHERS THEN
1527 WriteToLog('ERROR: Others error in Populate_All_Models::'||to_char(l_stmt_num)||'::'||sqlerrm);
1528 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1529 CTO_MSG_PUB.Count_And_Get
1530 (p_msg_count => x_msg_count
1531 ,p_msg_data => x_msg_data
1532 );
1533
1534 END populate_all_models;
1535
1536
1537 PROCEDURE populate_cat_models(
1538 p_cat_id IN number,
1539 p_changed_src IN varchar2,
1540 p_open_lines IN varchar2,
1541 x_return_status out NOCOPY varchar2,
1542 x_msg_count out NOCOPY number,
1543 x_msg_data out NOCOPY varchar2)
1544
1545 IS
1546
1547 --
1548 -- cursor to select all individual (not top level) config
1549 -- items in bac having item attribute = 3
1550 -- and assigned to CTO category and not in bcol_upg
1551 --
1552 CURSOR c_bac(p_cat_id number) IS
1553 -- individual configs not in bcol and having item attribute 3
1554 select /*+ ORDERED */ distinct bac.config_item_id config_id --Bugfix 6617686 Added a hint
1555 from mtl_item_categories mcat, --Bugfix 6617686: Changed the order of tables
1556 mtl_system_items msi,
1557 bom_ato_configurations bac
1558 where NOT EXISTS
1559 (select 'exists'
1560 from bom_cto_order_lines_upg bcolu
1561 where bcolu.config_item_id = bac.config_item_id)
1562 and bac.base_model_id = msi.inventory_item_id
1563 and bac.organization_id = msi.organization_id
1564 and nvl(msi.config_orgs, '1') = '3'
1565 -- and base model is in CTO category
1566 and mcat.inventory_item_id = msi.inventory_item_id
1567 and mcat.organization_id = msi.organization_id
1568 and mcat.category_id = p_cat_id;
1569
1570
1571 --
1572 -- cursor to select all top level config
1573 -- items in bac having item attribute = 3
1574 -- and assigned to CTO category and not in bcol_upg
1575 --
1576 CURSOR c_bac_top(p_cat_id number) IS
1577 -- individual configs not in bcol and having item attribute 3
1578 select /*+ ORDERED*/ distinct bac.config_item_id config_id --Bugfix 6617686 Added a hint
1579 from mtl_item_categories mcat, --Bugfix 6617686 Changed the order of tables
1580 mtl_system_items msi,
1581 bom_ato_configurations bac
1582 -- item attribute is 3
1583 where bac.base_model_id = msi.inventory_item_id
1584 and bac.organization_id = msi.organization_id
1585 and nvl(msi.config_orgs, '1') = '3'
1586 -- and base model is in CTO category
1587 and mcat.inventory_item_id = msi.inventory_item_id
1588 and mcat.organization_id = msi.organization_id
1589 and mcat.category_id = p_cat_id
1590 -- and is top parent with attribute 3
1591 and NOT EXISTS
1592 (select 'exists'
1593 from bom_ato_configurations bac2
1594 , mtl_system_items msi2
1595 where bac.config_item_id = bac2.component_item_id
1596 and bac2.base_model_id = msi2.inventory_item_id
1597 and bac2.organization_id = msi2.organization_id
1598 and nvl(msi2.config_orgs, '1') = '3')
1599 -- and not already in bcol_upg
1600 and NOT EXISTS
1601 (select 'exists'
1602 from bom_cto_order_lines_upg bcolu
1603 where bcolu.config_item_id = bac.config_item_id);
1604
1605
1606 l_match NUMBER;
1607 l_exists varchar2(1);
1608 l_return_status varchar2(1);
1609 l_msg_count number;
1610 l_msg_data varchar2(240);
1611 l_stmt_num number := 0;
1612
1613 BEGIN
1614 WriteToLog ('Entering populate_cat_models', 1);
1615 x_return_status := FND_API.G_RET_STS_SUCCESS;
1616 l_stmt_num := 10;
1617
1618 l_match := fnd_profile.value('BOM:MATCH_CONFIG');
1619 WriteToLog ('l_match is: ' || to_char(l_match), 1);
1620
1621 l_stmt_num := 20;
1622 IF ((l_match = 2) AND (p_open_lines = 'N')) THEN
1623 -- match is off and open lines not to be upgraded
1624 WriteToLog('+++++++++++++++++++++++++++++++++++++++++++++');
1625 WriteToLog('Match profile is No and you chose not to update existing configurations. No configurations will be updated.');
1626 WriteToLog('+++++++++++++++++++++++++++++++++++++++++++++');
1627 return;
1628 END IF;
1629
1630 WriteToLog ('CTO category id:: '||to_char(p_cat_id), 1);
1631
1632 l_stmt_num := 50;
1633 IF (p_changed_src = 'N') THEN
1634 -- sourcing has not changed
1635 WriteToLog('Sourcing has not changed', 2);
1636
1637 IF p_open_lines = 'Y' THEN
1638 --
1639 -- select all open order lines having config items in l_cat_id with attribute in (2,3)
1640 -- populate into bcol_upg
1641 -- mark as UPG
1642 --
1643 WriteToLog('sql 1', 3);
1644 l_stmt_num := 60;
1645 insert into bom_cto_order_lines_upg
1646 (
1647 ATO_LINE_ID
1648 , BATCH_ID
1649 , BOM_ITEM_TYPE
1650 , COMPONENT_CODE
1651 , COMPONENT_SEQUENCE_ID
1652 , CONFIG_ITEM_ID
1653 , INVENTORY_ITEM_ID
1654 , ITEM_TYPE_CODE
1655 , LINE_ID
1656 , LINK_TO_LINE_ID
1657 , ORDERED_QUANTITY
1658 , ORDER_QUANTITY_UOM
1659 , PARENT_ATO_LINE_ID
1660 , PERFORM_MATCH
1661 , PLAN_LEVEL
1662 , SCHEDULE_SHIP_DATE
1663 , SHIP_FROM_ORG_ID
1664 , TOP_MODEL_LINE_ID
1665 , WIP_SUPPLY_TYPE
1666 , HEADER_ID
1667 , LAST_UPDATE_DATE
1668 , LAST_UPDATED_BY
1669 , CREATION_DATE
1670 , CREATED_BY
1671 , LAST_UPDATE_LOGIN
1672 , REQUEST_ID
1673 , PROGRAM_APPLICATION_ID
1674 , PROGRAM_ID
1675 , PROGRAM_UPDATE_DATE
1676 , OPTION_SPECIFIC
1677 , REUSE_CONFIG
1678 , QTY_PER_PARENT_MODEL
1679 , STATUS
1680 --, SEQUENCE
1681 , config_creation
1682 )
1683 select distinct
1684 bcol2.ATO_LINE_ID
1685 , bcol2.BATCH_ID
1686 , bcol2.BOM_ITEM_TYPE
1687 , bcol2.COMPONENT_CODE
1688 , bcol2.COMPONENT_SEQUENCE_ID
1689 , bcol2.CONFIG_ITEM_ID
1690 , bcol2.INVENTORY_ITEM_ID
1691 , bcol2.ITEM_TYPE_CODE
1692 , bcol2.LINE_ID
1693 , bcol2.LINK_TO_LINE_ID
1694 , bcol2.ORDERED_QUANTITY
1695 , bcol2.ORDER_QUANTITY_UOM
1696 , bcol2.PARENT_ATO_LINE_ID
1697 , bcol2.PERFORM_MATCH --7201878
1698 --, 'N' --bcol2.PERFORM_MATCH
1699 , bcol2.PLAN_LEVEL
1700 , bcol2.SCHEDULE_SHIP_DATE
1701 , bcol2.SHIP_FROM_ORG_ID
1702 , bcol2.TOP_MODEL_LINE_ID
1703 , bcol2.WIP_SUPPLY_TYPE
1704 , bcol2.HEADER_ID
1705 , sysdate --LAST_UPDATE_DATE
1706 , bcol2.LAST_UPDATED_BY
1707 , sysdate --CREATION_DATE
1708 , bcol2.CREATED_BY
1709 , bcol2.LAST_UPDATE_LOGIN
1710 , bcol2.REQUEST_ID
1711 , bcol2.PROGRAM_APPLICATION_ID
1712 , bcol2.PROGRAM_ID
1713 , sysdate --PROGRAM_UPDATE_DATE
1714 , bcol2.OPTION_SPECIFIC
1715 , 'N' --bcol2.REUSE_CONFIG
1716 , bcol2.QTY_PER_PARENT_MODEL
1717 , 'UPG' --STATUS
1718 --, bcol2.SEQUENCE
1719 , nvl(msi.config_orgs, '1')
1720 from bom_cto_order_lines bcol1
1721 , bom_cto_order_lines bcol2
1722 , oe_order_lines_all oel
1723 , mtl_system_items msi
1724 , mtl_item_categories mcat
1725 -- select entire configuration
1726 where bcol2.ato_line_id = bcol1.ato_line_id
1727 and bcol1.config_item_id is not null
1728 -- for configs whose models are in CTO category
1729 and mcat.inventory_item_id = bcol1.inventory_item_id
1730 and mcat.organization_id = bcol1.ship_from_org_id
1731 and mcat.category_id = p_cat_id
1732 -- for configs whose models have attr=2,3
1733 and bcol1.inventory_item_id = msi.inventory_item_id
1734 and bcol1.ship_from_org_id = msi.organization_id
1735 and nvl(msi.config_orgs, '1') in ('2', '3')
1736 -- and are on open order lines
1737 and bcol1.line_id = oel.ato_line_id /* changed line_id to ato_line_id */
1738 and nvl(oel.open_flag, 'N') = 'Y'
1739 and oel.item_type_code = 'CONFIG' ; /* added check for config linked to oe */
1740
1741 WriteToLog('Rows inserted::'|| to_char(sql%rowcount), 3);
1742
1743 ELSE /* p_open_lines = 'N' */
1744 --
1745 -- select all open order lines having canned config items with attribute = 3
1746 -- populate into bcol_upg
1747 -- mark as UPG
1748 --
1749 WriteToLog('sql 2', 3);
1750 l_stmt_num := 70;
1751 insert into bom_cto_order_lines_upg
1752 (
1753 ATO_LINE_ID
1754 , BATCH_ID
1755 , BOM_ITEM_TYPE
1756 , COMPONENT_CODE
1757 , COMPONENT_SEQUENCE_ID
1758 , CONFIG_ITEM_ID
1759 , INVENTORY_ITEM_ID
1760 , ITEM_TYPE_CODE
1761 , LINE_ID
1762 , LINK_TO_LINE_ID
1763 , ORDERED_QUANTITY
1764 , ORDER_QUANTITY_UOM
1765 , PARENT_ATO_LINE_ID
1766 , PERFORM_MATCH
1767 , PLAN_LEVEL
1768 , SCHEDULE_SHIP_DATE
1769 , SHIP_FROM_ORG_ID
1770 , TOP_MODEL_LINE_ID
1771 , WIP_SUPPLY_TYPE
1772 , HEADER_ID
1773 , LAST_UPDATE_DATE
1774 , LAST_UPDATED_BY
1775 , CREATION_DATE
1776 , CREATED_BY
1777 , LAST_UPDATE_LOGIN
1778 , REQUEST_ID
1779 , PROGRAM_APPLICATION_ID
1780 , PROGRAM_ID
1781 , PROGRAM_UPDATE_DATE
1782 , OPTION_SPECIFIC
1783 , REUSE_CONFIG
1784 , QTY_PER_PARENT_MODEL
1785 , STATUS
1786 --, SEQUENCE
1787 , config_creation
1788 )
1789 select distinct
1790 bcol2.ATO_LINE_ID
1791 , bcol2.BATCH_ID
1792 , bcol2.BOM_ITEM_TYPE
1793 , bcol2.COMPONENT_CODE
1794 , bcol2.COMPONENT_SEQUENCE_ID
1795 , bcol2.CONFIG_ITEM_ID
1796 , bcol2.INVENTORY_ITEM_ID
1797 , bcol2.ITEM_TYPE_CODE
1798 , bcol2.LINE_ID
1799 , bcol2.LINK_TO_LINE_ID
1800 , bcol2.ORDERED_QUANTITY
1801 , bcol2.ORDER_QUANTITY_UOM
1802 , bcol2.PARENT_ATO_LINE_ID
1803 , bcol2.PERFORM_MATCH --7201878
1804 --, 'Y' --bcol2.PERFORM_MATCH /* Sushant made changes to identify matched items */
1805 , bcol2.PLAN_LEVEL
1806 , bcol2.SCHEDULE_SHIP_DATE
1807 , bcol2.SHIP_FROM_ORG_ID
1808 , bcol2.TOP_MODEL_LINE_ID
1809 , bcol2.WIP_SUPPLY_TYPE
1810 , bcol2.HEADER_ID
1811 , sysdate --LAST_UPDATE_DATE
1812 , bcol2.LAST_UPDATED_BY
1813 , sysdate --CREATION_DATE
1814 , bcol2.CREATED_BY
1815 , bcol2.LAST_UPDATE_LOGIN
1816 , bcol2.REQUEST_ID
1817 , bcol2.PROGRAM_APPLICATION_ID
1818 , bcol2.PROGRAM_ID
1819 , sysdate --PROGRAM_UPDATE_DATE
1820 , bcol2.OPTION_SPECIFIC
1821 , 'N' --bcol2.REUSE_CONFIG
1822 , bcol2.QTY_PER_PARENT_MODEL
1823 , 'UPG' --STATUS
1824 --, bcol2.SEQUENCE
1825 , nvl(msi.config_orgs, '1')
1826 from bom_cto_order_lines bcol1
1827 , bom_cto_order_lines bcol2
1828 , bom_ato_configurations bac
1829 , oe_order_lines_all oel
1830 , mtl_system_items msi
1831 , mtl_item_categories mcat
1832 -- base model has item attr = 3
1833 where bac.base_model_id = msi.inventory_item_id
1834 and bac.organization_id = msi.organization_id
1835 and nvl(msi.config_orgs, '1') = '3'
1836 -- and exists in bcol
1837 and bac.config_item_id = bcol1.config_item_id
1838 -- for configs whose models are in CTO category
1839 and mcat.inventory_item_id = bcol1.inventory_item_id
1840 and mcat.organization_id = bcol1.ship_from_org_id
1841 and mcat.category_id = p_cat_id
1842 -- on open order lines
1843 and bcol1.line_id = oel.ato_line_id /* changed line_id to ato_line_id */
1844 and nvl(oel.open_flag, 'N') = 'Y'
1845 and bcol2.ato_line_id = bcol1.ato_line_id
1846 and oel.item_type_code = 'CONFIG' ; /* added check for config linked to oe */
1847 WriteToLog('Rows inserted::'|| to_char(sql%rowcount), 3);
1848
1849 END IF; /* p_open_lines = 'Y' */
1850
1851 IF l_match = 1 THEN
1852 --
1853 -- select additional config items with attribute = 3 on closed order lines
1854 -- populate into bcol_upg
1855 -- mark as UPG
1856 --
1857 WriteToLog('sql 3', 3);
1858 l_stmt_num := 80;
1859 insert into bom_cto_order_lines_upg
1860 (
1861 ATO_LINE_ID
1862 , BATCH_ID
1863 , BOM_ITEM_TYPE
1864 , COMPONENT_CODE
1865 , COMPONENT_SEQUENCE_ID
1866 , CONFIG_ITEM_ID
1867 , INVENTORY_ITEM_ID
1868 , ITEM_TYPE_CODE
1869 , LINE_ID
1870 , LINK_TO_LINE_ID
1871 , ORDERED_QUANTITY
1872 , ORDER_QUANTITY_UOM
1873 , PARENT_ATO_LINE_ID
1874 , PERFORM_MATCH
1875 , PLAN_LEVEL
1876 , SCHEDULE_SHIP_DATE
1877 , SHIP_FROM_ORG_ID
1878 , TOP_MODEL_LINE_ID
1879 , WIP_SUPPLY_TYPE
1880 , HEADER_ID
1881 , LAST_UPDATE_DATE
1882 , LAST_UPDATED_BY
1883 , CREATION_DATE
1884 , CREATED_BY
1885 , LAST_UPDATE_LOGIN
1886 , REQUEST_ID
1887 , PROGRAM_APPLICATION_ID
1888 , PROGRAM_ID
1889 , PROGRAM_UPDATE_DATE
1890 , OPTION_SPECIFIC
1891 , REUSE_CONFIG
1892 , QTY_PER_PARENT_MODEL
1893 , STATUS
1894 --, SEQUENCE
1895 , config_creation
1896 )
1897 select /*+ ORDERED*/ distinct --Bugfix 6617686 Added a hint
1898 bcol.ATO_LINE_ID
1899 , bcol.BATCH_ID
1900 , bcol.BOM_ITEM_TYPE
1901 , bcol.COMPONENT_CODE
1902 , bcol.COMPONENT_SEQUENCE_ID
1903 , bcol.CONFIG_ITEM_ID
1904 , bcol.INVENTORY_ITEM_ID
1905 , bcol.ITEM_TYPE_CODE
1906 , bcol.LINE_ID
1907 , bcol.LINK_TO_LINE_ID
1908 , bcol.ORDERED_QUANTITY
1909 , bcol.ORDER_QUANTITY_UOM
1910 , bcol.PARENT_ATO_LINE_ID
1911 , bcol.PERFORM_MATCH --7201878
1912 --, 'N' --bcol.PERFORM_MATCH
1913 , bcol.PLAN_LEVEL
1914 , bcol.SCHEDULE_SHIP_DATE
1915 , bcol.SHIP_FROM_ORG_ID
1916 , bcol.TOP_MODEL_LINE_ID
1917 , bcol.WIP_SUPPLY_TYPE
1918 , bcol.HEADER_ID
1919 , sysdate --LAST_UPDATE_DATE
1920 , bcol.LAST_UPDATED_BY
1921 , sysdate --CREATION_DATE
1922 , bcol.CREATED_BY
1923 , bcol.LAST_UPDATE_LOGIN
1924 , bcol.REQUEST_ID
1925 , bcol.PROGRAM_APPLICATION_ID
1926 , 99 -- matched item on closed line
1927 , bcol.PROGRAM_UPDATE_DATE
1928 , bcol.OPTION_SPECIFIC
1929 , 'N' --bcol.REUSE_CONFIG
1930 , bcol.QTY_PER_PARENT_MODEL
1931 , 'UPG' --STATUS
1932 --, bcol.SEQUENCE
1933 , nvl(msi.config_orgs, '1')
1934 from mtl_item_categories mcat --Bugfix 6617686 Changed the order of tables
1935 , mtl_system_items msi
1936 , bom_ato_configurations bac
1937 , bom_cto_order_lines bcol
1938 -- base model has item attr = 3
1939 where bac.base_model_id = msi.inventory_item_id
1940 and bac.organization_id = msi.organization_id
1941 and nvl(msi.config_orgs, '1') = '3'
1942 -- and base model is in CTO category
1943 and mcat.inventory_item_id = msi.inventory_item_id
1944 and mcat.organization_id = msi.organization_id
1945 and mcat.category_id = p_cat_id
1946 -- and not already in bcol_upg
1947 and NOT EXISTS
1948 (select 'exists'
1949 from bom_cto_order_lines_upg bcolu
1950 where bcolu.config_item_id = bac.config_item_id)
1951 -- select first ato_line_id in bcol
1952 and bcol.ato_line_id =
1953 (select bcol1.ato_line_id
1954 from bom_cto_order_lines bcol1
1955 where bcol1.config_item_id = bac.config_item_id
1956 -- pick up only if config is at top level
1957 and bcol1.line_id = bcol1.ato_line_id
1958 and rownum = 1)
1959 ;
1960 WriteToLog('Rows inserted::'|| to_char(sql%rowcount), 3);
1961
1962 --
1963 -- select all individual (not top level) config items in bac having item attribute = 3 and not in bcol_upg
1964 -- populate into bcol_upg from bcol or bac
1965 -- mark as UPG
1966 -- mark with program_id = 99 to indicate that it was populated from bac
1967 --
1968 WriteToLog('sql 4', 3);
1969 l_stmt_num := 90;
1970 FOR v_bac IN c_bac(p_cat_id) LOOP
1971 --
1972 -- check to see if not already populated as part of parent
1973 --
1974 WriteToLog('Item being populated from bac::'|| to_char(v_bac.config_id), 4);
1975 BEGIN
1976 select 'Y'
1977 into l_exists
1978 from bom_cto_order_lines_upg
1979 where config_item_id = v_bac.config_id
1980 and rownum = 1;
1981 WriteToLog('Item::'|| to_char(v_bac.config_id)||' already exists in bcolu', 4);
1982
1983 EXCEPTION
1984 WHEN no_data_found THEN
1985 WriteToLog('Populating from bac Item::'|| to_char(v_bac.config_id), 4);
1986 populate_bcolu_from_bac(
1987 v_bac.config_id
1988 , l_return_status
1989 , l_msg_count
1990 , l_msg_data);
1991 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1992 WriteToLog('ERROR: Populate_bcolu_from_bac returned unexp error');
1993 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1994 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
1995 WriteToLog('ERROR: Populate_bcolu_from_bac returned expected error');
1996 raise FND_API.G_EXC_ERROR;
1997 END IF;
1998 END; -- sub block
1999 END LOOP;
2000
2001 END IF; /* l_match = 1 */
2002 ELSE
2003 WriteToLog('Sourcing has changed', 2);
2004
2005 -- srcing changed
2006 IF p_open_lines = 'Y' THEN
2007 --
2008 -- select all open order lines
2009 -- populate into bcol_upg
2010 -- mark as UPG
2011 --
2012 WriteToLog('sql 5', 3);
2013 l_stmt_num := 100;
2014 insert into bom_cto_order_lines_upg
2015 (
2016 ATO_LINE_ID
2017 , BATCH_ID
2018 , BOM_ITEM_TYPE
2019 , COMPONENT_CODE
2020 , COMPONENT_SEQUENCE_ID
2021 , CONFIG_ITEM_ID
2022 , INVENTORY_ITEM_ID
2023 , ITEM_TYPE_CODE
2024 , LINE_ID
2025 , LINK_TO_LINE_ID
2026 , ORDERED_QUANTITY
2027 , ORDER_QUANTITY_UOM
2028 , PARENT_ATO_LINE_ID
2029 , PERFORM_MATCH
2030 , PLAN_LEVEL
2031 , SCHEDULE_SHIP_DATE
2032 , SHIP_FROM_ORG_ID
2033 , TOP_MODEL_LINE_ID
2034 , WIP_SUPPLY_TYPE
2035 , HEADER_ID
2036 , LAST_UPDATE_DATE
2037 , LAST_UPDATED_BY
2038 , CREATION_DATE
2039 , CREATED_BY
2040 , LAST_UPDATE_LOGIN
2041 , REQUEST_ID
2042 , PROGRAM_APPLICATION_ID
2043 , PROGRAM_ID
2044 , PROGRAM_UPDATE_DATE
2045 , OPTION_SPECIFIC
2046 , REUSE_CONFIG
2047 , QTY_PER_PARENT_MODEL
2048 , STATUS
2049 --, SEQUENCE
2050 , CONFIG_CREATION
2051 )
2052 select distinct
2053 bcol.ATO_LINE_ID
2054 , bcol.BATCH_ID
2055 , bcol.BOM_ITEM_TYPE
2056 , bcol.COMPONENT_CODE
2057 , bcol.COMPONENT_SEQUENCE_ID
2058 , bcol.CONFIG_ITEM_ID
2059 , bcol.INVENTORY_ITEM_ID
2060 , bcol.ITEM_TYPE_CODE
2061 , bcol.LINE_ID
2062 , bcol.LINK_TO_LINE_ID
2063 , bcol.ORDERED_QUANTITY
2064 , bcol.ORDER_QUANTITY_UOM
2065 , bcol.PARENT_ATO_LINE_ID
2066 , bcol.PERFORM_MATCH --7201878
2067 --, 'N' --bcol.PERFORM_MATCH
2068 , bcol.PLAN_LEVEL
2069 , bcol.SCHEDULE_SHIP_DATE
2070 , bcol.SHIP_FROM_ORG_ID
2071 , bcol.TOP_MODEL_LINE_ID
2072 , bcol.WIP_SUPPLY_TYPE
2073 , bcol.HEADER_ID
2074 , sysdate --LAST_UPDATE_DATE
2075 , bcol.LAST_UPDATED_BY
2076 , sysdate --CREATION_DATE
2077 , bcol.CREATED_BY
2078 , bcol.LAST_UPDATE_LOGIN
2079 , bcol.REQUEST_ID
2080 , bcol.PROGRAM_APPLICATION_ID
2081 , bcol.PROGRAM_ID
2082 , sysdate --PROGRAM_UPDATE_DATE
2083 , bcol.OPTION_SPECIFIC
2084 , 'N' --bcol.REUSE_CONFIG
2085 , bcol.QTY_PER_PARENT_MODEL
2086 , 'UPG' --STATUS
2087 --, bcol.SEQUENCE
2088 , nvl(msi2.CONFIG_ORGS, '1')
2089 from bom_cto_order_lines bcol
2090 , oe_order_lines_all oel
2091 , mtl_system_items msi2
2092 -- select all configs on open order lines
2093 where bcol.ato_line_id = oel.ato_line_id
2094 and bcol.inventory_item_id = msi2.inventory_item_id
2095 and bcol.ship_from_org_id = msi2.organization_id
2096 and nvl(oel.open_flag, 'N') = 'Y'
2097 and oel.ato_line_id in -- bug 6617686 connect using oel rather than bcol to get better filtering
2098 (select /*+ leading(MCAT) */ distinct bcol2.ato_line_id --Bugfix 6617686 Added a hint
2099 from mtl_item_categories mcat --Bugfix 6617686 Changed the order of tables
2100 , mtl_system_items msi
2101 , bom_cto_order_lines bcol2
2102 where bcol2.config_item_id is not null
2103 and bcol2.inventory_item_id = msi.inventory_item_id
2104 and bcol2.ship_from_org_id = msi.organization_id
2105 -- and base model is in CTO category
2106 and mcat.inventory_item_id = msi.inventory_item_id
2107 and mcat.organization_id = msi.organization_id
2108 and mcat.category_id = p_cat_id)
2109 and oel.item_type_code = 'CONFIG' ;/* original bug detected, added condition for bug 3599397 */
2110
2111 WriteToLog('Rows inserted::'|| to_char(sql%rowcount), 3);
2112
2113 ELSE /* p_open_lines = 'N' */
2114 --
2115 -- select all open order lines having canned config items with attribute = 3 and assigned to CTO category
2116 -- populate into bcol_upg
2117 -- mark as UPG
2118 -- TEST THIS!!
2119 --
2120 WriteToLog('sql 6', 3);
2121 l_stmt_num := 110;
2122 insert into bom_cto_order_lines_upg
2123 (
2124 ATO_LINE_ID
2125 , BATCH_ID
2126 , BOM_ITEM_TYPE
2127 , COMPONENT_CODE
2128 , COMPONENT_SEQUENCE_ID
2129 , CONFIG_ITEM_ID
2130 , INVENTORY_ITEM_ID
2131 , ITEM_TYPE_CODE
2132 , LINE_ID
2133 , LINK_TO_LINE_ID
2134 , ORDERED_QUANTITY
2135 , ORDER_QUANTITY_UOM
2136 , PARENT_ATO_LINE_ID
2137 , PERFORM_MATCH
2138 , PLAN_LEVEL
2139 , SCHEDULE_SHIP_DATE
2140 , SHIP_FROM_ORG_ID
2141 , TOP_MODEL_LINE_ID
2142 , WIP_SUPPLY_TYPE
2143 , HEADER_ID
2144 , LAST_UPDATE_DATE
2145 , LAST_UPDATED_BY
2146 , CREATION_DATE
2147 , CREATED_BY
2148 , LAST_UPDATE_LOGIN
2149 , REQUEST_ID
2150 , PROGRAM_APPLICATION_ID
2151 , PROGRAM_ID
2152 , PROGRAM_UPDATE_DATE
2153 , OPTION_SPECIFIC
2154 , REUSE_CONFIG
2155 , QTY_PER_PARENT_MODEL
2156 , STATUS
2157 --, SEQUENCE
2158 , CONFIG_CREATION
2159 )
2160 select distinct
2161 bcol2.ATO_LINE_ID
2162 , bcol2.BATCH_ID
2163 , bcol2.BOM_ITEM_TYPE
2164 , bcol2.COMPONENT_CODE
2165 , bcol2.COMPONENT_SEQUENCE_ID
2166 , bcol2.CONFIG_ITEM_ID
2167 , bcol2.INVENTORY_ITEM_ID
2168 , bcol2.ITEM_TYPE_CODE
2169 , bcol2.LINE_ID
2170 , bcol2.LINK_TO_LINE_ID
2171 , bcol2.ORDERED_QUANTITY
2172 , bcol2.ORDER_QUANTITY_UOM
2173 , bcol2.PARENT_ATO_LINE_ID
2174 , bcol2.PERFORM_MATCH --7201878
2175 --, 'Y' --bcol2.PERFORM_MATCH /* Sushant made changes to identify matched items */
2176 , bcol2.PLAN_LEVEL
2177 , bcol2.SCHEDULE_SHIP_DATE
2178 , bcol2.SHIP_FROM_ORG_ID
2179 , bcol2.TOP_MODEL_LINE_ID
2180 , bcol2.WIP_SUPPLY_TYPE
2181 , bcol2.HEADER_ID
2182 , sysdate --LAST_UPDATE_DATE
2183 , bcol2.LAST_UPDATED_BY
2184 , sysdate --CREATION_DATE
2185 , bcol2.CREATED_BY
2186 , bcol2.LAST_UPDATE_LOGIN
2187 , bcol2.REQUEST_ID
2188 , bcol2.PROGRAM_APPLICATION_ID
2189 , bcol2.PROGRAM_ID
2190 , sysdate --PROGRAM_UPDATE_DATE
2191 , bcol2.OPTION_SPECIFIC
2192 , 'N' --bcol2.REUSE_CONFIG
2193 , bcol2.QTY_PER_PARENT_MODEL
2194 , 'UPG' --STATUS
2195 --, bcol2.SEQUENCE
2196 , nvl(msi.CONFIG_ORGS, '1')
2197 from bom_cto_order_lines bcol1
2198 , bom_cto_order_lines bcol2
2199 , bom_ato_configurations bac
2200 , oe_order_lines_all oel
2201 , mtl_system_items msi
2202 , mtl_item_categories mcat
2203 -- base model has item attr = 3
2204 where bac.base_model_id = msi.inventory_item_id
2205 and bac.organization_id = msi.organization_id
2206 and nvl(msi.config_orgs, '1') = '3'
2207 -- and exists in bcol
2208 and bac.config_item_id = bcol1.config_item_id
2209 -- on open order lines
2210 and bcol1.line_id = oel.line_id
2211 and nvl(oel.open_flag, 'N') = 'Y'
2212 and bcol2.ato_line_id = bcol1.ato_line_id
2213 -- and base model is in CTO category
2214 and mcat.inventory_item_id = msi.inventory_item_id
2215 and mcat.organization_id = msi.organization_id
2216 and mcat.category_id = p_cat_id;
2217
2218 WriteToLog('Rows inserted::'|| to_char(sql%rowcount), 3);
2219
2220 END IF; /* p_open_lines = 'Y' */
2221
2222 IF l_match = 1 THEN
2223 --
2224 -- select additional TOP LEVEL config items with attribute = 3 on closed order lines
2225 -- populate into bcol_upg
2226 -- mark as UPG
2227 --
2228 WriteToLog('sql 7', 3);
2229 l_stmt_num := 120;
2230 insert into bom_cto_order_lines_upg
2231 (
2232 ATO_LINE_ID
2233 , BATCH_ID
2234 , BOM_ITEM_TYPE
2235 , COMPONENT_CODE
2236 , COMPONENT_SEQUENCE_ID
2237 , CONFIG_ITEM_ID
2238 , INVENTORY_ITEM_ID
2239 , ITEM_TYPE_CODE
2240 , LINE_ID
2241 , LINK_TO_LINE_ID
2242 , ORDERED_QUANTITY
2243 , ORDER_QUANTITY_UOM
2244 , PARENT_ATO_LINE_ID
2245 , PERFORM_MATCH
2246 , PLAN_LEVEL
2247 , SCHEDULE_SHIP_DATE
2248 , SHIP_FROM_ORG_ID
2249 , TOP_MODEL_LINE_ID
2250 , WIP_SUPPLY_TYPE
2251 , HEADER_ID
2252 , LAST_UPDATE_DATE
2253 , LAST_UPDATED_BY
2254 , CREATION_DATE
2255 , CREATED_BY
2256 , LAST_UPDATE_LOGIN
2257 , REQUEST_ID
2258 , PROGRAM_APPLICATION_ID
2259 , PROGRAM_ID
2260 , PROGRAM_UPDATE_DATE
2261 , OPTION_SPECIFIC
2262 , REUSE_CONFIG
2263 , QTY_PER_PARENT_MODEL
2264 , STATUS
2265 --, SEQUENCE
2266 , CONFIG_CREATION
2267 )
2268 select /*+ leading(MCAT) */ distinct
2269 bcol.ATO_LINE_ID
2270 , bcol.BATCH_ID
2271 , bcol.BOM_ITEM_TYPE
2272 , bcol.COMPONENT_CODE
2273 , bcol.COMPONENT_SEQUENCE_ID
2274 , bcol.CONFIG_ITEM_ID
2275 , bcol.INVENTORY_ITEM_ID
2276 , bcol.ITEM_TYPE_CODE
2277 , bcol.LINE_ID
2278 , bcol.LINK_TO_LINE_ID
2279 , bcol.ORDERED_QUANTITY
2280 , bcol.ORDER_QUANTITY_UOM
2281 , bcol.PARENT_ATO_LINE_ID
2282 , bcol.PERFORM_MATCH --7201878
2283 --, 'Y' --bcol.PERFORM_MATCH /* Sushant made changes to identify matched items */
2284 , bcol.PLAN_LEVEL
2285 , bcol.SCHEDULE_SHIP_DATE
2286 , bcol.SHIP_FROM_ORG_ID
2287 , bcol.TOP_MODEL_LINE_ID
2288 , bcol.WIP_SUPPLY_TYPE
2289 , bcol.HEADER_ID
2290 , sysdate --LAST_UPDATE_DATE
2291 , bcol.LAST_UPDATED_BY
2292 , sysdate --CREATION_DATE
2293 , bcol.CREATED_BY
2294 , bcol.LAST_UPDATE_LOGIN
2295 , bcol.REQUEST_ID
2296 , bcol.PROGRAM_APPLICATION_ID
2297 , 99 -- matched item on closed line
2298 , bcol.PROGRAM_UPDATE_DATE
2299 , bcol.OPTION_SPECIFIC
2300 , 'N' --bcol.REUSE_CONFIG
2301 , bcol.QTY_PER_PARENT_MODEL
2302 , 'UPG' --STATUS
2303 --, bcol.SEQUENCE
2304 , nvl(msi.CONFIG_ORGS, '1')
2305 from bom_ato_configurations bac
2306 , bom_cto_order_lines bcol
2307 , mtl_system_items msi
2308 , mtl_item_categories mcat
2309 -- base model has item attr = 3
2310 where bac.base_model_id = msi.inventory_item_id
2311 and bac.organization_id = msi.organization_id
2312 and nvl(msi.config_orgs, '1') = '3'
2313 -- and base model is in CTO category
2314 and mcat.inventory_item_id = msi.inventory_item_id
2315 and mcat.organization_id = msi.organization_id
2316 and mcat.category_id = p_cat_id
2317 -- and is top parent with attribute 3
2318 and NOT EXISTS
2319 (select 'exists'
2320 from bom_ato_configurations bac2
2321 , mtl_system_items msi2
2322 where bac.config_item_id = bac2.component_item_id
2323 and bac2.base_model_id = msi2.inventory_item_id
2324 and bac2.organization_id = msi2.organization_id
2325 and nvl(msi2.config_orgs, '1') = '3')
2326 -- and not already in bcol_upg
2327 and NOT EXISTS
2328 (select 'exists'
2329 from bom_cto_order_lines_upg bcolu
2330 where bcolu.config_item_id = bac.config_item_id)
2331 -- select first ato_line_id in bcol
2332 and bcol.ato_line_id =
2333 (select bcol1.ato_line_id
2334 from bom_cto_order_lines bcol1
2335 where bcol1.config_item_id = bac.config_item_id
2336 -- pick up only if config is at top level
2337 and bcol1.line_id = bcol1.ato_line_id
2338 and rownum = 1)
2339 ;
2340
2341 WriteToLog('Rows inserted::'|| to_char(sql%rowcount), 3);
2342
2343 --
2344 -- select all top level config items in bac having item attribute = 3 and not in bcol_upg
2345 -- populate into bcol_upg from bcol or bac
2346 -- mark as UPG
2347 -- mark with program_id = 99 to indicate that it was populated from bac
2348 --
2349 WriteToLog('sql 8', 3);
2350 l_stmt_num := 130;
2351 FOR v_bac_top IN c_bac_top(p_cat_id) LOOP
2352 WriteToLog('Item being populated from bac::'|| to_char(v_bac_top.config_id), 4);
2353 BEGIN
2354 select 'exists'
2355 into l_exists
2356 from bom_cto_order_lines_upg bcolu
2357 where bcolu.config_item_id = v_bac_top.config_id
2358 and rownum = 1;
2359 WriteToLog('Item::'|| to_char(v_bac_top.config_id)||' already exists in bcolu', 4);
2360 EXCEPTION
2361 WHEN NO_DATA_FOUND THEN
2362 WriteToLog('Populating from bac Item::'|| to_char(v_bac_top.config_id), 4);
2363 populate_bcolu_from_bac(
2364 v_bac_top.config_id
2365 , l_return_status
2366 , l_msg_count
2367 , l_msg_data);
2368 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2369 WriteToLog('ERROR: Populate_bcolu_from_bac returned unexp error');
2370 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2371 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2372 WriteToLog('ERROR: Populate_bcolu_from_bac returned expected error');
2373 raise FND_API.G_EXC_ERROR;
2374 END IF;
2375 END; -- sub-block
2376 END LOOP;
2377
2378 END IF; /* l_match = 1 */
2379
2380 END IF; /* ms or oss not defined */
2381
2382 WriteToLog('Done populate_cat_models.', 1);
2383
2384 EXCEPTION
2385 WHEN FND_API.G_EXC_ERROR THEN
2386 WriteToLog('ERROR: Expected error in Populate_Cat_Models::'||to_char(l_stmt_num)||'::'||sqlerrm);
2387 x_return_status := FND_API.G_RET_STS_ERROR;
2388 CTO_MSG_PUB.Count_And_Get
2389 (p_msg_count => x_msg_count
2390 ,p_msg_data => x_msg_data
2391 );
2392
2393 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2394 WriteToLog('ERROR: Unexpected error in Populate_Cat_Models::'||to_char(l_stmt_num)||'::'||sqlerrm);
2395 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2396 CTO_MSG_PUB.Count_And_Get
2397 (p_msg_count => x_msg_count
2398 ,p_msg_data => x_msg_data
2399 );
2400
2401 WHEN OTHERS THEN
2402 WriteToLog('ERROR: Others error in Populate_Cat_Models::'||to_char(l_stmt_num)||'::'||sqlerrm);
2403 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2404 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2405 FND_MSG_PUB.Add_Exc_Msg
2406 (G_PKG_NAME
2407 ,'Populate_Cat_Models');
2408 END IF;
2409 CTO_MSG_PUB.Count_And_Get
2410 (p_msg_count => x_msg_count
2411 ,p_msg_data => x_msg_data
2412 );
2413
2414 END Populate_Cat_Models;
2415
2416
2417 PROCEDURE populate_config(
2418 p_changed_src IN varchar2,
2419 p_open_lines IN varchar2,
2420 p_config_id IN number,
2421 x_return_status out NOCOPY varchar2,
2422 x_msg_count out NOCOPY number,
2423 x_msg_data out NOCOPY varchar2) IS
2424
2425 l_match NUMBER;
2426 l_attribute NUMBER;
2427 l_exists varchar2(1);
2428 l_return_status varchar2(1);
2429 l_msg_count number;
2430 l_msg_data varchar2(240);
2431 l_stmt_num number := 0;
2432
2433 BEGIN
2434 WriteToLog ('Entering populate_config', 1);
2435 WriteToLog ('p_config_id is: ' || to_char(p_config_id), 1);
2436 x_return_status := FND_API.G_RET_STS_SUCCESS;
2437 l_stmt_num := 10;
2438
2439 l_match := fnd_profile.value('BOM:MATCH_CONFIG');
2440 WriteToLog ('l_match is: ' || to_char(l_match), 1);
2441 l_stmt_num := 20;
2442
2443 IF ((l_match = 2) AND (p_open_lines = 'N')) THEN
2444 -- match is off and open lines not to be upgraded
2445 WriteToLog('+++++++++++++++++++++++++++++++++++++++++++++');
2446 WriteToLog('Match profile is No and you chose not to update existing configurations. No configurations will be updated.');
2447 WriteToLog('+++++++++++++++++++++++++++++++++++++++++++++');
2448 return;
2449 END IF;
2450
2451 -- get item attribute for this config
2452 l_stmt_num := 40;
2453 select nvl(msi.config_orgs, '1')
2454 into l_attribute
2455 from mtl_system_items msi
2456 where msi.inventory_item_id =
2457 (select msi2.base_item_id
2458 from mtl_system_items msi2
2459 where msi2.inventory_item_id = p_config_id
2460 and rownum = 1)
2461 and rownum = 1;
2462
2463 WriteToLog ('Config_creation is: ' || to_char(l_attribute), 1);
2464
2465 IF (p_changed_src = 'N' and l_attribute = 1) THEN
2466 -- no changed sourcing and attribute is 1
2467 WriteToLog('Sourcing has not changed. Item attribute is 1.Configuration item will not be processed.', 1);
2468 return;
2469 ELSE
2470 IF p_open_lines = 'Y' THEN
2471 --
2472 -- select all open order lines having this config item
2473 -- populate into bcol_upg
2474 -- mark as UPG
2475 --
2476 WriteToLog('sql 1', 3);
2477 l_stmt_num := 50;
2478 insert into bom_cto_order_lines_upg
2479 (
2480 ATO_LINE_ID
2481 , BATCH_ID
2482 , BOM_ITEM_TYPE
2483 , COMPONENT_CODE
2484 , COMPONENT_SEQUENCE_ID
2485 , CONFIG_ITEM_ID
2486 , INVENTORY_ITEM_ID
2487 , ITEM_TYPE_CODE
2488 , LINE_ID
2489 , LINK_TO_LINE_ID
2490 , ORDERED_QUANTITY
2491 , ORDER_QUANTITY_UOM
2492 , PARENT_ATO_LINE_ID
2493 , PERFORM_MATCH
2494 , PLAN_LEVEL
2495 , SCHEDULE_SHIP_DATE
2496 , SHIP_FROM_ORG_ID
2497 , TOP_MODEL_LINE_ID
2498 , WIP_SUPPLY_TYPE
2499 , HEADER_ID
2500 , LAST_UPDATE_DATE
2501 , LAST_UPDATED_BY
2502 , CREATION_DATE
2503 , CREATED_BY
2504 , LAST_UPDATE_LOGIN
2505 , REQUEST_ID
2506 , PROGRAM_APPLICATION_ID
2507 , PROGRAM_ID
2508 , PROGRAM_UPDATE_DATE
2509 , OPTION_SPECIFIC
2510 , REUSE_CONFIG
2511 , QTY_PER_PARENT_MODEL
2512 , STATUS
2513 --, SEQUENCE
2514 , CONFIG_CREATION
2515 )
2516 select distinct
2517 bcol2.ATO_LINE_ID
2518 , bcol2.BATCH_ID
2519 , bcol2.BOM_ITEM_TYPE
2520 , bcol2.COMPONENT_CODE
2521 , bcol2.COMPONENT_SEQUENCE_ID
2522 , bcol2.CONFIG_ITEM_ID
2523 , bcol2.INVENTORY_ITEM_ID
2524 , bcol2.ITEM_TYPE_CODE
2525 , bcol2.LINE_ID
2526 , bcol2.LINK_TO_LINE_ID
2527 , bcol2.ORDERED_QUANTITY
2528 , bcol2.ORDER_QUANTITY_UOM
2529 , bcol2.PARENT_ATO_LINE_ID
2530 , bcol2.perform_match -- Sushant Changed as part of bug 3472654 'N'
2531 , bcol2.PLAN_LEVEL
2532 , bcol2.SCHEDULE_SHIP_DATE
2533 , bcol2.SHIP_FROM_ORG_ID
2534 , bcol2.TOP_MODEL_LINE_ID
2535 , bcol2.WIP_SUPPLY_TYPE
2536 , bcol2.HEADER_ID
2537 , sysdate --LAST_UPDATE_DATE
2538 , bcol2.LAST_UPDATED_BY
2539 , sysdate --CREATION_DATE
2540 , bcol2.CREATED_BY
2541 , bcol2.LAST_UPDATE_LOGIN
2542 , bcol2.REQUEST_ID
2543 , bcol2.PROGRAM_APPLICATION_ID
2544 , bcol2.PROGRAM_ID
2545 , sysdate --PROGRAM_UPDATE_DATE
2546 , bcol2.OPTION_SPECIFIC
2547 , 'N' --bcol2.REUSE_CONFIG
2548 , bcol2.QTY_PER_PARENT_MODEL
2549 , 'UPG' --STATUS
2550 --, bcol2.SEQUENCE
2551 , nvl(msi.config_orgs, '1')
2552 from bom_cto_order_lines bcol1
2553 , bom_cto_order_lines bcol2
2554 , oe_order_lines_all oel
2555 , mtl_system_items msi
2556 , oe_order_lines_all oel2 --bugfix 3371155
2557 -- select entire configuration
2558 where bcol2.ato_line_id = bcol1.ato_line_id
2559 -- to get item attribute
2560 and msi.inventory_item_id = bcol2.inventory_item_id
2561 and msi.organization_id = bcol2.ship_from_org_id
2562 -- for this config
2563 and bcol1.config_item_id = p_config_id
2564 -- and are on open order lines
2565 and bcol1.line_id = oel.line_id
2566 and nvl(oel.open_flag, 'N') = 'Y'
2567 --bugfix 3371155
2568 and bcol1.ato_line_id = oel2.ato_line_id
2569 and oel2.item_type_code = 'CONFIG'
2570 --end 3371155
2571 ;
2572
2573 WriteToLog('Rows inserted::'|| to_char(sql%rowcount), 3);
2574
2575
2576 ELSE /* p_open_lines = 'N' */
2577 --
2578 -- select all open order lines having this canned config item, only if it has attribute = 3
2579 -- populate into bcol_upg
2580 -- mark as UPG
2581 --
2582 IF (l_attribute = 3) THEN
2583 WriteToLog('sql 2', 3);
2584 l_stmt_num := 60;
2585 insert into bom_cto_order_lines_upg
2586 (
2587 ATO_LINE_ID
2588 , BATCH_ID
2589 , BOM_ITEM_TYPE
2590 , COMPONENT_CODE
2591 , COMPONENT_SEQUENCE_ID
2592 , CONFIG_ITEM_ID
2593 , INVENTORY_ITEM_ID
2594 , ITEM_TYPE_CODE
2595 , LINE_ID
2596 , LINK_TO_LINE_ID
2597 , ORDERED_QUANTITY
2598 , ORDER_QUANTITY_UOM
2599 , PARENT_ATO_LINE_ID
2600 , PERFORM_MATCH
2601 , PLAN_LEVEL
2602 , SCHEDULE_SHIP_DATE
2603 , SHIP_FROM_ORG_ID
2604 , TOP_MODEL_LINE_ID
2605 , WIP_SUPPLY_TYPE
2606 , HEADER_ID
2607 , LAST_UPDATE_DATE
2608 , LAST_UPDATED_BY
2609 , CREATION_DATE
2610 , CREATED_BY
2611 , LAST_UPDATE_LOGIN
2612 , REQUEST_ID
2613 , PROGRAM_APPLICATION_ID
2614 , PROGRAM_ID
2615 , PROGRAM_UPDATE_DATE
2616 , OPTION_SPECIFIC
2617 , REUSE_CONFIG
2618 , QTY_PER_PARENT_MODEL
2619 , STATUS
2620 --, SEQUENCE
2621 , CONFIG_CREATION
2622 )
2623 select distinct
2624 bcol2.ATO_LINE_ID
2625 , bcol2.BATCH_ID
2626 , bcol2.BOM_ITEM_TYPE
2627 , bcol2.COMPONENT_CODE
2628 , bcol2.COMPONENT_SEQUENCE_ID
2629 , bcol2.CONFIG_ITEM_ID
2630 , bcol2.INVENTORY_ITEM_ID
2631 , bcol2.ITEM_TYPE_CODE
2632 , bcol2.LINE_ID
2633 , bcol2.LINK_TO_LINE_ID
2634 , bcol2.ORDERED_QUANTITY
2635 , bcol2.ORDER_QUANTITY_UOM
2636 , bcol2.PARENT_ATO_LINE_ID
2637 , bcol2.PERFORM_MATCH --7201878
2638 --, 'Y' --bcol2.PERFORM_MATCH /* Sushant made change to identify matched items */
2639 , bcol2.PLAN_LEVEL
2640 , bcol2.SCHEDULE_SHIP_DATE
2641 , bcol2.SHIP_FROM_ORG_ID
2642 , bcol2.TOP_MODEL_LINE_ID
2643 , bcol2.WIP_SUPPLY_TYPE
2644 , bcol2.HEADER_ID
2645 , sysdate --LAST_UPDATE_DATE
2646 , bcol2.LAST_UPDATED_BY
2647 , sysdate --CREATION_DATE
2648 , bcol2.CREATED_BY
2649 , bcol2.LAST_UPDATE_LOGIN
2650 , bcol2.REQUEST_ID
2651 , bcol2.PROGRAM_APPLICATION_ID
2652 , bcol2.PROGRAM_ID
2653 , sysdate --PROGRAM_UPDATE_DATE
2654 , bcol2.OPTION_SPECIFIC
2655 , 'N' --bcol2.REUSE_CONFIG
2656 , bcol2.QTY_PER_PARENT_MODEL
2657 , 'UPG' --STATUS
2658 --, bcol2.SEQUENCE
2659 , nvl(msi.config_orgs, '1')
2660 from bom_cto_order_lines bcol1
2661 , bom_cto_order_lines bcol2
2662 , bom_ato_configurations bac
2663 , oe_order_lines_all oel
2664 , mtl_system_items msi
2665 where bac.config_item_id = p_config_id
2666 -- and exists in bcol
2667 and bac.config_item_id = bcol1.config_item_id
2668 -- on open order lines
2669 and bcol1.line_id = oel.line_id
2670 and nvl(oel.open_flag, 'N') = 'Y'
2671 and bcol2.ato_line_id = bcol1.ato_line_id
2672 -- to get item attribute
2673 and msi.inventory_item_id = bcol2.inventory_item_id
2674 and msi.organization_id = bcol2.ship_from_org_id;
2675
2676 WriteToLog('Rows inserted::'|| to_char(sql%rowcount), 3);
2677 END IF;
2678
2679 END IF; /* p_open_lines = 'Y' */
2680
2681 IF l_match = 1 THEN
2682 --
2683 -- select this config item on closed order lines, only if attribute = 3 and not already in bcol_upg
2684 -- populate into bcol_upg
2685 -- mark as UPG
2686 --
2687 IF (l_attribute = 3) THEN
2688 WriteToLog('sql 3', 3);
2689 l_stmt_num := 70;
2690 insert into bom_cto_order_lines_upg
2691 (
2692 ATO_LINE_ID
2693 , BATCH_ID
2694 , BOM_ITEM_TYPE
2695 , COMPONENT_CODE
2696 , COMPONENT_SEQUENCE_ID
2697 , CONFIG_ITEM_ID
2698 , INVENTORY_ITEM_ID
2699 , ITEM_TYPE_CODE
2700 , LINE_ID
2701 , LINK_TO_LINE_ID
2702 , ORDERED_QUANTITY
2703 , ORDER_QUANTITY_UOM
2704 , PARENT_ATO_LINE_ID
2705 , PERFORM_MATCH
2706 , PLAN_LEVEL
2707 , SCHEDULE_SHIP_DATE
2708 , SHIP_FROM_ORG_ID
2709 , TOP_MODEL_LINE_ID
2710 , WIP_SUPPLY_TYPE
2711 , HEADER_ID
2712 , LAST_UPDATE_DATE
2713 , LAST_UPDATED_BY
2714 , CREATION_DATE
2715 , CREATED_BY
2716 , LAST_UPDATE_LOGIN
2717 , REQUEST_ID
2718 , PROGRAM_APPLICATION_ID
2719 , PROGRAM_ID
2720 , PROGRAM_UPDATE_DATE
2721 , OPTION_SPECIFIC
2722 , REUSE_CONFIG
2723 , QTY_PER_PARENT_MODEL
2724 , STATUS
2725 , CONFIG_CREATION
2726 )
2727 select distinct
2728 bcol.ATO_LINE_ID
2729 , bcol.BATCH_ID
2730 , bcol.BOM_ITEM_TYPE
2731 , bcol.COMPONENT_CODE
2732 , bcol.COMPONENT_SEQUENCE_ID
2733 , bcol.CONFIG_ITEM_ID
2734 , bcol.INVENTORY_ITEM_ID
2735 , bcol.ITEM_TYPE_CODE
2736 , bcol.LINE_ID
2737 , bcol.LINK_TO_LINE_ID
2738 , bcol.ORDERED_QUANTITY
2739 , bcol.ORDER_QUANTITY_UOM
2740 , bcol.PARENT_ATO_LINE_ID
2741 , bcol.PERFORM_MATCH --7201878
2742 --, 'Y' --bcol.PERFORM_MATCH /* Sushant made changes to identify matched items */
2743 , bcol.PLAN_LEVEL
2744 , bcol.SCHEDULE_SHIP_DATE
2745 , bcol.SHIP_FROM_ORG_ID
2746 , bcol.TOP_MODEL_LINE_ID
2747 , bcol.WIP_SUPPLY_TYPE
2748 , bcol.HEADER_ID
2749 , sysdate --LAST_UPDATE_DATE
2750 , bcol.LAST_UPDATED_BY
2751 , sysdate --CREATION_DATE
2752 , bcol.CREATED_BY
2753 , bcol.LAST_UPDATE_LOGIN
2754 , bcol.REQUEST_ID
2755 , bcol.PROGRAM_APPLICATION_ID
2756 , 99 -- matched item on closed line
2757 , bcol.PROGRAM_UPDATE_DATE
2758 , bcol.OPTION_SPECIFIC
2759 , 'N' --bcol.REUSE_CONFIG
2760 , bcol.QTY_PER_PARENT_MODEL
2761 , 'UPG' --STATUS
2762 , nvl(msi.config_orgs, '1')
2763 from bom_ato_configurations bac
2764 , bom_cto_order_lines bcol
2765 , mtl_system_items msi
2766 where bac.config_item_id = p_config_id
2767 and NOT EXISTS
2768 (select 'exists'
2769 from bom_cto_order_lines_upg bcolu
2770 where bcolu.config_item_id = bac.config_item_id)
2771 -- select first ato_line_id in bcol
2772 and bcol.ato_line_id =
2773 (select bcol1.ato_line_id
2774 from bom_cto_order_lines bcol1
2775 where bcol1.config_item_id = bac.config_item_id
2776 -- pick up only if config is at top level
2777 and bcol1.line_id = bcol1.ato_line_id
2778 and rownum = 1)
2779 -- to get item attribute
2780 and msi.inventory_item_id = bcol.inventory_item_id
2781 and msi.organization_id = bcol.ship_from_org_id;
2782
2783 WriteToLog('Rows inserted::'|| to_char(sql%rowcount), 3);
2784 END IF;
2785
2786 --
2787 -- populate into bcol_upg from bac
2788 -- if not already exists
2789 -- only if attribute = 3
2790 -- mark as UPG
2791 -- mark with program_id = 99 to indicate that it was populated from bac
2792 --
2793 WriteToLog('sql 4', 3);
2794
2795 --
2796 -- check to see if not already populated as part of parent
2797 --
2798 IF (l_attribute = 3) THEN
2799
2800 WriteToLog('Item being populated from bac::'|| to_char(p_config_id), 4);
2801 l_stmt_num := 80;
2802 BEGIN
2803 select 'Y'
2804 into l_exists
2805 from bom_cto_order_lines_upg
2806 where config_item_id = p_config_id
2807 and rownum = 1;
2808 WriteToLog('Item::'|| to_char(p_config_id)||' already exists in bcolu', 4);
2809
2810 EXCEPTION
2811 WHEN no_data_found THEN
2812 WriteToLog('Populating from bac Item::'|| to_char(p_config_id), 4);
2813 populate_bcolu_from_bac(
2814 p_config_id
2815 , l_return_status
2816 , l_msg_count
2817 , l_msg_data);
2818 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2819 WriteToLog('ERROR: Populate_bcolu_from_bac returned unexp error');
2820 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2821 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
2822 WriteToLog('ERROR: Populate_bcolu_from_bac returned expected error');
2823 raise FND_API.G_EXC_ERROR;
2824 END IF;
2825 END; -- sub block
2826 END IF; /* l_attribute = 3 */
2827 END IF; /* l_match = 1 */
2828
2829 END IF; /* sourcing not changed, attribute is 1*/
2830
2831 WriteToLog('Done populate_config');
2832
2833 EXCEPTION
2834 WHEN FND_API.G_EXC_ERROR THEN
2835 WriteToLog('ERROR: Expected error in Populate_Config::'||to_char(l_stmt_num)||'::'||sqlerrm);
2836 x_return_status := FND_API.G_RET_STS_ERROR;
2837 CTO_MSG_PUB.Count_And_Get
2838 (p_msg_count => x_msg_count
2839 ,p_msg_data => x_msg_data
2840 );
2841
2842 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2843 WriteToLog('ERROR: Unexpected error in Populate_Config::'||to_char(l_stmt_num)||'::'||sqlerrm);
2844 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2845 CTO_MSG_PUB.Count_And_Get
2846 (p_msg_count => x_msg_count
2847 ,p_msg_data => x_msg_data
2848 );
2849
2850 WHEN OTHERS THEN
2851 WriteToLog('ERROR: Others error in Populate_Config::'||to_char(l_stmt_num)||'::'||sqlerrm);
2852 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2853 CTO_MSG_PUB.Count_And_Get
2854 (p_msg_count => x_msg_count
2855 ,p_msg_data => x_msg_data
2856 );
2857
2858 END populate_config;
2859
2860
2861 PROCEDURE populate_bcolu_from_bac(
2862 p_config_id IN number,
2863 x_return_status out NOCOPY varchar2,
2864 x_msg_count out NOCOPY number,
2865 x_msg_data out NOCOPY varchar2) IS
2866
2867 t_bcol BCOL_TAB;
2868
2869 CURSOR c_bac_data IS
2870 select
2871 bom_cto_order_lines_s1.nextval, -- line_id
2872 substr(bac.component_code, (instr(bac.component_code, '-', -1)+1)), -- inventory_item_id
2873 bac.component_item_id, -- header_id::storing comp_item_id here for intermediate processing
2874 bac.component_code, -- component_code
2875 msi.bom_item_type, -- bom_item_type
2876 msi.primary_uom_code, -- order_quantity_uom
2877 bac.component_quantity, -- ordered_quantity
2878 bac.component_quantity, -- per_quantity
2879 sysdate, -- schedule_ship_date
2880 'N' , -- option_specific BUGFIX 3602292 defaulted this value to N as model will not have option_specific_sourced flag.
2881 nvl(msi.config_orgs, '1'), -- config_orgs
2882 sysdate, -- creation_date
2883 nvl(Fnd_Global.USER_ID, -1), -- created_by
2884 sysdate, -- last_update_date
2885 nvl(Fnd_Global.USER_ID, -1), -- last_updated_by
2886 cto_update_configs_pk.bac_program_id, -- program_id
2887 'Y', -- perform_match /* Sushant made changes to identify matched items */
2888 'N', -- reuse_config
2889 bac.organization_id
2890 from bom_ato_configurations bac,
2891 mtl_system_items msi
2892 where bac.config_item_id = p_config_id
2893 -- and bac.component_item_id <> bac.base_model_id -- not pick up top model
2894 and msi.inventory_item_id = substr(bac.component_code, (instr(bac.component_code, '-', -1)+1)) -- bac.component_item_id
2895 and msi.organization_id = bac.organization_id;
2896
2897 l_index number := 0;
2898 l_top_model_line_id number;
2899 l_base_model_id number;
2900 l_header_id number;
2901 i number := 0;
2902 l_child_config_id NUMBER;
2903 l_parent_index NUMBER;
2904 l_return_status varchar2(1);
2905 l_msg_count number;
2906 l_msg_data varchar2(240);
2907 l_stmt_num number;
2908
2909 BEGIN
2910
2911 x_return_status := FND_API.G_RET_STS_SUCCESS;
2912 l_stmt_num := 10;
2913
2914 BEGIN
2915 select base_model_id
2916 into l_base_model_id
2917 from bom_ato_configurations
2918 where config_item_id = p_config_id
2919 and rownum = 1;
2920
2921 EXCEPTION
2922 WHEN no_data_found THEN
2923 WriteToLog('Config does not exist in match tables.', 1);
2924 return;
2925 END;
2926
2927 --
2928 -- Get cursor data from bac into t_bcol
2929 --
2930 l_stmt_num := 20;
2931 OPEN c_bac_data;
2932
2933 WHILE(TRUE)
2934 LOOP
2935
2936 l_index := t_bcol.count + 1;
2937 l_stmt_num := 30;
2938
2939 FETCH c_bac_data INTO
2940 t_bcol(l_index).line_id,
2941 t_bcol(l_index).inventory_item_id,
2942 t_bcol(l_index).header_id,
2943 t_bcol(l_index).component_code,
2944 t_bcol(l_index).bom_item_type,
2945 t_bcol(l_index).order_quantity_uom,
2946 t_bcol(l_index).ordered_quantity,
2947 t_bcol(l_index).qty_per_parent_model,
2948 t_bcol(l_index).schedule_ship_date,
2949 t_bcol(l_index).option_specific,
2950 t_bcol(l_index).config_creation,
2951 t_bcol(l_index).creation_date,
2952 t_bcol(l_index).created_by,
2953 t_bcol(l_index).last_update_date,
2954 t_bcol(l_index).last_updated_by,
2955 t_bcol(l_index).program_id,
2956 t_bcol(l_index).perform_match,
2957 t_bcol(l_index).reuse_config,
2958 t_bcol(l_index).ship_from_org_id;
2959
2960 EXIT WHEN c_bac_data%NOTFOUND OR c_bac_data%NOTFOUND IS NULL;
2961
2962 l_stmt_num := 35;
2963 IF t_bcol(l_index).inventory_item_id = l_base_model_id THEN
2964 l_top_model_line_id := t_bcol(l_index).line_id;
2965 t_bcol(l_index).plan_level := 1;
2966 t_bcol(l_index).config_item_id := p_config_id;
2967 END IF;
2968
2969 END LOOP;
2970 CLOSE c_bac_data;
2971
2972 --
2973 -- populate t_bcol recursively for all child configs
2974 -- error out if any child config has item attribute <> 3
2975 --
2976 l_stmt_num := 40;
2977 FOR i IN t_bcol.first .. t_bcol.last LOOP
2978
2979 IF t_bcol(i).header_id <> t_bcol(i).inventory_item_id THEN
2980 -- this is a lower level config
2981 l_child_config_id := t_bcol(i).header_id;
2982 l_parent_index := i;
2983
2984 populate_child_config(
2985 t_bcol,
2986 l_parent_index,
2987 l_child_config_id,
2988 l_return_status,
2989 l_msg_count,
2990 l_msg_data);
2991
2992 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2993 WriteToLog('ERROR: Populate_child_config returned unexp error');
2994 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2995 END IF;
2996
2997 END IF;
2998 END LOOP;
2999
3000 l_stmt_num := 50;
3001 select bom_cto_order_lines_s1.nextval
3002 into l_header_id
3003 from dual;
3004
3005 -- populate top_model_line_id, ato_line_id and header_id
3006 l_stmt_num := 60;
3007 FOR i IN 1..t_bcol.count LOOP
3008 t_bcol(i).top_model_line_id := l_top_model_line_id;
3009 t_bcol(i).ato_line_id := l_top_model_line_id;
3010 t_bcol(i).header_id := l_header_id;
3011 END LOOP;
3012
3013 -- populate link_to_line_id
3014 l_stmt_num := 70;
3015 populate_link_to_line_id(t_bcol,
3016 l_return_status);
3017 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3018 WriteToLog('ERROR: Populate_link_to_line_id returned unexp error');
3019 raise FND_API.G_EXC_UNEXPECTED_ERROR;
3020 END IF;
3021
3022 -- convert t_bcol to sparse array
3023 l_stmt_num := 80;
3024 contiguous_to_sparse_bcol(t_bcol,
3025 l_return_status);
3026 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3027 WriteToLog('ERROR: Contiguous_to_sparse_bcol returned unexp error');
3028 raise FND_API.G_EXC_UNEXPECTED_ERROR;
3029 END IF;
3030
3031 -- populate plan_level
3032 l_stmt_num := 90;
3033 populate_plan_level(t_bcol,
3034 l_return_status);
3035 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3036 WriteToLog('ERROR: Populate_plan_level returned unexp error');
3037 raise FND_API.G_EXC_UNEXPECTED_ERROR;
3038 END IF;
3039
3040 -- populate wip_supply_type
3041 l_stmt_num := 100;
3042 populate_wip_supply_type(t_bcol,
3043 l_return_status);
3044 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3045 WriteToLog('ERROR: Populate_wip_supply_type returned unexp error');
3046 raise FND_API.G_EXC_UNEXPECTED_ERROR;
3047 -- bug 5859772: If a 'N' (for no data found exception in populate_wip_supply_type ) is returned,
3048 -- it means the bill has changed since the config was created.
3049 -- We will not process the config in that case and simply return.
3050 ELSIF l_return_status = 'N' then
3051 WriteToLog('Model bill has changed since the config was created. Not processing this config '||p_config_id, 1);
3052 return;
3053 END IF;
3054
3055 -- populate parent_ato_line_id
3056 l_stmt_num := 110;
3057 populate_parent_ato(t_bcol,
3058 l_top_model_line_id,
3059 l_return_status);
3060 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3061 WriteToLog('ERROR: Populate_parent_ato returned unexp error');
3062 raise FND_API.G_EXC_UNEXPECTED_ERROR;
3063 END IF;
3064
3065 -- insert into bcol
3066 l_stmt_num := 120;
3067 i := t_bcol.first;
3068
3069 WHILE i IS NOT NULL LOOP
3070 l_stmt_num := 130;
3071 INSERT INTO bom_cto_order_lines_upg(
3072 HEADER_ID ,
3073 LINE_ID ,
3074 LINK_TO_LINE_ID ,
3075 ATO_LINE_ID ,
3076 PARENT_ATO_LINE_ID ,
3077 TOP_MODEL_LINE_ID ,
3078 PLAN_LEVEL ,
3079 WIP_SUPPLY_TYPE ,
3080 PERFORM_MATCH ,
3081 BOM_ITEM_TYPE ,
3082 COMPONENT_CODE ,
3083 COMPONENT_SEQUENCE_ID ,
3084 CONFIG_ITEM_ID ,
3085 INVENTORY_ITEM_ID ,
3086 ITEM_TYPE_CODE ,
3087 BATCH_ID ,
3088 ORDERED_QUANTITY ,
3089 ORDER_QUANTITY_UOM ,
3090 SCHEDULE_SHIP_DATE ,
3091 SHIP_FROM_ORG_ID ,
3092 LAST_UPDATE_DATE ,
3093 LAST_UPDATED_BY ,
3094 CREATION_DATE ,
3095 CREATED_BY ,
3096 LAST_UPDATE_LOGIN ,
3097 REQUEST_ID ,
3098 PROGRAM_APPLICATION_ID ,
3099 PROGRAM_ID ,
3100 PROGRAM_UPDATE_DATE ,
3101 QTY_PER_PARENT_MODEL,
3102 OPTION_SPECIFIC,
3103 REUSE_CONFIG,
3104 STATUS,
3105 SEQUENCE,
3106 CONFIG_CREATION
3107 )
3108 VALUES (
3109 t_bcol(i).header_id,
3110 t_bcol(i).line_id,
3111 t_bcol(i).link_to_line_id,
3112 t_bcol(i).ato_line_id,
3113 t_bcol(i).parent_ato_line_id,
3114 t_bcol(i).top_model_line_id,
3115 t_bcol(i).plan_level,
3116 t_bcol(i).wip_supply_type,
3117 'Y', -- perform_match BUGFIX 3567693
3118 t_bcol(i).bom_item_type,
3119 t_bcol(i).component_code,
3120 t_bcol(i).component_sequence_id,
3121 t_bcol(i).config_item_id,
3122 t_bcol(i).inventory_item_id,
3123 decode(t_bcol(i).line_id, t_bcol(i).ato_line_id, 'MODEL', decode(t_bcol(i).bom_item_type, '4', 'OPTION', 'CLASS')),
3124 null, -- batch_id
3125 t_bcol(i).ordered_quantity,
3126 t_bcol(i).order_quantity_uom,
3127 t_bcol(i).schedule_ship_date,
3128 t_bcol(i).ship_from_org_id,
3129 t_bcol(i).last_update_date,
3130 t_bcol(i).last_updated_by,
3131 t_bcol(i).creation_date,
3132 t_bcol(i).created_by,
3133 t_bcol(i).last_update_login,
3134 null, -- request_id
3135 null, -- program_application_id
3136 t_bcol(i).program_id,
3137 null, -- program_update_date
3138 t_bcol(i).qty_per_parent_model,
3139 t_bcol(i).option_specific,
3140 'N',
3141 'UPG',
3142 null,
3143 t_bcol(i).config_creation
3144 );
3145
3146 WriteToLog('populate_bcolu_from_bac: Inserted ' || t_bcol(i).line_id);
3147 i:= t_bcol.next(i);
3148 END LOOP;
3149
3150 EXCEPTION
3151 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3152 WriteToLog('ERROR: Unexpected error in Populate_Bcolu_From_Bac::'||to_char(l_stmt_num)||'::'||sqlerrm);
3153 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3154 CTO_MSG_PUB.Count_And_Get
3155 (p_msg_count => x_msg_count
3156 ,p_msg_data => x_msg_data
3157 );
3158
3159 WHEN OTHERS THEN
3160 WriteToLog('ERROR: Others error in Populate_Bcolu_From_Bac::'||to_char(l_stmt_num)||'::'||sqlerrm);
3161 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3162 CTO_MSG_PUB.Count_And_Get
3163 (p_msg_count => x_msg_count
3164 ,p_msg_data => x_msg_data
3165 );
3166
3167 END populate_bcolu_from_bac;
3168
3169
3170 PROCEDURE populate_child_config(
3171 t_bcol IN OUT NOCOPY bcol_tab,
3172 p_parent_index IN NUMBER,
3173 p_child_config_id IN NUMBER,
3174 x_return_status out NOCOPY varchar2,
3175 x_msg_count out NOCOPY number,
3176 x_msg_data out NOCOPY varchar2) IS
3177
3178
3179 CURSOR c_bac_child_data(l_curr_config_id NUMBER) IS
3180 select
3181 bom_cto_order_lines_s1.nextval, -- line_id
3182 substr(bac.component_code, (instr(bac.component_code, '-', -1)+1)), -- inventory_item_id
3183 bac.component_item_id, -- header_id::storing comp_item_id here for intermediate processing
3184 substr(bac.component_code, (instr(bac.component_code, '-', 1)+1)), -- component_code
3185 --bac.config_item_id, -- config_item_id
3186 msi.bom_item_type, -- bom_item_type
3187 msi.primary_uom_code, -- order_quantity_uom
3188 bac.component_quantity, -- ordered_quantity
3189 bac.component_quantity, -- per_quantity
3190 sysdate, -- schedule_ship_date
3191 nvl(to_char(msi.option_specific_sourced),'N'), -- option_specific --bugfix3845686
3192 nvl(msi.config_orgs, '1'), -- config_orgs
3193 sysdate, -- creation_date
3194 nvl(Fnd_Global.USER_ID, -1), -- created_by
3195 sysdate, -- last_update_date
3196 nvl(Fnd_Global.USER_ID, -1), -- last_updated_by
3197 cto_update_configs_pk.bac_program_id -- program_id
3198 from bom_ato_configurations bac,
3199 mtl_system_items msi
3200 where bac.config_item_id = l_curr_config_id
3201 and bac.component_item_id <> bac.base_model_id --not pick up top model
3202 and msi.inventory_item_id = bac.component_item_id
3203 and msi.organization_id = bac.organization_id;
3204
3205 l_child_config_id NUMBER;
3206 l_parent_index NUMBER;
3207 i NUMBER;
3208 l_item_attr NUMBER;
3209 l_new_index NUMBER;
3210 l_index NUMBER;
3211 l_return_status varchar2(1);
3212 l_msg_count number;
3213 l_msg_data varchar2(240);
3214 l_child_model_id number;
3215 l_child_model_name varchar2(50);
3216 l_stmt_num number;
3217
3218 BEGIN
3219
3220 --
3221 -- populate t_bcol recursively for all child configs
3222 -- error out if any child config has item attribute <> 3
3223 --
3224 WriteToLog('ENTERED Populate_child_config, this is a recursive api ');
3225 WriteToLog('IN parameters ');
3226 WriteToLog('p_parent_index=>'||p_parent_index);
3227 WriteToLog('p_child_config_id=>'||p_child_config_id);
3228
3229 l_stmt_num := 10;
3230 x_return_status := FND_API.G_RET_STS_SUCCESS;
3231 l_parent_index := p_parent_index;
3232
3233 WriteToLog('t_bcol(l_parent_index).header_id=>'||t_bcol(l_parent_index).header_id);
3234
3235 select nvl(msi.config_orgs, '1'), inventory_item_id
3236 into l_item_attr, l_child_model_id
3237 from mtl_system_items msi
3238 where msi.inventory_item_id = (select base_item_id --bugfix3845686
3239 from mtl_system_items
3240 where inventory_item_id = t_bcol(l_parent_index).header_id
3241 and rownum =1)
3242 --and msi.organization_id = t_bcol(l_parent_index).ship_from_org_id;
3243 and rownum = 1;
3244
3245
3246 WriteToLog('l_item_attr=>'||l_item_attr);
3247
3248 l_stmt_num := 20;
3249 IF (nvl(l_item_attr, 1) <> 3) THEN
3250
3251 l_stmt_num := 30;
3252 select substrb(concatenated_segments,1,50) name
3253 into l_child_model_name
3254 from mtl_system_items_kfv msi
3255 where msi.inventory_item_id = l_child_model_id
3256 and rownum=1;/* Fixed bug 3529482 */
3257
3258
3259
3260 WriteToLog('++++++++++++++++++++++++++++++++++++++++', 1);
3261 WriteToLog('Item attribute Configured Item, BOM creation not setup correctly for child model '||l_child_model_name||' . Please correct this and run the program again.', 1);
3262 WriteToLog('++++++++++++++++++++++++++++++++++++++++', 1);
3263 raise FND_API.G_EXC_ERROR;
3264 ELSE
3265 -- populate config_id column for this line
3266 l_stmt_num := 40;
3267 t_bcol(l_parent_index).config_item_id := t_bcol(l_parent_index).header_id;
3268 l_new_index := t_bcol.count + 1;
3269
3270 -- populate child config from bac
3271 l_stmt_num := 50;
3272 OPEN c_bac_child_data(p_child_config_id);
3273
3274 WHILE(TRUE)
3275 LOOP
3276 l_stmt_num := 60;
3277 l_index := t_bcol.count + 1;
3278
3279 FETCH c_bac_child_data INTO
3280 t_bcol(l_index).line_id,
3281 t_bcol(l_index).inventory_item_id,
3282 t_bcol(l_index).header_id,
3283 t_bcol(l_index).component_code,
3284 t_bcol(l_index).bom_item_type,
3285 t_bcol(l_index).order_quantity_uom,
3286 t_bcol(l_index).ordered_quantity,
3287 t_bcol(l_index).qty_per_parent_model,
3288 t_bcol(l_index).schedule_ship_date,
3289 t_bcol(l_index).option_specific,
3290 t_bcol(l_index).config_creation,
3291 t_bcol(l_index).creation_date,
3292 t_bcol(l_index).created_by,
3293 t_bcol(l_index).last_update_date,
3294 t_bcol(l_index).last_updated_by,
3295 t_bcol(l_index).program_id;
3296
3297 EXIT WHEN c_bac_child_data%NOTFOUND; -- bugfix 3845686
3298
3299 t_bcol(l_index).component_code := t_bcol(p_parent_index).component_code||'-'||t_bcol(l_index).component_code;
3300 t_bcol(l_index).ordered_quantity := t_bcol(p_parent_index).ordered_quantity * t_bcol(l_index).ordered_quantity;
3301
3302
3303
3304 END LOOP;
3305
3306 CLOSE c_bac_child_data;
3307 END IF;
3308
3309
3310 l_stmt_num := 70;
3311 FOR i IN l_new_index .. t_bcol.last LOOP
3312 IF t_bcol(i).header_id <> t_bcol(i).inventory_item_id THEN
3313
3314 -- this is a lower level config
3315 l_stmt_num := 80;
3316 l_child_config_id := t_bcol(i).header_id;
3317 l_parent_index := i;
3318
3319 --KIRAN
3320 WriteToLog('CIB of item being passed=>'||t_bcol(i).config_creation);
3321
3322 populate_child_config(
3323 t_bcol,
3324 l_parent_index,
3325 l_child_config_id,
3326 l_return_status,
3327 l_msg_count,
3328 l_msg_data);
3329
3330 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3331 WriteToLog('ERROR: Populate_child_config returned unexp error');
3332 raise FND_API.G_EXC_UNEXPECTED_ERROR;
3333 END IF;
3334 END IF;
3335 END LOOP;
3336
3337 EXCEPTION
3338 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3339 WriteToLog('ERROR: Unexpected error in Populate_Child_Config::'||to_char(l_stmt_num)||'::'||sqlerrm);
3340 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3341 CTO_MSG_PUB.Count_And_Get
3342 (p_msg_count => x_msg_count
3343 ,p_msg_data => x_msg_data
3344 );
3345
3346 WHEN OTHERS THEN
3347 WriteToLog('ERROR: Others error in Populate_Child_Config::'||to_char(l_stmt_num)||'::'||sqlerrm);
3348 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3349 CTO_MSG_PUB.Count_And_Get
3350 (p_msg_count => x_msg_count
3351 ,p_msg_data => x_msg_data
3352 );
3353
3354 END populate_child_config;
3355
3356
3357 PROCEDURE populate_link_to_line_id(
3358 p_bcol_tab IN OUT NOCOPY bcol_tab,
3359 x_return_status OUT NOCOPY varchar2) IS
3360
3361 TYPE varchar2_1000_tbl_type IS TABLE OF varchar2(1000) INDEX BY binary_integer;
3362 l_parent_code_tab varchar2_1000_tbl_type;
3363 l_loc number :=0 ;
3364 l_stmt_num number;
3365
3366 BEGIN
3367
3368 l_stmt_num := 10;
3369 FOR i IN 1..p_bcol_tab.count LOOP
3370 l_loc := instr(p_bcol_tab(i).component_code , '-' , -1 );
3371 IF (l_loc = 0) THEN
3372 l_parent_code_tab(i) := null;
3373 ELSE
3374 l_parent_code_tab(i) := substr(p_bcol_tab(i).component_code, 1, l_loc-1);
3375 END IF;
3376 p_bcol_tab(i).link_to_line_id := null;
3377 END LOOP;
3378
3379 l_stmt_num := 20;
3380 FOR i IN 1..l_parent_code_tab.count LOOP
3381 FOR j IN 1..p_bcol_tab.count LOOP
3382 IF (l_parent_code_tab(i) = p_bcol_tab(j).component_code) THEN
3383 p_bcol_tab(i).link_to_line_id := p_bcol_tab(j).line_id;
3384 EXIT;
3385
3386 END IF;
3387 END LOOP;
3388 END LOOP;
3389
3390 EXCEPTION
3391 WHEN OTHERS THEN
3392 WriteToLog('ERROR: Others error in Populate_Link_To_Line_Id::'||to_char(l_stmt_num)||'::'||sqlerrm);
3393 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3394
3395 END populate_link_to_line_id;
3396
3397
3398 PROCEDURE populate_plan_level(
3399 p_t_bcol IN OUT NOCOPY bcol_tab,
3400 x_return_status OUT NOCOPY varchar2)
3401 IS
3402
3403 TYPE TABNUM is TABLE of NUMBER index by binary_integer ;
3404 v_raw_line_id TABNUM ;
3405 v_src_point number ;
3406 j number ;
3407 i number := 0 ;
3408 l_stmt_num number;
3409
3410 BEGIN
3411
3412 l_stmt_num := 10;
3413 i := p_t_bcol.first ;
3414
3415 WHILE i IS NOT NULL LOOP
3416 IF (p_t_bcol.exists(i)) THEN
3417 v_src_point := i;
3418
3419 l_stmt_num := 20;
3420 WHILE (p_t_bcol(v_src_point).plan_level IS NULL) LOOP
3421 v_raw_line_id(v_raw_line_id.count + 1) := v_src_point;
3422 v_src_point := p_t_bcol(v_src_point).link_to_line_id;
3423 END LOOP;
3424
3425 j := v_raw_line_id.count;
3426
3427 l_stmt_num := 30;
3428 WHILE (j >= 1) LOOP
3429 p_t_bcol(v_raw_line_id(j)).plan_level := p_t_bcol(v_src_point).plan_level + 1;
3430 v_src_point := v_raw_line_id(j);
3431 j := j -1;
3432 END LOOP;
3433 v_raw_line_id.delete;
3434 END IF;
3435
3436 i := p_t_bcol.next(i);
3437 END LOOP;
3438
3439 EXCEPTION
3440 WHEN OTHERS THEN
3441 WriteToLog('ERROR: Others error in Populate_Plan_Level::'||to_char(l_stmt_num)||'::'||sqlerrm);
3442 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3443
3444 END populate_plan_level;
3445
3446
3447 PROCEDURE populate_wip_supply_type(
3448 p_t_bcol IN OUT NOCOPY bcol_tab,
3449 x_return_status OUT NOCOPY varchar2)
3450 IS
3451
3452 v_item number;
3453 v_parent_item number;
3454 i number := 0;
3455 l_stmt_num number;
3456
3457 BEGIN
3458
3459 l_stmt_num := 10;
3460 i := p_t_bcol.first ;
3461
3462 WHILE i IS NOT NULL LOOP
3463 IF (p_t_bcol.exists(i)) THEN
3464 l_stmt_num := 20;
3465 IF (p_t_bcol(i).line_id <> p_t_bcol(i).ato_line_id) THEN
3466 v_item := i;
3467 v_parent_item := p_t_bcol(v_item).link_to_line_id;
3468 l_stmt_num := 30;
3469 -- bug 5859772: We need to handle a no data found here. This can happen if the bill
3470 -- has changed since the config was created. In such a case we shall not process
3471 -- the config.
3472 begin
3473 select
3474 bic.wip_supply_type,
3475 bic.component_sequence_id
3476 into p_t_bcol(v_item).wip_supply_type,
3477 p_t_bcol(v_item).component_sequence_id
3478 from bom_bill_of_materials bbom,
3479 bom_inventory_components bic
3480 where bbom.bill_sequence_id =
3481 (select common_bill_sequence_id
3482 from bom_bill_of_materials
3483 where assembly_item_id = p_t_bcol(v_parent_item).inventory_item_id
3484 and alternate_bom_designator is null
3485 and rownum = 1)
3486 and bbom.common_bill_sequence_id = bic.bill_sequence_id
3487 and bic.component_item_id = p_t_bcol(v_item).inventory_item_id
3488 and rownum = 1;
3489 exception
3490 when no_data_found then
3491 x_return_status := 'N';
3492 end;
3493 -- end bug fix 5859772
3494
3495 END IF; /* line_id <> ato_line_id */
3496 END IF; /* exists */
3497
3498 i := p_t_bcol.next(i);
3499 END LOOP;
3500
3501 EXCEPTION
3502 WHEN OTHERS THEN
3503 WriteToLog('ERROR: Others error in Populate_Wip_Supply_Type::'||to_char(l_stmt_num)||'::'||sqlerrm);
3504 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3505
3506 END populate_wip_supply_type;
3507
3508
3509 PROCEDURE populate_parent_ato(
3510 p_t_bcol in out NOCOPY bcol_tab,
3511 p_bcol_line_id in bom_cto_order_lines.line_id%type,
3512 x_return_status OUT NOCOPY varchar2)
3513 IS
3514
3515 TYPE TABNUM IS TABLE of NUMBER index by binary_integer;
3516 v_raw_line_id TABNUM;
3517 v_src_point NUMBER;
3518 v_prev_src_point NUMBER;
3519 j NUMBER;
3520 v_step VARCHAR2(10);
3521 i NUMBER := 0;
3522 l_stmt_num number;
3523
3524 BEGIN
3525 l_stmt_num := 10;
3526 i := p_t_bcol.first;
3527
3528 WHILE i IS NOT NULL LOOP
3529 l_stmt_num := 20;
3530 IF (p_t_bcol.exists(i)) THEN
3531 v_src_point := i;
3532 WHILE (p_t_bcol.exists(v_src_point)) LOOP
3533 v_raw_line_id(v_raw_line_id.count + 1 ) := v_src_point;
3534 v_prev_src_point := v_src_point;
3535 v_src_point := p_t_bcol(v_src_point).link_to_line_id;
3536 l_stmt_num := 30;
3537 IF (v_src_point IS NULL or v_prev_src_point = p_bcol_line_id) THEN
3538 v_src_point := v_prev_src_point;
3539 EXIT;
3540 END IF;
3541
3542 l_stmt_num := 40;
3543 IF (p_t_bcol(v_src_point).bom_item_type = '1' AND
3544 p_t_bcol(v_src_point).ato_line_id IS NOT NULL AND
3545 nvl (p_t_bcol(v_src_point).wip_supply_type , 0) <> '6') THEN
3546 EXIT;
3547 END IF;
3548 END LOOP;
3549
3550 j := v_raw_line_id.count;
3551
3552 l_stmt_num := 50;
3553 WHILE (j >= 1) LOOP
3554 p_t_bcol(v_raw_line_id(j)).parent_ato_line_id := v_src_point;
3555 j := j -1;
3556 END LOOP;
3557
3558 v_raw_line_id.delete;
3559 END IF;
3560
3561 i := p_t_bcol.next(i);
3562 END LOOP;
3563
3564 EXCEPTION
3565 WHEN OTHERS THEN
3566 WriteToLog('ERROR: Others error in Populate_Parent_Ato::'||to_char(l_stmt_num)||'::'||sqlerrm);
3567 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3568
3569 END populate_parent_ato;
3570
3571
3572 PROCEDURE contiguous_to_sparse_bcol(
3573 p_t_bcol in out NOCOPY bcol_tab,
3574 x_return_status OUT NOCOPY varchar2)
3575 IS
3576
3577 p_t_sparse_bcol bcol_tab;
3578 l_stmt_num number;
3579
3580 BEGIN
3581
3582 l_stmt_num := 10;
3583 FOR i IN 1..p_t_bcol.count LOOP
3584 p_t_sparse_bcol(i) := p_t_bcol(i);
3585 END LOOP;
3586
3587 p_t_bcol.delete;
3588
3589 l_stmt_num := 20;
3590 FOR i IN 1..p_t_sparse_bcol.count LOOP
3591 p_t_bcol(p_t_sparse_bcol(i).line_id) := p_t_sparse_bcol(i);
3592 END LOOP;
3593
3594 EXCEPTION
3595 WHEN OTHERS THEN
3596 WriteToLog('ERROR: Others error in Contiguous_To_Sparse_Bcol::'||to_char(l_stmt_num)||'::'||sqlerrm);
3597 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3598
3599 END contiguous_to_sparse_bcol;
3600
3601
3602 PROCEDURE WriteToLog (p_message in varchar2 default null,
3603 p_level in number default 0) IS
3604 BEGIN
3605 IF gDebugLevel >= p_level THEN
3606 fnd_file.put_line (fnd_file.log, p_message);
3607 END IF;
3608 END WriteToLog;
3609
3610
3611 PROCEDURE Write_Config_Status(
3612 x_return_status out NOCOPY varchar2)
3613 IS
3614
3615 --
3616 -- This cursor will pick up all successfully upgraded configs:
3617 -- If config_creation = 3 and successful on atleast one order line
3618 -- Or if config_creation = 1,2 and successful on all order lines
3619 --
3620 CURSOR c_success IS
3621 select distinct substrb(concatenated_segments,1,50) name,msi.inventory_item_id
3622 item_id
3623 from bom_cto_order_lines_upg bcolu,
3624 mtl_system_items_kfv msi
3625 where bcolu.config_item_id is not null
3626 and bcolu.config_item_id = msi.inventory_item_id
3627 and bcolu.ship_from_org_id = msi.organization_id
3628 and ((bcolu.config_creation = '3'
3629 and exists (select 'exists'
3630 from bom_cto_order_lines_upg bcolu1
3631 where bcolu1.config_item_id = bcolu.config_item_id
3632 and bcolu1.status = 'MRP_SRC'
3633 and rownum = 1))
3634 or (bcolu.config_creation <> '3'
3635 and not exists (select 'exists'
3636 from bom_cto_order_lines_upg bcolu1
3637 where bcolu1.config_item_id = bcolu.config_item_id
3638 and bcolu1.status <> 'MRP_SRC')))
3639 order by 1; -- Modified by Renga for bug 3930047
3640
3641 --
3642 -- This cursor will pick up all errored configs:
3643 -- If errored on all order lines
3644 --
3645 CURSOR c_error IS
3646 select distinct substrb(concatenated_segments,1,50) name,
3647 msi.inventory_item_id item_id
3648 from bom_cto_order_lines_upg bcolu,
3649 mtl_system_items_kfv msi
3650 where bcolu.config_item_id is not null
3651 and bcolu.config_item_id = msi.inventory_item_id
3652 and bcolu.ship_from_org_id = msi.organization_id
3653 and not exists (select 'exists'
3654 from bom_cto_order_lines_upg bcolu1
3655 where bcolu1.config_item_id = bcolu.config_item_id
3656 and bcolu1.status = 'MRP_SRC')
3657 order by 1; -- Modified by Renga for bug 3930047
3658
3659 --
3660 -- This cursor will pick up partially successful configs:
3661 -- If config_creation = 1,2 and errored out on some
3662 -- order lines and successul on other order lines
3663 --
3664 CURSOR c_partial IS
3665 select distinct substrb(concatenated_segments,1,50) name,
3666 msi.inventory_item_id item_id,
3667 oeh.order_number,
3668 decode(bcolu.status, 'MRP_SRC', 'was successfully processed', 'errored out') status
3669 from bom_cto_order_lines_upg bcolu,
3670 mtl_system_items_kfv msi,
3671 oe_order_lines_all oel,
3672 oe_order_headers_all oeh
3673 where bcolu.config_item_id is not null
3674 and bcolu.config_item_id = msi.inventory_item_id
3675 and bcolu.ship_from_org_id = msi.organization_id
3676 and config_creation <> '3'
3677 and exists (select 'exists'
3678 from bom_cto_order_lines_upg bcolu1
3679 where bcolu1.config_item_id = bcolu.config_item_id
3680 and bcolu1.status = 'MRP_SRC')
3681 and exists (select 'exists'
3682 from bom_cto_order_lines_upg bcolu1
3683 where bcolu1.config_item_id = bcolu.config_item_id
3684 and bcolu1.status <> 'MRP_SRC')
3685 and oel.line_id = bcolu.ato_line_id
3686 and oel.header_id = oeh.header_id
3687 order by name, status;
3688
3689 l_stmt_num number;
3690
3691 BEGIN
3692
3693 l_stmt_num := 10;
3694 x_return_status := FND_API.G_RET_STS_SUCCESS;
3695
3696 WriteToLog('+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++', 1);
3697 WriteToLog('+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++', 1);
3698 WriteToLog('+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++', 1);
3699 WriteToLog('Following configuration items were processed successfully for Item, BOM and Routing creation:', 1);
3700
3701 FOR v_success IN c_success LOOP
3702 WriteToLog(' '||v_success.name||'('||v_success.item_id||')', 1);
3703 END LOOP;
3704
3705 l_stmt_num := 20;
3706 WriteToLog(' ', 1);
3707 WriteToLog('+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++', 1);
3708 WriteToLog('+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++', 1);
3709 WriteToLog('+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++', 1);
3710 WriteToLog('Following configuration items were not processed for Item, BOM and Routing creation due to errors:', 1);
3711
3712 FOR v_error IN c_error LOOP
3713 WriteToLog(' '||v_error.name||'('||v_error.item_id||')', 1);
3714 END LOOP;
3715
3716 WriteToLog('These configuration items may exist on multiple order lines. Please go through the log file for details on error and action required for each configuration item in error for all order lines.', 1);
3717
3718 l_stmt_num := 30;
3719 WriteToLog(' ', 1);
3720 WriteToLog('+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++', 1);
3721 WriteToLog('+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++', 1);
3722 WriteToLog('+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++', 1);
3723 WriteToLog('Following configuration items were partially successful for Item, BOM and Routing creation:', 1);
3724
3725 FOR v_partial IN c_partial LOOP
3726 WriteToLog(' Configuration item '||v_partial.name||'('||v_partial.item_id||')'||' '||v_partial.status||' for order number '||v_partial.order_number, 1);
3727 END LOOP;
3728
3729 WriteToLog('Please go through the log file for details on error and action required for each configuration item in error.', 1);
3730
3731 WriteToLog('+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++', 1);
3732 WriteToLog('+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++', 1);
3733 WriteToLog('+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++', 1);
3734
3735 EXCEPTION
3736 WHEN OTHERS THEN
3737 WriteToLog('Others error in Write_Config_Status::'||l_stmt_num||'::'||sqlerrm, 1);
3738 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3739
3740 END Write_Config_Status;
3741
3742
3743 --start bugfix 3377963
3744 --Checks if a model with CIB attribute 1 or 2 is present under a model
3745 --with a cib attribute 3
3746 PROCEDURE Check_invalid_configurations(
3747 x_return_status out NOCOPY varchar2)
3748
3749 IS
3750
3751 CURSOR c_invalid_configuration IS
3752 SELECT bom_item_type,
3753 wip_supply_type,
3754 config_creation,
3755 config_item_id,
3756 inventory_item_id,
3757 parent_ato_line_id,
3758 ato_line_id,
3759 line_id,
3760 ship_from_org_id
3761 FROM bom_cto_order_lines_upg
3762 WHERE ato_line_id in ( SELECT DISTINCT bupg1.ato_line_id
3763 FROM bom_cto_order_lines_upg bupg1
3764 WHERE bupg1.config_creation = 3);
3765
3766 TYPE r_bcolupg IS RECORD
3767 (
3768 bom_item_type number,
3769 wip_supply_type number,
3770 config_creation number,
3771 config_item_id number,
3772 inventory_item_id number,
3773 parent_ato_line_id number,
3774 ato_line_id number,
3775 line_id number,
3776 status number, -- 0 : fine --1 : error
3777 ship_from_org_id number
3778 );
3779
3780
3781 TYPE bcol_upg_tbl_type IS TABLE OF r_bcolupg index by binary_integer;
3782
3783 t_bcol bcol_upg_tbl_type;
3784
3785 TYPE number_arr_tbl_type IS TABLE OF number index by binary_integer;
3786
3787 t_ato_line_id number_arr_tbl_type;
3788
3789 k number;
3790 i number;
3791 l_stmt_num number;
3792
3793
3794 BEGIN
3795 l_stmt_num := 10;
3796 x_return_status := FND_API.G_RET_STS_SUCCESS;
3797
3798
3799 l_stmt_num := 20;
3800 FOR c_config in c_invalid_configuration
3801 LOOP
3802 k := c_config.line_id;
3803
3804 t_bcol(k).bom_item_type := c_config.bom_item_type;
3805 t_bcol(k).wip_supply_type := c_config.wip_supply_type;
3806 t_bcol(k).config_creation := c_config.config_creation;
3807 t_bcol(k).config_item_id := c_config.config_item_id;
3808 t_bcol(k).inventory_item_id := c_config.inventory_item_id;
3809 t_bcol(k).parent_ato_line_id := c_config.parent_ato_line_id ;
3810 t_bcol(k).ato_line_id := c_config.ato_line_id ;
3811 t_bcol(k).line_id := c_config.line_id ;
3812 t_bcol(k).status := 0;
3813 t_bcol(K).ship_from_org_id := c_config.ship_from_org_id;
3814
3815 END LOOP;
3816
3817
3818 --CHECKING for invalid configuration
3819 l_stmt_num := 30;
3820 i := t_bcol.first ;
3821
3822 l_stmt_num := 40;
3823 while i is not null
3824 loop
3825 if( t_bcol(i).bom_item_type = 1
3826 and
3827 nvl(t_bcol(i).wip_supply_type, 1 ) <> 6
3828 and
3829 t_bcol(i).config_creation in (1, 2)
3830 and
3831 t_bcol(t_bcol(i).ato_line_id).status = 0 ) then
3832
3833 if( t_bcol(t_bcol(i).parent_ato_line_id).config_creation = 3) then
3834
3835 t_bcol(t_bcol(i).ato_line_id).status := 1;
3836
3837 l_stmt_num := 50;
3838 IF t_ato_line_id.count = 0 THEN
3839 t_ato_line_id(1) := t_bcol(i).ato_line_id;
3840 ELSE
3841 t_ato_line_id( t_ato_line_id.last+1) := t_bcol(i).ato_line_id;
3842 END IF; --t_ato_line_id
3843
3844 IF PG_DEBUG <> 0 THEN
3845 oe_debug_pub.add('Check_invalid_configurations: ' ||
3846 'INVALID MODEL SETUP exists for line id ' ||t_bcol(i).line_id
3847 || ' model item ' || t_bcol(i).inventory_item_id
3848 || ' Ship from org'||t_bcol(i).ship_from_org_id
3849 || ' item config creation ' || t_bcol(i).config_creation
3850 || ' parent line id ' || t_bcol(t_bcol(i).parent_ato_line_id).line_id
3851 || ' parent model item ' || t_bcol(t_bcol(i).parent_ato_line_id).inventory_item_id
3852 || ' parent config_creation ' || t_bcol(t_bcol(i).parent_ato_line_id).config_creation
3853 , 1 );
3854
3855 END IF; --oe debug
3856
3857
3858 end if; --config_creation = 3
3859
3860 end if ;
3861
3862 l_stmt_num := 60;
3863 i := t_bcol.next(i) ;
3864
3865 end loop ;
3866
3867 l_stmt_num := 70;
3868 IF t_ato_line_id.count = 0 THEN
3869 oe_debug_pub.add('There are NO invalid configurations',5);
3870
3871 ELSIF t_ato_line_id.count > 0 THEN
3872 l_stmt_num := 75;
3873 oe_debug_pub.add('There are ' || t_ato_line_id.count ||'top ato models with invalid configurations');
3874
3875 l_stmt_num := 80;
3876 FORALL j IN t_ato_line_id.first..t_ato_line_id.last
3877 UPDATE bom_cto_order_lines_upg
3878 SET status = 'ERROR'
3879 WHERE ato_line_id = t_ato_line_id(j);
3880
3881 l_stmt_num:= 90;
3882 oe_debug_pub.add('Updated '||sql%rowcount||'lines with error status',1);
3883
3884 END IF;
3885
3886
3887 EXCEPTION
3888
3889 WHEN OTHERS THEN
3890 WriteToLog('ERROR: Unexp error in CTO_Update_Configs_Pk.Check_invalid_configurations:: '|| l_stmt_num ||'::'||sqlerrm, 1);
3891 WriteToLog('+++++++++++++++++++++++++++++++++++++++++++++++++++');
3892 WriteToLog('Update Existing Configurations completed with ERROR');
3893 WriteToLog('+++++++++++++++++++++++++++++++++++++++++++++++++++');
3894 WriteToLog(' error in Check_invalid_configurations::'||sqlerrm, 1);
3895 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3896
3897
3898
3899 END Check_invalid_configurations;
3900
3901 --bugfix 3259017
3902 --added no copy to out variables
3903 Procedure update_atp_attributes(
3904 p_item IN Number,
3905 p_cat_id IN Number,
3906 p_config_id IN Number,
3907 x_return_status OUT NOCOPY varchar2,
3908 x_msg_data OUT NOCOPY Varchar2,
3909 x_msg_count OUT NOCOPY Number) is
3910 Begin
3911 WriteToLog(' Entering Update_atp_attributes procedure');
3912 If p_item = 1 Then
3913
3914 -- Update based on all models
3915
3916 update mtl_system_items_b msic
3917 set (atp_components_flag,atp_flag) = (select CTO_CONFIG_ITEM_PK.evaluate_atp_attributes(nvl(msim.atp_flag,'N'),nvl(msim.atp_components_flag,'N')),CTO_CONFIG_ITEM_PK.get_atp_flag
3918 from mtl_system_items_b msim
3919 where msim.inventory_item_id = msic.base_item_id
3920 and msim.organization_id = msic.organization_id)
3921
3922 where msic.base_item_id is not null
3923 and 'x'= (select 'x'
3924 from mtl_system_items_b msim1
3925 where msim1.inventory_item_id = msic.base_item_id
3926 and msim1.organization_id = msic.organization_id);
3927
3928
3929
3930 WriteToLog(' Number of records updated = '||sql%rowcount);
3931
3932 elsif p_item = 2 then
3933 -- update based on the category id
3934 update mtl_system_items_b msic
3935 set (atp_components_flag,atp_flag) = (select CTO_CONFIG_ITEM_PK.evaluate_atp_attributes(nvl(msim.atp_flag,'N'),nvl(msim.atp_components_flag,'N')),CTO_CONFIG_ITEM_PK.get_atp_flag
3936 from mtl_system_items_b msim
3937 where msim.inventory_item_id = msic.base_item_id
3938 and msim.organization_id = msic.organization_id)
3939 where msic.inventory_item_id in (select msi.inventory_item_id
3940 from mtl_system_items_b msi,
3941 mtl_item_categories mcat
3942 where msi.base_item_id = mcat.inventory_item_id
3943 and mcat.category_id = p_cat_id)
3944 and exists (select 'x' from mtl_system_items_b msim
3945 where msim.inventory_item_id = msic.base_item_id
3946 and msim.organization_id = msic.organization_id);
3947
3948 WriteToLog(' Number of records updated = '||sql%rowcount);
3949
3950 elsif p_item = 3 then
3951 -- update based on config item
3952 update mtl_system_items_b msic
3953 set (atp_components_flag,atp_flag) = (select CTO_CONFIG_ITEM_PK.evaluate_atp_attributes(msim.atp_flag,msim.atp_components_flag),CTO_CONFIG_ITEM_PK.get_atp_flag
3954 from mtl_system_items_b msim
3955 where msim.inventory_item_id = msic.base_item_id
3956 and msim.organization_id = msic.organization_id)
3957 where msic.inventory_item_id = p_config_id
3958 and exists (select 'x' from mtl_system_items_b msim
3959 where msim.inventory_item_id = msic.base_item_id
3960 and msim.organization_id = msic.organization_id);
3961 WriteToLog(' Number of records updated = '||sql%rowcount);
3962 end if;
3963
3964 End Update_atp_attributes;
3965
3966 --end bugfix 3377963
3967
3968
3969
3970 END CTO_UPDATE_CONFIGS_PK;