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