[Home] [Help]
PACKAGE BODY: APPS.CTO_ITEM_PK
Source
1 package body CTO_ITEM_PK as
2 /* $Header: CTOCCFGB.pls 120.5.12010000.2 2008/08/14 11:34:39 ntungare ship $ */
3
4 /*============================================================================+
5 | Copyright (c) 1993 Oracle Corporation Belmont, California, USA |
6 | All rights reserved. |
7 | Oracle Manufacturing |
8 +=============================================================================+
9 |
10 | FILE NAME : CTOCCFGB.pls
11 | DESCRIPTION : Creates new inventory item for CTO orders. Performs
12 | the same functions as BOMLDCIB.pls and INVPRCIB.pls
13 | for streamlined CTO supported with new OE architecture.
14 |
15 | HISTORY : Created based on BOMLDCIB.pls and INVPRCIB.pls
16 | Created On : 09-JUL-1999 Usha Arora
17 | Modified : 01-JUN-2000 Sajani Sheth
18 | Added code to support Multilevel/Multi-org CTO functionality
19 |
20 | 18-JUN-01 Shashi Bhaskaran
21 | Bugfix 1835357: Comment out all FND_FILE calls
22 | since we are using oe_debug_pub.
23 |
24 | 24-AUG-2001 Sushant Sawant
25 | Bugfix 1957336: Added a new functionality for
26 | preconfigure bom.
27 |
28 | 09-10-2003 Kiran Konada
29 |
30 | bugfix 3070429,3124169
31 | propagation bugfix #: 3143556
32 |
33 | After a call to create item , a new call is added to
34 | CTO_ENI_WRAPPER.CTO_CALL_TO_ENI
35 |
36 | NOTE: CTO_ENI_WRAPPER is maintained in bom Source control and
37 | is owned by ENI team. This is done as part of bugfix 3070429
38 |
39 | Always the main code contains stubbed version and branch has the
40 | a call to file maintained in ENI product top
41 |
42 | Branch is always shipped with ENI
43 |
44 | The above approach was taken as CTO could not directly make a
45 | call to a ENI file. ENI is present from 11.5.4 onwards and
46 | CTO bugfixes can be shipped to all customers since base release
47 | (11.5.2)
48 |
49 | The error messages if any from CTO_CALL_TO_ENI are ignored
50 | decision:Usha Arora,Krishna Bhagvatula,Anuradha subramnian<Kiran Koanda)
51 | As CTO should not error out in its process becuase of failure in inserting
52 | in DBI atbles used for intelligence
53
54 |
55 |
56 | Modified : 18-FEB-2004 Sushant Sawant
57 | Fixed Bug 3441482
58 | Item Creation Code should not continue any further item processing
59 | for full configuration reuse.
60 |
61 |
62 | Modified : 02-MAR-2004 Sushant Sawant
63 | Fixed Bug 3472654
64 | provided check to see whether Config Item is enabled in all organizations
65 | where the model item is enabled for models with CIB = 3 and match = 'Y'.
66 |
67 |
68 | Modified : 02-APR-2004 Sushant Sawant
69 | Fixed Bug 3545019
70 | User created config for type3 model with match off
71 | changed order qty for option item and recreated config with match on
72 | This scenario errors out as bom_cto_src_orgs_b should be cleared
73 | for all partial reuse or no reuse scenarios. Data for type3 configs
74 | is stored in different formats.
75 | The fix will always clear bom_cto_src_orgs_b for partial reuse and no
76 | reuse scenarios to avoid the current issue.
77 |
78 |
79 | Modified : 13-APR-2004 Sushant Sawant
80 | Fixed Bug 3533192
81 | Similar configurations under different models should result in same config item
82 |
83 |
84 | 17-May-2004 Kiran Konada
85 | inserted ship_from_org-id from BCOL into the
86 | validation_org col on BCOL_GT
87 | code has been changed in CTO_REUSE for
88 | 3555026 to look at validation_org, and so
89 | validation-org cannot be null
90 |
91 *============================================================================*/
92
93 PG_DEBUG Number := NVL(FND_PROFILE.value('ONT_DEBUG_LEVEL'), 0);
94
95 /* OSS Items Org list for creating BOM */
96 g_bom_org_list CTO_OSS_SOURCE_PK.bom_org_list_tab ; /* line_id, inventory_item_id, org_id */
97
98 /*
99 procedure perform_match(
100 p_ato_line_id in bom_cto_order_lines.ato_line_id%type ,
101 x_match_found out NOCOPY varchar2,
102 x_matching_config_id out NOCOPY number,
103 x_error_message out NOCOPY VARCHAR2,
104 x_message_name out NOCOPY varchar2
105 );
106 */
107
108
109
110 PROCEDURE evaluate_item_behavior( p_ato_line_id in NUMBER
111 ,x_return_status out NOCOPY varchar2
112 ,x_msg_count out NOCOPY number
113 ,x_msg_data out NOCOPY varchar2 ) ;
114
115
116
117 FUNCTION Create_And_Link_Item(pTopAtoLineId in number,
118 xReturnStatus out NOCOPY varchar2,
119 xMsgCount out NOCOPY number,
120 xMsgData out NOCOPY varchar2,
121 p_mode in varchar2 default 'AUTOCONFIG' )
122 RETURN integer
123 IS
124
125 lSegDel varchar2(3) ;
126 lCiDel varchar2(3) ;
127 lConfigSegName fnd_id_flex_segments.segment_name%type;
128 lStmtNum number ;
129 lStatus varchar2(10) ;
130 lMatchProfile varchar2(10);
131 lOrgId number;
132 xErrorMessage varchar2(240);
133 xMessageName varchar2(240);
134 xTableName varchar2(240);
135 lModelId number;
136 lConfigId number;
137 lModelLineId number;
138 lTopModelLineId number;
139
140
141 v_bcol_data_exists varchar2(1) ;
142 v_config_change varchar2(1) ;
143
144 v_reuse_bcol_count number ;
145
146 cursor c_copy_src_rules IS
147 select bcso.rcv_org_id, bcso.organization_id, bcol.config_creation, bcso.create_src_rules
148 , bcso.model_item_id , bcso.config_item_id
149 from bom_cto_order_lines bcol, bom_cto_src_orgs bcso
150 where bcol.ato_line_id = pTopAtoLineId
151 and bcol.bom_item_type = '1' and nvl( bcol.wip_supply_type , 1 ) <> '6'
152 and bcol.option_specific = 'N'
153 and bcol.line_id = bcso.line_id ; /*Do not copy sourcing assignments for OSS Items*/
154
155 /*
156 and bcso.reference_id is null
157 UNION
158 select bcso.rcv_org_id, bcso.organization_id, bcol.config_creation, bcso.create_src_rules
159 , bcso.inventory_item_id, bcso.config_item_id
160 from bom_cto_order_lines bcol, bom_cto_src_orgs bcso, bom_cto_model_orgs bcmo
161 where bcol.ato_line_id = pTopAtoLineId
162 and bcol.bom_item_type = '1' and nvl( bcol.wip_supply_type , 1 ) <> '6'
163 and bcol.option_specific = 'N'
164 and bcol.line_id = bcso.line_id
165 and bcso.reference_id is not null ;
166 */
167
168
169 x_match_found varchar2(10);
170 x_top_matched_item_id number ;
171 x_error_message varchar2(240);
172 x_message_name varchar2(240);
173
174 v_source_type_code oe_order_lines_all.source_type_code%type ;
175
176 x_return_status varchar2(10) ;
177 x_msg_count number ;
178 x_msg_data varchar2(2000) ;
179
180 v_reuse_config_item_id number ;
181 v_reuse_config_flag varchar2(30) ;
182 v_reuse_config_creation varchar2(30) ;
183
184 v_bcol_ship_from_org_id number ;
185 v_bcolgt_ship_from_org_id number ;
186 v_bcso_data_exists varchar2(1) := 'N'; -- bug fix 5435745
187
188 lReuseProfile number; --Bugfix 6642016
189
190 cursor c_debug is
191 select line_id,
192 inventory_item_id,
193 ship_from_org_id,
194 perform_match,
195 config_item_id,
196 config_creation, plan_level , link_to_line_id
197 from bom_cto_order_lines
198 where top_model_line_id = pTopAtoLineId;
199
200
201 BEGIN
202
203 xReturnStatus := FND_API.G_RET_STS_SUCCESS;
204
205
206 v_bcol_data_exists := 'N' ;
207
208
209 if( p_mode = 'AUTOCONFIG' ) then
210
211
212 delete /*+ INDEX (BOM_CTO_ORDER_LINES_GT BOM_CTO_ORDER_LINES_GT_N1) */
213 from bom_cto_order_lines_gt where ato_line_id = pTopAtoLineId ;
214
215 oe_debug_pub.add( ' Deleted from bom_cto_order_lines_gt ' || SQL%ROWCOUNT , 1 ) ;
216
217
218 begin
219
220 select 'Y' into v_bcol_data_exists
221 from dual
222 where exists ( select line_id from bom_cto_order_lines
223 where line_id = pTopAtoLineId ) ;
224
225
226
227 exception
228 when others then
229
230 v_bcol_data_exists := 'N' ;
231
232 end ;
233
234
235 end if;
236
237
238
239
240
241 --
242 -- populate bom_cto_order_lines
243 -- populating bcol using ato_line_id instead of top_model_line_id
244 -- change to support multiple ATO models under a PTO model
245 --
246
247 lStmtNum := 5;
248 IF PG_DEBUG <> 0 THEN
249 oe_debug_pub.add('Create_And_Link_Item: ' || 'pTopAtoLineId::'||to_char(pTopAtoLineId), 2);
250 END IF;
251
252
253
254
255
256 if( p_mode = 'AUTOCONFIG' ) then
257
258
259 -- delete from bom_cto_order_lines where ato_line_id = pTopAtoLineId ;
260
261 IF PG_DEBUG <> 0 THEN
262 oe_debug_pub.add('Create_And_Link_Item: ' || ' deleted ' || SQL%ROWCOUNT ||
263 ' from bcol ' || to_char(pTopAtoLineId), 2);
264 END IF;
265
266 CTO_UTILITY_PK.Populate_Bcol(
267 p_bcol_line_id => pTopAtoLineId,
268 x_return_status => XReturnStatus,
269 x_msg_count => XMsgCount,
270 x_msg_data => XMsgData,
271 p_reschedule => v_bcol_data_exists ) ;
272 /* p_reschedule parameter should be 'N' for match scenario */
273
274
275
276 if XReturnStatus = FND_API.G_RET_STS_ERROR then
277
278 IF PG_DEBUG <> 0 THEN
279 oe_debug_pub.add ('Create_And_Link_Item: ' ||
280 'Failed in populate_bcol with expected error.', 1);
281 END IF;
282
283 raise FND_API.G_EXC_ERROR;
284
285 elsif XReturnStatus = FND_API.G_RET_STS_UNEXP_ERROR then
286
287 IF PG_DEBUG <> 0 THEN
288 oe_debug_pub.add ('Create_And_Link_Item: ' ||
289 'Failed in populate_bcol with unexpected error.', 1);
290 END IF;
291
292 raise FND_API.G_EXC_UNEXPECTED_ERROR;
293 end if;
294
295 IF PG_DEBUG <> 0 THEN
296 oe_debug_pub.add('Create_And_Link_Item: ' || 'After Populate_Bcol', 5);
297 END IF;
298
299
300
301 /* copy bcol data to bcol_temp */
302
303
304
305 else /* preconfigured items */
306
307
308
309 lStmtNum := 10 ;
310
311
312 oe_debug_pub.add( 'came into PRECONFIG UPDATE BCOL QUERY ' , 1 ) ;
313
314 update bom_cto_order_lines
315 set perform_match = 'Y'
316 where ato_line_id = pTopAtoLineId
317 and inventory_item_id in
318 ( select inventory_item_id
319 from bom_cto_order_lines
320 where ato_line_id = pTopAtoLineId
321 and bom_item_type = '1'
322 and wip_supply_type <> 6
323 and perform_match = 'U'
324 group by inventory_item_id
325 having count(*) > 1
326 );
327
328
329 oe_debug_pub.add( 'PRECONFIG Similar Instance UPDATE BCOL QUERY count ' || SQL%ROWCOUNT , 1 ) ;
330
331
332
333 end if; /* check for autoconfig or preconfig */
334
335
336
337 --
338 --
339 -- Step 2) Call Reuse for current configuration
340 --
341
342
343 oe_debug_pub.add('Create_And_Link_Item: ' || 'REUSE Section ', 5);
344
345
346 lStmtNum := 20 ;
347
348
349 if( p_mode = 'AUTOCONFIG' ) then
350 if ( v_bcol_data_exists = 'Y' ) then
351
352 lReuseProfile := FND_PROFILE.Value('CTO_REUSE_CONFIG'); --Bugfix 6642016
353
354 IF PG_DEBUG <> 0 THEN
355 oe_debug_pub.add('Create_And_Link_Item: ' ||
356 ' Reuse Configuration profile: ' || to_char(lReuseProfile) , 5);
357 END IF; --Bugfix 6642016
358
359 if ( nvl(lReuseProfile,1) = 1 ) then ----Bugfix 6642016
360
361 select count(*) into v_reuse_bcol_count from bom_cto_order_lines
362 where ato_line_id = pTopAtoLineId ;
363
364
365 IF PG_DEBUG <> 0 THEN
366 oe_debug_pub.add('Create_And_Link_Item: ' ||
367 ' calling reuse config' || to_char(v_reuse_bcol_count) , 5);
368 END IF;
369
370 /* call reuse config api */
371 CTO_MATCH_CONFIG.cto_reuse_configuration(
372 p_ato_line_id => pTopAtoLineId
373 ,x_config_change => v_config_change
374 ,x_return_status => XReturnStatus
375 ,x_msg_count => XMsgCount
376 ,x_msg_data => XMsgData);
377
378
379
380
381 IF PG_DEBUG <> 0 THEN
382 oe_debug_pub.add('Create_And_Link_Item: done reuse. ' , 5);
383 END IF;
384
385 end if; --lReuseProfile = 1 Bugfix 6642016
386
387
388 /* Bug 3441482 */
389 begin
390 select /*+ INDEX (BOM_CTO_ORDER_LINES_GT BOM_CTO_ORDER_LINES_GT_U1) */
391 bcol.ship_from_org_id , bcolgt.ship_from_org_id
392 into v_bcol_ship_from_org_id, v_bcolgt_ship_from_org_id
393 from bom_cto_order_lines bcol, bom_cto_order_lines_gt bcolgt
394 where bcol.line_id = bcolgt.line_id and bcol.line_id = pTopAtoLineId ;
395
396 exception
397 when others then
398 v_bcol_ship_from_org_id := -1 ;
399 v_bcolgt_ship_from_org_id := -1 ;
400
401 end ;
402
403
404 IF PG_DEBUG <> 0 THEN
405 oe_debug_pub.add('Create_And_Link_Item: ' ||
406 ' v_bcol_ship_from_org_id : ' || v_bcol_ship_from_org_id ||
407 ' v_bcolgt_ship_from_org_id : ' || v_bcolgt_ship_from_org_id
408 , 5);
409 END IF;
410
411 /* Bug 3441482 */
412 update /*+ INDEX (BOM_CTO_ORDER_LINES_GT BOM_CTO_ORDER_LINES_GT_N1) */
413 bom_cto_order_lines_gt bcolgt set option_specific =
414 ( select option_specific from bom_cto_order_lines bcol
415 where bcolgt.line_id = bcol.line_id )
416 where bcolgt.ato_line_id = pTopAtoLineId ;
417
418
419
420 IF PG_DEBUG <> 0 THEN
421 oe_debug_pub.add('Create_And_Link_Item: done oss flag update for reuse. ' , 5);
422 END IF;
423
424
425 /* delete from bcol */
426
427 delete from bom_cto_order_lines where ato_line_id = pTopAtoLineId ;
428
429 IF PG_DEBUG <> 0 THEN
430 oe_debug_pub.add('Create_And_Link_Item: ' ||
431 ' deleting from bcol ' || to_char(sql%rowcount) , 5);
432 END IF;
433
434 lStmtNum := 30 ;
435
436 /* copy bcol_temp data to bcol */
437
438 insert into bom_cto_order_lines (
439 LINE_ID
440 ,HEADER_ID
441 ,TOP_MODEL_LINE_ID
442 ,LINK_TO_LINE_ID
443 ,ATO_LINE_ID
444 ,PARENT_ATO_LINE_ID
445 ,INVENTORY_ITEM_ID
446 ,SHIP_FROM_ORG_ID
447 ,COMPONENT_SEQUENCE_ID
448 ,COMPONENT_CODE
449 ,ITEM_TYPE_CODE
450 ,SCHEDULE_SHIP_DATE
451 ,PLAN_LEVEL
452 ,PERFORM_MATCH
453 ,CONFIG_ITEM_ID
454 ,BOM_ITEM_TYPE
455 ,WIP_SUPPLY_TYPE
456 ,ORDERED_QUANTITY
457 ,ORDER_QUANTITY_UOM
458 ,BATCH_ID
459 ,CREATION_DATE
460 ,CREATED_BY
461 ,LAST_UPDATE_DATE
462 ,LAST_UPDATED_BY
463 ,LAST_UPDATE_LOGIN
464 ,PROGRAM_APPLICATION_ID
465 ,PROGRAM_ID
466 ,PROGRAM_UPDATE_DATE
467 ,REUSE_CONFIG
468 ,OPTION_SPECIFIC
469 ,QTY_PER_PARENT_MODEL
470 ,CONFIG_CREATION)
471 select /*+ INDEX (BOM_CTO_ORDER_LINES_GT BOM_CTO_ORDER_LINES_GT_N1) */
472 LINE_ID
473 ,HEADER_ID
474 ,TOP_MODEL_LINE_ID
475 ,LINK_TO_LINE_ID
476 ,ATO_LINE_ID
477 ,PARENT_ATO_LINE_ID
478 ,INVENTORY_ITEM_ID
479 ,SHIP_FROM_ORG_ID
480 ,COMPONENT_SEQUENCE_ID
481 ,COMPONENT_CODE
482 ,ITEM_TYPE_CODE
483 ,SCHEDULE_SHIP_DATE
484 ,PLAN_LEVEL
485 ,PERFORM_MATCH
486 ,CONFIG_ITEM_ID
487 ,BOM_ITEM_TYPE
488 ,WIP_SUPPLY_TYPE
489 ,ORDERED_QUANTITY
490 ,ORDER_QUANTITY_UOM
491 ,BATCH_ID
492 ,CREATION_DATE
493 ,CREATED_BY
494 ,LAST_UPDATE_DATE
495 ,LAST_UPDATED_BY
496 ,LAST_UPDATE_LOGIN
497 ,PROGRAM_APPLICATION_ID
498 ,PROGRAM_ID
499 ,PROGRAM_UPDATE_DATE
500 ,REUSE_CONFIG
501 ,nvl( option_specific, 'N' )
502 ,QTY_PER_PARENT_MODEL
503 ,CONFIG_CREATION
504 from bom_cto_order_lines_gt
505 where ato_line_id = pTopAtoLineId ;
506 /* add ods and reuse flag */
507
508 IF PG_DEBUG <> 0 THEN
509 oe_debug_pub.add('Create_And_Link_Item: ' || SQL%ROWCOUNT ||
510 ' copied from bcol_gt to bcol ', 5);
511 END IF;
512
513
514
515 else
516
517
518 lStmtNum := 40 ;
519
520 oe_debug_pub.add('Create_And_Link_Item: ' || 'copy bcol to bcol_gt ', 5);
521 /* copy bcol data to bcol_temp for matching */
522
523 insert into bom_cto_order_lines_gt (
524 LINE_ID
525 ,HEADER_ID
526 ,TOP_MODEL_LINE_ID
527 ,LINK_TO_LINE_ID
528 ,ATO_LINE_ID
529 ,PARENT_ATO_LINE_ID
530 ,INVENTORY_ITEM_ID
531 ,SHIP_FROM_ORG_ID
532 ,COMPONENT_SEQUENCE_ID
533 ,COMPONENT_CODE
534 ,ITEM_TYPE_CODE
535 ,SCHEDULE_SHIP_DATE
536 ,PLAN_LEVEL
537 ,PERFORM_MATCH
538 ,CONFIG_ITEM_ID
539 ,BOM_ITEM_TYPE
540 ,WIP_SUPPLY_TYPE
541 ,ORDERED_QUANTITY
542 ,ORDER_QUANTITY_UOM
543 ,BATCH_ID
544 ,CREATION_DATE
545 ,CREATED_BY
546 ,LAST_UPDATE_DATE
547 ,LAST_UPDATED_BY
548 ,LAST_UPDATE_LOGIN
549 ,PROGRAM_APPLICATION_ID
550 ,PROGRAM_ID
551 ,PROGRAM_UPDATE_DATE
552 ,REUSE_CONFIG
553 ,OPTION_SPECIFIC
554 ,QTY_PER_PARENT_MODEL
555 ,CONFIG_CREATION
556 ,VALIDATION_ORG)
557 select
558 LINE_ID
559 ,HEADER_ID
560 ,TOP_MODEL_LINE_ID
561 ,LINK_TO_LINE_ID
562 ,ATO_LINE_ID
563 ,PARENT_ATO_LINE_ID
564 ,INVENTORY_ITEM_ID
565 ,SHIP_FROM_ORG_ID
566 ,COMPONENT_SEQUENCE_ID
567 ,COMPONENT_CODE
568 ,ITEM_TYPE_CODE
569 ,SCHEDULE_SHIP_DATE
570 ,PLAN_LEVEL
571 ,PERFORM_MATCH
572 ,CONFIG_ITEM_ID
573 ,BOM_ITEM_TYPE
574 ,WIP_SUPPLY_TYPE
575 ,ORDERED_QUANTITY
576 ,ORDER_QUANTITY_UOM
577 ,BATCH_ID
578 ,CREATION_DATE
579 ,CREATED_BY
580 ,LAST_UPDATE_DATE
581 ,LAST_UPDATED_BY
582 ,LAST_UPDATE_LOGIN
583 ,PROGRAM_APPLICATION_ID
584 ,PROGRAM_ID
585 ,PROGRAM_UPDATE_DATE
586 ,REUSE_CONFIG
587 ,OPTION_SPECIFIC
588 ,QTY_PER_PARENT_MODEL
589 ,CONFIG_CREATION
590 ,SHIP_FROM_ORG_ID --for bugfix3555026
591 from bom_cto_order_lines
592 where ato_line_id = pTopAtoLineId ;
593
594 oe_debug_pub.add('Create_And_Link_Item: ' || ' copied bcol to bcol gt rows ' || SQL%ROWCOUNT , 5);
595 end if ; /* bcol data exists */
596 end if ; /* p_mode = AUTOCONFIG */
597
598
599 IF PG_DEBUG <> 0 THEN
600 oe_debug_pub.add('Create_And_Link_Item: ' ||
601 ' querying Reuse Flag ' , 2);
602 END IF;
603
604 lStmtNum := 50 ;
605
606
607 begin
608 select reuse_config, config_item_id , config_creation
609 into v_reuse_config_flag, v_reuse_config_item_id , v_reuse_config_creation
610 from bom_cto_order_lines
611 where line_id = pTopAtoLineId ;
612
613 IF PG_DEBUG <> 0 THEN
614 oe_debug_pub.add('Create_And_Link_Item: ' ||
615 'Reuse item ' || v_reuse_config_item_id ||
616 ' Reuse Flag ' || v_reuse_config_flag ||
617 ' Config Creation ' || v_reuse_config_creation
618 , 2);
619 END IF;
620
621
622 Exception
623 when others then
624
625 raise ;
626
627 end ;
628
629
630
631
632
633
634
635 if( p_mode = 'AUTOCONFIG' ) then /* validate_oe_data code to be execute only for auto create config bug 4341156 */
636
637
638 -- begin bugfix 4044709: New procedure validate_oe_data to validate
639 -- 1) BCOL(count) = OE (count ) for specific ato_line_id in question
640 -- 2) Line_id's in OE and BCOL matches for specific ato_line_id in question
641
642
643 IF PG_DEBUG <> 0 THEN
644 oe_debug_pub.add('create_and_link_item: ' || 'going to call validate_oe_data ', 1 );
645 END IF;
646
647 CTO_UTILITY_PK.validate_oe_data(p_bcol_line_id => pTopAtoLineId,
648 x_return_status => xReturnStatus);
649
650
651 if xReturnStatus <> FND_API.G_RET_STS_SUCCESS THEN
652 oe_debug_pub.add('create_and_link_item: ' || 'Error in OE BCOL Validation',5);
653 raise FND_API.G_EXC_UNEXPECTED_ERROR;
654 end if;
655
656
657 -- end bugfix 4044709: New procedure validate_oe_data to validate
658
659 end if; /* This check should be done only for Auto create configurations*/
660
661
662
663
664
665
666
667 oe_debug_pub.add('create_and_link_item: BCOL DATA ' || ' line_id ' ||
668 ' inventory_item_id ' || ' ship_org ' || ' match ' ||
669 ' config item ' || ' CIB ' || ' level ' || ' link ' , 1 ) ;
670 FOR v_debug IN c_debug LOOP
671 IF PG_DEBUG <> 0 THEN
672 oe_debug_pub.add('create_and_link_item: ' || to_char(v_debug.line_id)||' '||
673 to_char(v_debug.inventory_item_id)||' '||
674 nvl(to_char(v_debug.ship_from_org_id),null)||' '||
675 to_char(v_debug.perform_match)||' '||
676 nvl(v_debug.config_item_id, null)||' '||
677 nvl(v_debug.config_creation, null) || ' ' ||
678 nvl(v_debug.plan_level, null) || ' ' ||
679 nvl(v_debug.link_to_line_id, null)
680 , 2);
681 END IF;
682 END LOOP;
683
684
685 /* NO Item processing required for Reused Configurations */
686 /* No processing is required for full reuse of type 3 or full reuse of type 1,2 with no warehouse change */
687
688 /* bug 5435745: Check whether bcso data exists for reuse cases. It will not exists in case
689 of split config line. bcso needs to be populated in such cases.
690 */
691
692 begin
693 select 'Y' into v_bcso_data_exists
694 from bom_cto_src_orgs
695 where top_model_line_id = pTopAtoLineId
696 and rownum = 1; -- Bug Fix 5532777
697 exception
698 when no_data_found then
699 v_bcso_data_exists := 'N';
700 end;
701
702 /* Bug 3441482 */
703 -- bug 5380678: added condn on v_bcso_data_exists
704
705 if ( v_reuse_config_item_id is not null and v_reuse_config_flag = 'Y' and v_bcso_data_exists = 'Y') then
706
707
708 IF PG_DEBUG <> 0 THEN
709 oe_debug_pub.add('Create_And_Link_Item: ' ||
710 'Will Not Perform Any Item processing as it is reuse case ', 2);
711 END IF;
712
713
714
715 else
716
717
718
719
720 /* Fix for bug 3545019 and partial reuse scenarios */
721
722 delete from bom_cto_src_orgs_b where top_model_line_id = pTopAtoLineId ;
723
724 IF PG_DEBUG <> 0 THEN
725 oe_debug_pub.add('Create_And_Link_Item: ' ||
726 ' deleted from bcso_b as reuse is not applicable or doesnt exist '
727 || to_char(sql%rowcount) , 5);
728 END IF;
729
730
731
732
733
734 --
735 --
736 -- Step 3) Call Match for current configuration
737 --
738
739 oe_debug_pub.add('Create_And_Link_Item: ' || 'Match section ', 5);
740
741
742 lStmtNum := 50 ;
743
744 lMatchProfile := FND_PROFILE.Value('BOM:MATCH_CONFIG');
745
746 oe_debug_pub.add('Create_And_Link_Item: ' || ' Done Match section ', 5);
747 oe_debug_pub.add('Create_And_Link_Item: ' || ' Done Match section ' || lMatchProfile , 5);
748
749 if( lMatchProfile = 1 and p_mode = 'AUTOCONFIG' ) then
750
751
752 oe_debug_pub.add( 'CREATE_AND_LINK_ITEM ' || ' going to call CTO_MATCH_CONFIG perform_match ' , 1 ) ;
753
754
755 CTO_MATCH_CONFIG.perform_match( pTopAtoLineId ,
756 x_return_status ,
757 x_msg_count,
758 x_msg_data
759 ) ;
760
761 oe_debug_pub.add( 'CREATE_AND_LINK_ITEM ' || ' done perform_match ' , 1 ) ;
762
763
764
765 lStmtNum := 55 ;
766
767 select /*+ INDEX (BOM_CTO_ORDER_LINES_GT BOM_CTO_ORDER_LINES_GT_U1) */
768 perform_match , config_item_id into x_match_found , x_top_matched_item_id
769 from bom_cto_order_lines_gt
770 where line_id = pTopAtoLineId ;
771
772
773 oe_debug_pub.add( 'CREATE_AND_LINK_ITEM:: perform match data ' ||
774 'x_match_found ' || x_match_found ||
775 'x_top_matched_item_id ' || to_char( x_top_matched_item_id )
776 , 1 ) ;
777
778
779 if( x_match_found = 'Y' ) then
780 oe_debug_pub.add( 'CREATE_AND_LINK_ITEM ' || 'Top Model Match Success ' , 1 ) ;
781 oe_debug_pub.add( 'CREATE_AND_LINK_ITEM ' || 'Top Match '|| to_char( x_top_matched_item_id ) , 1 ) ;
782
783 null ;
784
785 end if ;
786
787
788 end if ; /* check for match profile */
789
790
791
792 lStmtNum := 60 ;
793 oe_debug_pub.add('Create_And_Link_Item: ' || ' Going to Synch up BCOL with data from BCOL_GT for matched info ' , 5);
794
795
796
797 update bom_cto_order_lines bcol
798 set ( bcol.perform_match, bcol.config_item_id ) =
799 ( select /*+ INDEX (BOM_CTO_ORDER_LINES_GT BOM_CTO_ORDER_LINES_GT_U1) */
800 bcol_gt.perform_match, bcol_gt.config_item_id
801 from bom_cto_order_lines_gt bcol_gt
802 where bcol.line_id = bcol_gt.line_id )
803 where bcol.ato_line_id = pTopAtoLineId ;
804
805
806 oe_debug_pub.add('Create_And_Link_Item: ' || ' Synch up BCOL with data from BCOL_GT for matched info rows ' || SQL%ROWCOUNT , 5);
807
808
809 --
810 --
811 -- Step 4) Call OSS Processing API to identify OSS Models
812 --
813
814
815 lStmtNum := 70 ;
816
817
818 CTO_OSS_SOURCE_PK.PROCESS_OSS_CONFIGURATIONS( p_ato_line_id => pTopAtoLineId
819 ,x_return_status => XReturnStatus
820 ,x_msg_count => XMsgCount
821 ,x_msg_data => XMsgData);
822
823
824
825 IF (XReturnStatus = FND_API.G_RET_STS_ERROR) THEN
826 IF PG_DEBUG <> 0 THEN
827 oe_debug_pub.add('Create_And_Link_Item: ' || 'process oss configurations exp error',1);
828 END IF;
829 raise FND_API.G_EXC_ERROR;
830
831 ELSIF (XReturnStatus = FND_API.G_RET_STS_UNEXP_ERROR) THEN
832 IF PG_DEBUG <> 0 THEN
833 oe_debug_pub.add('Create_And_Link_Item: ' || 'process_oss_configurations returned with unexp error',1);
834 END IF;
835 raise FND_API.G_EXC_UNEXPECTED_ERROR;
836
837 END IF;
838
839 IF PG_DEBUG <> 0 THEN
840 oe_debug_pub.add('Create_And_Link_Item: ' || 'After process_oss_configurations ', 2);
841 END IF;
842
843
844
845
846
847
848
849 --
850 --
851 --
852 --
853 -- Call OSS Processing API to identify OSS Models
854 --
855
856
857
858
859 --
860 -- Step 5) populate bom_cto_src_orgs
861 --
862
863 lStmtNum := 80 ;
864
865
866
867
868 IF PG_DEBUG <> 0 THEN
869 oe_debug_pub.add('Create_And_Link_Item: ' || 'Before CTO_MSUTIL_PUB.Populate_Src_Orgs', 2);
870 END IF;
871 lStatus := CTO_MSUTIL_PUB.Populate_Src_Orgs(
872 pTopAtoLineId => pTopAtoLineId,
873 x_return_status => XReturnStatus,
874 x_msg_count => XMsgCount,
875 x_msg_data => XMsgData);
876
877 IF (lStatus <> 1) AND (XReturnStatus = FND_API.G_RET_STS_ERROR) THEN
878 IF PG_DEBUG <> 0 THEN
879 oe_debug_pub.add('Create_And_Link_Item: ' || 'Populate_src_orgs returned with exp error',1);
880 END IF;
881 raise FND_API.G_EXC_ERROR;
882
883 ELSIF (lStatus <> 1) AND (XReturnStatus = FND_API.G_RET_STS_UNEXP_ERROR) THEN
884 IF PG_DEBUG <> 0 THEN
885 oe_debug_pub.add('Create_And_Link_Item: ' || 'Populate_src_orgs returned with unexp error',1);
886 END IF;
887 raise FND_API.G_EXC_UNEXPECTED_ERROR;
888
889 END IF;
890
891 IF PG_DEBUG <> 0 THEN
892 oe_debug_pub.add('Create_And_Link_Item: ' || 'After Populate_Src_Orgs', 2);
893 END IF;
894
895
896
897
898
899
900 --
901 -- Check delimiter to ensure it is a length of one
902 -- and that it is not the same as the item delimiter value.
903 --
904
905 lStmtNum := 90 ;
906 lCiDel := FND_PROFILE.Value('BOM:CONFIG_ITEM_DELIMITER');
907
908 if (lCiDel = ' ') then
909 lCiDel := '';
910 end if;
911
912 if (length(lCiDel )<> 1 ) then
913 IF PG_DEBUG <> 0 THEN
914 oe_debug_pub.add ('Create_And_Link_Item: ' || 'Error: Length of delimiter <> 1', 1);
915 END IF;
916 cto_msg_pub.cto_message('BOM','CTO_DELIMITER_ERROR');
917 raise FND_API.G_EXC_ERROR;
918 end if;
919 IF PG_DEBUG <> 0 THEN
920 oe_debug_pub.add('Create_And_Link_Item: ' || 'Delimiter is : ' || lCiDel,2);
921 END IF;
922
923
924 --
925 -- Get the item FF delimiter value
926 --
927
928 lStmtNum := 100 ;
929 select concatenated_segment_delimiter
930 into lSegDel
931 from fnd_id_flex_structures
932 where application_id = 401
933 and id_flex_code = 'MSTK'
934 and id_flex_num = 101;
935
936 IF PG_DEBUG <> 0 THEN
937 oe_debug_pub.add('Create_And_Link_Item: ' || 'Items FF segment Separator is : ' || lSegDel,2);
938 END IF;
939
940 if ( lSegDel = lCiDel ) then
941 IF PG_DEBUG <> 0 THEN
942 oe_debug_pub.add ('Create_And_Link_Item: ' || 'Error : Config Item delimiter = System Items FF segment separator. Not a valid setup.', 1);
943
944 oe_debug_pub.add ('Create_And_Link_Item: ' || 'Please set a different value for profile BOM:Configuration Item Delimiter.',1);
945 END IF;
946 cto_msg_pub.cto_message('BOM','CTO_DELIMITER_ERROR');
947 raise FND_API.G_EXC_ERROR;
948 end if;
949
950
951
952
953
954
955 -- Perform Match is set to NO
956
957 --
958 -- call create_all_items
959 --
960
961 lStmtNum := 110 ;
962 IF PG_DEBUG <> 0 THEN
963 oe_debug_pub.add('Create_And_Link_Item: ' || 'SRS Calling Create_All_Items', 2);
964 END IF;
965
966 lStatus := Create_All_Items(
967 pTopAtoLineId,
968 xReturnStatus,
969 XMsgCount,
970 XMsgData,
971 p_mode);
972 IF lStatus <> 1 then
973 IF PG_DEBUG <> 0 THEN
974 oe_debug_pub.add ('Create_And_Link_Item: ' || 'Create_All_Items returned with 0', 1);
975 END IF;
976 --cto_msg_pub.cto_message('BOM','CTO_CREATE_ITEM_ERROR');
977 raise FND_API.G_EXC_ERROR;
978 end if;
979
980
981
982
983
984 oe_debug_pub.add ('Create_And_Link_Item: ' || 'calling oss processing ', 1);
985
986
987
988
989
990
991 /* Call OSS Rules processing API */
992
993
994 lStmtNum := 120 ;
995
996 CTO_OSS_SOURCE_PK.create_oss_sourcing_rules( p_ato_line_id => pTopAtoLineId,
997 x_return_status => xReturnStatus,
998 x_msg_count => XMsgCount,
999 x_msg_data => XMsgData ) ;
1000
1001
1002
1003 --
1004 -- create sourcing rules if necessary
1005 --
1006
1007 lStmtNum := 130 ;
1008
1009 FOR v_src_rule IN c_copy_src_rules LOOP
1010 --
1011 -- call API to copy sourcing rules from model item
1012 -- to config item
1013 --
1014
1015 lStmtNum:= 110;
1016
1017 oe_debug_pub.add ('Create_Item: ' || ' c_copy_src_rules LOOP ' || v_src_rule.config_creation , 1 );
1018
1019
1020 if( v_src_rule.create_src_rules = 'Y' and v_src_rule.config_creation in ( 1, 2) ) then
1021
1022
1023
1024 IF PG_DEBUG <> 0 THEN
1025 oe_debug_pub.add ('Create_Item: ' || 'Copying src rule for cfg item '
1026 ||to_char(v_src_rule.config_item_id)||' in org '||
1027 to_char(v_src_rule.organization_id), 2);
1028 END IF;
1029
1030
1031 lStmtNum := 140 ;
1032
1033 CTO_MSUTIL_PUB.Create_Sourcing_Rules(
1034 pModelItemId => v_src_rule.model_item_id,
1035 pConfigId => v_src_rule.config_item_id,
1036 pRcvOrgId => v_src_rule.rcv_org_id,
1037 x_return_status => lStatus,
1038 x_msg_count => xMsgCount,
1039 x_msg_data => xMsgData);
1040
1041 IF (lStatus = fnd_api.G_RET_STS_ERROR) THEN
1042 IF PG_DEBUG <> 0 THEN
1043 oe_debug_pub.add ('Create_Item: ' ||
1044 'Create_Sourcing_Rules returned with expected error.');
1045 END IF;
1046 raise FND_API.G_EXC_ERROR;
1047
1048 ELSIF (lStatus = fnd_api.G_RET_STS_UNEXP_ERROR) THEN
1049 IF PG_DEBUG <> 0 THEN
1050 oe_debug_pub.add ('Create_Item: ' ||
1051 'Create_Sourcing_Rules returned with unexp error.');
1052 END IF;
1053 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1054
1055 END IF;
1056
1057 elsif( v_src_rule.config_creation = 3 ) then
1058
1059 IF PG_DEBUG <> 0 THEN
1060 oe_debug_pub.add ('Create_Item: ' || 'Copying src rule for cfg item '
1061 ||to_char(v_src_rule.config_item_id)||' in org '||
1062 to_char(v_src_rule.organization_id), 2);
1063 END IF;
1064
1065 lStmtNum := 150 ;
1066
1067 CTO_MSUTIL_PUB.Create_TYPE3_Sourcing_Rules(
1068 pModelItemId => v_src_rule.model_item_id,
1069 pConfigId => v_src_rule.config_item_id,
1070 pRcvOrgId => v_src_rule.organization_id,
1071 x_return_status => lStatus,
1072 x_msg_count => xMsgCount,
1073 x_msg_data => xMsgData);
1074
1075 oe_debug_pub.add ('Create_Item: type3 sourcing rules done ' , 1) ;
1076
1077 IF (lStatus = fnd_api.G_RET_STS_ERROR) THEN
1078 IF PG_DEBUG <> 0 THEN
1079 oe_debug_pub.add ('Create_Item: ' ||
1080 'Create_Sourcing_Rules returned with expected error.');
1081 END IF;
1082 raise FND_API.G_EXC_ERROR;
1083
1084 ELSIF (lStatus = fnd_api.G_RET_STS_UNEXP_ERROR) THEN
1085 IF PG_DEBUG <> 0 THEN
1086 oe_debug_pub.add ('Create_Item: ' ||
1087 'Create_Sourcing_Rules returned with unexp error.');
1088 END IF;
1089 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1090
1091 END IF;
1092
1093
1094
1095
1096 end if;
1097
1098 oe_debug_pub.add ('Create_Item: next iteration ' , 1) ;
1099
1100 END LOOP;
1101
1102
1103
1104
1105 end if; /* Check for Reuse Flag */
1106
1107
1108 IF PG_DEBUG <> 0 THEN
1109 oe_debug_pub.add ('Create_And_Link_Item: ' ||
1110 'Success in Item Creation function', 1);
1111 END IF;
1112
1113 return(1);
1114
1115 EXCEPTION
1116 when NO_DATA_FOUND then
1117 IF PG_DEBUG <> 0 THEN
1118 oe_debug_pub.add('Create_And_Link_Item: ' || 'create_and_link_item::ndf::lStmtNum::'||to_char(lStmtNum)||'::'||sqlerrm, 1);
1119 END IF;
1120 cto_msg_pub.count_and_get
1121 ( p_msg_count => xMsgCount
1122 , p_msg_data => xMsgData
1123 );
1124 xReturnStatus := FND_API.G_RET_STS_UNEXP_ERROR;
1125 return(0);
1126
1127 when FND_API.G_EXC_ERROR then
1128 IF PG_DEBUG <> 0 THEN
1129 oe_debug_pub.add('Create_And_Link_Item: ' || 'create_and_link_item::exp error in stmt '||to_char(lStmtNum), 1);
1130 END IF;
1131 cto_msg_pub.count_and_get
1132 ( p_msg_count => xMsgCount
1133 , p_msg_data => xMsgData
1134 );
1135 xReturnStatus := FND_API.G_RET_STS_ERROR;
1136 return(0);
1137
1138 when FND_API.G_EXC_UNEXPECTED_ERROR then
1139 IF PG_DEBUG <> 0 THEN
1140 oe_debug_pub.add('Create_And_Link_Item: ' || 'create_and_link_item::unexp error in stmt '||to_char(lStmtNum)||'::'||sqlerrm, 1);
1141 END IF;
1142 cto_msg_pub.count_and_get
1143 ( p_msg_count => xMsgCount
1144 , p_msg_data => xMsgData
1145 );
1146 xReturnStatus := FND_API.G_RET_STS_UNEXP_ERROR;
1147 return(0);
1148
1149 when OTHERS then
1150 IF PG_DEBUG <> 0 THEN
1151 oe_debug_pub.add('Create_And_Link_Item: ' || 'create_and_link_item::others::'||to_char(lStmtNum)||'::'||sqlerrm, 1);
1152 END IF;
1153 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1154 FND_MSG_PUB.Add_Exc_Msg
1155 ('CTO_ITEM_PK'
1156 ,'create_and_link_item'
1157 );
1158 END IF;
1159 cto_msg_pub.count_and_get
1160 ( p_msg_count => xMsgCount
1161 , p_msg_data => xMsgData
1162 );
1163 xReturnStatus := FND_API.G_RET_STS_UNEXP_ERROR;
1164 return(0);
1165
1166 END Create_And_Link_Item;
1167
1168
1169
1170 FUNCTION Create_All_Items(
1171 pTopAtoLineId in number,
1172 xReturnStatus out NOCOPY varchar2,
1173 xMsgCount out NOCOPY number,
1174 xMsgData out NOCOPY varchar2,
1175 p_mode in varchar2 default 'AUTOCONFIG')
1176 RETURN integer
1177 IS
1178
1179 lStmtNum number ;
1180 lStatus number;
1181 xConfigId number;
1182 xErrorMessage varchar2(100);
1183 xMessageName varchar2(100);
1184 xTableName varchar2(100);
1185
1186 cursor c_model_lines is
1187 select line_id,
1188 inventory_item_id,
1189 config_item_id,
1190 parent_ato_line_id,
1191 config_creation
1192 from bom_cto_order_lines
1193 where ato_line_id = pTopAtoLineId
1194 and ( bom_item_type = '1' )
1195 and nvl(wip_supply_type,0) <> '6'
1196 order by plan_level desc; -- added order by clause for wt/vol project
1197
1198
1199 v_line_exists number ;
1200 lXConfigId number;
1201 l_x_error_msg varchar2(100);
1202 l_x_msg_name varchar2(30);
1203 l_x_table_name varchar2(30);
1204 v_perform_match varchar2(1) ;
1205 v_parent_ato_line_id number ;
1206 v_ato_line_id number ;
1207
1208 lUserId Number;
1209 lLoginId Number;
1210
1211 v_match_found boolean := false ;
1212
1213 --start bugfix 3070429,3124169
1214 l_eni_star_record CTO_ENI_WRAPPER.STAR_REC_TYPE;
1215 eni_return_status VARCHAR2(1);
1216 --end bugfix 3070429,3124169
1217 v_update_count number ;
1218
1219 v_bcso_group_reference_id number ;
1220
1221
1222 v_bcmo_config_orgs bom_cto_order_lines.config_creation%type ;
1223
1224 v_model_item_status number ;
1225 v_config_item_status number ;
1226 l_token CTO_MSG_PUB.token_tbl;
1227
1228 v_model_item_name varchar2(2000) ;
1229 v_config_item_name varchar2(2000) ;
1230 l_lock_status number; -- bugfix 4227993
1231 --
1232 -- bug 7203643
1233 -- changed the hash value variable type to varchar2
1234 -- ntungare
1235 --
1236 --l_hash_value number; -- bugfix 4227993
1237 l_hash_value varchar2(2000);
1238
1239 BEGIN
1240
1241
1242 oe_debug_pub.add ('Create_All_items: ' || 'Entered ', 1);
1243
1244 xReturnStatus := FND_API.G_RET_STS_SUCCESS;
1245 lUserId := nvl(Fnd_Global.USER_ID, -1) ;
1246 lLoginId := nvl(Fnd_Global.LOGIN_ID, -1);
1247
1248
1249
1250
1251
1252
1253 oe_debug_pub.add ('Create_All_items: ' || 'Entered 1 ', 1);
1254
1255
1256
1257
1258
1259
1260
1261 --
1262 -- For each identified model line, call create_item to
1263 -- create config items in all required orgs
1264 --
1265
1266 lStmtNum := 30;
1267
1268
1269 FOR v_model_lines IN c_model_lines
1270 LOOP
1271
1272 oe_debug_pub.add ('Create_All_items: ' || 'Entered 2 ', 1);
1273 v_match_found := FALSE ;
1274
1275
1276
1277 IF PG_DEBUG <> 0 THEN
1278 oe_debug_pub.add('Create_All_Items: ' || 'loop::'||
1279 to_char(v_model_lines.line_id)||'::'||
1280 to_char(v_model_lines.inventory_item_id), 2);
1281 END IF;
1282
1283 --
1284 -- create this config item in all required orgs
1285 --
1286
1287 xConfigId := v_model_lines.config_item_id;
1288 IF PG_DEBUG <> 0 THEN
1289 oe_debug_pub.add('Create_All_Items: ' ||
1290 'Before calling create_item::config_id is::'||to_char(xConfigId), 2);
1291 END IF;
1292
1293
1294
1295 oe_debug_pub.add ('Create_All_items: ' || 'Entered 3' , 1);
1296
1297
1298 /* check for perform match flag */
1299
1300 select perform_match
1301 into v_perform_match
1302 from bom_cto_order_lines
1303 where line_id = v_model_lines.line_id;
1304
1305
1306 oe_debug_pub.add ('Create_All_items: ' || 'perform_match ' || v_perform_match , 1);
1307
1308 lXConfigId := v_model_lines.config_item_id ;
1309
1310 if( v_perform_match in ( 'Y' , 'C' ) and lXConfigId is null ) then /* Reattempt Match for preconfigured Scenario */
1311
1312 /* call check config match API */
1313
1314 --
1315 -- Begin Bugfix 4227993
1316 -- Acquire user-lock by calling lock_for_match so that the process does not end up
1317 -- creating new configs if a non-commited match exists.
1318 -- Incase lock is not acquired, wait indefinitely. We could error out but we decided
1319 -- to wait so that user does not have to resubmit the process again.
1320 --
1321 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Calling cto_utility_pk.lock_for_match: start time: ' ||
1322 to_char(sysdate , 'MM/DD/YYYY HH24:MI:SS'));
1323 CTO_UTILITY_PK.lock_for_match(
1324 x_return_status => xReturnStatus,
1325 xMsgCount => xMsgCount,
1326 xMsgData => xMsgData,
1327 x_lock_status => l_lock_status,
1328 x_hash_value => l_hash_value,
1329 p_line_id => v_model_lines.line_id );
1330
1331 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Calling cto_utility_pk.lock_for_match: end time: ' ||
1332 to_char(sysdate , 'MM/DD/YYYY HH24:MI:SS'));
1333
1334 if xReturnStatus <> FND_API.G_RET_STS_SUCCESS then
1335 oe_debug_pub.add('match_and_create_all_items: '|| 'get_user_lock returned error');
1336 raise fnd_api.g_exc_unexpected_error;
1337 end if;
1338
1339 --check for error cases
1340 if ( l_lock_status <> 0 ) THEN
1341 if (l_lock_status = 1) then -- timeout
1342 oe_debug_pub.add('l_lock_status = 1: TIMEOUT ');
1343 cto_msg_pub.cto_message('BOM','CTO_LOCK_TIMEOUT');
1344 raise fnd_api.g_exc_unexpected_error;
1345
1346 elsif (l_lock_status = 2) then -- deadlock
1347 oe_debug_pub.add('l_lock_status = 2: DEADLOCK ');
1348 cto_msg_pub.cto_message('BOM','CTO_LOCK_DEADLOCK');
1349 raise fnd_api.g_exc_unexpected_error;
1350
1351 elsif (l_lock_status = 3) then -- parameter error
1352 oe_debug_pub.add('l_lock_status = 3: PARAMETER ERROR ');
1353 cto_msg_pub.cto_message('BOM','CTO_LOCK_PARAM_ERROR');
1354 raise fnd_api.g_exc_unexpected_error;
1355
1356 elsif (l_lock_status = 4) then -- already locked.
1357 oe_debug_pub.add('l_lock_status = 4: ALREADY LOCKED ERROR ');
1358 cto_msg_pub.cto_message('BOM','CTO_LOCK_ALREADY_LOCKED');
1359 -- we shall not raise an error if we are already holding the lock.
1360
1361 else -- internal error - not fault of user
1362 oe_debug_pub.add('l_lock_status = '||l_lock_status||': INTERNAL ERROR ');
1363 cto_msg_pub.cto_message('BOM','CTO_LOCK_ERROR');
1364 raise fnd_api.g_exc_unexpected_error;
1365 end if;
1366 else
1367 IF PG_DEBUG <> 0 THEN
1368 oe_debug_pub.add('match_and_create_all_items: ' || 'Successfully obtained lock for match.');
1369 END IF;
1370
1371 end if;
1372 --
1373 -- End Bugfix 4227993
1374 --
1375
1376
1377
1378 if( v_perform_match = 'Y' ) then
1379 lStatus := CTO_MATCH_CONFIG.check_config_match(
1380 p_model_line_id => v_model_lines.line_id,
1381 x_config_match_id => lXConfigId,
1382 x_error_message => l_x_error_msg,
1383 x_message_name => l_x_msg_name);
1384
1385 else /* custom match */
1386 lStatus := CTO_CUSTOM_MATCH_PK.find_matching_config(
1387 pModelLineId => v_model_lines.line_id,
1388 xMatchedItemId => lXConfigId,
1389 xErrorMessage => l_x_error_msg,
1390 xMessageName => l_x_msg_name,
1391 xTableName => l_x_table_name);
1392
1393
1394
1395 end if ;
1396 -- bug 5859780 : need to handle error from match function.
1397 if lStatus <> 1 then
1398 oe_debug_pub.add('match_and_create_all_items: v_perform_match = '||v_perform_match);
1399 oe_debug_pub.add('match_and_create_all_items: '|| 'match returned error: '||l_x_error_msg);
1400 raise fnd_api.g_exc_error;
1401 end if;
1402 -- end bug 5859780
1403
1404
1405
1406 if( lXConfigId is not null ) then
1407 v_match_found := TRUE ;
1408
1409 --
1410 -- begin bugfix 4227993
1411 --
1412 CTO_UTILITY_PK.release_lock(
1413 x_return_status => xReturnStatus,
1414 x_Msg_Count => xMsgCount,
1415 x_Msg_Data => xMsgData,
1416 p_hash_value => l_hash_value);
1417
1418 if xReturnStatus <> FND_API.G_RET_STS_SUCCESS then
1419 oe_debug_pub.add('match_and_create_all_items: '|| 'get_user_lock returned error');
1420 raise fnd_api.g_exc_unexpected_error;
1421 end if;
1422
1423 --
1424 -- end bugfix 4227993
1425 --
1426
1427 end if;
1428
1429 if (lXConfigId is null) then
1430
1431 oe_debug_pub.add ('Create_All_items: ' || 'no match found ' , 1);
1432
1433 /* Sushant is Testig Important
1434 v_parent_ato_line_id := v_model_lines.parent_ato_line_id ;
1435 */
1436
1437 v_parent_ato_line_id := v_model_lines.line_id ;
1438
1439
1440 /* Set Perform Match = 'N' for current model and its parents */
1441
1442 v_update_count := null ; /* this has to be initialized to null for the loop below */
1443
1444 WHILE (TRUE)
1445 LOOP
1446
1447 if (v_parent_ato_line_id = v_ato_line_id or v_update_count = 0 ) then
1448 exit;
1449 end if;
1450
1451 update bom_cto_order_lines
1452 set perform_match = 'U' /* Unsuccessful Match */
1453 where line_id = v_parent_ato_line_id
1454 and perform_match = 'Y'
1455 returning parent_ato_line_id , ato_line_id
1456 into v_parent_ato_line_id , v_ato_line_id ;
1457
1458 v_update_count := SQL%rowcount ;
1459
1460 oe_debug_pub.add ('Create_All_items: ' || ' v_parent_ato ' || v_parent_ato_line_id
1461 || ' v_ato ' || v_ato_line_id
1462 || ' upd count ' || v_update_count , 1);
1463
1464
1465 END LOOP ;
1466
1467
1468
1469
1470
1471
1472 else /* Match Found */
1473
1474 /* update matched config in bcol and bcol_temp */
1475
1476 update bom_cto_order_lines
1477 set config_item_id = lXConfigId
1478 where line_id = v_model_lines.line_id
1479 returning config_creation into v_bcmo_config_orgs ;
1480
1481 oe_debug_pub.add('Create_All_Items: ' ||
1482 'update bcol count::'||
1483 SQL%ROWCOUNT , 1);
1484
1485 oe_debug_pub.add ('Create_All_items: ' || 'updated bcol ' || lXConfigId
1486 || ' for ' || v_model_lines.line_id
1487 || ' config_orgs ' || v_bcmo_config_orgs
1488 || ' rows ' || SQL%ROWCOUNT, 1);
1489
1490
1491 update /*+ INDEX (BOM_CTO_ORDER_LINES_GT BOM_CTO_ORDER_LINES_GT_U1) */
1492 bom_cto_order_lines_gt
1493 set config_item_id = lXConfigId
1494 where line_id = v_model_lines.line_id ;
1495
1496 oe_debug_pub.add ('Create_All_items: ' || 'updated bcol_gt ' || lXConfigId
1497 || ' for ' || v_model_lines.line_id
1498 || ' rows ' || SQL%ROWCOUNT, 1);
1499
1500
1501 if( v_bcmo_config_orgs = '3' ) then
1502
1503 select group_reference_id into v_bcso_group_reference_id from bom_cto_src_orgs_b
1504 where line_id = v_model_lines.line_id ;
1505
1506
1507
1508 update bom_cto_model_orgs
1509 set config_item_id = lXConfigId
1510 where group_reference_id = v_bcso_group_reference_id ;
1511
1512
1513
1514 oe_debug_pub.add ('Create_All_items: matched item ' || 'updated bcmo ' || lXConfigId
1515 || ' line id info ' || v_model_lines.line_id
1516 || ' for ' || v_bcso_group_reference_id
1517 || ' rows ' || SQL%ROWCOUNT, 1);
1518
1519
1520
1521
1522
1523
1524 else
1525 update bom_cto_src_orgs_b
1526 set config_item_id = lXConfigId
1527 where line_id = v_model_lines.line_id ;
1528
1529
1530 oe_debug_pub.add ('Create_All_items: ' || 'updated bcso_b ' || lXConfigId
1531 || ' for ' || v_model_lines.line_id
1532 || ' rows ' || SQL%ROWCOUNT, 1);
1533
1534
1535
1536 end if;
1537
1538
1539
1540 end if;
1541
1542 end if; /* attempt match code */
1543
1544
1545 /* create config item for matched and non matched configurations */
1546
1547
1548
1549 IF( lXConfigId is null or
1550 ( lXConfigId is not null and nvl(v_model_lines.config_creation, 1) <> 3 )
1551 or
1552 ( p_mode = 'PRECONFIG' )
1553 ) then
1554
1555 oe_debug_pub.add('Create_All_Items: Handle Item Creation for Type 1 , 2 , Preconfig or no match/reuse ' , 1 ) ;
1556
1557 lStatus := CTO_CONFIG_ITEM_PK.create_item(
1558 pModelId => v_model_lines.inventory_item_id,
1559 pLineId => v_model_lines.line_id,
1560 pConfigId => lxConfigId,
1561 xMsgCount => xMsgCount,
1562 xMsgData => xMsgData,
1563 p_mode => p_mode );
1564
1565 IF lStatus <> 1 THEN
1566 IF PG_DEBUG <> 0 THEN
1567 oe_debug_pub.add('Create_All_Items: ' ||
1568 'Create_Item returned 0::item::'||
1569 to_char(v_model_lines.inventory_item_id), 1);
1570 END IF;
1571
1572 -- cto_msg_pub.cto_message('BOM','CTO_CREATE_ITEM_ERROR');
1573 raise FND_API.G_EXC_ERROR;
1574
1575
1576 ELSE --if status is success
1577 --start bugfix 3070429,3124169
1578
1579 l_eni_star_record.inventory_item_id := lxConfigId;
1580
1581 IF PG_DEBUG <> 0 THEN
1582 oe_debug_pub.add('Create_All_Items: ' || 'conifg item id passed to ENI=>'||
1583 l_eni_star_record.inventory_item_id , 5);
1584 END IF;
1585
1586 --follwoing API is maintained by PLM,DBI team present in Bom source control
1587
1588
1589 CTO_ENI_WRAPPER.CTO_CALL_TO_ENI
1590 (p_api_version => 1.0,
1591 p_star_record => l_eni_star_record,
1592 x_return_status =>eni_return_status,
1593 x_msg_count => xMsgCount,
1594 x_msg_data => xMsgData);
1595
1596
1597
1598
1599
1600 --return status passed as 'S' and not as FND_API.XXXXX
1601 --CTO has decided not to fail for error messages but just log messages
1602 --refer bug 3124169 for more info
1603 IF eni_return_status = 'S' THEN
1604 IF PG_DEBUG <> 0 THEN
1605 oe_debug_pub.add('Cto_Eni_Wrapper_Api:' || 'ENI_CONFIG_ITEMS_PKG.Create_config_items returned=>'
1606 ||eni_return_status, 5);
1607 END IF;
1608 ELSE
1609 IF PG_DEBUG <> 0 THEN
1610 oe_debug_pub.add('Cto_Eni_Wrapper_Api: ' || 'ENI_CONFIG_ITEMS_PKG.Create_config_items returned=>'
1611 ||eni_return_status, 1);
1612 oe_debug_pub.add('Cto_Eni_Wrapper_Api: ' || 'IGNORING ABOVE ERROR', 1);
1613 END IF;
1614
1615 END IF;
1616
1617 --end bugfix 3070429,3124169
1618
1619
1620
1621
1622 END IF;
1623
1624
1625 IF PG_DEBUG <> 0 THEN
1626 oe_debug_pub.add('Create_All_Items: ' ||
1627 'Create_Item returned with lStatus::'||to_char(lStatus), 2);
1628
1629 oe_debug_pub.add('Create_All_Items: ' || 'ITEM CREATED IS ::'||
1630 to_char(lxConfigId), 1);
1631
1632
1633
1634 oe_debug_pub.add('Create_All_Items: ' || 'V_PERFORM_MATCH IS ::'|| v_perform_match, 1);
1635 END IF;
1636
1637
1638 /* update newly created config in bcol and bcol_temp */
1639
1640 update bom_cto_order_lines
1641 set config_item_id = lXConfigId
1642 where line_id = v_model_lines.line_id
1643 returning perform_match into v_perform_match;
1644
1645
1646 oe_debug_pub.add ('Create_All_items: ' || 'updated bcol ' || lXConfigId
1647 || ' for ' || v_model_lines.line_id , 1);
1648
1649
1650 oe_debug_pub.add('Create_All_Items: ' || 'V_PERFORM_MATCH IS ::'|| v_perform_match, 1);
1651
1652 update /*+ INDEX (BOM_CTO_ORDER_LINES_GT BOM_CTO_ORDER_LINES_GT_U1) */
1653 bom_cto_order_lines_gt
1654 set config_item_id = lXConfigId
1655 where line_id = v_model_lines.line_id ;
1656
1657
1658 oe_debug_pub.add ('Create_All_items: ' || 'updated bcol_gt ' || lXConfigId
1659 || ' for ' || v_model_lines.line_id , 1);
1660
1661
1662 update bom_cto_src_orgs_b
1663 set config_item_id = lXConfigId
1664 where line_id = v_model_lines.line_id ;
1665
1666
1667
1668 oe_debug_pub.add ('Create_All_items: ' || 'updated bcso ' || lXConfigId
1669 || ' for ' || v_model_lines.line_id
1670 || ' config_creation ' || v_model_lines.config_creation
1671 || ' rows ' || SQL%ROWCOUNT, 1);
1672
1673
1674
1675 if( v_model_lines.config_creation = 3 and v_perform_match = 'U' ) then
1676
1677 select group_reference_id into v_bcso_group_reference_id from bom_cto_src_orgs_b
1678 where line_id = v_model_lines.line_id ;
1679
1680
1681
1682 update bom_cto_model_orgs
1683 set config_item_id = lXConfigId
1684 where group_reference_id = v_bcso_group_reference_id ;
1685
1686
1687
1688 oe_debug_pub.add ('Create_All_items: ' || 'updated bcmo ' || lXConfigId
1689 || ' for ' || v_bcso_group_reference_id
1690 || ' rows ' || SQL%ROWCOUNT, 1);
1691
1692
1693 end if;
1694
1695
1696
1697
1698
1699
1700
1701
1702
1703 IF( v_perform_match = 'U') then
1704
1705 oe_debug_pub.add ('Create_All_items: ' || 'canning configuration' || v_model_lines.line_id, 1);
1706
1707 /* CAN configuration for items created when match profile = 'Yes' */
1708
1709 lStatus := CTO_MATCH_CONFIG.can_configurations(
1710 v_model_lines.line_id,
1711 0,
1712 0,
1713 0,
1714 lUserId,
1715 lLoginId,
1716 l_x_error_msg,
1717 l_x_msg_name);
1718
1719
1720
1721 END IF;
1722
1723
1724 /* Matched Type 3 configurations */
1725 elsif ( lXConfigId is not null and nvl(v_model_lines.config_creation, 1) = 3 and v_perform_match <> 'N' ) then
1726
1727
1728
1729
1730
1731 v_model_item_status := 0 ;
1732 v_config_item_status := 0 ;
1733
1734 begin
1735 select 1 /* BCMO not in synch with Model Item */ into v_model_item_status from dual
1736 where exists ( select organization_id from mtl_system_items msi
1737 where not exists
1738 ( select organization_id from bom_cto_model_orgs bcmo
1739 where bcmo.config_item_id = lXConfigId
1740 and bcmo.organization_id = msi.organization_id )
1741 and msi.inventory_item_id = v_model_lines.inventory_item_id ) ;
1742
1743 exception
1744 when others then
1745
1746 null ;
1747 end ;
1748
1749
1750
1751 begin
1752 select 1 /*Config not in synch with Model Item */ into v_config_item_status from dual
1753 where exists ( select organization_id from mtl_system_items model
1754 where not exists
1755 ( select organization_id from mtl_system_items config
1756 where config.inventory_item_id = lXConfigId
1757 and config.organization_id = model.organization_id )
1758 and model.inventory_item_id = v_model_lines.inventory_item_id ) ;
1759
1760 exception
1761 when others then
1762
1763 null ;
1764 end ;
1765
1766
1767 IF PG_DEBUG <> 0 THEN
1768 oe_debug_pub.add ('Create_All_Items: ' ||
1769 'v_model_item_status ' || to_char(v_model_item_status) ||
1770 'v_config_item_status ' || to_char(v_config_item_status) , 1);
1771 END IF;
1772
1773
1774 if( v_model_item_status = 1 or v_config_item_status = 1 ) then
1775
1776
1777 IF PG_DEBUG <> 0 THEN
1778 oe_debug_pub.add ('Create_All_Items: ' || 'Error: Item Not Enabled in some orgs', 1);
1779 END IF;
1780
1781 select concatenated_segments into v_model_item_name
1782 from mtl_system_items_kfv
1783 where inventory_item_id = v_model_lines.inventory_item_id
1784 and rownum = 1 ;
1785
1786
1787 l_token(1).token_name := 'MODEL_NAME';
1788 l_token(1).token_value := v_model_item_name ;
1789
1790
1791
1792 select concatenated_segments into v_config_item_name
1793 from mtl_system_items_kfv
1794 where inventory_item_id = lXConfigId
1795 and rownum = 1 ;
1796
1797
1798 l_token(2).token_name := 'CONFIG_NAME';
1799 l_token(2).token_value := v_config_item_name;
1800
1801
1802 cto_msg_pub.cto_message('BOM','CTO_MATCH_ITEM_NOT_ENABLED', l_token );
1803
1804 raise FND_API.G_EXC_ERROR;
1805
1806
1807 else
1808
1809 IF PG_DEBUG <> 0 THEN
1810 oe_debug_pub.add ('Create_All_Items: ' || 'Item Enabled in all orgs', 1);
1811 END IF;
1812
1813
1814 end if;
1815
1816
1817 oe_debug_pub.add('Create_All_Items: No need to Handle Item Creation for Type 3 matched AutoConfig ' , 1 ) ;
1818
1819
1820
1821
1822
1823
1824 /* update newly created config in bcol and bcol_temp */
1825
1826 update bom_cto_order_lines
1827 set config_item_id = lXConfigId
1828 where line_id = v_model_lines.line_id ;
1829
1830
1831 oe_debug_pub.add ('Create_All_items: ' || 'updated bcol ' || lXConfigId
1832 || ' for ' || v_model_lines.line_id , 1);
1833
1834
1835
1836 update /*+ INDEX (BOM_CTO_ORDER_LINES_GT BOM_CTO_ORDER_LINES_GT_U1) */
1837 bom_cto_order_lines_gt
1838 set config_item_id = lXConfigId
1839 where line_id = v_model_lines.line_id ;
1840
1841
1842 oe_debug_pub.add ('Create_All_items: ' || 'updated bcol_gt ' || lXConfigId
1843 || ' for ' || v_model_lines.line_id , 1);
1844
1845
1846 select group_reference_id into v_bcso_group_reference_id from bom_cto_src_orgs_b
1847 where line_id = v_model_lines.line_id ;
1848
1849
1850
1851 update bom_cto_model_orgs
1852 set config_item_id = lXConfigId
1853 where group_reference_id = v_bcso_group_reference_id ;
1854
1855
1856
1857 oe_debug_pub.add ('Create_All_items: ' || 'updated bcmo ' || lXConfigId
1858 || ' for ' || v_bcso_group_reference_id
1859 || ' rows ' || SQL%ROWCOUNT, 1);
1860
1861
1862 /* Needs to account for BCMO for type 3 */
1863 update bom_cto_src_orgs_b
1864 set config_item_id = lXConfigId
1865 where line_id = v_model_lines.line_id ;
1866
1867
1868
1869 oe_debug_pub.add ('Create_All_items: ' || 'updated bcso ' || lXConfigId
1870 || ' for ' || v_model_lines.line_id
1871 || ' rows ' || SQL%ROWCOUNT, 1);
1872
1873
1874
1875 elsif ( lXConfigId is not null and nvl(v_model_lines.config_creation, 1) = 3 and v_perform_match = 'N' ) then
1876
1877 oe_debug_pub.add('Create_All_Items: Handle Item Creation for Type 3 reuse ' , 1 ) ;
1878
1879 lStatus := CTO_CONFIG_ITEM_PK.create_item(
1880 pModelId => v_model_lines.inventory_item_id,
1881 pLineId => v_model_lines.line_id,
1882 pConfigId => lxConfigId,
1883 xMsgCount => xMsgCount,
1884 xMsgData => xMsgData,
1885 p_mode => p_mode );
1886
1887 IF lStatus <> 1 THEN
1888 IF PG_DEBUG <> 0 THEN
1889 oe_debug_pub.add('Create_All_Items: ' ||
1890 'Create_Item returned 0::item::'||
1891 to_char(v_model_lines.inventory_item_id), 1);
1892 END IF;
1893
1894 -- cto_msg_pub.cto_message('BOM','CTO_CREATE_ITEM_ERROR');
1895 raise FND_API.G_EXC_ERROR;
1896
1897
1898 ELSE --if status is success
1899 --start bugfix 3070429,3124169
1900
1901 l_eni_star_record.inventory_item_id := lxConfigId;
1902
1903 IF PG_DEBUG <> 0 THEN
1904 oe_debug_pub.add('Create_All_Items: ' || 'conifg item id passed to ENI=>'||
1905 l_eni_star_record.inventory_item_id , 5);
1906 END IF;
1907
1908 --follwoing API is maintained by PLM,DBI team present in Bom source control
1909
1910
1911 CTO_ENI_WRAPPER.CTO_CALL_TO_ENI
1912 (p_api_version => 1.0,
1913 p_star_record => l_eni_star_record,
1914 x_return_status =>eni_return_status,
1915 x_msg_count => xMsgCount,
1916 x_msg_data => xMsgData);
1917
1918
1919
1920
1921
1922 --return status passed as 'S' and not as FND_API.XXXXX
1923 --CTO has decided not to fail for error messages but just log messages
1924 --refer bug 3124169 for more info
1925 IF eni_return_status = 'S' THEN
1926 IF PG_DEBUG <> 0 THEN
1927 oe_debug_pub.add('Cto_Eni_Wrapper_Api:' || 'ENI_CONFIG_ITEMS_PKG.Create_config_items returned=>'
1928 ||eni_return_status, 5);
1929 END IF;
1930 ELSE
1931 IF PG_DEBUG <> 0 THEN
1932 oe_debug_pub.add('Cto_Eni_Wrapper_Api: ' || 'ENI_CONFIG_ITEMS_PKG.Create_config_items returned=>'
1933 ||eni_return_status, 1);
1934 oe_debug_pub.add('Cto_Eni_Wrapper_Api: ' || 'IGNORING ABOVE ERROR', 1);
1935 END IF;
1936
1937 END IF;
1938
1939 --end bugfix 3070429,3124169
1940
1941
1942
1943
1944 END IF;
1945
1946
1947 IF PG_DEBUG <> 0 THEN
1948 oe_debug_pub.add('Create_All_Items: ' ||
1949 'Create_Item returned with lStatus::'||to_char(lStatus), 2);
1950
1951 oe_debug_pub.add('Create_All_Items: ' || 'ITEM CREATED IS ::'||
1952 to_char(lxConfigId), 1);
1953 END IF;
1954
1955
1956 /* update newly created config in bcol and bcol_temp */
1957
1958 update bom_cto_order_lines
1959 set config_item_id = lXConfigId
1960 where line_id = v_model_lines.line_id ;
1961
1962
1963 oe_debug_pub.add ('Create_All_items: ' || 'updated bcol ' || lXConfigId
1964 || ' for ' || v_model_lines.line_id , 1);
1965
1966
1967
1968 update bom_cto_src_orgs_b
1969 set config_item_id = lXConfigId
1970 where line_id = v_model_lines.line_id ;
1971
1972
1973
1974 oe_debug_pub.add ('Create_All_items: ' || 'updated bcso ' || lXConfigId
1975 || ' for ' || v_model_lines.line_id
1976 || ' config_creation ' || v_model_lines.config_creation
1977 || ' rows ' || SQL%ROWCOUNT, 1);
1978
1979
1980
1981
1982 END IF; /* check for config item creation */
1983
1984
1985 END LOOP; /* config creation loop for each model line */
1986
1987 return(1);
1988
1989 EXCEPTION
1990 when NO_DATA_FOUND then
1991 IF PG_DEBUG <> 0 THEN
1992 oe_debug_pub.add('Create_All_Items: ' || 'create_all_items::ndf::lStmtNum::'||to_char(lStmtNum)||'::'||sqlerrm, 1);
1993 END IF;
1994 xReturnStatus := fnd_api.g_ret_sts_error;
1995 cto_msg_pub.count_and_get
1996 ( p_msg_count => xMsgCount
1997 , p_msg_data => xMsgData
1998 );
1999 return(0);
2000
2001 when FND_API.G_EXC_ERROR then
2002 IF PG_DEBUG <> 0 THEN
2003 oe_debug_pub.add('Create_All_Items: ' || 'Create_All_Items::exp error::'||to_char(lStmtNum)||'::'||sqlerrm, 1);
2004 END IF;
2005 xReturnStatus := fnd_api.g_ret_sts_error;
2006 cto_msg_pub.count_and_get
2007 ( p_msg_count => xMsgCount
2008 , p_msg_data => xMsgData
2009 );
2010 return(0);
2011
2012 when FND_API.G_EXC_UNEXPECTED_ERROR then
2013 IF PG_DEBUG <> 0 THEN
2014 oe_debug_pub.add('Create_All_Items: ' || 'Create_All_Items::unexp error::'||to_char(lStmtNum)||'::'||sqlerrm, 1);
2015 END IF;
2016 xReturnStatus := fnd_api.g_ret_sts_unexp_error;
2017 cto_msg_pub.count_and_get
2018 ( p_msg_count => xMsgCount
2019 , p_msg_data => xMsgData
2020 );
2021 return(0);
2022
2023 when OTHERS then
2024 IF PG_DEBUG <> 0 THEN
2025 oe_debug_pub.add('Create_All_Items: ' || 'Create_All_Items::others::'||to_char(lStmtNum)||'::'||sqlerrm, 1);
2026 END IF;
2027 xReturnStatus := fnd_api.g_ret_sts_unexp_error;
2028 cto_msg_pub.count_and_get
2029 ( p_msg_count => xMsgCount
2030 , p_msg_data => xMsgData
2031 );
2032 return(0);
2033
2034 END Create_All_Items;
2035
2036
2037 procedure perform_match(
2038 p_ato_line_id in bom_cto_order_lines.ato_line_id%type ,
2039 x_match_found out NOCOPY varchar2,
2040 x_matching_config_id out NOCOPY number,
2041 x_error_message out NOCOPY VARCHAR2,
2042 x_message_name out NOCOPY varchar2
2043 )
2044 is
2045 l_stmt_num number := 0;
2046 l_cfm_value number;
2047 l_config_line_id number;
2048 l_tree_id integer;
2049 l_return_status varchar2(1);
2050 l_x_error_msg_count number;
2051 l_x_error_msg varchar2(240);
2052 l_x_error_msg_name varchar2(30);
2053 l_x_table_name varchar2(30);
2054 l_match_profile varchar2(10);
2055 l_org_id number;
2056 l_model_id number;
2057 l_primary_uom_code varchar(3);
2058 l_x_config_id number;
2059 l_top_model_line_id number;
2060
2061 l_x_qoh number;
2062 l_x_rqoh number;
2063 l_x_qs number;
2064 l_x_qr number;
2065 l_x_att number;
2066 l_active_activity varchar2(30);
2067 l_x_bill_seq_id number;
2068 l_status number ;
2069
2070 l_perform_match varchar2(1) ;
2071
2072 x_return_status varchar2(1);
2073 x_msg_count number;
2074 x_msg_data varchar2(100);
2075
2076 PROCESS_ERROR EXCEPTION;
2077
2078
2079 cursor c_model_lines is
2080 select line_id, parent_ato_line_id
2081 from bom_cto_order_lines
2082 where bom_item_type = '1'
2083 and ato_line_id = p_ato_line_id
2084 and nvl(wip_supply_type,0) <> 6
2085 order by plan_level desc;
2086
2087 v_sqlcode number ;
2088 l_custom_match_profile varchar2(10);
2089
2090
2091 v_bcol_count number ;
2092 v_bcol_gt_count number ;
2093
2094
2095 begin
2096
2097
2098
2099 select count(*) into v_bcol_count from bom_cto_order_lines
2100 where ato_line_id = p_ato_line_id ;
2101
2102
2103 select /*+ INDEX (BOM_CTO_ORDER_LINES_GT BOM_CTO_ORDER_LINES_GT_N1) */
2104 count(*) into v_bcol_gt_count from bom_cto_order_lines_gt
2105 where ato_line_id = p_ato_line_id ;
2106
2107
2108 oe_debug_pub.add( ' perform_match bcol count ' || v_bcol_count , 1 ) ;
2109 oe_debug_pub.add( ' perform_match bcol_gt count ' || v_bcol_gt_count , 1 ) ;
2110
2111 l_stmt_num := 1;
2112
2113 x_match_found := 'N' ;
2114
2115 l_custom_match_profile := FND_PROFILE.Value('BOM:CUSTOM_MATCH');
2116
2117 l_stmt_num := 5;
2118
2119 /* for each model */
2120
2121 for l_next_rec in c_model_lines loop
2122
2123 l_x_config_id := NULL;
2124
2125
2126
2127 select perform_match into l_perform_match
2128 from bom_cto_order_lines
2129 where line_id = l_next_rec.line_id ;
2130
2131
2132 oe_debug_pub.add( ' perform_match: line_id ' || l_next_rec.line_id || ' match ' || l_perform_match , 1 ) ;
2133
2134
2135 if( l_perform_match = 'U' ) then
2136
2137 begin
2138
2139 update bom_cto_order_lines set perform_match = 'U'
2140 where perform_match = 'Y'
2141 and line_id = l_next_rec.parent_ato_line_id ;
2142
2143 exception
2144 when no_data_found then
2145 null ;
2146
2147 end ;
2148
2149 x_match_found := 'N' ;
2150
2151 x_matching_config_id := NULL ; /* fix for bug#2048023. */
2152
2153
2154
2155 elsif( l_perform_match in( 'Y' , 'C' ) ) then
2156
2157 if ( l_perform_match = 'Y' ) then
2158 l_stmt_num := 10;
2159 oe_debug_pub.add('Standard Match.', 1);
2160 l_status := cto_match_config.check_config_match(
2161 l_next_rec.line_id,
2162 l_x_config_id,
2163 l_x_error_msg,
2164 l_x_error_msg_name);
2165
2166 oe_debug_pub.add(' done Check Config Match ' , 1 ) ;
2167
2168
2169 elsif ( l_perform_match = 'C' ) then
2170 l_stmt_num := 15;
2171 l_status := CTO_CUSTOM_MATCH_PK.find_matching_config(
2172 l_next_rec.line_id,
2173 l_x_config_id,
2174 l_x_error_msg,
2175 l_x_error_msg_name,
2176 l_x_table_name);
2177 end if;
2178
2179 l_stmt_num := 20;
2180
2181 if (l_status = 0) then
2182 oe_debug_pub.add('Failed in Check Config Match for line id '
2183 || to_char(l_next_rec.line_id), 1);
2184
2185 raise PROCESS_ERROR;
2186
2187 end if;
2188
2189
2190 l_stmt_num := 25;
2191
2192
2193 if (l_status = 1 and l_x_config_id is NULL) then
2194 l_stmt_num := 30;
2195
2196 x_message_name := 'CTO_MR_NO_MATCH';
2197 x_error_message := 'No matching configurations for line '
2198 || to_char(l_next_rec.line_id);
2199 l_stmt_num := 137;
2200
2201 -- insert into my_debug_messages values ( 'No Match found' ) ;
2202 x_match_found := 'N' ;
2203
2204 x_matching_config_id := NULL ; /* fix for bug#2048023. */
2205
2206 /* fix for bug#2048023.
2207 This variable has to be initialized to null as it was not
2208 null for a lower level match in the perform match loop.
2209 */
2210
2211
2212 /* update the perform match column to 'U' so that this item is canned */
2213 begin
2214 update bom_cto_order_lines
2215 set perform_match = 'U'
2216 where line_id = l_next_rec.line_id
2217 and perform_match = 'Y';
2218
2219 exception
2220 when no_data_found then
2221 null ;
2222
2223 end ;
2224
2225
2226
2227 /* update the perform match column to 'U' so that no match
2228 is attempted against its parent and it is canned
2229 */
2230
2231 begin
2232 update bom_cto_order_lines
2233 set perform_match = 'U'
2234 where line_id = l_next_rec.parent_ato_line_id
2235 and perform_match = 'Y';
2236
2237 exception
2238 when no_data_found then
2239 null ;
2240
2241 end ;
2242
2243
2244 x_match_found := 'U' ;
2245
2246 x_matching_config_id := NULL ;
2247
2248 elsif (l_status = 1 and l_x_config_id is not null) then
2249
2250 l_stmt_num := 35;
2251
2252
2253 oe_debug_pub.add('Match for line id '
2254 || to_char(l_next_rec.line_id)
2255 || ' is ' || to_char(l_x_config_id) ,1);
2256
2257
2258 update bom_cto_order_lines
2259 set config_item_id = l_x_config_id
2260 where line_id = l_next_rec.line_id;
2261
2262
2263
2264 oe_debug_pub.add( 'perform_match: bcol update ' || SQL%rowcount , 1 ) ;
2265
2266 update /*+ INDEX (BOM_CTO_ORDER_LINES_GT BOM_CTO_ORDER_LINES_GT_U1) */
2267 bom_cto_order_lines_gt
2268 set config_item_id = l_x_config_id
2269 where line_id = l_next_rec.line_id;
2270
2271
2272 oe_debug_pub.add( 'perform_match: bcol_gt update ' || SQL%rowcount , 1 ) ;
2273
2274 l_stmt_num := 40 ;
2275
2276 x_matching_config_id := l_x_config_id ;
2277
2278 x_match_found := 'Y' ;
2279
2280
2281 l_stmt_num := 45 ;
2282
2283 -- insert into my_debug_messages values ( 'Match found' ) ;
2284 -- insert into my_debug_messages values ( 'Matched Item ' || to_char(x_matching_config_id ) ) ;
2285
2286
2287 end if;
2288
2289 else
2290
2291 oe_debug_pub.add(' Match OFF for line id '
2292 || to_char(l_next_rec.line_id) ,1);
2293
2294
2295 x_match_found := 'N' ;
2296
2297
2298 end if ; /* if perform_match = 'N' 'U' 'Y' 'C' */
2299
2300
2301 end loop;
2302
2303
2304 exception
2305 when others then
2306 V_SQLCODE := SQLCODE ;
2307 oe_debug_pub.add ( ' exception in match at step ' || to_char( l_stmt_num ) ) ;
2308 -- insert into my_debug_messages values ( ' exception in match at step ' || to_char( l_stmt_num ) ) ;
2309 -- insert into my_debug_messages values ( ' exception in match SQL ' || to_char( V_SQLCODE ) ) ;
2310
2311 end perform_match ;
2312
2313
2314
2315
2316
2317
2318
2319
2320
2321 PROCEDURE evaluate_item_behavior( p_ato_line_id in NUMBER
2322 ,x_return_status out NOCOPY varchar2
2323 ,x_msg_count out NOCOPY number
2324 ,x_msg_data out NOCOPY varchar2 )
2325 is
2326
2327 cursor c_item_behavior
2328 is
2329 select config_creation , line_id , parent_ato_line_id
2330 from bom_cto_order_lines
2331 where ato_line_id = p_ato_line_id
2332 and bom_item_type = '1' and nvl(wip_supply_type, 1 ) <> 6
2333 order by plan_level desc ;
2334
2335
2336 v_config_creation bom_cto_order_lines.config_creation%type ;
2337 v_line_id number ;
2338 v_parent_ato_line_id number ;
2339 v_last_config_creation bom_cto_order_lines.config_creation%type ;
2340
2341
2342 TYPE TAB_BCOL is TABLE of bom_cto_order_lines%rowtype index by binary_integer ;
2343
2344 item_behavior_violated exception ;
2345
2346 t_bcol TAB_BCOL ;
2347 i number ;
2348
2349 begin
2350
2351 oe_debug_pub.add ( ' entered evaluate item behavior ' , 1 ) ;
2352
2353
2354
2355 open c_item_behavior ;
2356
2357 loop
2358
2359 fetch c_item_behavior into v_config_creation
2360 ,v_line_id
2361 ,v_parent_ato_line_id ;
2362
2363
2364 exit when c_item_behavior%notfound ;
2365
2366
2367 t_bcol(v_line_id).line_id := v_line_id ;
2368 t_bcol(v_line_id).parent_ato_line_id := v_parent_ato_line_id ;
2369 t_bcol(v_line_id).config_creation := v_config_creation ;
2370
2371
2372
2373 end loop ;
2374
2375 close c_item_behavior ;
2376
2377
2378 i := t_bcol.first ;
2379
2380 while i is not null
2381 loop
2382
2383
2384 if( t_bcol(i).config_creation in ( 1, 2 ) and t_bcol(t_bcol(i).parent_ato_line_id).config_creation = 3 ) then
2385
2386 oe_debug_pub.add( 'evaluate_item_behavior:' || ' item behavior violated for line id ' || t_bcol(i).line_id
2387 || ' behavior ' || t_bcol(i).config_creation
2388 || ' parent ato line ' || t_bcol(i).parent_ato_line_id
2389 || ' parent behavior ' || t_bcol(t_bcol(i).parent_ato_line_id).config_creation , 1 ) ;
2390
2391
2392 raise item_behavior_violated ;
2393 end if;
2394
2395
2396 i := t_bcol.next(i) ;
2397
2398 end loop ;
2399
2400
2401
2402 exception
2403 when item_behavior_violated then
2404
2405 oe_debug_pub.add( 'evaluate_item_behavior:' || ' item behavior violated ' , 1 ) ;
2406
2407
2408 end evaluate_item_behavior;
2409
2410 end CTO_ITEM_PK;