[Home] [Help]
PACKAGE BODY: APPS.BOMPLDCB
Source
1 package body BOMPLDCB as
2 /* $Header: BOMLDCBB.pls 120.1 2005/06/21 00:06:01 appldev ship $ */
3 function bmlggpn_get_group_name
4 ( group_id number,
5 group_name in out nocopy /* file.sql.39 change */ varchar2,
6 err_buf in out nocopy /* file.sql.39 change */ varchar2
7 )
8 return integer
9 is
10 max_seg number;
11 stmt_num number;
12 type segvalueType is table of varchar2(30)
13 index by binary_integer;
14 seg_value segvalueType;
15 segvalue_tmp varchar2(30);
16 segnum_tmp number;
17 catseg_value varchar2(240);
18 delimiter varchar2(10);
19 profile_setting varchar2(30);
20 CURSOR profile_check IS
21 select nvl(substr(profile_option_value,1,30),'N')
22 from fnd_profile_option_values val,fnd_profile_options op
23 where op.application_id = 401
24 and op.profile_option_name = 'USE_NAME_ICG_DESC'
25 and val.level_id = 10001 /* This is for site level */
26 and val.application_id = op.application_id
27 and val.profile_option_id = op.profile_option_id;
28 begin
29 /* First lets get the value for profile option USE_NAME_ICG_DESC
30 ** If this is 'N' we need to use the description
31 ** If this is 'Y' then we need to use the group name
32 ** We are going to stick with group name if the customer is
33 ** not on R10.5, which means they do not have the profile
34 ** If they have R10.5 then we are going to use description
35 ** because that is what inventory is going to do.
36 ** Remember at the earliest we should get rid of this function
37 ** and call INV API. Remember we at ATO are not in the business
38 ** of duplicating code of other teams
39 */
40
41 profile_setting := 'Y';
42
43 OPEN profile_check;
44 FETCH profile_check INTO profile_setting;
45 IF profile_check%NOTFOUND THEN
46 profile_setting := 'Y';
47 END IF;
48
49
50 if profile_setting = 'Y' then
51
52 /* Let us select the catalog group name from mtl_catalog_groups
53 ** At some point in time we need to call the inventory function
54 ** to do this, so we can centralize this stuff
55 */
56 stmt_num :=100;
57
58 SELECT MICGK.concatenated_segments
59 INTO group_name
60 FROM mtl_item_catalog_groups_kfv MICGK
61 WHERE MICGK.item_catalog_group_id = group_id;
62
63 else
64 /* This is to get the description of the catalog */
65 SELECT MICG.description
66 INTO group_name
67 FROM mtl_item_catalog_groups MICG
68 WHERE MICG.item_catalog_group_id = group_id;
69
70 end if;
71 return(0);
72 exception
73 when others then
74 err_buf := 'BMLGGPN: ' || substrb(SQLERRM,1,150);
75 return(SQLCODE);
76 end bmlggpn_get_group_name;
77
78
79 function bmlupid_update_item_desc
80 (
81 item_id NUMBER,
82 org_id NUMBER,
83 err_buf in out nocopy /* file.sql.39 change */ VARCHAR2
84 )
85 return integer
86 is
87 /*
88 ** Create cursor to retrieve all descriptive element values for the item
89 */
90 CURSOR cc is
91 select element_value
92 from mtl_desc_elem_val_interface
93 where inventory_item_id = item_id
94 and element_value is not NULL
95 and default_element_flag = 'Y'
96 order by element_sequence;
97 delimiter varchar2(10);
98 e_value varchar2(30);
99 cat_value varchar2(240);
100 idx number;
101 group_id number;
102 group_name varchar2(30);
103 status number;
104 INV_GRP_ERROR exception;
105 begin
106
107 select concatenated_segment_delimiter into delimiter
108 from fnd_id_flex_structures
109 where id_flex_code = 'MICG'
110 and application_id = 401;
111
112 select item_catalog_group_id into group_id
113 from mtl_system_items_interface
114 where inventory_item_id = item_id
115 and organization_id = org_id;
116
117 idx := 0;
118 cat_value := '';
119 open cc;
120 loop
121 fetch cc into e_value;
122 exit when (cc%notfound);
123
124 if idx = 0 then
125 status := bmlggpn_get_group_name(group_id,group_name,
126 err_buf);
127 if status <> 0 then
128 raise INV_GRP_ERROR;
129 end if;
130 cat_value := group_name || delimiter || e_value;
131 else
132 cat_value := cat_value || SUBSTRB(delimiter || e_value,1,
133 240-LENGTHB(cat_value));
134 end if;
135 idx := idx + 1;
136 end loop;
137 close cc;
138
139 if idx <> 0 then
140 update mtl_system_items_interface
141 set description = cat_value
142 where inventory_item_id = item_id
143 and organization_id = org_id;
144 end if;
145
146 return(0);
147 exception
148 when INV_GRP_ERROR then
149 err_buf := 'BMLUPID: Invalid catalog group for the item ' || item_id || ' status:' || status;
150 return(status);
151 when OTHERS then
152 err_buf := 'BMLUPID: ' || substrb(SQLERRM,1,150);
153 return(SQLCODE);
154
155 END bmlupid_update_item_desc;
156
157 function bmldbrt_load_bom_rtg (
158 inherit_check in number,
159 error_message in out nocopy /* file.sql.39 change */ VARCHAR2,
160 message_name in out nocopy /* file.sql.39 change */ VARCHAR2,
161 table_name in out nocopy /* file.sql.39 change */ VARCHAR2)
162 return integer
163 is
164 /*
165 ** declare cursor for fetching all the duplicated rows
166 */
167 CURSOR cc IS
168 select distinct
169 b1.bill_sequence_id,
170 b1.operation_seq_num,
171 b1.component_sequence_id,
172 b1.component_item_id,
173 b1.component_quantity
174 from
175 BOM_INVENTORY_COMPS_INTERFACE b1,
176 BOM_INVENTORY_COMPS_INTERFACE b2,
177 BOM_BILL_OF_MTLS_INTERFACE b3
178 where
179 b1.bill_sequence_id = b2.bill_sequence_id
180 and b1.component_sequence_id <> b2.component_sequence_id
181 and b1.operation_seq_num = b2.operation_seq_num
182 and b1.component_item_id = b2.component_item_id
183 and b1.bill_sequence_id = b3.bill_sequence_id
184 and b3.set_id = TO_CHAR(to_number(USERENV('SESSIONID')))
185 order by b1.bill_sequence_id,
186 b1.component_item_id,
187 b1.operation_seq_num,
188 b1.component_quantity,
189 b1.component_sequence_id;
190
191 /*
192 ** declare cursor to handle duplicated op sequences
193 ** Operation type is being fetched since same operation sequences
194 ** can exist in different operation types. (event, line Op, process etc)
195 ** Item type is being fetched because we need to distinguish between
196 ** Operations inherited from a model vs operations inherited from a class
197 ** Important: Item type is stored in the last_update_login column
198 */
199 CURSOR dd IS
200 select distinct
201 b1.operation_sequence_id,
202 b1.operation_seq_num,
203 b1.operation_type,
204 b1.routing_sequence_id,
205 b1.last_update_login
206 from BOM_OP_SEQUENCES_INTERFACE b1,
207 BOM_OP_SEQUENCES_INTERFACE b2,
208 BOM_OP_ROUTINGS_INTERFACE r2
209 where r2.set_id = TO_CHAR(to_number(USERENV('SESSIONID')))
210 and b2.routing_sequence_id = r2.routing_sequence_id
211 and b1.routing_sequence_id = b2.routing_sequence_id
212 and b1.operation_sequence_id <> b2.operation_sequence_id
213 and b1.operation_seq_num = b2.operation_seq_num
214 and b1.operation_type = b2.operation_type
215 order by b1.routing_sequence_id,b1.operation_seq_num,b1.operation_type,b1.last_update_login;
216
217 /*
218 ** declare cursor to retrieve config items
219 */
220 CURSOR ee IS
221 select inventory_item_id, organization_id
222 from mtl_system_items_interface
223 where set_id = TO_CHAR(to_number(USERENV('SESSIONID')));
224
225 routing_id NUMBER;
226 bill_id NUMBER;
227 item_type NUMBER;
228 op_seq_num NUMBER;
229 op_seq_id NUMBER;
230 op_type NUMBER;
231 item_id NUMBER;
232 comp_seq_id NUMBER;
233 qty NUMBER;
234 save_bill_id NUMBER;
235 save_op_seq_num NUMBER;
236 save_routing_id NUMBER;
237 save_op_seq_id NUMBER;
238 save_op_type NUMBER;
239 save_item_id NUMBER;
240 save_comp_seq_id NUMBER;
241 total_qty NUMBER;
242 org_id NUMBER;
243 status NUMBER;
244 bom_level_indicator NUMBER;
245 rowcount NUMBER;
246 stmt_num NUMBER;
247 UP_DESC_ERR exception;
248
249
250
251 BEGIN
252 /*
253 ** load bom header interface table
254 */
255 table_name := 'BOM_BILL_OF_MTLS_INTERFACE ';
256 stmt_num := 10;
257 insert into BOM_BILL_OF_MTLS_INTERFACE(
258 assembly_item_id,
259 organization_id,
260 alternate_bom_designator,
261 last_update_date,
262 last_updated_by,
263 creation_date,
264 created_by,
265 last_update_login,
266 specific_assembly_comment,
267 pending_from_ecn,
268 attribute_category,
269 attribute1,
270 attribute2,
271 attribute3,
272 attribute4,
273 attribute5,
274 attribute6,
275 attribute7,
276 attribute8,
277 attribute9,
278 attribute10,
279 attribute11,
280 attribute12,
281 attribute13,
282 attribute14,
283 attribute15,
284 assembly_type,
285 bill_sequence_id,
286 common_bill_sequence_id,
287 request_id,
288 program_application_id,
289 program_id,
290 program_update_date,
291 demand_source_line ,
292 demand_source_type,
293 demand_source_header_id,
294 set_id
295 )
296 select
297 m.inventory_item_id, /* assembly_item_id */
298 m.organization_id, /* organization_id */
299 NULL, /* alternate_bom_designator */
300 SYSDATE, /* last_update_date */
301 1, /* last_update_by */
302 SYSDATE, /* creation date */
303 1, /* created by */
304 1, /* last_update_login */
305 NULL, /* specific assembly comment */
306 NULL, /* pending from ecn */
307 NULL, /* attribute category */
308 NULL, /* attribute1 */
309 NULL, /* attribute2 */
310 NULL, /* attribute3 */
311 NULL, /* attribute4 */
312 NULL, /* attribute5 */
313 NULL, /* attribute6 */
314 NULL, /* attribute7 */
315 NULL, /* attribute8 */
316 NULL, /* attribute9 */
317 NULL, /* attribute10 */
318 NULL, /* attribute11 */
319 NULL, /* attribute12 */
320 NULL, /* attribute13 */
321 NULL, /* attribute14 */
322 NULL, /* attribute15 */
323 b.assembly_type, /* assembly_ type */
324 BOM_INVENTORY_COMPONENTS_S.NEXTVAL,
325 BOM_INVENTORY_COMPONENTS_S.CURRVAL,
326 NULL, /* request id */
327 NULL, /* program_application_id */
328 NULL, /* program id */
329 NULL, /* program date */
330 m.demand_source_line, /* this is the additional column
331 to save the source_line */
332 m.demand_source_type,
333 m.demand_source_header_id,
334 m.set_id
335 from
336 BOM_BILL_OF_MATERIALS b,
337 MTL_SYSTEM_ITEMS_INTERFACE m
338 where m.set_id = TO_CHAR(to_number(USERENV('SESSIONID')))
339 and m.copy_item_id = b.assembly_item_id
340 and m.organization_id = b.organization_id
341 and b.alternate_bom_designator is NULL;
342
343
344 /*
345 ** Update the common bill sequence id equal to the
346 ** bill sequence id in the BOM_BILL_OF_MTLS_INTERFACE
347 */
348
349 /*
350 ** This is not required since it is being taken care in
351 ** the insert statement itself using the currval of the
352 ** sequence.
353 **
354 ** stmt_num := 20;
355 ** update BOM_BILL_OF_MTLS_INTERFACE b
356 ** set common_bill_sequence_id = bill_sequence_id
357 ** where (b.common_bill_sequence_id =1 or
358 ** b.common_bill_sequence_id is NULL )
359 ** and b.set_id = TO_CHAR(to_number(USERENV('SESSIONID')));
360 */
361
362 /*
363 ** Update MTL_DEMAND for the children to inherit the
364 ** operation_seq_num of their parents.This works
365 ** for all levels. This is executed only if the
366 ** BOM:CONFIG_INHERIT_OP_SEQ profile option is set to YES
367 */
368
369 IF inherit_check = 1 THEN
370
371 table_name := 'MTL_DEMAND';
372
373 /* Initialize the bom_level columns to NULL */
374
378 where config_group_id = USERENV('SESSIONID');
375 stmt_num := 30;
376 update mtl_demand
377 set bom_level = NULL
379
380 /* Here we are identifying the base model row and
381 ** setting the bom_level to zero.
382 */
383
384 stmt_num := 40;
385 update mtl_demand
386 set bom_level = 0
387 where config_group_id = USERENV('SESSIONID')
388 and demand_type = 1
389 and rto_model_source_line = demand_source_line
390 and primary_uom_quantity <>0
391 and parent_demand_id is NULL;
392
393 bom_level_indicator := 0;
394 rowcount := 1;
395
396 /* Now loop till you find no more rows to process
397 ** and update the bom_level of the children rows
398 */
399
400 WHILE rowcount <> 0 LOOP
401
402 stmt_num := 50;
403 update mtl_demand d0
404 set bom_level = bom_level_indicator + 1,
405 config_group_id = USERENV('SESSIONID'),
406 operation_seq_num =
407 (select distinct
408 decode(NVL(bic1.operation_seq_num,1),
409 1,NVL(d2.operation_seq_num,bic2.operation_seq_num),
410 bic1.operation_seq_num)
411 from bom_inventory_components bic1, /*child */
412 bom_inventory_components bic2, /*parent */
413 mtl_demand d2
414 where bic1.component_sequence_id=d0.component_sequence_id
415 and bic2.component_sequence_id=d0.parent_component_seq_id
416 and d0.parent_component_seq_id=d2.component_sequence_id
417 and d0.rto_model_source_line = d2.rto_model_source_line
418 and d2.primary_uom_quantity <>0
419 and d2.bom_level = bom_level_indicator)
420 where (d0.parent_component_seq_id, d0.rto_model_source_line)in
421 (select d1.component_sequence_id, d1.rto_model_source_line
422 from mtl_demand d1
423 where d1.config_group_id = USERENV('SESSIONID')
424 and d1.primary_uom_quantity <>0
425 and d1.bom_level = bom_level_indicator)
426 and d0.primary_uom_quantity <>0;
427
428 rowcount := SQL%ROWCOUNT;
429 bom_level_indicator := bom_level_indicator + 1;
430
431 END LOOP;
432
433 END IF;
434
435 /*
436 ** Load inventory components interface table
437 */
438
439 /*
440 ** First:
441 ** All the chosen option items associated
442 ** with the new configuration items will be loaded into the
443 ** BOM_INVENTORY_COMPS_INTERFACE table.
444 */
445 table_name := 'BOM_INVENTORY_COMPS_INTERFACE';
446 stmt_num := 60;
447 insert into BOM_INVENTORY_COMPS_INTERFACE
448 (
449 OPERATION_SEQ_NUM,
450 COMPONENT_ITEM_ID,
451 LAST_UPDATE_DATE,
452 LAST_UPDATED_BY,
453 CREATION_DATE,
454 CREATED_BY,
455 LAST_UPDATE_LOGIN,
456 ITEM_NUM,
457 COMPONENT_QUANTITY,
458 COMPONENT_YIELD_FACTOR,
459 COMPONENT_REMARKS,
460 EFFECTIVITY_DATE,
461 CHANGE_NOTICE,
462 IMPLEMENTATION_DATE,
463 DISABLE_DATE,
464 ATTRIBUTE_CATEGORY,
465 ATTRIBUTE1,
466 ATTRIBUTE2,
467 ATTRIBUTE3,
468 ATTRIBUTE4,
469 ATTRIBUTE5,
470 ATTRIBUTE6,
471 ATTRIBUTE7,
472 ATTRIBUTE8,
473 ATTRIBUTE9,
474 ATTRIBUTE10,
475 ATTRIBUTE11,
476 ATTRIBUTE12,
477 ATTRIBUTE13,
478 ATTRIBUTE14,
479 ATTRIBUTE15,
480 PLANNING_FACTOR,
481 QUANTITY_RELATED,
482 SO_BASIS,
483 OPTIONAL,
484 MUTUALLY_EXCLUSIVE_OPTIONS,
485 INCLUDE_IN_COST_ROLLUP,
486 CHECK_ATP,
487 SHIPPING_ALLOWED,
488 REQUIRED_TO_SHIP,
489 REQUIRED_FOR_REVENUE,
490 INCLUDE_ON_SHIP_DOCS,
491 INCLUDE_ON_BILL_DOCS,
492 LOW_QUANTITY,
493 HIGH_QUANTITY,
494 ACD_TYPE,
495 OLD_COMPONENT_SEQUENCE_ID,
496 COMPONENT_SEQUENCE_ID,
497 BILL_SEQUENCE_ID,
498 REQUEST_ID,
499 PROGRAM_APPLICATION_ID,
500 PROGRAM_ID,
501 PROGRAM_UPDATE_DATE,
502 WIP_SUPPLY_TYPE,
503 PICK_COMPONENTS,
504 MODEL_COMP_SEQ_ID,
505 SUPPLY_SUBINVENTORY,
509 decode(NVL(ic1.operation_seq_num,1),1,
506 SUPPLY_LOCATOR_ID
507 )
508 select
510 NVL(d1.operation_seq_num,1),ic1.operation_seq_num),
511 ic1.component_item_id,
512 SYSDATE, /* last_updated_date */
513 1, /* last_updated_by */
514 SYSDATE, /* creation_date */
515 1, /* created_by */
516 1, /* last_update_login */
517 ic1.item_num,
518 d1.primary_uom_quantity /
519 NVL(d2.primary_uom_quantity,1),
520 /* qty = comp_qty / model_qty */
521 ic1.component_yield_factor,
522 NULL, /*ic1.component_remark*/
523 TRUNC(SYSDATE), /* effective date */
524 NULL, /* change notice */
525 SYSDATE, /* implementation_date */
526 NULL, /* disable date */
527 ic1.attribute_category,
528 ic1.attribute1,
529 ic1.attribute2,
530 ic1.attribute3,
531 ic1.attribute4,
532 ic1.attribute5,
533 ic1.attribute6,
534 ic1.attribute7,
535 ic1.attribute8,
536 ic1.attribute9,
537 ic1.attribute10,
538 ic1.attribute11,
539 ic1.attribute12,
540 ic1.attribute13,
541 ic1.attribute14,
542 ic1.attribute15,
543 100, /* planning_factor */
544 2, /* quantity_related */
545 ic1.so_basis,
546 2, /* optional */
547 2, /* mutually_exclusive_options */
548 ic1.include_in_cost_rollup,
549 ic1.check_atp,
550 2, /* shipping_allowed = NO */
551 2, /* required_to_ship = NO */
552 ic1.required_for_revenue,
553 ic1.include_on_ship_docs,
554 ic1.include_on_bill_docs,
555 NULL, /* low_quantity */
556 NULL, /* high_quantity */
557 NULL, /* acd_type */
558 NULL, /*old_component_sequence_id */
559 BOM_INVENTORY_COMPONENTS_S.NEXTVAL,
560 /* component sequence id */
561 b.bill_sequence_id, /* bill sequence id */
562 NULL, /* request_id */
563 NULL, /* program_application_id */
564 NULL, /* program_id */
565 NULL, /* program_update_date */
566 ic1.wip_supply_type,
567 2, /* pick_components = NO */
568 ic1.component_sequence_id,
569 /* This is an addition column
570 to save the model component
571 seq id for later use */
572 ic1.supply_subinventory,
573 ic1.supply_locator_id
574 from
575 bom_inventory_components ic1,
576 mtl_demand d1, /*Option */
577 mtl_demand d2, /* Parent-Model */
578 mtl_demand d3, /* parent-Component */
579 bom_bill_of_mtls_interface b
580 where ic1.component_sequence_id =( /* Refer bug 625484. component_sequence_id */
581 select component_sequence_id /* in d1 points to components in validation */
582 from bom_inventory_components bic /* so,we need to map these to Mfg org. For */
583 where bill_sequence_id = ( /* this we find the assembly to which */
584 select common_bill_sequence_id /* d1.component_seq_id belongs and then find */
585 from bom_bill_of_materials bbm /* bill for it in Mfg org.We find equivalent */
586 where organization_id = d1.organization_id /* compnent in this bill by joining */
587 and alternate_bom_designator is null /* on component_item_id. Each component*/
588 and assembly_item_id =( /*is assumed to be used at one operation only */
589 select distinct assembly_item_id /* Operation_Seq_num must be same in bills in */
590 from bom_bill_of_materials bbm1, /* all organizations for that assembly */
591 bom_inventory_components bic1
592 where bbm1.common_bill_sequence_id = bic1.bill_sequence_id
596 and trunc(bic.effectivity_date) <= d1.requirement_date
593 and component_sequence_id = d1.component_sequence_id
594 and bbm1.assembly_item_id = d3.inventory_item_id))
595 and bic.component_item_id = d1.inventory_item_id
597 and NVL(bic.disable_date, d1.requirement_date ) + 1 > d1.requirement_date )
598 and ic1.optional = 1 /* optional = yes */
599 and ic1.bom_item_type = 4 /* standard */
600 and d1.config_status = 20
601 and d2.config_status = 20
602 and d1.primary_uom_quantity <> 0
603 and d1.rto_model_source_line = d2.demand_source_line
604 and d2.demand_source_line = b.demand_source_line
605 and d2.demand_source_type = b.demand_source_type
606 and d2.demand_source_header_id = b.demand_source_header_id
607 and d2.demand_type = 1 /* model */
608 and d2.organization_id = d1.organization_id
609 and d2.primary_uom_quantity <> 0
610 and d3.rto_model_source_line = d1.rto_model_source_line
611 and d3.component_sequence_id = d1.parent_component_seq_id
612 and b.set_id = TO_CHAR(to_number(USERENV('SESSIONID')));
613
614
615
616 /*
617 ** Second:
618 ** All the standard component items associated
619 ** with the new configuration items will be loaded into the
620 ** BOM_INVENTORY_COMPS_INTERFACE table.
621 */
622
623 stmt_num := 70;
624 insert into BOM_INVENTORY_COMPS_INTERFACE
625 (
626 OPERATION_SEQ_NUM,
627 COMPONENT_ITEM_ID,
628 LAST_UPDATE_DATE,
629 LAST_UPDATED_BY,
630 CREATION_DATE,
631 CREATED_BY,
632 LAST_UPDATE_LOGIN,
633 ITEM_NUM,
634 COMPONENT_QUANTITY,
635 COMPONENT_YIELD_FACTOR,
636 COMPONENT_REMARKS,
637 EFFECTIVITY_DATE,
638 CHANGE_NOTICE,
639 IMPLEMENTATION_DATE,
640 DISABLE_DATE,
641 ATTRIBUTE_CATEGORY,
642 ATTRIBUTE1,
643 ATTRIBUTE2,
644 ATTRIBUTE3,
645 ATTRIBUTE4,
646 ATTRIBUTE5,
647 ATTRIBUTE6,
648 ATTRIBUTE7,
649 ATTRIBUTE8,
650 ATTRIBUTE9,
651 ATTRIBUTE10,
652 ATTRIBUTE11,
653 ATTRIBUTE12,
654 ATTRIBUTE13,
655 ATTRIBUTE14,
656 ATTRIBUTE15,
657 PLANNING_FACTOR,
658 QUANTITY_RELATED,
659 SO_BASIS,
660 OPTIONAL,
661 MUTUALLY_EXCLUSIVE_OPTIONS,
662 INCLUDE_IN_COST_ROLLUP,
663 CHECK_ATP,
664 SHIPPING_ALLOWED,
665 REQUIRED_TO_SHIP,
666 REQUIRED_FOR_REVENUE,
667 INCLUDE_ON_SHIP_DOCS,
668 INCLUDE_ON_BILL_DOCS,
669 LOW_QUANTITY,
670 HIGH_QUANTITY,
671 ACD_TYPE,
672 OLD_COMPONENT_SEQUENCE_ID,
673 COMPONENT_SEQUENCE_ID,
674 BILL_SEQUENCE_ID,
675 REQUEST_ID,
676 PROGRAM_APPLICATION_ID,
677 PROGRAM_ID,
678 PROGRAM_UPDATE_DATE,
679 WIP_SUPPLY_TYPE,
680 PICK_COMPONENTS,
681 MODEL_COMP_SEQ_ID,
682 SUPPLY_SUBINVENTORY,
683 SUPPLY_LOCATOR_ID
684 )
685 select /*+ ORDERED */
686 decode(NVL(ic1.operation_seq_num,1),1,
687 NVL(d1.operation_seq_num,1),ic1.operation_seq_num),
688 ic1.component_item_id,
689 SYSDATE, /* last_updated_date */
690 1, /* last_updated_by */
691 SYSDATE, /* creation_date */
692 1, /* created_by */
693 1, /* last_update_login */
694 ic1.item_num,
695 ic1.component_quantity * (d1.primary_uom_quantity/d2.primary_uom_quantity),
696 ic1.component_yield_factor,
697 NULL, /*ic1.component_remark*/
698 TRUNC(SYSDATE), /* effective date */
699 NULL, /* change notice */
700 SYSDATE, /* implementation_date */
701 NULL, /* disable date */
702 ic1.attribute_category,
703 ic1.attribute1,
707 ic1.attribute5,
704 ic1.attribute2,
705 ic1.attribute3,
706 ic1.attribute4,
708 ic1.attribute6,
709 ic1.attribute7,
710 ic1.attribute8,
711 ic1.attribute9,
712 ic1.attribute10,
713 ic1.attribute11,
714 ic1.attribute12,
715 ic1.attribute13,
716 ic1.attribute14,
717 ic1.attribute15,
718 100, /* planning_factor */
719 2, /* quantity_related */
720 ic1.so_basis,
721 2, /* optional */
722 2, /* mutually_exclusive_options */
723 ic1.include_in_cost_rollup,
724 ic1.check_atp,
725 2, /* shipping_allowed = NO */
726 2, /* required_to_ship = NO */
727 ic1.required_for_revenue,
728 ic1.include_on_ship_docs,
729 ic1.include_on_bill_docs,
730 NULL, /* low_quantity */
731 NULL, /* high_quantity */
732 NULL, /* acd_type */
733 NULL, /* old_component_sequence_id */
734 BOM_INVENTORY_COMPONENTS_S.NEXTVAL,
735 /* component sequence id */
736 b1.bill_sequence_id, /* bill sequence id */
737 NULL, /* request_id */
738 NULL, /* program_application_id */
739 NULL, /* program_id */
740 NULL, /* program_update_date */
741 ic1.wip_supply_type,
742 2, /* pick_components = NO */
743 ic1.component_sequence_id,
744 /* This is an addition column
745 to save the model component
746 seq id for later use */
747 ic1.supply_subinventory,
748 ic1.supply_locator_id
749 from
750 bom_bill_of_mtls_interface b1,
751 mtl_demand d2, /* Model-Parent */
752 mtl_demand d1, /* Standard Mandatory comp */
753 mtl_system_items si1,
754 bom_bill_of_materials b,
755 bom_inventory_components ic1
756 where d1.organization_id = si1.organization_id
757 and d1.inventory_item_id = si1.inventory_item_id
758 and d1.config_status = 20
759 and si1.bom_item_type in (1,2)
760 /* model, option class */
761 and d1.primary_uom_quantity <> 0
762 and d1.rto_model_source_line = d2.demand_source_line
763 and b1.set_id = TO_CHAR(to_number(USERENV('SESSIONID')))
764 and d2.demand_source_type = 2
765 and d2.demand_source_header_id =
766 b1.demand_source_header_id
767 and d2.demand_type = 1
768 and d2.demand_source_line = b1.demand_source_line
769 and d2.primary_uom_quantity <> 0
770 and d1.organization_id = b.organization_id
771 and d1.inventory_item_id = b.assembly_item_id
772 and d1.parent_demand_id is NULL
773 and b.alternate_bom_designator is NULL
774 and b.common_bill_sequence_id = ic1.bill_sequence_id
775 and ic1.optional = 2 /* optional = no */
776 and ic1.effectivity_date <=
777 GREATEST(NVL(d2.estimated_release_date,SYSDATE),SYSDATE)
778 and ic1.implementation_date is not null
779 and NVL(ic1.disable_date,NVL(d2.estimated_release_date,
780 SYSDATE)+1) > NVL(d2.estimated_release_date,SYSDATE)
781 and ic1.bom_item_type = 4; /* standard */
782
783 /*
784 ** Third:
785 ** All the chosen option classes associated
786 ** with the new configuration items will be loaded into the
787 ** BOM_INVENTORY_COMPS_INTERFACE table.
788 */
789 table_name := 'BOM_INVENTORY_COMPS_INTERFACE';
790 stmt_num := 80;
791 insert into BOM_INVENTORY_COMPS_INTERFACE
792 (
793 OPERATION_SEQ_NUM,
794 COMPONENT_ITEM_ID,
795 LAST_UPDATE_DATE,
796 LAST_UPDATED_BY,
797 CREATION_DATE,
798 CREATED_BY,
799 LAST_UPDATE_LOGIN,
800 ITEM_NUM,
801 COMPONENT_QUANTITY,
802 COMPONENT_YIELD_FACTOR,
803 COMPONENT_REMARKS,
804 EFFECTIVITY_DATE,
805 CHANGE_NOTICE,
806 IMPLEMENTATION_DATE,
807 DISABLE_DATE,
808 ATTRIBUTE_CATEGORY,
809 ATTRIBUTE1,
813 ATTRIBUTE5,
810 ATTRIBUTE2,
811 ATTRIBUTE3,
812 ATTRIBUTE4,
814 ATTRIBUTE6,
815 ATTRIBUTE7,
816 ATTRIBUTE8,
817 ATTRIBUTE9,
818 ATTRIBUTE10,
819 ATTRIBUTE11,
820 ATTRIBUTE12,
821 ATTRIBUTE13,
822 ATTRIBUTE14,
823 ATTRIBUTE15,
824 PLANNING_FACTOR,
825 QUANTITY_RELATED,
826 SO_BASIS,
827 OPTIONAL,
828 MUTUALLY_EXCLUSIVE_OPTIONS,
829 INCLUDE_IN_COST_ROLLUP,
830 CHECK_ATP,
831 SHIPPING_ALLOWED,
832 REQUIRED_TO_SHIP,
833 REQUIRED_FOR_REVENUE,
834 INCLUDE_ON_SHIP_DOCS,
835 INCLUDE_ON_BILL_DOCS,
836 LOW_QUANTITY,
837 HIGH_QUANTITY,
838 ACD_TYPE,
839 OLD_COMPONENT_SEQUENCE_ID,
840 COMPONENT_SEQUENCE_ID,
841 BILL_SEQUENCE_ID,
842 REQUEST_ID,
843 PROGRAM_APPLICATION_ID,
844 PROGRAM_ID,
845 PROGRAM_UPDATE_DATE,
846 WIP_SUPPLY_TYPE,
847 PICK_COMPONENTS,
848 MODEL_COMP_SEQ_ID,
849 SUPPLY_SUBINVENTORY,
850 SUPPLY_LOCATOR_ID
851 )
852 select
853 decode(NVL(ic1.operation_seq_num,1),1,
854 NVL(d1.operation_seq_num,1),ic1.operation_seq_num),
855 ic1.component_item_id,
856 SYSDATE, /* last_updated_date */
857 1, /* last_updated_by */
858 SYSDATE, /* creation_date */
859 1, /* created_by */
860 1, /* last_update_login */
861 ic1.item_num,
862 d1.primary_uom_quantity /
863 NVL(d2.primary_uom_quantity,1),
864 /* qty = comp_qty / model_qty */
865 ic1.component_yield_factor,
866 NULL, /*ic1.component_remark*/
867 TRUNC(SYSDATE), /* effective date */
868 NULL, /* change notice */
869 SYSDATE, /* implementation_date */
870 NULL, /* disable date */
871 ic1.attribute_category,
872 ic1.attribute1,
873 ic1.attribute2,
874 ic1.attribute3,
875 ic1.attribute4,
876 ic1.attribute5,
877 ic1.attribute6,
878 ic1.attribute7,
879 ic1.attribute8,
880 ic1.attribute9,
881 ic1.attribute10,
882 ic1.attribute11,
883 ic1.attribute12,
884 ic1.attribute13,
885 ic1.attribute14,
886 ic1.attribute15,
887 100, /* planning_factor */
888 2, /* quantity_related */
889 2, /* so_basis */
890 2, /* optional */
891 2, /* mutually_exclusive_options */
892 2, /* include_in_cost_rollup */
893 2, /* check_atp */
894 2, /* shipping_allowed = NO */
895 2, /* required_to_ship = NO */
896 ic1.required_for_revenue,
897 ic1.include_on_ship_docs,
898 ic1.include_on_bill_docs,
899 NULL, /* low_quantity */
900 NULL, /* high_quantity */
901 NULL, /* acd_type */
902 NULL, /* old_component_sequence_id */
903 BOM_INVENTORY_COMPONENTS_S.NEXTVAL,
904 /* component sequence id */
905 b.bill_sequence_id, /* bill sequence id */
906 NULL, /* request_id */
907 NULL, /* program_application_id */
908 NULL, /* program_id */
909 NULL, /* program_update_date */
910 ic1.wip_supply_type,
911 2, /* pick_components = NO */
912 ic1.component_sequence_id,
913 /* This is an addition column
917 ic1.supply_locator_id
914 to save the model component
915 seq id for later use */
916 ic1.supply_subinventory,
918 from
919 bom_bill_of_mtls_interface b,
920 mtl_demand d2, /* Model */
921 mtl_demand d1, /* Option Classes */
922 mtl_demand d3, /* parent component */
923 bom_inventory_components ic1
924 where ic1.component_sequence_id =( /* See 625484 releated comments in stmt 60 */
925 select component_sequence_id
926 from bom_inventory_components bic
927 where bill_sequence_id = (
928 select common_bill_sequence_id
929 from bom_bill_of_materials bbm
930 where organization_id = d1.organization_id
931 and alternate_bom_designator is null
932 and assembly_item_id =(
933 select distinct assembly_item_id
934 from bom_bill_of_materials bbm1,
935 bom_inventory_components bic1
936 where bbm1.common_bill_sequence_id = bic1.bill_sequence_id
937 and component_sequence_id = d1.component_sequence_id
938 and (bbm1.assembly_item_id = d3.inventory_item_id
939 OR bbm1.assembly_item_id = d2.inventory_item_id)))
940 and bic.component_item_id = d1.inventory_item_id
941 and trunc(bic.effectivity_date) <= d1.requirement_date
942 and NVL(bic.disable_date, d1.requirement_date ) + 1 > d1.requirement_date )
943 and (ic1.bom_item_type = 2 /* option class */
944 or ic1.bom_item_type = 1) /* model */
945 and d1.config_status = 20
946 and d2.config_status = 20
947 and d1.primary_uom_quantity <> 0
948 and d1.demand_id <> d2.demand_id /* not base model */
949 and d1.rto_model_source_line = d2.demand_source_line
950 and d2.demand_source_line = b.demand_source_line
951 and d2.demand_source_type = b.demand_source_type
952 and d2.demand_source_header_id = b.demand_source_header_id
953 and d2.demand_type = 1 /* model */
954 and d2.primary_uom_quantity <> 0
955 and d1.organization_id = d2.organization_id
956 and d3.rto_model_source_line = d1.rto_model_source_line
957 and d3.component_sequence_id = d1.parent_component_seq_id
958 and b.set_id = TO_CHAR(to_number(USERENV('SESSIONID')));
959
960 /*
961 ** Fourth:
962 ** Get the base model row into BOM_INVENTORY_COMPS_INTERFACE
963 */
964 table_name := 'BOM_INVENTORY_COMPS_INTERFACE';
965 stmt_num := 90;
966 insert into BOM_INVENTORY_COMPS_INTERFACE
967 (
968 OPERATION_SEQ_NUM,
969 COMPONENT_ITEM_ID,
970 LAST_UPDATE_DATE,
971 LAST_UPDATED_BY,
972 CREATION_DATE,
973 CREATED_BY,
974 LAST_UPDATE_LOGIN,
975 ITEM_NUM,
976 COMPONENT_QUANTITY,
977 COMPONENT_YIELD_FACTOR,
978 COMPONENT_REMARKS,
979 EFFECTIVITY_DATE,
980 CHANGE_NOTICE,
981 IMPLEMENTATION_DATE,
982 DISABLE_DATE,
983 ATTRIBUTE_CATEGORY,
984 ATTRIBUTE1,
985 ATTRIBUTE2,
986 ATTRIBUTE3,
987 ATTRIBUTE4,
988 ATTRIBUTE5,
989 ATTRIBUTE6,
990 ATTRIBUTE7,
991 ATTRIBUTE8,
992 ATTRIBUTE9,
993 ATTRIBUTE10,
994 ATTRIBUTE11,
995 ATTRIBUTE12,
996 ATTRIBUTE13,
997 ATTRIBUTE14,
998 ATTRIBUTE15,
999 PLANNING_FACTOR,
1000 QUANTITY_RELATED,
1001 SO_BASIS,
1002 OPTIONAL,
1003 MUTUALLY_EXCLUSIVE_OPTIONS,
1004 INCLUDE_IN_COST_ROLLUP,
1005 CHECK_ATP,
1006 SHIPPING_ALLOWED,
1007 REQUIRED_TO_SHIP,
1008 REQUIRED_FOR_REVENUE,
1009 INCLUDE_ON_SHIP_DOCS,
1010 INCLUDE_ON_BILL_DOCS,
1011 LOW_QUANTITY,
1012 HIGH_QUANTITY,
1013 ACD_TYPE,
1014 OLD_COMPONENT_SEQUENCE_ID,
1015 COMPONENT_SEQUENCE_ID,
1016 BILL_SEQUENCE_ID,
1017 REQUEST_ID,
1018 PROGRAM_APPLICATION_ID,
1019 PROGRAM_ID,
1020 PROGRAM_UPDATE_DATE,
1024 )
1021 WIP_SUPPLY_TYPE,
1022 PICK_COMPONENTS,
1023 MODEL_COMP_SEQ_ID
1025 select
1026 1, /* operation_seq_num */
1027 d2.inventory_item_id,
1028 SYSDATE, /* last_updated_date */
1029 1, /* last_updated_by */
1030 SYSDATE, /* creation_date */
1031 1, /* created_by */
1032 1, /* last_update_login */
1033 10, /* item_num */
1034 1, /* comp_qty */
1035 1, /* yield_factor */
1036 NULL, /*ic1.component_remark*/
1037 TRUNC(SYSDATE), /* effective date */
1038 NULL, /* change notice */
1039 SYSDATE, /* implementation_date */
1040 NULL, /* disable date */
1041 NULL, /* attribute_category */
1042 NULL, /* attribute1 */
1043 NULL, /* attribute2 */
1044 NULL, /* attribute3 */
1045 NULL, /* attribute4 */
1046 NULL, /* attribute5 */
1047 NULL, /* attribute6 */
1048 NULL, /* attribute7 */
1049 NULL, /* attribute8 */
1050 NULL, /* attribute9 */
1051 NULL, /* attribute10 */
1052 NULL, /* attribute11 */
1053 NULL, /* attribute12 */
1054 NULL, /* attribute13 */
1055 NULL, /* attribute14 */
1056 NULL, /* attribute15 */
1057 100, /* planning_factor */
1058 2, /* quantity_related */
1059 2, /* so_basis */
1060 2, /* optional */
1061 2, /* mutually_exclusive_options */
1062 2, /* include_in_cost_rollup */
1063 2, /* check_atp */
1064 2, /* shipping_allowed = NO */
1065 2, /* required_to_ship = NO */
1066 2, /* required_for_revenue */
1067 2, /* include_on_ship_docs */
1068 2, /* include_on_bill_docs */
1069 NULL, /* low_quantity */
1070 NULL, /* high_quantity */
1071 NULL, /* acd_type */
1072 NULL, /* old_component_sequence_id */
1073 BOM_INVENTORY_COMPONENTS_S.NEXTVAL,
1074 /* component sequence id */
1075 b.bill_sequence_id, /* bill sequence id */
1076 NULL, /* request_id */
1077 NULL, /* program_application_id */
1078 NULL, /* program_id */
1079 NULL, /* program_update_date */
1080 6, /* wip_supply_type */
1081 2, /* pick_components = NO */
1082 0 /* This is an addition column
1083 to save the model component
1084 seq id for later use */
1085 from
1086 mtl_demand d2,
1087 bom_bill_of_mtls_interface b
1088 where d2.config_status = 20
1089 and d2.demand_source_line = b.demand_source_line
1090 and d2.demand_source_type = b.demand_source_type
1091 and d2.demand_source_header_id = b.demand_source_header_id
1092 and d2.demand_type = 1 /* model */
1093 and d2.primary_uom_quantity <> 0
1094 and b.set_id = TO_CHAR(to_number(USERENV('SESSIONID')));
1095
1096
1097 /*
1098 ** Load routing header interface table
1099 */
1100 table_name := 'BOM_OP_ROUTINGS_INTERFACE';
1101 stmt_num := 110;
1102 insert into BOM_OP_ROUTINGS_INTERFACE
1103 (
1104 ROUTING_SEQUENCE_ID,
1105 ASSEMBLY_ITEM_ID,
1106 ORGANIZATION_ID,
1107 ALTERNATE_ROUTING_DESIGNATOR,
1108 LAST_UPDATE_DATE,
1109 LAST_UPDATED_BY,
1110 CREATION_DATE,
1111 CREATED_BY,
1112 LAST_UPDATE_LOGIN,
1113 ROUTING_TYPE,
1114 COMMON_ROUTING_SEQUENCE_ID,
1115 COMMON_ASSEMBLY_ITEM_ID,
1116 ROUTING_COMMENT,
1117 COMPLETION_SUBINVENTORY,
1118 COMPLETION_LOCATOR_ID,
1122 ATTRIBUTE3,
1119 ATTRIBUTE_CATEGORY,
1120 ATTRIBUTE1,
1121 ATTRIBUTE2,
1123 ATTRIBUTE4,
1124 ATTRIBUTE5,
1125 ATTRIBUTE6,
1126 ATTRIBUTE7,
1127 ATTRIBUTE8,
1128 ATTRIBUTE9,
1129 ATTRIBUTE10,
1130 ATTRIBUTE11,
1131 ATTRIBUTE12,
1132 ATTRIBUTE13,
1133 ATTRIBUTE14,
1134 ATTRIBUTE15,
1135 REQUEST_ID,
1136 PROGRAM_APPLICATION_ID,
1137 PROGRAM_ID,
1138 PROGRAM_UPDATE_DATE,
1139 SET_ID,
1140 DEMAND_SOURCE_LINE,
1141 DEMAND_SOURCE_TYPE,
1142 DEMAND_SOURCE_HEADER_ID,
1143 PROCESS_REVISION,
1144 LINE_ID,
1145 MIXED_MODEL_MAP_FLAG,
1146 PRIORITY,
1147 CFM_ROUTING_FLAG,
1148 TOTAL_PRODUCT_CYCLE_TIME,
1149 CTP_FLAG
1150 )
1151 select
1152 BOM_OPERATIONAL_ROUTINGS_S.NEXTVAL,
1153 m.inventory_item_id,
1154 m.organization_id,
1155 NULL,
1156 SYSDATE,
1157 1,
1158 SYSDATE,
1159 1, /* CREATED_BY */
1160 1, /* LAST_UPDATE_LOGIN */
1161 b.routing_type, /* ROUTING_TYPE */
1162 1, /* COMMON_ROUTING_SEQUENCE_ID */
1163 NULL, /* COMMON_ASSEMBLY_ITEM_ID */
1164 b.routing_comment,
1165 b.completion_subinventory,
1166 b.completion_locator_id,
1167 NULL,
1168 b.attribute1,
1169 b.attribute2,
1170 b.attribute3,
1171 b.attribute4,
1172 b.attribute5,
1173 b.attribute6,
1174 b.attribute7,
1175 b.attribute8,
1176 b.attribute9,
1177 b.attribute10,
1178 b.attribute11,
1179 b.attribute12,
1180 b.attribute13,
1181 b.attribute14,
1182 b.attribute15,
1183 NULL,
1184 NULL,
1185 NULL,
1186 NULL,
1187 m.set_id,
1188 m.demand_source_line,
1189 m.demand_source_type,
1190 m.demand_source_header_id,
1191 mp.starting_revision,
1192 b.line_id,
1193 b.mixed_model_map_flag,
1194 b.priority,
1195 b.cfm_routing_flag,
1196 b.total_product_cycle_time,
1197 b.ctp_flag
1198 from
1199 BOM_OPERATIONAL_ROUTINGS b,
1200 MTL_DEMAND d,
1201 MTL_PARAMETERS mp,
1202 MTL_SYSTEM_ITEMS_INTERFACE m
1203 where m.set_id = TO_CHAR(to_number(USERENV('SESSIONID')))
1204 and d.demand_source_line = m.demand_source_line
1205 and d.demand_source_type = m.demand_source_type
1206 and d.demand_source_header_id = m.demand_source_header_id
1207 and d.inventory_item_id = m.copy_item_id
1208 and d.config_status = 20
1209 and d.primary_uom_quantity <> 0
1210 and b.assembly_item_id = d.inventory_item_id
1211 and b.organization_id = d.organization_id
1212 and b.alternate_routing_designator is NULL
1213 and mp.organization_id = m.organization_id;
1214
1215 /*
1216 ** Update the common_routing_sequence_id equal to the
1217 ** routing_sequence_id in BOM_OP_ROUTINGS_INTERFACE
1218 */
1219
1220 stmt_num := 120;
1221 update BOM_OP_ROUTINGS_INTERFACE b
1222 set common_routing_sequence_id = routing_sequence_id
1223 where (b.common_routing_sequence_id =1 or
1224 b.common_routing_sequence_id is NULL )
1225 and b.set_id = TO_CHAR(to_number(USERENV('SESSIONID')));
1226
1227 /*
1228 ** Udpate the mixed_model_map_flag. If the cfm_routing_flag
1229 ** is 1, then mixed_model_flag should be 1 if any flow_routing
1230 ** (primary or alternate) for the model has the mixed_model_flag
1231 ** equal to 1. */
1232
1233 stmt_num := 125;
1234
1235 Update BOM_OP_ROUTINGS_INTERFACE b
1236 set mixed_model_map_flag =
1237 ( select 1
1238 from bom_operational_routings b1,
1239 mtl_system_items_interface m
1243 and b1.assembly_item_id = m.copy_item_id
1240 where b.assembly_item_id = m.inventory_item_id
1241 and b.organization_id = m.organization_id
1242 and m.set_id = to_char(to_number(USERENV('SESSIONID')))
1244 and b1.organization_id = m.organization_id
1245 and b1.cfm_routing_flag = 1
1246 and b1.mixed_model_map_flag = 1
1247 and b1.alternate_routing_designator is not NULL )
1248 where b.set_id = to_char(to_number(USERENV('SESSIONID')))
1249 and b.mixed_model_map_flag <> 1
1250 and b.cfm_routing_flag =1;
1251
1252
1253
1254 /*
1255 ** Load the operation sequence interface table
1256 */
1257 /*
1258 ** Zero:
1259 ** ALL Processes and Line operations ,
1260 ** associated with the model/option classes will
1261 ** be inserted into the BOM_OP_SEQUENCES_INTERFACE
1262 */
1263 table_name := 'BOM_OP_SEQUENCES_INTERFACE';
1264 stmt_num := 130;
1265 insert into BOM_OP_SEQUENCES_INTERFACE
1266 (
1267 OPERATION_SEQUENCE_ID,
1268 ROUTING_SEQUENCE_ID,
1269 OPERATION_SEQ_NUM,
1270 LAST_UPDATE_DATE,
1271 LAST_UPDATED_BY,
1272 CREATION_DATE,
1273 CREATED_BY,
1274 LAST_UPDATE_LOGIN,
1275 STANDARD_OPERATION_ID,
1276 DEPARTMENT_ID ,
1277 OPERATION_LEAD_TIME_PERCENT,
1278 MINIMUM_TRANSFER_QUANTITY,
1279 COUNT_POINT_TYPE ,
1280 OPERATION_DESCRIPTION,
1281 EFFECTIVITY_DATE,
1282 CHANGE_NOTICE ,
1283 IMPLEMENTATION_DATE,
1284 DISABLE_DATE ,
1285 BACKFLUSH_FLAG,
1286 OPTION_DEPENDENT_FLAG,
1287 ATTRIBUTE_CATEGORY ,
1288 ATTRIBUTE1,
1289 ATTRIBUTE2,
1290 ATTRIBUTE3,
1291 ATTRIBUTE4,
1292 ATTRIBUTE5,
1293 ATTRIBUTE6,
1294 ATTRIBUTE7,
1295 ATTRIBUTE8,
1296 ATTRIBUTE9,
1297 ATTRIBUTE10,
1298 ATTRIBUTE11,
1299 ATTRIBUTE12,
1300 ATTRIBUTE13,
1301 ATTRIBUTE14,
1302 ATTRIBUTE15,
1303 REQUEST_ID,
1304 PROGRAM_APPLICATION_ID,
1305 PROGRAM_ID ,
1306 PROGRAM_UPDATE_DATE,
1307 MODEL_OP_SEQ_ID,
1308 REFERENCE_FLAG,
1309 OPERATION_TYPE,
1310 PROCESS_OP_SEQ_ID,
1311 LINE_OP_SEQ_ID,
1312 YIELD,
1313 CUMULATIVE_YIELD,
1314 REVERSE_CUMULATIVE_YIELD,
1315 LABOR_TIME_CALC,
1316 MACHINE_TIME_CALC,
1317 TOTAL_TIME_CALC,
1318 LABOR_TIME_USER,
1319 MACHINE_TIME_USER,
1320 TOTAL_TIME_USER,
1321 NET_PLANNING_PERCENT
1322 )
1323 select
1324 BOM_OPERATION_SEQUENCES_S.NEXTVAL,
1325 b1.routing_sequence_id, /* routing_sequence_id */
1326 os1.operation_seq_num,
1327 SYSDATE, /* last update date */
1328 1, /* last updated by */
1329 SYSDATE, /* creation date */
1330 1, /* created by */
1331 si1.bom_item_type, /* last update login col stores item type temporarily */
1332 os1.standard_operation_id,
1333 os1.department_id,
1334 os1.operation_lead_time_percent,
1335 os1.minimum_transfer_quantity,
1336 os1.count_point_type,
1337 os1.operation_description,
1338 TRUNC(SYSDATE), /* effective date */
1339 NULL, /* change_notice */
1340 TRUNC(SYSDATE), /* implementation date */
1341 NULL, /* disable date */
1342 os1.backflush_flag,
1343 2, /* option_dependent_flag */
1344 os1.attribute_category,
1345 os1.attribute1,
1346 os1.attribute2,
1347 os1.attribute3,
1348 os1.attribute4,
1349 os1.attribute5,
1350 os1.attribute6,
1354 os1.attribute10,
1351 os1.attribute7,
1352 os1.attribute8,
1353 os1.attribute9,
1355 os1.attribute11,
1356 os1.attribute12,
1357 os1.attribute13,
1358 os1.attribute14,
1359 os1.attribute15,
1360 NULL, /* request_id */
1361 NULL, /* program_application_id */
1362 NULL, /* program_id */
1363 NULL, /* program_update_date */
1364 os1.operation_sequence_id,
1365 reference_flag,
1366 operation_type,
1367 process_op_seq_id,
1368 line_op_seq_id,
1369 yield,
1370 cumulative_yield,
1371 reverse_cumulative_yield,
1372 labor_time_calc,
1373 machine_time_calc,
1374 total_time_calc,
1375 labor_time_user,
1376 machine_time_user,
1377 total_time_user,
1378 Net_planning_percent
1379 from
1380 bom_op_routings_interface b1,
1381 mtl_demand d2,/* Model for est. release date */
1382 mtl_demand d1,
1383 mtl_system_items si1,
1384 bom_operational_routings or1,
1385 bom_operation_sequences os1
1386 where b1.set_id = TO_CHAR(to_number(USERENV('SESSIONID')))
1387 and d1.rto_model_source_line = b1.demand_source_line
1388 and d1.config_status = 20
1389 and d1.primary_uom_quantity <> 0
1390 and d1.parent_demand_id is NULL
1391 and d2.demand_source_line = b1.demand_source_line
1392 and d2.demand_source_header_id = d1.demand_source_header_id
1393 and d2.demand_source_type = 2
1394 and d2.demand_type = 1
1395 and d2.primary_uom_quantity <> 0
1396 and si1.organization_id = d1.organization_id
1397 and si1.inventory_item_id = d1.inventory_item_id
1398 and si1.bom_item_type in (1,2)
1399 /* model and option classes */
1400 and or1.assembly_item_id = si1.inventory_item_id
1401 and or1.organization_id = si1.organization_id
1402 and or1.alternate_routing_designator is NULL
1403 and nvl(or1.cfm_routing_flag,2) = nvl(b1.cfm_routing_flag,2) /*ensure correct OC rtgs*/
1404 and os1.routing_sequence_id = or1.common_routing_sequence_id
1405 and os1.effectivity_date <= GREATEST(NVL(d2.estimated_release_date,
1406 SYSDATE),SYSDATE)
1407 and NVL(os1.disable_date,NVL(d2.estimated_release_date,
1408 SYSDATE)+ 1) > NVL(d2.estimated_release_date,SYSDATE)
1409 and NVL(os1.eco_for_production,2) = 2
1410 and os1.operation_type in (2,3);
1411
1412 /*
1413 ** First:
1414 ** All operations/events with NO in option_dependent_flag,
1415 ** associated with the model/option classes will
1416 ** be inserted into the BOM_OP_SEQUENCES_INTERFACE
1417 */
1418 table_name := 'BOM_OP_SEQUENCES_INTERFACE';
1419 stmt_num := 130;
1420 insert into BOM_OP_SEQUENCES_INTERFACE
1421 (
1422 OPERATION_SEQUENCE_ID,
1423 ROUTING_SEQUENCE_ID,
1424 OPERATION_SEQ_NUM,
1425 LAST_UPDATE_DATE,
1426 LAST_UPDATED_BY,
1427 CREATION_DATE,
1428 CREATED_BY,
1429 LAST_UPDATE_LOGIN,
1430 STANDARD_OPERATION_ID,
1431 DEPARTMENT_ID ,
1432 OPERATION_LEAD_TIME_PERCENT,
1433 MINIMUM_TRANSFER_QUANTITY,
1434 COUNT_POINT_TYPE ,
1435 OPERATION_DESCRIPTION,
1436 EFFECTIVITY_DATE,
1437 CHANGE_NOTICE ,
1438 IMPLEMENTATION_DATE,
1439 DISABLE_DATE ,
1440 BACKFLUSH_FLAG,
1441 OPTION_DEPENDENT_FLAG,
1442 ATTRIBUTE_CATEGORY ,
1443 ATTRIBUTE1,
1444 ATTRIBUTE2,
1445 ATTRIBUTE3,
1446 ATTRIBUTE4,
1447 ATTRIBUTE5,
1448 ATTRIBUTE6,
1449 ATTRIBUTE7,
1450 ATTRIBUTE8,
1451 ATTRIBUTE9,
1452 ATTRIBUTE10,
1453 ATTRIBUTE11,
1454 ATTRIBUTE12,
1455 ATTRIBUTE13,
1456 ATTRIBUTE14,
1457 ATTRIBUTE15,
1458 REQUEST_ID,
1459 PROGRAM_APPLICATION_ID,
1460 PROGRAM_ID ,
1464 OPERATION_TYPE,
1461 PROGRAM_UPDATE_DATE,
1462 MODEL_OP_SEQ_ID,
1463 REFERENCE_FLAG,
1465 PROCESS_OP_SEQ_ID,
1466 LINE_OP_SEQ_ID,
1467 YIELD,
1468 CUMULATIVE_YIELD,
1469 REVERSE_CUMULATIVE_YIELD,
1470 LABOR_TIME_CALC,
1471 MACHINE_TIME_CALC,
1472 TOTAL_TIME_CALC,
1473 LABOR_TIME_USER,
1474 MACHINE_TIME_USER,
1475 TOTAL_TIME_USER,
1476 NET_PLANNING_PERCENT
1477 )
1478 select
1479 BOM_OPERATION_SEQUENCES_S.NEXTVAL,
1480 b1.routing_sequence_id, /* routing_sequence_id */
1481 os1.operation_seq_num,
1482 SYSDATE, /* last update date */
1483 1, /* last updated by */
1484 SYSDATE, /* creation date */
1485 1, /* created by */
1486 si1.bom_item_type, /* last update login col stores item type temporarily */
1487 os1.standard_operation_id,
1488 os1.department_id,
1489 os1.operation_lead_time_percent,
1490 os1.minimum_transfer_quantity,
1491 os1.count_point_type,
1492 os1.operation_description,
1493 TRUNC(SYSDATE), /* effective date */
1494 NULL, /* change_notice */
1495 TRUNC(SYSDATE), /* implementation date */
1496 NULL, /* disable date */
1497 os1.backflush_flag,
1498 2, /* option_dependent_flag */
1499 os1.attribute_category,
1500 os1.attribute1,
1501 os1.attribute2,
1502 os1.attribute3,
1503 os1.attribute4,
1504 os1.attribute5,
1505 os1.attribute6,
1506 os1.attribute7,
1507 os1.attribute8,
1508 os1.attribute9,
1509 os1.attribute10,
1510 os1.attribute11,
1511 os1.attribute12,
1512 os1.attribute13,
1513 os1.attribute14,
1514 os1.attribute15,
1515 NULL, /* request_id */
1516 NULL, /* program_application_id */
1517 NULL, /* program_id */
1518 NULL, /* program_update_date */
1519 os1.operation_sequence_id,
1520 reference_flag,
1521 operation_type,
1522 process_op_seq_id,
1523 line_op_seq_id,
1524 yield,
1525 cumulative_yield,
1526 reverse_cumulative_yield,
1527 labor_time_calc,
1528 machine_time_calc,
1529 total_time_calc,
1530 labor_time_user,
1531 machine_time_user,
1532 total_time_user,
1533 Net_planning_percent
1534 from
1535 bom_op_routings_interface b1,
1536 mtl_demand d2,/* Model for est. release date */
1537 mtl_demand d1,
1538 mtl_system_items si1,
1539 bom_operational_routings or1,
1540 bom_operation_sequences os1
1541 where b1.set_id = TO_CHAR(to_number(USERENV('SESSIONID')))
1542 and d1.rto_model_source_line = b1.demand_source_line
1543 and d1.config_status = 20
1544 and d1.primary_uom_quantity <> 0
1545 and d1.parent_demand_id is NULL
1546 and d2.demand_source_line = b1.demand_source_line
1547 and d2.demand_source_header_id = d1.demand_source_header_id
1548 and d2.demand_source_type = 2
1549 and d2.demand_type = 1
1550 and d2.primary_uom_quantity <> 0
1551 and si1.organization_id = d1.organization_id
1552 and si1.inventory_item_id = d1.inventory_item_id
1553 and si1.bom_item_type in (1,2)
1554 /* model and option classes */
1555 and or1.assembly_item_id = si1.inventory_item_id
1556 and or1.organization_id = si1.organization_id
1557 and or1.alternate_routing_designator is NULL
1558 and nvl(or1.cfm_routing_flag,2) = nvl(b1.cfm_routing_flag,2) /*ensure correct OC rtgs*/
1559 and os1.routing_sequence_id = or1.common_routing_sequence_id
1560 and os1.effectivity_date <= GREATEST(NVL(d2.estimated_release_date,
1561 SYSDATE),SYSDATE)
1562 and NVL(os1.disable_date,NVL(d2.estimated_release_date,
1566 and ( os1.operation_type =1 OR os1.operation_type is NULL);
1563 SYSDATE)+ 1) > NVL(d2.estimated_release_date,SYSDATE)
1564 and os1.option_dependent_flag = 2
1565 and NVL(os1.eco_for_production,2) = 2
1567
1568
1569 /*
1570 ** Second:
1571 ** All operations/events with YES in option_dependent_flag,
1572 ** associated with the chosen option items/option classes
1573 ** will be inserted into BOM_OP_SEQUENCES_INTERFACE.
1574 */
1575 stmt_num := 140;
1576 insert into BOM_OP_SEQUENCES_INTERFACE
1577 (
1578 OPERATION_SEQUENCE_ID,
1579 ROUTING_SEQUENCE_ID,
1580 OPERATION_SEQ_NUM,
1581 LAST_UPDATE_DATE,
1582 LAST_UPDATED_BY,
1583 CREATION_DATE,
1584 CREATED_BY,
1585 LAST_UPDATE_LOGIN,
1586 STANDARD_OPERATION_ID,
1587 DEPARTMENT_ID ,
1588 OPERATION_LEAD_TIME_PERCENT,
1589 MINIMUM_TRANSFER_QUANTITY,
1590 COUNT_POINT_TYPE ,
1591 OPERATION_DESCRIPTION,
1592 EFFECTIVITY_DATE,
1593 CHANGE_NOTICE ,
1594 IMPLEMENTATION_DATE,
1595 DISABLE_DATE ,
1596 BACKFLUSH_FLAG,
1597 OPTION_DEPENDENT_FLAG,
1598 ATTRIBUTE_CATEGORY ,
1599 ATTRIBUTE1,
1600 ATTRIBUTE2,
1601 ATTRIBUTE3,
1602 ATTRIBUTE4,
1603 ATTRIBUTE5,
1604 ATTRIBUTE6,
1605 ATTRIBUTE7,
1606 ATTRIBUTE8,
1607 ATTRIBUTE9,
1608 ATTRIBUTE10,
1609 ATTRIBUTE11,
1610 ATTRIBUTE12,
1611 ATTRIBUTE13,
1612 ATTRIBUTE14,
1613 ATTRIBUTE15,
1614 REQUEST_ID,
1615 PROGRAM_APPLICATION_ID,
1616 PROGRAM_ID ,
1617 PROGRAM_UPDATE_DATE,
1618 MODEL_OP_SEQ_ID,
1619 REFERENCE_FLAG,
1620 OPERATION_TYPE,
1621 PROCESS_OP_SEQ_ID,
1622 LINE_OP_SEQ_ID,
1623 YIELD,
1624 CUMULATIVE_YIELD,
1625 REVERSE_CUMULATIVE_YIELD,
1626 LABOR_TIME_CALC,
1627 MACHINE_TIME_CALC,
1628 TOTAL_TIME_CALC,
1629 LABOR_TIME_USER,
1630 MACHINE_TIME_USER,
1631 TOTAL_TIME_USER,
1632 NET_PLANNING_PERCENT
1633 )
1634 select /*+ ORDERED */
1635 BOM_OPERATION_SEQUENCES_S.NEXTVAL,
1636 b.routing_sequence_id, /* routing_sequence_id */
1637 os1.operation_seq_num,
1638 SYSDATE, /* last update date */
1639 1, /* last updated by */
1640 SYSDATE, /* creation date */
1641 1, /* created by */
1642 d2.demand_type, /* Last update login stores demand_type temporarily */
1643 os1.standard_operation_id,
1644 os1.department_id,
1645 os1.operation_lead_time_percent,
1646 os1.minimum_transfer_quantity,
1647 os1.count_point_type,
1648 os1.operation_description,
1649 TRUNC(SYSDATE), /* effective date */
1650 NULL, /* change_notice */
1651 TRUNC(SYSDATE), /* implementation date */
1652 NULL, /* disable date */
1653 os1.backflush_flag,
1654 2, /* option_dependent_flag */
1655 os1.attribute_category,
1656 os1.attribute1,
1657 os1.attribute2,
1658 os1.attribute3,
1659 os1.attribute4,
1660 os1.attribute5,
1661 os1.attribute6,
1662 os1.attribute7,
1663 os1.attribute8,
1664 os1.attribute9,
1665 os1.attribute10,
1666 os1.attribute11,
1667 os1.attribute12,
1668 os1.attribute13,
1669 os1.attribute14,
1670 os1.attribute15,
1671 NULL, /* request_id */
1672 NULL, /* program_application_id */
1673 NULL, /* program_id */
1674 NULL, /* program_update_date */
1678 process_op_seq_id,
1675 os1.operation_sequence_id,
1676 reference_flag,
1677 operation_type,
1679 line_op_seq_id,
1680 yield,
1681 cumulative_yield,
1682 reverse_cumulative_yield,
1683 labor_time_calc,
1684 machine_time_calc,
1685 total_time_calc,
1686 labor_time_user,
1687 machine_time_user,
1688 total_time_user,
1689 Net_planning_percent
1690 from
1691 bom_op_routings_interface b,
1692 mtl_demand d1, /* components */
1693 mtl_demand d2, /* parents */
1694 mtl_demand d3, /* Model estd release date */
1695 bom_inventory_components ic1,
1696 bom_bill_of_materials b1,
1697 bom_operational_routings or1,
1698 bom_operation_sequences os1
1699 where b.set_id = TO_CHAR(to_number(USERENV('SESSIONID')))
1700 and d1.rto_model_source_line = b.demand_source_line
1701 and d1.demand_type in (2,3) /* option class,option item */
1702 and d1.config_status = 20
1703 and d2.rto_model_source_line = b.demand_source_line
1704 and d2.config_status = 20
1705 and d2.demand_type in (1,2) /* model, option class */
1706 and d2.primary_uom_quantity <> 0
1707 and d1.organization_id = d2.organization_id
1708 and d2.component_sequence_id = d1.parent_component_seq_id
1709 and d3.demand_type = 1
1710 and d3.demand_source_type = 2
1711 and d3.demand_source_header_id = d1.demand_source_header_id
1712 and d3.demand_source_line = d1.rto_model_source_line
1713 and d3.primary_uom_quantity <> 0
1714 and ic1.component_sequence_id = ( /* See 625484 releated comments in stmt 60 */
1715 select component_sequence_id
1716 from bom_inventory_components bic
1717 where bill_sequence_id = (
1718 select common_bill_sequence_id
1719 from bom_bill_of_materials bbm
1720 where organization_id = d1.organization_id
1721 and alternate_bom_designator is null
1722 and assembly_item_id =(
1723 select distinct assembly_item_id
1724 from bom_bill_of_materials bbm1,
1725 bom_inventory_components bic1
1726 where bbm1.common_bill_sequence_id = bic1.bill_sequence_id
1727 and component_sequence_id = d1.component_sequence_id
1728 and bbm1.assembly_item_id = d2.inventory_item_id))
1729 and bic.component_item_id = d1.inventory_item_id
1730 and trunc(bic.effectivity_date) <= d1.requirement_date
1731 and NVL(bic.disable_date, d1.requirement_date ) + 1 > d1.requirement_date )
1732 and b1.common_bill_sequence_id = ic1.bill_sequence_id
1733 and b1.alternate_bom_designator is NULL
1734 and or1.assembly_item_id = b1.assembly_item_id
1735 and or1.organization_id = b1.organization_id
1736 and or1.alternate_routing_designator is null
1737 and nvl(or1.cfm_routing_flag,2) = nvl(b.cfm_routing_flag,2) /*ensure correct OC rtgs*/
1738 and os1.effectivity_date <= GREATEST(NVL(d3.estimated_release_date,
1739 SYSDATE),SYSDATE)
1740 and NVL(os1.disable_date,NVL(d3.estimated_release_date,
1741 SYSDATE)+ 1) > NVL(d3.estimated_release_date,SYSDATE)
1742 and os1.routing_sequence_id =or1.common_routing_sequence_id
1743 and os1.operation_seq_num = ic1.operation_seq_num
1744 and os1.option_dependent_flag = 1
1745 and NVL(os1.eco_for_production,2) = 2
1746 and ( os1.operation_type =1 OR os1.operation_type is NULL);
1747
1748 /*
1749 ** Third:
1750 ** man: This SQL statement gets all the operations/events that
1751 are option dependent and have a standard mandatory
1752 component at that operation
1753 */
1754 stmt_num := 150;
1755 insert into BOM_OP_SEQUENCES_INTERFACE
1756 (
1757 OPERATION_SEQUENCE_ID,
1758 ROUTING_SEQUENCE_ID,
1759 OPERATION_SEQ_NUM,
1760 LAST_UPDATE_DATE,
1761 LAST_UPDATED_BY,
1762 CREATION_DATE,
1763 CREATED_BY,
1764 LAST_UPDATE_LOGIN,
1765 STANDARD_OPERATION_ID,
1766 DEPARTMENT_ID ,
1767 OPERATION_LEAD_TIME_PERCENT,
1768 MINIMUM_TRANSFER_QUANTITY,
1769 COUNT_POINT_TYPE ,
1770 OPERATION_DESCRIPTION,
1771 EFFECTIVITY_DATE,
1772 CHANGE_NOTICE ,
1773 IMPLEMENTATION_DATE,
1777 ATTRIBUTE_CATEGORY ,
1774 DISABLE_DATE ,
1775 BACKFLUSH_FLAG,
1776 OPTION_DEPENDENT_FLAG,
1778 ATTRIBUTE1,
1779 ATTRIBUTE2,
1780 ATTRIBUTE3,
1781 ATTRIBUTE4,
1782 ATTRIBUTE5,
1783 ATTRIBUTE6,
1784 ATTRIBUTE7,
1785 ATTRIBUTE8,
1786 ATTRIBUTE9,
1787 ATTRIBUTE10,
1788 ATTRIBUTE11,
1789 ATTRIBUTE12,
1790 ATTRIBUTE13,
1791 ATTRIBUTE14,
1792 ATTRIBUTE15,
1793 REQUEST_ID,
1794 PROGRAM_APPLICATION_ID,
1795 PROGRAM_ID ,
1796 PROGRAM_UPDATE_DATE,
1797 MODEL_OP_SEQ_ID,
1798 REFERENCE_FLAG,
1799 OPERATION_TYPE,
1800 PROCESS_OP_SEQ_ID,
1801 LINE_OP_SEQ_ID,
1802 YIELD,
1803 CUMULATIVE_YIELD,
1804 REVERSE_CUMULATIVE_YIELD,
1805 LABOR_TIME_CALC,
1806 MACHINE_TIME_CALC,
1807 TOTAL_TIME_CALC,
1808 LABOR_TIME_USER,
1809 MACHINE_TIME_USER,
1810 TOTAL_TIME_USER,
1811 NET_PLANNING_PERCENT
1812 )
1813 select
1814 BOM_OPERATION_SEQUENCES_S.NEXTVAL,
1815 /* operation_sequence_id */
1816 b.routing_sequence_id, /* routing_sequence_id */
1817 os1.operation_seq_num,
1818 SYSDATE, /* last update date */
1819 1, /* last updated by */
1820 SYSDATE, /* creation date */
1821 1, /* created by */
1822 si1.bom_item_type, /* last update login col stores item type temporarily */
1823 os1.standard_operation_id,
1824 os1.department_id,
1825 os1.operation_lead_time_percent,
1826 os1.minimum_transfer_quantity,
1827 os1.count_point_type,
1828 os1.operation_description,
1829 TRUNC(SYSDATE), /* effective date */
1830 NULL, /* change_notice */
1831 TRUNC(SYSDATE), /* implementation date */
1832 NULL, /* disable date */
1833 os1.backflush_flag,
1834 2, /* option_dependent_flag */
1835 os1.attribute_category,
1836 os1.attribute1,
1837 os1.attribute2,
1838 os1.attribute3,
1839 os1.attribute4,
1840 os1.attribute5,
1841 os1.attribute6,
1842 os1.attribute7,
1843 os1.attribute8,
1844 os1.attribute9,
1845 os1.attribute10,
1846 os1.attribute11,
1847 os1.attribute12,
1848 os1.attribute13,
1849 os1.attribute14,
1850 os1.attribute15,
1851 NULL, /* request_id */
1852 NULL, /* program_application_id */
1853 NULL, /* program_id */
1854 NULL, /* program_update_date */
1855 os1.operation_sequence_id ,
1856 reference_flag,
1857 operation_type,
1858 process_op_seq_id,
1859 line_op_seq_id,
1860 yield,
1861 cumulative_yield,
1862 reverse_cumulative_yield,
1863 labor_time_calc,
1864 machine_time_calc,
1865 total_time_calc,
1866 labor_time_user,
1867 machine_time_user,
1868 total_time_user,
1869 Net_planning_percent
1870 from
1871 bom_operation_sequences os1,
1872 bom_operational_routings or1,
1873 mtl_system_items si2,
1874 bom_inventory_components ic1,
1875 bom_bill_of_materials b1,
1876 mtl_system_items si1,
1877 mtl_demand d2, /* Model if option class */
1878 mtl_demand d1, /* Model or option class */
1879 bom_op_routings_interface b
1880 where b.set_id = TO_CHAR(to_number(USERENV('SESSIONID')))
1881 and d1.rto_model_source_line = b.demand_source_line
1882 and d1.config_status = 20
1883 and d1.component_sequence_id is not NULL
1884 and d1.primary_uom_quantity <> 0
1885 and d2.config_group_id = USERENV('SESSIONID')
1889 and d2.demand_source_line = d1.rto_model_source_line
1886 and d2.demand_type = 1
1887 and d2.demand_source_type = 2
1888 and d2.demand_source_header_id = d1.demand_source_header_id
1890 and d2.primary_uom_quantity <> 0
1891 and si1.organization_id = d1.organization_id
1892 and si1.inventory_item_id = d1.inventory_item_id
1893 and si1.bom_item_type in (1,2) /* model or option class */
1894 and b1.organization_id = d1.organization_id
1895 and b1.assembly_item_id = d1.inventory_item_id
1896 and b1.alternate_bom_designator is NULL
1897 and ic1.bill_sequence_id = b1.common_bill_sequence_id
1898 and ic1.optional = 2
1899 and ic1.effectivity_date <= GREATEST(NVL(d2.estimated_release_date,
1900 SYSDATE),SYSDATE)
1901 and ic1.implementation_date is not null
1902 and NVL(ic1.disable_date,NVL(d2.estimated_release_date,
1903 SYSDATE)+ 1) > NVL(d2.estimated_release_date,SYSDATE)
1904 and si2.inventory_item_id = ic1.component_item_id
1905 and si2.organization_id = b1.organization_id
1906 and si2.bom_item_type = 4 /* standard */
1907 and or1.assembly_item_id = b1.assembly_item_id
1908 and or1.organization_id = b1.organization_id
1909 and or1.alternate_routing_designator is NULL
1910 and nvl(or1.cfm_routing_flag,2) = nvl(b.cfm_routing_flag,2) /*ensure correct OC rtgs*/
1911 and os1.effectivity_date <= GREATEST(NVL(d2.estimated_release_date,
1912 SYSDATE),SYSDATE)
1913 and NVL(os1.disable_date,NVL(d2.estimated_release_date,
1914 SYSDATE)+ 1) > NVL(d2.estimated_release_date,SYSDATE)
1915 and os1.routing_sequence_id = or1.common_routing_sequence_id
1916 and os1.option_dependent_flag = 1
1917 and os1.operation_seq_num = ic1.operation_seq_num
1918 and NVL(os1.eco_for_production,2) = 2
1919 and ( os1.operation_type =1 OR os1.operation_type is NULL);
1920
1921
1922
1923
1924 /*
1925 ** Check for duplicated operation sequence number with
1926 ** the same routing in the BOM_OP_SEQUENCE_INTERFACE.
1927 ** For the rows with the same operation sequence num, operation
1928 ** type and routing sequence id, the Model row will be kept and
1929 ** the option class's row will be deleted. If the duplicates
1930 ** are from with in the option classes themseleves, the first row
1931 ** will be kept and the rest will be deleted.
1932 */
1933
1934 stmt_num := 155;
1935
1936 save_routing_id := 0;
1937 save_op_seq_num := 0;
1938 save_op_seq_id := 0;
1939 save_op_type := 0;
1940 open dd;
1941
1942 loop
1943 fetch dd into op_seq_id,op_seq_num,op_type,routing_id,item_type;
1944 exit when (dd%notfound);
1945
1946 if save_routing_id = routing_id and
1947 save_op_seq_num = op_seq_num and
1948 save_op_type = op_type then
1949 delete from BOM_OP_SEQUENCES_INTERFACE
1950 where operation_sequence_id = op_seq_id;
1951 else
1952 save_routing_id := routing_id;
1953 save_op_seq_num := op_seq_num;
1954 save_op_type := op_type;
1955 end if;
1956 end loop;
1957
1958 close dd;
1959
1960 /* Now update the process_op_seq_id and line_seq_id of
1961 ** all events to new operations sequence Ids (map).
1962 ** Old operation_sequence_ids are available in model_op_seq_id
1963 */
1964
1965 stmt_num := 160;
1966 update bom_op_sequences_interface bos1
1967 set process_op_seq_id = (
1968 select operation_sequence_id
1969 from bom_op_sequences_interface bos2,
1970 bom_op_routings_interface b
1971 where bos1.process_op_seq_id = bos2.model_op_seq_id
1972 and bos1.routing_sequence_id = b.routing_sequence_id
1973 and bos1.routing_sequence_id = bos2.routing_sequence_id
1974 and b.set_id = to_char(to_number(USERENV('SESSIONID'))))
1975 where bos1.operation_type = 1;
1976
1977 update bom_op_sequences_interface bos1
1978 set line_op_seq_id = (
1979 select operation_sequence_id
1980 from bom_op_sequences_interface bos2,
1981 bom_op_routings_interface b
1982 where bos1.line_op_seq_id = bos2.model_op_seq_id
1983 and bos1.routing_sequence_id = b.routing_sequence_id
1984 and bos1.routing_sequence_id = bos2.routing_sequence_id
1985 and b.set_id = to_char(to_number(USERENV('SESSIONID'))))
1986 where bos1.operation_type = 1;
1987
1988 /*
1989 ** Delete routing from routing header interface if
1993 stmt_num := 170;
1990 ** there is no operation associated with the routing
1991 */
1992
1994 table_name := 'BOM_OP_ROUTINGS_INTERFACE';
1995
1996 delete from BOM_OP_ROUTINGS_INTERFACE b1
1997 where b1.routing_sequence_id not in
1998 (select b2.routing_sequence_id
1999 from BOM_OP_SEQUENCES_INTERFACE b2
2000 where b2.routing_sequence_id = b1.routing_sequence_id)
2001 and b1.set_id = TO_CHAR(to_number(USERENV('SESSIONID')));
2002
2003 /*
2004 ** Handle the selected operations that do not belong
2005 ** to the routing of the model/option class.
2006 man: This delete is not required.
2007
2008 delete BOM_OP_SEQUENCES_INTERFACE si
2009 where operation_seq_num not in
2010 (select /o+ ORDERED o/
2011 s.operation_seq_num
2012 from
2013 mtl_demand md,
2014 mtl_system_items ms,
2015 bom_operational_routings r,
2016 bom_operation_sequences s,
2017 bom_op_routings_interface ri
2018 where ri.set_id = TO_CHAR(to_number(USERENV('SESSIONID')))
2019 and ri.routing_sequence_id = si.routing_sequence_id
2020 and md.rto_model_source_line = ri.demand_source_line
2021 and md.config_status = 20
2022 and md.primary_uom_quantity <> 0
2023 and ms.inventory_item_id = md.inventory_item_id
2024 and ms.bom_item_type in (1,2)
2025 and ms.organization_id = md.organization_id
2026 and r.assembly_item_id = md.inventory_item_id
2027 and r.organization_id = md.organization_id
2028 and r.alternate_routing_designator is NULL
2029 and s.routing_sequence_id = r.common_routing_sequence_id
2030 and s.effectivity_date <= SYSDATE
2031 and NVL(s.disable_date, SYSDATE+1) > SYSDATE);
2032 */
2033
2034
2035 /*
2036 ** If the operation_seq_num that associated with
2037 ** the config component and not belong to the
2038 ** config routing, the operation_seq_num will be
2039 ** set to 1.
2040 */
2041 stmt_num := 175;
2042
2043 update BOM_INVENTORY_COMPS_INTERFACE ci
2044 set ci.operation_seq_num = 1
2045 where not exists
2046 (select 'op seq exists in config routing'
2047 from
2048 BOM_OP_SEQUENCES_INTERFACE oi,
2049 BOM_OP_ROUTINGS_INTERFACE ri,
2050 BOM_BILL_OF_MTLS_INTERFACE bi
2051 where ci.bill_sequence_id = bi.bill_sequence_id
2052 and oi.operation_seq_num = ci.operation_seq_num
2053 and oi.routing_sequence_id = ri.routing_sequence_id
2054 and ri.assembly_item_id = bi.assembly_item_id
2055 and ri.organization_id = bi.organization_id
2056 and ri.alternate_routing_designator is NULL
2057 and ri.set_id || '' = TO_CHAR(to_number(USERENV('SESSIONID')))
2058 and bi.alternate_bom_designator is NULL
2059 and bi.set_id ||''= TO_CHAR(to_number(USERENV('SESSIONID'))))
2060 and ci.bill_sequence_id in
2061 ( select bi2.bill_sequence_id
2062 from bom_bill_of_mtls_interface bi2
2063 where bi2.set_id = TO_CHAR(to_number(USERENV('SESSIONID'))));
2064
2065
2066 /*
2067 ** If more than one row in the BOM_INVENTORY_COMPS_INTERFACE
2068 ** that contain the same bill_sequence_id,
2069 ** operation_seq_num and
2070 ** component_item_id,
2071 ** those rows will be combined into a single row and the
2072 ** accumulated COMPONENT_QUANTITY will be used in the row.
2073 */
2074
2075 save_bill_id := 0;
2076 save_op_seq_num := 0;
2077 save_item_id := 0;
2078 save_comp_seq_id := 0;
2079 total_qty := 0;
2080
2081 stmt_num := 0;
2082 open cc;
2083 loop
2084 fetch cc into bill_id,op_seq_num,
2085 comp_seq_id,item_id,qty;
2086 exit when (cc%notfound);
2087 if save_bill_id <> bill_id then
2088 /*
2089 ** different bill and
2090 ** not begining of the loop
2091 */
2092 if total_qty <> 0 then
2093 update BOM_INVENTORY_COMPS_INTERFACE
2094 set component_quantity = total_qty
2095 where component_sequence_id =
2096 save_comp_seq_id;
2097 end if;
2098
2099 total_qty := qty;
2100 save_bill_id := bill_id;
2101 save_op_seq_num := op_seq_num;
2102 save_item_id := item_id;
2106 ** same bill but different item
2103 save_comp_seq_id := comp_seq_id;
2104 else
2105 /*
2107 */
2108 if save_item_id <> item_id then
2109 update BOM_INVENTORY_COMPS_INTERFACE
2110 set component_quantity = total_qty
2111 where component_sequence_id
2112 = save_comp_seq_id;
2113 total_qty := qty;
2114 /*
2115 ** same bill and item but different seq_num
2116 */
2117 else
2118 if save_op_seq_num <> op_seq_num then
2119 update BOM_INVENTORY_COMPS_INTERFACE
2120 set component_quantity = total_qty
2121 where component_sequence_id
2122 = save_comp_seq_id;
2123
2124 total_qty := qty;
2125 /*
2126 ** duplicated one
2127 */
2128 else
2129 delete BOM_INVENTORY_COMPS_INTERFACE
2130 where component_sequence_id
2131 = save_comp_seq_id;
2132
2133 total_qty := total_qty + qty;
2134 end if;
2135 end if;
2136 save_bill_id := bill_id;
2137 save_op_seq_num := op_seq_num;
2138 save_item_id := item_id;
2139 save_comp_seq_id := comp_seq_id;
2140 end if;
2141 end loop;
2142 /*
2143 ** handle the last row here
2144 */
2145 stmt_num := 180;
2146 update BOM_INVENTORY_COMPS_INTERFACE
2147 set component_quantity = total_qty
2148 where component_sequence_id = save_comp_seq_id;
2149 close cc;
2150
2151 /*
2152 ** Load operation resources interface table
2153 */
2154 table_name := 'BOM_OP_RESOURCES_INTERFACE';
2155 stmt_num := 190;
2156 insert into BOM_OP_RESOURCES_INTERFACE
2157 (
2158 OPERATION_SEQUENCE_ID,
2159 RESOURCE_SEQ_NUM,
2160 RESOURCE_ID ,
2161 ACTIVITY_ID,
2162 STANDARD_RATE_FLAG,
2163 ASSIGNED_UNITS ,
2164 USAGE_RATE_OR_AMOUNT,
2165 USAGE_RATE_OR_AMOUNT_INVERSE,
2166 BASIS_TYPE,
2167 SCHEDULE_FLAG,
2168 LAST_UPDATE_DATE,
2169 LAST_UPDATED_BY,
2170 CREATION_DATE,
2171 CREATED_BY,
2172 LAST_UPDATE_LOGIN,
2173 RESOURCE_OFFSET_PERCENT,
2174 AUTOCHARGE_TYPE,
2175 ATTRIBUTE_CATEGORY,
2176 ATTRIBUTE1,
2177 ATTRIBUTE2,
2178 ATTRIBUTE3,
2179 ATTRIBUTE4,
2180 ATTRIBUTE5,
2181 ATTRIBUTE6,
2182 ATTRIBUTE7,
2183 ATTRIBUTE8,
2184 ATTRIBUTE9,
2185 ATTRIBUTE10,
2186 ATTRIBUTE11,
2187 ATTRIBUTE12,
2188 ATTRIBUTE13,
2189 ATTRIBUTE14,
2190 ATTRIBUTE15,
2191 REQUEST_ID,
2192 PROGRAM_APPLICATION_ID,
2193 PROGRAM_ID,
2194 PROGRAM_UPDATE_DATE
2195 )
2196 select
2197 osi.operation_sequence_id, /* operation sequence id */
2198 bor.resource_seq_num,
2199 bor.resource_id,
2200 /* resource id */
2201 bor.activity_id,
2202 bor.standard_rate_flag,
2203 bor.assigned_units,
2204 bor.usage_rate_or_amount,
2205 bor.usage_rate_or_amount_inverse,
2206 bor.basis_type,
2207 bor.schedule_flag,
2208 SYSDATE, /* last update date */
2209 1, /* last updated by */
2210 SYSDATE, /* creation date */
2211 1, /* created by */
2212 1, /* last update login */
2213 bor.resource_offset_percent,
2214 bor.autocharge_type,
2215 bor.attribute_category,
2219 bor.attribute4,
2216 bor.attribute1,
2217 bor.attribute2,
2218 bor.attribute3,
2220 bor.attribute5,
2221 bor.attribute6,
2222 bor.attribute7,
2223 bor.attribute8,
2224 bor.attribute9,
2225 bor.attribute10,
2226 bor.attribute11,
2227 bor.attribute12,
2228 bor.attribute13,
2229 bor.attribute14,
2230 bor.attribute15,
2231 NULL, /* request_id */
2232 NULL, /* program_application_id */
2233 NULL, /* program_id */
2234 NULL /* program_update_date */
2235 from
2236 bom_op_sequences_interface osi,
2237 bom_operation_resources bor,
2238 bom_op_routings_interface b
2239 where
2240 osi.routing_sequence_id = b.routing_sequence_id
2241 and b.set_id = TO_CHAR(to_number(USERENV('SESSIONID')))
2242 and osi.model_op_seq_id = bor.operation_sequence_id;
2243
2244
2245
2246 /*
2247 ** Insert into BOM_REF_DESGS_INTERFACE table
2248 */
2249 stmt_num := 205;
2250 insert into BOM_REF_DESGS_INTERFACE
2251 (
2252 COMPONENT_REFERENCE_DESIGNATOR,
2253 LAST_UPDATE_DATE,
2254 LAST_UPDATED_BY,
2255 CREATION_DATE,
2256 CREATED_BY,
2257 LAST_UPDATE_LOGIN,
2258 REF_DESIGNATOR_COMMENT,
2259 CHANGE_NOTICE,
2260 COMPONENT_SEQUENCE_ID,
2261 ACD_TYPE,
2262 REQUEST_ID,
2263 PROGRAM_APPLICATION_ID,
2264 PROGRAM_ID,
2265 PROGRAM_UPDATE_DATE,
2266 ATTRIBUTE_CATEGORY,
2267 ATTRIBUTE1,
2268 ATTRIBUTE2,
2269 ATTRIBUTE3,
2270 ATTRIBUTE4,
2271 ATTRIBUTE5,
2272 ATTRIBUTE6,
2273 ATTRIBUTE7,
2274 ATTRIBUTE8,
2275 ATTRIBUTE9,
2276 ATTRIBUTE10,
2277 ATTRIBUTE11,
2278 ATTRIBUTE12,
2279 ATTRIBUTE13,
2280 ATTRIBUTE14,
2281 ATTRIBUTE15,
2282 BILL_SEQUENCE_ID,
2283 ASSEMBLY_ITEM_ID,
2284 ALTERNATE_BOM_DESIGNATOR,
2285 ORGANIZATION_ID,
2286 COMPONENT_ITEM_ID,
2287 OPERATION_SEQ_NUM
2288 )
2289 select
2290 r.component_reference_designator,
2291 SYSDATE,
2292 1,
2293 SYSDATE,
2294 1,
2295 1,
2296 r.REF_DESIGNATOR_COMMENT,
2297 NULL,
2298 ic.COMPONENT_SEQUENCE_ID,
2299 r.ACD_TYPE,
2300 NULL,
2301 NULL,
2302 NULL,
2303 NULL,
2304 r.ATTRIBUTE_CATEGORY,
2305 r.ATTRIBUTE1,
2306 r.ATTRIBUTE2,
2307 r.ATTRIBUTE3,
2308 r.ATTRIBUTE4,
2309 r.ATTRIBUTE5,
2310 r.ATTRIBUTE6,
2311 r.ATTRIBUTE7,
2312 r.ATTRIBUTE8,
2313 r.ATTRIBUTE9,
2314 r.ATTRIBUTE10,
2315 r.ATTRIBUTE11,
2316 r.ATTRIBUTE12,
2317 r.ATTRIBUTE13,
2318 r.ATTRIBUTE14,
2319 r.ATTRIBUTE15,
2320 ic.BILL_SEQUENCE_ID,
2321 ic.ASSEMBLY_ITEM_ID,
2322 ic.ALTERNATE_BOM_DESIGNATOR,
2323 ic.ORGANIZATION_ID,
2324 ic.COMPONENT_ITEM_ID,
2325 ic.operation_seq_num
2326 from
2327 bom_inventory_comps_interface ic,
2328 bom_reference_designators r,
2329 bom_bill_of_mtls_interface b
2330 where b.set_id = TO_CHAR(to_number(USERENV('SESSIONID')))
2331 and ic.bill_sequence_id = b.bill_sequence_id
2332 and r.component_sequence_id = ic.model_comp_seq_id
2333 and nvl(r.acd_type,0) <> 3;
2334
2335 /*
2336 ** Update MTL_DESC_ELEM_VAL_INTERFACE table
2337 */
2338 table_name := 'MTL_DESC_ELEM_VAL_INTERFACE';
2339 stmt_num := 210;
2340 update MTL_DESC_ELEM_VAL_INTERFACE i
2341 set i.element_value =
2342 ( select /*+ ORDERED */
2343 NVL(max(v.element_value),i.element_value)
2344 from
2345 bom_bill_of_mtls_interface bi,
2346 bom_inventory_comps_interface bci,
2347 bom_inventory_components bc,
2348 bom_dependent_desc_elements be,
2349 mtl_descr_element_values v
2350 where bi.set_id = TO_CHAR(to_number(USERENV('SESSIONID')))
2351 and bi.assembly_item_id = i.inventory_item_id
2352 and bci.bill_sequence_id = bi.bill_sequence_id
2353 and bc.component_sequence_id = bci.model_comp_seq_id
2354 and be.bill_sequence_id = bc.bill_sequence_id
2355 and be.element_name = i.element_name
2356 and v.inventory_item_id = bci.component_item_id
2357 and v.element_name = i.element_name)
2358 where i.inventory_item_id =
2359 (select inventory_item_id
2360 from mtl_system_items_interface m
2361 where inventory_item_id = i.inventory_item_id
2362 and set_id = TO_CHAR(to_number(USERENV('SESSIONID'))));
2363
2364 /*
2365 ** Update descriptions of the config items in
2366 ** the MTL_SYSTEM_ITEMS_INTERFACE
2367 */
2368 stmt_num := 220;
2369 open ee;
2370
2371 loop
2372 fetch ee into item_id,org_id;
2373 exit when (ee%notfound);
2374 status := bmlupid_update_item_desc(item_id,org_id,error_message);
2375 if status <> 0 then
2376 raise UP_DESC_ERR;
2377 end if;
2378 end loop;
2379 close ee;
2380 return(1);
2381
2382 EXCEPTION
2383 WHEN NO_DATA_FOUND THEN
2384 return(1);
2385 WHEN UP_DESC_ERR then
2386 message_name := 'BOM_ATO_LOAD_ERROR';
2387 return(0);
2388 WHEN OTHERS THEN
2389 error_message := 'BOMPLDCB:'||to_char(stmt_num)||':'||substrb(sqlerrm,1,150);
2390 message_name := 'BOM_ATO_LOAD_ERROR';
2391 return(0);
2392
2393 END bmldbrt_load_bom_rtg;
2394 END BOMPLDCB;