[Home] [Help]
PACKAGE BODY: APPS.BOMPPRCB
Source
1 package body BOMPPRCB as
2 /* $Header: BOMPRCBB.pls 115.6 2002/10/31 07:33:35 djebar ship $ */
3 function bmprobm_process_bom
4 ( ato_flag in NUMBER,
5 prg_appid in NUMBER,
6 prg_id in NUMBER,
7 req_id in NUMBER,
8 user_id in NUMBER,
9 login_id in NUMBER,
10 error_message out VARCHAR2,
11 message_name out VARCHAR2,
12 table_name out VARCHAR2)
13 return integer
14 is
15 stmt_num number;
16 BEGIN
17
18 /*
19 ** process bom interface table
20 */
21 stmt_num := 10;
22 table_name := 'BOM_BILL_OF_MATERIALS';
23 insert into BOM_BILL_OF_MATERIALS(
24 assembly_item_id,
25 organization_id,
26 alternate_bom_designator,
27 last_update_date,
28 last_updated_by,
29 creation_date,
30 created_by,
31 last_update_login,
32 specific_assembly_comment,
33 pending_from_ecn,
34 attribute_category,
35 attribute1,
36 attribute2,
37 attribute3,
38 attribute4,
39 attribute5,
40 attribute6,
41 attribute7,
42 attribute8,
43 attribute9,
44 attribute10,
45 attribute11,
46 attribute12,
47 attribute13,
48 attribute14,
49 attribute15,
50 assembly_type,
51 common_bill_sequence_id,
52 bill_sequence_id,
53 request_id,
54 program_application_id,
55 program_id,
56 program_update_date
57 )
58 select
59 b.assembly_item_id,
60 b.organization_id,
61 b.alternate_bom_designator,
62 b.last_update_date,
63 user_id, /* last_updated_by */
64 b.creation_date,
65 user_id, /* created_by */
66 login_id, /* last_update_login */
67 b.specific_assembly_comment,
68 b.pending_from_ecn,
69 b.attribute_category,
70 b.attribute1,
71 b.attribute2,
72 b.attribute3,
73 b.attribute4,
74 b.attribute5,
75 b.attribute6,
76 b.attribute7,
77 b.attribute8,
78 b.attribute9,
79 b.attribute10,
80 b.attribute11,
81 b.attribute12,
82 b.attribute13,
83 b.attribute14,
84 b.attribute15,
85 b.assembly_type,
86 b.common_bill_sequence_id,
87 b.bill_sequence_id,
88 req_id, /* request_id */
89 prg_appid, /* program_application_id */
90 prg_id, /* program_id */
91 SYSDATE /* program_update_date */
92 from BOM_BILL_OF_MTLS_INTERFACE b
93 where b.set_id = TO_CHAR(to_number(USERENV('SESSIONID')));
94
95 /*
96 ** Process inventory components interface table
97 */
98
99 stmt_num := 20;
100 table_name := 'BOM_INVENTORY_COMPONENTS';
101 insert into BOM_INVENTORY_COMPONENTS
102 (
103 OPERATION_SEQ_NUM,
104 COMPONENT_ITEM_ID,
105 LAST_UPDATE_DATE,
106 LAST_UPDATED_BY,
107 CREATION_DATE,
108 CREATED_BY,
109 LAST_UPDATE_LOGIN,
110 ITEM_NUM,
111 COMPONENT_QUANTITY,
112 COMPONENT_YIELD_FACTOR,
113 COMPONENT_REMARKS,
114 EFFECTIVITY_DATE,
115 CHANGE_NOTICE,
116 IMPLEMENTATION_DATE,
117 DISABLE_DATE,
118 ATTRIBUTE_CATEGORY,
119 ATTRIBUTE1,
120 ATTRIBUTE2,
121 ATTRIBUTE3,
122 ATTRIBUTE4,
123 ATTRIBUTE5,
124 ATTRIBUTE6,
125 ATTRIBUTE7,
126 ATTRIBUTE8,
127 ATTRIBUTE9,
128 ATTRIBUTE10,
129 ATTRIBUTE11,
130 ATTRIBUTE12,
131 ATTRIBUTE13,
132 ATTRIBUTE14,
133 ATTRIBUTE15,
134 PLANNING_FACTOR,
135 QUANTITY_RELATED,
136 SO_BASIS,
137 OPTIONAL,
138 MUTUALLY_EXCLUSIVE_OPTIONS,
139 INCLUDE_IN_COST_ROLLUP,
140 CHECK_ATP,
141 SHIPPING_ALLOWED,
142 REQUIRED_TO_SHIP,
143 REQUIRED_FOR_REVENUE,
144 INCLUDE_ON_SHIP_DOCS,
145 INCLUDE_ON_BILL_DOCS,
146 LOW_QUANTITY,
147 HIGH_QUANTITY,
148 ACD_TYPE,
149 OLD_COMPONENT_SEQUENCE_ID,
150 COMPONENT_SEQUENCE_ID,
151 BILL_SEQUENCE_ID,
152 REQUEST_ID,
153 PROGRAM_APPLICATION_ID,
154 PROGRAM_ID,
155 PROGRAM_UPDATE_DATE,
156 WIP_SUPPLY_TYPE,
157 OPERATION_LEAD_TIME_PERCENT,
158 REVISED_ITEM_SEQUENCE_ID,
159 SUPPLY_LOCATOR_ID,
160 SUPPLY_SUBINVENTORY,
161 PICK_COMPONENTS,
162 BOM_ITEM_TYPE
163 )
164 select
165 b.OPERATION_SEQ_NUM,
166 b.COMPONENT_ITEM_ID,
167 b.LAST_UPDATE_DATE,
168 user_id, /* LAST_UPDATED_BY */
169 b.CREATION_DATE,
170 user_id, /* created_by */
171 login_id, /* last_update_login */
172 b.ITEM_NUM,
173 b.COMPONENT_QUANTITY,
174 b.COMPONENT_YIELD_FACTOR,
175 b.COMPONENT_REMARKS,
176 b.EFFECTIVITY_DATE,
177 b.CHANGE_NOTICE,
178 b.IMPLEMENTATION_DATE,
179 b.DISABLE_DATE,
180 b.ATTRIBUTE_CATEGORY,
181 b.ATTRIBUTE1,
182 b.ATTRIBUTE2,
183 b.ATTRIBUTE3,
184 b.ATTRIBUTE4,
185 b.ATTRIBUTE5,
186 b.ATTRIBUTE6,
187 b.ATTRIBUTE7,
188 b.ATTRIBUTE8,
189 b.ATTRIBUTE9,
190 b.ATTRIBUTE10,
191 b.ATTRIBUTE11,
192 b.ATTRIBUTE12,
193 b.ATTRIBUTE13,
194 b.ATTRIBUTE14,
195 b.ATTRIBUTE15,
196 b.PLANNING_FACTOR,
197 b.QUANTITY_RELATED,
198 b.SO_BASIS,
199 b.OPTIONAL,
200 b.MUTUALLY_EXCLUSIVE_OPTIONS,
201 b.INCLUDE_IN_COST_ROLLUP,
202 b.CHECK_ATP,
203 b.SHIPPING_ALLOWED,
204 b.REQUIRED_TO_SHIP,
205 b.REQUIRED_FOR_REVENUE,
206 b.INCLUDE_ON_SHIP_DOCS,
207 b.INCLUDE_ON_BILL_DOCS,
208 b.LOW_QUANTITY,
209 b.HIGH_QUANTITY,
210 b.ACD_TYPE,
211 b.OLD_COMPONENT_SEQUENCE_ID,
212 b.COMPONENT_SEQUENCE_ID,
213 b.BILL_SEQUENCE_ID,
214 req_id, /* request_id */
215 prg_appid, /* program_application_id */
216 prg_id, /* program_id */
217 SYSDATE, /* program_update_date */
218 b.WIP_SUPPLY_TYPE,
219 b.OPERATION_LEAD_TIME_PERCENT,
220 b.REVISED_ITEM_SEQUENCE_ID,
221 b.SUPPLY_LOCATOR_ID,
222 b.SUPPLY_SUBINVENTORY,
223 b.PICK_COMPONENTS,
224 i.BOM_ITEM_TYPE
225 from MTL_SYSTEM_ITEMS i,
226 BOM_INVENTORY_COMPS_INTERFACE b,
227 BOM_BILL_OF_MTLS_INTERFACE b1
228 where b.bill_sequence_id = b1.bill_sequence_id
229 and b1.organization_id = i.organization_id
230 and b.component_item_id = i.inventory_item_id
231 and b1.set_id = TO_CHAR(to_number(USERENV('SESSIONID')));
232
233
234 /*
235 ** Process reference designators interface table
236 */
237 stmt_num := 40;
238 table_name := 'BOM_REFERENCE_DESIGNATORS';
239 insert into BOM_REFERENCE_DESIGNATORS
240 (
241 COMPONENT_REFERENCE_DESIGNATOR,
242 LAST_UPDATE_DATE,
243 LAST_UPDATED_BY,
244 CREATION_DATE,
245 CREATED_BY,
246 LAST_UPDATE_LOGIN,
247 REF_DESIGNATOR_COMMENT,
248 CHANGE_NOTICE ,
249 COMPONENT_SEQUENCE_ID,
250 ACD_TYPE,
251 REQUEST_ID,
252 PROGRAM_APPLICATION_ID,
253 PROGRAM_ID,
254 PROGRAM_UPDATE_DATE,
255 ATTRIBUTE_CATEGORY,
256 ATTRIBUTE1 ,
257 ATTRIBUTE2,
258 ATTRIBUTE3,
259 ATTRIBUTE4,
260 ATTRIBUTE5,
261 ATTRIBUTE6,
262 ATTRIBUTE7,
263 ATTRIBUTE8,
264 ATTRIBUTE9,
265 ATTRIBUTE10,
266 ATTRIBUTE11,
267 ATTRIBUTE12,
268 ATTRIBUTE13,
269 ATTRIBUTE14,
270 ATTRIBUTE15
271 )
272 select
273 b1.COMPONENT_REFERENCE_DESIGNATOR,
274 b1.LAST_UPDATE_DATE,
275 user_id, /* LAST_UPDATED_BY */
276 b1.CREATION_DATE,
277 user_id, /* created_by */
278 login_id, /* last_update_login */
279 b1.REF_DESIGNATOR_COMMENT,
280 b1.CHANGE_NOTICE ,
281 b1.COMPONENT_SEQUENCE_ID,
282 b1.ACD_TYPE,
283 req_id, /* request_id */
284 prg_appid, /* program_application_id */
285 prg_id, /* program_id */
286 SYSDATE, /* program_update_date */
287 b1.ATTRIBUTE_CATEGORY,
288 b1.ATTRIBUTE1 ,
289 b1.ATTRIBUTE2,
290 b1.ATTRIBUTE3,
291 b1.ATTRIBUTE4,
292 b1.ATTRIBUTE5,
293 b1.ATTRIBUTE6,
294 b1.ATTRIBUTE7,
295 b1.ATTRIBUTE8,
296 b1.ATTRIBUTE9,
297 b1.ATTRIBUTE10,
298 b1.ATTRIBUTE11,
299 b1.ATTRIBUTE12,
300 b1.ATTRIBUTE13,
301 b1.ATTRIBUTE14,
302 b1.ATTRIBUTE15
303 from BOM_REF_DESGS_INTERFACE b1,
304 BOM_INVENTORY_COMPS_INTERFACE b2,
305 BOM_BILL_OF_MTLS_INTERFACE b3
306 where b1.component_sequence_id = b2.component_sequence_id
307 and b2.bill_sequence_id = b3.bill_sequence_id
308 and b3.set_id = TO_CHAR(to_number(USERENV('SESSIONID')));
309
310 /*
311 ** Process substitute components interface table
312 */
313 stmt_num := 50;
314 table_name := 'BOM_SUBSTITUTE_COMPONENTS';
315 insert into BOM_SUBSTITUTE_COMPONENTS
316 (
317 SUBSTITUTE_COMPONENT_ID,
318 LAST_UPDATE_DATE,
319 LAST_UPDATED_BY,
320 CREATION_DATE,
321 CREATED_BY,
322 LAST_UPDATE_LOGIN,
323 SUBSTITUTE_ITEM_QUANTITY,
324 ACD_TYPE,
325 ATTRIBUTE_CATEGORY,
326 ATTRIBUTE1,
327 ATTRIBUTE2 ,
328 ATTRIBUTE3,
329 ATTRIBUTE4,
330 ATTRIBUTE5,
331 ATTRIBUTE6,
332 ATTRIBUTE7,
333 ATTRIBUTE8,
334 ATTRIBUTE9,
335 ATTRIBUTE10,
336 ATTRIBUTE11,
337 ATTRIBUTE12,
338 ATTRIBUTE13,
339 ATTRIBUTE14,
340 ATTRIBUTE15,
341 CHANGE_NOTICE ,
342 COMPONENT_SEQUENCE_ID,
343 REQUEST_ID,
344 PROGRAM_APPLICATION_ID,
345 PROGRAM_ID ,
346 PROGRAM_UPDATE_DATE
347 )
348 select
349 b1.SUBSTITUTE_COMPONENT_ID,
350 b1.LAST_UPDATE_DATE,
351 user_id, /* LAST_UPDATED_BY */
352 b1.CREATION_DATE,
353 user_id, /* created_by */
354 login_id, /* last_update_login */
355 b1.SUBSTITUTE_ITEM_QUANTITY,
356 b1.ACD_TYPE,
357 b1.ATTRIBUTE_CATEGORY,
358 b1.ATTRIBUTE1,
359 b1.ATTRIBUTE2 ,
360 b1.ATTRIBUTE3,
361 b1.ATTRIBUTE4,
362 b1.ATTRIBUTE5,
363 b1.ATTRIBUTE6,
364 b1.ATTRIBUTE7,
365 b1.ATTRIBUTE8,
366 b1.ATTRIBUTE9,
367 b1.ATTRIBUTE10,
368 b1.ATTRIBUTE11,
369 b1.ATTRIBUTE12,
370 b1.ATTRIBUTE13,
371 b1.ATTRIBUTE14,
372 b1.ATTRIBUTE15,
373 b1.CHANGE_NOTICE,
374 b1.COMPONENT_SEQUENCE_ID,
375 req_id, /* request_id */
376 prg_appid, /* program_application_id */
377 prg_id, /* program_id */
378 SYSDATE /* program_update_date */
379 from BOM_SUB_COMPS_INTERFACE b1,
380 BOM_INVENTORY_COMPS_INTERFACE b2,
381 BOM_BILL_OF_MTLS_INTERFACE b3
382 where b1.component_sequence_id = b2.component_sequence_id
383 and b2.bill_sequence_id = b3.bill_sequence_id
384 and b3.set_id = TO_CHAR(to_number(USERENV('SESSIONID')));
385
386 return(1);
387
388 EXCEPTION
389 WHEN NO_DATA_FOUND THEN
390 return(1);
391 WHEN OTHERS THEN
392 error_message := 'BOMPPRCB:'||to_char(stmt_num)||':'|| substrb(sqlerrm,1,150);
393 message_name := 'BOM_ATO_PROCESS_ERROR';
394 return(0);
395
396 END bmprobm_process_bom;
397
398
399 function bmprort_process_rtg
400 ( ato_flag in NUMBER,
401 perform_fc in NUMBER,
402 prg_appid in NUMBER,
403 prg_id in NUMBER,
404 req_id in NUMBER,
405 user_id in NUMBER,
406 login_id in NUMBER,
407 error_message out VARCHAR2,
408 message_name out VARCHAR2,
409 table_name out VARCHAR2)
410 return integer
411 is
412 stmt_num number;
413 x_install_cfm BOOLEAN;
414 x_status VARCHAR2(1);
415 x_industry VARCHAR2(1);
416 x_schema VARCHAR2(30);
417 l_routing number;
418
419 cursor allRoutings is
420 select organization_id, routing_sequence_id,cfm_routing_flag
421 from bom_op_routings_interface b
422 where b.set_id = to_char(to_number(USERENV('SESSIONID')));
423
424 cursor allops is
425 select operation_sequence_id, model_op_seq_id
426 from bom_op_sequences_interface
427 where routing_sequence_id = l_routing;
428
429 BEGIN
430
431 /*
432 ** Process routing header interface table
433 */
434 stmt_num := 60;
435 table_name := 'BOM_OPERATIONAL_ROUTINGS';
436 insert into BOM_OPERATIONAL_ROUTINGS
437 (
438 ROUTING_SEQUENCE_ID,
439 ASSEMBLY_ITEM_ID,
440 ORGANIZATION_ID,
441 ALTERNATE_ROUTING_DESIGNATOR,
442 LAST_UPDATE_DATE,
443 LAST_UPDATED_BY,
444 CREATION_DATE,
445 CREATED_BY,
446 LAST_UPDATE_LOGIN,
447 ROUTING_TYPE,
448 COMMON_ROUTING_SEQUENCE_ID,
449 COMMON_ASSEMBLY_ITEM_ID,
450 ROUTING_COMMENT,
451 COMPLETION_SUBINVENTORY,
452 COMPLETION_LOCATOR_ID,
453 ATTRIBUTE_CATEGORY,
454 ATTRIBUTE1,
455 ATTRIBUTE2,
456 ATTRIBUTE3,
457 ATTRIBUTE4,
458 ATTRIBUTE5,
459 ATTRIBUTE6,
460 ATTRIBUTE7,
461 ATTRIBUTE8,
462 ATTRIBUTE9,
463 ATTRIBUTE10,
464 ATTRIBUTE11,
465 ATTRIBUTE12,
466 ATTRIBUTE13,
467 ATTRIBUTE14,
468 ATTRIBUTE15,
469 REQUEST_ID,
470 PROGRAM_APPLICATION_ID,
471 PROGRAM_ID,
472 PROGRAM_UPDATE_DATE,
473 LINE_ID,
474 CFM_ROUTING_FLAG,
475 MIXED_MODEL_MAP_FLAG,
476 PRIORITY,
477 TOTAL_PRODUCT_CYCLE_TIME,
478 CTP_FLAG
479 )
480 select
481 b.ROUTING_SEQUENCE_ID,
482 b.ASSEMBLY_ITEM_ID,
483 b.ORGANIZATION_ID,
484 b.ALTERNATE_ROUTING_DESIGNATOR,
485 b.LAST_UPDATE_DATE,
486 user_id, /* LAST_UPDATED_BY */
487 b.CREATION_DATE,
488 user_id, /* created_by */
489 login_id, /* last_update_login */
490 b.ROUTING_TYPE,
491 b.COMMON_ROUTING_SEQUENCE_ID,
492 b.COMMON_ASSEMBLY_ITEM_ID,
493 b.ROUTING_COMMENT,
494 b.COMPLETION_SUBINVENTORY,
495 b.COMPLETION_LOCATOR_ID,
496 b.ATTRIBUTE_CATEGORY,
497 b.ATTRIBUTE1,
498 b.ATTRIBUTE2,
499 b.ATTRIBUTE3,
500 b.ATTRIBUTE4,
501 b.ATTRIBUTE5,
502 b.ATTRIBUTE6,
503 b.ATTRIBUTE7,
504 b.ATTRIBUTE8,
505 b.ATTRIBUTE9,
506 b.ATTRIBUTE10,
507 b.ATTRIBUTE11,
508 b.ATTRIBUTE12,
509 b.ATTRIBUTE13,
510 b.ATTRIBUTE14,
511 b.ATTRIBUTE15,
512 req_id, /* request_id */
513 prg_appid, /* program_application_id */
514 prg_id, /* program_id */
515 SYSDATE, /* program_update_date */
516 line_id,
517 cfm_routing_flag,
518 mixed_model_map_flag,
519 priority,
520 total_product_cycle_time,
521 ctp_flag
522 from BOM_OP_ROUTINGS_INTERFACE b
523 where b.set_id = TO_CHAR(to_number(USERENV('SESSIONID')));
524
525 /*
526 ** Process routing revision interface table
527 */
528 stmt_num := 70;
529 table_name := 'MTL_RTG_ITEM_REVISIONS';
530 insert into MTL_RTG_ITEM_REVISIONS
531 (
532 INVENTORY_ITEM_ID,
533 ORGANIZATION_ID,
534 PROCESS_REVISION,
535 LAST_UPDATE_DATE,
536 LAST_UPDATED_BY,
537 CREATION_DATE,
538 CREATED_BY,
539 LAST_UPDATE_LOGIN,
540 CHANGE_NOTICE ,
541 ECN_INITIATION_DATE,
542 IMPLEMENTATION_DATE,
543 IMPLEMENTED_SERIAL_NUMBER,
544 EFFECTIVITY_DATE ,
545 ATTRIBUTE_CATEGORY,
546 ATTRIBUTE1 ,
547 ATTRIBUTE2,
548 ATTRIBUTE3,
549 ATTRIBUTE4,
550 ATTRIBUTE5,
551 ATTRIBUTE6,
552 ATTRIBUTE7,
553 ATTRIBUTE8,
554 ATTRIBUTE9,
555 ATTRIBUTE10,
556 ATTRIBUTE11,
557 ATTRIBUTE12,
558 ATTRIBUTE13 ,
559 ATTRIBUTE14,
560 ATTRIBUTE15
561 )
562 select
563 ASSEMBLY_ITEM_ID,
564 ORGANIZATION_ID,
565 PROCESS_REVISION,
566 SYSDATE, /* LAST_UPDATE_DATE */
567 user_id, /* LAST_UPDATED_BY */
568 SYSDATE, /* CREATION_DATE */
569 user_id, /* created_by */
570 login_id, /* last_update_login */
571 NULL, /* CHANGE_NOTICE */
572 NULL, /* ECN_INITIATION_DATE */
573 TRUNC(SYSDATE), /* IMPLEMENTATION_DATE */
574 NULL, /* IMPLEMENTED_SERIAL_NUMBER */
575 TRUNC(SYSDATE), /* EFFECTIVITY_DATE */
576 NULL, /* ATTRIBUTE_CATEGORY */
577 NULL, /* ATTRIBUTE1 */
578 NULL, /* ATTRIBUTE2 */
579 NULL, /* ATTRIBUTE3 */
580 NULL, /* ATTRIBUTE4 */
581 NULL, /* ATTRIBUTE5 */
582 NULL, /* ATTRIBUTE6 */
583 NULL, /* ATTRIBUTE7 */
584 NULL, /* ATTRIBUTE8 */
585 NULL, /* ATTRIBUTE9 */
586 NULL, /* ATTRIBUTE10 */
587 NULL, /* ATTRIBUTE11 */
588 NULL, /* ATTRIBUTE12 */
589 NULL, /* ATTRIBUTE13 */
590 NULL, /* ATTRIBUTE14 */
591 NULL /* ATTRIBUTE15 */
592 from bom_op_routings_interface
593 where set_id = TO_CHAR(to_number(USERENV('SESSIONID')));
594
595 /*
596 ** Process operation sequences interface table
597 */
598 stmt_num := 80;
599 table_name := 'BOM_OPERATION_SEQUENCES';
600 insert into BOM_OPERATION_SEQUENCES
601 (
602 OPERATION_SEQUENCE_ID,
603 ROUTING_SEQUENCE_ID,
604 OPERATION_SEQ_NUM,
605 LAST_UPDATE_DATE,
606 LAST_UPDATED_BY,
607 CREATION_DATE,
608 CREATED_BY,
609 LAST_UPDATE_LOGIN,
610 STANDARD_OPERATION_ID,
611 DEPARTMENT_ID ,
612 OPERATION_LEAD_TIME_PERCENT,
613 MINIMUM_TRANSFER_QUANTITY,
614 COUNT_POINT_TYPE ,
615 OPERATION_DESCRIPTION,
616 EFFECTIVITY_DATE,
617 DISABLE_DATE ,
618 BACKFLUSH_FLAG,
619 OPTION_DEPENDENT_FLAG,
620 ATTRIBUTE_CATEGORY ,
621 ATTRIBUTE1,
622 ATTRIBUTE2,
623 ATTRIBUTE3,
624 ATTRIBUTE4,
625 ATTRIBUTE5,
626 ATTRIBUTE6,
627 ATTRIBUTE7,
628 ATTRIBUTE8,
629 ATTRIBUTE9,
630 ATTRIBUTE10,
631 ATTRIBUTE11,
632 ATTRIBUTE12,
633 ATTRIBUTE13,
634 ATTRIBUTE14,
635 ATTRIBUTE15,
636 REQUEST_ID,
637 PROGRAM_APPLICATION_ID,
638 PROGRAM_ID ,
639 PROGRAM_UPDATE_DATE,
640 OPERATION_TYPE,
641 REFERENCE_FLAG,
642 PROCESS_OP_SEQ_ID,
643 LINE_OP_SEQ_ID,
644 YIELD,
645 CUMULATIVE_YIELD,
646 REVERSE_CUMULATIVE_YIELD,
647 LABOR_TIME_CALC,
648 MACHINE_TIME_CALC,
649 TOTAL_TIME_CALC,
650 LABOR_TIME_USER,
651 MACHINE_TIME_USER,
652 TOTAL_TIME_USER,
653 NET_PLANNING_PERCENT
654 )
655 select
656 b.OPERATION_SEQUENCE_ID,
657 b.ROUTING_SEQUENCE_ID,
658 b.OPERATION_SEQ_NUM,
659 b.LAST_UPDATE_DATE,
660 user_id, /* LAST_UPDATED_BY */
661 b.CREATION_DATE,
662 user_id, /* created_by */
663 login_id, /* last_update_login */
664 b.STANDARD_OPERATION_ID,
665 b.DEPARTMENT_ID ,
666 b.OPERATION_LEAD_TIME_PERCENT,
667 b.MINIMUM_TRANSFER_QUANTITY,
668 b.COUNT_POINT_TYPE ,
669 b.OPERATION_DESCRIPTION,
670 b.EFFECTIVITY_DATE,
671 b.DISABLE_DATE ,
672 b.BACKFLUSH_FLAG,
673 b.OPTION_DEPENDENT_FLAG,
674 b.ATTRIBUTE_CATEGORY ,
675 b.ATTRIBUTE1,
676 b.ATTRIBUTE2,
677 b.ATTRIBUTE3,
678 b.ATTRIBUTE4,
679 b.ATTRIBUTE5,
680 b.ATTRIBUTE6,
681 b.ATTRIBUTE7,
682 b.ATTRIBUTE8,
683 b.ATTRIBUTE9,
684 b.ATTRIBUTE10,
685 b.ATTRIBUTE11,
686 b.ATTRIBUTE12,
687 b.ATTRIBUTE13,
688 b.ATTRIBUTE14,
689 b.ATTRIBUTE15,
690 req_id, /* request_id */
691 prg_appid, /* program_application_id */
692 prg_id, /* program_id */
693 SYSDATE, /* program_update_date */
694 operation_type,
695 reference_flag,
696 process_op_seq_id,
697 line_op_seq_id,
698 yield,
699 cumulative_yield,
700 reverse_cumulative_yield,
701 labor_time_calc,
702 machine_time_calc,
703 total_time_calc,
704 labor_time_user,
705 machine_time_user,
706 total_time_user,
707 net_planning_percent
708 from BOM_OP_SEQUENCES_INTERFACE b,
709 BOM_OP_ROUTINGS_INTERFACE b1
710 where b.routing_sequence_id = b1.routing_sequence_id
711 and b1.set_id = TO_CHAR(to_number(USERENV('SESSIONID')));
712
713 /*
714 ** Process operation resources interface table
715 */
716 stmt_num := 90;
717 table_name := 'BOM_OPERATION_RESOURCES';
718 insert into BOM_OPERATION_RESOURCES
719 (
720 OPERATION_SEQUENCE_ID,
721 RESOURCE_SEQ_NUM,
722 RESOURCE_ID ,
723 ACTIVITY_ID,
724 STANDARD_RATE_FLAG,
725 ASSIGNED_UNITS ,
726 USAGE_RATE_OR_AMOUNT,
727 USAGE_RATE_OR_AMOUNT_INVERSE,
728 BASIS_TYPE,
729 SCHEDULE_FLAG,
730 LAST_UPDATE_DATE,
731 LAST_UPDATED_BY,
732 CREATION_DATE,
733 CREATED_BY,
734 LAST_UPDATE_LOGIN,
735 RESOURCE_OFFSET_PERCENT,
736 AUTOCHARGE_TYPE,
737 ATTRIBUTE_CATEGORY,
738 ATTRIBUTE1,
739 ATTRIBUTE2,
740 ATTRIBUTE3,
741 ATTRIBUTE4,
742 ATTRIBUTE5,
743 ATTRIBUTE6,
744 ATTRIBUTE7,
745 ATTRIBUTE8,
746 ATTRIBUTE9,
747 ATTRIBUTE10,
748 ATTRIBUTE11,
749 ATTRIBUTE12,
750 ATTRIBUTE13,
751 ATTRIBUTE14,
752 ATTRIBUTE15,
753 REQUEST_ID,
754 PROGRAM_APPLICATION_ID,
755 PROGRAM_ID,
756 PROGRAM_UPDATE_DATE
757 )
758 select
759 b1.OPERATION_SEQUENCE_ID,
760 b1.RESOURCE_SEQ_NUM,
761 b1.RESOURCE_ID ,
762 b1.ACTIVITY_ID,
763 b1.STANDARD_RATE_FLAG,
764 b1.ASSIGNED_UNITS ,
765 b1.USAGE_RATE_OR_AMOUNT,
766 b1.USAGE_RATE_OR_AMOUNT_INVERSE,
767 b1.BASIS_TYPE,
768 b1.SCHEDULE_FLAG,
769 b1.LAST_UPDATE_DATE,
770 user_id, /* LAST_UPDATED_BY */
771 b1.CREATION_DATE,
772 user_id, /* created_by */
773 login_id, /* last_update_login */
774 b1.RESOURCE_OFFSET_PERCENT,
775 b1.AUTOCHARGE_TYPE,
776 b1.ATTRIBUTE_CATEGORY,
777 b1.ATTRIBUTE1,
778 b1.ATTRIBUTE2,
779 b1.ATTRIBUTE3,
780 b1.ATTRIBUTE4,
781 b1.ATTRIBUTE5,
782 b1.ATTRIBUTE6,
783 b1.ATTRIBUTE7,
784 b1.ATTRIBUTE8,
785 b1.ATTRIBUTE9,
786 b1.ATTRIBUTE10,
787 b1.ATTRIBUTE11,
788 b1.ATTRIBUTE12,
789 b1.ATTRIBUTE13,
790 b1.ATTRIBUTE14,
791 b1.ATTRIBUTE15,
792 req_id, /* request_id */
793 prg_appid, /* program_application_id */
794 prg_id, /* program_id */
795 SYSDATE /* program_update_date */
796 from BOM_OP_RESOURCES_INTERFACE b1,
797 BOM_OP_SEQUENCES_INTERFACE b2,
798 BOM_OP_ROUTINGS_INTERFACE b3
799 where b2.operation_sequence_id = b1.operation_sequence_id
800 and b2.routing_sequence_id = b3.routing_sequence_id
801 and b3.set_id = TO_CHAR(to_number(USERENV('SESSIONID')));
802
803 /*
804 ** Process operation Networks table
805 */
806 stmt_num := 95;
807 table_name := 'BOM_OPERATION_NETWORKS';
808
809 INSERT INTO bom_operation_networks
810 ( FROM_OP_SEQ_ID,
811 TO_OP_SEQ_ID,
812 TRANSITION_TYPE,
813 PLANNING_PCT,
814 EFFECTIVITY_DATE,
815 DISABLE_DATE,
816 CREATED_BY,
817 CREATION_DATE,
818 LAST_UPDATED_BY,
819 LAST_UPDATE_DATE,
820 LAST_UPDATE_LOGIN,
821 ATTRIBUTE_CATEGORY,
822 ATTRIBUTE1 ,
823 ATTRIBUTE2 ,
824 ATTRIBUTE3 ,
825 ATTRIBUTE4 ,
826 ATTRIBUTE5 ,
827 ATTRIBUTE6 ,
828 ATTRIBUTE7 ,
829 ATTRIBUTE8 ,
830 ATTRIBUTE9 ,
831 ATTRIBUTE10 ,
832 ATTRIBUTE11 ,
833 ATTRIBUTE12 ,
834 ATTRIBUTE13 ,
835 ATTRIBUTE14 ,
836 ATTRIBUTE15
837 )
838 SELECT
839 bos3.operation_sequence_id,
840 bos4.operation_sequence_id,
841 bon.TRANSITION_TYPE,
842 bon.PLANNING_PCT,
843 bon.EFFECTIVITY_DATE,
844 bon.DISABLE_DATE,
845 bon.CREATED_BY,
846 bon.CREATION_DATE,
847 bon.LAST_UPDATED_BY,
848 bon.LAST_UPDATE_DATE,
849 bon.LAST_UPDATE_LOGIN,
850 bon.ATTRIBUTE_CATEGORY,
851 bon.ATTRIBUTE1,
852 bon.ATTRIBUTE2,
853 bon.ATTRIBUTE3,
854 bon.ATTRIBUTE4,
855 bon.ATTRIBUTE5,
856 bon.ATTRIBUTE6,
857 bon.ATTRIBUTE7,
858 bon.ATTRIBUTE8,
859 bon.ATTRIBUTE9,
860 bon.ATTRIBUTE10,
861 bon.ATTRIBUTE11,
862 bon.ATTRIBUTE12,
863 bon.ATTRIBUTE13,
864 bon.ATTRIBUTE14,
865 bon.ATTRIBUTE15
866 FROM bom_operation_networks bon,
867 bom_operation_sequences bos1, /* 'from' Ops of model */
868 bom_operation_sequences bos2, /* 'to' Ops of model */
869 bom_operation_sequences bos3, /* 'from' Ops of config */
870 bom_operation_sequences bos4, /* 'to' Ops of config */
871 bom_op_routings_interface brif
872 WHERE bon.from_op_seq_id = bos1.operation_sequence_id
873 AND bon.to_op_seq_id = bos2.operation_sequence_id
874 AND bos1.routing_sequence_id = bos2.routing_sequence_id
875 AND bos3.routing_sequence_id = brif.routing_sequence_id
876 AND brif.cfm_routing_flag = 1
877 AND brif.set_id = to_char(to_number(USERENV('SESSIONID')))
878 AND bos3.operation_seq_num = bos1.operation_seq_num
879 AND NVL(bos3.operation_type,1) = NVL(bos1.operation_type, 1)
880 AND bos4.routing_sequence_id = bos3.routing_sequence_id
881 AND bos4.operation_seq_num = bos2.operation_seq_num
882 AND NVL(bos4.operation_type,1) = NVL(bos2.operation_type, 1)
883 AND bos1.routing_sequence_id = ( /* find the model routing */
884 select routing_sequence_id
885 from bom_operational_routings bor,
886 mtl_system_items_interface msi
887 where brif.assembly_item_id = msi.inventory_item_id
888 and brif.organization_id = msi.organization_id
889 and bor.assembly_item_id = msi.copy_item_id
890 and bor.organization_id = msi.organization_id
891 and bor.cfm_routing_flag = 1
892 and bor.alternate_routing_designator is null );
893
894
895
896 stmt_num := 95;
897
898 /** Check if flow_manufacturing is installed **/
899
900 x_install_cfm := Fnd_Installation.Get_App_Info(application_short_name => 'FLM',
901 status => x_status,
902 industry => x_industry,
903 oracle_schema => x_schema);
904
905 for nextrec in allRoutings loop
906 l_routing := nextrec.routing_sequence_id;
907
908 /* For each operation in each routing, copy attachments of operations
909 ** copied from model/option class to operations on the config item
910 */
911
912 for nextop in allops loop
913
914 FND_ATTACHED_DOCUMENTS2_PKG.copy_attachments(
915 X_from_entity_name =>'BOM_OPERATION_SEQUENCES',
916 X_from_pk1_value =>nextop.model_op_seq_id,
917 X_from_pk2_value =>'',
918 X_from_pk3_value =>'',
919 X_from_pk4_value =>'',
920 X_from_pk5_value =>'',
921 X_to_entity_name =>'BOM_OPERATION_SEQUENCES',
922 X_to_pk1_value =>nextop.operation_sequence_id,
923 X_to_pk2_value =>'',
924 X_to_pk3_value =>'',
925 X_to_pk4_value =>'',
926 X_to_pk5_value =>'',
927 X_created_by =>user_id,
928 X_last_update_login =>'',
929 X_program_application_id =>'',
930 X_program_id =>'',
931 X_request_id =>''
932 );
933 end loop;
934
935 /** if flow manufacturing is installed and the 'Perform Flow Calulations'
936 * parameter is set to 2 or 3 (perform calculations based on processes or perform
937 * calulations based on Line operations) the routing is 'flow routing' then
938 * calculate operation times, yields, net planning percent and total
939 * product cycle time for config routing
940 **/
941
942 if ( x_status = 'I' and perform_fc >1 and nextrec.cfm_routing_flag = 1 ) then
943
944 /* Calculate Operation times */
945
946 BOM_CALC_OP_TIMES_PK.calculate_operation_times(
947 arg_org_id => nextrec.organization_id,
948 arg_routing_sequence_id => nextrec.routing_sequence_id );
949
950 /* Calculate cumu yield, rev cumu yield and net plannning percent */
951
952 BOM_CALC_CYNP.calc_cynp(
953 p_routing_sequence_id => nextrec.routing_sequence_id,
954 p_operation_type => perform_fc, /* operation_type = process */
955 p_update_events => 1 ); /* update events */
956
957 /* Calculate total_product_cycle_time */
958
959 BOM_CALC_TPCT.calculate_tpct(
960 p_routing_sequence_id => nextrec.routing_sequence_id,
961 p_operation_type => perform_fc); /* Operation_type = Process */
962 end if;
963 end loop;
964 return(1);
965
966 EXCEPTION
967 WHEN NO_DATA_FOUND THEN
968 return(1);
969 WHEN OTHERS THEN
970 error_message:='BOMPPRCB:'||to_char(stmt_num)||':'||substrb(sqlerrm,1,150);
971 message_name := 'BOM_ATO_PROCESS_ERROR';
972 return(0);
973
974 END bmprort_process_rtg;
975
976
977 function bmproec_process_eco
978 ( ato_flag in NUMBER,
979 prg_appid in NUMBER,
980 prg_id in NUMBER,
981 req_id in NUMBER,
982 user_id in NUMBER,
983 login_id in NUMBER,
984 error_message out VARCHAR2,
985 message_name out VARCHAR2,
986 table_name out VARCHAR2)
987 return integer
988 is
989 stmt_num number;
990 BEGIN
991 /*
992 ** Process engineering changes interface table
993 */
994 stmt_num := 110;
995 table_name := 'ENG_ENGINEERING_CHANGES';
996 insert into ENG_ENGINEERING_CHANGES
997 (
998 CHANGE_NOTICE,
999 ORGANIZATION_ID,
1000 LAST_UPDATE_DATE,
1001 LAST_UPDATED_BY,
1002 CREATION_DATE,
1003 CREATED_BY,
1004 LAST_UPDATE_LOGIN,
1005 DESCRIPTION ,
1006 STATUS_TYPE,
1007 INITIATION_DATE,
1008 IMPLEMENTATION_DATE,
1009 CANCELLATION_DATE,
1010 CANCELLATION_COMMENTS,
1011 PRIORITY_CODE ,
1012 REASON_CODE,
1013 ESTIMATED_ENG_COST,
1014 ESTIMATED_MFG_COST,
1015 REQUESTOR_ID ,
1016 ATTRIBUTE_CATEGORY,
1017 ATTRIBUTE1,
1018 ATTRIBUTE2,
1019 ATTRIBUTE3,
1020 ATTRIBUTE4,
1021 ATTRIBUTE5,
1022 ATTRIBUTE6,
1023 ATTRIBUTE7,
1024 ATTRIBUTE8,
1025 ATTRIBUTE9,
1026 ATTRIBUTE10,
1027 ATTRIBUTE11,
1028 ATTRIBUTE12,
1029 ATTRIBUTE13,
1030 ATTRIBUTE14,
1031 ATTRIBUTE15,
1032 REQUEST_ID,
1033 PROGRAM_APPLICATION_ID,
1034 PROGRAM_ID ,
1035 PROGRAM_UPDATE_DATE,
1036 APPROVAL_DATE,
1037 APPROVAL_STATUS_TYPE,
1038 APPROVAL_LIST_ID,
1039 CHANGE_ORDER_TYPE_ID,
1040 RESPONSIBLE_ORGANIZATION_ID,
1041 APPROVAL_REQUEST_DATE,
1042 DDF_CONTEXT
1043 )
1044 select
1045 CHANGE_NOTICE,
1046 ORGANIZATION_ID,
1047 LAST_UPDATE_DATE,
1048 user_id, /* LAST_UPDATED_BY */
1049 CREATION_DATE,
1050 user_id, /* created_by */
1051 login_id , /* last_update_login */
1052 DESCRIPTION ,
1053 STATUS_TYPE,
1054 INITIATION_DATE,
1055 IMPLEMENTATION_DATE,
1056 CANCELLATION_DATE,
1057 CANCELLATION_COMMENTS,
1058 PRIORITY_CODE ,
1059 REASON_CODE,
1060 ESTIMATED_ENG_COST,
1061 ESTIMATED_MFG_COST,
1062 REQUESTOR_ID ,
1063 ATTRIBUTE_CATEGORY,
1064 ATTRIBUTE1,
1065 ATTRIBUTE2,
1066 ATTRIBUTE3,
1067 ATTRIBUTE4,
1068 ATTRIBUTE5,
1069 ATTRIBUTE6,
1070 ATTRIBUTE7,
1071 ATTRIBUTE8,
1072 ATTRIBUTE9,
1073 ATTRIBUTE10,
1074 ATTRIBUTE11,
1075 ATTRIBUTE12,
1076 ATTRIBUTE13,
1077 ATTRIBUTE14,
1078 ATTRIBUTE15,
1079 req_id, /* request_id */
1080 prg_appid, /* program_application_id */
1081 prg_id, /* program_id */
1082 SYSDATE, /* program_update_date */
1083 APPROVAL_DATE,
1084 APPROVAL_STATUS_TYPE,
1085 APPROVAL_LIST_ID,
1086 CHANGE_ORDER_TYPE_ID,
1087 RESPONSIBLE_ORGANIZATION_ID,
1088 APPROVAL_REQUEST_DATE,
1089 DDF_CONTEXT
1090 from ENG_ENG_CHANGES_INTERFACE
1091 where set_id = TO_CHAR(to_number(USERENV('SESSIONID')));
1092
1093 /*
1094 ** Process revised items interface table
1095 */
1096 stmt_num := 120;
1097 table_name := 'ENG_REVISED_ITEMS';
1098 insert into ENG_REVISED_ITEMS
1099 (
1100 CHANGE_NOTICE,
1101 ORGANIZATION_ID,
1102 REVISED_ITEM_ID,
1103 LAST_UPDATE_DATE,
1104 LAST_UPDATED_BY,
1105 CREATION_DATE,
1106 CREATED_BY,
1107 LAST_UPDATE_LOGIN,
1108 IMPLEMENTATION_DATE,
1109 DESCRIPTIVE_TEXT,
1110 CANCELLATION_DATE,
1111 CANCEL_COMMENTS,
1112 DISPOSITION_TYPE,
1113 NEW_ITEM_REVISION,
1114 AUTO_IMPLEMENT_DATE,
1115 EARLY_SCHEDULE_DATE,
1116 ATTRIBUTE_CATEGORY,
1117 ATTRIBUTE1,
1118 ATTRIBUTE2,
1119 ATTRIBUTE3,
1120 ATTRIBUTE4 ,
1121 ATTRIBUTE5,
1122 ATTRIBUTE6 ,
1123 ATTRIBUTE7,
1124 ATTRIBUTE8,
1125 ATTRIBUTE9,
1126 ATTRIBUTE10,
1127 ATTRIBUTE11,
1128 ATTRIBUTE12,
1129 ATTRIBUTE13,
1130 ATTRIBUTE14,
1131 ATTRIBUTE15,
1132 STATUS_TYPE,
1133 SCHEDULED_DATE,
1134 BILL_SEQUENCE_ID,
1135 MRP_ACTIVE ,
1136 REQUEST_ID,
1137 PROGRAM_APPLICATION_ID,
1138 PROGRAM_ID ,
1139 PROGRAM_UPDATE_DATE ,
1140 UPDATE_WIP ,
1141 USE_UP ,
1142 USE_UP_ITEM_ID ,
1143 REVISED_ITEM_SEQUENCE_ID,
1144 USE_UP_PLAN_NAME
1145 )
1146 select
1147 e.CHANGE_NOTICE,
1148 e.ORGANIZATION_ID,
1149 e.REVISED_ITEM_ID,
1150 e.LAST_UPDATE_DATE,
1151 user_id, /* LAST_UPDATED_BY */
1152 e.CREATION_DATE,
1153 user_id, /* CREATED_BY */
1154 login_id, /* LAST_UPDATE_LOGIN */
1155 e.IMPLEMENTATION_DATE,
1156 e.DESCRIPTIVE_TEXT,
1157 e.CANCELLATION_DATE,
1158 e.CANCEL_COMMENTS,
1159 e.DISPOSITION_TYPE,
1160 e.NEW_ITEM_REVISION,
1161 e.AUTO_IMPLEMENT_DATE,
1162 e.EARLY_SCHEDULE_DATE,
1163 e.ATTRIBUTE_CATEGORY,
1164 e.ATTRIBUTE1,
1165 e.ATTRIBUTE2,
1166 e.ATTRIBUTE3,
1167 e.ATTRIBUTE4 ,
1168 e.ATTRIBUTE5,
1169 e.ATTRIBUTE6 ,
1170 e.ATTRIBUTE7,
1171 e.ATTRIBUTE8,
1172 e.ATTRIBUTE9,
1173 e.ATTRIBUTE10,
1174 e.ATTRIBUTE11,
1175 e.ATTRIBUTE12,
1176 e.ATTRIBUTE13,
1177 e.ATTRIBUTE14,
1178 e.ATTRIBUTE15,
1179 e.STATUS_TYPE,
1180 e.SCHEDULED_DATE,
1181 e.BILL_SEQUENCE_ID,
1182 e.MRP_ACTIVE ,
1183 req_id, /* request_id */
1184 prg_appid, /* program_application_id */
1185 prg_id, /* program_id */
1186 SYSDATE, /* program_update_date */
1187 e.UPDATE_WIP ,
1188 e.USE_UP ,
1189 e.USE_UP_ITEM_ID ,
1190 e.REVISED_ITEM_SEQUENCE_ID,
1191 e.USE_UP_PLAN_NAME
1192 from ENG_REVISED_ITEMS_INTERFACE e,
1193 ENG_ENG_CHANGES_INTERFACE e1
1194 where e1.set_id = TO_CHAR(to_number(USERENV('SESSIONID')))
1195 and e.change_notice = e1.change_notice
1196 and e.organization_id = e1.organization_id;
1197
1198 return(1);
1199
1200 EXCEPTION
1201 WHEN NO_DATA_FOUND THEN
1202 return(1);
1203 WHEN OTHERS THEN
1204 error_message:='BOMPPRCB:'||to_char(stmt_num)||':'|| substrb(sqlerrm,1,150);
1205 message_name := 'BOM_ATO_PROCESS_ERROR';
1206 return(0);
1207
1208 END bmproec_process_eco;
1209
1210 function bmprobr_process_bom_rtg (
1211 ato_flag in out NUMBER,
1212 perform_fc in NUMBER,
1213 prg_appid in out NUMBER,
1214 prg_id in out NUMBER,
1215 req_id in out NUMBER,
1216 user_id in out NUMBER,
1217 login_id in out NUMBER,
1218 error_message out VARCHAR2,
1219 message_name out VARCHAR2,
1220 table_name out VARCHAR2)
1221 return integer
1222 is
1223 status number;
1224 stmt_num number;
1225 PROCESS_ERROR exception;
1226 x_from_sequence_id number;
1227 x_to_sequence_id number;
1228
1229 cursor allconfigs is
1230 select inventory_item_id, organization_id,copy_item_id
1231 from mtl_system_items_interface m
1232 where m.set_id = to_char(to_number(USERENV('SESSIONID')));
1233 BEGIN
1234 status := 1; /* init status */
1235
1236 stmt_num := 130;
1237 status := bmprobm_process_bom(
1238 ato_flag,
1239 prg_appid,
1240 prg_id,
1241 req_id,
1242 user_id,
1243 login_id,
1244 error_message,
1245 message_name,
1246 table_name );
1247
1248 for nextconfig in allconfigs
1249 loop
1250
1251 select common_bill_sequence_id
1252 into x_from_sequence_id
1253 from bom_bill_of_materials
1254 where assembly_item_id = nextconfig.copy_item_id
1255 and organization_id = nextconfig.organization_id
1256 and alternate_bom_designator is NULL;
1257
1258
1259 select common_bill_sequence_id
1260 into x_to_sequence_id
1261 from bom_bill_of_materials
1262 where assembly_item_id = nextconfig.inventory_item_id
1263 and organization_id = nextconfig.organization_id
1264 and alternate_bom_designator is NULL;
1265
1266 fnd_attached_documents2_pkg.copy_attachments(
1267 X_from_entity_name => 'BOM_BILL_OF_MATERIALS',
1268 X_from_pk1_value => x_from_sequence_id,
1269 X_from_pk2_value => '',
1270 X_from_pk3_value => '',
1271 X_from_pk4_value => '',
1272 X_from_pk5_value => '',
1273 X_to_entity_name => 'BOM_BILL_OF_MATERIALS',
1274 X_to_pk1_value => x_to_sequence_id,
1275 X_to_pk2_value => '',
1276 X_to_pk3_value => '',
1277 X_to_pk4_value => '',
1278 X_to_pk5_value => '',
1279 X_created_by => user_id,
1280 X_last_update_login => '',
1281 X_program_application_id=> '',
1282 X_program_id => '',
1283 X_request_id => ''
1284 );
1285 end loop;
1286
1287 if (status = 1) then
1288 stmt_num := 140;
1289 status := bmprort_process_rtg(
1290 ato_flag,
1291 perform_fc,
1292 prg_appid,
1293 prg_id,
1294 req_id,
1295 user_id,
1296 login_id,
1297 error_message,
1298 message_name,
1299 table_name );
1300 end if;
1301
1302 /*
1303 As per discussion with Shreyas it is not known why we needed this stuff here
1304 Probably it was coded as part of the open interface project
1305 if (status = 1) then
1306 stmt_num := 150;
1307 status := bmproec_process_eco(
1308 ato_flag,
1309 prg_appid,
1310 prg_id,
1311 req_id,
1312 user_id,
1313 login_id,
1314 error_message,
1315 message_name,
1316 table_name );
1317 end if;
1318 */
1319 if (status = 1) then
1320 /*
1321 ** Remove rows from BOM_INVENTORY_COMPS_INTERFACE
1322 */
1323 stmt_num := 160;
1324 table_name := 'BOM_INVENTORY_COMPS_INTERFACE';
1325 delete from BOM_INVENTORY_COMPS_INTERFACE i
1326 where i.rowid in
1327 ( select b1.rowid
1328 from bom_inventory_comps_interface b1,
1329 BOM_BILL_OF_MTLS_INTERFACE b2
1330 where b2.set_id = TO_CHAR(to_number(USERENV('SESSIONID')))
1331 and b1.bill_sequence_id = b2.bill_sequence_id);
1332
1333 /*
1334 ** Remove rows from BOM_BILL_OF_MTLS_INTERFACE
1335 */
1336 stmt_num := 180;
1337 table_name := 'BOM_BILL_OF_MTLS_INTERFACE';
1338 delete from BOM_BILL_OF_MTLS_INTERFACE b
1339 where b.set_id = TO_CHAR(to_number(USERENV('SESSIONID')));
1340
1341 /*
1342 ** Remove rows from BOM_OP_RESOURCES_INTERFACE
1343 */
1344 stmt_num := 190;
1345 table_name := 'BOM_OP_RESOURCES_INTERFACE';
1346 delete from BOM_OP_RESOURCES_INTERFACE i
1347 where i.rowid in
1348 ( select b1.rowid from
1349 bom_op_resources_interface b1,
1350 BOM_OP_SEQUENCES_INTERFACE b2,
1351 BOM_OP_ROUTINGS_INTERFACE b3
1352 where b3.routing_sequence_id = b2.routing_sequence_id
1353 and b3.set_id = TO_CHAR(to_number(USERENV('SESSIONID')))
1354 and b1.operation_sequence_id = b2.operation_sequence_id);
1355
1356
1357 /*
1358 ** Remove from BOM_OP_SEQUENCES_INTERFACE
1359 */
1360 stmt_num := 210;
1361 table_name := 'BOM_OP_SEQUENCES_INTERFACE';
1362 delete from BOM_OP_SEQUENCES_INTERFACE i
1363 where i.rowid in
1364 ( select b1.rowid from
1365 bom_op_sequences_interface b1,
1366 BOM_OP_ROUTINGS_INTERFACE b2
1367 where b2.set_id = TO_CHAR(to_number(USERENV('SESSIONID')))
1368 and b1.routing_sequence_id = b2.routing_sequence_id);
1369
1370
1371 /*
1372 ** Remove the moved rows from BOM_OP_ROUTINGS_INTERFACE
1373 */
1374
1375 stmt_num := 220;
1376 table_name := 'BOM_OP_ROUTINGS_INTERFACE';
1377 delete from BOM_OP_ROUTINGS_INTERFACE b
1378 where b.set_id = TO_CHAR(to_number(USERENV('SESSIONID')));
1379
1380 stmt_num := 230;
1381 table_name := 'BOM_REF_DESGS_INTERFACE';
1382 delete from BOM_REF_DESGS_INTERFACE b1
1383 where b1.rowid in ( select b.rowid
1384 from BOM_REF_DESGS_INTERFACE b,
1385 BOM_INVENTORY_COMPS_INTERFACE b2,
1386 BOM_BILL_OF_MTLS_INTERFACE b3,
1387 MTL_SYSTEM_ITEMS_INTERFACE m
1388 where b.component_sequence_id = b2.component_sequence_id
1389 and b2.bill_sequence_id = b3.bill_sequence_id
1390 and b3.demand_source_line = m.demand_source_line
1391 and b3.demand_source_type = m.demand_source_type
1392 and b3.demand_source_header_id = m.demand_source_header_id
1393 and b3.assembly_item_id = m.inventory_item_id
1394 and b3.organization_id = m.organization_id
1395 and m.set_id = TO_CHAR(to_number(USERENV('SESSIONID'))));
1396
1397 stmt_num := 240;
1398 table_name := 'BOM_SUB_COMPS_INTERFACE';
1399 delete from BOM_SUB_COMPS_INTERFACE b1
1400 where b1.rowid in ( select b.rowid
1401 from BOM_SUB_COMPS_INTERFACE b,
1402 BOM_INVENTORY_COMPS_INTERFACE b2,
1403 BOM_BILL_OF_MTLS_INTERFACE b3,
1404 MTL_SYSTEM_ITEMS_INTERFACE m
1405 where b.component_sequence_id = b2.component_sequence_id
1406 and b2.bill_sequence_id = b3.bill_sequence_id
1407 and b3.demand_source_line = m.demand_source_line
1408 and b3.demand_source_type = m.demand_source_type
1409 and b3.demand_source_header_id = m.demand_source_header_id
1410 and b3.assembly_item_id = m.inventory_item_id
1411 and b3.organization_id = m.organization_id
1412 and m.set_id = TO_CHAR(to_number(USERENV('SESSIONID'))));
1413
1414 /*
1415 This is commented out since we do not insert into these tables anyway
1416 stmt_num := 250;
1417 table_name := 'ENG_REVISED_ITEMS_INTERFACE';
1418 delete from ENG_REVISED_ITEMS_INTERFACE
1419 where exists ( select 1
1420 from ENG_ENG_CHANGES_INTERFACE e,
1421 ENG_REVISED_ITEMS_INTERFACE e1
1422 where e.set_id = TO_CHAR(to_number(USERENV('SESSIONID')))
1423 and e.organization_id = e1.organization_id
1424 and e.change_notice = e1.change_notice);
1425
1426 stmt_num := 260;
1427 table_name := 'ENG_ENG_CHANGES_INTERFACE';
1428 delete from ENG_ENG_CHANGES_INTERFACE
1429 where set_id = TO_CHAR(to_number(USERENV('SESSIONID')));
1430
1431 */
1432 end if;
1433 return(status);
1434
1435 EXCEPTION
1436 WHEN NO_DATA_FOUND THEN
1437 return(1);
1438 WHEN PROCESS_ERROR THEN
1439 error_message :='BOMPPRCB:'||to_char(stmt_num)||':'||substrb(sqlerrm,1,150);
1440 message_name := 'BOM_ATO_PROCESS_ERROR';
1441 return(status);
1442 WHEN OTHERS THEN
1443 error_message:='BOMPPRCB:'||to_char(stmt_num)||':'|| substrb(sqlerrm,1,150);
1444 message_name := 'BOM_ATO_PROCESS_ERROR';
1445 return(status);
1446
1447 END bmprobr_process_bom_rtg;
1448
1449 function bmprbill_process_bill_data
1450 ( prog_appid NUMBER,
1451 prog_id NUMBER,
1452 request_id NUMBER,
1453 user_id NUMBER,
1454 login_id NUMBER,
1455 error_message OUT VARCHAR2,
1456 message_name OUT VARCHAR2,
1457 table_name OUT VARCHAR2)
1458 return integer
1459 is
1460 stmt_num number;
1461 commit_cnt NUMBER;
1462 BEGIN
1463
1464 /*
1465 ** process bill interface table
1466 */
1467 stmt_num := 10;
1468 table_name := 'BOM_BILL_OF_MATERIALS';
1469 commit_cnt := 0;
1470 loop
1471 insert into BOM_BILL_OF_MATERIALS(
1472 assembly_item_id,
1473 organization_id,
1474 alternate_bom_designator,
1475 last_update_date,
1476 last_updated_by,
1477 creation_date,
1478 created_by,
1479 last_update_login,
1480 common_assembly_item_id,
1481 specific_assembly_comment,
1482 attribute_category,
1483 attribute1,
1484 attribute2,
1485 attribute3,
1486 attribute4,
1487 attribute5,
1488 attribute6,
1489 attribute7,
1490 attribute8,
1491 attribute9,
1492 attribute10,
1493 attribute11,
1494 attribute12,
1495 attribute13,
1496 attribute14,
1497 attribute15,
1498 assembly_type,
1499 common_bill_sequence_id,
1500 bill_sequence_id,
1501 request_id,
1502 program_application_id,
1503 program_id,
1504 program_update_date,
1505 common_organization_id,
1506 next_explode_date
1507 )
1508 select
1509 assembly_item_id,
1510 organization_id,
1511 alternate_bom_designator,
1512 last_update_date,
1513 last_updated_by,
1514 creation_date,
1515 created_by,
1516 last_update_login,
1517 common_assembly_item_id,
1518 specific_assembly_comment,
1519 attribute_category,
1520 attribute1,
1521 attribute2,
1522 attribute3,
1523 attribute4,
1524 attribute5,
1525 attribute6,
1526 attribute7,
1527 attribute8,
1528 attribute9,
1529 attribute10,
1530 attribute11,
1531 attribute12,
1532 attribute13,
1533 attribute14,
1534 attribute15,
1535 assembly_type,
1536 common_bill_sequence_id,
1537 bill_sequence_id,
1538 request_id,
1539 program_application_id,
1540 program_id,
1541 program_update_date,
1542 common_organization_id,
1543 next_explode_date
1544 from BOM_BILL_OF_MTLS_INTERFACE
1545 where process_flag = 4
1546 and rownum < 500;
1547
1548 EXIT when SQL%NOTFOUND;
1549
1550 update bom_bill_of_mtls_interface bi
1551 set process_flag = 7
1552 where process_flag = 4
1553 and exists (select NULL
1554 from bom_bill_of_materials bom
1555 where bom.bill_sequence_id = bi.bill_sequence_id);
1556 commit;
1557
1558 end loop;
1559 /*
1560 ** Process inventory components interface table
1561 */
1562 stmt_num := 20;
1563 table_name := 'BOM_INVENTORY_COMPONENTS';
1564 commit_cnt := 0;
1565 loop
1566 insert into BOM_INVENTORY_COMPONENTS
1567 (
1568 OPERATION_SEQ_NUM,
1569 COMPONENT_ITEM_ID,
1570 LAST_UPDATE_DATE,
1571 LAST_UPDATED_BY,
1572 CREATION_DATE,
1573 CREATED_BY,
1574 LAST_UPDATE_LOGIN,
1575 ITEM_NUM,
1576 COMPONENT_QUANTITY,
1577 COMPONENT_YIELD_FACTOR,
1578 COMPONENT_REMARKS,
1579 EFFECTIVITY_DATE,
1580 CHANGE_NOTICE,
1581 IMPLEMENTATION_DATE,
1582 DISABLE_DATE,
1583 ATTRIBUTE_CATEGORY,
1584 ATTRIBUTE1,
1585 ATTRIBUTE2,
1586 ATTRIBUTE3,
1587 ATTRIBUTE4,
1588 ATTRIBUTE5,
1589 ATTRIBUTE6,
1590 ATTRIBUTE7,
1591 ATTRIBUTE8,
1592 ATTRIBUTE9,
1593 ATTRIBUTE10,
1594 ATTRIBUTE11,
1595 ATTRIBUTE12,
1596 ATTRIBUTE13,
1597 ATTRIBUTE14,
1598 ATTRIBUTE15,
1599 PLANNING_FACTOR,
1600 QUANTITY_RELATED,
1601 SO_BASIS,
1602 OPTIONAL,
1603 MUTUALLY_EXCLUSIVE_OPTIONS,
1604 INCLUDE_IN_COST_ROLLUP,
1605 CHECK_ATP,
1606 SHIPPING_ALLOWED,
1607 REQUIRED_TO_SHIP,
1608 REQUIRED_FOR_REVENUE,
1609 INCLUDE_ON_SHIP_DOCS,
1610 LOW_QUANTITY,
1611 HIGH_QUANTITY,
1612 COMPONENT_SEQUENCE_ID,
1613 BILL_SEQUENCE_ID,
1614 REQUEST_ID,
1615 PROGRAM_APPLICATION_ID,
1616 PROGRAM_ID,
1617 PROGRAM_UPDATE_DATE,
1618 WIP_SUPPLY_TYPE,
1619 SUPPLY_LOCATOR_ID,
1620 SUPPLY_SUBINVENTORY,
1621 BOM_ITEM_TYPE
1622 )
1623 select
1624 OPERATION_SEQ_NUM,
1625 COMPONENT_ITEM_ID,
1626 LAST_UPDATE_DATE,
1627 LAST_UPDATED_BY,
1628 CREATION_DATE,
1629 created_by,
1630 last_update_login,
1631 ITEM_NUM,
1632 COMPONENT_QUANTITY,
1633 COMPONENT_YIELD_FACTOR,
1634 COMPONENT_REMARKS,
1635 EFFECTIVITY_DATE,
1636 CHANGE_NOTICE,
1637 IMPLEMENTATION_DATE,
1638 DISABLE_DATE,
1639 ATTRIBUTE_CATEGORY,
1640 ATTRIBUTE1,
1641 ATTRIBUTE2,
1642 ATTRIBUTE3,
1643 ATTRIBUTE4,
1644 ATTRIBUTE5,
1645 ATTRIBUTE6,
1646 ATTRIBUTE7,
1647 ATTRIBUTE8,
1648 ATTRIBUTE9,
1649 ATTRIBUTE10,
1650 ATTRIBUTE11,
1651 ATTRIBUTE12,
1652 ATTRIBUTE13,
1653 ATTRIBUTE14,
1654 ATTRIBUTE15,
1655 PLANNING_FACTOR,
1656 QUANTITY_RELATED,
1657 SO_BASIS,
1658 OPTIONAL,
1659 MUTUALLY_EXCLUSIVE_OPTIONS,
1660 INCLUDE_IN_COST_ROLLUP,
1661 CHECK_ATP,
1662 SHIPPING_ALLOWED,
1663 REQUIRED_TO_SHIP,
1664 REQUIRED_FOR_REVENUE,
1665 INCLUDE_ON_SHIP_DOCS,
1666 LOW_QUANTITY,
1667 HIGH_QUANTITY,
1668 COMPONENT_SEQUENCE_ID,
1669 BILL_SEQUENCE_ID,
1670 REQUEST_ID,
1671 PROGRAM_APPLICATION_ID,
1672 PROGRAM_ID,
1673 PROGRAM_UPDATE_DATE,
1674 WIP_SUPPLY_TYPE,
1675 SUPPLY_LOCATOR_ID,
1676 SUPPLY_SUBINVENTORY,
1677 BOM_ITEM_TYPE
1678 from BOM_INVENTORY_COMPS_INTERFACE
1679 where process_flag = 4
1680 and rownum < 500;
1681
1682 EXIT when SQL%NOTFOUND;
1683
1684 update bom_inventory_comps_interface bci
1685 set process_flag = 7
1686 where process_flag = 4
1687 and exists (select NULL
1688 from bom_inventory_components bic
1689 where bic.component_sequence_id = bci.component_sequence_id);
1690
1691 commit;
1692
1693 end loop;
1694
1695 /*
1696 ** Process reference designators interface table
1697 */
1698 stmt_num := 40;
1699 table_name := 'BOM_REFERENCE_DESIGNATORS';
1700 commit_cnt := 0;
1701 loop
1702 insert into BOM_REFERENCE_DESIGNATORS
1703 (
1704 COMPONENT_REFERENCE_DESIGNATOR,
1705 LAST_UPDATE_DATE,
1706 LAST_UPDATED_BY,
1707 CREATION_DATE,
1708 CREATED_BY,
1709 LAST_UPDATE_LOGIN,
1710 REF_DESIGNATOR_COMMENT,
1711 CHANGE_NOTICE,
1712 COMPONENT_SEQUENCE_ID,
1713 ACD_TYPE,
1714 REQUEST_ID,
1715 PROGRAM_APPLICATION_ID,
1716 PROGRAM_ID,
1717 PROGRAM_UPDATE_DATE,
1718 ATTRIBUTE_CATEGORY,
1719 ATTRIBUTE1,
1720 ATTRIBUTE2,
1721 ATTRIBUTE3,
1722 ATTRIBUTE4,
1723 ATTRIBUTE5,
1724 ATTRIBUTE6,
1725 ATTRIBUTE7,
1726 ATTRIBUTE8,
1727 ATTRIBUTE9,
1728 ATTRIBUTE10,
1729 ATTRIBUTE11,
1730 ATTRIBUTE12,
1731 ATTRIBUTE13,
1732 ATTRIBUTE14,
1733 ATTRIBUTE15
1734 )
1735 select
1736 COMPONENT_REFERENCE_DESIGNATOR,
1737 LAST_UPDATE_DATE,
1738 LAST_UPDATED_BY,
1739 CREATION_DATE,
1740 created_by,
1741 last_update_login,
1742 REF_DESIGNATOR_COMMENT,
1743 CHANGE_NOTICE,
1744 COMPONENT_SEQUENCE_ID,
1745 ACD_TYPE,
1746 REQUEST_ID,
1747 PROGRAM_APPLICATION_ID,
1748 PROGRAM_ID,
1749 PROGRAM_UPDATE_DATE,
1750 ATTRIBUTE_CATEGORY,
1751 ATTRIBUTE1,
1752 ATTRIBUTE2,
1753 ATTRIBUTE3,
1754 ATTRIBUTE4,
1755 ATTRIBUTE5,
1756 ATTRIBUTE6,
1757 ATTRIBUTE7,
1758 ATTRIBUTE8,
1759 ATTRIBUTE9,
1760 ATTRIBUTE10,
1761 ATTRIBUTE11,
1762 ATTRIBUTE12,
1763 ATTRIBUTE13,
1764 ATTRIBUTE14,
1765 ATTRIBUTE15
1766 from BOM_REF_DESGS_INTERFACE
1767 where process_flag = 4
1768 and rownum < 500;
1769
1770 EXIT when SQL%NOTFOUND;
1771
1772 update bom_ref_desgs_interface bdi
1773 set process_flag = 7
1774 where process_flag = 4
1775 and exists (select NULL from bom_reference_designators brd
1776 where brd.component_sequence_id = bdi.component_sequence_id
1777 and brd.component_reference_designator = bdi.component_reference_designator
1778 and nvl(brd.acd_type,999) =nvl(bdi.acd_type,999));
1779 commit;
1780 end loop;
1781
1782 /*
1783 ** Process substitute components interface table
1784 */
1785 stmt_num := 50;
1786 table_name := 'BOM_SUBSTITUTE_COMPONENTS';
1787 commit_cnt := 0;
1788 loop
1789 insert into BOM_SUBSTITUTE_COMPONENTS
1790 (
1791 SUBSTITUTE_COMPONENT_ID,
1792 LAST_UPDATE_DATE,
1793 LAST_UPDATED_BY,
1794 CREATION_DATE,
1795 CREATED_BY,
1796 LAST_UPDATE_LOGIN,
1797 SUBSTITUTE_ITEM_QUANTITY,
1798 ATTRIBUTE_CATEGORY,
1799 ATTRIBUTE1,
1800 ATTRIBUTE2,
1801 ATTRIBUTE3,
1802 ATTRIBUTE4,
1803 ATTRIBUTE5,
1804 ATTRIBUTE6,
1805 ATTRIBUTE7,
1806 ATTRIBUTE8,
1807 ATTRIBUTE9,
1808 ATTRIBUTE10,
1809 ATTRIBUTE11,
1810 ATTRIBUTE12,
1811 ATTRIBUTE13,
1812 ATTRIBUTE14,
1813 ATTRIBUTE15,
1814 COMPONENT_SEQUENCE_ID,
1815 CHANGE_NOTICE,
1816 REQUEST_ID,
1817 PROGRAM_APPLICATION_ID,
1818 PROGRAM_ID ,
1819 PROGRAM_UPDATE_DATE
1820 )
1821 select
1822 SUBSTITUTE_COMPONENT_ID,
1823 LAST_UPDATE_DATE,
1824 LAST_UPDATED_BY,
1825 CREATION_DATE,
1826 created_by,
1827 last_update_login,
1828 SUBSTITUTE_ITEM_QUANTITY,
1829 ATTRIBUTE_CATEGORY,
1830 ATTRIBUTE1,
1831 ATTRIBUTE2,
1832 ATTRIBUTE3,
1833 ATTRIBUTE4,
1834 ATTRIBUTE5,
1835 ATTRIBUTE6,
1836 ATTRIBUTE7,
1837 ATTRIBUTE8,
1838 ATTRIBUTE9,
1839 ATTRIBUTE10,
1840 ATTRIBUTE11,
1841 ATTRIBUTE12,
1842 ATTRIBUTE13,
1843 ATTRIBUTE14,
1844 ATTRIBUTE15,
1845 COMPONENT_SEQUENCE_ID,
1846 CHANGE_NOTICE,
1847 REQUEST_ID,
1848 PROGRAM_APPLICATION_ID,
1849 PROGRAM_ID ,
1850 PROGRAM_UPDATE_DATE
1851 from BOM_SUB_COMPS_INTERFACE
1852 where process_flag = 4
1853 and rownum < 500;
1854
1855 EXIT when SQL%NOTFOUND;
1856
1857 update bom_sub_comps_interface bsi
1858 set process_flag = 7
1859 where process_flag = 4
1860 and exists (select NULL from bom_substitute_components bsc
1861 where bsc.component_sequence_id = bsi.component_sequence_id
1862 and bsc.substitute_component_id = bsi.substitute_component_id
1863 and nvl(bsc.acd_type,999) = nvl(bsi.acd_type,999));
1864 commit;
1865 end loop;
1866
1867 /*
1868 ** Process item revisions interface table
1869 */
1870 stmt_num := 60;
1871 table_name := 'MTL_ITEM_REVISIONS';
1872 commit_cnt := 0;
1873 loop
1874 insert into MTL_ITEM_REVISIONS
1875 (
1876 INVENTORY_ITEM_ID,
1877 ORGANIZATION_ID,
1878 REVISION,
1879 LAST_UPDATE_DATE,
1880 LAST_UPDATED_BY,
1881 CREATION_DATE,
1882 CREATED_BY,
1883 LAST_UPDATE_LOGIN,
1884 CHANGE_NOTICE,
1885 IMPLEMENTATION_DATE,
1886 EFFECTIVITY_DATE,
1887 ATTRIBUTE_CATEGORY,
1888 ATTRIBUTE1,
1889 ATTRIBUTE2,
1890 ATTRIBUTE3,
1891 ATTRIBUTE4,
1892 ATTRIBUTE5,
1893 ATTRIBUTE6,
1894 ATTRIBUTE7,
1895 ATTRIBUTE8,
1896 ATTRIBUTE9,
1897 ATTRIBUTE10,
1898 ATTRIBUTE11,
1899 ATTRIBUTE12,
1900 ATTRIBUTE13,
1901 ATTRIBUTE14,
1902 ATTRIBUTE15,
1903 PROGRAM_APPLICATION_ID,
1904 PROGRAM_ID,
1905 PROGRAM_UPDATE_DATE,
1906 REQUEST_ID,
1907 DESCRIPTION)
1908 select
1909 INVENTORY_ITEM_ID,
1910 ORGANIZATION_ID,
1911 REVISION,
1912 LAST_UPDATE_DATE,
1913 LAST_UPDATED_BY,
1914 CREATION_DATE,
1915 CREATED_BY,
1916 LAST_UPDATE_LOGIN,
1917 CHANGE_NOTICE,
1918 IMPLEMENTATION_DATE,
1919 EFFECTIVITY_DATE,
1920 ATTRIBUTE_CATEGORY,
1921 ATTRIBUTE1,
1922 ATTRIBUTE2,
1923 ATTRIBUTE3,
1924 ATTRIBUTE4,
1925 ATTRIBUTE5,
1926 ATTRIBUTE6,
1927 ATTRIBUTE7,
1928 ATTRIBUTE8,
1929 ATTRIBUTE9,
1930 ATTRIBUTE10,
1931 ATTRIBUTE11,
1932 ATTRIBUTE12,
1933 ATTRIBUTE13,
1934 ATTRIBUTE14,
1935 ATTRIBUTE15,
1936 PROGRAM_APPLICATION_ID,
1937 PROGRAM_ID,
1938 PROGRAM_UPDATE_DATE,
1939 REQUEST_ID,
1940 DESCRIPTION
1941 from mtl_item_revisions_interface
1942 where process_flag = 4
1943 and rownum < 500;
1944
1945 EXIT when SQL%NOTFOUND;
1946
1947 update mtl_item_revisions_interface mri
1948 set process_flag = 7
1949 where process_flag = 4
1950 and exists (select NULL from mtl_item_revisions mir
1951 where mir.inventory_item_id = mri.inventory_item_id
1952 and mir.organization_id = mri.organization_id
1953 and mir.revision = mri.revision);
1954 commit;
1955 end loop;
1956
1957 return(1);
1958
1959 EXCEPTION
1960 WHEN NO_DATA_FOUND THEN
1961 return(1);
1962 WHEN OTHERS THEN
1963 rollback;
1964 error_message := 'BOMPPRCB:'||to_char(stmt_num)||':'|| substrb(sqlerrm,1,150);
1965 message_name := 'BOM_BILL_PROCESS_ERROR';
1966 return(SQLCODE);
1967
1968 END bmprbill_process_bill_data;
1969
1970 function bmprrtg_process_rtg_data
1971 ( prog_appid NUMBER,
1972 prog_id NUMBER,
1973 request_id NUMBER,
1974 user_id NUMBER,
1975 login_id NUMBER,
1976 error_message OUT VARCHAR2,
1977 message_name OUT VARCHAR2,
1978 table_name OUT VARCHAR2)
1979 return integer
1980 is
1981 stmt_num number;
1982 BEGIN
1983
1984 /*
1985 ** process routing interface table
1986 */
1987 stmt_num := 10;
1988 table_name := 'BOM_OPERATIONAL_ROUTINGS';
1989 loop
1990 insert into BOM_OPERATIONAL_ROUTINGS(
1991 ROUTING_SEQUENCE_ID,
1992 ASSEMBLY_ITEM_ID,
1993 ORGANIZATION_ID,
1994 ALTERNATE_ROUTING_DESIGNATOR,
1995 LAST_UPDATE_DATE,
1996 LAST_UPDATED_BY,
1997 CREATION_DATE,
1998 CREATED_BY,
1999 LAST_UPDATE_LOGIN,
2000 ROUTING_TYPE,
2001 COMMON_ASSEMBLY_ITEM_ID,
2002 COMMON_ROUTING_SEQUENCE_ID,
2003 ROUTING_COMMENT,
2004 COMPLETION_SUBINVENTORY,
2005 COMPLETION_LOCATOR_ID,
2006 ATTRIBUTE_CATEGORY,
2007 ATTRIBUTE1,
2008 ATTRIBUTE2,
2009 ATTRIBUTE3,
2010 ATTRIBUTE4,
2011 ATTRIBUTE5,
2012 ATTRIBUTE6,
2013 ATTRIBUTE7,
2014 ATTRIBUTE8,
2015 ATTRIBUTE9,
2016 ATTRIBUTE10,
2017 ATTRIBUTE11,
2018 ATTRIBUTE12,
2019 ATTRIBUTE13,
2020 ATTRIBUTE14,
2021 ATTRIBUTE15,
2022 REQUEST_ID,
2023 PROGRAM_APPLICATION_ID,
2024 PROGRAM_ID,
2025 PROGRAM_UPDATE_DATE
2026 )
2027 select
2028 ROUTING_SEQUENCE_ID,
2029 ASSEMBLY_ITEM_ID,
2030 ORGANIZATION_ID,
2031 ALTERNATE_ROUTING_DESIGNATOR,
2032 LAST_UPDATE_DATE,
2033 LAST_UPDATED_BY,
2034 CREATION_DATE,
2035 CREATED_BY,
2036 LAST_UPDATE_LOGIN,
2037 ROUTING_TYPE,
2038 COMMON_ASSEMBLY_ITEM_ID,
2039 COMMON_ROUTING_SEQUENCE_ID,
2040 ROUTING_COMMENT,
2041 COMPLETION_SUBINVENTORY,
2042 COMPLETION_LOCATOR_ID,
2043 ATTRIBUTE_CATEGORY,
2044 ATTRIBUTE1,
2045 ATTRIBUTE2,
2046 ATTRIBUTE3,
2047 ATTRIBUTE4,
2048 ATTRIBUTE5,
2049 ATTRIBUTE6,
2050 ATTRIBUTE7,
2051 ATTRIBUTE8,
2052 ATTRIBUTE9,
2053 ATTRIBUTE10,
2054 ATTRIBUTE11,
2055 ATTRIBUTE12,
2056 ATTRIBUTE13,
2057 ATTRIBUTE14,
2058 ATTRIBUTE15,
2059 REQUEST_ID,
2060 PROGRAM_APPLICATION_ID,
2061 PROGRAM_ID,
2062 PROGRAM_UPDATE_DATE
2063 from BOM_OP_ROUTINGS_INTERFACE
2064 where process_flag = 4
2065 and rownum < 500;
2066
2067 EXIT when SQL%NOTFOUND;
2068
2069 update bom_op_routings_interface bri
2070 set process_flag = 7
2071 where process_flag = 4
2072 and exists (select NULL from bom_operational_routings bor
2073 where bor.routing_sequence_id =
2074 bri.routing_sequence_id);
2075 commit;
2076 end loop;
2077 /*
2078 ** Process operation sequences interface tables
2079 */
2080
2081 stmt_num := 20;
2082 table_name := 'BOM_OPERATION_SEQUENCES';
2083 loop
2084 insert into BOM_OPERATION_SEQUENCES
2085 (
2086 OPERATION_SEQUENCE_ID,
2087 ROUTING_SEQUENCE_ID,
2088 OPERATION_SEQ_NUM,
2089 LAST_UPDATE_DATE,
2090 LAST_UPDATED_BY,
2091 CREATION_DATE,
2092 CREATED_BY,
2093 LAST_UPDATE_LOGIN,
2094 STANDARD_OPERATION_ID,
2095 DEPARTMENT_ID,
2096 OPERATION_LEAD_TIME_PERCENT,
2097 MINIMUM_TRANSFER_QUANTITY,
2098 COUNT_POINT_TYPE,
2099 OPERATION_DESCRIPTION,
2100 EFFECTIVITY_DATE,
2101 DISABLE_DATE,
2102 BACKFLUSH_FLAG,
2103 OPTION_DEPENDENT_FLAG,
2104 ATTRIBUTE_CATEGORY,
2105 ATTRIBUTE1,
2106 ATTRIBUTE2,
2107 ATTRIBUTE3,
2108 ATTRIBUTE4,
2109 ATTRIBUTE5,
2110 ATTRIBUTE6,
2111 ATTRIBUTE7,
2112 ATTRIBUTE8,
2113 ATTRIBUTE9,
2114 ATTRIBUTE10,
2115 ATTRIBUTE11,
2116 ATTRIBUTE12,
2117 ATTRIBUTE13,
2118 ATTRIBUTE14,
2119 ATTRIBUTE15,
2120 REQUEST_ID,
2121 PROGRAM_APPLICATION_ID,
2122 PROGRAM_ID,
2123 PROGRAM_UPDATE_DATE
2124 )
2125 select
2126 OPERATION_SEQUENCE_ID,
2127 ROUTING_SEQUENCE_ID,
2128 OPERATION_SEQ_NUM,
2129 LAST_UPDATE_DATE,
2130 LAST_UPDATED_BY,
2131 CREATION_DATE,
2132 CREATED_BY,
2133 LAST_UPDATE_LOGIN,
2134 STANDARD_OPERATION_ID,
2135 DEPARTMENT_ID,
2136 OPERATION_LEAD_TIME_PERCENT,
2137 MINIMUM_TRANSFER_QUANTITY,
2138 COUNT_POINT_TYPE,
2139 OPERATION_DESCRIPTION,
2140 -- trunc(EFFECTIVITY_DATE), -- Changed for bug 2647027
2141 -- trunc(DISABLE_DATE),
2142 EFFECTIVITY_DATE,
2143 DISABLE_DATE,
2144 BACKFLUSH_FLAG,
2145 OPTION_DEPENDENT_FLAG,
2146 ATTRIBUTE_CATEGORY,
2147 ATTRIBUTE1,
2148 ATTRIBUTE2,
2149 ATTRIBUTE3,
2150 ATTRIBUTE4,
2151 ATTRIBUTE5,
2152 ATTRIBUTE6,
2153 ATTRIBUTE7,
2154 ATTRIBUTE8,
2155 ATTRIBUTE9,
2156 ATTRIBUTE10,
2157 ATTRIBUTE11,
2158 ATTRIBUTE12,
2159 ATTRIBUTE13,
2160 ATTRIBUTE14,
2161 ATTRIBUTE15,
2162 REQUEST_ID,
2163 PROGRAM_APPLICATION_ID,
2164 PROGRAM_ID,
2165 PROGRAM_UPDATE_DATE
2166 from BOM_OP_SEQUENCES_INTERFACE
2167 where process_flag = 4
2168 and rownum < 500;
2169
2170 EXIT when SQL%NOTFOUND;
2171
2172 update bom_op_sequences_interface bsi
2173 set process_flag = 7
2174 where process_flag = 4
2175 and exists (select NULL from bom_operation_sequences bos
2176 where bos.operation_sequence_id =
2177 bsi.operation_sequence_id);
2178 commit;
2179 end loop;
2180
2181
2182 /*
2183 ** Process operation resources interface table
2184 */
2185 stmt_num := 40;
2186 table_name := 'BOM_OPERATION_RESOURCES';
2187 loop
2188 insert into BOM_OPERATION_RESOURCES
2189 (
2190 OPERATION_SEQUENCE_ID,
2191 RESOURCE_SEQ_NUM,
2192 RESOURCE_ID,
2193 ACTIVITY_ID,
2194 STANDARD_RATE_FLAG,
2195 ASSIGNED_UNITS,
2196 USAGE_RATE_OR_AMOUNT,
2197 USAGE_RATE_OR_AMOUNT_INVERSE,
2198 BASIS_TYPE,
2199 SCHEDULE_FLAG,
2200 LAST_UPDATE_DATE,
2201 LAST_UPDATED_BY,
2202 CREATION_DATE,
2203 CREATED_BY,
2204 LAST_UPDATE_LOGIN,
2205 RESOURCE_OFFSET_PERCENT,
2206 AUTOCHARGE_TYPE,
2207 ATTRIBUTE_CATEGORY,
2208 ATTRIBUTE1,
2209 ATTRIBUTE2,
2210 ATTRIBUTE3,
2211 ATTRIBUTE4,
2212 ATTRIBUTE5,
2213 ATTRIBUTE6,
2214 ATTRIBUTE7,
2215 ATTRIBUTE8,
2216 ATTRIBUTE9,
2217 ATTRIBUTE10,
2218 ATTRIBUTE11,
2219 ATTRIBUTE12,
2220 ATTRIBUTE13,
2221 ATTRIBUTE14,
2222 ATTRIBUTE15,
2223 REQUEST_ID,
2224 PROGRAM_APPLICATION_ID,
2225 PROGRAM_ID,
2226 PROGRAM_UPDATE_DATE
2227 )
2228 select
2229 OPERATION_SEQUENCE_ID,
2230 RESOURCE_SEQ_NUM,
2231 RESOURCE_ID,
2232 ACTIVITY_ID,
2233 STANDARD_RATE_FLAG,
2234 ASSIGNED_UNITS,
2235 USAGE_RATE_OR_AMOUNT,
2236 USAGE_RATE_OR_AMOUNT_INVERSE,
2237 BASIS_TYPE,
2238 SCHEDULE_FLAG,
2239 LAST_UPDATE_DATE,
2240 LAST_UPDATED_BY,
2241 CREATION_DATE,
2242 CREATED_BY,
2243 LAST_UPDATE_LOGIN,
2244 RESOURCE_OFFSET_PERCENT,
2245 AUTOCHARGE_TYPE,
2246 ATTRIBUTE_CATEGORY,
2247 ATTRIBUTE1,
2248 ATTRIBUTE2,
2249 ATTRIBUTE3,
2250 ATTRIBUTE4,
2251 ATTRIBUTE5,
2252 ATTRIBUTE6,
2253 ATTRIBUTE7,
2254 ATTRIBUTE8,
2255 ATTRIBUTE9,
2256 ATTRIBUTE10,
2257 ATTRIBUTE11,
2258 ATTRIBUTE12,
2259 ATTRIBUTE13,
2260 ATTRIBUTE14,
2261 ATTRIBUTE15,
2262 REQUEST_ID,
2263 PROGRAM_APPLICATION_ID,
2264 PROGRAM_ID,
2265 PROGRAM_UPDATE_DATE
2266 from BOM_OP_RESOURCES_INTERFACE
2267 where process_flag = 4
2268 and rownum < 500;
2269
2270 EXIT when SQL%NOTFOUND;
2271
2272 update bom_op_resources_interface bri
2273 set process_flag = 7
2274 where process_flag = 4
2275 and exists (select NULL from bom_operation_resources bor
2276 where bor.operation_sequence_id =
2277 bri.operation_sequence_id
2278 and bor.resource_seq_num =
2279 bri.resource_seq_num);
2280 commit;
2281 end loop;
2282 /*
2283 ** process routing revision interface table
2284 */
2285 stmt_num := 50;
2286 table_name := 'MTL_RTG_ITEM_REVISIONS';
2287 loop
2288 insert into MTL_RTG_ITEM_REVISIONS(
2289 INVENTORY_ITEM_ID,
2290 ORGANIZATION_ID,
2291 PROCESS_REVISION,
2292 LAST_UPDATE_DATE,
2293 LAST_UPDATED_BY,
2294 CREATION_DATE,
2295 CREATED_BY,
2296 LAST_UPDATE_LOGIN,
2297 IMPLEMENTATION_DATE,
2298 EFFECTIVITY_DATE,
2299 ATTRIBUTE_CATEGORY,
2300 ATTRIBUTE1,
2301 ATTRIBUTE2,
2302 ATTRIBUTE3,
2303 ATTRIBUTE4,
2304 ATTRIBUTE5,
2305 ATTRIBUTE6,
2306 ATTRIBUTE7,
2307 ATTRIBUTE8,
2308 ATTRIBUTE9,
2309 ATTRIBUTE10,
2310 ATTRIBUTE11,
2311 ATTRIBUTE12,
2312 ATTRIBUTE13,
2313 ATTRIBUTE14,
2314 ATTRIBUTE15,
2315 REQUEST_ID,
2316 PROGRAM_APPLICATION_ID,
2317 PROGRAM_ID,
2318 PROGRAM_UPDATE_DATE
2319 )
2320 select
2321 INVENTORY_ITEM_ID,
2322 ORGANIZATION_ID,
2323 PROCESS_REVISION,
2324 LAST_UPDATE_DATE,
2325 LAST_UPDATED_BY,
2326 CREATION_DATE,
2327 CREATED_BY,
2328 LAST_UPDATE_LOGIN,
2329 trunc(IMPLEMENTATION_DATE),
2330 trunc(EFFECTIVITY_DATE),
2331 ATTRIBUTE_CATEGORY,
2332 ATTRIBUTE1,
2333 ATTRIBUTE2,
2334 ATTRIBUTE3,
2335 ATTRIBUTE4,
2336 ATTRIBUTE5,
2337 ATTRIBUTE6,
2338 ATTRIBUTE7,
2339 ATTRIBUTE8,
2340 ATTRIBUTE9,
2341 ATTRIBUTE10,
2342 ATTRIBUTE11,
2343 ATTRIBUTE12,
2344 ATTRIBUTE13,
2345 ATTRIBUTE14,
2346 ATTRIBUTE15,
2347 REQUEST_ID,
2348 PROGRAM_APPLICATION_ID,
2349 PROGRAM_ID,
2350 PROGRAM_UPDATE_DATE
2351 from MTL_RTG_ITEM_REVS_INTERFACE
2352 where process_flag = 4
2353 and rownum < 500;
2354
2355 EXIT when SQL%NOTFOUND;
2356
2357 update mtl_rtg_item_revs_interface mri
2358 set process_flag = 7
2359 where process_flag = 4
2360 and exists (select NULL from mtl_rtg_item_revisions mrr
2361 where mrr.inventory_item_id = mri.inventory_item_id
2362 and mrr.organization_id = mri.organization_id
2363 and mrr.process_revision = mri.process_revision);
2364 commit;
2365 end loop;
2366 return(1);
2367
2368 EXCEPTION
2369 WHEN NO_DATA_FOUND THEN
2370 return(1);
2371 WHEN OTHERS THEN
2372 rollback;
2373 error_message := 'BOMPPRCB:'||to_char(stmt_num)||':'|| substrb(sqlerrm,1,150);
2374 message_name := 'BOM_RTG_PROCESS_ERROR';
2375 return(SQLCODE);
2376
2377 END bmprrtg_process_rtg_data;
2378
2379
2380 END BOMPPRCB;