[Home] [Help]
PACKAGE BODY: APPS.CTO_CONFIG_BOM_PK
Source
1 package body CTO_CONFIG_BOM_PK as
2 /* $Header: CTOCBOMB.pls 120.15.12010000.4 2008/09/10 17:53:43 appldev ship $ */
3
4 /*============================================================================
5 | Copyright (c) 1993 Oracle Corporation Belmont, California, USA |
6 | All rights reserved. |
7 | Oracle Manufacturing |
8 +=============================================================================+
9 |
10 | FILE NAME : CTOCBOMB.pls
11 | DESCRIPTION :
12 | This file creates a packaged function that loads the
13 | BOM tables for the config item. Converted from BOMLDCBB.pls
14 | for CTO streamline for new OE
15 | HISTORY : created 09-JUL-1999 by Usha Arora
16 |
17 | ksarkar 12-JUN-01 Bugfix 1653881
18 | single row subquery of ic1.component_sequence_id returns more than
19 | one rows when same component is used more than once in a given
20 | assembly.
21 | We do not need to select component_sequence_id through a subquery.
22 | The bill_sequence_id of the option class and join condition
23 | ic1.component_item_id = bcol1.inventory_item_id will select unique components
24 | for the option class.
25 |
26 | ksarkar 01-JUN-01 Bugfix 1812159
27 | Date operations make a disabled item effective and increases the
28 | component usage in configured item.
29 |
30 | sbhaskar 16-JUN-01 Bugfix 1835357
31 | Replaced fnd_file calls with oe_debug_pub
32 |
33 | ksarkar 19-JUL-01 Bugfix 1845141
34 | mtl_system_items_tl is not getting updated with correct description .
35 | Added message in lines 1486-87 and 1669-70 for better understanding of
36 | "WHEN OTHERS" exception .
37 |
38 | ksarkar 19-JUL-01 Bugfix 1876998
39 | Remove semicolon from comment to improve performance.
40 |
41 | Modified on 24-AUG-2001 by Sushant Sawant: BUG #1957336
42 | Added a new functionality for preconfigure bom.
43 |
44 | ksarkar 13-NOV-01 Bugfix 2086234
45 | Add condition "Implementation_date is not null" in Inherit_op_seq_ml
46 |
47 | ksarkar 26-NOV-01 Bugfix 2115056
48 | Copy base model attributes ( DFF's) to configured item.
49 |
50 | ksarkar 04-JAN-02 Bugfix 2171807 ( Bugfix 2163311 in main )
51 | Catalog description is not getting updated in Master Org.
52 |
53 | sbhaskar 07-FEB-02 Bugfix 2215274 (bugfix 2221008 in main)
54 | Performance : Replaced bind variables with column join.
55 |
56 | ksarkar 21-FEB-02 Bugfix 2222518 (bugfix 2236844 in main )
57 | Option Class operation seq not getting inherited to child
58 | included items.
59 |
60 | ksarkar 28-FEB-02 Bugfix 2244856 (bugfix 2246663 in main )
61 | Unable to handle no_data_found error
62 |
63 | ksarkar 09-APR-02 Bugfix 1912376 (bugfix 2292468 in main )
64 | Checking item effectivity till schedule ship date
65 |
66 | ksarkar 17-MAY-02 Bugfix 2307936 (bugfix 2379051 in main )
67 | New logic of operation seq inheritence
68 |
69 | ssawant 28-MAY-02 Bugfix 2312199 (Refix for bug1912376 )
70 | bug 1912376 could still fail in case of sourced lower level models
71 |
72 | ksarkar 04-JUN-02 Bugfix 2374246 (Bugfix 2402935 in main )
73 | Config item created with no BOM
74 |
75 | ksarkar 04-JUN-02 Bugfix 2389283 (Bugfix 2402935 in main )
76 | Included Item under a non-phantom sub model gets attached to top
77 | model in config item bill.
78 |
79 | ksarkar 26-JUN-02 Bugfix 2433862 ( Bugfix 2435855 in main )
80 | Failed to insert rows with null op seq num in bom_inventory_components
81 | when ATO under PTO has no routing but inherit_op_seq profile is
82 | set to YES.
83 |
84 | ksarkar 10-OCT-02 Bugfix 2590966 ( Bugfix 2618752 in main )
85 | Catalog descriptions not rolled up correctly for multi -level
86 | configurations.
87 |
88 | ksarkar 21-NOV-02 Bugfix 2524562 ( Bugfix 2652271 in main )
89 | Inconsistent use of order dates in validating BOM effectivity.
90 |
91 | ksarkar 18-FEB-03 Bugfix 2765635 ( Bugfix 2807548 in main )
92 | New custom hook for catalog description of multi-level model .
93 |
94 | ksarkar 23-FEB-03 Bugfix 2814257 ( Bugfix 2817041 in main )
95 | Fix for 2524562 not working when opseq profile is turned ON.
96 |
97 | ksarkar 02-JUL-03 Bugfix 2929861 ( Bugfix 2986192 in main )
98 | Config item creation will now depend upon the value of
99 | profile BOM:CONFIG_EXCEPTION
100 |
101 | Modified on 14-MAR-2003 By Sushant Sawant
102 | Decimal-Qty Support for Option Items.
103 |
104 | ksarkar 20-NOV-03 Bugfix 3222932
105 | Inserting actual eff and disable dates for config components
106 | New consolidation logic
107 |
108 |
109 | ssawant 09-JAN-04 Bugfix 3358160
110 | Error Message Added CTO_ZERO_BOM_COMP for option item with zero qty on config bom.
111 |
112 |
113 | ssawant 15-JAN-04 Bugfix 3374548
114 | Added bill_sequence_id to condition to avoid corrupt data from bom_inventory_comps_interface.
115 |
116 |
117 | ssawant 29-JAN-04 Bugfix 3367823
118 | Accounted for UOM conversion in bom_inventory_components.
119 |
120 |
121 | ssawant 05-FEB-04 Bugfix 3389846
122 | Accounted for disable date greater than EstRelDate, sysdate
123 |
124 | ssawant 05-FEB-04 Bugfix 3389846
125 | Accounted for disable date greater than EstRelDate, sysdate. Disable date clause has been changed to compare
126 | only if it is not null. This improves the query as well.
127 |
128 |
129 | Modified on 26-Mar-2004 By Sushant Sawant
130 | Fixed Bug#3484511
131 | all queries referencing oe_system_parameters_all
132 | should be replaced with a function call to oe_sys_parameters.value
133 |
134 | Modified : 21-JUN-2004 Sushant Sawant
135 | Fixed bug 3710032.
136 | Substitute components were not copied correctly.
137 |
138 |
139 | Modified : 12-AUG-2004 Sushant Sawant
140 | Fixed bug 3793286.
141 | Front Ported bug 3674833
142 |
143 |
144 | Modified : 13-AUG-2004 Kiran Konada
145 | bug fix 3759118,FP 3810243
146 | Added implemenation_date to BOM_BOM
147 | as sysdate
148 |
149 | Modified : 11-05-2004 Kiran Konada
150 | Fixed issue with bug 3793286.(Front Ported bug 3674833)
151 | added abs() in where clause as model_comp_seq in
152 | pl/sql record was a -ve value
153 |
154 |
155 |
156 | Modified : 12-08-2004 Sushant Sawant
157 | Fixed issue for bug 3793286
158 | commented "IF prev_comp_item_id <> component_item_id_arr(x1) then"
159 | This bug was not fixed properly for components with
160 | multiple effectivity date windows.
161 |
162 |
163 | Modified : 02-02-2005 Kiran Konada
164 | bug#4092184 FP:11.5.9 - 11.5.10 :I
165 | customer bug#4081613
166 | if custom package CTO_CUSTOM_CATALOG_DESC.catalog_desc_method is
167 | set to 'C' to use custom api AND if model item is not assigned
168 | to a catalog group. Create configuration process fails
169 |
170 | Fix has been made not to honor the custom package if a ato model
171 | is not assigned to a catalog gtroup or there are no descrptive elements
172 | defined for a catalog group. In fumction create_bom_data_ml
173 |
174 |
175 | Modified : 01-APR-2005 Sushant Sawant
176 | Fixed issue for bug4271269.
177 | populate structure_type_id and effectivity_control columns in
178 | bom_bill_of_materials view.
179 |
180 | Modified by Renga Kannan on 09/01/06 for bug 4542461
181 | Modified : 09-02-2005 Renga Kannan
182 | Fixed the following issues in LBM and effecitivity
183 | part of code
184 |
185 | 1.) LBM code does not handle null value for basis type
186 | Added nvl clause for all insert stmt from bom_inventory_components
187 | to bom_inventory_components_interface
188 |
189 | 2.) for overlapping effectivity dates with components having
190 | having different basis type the message is not raised
191 | properly. fixd that code
192 |
193 | 3.) Clubbing component code is inserting null qty value into
194 | bic interface. Fixed the code not to insert these rows.
195 |
196 | Modified by Renga Kannan on 09/07/2005
197 | Bug Fix 4595162
198 | Modified the code that populates basis type to
199 | bom_inventory_components table. As per bom team
200 | basis_type should have null for 'ITEM' and 2 for 'LOT'
201 |
202 |
203 *============================================================================*/
204
205 -- Bug 1912376 Declaring Global variable to hold the value of Schedule Ship Date
206
207 g_SchShpDate Date;
208
209 -- Bug 2222518 Declaring Global variable to hold the value of Estimated Release Date
210
211 g_EstRelDate Date;
212
213 PG_DEBUG Number := NVL(FND_PROFILE.value('ONT_DEBUG_LEVEL'), 0);
214
215 -- 3222932 setting global replacement of null disable dates
216
217 g_futuredate DATE := to_date('01/01/2099 00:00:00','MM/DD/YYYY HH24:MI:SS');
218
219
220
221 -- 4271269 populate structure_type_id in bom.
222
223 g_structure_type_id bom_bill_of_materials.structure_type_id%type ;
224
225
226 PROCEDURE update_item_num(
227 p_parent_bill_seq_id IN NUMBER,
228 p_item_num IN OUT NOCOPY NUMBER, /* NOCOPY project */
229 p_org_id IN NUMBER,
230 p_seq_increment IN NUMBER);
231
232 function create_bom_ml (
233 pModelId in number,
234 pConfigId in number,
235 pOrgId in number,
236 pLineId in number,
237 xBillId out NOCOPY number,
238 xErrorMessage out NOCOPY varchar2,
239 xMessageName out NOCOPY varchar2,
240 xTableName out NOCOPY varchar2)
241 return integer
242 is
243
244 lStmtNum number;
245 lCnt number := 0;
246 lConfigBillId number;
247 lstatus number;
248 lEstRelDate date;
249 lOpseqProfile number;
250 lItmBillID number;
251 lLineId number;
252 lModelId number;
253 lParentAtoLineId number := pLineId;
254 lOrderedQty number;
255 lLeadTime number;
256 lErrBuf varchar2(80);
257 lTotLeadTime number := 0;
258 lOEValidationOrg number;
259
260 v_ato_line_id bom_cto_order_lines.ato_line_id%type ;
261 v_program_id bom_cto_order_lines.program_id%type ;
262
263 /* 2524562 Declaring variables */
264
265 v_missed_line_id number;
266 v_missed_item varchar2(50);
267 v_config_item varchar2(50);
268 v_model varchar2(50);
269 v_missed_line_number varchar2(50);
270 v_order_number number;
271 l_token CTO_MSG_PUB.token_tbl;
272 lcreate_item number; -- 2986192
273 lorg_code varchar2(3); -- 2986192
274
275 /* Cursor to select dropped lines */
276 cursor missed_lines ( xlineid number,
277 xconfigbillid number,
278 xEstRelDate date ) is /* Effectivity_date changes */
279 select line_id
280 from bom_cto_order_lines
281 where parent_ato_line_id=xlineid
282 and parent_ato_line_id <> line_id /* to avoid selecting top model */
283 minus
284 select revised_item_sequence_id /* new column used to store line_id */
285 from bom_inventory_comps_interface
286 where bill_sequence_id = xconfigbillid
287 and greatest(sysdate, xEstRelDate ) >= effectivity_date
288 and (( disable_date is null ) or ( disable_date is not null and greatest(sysdate, xEstRelDate) <= disable_date )) ;
289
290 /* 2524562 End declaration */
291
292
293 v_zero_qty_count number ;
294
295 v_option_num number := 0 ;
296
297 l_new_line varchar2(10) := fnd_global.local_chr(10);
298
299 l_aname wf_engine.nametabtyp;
300 l_anumvalue wf_engine.numtabtyp;
301 l_atxtvalue wf_engine.texttabtyp;
302 luser_key varchar2(100);
303 litem_key varchar2(100);
304 lplanner_code mtl_system_items_vl.planner_code%type;
305
306 v_problem_model varchar2(1000) ;
307 v_problem_config varchar2(1000) ;
308 v_error_org varchar2(1000) ;
309 v_problem_model_line_num varchar2(1000) ;
310
311 v_table_count number ;
312
313 v_dropped_item_string varchar2(2000) ;
314 v_sub_dropped_item_string varchar2(2000) ;
315 v_ac_message_string varchar2(2000) ;
316
317 -- 3222932 setting replacement of null disable dates
318
319 g_futuredate DATE := to_date('01/01/2099','MM/DD/YYYY');
320
321
322 v_header_id oe_order_lines_all.header_id%type ;
323
324
325 l_return_status varchar2(10) ;
326 l_msg_count number ;
327 l_msg_data varchar2(2000) ;
328
329
330 v_recipient varchar2(100) ;
331
332 l_token1 CTO_MSG_PUB.token_tbl;
333 v_model_item_name varchar2(2000) ;
334
335
336
337 v_overlap_check number := 0 ;
338
339 TYPE num_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
340 TYPE date_tab IS TABLE OF DATE INDEX BY BINARY_INTEGER;
341
342 v_t_overlap_comp_item_id num_tab;
343 v_t_overlap_src_op_seq_num num_tab;
344 v_t_overlap_src_eff_date date_tab;
345 v_t_overlap_src_disable_date date_tab;
346 v_t_overlap_dest_op_seq_num num_tab;
347 v_t_overlap_dest_eff_date date_tab;
348 v_t_overlap_dest_disable_date date_tab;
349
350
351 --- Renga
352
353 cursor Debug_cur is
357 group by assembly_item_id,component_item_id,operation_seq_num;
354 select assembly_item_id,component_item_id,operation_seq_num,max(disable_date) disable_date
355 from bom_inventory_comps_interface
356 where bill_sequence_id = lconfigbillid
358
359 --- End Renga
360 /* END : New Effectivity date approach for bug 4147224 */
361
362 cursor debug_cur1 is
363 select component_item_id,component_sequence_id,operation_seq_num,effectivity_date,disable_date
364 from bom_inventory_comps_interface
365 where bill_sequence_id = lConfigBillId --Bugfix 6603382: So that components belonging to this bill only are picked up
366 order by component_item_id,operation_seq_num,effectivity_Date,disable_date;
367 l_token2 CTO_MSG_PUB.token_tbl;
368 l_model_name varchar2(1000);
369
370 BEGIN
371
372 /*------------------------------------------+
373 If the BOM exists, we do not need to do
374 anything. return with success.
375 This can happen because we allow delay
376 between 'create Item' and 'Create BOM'
377 workflow activities. A higher priority
378 order with matching configuration may
379 have created the BOM.
380 +------------------------------------------*/
381 xBillId := 0;
382 lStmtNum := 10;
383
384 lStatus := check_bom (pConfigId, pOrgId,lItmBillId);
385 if lStatus = 1 then
386 IF PG_DEBUG <> 0 THEN
387 oe_debug_pub.add('create_bom_ml: ' || ' Config BOM ' || lItmBillId || ' Already exists ' ,1);
388 END IF;
389 return (1);
390 end if;
391
392 /*-------------------------------------------+
393 BOM does not exist, so we need to create it
394 get the bill_sequence_id to be used.
395 +--------------------------------------------*/
396 lStmtNum := 20;
397
398
399 /* BUG #1957336 Change for preconfigure bom */
400
401 select ato_line_id, program_id , header_id
402 into v_ato_line_id, v_program_id , v_header_id
403 from bom_cto_order_lines
404 where line_id = pLineId ;
405
406
407 /* BUG #1957336 Change for preconfigure bom */
408
409 if( v_program_id = CTO_UTILITY_PK.PC_BOM_PROGRAM_ID AND
410 v_ato_line_id = pLineId and pOrgId = CTO_UTILITY_PK.PC_BOM_CURRENT_ORG ) then
411
412 lConfigBillId := CTO_UTILITY_PK.PC_BOM_BILL_SEQUENCE_ID ;
413
414 oe_debug_pub.add('create_bom_ml: ' || 'Setting Bill ' || lConfigBillId || ' for org ' || pOrgId
415 || ' for line id ' || pLineId , 1);
416 else
417
418 select bom_inventory_components_s.nextval
419 into lConfigBillId
420 from dual;
421
422 end if ;
423
424 xBillId := lConfigBillId;
425
426 IF PG_DEBUG <> 0 THEN
427 oe_debug_pub.add('create_bom_ml: ' || 'Creating Bill ' || lConfigBillId, 1);
428 END IF;
429
430
431
432 /* Added for avoiding bad data during preconfig bom scenario or any data that could interfere with bom creation */
433 delete from bom_inventory_comps_interface where bill_sequence_id = lConfigBillId ;
434
435
436 IF PG_DEBUG <> 0 THEN
437 oe_debug_pub.add('create_bom_ml: ' || 'deleted from bici ' || to_char(sql%rowcount) , 1);
438 END IF;
439
440
441
442
443
444
445 -- Start Bugfix 1912376
446
447 /*-------------------------------------------+
448 Selecting Schedule_ship_date of ATO Model and assigning
449 this to a Global variable
450 +--------------------------------------------*/
451 lStmtNum := 21;
452
453 select nvl(schedule_ship_date,sysdate)
454 into g_SchShpDate
455 from bom_cto_order_lines
456 where line_id = pLineId ;
457 -- and ship_from_org_id = pOrgId ** bugfix 2312199 **
458 /* commented line as part of bugfix 2312199, the bug 1912376 was not fixed
459 ** properly, the bugfix will not work in case of sourced lower level models
460 ** and hence this line needs to be commented as part of bug 2312199
461 */
462
463
464 IF PG_DEBUG <> 0 THEN
465 oe_debug_pub.add('create_bom_ml: ' || 'Line Id ' || pLineId ||' has Schedule Ship Date of '||g_SchShpDate, 2);
466 END IF;
467
468 -- End Bugfix 1912376
469
470 -- Bugfix 1912376 : Change the position of lead time calculation
471
472 -- New Estimated Release Date for Multilevel ATO
473 lStmtNum := 40;
474
475 -- get oevalidation org
476 IF PG_DEBUG <> 0 THEN
477 oe_debug_pub.add('create_bom_ml: ' || 'Before getting validation org', 2);
478 END IF;
479
480
481 /* BUG #1957336 Change for preconfigure bom */
482
483 if( v_program_id = CTO_UTILITY_PK.PC_BOM_PROGRAM_ID ) then
484 lOEValidationOrg := CTO_UTILITY_PK.PC_BOM_VALIDATION_ORG ;
485 else
486 /*
487 BUG:3484511
488 -----------
489 select nvl(master_organization_id,-99) --bugfix 2646849: master_organization_id can be 0
490 into lOEValidationOrg
491 from oe_order_lines_all oel,
492 oe_system_parameters_all ospa
493 where oel.line_id = pLineid
497
494 and nvl(oel.org_id, -1) = nvl(ospa.org_id, -1) --bug 1531691
495 and oel.inventory_item_id = pModelId;
496 */
498
499 IF PG_DEBUG <> 0 THEN
500 oe_debug_pub.add('create_bom_ml: ' || 'Going to fetch Validation Org ' ,2);
501 END IF;
502
503
504 select nvl( oe_sys_parameters.value( 'MASTER_ORGANIZATION_ID' , oel.org_id) , -99)
505 into lOEValidationOrg from oe_order_lines_all oel
506 where oel.line_id = pLineId ;
507
508
509 end if ;
510
511 if (lOEValidationOrg = -99) then --bugfix 2646849
512 cto_msg_pub.cto_message('BOM','CTO_VALIDATION_ORG_NOT_SET');
513 raise FND_API.G_EXC_ERROR;
514 end if;
515
516 IF PG_DEBUG <> 0 THEN
517 oe_debug_pub.add('create_bom_ml: ' || 'Validation Org is :' || lOEValidationOrg,2);
518 END IF;
519
520 lStmtNum := 41;
521
522 loop
523 select bcol.line_id, bcol.inventory_item_id, bcol.parent_ato_line_id,
524 bcol.ordered_quantity
525 into lLineId, lModelId, lParentAtoLineId, lOrderedQty
526 from bom_cto_order_lines bcol
527 where bcol.line_id = lParentAtoLineId;
528
529 IF PG_DEBUG <> 0 THEN
530 oe_debug_pub.add('create_bom_ml: ' || 'lLineId: ' || to_char(lLineId), 2);
531 oe_debug_pub.add('create_bom_ml: ' || 'lModelId: ' || to_char(lModelId), 2);
532 oe_debug_pub.add('create_bom_ml: ' || 'lParentAtoLineId: ' || to_char(lParentAtoLineId), 2);
533 END IF;
534
535 lStmtNum := 42;
536 lStatus := get_model_lead_time(
537 lModelId,
538 lOEValidationOrg,
539 lOrderedQty,
540 lLeadTime,
541 lErrBuf);
542
543 if (lStatus = 0) then
544 IF PG_DEBUG <> 0 THEN
545 oe_debug_pub.add('create_bom_ml: ' || 'Failed in get_model_lead_time. Error Buffer : '||lERrBuf, 1);
546 END IF;
547 raise FND_API.G_EXC_ERROR;
548 else
549 lTotLeadTime := lLeadTime + lTotLeadTime;
550 end if;
551
552 exit when lLineId = lParentAtoLineId; -- when we reach the top model
553 end loop;
554
555 IF PG_DEBUG <> 0 THEN
556 oe_debug_pub.add('create_bom_ml: ' || 'Total lead time is: ' || to_char(lTotLeadTime), 1);
557 END IF;
558
559 xTableName := 'OE_ORDER_LINES ';
560 lStmtNum := 43;
561
562 begin --Bugfix 2374246
563 select CAL.CALENDAR_DATE
564 into lEstRelDate
565 from bom_calendar_dates cal,
566 mtl_system_items msi,
567 bom_cto_order_lines bcol,
568 mtl_parameters mp
569 where msi.organization_id = pOrgId
570 and msi.inventory_item_id = pModelId
571 and bcol.line_id = pLineId
572 and bcol.inventory_item_id = msi.inventory_item_id
573 and mp.organization_id = msi.organization_id
574 and cal.calendar_code = mp.calendar_code
575 and cal.exception_set_id = mp.calendar_exception_set_id
576 and cal.seq_num =
577 (select cal2.prior_seq_num - lTotLeadTime
578 from bom_calendar_dates cal2
579 where cal2.calendar_code = mp.calendar_code
580 and cal2.exception_set_id = mp.calendar_exception_set_id
581 and cal2.calendar_date = trunc(bcol.schedule_ship_date));
582 -- Bugfix 2374246
583 exception
584 when no_data_found then
585 xErrorMessage := ' Error in calculating Estimated Release date ';
586 xMessageName := 'CTO_NO_CALENDAR';
587 IF PG_DEBUG <> 0 THEN
588 oe_debug_pub.add('create_bom_ml: ' || 'Error in stmt # ' || lStmtNum ||' : '|| xErrorMessage, 1);
589 END IF;
590 return(0);
591 end;
592 -- Bugfix 2374246
593
594 IF PG_DEBUG <> 0 THEN
595 oe_debug_pub.add ('create_bom_ml: ' || 'Estimated Release Date is : ' || lEstRelDate, 2);
596 END IF;
597 g_EstRelDate := lEstRelDate; -- 2222518
598 IF PG_DEBUG <> 0 THEN
599 oe_debug_pub.add ('create_bom_ml: ' || 'Global Estimated Release Date is : ' || g_EstRelDate, 2);
600 END IF; -- 2222518
601
602 -- b2307936
603 /*---------------------------------------------------------------------------+
604 In new code , we will check op seq profile before insert into bic interface.
605 If op seq = 1 , we will insert into bet and then to bic interface
606 If op seq != 1 , we will do direct insert into bic interface
607 +----------------------------------------------------------------------------*/
608 /*-------------------------------------------------------------------------+
609 Check profile option 'Inherit Operation_sequence_number'. If it is set
610 to 'Yes', ensure that the childern default the operation sequence number
611 from its parent, if not already assigned.
612 Open : As in prev releases, this does not cover non-ATPable SMCs because
613 they are not in oe_order_lines. Do we need to ?
614 +--------------------------------------------------------------------------*/
615
616 lOpseqProfile := FND_PROFILE.Value('BOM:CONFIG_INHERIT_OP_SEQ');
617
618 IF PG_DEBUG <> 0 THEN
622 lStmtNum := 80;
619 oe_debug_pub.add ('create_bom_ml: ' || 'Config_inherit_op_seq is ' || lOpseqProfile, 2);
620 END IF;
621
623 if lOpseqProfile = 1 then
624 IF PG_DEBUG <> 0 THEN
625 oe_debug_pub.add('create_bom_ml: ' || 'Calling inherit_op_seq_ml with line id ' ||
626 to_char(pLineId) || ' in org ' ||
627 to_char(pOrgId), 1);
628 END IF;
629 lStatus := inherit_op_seq_ml(pLineId, pOrgId,pModelId,lConfigBillId,xErrorMessage,xMessageName);
630 if lStatus <> 1 then
631 IF PG_DEBUG <> 0 THEN
632 oe_debug_pub.add('create_bom_ml: ' || 'Failed in inherit_op_seq for line id: '|| to_char(pLineId), 1);
633 END IF;
634 return(0);
635 end if;
636 else
637 -- e2307936
638
639 /*-------------------------------------------+
640 Load inventory components interface table
641 +--------------------------------------------*/
642
643 /*-----------------------------------------------------------+
644 First:
645 All the chosen option items/models/Classes associated
646 with the new configuration items will be loaded into the
647 BOM_INVENTORY_COMPS_INTERFACE table.
648 +-------------------------------------------------------------*/
649
650 xTableName := 'BOM_INVENTORY_COMPS_INTERFACE';
651 lStmtNum := 30;
652
653 -- rkaza. bug 4524248. bom structure import enhancements. Added batch_id
654
655 insert into BOM_INVENTORY_COMPS_INTERFACE
656 (
657 operation_seq_num,
658 component_item_id,
659 last_update_date,
660 last_updated_by,
661 creation_date,
662 created_by,
663 last_update_login,
664 item_num,
665 component_quantity,
666 component_yield_factor,
667 component_remarks,
668 effectivity_date,
669 change_notice,
670 implementation_date,
671 disable_date,
672 attribute_category,
673 attribute1,
674 attribute2,
675 attribute3,
676 attribute4,
677 attribute5,
678 attribute6,
679 attribute7,
680 attribute8,
681 attribute9,
682 attribute10,
683 attribute11,
684 attribute12,
685 attribute13,
686 attribute14,
687 attribute15,
688 planning_factor,
689 quantity_related,
690 so_basis,
691 optional,
692 mutually_exclusive_options,
693 include_in_cost_rollup,
694 check_atp,
695 shipping_allowed,
696 required_to_ship,
697 required_for_revenue,
698 include_on_ship_docs,
699 include_on_bill_docs,
700 low_quantity,
701 high_quantity,
702 acd_type,
703 old_component_sequence_id,
704 component_sequence_id,
705 bill_sequence_id,
706 request_id,
707 program_application_id,
708 program_id,
709 program_update_date,
710 wip_supply_type,
711 pick_components,
712 model_comp_seq_id,
713 supply_subinventory,
714 supply_locator_id,
715 bom_item_type,
716 optional_on_model, -- New columns for configuration
717 parent_bill_seq_id, -- BOM restructure project
718 plan_level -- Used by CTO only
719 ,revised_item_sequence_id /* 2524562 : New column added to store line_id */
720 ,Assembly_item_id /* Bug fix: 4147224 */
721 , basis_type, /* LBM project */
722 batch_id
723 )
724 select
725 nvl(ic1.operation_seq_num,1),
726 decode(bcol1.config_item_id, NULL, ic1.component_item_id, -- new
727 bcol1.config_item_id),
728 SYSDATE, -- last_updated_date
729 1, -- last_updated_by
730 SYSDATE, -- creation_date
731 1, -- created_by
732 1, -- last_update_login
733 ic1.item_num,
734 Round(
735 CTO_UTILITY_PK.convert_uom( bcol1.order_quantity_uom, msi_child.primary_uom_code, bcol1.ordered_quantity , msi_child.inventory_item_id )
736 / CTO_UTILITY_PK.convert_uom(bcol2.order_quantity_uom, msi_parent.primary_uom_code, NVL(bcol2.ordered_quantity,1) , msi_parent.inventory_item_id )
737 , 7) , -- qty = comp_qty / model_qty /* Decimal-Qty Support for Option Items */
738 ic1.component_yield_factor,
739 ic1.component_remarks, --Bugfix 7188428
740 --NULL, --ic1.component_remark
741 -- 3222932 TRUNC(SYSDATE), -- effective date
742 -- 3222932 If eff_date > sysdate , insert eff_Date else insert sysdate
743 decode(
744 greatest(ic1.effectivity_date,sysdate),
745 ic1.effectivity_date ,
746 ic1.effectivity_date ,
747 sysdate ),
748 NULL, -- change notice
749 SYSDATE, -- implementation_date
750 -- 3222932 NULL, -- disable date
751 nvl(ic1.disable_date,g_futuredate), -- 3222932
752 ic1.attribute_category,
753 ic1.attribute1,
757 ic1.attribute5,
754 ic1.attribute2,
755 ic1.attribute3,
756 ic1.attribute4,
758 ic1.attribute6,
759 ic1.attribute7,
760 ic1.attribute8,
761 ic1.attribute9,
762 ic1.attribute10,
763 ic1.attribute11,
764 ic1.attribute12,
765 ic1.attribute13,
766 ic1.attribute14,
767 ic1.attribute15,
768 100, -- planning_factor */
769 2, -- quantity_related */
770 decode(bcol1.config_item_id, NULL,
771 decode(ic1.bom_item_type,4,ic1.so_basis,2),
772 2), -- so_basis */
773 2, -- optional */
774 2, -- mutually_exclusive_options */
775 decode(bcol1.config_item_id, NULL,
776 decode(ic1.bom_item_type,4, ic1.include_in_cost_rollup, 2),
777 1), -- Cost_rollup */
778 decode(bcol1.config_item_id, NULL, decode(ic1.bom_item_type,4, ic1.check_atp, 2),
779 2), -- check_atp */
780 2, -- shipping_allowed = NO */
781 2, -- required_to_ship = NO */
782 ic1.required_for_revenue,
783 ic1.include_on_ship_docs,
784 ic1.include_on_bill_docs,
785 NULL, -- low_quantity */
786 NULL, -- high_quantity */
787 NULL, -- acd_type */
788 NULL, --old_component_sequence_id */
789 bom_inventory_components_s.nextval, -- component sequence id */
790 lConfigBillId, -- bill sequence id */
791 NULL, -- request_id */
792 NULL, -- program_application_id */
793 NULL, -- program_id */
794 NULL, -- program_update_date */
795 ic1.wip_supply_type,
796 2, -- pick_components = NO */
797 decode(bcol1.config_item_id, NULL, (-1)*ic1.component_sequence_id, ic1.component_sequence_id), -- saved model comp seq for later use. If config item, then saved model comp seq id as positive, otherwise negative.
798 ic1.supply_subinventory,
799 ic1.supply_locator_id,
800 --ic1.bom_item_type
801 decode(bcol1.config_item_id, NULL, ic1.bom_item_type, 4), -- new
802 1, --optional_on_model,
803 ic1.bill_sequence_id, --parent_bill_seq_id,
804 (bcol1.plan_level-bcol2.plan_level) --plan_level
805 ,bcol1.line_id /* 2524562 Storing line_id */
806 ,bcol3.inventory_item_id /* Bug fix: 4863055 */
807 , nvl(ic1.basis_type,1), /* LBM project */
808 cto_msutil_pub.bom_batch_id
809 from
810 bom_inventory_components ic1,
811 bom_cto_order_lines bcol1, --Option
812 bom_cto_order_lines bcol2, -- Parent-Model
813 bom_cto_order_lines bcol3, -- Parent-component
814 mtl_system_items msi_child ,
815 mtl_system_items msi_parent
816 -- begin bugfix 1653881
817 where ic1.bill_sequence_id = ( -- this we find the assembly to which
818 select common_bill_sequence_id -- d1.component_seq_id belongs and then find
819 from bom_bill_of_materials bbm -- bill for it in Mfg org.We find equivalent
820 where organization_id = pOrgId -- compnent in this bill by joining
821 and alternate_bom_designator is null -- on component_item_id. Each component
822 and assembly_item_id =( --is assumed to be used at one operation only
823 select distinct assembly_item_id -- Operation_Seq_num must be same in bills in
824 from bom_bill_of_materials bbm1, -- all organizations for that assembly
825 bom_inventory_components bic1
826 where bbm1.common_bill_sequence_id = bic1.bill_sequence_id
827 and component_sequence_id = bcol1.component_sequence_id
828 and bbm1.assembly_item_id = bcol3.inventory_item_id ))
829 and ic1.component_item_id = bcol1.inventory_item_id
830 and msi_child.inventory_item_id = bcol1.inventory_item_id
831 and msi_child.organization_id = pOrgId
832 and msi_parent.inventory_item_id = bcol2.inventory_item_id
833 and msi_parent.organization_id = pOrgId
834 -- end bugfix 1653881
835 -- begin bugfix 1912376
836 -- and ic1.effectivity_date <= g_SchShpDate /* New Approach for Effectivity Dates */
837 and ic1.implementation_date is not null --bug 4122212
838 -- and NVL(ic1.disable_date, (lEstRelDate + 1)) >= greatest( nvl( lEstRelDate, sysdate ) , sysdate ) /* bug #3389846 */
839 -- end bugfix 1912376
840 and ( ic1.disable_date is null or
841 (ic1.disable_date is not null and ic1.disable_date >= sysdate )) /* New Approach for Effectivity Dates */
842 and (( ic1.optional = 1 and ic1.bom_item_type = 4)
843 or
844 ( ic1.bom_item_type in (1,2)))
845 and bcol1.ordered_quantity <> 0
846 and bcol1.line_id <> bcol2.line_id -- not the top ato model
847 and bcol1.parent_ato_line_id = bcol2.line_id
851 and bcol2.ship_from_org_id = bcol1.ship_from_org_id
848 and bcol1.parent_ato_line_id is not null
849 and bcol1.link_to_line_id is not null
850 and bcol2.line_id = pLineId
852 and (bcol3.parent_ato_line_id = bcol1.parent_ato_line_id
853 or
854 bcol3.line_id = bcol1.parent_ato_line_id)
855 -- new condition to include parent model
856 -- in a sub-assy since its
857 -- ato_line_id is not equal
858 -- to itself, unlike a top
859 -- model.
860 and bcol3.line_id = bcol1.link_to_line_id;
861
862
863 lCnt := sql%rowcount ;
864 IF PG_DEBUG <> 0 THEN
865 oe_debug_pub.add ('create_bom_ml: ' || 'First -- Inserted ' || lCnt ||' rows into BOM_INVENTORY_COMPS_INTERFACE.',1);
866 END IF;
867
868
869
870
871 select count(*) into v_zero_qty_count from bom_inventory_comps_interface
872 where bill_sequence_id = lConfigBillId and component_quantity = 0 ;
873
874
875 oe_debug_pub.add( 'MODELS: CHECK Raise Exception for Zero QTY Count ' || v_zero_qty_count , 1 ) ;
876
877 if( v_zero_qty_count > 0 ) then
878
879 oe_debug_pub.add( 'SHOULD Raise Exception for Zero QTY Count ' || v_zero_qty_count , 1 ) ;
880
881 select concatenated_segments into v_model_item_name
882 from mtl_system_items_kfv
883 where inventory_item_id = pModelId
884 and rownum = 1 ;
885
886
887 l_token1(1).token_name := 'MODEL_NAME';
888 l_token1(1).token_value := v_model_item_name ;
889
890
891 cto_msg_pub.cto_message('BOM','CTO_ZERO_BOM_COMP', l_token1 );
892
893 raise fnd_api.g_exc_error;
894
895
896 /* Please incorporate raising exception */
897
898
899 end if ;
900
901
902
903
904
905
906
907 -- Remove fix of 2524562 from here to fix 2814257
908
909 /*---------------------------------------------------------------+
910 Second:
911 All the standard component items associated
912 with the new configuration items will be loaded into the
913 BOM_INVENTORY_COMPS_INTERFACE table.
914 +----------------------------------------------------------------*/
915
916 lStmtNum := 50;
917 xTableName := 'BOM_INVENTORY_COMPS_INTERFACE';
918 insert into BOM_INVENTORY_COMPS_INTERFACE
919 (
920 operation_seq_num,
921 component_item_id,
922 last_update_date,
923 last_updated_by,
924 creation_date,
925 created_by,
926 last_update_login,
927 item_num,
928 component_quantity,
929 component_yield_factor,
930 component_remarks,
931 effectivity_date,
932 change_notice,
933 implementation_date,
934 disable_date,
935 attribute_category,
936 attribute1,
937 attribute2,
938 attribute3,
939 attribute4,
940 attribute5,
941 attribute6,
942 attribute7,
943 attribute8,
944 attribute9,
945 attribute10,
946 attribute11,
947 attribute12,
948 attribute13,
949 attribute14,
950 attribute15,
951 planning_factor,
952 quantity_related,
953 so_basis,
954 optional,
955 mutually_exclusive_options,
956 include_in_cost_rollup,
957 check_atp,
958 shipping_allowed,
959 required_to_ship,
960 required_for_revenue,
961 include_on_ship_docs,
962 include_on_bill_docs,
963 low_quantity,
964 high_quantity,
965 acd_type,
966 old_component_sequence_id,
967 component_sequence_id,
968 bill_sequence_id,
969 request_id,
970 program_application_id,
971 program_id,
972 program_update_date,
973 wip_supply_type,
974 pick_components,
975 model_comp_seq_id,
976 supply_subinventory,
977 supply_locator_id,
978 bom_item_type,
979 optional_on_model, -- New columns for configuration
980 parent_bill_seq_id, -- BOM restructure project.
981 plan_level -- Used by CTO only.
982 , basis_type, /* LBM project */
983 batch_id
984 )
985 select
986 nvl(ic1.operation_seq_num,1),
987 ic1.component_item_id,
988 SYSDATE, -- last_updated_date
989 1, -- last_updated_by
990 SYSDATE, -- creation_date
991 1, -- created_by
992 1, -- last_update_login
993 ic1.item_num,
994 decode( nvl(ic1.basis_type,1), 1 , Round( ( ic1.component_quantity * ( bcol1.ordered_quantity
995 / bcol2.ordered_quantity)), 7 ) , Round(ic1.component_quantity , 7 ) ) , /* Decimal-Qty Support for Option Items, LBM project */
996 ic1.component_yield_factor,
997 ic1.component_remarks, --Bugfix 7188428
998 --NULL, -- ic1.component_remark
999 -- 3222932 TRUNC(SYSDATE), -- effective date
1000 decode( -- 3222932
1004 sysdate ),
1001 greatest(ic1.effectivity_date,sysdate),
1002 ic1.effectivity_date ,
1003 ic1.effectivity_date ,
1005 NULL, -- change notice
1006 SYSDATE, -- implementation_date
1007 -- 3222932 NULL, -- disable date
1008 nvl(ic1.disable_date,g_futuredate), -- 3222932
1009 ic1.attribute_category,
1010 ic1.attribute1,
1011 ic1.attribute2,
1012 ic1.attribute3,
1013 ic1.attribute4,
1014 ic1.attribute5,
1015 ic1.attribute6,
1016 ic1.attribute7,
1017 ic1.attribute8,
1018 ic1.attribute9,
1019 ic1.attribute10,
1020 ic1.attribute11,
1021 ic1.attribute12,
1022 ic1.attribute13,
1023 ic1.attribute14,
1024 ic1.attribute15,
1025 100, -- planning_factor
1026 2, -- quantity_related
1027 ic1.so_basis,
1028 2, -- optional
1029 2, -- mutually_exclusive_options
1030 ic1.include_in_cost_rollup,
1031 ic1.check_atp,
1032 2, -- shipping_allowed = NO
1033 2, -- required_to_ship = NO
1034 ic1.required_for_revenue,
1035 ic1.include_on_ship_docs,
1036 ic1.include_on_bill_docs,
1037 NULL, -- low_quantity
1038 NULL, -- high_quantity
1039 NULL, -- acd_type
1040 NULL, -- old_component_sequence_id
1041 bom_inventory_components_s.nextval, -- component sequence id
1042 lConfigBillId, -- bill sequence id
1043 NULL, -- request_id
1044 NULL, -- program_application_id
1045 NULL, -- program_id
1046 NULL, -- program_update_date
1047 ic1.wip_supply_type,
1048 2, -- pick_components = NO
1049 (-1)*ic1.component_sequence_id, -- model comp seq for later use
1050 ic1.supply_subinventory,
1051 ic1.supply_locator_id,
1052 ic1.bom_item_type,
1053 2, --optional_on_model,
1054 ic1.bill_sequence_id, --parent_bill_seq_id,
1055 bcol1.plan_level+1-bcol2.plan_level --plan_level
1056 , nvl(ic1.basis_type,1), /* LBM project */
1057 cto_msutil_pub.bom_batch_id
1058 from
1059 bom_cto_order_lines bcol1, -- component
1060 bom_cto_order_lines bcol2, -- Model
1061 mtl_system_items si1,
1062 mtl_system_items si2,
1063 bom_bill_of_materials b,
1064 bom_inventory_components ic1
1065 where si1.organization_id = pOrgId
1066 and bcol1.inventory_item_id = si1.inventory_item_id
1067 and si1.bom_item_type in (1,2) -- model, option class
1068 and si2.inventory_item_id = bcol2.inventory_item_id
1069 and si2.organization_id = si1.organization_id
1070 and si2.bom_item_type = 1
1071 and ((bcol1.parent_ato_line_id = bcol2.line_id
1072 -- bugfix 2215274: replaced bind variable with column join to improve performance.
1073 and ( bcol1.bom_item_type <> 1
1074 or
1075 (bcol1.bom_item_type = 1 and nvl(bcol1.wip_supply_type, 0) = 6))
1076 )
1077 or bcol1.line_id = bcol2.line_id
1078 ) -- new condition to get the parent itself
1079 -- bugfix 2215274: replaced bind variable with column join to improve performance.
1080 and bcol2.line_id = pLineId
1081 and si1.organization_id = b.organization_id
1082 and bcol1.inventory_item_id = b.assembly_item_id
1083 and b.alternate_bom_designator is NULL
1084 and b.common_bill_sequence_id = ic1.bill_sequence_id
1085 and ic1.optional = 2 -- optional = no
1086 -- inserted code for checking bugfix 1522647
1087 -- and ic1.effectivity_date <= greatest( NVL(lEstRelDate,sysdate),sysdate)
1088 -- begin bugfix 1912376
1089 -- and ic1.effectivity_date <= greatest( NVL(g_SchShpDate,sysdate),sysdate) /* New approach for effectivity dates */
1090 -- end bugfix 1912376
1091 and ic1.implementation_date is not null
1092 -- and NVL(ic1.disable_date,NVL(lEstRelDate, SYSDATE)+1) > NVL(lEstRelDate,SYSDATE) /* NEW approach for effectivity */
1093 -- and NVL(ic1.disable_date,SYSDATE) >= SYSDATE /* New approach for effectivity */
1094 and ( ic1.disable_date is null or
1095 (ic1.disable_date is not null and ic1.disable_date >= sysdate )) /* New Approach for Effectivity Dates */
1096 -- code of bugfix 1522647 ends here
1097 and ic1.bom_item_type = 4;
1098
1099 lCnt := sql%rowcount ;
1100
1101 IF PG_DEBUG <> 0 THEN
1102 oe_debug_pub.add ('create_bom_ml: ' || 'Second -- Inserted ' || lCnt ||' rows',1);
1103 END IF;
1104
1105 end if; /* end of check lOpseqProfile = 1 */
1106
1107
1108 /* begin Extend Effectivity Dates for Option Items with disable date */
1109
1110 oe_debug_pub.add('create_bom_ml:: Config bill id = '||lconfigbillid,1);
1111
1112 For debug_rec in debug_cur
1113 Loop
1117 oe_debug_pub.add('create_bom_ml: : MAxDisbale Date = '||debug_rec.disable_date,1);
1114 oe_debug_pub.add('create_bom_ml: : Assembly_item_id = '||debug_rec.assembly_item_id,1);
1115 oe_debug_pub.add('create_bom_ml: : Componenet_item_id = '||debug_rec.component_item_id,1);
1116 oe_debug_pub.add('create_bom_ml: : operation_sequence_num = '||debug_rec.operation_seq_num,1);
1118 oe_debug_pub.add('==================================',1);
1119 End Loop;
1120
1121 -- Modified by Renga Kannan on 01/10/06
1122 -- The logic to find the last window for option item and mandatory comps
1123 -- are little different.
1124 -- For option items, identify the last window under a parent(option class) accross
1125 -- all operating sequence
1126 -- For Mandatory items, identify the last window across all parents and across all
1127 -- operating sequence.
1128 -- Mandatory comps row will have assembly_item_id as null
1129 -- option items row will have assembly_item_id populated
1130
1131 update bom_inventory_comps_interface
1132 set disable_date = g_futuredate
1133 where (component_item_id, nvl(assembly_item_id,-1),disable_date)
1134 in ( select
1135 component_item_id,nvl(assembly_item_id,-1),max(disable_date)
1136 from bom_inventory_comps_interface
1137 where bill_sequence_id = lConfigBillId
1138 group by component_item_id, assembly_item_id
1139 )
1140 and bill_sequence_id = lConfigBillId
1141 and disable_date <> g_futuredate ;
1142
1143 If PG_DEBUG <> 0 Then
1144 oe_debug_pub.add('Create_bom_ml: Extending the disable dates to futuure date = '||sql%rowcount,1);
1145 oe_debug_pub.add('Create_bom_ml: lconfigBillId = '||to_char(lConfigBillid),1);
1146 End if;
1147
1148
1149
1150 /* end Extend Effectivity Dates for Option Items with disable date */
1151
1152 /* New approach for effectivity dates */
1153 /* begin Check for Overlapping Effectivity Dates */
1154 v_overlap_check := 0 ;
1155
1156 begin
1157 select 1 into v_overlap_check
1158 from dual
1159 where exists
1160 ( select * from bom_inventory_comps_interface
1161 where bill_sequence_id = lConfigBillId
1162 group by component_item_id, assembly_item_id
1163 having count(distinct operation_seq_num) > 1
1164 );
1165 exception
1166 when others then
1167 v_overlap_check := 0 ;
1168 end;
1169 oe_debug_pub.add(' Overlap check = '||v_overlap_check,1);
1170
1171 if(v_overlap_check = 1) then
1172
1173 for debug_cur2 in debug_cur1
1174 Loop
1175 oe_debug_pub.add(debug_cur2.component_item_id||'-'||debug_cur2.component_sequence_id||'-'||
1176 debug_cur2.operation_seq_num||'-'||to_char(debug_cur2.effectivity_date)
1177 ||'-'||to_char(debug_cur2.disable_date),1);
1178
1179 end loop;
1180
1181 begin
1182 select s1.component_item_id,
1183 s1.operation_seq_num, s1.effectivity_date, s1.disable_date,
1184 s2.operation_Seq_num , s2.effectivity_date, s2.disable_date
1185 BULK COLLECT INTO
1186 v_t_overlap_comp_item_id,
1187 v_t_overlap_src_op_seq_num, v_t_overlap_src_eff_date, v_t_overlap_src_disable_date ,
1188 v_t_overlap_dest_op_seq_num , v_t_overlap_dest_eff_date, v_t_overlap_dest_disable_date
1189 from bom_inventory_comps_interface s1 , bom_inventory_comps_interface s2
1190 where s1.component_item_id = s2.component_item_id and s1.assembly_item_id = s2.assembly_item_id
1191 --and s1.effectivity_date between s2.effectivity_date and s2.disable_date
1192 and s1.effectivity_date > s2.effectivity_date --Bugfix 6603382
1193 and s1.effectivity_date < s2.disable_date --Bugfix 6603382
1194 and s1.bill_sequence_id = lConfigBillId --Bugfix 6603382
1195 and s2.bill_sequence_id = lConfigBillId --Bugfix 6603382
1196 and s1.component_sequence_id <> s2.component_sequence_id ;
1197
1198
1199 exception
1200 when others then
1201 null ;
1202 end ;
1203 oe_debug_pub.add('Over lap record count = '||v_t_overlap_src_op_seq_num.count,1);
1204
1205 if( v_t_overlap_src_op_seq_num.count > 0 ) then
1206 for i in v_t_overlap_src_op_seq_num.first..v_t_overlap_src_op_seq_num.last
1207 loop
1208 IF PG_DEBUG <> 0 THEN
1209 oe_debug_pub.add (' The following components have overlapping dates ', 1);
1210 oe_debug_pub.add (' COMP ' || ' OP SEQ' || 'EFFECTIVITY DT ' || ' DISABLE DT ' || ' OVERLAPS ' ||
1211 ' OP SEQ' || 'EFFECTIVITY DT ' || ' DISABLE DT ' , 1);
1212
1213 oe_debug_pub.add ( v_t_overlap_comp_item_id(i) ||
1214 ' ' || v_t_overlap_src_op_seq_num(i) ||
1215 ' ' || v_t_overlap_src_eff_date(i) ||
1216 ' ' || v_t_overlap_src_disable_date(i) ||
1217 ' OVERLAPS ' ||
1218 ' ' || v_t_overlap_src_op_seq_num(i) ||
1219 ' ' || v_t_overlap_src_eff_date(i) ||
1220 ' ' || v_t_overlap_src_disable_date(i) , 1);
1221
1222 END IF;
1223 select segment1
1224 into
1225 l_model_name
1226 from mtl_system_items
1227 where inventory_item_id=pModelId
1228 and rownum=1;
1229
1233
1230 l_token2(1).token_name :='MODEL';
1231 l_token2(1).token_value :=l_model_name;
1232 cto_msg_pub.cto_message('BOM','CTO_OVERLAP_DATE_ERROR',l_token2);
1234 end loop ;
1235
1236 raise fnd_api.g_exc_error;
1237
1238 end if ;
1239
1240 end if;
1241
1242
1243
1244 /* end Check for Overlapping Effectivity Dates */
1245
1246
1247
1248
1249
1250 -- Fix 2814257 : Move fix of 2524562 out of if..then..else to
1251 -- print dropped line info irrespective of opseq profile set up.
1252
1253 /* 2524562 Print dropped line information
1254 in Forms and log files */
1255
1256 -- start fix 2986192
1257
1258 lStmtNum := 51;
1259
1260 BEGIN
1261
1262 lcreate_item := nvl(FND_PROFILE.VALUE('CTO_CONFIG_EXCEPTION'), 1);
1263
1264 IF PG_DEBUG <> 0 THEN
1265 oe_debug_pub.add ('Config exception profile '||lcreate_item, 1);
1266 END IF;
1267
1268 open missed_lines(pLineId, lConfigBillId, lEstRelDate ); /* Effectivity dates change */
1269 loop
1270 fetch missed_lines into v_missed_line_id;
1271 exit when missed_lines%NOTFOUND;
1272
1273 v_option_num := v_option_num + 1 ;
1274
1275
1276 lStmtNum := 52;
1277
1278 BEGIN
1279
1280 IF PG_DEBUG <> 0 THEN
1281 oe_debug_pub.add('Select missed component details.. ' ,1);
1282 END IF;
1283
1284
1285
1286 if( v_program_id = CTO_UTILITY_PK.PC_BOM_PROGRAM_ID ) then
1287 IF PG_DEBUG <> 0 THEN
1288 oe_debug_pub.add('Pre configured Item .. ' ,1);
1289 END IF;
1290
1291 select substrb(msi.concatenated_segments,1,50),
1292 'Not Available' ,
1293 -1
1294 into v_missed_item,v_missed_line_number,v_order_number
1295 from mtl_system_items_kfv msi, bom_cto_order_lines bcol
1296 where msi.organization_id = bcol.ship_from_org_id
1297 and msi.inventory_item_id = bcol.inventory_item_id
1298 and bcol.line_id = v_missed_line_id;
1299
1300 else
1301 IF PG_DEBUG <> 0 THEN
1302 oe_debug_pub.add('Auto configured Item .. ' ,1);
1303 END IF;
1304
1305 select substrb(msi.concatenated_segments,1,50),
1306 to_char(oel.line_number)||'.'||to_char(oel.shipment_number) ||decode(oel.option_number,NULL,NULL,'.'
1307 ||to_char(option_number)),
1308 oeh.order_number
1309 into v_missed_item,v_missed_line_number,v_order_number
1310 from mtl_system_items_kfv msi, oe_order_lines_all oel,oe_order_headers_all oeh
1311 where msi.organization_id = oel.ship_from_org_id
1312 and msi.inventory_item_id = oel.inventory_item_id
1313 and oel.header_id = oeh.header_id
1314 and oel.line_id = v_missed_line_id;
1315
1316
1317
1318 end if ;
1319
1320
1321 lStmtNum := 53;
1322
1323 IF PG_DEBUG <> 0 THEN
1324 oe_debug_pub.add('Select model.. ' ,1);
1325 END IF;
1326
1327 select substrb(concatenated_segments,1,50)
1328 into v_model
1329 from mtl_system_items_kfv
1330 where organization_id = pOrgId
1331 and inventory_item_id = pModelId ;
1332
1333 lStmtNum := 54;
1334
1335 IF PG_DEBUG <> 0 THEN
1336 oe_debug_pub.add('Select Org.. ' ,1);
1337 END IF;
1338
1339 select organization_code
1340 into lOrg_code
1341 from mtl_parameters
1342 where organization_id =pOrgId ;
1343
1344
1345
1346 if ( v_option_num = 1 ) then
1347
1348 v_dropped_item_string := 'Option ' || v_option_num || ': ' || v_missed_item || l_new_line ;
1349
1350 v_ac_message_string := ' Line ' || v_missed_line_number || ' ' || v_dropped_item_string ;
1351
1352 else
1353
1354 v_sub_dropped_item_string := 'Option ' || v_option_num || ': ' || v_missed_item || l_new_line ;
1355 v_dropped_item_string := v_dropped_item_string || v_sub_dropped_item_string ;
1356
1357 v_ac_message_string := v_ac_message_string || ' Line ' || v_missed_line_number || ' ' || v_sub_dropped_item_string ;
1358
1359
1360 end if ;
1361
1362
1363 if ( lcreate_item = 1 ) then
1364
1365 IF PG_DEBUG <> 0 THEN
1366 oe_debug_pub.add ('Warning: The component '||v_missed_item
1367 || ' on Line Number '||v_missed_line_number
1368 || ' in organization ' || lOrg_code
1369 || ' was not included in the configured item''s bill. ',1);
1370 oe_debug_pub.add ('Model Name : '||v_model,1);
1371 oe_debug_pub.add ('Order Number : '||v_order_number,1);
1372
1373
1374
1375
1376
1377 END IF;
1378
1379 /*
1380 l_token(1).token_name := 'OPTION_NAME';
1381 l_token(1).token_value := v_missed_item;
1385 l_token(3).token_value := lOrg_code ;
1382 l_token(2).token_name := 'LINE_ID';
1383 l_token(2).token_value := v_missed_line_number;
1384 l_token(3).token_name := 'ORG_CODE';
1386 l_token(4).token_name := 'MODEL_NAME';
1387 l_token(4).token_value := v_model;
1388 l_token(5).token_name := 'ORDER_NUMBER';
1389 l_token(5).token_value := v_order_number;
1390
1391 cto_msg_pub.cto_message('BOM','CTO_DROP_ITEM_FROM_CONFIG',l_token);
1392
1393 */
1394
1395
1396 else
1397 IF PG_DEBUG <> 0 THEN
1398 oe_debug_pub.add ('Warning: The configured item was not created because component '||v_missed_item
1399 || ' on Line Number '||v_missed_line_number
1400 || ' in organization ' || lOrg_code
1401 || ' could not be included in the configured item''s bill. ',1);
1402 oe_debug_pub.add ('Model Name : '||v_model,1);
1403 oe_debug_pub.add ('Order Number : '||v_order_number,1);
1404 END IF;
1405
1406 /*
1407 l_token(1).token_name := 'OPTION_NAME';
1408 l_token(1).token_value := v_missed_item;
1409 l_token(2).token_name := 'LINE_ID';
1410 l_token(2).token_value := v_missed_line_number;
1411 l_token(3).token_name := 'ORG_CODE';
1412 l_token(3).token_value := lOrg_code ;
1413 l_token(4).token_name := 'MODEL_NAME';
1414 l_token(4).token_value := v_model;
1415 l_token(5).token_name := 'ORDER_NUMBER';
1416 l_token(5).token_value := v_order_number;
1417
1418 cto_msg_pub.cto_message('BOM','CTO_DO_NOT_CREATE_ITEM',l_token);
1419 */
1420
1421 end if;
1422
1423 EXCEPTION -- exception for stmt 52 ,53 and 54
1424
1425 when others then
1426 IF PG_DEBUG <> 0 THEN
1427 oe_debug_pub.add('Others excepn from stmt '||lStmtNum ||':'||sqlerrm);
1428 END IF;
1429 raise fnd_api.g_exc_error;
1430 END ;
1431 end loop;
1432
1433 /* gDropItem is set to 0 . Not resetting this to 1
1434 for next order in the batch since even when items are
1435 dropped for one order in the batch , the whole batch
1436 should end with warning */
1437
1438 if( v_program_id = CTO_UTILITY_PK.PC_BOM_PROGRAM_ID ) then
1439 IF PG_DEBUG <> 0 THEN
1440 oe_debug_pub.add ('Will not go through Hold Logic and Notification as Preconfigured Bom' , 1 );
1441 END IF;
1442
1443 if missed_lines%ROWCOUNT > 0 then
1444 if ( lcreate_item = 1 ) then
1445 IF PG_DEBUG <> 0 THEN
1446 oe_debug_pub.add ('Create Item profile set to Create and Link Item ' , 1 );
1447 END IF;
1448
1449 xMessageName := 'CTO_DROP_ITEM_FROM_CONFIG';
1450
1451
1452
1453
1454 /* DROPPED ITEM CAPTURE PROCESS */
1455
1456 select segment1 into v_problem_model from mtl_system_items
1457 where inventory_item_id = pModelId and rownum = 1 ;
1458
1459 select segment1 into v_problem_config from mtl_system_items
1460 where inventory_item_id = pConfigId and rownum = 1 ;
1461
1462 -- rkaza. bug 3742393. 08/11/2004. Replaced org_organization
1463 -- _deinitions with inv_organization_name_v
1464 select organization_name into v_error_org from inv_organization_name_v
1465 where organization_id = pOrgId ;
1466
1467
1468 v_problem_model_line_num := ' -1 ' ;
1469
1470
1471
1472 v_table_count := g_t_dropped_item_type.count + 1 ;
1473 g_t_dropped_item_type(v_table_count).PROCESS := 'NOTIFY_OID_IC' ; /* ITEM CREATED */
1474 g_t_dropped_item_type(v_table_count).LINE_ID := pLineId ;
1475 g_t_dropped_item_type(v_table_count).SALES_ORDER_NUM := null ;
1476 g_t_dropped_item_type(v_table_count).ERROR_MESSAGE := v_dropped_item_string ;
1477 g_t_dropped_item_type(v_table_count).TOP_MODEL_NAME := null ;
1478 g_t_dropped_item_type(v_table_count).TOP_MODEL_LINE_NUM := null ;
1479 g_t_dropped_item_type(v_table_count).TOP_CONFIG_NAME := null ;
1480 g_t_dropped_item_type(v_table_count).TOP_CONFIG_LINE_NUM := null ;
1481 g_t_dropped_item_type(v_table_count).PROBLEM_MODEL := v_problem_model ;
1482 g_t_dropped_item_type(v_table_count).PROBLEM_MODEL_LINE_NUM := v_problem_model_line_num ;
1483 g_t_dropped_item_type(v_table_count).PROBLEM_CONFIG := v_problem_config ;
1484 g_t_dropped_item_type(v_table_count).ERROR_ORG := v_error_org ;
1485 g_t_dropped_item_type(v_table_count).ERROR_ORG_ID := pOrgId ;
1486 -- g_t_dropped_item_type(v_table_count).MFG_REL_DATE := to_char( lEstRelDate , 'DD-MON-YYYY' ) ;
1487 g_t_dropped_item_type(v_table_count).MFG_REL_DATE := lEstRelDate ;
1488
1489 IF PG_DEBUG <> 0 THEN
1490 oe_debug_pub.add ('CTOCBOMB: REQUEST ID : ' || fnd_global.conc_request_id , 1 );
1491 END IF;
1492
1493 g_t_dropped_item_type(v_table_count).REQUEST_ID := to_char( fnd_global.conc_request_id ) ;
1494
1495 else
1499
1496 IF PG_DEBUG <> 0 THEN
1497 oe_debug_pub.add ('Create Item profile set to Do Not Create Item ' , 1 );
1498 END IF;
1500 xMessageName := 'CTO_DO_NOT_CREATE_ITEM';
1501
1502
1503
1504 /* DROPPED ITEM CAPTURE PROCESS */
1505
1506 select segment1 into v_problem_model from mtl_system_items
1507 where inventory_item_id = pModelId and rownum = 1 ;
1508
1509 select segment1 into v_problem_config from mtl_system_items
1510 where inventory_item_id = pConfigId and rownum = 1 ;
1511
1512 -- rkaza. bug 3742393. 08/11/2004. Replaced org_organization
1513 -- _deinitions with inv_organization_name_v
1514
1515 select organization_name into v_error_org from inv_organization_name_v
1516 where organization_id = pOrgId ;
1517
1518
1519 v_problem_model_line_num := ' -1 ' ;
1520
1521
1522 v_table_count := g_t_dropped_item_type.count + 1 ;
1523 g_t_dropped_item_type(v_table_count).PROCESS := 'NOTIFY_OID_INC' ; /* ITEM NOT CREATED */
1524 g_t_dropped_item_type(v_table_count).LINE_ID := pLineId ;
1525 g_t_dropped_item_type(v_table_count).SALES_ORDER_NUM := null ;
1526 g_t_dropped_item_type(v_table_count).ERROR_MESSAGE := v_dropped_item_string ;
1527 g_t_dropped_item_type(v_table_count).TOP_MODEL_NAME := null ;
1528 g_t_dropped_item_type(v_table_count).TOP_MODEL_LINE_NUM := null ;
1529 g_t_dropped_item_type(v_table_count).TOP_CONFIG_NAME := null ;
1530 g_t_dropped_item_type(v_table_count).TOP_CONFIG_LINE_NUM := null ;
1531 g_t_dropped_item_type(v_table_count).PROBLEM_MODEL := v_problem_model ;
1532 g_t_dropped_item_type(v_table_count).PROBLEM_MODEL_LINE_NUM := v_problem_model_line_num ;
1533 g_t_dropped_item_type(v_table_count).PROBLEM_CONFIG := v_problem_config ;
1534 g_t_dropped_item_type(v_table_count).ERROR_ORG := v_error_org ;
1535 g_t_dropped_item_type(v_table_count).ERROR_ORG_ID := pOrgId ;
1536 g_t_dropped_item_type(v_table_count).MFG_REL_DATE := lEstRelDate ;
1537
1538 IF PG_DEBUG <> 0 THEN
1539 oe_debug_pub.add ('CTOCBOMB: REQUEST ID : ' || fnd_global.conc_request_id , 1 );
1540 END IF;
1541
1542 g_t_dropped_item_type(v_table_count).REQUEST_ID := to_char( fnd_global.conc_request_id ) ;
1543
1544
1545
1546 raise fnd_api.g_exc_error;
1547
1548 end if;
1549
1550 end if;
1551
1552 else
1553 if missed_lines%ROWCOUNT > 0 then
1554 CTO_CONFIG_BOM_PK.gDropItem := 0;
1555
1556
1557
1558
1559
1560
1561 lStmtNum := 55;
1562
1563 if ( lcreate_item = 1 ) then
1564
1565
1566 -- bugfix 2840801 :
1567 -- Set the global variable gApplyHold to apply hold on config line.
1568
1569 IF PG_DEBUG <> 0 THEN
1570 oe_debug_pub.add ('Setting the global var gApplyHold to Y');
1571 END IF;
1572
1573 CTO_CONFIG_BOM_PK.gApplyHold := 'Y';
1574
1575
1576
1577
1578
1579
1580 /* DROPPED ITEM CAPTURE PROCESS */
1581
1582 select segment1 into v_problem_model from mtl_system_items
1583 where inventory_item_id = pModelId and rownum = 1 ;
1584
1585 select segment1 into v_problem_config from mtl_system_items
1586 where inventory_item_id = pConfigId and rownum = 1 ;
1587
1588 -- rkaza. bug 3742393. 08/11/2004. Replaced org_organization
1589 -- _deinitions with inv_organization_name_v
1590
1591 select organization_name into v_error_org from inv_organization_name_v
1592 where organization_id = pOrgId ;
1593
1594
1595
1596
1597 if( v_program_id = CTO_UTILITY_PK.PC_BOM_PROGRAM_ID ) then
1598 v_problem_model_line_num := ' -1 ' ;
1599
1600 else
1601 select oel.line_number || '.' || oel.shipment_number
1602 into v_problem_model_line_num
1603 from oe_order_lines_all oel
1604 where line_id = pLineId ;
1605
1606 end if;
1607
1608 oe_debug_pub.add( ' DROPPED ITEM INFO: ' ||
1609 ' Problem Model ' || v_problem_model ||
1610 ' Problem CONFIG ' || v_problem_config ||
1611 ' ERROR ORG ' || v_error_org ||
1612 ' PROBLEM MODEL LINE NUM ' || v_problem_model_line_num
1613 , 1 ) ;
1614
1615 v_table_count := g_t_dropped_item_type.count + 1 ;
1616 g_t_dropped_item_type(v_table_count).PROCESS := 'NOTIFY_OID_IC' ; /* ITEM CREATED */
1617 g_t_dropped_item_type(v_table_count).LINE_ID := pLineId ;
1618 g_t_dropped_item_type(v_table_count).SALES_ORDER_NUM := null ;
1619 g_t_dropped_item_type(v_table_count).ERROR_MESSAGE := v_dropped_item_string ;
1620 g_t_dropped_item_type(v_table_count).TOP_MODEL_NAME := null ;
1624 g_t_dropped_item_type(v_table_count).PROBLEM_MODEL := v_problem_model ;
1621 g_t_dropped_item_type(v_table_count).TOP_MODEL_LINE_NUM := null ;
1622 g_t_dropped_item_type(v_table_count).TOP_CONFIG_NAME := null ;
1623 g_t_dropped_item_type(v_table_count).TOP_CONFIG_LINE_NUM := null ;
1625 g_t_dropped_item_type(v_table_count).PROBLEM_MODEL_LINE_NUM := v_problem_model_line_num ;
1626 g_t_dropped_item_type(v_table_count).PROBLEM_CONFIG := v_problem_config ;
1627 g_t_dropped_item_type(v_table_count).ERROR_ORG := v_error_org ;
1628 g_t_dropped_item_type(v_table_count).ERROR_ORG_ID := pOrgId ;
1629 g_t_dropped_item_type(v_table_count).MFG_REL_DATE := lEstRelDate ;
1630
1631 IF PG_DEBUG <> 0 THEN
1632 oe_debug_pub.add ('CTOCBOMB: REQUEST ID : ' || fnd_global.conc_request_id , 1 );
1633 END IF;
1634
1635 g_t_dropped_item_type(v_table_count).REQUEST_ID := to_char(fnd_global.conc_request_id) ;
1636
1637
1638
1639
1640 /* IDENTIFY NOTIFY_USER for DROPPED COMPONENT NOTIFICATION */
1641
1642 IF PG_DEBUG <> 0 THEN
1643 oe_debug_pub.add('create_bom_ml: ' || 'Getting Custom Recipient..',3);
1644 END IF;
1645
1646 v_recipient := CTO_CUSTOM_NOTIFY_PK.get_recipient( p_error_type => CTO_UTILITY_PK.OPT_DROP_AND_ITEM_CREATED
1647 ,p_inventory_item_id => pModelId
1648 ,p_organization_id => pOrgId
1649 ,p_line_id => pLineId );
1650
1651
1652
1653
1654 if( v_recipient is not null ) then
1655 IF PG_DEBUG <> 0 THEN
1656 oe_debug_pub.add('create_bom_ml: ' || 'Recipient returned from CTO_CUSTOM_NOTIFY_PK..' || v_recipient ,3);
1657 END IF;
1658
1659 g_t_dropped_item_type(v_table_count).NOTIFY_USER := v_recipient ; /* commented 'MFG' */
1660
1661 else
1662
1663
1664
1665 IF PG_DEBUG <> 0 THEN
1666 oe_debug_pub.add('create_bom_ml: ' || 'Recipient returned from CTO_CUSTOM_NOTIFY_PK is null ..' , 3);
1667 oe_debug_pub.add('create_bom_ml: ' || 'Getting the planner code ..',3);
1668 END IF;
1669
1670 BEGIN
1671 -- bugfix 2203802: Instead of getting the planner code directly from MSI,
1672 -- get the corresponding application user.
1673
1674 SELECT u.user_name
1675 INTO lplanner_code
1676 FROM mtl_system_items_vl item
1677 ,mtl_planners p
1678 ,fnd_user u
1679 WHERE item.inventory_item_id = pModelId
1680 and item.organization_id = pOrgId
1681 and p.organization_id = item.organization_id
1682 and p.planner_code = item.planner_code
1683 and p.employee_id = u.employee_id(+); --outer join b'cos employee need not be an fnd user.
1684
1685
1686 oe_debug_pub.add('create_bom_ml: ' || '****PLANNER CODE DATA' || lplanner_code ,2);
1687
1688
1689 EXCEPTION
1690 WHEN OTHERS THEN
1691 IF PG_DEBUG <> 0 THEN
1692 oe_debug_pub.add('create_bom_ml: ' || 'Error in getting the planner code data. Defaulting to SYSADMIN.',2);
1693
1694 oe_debug_pub.add('create_bom_ml: ' || 'Error Message : '||sqlerrm,2);
1695
1696
1697 END IF;
1698 END;
1699
1700
1701
1702 g_t_dropped_item_type(v_table_count).NOTIFY_USER := lplanner_code ; /* commented 'MFG' */
1703
1704 end if; /* check custom recipient */
1705
1706
1707
1708
1709 l_token(1).token_name := 'ORDER_NUM';
1710 l_token(1).token_value := v_order_number;
1711 l_token(2).token_name := 'MODEL_NAME';
1712 l_token(2).token_value := v_problem_model;
1713 l_token(3).token_name := 'ORG';
1714 l_token(3).token_value := v_error_org;
1715 l_token(4).token_name := 'CONFIG_NAME';
1716 l_token(4).token_value := v_problem_config;
1717 l_token(5).token_name := 'ERROR_MESSAGE';
1718 l_token(5).token_value := v_ac_message_string ;
1719 cto_msg_pub.cto_message('BOM','CTO_AC_DROP_ITEM_FROM_CONFIG',l_token);
1720
1721
1722 else
1723
1724 IF PG_DEBUG <> 0 THEN
1725 oe_debug_pub.add ('Not creating Item...');
1726 END IF;
1727
1728
1729
1730
1731
1732
1733
1734 /* DROPPED ITEM CAPTURE PROCESS */
1735
1736 select segment1 into v_problem_model from mtl_system_items
1737 where inventory_item_id = pModelId and rownum = 1 ;
1738
1739 select segment1 into v_problem_config from mtl_system_items
1740 where inventory_item_id = pConfigId and rownum = 1 ;
1741
1742 -- rkaza. bug 3742393. 08/11/2004. Replaced org_organization
1743 -- _deinitions with inv_organization_name_v
1744 select organization_name into v_error_org from inv_organization_name_v
1745 where organization_id = pOrgId ;
1746
1747
1751 where line_id = pLineId ;
1748 select oel.line_number || '.' || oel.shipment_number
1749 into v_problem_model_line_num
1750 from oe_order_lines_all oel
1752
1753
1754 v_table_count := g_t_dropped_item_type.count + 1 ;
1755 g_t_dropped_item_type(v_table_count).PROCESS := 'NOTIFY_OID_INC' ; /* ITEM NOT CREATED */
1756 g_t_dropped_item_type(v_table_count).LINE_ID := pLineId ;
1757 g_t_dropped_item_type(v_table_count).SALES_ORDER_NUM := null ;
1758 g_t_dropped_item_type(v_table_count).ERROR_MESSAGE := v_dropped_item_string ;
1759 g_t_dropped_item_type(v_table_count).TOP_MODEL_NAME := null ;
1760 g_t_dropped_item_type(v_table_count).TOP_MODEL_LINE_NUM := null ;
1761 g_t_dropped_item_type(v_table_count).TOP_CONFIG_NAME := null ;
1762 g_t_dropped_item_type(v_table_count).TOP_CONFIG_LINE_NUM := null ;
1763 g_t_dropped_item_type(v_table_count).PROBLEM_MODEL := v_problem_model ;
1764 g_t_dropped_item_type(v_table_count).PROBLEM_MODEL_LINE_NUM := v_problem_model_line_num ;
1765 g_t_dropped_item_type(v_table_count).PROBLEM_CONFIG := v_problem_config ;
1766 g_t_dropped_item_type(v_table_count).ERROR_ORG := v_error_org ;
1767 g_t_dropped_item_type(v_table_count).ERROR_ORG_ID := pOrgId ;
1768 g_t_dropped_item_type(v_table_count).MFG_REL_DATE := lEstRelDate ;
1769
1770 IF PG_DEBUG <> 0 THEN
1771 oe_debug_pub.add ('CTOCBOMB: REQUEST ID : ' || fnd_global.conc_request_id , 1 );
1772 END IF;
1773
1774 g_t_dropped_item_type(v_table_count).REQUEST_ID := to_char( fnd_global.conc_request_id ) ;
1775
1776
1777
1778 /* IDENTIFY NOTIFY_USER for DROPPED COMPONENT NOTIFICATION */
1779
1780
1781 IF PG_DEBUG <> 0 THEN
1782 oe_debug_pub.add('create_bom_ml: ' || 'Getting Custom Recipient..',3);
1783 END IF;
1784
1785 v_recipient := CTO_CUSTOM_NOTIFY_PK.get_recipient( p_error_type => CTO_UTILITY_PK.OPT_DROP_AND_ITEM_NOT_CREATED
1786 ,p_inventory_item_id => pModelId
1787 ,p_organization_id => pOrgId
1788 ,p_line_id => pLineId );
1789
1790
1791
1792
1793 if( v_recipient is not null ) then
1794 IF PG_DEBUG <> 0 THEN
1795 oe_debug_pub.add('create_bom_ml: ' || 'Recipient returned from CTO_CUSTOM_NOTIFY_PK..' || v_recipient ,3);
1796 END IF;
1797
1798 g_t_dropped_item_type(v_table_count).NOTIFY_USER := v_recipient ; /* commented 'MFG' */
1799
1800 else
1801
1802
1803
1804
1805
1806 IF PG_DEBUG <> 0 THEN
1807 oe_debug_pub.add('create_bom_ml: ' || 'Recipient returned from CTO_CUSTOM_NOTIFY_PK is null ..' , 3);
1808 oe_debug_pub.add('create_bom_ml: ' || 'Getting the planner code ..',3);
1809 END IF;
1810
1811 BEGIN
1812 -- bugfix 2203802: Instead of getting the planner code directly from MSI,
1813 -- get the corresponding application user.
1814
1815 SELECT u.user_name
1816 INTO lplanner_code
1817 FROM mtl_system_items_vl item
1818 ,mtl_planners p
1819 ,fnd_user u
1820 WHERE item.inventory_item_id = pModelId
1821 and item.organization_id = pOrgId
1822 and p.organization_id = item.organization_id
1823 and p.planner_code = item.planner_code
1824 and p.employee_id = u.employee_id(+); --outer join b'cos employee need not be an fnd user.
1825
1826
1827 oe_debug_pub.add('create_bom_ml: ' || '****PLANNER CODE DATA' || lplanner_code ,2);
1828
1829
1830 EXCEPTION
1831 WHEN OTHERS THEN
1832 IF PG_DEBUG <> 0 THEN
1833 oe_debug_pub.add('create_bom_ml: ' || 'Error in getting the planner code data. Defaulting to SYSADMIN.',2);
1834
1835 oe_debug_pub.add('create_bom_ml: ' || 'Error Message : '||sqlerrm,2);
1836
1837
1838 END IF;
1839 END;
1840
1841
1842
1843 g_t_dropped_item_type(v_table_count).NOTIFY_USER := lplanner_code ; /* commented 'MFG' */
1844
1845 end if; /* check custom recipient */
1846
1847
1848
1849 -- rkaza. bug 4315973. 08/24/2005.
1850 -- Hold ato line for dropped items when profile is set to do not
1851 -- create item. Removed aps_version restriction.
1852
1853 oe_debug_pub.add('create_bom_ml: ' || 'fetching information for apply hold on lineid '|| to_char(pLineId) ,2);
1854 oe_debug_pub.add('create_bom_ml: ' || 'going to apply hold on lineid '|| to_char(pLineId) ,2);
1855
1856 cto_utility_pk.apply_create_config_hold( v_ato_line_id, v_header_id, l_return_status, l_msg_count, l_msg_data ) ;
1857
1858
1862 l_token(2).token_value := v_problem_model;
1859 l_token(1).token_name := 'ORDER_NUM';
1860 l_token(1).token_value := v_order_number;
1861 l_token(2).token_name := 'MODEL_NAME';
1863 l_token(3).token_name := 'ORG';
1864 l_token(3).token_value := v_error_org;
1865 l_token(4).token_name := 'ERROR_MESSAGE';
1866 l_token(4).token_value := v_ac_message_string ;
1867
1868 cto_msg_pub.cto_message('BOM','CTO_AC_DO_NOT_CREATE_ITEM',l_token);
1869
1870 -- Bugfix 4084568: Adding message for model line on Hold.
1871
1872 cto_msg_pub.cto_message('BOM','CTO_MODEL_LINE_EXCPN_HOLD');
1873
1874
1875
1876 raise fnd_api.g_exc_error;
1877
1878 end if; /* create item profile condition */
1879
1880 end if; /* missed lines cursor condition */
1881
1882 end if; /* Preconfigure / Autoconfigure condition */
1883
1884
1885
1886 close missed_lines;
1887
1888 EXCEPTION -- exception for stmt 51 and 55
1889
1890 when others then
1891 IF PG_DEBUG <> 0 THEN
1892 oe_debug_pub.add ('Failed in stmt ' || lStmtNum || ' with error: '||sqlerrm);
1893 END IF;
1894 raise fnd_api.g_exc_error;
1895 END ;
1896
1897
1898 /* 2524562 End of bugfix */
1899
1900 -- b2307936 : We will insert the base model row irrespective of the OpseqProfile value.
1901
1902
1903 /*---------------------------------------------------------------+
1904 Third : Get the base model row into BOM_INVENTORY_COMPONENTS
1905 +----------------------------------------------------------------*/
1906
1907 lStmtNum := 60;
1908 insert into BOM_INVENTORY_COMPS_INTERFACE
1909 (
1910 operation_seq_num,
1911 component_item_id,
1912 last_update_date,
1913 last_updated_by,
1914 creation_date,
1915 created_by,
1916 last_update_login,
1917 item_num,
1918 component_quantity,
1919 component_yield_factor,
1920 component_remarks,
1921 effectivity_date,
1922 change_notice,
1923 implementation_date,
1924 disable_date,
1925 attribute_category,
1926 attribute1,
1927 attribute2,
1928 attribute3,
1929 attribute4,
1930 attribute5,
1931 attribute6,
1932 attribute7,
1933 attribute8,
1934 attribute9,
1935 attribute10,
1936 attribute11,
1937 attribute12,
1938 attribute13,
1939 attribute14,
1940 attribute15,
1941 planning_factor,
1942 quantity_related,
1943 so_basis,
1944 optional,
1945 mutually_exclusive_options,
1946 include_in_cost_rollup,
1947 check_atp,
1948 shipping_allowed,
1949 required_to_ship,
1950 required_for_revenue,
1951 include_on_ship_docs,
1952 include_on_bill_docs,
1953 low_quantity,
1954 high_quantity,
1955 acd_type,
1956 old_component_sequence_id,
1957 component_sequence_id,
1958 bill_sequence_id,
1959 request_id,
1960 program_application_id,
1961 program_id,
1962 program_update_date,
1963 wip_supply_type,
1964 pick_components,
1965 model_comp_seq_id,
1966 bom_item_type,
1967 optional_on_model, -- New columns for configuration
1968 parent_bill_seq_id, -- BOM restructure project.
1969 plan_level -- Used by CTO only.
1970 , basis_type, /* LBM project */
1971 batch_id
1972 )
1973 select
1974 1, -- operation_seq_num
1975 bcol.inventory_item_id,
1976 SYSDATE, -- last_updated_date
1977 1, -- last_updated_by
1978 SYSDATE, -- creation_date
1979 1, -- created_by
1980 1, -- last_update_login
1981 9, -- item_num
1982 1, -- comp_qty
1983 1, -- yield_factor
1984 NULL, --ic1.component_remark
1985 SYSDATE, -- effective date --bug4150255: Removed the trunc so that time is also populated.
1986 NULL, -- change notice
1987 SYSDATE, -- implementation_date
1988 NULL, -- disable date
1989 NULL, -- attribute_category
1990 NULL, -- attribute1
1991 NULL, -- attribute2
1992 NULL, -- attribute3
1993 NULL, -- attribute4
1994 NULL, -- attribute5
1995 NULL, -- attribute6
1996 NULL, -- attribute7
1997 NULL, -- attribute8
1998 NULL, -- attribute9
1999 NULL, -- attribute10
2000 NULL, -- attribute11
2001 NULL, -- attribute12
2002 NULL, -- attribute13
2006 2, -- quantity_related
2003 NULL, -- attribute14
2004 NULL, -- attribute15
2005 100, -- planning_factor
2007 2, -- so_basis
2008 2, -- optional
2009 2, -- mutually_exclusive_options
2010 2, -- include_in_cost_rollup
2011 2, -- check_atp
2012 2, -- shipping_allowed = NO
2013 2, -- required_to_ship = NO
2014 2, -- required_for_revenue
2015 2, -- include_on_ship_docs
2016 2, -- include_on_bill_docs
2017 NULL, -- low_quantity
2018 NULL, -- high_quantity
2019 NULL, -- acd_type
2020 NULL, -- old_component_sequence_id
2021 bom_inventory_components_s.nextval, -- component sequence id
2022 lConfigBillId, -- bill sequence id
2023 NULL, -- request_id
2024 NULL, -- program_application_id
2025 NULL, -- program_id
2026 NULL, -- program_update_date
2027 6, -- wip_supply_type
2028 2, -- pick_components = NO
2029 NULL, -- model comp seq id for later use
2030 1, -- bom_item_type
2031 1, --optional_on_model,
2032 0, --parent_bill_seq_id,
2033 0 --plan_level
2034 , 1, -- basis_type /* LBM project */
2035 cto_msutil_pub.bom_batch_id
2036 from
2037 bom_cto_order_lines bcol
2038 where bcol.line_id = pLineId
2039 and bcol.ordered_quantity <> 0
2040 and bcol.inventory_item_id = pModelId;
2041
2042
2043 lCnt := sql%rowcount ;
2044
2045 IF PG_DEBUG <> 0 THEN
2046 oe_debug_pub.add ('create_bom_ml: ' || 'Third -- Inserted ' || lCnt ||' rows',1);
2047 END IF;
2048
2049 xBillId := lConfigBillId;
2050
2051 return(1);
2052
2053 EXCEPTION
2054
2055 WHEN NO_DATA_FOUND THEN -- Bugfix 2374246 Instead of handling no_calendar_date exception here
2056 xBillID := 0; -- the exception is placed directly with stmt # 43.
2057 return(-1); -- 2986192
2058
2059 WHEN FND_API.G_EXC_ERROR THEN
2060 xErrorMessage := 'CTOCBOMB:create_bom_ml failed with expected error in stmt '||to_char(lStmtNum);
2061 --xMessageName := 'CTO_CREATE_BOM_ERROR';
2062
2063 IF PG_DEBUG <> 0 THEN
2064 oe_debug_pub.add ('create_bom_ml: ' || 'create_item::exp error::'||to_char(lStmtNum)||sqlerrm,1);
2065 END IF;
2066
2067
2068
2069 delete from bom_inventory_comps_interface
2070 where bill_sequence_id = xBillId ;
2071
2072 xBillId := null ;
2073
2074 IF PG_DEBUG <> 0 THEN
2075 oe_debug_pub.add ('create_bom_ml: ' || 'deleted records from bici ::'||to_char(sql%rowcount) ,1);
2076 END IF;
2077
2078 return(0);
2079
2080 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2081 xErrorMessage := 'CTOCBOMB:create_bom_ml failed with unexpected error in stmt '||to_char(lStmtNum);
2082 xMessageName := 'CTO_CREATE_BOM_ERROR';
2083 IF PG_DEBUG <> 0 THEN
2084 oe_debug_pub.add ('create_bom_ml: ' || 'create_item::unexp error::'||to_char(lStmtNum)||sqlerrm,1);
2085 END IF;
2086 return(-1);
2087
2088
2089 WHEN OTHERS THEN
2090 xErrorMessage := 'CTOCBOMB:'||to_char(lStmtNum)||':'||substrb(sqlerrm,1,150);
2091 xMessageName := 'CTO_CREATE_BOM_ERROR';
2092 IF PG_DEBUG <> 0 THEN
2093 oe_debug_pub.add('create_bom_ml: ' || 'Error: Others excpn in create_bom_ml: ' || sqlerrm);
2094 END IF;
2095 return(-1);
2096
2097 END create_bom_ml;
2098
2099
2100
2101
2102
2103
2104 /*---------------------------------------------------------------
2105 Modified : 02-02-2005 Kiran Konada
2106 | bug#4092184 FP:11.5.9 - 11.5.10 :I
2107 | customer bug#4081613
2108 ---------------------------------------------------------------*/
2109
2110 function create_bom_data_ml (
2111 pModelId in number,
2112 pConfigId in number,
2113 pOrgId in number,
2114 pConfigBillId in number,
2115 xErrorMessage out NOCOPY VARCHAR2,
2116 xMessageName out NOCOPY VARCHAR2,
2117 xTableName out NOCOPY VARCHAR2)
2118 return integer
2119 is
2120
2121 status number;
2122 lStmtNum number;
2123 lCfmRtgFlag number;
2124 l_from_sequence_id number;
2125 lBomId number ;
2126 lSaveBomId number ;
2127 lSaveOpSeqNum number ;
2128 lSaveItemId number ;
2129 lSaveCompSeqId number ;
2130 lTotalQty number ;
2131 lOpSeqNum number ;
2132 lCompSeqId number ;
2133 lItemId number ;
2134 lqty number ;
2135 lSaveOptional number ;
2136 lOptional number ;
2137
2141 p_item_num number := 0;
2138 UP_DESC_ERR exception;
2139
2140
2142 p_bill_seq_id number;
2143 p_seq_increment number;
2144
2145 v_bom_count number ;
2146 v_bom_organization_id number ;
2147 v_bom_assembly_item_id number ;
2148 v_bom_creation_date date ;
2149
2150 -- 3222932 Variable declaration of new code
2151
2152 -- Collection to store all eff and disable dates
2153
2154 TYPE date_tab IS TABLE OF DATE INDEX BY BINARY_INTEGER;
2155 asc_date_arr date_tab;
2156
2157 -- Collection to store clubbed quantity with new date window
2158
2159 TYPE club_rec IS RECORD (
2160 eff_dt DATE,
2161 dis_dt DATE,
2162 qty NUMBER,
2163 row_id rowid
2164 );
2165
2166 TYPE club_tab IS TABLE OF club_rec INDEX BY BINARY_INTEGER;
2167
2168 club_tab_arr club_tab;
2169
2170 lrowid ROWID;
2171
2172 -- Get all components to be clubbed
2173 -- bug 4244576: It is possible that the same item is existing at op seq 15, 25, 30, 15. In
2174 -- this case the two records at 15 needs to be clubbed but not the once at 25 and 30. Going
2175 -- just by item_id will club all 4 records. We need to go by item_id and op_seq.
2176 cursor club_comp is
2177 select distinct b1.component_item_id item_id, b1.operation_seq_num
2178 from bom_inventory_comps_interface b1,bom_inventory_comps_interface b2
2179 where b1.bill_sequence_id = b2.bill_sequence_id
2180 and b1.component_sequence_id <> b2.component_sequence_id
2181 and b1.operation_seq_num = b2.operation_seq_num
2182 and b1.component_item_id = b2.component_item_id
2183 and b1.bill_sequence_id = pConfigBillId ; /* Sushant Made a change */
2184 /* LBM project */
2185
2186
2187 -- start 3674833
2188 -- Collection to store comp seq
2189
2190
2191 TYPE seq_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
2192
2193
2194 model_comp_seq_id_arr seq_tab;
2195 component_item_id_arr seq_tab;
2196 operation_seq_num_arr seq_tab; --4244576
2197 club_component_sequence_id number;
2198 prev_comp_item_id number;
2199
2200 -- end 3674833
2201
2202 max_dis_date DATE;
2203 null_dis_date DATE;
2204 kounter NUMBER;
2205
2206 -- variables for debugging
2207 dbg_eff_date Date;
2208 dbg_dis_date Date;
2209 dbg_qty Number;
2210
2211
2212 -- Cursor for debugging
2213 cursor c1_debug( xItemId number, xOperation_seq_num number) is
2214 select effectivity_date eff_date,
2215 nvl (disable_date,g_SchShpDate) dis_date,
2216 component_quantity cmp_qty,
2217 basis_type
2218 from bom_inventory_comps_interface
2219 where bill_sequence_id = pConfigBillId
2220 and component_item_id = xItemId
2221 and operation_seq_num = xOperation_seq_num; --4244576
2222 -- bugfix 3985173
2223 -- new cursor for component sequence
2224 cursor club_comp_seq ( xComponentItemId number, xOperation_seq_num number ) is
2225 select bic.component_sequence_id comp_seq_id
2226 from bom_inventory_components bic,
2227 bom_bill_of_materials bom
2228 where bom.assembly_item_id = pConfigId
2229 and bom.organization_id = pOrgId
2230 and bic.bill_sequence_id = bom.bill_sequence_id
2231 and bic.component_item_id = xComponentItemId
2232 and bic.operation_seq_num = xOperation_seq_num; --4244576
2233
2234 v_diff_basis_string varchar2(2000);
2235 v_sub_diff_basis_string varchar2(2000);
2236
2237 l_new_line varchar2(10) := fnd_global.local_chr(10);
2238
2239 l_token CTO_MSG_PUB.token_tbl;
2240 basis_model_comp_seq_id_arr seq_tab;
2241 basis_component_item_id_arr seq_tab;
2242 l_model_name varchar2(1000);
2243 l_comp_name varchar2(1000);
2244 l_org_name varchar2(1000);
2245 begin
2246
2247 /*--------------------------------------------------------------+
2248 If more than one row in the BOM_INVENTORY_COMPS_INTERFACE
2249 that contain the same bill_sequence_id, operation_seq_num and
2250 component_item_id, those rows will be combined into a
2251 single row and the accumulated COMPONENT_QUANTITY will be
2252 used in the row.
2253 +---------------------------------------------------------------*/
2254
2255 -- start 3674833
2256 -- Populate seq_tab_arr with component sequence id information
2257 -- We need this info before inserting into bom_reference_designator
2258 -- 4244576 - Also need to get operstion_seq_num into an array.
2259
2260 select b1.model_comp_seq_id, b1.component_item_id, b1.operation_seq_num
2261 BULK COLLECT INTO model_comp_seq_id_arr, component_item_id_arr, operation_seq_num_arr
2262 from bom_inventory_comps_interface b1,bom_inventory_comps_interface b2
2263 where b1.bill_sequence_id = b2.bill_sequence_id
2264 and b1.component_sequence_id <> b2.component_sequence_id
2268 UNION
2265 and b1.operation_seq_num = b2.operation_seq_num
2266 and b1.component_item_id = b2.component_item_id
2267 and b1.bill_sequence_id = pConfigBillId
2269 select b2.model_comp_seq_id, b2.component_item_id, b2.operation_seq_num
2270 from bom_inventory_comps_interface b1,bom_inventory_comps_interface b2
2271 where b1.bill_sequence_id = b2.bill_sequence_id
2272 and b1.component_sequence_id <> b2.component_sequence_id
2273 and b1.operation_seq_num = b2.operation_seq_num
2274 and b1.component_item_id = b2.component_item_id
2275 and b2.bill_sequence_id = pConfigBillId
2276 ORDER by 2;
2277
2278
2279 if model_comp_seq_id_arr.count > 0 then
2280 for x1 in model_comp_seq_id_arr.FIRST..model_comp_seq_id_arr.LAST
2281 loop
2282 oe_debug_pub.add( ' Start Looping ',1);
2283 IF PG_DEBUG <> 0 THEN
2284 oe_debug_pub.add ( ' Model_Comp_seq (' ||x1|| ') = ' ||model_comp_seq_id_arr(x1)
2285 ||' Component_item_id (' ||x1|| ') = ' ||component_item_id_arr(x1)
2286 ||' operation-seq_num (' ||x1|| ') = ' ||operation_seq_num_arr(x1),1); --4244576
2287
2288 END IF;
2289 end loop;
2290 end if;
2291 -- end 3674833
2292
2293
2294
2295 gUserId := nvl(fnd_global.user_id, -1);
2296 gLoginId := nvl(fnd_global.login_id, -1);
2297
2298 -- Start new code 3222932
2299
2300 -- Execute following code for each clubbed components
2301 for club_comp_rec in club_comp
2302 loop
2303
2304 -- Get all eff and disable dates in asc order
2305 -- 4244576
2306 oe_debug_pub.add( ' Looping for item id : ' ||club_comp_rec.item_id ||' operation_seq : '||club_comp_rec.operation_seq_num,1);
2307
2308 select distinct effectivity_date
2309 BULK COLLECT INTO asc_date_arr
2310 from bom_inventory_comps_interface
2311 where bill_sequence_id = pConfigBillId
2312 and component_item_id = club_comp_rec.item_id
2313 and operation_seq_num = club_comp_rec.operation_seq_num --4244576
2314 UNION
2315 select distinct disable_date
2316 from bom_inventory_comps_interface
2317 where bill_sequence_id = pConfigBillId
2318 and component_item_id = club_comp_rec.item_id
2319 and operation_seq_num = club_comp_rec.operation_seq_num --4244576
2320 order by 1;
2321
2322 -- Printing dates
2323
2324 if asc_date_arr.count > 0 then
2325 for x1 in asc_date_arr.FIRST..asc_date_arr.LAST
2326 loop
2327 IF PG_DEBUG <> 0 THEN
2328 oe_debug_pub.add ('Date ('||x1||') = '||to_char(asc_date_arr(x1),'DD-MON-YY HH24:MI:SS'),1);
2329 END IF;
2330 end loop;
2331 end if;
2332
2333 -- Creating clubbing windows
2334
2335
2336 if asc_date_arr.count > 0 then
2337 for x2 in 1..(asc_date_arr.count-1)
2338 loop
2339 club_tab_arr(x2).eff_dt := asc_date_arr(x2);
2340 club_tab_arr(x2).dis_dt := asc_date_arr(x2+1);
2341 end loop;
2342 end if;
2343
2344 -- Printing dates of clubbing window
2345
2346 if club_tab_arr.count > 0 then
2347 for x3 in club_tab_arr.FIRST..club_tab_arr.LAST
2348 loop
2349 IF PG_DEBUG <> 0 THEN
2350 oe_debug_pub.add ('ED ('||x3||') = ' ||to_char(club_tab_arr(x3).eff_dt,'DD-MON-YY HH24:MI:SS')||
2351 ' ---- DD ('||x3||') = '|| to_char(club_tab_arr(x3).dis_dt,'DD-MON-YY HH24:MI:SS'),1);
2352 END IF;
2353 end loop;
2354 end if;
2355
2356 -- Modifying eff dates of clubbing windows
2357
2358 if club_tab_arr.count > 0 then
2359 for x21 in 2..(club_tab_arr.count)
2360 loop
2361 if ( club_tab_arr(x21 - 1).dis_dt = club_tab_arr(x21).eff_dt ) then
2362 club_tab_arr(x21).eff_dt := club_tab_arr(x21).eff_dt + 1/86400;
2363 end if;
2364 end loop;
2365 end if;
2366
2367 -- Printing dates of clubbing window
2368
2369 if club_tab_arr.count > 0 then
2370 for x22 in club_tab_arr.FIRST..club_tab_arr.LAST
2371 loop
2372 IF PG_DEBUG <> 0 THEN
2373 oe_debug_pub.add ('ED ('||x22||') = ' ||to_char(club_tab_arr(x22).eff_dt,'DD-MON-YY HH24:MI:SS')||
2374 ' ---- DD ('||x22||') = '|| to_char(club_tab_arr(x22).dis_dt,'DD-MON-YY HH24:MI:SS'),1);
2375 END IF;
2376 end loop;
2377 end if;
2378
2379 -- for debug
2380 for d1 in c1_debug (club_comp_rec.item_id, club_comp_rec.operation_seq_num) loop --4244576
2381
2382 dbg_eff_date := d1.eff_date;
2383 dbg_dis_date := d1.dis_date;
2384 dbg_qty := d1.cmp_qty;
2385
2386 IF PG_DEBUG <> 0 THEN
2387 oe_debug_pub.add( 'ED '||to_char(dbg_eff_date,'DD-MON-YY HH24:MI:SS')||' DD '||to_char(dbg_dis_date,'DD-MON-YY HH24:MI:SS')||' Qty '||dbg_qty||' Basis Type = '||d1.basis_type);
2391
2388 END IF;
2389
2390 end loop;
2392 -- Clubbing quantities
2393
2394 if club_tab_arr.count > 0 then
2395 for x4 in club_tab_arr.FIRST.. club_tab_arr.LAST
2396 loop
2397
2398
2399
2400
2401
2402
2403 IF PG_DEBUG <> 0 THEN
2404 oe_debug_pub.add ('checking for club comp error ', 1 ) ;
2405 END IF;
2406
2407
2408
2409 /* begin LBM project */
2410 /* Check whether multiple occurences of the same component with the same inventory_item_id
2411 and operation_sequence have conflicting basis_type.
2412 */
2413 select b1.model_comp_seq_id, b1.component_item_id
2414 BULK COLLECT INTO
2415 basis_model_comp_seq_id_arr, basis_component_item_id_arr
2416 from
2417 bom_inventory_comps_interface b1,bom_inventory_comps_interface b2
2418 where b1.bill_sequence_id = b2.bill_sequence_id
2419 and b1.component_sequence_id <> b2.component_sequence_id
2420 and b1.operation_seq_num = b2.operation_seq_num
2421 and b1.component_item_id = b2.component_item_id
2422 and b1.bill_sequence_id = pConfigBillId
2423 and b1.basis_type <> b2.basis_type
2424 and b1.effectivity_date <= club_tab_arr(x4).eff_dt
2425 and nvl(b1.disable_date,g_SchShpDate) >= club_tab_arr(x4).dis_dt
2426 and b1.bill_sequence_id = pConfigBillId
2427 and b1.component_item_id = club_comp_rec.item_id
2428 and b1.operation_seq_num = club_comp_rec.operation_seq_num
2429 and b2.effectivity_date <= club_tab_arr(x4).eff_dt
2430 and nvl(b2.disable_date,g_schshpdate) >= club_tab_arr(x4).dis_dt;
2431
2432
2433 if( basis_model_comp_seq_id_arr.count > 0 ) then
2434
2435
2436 for i in 1..basis_model_comp_seq_id_arr.count
2437 loop
2438 if ( i = 1 ) then
2439
2440 v_diff_basis_string := 'component ' || basis_component_item_id_arr(i) ;
2441
2442 else
2443
2444 v_sub_diff_basis_string := 'component ' || basis_component_item_id_arr(i) || l_new_line ;
2445
2446 v_diff_basis_string := v_diff_basis_string || v_sub_diff_basis_string ;
2447
2448 end if ;
2449
2450
2451 end loop;
2452
2453
2454 IF PG_DEBUG <> 0 THEN
2455 oe_debug_pub.add( 'Going to Raise CTO_CLUB_COMP_ERROR');
2456 oe_debug_pub.add( 'will not populated message CTO_CLUB_COMP_ERROR');
2457 END IF;
2458
2459 select segment1 into
2460 l_model_name
2461 from mtl_system_items
2462 where inventory_item_id = pmodelid
2463 and organization_id = porgid;
2464
2465
2466 select segment1 into
2467 l_comp_name
2468 from mtl_system_items
2469 where inventory_item_id = club_comp_rec.item_id
2470 and organization_id = porgid;
2471
2472 select organization_name
2473 into l_org_name
2474 from inv_organization_name_v
2475 where organization_id = porgid;
2476
2477 --l_token(1).token_name := 'ERROR_COMPONENTS';
2478 --l_token(1).token_value := v_diff_basis_string ;
2479 l_token(1).token_name := 'MODEL';
2480 l_token(1).token_value := l_model_name;
2481 l_token(2).token_name := 'ORGANIZATION';
2482 l_token(2).token_value := l_org_name;
2483 l_token(3).token_name := 'COMPONENT';
2484 l_token(3).token_value := l_comp_name;
2485 --cto_msg_pub.cto_message('BOM','CTO_CLUB_COMP_ERROR',l_token);
2486 cto_msg_pub.cto_message('BOM','CTO_CLUB_COMP_ERROR',l_token);
2487
2488
2489 raise fnd_api.g_exc_error;
2490
2491
2492 end if;
2493
2494 /* end LBM project */
2495
2496
2497
2498
2499
2500
2501 select max(rowid), sum(decode(nvl(basis_type,1), 1, component_quantity, 0))
2502 + max(decode(nvl(basis_type,1), 2, component_quantity, 0)) /* LBM Project */
2503 into club_tab_arr(x4).row_id,club_tab_arr(x4).qty
2504 from bom_inventory_comps_interface
2505 where effectivity_date <= club_tab_arr(x4).eff_dt
2506 and nvl(disable_date,g_SchShpDate) >= club_tab_arr(x4).dis_dt
2507 and bill_sequence_id = pConfigBillId
2508 and component_item_id = club_comp_rec.item_id
2509 and operation_seq_num = club_comp_rec.operation_seq_num; --4244576
2510 end loop;
2511 end if;
2512
2513 -- Printing Clubbed quantity with window
2514
2515 if club_tab_arr.count > 0 then
2516 for x5 in club_tab_arr.FIRST..club_tab_arr.LAST
2517 loop
2518 IF PG_DEBUG <> 0 THEN
2519 oe_debug_pub.add ('ED (' ||x5|| ') = ' ||to_char(club_tab_arr(x5).eff_dt,'DD-MON-YY HH24:MI:SS')||
2520 ' -- DD (' ||x5|| ') = ' ||to_char(club_tab_arr(x5).dis_dt,'DD-MON-YY HH24:MI:SS')||
2524 end if;
2521 ' -- Qty (' ||x5|| ') = ' ||club_tab_arr(x5).qty,1);
2522 END IF;
2523 end loop;
2525
2526 -- Now insert into bom_inventory_comps_interface
2527
2528 -- Modified by Renga Kannan on 09/01/06 for bug 4542461
2529 -- For the window where there is no qty the above select statement will
2530 -- return null qty. We should not insert this row into interface table.
2531
2532 if club_tab_arr.count > 0 then
2533
2534 for x6 in club_tab_arr.FIRST.. club_tab_arr.LAST
2535 loop
2536 If nvl(club_tab_arr(x6).qty,0) <> 0 then
2537 insert into bom_inventory_comps_interface
2538 (
2539 component_item_id,
2540 bill_sequence_id,
2541 effectivity_date,
2542 disable_date,
2543 component_quantity,
2544 creation_date,
2545 created_by,
2546 last_update_date,
2547 last_updated_by,
2548 operation_seq_num,
2549 last_update_login,
2550 item_num,
2551 component_yield_factor,
2552 component_remarks,
2553 change_notice,
2554 implementation_date,
2555 attribute_category,
2556 attribute1,
2557 attribute2,
2558 attribute3,
2559 attribute4,
2560 attribute5,
2561 attribute6,
2562 attribute7,
2563 attribute8,
2564 attribute9,
2565 attribute10,
2566 attribute11,
2567 attribute12,
2568 attribute13,
2569 attribute14,
2570 attribute15,
2571 planning_factor,
2572 quantity_related,
2573 so_basis,
2574 optional,
2575 mutually_exclusive_options,
2576 include_in_cost_rollup,
2577 check_atp,
2578 shipping_allowed,
2579 required_to_ship,
2580 required_for_revenue,
2581 include_on_ship_docs,
2582 include_on_bill_docs,
2583 low_quantity,
2584 high_quantity,
2585 acd_type,
2586 old_component_sequence_id,
2587 component_sequence_id,
2588 request_id,
2589 program_application_id,
2590 program_id,
2591 program_update_date,
2592 wip_supply_type,
2593 pick_components,
2594 model_comp_seq_id,
2595 supply_subinventory,
2596 supply_locator_id,
2597 bom_item_type,
2598 optional_on_model,
2599 parent_bill_seq_id,
2600 plan_level,
2601 revised_item_sequence_id
2602 , basis_type, /* LBM change */
2603 batch_id
2604 )
2605 select
2606 club_comp_rec.item_id,
2607 pConfigBillId,
2608 club_tab_arr(x6).eff_dt,
2609 club_tab_arr(x6).dis_dt,
2610 round(club_tab_arr(x6).qty,7), -- to maintain decimal qty support of option items
2611 SYSDATE,
2612 pConfigBillId, -- CREATED_BY is set to pConfigBillId to identify rows from clubbing
2613 SYSDATE,
2614 1,
2615 operation_seq_num,
2616 last_update_login,
2617 item_num,
2618 component_yield_factor,
2619 component_remarks,
2620 change_notice,
2621 implementation_date,
2622 attribute_category,
2623 attribute1,
2624 attribute2,
2625 attribute3,
2626 attribute4,
2627 attribute5,
2628 attribute6,
2629 attribute7,
2630 attribute8,
2631 attribute9,
2632 attribute10,
2633 attribute11,
2634 attribute12,
2635 attribute13,
2636 attribute14,
2637 attribute15,
2638 planning_factor,
2639 quantity_related,
2640 so_basis,optional,
2641 mutually_exclusive_options,
2642 include_in_cost_rollup,
2643 check_atp,
2644 shipping_allowed,
2645 required_to_ship,
2646 required_for_revenue,
2647 include_on_ship_docs,
2648 include_on_bill_docs,
2649 low_quantity,
2650 high_quantity,
2651 acd_type,
2652 old_component_sequence_id,
2653 bom_inventory_components_s.nextval,
2654 request_id,
2655 program_application_id,
2656 program_id,
2657 program_update_date,
2661 supply_subinventory,
2658 wip_supply_type,
2659 pick_components,
2660 model_comp_seq_id,
2662 supply_locator_id,
2663 bom_item_type,
2664 optional_on_model,
2665 parent_bill_seq_id,
2666 plan_level,
2667 revised_item_sequence_id
2668 , nvl(basis_type,1), /* LBM project */
2669 cto_msutil_pub.bom_batch_id
2670 from bom_inventory_comps_interface
2671 where component_item_id = club_comp_rec.item_id
2672 and operation_seq_num = club_comp_rec.operation_seq_num --4244576
2673 and bill_sequence_id = pConfigBillId
2674 and rowid = club_tab_arr(x6).row_id;
2675 end if;
2676 end loop;
2677 end if;
2678
2679 -- Delete original option item rows from bici
2680 delete from bom_inventory_comps_interface
2681 where component_item_id = club_comp_rec.item_id
2682 and operation_seq_num = club_comp_rec.operation_seq_num --4244576
2683 and bill_sequence_id = pConfigBillId
2684 and created_by <> pConfigBillId;
2685
2686 -- Delete rows from bom_inventory_comps_interface where qty = 0
2687 delete from bom_inventory_comps_interface
2688 where component_item_id = club_comp_rec.item_id
2689 and operation_seq_num = club_comp_rec.operation_seq_num --4244576
2690 and bill_sequence_id = pConfigBillId
2691 and created_by = pConfigBillId
2692 and component_quantity = 0;
2693
2694 -- Delete club_tab_arr and asc_date_arr to process next item in club_comp_cur
2695 if club_tab_arr.count > 0 then
2696 for x7 in club_tab_arr.FIRST..club_tab_arr.LAST
2697 loop
2698 club_tab_arr.DELETE(x7);
2699 end loop;
2700 end if;
2701
2702 if asc_date_arr.count > 0 then
2703 for x8 in asc_date_arr.FIRST..asc_date_arr.LAST
2704 loop
2705 asc_date_arr.DELETE(x8);
2706 end loop;
2707 end if;
2708
2709 end loop; -- End loop of club_comp_cur
2710
2711 -- end new code 3222932
2712
2713
2714
2715
2716
2717
2718
2719 /*----------------------------------------------+
2720 Update item sequence id.
2721 To address configuration BOM restructure enhancements,
2722 item sequence is being updated such that there are no
2723 duplicate sequences, and in the logical order of components
2724 selection from the parent model BOM.
2725 The Item Sequence Increment is based on the profile
2726 "BOM:Item Sequence Increment".
2727 +----------------------------------------------*/
2728
2729 --
2730 -- Get item sequence increment
2731 --
2732 p_seq_increment := fnd_profile.value('BOM:ITEM_SEQUENCE_INCREMENT');
2733 IF PG_DEBUG <> 0 THEN
2734 oe_debug_pub.add('create_bom_data_ml: ' || 'Item Seq Increment::'||to_char(p_seq_increment), 1);
2735 END IF;
2736
2737 --
2738 -- update item_num of top model
2739 --
2740 p_item_num := p_item_num + p_seq_increment;
2741
2742 IF PG_DEBUG <> 0 THEN
2743 oe_debug_pub.add('create_bom_data_ml: ' || 'p_item_num::'||to_char(p_item_num), 2);
2744 END IF;
2745
2746 update bom_inventory_comps_interface
2747 set item_num = p_item_num
2748 where bill_sequence_id = pConfigBillId and parent_bill_seq_id = 0; -- Sushant Fixed bug #3374548
2749
2750 IF PG_DEBUG <> 0 THEN
2751 oe_debug_pub.add('create_bom_data_ml: ' || 'Updated model row::'||sql%rowcount, 2);
2752 END IF;
2753
2754 p_item_num := p_item_num + p_seq_increment;
2755
2756 oe_debug_pub.add('create_bom_data_ml: ' || 'config bill id ::'|| pConfigBillId , 2);
2757
2758
2759 --
2760 -- get bill_sequence_id of top model
2761 --
2762 select common_bill_sequence_id
2763 into p_bill_seq_id
2764 from bom_bill_of_materials
2765 where assembly_item_id =
2766 (select component_item_id
2767 from bom_inventory_comps_interface
2768 where bill_sequence_id = pConfigBillId and parent_bill_seq_id = 0) -- Sushant Fixed bug #3374548
2769 and organization_id = pOrgId
2770 and alternate_bom_designator is null;
2771
2772 oe_debug_pub.add('create_bom_data_ml: ' || 'common bill seq id ::'|| p_bill_seq_id , 2);
2773 --
2774 -- call update_item_num procedure with top model
2775 -- this will update item_num for the rest of the items
2776 --
2777 IF PG_DEBUG <> 0 THEN
2778 oe_debug_pub.add('create_bom_data_ml: ' || 'Calling update_item_num will p_bill_seq_id::'||to_char(p_bill_seq_id)||' and p_item_num::'||to_char(p_item_num), 2);
2779 END IF;
2780
2781 update_item_num(
2782 p_bill_seq_id,
2783 p_item_num,
2784 pOrgId,
2785 p_seq_increment);
2786
2787
2788
2789
2790 begin
2791 select organization_id, assembly_item_id , creation_date
2792 into v_bom_organization_id, v_bom_assembly_item_id, v_bom_creation_date
2793 from bom_bill_of_materials where bill_sequence_id = pConfigBillId ;
2794
2795 exception
2796 when others then
2800
2797
2798 oe_debug_pub.add('create_bom_data_ml: ' || SQLERRM ,2);
2799 oe_debug_pub.add('create_bom_data_ml: ' || SQLCODE ,2);
2801 end ;
2802
2803
2804
2805 oe_debug_pub.add('create_bom_data_ml: ' || 'count ' || v_bom_count ,2);
2806 oe_debug_pub.add('create_bom_data_ml: ' || 'org ' || v_bom_organization_id ,2);
2807 oe_debug_pub.add('create_bom_data_ml: ' || 'assid ' || v_bom_assembly_item_id ,2);
2808 oe_debug_pub.add('create_bom_data_ml: ' || 'date ' || v_bom_creation_date ,2);
2809 /*-------------------------------------------+
2810
2811 Load BOM_bill_of_materials
2812 +-------------------------------------------*/
2813 IF PG_DEBUG <> 0 THEN
2814 oe_debug_pub.add('create_bom_data_ml: ' || 'Before first insert into bill_of_materials.' ,2);
2815 oe_debug_pub.add('create_bom_data_ml: ' || 'Org: ' ||to_char(pOrgId), 2);
2816 oe_debug_pub.add('create_bom_data_ml: ' || 'Model: ' || to_char(pModelId), 2);
2817 oe_debug_pub.add('create_bom_data_ml: ' || 'Config: ' || to_char(pConfigId), 2);
2818 END IF;
2819
2820
2821
2822 /* begin changes for bug 4271269 */
2823
2824 if g_structure_type_id is null then
2825
2826 begin
2827
2828 select structure_type_id into g_structure_type_id from bom_alternate_designators
2829 where alternate_designator_code is null ;
2830
2831 exception
2832 when others then
2833 IF PG_DEBUG <> 0 THEN
2834 oe_debug_pub.add('create_bom_data_ml: ' || 'others error while retrieving structure_type_id .' ,2);
2835 oe_debug_pub.add('create_bom_data_ml: ' || 'defaulting structure_type_id to 1 .' ,2);
2836 g_structure_type_id := 1;
2837
2838 END IF;
2839
2840 end ;
2841
2842
2843
2844 IF PG_DEBUG <> 0 THEN
2845 oe_debug_pub.add('create_bom_data_ml: ' || 'structure_type_id is ' || g_structure_type_id ,2);
2846 END IF;
2847
2848 end if ;
2849
2850 /* end changes for bug 4271269 */
2851
2852
2853
2854
2855 -- As per BOM team, they have added two new fileds
2856 -- PK1_value and PK2_VAlue in 11.5.10 and R12
2857 -- These fields are added for some PLM projects
2858 -- PK1_VALUE should be assembly_item_id
2859 -- PK2_VALUE should be organization id
2860 -- So far these two columns are populated thru database trigger
2861 -- bom is planning on droping this trigger in R12, hence we need
2862 lStmtNum := 145;
2863 xTableName := 'BOM_BILL_OF_MATERIALS';
2864 insert into BOM_BILL_OF_MATERIALS(
2865 assembly_item_id,
2866 organization_id,
2867 alternate_bom_designator,
2868 last_update_date,
2869 last_updated_by,
2870 creation_date,
2871 created_by,
2872 last_update_login,
2873 specific_assembly_comment,
2874 pending_from_ecn,
2875 attribute_category,
2876 attribute1,
2877 attribute2,
2878 attribute3,
2879 attribute4,
2880 attribute5,
2881 attribute6,
2882 attribute7,
2883 attribute8,
2884 attribute9,
2885 attribute10,
2886 attribute11,
2887 attribute12,
2888 attribute13,
2889 attribute14,
2890 attribute15,
2891 assembly_type,
2892 bill_sequence_id,
2893 common_bill_sequence_id,
2894 source_bill_sequence_id, /* COMMON BOM Project 12.0 */
2895 request_id,
2896 program_application_id,
2897 program_id,
2898 program_update_date,
2899 implementation_date, -- bug fix 3759118,FP 3810243
2900 structure_type_id, -- bugfix 4271269
2901 effectivity_control, -- bugfix 4271269
2902 pk1_value,
2903 pk2_value
2904 )
2905 select
2906 pConfigId, -- assembly_item_id
2907 pOrgId, -- organization_id
2908 NULL, -- alternate_bom_designator
2909 sysdate, -- last_update_date
2910 1, -- last_update_by
2911 sysdate, -- creation date
2912 1, -- created by
2913 1, -- last_update_login
2914 b.specific_assembly_comment, -- specific assembly comment /*Bugfix 2115056*/
2915 NULL, -- pending from ecn
2916 -- Begin Bugfix 2115056
2917 b.attribute_category, -- attribute category
2918 b.attribute1, -- attribute1
2919 b.attribute2, -- attribute2
2920 b.attribute3, -- attribute3
2921 b.attribute4, -- attribute4
2922 b.attribute5, -- attribute5
2923 b.attribute6, -- attribute6
2924 b.attribute7, -- attribute7
2925 b.attribute8, -- attribute8
2926 b.attribute9, -- attribute9
2927 b.attribute10, -- attribute10
2928 b.attribute11, -- attribute11
2929 b.attribute12, -- attribute12
2930 b.attribute13, -- attribute13
2931 b.attribute14, -- attribute14
2932 b.attribute15, -- attribute15
2933 -- End Bugfix 2115056
2937 pConfigBillId, -- source_bill_sequence_id COMMON BOM Project 12.0
2934 b.assembly_type, -- assembly_type
2935 pConfigBillId,
2936 pConfigBillId,
2938 NULL, -- request id
2939 NULL, -- program_application_id
2940 NULL, -- program id
2941 NULL, -- program date
2942 SYSDATE, -- implementation date bug fix 3759118,FP 3810243
2943 g_structure_type_id, -- bugfix 4271269 structure_type_id
2944 1, -- bugfix 4271269 effectivity_control
2945 pconfigid,
2946 porgid
2947 from bom_bill_of_materials b
2948 where b.assembly_item_id = pModelId
2949 and b.organization_id = pOrgId
2950 and b.alternate_bom_designator is NULL;
2951
2952 IF PG_DEBUG <> 0 THEN
2953 oe_debug_pub.add ('create_bom_data_ml: ' || xTableName || '-'|| lStmtNum || ': ' || sql%rowcount, 1 );
2954 END IF;
2955
2956 /*-----------------------------------------------+
2957 Load Bom_inventory_components
2958 +----------------------------------------------*/
2959 IF PG_DEBUG <> 0 THEN
2960 oe_debug_pub.add('create_bom_data_ml: ' || 'Before second insert into bom_inventory_components. ', 2);
2961 END IF;
2962 lStmtNum := 310;
2963 xTableName := 'BOM_INVENTORY_COMPONENTS';
2964 insert into BOM_INVENTORY_COMPONENTS
2965 (
2966 operation_seq_num,
2967 component_item_id,
2968 last_update_date,
2969 last_updated_by,
2970 creation_date,
2971 created_by,
2972 last_update_login,
2973 item_num,
2974 component_quantity,
2975 component_yield_factor,
2976 component_remarks,
2977 effectivity_date,
2978 change_notice,
2979 implementation_date,
2980 disable_date,
2981 attribute_category,
2982 attribute1,
2983 attribute2,
2984 attribute3,
2985 attribute4,
2986 attribute5,
2987 attribute6,
2988 attribute7,
2989 attribute8,
2990 attribute9,
2991 attribute10,
2992 attribute11,
2993 attribute12,
2994 attribute13,
2995 attribute14,
2996 attribute15,
2997 planning_factor,
2998 quantity_related,
2999 so_basis,
3000 optional,
3001 mutually_exclusive_options,
3002 include_in_cost_rollup,
3003 check_atp,
3004 shipping_allowed,
3005 required_to_ship,
3006 required_for_revenue,
3007 include_on_ship_docs,
3008 include_on_bill_docs,
3009 low_quantity,
3010 high_quantity,
3011 acd_type,
3012 old_component_sequence_id,
3013 component_sequence_id,
3014 common_component_sequence_id, /* COMMON BOM Project 12.0 */
3015 bill_sequence_id,
3016 request_id,
3017 program_application_id,
3018 program_id,
3019 program_update_date,
3020 wip_supply_type,
3021 operation_lead_time_percent,
3022 revised_item_sequence_id,
3023 supply_locator_id,
3024 supply_subinventory,
3025 pick_components,
3026 bom_item_type,
3027 optional_on_model, --isp bom
3028 parent_bill_seq_id, --isp bom
3029 plan_level, --isp bom
3030 model_comp_seq_id --isp bom
3031 , basis_type /* LBM change */
3032 )
3033 select
3034 b.operation_seq_num,
3035 b.component_item_id,
3036 b.last_update_date,
3037 1, /* last_updated_by */
3038 b.creation_date,
3039 1, /* created_by */
3040 b.last_update_login,
3041 b.item_num,
3042 b.component_quantity,
3043 b.component_yield_factor,
3044 b.component_remarks,
3045 b.effectivity_date,
3046 b.change_notice,
3047 b.implementation_date,
3048 -- 3222932 Chg g_futuredate back to NULL
3049 decode(b.disable_date,g_futuredate,to_date(NULL), b.disable_date),
3050 b.attribute_category,
3051 b.attribute1,
3052 b.attribute2,
3053 b.attribute3,
3054 b.attribute4,
3055 b.attribute5,
3056 b.attribute6,
3057 b.attribute7,
3058 b.attribute8,
3059 b.attribute9,
3060 b.attribute10,
3061 b.attribute11,
3062 b.attribute12,
3063 b.attribute13,
3064 b.attribute14,
3065 b.attribute15,
3066 b.planning_factor,
3067 b.quantity_related,
3068 b.so_basis,
3069 b.optional,
3070 b.mutually_exclusive_options,
3071 b.include_in_cost_rollup,
3072 decode( msi.bom_item_type , 1 , decode( msi.atp_flag , 'Y' , 1 , b.check_atp ) , b.check_atp ) , /* ATP changes for Model component */
3073 b.shipping_allowed,
3074 b.required_to_ship,
3075 b.required_for_revenue,
3076 b.include_on_ship_docs,
3077 b.include_on_bill_docs,
3078 b.low_quantity,
3079 b.high_quantity,
3080 b.acd_type,
3084 b.bill_sequence_id,
3081 b.old_component_sequence_id,
3082 b.component_sequence_id,
3083 b.component_sequence_id, -- common_component_sequence_id COMMON BOM Project 12.0
3085 NULL, /* request_id */
3086 NULL, /* program_application_id */
3087 NULL, /* program_id */
3088 sysdate, /* program_update_date */
3089 b.wip_supply_type,
3090 b.operation_lead_time_percent,
3091 NULL, -- 2524562
3092 b.supply_locator_id,
3093 b.supply_subinventory,
3094 b.pick_components,
3095 b.bom_item_type,
3096 b.optional_on_model, --isp bom
3097 b.parent_bill_seq_id, --isp bom
3098 b.plan_level, --isp bom
3099 b.model_comp_seq_id --isp bom
3100 , decode(b.basis_type,1,null,b.basis_type) /* LBM Change */
3101 from bom_inventory_comps_interface b , mtl_system_items msi
3102 where b.bill_sequence_id = pConfigBillId
3103 and b.component_item_id = msi.inventory_item_id
3104 and msi.organization_id = pOrgId ;
3105
3106
3107 IF PG_DEBUG <> 0 THEN
3108 oe_debug_pub.add ('create_bom_data_ml: ' || xTableName || '-'|| lStmtNum || ': ' || sql%rowcount, 1);
3109 END IF;
3110
3111
3112
3113 /*-----------------------------------------------+
3114 Populate Substitutes for Mandatory components
3115 +----------------------------------------------*/
3116 IF PG_DEBUG <> 0 THEN
3117 oe_debug_pub.add('create_bom_data_ml: ' || 'Before second insert into bom_inventory_components. ', 2);
3118 END IF;
3119 lStmtNum := 315;
3120 xTableName := 'BOM_SUBSTITUTE_COMPONENTS';
3121
3122
3123
3124
3125 insert into bom_substitute_components (
3126 substitute_component_id
3127 ,substitute_item_quantity
3128 ,component_sequence_id
3129 ,acd_type
3130 ,change_notice
3131 ,attribute_category
3132 ,attribute1
3133 ,attribute2
3134 ,attribute3
3135 ,attribute4
3136 ,attribute5
3137 ,attribute6
3138 ,attribute7
3139 ,attribute8
3140 ,attribute9
3141 ,attribute10
3142 ,attribute11
3143 ,attribute12
3144 ,attribute13
3145 ,attribute14
3146 ,attribute15
3147 ,original_system_reference
3148 ,enforce_int_requirements
3149 ,request_id
3150 ,program_application_id
3151 ,program_id
3152 ,program_update_date
3153 ,last_update_date
3154 ,last_updated_by
3155 ,creation_date
3156 ,created_by
3157 ,last_update_login
3158 )
3159 select
3160 s.substitute_component_id -- substitute_component_id
3161 ,s.substitute_item_quantity
3162 ,b.component_sequence_id
3163 ,s.acd_type
3164 ,s.change_notice
3165 ,s.attribute_category
3166 ,s.attribute1
3167 ,s.attribute2
3168 ,s.attribute3
3169 ,s.attribute4
3170 ,s.attribute5
3171 ,s.attribute6
3172 ,s.attribute7
3173 ,s.attribute8
3174 ,s.attribute9
3175 ,s.attribute10
3176 ,s.attribute11
3177 ,s.attribute12
3178 ,s.attribute13
3179 ,s.attribute14
3180 ,s.attribute15
3181 ,s.original_system_reference
3182 ,s.enforce_int_requirements
3183 ,FND_GLOBAL.CONC_REQUEST_ID /* REQUEST_ID */
3184 ,FND_GLOBAL.PROG_APPL_ID /* PROGRAM_APPLICATION_ID */
3185 ,FND_GLOBAL.CONC_PROGRAM_ID /* PROGRAM_ID */
3186 ,sysdate /* PROGRAM_UPDATE_DATE */
3187 ,sysdate /* LAST_UPDATE_DATE */
3188 ,gUserId /* LAST_UPDATED_BY */
3189 ,sysdate /* CREATION_DATE */
3190 ,gUserId /* CREATED_BY */
3191 ,gLoginId /* LAST_UPDATE_LOGIN */
3192 /*
3193 ,request_id
3194 ,program_application_id
3195 ,program_id
3196 ,program_update_date
3197 ,last_update_date
3198 ,last_updated_by
3199 ,creation_date
3200 ,created_by
3201 ,last_update_login
3202 */
3203
3204 from bom_inventory_comps_interface b , bom_inventory_components bic, bom_substitute_components s
3205 where b.bill_sequence_id = pConfigBillId
3206 and ABS(b.model_comp_seq_id) = bic.component_sequence_id
3207 and bic.optional = 2 /* only mandatory components */
3211
3208 and bic.component_sequence_id = s.component_sequence_id ;
3209
3210
3212
3213
3214
3215 IF PG_DEBUG <> 0 THEN
3216 oe_debug_pub.add ('create_bom_data_ml: ' || xTableName || '-'|| lStmtNum || ': ' || sql%rowcount, 1);
3217 END IF;
3218
3219
3220
3221
3222
3223
3224
3225
3226
3227
3228
3229
3230
3231
3232
3233 /* -------------------------------------------------------------------------+
3234 Insert into BOM_REFERENCE_DESIGNATORS table
3235 +--------------------------------------------------------------------------*/
3236 IF PG_DEBUG <> 0 THEN
3237 oe_debug_pub.add('create_bom_data_ml: ' || 'Before third insert into bom_reference_designators. ', 2);
3238 END IF;
3239 lStmtNum := 320;
3240 xTableName := 'BOM_REFERENCE_DESIGNATORS';
3241 insert into BOM_REFERENCE_DESIGNATORS
3242 (
3243 component_reference_designator,
3244 last_update_date,
3245 last_updated_by,
3246 creation_date,
3247 created_by,
3248 last_update_login,
3249 ref_designator_comment,
3250 change_notice,
3251 component_sequence_id,
3252 acd_type,
3253 request_id,
3254 program_application_id,
3255 program_id,
3256 program_update_date,
3257 attribute_category,
3258 attribute1,
3259 attribute2,
3260 attribute3,
3261 attribute4,
3262 attribute5,
3263 attribute6,
3264 attribute7,
3265 attribute8,
3266 attribute9,
3267 attribute10,
3268 attribute11,
3269 attribute12,
3270 attribute13,
3271 attribute14,
3272 attribute15
3273 )
3274 select
3275 r.component_reference_designator,
3276 SYSDATE,
3277 1,
3278 SYSDATE,
3279 1,
3280 1,
3281 r.REF_DESIGNATOR_COMMENT,
3282 NULL,
3283 ic.COMPONENT_SEQUENCE_ID,
3284 r.ACD_TYPE,
3285 NULL,
3286 NULL,
3287 NULL,
3288 NULL,
3289 r.ATTRIBUTE_CATEGORY,
3290 r.ATTRIBUTE1,
3291 r.ATTRIBUTE2,
3292 r.ATTRIBUTE3,
3293 r.ATTRIBUTE4,
3294 r.ATTRIBUTE5,
3295 r.ATTRIBUTE6,
3296 r.ATTRIBUTE7,
3297 r.ATTRIBUTE8,
3298 r.ATTRIBUTE9,
3299 r.ATTRIBUTE10,
3300 r.ATTRIBUTE11,
3301 r.ATTRIBUTE12,
3302 r.ATTRIBUTE13,
3303 r.ATTRIBUTE14,
3304 r.ATTRIBUTE15
3305 from
3306 bom_inventory_components ic,
3307 bom_reference_designators r,
3308 bom_bill_of_materials b
3309 where b.assembly_item_id = pConfigId
3310 and b.organization_id = pOrgId
3311 and ic.bill_sequence_id = b.bill_sequence_id
3312 and r.component_sequence_id = abs(ic.model_comp_seq_id) -- previously last_update_login
3313 and nvl(r.acd_type,0) <> 3;
3314
3315 IF PG_DEBUG <> 0 THEN
3316 oe_debug_pub.add ('create_bom_data_ml: ' || xTableName || '-'|| lStmtNum || ': ' || sql%rowcount,1 );
3317 END IF;
3318
3319
3320
3321 -- start 3674833
3322 -- need to insert reference designators of remaining components
3323
3324
3325 if model_comp_seq_id_arr.count > 0 then
3326 prev_comp_item_id := 0;
3327 for x1 in model_comp_seq_id_arr.FIRST..model_comp_seq_id_arr.LAST
3328 loop
3329 IF PG_DEBUG <> 0 THEN
3330 oe_debug_pub.add ( ' Model_Comp_seq (' ||x1|| ') = ' ||model_comp_seq_id_arr(x1)
3331 ||' Component_item_id (' ||x1|| ') = ' ||component_item_id_arr(x1),1);
3332 END IF;
3333
3334
3335 -- commented if condition for bug 3793286 IF prev_comp_item_id <> component_item_id_arr(x1) then
3336
3337
3338
3339 /* bugfix 3985173 : Commented following code since there could be instances when same
3340 component with same op seq number is appearing multiple times for a config bom. In
3341 that scenario , following query will return ORA-01422 error.
3342
3343 -- Determine the component_sequence_id into which this item has been clubbed
3344 select
3345 bic.component_sequence_id into club_component_sequence_id
3346 from
3347 bom_inventory_components bic,
3348 bom_bill_of_materials bom
3349 where bom.assembly_item_id = pConfigId
3350 and bom.organization_id = pOrgId
3354 Comment of bugfix 3985173 ends here */
3351 and bic.bill_sequence_id = bom.bill_sequence_id
3352 and bic.component_item_id = component_item_id_arr(x1);
3353 prev_comp_item_id := component_item_id_arr(x1);
3355
3356 -- bugfix 3985173 : New code will loop through component seq and insert
3357 -- into bom_reference_designator
3358 for a1 in club_comp_seq ( component_item_id_arr(x1), operation_seq_num_arr(x1) ) loop --4244576
3359
3360 club_component_sequence_id := a1.comp_seq_id;
3361
3362
3363 -- insert into BOM_REFERENCE_DESIGNATORS for the corresponding model_comp_seq_id
3364 -- if it has not already been inserted.
3365 IF PG_DEBUG <> 0 THEN
3366 oe_debug_pub.add ('club_component_sequence_id is '||club_component_sequence_id, 1);
3367 END if;
3368 IF PG_DEBUG <> 0 THEN
3369 oe_debug_pub.add ('Trying to insert into BOM_REFERENCE_DESIGNATORS', 1);
3370 END if;
3371 begin
3372 insert into BOM_REFERENCE_DESIGNATORS
3373 (
3374 component_reference_designator,
3375 last_update_date,
3376 last_updated_by,
3377 creation_date,
3378 created_by,
3379 last_update_login,
3380 ref_designator_comment,
3381 change_notice,
3382 component_sequence_id,
3383 acd_type,
3384 request_id,
3385 program_application_id,
3386 program_id,
3387 program_update_date,
3388 attribute_category,
3389 attribute1,
3390 attribute2,
3391 attribute3,
3392 attribute4,
3393 attribute5,
3394 attribute6,
3395 attribute7,
3396 attribute8,
3397 attribute9,
3398 attribute10,
3399 attribute11,
3400 attribute12,
3401 attribute13,
3402 attribute14,
3403 attribute15
3404 )
3405 select
3406 r.component_reference_designator,
3407 SYSDATE,
3408 1,
3409 SYSDATE,
3410 1,
3411 1,
3412 r.REF_DESIGNATOR_COMMENT,
3413 NULL,
3414 club_component_sequence_id,
3415 r.ACD_TYPE,
3416 NULL,
3417 NULL,
3418 NULL,
3419 NULL,
3420 r.ATTRIBUTE_CATEGORY,
3421 r.ATTRIBUTE1,
3422 r.ATTRIBUTE2,
3423 r.ATTRIBUTE3,
3424 r.ATTRIBUTE4,
3425 r.ATTRIBUTE5,
3426 r.ATTRIBUTE6,
3427 r.ATTRIBUTE7,
3428 r.ATTRIBUTE8,
3429 r.ATTRIBUTE9,
3430 r.ATTRIBUTE10,
3431 r.ATTRIBUTE11,
3432 r.ATTRIBUTE12,
3433 r.ATTRIBUTE13,
3434 r.ATTRIBUTE14,
3435 r.ATTRIBUTE15
3436 from
3437 bom_reference_designators r
3438 --added abs() was model_comp_seq would be -ve value
3439 where r.component_sequence_id = abs(model_comp_seq_id_arr(x1))
3440 and nvl(r.acd_type,0) <> 3;
3441 exception
3442 when others then
3443 IF PG_DEBUG <> 0 THEN
3444 oe_debug_pub.add ('The record for this designator and component sequence already exists in BOM_REFERENCE_DESIGNATORS', 1);
3445 END IF;
3446 end;
3447 IF PG_DEBUG <> 0 THEN
3448 oe_debug_pub.add ('For this record '||sql%rowcount||' records are inserted in bom_reference_designators', 1);
3449 END if;
3450 end loop; -- 3985173 : end of club_comp_seq cursor loop
3454 -- commented end if for bug 3793286 end if; -- 3985173
3451 prev_comp_item_id := component_item_id_arr(x1); -- 3985173
3452
3453
3455
3456
3457
3458
3459 end loop;
3460 end if;
3461
3462 IF PG_DEBUG <> 0 THEN
3463 oe_debug_pub.add ('create_bom_data_ml: ' || xTableName || '-'|| lStmtNum || ': ' || sql%rowcount,1 );
3464 END IF;
3465 -- end 3674833
3466
3467
3468 /*-----------------------------------------------------------+
3469 Update MTL_DESCR_ELEMENT_VALUES table
3470 +------------------------------------------------------------*/
3471
3472 xTableName := 'MTL_DESCR_ELEMENT_VALUES';
3473 lStmtNum := 330;
3474
3475 -- bugfix 2765635: This is a round-about fix for this issue by calling a custom-hook.
3476 -- Refer bug for details.
3477 -- begin bugfix
3478
3479 if CTO_CUSTOM_CATALOG_DESC.catalog_desc_method = 'C' then
3480 -- Call Custom API with details..
3481
3482 IF PG_DEBUG <> 0 THEN
3483 oe_debug_pub.add ('Prepare data for calling custom hook...');
3484 END IF;
3485
3486 DECLARE
3487 cursor ctg is
3488 select ELEMENT_NAME
3489 from mtl_descr_element_values
3490 where inventory_item_id = pConfigId;
3491
3492 l_catalog_dtls CTO_CUSTOM_CATALOG_DESC.CATALOG_DTLS_TBL_TYPE;
3493 l_params CTO_CUSTOM_CATALOG_DESC.INPARAMS;
3494 i NUMBER;
3495 original_count NUMBER;
3496 l_return_status VARCHAR2(1);
3497
3498 BEGIN
3499 i := 1;
3500 l_return_status := FND_API.G_RET_STS_SUCCESS;
3501
3502 for rec in ctg
3503 loop
3504 l_catalog_dtls(i).cat_element_name := rec.element_name;
3505 l_catalog_dtls(i).cat_element_value := NULL;
3506 IF PG_DEBUG <> 0 THEN
3507 oe_debug_pub.add ('l_catalog_dtls('||i||').cat_element_name = '||
3508 rec.element_name);
3509 END IF;
3510 i := i+1;
3511 end loop;
3512
3513 original_count := l_catalog_dtls.count;
3514
3515 -- bugfix 4081613: Do not execute the rest of the code if cursor ctg did not fetch any rows.
3516 if original_count > 0 then
3517 l_params.p_item_id := pConfigId;
3518 l_params.p_org_id := pOrgId;
3519
3520 IF PG_DEBUG <> 0 THEN
3521 oe_debug_pub.add ('Parameter passed: l_params.p_item_id = '||l_params.p_item_id ||
3522 '; l_params.p_org_id = '||l_params.p_org_id );
3523 END IF;
3524
3525 CTO_CUSTOM_CATALOG_DESC.user_catalog_desc (
3526 p_params => l_params,
3527 p_catalog_dtls => l_catalog_dtls,
3528 x_return_status => l_return_status);
3529
3530 if( l_return_status = FND_API.G_RET_STS_ERROR ) then
3531 IF PG_DEBUG <> 0 THEN
3532 oe_debug_pub.add ('CTO_CUSTOM_CATALOG_DESC.user_catalog_desc returned exp error');
3533 END IF;
3534 RAISE FND_API.G_EXC_ERROR ;
3535
3536 elsif( l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) then
3537 IF PG_DEBUG <> 0 THEN
3538 oe_debug_pub.add ('CTO_CUSTOM_CATALOG_DESC.user_catalog_desc returned unexp error');
3539 END IF;
3540 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
3541
3542 end if ;
3543
3544 if l_catalog_dtls.count <> original_count then
3545 IF PG_DEBUG <> 0 THEN
3546 oe_debug_pub.add ('Custom hook did not return same number of elements.'||
3547 'Original_count='||original_count||
3548 'New count = '||l_catalog_dtls.count);
3549 END IF;
3550 raise FND_API.G_EXC_ERROR;
3551 end if;
3552
3553 for k in l_catalog_dtls.first..l_catalog_dtls.last
3554 loop
3555 if l_catalog_dtls(k).cat_element_value is not null then
3556 IF PG_DEBUG <> 0 THEN
3557 oe_debug_pub.add ('l_catalog_dtls('||k||').cat_element_name = '||
3558 l_catalog_dtls(k).cat_element_name||
3559 '; l_catalog_dtls('||k||').cat_element_value = '||
3560 l_catalog_dtls(k).cat_element_value);
3561 END IF;
3562
3563 lStmtNum := 331;
3564
3565 update MTL_DESCR_ELEMENT_VALUES i
3566 set i.element_value = l_catalog_dtls(k).cat_element_value
3567 where i.inventory_item_id = pConfigId
3568 and i.element_name = l_catalog_dtls(k).cat_element_name;
3569 IF PG_DEBUG <> 0 THEN
3570 oe_debug_pub.add (xTableName || '-'|| lStmtNum || ': ' || sql%rowcount,1 );
3571 END IF;
3572
3573 end if;
3574 end loop;
3575
3576 end if; --bugfix 4081613
3577
3578 END;
3579
3580 elsif CTO_CUSTOM_CATALOG_DESC.catalog_desc_method = 'Y' then
3581 lStmtNum := 332;
3582 IF PG_DEBUG <> 0 THEN
3583 oe_debug_pub.add ('Std feature : Rollup lower level model catalog desc to top level');
3584 END IF;
3585 update MTL_DESCR_ELEMENT_VALUES i
3586 set i.element_value =
3587 ( select /*+ ORDERED */
3588 NVL(max(v.element_value),i.element_value)
3589 from
3590 bom_bill_of_materials bi,
3591 bom_inventory_components bc1,
3592 bom_inventory_components bc2,
3593 bom_dependent_desc_elements be,
3594 mtl_descr_element_values v
3598 and bc1.bill_sequence_id = bi.bill_sequence_id
3595 where bi.assembly_item_id = pConfigId
3596 and bi.organization_id = pOrgId
3597 and bi.alternate_bom_Designator is null
3599 and bc2.component_sequence_id = abs(bc1.model_comp_seq_id) -- previously last_update_login
3600 and be.bill_sequence_id = bc2.bill_sequence_id
3601 and be.element_name = i.element_name
3602 and v.inventory_item_id = bc1.component_item_id
3603 and v.element_name = i.element_name
3604 )
3605 where i.inventory_item_id = pConfigId;
3606 IF PG_DEBUG <> 0 THEN
3607 oe_debug_pub.add (xTableName || '-'|| lStmtNum || ': ' || sql%rowcount,1 );
3608 END IF;
3609 else
3610
3611 lStmtNum := 333;
3612 IF PG_DEBUG <> 0 THEN
3613 oe_debug_pub.add ('Std feature : DO NOT Rollup lower level model catalog desc to top level');
3614 END IF;
3615 update MTL_DESCR_ELEMENT_VALUES i
3616 set i.element_value =
3617 ( select /*+ ORDERED */
3618 NVL(max(v.element_value),i.element_value)
3619 from
3620 bom_bill_of_materials bi,
3621 bom_inventory_components bc1,
3622 bom_inventory_components bc2,
3623 bom_dependent_desc_elements be,
3624 mtl_descr_element_values v
3625 where bi.assembly_item_id = pConfigId
3626 and bi.organization_id = pOrgId
3627 and bi.alternate_bom_Designator is null
3628 and bc1.bill_sequence_id = bi.bill_sequence_id
3629 and bc2.component_sequence_id = abs(bc1.model_comp_seq_id) -- previously last_update_login
3630 and be.bill_sequence_id = bc2.bill_sequence_id
3631 and be.element_name = i.element_name
3632 and v.inventory_item_id = bc1.component_item_id
3633 and v.element_name = i.element_name
3634 -- bugfix 2590966
3635 -- Following code eliminates lower level configurations
3636 -- FP Bug Fix 4761813
3637 -- Tuned the query to user not exists for perfomance reason
3638 and not exists
3639 (
3640 SELECT 'x' FROM MTL_SYSTEM_ITEMS
3641 WHERE ORGANIZATION_ID = pOrgId
3642 AND BC1.COMPONENT_ITEM_ID = INVENTORY_ITEM_ID
3643 AND BASE_ITEM_ID IS NOT NULL
3644 AND BOM_ITEM_TYPE = 4
3645 AND REPLENISH_TO_ORDER_FLAG = 'Y'
3646 )
3647 -- end bugfix 2590966
3648 )
3649 where i.inventory_item_id = pConfigId;
3650 IF PG_DEBUG <> 0 THEN
3651 oe_debug_pub.add (xTableName || '-'|| lStmtNum || ': ' || sql%rowcount,1 );
3652 END IF;
3653 end if;
3654
3655 -- end bugfix 2765635
3656
3657 /*---------------------------------------------------------------------+
3658 Update descriptions of the config items in
3659 the MTL_SYSTEM_ITEMS
3660 +----------------------------------------------------------------------*/
3661
3662 lStmtNum := 350;
3663 xTableName := 'MTL_SYSTEM_ITMES';
3664 status := bmlupid_update_item_desc(pConfigid,
3665 pOrgId,
3666 xErrorMessage);
3667 IF PG_DEBUG <> 0 THEN
3668 oe_debug_pub.add('create_bom_data_ml: ' || 'bmlupid_update_item_desc returned ' || status,1 );
3669 END IF;
3670
3671 if status <> 0 then
3672 raise FND_API.G_EXC_ERROR;
3673 end if;
3674
3675 lStmtNum := 360;
3676 select common_bill_sequence_id
3677 into l_from_sequence_id
3678 from bom_bill_of_materials
3679 where assembly_item_id = pModelId
3680 and organization_id = pOrgId
3681 and alternate_bom_designator is NULL;
3682
3683 lStmtNum := 370;
3684 fnd_attached_documents2_pkg.copy_attachments(
3685 X_from_entity_name => 'BOM_BILL_OF_MATERIALS',
3686 X_from_pk1_value => l_from_sequence_id,
3687 X_from_pk2_value => '',
3688 X_from_pk3_value => '',
3689 X_from_pk4_value => '',
3690 X_from_pk5_value => '',
3691 X_to_entity_name => 'BOM_BILL_OF_MATERIALS',
3692 X_to_pk1_value => pConfigBillId,
3693 X_to_pk2_value => '',
3694 X_to_pk3_value => '',
3695 X_to_pk4_value => '',
3696 X_to_pk5_value => '',
3697 X_created_by => 1,
3698 X_last_update_login => '',
3699 X_program_application_id=> '',
3700 X_program_id => '',
3701 X_request_id => ''
3702 );
3703
3704 lStmtNum := 380;
3708 delete from bom_inventory_comps_interface
3705
3706 /* Clean up bom_inventory_comps_interface */
3707
3709 where bill_sequence_id = pConfigBillId;
3710
3711 return(1);
3712
3713 EXCEPTION
3714 WHEN NO_DATA_FOUND THEN
3715 xErrorMessage:='CTOCBOMB:'||lStmtNum||':'||substrb(sqlerrm,1,150);
3716
3717 -- Sushant Fixed bug #3374548
3718 /* Clean up bom_inventory_comps_interface */
3719 delete from bom_inventory_comps_interface
3720 where bill_sequence_id = pConfigBillId;
3721
3722 return(0);
3723
3724 when FND_API.G_EXC_ERROR then
3725 xErrorMessage:='CTOCBOMB:'||lStmtNum||':'||substrb(sqlerrm,1,150);
3726 xMessageName := 'CTO_CREATE_BOM_ERROR';
3727 IF PG_DEBUG <> 0 THEN
3728 oe_debug_pub.add('create_bom_data_ml: ' || 'eXpected Error: ' || xErrorMessage, 1);
3729 oe_debug_pub.add('create_bom_data_ml: ' || 'eXpected Error: ' || xMessageName , 1);
3730 END IF;
3731
3732 -- Sushant Fixed bug #3374548
3733 /* Clean up bom_inventory_comps_interface */
3734 delete from bom_inventory_comps_interface
3735 where bill_sequence_id = pConfigBillId;
3736
3737
3738 return(0);
3739
3740 when FND_API.G_EXC_UNEXPECTED_ERROR then -- bugfix 2765635
3741 xErrorMessage:='CTOCBOMB:'||lStmtNum||':'||substrb(sqlerrm,1,150);
3742 IF PG_DEBUG <> 0 THEN
3743 oe_debug_pub.add('Unexpected Error: ' || xErrorMessage);
3744 END IF;
3745
3746 -- Sushant Fixed bug #3374548
3747 /* Clean up bom_inventory_comps_interface */
3748 delete from bom_inventory_comps_interface
3749 where bill_sequence_id = pConfigBillId;
3750
3751 return(0);
3752
3753 WHEN OTHERS THEN
3754 xErrorMessage:='CTOCBOMB:'||lStmtNum||':'||substrb(sqlerrm,1,150);
3755 xMessageName := 'CTO_CREATE_BOM_ERROR';
3756 IF PG_DEBUG <> 0 THEN
3757 oe_debug_pub.add('create_bom_data_ml: ' || 'other Error: ' || xErrorMessage, 1);
3758 END IF;
3759
3760 -- Sushant Fixed bug #3374548
3761 /* Clean up bom_inventory_comps_interface */
3762 delete from bom_inventory_comps_interface
3763 where bill_sequence_id = pConfigBillId;
3764
3765 return(0);
3766
3767 END create_bom_data_ml;
3768
3769 --b2307936
3770
3771 /*------------------------------------------------+
3772 This procedure is called in a loop to update the
3773 Item Sequence Number on the components of the configuration
3774 BOM such that there are no duplicates, and the logical order
3775 in which they are selected from the model BOM is maintained.
3776 +------------------------------------------------*/
3777 PROCEDURE update_item_num(
3778 p_parent_bill_seq_id IN NUMBER,
3779 p_item_num IN OUT NOCOPY NUMBER, /* NOCOPY Project */
3780 p_org_id IN NUMBER,
3781 p_seq_increment IN NUMBER)
3782
3783 IS
3784
3785 CURSOR c_update_item_num(p_parent_bill_seq_id number) IS
3786 select component_sequence_id,
3787 component_item_id
3788 from bom_inventory_comps_interface
3789 where parent_bill_seq_id = p_parent_bill_seq_id
3790 FOR UPDATE OF item_num;
3791
3792 p_bill_seq_id number;
3793
3794 BEGIN
3795
3796 FOR v_update_item_num IN c_update_item_num(p_parent_bill_seq_id)
3797 LOOP
3798
3799 IF PG_DEBUG <> 0 THEN
3800 oe_debug_pub.add('update_item_num: ' || 'In update loop for item '||to_char(v_update_item_num.component_item_id), 2);
3801 END IF;
3802
3803 --
3804 -- update item_num of child of this model
3805 --
3806 update bom_inventory_comps_interface
3807 set item_num = p_item_num
3808 where current of c_update_item_num;
3809
3810 IF PG_DEBUG <> 0 THEN
3811 oe_debug_pub.add('update_item_num: ' || 'Updated item '||to_char(v_update_item_num.component_item_id)|| ' with item num '||to_char(p_item_num), 2);
3812 END IF;
3813
3814 p_item_num := p_item_num + p_seq_increment;
3815
3816 --
3817 -- get bill_sequence_id of child
3818 --
3819 BEGIN
3820
3821 select common_bill_sequence_id
3822 into p_bill_seq_id
3823 from bom_bill_of_materials
3824 where assembly_item_id = v_update_item_num.component_item_id
3825 and organization_id = p_org_id
3826 and alternate_bom_designator is null;
3827
3828 IF PG_DEBUG <> 0 THEN
3829 oe_debug_pub.add('update_item_num: ' || 'Calling update_item_num will p_bill_seq_id::'||to_char(p_bill_seq_id)||' and p_item_num::'||to_char(p_item_num), 2);
3830 END IF;
3831
3832 update_item_num(
3833 p_bill_seq_id,
3834 p_item_num,
3835 p_org_id,
3836 p_seq_increment);
3837
3838 EXCEPTION
3839 WHEN NO_DATA_FOUND THEN
3840 IF PG_DEBUG <> 0 THEN
3841 oe_debug_pub.add('update_item_num: ' || 'This component '||to_char(v_update_item_num.component_item_id)||' does not have a BOM', 2);
3842 END IF;
3843
3844 END;
3845
3846 END LOOP;
3847
3848 END update_item_num;
3849
3850
3851 function inherit_op_seq_ml(
3852 pLineId in oe_order_lines.line_id%TYPE := NULL,
3856 xErrorMessage out NOCOPY VARCHAR2,
3853 pOrgId in oe_order_lines.ship_from_org_id%TYPE := NULL,
3854 pModelId in bom_bill_of_materials.assembly_item_id%TYPE := NULL ,
3855 pConfigBillId in bom_inventory_components.bill_sequence_id%TYPE := NULL,
3857 xMessageName out NOCOPY VARCHAR2)
3858 return integer is
3859
3860 CURSOR c_incl_items_all_level ( xOrgId mtl_system_items.organization_id%TYPE,
3861 xLineId bom_cto_order_lines.line_id%TYPE,
3862 xConfigBillId bom_inventory_components.bill_sequence_id%TYPE ,
3863 xSchShpdt date,
3864 xEstReldt date ) IS
3865 select bbm.organization_id,
3866 nvl(bic.operation_seq_num,1) operation_seq_num , -- 2433862
3867 nvl(bet.operation_seq_num,1) parent_op_seq_num, -- 2433862
3868 bic.component_item_id,
3869 bic.item_num,
3870 decode(nvl(bic.basis_type,1),1,bic.component_quantity * (bcol1.ordered_quantity / bcol2.ordered_quantity ),bic.component_quantity) component_qty,
3871 bic.component_yield_factor,
3872 bic.component_remarks, --Bugfix 7188428
3873 bic.attribute_category,
3874 bic.attribute1,
3875 bic.attribute2,
3876 bic.attribute3,
3877 bic.attribute4,
3878 bic.attribute5,
3879 bic.attribute6,
3880 bic.attribute7,
3881 bic.attribute8,
3882 bic.attribute9,
3883 bic.attribute10,
3884 bic.attribute11,
3885 bic.attribute12,
3886 bic.attribute13,
3887 bic.attribute14,
3888 bic.attribute15,
3889 bic.so_basis,
3890 bic.include_in_cost_rollup,
3891 bic.check_atp,
3892 bic.required_for_revenue,
3893 bic.include_on_ship_docs,
3894 bic.include_on_bill_docs,
3895 bic.wip_supply_type,
3896 bic.component_sequence_id, -- model comp seq for later use
3897 bic.supply_subinventory,
3898 bic.supply_locator_id,
3899 bic.bom_item_type,
3900 bic.bill_sequence_id, -- parent_bill_seq_id
3901 bcol1.plan_level+1 plan_level,
3902 decode( -- 3222932
3903 greatest(bic.effectivity_date,sysdate),
3904 bic.effectivity_date ,
3905 bic.effectivity_date ,
3906 sysdate ) eff_date,
3907 nvl(bic.disable_date,g_futuredate) dis_date -- 3222932
3908 , nvl(bic.basis_type,1) basis_type /* LBM project */
3909 from bom_cto_order_lines bcol1, -- COMPONENT
3910 bom_cto_order_lines bcol2, -- MODEL
3911 mtl_system_items si1,
3912 mtl_system_items si2,
3913 bom_bill_of_materials bbm,
3914 bom_inventory_components bic, -- Components
3915 bom_inventory_components bic1, -- Parent
3916 bom_explosion_temp bet
3917 /*-----------------------------------------------------------------------------------------------------+
3918 For a multilevel model , ato_line_id=xLineId will not fetch included items of lower level
3919 non-phantom models so Parent_ATO_Line_id is used in the join condition.
3920 e.g. For a bill like this :
3921 MODEL1
3922 ..OC1
3923 ...MODEL2 ( Phantom Model )
3924 ....OC3
3925 .....MAND2
3926 ..OC2
3927 ...MODEL3 ( Non Phantom Model )
3928 ....OC4
3929 .....MAND2
3930
3931 Line id data in BCOL is as under :
3932
3933 ITEM LINE_ID LNK_TO_LINE_ID PRNT_ATO_LINE_ID ATO_LINE_ID
3934 ---------- ------- -------------- ---------------- -----------
3935 MODEL1 1 1 1
3936 ..OC1 2 1 1 1
3937 ...MODEL2 3 2 1 1
3938 ....OC3 4 3 1 1
3939 ..OC2 5 1 1 1
3940 ...MODEL3 6 5 1 1
3941 ....OC4 7 6 6 1
3942
3943 FOR join condition ato_line_id = xLine_id , MAND2 under OC4 will not be picked up while
3944 configuring MODEL3. So parent_atoline_id = xLine_id is used.
3945 +------------------------------------------------------------------------------------------------------------*/
3946 where bcol1.parent_ato_line_id = xLineId
3947 and bcol1.component_code = bet.component_code
3948 and si1.organization_id = xOrgId
3949 and bcol1.inventory_item_id = si1.inventory_item_id
3950 and si1.bom_item_type in (1,2) -- model, option class
3951 and si2.inventory_item_id = bcol2.inventory_item_id
3952 and si2.organization_id = si1.organization_id
3953 and si2.bom_item_type = 1
3954 -- Bugfix 2389283 : Commented bcol1.line_id = bcol2.line_id condition
3955 and (bcol1.parent_ato_line_id = bcol2.line_id
3956 and ( bcol1.bom_item_type <> 1
3957 or ( bcol1.bom_item_type = 1
3958 and nvl(bcol1.wip_supply_type, 0) = 6
3959 )
3960 )
3961 )
3962 -- or bcol1.line_id = bcol2.line_id )
3963 and bet.bill_sequence_id = xConfigBillId
3964 and bet.top_bill_sequence_id = xConfigBillId
3965 and bic1.component_sequence_id = bcol1.component_sequence_id
3966 and bic1.bom_item_type in (1,2)
3967 and bbm.assembly_item_id = bic1.component_item_id
3971 and bic.optional = 2
3968 and bbm.organization_id = si1.organization_id
3969 and bbm.alternate_bom_designator is NULL
3970 and bic.bill_sequence_id = DECODE(bbm.common_bill_sequence_id,bbm.bill_sequence_id,bbm.bill_sequence_id,bbm.common_bill_sequence_id)
3972 and bic.bom_item_type = 4
3973 -- and bic.effectivity_date <= greatest( NVL(xSchShpdt,sysdate),sysdate) /* New Approach for effectivity dates */
3974 and bic.implementation_date is not null
3975 -- and NVL(bic.disable_date,NVL(xEstReldt, SYSDATE)+1) > NVL(xEstReldt,SYSDATE) /* NEw Approach for effectivity dates*/
3976 -- and NVL(bic.disable_date,SYSDATE) >= SYSDATE; /* New approach for effectivity dates */
3977 and ( bic.disable_date is null or
3978 (bic.disable_date is not null and bic.disable_date >= sysdate )) ;/* New Approach for Effectivity Dates */
3979
3980 CURSOR c_model_oc_oi_rows(xConfigBillId bom_inventory_components.bill_sequence_id%TYPE) IS
3981 SELECT /*+ INDEX ( BOM_EXPLOSION_TEMP BOM_EXPLOSION_TEMP_N11) */
3982 nvl(operation_seq_num,1) operation_seq_num, -- 2433862
3983 component_code,
3984 rowid
3985 from bom_explosion_temp
3986 where bill_sequence_id = xConfigBillId
3987 and component_code IS NOT NULL
3988 ORDER BY component_code;
3989
3990 lStmtNumber number;
3991 lCnt number;
3992
3993 v_zero_qty_count number ;
3994 v_zero_qty_component number ;
3995
3996
3997 l_token1 CTO_MSG_PUB.token_tbl;
3998 v_model_item_name varchar2(2000) ;
3999
4000
4001 v_overlap_check number := 0 ;
4002
4003 TYPE num_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
4004 TYPE date_tab IS TABLE OF DATE INDEX BY BINARY_INTEGER;
4005
4006 v_t_overlap_comp_item_id num_tab;
4007 v_t_overlap_src_op_seq_num num_tab;
4008 v_t_overlap_src_eff_date date_tab;
4009 v_t_overlap_src_disable_date date_tab;
4010 v_t_overlap_dest_op_seq_num num_tab;
4011 v_t_overlap_dest_eff_date date_tab;
4012 v_t_overlap_dest_disable_date date_tab;
4013 l_token2 CTO_MSG_PUB.token_tbl;
4014 l_model_name varchar2(1000);
4015
4016 BEGIN
4017
4018
4019
4020
4021
4022
4023 lStmtNumber := 520;
4024
4025 --
4026 -- Insert Option Classes and Option Items
4027 -- Compare to last insert , here we have an addl column
4028 -- component_code to insert comp_code of classes /items
4029 -- from bcol
4030 --
4031
4032 INSERT INTO BOM_EXPLOSION_TEMP
4033 ( top_bill_sequence_id,
4034 organization_id,
4035 plan_level,
4036 sort_order,
4037 operation_seq_num,
4038 component_item_id,
4039 item_num,
4040 component_quantity,
4041 component_yield_factor,
4042 component_remarks, --Bugfix 7188428
4043 context, -- mapped to attribute_category in bic interface
4044 attribute1,
4045 attribute2,
4046 attribute3,
4047 attribute4,
4048 attribute5,
4049 attribute6,
4050 attribute7,
4051 attribute8,
4052 attribute9,
4053 attribute10,
4054 attribute11,
4055 attribute12,
4056 attribute13,
4057 attribute14,
4058 attribute15,
4059 planning_factor,
4060 select_quantity, -- mapped to quantity_related of bic interface
4061 so_basis,
4062 optional, -- mapped to optional_on_model of bic interface
4063 mutually_exclusive_options,
4064 include_in_rollup_flag, -- mapped to include_in_cost rollup of bic interface
4065 check_atp,
4066 shipping_allowed,
4067 required_to_ship,
4068 required_for_revenue,
4069 include_on_ship_docs,
4070 include_on_bill_docs,
4071 component_sequence_id,
4072 bill_sequence_id,
4073 wip_supply_type,
4074 pick_components,
4075 base_item_id, -- mapped to model_comp_seq_id of bic_interface
4076 supply_subinventory,
4077 supply_locator_id,
4078 bom_item_type,
4079 component_code, -- Additional
4080 line_id, -- 2814257
4081 top_item_id,
4082 effectivity_date, -- 3222932
4083 disable_date -- 3222932-- mapped to parent_bill_seq_id of bic interface
4084 , basis_type /* LBM project */
4085 ,assembly_item_id /* Bug Fix: 4147224 */
4086 )
4087 select pConfigBillId, -- top bill sequence id
4088 bcol2.ship_from_org_id, -- Model's organization_id
4089 (bcol1.plan_level-bcol2.plan_level), -- Plan Level
4090 '1', -- Sort Order
4091 nvl(ic1.operation_seq_num,1),
4092 decode(bcol1.config_item_id, NULL, ic1.component_item_id,bcol1.config_item_id),
4093 ic1.item_num,
4094 Round(
4095 CTO_UTILITY_PK.convert_uom( bcol1.order_quantity_uom, msi_child.primary_uom_code, bcol1.ordered_quantity , msi_child.inventory_item_id )
4096 / CTO_UTILITY_PK.convert_uom(bcol2.order_quantity_uom, msi_parent.primary_uom_code, NVL(bcol2.ordered_quantity,1) , msi_parent.inventory_item_id )
4097 , 7) , -- qty = comp_qty / model_qty /* Decimal-Qty Support for Option Items */
4098 ic1.component_yield_factor,
4099 ic1.component_remarks, --Bugfix 7188428
4103 ic1.attribute3,
4100 ic1.attribute_category,
4101 ic1.attribute1,
4102 ic1.attribute2,
4104 ic1.attribute4,
4105 ic1.attribute5,
4106 ic1.attribute6,
4107 ic1.attribute7,
4108 ic1.attribute8,
4109 ic1.attribute9,
4110 ic1.attribute10,
4111 ic1.attribute11,
4112 ic1.attribute12,
4113 ic1.attribute13,
4114 ic1.attribute14,
4115 ic1.attribute15,
4116 100, -- planning_factor
4117 2, -- quantity_related
4118 decode(bcol1.config_item_id, NULL,
4119 decode(ic1.bom_item_type,4,ic1.so_basis,2),2), -- so_basis
4120 1, -- optional
4121 2, -- mutually_exclusive_options
4122 decode(bcol1.config_item_id, NULL,
4123 decode(ic1.bom_item_type,4,
4124 ic1.include_in_cost_rollup, 2),1), -- Cost_rollup
4125 decode(bcol1.config_item_id, NULL,
4126 decode(ic1.bom_item_type,4,
4127 ic1.check_atp, 2),2), -- check_atp
4128 2, -- shipping_allowed = NO
4129 2, -- required_to_ship = NO
4130 ic1.required_for_revenue,
4131 ic1.include_on_ship_docs,
4132 ic1.include_on_bill_docs,
4133 bom_inventory_components_s.nextval, -- component sequence id
4134 pConfigBillId, -- bill sequence id
4135 ic1.wip_supply_type,
4136 2, -- pick_components = NO
4137 decode(bcol1.config_item_id, NULL, (-1)*ic1.component_sequence_id, ic1.component_sequence_id), -- saved model comp seq for later use. If config item, then save model comp seq id as positive, otherwise negative.
4138 ic1.supply_subinventory,
4139 ic1.supply_locator_id,
4140 decode(bcol1.config_item_id, NULL, ic1.bom_item_type, 4),
4141 bcol1.component_code,
4142 bcol1.line_id, -- 2814257
4143 ic1.bill_sequence_id,
4144 decode( -- 3222932
4145 greatest(ic1.effectivity_date,sysdate),
4146 ic1.effectivity_date ,
4147 ic1.effectivity_date ,
4148 sysdate ),
4149 nvl(ic1.disable_date,g_futuredate) -- 3222932
4150 , nvl(ic1.basis_type,1) /* LBM project */
4151 ,bcol3.inventory_item_id /* Bug Fix : 4147224 */
4152 from bom_inventory_components ic1,
4153 bom_cto_order_lines bcol1, -- Option
4154 bom_cto_order_lines bcol2, -- Parent-Model
4155 bom_cto_order_lines bcol3 , -- Parent-component
4156 mtl_system_items msi_child,
4157 mtl_system_items msi_parent
4158 where ic1.bill_sequence_id = (
4159 select common_bill_sequence_id
4160 from bom_bill_of_materials bbm
4161 where organization_id = pOrgId
4162 and alternate_bom_designator is null
4163 and assembly_item_id =(
4164 select distinct assembly_item_id
4165 from bom_bill_of_materials bbm1,
4166 bom_inventory_components bic1
4167 where bbm1.common_bill_sequence_id = bic1.bill_sequence_id
4168 and component_sequence_id = bcol1.component_sequence_id
4169 and bbm1.assembly_item_id = bcol3.inventory_item_id ))
4170 and ic1.component_item_id = bcol1.inventory_item_id
4171 and msi_child.inventory_item_id = bcol1.inventory_item_id
4172 and msi_child.organization_id = pOrgId
4173 and msi_parent.inventory_item_id = bcol2.inventory_item_id
4174 and msi_parent.organization_id = pOrgId
4175 -- and ic1.effectivity_date <= g_SchShpDate /* New Approach for effectivity dates */
4176 and ic1.implementation_date is not null --bug 4244147
4177 -- and NVL(ic1.disable_date, (g_EstRelDate + 1)) >= greatest( nvl( g_EstRelDate, sysdate) , sysdate) /* bug 3389846 */
4178 /*
4179 and ( ic1.disable_date is null or
4180 (ic1.disable_date is not null and ic1.disable_date >= greatest( nvl( g_EstRelDate, sysdate ) , sysdate )) #3389846
4181 )
4182 */
4183 and ( ic1.disable_date is null or
4184 (ic1.disable_date is not null and ic1.disable_date >= sysdate )) /* New Approach for Effectivity Dates */
4185 and (( ic1.optional = 1 and ic1.bom_item_type = 4)
4186 or
4187 ( ic1.bom_item_type in (1,2)))
4188 and bcol1.ordered_quantity <> 0
4189 and bcol1.line_id <> bcol2.line_id -- not the top ato model
4190 and bcol1.parent_ato_line_id = bcol2.line_id
4191 and bcol1.parent_ato_line_id is not null
4192 and bcol1.link_to_line_id is not null
4193 and bcol2.line_id = pLineId
4194 and bcol2.ship_from_org_id = bcol1.ship_from_org_id
4195 and (bcol3.parent_ato_line_id = bcol1.parent_ato_line_id
4196 or
4197 bcol3.line_id = bcol1.parent_ato_line_id)
4198 and bcol3.line_id = bcol1.link_to_line_id;
4199
4200 lCnt := sql%rowcount ;
4204 END IF;
4201
4202 IF PG_DEBUG <> 0 THEN
4203 oe_debug_pub.add ('inherit_op_seq_ml: ' || 'Second -- Inserted in BE Temp ' || lCnt ||' Option item/Option class rows with bill seq id as '|| pConfigBillId,1);
4205
4206
4207
4208
4209
4210
4211
4212
4213 select /*+ INDEX ( BOM_EXPLOSION_TEMP BOM_EXPLOSION_TEMP_N11) */
4214 count(*) into v_zero_qty_count from bom_explosion_temp
4215 where bill_sequence_id = pConfigBillId and component_quantity = 0 ;
4216
4217 oe_debug_pub.add( 'MODELS: CHECK Raise Exception for Zero QTY Count ' || v_zero_qty_count , 1 ) ;
4218
4219 if( v_zero_qty_count > 0 ) then
4220
4221 oe_debug_pub.add( 'Inherit_op_seq_ml:: SHOULD Raise Exception for Zero QTY Count ' || v_zero_qty_count , 1 ) ;
4222
4223
4224 select concatenated_segments into v_model_item_name
4225 from mtl_system_items_kfv
4226 where inventory_item_id = pModelId
4227 and rownum = 1 ;
4228
4229
4230 l_token1(1).token_name := 'MODEL_NAME';
4231 l_token1(1).token_value := v_model_item_name ;
4232
4233
4234 cto_msg_pub.cto_message('BOM','CTO_ZERO_BOM_COMP' , l_token1 );
4235
4236 raise fnd_api.g_exc_error;
4237
4238
4239
4240
4241 end if ;
4242
4243
4244
4245
4246
4247
4248
4249
4250 /* begin Extend Effectivity Dates for Option Items with disable date */
4251
4252
4253 update bom_explosion_temp set disable_date = g_futuredate
4254 where ( component_item_id , operation_seq_num, nvl(assembly_item_id,-1) , disable_date) in
4255 ( select component_item_id, operation_seq_num, nvl(assembly_item_id,-1), max(disable_date)
4256 from bom_inventory_comps_interface
4257 where bill_sequence_id = pConfigBillId
4258 group by component_item_id, operation_seq_num, assembly_item_id)
4259 and disable_date <> g_futuredate ;
4260
4261
4262 /* end Extend Effectivity Dates for Option Items with disable date */
4263
4264
4265
4266
4267
4268
4269
4270 /* Effectivity Dates changes */
4271 /* moved Mandatory comps code to insert components after ordered items */
4272
4273 lStmtNumber := 510;
4274
4275 /*Insert Incl. items under Base Model */
4276
4277 INSERT INTO bom_explosion_temp
4278 (
4279 top_bill_sequence_id,
4280 organization_id,
4281 plan_level,
4282 sort_order,
4283 operation_seq_num,
4284 component_item_id,
4285 item_num,
4286 component_quantity,
4287 component_yield_factor,
4288 component_remarks, --Bugfix 7188428
4289 context, -- mapped to attribute_category in bic interface
4290 attribute1,
4291 attribute2,
4292 attribute3,
4293 attribute4,
4294 attribute5,
4295 attribute6,
4296 attribute7,
4297 attribute8,
4298 attribute9,
4299 attribute10,
4300 attribute11,
4301 attribute12,
4302 attribute13,
4303 attribute14,
4304 attribute15,
4305 planning_factor,
4306 select_quantity, -- mapped to quantity_related of bic interface
4307 so_basis,
4308 optional, -- mapped to optional_on_model in bic interface
4309 mutually_exclusive_options,
4310 include_in_rollup_flag, -- mapped to include_in_cost rollup of bic interface
4311 check_atp,
4312 shipping_allowed,
4313 required_to_ship,
4314 required_for_revenue,
4315 include_on_ship_docs,
4316 include_on_bill_docs,
4317 component_sequence_id,
4318 bill_sequence_id,
4319 wip_supply_type,
4320 pick_components,
4321 base_item_id, -- mapped to model_comp_seq_id of bic_interface
4322 supply_subinventory,
4323 supply_locator_id,
4324 bom_item_type,
4325 top_item_id,
4326 effectivity_date, -- 3222932
4327 disable_date -- 3222932-- mapped to parent_bill_seq_id in bic interface
4328 , basis_type /* LBM project */
4329 )
4330 select pConfigBillId, -- top bill sequence id
4331 bbm.organization_id, -- Model's organization_id
4332 1, -- Plan Level, should be 0+1 for model's smc's
4333 '1', -- Sort Order
4334 nvl(bic.operation_seq_num,1),
4335 bic.component_item_id,
4336 bic.item_num,
4337 bic.component_quantity component_qty,
4338 /*
4339 please check whether this change is rquired
4340 decode( nvl(bic.basis_type,1), 1 , Round( ( bic.component_quantity * ( bcol1.ordered_quantity
4341 / bcol2.ordered_quantity)), 7 ) , Round(bic.component_quantity , 7 ) ) , * Decimal-Qty Support for Option Items, LBM project
4342 */
4343 bic.component_yield_factor,
4344 bic.component_remarks, --Bugfix 7188428
4345 bic.attribute_category,
4346 bic.attribute1,
4347 bic.attribute2,
4348 bic.attribute3,
4349 bic.attribute4,
4350 bic.attribute5,
4351 bic.attribute6,
4352 bic.attribute7,
4353 bic.attribute8,
4354 bic.attribute9,
4355 bic.attribute10,
4356 bic.attribute11,
4360 bic.attribute15,
4357 bic.attribute12,
4358 bic.attribute13,
4359 bic.attribute14,
4361 100, -- planning_factor
4362 2, -- quantity_related
4363 bic.so_basis,
4364 2, -- optional
4365 2, -- mutually_exclusive_options
4366 bic.include_in_cost_rollup,
4367 bic.check_atp,
4368 2, -- shipping_allowed = NO
4369 2, -- required_to_ship = NO
4370 bic.required_for_revenue,
4371 bic.include_on_ship_docs,
4372 bic.include_on_bill_docs,
4373 bom_inventory_components_s.nextval, -- component sequence id
4374 pConfigBillId, -- bill sequence id
4375 bic.wip_supply_type,
4376 2, -- pick_components = NO
4377 (-1)*bic.component_sequence_id, -- model comp seq for later use
4378 bic.supply_subinventory,
4379 bic.supply_locator_id,
4380 bic.bom_item_type,
4381 bic.bill_sequence_id,
4382 decode( -- 3222932
4383 greatest(bic.effectivity_date,sysdate),
4384 bic.effectivity_date ,
4385 bic.effectivity_date ,
4386 sysdate ),
4387 nvl(bic.disable_date,g_futuredate) -- 3222932
4388 , nvl(bic.basis_type,1) /* LBM project */
4389 from bom_cto_order_lines bcol,
4390 bom_bill_of_materials bbm,
4391 bom_inventory_components bic
4392 where bcol.line_id = pLineId
4393 and bcol.ordered_quantity <> 0
4394 -- bugfix 2389283 and instr(bcol.component_code,'-',1,1) = 0 /* To identify Top Model */
4395 and bcol.inventory_item_id = pModelId
4396 and bbm.organization_id = pOrgId
4397 and bcol.inventory_item_id = bbm.assembly_item_id
4398 and bbm.alternate_bom_designator is NULL
4399 and bbm.common_bill_sequence_id = bic.bill_sequence_id
4400 and bic.optional = 2
4401 and bic.bom_item_type = 4
4402 -- and bic.effectivity_date <= greatest( NVL(g_SchShpDate,sysdate),sysdate) /* New Approach for effectivity dates */
4403 and bic.implementation_date is not null
4404 /*
4405 and NVL(bic.disable_date,NVL(g_EstRelDate, SYSDATE)+1) > NVL(g_EstRelDate,SYSDATE) NEW approach for effectivity dates
4406 and NVL(bic.disable_date,SYSDATE) >= SYSDATE; New approach for effectivity dates
4407 */
4408 and ( bic.disable_date is null or
4409 (bic.disable_date is not null and bic.disable_date >= sysdate )) ; /* New Approach for Effectivity Dates */
4410
4411 lCnt := sql%rowcount ;
4412
4413 IF PG_DEBUG <> 0 THEN
4414 oe_debug_pub.add ('inherit_op_seq_ml: ' || 'First -- Inserted in BE Temp ' || lCnt ||' Incl Item rows with bill seq id as '|| pConfigBillId,1);
4415 END IF;
4416
4417
4418
4419
4420
4421 lStmtNumber := 530;
4422
4423 /*+------------------------------------------------------------------------------------------------------------
4424 Open cursor c_model_oc_oi_rows(xConfigBillId) for rows inserted in bet
4425 This will update all Option Class and Option Item rows
4426 Mandatory items directly under model will already have op_seq_num. For these mandatory items we don't need to
4427 inherit the op_seq_num since they are directly under model.
4428 The component_code for these mand items are NULL as they are not in BCOL.
4429 so , mandatory item rows from bet will not be selected by c_model_oc_oi_rows cursor and will not be updated
4430 Explanation :
4431 For a Bill structure like this :
4432 55631 1.1.0 KS-ATO-MODEL1*6389
4433 55627 1.1 KS-ATO-MODEL1
4434 55628 1.1.1 KS-ATO-MODEL3
4435 55629 1.1.2 KS-ATO-OC1
4436 55630 1.1.3 KS-ATO-OI1
4437 BCOL.LINE_ID BCOL.COMP_SEQ_ID BCOL.COMPONENT_CODE
4438 ---------- ---------------- ---------------
4439 55627 21053 6280
4440 55628 21322 6280-6376
4441 55629 21303 6280-6376-6282
4442 55630 21035 6280-6376-6282-6288
4443 Now , instr( bet.component_code,'-',1,2 ) will select line_id 55629 and 55630 as those rows are actual candidates for
4444 op_seq_num update. 55627 was not inserted in bet as it is the base model row and we are not selecting 55628 since this
4445 is directly under the top model and inheritence logic does not apply to this line.
4446 Inheritence starts from second level . First level components under top model will always have op_seq_num.
4447
4448 +------------------------------------------------------------------------------------------------------------+*/
4449
4450 FOR r1 in c_model_oc_oi_rows(pConfigBillId) LOOP
4451 IF r1.operation_seq_num = 1 AND instr(r1.component_code,'-',1,2)<>0 THEN
4452 UPDATE bom_explosion_temp bet
4453 SET bet.operation_seq_num = (
4454 SELECT /*+ INDEX ( BOM_EXPLOSION_TEMP BOM_EXPLOSION_TEMP_N11) */
4455 nvl(operation_seq_num,1) -- 2433862
4456 FROM bom_explosion_temp
4457 WHERE component_code = substr(bet.component_code,1,to_number(instr(bet.component_code,'-',-1,1))-1)
4458 AND bill_sequence_id = pConfigBillId
4459 AND top_bill_sequence_id = pConfigBillId)
4463 END LOOP;
4460 WHERE component_code = r1.component_code
4461 AND rowid = r1.rowid;
4462 END IF;
4464
4465 lStmtNumber := 540;
4466
4467 /* Open cursor c_incl_items_all_level */
4468
4469 FOR r2 in c_incl_items_all_level (pOrgId ,pLineId ,pConfigBillId,g_SchShpDate,g_EstRelDate ) LOOP
4470 INSERT INTO bom_explosion_temp
4471 ( top_bill_sequence_id,
4472 organization_id,
4473 plan_level,
4474 sort_order,
4475 operation_seq_num,
4476 component_item_id,
4477 item_num,
4478 component_quantity,
4479 component_yield_factor,
4480 component_remarks, --Bugfix 7188428
4481 context, -- mapped to attribute_category in bic interface
4482 attribute1,
4483 attribute2,
4484 attribute3,
4485 attribute4,
4486 attribute5,
4487 attribute6,
4488 attribute7,
4489 attribute8,
4490 attribute9,
4491 attribute10,
4492 attribute11,
4493 attribute12,
4494 attribute13,
4495 attribute14,
4496 attribute15,
4497 planning_factor,
4498 select_quantity, -- mapped to quantity_related of bic interface
4499 so_basis,
4500 optional, -- mapped to optional_on_model of bic interface
4501 mutually_exclusive_options,
4502 include_in_rollup_flag, -- mapped to include_in_cost rollup of bic interface
4503 check_atp,
4504 shipping_allowed,
4505 required_to_ship,
4506 required_for_revenue,
4507 include_on_ship_docs,
4508 include_on_bill_docs,
4509 component_sequence_id,
4510 bill_sequence_id,
4511 wip_supply_type,
4512 pick_components,
4513 base_item_id, -- mapped to model_comp_seq_id of bic_interface
4514 supply_subinventory,
4515 supply_locator_id,
4516 bom_item_type,
4517 top_item_id, -- mapped to parent_bill_seq_id of bic interface
4518 effectivity_date, -- 3222932
4519 disable_date -- 3222932
4520 , basis_type /* LBM project */
4521 )
4522 VALUES
4523 ( pConfigBillId, -- top bill sequence id
4524 r2.organization_id, -- Model's organization_id
4525 r2.plan_level, -- Plan Level
4526 '1', -- Sort Order
4527 DECODE(r2.operation_seq_num,1,r2.parent_op_seq_num,r2.operation_seq_num),
4528 r2.component_item_id,
4529 r2.item_num,
4530 r2.component_qty,
4531 r2.component_yield_factor,
4532 r2.component_remarks, --Bugfix 7188428
4533 r2.attribute_category,
4534 r2.attribute1,
4535 r2.attribute2,
4536 r2.attribute3,
4537 r2.attribute4,
4538 r2.attribute5,
4539 r2.attribute6,
4540 r2.attribute7,
4541 r2.attribute8,
4542 r2.attribute9,
4543 r2.attribute10,
4544 r2.attribute11,
4545 r2.attribute12,
4546 r2.attribute13,
4547 r2.attribute14,
4548 r2.attribute15,
4549 100, -- planning_factor
4550 2, -- quantity_related
4551 r2.so_basis,
4552 2, -- optional
4553 2, -- mutually_exclusive_options
4554 r2.include_in_cost_rollup,
4555 r2.check_atp,
4556 2, -- shipping_allowed = NO
4557 2, -- required_to_ship = NO
4558 r2.required_for_revenue,
4559 r2.include_on_ship_docs,
4560 r2.include_on_bill_docs,
4561 bom_inventory_components_s.nextval, -- component sequence id
4562 pConfigBillId, -- bill sequence id
4563 r2.wip_supply_type,
4564 2, -- pick_components = NO
4565 (-1)*r2.component_sequence_id, -- model comp seq for later use
4566 r2.supply_subinventory,
4567 r2.supply_locator_id,
4568 r2.bom_item_type,
4569 r2.bill_sequence_id, -- parent_bill_seq_id
4570 r2.eff_date, -- 3222932
4571 r2.dis_date -- 3222932
4572 , r2.basis_type /* LBM project */
4573 );
4574 lCnt := sql%rowcount ;
4575 IF PG_DEBUG <> 0 THEN
4576 oe_debug_pub.add ('inherit_op_seq_ml: ' || 'INSIDE Loop : Inserted in BE Temp ' || lCnt ||' manadatory item rows with bill seq id as '|| pConfigBillId,1);
4577 END IF;
4578 END LOOP;
4579
4580
4581 lStmtNumber := 550;
4582
4583 /*Insert into bic interface*/
4584 insert into BOM_INVENTORY_COMPS_INTERFACE
4585 ( operation_seq_num,
4586 component_item_id,
4587 last_update_date,
4588 last_updated_by,
4589 creation_date,
4590 created_by,
4591 last_update_login,
4592 item_num,
4593 component_quantity,
4594 component_yield_factor,
4595 component_remarks,
4596 effectivity_date,
4597 change_notice,
4598 implementation_date,
4602 attribute2,
4599 disable_date,
4600 attribute_category,
4601 attribute1,
4603 attribute3,
4604 attribute4,
4605 attribute5,
4606 attribute6,
4607 attribute7,
4608 attribute8,
4609 attribute9,
4610 attribute10,
4611 attribute11,
4612 attribute12,
4613 attribute13,
4614 attribute14,
4615 attribute15,
4616 planning_factor,
4617 quantity_related,
4618 so_basis,
4619 optional,
4620 mutually_exclusive_options,
4621 include_in_cost_rollup,
4622 check_atp,
4623 shipping_allowed,
4624 required_to_ship,
4625 required_for_revenue,
4626 include_on_ship_docs,
4627 include_on_bill_docs,
4628 low_quantity,
4629 high_quantity,
4630 acd_type,
4631 old_component_sequence_id,
4632 component_sequence_id,
4633 bill_sequence_id,
4634 request_id,
4635 program_application_id,
4636 program_id,
4637 program_update_date,
4638 wip_supply_type,
4639 pick_components,
4640 model_comp_seq_id,
4641 supply_subinventory,
4642 supply_locator_id,
4643 bom_item_type,
4644 revised_item_sequence_id, -- 2814257
4645 optional_on_model,
4646 plan_level,
4647 parent_bill_seq_id,
4648 assembly_item_id /* Bug Fix: 4147224 */
4649 , basis_type, /* LBM changes */
4650 batch_id
4651 )
4652 select /*+ INDEX ( BOM_EXPLOSION_TEMP BOM_EXPLOSION_TEMP_N11) */
4653 nvl(operation_seq_num,1), -- 2433862
4654 component_item_id,
4655 SYSDATE, -- last_updated_date
4656 1, -- last_updated_by
4657 SYSDATE, -- creation_date
4658 1, -- created_by
4659 1, -- last_update_login
4660 item_num,
4661 component_quantity,
4662 component_yield_factor,
4663 component_remarks, --Bugfix 7188428
4664 --NULL, -- component_remark
4665 -- 3222932 TRUNC(SYSDATE), -- effective date
4666 effectivity_date,
4667 NULL, -- change notice
4668 SYSDATE, -- implementation_date
4669 -- 3222932 NULL, -- disable date
4670 disable_date,
4671 context, -- mapped to attribute_category in bic interface
4672 attribute1,
4673 attribute2,
4674 attribute3,
4675 attribute4,
4676 attribute5,
4677 attribute6,
4678 attribute7,
4679 attribute8,
4680 attribute9,
4681 attribute10,
4682 attribute11,
4683 attribute12,
4684 attribute13,
4685 attribute14,
4686 attribute15,
4687 planning_factor,
4688 select_quantity, -- mapped to quantity_related of bic interface
4689 so_basis,
4690 2, -- optional
4691 mutually_exclusive_options,
4692 include_in_rollup_flag, -- mapped to include_in_cost rollup of bic interface
4693 check_atp,
4694 shipping_allowed,
4695 required_to_ship,
4696 required_for_revenue,
4697 include_on_ship_docs,
4698 include_on_bill_docs,
4699 NULL, -- low_quantity
4700 NULL, -- high_quantity
4701 NULL, -- acd_type
4702 NULL, -- old_component_sequence_id
4703 component_sequence_id,
4704 bill_sequence_id,
4705 NULL, -- request_id
4706 NULL, -- program_application_id
4707 NULL, -- program_id
4708 NULL, -- program_update_date
4709 wip_supply_type,
4710 pick_components,
4711 base_item_id, -- mapped to model_comp_seq_id of bic_interface
4712 supply_subinventory,
4713 supply_locator_id,
4714 bom_item_type,
4715 line_id, -- 2814257
4716 optional,
4717 plan_level,
4718 top_item_id,
4719 assembly_item_id /* Bug Fix: 4147224 */
4720 , nvl(basis_type,1), /* LBM project */
4721 cto_msutil_pub.bom_batch_id
4722 from bom_explosion_temp
4723 where bill_sequence_id = pConfigBillId;
4724
4725 lCnt := sql%rowcount ;
4726 IF PG_DEBUG <> 0 THEN
4727 oe_debug_pub.add ('inherit_op_seq_ml: ' || 'Final - Inserted in BIC Interface ' || lCnt ||' rows from BET',1);
4728 END IF;
4729
4730
4731
4732
4733
4734
4735
4736
4737
4738 /* begin Check for Overlapping Effectivity Dates */
4739 v_overlap_check := 0 ;
4740
4741 begin
4742 select 1 into v_overlap_check
4746 where bill_sequence_id = pConfigBillId
4743 from dual
4744 where exists
4745 ( select * from bom_inventory_comps_interface
4747 group by component_item_id, assembly_item_id
4748 having count(distinct operation_seq_num) > 1
4749 );
4750 exception
4751 when others then
4752 v_overlap_check := 0 ;
4753 end;
4754
4755
4756 if(v_overlap_check = 1) then
4757
4758 begin
4759 select s1.component_item_id,
4760 s1.operation_seq_num, s1.effectivity_date, s1.disable_date,
4761 s2.operation_Seq_num , s2.effectivity_date, s2.disable_date
4762 BULK COLLECT INTO
4763 v_t_overlap_comp_item_id,
4764 v_t_overlap_src_op_seq_num, v_t_overlap_src_eff_date, v_t_overlap_src_disable_date ,
4765 v_t_overlap_dest_op_seq_num , v_t_overlap_dest_eff_date, v_t_overlap_dest_disable_date
4766 from bom_inventory_comps_interface s1 , bom_inventory_comps_interface s2
4767 where s1.component_item_id = s2.component_item_id and s1.assembly_item_id = s2.assembly_item_id
4768 --and s1.effectivity_date between s2.effectivity_date and s2.disable_date
4769 and s1.effectivity_date > s2.effectivity_date --Bugfix 6603382
4770 and s1.effectivity_date < s2.disable_date --Bugfix 6603382
4771 and s1.bill_sequence_id = pConfigBillId --Bugfix 6603382
4772 and s2.bill_sequence_id = pConfigBillId --Bugfix 6603382
4773 and s1.component_sequence_id <> s2.component_sequence_id ;
4774
4775
4776 exception
4777 when others then
4778 null ;
4779 end ;
4780
4781
4782 if( v_t_overlap_src_op_seq_num.count > 0 ) then
4783 for i in v_t_overlap_src_op_seq_num.first..v_t_overlap_src_op_seq_num.last
4784 loop
4785 IF PG_DEBUG <> 0 THEN
4786 oe_debug_pub.add (' The following components have overlapping dates ', 1);
4787 oe_debug_pub.add (' COMP ' || ' OP SEQ' || 'EFFECTIVITY DT ' || ' DISABLE DT ' || ' OVERLAPS ' ||
4788 ' OP SEQ' || 'EFFECTIVITY DT ' || ' DISABLE DT ' , 1);
4789 /*
4790 oe_debug_pub.add ( v_t_overlap_comp_item_id(i) ||
4791 ' ' || v_t_overlap_src_op_seq_num(i) ||
4792 ' ' || v_t_overlap_src_eff_date(i) ||
4793 ' ' || v_t_overlap_src_disable_date(i) ||
4794 ' OVERLAPS ' ||
4795 ' ' || v_t_overlap_src_op_seq_num(i) ||
4796 ' ' || v_t_overlap_src_eff_date(i) ||
4797 ' ' || v_t_overlap_src_disable_date(i) , 1);
4798 */
4799 END IF;
4800
4801 select segment1
4802 into
4803 l_model_name
4804 from mtl_system_items
4805 where inventory_item_id=pModelId
4806 and rownum=1;
4807
4808 l_token2(1).token_name :='MODEL';
4809 l_token2(1).token_value :=l_model_name;
4810 cto_msg_pub.cto_message('BOM','CTO_OVERLAP_DATE_ERROR',l_token2);
4811 end loop ;
4812
4813 raise fnd_api.g_exc_error;
4814
4815 end if ;
4816
4817 end if;
4818
4819
4820
4821 /* end Check for Overlapping Effectivity Dates */
4822
4823
4824
4825
4826
4827
4828
4829
4830
4831
4832
4833
4834
4835 lStmtNumber := 560;
4836
4837 /*Flushing the temp table*/
4838 DELETE /*+ INDEX ( BOM_EXPLOSION_TEMP BOM_EXPLOSION_TEMP_N11) */
4839 from bom_explosion_temp
4840 WHERE bill_sequence_id = pConfigBillId;
4841
4842 return(1);
4843
4844 EXCEPTION
4845 when no_data_found then
4846 xErrorMessage := 'CTOCBOMB:'||to_char(lStmtNumber);
4847 xMessageName := 'CTO_INHERIT_OP_SEQ_ERROR';
4848 IF PG_DEBUG <> 0 THEN
4849 oe_debug_pub.add ('inherit_op_seq_ml: ' || 'Error: No data found in inherit_op_seq_ml. Returning 0 ', 1);
4850 END IF;
4851 return(0);
4852
4853 when FND_API.G_EXC_ERROR then
4854 xErrorMessage:='CTOCBOMB:'||lStmtNumber||':'||substrb(sqlerrm,1,150);
4855 xMessageName := 'CTO_INHERIT_OP_SEQ_ERROR';
4856 IF PG_DEBUG <> 0 THEN
4857 oe_debug_pub.add('create_bom_data_ml: ' || 'Error: ' || xErrorMessage, 1);
4858 END IF;
4859 return(0);
4860
4861 when FND_API.G_EXC_UNEXPECTED_ERROR then -- bugfix 2765635
4862 xErrorMessage:='CTOCBOMB:'||lStmtNumber||':'||substrb(sqlerrm,1,150);
4863 xMessageName := 'CTO_INHERIT_OP_SEQ_ERROR';
4864 IF PG_DEBUG <> 0 THEN
4865 oe_debug_pub.add('Error: ' || xErrorMessage);
4866 END IF;
4867 return(0);
4868
4869 when others then
4870 xErrorMessage := 'CTOCBOMB:'||to_char(lStmtNumber)||':'||substrb(sqlerrm,1,150);
4871 xMessageName := 'CTO_INHERIT_OP_SEQ_ERROR';
4872 IF PG_DEBUG <> 0 THEN
4873 oe_debug_pub.add ('inherit_op_seq_ml: ' || 'Error: Others excpn : '||sqlerrm, 1);
4874 END IF;
4878 --e2307936
4875 return (0);
4876 END inherit_op_seq_ml;
4877
4879
4880 /*-----------------------------------------------------------------+
4881 Name : check_bom
4882 Check to see if the BOM exists for the item in the
4883 specified org.
4884 +------------------------------------------------------------------*/
4885 function check_bom(
4886 pItemId in number,
4887 pOrgId in number,
4888 xBillId out NOCOPY number)
4889 return integer
4890 is
4891
4892
4893 begin
4894
4895 xBillId := 0;
4896
4897 IF PG_DEBUG <> 0 THEN
4898 oe_debug_pub.add('check_bom: ' || 'before check_bom sql::xBillId:: '||to_char(xBillId ), 2);
4899 END IF;
4900
4901
4902 select bill_sequence_id
4903 into xBillId
4904 from bom_bill_of_materials
4905 where assembly_item_id = pItemId
4906 and organization_id = pOrgId
4907 and alternate_bom_designator is null;
4908
4909 IF PG_DEBUG <> 0 THEN
4910 oe_debug_pub.add('check_bom: ' || 'after check_bom sql::xBillId:: '||to_char(xBillId )||'returning 1', 2);
4911 END IF;
4912
4913 return(1);
4914
4915 exception
4916
4917 when no_data_found then
4918 IF PG_DEBUG <> 0 THEN
4919 oe_debug_pub.add ('check_bom: ' || 'NDF exception for Check BOM::item id '||to_char(pItemId), 1);
4920 END IF;
4921 return(0);
4922
4923 when others then
4924 IF PG_DEBUG <> 0 THEN
4925 oe_debug_pub.add ('check_bom: ' || 'Others exception for Check BOM::item id '||to_char(pItemId), 1);
4926 END IF;
4927 cto_msg_pub.cto_message('BOM', 'CTO_CREATE_BOM_ERROR');
4928 return(0);
4929
4930 end check_bom;
4931
4932
4933 /*-----------------------------------------------------------------+
4934 Name : get_model_lead_time
4935 +------------------------------------------------------------------*/
4936
4937 function get_model_lead_time
4938 ( pModelId in number,
4939 pOrgId in number,
4940 pQty in number,
4941 pLeadTime out NOCOPY number,
4942 pErrBuf out NOCOPY varchar2
4943 )
4944 return integer
4945
4946 is
4947
4948 lStmtNum number;
4949
4950 begin
4951
4952 IF PG_DEBUG <> 0 THEN
4953 oe_debug_pub.add('get_model_lead_time: ' || 'Getting Lead Time for Model: ' || to_char(pModelId), 2);
4954 END IF;
4955 lStmtNum := 100;
4956
4957 select (ceil(nvl(msi.fixed_lead_time,0)
4958 + nvl(msi.variable_lead_time,0) * pQty))
4959 into pLeadTime
4960 from mtl_system_items msi
4961 where inventory_item_id = pModelId
4962 and organization_id = pOrgId;
4963
4964 IF PG_DEBUG <> 0 THEN
4965 oe_debug_pub.add('get_model_lead_time: ' || 'Lead Time: ' || to_char(pLeadtime), 2);
4966 END IF;
4967
4968 return 1;
4969
4970 exception
4971
4972 when others then
4973 pErrBuf := 'CTOCBOMB: ' || lStmtNum || substrb(SQLERRM,1,150);
4974 return 0;
4975
4976 end get_model_lead_time;
4977
4978 /*-----------------------------------------------------------------+
4979 Name : bmlggpn_get_group_name
4980 +------------------------------------------------------------------*/
4981
4982 function bmlggpn_get_group_name
4983 ( group_id number,
4984 group_name out NOCOPY varchar2,
4985 err_buf out NOCOPY varchar2
4986 )
4987 return integer
4988 is
4989 max_seg number;
4990 lStmtNum number;
4991 type segvalueType is table of varchar2(30)
4992 index by binary_integer;
4993 seg_value segvalueType;
4994 segvalue_tmp varchar2(30);
4995 segnum_tmp number;
4996 catseg_value varchar2(240);
4997 delimiter varchar2(10);
4998 profile_setting varchar2(30);
4999 CURSOR profile_check IS
5000 select nvl(substr(profile_option_value,1,30),'N')
5001 from fnd_profile_option_values val,fnd_profile_options op
5002 where op.application_id = 401
5003 and op.profile_option_name = 'USE_NAME_ICG_DESC'
5004 and val.level_id = 10001 /* This is for site level */
5005 and val.application_id = op.application_id
5006 and val.profile_option_id = op.profile_option_id;
5007 begin
5008 /* First lets get the value for profile option USE_NAME_ICG_DESC
5009 ** If this is 'N' we need to use the description
5010 ** If this is 'Y' then we need to use the group name
5011 ** We are going to stick with group name if the customer is
5012 ** not on R10.5, which means they do not have the profile
5013 ** If they have R10.5 then we are going to use description
5014 ** because that is what inventory is going to do.
5015 ** Remember at the earliest we should get rid of this function
5016 ** and call INV API. Remember we at ATO are not in the business
5017 ** of duplicating code of other teams
5018 */
5019
5020 profile_setting := 'Y';
5021
5022 lStmtNum :=250;
5023 OPEN profile_check;
5024 FETCH profile_check INTO profile_setting;
5025 IF profile_check%NOTFOUND THEN
5026 profile_setting := 'Y';
5027 END IF;
5028 IF PG_DEBUG <> 0 THEN
5029 oe_debug_pub.add ('bmlggpn_get_group_name: ' || ' USE_NAME_ICG_DESC :'|| profile_setting, 2);
5030 END IF;
5031
5032 if profile_setting = 'Y' then
5033
5037 */
5034 /* Let us select the catalog group name from mtl_catalog_groups
5035 ** At some point in time we need to call the inventory function
5036 ** to do this, so we can centralize this stuff
5038 lStmtNum :=260;
5039
5040 SELECT MICGK.concatenated_segments
5041 INTO group_name
5042 FROM mtl_item_catalog_groups_kfv MICGK
5043 WHERE MICGK.item_catalog_group_id = group_id;
5044
5045 else
5046 lStmtNum :=270;
5047 /* This is to get the description of the catalog */
5048 SELECT MICG.description
5049 INTO group_name
5050 FROM mtl_item_catalog_groups MICG
5051 WHERE MICG.item_catalog_group_id = group_id;
5052
5053 end if;
5054 return(0);
5055 exception
5056 when others then
5057 err_buf := 'CTOCBOMB: ' || lStmtNum || substrb(SQLERRM,1,150);
5058 return(SQLCODE);
5059 end bmlggpn_get_group_name;
5060
5061
5062 /*-----------------------------------------------------------------+
5063 Name : bmlupid_update_item_desc
5064 +------------------------------------------------------------------*/
5065
5066 function bmlupid_update_item_desc
5067 (
5068 item_id NUMBER,
5069 org_id NUMBER,
5070 err_buf out NOCOPY VARCHAR2
5071 )
5072 return integer
5073 is
5074 /*
5075 ** Create cursor to retrieve all descriptive element values for the item
5076 */
5077 CURSOR cc is
5078 select element_value
5079 from mtl_descr_element_values
5080 where inventory_item_id = item_id
5081 and element_value is not NULL
5082 and default_element_flag = 'Y'
5083 order by element_sequence;
5084
5085 delimiter varchar2(10);
5086 e_value varchar2(30);
5087 cat_value varchar2(240);
5088 idx number;
5089 group_id number;
5090 group_name varchar2(240); -- bugfix 2483982: increased the size from 30 to 240
5091 lStmtNum number;
5092 status number;
5093 INV_GRP_ERROR exception;
5094 begin
5095 lStmtNum := 280;
5096 IF PG_DEBUG <> 0 THEN
5097 oe_debug_pub.add('bmlupid_update_item_desc: ' || ' In bmlupid_update_item_desc ',2);
5098 oe_debug_pub.add('bmlupid_update_item_desc: ' || ' item id ' || item_id ,2);
5099 oe_debug_pub.add('bmlupid_update_item_desc: ' || ' org id ' || org_id ,2);
5100 END IF;
5101
5102 select concatenated_segment_delimiter into delimiter
5103 from fnd_id_flex_structures
5104 where id_flex_code = 'MICG'
5105 and application_id = 401;
5106
5107 lStmtNum := 285;
5108 select item_catalog_group_id into group_id
5109 from mtl_system_items
5110 where inventory_item_id = item_id
5111 and organization_id = org_id;
5112
5113 IF PG_DEBUG <> 0 THEN
5114 oe_debug_pub.add('bmlupid_update_item_desc: ' || ' item_catalog_group_id : ' || group_id,2);
5115 END IF;
5116 idx := 0;
5117 cat_value := '';
5118 open cc;
5119 loop
5120 fetch cc into e_value;
5121 exit when (cc%notfound);
5122
5123 if idx = 0 then
5124 lStmtNum := 290;
5125 status := bmlggpn_get_group_name(group_id,group_name,
5126 err_buf);
5127 if status <> 0 then
5128 raise INV_GRP_ERROR;
5129 end if;
5130 cat_value := group_name || delimiter || e_value;
5131 else
5132 lStmtNum := 295;
5133 cat_value := cat_value || SUBSTRB(delimiter || e_value,1,
5134 240-LENGTHB(cat_value));
5135 end if;
5136 IF PG_DEBUG <> 0 THEN
5137 oe_debug_pub.add('bmlupid_update_item_desc: ' || 'cat_value :' || cat_value,1);
5138 END IF;
5139 idx := idx + 1;
5140 end loop;
5141 close cc;
5142
5143 if idx <> 0 then
5144 update mtl_system_items
5145 set description = cat_value
5146 where inventory_item_id = item_id;
5147 /*and organization_id = org_id; Bugfix 2163311 */
5148 /* start bugfix 1845141 */
5149 update mtl_system_items_tl
5150 set description = cat_value
5151 where inventory_item_id = item_id;
5152 /*and organization_id = org_id; Bugfix 2163311 */
5153 /* end bugfix 1845141 */
5154 end if;
5155
5156 return(0);
5157 exception
5158 when INV_GRP_ERROR then
5159 err_buf := 'CTOCBOMB: Invalid catalog group for the item ' || item_id || ' status:' || status;
5160 IF PG_DEBUG <> 0 THEN
5161 oe_debug_pub.add ('bmlupid_update_item_desc: ' || err_buf, 1);
5162 END IF;
5163 cto_msg_pub.cto_message ('BOM', 'CTO_INVALID_CATALOG_GRP');
5164 return(1);
5165
5166 when OTHERS then
5167 err_buf := 'CTOCBOMB: ' || lStmtNum ||substrb(SQLERRM,1,150);
5168 IF PG_DEBUG <> 0 THEN
5169 oe_debug_pub.add ('bmlupid_update_item_desc: ' || err_buf, 1);
5170 END IF;
5171 cto_msg_pub.cto_message ('BOM', 'CTO_CREATE_BOM_ERROR');
5172 return(1);
5176
5173
5174 END bmlupid_update_item_desc;
5175
5177
5178
5179
5180
5181 /*
5182 l_aname wf_engine.nametabtyp;
5183 l_anumvalue wf_engine.numtabtyp;
5184 l_atxtvalue wf_engine.texttabtyp;
5185
5186 wf_engine.CreateProcess (ItemType=> 'CTOCHORD',ItemKey=>litem_key,Process=>'CHGNOTIFY');
5187 wf_engine.SetItemUserKey(ItemType=> 'CTOCHORD',ItemKey=>litem_key,UserKey=>luser_key);
5188
5189 wf_engine.SetItemAttrTextArray(ItemType =>'CTOCHORD',ItemKey=>litem_key,aname=>l_aname,avalue=>l_atxtvalue);
5190
5191 wf_engine.SetItemOwner(Itemtype=>'CTOCHORD',itemkey=>litem_key,owner=>lplanner_code);
5192 wf_engine.StartProcess(itemtype=>'CTOCHORD',ItemKey=>litem_key);
5193 */
5194
5195
5196 procedure send_oid_notification(
5197 P_LINE_ID in number
5198 ,P_SALES_ORDER_NUM in number
5199 ,P_ERROR_MESSAGE in varchar2
5200 ,P_TOP_MODEL_NAME in varchar2
5201 ,P_TOP_MODEL_LINE_NUM in varchar2
5202 ,P_TOP_CONFIG_NAME in varchar2
5203 ,P_TOP_CONFIG_LINE_NUM in varchar2
5204 ,P_PROBLEM_MODEL in varchar2
5205 ,P_PROBLEM_MODEL_LINE_NUM in varchar2
5206 ,P_PROBLEM_CONFIG in varchar2
5207 ,P_ERROR_ORG in varchar2
5208 ,P_NOTIFY_USER in varchar2
5209 ,P_REQUEST_ID in number
5210 )
5211 is
5212 l_aname wf_engine.nametabtyp;
5213 l_anumvalue wf_engine.numtabtyp;
5214 l_atxtvalue wf_engine.texttabtyp;
5215 luser_key varchar2(100);
5216 litem_key varchar2(100);
5217 lplanner_code mtl_system_items_vl.planner_code%type;
5218
5219 porder_no number := 2222 ;
5220 pline_no number := 1111 ;
5221
5222 lstmt_num number ;
5223
5224 l_new_line varchar2(10) := fnd_global.local_chr(10);
5225 begin
5226 lstmt_num := 10 ;
5227
5228
5229 litem_key := to_char(p_line_id)||to_char(sysdate,'mmddyyhhmiss');
5230 luser_key := litem_key;
5231
5232 lplanner_code := P_NOTIFY_USER ;
5233
5234 lstmt_num := 20 ;
5235
5236
5237
5238 IF WF_DIRECTORY.USERACTIVE(lplanner_code) <>TRUE THEN
5239 -- Get the default adminstrator value from Workflow Attributes.
5240 lplanner_code := wf_engine.getItemAttrText(ItemType => 'CTOEXCP',
5241 ItemKey => litem_key,
5242 aname => 'WF_ADMINISTRATOR');
5243 oe_debug_pub.add('start_work_flow: ' || 'Planner code is not a valid workflow user...Defaulting to'||lplanner_code,5);
5244
5245 else
5246
5247 oe_debug_pub.add('start_work_flow: ' || 'Planner code is a valid workflow user...' ,5);
5248
5249 END IF;
5250
5251 lstmt_num := 30 ;
5252
5253
5254 l_aname(1) := 'PROBLEM_MODEL';
5255 l_atxtvalue(1) := 'CN97444' ;
5256
5257 l_aname(2) := 'ERROR_MESSAGE' ;
5258 l_atxtvalue(2) := P_ERROR_MESSAGE ;
5259
5260 l_aname(3) := 'TOP_MODEL_NAME' ;
5261 l_atxtvalue(3) := P_TOP_MODEL_NAME ;
5262
5263 l_aname(4) := 'TOP_MODEL_LINE_NUM' ;
5264 l_atxtvalue(3) := P_TOP_MODEL_LINE_NUM ;
5265
5266 l_aname(5) := 'TOP_CONFIG_NAME' ;
5267 l_atxtvalue(5) := P_TOP_CONFIG_NAME ;
5268
5269 l_aname(6) := 'TOP_CONFIG_LINE_NUM' ;
5270 l_atxtvalue(6) := P_TOP_CONFIG_LINE_NUM ;
5271
5272 l_aname(7) := 'PROBLEM_MODEL' ;
5273 l_atxtvalue(7) := P_PROBLEM_MODEL ;
5274
5275 l_aname(8) := 'PROBLEM_MODEL_LINE_NUM' ;
5276 l_atxtvalue(8) := P_PROBLEM_MODEL_LINE_NUM ;
5277
5278 l_aname(9) := 'PROBLEM_CONFIG' ;
5279 l_atxtvalue(8) := P_PROBLEM_CONFIG ;
5280
5281 l_aname(10) := 'ERROR_ORG' ;
5282 l_atxtvalue(10) := P_ERROR_ORG ;
5283
5284 l_aname(11) := 'REQUEST_ID' ;
5285 l_atxtvalue(11) := P_REQUEST_ID ;
5286
5287 lstmt_num := 35 ;
5288
5289 l_aname(12) := 'NOTIFY_USER';
5290 l_atxtvalue(12) := lplanner_code;
5291
5292 lstmt_num := 50 ;
5293 wf_engine.CreateProcess (ItemType=> 'CTOEXCP',ItemKey=>litem_key,Process=>'NOTIFY_OID_INC');
5294
5295 lstmt_num := 60 ;
5296 wf_engine.SetItemUserKey(ItemType=> 'CTOEXCP',ItemKey=>litem_key,UserKey=>luser_key);
5297
5298 lstmt_num := 40 ;
5299
5300 wf_engine.SetItemAttrNumber(ItemType =>'CTOEXCP',
5301 itemkey =>litem_key,
5302 aname =>'ORDER_NUM',
5303 avalue => p_sales_order_num );
5304
5305 lstmt_num := 70 ;
5306 wf_engine.SetItemAttrTextArray(ItemType =>'CTOEXCP',ItemKey=>litem_key,aname=>l_aname,avalue=>l_atxtvalue);
5307
5308 lstmt_num := 80 ;
5309 wf_engine.SetItemOwner(Itemtype=>'CTOEXCP',itemkey=>litem_key,owner=>lplanner_code);
5310
5311
5312 lstmt_num := 90 ;
5313 wf_engine.StartProcess(itemtype=>'CTOEXCP',ItemKey=>litem_key);
5314
5315
5316 oe_debug_pub.add( ' done till stmt ' || lstmt_num ) ;
5317
5318
5322
5319
5320 exception
5321 when others then
5323 oe_debug_pub.add( ' exception in others at stmt ' || lstmt_num ) ;
5324 oe_debug_pub.add( ' exception in others ' || SQLCODE ) ;
5325
5326
5327 end send_oid_notification ;
5328
5329
5330 function get_dit_count
5331 return number
5332 is
5333 begin
5334 if( g_t_dropped_item_type is not null ) then
5335 return g_t_dropped_item_type.count ;
5336
5337 else
5338 return 0 ;
5339 end if ;
5340
5341
5342 end ;
5343
5344
5345 procedure get_dropped_components( x_t_dropped_items out NOCOPY t_dropped_item_type )
5346 is
5347 begin
5348 for i in 1..g_t_dropped_item_type.count
5349 loop
5350 x_t_dropped_items(i) := g_t_dropped_item_type(i) ;
5351
5352 end loop ;
5353
5354
5355 end get_dropped_components ;
5356
5357
5358
5359 procedure reset_dropped_components
5360 is
5361 begin
5362 g_t_dropped_item_type.delete ;
5363
5364 end reset_dropped_components ;
5365
5366
5367 END CTO_CONFIG_BOM_PK;