[Home] [Help]
PACKAGE BODY: APPS.CTO_CONFIG_ROUTING_PK
Source
1 package body CTO_CONFIG_ROUTING_PK as
2 /* $Header: CTOCRTGB.pls 120.5.12000000.2 2007/07/27 07:25:55 abhissri ship $ */
3
4 /*============================================================================+
5 | Copyright (c) 1993 Oracle Corporation Belmont, California, USA |
6 | All rights reserved. |
7 | Oracle Manufacturing |
8 +=============================================================================+
9 |
10 | FILE NAME : CTOCRTGB.sql
11 | DESCRIPTION :
12 | This file creates a packaged function that loads the
13 | Routing tables for the config item. Converted from BOMLDCBB.pls
14 | for CTO streamline for new OE
15 |
16 | HISTORY : created 10-JUN-99
17 | Added changes to support WIP's Simultaneous and Substitute
18 | resources 19-JUN-2000 Sajani Sheth
19 |
20 | ksarkar 12-JUN-01 Bugfix 1653881
21 | single row subquery of ic1.component_sequence_id returns more than
22 | one rows when same component is used more than once in a given
23 | assembly.
24 | We do not need to select component_sequence_id through a subquery.
25 | The bill_sequence_id of the option class and join condition
26 | ic1.component_item_id = bcol1.inventory_item_id will select unique components
27 | for the option class.
28 |
29 | ksarkar 01-JUN-01 Bugfix 1812159
30 | Date operations make a disabled item effective and increases the
31 | component usage in configured item.
32 |
33 | ksarkar 19-JUL-01 Bugfix 1876999
34 | Remove semicolon from comment to improve performance .
35 | ksarkar 17-AUG-01 Bugfix 1906371
36 | Unique constraint on bom_operation_sequences will be violated
37 | when config item has selected options with same op seq number.
38 | Refer bug for details
39 |
40 | ksarkar 17-AUG-01 Bugfix 1935580
41 | Unique constraint on bom_operation_sequences will be violated
42 | hen config item has selected models with same op seq number.
43 | Refer bug for details
44 | Add bom_bill_of_materials.organization_id = pOrgId condition
45 | to select routing from correct organisation.
46 |
47 | Combining 1935580 and 1906371 ; since option rows are inserted into
48 | bom_operation_sequences after model rows , check needs to be there
49 | while inserting option rows to see whether combination of
50 | operation_seq_num - operation_type - routing_sequence_id - effectivity_date
51 | is already present in the table for the model rows .
52 |
53 | Modified on 24-AUG-2001 by Sushant Sawant: BUG #1957336
54 | Added a new functionality for preconfigure bom.
55 |
56 | Combining 1935580 and 1906371 ; check for null values of operation_type
57 | in the cursor get_op_seq_num.
58 |
59 | ksarkar 07-SEP-01 Bugfix 1974130 and Bug 1983384
60 | Config item is created with improper routing
61 |
62 | ksarkar 09-JAN-02 Bugfix 2177101 ( Bugfix 2143014 )
63 | Create config item process is failed with unique constraint violation.
64 |
65 | ksarkar 04-APR-02 Bugfix 2292468 ( Bugfix 1912376 )
66 | Checking item effectivity till schedule_ship_date.
67 |
68 | ssawant 28-MAY-02 Bugfix 2312199 (Refix for bug1912376 )
69 | bug 1912376 could still fail in case of sourced lower level models
70 |
71 | ksarkar 04-JUN-02 Bugfix 2382396 ( Bugfix 2402935 in main )
72 | Multi level model not selecting option dependent routing steps
73 | on non-phantom sub-model
74 |
75 | kkonada 31-OCT-2002
76 | Made changes for changes for feature SERIAL TRACKING IN WIP
77 |
78 |
79 | ksarkar 05-NOV-02 Bugfix 2652844 ( Customer bug 2650828 )
80 | Time lag in copying routing information to configured item
81 | routing.
82 |
83 |
84 | kkonada 06-FEb-2003 Bugfix 2771065
85 | Disbled routing op was getting picked up when
86 | disabled date was greater than Est released date
87 | Est rel date is assumed to be begining of the day
88 | ie midnight of each day
89 | FIX
90 | added a where clasue
91 | nvl(disable_date,sysdate+1)>sysdate
92 |
93 |
94 | ksarkar 14-MAY-03 Bugfix 2958044 ( Customer bug 2950774 )
95 | Config item routing not copying set up type from its base model
96 | routing.
97 |
98 |
99 | ksarkar 26-SEP-03 Bugfix 3093686 ( Customer bug 3093686 )
100 | Autocreate process adding extra routing steps to configuration
101 | routing.
102 |
103 | ksarkar 09-OCT-03 Bugfix 3180827 ( Customer bug 3144822 )
104 | Autocreate process not picking up routing steps to configuration
105 | routing.
106 |
107 |
108 | Modified on 26-Mar-2004 By Sushant Sawant
109 | Fixed Bug#3484511
110 | all queries referencing oe_system_parameters_all
111 | should be replaced with a function call to oe_sys_parameters.value
112 |
113 | Renga Kannan 28-Jan-2004 Front Port bug fix 4049807
114 | Descriptive Flexfield Attribute
115 | category is not copied from model
116 | Added this column while inserting
117 | into bom_operational_routings
118 |
119 | Kiran Konada 05-Jan-2006 bugfix1765149
120 | get the x and Y coordinate on canvas for flow routing
121 *============================================================================*/
122
123 -- Bug 1912376 Declaring Global variable to hold the value of Schedule Ship Date
124 g_SchShpDate Date;
125
126 -- Bug 3180827 Declaring Global variable to hold the value of Last update Date
127 glast_update_date Date := to_date('01/01/2099 00:00:00','MM/DD/YYYY HH24:MI:SS');
128
129 /*-------------------------------------------------+
130 check_routing :
131 Checks the existence of routing of an assembly
132 in an org. If routing exists, returns 1 and
133 otherwise returns 0
134 +-------------------------------------------------*/
135
136 PG_DEBUG Number := NVL(FND_PROFILE.value('ONT_DEBUG_LEVEL'), 0);
137
138 function check_routing (
139 pItemId in number,
140 pOrgId in number,
141 xRtgId out NOCOPY number,
142 xRtgType out NOCOPY number)
143 return integer
144 is
145
146
147 begin
148
149 xRtgId := 0;
150 xRtgType := 0;
151
152 select routing_sequence_id,
153 NVL(cfm_routing_flag,2)
154 into xRtgId,
155 xRtgType
156 from bom_operational_routings
157 where assembly_item_id = pItemId
158 and organization_id = pOrgId
159 and alternate_routing_designator is null;
160
161 return (1);
162
163 exception
164
165 when no_data_found then
166 return (0) ;
167
168 when others then
169 IF PG_DEBUG <> 0 THEN
170 oe_debug_pub.add ('check_routing: ' || 'Others exception for Check RTG::item id '||to_char(pItemId),1);
171
172 oe_debug_pub.add ('check_routing: ' || 'Error message is : '||sqlerrm);
173 END IF;
174 return (0) ;
175
176 end check_routing;
177
178 /*-----------------------------------------------------+
179 Create_routing_ml :
180 Creates config routing and poplulates associated
181 tables based on model item in mfg org. Needs config
182 bill id as in parameter because the bill does not
183 exists in production tables yet.
184
185 returns 1 when succesful, 0 when failure.
186 +-----------------------------------------------------*/
187
188
189
190 FUNCTION create_routing_ml (
191 pModelId in number,
192 pConfigId in number,
193 pCfgBillId in number,
194 pOrgId in number,
195 pLineId in number,
196 pFlowCalc in number,
197 xRtgId out NOCOPY number,
198 xErrorMessage out NOCOPY varchar2,
199 xMessageName out NOCOPY varchar2,
200 xTableName out NOCOPY varchar2)
201 RETURN integer
202 is
203
204 l_ser_start_op number;
205 l_ser_code number;
206 l_row_count number := 0;
207
208
209 lStmtNum number;
210 lItmRtgId number;
211 lStatus number;
212 lCfgRtgId number;
213 lCfmRtgflag number;
214 lEstRelDate date;
215 l_status VARCHAR2(1);
216 l_industry VARCHAR2(1);
217 l_schema VARCHAR2(30);
218 lLineId number;
219 lModelId number;
220 lParentAtoLineId number := pLineId;
221 lOrderedQty number;
222 lLeadTime number;
223 lErrBuf varchar2(80);
224 lTotLeadTime number := 0;
225 lOEValidationOrg number;
226
227 /*New variables added for bugfix 1906371 and 1935580*/
228 lmodseqnum number;
229 lmodtyp number;
230 lmodrtgseqid number;
231 lmodnewCfgRtgId number;
232 lopseqnum number;
233 loptyp number;
234 lrtgseqid number;
235 lnewCfgRtgId number;
236
237 l_test number;
238
239 /*End of variable addition*/
240
241 l_install_cfm BOOLEAN;
242
243 UP_DESC_ERR exception;
244
245 /* ------------------------------------------------------+
246 cursor to be used to copy attachments for all
247 operations fro model to operations on config
248 requset id column contains model_op_seq_id.
249 +--------------------------------------------------------*/
250
251 cursor allops is
252 select operation_sequence_id, request_id
253 from bom_operation_sequences
254 where routing_sequence_id = lCfgRtgId;
255
256 /* ------------------------------------------------------+
257 cursor added for bugfix 1906371 and 1935580 to select
258 distinct combinations of op_seq_num and op_type
259 +--------------------------------------------------------*/
260
261 cursor get_op_seq_num (pRtgId number) is
262 select distinct operation_seq_num,nvl(operation_type,1)
263 from bom_operation_sequences_temp -- 5336292
264 where config_routing_id=pRtgId; -- bugfix 3239267: replaced last_update_login
265
266 -- 5336292
267 cursor bos_temp (pRtgId number ) is
268 select operation_sequence_id,routing_sequence_id,operation_seq_num,config_routing_id
269 from bom_operation_sequences_temp
270 where config_routing_id=pRtgId;
271
272 d_op_seq_id bom_operation_sequences_temp.operation_sequence_id%TYPE;
273 d_rtg_seq_id bom_operation_sequences_temp.routing_sequence_id%TYPE;
274 d_op_seq_num bom_operation_sequences_temp.operation_seq_num%TYPE;
275 d_cfg_rtg_id bom_operation_sequences_temp.config_routing_id%TYPE;
276
277
278 v_program_id bom_cto_order_lines.program_id%type;
279
280 -- 3093686 : structure declaration
281
282 TYPE mod_opclass_rtg_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
283
284
285 tModOpClassRtg mod_opclass_rtg_tab;
286 tDistinctRtgSeq mod_opclass_rtg_tab;
287 lexists varchar2(1);
288 k number;
289
290
291 -- 3093686
292
293 --4905857
294 l_batch_id number;
295 BEGIN
296
297 gUserId := nvl(Fnd_Global.USER_ID, -1);
298 gLoginId := nvl(Fnd_Global.LOGIN_ID, -1);
299 xRtgID := 0;
300
301
302 /*-----------------------------------------------------------+
303 If the routing already exists for this config item then
304 we do not need to create the routing. Return with success.
305 +------------------------------------------------------------*/
306
307 lStatus := check_routing (pConfigId,
308 pOrgId,
309 lItmRtgId,
310 lCfmRtgFlag );
311 if lStatus = 1 then
312 IF PG_DEBUG <> 0 THEN
313 oe_debug_pub.add ('create_routing_ml: ' || 'Config Routing' || lCfgRtgId || ' Already Exists ',1);
314 END IF;
315 return (1) ;
316 end if;
317
318 /*-------------------------------------------------------------+
319 Config does not have routing. If model also does not have
320 routing, we do not need to do anything, return with success.
321 +--------------------------------------------------------------*/
322
323 lCfmRtgFlag := NULL;
324 lStatus := check_routing (pModelId,
325 pOrgId,
326 lItmRtgId,
327 lCfmRtgFlag);
328 if lStatus <> 1 then
329 IF PG_DEBUG <> 0 THEN
330 oe_debug_pub.add ('create_routing_ml: ' || 'Model Does not have a routing ',1);
331
332 END IF;
333 return (1);
334 end if;
335
336 /* --------------------------------------------------------------+
337 Routing needs to be Created for the config item.
338 +---------------------------------------------------------------*/
339 -- Start Bugfix 1912376
340
341 /*-------------------------------------------+
342 Selecting Schedule_ship_date of ATO Model and assigning
343 this to a Global variable
344 +--------------------------------------------*/
345 lStmtNum := 05;
346
347 select nvl(schedule_ship_date,sysdate)
348 into g_SchShpDate
349 from bom_cto_order_lines
350 where line_id = pLineId ;
351 -- and ship_from_org_id = pOrgId ** bugfix 2312199 **
352 /* commented line as part of bugfix 2312199, the bug 1912376 was not fixed
353 ** properly, the bugfix will not work in case of sourced lower level models
354 ** and hence this line needs to be commented as part of bug 2312199
355 */
356
357 IF PG_DEBUG <> 0 THEN
358 oe_debug_pub.add('create_routing_ml: ' || 'Line Id ' || pLineId ||' has Schedule Ship Date of '||g_SchShpDate, 1);
359 oe_debug_pub.add (' org id = '||to_char(porgid),1);
360 END IF;
361
362 -- End Bugfix 1912376
363
364
365
366 /*--------------------------------------------------------------+
367 Replacing Calculate Estimated Release date of the model
368 with Multilevel version which will sum the lead time
369 of all parent ATO models.
370 +---------------------------------------------------------------*/
371
372 /**** comment begins --------------------------------------------------------+
373
374 xTableName := 'OE_ORDER_LINES_ALL';
375 lStmtNum := 10;
376
377 select CAL.CALENDAR_DATE
378 into lEstRelDate
379 from bom_calendar_dates cal,
380 mtl_system_items msi,
381 bom_cto_order_lines bcol,
382 mtl_parameters mp
383 where msi.organization_id = pOrgId
384 and msi.inventory_item_id = pModelId
385 and bcol.line_id = pLineId
386 and bcol.inventory_item_id = msi.inventory_item_id
387 and mp.organization_id = msi.organization_id
388 and cal.calendar_code = mp.calendar_code
389 and cal.exception_set_id = mp.calendar_exception_set_id
390 and cal.seq_num =
391 (select cal2.prior_seq_num
392 - (ceil(nvl(msi.fixed_lead_time,0)
393 + nvl(msi.variable_lead_time,0) * bcol.ordered_quantity))
394 from bom_calendar_dates cal2
395 where cal2.calendar_code = mp.calendar_code
396 and cal2.exception_set_id = mp.calendar_exception_set_id
397 and cal2.calendar_date = trunc(bcol.schedule_ship_date));
398
399 +- comment ends ----------------------------------------------------------*/
400
401
402
403
404 -- New Estimated Release Date for Multilevel ATO
405 -- get oevalidation org
406
407 lStmtNum := 10;
408 IF PG_DEBUG <> 0 THEN
409 oe_debug_pub.add('create_routing_ml: ' || 'Before getting validation org',2);
410 END IF;
411
412 -- BUG #1957336 Change for preconfigure bom by Sushant Sawant
413 -- Sushant added this code to check bcol records populated by preconfigure bom module
414
415 select program_id
416 into v_program_id
417 from bom_cto_order_lines
418 where line_id = pLineId ;
419
420
421
422 if( v_program_id = CTO_UTILITY_PK.PC_BOM_PROGRAM_ID ) then
423
424 lOEValidationOrg := CTO_UTILITY_PK.PC_BOM_VALIDATION_ORG ;
425
426 else
427
428 /*
429 BUG:3484511
430 -----------
431 select nvl(master_organization_id,-99) --bugfix 2646849: master_organization_id can be 0
432 into lOEValidationOrg
433 from oe_order_lines_all oel,
434 oe_system_parameters_all ospa
435 where oel.line_id = pLineid
436 and nvl(oel.org_id, -1) = nvl(ospa.org_id, -1) --bug 1531691
437 and oel.inventory_item_id = pModelId;
438 */
439
440 IF PG_DEBUG <> 0 THEN
441 oe_debug_pub.add('create_routing_ml: ' || 'Going to fetch Validation Org ' ,2);
442 END IF;
443
444
445 select nvl( oe_sys_parameters.value( 'MASTER_ORGANIZATION_ID' , oel.org_id) , -99)
446 into lOEValidationOrg from oe_order_lines_all oel
447 where oel.line_id = pLineId ;
448
449
450 end if ;
451
452
453 if (lOEValidationOrg = -99) then -- bugfix 2646849
454 cto_msg_pub.cto_message('BOM','CTO_VALIDATION_ORG_NOT_SET');
455 raise FND_API.G_EXC_ERROR;
456 end if;
457
458 IF PG_DEBUG <> 0 THEN
459 oe_debug_pub.add('create_routing_ml: ' || 'Validation Org is :' || lOEValidationOrg,2);
460 END IF;
461
462
463 lStmtNum := 11;
464
465 loop
466 select bcol.line_id, bcol.inventory_item_id, bcol.parent_ato_line_id,
467 bcol.ordered_quantity
468 into lLineId, lModelId, lParentAtoLineId, lOrderedQty
469 from bom_cto_order_lines bcol
470 where bcol.line_id = lParentAtoLineId;
471
472 IF PG_DEBUG <> 0 THEN
473 oe_debug_pub.add('create_routing_ml: ' || 'lLineId: ' || to_char(lLineId), 2);
474
475 oe_debug_pub.add('create_routing_ml: ' || 'lModelId: ' || to_char(lModelId), 2);
476
477 oe_debug_pub.add('create_routing_ml: ' || 'lParentAtoLineId: ' || to_char(lParentAtoLineId), 2);
478 END IF;
479
480 lStmtNum := 12;
481 lStatus := CTO_CONFIG_BOM_PK.get_model_lead_time(
482 pModelId => lModelId,
483 pOrgId => lOEValidationOrg,
484 pQty => lOrderedQty,
485 pLeadTime => lLeadTime,
486 pErrBuf => lErrBuf);
487
488 if (lStatus = 0) then
489 IF PG_DEBUG <> 0 THEN
490 oe_debug_pub.add('create_routing_ml: ' || 'Failed in get_model_lead_time.', 1);
491 END IF;
492 return 0;
493
494 else
495 lTotLeadTime := lLeadTime + lTotLeadTime;
496
497 end if;
498
499 exit when lLineId = lParentAtoLineId; -- when we reach the top model
500
501 end loop;
502
503 IF PG_DEBUG <> 0 THEN
504 oe_debug_pub.add('create_routing_ml: ' || 'Total lead time is: ' || to_char(lTotLeadTime), 2);
505 END IF;
506
507 xTableName := 'OE_ORDER_LINES ';
508 lStmtNum := 13;
509
510 select CAL.CALENDAR_DATE
511 into lEstRelDate
512 from bom_calendar_dates cal,
513 mtl_system_items msi,
514 bom_cto_order_lines bcol,
515 mtl_parameters mp
516 where msi.organization_id = pOrgId
517 and msi.inventory_item_id = pModelId
518 and bcol.line_id = pLineId
519 and bcol.inventory_item_id = msi.inventory_item_id
520 -- and bcol.ship_from_org_id = msi.organization_id
521 and mp.organization_id = msi.organization_id
522 and cal.calendar_code = mp.calendar_code
523 and cal.exception_set_id = mp.calendar_exception_set_id
524 and cal.seq_num =
525 (select cal2.prior_seq_num - lTotLeadTime
526 from bom_calendar_dates cal2
527 where cal2.calendar_code = mp.calendar_code
528 and cal2.exception_set_id = mp.calendar_exception_set_id
529 and cal2.calendar_date = trunc(bcol.schedule_ship_date));
530
531 IF PG_DEBUG <> 0 THEN
532 oe_debug_pub.add ('create_routing_ml: ' || 'Estimated Release Date is : ' || to_char(lEstRelDate,'dd-mon-yy::hh24:mi:ss'), 2);
533 END IF;
534
535
536 /*---------------------------------------------------------------+
537 Get new routing_id and the type of routing to be created.
538 +----------------------------------------------------------------*/
539
540 select bom_operational_routings_s.nextval
541 into lCfgRtgId
542 from dual;
543
544
545 /*---------------------------------------------------------------+
546 Insert the routing header for the Config Item
547 +----------------------------------------------------------------*/
548
549 xTableName := 'BOM_OPERATIONAL_ROUTING';
550 lStmtNum := 30;
551
552
553 IF PG_DEBUG <> 0 THEN
554 oe_debug_pub.add ('create_routing_ml: ' || 'Inserting the routing header information into bom_operational_routings..',5);
555 END IF;
556 insert into bom_operational_routings
557 (
558 routing_sequence_id,
559 assembly_item_id,
560 organization_id,
561 alternate_routing_designator,
562 last_update_date,
563 last_updated_by,
564 creation_date,
565 created_by,
566 last_update_login,
567 routing_type,
568 common_routing_sequence_id,
569 common_assembly_item_id,
570 routing_comment,
571 completion_subinventory,
572 completion_locator_id,
573 attribute_category,
574 attribute1,
575 attribute2,
576 attribute3,
577 attribute4,
578 attribute5,
579 attribute6,
580 attribute7,
581 attribute8,
582 attribute9,
583 attribute10,
584 attribute11,
585 attribute12,
586 attribute13,
587 attribute14,
588 attribute15,
589 request_id,
590 program_application_id,
591 program_id,
592 program_update_date,
593 line_id,
594 mixed_model_map_flag,
595 priority,
596 cfm_routing_flag,
597 total_product_cycle_time,
598 ctp_flag,
599 project_id,
600 task_id
601 )
602 select
603 lCfgRtgId, -- Routing Sequence Id
604 pConfigId, -- assembly item Id
605 pOrgId, -- Organization Id
606 null, -- alternate routing designator
607 sysdate, -- last update date
608 gUserID, -- last updated by
609 sysdate,
610 gUserId, /* created_by */
611 gLoginId, /* last_update_login */
612 bor.routing_type, /* routing_type */
613 lCfgRtgId, /* common_routing_sequence_id */
614 null, /* common_assembly_item_id */
615 bor.routing_comment,
616 bor.completion_subinventory,
617 bor.completion_locator_id,
618 bor.attribute_category, -- Bug Fix 4049807 added Attribute category
619 bor.attribute1,
620 bor.attribute2,
621 bor.attribute3,
622 bor.attribute4,
623 bor.attribute5,
624 bor.attribute6,
625 bor.attribute7,
626 bor.attribute8,
627 bor.attribute9,
628 bor.attribute10,
629 bor.attribute11,
630 bor.attribute12,
631 bor.attribute13,
632 bor.attribute14,
633 bor.attribute15,
634 null,
635 null,
636 null,
637 null,
638 bor.line_id,
639 bor.mixed_model_map_flag,
640 bor.priority,
641 bor.cfm_routing_flag,
642 bor.total_product_cycle_time,
643 bor.ctp_flag,
644 bor.project_id,
645 bor.task_id
646 from
647 bom_operational_routings bor,
648 mtl_parameters mp
649 where bor.assembly_item_id = pModelId
650 and bor.organization_id = pOrgId
651 and bor.alternate_routing_designator is null
652 and mp.organization_id = pOrgId;
653
654 IF PG_DEBUG <> 0 THEN
655 oe_debug_pub.add ('create_routing_ml: ' || 'Inserted Routing Header :' || lCfgRtgId,2 );
656 END IF;
657
658 /*---------------------------------------------------------------+
659 Udpate the mixed_model_map_flag. If the cfm_routing_flag
660 is 1, then mixed_model_flag should be 1 if any flow_routing
661 (primary or alternate) for the model has the mixed_model_flag
662 equal to 1.
663 +----------------------------------------------------------------*/
664
665 lStmtNum := 40;
666
667 update bom_operational_routings b
668 set mixed_model_map_flag =
669 ( select 1
670 from bom_operational_routings bor
671 where bor.assembly_item_id = pModelId
672 and bor.organization_id = pOrgId
673 and bor.cfm_routing_flag = 1
674 and bor.mixed_model_map_flag = 1
675 and bor.alternate_routing_designator is not NULL )
676 where b.routing_sequence_id = lCfgRtgID
677 and b.mixed_model_map_flag <> 1
678 and b.cfm_routing_flag =1;
679
680
681
682 /*---------------------------------------------------------------+
683 -- rtg2
684
685 Identify all distinct operation steps to be picked up from
686 Model routing and mark the config_routing_id field -- bugfix 3239267: replaced last_update_login
687 for those to lCfgRtgId.
688 Ignore option dependednt flag on operations types 2 and 3
689 Copy from Model Item's routing only.
690 -- Mandatory steps model
691 -- option dependent steps associated with options/option Class
692 -- "additional" option dependent steps associated with options/OC
693 -- Option dependent steps associated with mandatory comps.
694 -- "additional" Option dependent steps associated with mandatory comps.
695 The "additional" operation steps are the steps stored in the new
696 table bom_component_operations to support one-to-many BOM components
697 to Routing steps.
698 +----------------------------------------------------------------*/
699
700 --perf bug#4905857 , sql id 16103149
701 --Fixed Performance bug in the following sql. AS the following sql is huge one,
702 --performance team asked us to divide the sql into pieces. We are planning to
703 --insert a record from each sub query in the union class and then update it from
704 --interface table
705
706 /********************************************************************
707 NEW GFSI CODE 5336292
708 ********************************************************************/
709
710 -- Insert eligible rows into bom_operation_sequences_temp
711 -- Note : We are not selecting distinct of ( operation_seq_num,operation_type,routing_sequence_id )
712 -- since bom_operation_sequences has operation_seq_num,operation_type,routing_sequence_id,eff_date
713 -- as unique combination key and eff_date is used in the where clause of select
714
715
716
717 lStmtNum := 50;
718 l_batch_id := bom_import_pub.get_batchid;
719
720 --1st insert
721 lStmtNum := 51;
722 insert into bom_operation_sequences_temp
723 (
724 operation_sequence_id,
725 routing_sequence_id,
726 config_routing_id, -- 5336292
727 operation_seq_num,
728 last_update_date,
729 last_updated_by,
730 creation_date,
731 created_by,
732 last_update_login,
733 standard_operation_id,
734 department_id ,
735 operation_lead_time_percent,
736 minimum_transfer_quantity,
737 count_point_type ,
738 operation_description,
739 effectivity_date,
740 disable_date ,
741 backflush_flag,
742 option_dependent_flag,
743 attribute_category ,
744 attribute1,
745 attribute2,
746 attribute3,
747 attribute4,
748 attribute5,
749 attribute6,
750 attribute7,
751 attribute8,
752 attribute9,
753 attribute10,
754 attribute11,
755 attribute12,
756 attribute13,
757 attribute14,
758 attribute15,
759 request_id,
760 program_application_id,
761 program_id ,
762 program_update_date,
763 reference_flag,
764 operation_type,
765 process_op_seq_id,
766 line_op_seq_id,
767 yield,
768 cumulative_yield,
769 reverse_cumulative_yield,
770 labor_time_calc,
771 machine_time_calc,
772 total_time_calc,
773 labor_time_user,
774 machine_time_user,
775 total_time_user,
776 net_planning_percent,
777 implementation_date,
778 x_coordinate,
779 y_coordinate
780 )
781 SELECT distinct
782 os1.operation_sequence_id, -- 5336292
783 os1.routing_sequence_id, -- 5336292
784 lCfgRtgId, -- 5336292
785 os1.operation_seq_num,
786 glast_update_date, -- 5336292
787 gUserId,
788 sysdate,
789 gUserId,
790 gLoginId,
791 os1.standard_operation_id,
792 os1.department_id,
793 os1.operation_lead_time_percent,
794 os1.minimum_transfer_quantity,
795 os1.count_point_type,
796 os1.operation_description,
797 os1.effectivity_date, -- 5336292
798 os1.disable_date, -- 5336292
799 os1.backflush_flag,
800 os1.option_dependent_flag, -- 5336292
801 os1.attribute_category,
802 os1.attribute1,
803 os1.attribute2,
804 os1.attribute3,
805 os1.attribute4,
806 os1.attribute5,
807 os1.attribute6,
808 os1.attribute7,
809 os1.attribute8,
810 os1.attribute9,
811 os1.attribute10,
812 os1.attribute11,
813 os1.attribute12,
814 os1.attribute13,
815 os1.attribute14,
816 os1.attribute15,
817 os1.request_id,
818 os1.program_application_id,
819 os1.program_id ,
820 os1.program_update_date,
821 os1.reference_flag,
822 nvl(os1.operation_type,1),
823 os1.process_op_seq_id,
824 os1.line_op_seq_id,
825 os1.yield,
826 os1.cumulative_yield,
827 os1.reverse_cumulative_yield,
828 os1.labor_time_calc,
829 os1.machine_time_calc,
830 os1.total_time_calc,
831 os1.labor_time_user,
832 os1.machine_time_user,
833 os1.total_time_user,
834 os1.net_planning_percent,
835 os1.implementation_date,
836 x_coordinate,
837 y_coordinate
838 FROM bom_cto_order_lines bcol1,
839 mtl_system_items si1,
840 bom_operational_routings or1,
841 bom_operation_sequences os1
842 WHERE bcol1.line_id = pLineId
843 AND bcol1.inventory_item_id = pModelId
844 AND si1.organization_id = pOrgId -- this is the mfg org from src_orgs
845 AND si1.inventory_item_id = bcol1.inventory_item_id
846 AND si1.bom_item_type = 1
847 AND or1.assembly_item_id = si1.inventory_item_id
848 AND or1.organization_id = si1.organization_id
849 AND or1.alternate_routing_designator is NULL
850 AND nvl(or1.cfm_routing_flag,2) = lCfmRtgflag
851 AND os1.routing_sequence_id = or1.common_routing_sequence_id
852 AND
853 (
854 os1.operation_type in (2,3)
855 OR
856 (
857 os1.option_dependent_flag = 2
858 AND nvl(os1.operation_type,1 ) = 1
859 )
860 )
861 AND
862 (
863 os1.disable_date is null
864 OR
865 (
866 os1.disable_date is not null
867 AND os1.disable_date >= sysdate
868 )
869 );
870
871 If PG_DEBUG <> 0 Then
872 oe_debug_pub.add('Number of Model option independent operations selected = '||sql%rowcount,1);
873 End if;
874
875 --2nd insert
876 lStmtNum := 52;
877 insert into bom_operation_sequences_temp
878 (
879 operation_sequence_id,
880 routing_sequence_id,
881 config_routing_id, -- 5336292
882 operation_seq_num,
883 last_update_date,
884 last_updated_by,
885 creation_date,
886 created_by,
887 last_update_login,
888 standard_operation_id,
889 department_id ,
890 operation_lead_time_percent,
891 minimum_transfer_quantity,
892 count_point_type ,
893 operation_description,
894 effectivity_date,
895 disable_date ,
896 backflush_flag,
897 option_dependent_flag,
898 attribute_category ,
899 attribute1,
900 attribute2,
901 attribute3,
902 attribute4,
903 attribute5,
904 attribute6,
905 attribute7,
906 attribute8,
907 attribute9,
908 attribute10,
909 attribute11,
910 attribute12,
911 attribute13,
912 attribute14,
913 attribute15,
914 request_id,
915 program_application_id,
916 program_id ,
917 program_update_date,
918 reference_flag,
919 operation_type,
920 process_op_seq_id,
921 line_op_seq_id,
922 yield,
923 cumulative_yield,
924 reverse_cumulative_yield,
925 labor_time_calc,
926 machine_time_calc,
927 total_time_calc,
928 labor_time_user,
929 machine_time_user,
930 total_time_user,
931 net_planning_percent,
932 implementation_date,
933 x_coordinate,
934 y_coordinate
935
936
937 )
938 SELECT DISTINCT
939 os1.operation_sequence_id, -- 5336292
940 os1.routing_sequence_id, -- 5336292
941 lCfgRtgId, -- 5336292
942 os1.operation_seq_num,
943 glast_update_date, -- 5336292
944 gUserId,
945 sysdate,
946 gUserId,
947 gLoginId,
948 os1.standard_operation_id,
949 os1.department_id,
950 os1.operation_lead_time_percent,
951 os1.minimum_transfer_quantity,
952 os1.count_point_type,
953 os1.operation_description,
954 os1.effectivity_date, -- 5336292
955 os1.disable_date, -- 5336292
956 os1.backflush_flag,
957 os1.option_dependent_flag, -- 5336292
958 os1.attribute_category,
959 os1.attribute1,
960 os1.attribute2,
961 os1.attribute3,
962 os1.attribute4,
963 os1.attribute5,
964 os1.attribute6,
965 os1.attribute7,
966 os1.attribute8,
967 os1.attribute9,
968 os1.attribute10,
969 os1.attribute11,
970 os1.attribute12,
971 os1.attribute13,
972 os1.attribute14,
973 os1.attribute15,
974 os1.request_id,
975 os1.program_application_id,
976 os1.program_id ,
977 os1.program_update_date,
978 os1.reference_flag,
979 nvl(os1.operation_type,1),
980 os1.process_op_seq_id,
981 os1.line_op_seq_id,
982 os1.yield,
983 os1.cumulative_yield,
984 os1.reverse_cumulative_yield,
985 os1.labor_time_calc,
986 os1.machine_time_calc,
987 os1.total_time_calc,
988 os1.labor_time_user,
989 os1.machine_time_user,
990 os1.total_time_user,
991 os1.net_planning_percent,
992 os1.implementation_date,
993 x_coordinate,
994 y_coordinate
995
996 FROM bom_cto_order_lines bcol1, -- components
997 bom_cto_order_lines bcol2, -- parent models or option classes
998 mtl_system_items msi,
999 bom_inventory_components ic1,
1000 bom_bill_of_materials b1,
1001 bom_operational_routings or1,
1002 bom_operation_sequences os1
1003 WHERE bcol1.parent_ato_line_id = pLineId /*AP*/
1004 AND bcol1.item_type_code in ('CLASS','OPTION')
1005 AND bcol1.line_id <> bcol2.line_id
1006 -- bugfix 2382396 and bcol2.parent_ato_line_id = bcol1.parent_ato_line_id /*AP*/
1007 AND bcol2.inventory_item_id = msi.inventory_item_id
1008 AND msi.organization_id = pOrgId -- new from src_orgs
1009 AND msi.bom_item_type = 1
1010 AND bcol2.line_id = pLineId
1011 AND bcol2.ordered_quantity <> 0
1012 AND bcol2.line_id = bcol1.link_to_line_id
1013 -- begin 1653881
1014 AND ic1.bill_sequence_id =
1015 (
1016 SELECT
1017 common_bill_sequence_id
1018 FROM bom_bill_of_materials bbm
1019 WHERE organization_id = pOrgId
1020 AND alternate_bom_designator is null
1021 AND assembly_item_id =
1022 (
1023 SELECT DISTINCT
1024 assembly_item_id
1025 FROM bom_bill_of_materials bbm1,
1026 bom_inventory_components bic1
1027 WHERE bbm1.common_bill_sequence_id = bic1.bill_sequence_id
1028 AND component_sequence_id = bcol1.component_sequence_id
1029 AND bbm1.assembly_item_id = bcol2.inventory_item_id
1030 )
1031 )
1032 AND ic1.component_item_id = bcol1.inventory_item_id
1033 --end 1653881
1034 -- 1912376
1035 AND
1036 (
1037 ic1.disable_date is null
1038 OR
1039 (
1040 ic1.disable_date is not null
1041 AND ic1.disable_date >= sysdate
1042 )
1043 )
1044 AND b1.common_bill_sequence_id = ic1.bill_sequence_id
1045 AND b1.assembly_item_id = bcol2.inventory_item_id -- 1272142
1046 AND b1.alternate_bom_designator is NULL
1047 AND or1.assembly_item_id = b1.assembly_item_id
1048 AND or1.organization_id = b1.organization_id
1049 AND b1.organization_id = pOrgId --bug 1935580
1050 AND or1.alternate_routing_designator is null
1051 AND nvl(or1.cfm_routing_flag,2) = lCfmRtgFlag
1052 AND
1053 (
1054 os1.disable_date is null
1055 OR
1056 (
1057 os1.disable_date is not null
1058 AND os1.disable_date >= sysdate
1059 )
1060 )
1061 AND os1.routing_sequence_id = or1.common_routing_sequence_id
1062 --one-to-many BOM components to Rtg operations
1063 AND
1064 (
1065 (
1066 os1.operation_seq_num = ic1.operation_seq_num
1067 )
1068 OR
1069 (
1070 os1.operation_seq_num in
1071 (
1072 SELECT
1073 bco.operation_seq_num
1074 FROM bom_component_operations bco
1075 WHERE bco.component_sequence_id = ic1.component_sequence_id
1076 )
1077 )
1078 )
1079 --one-to-many BOM components to Rtg operations
1080 AND os1.option_dependent_flag = 1
1081 AND nvl(os1.operation_type,1) = 1
1082 and operation_sequence_id not in(select operation_sequence_id
1083 from bom_operation_sequences_temp);
1084
1085 If PG_DEBUG <> 0 Then
1086 oe_debug_pub.add('Number of Model option dependent operations selected = '||sql%rowcount,1);
1087 End if;
1088 --3rdnd insert
1089 lStmtNum := 53;
1090 insert into bom_operation_sequences_temp
1091 (
1092 operation_sequence_id,
1093 routing_sequence_id,
1094 config_routing_id, -- 5336292
1095 operation_seq_num,
1096 last_update_date,
1097 last_updated_by,
1098 creation_date,
1099 created_by,
1100 last_update_login,
1101 standard_operation_id,
1102 department_id ,
1103 operation_lead_time_percent,
1104 minimum_transfer_quantity,
1105 count_point_type ,
1106 operation_description,
1107 effectivity_date,
1108 disable_date ,
1109 backflush_flag,
1110 option_dependent_flag,
1111 attribute_category ,
1112 attribute1,
1113 attribute2,
1114 attribute3,
1115 attribute4,
1116 attribute5,
1117 attribute6,
1118 attribute7,
1119 attribute8,
1120 attribute9,
1121 attribute10,
1122 attribute11,
1123 attribute12,
1124 attribute13,
1125 attribute14,
1126 attribute15,
1127 request_id,
1128 program_application_id,
1129 program_id ,
1130 program_update_date,
1131 reference_flag,
1132 operation_type,
1133 process_op_seq_id,
1134 line_op_seq_id,
1135 yield,
1136 cumulative_yield,
1137 reverse_cumulative_yield,
1138 labor_time_calc,
1139 machine_time_calc,
1140 total_time_calc,
1141 labor_time_user,
1142 machine_time_user,
1143 total_time_user,
1144 net_planning_percent,
1145 implementation_date,
1146 x_coordinate,
1147 y_coordinate
1148
1149 )
1150 SELECT DISTINCT
1151 os1.operation_sequence_id, -- 5336292
1152 os1.routing_sequence_id, -- 5336292
1153 lCfgRtgId, -- 5336292
1154 os1.operation_seq_num,
1155 glast_update_date, -- 5336292
1156 gUserId,
1157 sysdate,
1158 gUserId,
1159 gLoginId,
1160 os1.standard_operation_id,
1161 os1.department_id,
1162 os1.operation_lead_time_percent,
1163 os1.minimum_transfer_quantity,
1164 os1.count_point_type,
1165 os1.operation_description,
1166 os1.effectivity_date, -- 5336292
1167 os1.disable_date, -- 5336292
1168 os1.backflush_flag,
1169 os1.option_dependent_flag, -- 5336292
1170 os1.attribute_category,
1171 os1.attribute1,
1172 os1.attribute2,
1173 os1.attribute3,
1174 os1.attribute4,
1175 os1.attribute5,
1176 os1.attribute6,
1177 os1.attribute7,
1178 os1.attribute8,
1179 os1.attribute9,
1180 os1.attribute10,
1181 os1.attribute11,
1182 os1.attribute12,
1183 os1.attribute13,
1184 os1.attribute14,
1185 os1.attribute15,
1186 os1.request_id,
1187 os1.program_application_id,
1188 os1.program_id ,
1189 os1.program_update_date,
1190 os1.reference_flag,
1191 nvl(os1.operation_type,1),
1192 os1.process_op_seq_id,
1193 os1.line_op_seq_id,
1194 os1.yield,
1195 os1.cumulative_yield,
1196 os1.reverse_cumulative_yield,
1197 os1.labor_time_calc,
1198 os1.machine_time_calc,
1199 os1.total_time_calc,
1200 os1.labor_time_user,
1201 os1.machine_time_user,
1202 os1.total_time_user,
1203 os1.net_planning_percent,
1204 os1.implementation_date,
1205 x_coordinate,
1206 y_coordinate
1207 -- 5336292
1208 FROM bom_operation_sequences os1,
1209 bom_operational_routings or1,
1210 mtl_system_items si2,
1211 bom_inventory_components ic1,
1212 bom_bill_of_materials b1,
1213 mtl_system_items si1
1214 WHERE si1.organization_id = pOrgId
1215 AND si1.inventory_item_id = pModelId
1216 AND si1.bom_item_type = 1 /* model */
1217 AND b1.organization_id = si1.organization_id
1218 AND b1.assembly_item_id = si1.inventory_item_id
1219 AND b1.alternate_bom_designator is null
1220 AND or1.assembly_item_id = b1.assembly_item_id
1221 AND or1.organization_id = b1.organization_id
1222 AND or1.alternate_routing_designator is null
1223 AND nvl(or1.cfm_routing_flag,2) = lCfmRtgFlag
1224 AND os1.routing_sequence_id = or1.common_routing_sequence_id
1225 AND
1226 (
1227 os1.disable_date is null
1228 OR
1229 (
1230 os1.disable_date is not null
1231 AND os1.disable_date >= sysdate
1232 )
1233 )
1234 AND ic1.bill_sequence_id = b1.common_bill_sequence_id
1235 AND ic1.optional = 2
1236 AND ic1.implementation_date is not null
1237 AND
1238 (
1239 ic1.disable_date is null
1240 OR
1241 (
1242 ic1.disable_date is not null
1243 AND ic1.disable_date >= sysdate
1244 )
1245 )
1246 AND si2.inventory_item_id = ic1.component_item_id
1247 AND si2.organization_id = b1.organization_id
1248 AND si2.bom_item_type = 4 -- standard
1249 AND os1.option_dependent_flag = 1
1250 /* one-to-many BOM components to Rtg operations */
1251 AND
1252 (
1253 (
1254 os1.operation_seq_num = ic1.operation_seq_num
1255 )
1256 OR
1257 (
1258 os1.operation_seq_num in
1259 (
1260 SELECT
1261 bco.operation_seq_num
1262 FROM bom_component_operations bco
1263 WHERE bco.component_sequence_id = ic1.component_sequence_id
1264 )
1265 )
1266 )
1267 /* one-to-many BOM components to Rtg operations */
1268 AND nvl(os1.operation_type,1) = 1
1269 and operation_sequence_id not in(select operation_sequence_id
1270 from bom_operation_sequences_temp);
1271
1272
1273
1274 If PG_DEBUG <> 0 Then
1275 oe_debug_pub.add('Number of Model mandatory independent operations selected = '||sql%rowcount,1);
1276 End if;
1277
1278 IF PG_DEBUG <> 0 THEN
1279 oe_debug_pub.add ('create_routing_ml: ' || ' Model Routing : Inserted ' || sql%rowcount || ' records in temp table',2 );
1280 END IF;
1281
1282 IF PG_DEBUG <> 0 THEN
1283 open bos_temp(lCfgRtgId);
1284
1285 loop
1286 fetch bos_temp into d_op_seq_id,d_rtg_seq_id,d_op_seq_num,d_cfg_rtg_id;
1287 exit when bos_temp%notfound;
1288 oe_debug_pub.add ('create_routing_ml: ' || 'TempTable after insert :Op Seq Id: ' || d_op_seq_id
1289 || 'Rtg Seq Id: ' ||d_rtg_seq_id
1290 || 'Op Seq #: ' || d_op_seq_num
1291 || 'Cfg Rtg Id: ' ||d_cfg_rtg_id ,2);
1292 end loop;
1293 close bos_temp;
1294 END IF;
1295
1296
1297
1298
1299
1300 /*-------------------------------------------------------------------------------------+
1301 Bugfix - 1935580 Reselect to identify unique rows and update cfgrtgid to (-)cfgrtgid
1302 We are doing this here since we do not want to touch the main update above.
1303 +-------------------------------------------------------------------------------------*/
1304 lStmtNum := 51;
1305 lmodnewCfgRtgId := lCfgRtgId * (-1);
1306 lmodseqnum:=0;
1307 lmodtyp:=0;
1308 lmodrtgseqid :=0;
1309
1310
1311 open get_op_seq_num(lCfgRtgId);
1312
1313 loop
1314 fetch get_op_seq_num into lmodseqnum,lmodtyp;
1315 exit when get_op_seq_num%notfound;
1316
1317 IF PG_DEBUG <> 0 THEN
1318 oe_debug_pub.add ('create_routing_ml: ' || ' Op Seq # : ' || lmodseqnum || ' Op Type : ' || lmodtyp ,2);
1319 oe_debug_pub.add ('create_routing_ml: ' || 'Esitmated release date lEstRelDate '|| to_char(lEstRelDate,'mm-dd-yy:hh:mi:ss'),2 );
1320 END IF;
1321
1322 select max(routing_sequence_id) into lmodrtgseqid
1323 from bom_operation_sequences_temp -- 5336292
1324 where operation_seq_num = lmodseqnum
1325 and nvl(operation_type,1)= lmodtyp
1326 and config_routing_id = lCfgRtgId -- bugfix 3239267: replaced last_update_login
1327 and last_update_date = glast_update_date; -- 3180827
1328
1329 IF PG_DEBUG <> 0 THEN
1330 oe_debug_pub.add ('create_routing_ml: ' || ' Max. Routing Seq Id : ' || lmodrtgseqid,2);
1331 END IF;
1332
1333 update bom_operation_sequences_temp -- 5336292
1334 set config_routing_id = lmodnewCfgRtgId -- bugfix 3239267: replaced last_update_login
1335 where operation_seq_num = lmodseqnum
1336 and nvl(operation_type,1)= lmodtyp
1337 and routing_sequence_id=lmodrtgseqid
1338 /* Bugfix 2177101/2143014 */
1339 /*
1340 and effectivity_date <= greatest(nvl(lEstRelDate, sysdate),sysdate) -- 2650828
1341 removed due to changes in effectivity date logic
1342 */
1343 and implementation_date is not null
1344 /*
1345 and nvl(disable_date,nvl(lEstRelDate, sysdate)+ 1) > NVL(lEstRelDate,sysdate)
1346 and nvl(disable_date,sysdate+1) > sysdate;--Bugfix 2771065
1347 changed due to new effectivity dates logic
1348 */
1349 and ( disable_date is null or (disable_date is not null and disable_date >= sysdate ));
1350 /* Bugfix 2177101/2143014 */
1351
1352 IF PG_DEBUG <> 0 THEN
1353 oe_debug_pub.add ('create_routing_ml: ' || 'Update login to ' || lmodnewCfgRtgId ||' where routing seq Id is '||lmodrtgseqid,2);
1354 END IF;
1355
1356 end loop;
1357
1358 close get_op_seq_num;
1359
1360 IF PG_DEBUG <> 0 THEN
1361 oe_debug_pub.add ('create_routing_ml: ' || ' Model Routing : Marked Finally ' || sql%rowcount || ' rows for insertion' ,2);
1362 END IF;
1363 IF PG_DEBUG <> 0 THEN
1364
1365 open bos_temp(lmodnewCfgRtgId);
1366
1367 loop
1368 fetch bos_temp into d_op_seq_id,d_rtg_seq_id,d_op_seq_num,d_cfg_rtg_id;
1369 exit when bos_temp%notfound;
1370 oe_debug_pub.add ('create_routing_ml: ' || ' TempTable after update :Op Seq Id: ' || d_op_seq_id
1371 || 'Rtg Seq Id: ' ||d_rtg_seq_id
1372 || 'Op Seq# : ' || d_op_seq_num
1373 || 'Cfg Rtg Id: ' ||d_cfg_rtg_id ,2);
1374 end loop;
1375 close bos_temp;
1376
1377 END IF;
1378
1379 /*-------------------------+
1380 End Bugfix - 1935580
1381 +---------------------------*/
1382
1383 /*-----------------------------------------------------------------+
1384 First Insert :
1385 Load distinct operation steps from Model's routing
1386 +-------------------------------------------------------------------*/
1387
1388 lStmtNum := 60;
1389
1390 IF PG_DEBUG <> 0 THEN
1391 oe_debug_pub.add ('create_routing_ml: ' || 'Inserting into bom_operation_sequences - 1st insert ..',5);
1392 END IF;
1393
1394 insert into bom_operation_sequences
1395 (
1396 operation_sequence_id,
1397 routing_sequence_id,
1398 operation_seq_num,
1399 last_update_date,
1400 last_updated_by,
1401 creation_date,
1402 created_by,
1403 last_update_login,
1404 standard_operation_id,
1405 department_id ,
1406 operation_lead_time_percent,
1407 minimum_transfer_quantity,
1408 count_point_type ,
1409 operation_description,
1410 effectivity_date,
1411 disable_date ,
1412 backflush_flag,
1413 option_dependent_flag,
1414 attribute_category ,
1415 attribute1,
1416 attribute2,
1417 attribute3,
1418 attribute4,
1419 attribute5,
1420 attribute6,
1421 attribute7,
1422 attribute8,
1423 attribute9,
1424 attribute10,
1425 attribute11,
1426 attribute12,
1427 attribute13,
1428 attribute14,
1429 attribute15,
1430 request_id, /* using this column to store model op seq id */
1431 program_application_id,
1432 program_id ,
1433 program_update_date,
1434 reference_flag,
1435 operation_type,
1436 process_op_seq_id,
1437 line_op_seq_id,
1438 yield,
1439 cumulative_yield,
1440 reverse_cumulative_yield,
1441 labor_time_calc,
1442 machine_time_calc,
1443 total_time_calc,
1444 labor_time_user,
1445 machine_time_user,
1446 total_time_user,
1447 net_planning_percent,
1448 implementation_date, -- new column for 11.5.4 BOM patchset
1449 x_coordinate, --bugfix 1765149
1450 y_coordinate --bugfix 1765149
1451 )
1452 select
1453 bom_operation_sequences_s.nextval, /* operation_sequence_id */
1454 lcfgrtgid, /* routing_sequence_id */
1455 os1.operation_seq_num,
1456 sysdate, /* last update date */
1457 gUserId, /* last updated by */
1458 sysdate, /* creation date */
1459 gUserId, /* created by */
1460 gLoginId, /* last update login */
1461 os1.standard_operation_id,
1462 os1.department_id,
1463 os1.operation_lead_time_percent,
1464 os1.minimum_transfer_quantity,
1465 os1.count_point_type,
1466 os1.operation_description,
1467 trunc(sysdate), /* effective date */
1468 null, /* disable date */
1469 os1.backflush_flag,
1470 2, /* option_dependent_flag */
1471 os1.attribute_category,
1472 os1.attribute1,
1473 os1.attribute2,
1474 os1.attribute3,
1475 os1.attribute4,
1476 os1.attribute5,
1477 os1.attribute6,
1478 os1.attribute7,
1479 os1.attribute8,
1480 os1.attribute9,
1481 os1.attribute10,
1482 os1.attribute11,
1483 os1.attribute12,
1484 os1.attribute13,
1485 os1.attribute14,
1486 os1.attribute15,
1487 os1.operation_sequence_id, /* using request_id column to store model op seq id */
1488 1, /* program_application_id */
1489 1, /* program_id */
1490 sysdate, /* program_update_date */
1491 reference_flag,
1492 nvl(operation_type,1),
1493 process_op_seq_id,
1494 line_op_seq_id,
1495 yield,
1496 cumulative_yield,
1497 reverse_cumulative_yield,
1498 labor_time_calc,
1499 machine_time_calc,
1500 total_time_calc,
1501 labor_time_user,
1502 machine_time_user,
1503 total_time_user,
1504 net_planning_percent,
1505 trunc(sysdate), -- new column for 11.5.4 BOM patchset
1506 os1.x_coordinate, --bugfix 1765149
1507 os1.y_coordinate --bugfix 1765149
1508 from
1509 bom_operation_sequences_temp os1 -- 5336292
1510 where os1.config_routing_id = lmodnewcfgrtgid ; /*Bugfix 1935580 - change lCfgRtgId to lmodnewCfgRtgId */
1511 -- bugfix 3239267: replaced last_update_login
1512
1513
1514
1515 IF PG_DEBUG <> 0 THEN
1516 oe_debug_pub.add('create_routing_ml: ' || 'Inserted ' || sql%rowcount || ' rows in BOS',2);
1517 END IF;
1518
1519
1520 /*--------------------------------------------------------------+
1521 Initialize config_routing_id column so that it can be used -- bugfix 3239267: replaced last_update_login
1522 to identify steps from option class routings
1523 +---------------------------------------------------------------*/
1524
1525 -- 5336292 : Instead of update we will delete rows
1526 lStmtNum := 70;
1527 delete from bom_operation_sequences_temp
1528 where config_routing_id in (lCfgRtgId, lmodnewcfgrtgid);
1529
1530 IF PG_DEBUG <> 0 THEN
1531 oe_debug_pub.add('create_routing_ml: ' || 'Deleted ' || sql%rowcount || ' rows from temp table',2);
1532 -- bugfix 3239267: replaced last_update_login
1533 END IF;
1534
1535
1536
1537
1538 /*--------------------------------------------------------------+
1539
1540 --rtg4
1541
1542 Mark all steps that need to be picked up from option
1543 Class routings
1544 -- Mandatory steps of Class routing
1545 -- Option dependent steps associated with options/option Class
1546 -- "Additional" option dependent steps associated with options/option Class
1547 -- Option dependent steps associated with mandatory comps.
1548 -- "Additional" option dependent steps associated with mandatory comps.
1549 The "additional" operation steps are the steps stored in the new
1550 table bom_component_operations to support one-to-many BOM components
1551 to Routing steps.
1552 +-------------------------------------------------------------*/
1553
1554 --perf bugfix 4905857 sql id 16103308
1555 --Fixed Performance bug in the following sql. AS the following sql is huge one,
1556 --performance team asked us to divide the sql into pieces. We are planning to
1557 --insert a record from each sub query in the union class and then update it from
1558 --interface table
1559
1560
1561 lStmtNum := 80;
1562 l_batch_id := bom_import_pub.get_batchid;
1563
1564 --1st insert
1565 lStmtNum := 81;
1566 insert into bom_operation_sequences_temp
1567 ( operation_sequence_id,
1568 routing_sequence_id,
1569 config_routing_id, -- 5336292
1570 operation_seq_num,
1571 last_update_date,
1572 last_updated_by,
1573 creation_date,
1574 created_by,
1575 last_update_login,
1576 standard_operation_id,
1577 department_id ,
1578 operation_lead_time_percent,
1579 minimum_transfer_quantity,
1580 count_point_type ,
1581 operation_description,
1582 effectivity_date,
1583 disable_date ,
1584 backflush_flag,
1585 option_dependent_flag,
1586 attribute_category ,
1587 attribute1,
1588 attribute2,
1589 attribute3,
1590 attribute4,
1591 attribute5,
1592 attribute6,
1593 attribute7,
1594 attribute8,
1595 attribute9,
1596 attribute10,
1597 attribute11,
1598 attribute12,
1599 attribute13,
1600 attribute14,
1601 attribute15,
1602 request_id, /* using this column to store model op seq id */
1603 program_application_id,
1604 program_id ,
1605 program_update_date,
1606 reference_flag,
1607 operation_type,
1608 process_op_seq_id,
1609 line_op_seq_id,
1610 yield,
1611 cumulative_yield,
1612 reverse_cumulative_yield,
1613 labor_time_calc,
1614 machine_time_calc,
1615 total_time_calc,
1616 labor_time_user,
1617 machine_time_user,
1618 total_time_user,
1619 net_planning_percent,
1620 implementation_date,
1621 x_coordinate,
1622 y_coordinate )
1623 SELECT distinct
1624 os1.operation_sequence_id, -- 5336292
1625 os1.routing_sequence_id, -- 5336292
1626 lCfgRtgId, -- 5336292
1627 os1.operation_seq_num,
1628 glast_update_date, -- 5336292
1629 gUserId,
1630 sysdate,
1631 gUserID,
1632 gLoginId,
1633 os1.standard_operation_id,
1634 os1.department_id,
1635 os1.operation_lead_time_percent,
1636 os1.minimum_transfer_quantity,
1637 os1.count_point_type,
1638 os1.operation_description,
1639 os1.effectivity_date, -- 5336292
1640 os1.disable_date, -- 5336292
1641 os1.backflush_flag,
1642 os1.option_dependent_flag,
1643 os1.attribute_category,
1644 os1.attribute1,
1645 os1.attribute2,
1646 os1.attribute3,
1647 os1.attribute4,
1648 os1.attribute5,
1649 os1.attribute6,
1650 os1.attribute7,
1651 os1.attribute8,
1652 os1.attribute9,
1653 os1.attribute10,
1654 os1.attribute11,
1655 os1.attribute12,
1656 os1.attribute13,
1657 os1.attribute14,
1658 os1.attribute15,
1659 os1.request_id,
1660 os1.program_application_id,
1661 os1.program_id ,
1662 os1.program_update_date,
1663 os1.reference_flag,
1664 nvl(os1.operation_type,1),
1665 os1.process_op_seq_id,
1666 os1.line_op_seq_id,
1667 os1.yield,
1668 os1.cumulative_yield,
1669 os1.reverse_cumulative_yield,
1670 os1.labor_time_calc,
1671 os1.machine_time_calc,
1672 os1.total_time_calc,
1673 os1.labor_time_user,
1674 os1.machine_time_user,
1675 os1.total_time_user,
1676 os1.net_planning_percent,
1677 os1.implementation_date,
1678 x_coordinate,
1679 y_coordinate
1680 FROM mtl_system_items si1,
1681 bom_cto_order_lines bcol,
1682 bom_operational_routings or1,
1683 bom_operation_sequences os1
1684 WHERE bcol.parent_ato_line_id = pLineId
1685 AND si1.organization_id = pOrgId
1686 AND si1.inventory_item_id = bcol.inventory_item_id
1687 AND
1688 (
1689 (
1690 si1.bom_item_type = 1
1691 AND bcol.wip_supply_type = 6
1692 )
1693 OR
1694 (
1695 si1.bom_item_type = 2
1696 )
1697 ) /* Phantom Model ROUTING Should be included in parent model */
1698 AND bcol.line_id <> pLineId
1699 AND or1.assembly_item_id = si1.inventory_item_id
1700 AND or1.organization_id = si1.organization_id
1701 AND or1.alternate_routing_designator is NULL
1702 AND NVL(or1.cfm_routing_flag,2) = lCfmRtgflag
1703 AND os1.routing_sequence_id = or1.common_routing_sequence_id
1704 AND
1705 (
1706 os1.disable_date is null
1707 OR
1708 (
1709 os1.disable_date is not null
1710 AND os1.disable_date >= sysdate
1711 )
1712 )
1713 AND
1714 (
1715 os1.operation_type in (2,3)
1716 OR
1717 (
1718 os1.option_dependent_flag = 2
1719 AND NVL(os1.operation_type,1 ) = 1
1720 )
1721 );
1722
1723 If PG_DEBUG <> 0 Then
1724 oe_debug_pub.add('Number of option class option independent operations selected = '||sql%rowcount,1);
1725 End if;
1726 --2nd insert
1727 lStmtNum := 82;
1728
1729 insert into bom_operation_sequences_temp
1730 ( operation_sequence_id,
1731 routing_sequence_id,
1732 config_routing_id, -- 5336292
1733 operation_seq_num,
1734 last_update_date,
1735 last_updated_by,
1736 creation_date,
1737 created_by,
1738 last_update_login,
1739 standard_operation_id,
1740 department_id ,
1741 operation_lead_time_percent,
1742 minimum_transfer_quantity,
1743 count_point_type ,
1744 operation_description,
1745 effectivity_date,
1746 disable_date ,
1747 backflush_flag,
1748 option_dependent_flag,
1749 attribute_category ,
1750 attribute1,
1751 attribute2,
1752 attribute3,
1753 attribute4,
1754 attribute5,
1755 attribute6,
1756 attribute7,
1757 attribute8,
1758 attribute9,
1759 attribute10,
1760 attribute11,
1761 attribute12,
1762 attribute13,
1763 attribute14,
1764 attribute15,
1765 request_id, /* using this column to store model op seq id */
1766 program_application_id,
1767 program_id ,
1768 program_update_date,
1769 reference_flag,
1770 operation_type,
1771 process_op_seq_id,
1772 line_op_seq_id,
1773 yield,
1774 cumulative_yield,
1775 reverse_cumulative_yield,
1776 labor_time_calc,
1777 machine_time_calc,
1778 total_time_calc,
1779 labor_time_user,
1780 machine_time_user,
1781 total_time_user,
1782 net_planning_percent,
1783 implementation_date,
1784 x_coordinate,
1785 y_coordinate
1786 )
1787 SELECT distinct
1788 os1.operation_sequence_id, -- 5336292
1789 os1.routing_sequence_id, -- 5336292
1790 lCfgRtgId, -- 5336292
1791 os1.operation_seq_num,
1792 glast_update_date, -- 5336292
1793 gUserId,
1794 sysdate,
1795 gUserID,
1796 gLoginId,
1797 os1.standard_operation_id,
1798 os1.department_id,
1799 os1.operation_lead_time_percent,
1800 os1.minimum_transfer_quantity,
1801 os1.count_point_type,
1802 os1.operation_description,
1803 os1.effectivity_date, -- 5336292
1804 os1.disable_date, -- 5336292
1805 os1.backflush_flag,
1806 os1.option_dependent_flag,
1807 os1.attribute_category,
1808 os1.attribute1,
1809 os1.attribute2,
1810 os1.attribute3,
1811 os1.attribute4,
1812 os1.attribute5,
1813 os1.attribute6,
1814 os1.attribute7,
1815 os1.attribute8,
1816 os1.attribute9,
1817 os1.attribute10,
1818 os1.attribute11,
1819 os1.attribute12,
1820 os1.attribute13,
1821 os1.attribute14,
1822 os1.attribute15,
1823 os1.request_id,
1824 os1.program_application_id,
1825 os1.program_id ,
1826 os1.program_update_date,
1827 os1.reference_flag,
1828 nvl(os1.operation_type,1),
1829 os1.process_op_seq_id,
1830 os1.line_op_seq_id,
1831 os1.yield,
1832 os1.cumulative_yield,
1833 os1.reverse_cumulative_yield,
1834 os1.labor_time_calc,
1835 os1.machine_time_calc,
1836 os1.total_time_calc,
1837 os1.labor_time_user,
1838 os1.machine_time_user,
1839 os1.total_time_user,
1840 os1.net_planning_percent,
1841 os1.implementation_date,
1842 x_coordinate,
1843 y_coordinate
1844 FROM bom_cto_order_lines bcol1, /* components */
1845 bom_cto_order_lines bcol2, /* parents model */
1846 bom_inventory_components ic1,
1847 bom_bill_of_materials b1,
1848 bom_operational_routings or1,
1849 bom_operation_sequences os1
1850 WHERE bcol1.parent_ato_line_id = pLineId
1851 AND bcol1.item_type_code in ('CLASS','OPTION')
1852 AND bcol2.parent_ato_line_id = pLineId
1853 AND bcol2.line_id <> pLineId /*AP*/
1854 AND bcol2.item_type_code = 'CLASS' /* option classes */
1855 AND bcol2.ordered_quantity <> 0
1856 AND bcol2.line_id = bcol1.link_to_line_id /* check, replaced from parent_comp_seq_id */
1857 -- begin 1653881
1858 AND ic1.bill_sequence_id =
1859 (
1860 SELECT
1861 common_bill_sequence_id
1862 FROM bom_bill_of_materials bbm
1863 WHERE organization_id = pOrgId
1864 AND alternate_bom_designator is null
1865 AND assembly_item_id =
1866 (
1867 SELECT DISTINCT
1868 assembly_item_id
1869 FROM bom_bill_of_materials bbm1,
1870 bom_inventory_components bic1
1871 WHERE bbm1.common_bill_sequence_id = bic1.bill_sequence_id
1872 AND component_sequence_id = bcol1.component_sequence_id
1873 AND bbm1.assembly_item_id = bcol2.inventory_item_id
1874 )
1875 )
1876 AND ic1.component_item_id = bcol1.inventory_item_id
1877 -- end 1653881
1878 -- 1912376
1879 AND
1880 (
1881 ic1.disable_date is null
1882 OR
1883 (
1884 ic1.disable_date is not null
1885 AND ic1.disable_date >= sysdate
1886 )
1887 )
1888 AND b1.common_bill_sequence_id = ic1.bill_sequence_id
1889 AND b1.assembly_item_id = bcol2.inventory_item_id --1272142
1890 AND b1.alternate_bom_designator is NULL
1891 AND or1.assembly_item_id = b1.assembly_item_id
1892 AND or1.organization_id = b1.organization_id
1893 AND b1.organization_id = pOrgId --1210477
1894 AND or1.alternate_routing_designator is null
1895 AND nvl(or1.cfm_routing_flag,2) = lCfmRtgFlag
1896 AND
1897 (
1898 os1.disable_date is null
1899 OR
1900 (
1901 os1.disable_date is not null
1902 AND os1.disable_date >= sysdate
1903 )
1904 )
1905 AND os1.routing_sequence_id = or1.common_routing_sequence_id
1906 /* one-to-many BOM components to Rtg operations */
1907 AND
1908 (
1909 (
1910 os1.operation_seq_num = ic1.operation_seq_num
1911 )
1912 OR
1913 (
1914 os1.operation_seq_num in
1915 (
1916 SELECT
1917 bco.operation_seq_num
1918 FROM bom_component_operations bco
1919 WHERE bco.component_sequence_id = ic1.component_sequence_id
1920 )
1921 )
1922 )
1923 /* one-to-many BOM components to Rtg operations */
1924 AND os1.option_dependent_flag = 1
1925 AND nvl(os1.operation_type,1) = 1
1926 and operation_sequence_id not in(select operation_sequence_id
1927 from bom_operation_sequences_temp);
1928
1929
1930 If PG_DEBUG <> 0 Then
1931 oe_debug_pub.add('Number of option class option dependent operations selected = '||sql%rowcount,1);
1932 End if;
1933 --3rd insert
1934 lStmtNum := 83;
1935
1936 insert into bom_operation_sequences_temp
1937 ( operation_sequence_id,
1938 routing_sequence_id,
1939 config_routing_id, -- 5336292
1940 operation_seq_num,
1941 last_update_date,
1942 last_updated_by,
1943 creation_date,
1944 created_by,
1945 last_update_login,
1946 standard_operation_id,
1947 department_id ,
1948 operation_lead_time_percent,
1949 minimum_transfer_quantity,
1950 count_point_type ,
1951 operation_description,
1952 effectivity_date,
1953 disable_date ,
1954 backflush_flag,
1955 option_dependent_flag,
1956 attribute_category ,
1957 attribute1,
1958 attribute2,
1959 attribute3,
1960 attribute4,
1961 attribute5,
1962 attribute6,
1963 attribute7,
1964 attribute8,
1965 attribute9,
1966 attribute10,
1967 attribute11,
1968 attribute12,
1969 attribute13,
1970 attribute14,
1971 attribute15,
1972 request_id, /* using this column to store model op seq id */
1973 program_application_id,
1974 program_id ,
1975 program_update_date,
1976 reference_flag,
1977 operation_type,
1978 process_op_seq_id,
1979 line_op_seq_id,
1980 yield,
1981 cumulative_yield,
1982 reverse_cumulative_yield,
1983 labor_time_calc,
1984 machine_time_calc,
1985 total_time_calc,
1986 labor_time_user,
1987 machine_time_user,
1988 total_time_user,
1989 net_planning_percent,
1990 implementation_date,
1991 x_coordinate,
1992 y_coordinate
1993 )
1994 SELECT distinct
1995 os1.operation_sequence_id, -- 5336292
1996 os1.routing_sequence_id, -- 5336292
1997 lCfgRtgId, -- 5336292
1998 os1.operation_seq_num,
1999 glast_update_date, -- 5336292
2000 gUserId,
2001 sysdate,
2002 gUserID,
2003 gLoginId,
2004 os1.standard_operation_id,
2005 os1.department_id,
2006 os1.operation_lead_time_percent,
2007 os1.minimum_transfer_quantity,
2008 os1.count_point_type,
2009 os1.operation_description,
2010 os1.effectivity_date, -- 5336292
2011 os1.disable_date, -- 5336292
2012 os1.backflush_flag,
2013 os1.option_dependent_flag,
2014 os1.attribute_category,
2015 os1.attribute1,
2016 os1.attribute2,
2017 os1.attribute3,
2018 os1.attribute4,
2019 os1.attribute5,
2020 os1.attribute6,
2021 os1.attribute7,
2022 os1.attribute8,
2023 os1.attribute9,
2024 os1.attribute10,
2025 os1.attribute11,
2026 os1.attribute12,
2027 os1.attribute13,
2028 os1.attribute14,
2029 os1.attribute15,
2030 os1.request_id,
2031 os1.program_application_id,
2032 os1.program_id ,
2033 os1.program_update_date,
2034 os1.reference_flag,
2035 nvl(os1.operation_type,1),
2036 os1.process_op_seq_id,
2037 os1.line_op_seq_id,
2038 os1.yield,
2039 os1.cumulative_yield,
2040 os1.reverse_cumulative_yield,
2041 os1.labor_time_calc,
2042 os1.machine_time_calc,
2043 os1.total_time_calc,
2044 os1.labor_time_user,
2045 os1.machine_time_user,
2046 os1.total_time_user,
2047 os1.net_planning_percent,
2048 os1.implementation_date,
2049 x_coordinate,
2050 y_coordinate
2051
2052 FROM bom_operation_sequences os1,
2053 bom_operational_routings or1,
2054 mtl_system_items si2,
2055 bom_inventory_components ic1,
2056 bom_bill_of_materials b1,
2057 mtl_system_items si1,
2058 bom_cto_order_lines bcol /* Model or option class */
2059 WHERE bcol.parent_ato_line_id = pLineId
2060 AND bcol.component_sequence_id is not null
2061 AND bcol.ordered_quantity <> 0
2062 AND si1.organization_id = pOrgId
2063 AND si1.inventory_item_id = bcol.inventory_item_id
2064 AND si1.bom_item_type in (1,2) /* model or option class */
2065 AND b1.organization_id = pOrgId
2066 AND b1.assembly_item_id = bcol.inventory_item_id
2067 AND b1.alternate_bom_designator is null
2068 AND ic1.bill_sequence_id = b1.common_bill_sequence_id
2069 AND ic1.optional = 2
2070 AND ic1.implementation_date is not null
2071 AND
2072 (
2073 ic1.disable_date is null
2074 OR
2075 (
2076 ic1.disable_date is not null
2077 AND ic1.disable_date >= sysdate
2078 )
2079 )
2080 AND si2.inventory_item_id = ic1.component_item_id
2081 AND si2.organization_id = b1.organization_id
2082 AND si2.bom_item_type = 4 /* standard */
2083 AND or1.assembly_item_id = b1.assembly_item_id
2084 AND or1.organization_id = b1.organization_id
2085 AND or1.alternate_routing_designator is null
2086 AND nvl(or1.cfm_routing_flag,2) = lCfmRtgFlag /*ensure correct OC rtgs*/
2087 AND
2088 (
2089 os1.disable_date is null
2090 OR
2091 (
2092 os1.disable_date is not null
2093 AND os1.disable_date >= sysdate
2094 )
2095 )
2096 AND os1.routing_sequence_id = or1.common_routing_sequence_id
2097 AND os1.option_dependent_flag = 1
2098 /* one-to-many BOM components to Rtg operations */
2099 AND
2100 (
2101 (
2102 os1.operation_seq_num = ic1.operation_seq_num
2103 )
2104 OR
2105 (
2106 os1.operation_seq_num in
2107 (
2108 SELECT
2109 bco.operation_seq_num
2110 FROM bom_component_operations bco
2111 WHERE bco.component_sequence_id = ic1.component_sequence_id
2112 )
2113 )
2114 )
2115 /* one-to-many BOM components to Rtg operations */
2116 AND nvl(os1.operation_type,1) = 1
2117 and operation_sequence_id not in(select operation_sequence_id
2118 from bom_operation_sequences_temp);
2119
2120 If PG_DEBUG <> 0 Then
2121 oe_debug_pub.add('Number of option class mandatory comps dependent operations selected = '||sql%rowcount,1);
2122 End if;
2123
2124
2125 lStmtNum := 84;
2126 IF PG_DEBUG <> 0 THEN
2127 oe_debug_pub.add ('create_routing_ml: ' || ' Option Class outing : Inserted ' || sql%rowcount || ' records in temp table',2 );
2128 END IF;
2129
2130 open bos_temp(lCfgRtgId);
2131
2132 loop
2133 fetch bos_temp into d_op_seq_id,d_rtg_seq_id,d_op_seq_num,d_cfg_rtg_id;
2134 exit when bos_temp%notfound;
2135
2136 IF PG_DEBUG <> 0 THEN
2137 oe_debug_pub.add ('create_routing_ml: ' || ' Temp table after insert :Op Seq Id : ' || d_op_seq_id || ' Rtg Seq Id : ' ||d_rtg_seq_id ,2);
2138 oe_debug_pub.add ('create_routing_ml: ' || ' Temp table after insert :Op Seq # : ' || d_op_seq_num || ' Cfg Rtg Id : ' ||d_cfg_rtg_id ,2);
2139 END IF;
2140 end loop;
2141 close bos_temp;
2142
2143 /*-------------------------------------------------------------------------------------+
2144 Bugfix - 1906371 Reselect to identify unique rows and update cfgrtgid to (-)cfgrtgid
2145 We are doing this here since we do not want to touch the main update above.
2146 +-------------------------------------------------------------------------------------*/
2147 lStmtNum := 81;
2148 lnewCfgRtgId := lCfgRtgId * (-1);
2149 lopseqnum:=0;
2150 loptyp:=0;
2151 lrtgseqid:=0;
2152
2153
2154 open get_op_seq_num(lCfgRtgId);
2155
2156 loop
2157 fetch get_op_seq_num into lopseqnum,loptyp;
2158 exit when get_op_seq_num%notfound;
2159
2160 IF PG_DEBUG <> 0 THEN
2161 oe_debug_pub.add ('create_routing_ml: ' || ' Op Seq # : ' || lopseqnum || ' Op Type : ' || loptyp ,2);
2162 END IF;
2163
2164 select max(routing_sequence_id) into lrtgseqid
2165 from bom_operation_sequences_temp -- 5336292
2166 where operation_seq_num = lopseqnum
2167 and nvl(operation_type,1)= loptyp
2168 and config_routing_id=lCfgRtgId -- bugfix 3239267: replaced last_update_login
2169 and last_update_date = glast_update_date; -- 3180827
2170
2171
2172 IF PG_DEBUG <> 0 THEN
2173 oe_debug_pub.add ('create_routing_ml: ' || ' Max. Routing Seq Id : ' || lrtgseqid,2);
2174 END IF;
2175
2176 update bom_operation_sequences_temp -- 5336292
2177 set config_routing_id = lnewCfgRtgId -- bugfix 3239267: replaced last_update_login
2178 where operation_seq_num = lopseqnum
2179 and nvl(operation_type,1)= loptyp
2180 and routing_sequence_id=lrtgseqid
2181 /* Bugfix 2177101/2143014 */
2182 /*
2183 and effectivity_date <= greatest(nvl(lEstRelDate, sysdate),sysdate) -- 2650828
2184 removed for new effectivity dates logic
2185 */
2186 and implementation_date is not null
2187 /*
2188 and nvl(disable_date,nvl(lEstRelDate, sysdate)+ 1) > NVL(lEstRelDate,sysdate)
2189 and nvl(disable_date,sysdate+1) > sysdate;--Bugfix 2771065
2190 changed for new effectivity dates logic
2191 */
2192 and ( disable_date is null or (disable_date is not null and disable_date >= sysdate ));
2193 /* Bugfix 2177101/2143014 */
2194
2195 IF PG_DEBUG <> 0 THEN
2196 oe_debug_pub.add ('create_routing_ml: ' || 'Update login to ' || lnewCfgRtgId ||' where routing seq Id is '||lrtgseqid,2);
2197 END IF;
2198
2199 end loop;
2200
2201 close get_op_seq_num;
2202
2203 IF PG_DEBUG <> 0 THEN
2204 oe_debug_pub.add ('create_routing_ml: ' || ' Option Routing : Marked Finally ' || sql%rowcount || ' rows for insertion' ,2);
2205 END IF;
2206
2207 open bos_temp(lnewCfgRtgId);
2208
2209 loop
2210 fetch bos_temp into d_op_seq_id,d_rtg_seq_id,d_op_seq_num,d_cfg_rtg_id;
2211 exit when bos_temp%notfound;
2212
2213 IF PG_DEBUG <> 0 THEN
2214 oe_debug_pub.add ('create_routing_ml: ' || ' Temp table after update :Op Seq Id : ' || d_op_seq_id || ' Rtg Seq Id : ' ||d_rtg_seq_id ,2);
2215 oe_debug_pub.add ('create_routing_ml: ' || ' Temp table after update :Op Seq # : ' || d_op_seq_num || ' Cfg Rtg Id : ' ||d_cfg_rtg_id ,2);
2216 END IF;
2217 end loop;
2218 close bos_temp;
2219 /*-------------------------+
2220 End Bugfix - 1906371
2221 +---------------------------*/
2222
2223 /*-----------------------------------------------------------------+
2224 Second Insert :
2225 Load distinct operation steps from Class(es) routing
2226 ( steps include Option independednt steps, option dependednt
2227 steps associated with selected components, option dependent
2228 steps associated with mandatory componets)
2229 +-------------------------------------------------------------------*/
2230
2231 lStmtNum := 90;
2232
2233 IF PG_DEBUG <> 0 THEN
2234 oe_debug_pub.add ('create_routing_ml: ' || 'Inserting into bom_operation_sequences - 2nd insert ..',5);
2235 END IF;
2236
2237 insert into bom_operation_sequences
2238 (
2239 operation_sequence_id,
2240 routing_sequence_id,
2241 operation_seq_num,
2242 last_update_date,
2243 last_updated_by,
2244 creation_date,
2245 created_by,
2246 last_update_login,
2247 standard_operation_id,
2248 department_id ,
2249 operation_lead_time_percent,
2250 minimum_transfer_quantity,
2251 count_point_type ,
2252 operation_description,
2253 effectivity_date,
2254 disable_date ,
2255 backflush_flag,
2256 option_dependent_flag,
2257 attribute_category ,
2258 attribute1,
2259 attribute2,
2260 attribute3,
2261 attribute4,
2262 attribute5,
2263 attribute6,
2264 attribute7,
2265 attribute8,
2266 attribute9,
2267 attribute10,
2268 attribute11,
2269 attribute12,
2270 attribute13,
2271 attribute14,
2272 attribute15,
2273 request_id, /* using this column to store model op seq id */
2274 program_application_id,
2275 program_id ,
2276 program_update_date,
2277 reference_flag,
2278 operation_type,
2279 process_op_seq_id,
2280 line_op_seq_id,
2281 yield,
2282 cumulative_yield,
2283 reverse_cumulative_yield,
2284 labor_time_calc,
2285 machine_time_calc,
2286 total_time_calc,
2287 labor_time_user,
2288 machine_time_user,
2289 total_time_user,
2290 net_planning_percent,
2291 implementation_date, -- new column for 11.5.4 BOM patchset
2292 x_coordinate, --bugfix 1765149
2293 y_coordinate --bugfix 1765149
2294 )
2295 select
2296 bom_operation_sequences_s.nextval, /* operation_sequence_id */
2297 lcfgrtgid, /* routing_sequence_id */
2298 os1.operation_seq_num,
2299 sysdate, /* last update date */
2300 gUserId, /* last updated by */
2301 sysdate, /* creation date */
2302 gUserID, /* created by */
2303 gLoginId, /* last update login */
2304 os1.standard_operation_id,
2305 os1.department_id,
2306 os1.operation_lead_time_percent,
2307 os1.minimum_transfer_quantity,
2308 os1.count_point_type,
2309 os1.operation_description,
2310 trunc(sysdate), /* effective date */
2311 null, /* disable date */
2312 os1.backflush_flag,
2313 2, /* option_dependent_flag */
2314 os1.attribute_category,
2315 os1.attribute1,
2316 os1.attribute2,
2317 os1.attribute3,
2318 os1.attribute4,
2319 os1.attribute5,
2320 os1.attribute6,
2321 os1.attribute7,
2322 os1.attribute8,
2323 os1.attribute9,
2324 os1.attribute10,
2325 os1.attribute11,
2326 os1.attribute12,
2327 os1.attribute13,
2328 os1.attribute14,
2329 os1.attribute15,
2330 os1.operation_sequence_id, /* using request_id -> model op seq id */
2331 1, /* program_application_id */
2332 1, /* program_id */
2333 sysdate, /* program_update_date */
2334 reference_flag,
2335 nvl(operation_type,1),
2336 process_op_seq_id,
2337 line_op_seq_id,
2338 yield,
2339 cumulative_yield,
2340 reverse_cumulative_yield,
2341 labor_time_calc,
2342 machine_time_calc,
2343 total_time_calc,
2344 labor_time_user,
2345 machine_time_user,
2346 total_time_user,
2347 net_planning_percent,
2348 trunc(sysdate), -- new column for 11.5.4 BOM patchset
2349 os1.x_coordinate, --bugfix 1765149
2350 os1.y_coordinate --bugfix 1765149
2351 from
2352 bom_operation_sequences_temp os1
2353 where os1.config_routing_id = lnewCfgRtgId /*Bugfix 1906371 - change lCfgRtgId to lnewCfgRtgId */
2354 -- bugfix 3239267: replaced last_update_login
2355 and os1.operation_seq_num not in (
2356 select operation_seq_num
2357 from bom_operation_sequences bos1
2358 where bos1.routing_sequence_id = lCfgRtgId
2359 /* Bugfix 1983384 where bos1.last_update_login = lnewCfgRtgId */
2360 and nvl(bos1.operation_type,1) = nvl(os1.operation_type,1));
2361
2362 -- 3093686
2363 IF PG_DEBUG <> 0 THEN
2364 oe_debug_pub.add ('create_routing_ml: ' || ' Inserted ' || sql%rowcount || 'Records ',2 );
2365 END IF;
2366
2367
2368 -- New update of 3180827
2369 lStmtNum := 95;
2370 lStmtNum := 95;
2371 delete from bom_operation_sequences_temp
2372 where config_routing_id in (lCfgRtgId, lmodnewcfgrtgid);
2373
2374 /*-------------------------------------------------------------------+
2375 Now update the process_op_seq_id and line_seq_id of
2376 all events to new operations sequence Ids (map).
2377 Old operation_sequence_ids are available in request_id
2378 +-------------------------------------------------------------------*/
2379
2380 lStmtNum := 100;
2381 xTableName := 'BOM_OPERATION_SEQUENCES';
2382 -- bug 6087687: Events from option class routing operations also need to
2383 -- be linked to operations on config routing.
2384 /***********************************************************************
2385 update bom_operation_sequences bos1
2386 set process_op_seq_id = (
2387 select operation_sequence_id
2388 from bom_operation_sequences bos2
2389 where bos1.process_op_seq_id = bos2.request_id
2390 and bos2.routing_sequence_id = lCfgRtgId)
2391 where bos1.operation_type = 1
2392 and bos1.routing_sequence_id = lCfgRtgId;
2393
2394 lStmtNum := 110;
2395 update bom_operation_sequences bos1
2396 set line_op_seq_id = (
2397 select operation_sequence_id
2398 from bom_operation_sequences bos2
2399 where bos1.line_op_seq_id = bos2.request_id
2400 and bos2.routing_sequence_id = lCfgRtgId)
2401 where bos1.operation_type = 1
2402 and bos1.routing_sequence_id = lCfgRtgId;
2403 ***************************************************************************/
2404
2405 update bom_operation_sequences bos1
2406 set line_op_seq_id = (
2407 select bos2.operation_sequence_id
2408 from bom_operation_sequences bos2,
2409 bom_operation_sequences bos3
2410 where bos3.operation_sequence_id = bos1.line_op_seq_id
2411 and bos2.routing_sequence_id = lCfgRtgId
2412 and bos3.operation_seq_num = bos2.operation_seq_num
2413 and bos2.operation_type = 3)
2414 where bos1.operation_type = 1
2415 and bos1.routing_sequence_id = lCfgRtgId;
2416
2417 lStmtNum := 110;
2418 update bom_operation_sequences bos1
2419 set process_op_seq_id = (
2420 select bos2.operation_sequence_id
2421 from bom_operation_sequences bos2,
2422 bom_operation_sequences bos3
2423 where bos3.operation_sequence_id = bos1.process_op_seq_id
2424 and bos2.routing_sequence_id = lCfgRtgId
2425 and bos3.operation_seq_num = bos2.operation_seq_num
2426 and bos2.operation_type = 2)
2427 where bos1.operation_type = 1
2428 and bos1.routing_sequence_id = lCfgRtgId;
2429
2430 -- end bug 6087687
2431
2432
2433 /*-----------------------------------------------------------+
2434 Delete routing from routing header if
2435 there is no operation associated with the routing
2436 +-----------------------------------------------------------*/
2437
2438 lStmtNum := 120;
2439 xTableName := 'BOM_OPERATIONAL_ROUTINGS';
2440
2441 delete from BOM_OPERATIONAL_ROUTINGS b1
2442 where b1.routing_sequence_id not in
2443 (select routing_sequence_id
2444 from bom_operation_sequences )
2445 and b1.routing_sequence_id = lCfgRtgId;
2446
2447 if sql%rowcount > 0 then
2448 IF PG_DEBUG <> 0 THEN
2449 oe_debug_pub.add ('create_routing_ml: ' || 'No operations were copied, config routing deleted. ',2);
2450 END IF;
2451 return(1);
2452 end if;
2453
2454
2455 /*--------------------------------------------------------------+
2456 If there is a operation_seq_num associated with
2457 the config component which not belong to the
2458 config routing, the operation_seq_num will be
2459 set to 1.
2460 +--------------------------------------------------------------*/
2461 lStmtNum := 130;
2462 xTableName := 'BOM_INVENTORY_COMPS_INTERFACE';
2463
2464 update bom_inventory_comps_interface ci
2465 set ci.operation_seq_num = 1
2466 where not exists
2467 (select 'op seq exists in config routing'
2468 from
2469 bom_operation_sequences bos,
2470 bom_operational_routings bor
2471 where bos.operation_seq_num = ci.operation_seq_num
2472 and bos.routing_sequence_id = bor.routing_sequence_id
2473 and bor.assembly_item_id = pConfigId
2474 and bor.organization_id = pOrgId
2475 and bor.alternate_routing_designator is null)
2476 and ci.bill_sequence_id = pCfgBillId;
2477
2478
2479 /*-----------------------------------------------------+
2480 Process routing revision table
2481 +-----------------------------------------------------*/
2482 lStmtNum := 70;
2483 xTableName := 'MTL_RTG_ITEM_REVISIONS';
2484
2485 IF PG_DEBUG <> 0 THEN
2486 oe_debug_pub.add ('create_routing_ml: ' || 'Inserting into mtl_rtg_item_revisions..',5);
2487 END IF;
2488 insert into MTL_RTG_ITEM_REVISIONS
2489 (
2490 inventory_item_id,
2491 organization_id,
2492 process_revision,
2493 last_update_date,
2494 last_updated_by,
2495 creation_date,
2496 created_by,
2497 last_update_login,
2498 change_notice ,
2499 ecn_initiation_date,
2500 implementation_date,
2501 implemented_serial_number,
2502 effectivity_date ,
2503 attribute_category,
2504 attribute1 ,
2505 attribute2,
2506 attribute3,
2507 attribute4,
2508 attribute5,
2509 attribute6,
2510 attribute7,
2511 attribute8,
2512 attribute9,
2513 attribute10,
2514 ATTRIBUTE11,
2515 ATTRIBUTE12,
2516 ATTRIBUTE13 ,
2517 ATTRIBUTE14,
2518 ATTRIBUTE15
2519 )
2520 select
2521 bor.assembly_item_id,
2522 bor.organization_id,
2523 mp.starting_revision,
2524 sysdate, /* LAST_UPDATE_DATE */
2525 gUserId, /* LAST_UPDATED_BY */
2526 sysdate, /* CREATION_DATE */
2527 gUserId, /* created_by */
2528 gLoginId, /* last_update_login */
2529 NULL, /* CHANGE_NOTICE */
2530 NULL, /* ECN_INITIATION_DATE */
2531 TRUNC(SYSDATE), /* IMPLEMENTATION_DATE */
2532 NULL, /* IMPLEMENTED_SERIAL_NUMBER */
2533 TRUNC(SYSDATE), /* EFFECTIVITY_DATE */
2534 NULL, /* ATTRIBUTE_CATEGORY */
2535 NULL, /* ATTRIBUTE1 */
2536 NULL, /* ATTRIBUTE2 */
2537 NULL, /* ATTRIBUTE3 */
2538 NULL, /* ATTRIBUTE4 */
2539 NULL, /* ATTRIBUTE5 */
2540 NULL, /* ATTRIBUTE6 */
2541 NULL, /* ATTRIBUTE7 */
2542 NULL, /* ATTRIBUTE8 */
2543 NULL, /* ATTRIBUTE9 */
2544 NULL, /* ATTRIBUTE10 */
2545 NULL, /* ATTRIBUTE11 */
2546 NULL, /* ATTRIBUTE12 */
2547 NULL, /* ATTRIBUTE13 */
2548 NULL, /* ATTRIBUTE14 */
2549 NULL /* ATTRIBUTE15 */
2550 from bom_operational_routings bor,
2551 mtl_parameters mp
2552 where bor.routing_sequence_id = lCfgRtgId
2553 and bor.organization_id = mp.organization_id;
2554
2555 /*------------------------------------------------+
2556 ** Load operation resources table
2557 ** 3 new columns added for WIP Simultaneous Resources
2558 +-------------------------------------------------*/
2559
2560 xTableName := 'BOM_OPERATION_RESOURCES';
2561 lStmtNum := 150;
2562
2563 IF PG_DEBUG <> 0 THEN
2564 oe_debug_pub.add ('create_routing_ml: ' || 'Inserting into bom_operation_resources..',5);
2565 END IF;
2566 insert into BOM_OPERATION_RESOURCES
2567 (
2568 operation_sequence_id,
2569 resource_seq_num,
2570 resource_id ,
2571 activity_id,
2572 standard_rate_flag,
2573 assigned_units ,
2574 usage_rate_or_amount,
2575 usage_rate_or_amount_inverse,
2576 basis_type,
2577 schedule_flag,
2578 last_update_date,
2579 last_updated_by,
2580 creation_date,
2581 created_by,
2582 last_update_login,
2583 resource_offset_percent,
2584 autocharge_type,
2585 attribute_category,
2586 attribute1,
2587 attribute2,
2588 attribute3,
2589 attribute4,
2590 attribute5,
2591 attribute6,
2592 attribute7,
2593 attribute8,
2594 attribute9,
2595 attribute10,
2596 attribute11,
2597 attribute12,
2598 attribute13,
2599 attribute14,
2600 attribute15,
2601 request_id,
2602 program_application_id,
2603 program_id,
2604 program_update_date,
2605 schedule_seq_num,
2606 substitute_group_num,
2607 setup_id, /*bugfix2950774*/
2608 principle_flag
2609 )
2610 select
2611 osi.operation_sequence_id, /* operation sequence id */
2612 bor.resource_seq_num,
2613 bor.resource_id,
2614 /* resource id */
2615 bor.activity_id,
2616 bor.standard_rate_flag,
2617 bor.assigned_units,
2618 bor.usage_rate_or_amount,
2619 bor.usage_rate_or_amount_inverse,
2620 bor.basis_type,
2621 bor.schedule_flag,
2622 SYSDATE, /* last update date */
2623 gUserId, /* last updated by */
2624 SYSDATE, /* creation date */
2625 gUserId, /* created by */
2626 1, /* last update login */
2627 bor.resource_offset_percent,
2628 bor.autocharge_type,
2629 bor.attribute_category,
2630 bor.attribute1,
2631 bor.attribute2,
2632 bor.attribute3,
2633 bor.attribute4,
2634 bor.attribute5,
2635 bor.attribute6,
2636 bor.attribute7,
2637 bor.attribute8,
2638 bor.attribute9,
2639 bor.attribute10,
2640 bor.attribute11,
2641 bor.attribute12,
2642 bor.attribute13,
2643 bor.attribute14,
2644 bor.attribute15,
2645 NULL, /* request_id */
2646 NULL, /* program_application_id */
2647 NULL, /* program_id */
2648 NULL, /* program_update_date */
2649 bor.schedule_seq_num,
2650 bor.substitute_group_num,
2651 bor.setup_id, /* Bugfix2950774 */
2652 bor.principle_flag
2653 from
2654 bom_operation_sequences osi,
2655 bom_operation_resources bor
2656 where osi.routing_sequence_id = lCfgRtgId
2657 and osi.request_id = bor.operation_sequence_id;
2658 /* request_id contains model op seq_id now */
2659
2660
2661
2662 /*------------------------------------------------+
2663 ** Load sub operation resources table
2664 ** new table for WIP Simultaneous Resources
2665 +-------------------------------------------------*/
2666 xTableName := 'BOM_SUB_OPERATION_RESOURCES';
2667 lStmtNum := 155;
2668
2669 IF PG_DEBUG <> 0 THEN
2670 oe_debug_pub.add ('create_routing_ml: ' || 'Inserting into bom_sub_operation_resources ..',5);
2671 END IF;
2672 insert into BOM_SUB_OPERATION_RESOURCES
2673 (operation_sequence_id,
2674 substitute_group_num,
2675 --resource_seq_num,
2676 resource_id,
2677 --scheduling_seq_num,
2678 schedule_seq_num,
2679 replacement_group_num,
2680 activity_id,
2681 standard_rate_flag,
2682 assigned_units,
2683 usage_rate_or_amount,
2684 usage_rate_or_amount_inverse,
2685 basis_type,
2686 schedule_flag,
2687 last_update_date,
2688 last_updated_by,
2689 creation_date,
2690 created_by,
2691 last_update_login,
2692 resource_offset_percent,
2693 autocharge_type,
2694 principle_flag,
2695 attribute_category,
2696 attribute1,
2697 attribute2,
2698 attribute3,
2699 attribute4,
2700 attribute5,
2701 attribute6,
2702 attribute7,
2703 attribute8,
2704 attribute9,
2705 attribute10,
2706 attribute11,
2707 attribute12,
2708 attribute13,
2709 attribute14,
2710 attribute15,
2711 setup_id, /* bugfix2950774 */
2712 request_id,
2713 program_application_id,
2714 program_id,
2715 program_update_date
2716 )
2717 select
2718 osi.operation_sequence_id,
2719 bsor.substitute_group_num,
2720 --bsor.resource_seq_num,
2721 bsor.resource_id,
2722 --bsor.scheduling_seq_num,
2723 bsor.schedule_seq_num,
2724 bsor.replacement_group_num,
2725 bsor.activity_id,
2726 bsor.standard_rate_flag,
2727 bsor.assigned_units,
2728 bsor.usage_rate_or_amount,
2729 bsor.usage_rate_or_amount_inverse,
2730 bsor.basis_type,
2731 bsor.schedule_flag,
2732 SYSDATE, /*last_update_date*/
2733 gUserId, /*last_updated_by*/
2734 SYSDATE, /*creation_date*/
2735 gUserId, /*created_by*/
2736 1, /*last_update_login*/
2737 bsor.resource_offset_percent,
2738 bsor.autocharge_type,
2739 bsor.principle_flag,
2740 bsor.attribute_category,
2741 bsor.attribute1,
2742 bsor.attribute2,
2743 bsor.attribute3,
2744 bsor.attribute4,
2745 bsor.attribute5,
2746 bsor.attribute6,
2747 bsor.attribute7,
2748 bsor.attribute8,
2749 bsor.attribute9,
2750 bsor.attribute10,
2751 bsor.attribute11,
2752 bsor.attribute12,
2753 bsor.attribute13,
2754 bsor.attribute14,
2755 bsor.attribute15,
2756 bsor.setup_id, /* bugfix2950774 */
2757 NULL, /*request_id*/
2758 NULL, /*program_application_id*/
2759 NULL, /*program_id*/
2760 NULL /*program_update_date*/
2761 from
2762 bom_operation_sequences osi,
2763 bom_sub_operation_resources bsor
2764 where osi.routing_sequence_id = lCfgRtgId
2765 and osi.request_id = bsor.operation_sequence_id;
2766 /* request_id contains model op seq_id now */
2767
2768
2769 /*---------------------------------------------------+
2770 ** Process operation Networks table
2771 +---------------------------------------------------*/
2772 lStmtNum := 380;
2773 xTableName := 'BOM_OPERATION_NETWORKS';
2774
2775 IF PG_DEBUG <> 0 THEN
2776 oe_debug_pub.add ('create_routing_ml: ' || 'Inserting into bom_operation_networks ..',5);
2777 END IF;
2778 INSERT INTO bom_operation_networks
2779 ( FROM_OP_SEQ_ID,
2780 TO_OP_SEQ_ID,
2781 TRANSITION_TYPE,
2782 PLANNING_PCT,
2783 EFFECTIVITY_DATE,
2784 DISABLE_DATE,
2785 CREATED_BY,
2786 CREATION_DATE,
2787 LAST_UPDATED_BY,
2788 LAST_UPDATE_DATE,
2789 LAST_UPDATE_LOGIN,
2790 ATTRIBUTE_CATEGORY,
2791 ATTRIBUTE1 ,
2792 ATTRIBUTE2 ,
2793 ATTRIBUTE3 ,
2794 ATTRIBUTE4 ,
2795 ATTRIBUTE5 ,
2796 ATTRIBUTE6 ,
2797 ATTRIBUTE7 ,
2798 ATTRIBUTE8 ,
2799 ATTRIBUTE9 ,
2800 ATTRIBUTE10 ,
2801 ATTRIBUTE11 ,
2802 ATTRIBUTE12 ,
2803 ATTRIBUTE13 ,
2804 ATTRIBUTE14 ,
2805 ATTRIBUTE15
2806 )
2807 SELECT
2808 bos3.operation_sequence_id,
2809 bos4.operation_sequence_id,
2810 bon.TRANSITION_TYPE,
2811 bon.PLANNING_PCT,
2812 bon.EFFECTIVITY_DATE,
2813 bon.DISABLE_DATE,
2814 bon.CREATED_BY,
2815 bon.CREATION_DATE,
2816 bon.LAST_UPDATED_BY,
2817 bon.LAST_UPDATE_DATE,
2818 bon.LAST_UPDATE_LOGIN,
2819 bon.ATTRIBUTE_CATEGORY,
2820 bon.ATTRIBUTE1,
2821 bon.ATTRIBUTE2,
2822 bon.ATTRIBUTE3,
2823 bon.ATTRIBUTE4,
2824 bon.ATTRIBUTE5,
2825 bon.ATTRIBUTE6,
2826 bon.ATTRIBUTE7,
2827 bon.ATTRIBUTE8,
2828 bon.ATTRIBUTE9,
2829 bon.ATTRIBUTE10,
2830 bon.ATTRIBUTE11,
2831 bon.ATTRIBUTE12,
2832 bon.ATTRIBUTE13,
2833 bon.ATTRIBUTE14,
2834 bon.ATTRIBUTE15
2835 FROM bom_operation_networks bon,
2836 bom_operation_sequences bos1, /* 'from' Ops of model */
2837 bom_operation_sequences bos2, /* 'to' Ops of model */
2838 bom_operation_sequences bos3, /* 'from' Ops of config */
2839 bom_operation_sequences bos4, /* 'to' Ops of config */
2840 bom_operational_routings brif
2841 WHERE bon.from_op_seq_id = bos1.operation_sequence_id
2842 AND bon.to_op_seq_id = bos2.operation_sequence_id
2843 AND bos1.routing_sequence_id = bos2.routing_sequence_id
2844 AND bos3.routing_sequence_id = brif.routing_sequence_id
2845 AND brif.cfm_routing_flag = 1
2846 AND brif.routing_sequence_id = lCfgrtgId
2847 AND bos3.operation_seq_num = bos1.operation_seq_num
2848 AND NVL(bos3.operation_type,1) = NVL(bos1.operation_type, 1)
2849 AND bos4.routing_sequence_id = bos3.routing_sequence_id
2850 AND bos4.operation_seq_num = bos2.operation_seq_num
2851 AND NVL(bos4.operation_type,1) = NVL(bos2.operation_type, 1)
2852 AND bos1.routing_sequence_id = ( /* find the model routing */
2853 select common_routing_sequence_id --5103316
2854 from bom_operational_routings bor,
2855 mtl_system_items msi
2856 where brif.assembly_item_id = msi.inventory_item_id
2857 and brif.organization_id = msi.organization_id
2858 and bor.assembly_item_id = msi.base_item_id
2859 and bor.organization_id = msi.organization_id
2860 and bor.cfm_routing_flag = 1
2861 and bor.alternate_routing_designator is null );
2862
2863
2864 IF PG_DEBUG <> 0 THEN
2865 oe_debug_pub.add ('create_routing_ml: ' || xTableName || '-'|| lStmtNum || ': ' || sql%rowcount,2 );
2866 END IF;
2867
2868 lstmtNum := 390;
2869
2870 --
2871 -- Check if flow_manufacturing is installed
2872 --
2873
2874 l_install_cfm := FND_INSTALLATION.Get_App_Info(application_short_name => 'FLM',
2875 status => l_status,
2876 industry => l_industry,
2877 oracle_schema => l_schema);
2878
2879 --
2880 -- For each operation in the routing, copy attachments of operations
2881 -- copied from model/option class to operations on the config item
2882 --
2883
2884 for nextop in allops loop
2885
2886 lstmtNum := 400;
2887
2888 FND_ATTACHED_DOCUMENTS2_PKG.copy_attachments(
2889 X_from_entity_name =>'BOM_OPERATION_SEQUENCES',
2890 X_from_pk1_value =>nextop.request_id,
2891 X_from_pk2_value =>'',
2892 X_from_pk3_value =>'',
2893 X_from_pk4_value =>'',
2894 X_from_pk5_value =>'',
2895 X_to_entity_name =>'BOM_OPERATION_SEQUENCES',
2896 X_to_pk1_value =>nextop.operation_sequence_id,
2897 X_to_pk2_value =>'',
2898 X_to_pk3_value =>'',
2899 X_to_pk4_value =>'',
2900 X_to_pk5_value =>'',
2901 X_created_by =>1,
2902 X_last_update_login =>'',
2903 X_program_application_id =>'',
2904 X_program_id =>'',
2905 X_request_id =>''
2906 );
2907 end loop;
2908
2909 lstmtNum := 410;
2910 select nvl(cfm_routing_flag,2)
2911 into lCfmRtgFlag
2912 from bom_operational_routings
2913 where routing_sequence_id = lCfgrtgId;
2914
2915
2916 --
2917 -- if flow manufacturing is installed and the 'Perform Flow Calulations'
2918 -- parameter is set to 2 or 3 (perform calculations based on processes or perform
2919 -- calulations based on Line operations) the routing is 'flow routing' then
2920 -- calculate operation times, yields, net planning percent and total
2921 -- product cycle time for config routing
2922 --
2923
2924
2925 lstmtNum := 410;
2926 if ( l_status = 'I' and pFlowCalc >1 and lCfmRtgflag = 1 ) then
2927
2928 --
2929 -- Calculate Operation times
2930 --
2931
2932 BOM_CALC_OP_TIMES_PK.calculate_operation_times(
2933 arg_org_id => pOrgId,
2934 arg_routing_sequence_id => lcfgRtgId);
2935
2936 --
2937 -- Calculate cumu yield, rev cumu yield and net plannning percent
2938 --
2939
2940 BOM_CALC_CYNP.calc_cynp(
2941 p_routing_sequence_id => lcfgRtgId,
2942 p_operation_type => pFlowCalc, /* operation_type = process */
2943 p_update_events => 1 ); /* update events */
2944
2945 --
2946 -- Calculate total_product_cycle_time
2947 --
2948
2949 BOM_CALC_TPCT.calculate_tpct(
2950 p_routing_sequence_id => lcfgRtgId,
2951 p_operation_type => pFlowCalc); /* Operation_type = Process */
2952 end if;
2953
2954 -- Feature :Serial tracking in wip
2955 -- LOgic : serial tracking is enabled only when serial control mode is 'pre-defined' (ie 2)
2956 -- If model serialization_start_op seq is not populated, we will copy the minimum 'seriallization_start_op'
2957 -- of OC's chosen
2958 --modified by kkonada
2959
2960
2961 if( lCfmRtgFlag = 1) then ---flow doesnot support serial tracking
2962 null;
2963 else
2964 lstmtNum := 411;
2965 Select serial_number_control_code
2966 into l_ser_code
2967 from mtl_System_items
2968 where inventory_item_id = pModelId
2969 and organization_id =pOrgId;
2970
2971 IF PG_DEBUG <> 0 THEN
2972 oe_debug_pub.add('create_routing_ml: ' || 'serial_number_control_code of model is '||l_ser_code , 4);
2973 END IF;
2974
2975 if ( l_ser_code = 2) then --serialized ,pre-defined
2976
2977 lstmtNum := 412;
2978
2979 IF PG_DEBUG <> 0 THEN
2980 oe_debug_pub.add('create_routing_ml: ' || 'select serial start op from model ' , 4);
2981 END IF;
2982
2983
2984 BEGIN
2985 --will select serial start op of model, only if effective on the day
2986 --as routing generation takes care of eefectivity, we check if op seq is present in config routing
2987 select serialization_start_op
2988 into l_ser_start_op
2989 from bom_operational_routings
2990 where assembly_item_id = pModelId
2991 and alternate_routing_designator is null
2992 and organization_id = pOrgId
2993 and serialization_start_op in
2994 (Select OPERATION_SEQ_NUM
2995 from bom_operation_sequences
2996 where routing_sequence_id = lCfgRtgId
2997 );
2998 EXCEPTION
2999 WHEN no_data_found THEN
3000 l_ser_start_op := NULL;
3001 END;
3002
3003 IF PG_DEBUG <> 0 THEN
3004 oe_debug_pub.add('create_routing_ml: ' || 'l_ser_start_op ie serialization_start_op from model is '|| l_ser_start_op, 4);
3005 END IF;
3006
3007 if(l_ser_start_op is null)then
3008
3009 lstmtNum := 413;
3010 IF PG_DEBUG <> 0 THEN
3011 oe_debug_pub.add('create_routing_ml: ' || 'before updating config routing with serial start op of option class', 4);
3012 END IF;
3013
3014 begin
3015 update bom_operational_routings
3016 set serialization_start_op =
3017 ( select min( serialization_start_op)
3018 from bom_operational_routings
3019 where organization_id = pOrgId
3020 and alternate_routing_designator is null
3021 and assembly_item_id in
3022 ( select component_item_id
3023 from bom_inventory_comps_interface
3024 where bom_item_type =2
3025 and bill_sequence_id = pCfgBillId
3026 )
3027 and serialization_start_op in
3028 (Select OPERATION_SEQ_NUM
3029 from bom_operation_sequences
3030 where routing_sequence_id = lCfgRtgId
3031 )--serial start op exists as a operation in routing(ie effective oper)
3032 )
3033 where assembly_item_id = pConfigId
3034 and alternate_routing_designator is null
3035 and organization_id = pOrgId;
3036
3037 l_row_count := sql%rowcount;
3038 exception
3039 when no_data_found then
3040
3041 IF PG_DEBUG <> 0 THEN
3042 oe_debug_pub.add('create_routing_ml: ' || 'No option classes chosen while creating coonfiguration ', 4);
3043 END IF;
3044
3045 end;
3046
3047 IF PG_DEBUG <> 0 THEN
3048 oe_debug_pub.add('create_routing_ml: ' || 'no# config rows rows updated with OC serial start opseq->'||l_row_count, 4);
3049 END IF;
3050
3051 else --model has serial start op seq
3052
3053 lstmtNum := 414;
3054 update bom_operational_routings
3055 set serialization_start_op = l_ser_start_op
3056 where routing_sequence_id = lCfgRtgId ;
3057
3058 IF PG_DEBUG <> 0 THEN
3059 oe_debug_pub.add('create_routing_ml: ' || 'updated with serial start op of model, serial start op =>'||l_ser_start_op , 4);
3060 END IF;
3061
3062
3063 end if;--l_ser_start_op
3064
3065
3066 end if;--l_ser_code
3067
3068
3069 end if;
3070
3071 xRtgId := lCfgRtgId;
3072
3073 return (1);
3074
3075 EXCEPTION
3076 when no_data_found then
3077
3078 xErrorMessage := 'CTOCRTGB:'||to_char(lStmtNum)||'raised NDF ';
3079 xMessageName := 'CTO_CREATE_ROUTING_ERROR';
3080 xRtgId := 0;
3081 IF PG_DEBUG <> 0 THEN
3082 oe_debug_pub.add('create_routing_ml: ' || xErrorMessage, 1);
3083 END IF;
3084 return(0);
3085
3086 when FND_API.G_EXC_ERROR then
3087
3088 xErrorMessage := 'CTOCRTGB:'||to_char(lStmtNum)||' raised expected error.';
3089 xMessageName := 'CTO_CREATE_ROUTING_ERROR';
3090 xRtgId := 0;
3091 IF PG_DEBUG <> 0 THEN
3092 oe_debug_pub.add('create_routing_ml: ' || xErrorMessage, 1);
3093 END IF;
3094 return(0);
3095
3096 when FND_API.G_EXC_UNEXPECTED_ERROR then
3097
3098 xErrorMessage := 'CTOCRTGB:'||to_char(lStmtNum)||' raised unexpected error.';
3099 xMessageName := 'CTO_CREATE_ROUTING_ERROR';
3100 xRtgId := 0;
3101 IF PG_DEBUG <> 0 THEN
3102 oe_debug_pub.add('create_routing_ml: ' || xErrorMessage, 1);
3103 END IF;
3104 return(0);
3105
3106 when others then
3107 xErrorMessage := 'CTOCRTGB:'||to_char(lStmtNum)||'raised OTHERS exception.';
3108 xMessageName := 'CTO_CREATE_ROUTING_ERROR';
3109 IF PG_DEBUG <> 0 THEN
3110 oe_debug_pub.add ('create_routing_ml: ' || xErrorMessage, 1);
3111
3112 oe_debug_pub.add ('create_routing_ml: ' || 'Error Message : '||sqlerrm);
3113 END IF;
3114 return(0);
3115
3116 END create_routing_ml;
3117
3118 END CTO_CONFIG_ROUTING_PK;