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