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