1 package body CTO_MATCH_CONFIG as
2 /* $Header: CTOMCFGB.pls 120.7.12020000.4 2013/03/29 10:04:01 abhissri 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 -- bug 16352937
1881 --
1882 PROCEDURE perform_match
1883 (
1884 p_ato_line_id in number,
1885 x_return_status OUT NOCOPY VARCHAR2,
1886 x_msg_count OUT NOCOPY NUMBER,
1887 x_msg_data OUT NOCOPY VARCHAR2
1888 )
1889 IS
1890 -- Bugfix 16531547: hash_value should be a varchar variable.
1891 -- l_hash_value number;
1892 l_hash_value varchar2(2000);
1893
1894 BEGIN
1895
1896 perform_match(
1897 p_ato_line_id,
1898 x_return_status,
1899 x_msg_count,
1900 x_msg_data,
1901 l_hash_value);
1902
1903 END perform_match;
1904
1905 --
1906 -- bug 16352937
1907 -- Overloaded the API to return l_hash_value
1908 --
1909 PROCEDURE perform_match
1910 (
1911 p_ato_line_id in number,
1912 -- p_custom_match_profile in VARCHAR2,
1913 x_return_status OUT NOCOPY VARCHAR2,
1914 x_msg_count OUT NOCOPY NUMBER,
1915 x_msg_data OUT NOCOPY VARCHAR2,
1916 -- Bugfix 16531547: hash_value should be varchar.
1917 -- x_hash_value OUT NOCOPY NUMBER
1918 x_hash_value OUT NOCOPY VARCHAR2
1919 )
1920 IS
1921
1922 cursor c_model_lines is
1923 select line_id, parent_ato_line_id, inventory_item_id
1924 from bom_cto_order_lines_gt
1925 where bom_item_type = '1'
1926 and ato_line_id = p_ato_line_id
1927 and nvl(wip_supply_type,0) <> 6
1928 and ato_line_id is not null -- could be a PTO
1929 and config_item_id is null --becos item could have been re-used
1930 and perform_match in ('Y','C')
1931 order by plan_level desc, inventory_item_id asc;
1932 /* bugfix 4227993: added item_id in the order by, so that 2 processes always process the
1933 sub-models in the same sequence. This should avoid deadlock issues while
1934 acquiring user-locks.
1935 */
1936
1937
1938 lStatus number;
1939 lXConfigId number;
1940 lPerformMatch varchar2(1);
1941 l_x_error_msg varchar2(100);
1942 l_x_msg_name varchar2(30);
1943 l_x_table_name varchar2(30);
1944 lStmtNum number;
1945 l_lock_status number; -- bugfix 4227993
1946 --
1947 -- bug 7203643
1948 -- changed the hash value variable type to varchar2
1949 -- ntungare
1950 --
1951 --l_hash_value number; -- bugfix 4227993
1952 l_hash_value varchar2(2000);
1953
1954 v_total_count number ;
1955
1956 BEGIN
1957
1958
1959 /*IF PG_DEBUG <> 0 THEN
1960 oe_debug_pub.add('Entered perform_match for =>'|| p_ato_line_id
1961 ||'custm prof=>'||p_custom_match_profile,1);
1962 END IF;*/
1963 x_return_status := FND_API.G_RET_STS_SUCCESS;
1964
1965 lStmtNum:=10;
1966 for lNextRec in c_model_lines loop
1967
1968 lXConfigId := NULL;
1969 lStmtNum :=20;
1970 select perform_match
1971 into lPerformMatch
1972 from bom_cto_order_lines_gt
1973 where line_id = lNextRec.line_id;
1974
1975
1976
1977
1978 IF PG_DEBUG <> 0 THEN
1979 oe_debug_pub.add(' perform_match flag =>'||lPerformMatch
1980 ||'for line_id =>'||lNextRec.line_id,5
1981 );
1982 END IF;
1983
1984 if (lPerformMatch = 'U') then
1985
1986 lStmtNum:=30;
1987 update bom_cto_order_lines_gt
1988 set perform_match = 'U'
1989 where line_id = lNextRec.parent_ato_line_id
1990 and perform_match in ('Y','C');
1991 -- if the update fails, its not an error
1992
1993 else
1994
1995 lStmtNum := 120;
1996 --
1997 -- Begin Bugfix 4227993
1998 -- Acquire user-lock by calling lock_for_match so that the process does not end up
1999 -- creating new configs if a non-commited match exists.
2000 -- Incase lock is not acquired, wait indefinitely. We could error out but we decided
2001 -- to wait so that user does not have to resubmit the process again.
2002 --
2003 IF( lPerformMatch in ( 'Y' , 'C' )) then
2004
2005 /*FND_FILE.PUT_LINE(FND_FILE.LOG, 'Calling cto_utility_pk.lock_for_match: start time: ' ||
2006 to_char(sysdate , 'MM/DD/YYYY HH24:MI:SS'));*/
2007 IF PG_DEBUG <> 0 THEN
2008 oe_debug_pub.add('Calling cto_utility_pk.lock_for_match: start time: ' || to_char(sysdate , 'MM/DD/YYYY HH24:MI:SS'), 1);
2009 END IF; --Bugfix 6452747
2010
2011 CTO_UTILITY_PK.lock_for_match(
2012 x_return_status => x_return_status,
2013 xMsgCount => x_msg_count,
2014 xMsgData => x_msg_data,
2015 x_lock_status => l_lock_status,
2016 x_hash_value => l_hash_value,
2017 p_line_id => lNextRec.line_id );
2018
2019 --
2020 -- bug 16352937
2021 --
2022 x_hash_value := l_hash_value;
2023
2024 /*FND_FILE.PUT_LINE(FND_FILE.LOG, 'Calling cto_utility_pk.lock_for_match: end time: ' ||
2025 to_char(sysdate , 'MM/DD/YYYY HH24:MI:SS'));*/
2026 IF PG_DEBUG <> 0 THEN
2027 oe_debug_pub.add('Calling cto_utility_pk.lock_for_match: end time: ' || to_char(sysdate , 'MM/DD/YYYY HH24:MI:SS'), 1);
2028 END IF; --Bugfix 6452747
2029
2030 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
2031 oe_debug_pub.add('match_and_create_all_items: '|| 'get_user_lock returned error');
2032 raise fnd_api.g_exc_unexpected_error;
2033 end if;
2034
2035 --check for error cases
2036 if ( l_lock_status <> 0 ) THEN
2037 if (l_lock_status = 1) then -- timeout
2038 oe_debug_pub.add('l_lock_status = 1: TIMEOUT ');
2039 cto_msg_pub.cto_message('BOM','CTO_LOCK_TIMEOUT');
2040 raise fnd_api.g_exc_unexpected_error;
2041
2042 elsif (l_lock_status = 2) then -- deadlock
2043 oe_debug_pub.add('l_lock_status = 2: DEADLOCK ');
2044 cto_msg_pub.cto_message('BOM','CTO_LOCK_DEADLOCK');
2045 raise fnd_api.g_exc_unexpected_error;
2046
2047 elsif (l_lock_status = 3) then -- parameter error
2048 oe_debug_pub.add('l_lock_status = 3: PARAMETER ERROR ');
2049 cto_msg_pub.cto_message('BOM','CTO_LOCK_PARAM_ERROR');
2050 raise fnd_api.g_exc_unexpected_error;
2051
2052 elsif (l_lock_status = 4) then -- already locked.
2053 oe_debug_pub.add('l_lock_status = 4: ALREADY LOCKED ERROR ');
2054 cto_msg_pub.cto_message('BOM','CTO_LOCK_ALREADY_LOCKED');
2055 -- we shall not raise an error if we are already holding the lock.
2056
2057 else -- internal error - not fault of user
2058 oe_debug_pub.add('l_lock_status = '||l_lock_status||': INTERNAL ERROR ');
2059 cto_msg_pub.cto_message('BOM','CTO_LOCK_ERROR');
2060 raise fnd_api.g_exc_unexpected_error;
2061 end if;
2062 else
2063 IF PG_DEBUG <> 0 THEN
2064 oe_debug_pub.add('match_and_create_all_items: ' || 'Successfully obtained lock for match.');
2065 END IF;
2066
2067 end if;
2068 END IF;
2069 --
2070 -- End Bugfix 4227993
2071 --
2072
2073 IF lPerformMatch = 'Y' THEN
2074
2075 lStmtNum:=40;
2076 lStatus := CTO_MATCH_CONFIG.check_config_match(
2077 lNextRec.line_id,
2078 lXConfigId,
2079 l_x_error_msg,
2080 l_x_msg_name);
2081
2082
2083
2084 if lStatus <> 1 then
2085 raise fnd_api.g_exc_error;
2086 end if;
2087 ELSIF lPerformMatch = 'C' THEN
2088 IF PG_DEBUG <> 0 THEN
2089 oe_debug_pub.add('match_and_create_all_items: ' || 'Custom Match.', 1);
2090 END IF;
2091
2092 lStmtNum:=50;
2093 lStatus := CTO_CUSTOM_MATCH_PK.find_matching_config(
2094 pModelLineId => lNextRec.line_id,
2095 xMatchedItemId => lXConfigId,
2096 xErrorMessage => l_x_error_msg,
2097 xMessageName => l_x_msg_name,
2098 xTableName => l_x_table_name);
2099
2100
2101 if lStatus <> 1 then
2102 raise fnd_api.g_exc_error;
2103 end if;
2104
2105 END IF;
2106
2107 if (lStatus = 1 and lXConfigId is null) then
2108
2109
2110 lStmtNum:=60;
2111 update bom_cto_order_lines_gt
2112 set perform_match = 'U'
2113 where line_id = lNextRec.line_id
2114 and perform_match in ('Y','C');
2115 -- if the update fails, its not an error
2116
2117 oe_debug_pub.add('perform_match: ' || 'updated to U : ' || to_char(lNextRec.Line_Id), 1);
2118
2119 oe_debug_pub.add('perform_match: ' || 'rowcount : ' || to_char(sql%rowcount), 1);
2120
2121 lStmtNum:=70;
2122 update bom_cto_order_lines_gt
2123 set perform_match = 'U'
2124 where line_id = lNextRec.parent_ato_line_id
2125 and perform_match in ( 'Y','C');
2126 -- if the update fails, its not an error
2127
2128 oe_debug_pub.add('perform_match: ' || 'updated to U : ' || to_char(lNextRec.parent_ato_Line_Id), 1);
2129
2130 oe_debug_pub.add('perform_match: ' || 'rowcount : ' || to_char(sql%rowcount), 1);
2131
2132 elsif (lStatus = 1 and lXConfigId is not null) then
2133
2134
2135 lStmtNum:=80;
2136 update bom_cto_order_lines_gt
2137 set config_item_id = lXConfigId
2138 where line_id = lNextRec.line_id;
2139
2140 --
2141 -- begin bugfix 4227993
2142 -- Release the lock if match found rather than wait for commit/rollback.
2143 --
2144
2145 IF PG_DEBUG <> 0 THEN
2146 oe_debug_pub.add('match_and_create_all_items: ' || 'Match found for line ' ||
2147 to_char(lNextRec.line_id) ||
2148 ' with config item ' ||
2149 to_char(lXConfigId), 1);
2150 END IF;
2151
2152 CTO_UTILITY_PK.release_lock(
2153 x_return_status => x_return_status,
2154 x_Msg_Count => x_Msg_Count,
2155 x_Msg_Data => x_msg_data,
2156 p_hash_value => l_hash_value);
2157
2158 if x_return_status <> FND_API.G_RET_STS_SUCCESS then
2159 oe_debug_pub.add('match_and_create_all_items: '|| 'get_user_lock returned error');
2160 raise fnd_api.g_exc_unexpected_error;
2161 end if;
2162
2163 --
2164 -- end bugfix 4227993
2165 --
2166
2167
2168 else
2169
2170 raise fnd_api.g_exc_error;
2171
2172 end if; -- end lStatus = 1 and lXConfigID is not null
2173
2174 end if; -- else perform_match = 'U'
2175
2176 lStmtNum := 160;
2177
2178
2179 end loop;
2180
2181
2182
2183
2184 /* Fix for bug 3533192 */
2185
2186 update bom_cto_order_lines_gt
2187 set perform_match = 'Y'
2188 where ato_line_id = p_ato_line_id
2189 and inventory_item_id in
2190 ( select inventory_item_id
2191 from bom_cto_order_lines_gt
2192 where ato_line_id = p_ato_line_id
2193 and bom_item_type = '1'
2194 and wip_supply_type <> 6
2195 and perform_match = 'U'
2196 group by inventory_item_id
2197 having count(*) > 1
2198 );
2199
2200
2201
2202
2203 oe_debug_pub.add('perform_match: ' || 'Updated possible similar models to Y : '
2204 || to_char(sql%rowcount), 1);
2205
2206
2207
2208
2209
2210
2211
2212 EXCEPTION
2213 WHEN fnd_api.g_exc_error THEN
2214 IF PG_DEBUG <> 0 THEN
2215 oe_debug_pub.add('perform_match: ' || 'Exception in stmt num: '
2216 || to_char(lStmtNum), 1);
2217 END IF;
2218 x_return_status := FND_API.G_RET_STS_ERROR;
2219 -- Get message count and data
2220 cto_msg_pub.count_and_get
2221 ( p_msg_count => x_msg_count
2222 , p_msg_data => x_msg_data
2223 );
2224 WHEN fnd_api.g_exc_unexpected_error THEN
2225 IF PG_DEBUG <> 0 THEN
2226 oe_debug_pub.add('perform_match: ' || ' Unexpected Exception in stmt num: '
2227 || to_char(lStmtNum), 1);
2228 END IF;
2229 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2230 -- Get message count and data
2231 cto_msg_pub.count_and_get
2232 ( p_msg_count => x_msg_count
2233 , p_msg_data => x_msg_data
2234 );
2235 WHEN OTHERS then
2236
2237 IF PG_DEBUG <> 0 THEN
2238 oe_debug_pub.add('perform_match: ' || 'Others Exception in stmt num: '
2239 || to_char(lStmtNum), 1);
2240 oe_debug_pub.add('errmsg'||sqlerrm,1);
2241 END IF;
2242 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2243 -- Get message count and data
2244 cto_msg_pub.count_and_get
2245 ( p_msg_count => x_msg_count
2246 , p_msg_data => x_msg_data
2247 );
2248
2249
2250 END perform_match;
2251
2252 PROCEDURE flag_reuse_config(
2253 p_model_line_id IN number,
2254 x_return_status OUT NOCOPY varchar2
2255
2256 )
2257 IS
2258
2259 l_model_line_id number;
2260 lStmtNum number :=10;
2261 BEGIN
2262
2263 x_return_status := FND_API.G_RET_STS_SUCCESS;
2264
2265
2266 g_reuse_tbl(p_model_line_id).reuse_config := 'N';
2267
2268 g_model_line_tbl(g_model_line_tbl.count+1):= p_model_line_id;
2269
2270 l_model_line_id := g_reuse_tbl(p_model_line_id).parent_ato_line_id;
2271
2272 --as this model might have a reuse flag =N
2273 --becuase of another child model /becuase of its own componenet
2274 --and also it atkes care of condition where top most ato
2275 --line is reached
2276 IF g_reuse_tbl(l_model_line_id).reuse_config= 'Y' THEN
2277
2278 flag_reuse_config(p_model_line_id =>l_model_line_id,
2279 x_return_status =>x_return_status
2280 );
2281 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2282 RAISE fnd_api.g_exc_unexpected_error;
2283 END IF;
2284
2285 END IF;
2286
2287
2288
2289
2290 EXCEPTION
2291 WHEN fnd_api.g_exc_unexpected_error THEN
2292 IF PG_DEBUG <> 0 THEN
2293 oe_debug_pub.add('flag_reuse_config: ' || ' Unexpected Exception in stmt num: '
2294 || to_char(lStmtNum), 1);
2295 END IF;
2296 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2297 /*-- Get message count and data
2298 cto_msg_pub.count_and_get
2299 ( p_msg_count => x_msg_count
2300 , p_msg_data => x_msg_data
2301 );*/
2302
2303 WHEN OTHERS then
2304
2305 IF PG_DEBUG <> 0 THEN
2306
2307 oe_debug_pub.add('flag_reuse_config: ' || 'Others Exception in stmt num: '
2308 || to_char(lStmtNum), 1);
2309 oe_debug_pub.add('error='||sqlerrm,1);
2310
2311 END IF;
2312 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2313
2314
2315
2316
2317 END flag_reuse_config;
2318
2319
2320 PROCEDURE CTO_REUSE_CONFIGURATION(
2321 p_ato_line_id IN number default null,
2322 x_config_change OUT NOCOPY varchar2,
2323 x_return_status OUT NOCOPY varchar2,
2324 x_msg_count OUT NOCOPY number,
2325 x_msg_data OUT NOCOPY varchar2
2326
2327 )
2328 IS
2329
2330 Type number_tbl_type IS TABLE OF number INDEX BY BINARY_INTEGER;
2331
2332 l_parent_ato_line_tbl number_tbl_type;
2333 l_ato_line_tbl number_tbl_type;
2334 l_unprocessed_parents number_tbl_type;
2335
2336
2337
2338 i number;
2339 l_parent_ato_last_index number;
2340 l_ato_last_index number;
2341
2342 lStmtNum number;
2343
2344 CURSOR c_single_ato is
2345 SELECT line_id,
2346 parent_ato_line_id,
2347 reuse_config
2348 FROM bom_cto_order_lines_gt
2349 --added nvl, bugfix 3530054
2350 WHERE nvl(wip_supply_type,1) <>6 --non phantom ato models
2351 AND bom_item_type = '1' --used inverted commas to use index N5
2352 AND ato_line_id = p_ato_line_id;
2353
2354
2355 CURSOR c_bulk is
2356 SELECT line_id,
2357 parent_ato_line_id,
2358 reuse_config
2359 FROM bom_cto_order_lines_gt
2360 ----added nvl, bugfix 3530054
2361 WHERE nvl(wip_supply_type,1) <>6 --non phantom
2362 AND bom_item_type = '1' ; --'1' for using idx_N5 --ato models
2363
2364 CURSOR c_gt_intial_pic is
2365 SELECT
2366 line_id,
2367 parent_ato_line_id,
2368 ato_line_id,
2369 wip_supply_type,
2370 bom_item_type,
2371 qty_per_parent_model,
2372 reuse_config
2373 FROM bom_cto_order_lines_gt
2374 WHERE ato_line_id = p_ato_line_id;
2375
2376 CURSOR c_debug is
2377 SELECT
2378 line_id,
2379 reuse_config,
2380 config_item_id,
2381 qty_per_parent_model,
2382 config_creation,
2383 ship_from_org_id,
2384 validation_org
2385 FROM bom_cto_order_lines_gt
2386 WHERE reuse_config is not null;
2387
2388
2389
2390
2391 --temporary structures use for debug
2392 l_temp_line_id number_tbl_type;
2393 l_qty_per_parent_model number_tbl_type;
2394 l_bcol_ato_line_tbl number_tbl_type;
2395
2396
2397
2398
2399 BEGIN
2400
2401
2402 IF PG_DEBUG <> 0 THEN
2403 oe_debug_pub.add('ENTERED reuse configuration',5);
2404 END IF;
2405 x_return_status := FND_API.G_RET_STS_SUCCESS;
2406
2407
2408
2409 lStmtNum:=10;
2410 IF p_ato_line_id IS NOT NULL THEN
2411
2412
2413 IF PG_DEBUG <> 0 THEN
2414 oe_debug_pub.add('reuse for ato line=>'||p_ato_line_id,1);
2415 END IF;
2416
2417 lStmtNum := 20;
2418
2419
2420
2421 UPDATE bom_cto_order_lines_gt
2422 SET reuse_config = 'Y'
2423 WHERE ato_line_id = p_ato_line_id
2424 AND bom_item_type = '1' --'1' uses idx_n5 --identifies non-phantom
2425 --need a nvl as for top most ato model there is no value
2426 AND nvl(WIP_SUPPLY_TYPE,1) <> 6; --model lines
2427
2428 lStmtNum:=30;
2429 l_ato_line_tbl(1) := p_ato_line_id;
2430
2431 --debug select
2432 IF PG_DEBUG = 5 THEN
2433 SELECT line_id,
2434 qty_per_parent_model
2435 BULK COLLECT INTO
2436 l_temp_line_id,
2437 l_qty_per_parent_model
2438 FROM bom_cto_order_lines_gt
2439 WHERE ato_line_id = p_ato_line_id;
2440
2441 oe_debug_pub.add('LINE_ID >>QTY_PER_PARENT_MODEL',5);
2442
2443 FOR i in l_temp_line_id.first..l_temp_line_id.last LOOP
2444
2445 oe_debug_pub.add(l_temp_line_id(i)||'>>'||l_qty_per_parent_model(i),5);
2446
2447 END LOOP;
2448
2449 oe_debug_pub.add('Picture of bcol_gt before reuse process',5);
2450
2451 FOR kiran_rec in c_gt_intial_pic
2452 LOOP
2453 oe_debug_pub.add('LINE_ID=>'||kiran_rec.line_id,5);
2454 oe_debug_pub.add('parent_LINE_ID=>'||kiran_rec.parent_ato_line_id,5);
2455 oe_debug_pub.add('ato_LINE_ID=>'||kiran_rec.ato_line_id,5);
2456 oe_debug_pub.add('WS=>'||kiran_rec.wip_supply_type,5);
2457 oe_debug_pub.add('BIT=>'||kiran_rec.bom_item_type,5);
2458 oe_debug_pub.add('QPM=>'||kiran_rec.qty_per_parent_model,5);
2459 oe_debug_pub.add('reuse=>'||kiran_rec.reuse_config,5);
2460
2461 END LOOP;
2462
2463
2464
2465 END IF;
2466 --debug end
2467
2468 ELSE --bulk call
2469
2470
2471 IF PG_DEBUG <> 0 THEN
2472 oe_debug_pub.add('reuse configuration BULK call',1);
2473 END IF;
2474
2475
2476 lStmtNum := 40;
2477 UPDATE bom_cto_order_lines_gt
2478 SET reuse_config = 'Y'
2479 WHERE bom_item_type = '1' --used idx_n5 --identifies non-phantom
2480 AND nvl(WIP_SUPPLY_TYPE,1) <> 6;
2481
2482 --had to use a slect clause
2483 --returning into cluase doesnot supoort distinct
2484 lStmtNum:=50;
2485 SELECT distinct(ato_line_id)
2486 BULK COLLECT INTO l_ato_line_tbl
2487 FROM bom_cto_order_lines_gt
2488 WHERE top_model_line_id is not null;
2489 END IF;
2490
2491 l_ato_last_index := l_ato_line_tbl.count;
2492
2493
2494
2495 IF PG_DEBUG <> 0 THEN
2496 oe_debug_pub.add('IF count>1, re-use called for more than 1 order line',5);
2497 oe_debug_pub.add('l_ato_line_tbl.count=>'||l_ato_line_tbl.count,5);
2498 END IF;
2499
2500
2501 IF l_ato_line_tbl.count > 0 THEN
2502
2503 BEGIN
2504
2505 -- rkaza. 12/06/2005. bug 4520992. Fp'ed bug 4493512.
2506 -- Added hint as per Perf team so that bcolgt drives the query
2507 SELECT /*+ leading(BCGT) use_nl(BCGT BCOL) */ distinct(bcol.ato_line_id)
2508 BULK COLLECT INTO l_bcol_ato_line_tbl
2509 FROM bom_cto_order_lines bcol,
2510 bom_cto_order_lines_gt bcgt
2511 WHERE bcgt.line_id = bcol.line_id
2512 AND bcol.qty_per_parent_model is null;
2513
2514 EXCEPTION
2515 WHEN others then
2516 null;
2517
2518 END;
2519
2520 IF l_bcol_ato_line_tbl.count > 0 THEN
2521
2522 IF PG_DEBUG <> 0 THEN
2523 oe_debug_pub.add('Updating bcol (permanent table) with qty per parent model',5);
2524
2525 END IF;
2526
2527 FORALL j IN 1..l_bcol_ato_line_tbl.last
2528 UPDATE bom_cto_order_lines child
2529 SET qty_per_parent_model =
2530 --used round to be consistent with can_configuration code
2531 ( SELECT ROUND(child.ordered_quantity/parent.ordered_quantity,7)
2532 FROM bom_cto_order_lines parent
2533 WHERE child.parent_ato_line_id= parent.line_id
2534 )
2535 --to filter out ato item order lines
2536 WHERE top_model_line_id is not null
2537 AND ato_line_id = l_bcol_ato_line_tbl(j) ;
2538
2539 END IF; -- l_bcol_ato_line_tbl.count > 0
2540
2541
2542 --bugfix 3503764
2543 --need config_creation as part of fix 3503764
2544 --in core reuse sql
2545 UPDATE bom_cto_order_lines_gt bcol_gt
2546 SET config_creation =
2547 --used round to be consistent with can_configuration code
2548 ( SELECT nvl(mtl.config_orgs,1)--3555026
2549 FROM mtl_system_items mtl
2550 WHERE mtl.inventory_item_id = bcol_gt.inventory_item_id
2551 AND mtl.organization_id = bcol_gt.validation_org--3555026
2552 )
2553 --to filter out ato item order lines
2554 WHERE top_model_line_id is not null
2555 AND bom_item_type= '1'
2556 --nvl as for top most model there wst is not populated
2557 AND nvl(wip_supply_type,1) <> 6;
2558
2559
2560 lStmtNum := 60;
2561 FORALL i in 1..l_ato_last_index
2562 UPDATE bom_cto_order_lines_gt bcolt
2563 SET bcolt.reuse_config = 'N'
2564 WHERE
2565 line_id in (
2566
2567 --bugfix start 3503764
2568 --if ware house is different then reuse = N
2569 (SELECT bcol_gt1.line_id
2570 FROM bom_cto_order_lines_gt bcol_gt1,
2571 bom_cto_order_lines bcol
2572 WHERE bcol.line_id = l_ato_line_tbl(i)
2573 AND bcol_gt1.config_creation in (1,2)
2574 AND bcol_gt1.ato_line_id = l_ato_line_tbl(i)
2575 AND bcol_gt1.ship_from_org_id <> bcol.ship_from_org_id
2576 AND bcol_gt1.bom_item_type = '1'
2577 AND nvl(bcol_gt1.wip_supply_type,1) <> 6
2578 )
2579 --end bugfix 3503764
2580
2581 UNION
2582 ( Select parent_ato_line_id
2583 from bom_cto_order_lines_gt bcolt1
2584 Where (bcolt1.line_id,
2585 bcolt1.qty_per_parent_model,
2586 bcolt1.inventory_item_id)
2587 not in ( Select line_id,
2588 qty_per_parent_model,
2589 inventory_item_id
2590 from bom_cto_order_lines
2591 where ato_line_id = l_ato_line_tbl(i) )
2592 --filters out pure ato item lines
2593 AND bcolt1.top_model_line_id is not null
2594 AND bcolt1.ato_line_id = l_ato_line_tbl(i)
2595 )
2596 -- bugfix 3381658 start
2597 UNION
2598 (Select parent_ato_line_id
2599 from bom_cto_order_lines bcol2
2600 Where (bcol2.line_id,
2601 bcol2.qty_per_parent_model,
2602 bcol2.inventory_item_id)
2603 not in ( Select bcolgt.line_id,
2604 bcolgt.qty_per_parent_model,
2605 bcolgt.inventory_item_id
2606 from bom_cto_order_lines_gt bcolgt
2607 where ato_line_id = l_ato_line_tbl(i) )
2608 --filters out pure ato item lines
2609 AND bcol2.top_model_line_id is not null
2610 AND bcol2.ato_line_id = l_ato_line_tbl(i)
2611 )
2612 --end bugfix 3381658
2613 )
2614 RETURNING parent_ato_line_id BULK COLLECT INTO l_parent_ato_line_tbl;
2615
2616
2617
2618 lStmtNum:= 70;
2619 IF l_parent_ato_line_tbl.EXISTS(1) THEN
2620
2621
2622
2623 IF PG_DEBUG <> 0 THEN
2624 oe_debug_pub.add('Some UN-reusable parent ato lines have been identified',5);
2625 FOR p_ato_idx in l_parent_ato_line_tbl.first..l_parent_ato_line_tbl.last
2626 LOOP
2627 oe_debug_pub.add('kiran'||l_parent_ato_line_tbl(p_ato_idx),5);
2628 END LOOP;
2629 END IF;
2630
2631
2632
2633
2634 l_parent_ato_last_index := l_parent_ato_line_tbl.LAST;
2635
2636 lStmtNum:= 80;
2637 IF p_ato_line_id IS NOT NULL THEN
2638 lStmtNum:=90;
2639 FOR bcol_rec in c_single_ato
2640 Loop
2641
2642 g_reuse_tbl(bcol_rec.line_id).line_id := bcol_rec.line_id;
2643 g_reuse_tbl(bcol_rec.line_id).parent_ato_line_id := bcol_rec.parent_ato_line_id;
2644 g_reuse_tbl(bcol_rec.line_id).reuse_config := bcol_rec.reuse_config;
2645
2646 IF PG_DEBUG <> 0 THEN
2647 oe_debug_pub.add('LINE_ID=>'||g_reuse_tbl(bcol_rec.line_id).line_id,5);
2648 oe_debug_pub.add('PARENT_ATO_LINE_ID=>'||g_reuse_tbl(bcol_rec.line_id).parent_ato_line_id,5);
2649 oe_debug_pub.add('REUSE_CONFIG_from_GT=>'||g_reuse_tbl(bcol_rec.line_id).reuse_config,5);
2650
2651 END IF;
2652
2653 End Loop;
2654
2655 ELSE --bulk call
2656 lStmtNum:=91;
2657 FOR bcol_rec in c_bulk
2658 Loop
2659
2660 g_reuse_tbl(bcol_rec.line_id).line_id := bcol_rec.line_id;
2661 g_reuse_tbl(bcol_rec.line_id).parent_ato_line_id := bcol_rec.parent_ato_line_id;
2662 g_reuse_tbl(bcol_rec.line_id).reuse_config := bcol_rec.reuse_config;
2663
2664 End Loop;
2665
2666
2667
2668 END IF;--check for bulk call
2669
2670
2671
2672 lStmtNum:= 100;
2673 FOR i IN l_parent_ato_line_tbl.FIRST..l_parent_ato_line_tbl.LAST LOOP
2674
2675
2676
2677 --previous update might have put reuse_config to N
2678 --so following if condition
2679 --OR previous element might have updated reuse to N
2680 IF g_reuse_tbl(l_parent_ato_line_tbl(i)).reuse_config= 'Y' THEN
2681
2682 IF PG_DEBUG <> 0 THEN
2683 oe_debug_pub.add('calling flag_reuse_config for model_line_id=>'
2684 ||l_parent_ato_line_tbl(i),5);
2685 END IF;
2686
2687 lStmtNum:= 110;
2688 flag_reuse_config(p_model_line_id =>l_parent_ato_line_tbl(i),
2689 x_return_status =>x_return_status
2690 );
2691 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2692 RAISE fnd_api.g_exc_unexpected_error;
2693 END IF;
2694
2695 END IF;--re-use = Y
2696 END LOOP;
2697
2698 lStmtNum:= 120;
2699
2700 IF g_model_line_tbl.EXISTS(1) THEN
2701
2702 IF PG_DEBUG <> 0 THEN
2703 oe_debug_pub.add('updating reuse_flag to N for following model lines =>',5);
2704 END IF;
2705
2706 FOR i IN g_model_line_tbl.FIRST..g_model_line_tbl.LAST LOOP
2707 IF PG_DEBUG <> 0 THEN
2708 oe_debug_pub.add(g_model_line_tbl(i),5);
2709 END IF;
2710
2711 END LOOP;
2712
2713 FORALL i in g_model_line_tbl.FIRST..g_model_line_tbl.LAST
2714 UPDATE bom_cto_order_lines_gt
2715 SET reuse_config = 'N'
2716 WHERE line_id = g_model_line_tbl(i);
2717 END IF;--g_model_line exists
2718
2719 ELSE
2720
2721
2722 -- IF PG_DEBUG <> 0 THEN
2723 oe_debug_pub.add('All parent ato lines are re-usable',5);--5 level
2724 -- END IF;
2725 END IF; -- l_parent_ato_line_tbl.EXISTS(1)
2726
2727 lStmtNum:=130;
2728 IF p_ato_line_id IS NOT NULL THEN
2729
2730 --deleted, as the procedure gets called again
2731 --for another ATO model line during ACC
2732 g_reuse_tbl.DELETE;
2733 g_model_line_tbl.DELETE;
2734
2735 --check if this needs to be a performance query
2736 lStmtNum := 140;
2737
2738 --as per perf std ahmed almori
2739 --If the global temporary table is referenced in a sub-query in a
2740 --SQL statement which accesses other tables.
2741 --In such cases, the join order may not be optimal due to the lack
2742 --of stats on the temp table, hence hints should be used to ensure the optimal join order.
2743
2744 UPDATE /*+ INDEX (bcolt BOM_CTO_ORDER_LINES_GT_N5) */bom_cto_order_lines_gt bcolt
2745 SET bcolt.config_item_id =
2746 (SELECT bcol.config_item_id
2747 FROM bom_cto_order_lines bcol
2748 WHERE bcolt.line_id = bcol.line_id
2749 )
2750 WHERE bcolt.bom_item_type = '1'--used idx_n5
2751 AND nvl(bcolt.WIP_SUPPLY_TYPE,1) <>6
2752 AND bcolt.reuse_config = 'Y'
2753 AND bcolt.ato_line_id = p_ato_line_id ;
2754 ELSE --bulk call
2755 --check if this needs to be a performance query
2756 lStmtNum:=150;
2757 --as per perf std ahmed almori
2758 --If the global temporary table is referenced in a sub-query in a
2759 --SQL statement which accesses other tables.
2760 --In such cases, the join order may not be optimal due to the lack
2761 --of stats on the temp table, hence hints should be used to ensure the optimal join order.
2762
2763 UPDATE /*+ INDEX (bcolt BOM_CTO_ORDER_LINES_GT_N5) */bom_cto_order_lines_gt bcolt
2764 SET bcolt.config_item_id =
2765 (SELECT bcol.config_item_id
2766 FROM bom_cto_order_lines bcol
2767 WHERE bcolt.line_id = bcol.line_id
2768 )
2769 WHERE bcolt.bom_item_type = '1' --used inverted commas, so that index is used
2770 AND nvl(bcolt.WIP_SUPPLY_TYPE,1) <>6
2771 AND bcolt.reuse_config = 'Y';
2772
2773
2774 END IF;
2775
2776 END IF;--if l_ato_line_tbl.count is > 0)
2777
2778 IF PG_DEBUG = 5 THEN
2779 Oe_debug_pub.add('LINE_ID--'||'reuse_config--'||
2780 'CONFIG_ITEM_ID--' ||'qty_per_parent_model--'
2781 ||'CIB--'||'ship_from_org--'||'validation_org');
2782 FOR debug_rec in c_debug
2783 LOOP
2784 oe_debug_pub.add(debug_rec.line_id||'--'||debug_rec.reuse_config||'--'||
2785 debug_rec.config_item_id||'--'||debug_rec.qty_per_parent_model||'--'||
2786 debug_rec.config_creation||'--'||debug_rec.ship_from_org_id||'--'||
2787 debug_rec.validation_org
2788 ,5);
2789 END LOOP;
2790 END IF;
2791
2792
2793 EXCEPTION
2794
2795 WHEN fnd_api.g_exc_error THEN
2796 IF PG_DEBUG <> 0 THEN
2797 oe_debug_pub.add('CTO_REUSE_CONFIGURATION: ' || 'Exception in stmt num: '
2798 || to_char(lStmtNum), 1);
2799 END IF;
2800 x_return_status := FND_API.G_RET_STS_ERROR;
2801 -- Get message count and data
2802 cto_msg_pub.count_and_get
2803 ( p_msg_count => x_msg_count
2804 , p_msg_data => x_msg_data
2805 );
2806 WHEN fnd_api.g_exc_unexpected_error THEN
2807 IF PG_DEBUG <> 0 THEN
2808 oe_debug_pub.add('CTO_REUSE_CONFIGURATION: ' || ' Unexpected Exception in stmt num: '
2809 || to_char(lStmtNum), 1);
2810 END IF;
2811 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2812 -- Get message count and data
2813 cto_msg_pub.count_and_get
2814 ( p_msg_count => x_msg_count
2815 , p_msg_data => x_msg_data
2816 );
2817 WHEN OTHERS then
2818
2819
2820 IF PG_DEBUG <> 0 THEN
2821 oe_debug_pub.add('CTO_REUSE_CONFIGURATION: ' || 'Others Exception in stmt num: '
2822 || to_char(lStmtNum), 1);
2823 oe_debug_pub.add('errmsg =>'||sqlerrm,1);
2824 END IF;
2825 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2826 -- Get message count and data
2827 cto_msg_pub.count_and_get
2828 ( p_msg_count => x_msg_count
2829 , p_msg_data => x_msg_data
2830 );
2831
2832
2833
2834 END CTO_REUSE_CONFIGURATION ;
2835
2836
2837
2838
2839
2840
2841
2842
2843
2844 PROCEDURE prepare_bcol_temp_data(
2845 p_source IN VARCHAR2,
2846 p_match_rec_of_tab IN OUT NOCOPY CTO_Configured_Item_GRP.CTO_MATCH_REC_TYPE,
2847 x_return_status OUT NOCOPY VARCHAR2,
2848 x_msg_count OUT NOCOPY NUMBER,
2849 x_msg_data OUT NOCOPY VARCHAR2
2850 )
2851 IS
2852 l_last_index number;
2853
2854 l_row_count number;
2855
2856 l_tab_of_rec CTO_Configured_Item_GRP.TEMP_TAB_OF_REC_TYPE;
2857 --l_return_status VARCHAR2(1);
2858 -- l_msg_count number;
2859 -- l_msg_data varchar2(;
2860 lStmtNum NUMBER;
2861
2862 BEGIN
2863
2864 x_return_status := FND_API.G_RET_STS_SUCCESS;
2865
2866 lStmtNum:=10;
2867 l_last_index := p_match_rec_of_tab.line_id.count;
2868
2869
2870
2871 lStmtNum:=20;
2872 UPDATE bom_cto_order_lines_gt bcol
2873 SET (bcol.wip_supply_type,
2874 bcol.bom_item_type )=
2875 (SELECT wip_supply_type,
2876 bom_item_type
2877 FROM bom_inventory_components bic
2878 WHERE bcol.component_sequence_id = bic.component_sequence_id
2879 )
2880 where bcol.ato_line_id <>bcol.line_id;
2881
2882
2883 oe_debug_pub.add('rowcount after update from bic=>'||sql%rowcount,5);
2884
2885
2886 --rowcount after insert of bom_item_type and wip_supply_type is l_rowcount;
2887
2888 --getting bom_item_type and wip_supply_type into cto_match_rec_type
2889 lStmtNum:=30;
2890 SELECT bom_item_type,
2891 wip_supply_type
2892 BULK COLLECT INTO
2893 p_match_rec_of_tab.bom_item_type,
2894 p_match_rec_of_tab.wip_supply_type
2895 FROM bom_cto_order_lines_gt;
2896
2897 oe_debug_pub.add('rowcount after select for BIT,WST=>'||sql%rowcount,5);
2898
2899 --rowcount of bom_itemtype,wip_supply_typ after select l_rowcount;
2900 lStmtNum:=40;
2901 xfer_tab_to_rec(
2902 p_match_rec_of_tab,
2903 l_tab_of_rec,
2904 x_return_status,
2905 x_msg_count,
2906 x_msg_data
2907 );
2908
2909 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
2910 oe_debug_pub.add('SUCCESS after xfer_tab_to_rec',5);
2911
2912 --add retrun status and error mesages to these
2913 lStmtNum:=50;
2914 populate_parent_ato(
2915 P_Source => p_source,
2916 P_tab_of_rec => l_tab_of_rec,
2917 x_return_status => x_return_status,
2918 x_msg_count => x_msg_count,
2919 x_msg_data => x_msg_data );
2920
2921 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
2922 oe_debug_pub.add('status after after xfer_tab_to_rec=>'
2923 || FND_API.G_RET_STS_ERROR,5);
2924 RAISE fnd_api.g_exc_error;
2925 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2926 oe_debug_pub.add('status after after xfer_tab_to_rec=>'
2927 || FND_API.G_RET_STS_UNEXP_ERROR,5);
2928 RAISE fnd_api.g_exc_unexpected_error;
2929 END IF;
2930
2931
2932
2933 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
2934 oe_debug_pub.add('SUCCESS after populate_parent_ato=>');
2935 lStmtNum:=60;
2936 populate_plan_level( P_tab_of_rec => l_tab_of_rec,
2937 x_return_status => x_return_status,
2938 x_msg_count => x_msg_count,
2939 x_msg_data => x_msg_data );
2940 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
2941 oe_debug_pub.add('status after after populate_parent_ato=>'
2942 || FND_API.G_RET_STS_ERROR);
2943 RAISE fnd_api.g_exc_error;
2944 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2945 oe_debug_pub.add('status after after populate_parent_ato=>'
2946 || FND_API.G_RET_STS_UNEXP_ERROR );
2947 RAISE fnd_api.g_exc_unexpected_error;
2948 END IF;
2949
2950
2951
2952 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
2953 oe_debug_pub.add('SUCCESS after populate_plan_level=>');
2954
2955 lStmtNum:=70;
2956 xfer_rec_to_tab(
2957 p_tab_of_rec => l_tab_of_rec ,
2958 p_match_rec_of_tab => p_match_rec_of_tab,
2959 x_return_status => x_return_status,
2960 x_msg_count => x_msg_count,
2961 x_msg_data => x_msg_data
2962 );
2963
2964 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
2965 oe_debug_pub.add('status after after populate_plan_level=>'
2966 || FND_API.G_RET_STS_ERROR);
2967 RAISE fnd_api.g_exc_error;
2968 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2969 oe_debug_pub.add('status after after populate_plan_level=>'
2970 || FND_API.G_RET_STS_UNEXP_ERROR );
2971 RAISE fnd_api.g_exc_unexpected_error;
2972 END IF;
2973
2974
2975 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
2976 oe_debug_pub.add('SUCCESS after xfer_rec_to_tabl=>');
2977
2978 lStmtNum:=80;
2979 --update the remaining columns into bcol_temp
2980
2981 FORALL i IN 1..l_last_index
2982 UPDATE bom_cto_order_lines_gt
2983 SET PARENT_ATO_LINE_ID = p_match_rec_of_tab.PARENT_ATO_LINE_ID(i),
2984 GOP_PARENT_ATO_LINE_ID = p_match_rec_of_tab.GOP_PARENT_ATO_LINE_ID(i),
2985 PLAN_LEVEL = p_match_rec_of_tab.PLAN_LEVEL (i)
2986 WHERE line_id = p_match_rec_of_tab.LINE_ID(i);
2987
2988 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
2989 oe_debug_pub.add('status after after xfer_rec_to_tab=>'
2990 || FND_API.G_RET_STS_ERROR);
2991 RAISE fnd_api.g_exc_error;
2992 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2993 oe_debug_pub.add('status after after xfer_rec_to_tab=>'
2994 || FND_API.G_RET_STS_UNEXP_ERROR );
2995 RAISE fnd_api.g_exc_unexpected_error;
2996 END IF;
2997
2998
2999 EXCEPTION
3000 WHEN fnd_api.g_exc_error THEN
3001 IF PG_DEBUG <> 0 THEN
3002 oe_debug_pub.add(' prepare_bcol_temp_data: ' || 'Exception in stmt num: '
3003 || to_char(lStmtNum), 1);
3004 END IF;
3005 x_return_status := FND_API.G_RET_STS_ERROR;
3006 -- Get message count and data
3007 cto_msg_pub.count_and_get
3008 ( p_msg_count => x_msg_count
3009 , p_msg_data => x_msg_data
3010 );
3011 WHEN fnd_api.g_exc_unexpected_error THEN
3012 IF PG_DEBUG <> 0 THEN
3013 oe_debug_pub.add(' prepare_bcol_temp_data: ' || ' Unexpected Exception in stmt num: '
3014 || to_char(lStmtNum), 1);
3015 END IF;
3016 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3017 -- Get message count and data
3018 cto_msg_pub.count_and_get
3019 ( p_msg_count => x_msg_count
3020 , p_msg_data => x_msg_data
3021 );
3022 WHEN OTHERS then
3023 oe_debug_pub.add('errmsg'||sqlerrm);
3024 IF PG_DEBUG <> 0 THEN
3025 oe_debug_pub.add(' prepare_bcol_temp_data: ' || 'Others Exception in stmt num: ' ||
3026 to_char(lStmtNum), 1);
3027 END IF;
3028 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3029 -- Get message count and data
3030 cto_msg_pub.count_and_get
3031 ( p_msg_count => x_msg_count
3032 , p_msg_data => x_msg_data
3033 );
3034
3035
3036
3037 END prepare_bcol_temp_data;
3038
3039 /*----------------------
3040 Checks if Ato model is present
3041 in the data
3042
3043 ------------------------*/
3044 PROCEDURE Insert_into_bcol_gt(
3045 p_match_rec_of_tab IN OUT NOCOPY CTO_Configured_Item_GRP.CTO_MATCH_REC_TYPE,
3046 x_return_status OUT NOCOPY VARCHAR2,
3047 x_msg_count OUT NOCOPY NUMBER,
3048 x_msg_data OUT NOCOPY VARCHAR2
3049 )
3050 IS
3051
3052 l_last_index number;
3053 lStmtNum number;
3054
3055 BEGIN
3056 x_return_status := FND_API.G_RET_STS_SUCCESS;
3057
3058 lStmtNum:=10;
3059 l_last_index := p_match_rec_of_tab.line_id.count;
3060 IF PG_DEBUG <> 0 THEN
3061
3062 oe_debug_pub.add('Last index ='||l_last_index,3);
3063 oe_debug_pub.add('first index ='||p_match_rec_of_tab.line_id.first,3);
3064 END IF;
3065
3066 -- rkaza. 11/30/2005. bug 4712706. This procedure is called during match
3067 -- and reserve flow. Each transaction processes a bunch of top ato models
3068 -- for match that are present in the pl/sql table. Here we insert these
3069 -- ato models and their components into bcol gt from pl/sql table. The
3070 -- rows remain within a session. So the next transaction still sees the
3071 -- old rows and this is causing the bug down the line. In CTOGCFGB ->
3072 -- match_configured_item procedure, we do a bulk collect finally from
3073 -- bcolgt into the pl/sql table. But since bcol gt has more rows than
3074 -- the pl/sql table, incorrect configs are matched to models.
3075 -- So executing a complete delete here from bcol gt.
3076
3077 delete from bom_cto_order_lines_gt;
3078
3079 IF PG_DEBUG <> 0 THEN
3080 oe_debug_pub.add('insert into bcol_gt',5);
3081 END IF;
3082
3083
3084 lStmtNum:=20;
3085 FORALL i in 1..l_last_index
3086 INSERT INTO bom_cto_order_lines_gt
3087 (
3088 ATO_LINE_ID,
3089 BOM_ITEM_TYPE,
3090 COMPONENT_CODE,
3091 COMPONENT_SEQUENCE_ID,
3092 INVENTORY_ITEM_ID,
3093 LINE_ID,
3094 LINK_TO_LINE_ID,
3095 ORDERED_QUANTITY,
3096 ORDER_QUANTITY_UOM,
3097 PARENT_ATO_LINE_ID,
3098 PLAN_LEVEL,
3099 TOP_MODEL_LINE_ID,
3100 WIP_SUPPLY_TYPE,
3101 SHIP_FROM_ORG_ID,
3102 VALIDATION_ORG --3503764
3103 )
3104 VALUES
3105 (
3106 p_match_rec_of_tab.ato_line_id(i),
3107 --added -1 to be consistent with CTOGOPIB insert
3108 -- -1 is used in where cluase in downstream procedure
3109 -- prepare_bcol_temp
3110 nvl(p_match_rec_of_tab.bom_item_type(i),-1),
3111 p_match_rec_of_tab.component_code(i),
3112 p_match_rec_of_tab.component_sequence_id(i),
3113 p_match_rec_of_tab.inventory_item_id(i),
3114 p_match_rec_of_tab.line_id(i),
3115 p_match_rec_of_tab.link_to_line_id(i),
3116 p_match_rec_of_tab.ordered_quantity(i),
3117 p_match_rec_of_tab.order_quantity_uom(i),
3118 p_match_rec_of_tab.parent_ato_line_id(i),
3119 p_match_rec_of_tab.plan_level(i),
3120 p_match_rec_of_tab.top_model_line_id(i),
3121 --added -1 to be consistent with CTOGOPIB insert
3122 -- -1 is used in where cluase in downstream procedure
3123 -- prepare_bcol_temp
3124 nvl(p_match_rec_of_tab.wip_supply_type(i),-1),
3125 nvl(p_match_rec_of_tab.ship_from_org_id(i),-99),--3555026
3126
3127 p_match_rec_of_tab.validation_org(i)--3503764
3128 );
3129
3130
3131 IF PG_DEBUG <> 0 THEN
3132 oe_debug_pub.add('Sql%row count ='||sql%rowcount,5);
3133 END IF;
3134
3135
3136
3137 EXCEPTION
3138 WHEN fnd_api.g_exc_error THEN
3139 IF PG_DEBUG <> 0 THEN
3140 oe_debug_pub.add('Insert_into_bcol_gt: ' || 'Exception in stmt num: '
3141 || to_char(lStmtNum), 1);
3142 END IF;
3143 x_return_status := FND_API.G_RET_STS_ERROR;
3144 -- Get message count and data
3145 cto_msg_pub.count_and_get
3146 ( p_msg_count => x_msg_count
3147 , p_msg_data => x_msg_data
3148 );
3149 WHEN fnd_api.g_exc_unexpected_error THEN
3150 IF PG_DEBUG <> 0 THEN
3151 oe_debug_pub.add('Insert_into_bcol_gt: ' || ' Unexpected Exception in stmt num: '
3152 || to_char(lStmtNum), 1);
3153 END IF;
3154 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3155 -- Get message count and data
3156 cto_msg_pub.count_and_get
3157 ( p_msg_count => x_msg_count
3158 , p_msg_data => x_msg_data
3159 );
3160 WHEN OTHERS then
3161 oe_debug_pub.add('errmsg'||sqlerrm);
3162 IF PG_DEBUG <> 0 THEN
3163 oe_debug_pub.add('error='||sqlerrm);
3164 oe_debug_pub.add('Insert_into_bcol_gt: ' || 'Others Exception in stmt num: '
3165 || to_char(lStmtNum), 1);
3166 END IF;
3167 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3168 -- Get message count and data
3169 cto_msg_pub.count_and_get
3170 ( p_msg_count => x_msg_count
3171 , p_msg_data => x_msg_data
3172 );
3173
3174
3175
3176
3177
3178 END Insert_into_bcol_gt;
3179
3180
3181 -- This procedure will get the Match_attribute from mtl_system_items_b
3182 -- Will process those flags.
3183 --Eg:
3184 -- Model levels Match_ttribute perform_match
3185 -- (from Item form) (calculated)
3186 -- M1 Y N
3187 -- ---M2 N N
3188 -- ----M3 Y Y
3189 -- If match flag is not passed it will be treated as 'Y'
3190 --only non-phantom models need to be passed
3191
3192 PROCEDURE Evaluate_N_Pop_Match_Flag
3193 (
3194 p_match_flag_tab IN MATCH_FLAG_TBL_TYPE,
3195 x_sparse_tab OUT NOCOPY MATCH_FLAG_TBL_TYPE,
3196 x_return_status OUT NOCOPY VARCHAR2,
3197 x_msg_count OUT NOCOPY NUMBER,
3198 x_msg_data OUT NOCOPY VARCHAR2
3199
3200 )
3201 IS
3202
3203 l_count number;
3204 lStmtNum number;
3205
3206
3207 TYPE TABNUM is TABLE of NUMBER index by binary_integer ;
3208 v_raw_line_id TABNUM ;
3209
3210 i number;
3211 j number;
3212 k number;
3213 l_sparse_index number;
3214 v_src_point number;
3215 v_prev_src_point number;
3216 l_custom_match_profile varchar2(10);
3217
3218 l_profile_value VARCHAR2(1) := 'Y'; --standard match as this API is
3219 --called when BOM: Match to Existing Configuration
3220 --is YEs
3221
3222
3223 BEGIN
3224 IF PG_DEBUG <> 0 THEN
3225 oe_debug_pub.add('Evaluate_N_Pop_Match_Flag:'||'BEGIN Evaluate_N_Pop_Match_Flag',5);
3226 END IF;
3227
3228 lStmtNum := 9;
3229 l_custom_match_profile := FND_PROFILE.Value('BOM:CUSTOM_MATCH');
3230
3231 IF PG_DEBUG <> 0 THEN
3232 oe_debug_pub.add('Evaluate_N_Pop_Match_Flag:'||'custome matc value=>'||l_custom_match_profile,5);
3233 END IF;
3234
3235 --if custom match is also YES then
3236 --we should use 'C' instead of 'Y'
3237 IF l_custom_match_profile = 1 THEN
3238
3239 IF PG_DEBUG <> 0 THEN
3240 oe_debug_pub.add('Evaluate_N_Pop_Match_Flag:'||'setting l_profile vale to C',5);
3241 END IF;
3242 l_profile_value := 'C';
3243
3244 END IF;
3245
3246 lStmtNum := 10;
3247
3248 l_count := p_match_flag_tab.count;
3249
3250 oe_debug_pub.add('Evaluate_N_Pop_Match_Flag:'||'Converting into sparse record structure indexed by line_id',5);
3251
3252 lStmtNum := 20;
3253 i := p_match_flag_tab.first ;
3254
3255 lStmtNum := 30;
3256 WHILE i is not null --sparse while
3257 LOOP
3258 l_sparse_index := p_match_flag_tab(i).line_id;
3259
3260 x_sparse_tab(l_sparse_index).line_id := p_match_flag_tab(i).line_id;
3261 x_sparse_tab(l_sparse_index).parent_ato_line_id := p_match_flag_tab(i).parent_ato_line_id;
3262 x_sparse_tab(l_sparse_index).ato_line_id := p_match_flag_tab(i).ato_line_id;
3263
3264 oe_debug_pub.add('Evaluate_N_Pop_Match_Flag:'||'original amtch flag=>'|| p_match_flag_tab(i).match_flag);
3265
3266 x_sparse_tab(l_sparse_index).match_flag := nvl(p_match_flag_tab(i).match_flag,l_profile_value);
3267
3268 i := p_match_flag_tab.next(i);
3269 END LOOP; --end of sparse while loop
3270
3271 --evaluating match flag
3272 lStmtNum := 40;
3273 j := x_sparse_tab.first;
3274
3275 lStmtNum := 50;
3276 WHILE j is not null --while loop B
3277 LOOP
3278 IF( x_sparse_tab.exists(j)) THEN
3279 v_src_point := j ;
3280
3281 IF x_sparse_tab(v_src_point).ato_line_id <> v_src_point THEN --check for ato model line
3282 IF x_sparse_tab(v_src_point).match_flag = 'N' THEN --check match =N
3283
3284 lStmtNum := 60;
3285 WHILE(x_sparse_tab.exists(v_src_point) ) --while loop C
3286 LOOP
3287
3288 IF x_sparse_tab(x_sparse_tab(v_src_point).parent_ato_line_id).match_flag
3289 = 'Y' THEN --check match =Y
3290
3291 v_prev_src_point := v_src_point ;
3292 v_src_point := x_sparse_tab(v_src_point).parent_ato_line_id;
3293 v_raw_line_id(v_raw_line_id.count + 1) := v_src_point ;
3294
3295 IF x_sparse_tab(v_src_point).ato_line_id = v_src_point THEN
3296 exit;
3297 END IF;
3298 ELSE
3299 exit;
3300
3301 END IF;--check match = Y
3302
3303
3304 END LOOP;--while loop C
3305
3306 lStmtNum := 70;
3307 k := v_raw_line_id.count ; /* total number of items to be resolved */
3308
3309
3310 lStmtNum := 80;
3311 WHILE( k >= 1 ) --while loop D
3312 LOOP
3313 x_sparse_tab(v_raw_line_id(k)).match_flag := 'N' ;
3314 k := k -1 ;
3315
3316 END LOOP ;--while loop D
3317
3318 v_raw_line_id.delete ; /* remove all elements as they have been resolved */
3319
3320 END IF; --check match =N
3321
3322 END IF;--check for ato model line
3323
3324 END IF;
3325
3326 lStmtNum := 90;
3327 j := x_sparse_tab.next(j) ; /* added for bug 1728383 for performance */
3328
3329
3330 END LOOP ;--while loop B
3331
3332
3333 --debug statement
3334 IF PG_DEBUG <> 0 THEN
3335 oe_debug_pub.add('LINE_ID =>'||' MATCH_FLAG');
3336
3337 lStmtNum := 100;
3338 j := x_sparse_tab.first;
3339
3340 lStmtNum := 110;
3341 WHILE j is not null --while loop C
3342 LOOP
3343
3344 oe_debug_pub.add(x_sparse_tab(j).line_id ||' => '||x_sparse_tab(j).match_flag, 5);
3345
3346 j := x_sparse_tab.next(j) ;
3347 END LOOP;
3348 END IF;--PG_DEBUG
3349
3350
3351
3352
3353
3354 EXCEPTION
3355 WHEN fnd_api.g_exc_error THEN
3356 IF PG_DEBUG <> 0 THEN
3357 oe_debug_pub.add('Evaluate_N_Pop_Match_Flag: ' || 'Exception in stmt num: '
3358 || to_char(lStmtNum), 1);
3359 END IF;
3360 x_return_status := FND_API.G_RET_STS_ERROR;
3361 -- Get message count and data
3362 cto_msg_pub.count_and_get
3363 ( p_msg_count => x_msg_count
3364 , p_msg_data => x_msg_data
3365 );
3366 WHEN fnd_api.g_exc_unexpected_error THEN
3367 IF PG_DEBUG <> 0 THEN
3368 oe_debug_pub.add('Evaluate_N_Pop_Match_Flag: ' || ' Unexpected Exception in stmt num: '
3369 || to_char(lStmtNum), 1);
3370 END IF;
3371 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3372 -- Get message count and data
3373 cto_msg_pub.count_and_get
3374 ( p_msg_count => x_msg_count
3375 , p_msg_data => x_msg_data
3376 );
3377 WHEN OTHERS then
3378 oe_debug_pub.add('errmsg'||sqlerrm);
3379 IF PG_DEBUG <> 0 THEN
3380 oe_debug_pub.add('error='||sqlerrm);
3381 oe_debug_pub.add('Evaluate_N_Pop_Match_Flag: ' || 'Others Exception in stmt num: '
3382 || to_char(lStmtNum), 1);
3383 END IF;
3384 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3385 -- Get message count and data
3386 cto_msg_pub.count_and_get
3387 ( p_msg_count => x_msg_count
3388 , p_msg_data => x_msg_data
3389 );
3390 END Evaluate_N_Pop_Match_Flag;
3391
3392
3393
3394 --This will transfer sparse record to record of tables
3395 PROCEDURE xfer_match_flag_to_rec_of_tab
3396 (
3397
3398 p_sparse_tab IN MATCH_FLAG_TBL_TYPE,
3399 x_match_flag_rec OUT NOCOPY Match_flag_rec_of_tab,
3400 x_return_status OUT NOCOPY VARCHAR2,
3401 x_msg_count OUT NOCOPY NUMBER,
3402 x_msg_data OUT NOCOPY VARCHAR2
3403
3404 )
3405 IS
3406 i binary_integer := 1;
3407 j number;
3408 lStmtNum number;
3409
3410
3411 BEGIN
3412
3413 IF PG_DEBUG <> 0 THEN
3414 oe_debug_pub.add('BEGIN xfer_match_flag_to_rec_of_tab: ', 5);
3415 END IF;
3416
3417 lStmtNum :=10;
3418 j:= p_sparse_tab.first;
3419
3420 lStmtNum :=20;
3421 WHILE(j is not null)
3422 LOOP
3423 x_match_flag_rec.line_id(i) := p_sparse_tab(j).line_id;
3424 x_match_flag_rec.match_flag(i) := p_sparse_tab(j).match_flag;
3425
3426 i := i+1;
3427 j := p_sparse_tab.next(j);
3428
3429 END LOOP;
3430
3431 EXCEPTION
3432 WHEN fnd_api.g_exc_error THEN
3433 IF PG_DEBUG <> 0 THEN
3434 oe_debug_pub.add('xfer_match_flag_to_rec_of_tab: ' || 'Exception in stmt num: '
3435 || to_char(lStmtNum), 1);
3436 END IF;
3437 x_return_status := FND_API.G_RET_STS_ERROR;
3438 -- Get message count and data
3439 cto_msg_pub.count_and_get
3440 ( p_msg_count => x_msg_count
3441 , p_msg_data => x_msg_data
3442 );
3443 WHEN fnd_api.g_exc_unexpected_error THEN
3444 IF PG_DEBUG <> 0 THEN
3445 oe_debug_pub.add('xfer_match_flag_to_rec_of_tab: ' || ' Unexpected Exception in stmt num: '
3446 || to_char(lStmtNum), 1);
3447 END IF;
3448 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3449 -- Get message count and data
3450 cto_msg_pub.count_and_get
3451 ( p_msg_count => x_msg_count
3452 , p_msg_data => x_msg_data
3453 );
3454 WHEN OTHERS then
3455 oe_debug_pub.add('errmsg'||sqlerrm);
3456 IF PG_DEBUG <> 0 THEN
3457 oe_debug_pub.add('error='||sqlerrm);
3458 oe_debug_pub.add('xfer_match_flag_to_rec_of_tab: ' || 'Others Exception in stmt num: '
3459 || to_char(lStmtNum), 1);
3460 END IF;
3461 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3462 -- Get message count and data
3463 cto_msg_pub.count_and_get
3464 ( p_msg_count => x_msg_count
3465 , p_msg_data => x_msg_data
3466 );
3467
3468 END xfer_match_flag_to_rec_of_tab;
3469
3470
3471 PROCEDURE Update_BCOLGT_with_match_flag
3472 (
3473 x_return_status OUT NOCOPY VARCHAR2,
3474 x_msg_count OUT NOCOPY NUMBER,
3475 x_msg_data OUT NOCOPY VARCHAR2
3476
3477 )
3478 IS
3479
3480
3481 l_match_flag_tab CTO_MATCH_CONFIG.MATCH_FLAG_TBL_TYPE;
3482 x_sparse_match_tab CTO_MATCH_CONFIG.MATCH_FLAG_TBL_TYPE;
3483 l_match_flag_rec_of_tab CTO_MATCH_CONFIG.Match_flag_rec_of_tab;
3484
3485 lStmtNum number;
3486 i number;
3487 j number;
3488
3489 CURSOR c_models_match_flag
3490 IS
3491 SELECT line_id,
3492 parent_ato_line_id,
3493 ato_line_id,
3494 perform_match
3495 FROM bom_cto_order_lines_gt
3496 WHERE bom_item_type = '1' -- put in inverted commas to use hint
3497 AND nvl(wip_supply_type,1)<> 6;
3498
3499
3500
3501
3502 BEGIN
3503
3504 IF PG_DEBUG <> 0 THEN
3505 oe_debug_pub.add('ENTERED Update_BCOLGT_with_match_flag', 5);
3506 END IF;
3507
3508 --added for re-arch
3509 --get match flag for all non-pahtom ato models
3510 lStmtNum :=10;
3511
3512 --as per perf std ahmed almori
3513 --If the global temporary table is referenced in a sub-query in a
3514 --SQL statement which accesses other tables.
3515 --In such cases, the join order may not be optimal due to the lack
3516 --of stats on the temp table, hence hints should be used to ensure the optimal join order.
3517
3518 UPDATE /*+ INDEX (bcol BOM_CTO_ORDER_LINES_GT_N5) */ bom_cto_order_lines_gt bcol
3519 SET bcol.perform_match=
3520 (SELECT config_match
3521 FROM mtl_system_items_b mtl
3522 WHERE mtl.inventory_item_id = bcol.inventory_item_id
3523
3524 AND mtl.organization_id = bcol.validation_org --reuse_revert
3525 --3555026
3526
3527 )
3528 WHERE bcol.bom_item_type = '1'-- used inverted commas to use index
3529 AND nvl(bcol.wip_supply_type,1) <> 6;
3530
3531 IF PG_DEBUG <> 0 THEN
3532 oe_debug_pub.add('Update_BCOLGT_with_match_flag:'||'Sql%row count ='||sql%rowcount,3);
3533 END IF;
3534
3535
3536 --prepare a record structure for input paremeter to
3537 --procedure evaluate_n_pop_match
3538 j := 1;
3539
3540 lStmtNum := 20;
3541 FOR models_match_rec in c_models_match_flag
3542 LOOP
3543 l_match_flag_tab(j).line_id := models_match_rec.line_id;
3544 l_match_flag_tab(j).parent_ato_line_id := models_match_rec.parent_ato_line_id;
3545 l_match_flag_tab(j).ato_line_id := models_match_rec.ato_line_id;
3546 l_match_flag_tab(j).match_flag := models_match_rec.perform_match;
3547
3548 j := j+1 ;
3549 END LOOP;
3550
3551 --call evaluate_n_pop_match_flag proceure
3552 -- to process the match flag
3553 lStmtNum := 30;
3554 Evaluate_N_Pop_Match_Flag
3555 (
3556 p_match_flag_tab => l_match_flag_tab,
3557 x_sparse_tab => x_sparse_match_tab,
3558 x_return_status => x_return_status,
3559 x_msg_count => X_msg_count,
3560 x_msg_data => X_msg_data
3561
3562 );
3563
3564
3565 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
3566 --level1
3567 -- IF PG_DEBUG <> 0 THEN
3568 oe_debug_pub.add('Update_BCOLGT_with_match_flag:'||
3569 'success after Evaluate_N_Pop_Match_Flag', 1);
3570 -- END IF;
3571
3572 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
3573 RAISE fnd_api.g_exc_error;
3574 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3575 RAISE fnd_api.g_exc_unexpected_error;
3576 END IF;
3577
3578 lStmtNum := 40;
3579 CTO_MATCH_CONFIG.xfer_match_flag_to_rec_of_tab
3580 (
3581 p_sparse_tab => x_sparse_match_tab,
3582 x_match_flag_rec => l_match_flag_rec_of_tab,
3583 x_return_status => x_return_status,
3584 x_msg_count => X_msg_count,
3585 x_msg_data => X_msg_data
3586 );
3587
3588
3589 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
3590 --level1
3591 IF PG_DEBUG <> 0 THEN
3592 oe_debug_pub.add('Update_BCOLGT_with_match_flag:'||'success after xfer_match_flag_to_rec_of_tab', 1);
3593 END IF;
3594
3595 ELSIF x_return_status = FND_API.G_RET_STS_ERROR THEN
3596 RAISE fnd_api.g_exc_error;
3597 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3598 RAISE fnd_api.g_exc_unexpected_error;
3599 END IF;
3600
3601 lStmtNum := 50;
3602 FORALL i IN 1..l_match_flag_rec_of_tab.line_id.count
3603 UPDATE bom_cto_order_lines_gt
3604 SET perform_match = l_match_flag_rec_of_tab.match_flag(i)
3605 WHERE line_id = l_match_flag_rec_of_tab.line_id (i);
3606
3607
3608 IF PG_DEBUG <> 0 THEN
3609 oe_debug_pub.add('Update_BCOLGT_with_match_flag:'||'Sql%row count ='||sql%rowcount,3);
3610 END IF;
3611
3612 EXCEPTION
3613 WHEN fnd_api.g_exc_error THEN
3614 IF PG_DEBUG <> 0 THEN
3615 oe_debug_pub.add('Update_BCOLGT_with_match_flag ' || 'Exception in stmt num: '
3616 || to_char(lStmtNum), 1);
3617 END IF;
3618 x_return_status := FND_API.G_RET_STS_ERROR;
3619 -- Get message count and data
3620 cto_msg_pub.count_and_get
3621 ( p_msg_count => x_msg_count
3622 , p_msg_data => x_msg_data
3623 );
3624 WHEN fnd_api.g_exc_unexpected_error THEN
3625 IF PG_DEBUG <> 0 THEN
3626 oe_debug_pub.add('Update_BCOLGT_with_match_flag ' || ' Unexpected Exception in stmt num: '
3627 || to_char(lStmtNum), 1);
3628 END IF;
3629 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3630 -- Get message count and data
3631 cto_msg_pub.count_and_get
3632 ( p_msg_count => x_msg_count
3633 , p_msg_data => x_msg_data
3634 );
3635 WHEN OTHERS then
3636
3637 IF PG_DEBUG <> 0 THEN
3638
3639 oe_debug_pub.add('Update_BCOLGT_with_match_flag' || 'Others Exception in stmt num: '
3640 || to_char(lStmtNum), 1);
3641 oe_debug_pub.add('error '||sqlerrm,1);
3642 END IF;
3643 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3644 -- Get message count and data
3645 cto_msg_pub.count_and_get
3646 ( p_msg_count => x_msg_count
3647 , p_msg_data => x_msg_data
3648 );
3649
3650 END;
3651
3652
3653
3654 end CTO_MATCH_CONFIG;
[Home] [Help]