1 Package Body Wip_Flow_Validation as
2 /* $Header: wipwovlb.pls 120.3.12020000.2 2012/10/22 22:45:57 pding ship $ */
3
4 /* *********************************************************************
5 Public Procedures
6 ***********************************************************************/
7
8 /* Is it a valid Buildable Item */
9 function primary_item_id(p_rowid in rowid) return number is
10 x_success number := 0;
11 x_see_eng_item number := 2; --Default to not an engineering item
12 BEGIN
13
14
15 begin
16 x_see_eng_item := to_number(fnd_profile.value('WIP_SEE_ENG_ITEMS'));
17
18 exception
19 when others then
20 x_see_eng_item := 2; --Default to not an engineering item
21 end ;
22
23
24 select 1 into x_success
25 from mtl_transactions_interface mti
26 where rowid = p_rowid
27 and exists (
28 select 1
29 from mtl_system_items msi
30 where msi.inventory_item_id = mti.inventory_item_id
31 and msi.organization_id = mti.organization_id
32 and msi.build_in_wip_flag = 'Y'
33 and msi.pick_components_flag = 'N'
34 and eng_item_flag = decode( x_see_eng_item,
35 1, eng_item_flag,
36 'N') );
37
38 return x_success ;
39
40 exception
41 when others then
42 return 0;
43
44 end primary_item_id ;
45
46
47 function class_code( p_rowid in rowid) return number is
48 x_success number := 0;
49 begin
50
51
52 select 1 into x_success
53 from mtl_transactions_interface mti
54 where mti.rowid = p_rowid
55 and mti.accounting_class is not null
56 and ( ( mti.source_project_id is null
57 and exists (
58 select 'class is valid'
59 from cst_cg_wip_acct_classes_v cwac
60 where cwac.class_code = mti.accounting_class
61 and cwac.organization_id = mti.organization_id
62 and cwac.class_type = 1
63 )
64 )
65 or( mti.source_project_id is not null
66 and exists (
67 select 'class is valid'
68 from cst_cg_wip_acct_classes_v cwac,
69 mrp_project_parameters mpp, mtl_parameters mp
70 where cwac.class_code = mti.accounting_class
71 and cwac.class_type = 1
72 and mpp.project_id = mti.source_project_id
73 and mpp.organization_id = mti.organization_id
74 and mpp.organization_id = mp.organization_id
75 and cwac.cost_group_id = decode(mp.primary_cost_method,1,-1,mpp.costing_group_id)
76 and cwac.organization_id = mti.organization_id
77 )
78 )
79 );
80
81 return x_success ;
82
83 exception
84 when others then
85 return 0;
86
87 end class_code ;
88
89
90
91 function bom_revision(p_rowid in rowid) return number is
92 x_success number := 0;
93 x_bom_rev varchar2(3);
94 x_rev varchar2(3);
95 x_rev_exists number ;
96 x_bom_rev_exists number;
97 x_item_id number ;
98 x_org_id number;
99 begin
100
101
102 begin
103
104 select inventory_item_id,
105 organization_id,
106 revision,
107 bom_revision
108 into x_item_id,
109 x_org_id,
110 x_rev,
111 x_bom_rev
112 from mtl_transactions_interface
113 where rowid = p_rowid ;
114
115 exception
116 when no_data_found then
117 null ;
118 end ;
119
120
121
122 x_bom_rev_exists := wip_common.bill_exists(
123 p_item_id => x_item_id,
124 p_org_id => x_org_id );
125
126 if (x_bom_rev_exists = -1) then
127 /* SQLERROR occured in bill_exists routine */
128 raise PROGRAM_ERROR ;
129 end if ;
130
131
132 x_rev_exists := wip_common.revision_exists(
133 p_item_id => x_item_id,
134 p_org_id => x_org_id );
135
136 if(x_rev_exists in (-1, -2)) then
137 /* SQLERROR or Application Error occured in
138 the revision_exists routine */
139 raise PROGRAM_ERROR ;
140 end if ;
141
142 /*Commented out by bug 14702702
143 if (x_rev_exists in (1, 2) ) then*/
144
145 /*********************************************
146 This is done already in inltwv.ppc, should be
147 cleaned up for R12
148 **********************************************/
149
150 /* select 1 into x_success
151 from mtl_transactions_interface mti,
152 mtl_system_items msi
153 where mti.rowid = p_rowid
154 and msi.inventory_item_id = mti.inventory_item_id
155 and msi.organization_id = mti.organization_id
156 and ( (
157 msi.revision_qty_control_code = 2
158 and exists
159 (
160 select 1
161 from mtl_item_revisions mir
162 where mir.organization_id = mti.organization_id
163 and mir.inventory_item_id = mti.inventory_item_id
164 and mir.revision = mti.revision
165 )
166 )
167 or
168 (
169 msi.revision_qty_control_code = 1
170 and mti.revision is null
171 )
172 );
173
174 end if ;*/
175
176 if (x_bom_rev_exists in (1, 0) ) then
177
178 select 1 into x_success
179 from mtl_transactions_interface mti,
180 mtl_system_items msi
181 where mti.rowid = p_rowid
182 and msi.inventory_item_id = mti.inventory_item_id
183 and msi.organization_id = mti.organization_id
184 and ( (
185 x_bom_rev_exists = 1
186 and exists
187 (
188 select 1
189 from mtl_item_revisions mir
190 where mir.organization_id = mti.organization_id
191 and mir.inventory_item_id = mti.inventory_item_id
192 and mir.revision = mti.bom_revision
193 )
194 )
195 or
196 (
197 x_bom_rev_exists = 0
198 and mti.bom_revision is null
199 )
200 );
201
202 end if ;
203
204
205 /*************************************************
206 Make sure that if both the revision as well as
207 the Bom revision are populated, then they should
208 be the same as both of them are based out of the
209 same table MTL_ITEM_REVISIONS.
210 **************************************************/
211
212 if (x_bom_rev is not null) and (x_rev is not null)
213 and (x_success =1 ) then
214
215 /* The revision and the bom revision are not sync, so
216 they will fail */
217 if (x_bom_rev <> x_rev) then
218 x_success := 0 ;
219 end if ;
220
221 end if;
222
223 return x_success;
224
225 exception
226 when others then
227
228 return 0;
229
230
231 end bom_revision ;
232
233
234 function routing_revision(p_rowid in rowid) return number is
235 x_success number := 0;
236 x_rtg_exists number := 0;
237 x_item_id number ;
238 x_org_id number;
239 x_txn_date DATE;
240 begin
241
242 begin
243
244 select inventory_item_id,
245 organization_id,
246 transaction_date
247 into x_item_id,
248 x_org_id,
249 x_txn_date
250 from mtl_transactions_interface
251 where rowid = p_rowid ;
252
253 exception
254 when no_data_found then
255 null ;
256 end ;
257
258
259 x_rtg_exists := wip_common.routing_exists(
260 p_item_id => x_item_id,
261 p_org_id => x_org_id,
262 p_eff_date => x_txn_date );
263
264
265 if ( x_rtg_exists >=0 ) then
266
267 select 1 into x_success
268 from mtl_transactions_interface mti,
269 mtl_system_items msi
270 where mti.rowid = p_rowid
271 and msi.inventory_item_id = mti.inventory_item_id
272 and msi.organization_id = mti.organization_id
273 and ( (x_rtg_exists >= 1
274 and exists (
275 select 1
276 from mtl_rtg_item_revisions mrir
277 where mrir.organization_id = mti.organization_id
278 and mrir.inventory_item_id = mti.inventory_item_id
279 and mrir.process_revision = mti.routing_revision
280 and mrir.implementation_date is not null
281 )
282 )
283 or
284 (
285 x_rtg_exists = 0
286 and mti.routing_revision is null
287 )
288 );
289
290 elsif ( x_rtg_exists = -1 ) then
291 /* SQLERROR in routing_exists routine */
292 raise PROGRAM_ERROR ;
293
294 end if ;
295
296 return x_success;
297
298 exception
299 when others then
300 return 0;
301
302 end routing_revision;
303
304
305
306 function bom_rev_date(p_rowid in rowid) return number is
307 x_success number := 0;
308 x_bom_rev varchar2(3);
309 x_bom_rev_date date ;
310 x_org_id number;
311 x_item_id number;
312 x_check_rev varchar2(3);
313 x_bom_exists NUMBER;
314 x_released_revs_type NUMBER;
315 x_released_revs_meaning Varchar2(30);
316
317 begin
318
319
320 begin
321
322 select bom_revision,
323 bom_revision_date,
324 organization_id,
325 inventory_item_id
326 into x_bom_rev,
327 x_bom_rev_date,
328 x_org_id,
329 x_item_id
330 from mtl_transactions_interface
331 where rowid = p_rowid ;
332
333
334 exception
335 when no_data_found then
336 null ;
337
338 end ;
339
340
341 x_bom_exists := wip_common.bill_exists(
342 p_item_id => x_item_id,
343 p_org_id => x_org_id );
344
345 IF (x_bom_exists = -1) then
346 /* SQLERROR occured in bill_exists routine */
347 raise PROGRAM_ERROR ;
348
349 ELSIF (x_bom_exists = 0) THEN
350 x_success := 1;
351
352 ELSE
353 /* 3033785 */
354 wip_common.Get_Released_Revs_Type_Meaning (x_released_revs_type,
355 x_released_revs_meaning
356 );
357 BOM_REVISIONS.Get_Revision
358 (type => 'PART',
359 eco_status => x_released_revs_meaning,
360 examine_type => 'ALL',
361 org_id => x_Org_Id,
362 item_id => x_item_id,
363 rev_date => x_bom_rev_date,
364 itm_rev =>x_check_rev);
365
366 if((x_bom_rev is null) or (x_check_rev = x_bom_rev)) then
367 x_success := 1;
368 else
369 x_success := 0 ;
370 end if ;
371
372 END IF;
373
374 return x_success;
375
376 exception
377 when others then
378 return 0;
379
380 end bom_rev_date ;
381
382
383 function rout_rev_date(p_rowid in rowid) return number is
384 x_success number := 0;
385 x_rtg_rev varchar2(3);
386 x_rtg_rev_date date ;
387 x_org_id number;
388 x_item_id number;
389 x_txn_date DATE;
390 x_check_rev varchar2(3);
391 x_rtg_exists number := 0;
392 x_released_revs_type NUMBER;
393 x_released_revs_meaning Varchar2(30);
394
395 begin
396
397
398
399 begin
400
401 select routing_revision,
402 routing_revision_date,
403 organization_id,
404 inventory_item_id,
405 transaction_date
406 into x_rtg_rev,
407 x_rtg_rev_date,
408 x_org_id,
409 x_item_id,
410 x_txn_date
411 from mtl_transactions_interface
412 where rowid = p_rowid ;
413
414
415 exception
416
417 when others then
418 null ;
419
420 end ;
421
422 x_rtg_exists := wip_common.routing_exists(
423 p_item_id => x_item_id,
424 p_org_id => x_org_id,
425 p_eff_date => x_txn_date );
426
427
428 if (x_rtg_exists = 1) then
429 /* 3033785 */
430 wip_common.Get_Released_Revs_Type_Meaning (x_released_revs_type,
431 x_released_revs_meaning
432 );
433 BOM_REVISIONS.Get_Revision
434 (type => 'PROCESS',
435 eco_status => x_released_revs_meaning,
436 examine_type => 'ALL',
437 org_id => x_Org_Id,
438 item_id => x_item_id,
439 rev_date => x_rtg_rev_date,
440 itm_rev =>x_check_rev);
441
442 if(x_check_rev = x_rtg_rev) then
443 x_success := 1;
444 else
445 x_success := 0 ;
446 end if ;
447
448 elsif (x_rtg_exists = 0 ) then
449
450 if(x_rtg_rev_date is null and x_rtg_rev is null ) then
451 x_success := 1;
452 else
453 x_success := 0;
454 end if ;
455
456 elsif (x_rtg_exists = -1 ) then
457 /* SQLERROR in routing exists routine */
458 raise PROGRAM_ERROR ;
459
460 end if ;
461
462 return x_success;
463
464
465 exception
466 when others then
467 return 0;
468
469 end rout_rev_date;
470
471
472 function alt_bom_desg(p_rowid in rowid) return number is
473 x_success number := 0;
474 begin
475
476 /* we look at only manufacturing bill */
477 select 1 into x_success
478 from mtl_transactions_interface mti
479 where rowid = p_rowid
480 and ( (alternate_bom_designator is null)
481 or (alternate_bom_designator is not null
482 and exists (
483 select 1
484 from bom_bill_alternates_v bba
485 where bba.alternate_bom_designator =
486 mti.alternate_bom_designator
487 and bba.organization_id = mti.organization_id
488 /* and bba.assembly_type = 1 */ /*changed condition to include engineering BOMs also as WOL transactions are currently supported for primary routings and erroring for alt routings due to this validation */
489 and bba.assembly_type in (1,2)
490 and bba.assembly_item_id = mti.inventory_item_id)));
491
492 return x_success ;
493
494 exception
495 when others then
496 return 0;
497
498 end alt_bom_desg ;
499
500
501 function alt_rout_desg(p_rowid in rowid) return number is
502 x_success number := 0;
503 begin
504 /* we look at only manufacturing routing */
505 select 1 into x_success
506 from mtl_transactions_interface mti
507 where rowid = p_rowid
508 and ( (alternate_routing_designator is null)
509 or (alternate_routing_designator is not null
510 and exists (
511 select 1
512 from bom_routing_alternates_v bra
513 where bra.alternate_routing_designator =
514 mti.alternate_routing_designator
515 and bra.organization_id = mti.organization_id
516 /*and bra.routing_type = 1 *//*changed condition to include engineering routings also as WOL transactions are currently supported for primary routings and erroring for alt routings due to this validation*/
517 and bra.routing_type in (1,2)
518 and bra.assembly_item_id = mti.inventory_item_id)));
519
520 return x_success ;
521
522
523 exception
524 when others then
525 return 0;
526
527 end alt_rout_desg ;
528
529
530 function completion_sub(p_rowid in rowid) return number is
531 x_success number := 0;
532 x_subinv_code VARCHAR2(30);
533 x_txn_action NUMBER;
534 begin
535
536 /******************************************************
537 Validation whether the subinventory is under locator
538 control and if so whether it the correct locator is
539 done as a part of inventory validation in inltev.ppc
540 *******************************************************/
541
542 SELECT subinventory_code, transaction_action_id -- CFM Scrap Section
543 INTO x_subinv_code, x_txn_action
544 FROM mtl_transactions_interface mti
545 WHERE ROWID = p_rowid;
546
547 IF x_txn_action = 30 THEN
548 IF x_subinv_code IS NULL THEN
549 RETURN 1;
550 ELSE
551 RETURN 0;
552 END IF;
553 END IF; -- CFM Scrap Section End
554
555
556 select 1 into x_success
557 from mtl_transactions_interface mti
558 where rowid = p_rowid
559 and subinventory_code is not null
560 and exists (
561 (
562 select 1
563 from mtl_system_items msi
564 where mti.inventory_item_id = msi.inventory_item_id
565 and mti.organization_id = msi.organization_id
566 and msi.restrict_subinventories_code = 2
567 )
568 union (
569 select 1
570 from mtl_system_items msi, mtl_item_sub_val_v msvv
571 where mti.inventory_item_id = msi.inventory_item_id
572 and mti.organization_id = msi.organization_id
573 and msi.restrict_subinventories_code = 1
574 and msi.inventory_asset_flag = 'N'
575 and msvv.organization_id = mti.organization_id
576 and msvv.inventory_item_id = mti.inventory_item_id
577 and msvv.secondary_inventory_name =
578 mti.subinventory_code
579 )
580 union (
581 select 1
582 from mtl_system_items msi, mtl_item_sub_ast_trk_val_v msvv
583 where mti.inventory_item_id = msi.inventory_item_id
584 and mti.organization_id = msi.organization_id
585 and msi.restrict_subinventories_code = 1
586 and msi.inventory_asset_flag = 'Y'
587 and msvv.organization_id = mti.organization_id
588 and msvv.inventory_item_id = mti.inventory_item_id
589 and msvv.secondary_inventory_name =
590 mti.subinventory_code
591 )
592 );
593
594 return x_success ;
595
596 exception
597 when others then
598 return 0;
599
600 end completion_sub ;
601
602
603 function completion_locator_id(p_rowid in rowid) return number is
604 x_success number := 0;
605 x_proj_ref_exists number := 0 ;
606 x_loc_id NUMBER;
607 x_txn_action NUMBER;
608 begin
609
610
611 /************************************************
612 Simple validation for the Sub Inventory
613 and the Completion Locator. For Project related
614 Locator validation, we do the validation only if
615 the locator id is specified. However if the locator
616 segments are specified, we leave it up to the
617 inventory validation to handle it (inltev.ppc).
618 ************************************************/
619
620
621 SELECT locator_id, transaction_action_id -- CFM Scrap Section
622 INTO x_loc_id, x_txn_action
623 FROM mtl_transactions_interface mti
624 WHERE ROWID = p_rowid;
625
626 IF x_txn_action = 30 THEN
627 IF x_loc_id IS NULL THEN
628 RETURN 1;
629 ELSE
630 RETURN 0;
631 END IF;
632 END IF; -- CFM Scrap Section End
633
634 select 1 into x_success
635 from mtl_transactions_interface mti
636 where mti.rowid = p_rowid
637 and mti.subinventory_code is not null
638 and ( ( mti.locator_id is not null
639 and exists
640 (
641 select 1
642 from mtl_item_locations mil
643 where mil.inventory_location_id =
644 mti.locator_id
645 and mil.subinventory_code =
646 mti.subinventory_code
647 and mil.organization_id =
648 mti.organization_id
649 )
650 )
651 or
652 (
653 locator_id is null
654 )
655 );
656
657
658 select count(*) into x_proj_ref_exists
659 from mtl_transactions_interface mti
660 where mti.rowid = p_rowid
661 and source_project_id is not null ;
662
663 if (x_proj_ref_exists = 1 ) then
664
665 select 1 into x_success
666 from mtl_transactions_interface mti,
667 mtl_item_locations mil
668 where mti.rowid = p_rowid
669 and ( ( mti.locator_id is not null
670 and mil.inventory_location_id = mti.locator_id
671 and mil.organization_id = mti.organization_id
672 and mil.segment19 = mti.source_project_id
673 AND nvl(mil.segment20, -1)
674 = nvl(mti.source_task_id , -1)
675 )
676 or ( mti.locator_id is null
677 and mti.organization_id = mil.organization_id --fix for 4896646
678 and ( mil.segment1 is not null
679 or mil.segment2 is not null
680 or mil.segment3 is not null
681 or mil.segment4 is not null
682 or mil.segment5 is not null
683 or mil.segment6 is not null
684 or mil.segment7 is not null
685 or mil.segment8 is not null
686 or mil.segment9 is not null
687 or mil.segment10 is not null
688 or mil.segment11 is not null
689 or mil.segment12 is not null
690 or mil.segment13 is not null
691 or mil.segment14 is not null
692 or mil.segment15 is not null
693 or mil.segment16 is not null
694 or mil.segment17 is not null
695 or mil.segment18 is not null
696 or mil.segment19 is not null
697 or mil.segment20 is not null
698 )
699 )
700 ) ;
701
702
703 end if ;
704
705 return x_success ;
706
707 exception
708 when others then
709 return 0;
710
711 end completion_locator_id ;
712
713
714
715 function demand_class(p_rowid in rowid) return number is
716 x_success number := 0;
717 begin
718
719
720 select 1 into x_success
721 from mtl_transactions_interface mti
722 where rowid = p_rowid
723 and ( ( demand_class is not null
724 and exists
725 (
726 select 1
727 from so_demand_classes_active_v sdca
728 where sdca.demand_class_code = mti.demand_class
729 )
730 )
731 or
732 (
733 demand_class is null
734 )
735 );
736
737 return x_success ;
738
739 exception
740 when others then
741 return 0;
742
743 end demand_class ;
744
745
746 function schedule_group_id(p_rowid in rowid) return number is
747 x_success number := 0;
748 begin
749
750
751 /***********************************************
752 We don't have the creation of schedule group
753 for Automotive right now. But it may be added
754 in R11 +, discussed it with mmodi and jgu
755 and decided that it is not a business
756 requirement
757 ************************************************/
758
759 select 1 into x_success
760 from mtl_transactions_interface mti
761 where rowid = p_rowid
762 and ( ( schedule_group is not null
763 and exists
764 ( select 1
765 from wip_schedule_groups_val_v wsg
766 where wsg.schedule_group_id = mti.schedule_group
767 and wsg.organization_id = mti.organization_id
768 )
769 )
770 or (
771 schedule_group is null
772 )
773 ) ;
774
775 return x_success ;
776
777 exception
778 when others then
779 return 0;
780
781 end schedule_group_id ;
782
783
784
785 function build_sequence(p_rowid in rowid) return number is
786 x_success number := 0;
787 x_build_sequence NUMBER;
788 x_wip_entity_id NUMBER;
789 x_organization_id NUMBER;
790 x_line_id NUMBER;
791 x_schedule_group_id NUMBER;
792 begin
793
794
795 SELECT
796 mti.build_sequence,
797 mti.transaction_source_id,
798 mti.organization_id,
799 mti.repetitive_line_id,
800 mti.schedule_group
801 INTO
802 x_build_sequence,
803 x_wip_entity_id,
804 x_organization_id,
805 x_line_id,
806 x_schedule_group_id
807 FROM mtl_transactions_interface mti
808 WHERE rowid = p_rowid;
809
810 IF WIP_Validate.build_sequence(p_build_sequence => x_build_sequence,
811 p_wip_entity_id => x_wip_entity_id,
812 p_organization_id => x_organization_id,
813 p_line_id => x_line_id,
814 p_schedule_group_id => x_schedule_group_id)
815 THEN
816 x_success := 1;
817 ELSE
818 x_success := 0;
819 END IF;
820
821
822 return x_success ;
823
824 exception
825 when others then
826 return 0;
827
828 end build_sequence ;
829
830
831 function line_id(p_rowid in rowid) return number is
832 x_success number := 0;
833 begin
834
835 select 1 into x_success
836 from mtl_transactions_interface mti
837 where rowid = p_rowid
838 and ( ( repetitive_line_id is not null
839 and exists
840 (
841 select 1
842 from wip_lines_val_v wl
843 where wl.line_id = mti.repetitive_line_id
844 and wl.organization_id = mti.organization_id
845 )
846 )
847 or(
848 repetitive_line_id is null
849 )
850 );
851
852 return x_success ;
853
854
855 exception
856 when others then
857 return 0;
858
859 end line_id ;
860
861
862 function project_id(p_rowid in rowid) return number is
863 x_success number := 0;
864 l_org_id number;
865 begin
866 -- fix MOAC, set id so project view works
867 select organization_id into l_org_id
868 from mtl_transactions_interface
869 where rowid = p_rowid;
870 fnd_profile.put('MFG_ORGANIZATION_ID', l_org_id);
871
872 /*******************************************************
873 We can have non project related flow schedules in a project
874 enabled organization.
875 For Project Related Flow Schedules the Locator Validation
876 is done in inltev.ppc.
877 *********************************************************/
878
879 select 1 into x_success
880 from mtl_transactions_interface mti
881 where rowid = p_rowid
882 and ( ( source_project_id is not null
883 and exists (
884 select 1
885 from mtl_parameters mps
886 where nvl(mps.project_reference_enabled,2) = 1
887 and mps.organization_id = mti.organization_id
888 )
889 and exists (
890 select 1
891 from mtl_project_v mp
892 where mp.project_id = mti.project_id
893 )
894 )
895 or( source_project_id is null )
896 ) ;
897
898 return x_success ;
899
900 exception
901 when others then
902 return 0;
903
904 end project_id ;
905
906
907 function task_id(p_rowid in rowid) return number is
908 x_success number := 0;
909 begin
910
911
912
913 /* ****************************************************
914 The logic for task validation in a simple condition
915 statement :
916
917 If Org is task enabled
918 If Project not null
919 if task is null
920 Error ;
921 else
922 Check it in pa_tasks_expend_v;
923 end if;
924 else
925 if task is null ;
926 success ;
927 else
928 Error ;
929 end if;
930
931 end if;
932
933 else
934
935 Success ;
936 end if;
937 ***************************************************/
938
939 select 1 into x_success
940 from mtl_transactions_interface mti,
941 mtl_parameters mps
942 where mti.rowid = p_rowid
943 and mps.organization_id = mti.organization_id
944 and ( ( nvl(mps.project_control_level,1) = 2
945 and ( ( mti.source_project_id is not null
946 and mti.source_task_id is not null
947 and exists (
948 select 1
949 from pa_tasks_expend_v pt
950 where pt.project_id = mti.source_project_id
951 and pt.task_id = mti.source_task_id
952 )
953 )
954 or ( mti.source_project_id is null
955 and mti.source_task_id is null
956 )
957 )
958 )
959 or( nvl(mps.project_control_level,1) = 1 )
960 );
961
962 return x_success ;
963
964 exception
965 when others then
966 return 0;
967
968 end task_id ;
969
970
971
972 /* This is not mti column but a wfs column */
973 function status(p_status in number) return number is
974 x_success number := 0;
975 begin
976
977 select 1 into x_success
978 from dual
979 where p_status in (
980 select lookup_code
981 from mfg_lookups
982 where lookup_type = 'WIP_FLOW_SCHEDULE_STATUS');
983
984 return x_success ;
985
986 exception
987 when others then
988 return 0;
989
990 end status ;
991
992
993 function schedule_number(p_rowid in rowid) return number is
994 x_success number := 0;
995 begin
996
997 /**************************************************
998 As we are inserting the flow schedules one after
999 the other into wip_flow_schedules, we don't have
1000 to check for duplicate schedule_number in MTI
1001 itself as this will error out by itself
1002 ***************************************************/
1003
1004
1005 select 1 into x_success
1006 from mtl_transactions_interface mti
1007 where mti.rowid = p_rowid
1008 and((mti.scheduled_flag <> 1
1009 and not exists(
1010 select 'exists'
1011 from wip_entities
1012 where wip_entity_name = mti.schedule_number))
1013 or(mti.scheduled_flag = 1
1014 and exists(
1015 select 'exists'
1016 from wip_entities
1017 where wip_entity_id = mti.transaction_source_id)));
1018
1019 return x_success ;
1020
1021
1022 exception
1023 when others then
1024 return 0;
1025
1026 end schedule_number ;
1027
1028 function schedule_number(p_schedule_number in varchar2) return number is
1029 x_success number := 0;
1030 begin
1031
1032 /**************************************************
1033 As we are inserting the flow schedules one after
1034 the other into wip_flow_schedules, we don't have
1035 to check for duplicate schedule_number in MTI
1036 itself as this will error out by itself
1037 ***************************************************/
1038
1039 select 1 into x_success
1040 from sys.dual
1041 where not exists(
1042 select 'exists'
1043 from wip_entities
1044 where wip_entity_name = p_schedule_number );
1045
1046
1047 return x_success ;
1048
1049 exception
1050 when others then
1051 return 0;
1052
1053 end schedule_number ;
1054
1055 function scheduled_flag(p_rowid in rowid) return number is
1056 x_success number := 0;
1057 begin
1058
1059
1060 select 1 into x_success
1061 from mtl_transactions_interface mti
1062 where rowid = p_rowid
1063 and mti.scheduled_flag in (
1064 select lookup_code
1065 from mfg_lookups
1066 where lookup_type = 'SYS_YES_NO');
1067
1068 return x_success ;
1069
1070
1071 exception
1072 when others then
1073 return 0;
1074
1075
1076
1077 end scheduled_flag ;
1078
1079 /*****************************************************************************
1080 * Unit Number Validation:
1081 * There are two checks performed in this function:
1082 * 1. Check to see that if a unit number is not provided then either there is
1083 * no unit number control OR the assembly is not under unit number control.
1084 * 2. Check to see that if a unit number is provided, the organization is
1085 * enabled for model unit effectivity
1086 * AND the assembly is a unit effective item
1087 * AND that the unit number exists in the master org of the current organization
1088 * AND in the case of scheduled flow schedules the unit number provided matches
1089 * the unit number in wip_flow_schedules.
1090 ******************************************************************************/
1091
1092 function unit_number(p_rowid in rowid) return number is
1093 x_success number := 0;
1094 begin
1095
1096 SELECT 1
1097 INTO x_success
1098 FROM mtl_transactions_interface mti
1099 WHERE rowid = p_rowid
1100 AND ((end_item_unit_number IS NULL
1101 AND (pjm_unit_eff.enabled = 'N'
1102 OR pjm_unit_eff.unit_effective_item(mti.inventory_item_id,mti.organization_id) = 'N'))
1103 OR (end_item_unit_number IS NOT NULL
1104 AND (pjm_unit_eff.enabled = 'Y'
1105 AND pjm_unit_eff.unit_effective_item(mti.inventory_item_id,mti.organization_id) = 'Y'
1106 AND end_item_unit_number IN (SELECT unit_number
1107 FROM pjm_unit_numbers_lov_v N, mtl_parameters P
1108 WHERE P.organization_id = mti.organization_id and
1109 P.master_organization_id = N.master_organization_id)
1110 AND (mti.scheduled_flag <> 1
1111 OR exists (SELECT 1
1112 FROM wip_flow_schedules wfs
1113 WHERE wfs.wip_entity_id = mti.transaction_source_id
1114 AND wfs.end_item_unit_number = mti.end_item_unit_number
1115 )))));
1116
1117 return x_success ;
1118
1119
1120 exception
1121 when others then
1122 return 0;
1123
1124
1125
1126 end unit_number;
1127
1128
1129 End Wip_Flow_Validation ;
[Home] [Help]