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