1 package body CTO_MATCH_CONFIG as
2 /* $Header: CTOMCFGB.pls 120.6.12010000.2 2008/08/14 11:33:11 ntungare ship $ */
3
4 /*============================================================================+
5 | Copyright (c) 1993 Oracle Corporation Belmont, California, USA |
6 | All rights reserved. |
7 | Oracle Manufacturing |
8 +=============================================================================+
9 | |
10 | FILE NAME : CTOMCFGB.pls |
11 | DESCRIPTION: |
12 | This file creates packaged functions that check for matching |
13 | configurations and insert unique configurations into |
14 | BOM_ATO_CONFIGURATIONS. |
15 | |
16 | check_config_match - checks BOM_ATO_CONFIGURATIONS for |
17 | configurations that match the ordered configuration. It |
18 | is called from the Match Configuration Workflow activity |
19 | and from the Create Configuration batch process. |
20 | |
21 | can_configurations - inserts unique configurations into |
22 | BOM_ATO_CONFIGURATIONS. It is called from the Create |
23 | Configuration batch process and the Create Configuration |
24 | Item and BOM workflow activity. |
25 | |
26 | To Do: Handle Errors. Need to discuss with Usha and Girish what |
27 | error information to include in Notification. |
28 | |
29 | HISTORY : |
30 | May 10, 99 Angela Makalintal Initial version |
31 | |
32 | 2/23/01 SBHASKAR Bugfix 1553467 |
33
34 |
35 |
36 |
37 |
38 | 2/31/03 SSAWANT BugFix 2789771 |
39 | A fundamental bug for matching was fixed. |
40 | This happens due to the new |
41 | Multiple Instantiation |
42 | feature for all levels of ATO Models |
43 | introduced in current DMF, CZ 11.5.9 |
44 | |
45 | |
46 | |
47 | 7/02/03 KSARKAR Bugfix 2986192 |
48 |
49 |
50 | Modified on 14-MAR-2003 By Sushant Sawant
51 | Decimal-Qty Support for Option Items
52 |
53 |09/05/03 Kiran Konada chnages for patchset-J
54 |
55 |
56 |||09-10-2003 Kiran Konada
57 |
58 | bugfix 3070429,3124169
59 | pragtion bugfix #3143556
60 |
61 |
62 | After a call to create item , a new call is added to
63 | CTO_ENI_WRAPPER.CTO_CALL_TO_ENI
64 |
65 | NOTE: CTO_ENI_WRAPPER is maintained in bom Source control and
66 | is owned by ENI team. This is done as part of bugfix 3070429
67 |
68 | Always the main code contains stubbed version and branch has the
69 | a call to file maintained in ENI product top
70 |
71 | Branch is always shipped with ENI
72 |
73 | The above approach was taken as CTO could not directly make a
74 | call to a ENI file. ENI is present from 11.5.4 onwards and
75 | CTO bugfixes can be shipped to all customers since base release
76 | (11.5.2)
77 |
78 | The error messages if any from CTO_CALL_TO_ENI are ignored
79 | decision:Usha Arora,Krishna Bhagvatula,Anuradha subramnian<Kiran Koanda)
80 | As CTO should not error out in its process becuase of failure in inserting
81 | in DBI atbles used for intelligence
82 |
83 |
84 | 11-05-2003 Kiran Konada
85 | added following line in evaluate and pop match procedure
86 | to look at custom profile
87 |
88 | l_custom_match_profile := FND_PROFILE.Value('BOM:CUSTOM_MATCH');
89 |
90 | 01-20-2003 Kiran Konada bugfix 3381658
91 |
92 |
93 |
94 | 02/23/2003 Kiran Konada bugfix 3259017
95 | added no copy chnages for new procedures added
96 | as part of 11.5.10
97 |
98 |
99 | 03/04/2004 Kiran Konada bugfix 3443204
100 | Preventing canning for pre-configured item
101 | which has relenish_to_order_lflag set to 'N'
102 |
103 |
104 | 03/26/2004 Kiran Konada 3530054
105 | Added a nvl for wip_supply_type
106 | wip_supply_type is populated as null value during ACC
107 |
108 |
109 | Modified : 13-APR-2004 Sushant Sawant
110 | Fixed Bug 3533192
111 | Similar configurations under different models should result in same config item
112 |04/26/2004 Kiran Konada 3503764
113 | re-use shhould not happen for a ware-house change
114 |
115 |05/03/2004 Kiran Konada 3503764
116 | reuse sql for 3503764 needs to comapre th ware house change
117 | only for models
118 | Hence added additional where conditions
119 | bom_item_type=1 and wip_supply_type<>6
120 |
121 |05/17/2004 Kiran Konada 3555026
122 |
123 | --null value in config_orgs should be treated as
124 | based on sourcing
125 | --When ATP passes null in ship_from_org_id, we should
126 | NOT default to any other organization
127 | AS that org could be a ware house on SO pad
128 | during intial scheduling and hence bcol could have
129 | the data AND would create a problem in re-use,
130 | as configitem is reused if ware house is same
131 | before and after re-scheduling
132 | --To get match attribute using validation_org as
133 | ship_from_org_id can be null
134 |
135 |07/07/2004 Kiran Konada bugfix 3745659
136 | added delete before insert into BCOL_GT
137 |
138 |
139 +=============================================================================*/
140
141 /****************************************************************************
142 Procedure: Match_and_create_all_items
143 Parameters: p_model_line_id - line id of the top model in
144 oe_order_lines_all
145 x_return_status - return status
146 x_msg_count
147 x_msg_data
148
149 Description: This function looks for a configuration in
150 bom_ato_configurations that matches the ordered
151 configuration in oe_order_lines_all.
152
153 *****************************************************************************/
154 PG_DEBUG Number := NVL(FND_PROFILE.value('ONT_DEBUG_LEVEL'), 0);
155 --PG_DEBUG Number := 5;
156
157 TYPE reuse_rec_type is RECORD (
158 line_id Number,
159 parent_ato_line_id Number,
160 reuse_config VARCHAR2(1));
161
162 TYPE reuse_tbl_rec_type is TABLE OF reuse_rec_type INDEX BY Binary_integer;
163
164 TYPE Number1_arr_tbl_type is TABLE of Number INDEX BY Binary_integer;
165
166 g_reuse_tbl reuse_tbl_rec_type;
167 g_model_line_tbl Number1_arr_tbl_type;
168
169
170 procedure match_and_create_all_items(
171 pModelLineId in number, -- ato line id
172 xReturnStatus out NOCOPY varchar2,
173 xMsgCount out NOCOPY number,
174 xMsgData out NOCOPY varchar2
175 )
176
177 IS
178
179 l_x_error_msg varchar2(100);
180 l_x_msg_name varchar2(30);
181 l_x_table_name varchar2(30);
182 lStatus number;
183 lXConfigId number;
184 lPerformMatch varchar2(1);
185 lStmtNum number := 0;
186 lFlowCalc number := 1;
187 l_custom_match_profile varchar2(10);
188
189 cursor c_model_lines is
190 --select perform_match, line_id, parent_ato_line_id, inventory_item_id
191 select line_id, parent_ato_line_id, inventory_item_id
192 from bom_cto_order_lines
193 where bom_item_type = 1
194 --and top_model_line_id = pModelLineId -- top model
195 and ato_line_id = pModelLineId
196 and nvl(wip_supply_type,0) <> 6
197 and config_item_id is null -- do we need this in case on-line match
198 and ato_line_id is not null -- could be a PTO
199 order by plan_level desc;
200
201 --start bugfix 3070429,3124169
202 l_eni_star_record CTO_ENI_WRAPPER.STAR_REC_TYPE;
203 eni_return_status VARCHAR2(1);
204
205 --end bugfix 3070429,3124169
206
207 BEGIN
208
209 gUserId := nvl(Fnd_Global.USER_ID, -1) ;
210 gLoginId := nvl(Fnd_Global.LOGIN_ID, -1);
211
212 xReturnStatus := FND_API.G_RET_STS_SUCCESS;
213
214
215 IF PG_DEBUG <> 0 THEN
216 oe_debug_pub.add('match_and_create_all_items: ' || 'In Match_and_Create_all_Items for ato_line_id '
217 || to_char(pModelLineId), 1);
218 END IF;
219
220 l_custom_match_profile := FND_PROFILE.Value('BOM:CUSTOM_MATCH');
221 IF PG_DEBUG <> 0 THEN
222 oe_debug_pub.add('match_and_create_all_items: ' || 'CUSTOM_MATCH: ' || l_custom_match_profile, 1);
223 END IF;
224
225 /*-----------------------------------------------------+
226 This is the loop that traverses bom_cto_order_lines
227 to match each configured assembly. If an assembly
228 does not have a match, a new item is created. If
229 it does have a match, we make create that item
230 in all the sourcing organizations if it does not exist.
231 +-----------------------------------------------------*/
232 for lNextRec in c_model_lines loop
233
234 lXConfigId := NULL;
235
236 select perform_match
237 into lPerformMatch
238 from bom_cto_order_lines
239 where line_id = lNextRec.line_id;
240
241 IF PG_DEBUG <> 0 THEN
242 oe_debug_pub.add('match_and_create_all_items: ' || 'Match_and_create_all_items: Processing line_id '
243 || to_char(lNextRec.line_id) ||
244 ' with perform_match value ' ||
245 lPerformMatch, 1);
246 END IF;
247
248 if (lPerformMatch = 'N') then
249
250 lStmtNum := 100;
251
252 lStatus := CTO_CONFIG_ITEM_PK.create_item(
253 pModelId => lNextRec.inventory_item_id, -- Model
254 PLineId => lNextRec.line_id,
255 pConfigId => lXConfigId,
256 xMsgCount => xMsgCount,
257 xMsgData => xMsgData
258 );
259
260 IF PG_DEBUG <> 0 THEN
261 oe_debug_pub.add('match_and_create_all_items: ' ||
262 'Returned from create_item in stmt num 100 with status ' ||
263 to_char(lStatus), 1);
264 END IF;
265
266 if (lStatus = 0) then
267
268 raise fnd_api.g_exc_error;
269
270 elsif lStatus =1 then
271 --start bugfix 3070429,3124169
272
273 l_eni_star_record.inventory_item_id := lXConfigId;
274
275 IF PG_DEBUG <> 0 THEN
276 oe_debug_pub.add('match_and_create_all_items: ' || 'conifg item id passed to ENI=>'||
277 l_eni_star_record.inventory_item_id , 5);
278 END IF;
279
280 --follwoing API is maintained by PLM,DBI team present in Bom source control
281 CTO_ENI_WRAPPER.CTO_CALL_TO_ENI
282 (p_api_version => 1.0,
283 p_star_record => l_eni_star_record,
284 x_return_status =>eni_return_status,
285 x_msg_count => xMsgCount,
286 x_msg_data => xMsgData);
287
288
289 --return status passed as 'S' and not as FND_API.XXXXX
290 --CTO has decided not to fail for error messages but just log messages
291 --refer bug 3124169 for more info
292 IF eni_return_status = 'S' THEN
293 IF PG_DEBUG <> 0 THEN
294 oe_debug_pub.add('match_and_create_all_items:' || 'ENI_CONFIG_ITEMS_PKG.Create_config_items returned=>'
295 ||eni_return_status, 5);
296 END IF;
297 ELSE
298 IF PG_DEBUG <> 0 THEN
299 oe_debug_pub.add('match_and_create_all_items: ' || 'ENI_CONFIG_ITEMS_PKG.Create_config_items returned=>'
300 ||eni_return_status, 1);
301 oe_debug_pub.add('match_and_create_all_items: ' || 'IGNORING ABOVE ERROR', 1);
302 END IF;
303
304 END IF;
305
306 --end bugfix 3070429,3124169
307
308 end if;
309
310 lStmtNum := 110;
311 lStatus := can_configurations(
312 lNextRec.line_id,
313 0,
314 0,
315 0,
316 gUserId,
317 gLoginId,
318 l_x_error_msg,
319 l_x_msg_name);
320
321 IF PG_DEBUG <> 0 THEN
322 oe_debug_pub.add('match_and_create_all_items: ' ||
323 'Returned from canning in stmt num 110 with status '
324 || to_char(lStatus), 1);
325 END IF;
326
327 if (lStatus = 1) then
328
329 begin
330
331 update bom_cto_order_lines
332 set perform_match = 'N'
333 where line_id = lNextRec.parent_ato_line_id
334 and perform_match = 'Y';
335 -- if the update fails, its not an error
336
337 end;
338
339 else
340
341 raise fnd_api.g_exc_error;
342
343 end if; -- end lStatus = 1
344
345 else
346
347 lStmtNum := 120;
348 IF l_custom_match_profile = 2 THEN
349 IF PG_DEBUG <> 0 THEN
350 oe_debug_pub.add('match_and_create_all_items: ' || 'Standard Match.', 1);
351 END IF;
352 lStatus := check_config_match(
353 lNextRec.line_id,
354 lXConfigId,
355 l_x_error_msg,
356 l_x_msg_name);
357
358 IF PG_DEBUG <> 0 THEN
359 oe_debug_pub.add('match_and_create_all_items: ' || 'Returned from check_config_match with status '
360 || to_char(lStatus), 1);
361 END IF;
362
363 if lStatus <> 1 then
364 raise fnd_api.g_exc_error;
365 end if;
366 ELSE
367 IF PG_DEBUG <> 0 THEN
368 oe_debug_pub.add('match_and_create_all_items: ' || 'Custom Match.', 1);
369 END IF;
370 lStatus := CTO_CUSTOM_MATCH_PK.find_matching_config(
371 pModelLineId => lNextRec.line_id,
372 xMatchedItemId => lXConfigId,
373 xErrorMessage => l_x_error_msg,
374 xMessageName => l_x_msg_name,
375 xTableName => l_x_table_name);
376
377 IF PG_DEBUG <> 0 THEN
378 oe_debug_pub.add('match_and_create_all_items: ' || 'Returned from find_matching_config with status '
379 || to_char(lStatus), 1);
380 END IF;
381
382 if lStatus <> 1 then
383 raise fnd_api.g_exc_error;
384 end if;
385
386 END IF;
387
388 if (lStatus = 1 and lXConfigId is null) then
389
390 IF PG_DEBUG <> 0 THEN
391 oe_debug_pub.add('match_and_create_all_items: ' || 'No match for line ' ||
392 to_char(lNextRec.line_id), 1);
393 END IF;
394
395 lStmtNum := 130;
396
397 lStatus := CTO_CONFIG_ITEM_PK.create_item(
398 pModelId => lNextRec.inventory_item_id, -- Model
399 pLineId => lNextRec.line_id,
400 pConfigId => lXConfigId,
401 xMsgCount => xMsgCount,
402 xMsgData => xMsgData);
403
404 IF PG_DEBUG <> 0 THEN
405 oe_debug_pub.add('match_and_create_all_items: ' ||
406 'Returned from create_item in stmt num 130 with status '
407 || to_char(lStatus), 1);
408 END IF;
409
410 if (lStatus <> 1) then
411
412 raise fnd_api.g_exc_error;
413
414 else
415 --start bugfix 3070429,3124169
416
417 l_eni_star_record.inventory_item_id := lXConfigId;
418 IF PG_DEBUG <> 0 THEN
419 oe_debug_pub.add('match_and_create_all_items: ' || 'conifg item id passed to ENI=>'||
420 l_eni_star_record.inventory_item_id , 5);
421 END IF;
422
423 --follwoing API is maintained by PLM,DBI team present in Bom source control
424 CTO_ENI_WRAPPER.CTO_CALL_TO_ENI
425 (p_api_version => 1.0,
426 p_star_record => l_eni_star_record,
427 x_return_status =>eni_return_status,
428 x_msg_count => xMsgCount,
429 x_msg_data => xMsgData);
430
431
432 --return status passed as 'S' and not as FND_API.XXXXX
433 --CTO has decided not to fail for error messages but just log messages
434 --refer bug 3124169 for more info
435 IF eni_return_status = 'S' THEN
436 IF PG_DEBUG <> 0 THEN
437 oe_debug_pub.add('match_and_create_all_items:' || 'ENI_CONFIG_ITEMS_PKG.Create_config_items returned=>'
438 ||eni_return_status, 5);
439 END IF;
440 ELSE
441 IF PG_DEBUG <> 0 THEN
442 oe_debug_pub.add('match_and_create_all_items: ' || 'ENI_CONFIG_ITEMS_PKG.Create_config_items returned=>'
443 ||eni_return_status, 1);
444 oe_debug_pub.add('match_and_create_all_items: ' || 'IGNORING ABOVE ERROR', 1);
445 END IF;
446
447 END IF;
448
449 --end bugfix 3070429,3124169
450
451
452 end if;
453
454 lStmtNum := 140;
455 lStatus := can_configurations(
456 lNextRec.line_id,
457 0,
458 0,
459 0,
460 gUserId,
461 gLoginId,
462 l_x_error_msg,
463 l_x_msg_name);
464
465 IF PG_DEBUG <> 0 THEN
466 oe_debug_pub.add('match_and_create_all_items: ' ||
467 'Returned from canning in stmt num 140 with status '
468 || to_char(lStatus), 1);
469 END IF;
470
471 if (lStatus <> 1) then
472
473 raise fnd_api.g_exc_error;
474 end if;
475
476 begin
477
478 update bom_cto_order_lines
479 set perform_match = 'N'
480 where line_id = lNextRec.parent_ato_line_id
481 and perform_match = 'Y';
482 -- if the update fails, its not an error
483
484 end;
485
486 elsif (lStatus = 1 and lXConfigId is not null) then
487
488 IF PG_DEBUG <> 0 THEN
489 oe_debug_pub.add('match_and_create_all_items: ' || 'Match found for line ' ||
490 to_char(lNextRec.line_id) ||
491 ' with config item ' ||
492 to_char(lXConfigId), 1);
493 END IF;
494
495 /*bugfix 2986192 */
496 gMatch := 1;
497 IF PG_DEBUG <> 0 THEN
498 oe_debug_pub.add('Value of gMatch ' ||gMatch,1);
499 END IF;
500
501 lStmtNum := 150;
502 lStatus := CTO_CONFIG_ITEM_PK.create_item(
503 pModelId => lNextRec.inventory_item_id, -- Model
504 pLineId => lNextRec.line_id,
505 pConfigId => lXConfigId,
506 xMsgCount => xMsgCount,
507 xMsgData => xMsgData);
508
509 IF PG_DEBUG <> 0 THEN
510 oe_debug_pub.add('match_and_create_all_items: ' ||
511 'Returned from create_item in stmt num 150 with status '
512 || to_char(lStatus), 1);
513 END IF;
514
515 if (lStatus = 0) then
516
517 raise fnd_api.g_exc_error;
518
519 else
520 --start bugfix 3070429,3124169
521
522 l_eni_star_record.inventory_item_id := lXConfigId;
523
524 IF PG_DEBUG <> 0 THEN
525 oe_debug_pub.add('match_and_create_all_items: ' || 'conifg item id passed to ENI=>'||
526 l_eni_star_record.inventory_item_id , 5);
527 END IF;
528
529 --follwoing API is maintained by PLM,DBI team present in Bom source control
530 CTO_ENI_WRAPPER.CTO_CALL_TO_ENI
531 (p_api_version => 1.0,
532 p_star_record => l_eni_star_record,
533 x_return_status =>eni_return_status,
534 x_msg_count => xMsgCount,
535 x_msg_data => xMsgData);
536
537
538 --return status passed as 'S' and not as FND_API.XXXXX
539 --CTO has decided not to fail for error messages but just log messages
540 --refer bug 3124169 for more info
541 IF eni_return_status = 'S' THEN
542 IF PG_DEBUG <> 0 THEN
543 oe_debug_pub.add('match_and_create_all_items:' || 'ENI_CONFIG_ITEMS_PKG.Create_config_items returned=>'
544 ||eni_return_status, 5);
545 END IF;
546 ELSE
547 IF PG_DEBUG <> 0 THEN
548 oe_debug_pub.add('match_and_create_all_items: ' || 'ENI_CONFIG_ITEMS_PKG.Create_config_items returned=>'
549 ||eni_return_status, 1);
550 oe_debug_pub.add('match_and_create_all_items: ' || 'IGNORING ABOVE ERROR', 1);
551 END IF;
552
553 END IF;
554
555 --end bugfix 3070429,3124169
556
557 end if;
558
559 else
560
561 raise fnd_api.g_exc_error;
562
563 end if; -- end lStatus = 1 and lXConfigID is not null
564
565 end if; -- end lNextRec.Perform_match
566
567 lStmtNum := 160;
568 update bom_cto_order_lines
569 set config_item_id = lXConfigId
570 where line_id = lNextRec.line_id;
571
572 end loop;
573
574 -- Get message count and data
575 cto_msg_pub.count_and_get
576 ( p_msg_count => xMsgCount
577 , p_msg_data => xMsgData
578 );
579
580 EXCEPTION
581
582 WHEN fnd_api.g_exc_error THEN
583 IF PG_DEBUG <> 0 THEN
584 oe_debug_pub.add('match_and_create_all_items: ' || 'Exception in stmt num: ' || to_char(lStmtNum), 1);
585 END IF;
586 xReturnStatus := FND_API.G_RET_STS_ERROR;
587 -- Get message count and data
588 cto_msg_pub.count_and_get
589 ( p_msg_count => xMsgCount
590 , p_msg_data => xMsgData
591 );
592 WHEN fnd_api.g_exc_unexpected_error THEN
593 IF PG_DEBUG <> 0 THEN
594 oe_debug_pub.add('match_and_create_all_items: ' || ' Unexpected Exception in stmt num: ' || to_char(lStmtNum), 1);
595 END IF;
596 xReturnStatus := FND_API.G_RET_STS_UNEXP_ERROR ;
597 -- Get message count and data
598 cto_msg_pub.count_and_get
599 ( p_msg_count => xMsgCount
600 , p_msg_data => xMsgData
601 );
602
603 WHEN OTHERS then
604 oe_debug_pub.add('errmsg'||sqlerrm);
605 IF PG_DEBUG <> 0 THEN
606 oe_debug_pub.add('match_and_create_all_items: ' || 'Others Exception in stmt num: ' || to_char(lStmtNum), 1);
607 END IF;
608 xReturnStatus := FND_API.G_RET_STS_UNEXP_ERROR;
609 -- Get message count and data
610 cto_msg_pub.count_and_get
611 ( p_msg_count => xMsgCount
612 , p_msg_data => xMsgData
613 );
614
615 END match_and_create_all_items;
616
617
618 /*****************************************************************************
619 Function: check_config_match
620 Parameters: pModelLineId - line id of the top model in oe_order_lines_all
621 x_match_config_id - config id of the matching configuration
622 from bom_ato_configurations
623 x_error_message - error message if match function fails
624 x_message_name - name of error message if match
625 function fails
626
627 Description: This function looks for a configuration in
628 bom_ato_configurations that matches the ordered
629 configuration in oe_order_lines_all.
630
631 Bugfix 1553467 : If an ATO model is part of a PTO model (see fig below),
632 then, the link_to_line_id of ATO model will be the line_id of
633 the PTO model. Since the PTO options are not inserted in
634 bom_cto_order_lines, the condition
635 "and colp.line_id = nvl(col1.link_to_line_id, col1.line_id)"
636 will fail.
637 Removed "colp" from the FROM clause and added a new condition after
638 commenting the old. Search on 1553467.
639 PTO-MODEL-1
640 ... ATO-MODEL-1
641 ......ATO-OPTCLASS-1
642 .........OPTION-1
643 .........OPTION-2
644 ......OPTION-3
645
646 08-AUG-2003 Kiran Konada
647 chnaged the code to use BCOL_TEMP instead of BCOL for patchser J
648
649
650
651 *****************************************************************************/
652
653
654 function check_config_match(
655 p_model_line_id in number,
656 x_config_match_id out NOCOPY number,
657 x_error_message out NOCOPY VARCHAR2, /* 70 bytes to hold msg */
658 x_message_name out NOCOPY VARCHAR2 /* 30 bytes to hold name */
659 )
660 RETURN integer
661
662 IS
663
664 l_stmt_num number;
665 l_cfm_value number;
666
667 PARAMETER_ERROR EXCEPTION;
668
669 /* (FP 4895615) 4526218: Added the following variables*/
670 l_start_time date;
671 diff number;
672 l_component_sum number;
673 l_component_count number;
674 l_base_model_id number;
675
676
677 BEGIN
678
679 /**************************************************************
680 * Check BOM_ATO_CONFIGURATIONS for a configuration that matches the
681 * ordered configuration in oe_order_lines_all.
682 ****************************************************************/
683
684
685 oe_debug_pub.add('entered Check_config_match=>' );
686
687 l_stmt_num := 0;
688 if (p_model_line_id is NULL) then
689 raise PARAMETER_ERROR;
690 end if;
691
692 /*******************************************************************
693 As part of (FP 4895615)
694 base bug 4526218 doesnot work for multi-level match.SO,corrected the fix
695 in FP. Correction is the usage of decode function. Abhimanyu, will obsolete
696 the bug 4526218 and create a new patch for it.
697
698 Bug4526218 begin: performance issue- Broke the match sql into two parts.
699 The first sql shall insert into bom_ato_configs_temp the "approximate"
700 matching configurations. For "approximate" match, it must have the same
701 count of components and the sum of component item ids must be equal.
702
703 The second sql shall work on the filtered set of probable match candidate
704 configs to determine if there is any extra component in the order or in
705 the config or whether the config has been deactivated in some orgs.
706 ********************************************************************/
707
708 l_start_time := sysdate;
709
710 IF PG_DEBUG <> 0 THEN
711 oe_debug_pub.add ('check_config_match :: start time : '||to_char(l_start_time, 'MM-DD-YYYY HH24:MI:SS'));
712 END IF;
713
714 l_stmt_num := 100;
715 delete bom_ato_configs_temp;
716
717 l_stmt_num := 110;
718 select count(*), sum( nvl( decode(line_id, p_model_line_id, inventory_item_id, config_item_id),
719 inventory_item_id
720 )
721 )
722 into l_component_count, l_component_sum
723 from bom_cto_order_lines_gt
724 where parent_ato_line_id = p_model_line_id
725 or line_id = p_model_line_id;
726
727
728
729 l_stmt_num := 120;
730 select inventory_item_id
731 into l_base_model_id
732 from bom_cto_order_lines_gt
733 where line_id = p_model_line_id;
734
735 IF PG_DEBUG <> 0 THEN
736 oe_debug_pub.add('l_component_count = '||l_component_count ||' l_component_sum = '||l_component_sum);
737 oe_debug_pub.add(' l_base_model_id = '||l_base_model_id||' p_model_line_id = '||p_model_line_id);
738 END IF;
739
740 l_stmt_num := 130;
741 --
742 -- bug 7203643
743 -- modified the SQL query to make use of the Exists clause
744 -- instead of the IN operator
745 -- ntungare
746 --
747 /*
748 insert into bom_ato_configs_temp(
749 config_item_id,
750 organization_id,
751 base_model_id,
752 component_item_id,
753 component_code,
754 component_quantity)
755 select bac1.config_item_id,
756 bac1.organization_id,
757 bac1.base_model_id,
758 bac1.component_item_id,
759 bac1.component_code,
760 bac1.component_quantity
761 from bom_ato_configurations bac1
762 where bac1.config_item_id in (
763 select config_item_id
764 from BOM_ATO_CONFIGURATIONS bac3
765 where bac3.base_model_id = l_base_model_id
766 group by bac3.config_item_id
767 having count(*) = l_component_count
768 and sum(component_item_id) = l_component_sum
769 )
770 and bac1.component_item_id = bac1.base_model_id; --6086540: load just 1 record per config item
771 */
772
773 insert into bom_ato_configs_temp(
774 config_item_id,
775 organization_id,
776 base_model_id,
777 component_item_id,
778 component_code,
779 component_quantity)
780 select /*+ INDEX(BAC1 BOM_ATO_CONFIGURATIONS_N1)*/
781 bac1.config_item_id,
782 bac1.organization_id,
783 bac1.base_model_id,
784 bac1.component_item_id,
785 bac1.component_code,
786 bac1.component_quantity
787 from bom_ato_configurations bac1
788 where bac1.component_item_id = bac1.base_model_id
789 and bac1.base_model_id = l_base_model_id
790 and EXISTS (SELECT 1
791 from BOM_ATO_CONFIGURATIONS bac3
792 where bac3.base_model_id = l_base_model_id
793 and bac1.config_item_id = bac3.config_item_id
794 and bac1.base_model_id = bac3.base_model_id
795 group by bac3.config_item_id
796 having count(*) = l_component_count
797 and sum(component_item_id) = l_component_sum);
798
799
800 IF PG_DEBUG <> 0 THEN
801 oe_debug_pub.add ('Rows inserted into gtt :'||sql%rowcount);
802 oe_debug_pub.add ('check_config_match :: after bom_ato_configs_temp insert time : '||to_char(sysdate, 'MM-DD-YYYY HH24:MI:SS'));
803 END IF;
804
805 l_stmt_num := 140;
806
807 -- If the config item is INACTIVE in even one orgn, we will not consider that configuration for matching.
808
809 select /*+ ordered */ bac1.config_item_id -- 6086540: added the ordered hint
810 into x_config_match_id
811 from bom_cto_order_lines_gt col1, /* model */
812 bom_ato_configs_temp bact1, --6086540: use the GTT for filtering based on approx match
813 bom_ato_configurations bac1 --6086540: matching will be done in main table
814 where col1.line_id = p_model_line_id
815 and bac1.base_model_id = col1.inventory_item_id
816 and bact1.base_model_id = col1.inventory_item_id
817 and bac1.component_item_id = col1.inventory_item_id
818 and bac1.config_item_id = bact1.config_item_id
819 and not exists (
820 select 'Config Item is not active in atleast one orgn'
821 from mtl_system_items msi,
822 bom_parameters bp
823 where msi.organization_id = bp.organization_id
824 and msi.inventory_item_id = bac1.config_item_id
825 and msi.inventory_item_status_code = nvl(bp.bom_delete_status_code,'NULL')
826 )
827 and not exists
828 (select 'Extra Options in Order'
829 from bom_cto_order_lines_gt col5
830 where (col5.parent_ato_line_id = col1.line_id
831 or col5.line_id = col1.line_id) -- to pick up top model
832 and col5.ordered_quantity > 0
833 and nvl(decode(col5.line_id, col1.line_id, col5.inventory_item_id,
834 col5.config_item_id),
835 col5.inventory_item_id) not in
836 (select bac2.component_item_id
837 from bom_ato_configurations bac2 -- 6086540
838 where bac2.config_item_id = bac1.config_item_id
839 and bac2.component_item_id =
840 decode(col5.config_item_id, NULL,
841 col5.inventory_item_id, decode(col5.line_id, col1.line_id,
842 col5.inventory_item_id, col5.config_item_id))
843 and bac2.component_code =
844 substrb(col5.component_code,
845 instrb(col5.component_code||'-',
846 '-'||to_char(col1.inventory_item_id)||'-')+1)
847 and bac2.component_quantity =
848 Round( nvl(col5.ordered_quantity,0)/ nvl(col1.ordered_quantity,0) , 7 ) /* Decimal-Qty Support for Option Items */
849 )
850 )
851 and not exists /* Added due to Multiple Instantiation */
852 ( select 'Extra Options in Config' from bom_ato_configurations bac9 -- 6086540
853 where bac9.config_item_id = bac1.config_item_id /* v_config_item_id */
854 and ( bac9.component_item_id , bac9.component_quantity )
855 not in
856 ( select decode( col1.line_id , col9.line_id, col9.inventory_item_id ,
857 nvl( col9.config_item_id, col9.inventory_item_id )),
858 Round( nvl( col9.ordered_quantity, 0)/nvl( col1.ordered_quantity, 0 ), 7 ) /* Decimal-Qty Support for Option Items */
859 from bom_cto_order_lines_gt col9
860 where col9.parent_ato_line_id = col1.line_id or col9.line_id = col1.line_id
861 )
862 )
863 and rownum = 1;
864
865 if (x_config_match_id is not NULL) then
866 IF PG_DEBUG <> 0 THEN
867 oe_debug_pub.add('matched item=>'|| x_config_match_id,5);
868 END IF;
869
870 --Begin bugfix 7203643
871 declare
872
873 record_locked EXCEPTION;
874 pragma exception_init (record_locked, -54);
875 --l_dummy VARCHAR2(2);
876
877 CURSOR config_rows IS
878 SELECT last_referenced_date
879 FROM bom_ato_configurations
880 WHERE config_item_id = x_config_match_id
881 FOR UPDATE NOWAIT;
882
883 begin
884 l_stmt_num := 110;
885
886 OPEN config_rows;
887 CLOSE config_rows;
888
889 IF PG_DEBUG <> 0 THEN
890 OE_DEBUG_PUB.add ('check_config_match: ' || 'Locked rows.');
891 END IF;
892
893 l_stmt_num := 120;
894
895 update bom_ato_configurations
896 set last_referenced_date = SYSDATE
897 where config_item_id = x_config_match_id;
898
899 exception
900 when record_locked then
901 IF PG_DEBUG <> 0 THEN
902 OE_DEBUG_PUB.add ('check_config_match: ' || 'Could not lock for config id '|| x_config_match_id ||' for update.');
903 OE_DEBUG_PUB.add ('check_config_match: ' || 'This config is being processed by another process. Not updating last_referenced_date.');
904 END IF;
905 end;
906 --End bugfix 7203643
907 end if;
908
909 --start (FP 4895615 )4526218
910 diff := (sysdate - l_start_time)*24*60*60;
911
912 IF PG_DEBUG <> 0 THEN
913 oe_debug_pub.add ('check_config_match :: end time : '||to_char(sysdate, 'MM-DD-YYYY HH24:MI:SS'));
914 oe_debug_pub.add('Time taken : '||diff);
915 END IF;
916 --end (FP 4895615)4526218
917
918 return 1;
919
920 EXCEPTION
921 when PARAMETER_ERROR THEN
922 IF PG_DEBUG <> 0 THEN
923 oe_debug_pub.add ('check_config_match raised PARAMETER_ERROR',1);
924 END IF;
925
926 x_error_message := 'CTO_MATCH_CONFIG.check_config_match' ||
927 'Verify Parameters';
928 x_message_name := 'CTO_MATCH_ERROR';
929 cto_msg_pub.cto_message('BOM','CTO_MATCH_ERROR');
930 return 0;
931
932
933 when NO_DATA_FOUND then
934
935 IF PG_DEBUG <> 0 THEN
936 oe_debug_pub.add ('check_config_match :No data found',1);
937 oe_debug_pub.add ('check_config_match :returning 1,success',1);
938 END IF;
939 return 1;
940
941 when OTHERS THEN
942
943 IF PG_DEBUG <> 0 THEN
944 oe_debug_pub.add ('check_config_match raised OTHERS exception:'||sqlerrm);
945 END IF;
946 x_error_message := 'CTO_MATCH_CONFIG.check_config_match' ||
947 to_char(l_stmt_num) || ':' ||
948 substrb(sqlerrm,1,150);
949 x_message_name := 'CTO_MATCH_ERROR';
950 cto_msg_pub.cto_message('BOM','CTO_MATCH_ERROR');
951 return 0;
952 END;
953
954
955
956 /*****************************************************************************
957 Function: can_configurations
958 Parameters: p_model_line_id - line id of the model in oe_order_lines_all
959 whose
960 configuration will be inserted
961 prg_appid - program application id
962 prg_id - program id
963 req_id - job id
964 user_id - id of user running process
965 login_id - login id
966 x_error_message - error message if match function fails
967 x_message_name - name of error msg if match function fails
968
969 Description: This function inserts the configuration (model and components)
970 into BOM_ATO_CONFIGURATIONS for use when matching a
971 configuration via the Match functionality.
972
973 It is called from the Create Item and BOM batch process.
974
975 Bugfix 1553467 : If an ATO model is part of a PTO model (see fig below),
976 then, the link_to_line_id of ATO model will be the line_id of
977 the PTO model. Since the PTO options are not inserted in
978 bom_cto_order_lines, the condition
979 "and bcolParent.line_id = NVL(bcolModel.link_to_line_id, bcolModel.line_id); "
980 will fail.
981
982 Removed "bcolParent" from the FROM clause and added a new condition after
983 commenting the old. Search on 1553467.
984 PTO-MODEL-1
985 ... ATO-MODEL-1
986 ......ATO-OPTCLASS-1
987 .........OPTION-1
988 .........OPTION-2
989 ......OPTION-3
990 *****************************************************************************/
991 function can_configurations(
992 p_model_line_id in number,
993 prg_appid in number,
994 prg_id in number,
995 req_id in number,
996 user_id in number,
997 login_id in number,
998 error_msg out NOCOPY varchar2,
999 msg_name out NOCOPY varchar2
1000 )
1001 return integer
1002
1003 IS
1004 l_stmt_num number;
1005
1006 PARAMETER_ERROR exception;
1007 INSERT_ERROR exception;
1008
1009 l_ato_flag VARCHAR2(1);
1010 l_cfg_item_id NUMBER;
1011
1012 BEGIN
1013 l_stmt_num := 0;
1014 if (p_model_line_id is NULL) then
1015 raise PARAMETER_ERROR;
1016 end if;
1017
1018
1019
1020
1021 --start bugfix 3443204
1022 --For a pre-configured item , the replenish_to_order_flag
1023 --can be 'N'. We should not can such items
1024 --Checking the flag irrespective of organization as
1025 --we expect the setup for an pre-configured item to be
1026 --same across all orgs
1027 l_stmt_num := 10;
1028 BEGIN
1029 SELECT 'Y'
1030 INTO l_ato_flag
1031 FROM bom_cto_order_lines bcol,
1032 mtl_system_items msi
1033 WHERE bcol.line_id = p_model_line_id
1034 AND bcol.config_item_id = msi.inventory_item_id
1035 AND msi.replenish_to_order_flag = 'Y'
1036 AND rownum =1;
1037 EXCEPTION
1038 WHEN no_data_found THEN
1039 l_ato_flag := 'N';
1040 END ;
1041 --bugfix 3443204
1042
1043 IF l_ato_flag = 'Y' THEN --if clause added for 3443204
1044
1045
1046
1047
1048 /******************************************************************
1049 Insert into BOM_ATO_CONFIGURATIONS the model configuration from
1050 oe_order_lines_all.
1051 ******************************************************************/
1052 l_stmt_num := 100;
1053 insert into BOM_ATO_CONFIGURATIONS(
1054 config_item_id,
1055 organization_id,
1056 base_model_id,
1057 component_item_id,
1058 component_code,
1059 component_quantity,
1060 creation_date,
1061 created_by,
1062 last_update_date,
1063 last_updated_by,
1064 last_update_login,
1065 last_referenced_date,
1066 request_id,
1067 program_application_id,
1068 program_id,
1069 program_update_date)
1070 select bcolModel.config_item_id,
1071 bcolModel.ship_from_org_id,
1072 bcolModel.inventory_item_id,
1073 nvl(decode(bcolOptions.line_id, bcolModel.line_id, bcolOptions.inventory_item_id,
1074 bcolOptions.config_item_id),
1075 bcolOptions.inventory_item_id),
1076 -- bugfix 1553467 begin
1077 substrb(bcolOptions.component_code,
1078 instrb(bcolOptions.component_code||'-',
1079 '-'||to_char(bcolModel.inventory_item_id)||'-')+1),
1080 -- bugfix 1553467 end
1081
1082 /* -- bugfix 1553467 comment begin
1083 decode(bcolModel.link_to_line_id, NULL,
1084 bcolOptions.component_code,
1085 substr(bcolOptions.component_code,
1086 lengthb(bcolParent.component_code)+2)),
1087 -- bugfix 1553467 comment end
1088 */
1089 Round( (bcolOptions.ordered_quantity / bcolModel.ordered_quantity), 7 ) ,
1090 -- qty represents ordered - canclld
1091 /* Decimal-Qty Support for Option Items */
1092 SYSDATE,
1093 user_id,
1094 SYSDATE,
1095 user_id,
1096 login_id,
1097 SYSDATE,
1098 req_id,
1099 prg_appid,
1100 prg_id,
1101 SYSDATE
1102 from
1103 -- bugfix 1553467: bom_cto_order_lines bcolParent, /* Parent of Model, if any */
1104 bom_cto_order_lines bcolModel, /* Model */
1105 bom_cto_order_lines bcolOptions /* Options */
1106 where bcolModel.line_id = p_model_line_id
1107 and (bcolOptions.parent_ato_line_id = bcolModel.line_id or
1108 bcolOptions.line_id = bcolModel.line_id);
1109 --and bcolOptions.ordered_quantity >
1110 -- NVL(bcolOptions.cancelled_quantity, 0)
1111 /*
1112 -- bugfix 1553467 : comment begin
1113 and bcolParent.line_id = NVL(bcolModel.link_to_line_id,
1114 bcolModel.line_id);
1115 -- bugfix 1553467 : comment end
1116 */
1117
1118
1119 if (SQL%ROWCOUNT > 0) then
1120 return 1;
1121 else
1122 raise INSERT_ERROR;
1123 end if;
1124
1125 ELSE --flag is N
1126
1127 IF PG_DEBUG <> 0 THEN
1128 oe_debug_pub.add('can_configurations: ' || 'Not canning the configuration AS', 3);
1129 oe_debug_pub.add('Assemble_to_order_flag is set to N ', 3);
1130
1131
1132
1133 END IF;
1134 return 1;
1135
1136 END IF; --3443204
1137
1138 EXCEPTION
1139
1140 when PARAMETER_ERROR then
1141 -- IF PG_DEBUG <> 0 THEN
1142 oe_debug_pub.add('can_configurations: ' || 'Failed in can_configurations 1. ', 1);
1143 -- END IF;
1144 error_msg := 'CTO_MATCH_CONFIG.can_configurations' ||
1145 to_char(l_stmt_num) || ':' ||
1146 'Verify Parameters';
1147 msg_name := 'CTO_MATCH_ERROR';
1148 -- IF PG_DEBUG <> 0 THEN
1149 oe_debug_pub.add('can_configurations: ' || error_msg, 1);
1150 -- END IF;
1151 cto_msg_pub.cto_message('BOM','CTO_MATCH_ERROR');
1152 return 0;
1153
1154 when INSERT_ERROR then
1155 -- IF PG_DEBUG <> 0 THEN
1156 oe_debug_pub.add('can_configurations: ' || 'Failed in can_configurations 2. ', 1);
1157 -- END IF;
1158 error_msg := 'CTO_MATCH_CONFIG.can_configurations' ||
1159 'Insert Error';
1160 msg_name := 'CTO_MATCH_ERROR';
1161 -- IF PG_DEBUG <> 0 THEN
1162 oe_debug_pub.add('can_configurations: ' || error_msg, 1);
1163 -- END IF;
1164 cto_msg_pub.cto_message('BOM','CTO_MATCH_ERROR');
1165 return 0;
1166
1167 when OTHERS then
1168 oe_debug_pub.add('errmsg'||sqlerrm);
1169 IF PG_DEBUG <> 0 THEN
1170 oe_debug_pub.add('can_configurations: ' || 'Failed in can_configurations 3. ', 1);
1171 END IF;
1172 error_msg := 'CTO_MATCH_CONFIG.can_configurations' ||
1173 to_char(l_stmt_num) || ':' ||
1174 substrb(sqlerrm,1,150);
1175 IF PG_DEBUG <> 0 THEN
1176 oe_debug_pub.add('can_configurations: ' || error_msg, 1);
1177 END IF;
1178 msg_name := 'CTO_MATCH_ERROR';
1179 cto_msg_pub.cto_message('BOM','CTO_MATCH_ERROR');
1180 return 0;
1181
1182
1183 END; /* end can_configurations */
1184
1185
1186 /*-----------------------
1187 This procedure calculates the parent_ato_line_id
1188
1189
1190 ----------------------------*/
1191
1192 PROCEDURE populate_parent_ato(
1193 P_Source in varchar2,
1194 P_tab_of_rec in out NOCOPY CTO_Configured_Item_GRP.TEMP_TAB_OF_REC_TYPE,
1195 x_return_status OUT NOCOPY VARCHAR2,
1196 x_msg_count OUT NOCOPY NUMBER,
1197 x_msg_data OUT NOCOPY VARCHAR2
1198
1199 );
1200
1201
1202
1203 /*--------------------------
1204 trans_tab_of_rec converts rec of tables
1205 structure into table of records
1206
1207 ----------------------------*/
1208
1209 PROCEDURE xfer_tab_to_rec(
1210 p_match_rec_of_tab IN CTO_Configured_Item_GRP.CTO_MATCH_REC_TYPE,
1211 x_tab_of_rec OUT NOCOPY CTO_Configured_Item_GRP.TEMP_TAB_OF_REC_TYPE,
1212 x_return_status OUT NOCOPY VARCHAR2,
1213 x_msg_count OUT NOCOPY NUMBER,
1214 x_msg_data OUT NOCOPY VARCHAR2
1215
1216 )
1217 IS
1218
1219 i NUMBER := 0 ;
1220 j integer ;
1221
1222 l_count number ;
1223 lStmtNum NUMBER;
1224
1225
1226 BEGIN
1227
1228
1229 IF PG_DEBUG <> 0 THEN
1230 oe_debug_pub.add('ENTERED xfer_tab_to_rec',1);
1231 END IF;
1232 x_return_status := FND_API.G_RET_STS_SUCCESS;
1233
1234 lStmtNum :=10;
1235 l_count := p_match_rec_of_tab.LINE_ID.count;
1236
1237
1238 IF PG_DEBUG <> 0 THEN
1239 oe_debug_pub.add('count in table=>'|| l_count,5);
1240 END IF;
1241
1242
1243
1244
1245 IF PG_DEBUG <> 0 THEN
1246 oe_debug_pub.add('no of records in table=>'|| l_count,5);--level 5
1247 END IF;
1248 i := 1;
1249
1250 lStmtNum:= 20;
1251 While i is not null
1252 LOOP
1253 j := p_match_rec_of_tab.LINE_ID(i);
1254
1255 x_tab_of_rec(j).l_index := i;
1256
1257
1258 x_tab_of_rec(j).LINE_ID := p_match_rec_of_tab.LINE_ID(i);
1259
1260
1261 x_tab_of_rec(j).ATO_LINE_ID := p_match_rec_of_tab.ATO_LINE_ID(i);
1262 x_tab_of_rec(j).TOP_MODEL_LINE_ID:= p_match_rec_of_tab.TOP_MODEL_LINE_ID(i);
1263 x_tab_of_rec(j).LINK_TO_LINE_ID := p_match_rec_of_tab.LINK_TO_LINE_ID(i);
1264 x_tab_of_rec(j).BOM_ITEM_TYPE := p_match_rec_of_tab.BOM_ITEM_TYPE(i);
1265 x_tab_of_rec(j).wip_supply_type := p_match_rec_of_tab.WIP_SUPPLY_TYPE(i);
1266
1267 lStmtNum:=30;
1268 i := p_match_rec_of_tab.LINE_ID.NEXT(i);
1269 --EXIT when i = l_count;
1270
1271 END LOOP;
1272
1273
1274 l_count := x_tab_of_rec.count;
1275
1276
1277 IF PG_DEBUG <> 0 THEN
1278 oe_debug_pub.add('count in record structure=>'|| l_count,5);
1279 END IF;
1280
1281
1282
1283
1284
1285 EXCEPTION
1286 WHEN fnd_api.g_exc_error THEN
1287 IF PG_DEBUG <> 0 THEN
1288 oe_debug_pub.add('xfer_tab_to_rec: ' || 'Exception in stmt num: '
1289 || to_char(lStmtNum), 1);
1290 END IF;
1291 x_return_status := FND_API.G_RET_STS_ERROR;
1292 -- Get message count and data
1293 cto_msg_pub.count_and_get
1294 ( p_msg_count => x_msg_count
1295 , p_msg_data => x_msg_data
1296 );
1297 WHEN fnd_api.g_exc_unexpected_error THEN
1298 IF PG_DEBUG <> 0 THEN
1299 oe_debug_pub.add('xfer_tab_to_rec: ' || ' Unexpected Exception in stmt num: '
1300 || to_char(lStmtNum), 1);
1301
1302 END IF;
1303
1304 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1305 -- Get message count and data
1306 cto_msg_pub.count_and_get
1307 ( p_msg_count => x_msg_count
1308 , p_msg_data => x_msg_data
1309 );
1310 WHEN OTHERS then
1311
1312 IF PG_DEBUG <> 0 THEN
1313 oe_debug_pub.add('xfer_tab_to_rec: ' || 'Others Exception in stmt num: '
1314 || to_char(lStmtNum), 1);
1315 END IF;
1316 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1317 oe_debug_pub.add('error mse'||sqlerrm);
1318 -- Get message count and data
1319 cto_msg_pub.count_and_get
1320 ( p_msg_count => x_msg_count
1321 , p_msg_data => x_msg_data
1322 );
1323
1324
1325
1326 END xfer_tab_to_rec;
1327
1328 PROCEDURE xfer_rec_to_tab(
1329 p_tab_of_rec IN CTO_Configured_Item_GRP.TEMP_TAB_OF_REC_TYPE,
1330 p_match_rec_of_tab IN OUT NOCOPY CTO_Configured_Item_GRP.CTO_MATCH_REC_TYPE,
1331 x_return_status OUT NOCOPY VARCHAR2,
1332 x_msg_count OUT NOCOPY NUMBER,
1333 x_msg_data OUT NOCOPY VARCHAR2
1334
1335 )
1336
1337 IS
1338
1339 i number := 0 ;
1340 j number ;
1341
1342
1343 l_last_idx number;
1344 lStmtNum NUMBER;
1345 l_count number;
1346
1347
1348
1349 BEGIN
1350 x_return_status := FND_API.G_RET_STS_SUCCESS;
1351
1352 lStmtNum := 10;
1353 l_last_idx := p_tab_of_rec.last;
1354 j := p_tab_of_rec.first;
1355 l_count := p_tab_of_rec.count;
1356
1357 IF PG_DEBUG <> 0 THEN
1358
1359 oe_debug_pub.add('Last index in p_tab_of_rec strcuture=>'||l_last_idx,5);
1360 oe_debug_pub.add('first index in p_tab_of_rec strcuture=>'||j,5);
1361 oe_debug_pub.add('no of recs in p_tab_of_rec strcuture=>'||l_count,3);
1362
1363 END IF;
1364
1365
1366
1367
1368
1369 -- p_match_rec_of_tab.plan_level.extend(l_count);
1370 -- p_match_rec_of_tab.parent_ato_line_id.extend(l_count);
1371 -- p_match_rec_of_tab.gop_parent_ato_line_id.extend(l_count);
1372
1373
1374 --print only fi debug = 5
1375
1376 IF PG_DEBUG <> 0 THEN
1377 oe_debug_pub.add('index '||' line_id '||' plan_level'
1378 || 'parent_ato '||'gop_parent',5);
1379 END IF;
1380
1381 lStmtNum :=30;
1382 WHILE(j <= l_last_idx)
1383 LOOP
1384
1385 i := p_tab_of_rec(j).l_index;
1386
1387
1388 p_match_rec_of_tab.plan_level(i) := p_tab_of_rec(j).plan_level;
1389
1390 p_match_rec_of_tab.parent_ato_line_id(i) := p_tab_of_rec(j).parent_ato_line_id;
1391 p_match_rec_of_tab.gop_parent_ato_line_id(i) := p_tab_of_rec(j).gop_parent_ato_line_id;
1392
1393 --print only fi debug = 5
1394
1395 IF PG_DEBUG <> 0 THEN
1396 oe_debug_pub.add(i||'=>'||p_match_rec_of_tab.line_id(i)||'=>' ||
1397 p_match_rec_of_tab.plan_level(i)||'=>'||
1398 p_match_rec_of_tab.parent_ato_line_id(i)||'=>'||
1399 p_match_rec_of_tab.gop_parent_ato_line_id(i),5);
1400 END IF;
1401
1402 lStmtNum :=40;
1403
1404 j := p_tab_of_rec.next(j);
1405 END LOOP;
1406
1407
1408
1409
1410 EXCEPTION
1411
1412 WHEN fnd_api.g_exc_error THEN
1413 IF PG_DEBUG <> 0 THEN
1414 oe_debug_pub.add('xfer_rec_to_tab: ' || 'Exception in stmt num: '
1415 || to_char(lStmtNum), 1);
1416 END IF;
1417 x_return_status := FND_API.G_RET_STS_ERROR;
1418 -- Get message count and data
1419 cto_msg_pub.count_and_get
1420 ( p_msg_count => x_msg_count
1421 , p_msg_data => x_msg_data
1422 );
1423 WHEN fnd_api.g_exc_unexpected_error THEN
1424 IF PG_DEBUG <> 0 THEN
1425 oe_debug_pub.add('xfer_rec_to_tab: ' || ' Unexpected Exception in stmt num: ' ||
1426 to_char(lStmtNum), 1);
1427 END IF;
1428 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1429 -- Get message count and data
1430 cto_msg_pub.count_and_get
1431 ( p_msg_count => x_msg_count
1432 , p_msg_data => x_msg_data
1433 );
1434 WHEN OTHERS then
1435 IF PG_DEBUG <> 0 THEN
1436 oe_debug_pub.add('xfer_rec_to_tab: ' || 'Others Exception in stmt num: ' ||
1437 to_char(lStmtNum), 1);
1438 oe_debug_pub.add('errmessage' || sqlerrm,1);
1439 END IF;
1440
1441 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1442 -- Get message count and data
1443 cto_msg_pub.count_and_get
1444 ( p_msg_count => x_msg_count
1445 , p_msg_data => x_msg_data
1446 );
1447
1448
1449
1450 END xfer_rec_to_tab;
1451
1452
1453
1454
1455 PROCEDURE populate_parent_ato(
1456 P_Source in Varchar2,
1457 P_tab_of_rec in out NOCOPY CTO_Configured_Item_GRP.TEMP_TAB_OF_REC_TYPE,
1458 x_return_status OUT NOCOPY VARCHAR2,
1459 x_msg_count OUT NOCOPY NUMBER,
1460 x_msg_data OUT NOCOPY VARCHAR2
1461 )
1462 is
1463 TYPE TABNUM is TABLE of NUMBER index by binary_integer ;
1464 v_raw_line_id TABNUM ;
1465 v_src_point number ;
1466 v_prev_src_point number ;
1467 j number ;
1468 v_step VARCHAR2(10) ;
1469 i number;
1470 l_last_idx number;
1471 lStmtNum number;
1472 BEGIN
1473
1474 x_return_status := FND_API.G_RET_STS_SUCCESS;
1475 IF PG_DEBUG <> 0 THEN
1476 oe_debug_pub.add('Entered populate_parent_ato',5);
1477 END IF;
1478
1479 v_step := 'Step C1' ;
1480
1481 lStmtNum:=10;
1482 l_last_idx := P_tab_of_rec.last;
1483
1484 IF PG_DEBUG <> 0 THEN
1485 oe_debug_pub.add('last index'|| l_last_idx,5);
1486 END IF;
1487
1488
1489 i := P_tab_of_rec.FIRST;
1490
1491 IF PG_DEBUG <> 0 THEN
1492 oe_debug_pub.add('first index'|| i,5);
1493 END IF;
1494
1495 lStmtNum:=20;
1496 LOOP
1497 IF( P_tab_of_rec.exists(i) ) THEN
1498 v_src_point := i ;
1499
1500
1501 IF PG_DEBUG <> 0 THEN
1502 oe_debug_pub.add('populate_parent_ato: ' || 'present index=>'
1503 || v_src_point,5);
1504 oe_debug_pub.add('populate_parent_ato: ' || 'ato line => '
1505 ||P_tab_of_rec(v_src_point).ato_line_id,5);
1506 oe_debug_pub.add('populate_parent_ato: ' || 'top line => '
1507 ||P_tab_of_rec(v_src_point).top_model_line_id,5);
1508
1509 END IF;
1510
1511 lStmtNum:=30;
1512 IF(P_tab_of_rec(v_src_point).ato_line_id is not null
1513 and
1514 --filters out any individual embedded ato lines
1515 P_tab_of_rec(v_src_point).top_model_line_id is not null) THEN
1516
1517 IF PG_DEBUG <> 0 THEN
1518 oe_debug_pub.add('populate_parent_ato: ' || ' processing '
1519 || to_char( v_src_point ) , 4 );
1520 END IF;
1521
1522 /*
1523 ** resolve parent ato line id for item.
1524 */
1525
1526 v_step := 'Step C2' ;
1527 lStmtNum:=40;
1528 WHILE( P_tab_of_rec.exists(v_src_point) )
1529 LOOP
1530 v_raw_line_id(v_raw_line_id.count + 1 ) := v_src_point ;
1531 IF( P_tab_of_rec(v_src_point).line_id =
1532 P_tab_of_rec(v_src_point).ato_line_id )
1533 THEN
1534
1535 IF PG_DEBUG <> 0 THEN
1536 oe_debug_pub.add('populate_parent_ato: ' || 'top_most ato parent '
1537 || to_char( v_src_point ),5);
1538 END IF;
1539 exit ;
1540 END IF ;
1541
1542 /* store each unresolved item in its heirarchy */
1543 v_prev_src_point := v_src_point ;
1544 v_src_point := P_tab_of_rec(v_src_point).link_to_line_id ;
1545
1546
1547
1548 IF( v_src_point is null ) THEN
1549 v_src_point := v_prev_src_point ;
1550 exit ;
1551 END IF ;
1552
1553 IF( P_tab_of_rec(v_src_point).ato_line_id is null ) THEN
1554 v_src_point := v_prev_src_point ;
1555 /* break IF pto is on top of top level ato or
1556 the current lineid is top level phantom ato
1557 v_src_point := null ;
1558 */
1559 exit ;
1560 END IF ;
1561
1562 IF( P_tab_of_rec(v_src_point).bom_item_type = '1' AND
1563 P_tab_of_rec(v_src_point).ato_line_id is not null AND
1564 nvl( P_tab_of_rec(v_src_point).wip_supply_type , 0 ) <> '6' ) THEN
1565 exit ;
1566 /* break if non phantom ato parent found */
1567 END IF ;
1568 END LOOP ;
1569
1570 j := v_raw_line_id.count ; /* total number of items to be resolved */
1571
1572 v_step := 'Step C3' ;
1573 lStmtNum:=50;
1574 WHILE( j >= 1 )
1575 LOOP
1576 P_tab_of_rec(v_raw_line_id(j)).parent_ato_line_id := v_src_point ;
1577
1578
1579 IF PG_DEBUG <> 0 THEN
1580 oe_debug_pub.add('populate_parent_ato: ' || v_raw_line_id(j)||
1581 ' parent '||v_src_point,5 );
1582 END IF;
1583
1584 j := j -1 ;
1585 END LOOP ;
1586
1587 /* remove all elements as they have been resolved */
1588 v_raw_line_id.delete ;
1589
1590 END IF ; /* check whether ato_line_id is not null */
1591
1592 END IF ;
1593
1594 EXIT when i = l_last_idx;
1595 lStmtNum:=50;
1596 i := P_tab_of_rec.next(i);
1597
1598
1599 IF PG_DEBUG <> 0 THEN
1600 oe_debug_pub.add('populate_parent_ato: ' || 'next index=>'|| i,5);
1601 END IF;
1602
1603
1604 END LOOP ; --end of first while
1605
1606
1607
1608
1609 IF PG_DEBUG <> 0 THEN
1610 oe_debug_pub.add('--parent_ato_line_id--',5);
1611 oe_debug_pub.add(' Line_id '||' => '||' parent_ato_line_id ',5);
1612
1613 i := P_tab_of_rec.first;
1614 WHILE i is not null
1615 LOOP
1616 oe_debug_pub.add(i||' => '||P_tab_of_rec(i).parent_ato_line_id,5);
1617 i := P_tab_of_rec.NEXT(i);
1618 END LOOP;
1619
1620 END IF;
1621
1622
1623
1624 --calculation of parent_ato_line_id for Gop (ATP) purpose
1625 --phatom models are treated as non-phantom during this calculation
1626 i := 0;
1627 l_last_idx := 0;
1628 lStmtNum:=70;
1629 IF P_Source = 'GOP' THEN
1630 v_step := 'Step C1' ;
1631
1632 l_last_idx := P_tab_of_rec.last;
1633 i := P_tab_of_rec.FIRST;
1634
1635 lStmtNum:=80;
1636 LOOP
1637 IF( P_tab_of_rec.exists(i) ) THEN
1638 v_src_point := i ;
1639
1640 IF(P_tab_of_rec(v_src_point).ato_line_id is not null
1641 and
1642 --filters out any individual embedded ato lines
1643 P_tab_of_rec(v_src_point).top_model_line_id is not null) THEN
1644
1645 IF PG_DEBUG <> 0 THEN
1646 oe_debug_pub.add('populate_parent_ato: ' || ' processing '
1647 || to_char( v_src_point ) , 4 );
1648 END IF;
1649 /*
1650 ** resolve parent ato line id for item.
1651 */
1652
1653 v_step := 'Step C2' ;
1654 lStmtNum:=90;
1655 WHILE( P_tab_of_rec.exists(v_src_point) )
1656 LOOP
1657 v_raw_line_id(v_raw_line_id.count + 1 ) := v_src_point ;
1658 IF( P_tab_of_rec(v_src_point).line_id =
1659 P_tab_of_rec(v_src_point).ato_line_id )
1660 THEN
1661 exit ;
1662 END IF ;
1663
1664 /* store each unresolved item in its heirarchy */
1665 v_prev_src_point := v_src_point ;
1666 v_src_point := P_tab_of_rec(v_src_point).link_to_line_id ;
1667
1668
1669
1670 IF( v_src_point is null ) THEN
1671 v_src_point := v_prev_src_point ;
1672 exit ;
1673 END IF ;
1674
1675 IF( P_tab_of_rec(v_src_point).ato_line_id is null ) THEN
1676 v_src_point := v_prev_src_point ;
1677 /* break IF pto is on top of top level ato or
1678 the current lineid is top level phantom ato
1679 v_src_point := null ;
1680 */
1681 exit ;
1682 END IF ;
1683
1684 IF( P_tab_of_rec(v_src_point).bom_item_type = '1' AND
1685 P_tab_of_rec(v_src_point).ato_line_id is not null
1686 --wip_supplY-Type is ignored
1687 ) THEN
1688 exit ;
1689 /* break if non phantom ato parent found */
1690 END IF ;
1691 END LOOP ;
1692
1693 j := v_raw_line_id.count ; /* total number of items to be resolved */
1694
1695 v_step := 'Step C3' ;
1696 lStmtNum:=90;
1697 WHILE( j >= 1 )
1698 LOOP
1699 P_tab_of_rec(v_raw_line_id(j)).gop_parent_ato_line_id := v_src_point ;
1700 j := j -1 ;
1701 END LOOP ;
1702
1703 /* remove all elements as they have been resolved */
1704 v_raw_line_id.delete ;
1705
1706 END IF ; /* check whether ato_line_id is not null */
1707
1708 END IF ;
1709
1710 EXIT when i = l_last_idx;
1711
1712 lStmtNum:=100;
1713 i := P_tab_of_rec.next(i);
1714
1715 END LOOP ; --end of first while
1716
1717
1718
1719 IF PG_DEBUG <> 0 THEN
1720 oe_debug_pub.add('--gop parent_ato_line_id--',5);
1721 oe_debug_pub.add(' Line_id '||' => '||' gop parent_ato_line_id ',5);
1722 i := P_tab_of_rec.first;
1723 WHILE i is not null
1724 LOOP
1725 oe_debug_pub.add(i||' => '||P_tab_of_rec(i).gop_parent_ato_line_id,5);
1726 i := P_tab_of_rec.NEXT(i);
1727 END LOOP;
1728 END IF;--p debug
1729
1730 END IF; --P_Source = GOP
1731
1732 EXCEPTION
1733 WHEN others THEN
1734
1735 IF PG_DEBUG <> 0 THEN
1736 oe_debug_pub.add('populate_parent_ato: ' || 'Others Exception in stmt num: '
1737 || to_char(lStmtNum), 1);
1738 oe_debug_pub.add('errmsg'||sqlerrm,5);
1739 END IF;
1740 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1741 -- Get message count and data
1742 cto_msg_pub.count_and_get
1743 ( p_msg_count => x_msg_count
1744 , p_msg_data => x_msg_data
1745 );
1746
1747
1748 END populate_parent_ato ;
1749
1750
1751
1752
1753 PROCEDURE populate_plan_level
1754 ( P_tab_of_rec in out NOCOPY CTO_Configured_Item_GRP.TEMP_TAB_OF_REC_TYPE,
1755 x_return_status OUT NOCOPY VARCHAR2,
1756 x_msg_count OUT NOCOPY NUMBER,
1757 x_msg_data OUT NOCOPY VARCHAR2)
1758 is
1759 TYPE TABNUM is TABLE of NUMBER index by binary_integer ;
1760 v_raw_line_id TABNUM ;
1761 v_src_point number ;
1762 j number ;
1763 v_step VARCHAR2(10) ;
1764 i number := 0 ;
1765 lStmtNum number;
1766
1767 begin
1768
1769 x_return_status := FND_API.G_RET_STS_SUCCESS;
1770 IF PG_DEBUG <> 0 THEN
1771 oe_debug_pub.add('Entered populate_plan_level',5);
1772 END IF;
1773
1774 /*
1775 ** Strategy: Resolve plan_level for each line item by setting it to 1 + plan_level of parent.
1776 ** use the link_to_line_id column to get to the parent. if parents plan_level is not yet
1777 ** resolved, go to its immediate ancestor recursively till you find a line item with
1778 ** plan_level set( Top level plan level is always set to zero ). When coming out of recursion
1779 ** set the plan_level of any ancestors that havent been resolved yet.
1780 ** Implementation: As Pl/Sql does not support Stack built-in-datatype, an equivalent behavior
1781 ** can be achieved by storing items in a table( PUSH implementation) and retrieving them from
1782 ** the end of the table ( POP implmentation [LIFO] )
1783 */
1784
1785 v_step := 'Step B1' ;
1786 lStmtNum :=10;
1787 i := P_tab_of_rec.first ;
1788
1789 lStmtNum :=20;
1790 while i is not null
1791 loop
1792
1793 if( P_tab_of_rec.exists(i) ) then
1794 v_src_point := i ;
1795
1796 --to filter out ATO items ordered individually
1797 IF P_tab_of_rec(v_src_point).top_model_line_id is not null THEN
1798
1799
1800 /*
1801 ** resolve plan level for item only if not yet resolved
1802 */
1803 lStmtNum :=30;
1804 while( P_tab_of_rec(v_src_point).plan_level is null )
1805 loop
1806 IF (P_tab_of_rec(v_src_point).line_id = P_tab_of_rec(v_src_point).ato_line_id) THEN
1807 P_tab_of_rec(v_src_point).plan_level := 0;
1808 EXIT;
1809
1810 END IF;
1811
1812 v_raw_line_id(v_raw_line_id.count + 1 ) := v_src_point ;
1813 /* store each unresolved item in its heirarchy */
1814
1815 v_src_point := P_tab_of_rec(v_src_point).link_to_line_id ;
1816
1817 end loop ;
1818
1819 v_step := 'Step B2' ;
1820
1821 j := v_raw_line_id.count ; /* total number of items to be resolved */
1822
1823 lStmtNum :=40;
1824 while( j >= 1 )
1825 loop
1826
1827 P_tab_of_rec(v_raw_line_id(j)).plan_level := P_tab_of_rec(v_src_point).plan_level + 1;
1828
1829 v_src_point := v_raw_line_id(j) ;
1830
1831 j := j -1 ;
1832 end loop ;
1833
1834 v_raw_line_id.delete ; /* remove all elements as they have been resolved */
1835
1836 END IF; --top model line id check
1837
1838 end if ;
1839
1840
1841 lStmtNum :=50;
1842 i := P_tab_of_rec.next(i) ; /* added for bug 1728383 for performance */
1843
1844
1845 end loop ;
1846
1847
1848
1849 IF PG_DEBUG <> 0 THEN
1850 oe_debug_pub.add('--plan level--',5);
1851 oe_debug_pub.add(' Line_id '||' => '||' plan_level ',5);
1852 i := P_tab_of_rec.first;
1853 WHILE i is not null
1854 LOOP
1855 oe_debug_pub.add(i||' => '||P_tab_of_rec(i).plan_level,5);
1856 i := P_tab_of_rec.NEXT(i);
1857 END LOOP;
1858
1859 END IF;
1860
1861 EXCEPTION
1862 WHEN others THEN
1863
1864 IF PG_DEBUG <> 0 THEN
1865 oe_debug_pub.add('populate_plan_level : ' || 'Others Exception in stmt num: '
1866 || to_char(lStmtNum), 1);
1867 oe_debug_pub.add('errmsg'||sqlerrm,5);
1868 END IF;
1869 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1870 -- Get message count and data
1871 cto_msg_pub.count_and_get
1872 ( p_msg_count => x_msg_count
1873 , p_msg_data => x_msg_data
1874 );
1875
1876
1877 end populate_plan_level ;
1878
1879
1880
1881
1882 PROCEDURE perform_match
1883 (
1884 p_ato_line_id in number,
1885 -- p_custom_match_profile in VARCHAR2,
1886 x_return_status OUT NOCOPY VARCHAR2,
1887 x_msg_count OUT NOCOPY NUMBER,
1888 x_msg_data OUT NOCOPY VARCHAR2
1889 )
1890 IS
1891
1892 cursor c_model_lines is
1893 select line_id, parent_ato_line_id, inventory_item_id
1894 from bom_cto_order_lines_gt
1895 where bom_item_type = '1'
1896 and ato_line_id = p_ato_line_id
1897 and nvl(wip_supply_type,0) <> 6
1898 and ato_line_id is not null -- could be a PTO
1899 and config_item_id is null --becos item could have been re-used
1900 and perform_match in ('Y','C')
1901 order by plan_level desc, inventory_item_id asc;
1902 /* bugfix 4227993: added item_id in the order by, so that 2 processes always process the
1903 sub-models in the same sequence. This should avoid deadlock issues while
1904 acquiring user-locks.
1905 */
1906
1907
1908 lStatus number;
1909 lXConfigId number;
1910 lPerformMatch varchar2(1);
1911 l_x_error_msg varchar2(100);
1912 l_x_msg_name varchar2(30);
1913 l_x_table_name varchar2(30);
1914 lStmtNum number;
1915 l_lock_status number; -- bugfix 4227993
1916 --
1917 -- bug 7203643
1918 -- changed the hash value variable type to varchar2
1919 -- ntungare
1920 --
1921 --l_hash_value number; -- bugfix 4227993
1922 l_hash_value varchar2(2000);
1923
1924 v_total_count number ;
1925
1926 BEGIN
1927
1928
1929 /*IF PG_DEBUG <> 0 THEN
1930 oe_debug_pub.add('Entered perform_match for =>'|| p_ato_line_id
1931 ||'custm prof=>'||p_custom_match_profile,1);
1932 END IF;*/
1933 x_return_status := FND_API.G_RET_STS_SUCCESS;
1934
1935 lStmtNum:=10;
1936 for lNextRec in c_model_lines loop
1937
1938 lXConfigId := NULL;
1939 lStmtNum :=20;
1940 select perform_match
1941 into lPerformMatch
1942 from bom_cto_order_lines_gt
1943 where line_id = lNextRec.line_id;
1944
1945
1946
1947
1948 IF PG_DEBUG <> 0 THEN
1949 oe_debug_pub.add(' perform_match flag =>'||lPerformMatch
1950 ||'for line_id =>'||lNextRec.line_id,5
1951 );
1952 END IF;
1953
1954 if (lPerformMatch = 'U') then
1955
1956 lStmtNum:=30;
1957 update bom_cto_order_lines_gt
1958 set perform_match = 'U'
1959 where line_id = lNextRec.parent_ato_line_id
1960 and perform_match in ('Y','C');
1961 -- if the update fails, its not an error
1962
1963 else
1964
1965 lStmtNum := 120;
1966 --
1967 -- Begin Bugfix 4227993
1968 -- Acquire user-lock by calling lock_for_match so that the process does not end up
1969 -- creating new configs if a non-commited match exists.
1970 -- Incase lock is not acquired, wait indefinitely. We could error out but we decided
1971 -- to wait so that user does not have to resubmit the process again.
1972 --
1973 IF( lPerformMatch in ( 'Y' , 'C' )) then
1974
1975 /*FND_FILE.PUT_LINE(FND_FILE.LOG, 'Calling cto_utility_pk.lock_for_match: start time: ' ||
1976 to_char(sysdate , 'MM/DD/YYYY HH24:MI:SS'));*/
1977 IF PG_DEBUG <> 0 THEN
1978 oe_debug_pub.add('Calling cto_utility_pk.lock_for_match: start time: ' || to_char(sysdate , 'MM/DD/YYYY HH24:MI:SS'), 1);
1979 END IF; --Bugfix 6452747
1980
1981 CTO_UTILITY_PK.lock_for_match(
1982 x_return_status => x_return_status,
1983 xMsgCount => x_msg_count,
1984 xMsgData => x_msg_data,
1985 x_lock_status => l_lock_status,
1986 x_hash_value => l_hash_value,
1987 p_line_id => lNextRec.line_id );
1988
1989 /*FND_FILE.PUT_LINE(FND_FILE.LOG, 'Calling cto_utility_pk.lock_for_match: end time: ' ||
1990 to_char(sysdate , 'MM/DD/YYYY HH24:MI:SS'));*/
1991 IF PG_DEBUG <> 0 THEN
1992 oe_debug_pub.add('Calling cto_utility_pk.lock_for_match: end time: ' || to_char(sysdate , 'MM/DD/YYYY HH24:MI:SS'), 1);
1993 END IF; --Bugfix 6452747
1994
1995 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
1996 oe_debug_pub.add('match_and_create_all_items: '|| 'get_user_lock returned error');
1997 raise fnd_api.g_exc_unexpected_error;
1998 end if;
1999
2000 --check for error cases
2001 if ( l_lock_status <> 0 ) THEN
2002 if (l_lock_status = 1) then -- timeout
2003 oe_debug_pub.add('l_lock_status = 1: TIMEOUT ');
2004 cto_msg_pub.cto_message('BOM','CTO_LOCK_TIMEOUT');
2005 raise fnd_api.g_exc_unexpected_error;
2006
2007 elsif (l_lock_status = 2) then -- deadlock
2008 oe_debug_pub.add('l_lock_status = 2: DEADLOCK ');
2009 cto_msg_pub.cto_message('BOM','CTO_LOCK_DEADLOCK');
2010 raise fnd_api.g_exc_unexpected_error;
2011
2012 elsif (l_lock_status = 3) then -- parameter error
2013 oe_debug_pub.add('l_lock_status = 3: PARAMETER ERROR ');
2014 cto_msg_pub.cto_message('BOM','CTO_LOCK_PARAM_ERROR');
2015 raise fnd_api.g_exc_unexpected_error;
2016
2017 elsif (l_lock_status = 4) then -- already locked.
2018 oe_debug_pub.add('l_lock_status = 4: ALREADY LOCKED ERROR ');
2019 cto_msg_pub.cto_message('BOM','CTO_LOCK_ALREADY_LOCKED');
2020 -- we shall not raise an error if we are already holding the lock.
2021
2022 else -- internal error - not fault of user
2023 oe_debug_pub.add('l_lock_status = '||l_lock_status||': INTERNAL ERROR ');
2024 cto_msg_pub.cto_message('BOM','CTO_LOCK_ERROR');
2025 raise fnd_api.g_exc_unexpected_error;
2026 end if;
2027 else
2028 IF PG_DEBUG <> 0 THEN
2029 oe_debug_pub.add('match_and_create_all_items: ' || 'Successfully obtained lock for match.');
2030 END IF;
2031
2032 end if;
2033 END IF;
2034 --
2035 -- End Bugfix 4227993
2036 --
2037
2038 IF lPerformMatch = 'Y' THEN
2039
2040 lStmtNum:=40;
2041 lStatus := CTO_MATCH_CONFIG.check_config_match(
2042 lNextRec.line_id,
2043 lXConfigId,
2044 l_x_error_msg,
2045 l_x_msg_name);
2046
2047
2048
2049 if lStatus <> 1 then
2050 raise fnd_api.g_exc_error;
2051 end if;
2052 ELSIF lPerformMatch = 'C' THEN
2053 IF PG_DEBUG <> 0 THEN
2054 oe_debug_pub.add('match_and_create_all_items: ' || 'Custom Match.', 1);
2055 END IF;
2056
2057 lStmtNum:=50;
2058 lStatus := CTO_CUSTOM_MATCH_PK.find_matching_config(
2059 pModelLineId => lNextRec.line_id,
2060 xMatchedItemId => lXConfigId,
2061 xErrorMessage => l_x_error_msg,
2062 xMessageName => l_x_msg_name,
2063 xTableName => l_x_table_name);
2064
2065
2066 if lStatus <> 1 then
2067 raise fnd_api.g_exc_error;
2068 end if;
2069
2070 END IF;
2071
2072 if (lStatus = 1 and lXConfigId is null) then
2073
2074
2075 lStmtNum:=60;
2076 update bom_cto_order_lines_gt
2077 set perform_match = 'U'
2078 where line_id = lNextRec.line_id
2079 and perform_match in ('Y','C');
2080 -- if the update fails, its not an error
2081
2082 oe_debug_pub.add('perform_match: ' || 'updated to U : ' || to_char(lNextRec.Line_Id), 1);
2083
2084 oe_debug_pub.add('perform_match: ' || 'rowcount : ' || to_char(sql%rowcount), 1);
2085
2086 lStmtNum:=70;
2087 update bom_cto_order_lines_gt
2088 set perform_match = 'U'
2089 where line_id = lNextRec.parent_ato_line_id
2090 and perform_match in ( 'Y','C');
2091 -- if the update fails, its not an error
2092
2093 oe_debug_pub.add('perform_match: ' || 'updated to U : ' || to_char(lNextRec.parent_ato_Line_Id), 1);
2094
2095 oe_debug_pub.add('perform_match: ' || 'rowcount : ' || to_char(sql%rowcount), 1);
2096
2097 elsif (lStatus = 1 and lXConfigId is not null) then
2098
2099
2100 lStmtNum:=80;
2101 update bom_cto_order_lines_gt
2102 set config_item_id = lXConfigId
2103 where line_id = lNextRec.line_id;
2104
2105 --
2106 -- begin bugfix 4227993
2107 -- Release the lock if match found rather than wait for commit/rollback.
2108 --
2109
2110 IF PG_DEBUG <> 0 THEN
2111 oe_debug_pub.add('match_and_create_all_items: ' || 'Match found for line ' ||
2112 to_char(lNextRec.line_id) ||
2113 ' with config item ' ||
2114 to_char(lXConfigId), 1);
2115 END IF;
2116
2117 CTO_UTILITY_PK.release_lock(
2118 x_return_status => x_return_status,
2119 x_Msg_Count => x_Msg_Count,
2120 x_Msg_Data => x_msg_data,
2121 p_hash_value => l_hash_value);
2122
2123 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
2124 oe_debug_pub.add('match_and_create_all_items: '|| 'get_user_lock returned error');
2125 raise fnd_api.g_exc_unexpected_error;
2126 end if;
2127
2128 --
2129 -- end bugfix 4227993
2130 --
2131
2132
2133 else
2134
2135 raise fnd_api.g_exc_error;
2136
2137 end if; -- end lStatus = 1 and lXConfigID is not null
2138
2139 end if; -- else perform_match = 'U'
2140
2141 lStmtNum := 160;
2142
2143
2144 end loop;
2145
2146
2147
2148
2149 /* Fix for bug 3533192 */
2150
2151 update bom_cto_order_lines_gt
2152 set perform_match = 'Y'
2153 where ato_line_id = p_ato_line_id
2154 and inventory_item_id in
2155 ( select inventory_item_id
2156 from bom_cto_order_lines_gt
2157 where ato_line_id = p_ato_line_id
2158 and bom_item_type = '1'
2159 and wip_supply_type <> 6
2160 and perform_match = 'U'
2161 group by inventory_item_id
2162 having count(*) > 1
2163 );
2164
2165
2166
2167
2168 oe_debug_pub.add('perform_match: ' || 'Updated possible similar models to Y : '
2169 || to_char(sql%rowcount), 1);
2170
2171
2172
2173
2174
2175
2176
2177 EXCEPTION
2178 WHEN fnd_api.g_exc_error THEN
2179 IF PG_DEBUG <> 0 THEN
2180 oe_debug_pub.add('perform_match: ' || 'Exception in stmt num: '
2181 || to_char(lStmtNum), 1);
2182 END IF;
2183 x_return_status := FND_API.G_RET_STS_ERROR;
2184 -- Get message count and data
2185 cto_msg_pub.count_and_get
2186 ( p_msg_count => x_msg_count
2187 , p_msg_data => x_msg_data
2188 );
2189 WHEN fnd_api.g_exc_unexpected_error THEN
2190 IF PG_DEBUG <> 0 THEN
2191 oe_debug_pub.add('perform_match: ' || ' Unexpected Exception in stmt num: '
2192 || to_char(lStmtNum), 1);
2193 END IF;
2194 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2195 -- Get message count and data
2196 cto_msg_pub.count_and_get
2197 ( p_msg_count => x_msg_count
2198 , p_msg_data => x_msg_data
2199 );
2200 WHEN OTHERS then
2201
2202 IF PG_DEBUG <> 0 THEN
2203 oe_debug_pub.add('perform_match: ' || 'Others Exception in stmt num: '
2204 || to_char(lStmtNum), 1);
2205 oe_debug_pub.add('errmsg'||sqlerrm,1);
2206 END IF;
2207 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2208 -- Get message count and data
2209 cto_msg_pub.count_and_get
2210 ( p_msg_count => x_msg_count
2211 , p_msg_data => x_msg_data
2212 );
2213
2214
2215 END perform_match;
2216
2217 PROCEDURE flag_reuse_config(
2218 p_model_line_id IN number,
2219 x_return_status OUT NOCOPY varchar2
2220
2221 )
2222 IS
2223
2224 l_model_line_id number;
2225 lStmtNum number :=10;
2226 BEGIN
2227
2228 x_return_status := FND_API.G_RET_STS_SUCCESS;
2229
2230
2231 g_reuse_tbl(p_model_line_id).reuse_config := 'N';
2232
2233 g_model_line_tbl(g_model_line_tbl.count+1):= p_model_line_id;
2234
2235 l_model_line_id := g_reuse_tbl(p_model_line_id).parent_ato_line_id;
2236
2237 --as this model might have a reuse flag =N
2238 --becuase of another child model /becuase of its own componenet
2239 --and also it atkes care of condition where top most ato
2240 --line is reached
2241 IF g_reuse_tbl(l_model_line_id).reuse_config= 'Y' THEN
2242
2243 flag_reuse_config(p_model_line_id =>l_model_line_id,
2244 x_return_status =>x_return_status
2245 );
2246 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2247 RAISE fnd_api.g_exc_unexpected_error;
2248 END IF;
2249
2250 END IF;
2251
2252
2253
2254
2255 EXCEPTION
2256 WHEN fnd_api.g_exc_unexpected_error THEN
2257 IF PG_DEBUG <> 0 THEN
2258 oe_debug_pub.add('flag_reuse_config: ' || ' Unexpected Exception in stmt num: '
2259 || to_char(lStmtNum), 1);
2260 END IF;
2261 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2262 /*-- Get message count and data
2263 cto_msg_pub.count_and_get
2264 ( p_msg_count => x_msg_count
2265 , p_msg_data => x_msg_data
2266 );*/
2267
2268 WHEN OTHERS then
2269
2270 IF PG_DEBUG <> 0 THEN
2271
2272 oe_debug_pub.add('flag_reuse_config: ' || 'Others Exception in stmt num: '
2273 || to_char(lStmtNum), 1);
2274 oe_debug_pub.add('error='||sqlerrm,1);
2275
2276 END IF;
2277 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2278
2279
2280
2281
2282 END flag_reuse_config;
2283
2284
2285 PROCEDURE CTO_REUSE_CONFIGURATION(
2286 p_ato_line_id IN number default null,
2287 x_config_change OUT NOCOPY varchar2,
2288 x_return_status OUT NOCOPY varchar2,
2289 x_msg_count OUT NOCOPY number,
2290 x_msg_data OUT NOCOPY varchar2
2291
2292 )
2293 IS
2294
2295 Type number_tbl_type IS TABLE OF number INDEX BY BINARY_INTEGER;
2296
2297 l_parent_ato_line_tbl number_tbl_type;
2298 l_ato_line_tbl number_tbl_type;
2299 l_unprocessed_parents number_tbl_type;
2300
2301
2302
2303 i number;
2304 l_parent_ato_last_index number;
2305 l_ato_last_index number;
2306
2307 lStmtNum number;
2308
2309 CURSOR c_single_ato is
2310 SELECT line_id,
2311 parent_ato_line_id,
2312 reuse_config
2313 FROM bom_cto_order_lines_gt
2314 --added nvl, bugfix 3530054
2315 WHERE nvl(wip_supply_type,1) <>6 --non phantom ato models
2316 AND bom_item_type = '1' --used inverted commas to use index N5
2317 AND ato_line_id = p_ato_line_id;
2318
2319
2320 CURSOR c_bulk is
2321 SELECT line_id,
2322 parent_ato_line_id,
2323 reuse_config
2324 FROM bom_cto_order_lines_gt
2325 ----added nvl, bugfix 3530054
2326 WHERE nvl(wip_supply_type,1) <>6 --non phantom
2327 AND bom_item_type = '1' ; --'1' for using idx_N5 --ato models
2328
2329 CURSOR c_gt_intial_pic is
2330 SELECT
2331 line_id,
2332 parent_ato_line_id,
2333 ato_line_id,
2334 wip_supply_type,
2335 bom_item_type,
2336 qty_per_parent_model,
2337 reuse_config
2338 FROM bom_cto_order_lines_gt
2339 WHERE ato_line_id = p_ato_line_id;
2340
2341 CURSOR c_debug is
2342 SELECT
2343 line_id,
2344 reuse_config,
2345 config_item_id,
2346 qty_per_parent_model,
2347 config_creation,
2348 ship_from_org_id,
2349 validation_org
2350 FROM bom_cto_order_lines_gt
2351 WHERE reuse_config is not null;
2352
2353
2354
2355
2356 --temporary structures use for debug
2357 l_temp_line_id number_tbl_type;
2358 l_qty_per_parent_model number_tbl_type;
2359 l_bcol_ato_line_tbl number_tbl_type;
2360
2361
2362
2363
2364 BEGIN
2365
2366
2367 IF PG_DEBUG <> 0 THEN
2368 oe_debug_pub.add('ENTERED reuse configuration',5);
2369 END IF;
2370 x_return_status := FND_API.G_RET_STS_SUCCESS;
2371
2372
2373
2374 lStmtNum:=10;
2375 IF p_ato_line_id IS NOT NULL THEN
2376
2377
2378 IF PG_DEBUG <> 0 THEN
2379 oe_debug_pub.add('reuse for ato line=>'||p_ato_line_id,1);
2380 END IF;
2381
2382 lStmtNum := 20;
2383
2384
2385
2386 UPDATE bom_cto_order_lines_gt
2387 SET reuse_config = 'Y'
2388 WHERE ato_line_id = p_ato_line_id
2389 AND bom_item_type = '1' --'1' uses idx_n5 --identifies non-phantom
2390 --need a nvl as for top most ato model there is no value
2391 AND nvl(WIP_SUPPLY_TYPE,1) <> 6; --model lines
2392
2393 lStmtNum:=30;
2394 l_ato_line_tbl(1) := p_ato_line_id;
2395
2396 --debug select
2397 IF PG_DEBUG = 5 THEN
2398 SELECT line_id,
2399 qty_per_parent_model
2400 BULK COLLECT INTO
2401 l_temp_line_id,
2402 l_qty_per_parent_model
2403 FROM bom_cto_order_lines_gt
2404 WHERE ato_line_id = p_ato_line_id;
2405
2406 oe_debug_pub.add('LINE_ID >>QTY_PER_PARENT_MODEL',5);
2407
2408 FOR i in l_temp_line_id.first..l_temp_line_id.last LOOP
2409
2410 oe_debug_pub.add(l_temp_line_id(i)||'>>'||l_qty_per_parent_model(i),5);
2411
2412 END LOOP;
2413
2414 oe_debug_pub.add('Picture of bcol_gt before reuse process',5);
2415
2416 FOR kiran_rec in c_gt_intial_pic
2417 LOOP
2418 oe_debug_pub.add('LINE_ID=>'||kiran_rec.line_id,5);
2419 oe_debug_pub.add('parent_LINE_ID=>'||kiran_rec.parent_ato_line_id,5);
2420 oe_debug_pub.add('ato_LINE_ID=>'||kiran_rec.ato_line_id,5);
2421 oe_debug_pub.add('WS=>'||kiran_rec.wip_supply_type,5);
2422 oe_debug_pub.add('BIT=>'||kiran_rec.bom_item_type,5);
2423 oe_debug_pub.add('QPM=>'||kiran_rec.qty_per_parent_model,5);
2424 oe_debug_pub.add('reuse=>'||kiran_rec.reuse_config,5);
2425
2426 END LOOP;
2427
2428
2429
2430 END IF;
2431 --debug end
2432
2433 ELSE --bulk call
2434
2435
2436 IF PG_DEBUG <> 0 THEN
2437 oe_debug_pub.add('reuse configuration BULK call',1);
2438 END IF;
2439
2440
2441 lStmtNum := 40;
2442 UPDATE bom_cto_order_lines_gt
2443 SET reuse_config = 'Y'
2444 WHERE bom_item_type = '1' --used idx_n5 --identifies non-phantom
2445 AND nvl(WIP_SUPPLY_TYPE,1) <> 6;
2446
2447 --had to use a slect clause
2448 --returning into cluase doesnot supoort distinct
2449 lStmtNum:=50;
2450 SELECT distinct(ato_line_id)
2451 BULK COLLECT INTO l_ato_line_tbl
2452 FROM bom_cto_order_lines_gt
2453 WHERE top_model_line_id is not null;
2454 END IF;
2455
2456 l_ato_last_index := l_ato_line_tbl.count;
2457
2458
2459
2460 IF PG_DEBUG <> 0 THEN
2461 oe_debug_pub.add('IF count>1, re-use called for more than 1 order line',5);
2462 oe_debug_pub.add('l_ato_line_tbl.count=>'||l_ato_line_tbl.count,5);
2463 END IF;
2464
2465
2466 IF l_ato_line_tbl.count > 0 THEN
2467
2468 BEGIN
2469
2470 -- rkaza. 12/06/2005. bug 4520992. Fp'ed bug 4493512.
2471 -- Added hint as per Perf team so that bcolgt drives the query
2472 SELECT /*+ leading(BCGT) use_nl(BCGT BCOL) */ distinct(bcol.ato_line_id)
2473 BULK COLLECT INTO l_bcol_ato_line_tbl
2474 FROM bom_cto_order_lines bcol,
2475 bom_cto_order_lines_gt bcgt
2476 WHERE bcgt.line_id = bcol.line_id
2477 AND bcol.qty_per_parent_model is null;
2478
2479 EXCEPTION
2480 WHEN others then
2481 null;
2482
2483 END;
2484
2485 IF l_bcol_ato_line_tbl.count > 0 THEN
2486
2487 IF PG_DEBUG <> 0 THEN
2488 oe_debug_pub.add('Updating bcol (permanent table) with qty per parent model',5);
2489
2490 END IF;
2491
2492 FORALL j IN 1..l_bcol_ato_line_tbl.last
2493 UPDATE bom_cto_order_lines child
2494 SET qty_per_parent_model =
2495 --used round to be consistent with can_configuration code
2496 ( SELECT ROUND(child.ordered_quantity/parent.ordered_quantity,7)
2497 FROM bom_cto_order_lines parent
2498 WHERE child.parent_ato_line_id= parent.line_id
2499 )
2500 --to filter out ato item order lines
2501 WHERE top_model_line_id is not null
2502 AND ato_line_id = l_bcol_ato_line_tbl(j) ;
2503
2504 END IF; -- l_bcol_ato_line_tbl.count > 0
2505
2506
2507 --bugfix 3503764
2508 --need config_creation as part of fix 3503764
2509 --in core reuse sql
2510 UPDATE bom_cto_order_lines_gt bcol_gt
2511 SET config_creation =
2512 --used round to be consistent with can_configuration code
2513 ( SELECT nvl(mtl.config_orgs,1)--3555026
2514 FROM mtl_system_items mtl
2515 WHERE mtl.inventory_item_id = bcol_gt.inventory_item_id
2516 AND mtl.organization_id = bcol_gt.validation_org--3555026
2517 )
2518 --to filter out ato item order lines
2519 WHERE top_model_line_id is not null
2520 AND bom_item_type= '1'
2521 --nvl as for top most model there wst is not populated
2522 AND nvl(wip_supply_type,1) <> 6;
2523
2524
2525 lStmtNum := 60;
2526 FORALL i in 1..l_ato_last_index
2527 UPDATE bom_cto_order_lines_gt bcolt
2528 SET bcolt.reuse_config = 'N'
2529 WHERE
2530 line_id in (
2531
2532 --bugfix start 3503764
2533 --if ware house is different then reuse = N
2534 (SELECT bcol_gt1.line_id
2535 FROM bom_cto_order_lines_gt bcol_gt1,
2536 bom_cto_order_lines bcol
2537 WHERE bcol.line_id = l_ato_line_tbl(i)
2538 AND bcol_gt1.config_creation in (1,2)
2539 AND bcol_gt1.ato_line_id = l_ato_line_tbl(i)
2540 AND bcol_gt1.ship_from_org_id <> bcol.ship_from_org_id
2541 AND bcol_gt1.bom_item_type = '1'
2542 AND nvl(bcol_gt1.wip_supply_type,1) <> 6
2543 )
2544 --end bugfix 3503764
2545
2546 UNION
2547 ( Select parent_ato_line_id
2548 from bom_cto_order_lines_gt bcolt1
2549 Where (bcolt1.line_id,
2550 bcolt1.qty_per_parent_model,
2551 bcolt1.inventory_item_id)
2552 not in ( Select line_id,
2553 qty_per_parent_model,
2554 inventory_item_id
2555 from bom_cto_order_lines
2556 where ato_line_id = l_ato_line_tbl(i) )
2557 --filters out pure ato item lines
2558 AND bcolt1.top_model_line_id is not null
2559 AND bcolt1.ato_line_id = l_ato_line_tbl(i)
2560 )
2561 -- bugfix 3381658 start
2562 UNION
2563 (Select parent_ato_line_id
2564 from bom_cto_order_lines bcol2
2565 Where (bcol2.line_id,
2566 bcol2.qty_per_parent_model,
2567 bcol2.inventory_item_id)
2568 not in ( Select bcolgt.line_id,
2569 bcolgt.qty_per_parent_model,
2570 bcolgt.inventory_item_id
2571 from bom_cto_order_lines_gt bcolgt
2572 where ato_line_id = l_ato_line_tbl(i) )
2573 --filters out pure ato item lines
2574 AND bcol2.top_model_line_id is not null
2575 AND bcol2.ato_line_id = l_ato_line_tbl(i)
2576 )
2577 --end bugfix 3381658
2578 )
2579 RETURNING parent_ato_line_id BULK COLLECT INTO l_parent_ato_line_tbl;
2580
2581
2582
2583 lStmtNum:= 70;
2584 IF l_parent_ato_line_tbl.EXISTS(1) THEN
2585
2586
2587
2588 IF PG_DEBUG <> 0 THEN
2589 oe_debug_pub.add('Some UN-reusable parent ato lines have been identified',5);
2590 FOR p_ato_idx in l_parent_ato_line_tbl.first..l_parent_ato_line_tbl.last
2591 LOOP
2592 oe_debug_pub.add('kiran'||l_parent_ato_line_tbl(p_ato_idx),5);
2593 END LOOP;
2594 END IF;
2595
2596
2597
2598
2599 l_parent_ato_last_index := l_parent_ato_line_tbl.LAST;
2600
2601 lStmtNum:= 80;
2602 IF p_ato_line_id IS NOT NULL THEN
2603 lStmtNum:=90;
2604 FOR bcol_rec in c_single_ato
2605 Loop
2606
2607 g_reuse_tbl(bcol_rec.line_id).line_id := bcol_rec.line_id;
2608 g_reuse_tbl(bcol_rec.line_id).parent_ato_line_id := bcol_rec.parent_ato_line_id;
2609 g_reuse_tbl(bcol_rec.line_id).reuse_config := bcol_rec.reuse_config;
2610
2611 IF PG_DEBUG <> 0 THEN
2612 oe_debug_pub.add('LINE_ID=>'||g_reuse_tbl(bcol_rec.line_id).line_id,5);
2613 oe_debug_pub.add('PARENT_ATO_LINE_ID=>'||g_reuse_tbl(bcol_rec.line_id).parent_ato_line_id,5);
2614 oe_debug_pub.add('REUSE_CONFIG_from_GT=>'||g_reuse_tbl(bcol_rec.line_id).reuse_config,5);
2615
2616 END IF;
2617
2618 End Loop;
2619
2620 ELSE --bulk call
2621 lStmtNum:=91;
2622 FOR bcol_rec in c_bulk
2623 Loop
2624
2625 g_reuse_tbl(bcol_rec.line_id).line_id := bcol_rec.line_id;
2626 g_reuse_tbl(bcol_rec.line_id).parent_ato_line_id := bcol_rec.parent_ato_line_id;
2627 g_reuse_tbl(bcol_rec.line_id).reuse_config := bcol_rec.reuse_config;
2628
2629 End Loop;
2630
2631
2632
2633 END IF;--check for bulk call
2634
2635
2636
2637 lStmtNum:= 100;
2638 FOR i IN l_parent_ato_line_tbl.FIRST..l_parent_ato_line_tbl.LAST LOOP
2639
2640
2641
2642 --previous update might have put reuse_config to N
2643 --so following if condition
2644 --OR previous element might have updated reuse to N
2645 IF g_reuse_tbl(l_parent_ato_line_tbl(i)).reuse_config= 'Y' THEN
2646
2647 IF PG_DEBUG <> 0 THEN
2648 oe_debug_pub.add('calling flag_reuse_config for model_line_id=>'
2649 ||l_parent_ato_line_tbl(i),5);
2650 END IF;
2651
2652 lStmtNum:= 110;
2653 flag_reuse_config(p_model_line_id =>l_parent_ato_line_tbl(i),
2654 x_return_status =>x_return_status
2655 );
2656 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2657 RAISE fnd_api.g_exc_unexpected_error;
2658 END IF;
2659
2660 END IF;--re-use = Y
2661 END LOOP;
2662
2663 lStmtNum:= 120;
2664
2665 IF g_model_line_tbl.EXISTS(1) THEN
2666
2667 IF PG_DEBUG <> 0 THEN
2668 oe_debug_pub.add('updating reuse_flag to N for following model lines =>',5);
2669 END IF;
2670
2671 FOR i IN g_model_line_tbl.FIRST..g_model_line_tbl.LAST LOOP
2672 IF PG_DEBUG <> 0 THEN
2673 oe_debug_pub.add(g_model_line_tbl(i),5);
2674 END IF;
2675
2676 END LOOP;
2677
2678 FORALL i in g_model_line_tbl.FIRST..g_model_line_tbl.LAST
2679 UPDATE bom_cto_order_lines_gt
2680 SET reuse_config = 'N'
2681 WHERE line_id = g_model_line_tbl(i);
2682 END IF;--g_model_line exists
2683
2684 ELSE
2685
2686
2687 -- IF PG_DEBUG <> 0 THEN
2688 oe_debug_pub.add('All parent ato lines are re-usable',5);--5 level
2689 -- END IF;
2690 END IF; -- l_parent_ato_line_tbl.EXISTS(1)
2691
2692 lStmtNum:=130;
2693 IF p_ato_line_id IS NOT NULL THEN
2694
2695 --deleted, as the procedure gets called again
2696 --for another ATO model line during ACC
2697 g_reuse_tbl.DELETE;
2698 g_model_line_tbl.DELETE;
2699
2700 --check if this needs to be a performance query
2701 lStmtNum := 140;
2702
2703 --as per perf std ahmed almori
2704 --If the global temporary table is referenced in a sub-query in a
2705 --SQL statement which accesses other tables.
2706 --In such cases, the join order may not be optimal due to the lack
2707 --of stats on the temp table, hence hints should be used to ensure the optimal join order.
2708
2709 UPDATE /*+ INDEX (bcolt BOM_CTO_ORDER_LINES_GT_N5) */bom_cto_order_lines_gt bcolt
2710 SET bcolt.config_item_id =
2711 (SELECT bcol.config_item_id
2712 FROM bom_cto_order_lines bcol
2713 WHERE bcolt.line_id = bcol.line_id
2714 )
2715 WHERE bcolt.bom_item_type = '1'--used idx_n5
2716 AND nvl(bcolt.WIP_SUPPLY_TYPE,1) <>6
2717 AND bcolt.reuse_config = 'Y'
2718 AND bcolt.ato_line_id = p_ato_line_id ;
2719 ELSE --bulk call
2720 --check if this needs to be a performance query
2721 lStmtNum:=150;
2722 --as per perf std ahmed almori
2723 --If the global temporary table is referenced in a sub-query in a
2724 --SQL statement which accesses other tables.
2725 --In such cases, the join order may not be optimal due to the lack
2726 --of stats on the temp table, hence hints should be used to ensure the optimal join order.
2727
2728 UPDATE /*+ INDEX (bcolt BOM_CTO_ORDER_LINES_GT_N5) */bom_cto_order_lines_gt bcolt
2729 SET bcolt.config_item_id =
2730 (SELECT bcol.config_item_id
2731 FROM bom_cto_order_lines bcol
2732 WHERE bcolt.line_id = bcol.line_id
2733 )
2734 WHERE bcolt.bom_item_type = '1' --used inverted commas, so that index is used
2735 AND nvl(bcolt.WIP_SUPPLY_TYPE,1) <>6
2736 AND bcolt.reuse_config = 'Y';
2737
2738
2739 END IF;
2740
2741 END IF;--if l_ato_line_tbl.count is > 0)
2742
2743 IF PG_DEBUG = 5 THEN
2744 Oe_debug_pub.add('LINE_ID--'||'reuse_config--'||
2745 'CONFIG_ITEM_ID--' ||'qty_per_parent_model--'
2746 ||'CIB--'||'ship_from_org--'||'validation_org');
2747 FOR debug_rec in c_debug
2748 LOOP
2749 oe_debug_pub.add(debug_rec.line_id||'--'||debug_rec.reuse_config||'--'||
2750 debug_rec.config_item_id||'--'||debug_rec.qty_per_parent_model||'--'||
2751 debug_rec.config_creation||'--'||debug_rec.ship_from_org_id||'--'||
2752 debug_rec.validation_org
2753 ,5);
2754 END LOOP;
2755 END IF;
2756
2757
2758 EXCEPTION
2759
2760 WHEN fnd_api.g_exc_error THEN
2761 IF PG_DEBUG <> 0 THEN
2762 oe_debug_pub.add('CTO_REUSE_CONFIGURATION: ' || 'Exception in stmt num: '
2763 || to_char(lStmtNum), 1);
2764 END IF;
2765 x_return_status := FND_API.G_RET_STS_ERROR;
2766 -- Get message count and data
2767 cto_msg_pub.count_and_get
2768 ( p_msg_count => x_msg_count
2769 , p_msg_data => x_msg_data
2770 );
2771 WHEN fnd_api.g_exc_unexpected_error THEN
2772 IF PG_DEBUG <> 0 THEN
2773 oe_debug_pub.add('CTO_REUSE_CONFIGURATION: ' || ' Unexpected Exception in stmt num: '
2774 || to_char(lStmtNum), 1);
2775 END IF;
2776 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2777 -- Get message count and data
2778 cto_msg_pub.count_and_get
2779 ( p_msg_count => x_msg_count
2780 , p_msg_data => x_msg_data
2781 );
2782 WHEN OTHERS then
2783
2784
2785 IF PG_DEBUG <> 0 THEN
2786 oe_debug_pub.add('CTO_REUSE_CONFIGURATION: ' || 'Others Exception in stmt num: '
2787 || to_char(lStmtNum), 1);
2788 oe_debug_pub.add('errmsg =>'||sqlerrm,1);
2789 END IF;
2790 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2791 -- Get message count and data
2792 cto_msg_pub.count_and_get
2793 ( p_msg_count => x_msg_count
2794 , p_msg_data => x_msg_data
2795 );
2796
2797
2798
2799 END CTO_REUSE_CONFIGURATION ;
2800
2801
2802
2803
2804
2805
2806
2807
2808
2809 PROCEDURE prepare_bcol_temp_data(
2810 p_source IN VARCHAR2,
2811 p_match_rec_of_tab IN OUT NOCOPY CTO_Configured_Item_GRP.CTO_MATCH_REC_TYPE,
2812 x_return_status OUT NOCOPY VARCHAR2,
2813 x_msg_count OUT NOCOPY NUMBER,
2814 x_msg_data OUT NOCOPY VARCHAR2
2815 )
2816 IS
2817 l_last_index number;
2818
2819 l_row_count number;
2820
2821 l_tab_of_rec CTO_Configured_Item_GRP.TEMP_TAB_OF_REC_TYPE;
2822 --l_return_status VARCHAR2(1);
2823 -- l_msg_count number;
2824 -- l_msg_data varchar2(;
2825 lStmtNum NUMBER;
2826
2827 BEGIN
2828
2829 x_return_status := FND_API.G_RET_STS_SUCCESS;
2830
2831 lStmtNum:=10;
2832 l_last_index := p_match_rec_of_tab.line_id.count;
2833
2834
2835
2836 lStmtNum:=20;
2837 UPDATE bom_cto_order_lines_gt bcol
2838 SET (bcol.wip_supply_type,
2839 bcol.bom_item_type )=
2840 (SELECT wip_supply_type,
2841 bom_item_type
2842 FROM bom_inventory_components bic
2843 WHERE bcol.component_sequence_id = bic.component_sequence_id
2844 )
2845 where bcol.ato_line_id <>bcol.line_id;
2846
2847
2848 oe_debug_pub.add('rowcount after update from bic=>'||sql%rowcount,5);
2849
2850
2851 --rowcount after insert of bom_item_type and wip_supply_type is l_rowcount;
2852
2853 --getting bom_item_type and wip_supply_type into cto_match_rec_type
2854 lStmtNum:=30;
2855 SELECT bom_item_type,
2856 wip_supply_type
2857 BULK COLLECT INTO
2858 p_match_rec_of_tab.bom_item_type,
2859 p_match_rec_of_tab.wip_supply_type
2860 FROM bom_cto_order_lines_gt;
2861
2862 oe_debug_pub.add('rowcount after select for BIT,WST=>'||sql%rowcount,5);
2863
2864 --rowcount of bom_itemtype,wip_supply_typ after select l_rowcount;
2865 lStmtNum:=40;
2866 xfer_tab_to_rec(
2867 p_match_rec_of_tab,
2868 l_tab_of_rec,
2869 x_return_status,
2870 x_msg_count,
2871 x_msg_data
2872 );
2873
2874 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
2875 oe_debug_pub.add('SUCCESS after xfer_tab_to_rec',5);
2876
2877 --add retrun status and error mesages to these
2878 lStmtNum:=50;
2879 populate_parent_ato(
2880 P_Source => p_source,
2881 P_tab_of_rec => l_tab_of_rec,
2882 x_return_status => x_return_status,
2883 x_msg_count => x_msg_count,
2884 x_msg_data => x_msg_data );
2885
2886 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
2887 oe_debug_pub.add('status after after xfer_tab_to_rec=>'
2888 || FND_API.G_RET_STS_ERROR,5);
2889 RAISE fnd_api.g_exc_error;
2890 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2891 oe_debug_pub.add('status after after xfer_tab_to_rec=>'
2892 || FND_API.G_RET_STS_UNEXP_ERROR,5);
2893 RAISE fnd_api.g_exc_unexpected_error;
2894 END IF;
2895
2896
2897
2898 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
2899 oe_debug_pub.add('SUCCESS after populate_parent_ato=>');
2900 lStmtNum:=60;
2901 populate_plan_level( P_tab_of_rec => l_tab_of_rec,
2902 x_return_status => x_return_status,
2903 x_msg_count => x_msg_count,
2904 x_msg_data => x_msg_data );
2905 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
2906 oe_debug_pub.add('status after after populate_parent_ato=>'
2907 || FND_API.G_RET_STS_ERROR);
2908 RAISE fnd_api.g_exc_error;
2909 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2910 oe_debug_pub.add('status after after populate_parent_ato=>'
2911 || FND_API.G_RET_STS_UNEXP_ERROR );
2912 RAISE fnd_api.g_exc_unexpected_error;
2913 END IF;
2914
2915
2916
2917 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
2918 oe_debug_pub.add('SUCCESS after populate_plan_level=>');
2919
2920 lStmtNum:=70;
2921 xfer_rec_to_tab(
2922 p_tab_of_rec => l_tab_of_rec ,
2923 p_match_rec_of_tab => p_match_rec_of_tab,
2924 x_return_status => x_return_status,
2925 x_msg_count => x_msg_count,
2926 x_msg_data => x_msg_data
2927 );
2928
2929 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
2930 oe_debug_pub.add('status after after populate_plan_level=>'
2931 || FND_API.G_RET_STS_ERROR);
2932 RAISE fnd_api.g_exc_error;
2933 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2934 oe_debug_pub.add('status after after populate_plan_level=>'
2935 || FND_API.G_RET_STS_UNEXP_ERROR );
2936 RAISE fnd_api.g_exc_unexpected_error;
2937 END IF;
2938
2939
2940 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
2941 oe_debug_pub.add('SUCCESS after xfer_rec_to_tabl=>');
2942
2943 lStmtNum:=80;
2944 --update the remaining columns into bcol_temp
2945
2946 FORALL i IN 1..l_last_index
2947 UPDATE bom_cto_order_lines_gt
2948 SET PARENT_ATO_LINE_ID = p_match_rec_of_tab.PARENT_ATO_LINE_ID(i),
2949 GOP_PARENT_ATO_LINE_ID = p_match_rec_of_tab.GOP_PARENT_ATO_LINE_ID(i),
2950 PLAN_LEVEL = p_match_rec_of_tab.PLAN_LEVEL (i)
2951 WHERE line_id = p_match_rec_of_tab.LINE_ID(i);
2952
2953 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
2954 oe_debug_pub.add('status after after xfer_rec_to_tab=>'
2955 || FND_API.G_RET_STS_ERROR);
2956 RAISE fnd_api.g_exc_error;
2957 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2958 oe_debug_pub.add('status after after xfer_rec_to_tab=>'
2959 || FND_API.G_RET_STS_UNEXP_ERROR );
2960 RAISE fnd_api.g_exc_unexpected_error;
2961 END IF;
2962
2963
2964 EXCEPTION
2965 WHEN fnd_api.g_exc_error THEN
2966 IF PG_DEBUG <> 0 THEN
2967 oe_debug_pub.add(' prepare_bcol_temp_data: ' || 'Exception in stmt num: '
2968 || to_char(lStmtNum), 1);
2969 END IF;
2970 x_return_status := FND_API.G_RET_STS_ERROR;
2971 -- Get message count and data
2972 cto_msg_pub.count_and_get
2973 ( p_msg_count => x_msg_count
2974 , p_msg_data => x_msg_data
2975 );
2976 WHEN fnd_api.g_exc_unexpected_error THEN
2977 IF PG_DEBUG <> 0 THEN
2978 oe_debug_pub.add(' prepare_bcol_temp_data: ' || ' Unexpected Exception in stmt num: '
2979 || to_char(lStmtNum), 1);
2980 END IF;
2981 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2982 -- Get message count and data
2983 cto_msg_pub.count_and_get
2984 ( p_msg_count => x_msg_count
2985 , p_msg_data => x_msg_data
2986 );
2987 WHEN OTHERS then
2988 oe_debug_pub.add('errmsg'||sqlerrm);
2989 IF PG_DEBUG <> 0 THEN
2990 oe_debug_pub.add(' prepare_bcol_temp_data: ' || 'Others Exception in stmt num: ' ||
2991 to_char(lStmtNum), 1);
2992 END IF;
2993 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2994 -- Get message count and data
2995 cto_msg_pub.count_and_get
2996 ( p_msg_count => x_msg_count
2997 , p_msg_data => x_msg_data
2998 );
2999
3000
3001
3002 END prepare_bcol_temp_data;
3003
3004 /*----------------------
3005 Checks if Ato model is present
3006 in the data
3007
3008 ------------------------*/
3009 PROCEDURE Insert_into_bcol_gt(
3010 p_match_rec_of_tab IN OUT NOCOPY CTO_Configured_Item_GRP.CTO_MATCH_REC_TYPE,
3011 x_return_status OUT NOCOPY VARCHAR2,
3012 x_msg_count OUT NOCOPY NUMBER,
3013 x_msg_data OUT NOCOPY VARCHAR2
3014 )
3015 IS
3016
3017 l_last_index number;
3018 lStmtNum number;
3019
3020 BEGIN
3021 x_return_status := FND_API.G_RET_STS_SUCCESS;
3022
3023 lStmtNum:=10;
3024 l_last_index := p_match_rec_of_tab.line_id.count;
3025 IF PG_DEBUG <> 0 THEN
3026
3027 oe_debug_pub.add('Last index ='||l_last_index,3);
3028 oe_debug_pub.add('first index ='||p_match_rec_of_tab.line_id.first,3);
3029 END IF;
3030
3031 -- rkaza. 11/30/2005. bug 4712706. This procedure is called during match
3032 -- and reserve flow. Each transaction processes a bunch of top ato models
3033 -- for match that are present in the pl/sql table. Here we insert these
3034 -- ato models and their components into bcol gt from pl/sql table. The
3035 -- rows remain within a session. So the next transaction still sees the
3036 -- old rows and this is causing the bug down the line. In CTOGCFGB ->
3037 -- match_configured_item procedure, we do a bulk collect finally from
3038 -- bcolgt into the pl/sql table. But since bcol gt has more rows than
3039 -- the pl/sql table, incorrect configs are matched to models.
3040 -- So executing a complete delete here from bcol gt.
3041
3042 delete from bom_cto_order_lines_gt;
3043
3044 IF PG_DEBUG <> 0 THEN
3045 oe_debug_pub.add('insert into bcol_gt',5);
3046 END IF;
3047
3048
3049 lStmtNum:=20;
3050 FORALL i in 1..l_last_index
3051 INSERT INTO bom_cto_order_lines_gt
3052 (
3053 ATO_LINE_ID,
3054 BOM_ITEM_TYPE,
3055 COMPONENT_CODE,
3056 COMPONENT_SEQUENCE_ID,
3057 INVENTORY_ITEM_ID,
3058 LINE_ID,
3059 LINK_TO_LINE_ID,
3060 ORDERED_QUANTITY,
3061 ORDER_QUANTITY_UOM,
3062 PARENT_ATO_LINE_ID,
3063 PLAN_LEVEL,
3064 TOP_MODEL_LINE_ID,
3065 WIP_SUPPLY_TYPE,
3066 SHIP_FROM_ORG_ID,
3067 VALIDATION_ORG --3503764
3068 )
3069 VALUES
3070 (
3071 p_match_rec_of_tab.ato_line_id(i),
3072 --added -1 to be consistent with CTOGOPIB insert
3073 -- -1 is used in where cluase in downstream procedure
3074 -- prepare_bcol_temp
3075 nvl(p_match_rec_of_tab.bom_item_type(i),-1),
3076 p_match_rec_of_tab.component_code(i),
3077 p_match_rec_of_tab.component_sequence_id(i),
3078 p_match_rec_of_tab.inventory_item_id(i),
3079 p_match_rec_of_tab.line_id(i),
3080 p_match_rec_of_tab.link_to_line_id(i),
3081 p_match_rec_of_tab.ordered_quantity(i),
3082 p_match_rec_of_tab.order_quantity_uom(i),
3083 p_match_rec_of_tab.parent_ato_line_id(i),
3084 p_match_rec_of_tab.plan_level(i),
3085 p_match_rec_of_tab.top_model_line_id(i),
3086 --added -1 to be consistent with CTOGOPIB insert
3087 -- -1 is used in where cluase in downstream procedure
3088 -- prepare_bcol_temp
3089 nvl(p_match_rec_of_tab.wip_supply_type(i),-1),
3090 nvl(p_match_rec_of_tab.ship_from_org_id(i),-99),--3555026
3091
3092 p_match_rec_of_tab.validation_org(i)--3503764
3093 );
3094
3095
3096 IF PG_DEBUG <> 0 THEN
3097 oe_debug_pub.add('Sql%row count ='||sql%rowcount,5);
3098 END IF;
3099
3100
3101
3102 EXCEPTION
3103 WHEN fnd_api.g_exc_error THEN
3104 IF PG_DEBUG <> 0 THEN
3105 oe_debug_pub.add('Insert_into_bcol_gt: ' || 'Exception in stmt num: '
3106 || to_char(lStmtNum), 1);
3107 END IF;
3108 x_return_status := FND_API.G_RET_STS_ERROR;
3109 -- Get message count and data
3110 cto_msg_pub.count_and_get
3111 ( p_msg_count => x_msg_count
3112 , p_msg_data => x_msg_data
3113 );
3114 WHEN fnd_api.g_exc_unexpected_error THEN
3115 IF PG_DEBUG <> 0 THEN
3116 oe_debug_pub.add('Insert_into_bcol_gt: ' || ' Unexpected Exception in stmt num: '
3117 || to_char(lStmtNum), 1);
3118 END IF;
3119 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3120 -- Get message count and data
3121 cto_msg_pub.count_and_get
3122 ( p_msg_count => x_msg_count
3123 , p_msg_data => x_msg_data
3124 );
3125 WHEN OTHERS then
3126 oe_debug_pub.add('errmsg'||sqlerrm);
3127 IF PG_DEBUG <> 0 THEN
3128 oe_debug_pub.add('error='||sqlerrm);
3129 oe_debug_pub.add('Insert_into_bcol_gt: ' || 'Others Exception in stmt num: '
3130 || to_char(lStmtNum), 1);
3131 END IF;
3132 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3133 -- Get message count and data
3134 cto_msg_pub.count_and_get
3135 ( p_msg_count => x_msg_count
3136 , p_msg_data => x_msg_data
3137 );
3138
3139
3140
3141
3142
3143 END Insert_into_bcol_gt;
3144
3145
3146 -- This procedure will get the Match_attribute from mtl_system_items_b
3147 -- Will process those flags.
3148 --Eg:
3149 -- Model levels Match_ttribute perform_match
3150 -- (from Item form) (calculated)
3151 -- M1 Y N
3152 -- ---M2 N N
3153 -- ----M3 Y Y
3154 -- If match flag is not passed it will be treated as 'Y'
3155 --only non-phantom models need to be passed
3156
3157 PROCEDURE Evaluate_N_Pop_Match_Flag
3158 (
3159 p_match_flag_tab IN MATCH_FLAG_TBL_TYPE,
3160 x_sparse_tab OUT NOCOPY MATCH_FLAG_TBL_TYPE,
3161 x_return_status OUT NOCOPY VARCHAR2,
3162 x_msg_count OUT NOCOPY NUMBER,
3163 x_msg_data OUT NOCOPY VARCHAR2
3164
3165 )
3166 IS
3167
3168 l_count number;
3169 lStmtNum number;
3170
3171
3172 TYPE TABNUM is TABLE of NUMBER index by binary_integer ;
3173 v_raw_line_id TABNUM ;
3174
3175 i number;
3176 j number;
3177 k number;
3178 l_sparse_index number;
3179 v_src_point number;
3180 v_prev_src_point number;
3181 l_custom_match_profile varchar2(10);
3182
3183 l_profile_value VARCHAR2(1) := 'Y'; --standard match as this API is
3184 --called when BOM: Match to Existing Configuration
3185 --is YEs
3186
3187
3188 BEGIN
3189 IF PG_DEBUG <> 0 THEN
3190 oe_debug_pub.add('Evaluate_N_Pop_Match_Flag:'||'BEGIN Evaluate_N_Pop_Match_Flag',5);
3191 END IF;
3192
3193 lStmtNum := 9;
3194 l_custom_match_profile := FND_PROFILE.Value('BOM:CUSTOM_MATCH');
3195
3196 IF PG_DEBUG <> 0 THEN
3197 oe_debug_pub.add('Evaluate_N_Pop_Match_Flag:'||'custome matc value=>'||l_custom_match_profile,5);
3198 END IF;
3199
3200 --if custom match is also YES then
3201 --we should use 'C' instead of 'Y'
3202 IF l_custom_match_profile = 1 THEN
3203
3204 IF PG_DEBUG <> 0 THEN
3205 oe_debug_pub.add('Evaluate_N_Pop_Match_Flag:'||'setting l_profile vale to C',5);
3206 END IF;
3207 l_profile_value := 'C';
3208
3209 END IF;
3210
3211 lStmtNum := 10;
3212
3213 l_count := p_match_flag_tab.count;
3214
3215 oe_debug_pub.add('Evaluate_N_Pop_Match_Flag:'||'Converting into sparse record structure indexed by line_id',5);
3216
3217 lStmtNum := 20;
3218 i := p_match_flag_tab.first ;
3219
3220 lStmtNum := 30;
3221 WHILE i is not null --sparse while
3222 LOOP
3223 l_sparse_index := p_match_flag_tab(i).line_id;
3224
3225 x_sparse_tab(l_sparse_index).line_id := p_match_flag_tab(i).line_id;
3226 x_sparse_tab(l_sparse_index).parent_ato_line_id := p_match_flag_tab(i).parent_ato_line_id;
3227 x_sparse_tab(l_sparse_index).ato_line_id := p_match_flag_tab(i).ato_line_id;
3228
3229 oe_debug_pub.add('Evaluate_N_Pop_Match_Flag:'||'original amtch flag=>'|| p_match_flag_tab(i).match_flag);
3230
3231 x_sparse_tab(l_sparse_index).match_flag := nvl(p_match_flag_tab(i).match_flag,l_profile_value);
3232
3233 i := p_match_flag_tab.next(i);
3234 END LOOP; --end of sparse while loop
3235
3236 --evaluating match flag
3237 lStmtNum := 40;
3238 j := x_sparse_tab.first;
3239
3240 lStmtNum := 50;
3241 WHILE j is not null --while loop B
3242 LOOP
3243 IF( x_sparse_tab.exists(j)) THEN
3244 v_src_point := j ;
3245
3246 IF x_sparse_tab(v_src_point).ato_line_id <> v_src_point THEN --check for ato model line
3247 IF x_sparse_tab(v_src_point).match_flag = 'N' THEN --check match =N
3248
3249 lStmtNum := 60;
3250 WHILE(x_sparse_tab.exists(v_src_point) ) --while loop C
3251 LOOP
3252
3253 IF x_sparse_tab(x_sparse_tab(v_src_point).parent_ato_line_id).match_flag
3254 = 'Y' THEN --check match =Y
3255
3256 v_prev_src_point := v_src_point ;
3257 v_src_point := x_sparse_tab(v_src_point).parent_ato_line_id;
3258 v_raw_line_id(v_raw_line_id.count + 1) := v_src_point ;
3259
3260 IF x_sparse_tab(v_src_point).ato_line_id = v_src_point THEN
3261 exit;
3262 END IF;
3263 ELSE
3264 exit;
3265
3266 END IF;--check match = Y
3267
3268
3269 END LOOP;--while loop C
3270
3271 lStmtNum := 70;
3272 k := v_raw_line_id.count ; /* total number of items to be resolved */
3273
3274
3275 lStmtNum := 80;
3276 WHILE( k >= 1 ) --while loop D
3277 LOOP
3278 x_sparse_tab(v_raw_line_id(k)).match_flag := 'N' ;
3279 k := k -1 ;
3280
3281 END LOOP ;--while loop D
3282
3283 v_raw_line_id.delete ; /* remove all elements as they have been resolved */
3284
3285 END IF; --check match =N
3286
3287 END IF;--check for ato model line
3288
3289 END IF;
3290
3291 lStmtNum := 90;
3292 j := x_sparse_tab.next(j) ; /* added for bug 1728383 for performance */
3293
3294
3295 END LOOP ;--while loop B
3296
3297
3298 --debug statement
3299 IF PG_DEBUG <> 0 THEN
3300 oe_debug_pub.add('LINE_ID =>'||' MATCH_FLAG');
3301
3302 lStmtNum := 100;
3303 j := x_sparse_tab.first;
3304
3305 lStmtNum := 110;
3306 WHILE j is not null --while loop C
3307 LOOP
3308
3309 oe_debug_pub.add(x_sparse_tab(j).line_id ||' => '||x_sparse_tab(j).match_flag, 5);
3310
3311 j := x_sparse_tab.next(j) ;
3312 END LOOP;
3313 END IF;--PG_DEBUG
3314
3315
3316
3317
3318
3319 EXCEPTION
3320 WHEN fnd_api.g_exc_error THEN
3321 IF PG_DEBUG <> 0 THEN
3322 oe_debug_pub.add('Evaluate_N_Pop_Match_Flag: ' || 'Exception in stmt num: '
3323 || to_char(lStmtNum), 1);
3324 END IF;
3325 x_return_status := FND_API.G_RET_STS_ERROR;
3326 -- Get message count and data
3327 cto_msg_pub.count_and_get
3328 ( p_msg_count => x_msg_count
3329 , p_msg_data => x_msg_data
3330 );
3331 WHEN fnd_api.g_exc_unexpected_error THEN
3332 IF PG_DEBUG <> 0 THEN
3333 oe_debug_pub.add('Evaluate_N_Pop_Match_Flag: ' || ' Unexpected Exception in stmt num: '
3334 || to_char(lStmtNum), 1);
3335 END IF;
3336 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3337 -- Get message count and data
3338 cto_msg_pub.count_and_get
3339 ( p_msg_count => x_msg_count
3340 , p_msg_data => x_msg_data
3341 );
3342 WHEN OTHERS then
3343 oe_debug_pub.add('errmsg'||sqlerrm);
3344 IF PG_DEBUG <> 0 THEN
3345 oe_debug_pub.add('error='||sqlerrm);
3346 oe_debug_pub.add('Evaluate_N_Pop_Match_Flag: ' || 'Others Exception in stmt num: '
3347 || to_char(lStmtNum), 1);
3348 END IF;
3349 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3350 -- Get message count and data
3351 cto_msg_pub.count_and_get
3352 ( p_msg_count => x_msg_count
3353 , p_msg_data => x_msg_data
3354 );
3355 END Evaluate_N_Pop_Match_Flag;
3356
3357
3358
3359 --This will transfer sparse record to record of tables
3360 PROCEDURE xfer_match_flag_to_rec_of_tab
3361 (
3362
3363 p_sparse_tab IN MATCH_FLAG_TBL_TYPE,
3364 x_match_flag_rec OUT NOCOPY Match_flag_rec_of_tab,
3365 x_return_status OUT NOCOPY VARCHAR2,
3366 x_msg_count OUT NOCOPY NUMBER,
3367 x_msg_data OUT NOCOPY VARCHAR2
3368
3369 )
3370 IS
3371 i binary_integer := 1;
3372 j number;
3373 lStmtNum number;
3374
3375
3376 BEGIN
3377
3378 IF PG_DEBUG <> 0 THEN
3379 oe_debug_pub.add('BEGIN xfer_match_flag_to_rec_of_tab: ', 5);
3380 END IF;
3381
3382 lStmtNum :=10;
3383 j:= p_sparse_tab.first;
3384
3385 lStmtNum :=20;
3386 WHILE(j is not null)
3387 LOOP
3388 x_match_flag_rec.line_id(i) := p_sparse_tab(j).line_id;
3389 x_match_flag_rec.match_flag(i) := p_sparse_tab(j).match_flag;
3390
3391 i := i+1;
3392 j := p_sparse_tab.next(j);
3393
3394 END LOOP;
3395
3396 EXCEPTION
3397 WHEN fnd_api.g_exc_error THEN
3398 IF PG_DEBUG <> 0 THEN
3399 oe_debug_pub.add('xfer_match_flag_to_rec_of_tab: ' || 'Exception in stmt num: '
3400 || to_char(lStmtNum), 1);
3401 END IF;
3402 x_return_status := FND_API.G_RET_STS_ERROR;
3403 -- Get message count and data
3404 cto_msg_pub.count_and_get
3405 ( p_msg_count => x_msg_count
3406 , p_msg_data => x_msg_data
3407 );
3408 WHEN fnd_api.g_exc_unexpected_error THEN
3409 IF PG_DEBUG <> 0 THEN
3410 oe_debug_pub.add('xfer_match_flag_to_rec_of_tab: ' || ' Unexpected Exception in stmt num: '
3411 || to_char(lStmtNum), 1);
3412 END IF;
3413 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3414 -- Get message count and data
3415 cto_msg_pub.count_and_get
3416 ( p_msg_count => x_msg_count
3417 , p_msg_data => x_msg_data
3418 );
3419 WHEN OTHERS then
3420 oe_debug_pub.add('errmsg'||sqlerrm);
3421 IF PG_DEBUG <> 0 THEN
3422 oe_debug_pub.add('error='||sqlerrm);
3423 oe_debug_pub.add('xfer_match_flag_to_rec_of_tab: ' || 'Others Exception in stmt num: '
3424 || to_char(lStmtNum), 1);
3425 END IF;
3426 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3427 -- Get message count and data
3428 cto_msg_pub.count_and_get
3429 ( p_msg_count => x_msg_count
3430 , p_msg_data => x_msg_data
3431 );
3432
3433 END xfer_match_flag_to_rec_of_tab;
3434
3435
3436 PROCEDURE Update_BCOLGT_with_match_flag
3437 (
3438 x_return_status OUT NOCOPY VARCHAR2,
3439 x_msg_count OUT NOCOPY NUMBER,
3440 x_msg_data OUT NOCOPY VARCHAR2
3441
3442 )
3443 IS
3444
3445
3446 l_match_flag_tab CTO_MATCH_CONFIG.MATCH_FLAG_TBL_TYPE;
3447 x_sparse_match_tab CTO_MATCH_CONFIG.MATCH_FLAG_TBL_TYPE;
3448 l_match_flag_rec_of_tab CTO_MATCH_CONFIG.Match_flag_rec_of_tab;
3449
3450 lStmtNum number;
3451 i number;
3452 j number;
3453
3454 CURSOR c_models_match_flag
3455 IS
3456 SELECT line_id,
3457 parent_ato_line_id,
3458 ato_line_id,
3459 perform_match
3460 FROM bom_cto_order_lines_gt
3461 WHERE bom_item_type = '1' -- put in inverted commas to use hint
3462 AND nvl(wip_supply_type,1)<> 6;
3463
3464
3465
3466
3467 BEGIN
3468
3469 IF PG_DEBUG <> 0 THEN
3470 oe_debug_pub.add('ENTERED Update_BCOLGT_with_match_flag', 5);
3471 END IF;
3472
3473 --added for re-arch
3474 --get match flag for all non-pahtom ato models
3475 lStmtNum :=10;
3476
3477 --as per perf std ahmed almori
3478 --If the global temporary table is referenced in a sub-query in a
3479 --SQL statement which accesses other tables.
3480 --In such cases, the join order may not be optimal due to the lack
3481 --of stats on the temp table, hence hints should be used to ensure the optimal join order.
3482
3483 UPDATE /*+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_N5) */ bom_cto_order_lines_gt bcol
3484 SET bcol.perform_match=
3485 (SELECT config_match
3486 FROM mtl_system_items_b mtl
3487 WHERE mtl.inventory_item_id = bcol.inventory_item_id
3488
3489 AND mtl.organization_id = bcol.validation_org --reuse_revert
3490 --3555026
3491
3492 )
3493 WHERE bcol.bom_item_type = '1'-- used inverted commas to use index
3494 AND nvl(bcol.wip_supply_type,1) <> 6;
3495
3496 IF PG_DEBUG <> 0 THEN
3497 oe_debug_pub.add('Update_BCOLGT_with_match_flag:'||'Sql%row count ='||sql%rowcount,3);
3498 END IF;
3499
3500
3501 --prepare a record structure for input paremeter to
3502 --procedure evaluate_n_pop_match
3503 j := 1;
3504
3505 lStmtNum := 20;
3506 FOR models_match_rec in c_models_match_flag
3507 LOOP
3508 l_match_flag_tab(j).line_id := models_match_rec.line_id;
3509 l_match_flag_tab(j).parent_ato_line_id := models_match_rec.parent_ato_line_id;
3510 l_match_flag_tab(j).ato_line_id := models_match_rec.ato_line_id;
3511 l_match_flag_tab(j).match_flag := models_match_rec.perform_match;
3512
3513 j := j+1 ;
3514 END LOOP;
3515
3516 --call evaluate_n_pop_match_flag proceure
3517 -- to process the match flag
3518 lStmtNum := 30;
3519 Evaluate_N_Pop_Match_Flag
3520 (
3521 p_match_flag_tab => l_match_flag_tab,
3522 x_sparse_tab => x_sparse_match_tab,
3523 x_return_status => x_return_status,
3524 x_msg_count => X_msg_count,
3525 x_msg_data => X_msg_data
3526
3527 );
3528
3529
3530 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
3531 --level1
3532 -- IF PG_DEBUG <> 0 THEN
3533 oe_debug_pub.add('Update_BCOLGT_with_match_flag:'||
3534 'success after Evaluate_N_Pop_Match_Flag', 1);
3535 -- END IF;
3536
3537 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
3538 RAISE fnd_api.g_exc_error;
3539 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3540 RAISE fnd_api.g_exc_unexpected_error;
3541 END IF;
3542
3543 lStmtNum := 40;
3544 CTO_MATCH_CONFIG.xfer_match_flag_to_rec_of_tab
3545 (
3546 p_sparse_tab => x_sparse_match_tab,
3547 x_match_flag_rec => l_match_flag_rec_of_tab,
3548 x_return_status => x_return_status,
3549 x_msg_count => X_msg_count,
3550 x_msg_data => X_msg_data
3551 );
3552
3553
3554 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
3555 --level1
3556 IF PG_DEBUG <> 0 THEN
3557 oe_debug_pub.add('Update_BCOLGT_with_match_flag:'||'success after xfer_match_flag_to_rec_of_tab', 1);
3558 END IF;
3559
3560 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
3561 RAISE fnd_api.g_exc_error;
3562 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3563 RAISE fnd_api.g_exc_unexpected_error;
3564 END IF;
3565
3566 lStmtNum := 50;
3567 FORALL i IN 1..l_match_flag_rec_of_tab.line_id.count
3568 UPDATE bom_cto_order_lines_gt
3569 SET perform_match = l_match_flag_rec_of_tab.match_flag(i)
3570 WHERE line_id = l_match_flag_rec_of_tab.line_id (i);
3571
3572
3573 IF PG_DEBUG <> 0 THEN
3574 oe_debug_pub.add('Update_BCOLGT_with_match_flag:'||'Sql%row count ='||sql%rowcount,3);
3575 END IF;
3576
3577 EXCEPTION
3578 WHEN fnd_api.g_exc_error THEN
3579 IF PG_DEBUG <> 0 THEN
3580 oe_debug_pub.add('Update_BCOLGT_with_match_flag ' || 'Exception in stmt num: '
3581 || to_char(lStmtNum), 1);
3582 END IF;
3583 x_return_status := FND_API.G_RET_STS_ERROR;
3584 -- Get message count and data
3585 cto_msg_pub.count_and_get
3586 ( p_msg_count => x_msg_count
3587 , p_msg_data => x_msg_data
3588 );
3589 WHEN fnd_api.g_exc_unexpected_error THEN
3590 IF PG_DEBUG <> 0 THEN
3591 oe_debug_pub.add('Update_BCOLGT_with_match_flag ' || ' Unexpected Exception in stmt num: '
3592 || to_char(lStmtNum), 1);
3593 END IF;
3594 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3595 -- Get message count and data
3596 cto_msg_pub.count_and_get
3597 ( p_msg_count => x_msg_count
3598 , p_msg_data => x_msg_data
3599 );
3600 WHEN OTHERS then
3601
3602 IF PG_DEBUG <> 0 THEN
3603
3604 oe_debug_pub.add('Update_BCOLGT_with_match_flag' || 'Others Exception in stmt num: '
3605 || to_char(lStmtNum), 1);
3606 oe_debug_pub.add('error '||sqlerrm,1);
3607 END IF;
3608 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3609 -- Get message count and data
3610 cto_msg_pub.count_and_get
3611 ( p_msg_count => x_msg_count
3612 , p_msg_data => x_msg_data
3613 );
3614
3615 END;
3616
3617
3618
3619 end CTO_MATCH_CONFIG;
[Home] [Help]