[Home] [Help]
PACKAGE BODY: APPS.BOMPVALB
Source
1 package body BOMPVALB as
2 /* $Header: BOMVALBB.pls 115.4 99/07/16 05:16:42 porting sh $ */
3 /*==========================================================================+
4 | Copyright (c) 1993 Oracle Corporation Belmont, California, USA |
5 | All rights reserved. |
6 +===========================================================================+
7 | |
8 | File Name : BOMPVALB.plb |
9 | DESCRIPTION : This package contains functions used to validate 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 | request_id request_id |
18 | user_id user id |
19 | login_id login id |
20 | History: |
21 | 11/22/93 Shreyas Shah creation date |
22 | 04/24/94 Julie Maeyama Modified code |
23 +==========================================================================*/
24 /*---------------------- bmvbomh_validate_bom_header -----------------------*/
25 /* NAME
26 bmvbomh_validate_bom_header - validate bom data
27 DESCRIPTION
28 validate the bom header information before loading into the
29 production tables.
30
31 REQUIRES
32 err_text out buffer to return error message
33 MODIFIES
34 MTL_INTERFACE_ERRORS
35 RETURNS
36 0 if successful
37 SQLCODE if unsuccessful
38 NOTES
39 -----------------------------------------------------------------------------*/
40 FUNCTION bmvbomh_validate_bom_header (
41 org_id NUMBER,
42 all_org NUMBER := 2,
43 user_id NUMBER,
44 login_id NUMBER,
45 prog_appid NUMBER,
46 prog_id NUMBER,
47 request_id NUMBER,
48 err_text IN OUT VARCHAR2
49 )
50 return INTEGER
51 IS
52 CURSOR c1 is select
53 organization_id OI, bill_sequence_id BSI,
54 assembly_item_id AII, common_bill_sequence_id CBSI,
55 common_assembly_item_id CAII, assembly_type AST,
56 common_organization_id COI,
57 alternate_bom_designator ABD, transaction_id TI
58 from bom_bill_of_mtls_interface
59 where process_flag = 2
60 and rownum < 500;
61
62 ret_code NUMBER;
63 stmt_num NUMBER;
64 dummy_id NUMBER;
65 commit_cnt NUMBER;
66 continue_loop BOOLEAN := TRUE;
67 BEGIN
68 /*
69 ** do row by row verification
70 */
71 while continue_loop loop
72 commit_cnt := 0;
73 for c1rec in c1 loop
74 commit_cnt := commit_cnt + 1;
75 stmt_num := 1; /* Check for valid org id */
76 BEGIN
77 select organization_id
78 into dummy_id
79 from mtl_parameters
80 where organization_id = c1rec.OI;
81 EXCEPTION
82 when NO_DATA_FOUND then
83 ret_code := INVPUOPI.mtl_log_interface_err(
84 org_id => c1rec.OI,
85 user_id => user_id,
86 login_id => login_id,
87 prog_appid => prog_appid,
88 prog_id => prog_id,
89 req_id => request_id,
90 trans_id => c1rec.TI,
91 error_text => err_text,
92 tbl_name => 'BOM_BILL_OF_MTLS_INTERFACE',
93 msg_name => 'BOM_INVALID_ORG_ID',
94 err_text => err_text);
95 update bom_bill_of_mtls_interface set
96 process_flag = 3
97 where transaction_id = c1rec.TI;
98
99 if (ret_code <> 0) then
100 return(ret_code);
101 end if;
102 goto continue_loop;
103 END;
104
105 stmt_num := 2;
106 if (c1rec.ABD is not null) then /* Check for valid alternate */
107 BEGIN
108 select 1
109 into dummy_id
110 from bom_alternate_designators
111 where organization_id = c1rec.OI
112 and alternate_designator_code = c1rec.ABD;
113 EXCEPTION
114 when NO_DATA_FOUND then
115 ret_code := INVPUOPI.mtl_log_interface_err(
116 org_id => c1rec.OI,
117 user_id => user_id,
118 login_id => login_id,
119 prog_appid => prog_appid,
120 prog_id => prog_id,
121 req_id => request_id,
122 trans_id => c1rec.TI,
123 error_text => err_text,
124 tbl_name => 'BOM_BILL_OF_MTLS_INTERFACE',
125 msg_name => 'BOM_INVALID_ALTERNATE',
126 err_text => err_text);
127 update bom_bill_of_mtls_interface set
128 process_flag = 3
129 where transaction_id = c1rec.TI;
130
131 if (ret_code <> 0) then
132 return(ret_code);
133 end if;
134 goto continue_loop;
135 END;
136 end if;
137
138 stmt_num := 3; /* Check if assembly item exists */
139 ret_code := bmvassyid_verify_assembly_id(
140 org_id => c1rec.OI,
141 assy_id => c1rec.AII,
142 err_text => err_text);
143 if (ret_code <> 0) then
144 ret_code := INVPUOPI.mtl_log_interface_err(
145 org_id => c1rec.OI,
146 user_id => user_id,
147 login_id => login_id,
148 prog_appid => prog_appid,
149 prog_id => prog_id,
150 req_id => request_id,
151 trans_id => c1rec.TI,
152 error_text => err_text,
153 tbl_name => 'BOM_BILL_OF_MTLS_INTERFACE',
154 msg_name => 'BOM_ASSEMBLY_ITEM_INVALID',
155 err_text => err_text);
156 update bom_bill_of_mtls_interface set
157 process_flag = 3
158 where transaction_id = c1rec.TI;
159
160 if (ret_code <> 0) then
161 return(ret_code);
162 end if;
163 goto continue_loop;
164 end if;
165
166 stmt_num := 4; /* assembly_type must be 1 or 2 */
167 if (c1rec.AST <> 1) and (c1rec.AST <> 2) then
168 ret_code := INVPUOPI.mtl_log_interface_err(
169 org_id => c1rec.OI,
170 user_id => user_id,
171 login_id => login_id,
172 prog_appid => prog_appid,
173 prog_id => prog_id,
174 req_id => request_id,
175 trans_id => c1rec.TI,
176 error_text => err_text,
177 tbl_name => 'BOM_BILL_OF_MTLS_INTERFACE',
178 msg_name => 'BOM_ASSEMBLY_TYPE_INVALID',
179 err_text => err_text);
180 update bom_bill_of_mtls_interface set
181 process_flag = 3
182 where transaction_id = c1rec.TI;
183
184 if (ret_code <> 0) then
185 return(ret_code);
186 end if;
187 goto continue_loop;
188 end if;
189
190 stmt_num := 5;
191 ret_code :=bmvrbom_verify_bom( /* Check for unique bill seq id */
192 bom_seq_id => c1rec.BSI,
193 mode_type => 1,
194 err_text => err_text);
195 if (ret_code <> 0) then
196 ret_code := INVPUOPI.mtl_log_interface_err(
197 org_id => c1rec.OI,
198 user_id => user_id,
199 login_id => login_id,
200 prog_appid => prog_appid,
201 prog_id => prog_id,
202 req_id => request_id,
203 trans_id => c1rec.TI,
204 error_text => err_text,
205 tbl_name => 'BOM_BILL_OF_MTLS_INTERFACE',
206 msg_name => 'BOM_DUPLICATE_BILL',
207 err_text => err_text);
208 update bom_bill_of_mtls_interface set
209 process_flag = 3
210 where transaction_id = c1rec.TI;
211
212 if (ret_code <> 0) then
213 return(ret_code);
214 end if;
215 goto continue_loop;
216 end if;
217
218 stmt_num := 6;
219 /*
220 ** Check for duplicate assy,org,alt combo
221 ** Check for primary/alternate violation
222 */
223 ret_code :=bmvdupbom_verify_duplicate_bom(
224 org_id => c1rec.OI,
225 assy_id => c1rec.AII,
226 alt_desg => c1rec.ABD,
227 assy_type => c1rec.AST,
228 err_text => err_text);
229 if (ret_code <> 0) then
230 ret_code := INVPUOPI.mtl_log_interface_err(
231 org_id => c1rec.OI,
232 user_id => user_id,
233 login_id => login_id,
234 prog_appid => prog_appid,
235 prog_id => prog_id,
236 req_id => request_id,
237 trans_id => c1rec.TI,
238 error_text => err_text,
239 tbl_name => 'BOM_BILL_OF_MTLS_INTERFACE',
240 msg_name => 'BOM_BILL_VALIDATION_ERR',
241 err_text => err_text);
242 update bom_bill_of_mtls_interface set
243 process_flag = 3
244 where transaction_id = c1rec.TI;
245
246 if (ret_code <> 0) then
247 return(ret_code);
248 end if;
249 goto continue_loop;
250 end if;
251
252 stmt_num := 7; /* Check assembly type and BOM enabled flag */
253 ret_code :=bmvbitm_verify_assembly_type(
254 org_id => c1rec.OI,
255 assy_id => c1rec.AII,
256 assy_type => c1rec.AST,
257 err_text => err_text);
258 if (ret_code <> 0) then
259 ret_code := INVPUOPI.mtl_log_interface_err(
260 org_id => c1rec.OI,
261 user_id => user_id,
262 login_id => login_id,
263 prog_appid => prog_appid,
264 prog_id => prog_id,
265 req_id => request_id,
266 trans_id => c1rec.TI,
267 error_text => err_text,
268 tbl_name => 'BOM_BILL_OF_MTLS_INTERFACE',
269 msg_name => 'BOM_ASSY_TYPE_ERR',
270 err_text => err_text);
271 update bom_bill_of_mtls_interface set
272 process_flag = 3
273 where transaction_id = c1rec.TI;
274
275 if (ret_code <> 0) then
276 return(ret_code);
277 end if;
278 goto continue_loop;
279 end if;
280
281 stmt_num := 8;
282 if c1rec.BSI = c1rec.CBSI then
283 NULL;
284 else
285 ret_code :=bmvrbom_verify_bom( /* Check cmn bill seq id existence*/
286 bom_seq_id => c1rec.CBSI,
287 mode_type => 2,
288 err_text => err_text);
289 if (ret_code <> 0) then
290 ret_code := INVPUOPI.mtl_log_interface_err(
291 org_id => c1rec.OI,
292 user_id => user_id,
293 login_id => login_id,
294 prog_appid => prog_appid,
295 prog_id => prog_id,
296 req_id => request_id,
297 trans_id => c1rec.TI,
298 error_text => err_text,
299 tbl_name => 'BOM_BILL_OF_MTLS_INTERFACE',
300 msg_name => 'BOM_COMMON_BILL_NOT_EXIST',
301 err_text => err_text);
302 update bom_bill_of_mtls_interface set
303 process_flag = 3
304 where transaction_id = c1rec.TI;
305
306 if (ret_code <> 0) then
307 return(ret_code);
308 end if;
309 goto continue_loop;
310 end if;
311
312 stmt_num := 9; /* Verify common bill attributes */
313 ret_code :=bmvcmbom_verify_common_bom(
314 bom_id => c1rec.BSI,
315 cmn_bom_id => c1rec.CBSI,
316 bom_type => c1rec.AST,
317 item_id => c1rec.AII,
318 cmn_item_id => c1rec.CAII,
319 org_id => c1rec.OI,
320 cmn_org_id => c1rec.COI,
321 alt_desg => c1rec.ABD,
322 err_text => err_text);
323 if (ret_code <> 0) then
324 ret_code := INVPUOPI.mtl_log_interface_err(
325 org_id => c1rec.OI,
326 user_id => user_id,
327 login_id => login_id,
328 prog_appid => prog_appid,
329 prog_id => prog_id,
330 req_id => request_id,
331 trans_id => c1rec.TI,
332 error_text => err_text,
333 tbl_name => 'BOM_BILL_OF_MTLS_INTERFACE',
334 msg_name => 'BOM_COMMON_BOM_ERROR',
335 err_text => err_text);
336 update bom_bill_of_mtls_interface set
337 process_flag = 3
338 where transaction_id = c1rec.TI;
339
340 if (ret_code <> 0) then
341 return(ret_code);
342 end if;
343 goto continue_loop;
344 end if;
345 end if;
346
347 stmt_num := 10;
348 update bom_bill_of_mtls_interface
349 set process_flag = 4
350 where transaction_id = c1rec.TI;
351
352 <<continue_loop>>
353 NULL;
354 end loop;
355 commit;
356
357 if (commit_cnt < (500 - 1)) then
358 continue_loop := FALSE;
359 end if;
360
361 end loop;
362
363 return(0);
364
365 EXCEPTION
366 when others then
367 err_text := 'BOMPVALB(bmvbomh-' || stmt_num || ') ' || substrb(SQLERRM,1,60);
368 return(SQLCODE);
369 END bmvbomh_validate_bom_header;
370
371
372 /*---------------------- bmvassyid_verify_assembly_id -----------------------*/
373 /* NAME
374 bmvassyid_verify_assembly_id - verify assembly item id exists in item
375 master
376 DESCRIPTION
377 Verifies in MTL_SYSTEM_ITEMS if assembly item exists.
378
379 REQUIRES
380 org_id organization_id
381 assy_id assembly_item_id
382 err_text out buffer to return error message
383 MODIFIES
384 RETURNS
385 0 if successful
386 SQLCODE if error
387 NOTES
388 -----------------------------------------------------------------------------*/
389 FUNCTION bmvassyid_verify_assembly_id(
390 org_id NUMBER,
391 assy_id NUMBER,
392 err_text OUT VARCHAR2
393 )
394 return INTEGER
395 IS
396 cnt NUMBER := 0;
397 BEGIN
398 select inventory_item_id
399 into cnt
400 from mtl_system_items
401 where organization_id = org_id
402 and inventory_item_id = assy_id;
403 return(0);
404 EXCEPTION
405 when NO_DATA_FOUND then
406 err_text := 'BOMPVALB(bmvassyid): Assembly item does not exist';
407 return(9999);
408 when others then
409 err_text := 'BOMPVALB(bmvassyid) ' || substrb(SQLERRM,1,60);
410 return(SQLCODE);
411
412 END bmvassyid_verify_assembly_id;
413
414 /*--------------------------- bmvrbom_verify_bom ----------------------------*/
415 /* NAME
416 bmvrbom_verify_bom - verify for uniqueness or existence of bom
417 sequence id
418 DESCRIPTION
419 verifies if the given bom sequence id is unique in prod and
420 interface tables
421
422 REQUIRES
423 bom_sq_id bom_sequecne_id
424 mode_type 1 - verify uniqueness of bom
425 2 - verify existence of bom
426 err_text out buffer to return error message
427 MODIFIES
428 RETURNS
429 0 if successful
430 count of routings with same bom_sequence_id if any found
431 SQLCODE if error
432 NOTES
433 -----------------------------------------------------------------------------*/
434 FUNCTION bmvrbom_verify_bom(
435 bom_seq_id NUMBER,
436 mode_type NUMBER,
437 err_text OUT VARCHAR2
438 )
439 return INTEGER
440 IS
441 cnt NUMBER := 0;
442 NOT_UNIQUE EXCEPTION;
443 BEGIN
444 /*
445 ** first check in prod tables
446 */
447 BEGIN
448 select bill_sequence_id
449 into cnt
450 from bom_bill_of_materials
451 where bill_sequence_id = bom_seq_id;
452 if (mode_type = 2) then
453 return(0);
454 else
455 raise NOT_UNIQUE;
456 end if;
457 EXCEPTION
458 when NO_DATA_FOUND then
459 NULL;
460 when NOT_UNIQUE then
461 raise NOT_UNIQUE;
462 when others then
463 err_text := 'BOMPVALB(bmvrbom) ' || substrb(SQLERRM,1,60);
464 return(SQLCODE);
465 END;
466
467 /*
468 ** check in interface table
469 */
470 select count(*)
471 into cnt
472 from bom_bill_of_mtls_interface
473 where bill_sequence_id = bom_seq_id
474 and process_flag = 4;
475
476 if (cnt = 0) then
477 if (mode_type = 1) then
478 return(0);
479 else
480 raise NO_DATA_FOUND;
481 end if;
482 end if;
483
484 if (cnt > 0) then
485 if (mode_type = 1) then
486 raise NOT_UNIQUE;
487 else
488 return(0);
489 end if;
490 end if;
491
492 EXCEPTION
493 when NO_DATA_FOUND then
494 err_text := substrb('BOMPVALB(bmvrbom): Bill does not exist ' || SQLERRM,1,70);
495 return(9999);
496 when NOT_UNIQUE then
497 err_text := 'BOMPVALB(bmvrbom) ' || 'Duplicate bill sequence id';
498 return(9999);
499 when others then
500 err_text := 'BOMPVALB(bmvrbom) ' || substrb(SQLERRM,1,60);
501 return(SQLCODE);
502 END bmvrbom_verify_bom;
503
504 /*--------------------- bmvdupbom_verify_duplicate_bom ----------------------*/
505 /* NAME
506 bmvdupbom_verify_duplicate_bom - verify if there is another bom
507 with same alt.
508 DESCRIPTION
509 Verifies in the production and interface tables if bom with
510 same alt exists. Also verifies for an alternate bom, if the
511 primary already exists.
512
513 REQUIRES
514 org_id organization_id
515 assy_id assembly_item_id
516 alt_desg alternate routing designator
517 err_text out buffer to return error message
518 MODIFIES
519 RETURNS
520 0 if successful
521 cnt if bom already exists
522 9999 if primary does not exist
523 SQLCODE if error
524 NOTES
525 -----------------------------------------------------------------------------*/
526 FUNCTION bmvdupbom_verify_duplicate_bom(
527 org_id NUMBER,
528 assy_id NUMBER,
529 alt_desg VARCHAR2,
530 assy_type NUMBER,
531 err_text OUT VARCHAR2
532 )
533 return INTEGER
534 IS
535 cnt NUMBER := 0;
536 ALREADY_EXISTS EXCEPTION;
537 BEGIN
538 begin
539 select bill_sequence_id
540 into cnt
541 from bom_bill_of_materials
542 where organization_id = org_id
543 and assembly_item_id = assy_id
544 and nvl(alternate_bom_designator, 'NONE') =
545 nvl(alt_desg, 'NONE');
546 raise ALREADY_EXISTS;
547 exception
548 when ALREADY_EXISTS then
549 err_text := 'BOMPVALB(bmvdupbom): Bill already exists in production';
550 return(cnt);
551 when NO_DATA_FOUND then
552 NULL;
553 when others then
554 err_text := 'BOMPVALB(bmvdupbom) ' || substrb(SQLERRM,1,60);
555 return(SQLCODE);
556 end;
557
558 begin
559 select bill_sequence_id
560 into cnt
561 from bom_bill_of_mtls_interface
562 where organization_id = org_id
563 and assembly_item_id = assy_id
564 and nvl(alternate_bom_designator, 'NONE') =
565 nvl(alt_desg, 'NONE')
566 and rownum = 1
567 and process_flag = 4;
568
569 raise ALREADY_EXISTS;
570 exception
571 when ALREADY_EXISTS then
572 err_text := 'BOMPVALB(bmvdupbom): Bill already exists in interface';
573 return(cnt);
574 when NO_DATA_FOUND then
575 NULL;
576 when others then
577 err_text := 'BOMPVALB(bmvdupbom) ' || substrb(SQLERRM,1,60);
578 return(SQLCODE);
579 end;
580
581 /*
582 ** for alternate bills, verify if primary exists (or will exist)
583 ** Alternate mfg bills cannot have primary eng bills
584 */
585 if (alt_desg is not null) then
586 begin
587 select bill_sequence_id
588 into cnt
589 from bom_bill_of_materials
590 where organization_id = org_id
591 and assembly_item_id = assy_id
592 and alternate_bom_designator is null
593 and ((assy_type = 2)
594 or
595 (assy_type =1 and assembly_type = 1)
596 );
597 return(0);
598 exception
599 when NO_DATA_FOUND then
600 NULL;
601 when others then
602 err_text := 'BOMPVALB(bmvdupbom) ' || substrb(SQLERRM,1,60);
603 return(SQLCODE);
604 end;
605
606 begin
607 select bill_sequence_id
608 into cnt
609 from bom_bill_of_mtls_interface
610 where organization_id = org_id
611 and assembly_item_id = assy_id
612 and alternate_bom_designator is null
613 and ((assy_type = 2)
614 or
615 (assy_type =1 and assembly_type = 1)
616 )
617 and process_flag = 4
618 and rownum = 1;
619 exception
620 when NO_DATA_FOUND then
621 err_text := 'BOMPVALB(bmvdupbom): Valid primary does not exist';
622 return(9999);
623 when others then
624 err_text := 'BOMPVALB(bmvdupbom) ' || substrb(SQLERRM,1,60);
625 return(SQLCODE);
626 end;
627 end if;
628
629 return(0);
630
631 EXCEPTION
632 when others then
633 err_text := 'BOMPVALB(bmvdupbom) ' || substrb(SQLERRM,1,60);
634 return(SQLCODE);
635 END bmvdupbom_verify_duplicate_bom;
636
637
638 /*--------------------- bmvbitm_verify_assembly_type ----------------------*/
639 /* NAME
640 bmvbitm_verify_assembly_type - verify assembly type
641 DESCRIPTION
642 a bom can be defined only if the bom_enabled_flag = 'Y.
643 Also verifies if assembly_type = mfg, then item must also be mfg.
644
645 REQUIRES
646 assy_type assembly_type
647 org_id organization_id
648 assy_id assembly_item_id
649 err_text out buffer to return error message
650 MODIFIES
651 RETURNS
652 0 if successful
653 9999 if invalid item
654 SQLCODE if error
655 NOTES
656 -----------------------------------------------------------------------------*/
657 FUNCTION bmvbitm_verify_assembly_type(
658 org_id NUMBER,
659 assy_id NUMBER,
660 assy_type NUMBER,
661 err_text OUT VARCHAR2
662 )
663 return INTEGER
664 IS
665 cnt NUMBER := 0;
666 BEGIN
667 select 1
668 into cnt
669 from mtl_system_items
670 where organization_id = org_id
671 and inventory_item_id = assy_id
672 and bom_enabled_flag = 'Y'
673 and ((assy_type = 2)
674 or
675 (assy_type = 1 and
676 eng_item_flag = 'N')
677 );
678 return(0);
679
680 EXCEPTION
681 when NO_DATA_FOUND then
682 err_text := 'BOMPVALB(bmvbitm): Assembly type invalid or item not BOM enabled';
683 return(9999);
684 when others then
685 err_text := 'BOMPVALB(bmvbitm) ' || substrb(SQLERRM,1,60);
686 return(SQLCODE);
687
688 END bmvbitm_verify_assembly_type;
689
690 /*----------------------- bmvcmbom_verify_common_bom ------------------------*/
691 /* NAME
692 bmvcmbom_verify_common_bom - verify common_bom
693 DESCRIPTION
694 if bom is mfg then it cannot point to engineerging bom
695 if common bom then bill cannot have components
696 if inter-org common then all components items must be in both orgs
697 Common bill's org and current bill's org must have same master org
698 Common bill's alt must be same as current bill's alt
699 Common bill cannot have same assembly_item_id/org_id as current bill
700 Common bill cannot reference a common bill
701
702 REQUIRES
703 bom_id bill_sequence_id
704 cmn_bom_id common bill_seqience_id
705 bom_type assembly_type
706 item_id assembly item id
707 cmn_item_id common item id
708 org_id org id
709 cmn_org_id common org id
710 err_text out buffer to return error message
711 MODIFIES
712 RETURNS
713 0 if successful
714 9999 if invalid item
715 SQLCODE if error
716 NOTES
717 -----------------------------------------------------------------------------*/
718 FUNCTION bmvcmbom_verify_common_bom(
719 bom_id NUMBER,
720 cmn_bom_id NUMBER,
721 bom_type NUMBER,
722 item_id NUMBER,
723 cmn_item_id NUMBER,
724 org_id NUMBER,
725 cmn_org_id NUMBER,
726 alt_desg VARCHAR2,
727 err_text OUT VARCHAR2
728 )
729 return INTEGER
730 IS
731 cnt NUMBER;
732 bit NUMBER;
733 base_id NUMBER;
734 ato VARCHAR2(1);
735 pto VARCHAR2(1);
736 MISSING_ITEMS EXCEPTION;
737 MISSING_SUB_ITEMS EXCEPTION;
738
739 BEGIN
740 /*
741 ** Common bill's org and current bill's org must have same master org
742 */
743 begin
744 select 1
745 into cnt
746 from mtl_parameters mp1, mtl_parameters mp2
747 where mp1.organization_id = org_id
748 and mp2.organization_id = cmn_org_id
749 and mp1.master_organization_id = mp2.master_organization_id;
750 exception
751 when NO_DATA_FOUND then
752 err_text := 'BOMPVALB(bmvcmbom): Invalid common master org id';
753 return(9999);
754 when others then
755 err_text := 'BOMPVALB(bmvcmbom) ' || substrb(SQLERRM,1,60);
756 return(SQLCODE);
757 end;
758 /*
759 ** Common bill's alt must be same as current bill's alt
760 ** Common bill cannot have same assembly_item_id/org_id as current bill
761 ** Common bill must be mfg bill if current bill is a mfg bill
762 ** Common bill cannot reference a common bill
763 ** Common bill sequence id must have the correct common_assembly_item_id
764 ** and common_organization_id
765 */
766 begin
767 select bill_sequence_id
768 into cnt
769 from bom_bill_of_materials
770 where bill_sequence_id = cmn_bom_id
771 and assembly_item_id = cmn_item_id
772 and organization_id = cmn_org_id
773 and nvl(alternate_bom_designator, 'NONE') =
774 nvl(alt_desg, 'NONE')
775 and common_bill_sequence_id = bill_sequence_id
776 and (assembly_item_id <> item_id
777 or
778 organization_id <> org_id
779 )
780 and ((bom_type <> 1)
781 or
782 (bom_type = 1
783 and
784 assembly_type = 1
785 )
786 );
787 goto check_ops;
788 exception
789 when NO_DATA_FOUND then
790 NULL;
791 when others then
792 err_text := 'BOMPVALB(bmvmbom) ' || substrb(SQLERRM,1,60);
793 return(SQLCODE);
794 end;
795
796 select bill_sequence_id
797 into cnt
798 from bom_bill_of_mtls_interface
799 where bill_sequence_id = cmn_bom_id
800 and assembly_item_id = cmn_item_id
801 and organization_id = cmn_org_id
802 and nvl(alternate_bom_designator, 'NONE') =
803 nvl(alt_desg, 'NONE')
804 and common_bill_sequence_id = bill_sequence_id
805 and (assembly_item_id <> item_id
806 or
807 organization_id <> org_id
808 )
809 and process_flag = 4
810 and ((bom_type <> 1)
811 or
812 (bom_type = 1
813 and
814 assembly_type = 1
815 )
816 );
817
818 <<check_ops>>
819
820 /*
821 ** check to see if components exist in both orgs for inter-org commons
822 */
823 if (org_id <> cmn_org_id) then
824
825 /* Get item attributes for the bill */
826 select bom_item_type, base_item_id, replenish_to_order_flag,
827 pick_components_flag
828 into bit, base_id, ato, pto
829 from mtl_system_items
830 where inventory_item_id = item_id
831 and organization_id = org_id;
832
833 select count(*)
834 into cnt
835 from bom_inventory_components bic
836 where bic.bill_sequence_id = cmn_bom_id
837 and NOT EXISTS
838 (SELECT 'x'
839 FROM MTL_SYSTEM_ITEMS S
840 WHERE S.ORGANIZATION_ID = org_id
841 AND S.INVENTORY_ITEM_ID = bic.COMPONENT_ITEM_ID
842 AND ((bom_type = 1
843 AND S.ENG_ITEM_FLAG = 'N')
844 OR (bom_type = 2))
845 AND S.BOM_ENABLED_FLAG = 'Y'
846 AND S.INVENTORY_ITEM_ID <> item_id
847 AND ((bit = 1
848 AND S.BOM_ITEM_TYPE <> 3)
849 OR (bit = 2
850 AND S.BOM_ITEM_TYPE <> 3)
851 OR (bit = 3)
852 OR (bit = 4
853 AND (S.BOM_ITEM_TYPE = 4
854 OR (S.BOM_ITEM_TYPE IN (2, 1)
855 AND S.REPLENISH_TO_ORDER_FLAG = 'Y'
856 AND base_id IS NOT NULL
857 AND ato = 'Y')))
858 )
859 AND (bit = 3
860 OR
861 pto = 'Y'
862 OR
863 S.PICK_COMPONENTS_FLAG = 'N')
864 AND (bit = 3
865 OR
866 NVL(S.BOM_ITEM_TYPE, 4) <> 2
867 OR
868 (S.BOM_ITEM_TYPE = 2
869 AND ((pto = 'Y'
870 AND S.PICK_COMPONENTS_FLAG = 'Y')
871 OR (ato = 'Y'
872 AND S.REPLENISH_TO_ORDER_FLAG = 'Y'))))
873 AND NOT(bit = 4
874 AND pto = 'Y'
875 AND S.BOM_ITEM_TYPE = 4
876 AND S.REPLENISH_TO_ORDER_FLAG = 'Y')
877 );
878
879 if (cnt > 0) then
880 raise MISSING_ITEMS;
881 end if;
882
883 select count(*)
884 into cnt
885 from bom_inventory_comps_interface bic
886 where bill_sequence_id = cmn_bom_id
887 and process_flag in (2, 4)
888 and NOT EXISTS
889 (SELECT 'x'
890 FROM MTL_SYSTEM_ITEMS S
891 WHERE S.ORGANIZATION_ID = org_id
892 AND S.INVENTORY_ITEM_ID = bic.COMPONENT_ITEM_ID
893 AND ((bom_type = 1
894 AND S.ENG_ITEM_FLAG = 'N')
895 OR (bom_type = 2))
896 AND S.BOM_ENABLED_FLAG = 'Y'
897 AND S.INVENTORY_ITEM_ID <> item_id
898 AND ((bit = 1
899 AND S.BOM_ITEM_TYPE <> 3)
900 OR (bit = 2
901 AND S.BOM_ITEM_TYPE <> 3)
902 OR (bit = 3)
903 OR (bit = 4
904 AND (S.BOM_ITEM_TYPE = 4
905 OR (S.BOM_ITEM_TYPE IN (2, 1)
906 AND S.REPLENISH_TO_ORDER_FLAG = 'Y'
907 AND base_id IS NOT NULL
908 AND ato = 'Y')))
909 )
910 AND (bit = 3
911 OR
912 pto = 'Y'
913 OR
914 S.PICK_COMPONENTS_FLAG = 'N')
915 AND (bit = 3
916 OR
917 NVL(S.BOM_ITEM_TYPE, 4) <> 2
918 OR
919 (S.BOM_ITEM_TYPE = 2
920 AND ((pto = 'Y'
921 AND S.PICK_COMPONENTS_FLAG = 'Y')
922 OR (ato = 'Y'
923 AND S.REPLENISH_TO_ORDER_FLAG = 'Y'))))
924 AND NOT(bit = 4
925 AND pto = 'Y'
926 AND S.BOM_ITEM_TYPE = 4
927 AND S.REPLENISH_TO_ORDER_FLAG = 'Y')
928 );
929
930 if (cnt > 0) then
931 raise MISSING_ITEMS;
932 end if;
933
934 end if;
935 /*
936 ** check if substitute components exist in both orgs for inter-org commons
937 */
938 if (org_id <> cmn_org_id) then /* Comp and sub comp in production */
939 select count(*)
940 into cnt
941 from bom_inventory_components bic,
942 bom_substitute_components bsc
943 where bic.bill_sequence_id = cmn_bom_id
944 and bic.component_sequence_id = bsc.component_sequence_id
945 and bsc.substitute_component_id not in
946 (select msi1.inventory_item_id
947 from mtl_system_items msi1, mtl_system_items msi2
948 where msi1.organization_id = org_id
949 and msi1.inventory_item_id = bsc.substitute_component_id
950 and msi2.organization_id = cmn_org_id
951 and msi2.inventory_item_id = msi1.inventory_item_id);
952 if (cnt > 0) then
953 raise MISSING_SUB_ITEMS;
954 end if;
955
956 select count(*) /* Comp and sub comp in interface */
957 into cnt
958 from bom_inventory_comps_interface bic,
959 bom_sub_comps_interface bsc
960 where bic.bill_sequence_id = cmn_bom_id
961 and bic.process_flag in (2, 4)
962 and bsc.process_flag in (2, 4)
963 and bic.component_sequence_id = bsc.component_sequence_id
964 and bsc.substitute_component_id not in
965 (select msi1.inventory_item_id
966 from mtl_system_items msi1, mtl_system_items msi2
967 where msi1.organization_id = org_id
968 and msi1.inventory_item_id = bsc.substitute_component_id
969 and msi2.organization_id = cmn_org_id
970 and msi2.inventory_item_id = msi1.inventory_item_id);
971 if (cnt > 0) then
972 raise MISSING_SUB_ITEMS;
973 end if;
974
975 select count(*) /* Comp in production and sub comp in interface */
976 into cnt
977 from bom_inventory_components bic,
978 bom_sub_comps_interface bsc
979 where bic.bill_sequence_id = cmn_bom_id
980 and bsc.process_flag in (2, 4)
981 and bic.component_sequence_id = bsc.component_sequence_id
982 and bsc.substitute_component_id not in
983 (select msi1.inventory_item_id
984 from mtl_system_items msi1, mtl_system_items msi2
985 where msi1.organization_id = org_id
986 and msi1.inventory_item_id = bsc.substitute_component_id
987 and msi2.organization_id = cmn_org_id
988 and msi2.inventory_item_id = msi1.inventory_item_id);
989 if (cnt > 0) then
990 raise MISSING_SUB_ITEMS;
991 end if;
992
993
994 end if;
995
996 /*
997 ** check to see if bill item and common item have same bom_item_type,
998 ** pick_components_flag and replenish_to_order_flag
999 ** Common item must have bom_enabled_flag = 'Y'
1000 */
1001 begin
1002 select 1
1003 into cnt
1004 from mtl_system_items msi1, mtl_system_items msi2
1005 where msi1.organization_id = org_id
1006 and msi1.inventory_item_id = item_id
1007 and msi2.organization_id = cmn_org_id
1008 and msi2.inventory_item_id = cmn_item_id
1009 and msi2.bom_enabled_flag = 'Y'
1010 and msi1.bom_item_type = msi2.bom_item_type
1011 and msi1.pick_components_flag = msi2.pick_components_flag
1012 and msi1.replenish_to_order_flag = msi2.replenish_to_order_flag;
1013 exception
1014 when NO_DATA_FOUND then
1015 err_text := 'BOMPVALB(bmvcmbom): Invalid item attributes';
1016 return(9999);
1017 when others then
1018 err_text := 'BOMPVALB(bmvcmbom) ' || substrb(SQLERRM,1,60);
1019 return(SQLCODE);
1020 end;
1021
1022 return(0);
1023 EXCEPTION
1024 when NO_DATA_FOUND then
1025 err_text := 'BOMPVALB(bmvcmbom):Invalid common bill';
1026 return(9999);
1027 when MISSING_ITEMS then
1028 err_text := 'BOMPVALB(bmvcmbom): Component items not in both orgs or invalid';
1029 return(9999);
1030 when MISSING_SUB_ITEMS then
1031 err_text := 'BOMPVALB(bmvcmbom): Substitute items not in both orgs';
1032 return(9999);
1033 when others then
1034 err_text := 'BOMPVALB(bmvcmbom) ' || substrb(SQLERRM,1,60);
1035 return(SQLCODE);
1036 END bmvcmbom_verify_common_bom;
1037
1038 /*---------------------- bmvcomp_validate_components -----------------------*/
1039 /* NAME
1040 bmvcomp_validate_components - validate component data
1041 DESCRIPTION
1042 validate the component data in the interface tables before loading
1043 into production tables
1044 REQUIRES
1045 err_text out buffer to return error message
1046 MODIFIES
1047 MTL_INTERFACE_ERRORS
1048 RETURNS
1049 0 if successful
1050 SQLCODE if unsuccessful
1051 NOTES
1052 -----------------------------------------------------------------------------*/
1053 FUNCTION bmvcomp_validate_components (
1054 org_id NUMBER,
1055 all_org NUMBER := 2,
1056 user_id NUMBER,
1057 login_id NUMBER,
1058 prog_appid NUMBER,
1059 prog_id NUMBER,
1060 request_id NUMBER,
1061 err_text IN OUT VARCHAR2
1062 )
1063 return INTEGER
1064 IS
1065 ret_code NUMBER;
1066 ret_code_error exception; -- ret_code <> 0
1067 stmt_num NUMBER := 0;
1068 dummy VARCHAR2(50);
1069 eng_bill NUMBER;
1070 oe_install VARCHAR2(1);
1071 commit_cnt constant NUMBER := 500; -- commit every 500 rows
1072 inv_asst VARCHAR2(1);
1073 r_subinv NUMBER;
1074 r_loc NUMBER;
1075 loc_ctl NUMBER;
1076 org_loc NUMBER;
1077 sub_loc_code NUMBER;
1078 X_expense_to_asset_transfer NUMBER;
1079 continue_loop exception;
1080 write_loc_error exception;
1081 write_subinv_error exception;
1082 update_comp exception;
1083
1084 cursor c1 is
1085 select component_sequence_id CSI, bill_sequence_id BSI,
1086 transaction_id TI,
1087 to_char(effectivity_date,'YYYY/MM/DD HH24:MI') ED,
1088 effectivity_date EDD,
1089 to_char(disable_date,'YYYY/MM/DD HH24:MI') DD,
1090 to_char(implementation_date,'YYYY/MM/DD HH24:MI') ID,
1091 operation_seq_num OSN, supply_locator_id SLI,
1092 supply_subinventory SS,
1093 msic.organization_id OI, component_item_id CII,
1094 assembly_item_id AII, alternate_bom_designator ABD,
1095 planning_factor PF, optional O, check_atp CATP,
1096 msic.atp_flag AF, so_basis SB, required_for_revenue RFR,
1097 required_to_ship RTS, mutually_exclusive_options MEO,
1098 low_quantity LQ, high_quantity HQ,
1099 quantity_related QR, include_in_cost_rollup ICR,
1100 shipping_allowed SA, include_on_ship_docs ISD,
1101 component_yield_factor CYF, ici.wip_supply_type WST,
1102 component_quantity CQ, msic.bom_item_type BITC,
1103 msic.pick_components_flag PCF, msia.bom_item_type BITA,
1104 msia.pick_components_flag PCFA,
1105 msia.replenish_to_order_flag RTOF,
1106 msic.replenish_to_order_flag RTOFC,
1107 msia.atp_components_flag ACF,
1108 msic.ato_forecast_control AFC
1109 from mtl_system_items msic,
1110 mtl_system_items msia,
1111 bom_inventory_comps_interface ici
1112 where process_flag = 2
1113 and msic.organization_id = ici.organization_id
1114 and msia.organization_id = ici.organization_id
1115 and msic.inventory_item_id = ici.component_item_id
1116 and msia.inventory_item_id = ici.assembly_item_id;
1117
1118 BEGIN
1119 for c1rec in c1 loop
1120 Begin
1121 /*
1122 ** verify for uniqueness of component seq ID
1123 */
1124 stmt_num := 1;
1125 ret_code := BOMPVALB.bmvuncmp_verify_unique_comp (
1126 cmp_seq_id => c1rec.CSI,
1127 exist_flag => 2,
1128 err_text => err_text);
1129 if (ret_code <> 0) then
1130 ret_code := INVPUOPI.mtl_log_interface_err(
1131 org_id => c1rec.OI,
1132 user_id => user_id,
1133 login_id => login_id,
1134 prog_appid => prog_appid,
1135 prog_id => prog_id,
1136 req_id => request_id,
1137 trans_id => c1rec.TI,
1138 error_text => err_text,
1139 tbl_name => 'BOM_INVENTORY_COMPS_INTERFACE',
1140 msg_name => 'BOM_COMP_SEQ_ID_DUPLICATE',
1141 err_text => err_text);
1142 update bom_inventory_comps_interface set
1143 process_flag = 3
1144 where transaction_id = c1rec.TI;
1145
1146 if (ret_code <> 0) then
1147 raise ret_code_error;
1148 end if;
1149 raise continue_loop;
1150 end if;
1151 /*
1152 ** verify uniqueness of bill seq id,effective date,op seq, and component item
1153 */
1154 stmt_num := 2;
1155 ret_code := BOMPVALB.bmvdupcmp_verify_duplicate_cmp (
1156 bill_seq_id => c1rec.BSI,
1157 eff_date => c1rec.ED,
1158 cmp_item_id => c1rec.CII,
1159 op_seq => c1rec.OSN,
1160 err_text => err_text);
1161 if (ret_code <> 0) then
1162 ret_code := INVPUOPI.mtl_log_interface_err(
1163 org_id => c1rec.OI,
1164 user_id => user_id,
1165 login_id => login_id,
1166 prog_appid => prog_appid,
1167 prog_id => prog_id,
1168 req_id => request_id,
1169 trans_id => c1rec.TI,
1170 error_text => err_text,
1171 tbl_name => 'BOM_INVENTORY_COMPS_INTERFACE',
1172 msg_name => 'BOM_COMPONENT_DUPLICATE',
1173 err_text => err_text);
1174 update bom_inventory_comps_interface set
1175 process_flag = 3
1176 where transaction_id = c1rec.TI;
1177
1178 if (ret_code <> 0) then
1179 raise ret_code_error;
1180 end if;
1181 raise continue_loop;
1182 end if;
1183 /*
1184 ** check for existence of bill
1185 */
1186 stmt_num := 3;
1187 ret_code :=bmvrbom_verify_bom(
1188 bom_seq_id => c1rec.BSI,
1189 mode_type => 2,
1190 err_text => err_text);
1191 if (ret_code <> 0) then
1192 ret_code := INVPUOPI.mtl_log_interface_err(
1193 org_id => c1rec.OI,
1194 user_id => user_id,
1195 login_id => login_id,
1196 prog_appid => prog_appid,
1197 prog_id => prog_id,
1198 req_id => request_id,
1199 trans_id => c1rec.TI,
1200 error_text => err_text,
1201 tbl_name => 'BOM_INVENTORY_COMPS_INTERFACE',
1202 msg_name => 'BOM_BILL_SEQ_MISSING',
1203 err_text => err_text);
1204 update bom_inventory_comps_interface set
1205 process_flag = 3
1206 where transaction_id = c1rec.TI;
1207
1208 if (ret_code <> 0) then
1209 raise ret_code_error;
1210 end if;
1211 raise continue_loop;
1212 end if;
1213 /*
1214 ** make sure there is no overlapping components
1215 */
1216 stmt_num := 4;
1217 if (c1rec.ID is not null) then
1218 ret_code :=bmvovlap_verify_overlaps (
1219 bom_id => c1rec.BSI,
1220 op_num => c1rec.OSN,
1221 cmp_id => c1rec.CII,
1222 eff_date => c1rec.ED,
1223 dis_date => c1rec.DD,
1224 err_text => err_text);
1225 if (ret_code <> 0) then
1226 ret_code := INVPUOPI.mtl_log_interface_err(
1227 org_id => c1rec.OI,
1228 user_id => user_id,
1229 login_id => login_id,
1230 prog_appid => prog_appid,
1231 prog_id => prog_id,
1232 req_id => request_id,
1233 trans_id => c1rec.TI,
1234 error_text => err_text,
1235 tbl_name => 'BOM_INVENTORY_COMPS_INTERFACE',
1236 msg_name => 'BOM_IMPL_COMP_OVERLAP',
1237 err_text => err_text);
1238 update bom_inventory_comps_interface set
1239 process_flag = 3
1240 where transaction_id = c1rec.TI;
1241
1242 if (ret_code <> 0) then
1243 raise ret_code_error;
1244 end if;
1245 raise continue_loop;
1246 end if;
1247 end if;
1248 /*
1249 ** verify that the bill is not a common bill. If so it cannot have
1250 ** components
1251 */
1252 stmt_num := 5;
1253 begin
1254 select 'Is pointing to a common'
1255 into dummy
1256 from bom_bill_of_materials
1257 where bill_sequence_id = c1rec.BSI
1258 and common_bill_sequence_id <> c1rec.BSI;
1259
1260 ret_code := INVPUOPI.mtl_log_interface_err(
1261 org_id => c1rec.OI,
1262 user_id => user_id,
1263 login_id => login_id,
1264 prog_appid => prog_appid,
1265 prog_id => prog_id,
1266 req_id => request_id,
1267 trans_id => c1rec.TI,
1268 error_text => err_text,
1269 tbl_name => 'BOM_INVENTORY_COMPS_INTERFACE',
1270 msg_name => 'BOM_COMMON_COMP',
1271 err_text => err_text);
1272 update bom_inventory_comps_interface set
1273 process_flag = 3
1274 where transaction_id = c1rec.TI;
1275
1276 if (ret_code <> 0) then
1277 raise ret_code_error;
1278 end if;
1279 raise continue_loop;
1280 exception
1281 when NO_DATA_FOUND then
1282 NULL;
1283 end;
1284 begin
1285 select 'Is pointing to a common'
1286 into dummy
1287 from bom_bill_of_mtls_interface
1288 where bill_sequence_id = c1rec.BSI
1289 and process_flag = 4
1290 and common_bill_sequence_id <> c1rec.BSI;
1291
1292 ret_code := INVPUOPI.mtl_log_interface_err(
1293 org_id => c1rec.OI,
1294 user_id => user_id,
1295 login_id => login_id,
1296 prog_appid => prog_appid,
1297 prog_id => prog_id,
1298 req_id => request_id,
1299 trans_id => c1rec.TI,
1300 error_text => err_text,
1301 tbl_name => 'BOM_INVENTORY_COMPS_INTERFACE',
1302 msg_name => 'BOM_COMMON_COMP',
1303 err_text => err_text);
1304 update bom_inventory_comps_interface set
1305 process_flag = 3
1306 where transaction_id = c1rec.TI;
1307
1308 if (ret_code <> 0) then
1309 raise ret_code_error;
1310 end if;
1311 raise continue_loop;
1312
1313 exception
1314 when NO_DATA_FOUND then
1315 NULL;
1316 end;
1317
1318 /*
1319 ** verify the validity of item attributes
1320 */
1321 stmt_num := 6;
1322 declare
1323 Cursor CheckBOM is
1324 select assembly_type
1325 from bom_bill_of_materials
1326 where bill_sequence_id = c1rec.BSI;
1327 Cursor CheckInterface is
1328 select assembly_type
1329 from bom_bill_of_mtls_interface
1330 where bill_sequence_id = c1rec.BSI
1331 and process_flag = 4;
1332 begin
1333 eng_bill := null;
1334 For X_bill in CheckBOM loop
1335 eng_bill := X_Bill.assembly_type;
1336 End loop;
1337 If eng_bill is null then
1338 For X_Interface in CheckInterface loop
1339 eng_bill := X_Interface.assembly_type;
1340 End loop;
1341 End if;
1342 end;
1343
1344 stmt_num := 7;
1345 ret_code := BOMPVALB.bmvitmatt_verify_item_attr (
1346 org_id => c1rec.OI,
1347 cmp_id => c1rec.CII,
1348 eng_bill => eng_bill,
1349 assy_id => c1rec.AII,
1350 err_text => err_text);
1351 if (ret_code <> 0) then
1352 ret_code := INVPUOPI.mtl_log_interface_err(
1353 org_id => c1rec.OI,
1354 user_id => user_id,
1355 login_id => login_id,
1356 prog_appid => prog_appid,
1357 prog_id => prog_id,
1358 req_id => request_id,
1359 trans_id => c1rec.TI,
1360 error_text => err_text,
1361 tbl_name => 'BOM_INVENTORY_COMPS_INTERFACE',
1362 msg_name => 'BOM_INVALID_ITEM_ATTRIBUTES',
1363 err_text => err_text);
1364 update bom_inventory_comps_interface set
1365 process_flag = 3
1366 where transaction_id = c1rec.TI;
1367
1368 if (ret_code <> 0) then
1369 raise ret_code_error;
1370 end if;
1371 raise continue_loop;
1372 end if;
1373
1374 /*
1375 ** check for validity of operation sequences
1376 */
1377 stmt_num := 8;
1378 ret_code := BOMPVALB.bmvopseqs_valid_op_seqs (
1379 org_id => c1rec.OI,
1380 assy_id => c1rec.AII,
1381 alt_desg => c1rec.ABD,
1382 op_seq => c1rec.OSN,
1383 err_text => err_text);
1384 if (ret_code <> 0) then
1385 ret_code := INVPUOPI.mtl_log_interface_err(
1386 org_id => c1rec.OI,
1387 user_id => user_id,
1388 login_id => login_id,
1389 prog_appid => prog_appid,
1390 prog_id => prog_id,
1391 req_id => request_id,
1392 trans_id => c1rec.TI,
1393 error_text => err_text,
1394 tbl_name => 'BOM_INVENTORY_COMPS_INTERFACE',
1395 msg_name => 'BOM_INVALID_OP_SEQ',
1396 err_text => err_text);
1397 update bom_inventory_comps_interface set
1398 process_flag = 3
1399 where transaction_id = c1rec.TI;
1400
1401 if (ret_code <> 0) then
1402 raise ret_code_error;
1403 end if;
1404 raise continue_loop;
1405 end if;
1406 /*
1407 ** effectivity date check
1408 */
1409 stmt_num := 9;
1410 if (to_date(c1rec.ED,'YYYY/MM/DD HH24:MI') >
1411 to_date(c1rec.DD,'YYYY/MM/DD HH24:MI')) then
1412 ret_code := INVPUOPI.mtl_log_interface_err(
1413 org_id => c1rec.OI,
1414 user_id => user_id,
1415 login_id => login_id,
1416 prog_appid => prog_appid,
1417 prog_id => prog_id,
1418 req_id => request_id,
1419 trans_id => c1rec.TI,
1420 error_text => err_text,
1421 tbl_name => 'BOM_INVENTORY_COMPS_INTERFACE',
1422 msg_name => 'BOM_EFFECTIVE_DATE_ERR',
1423 err_text => err_text);
1424 update bom_inventory_comps_interface set
1425 process_flag = 3
1426 where transaction_id = c1rec.TI;
1427
1428 if (ret_code <> 0) then
1429 raise ret_code_error;
1430 end if;
1431 raise continue_loop;
1432 end if;
1433
1434 /*
1435 ** planning_factor can be <>100 only if
1436 ** assembly_item bom_item_type = planning bill or
1437 ** assembly_item bom_item_type = model/OC and component is optional or
1438 ** assembly_item bom_item_type = model/OC and component is mandatory and
1439 ** component's forecast control = Consume and derive
1440 */
1441 stmt_num := 10;
1442 if (c1rec.PF <> 100) then
1443 if (c1rec.BITA = 3 or
1444 ((c1rec.BITA = 1 or c1rec.BITA = 2) and c1rec.O = 1) or
1445 ((c1rec.BITA = 1 or c1rec.BITA = 2) and c1rec.O = 2 and
1446 c1rec.AFC = 2)
1447 ) then
1448 NULL;
1449 else
1450 err_text := 'Planning percentage must be 100';
1451 ret_code := INVPUOPI.mtl_log_interface_err(
1452 org_id => c1rec.OI,
1453 user_id => user_id,
1454 login_id => login_id,
1455 prog_appid => prog_appid,
1456 prog_id => prog_id,
1457 req_id => request_id,
1458 trans_id => c1rec.TI,
1459 error_text => err_text,
1460 tbl_name => 'BOM_INVENTORY_COMPS_INTERFACE',
1461 msg_name => 'BOM_PLANNING_FACTOR_ERR',
1462 err_text => err_text);
1463 update bom_inventory_comps_interface set
1464 process_flag = 3
1465 where transaction_id = c1rec.TI;
1466
1467 if (ret_code <> 0) then
1468 raise ret_code_error;
1469 end if;
1470 raise continue_loop;
1471 end if;
1472 end if;
1473 /*
1474 ** If component is an ATO Standard item and the bill is a PTO Model or
1475 ** PTO Option Class, then Optional must be Yes
1476 */
1477 stmt_num := 11;
1478 if (c1rec.BITC = 4 and c1rec.RTOFC = 'Y' and c1rec.BITA in (1,2)
1479 and c1rec.PCFA = 'Y' and c1rec.O = 2) then
1480 ret_code := INVPUOPI.mtl_log_interface_err(
1481 org_id => c1rec.OI,
1482 user_id => user_id,
1483 login_id => login_id,
1484 prog_appid => prog_appid,
1485 prog_id => prog_id,
1486 req_id => request_id,
1487 trans_id => c1rec.TI,
1488 error_text => err_text,
1489 tbl_name => 'BOM_INVENTORY_COMPS_INTERFACE',
1490 msg_name => 'BOM_OPTIONAL_ERR',
1491 err_text => err_text);
1492 update bom_inventory_comps_interface set
1493 process_flag = 3
1494 where transaction_id = c1rec.TI;
1495
1496 if (ret_code <> 0) then
1497 raise ret_code_error;
1498 end if;
1499 raise continue_loop;
1500 end if;
1501
1502 /*
1503 ** if planning bill then
1504 ** yield must be 1 and order entry values should be defaulted
1505 */
1506 stmt_num := 12;
1507 if (c1rec.BITA = 3) then
1508 update bom_inventory_comps_interface
1509 set component_yield_factor = 1,
1510 check_atp = 2,
1511 include_on_ship_docs = 2,
1512 so_basis = 2,
1513 mutually_exclusive_options = 2,
1514 required_to_ship = 2,
1515 required_for_revenue = 2,
1516 low_quantity = NULL,
1517 high_quantity = NULL
1518 where transaction_id = c1rec.TI;
1519 end if;
1520
1521 err_text := NULL;
1522 /*
1523 ** validate component details
1524 */
1525 stmt_num := 13;
1526 if (c1rec.QR not in (1,2)) then
1527 err_text := 'QUANTITY_RELATED must be 1 or 2';
1528 end if;
1529
1530 if (c1rec.SB not in (1,2)) then
1531 err_text := 'SO_BASIS must be 1 or 2';
1532 end if;
1533
1534 if (c1rec.O not in(1,2)) then
1535 err_text := 'OPTIONAL must be 1 or 2';
1536 end if;
1537
1538 if (c1rec.MEO not in(1,2)) then
1539 err_text := 'MUTUALLY_EXCLUSIVE_OPTIONS must be 1 or 2';
1540 end if;
1541
1542 if (c1rec.ICR not in(1,2)) then
1543 err_text := 'INCLUDE_IN_COST_ROLLUP must be 1 or 2';
1544 end if;
1545
1546 if (c1rec.CATP not in(1,2)) then
1547 err_text := 'CHECK_ATP must be 1 or 2';
1548 end if;
1549
1550 if (c1rec.RTS not in(1,2)) then
1551 err_text := 'REQUIRED_TO_SHIP must be 1 or 2';
1552 end if;
1553
1554 if (c1rec.RFR not in(1,2)) then
1555 err_text := 'REQUIRED_FOR_REVENUE must be 1 or 2';
1556 end if;
1557
1558 if (c1rec.ISD not in(1,2)) then
1559 err_text := 'INCLUDE_ON_SHIP_DOCS must be 1 or 2';
1560 end if;
1561
1562 if (c1rec.CATP = 1 and not(c1rec.ACF = 'Y' and c1rec.CQ > 0)) then
1563 err_text := 'Component cannot have ATP check';
1564 end if;
1565
1566 if (c1rec.BITA <> 1 and c1rec.BITA <> 2 and c1rec.O = 1) then
1567 err_text := 'Component cannot be optional';
1568 end if;
1569
1570 if (c1rec.BITC <> 2 and c1rec.SB = 1) then
1571 err_text := 'Basis must be None';
1572 end if;
1573
1574 if (c1rec.RTOF = 'Y' and c1rec.RFR = 1) then
1575 err_text := 'An ATO item cannot be required for revenue';
1576 end if;
1577
1578 if (c1rec.RTOF = 'Y' and c1rec.RTS = 1) then
1579 err_text := 'An ATO item cannot be required to ship';
1580 end if;
1581
1582 if (c1rec.MEO = 1 and c1rec.BITC <>2) then
1583 err_text := 'Component cannot be mutually exclusive';
1584 end if;
1585
1586 if (c1rec.LQ > c1rec.CQ) and (c1rec.LQ is not null) then
1587 err_text := 'Low quantity must be less than or equal to component quantity';
1588 end if;
1589
1590 if (c1rec.HQ < c1rec.CQ) and (c1rec.HQ is not null) then
1591 err_text := 'High quantity must be greater than or equal to component quantity';
1592 end if;
1593
1594 if (c1rec.CYF <> 1 and c1rec.BITC = 2) then
1595 err_text := 'Component yield factor must be 1';
1596 end if;
1597
1598 if (c1rec.CYF <= 0) then
1599 err_text := 'Component yield factor must be greater than zero';
1600 end if;
1601
1602 if (c1rec.BITC = 1 or c1rec.BITC = 2) and (c1rec.WST <> 6) then
1603 err_text := 'WIP supply type must be Phantom';
1604 end if;
1605
1606 if (((c1rec.CATP = 1) or (c1rec.QR = 1) or
1607 (c1rec.BITC = 2 and c1rec.PCF = 'Y'))
1608 and c1rec.CQ < 0) then
1609 err_text := 'Component quantity cannot be negative';
1610 end if;
1611
1612 if (c1rec.QR = 1) and (c1rec.CQ <> round(c1rec.CQ)) then
1613 err_text := 'Component quantity must be an integer value';
1614 end if;
1615
1616 /* check if Order Entry is installed */
1617
1618
1619 begin
1620 select distinct 'I'
1621 into oe_install
1622 from fnd_product_installations
1623 where application_id = 300
1624 and status = 'I';
1625
1626 if (oe_install = 'I') and (c1rec.CQ <> round(c1rec.CQ)) and
1627 (c1rec.PCFA = 'Y') then
1628 err_text := 'Component quantity must be an integer value';
1629 end if;
1630 exception
1631 when NO_DATA_FOUND then
1632 null;
1633 end;
1634
1635 if (err_text is not null) then
1636 ret_code := INVPUOPI.mtl_log_interface_err(
1637 org_id => c1rec.OI,
1638 user_id => user_id,
1639 login_id => login_id,
1640 prog_appid => prog_appid,
1641 prog_id => prog_id,
1642 req_id => request_id,
1643 trans_id => c1rec.TI,
1644 error_text => err_text,
1645 tbl_name => 'BOM_INVENTORY_COMPS_INTERFACE',
1646 msg_name => 'BOM_COMPONENT_ERROR',
1647 err_text => err_text);
1648 update bom_inventory_comps_interface set
1649 process_flag = 3
1650 where transaction_id = c1rec.TI;
1651
1652 if (ret_code <> 0) then
1653 raise ret_code_error;
1654 end if;
1655 raise continue_loop;
1656 end if;
1657
1658 /*
1659 ** Validate subinventory
1660 */
1661 if (c1rec.SLI is not null and c1rec.SS is null) then
1662 raise write_loc_error;
1663 end if;
1664
1665 if (c1rec.SLI is null and c1rec.SS is null) then
1666 raise update_comp;
1667 end if;
1668
1669 select inventory_asset_flag,restrict_subinventories_code,
1670 restrict_locators_code, location_control_code
1671 into inv_asst, r_subinv, r_loc, loc_ctl
1672 from mtl_system_items
1673 where inventory_item_id = c1rec.CII
1674 and organization_id = c1rec.OI;
1675 /*
1676 ** if item locator control is null, set to 1 (no loc control)
1677 */
1678 if (loc_ctl is null) then
1679 loc_ctl := 1;
1680 end if;
1681 /*
1682 ** if subinv is not restricted and locator is, then make
1683 ** locator unrestricted
1684 */
1685
1686 if (r_subinv = 2) and (r_loc = 1) then
1687 r_loc := 2;
1688 end if;
1689 /*
1690 ** Check if subinventory is valid
1691 */
1692
1693 /*
1694 ** get value of profile INV:EXPENSE_TO_ASSET_TRANSFER
1695 */
1696 BOMPRFIL.bom_pr_get_profile(
1697 appl_short_name => 'INV',
1698 profile_name => 'INV:EXPENSE_TO_ASSET_TRANSFER',
1699 user_id => user_id,
1700 resp_appl_id => prog_appid,
1701 resp_id => 401,
1702 profile_value => X_expense_to_asset_transfer,
1703 return_code => ret_code,
1704 return_message => err_text);
1705 if (ret_code <> 0) then
1706 return(ret_code);
1707 end if;
1708
1709 if (r_subinv = 2) then /* non-restricted subinventory */
1710 IF (X_expense_to_asset_transfer = 1) THEN
1711 begin
1712 select locator_type
1713 into sub_loc_code
1714 from mtl_secondary_inventories
1715 where secondary_inventory_name = c1rec.SS
1716 and organization_id = c1rec.OI
1717 and nvl(disable_date,TRUNC(c1rec.EDD)+1) > TRUNC(c1rec.EDD)
1718 and quantity_tracked = 1;
1719 exception
1720 when no_data_found then
1721 raise write_subinv_error;
1722 end;
1723 ELSE
1724 begin
1725 select locator_type
1726 into sub_loc_code
1727 from mtl_secondary_inventories
1728 where secondary_inventory_name = c1rec.SS
1729 and organization_id = c1rec.OI
1730 and nvl(disable_date,TRUNC(c1rec.EDD)+1) > TRUNC(c1rec.EDD)
1731 and quantity_tracked = 1
1732 and ((inv_asst = 'Y' and asset_inventory = 1)
1733 or
1734 (inv_asst = 'N')
1735 );
1736 exception
1737 when no_data_found then
1738 raise write_subinv_error;
1739 end;
1740 END IF;
1741 else /* restricted subinventory */
1742 IF (X_expense_to_asset_transfer = 1) THEN
1743 begin
1744 select locator_type
1745 into sub_loc_code
1746 from mtl_secondary_inventories sub,
1747 mtl_item_sub_inventories item
1748 where item.organization_id = sub.organization_id
1749 and item.secondary_inventory = sub.secondary_inventory_name
1750 and item.inventory_item_id = c1rec.CII
1751 and sub.secondary_inventory_name = c1rec.SS
1752 and sub.organization_id = c1rec.OI
1753 and nvl(sub.disable_date,TRUNC(c1rec.EDD)+1) >
1754 TRUNC(c1rec.EDD)
1755 and sub.quantity_tracked = 1;
1756 exception
1757 when no_data_found then
1758 raise write_subinv_error;
1759 end;
1760 ELSE
1761 begin
1762 select locator_type
1763 into sub_loc_code
1764 from mtl_secondary_inventories sub,
1765 mtl_item_sub_inventories item
1766 where item.organization_id = sub.organization_id
1767 and item.secondary_inventory = sub.secondary_inventory_name
1768 and item.inventory_item_id = c1rec.CII
1769 and sub.secondary_inventory_name = c1rec.SS
1770 and sub.organization_id = c1rec.OI
1771 and nvl(sub.disable_date,TRUNC(c1rec.EDD)+1) >
1772 TRUNC(c1rec.EDD)
1773 and sub.quantity_tracked = 1
1774 and ((inv_asst = 'Y' and sub.asset_inventory = 1)
1775 or
1776 (inv_asst = 'N')
1777 );
1778 exception
1779 when no_data_found then
1780 raise write_subinv_error;
1781 end;
1782 END IF;
1783 end if;
1784 /*
1785 ** Validate locator
1786 */
1787 /* Org level */
1788 select stock_locator_control_code
1789 into org_loc
1790 from mtl_parameters
1791 where organization_id = c1rec.OI;
1792
1793 if (org_loc = 1) and (c1rec.SLI is not null) then
1794 raise write_loc_error;
1795 end if;
1796
1797 if ((org_loc = 2) or (org_loc = 3))and (c1rec.SLI is null) then
1798 raise write_loc_error;
1799 end if;
1800
1801 if ((org_loc = 2) or (org_loc = 3)) and (c1rec.SLI is not null) then
1802 if (r_loc = 2) then /* non-restricted locator */
1803 begin
1804 select 'loc exists'
1805 into dummy
1806 from mtl_item_locations
1807 where inventory_location_id = c1rec.SLI
1808 and organization_id = c1rec.OI
1809 and subinventory_code = c1rec.SS
1810 and nvl(disable_date,trunc(c1rec.EDD)+1) > trunc(c1rec.EDD);
1811 exception
1812 when no_data_found then
1813 raise write_loc_error;
1814 end;
1815 else /* restricted locator */
1816 begin
1817 select 'restricted loc exists'
1818 into dummy
1819 from mtl_item_locations loc,
1820 mtl_secondary_locators item
1821 where loc.inventory_location_id = c1rec.SLI
1822 and loc.organization_id = c1rec.OI
1823 and loc.subinventory_code = c1rec.SS
1824 and nvl(loc.disable_date,trunc(c1rec.EDD)+1) >
1825 trunc(c1rec.EDD)
1826 and loc.inventory_location_id = item.secondary_locator
1827 and loc.organization_id = item.organization_id
1828 and item.inventory_item_id = c1rec.CII;
1829 exception
1830 when no_data_found then
1831 raise write_loc_error;
1832 end;
1833 end if;
1834 end if;
1835
1836 if (org_loc not in (1,2,3,4) and c1rec.SLI is not null) then
1837 raise write_loc_error;
1838 end if;
1839
1840 /* Subinv level */
1841 if (org_loc = 4 and sub_loc_code = 1 and c1rec.SLI is not null) then
1842 raise write_loc_error;
1843 end if;
1844
1845 if (org_loc = 4) then
1846 if ((sub_loc_code = 2) or (sub_loc_code = 3))
1847 and (c1rec.SLI is null) then
1848 raise write_loc_error;
1849 end if;
1850
1851 if ((sub_loc_code = 2) or (sub_loc_code = 3))
1852 and (c1rec.SLI is not null) then
1853 if (r_loc = 2) then /* non-restricted locator */
1854 begin
1855 select 'loc exists'
1856 into dummy
1857 from mtl_item_locations
1858 where inventory_location_id = c1rec.SLI
1859 and organization_id = c1rec.OI
1860 and subinventory_code = c1rec.SS
1861 and nvl(disable_date,trunc(c1rec.EDD)+1) >
1862 trunc(c1rec.EDD);
1863 exception
1864 when no_data_found then
1865 raise write_loc_error;
1866 end;
1867 else /* restricted locator */
1868 begin
1869 select 'restricted loc exists'
1870 into dummy
1871 from mtl_item_locations loc,
1872 mtl_secondary_locators item
1873 where loc.inventory_location_id = c1rec.SLI
1874 and loc.organization_id = c1rec.OI
1875 and loc.subinventory_code = c1rec.SS
1876 and nvl(loc.disable_date,trunc(c1rec.EDD)+1) >
1877 trunc(c1rec.EDD)
1878 and loc.inventory_location_id = item.secondary_locator
1879 and loc.organization_id = item.organization_id
1880 and item.inventory_item_id = c1rec.CII;
1881 exception
1882 when no_data_found then
1883 raise write_loc_error;
1884 end;
1885 end if;
1886 end if;
1887
1888 if (sub_loc_code not in (1,2,3,5) and c1rec.SLI is not null) then
1889 raise write_loc_error;
1890 end if;
1891 end if;
1892
1893 /* Item level */
1894 if (org_loc = 4 and sub_loc_code = 5 and loc_ctl = 1
1895 and c1rec.SLI is not null) then
1896 raise write_loc_error;
1897 end if;
1898
1899 if (org_loc = 4 and sub_loc_code = 5) then
1900 if ((loc_ctl = 2) or (loc_ctl = 3))
1901 and (c1rec.SLI is null) then
1902 raise write_loc_error;
1903 end if;
1904
1905 if ((loc_ctl = 2) or (loc_ctl = 3))
1906 and (c1rec.SLI is not null) then
1907 if (r_loc = 2) then /* non-restricted locator */
1908 begin
1909 select 'loc exists'
1910 into dummy
1911 from mtl_item_locations
1912 where inventory_location_id = c1rec.SLI
1913 and organization_id = c1rec.OI
1914 and subinventory_code = c1rec.SS
1915 and nvl(disable_date,trunc(c1rec.EDD)+1) >
1916 trunc(c1rec.EDD);
1917 exception
1918 when no_data_found then
1919 raise write_loc_error;
1920 end;
1921 else /* restricted locator */
1922 begin
1923 select 'restricted loc exists'
1924 into dummy
1925 from mtl_item_locations loc,
1926 mtl_secondary_locators item
1927 where loc.inventory_location_id = c1rec.SLI
1928 and loc.organization_id = c1rec.OI
1929 and loc.subinventory_code = c1rec.SS
1930 and nvl(loc.disable_date,trunc(c1rec.EDD)+1) >
1931 trunc(c1rec.EDD)
1932 and loc.inventory_location_id = item.secondary_locator
1933 and loc.organization_id = item.organization_id
1934 and item.inventory_item_id = c1rec.CII;
1935 exception
1936 when no_data_found then
1937 raise write_loc_error;
1938 end;
1939 end if;
1940 end if;
1941
1942 if (loc_ctl not in (1,2,3) and c1rec.SLI is not null) then
1943 raise write_loc_error;
1944 end if;
1945 end if;
1946
1947 raise update_comp;
1948
1949 if mod(c1%rowcount, commit_cnt) = 0 then
1950 commit;
1951 -- dbms_output.put_line('Validate Component commited at row '||
1952 -- to_char(c1%rowcount));
1953 end if;
1954
1955 Exception
1956 when write_loc_error then
1957 ret_code := INVPUOPI.mtl_log_interface_err(
1958 org_id => org_id,
1959 user_id => user_id,
1960 login_id => login_id,
1961 prog_appid => prog_appid,
1962 prog_id => prog_id,
1963 req_id => request_id,
1964 trans_id => c1rec.TI,
1965 error_text => err_text,
1966 tbl_name => 'BOM_INVENTORY_COMPS_INTERFACE',
1967 msg_name => 'BOM_LOCATOR_INVALID',
1968 err_text => err_text);
1969 update bom_inventory_comps_interface set
1970 process_flag = 3
1971 where transaction_id = c1rec.TI;
1972
1973 if (ret_code <> 0) then
1974 raise ret_code_error;
1975 end if;
1976
1977 when write_subinv_error then
1978 ret_code := INVPUOPI.mtl_log_interface_err(
1979 org_id => org_id,
1980 user_id => user_id,
1981 login_id => login_id,
1982 prog_appid => prog_appid,
1983 prog_id => prog_id,
1984 req_id => request_id,
1985 trans_id => c1rec.TI,
1986 error_text => err_text,
1987 tbl_name => 'BOM_INVENTORY_COMPS_INTERFACE',
1988 msg_name => 'BOM_SUBINV_INVALID',
1989 err_text => err_text);
1990 update bom_inventory_comps_interface set
1991 process_flag = 3
1992 where transaction_id = c1rec.TI;
1993
1994 if (ret_code <> 0) then
1995 raise ret_code_error;
1996 end if;
1997
1998 when update_comp then
1999 update bom_inventory_comps_interface
2000 set process_flag = 4
2001 where transaction_id = c1rec.TI;
2002
2003 when continue_loop then
2004 if mod(c1%rowcount, commit_cnt) = 0 then
2005 commit;
2006 -- dbms_output.put_line('Validate Component commited at row '||
2007 -- to_char(c1%rowcount));
2008 end if;
2009 end; -- each component
2010 end loop; -- cursor
2011
2012 commit;
2013 return(0);
2014
2015 EXCEPTION
2016 when ret_code_error then
2017 return(ret_code);
2018 when others then
2019 err_text := 'BOMPVALB(bmvcomp-' || stmt_num || ') ' || substrb(SQLERRM,1,60);
2020 return(SQLCODE);
2021 END bmvcomp_validate_components;
2022
2023 /*------------------------ bmvitmatt_verify_item_attr -----------------------*/
2024 /* NAME
2025 bmvitmatt_verify_item_attr - verify if the item attributes
2026 of component item and assembly_item are compatible
2027 DESCRIPTION
2028 Component must be bom enabled
2029 Component item cannot be same as assembly item
2030 Mfg bills must have mfg component items
2031
2032 Component Types
2033
2034 Bill PTO ATO PTO ATO ATO PTO Standard
2035 Types Model Model OC OC Planning Item Item Item
2036 ------------- ------------------------------------------------------------
2037 PTO Model Yes Yes Yes No No Yes Yes Yes
2038 ATO Model No Yes No Yes No Yes No Yes
2039 PTO OC Yes Yes Yes No No Yes Yes Yes
2040 ATO OC No Yes No Yes No Yes No Yes
2041 Planning Yes Yes Yes Yes Yes Yes Yes Yes
2042 ATO Item No No No No No Yes No Yes
2043 PTO Item No No No No No No Yes Yes
2044 Standard Item No No No No No Yes No Yes
2045 Config Item No Yes No Yes No Yes No Yes
2046
2047 REQUIRES
2048 org_id organization id
2049 cmp_id component item id
2050 assy_id assembly item id
2051 eng_bill engineering bill (1=no, 2=yes)
2052 err_text out buffer to return error message
2053 MODIFIES
2054 MTL_INTERFACE_ERRORS
2055 RETURNS
2056 0 if successful
2057 SQLCODE if unsuccessful
2058 NOTES
2059 -----------------------------------------------------------------------------*/
2060 FUNCTION bmvitmatt_verify_item_attr (
2061 org_id NUMBER,
2062 cmp_id NUMBER,
2063 assy_id NUMBER,
2064 eng_bill NUMBER,
2065 err_text OUT VARCHAR2
2066 )
2067 return INTEGER
2068 IS
2069 ret_code NUMBER;
2070 stmt_num NUMBER := 0;
2071 dummy NUMBER;
2072
2073 BEGIN
2074 select 1
2075 into dummy
2076 from mtl_system_items assy, mtl_system_items comp
2077 where comp.organization_id = org_id
2078 and assy.organization_id = org_id
2079 and comp.inventory_item_id = cmp_id
2080 and assy.inventory_item_id = assy_id
2081 and comp.bom_enabled_flag = 'Y'
2082 and comp.inventory_item_id <> assy.inventory_item_id
2083 and ((eng_bill = 1 and comp.eng_item_flag = 'N')
2084 or
2085 (eng_bill = 2))
2086 and ((assy.bom_item_type = 1 and comp.bom_item_type <> 3)
2087 or
2088 (assy.bom_item_type = 2 and comp.bom_item_type <> 3)
2089 or
2090 (assy.bom_item_type = 3)
2091 or
2092 (assy.bom_item_type = 4
2093 and (comp.bom_item_type = 4
2094 or (comp.bom_item_type in (2,1)
2095 and comp.replenish_to_order_flag = 'Y'
2096 and assy.base_item_id is not null
2097 and assy.replenish_to_order_flag = 'Y')))
2098 )
2099 and (assy.bom_item_type = 3
2100 or
2101 assy.pick_components_flag = 'Y'
2102 or
2103 comp.pick_components_flag = 'N')
2104 and (assy.bom_item_type = 3
2105 or
2106 comp.bom_item_type <> 2
2107 or
2108 (comp.bom_item_type = 2
2109 and ((assy.pick_components_flag = 'Y'
2110 and comp.pick_components_flag = 'Y')
2111 or (assy.replenish_to_order_flag = 'Y'
2112 and comp.replenish_to_order_flag = 'Y'))))
2113 and not(assy.bom_item_type = 4
2114 and assy.pick_components_flag = 'Y'
2115 and comp.bom_item_type = 4
2116 and comp.replenish_to_order_flag = 'Y');
2117
2118 begin
2119 select 1
2120 into dummy
2121 from mtl_system_items assy, mtl_system_items comp
2122 where comp.organization_id = org_id
2123 and assy.organization_id = org_id
2124 and comp.inventory_item_id = cmp_id
2125 and assy.inventory_item_id = assy_id
2126 and (comp.atp_components_flag = 'Y' or
2127 comp.atp_flag = 'Y')
2128 and assy.atp_components_flag = 'N'
2129 and (nvl(assy.wip_supply_type,1) = 6
2130 or assy.replenish_to_order_flag = 'Y'
2131 or assy.pick_components_flag = 'Y');
2132 err_text := 'Component ATP flag item attributes invalid';
2133 return(9999);
2134 exception
2135 when NO_DATA_FOUND then
2136 return(0);
2137 end;
2138
2139 EXCEPTION
2140 when NO_DATA_FOUND then
2141 err_text := 'Component and assembly item attributes invalid';
2142 return(9999);
2143 when others then
2144 err_text := 'bmvitmatt: ' || substrb(SQLERRM,1,60);
2145 return(SQLCODE);
2146 END bmvitmatt_verify_item_attr;
2147
2148 /*-------------------------- bmvopseqs_valid_op_seqs ------------------------*/
2149 /* NAME
2150 bmvopseqs_valid_op_seqs - validate the operation seq nums
2151 DESCRIPTION
2152 verify if op seq is valid. For alternate bills, op seq can be of same
2153 alternate or primary if alternate does not exist
2154 REQUIRES
2155 err_text out buffer to return error message
2156 MODIFIES
2157 MTL_INTERFACE_ERRORS
2158 RETURNS
2159 0 if successful
2160 SQLCODE if unsuccessful
2161 NOTES
2162 -----------------------------------------------------------------------------*/
2163 FUNCTION bmvopseqs_valid_op_seqs (
2164 org_id NUMBER,
2165 assy_id NUMBER,
2166 alt_desg VARCHAR2,
2167 op_seq NUMBER,
2168 err_text OUT VARCHAR2
2169 )
2170 return INTEGER
2171 IS
2172 ret_code NUMBER;
2173 stmt_num NUMBER := 0;
2174 dummy NUMBER;
2175
2176 BEGIN
2177 select bom_item_type
2178 into dummy
2179 from mtl_system_items
2180 where organization_id = org_id
2181 and inventory_item_id = assy_id;
2182
2183 if dummy = 3 and op_seq <> 1 then
2184 err_text := 'Planning bom cannot have routing';
2185 return (9999);
2186 end if;
2187
2188 if (op_seq <> 1) then
2189 select operation_seq_num
2190 into dummy
2191 from bom_operation_sequences a, bom_operational_routings b
2192 where b.organization_id = org_id
2193 and b.assembly_item_id = assy_id
2194 and operation_seq_num = op_seq
2195 and b.common_routing_sequence_id = a.routing_sequence_id
2196 and ( (alt_desg is null and b.alternate_routing_designator is null)
2197 or
2198 (alt_desg is not null
2199 and
2200 ( (b.alternate_routing_designator = alt_desg)
2201 or
2202 (b.alternate_routing_designator is null
2203 and not exists (select 'No alt routing'
2204 from bom_operational_routings c
2205 where c.organization_id = org_id
2206 and c.assembly_item_id = assy_id
2207 and c.alternate_routing_designator = alt_desg)
2208 )
2209 )
2210 )
2211 );
2212 end if;
2213 return (0);
2214
2215 EXCEPTION
2216 when NO_DATA_FOUND then
2217 err_text := 'Invalid operation seq num';
2218 return (9999);
2219 when others then
2220 err_text := 'bmvopseqs: ' || substrb(SQLERRM,1,60);
2221 return(SQLCODE);
2222 END bmvopseqs_valid_op_seqs;
2223
2224 /*------------------------ bmvovlap_verify_overlaps -------------------------*/
2225 /* NAME
2226 bmvovlap_verify_overlaps - verify component overlaps
2227 DESCRIPTION
2228 verify the current component does not have overlapping effectivity
2229 REQUIRES
2230 bom_id bill sequence id
2231 op_num operation sequence number
2232 cmp_id component item id
2233 eff_date effectivity date
2234 dis_date disable date
2235 err_text out buffer to return error message
2236 MODIFIES
2237 RETURNS
2238 0 if successful
2239 SQLCODE if unsuccessful
2240 NOTES
2241 -----------------------------------------------------------------------------*/
2242 FUNCTION bmvovlap_verify_overlaps (
2243 bom_id NUMBER,
2244 op_num NUMBER,
2245 cmp_id NUMBER,
2246 eff_date VARCHAR2,
2247 dis_date VARCHAR2,
2248 err_text OUT VARCHAR2
2249 )
2250 return INTEGER
2251 IS
2252 dummy NUMBER;
2253 OVERLAP EXCEPTION;
2254 BEGIN
2255 select count(*)
2256 into dummy
2257 from bom_inventory_components
2258 where bill_sequence_id = bom_id
2259 and component_item_id = cmp_id
2260 and operation_seq_num = op_num
2261 and implementation_date is not null
2262 and ((dis_date is null
2263 and to_date(eff_date,'YYYY/MM/DD HH24:MI') <
2264 nvl(disable_date, to_date(eff_date,'YYYY/MM/DD HH24:MI') +1))
2265 or
2266 (dis_date is not null
2267 and to_date(dis_date,'YYYY/MM/DD HH24:MI') > effectivity_date
2268 and to_date(eff_date,'YYYY/MM/DD HH24:MI') <
2269 nvl(disable_date,to_date(eff_date,'YYYY/MM/DD HH24:MI') +1))
2270 );
2271 if (dummy <> 0) then
2272 raise OVERLAP;
2273 end if;
2274
2275 select count(*)
2276 into dummy
2277 from bom_inventory_comps_interface
2278 where bill_sequence_id = bom_id
2279 and process_flag = 4
2280 and component_item_id = cmp_id
2281 and operation_seq_num = op_num
2282 and implementation_date is not null
2283 and ((dis_date is null
2284 and to_date(eff_date,'YYYY/MM/DD HH24:MI') <
2285 nvl(disable_date, to_date(eff_date,'YYYY/MM/DD HH24:MI') +1))
2286 or
2287 (dis_date is not null
2288 and to_date(dis_date,'YYYY/MM/DD HH24:MI') > effectivity_date
2289 and to_date(eff_date,'YYYY/MM/DD HH24:MI') <
2290 nvl(disable_date,to_date(eff_date,'YYYY/MM/DD HH24:MI') +1))
2291 );
2292 if (dummy <> 0) then
2293 raise OVERLAP;
2294 end if;
2295
2296 return(0);
2297 EXCEPTION
2298 when OVERLAP then
2299 err_text := 'Component causes overlapping effectivity';
2300 return(9999);
2301 when others then
2302 err_text := 'bmvovlap: ' || substrb(SQLERRM,1,60);
2303 return (SQLCODE);
2304 END bmvovlap_verify_overlaps;
2305
2306 /*---------------------- bmvuncmp_verify_unique_comp ------------------------*/
2307 /* NAME
2308 bmvuncmp_verify_unique_comp - verify uniqueness or existence
2309 of comp seq id
2310 DESCRIPTION
2311 verifies if the given component sequence id is unique in prod and
2312 interface tables
2313 REQUIRES
2314 cmp_seq_id component sequence id
2315 exist_flag 1 - check for existence
2316 2 - check for uniqueness
2317 err_text out buffer to return error message
2318 MODIFIES
2319 RETURNS
2320 0 if successful
2321 SQLCODE if unsuccessful
2322 NOTES
2323 -----------------------------------------------------------------------------*/
2324 FUNCTION bmvuncmp_verify_unique_comp (
2325 cmp_seq_id NUMBER,
2326 exist_flag NUMBER,
2327 err_text OUT VARCHAR2
2328 )
2329 return INTEGER
2330 IS
2331 dummy NUMBER;
2332 NOT_UNIQUE EXCEPTION;
2333 BEGIN
2334 /*
2335 ** first check in prod tables
2336 */
2337 begin
2338 select 1
2339 into dummy
2340 from bom_inventory_components
2341 where component_sequence_id = cmp_seq_id;
2342 if (exist_flag = 1) then
2343 return(0);
2344 else
2345 raise NOT_UNIQUE;
2346 end if;
2347 exception
2348 when NO_DATA_FOUND then
2349 null;
2350 when NOT_UNIQUE then
2351 raise NOT_UNIQUE;
2352 when others then
2353 err_text := 'BOMPVALB(bmvuncmp) ' || substrb(SQLERRM,1,60);
2354 return(SQLCODE);
2355 end;
2356
2357 /*
2358 ** check in interface table
2359 */
2360 select count(*)
2361 into dummy
2362 from bom_inventory_comps_interface
2363 where component_sequence_id = cmp_seq_id
2364 and process_flag = 4;
2365
2366 if (dummy = 0) then
2367 if (exist_flag = 2) then
2368 return(0);
2369 else
2370 raise NO_DATA_FOUND;
2371 end if;
2372 end if;
2373
2374 if (dummy > 0) then
2375 if (exist_flag = 2) then
2376 raise NOT_UNIQUE;
2377 else
2378 return(0);
2379 end if;
2380 end if;
2381
2382 EXCEPTION
2383 when NO_DATA_FOUND then
2384 err_text := substrb('BOMPVALB(bmvuncmp): Component does not exist ' || SQLERRM,1,70);
2385 return(9999);
2386 when NOT_UNIQUE then
2387 err_text := 'BOMPVALB(bmvuncmp) ' ||'Duplicate component sequence ids';
2388 return(9999);
2389 when others then
2390 err_text := 'BOMPVALB(bmvuncmp) ' || substrb(SQLERRM,1,60);
2391 return(SQLCODE);
2392 END bmvuncmp_verify_unique_comp;
2393
2394 /*--------------------- bmvdupcmp_verify_duplicate_cmp ----------------------*/
2395 /* NAME
2396 bmvdupcmp_verify_duplicate_cmp - verify if there is another component
2397 with the same bill, effective date, and operation seq num.
2398 DESCRIPTION
2399 Verifies in the production and interface tables if component with
2400 the same bill, effective date, and operation seq num exists.
2401
2402 REQUIRES
2403 bill_seq_id bill sequence id
2404 eff_date effectivity date
2405 cmp_item_id component item id
2406 op_seq operation seq
2407 err_text out buffer to return error message
2408 MODIFIES
2409 RETURNS
2410 0 if successful
2411 cnt if component already exists
2412 SQLCODE if error
2413 NOTES
2414 -----------------------------------------------------------------------------*/
2415 FUNCTION bmvdupcmp_verify_duplicate_cmp(
2416 bill_seq_id NUMBER,
2417 eff_date VARCHAR2,
2418 cmp_item_id NUMBER,
2419 op_seq NUMBER,
2420 err_text OUT VARCHAR2
2421 )
2422 return INTEGER
2423 IS
2424 cnt NUMBER := 0;
2425 ALREADY_EXISTS EXCEPTION;
2426 BEGIN
2427 begin
2428 select component_sequence_id
2429 into cnt
2430 from bom_inventory_components
2431 where bill_sequence_id = bill_seq_id
2432 and to_char(effectivity_date,'YYYY/MM/DD HH24:MI') = eff_date
2433 and component_item_id = cmp_item_id
2434 and operation_seq_num = op_seq;
2435 raise ALREADY_EXISTS;
2436 exception
2437 when ALREADY_EXISTS then
2438 err_text := 'BOMPVALB(bmvdupcmp): Component already exists in production';
2439 return(cnt);
2440 when NO_DATA_FOUND then
2441 NULL;
2442 when others then
2443 err_text := 'BOMPVALB(bmvdupcmp) ' || substrb(SQLERRM,1,60);
2444 return(SQLCODE);
2445 end;
2446
2447 begin
2448 select component_sequence_id
2449 into cnt
2450 from bom_inventory_comps_interface
2451 where bill_sequence_id = bill_seq_id
2452 and to_char(effectivity_date,'YYYY/MM/DD HH24:MI') = eff_date
2453 and component_item_id = cmp_item_id
2454 and operation_seq_num = op_seq
2455 and rownum = 1
2456 and process_flag = 4;
2457
2458 raise ALREADY_EXISTS;
2459 exception
2460 when ALREADY_EXISTS then
2461 err_text := 'BOMPVALB(bmvdupcmp): Component already exists in interface';
2462 return(cnt);
2463 when NO_DATA_FOUND then
2464 NULL;
2465 when others then
2466 err_text := 'BOMPVALB(bmvdupcmp) ' || substrb(SQLERRM,1,60);
2467 return(SQLCODE);
2468 end;
2469 return(0);
2470
2471 EXCEPTION
2472 when others then
2473 err_text := 'BOMPVALB(bmvdupcmp) ' || substrb(SQLERRM,1,60);
2474 return(SQLCODE);
2475 END bmvdupcmp_verify_duplicate_cmp;
2476
2477
2478 /*------------------------ bmvref_validate_ref_desgs ------------------------*/
2479 /* NAME
2480 bmvref_validate_ref_desgs - validate reference designators
2481 DESCRIPTION
2482 check for validity of component sequence id
2483 no ref desgns for planning or option class items
2484 if quantity related then # of ref desgs = component qty
2485 REQUIRES
2486 err_text out buffer to return error message
2487 MODIFIES
2488 MTL_INTERFACE_ERRORS
2489 RETURNS
2490 0 if successful
2491 SQLCODE if unsuccessful
2492 NOTES
2493 -----------------------------------------------------------------------------*/
2494 FUNCTION bmvref_validate_ref_desgs (
2495 org_id NUMBER,
2496 all_org NUMBER := 2,
2497 user_id NUMBER,
2498 login_id NUMBER,
2499 prog_appid NUMBER,
2500 prog_id NUMBER,
2501 request_id NUMBER,
2502 err_text IN OUT VARCHAR2
2503 )
2504 return INTEGER
2505 IS
2506 ret_code NUMBER;
2507 stmt_num NUMBER := 0;
2508 dummy NUMBER;
2509 org_id_dummy NUMBER;
2510 assy_id_dummy NUMBER;
2511 commit_cnt NUMBER;
2512 comp_type NUMBER;
2513 continue_loop BOOLEAN := TRUE;
2514 total_recs NUMBER;
2515
2516 cursor c1 is
2517 select component_sequence_id CSI, count(*) CNT,
2518 transaction_id TI, component_item_id CII
2519 from bom_ref_desgs_interface
2520 where process_flag = 2
2521 and rownum < 500
2522 group by transaction_id, component_sequence_id, component_item_id;
2523
2524 BEGIN
2525 select count(distinct component_sequence_id)
2526 into total_recs
2527 from bom_ref_desgs_interface
2528 where process_flag = 2;
2529
2530 continue_loop := TRUE;
2531 commit_cnt := 0;
2532
2533 while continue_loop loop
2534 for c1rec in c1 loop
2535 /*
2536 ** check for null ref desgs
2537 */
2538 commit_cnt := commit_cnt + 1;
2539 select count(*)
2540 into dummy
2541 from bom_ref_desgs_interface
2542 where transaction_id = c1rec.TI
2543 and component_reference_designator is null;
2544
2545 if (dummy = 0) then
2546 NULL;
2547 else
2548 ret_code := INVPUOPI.mtl_log_interface_err(
2549 org_id => org_id,
2550 user_id => user_id,
2551 login_id => login_id,
2552 prog_appid => prog_appid,
2553 prog_id => prog_id,
2554 req_id => request_id,
2555 trans_id => c1rec.TI,
2556 error_text => err_text,
2557 tbl_name => 'BOM_REF_DESGS_INTERFACE',
2558 msg_name => 'BOM_NULL_REF_DESGS',
2559 err_text => err_text);
2560 update bom_ref_desgs_interface set
2561 process_flag = 3
2562 where transaction_id = c1rec.TI;
2563
2564 if (ret_code <> 0) then
2565 return(ret_code);
2566 end if;
2567 goto continue_loop;
2568 end if;
2569
2570
2571 /*
2572 ** verify for existence of component seq id
2573 */
2574 ret_code := BOMPVALB.bmvuncmp_verify_unique_comp (
2575 cmp_seq_id => c1rec.CSI,
2576 exist_flag => 1,
2577 err_text => err_text);
2578 if (ret_code <> 0) then
2579 ret_code := INVPUOPI.mtl_log_interface_err(
2580 org_id => org_id,
2581 user_id => user_id,
2582 login_id => login_id,
2583 prog_appid => prog_appid,
2584 prog_id => prog_id,
2585 req_id => request_id,
2586 trans_id => c1rec.TI,
2587 error_text => err_text,
2588 tbl_name => 'BOM_REF_DESGS_INTERFACE',
2589 msg_name => 'BOM_COMP_SEQ_ID_INVALID',
2590 err_text => err_text);
2591 update bom_ref_desgs_interface set
2592 process_flag = 3
2593 where transaction_id = c1rec.TI;
2594
2595 if (ret_code <> 0) then
2596 return(ret_code);
2597 end if;
2598 goto continue_loop;
2599 end if;
2600
2601 /*
2602 ** check for duplicate component seq id/ref desg combinations
2603 */
2604 ret_code := BOMPVALB.bmvundesg_verify_unique_desg (
2605 trans_id => c1rec.TI,
2606 err_text => err_text);
2607 if (ret_code <> 0) then
2608 ret_code := INVPUOPI.mtl_log_interface_err(
2609 org_id => org_id,
2610 user_id => user_id,
2611 login_id => login_id,
2612 prog_appid => prog_appid,
2613 prog_id => prog_id,
2614 req_id => request_id,
2615 trans_id => c1rec.TI,
2616 error_text => err_text,
2617 tbl_name => 'BOM_REF_DESGS_INTERFACE',
2618 msg_name => 'BOM_DUP_REF_DESG',
2619 err_text => err_text);
2620 update bom_ref_desgs_interface set
2621 process_flag = 3
2622 where transaction_id = c1rec.TI;
2623
2624 if (ret_code <> 0) then
2625 return(ret_code);
2626 end if;
2627 goto continue_loop;
2628 end if;
2629
2630 /*
2631 ** count reference designators if quantity related is Yes
2632 */
2633 ret_code := BOMPVALB.bmvcdesg_cnt_ref_desgs (
2634 trans_id => c1rec.TI,
2635 cmp_seq_id => c1rec.CSI,
2636 err_text => err_text);
2637 if (ret_code <> 0) then
2638 ret_code := INVPUOPI.mtl_log_interface_err(
2639 org_id => org_id,
2640 user_id => user_id,
2641 login_id => login_id,
2642 prog_appid => prog_appid,
2643 prog_id => prog_id,
2644 req_id => request_id,
2645 trans_id => c1rec.TI,
2646 error_text => err_text,
2647 tbl_name => 'BOM_REF_DESGS_INTERFACE',
2648 msg_name => 'BOM_REF_DESG_COUNT_INVALID',
2649 err_text => err_text);
2650 update bom_ref_desgs_interface set
2651 process_flag = 3
2652 where transaction_id = c1rec.TI;
2653
2654 if (ret_code <> 0) then
2655 return(ret_code);
2656 end if;
2657 goto continue_loop;
2658 end if;
2659
2660 /*
2661 ** no ref desgns for planning bills and
2662 ** non-Standard components
2663 */
2664 begin
2665 select bbom.organization_id,
2666 bbom.assembly_item_id, bic.bom_item_type
2667 into org_id_dummy, assy_id_dummy, comp_type
2668 from bom_inventory_components bic,
2669 bom_bill_of_materials bbom
2670 where component_sequence_id = c1rec.CSI
2671 and bbom.bill_sequence_id = bic.bill_sequence_id;
2672 goto check_bom_type;
2673 exception
2674 when NO_DATA_FOUND then
2675 NULL;
2676 end;
2677
2678 begin
2679 select bbom.organization_id,
2680 bbom.assembly_item_id, bic.bom_item_type
2681 into org_id_dummy, assy_id_dummy, comp_type
2682 from bom_inventory_comps_interface bic,
2683 bom_bill_of_mtls_interface bbom
2684 where component_sequence_id = c1rec.CSI
2685 and bic.process_flag = 4
2686 and bbom.process_flag = 4
2687 and bbom.bill_sequence_id = bic.bill_sequence_id;
2688 goto check_bom_type;
2689 exception
2690 when NO_DATA_FOUND then
2691 NULL;
2692 end;
2693
2694 begin
2695 select bbom.organization_id,
2696 bbom.assembly_item_id, bic.bom_item_type
2697 into org_id_dummy, assy_id_dummy, comp_type
2698 from bom_inventory_comps_interface bic,
2699 bom_bill_of_materials bbom
2700 where component_sequence_id = c1rec.CSI
2701 and bic.process_flag = 4
2702 and bbom.bill_sequence_id = bic.bill_sequence_id;
2703 goto check_bom_type;
2704 exception
2705 when NO_DATA_FOUND then
2706 err_text := substrb('bmvref: Invalid component sequence id ' || SQLERRM,1,70);
2707 return(9999);
2708 end;
2709
2710 <<check_bom_type>>
2711 NULL;
2712 select bom_item_type
2713 into dummy
2714 from mtl_system_items msi
2715 where msi.organization_id = org_id_dummy
2716 and msi.inventory_item_id = assy_id_dummy;
2717
2718 if (dummy = 3) then
2719 err_text := 'Cannot have reference desgs for planning bills';
2720 ret_code := INVPUOPI.mtl_log_interface_err(
2721 org_id => org_id_dummy,
2722 user_id => user_id,
2723 login_id => login_id,
2724 prog_appid => prog_appid,
2725 prog_id => prog_id,
2726 req_id => request_id,
2727 trans_id => c1rec.TI,
2728 error_text => err_text,
2729 tbl_name => 'BOM_REF_DESGS_INTERFACE',
2730 msg_name => 'BOM_NO_REF_DESGS_ALLOWED',
2731 err_text => err_text);
2732 update bom_ref_desgs_interface set
2733 process_flag = 3
2734 where transaction_id = c1rec.TI;
2735
2736 if (ret_code <> 0) then
2737 return(ret_code);
2738 end if;
2739 goto continue_loop;
2740 end if;
2741
2742 if (comp_type <> 4) then
2743 err_text := 'Cannot have reference desgs for model, option class, or planning components';
2744 ret_code := INVPUOPI.mtl_log_interface_err(
2745 org_id => org_id_dummy,
2746 user_id => user_id,
2747 login_id => login_id,
2748 prog_appid => prog_appid,
2749 prog_id => prog_id,
2750 req_id => request_id,
2751 trans_id => c1rec.TI,
2752 error_text => err_text,
2753 tbl_name => 'BOM_REF_DESGS_INTERFACE',
2754 msg_name => 'BOM_NO_REF_DESGS_COMP',
2755 err_text => err_text);
2756 update bom_ref_desgs_interface set
2757 process_flag = 3
2758 where transaction_id = c1rec.TI;
2759
2760 if (ret_code <> 0) then
2761 return(ret_code);
2762 end if;
2763 goto continue_loop;
2764 end if;
2765
2766 update bom_ref_desgs_interface
2767 set process_flag = 4
2768 where transaction_id = c1rec.TI;
2769
2770 <<continue_loop>>
2771 null;
2772 end loop;
2773
2774 commit;
2775
2776 if (commit_cnt < total_recs) then
2777 null;
2778 else
2779 continue_loop := FALSE;
2780 end if;
2781
2782 end loop;
2783
2784 return(0);
2785
2786 EXCEPTION
2787 when others then
2788 err_text := 'bmvref(' || stmt_num || ') ' || substrb(SQLERRM,1,60);
2789 return(SQLCODE);
2790 END bmvref_validate_ref_desgs;
2791
2792
2793 /*---------------------- bmvundesg_verify_unique_desg -----------------------*/
2794 /* NAME
2795 bmvundesg_verify_unique_desg - verify that the ref_desg is unique
2796 DESCRIPTION
2797 verify that the ref_desg is unique in both prod and interface tables
2798 for any component on a bill
2799
2800 REQUIRES
2801 trans_id transaction_id
2802 err_text out buffer to return error message
2803 MODIFIES
2804 RETURNS
2805 0 if successful
2806 SQLCODE if unsuccessful
2807 NOTES
2808 -----------------------------------------------------------------------------*/
2809 FUNCTION bmvundesg_verify_unique_desg (
2810 trans_id NUMBER,
2811 err_text OUT VARCHAR2
2812 )
2813 return INTEGER
2814 IS
2815 dummy NUMBER;
2816 NOT_UNIQUE EXCEPTION;
2817 BEGIN
2818 /*
2819 ** first check in prod tables
2820 */
2821 begin
2822 select 1
2823 into dummy
2824 from bom_reference_designators a, bom_ref_desgs_interface b
2825 where b.transaction_id = trans_id
2826 and a.component_sequence_id = b.component_sequence_id
2827 and a.COMPONENT_REFERENCE_DESIGNATOR =
2828 b.COMPONENT_REFERENCE_DESIGNATOR
2829 and rownum = 1;
2830 raise NOT_UNIQUE;
2831 exception
2832 when NO_DATA_FOUND then
2833 null;
2834 when NOT_UNIQUE then
2835 raise NOT_UNIQUE;
2836 when others then
2837 err_text := 'BOMPVALB(bmvundesg) ' || substrb(SQLERRM,1,60);
2838 return(SQLCODE);
2839 end;
2840
2841 /*
2842 ** check in interface table
2843 */
2844 select count(*)
2845 into dummy
2846 from bom_ref_desgs_interface a
2847 where transaction_id = trans_id
2848 and exists (select 'same designator'
2849 from bom_ref_desgs_interface b
2850 where b.transaction_id = trans_id
2851 and b.rowid <> a.rowid
2852 and b.COMPONENT_REFERENCE_DESIGNATOR =
2853 a.COMPONENT_REFERENCE_DESIGNATOR
2854 and b.process_flag <> 3
2855 and b.process_flag <> 7)
2856 and process_flag <> 3
2857 and process_flag <> 7;
2858
2859 if (dummy > 0) then
2860 raise NOT_UNIQUE;
2861 else
2862 return(0);
2863 end if;
2864 exception
2865 when NOT_UNIQUE then
2866 err_text := 'BOMPVALB(bmvundesg) ' ||'Duplicate ref desgs';
2867 return(9999);
2868 when others then
2869 err_text := 'BOMPVALB(bmvundesg) ' || substrb(SQLERRM,1,60);
2870 return(SQLCODE);
2871 end bmvundesg_verify_unique_desg;
2872
2873 /*------------------------ bmvcdesg_cnt_ref_desgs ------------------------*/
2874 /* NAME
2875 bmvcdesg_cnt_ref_desgs - verify ref desg count
2876 DESCRIPTION
2877 ensure that the number of ref desgs is same as component quantity
2878 if qty related
2879 REQUIRES
2880 trans_id transaction_id
2881 cmp_seq_id component_sequence_id
2882 err_text out buffer to return error message
2883 MODIFIES
2884 RETURNS
2885 0 if successful
2886 SQLCODE if unsuccessful
2887 NOTES
2888 -----------------------------------------------------------------------------*/
2889 FUNCTION bmvcdesg_cnt_ref_desgs (
2890 trans_id NUMBER,
2891 cmp_seq_id NUMBER,
2892 err_text OUT VARCHAR2
2893 )
2894 return INTEGER
2895 IS
2896 qty_flag NUMBER := -1;
2897 cmp_qty NUMBER;
2898 ref_qty NUMBER;
2899 int_ref_qty NUMBER;
2900 BEGIN
2901 begin
2902 select QUANTITY_RELATED, COMPONENT_QUANTITY
2903 into qty_flag, cmp_qty
2904 from bom_inventory_components
2905 where component_sequence_id = cmp_seq_id;
2906
2907 exception
2908 when NO_DATA_FOUND then
2909 null;
2910 when others then
2911 err_text := 'BOMPVALB(bmvcdesg) ' || substrb(SQLERRM,1,60);
2912 return(SQLCODE);
2913 end;
2914
2915 /*
2916 ** if not qty related then return
2917 */
2918 if (qty_flag = 2) then
2919 return(0);
2920 end if;
2921
2922 /*
2923 ** if no rows selected from prod table, then get from interface table
2924 */
2925 if (qty_flag <> 1 and qty_flag <> 2) then
2926 select QUANTITY_RELATED, COMPONENT_QUANTITY
2927 into qty_flag, cmp_qty
2928 from bom_inventory_comps_interface
2929 where component_sequence_id = cmp_seq_id
2930 and process_flag = 4;
2931 end if;
2932
2933 if (qty_flag = 1) then
2934 select count(*)
2935 into ref_qty
2936 from bom_reference_designators
2937 where component_sequencE_id = cmp_seq_id;
2938
2939 select count(*)
2940 into int_ref_qty
2941 from bom_ref_desgs_interface
2942 where transaction_id = trans_id
2943 and process_flag <> 3
2944 and process_flag <> 7;
2945 if (ref_qty + int_ref_qty <> cmp_qty) then
2946 err_text := 'BOMPVALB(bmvcdesg) ' || 'Number of ref desg
2947 not equal to component qty';
2948 return (9999);
2949 end if;
2950 end if;
2951
2952 return(0);
2953
2954 exception
2955 when others then
2956 err_text := 'BOMPVALB(bmvcdesg) ' || substrb(SQLERRM,1,60);
2957 return(SQLCODE);
2958 END bmvcdesg_cnt_ref_desgs;
2959
2960
2961 /*------------------------ bmvsubs_validate_sub_comps -----------------------*/
2962 /* NAME
2963 bmvsubs_validate_sub_comps - validate substitute components
2964 DESCRIPTION
2965 check for validity of component sequence id
2966 check for validity of component item id
2967 sub item qty must not be negative
2968 no subs for planning or option class items
2969 REQUIRES
2970 err_text out buffer to return error message
2971 MODIFIES
2972 MTL_INTERFACE_ERRORS
2973 RETURNS
2974 0 if successful
2975 SQLCODE if unsuccessful
2976 NOTES
2977 -----------------------------------------------------------------------------*/
2978 FUNCTION bmvsubs_validate_sub_comps (
2979 org_id NUMBER,
2980 all_org NUMBER := 2,
2981 user_id NUMBER,
2982 login_id NUMBER,
2983 prog_appid NUMBER,
2984 prog_id NUMBER,
2985 request_id NUMBER,
2986 err_text IN OUT VARCHAR2
2987 )
2988 return INTEGER
2989 IS
2990 ret_code NUMBER;
2991 stmt_num NUMBER := 0;
2992 dummy NUMBER;
2993 assy_id_dummy NUMBER;
2994 org_id_dummy NUMBER;
2995 assy_type_dummy NUMBER;
2996 comp_id_dummy NUMBER;
2997 commit_cnt NUMBER;
2998 comp_type NUMBER;
2999 continue_loop BOOLEAN := TRUE;
3000 total_recs NUMBER;
3001
3002 cursor c1 is
3003 select component_sequence_id CSI, count(*) CNT,
3004 transaction_id TI, assembly_item_id AII,
3005 organization_id OI
3006 from bom_sub_comps_interface
3007 where process_flag = 2
3008 and rownum < 500
3009 group by transaction_id, component_sequence_id,
3010 organization_id, assembly_item_id;
3011
3012 BEGIN
3013 select count(distinct component_sequence_id)
3014 into total_recs
3015 from bom_sub_comps_interface
3016 where process_flag = 2;
3017
3018 commit_cnt := 0;
3019
3020 while continue_loop loop
3021 for c1rec in c1 loop
3022 /*
3023 ** verify for existence of component seq id
3024 */
3025 commit_cnt := commit_cnt + 1;
3026 ret_code := BOMPVALB.bmvuncmp_verify_unique_comp (
3027 cmp_seq_id => c1rec.CSI,
3028 exist_flag => 1,
3029 err_text => err_text);
3030 if (ret_code <> 0) then
3031 ret_code := INVPUOPI.mtl_log_interface_err(
3032 org_id => c1rec.OI,
3033 user_id => user_id,
3034 login_id => login_id,
3035 prog_appid => prog_appid,
3036 prog_id => prog_id,
3037 req_id => request_id,
3038 trans_id => c1rec.TI,
3039 error_text => err_text,
3040 tbl_name => 'BOM_SUB_COMPS_INTERFACE',
3041 msg_name => 'BOM_COMP_SEQ_ID_INVALID',
3042 err_text => err_text);
3043 update bom_sub_comps_interface set
3044 process_flag = 3
3045 where transaction_id = c1rec.TI;
3046
3047 if (ret_code <> 0) then
3048 return(ret_code);
3049 end if;
3050 goto continue_loop;
3051 end if;
3052
3053 /*
3054 ** no sub comps for planning bills and
3055 ** non-standard components
3056 */
3057 begin
3058 select bbom.assembly_item_id,bbom.organization_id,
3059 bbom.assembly_type, bic.component_item_id,
3060 bic.bom_item_type
3061 into assy_id_dummy, org_id_dummy,assy_type_dummy,
3062 comp_id_dummy, comp_type
3063 from bom_inventory_components bic,
3064 bom_bill_of_materials bbom
3065 where component_sequence_id = c1rec.CSI
3066 and bbom.bill_sequence_id = bic.bill_sequence_id;
3067 goto check_bom_type;
3068 exception
3069 when NO_DATA_FOUND then
3070 NULL;
3071 end;
3072
3073 begin
3074 select bbom.assembly_item_id,bbom.organization_id,
3075 bbom.assembly_type, bic.component_item_id,
3076 bic.bom_item_type
3077 into assy_id_dummy, org_id_dummy,assy_type_dummy,
3078 comp_id_dummy, comp_type
3079 from bom_inventory_comps_interface bic,
3080 bom_bill_of_mtls_interface bbom
3081 where component_sequence_id = c1rec.CSI
3082 and bic.process_flag = 4
3083 and bbom.process_flag = 4
3084 and bbom.bill_sequence_id = bic.bill_sequence_id;
3085 goto check_bom_type;
3086 exception
3087 when NO_DATA_FOUND then
3088 NULL;
3089 end;
3090
3091 begin
3092 select bbom.assembly_item_id,bbom.organization_id,
3093 bbom.assembly_type, bic.component_item_id,
3094 bic.bom_item_type
3095 into assy_id_dummy, org_id_dummy,assy_type_dummy,
3096 comp_id_dummy, comp_type
3097 from bom_inventory_comps_interface bic,
3098 bom_bill_of_materials bbom
3099 where component_sequence_id = c1rec.CSI
3100 and bic.process_flag = 4
3101 and bbom.bill_sequence_id = bic.bill_sequence_id;
3102 goto check_bom_type;
3103 exception
3104 when NO_DATA_FOUND then
3105 err_text := substrb('bmvsubs: Invalid component sequence id ' || SQLERRM,1,70);
3106 return(9999);
3107 end;
3108
3109 <<check_bom_type>>
3110 NULL;
3111 select bom_item_type
3112 into dummy
3113 from mtl_system_items msi
3114 where msi.organization_id = org_id_dummy
3115 and msi.inventory_item_id = assy_id_dummy;
3116
3117 if (dummy = 3) then
3118 ret_code := INVPUOPI.mtl_log_interface_err(
3119 org_id => org_id_dummy,
3120 user_id => user_id,
3121 login_id => login_id,
3122 prog_appid => prog_appid,
3123 prog_id => prog_id,
3124 req_id => request_id,
3125 trans_id => c1rec.TI,
3126 error_text => err_text,
3127 tbl_name => 'BOM_SUB_COMPS_INTERFACE',
3128 msg_name => 'BOM_NO_SUB_COMPS_ALLOWED',
3129 err_text => err_text);
3130 update bom_sub_comps_interface set
3131 process_flag = 3
3132 where transaction_id = c1rec.TI;
3133
3134 if (ret_code <> 0) then
3135 return(ret_code);
3136 end if;
3137 goto continue_loop;
3138 end if;
3139
3140 if (comp_type <> 4) then
3141 ret_code := INVPUOPI.mtl_log_interface_err(
3142 org_id => org_id_dummy,
3143 user_id => user_id,
3144 login_id => login_id,
3145 prog_appid => prog_appid,
3146 prog_id => prog_id,
3147 req_id => request_id,
3148 trans_id => c1rec.TI,
3149 error_text => err_text,
3150 tbl_name => 'BOM_SUB_COMPS_INTERFACE',
3151 msg_name => 'BOM_NO_SUB_COMPS_COMP',
3152 err_text => err_text);
3153 update bom_sub_comps_interface set
3154 process_flag = 3
3155 where transaction_id = c1rec.TI;
3156
3157 if (ret_code <> 0) then
3158 return(ret_code);
3159 end if;
3160 goto continue_loop;
3161 end if;
3162
3163 /*
3164 ** Check substitute item existence in item master
3165 */
3166 select count(*)
3167 into dummy
3168 from bom_sub_comps_interface a
3169 where transaction_id = c1rec.TI
3170 and process_flag <> 3 and process_flag <> 7
3171 and not exists (select 'items exist'
3172 from mtl_system_items b
3173 where b.organization_id = org_id_dummy
3174 and b.inventory_item_id = a.substitute_component_id);
3175
3176 if (dummy <> 0) then
3177 err_text := 'Substitute item does not exist in item master';
3178 ret_code := INVPUOPI.mtl_log_interface_err(
3179 org_id => org_id_dummy,
3180 user_id => user_id,
3181 login_id => login_id,
3182 prog_appid => prog_appid,
3183 prog_id => prog_id,
3184 req_id => request_id,
3185 trans_id => c1rec.TI,
3186 error_text => err_text,
3187 tbl_name => 'BOM_SUB_COMPS_INTERFACE',
3188 msg_name => 'BOM_SUB_COMP_ITEM_INVALID',
3189 err_text => err_text);
3190 update bom_sub_comps_interface set
3191 process_flag = 3
3192 where transaction_id = c1rec.TI;
3193
3194 if (ret_code <> 0) then
3195 return(ret_code);
3196 end if;
3197 goto continue_loop;
3198 end if;
3199
3200 /*
3201 ** Verify substitute component is unique for a component
3202 */
3203 ret_code := BOMPVALB.bmvunsub_verify_unique_sub (
3204 trans_id => c1rec.TI,
3205 err_text => err_text);
3206 if (ret_code <> 0) then
3207 ret_code := INVPUOPI.mtl_log_interface_err(
3208 org_id => org_id_dummy,
3209 user_id => user_id,
3210 login_id => login_id,
3211 prog_appid => prog_appid,
3212 prog_id => prog_id,
3213 req_id => request_id,
3214 trans_id => c1rec.TI,
3215 error_text => err_text,
3216 tbl_name => 'BOM_SUB_COMPS_INTERFACE',
3217 msg_name => 'BOM_DUPLICATE_SUB_COMP',
3218 err_text => err_text);
3219 update bom_sub_comps_interface set
3220 process_flag = 3
3221 where transaction_id = c1rec.TI;
3222
3223 if (ret_code <> 0) then
3224 return(ret_code);
3225 end if;
3226 goto continue_loop;
3227 end if;
3228
3229 /*
3230 ** Verify sub comp is not the same as bill or component
3231 */
3232 select count(*)
3233 into dummy
3234 from bom_sub_comps_interface
3235 where transaction_id = c1rec.TI
3236 and (SUBSTITUTE_COMPONENT_ID = assy_id_dummy
3237 or
3238 SUBSTITUTE_COMPONENT_ID = comp_id_dummy)
3239 and process_flag <> 3 and process_flag <> 7;
3240
3241 if (dummy <> 0) then
3242 err_text := 'Substitute item is the same as assembly item or component item';
3243 ret_code := INVPUOPI.mtl_log_interface_err(
3244 org_id => org_id_dummy,
3245 user_id => user_id,
3246 login_id => login_id,
3247 prog_appid => prog_appid,
3248 prog_id => prog_id,
3249 req_id => request_id,
3250 trans_id => c1rec.TI,
3251 error_text => err_text,
3252 tbl_name => 'BOM_SUB_COMPS_INTERFACE',
3253 msg_name => 'BOM_SUB_COMP_ITEM_SAME',
3254 err_text => err_text);
3255 update bom_sub_comps_interface set
3256 process_flag = 3
3257 where transaction_id = c1rec.TI;
3258
3259 if (ret_code <> 0) then
3260 return(ret_code);
3261 end if;
3262 goto continue_loop;
3263 end if;
3264
3265 /*
3266 ** Check substitute item attributes
3267 */
3268 select count(*)
3269 into dummy
3270 from bom_sub_comps_interface bsc
3271 where bsc.transaction_id = c1rec.TI
3272 and not exists (select 'x'
3273 from mtl_system_items msi
3274 where organization_id = org_id_dummy
3275 and inventory_item_id = bsc.substitute_component_id
3276 and bom_enabled_flag = 'Y'
3277 and bom_item_type = 4
3278 and ((assy_type_dummy = 2)
3279 or
3280 (assy_type_dummy = 1
3281 and eng_item_flag = 'N')));
3282 if (dummy <> 0) then
3283 err_text := 'Substitute item has invalid item attributes';
3284 ret_code := INVPUOPI.mtl_log_interface_err(
3285 org_id => org_id_dummy,
3286 user_id => user_id,
3287 login_id => login_id,
3288 prog_appid => prog_appid,
3289 prog_id => prog_id,
3290 req_id => request_id,
3291 trans_id => c1rec.TI,
3292 error_text => err_text,
3293 tbl_name => 'BOM_SUB_COMPS_INTERFACE',
3294 msg_name => 'BOM_SUB_COMP_ITEM_ATTR_INVALID',
3295 err_text => err_text);
3296 update bom_sub_comps_interface set
3297 process_flag = 3
3298 where transaction_id = c1rec.TI;
3299
3300 if (ret_code <> 0) then
3301 return(ret_code);
3302 end if;
3303 goto continue_loop;
3304 end if;
3305
3306 /*
3307 ** Substitute item quantity cannot be zero
3308 */
3309 select count(*)
3310 into dummy
3311 from bom_sub_comps_interface
3312 where transaction_id = c1rec.TI
3313 and process_flag <> 3 and process_flag <> 7
3314 and substitute_item_quantity = 0;
3315 if (dummy <> 0) then
3316 err_text := 'Quantity cannot be zero';
3317 ret_code := INVPUOPI.mtl_log_interface_err(
3318 org_id => org_id_dummy,
3319 user_id => user_id,
3320 login_id => login_id,
3321 prog_appid => prog_appid,
3322 prog_id => prog_id,
3323 req_id => request_id,
3324 trans_id => c1rec.TI,
3325 error_text => err_text,
3326 tbl_name => 'BOM_SUB_COMPS_INTERFACE',
3327 msg_name => 'BOM_SUB_COMP_QTY_ZERO',
3328 err_text => err_text);
3329 update bom_sub_comps_interface set
3330 process_flag = 3
3331 where transaction_id = c1rec.TI;
3332
3333 if (ret_code <> 0) then
3334 return(ret_code);
3335 end if;
3336 goto continue_loop;
3337 end if;
3338
3339 update bom_sub_comps_interface
3340 set process_flag = 4
3341 where transaction_id = c1rec.TI;
3342
3343 <<continue_loop>>
3344 null;
3345 end loop;
3346
3347 commit;
3348
3349 if (commit_cnt < total_recs) then
3350 null;
3351 else
3352 continue_loop := FALSE;
3353 end if;
3354
3355 end loop;
3356
3357 return(0);
3358
3359 EXCEPTION
3360 when others then
3361 err_text := 'bmvsubs(' || stmt_num || ') ' || substrb(SQLERRM,1,60);
3362 return(SQLCODE);
3363 END bmvsubs_validate_sub_comps;
3364
3365 /*---------------------- bmvunsub_verify_unique_sub -----------------------*/
3366 /* NAME
3367 bmvunsub_verify_unique_sub - verify that the sub_comp is unique
3368 DESCRIPTION
3369 verify that the substitute component is unique in both prod and
3370 interface tables for any component on a bill
3371
3372 REQUIRES
3373 trans_id transaction_id
3374 err_text out buffer to return error message
3375 MODIFIES
3376 RETURNS
3377 0 if successful
3378 SQLCODE if unsuccessful
3379 NOTES
3380 -----------------------------------------------------------------------------*/
3381 FUNCTION bmvunsub_verify_unique_sub (
3382 trans_id NUMBER,
3383 err_text OUT VARCHAR2
3384 )
3385 return INTEGER
3386 IS
3387 dummy NUMBER;
3388 NOT_UNIQUE EXCEPTION;
3389 BEGIN
3390 /*
3391 ** first check in prod tables
3392 */
3393 begin
3394 select 1
3395 into dummy
3396 from bom_substitute_components a, bom_sub_comps_interface b
3397 where b.transaction_id = trans_id
3398 and a.component_sequence_id = b.component_sequence_id
3399 and a.SUBSTITUTE_COMPONENT_ID =
3400 b.SUBSTITUTE_COMPONENT_ID
3401 and rownum = 1;
3402 raise NOT_UNIQUE;
3403 exception
3404 when NO_DATA_FOUND then
3405 null;
3406 when NOT_UNIQUE then
3407 raise NOT_UNIQUE;
3408 when others then
3409 err_text := 'BOMPVALB(bmvunsub) ' || substrb(SQLERRM,1,60);
3410 return(SQLCODE);
3411 end;
3412
3413 /*
3414 ** check in interface table
3415 */
3416 select count(*)
3417 into dummy
3418 from bom_sub_comps_interface a
3419 where transaction_id = trans_id
3420 and exists (select 'same substitue'
3421 from bom_sub_comps_interface b
3422 where b.transaction_id = trans_id
3423 and b.rowid <> a.rowid
3424 and b.SUBSTITUTE_COMPONENT_ID =
3425 a.SUBSTITUTE_COMPONENT_ID
3426 and b.process_flag <> 3
3427 and b.process_flag <> 7)
3428 and process_flag <> 3
3429 and process_flag <> 7;
3430
3431 if (dummy > 0) then
3432 raise NOT_UNIQUE;
3433 else
3434 return(0);
3435 end if;
3436 exception
3437 when NOT_UNIQUE then
3438 err_text := 'BOMPVALB(bmvunsub) ' ||'Duplicate substitute components';
3439 return(9999);
3440 when others then
3441 err_text := 'BOMPVALB(bmvunsub) ' || substrb(SQLERRM,1,60);
3442 return(SQLCODE);
3443 end bmvunsub_verify_unique_sub;
3444
3445
3446 /*------------------------ bmvitmrev_validate_itm_rev -----------------------*/
3447 /* NAME
3448 bmvitmrev_validate_itm_rev - validate the item rev interface table
3449 DESCRIPTION
3450 validate revs
3451 - ensure revs in ascending order
3452 - no duplicate revs
3453 REQUIRES
3454 org_id org id to validate
3455 all_org all_org flag
3456 user_id user id
3457 login_id login id
3458 prog_appid program application id
3459 prod_id program id
3460 req_id request id
3461 err_text out buffer to return error message
3462 MODIFIES
3463 RETURNS
3464 0 if successful
3465 SQLCODE if unsuccessful
3466 NOTES
3467 -----------------------------------------------------------------------------*/
3468 FUNCTION bmvitmrev_validate_itm_rev (
3469 org_id NUMBER,
3470 all_org NUMBER,
3471 user_id NUMBER,
3472 login_id NUMBER,
3473 prog_appid NUMBER,
3474 prog_id NUMBER,
3475 req_id NUMBER,
3476 err_text IN OUT VARCHAR2
3477 )
3478 return INTEGER
3479 IS
3480 cursor c0 is
3481 select inventory_item_id AII, organization_id OI,
3482 revision R, transaction_id TI
3483 from mtl_item_revisions_interface
3484 where process_flag = 2
3485 and rownum < 500;
3486
3487 cursor c1 is
3488 select inventory_item_id AII, organization_id OI
3489 from mtl_item_revisions_interface
3490 where process_flag = 99
3491 and rownum < 500
3492 group by organization_id, inventory_item_id;
3493
3494 cursor c2 is
3495 select 'x'
3496 from mtl_item_revisions_interface
3497 where process_flag = 99
3498 group by organization_id, inventory_item_id;
3499
3500 ret_code NUMBER;
3501 dummy NUMBER;
3502 dummy_id NUMBER;
3503 stmt_num NUMBER;
3504 commit_cnt NUMBER;
3505 dummy_bill NUMBER;
3506 continue_loop BOOLEAN := TRUE;
3507 total_recs NUMBER;
3508
3509 BEGIN
3510 /*
3511 ** Check if revision is null
3512 */
3513 while continue_loop loop
3514 commit_cnt := 0;
3515 for c0rec in c0 loop
3516 commit_cnt := commit_cnt + 1;
3517 stmt_num := 1;
3518 if (c0rec.R is null) then
3519 ret_code := INVPUOPI.mtl_log_interface_err(
3520 org_id => org_id,
3521 user_id => user_id,
3522 login_id => login_id,
3523 prog_appid => prog_appid,
3524 prog_id => prog_id,
3525 req_id => req_id,
3526 trans_id => c0rec.TI,
3527 error_text => err_text,
3528 tbl_name => 'MTL_ITEM_REVISIONS_INTERFACE',
3529 msg_name => 'BOM_NULL_REV',
3530 err_text => err_text);
3531 update mtl_item_revisions_interface set
3532 process_flag = 3
3533 where transaction_id = c0rec.TI;
3534
3535 if (ret_code <> 0) then
3536 return(ret_code);
3537 end if;
3538 goto continue_loop;
3539 end if;
3540 /*
3541 ** Check for valid org id
3542 */
3543 stmt_num := 2;
3544 BEGIN
3545 select organization_id
3546 into dummy_id
3547 from mtl_parameters
3548 where organization_id = c0rec.OI;
3549 EXCEPTION
3550 when NO_DATA_FOUND then
3551 ret_code := INVPUOPI.mtl_log_interface_err(
3552 org_id => c0rec.OI,
3553 user_id => user_id,
3554 login_id => login_id,
3555 prog_appid => prog_appid,
3556 prog_id => prog_id,
3557 req_id => req_id,
3558 trans_id => c0rec.TI,
3559 error_text => err_text,
3560 tbl_name => 'MTL_ITEM_REVISIONS_INTERFACE',
3561 msg_name => 'BOM_INVALID_ORG_ID',
3562 err_text => err_text);
3563 update mtl_item_revisions_interface set
3564 process_flag = 3
3565 where transaction_id = c0rec.TI;
3566 if (ret_code <> 0) then
3567 return(ret_code);
3568 end if;
3569 goto continue_loop;
3570 END;
3571
3572 /* Check if assembly item exists */
3573 stmt_num := 3;
3574 ret_code := BOMPVALB.bmvassyid_verify_assembly_id(
3575 org_id => c0rec.OI,
3576 assy_id => c0rec.AII,
3577 err_text => err_text);
3578 if (ret_code <> 0) then
3579 ret_code := INVPUOPI.mtl_log_interface_err(
3580 org_id => c0rec.OI,
3581 user_id => user_id,
3582 login_id => login_id,
3583 prog_appid => prog_appid,
3584 prog_id => prog_id,
3585 req_id => req_id,
3586 trans_id => c0rec.TI,
3587 error_text => err_text,
3588 tbl_name => 'MTL_ITEM_REVISIONS_INTERFACE',
3589 msg_name => 'BOM_INV_ITEM_INVALID',
3590 err_text => err_text);
3591 update mtl_item_revisions_interface set
3592 process_flag = 3
3593 where transaction_id = c0rec.TI;
3594
3595 if (ret_code <> 0) then
3596 return(ret_code);
3597 end if;
3598 goto continue_loop;
3599 end if;
3600
3601 /*
3602 ** check if a valid bill exists for this revision
3603 */
3604 dummy_bill := 0;
3605
3606 select count(*)
3607 into dummy_bill
3608 from bom_bill_of_materials
3609 where organization_id = c0rec.OI
3610 and assembly_item_id = c0rec.AII;
3611
3612 if (dummy_bill = 0) then
3613 select count(*)
3614 into dummy_bill
3615 from bom_bill_of_mtls_interface
3616 where process_flag = 4
3617 and organization_id = c0rec.OI
3618 and assembly_item_id = c0rec.AII;
3619
3620 if (dummy_bill = 0) then
3621 ret_code := INVPUOPI.mtl_log_interface_err(
3622 org_id => c0rec.OI,
3623 user_id => user_id,
3624 login_id => login_id,
3625 prog_appid => prog_appid,
3626 prog_id => prog_id,
3627 req_id => req_id,
3628 trans_id => c0rec.TI,
3629 error_text => err_text,
3630 tbl_name => 'MTL_ITEM_REVISIONS_INTERFACE',
3631 msg_name => 'BOM_BILL_DOES_NOT_EXIST',
3632 err_text => err_text);
3633 update mtl_item_revisions_interface set
3634 process_flag = 3
3635 where transaction_id = c0rec.TI;
3636
3637 if (ret_code <> 0) then
3638 return(ret_code);
3639 end if;
3640 goto continue_loop;
3641 end if;
3642 end if;
3643
3644 update mtl_item_revisions_interface set
3645 process_flag = 99
3646 where transaction_id = c0rec.TI;
3647
3648 <<continue_loop>>
3649 NULL;
3650 end loop;
3651 commit;
3652
3653 if (commit_cnt < (500 - 1)) then
3654 continue_loop := FALSE;
3655 end if;
3656
3657 end loop;
3658
3659 total_recs := 0;
3660 for c2rec in c2 loop
3661 total_recs := total_recs + 1;
3662 end loop;
3663
3664 continue_loop := TRUE;
3665 commit_cnt := 0;
3666
3667 while continue_loop loop
3668 for c1rec in c1 loop
3669 commit_cnt := commit_cnt + 1;
3670 stmt_num := 4;
3671 ret_code := BOMPVALB.bmvalrev_validate_rev (
3672 org_id => c1rec.OI,
3673 assy_id => c1rec.AII,
3674 user_id => user_id,
3675 login_id => login_id,
3676 prog_appid => prog_appid,
3677 prog_id => prog_id,
3678 req_id => req_id,
3679 err_text => err_text);
3680 if (ret_code <> 0) then
3681 return(ret_code);
3682 end if;
3683
3684 <<continue_loop>>
3685 NULL;
3686 end loop;
3687
3688 commit;
3689
3690 if (commit_cnt < total_recs) then
3691 null;
3692 else
3693 continue_loop := FALSE;
3694 end if;
3695
3696 end loop;
3697
3698 return(0);
3699
3700 EXCEPTION
3701 when others then
3702 err_text := 'BOMPVALB(bmvitmrev)' || substrb(SQLERRM,1,60);
3703 return(SQLCODE);
3704 END bmvitmrev_validate_itm_rev;
3705
3706 /*--------------------------- bmvalrev_validate_rev -------------------------*/
3707 /* NAME
3708 bmvalrev_validate_rev - validate item revision
3709 DESCRIPTION
3710 validate revs
3711 - ensure revs in ascending order
3712 - no duplicate revs
3713 REQUIRES
3714 org_id NUMBER,
3715 assy_id NUMBER,
3716 err_text out buffer to return error message
3717 MODIFIES
3718 RETURNS
3719 0 if successful
3720 SQLCODE if unsuccessful
3721 NOTES
3722 -----------------------------------------------------------------------------*/
3723 FUNCTION bmvalrev_validate_rev (
3724 org_id NUMBER,
3725 assy_id NUMBER,
3726 user_id NUMBER,
3727 login_id NUMBER,
3728 prog_appid NUMBER,
3729 prog_id NUMBER,
3730 req_id NUMBER,
3731 err_text IN OUT VARCHAR2
3732 )
3733 return INTEGER
3734 IS
3735 cursor c1 is
3736 select revision R, effectivity_date ED,
3737 transaction_id TI
3738 from mtl_item_revisions_interface
3739 where organization_id = org_id
3740 and inventory_item_id = assy_id
3741 and process_flag = 99;
3742 ret_code NUMBER;
3743 err_cnt NUMBER;
3744 err_flag NUMBER;
3745 stmt_num NUMBER;
3746 BEGIN
3747 for c1rec in c1 loop
3748 err_cnt := 0;
3749 stmt_num := 1;
3750 /*
3751 ** check for ascending order and identical revs
3752 */
3753 select count(*)
3754 into err_cnt
3755 from mtl_item_revisions_interface a
3756 where transaction_id <> c1rec.TI
3757 and inventory_item_id = assy_id
3758 and organization_id = org_id
3759 and process_flag = 4
3760 and ( (revision = c1rec.R)
3761 or
3762 (effectivity_date > c1rec.ED
3763 and revision < c1rec.R)
3764 or
3765 (effectivity_date < c1rec.ED
3766 and revision > c1rec.R)
3767 );
3768
3769 if (err_cnt <> 0) then
3770 goto write_error;
3771 end if;
3772
3773 stmt_num := 2;
3774 select count(*)
3775 into err_cnt
3776 from mtl_item_revisions
3777 where inventory_item_id = assy_id
3778 and organization_id = org_id
3779 and ( (revision = c1rec.R)
3780 or
3781 (effectivity_date > c1rec.ED
3782 and revision < c1rec.R)
3783 or
3784 (effectivity_date < c1rec.ED
3785 and revision > c1rec.R)
3786 );
3787
3788 if (err_cnt <> 0) then
3789 goto write_error;
3790 end if;
3791
3792 stmt_num := 3;
3793 update mtl_item_revisions_interface set
3794 process_flag = 4
3795 where transaction_id = c1rec.TI;
3796 goto continue_loop;
3797
3798 <<write_error>>
3799 ret_code := INVPUOPI.mtl_log_interface_err(
3800 org_id => org_id,
3801 user_id => user_id,
3802 login_id => login_id,
3803 prog_appid => prog_appid,
3804 prog_id => prog_id,
3805 req_id => req_id,
3806 trans_id => c1rec.TI,
3807 error_text => err_text,
3808 tbl_name => 'MTL_ITEM_REVISIONS_INTERFACE',
3809 msg_name => 'BOM_REV_INVALID',
3810 err_text => err_text);
3811 update mtl_item_revisions_interface set
3812 process_flag = 3
3813 where transaction_id = c1rec.TI;
3814 <<continue_loop>>
3815 null;
3816 end loop;
3817 return(0);
3818 exception
3819 when others then
3820 err_text := 'BOMPVALB(bmvalrev-' || stmt_num || ')' || substrb(SQLERRM,1,60);
3821 return(SQLCODE);
3822 END bmvalrev_validate_rev;
3823
3824 END BOMPVALB;