[Home] [Help]
PACKAGE BODY: APPS.BOMPASGR
Source
1 package body BOMPASGR as
2 /* $Header: BOMASGRB.pls 115.7 2002/10/31 07:19:39 djebar ship $ */
3 /*==========================================================================+
4 | Copyright (c) 1993 Oracle Corporation Belmont, California, USA |
5 | All rights reserved. |
6 +===========================================================================+
7 | |
8 | File Name : BOMASGRB.pls |
9 | DESCRIPTION : This package contains functions used to assign routing |
10 | data in the interface tables |
11 | Parameters: org_id organization_id |
12 | all_org process all orgs or just current org |
13 | 1 - all orgs |
14 | 2 - only org_id |
15 | prog_appid program application_id |
16 | prog_id program id |
17 | req_id request_id |
18 | user_id user id |
19 | login_id login id |
20 | History: |
21 | 09/28/93 Shreyas Shah creation date |
22 | 11/05/93 Shreyas Shah added all_org option |
23 | 05/03/94 Julie Maeyama Update logic |
24 | |
25 +===========================================================================+
26 -------------------------- bmartorg_assign_rtg_orgid ------------------------
27 NAME
28 bmartorg_assign_rtg_orgid - assign organization_id to all routing tables
29 DESCRIPTION
30 assign org id to all routing and its child tables
31
32 REQUIRES
33 err_text out buffer to return error message
34 MODIFIES
35 BOM_OP_ROUTINGS_INTERFACE
36 BOM_OP_SEQUENCES_INTERFACE
37 BOM_OP_RESOURCES_INTERFACE
38 MTL_RTG_ITEM_REVS_INTERFACE
39 MTL_INTERFACE_ERRORS
40 RETURNS
41 0 if successful
42 SQLCODE if unsuccessful
43 NOTES
44 ----------------------------------------------------------------------------*/
45 FUNCTION bmartorg_assign_rtg_orgid (
46 err_text IN OUT VARCHAR2
47 )
48 return INTEGER
49
50 IS
51 stmt_num NUMBER;
52 BEGIN
53 stmt_num := 1;
54 loop
55 update bom_op_routings_interface ori
56 set organization_id = (select organization_id from
57 mtl_parameters a
58 where a.organization_code = ori.organization_code)
59 where process_flag = 1
60 and organization_id is null
61 and organization_code is not null
62 and exists (select organization_code
63 from mtl_parameters b
64 where b.organization_code = ori.organization_code)
65 and rownum < 2000;
66 EXIT when SQL%NOTFOUND;
67 commit;
68
69 end loop;
70
71 stmt_num := 2;
72 loop
73 update bom_op_sequences_interface ori
74 set organization_id = (select organization_id from
75 mtl_parameters a
76 where a.organization_code = ori.organization_code)
77 where process_flag = 1
78 and organization_id is null
79 and organization_code is not null
80 and exists (select organization_code
81 from mtl_parameters b
82 where b.organization_code = ori.organization_code)
83 and rownum < 2000;
84 EXIT when SQL%NOTFOUND;
85 commit;
86
87 end loop;
88
89 stmt_num := 3;
90 loop
91 update bom_op_resources_interface ori
92 set organization_id = (select organization_id from
93 mtl_parameters a
94 where a.organization_code = ori.organization_code)
95 where process_flag = 1
96 and organization_id is null
97 and organization_code is not null
98 and exists (select organization_code
99 from mtl_parameters b
100 where b.organization_code = ori.organization_code)
101 and rownum < 2000;
102 EXIT when SQL%NOTFOUND;
103 commit;
104
105 end loop;
106
107 stmt_num := 4;
108 loop
109 update mtl_rtg_item_revs_interface ori
110 set organization_id = (select organization_id from
111 mtl_parameters a
112 where a.organization_code = ori.organization_code)
113 where process_flag = 1
114 and organization_id is null
115 and organization_code is not null
116 and exists (select organization_code
117 from mtl_parameters b
118 where b.organization_code = ori.organization_code)
119 and rownum < 2000;
120 EXIT when SQL%NOTFOUND;
121 commit;
122
123 end loop;
124
125 return(0);
126 exception
127 when others then
128 err_text := 'BOMPASGR(bmartorg-'||stmt_num||') '|| substrb(SQLERRM,1,60);
129 return(SQLCODE);
130 end bmartorg_assign_rtg_orgid;
131
132 /*------------------------ bmasrrev_assign_rtg_revision -------------------*/
133 /* NAME
134 bmasrrev_assign_rtg_revision - assign routing revision
135 DESCRIPTION
136 assign defaults and various ids in the interface table
137 MTL_RTG_ITEM_REVS_INTERFACE. If any application error occurs, it
138 inserts record into MTL_INTERFACE_ERRORS.
139
140 REQUIRES
141 err_text out buffer to return error message
142 MODIFIES
143 MTL_RTG_ITEM_REVS_INTERFACE
144 MTL_INTERFACE_ERRORS
145 RETURNS
146 0 if successful
147 SQLCODE if unsuccessful
148 NOTES
149 -----------------------------------------------------------------------------*/
150 FUNCTION bmasrrev_assign_rtg_revision (
151 org_id NUMBER,
152 all_org NUMBER,
153 user_id NUMBER,
154 login_id NUMBER,
155 prog_appid NUMBER,
156 prog_id NUMBER,
157 req_id NUMBER,
158 err_text IN OUT VARCHAR2
159 )
160 return INTEGER
161
162 IS
163 stmt_num NUMBER := 0;
164 ret_code NUMBER;
165 commit_cnt NUMBER;
166 continue_loop BOOLEAN := TRUE;
167 CURSOR c1 is
168 select organization_code OC, organization_id OI,
169 process_revision PR,
170 inventory_item_id III, inventory_item_number IIN,
171 transaction_id TI, change_notice CN, ecn_initiation_date CID,
172 implementation_date ID, effectivity_date ED
173 from mtl_rtg_item_revs_interface
174 where process_flag = 1
175 and (all_org = 1
176 or
177 (all_org = 2 and organization_id = org_id)
178 )
179 and rownum < 500;
180
181 BEGIN
182 /*
183 ** assign transaction ids to all rows first
184 */
185 loop
186 update mtl_rtg_item_revs_interface
187 set transaction_id = mtl_system_items_interface_s.nextval
188 where transaction_id is null
189 and process_flag = 1
190 and rownum < 500;
191 EXIT when SQL%NOTFOUND;
192 commit;
193
194 end loop;
195
196 while continue_loop loop
197 commit_cnt := 0;
198 for c1rec in c1 loop
199 commit_cnt := commit_cnt + 1;
200 stmt_num := 1;
201 if (c1rec.OI is null) then
202 ret_code := INVPUOPI.mtl_log_interface_err(
203 org_id => NULL,
204 user_id => user_id,
205 login_id => login_id,
206 prog_appid => prog_appid,
207 prog_id => prog_id,
208 req_id => req_id,
209 trans_id => c1rec.TI,
210 error_text => err_text,
211 tbl_name => 'MTL_RTG_ITEM_REVS_INTERFACE',
212 msg_name => 'BOM_ORG_ID_MISSING',
213 err_text => err_text);
214 update mtl_rtg_item_revs_interface set
215 process_flag = 3
216 where transaction_id = c1rec.TI;
217
218 goto continue_loop;
219 end if;
220
221 stmt_num := 2;
222 if (c1rec.III is null) then
223 ret_code := INVPUOPI.mtl_pr_parse_flex_name(
224 org_id => c1rec.OI,
225 flex_code => 'MSTK',
226 flex_name => c1rec.IIN,
227 flex_id => c1rec.III,
228 set_id => -1,
229 err_text => err_text);
230 if (ret_code <> 0) then
231 ret_code := INVPUOPI.mtl_log_interface_err(
232 org_id => c1rec.OI,
233 user_id => user_id,
234 login_id => login_id,
235 prog_appid => prog_appid,
236 prog_id => prog_id,
237 req_id => req_id,
238 trans_id => c1rec.TI,
239 error_text => err_text,
240 tbl_name => 'MTL_RTG_ITEM_REVS_INTERFACE',
241 msg_name => 'BOM_INV_ITEM_ID_MISSING',
242 err_text => err_text);
243 update mtl_rtg_item_revs_interface set
244 process_flag = 3
245 where transaction_id = c1rec.TI;
246
247 if (ret_code <> 0) then
248 return(ret_code);
249 end if;
250 goto continue_loop;
251 end if;
252 end if;
253
254 stmt_num := 3;
255
256 update mtl_rtg_item_revs_interface set
257 organization_id = nvl(organization_id, c1rec.OI),
258 inventory_item_id = nvl(inventory_item_id, c1rec.III),
259 process_revision = UPPER(c1rec.PR),
260 process_flag = 2,
261 last_update_date = nvl(last_update_date, sysdate),
262 last_updated_by = nvl(last_updated_by, user_id),
263 creation_date = nvl(creation_date, sysdate),
264 created_by = nvl(created_by, user_id),
265 last_update_login = nvl(last_update_login, user_id),
266 request_id = nvl(request_id, req_id),
267 program_application_id = nvl(program_application_id, prog_appid),
268 program_id = nvl(program_id, prog_id),
269 program_update_date = nvl(program_update_date, sysdate),
270 effectivity_date = nvl(effectivity_date, sysdate),
271 IMPLEMENTATION_DATE = nvl(effectivity_date, sysdate)
272 where transaction_id = c1rec.TI;
273
274 if (SQL%NOTFOUND) then
275 err_text := 'BOMPASGR(' || stmt_num || ')' || substrb(SQLERRM,1,60);
276 return(SQLCODE);
277 end if;
278
279 <<continue_loop>>
280 NULL;
281 end loop;
282
283 commit;
284
285 if (commit_cnt < (500 - 1)) then
286 continue_loop := FALSE;
287 end if;
288
289 end loop;
290
291 return(0);
292 EXCEPTION
293 when others then
294 err_text := 'BOMPASGR(bmasrrev-'|| stmt_num ||') ' || substrb(SQLERRM,1,60);
295 return(SQLCODE);
296
297 END bmasrrev_assign_rtg_revision;
298
299 /*------------------------ bmprtgh_assign_rtg_header ------------------------*/
300 /* NAME
301 bmprtgh_assign_rtg_header - assign routing data
302 DESCRIPTION
303 assign defaults and various ids in the interface table,
304 BOM_OP_ROUTINGS_INTERFACE. If any application error occurs, it
305 inserts record into MTL_INTERFACE_ERRORS.
306
307 REQUIRES
308 err_text out buffer to return error message
309 MODIFIES
310 BOM_OP_ROUTINGS_INTERFACE
311 MTL_INTERFACE_ERRORS
312 RETURNS
313 0 if successful
314 SQLCODE if unsuccessful
315 NOTES
316 -----------------------------------------------------------------------------*/
317 FUNCTION bmprtgh_assign_rtg_header (
318 org_id NUMBER,
319 all_org NUMBER,
320 user_id NUMBER,
321 login_id NUMBER,
322 prog_appid NUMBER,
323 prog_id NUMBER,
324 req_id NUMBER,
325 err_text IN OUT VARCHAR2
326 )
327 return INTEGER
328
329 IS
330
331 stmt_num NUMBER;
332 org_code VARCHAR2(3);
333 assy_id NUMBER;
334 proc_flag NUMBER;
335 ret_code NUMBER;
336 revs NUMBER := 0;
337 revs_int NUMBER := 0;
338 revs_prod NUMBER := 0;
339 dummy_alt VARCHAR2(10);
340 commit_cnt NUMBER;
341 continue_loop BOOLEAN := TRUE;
342
343 cursor c1 is select
344 organization_id OI, organization_code OC,
345 assembly_item_id AII, assembly_item_number AIN,
346 completion_locator_id CLI, location_name LN,
347 common_assembly_item_id CAII, common_item_number CAIN,
348 common_routing_sequence_id CRSI,
349 alternate_routing_designator ARD, transaction_id TI,
350 routing_sequence_id RSI, process_revision PR,
351 creation_date CD, created_by CB, last_update_login LUL,
352 last_update_date LUD, last_updated_by LUB
353 from bom_op_routings_interface
354 where process_flag = 1
355 and (all_org = 1
356 or
357 (all_org = 2 and organization_id = org_id)
358 )
359 and rownum < 500;
360
361 cursor c2 is select
362 transaction_id TI, common_routing_sequence_id CRSI,
363 assembly_item_id AII, routing_sequence_id RSI,
364 common_assembly_item_id CAID, organization_id OI,
365 alternate_routing_designator ARD
366 from bom_op_routings_interface
367 where process_flag = 99
368 and (all_org = 1
369 or
370 (all_org = 2 and organization_id = org_id)
371 )
372 and rownum < 500;
373
374 BEGIN
375 /*
376 ** assign transaction ids for every row first
377 */
378 stmt_num := 1;
379 loop
380 update bom_op_routings_interface ori
381 set transaction_id = mtl_system_items_interface_s.nextval,
382 routing_sequence_id = nvl(routing_sequence_id,
383 bom_operational_routings_s.nextval)
384 where transaction_id is null
385 and process_flag = 1
386 and rownum < 500;
387 EXIT when SQL%NOTFOUND;
388 commit;
389
390 end loop;
391
392 while continue_loop loop
393 commit_cnt := 0;
394 for c1rec in c1 loop
395 commit_cnt := commit_cnt + 1;
396 stmt_num := 2;
397 if (c1rec.OI is null) then
398 ret_code := INVPUOPI.mtl_log_interface_err(
399 org_id => NULL,
400 user_id => user_id,
401 login_id => login_id,
402 prog_appid => prog_appid,
403 prog_id => prog_id,
404 req_id => req_id,
405 trans_id => c1rec.TI,
406 error_text => err_text,
407 tbl_name => 'BOM_OP_ROUTINGS_INTERFACE',
408 msg_name => 'BOM_ORG_ID_MISSING',
409 err_text => err_text);
410 update bom_op_routings_interface set
411 process_flag = 3
412 where transaction_id = c1rec.TI;
413
414 goto continue_loop;
415 end if;
416
417 /*
418 ** set assembly item ids
419 */
420 stmt_num := 3;
421 if (c1rec.AII is null) then
422 ret_code := INVPUOPI.mtl_pr_parse_flex_name(
423 org_id => c1rec.OI,
424 flex_code => 'MSTK',
425 flex_name => c1rec.AIN,
426 flex_id => c1rec.AII,
427 set_id => -1,
428 err_text => err_text);
429 if (ret_code <> 0) then
430 ret_code := INVPUOPI.mtl_log_interface_err(
431 org_id => NULL,
432 user_id => user_id,
433 login_id => login_id,
434 prog_appid => prog_appid,
435 prog_id => prog_id,
436 req_id => req_id,
437 trans_id => c1rec.TI,
438 error_text => err_text,
439 tbl_name => 'BOM_OP_ROUTINGS_INTERFACE',
440 msg_name => 'BOM_ASSY_ITEM_MISSING',
441 err_text => err_text);
442 update bom_op_routings_interface set
443 process_flag = 3
444 where transaction_id = c1rec.TI;
445
446 if (ret_code <> 0) then
447 return(ret_code);
448 end if;
449 goto continue_loop;
450 end if;
451 end if;
452
453 /*
454 ** set locator id
455 */
456 stmt_num := 4;
457 if (c1rec.CLI is null and c1rec.LN is not null) then
458 ret_code := INVPUOPI.mtl_pr_parse_flex_name(
459 org_id => c1rec.OI,
460 flex_code => 'MTLL',
461 flex_name => c1rec.LN,
462 flex_id => c1rec.CLI,
463 set_id => -1,
464 err_text => err_text);
465 if (ret_code <> 0) then
466 ret_code := INVPUOPI.mtl_log_interface_err(
467 org_id => NULL,
468 user_id => user_id,
469 login_id => login_id,
470 prog_appid => prog_appid,
471 prog_id => prog_id,
472 req_id => req_id,
473 trans_id => c1rec.TI,
474 error_text => err_text,
475 tbl_name => 'BOM_OP_ROUTINGS_INTERFACE',
476 msg_name => 'BOM_LOCATION_NAME_INVALID',
477 err_text => err_text);
478 update bom_op_routings_interface set
479 process_flag = 3
480 where transaction_id = c1rec.TI;
481
482 if (ret_code <> 0) then
483 return(ret_code);
484 end if;
485 goto continue_loop;
486 end if;
487 end if;
488
489 /*
490 ** set common assembly item ids
491 */
492 stmt_num := 5;
493 if (c1rec.CAII is null and c1rec.CAIN is not null and
494 c1rec.CRSI is null) then
495 ret_code := INVPUOPI.mtl_pr_parse_flex_name(
496 org_id => c1rec.OI,
497 flex_code => 'MSTK',
498 flex_name => c1rec.CAIN,
499 flex_id => c1rec.CAII,
500 set_id => -1,
501 err_text => err_text);
502 if (ret_code <> 0) then
503 ret_code := INVPUOPI.mtl_log_interface_err(
504 org_id => NULL,
505 user_id => user_id,
506 login_id => login_id,
507 prog_appid => prog_appid,
508 prog_id => prog_id,
509 req_id => req_id,
510 trans_id => c1rec.TI,
511 error_text => err_text,
512 tbl_name => 'BOM_OP_ROUTINGS_INTERFACE',
513 msg_name => 'BOM_CMN_ASSY_ITEM_INVALID',
514 err_text => err_text);
515 update bom_op_routings_interface set
516 process_flag = 3
517 where transaction_id = c1rec.TI;
518
519 if (ret_code <> 0) then
520 return(ret_code);
521 end if;
522 goto continue_loop;
523 end if;
524 end if;
525 /*
526 ** insert given process_revision
527 */
528 if (c1rec.PR is not null) then
529 insert into mtl_rtg_item_revs_interface
530 (organization_id,
531 inventory_item_id,
532 process_revision,
533 process_flag,
534 last_update_date,
535 last_updated_by,
536 creation_date,
537 created_by,
538 last_update_login,
539 request_id,
540 program_application_id,
541 program_id,
542 program_update_date,
543 effectivity_date,
544 IMPLEMENTATION_DATE,
545 transaction_id)
546 values (c1rec.OI,
547 c1rec.AII,
548 UPPER(c1rec.PR),
549 2,
550 nvl(c1rec.LUD,sysdate),
551 nvl(c1rec.LUB,user_id),
552 nvl(c1rec.CD,sysdate),
553 nvl(c1rec.CB,user_id),
554 nvl(c1rec.LUL, user_id),
555 req_id,
556 prog_appid,
557 prog_id,
558 sysdate,
559 sysdate,
560 sysdate,
561 mtl_system_items_interface_s.nextval);
562 goto update_rtg;
563 else
564 select starting_revision into c1rec.PR
565 from mtl_parameters
566 where organization_id = c1rec.OI;
567 end if;
568
569 /*
570 ** check to see if a record exists in the revs interface table for this
571 ** item/org combination
572 */
573 select count(process_revision) into revs_int
574 from mtl_rtg_item_revs_interface
575 where organization_id = c1rec.OI
576 and inventory_item_id = c1rec.AII
577 and process_flag <> 3 and process_flag <> 7;
578 /*
579 ** check to see if a record exists in the revs production table for this
580 ** item/org combination
581 */
582 select count(process_revision) into revs_prod
583 from mtl_rtg_item_revisions
584 where organization_id = c1rec.OI
585 and inventory_item_id = c1rec.AII;
586
587 revs := revs_int + revs_prod;
588 /*
589 ** insert a record into the revs interface table because one does not exist
590 */
591 if (revs = 0) then
592 insert into mtl_rtg_item_revs_interface
593 (organization_id,
594 inventory_item_id,
595 process_revision,
596 process_flag,
597 last_update_date,
598 last_updated_by,
599 creation_date,
600 created_by,
601 last_update_login,
602 request_id,
603 program_application_id,
604 program_id,
605 program_update_date,
606 effectivity_date,
607 IMPLEMENTATION_DATE,
608 transaction_id)
609 values (c1rec.OI,
610 c1rec.AII,
611 c1rec.PR,
612 2,
613 nvl(c1rec.LUD, sysdate),
614 nvl(c1rec.LUB, user_id),
615 nvl(c1rec.CD, sysdate),
616 nvl(c1rec.CB, user_id),
617 nvl(c1rec.LUL, user_id),
618 req_id,
619 prog_appid,
620 prog_id,
621 sysdate,
622 sysdate,
623 sysdate,
624 mtl_system_items_interface_s.nextval);
625 end if;
626
627 /*
628 ** update bom_op_routings_interface with the modified column values
629 */
630 <<update_rtg>>
631 stmt_num := 6;
632 update bom_op_routings_interface
633 set organization_id = nvl(organization_id, c1rec.OI),
634 assembly_item_id = nvl(assembly_item_id, c1rec.AII),
635 completion_locator_id = nvl(completion_locator_id, c1rec.CLI),
636 common_assembly_item_id = nvl(common_assembly_item_id, c1rec.CAII),
637 routing_type = nvl(routing_type, 1),
638 last_update_date = nvl(last_update_date, sysdate),
639 last_updated_by = nvl(last_updated_by, user_id),
640 creation_date = nvl(creation_date, sysdate),
641 created_by = nvl(created_by, user_id),
642 last_update_login = nvl(last_update_login, user_id),
643 request_id = nvl(request_id, req_id),
644 program_application_id = nvl(program_application_id, prog_appid),
645 program_id = nvl(program_id, prog_id),
646 program_update_date = nvl(program_update_date, sysdate),
647 process_flag = 99
648 where transaction_id = c1rec.TI;
649
650 if (SQL%NOTFOUND) then
651 err_text := 'BOMPASGR(bmprtgh-' || stmt_num || ')' || substrb(SQLERRM,1,60);
652 return(SQLCODE);
653 end if;
654
655 /*
656 ** assign assembly_item_id to all child recs
657 */
658 stmt_num := 7;
659 ret_code := bmasritm_assign_rtg_item_id(
660 org_id => c1rec.OI,
661 item_num => c1rec.AIN,
662 item_id => c1rec.AII,
663 err_text => err_text);
664 if (ret_code <> 0) then
665 return(ret_code);
666 end if;
667 /*
668 ** assign routing sequence id to all child tables
669 */
670 stmt_num := 8;
671 ret_code := bmasrtgid_assign_rtg_seq_id(
672 org_id => c1rec.OI,
673 assy_id => c1rec.AII,
674 alt_desg => c1rec.ARD,
675 rtg_id => c1rec.RSI,
676 err_text => err_text);
677 if (ret_code <> 0) then
678 return(ret_code);
679 end if;
680
681 <<continue_loop>>
682 NULL;
683 end loop;
684
685 commit;
686
687 if (commit_cnt < (500 - 1)) then
688 continue_loop := FALSE;
689 end if;
690
691 end loop;
692
693 continue_loop := TRUE;
694 while continue_loop loop
695 commit_cnt := 0;
696 for c2rec in c2 loop
697 commit_cnt := commit_cnt + 1;
698 stmt_num := 9;
699 proc_flag := 2;
700 if (c2rec.CRSI is null) then
701 if (c2rec.CAID is null) then
702 c2rec.CRSI := c2rec.RSI;
703 else
704 assy_id := c2rec.CAID;
705 ret_code := bmgrtsq_get_routing_sequence(
706 org_id => c2rec.OI,
707 item_id => assy_id,
708 alt_desg => c2rec.ARD,
709 routing_seq_id => c2rec.CRSI,
710 err_text => err_text);
711 if (ret_code <> 0) then
712 ret_code := INVPUOPI.mtl_log_interface_err(
713 org_id => NULL,
714 user_id => user_id,
715 login_id => login_id,
716 prog_appid => prog_appid,
717 prog_id => prog_id,
718 req_id => req_id,
719 trans_id => c2rec.TI,
720 error_text => err_text,
721 tbl_name => 'BOM_OP_ROUTINGS_INTERFACE',
722 msg_name => 'BOM_CMN_RTG_SEQ_INVALID',
723 err_text => err_text);
724 proc_flag := 3;
725 end if;
726 end if;
727 else
728 ret_code := bmgrtin_get_rtg_info(
729 org_id => c2rec.OI,
730 item_id => assy_id,
731 alt_desg => dummy_alt,
732 rtg_seq_id => c2rec.CRSI,
733 err_text => err_text);
734 if (ret_code <> 0) then
735 ret_code := INVPUOPI.mtl_log_interface_err(
736 org_id => c2rec.OI,
737 user_id => user_id,
738 login_id => login_id,
739 prog_appid => prog_appid,
740 prog_id => prog_id,
741 req_id => req_id,
742 trans_id => c2rec.TI,
743 error_text => err_text,
744 tbl_name => 'BOM_OP_ROUTINGS_INTERFACE',
745 msg_name => 'BOM_CMN_RTG_SEQ_INVALID',
746 err_text => err_text);
747 proc_flag := 3;
748 end if;
749 end if;
750 if (c2rec.CRSI = c2rec.RSI) then
751 assy_id := NULL;
752 end if;
753
754 stmt_num := 8;
755 update bom_op_routings_interface set
756 process_flag = proc_flag,
757 common_routing_sequence_id = c2rec.CRSI,
758 common_assembly_item_id = assy_id
759 where transaction_id = c2rec.TI;
760
761 if (ret_code <> 0) then
762 return(ret_code);
763 end if;
764
765 end loop;
766
767 commit;
768
769 if (commit_cnt < (500 - 1)) then
770 continue_loop := FALSE;
771 end if;
772
773 end loop;
774
775 return(0);
776 EXCEPTION
777 when others then
778 err_text := 'BOMPASGR(bmprtgh-' || stmt_num || ') ' || substrb(SQLERRM,1,60);
779 return(SQLCODE);
780 END bmprtgh_assign_rtg_header;
781
782 /*------------------------ bmasopd_assign_operation_data --------------------*/
783 /* NAME
784 bmasopd_assign_operation_data - assign operation data
785 DESCRIPTION
786 assign defaults and various ids in the interface table
787 BOM_OP_SEQUENCES_INTERFACE. If any application error occurs, it
788 inserts record into MTL_INTERFACE_ERRORS.
789
790 REQUIRES
791 err_text out buffer to return error message
792 MODIFIES
793 BOM_OP_SEQUENCES_INTERFACE
794 MTL_INTERFACE_ERRORS
795 RETURNS
796 0 if successful
797 SQLCODE if unsuccessful
798 NOTES
799 -----------------------------------------------------------------------------*/
800 FUNCTION bmasopd_assign_operation_data (
801 org_id NUMBER,
802 all_org NUMBER,
803 user_id NUMBER,
804 login_id NUMBER,
805 prog_appid NUMBER,
806 prog_id NUMBER,
807 req_id NUMBER,
808 err_text IN OUT VARCHAR2
809 )
810 return INTEGER
811
812 IS
813 stmt_num NUMBER := 0;
814 ret_code NUMBER;
815 std_dept_id NUMBER;
816 std_min_qty NUMBER;
817 std_cnt_pt NUMBER;
818 std_desc VARCHAR2(240);
819 std_bkflsh NUMBER;
820 std_opt NUMBER;
821 std_attcat VARCHAR2(30);
822 std_att1 VARCHAR2(150);
823 std_att2 VARCHAR2(150);
824 std_att3 VARCHAR2(150);
825 std_att4 VARCHAR2(150);
826 std_att5 VARCHAR2(150);
827 std_att6 VARCHAR2(150);
828 std_att7 VARCHAR2(150);
829 std_att8 VARCHAR2(150);
830 std_att9 VARCHAR2(150);
831 std_att10 VARCHAR2(150);
832 std_att11 VARCHAR2(150);
833 std_att12 VARCHAR2(150);
834 std_att13 VARCHAR2(150);
835 std_att14 VARCHAR2(150);
836 std_att15 VARCHAR2(150);
837 continue_loop BOOLEAN := TRUE;
838
839 CURSOR c1 is
840 select organization_code OC, organization_id OI,
841 operation_sequence_id OSI,
842 assembly_item_id AII, assembly_item_number AIN,
843 alternate_routing_designator ARD, routing_sequence_id RSI,
844 department_id DI, department_code DC,
845 operation_code SOC, standard_operation_id SOI,
846 transaction_id TI, operation_seq_num OSN,
847 to_char(effectivity_date,'YYYY/MM/DD HH24:MI:SS') ED, -- Changed for bug 2647027
848 -- to_char(effectivity_date,'YYYY/MM/DD HH24:MI') ED,
849 minimum_transfer_quantity MTQ, count_point_type CPT,
850 operation_description OD, backflush_flag BF,
851 option_dependent_flag ODF, attribute_category AC,
852 attribute1 A1, attribute1 A2, attribute1 A3,
853 attribute1 A4,attribute1 A5,attribute1 A6,attribute1 A7,
854 attribute1 A8,attribute1 A9,attribute1 A10,attribute1 A11,
855 attribute1 A12,attribute1 A13,attribute1 A14,attribute1 A15
856 from bom_op_sequences_interface
857 where process_flag = 1
858 and (all_org = 1
859 or
860 (all_org = 2 and organization_id = org_id)
861 )
862 and rownum < 500;
863
864 curr_org_code VARCHAR2(3);
865 commit_cnt NUMBER;
866
867 BEGIN
868 /*
869 ** assign transaction ids to all rows first
870 */
871 loop
872 update bom_op_sequences_interface
873 set transaction_id = mtl_system_items_interface_s.nextval,
874 operation_sequence_id = nvl(operation_sequence_id,
875 bom_operation_sequences_s.nextval)
876 where transaction_id is null
877 and process_flag = 1
878 and rownum < 500;
879 EXIT when SQL%NOTFOUND;
880 commit;
881
882 end loop;
883
884 while continue_loop loop
885 commit_cnt := 0;
886 for c1rec in c1 loop
887 commit_cnt := commit_cnt + 1;
888 stmt_num := 1;
889 if (c1rec.OI is null and (c1rec.RSI is null or
890 c1rec.DI is null or (c1rec.SOI is null and
891 c1rec.SOC is not null))) then
892 ret_code := INVPUOPI.mtl_log_interface_err(
893 org_id => NULL,
894 user_id => user_id,
895 login_id => login_id,
896 prog_appid => prog_appid,
897 prog_id => prog_id,
898 req_id => req_id,
899 trans_id => c1rec.TI,
900 error_text => err_text,
901 tbl_name => 'BOM_OP_SEQUENCES_INTERFACE',
902 msg_name => 'BOM_ORG_ID_MISSING',
903 err_text => err_text);
904 update bom_op_sequences_interface set
905 process_flag = 3
906 where transaction_id = c1rec.TI;
907
908 goto continue_loop;
909 end if;
910
911 stmt_num := 1.5;
912 if (c1rec.ED is null) then
913 ret_code := INVPUOPI.mtl_log_interface_err(
914 org_id => NULL,
915 user_id => user_id,
916 login_id => login_id,
917 prog_appid => prog_appid,
918 prog_id => prog_id,
919 req_id => req_id,
920 trans_id => c1rec.TI,
921 error_text => err_text,
922 tbl_name => 'BOM_OP_SEQUENCES_INTERFACE',
923 msg_name => 'BOM_EFF_DATE_MISSING',
924 err_text => err_text);
925 update bom_op_sequences_interface set
926 process_flag = 3
927 where transaction_id = c1rec.TI;
928
929 goto continue_loop;
930 end if;
931
932
933 stmt_num := 2;
934 if (c1rec.AII is null and c1rec.RSI is null) then
935 ret_code := INVPUOPI.mtl_pr_parse_flex_name(
936 org_id => c1rec.OI,
937 flex_code => 'MSTK',
938 flex_name => c1rec.AIN,
939 flex_id => c1rec.AII,
940 set_id => -1,
941 err_text => err_text);
942 if (ret_code <> 0) then
943 ret_code := INVPUOPI.mtl_log_interface_err(
944 org_id => c1rec.OI,
945 user_id => user_id,
946 login_id => login_id,
947 prog_appid => prog_appid,
948 prog_id => prog_id,
949 req_id => req_id,
950 trans_id => c1rec.TI,
951 error_text => err_text,
952 tbl_name => 'BOM_OP_SEQUENCES_INTERFACE',
953 msg_name => 'BOM_ASSY_ITEM_MISSING',
954 err_text => err_text);
955 update bom_op_sequences_interface set
956 process_flag = 3
957 where transaction_id = c1rec.TI;
958
959 if (ret_code <> 0) then
960 return(ret_code);
961 end if;
962 goto continue_loop;
963 end if;
964 end if;
965
966 stmt_num := 3;
967 if (c1rec.RSI is null) then
968 ret_code := bmgrtsq_get_routing_sequence(
969 org_id => c1rec.OI,
970 item_id => c1rec.AII,
971 alt_desg => c1rec.ARD,
972 routing_seq_id => c1rec.RSI,
973 err_text => err_text);
974 if (ret_code <> 0) then
975 ret_code := INVPUOPI.mtl_log_interface_err(
976 org_id => c1rec.OI,
977 user_id => user_id,
978 login_id => login_id,
979 prog_appid => prog_appid,
980 prog_id => prog_id,
981 req_id => req_id,
982 trans_id => c1rec.TI,
983 error_text => err_text,
984 tbl_name => 'BOM_OP_SEQUENCES_INTERFACE',
985 msg_name => 'BOM_RTG_SEQ_INVALID',
986 err_text => err_text);
987 update bom_op_sequences_interface set
988 process_flag = 3
989 where transaction_id = c1rec.TI;
990
991 if (ret_code <> 0) then
992 return(ret_code);
993 end if;
994 goto continue_loop;
995 end if;
996 else /* Needed for verify */
997 ret_code := bmgrtin_get_rtg_info(
998 org_id => c1rec.OI,
999 item_id => c1rec.AII,
1000 alt_desg => c1rec.ARD,
1001 rtg_seq_id => c1rec.RSI,
1002 err_text => err_text);
1003 if (ret_code <> 0) then
1004 ret_code := INVPUOPI.mtl_log_interface_err(
1005 org_id => NULL,
1006 user_id => user_id,
1007 login_id => login_id,
1008 prog_appid => prog_appid,
1009 prog_id => prog_id,
1010 req_id => req_id,
1011 trans_id => c1rec.TI,
1012 error_text => err_text,
1013 tbl_name => 'BOM_OP_SEQUENCES_INTERFACE',
1014 msg_name => 'BOM_RTG_SEQ_INVALID',
1015 err_text => err_text);
1016 update bom_op_sequences_interface set
1017 process_flag = 3
1018 where transaction_id = c1rec.TI;
1019
1020 if (ret_code <> 0) then
1021 return(ret_code);
1022 end if;
1023 goto continue_loop;
1024 end if;
1025 end if;
1026
1027 stmt_num := 4;
1028 if (c1rec.SOI is null and c1rec.SOC is not null) then
1029 ret_code := bmgtstdop_get_stdop(
1030 org_id => c1rec.OI,
1031 stdop_code => c1rec.SOC,
1032 stdop_id => c1rec.SOI,
1033 err_text => err_text);
1034 if (ret_code <> 0) then
1035 ret_code := INVPUOPI.mtl_log_interface_err(
1036 org_id => c1rec.OI,
1037 user_id => user_id,
1038 login_id => login_id,
1039 prog_appid => prog_appid,
1040 prog_id => prog_id,
1041 req_id => req_id,
1042 trans_id => c1rec.TI,
1043 error_text => err_text,
1044 tbl_name => 'BOM_OP_SEQUENCES_INTERFACE',
1045 msg_name => 'BOM_STD_OP_CODE_INVALID',
1046 err_text => err_text);
1047 update bom_op_sequences_interface set
1048 process_flag = 3
1049 where transaction_id = c1rec.TI;
1050
1051 if (ret_code <> 0) then
1052 return(ret_code);
1053 end if;
1054 goto continue_loop;
1055 end if;
1056 end if;
1057
1058 stmt_num := 5;
1059 if (c1rec.DI is null and c1rec.SOI is null) then
1060 ret_code := bmgtdep_get_department(
1061 org_id => c1rec.OI,
1062 dept_code => c1rec.DC,
1063 dept_id => c1rec.DI,
1064 err_text => err_text);
1065 if (ret_code <> 0) then
1066 ret_code := INVPUOPI.mtl_log_interface_err(
1067 org_id => c1rec.OI,
1068 user_id => user_id,
1069 login_id => login_id,
1070 prog_appid => prog_appid,
1071 prog_id => prog_id,
1072 req_id => req_id,
1073 trans_id => c1rec.TI,
1074 error_text => err_text,
1075 tbl_name => 'BOM_OP_SEQUENCES_INTERFACE',
1076 msg_name => 'BOM_DEPT_CODE_INVALID',
1077 err_text => err_text);
1078 update bom_op_sequences_interface set
1079 process_flag = 3
1080 where transaction_id = c1rec.TI;
1081
1082 if (ret_code <> 0) then
1083 return(ret_code);
1084 end if;
1085 goto continue_loop;
1086 end if;
1087 end if;
1088
1089 /*
1090 ** Set standard operation values as defaults
1091 */
1092 stmt_num := 6;
1093 if (c1rec.SOI is not null) then
1094 begin
1095 select DEPARTMENT_ID, MINIMUM_TRANSFER_QUANTITY,
1096 COUNT_POINT_TYPE, OPERATION_DESCRIPTION,
1097 BACKFLUSH_FLAG, OPTION_DEPENDENT_FLAG,
1098 ATTRIBUTE_CATEGORY, ATTRIBUTE1, ATTRIBUTE2,ATTRIBUTE3,
1099 ATTRIBUTE4, ATTRIBUTE5, ATTRIBUTE6, ATTRIBUTE7,
1100 ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10, ATTRIBUTE11,
1101 ATTRIBUTE12, ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15
1102 into std_dept_id, std_min_qty, std_cnt_pt, std_desc,
1103 std_bkflsh, std_opt, std_attcat, std_att1, std_att2,
1104 std_att3, std_att4, std_att5, std_att6, std_att7,
1105 std_att8, std_att9, std_att10, std_att11, std_att12,
1106 std_att13, std_att14, std_att15
1107 from bom_standard_operations
1108 where organization_id = c1rec.OI
1109 and standard_operation_id = c1rec.SOI;
1110 exception
1111 when NO_DATA_FOUND then
1112 ret_code := INVPUOPI.mtl_log_interface_err(
1113 org_id => c1rec.OI,
1114 user_id => user_id,
1115 login_id => login_id,
1116 prog_appid => prog_appid,
1117 prog_id => prog_id,
1118 req_id => req_id,
1119 trans_id => c1rec.TI,
1120 error_text => err_text,
1121 tbl_name => 'BOM_OP_SEQUENCES_INTERFACE',
1122 msg_name => 'BOM_STD_OP_ID_INVALID',
1123 err_text => err_text);
1124 update bom_op_sequences_interface set
1125 process_flag = 3
1126 where transaction_id = c1rec.TI;
1127
1128 if (ret_code <> 0) then
1129 return(ret_code);
1130 end if;
1131 goto continue_loop;
1132 end;
1133
1134 if (c1rec.DI is null) then
1135 c1rec.DI := std_dept_id;
1136 end if;
1137
1138 if (c1rec.MTQ is null) then
1139 c1rec.MTQ := std_min_qty;
1140 end if;
1141
1142 if (c1rec.CPT is null) then
1143 c1rec.CPT := std_cnt_pt;
1144 end if;
1145
1146 if (c1rec.OD is null) then
1147 c1rec.OD := std_desc;
1148 end if;
1149
1150 if (c1rec.BF is null) then
1151 c1rec.BF := std_bkflsh;
1152 end if;
1153
1154 if (c1rec.ODF is null) then
1155 c1rec.ODF := std_opt;
1156 end if;
1157
1158 if (c1rec.AC is null) then
1159 c1rec.AC := std_attcat;
1160 end if;
1161
1162 if (c1rec.A1 is null) then
1163 c1rec.A1 := std_att1;
1164 end if;
1165
1166 if (c1rec.A2 is null) then
1167 c1rec.A2 := std_att2;
1168 end if;
1169
1170 if (c1rec.A3 is null) then
1171 c1rec.A3 := std_att3;
1172 end if;
1173
1174 if (c1rec.A4 is null) then
1175 c1rec.A4 := std_att4;
1176 end if;
1177
1178 if (c1rec.A5 is null) then
1179 c1rec.A5 := std_att5;
1180 end if;
1181
1182 if (c1rec.A6 is null) then
1183 c1rec.A6 := std_att6;
1184 end if;
1185
1186 if (c1rec.A7 is null) then
1187 c1rec.A7 := std_att7;
1188 end if;
1189
1190 if (c1rec.A8 is null) then
1191 c1rec.A8 := std_att8;
1192 end if;
1193
1194 if (c1rec.A9 is null) then
1195 c1rec.A9 := std_att9;
1196 end if;
1197
1198 if (c1rec.A10 is null) then
1199 c1rec.A10 := std_att10;
1200 end if;
1201
1202 if (c1rec.A11 is null) then
1203 c1rec.A11 := std_att11;
1204 end if;
1205
1206 if (c1rec.A12 is null) then
1207 c1rec.A12 := std_att12;
1208 end if;
1209
1210 if (c1rec.A13 is null) then
1211 c1rec.A13 := std_att13;
1212 end if;
1213
1214 if (c1rec.A14 is null) then
1215 c1rec.A14 := std_att14;
1216 end if;
1217
1218 if (c1rec.A15 is null) then
1219 c1rec.A15 := std_att1;
1220 end if;
1221
1222 end if;
1223
1224
1225 stmt_num := 7;
1226 update bom_op_sequences_interface set
1227 department_id = nvl(department_id, c1rec.DI),
1228 organization_id = nvl(organization_id, c1rec.OI),
1229 assembly_item_id = nvl(assembly_item_id, c1rec.AII),
1230 process_flag = 2,
1231 standard_operation_id = nvl(standard_operation_id,c1rec.SOI),
1232 routing_sequence_id = nvl(routing_sequence_id, c1rec.RSI),
1233 operation_sequence_id = nvl(operation_sequence_id, c1rec.OSI),
1234 last_update_date = nvl(last_update_date, sysdate),
1235 last_updated_by = nvl(last_updated_by, user_id),
1236 creation_date = nvl(creation_date, sysdate),
1237 created_by = nvl(created_by, user_id),
1238 last_update_login = nvl(last_update_login, user_id),
1239 request_id = nvl(request_id, req_id),
1240 program_application_id = nvl(program_application_id, prog_appid),
1241 program_id = nvl(program_id, prog_id),
1242 program_update_date = nvl(program_update_date, sysdate),
1243 backflush_flag = nvl(backflush_flag, nvl(c1rec.BF,1)),
1244 count_point_type = nvl(count_point_type,nvl(c1rec.CPT, 1)),
1245 operation_description = nvl(operation_description,
1246 nvl(c1rec.OD,NULL)),
1247 option_dependent_flag =nvl(option_dependent_flag,nvl(c1rec.ODF,2)),
1248 minimum_transfer_quantity = nvl(minimum_transfer_quantity,
1249 nvl(c1rec.MTQ, 0.00)),
1250 attribute_category = nvl(attribute_category,nvl(c1rec.AC,NULL)),
1251 attribute1 = nvl(attribute1, nvl(c1rec.A1, NULL)),
1252 attribute2 = nvl(attribute2, nvl(c1rec.A2, NULL)),
1253 attribute3 = nvl(attribute3, nvl(c1rec.A3, NULL)),
1254 attribute4 = nvl(attribute4, nvl(c1rec.A4, NULL)),
1255 attribute5 = nvl(attribute5, nvl(c1rec.A5, NULL)),
1256 attribute6 = nvl(attribute6, nvl(c1rec.A6, NULL)),
1257 attribute7 = nvl(attribute7, nvl(c1rec.A7, NULL)),
1258 attribute8 = nvl(attribute8, nvl(c1rec.A8, NULL)),
1259 attribute9 = nvl(attribute9, nvl(c1rec.A9, NULL)),
1260 attribute10 = nvl(attribute10, nvl(c1rec.A10, NULL)),
1261 attribute11 = nvl(attribute11, nvl(c1rec.A11, NULL)),
1262 attribute12 = nvl(attribute12, nvl(c1rec.A12, NULL)),
1263 attribute13 = nvl(attribute13, nvl(c1rec.A13, NULL)),
1264 attribute14 = nvl(attribute14, nvl(c1rec.A14, NULL)),
1265 attribute15 = nvl(attribute15, nvl(c1rec.A15, NULL))
1266 where transaction_id = c1rec.TI;
1267
1268 if (SQL%NOTFOUND) then
1269 err_text := substrb('BOMPASGR(bmasopd-' || stmt_num || ') ' || SQLERRM,1,240);
1270 return(SQLCODE);
1271 end if;
1272 /*
1273 ** assign resources for standard operation
1274 */
1275 if (c1rec.SOI is not null) then
1276 insert into bom_op_resources_interface (
1277 OPERATION_SEQUENCE_ID,
1278 RESOURCE_SEQ_NUM,
1279 RESOURCE_ID,
1280 ACTIVITY_ID,
1281 STANDARD_RATE_FLAG,
1282 ASSIGNED_UNITS,
1283 USAGE_RATE_OR_AMOUNT,
1284 USAGE_RATE_OR_AMOUNT_INVERSE,
1285 BASIS_TYPE,
1286 SCHEDULE_FLAG,
1287 AUTOCHARGE_TYPE,
1288 ATTRIBUTE_CATEGORY,
1289 ATTRIBUTE1,
1290 ATTRIBUTE2,
1291 ATTRIBUTE3,
1292 ATTRIBUTE4,
1293 ATTRIBUTE5,
1294 ATTRIBUTE6,
1295 ATTRIBUTE7,
1296 ATTRIBUTE8,
1297 ATTRIBUTE9,
1298 ATTRIBUTE10,
1299 ATTRIBUTE11,
1300 ATTRIBUTE12,
1301 ATTRIBUTE13,
1302 ATTRIBUTE14,
1303 ATTRIBUTE15,
1304 ASSEMBLY_ITEM_ID,
1305 ALTERNATE_ROUTING_DESIGNATOR,
1306 ORGANIZATION_ID,
1307 OPERATION_SEQ_NUM,
1308 EFFECTIVITY_DATE,
1309 ROUTING_SEQUENCE_ID,
1310 ORGANIZATION_CODE,
1311 ASSEMBLY_ITEM_NUMBER,
1312 RESOURCE_CODE,
1313 ACTIVITY,
1314 TRANSACTION_ID,
1315 PROCESS_FLAG) select
1316 c1rec.OSI,
1317 RESOURCE_SEQ_NUM,
1318 RESOURCE_ID,
1319 ACTIVITY_ID,
1320 STANDARD_RATE_FLAG,
1321 ASSIGNED_UNITS,
1322 USAGE_RATE_OR_AMOUNT,
1323 USAGE_RATE_OR_AMOUNT_INVERSE,
1324 BASIS_TYPE,
1325 SCHEDULE_FLAG,
1326 AUTOCHARGE_TYPE,
1327 ATTRIBUTE_CATEGORY,
1328 ATTRIBUTE1,
1329 ATTRIBUTE2,
1330 ATTRIBUTE3,
1331 ATTRIBUTE4,
1332 ATTRIBUTE5,
1333 ATTRIBUTE6,
1334 ATTRIBUTE7,
1335 ATTRIBUTE8,
1336 ATTRIBUTE9,
1337 ATTRIBUTE10,
1338 ATTRIBUTE11,
1339 ATTRIBUTE12,
1340 ATTRIBUTE13,
1341 ATTRIBUTE14,
1342 ATTRIBUTE15,
1343 NULL,
1344 NULL,
1345 c1rec.OI,
1346 NULL,
1347 NULL,
1348 NULL,
1349 NULL,
1350 NULL,
1351 NULL,
1352 NULL,
1353 NULL,
1354 1
1355 from bom_std_op_resources
1356 where standard_operation_id = c1rec.SOI;
1357
1358 end if;
1359
1360 /*
1361 ** assign resources and instructions with op seq for this operation
1362 */
1363 ret_code := bmasopid_assign_op_seq_id(
1364 org_id => c1rec.OI,
1365 assy_id => c1rec.AII,
1366 alt_desg => c1rec.ARD,
1367 op_seq => c1rec.OSN,
1368 op_id => c1rec.OSI,
1369 eff_date => c1rec.ED,
1370 err_text => err_text);
1371
1372 <<continue_loop>>
1373 NULL;
1374 end loop;
1375
1376 commit;
1377
1378 if (commit_cnt < (500 - 1)) then
1379 continue_loop := FALSE;
1380 end if;
1381
1382 end loop;
1383
1384 return(0);
1385 EXCEPTION
1386 when others then
1387 err_text := substrb('BOMPASGR(bmasopd-' || stmt_num || ') ' || SQLERRM,1,240);
1388 return(SQLCODE);
1389
1390 END bmasopd_assign_operation_data;
1391
1392 /*------------------------ bmasrsd_assign_resource_data --------------------*/
1393 /* NAME
1394 bmasrsd_assign_resource_data - assign resource data
1395 DESCRIPTION
1396 assign defaults and various ids in the interface table
1397 BOM_OP_RESOURCES_INTERFACE. If any application error occurs, it
1398 inserts record into MTL_INTERFACE_ERRORS.
1399
1400 REQUIRES
1401 err_text out buffer to return error message
1402 MODIFIES
1403 BOM_OP_RESOURCES_INTERFACE
1404 MTL_INTERFACE_ERRORS
1405 RETURNS
1406 0 if successful
1407 SQLCODE if unsuccessful
1408 NOTES
1409 -----------------------------------------------------------------------------*/
1410 FUNCTION bmasrsd_assign_resource_data (
1411 org_id NUMBER,
1412 all_org NUMBER,
1413 user_id NUMBER,
1414 login_id NUMBER,
1415 prog_appid NUMBER,
1416 prog_id NUMBER,
1417 req_id NUMBER,
1418 err_text IN OUT VARCHAR2
1419 )
1420 return INTEGER
1421 IS
1422 stmt_num NUMBER := 0;
1423 ret_code NUMBER;
1424 dummy_dept NUMBER;
1425 dummy_loc NUMBER;
1426 dummy_24hours NUMBER;
1427 dummy_txn NUMBER;
1428 dummy_org_id NUMBER;
1429 commit_cnt NUMBER;
1430 continue_loop BOOLEAN := TRUE;
1431 total_recs NUMBER;
1432
1433 CURSOR c1 is
1434 select operation_sequence_id OSI,assembly_item_number AIN,
1435 assembly_item_id AII, organization_id OI,
1436 organization_code OC,
1437 alternate_routing_designator ARD, operation_seq_num OSN,
1438 to_char(effectivity_date, 'YYYY/MM/DD HH24:MI:SS') ED, -- Changed for bug 2647027
1439 -- to_char(effectivity_date, 'YYYY/MM/DD HH24:MI') ED,
1440 routing_sequence_id RSI,
1441 transaction_id TI
1442 from bom_op_resources_interface
1443 where process_flag = 1
1444 and operation_sequence_id is null
1445 and (all_org = 1
1446 or
1447 (all_org = 2 and organization_id = org_id)
1448 )
1449 and rownum < 500;
1450
1451 CURSOR c2 is
1452 select transaction_id TI, organization_id OI,
1453 operation_sequence_id OSI, resource_id RI, resource_code RC,
1454 activity_id AI, activity A, usage_rate_or_amount URA,
1455 usage_rate_or_amount_inverse URAI, assigned_units AU,
1456 basis_type BT, autocharge_type AUT, standard_rate_flag SRF
1457 from bom_op_Resources_interface
1458 where process_flag = 1
1459 and operation_sequence_id is not null
1460 and (all_org = 1
1461 or
1462 (all_org = 2 and organization_id = org_id)
1463 )
1464 and rownum < 500;
1465
1466 CURSOR c3 is
1467 select operation_sequence_id OSI
1468 from bom_op_Resources_interface
1469 where process_flag = 99
1470 and (all_org = 1
1471 or
1472 (all_org = 2 and organization_id = org_id)
1473 )
1474 and rownum < 500
1475 group by operation_sequence_id;
1476
1477 BEGIN
1478 /*
1479 ** first load all rows from operations interface into resource interface
1480 */
1481
1482 insert into bom_op_resources_interface (
1483 OPERATION_SEQUENCE_ID,
1484 RESOURCE_SEQ_NUM,
1485 RESOURCE_ID,
1486 RESOURCE_CODE,
1487 ORGANIZATION_ID,
1488 PROCESS_FLAG,
1489 LAST_UPDATE_DATE,
1490 LAST_UPDATED_BY,
1491 CREATION_DATE,
1492 CREATED_BY) select
1493 operation_sequence_id,
1494 10,
1495 resource_id1,
1496 resource_code1,
1497 organization_id,
1498 1,
1499 sysdate,
1500 user_id,
1501 sysdate,
1502 user_id
1503 from bom_op_sequences_interface
1504 where process_flag = 2
1505 and (all_org = 1
1506 or
1507 (all_org = 2 and organization_id = org_id)
1508 )
1509 and (resource_id1 is not null or resource_code1 is not null);
1510 commit;
1511
1512 stmt_num := 1;
1513 insert into bom_op_resources_interface (
1514 OPERATION_SEQUENCE_ID,
1515 RESOURCE_SEQ_NUM,
1516 RESOURCE_ID,
1517 RESOURCE_CODE,
1518 ORGANIZATION_ID,
1519 PROCESS_FLAG,
1520 LAST_UPDATE_DATE,
1521 LAST_UPDATED_BY,
1522 CREATION_DATE,
1523 CREATED_BY) select
1524 operation_sequence_id,
1525 20,
1526 resource_id2,
1527 resource_code2,
1528 organization_id,
1529 1,
1530 sysdate,
1531 user_id,
1532 sysdate,
1533 user_id
1534 from bom_op_sequences_interface
1535 where process_flag = 2
1536 and (all_org = 1
1537 or
1538 (all_org = 2 and organization_id = org_id)
1539 )
1540 and (resource_id2 is not null or resource_code2 is not null);
1541 commit;
1542
1543 stmt_num := 2;
1544 insert into bom_op_resources_interface (
1545 OPERATION_SEQUENCE_ID,
1546 RESOURCE_SEQ_NUM,
1547 RESOURCE_ID,
1548 RESOURCE_CODE,
1549 ORGANIZATION_ID,
1550 PROCESS_FLAG,
1551 LAST_UPDATE_DATE,
1552 LAST_UPDATED_BY,
1553 CREATION_DATE,
1554 CREATED_BY) select
1555 operation_sequence_id,
1556 30,
1557 resource_id3,
1558 resource_code3,
1559 organization_id,
1560 1,
1561 sysdate,
1562 user_id,
1563 sysdate,
1564 user_id
1565 from bom_op_sequences_interface
1566 where process_flag = 2
1567 and (all_org = 1
1568 or
1569 (all_org = 2 and organization_id = org_id)
1570 )
1571 and (resource_id3 is not null or resource_code3 is not null);
1572 commit;
1573 /*
1574 ** assign transaction ids for every row
1575 */
1576 stmt_num := 3;
1577 loop
1578 update bom_op_resources_interface
1579 set transaction_id = mtl_system_items_interface_s.nextval
1580 where transaction_id is null
1581 and process_flag = 1
1582 and rownum < 500;
1583 EXIT when SQL%NOTFOUND;
1584 commit;
1585
1586 end loop;
1587 /*
1588 ** Check if organization id is null
1589 */
1590 stmt_num := 4;
1591 while continue_loop loop
1592 commit_cnt := 0;
1593 for c1rec in c1 loop
1594 commit_cnt := commit_cnt + 1;
1595 if (c1rec.OI is null and c1rec.RSI is null) then
1596 ret_code := INVPUOPI.mtl_log_interface_err(
1597 org_id => NULL,
1598 user_id => user_id,
1599 login_id => login_id,
1600 prog_appid => prog_appid,
1601 prog_id => prog_id,
1602 req_id => req_id,
1603 trans_id => c1rec.TI,
1604 error_text => err_text,
1605 tbl_name => 'BOM_OP_RESOURCES_INTERFACE',
1606 msg_name => 'BOM_ORG_ID_MISSING',
1607 err_text => err_text);
1608 update bom_op_resources_interface set
1609 process_flag = 3
1610 where transaction_id = c1rec.TI;
1611
1612 if (ret_code <> 0) then
1613 return(ret_code);
1614 end if;
1615 goto continue_loop;
1616 end if;
1617
1618 /*
1619 ** Set assembly item id
1620 */
1621 stmt_num := 5;
1622 if (c1rec.AII is null and c1rec.RSI is null) then
1623 ret_code := INVPUOPI.mtl_pr_parse_flex_name(
1624 org_id=> c1rec.OI,
1625 flex_code => 'MSTK',
1626 flex_name => c1rec.AIN,
1627 flex_id => c1rec.AII,
1628 set_id => -1,
1629 err_text => err_text);
1630 if (ret_code <> 0) then
1631 ret_code := INVPUOPI.mtl_log_interface_err(
1632 org_id => c1rec.OI,
1633 user_id => user_id,
1634 login_id => login_id,
1635 prog_appid => prog_appid,
1636 prog_id => prog_id,
1637 req_id => req_id,
1638 trans_id => c1rec.TI,
1639 error_text => err_text,
1640 tbl_name => 'BOM_OP_RESOURCES_INTERFACE',
1641 msg_name => 'BOM_ASSY_ITEM_MISSING',
1642 err_text => err_text);
1643 update bom_op_resources_interface set
1644 process_flag = 3
1645 where transaction_id = c1rec.TI;
1646
1647 if (ret_code <> 0) then
1648 return(ret_code);
1649 end if;
1650 goto continue_loop;
1651 end if;
1652 end if;
1653
1654 /*
1655 ** Get routing sequence id
1656 */
1657 stmt_num := 6;
1658 if (c1rec.RSI is null) then
1659 ret_code := bmgrtsq_get_routing_sequence(
1660 org_id => c1rec.OI,
1661 item_id => c1rec.AII,
1662 alt_desg => c1rec.ARD,
1663 routing_seq_id => c1rec.RSI,
1664 err_text => err_text);
1665 if (ret_code <> 0) then
1666 ret_code := INVPUOPI.mtl_log_interface_err(
1667 org_id => c1rec.OI,
1668 user_id => user_id,
1669 login_id => login_id,
1670 prog_appid => prog_appid,
1671 prog_id => prog_id,
1672 req_id => req_id,
1673 trans_id => c1rec.TI,
1674 error_text => err_text,
1675 tbl_name => 'BOM_OP_RESOURCES_INTERFACE',
1676 msg_name => 'BOM_RTG_SEQ_INVALID',
1677 err_text => err_text);
1678 update bom_op_resources_interface set
1679 process_flag = 3
1680 where transaction_id = c1rec.TI;
1681
1682 if (ret_code <> 0) then
1683 return(ret_code);
1684 end if;
1685 goto continue_loop;
1686 end if;
1687 end if;
1688
1689 /*
1690 ** Get operation sequence id
1691 */
1692 stmt_num := 7;
1693 ret_code := bmgopsq_get_op_sequence(
1694 rtg_seq_id => c1rec.RSI,
1695 op_seq => c1rec.OSN,
1696 eff_date => c1rec.ED,
1697 op_seq_id => c1rec.OSI,
1698 err_text => err_text);
1699 if (ret_code <> 0) then
1700 ret_code := INVPUOPI.mtl_log_interface_err(
1701 org_id => NULL,
1702 user_id => user_id,
1703 login_id => login_id,
1704 prog_appid => prog_appid,
1705 prog_id => prog_id,
1706 req_id => req_id,
1707 trans_id => c1rec.TI,
1708 error_text => err_text,
1709 tbl_name => 'BOM_OP_RESOURCES_INTERFACE',
1710 msg_name => 'BOM_OP_SEQ_INVALID',
1711 err_text => err_text);
1712 update bom_op_resources_interface set
1713 process_flag = 3
1714 where transaction_id = c1rec.TI;
1715
1716 if (ret_code <> 0) then
1717 return(ret_code);
1718 end if;
1719 goto continue_loop;
1720 end if;
1721
1722 stmt_num := 8;
1723 update bom_op_resources_interface
1724 set operation_sequence_id = c1rec.OSI,
1725 assembly_item_id = c1rec.AII,
1726 routing_sequence_id = c1rec.RSI
1727 where transaction_id = c1rec.TI;
1728
1729 <<continue_loop>>
1730 NULL;
1731 end loop;
1732 commit;
1733
1734 if (commit_cnt < (500 - 1)) then
1735 continue_loop := FALSE;
1736 end if;
1737
1738 end loop;
1739
1740 /*
1741 ** Get org id for all records. Needed for record verification.
1742 */
1743 continue_loop := TRUE;
1744 while continue_loop loop
1745 commit_cnt := 0;
1746 for c2rec in c2 loop
1747 begin
1748 commit_cnt := commit_cnt + 1;
1749 dummy_org_id := 0;
1750
1751 select bor.organization_id
1752 into dummy_org_id
1753 from bom_operation_sequences bos,
1754 bom_operational_routings bor
1755 where operation_sequence_id = c2rec.OSI
1756 and bos.routing_sequence_id = bor.routing_sequence_id;
1757 goto get_resource;
1758 exception
1759 when NO_DATA_FOUND then
1760 NULL;
1761 end;
1762
1763 begin
1764 select bori.organization_id
1765 into dummy_org_id
1766 from bom_op_sequences_interface bosi,
1767 bom_op_routings_interface bori
1768 where operation_sequence_id = c2rec.OSI
1769 and bosi.process_flag <>3 and bosi.process_flag <>7
1770 and bori.process_flag <>3 and bori.process_flag <>7
1771 and bosi.routing_sequence_id = bori.routing_sequence_id
1772 and rownum = 1;
1773 goto get_resource;
1774 exception
1775 when NO_DATA_FOUND then
1776 NULL;
1777 end;
1778
1779 begin
1780 select bor.organization_id
1781 into dummy_org_id
1782 from bom_op_sequences_interface bosi,
1783 bom_operational_routings bor
1784 where operation_sequence_id = c2rec.OSI
1785 and bosi.process_flag <> 3 and bosi.process_flag <> 7
1786 and bosi.routing_sequence_id = bor.routing_sequence_id
1787 and rownum = 1;
1788 goto get_resource;
1789 exception
1790 when NO_DATA_FOUND then
1791 ret_code := INVPUOPI.mtl_log_interface_err(
1792 org_id => NULL,
1793 user_id => user_id,
1794 login_id => login_id,
1795 prog_appid => prog_appid,
1796 prog_id => prog_id,
1797 req_id => req_id,
1798 trans_id => c2rec.TI,
1799 error_text => err_text,
1800 tbl_name => 'BOM_OP_RESOURCES_INTERFACE',
1801 msg_name => 'BOM_OP_SEQ_INVALID',
1802 err_text => err_text);
1803 update bom_op_resources_interface set
1804 process_flag = 3
1805 where transaction_id = c2rec.TI;
1806
1807 if (ret_code <> 0) then
1808 return(ret_code);
1809 end if;
1810 goto continue_loop2;
1811 end;
1812 /*
1813 ** Get resource_id
1814 */
1815 <<get_resource>>
1816 if (c2rec.OI is null) then
1817 c2rec.OI := dummy_org_id;
1818 end if;
1819
1820 stmt_num := 10;
1821 if (c2rec.RI is null) then
1822 BEGIN
1823 select resource_id
1824 into c2rec.RI
1825 from bom_resources
1826 where resource_code = c2rec.RC
1827 and organization_id = c2rec.OI;
1828 EXCEPTION
1829 when NO_DATA_FOUND then
1830 ret_code := INVPUOPI.mtl_log_interface_err(
1831 org_id => NULL,
1832 user_id => user_id,
1833 login_id => login_id,
1834 prog_appid => prog_appid,
1835 prog_id => prog_id,
1836 req_id => req_id,
1837 trans_id => c2rec.TI,
1838 error_text => err_text,
1839 tbl_name => 'BOM_OP_RESOURCES_INTERFACE',
1840 msg_name => 'BOM_RESOURCE_ID_MISSING',
1841 err_text => err_text);
1842 update bom_op_resources_interface set
1843 process_flag = 3
1844 where transaction_id = c2rec.TI;
1845
1846 if (ret_code <> 0) then
1847 return(ret_code);
1848 end if;
1849 goto continue_loop2;
1850 END;
1851 end if;
1852 /*
1853 ** Get activity_id
1854 */
1855 stmt_num := 11;
1856 if (c2rec.AI is null and c2rec.A is not null) then
1857 BEGIN
1858 select activity_id
1859 into c2rec.AI
1860 from cst_activities
1861 where activity = c2rec.A
1862 and nvl(organization_id,c2rec.OI) = c2rec.OI;
1863 EXCEPTION
1864 when NO_DATA_FOUND then
1865 ret_code := INVPUOPI.mtl_log_interface_err(
1866 org_id => NULL,
1867 user_id => user_id,
1868 login_id => login_id,
1869 prog_appid => prog_appid,
1870 prog_id => prog_id,
1871 req_id => req_id,
1872 trans_id => c2rec.TI,
1873 error_text => err_text,
1874 tbl_name => 'BOM_OP_RESOURCES_INTERFACE',
1875 msg_name => 'BOM_ACTIVITY_INVALID',
1876 err_text => err_text);
1877 update bom_op_resources_interface set
1878 process_flag = 3
1879 where transaction_id = c2rec.TI;
1880
1881 if (ret_code <> 0) then
1882 return(ret_code);
1883 end if;
1884 goto continue_loop2;
1885 END;
1886 end if;
1887 /*
1888 ** Check usage rate or amount values
1889 */
1890 stmt_num := 12;
1891 if (c2rec.URA is null and c2rec.URAI is not null) then
1892 if (c2rec.URAI = 0) then
1893 c2rec.URA := 0;
1894 else
1895 c2rec.URA := 1/(c2rec.URAI);
1896 end if;
1897 end if;
1898
1899 if (c2rec.URAI is null and c2rec.URA is not null) then
1900 if (c2rec.URA = 0) then
1901 c2rec.URAI := 0;
1902 else
1903 c2rec.URAI := 1/(c2rec.URA);
1904 end if;
1905 end if;
1906 /*
1907 ** Check if resource is available 24 hours
1908 */
1909
1910 if (c2rec.AU is null) then
1911 c2rec.AU := 1;
1912 end if;
1913
1914 stmt_num := 13;
1915 BEGIN
1916 select department_id
1917 into dummy_dept
1918 from bom_operation_sequences
1919 where operation_sequence_id = c2rec.OSI;
1920 goto get_flag;
1921 EXCEPTION
1922 when NO_DATA_FOUND then
1923 NULL;
1924 END;
1925
1926 BEGIN
1927 select department_id
1928 into dummy_dept
1929 from bom_op_sequences_interface
1930 where operation_sequence_id = c2rec.OSI
1931 and process_flag <>3 and process_flag <> 7
1932 and rownum = 1;
1933
1934 EXCEPTION
1935 when NO_DATA_FOUND then
1936 ret_code := INVPUOPI.mtl_log_interface_err(
1937 org_id => NULL,
1938 user_id => user_id,
1939 login_id => login_id,
1940 prog_appid => prog_appid,
1941 prog_id => prog_id,
1942 req_id => req_id,
1943 trans_id => c2rec.TI,
1944 error_text => err_text,
1945 tbl_name => 'BOM_OP_RESOURCES_INTERFACE',
1946 msg_name => 'BOM_OP_SEQ_INVALID',
1947 err_text => err_text);
1948 update bom_op_resources_interface set
1949 process_flag = 3
1950 where transaction_id = c2rec.TI;
1951
1952 if (ret_code <> 0) then
1953 return(ret_code);
1954 end if;
1955 goto continue_loop2;
1956 END;
1957
1958 <<get_flag>>
1959 stmt_num := 14;
1960 BEGIN
1961 select bdr.AVAILABLE_24_HOURS_FLAG, bd.location_id
1962 into dummy_24hours, dummy_loc
1963 from bom_department_resources bdr,
1964 bom_departments bd
1965 where bdr.resource_id = c2rec.RI
1966 and bdr.department_id = dummy_dept
1967 and bdr.department_id = bd.department_id;
1968
1969 if (dummy_24hours = 1) then
1970 c2rec.AU := 1;
1971 end if;
1972
1973 EXCEPTION
1974 when NO_DATA_FOUND then
1975 ret_code := INVPUOPI.mtl_log_interface_err(
1976 org_id => NULL,
1977 user_id => user_id,
1978 login_id => login_id,
1979 prog_appid => prog_appid,
1980 prog_id => prog_id,
1981 req_id => req_id,
1982 trans_id => c2rec.TI,
1983 error_text => err_text,
1984 tbl_name => 'BOM_OP_RESOURCES_INTERFACE',
1985 msg_name => 'BOM_DEPT_RES_INVALID',
1986 err_text => err_text);
1987 update bom_op_resources_interface set
1988 process_flag = 3
1989 where transaction_id = c2rec.TI;
1990
1991 if (ret_code <> 0) then
1992 return(ret_code);
1993 end if;
1994 goto continue_loop2;
1995 END;
1996
1997 /*
1998 ** Get Basis and Autocharge defaults
1999 */
2000 stmt_num := 16;
2001 select nvl(c2rec.BT,default_basis_type),
2002 nvl(c2rec.AI, default_activity_id),
2003 nvl(c2rec.AUT,decode(dummy_loc, NULL,
2004 decode(AUTOCHARGE_TYPE, NULL, 2, 3, 2, 4, 2,
2005 AUTOCHARGE_TYPE), nvl(AUTOCHARGE_TYPE, 2))),
2006 nvl(c2rec.SRF, standard_rate_flag)
2007 into c2rec.BT, c2rec.AI, c2rec.AUT, c2rec.SRF
2008 from bom_resources
2009 where resource_id = c2rec.RI;
2010
2011 stmt_num := 17;
2012 update bom_op_resources_interface set
2013 resource_id = c2rec.RI,
2014 organization_id = c2rec.OI,
2015 BASIS_TYPE = nvl(c2rec.BT,1),
2016 AUTOCHARGE_TYPE = c2rec.AUT,
2017 ACTIVITY_ID = c2rec.AI,
2018 STANDARD_RATE_FLAG = nvl(c2rec.SRF, 1),
2019 ASSIGNED_UNITS = c2rec.AU,
2020 USAGE_RATE_OR_AMOUNT = nvl(c2rec.URA,1),
2021 USAGE_RATE_OR_AMOUNT_INVERSE = nvl(c2rec.URAI,1),
2022 SCHEDULE_FLAG = nvl(schedule_flag, 2),
2023 PROCESS_FLAG = 99,
2024 last_update_date = sysdate,
2025 last_updated_by = user_id,
2026 creation_date = sysdate,
2027 created_by = user_id,
2028 last_update_login = nvl(last_update_login, user_id),
2029 request_id = nvl(request_id, req_id),
2030 program_application_id = nvl(program_application_id, prog_appid),
2031 program_id = nvl(program_id, prog_id),
2032 program_update_date = nvl(program_update_date, sysdate)
2033 where transaction_id = c2rec.TI;
2034
2035 <<continue_loop2>>
2036 NULL;
2037 end loop;
2038
2039 commit;
2040
2041 if (commit_cnt < (500 - 1)) then
2042 continue_loop := FALSE;
2043 end if;
2044
2045 end loop;
2046
2047 /*
2048 ** Set records with same operation_sequence_id with the same txn id
2049 ** for set processing
2050 */
2051 select count(distinct operation_sequence_id)
2052 into total_recs
2053 from bom_op_resources_interface
2054 where process_flag = 99;
2055
2056 continue_loop := TRUE;
2057 commit_cnt := 0;
2058
2059 while continue_loop loop
2060 for c3rec in c3 loop
2061 commit_cnt := commit_cnt + 1;
2062 select mtl_system_items_interface_s.nextval
2063 into dummy_txn
2064 from sys.dual;
2065
2066 update bom_op_resources_interface
2067 set transaction_id = dummy_txn,
2068 process_flag = 2
2069 where operation_sequence_id = c3rec.OSI
2070 and process_flag = 99;
2071
2072 end loop;
2073
2074 commit;
2075
2076 if (commit_cnt < total_recs) then
2077 null;
2078 else
2079 continue_loop := FALSE;
2080 end if;
2081
2082 end loop;
2083
2084 return (0);
2085 EXCEPTION
2086 when others then
2087 err_text := substrb('BOMPASGR(bmasrsd-'||stmt_num||') '|| SQLERRM,1,240);
2088 return(SQLCODE);
2089 END bmasrsd_assign_resource_data;
2090
2091
2092 /*----------------------- bmgrtsq_get_routing_sequence ----------------------*/
2093 /* NAME
2094 bmgrtsq_get_routing_sequence - get routing sequence id
2095 DESCRIPTION
2096 searches the prod table first adn then the interface table to
2097 determine the routing_sequence_id
2098
2099 REQUIRES
2100 org_id organization_id
2101 item_id assembly item id
2102 alt_desg alternate_routing_designator
2103 routing_seq_id out parameter for routing sequence id
2104 err_text out buffer to return error message
2105 MODIFIES
2106 RETURNS
2107 0 if successful
2108 SQLCODE if unsuccessful
2109 NOTES
2110 -----------------------------------------------------------------------------*/
2111 FUNCTION bmgrtsq_get_routing_sequence (
2112 org_id NUMBER,
2113 item_id NUMBER,
2114 alt_desg VARCHAR2,
2115 routing_seq_id OUT NUMBER,
2116 err_text OUT VARCHAR2
2117 )
2118 return INTEGER
2119 IS
2120 rtg_seq_id NUMBER;
2121 BEGIN
2122
2123 BEGIN
2124 select routing_sequence_id
2125 into rtg_seq_id
2126 from bom_operational_routings
2127 where organization_id = org_id
2128 and assembly_item_id = item_id
2129 and nvl(alternate_routing_designator, 'NONE') =
2130 nvl(alt_desg, 'NONE');
2131 routing_seq_id := rtg_seq_id;
2132 return(0);
2133 EXCEPTION
2134 when NO_DATA_FOUND then
2135 NULL;
2136 when others then
2137 err_text := 'BOMPAVRT ' || substrb(SQLERRM,1,240);
2138 return(SQLCODE);
2139 END;
2140
2141 select routing_sequence_id
2142 into rtg_seq_id
2143 from bom_op_routings_interface
2144 where organization_id = org_id
2145 and assembly_item_id = item_id
2146 and nvl(alternate_routing_designator, 'NONE') =
2147 nvl(alt_desg, 'NONE')
2148 and process_flag <> 3 and process_flag <> 7
2149 and rownum = 1;
2150
2151 routing_seq_id := rtg_seq_id;
2152 return(0);
2153
2154 EXCEPTION
2155 when others then
2156 routing_seq_id := -1;
2157 err_text := 'BOMPAVRT ' || substrb(SQLERRM,1,240);
2158 return(SQLCODE);
2159
2160 END bmgrtsq_get_routing_sequence;
2161
2162
2163 /*------------------------- bmgopsq_get_op_sequence -------------------------*/
2164 /* NAME
2165 bmgopsq_get_op_sequence - get operation sequence id
2166 DESCRIPTION
2167 searches the prod table first and then the interface table to
2168 determine the operation_sequence_id
2169
2170 REQUIRES
2171 rtg_seq_id routing sequence id
2172 op_seq operation_seq_num
2173 eff_date effectivity_date
2174 op_seq_id out operation_sequence_id
2175 err_text out buffer to return error message
2176 MODIFIES
2177 RETURNS
2178 0 if successful
2179 SQLCODE if unsuccessful
2180 NOTES
2181 -----------------------------------------------------------------------------*/
2182 FUNCTION bmgopsq_get_op_sequence(
2183 rtg_seq_id NUMBER,
2184 op_seq NUMBER,
2185 eff_date VARCHAR2,
2186 op_seq_id OUT NUMBER,
2187 err_text OUT VARCHAR2
2188 )
2189 return INTEGER
2190 IS
2191 BEGIN
2192
2193 BEGIN
2194 select operation_sequence_id
2195 into op_seq_id
2196 from bom_operation_sequences
2197 where routing_sequence_id = rtg_seq_id
2198 and operation_seq_num = op_seq
2199 and to_char(effectivity_date,'YYYY/MM/DD HH24:MI:SS') = eff_date; -- Changed for bug 2647027
2200 -- and to_char(effectivity_date,'YYYY/MM/DD HH24:MI') = eff_date;
2201 return(0);
2202 EXCEPTION
2203 when NO_DATA_FOUND then
2204 NULL;
2205 when others then
2206 err_text := 'BOMPASGR(bmgopsq) ' || substrb(SQLERRM,1,240);
2207 return(SQLCODE);
2208 END;
2209
2210 select operation_sequence_id
2211 into op_seq_id
2212 from bom_op_sequences_interface
2213 where routing_sequence_id = rtg_seq_id
2214 and operation_seq_num = op_seq
2215 and to_char(effectivity_date,'YYYY/MM/DD HH24:MI:SS') = eff_date -- Changed for bug 2647027
2216 -- and to_char(effectivity_date,'YYYY/MM/DD HH24:MI') = eff_date
2217 and process_flag <> 3 and process_flag <> 7
2218 and rownum = 1;
2219 return(0);
2220
2221 EXCEPTION
2222 when NO_DATA_FOUND then
2223 err_text := 'BOMPASGR(bmgopsq): Operation does not exist';
2224 return(9999);
2225 when others then
2226 op_seq_id := -1;
2227 err_text := 'BOMPASGR(bmgopsq) ' || substrb(SQLERRM,1,240);
2228 return(SQLCODE);
2229
2230 END bmgopsq_get_op_sequence;
2231
2232
2233 /*------------------------ bmasopid_assign_op_seq_id ------------------------*/
2234 /* NAME
2235 bmasopid_assign_op_seq_id - assign operation_sequence_ids to
2236 child tables
2237 DESCRIPTION
2238 assigns operation_seq_ids to child table for op resoruces
2239 and op instructions
2240
2241 REQUISTDOP
2242 org_id organization_id
2243 assy_id assembly_itEM_ID
2244 alt_desg alterante_routing_Designator
2245 op_seq operation_seq_num
2246 op_id operation_seq_id
2247 eff_date effectivity_date
2248 err_text out buffer to return error message
2249 MODIFIES
2250 RETURNS
2251 0 if successful
2252 SQLCODE if unsuccessful
2253 NOTES
2254 -----------------------------------------------------------------------------*/
2255 FUNCTION bmasopid_assign_op_seq_id(
2256 org_id NUMBER,
2257 assy_id NUMBER,
2258 alt_desg VARCHAR2,
2259 op_seq NUMBER,
2260 op_id NUMBER,
2261 eff_date VARCHAR2,
2262 err_text OUT VARCHAR2
2263 )
2264 return INTEGER
2265
2266 IS
2267 stmt_num NUMBER := 1;
2268 BEGIN
2269 loop
2270 update bom_op_resources_interface set
2271 operation_sequence_id = op_id
2272 where process_flag = 1
2273 and organization_id = org_id
2274 and assembly_item_id = assy_id
2275 and nvl(alternate_routing_designator, 'NONE') =
2276 nvl(alt_desg, 'NONE')
2277 and operation_seq_num = op_seq
2278 and to_char(effectivity_date,'YYYY/MM/DD HH24:MI:SS') = eff_date -- Changed for bug 2647027
2279 -- and to_char(effectivity_date,'YYYY/MM/DD HH24:MI') = eff_date
2280 and operation_sequence_id is null
2281 and rownum < 500;
2282 EXIT when SQL%NOTFOUND;
2283 commit;
2284 end loop;
2285
2286 return(0);
2287
2288 EXCEPTION
2289 when others then
2290 err_text := substrb('BOMPASGR(bmasopid-' || stmt_num || ') ' || SQLERRM,1,240);
2291 return(SQLCODE);
2292 END bmasopid_assign_op_seq_id;
2293
2294 /*---------------------- bmasrtgid_assign_rtg_seq_id -----------------------*/
2295 /* NAME
2296 bmasrtgid_assign_rtg_seq_id - assign routing_sequence_ids to
2297 child tables
2298 DESCRIPTION
2299 assigns routing_seq_ids to routing child tables
2300
2301 REQUISTDOP
2302 org_id organization_id
2303 assy_id assembly_itEM_ID
2304 alt_desg alterante_routing_Designator
2305 rtg_id routing_sequence_id
2306 err_text out buffer to return error message
2307 MODIFIES
2308 RETURNS
2309 0 if successful
2310 SQLCODE if unsuccessful
2311 NOTES
2312 -----------------------------------------------------------------------------*/
2313 FUNCTION bmasrtgid_assign_rtg_seq_id(
2314 org_id NUMBER,
2315 assy_id NUMBER,
2316 alt_desg VARCHAR2,
2317 rtg_id NUMBER,
2318 err_text OUT VARCHAR2
2319 )
2320 return INTEGER
2321
2322 IS
2323 stmt_num NUMBER := 1;
2324 BEGIN
2325 loop
2326 update bom_op_sequences_interface set
2327 routing_sequence_id = rtg_id
2328 where process_flag = 1
2329 and organization_id = org_id
2330 and assembly_item_id = assy_id
2331 and nvl(alternate_routing_designator, 'NONE') =
2332 nvl(alt_desg, 'NONE')
2333 and routing_sequence_id is null
2334 and rownum < 500;
2335 EXIT when SQL%NOTFOUND;
2336 commit;
2337 end loop;
2338
2339 loop
2340 stmt_num := 2;
2341 update bom_op_resources_interface set
2342 routing_sequence_id = rtg_id
2343 where process_flag = 1
2344 and organization_id = org_id
2345 and assembly_item_id = assy_id
2346 and nvl(alternate_routing_designator, 'NONE') =
2347 nvl(alt_desg, 'NONE')
2348 and routing_sequence_id is null
2349 and rownum < 500;
2350 EXIT when SQL%NOTFOUND;
2351 commit;
2352 end loop;
2353
2354 return(0);
2355 EXCEPTION
2356 when others then
2357 err_text := substrb('BOMPASGR(bmasrtgid-' || stmt_num || ') ' || SQLERRM,1,240);
2358 return(SQLCODE);
2359 END bmasrtgid_assign_rtg_seq_id;
2360
2361 /*-------------------------- bmgrtin_get_rtg_info --------------------------*/
2362 /* NAME
2363 bmgrtin_get_rtg_info- get assembly item id, org id and alternate
2364 DESCRIPTION
2365 searches the prod table first and then the interface table to
2366 determine the assembly item id, org id and alternate for a rtg seq id
2367
2368 REQUIRES
2369 org_id out organization_id
2370 item_id out assembly item id
2371 alt_desg out alternate bom designator
2372 rtg_seq_id parameter for rtg sequence id
2373 err_text out buffer to return error message
2374 MODIFIES
2375 RETURNS
2376 0 if successful
2377 SQLCODE if unsuccessful
2378 NOTES
2379 -----------------------------------------------------------------------------*/
2380 FUNCTION bmgrtin_get_rtg_info(
2381 org_id OUT NUMBER,
2382 item_id OUT NUMBER,
2383 alt_desg OUT VARCHAR2,
2384 rtg_seq_id NUMBER,
2385 err_text OUT VARCHAR2
2386 )
2387 return INTEGER
2388 IS
2389 seq_id NUMBER;
2390 BEGIN
2391
2392 BEGIN
2393 select assembly_item_id, organization_id, alternate_routing_designator
2394 into item_id, org_id, alt_desg
2395 from bom_operational_routings
2396 where routing_sequence_id = rtg_seq_id;
2397 return(0);
2398 EXCEPTION
2399 when NO_DATA_FOUND then
2400 NULL;
2401 when others then
2402 err_text := 'BOMPASGR(bmgrtin) ' || substrb(SQLERRM,1,240);
2403 return(SQLCODE);
2404 END;
2405
2406 select assembly_item_id, organization_id, alternate_routing_designator
2407 into item_id, org_id, alt_desg
2408 from bom_op_routings_interface
2409 where routing_sequence_id = rtg_seq_id
2410 and process_flag <> 3 and process_flag <> 7
2411 and rownum = 1;
2412
2413 return(0);
2414
2415 EXCEPTION
2416 when NO_DATA_FOUND then
2417 err_text := 'BOMPASGR(bmgrtin): Routing sequence id does not exist';
2418 return(9999);
2419 when others then
2420 err_text := 'BOMPASGR(bmgrtin) ' || substrb(SQLERRM,1,240);
2421 return(SQLCODE);
2422
2423 END bmgrtin_get_rtg_info;
2424
2425
2426 /*------------------------- bmgtdep_get_department --------------------------*/
2427 /* NAME
2428 bmgtdep_get_department - get department id
2429 DESCRIPTION
2430 given dept code, get department id
2431
2432 REQUISTDOP
2433 org_id organization_id
2434 dept_code department_code
2435 dept_id return department id
2436 err_text out buffer to return error message
2437 MODIFIES
2438 RETURNS
2439 0 if successful
2440 SQLCODE if unsuccessful
2441 NOTES
2442 -----------------------------------------------------------------------------*/
2443 FUNCTION bmgtdep_get_department(
2444 org_id NUMBER,
2445 dept_code VARCHAR2,
2446 dept_id OUT NUMBER,
2447 err_text OUT VARCHAR2
2448 )
2449 return INTEGER
2450 IS
2451
2452 BEGIN
2453 select department_id
2454 into dept_id
2455 from bom_departments
2456 where organization_id = org_id
2457 and department_code = dept_code;
2458 return(0);
2459 EXCEPTION
2460 when others then
2461 err_text := 'BOMPASGR(bmgtdep)' || substrb(SQLERRM,1,240);
2462 return(SQLCODE);
2463 END bmgtdep_get_department;
2464
2465 /*-------------------------- bmgtstdop_get_stdop ----------------------------*/
2466 /* NAME
2467 bmgtstdop_get_stdop - get std op id
2468 DESCRIPTION
2469 given std op code, get std op id
2470
2471 REQUISTDOP
2472 org_id organization_id
2473 stdop_code std op code
2474 stdop_id return std op id
2475 err_text out buffer to return error message
2476 MODIFIES
2477 RETURNS
2478 0 if successful
2479 SQLCODE if unsuccessful
2480 NOTES
2481 -----------------------------------------------------------------------------*/
2482 FUNCTION bmgtstdop_get_stdop(
2483 org_id NUMBER,
2484 stdop_code VARCHAR2,
2485 stdop_id OUT NUMBER,
2486 err_text OUT VARCHAR2
2487 )
2488 return INTEGER
2489 IS
2490
2491 BEGIN
2492 select standard_operation_id
2493 into stdop_id
2494 from bom_Standard_operations
2495 where organization_id = org_id
2496 and operation_code = stdop_code;
2497 return(0);
2498 EXCEPTION
2499 when others then
2500 err_text := 'BOMPASGR(bmgtstdop)' || substrb(SQLERRM,1,240);
2501 return(SQLCODE);
2502 END bmgtstdop_get_stdop;
2503
2504 /*----------------------- bmasritm_assign_rtg_item_id -----------------------*/
2505 /* NAME
2506 bmasritm_assign_rtg_item_id - assign item_id to all rtg tables
2507 DESCRIPTION
2508 assign item id to all routing child tables
2509
2510 REQUIRES
2511 err_text out buffer to return error message
2512 MODIFIES
2513 BOM_OP_ROUTINGS_INTERFACE
2514 BOM_OP_SEQUENCES_INTERFACE
2515 BOM_OP_RESOURCES_INTERFACE
2516 MTL_INTERFACE_ERRORS
2517 RETURNS
2518 0 if successful
2519 SQLCODE if unsuccessful
2520 NOTES
2521 -----------------------------------------------------------------------------*/
2522 FUNCTION bmasritm_assign_rtg_item_id(
2523 org_id NUMBER,
2524 item_num VARCHAR2,
2525 item_id NUMBER,
2526 err_text OUT VARCHAR2
2527 ) return INTEGER IS
2528
2529 Cursor GetOps is
2530 Select bos.rowid row_id
2531 From bom_op_sequences_interface bos
2532 where bos.process_flag = 1
2533 and bos.assembly_item_id is null
2534 and bos.organization_id = org_id
2535 and bos.assembly_item_number = item_num
2536 and bos.routing_sequence_id is null;
2537 Cursor GetResources is
2538 Select bor.rowid row_id
2539 From bom_op_resources_interface bor
2540 where bor.process_flag = 1
2541 and bor.assembly_item_id is null
2542 and bor.organization_id = org_id
2543 and bor.assembly_item_number = item_num
2544 and bor.routing_sequence_id is null;
2545
2546 BEGIN
2547 For X_Operation in GetOps loop
2548 update bom_op_sequences_interface
2549 set assembly_item_id = item_id
2550 where rowid = X_Operation.row_id;
2551 If mod(GetOps%rowcount, 500) = 0 then
2552 commit;
2553 -- dbms_output.put_line(
2554 -- 'Operation assembly ids commited at row '||to_char(GetOps%rowcount));
2555 End if; -- commit every 500 rows
2556 end loop;
2557 commit;
2558
2559 For X_Resource in GetResources loop
2560 update bom_op_resources_interface
2561 set assembly_item_id = item_id
2562 where rowid = X_Resource.row_id;
2563 If mod(GetResources%rowcount, 500) = 0 then
2564 commit;
2565 -- dbms_output.put_line(
2566 -- 'Resource assembly ids commited at row '||to_char(GetResources%rowcount));
2567 End if; -- commit every 500 rows
2568 end loop;
2569 commit;
2570
2571 return(0);
2572 exception
2573 when others then
2574 err_text := 'BOMPASGR(bmasritm) ' || substrb(SQLERRM,1,240);
2575 return(SQLCODE);
2576 end bmasritm_assign_rtg_item_id;
2577
2578 END BOMPASGR;