[Home] [Help]
PACKAGE BODY: APPS.CTO_UPDATE_BOM_RTG_PK
Source
1 package body CTO_UPDATE_BOM_RTG_PK as
2 /* $Header: CTOUBOMB.pls 120.23.12010000.2 2008/08/05 12:12:08 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 : CTOUBOMB.sql
11 | DESCRIPTION :
12 | HISTORY : created 16-OCT-2003 by Sajani Sheth
13 |
14 | 02/11/04 Sushant fixed bug 3402690. preconfigured items |
15 | not upgraded.
16 | |
17 | Sushant fixed bug 3396081. order not on hold |
18 | for preconfigured items. |
19 | |
20 | |
21 | |
22 | 04/19/04 Sushant fixed bug 3529592. Insert query for |
23 | table bom_reference_designators should |
24 | check for null values for column acd_type. |
25 | |
26 |
27 | Modified : 14-MAY-2004 Sushant Sawant
28 | Fixed bug 3484511.
29 |
30
31 |
32 | Modified : 21-JUN-2004 Sushant Sawant
33 | Fixed bug 3710096.
34 | Substitute components were not copied
35 |
36 | Modified : 11-05-2004 Kiran Konada
37 | Fixed bug 3793286.
38 | Front Ported bug 3674833
39 |
40 | Renga Kannan 28-Jan-2004 Front Port bug fix 4049807
41 | Descriptive Flexfield Attribute
42 | category is not copied from model
43 | Added this column while inserting
44 | into bom_operational_routings
45 |
46 | Modified : 02-02-2005 Kiran Konada
47 | bug#4092184 FP:11.5.9 - 11.5.10 :I
48 | customer bug#4081613
49 | if custom package CTO_CUSTOM_CATALOG_DESC.catalog_desc_method is
50 | set to 'C' to use custom api AND if model item is not assigned
51 | to a catalog group. Create configuration process fails
52 |
53 | Fix has been made not to honor the custom package if a ato model
54 | is not assigned to a catalog group or there are no descrptive elements
55 | defined for a catalog group.
56 |
57 |
58 |
59 | Modified : 04-09-2005 Sushant Sawant
60 | bug#3793286
61 | Reference designators from all individual instances of the consolidated
62 | component from the model bill should be copied to the consolidated component
63 | on the configuration bill.
64 | |
65 |
66 | Modified : 04-09-2005 Sushant Sawant
67 | Fixed issue for bug 4271269.
68 | populate structure_type_id and effectivity_control columns in
69 | bom_bill_of_materials view.
70 |
71 | Modified : 09-02-2005 Renga Kannan
72 | Fixed the following issues in LBM and effecitivity
73 | part of code
74 |
75 | 1.) LBM code does not handle null value for basis type
76 | Added nvl clause for all insert stmt from bom_inventory_components
77 | to bom_inventory_components_interface
78 |
79 | 2.) for overlapping effectivity dates with components having
80 | having different basis type the message is not raised
81 | properly. fixd that code
82 |
83 | 3.) Clubbing component code is inserting null qty value into
84 | bic interface. Fixed the code not to insert these rows.
85 |
86 || Modified by Renga Kannan on 09/07/2005
87 | Bug Fix 4595162
88 | Modified the code that populates basis type to
89 | bom_inventory_components table. As per bom team
90 | basis_type should have null for 'ITEM' and 2 for 'LOT'
91 |
92 | Modified by Renga Kannan on 09/26/2006
93 | Bug Fix for 4628806
94 | Fixed a LBM related bug
95 |
96 | Kiran Konada 05-Jan-2006 bugfix1765149
97 | get the x and Y coordinate on canvas for flow routing
98 |
99 |
100 | 06-Jan-2006 Kiran Konada
101 | bugfix#4492875
102 | Removed the debug statement having sql%rowcount as parameter, which
103 | was immeditaly after sql statement and before if statement using sql%rowcount
104 |
105 | Reason : if there is a logic dependent on sql%rowcount and debug log statement before
106 | it uses sql%rowcount , then logic may go wrong
107 |
108 |
109 *============================================================================*/
110
111 g_SchShpDate Date;
112 g_EstRelDate Date;
113 glast_update_date Date := to_date('01/01/2099 00:00:00','MM/DD/YYYY HH24:MI:SS');
114
115 /*
116 gUserId number := nvl(Fnd_Global.USER_ID, -1);
117 gLoginId number := nvl(Fnd_Global.LOGIN_ID, -1);
118 */
119
120 -- bug 4271269. populate structure_type_id in BOM
121 g_structure_type_id bom_bill_of_materials.structure_type_id%type ;
122
123 gUserId number := nvl(fnd_global.user_id, -1);
124 gLoginId number := nvl(fnd_global.login_id, -1);
125 gRequestId number := nvl(fnd_global.conc_request_id, -1) ;
126 gProgramApplId number := nvl(fnd_global.prog_appl_id, -1) ;
127 gProgramId number := nvl(fnd_global.conc_program_id, -1) ;
128
129 -- 3222932 setting global replacement of null disable dates
130
131 g_futuredate DATE := to_date('01/01/2099 00:00:00','MM/DD/YYYY HH24:MI:SS'); /* 02-14-2005 Sushant */
132
133 PG_DEBUG Number := NVL(FND_PROFILE.value('ONT_DEBUG_LEVEL'), 0);
134
135 /***************************************************************************
136 This procedure will be called by the Update Configuration BOMs concurrent
137 program for a particular bcol_upg sequence. It will create BOMs and
138 Routings for all configurations having this sequence. Each line_id processed
139 successfully will be updated to status DONE. If BOM creation errors out, status
140 will be updated to 'ERROR'.
141 ***************************************************************************/
142 PROCEDURE Update_Boms_Rtgs(
143 errbuf OUT NOCOPY varchar2,
144 retcode OUT NOCOPY varchar2,
145 p_seq IN number,
146 p_changed_src IN varchar2) IS
147
148 CURSOR c_boms IS
149 select distinct
150 bcolu.ato_line_id ato_line_id
151 from bom_cto_order_lines_upg bcolu
152 where bcolu.sequence = p_seq
153 and bcolu.status = 'BOM_PROC'
154 and bcolu.ato_line_id = bcolu.line_id;
155
156 CURSOR c_all_configs(p_ato_line_id number) IS
157 select /*+ INDEX (BCOLU BOM_CTO_ORDER_LINES_UPG_N4) */
158 bcolu.line_id,
159 bcolu.inventory_item_id,
160 bcolu.config_item_id
161 from bom_cto_order_lines_upg bcolu
162 where bcolu.ato_line_id = p_ato_line_id
163 and bcolu.bom_item_type = 1
164 and nvl(bcolu.wip_supply_type,0) <> 6
165 and bcolu.config_item_id is not null
166 and bcolu.ato_line_id is not null
167 order by plan_level desc;
168
169 l_flow_calc number;
170 l_return_status varchar2(1);
171 l_msg_count number;
172 l_msg_data varchar2(240);
173 l_stmt_num number;
174 l_mrp_aset_id number;
175 l_cto_aset_id number;
176 l_bcolu_status varchar2(15);
177 l_row_count Number;
178
179 l_error_flag Varchar2(1) := 'N';
180 l_msg_data1 Varchar2(2000);
181 BEGIN
182
183 WriteToLog('Entering update_boms_rtgs', 1);
184 WriteToLog(' Sequence::'||p_seq, 1);
185 WriteToLog(' Changed sourcing::'||p_changed_src, 1);
186
187 l_stmt_num := 10;
188 l_flow_calc := FND_PROFILE.Value('CTO_PERFORM_FLOW_CALC');
189 WriteToLog('Perform_flow_calc::'||l_flow_calc, 3);
190
191 WHILE TRUE LOOP
192 --
193 -- select next N ato_line_ids and update status to BOM_PROC
194 --
195 l_stmt_num := 20;
196 update bom_cto_order_lines_upg bcolu
197 set status = 'BOM_PROC'
198 where bcolu.ato_line_id in (select ato_line_id
199 from bom_cto_order_lines_upg bcolu2
200 where bcolu2.ato_line_id = bcolu2.line_id
201 and bcolu2.sequence = p_seq
202 and bcolu2.status = 'CTO_SRC'
203 and rownum < G_SUB_BATCH_SIZE + 1);
204
205
206 IF sql%notfound THEN
207 WriteToLog('No records to Process in BCOL',3);
208 Exit;
209 Else
210 WriteToLog('Updated status to BOM_PROC for rows::'||sql%rowcount, 3);
211 END IF;
212
213 l_stmt_num := 30;
214 FOR v_boms in c_boms LOOP
215
216 --
217 -- Line could be put on hold due to dropped components
218 -- being found for the same config item on a different order line
219 -- Process only if line is not on hold
220 --
221 l_stmt_num := 35;
222 select /*+ INDEX (BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_U1) */
223 status
224 into l_bcolu_status
225 from bom_cto_order_lines_upg
226 where ato_line_id = v_boms.ato_line_id
227 and rownum = 1;
228
229 WriteToLog('Line is in status :: '||l_bcolu_status, 2);
230
231 IF (l_bcolu_status = 'BOM_PROC') THEN
232 -- Line is not on hold or in error. Process for BOM creation. l_stmt_num := 40;
233 FOR v_all_configs IN c_all_configs(v_boms.ato_line_id) LOOP
234
235 l_stmt_num := 50;
236 WriteToLog('In v_boms loop, ato_line_id:: '||v_boms.ato_line_id, 4);
237
238
239
240 select /*+ INDEX (BOM_CTO_ORDER_LINES_UPG BOM_CTO_ORDER_LINES_UPG_N4) */
241 status
242 into l_bcolu_status
243 from bom_cto_order_lines_upg
244 where line_id = v_all_configs.line_id ;
245
246 WriteToLog('Line is in status :: '||l_bcolu_status, 2);
247
248
249 IF (l_bcolu_status = 'BOM_PROC') THEN
250
251 Update_In_Src_Orgs(
252 v_all_configs.line_id,
253 v_all_configs.inventory_item_id,
254 v_all_configs.config_item_id,
255 l_flow_calc,
256 l_return_status,
257 l_msg_count,
258 l_msg_data);
259
260 IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
261 WriteToLog('Update_In_Src_Orgs returned with expected error.', 1);
262 --RAISE FND_API.G_EXC_ERROR ;
263 --
264 -- Here, we want to skip processing for
265 -- the rest of this ato_line_id, but continue
266 -- processing the remaining ato_line_ids.
267 --
268 update /*+ INDEX (BCOLU1 BOM_CTO_ORDER_LINES_UPG_N4) */
269 bom_cto_order_lines_upg bcolu1
270 set bcolu1.status = 'ERROR'
271 where bcolu1.ato_line_id =
272 (select bcolu2.ato_line_id
273 from bom_cto_order_lines_upg bcolu2
274 where bcolu2.line_id = v_all_configs.line_id);
275
276 WriteToLog('Rows updated to status ERROR::'||sql%rowcount, 1);
277 l_error_flag := 'Y';
278 EXIT;
279 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
280 WriteToLog('Update_In_Src_Orgs returned with unexpected error.', 1);
281 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
282 END IF;
283
284
285
286 END IF; /* line id could have been set to error if child line has encountered an error */
287
288
289
290 END LOOP;
291 WriteToLog('Error occured in bom creation',1);
292 fnd_msg_pub.count_and_get(p_count => l_msg_count,
293 p_data => l_msg_data);
294 If l_msg_count > 0 Then
295 WriteToLog('============= Error Messages ==============',1);
296 for i in 1..l_msg_count
297 Loop
298 l_msg_data1 := fnd_msg_pub.get(
299 p_msg_index => i,
300 p_encoded => fnd_api.g_false);
301 WriteToLog(l_msg_data1,1);
302 end loop;
303 WriteToLog('============= End of error Messages ============',1);
304 End if;
305 END IF; /* line on hold */
306 END LOOP;
307
308 l_stmt_num := 60;
309 update bom_cto_order_lines_upg bcolu
310 set status = 'BOM_LOOP'
311 where sequence = p_seq
312 and status = 'BOM_PROC';
313
314 WriteToLog('Updated status to BOM_LOOP for rows::'||sql%rowcount, 3);
315
316 --
317 -- Loop processing done for these N ato_line_ids
318 -- Do BOM and Rtg bulk processing
319 --
320 l_stmt_num := 70;
321 Update_Bom_Rtg_Bulk(
322 p_seq,
323 l_return_status,
324 l_msg_count,
325 l_msg_data);
326 IF (l_return_status = FND_API.G_RET_STS_ERROR ) THEN
327 WriteToLog('Update_Bom_Rtg_Bulk returned with expected error.', 1);
328 RAISE FND_API.G_EXC_ERROR ;
329 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) THEN
330 WriteToLog('Update_Bom_Rtg_Bulk returned with unexpected error.', 1);
331 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
332 END IF;
333
334 --
335 -- Update rows processed to BOM_BULK
336 --
337 update bom_cto_order_lines_upg bcolu
338 set status = 'BOM_BULK'
339 where bcolu.ato_line_id in (select ato_line_id
340 from bom_cto_order_lines_upg bcolu2
341 where bcolu2.sequence = p_seq
342 and bcolu2.status = 'BOM_LOOP');
343
344 WriteToLog('Rows updated to status BOM_BULK::' ||sql%rowcount, 2);
345
346 l_mrp_aset_id := to_number(FND_PROFILE.VALUE('MRP_DEFAULT_ASSIGNMENT_SET'));
347 WriteToLog('MRP Assignment Set Id::'||l_mrp_aset_id, 2);
348
349 BEGIN
350 select assignment_set_id
351 into l_cto_aset_id
352 from mrp_assignment_sets
353 where assignment_set_name = 'CTO Configuration Updates';
354
355 WriteToLog('CTO Seeded Assignment Set Id::'||l_cto_aset_id, 2);
356
357 EXCEPTION
358 WHEN no_data_found THEN
359 WriteToLog('ERROR: CTO seeded assignment set not found', 1);
360 RAISE FND_API.G_EXC_ERROR;
361 END;
362
363 --
364 -- Copy sourcing from CTO to MRP Default Assignment Set
365 --
366 delete from mrp_sr_assignments
367 where assignment_set_id = l_mrp_aset_id
368 and inventory_item_id in
369 (select config_item_id
370 from bom_cto_order_lines_upg
371 where sequence = p_seq
372 and status = 'BOM_BULK'
373 and (p_changed_src = 'Y'
374 or (p_changed_src = 'N' and nvl(config_creation,'1') = '3')));
375
376 WriteToLog('Rows deleted from MRP Default Assignment Set::' ||sql%rowcount, 2);
377
378 insert into mrp_sr_assignments(
379 ASSIGNMENT_ID,
380 ASSIGNMENT_TYPE,
381 SOURCING_RULE_ID,
382 SOURCING_RULE_TYPE,
383 ASSIGNMENT_SET_ID,
384 LAST_UPDATE_DATE,
385 LAST_UPDATED_BY,
386 CREATION_DATE,
387 CREATED_BY,
388 LAST_UPDATE_LOGIN,
389 REQUEST_ID,
390 PROGRAM_APPLICATION_ID,
391 PROGRAM_ID,
392 PROGRAM_UPDATE_DATE,
393 ORGANIZATION_ID,
394 CATEGORY_ID,
395 CATEGORY_SET_ID,
396 INVENTORY_ITEM_ID,
397 SECONDARY_INVENTORY,
398 ATTRIBUTE_CATEGORY,
399 ATTRIBUTE1,
400 ATTRIBUTE2,
401 ATTRIBUTE3,
402 ATTRIBUTE4,
403 ATTRIBUTE5,
404 ATTRIBUTE6,
405 ATTRIBUTE7,
406 ATTRIBUTE8,
407 ATTRIBUTE9,
408 ATTRIBUTE10,
409 ATTRIBUTE11,
410 ATTRIBUTE12,
411 ATTRIBUTE13,
412 ATTRIBUTE14,
413 ATTRIBUTE15,
414 CUSTOMER_ID,
415 SHIP_TO_SITE_ID)
416 select
417 mrp_sr_assignments_s.nextval, --ASSIGNMENT_ID,
418 ma.ASSIGNMENT_TYPE,
419 ma.SOURCING_RULE_ID,
420 ma.SOURCING_RULE_TYPE,
421 l_mrp_aset_id,
422 sysdate, --LAST_UPDATE_DATE,
423 gUserId, --LAST_UPDATED_BY,
424 sysdate, --CREATION_DATE,
425 gUserId, --CREATED_BY,
426 gLoginId, --LAST_UPDATE_LOGIN,
427 null, --REQUEST_ID,
428 null, --PROGRAM_APPLICATION_ID,
429 null, --PROGRAM_ID,
430 null, --PROGRAM_UPDATE_DATE,
431 ma.ORGANIZATION_ID,
432 ma.CATEGORY_ID,
433 ma.CATEGORY_SET_ID,
434 ma.INVENTORY_ITEM_ID,
435 ma.SECONDARY_INVENTORY,
436 ma.ATTRIBUTE_CATEGORY,
437 ma.ATTRIBUTE1,
438 ma.ATTRIBUTE2,
439 ma.ATTRIBUTE3,
440 ma.ATTRIBUTE4,
441 ma.ATTRIBUTE5,
442 ma.ATTRIBUTE6,
443 ma.ATTRIBUTE7,
444 ma.ATTRIBUTE8,
445 ma.ATTRIBUTE9,
446 ma.ATTRIBUTE10,
447 ma.ATTRIBUTE11,
448 ma.ATTRIBUTE12,
449 ma.ATTRIBUTE13,
450 ma.ATTRIBUTE14,
451 ma.ATTRIBUTE15,
452 ma.CUSTOMER_ID,
453 ma.SHIP_TO_SITE_ID
454 from mrp_sr_assignments ma
455 where ma.assignment_set_id = l_cto_aset_id
456 and ma.inventory_item_id in (
457 select distinct bcolu.config_item_id
458 from bom_cto_order_lines_upg bcolu
459 where bcolu.sequence = p_seq
460 and bcolu.status = 'BOM_BULK');
461
462 WriteToLog('Rows inserted into MRP Default Assignment Set::' ||sql%rowcount, 2);
463
464 --
465 -- update status to 'MRP_SRC'
466 --
467 update bom_cto_order_lines_upg
468 set status = 'MRP_SRC'
469 where sequence = p_seq
470 and status = 'BOM_BULK';
471
472 WriteToLog('Updated status to MRP_SRC for rows::'||sql%rowcount, 3);
473
474 END LOOP; /* main wrapper loop */
475
476
477 --
478 -- Delete rows from CTO assignment set
479 --
480 delete from mrp_sr_assignments
481 where assignment_set_id = l_cto_aset_id;
482
483 WriteToLog('Rows deleted from CTO Seeded Assignment Set::' ||sql%rowcount, 2);
484
485 If l_error_flag = 'Y' Then
486 retcode :=1;
487 End if;
488
489 EXCEPTION
490
491 WHEN fnd_api.g_exc_error THEN
492 WriteToLog('ERROR: Expected error in CTO_Bom_Rtg_Pk.Update_Boms_Rtgs:: '|| l_stmt_num ||'::'||sqlerrm, 1);
493 WriteToLog('+++++++++++++++++++++++++++++++++++++++++++++++++++');
494 WriteToLog('Update Configuration Boms completed with WARNING');
495 WriteToLog('+++++++++++++++++++++++++++++++++++++++++++++++++++');
496 errbuf := 'Progam completed with warning';
497 retcode := 1; -- exit with warning
498
499 WHEN fnd_api.g_exc_unexpected_error THEN
500 WriteToLog('ERROR: Unexpected error in CTO_Bom_Rtg_Pk.Update_Boms_Rtgs:: '|| l_stmt_num ||'::'||sqlerrm, 1);
501 WriteToLog('+++++++++++++++++++++++++++++++++++++++++++++++++++');
502 WriteToLog('Update Configuration Boms completed with ERROR');
503 WriteToLog('+++++++++++++++++++++++++++++++++++++++++++++++++++');
504 errbuf := 'Progam completed with error';
505 retcode := 2; -- exit with error
506
507 WHEN OTHERS then
508 WriteToLog('ERROR: Others error in CTO_Bom_Rtg_Pk.Update_Boms_Rtgs:: '|| l_stmt_num ||'::'||sqlerrm, 1);
509 WriteToLog('+++++++++++++++++++++++++++++++++++++++++++++++++++');
510 WriteToLog('Update Configuration Boms completed with ERROR');
511 WriteToLog('+++++++++++++++++++++++++++++++++++++++++++++++++++');
512 errbuf := 'Progam completed with error';
513 retcode := 2; -- exit with error
514
515
516 END Update_Boms_Rtgs;
517
518
519 /*-------------------------------------------------------------+
520 Name : update_in_src_orgs
521 This procedure creates a config item's bom and routing
522 in all of the proper sourcing orgs based on the base
523 model's sourcing rules.
524 +-------------------------------------------------------------*/
525 PROCEDURE Update_In_Src_Orgs(
526 pLineId in number, -- Current Model Line ID
527 pModelId in number,
528 pConfigId in number,
529 pFlowCalc in number,
530 xReturnStatus out NOCOPY varchar2,
531 xMsgCount out NOCOPY number,
532 xMsgData out NOCOPY varchar2
533 )
534
535 IS
536
537 lStmtNum number;
538 lStatus number;
539 lItmBillId number;
540 lCfgBillId number;
541 lCfgRtgId number;
542 xBillId number;
543 lXErrorMessage varchar2(100);
544 lXMessageName varchar2(100);
545 lXTableName varchar2(100);
546 XTableName varchar2(100);
547 lLineId number;
548 lModelId number;
549 lParentAtoLineId number := pLineId;
550 lErrBuf varchar2(80);
551 lTotLeadTime number := 0;
552 lOEValidationOrg number;
553 lOrderedQty number;
554 lLeadTime number;
555
556 CURSOR cSrcOrgs IS
557 select distinct bcso.organization_id,
558 bcolu.perform_match,
559 bcolu.option_specific,
560 bcso.create_bom bom_create,
561 bcso.model_item_id,
562 bcso.config_item_id
563 from bom_cto_src_orgs bcso,
564 bom_cto_order_lines_upg bcolu
565 where bcso.line_id = pLineId
566 and bcso.model_item_id = pModelId
567 and bcso.config_item_id is not null
568 and bcso.line_id = bcolu.line_id ;
569
570 v_bom_created number := 0 ;
571 v_config_bom_exists number := 0 ;
572 l_program_id number;
573
574 BEGIN
575
576 WriteToLog('Processing line_id '||pLineId, 3);
577
578 xReturnStatus := fnd_api.g_ret_sts_success;
579
580 --
581 -- Get total lead time for this config based on OE validation org
582 --
583 select nvl(schedule_ship_date,sysdate), nvl(program_id, 0)
584 into g_SchShpDate, l_program_id
585 from bom_cto_order_lines_upg
586 where line_id = pLineId ;
587
588 g_SchShpDate := greatest(g_SchShpDate, sysdate);
589 WriteToLog('Schedule Ship Date is '||g_SchShpDate, 4);
590
591 --
592 -- For canned configs not on open or closed order lines, estimated release
593 -- date should be the sysdate. It does not make sense to calculate a mfg
594 -- date that is in the past. So, we will skip ERD calculation in this case.
595 --
596 IF l_program_id = 99 THEN /* canned config */
597 lTotLeadTime := 0;
598 ELSE
599
600 lStmtNum := 40;
601 -- get oevalidation org
602 WriteToLog('Before getting validation org', 5);
603
604 begin
605 /* BUGFIX# 3484511 */
606 select nvl( oe_sys_parameters.value( 'MASTER_ORGANIZATION_ID' , oel.org_id) , -99)
607 into lOEValidationOrg
608 from oe_order_lines_all oel
609 where oel.line_id = pLineid ;
610
611 exception
612 when no_data_found then
613 SELECT master_organization_id
614 INTO lOEValidationOrg
615 FROM mtl_parameters mp, bom_cto_order_lines_upg bcol
616 WHERE bcol.ship_from_org_id = mp.organization_id
617 and bcol.line_id = pLineid;
618 end; --Bugfix 6376208: The main query will run into no data found if SO having line_id has been purged.
619
620 IF (lOEValidationOrg = -99) THEN
621 WriteToLog('+++++++++++++++++++++++++++++++++++++++++++++++++++++++++', 1);
622 WriteToLog('ERROR: Unable to find OE Validation Org for line_id '||pLineId, 1);
623 WriteToLog('+++++++++++++++++++++++++++++++++++++++++++++++++++++++++', 1);
624 raise FND_API.G_EXC_ERROR;
625 END IF;
626
627 WriteToLog('Validation Org is :' || lOEValidationOrg,4);
628
629 lStmtNum := 41;
630 LOOP
631 select bcolu.line_id,
632 bcolu.inventory_item_id,
633 bcolu.parent_ato_line_id,
634 bcolu.ordered_quantity
635 into lLineId, lModelId, lParentAtoLineId, lOrderedQty
636 from bom_cto_order_lines_upg bcolu
637 where bcolu.line_id = lParentAtoLineId;
638
639 WriteToLog('lLineId: ' || to_char(lLineId), 5);
640 WriteToLog('lModelId: ' || to_char(lModelId), 5);
641 WriteToLog('lParentAtoLineId: ' || to_char(lParentAtoLineId), 5);
642
643 lStmtNum := 42;
644 lStatus := get_model_lead_time(
645 lModelId,
646 lOEValidationOrg,
647 lOrderedQty,
648 lLeadTime,
649 lErrBuf);
650
651 IF (lStatus = 0) THEN
652 WriteToLog('++++++++++++++++++++++++++++++++++++++++++++++', 1);
653 WriteToLog('ERROR: Error in get_model_lead_time', 1);
654 WriteToLog('++++++++++++++++++++++++++++++++++++++++++++++', 1);
655 raise FND_API.G_EXC_ERROR;
656 ELSE
657 lTotLeadTime := lLeadTime + lTotLeadTime;
658 END IF;
659
660 EXIT WHEN lLineId = lParentAtoLineId; -- when we reach the top model
661 END LOOP;
662
663 WriteToLog('Total lead time is: ' || to_char(lTotLeadTime), 3);
664
665 END IF; /* canned config */
666
667 FOR lNextRec IN cSrcOrgs LOOP
668
669 WriteToLog('Entered cSrcOrgs loop ' , 4);
670 WriteToLog('Update_in_src_orgs: model ' || pModelId || ' Line ' || pLineId || ' config ' || lNextRec.config_item_id || ' org ' || lNextRec.organization_id , 1);
671
672 if( lNextRec.bom_create = 'Y' ) then
673 -- check if model bom exists in src org
674 lStmtNum := 10;
675 WriteToLog('In update_in_src_orgs. Item: ' ||to_char(pConfigId) || '. Org ' || to_char(lNextRec.organization_id), 5);
676
677 lStmtNum := 100;
678 lStatus := CTO_CONFIG_BOM_PK.check_bom(
679 pItemId => pModelId,
680 pOrgId => lNextRec.organization_id,
681 xBillId => lItmBillId);
682
683 WriteToLog('Returned from check_bom for model with result '
684 || to_char(lStatus), 3);
685
686 if (lStatus = 1) then /* model BOM exists in this org */
687 lStmtNum := 110;
688 lStatus := CTO_CONFIG_BOM_PK.check_bom(
689 pItemId => pConfigId,
690 pOrgId => lNextRec.organization_id,
691 xBillId => lItmBillId);
692
693 WriteToLog('Returned from check_bom for config with result '
694 || to_char(lStatus), 3);
695
696
697 if (lStatus <> 1) then
698
699 -- config BOM does not exist
700 lStmtNum := 125;
701
702 lStatus := CTO_UPDATE_BOM_RTG_PK.update_bom_rtg_loop(
703 pModelId => pModelId,
704 pConfigId => pConfigId,
705 pOrgId => lNextRec.organization_id,
706 pLineId => pLineId,
707 pLeadTime => lTotLeadTime,
708 pFlowCalc => pFlowCalc,
709 xBillId => lCfgBillId,
710 xRtgId => lCfgRtgId,
711 xErrorMessage => lXErrorMessage,
712 xMessageName => lXMessageName,
713 xTableName => lXTableName);
714
715 WriteToLog('Returned from Update_bom_rtg_loop with status: '
716 || to_char(lStatus), 1);
717
718
719
720
721 if (lStatus <> 1) then
722 WriteToLog('ERROR: Update_Bom_Rtg_Loop returned with error.', 1);
723 raise fnd_api.g_exc_error;
724 end if;
725
726 v_bom_created := v_bom_created + 1 ; /* increment bom created variable */
727 lStmtNum := 130;
728
729
730 end if; -- end check config bom
731 WriteToLog('Update_in_src_orgs: after bom loop creation.', 5);
732 else /* model BOM does not exist in this org */
733 -- Added by Renga Kannan to handle the exception
734 WriteToLog('There is no bill for this model in this org',1);
735 WriteToLog('Model id :'||to_char(pModelId),1);
736 WriteToLog('Org id :'||to_char(lNextRec.organization_id),1);
737 /*
738 ** Warning **
739 ** Achtung **
740 ** Model BOM does not exist should not be treated as an error
741 **
742 ** Case: Specific Org
743 ** BOM is created only in the end manufacturing org
744 **
745 ** Case: All Org
746 ** BOM is created in all orgs where the model bom exists
747 **
748 ** In either case the error will be caught if the bom
749 ** was not created even in a single org.
750
751 cto_msg_pub.cto_message('BOM','CTO_BOM_NOT_DEFINED');
752 raise fnd_api.g_exc_error;
753 */
754 end if; -- end check model bom
755
756 else /* create_config_bom = 'N' */
757 WriteToLog('Create_config_bom parameter is set to N in this org',3);
758 WriteToLog('Model id :'||to_char(pModelId),3);
759 WriteToLog('Org id ;'||to_char(lNextRec.organization_id),3);
760
761 end if ; /* create_config_bom = 'Y' */
762
763 end loop;
764
765 if( v_bom_created = 0 and v_config_bom_exists = 0 ) then
766 WriteToLog('BOM not created in any orgs.', 1);
767 end if ;
768
769
770 EXCEPTION
771
772 WHEN fnd_api.g_exc_error THEN
773 WriteToLog('Expected error in update_in_src_orgs: '||to_char(lStmtNum)||'::'||sqlerrm, 1);
774 xReturnStatus := fnd_api.g_ret_sts_error;
775 cto_msg_pub.count_and_get
776 ( p_msg_count => xMsgCount
777 , p_msg_data => xMsgData
778 );
779
780 WHEN fnd_api.g_exc_unexpected_error THEN
781 WriteToLog('Unexpected error in update_in_src_orgs: '||to_char(lStmtNum)||'::'||sqlerrm, 1);
782 xReturnStatus := fnd_api.g_ret_sts_unexp_error ;
783 cto_msg_pub.count_and_get
784 ( p_msg_count => xMsgCount
785 , p_msg_data => xMsgData
786 );
787
788 WHEN OTHERS then
789 WriteToLog('Others error in update_in_src_orgs: '||to_char(lStmtNum)||'::'||sqlerrm, 1);
790 xReturnStatus := fnd_api.g_ret_sts_unexp_error;
791 cto_msg_pub.count_and_get
792 ( p_msg_count => xMsgCount
793 , p_msg_data => xMsgData
794 );
795
796
797 END Update_In_Src_Orgs;
798
799
800 PROCEDURE WriteToLog (p_message in varchar2 default null,
801 p_level in number default 0) IS
802 BEGIN
803 IF gDebugLevel >= p_level THEN
804 fnd_file.put_line (fnd_file.log, p_message);
805 END IF;
806 END WriteToLog;
807
808
809 PROCEDURE update_item_num(
810 p_parent_bill_seq_id IN NUMBER,
811 p_item_num IN OUT NOCOPY NUMBER,
812 p_org_id IN NUMBER,
813 p_seq_increment IN NUMBER);
814
815
816 FUNCTION Update_Bom_Rtg_Loop(
817 pModelId in number,
818 pConfigId in number,
819 pOrgId in number,
820 pLineId in number,
821 pLeadTime in number,
822 pFlowCalc in number,
823 xBillId out NOCOPY number,
824 xRtgId out NOCOPY number,
825 xErrorMessage out NOCOPY varchar2,
826 xMessageName out NOCOPY varchar2,
827 xTableName out NOCOPY varchar2)
828 RETURN INTEGER
829 IS
830
831 lStmtNum number;
832 lCnt number := 0;
833 lConfigBillId number;
834 lstatus number;
835 lEstRelDate date;
836 lOpseqProfile number;
837 lItmBillID number;
838
839 v_missed_line_id number;
840 v_missed_item varchar2(50);
841 v_config_item varchar2(50);
842 v_model varchar2(50);
843 v_config varchar2(50);
844 v_missed_line_number varchar2(50);
845 v_order_number number;
846 l_token CTO_MSG_PUB.token_tbl;
847 lcreate_item number; -- 2986192
848 lorg_code varchar2(3); -- 2986192
849
850 /* Cursor to select dropped lines */
851 cursor missed_lines ( xlineid number,
852 xconfigbillid number,
853 xEstRelDate date ) is /* Effectivity_date changes */
854 select line_id
855 from bom_cto_order_lines_upg
856 where parent_ato_line_id=xlineid
857 and parent_ato_line_id <> line_id /* to avoid selecting top model */
858 minus
859 select revised_item_sequence_id /* new column used to store line_id */
860 from bom_inventory_comps_interface
861 where bill_sequence_id = xconfigbillid
862 and greatest(sysdate, xEstRelDate ) >= effectivity_date
863 and (( disable_date is null ) or ( disable_date is not null and greatest(sysdate, xEstRelDate) <= disable_date )) ;
864
865 CURSOR consolidate_components IS
866 select distinct
867 b1.bill_sequence_id,
868 b1.operation_seq_num,
869 b1.component_sequence_id,
870 b1.component_item_id,
871 b1.component_quantity,
872 nvl(b1.optional_on_model, 1)
873 from
874 bom_inventory_comps_interface b1,
875 bom_inventory_comps_interface b2
876 where b1.bill_sequence_id = b2.bill_sequence_id
877 and b1.component_sequence_id <> b2.component_sequence_id
878 and b1.operation_seq_num = b2.operation_seq_num
879 and b1.component_item_id = b2.component_item_id
880 and b1.bill_sequence_id = lConfigBillId
881 order by b1.bill_sequence_id,
882 b1.component_item_id,
883 b1.operation_seq_num,
884 b1.component_quantity,
885 b1.component_sequence_id;
886
887 p_item_num number := 0;
888 p_bill_seq_id number;
889 p_seq_increment number;
890 lCfgRtgId number;
891 lCfmRtgflag number;
892 l_ser_start_op number;
893 l_ser_code number;
894 l_row_count number := 0;
895 lItmRtgId number;
896 l_status VARCHAR2(1);
897 l_industry VARCHAR2(1);
898 l_schema VARCHAR2(30);
899 lLineId number;
900 lModelId number;
901 lParentAtoLineId number := pLineId;
902 lOrderedQty number;
903 lLeadTime number;
904 lErrBuf varchar2(80);
905 lTotLeadTime number := 0;
906 lOEValidationOrg number;
907
908 /*New variables added for bugfix 1906371 and 1935580*/
909 lmodseqnum number;
910 lmodtyp number;
911 lmodrtgseqid number;
912 lmodnewCfgRtgId number;
913 lopseqnum number;
914 loptyp number;
915 lrtgseqid number;
916 lnewCfgRtgId number;
917
918 l_test number;
919
920 lBomId number;
921 lSaveBomId number;
922 lSaveOpSeqNum number;
923 lSaveItemId number;
924 lSaveCompSeqId number;
925 lTotalQty number;
926 lSaveOptional number;
927 lCompSeqId number ;
928 lItemId number ;
929 lqty number ;
930 lOptional number ;
931 l_from_sequence_id number;
932
933 l_install_cfm BOOLEAN;
934
935 UP_DESC_ERR exception;
936
937 /* ------------------------------------------------------+
938 cursor to be used to copy attachments for all
939 operations fro model to operations on config
940 requset id column contains model_op_seq_id.
941 +--------------------------------------------------------*/
942
943 cursor allops is
944 select operation_sequence_id, request_id
945 from bom_operation_sequences
946 where routing_sequence_id = lCfgRtgId;
947
948 /* ------------------------------------------------------+
949 cursor added for bugfix 1906371 and 1935580 to select
950 distinct combinations of op_seq_num and op_type
951 +--------------------------------------------------------*/
952
953 cursor get_op_seq_num (pRtgId number) is
954 select distinct operation_seq_num,nvl(operation_type,1)
955 from bom_operation_sequences
956 --where last_update_login=pRtgId;
957 where config_routing_id=pRtgId;
958
959
960 v_program_id bom_cto_order_lines_upg.program_id%type;
961
962 TYPE mod_opclass_rtg_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
963
964
965 tModOpClassRtg mod_opclass_rtg_tab;
966 tDistinctRtgSeq mod_opclass_rtg_tab;
967 lexists varchar2(1);
968 k number;
969
970 l_config_creation varchar(1);
971 l_program_id number;
972 l_hold_source_rec OE_Holds_PVT.Hold_Source_REC_type;
973 l_return_status varchar2(1);
974 l_msg_count number;
975 l_msg_data varchar2(240);
976 l_hold_result_out varchar2(30);
977 l_order_num number;
978 l_line_num number;
979 l_line_number varchar2(100);
980
981 CURSOR c_holds IS
982 select oel.line_id,
983 oel.header_id header_id,
984 oeh.order_number order_num,
985 to_char(oel.line_number)||'.'||to_char(oel.shipment_number) ||decode(oel.option_number,NULL,NULL,'.'||to_char(oel.option_number)) line_num
986 from bom_cto_order_lines_upg bcolu,
987 oe_order_lines_all oel,
988 oe_order_headers_all oeh
989 where bcolu.config_item_id = pConfigId
990 and nvl(bcolu.program_id, -99) <> 99
991 and bcolu.line_id = oel.ato_line_id
992 and oel.item_type_code = 'CONFIG'
993 and oel.header_id = oeh.header_id;
994
995
996
997 v_orders_present number := 0 ;
998
999 -- start 3674833
1000 -- Collection to store comp seq
1001
1002
1003 TYPE seq_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1004
1005
1006 model_comp_seq_id_arr seq_tab;
1007 component_item_id_arr seq_tab;
1008 operation_seq_num_arr seq_tab; --4244576
1009
1010 club_component_sequence_id number;
1011 prev_comp_item_id number;
1012
1013 -- end 3674833
1014
1015
1016
1017
1018
1019 /* begin 02-14-2005 Sushant */
1020
1021 -- 3222932 Variable declaration of new code
1022
1023 -- Collection to store all eff and disable dates
1024
1025 TYPE date_tab IS TABLE OF DATE INDEX BY BINARY_INTEGER;
1026 asc_date_arr date_tab;
1027
1028 -- Collection to store clubbed quantity with new date window
1029
1030 TYPE club_rec IS RECORD (
1031 eff_dt DATE,
1032 dis_dt DATE,
1033 qty NUMBER,
1034 row_id rowid
1035 );
1036
1037 TYPE club_tab IS TABLE OF club_rec INDEX BY BINARY_INTEGER;
1038
1039 club_tab_arr club_tab;
1040
1041
1042 lrowid ROWID;
1043
1044 -- Get all components to be clubbed
1045 -- bug 4244576: It is possible that the same item is existing at op seq 15, 25, 30, 15. In
1046 -- this case the two records at 15 needs to be clubbed but not the once at 25 and 30. Going
1047 -- just by item_id will club all 4 records. We need to go by item_id and op_seq.
1048 cursor club_comp is
1049 select distinct b1.component_item_id item_id, b1.operation_seq_num
1050 from bom_inventory_comps_interface b1,bom_inventory_comps_interface b2
1051 where b1.bill_sequence_id = b2.bill_sequence_id
1052 and b1.component_sequence_id <> b2.component_sequence_id
1053 and b1.operation_seq_num = b2.operation_seq_num
1054 and b1.component_item_id = b2.component_item_id
1055 and b1.bill_sequence_id = lConfigBillId; /* No changes required for LBM Project */
1056
1057
1058 -- variables for debugging
1059 dbg_eff_date Date;
1060 dbg_dis_date Date;
1061 dbg_qty Number;
1062
1063 -- Cursor for debugging
1064 cursor c1_debug( xItemId number, xOperation_seq_num number) is
1065 select effectivity_date eff_date,
1066 nvl (disable_date,g_SchShpDate) dis_date,
1067 component_quantity cmp_qty
1068 from bom_inventory_comps_interface
1069 where bill_sequence_id = lConfigBillId
1070 and component_item_id = xItemId
1071 and operation_seq_num = xOperation_seq_num; --4244576
1072 -- bugfix 3985173
1073 -- new cursor for component sequence
1074 cursor club_comp_seq ( xComponentItemId number, xOperation_seq_num number ) is
1075 select bic.component_sequence_id comp_seq_id
1076 from bom_inventory_components bic,
1077 bom_bill_of_materials bom
1078 where bom.assembly_item_id = pConfigId
1079 and bom.organization_id = pOrgId
1080 and bic.bill_sequence_id = bom.bill_sequence_id
1081 and bic.component_item_id = xComponentItemId
1082 and bic.operation_seq_num = xOperation_seq_num; --4244576
1083
1084
1085
1086 v_zero_qty_count number ;
1087 l_token1 CTO_MSG_PUB.token_tbl;
1088 v_model_item_name varchar2(2000) ;
1089
1090 /* end 02-14-2005 Sushant */
1091
1092 /* LBM Project */
1093 v_diff_basis_string varchar2(2000);
1094 v_sub_diff_basis_string varchar2(2000);
1095
1096 l_new_line varchar2(10) := fnd_global.local_chr(10);
1097
1098 basis_model_comp_seq_id_arr seq_tab;
1099 basis_component_item_id_arr seq_tab;
1100
1101
1102
1103
1104 v_overlap_check number := 0 ;
1105
1106 TYPE num_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1107
1108 v_t_overlap_comp_item_id num_tab;
1109 v_t_overlap_src_op_seq_num num_tab;
1110 v_t_overlap_src_eff_date date_tab;
1111 v_t_overlap_src_disable_date date_tab;
1112 v_t_overlap_dest_op_seq_num num_tab;
1113 v_t_overlap_dest_eff_date date_tab;
1114 v_t_overlap_dest_disable_date date_tab;
1115
1116 /* LBM Project */
1117 l_model_name varchar2(1000);
1118 l_comp_name varchar2(1000);
1119 l_org_name varchar2(1000);
1120
1121 --- Renga
1122
1123 cursor Debug_cur is
1124 select assembly_item_id,component_item_id,operation_seq_num,max(disable_date) disable_date
1125 from bom_inventory_comps_interface
1126 where bill_sequence_id = lconfigbillid
1127 group by assembly_item_id,component_item_id,operation_seq_num;
1128
1129 --- End Renga
1130
1131 l_batch_id Number;
1132 l_token2 CTO_MSG_PUB.token_tbl;
1133
1134 BEGIN
1135
1136 WriteToLog('Entering Update_Bom_Rtg_Loop', 2);
1137
1138 xBillId := 0;
1139 lStmtNum := 10;
1140 select bom_inventory_components_s.nextval
1141 into lConfigBillId
1142 from dual;
1143
1144 WriteToLog('Creating Bill:: ' || lConfigBillId, 2);
1145
1146 lStmtNum := 20;
1147 BEGIN
1148 select CAL.CALENDAR_DATE
1149 into lEstRelDate
1150 from bom_calendar_dates cal,
1151 mtl_system_items msi,
1152 bom_cto_order_lines_upg bcolu,
1153 mtl_parameters mp
1154 where msi.organization_id = pOrgId
1155 and msi.inventory_item_id = pModelId
1156 and bcolu.line_id = pLineId
1157 and bcolu.inventory_item_id = msi.inventory_item_id
1158 and mp.organization_id = msi.organization_id
1159 and cal.calendar_code = mp.calendar_code
1160 and cal.exception_set_id = mp.calendar_exception_set_id
1161 and cal.seq_num =
1162 (select cal2.prior_seq_num - pLeadTime
1163 from bom_calendar_dates cal2
1164 where cal2.calendar_code = mp.calendar_code
1165 and cal2.exception_set_id = mp.calendar_exception_set_id
1166 and cal2.calendar_date = trunc(bcolu.schedule_ship_date));
1167 EXCEPTION
1168 WHEN no_data_found THEN
1169 xErrorMessage := ' Error in calculating Estimated Release date '; xMessageName := 'CTO_NO_CALENDAR';
1170 WriteToLog('+++++++++++++++++++++++++++++++++++++++++++++', 1);
1171 WriteToLog('ERROR: Error in calculating Estimated Release Date', 1);
1172 WriteToLog('+++++++++++++++++++++++++++++++++++++++++++++', 1);
1173 return(1);
1174 END;
1175
1176 lEstRelDate := greatest(lEstRelDate, sysdate);
1177 WriteToLog('Estimated Release Date is : ' || lEstRelDate, 3);
1178 g_EstRelDate := lEstRelDate;
1179 WriteToLog('Global Estimated Release Date is : ' || g_EstRelDate, 3);
1180
1181 /*-------------------------------------------------------------------------+
1182 Check profile option 'Inherit Operation_sequence_number'. If it is set
1183 to 'Yes', ensure that the childern default the operation sequence number
1184 from its parent, if not already assigned.
1185 +--------------------------------------------------------------------------*/
1186 lOpseqProfile := FND_PROFILE.Value('BOM:CONFIG_INHERIT_OP_SEQ');
1187 WriteToLog('Profile Config_inherit_op_seq is ' || lOpseqProfile, 3);
1188
1189 lStmtNum := 80;
1190 if lOpseqProfile = 1 then
1191 WriteToLog('Calling inherit_op_seq_ml with line id ' ||
1192 to_char(pLineId) || ' in org ' ||
1193 to_char(pOrgId), 4);
1194
1195 lStatus := inherit_op_seq_ml(pLineId, pOrgId,pModelId,lConfigBillId,xErrorMessage,xMessageName);
1196 if lStatus <> 1 then
1197 WriteToLog('Inherit_op_seq_ml returned with error for line id: '|| to_char(pLineId), 1);
1198 return(1);
1199 end if;
1200
1201 else
1202 /*-----------------------------------------------------------+
1203 First:
1204 All the chosen option items/models/Classes associated
1205 with the new configuration items will be loaded into the
1206 BOM_INVENTORY_COMPS_INTERFACE table.
1207 +-------------------------------------------------------------*/
1208
1209 xTableName := 'BOM_INVENTORY_COMPS_INTERFACE';
1210 lStmtNum := 30;
1211
1212 -- rkaza. bug 4524248. bom structure import enhancements.
1213 -- Added batch_id
1214
1215 insert into BOM_INVENTORY_COMPS_INTERFACE
1216 (
1217 operation_seq_num,
1218 component_item_id,
1219 last_update_date,
1220 last_updated_by,
1221 creation_date,
1222 created_by,
1223 last_update_login,
1224 item_num,
1225 component_quantity,
1226 component_yield_factor,
1227 component_remarks,
1228 effectivity_date,
1229 change_notice,
1230 implementation_date,
1231 disable_date,
1232 attribute_category,
1233 attribute1,
1234 attribute2,
1235 attribute3,
1236 attribute4,
1237 attribute5,
1238 attribute6,
1239 attribute7,
1240 attribute8,
1241 attribute9,
1242 attribute10,
1243 attribute11,
1244 attribute12,
1245 attribute13,
1246 attribute14,
1247 attribute15,
1248 planning_factor,
1249 quantity_related,
1250 so_basis,
1251 optional,
1252 mutually_exclusive_options,
1253 include_in_cost_rollup,
1254 check_atp,
1255 shipping_allowed,
1256 required_to_ship,
1257 required_for_revenue,
1258 include_on_ship_docs,
1259 include_on_bill_docs,
1260 low_quantity,
1261 high_quantity,
1262 acd_type,
1263 old_component_sequence_id,
1264 component_sequence_id,
1265 bill_sequence_id,
1266 request_id,
1267 program_application_id,
1268 program_id,
1269 program_update_date,
1270 wip_supply_type,
1271 pick_components,
1272 model_comp_seq_id,
1273 supply_subinventory,
1274 supply_locator_id,
1275 bom_item_type,
1276 optional_on_model, -- New columns for configuration
1277 parent_bill_seq_id, -- BOM restructure project
1278 plan_level, -- Used by CTO only
1279 revised_item_sequence_id,
1280 assembly_item_id /* Bug Fix: 4147224 */
1281 , basis_type, /* LBM project */
1282 batch_id
1283 )
1284 select
1285 nvl(ic1.operation_seq_num,1),
1286 decode(bcol1.config_item_id, NULL, ic1.component_item_id, -- new
1287 bcol1.config_item_id),
1288 SYSDATE, -- last_updated_date
1289 1, -- last_updated_by
1290 SYSDATE, -- creation_date
1291 1, -- created_by
1292 1, -- last_update_login
1293 ic1.item_num,
1294 Round(
1295 CTO_UTILITY_PK.convert_uom( bcol1.order_quantity_uom, msi_child.primary_uom_code, bcol1.ordered_quantity ,
1296 msi_child.inventory_item_id )
1297 / CTO_UTILITY_PK.convert_uom(bcol2.order_quantity_uom, msi_parent.primary_uom_code, NVL(bcol2.ordered_quantity,1) , msi_parent.inventory_item_id ) , 7) , /* 02-14-2005 Sushant */
1298 -- Decimal-Qty Support for Option Items
1299 ic1.component_yield_factor,
1300 ic1.component_remarks, --Bugfix 7188428
1301 --NULL, --ic1.component_remark
1302 -- TRUNC(SYSDATE), -- effective date
1303 -- 3222932 If eff_date > sysdate , insert eff_Date else insert sysdate
1304 decode(
1305 greatest(ic1.effectivity_date,sysdate), ic1.effectivity_date , ic1.effectivity_date , sysdate ),
1306 /* 02-14-2005 sushant */
1307 NULL, -- change notice
1308 SYSDATE, -- implementation_date
1309 -- NULL, -- disable date
1310 nvl(ic1.disable_date,g_futuredate), -- 3222932 /* 02-14-2005 Sushant */
1311 ic1.attribute_category,
1312 ic1.attribute1,
1313 ic1.attribute2,
1314 ic1.attribute3,
1315 ic1.attribute4,
1316 ic1.attribute5,
1317 ic1.attribute6,
1318 ic1.attribute7,
1319 ic1.attribute8,
1320 ic1.attribute9,
1321 ic1.attribute10,
1322 ic1.attribute11,
1323 ic1.attribute12,
1324 ic1.attribute13,
1325 ic1.attribute14,
1326 ic1.attribute15,
1327 100, -- planning_factor */
1328 2, -- quantity_related */
1329 decode(bcol1.config_item_id, NULL, decode(ic1.bom_item_type,4,ic1.so_basis,2),
1330 2), -- so_basis */
1331 2, -- optional */
1332 2, -- mutually_exclusive_options */
1333 decode(bcol1.config_item_id, NULL, decode(ic1.bom_item_type,4, ic1.include_in_cost_rollup, 2), 1), -- Cost_rollup */
1334 decode(bcol1.config_item_id, NULL, decode(ic1.bom_item_type,4, ic1.check_atp, 2), 2), -- check_atp */
1335 2, -- shipping_allowed = NO */
1336 2, -- required_to_ship = NO */
1337 ic1.required_for_revenue,
1338 ic1.include_on_ship_docs,
1339 ic1.include_on_bill_docs,
1340 NULL, -- low_quantity */
1341 NULL, -- high_quantity */
1342 NULL, -- acd_type */
1343 NULL, --old_component_sequence_id */
1344 bom_inventory_components_s.nextval, -- component sequence id */
1345 lConfigBillId, -- bill sequence id */
1346 NULL, -- request_id */
1347 NULL, -- program_application_id */
1348 NULL, -- program_id */
1349 NULL, -- program_update_date */
1350 ic1.wip_supply_type,
1351 2, -- pick_components = NO */
1352 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.
1353 ic1.supply_subinventory,
1354 ic1.supply_locator_id,
1355 --ic1.bom_item_type
1356 decode(bcol1.config_item_id, NULL, ic1.bom_item_type, 4),
1357 1, --optional_on_model,
1358 ic1.bill_sequence_id, --parent_bill_seq_id,
1359 (bcol1.plan_level-bcol2.plan_level), --plan_level
1360 bcol1.line_id,
1361 bcol3.inventory_item_id /* Bug Fix: 4147224 */
1362 , nvl(ic1.basis_type,1), /* LBM project */
1363 cto_msutil_pub.bom_batch_id
1364 from
1365 bom_inventory_components ic1,
1366 bom_cto_order_lines_upg bcol1, -- Option
1367 bom_cto_order_lines_upg bcol2, -- Parent-Model
1368 bom_cto_order_lines_upg bcol3, -- Parent-component
1369 mtl_system_items msi_child , /* 02-14-2005 Sushant */ -- begin bugfix 1653881
1370 mtl_system_items msi_parent /* 02-14-2005 Sushant */ -- begin bugfix 1653881
1371 where ic1.bill_sequence_id = (
1372 select common_bill_sequence_id
1373 from bom_bill_of_materials bbm
1374 where organization_id = pOrgId
1375 and alternate_bom_designator is null
1376 and assembly_item_id =(
1377 select distinct assembly_item_id
1378 from bom_bill_of_materials bbm1,
1379 bom_inventory_components bic1
1380 where bbm1.common_bill_sequence_id = bic1.bill_sequence_id
1381 and component_sequence_id = bcol1.component_sequence_id
1382 and bbm1.assembly_item_id = bcol3.inventory_item_id ))
1383 and ic1.component_item_id = bcol1.inventory_item_id
1384 /* begin 02-14-2005 Sushant */
1385 and msi_child.inventory_item_id = bcol1.inventory_item_id
1386 and msi_child.organization_id = pOrgId
1387 and msi_parent.inventory_item_id = bcol2.inventory_item_id
1388 and msi_parent.organization_id = pOrgId
1389 /* end 02-14-2005 Sushant */
1390 -- and ic1.effectivity_date <= g_SchShpDate /* New approach for effectivity dates */
1391 and ic1.implementation_date is not null --bug4122212
1392 -- and NVL(ic1.disable_date, (lEstRelDate + 1)) > lEstRelDate
1393 and ( ic1.disable_date is null or
1394 (ic1.disable_date is not null and ic1.disable_date >= sysdate ) -- New Approach for effectivity dates /* bug #3389846 */
1395 )
1396 and (( ic1.optional = 1 and ic1.bom_item_type = 4)
1397 or
1398 ( ic1.bom_item_type in (1,2)))
1399 and bcol1.ordered_quantity <> 0
1400 and bcol1.line_id <> bcol2.line_id
1401 and bcol1.parent_ato_line_id = bcol2.line_id
1402 and bcol1.parent_ato_line_id is not null
1403 and bcol1.link_to_line_id is not null
1404 and bcol2.line_id = pLineId
1405 and bcol2.ship_from_org_id = bcol1.ship_from_org_id
1406 and (bcol3.parent_ato_line_id = bcol1.parent_ato_line_id
1407 or
1408 bcol3.line_id = bcol1.parent_ato_line_id)
1409 and bcol3.line_id = bcol1.link_to_line_id;
1410
1411 WriteToLog('Inserted ' || sql%rowcount ||' rows into BOM_INVENTORY_COMPS_INTERFACE.',3);
1412
1413
1414 /* begin 04-04-2005 */
1415
1416 select count(*) into v_zero_qty_count from bom_inventory_comps_interface
1417 where bill_sequence_id = lConfigBillId and component_quantity = 0 ;
1418
1419
1420 WriteToLog( 'MODELS: CHECK Raise Exception for Zero QTY Count ' || v_zero_qty_count , 1 ) ;
1421
1422 if( v_zero_qty_count > 0 ) then
1423
1424 WriteToLog( 'SHOULD Raise Exception for Zero QTY Count ' || v_zero_qty_count , 1 ) ;
1425
1426 select concatenated_segments into v_model_item_name
1427 from mtl_system_items_kfv
1428 where inventory_item_id = pModelId
1429 and rownum = 1 ;
1430
1431
1432 l_token1(1).token_name := 'MODEL_NAME';
1433 l_token1(1).token_value := v_model_item_name ;
1434
1435
1436 cto_msg_pub.cto_message('BOM','CTO_ZERO_BOM_COMP', l_token1 );
1437
1438 raise fnd_api.g_exc_error;
1439
1440
1441
1442
1443 end if ;
1444
1445
1446
1447 /* end 04-04-2005 */
1448
1449
1450
1451
1452
1453
1454 /* New Approach for effectivity dates */
1455
1456
1457
1458
1459
1460
1461 /*---------------------------------------------------------------+
1462 Second:
1463 All the standard component items associated
1464 with the new configuration items will be loaded into the
1465 BOM_INVENTORY_COMPS_INTERFACE table.
1466 +----------------------------------------------------------------*/
1467
1468 lStmtNum := 50;
1469 xTableName := 'BOM_INVENTORY_COMPS_INTERFACE';
1470 insert into BOM_INVENTORY_COMPS_INTERFACE
1471 (
1472 operation_seq_num,
1473 component_item_id,
1474 last_update_date,
1475 last_updated_by,
1476 creation_date,
1477 created_by,
1478 last_update_login,
1479 item_num,
1480 component_quantity,
1481 component_yield_factor,
1482 component_remarks,
1483 effectivity_date,
1484 change_notice,
1485 implementation_date,
1486 disable_date,
1487 attribute_category,
1488 attribute1,
1489 attribute2,
1490 attribute3,
1491 attribute4,
1492 attribute5,
1493 attribute6,
1494 attribute7,
1495 attribute8,
1496 attribute9,
1497 attribute10,
1498 attribute11,
1499 attribute12,
1500 attribute13,
1501 attribute14,
1502 attribute15,
1503 planning_factor,
1504 quantity_related,
1505 so_basis,
1506 optional,
1507 mutually_exclusive_options,
1508 include_in_cost_rollup,
1509 check_atp,
1510 shipping_allowed,
1511 required_to_ship,
1512 required_for_revenue,
1513 include_on_ship_docs,
1514 include_on_bill_docs,
1515 low_quantity,
1516 high_quantity,
1517 acd_type,
1518 old_component_sequence_id,
1519 component_sequence_id,
1520 bill_sequence_id,
1521 request_id,
1522 program_application_id,
1523 program_id,
1524 program_update_date,
1525 wip_supply_type,
1526 pick_components,
1527 model_comp_seq_id,
1528 supply_subinventory,
1529 supply_locator_id,
1530 bom_item_type,
1531 optional_on_model, -- New columns for configuration
1532 parent_bill_seq_id, -- BOM restructure project.
1533 plan_level -- Used by CTO only.
1534 , basis_type, /* LBM project */
1535 batch_id
1536 )
1537 select
1538 nvl(ic1.operation_seq_num,1),
1539 ic1.component_item_id,
1540 SYSDATE, -- last_updated_date
1541 1, -- last_updated_by
1542 SYSDATE, -- creation_date
1543 1, -- created_by
1544 1, -- last_update_login
1545 ic1.item_num,
1546 decode( nvl(ic1.basis_type,1), 1 , Round( ( ic1.component_quantity * ( bcol1.ordered_quantity
1547 / bcol2.ordered_quantity)), 7 ) , Round(ic1.component_quantity , 7 ) ) , /* Decimal-Qty Support for Option Items, LBM project */
1548 ic1.component_yield_factor,
1549 ic1.component_remarks, --Bugfix 7188428
1550 --NULL, -- ic1.component_remark
1551 -- TRUNC(SYSDATE), -- effective date
1552 decode( -- 3222932 /* 02-14-2005 Sushant */
1553 greatest(ic1.effectivity_date,sysdate), ic1.effectivity_date , ic1.effectivity_date , sysdate ),
1554 NULL, -- change notice
1555 SYSDATE, -- implementation_date
1556 -- NULL, -- disable date
1557 nvl(ic1.disable_date,g_futuredate), -- 3222932 /* 02-14-2005 Sushant */
1558 ic1.attribute_category,
1559 ic1.attribute1,
1560 ic1.attribute2,
1561 ic1.attribute3,
1562 ic1.attribute4,
1563 ic1.attribute5,
1564 ic1.attribute6,
1565 ic1.attribute7,
1566 ic1.attribute8,
1567 ic1.attribute9,
1568 ic1.attribute10,
1569 ic1.attribute11,
1570 ic1.attribute12,
1571 ic1.attribute13,
1572 ic1.attribute14,
1573 ic1.attribute15,
1574 100, -- planning_factor
1575 2, -- quantity_related
1576 ic1.so_basis,
1577 2, -- optional
1578 2, -- mutually_exclusive_options
1579 ic1.include_in_cost_rollup,
1580 ic1.check_atp,
1581 2, -- shipping_allowed = NO
1582 2, -- required_to_ship = NO
1583 ic1.required_for_revenue,
1584 ic1.include_on_ship_docs,
1585 ic1.include_on_bill_docs,
1586 NULL, -- low_quantity
1587 NULL, -- high_quantity
1588 NULL, -- acd_type
1589 NULL, -- old_component_sequence_id
1590 bom_inventory_components_s.nextval, -- component sequence id
1591 lConfigBillId, -- bill sequence id
1592 NULL, -- request_id
1593 NULL, -- program_application_id
1594 NULL, -- program_id
1595 NULL, -- program_update_date
1596 ic1.wip_supply_type,
1597 2, -- pick_components = NO
1598 (-1)*ic1.component_sequence_id, -- model comp seq for later use
1599 ic1.supply_subinventory,
1600 ic1.supply_locator_id,
1601 ic1.bom_item_type,
1602 2, --optional_on_model,
1603 ic1.bill_sequence_id, --parent_bill_seq_id,
1604 bcol1.plan_level+1-bcol2.plan_level --plan_level
1605 , nvl(ic1.basis_type,1), /* LBM project */
1606 cto_msutil_pub.bom_batch_id
1607 from
1608 bom_cto_order_lines_upg bcol1, -- component
1609 bom_cto_order_lines_upg bcol2, -- Model
1610 mtl_system_items si1,
1611 mtl_system_items si2,
1612 bom_bill_of_materials b,
1613 bom_inventory_components ic1
1614 where si1.organization_id = pOrgId
1615 and bcol1.inventory_item_id = si1.inventory_item_id
1616 and si1.bom_item_type in (1,2) -- model, option class
1617 and si2.inventory_item_id = bcol2.inventory_item_id
1618 and si2.organization_id = si1.organization_id
1619 and si2.bom_item_type = 1
1620 and ((bcol1.parent_ato_line_id = bcol2.line_id
1621 and ( bcol1.bom_item_type <> 1
1622 or
1623 (bcol1.bom_item_type = 1 and nvl(bcol1.wip_supply_type, 0) = 6))
1624 )
1625 or bcol1.line_id = bcol2.line_id
1626 )
1627 and bcol2.line_id = pLineId
1628 and si1.organization_id = b.organization_id
1629 and bcol1.inventory_item_id = b.assembly_item_id
1630 and b.alternate_bom_designator is NULL
1631 and b.common_bill_sequence_id = ic1.bill_sequence_id
1632 and ic1.optional = 2 -- optional = no
1633 -- and ic1.effectivity_date <= greatest( NVL(g_SchShpDate,sysdate),sysdate) /* New Approach for effectivity dates */
1634 and ic1.implementation_date is not null
1635 -- and NVL(ic1.disable_date,NVL(lEstRelDate, SYSDATE)+1) > NVL(lEstRelDate,SYSDATE)
1636 -- and NVL(ic1.disable_date,SYSDATE) >= SYSDATE
1637 and ( ic1.disable_date is null or
1638 (ic1.disable_date is not null and ic1.disable_date >= sysdate )) /* New Approach for Effectivity Dates */
1639 and ic1.bom_item_type = 4;
1640
1641 WriteToLog('Inserted ' || sql%rowcount ||' rows into BOM_INVENTORY_COMPS_INTERFACE.',3);
1642
1643
1644
1645 /* begin Extend Effectivity Dates for Option Items with disable date */
1646
1647 oe_debug_pub.add('create_bom_ml:: Config bill id = '||lconfigbillid,1);
1648
1649 For debug_rec in debug_cur
1650 Loop
1651 WriteToLog('create_bom_ml: : Assembly_item_id = '||debug_rec.assembly_item_id,1);
1652 WriteToLog('create_bom_ml: : Componenet_item_id = '||debug_rec.component_item_id,1);
1653 WriteToLog('create_bom_ml: : operation_sequence_num = '||debug_rec.operation_seq_num,1);
1654 WriteToLog('create_bom_ml: : MAxDisbale Date = '||debug_rec.disable_date,1);
1655 WriteToLog('==================================',1);
1656 End Loop;
1657
1658 update bom_inventory_comps_interface
1659 set disable_date = g_futuredate
1660 where (component_item_id, nvl(assembly_item_id,-1),disable_date)
1661 in ( select
1662 component_item_id, nvl(assembly_item_id,-1),max(disable_date)
1663 from bom_inventory_comps_interface
1664 where bill_sequence_id = lConfigBillId
1665 group by component_item_id, assembly_item_id
1666 )
1667 and bill_sequence_id = lConfigBillId
1668 and disable_date <> g_futuredate ;
1669
1670 If PG_DEBUG <> 0 Then
1671 WriteToLog('Create_bom_ml: Extending the disable dates to futuure date = '||sql%rowcount,1);
1672 WriteToLog('Create_bom_ml: lconfigBillId = '||to_char(lConfigBillid),1);
1673 End if;
1674
1675
1676
1677 /* end Extend Effectivity Dates for Option Items with disable date */
1678
1679
1680
1681 /* New Approach for effectivity dates */
1682 /* begin Check for Overlapping Effectivity Dates */
1683 v_overlap_check := 0 ;
1684
1685 begin
1686 select 1 into v_overlap_check
1687 from dual
1688 where exists
1689 ( select * from bom_inventory_comps_interface
1690 where bill_sequence_id = lConfigBillId
1691 group by component_item_id, assembly_item_id
1692 having count(distinct operation_seq_num) > 1
1693 );
1694 exception
1695 when others then
1696 v_overlap_check := 0 ;
1697 end;
1698
1699
1700 if(v_overlap_check = 1) then
1701
1702 begin
1703 select s1.component_item_id,
1704 s1.operation_seq_num, s1.effectivity_date, s1.disable_date,
1705 s2.operation_Seq_num , s2.effectivity_date, s2.disable_date
1706 BULK COLLECT INTO
1707 v_t_overlap_comp_item_id,
1708 v_t_overlap_src_op_seq_num, v_t_overlap_src_eff_date, v_t_overlap_src_disable_date ,
1709 v_t_overlap_dest_op_seq_num , v_t_overlap_dest_eff_date, v_t_overlap_dest_disable_date
1710 from bom_inventory_comps_interface s1 , bom_inventory_comps_interface s2
1711 where s1.component_item_id = s2.component_item_id and s1.assembly_item_id = s2.assembly_item_id
1712 and s1.effectivity_date between s2.effectivity_date and s2.disable_date
1713 and s1.component_sequence_id <> s2.component_sequence_id ;
1714
1715
1716 exception
1717 when others then
1718 null ;
1719 end ;
1720
1721
1722 if( v_t_overlap_src_op_seq_num.count > 0 ) then
1723 for i in v_t_overlap_src_op_seq_num.first..v_t_overlap_src_op_seq_num.last
1724 loop
1725 IF PG_DEBUG <> 0 THEN
1726 WriteToLog (' The following components have overlapping dates ', 1);
1727 WriteToLog (' COMP ' || ' OP SEQ' || 'EFFECTIVITY DT ' || ' DISABLE DT ' || ' OVERLAPS ' ||
1728 ' OP SEQ' || 'EFFECTIVITY DT ' || ' DISABLE DT ' , 1);
1729
1730 WriteToLog ( v_t_overlap_comp_item_id(i) ||
1731 ' ' || v_t_overlap_src_op_seq_num(i) ||
1732 ' ' || v_t_overlap_src_eff_date(i) ||
1733 ' ' || v_t_overlap_src_disable_date(i) ||
1734 ' OVERLAPS ' ||
1735 ' ' || v_t_overlap_src_op_seq_num(i) ||
1736 ' ' || v_t_overlap_src_eff_date(i) ||
1737 ' ' || v_t_overlap_src_disable_date(i) , 1);
1738
1739 END IF;
1740
1741 cto_msg_pub.cto_message('BOM','CTO_OVERLAP_DATE_ERROR');
1742
1743 end loop ;
1744
1745 raise fnd_api.g_exc_error;
1746
1747 end if ;
1748
1749 end if;
1750
1751
1752 end if; /* end of check lOpseqProfile = 1 */
1753
1754
1755
1756 lStmtNum := 51;
1757 --
1758 -- checking for dropped components
1759 --
1760 WriteToLog ('Checking for dropped components', 3);
1761 BEGIN
1762
1763 select substrb(concatenated_segments,1,50)
1764 into v_config
1765 from mtl_system_items_kfv
1766 where organization_id = pOrgId
1767 and inventory_item_id = pConfigId ;
1768 WriteToLog('Config name is.. '|| v_config ,5);
1769
1770 lcreate_item := nvl(FND_PROFILE.VALUE('CTO_CONFIG_EXCEPTION'), 1);
1771 WriteToLog ('Config exception profile:: '||lcreate_item, 3);
1772 WriteToLog ('Estimated Release date :: '||to_char(lEstRelDate),1);
1773
1774 open missed_lines(pLineId,lConfigBillId, lEstRelDate ); /* New Approach for effectivity dates */
1775 loop
1776 fetch missed_lines into v_missed_line_id;
1777
1778 exit when missed_lines%NOTFOUND;
1779
1780 lStmtNum := 52;
1781 BEGIN
1782 WriteToLog('Select missed component details.. ' || v_missed_line_id ,3);
1783
1784
1785
1786
1787 begin
1788
1789
1790 select substrb(msi.concatenated_segments,1,50),
1791 to_char(oel.line_number)||'.'||to_char(oel.shipment_number) ||decode(oel.option_number,NULL,NULL,'.'||to_char(option_number)),
1792 oeh.order_number
1793 into v_missed_item,v_missed_line_number,v_order_number
1794 from mtl_system_items_kfv msi, oe_order_lines_all oel,oe_order_headers_all oeh
1795 where msi.organization_id = oel.ship_from_org_id
1796 and msi.inventory_item_id = oel.inventory_item_id
1797 and oel.header_id = oeh.header_id
1798 and oel.line_id = v_missed_line_id;
1799
1800
1801 exception
1802 when no_data_found then
1803
1804
1805 /* Fix for bug 3402690 */
1806 WriteToLog('No data found, must be preconfigured item .. ' ,5);
1807
1808 select substrb(msi.concatenated_segments,1,50),
1809 'Not Available' ,
1810 -1
1811 into v_missed_item,v_missed_line_number,v_order_number
1812 from mtl_system_items_kfv msi, bom_cto_order_lines_upg bcolu
1813 where msi.organization_id = bcolu.ship_from_org_id
1814 and msi.inventory_item_id = bcolu.inventory_item_id
1815 and bcolu.line_id = v_missed_line_id;
1816
1817
1818
1819
1820
1821 when others then
1822 raise ;
1823
1824
1825 end ;
1826
1827
1828
1829
1830 lStmtNum := 53;
1831 WriteToLog('Select model.. ' ,5);
1832 select substrb(concatenated_segments,1,50)
1833 into v_model
1834 from mtl_system_items_kfv
1835 where organization_id = pOrgId
1836 and inventory_item_id = pModelId ;
1837
1838 lStmtNum := 54;
1839 WriteToLog('Select Org.. ' ,5);
1840 select organization_code
1841 into lOrg_code
1842 from mtl_parameters
1843 where organization_id =pOrgId ;
1844
1845 if ( lcreate_item = 1 ) then
1846 WriteToLog('+++++++++++++++++++++++++++++++++++++++++++++++++', 1);
1847 WriteToLog('WARNING: The component '||v_missed_item
1848 || ' on Line Number '||v_missed_line_number
1849 || ' in organization ' || lOrg_code
1850 || ' was not included in the configured item''s bill. ',1);
1851 WriteToLog ('Configuration Item Name : '||v_config,1);
1852 WriteToLog ('Model Name : '||v_model,1);
1853 WriteToLog ('Order Number : '||v_order_number,1);
1854 WriteToLog('+++++++++++++++++++++++++++++++++++++++++++++++++', 1);
1855 else
1856 WriteToLog('+++++++++++++++++++++++++++++++++++++++++++++++++', 1);
1857 WriteToLog ('ERROR: The configured item BOM was not created because component '||v_missed_item || ' on Line Number '||v_missed_line_number
1858 || ' in organization ' || lOrg_code
1859 || ' could not be included in the configured item''s bill. ',1);
1860 WriteToLog ('Configuration Item Name : '||v_config,1);
1861 WriteToLog ('Model Name : '||v_model,1);
1862 WriteToLog ('Order Number : '||v_order_number,1);
1863 WriteToLog('+++++++++++++++++++++++++++++++++++++++++++++++++', 1);
1864
1865 end if;
1866
1867 EXCEPTION -- exception for stmt 52 ,53 and 54
1868 when others then
1869 WriteToLog('Others excepn from stmt '||lStmtNum ||':'||sqlerrm, 1);
1870 raise fnd_api.g_exc_error;
1871
1872 END ;
1873
1874 end loop; /* missed lines cursor */
1875
1876 /* gDropItem is set to 0 . Not resetting this to 1
1877 for next order in the batch since even when items are
1878 dropped for one order in the batch , the whole batch
1879 should end with warning */
1880
1881 if missed_lines%ROWCOUNT > 0 then
1882 CTO_CONFIG_BOM_PK.gDropItem := 0;
1883 lStmtNum := 55;
1884 --
1885 -- Put all open order lines having this config item on hold
1886 --
1887 select nvl(config_creation, '1')
1888 into l_config_creation
1889 from bom_cto_order_lines_upg
1890 where line_id = pLineId;
1891
1892 WriteToLog('l_config_creation:: '||l_config_creation, 3);
1893
1894 --IF (l_program_id <> 99) THEN
1895 IF l_config_creation = 3 THEN
1896 FOR v_holds in c_holds LOOP
1897 --
1898 -- apply hold if one does not already exist
1899 --
1900 OE_HOLDS_PUB.Check_Holds (
1901 p_api_version => 1.0
1902 ,p_line_id => v_holds.line_id
1903 ,x_result_out => l_hold_result_out
1904 ,x_return_status => l_return_status
1905 ,x_msg_count => l_msg_count
1906 ,x_msg_data => l_msg_data);
1907
1908 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1909 WriteToLog('Failed in Check Holds with expected error.' ,1);
1910 raise FND_API.G_EXC_ERROR;
1911
1912 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1913 WriteToLog('Failed in Check Holds with unexpected error.' ,1);
1914 raise FND_API.G_EXC_UNEXPECTED_ERROR;
1915
1916 ELSE
1917 WriteToLog('Success in Check Holds.' ,1);
1918 if l_hold_result_out = FND_API.G_FALSE then
1919
1920 WriteToLog('Calling OM api to apply hold.' ,1);
1921 l_hold_source_rec.hold_entity_code := 'O';
1922 l_hold_source_rec.hold_id := 55;
1923 l_hold_source_rec.hold_entity_id := v_holds.header_id;
1924 l_hold_source_rec.header_id := v_holds.header_id;
1925 l_hold_source_rec.line_id := v_holds.line_id;
1926
1927 OE_Holds_PUB.Apply_Holds (
1928 p_api_version => 1.0
1929 , p_hold_source_rec => l_hold_source_rec
1930 , x_return_status => l_return_status
1931 , x_msg_count => l_msg_count
1932 , x_msg_data => l_msg_data);
1933
1934 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1935 WriteToLog ('+++++++++++++++++++++++++++++++++++++++++++++', 1);
1936 WriteToLog ('ERROR: Apply_holds returned expected error.', 1);
1937 WriteToLog ('+++++++++++++++++++++++++++++++++++++++++++++', 1);
1938 raise fnd_api.g_exc_error;
1939
1940 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1941 WriteToLog ('+++++++++++++++++++++++++++++++++++++++++++++', 1);
1942 WriteToLog ('ERROR: Apply_holds returned unexpected error.', 1);
1943 WriteToLog ('+++++++++++++++++++++++++++++++++++++++++++++', 1);
1944 raise fnd_api.g_exc_error;
1945 END IF;
1946 l_order_num := v_holds.order_num;
1947 l_line_num := v_holds.line_num;
1948
1949 WriteToLog ('+++++++++++++++++++++++++++++++++++++++++++++', 1);
1950 WriteToLog ('WARNING: Order line put on hold due to dropped components on configuration item '|| v_config , 1);
1951 WriteToLog ('Order number: '||l_order_num, 1);
1952 WriteToLog ('Line number: '||l_line_num, 1);
1953 WriteToLog ('+++++++++++++++++++++++++++++++++++++++++++++', 1);
1954 ELSE /* l_hold_result_out */
1955 l_order_num := v_holds.order_num;
1956 l_line_num := v_holds.line_num;
1957
1958
1959 WriteToLog ('+++++++++++++++++++++++++++++++++++++++++++++', 1);
1960 WriteToLog ('Order line already on hold.', 1);
1961 WriteToLog ('Order number: '||l_order_num, 1);
1962 WriteToLog ('Line number: '||l_line_num, 1);
1963 WriteToLog ('+++++++++++++++++++++++++++++++++++++++++++++', 1);
1964 END IF; /* l_hold_result_out */
1965 END IF; /* check holds returns error */
1966 END LOOP;
1967 ELSE /* l_config_creation is 1 or 2 */
1968
1969 WriteToLog('Going to Get order Information .' ,1);
1970
1971
1972 begin
1973
1974 v_orders_present := 1 ;
1975
1976 select oel.line_id,
1977 oel.header_id,
1978 oeh.order_number,
1979 to_char(oel.line_number)||'.'||to_char(oel.shipment_number) ||decode(oel.option_number,NULL,NULL,'.'||to_char(option_number))
1980 into l_hold_source_rec.line_id,
1981 l_hold_source_rec.header_id,
1982 l_order_num,
1983 l_line_number
1984 from bom_cto_order_lines_upg bcolu,
1985 oe_order_lines_all oel,
1986 oe_order_headers_all oeh
1987 where bcolu.line_id = pLineId
1988 and bcolu.ato_line_id = oel.ato_line_id /* BUG 3396081 dropped component in lower config */
1989 and oel.item_type_code = 'CONFIG'
1990 and oel.header_id = oeh.header_id;
1991
1992
1993 EXCEPTION
1994 when no_data_found then
1995 WriteToLog('No Orders present for this configuration.' ,1);
1996 v_orders_present := 0 ;
1997
1998
1999 when others then
2000 raise ;
2001 END ;
2002
2003
2004
2005 if( v_orders_present = 1 ) then
2006
2007
2008 WriteToLog('Going to Check Holds .' ,1);
2009
2010 --
2011 -- apply hold if one does not already exist
2012 --
2013 OE_HOLDS_PUB.Check_Holds (
2014 p_api_version => 1.0
2015 ,p_line_id => pLineId
2016 ,x_result_out => l_hold_result_out
2017 ,x_return_status => l_return_status
2018 ,x_msg_count => l_msg_count
2019 ,x_msg_data => l_msg_data);
2020
2021 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
2022 WriteToLog('Failed in Check Holds with expected error.' ,1);
2023 raise FND_API.G_EXC_ERROR;
2024 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2025 WriteToLog('Failed in Check Holds with unexpected error.' ,1);
2026 raise FND_API.G_EXC_UNEXPECTED_ERROR;
2027 ELSE
2028 WriteToLog('Success in Check Holds.' ,1);
2029 if l_hold_result_out = FND_API.G_FALSE then
2030
2031 WriteToLog('Calling OM api to apply hold.' ,1);
2032 l_hold_source_rec.hold_entity_code := 'O';
2033 l_hold_source_rec.hold_id := 55;
2034 --l_hold_source_rec.hold_entity_id := v_holds.header_id;
2035 l_hold_source_rec.hold_entity_id := l_hold_source_rec.header_id;
2036 --l_hold_source_rec.header_id := v_holds.header_id;
2037 --l_hold_source_rec.line_id := v_holds.ato_line_id;
2038
2039 WriteToLog('Going to Apply Holds .' ,1);
2040
2041
2042 OE_Holds_PUB.Apply_Holds (
2043 p_api_version => 1.0
2044 , p_hold_source_rec => l_hold_source_rec
2045 , x_return_status => l_return_status
2046 , x_msg_count => l_msg_count
2047 , x_msg_data => l_msg_data);
2048
2049 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
2050 WriteToLog ('+++++++++++++++++++++++++++++++++++++++++++++', 1);
2051 WriteToLog ('ERROR: Apply_holds returned expected error.', 1);
2052 WriteToLog ('+++++++++++++++++++++++++++++++++++++++++++++', 1);
2053 raise fnd_api.g_exc_error;
2054
2055 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2056 WriteToLog ('+++++++++++++++++++++++++++++++++++++++++++++', 1);
2057 WriteToLog ('ERROR: Apply_holds returned unexpected error.', 1);
2058 WriteToLog ('+++++++++++++++++++++++++++++++++++++++++++++', 1);
2059 raise fnd_api.g_exc_error;
2060 END IF;
2061 WriteToLog ('+++++++++++++++++++++++++++++++++++++++++++++', 1);
2062 WriteToLog ('WARNING: Order line put on hold due to dropped components on configuration item '|| v_config , 1);
2063 WriteToLog ('Order number: '||l_order_num, 1);
2064 WriteToLog ('Line number: '||l_line_number, 1);
2065 WriteToLog ('+++++++++++++++++++++++++++++++++++++++++++++', 1);
2066 ELSE
2067 WriteToLog ('+++++++++++++++++++++++++++++++++++++++++++++', 1);
2068 WriteToLog ('Order line already on hold.', 1);
2069 WriteToLog ('Order number: '||l_order_num, 1);
2070 WriteToLog ('Line number: '||l_line_number, 1);
2071 WriteToLog ('+++++++++++++++++++++++++++++++++++++++++++++', 1);
2072 END IF; /* l_hold_results */
2073 END IF; /* line not on hold */
2074
2075 END IF; /* check for orders present */
2076
2077 END IF; /* l_config_creation = 3 */
2078 --END IF; /*l_program_id <> 99 */
2079 if ( lcreate_item <> 1 ) then
2080 WriteToLog ('+++++++++++++++++++++++++++++++++++++++++++++', 1);
2081 WriteToLog ('ERROR: BOM for configuration item '||v_config||' is not created due to dropped components.', 1);
2082 WriteToLog ('+++++++++++++++++++++++++++++++++++++++++++++', 1);
2083 close missed_lines;
2084
2085 --
2086 -- Update status to 'ERROR'
2087 -- Update for all lines having this config if config creation = 3
2088 --
2089 IF l_config_creation = 3 THEN
2090 update bom_cto_order_lines_upg bcolu1
2091 set bcolu1.status = 'ERROR'
2092 where bcolu1.ato_line_id in
2093 (select bcolu2.ato_line_id
2094 from bom_cto_order_lines_upg bcolu2
2095 where config_item_id = pConfigId);
2096
2097 WriteToLog('Rows updated to status ERROR::'||sql%rowcount, 1);
2098 ELSE
2099 update bom_cto_order_lines_upg bcolu1
2100 set bcolu1.status = 'ERROR'
2101 where bcolu1.ato_line_id =
2102 (select bcolu2.ato_line_id
2103 from bom_cto_order_lines_upg bcolu2
2104 where bcolu2.line_id = pLineId);
2105
2106 WriteToLog('Rows updated to status ERROR::'||sql%rowcount, 1);
2107 END IF;
2108 return(1);
2109 end if;
2110 end if;
2111 close missed_lines;
2112
2113 EXCEPTION -- exception for stmt 51 and 55
2114 when others then
2115 WriteToLog ('Failed in stmt ' || lStmtNum || ' with error: '||sqlerrm, 1);
2116 raise fnd_api.g_exc_error;
2117 END ; /* check for dropped components */
2118
2119 /*---------------------------------------------------------------+
2120 Third : Get the base model row into BOM_INVENTORY_COMPONENTS
2121 +----------------------------------------------------------------*/
2122
2123 lStmtNum := 60;
2124 insert into BOM_INVENTORY_COMPS_INTERFACE
2125 (
2126 operation_seq_num,
2127 component_item_id,
2128 last_update_date,
2129 last_updated_by,
2130 creation_date,
2131 created_by,
2132 last_update_login,
2133 item_num,
2134 component_quantity,
2135 component_yield_factor,
2136 component_remarks,
2137 effectivity_date,
2138 change_notice,
2139 implementation_date,
2140 disable_date,
2141 attribute_category,
2142 attribute1,
2143 attribute2,
2144 attribute3,
2145 attribute4,
2146 attribute5,
2147 attribute6,
2148 attribute7,
2149 attribute8,
2150 attribute9,
2151 attribute10,
2152 attribute11,
2153 attribute12,
2154 attribute13,
2155 attribute14,
2156 attribute15,
2157 planning_factor,
2158 quantity_related,
2159 so_basis,
2160 optional,
2161 mutually_exclusive_options,
2162 include_in_cost_rollup,
2163 check_atp,
2164 shipping_allowed,
2165 required_to_ship,
2166 required_for_revenue,
2167 include_on_ship_docs,
2168 include_on_bill_docs,
2169 low_quantity,
2170 high_quantity,
2171 acd_type,
2172 old_component_sequence_id,
2173 component_sequence_id,
2174 bill_sequence_id,
2175 request_id,
2176 program_application_id,
2177 program_id,
2178 program_update_date,
2179 wip_supply_type,
2180 pick_components,
2181 model_comp_seq_id,
2182 bom_item_type,
2183 optional_on_model, -- New columns for configuration
2184 parent_bill_seq_id, -- BOM restructure project.
2185 plan_level -- Used by CTO only.
2186 , basis_type, /* LBM project */
2187 batch_id
2188 )
2189 select
2190 1, -- operation_seq_num
2191 bcol.inventory_item_id,
2192 SYSDATE, -- last_updated_date
2193 1, -- last_updated_by
2194 SYSDATE, -- creation_date
2195 1, -- created_by
2196 1, -- last_update_login
2197 9, -- item_num
2198 1, -- comp_qty
2199 1, -- yield_factor
2200 NULL, --ic1.component_remark
2201 SYSDATE, -- effective date -bug4150255: removed the trunc 04-10-2005
2202 NULL, -- change notice
2203 SYSDATE, -- implementation_date
2204 NULL, -- disable date
2205 NULL, -- attribute_category
2206 NULL, -- attribute1
2207 NULL, -- attribute2
2208 NULL, -- attribute3
2209 NULL, -- attribute4
2210 NULL, -- attribute5
2211 NULL, -- attribute6
2212 NULL, -- attribute7
2213 NULL, -- attribute8
2214 NULL, -- attribute9
2215 NULL, -- attribute10
2216 NULL, -- attribute11
2217 NULL, -- attribute12
2218 NULL, -- attribute13
2219 NULL, -- attribute14
2220 NULL, -- attribute15
2221 100, -- planning_factor
2222 2, -- quantity_related
2223 2, -- so_basis
2224 2, -- optional
2225 2, -- mutually_exclusive_options
2226 2, -- include_in_cost_rollup
2227 2, -- check_atp
2228 2, -- shipping_allowed = NO
2229 2, -- required_to_ship = NO
2230 2, -- required_for_revenue
2231 2, -- include_on_ship_docs
2232 2, -- include_on_bill_docs
2233 NULL, -- low_quantity
2234 NULL, -- high_quantity
2235 NULL, -- acd_type
2236 NULL, -- old_component_sequence_id
2237 bom_inventory_components_s.nextval, -- component sequence id
2238 lConfigBillId, -- bill sequence id
2239 NULL, -- request_id
2240 NULL, -- program_application_id
2241 NULL, -- program_id
2242 NULL, -- program_update_date
2243 6, -- wip_supply_type
2244 2, -- pick_components = NO
2245 NULL, -- model comp seq id for later use
2246 1, -- bom_item_type
2247 1, --optional_on_model,
2248 0, --parent_bill_seq_id,
2249 0 --plan_level
2250 , 1, -- basis_type /* LBM project */
2251 cto_msutil_pub.bom_batch_id
2252 from
2253 bom_cto_order_lines_upg bcol
2254 where bcol.line_id = pLineId
2255 and bcol.ordered_quantity <> 0
2256 and bcol.inventory_item_id = pModelId;
2257
2258 lCnt := sql%rowcount ;
2259 WriteToLog('Inserted ' || lCnt ||' rows into bom_inventory_comps_interface',3);
2260
2261 xBillId := lConfigBillId;
2262
2263 --
2264 -- create routing
2265 --
2266
2267 xRtgID := 0;
2268 lStatus := check_routing (pConfigId,
2269 pOrgId,
2270 lItmRtgId,
2271 lCfmRtgFlag );
2272
2273 if lStatus = 1 then
2274 WriteToLog('Config Routing' || lCfgRtgId || ' already exists ',2);
2275 GOTO ROUTING;
2276 end if;
2277
2278 /*-------------------------------------------------------------+
2279 Config does not have routing. If model also does not have
2280 routing, we do not need to do anything, return with success.
2281 +--------------------------------------------------------------*/
2282
2283 lCfmRtgFlag := NULL;
2284 lStatus := check_routing (pModelId,
2285 pOrgId,
2286 lItmRtgId,
2287 lCfmRtgFlag);
2288 if lStatus <> 1 then
2289 WriteToLog('Model Does not have a routing ',1);
2290 GOTO ROUTING;
2291 end if;
2292
2293 select bom_operational_routings_s.nextval
2294 into lCfgRtgId
2295 from dual;
2296
2297 xTableName := 'BOM_OPERATIONAL_ROUTING';
2298 lStmtNum := 30;
2299
2300 WriteToLog('Inserting the routing header information into bom_operational_routings..',5);
2301
2302 insert into bom_operational_routings
2303 (
2304 routing_sequence_id,
2305 assembly_item_id,
2306 organization_id,
2307 alternate_routing_designator,
2308 last_update_date,
2309 last_updated_by,
2310 creation_date,
2311 created_by,
2312 last_update_login,
2313 routing_type,
2314 common_routing_sequence_id,
2315 common_assembly_item_id,
2316 routing_comment,
2317 completion_subinventory,
2318 completion_locator_id,
2319 attribute_category,
2320 attribute1,
2321 attribute2,
2322 attribute3,
2323 attribute4,
2324 attribute5,
2325 attribute6,
2326 attribute7,
2327 attribute8,
2328 attribute9,
2329 attribute10,
2330 attribute11,
2331 attribute12,
2332 attribute13,
2333 attribute14,
2334 attribute15,
2335 request_id,
2336 program_application_id,
2337 program_id,
2338 program_update_date,
2339 line_id,
2340 mixed_model_map_flag,
2341 priority,
2342 cfm_routing_flag,
2343 total_product_cycle_time,
2344 ctp_flag,
2345 project_id,
2346 task_id
2347 )
2348 select
2349 lCfgRtgId, -- Routing Sequence Id
2350 pConfigId, -- assembly item Id
2351 pOrgId, -- Organization Id
2352 null, -- alternate routing designator
2353 sysdate, -- last update date
2354 gUserID, -- last updated by
2355 sysdate,
2356 gUserId, /* created_by */
2357 gLoginId, /* last_update_login */
2358 bor.routing_type, /* routing_type */
2359 lCfgRtgId, /* common_routing_sequence_id */
2360 null, /* common_assembly_item_id */
2361 bor.routing_comment,
2362 bor.completion_subinventory,
2363 bor.completion_locator_id,
2364 bor.attribute_category, -- 4049807
2365 bor.attribute1,
2366 bor.attribute2,
2367 bor.attribute3,
2368 bor.attribute4,
2369 bor.attribute5,
2370 bor.attribute6,
2371 bor.attribute7,
2372 bor.attribute8,
2373 bor.attribute9,
2374 bor.attribute10,
2375 bor.attribute11,
2376 bor.attribute12,
2377 bor.attribute13,
2378 bor.attribute14,
2379 bor.attribute15,
2380 null,
2381 null,
2382 -99, --program_id
2383 null,
2384 bor.line_id,
2385 bor.mixed_model_map_flag,
2386 bor.priority,
2387 bor.cfm_routing_flag,
2388 bor.total_product_cycle_time,
2389 bor.ctp_flag,
2390 bor.project_id,
2391 bor.task_id
2392 from
2393 bom_operational_routings bor,
2394 mtl_parameters mp
2395 where bor.assembly_item_id = pModelId
2396 and bor.organization_id = pOrgId
2397 and bor.alternate_routing_designator is null
2398 and mp.organization_id = pOrgId;
2399
2400 WriteToLog('Inserted Routing Header :' || lCfgRtgId, 4);
2401
2402 /*---------------------------------------------------------------+
2403 Udpate the mixed_model_map_flag. If the cfm_routing_flag
2404 is 1, then mixed_model_flag should be 1 if any flow_routing
2405 (primary or alternate) for the model has the mixed_model_flag
2406 equal to 1.
2407 +----------------------------------------------------------------*/
2408
2409 lStmtNum := 40;
2410
2411 update bom_operational_routings b
2412 set mixed_model_map_flag =
2413 ( select 1
2414 from bom_operational_routings bor
2415 where bor.assembly_item_id = pModelId
2416 and bor.organization_id = pOrgId
2417 and bor.cfm_routing_flag = 1
2418 and bor.mixed_model_map_flag = 1
2419 and bor.alternate_routing_designator is not NULL )
2420 where b.routing_sequence_id = lCfgRtgID
2421 and b.mixed_model_map_flag <> 1
2422 and b.cfm_routing_flag =1;
2423
2424 /*---------------------------------------------------------------+
2425 Identify all distinct operation steps to be picked up from
2426 Model routing and mark the last_update_login field
2427 for those to lCfgRtgId.
2428 Ignore option dependednt flag on operations types 2 and 3
2429 Copy from Model Item's routing only.
2430 -- Mandatory steps model
2431 -- option dependent steps associated with options/option Class
2432 -- "additional" option dependent steps associated with options/OC
2433 -- Option dependent steps associated with mandatory comps.
2434 -- "additional" Option dependent steps associated with mandatory comps.
2435 The "additional" operation steps are the steps stored in the new
2436 table bom_component_operations to support one-to-many BOM components
2437 to Routing steps.
2438 +----------------------------------------------------------------*/
2439
2440 lStmtNum := 50;
2441 /*
2442 Fixed Performance bug in the following sql. AS the following sql is huge one,
2443 performance team asked us to divide the sql into pieces. We are planning to
2444 insert a record from each sub query in the union class and then update it from
2445 temp table
2446 */
2447
2448 l_batch_id := bom_import_pub.get_batchid;
2449
2450 insert into bom_op_sequences_interface
2451 (
2452 operation_seq_num,
2453 operation_type,
2454 routing_sequence_id,
2455 batch_id
2456 )
2457 select distinct
2458 os1.operation_seq_num,
2459 nvl(operation_type,1),
2460 os1.routing_sequence_id,
2461 l_batch_id
2462 from
2463 bom_cto_order_lines_upg bcol1,
2464 mtl_system_items si1,
2465 bom_operational_routings or1,
2466 bom_operation_sequences os1
2467 where bcol1.line_id = pLineId
2468 and bcol1.inventory_item_id = pModelId
2469 and si1.organization_id = pOrgId -- this is the mfg org from src_orgs
2470 and si1.inventory_item_id = bcol1.inventory_item_id
2471 and si1.bom_item_type = 1 /* model */
2472 and or1.assembly_item_id = si1.inventory_item_id
2473 and or1.organization_id = si1.organization_id
2474 and or1.alternate_routing_designator is NULL
2475 and nvl(or1.cfm_routing_flag,2) = lCfmRtgflag
2476 and os1.routing_sequence_id = or1.common_routing_sequence_id
2477 and ( os1.operation_type in (2,3)
2478 or ( os1.option_dependent_flag = 2
2479 and nvl(os1.operation_type,1 ) = 1 ))
2480 and ( os1.disable_date is null or
2481 (os1.disable_date is not null and os1.disable_date >= sysdate ));
2482
2483 insert into bom_op_sequences_interface
2484 (
2485 operation_seq_num,
2486 operation_type,
2487 routing_sequence_id,
2488 batch_id
2489 )
2490 select distinct
2491 os1.operation_seq_num,
2492 NVL(os1.operation_type,1),
2493 os1.routing_sequence_id,
2494 l_batch_id
2495 from
2496 bom_cto_order_lines_upg bcol1, -- components
2497 bom_cto_order_lines_upg bcol2, -- parent models or option classes
2498 mtl_system_items msi,
2499 bom_inventory_components ic1,
2500 bom_bill_of_materials b1,
2501 bom_operational_routings or1,
2502 bom_operation_sequences os1
2503 where bcol1.parent_ato_line_id = pLineId /*AP*/
2504 and bcol1.item_type_code in ('CLASS','OPTION') /* OC and Option items */
2505 and bcol1.line_id <> bcol2.line_id
2506 and bcol2.inventory_item_id = msi.inventory_item_id
2507 and msi.organization_id = pOrgId -- new from src_orgs
2508 and msi.bom_item_type = 1
2509 and bcol2.line_id = pLineId
2510 and bcol2.ordered_quantity <> 0
2511 and bcol2.line_id = bcol1.link_to_line_id
2512 and ic1.bill_sequence_id = (
2513 select common_bill_sequence_id
2514 from bom_bill_of_materials bbm
2515 where organization_id = pOrgId
2516 and alternate_bom_designator is null
2517 and assembly_item_id =(
2518 select distinct assembly_item_id
2519 from bom_bill_of_materials bbm1,
2520 bom_inventory_components bic1
2521 where bbm1.common_bill_sequence_id = bic1.bill_sequence_id
2522 and component_sequence_id = bcol1.component_sequence_id
2523 and bbm1.assembly_item_id = bcol2.inventory_item_id ))
2524 and ic1.component_item_id = bcol1.inventory_item_id
2525 and ic1.effectivity_date<= g_SchShpdate
2526 and NVL(ic1.disable_date, (lEstRelDate + 1)) > lEstRelDate
2527 and b1.common_bill_sequence_id = ic1.bill_sequence_id
2528 and b1.assembly_item_id = bcol2.inventory_item_id -- fix to bug 1272142
2529 and b1.alternate_bom_designator is NULL
2530 and or1.assembly_item_id = b1.assembly_item_id
2531 and or1.organization_id = b1.organization_id
2532 and b1.organization_id = pOrgId --bug 1935580
2533 and or1.alternate_routing_designator is null
2534 and nvl(or1.cfm_routing_flag,2) = lCfmRtgFlag
2535 and ( os1.disable_date is null or
2536 (os1.disable_date is not null and os1.disable_date >= sysdate )) /* New Approach for Effectivity Dates */
2537 and os1.routing_sequence_id = or1.common_routing_sequence_id
2538 and ((os1.operation_seq_num = ic1.operation_seq_num)
2539 or (os1.operation_seq_num in
2540 (select bco.operation_seq_num
2541 from bom_component_operations bco
2542 where bco.component_sequence_id = ic1.component_sequence_id)))
2543 and os1.option_dependent_flag = 1
2544 and nvl(os1.operation_type,1) = 1;
2545
2546
2547 insert into bom_op_sequences_interface
2548 (
2549 operation_seq_num,
2550 operation_type,
2551 routing_sequence_id,
2552 batch_id
2553 )
2554 select
2555 distinct
2556 os1.operation_seq_num,
2557 nvl(os1.operation_type,1),
2558 os1.routing_sequence_id,
2559 l_batch_id
2560 from
2561 bom_operation_sequences os1,
2562 bom_operational_routings or1,
2563 mtl_system_items si2,
2564 bom_inventory_components ic1,
2565 bom_bill_of_materials b1,
2566 mtl_system_items si1
2567 where si1.organization_id = pOrgId
2568 and si1.inventory_item_id = pModelId
2569 and si1.bom_item_type = 1 /* model */
2570 and b1.organization_id = si1.organization_id
2571 and b1.assembly_item_id = si1.inventory_item_id
2572 and b1.alternate_bom_designator is null
2573 and or1.assembly_item_id = b1.assembly_item_id
2574 and or1.organization_id = b1.organization_id
2575 and or1.alternate_routing_designator is null
2576 and nvl(or1.cfm_routing_flag,2) = lCfmRtgFlag /*ensure correct OC rtgs*/
2577 and os1.routing_sequence_id = or1.common_routing_sequence_id
2578 and ( os1.disable_date is null or
2579 (os1.disable_date is not null and os1.disable_date >= sysdate )) /* New Approach for Effectivity Dates */
2580 and ic1.bill_sequence_id = b1.common_bill_sequence_id
2581 and ic1.optional = 2
2582 and ic1.implementation_date is not null
2583 and ( ic1.disable_date is null or
2584 (ic1.disable_date is not null and ic1.disable_date >= sysdate )) /* New Approach for Effectivity Dates */
2585 and si2.inventory_item_id = ic1.component_item_id
2586 and si2.organization_id = b1.organization_id
2587 and si2.bom_item_type = 4 /* standard */
2588 and os1.option_dependent_flag = 1
2589 and ((os1.operation_seq_num = ic1.operation_seq_num)
2590 or (os1.operation_seq_num in
2591 (select bco.operation_seq_num
2592 from bom_component_operations bco
2593 where bco.component_sequence_id = ic1.component_sequence_id)))
2594 and nvl(os1.operation_type,1) = 1;
2595
2596
2597 -- Fixed by Renga Kannan on 05/23/06
2598 -- Fixed bug 5228179
2599 -- Start updating config_routing_id for performance reasons
2600 Update bom_operation_sequences
2601 set config_routing_id = lCfgRtgId,
2602 last_update_date = glast_update_date
2603 Where (
2604 operation_seq_num,
2605 nvl(operation_type,1),
2606 routing_sequence_id) In
2607 (select operation_seq_num,
2608 nvl(operation_type,1),
2609 routing_sequence_id
2610 from bom_op_sequences_interface
2611 where batch_id = l_batch_id)
2612 and implementation_date is not null
2613 and ( disable_date is null or
2614 (disable_date is not null and disable_date >= sysdate ))
2615 RETURNING routing_sequence_id BULK COLLECT INTO tModOpClassRtg;
2616
2617 WriteToLog('Model Routing : Marked ' || sql%rowcount || ' records for insertion',4);--moved here for 4492875
2618
2619 delete from bom_op_sequences_interface where batch_id = l_batch_id;
2620
2621 if tModOpClassRtg.count > 0 then
2622 k := 1;
2623 tDistinctRtgSeq(k) := tModOpClassRtg(1);
2624 for i in tModOpClassRtg.FIRST..tModOpClassRtg.LAST
2625 loop
2626 lexists := 'N';
2627 for j in tDistinctRtgSeq.FIRST..tDistinctRtgSeq.LAST
2628 loop
2629 if tDistinctRtgSeq(j) = tModOpClassRtg(i) then
2630 lexists := 'Y';
2631 exit;
2632 end if;
2633 end loop;
2634 if lexists = 'N' then
2635 k := k+1;
2636 tDistinctRtgSeq(k) := tModOpClassRtg(i);
2637 end if;
2638 end loop;
2639
2640 end if;
2641
2642 --- Added by Renga Kannan
2643
2644
2645
2646 if( tDistinctRtgSeq.count > 0 ) then
2647 for i in tDistinctRtgSeq.first..tDistinctRtgSeq.last
2648 loop
2649 if( tDistinctRtgSeq.exists(i) ) then
2650 WriteToLog('Distinct Model Routing Seq Id: '||tDistinctRtgSeq(i),4);
2651 end if ;
2652 end loop ;
2653
2654 else
2655 WriteToLog('Distinct Table contains ' || tDistinctRtgSeq.count, 4 ) ;
2656 end if ;
2657
2658
2659 lStmtNum := 51;
2660 lmodnewCfgRtgId := lCfgRtgId * (-1);
2661 lmodseqnum:=0;
2662 lmodtyp:=0;
2663 lmodrtgseqid :=0;
2664
2665 open get_op_seq_num(lCfgRtgId);
2666
2667 loop
2668 fetch get_op_seq_num into lmodseqnum,lmodtyp;
2669 exit when get_op_seq_num%notfound;
2670
2671 WriteToLog('Op Seq # : ' || lmodseqnum || ' Op Type : ' || lmodtyp ,4);
2672 WriteToLog('Estimated release date lEstRelDate '|| to_char(lEstRelDate,'mm-dd-yy:hh:mi:ss'), 4);
2673
2674 select max(routing_sequence_id) into lmodrtgseqid
2675 from bom_operation_sequences
2676 where operation_seq_num = lmodseqnum
2677 and nvl(operation_type,1)= lmodtyp
2678 --and last_update_login=lCfgRtgId
2679 and config_routing_id = lCfgRtgId
2680 and last_update_date = glast_update_date;
2681
2682 WriteToLog('Max. Routing Seq Id : ' || lmodrtgseqid, 4);
2683
2684 update bom_operation_sequences
2685 --set last_update_login=lmodnewCfgRtgId
2686 set config_routing_id=lmodnewCfgRtgId
2687 where operation_seq_num = lmodseqnum
2688 and nvl(operation_type,1)= lmodtyp
2689 and routing_sequence_id=lmodrtgseqid
2690 -- and effectivity_date <= greatest(nvl(lEstRelDate, sysdate),sysdate) NEw approach for effectivity dates
2691 and implementation_date is not null
2692 /*
2693 and nvl(disable_date,nvl(lEstRelDate, sysdate)+ 1) > NVL(lEstRelDate,sysdate)
2694 and nvl(disable_date,sysdate+1) > sysdate;--Bugfix 2771065
2695 */
2696 and ( disable_date is null or
2697 (disable_date is not null and disable_date >= sysdate )) ;/* New Approach for Effectivity Dates */
2698
2699
2700 WriteToLog('Update login to ' || lmodnewCfgRtgId ||' where routing seq Id is '||lmodrtgseqid, 4);
2701
2702 end loop;
2703 close get_op_seq_num;
2704
2705 WriteToLog('Model Routing : Marked ' || sql%rowcount || ' rows for insertion' , 4);
2706
2707 /*-----------------------------------------------------------------+
2708 First Insert :
2709 Load distinct operation steps from Model's routing
2710 +-------------------------------------------------------------------*/
2711
2712 lStmtNum := 60;
2713
2714 WriteToLog('Inserting into bom_operation_sequences - 1st insert ..',5);
2715
2716 if( tDistinctRtgSeq.count > 0 ) then
2717 FORALL i IN tDistinctRtgSeq.FIRST..tDistinctRtgSeq.LAST
2718 insert into bom_operation_sequences
2719 (
2720 operation_sequence_id,
2721 routing_sequence_id,
2722 operation_seq_num,
2723 last_update_date,
2724 last_updated_by,
2725 creation_date,
2726 created_by,
2727 last_update_login,
2728 standard_operation_id,
2729 department_id ,
2730 operation_lead_time_percent,
2731 minimum_transfer_quantity,
2732 count_point_type ,
2733 operation_description,
2734 effectivity_date,
2735 disable_date ,
2736 backflush_flag,
2737 option_dependent_flag,
2738 attribute_category ,
2739 attribute1,
2740 attribute2,
2741 attribute3,
2742 attribute4,
2743 attribute5,
2744 attribute6,
2745 attribute7,
2746 attribute8,
2747 attribute9,
2748 attribute10,
2749 attribute11,
2750 attribute12,
2751 attribute13,
2752 attribute14,
2753 attribute15,
2754 request_id, /* using this column to store model op seq id */
2755 program_application_id,
2756 program_id ,
2757 program_update_date,
2758 reference_flag,
2759 operation_type,
2760 process_op_seq_id,
2761 line_op_seq_id,
2762 yield,
2763 cumulative_yield,
2764 reverse_cumulative_yield,
2765 labor_time_calc,
2766 machine_time_calc,
2767 total_time_calc,
2768 labor_time_user,
2769 machine_time_user,
2770 total_time_user,
2771 net_planning_percent,
2772 implementation_date,-- new column for 11.5.4 BOM patchset
2773 x_coordinate, --bugfix 1765149
2774 y_coordinate --bugfix 1765149
2775 )
2776 select
2777 bom_operation_sequences_s.nextval, /* operation_sequence_id */
2778 lcfgrtgid, /* routing_sequence_id */
2779 os1.operation_seq_num,
2780 sysdate, /* last update date */
2781 gUserId, /* last updated by */
2782 sysdate, /* creation date */
2783 gUserId, /* created by */
2784 gLoginId, /* last update login */
2785 os1.standard_operation_id,
2786 os1.department_id,
2787 os1.operation_lead_time_percent,
2788 os1.minimum_transfer_quantity,
2789 os1.count_point_type,
2790 os1.operation_description,
2791 trunc(sysdate), /* effective date */
2792 null, /* disable date */
2793 os1.backflush_flag,
2794 2, /* option_dependent_flag */
2795 os1.attribute_category,
2796 os1.attribute1,
2797 os1.attribute2,
2798 os1.attribute3,
2799 os1.attribute4,
2800 os1.attribute5,
2801 os1.attribute6,
2802 os1.attribute7,
2803 os1.attribute8,
2804 os1.attribute9,
2805 os1.attribute10,
2806 os1.attribute11,
2807 os1.attribute12,
2808 os1.attribute13,
2809 os1.attribute14,
2810 os1.attribute15,
2811 os1.operation_sequence_id, /* using request_id column to store model op seq id */
2812 1, /* program_application_id */
2813 1, /* program_id */
2814 sysdate, /* program_update_date */
2815 reference_flag,
2816 nvl(operation_type,1),
2817 process_op_seq_id,
2818 line_op_seq_id,
2819 yield,
2820 cumulative_yield,
2821 reverse_cumulative_yield,
2822 labor_time_calc,
2823 machine_time_calc,
2824 total_time_calc,
2825 labor_time_user,
2826 machine_time_user,
2827 total_time_user,
2828 net_planning_percent,
2829 trunc(sysdate), -- new column for 11.5.4 BOM patchset
2830 os1.x_coordinate, --bugfix 1765149
2831 os1.y_coordinate --bugfix 1765149
2832 from
2833 bom_operation_sequences os1
2834 --where os1.last_update_login = lmodnewcfgrtgid
2835 where os1.config_routing_id = lmodnewcfgrtgid
2836 and os1.routing_sequence_id = tDistinctRtgSeq(i);
2837
2838 end if;
2839
2840 WriteToLog('Inserted ' || sql%rowcount || ' rows in BOS', 3);
2841
2842 tModOpClassRtg.DELETE;
2843 tDistinctRtgSeq.DELETE;
2844
2845 /*--------------------------------------------------------------+
2846 Intialize last_update_login column so that it can be used
2847 to identify steps from option class routings
2848 +---------------------------------------------------------------*/
2849
2850 lStmtNum := 70;
2851 update bom_operation_sequences
2852 --set last_update_login = - 1
2853 set config_routing_id = - 1
2854 --where last_update_login in (lCfgRtgId, lmodnewcfgrtgid);
2855 where config_routing_id in (lCfgRtgId, lmodnewcfgrtgid);
2856
2857 WriteToLog('Initialized config_routing_id for ' || sql%rowcount || ' rows in BOS', 4);
2858
2859 /*--------------------------------------------------------------+
2860 Mark all steps that need to be picked up from option
2861 Class routings
2862 -- Mandatory steps of Class routing
2863 -- Option dependent steps associated with options/option Class
2864 -- "Additional" option dependent steps associated with options/option Class
2865 -- Option dependent steps associated with mandatory comps.
2866 -- "Additional" option dependent steps associated with mandatory comps.
2867 The "additional" operation steps are the steps stored in the new
2868 table bom_component_operations to support one-to-many BOM components
2869 to Routing steps.
2870 +-------------------------------------------------------------*/
2871 lStmtNum := 80;
2872 update bom_operation_sequences
2873 --set last_update_login = lCfgRtgId
2874 set config_routing_id = lCfgRtgId
2875 ,last_update_date = glast_update_date -- 3180827
2876 where (
2877 operation_seq_num,
2878 nvl(operation_type,1),
2879 routing_sequence_id
2880 ) in (
2881 select
2882 distinct
2883 os1.operation_seq_num,
2884 nvl(os1.operation_type,1),
2885 os1.routing_sequence_id
2886 from
2887 mtl_system_items si1,
2888 bom_cto_order_lines_upg bcol,
2889 bom_operational_routings or1,
2890 bom_operation_sequences os1
2891 where bcol.parent_ato_line_id = pLineId
2892 and si1.organization_id = pOrgId
2893 and si1.inventory_item_id = bcol.inventory_item_id
2894 and si1.bom_item_type in ( 1, 2 ) /* Models and Classes */
2895 and bcol.line_id <> pLineId
2896 and or1.assembly_item_id = si1.inventory_item_id
2897 and or1.organization_id = si1.organization_id
2898 and or1.alternate_routing_designator is NULL
2899 and NVL(or1.cfm_routing_flag,2) = lCfmRtgflag
2900 and os1.routing_sequence_id = or1.common_routing_sequence_id
2901 /*
2902 and os1.effectivity_date <= greatest(nvl(lEstRelDate, sysdate),sysdate)
2903 and nvl(os1.disable_date,nvl(lEstRelDate, sysdate)+ 1) > NVL(lEstRelDate,sysdate)
2904 */
2905 and ( os1.disable_date is null or
2906 (os1.disable_date is not null and os1.disable_date >= sysdate )) /* New Approach for Effectivity Dates */
2907 and ( os1.operation_type in (2,3)
2908 OR ( os1.option_dependent_flag = 2
2909 and NVL(os1.operation_type,1 ) = 1 ))
2910 union
2911 select
2912 distinct
2913 os1.operation_seq_num,
2914 nvl(os1.operation_type,1),
2915 os1.routing_sequence_id
2916 from
2917 bom_cto_order_lines_upg bcol1, /* components */
2918 bom_cto_order_lines_upg bcol2, /* parents model */
2919 bom_inventory_components ic1,
2920 bom_bill_of_materials b1,
2921 bom_operational_routings or1,
2922 bom_operation_sequences os1
2923 where bcol1.parent_ato_line_id = pLineId
2924 and bcol1.item_type_code in ('CLASS','OPTION')
2925 and bcol2.parent_ato_line_id = pLineId
2926 and bcol2.line_id <> pLineId /*AP*/
2927 and bcol2.item_type_code = 'CLASS' /* option classes */
2928 and bcol2.ordered_quantity <> 0
2929 and bcol2.line_id = bcol1.link_to_line_id
2930 and ic1.bill_sequence_id = (
2931 select common_bill_sequence_id
2932 from bom_bill_of_materials bbm
2933 where organization_id = pOrgId
2934 and alternate_bom_designator is null
2935 and assembly_item_id =(
2936 select distinct assembly_item_id
2937 from bom_bill_of_materials bbm1,
2938 bom_inventory_components bic1
2939 where bbm1.common_bill_sequence_id = bic1.bill_sequence_id
2940 and component_sequence_id = bcol1.component_sequence_id
2941 and bbm1.assembly_item_id = bcol2.inventory_item_id ))
2942 and ic1.component_item_id = bcol1.inventory_item_id
2943 /*
2944 and ic1.effectivity_date<= g_SchShpDate
2945 and NVL(ic1.disable_date, (lEstRelDate + 1)) > lEstRelDate
2946 */
2947 and ( ic1.disable_date is null or
2948 (ic1.disable_date is not null and ic1.disable_date >= sysdate )) /* New Approach for Effectivity Dates */
2949 and b1.common_bill_sequence_id = ic1.bill_sequence_id
2950 and b1.assembly_item_id = bcol2.inventory_item_id -- fix for bug 1272142
2951 and b1.alternate_bom_designator is NULL
2952 and or1.assembly_item_id = b1.assembly_item_id
2953 and or1.organization_id = b1.organization_id
2954 and b1.organization_id = pOrgId --bug 1210477
2955 and or1.alternate_routing_designator is null
2956 and nvl(or1.cfm_routing_flag,2) = lCfmRtgFlag
2957 /*
2958 and os1.effectivity_date <= greatest(nvl(lEstRelDate, sysdate),sysdate)
2959 and nvl(os1.disable_date,nvl(lEstRelDate, sysdate)+ 1) > nvl(lEstRelDate,sysdate)
2960 */
2961 and ( os1.disable_date is null or
2962 (os1.disable_date is not null and os1.disable_date >= sysdate )) /* New Approach for Effectivity Dates */
2963 and os1.routing_sequence_id = or1.common_routing_sequence_id
2964 and ((os1.operation_seq_num = ic1.operation_seq_num)
2965 or (os1.operation_seq_num in
2966 (select bco.operation_seq_num
2967 from bom_component_operations bco
2968 where bco.component_sequence_id = ic1.component_sequence_id)))
2969 and os1.option_dependent_flag = 1
2970 and nvl(os1.operation_type,1) = 1
2971 union
2972 select
2973 distinct
2974 os1.operation_seq_num,
2975 nvl(os1.operation_type,1),
2976 os1.routing_sequence_id
2977 from
2978 bom_operation_sequences os1,
2979 bom_operational_routings or1,
2980 mtl_system_items si2,
2981 bom_inventory_components ic1,
2982 bom_bill_of_materials b1,
2983 mtl_system_items si1,
2984 bom_cto_order_lines_upg bcol /* Model or option class */
2985 where bcol.parent_ato_line_id = pLineId
2986 and bcol.component_sequence_id is not null
2987 and bcol.ordered_quantity <> 0
2988 and si1.organization_id = pOrgId
2989 and si1.inventory_item_id = bcol.inventory_item_id
2990 and si1.bom_item_type in (1,2) /* model or option class */
2991 and b1.organization_id = pOrgId
2992 and b1.assembly_item_id = bcol.inventory_item_id
2993 and b1.alternate_bom_designator is null
2994 and ic1.bill_sequence_id = b1.common_bill_sequence_id
2995 and ic1.optional = 2
2996 -- and ic1.effectivity_date <= greatest(nvl(g_SchShpdate, sysdate),sysdate) New Approach for effectivity dates
2997 and ic1.implementation_date is not null
2998 -- and nvl(ic1.disable_date,nvl(lEstRelDate, sysdate)+ 1) > NVL(lEstRelDate,sysdate)
2999 and ( ic1.disable_date is null or
3000 (ic1.disable_date is not null and ic1.disable_date >= sysdate )) /* New Approach for Effectivity Dates */
3001 and si2.inventory_item_id = ic1.component_item_id
3002 and si2.organization_id = b1.organization_id
3003 and si2.bom_item_type = 4 /* standard */
3004 and or1.assembly_item_id = b1.assembly_item_id
3005 and or1.organization_id = b1.organization_id
3006 and or1.alternate_routing_designator is null
3007 and nvl(or1.cfm_routing_flag,2) = lCfmRtgFlag
3008 /*
3009 and os1.effectivity_date <= greatest(nvl(lEstRelDate, sysdate),sysdate)
3010 and nvl(os1.disable_date,nvl(lEstRelDate, sysdate)+ 1) > nvl(lEstRelDate,sysdate)
3011 */
3012 and ( os1.disable_date is null or
3013 (os1.disable_date is not null and os1.disable_date >= sysdate )) /* New Approach for Effectivity Dates */
3014 and os1.routing_sequence_id = or1.common_routing_sequence_id
3015 and os1.option_dependent_flag = 1
3016 and ((os1.operation_seq_num = ic1.operation_seq_num)
3017 or (os1.operation_seq_num in
3018 (select bco.operation_seq_num
3019 from bom_component_operations bco
3020 where bco.component_sequence_id = ic1.component_sequence_id)))
3021 and nvl(os1.operation_type,1) = 1)
3022 -- and effectivity_date <= greatest(nvl(lEstRelDate, sysdate),sysdate)
3023 and implementation_date is not null
3024 /*
3025 and nvl(disable_date,nvl(lEstRelDate, sysdate)+ 1) > NVL(lEstRelDate,sysdate)
3026 and nvl(disable_date,sysdate+1) > sysdate --Bugfix 2771065
3027 */
3028 and ( disable_date is null or
3029 (disable_date is not null and disable_date >= sysdate )) /* New Approach for Effectivity Dates */
3030 RETURNING routing_sequence_id BULK COLLECT INTO tModOpClassRtg;
3031
3032 WriteToLog('Option Routing : Marked ' || sql%rowcount || ' rows for insertion' ,3);--moved here for 4492875
3033
3034 /*3093686 get only the distinct ones */
3035
3036 if tModOpClassRtg.count > 0 then
3037 k := 1;
3038 tDistinctRtgSeq(k) := tModOpClassRtg(1);
3039 for i in tModOpClassRtg.FIRST..tModOpClassRtg.LAST
3040 loop
3041 lexists := 'N';
3042 for j in tDistinctRtgSeq.FIRST..tDistinctRtgSeq.LAST
3043 loop
3044 if tDistinctRtgSeq(j) = tModOpClassRtg(i) then
3045 lexists := 'Y';
3046 exit;
3047 end if;
3048 end loop;
3049 if lexists = 'N' then
3050 k := k+1;
3051 tDistinctRtgSeq(k) := tModOpClassRtg(i);
3052 end if;
3053 end loop;
3054 end if;
3055
3056 if( tDistinctRtgSeq.count > 0 ) then
3057 for i in tDistinctRtgSeq.first..tDistinctRtgSeq.last
3058 loop
3059 if( tDistinctRtgSeq.exists(i) ) then
3060 IF PG_DEBUG <> 0 THEN
3061 WriteToLog('Distinct Option Class Routing Seq Id: '||tDistinctRtgSeq(i),1);
3062 END IF;
3063 end if ;
3064 end loop ;
3065
3066 else
3067 WriteToLog( 'Distinct Table contains ' || tDistinctRtgSeq.count, 5 ) ;
3068 end if ;
3069
3070
3071 lStmtNum := 81;
3072 lnewCfgRtgId := lCfgRtgId * (-1);
3073 lopseqnum:=0;
3074 loptyp:=0;
3075 lrtgseqid:=0;
3076
3077 open get_op_seq_num(lCfgRtgId);
3078
3079 loop
3080 fetch get_op_seq_num into lopseqnum,loptyp;
3081 exit when get_op_seq_num%notfound;
3082
3083 WriteToLog('Op Seq # : ' || lopseqnum || ' Op Type : ' || loptyp , 4);
3084
3085 select max(routing_sequence_id) into lrtgseqid
3086 from bom_operation_sequences
3087 where operation_seq_num = lopseqnum
3088 and nvl(operation_type,1)= loptyp
3089 --and last_update_login=lCfgRtgId
3090 and config_routing_id=lCfgRtgId
3091 and last_update_date = glast_update_date;
3092
3093 WriteToLog('Max. Routing Seq Id : ' || lrtgseqid, 4);
3094
3095 update bom_operation_sequences
3096 --set last_update_login=lnewCfgRtgId
3097 set config_routing_id=lnewCfgRtgId
3098 where operation_seq_num = lopseqnum
3099 and nvl(operation_type,1)= loptyp
3100 and routing_sequence_id=lrtgseqid
3101 -- and effectivity_date <= greatest(nvl(lEstRelDate, sysdate),sysdate) -- 2650828 New approach for effectivity dates
3102 and implementation_date is not null
3103 /*
3104 and nvl(disable_date,nvl(lEstRelDate, sysdate)+ 1) > NVL(lEstRelDate,sysdate)
3105 and nvl(disable_date,sysdate+1) > sysdate;--Bugfix 2771065
3106 */
3107 and ( disable_date is null or
3108 (disable_date is not null and disable_date >= sysdate )) ; /* New Approach for Effectivity Dates */
3109
3110
3111 WriteToLog('Update login to ' || lnewCfgRtgId ||' where routing seq Id is '||lrtgseqid, 4);
3112
3113 end loop;
3114 close get_op_seq_num;
3115
3116
3117
3118
3119 /*-----------------------------------------------------------------+
3120 Second Insert :
3121 Load distinct operation steps from Class(es) routing
3122 ( steps include Option independednt steps, option dependednt
3123 steps associated with selected components, option dependent
3124 steps associated with mandatory componets)
3125 +-------------------------------------------------------------------*/
3126
3127 lStmtNum := 90;
3128
3129 WriteToLog('Inserting into bom_operation_sequences - 2nd insert ..',5);
3130
3131 if( tDistinctRtgSeq.count > 0 ) then -- 3093686
3132 FORALL i IN tDistinctRtgSeq.FIRST..tDistinctRtgSeq.LAST -- 3093686
3133 insert into bom_operation_sequences
3134 (
3135 operation_sequence_id,
3136 routing_sequence_id,
3137 operation_seq_num,
3138 last_update_date,
3139 last_updated_by,
3140 creation_date,
3141 created_by,
3142 last_update_login,
3143 standard_operation_id,
3144 department_id ,
3145 operation_lead_time_percent,
3146 minimum_transfer_quantity,
3147 count_point_type ,
3148 operation_description,
3149 effectivity_date,
3150 disable_date ,
3151 backflush_flag,
3152 option_dependent_flag,
3153 attribute_category ,
3154 attribute1,
3155 attribute2,
3156 attribute3,
3157 attribute4,
3158 attribute5,
3159 attribute6,
3160 attribute7,
3161 attribute8,
3162 attribute9,
3163 attribute10,
3164 attribute11,
3165 attribute12,
3166 attribute13,
3167 attribute14,
3168 attribute15,
3169 request_id, /* using this column to store model op seq id */
3170 program_application_id,
3171 program_id ,
3172 program_update_date,
3173 reference_flag,
3174 operation_type,
3175 process_op_seq_id,
3176 line_op_seq_id,
3177 yield,
3178 cumulative_yield,
3179 reverse_cumulative_yield,
3180 labor_time_calc,
3181 machine_time_calc,
3182 total_time_calc,
3183 labor_time_user,
3184 machine_time_user,
3185 total_time_user,
3186 net_planning_percent,
3187 implementation_date, -- new column for 11.5.4 BOM patchset
3188 x_coordinate, --bugfix 1765149
3189 y_coordinate --bugfix 1765149
3190 )
3191 select
3192 bom_operation_sequences_s.nextval, /* operation_sequence_id */
3193 lcfgrtgid, /* routing_sequence_id */
3194 os1.operation_seq_num,
3195 sysdate, /* last update date */
3196 gUserId, /* last updated by */
3197 sysdate, /* creation date */
3198 gUserID, /* created by */
3199 gLoginId, /* last update login */
3200 os1.standard_operation_id,
3201 os1.department_id,
3202 os1.operation_lead_time_percent,
3203 os1.minimum_transfer_quantity,
3204 os1.count_point_type,
3205 os1.operation_description,
3206 trunc(sysdate), /* effective date */
3207 null, /* disable date */
3208 os1.backflush_flag,
3209 2, /* option_dependent_flag */
3210 os1.attribute_category,
3211 os1.attribute1,
3212 os1.attribute2,
3213 os1.attribute3,
3214 os1.attribute4,
3215 os1.attribute5,
3216 os1.attribute6,
3217 os1.attribute7,
3218 os1.attribute8,
3219 os1.attribute9,
3220 os1.attribute10,
3221 os1.attribute11,
3222 os1.attribute12,
3223 os1.attribute13,
3224 os1.attribute14,
3225 os1.attribute15,
3226 os1.operation_sequence_id, /* using request_id -> model op seq id */
3227 1, /* program_application_id */
3228 1, /* program_id */
3229 sysdate, /* program_update_date */
3230 reference_flag,
3231 nvl(operation_type,1),
3232 process_op_seq_id,
3233 line_op_seq_id,
3234 yield,
3235 cumulative_yield,
3236 reverse_cumulative_yield,
3237 labor_time_calc,
3238 machine_time_calc,
3239 total_time_calc,
3240 labor_time_user,
3241 machine_time_user,
3242 total_time_user,
3243 net_planning_percent,
3244 trunc(sysdate), -- new column for 11.5.4 BOM patchset
3245 os1.x_coordinate, --bugfix 1765149
3246 os1.y_coordinate --bugfix 1765149
3247 from
3248 bom_operation_sequences os1
3249 --where os1.last_update_login = lnewCfgRtgId /*Bugfix 1906371 - change lCfgRtgId to lnewCfgRtgId */
3250 where os1.config_routing_id = lnewCfgRtgId /*Bugfix 1906371 - change lCfgRtgId to lnewCfgRtgId */
3251 and os1.operation_seq_num not in (
3252 select operation_seq_num
3253 from bom_operation_sequences bos1
3254 where bos1.routing_sequence_id = lCfgRtgId
3255 /* Bugfix 1983384 where bos1.last_update_login = lnewCfgRtgId */
3256 and nvl(bos1.operation_type,1) = nvl(os1.operation_type,1))
3257 and os1.routing_sequence_id = tDistinctRtgSeq(i); -- 3093686
3258 end if; -- 3093686
3259
3260 WriteToLog('Inserted ' || sql%rowcount || 'rows ', 4);
3261
3262 tModOpClassRtg.DELETE;
3263 tDistinctRtgSeq.DELETE;
3264
3265 -- New update of 3180827
3266 lStmtNum := 95;
3267 update bom_operation_sequences
3268 --set last_update_login = - 1
3269 set config_routing_id = - 1
3270 --where last_update_login in (lCfgRtgId, lmodnewcfgrtgid);
3271 where config_routing_id in (lCfgRtgId, lmodnewcfgrtgid);
3272
3273 /*-------------------------------------------------------------------+
3274 Now update the process_op_seq_id and line_seq_id of
3275 all events to new operations sequence Ids (map).
3276 Old operation_sequence_ids are available in request_id
3277 +-------------------------------------------------------------------*/
3278
3279 lStmtNum := 100;
3280 xTableName := 'BOM_OPERATION_SEQUENCES';
3281 update bom_operation_sequences bos1
3282 set process_op_seq_id = (
3283 select operation_sequence_id
3284 from bom_operation_sequences bos2
3285 where bos1.process_op_seq_id = bos2.request_id
3286 and bos2.routing_sequence_id = lCfgRtgId)
3287 where bos1.operation_type = 1
3288 and bos1.routing_sequence_id = lCfgRtgId;
3289
3290 lStmtNum := 110;
3291 update bom_operation_sequences bos1
3292 set line_op_seq_id = (
3293 select operation_sequence_id
3294 from bom_operation_sequences bos2
3295 where bos1.line_op_seq_id = bos2.request_id
3296 and bos2.routing_sequence_id = lCfgRtgId)
3297 where bos1.operation_type = 1
3298 and bos1.routing_sequence_id = lCfgRtgId;
3299
3300 /*-----------------------------------------------------------+
3301 Delete routing from routing header if
3302 there is no operation associated with the routing
3303 +-----------------------------------------------------------*/
3304
3305 lStmtNum := 120;
3306 xTableName := 'BOM_OPERATIONAL_ROUTINGS';
3307
3308 delete from BOM_OPERATIONAL_ROUTINGS b1
3309 where b1.routing_sequence_id not in
3310 (select routing_sequence_id
3311 from bom_operation_sequences )
3312 and b1.routing_sequence_id = lCfgRtgId;
3313
3314 if sql%rowcount > 0 then
3315 WriteToLog( 'No operations were copied, config routing deleted. ', 2);
3316 GOTO ROUTING;
3317 end if;
3318
3319
3320 /*--------------------------------------------------------------+
3321 If there is a operation_seq_num associated with
3322 the config component which not belong to the
3323 config routing, the operation_seq_num will be
3324 set to 1.
3325 +--------------------------------------------------------------*/
3326 lStmtNum := 130;
3327 xTableName := 'BOM_INVENTORY_COMPS_INTERFACE';
3328
3329 update bom_inventory_comps_interface ci
3330 set ci.operation_seq_num = 1
3331 where not exists
3332 (select 'op seq exists in config routing'
3333 from
3334 bom_operation_sequences bos,
3335 bom_operational_routings bor
3336 where bos.operation_seq_num = ci.operation_seq_num
3337 and bos.routing_sequence_id = bor.routing_sequence_id
3338 and bor.assembly_item_id = pConfigId
3339 and bor.organization_id = pOrgId
3340 and bor.alternate_routing_designator is null)
3341 and ci.bill_sequence_id = lConfigBillId;
3342
3343
3344 lstmtNum := 390;
3345
3346 --
3347 -- For each operation in the routing, copy attachments of operations
3348 -- copied from model/option class to operations on the config item
3349 --
3350
3351 for nextop in allops loop
3352
3353 lstmtNum := 400;
3354
3355 FND_ATTACHED_DOCUMENTS2_PKG.copy_attachments(
3356 X_from_entity_name =>'BOM_OPERATION_SEQUENCES',
3357 X_from_pk1_value =>nextop.request_id,
3358 X_from_pk2_value =>'',
3359 X_from_pk3_value =>'',
3360 X_from_pk4_value =>'',
3361 X_from_pk5_value =>'',
3362 X_to_entity_name =>'BOM_OPERATION_SEQUENCES',
3363 X_to_pk1_value =>nextop.operation_sequence_id,
3364 X_to_pk2_value =>'',
3365 X_to_pk3_value =>'',
3366 X_to_pk4_value =>'',
3367 X_to_pk5_value =>'',
3368 X_created_by =>1,
3369 X_last_update_login =>'',
3370 X_program_application_id =>'',
3371 X_program_id =>'',
3372 X_request_id =>''
3373 );
3374 end loop;
3375
3376 lstmtNum := 410;
3377 select nvl(cfm_routing_flag,2)
3378 into lCfmRtgFlag
3379 from bom_operational_routings
3380 where routing_sequence_id = lCfgrtgId;
3381
3382
3383 --
3384 -- if flow manufacturing is installed and the 'Perform Flow Calulations'
3385 -- parameter is set to 2 or 3 (perform calculations based on processes or perform
3386 -- calulations based on Line operations) the routing is 'flow routing' then
3387 -- calculate operation times, yields, net planning percent and total
3388 -- product cycle time for config routing
3389 --
3390
3391
3392 --
3393 -- Check if flow_manufacturing is installed
3394 --
3395
3396 l_install_cfm := FND_INSTALLATION.Get_App_Info(application_short_name => 'FLM',
3397 status => l_status,
3398 industry => l_industry,
3399 oracle_schema => l_schema);
3400
3401 lstmtNum := 410;
3402 if ( l_status = 'I' and pFlowCalc >1 and lCfmRtgflag = 1 ) then
3403
3404 --
3405 -- Calculate Operation times
3406 --
3407
3408 BOM_CALC_OP_TIMES_PK.calculate_operation_times(
3409 arg_org_id => pOrgId,
3410 arg_routing_sequence_id => lcfgRtgId);
3411
3412 --
3413 -- Calculate cumu yield, rev cumu yield and net plannning percent
3414 --
3415
3416 BOM_CALC_CYNP.calc_cynp(
3417 p_routing_sequence_id => lcfgRtgId,
3418 p_operation_type => pFlowCalc, /* operation_type = process */
3419 p_update_events => 1 ); /* update events */
3420
3421 --
3422 -- Calculate total_product_cycle_time
3423 --
3424
3425 BOM_CALC_TPCT.calculate_tpct(
3426 p_routing_sequence_id => lcfgRtgId,
3427 p_operation_type => pFlowCalc); /* Operation_type = Process */
3428 end if;
3429
3430 -- Feature :Serial tracking in wip
3431 -- LOgic : serial tracking is enabled only when serial control mode is 'pre-defined' (ie 2)
3432 -- If model serialization_start_op seq is not populated, we will copy the minimum 'seriallization_start_op'
3433 -- of OC's chosen
3434 --modified by kkonada
3435
3436
3437 if( lCfmRtgFlag = 1) then ---flow doesnot support serial tracking
3438 null;
3439 else
3440 lstmtNum := 411;
3441 Select serial_number_control_code
3442 into l_ser_code
3443 from mtl_System_items
3444 where inventory_item_id = pModelId
3445 and organization_id =pOrgId;
3446
3447 WriteToLog('serial_number_control_code of model is '||l_ser_code , 4);
3448
3449 if ( l_ser_code = 2) then --serialized ,pre-defined
3450
3451 lstmtNum := 412;
3452
3453 WriteToLog('select serial start op from model ' , 4);
3454
3455 BEGIN
3456 --will select serial start op of model, only if effective on the day
3457 --as routing generation takes care of eefectivity, we check if op seq is present in config routing
3458 select serialization_start_op
3459 into l_ser_start_op
3460 from bom_operational_routings
3461 where assembly_item_id = pModelId
3462 and alternate_routing_designator is null
3463 and organization_id = pOrgId
3464 and serialization_start_op in
3465 (Select OPERATION_SEQ_NUM
3466 from bom_operation_sequences
3467 where routing_sequence_id = lCfgRtgId
3468 );
3469 EXCEPTION
3470 WHEN no_data_found THEN
3471 l_ser_start_op := NULL;
3472 END;
3473
3474 WriteToLog('l_ser_start_op ie serialization_start_op from model is '|| l_ser_start_op, 4);
3475
3476 if(l_ser_start_op is null)then
3477
3478 lstmtNum := 413;
3479 WriteToLog('Before updating config routing with serial start op of option class', 4);
3480
3481 begin
3482 update bom_operational_routings
3483 set serialization_start_op =
3484 ( select min( serialization_start_op)
3485 from bom_operational_routings
3486 where organization_id = pOrgId
3487 and alternate_routing_designator is null
3488 and assembly_item_id in
3489 ( select component_item_id
3490 from bom_inventory_comps_interface
3491 where bom_item_type =2
3492 and bill_sequence_id = lConfigBillId
3493 )
3494 and serialization_start_op in
3495 (Select OPERATION_SEQ_NUM
3496 from bom_operation_sequences
3497 where routing_sequence_id = lCfgRtgId
3498 )--serial start op exists as a operation in routing(ie effective oper)
3499 )
3500 where assembly_item_id = pConfigId
3501 and alternate_routing_designator is null
3502 and organization_id = pOrgId;
3503
3504 l_row_count := sql%rowcount;
3505 exception
3506 when no_data_found then
3507 WriteToLog('No option classes chosen while creating coonfiguration ', 4);
3508 end;
3509
3510 WriteToLog('Config rows updated with OC serial start opseq->'||l_row_count, 4);
3511
3512 else --model has serial start op seq
3513
3514 lstmtNum := 414;
3515 update bom_operational_routings
3516 set serialization_start_op = l_ser_start_op
3517 where routing_sequence_id = lCfgRtgId ;
3518
3519 WriteToLog('Updated with serial start op of model, serial start op =>'||l_ser_start_op , 4);
3520
3521 end if;--l_ser_start_op
3522 end if;--l_ser_code
3523 end if; /* flow rtg */
3524
3525 <<ROUTING>>
3526
3527
3528
3529
3530
3531
3532
3533
3534
3535
3536 /*--------------------------------------------------------------+
3537 If more than one row in the BOM_INVENTORY_COMPS_INTERFACE
3538 that contain the same bill_sequence_id, operation_seq_num and
3539 component_item_id, those rows will be combined into a
3540 single row and the accumulated COMPONENT_QUANTITY will be
3541 used in the row.
3542 +---------------------------------------------------------------*/
3543
3544 -- start 3674833
3545 -- Populate seq_tab_arr with component sequence id information
3546 -- We need this info before inserting into bom_reference_designator
3547 -- 4244576 - Also need to get operstion_seq_num into an array.
3548
3549 select b1.model_comp_seq_id, b1.component_item_id, b1.operation_seq_num
3550 BULK COLLECT INTO model_comp_seq_id_arr, component_item_id_arr, operation_seq_num_arr
3551 from bom_inventory_comps_interface b1,bom_inventory_comps_interface b2
3552 where b1.bill_sequence_id = b2.bill_sequence_id
3553 and b1.component_sequence_id <> b2.component_sequence_id
3554 and b1.operation_seq_num = b2.operation_seq_num
3555 and b1.component_item_id = b2.component_item_id
3556 and b1.bill_sequence_id = lConfigBillId
3557 UNION
3558 select b2.model_comp_seq_id, b2.component_item_id, b2.operation_seq_num
3559 from bom_inventory_comps_interface b1,bom_inventory_comps_interface b2
3560 where b1.bill_sequence_id = b2.bill_sequence_id
3561 and b1.component_sequence_id <> b2.component_sequence_id
3562 and b1.operation_seq_num = b2.operation_seq_num
3563 and b1.component_item_id = b2.component_item_id
3564 and b2.bill_sequence_id = lConfigBillId
3565 ORDER by 2;
3566
3567
3568 if model_comp_seq_id_arr.count > 0 then
3569 for x1 in model_comp_seq_id_arr.FIRST..model_comp_seq_id_arr.LAST
3570 loop
3571 WriteToLog( ' Start Looping ',1);
3572 IF PG_DEBUG <> 0 THEN
3573 WriteToLog( ' Model_Comp_seq (' ||x1|| ') = ' ||model_comp_seq_id_arr(x1)
3574 ||' Component_item_id (' ||x1|| ') = ' ||component_item_id_arr(x1)
3575 ||' operation-seq_num (' ||x1|| ') = ' ||operation_seq_num_arr(x1),1); --4244576
3576 END IF;
3577 end loop;
3578 end if;
3579 -- end 3674833
3580
3581
3582 /*
3583
3584 *
3585 *
3586 *
3587 *
3588 *
3589 *
3590 lSaveBomId := 0;
3591 lSaveOpSeqNum := 0;
3592 lSaveItemId := 0;
3593 lSaveCompSeqId := 0;
3594 lTotalQty := 0;
3595 lSaveOptional := 2;
3596
3597 lStmtNum := 300;
3598 open consolidate_components;
3599 loop
3600 fetch consolidate_components into
3601 lBomId,
3602 lOpSeqNum,
3603 lCompSeqId,
3604 lItemId,
3605 lqty,
3606 lOptional;
3607 exit when (consolidate_components%notfound);
3608 if lSaveBomId <> lBomId then
3609 *--------------------------------------------+
3610 different bill and not begining of the loop
3611 +--------------------------------------------*
3612 if lTotalQty <> 0 then
3613 update bom_inventory_comps_interface
3614 set component_quantity = Round( lTotalQty, 7) * Decimal-Qty Support for Option Items *
3615 where component_sequence_id = lSaveCompSeqId;
3616 end if;
3617
3618 if lSaveOptional = 1 then
3619 update bom_inventory_comps_interface
3620 set optional_on_model = lSaveOptional
3621 where component_sequence_id = lSaveCompSeqId;
3622 end if;
3623
3624 lTotalQty := lqty;
3625 lSaveBomId := lBomId;
3626 lSaveOpSeqNum := lOpSeqNum;
3627 lSaveItemId := lItemId;
3628 lSaveCompSeqId := lCompSeqId;
3629 if lOptional = 1 then
3630 lSaveOptional := 1;
3631 end if;
3632 else
3633 *-----------------------------------------------+
3634 same bill but different item
3635 +------------------------------------------------*
3636 if lSaveItemId <> lItemId then
3637 update bom_inventory_comps_interface
3638 set component_quantity = Round( lTotalQty, 7 ) * Decimal-Qty Support for Option Items *
3639 where component_sequence_id = lSaveCompSeqId;
3640
3641 if lSaveOptional = 1 then
3642 update bom_inventory_comps_interface
3643 set optional_on_model = lSaveOptional
3644 where component_sequence_id = lSaveCompSeqId;
3645 end if;
3646
3647 lTotalQty := lqty;
3648 lSaveOptional := lOptional;
3649 *--------------------------------------------+
3650 same bill and item but different seq_num
3651 +---------------------------------------------*
3652 else
3653 if lSaveOpSeqNum <> lOpSeqNum then
3654 update bom_inventory_comps_interface
3655 set component_quantity = Round( lTotalQty , 7 ) * Decimal-Qty Support for Option Items *
3656 where component_sequence_id = lSaveCompSeqId;
3657 lTotalQty := lqty;
3658
3659 if lSaveOptional = 1 then
3660 update bom_inventory_comps_interface
3661 set optional_on_model = lSaveOptional
3662 where component_sequence_id = lSaveCompSeqId;
3663 end if;
3664 lSaveOptional := lOptional;
3665 *----------------------------------+
3666 duplicated one
3667 +----------------------------------*
3668 else
3669 delete bom_inventory_comps_interface
3670 where component_sequence_id = lSaveCompSeqId;
3671 lTotalQty := lTotalQty + lqty;
3672 if lOptional = 1 then
3673 lSaveOptional := 1;
3674 end if;
3675 end if;
3676 end if;
3677 lSaveBomId := lBomId;
3678 lSaveOpSeqNum := lOpSeqNum;
3679 lSaveItemId := lItemId;
3680 lSaveCompSeqId := lCompSeqId;
3681 end if;
3682 end loop;
3683 *------------------------------------------------+
3684 handle the last row here
3685 +-------------------------------------------------*
3686
3687 WriteToLog('Consolidate_components:lTotalQty: ' || to_char(lTotalQty), 5);
3688 WriteToLog('Consolidate_components:ComponentSeqID: ' || to_char(lSaveCompSeqId), 5);
3689
3690 lStmtNum := 140;
3691 update bom_inventory_comps_interface
3692 set component_quantity = Round( lTotalQty , 7 ) * Decimal-Qty Support for Option Items *
3693 where component_sequence_id = lSaveCompSeqId;
3694
3695 if lSaveOptional = 1 then
3696 update bom_inventory_comps_interface
3697 set optional_on_model = lSaveOptional
3698 where component_sequence_id = lSaveCompSeqId;
3699 end if;
3700 close consolidate_components;
3701
3702
3703
3704
3705
3706
3707 *
3708 *
3709 *
3710 *
3711 *
3712 *
3713 *
3714 *
3715 */
3716
3717
3718 /* begin 02-14-2005 Sushant */
3719
3720 -- Start new code 3222932
3721
3722 -- Execute following code for each clubbed components
3723 for club_comp_rec in club_comp
3724 loop
3725
3726 -- Get all eff and disable dates in asc order
3727 -- 4244576
3728 WriteToLog( ' Looping for item id : ' ||club_comp_rec.item_id ||' operation_seq : '||club_comp_rec.operation_seq_num,1);
3729
3730 select distinct effectivity_date
3731 BULK COLLECT INTO asc_date_arr
3732 from bom_inventory_comps_interface
3733 where bill_sequence_id = lConfigBillId
3734 and component_item_id = club_comp_rec.item_id
3735 and operation_seq_num = club_comp_rec.operation_seq_num --4244576
3736 UNION
3737 select distinct disable_date
3738 from bom_inventory_comps_interface
3739 where bill_sequence_id = lConfigBillId
3740 and component_item_id = club_comp_rec.item_id
3741 and operation_seq_num = club_comp_rec.operation_seq_num --4244576
3742 order by 1;
3743
3744 -- Printing dates
3745
3746 if asc_date_arr.count > 0 then
3747 for x1 in asc_date_arr.FIRST..asc_date_arr.LAST
3748 loop
3749 IF PG_DEBUG <> 0 THEN
3750 WriteToLog('Date ('||x1||') = '||to_char(asc_date_arr(x1),'DD-MON-YY HH24:MI:SS'),1);
3751 END IF;
3752 end loop;
3753 end if;
3754
3755 -- Creating clubbing windows
3756
3757
3758 if asc_date_arr.count > 0 then
3759 for x2 in 1..(asc_date_arr.count-1)
3760 loop
3761 club_tab_arr(x2).eff_dt := asc_date_arr(x2);
3762 club_tab_arr(x2).dis_dt := asc_date_arr(x2+1);
3763 end loop;
3764 end if;
3765
3766 -- Printing dates of clubbing window
3767
3768 if club_tab_arr.count > 0 then
3769 for x3 in club_tab_arr.FIRST..club_tab_arr.LAST
3770 loop
3771 IF PG_DEBUG <> 0 THEN
3772 WriteToLog('ED ('||x3||') = ' ||to_char(club_tab_arr(x3).eff_dt,'DD-MON-YY HH24:MI:SS')||
3773 ' ---- DD ('||x3||') = '|| to_char(club_tab_arr(x3).dis_dt,'DD-MON-YY HH24:MI:SS'),1);
3774 END IF;
3775 end loop;
3776 end if;
3777
3778 -- Modifying eff dates of clubbing windows
3779
3780 if club_tab_arr.count > 0 then
3781 for x21 in 2..(club_tab_arr.count)
3782 loop
3783 if ( club_tab_arr(x21 - 1).dis_dt = club_tab_arr(x21).eff_dt ) then
3784 club_tab_arr(x21).eff_dt := club_tab_arr(x21).eff_dt + 1/86400;
3785 end if;
3786 end loop;
3787 end if;
3788
3789 -- Printing dates of clubbing window
3790
3791 if club_tab_arr.count > 0 then
3792 for x22 in club_tab_arr.FIRST..club_tab_arr.LAST
3793 loop
3794 IF PG_DEBUG <> 0 THEN
3795 WriteToLog('ED ('||x22||') = ' ||to_char(club_tab_arr(x22).eff_dt,'DD-MON-YY HH24:MI:SS')||
3796 ' ---- DD ('||x22||') = '|| to_char(club_tab_arr(x22).dis_dt,'DD-MON-YY HH24:MI:SS'),1);
3797 END IF;
3798 end loop;
3799 end if;
3800
3801
3802 -- for debug
3803 for d1 in c1_debug (club_comp_rec.item_id, club_comp_rec.operation_seq_num) loop --4244576
3804
3805 dbg_eff_date := d1.eff_date;
3806 dbg_dis_date := d1.dis_date;
3807 dbg_qty := d1.cmp_qty;
3808
3809 IF PG_DEBUG <> 0 THEN
3810 WriteToLog( '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);
3811 END IF;
3812
3813 end loop;
3814
3815 -- Clubbing quantities
3816
3817 if club_tab_arr.count > 0 then
3818 for x4 in club_tab_arr.FIRST.. club_tab_arr.LAST
3819 loop
3820
3821
3822
3823 IF PG_DEBUG <> 0 THEN
3824 WriteToLog ('checking for club comp error ', 1 ) ;
3825 END IF;
3826
3827
3828
3829 /* begin LBM project */
3830 /* Check whether multiple occurences of the same component with the same inventory_item_id
3831 and operation_sequence have conflicting basis_type.
3832 */
3833 select b1.model_comp_seq_id, b1.component_item_id
3834 BULK COLLECT INTO
3835 basis_model_comp_seq_id_arr, basis_component_item_id_arr
3836 from
3837 bom_inventory_comps_interface b1,bom_inventory_comps_interface b2
3838 where b1.bill_sequence_id = b2.bill_sequence_id
3839 and b1.component_sequence_id <> b2.component_sequence_id
3840 and b1.operation_seq_num = b2.operation_seq_num
3841 and b1.component_item_id = b2.component_item_id
3842 and b1.bill_sequence_id = lConfigBillId
3843 and b1.basis_type <> b2.basis_type
3844 and b1.effectivity_date <= club_tab_arr(x4).eff_dt
3845 and nvl(b1.disable_date,g_SchShpDate) >= club_tab_arr(x4).dis_dt
3846 and b1.bill_sequence_id = lConfigBillId
3847 and b1.component_item_id = club_comp_rec.item_id
3848 and b1.operation_seq_num = club_comp_rec.operation_seq_num
3849 and b2.effectivity_date <= club_tab_arr(x4).eff_dt
3850 and nvl(b2.disable_date,g_SchShpDate) >= club_tab_arr(x4).dis_dt;
3851
3852
3853 if( basis_model_comp_seq_id_arr.count > 0 ) then
3854
3855
3856 for i in 1..basis_model_comp_seq_id_arr.count
3857 loop
3858 if ( i = 1 ) then
3859
3860 v_diff_basis_string := 'component ' || basis_component_item_id_arr(i) ;
3861
3862 else
3863
3864 v_sub_diff_basis_string := 'component ' || basis_component_item_id_arr(i) || l_new_line ;
3865
3866 v_diff_basis_string := v_diff_basis_string || v_sub_diff_basis_string ;
3867
3868 end if ;
3869 end loop;
3870
3871
3872 IF PG_DEBUG <> 0 THEN
3873 WriteToLog( 'Going to Raise CTO_CLUB_COMP_ERROR');
3874 WriteToLog( 'will not populated message CTO_CLUB_COMP_ERROR');
3875 END IF;
3876 select segment1 into
3877 l_model_name
3878 from mtl_system_items
3879 where inventory_item_id = pmodelid
3880 and organization_id = porgid;
3881
3882
3883 select segment1 into
3884 l_comp_name
3885 from mtl_system_items
3886 where inventory_item_id = club_comp_rec.item_id
3887 and organization_id = porgid;
3888
3889 select organization_name
3890 into l_org_name
3891 from inv_organization_name_v
3892 where organization_id = porgid;
3893
3894 l_token(1).token_name := 'MODEL';
3895 l_token(1).token_value := l_model_name;
3896 l_token(2).token_name := 'ORGANIZATION';
3897 l_token(2).token_value := l_org_name;
3898 l_token(3).token_name := 'COMPONENT';
3899 l_token(3).token_value := l_comp_name;
3900 cto_msg_pub.cto_message('BOM','CTO_CLUB_COMP_ERROR',l_token);
3901
3902
3903
3904 raise fnd_api.g_exc_error;
3905
3906
3907 end if;
3908
3909 /* end LBM project */
3910
3911
3912
3913 IF PG_DEBUG <> 0 THEN
3914 WriteToLog ('Going for Group Function ', 1 ) ;
3915 END IF;
3916
3917
3918
3919
3920
3921
3922 select max(rowid), sum(decode(basis_type, 1, component_quantity, 0))
3923 + max(decode(basis_type, 2, component_quantity, 0)) /* LBM Project */
3924 into club_tab_arr(x4).row_id,club_tab_arr(x4).qty
3925 from bom_inventory_comps_interface
3926 where effectivity_date <= club_tab_arr(x4).eff_dt
3927 and nvl(disable_date,g_SchShpDate) >= club_tab_arr(x4).dis_dt
3928 and bill_sequence_id = lConfigBillId
3929 and component_item_id = club_comp_rec.item_id
3930 and operation_seq_num = club_comp_rec.operation_seq_num; --4244576
3931
3932 end loop;
3933 end if;
3934
3935 -- Printing Clubbed quantity with window
3936
3937 if club_tab_arr.count > 0 then
3938 for x5 in club_tab_arr.FIRST..club_tab_arr.LAST
3939 loop
3940 IF PG_DEBUG <> 0 THEN
3941 WriteToLog('ED (' ||x5|| ') = ' ||to_char(club_tab_arr(x5).eff_dt,'DD-MON-YY HH24:MI:SS')||
3942 ' -- DD (' ||x5|| ') = ' ||to_char(club_tab_arr(x5).dis_dt,'DD-MON-YY HH24:MI:SS')|| ' -- Qty (' ||x5|| ') = ' ||club_tab_arr(x5).qty,1);
3943 END IF;
3944 end loop;
3945 end if;
3946
3947 -- Now insert into bom_inventory_comps_interface
3948
3949 if club_tab_arr.count > 0 then
3950
3951 for x6 in club_tab_arr.FIRST.. club_tab_arr.LAST
3952 loop
3953 If nvl(club_tab_arr(x6).qty,0) <> 0 Then
3954 insert into bom_inventory_comps_interface
3955 (
3956 component_item_id,
3957 bill_sequence_id,
3958 effectivity_date,
3959 disable_date,
3960 component_quantity,
3961 creation_date,
3962 created_by,
3963 last_update_date,
3964 last_updated_by,
3965 operation_seq_num,
3966 last_update_login,
3967 item_num,
3968 component_yield_factor,
3969 component_remarks,
3970 change_notice,
3971 implementation_date,
3972 attribute_category,
3973 attribute1,
3974 attribute2,
3975 attribute3,
3976 attribute4,
3977 attribute5,
3978 attribute6,
3979 attribute7,
3980 attribute8,
3981 attribute9,
3982 attribute10,
3983 attribute11,
3984 attribute12,
3985 attribute13,
3986 attribute14,
3987 attribute15,
3988 planning_factor,
3989 quantity_related,
3990 so_basis,
3991 optional,
3992 mutually_exclusive_options,
3993 include_in_cost_rollup,
3994 check_atp,
3995 shipping_allowed,
3996 required_to_ship,
3997 required_for_revenue,
3998 include_on_ship_docs,
3999 include_on_bill_docs,
4000 low_quantity,
4001 high_quantity,
4002 acd_type,
4003 old_component_sequence_id,
4004 component_sequence_id,
4005 request_id,
4006 program_application_id,
4007 program_id,
4008 program_update_date,
4009 wip_supply_type,
4010 pick_components,
4011 model_comp_seq_id,
4012 supply_subinventory,
4013 supply_locator_id,
4014 bom_item_type,
4015 optional_on_model,
4016 parent_bill_seq_id,
4017 plan_level,
4018 revised_item_sequence_id
4019 , basis_type, /* LBM change */
4020 batch_id
4021 )
4022 select
4023 club_comp_rec.item_id,
4024 lConfigBillId,
4025 club_tab_arr(x6).eff_dt,
4026 club_tab_arr(x6).dis_dt,
4027 round(club_tab_arr(x6).qty,7), -- to maintain decimal qty support of option items
4028 SYSDATE,
4029 lConfigBillId, -- CREATED_BY is set to lConfigBillId to identify rows from clubbing
4030 SYSDATE,
4031 1,
4032 operation_seq_num,
4033 last_update_login,
4034 item_num,
4035 component_yield_factor,
4036 component_remarks,
4037 change_notice,
4038 implementation_date,
4039 attribute_category,
4040 attribute1,
4041 attribute2,
4042 attribute3,
4043 attribute4,
4044 attribute5,
4045 attribute6,
4046 attribute7,
4047 attribute8,
4048 attribute9,
4049 attribute10,
4050 attribute11,
4051 attribute12,
4052 attribute13,
4053 attribute14,
4054 attribute15,
4055 planning_factor,
4056 quantity_related,
4057 so_basis,optional,
4058 mutually_exclusive_options,
4059 include_in_cost_rollup,
4060 check_atp,
4061 shipping_allowed,
4062 required_to_ship,
4063 required_for_revenue,
4064 include_on_ship_docs,
4065 include_on_bill_docs,
4066 low_quantity,
4067 high_quantity,
4068 acd_type,
4069 old_component_sequence_id,
4070 bom_inventory_components_s.nextval,
4071 request_id,
4072 program_application_id,
4073 program_id,
4074 program_update_date,
4075 wip_supply_type,
4076 pick_components,
4077 model_comp_seq_id,
4078 supply_subinventory,
4079 supply_locator_id,
4080 bom_item_type,
4081 optional_on_model,
4082 parent_bill_seq_id,
4083 plan_level,
4084 revised_item_sequence_id
4085 , nvl(basis_type,1), /* LBM project */
4086 cto_msutil_pub.bom_batch_id
4087 from bom_inventory_comps_interface
4088 where component_item_id = club_comp_rec.item_id
4089 and operation_seq_num = club_comp_rec.operation_seq_num --4244576
4090 and bill_sequence_id = lConfigBillId
4091 and rowid = club_tab_arr(x6).row_id;
4092 End if;
4093 end loop;
4094 end if;
4095
4096 -- Delete original option item rows from bici
4097 delete from bom_inventory_comps_interface
4098 where component_item_id = club_comp_rec.item_id
4099 and operation_seq_num = club_comp_rec.operation_seq_num --4244576
4100 and bill_sequence_id = lConfigBillId
4101 and created_by <> lConfigBillId;
4102
4103 -- Delete rows from bom_inventory_comps_interface where qty = 0
4104 delete from bom_inventory_comps_interface
4105 where component_item_id = club_comp_rec.item_id
4106 and operation_seq_num = club_comp_rec.operation_seq_num --4244576
4107 and bill_sequence_id = lConfigBillId
4108 and created_by = lConfigBillId
4109 and component_quantity = 0;
4110
4111 -- Delete club_tab_arr and asc_date_arr to process next item in club_comp_cur
4112 if club_tab_arr.count > 0 then
4113 for x7 in club_tab_arr.FIRST..club_tab_arr.LAST
4114 loop
4115 club_tab_arr.DELETE(x7);
4116 end loop;
4117 end if;
4118
4119 if asc_date_arr.count > 0 then
4120 for x8 in asc_date_arr.FIRST..asc_date_arr.LAST
4121 loop
4122 asc_date_arr.DELETE(x8);
4123 end loop;
4124 end if;
4125
4126 end loop; -- End loop of club_comp_cur
4127
4128 -- end new code 3222932
4129
4130
4131
4132
4133
4134 /* end 02-14-2005 Sushant */
4135
4136
4137
4138
4139
4140 /*----------------------------------------------+
4141 Update item sequence id.
4142 To address configuration BOM restructure enhancements,
4143 item sequence is being updated such that there are no
4144 duplicate sequences, and in the logical order of components
4145 selection from the parent model BOM.
4146 The Item Sequence Increment is based on the profile
4147 "BOM:Item Sequence Increment".
4148 +----------------------------------------------*/
4149
4150 --
4151 -- Get item sequence increment
4152 --
4153 p_seq_increment := fnd_profile.value('BOM:ITEM_SEQUENCE_INCREMENT');
4154 WriteToLog('Item Seq Increment::'||to_char(p_seq_increment), 5);
4155
4156 --
4157 -- update item_num of top model
4158 --
4159 p_item_num := p_item_num + p_seq_increment;
4160
4161 WriteToLog('p_item_num::'||to_char(p_item_num), 5);
4162
4163 update bom_inventory_comps_interface
4164 set item_num = p_item_num
4165 where bill_sequence_id = lConfigbillid and parent_bill_seq_id = 0; /* 04-04-2005 bugfix 3374548 */
4166
4167
4168
4169 WriteToLog('Updated model row::'||sql%rowcount, 5);
4170
4171 p_item_num := p_item_num + p_seq_increment;
4172
4173 WriteToLog('Going to get top model for billseq ::'|| to_char(lConfigBillId) , 5); /* Introduced by sushant */
4174 --
4175 -- get bill_sequence_id of top model
4176 --
4177 select common_bill_sequence_id
4178 into p_bill_seq_id
4179 from bom_bill_of_materials
4180 where assembly_item_id =
4181 (select component_item_id
4182 from bom_inventory_comps_interface
4183 where bill_sequence_id = lConfigBillId and parent_bill_seq_id = 0) /* Introduced by sushant */
4184 and organization_id = pOrgId
4185 and alternate_bom_designator is null;
4186
4187 --
4188 -- call update_item_num procedure with top model
4189 -- this will update item_num for the rest of the items
4190 --
4191 WriteToLog('Calling update_item_num will p_bill_seq_id::'||to_char(p_bill_seq_id)||' and p_item_num::'||to_char(p_item_num), 5);
4192
4193 update_item_num(
4194 p_bill_seq_id,
4195 p_item_num,
4196 pOrgId,
4197 p_seq_increment);
4198
4199
4200 /*-------------------------------------------+
4201 Load BOM_bill_of_materials
4202 +-------------------------------------------*/
4203 WriteToLog('Before first insert into bill_of_materials.' ,3);
4204 WriteToLog('Org: ' ||to_char(pOrgId), 4);
4205 WriteToLog('Model: ' || to_char(pModelId), 4);
4206 WriteToLog('Config: ' || to_char(pConfigId), 4);
4207
4208
4209
4210 /* begin changes for bug 4271269 */
4211
4212 if g_structure_type_id is null then
4213
4214 begin
4215
4216 select structure_type_id into g_structure_type_id from bom_alternate_designators
4217 where alternate_designator_code is null ;
4218
4219 exception
4220 when others then
4221 IF PG_DEBUG <> 0 THEN
4222 WriteToLog('create_bom_data_ml: ' || 'others error while retrieving structure_type_id .' ,2);
4223 WriteToLog('create_bom_data_ml: ' || 'defaulting structure_type_id to 1 .' ,2);
4224 g_structure_type_id := 1;
4225
4226 END IF;
4227
4228 end ;
4229
4230
4231 IF PG_DEBUG <> 0 THEN
4232 WriteToLog('create_bom_data_ml: ' || 'structure_type_id is ' || g_structure_type_id ,2);
4233 END IF;
4234
4235
4236 end if ;
4237
4238 /* end changes for bug 4271269 */
4239
4240
4241 -- As per BOM team, they have added two new fileds
4242 -- PK1_value and PK2_VAlue in 11.5.10 and R12
4243 -- These fields are added for some PLM projects
4244 -- PK1_VALUE should be assembly_item_id
4245 -- PK2_VALUE should be organization id
4246 -- So far these two columns are populated thru database trigger
4247 -- bom is planning on droping this trigger in R12, hence we need
4248
4249 lStmtNum := 145;
4250 xTableName := 'BOM_BILL_OF_MATERIALS';
4251 insert into BOM_BILL_OF_MATERIALS(
4252 assembly_item_id,
4253 organization_id,
4254 alternate_bom_designator,
4255 last_update_date,
4256 last_updated_by,
4257 creation_date,
4258 created_by,
4259 last_update_login,
4260 specific_assembly_comment,
4261 pending_from_ecn,
4262 attribute_category,
4263 attribute1,
4264 attribute2,
4265 attribute3,
4266 attribute4,
4267 attribute5,
4268 attribute6,
4269 attribute7,
4270 attribute8,
4271 attribute9,
4272 attribute10,
4273 attribute11,
4274 attribute12,
4275 attribute13,
4276 attribute14,
4277 attribute15,
4278 assembly_type,
4279 bill_sequence_id,
4280 common_bill_sequence_id,
4281 source_bill_sequence_id, /* COMMON BOM Project 12.0 */
4282 request_id,
4283 program_application_id,
4284 program_id,
4285 program_update_date,
4286 implementation_date, -- bug fix 3759118,FP 3810243
4287 structure_type_id, -- bugfix 4271269
4288 effectivity_control, -- bugfix 4271269
4289 pk1_value,
4290 pk2_value
4291 )
4292 select
4293 pConfigId, -- assembly_item_id
4294 pOrgId, -- organization_id
4295 NULL, -- alternate_bom_designator
4296 sysdate, -- last_update_date
4297 1, -- last_update_by
4298 sysdate, -- creation date
4299 1, -- created by
4300 1, -- last_update_login
4301 b.specific_assembly_comment, -- specific assembly comment
4302 NULL, -- pending from ecn
4303 b.attribute_category, -- attribute category
4304 b.attribute1, -- attribute1
4305 b.attribute2, -- attribute2
4306 b.attribute3, -- attribute3
4307 b.attribute4, -- attribute4
4308 b.attribute5, -- attribute5
4309 b.attribute6, -- attribute6
4310 b.attribute7, -- attribute7
4311 b.attribute8, -- attribute8
4312 b.attribute9, -- attribute9
4313 b.attribute10, -- attribute10
4314 b.attribute11, -- attribute11
4315 b.attribute12, -- attribute12
4316 b.attribute13, -- attribute13
4317 b.attribute14, -- attribute14
4318 b.attribute15, -- attribute15
4319 b.assembly_type, -- assembly_type
4320 lConfigBillId,
4321 lConfigBillId,
4322 lConfigBillId, -- source_bill_sequence_id COMMON BOM Project 12.0
4323 NULL, -- request id
4324 NULL, -- program_application_id
4325 NULL, -- program id
4326 NULL, -- program date
4327 SYSDATE, -- implementation date bug fix 3759118,FP 3810243
4328 g_structure_type_id, -- bugfix 4271269 structure_type_id
4329 1, -- bugfix 4271269 effectivity_control
4330 pconfigid,
4331 porgid
4332 from bom_bill_of_materials b
4333 where b.assembly_item_id = pModelId
4334 and b.organization_id = pOrgId
4335 and b.alternate_bom_designator is NULL;
4336
4337 WriteToLog('Inserted rows into bom_bill_of_materials::'||sql%rowcount, 2 );
4338
4339 /*-----------------------------------------------+
4340 Load Bom_inventory_components
4341 +----------------------------------------------*/
4342 WriteToLog('Before second insert into bom_inventory_components. ', 3);
4343 lStmtNum := 310;
4344 xTableName := 'BOM_INVENTORY_COMPONENTS';
4345 insert into BOM_INVENTORY_COMPONENTS
4346 (
4347 operation_seq_num,
4348 component_item_id,
4349 last_update_date,
4350 last_updated_by,
4351 creation_date,
4352 created_by,
4353 last_update_login,
4354 item_num,
4355 component_quantity,
4356 component_yield_factor,
4357 component_remarks,
4358 effectivity_date,
4359 change_notice,
4360 implementation_date,
4361 disable_date,
4362 attribute_category,
4363 attribute1,
4364 attribute2,
4365 attribute3,
4366 attribute4,
4367 attribute5,
4368 attribute6,
4369 attribute7,
4370 attribute8,
4371 attribute9,
4372 attribute10,
4373 attribute11,
4374 attribute12,
4375 attribute13,
4376 attribute14,
4377 attribute15,
4378 planning_factor,
4379 quantity_related,
4380 so_basis,
4381 optional,
4382 mutually_exclusive_options,
4383 include_in_cost_rollup,
4384 check_atp,
4385 shipping_allowed,
4386 required_to_ship,
4387 required_for_revenue,
4388 include_on_ship_docs,
4389 include_on_bill_docs,
4390 low_quantity,
4391 high_quantity,
4392 acd_type,
4393 old_component_sequence_id,
4394 component_sequence_id,
4395 common_component_sequence_id, /* COMMON BOM Project 12.0 */
4396 bill_sequence_id,
4397 request_id,
4398 program_application_id,
4399 program_id,
4400 program_update_date,
4401 wip_supply_type,
4402 operation_lead_time_percent,
4403 revised_item_sequence_id,
4404 supply_locator_id,
4405 supply_subinventory,
4406 pick_components,
4407 bom_item_type,
4408 optional_on_model, --isp bom
4409 parent_bill_seq_id, --isp bom
4410 plan_level, --isp bom
4411 model_comp_seq_id --isp bom
4412 , basis_type /* LBM change */
4413 )
4414 select
4415 b.operation_seq_num,
4416 b.component_item_id,
4417 b.last_update_date,
4418 1, /* last_updated_by */
4419 b.creation_date,
4420 1, /* created_by */
4421 b.last_update_login,
4422 b.item_num,
4423 b.component_quantity,
4424 b.component_yield_factor,
4425 b.component_remarks,
4426 b.effectivity_date,
4427 b.change_notice,
4428 b.implementation_date,
4429 -- b.disable_date,
4430 -- 3222932 Chg g_futuredate back to NULL
4431 decode(b.disable_date,g_futuredate,to_date(NULL), b.disable_date), /* 02-14-2005 Sushant */
4432 b.attribute_category,
4433 b.attribute1,
4434 b.attribute2,
4435 b.attribute3,
4436 b.attribute4,
4437 b.attribute5,
4438 b.attribute6,
4439 b.attribute7,
4440 b.attribute8,
4441 b.attribute9,
4442 b.attribute10,
4443 b.attribute11,
4444 b.attribute12,
4445 b.attribute13,
4446 b.attribute14,
4447 b.attribute15,
4448 b.planning_factor,
4449 b.quantity_related,
4450 b.so_basis,
4451 b.optional,
4452 b.mutually_exclusive_options,
4453 b.include_in_cost_rollup,
4454 decode( msi.bom_item_type , 1 , decode( msi.atp_flag , 'Y' , 1 , b.check_atp ) , b.check_atp ) , /* ATP changes for Model component */
4455 b.shipping_allowed,
4456 b.required_to_ship,
4457 b.required_for_revenue,
4458 b.include_on_ship_docs,
4459 b.include_on_bill_docs,
4460 b.low_quantity,
4461 b.high_quantity,
4462 b.acd_type,
4463 b.old_component_sequence_id,
4464 b.component_sequence_id,
4465 b.component_sequence_id, -- common_component_sequence_id COMMON BOM Project 12.0
4466 b.bill_sequence_id,
4467 NULL, /* request_id */
4468 NULL, /* program_application_id */
4469 NULL, /* program_id */
4470 sysdate, /* program_update_date */
4471 b.wip_supply_type,
4472 b.operation_lead_time_percent,
4473 NULL, -- 2524562
4474 b.supply_locator_id,
4475 b.supply_subinventory,
4476 b.pick_components,
4477 b.bom_item_type,
4478 b.optional_on_model, --isp bom
4479 b.parent_bill_seq_id, --isp bom
4480 b.plan_level, --isp bom
4481 b.model_comp_seq_id --isp bom
4482 , decode(b.basis_type,1,null,b.basis_type) /* LBM change */
4483 from bom_inventory_comps_interface b,
4484 mtl_system_items msi
4485 where b.bill_sequence_id = lConfigBillId
4486 and b.component_item_id = msi.inventory_item_id
4487 and msi.organization_id = pOrgId;
4488
4489 WriteToLog('Inserted rows into bom_inv_comps::'||sql%rowcount, 2 );
4490
4491
4492
4493
4494
4495
4496
4497
4498
4499
4500
4501
4502
4503
4504
4505
4506
4507 /*-----------------------------------------------+
4508 Populate Substitutes for Mandatory components
4509 +----------------------------------------------*/
4510 IF PG_DEBUG <> 0 THEN
4511 WriteToLog('create_bom_data_ml: ' || 'Before second insert into bom_inventory_components. ', 2);
4512 END IF;
4513 lStmtNum := 315;
4514 xTableName := 'BOM_SUBSTITUTE_COMPONENTS';
4515
4516
4517
4518
4519 insert into bom_substitute_components (
4520 substitute_component_id
4521 ,substitute_item_quantity
4522 ,component_sequence_id
4523 ,acd_type
4524 ,change_notice
4525 ,attribute_category
4526 ,attribute1
4527 ,attribute2
4528 ,attribute3
4529 ,attribute4
4530 ,attribute5
4531 ,attribute6
4532 ,attribute7
4533 ,attribute8
4534 ,attribute9
4535 ,attribute10
4536 ,attribute11
4537 ,attribute12
4538 ,attribute13
4539 ,attribute14
4540 ,attribute15
4541 ,original_system_reference
4542 ,enforce_int_requirements
4543 ,request_id
4544 ,program_application_id
4545 ,program_id
4546 ,program_update_date
4547 ,last_update_date
4548 ,last_updated_by
4549 ,creation_date
4550 ,created_by
4551 ,last_update_login
4552 )
4553 select
4554 s.substitute_component_id -- substitute_component_id
4555 ,s.substitute_item_quantity
4556 ,b.component_sequence_id
4557 ,s.acd_type
4558 ,s.change_notice
4559 ,s.attribute_category
4560 ,s.attribute1
4561 ,s.attribute2
4562 ,s.attribute3
4563 ,s.attribute4
4564 ,s.attribute5
4565 ,s.attribute6
4566 ,s.attribute7
4567 ,s.attribute8
4568 ,s.attribute9
4569 ,s.attribute10
4570 ,s.attribute11
4571 ,s.attribute12
4572 ,s.attribute13
4573 ,s.attribute14
4574 ,s.attribute15
4575 ,s.original_system_reference
4576 ,s.enforce_int_requirements
4577 ,FND_GLOBAL.CONC_REQUEST_ID /* REQUEST_ID */
4578 ,FND_GLOBAL.PROG_APPL_ID /* PROGRAM_APPLICATION_ID */
4579 ,FND_GLOBAL.CONC_PROGRAM_ID /* PROGRAM_ID */
4580 ,sysdate /* PROGRAM_UPDATE_DATE */
4581 ,sysdate /* LAST_UPDATE_DATE */
4582 ,gUserId /* LAST_UPDATED_BY */
4583 ,sysdate /* CREATION_DATE */
4584 ,gUserId /* CREATED_BY */
4585 ,gLoginId /* LAST_UPDATE_LOGIN */
4586 /*
4587 ,request_id
4588 ,program_application_id
4589 ,program_id
4590 ,program_update_date
4591 ,last_update_date
4592 ,last_updated_by
4593 ,creation_date
4594 ,created_by
4595 ,last_update_login
4596 */
4597 from bom_inventory_comps_interface b , bom_inventory_components bic, bom_substitute_components s
4598 where b.bill_sequence_id = lConfigBillId
4599 and ABS(b.model_comp_seq_id) = bic.component_sequence_id
4600 and bic.optional = 2 /* only mandatory components */
4601 and bic.component_sequence_id = s.component_sequence_id ;
4602
4603
4604
4605
4606
4607
4608 IF PG_DEBUG <> 0 THEN
4609 WriteToLog('create_bom_data_ml: ' || xTableName || '-'|| lStmtNum || ': ' || sql%rowcount, 1);
4610 END IF;
4611
4612
4613
4614
4615
4616
4617
4618
4619
4620
4621
4622
4623
4624
4625
4626 /* -------------------------------------------------------------------------+
4627 Insert into BOM_REFERENCE_DESIGNATORS table
4628 +--------------------------------------------------------------------------*/
4629 IF PG_DEBUG <> 0 THEN
4630 WriteToLog('create_bom_data_ml: ' || 'Before third insert into bom_reference_designators. ', 2);
4631 END IF;
4632 lStmtNum := 320;
4633 xTableName := 'BOM_REFERENCE_DESIGNATORS';
4634 insert into BOM_REFERENCE_DESIGNATORS
4635 (
4636 component_reference_designator,
4637 last_update_date,
4638 last_updated_by,
4639 creation_date,
4640 created_by,
4641 last_update_login,
4642 ref_designator_comment,
4643 change_notice,
4644 component_sequence_id,
4645 acd_type,
4646 request_id,
4647 program_application_id,
4648 program_id,
4649 program_update_date,
4650 attribute_category,
4651 attribute1,
4652 attribute2,
4653 attribute3,
4654 attribute4,
4655 attribute5,
4656 attribute6,
4657 attribute7,
4658 attribute8,
4659 attribute9,
4660 attribute10,
4661 attribute11,
4662 attribute12,
4663 attribute13,
4664 attribute14,
4665 attribute15
4666 )
4667 select
4668 r.component_reference_designator,
4669 SYSDATE,
4670 1,
4671 SYSDATE,
4672 1,
4673 1,
4674 r.REF_DESIGNATOR_COMMENT,
4675 NULL,
4676 ic.COMPONENT_SEQUENCE_ID,
4677 r.ACD_TYPE,
4678 NULL,
4679 NULL,
4680 NULL,
4681 NULL,
4682 r.ATTRIBUTE_CATEGORY,
4683 r.ATTRIBUTE1,
4684 r.ATTRIBUTE2,
4685 r.ATTRIBUTE3,
4686 r.ATTRIBUTE4,
4687 r.ATTRIBUTE5,
4688 r.ATTRIBUTE6,
4689 r.ATTRIBUTE7,
4690 r.ATTRIBUTE8,
4691 r.ATTRIBUTE9,
4692 r.ATTRIBUTE10,
4693 r.ATTRIBUTE11,
4694 r.ATTRIBUTE12,
4695 r.ATTRIBUTE13,
4696 r.ATTRIBUTE14,
4697 r.ATTRIBUTE15
4698 from
4699 bom_inventory_components ic,
4700 bom_reference_designators r,
4701 bom_bill_of_materials b
4702 where b.assembly_item_id = pConfigId
4703 and b.organization_id = pOrgId
4704 and ic.bill_sequence_id = b.bill_sequence_id
4705 and r.component_sequence_id = abs(ic.model_comp_seq_id) -- previously last_update_login
4706 and nvl(r.acd_type,0) <> 3;
4707
4708 IF PG_DEBUG <> 0 THEN
4709 WriteToLog('create_bom_data_ml: ' || xTableName || '-'|| lStmtNum || ': ' || sql%rowcount,1 );
4710 END IF;
4711
4712
4713 -- start 3674833
4714 -- need to insert reference designators of remaining components
4715
4716
4717 if model_comp_seq_id_arr.count > 0 then
4718 prev_comp_item_id := 0;
4719 for x1 in model_comp_seq_id_arr.FIRST..model_comp_seq_id_arr.LAST
4720 loop
4721 IF PG_DEBUG <> 0 THEN
4722 WriteToLog( ' Model_Comp_seq (' ||x1|| ') = ' ||model_comp_seq_id_arr(x1)
4723 ||' Component_item_id (' ||x1|| ') = ' ||component_item_id_arr(x1),1);
4724 END IF;
4725
4726
4727
4728 /* 04-09-2005
4729
4730 bugfix 3985173: Commented following code since there could be instances when same component with
4731 same op seq number is appearing multiple times for a config bom. In that scenario,
4732 following query will return ORA-01422 error
4733
4734 if prev_comp_item_id <> component_item_id_arr(x1) then
4735
4736 -- Determine the component_sequence_id into which this item has been clubbed
4737 select
4738 bic.component_sequence_id into club_component_sequence_id
4739 from
4740 bom_inventory_components bic,
4741 bom_bill_of_materials bom
4742 where bom.assembly_item_id = pConfigId
4743 and bom.organization_id = pOrgId
4744 and bic.bill_sequence_id = bom.bill_sequence_id
4745 and bic.component_item_id = component_item_id_arr(x1);
4746 prev_comp_item_id := component_item_id_arr(x1);
4747 end if;
4748 */
4749
4750
4751
4752
4753 -- bugfix 3985173 : New code will loop through component seq and insert
4754 -- into bom_reference_designator
4755 for a1 in club_comp_seq ( component_item_id_arr(x1), operation_seq_num_arr(x1) ) loop --4244576
4756
4757 club_component_sequence_id := a1.comp_seq_id;
4758
4759
4760 -- insert into BOM_REFERENCE_DESIGNATORS for the corresponding model_comp_seq_id
4761 -- if it has not already been inserted.
4762 IF PG_DEBUG <> 0 THEN
4763 WriteToLog('club_component_sequence_id is '||club_component_sequence_id, 1);
4764 END if;
4765 IF PG_DEBUG <> 0 THEN
4766 WriteToLog('Trying to insert into BOM_REFERENCE_DESIGNATORS', 1);
4767 END if;
4768 begin
4769 insert into BOM_REFERENCE_DESIGNATORS
4770 (
4771 component_reference_designator,
4772 last_update_date,
4773 last_updated_by,
4774 creation_date,
4775 created_by,
4776 last_update_login,
4777 ref_designator_comment,
4778 change_notice,
4779 component_sequence_id,
4780 acd_type,
4781 request_id,
4782 program_application_id,
4783 program_id,
4784 program_update_date,
4785 attribute_category,
4786 attribute1,
4787 attribute2,
4788 attribute3,
4789 attribute4,
4790 attribute5,
4791 attribute6,
4792 attribute7,
4793 attribute8,
4794 attribute9,
4795 attribute10,
4796 attribute11,
4797 attribute12,
4798 attribute13,
4799 attribute14,
4800 attribute15
4801 )
4802 select
4803 r.component_reference_designator,
4804 SYSDATE,
4805 1,
4806 SYSDATE,
4807 1,
4808 1,
4809 r.REF_DESIGNATOR_COMMENT,
4810 NULL,
4811 club_component_sequence_id,
4812 r.ACD_TYPE,
4813 NULL,
4814 NULL,
4815 NULL,
4816 NULL,
4817 r.ATTRIBUTE_CATEGORY,
4818 r.ATTRIBUTE1,
4819 r.ATTRIBUTE2,
4820 r.ATTRIBUTE3,
4821 r.ATTRIBUTE4,
4822 r.ATTRIBUTE5,
4823 r.ATTRIBUTE6,
4824 r.ATTRIBUTE7,
4825 r.ATTRIBUTE8,
4826 r.ATTRIBUTE9,
4827 r.ATTRIBUTE10,
4828 r.ATTRIBUTE11,
4829 r.ATTRIBUTE12,
4830 r.ATTRIBUTE13,
4831 r.ATTRIBUTE14,
4832 r.ATTRIBUTE15
4833 from
4834 bom_reference_designators r
4835 where r.component_sequence_id = abs(model_comp_seq_id_arr(x1))
4836 and nvl(r.acd_type,0) <> 3;
4837
4838 --moved here for 4492875
4839 IF PG_DEBUG <> 0 THEN
4840 WriteToLog('For this record '||sql%rowcount||' records are inserted in bom_reference_designators', 1);
4841 END if;
4842 exception
4843 when others then
4844 IF PG_DEBUG <> 0 THEN
4845 WriteToLog('The record for this designator and component sequence already exists in BOM_REFERENCE_DESIGNATORS', 1);
4846 END IF;
4847 end;
4848
4849 end loop; -- 3985173 : end of club_comp_seq cursor loop
4850
4851 prev_comp_item_id := component_item_id_arr(x1); -- 3985173
4852
4853 end loop ;
4854
4855 end if;
4856
4857
4858 -- end 3674833
4859
4860
4861 xRtgId := lCfgRtgId;
4862
4863 /*-----------------------------------------------------------+
4864 Update MTL_DESCR_ELEMENT_VALUES table
4865 +------------------------------------------------------------*/
4866
4867 xTableName := 'MTL_DESCR_ELEMENT_VALUES';
4868 lStmtNum := 330;
4869
4870
4871 if CTO_CUSTOM_CATALOG_DESC.catalog_desc_method = 'C' then
4872 -- Call Custom API with details..
4873
4874 WriteToLog ('Prepare data for calling custom hook...', 5);
4875
4876 DECLARE
4877 cursor ctg is
4878 select ELEMENT_NAME
4879 from mtl_descr_element_values
4880 where inventory_item_id = pConfigId;
4881
4882 l_catalog_dtls CTO_CUSTOM_CATALOG_DESC.CATALOG_DTLS_TBL_TYPE;
4883 l_params CTO_CUSTOM_CATALOG_DESC.INPARAMS;
4884 i NUMBER;
4885 original_count NUMBER;
4886 l_return_status VARCHAR2(1);
4887
4888 BEGIN
4889 i := 1;
4890 l_return_status := FND_API.G_RET_STS_SUCCESS;
4891
4892 for rec in ctg
4893 loop
4894 l_catalog_dtls(i).cat_element_name := rec.element_name;
4895 l_catalog_dtls(i).cat_element_value := NULL;
4896 WriteToLog ('l_catalog_dtls('||i||').cat_element_name = '||
4897 rec.element_name, 5);
4898 i := i+1;
4899 end loop;
4900
4901 original_count := l_catalog_dtls.count;
4902
4903 -- bugfix 4081613: Do not execute the rest of the code if cursor ctg did not fetch any rows.
4904 if original_count > 0 then
4905 l_params.p_item_id := pConfigId;
4906 l_params.p_org_id := pOrgId;
4907
4908 WriteToLog ('Parameter passed: l_params.p_item_id = '||l_params.p_item_id ||
4909 '; l_params.p_org_id = '||l_params.p_org_id , 5);
4910
4911 CTO_CUSTOM_CATALOG_DESC.user_catalog_desc (
4912 p_params => l_params,
4913 p_catalog_dtls => l_catalog_dtls,
4914 x_return_status => l_return_status);
4915
4916 if( l_return_status = FND_API.G_RET_STS_ERROR ) then
4917 WriteToLog ('CTO_CUSTOM_CATALOG_DESC.user_catalog_desc returned exp error', 1);
4918 RAISE FND_API.G_EXC_ERROR ;
4919
4920 elsif( l_return_status = FND_API.G_RET_STS_UNEXP_ERROR ) then
4921 WriteToLog ('CTO_CUSTOM_CATALOG_DESC.user_catalog_desc returned unexp error', 1);
4922 RAISE FND_API.G_EXC_UNEXPECTED_ERROR ;
4923 end if ;
4924
4925 if l_catalog_dtls.count <> original_count then
4926 WriteToLog ('ERROR: Custom hook did not return same number of elements.'||
4927 'Original_count='||original_count||
4928 'New count = '||l_catalog_dtls.count, 1);
4929 raise FND_API.G_EXC_ERROR;
4930 end if;
4931
4932 for k in l_catalog_dtls.first..l_catalog_dtls.last
4933 loop
4934 if l_catalog_dtls(k).cat_element_value is not null then
4935 WriteToLog ('l_catalog_dtls('||k||').cat_element_name = '||
4936 l_catalog_dtls(k).cat_element_name||
4937 '; l_catalog_dtls('||k||').cat_element_value = '||
4938 l_catalog_dtls(k).cat_element_value, 5);
4939 lStmtNum := 331;
4940
4941 update MTL_DESCR_ELEMENT_VALUES i
4942 set i.element_value = l_catalog_dtls(k).cat_element_value
4943 where i.inventory_item_id = pConfigId
4944 and i.element_name = l_catalog_dtls(k).cat_element_name;
4945
4946 WriteToLog('Updated rows in mtl_desc_element_values::'|| sql%rowcount, 2);
4947 end if;
4948 end loop;
4949
4950 end if; --bugfix 4081613
4951 END;
4952
4953 elsif CTO_CUSTOM_CATALOG_DESC.catalog_desc_method = 'Y' then
4954 lStmtNum := 332;
4955 WriteToLog ('Std feature : Rollup lower level model catalog desc to top level', 4);
4956 update MTL_DESCR_ELEMENT_VALUES i
4957 set i.element_value =
4958 ( select /*+ ORDERED */
4959 NVL(max(v.element_value),i.element_value)
4960 from
4961 bom_bill_of_materials bi,
4962 bom_inventory_components bc1,
4963 bom_inventory_components bc2,
4964 bom_dependent_desc_elements be,
4965 mtl_descr_element_values v
4966 where bi.assembly_item_id = pConfigId
4967 and bi.organization_id = pOrgId
4968 and bi.alternate_bom_Designator is null
4969 and bc1.bill_sequence_id = bi.bill_sequence_id
4970 and bc2.component_sequence_id = abs(bc1.model_comp_seq_id) -- previously last_update_login
4971 and be.bill_sequence_id = bc2.bill_sequence_id
4972 and be.element_name = i.element_name
4973 and v.inventory_item_id = bc1.component_item_id
4974 and v.element_name = i.element_name
4975 )
4976 where i.inventory_item_id = pConfigId;
4977
4978 WriteToLog('Updated rows in mtl_desc_element_values::'|| sql%rowcount, 2);
4979 else
4980
4981 lStmtNum := 333;
4982 WriteToLog ('Std feature : DO NOT Rollup lower level model catalog desc to top level', 4);
4983
4984 update MTL_DESCR_ELEMENT_VALUES i
4985 set i.element_value =
4986 ( select /*+ ORDERED */
4987 NVL(max(v.element_value),i.element_value)
4988 from
4989 bom_bill_of_materials bi,
4990 bom_inventory_components bc1,
4991 bom_inventory_components bc2,
4992 bom_dependent_desc_elements be,
4993 mtl_descr_element_values v
4994 where bi.assembly_item_id = pConfigId
4995 and bi.organization_id = pOrgId
4996 and bi.alternate_bom_Designator is null
4997 and bc1.bill_sequence_id = bi.bill_sequence_id
4998 and bc2.component_sequence_id = abs(bc1.model_comp_seq_id) -- previously last_update_login
4999 and be.bill_sequence_id = bc2.bill_sequence_id
5000 and be.element_name = i.element_name
5001 and v.inventory_item_id = bc1.component_item_id
5002 and v.element_name = i.element_name
5003 -- bugfix 2590966
5004 -- Following code eliminates lower level configurations
5005 -- Fp bug fix 4761813. Modified the sub query sql to
5006 -- user exists clause instead of using not in for performance
5007 -- reason
5008 and not exists
5009 (
5010 SELECT 'x' FROM MTL_SYSTEM_ITEMS
5011 WHERE ORGANIZATION_ID = pOrgId
5012 AND BC1.COMPONENT_ITEM_ID = INVENTORY_ITEM_ID
5013 AND BASE_ITEM_ID IS NOT NULL
5014 AND BOM_ITEM_TYPE = 4
5015 AND REPLENISH_TO_ORDER_FLAG = 'Y'
5016 )
5017 -- end bugfix 2590966
5018 )
5019 where i.inventory_item_id = pConfigId;
5020 WriteToLog('Updated rows in mtl_desc_element_values::'|| sql%rowcount, 2);
5021 end if;
5022
5023
5024 /*---------------------------------------------------------------------+
5025 Update descriptions of the config items in
5026 the MTL_SYSTEM_ITEMS
5027 +----------------------------------------------------------------------*/
5028
5029 lStmtNum := 350;
5030 xTableName := 'MTL_SYSTEM_ITMES';
5031 l_status := bmlupid_update_item_desc(pConfigid,
5032 pOrgId,
5033 xErrorMessage);
5034
5035 if l_status <> 0 then
5036 WriteToLog('ERROR:bmlupid_update_item_desc returned error::' || l_status, 1);
5037 raise FND_API.G_EXC_ERROR;
5038 end if;
5039 /*------------------------------------------------------------+
5040 Copy BOM attachments
5041 +------------------------------------------------------------*/
5042
5043 lStmtNum := 360;
5044 select common_bill_sequence_id
5045 into l_from_sequence_id
5046 from bom_bill_of_materials
5047 where assembly_item_id = pModelId
5048 and organization_id = pOrgId
5049 and alternate_bom_designator is NULL;
5050
5051 lStmtNum := 370;
5052 fnd_attached_documents2_pkg.copy_attachments(
5053 X_from_entity_name => 'BOM_BILL_OF_MATERIALS',
5054 X_from_pk1_value => l_from_sequence_id,
5055 X_from_pk2_value => '',
5056 X_from_pk3_value => '',
5057 X_from_pk4_value => '',
5058 X_from_pk5_value => '',
5059 X_to_entity_name => 'BOM_BILL_OF_MATERIALS',
5060 X_to_pk1_value => lConfigBillId,
5061 X_to_pk2_value => '',
5062 X_to_pk3_value => '',
5063 X_to_pk4_value => '',
5064 X_to_pk5_value => '',
5065 X_created_by => 1,
5066 X_last_update_login => '',
5067 X_program_application_id=> '',
5068 X_program_id => '',
5069 X_request_id => ''
5070 );
5071
5072 lStmtNum := 380;
5073
5074 /* Clean up bom_inventory_comps_interface */
5075
5076 delete from bom_inventory_comps_interface
5077 where bill_sequence_id = lConfigBillId;
5078
5079
5080 return(1);
5081
5082
5083 EXCEPTION
5084
5085 WHEN NO_DATA_FOUND THEN
5086 xBillID := 0;
5087 return(0);
5088
5089 WHEN FND_API.G_EXC_ERROR THEN
5090 xErrorMessage := 'CTOCBOMB:create_bom_ml failed with expected error in stmt '||to_char(lStmtNum);
5091 xMessageName := 'CTO_CREATE_BOM_ERROR';
5092 WriteToLog('++++++++++++++++++++++++++++++++++++++++++++++++', 1);
5093 WriteToLog('ERROR: Expected error in Update_Bom_Rtg_Loop::'||to_char(lStmtNum)||sqlerrm,1);
5094 WriteToLog('++++++++++++++++++++++++++++++++++++++++++++++++', 1);
5095 return(0);
5096
5097 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5098 xErrorMessage := 'CTOCBOMB:create_bom_ml failed with unexpected error in stmt '||to_char(lStmtNum);
5099 xMessageName := 'CTO_CREATE_BOM_ERROR';
5100 WriteToLog('++++++++++++++++++++++++++++++++++++++++++++++++', 1);
5101 WriteToLog('ERROR: Unexpected error in Update_Bom_Rtg_Loop::'||to_char(lStmtNum)||sqlerrm,1);
5102 WriteToLog('++++++++++++++++++++++++++++++++++++++++++++++++', 1);
5103 return(0);
5104
5105
5106 WHEN OTHERS THEN
5107 xErrorMessage := 'CTOCBOMB:'||to_char(lStmtNum)||':'||substrb(sqlerrm,1,150);
5108 xMessageName := 'CTO_CREATE_BOM_ERROR';
5109 WriteToLog('++++++++++++++++++++++++++++++++++++++++++++++++', 1);
5110 WriteToLog('ERROR: Others error in Update_Bom_Rtg_Loop::'||to_char(lStmtNum)||sqlerrm,1);
5111 WriteToLog('++++++++++++++++++++++++++++++++++++++++++++++++', 1);
5112 return(0);
5113
5114 END Update_Bom_Rtg_Loop;
5115
5116
5117 PROCEDURE Update_Bom_Rtg_Bulk(
5118 p_seq in number,
5119 xReturnStatus out NOCOPY varchar2,
5120 xMsgCount out NOCOPY number,
5121 xMsgData out NOCOPY varchar2)
5122 IS
5123
5124 lStmtNum number;
5125
5126 BEGIN
5127
5128 WriteToLog('Entering update_bom_rtg_bulk', 1);
5129
5130 /*-----------------------------------------------------+
5131 Process routing revision table
5132 +-----------------------------------------------------*/
5133 lStmtNum := 70;
5134
5135 WriteToLog('Inserting into mtl_rtg_item_revisions..',5);
5136 insert into MTL_RTG_ITEM_REVISIONS
5137 (
5138 inventory_item_id,
5139 organization_id,
5140 process_revision,
5141 last_update_date,
5142 last_updated_by,
5143 creation_date,
5144 created_by,
5145 last_update_login,
5146 change_notice ,
5147 ecn_initiation_date,
5148 implementation_date,
5149 implemented_serial_number,
5150 effectivity_date ,
5151 attribute_category,
5152 attribute1 ,
5153 attribute2,
5154 attribute3,
5155 attribute4,
5156 attribute5,
5157 attribute6,
5158 attribute7,
5159 attribute8,
5160 attribute9,
5161 attribute10,
5162 ATTRIBUTE11,
5163 ATTRIBUTE12,
5164 ATTRIBUTE13 ,
5165 ATTRIBUTE14,
5166 ATTRIBUTE15
5167 )
5168 select distinct
5169 bor.assembly_item_id,
5170 bor.organization_id,
5171 mp.starting_revision,
5172 sysdate, /* LAST_UPDATE_DATE */
5173 gUserId, /* LAST_UPDATED_BY */
5174 sysdate, /* CREATION_DATE */
5175 gUserId, /* created_by */
5176 gLoginId, /* last_update_login */
5177 NULL, /* CHANGE_NOTICE */
5178 NULL, /* ECN_INITIATION_DATE */
5179 TRUNC(SYSDATE), /* IMPLEMENTATION_DATE */
5180 NULL, /* IMPLEMENTED_SERIAL_NUMBER */
5181 TRUNC(SYSDATE), /* EFFECTIVITY_DATE */
5182 NULL, /* ATTRIBUTE_CATEGORY */
5183 NULL, /* ATTRIBUTE1 */
5184 NULL, /* ATTRIBUTE2 */
5185 NULL, /* ATTRIBUTE3 */
5186 NULL, /* ATTRIBUTE4 */
5187 NULL, /* ATTRIBUTE5 */
5188 NULL, /* ATTRIBUTE6 */
5189 NULL, /* ATTRIBUTE7 */
5190 NULL, /* ATTRIBUTE8 */
5191 NULL, /* ATTRIBUTE9 */
5192 NULL, /* ATTRIBUTE10 */
5193 NULL, /* ATTRIBUTE11 */
5194 NULL, /* ATTRIBUTE12 */
5195 NULL, /* ATTRIBUTE13 */
5196 NULL, /* ATTRIBUTE14 */
5197 NULL /* ATTRIBUTE15 */
5198 from bom_operational_routings bor,
5199 mtl_parameters mp,
5200 bom_cto_order_lines_upg bcolu
5201 where bcolu.sequence = p_seq
5202 and bcolu.status = 'BOM_LOOP'
5203 and bcolu.config_item_id = bor.assembly_item_id
5204 and bor.alternate_routing_designator is null
5205 -- and bor.routing_sequence_id = lCfgRtgId
5206 and bor.organization_id = mp.organization_id
5207 and not exists (
5208 select 'exists'
5209 from mtl_rtg_item_revisions mrir
5210 where mrir.inventory_item_id = bcolu.config_item_id
5211 and mrir.organization_id = mp.organization_id
5212 and mrir.process_revision = mp.starting_revision);
5213
5214 WriteToLog('Inserted rows into mtl_rtg_item_revisions::'||sql%rowcount, 3);
5215 /*------------------------------------------------+
5216 ** Load operation resources table
5217 ** 3 new columns added for WIP Simultaneous Resources
5218 +-------------------------------------------------*/
5219
5220 lStmtNum := 150;
5221
5222 WriteToLog('Inserting into bom_operation_resources..',5);
5223 insert into BOM_OPERATION_RESOURCES
5224 (
5225 operation_sequence_id,
5226 resource_seq_num,
5227 resource_id ,
5228 activity_id,
5229 standard_rate_flag,
5230 assigned_units ,
5231 usage_rate_or_amount,
5232 usage_rate_or_amount_inverse,
5233 basis_type,
5234 schedule_flag,
5235 last_update_date,
5236 last_updated_by,
5237 creation_date,
5238 created_by,
5239 last_update_login,
5240 resource_offset_percent,
5241 autocharge_type,
5242 attribute_category,
5243 attribute1,
5244 attribute2,
5245 attribute3,
5246 attribute4,
5247 attribute5,
5248 attribute6,
5249 attribute7,
5250 attribute8,
5251 attribute9,
5252 attribute10,
5253 attribute11,
5254 attribute12,
5255 attribute13,
5256 attribute14,
5257 attribute15,
5258 request_id,
5259 program_application_id,
5260 program_id,
5261 program_update_date,
5262 schedule_seq_num,
5263 substitute_group_num,
5264 setup_id, /*bugfix2950774*/
5265 principle_flag
5266 )
5267 select distinct
5268 osi.operation_sequence_id, /* operation sequence id */
5269 bor.resource_seq_num,
5270 bor.resource_id, /* resource id */
5271 bor.activity_id,
5272 bor.standard_rate_flag,
5273 bor.assigned_units,
5274 bor.usage_rate_or_amount,
5275 bor.usage_rate_or_amount_inverse,
5276 bor.basis_type,
5277 bor.schedule_flag,
5278 SYSDATE, /* last update date */
5279 gUserId, /* last updated by */
5280 SYSDATE, /* creation date */
5281 gUserId, /* created by */
5282 1, /* last update login */
5283 bor.resource_offset_percent,
5284 bor.autocharge_type,
5285 bor.attribute_category,
5286 bor.attribute1,
5287 bor.attribute2,
5288 bor.attribute3,
5289 bor.attribute4,
5290 bor.attribute5,
5291 bor.attribute6,
5292 bor.attribute7,
5293 bor.attribute8,
5294 bor.attribute9,
5295 bor.attribute10,
5296 bor.attribute11,
5297 bor.attribute12,
5298 bor.attribute13,
5299 bor.attribute14,
5300 bor.attribute15,
5301 NULL, /* request_id */
5302 NULL, /* program_application_id */
5303 NULL, /* program_id */
5304 NULL, /* program_update_date */
5305 bor.schedule_seq_num,
5306 bor.substitute_group_num,
5307 bor.setup_id, /* Bugfix2950774 */
5308 bor.principle_flag
5309 from
5310 bom_operation_sequences osi,
5311 bom_operation_resources bor,
5312 bom_cto_order_lines_upg bcolu,
5313 bom_operational_routings bor1
5314 where bcolu.sequence = p_seq
5315 and bcolu.status = 'BOM_LOOP'
5316 and bcolu.config_item_id = bor1.assembly_item_id
5317 and osi.routing_sequence_id = bor1.routing_sequence_id
5318 -- and osi.routing_sequence_id = lCfgRtgId
5319 and osi.request_id = bor.operation_sequence_id
5320 and not exists (
5321 select 'exists'
5322 from bom_operation_resources bor2
5323 where bor2.operation_sequence_id = osi.operation_sequence_id
5324 and bor2.resource_seq_num = bor.resource_seq_num);
5325
5326 /* request_id contains model op seq_id now */
5327
5328 WriteToLog('Inserted rows into bom_operation_resources::'||sql%rowcount, 3);
5329
5330
5331 /*------------------------------------------------+
5332 ** Load sub operation resources table
5333 ** new table for WIP Simultaneous Resources
5334 +-------------------------------------------------*/
5335 lStmtNum := 155;
5336
5337 WriteToLog('Inserting into bom_sub_operation_resources ..',5);
5338 insert into BOM_SUB_OPERATION_RESOURCES
5339 (operation_sequence_id,
5340 substitute_group_num,
5341 --resource_seq_num,
5342 resource_id,
5343 --scheduling_seq_num,
5344 schedule_seq_num,
5345 replacement_group_num,
5346 activity_id,
5347 standard_rate_flag,
5348 assigned_units,
5349 usage_rate_or_amount,
5350 usage_rate_or_amount_inverse,
5351 basis_type,
5352 schedule_flag,
5353 last_update_date,
5354 last_updated_by,
5355 creation_date,
5356 created_by,
5357 last_update_login,
5358 resource_offset_percent,
5359 autocharge_type,
5360 principle_flag,
5361 attribute_category,
5362 attribute1,
5363 attribute2,
5364 attribute3,
5365 attribute4,
5366 attribute5,
5367 attribute6,
5368 attribute7,
5369 attribute8,
5370 attribute9,
5371 attribute10,
5372 attribute11,
5373 attribute12,
5374 attribute13,
5375 attribute14,
5376 attribute15,
5377 setup_id, /* bugfix2950774 */
5378 request_id,
5379 program_application_id,
5380 program_id,
5381 program_update_date
5382 )
5383 select distinct
5384 osi.operation_sequence_id,
5385 bsor.substitute_group_num,
5386 --bsor.resource_seq_num,
5387 bsor.resource_id,
5388 --bsor.scheduling_seq_num,
5389 bsor.schedule_seq_num,
5390 bsor.replacement_group_num,
5391 bsor.activity_id,
5392 bsor.standard_rate_flag,
5393 bsor.assigned_units,
5394 bsor.usage_rate_or_amount,
5395 bsor.usage_rate_or_amount_inverse,
5396 bsor.basis_type,
5397 bsor.schedule_flag,
5398 SYSDATE, /*last_update_date*/
5399 gUserId, /*last_updated_by*/
5400 SYSDATE, /*creation_date*/
5401 gUserId, /*created_by*/
5402 1, /*last_update_login*/
5403 bsor.resource_offset_percent,
5404 bsor.autocharge_type,
5405 bsor.principle_flag,
5406 bsor.attribute_category,
5407 bsor.attribute1,
5408 bsor.attribute2,
5409 bsor.attribute3,
5410 bsor.attribute4,
5411 bsor.attribute5,
5412 bsor.attribute6,
5413 bsor.attribute7,
5414 bsor.attribute8,
5415 bsor.attribute9,
5416 bsor.attribute10,
5417 bsor.attribute11,
5418 bsor.attribute12,
5419 bsor.attribute13,
5420 bsor.attribute14,
5421 bsor.attribute15,
5422 bsor.setup_id, /* bugfix2950774 */
5423 NULL, /*request_id*/
5424 NULL, /*program_application_id*/
5425 NULL, /*program_id*/
5426 NULL /*program_update_date*/
5427 from
5428 bom_operation_sequences osi,
5429 bom_sub_operation_resources bsor,
5430 bom_cto_order_lines_upg bcolu,
5431 bom_operational_routings bor
5432 where bcolu.sequence = p_seq
5433 and bcolu.status = 'BOM_LOOP'
5434 and bcolu.config_item_id = bor.assembly_item_id
5435 and osi.routing_sequence_id = bor.routing_sequence_id
5436 -- and osi.routing_sequence_id = lCfgRtgId
5437 and osi.request_id = bsor.operation_sequence_id
5438 and not exists (
5439 select 'exists'
5440 from bom_sub_operation_resources bsor1
5441 where bsor1.operation_sequence_id = osi.operation_sequence_id
5442 and bsor1.resource_id = bsor.resource_id
5443 and bsor1.substitute_group_num = bsor.substitute_group_num
5444 and bsor1.replacement_group_num = bsor.replacement_group_num);
5445
5446 /* request_id contains model op seq_id now */
5447
5448 WriteToLog('Inserted rows into bom_sub_operation_resources::'||sql%rowcount, 3);
5449
5450 /*---------------------------------------------------+
5451 ** Process operation Networks table
5452 +---------------------------------------------------*/
5453 lStmtNum := 380;
5454
5455 WriteToLog('Inserting into bom_operation_networks ..',5);
5456 INSERT INTO bom_operation_networks
5457 ( FROM_OP_SEQ_ID,
5458 TO_OP_SEQ_ID,
5459 TRANSITION_TYPE,
5460 PLANNING_PCT,
5461 EFFECTIVITY_DATE,
5462 DISABLE_DATE,
5463 CREATED_BY,
5464 CREATION_DATE,
5465 LAST_UPDATED_BY,
5466 LAST_UPDATE_DATE,
5467 LAST_UPDATE_LOGIN,
5468 ATTRIBUTE_CATEGORY,
5469 ATTRIBUTE1 ,
5470 ATTRIBUTE2 ,
5471 ATTRIBUTE3 ,
5472 ATTRIBUTE4 ,
5473 ATTRIBUTE5 ,
5474 ATTRIBUTE6 ,
5475 ATTRIBUTE7 ,
5476 ATTRIBUTE8 ,
5477 ATTRIBUTE9 ,
5478 ATTRIBUTE10 ,
5479 ATTRIBUTE11 ,
5480 ATTRIBUTE12 ,
5481 ATTRIBUTE13 ,
5482 ATTRIBUTE14 ,
5483 ATTRIBUTE15
5484 )
5485 SELECT distinct
5486 bos3.operation_sequence_id,
5487 bos4.operation_sequence_id,
5488 bon.TRANSITION_TYPE,
5489 bon.PLANNING_PCT,
5490 bon.EFFECTIVITY_DATE,
5491 bon.DISABLE_DATE,
5492 bon.CREATED_BY,
5493 bon.CREATION_DATE,
5494 bon.LAST_UPDATED_BY,
5495 bon.LAST_UPDATE_DATE,
5496 bon.LAST_UPDATE_LOGIN,
5497 bon.ATTRIBUTE_CATEGORY,
5498 bon.ATTRIBUTE1,
5499 bon.ATTRIBUTE2,
5500 bon.ATTRIBUTE3,
5501 bon.ATTRIBUTE4,
5502 bon.ATTRIBUTE5,
5503 bon.ATTRIBUTE6,
5504 bon.ATTRIBUTE7,
5505 bon.ATTRIBUTE8,
5506 bon.ATTRIBUTE9,
5507 bon.ATTRIBUTE10,
5508 bon.ATTRIBUTE11,
5509 bon.ATTRIBUTE12,
5510 bon.ATTRIBUTE13,
5511 bon.ATTRIBUTE14,
5512 bon.ATTRIBUTE15
5513 FROM bom_operation_networks bon,
5514 bom_operation_sequences bos1, /* 'from' Ops of model */
5515 bom_operation_sequences bos2, /* 'to' Ops of model */
5516 bom_operation_sequences bos3, /* 'from' Ops of config */
5517 bom_operation_sequences bos4, /* 'to' Ops of config */
5518 bom_operational_routings brif,
5519 bom_cto_order_lines_upg bcolu
5520 WHERE bon.from_op_seq_id = bos1.operation_sequence_id
5521 AND bon.to_op_seq_id = bos2.operation_sequence_id
5522 AND bos1.routing_sequence_id = bos2.routing_sequence_id
5523 AND bos3.routing_sequence_id = brif.routing_sequence_id
5524 AND brif.cfm_routing_flag = 1
5525 --AND brif.routing_sequence_id = lCfgrtgId
5526 and bcolu.sequence = p_seq
5527 and bcolu.status = 'BOM_LOOP'
5528 and bcolu.config_item_id = brif.assembly_item_id
5529 and brif.alternate_routing_designator is null
5530 AND bos3.operation_seq_num = bos1.operation_seq_num
5531 AND NVL(bos3.operation_type,1) = NVL(bos1.operation_type, 1)
5532 AND bos4.routing_sequence_id = bos3.routing_sequence_id
5533 AND bos4.operation_seq_num = bos2.operation_seq_num
5534 AND NVL(bos4.operation_type,1) = NVL(bos2.operation_type, 1)
5535 AND bos1.routing_sequence_id = ( /* find the model routing */
5536 select routing_sequence_id
5537 from bom_operational_routings bor,
5538 mtl_system_items msi
5539 where brif.assembly_item_id = msi.inventory_item_id
5540 and brif.organization_id = msi.organization_id
5541 and bor.assembly_item_id = msi.base_item_id
5542 and bor.organization_id = msi.organization_id
5543 and bor.cfm_routing_flag = 1
5544 and bor.alternate_routing_designator is null )
5545 and not exists (
5546 select 'exists'
5547 from bom_operation_networks bon2
5548 where bon2.from_op_seq_id = bos3.operation_sequence_id
5549 and bon2.to_op_seq_id = bos4.operation_sequence_id);
5550
5551 WriteToLog('Inserted rows into bom_operation_networks::'||sql%rowcount, 3);
5552
5553
5554 /* -------------------------------------------------------------------------+
5555 Insert into BOM_REFERENCE_DESIGNATORS table
5556 HAS BEEN REMOVED AS PART OF BUGFIX 3793286
5557 as there is already a insert in this table in api update_bom_rtg_loop.
5558 For additional details look at update *** KKONADA 11/05/04 03:43 pm ***
5559 of bug 3793286
5560 +--------------------------------------------------------------------------*/
5561
5562
5563 EXCEPTION
5564
5565 WHEN OTHERS THEN
5566 WriteToLog('++++++++++++++++++++++++++++++++++++++++++++++++', 1);
5567 WriteToLog('ERROR: Others error in Update_Bom_Rtg_Bulk::'||to_char(lStmtNum)||sqlerrm,1);
5568 WriteToLog('++++++++++++++++++++++++++++++++++++++++++++++++', 1);
5569 xReturnStatus := fnd_api.g_ret_sts_unexp_error;
5570 cto_msg_pub.count_and_get
5571 ( p_msg_count => xMsgCount
5572 , p_msg_data => xMsgData
5573 );
5574
5575
5576 END Update_Bom_Rtg_Bulk;
5577
5578 /*------------------------------------------------+
5579 This procedure is called in a loop to update the
5580 Item Sequence Number on the components of the configuration
5581 BOM such that there are no duplicates, and the logical order
5582 in which they are selected from the model BOM is maintained.
5583 +------------------------------------------------*/
5584 PROCEDURE update_item_num(
5585 p_parent_bill_seq_id IN NUMBER,
5586 p_item_num IN OUT NOCOPY NUMBER,
5587 p_org_id IN NUMBER,
5588 p_seq_increment IN NUMBER)
5589
5590 IS
5591
5592 CURSOR c_update_item_num(p_parent_bill_seq_id number) IS
5593 select component_sequence_id,
5594 component_item_id
5595 from bom_inventory_comps_interface
5596 where parent_bill_seq_id = p_parent_bill_seq_id
5597 FOR UPDATE OF item_num;
5598
5599 p_bill_seq_id number;
5600
5601 BEGIN
5602
5603 FOR v_update_item_num IN c_update_item_num(p_parent_bill_seq_id)
5604 LOOP
5605
5606 WriteToLog('In update loop for item '||to_char(v_update_item_num.component_item_id), 5);
5607
5608 --
5609 -- update item_num of child of this model
5610 --
5611 update bom_inventory_comps_interface
5612 set item_num = p_item_num
5613 where current of c_update_item_num;
5614
5615 WriteToLog('Updated item '||to_char(v_update_item_num.component_item_id)|| ' with item num '||to_char(p_item_num), 5);
5616
5617 p_item_num := p_item_num + p_seq_increment;
5618
5619 --
5620 -- get bill_sequence_id of child
5621 --
5622 BEGIN
5623
5624 select common_bill_sequence_id
5625 into p_bill_seq_id
5626 from bom_bill_of_materials
5627 where assembly_item_id = v_update_item_num.component_item_id
5628 and organization_id = p_org_id
5629 and alternate_bom_designator is null;
5630
5631 WriteToLog('Calling update_item_num will p_bill_seq_id::'||to_char(p_bill_seq_id)||' and p_item_num::'||to_char(p_item_num), 5);
5632
5633 update_item_num(
5634 p_bill_seq_id,
5635 p_item_num,
5636 p_org_id,
5637 p_seq_increment);
5638
5639 EXCEPTION
5640 WHEN NO_DATA_FOUND THEN
5641 WriteToLog('This component '||to_char(v_update_item_num.component_item_id)||' does not have a BOM', 2);
5642
5643 END;
5644
5645 END LOOP;
5646
5647 EXCEPTION
5648 WHEN OTHERS THEN
5649 WriteToLog('++++++++++++++++++++++++++++++++++++++++++++++++', 1);
5650 WriteToLog('ERROR: Others error in Update_Item_Num::'||sqlerrm,1);
5651 WriteToLog('++++++++++++++++++++++++++++++++++++++++++++++++', 1);
5652
5653 END update_item_num;
5654
5655
5656 FUNCTION inherit_op_seq_ml(
5657 pLineId in oe_order_lines.line_id%TYPE := NULL,
5658 pOrgId in oe_order_lines.ship_from_org_id%TYPE := NULL,
5659 pModelId in bom_bill_of_materials.assembly_item_id%TYPE := NULL ,
5660 pConfigBillId in bom_inventory_components.bill_sequence_id%TYPE := NULL,
5661 xErrorMessage out NOCOPY VARCHAR2,
5662 xMessageName out NOCOPY VARCHAR2)
5663 RETURN INTEGER IS
5664
5665 CURSOR c_incl_items_all_level ( xOrgId mtl_system_items.organization_id%TYPE,
5666 xLineId bom_cto_order_lines_upg.line_id%TYPE,
5667 xConfigBillId bom_inventory_components.bill_sequence_id%TYPE ,
5668 xSchShpdt date,
5669 xEstReldt date ) IS
5670 select bbm.organization_id,
5671 nvl(bic.operation_seq_num,1) operation_seq_num , -- 2433862
5672 nvl(bet.operation_seq_num,1) parent_op_seq_num, -- 2433862
5673 bic.component_item_id,
5674 bic.item_num,
5675 decode(nvl(bic.basis_type,1),1,bic.component_quantity * (bcol1.ordered_quantity / bcol2.ordered_quantity ),bic.component_quantity) component_qty,
5676 bic.component_yield_factor,
5677 bic.component_remarks, --Bugfix 7188428
5678 bic.attribute_category,
5679 bic.attribute1,
5680 bic.attribute2,
5681 bic.attribute3,
5682 bic.attribute4,
5683 bic.attribute5,
5684 bic.attribute6,
5685 bic.attribute7,
5686 bic.attribute8,
5687 bic.attribute9,
5688 bic.attribute10,
5689 bic.attribute11,
5690 bic.attribute12,
5691 bic.attribute13,
5692 bic.attribute14,
5693 bic.attribute15,
5694 bic.so_basis,
5695 bic.include_in_cost_rollup,
5696 bic.check_atp,
5697 bic.required_for_revenue,
5698 bic.include_on_ship_docs,
5699 bic.include_on_bill_docs,
5700 bic.wip_supply_type,
5701 bic.component_sequence_id, -- model comp seq for later use
5702 bic.supply_subinventory,
5703 bic.supply_locator_id,
5704 bic.bom_item_type,
5705 bic.bill_sequence_id, -- parent_bill_seq_id
5706 bcol1.plan_level+1 plan_level,
5707 decode( -- 3222932 /* 02-14-2005 Sushant */
5708 greatest(bic.effectivity_date,sysdate),
5709 bic.effectivity_date ,
5710 bic.effectivity_date ,
5711 sysdate ) eff_date,
5712 nvl(bic.disable_date,g_futuredate) dis_date, -- 3222932 /* 02-14-2005 Sushant */
5713 nvl(bic.basis_type,1) basis_type
5714 from bom_cto_order_lines_upg bcol1, -- COMPONENT
5715 bom_cto_order_lines_upg bcol2, -- MODEL
5716 mtl_system_items si1,
5717 mtl_system_items si2,
5718 bom_bill_of_materials bbm,
5719 bom_inventory_components bic, -- Components
5720 bom_inventory_components bic1, -- Parent
5721 bom_explosion_temp bet
5722 where bcol1.parent_ato_line_id = xLineId
5723 and bcol1.component_code = bet.component_code
5724 and si1.organization_id = xOrgId
5725 and bcol1.inventory_item_id = si1.inventory_item_id
5726 and si1.bom_item_type in (1,2) -- model, option class
5727 and si2.inventory_item_id = bcol2.inventory_item_id
5728 and si2.organization_id = si1.organization_id
5729 and si2.bom_item_type = 1
5730 and (bcol1.parent_ato_line_id = bcol2.line_id
5731 and ( bcol1.bom_item_type <> 1
5732 or ( bcol1.bom_item_type = 1
5733 and nvl(bcol1.wip_supply_type, 0) = 6
5734 )
5735 )
5736 )
5737 and bet.bill_sequence_id = xConfigBillId
5738 and bet.top_bill_sequence_id = xConfigBillId
5739 and bic1.component_sequence_id = bcol1.component_sequence_id
5740 and bic1.bom_item_type in (1,2)
5741 and bbm.assembly_item_id = bic1.component_item_id
5742 and bbm.organization_id = si1.organization_id
5743 and bbm.alternate_bom_designator is NULL
5744 and bic.bill_sequence_id = DECODE(bbm.common_bill_sequence_id,bbm.bill_sequence_id,bbm.bill_sequence_id,bbm.common_bill_sequence_id)
5745 and bic.optional = 2
5746 and bic.bom_item_type = 4
5747 and bic.effectivity_date <= greatest( NVL(xSchShpdt,sysdate),sysdate)
5748 and bic.implementation_date is not null
5749 and NVL(bic.disable_date,NVL(xEstReldt, SYSDATE)+1) > NVL(xEstReldt,SYSDATE)
5750 and NVL(bic.disable_date,SYSDATE) >= SYSDATE;
5751
5752 CURSOR c_model_oc_oi_rows(xConfigBillId bom_inventory_components.bill_sequence_id%TYPE) IS
5753 SELECT /*+ INDEX( BOM_EXPLOSION_TEMP BOM_EXPLOSION_TEMP_N11) */
5754 nvl(operation_seq_num,1) operation_seq_num, -- 2433862
5755 component_code,
5756 rowid
5757 from bom_explosion_temp
5758 where bill_sequence_id = xConfigBillId
5759 and component_code IS NOT NULL
5760 ORDER BY component_code;
5761
5762 lStmtNumber number;
5763 lCnt number;
5764
5765 /* begin 04-04-2005 */
5766 v_zero_qty_count number ;
5767 l_token1 CTO_MSG_PUB.token_tbl;
5768 v_model_item_name varchar2(2000) ;
5769 /* end 04-04-2005 */
5770
5771
5772
5773 v_overlap_check number := 0 ;
5774
5775 TYPE num_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
5776 TYPE date_tab IS TABLE OF DATE INDEX BY BINARY_INTEGER;
5777
5778 v_t_overlap_comp_item_id num_tab;
5779 v_t_overlap_src_op_seq_num num_tab;
5780 v_t_overlap_src_eff_date date_tab;
5781 v_t_overlap_src_disable_date date_tab;
5782 v_t_overlap_dest_op_seq_num num_tab;
5783 v_t_overlap_dest_eff_date date_tab;
5784 v_t_overlap_dest_disable_date date_tab;
5785
5786 l_token2 CTO_MSG_PUB.token_tbl;
5787
5788
5789 BEGIN
5790
5791
5792 lStmtNumber := 520;
5793
5794 --
5795 -- Insert Option Classes and Option Items
5796 -- Compare to last insert , here we have an addl column
5797 -- component_code to insert comp_code of classes /items
5798 -- from bcol
5799 --
5800
5801 insert into bom_explosion_temp
5802 ( top_bill_sequence_id,
5803 organization_id,
5804 plan_level,
5805 sort_order,
5806 operation_seq_num,
5807 component_item_id,
5808 item_num,
5809 component_quantity,
5810 component_yield_factor,
5811 component_remarks, --Bugfix 7188428
5812 context, -- mapped to attribute_category in bic interface
5813 attribute1,
5814 attribute2,
5815 attribute3,
5816 attribute4,
5817 attribute5,
5818 attribute6,
5819 attribute7,
5820 attribute8,
5821 attribute9,
5822 attribute10,
5823 attribute11,
5824 attribute12,
5825 attribute13,
5826 attribute14,
5827 attribute15,
5828 planning_factor,
5829 select_quantity,
5830 so_basis,
5831 optional,
5832 mutually_exclusive_options,
5833 include_in_rollup_flag,
5834 check_atp,
5835 shipping_allowed,
5836 required_to_ship,
5837 required_for_revenue,
5838 include_on_ship_docs,
5839 include_on_bill_docs,
5840 component_sequence_id,
5841 bill_sequence_id,
5842 wip_supply_type,
5843 pick_components,
5844 base_item_id,
5845 supply_subinventory,
5846 supply_locator_id,
5847 bom_item_type,
5848 component_code,
5849 line_id,
5850 top_item_id,
5851 effectivity_date,
5852 disable_date,
5853 assembly_item_id, /* Bug Fix: 4147224 */
5854 basis_type
5855 )
5856 select pconfigbillid,
5857 bcol2.ship_from_org_id,
5858 (bcol1.plan_level-bcol2.plan_level),
5859 '1', -- Sort Order
5860 nvl(ic1.operation_seq_num,1),
5861 decode(bcol1.config_item_id, NULL, ic1.component_item_id,bcol1.config_item_id),
5862 ic1.item_num,
5863 Round(
5864 CTO_UTILITY_PK.convert_uom( bcol1.order_quantity_uom, msi_child.primary_uom_code,
5865 bcol1.ordered_quantity , msi_child.inventory_item_id ) /
5866 CTO_UTILITY_PK.convert_uom(bcol2.order_quantity_uom, msi_parent.primary_uom_code,
5867 NVL(bcol2.ordered_quantity,1) , msi_parent.inventory_item_id ) , 7) ,
5868 ic1.component_yield_factor,
5869 ic1.component_remarks, --Bugfix 7188428
5870 ic1.attribute_category,
5871 ic1.attribute1,
5872 ic1.attribute2,
5873 ic1.attribute3,
5874 ic1.attribute4,
5875 ic1.attribute5,
5876 ic1.attribute6,
5877 ic1.attribute7,
5878 ic1.attribute8,
5879 ic1.attribute9,
5880 ic1.attribute10,
5881 ic1.attribute11,
5882 ic1.attribute12,
5883 ic1.attribute13,
5884 ic1.attribute14,
5885 ic1.attribute15,
5886 100,
5887 2,
5888 decode(bcol1.config_item_id, NULL,
5889 decode(ic1.bom_item_type,4,ic1.so_basis,2),2),
5890 1,
5891 2,
5892 decode(bcol1.config_item_id, NULL,
5893 decode(ic1.bom_item_type,4,
5894 ic1.include_in_cost_rollup, 2),1),
5895 decode(bcol1.config_item_id, NULL,
5896 decode(ic1.bom_item_type,4,
5897 ic1.check_atp, 2),2),
5898 2,
5899 2,
5900 ic1.required_for_revenue,
5901 ic1.include_on_ship_docs,
5902 ic1.include_on_bill_docs,
5903 bom_inventory_components_s.nextval,
5904 pConfigBillId,
5905 ic1.wip_supply_type,
5906 2,
5907 decode(bcol1.config_item_id, NULL, (-1)*ic1.component_sequence_id, ic1.component_sequence_id),
5908 ic1.supply_subinventory,
5909 ic1.supply_locator_id,
5910 decode(bcol1.config_item_id, NULL, ic1.bom_item_type, 4),
5911 bcol1.component_code,
5912 bcol1.line_id,
5913 ic1.bill_sequence_id,
5914 decode(
5915 greatest(ic1.effectivity_date,sysdate),
5916 ic1.effectivity_date ,
5917 ic1.effectivity_date ,
5918 sysdate ),
5919 nvl(ic1.disable_date,g_futuredate),
5920 bcol3.inventory_item_id , /* Bug Fix: 4147224 */
5921 nvl(ic1.basis_type,1)
5922 from bom_inventory_components ic1,
5923 bom_cto_order_lines_upg bcol1,
5924 bom_cto_order_lines_upg bcol2,
5925 bom_cto_order_lines_upg bcol3,
5926 mtl_system_items msi_child,
5927 mtl_system_items msi_parent
5928 where ic1.bill_sequence_id = (
5929 select common_bill_sequence_id
5930 from bom_bill_of_materials bbm
5931 where organization_id = pOrgId
5932 and alternate_bom_designator is null
5933 and assembly_item_id =(
5934 select distinct assembly_item_id
5935 from bom_bill_of_materials bbm1,
5936 bom_inventory_components bic1
5937 where bbm1.common_bill_sequence_id = bic1.bill_sequence_id
5938 and component_sequence_id = bcol1.component_sequence_id
5939 and bbm1.assembly_item_id = bcol3.inventory_item_id ))
5940 and ic1.component_item_id = bcol1.inventory_item_id
5941 and msi_child.inventory_item_id = bcol1.inventory_item_id
5942 and msi_child.organization_id = pOrgId
5943 and msi_parent.inventory_item_id = bcol2.inventory_item_id
5944 and msi_parent.organization_id = pOrgId
5945 and ic1.implementation_date is not null
5946 and ( ic1.disable_date is null or
5947 (ic1.disable_date is not null and ic1.disable_date >= sysdate ))
5948 and (( ic1.optional = 1 and ic1.bom_item_type = 4)
5949 or
5950 ( ic1.bom_item_type in (1,2)))
5951 and bcol1.ordered_quantity <> 0
5952 and bcol1.line_id <> bcol2.line_id
5953 and bcol1.parent_ato_line_id = bcol2.line_id
5954 and bcol1.parent_ato_line_id is not null
5955 and bcol1.link_to_line_id is not null
5956 and bcol2.line_id = pLineId
5957 and bcol2.ship_from_org_id = bcol1.ship_from_org_id
5958 and (bcol3.parent_ato_line_id = bcol1.parent_ato_line_id
5959 or
5960 bcol3.line_id = bcol1.parent_ato_line_id)
5961 and bcol3.line_id = bcol1.link_to_line_id;
5962
5963 lCnt := sql%rowcount ;
5964
5965 WriteToLog('Inherit_op_seq_ml:Inserted in BE Temp ' || lCnt ||' Option item/Option class rows with bill seq id as '|| pConfigBillId, 3);
5966
5967
5968
5969
5970
5971
5972
5973 /* 04-04-2005 begin zero qty check */
5974
5975 select /*+ INDEX ( BOM_EXPLOSION_TEMP BOM_EXPLOSION_TEMP_N11) */
5976 count(*) into v_zero_qty_count from bom_explosion_temp
5977 where bill_sequence_id = pConfigBillId and component_quantity = 0 ;
5978
5979 WriteToLog( 'MODELS: CHECK Raise Exception for Zero QTY Count ' || v_zero_qty_count , 1 ) ;
5980
5981 if( v_zero_qty_count > 0 ) then
5982
5983 WriteToLog( 'Inherit_op_seq_ml:: SHOULD Raise Exception for Zero QTY Count ' || v_zero_qty_count , 1 ) ;
5984
5985
5986 select concatenated_segments into v_model_item_name
5987 from mtl_system_items_kfv
5988 where inventory_item_id = pModelId
5989 and rownum = 1 ;
5990
5991
5992 l_token1(1).token_name := 'MODEL_NAME';
5993 l_token1(1).token_value := v_model_item_name ;
5994
5995
5996 cto_msg_pub.cto_message('BOM','CTO_ZERO_BOM_COMP' , l_token1 );
5997
5998 raise fnd_api.g_exc_error;
5999
6000
6001
6002
6003 end if ;
6004
6005
6006 /* 04-04-2005 end zero qty check */
6007
6008
6009
6010 /* Effectivity Dates changes */
6011 /* moved mandatory comps code */
6012 lStmtNumber := 510;
6013
6014 /*Insert Incl. items under Base Model */
6015
6016 INSERT INTO bom_explosion_temp
6017 (
6018 top_bill_sequence_id,
6019 organization_id,
6020 plan_level,
6021 sort_order,
6022 operation_seq_num,
6023 component_item_id,
6024 item_num,
6025 component_quantity,
6026 component_yield_factor,
6027 component_remarks, --Bugfix 7188428
6028 context, -- mapped to attribute_category in bic interface
6029 attribute1,
6030 attribute2,
6031 attribute3,
6032 attribute4,
6033 attribute5,
6034 attribute6,
6035 attribute7,
6036 attribute8,
6037 attribute9,
6038 attribute10,
6039 attribute11,
6040 attribute12,
6041 attribute13,
6042 attribute14,
6043 attribute15,
6044 planning_factor,
6045 select_quantity, -- mapped to quantity_related of bic interface
6046 so_basis,
6047 optional, -- mapped to optional_on_model in bic interface
6048 mutually_exclusive_options,
6049 include_in_rollup_flag, -- mapped to include_in_cost rollup of bic interface
6050 check_atp,
6051 shipping_allowed,
6052 required_to_ship,
6053 required_for_revenue,
6054 include_on_ship_docs,
6055 include_on_bill_docs,
6056 component_sequence_id,
6057 bill_sequence_id,
6058 wip_supply_type,
6059 pick_components,
6060 base_item_id, -- mapped to model_comp_seq_id of bic_interface
6061 supply_subinventory,
6062 supply_locator_id,
6063 bom_item_type,
6064 top_item_id,
6065 Effectivity_date, -- Added by Renga
6066 Disable_date -- Added by Renga -- mapped to parent_bill_seq_id in bic interface
6067 , basis_type /* LBM project */
6068 )
6069 select pConfigBillId, -- top bill sequence id
6070 bbm.organization_id, -- Model's organization_id
6071 1, -- Plan Level, should be 0+1 for model's smc's
6072 '1', -- Sort Order
6073 nvl(bic.operation_seq_num,1),
6074 bic.component_item_id,
6075 bic.item_num,
6076 bic.component_quantity component_qty,
6077 bic.component_yield_factor,
6078 bic.component_remarks, --Bugfix 7188428
6079 bic.attribute_category,
6080 bic.attribute1,
6081 bic.attribute2,
6082 bic.attribute3,
6083 bic.attribute4,
6084 bic.attribute5,
6085 bic.attribute6,
6086 bic.attribute7,
6087 bic.attribute8,
6088 bic.attribute9,
6089 bic.attribute10,
6090 bic.attribute11,
6091 bic.attribute12,
6092 bic.attribute13,
6093 bic.attribute14,
6094 bic.attribute15,
6095 100, -- planning_factor
6096 2, -- quantity_related
6097 bic.so_basis,
6098 2, -- optional
6099 2, -- mutually_exclusive_options
6100 bic.include_in_cost_rollup,
6101 bic.check_atp,
6102 2, -- shipping_allowed = NO
6103 2, -- required_to_ship = NO
6104 bic.required_for_revenue,
6105 bic.include_on_ship_docs,
6106 bic.include_on_bill_docs,
6107 bom_inventory_components_s.nextval, -- component sequence id
6108 pConfigBillId, -- bill sequence id
6109 bic.wip_supply_type,
6110 2, -- pick_components = NO
6111 (-1)*bic.component_sequence_id, -- model comp seq for later use
6112 bic.supply_subinventory,
6113 bic.supply_locator_id,
6114 bic.bom_item_type,
6115 bic.bill_sequence_id,
6116 decode( -- 3222932
6117 greatest(bic.effectivity_date,sysdate),
6118 bic.effectivity_date ,
6119 bic.effectivity_date ,
6120 sysdate ),
6121 nvl(bic.disable_date,g_futuredate) -- 3222932
6122 , nvl(bic.basis_type,1) /* LBM project */
6123 from bom_cto_order_lines_upg bcol,
6124 bom_bill_of_materials bbm,
6125 bom_inventory_components bic
6126 where bcol.line_id = pLineId
6127 and bcol.ordered_quantity <> 0
6128 -- bugfix 2389283 and instr(bcol.component_code,'-',1,1) = 0 /* To identify Top Model */
6129 and bcol.inventory_item_id = pModelId
6130 and bbm.organization_id = pOrgId
6131 and bcol.inventory_item_id = bbm.assembly_item_id
6132 and bbm.alternate_bom_designator is NULL
6133 and bbm.common_bill_sequence_id = bic.bill_sequence_id
6134 and bic.optional = 2
6135 and bic.bom_item_type = 4
6136 -- and bic.effectivity_date <= greatest( NVL(g_SchShpDate,sysdate),sysdate) /* New approach for effectivity dates */
6137 and bic.implementation_date is not null
6138 /*
6139 and NVL(bic.disable_date,NVL(g_EstRelDate, SYSDATE)+1) > NVL(g_EstRelDate,SYSDATE)
6140 and NVL(bic.disable_date,SYSDATE) >= SYSDATE;
6141 */
6142 and ( bic.disable_date is null or
6143 (bic.disable_date is not null and bic.disable_date >= sysdate )) ; /* New Approach for Effectivity Dates */
6144
6145 lCnt := sql%rowcount ;
6146
6147 IF PG_DEBUG <> 0 THEN
6148 WriteToLog ('inherit_op_seq_ml: ' || 'First -- Inserted in BE Temp ' || lCnt ||' Incl Item rows with bill seq id as '|| pConfigBillId,1);
6149 END IF;
6150
6151 lStmtNumber := 530;
6152
6153 /*+------------------------------------------------------------------------------------------------------------
6154 Open cursor c_model_oc_oi_rows(xConfigBillId) for rows inserted in bet
6155 This will update all Option Class and Option Item rows
6156 Mandatory items directly under model will already have op_seq_num. For these mandatory items we don't need to
6157 inherit the op_seq_num since they are directly under model.
6158 The component_code for these mand items are NULL as they are not in BCOL.
6159 so , mandatory item rows from bet will not be selected by c_model_oc_oi_rows cursor and will not be updated
6160 Explanation :
6161 For a Bill structure like this :
6162 55631 1.1.0 KS-ATO-MODEL1*6389
6163 55627 1.1 KS-ATO-MODEL1
6164 55628 1.1.1 KS-ATO-MODEL3
6165 55629 1.1.2 KS-ATO-OC1
6166 55630 1.1.3 KS-ATO-OI1
6167 BCOL.LINE_ID BCOL.COMP_SEQ_ID BCOL.COMPONENT_CODE
6168 ---------- ---------------- ---------------
6169 55627 21053 6280
6170 55628 21322 6280-6376
6171 55629 21303 6280-6376-6282
6172 55630 21035 6280-6376-6282-6288
6173 Now , instr( bet.component_code,'-',1,2 ) will select line_id 55629 and 55630 as those rows are actual candidates for
6174 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
6175 is directly under the top model and inheritence logic does not apply to this line.
6176 Inheritence starts from second level . First level components under top model will always have op_seq_num.
6177
6178 +------------------------------------------------------------------------------------------------------------+*/
6179
6180 FOR r1 in c_model_oc_oi_rows(pConfigBillId) LOOP
6181 IF r1.operation_seq_num = 1 AND instr(r1.component_code,'-',1,2)<>0 THEN
6182 UPDATE bom_explosion_temp bet
6183 SET bet.operation_seq_num = (
6184 SELECT /*+ INDEX( BOM_EXPLOSION_TEMP BOM_EXPLOSION_TEMP_N11) */
6185 nvl(operation_seq_num,1) -- 2433862
6186 FROM bom_explosion_temp
6187 WHERE component_code = substr(bet.component_code,1,to_number(instr(bet.component_code,'-',-1,1))-1)
6188 AND bill_sequence_id = pConfigBillId
6189 AND top_bill_sequence_id = pConfigBillId)
6190 WHERE component_code = r1.component_code
6191 AND rowid = r1.rowid;
6192 END IF;
6193 END LOOP;
6194
6195 lStmtNumber := 540;
6196
6197 /* Open cursor c_incl_items_all_level */
6198
6199 FOR r2 in c_incl_items_all_level (pOrgId ,pLineId ,pConfigBillId,g_SchShpDate,g_EstRelDate ) LOOP
6200 INSERT INTO bom_explosion_temp
6201 ( top_bill_sequence_id,
6202 organization_id,
6203 plan_level,
6204 sort_order,
6205 operation_seq_num,
6206 component_item_id,
6207 item_num,
6208 component_quantity,
6209 component_yield_factor,
6210 component_remarks, --Bugfix 7188428
6211 context, -- mapped to attribute_category in bic interface
6212 attribute1,
6213 attribute2,
6214 attribute3,
6215 attribute4,
6216 attribute5,
6217 attribute6,
6218 attribute7,
6219 attribute8,
6220 attribute9,
6221 attribute10,
6222 attribute11,
6223 attribute12,
6224 attribute13,
6225 attribute14,
6226 attribute15,
6227 planning_factor,
6228 select_quantity, -- mapped to quantity_related of bic interface
6229 so_basis,
6230 optional, -- mapped to optional_on_model of bic interface
6231 mutually_exclusive_options,
6232 include_in_rollup_flag, -- mapped to include_in_cost rollup of bic interface
6233 check_atp,
6234 shipping_allowed,
6235 required_to_ship,
6236 required_for_revenue,
6237 include_on_ship_docs,
6238 include_on_bill_docs,
6239 component_sequence_id,
6240 bill_sequence_id,
6241 wip_supply_type,
6242 pick_components,
6243 base_item_id, -- mapped to model_comp_seq_id of bic_interface
6244 supply_subinventory,
6245 supply_locator_id,
6246 bom_item_type,
6247 top_item_id, -- mapped to parent_bill_seq_id of bic interface
6248 effectivity_date, -- 3222932 /* 02-14-2005 Sushant */
6249 disable_date, -- 3222932 /* 02-14-2005 Sushant */
6250 basis_type
6251 )
6252 VALUES
6253 ( pConfigBillId, -- top bill sequence id
6254 r2.organization_id, -- Model's organization_id
6255 r2.plan_level, -- Plan Level
6256 '1', -- Sort Order
6257 DECODE(r2.operation_seq_num,1,r2.parent_op_seq_num,r2.operation_seq_num),
6258 r2.component_item_id,
6259 r2.item_num,
6260 r2.component_qty,
6261 r2.component_yield_factor,
6262 r2.component_remarks, --Bugfix 7188428
6263 r2.attribute_category,
6264 r2.attribute1,
6265 r2.attribute2,
6266 r2.attribute3,
6267 r2.attribute4,
6268 r2.attribute5,
6269 r2.attribute6,
6270 r2.attribute7,
6271 r2.attribute8,
6272 r2.attribute9,
6273 r2.attribute10,
6274 r2.attribute11,
6275 r2.attribute12,
6276 r2.attribute13,
6277 r2.attribute14,
6278 r2.attribute15,
6279 100, -- planning_factor
6280 2, -- quantity_related
6281 r2.so_basis,
6282 2, -- optional
6283 2, -- mutually_exclusive_options
6284 r2.include_in_cost_rollup,
6285 r2.check_atp,
6286 2, -- shipping_allowed = NO
6287 2, -- required_to_ship = NO
6288 r2.required_for_revenue,
6289 r2.include_on_ship_docs,
6290 r2.include_on_bill_docs,
6291 bom_inventory_components_s.nextval, -- component sequence id
6292 pConfigBillId, -- bill sequence id
6293 r2.wip_supply_type,
6294 2, -- pick_components = NO
6295 (-1)*r2.component_sequence_id, -- model comp seq for later use
6296 r2.supply_subinventory,
6297 r2.supply_locator_id,
6298 r2.bom_item_type,
6299 r2.bill_sequence_id, -- parent_bill_seq_id
6300 r2.eff_date, -- 3222932 /* 02-14-2005 Sushant */
6301 r2.dis_date, -- 3222932 /* 02-14-2005 Sushant */
6302 r2.basis_type
6303 );
6304 lCnt := sql%rowcount ;
6305 WriteToLog('Inherit_op_seq_ml:Inserted in BE Temp ' || lCnt ||' manadatory item rows with bill seq id as '|| pConfigBillId, 4);
6306 END LOOP;
6307
6308
6309 lStmtNumber := 550;
6310
6311 /*Insert into bic interface*/
6312 insert into BOM_INVENTORY_COMPS_INTERFACE
6313 ( operation_seq_num,
6314 component_item_id,
6315 last_update_date,
6316 last_updated_by,
6317 creation_date,
6318 created_by,
6319 last_update_login,
6320 item_num,
6321 component_quantity,
6322 component_yield_factor,
6323 component_remarks,
6324 effectivity_date,
6325 change_notice,
6326 implementation_date,
6327 disable_date,
6328 attribute_category,
6329 attribute1,
6330 attribute2,
6331 attribute3,
6332 attribute4,
6333 attribute5,
6334 attribute6,
6335 attribute7,
6336 attribute8,
6337 attribute9,
6338 attribute10,
6339 attribute11,
6340 attribute12,
6341 attribute13,
6342 attribute14,
6343 attribute15,
6344 planning_factor,
6345 quantity_related,
6346 so_basis,
6347 optional,
6348 mutually_exclusive_options,
6349 include_in_cost_rollup,
6350 check_atp,
6351 shipping_allowed,
6352 required_to_ship,
6353 required_for_revenue,
6354 include_on_ship_docs,
6355 include_on_bill_docs,
6356 low_quantity,
6357 high_quantity,
6358 acd_type,
6359 old_component_sequence_id,
6360 component_sequence_id,
6361 bill_sequence_id,
6362 request_id,
6363 program_application_id,
6364 program_id,
6365 program_update_date,
6366 wip_supply_type,
6367 pick_components,
6368 model_comp_seq_id,
6369 supply_subinventory,
6370 supply_locator_id,
6371 bom_item_type,
6372 revised_item_sequence_id, -- 2814257
6373 optional_on_model,
6374 plan_level,
6375 parent_bill_seq_id,
6376 assembly_item_id /* Bug Fix 4147224 */
6377 , basis_type, /* LBM changes */
6378 batch_id
6379 )
6380 select /*+ INDEX( BOM_EXPLOSION_TEMP BOM_EXPLOSION_TEMP_N11) */
6381 nvl(operation_seq_num,1), -- 2433862
6382 component_item_id,
6383 SYSDATE, -- last_updated_date
6384 1, -- last_updated_by
6385 SYSDATE, -- creation_date
6386 1, -- created_by
6387 1, -- last_update_login
6388 item_num,
6389 component_quantity,
6390 component_yield_factor,
6391 component_remarks, --Bugfix 7188428
6392 --NULL, -- component_remark
6393 -- TRUNC(SYSDATE), -- effective date
6394 effectivity_date, /* 02-14-2005 Sushant */
6395 NULL, -- change notice
6396 SYSDATE, -- implementation_date
6397 disable_date, -- disable date
6398 context, -- mapped to attribute_category in bic interface
6399 attribute1,
6400 attribute2,
6401 attribute3,
6402 attribute4,
6403 attribute5,
6404 attribute6,
6405 attribute7,
6406 attribute8,
6407 attribute9,
6408 attribute10,
6409 attribute11,
6410 attribute12,
6411 attribute13,
6412 attribute14,
6413 attribute15,
6414 planning_factor,
6415 select_quantity, -- mapped to quantity_related of bic interface
6416 so_basis,
6417 2, -- optional
6418 mutually_exclusive_options,
6419 include_in_rollup_flag, -- mapped to include_in_cost rollup of bic interface
6420 check_atp,
6421 shipping_allowed,
6422 required_to_ship,
6423 required_for_revenue,
6424 include_on_ship_docs,
6425 include_on_bill_docs,
6426 NULL, -- low_quantity
6427 NULL, -- high_quantity
6428 NULL, -- acd_type
6429 NULL, -- old_component_sequence_id
6430 component_sequence_id,
6431 bill_sequence_id,
6432 NULL, -- request_id
6433 NULL, -- program_application_id
6434 NULL, -- program_id
6435 NULL, -- program_update_date
6436 wip_supply_type,
6437 pick_components,
6438 base_item_id, -- mapped to model_comp_seq_id of bic_interface
6439 supply_subinventory,
6440 supply_locator_id,
6441 bom_item_type,
6442 line_id, -- 2814257
6443 optional,
6444 plan_level,
6445 top_item_id,
6446 assembly_item_id /* Bug Fix: 4147224 */
6447 , nvl(basis_type,1), /* LBM project */
6448 cto_msutil_pub.bom_batch_id
6449 from bom_explosion_temp
6450 where bill_sequence_id = pConfigBillId;
6451
6452 lCnt := sql%rowcount ;
6453 WriteToLog('Inherit_op_seq_ml:Inserted in BIC Interface ' || lCnt ||' rows from BET', 4);
6454 update bom_inventory_comps_interface
6455 set disable_date = g_futuredate
6456 where (component_item_id, operation_seq_num,disable_date)
6457 in ( select
6458 component_item_id, operation_seq_num,max(disable_date)
6459 from bom_inventory_comps_interface
6460 where bill_sequence_id = pConfigBillId
6461 group by component_item_id, operation_seq_num, assembly_item_id
6462 )
6463 and bill_sequence_id = pConfigBillId
6464 and disable_date <> g_futuredate ;
6465
6466 If PG_DEBUG <> 0 Then
6467 WriteToLog('Create_bom_ml: Extending the disable dates to futuure date = '||sql%rowcount,1);
6468 WriteToLog('Create_bom_ml: lconfigBillId = '||to_char(pConfigBillid),1);
6469 End if;
6470
6471
6472 /* begin Check for Overlapping Effectivity Dates */
6473 v_overlap_check := 0 ;
6474
6475 begin
6476 select 1 into v_overlap_check
6477 from dual
6478 where exists
6479 ( select * from bom_inventory_comps_interface
6480 where bill_sequence_id = pConfigBillId
6481 group by component_item_id, assembly_item_id
6482 having count(distinct operation_seq_num) > 1
6483 );
6484 exception
6485 when others then
6486 v_overlap_check := 0 ;
6487 end;
6488
6489
6490 if(v_overlap_check = 1) then
6491
6492 begin
6493 select s1.component_item_id,
6494 s1.operation_seq_num, s1.effectivity_date, s1.disable_date,
6495 s2.operation_Seq_num , s2.effectivity_date, s2.disable_date
6496 BULK COLLECT INTO
6497 v_t_overlap_comp_item_id,
6498 v_t_overlap_src_op_seq_num, v_t_overlap_src_eff_date, v_t_overlap_src_disable_date ,
6499 v_t_overlap_dest_op_seq_num , v_t_overlap_dest_eff_date, v_t_overlap_dest_disable_date
6500 from bom_inventory_comps_interface s1 , bom_inventory_comps_interface s2
6501 where s1.component_item_id = s2.component_item_id and s1.assembly_item_id = s2.assembly_item_id
6502 and s1.effectivity_date between s2.effectivity_date and s2.disable_date
6503 and s1.component_sequence_id <> s2.component_sequence_id ;
6504
6505
6506 exception
6507 when others then
6508 null ;
6509 end ;
6510
6511
6512
6513 if( v_t_overlap_src_op_seq_num.count > 0 ) then
6514 for i in v_t_overlap_src_op_seq_num.first..v_t_overlap_src_op_seq_num.last
6515 loop
6516 IF PG_DEBUG <> 0 THEN
6517 oe_debug_pub.add (' The following components have overlapping dates ', 1);
6518 oe_debug_pub.add (' COMP ' || ' OP SEQ' || 'EFFECTIVITY DT ' || ' DISABLE DT ' || ' OVERLAPS ' ||
6519 ' OP SEQ' || 'EFFECTIVITY DT ' || ' DISABLE DT ' , 1);
6520 /*
6521 oe_debug_pub.add ( v_t_overlap_comp_item_id(i) ||
6522 ' ' || v_t_overlap_src_op_seq_num(i) ||
6523 ' ' || v_t_overlap_src_eff_date(i) ||
6524 ' ' || v_t_overlap_src_disable_date(i) ||
6525 ' OVERLAPS ' ||
6526 ' ' || v_t_overlap_src_op_seq_num(i) ||
6527 ' ' || v_t_overlap_src_eff_date(i) ||
6528 ' ' || v_t_overlap_src_disable_date(i) , 1);
6529 */
6530 END IF;
6531
6532 select concatenated_segments into v_model_item_name
6533 from mtl_system_items_kfv
6534 where inventory_item_id = pModelId
6535 and rownum = 1 ;
6536
6537
6538 l_token1(2).token_name := 'MODEL';
6539 l_token1(2).token_value := v_model_item_name ;
6540
6541 cto_msg_pub.cto_message('BOM','CTO_OVERLAP_DATE_ERROR');
6542 end loop ;
6543
6544 raise fnd_api.g_exc_error;
6545
6546 end if ;
6547
6548 end if;
6549
6550
6551
6552 /* end Check for Overlapping Effectivity Dates */
6553
6554
6555
6556
6557
6558
6559
6560
6561
6562
6563 lStmtNumber := 560;
6564
6565 /*Flushing the temp table*/
6566 DELETE /*+ INDEX (BOM_EXPLOSION_TEMP BOM_EXPLOSION_TEMP_N11 ) */
6567 from bom_explosion_temp
6568 WHERE bill_sequence_id = pConfigBillId;
6569
6570 return(1);
6571
6572 EXCEPTION
6573 when no_data_found then
6574 xErrorMessage := 'CTOCBOMB:'||to_char(lStmtNumber);
6575 xMessageName := 'CTO_INHERIT_OP_SEQ_ERROR';
6576 WriteToLog('++++++++++++++++++++++++++++++++++++++++++++++++', 1);
6577 WriteToLog('ERROR: No data found error in Inherit_Op_Seq_Ml::'||to_char(lStmtNumber)||sqlerrm,1);
6578 WriteToLog('++++++++++++++++++++++++++++++++++++++++++++++++', 1);
6579 return(0);
6580 when others then
6581 xErrorMessage := 'CTOCBOMB:'||to_char(lStmtNumber)||':'||substrb(sqlerrm,1,150);
6582 xMessageName := 'CTO_INHERIT_OP_SEQ_ERROR';
6583 WriteToLog('++++++++++++++++++++++++++++++++++++++++++++++++', 1);
6584 WriteToLog('ERROR: Others error in Inherit_Op_Seq_Ml::'||to_char(lStmtNumber)||sqlerrm,1);
6585 WriteToLog('++++++++++++++++++++++++++++++++++++++++++++++++', 1);
6586 return(0);
6587 END inherit_op_seq_ml;
6588
6589
6590 /*-----------------------------------------------------------------+
6591 Name : check_bom
6592 Check to see if the BOM exists for the item in the
6593 specified org.
6594 +------------------------------------------------------------------*/
6595 FUNCTION check_bom(
6596 pItemId in number,
6597 pOrgId in number,
6598 xBillId out NOCOPY number)
6599 RETURN INTEGER
6600 IS
6601
6602
6603 BEGIN
6604
6605 xBillId := 0;
6606
6607 WriteToLog('Check_bom:Before check_bom sql::xBillId:: '||to_char(xBillId ), 5);
6608
6609 select bill_sequence_id
6610 into xBillId
6611 from bom_bill_of_materials
6612 where assembly_item_id = pItemId
6613 and organization_id = pOrgId
6614 and alternate_bom_designator is null;
6615
6616 WriteToLog('Check_bom:After check_bom sql::xBillId:: '||to_char(xBillId )||'returning 1', 5);
6617
6618 return(1);
6619
6620 EXCEPTION
6621
6622 when no_data_found then
6623 WriteToLog('BOM not found.', 4);
6624 return(0);
6625
6626 when others then
6627 WriteToLog('++++++++++++++++++++++++++++++++++++++++++++++++', 1);
6628 WriteToLog('ERROR: Others error in Check_BOM::'||sqlerrm,1);
6629 WriteToLog('++++++++++++++++++++++++++++++++++++++++++++++++', 1);
6630 return(0);
6631
6632 END CHECK_BOM;
6633
6634
6635 /*-----------------------------------------------------------------+
6636 Name : get_model_lead_time
6637 +------------------------------------------------------------------*/
6638
6639 FUNCTION get_model_lead_time
6640 ( pModelId in number,
6641 pOrgId in number,
6642 pQty in number,
6643 pLeadTime out NOCOPY number,
6644 pErrBuf out NOCOPY varchar2
6645 )
6646 RETURN INTEGER
6647
6648 IS
6649
6650 lStmtNum number;
6651
6652 begin
6653 WriteToLog('Getting Lead Time for Model: ' || to_char(pModelId), 4);
6654 lStmtNum := 100;
6655
6656 select (ceil(nvl(msi.fixed_lead_time,0)
6657 + nvl(msi.variable_lead_time,0) * pQty))
6658 into pLeadTime
6659 from mtl_system_items msi
6660 where inventory_item_id = pModelId
6661 and organization_id = pOrgId;
6662
6663 WriteToLog('Lead Time: ' || to_char(pLeadtime), 4);
6664
6665 return 1;
6666
6667 EXCEPTION
6668
6669 WHEN others THEN
6670 pErrBuf := 'CTOCBOMB: ' || lStmtNum || substrb(SQLERRM,1,150);
6671 WriteToLog('++++++++++++++++++++++++++++++++++++++++++++++++', 1);
6672 WriteToLog('ERROR: Others error in Get_Model_Lead_Time::'||to_char(lStmtNum)||sqlerrm,1);
6673 WriteToLog('++++++++++++++++++++++++++++++++++++++++++++++++', 1);
6674 return 0;
6675
6676 END get_model_lead_time;
6677
6678 /*-----------------------------------------------------------------+
6679 Name : bmlggpn_get_group_name
6680 +------------------------------------------------------------------*/
6681
6682 FUNCTION bmlggpn_get_group_name
6683 ( group_id number,
6684 group_name out nocopy varchar2,
6685 err_buf out nocopy varchar2
6686 )
6687 RETURN INTEGER
6688 is
6689 max_seg number;
6690 lStmtNum number;
6691 type segvalueType is table of varchar2(30)
6692 index by binary_integer;
6693 seg_value segvalueType;
6694 segvalue_tmp varchar2(30);
6695 segnum_tmp number;
6696 catseg_value varchar2(240);
6697 delimiter varchar2(10);
6698 profile_setting varchar2(30);
6699 CURSOR profile_check IS
6700 select nvl(substr(profile_option_value,1,30),'N')
6701 from fnd_profile_option_values val,fnd_profile_options op
6702 where op.application_id = 401
6703 and op.profile_option_name = 'USE_NAME_ICG_DESC'
6704 and val.level_id = 10001 /* This is for site level */
6705 and val.application_id = op.application_id
6706 and val.profile_option_id = op.profile_option_id;
6707 begin
6708 /* First lets get the value for profile option USE_NAME_ICG_DESC
6709 ** If this is 'N' we need to use the description
6710 ** If this is 'Y' then we need to use the group name
6711 ** We are going to stick with group name if the customer is
6712 ** not on R10.5, which means they do not have the profile
6713 ** If they have R10.5 then we are going to use description
6714 ** because that is what inventory is going to do.
6715 ** Remember at the earliest we should get rid of this function
6716 ** and call INV API. Remember we at ATO are not in the business
6717 ** of duplicating code of other teams
6718 */
6719
6720 profile_setting := 'Y';
6721
6722 lStmtNum :=250;
6723 OPEN profile_check;
6724 FETCH profile_check INTO profile_setting;
6725 IF profile_check%NOTFOUND THEN
6726 profile_setting := 'Y';
6727 END IF;
6728
6729 WriteToLog('Bmlggpn_get_group_name: use_name_icg_desc :'|| profile_setting, 5);
6730
6731 if profile_setting = 'Y' then
6732
6733 /* Let us select the catalog group name from mtl_catalog_groups
6734 ** At some point in time we need to call the inventory function
6735 ** to do this, so we can centralize this stuff
6736 */
6737 lStmtNum :=260;
6738
6739 SELECT MICGK.concatenated_segments
6740 INTO group_name
6741 FROM mtl_item_catalog_groups_kfv MICGK
6742 WHERE MICGK.item_catalog_group_id = group_id;
6743
6744 else
6745 lStmtNum :=270;
6746 /* This is to get the description of the catalog */
6747 SELECT MICG.description
6748 INTO group_name
6749 FROM mtl_item_catalog_groups MICG
6750 WHERE MICG.item_catalog_group_id = group_id;
6751
6752 end if;
6753 return(0);
6754 EXCEPTION
6755 when others then
6756 err_buf := 'CTOCBOMB: ' || lStmtNum || substrb(SQLERRM,1,150);
6757 WriteToLog('++++++++++++++++++++++++++++++++++++++++++++++++', 1);
6758 WriteToLog('ERROR: Others error in Bmlggpn_Get_Group_Name::'||to_char(lStmtNum)||sqlerrm,1);
6759 WriteToLog('++++++++++++++++++++++++++++++++++++++++++++++++', 1);
6760 return(SQLCODE);
6761 END bmlggpn_get_group_name;
6762
6763
6764 /*-----------------------------------------------------------------+
6765 Name : bmlupid_update_item_desc
6766 +------------------------------------------------------------------*/
6767
6768 FUNCTION bmlupid_update_item_desc
6769 (
6770 item_id NUMBER,
6771 org_id NUMBER,
6772 err_buf out nocopy VARCHAR2
6773 )
6774 RETURN INTEGER
6775 IS
6776 /*
6777 ** Create cursor to retrieve all descriptive element values for the item
6778 */
6779 CURSOR cc is
6780 select element_value
6781 from mtl_descr_element_values
6782 where inventory_item_id = item_id
6783 and element_value is not NULL
6784 and default_element_flag = 'Y'
6785 order by element_sequence;
6786
6787 delimiter varchar2(10);
6788 e_value varchar2(30);
6789 cat_value varchar2(240);
6790 idx number;
6791 group_id number;
6792 group_name varchar2(240); -- bugfix 2483982: increased the size from 30 to 240
6793 lStmtNum number;
6794 status number;
6795 INV_GRP_ERROR exception;
6796 begin
6797 lStmtNum := 280;
6798 WriteToLog('bmlupid_update_item_desc: ' || ' In bmlupid_update_item_desc ',2);
6799
6800 select concatenated_segment_delimiter into delimiter
6801 from fnd_id_flex_structures
6802 where id_flex_code = 'MICG'
6803 and application_id = 401;
6804
6805 lStmtNum := 285;
6806 select item_catalog_group_id into group_id
6807 from mtl_system_items
6808 where inventory_item_id = item_id
6809 and organization_id = org_id;
6810
6811 WriteToLog('Bmlupid_update_item_desc:item_catalog_group_id : ' || group_id, 4);
6812
6813 idx := 0;
6814 cat_value := '';
6815 open cc;
6816 loop
6817 fetch cc into e_value;
6818 exit when (cc%notfound);
6819
6820 if idx = 0 then
6821 lStmtNum := 290;
6822 status := bmlggpn_get_group_name(group_id,group_name,
6823 err_buf);
6824 if status <> 0 then
6825 raise INV_GRP_ERROR;
6826 end if;
6827 cat_value := group_name || delimiter || e_value;
6828 else
6829 lStmtNum := 295;
6830 cat_value := cat_value || SUBSTRB(delimiter || e_value,1,
6831 240-LENGTHB(cat_value));
6832 end if;
6833 WriteToLog('Bmlupid_update_item_desc:cat_value :' || cat_value, 4);
6834 idx := idx + 1;
6835 end loop;
6836 close cc;
6837
6838 if idx <> 0 then
6839 update mtl_system_items
6840 set description = cat_value
6841 where inventory_item_id = item_id;
6842 /*and organization_id = org_id; Bugfix 2163311 */
6843 /* start bugfix 1845141 */
6844 update mtl_system_items_tl
6845 set description = cat_value
6846 where inventory_item_id = item_id;
6847 /*and organization_id = org_id; Bugfix 2163311 */
6848 /* end bugfix 1845141 */
6849 end if;
6850
6851 return(0);
6852
6853 EXCEPTION
6854 when INV_GRP_ERROR then
6855 err_buf := 'CTOCBOMB: Invalid catalog group for the item ' || item_id || ' status:' || status;
6856 WriteToLog('++++++++++++++++++++++++++++++++++++++++++++++++', 1);
6857 WriteToLog('ERROR: Invalid catalog group for the item::'||to_char(lStmtNum)||sqlerrm,1);
6858 WriteToLog('++++++++++++++++++++++++++++++++++++++++++++++++', 1);
6859 return(1);
6860
6861 when OTHERS then
6862 err_buf := 'CTOCBOMB: ' || lStmtNum ||substrb(SQLERRM,1,150);
6863 WriteToLog('++++++++++++++++++++++++++++++++++++++++++++++++', 1);
6864 WriteToLog('ERROR: Others error in Bmlupid_Update_Item_Desc::'||to_char(lStmtNum)||sqlerrm,1);
6865 WriteToLog('++++++++++++++++++++++++++++++++++++++++++++++++', 1);
6866 return(1);
6867
6868 END bmlupid_update_item_desc;
6869
6870
6871 /*-------------------------------------------------+
6872 check_routing :
6873 Checks the existence of routing of an assembly
6874 in an org. If routing exists, returns 1 and
6875 otherwise returns 0
6876 +-------------------------------------------------*/
6877
6878 FUNCTION check_routing (
6879 pItemId in number,
6880 pOrgId in number,
6881 xRtgId out nocopy number,
6882 xRtgType out nocopy number)
6883 RETURN INTEGER
6884 IS
6885
6886
6887 BEGIN
6888
6889 xRtgId := 0;
6890 xRtgType := 0;
6891
6892 select routing_sequence_id,
6893 NVL(cfm_routing_flag,2)
6894 into xRtgId,
6895 xRtgType
6896 from bom_operational_routings
6897 where assembly_item_id = pItemId
6898 and organization_id = pOrgId
6899 and alternate_routing_designator is null;
6900
6901 return (1);
6902
6903 EXCEPTION
6904
6905 when no_data_found then
6906 WriteToLog('Routing does not exist.', 4);
6907 return (0) ;
6908
6909 when others then
6910 WriteToLog('++++++++++++++++++++++++++++++++++++++++++++++++', 1);
6911 WriteToLog('ERROR: Others error in Check_Routing::'||sqlerrm,1);
6912 WriteToLog('++++++++++++++++++++++++++++++++++++++++++++++++', 1);
6913 return (0) ;
6914
6915 END check_routing;
6916
6917 END CTO_UPDATE_BOM_RTG_PK;