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