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