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