[Home] [Help]
PACKAGE BODY: APPS.BOMPVALR
Source
1 package body BOMPVALR as
2 /* $Header: BOMVALRB.pls 120.2.12010000.2 2008/11/14 16:41:56 snandana ship $
3 +===========================================================================+
4 | Copyright (c) 1993 Oracle Corporation Belmont, California, USA |
5 | All rights reserved. |
6 +===========================================================================+
7 | |
8 | File Name : BOMPVALR.plb |
9 | DESCRIPTION : This package contains functions used to validate routing |
10 | data in the interface tables |
11 | Parameters: org_id organization_id |
12 | all_org process all orgs or just current org |
13 | 1 - all orgs |
14 | 2 - only org_id |
15 | prog_appid program application_id |
16 | prog_id program id |
17 | request_id request_id |
18 | user_id user id |
19 | login_id login id |
20 | History: |
21 | 10/05/93 Shreyas Shah creation date |
22 | 05/04/94 Julie Maeyama updated code |
23 | |
24 +==========================================================================*/
25
26 /*---------------------- bmvrtgh_validate_rtg_header -----------------------*/
27 /* NAME
28 bmvrtgh_validate_rtg_header - validate routing data
29 DESCRIPTION
30 validate the routing header information before loading into the
31 production tables.
32
33 REQUIRES
34 err_text out buffer to return error message
35 MODIFIES
36 MTL_INTERFACE_ERRORS
37 RETURNS
38 0 if successful
39 SQLCODE if unsuccessful
40 NOTES
41 -----------------------------------------------------------------------------*/
42 FUNCTION bmvrtgh_validate_rtg_header (
43 org_id NUMBER,
44 all_org NUMBER,
45 user_id NUMBER,
46 login_id NUMBER,
47 prog_appid NUMBER,
48 prog_id NUMBER,
49 request_id NUMBER,
50 err_text IN OUT NOCOPY VARCHAR2
51 )
52 return INTEGER
53 IS
54 CURSOR c1 is select
55 organization_id OI, routing_sequence_id RSI,
56 assembly_item_id AII, common_routing_sequence_id CRSI,
57 completion_locator_id CLI, routing_type RT,
58 common_assembly_item_id CAII, completion_subinventory CS,
59 alternate_routing_designator ARD, transaction_id TI
60 from bom_op_routings_interface
61 where process_flag = 2
62 and rownum < 500;
63
64 ret_code NUMBER;
65 stmt_num NUMBER;
66 dummy_id NUMBER;
67 commit_cnt NUMBER;
68 inv_asst VARCHAR2(1);
69 r_subinv NUMBER;
70 r_loc NUMBER;
71 loc_ctl NUMBER;
72 org_loc NUMBER;
73 sub_loc_code NUMBER;
74 dummy VARCHAR2(50);
75 continue_loop BOOLEAN := TRUE;
76 X_expense_to_asset_transfer NUMBER;
77
78 BEGIN
79 /*
80 ** Check for valid org id
81 */
82 while continue_loop loop
83 commit_cnt := 0;
84 for c1rec in c1 loop
85 commit_cnt := commit_cnt + 1;
86 stmt_num := 1;
87 BEGIN
88 select organization_id
89 into dummy_id
90 from mtl_parameters
91 where organization_id = c1rec.OI;
92 EXCEPTION
93 when NO_DATA_FOUND then
94 ret_code := INVPUOPI.mtl_log_interface_err(
95 org_id => c1rec.OI,
96 user_id => user_id,
97 login_id => login_id,
98 prog_appid => prog_appid,
99 prog_id => prog_id,
100 req_id => request_id,
101 trans_id => c1rec.TI,
102 error_text => err_text,
103 tbl_name => 'BOM_OP_ROUTINGS_INTERFACE',
104 msg_name => 'BOM_INVALID_ORG_ID',
105 err_text => err_text);
106 update bom_op_routings_interface set
107 process_flag = 3
108 where transaction_id = c1rec.TI;
109 if (ret_code <> 0) then
110 return(ret_code);
111 end if;
112 goto continue_loop;
113 END;
114
115 stmt_num := 2;
116 if (c1rec.ARD is not null) then /* Check for valid alternate */
117 BEGIN
118 select 1
119 into dummy_id
120 from bom_alternate_designators
121 where organization_id = c1rec.OI
122 and alternate_designator_code = c1rec.ARD;
123 EXCEPTION
124 when NO_DATA_FOUND then
125 ret_code := INVPUOPI.mtl_log_interface_err(
126 org_id => c1rec.OI,
127 user_id => user_id,
128 login_id => login_id,
129 prog_appid => prog_appid,
130 prog_id => prog_id,
131 req_id => request_id,
132 trans_id => c1rec.TI,
133 error_text => err_text,
134 tbl_name => 'BOM_OP_ROUTINGS_INTERFACE',
135 msg_name => 'BOM_INVALID_RTG_ALTERNATE',
136 err_text => err_text);
137 update bom_op_routings_interface set
138 process_flag = 3
139 where transaction_id = c1rec.TI;
140
141 if (ret_code <> 0) then
142 return(ret_code);
143 end if;
144 goto continue_loop;
145 END;
146 end if;
147
148 stmt_num := 3; /* Check if assembly item exists */
149 ret_code := BOMPVALB.bmvassyid_verify_assembly_id(
150 org_id => c1rec.OI,
151 assy_id => c1rec.AII,
152 err_text => err_text);
153 if (ret_code <> 0) then
154 ret_code := INVPUOPI.mtl_log_interface_err(
155 org_id => c1rec.OI,
156 user_id => user_id,
157 login_id => login_id,
158 prog_appid => prog_appid,
159 prog_id => prog_id,
160 req_id => request_id,
161 trans_id => c1rec.TI,
162 error_text => err_text,
163 tbl_name => 'BOM_OP_ROUTINGS_INTERFACE',
164 msg_name => 'BOM_ASSEMBLY_ITEM_INVALID',
165 err_text => err_text);
166 update bom_op_routings_interface set
167 process_flag = 3
168 where transaction_id = c1rec.TI;
169
170 if (ret_code <> 0) then
171 return(ret_code);
172 end if;
173 goto continue_loop;
174 end if;
175
176 stmt_num := 4; /* routing_type must be 1 or 2 */
177 if (c1rec.RT <> 1) and (c1rec.RT <> 2) then
178 ret_code := INVPUOPI.mtl_log_interface_err(
179 org_id => c1rec.OI,
180 user_id => user_id,
181 login_id => login_id,
182 prog_appid => prog_appid,
183 prog_id => prog_id,
184 req_id => request_id,
185 trans_id => c1rec.TI,
186 error_text => err_text,
187 tbl_name => 'BOM_OP_ROUTINGS_INTERFACE',
188 msg_name => 'BOM_ROUTING_TYPE_INVALID',
189 err_text => err_text);
190 update bom_op_routings_interface set
191 process_flag = 3
192 where transaction_id = c1rec.TI;
193
194 if (ret_code <> 0) then
195 return(ret_code);
196 end if;
197 goto continue_loop;
198 end if;
199
200 /* Check for unique routing seq id */
201 ret_code :=bmvurtg_verify_routing(
202 rtg_seq_id => c1rec.RSI,
203 mode_type => 1,
204 err_text => err_text);
205 if (ret_code <> 0) then
206 ret_code := INVPUOPI.mtl_log_interface_err(
207 org_id => NULL,
208 user_id => user_id,
209 login_id => login_id,
210 prog_appid => prog_appid,
211 prog_id => prog_id,
212 req_id => request_id,
213 trans_id => c1rec.TI,
214 error_text => err_text,
215 tbl_name => 'BOM_OP_ROUTINGS_INTERFACE',
216 msg_name => 'BOM_DUPLICATE_RTG',
217 err_text => err_text);
218 update bom_op_routings_interface set
219 process_flag = 3
220 where transaction_id = c1rec.TI;
221
222 if (ret_code <> 0) then
223 return(ret_code);
224 end if;
225 goto continue_loop;
226 end if;
227
228 /*
229 ** Check for duplicate assy,org,alt combo
230 ** Check alternate routing has a primary
231 ** Check alternate mfg routing does not have an eng primary routing
232 */
233 stmt_num := 6;
234 ret_code :=bmvduprt_verify_duplicate_rtg(
235 org_id => c1rec.OI,
236 assy_id => c1rec.AII,
237 alt_desg => c1rec.ARD,
238 rtg_type => c1rec.RT,
239 err_text => err_text);
240 if (ret_code <> 0) then
241 ret_code := INVPUOPI.mtl_log_interface_err(
242 org_id => c1rec.OI,
243 user_id => user_id,
244 login_id => login_id,
245 prog_appid => prog_appid,
246 prog_id => prog_id,
247 req_id => request_id,
248 trans_id => c1rec.TI,
249 error_text => err_text,
250 tbl_name => 'BOM_OP_ROUTINGS_INTERFACE',
251 msg_name => 'BOM_RTG_INVALID',
252 err_text => err_text);
253 update bom_op_routings_interface set
254 process_flag = 3
255 where transaction_id = c1rec.TI;
256
257 if (ret_code <> 0) then
258 return(ret_code);
259 end if;
260 goto continue_loop;
261 end if;
262
263 /* Check routing type and item attributes */
264 stmt_num := 7;
265 ret_code :=bmvrtg_verify_rtg_type(
266 org_id => c1rec.OI,
267 assy_id => c1rec.AII,
268 rtg_type => c1rec.RT,
269 err_text => err_text);
270 if (ret_code <> 0) then
271 ret_code := INVPUOPI.mtl_log_interface_err(
272 org_id => NULL,
273 user_id => user_id,
274 login_id => login_id,
275 prog_appid => prog_appid,
276 prog_id => prog_id,
277 req_id => request_id,
278 trans_id => c1rec.TI,
279 error_text => err_text,
280 tbl_name => 'BOM_OP_ROUTINGS_INTERFACE',
281 msg_name => 'BOM_ROUTING_TYPE_ERR',
282 err_text => err_text);
283 update bom_op_routings_interface set
284 process_flag = 3
285 where transaction_id = c1rec.TI;
286
287 if (ret_code <> 0) then
288 return(ret_code);
289 end if;
290 goto continue_loop;
291 end if;
292
293 /* Check cmn rtg seq id existence */
294 stmt_num := 8;
295 if c1rec.RSI = c1rec.CRSI then
296 NULL;
297 else
298 ret_code :=bmvurtg_verify_routing(
299 rtg_seq_id => c1rec.CRSI,
300 mode_type => 2,
301 err_text => err_text);
302 if (ret_code <> 0) then
303 ret_code := INVPUOPI.mtl_log_interface_err(
304 org_id => c1rec.OI,
305 user_id => user_id,
306 login_id => login_id,
307 prog_appid => prog_appid,
308 prog_id => prog_id,
309 req_id => request_id,
310 trans_id => c1rec.TI,
311 error_text => err_text,
312 tbl_name => 'BOM_OP_ROUTINGS_INTERFACE',
313 msg_name => 'BOM_COMMON_RTG_NOT_EXIST',
314 err_text => err_text);
315 update bom_op_routings_interface set
316 process_flag = 3
317 where transaction_id = c1rec.TI;
318
319 if (ret_code <> 0) then
320 return(ret_code);
321 end if;
322 goto continue_loop;
323 end if;
324
325
326 /* Verify common routing attributes */
327 ret_code :=bmvcmrtg_verify_common_routing(
328 rtg_id => c1rec.RSI,
329 cmn_rtg_id => c1rec.CRSI,
330 rtg_type => c1rec.RT,
331 item_id => c1rec.AII,
332 org_id => c1rec.OI,
333 alt_desg => c1rec.ARD,
334 err_text => err_text);
335 if (ret_code <> 0) then
336 ret_code := INVPUOPI.mtl_log_interface_err(
337 org_id => NULL,
338 user_id => user_id,
339 login_id => login_id,
340 prog_appid => prog_appid,
341 prog_id => prog_id,
342 req_id => request_id,
343 trans_id => c1rec.TI,
344 error_text => err_text,
345 tbl_name => 'BOM_OP_ROUTINGS_INTERFACE',
346 msg_name => 'BOM_COMMON_RTG_ERROR',
347 err_text => err_text);
348 update bom_op_routings_interface set
349 process_flag = 3
350 where transaction_id = c1rec.TI;
351
352 if (ret_code <> 0) then
353 return(ret_code);
354 end if;
355 goto continue_loop;
356 end if;
357 end if;
358 /*
359 ** Validate subinventory
360 */
361 if (c1rec.CLI is not null and c1rec.CS is null) then
362 goto write_loc_error;
363 end if;
364
365 if (c1rec.CLI is null and c1rec.CS is null) then
366 goto update_comp;
367 end if;
368
369 select inventory_asset_flag,restrict_subinventories_code,
370 restrict_locators_code, location_control_code
371 into inv_asst, r_subinv, r_loc, loc_ctl
372 from mtl_system_items
373 where inventory_item_id = c1rec.AII
374 and organization_id = c1rec.OI;
375 /*
376 ** if item locator control is null, set to 1 (no loc control)
377 */
378 if (loc_ctl is null) then
379 loc_ctl := 1;
380 end if;
381 /*
382 ** if subinv is not restricted and locator is, then make
383 ** locator unrestricted
384 */
385
386 if (r_subinv = 2) and (r_loc = 1) then
387 r_loc := 2;
388 end if;
389 /*
390 ** Check if subinventory is valid
391 */
392
393 /*
394 ** get value of profile INV:EXPENSE_TO_ASSET_TRANSFER
395 */
396 BOMPRFIL.bom_pr_get_profile(
397 appl_short_name => 'INV',
398 profile_name => 'INV:EXPENSE_TO_ASSET_TRANSFER',
399 user_id => user_id,
400 resp_appl_id => prog_appid,
401 resp_id => 401,
402 profile_value => X_expense_to_asset_transfer,
403 return_code => ret_code,
404 return_message => err_text);
405 if (ret_code <> 0) then
406 return(ret_code);
407 end if;
408
412 select locator_type
409 IF (r_subinv = 2) THEN /* non-restricted subinventory */
410 IF (X_expense_to_asset_transfer = 1) THEN
411 begin
413 into sub_loc_code
414 from mtl_secondary_inventories
415 where secondary_inventory_name = c1rec.CS
416 and organization_id = c1rec.OI
417 and nvl(disable_date,TRUNC(SYSDATE)+1) > TRUNC(SYSDATE)
418 and quantity_tracked = 1;
419 exception
420 when no_data_found then
421 goto write_subinv_error;
422 end;
423 ELSE
424 begin
425 select locator_type
426 into sub_loc_code
427 from mtl_secondary_inventories
428 where secondary_inventory_name = c1rec.CS
429 and organization_id = c1rec.OI
430 and nvl(disable_date,TRUNC(SYSDATE)+1) > TRUNC(SYSDATE)
431 and ((inv_asst = 'Y' and asset_inventory = 1 and
432 quantity_tracked = 1)
433 or
434 (inv_asst = 'N')
435 );
436 exception
437 when no_data_found then
438 goto write_subinv_error;
439 end;
440 END IF;
441 ELSE /* restricted subinventory */
442 IF (X_expense_to_asset_transfer = 1) THEN
443 begin
444 select locator_type
445 into sub_loc_code
446 from mtl_secondary_inventories sub,
447 mtl_item_sub_inventories item
448 where item.organization_id = sub.organization_id
449 and item.secondary_inventory = sub.secondary_inventory_name
450 and item.inventory_item_id = c1rec.AII
451 and sub.secondary_inventory_name = c1rec.CS
452 and sub.organization_id = c1rec.OI
453 and nvl(sub.disable_date,TRUNC(SYSDATE)+1) >
454 TRUNC(SYSDATE)
455 and sub.quantity_tracked = 1;
456 exception
457 when no_data_found then
458 goto write_subinv_error;
459 end;
460 ELSE
461 begin
462 select locator_type
463 into sub_loc_code
464 from mtl_secondary_inventories sub,
465 mtl_item_sub_inventories item
466 where item.organization_id = sub.organization_id
467 and item.secondary_inventory = sub.secondary_inventory_name
468 and item.inventory_item_id = c1rec.AII
469 and sub.secondary_inventory_name = c1rec.CS
470 and sub.organization_id = c1rec.OI
471 and nvl(sub.disable_date,TRUNC(SYSDATE)+1) >
472 TRUNC(SYSDATE)
473 and ((inv_asst = 'Y' and sub.asset_inventory = 1 and
474 sub.quantity_tracked = 1)
475 or
476 (inv_asst = 'N')
477 );
478 exception
479 when no_data_found then
480 goto write_subinv_error;
481 end;
482 END IF;
483 END IF;
484 /*
485 ** Validate locator
486 */
487 /* Org level */
488 select stock_locator_control_code
489 into org_loc
490 from mtl_parameters
491 where organization_id = c1rec.OI;
492
493 if (org_loc = 1) and (c1rec.CLI is not null) then
494 goto write_loc_error;
495 end if;
496
497 if ((org_loc = 2) or (org_loc = 3))and (c1rec.CLI is null) then
498 goto write_loc_error;
499 end if;
500
501 if ((org_loc = 2) or (org_loc = 3)) and (c1rec.CLI is not null) then
502 if (r_loc = 2) then /* non-restricted locator */
503 begin
504 select 'loc exists'
505 into dummy
506 from mtl_item_locations
507 where inventory_location_id = c1rec.CLI
508 and organization_id = c1rec.OI
509 and subinventory_code = c1rec.CS
510 and nvl(disable_date,trunc(SYSDATE)+1) > trunc(SYSDATE);
511 exception
512 when no_data_found then
513 goto write_loc_error;
514 end;
515 else /* restricted locator */
516 begin
517 select 'restricted loc exists'
518 into dummy
519 from mtl_item_locations loc,
520 mtl_secondary_locators item
521 where loc.inventory_location_id = c1rec.CLI
522 and loc.organization_id = c1rec.OI
523 and loc.subinventory_code = c1rec.CS
524 and nvl(loc.disable_date,trunc(SYSDATE)+1) >
525 trunc(SYSDATE)
529 exception
526 and loc.inventory_location_id = item.secondary_locator
527 and loc.organization_id = item.organization_id
528 and item.inventory_item_id = c1rec.AII;
530 when no_data_found then
531 goto write_loc_error;
532 end;
533 end if;
534 end if;
535
536 if (org_loc not in (1,2,3,4) and c1rec.CLI is not null) then
537 goto write_loc_error;
538 end if;
539
540 /* Subinv level */
541 if (org_loc = 4 and sub_loc_code = 1 and c1rec.CLI is not null) then
542 goto write_loc_error;
543 end if;
544
545 if (org_loc = 4) then
546 if ((sub_loc_code = 2) or (sub_loc_code = 3))
547 and (c1rec.CLI is null) then
548 goto write_loc_error;
549 end if;
550
551 if ((sub_loc_code = 2) or (sub_loc_code = 3))
552 and (c1rec.CLI is not null) then
553 if (r_loc = 2) then /* non-restricted locator */
554 begin
555 select 'loc exists'
556 into dummy
557 from mtl_item_locations
558 where inventory_location_id = c1rec.CLI
559 and organization_id = c1rec.OI
560 and subinventory_code = c1rec.CS
561 and nvl(disable_date,trunc(SYSDATE)+1) >
562 trunc(SYSDATE);
563 exception
564 when no_data_found then
565 goto write_loc_error;
566 end;
567 else /* restricted locator */
568 begin
569 select 'restricted loc exists'
570 into dummy
571 from mtl_item_locations loc,
572 mtl_secondary_locators item
573 where loc.inventory_location_id = c1rec.CLI
574 and loc.organization_id = c1rec.OI
575 and loc.subinventory_code = c1rec.CS
576 and nvl(loc.disable_date,trunc(SYSDATE)+1) >
577 trunc(SYSDATE)
578 and loc.inventory_location_id = item.secondary_locator
579 and loc.organization_id = item.organization_id
580 and item.inventory_item_id = c1rec.AII;
581 exception
582 when no_data_found then
583 goto write_loc_error;
584 end;
585 end if;
586 end if;
587
588 if (sub_loc_code not in (1,2,3,5) and c1rec.CLI is not null) then
589 goto write_loc_error;
590 end if;
591 end if;
592
593 /* Item level */
594 if (org_loc = 4 and sub_loc_code = 5 and loc_ctl = 1
595 and c1rec.CLI is not null) then
596 goto write_loc_error;
597 end if;
598
599 if (org_loc = 4 and sub_loc_code = 5) then
600 if ((loc_ctl = 2) or (loc_ctl = 3))
601 and (c1rec.CLI is null) then
602 goto write_loc_error;
603 end if;
604
605 if ((loc_ctl = 2) or (loc_ctl = 3))
606 and (c1rec.CLI is not null) then
607 if (r_loc = 2) then /* non-restricted locator */
608 begin
609 select 'loc exists'
610 into dummy
611 from mtl_item_locations
612 where inventory_location_id = c1rec.CLI
613 and organization_id = c1rec.OI
614 and subinventory_code = c1rec.CS
615 and nvl(disable_date,trunc(SYSDATE)+1) >
616 trunc(SYSDATE);
617 exception
618 when no_data_found then
619 goto write_loc_error;
620 end;
621 else /* restricted locator */
622 begin
623 select 'restricted loc exists'
624 into dummy
625 from mtl_item_locations loc,
626 mtl_secondary_locators item
627 where loc.inventory_location_id = c1rec.CLI
628 and loc.organization_id = c1rec.OI
629 and loc.subinventory_code = c1rec.CS
630 and nvl(loc.disable_date,trunc(SYSDATE)+1) >
631 trunc(SYSDATE)
632 and loc.inventory_location_id = item.secondary_locator
633 and loc.organization_id = item.organization_id
634 and item.inventory_item_id = c1rec.AII;
635 exception
636 when no_data_found then
637 goto write_loc_error;
638 end;
639 end if;
640 end if;
641
645 end if;
642 if (loc_ctl not in (1,2,3) and c1rec.CLI is not null) then
643 goto write_loc_error;
644 end if;
646
647 goto update_comp;
648
649 <<write_loc_error>>
650 ret_code := INVPUOPI.mtl_log_interface_err(
651 org_id => org_id,
652 user_id => user_id,
653 login_id => login_id,
654 prog_appid => prog_appid,
655 prog_id => prog_id,
656 req_id => request_id,
657 trans_id => c1rec.TI,
658 error_text => err_text,
659 tbl_name => 'BOM_OP_ROUTINGS_INTERFACE',
660 msg_name => 'BOM_LOCATOR_INVALID',
661 err_text => err_text);
662 update bom_op_routings_interface set
663 process_flag = 3
664 where transaction_id = c1rec.TI;
665
666 if (ret_code <> 0) then
667 return(ret_code);
668 end if;
669 goto continue_loop;
670
671 <<write_subinv_error>>
672 ret_code := INVPUOPI.mtl_log_interface_err(
673 org_id => org_id,
674 user_id => user_id,
675 login_id => login_id,
676 prog_appid => prog_appid,
677 prog_id => prog_id,
678 req_id => request_id,
679 trans_id => c1rec.TI,
680 error_text => err_text,
681 tbl_name => 'BOM_OP_ROUTINGS_INTERFACE',
682 msg_name => 'BOM_SUBINV_INVALID',
683 err_text => err_text);
684 update bom_op_routings_interface set
685 process_flag = 3
686 where transaction_id = c1rec.TI;
687
688 if (ret_code <> 0) then
689 return(ret_code);
690 end if;
691 goto continue_loop;
692
693 <<update_comp>>
694 stmt_num := 10;
695 update bom_op_routings_interface
696 set process_flag = 4
697 where transaction_id = c1rec.TI;
698
699 <<continue_loop>>
700 NULL;
701 end loop;
702 commit;
703
704 if (commit_cnt < (500 - 1)) then
705 continue_loop := FALSE;
706 end if;
707
708 end loop;
709
710 return(0);
711
712 EXCEPTION
713 when others then
714 err_text := 'BOMPVALR(bmvrtgh-' || stmt_num || ') ' || substrb(SQLERRM,1,60);
715 return(SQLCODE);
716 END bmvrtgh_validate_rtg_header;
717
718 /*------------------------ bmvopr_validate_operations -----------------------*/
719 /* NAME
720 bmvopr_validate_operations - validate operation data
721 DESCRIPTION
722 validate the operation data in the interface tables before loading
723 into production tables
724 REQUIRES
725 err_text out buffer to return error message
726 MODIFIES
727 MTL_INTERFACE_ERRORS
728 RETURNS
729 0 if successful
730 SQLCODE if unsuccessful
731 NOTES
732 -----------------------------------------------------------------------------*/
733 FUNCTION bmvopr_validate_operations (
734 org_id NUMBER,
735 all_org NUMBER := 2,
736 user_id NUMBER,
737 login_id NUMBER,
738 prog_appid NUMBER,
739 prog_id NUMBER,
740 request_id NUMBER,
741 err_text IN OUT NOCOPY VARCHAR2
742 )
743 return INTEGER
744 IS
745 ret_code NUMBER;
746 stmt_num NUMBER := 0;
747 commit_cnt NUMBER;
748 dummy VARCHAR2(40);
749 continue_loop BOOLEAN := TRUE;
750 cursor c1 is
751 select operation_sequence_id OSI, routing_sequence_id RSI,
752 department_id DI, count_point_type CPT,
753 backflush_flag BF, option_dependent_flag ODF,
754 minimum_transfer_quantity MTQ, standard_operation_id SOI,
755 transaction_id TI, operation_lead_time_percent OLTP,
756 to_char(effectivity_date,'YYYY/MM/DD HH24:MI:SS') ED,
757 to_char(disable_date,'YYYY/MM/DD HH24:MI:SS') DD,
758 /** Changed for bug 2647027
759 to_char(effectivity_date,'YYYY/MM/DD HH24:MI') ED,
760 to_char(disable_date,'YYYY/MM/DD HH24:MI') DD,
761 **/ operation_seq_num OSN,
762 organization_id OI
763 from bom_op_sequences_interface
764 where process_flag = 2
765 and rownum < 500;
766
767 BEGIN
768 while continue_loop loop
769 commit_cnt := 0;
770 for c1rec in c1 loop
771 /*
772 ** verify operation seq num is not null
773 */
774 commit_cnt := commit_cnt + 1;
775 stmt_num := 1;
776 if (c1rec.OSN is null) then
777 ret_code := INVPUOPI.mtl_log_interface_err(
778 org_id => NULL,
779 user_id => user_id,
780 login_id => login_id,
781 prog_appid => prog_appid,
782 prog_id => prog_id,
783 req_id => request_id,
784 trans_id => c1rec.TI,
788 err_text => err_text);
785 error_text => err_text,
786 tbl_name => 'BOM_OP_SEQUENCES_INTERFACE',
787 msg_name => 'BOM_OP_SEQ_NUM_MISSING',
789 update bom_op_sequences_interface set
790 process_flag = 3
791 where transaction_id = c1rec.TI;
792
793 if (ret_code <> 0) then
794 return(ret_code);
795 end if;
796 goto continue_loop;
797 end if;
798 /*
799 ** verify for uniqueness of operation seq ID
800 */
801 stmt_num := 2;
802 ret_code :=bmvunop_verify_unique_op (
803 op_seq_id => c1rec.OSI,
804 exist_flag => 2,
805 err_text => err_text);
806 if (ret_code <> 0) then
807 ret_code := INVPUOPI.mtl_log_interface_err(
808 org_id => NULL,
809 user_id => user_id,
810 login_id => login_id,
811 prog_appid => prog_appid,
812 prog_id => prog_id,
813 req_id => request_id,
814 trans_id => c1rec.TI,
815 error_text => err_text,
816 tbl_name => 'BOM_OP_SEQUENCES_INTERFACE',
817 msg_name => 'BOM_OP_SEQ_ID_DUPLICATE',
818 err_text => err_text);
819 update bom_op_sequences_interface set
820 process_flag = 3
821 where transaction_id = c1rec.TI;
822
823 if (ret_code <> 0) then
824 return(ret_code);
825 end if;
826 goto continue_loop;
827 end if;
828 /*
829 ** verify uniqueness of routing seq id, op seq num, and effectivity date
830 */
831 stmt_num := 3;
832 ret_code := BOMPVALR.bmvdupop_verify_duplicate_op (
833 rtg_seq_id => c1rec.RSI,
834 eff_date => c1rec.ED,
835 op_seq => c1rec.OSN,
836 err_text => err_text);
837 if (ret_code <> 0) then
838 ret_code := INVPUOPI.mtl_log_interface_err(
839 org_id => c1rec.OI,
840 user_id => user_id,
841 login_id => login_id,
842 prog_appid => prog_appid,
843 prog_id => prog_id,
844 req_id => request_id,
845 trans_id => c1rec.TI,
846 error_text => err_text,
847 tbl_name => 'BOM_OP_SEQUENCES_INTERFACE',
848 msg_name => 'BOM_OPERATION_DUPLICATE',
849 err_text => err_text);
850 update bom_op_sequences_interface set
851 process_flag = 3
852 where transaction_id = c1rec.TI;
853
854 if (ret_code <> 0) then
855 return(ret_code);
856 end if;
857 goto continue_loop;
858 end if;
859 /*
860 ** check for existence of routing
861 */
862 stmt_num := 4;
863 ret_code :=bmvurtg_verify_routing(
864 rtg_seq_id => c1rec.RSI,
865 mode_type => 2,
866 err_text => err_text);
867 if (ret_code <> 0) then
868 ret_code := INVPUOPI.mtl_log_interface_err(
869 org_id => NULL,
870 user_id => user_id,
871 login_id => login_id,
872 prog_appid => prog_appid,
873 prog_id => prog_id,
874 req_id => request_id,
875 trans_id => c1rec.TI,
876 error_text => err_text,
877 tbl_name => 'BOM_OP_SEQUENCES_INTERFACE',
878 msg_name => 'BOM_RTG_SEQ_INVALID',
879 err_text => err_text);
880 update bom_op_sequences_interface set
881 process_flag = 3
882 where transaction_id = c1rec.TI;
883
884 if (ret_code <> 0) then
885 return(ret_code);
886 end if;
887 goto continue_loop;
888 end if;
889 /*
890 ** make sure there is no overlapping operations
891 */
892 stmt_num := 5;
893 ret_code :=bmvovlap_verify_overlaps (
894 rtg_id => c1rec.RSI,
895 op_num => c1rec.OSN,
896 eff_date => c1rec.ED,
897 dis_date => c1rec.DD,
898 err_text => err_text);
899 if (ret_code <> 0) then
900 ret_code := INVPUOPI.mtl_log_interface_err(
901 org_id => NULL,
902 user_id => user_id,
903 login_id => login_id,
904 prog_appid => prog_appid,
905 prog_id => prog_id,
906 req_id => request_id,
907 trans_id => c1rec.TI,
908 error_text => err_text,
909 tbl_name => 'BOM_OP_SEQUENCES_INTERFACE',
910 msg_name => 'BOM_IMPL_OP_OVERLAP',
911 err_text => err_text);
912 update bom_op_sequences_interface set
913 process_flag = 3
914 where transaction_id = c1rec.TI;
915
916 if (ret_code <> 0) then
917 return(ret_code);
918 end if;
919 goto continue_loop;
920 end if;
921 /*
922 ** verify department is valid and enabled
923 */
924 stmt_num := 6;
925 ret_code := bmvdept_validate_department (
926 org_id => c1rec.OI,
927 dept_id => c1rec.DI,
928 eff_date => c1rec.ED,
929 err_text => err_text);
930 if (ret_code <> 0) then
931 ret_code := INVPUOPI.mtl_log_interface_err(
932 org_id => NULL,
933 user_id => user_id,
934 login_id => login_id,
935 prog_appid => prog_appid,
936 prog_id => prog_id,
937 req_id => request_id,
938 trans_id => c1rec.TI,
939 error_text => err_text,
940 tbl_name => 'BOM_OP_SEQUENCES_INTERFACE',
941 msg_name => 'BOM_DEPT_ID_INVALID',
942 err_text => err_text);
943 update bom_op_sequences_interface set
944 process_flag = 3
945 where transaction_id = c1rec.TI;
946
947 if (ret_code <> 0) then
948 return(ret_code);
949 end if;
950 goto continue_loop;
951 end if;
952 /*
953 ** verify that the routing does not have a common. If so, it cannot have
954 ** operations
955 */
956 stmt_num := 7;
957 begin
958 select 'Is pointing to a common'
959 into dummy
960 from bom_operational_routings
961 where routing_sequence_id = c1rec.RSI
962 and common_routing_sequence_id <> c1rec.RSI;
963
964 ret_code := INVPUOPI.mtl_log_interface_err(
965 org_id => c1rec.OI,
966 user_id => user_id,
967 login_id => login_id,
968 prog_appid => prog_appid,
969 prog_id => prog_id,
970 req_id => request_id,
971 trans_id => c1rec.TI,
972 error_text => err_text,
973 tbl_name => 'BOM_OP_SEQUENCES_INTERFACE',
974 msg_name => 'BOM_COMMON_OP',
975 err_text => err_text);
976 update bom_op_sequences_interface set
977 process_flag = 3
978 where transaction_id = c1rec.TI;
979
980 if (ret_code <> 0) then
981 return(ret_code);
982 end if;
983 goto continue_loop;
984 exception
985 when NO_DATA_FOUND then
986 NULL;
987 end;
988 begin
989 select 'Is pointing to a common'
990 into dummy
991 from bom_op_routings_interface
992 where routing_sequence_id = c1rec.RSI
993 and process_flag = 4
994 and common_routing_sequence_id <> c1rec.RSI;
995
996 ret_code := INVPUOPI.mtl_log_interface_err(
997 org_id => c1rec.OI,
998 user_id => user_id,
999 login_id => login_id,
1000 prog_appid => prog_appid,
1001 prog_id => prog_id,
1002 req_id => request_id,
1003 trans_id => c1rec.TI,
1004 error_text => err_text,
1005 tbl_name => 'BOM_OP_SEQUENCES_INTERFACE',
1006 msg_name => 'BOM_COMMON_OP',
1007 err_text => err_text);
1008 update bom_op_sequences_interface set
1009 process_flag = 3
1010 where transaction_id = c1rec.TI;
1011
1012 if (ret_code <> 0) then
1013 return(ret_code);
1014 end if;
1015 goto continue_loop;
1016
1017 exception
1018 when NO_DATA_FOUND then
1019 NULL;
1020 end;
1021 /*
1022 ** validate operation details
1023 */
1024 stmt_num := 8;
1025 err_text := NULL;
1026
1027 if (c1rec.MTQ < 0) then
1028 err_text := 'Minimum transfer quantity cannot be negative';
1029 end if;
1030
1031 if (to_date(c1rec.ED,'YYYY/MM/DD HH24:MI') >
1032 nvl(to_date(c1rec.DD,'YYYY/MM/DD HH24:MI'),
1033 to_date(c1rec.ED,'YYYY/MM/DD HH24:MI') + 1) ) then
1034 err_text := 'Effective date must be less than or equal to disable date';
1035 end if;
1036
1037 if (c1rec.CPT not in (1,2,3)) then
1038 err_text := 'COUNT_POINT_TYPE must be 1, 2, or 3';
1039 end if;
1040
1041 if (c1rec.BF not in (1,2)) then
1042 err_text := 'BACKFLUSH_FLAG must be 1 or 2';
1043 end if;
1044
1045 if (c1rec.ODF not in (1,2)) then
1046 err_text := 'OPTION_DEPENDENT_FLAG must be 1 or 2';
1047 end if;
1048
1049 if (c1rec.CPT = 3 and c1rec.BF <>1) then
1050 err_text := 'BACKFLUSH_FLAG must be Yes if COUNT_POINT_TYPE is No-direct charge';
1051 end if;
1052
1053 if (c1rec.OLTP not between 0 and 100) then
1054 err_text := 'OPERATION_LEAD_TIME_PERCENT must be between 0 and 100';
1055 end if;
1056
1057 if (err_text is not null) then
1058 ret_code := INVPUOPI.mtl_log_interface_err(
1059 org_id => c1rec.OI,
1060 user_id => user_id,
1061 login_id => login_id,
1062 prog_appid => prog_appid,
1063 prog_id => prog_id,
1064 req_id => request_id,
1065 trans_id => c1rec.TI,
1066 error_text => err_text,
1067 tbl_name => 'BOM_OP_SEQUENCES_INTERFACE',
1068 msg_name => 'BOM_OPERATION_ERROR',
1069 err_text => err_text);
1070 update bom_op_sequences_interface set
1071 process_flag = 3
1072 where transaction_id = c1rec.TI;
1073
1074 if (ret_code <> 0) then
1075 return(ret_code);
1076 end if;
1077 goto continue_loop;
1078 end if;
1079
1080 update bom_op_sequences_interface
1081 set process_flag = 4
1082 where transaction_id = c1rec.TI;
1083
1084 <<continue_loop>>
1085 NULL;
1086 end loop;
1087 commit;
1088
1089 if (commit_cnt < (500 - 1)) then
1090 continue_loop := FALSE;
1091 end if;
1092
1093 end loop;
1094
1095 return(0);
1096 EXCEPTION
1097 when others then
1098 err_text := 'BOMPVALR(bmvopr-' || stmt_num || ') ' || substrb(SQLERRM,1,60);
1099 return(SQLCODE);
1100 END bmvopr_validate_operations;
1101
1102 /*------------------------ bmvres_validate_resources ------------------------*/
1103 /* NAME
1104 bmvres_validate_resources - validates resources
1105 DESCRIPTION
1106 validate the resource data in the interface tables before loading
1107 into production tables
1108 verify if resource exists in department
1109 verify if resource seq num is unique
1110 verify if resoruce is enabled
1111 can schedule resource if time class UOM and conversion exists to
1112 hour UOM
1113 cannot have more than one 'Prior' or 'Next' schedule per op
1114 negative usage rates only for non-schedulable resources
1115 non-negative assigned units
1116 activity must be valid and enabled
1117 cannot have PO move or PO receipt if dept does not have location
1118 or resource does not have purchase item
1119 only one PO Move per operation
1120
1121 REQUIRES
1122 err_text out buffer to return error message
1123 MODIFIES
1124 MTL_INTERFACE_ERRORS
1125 RETURNS
1126 0 if successful
1127 SQLCODE if unsuccessful
1128 NOTES
1129 -----------------------------------------------------------------------------*/
1130 FUNCTION bmvres_validate_resources (
1131 org_id NUMBER,
1132 all_org NUMBER,
1133 user_id NUMBER,
1134 login_id NUMBER,
1135 prog_appid NUMBER,
1136 prog_id NUMBER,
1137 request_id NUMBER,
1138 err_text IN OUT NOCOPY VARCHAR2
1139 )
1140 return INTEGER
1141 IS
1142 cursor c1 is
1143 select operation_sequence_id OSI, transaction_id TI,
1144 organization_id OI
1145 from bom_op_resources_interface
1146 where process_flag = 2
1147 and rownum < 500
1148 group by transaction_id, operation_sequence_id, organization_id;
1149
1150 stmt_num NUMBER := 0;
1151 ret_code NUMBER;
1152 dept_id NUMBER;
1153 dummy_eff DATE;
1154 dummy_code VARCHAR2(3);
1155 res_cnt NUMBER := 0;
1156 dummy NUMBER;
1157 hr_uom_code VARCHAR2(30);
1158 hr_uom VARCHAR2(3);
1159 hr_uom_class MTL_UNITS_OF_MEASURE.UOM_CLASS%TYPE;
1160 commit_cnt NUMBER;
1161 continue_loop BOOLEAN := TRUE;
1162 total_recs NUMBER;
1163
1164 BEGIN
1165 /*
1166 ** check for null resource seq num
1167 */
1168 select count(distinct operation_sequence_id)
1169 into total_recs
1170 from bom_op_resources_interface
1171 where process_flag = 2;
1172
1173 continue_loop := TRUE;
1174 commit_cnt := 0;
1175
1176 while continue_loop loop
1177 for c1rec in c1 loop
1178 commit_cnt := commit_cnt + 1;
1179 select count(*)
1180 into dummy
1181 from bom_op_resources_interface
1182 where transaction_id = c1rec.TI
1183 and resource_seq_num is null;
1184
1185 if (dummy = 0) then
1186 NULL;
1187 else
1188 ret_code := INVPUOPI.mtl_log_interface_err(
1189 org_id => org_id,
1190 user_id => user_id,
1191 login_id => login_id,
1192 prog_appid => prog_appid,
1193 prog_id => prog_id,
1194 req_id => request_id,
1195 trans_id => c1rec.TI,
1196 error_text => err_text,
1197 tbl_name => 'BOM_OP_RESOURCES_INTERFACE',
1198 msg_name => 'BOM_NULL_RESOURCE_SEQ_NUM',
1199 err_text => err_text);
1200 update bom_op_resources_interface set
1201 process_flag = 3
1202 where transaction_id = c1rec.TI;
1203
1204 if (ret_code <> 0) then
1205 return(ret_code);
1206 end if;
1207 goto continue_loop;
1208 end if;
1209
1210 /*
1211 ** verify for existence of operation seq id
1212 */
1213 ret_code := bmvunop_verify_unique_op (
1214 op_seq_id => c1rec.OSI,
1215 exist_flag => 1,
1216 err_text => err_text);
1217 if (ret_code <> 0) then
1218 ret_code := INVPUOPI.mtl_log_interface_err(
1219 org_id => NULL,
1220 user_id => user_id,
1221 login_id => login_id,
1222 prog_appid => prog_appid,
1223 prog_id => prog_id,
1224 req_id => request_id,
1225 trans_id => c1rec.TI,
1226 error_text => err_text,
1227 tbl_name => 'BOM_OP_RESOURCES_INTERFACE',
1228 msg_name => 'BOM_OP_SEQ_INVALID',
1229 err_text => err_text);
1230 update bom_op_resources_interface set
1231 process_flag = 3
1232 where transaction_id = c1rec.TI;
1233
1234 if (ret_code <> 0) then
1235 return(ret_code);
1236 end if;
1237 goto continue_loop;
1238 end if;
1239
1240 /*
1241 ** get dept id and eff date. first bom interface table and if op not
1242 ** exists then from prod table.
1243 */
1244 begin
1245 select department_id, effectivity_date
1246 into dept_id, dummy_eff
1247 from bom_op_sequences_interface
1248 where operation_sequence_id = c1rec.OSI
1249 and process_flag = 4;
1250 exception
1251 when NO_DATA_FOUND then
1252 select department_id, effectivity_date
1253 into dept_id, dummy_eff
1254 from bom_operation_sequences
1255 where operation_sequence_id = c1rec.OSI;
1256 when others then
1257 err_text := 'BOMPVALR(bmvres) ' || substrb(SQLERRM,1,60);
1258 return(SQLCODE);
1259 end;
1260
1261 /*
1262 ** validate resource exists and is enabled and belongs to dept
1263 */
1264 select count(*)
1265 into res_cnt
1266 from bom_op_resources_interface ori
1267 where ori.operation_sequence_id = c1rec.OSI
1268 and ori.resource_id not in (select br.resource_id
1269 from bom_Resources br, bom_department_resources bdr
1270 where br.resource_id = ori.resource_id
1271 and nvl(br.disable_date, dummy_eff + 1) > dummy_eff
1272 and bdr.department_id = dept_id
1273 and bdr.resource_id = ori.resource_id);
1274 if (res_cnt <> 0) then
1275 ret_code := INVPUOPI.mtl_log_interface_err(
1276 org_id => NULL,
1277 user_id => user_id,
1278 login_id => login_id,
1279 prog_appid => prog_appid,
1280 prog_id => prog_id,
1281 req_id => request_id,
1282 trans_id => c1rec.TI,
1283 error_text => err_text,
1284 tbl_name => 'BOM_OP_RESOURCES_INTERFACE',
1285 msg_name => 'BOM_DEPT_RES_INVALID',
1286 err_text => err_text);
1287 update bom_op_resources_interface set
1288 process_flag = 3
1289 where transaction_id = c1rec.TI;
1290
1291 if (ret_code <> 0) then
1292 return(ret_code);
1293 end if;
1294 goto continue_loop;
1295 end if;
1296
1297 /*
1298 ** verify activity is enabled
1299 */
1300 select count(*)
1301 into res_cnt
1302 from bom_op_resources_interface ori
1303 where operation_Sequence_id = c1rec.OSI
1304 and activity_id is not null
1305 and activity_id not in (select activity_id
1306 from cst_activities ca
1307 where ca.activity_id = ori.activity_id
1308 and nvl(ca.organization_id, ori.organization_id)
1309 = ori.organization_id
1310 and nvl(ca.disable_date, dummy_eff + 1) > dummy_eff);
1311 if (res_cnt <> 0) then
1312 ret_code := INVPUOPI.mtl_log_interface_err(
1313 org_id => NULL,
1314 user_id => user_id,
1315 login_id => login_id,
1316 prog_appid => prog_appid,
1317 prog_id => prog_id,
1318 req_id => request_id,
1319 trans_id => c1rec.TI,
1320 error_text => err_text,
1321 tbl_name => 'BOM_OP_RESOURCES_INTERFACE',
1322 msg_name => 'BOM_ACTIVITY_ID_INVALID',
1323 err_text => err_text);
1324 update bom_op_resources_interface set
1325 process_flag = 3
1326 where transaction_id = c1rec.TI;
1327
1328 if (ret_code <> 0) then
1329 return(ret_code);
1330 end if;
1331 goto continue_loop;
1332 end if;
1333 /*
1334 ** check for duplicate resource seq num/operation sequence id
1335 */
1336 ret_code := bmvunres_verify_unique_res (
1337 trans_id => c1rec.TI,
1338 err_text => err_text);
1339 if (ret_code <> 0) then
1340 ret_code := INVPUOPI.mtl_log_interface_err(
1341 org_id => NULL,
1342 user_id => user_id,
1343 login_id => login_id,
1344 prog_appid => prog_appid,
1345 prog_id => prog_id,
1346 req_id => request_id,
1347 trans_id => c1rec.TI,
1348 error_text => err_text,
1349 tbl_name => 'BOM_OP_RESOURCES_INTERFACE',
1350 msg_name => 'BOM_DUPLICATE_RES_NUM',
1351 err_text => err_text);
1352 update bom_op_resources_interface set
1353 process_flag = 3
1354 where transaction_id = c1rec.TI;
1355
1356 if (ret_code <> 0) then
1357 return(ret_code);
1358 end if;
1359 goto continue_loop;
1360 end if;
1361
1362 /*
1363 ** Schedule must be No if any one of the following are true:
1364 ** 1) res uom = currency code 2) res uom class <> hour uom class
1365 ** 3) no conversion between resource uom and hour uom
1366 */
1367 /*
1368 ** get value of profile BOM:HOUR_UOM_CODE
1369 */
1370 BOMPRFIL.bom_pr_get_profile(
1371 appl_short_name => 'BOM',
1372 profile_name => 'BOM:HOUR_UOM_CODE',
1373 user_id => user_id,
1374 resp_appl_id => prog_appid,
1375 resp_id => 702,
1376 profile_value => hr_uom_code,
1377 return_code => ret_code,
1378 return_message => err_text);
1379 if (ret_code <> 0) then
1380 return(ret_code);
1381 end if;
1382
1383 /*
1384 ** get the hour_uom class
1385 */
1386 hr_uom := ltrim(rtrim(hr_uom_code));
1387
1388 select uom_class
1389 into hr_uom_class
1390 from mtl_units_of_measure
1391 where uom_code = hr_uom;
1392
1393 /* get currency code */
1394 select SUBSTRB(CURRENCY_CODE, 1, 3)
1395 into dummy_code
1396 from org_organization_definitions ood,
1397 gl_sets_of_books gsb
1398 where ood.organization_id = c1rec.OI
1399 and ood.set_of_books_id = gsb.set_of_books_id;
1400
1401 select count(*)
1402 into res_cnt
1403 from bom_op_resources_interface ori, bom_resources br,
1404 mtl_units_of_measure uom
1405 where ori.operation_sequence_id = c1rec.OSI
1406 and ori.schedule_flag = 1
1407 and ori.resource_id = br.resource_id
1408 and uom.uom_code = br.unit_of_measure
1409 and ((br.unit_of_measure = dummy_code)
1410 or
1411 (uom.uom_class <> hr_uom_class)
1412 or
1413 (not exists (select 'No conversion exists'
1414 from mtl_uom_conversions a,
1415 mtl_uom_conversions b
1416 where a.uom_code = uom.uom_code
1417 and a.uom_class = uom.uom_class
1418 and a.inventory_item_id = 0
1419 and nvl(a.disable_date, sysdate + 1) > sysdate
1420 and b.uom_code = hr_uom
1421 and b.inventory_item_id = 0
1422 and b.uom_class = hr_uom_class ))
1423 );
1424
1425 if (res_cnt > 0) then
1426 ret_code := INVPUOPI.mtl_log_interface_err(
1427 org_id => NULL,
1428 user_id => user_id,
1429 login_id => login_id,
1430 prog_appid => prog_appid,
1431 prog_id => prog_id,
1432 req_id => request_id,
1433 trans_id => c1rec.TI,
1434 error_text => err_text,
1435 tbl_name => 'BOM_OP_RESOURCES_INTERFACE',
1436 msg_name => 'BOM_OP_RES_SCHED_NO',
1437 err_text => err_text);
1438 update bom_op_resources_interface set
1439 process_flag = 3
1440 where transaction_id = c1rec.TI;
1441
1442 if (ret_code <> 0) then
1443 Return(ret_code);
1444 end if;
1445 goto continue_loop;
1446 end if;
1447
1448 /*
1449 ** cannot have more than one Next or Prior scheduled resource
1450 ** for an operation
1451 */
1452 ret_code := bmvrsch_verify_resource_sched (
1453 op_seq => c1rec.OSI,
1454 sched_type => 3, /* Prior */
1455 err_text => err_text);
1456 if (ret_code <> 0) then
1457 ret_code := INVPUOPI.mtl_log_interface_err(
1458 org_id => NULL,
1459 user_id => user_id,
1460 login_id => login_id,
1461 prog_appid => prog_appid,
1462 prog_id => prog_id,
1463 req_id => request_id,
1464 trans_id => c1rec.TI,
1465 error_text => err_text,
1466 tbl_name => 'BOM_OP_RESOURCES_INTERFACE',
1467 msg_name => 'BOM_OP_RES_PRIOR_ERROR',
1468 err_text => err_text);
1469 update bom_op_resources_interface set
1470 process_flag = 3
1471 where transaction_id = c1rec.TI;
1472
1473 if (ret_code <> 0) then
1474 return(ret_code);
1475 end if;
1476 goto continue_loop;
1477 end if;
1478
1479 ret_code := bmvrsch_verify_resource_sched (
1480 op_seq => c1rec.OSI,
1481 sched_type => 4, /* Next */
1482 err_text => err_text);
1483 if (ret_code <> 0) then
1484 ret_code := INVPUOPI.mtl_log_interface_err(
1485 org_id => NULL,
1486 user_id => user_id,
1487 login_id => login_id,
1488 prog_appid => prog_appid,
1489 prog_id => prog_id,
1490 req_id => request_id,
1491 trans_id => c1rec.TI,
1492 error_text => err_text,
1493 tbl_name => 'BOM_OP_RESOURCES_INTERFACE',
1494 msg_name => 'BOM_OP_RES_NEXT_ERROR',
1495 err_text => err_text);
1496 update bom_op_resources_interface set
1497 process_flag = 3
1498 where transaction_id = c1rec.TI;
1499
1500 if (ret_code <> 0) then
1501 return(ret_code);
1502 end if;
1503 goto continue_loop;
1504 end if;
1505
1506 /*
1507 ** cannot have negative usage rate if one of the following is true:
1508 ** 1) autocharge_type = 3 or 4 2) res uom class = hour_uom_class
1509 */
1510 select count(*)
1511 into res_cnt
1512 from bom_op_resources_interface bori
1513 where operation_sequence_id = c1rec.OSI
1514 and process_flag <> 3 and process_flag <> 7
1515 and usage_rate_or_amount < 0
1516 and (autocharge_type in (3,4)
1517 or
1518 (hr_uom_class in
1519 (select uom_class
1520 from mtl_units_of_measure mum,
1521 bom_resources br
1522 where br.resource_id = bori.resource_id
1523 and mum.uom_code = br.unit_of_measure))
1524 );
1525 if (res_cnt <> 0) then
1526 ret_code := INVPUOPI.mtl_log_interface_err(
1527 org_id => NULL,
1528 user_id => user_id,
1529 login_id => login_id,
1530 prog_appid => prog_appid,
1531 prog_id => prog_id,
1532 req_id => request_id,
1533 trans_id => c1rec.TI,
1534 error_text => err_text,
1535 tbl_name => 'BOM_OP_RESOURCES_INTERFACE',
1536 msg_name => 'BOM_NEGATIVE_USAGE_RATE',
1537 err_text => err_text);
1538 update bom_op_resources_interface set
1539 process_flag = 3
1540 where transaction_id = c1rec.TI;
1541
1542 if (ret_code <> 0) then
1543 return(ret_code);
1544 end if;
1545 goto continue_loop;
1546 end if;
1547
1548 /*
1549 ** assigned units cannot be less than or equal to .00001
1550 ** if resource is available 24 hours then assigned units must be 1
1551 ** (verified in ASSIGN)
1552 */
1553 select count(*)
1554 into res_cnt
1555 from bom_op_resources_interface ori
1556 where operation_sequence_id = c1rec.OSI
1557 and assigned_units <= .00001;
1558
1559 if (res_cnt <> 0) then
1560 ret_code := INVPUOPI.mtl_log_interface_err(
1561 org_id => NULL,
1562 user_id => user_id,
1563 login_id => login_id,
1564 prog_appid => prog_appid,
1565 prog_id => prog_id,
1566 req_id => request_id,
1567 trans_id => c1rec.TI,
1568 error_text => err_text,
1569 tbl_name => 'BOM_OP_RESOURCES_INTERFACE',
1570 msg_name => 'BOM_ASSIGNED_UNIT_ERROR',
1571 err_text => err_text);
1572 update bom_op_resources_interface set
1573 process_flag = 3
1574 where transaction_id = c1rec.TI;
1575
1576 if (ret_code <> 0) then
1577 return(ret_code);
1578 end if;
1579 goto continue_loop;
1580 end if;
1581
1582 /*
1583 ** check if basis type,standard rate flag, schedule flag
1584 ** and autocharge type are valid
1585 */
1586 select count(*)
1587 into res_cnt
1588 from bom_op_resources_interface ori
1589 where operation_sequence_id = c1rec.OSI
1590 and ( (basis_type not in (1,2))
1591 or
1592 (standard_rate_flag not in (1, 2))
1593 or
1594 (schedule_flag not in (1,2,3,4))
1595 or
1596 (autocharge_type not in (1,2,3,4))
1597 );
1598 if (res_cnt <> 0) then
1599 ret_code := INVPUOPI.mtl_log_interface_err(
1600 org_id => NULL,
1601 user_id => user_id,
1602 login_id => login_id,
1603 prog_appid => prog_appid,
1604 prog_id => prog_id,
1605 req_id => request_id,
1606 trans_id => c1rec.TI,
1607 error_text => err_text,
1608 tbl_name => 'BOM_OP_RESOURCES_INTERFACE',
1609 msg_name => 'BOM_OP_RES_LOOKUP_ERROR',
1610 err_text => err_text);
1611 update bom_op_resources_interface set
1612 process_flag = 3
1613 where transaction_id = c1rec.TI;
1614
1615 if (ret_code <> 0) then
1616 return(ret_code);
1617 end if;
1618 goto continue_loop;
1619 end if;
1620 /*
1621 ** Only one PO move per operation
1622 ** Autocharge cannot be PO Move or PO Receipt if
1623 ** the department has no location
1624 */
1625 ret_code := BOMPVALR.bmvauto_verify_autocharge (
1626 op_seq => c1rec.OSI,
1627 dept_id => dept_id,
1628 err_text => err_text);
1629 if (ret_code <> 0) then
1630 ret_code := INVPUOPI.mtl_log_interface_err(
1631 org_id => NULL,
1632 user_id => user_id,
1633 login_id => login_id,
1634 prog_appid => prog_appid,
1635 prog_id => prog_id,
1636 req_id => request_id,
1637 trans_id => c1rec.TI,
1638 error_text => err_text,
1639 tbl_name => 'BOM_OP_RESOURCES_INTERFACE',
1640 msg_name => 'BOM_AUTOCHARGE_INVALID',
1641 err_text => err_text);
1642 update bom_op_resources_interface set
1643 process_flag = 3
1644 where transaction_id = c1rec.TI;
1645
1646 if (ret_code <> 0) then
1647 return(ret_code);
1648 end if;
1649 goto continue_loop;
1650 end if;
1651
1652 /* Check offset percent */
1653 select count(*)
1654 into res_cnt
1655 from bom_op_resources_interface ori
1656 where operation_sequence_id = c1rec.OSI
1657 and resource_offset_percent not between 0 and 100
1658 and resource_offset_percent is not null;
1659
1660 if (res_cnt <> 0) then
1661 ret_code := INVPUOPI.mtl_log_interface_err(
1662 org_id => NULL,
1663 user_id => user_id,
1664 login_id => login_id,
1665 prog_appid => prog_appid,
1666 prog_id => prog_id,
1667 req_id => request_id,
1668 trans_id => c1rec.TI,
1669 error_text => err_text,
1670 tbl_name => 'BOM_OP_RESOURCES_INTERFACE',
1671 msg_name => 'BOM_OFFSET_PERCENT_INVALID',
1672 err_text => err_text);
1673 update bom_op_resources_interface set
1674 process_flag = 3
1675 where transaction_id = c1rec.TI;
1676
1677 if (ret_code <> 0) then
1678 return(ret_code);
1679 end if;
1680 goto continue_loop;
1681 end if;
1682 /*
1683 ** Check usage rate and usage rate inverse -- Bug 7322996
1684 */
1685 select count(*)
1686 into res_cnt
1687 from bom_op_resources_interface ori
1688 where operation_sequence_id = c1rec.OSI
1689 and round(usage_rate_or_amount,G_round_off_val) <>
1690 decode(usage_rate_or_amount_inverse,0,0,
1691 round((1/usage_rate_or_amount_inverse),G_round_off_val)
1692 );
1693
1694 if (res_cnt <> 0) then
1695 ret_code := INVPUOPI.mtl_log_interface_err(
1696 org_id => NULL,
1697 user_id => user_id,
1698 login_id => login_id,
1699 prog_appid => prog_appid,
1700 prog_id => prog_id,
1701 req_id => request_id,
1702 trans_id => c1rec.TI,
1703 error_text => err_text,
1704 tbl_name => 'BOM_OP_RESOURCES_INTERFACE',
1705 msg_name => 'BOM_USAGE_RATE_INVALID',
1706 err_text => err_text);
1707 update bom_op_resources_interface set
1708 process_flag = 3
1709 where transaction_id = c1rec.TI;
1710
1711 if (ret_code <> 0) then
1712 return(ret_code);
1713 end if;
1714 goto continue_loop;
1715 end if;
1716
1717 update bom_op_resources_interface
1718 set process_flag = 4
1719 where transaction_id = c1rec.TI;
1720
1721 <<continue_loop>>
1722 NULL;
1723 end loop;
1724
1725 commit;
1726
1727 if (commit_cnt < total_recs) then
1728 null;
1729 else
1730 continue_loop := FALSE;
1731 end if;
1732
1733 end loop;
1734
1735 return(0);
1736 EXCEPTION
1737 when others then
1738 err_text := 'BOMPVALR(bmvres-' || stmt_num || ') ' || substrb(SQLERRM,1,60);
1739 return(SQLCODE);
1740 END bmvres_validate_resources;
1741
1742 /*---------------------- bmvunres_verify_unique_res -------------------------*/
1743 /* NAME
1744 bmvunres_verify_unique_res - verify that the op resource is unique
1745 DESCRIPTION
1746 verify that the op resource is unique in both prod and
1747 interface tables for any operation on a routing
1748
1749 REQUIRES
1750 trans_id transaction_id
1751 err_text IN OUT NOCOPY buffer to return error message
1752 MODIFIES
1753 RETURNS
1754 0 if successful
1755 SQLCODE if unsuccessful
1756 NOTES
1757 -----------------------------------------------------------------------------*/
1758 FUNCTION bmvunres_verify_unique_res (
1759 trans_id NUMBER,
1760 err_text IN OUT NOCOPY VARCHAR2
1761 )
1762 return INTEGER
1763 IS
1764 dummy NUMBER;
1765 NOT_UNIQUE EXCEPTION;
1766 BEGIN
1767 /*
1768 ** first check in prod tables
1769 */
1770 begin
1771 select 1
1772 into dummy
1773 from bom_operation_resources a, bom_op_resources_interface b
1774 where b.transaction_id = trans_id
1775 and a.operation_sequence_id = b.operation_sequence_id
1776 and a.resource_seq_num =
1777 b.resource_seq_num
1778 and rownum = 1;
1779 raise NOT_UNIQUE;
1780 exception
1781 when NO_DATA_FOUND then
1782 null;
1783 when NOT_UNIQUE then
1784 raise NOT_UNIQUE;
1785 when others then
1786 err_text := 'BOMPVALR(bmvunres) ' || substrb(SQLERRM,1,60);
1787 return(SQLCODE);
1788 end;
1789
1790 /*
1791 ** check in interface table
1792 */
1793 select count(*)
1794 into dummy
1795 from bom_op_resources_interface a
1796 where transaction_id = trans_id
1797 and exists (select 'same resource'
1798 from bom_op_resources_interface b
1799 where b.transaction_id = trans_id
1800 and b.rowid <> a.rowid
1801 and b.resource_seq_num =
1802 a.resource_seq_num
1803 and b.process_flag <> 3
1804 and b.process_flag <> 7)
1805 and process_flag <> 3
1806 and process_flag <> 7;
1807
1808 if (dummy > 0) then
1809 raise NOT_UNIQUE;
1810 else
1811 return(0);
1812 end if;
1813 exception
1814 when NOT_UNIQUE then
1815 err_text := 'BOMPVALR(bmvunres) ' ||'Duplicate resource seq nums';
1816 return(9999);
1817 when others then
1818 err_text := 'BOMPVALR(bmvunres) ' || substrb(SQLERRM,1,60);
1819 return(SQLCODE);
1820 end bmvunres_verify_unique_res;
1821
1822 /*------------------------ bmvrtgrev_validate_rtg_rev -----------------------*/
1823 /* NAME
1824 bmvrtgrev_validate_rtg_rev - validate the routing rev interface table
1825 DESCRIPTION
1826 validate revs
1827 - ensure revs in ascending order
1828 - no duplicate revs
1829 REQUIRES
1830 org_id org id to validate
1831 all_org all_org flag
1832 user_id user id
1833 login_id login id
1834 prog_appid program application id
1835 prod_id program id
1836 req_id request id
1837 err_text IN OUT NOCOPY buffer to return error message
1838 MODIFIES
1839 RETURNS
1840 0 if successful
1841 SQLCODE if unsuccessful
1842 NOTES
1843 -----------------------------------------------------------------------------*/
1844 FUNCTION bmvrtgrev_validate_rtg_rev (
1845 org_id NUMBER,
1846 all_org NUMBER,
1847 user_id NUMBER,
1848 login_id NUMBER,
1849 prog_appid NUMBER,
1850 prog_id NUMBER,
1851 req_id NUMBER,
1852 err_text IN OUT NOCOPY VARCHAR2
1853 )
1854 return INTEGER
1855 IS
1856 cursor c0 is
1857 select inventory_item_id AII, organization_id OI,
1858 process_revision PR, transaction_id TI
1859 from mtl_rtg_item_revs_interface
1860 where process_flag = 2
1861 and rownum < 500;
1862
1863 cursor c1 is
1864 select inventory_item_id AII, organization_id OI
1865 from mtl_rtg_item_revs_interface
1866 where process_flag = 99
1867 and rownum < 500
1868 group by organization_id, inventory_item_id;
1869
1870 cursor c2 is
1871 select 'x'
1872 from mtl_rtg_item_revs_interface
1873 where process_flag = 99
1874 group by organization_id, inventory_item_id;
1875
1876 ret_code NUMBER;
1877 dummy NUMBER;
1878 dummy_id NUMBER;
1879 stmt_num NUMBER;
1880 commit_cnt NUMBER;
1881 dummy_rtg NUMBER;
1882 continue_loop BOOLEAN := TRUE;
1883 total_recs NUMBER;
1884
1885 BEGIN
1886 /*
1887 ** Check if process revision is null
1888 */
1889 while continue_loop loop
1890 commit_cnt := 0;
1891 for c0rec in c0 loop
1892 commit_cnt := commit_cnt + 1;
1893 stmt_num := 1;
1894 if (c0rec.PR is null) then
1895 ret_code := INVPUOPI.mtl_log_interface_err(
1896 org_id => org_id,
1897 user_id => user_id,
1898 login_id => login_id,
1899 prog_appid => prog_appid,
1900 prog_id => prog_id,
1901 req_id => req_id,
1902 trans_id => c0rec.TI,
1903 error_text => err_text,
1904 tbl_name => 'MTL_RTG_ITEM_REVS_INTERFACE',
1905 msg_name => 'BOM_NULL_RTG_REV',
1906 err_text => err_text);
1907 update mtl_rtg_item_revs_interface set
1908 process_flag = 3
1909 where transaction_id = c0rec.TI;
1910
1911 if (ret_code <> 0) then
1912 return(ret_code);
1913 end if;
1914 goto continue_loop;
1915 end if;
1916 /*
1917 ** Check for valid org id
1918 */
1919 stmt_num := 2;
1920 BEGIN
1921 select organization_id
1922 into dummy_id
1923 from mtl_parameters
1924 where organization_id = c0rec.OI;
1925 EXCEPTION
1926 when NO_DATA_FOUND then
1927 ret_code := INVPUOPI.mtl_log_interface_err(
1928 org_id => c0rec.OI,
1929 user_id => user_id,
1930 login_id => login_id,
1931 prog_appid => prog_appid,
1932 prog_id => prog_id,
1933 req_id => req_id,
1934 trans_id => c0rec.TI,
1935 error_text => err_text,
1936 tbl_name => 'MTL_RTG_ITEM_REVS_INTERFACE',
1937 msg_name => 'BOM_INVALID_ORG_ID',
1938 err_text => err_text);
1939 update mtl_rtg_item_revs_interface set
1940 process_flag = 3
1941 where transaction_id = c0rec.TI;
1942 if (ret_code <> 0) then
1943 return(ret_code);
1944 end if;
1945 goto continue_loop;
1946 END;
1947
1948 /* Check if assembly item exists */
1949 stmt_num := 3;
1950 ret_code := BOMPVALB.bmvassyid_verify_assembly_id(
1951 org_id => c0rec.OI,
1952 assy_id => c0rec.AII,
1953 err_text => err_text);
1954 if (ret_code <> 0) then
1955 ret_code := INVPUOPI.mtl_log_interface_err(
1956 org_id => c0rec.OI,
1957 user_id => user_id,
1958 login_id => login_id,
1959 prog_appid => prog_appid,
1960 prog_id => prog_id,
1961 req_id => req_id,
1962 trans_id => c0rec.TI,
1963 error_text => err_text,
1964 tbl_name => 'MTL_RTG_ITEM_REVS_INTERFACE',
1965 msg_name => 'BOM_INV_ITEM_INVALID',
1966 err_text => err_text);
1967 update mtl_rtg_item_revs_interface set
1968 process_flag = 3
1969 where transaction_id = c0rec.TI;
1970
1971 if (ret_code <> 0) then
1972 return(ret_code);
1973 end if;
1974 goto continue_loop;
1975 end if;
1976 /*
1977 ** check if a valid routing exists for this revision
1978 */
1979 dummy_rtg := 0;
1980
1981 select count(*)
1982 into dummy_rtg
1983 from bom_operational_routings
1984 where organization_id = c0rec.OI
1985 and assembly_item_id = c0rec.AII;
1986
1987 if (dummy_rtg = 0) then
1988 select count(*)
1989 into dummy_rtg
1990 from bom_op_routings_interface
1991 where process_flag = 4
1992 and organization_id = c0rec.OI
1993 and assembly_item_id = c0rec.AII;
1994
1995 if (dummy_rtg = 0) then
1996 ret_code := INVPUOPI.mtl_log_interface_err(
1997 org_id => c0rec.OI,
1998 user_id => user_id,
1999 login_id => login_id,
2000 prog_appid => prog_appid,
2001 prog_id => prog_id,
2002 req_id => req_id,
2003 trans_id => c0rec.TI,
2004 error_text => err_text,
2005 tbl_name => 'MTL_RTG_ITEM_REVS_INTERFACE',
2006 msg_name => 'BOM_RTG_DOES_NOT_EXIST',
2007 err_text => err_text);
2008 update mtl_rtg_item_revs_interface set
2009 process_flag = 3
2010 where transaction_id = c0rec.TI;
2011
2012 if (ret_code <> 0) then
2013 return(ret_code);
2014 end if;
2015 goto continue_loop;
2016 end if;
2017 end if;
2018
2019 update mtl_rtg_item_revs_interface set
2020 process_flag = 99
2021 where transaction_id = c0rec.TI;
2022
2023 <<continue_loop>>
2024 NULL;
2025 end loop;
2026 commit;
2027
2028 if (commit_cnt < (500 - 1)) then
2029 continue_loop := FALSE;
2030 end if;
2031
2032 end loop;
2033
2034 total_recs := 0;
2035 for c2rec in c2 loop
2036 total_recs := total_recs + 1;
2037 end loop;
2038
2039 continue_loop := TRUE;
2040 commit_cnt := 0;
2041
2042 while continue_loop loop
2043 for c1rec in c1 loop
2044 commit_cnt := commit_cnt + 1;
2045 stmt_num := 4;
2046 ret_code := BOMPVALR.bmvrev_validate_rev (
2047 org_id => c1rec.OI,
2048 assy_id => c1rec.AII,
2049 user_id => user_id,
2050 login_id => login_id,
2051 prog_appid => prog_appid,
2052 prog_id => prog_id,
2053 req_id => req_id,
2054 err_text => err_text);
2055 if (ret_code <> 0) then
2056 return(ret_code);
2057 end if;
2058
2059 <<continue_loop>>
2060 NULL;
2061 end loop;
2062
2063 commit;
2064 if (commit_cnt < total_recs) then
2065 null;
2066 else
2067 continue_loop := FALSE;
2068 end if;
2069
2070 end loop;
2071
2072 return(0);
2073
2074 EXCEPTION
2075 when others then
2076 err_text := 'BOMPVALR(bmvrtgrev)' || substrb(SQLERRM,1,60);
2077 return(SQLCODE);
2078 END bmvrtgrev_validate_rtg_rev;
2079
2080 /*--------------------------- bmvrev_validate_rev ---------------------------*/
2081 /* NAME
2082 bmvrev_validate_rev - validate routing rev
2083 DESCRIPTION
2084 validate revs
2085 - ensure revs in ascending order
2086 - no duplicate revs
2087 REQUIRES
2088 org_id NUMBER,
2089 assy_id NUMBER,
2090 err_text IN OUT NOCOPY buffer to return error message
2091 MODIFIES
2092 RETURNS
2093 0 if successful
2094 SQLCODE if unsuccessful
2095 NOTES
2096 -----------------------------------------------------------------------------*/
2097 FUNCTION bmvrev_validate_rev (
2098 org_id NUMBER,
2099 assy_id NUMBER,
2100 user_id NUMBER,
2101 login_id NUMBER,
2102 prog_appid NUMBER,
2103 prog_id NUMBER,
2104 req_id NUMBER,
2105 err_text IN OUT NOCOPY VARCHAR2
2106 )
2107 return INTEGER
2108 IS
2109 cursor c1 is
2110 select process_revision PR, effectivity_date ED,
2111 transaction_id TI
2112 from mtl_rtg_item_revs_interface
2113 where organization_id = org_id
2114 and inventory_item_id = assy_id
2115 and process_flag = 99;
2116 ret_code NUMBER;
2117 err_cnt NUMBER;
2118 err_flag NUMBER;
2119 stmt_num NUMBER;
2120 BEGIN
2121 for c1rec in c1 loop
2122 err_cnt := 0;
2123 stmt_num := 1;
2124 /*
2125 ** check for ascending order and identical revs
2126 */
2127 select count(*)
2128 into err_cnt
2129 from mtl_rtg_item_revs_interface a
2130 where transaction_id <> c1rec.TI
2131 and inventory_item_id = assy_id
2132 and organization_id = org_id
2133 and process_flag = 4
2134 and ( (process_revision = c1rec.PR)
2135 or
2136 (effectivity_date > c1rec.ED
2137 and process_revision < c1rec.PR)
2138 or
2139 (effectivity_date < c1rec.ED
2140 and process_revision > c1rec.PR)
2141 );
2142
2143 if (err_cnt <> 0) then
2144 goto write_error;
2145 end if;
2146
2147 stmt_num := 2;
2148 select count(*)
2149 into err_cnt
2150 from mtl_rtg_item_revisions
2151 where inventory_item_id = assy_id
2152 and organization_id = org_id
2153 and ( (process_revision = c1rec.PR)
2154 or
2155 (effectivity_date > c1rec.ED
2156 and process_revision < c1rec.PR)
2157 or
2158 (effectivity_date < c1rec.ED
2159 and process_revision > c1rec.PR)
2160 );
2161
2162 if (err_cnt <> 0) then
2163 goto write_error;
2164 end if;
2165
2166 stmt_num := 3;
2167 update mtl_rtg_item_revs_interface set
2168 process_flag = 4
2169 where transaction_id = c1rec.TI;
2170 goto continue_loop;
2171
2172 <<write_error>>
2173 ret_code := INVPUOPI.mtl_log_interface_err(
2174 org_id => org_id,
2175 user_id => user_id,
2176 login_id => login_id,
2177 prog_appid => prog_appid,
2178 prog_id => prog_id,
2179 req_id => req_id,
2180 trans_id => c1rec.TI,
2181 error_text => err_text,
2182 tbl_name => 'MTL_RTG_ITEM_REVS_INTERFACE',
2183 msg_name => 'BOM_REV_INVALID',
2184 err_text => err_text);
2185 update mtl_rtg_item_revs_interface set
2186 process_flag = 3
2187 where transaction_id = c1rec.TI;
2188 <<continue_loop>>
2189 null;
2190 end loop;
2191 return(0);
2192 exception
2193 when others then
2194 err_text := 'BOMPVALR(bmvrev(' || stmt_num || ') ' || substrb(SQLERRM,1,60);
2195 return(SQLCODE);
2196 END bmvrev_validate_rev;
2197
2198
2199 /*--------------------- bmvcmrtg_verify_common_routing ----------------------*/
2200 /* NAME
2201 bmvcmrtg_verify_common_routing- verify common_routing
2202 DESCRIPTION
2203 if routing is mfg then it cannot point to engineering routing
2204 Common routing's alt must be same as current routing's alt
2205 Common routing cannot have same assembly_item_id/org_id as current routing
2206 Common routing cannot reference a common routing
2207
2208 REQUIRES
2209 rtg_id routing_sequence_id
2210 cmn_rtg_id common routing_seqience_id
2211 rtg_type routing_type
2212 err_text out buffer to return error message
2213 MODIFIES
2214 RETURNS
2215 0 if successful
2216 9999 if invalid item
2217 SQLCODE if error
2218 NOTES
2219 -----------------------------------------------------------------------------*/
2220 FUNCTION bmvcmrtg_verify_common_routing (
2221 rtg_id NUMBER,
2222 cmn_rtg_id NUMBER,
2223 rtg_type NUMBER,
2224 item_id NUMBER,
2225 org_id NUMBER,
2226 alt_desg VARCHAR2,
2227 err_text IN OUT NOCOPY VARCHAR2
2228 )
2229 return INTEGER
2230 IS
2231 cnt NUMBER;
2232
2233 BEGIN
2234 /*
2235 ** Common routing's alt must be same as current routing's alt
2236 ** Common routing cannot have same assembly_item_id as current routing
2237 ** Common routing must have the same org id as current routing
2238 ** Common routing must be mfg routing if current routing is a mfg routing
2239 ** Common routing cannot reference a common routing
2240 */
2241 begin
2242 select routing_sequence_id
2243 into cnt
2244 from bom_operational_routings
2245 where routing_sequence_id = cmn_rtg_id
2246 and nvl(alternate_routing_designator, 'NONE') =
2247 nvl(alt_desg, 'NONE')
2248 and common_routing_sequence_id = routing_sequence_id
2249 and assembly_item_id <> item_id
2250 and organization_id = org_id
2251 and ((rtg_type <> 1)
2252 or
2253 (rtg_type = 1
2254 and
2255 routing_type = 1
2256 )
2257 );
2258 goto check_ops;
2259 exception
2260 when NO_DATA_FOUND then
2261 NULL;
2262 when others then
2263 err_text := 'BOMPVALR(bmvcmrtg) ' || substrb(SQLERRM,1,60);
2264 return(SQLCODE);
2265 end;
2266
2267 select routing_sequence_id
2268 into cnt
2269 from bom_op_routings_interface
2270 where routing_sequence_id = cmn_rtg_id
2271 and nvl(alternate_routing_designator, 'NONE') =
2272 nvl(alt_desg, 'NONE')
2273 and common_routing_sequence_id = routing_sequence_id
2274 and assembly_item_id <> item_id
2275 and organization_id = org_id
2276 and process_flag = 4
2277 and ((rtg_type <> 1)
2278 or
2279 (rtg_type = 1
2280 and
2281 routing_type = 1
2282 )
2283 );
2284 <<check_ops>>
2285 return(0);
2286 EXCEPTION
2287 when NO_DATA_FOUND then
2288 err_text := 'BOMPVALR(bmvmrtg):Invalid common routing';
2289 return(9999);
2290 when others then
2291 err_text := 'BOMPVALR(bmcvmrtg) ' || substrb(SQLERRM,1,60);
2292 return(SQLCODE);
2293 END bmvcmrtg_verify_common_routing;
2294
2295 /*------------------------ bmvrtg_verify_rtg_type ---------------------------*/
2296 /* NAME
2297 bmvrtg_verify_rtg_type - verify routing type
2298 DESCRIPTION
2299 a routing can be defined only if the bom_enabled_flag = 'Y' and
2300 bom_item_type <> 3 (ie not a planning item). Also verifies
2301 if routing_type = mfg, then item must also be mfg
2302
2303 REQUIRES
2304 rtg_type routing_type
2305 org_id organization_id
2306 assy_id assembly_item_id
2307 err_text out buffer to return error message
2308 MODIFIES
2309 RETURNS
2310 0 if successful
2311 9999 if invalid item
2312 SQLCODE if error
2313 NOTES
2314 -----------------------------------------------------------------------------*/
2315 FUNCTION bmvrtg_verify_rtg_type (
2316 org_id NUMBER,
2317 assy_id NUMBER,
2318 rtg_type NUMBER,
2319 err_text IN OUT NOCOPY VARCHAR2
2320 )
2321 return INTEGER
2322 IS
2323 cnt NUMBER := 0;
2324 BEGIN
2325 select 1
2326 into cnt
2327 from mtl_system_items
2328 where organization_id = org_id
2329 and inventory_item_id = assy_id
2330 and bom_item_type <> 3
2331 and bom_enabled_flag = 'Y'
2332 and pick_components_flag = 'N'
2333 and ((rtg_type = 2)
2334 or
2335 (rtg_type = 1
2336 and
2337 eng_item_flag = 'N')
2338 );
2339 return(0);
2340
2341 EXCEPTION
2342 when NO_DATA_FOUND then
2343 err_text := 'BOMPVALR(bmvrtg):Invalid routing type or item attribute';
2344 return(9999);
2345 when others then
2346 err_text := 'BOMPVALR(bmvrtg) ' || substrb(SQLERRM,1,60);
2347 return(SQLCODE);
2348
2349 END bmvrtg_verify_rtg_type;
2350
2351 /*------------------------- bmvurtg_verify_routing --------------------------*/
2352 /* NAME
2353 bmvurtg_verify_routing - verify for uniqueness or existence of routing
2354 sequence id
2355 DESCRIPTION
2356 verifies if the given routing sequence id is unique in prod and
2357 interface tables
2358
2359 REQUIRES
2360 rtg_sq_id routing_sequecne_id
2361 mode_type 1 - verify uniqueness of routing
2362 2 - verify existence of routing
2363 err_text out buffer to return error message
2364 MODIFIES
2365 RETURNS
2366 0 if successful
2367 count of routings with same routing_sequence_id if any found
2368 SQLCODE if error
2369 NOTES
2370 -----------------------------------------------------------------------------*/
2371 FUNCTION bmvurtg_verify_routing (
2372 rtg_seq_id NUMBER,
2373 mode_type NUMBER,
2374 err_text IN OUT NOCOPY VARCHAR2
2375 )
2376 return INTEGER
2377 IS
2378 cnt NUMBER := 0;
2379 NOT_UNIQUE EXCEPTION;
2380 BEGIN
2381
2382 /*
2383 ** first check in production tables
2384 */
2385 begin
2386 select routing_sequence_id
2387 into cnt
2388 from bom_operational_routings
2389 where routing_sequence_id = rtg_seq_id;
2390 if (mode_type = 2) then
2391 return(0);
2392 else
2393 raise NOT_UNIQUE;
2394 end if;
2395 exception
2396 when NO_DATA_FOUND then
2397 NULL;
2398 when NOT_UNIQUE then
2399 raise NOT_UNIQUE;
2400 when others then
2401 err_text := 'BOMPVALR(bmvurtg) ' || substrb(SQLERRM,1,60);
2402 return(SQLCODE);
2403 end;
2404 /*
2405 ** check in interface table
2406 */
2407 select count(*)
2408 into cnt
2409 from bom_op_routings_interface
2410 where routing_sequence_id = rtg_seq_id
2411 and process_flag = 4;
2412
2413 if (cnt = 0) then
2414 if (mode_type = 1) then
2415 return(0);
2416 else
2417 raise NO_DATA_FOUND;
2418 end if;
2419 end if;
2420
2421 if (cnt > 0) then
2422 if (mode_type = 1) then
2423 raise NOT_UNIQUE;
2424 else
2425 return(0);
2426 end if;
2427 end if;
2428
2429 EXCEPTION
2430 when NO_DATA_FOUND then
2431 err_text := substrb('BOMPVALR(bmvurtg): Routing does not exist ' || SQLERRM,1,70);
2432 return(9999);
2433 when NOT_UNIQUE then
2434 err_text := 'BOMPVALR(bmvurtg) ' || 'Duplicate routing sequence id';
2435 return(9999);
2436 when others then
2437 err_text := 'BOMPVALR(bmvurtg) ' || substrb(SQLERRM,1,60);
2438 return(SQLCODE);
2439 END bmvurtg_verify_routing;
2440
2441 /*--------------------- bmvduprt_verify_duplicate_rtg ----------------------*/
2442 /* NAME
2443 bmvduprt_verify_duplicate_rtg - verify if there is another routing
2444 with same alt.
2445 DESCRIPTION
2446 Verifies in the production and interface tables if routing with
2447 same alt exists. Also verifies for an alternate routing, if the
2448 primary already exists.
2449
2450 REQUIRES
2451 org_id organization_id
2452 assy_id assembly_item_id
2453 alt_Desg alternate routing designator
2454 rtg_type routing type
2455 err_text out buffer to return error message
2456 MODIFIES
2457 RETURNS
2458 0 if successful
2459 cnt if routing already exists
2460 9999 if primary does not exist
2461 SQLCODE if error
2462 NOTES
2463 -----------------------------------------------------------------------------*/
2464 FUNCTION bmvduprt_verify_duplicate_rtg(
2465 org_id NUMBER,
2466 assy_id NUMBER,
2467 alt_desg VARCHAR2,
2468 rtg_type NUMBER,
2469 err_text IN OUT NOCOPY VARCHAR2
2470 )
2471 return INTEGER
2472 IS
2473 cnt NUMBER := 0;
2474 ALREADY_EXISTS EXCEPTION;
2475 BEGIN
2476 begin
2477 select routing_sequence_id
2478 into cnt
2479 from bom_operational_routings
2480 where organization_id = org_id
2481 and assembly_item_id = assy_id
2482 and nvl(alternate_routing_designator, 'NONE') =
2483 nvl(alt_desg, 'NONE');
2484 raise ALREADY_EXISTS;
2485 exception
2486 when ALREADY_EXISTS then
2487 err_text := 'BOMPVALR(bmvduprt): Rtg already exists in production';
2488 return(cnt);
2489 when NO_DATA_FOUND then
2490 NULL;
2491 when others then
2492 err_text := 'BOMPVALR(bmvduprt) ' || substrb(SQLERRM,1,60);
2493 return(SQLCODE);
2494 end;
2495
2496 begin
2497 select routing_sequence_id
2498 into cnt
2499 from bom_op_routings_interface
2500 where organization_id = org_id
2501 and assembly_item_id = assy_id
2502 and nvl(alternate_routing_designator, 'NONE') =
2503 nvl(alt_desg, 'NONE')
2504 and rownum = 1
2505 and process_flag = 4;
2506
2507 raise ALREADY_EXISTS;
2508 exception
2509 when ALREADY_EXISTS then
2510 err_text := 'BOMPVALR(bmvduprt): Rtg already exists in interface';
2511 return(cnt);
2512 when NO_DATA_FOUND then
2513 NULL;
2514 when others then
2515 err_text := 'BOMPVALR(bmvduprt) ' || substrb(SQLERRM,1,60);
2516 return(SQLCODE);
2517 end;
2518 /*
2519 ** for alternate routings, verify if primary exists (or will exist)
2520 ** Alternate mfg routings cannot have primary eng routings
2521 */
2522 if (alt_desg is not null) then
2523 begin
2524 select routing_sequence_id
2525 into cnt
2526 from bom_operational_routings
2527 where organization_id = org_id
2528 and assembly_item_id = assy_id
2529 and alternate_routing_designator is null
2530 and ((rtg_type = 2)
2531 or
2532 (rtg_type =1 and routing_type = 1)
2533 );
2534 return(0);
2535 exception
2536 when NO_DATA_FOUND then
2537 NULL;
2538 when others then
2539 err_text := 'BOMPVALR(bmvduprt) ' || substrb(SQLERRM,1,60);
2540 return(SQLCODE);
2541 end;
2542
2543 begin
2544 select routing_sequence_id
2545 into cnt
2546 from bom_op_routings_interface
2547 where organization_id = org_id
2548 and assembly_item_id = assy_id
2549 and alternate_routing_designator is null
2550 and ((rtg_type = 2)
2551 or
2552 (rtg_type =1 and routing_type = 1)
2553 )
2554 and process_flag = 4
2555 and rownum = 1;
2556 exception
2557 when NO_DATA_FOUND then
2558 err_text := 'BOMPVALR(bmvduprt): Valid primary does not exist';
2559 return(9999);
2560 when others then
2561 err_text := 'BOMPVALR(bmvduprt) ' || substrb(SQLERRM,1,60);
2562 return(SQLCODE);
2563 end;
2564 end if;
2565
2566 return(0);
2567
2568 EXCEPTION
2569 when others then
2570 err_text := 'BOMPVALR(bmvduprt) ' || substrb(SQLERRM,1,60);
2571 return(SQLCODE);
2572 END bmvduprt_verify_duplicate_rtg;
2573
2574 /*------------------------ bmvunop_verify_unique_op -------------------------*/
2575 /* NAME
2576 bmvunop_verify_unique_op - verify if operation seq id is unique or exists
2577 DESCRIPTION
2578 verify the uniqueness or existence of the operation_seq_id in prod and
2579 interface tables
2580
2581 REQUIRES
2582 op_seq_id operation_sequence_id
2583 exist_flag 1 - check for existence
2584 2 - check for uniqueness
2585 err_text out buffer to return error message
2586 MODIFIES
2587 RETURNS
2588 0 if successful
2589 cnt if opeation already exists
2590 SQLCODE if error
2591 NOTES
2592 -----------------------------------------------------------------------------*/
2593 FUNCTION bmvunop_verify_unique_op (
2594 op_seq_id NUMBER,
2595 exist_flag NUMBER,
2596 err_text IN OUT NOCOPY VARCHAR2
2597 )
2598 return INTEGER
2599 IS
2600 cnt NUMBER;
2601 NOT_UNIQUE EXCEPTION;
2602 BEGIN
2603 /*
2604 ** first check in prod tables
2605 */
2606 begin
2607 select 1
2608 into cnt
2609 from bom_operation_sequences
2610 where operation_sequence_id = op_seq_id;
2611 if (exist_flag = 1) then
2612 return(0);
2613 else
2614 raise NOT_UNIQUE;
2615 end if;
2616 exception
2617 when NO_DATA_FOUND then
2618 NULL;
2619 when NOT_UNIQUE then
2620 raise NOT_UNIQUE;
2621 when others then
2622 err_text := 'BOMPVALR(bmvunop) ' || substrb(SQLERRM,1,60);
2623 return(SQLCODE);
2624 end;
2625 /*
2626 ** check in interface table
2627 */
2628 select count(*)
2629 into cnt
2630 from bom_op_sequences_interface
2631 where operation_sequence_id = op_seq_id
2632 and process_flag = 4;
2633
2634 if (cnt = 0) then
2635 if (exist_flag = 2) then
2636 return(0);
2637 else
2638 raise NO_DATA_FOUND;
2639 end if;
2640 end if;
2641
2642 if (cnt > 0) then
2643 if (exist_flag = 2) then
2644 raise NOT_UNIQUE;
2645 else
2646 return(0);
2647 end if;
2648 end if;
2649
2650 EXCEPTION
2651 when NO_DATA_FOUND then
2652 err_text := substrb('BOMPVALR(bmvunop): Operation does not exist '|| SQLERRM,1,70);
2653 return(9999);
2654 when NOT_UNIQUE then
2655 err_text := 'BOMPVALR(bmvunop) ' ||'Duplicate op sequence ids';
2656 return(9999);
2657 when others then
2658 err_text := 'BOMPVALR(bmvunop) ' || substrb(SQLERRM,1,60);
2659 return (SQLCODE);
2660 END bmvunop_verify_unique_op;
2661
2662 /*--------------------- bmvdupop_verify_duplicate_op ------------------------*/
2663 /* NAME
2664 bmvdupop_verify_duplicate_op - verify if there is another operation
2665 with the same routing, effective date, and operation seq num.
2666 DESCRIPTION
2667 Verifies in the production and interface tables if operation with
2668 the same routing, effective date, and operation seq num exists.
2669
2670 REQUIRES
2671 rtg_seq_id rtg sequence id
2672 eff_date effectivity date
2673 op_seq operation seq num
2674 err_text IN OUT NOCOPY buffer to return error message
2675 MODIFIES
2676 RETURNS
2677 0 if successful
2678 cnt if component already exists
2679 SQLCODE if error
2680 NOTES
2681 -----------------------------------------------------------------------------*/
2682 FUNCTION bmvdupop_verify_duplicate_op(
2683 rtg_seq_id NUMBER,
2684 eff_date VARCHAR2,
2685 op_seq NUMBER,
2686 err_text IN OUT NOCOPY VARCHAR2
2687 )
2688 return INTEGER
2689 IS
2690 cnt NUMBER := 0;
2691 ALREADY_EXISTS EXCEPTION;
2692 BEGIN
2693 begin
2694 select operation_sequence_id
2695 into cnt
2696 from bom_operation_sequences
2697 where routing_sequence_id = rtg_seq_id
2698 and to_char(effectivity_date,'YYYY/MM/DD HH24:MI:SS') = eff_date -- Changed for bug 2647027
2699 -- and to_char(effectivity_date,'YYYY/MM/DD HH24:MI') = eff_date
2700 and operation_seq_num = op_seq;
2701 raise ALREADY_EXISTS;
2702 exception
2703 when ALREADY_EXISTS then
2704 err_text := 'BOMPVALR(bmvdupop): Operation already exists in production';
2705 return(cnt);
2706 when NO_DATA_FOUND then
2707 NULL;
2708 when others then
2709 err_text := 'BOMPVALR(bmvdupop) ' || substrb(SQLERRM,1,60);
2710 return(SQLCODE);
2711 end;
2712
2713 begin
2714 select operation_sequence_id
2715 into cnt
2716 from bom_op_sequences_interface
2717 where routing_sequence_id = rtg_seq_id
2718 and to_char(effectivity_date,'YYYY/MM/DD HH24:MI:SS') = eff_date -- Changed for bug 2647027
2719 -- and to_char(effectivity_date,'YYYY/MM/DD HH24:MI') = eff_date
2720 and operation_seq_num = op_seq
2721 and rownum = 1
2722 and process_flag = 4;
2723
2724 raise ALREADY_EXISTS;
2725 exception
2726 when ALREADY_EXISTS then
2727 err_text := 'BOMPVALR(bmvdupop): Operation already exists in interface';
2728 return(cnt);
2729 when NO_DATA_FOUND then
2730 NULL;
2731 when others then
2732 err_text := 'BOMPVALR(bmvdupop) ' || substrb(SQLERRM,1,60);
2733 return(SQLCODE);
2734 end;
2735 return(0);
2736
2737 EXCEPTION
2738 when others then
2739 err_text := 'BOMPVALR(bmvdupop) ' || substrb(SQLERRM,1,60);
2740 return(SQLCODE);
2741 END bmvdupop_verify_duplicate_op;
2742
2743 /*------------------------ bmvovlap_verify_overlaps -------------------------*/
2744 /* NAME
2745 bmvovlap_verify_overlaps - verify operation overlaps
2746 DESCRIPTION
2747 verifies if operation would cause overalpping effectivity
2748
2749 REQUIRES
2750 rtg_id routing_sequence_id
2751 op_num operation_seq_num
2752 eff_date effectivity_date
2753 dis_date disable date
2754 err_text out buffer to return error message
2755 MODIFIES
2756 RETURNS
2757 0 if successful
2758 cnt if opeation already exists
2759 SQLCODE if error
2760 NOTES
2761 -----------------------------------------------------------------------------*/
2762 FUNCTION bmvovlap_verify_overlaps (
2763 rtg_id NUMBER,
2764 op_num NUMBER,
2765 eff_date VARCHAR2,
2766 dis_date VARCHAR2,
2767 err_text IN OUT NOCOPY VARCHAR2
2768 )
2769 return INTEGER
2770 IS
2771 cnt NUMBER := 0;
2772 OVERLAP EXCEPTION;
2773 BEGIN
2774 /*
2775 ** first check in production tables
2776 */
2777 select count(*)
2778 into cnt
2779 from bom_operation_sequences
2780 where routing_sequence_id = rtg_id
2781 and operation_seq_num = op_num
2782 and ((dis_date is null
2783 and to_date(eff_date,'YYYY/MM/DD HH24:MI') <
2784 nvl(disable_date, to_date(eff_date,'YYYY/MM/DD HH24:MI') +1))
2785 or
2786 (dis_date is not null
2787 and to_date(dis_date,'YYYY/MM/DD HH24:MI') > effectivity_date
2788 and to_date(eff_date,'YYYY/MM/DD HH24:MI') <
2789 nvl(disable_date,to_date(eff_date,'YYYY/MM/DD HH24:MI') +1))
2790 );
2791 if (cnt <> 0) then
2792 raise OVERLAP;
2793 end if;
2794 /*
2795 ** search in interface tables
2796 */
2797 select count(*)
2798 into cnt
2799 from bom_op_sequences_interface
2800 where routing_sequence_id = rtg_id
2801 and operation_seq_num = op_num
2802 and process_flag = 4
2803 and ((dis_date is null
2804 and to_date(eff_date,'YYYY/MM/DD HH24:MI') <
2805 nvl(disable_date, to_date(eff_date,'YYYY/MM/DD HH24:MI') +1))
2806 or
2807 (dis_date is not null
2808 and to_date(dis_date,'YYYY/MM/DD HH24:MI') > effectivity_date
2809 and to_date(eff_date,'YYYY/MM/DD HH24:MI') <
2810 nvl(disable_date,to_date(eff_date,'YYYY/MM/DD HH24:MI') +1))
2811 );
2812 if (cnt <> 0) then
2813 raise OVERLAP;
2814 end if;
2815
2816 return(0);
2817
2818 EXCEPTION
2819 when OVERLAP then
2820 err_text := 'Operation causes overlapping effectivity';
2824 return(SQLCODE);
2821 return(9999);
2822 when others then
2823 err_text := 'BOMPVALR(bmvovlap)' || substrb(SQLERRM,1,60);
2825 END bmvovlap_verify_overlaps;
2826
2827 /*---------------------- bmvdept_validate_department -----------------------*/
2828 /* NAME
2829 bmvdept_validate_department - validates department id
2830 DESCRIPTION
2831 verify if department is valid, in the same org and enabled
2832
2833 REQUIRES
2834 org_id organization_id
2835 dept_id return depratmetn id
2836 err_text out buffer to return error message
2837 MODIFIES
2838 RETURNS
2839 0 if successful
2840 SQLCODE if unsuccessful
2841 NOTES
2842 -----------------------------------------------------------------------------*/
2843 FUNCTION bmvdept_validate_department (
2844 org_id NUMBER,
2845 dept_id NUMBER,
2846 eff_date VARCHAR2,
2847 err_text IN OUT NOCOPY VARCHAR2
2848 )
2849 return INTEGER
2850 IS
2851 dummy VARCHAR2(20);
2852 BEGIN
2853 select 'x'
2854 into dummy
2855 from bom_departments
2856 where organization_id = org_id
2857 and department_id = dept_id
2858 and nvl(DISABLE_DATE, to_date(eff_date,'YYYY/MM/DD HH24:MI') +1) >
2859 to_date(eff_date,'YYYY/MM/DD HH24:MI');
2860
2861 return(0);
2862 EXCEPTION
2863 when NO_DATA_FOUND then
2864 err_text := 'BOMPVALR(bmvdept):Invalid department';
2865 return(SQLCODE);
2866 when others then
2867 err_text := 'BOMPVALR(bmvdept)' || substrb(SQLERRM,1,60);
2868 return(SQLCODE);
2869 END bmvdept_validate_department;
2870
2871 /*-------------------- bmvrsch_verify_resource_sched -----------------------*/
2872 /* NAME
2876 REQUIRES
2873 bmvrsch_verify_resource_sched - verify scheduled resources
2874 DESCRIPTION
2875 only one resource can be Next or Prior scheduled per operation
2877 op_seq operation_sequence_id
2878 sched_type 3 - Prior
2879 4 - Next
2880 err_text out buffer to return error message
2881 MODIFIES
2882 RETURNS
2883 0 if successful
2884 SQLCODE if unsuccessful
2885 NOTES
2886 -----------------------------------------------------------------------------*/
2887 FUNCTION bmvrsch_verify_resource_sched (
2888 op_seq NUMBER,
2889 sched_type NUMBER,
2890 err_text IN OUT NOCOPY VARCHAR2
2891 )
2892 return INTEGER
2893 IS
2894 res_cnt NUMBER;
2895
2896 BEGIN
2897 select count(*)
2898 into res_cnt
2899 from bom_op_resources_interface
2900 where operation_sequence_id = op_seq
2901 and schedule_flag = sched_type
2902 and process_flag <> 3 and process_flag <> 7;
2903
2904 if (res_cnt > 1) then
2905 err_text := 'BOMPVALR(bmvrsch):More than one Next or Prior scheduled resource';
2906 return (9999);
2907 end if;
2908 /*
2909 ** if only one resource was found then make sure none exist in the
2910 ** prod tables.
2911 */
2912 if (res_cnt = 1) then
2913 select count(*)
2914 into res_cnt
2918
2915 from bom_operation_resources bor
2916 where operation_sequence_id = op_seq
2917 and schedule_flag = sched_type;
2919 if (res_cnt <> 0) then
2920 err_text := 'BOMPVALR(bmvrsch):More than one Next or Prior scheduled resource';
2921 return (9999);
2922 end if;
2923 end if;
2924
2925 return(0);
2926 EXCEPTION
2927 when NO_DATA_FOUND then
2928 return(0);
2929 when others then
2930 err_text := 'BOMPVALR(bmvrsch)' || substrb(SQLERRM,1,60);
2931 return(SQLCODE);
2932 END bmvrsch_verify_resource_sched;
2933
2934 /*---------------------- bmvauto_verify_autocharge --------------------------*/
2935 /* NAME
2936 bmvauto_verify_autocharge - verify autocharge for resources
2937 DESCRIPTION
2938 -If department has no location, autocharge cannot be PO Move or PO Receipt
2939 -Only one PO move per operation
2940
2941 REQUIRES
2942 op_seq operation sequence id
2943 dept_id department id
2944 err_text out buffer to return error message
2945 MODIFIES
2946 RETURNS
2947 0 if successful
2948 9999 if failed
2949 SQLCODE if unsuccessful
2950 NOTES
2951 -----------------------------------------------------------------------------*/
2955 err_text IN OUT NOCOPY VARCHAR2
2952 FUNCTION bmvauto_verify_autocharge (
2953 op_seq NUMBER,
2954 dept_id NUMBER,
2956 )
2957 return INTEGER
2958 IS
2959 cnt NUMBER;
2960 BEGIN
2961 select count(*)
2962 into cnt
2963 from bom_op_resources_interface ori
2964 where operation_sequence_id = op_seq
2965 and autocharge_type in (3,4)
2966 and not exists (select 'no dept loc or res pur item'
2967 from bom_departments bd
2968 where bd.department_id = dept_id
2969 and bd.location_id is not null);
2970
2971 if (cnt <> 0) then
2972 err_text := 'BOMPVALR(bmvauto):Invalid autocharge type, no loc';
2973 return(9999);
2974 end if;
2975
2976 select count(*)
2977 into cnt
2978 from bom_op_resources_interface ori
2979 where operation_sequence_id = op_seq
2980 and autocharge_type = 4
2981 and process_flag <> 3 and process_flag <> 7;
2982 if (cnt > 1) then
2983 err_text := 'BOMPVALR(bmvauto):Invalid autocharge type, too many';
2984 return(9999);
2985 end if;
2986
2987 if (cnt = 1) then
2988 select count(*)
2989 into cnt
2990 from bom_operation_resources
2991 where operation_sequence_id = op_seq
2992 and autocharge_type = 4;
2993 if (cnt > 0) then
2994 err_text := 'BOMPVALR(bmvauto):Invalid autocharge type, too many';
2995 return(9999);
2996 end if;
2997 end if;
2998
2999 return (0);
3000
3001 EXCEPTION
3002 when others then
3003 err_text := 'BOMPVALR(bmvauto)' || substrb(SQLERRM,1,60);
3004 return (SQLCODE);
3005 END bmvauto_verify_autocharge;
3006
3007 END BOMPVALR;