1 Package Body Wip_Flow_Derive as
2 /* $Header: wipwodfb.pls 120.2 2011/02/09 14:49:34 sisankar ship $ */
3
4 /* *********************************************************************
5 Public Procedures
6 ***********************************************************************/
7
8
9 function class_code( p_class_code in out NOCOPY varchar2,
10 p_err_mesg in out NOCOPY varchar2,
11 p_org_id in number,
12 p_item_id in number,
13 p_wip_entity_type in number,
14 p_project_id in number) return number is
15 x_success number := 0;
16 x_err_code1 varchar2(30);
17 x_token1 varchar2(10);
18 x_err_code2 varchar2(30);
19 x_token2 varchar2(10);
20 begin
21
22 if (p_class_code is null) then
23 p_class_code := wip_common.default_acc_class(
24 X_ORG_ID => p_org_id,
25 X_ITEM_ID => p_item_id,
26 X_ENTITY_TYPE => p_wip_entity_type,
27 X_PROJECT_ID => p_project_id,
28 X_ERR_MESG_1 => x_err_code1,
29 X_ERR_CLASS_1 => x_token1,
30 X_ERR_MESG_2 => x_err_code2,
31 X_ERR_CLASS_2 => x_token2
32 ) ;
33
34 if(p_class_code is null) then
35
36 fnd_message.set_name('WIP',x_err_code1);
37 fnd_message.set_token('CLASS_CODE',x_token1);
38 p_err_mesg := fnd_message.get;
39 return 0 ;
40 end if;
41
42 end if ;
43
44 return 1;
45
46 exception
47 when others then
48 return 0;
49
50 end class_code ;
51
52
53
54 function bom_revision(
55 p_bom_rev in out NOCOPY varchar2,
56 p_rev in out NOCOPY varchar2,
57 p_bom_rev_date in out NOCOPY date,
58 p_item_id in number,
59 p_start_date in date,
60 p_Org_id in number) return number is
61 x_success number := 0;
62 x_rev_exists number ;
63 x_bom_rev_exists number;
64 x_bom_rev varchar2(3);
65 begin
66
67 /* Try to handle the corner case when the rev and Bom_rev
68 are not in Sync - That is done in the Validation
69 Routine */
70
71 x_bom_rev := p_bom_rev ;
72
73
74 x_bom_rev_exists := wip_common.bill_exists(
75 p_item_id => p_item_id,
76 p_org_id => p_org_id );
77
78 if (x_bom_rev_exists = -1) then
79 /* SQLERROR occured in bill_exists routine */
80 raise PROGRAM_ERROR ;
81 end if ;
82
83 x_rev_exists := wip_common.revision_exists(
84 p_item_id => p_item_id,
85 p_org_id => p_org_id );
86
87 if(x_rev_exists in (-1, -2)) then
88 /* SQLERROR or Application Error occured in
89 the revision_exists routine */
90 raise PROGRAM_ERROR ;
91 end if ;
92
93 /* This basically fetches you the revision for
94 the Item - doesnot worry about whether it is
95 a Bill or the Item */
96
97 wip_revisions.bom_revision(
98 P_Organization_Id => p_org_id,
99 P_Item_Id => p_item_id,
100 P_Revision => x_bom_rev,
101 P_Revision_Date => p_bom_rev_date,
102 P_Start_Date => p_start_date );
103
104 -- If revision exists
105 if (x_rev_exists = 2) and (p_rev is null) then
106 p_rev := x_bom_rev ;
107 end if ;
108
109 -- If the Bill exists
110 if (x_bom_rev_exists = 1) and (p_bom_rev is null) then
111 p_bom_rev := x_bom_rev ;
112 end if ;
113
114 return 1;
115
116 exception
117 when others then
118 return 0;
119
120 end bom_revision ;
121
122
123
124 function routing_revision( p_rout_rev in out NOCOPY varchar2,
125 p_rout_rev_date in out NOCOPY date,
126 p_item_id in number,
127 p_start_date in date,
128 p_Org_id in number) return number is
129 x_success number := 0;
130 x_rtg_exists number := 0;
131 begin
132
133 x_rtg_exists := wip_common.routing_exists(
134 p_item_id => p_item_id,
135 p_org_id => p_org_id );
136
137 if (x_rtg_exists >= 1 ) then
138
139 wip_revisions.Routing_Revision(
140 P_Organization_Id => p_org_id,
141 P_Item_Id => p_item_id,
142 P_Revision => p_rout_rev,
143 P_Revision_Date => p_rout_rev_date,
144 P_Start_Date => p_start_date );
145
146 elsif(x_rtg_exists = -1) then
147 /* SQLERROR from the routing_exists routine */
148 raise PROGRAM_ERROR ;
149 end if ;
150
151 return 1;
152
153 exception
154 when others then
155 return 0;
156
157 end routing_revision ;
158
159
160
161
162 function completion_sub(p_comp_sub in out NOCOPY varchar2,
163 p_item_id in number,
164 p_org_id in number,
165 p_alt_rtg_des in varchar2) return number is
166 x_success number := 0;
167 begin
168
169 if p_comp_sub is null then
170
171 select BOR.COMPLETION_SUBINVENTORY
172 into p_comp_sub
173 from BOM_OPERATIONAL_ROUTINGS BOR
174 where BOR.ORGANIZATION_ID = p_org_id
175 and BOR.ASSEMBLY_ITEM_ID = p_item_id
176 and NVL(BOR.ALTERNATE_ROUTING_DESIGNATOR,'@@@') =
177 NVL(p_alt_rtg_des, '@@@');
178
179 end if;
180
181 return 1;
182
183 exception
184 when no_data_found then
185 return 1; -- This was not defined in the routing
186
187 when others then
188 return 0;
189
190 end completion_sub ;
191
192
193 /* Defaulting Routing Completion Locator Id */
194 function routing_completion_sub_loc(
195 p_rout_comp_sub in out NOCOPY varchar2,
196 p_rout_comp_loc in out NOCOPY number,
197 p_item_id in number,
198 p_org_id in number,
199 p_alt_rtg_des in varchar2) return number is
200 x_success number := 0;
201 begin
202
203
204 if p_rout_comp_sub is null and p_rout_comp_loc is null then
205
206 select BOR.COMPLETION_SUBINVENTORY,
207 BOR.COMPLETION_LOCATOR_ID
208 into p_rout_comp_sub,
209 p_rout_comp_loc
210 from BOM_OPERATIONAL_ROUTINGS BOR
211 where BOR.ORGANIZATION_ID = p_org_id
212 and BOR.ASSEMBLY_ITEM_ID = p_item_id
213 and NVL(BOR.ALTERNATE_ROUTING_DESIGNATOR,'@@@') =
214 NVL(p_alt_rtg_des, '@@@') ;
215
216 end if ;
217
218
219 return 1 ;
220
221 exception
222
223 when no_data_found then
224 return 1 ;
225
226 when others then
227 return 0 ;
228
229 end routing_completion_sub_loc ;
230
231 /* Defaulting completion locator id. In completion_loc, we only default locator id from
232 the routing if p_proj_id is not null. I don't think we need that restriction. Also,
233 p_txn_int_id is unneccessary. INV validation should derive the locator id from the
234 segments provided. We only need to check the existence of locator id */
235 function completion_locator_id(p_comp_loc in out NOCOPY number,
236 p_item_id in number,
237 p_org_id in number,
238 p_alt_rtg_des in varchar2,
239 p_proj_id in number,
240 p_task_id in number,
241 p_comp_sub in varchar2) return number is
242 l_success number := 0;
243 l_comp_sub varchar2(10);
244 l_comp_loc Number;
245 general exception;
246 begin
247
248 -- Modified for Bug 11695990. Initialize for comparison
249 l_comp_sub := p_comp_sub;
250
251 l_success := routing_completion_sub_loc(
252 p_rout_comp_sub => l_comp_sub,
253 p_rout_comp_loc => p_comp_loc,
254 p_item_id => p_item_id,
255 p_org_id => p_org_id,
256 p_alt_rtg_des => p_alt_rtg_des) ;
257
258 if (l_success = 0) then
259 raise general;
260 end if;
261
262 -- Modified for Bug 11695990.
263 -- We shouldn't make it null since it's defaulted from Routing when both subinv and locator is null.
264 /*
265 if (l_comp_sub <> p_comp_sub) then
266 p_comp_loc := null ;
267 end if ;
268 */
269
270 if ((p_proj_id is not null) and
271 (p_comp_sub is not null) and
272 (p_comp_loc is not null)) then
273
274 --The following piece of code that checks for dynamic
275 --locator creation, will be replaced by call to pacifia's routine.
276
277 if (pjm_project_locator.check_itemlocatorcontrol(
278 p_organization_id => p_org_id,
279 p_sub => p_comp_sub,
280 p_loc => p_comp_loc,
281 p_item_id => p_item_id,
282 p_hardpeg_only => 2 ) = TRUE ) then
283
284 select count(*) into l_success
285 from mtl_item_locations
286 where inventory_location_id = p_comp_loc
287 and organization_id = p_org_id
288 and subinventory_code = p_comp_sub ;
289
290 if (l_success = 1 ) then
291 -- Modified for Bug 11695990. In, IN-OUT Variables should be different.
292 l_comp_loc := p_comp_loc;
293 pjm_project_locator.Get_DefaultProjectLocator(
294 p_organization_id => p_org_id,
295 p_locator_id => l_comp_loc,
296 p_project_id => p_proj_id,
297 p_task_id => p_task_id,
298 p_project_locator_id => p_comp_loc ) ;
299 end if ;
300 end if ;
301 end if ;
302
303 return 1;
304
305 exception
306 when others then
307 return 0;
308 end completion_locator_id;
309
310
311 function completion_loc(p_comp_loc in out NOCOPY number,
312 p_item_id in number,
313 p_org_id in number,
314 p_alt_rtg_des in varchar2,
315 p_proj_id in number,
316 p_task_id in number,
317 p_comp_sub in varchar2,
318 p_txn_int_id in number default null ) return number is
319 x_success number := 0;
320 x_comp_sub varchar2(10);
321 x_loc_seg_exists number := 0 ;
322 x_item_loc_control number := null ;
323 x_item_loc_restrict number := null ;
324 x_item_sub_restrict number := null ;
325 x_sub_loc_control number := null ;
326 x_org_loc_control number := null ;
327 general exception;
328 begin
329
330
331
332 if (p_comp_loc is null) and (p_comp_sub is not null) and (p_proj_id is not null) then
333
334 if p_txn_int_id is not null then
335
336 select count(*) into x_loc_seg_exists
337 from mtl_transactions_interface
338 where transaction_interface_id = p_txn_int_id
339 and ( LOC_SEGMENT1 is not null
340 or LOC_SEGMENT2 is not null
341 or LOC_SEGMENT3 is not null
342 or LOC_SEGMENT4 is not null
343 or LOC_SEGMENT5 is not null
344 or LOC_SEGMENT6 is not null
345 or LOC_SEGMENT7 is not null
346 or LOC_SEGMENT8 is not null
347 or LOC_SEGMENT9 is not null
348 or LOC_SEGMENT10 is not null
349 or LOC_SEGMENT11 is not null
350 or LOC_SEGMENT12 is not null
351 or LOC_SEGMENT13 is not null
352 or LOC_SEGMENT14 is not null
353 or LOC_SEGMENT15 is not null
354 or LOC_SEGMENT16 is not null
355 or LOC_SEGMENT17 is not null
356 or LOC_SEGMENT18 is not null
357 or LOC_SEGMENT19 is not null
358 or LOC_SEGMENT20 is not null
359 ) ;
360
361 end if;
362
363
364 if (p_txn_int_id is null) or (x_loc_seg_exists = 0) then
365
366 x_success := routing_completion_sub_loc(
367 p_rout_comp_sub => x_comp_sub,
368 p_rout_comp_loc => p_comp_loc,
369 p_item_id => p_item_id,
370 p_org_id => p_org_id,
371 p_alt_rtg_des => p_alt_rtg_des) ;
372
373 if (x_success = 0) then
374 raise general;
375 end if;
376
377 if x_comp_sub <> p_comp_sub then
378 p_comp_loc := null ;
379 end if ;
380
381 end if ;
382
383 end if;
384
385
386 if (p_proj_id is not null) and (p_comp_sub is not null) and (p_comp_loc is not null) then
387
388 /*****************************************************
389 The following piece of code that checks for dynamic
390 locator creation, will be replaced by call to
391 pacifia's routine.
392 *****************************************************/
393
394 if (pjm_project_locator.check_itemlocatorcontrol(
395 p_organization_id => p_org_id,
396 p_sub => p_comp_sub,
397 p_loc => p_comp_loc,
398 p_item_id => p_item_id,
399 p_hardpeg_only => 2 ) = TRUE ) then
400
401 select count(*) into x_success
402 from mtl_item_locations
403 where inventory_location_id = p_comp_loc
404 and organization_id = p_org_id
405 and subinventory_code = p_comp_sub ;
406
407 if (x_success = 1 ) then
408
409 pjm_project_locator.Get_DefaultProjectLocator(
410 p_organization_id => p_org_id,
411 p_locator_id => p_comp_loc,
412 p_project_id => p_proj_id,
413 p_task_id => p_task_id,
414 p_project_locator_id => p_comp_loc ) ;
415
416 end if ;
417
418 end if ;
419
420
421 end if ;
422
423 return 1;
424
425 exception
426
427 when others then
428 return 0;
429
430 end completion_loc ;
431
432
433
434 function schedule_group_id(p_sched_grp_id in out NOCOPY number) return number is
435 x_success number := 0;
436 begin
437
438 return 1;
439
440 exception
441 when others then
442 return 0;
443
444
445 end schedule_group_id ;
446
447
448
449 function build_sequence(p_build_seq in out NOCOPY number) return number is
450 x_success number := 0;
451 begin
452
453 return 1;
454
455 exception
456 when others then
457 return 0;
458
459
460 end build_sequence ;
461
462
463
464 function src_project_id(p_src_proj_id in out NOCOPY number,
465 p_proj_id in out NOCOPY number) return number is
466 x_success number := 0;
467 begin
468
469 if(p_src_proj_id is null) and (p_proj_id is not null) then
470 p_src_proj_id := p_proj_id ;
471 elsif (p_src_proj_id is not null) and (p_proj_id is null) then
472 p_proj_id := p_src_proj_id ;
473 end if ;
474
475 return 1;
476
477 exception
478 when others then
479 return 0;
480
481 end src_project_id ;
482
483
484 function src_task_id(p_src_task_id in out NOCOPY number,
485 p_task_id in out NOCOPY number) return number is
486 x_success number := 0;
487 begin
488
489
490 if(p_src_task_id is null) and (p_task_id is not null) then
491 p_src_task_id := p_task_id ;
492 elsif (p_src_task_id is not null) and (p_task_id is null) then
493 p_task_id := p_src_task_id ;
494 end if ;
495
496 return 1;
497
498 exception
499 when others then
500 return 0;
501
502 end src_task_id ;
503
504
505
506 function schedule_number(p_sched_num in out NOCOPY varchar2) return number is
507 begin
508
509 if(p_sched_num is null) then
510
511 select WIP_JOB_NUMBER_S.nextval into p_sched_num
512 from dual ;
513 p_sched_num := substr(FND_PROFILE.value('WIP_JOB_PREFIX'),1,20)
514 || p_sched_num ;
515
516 end if ;
517
518 return 1;
519
520 exception
521 when others then
522 return 0;
523 end schedule_number ;
524
525
526 function Last_Updated_Id(p_last_up_by_name in out NOCOPY varchar2,
527 p_last_up_id in out NOCOPY number) return number is
528 x_success number := 0;
529 BEGIN
530
531 if (p_last_up_id is null) and (p_last_up_by_name is not null) then
532
533 select USER_ID
534 into p_last_up_id
535 from FND_USER
536 where USER_NAME = p_last_up_by_name ;
537
538 elsif (p_last_up_id is not null) and (p_last_up_by_name is null) then
539
540 select USER_NAME
541 into p_last_up_by_name
542 from FND_USER
543 where USER_ID = p_last_up_id ;
544
545 end if ;
546
547 return 1;
548
549 exception
550 when others then
551 return 0;
552
553 END Last_Updated_Id;
554
555 function Created_By_ID(p_created_by_name in out NOCOPY varchar2,
556 p_created_id in out NOCOPY number) return number is
557 x_success number := 0;
558 BEGIN
559
560 if (p_created_id is null) and (p_created_by_name is not null) then
561
562 select USER_ID
563 into p_created_id
564 from FND_USER
565 where USER_NAME = p_created_by_name ;
566
567 elsif (p_created_id is not null) and (p_created_by_name is null) then
568
569 select USER_NAME
570 into p_created_by_name
571 from FND_USER
572 where USER_ID = p_created_id ;
573
574 end if ;
575
576 return 1;
577
578 exception
579 when others then
580 return 0;
581
582 END Created_By_ID;
583
584
585
586 function Organization_Code(p_org_name in out NOCOPY varchar2,
587 p_org_id in out NOCOPY number) return number is
588 x_success number := 0;
589 BEGIN
590
591 if (p_org_id is null) and (p_org_name is not null) then
592
593 select ORGANIZATION_ID
594 into p_org_id
595 --from ORG_ORGANIZATION_DEFINITIONS
596 from mtl_parameters
597 where ORGANIZATION_CODE = p_org_name ;
598
599 elsif (p_org_id is not null) and (p_org_name is null) then
600
601 select ORGANIZATION_CODE
602 into p_org_name
603 --from ORG_ORGANIZATION_DEFINITIONS
604 from mtl_parameters
605 where ORGANIZATION_ID = p_org_id ;
606
607 end if ;
608
609 return 1;
610
611 exception
612 when others then
613 return 0;
614
615
616
617 END Organization_Code;
618
619
620
621 /**********************************************************
622 * This particular function should be called only for *
623 * Scheduled Flow Schedules as in the case of Unscheduled *
624 * Flow Schedules we create a New Flow Schedule at runtime *
625 * and this does not make sense for them *
626 * *
627 * - dsoosai 12/11/97 *
628 **********************************************************/
629
630 function Transaction_Source_Name(
631 p_txn_src_name in out NOCOPY varchar2,
632 p_txn_src_id in out NOCOPY number,
633 p_Org_id in number) return number is
634
635 BEGIN
636
637
638 if (p_txn_src_id is null) and (p_txn_src_name is not null) then
639
640 select wip_entity_id
641 into p_txn_src_id
642 from wip_entities we
643 where we.organization_id = p_org_id
644 and we.wip_entity_name = p_txn_src_name ;
645
646 elsif (p_txn_src_id is not null) and (p_txn_src_name is null) then
647
648 select wip_entity_name
649 into p_txn_src_name
650 from wip_entities we
651 where we.organization_id = p_org_id
652 and we.wip_entity_id = p_txn_src_id ;
653
654 end if ;
655
656 return 1;
657
658 exception
659 when others then
660 return 0 ;
661
662 END Transaction_Source_Name;
663
664
665
666 function Scheduled_Flow_Derivation(
667 p_txn_action_id IN NUMBER,-- CFM Scrap
668 p_item_id in number,
669 p_org_id in number,
670 p_txn_src_id in number,
671 p_sched_num in out NOCOPY varchar2,
672 p_src_proj_id in out NOCOPY number,
673 p_proj_id in out NOCOPY number,
674 p_src_task_id in out NOCOPY number,
675 p_task_id in out NOCOPY number,
676 p_bom_rev in out NOCOPY varchar2,
677 p_rev in out NOCOPY varchar2,
678 p_bom_rev_date in out NOCOPY date,
679 p_rout_rev in out NOCOPY varchar2,
680 p_rout_rev_date in out NOCOPY date,
681 p_comp_sub in out NOCOPY varchar2,
682 p_class_code in out NOCOPY varchar2,
683 p_wip_entity_type in out NOCOPY number,
684 p_comp_loc in out NOCOPY number,
685 p_alt_rtg_des in out NOCOPY varchar2,
686 p_alt_bom_des in out NOCOPY varchar2) return number is
687 x_success number := 0;
688 x_sched_cmp_date date;
689 begin
690
691 select
692 nvl(p_sched_num, schedule_number),
693 nvl(p_src_proj_id,project_id),
694 nvl(p_src_task_id,task_id),
695 nvl(p_proj_id,project_id),
696 nvl(p_task_id,task_id),
697 nvl(p_bom_rev,bom_revision),
698 nvl(p_bom_rev_date,bom_revision_date),
699 nvl(p_rout_rev,routing_revision),
700 nvl(p_rout_rev_date,routing_revision_date),
701 Decode(p_txn_action_id, 30, NULL, nvl(p_comp_sub,completion_subinventory)),-- CFM Scrap
702 nvl(p_class_code,class_code),
703 nvl(p_wip_entity_type,4), -- Work Order-less Completions
704 -- fix bug#956467
705 Decode(p_txn_action_id, 30, NULL,nvl(p_comp_loc,completion_locator_id)),-- CFM Scrap
706 nvl(p_alt_rtg_des,alternate_routing_designator),
707 nvl(p_alt_bom_des,alternate_bom_designator),
708 scheduled_completion_date
709 into
710 p_sched_num,
711 p_src_proj_id,
712 p_src_task_id,
713 p_proj_id,
714 p_task_id,
715 p_bom_rev,
716 p_bom_rev_date,
717 p_rout_rev,
718 p_rout_rev_date,
719 p_comp_sub,
720 p_class_code,
721 p_wip_entity_type,
722 p_comp_loc,
723 p_alt_rtg_des,
724 p_alt_bom_des,
725 x_sched_cmp_date
726 from
727 wip_flow_schedules
728 where
729 wip_entity_id = p_txn_src_id
730 and organization_id = p_org_id ;
731
732
733 x_success := 1 ;
734
735 --if p_bom_rev is null then(fix bug#1032431)
736
737 x_success := bom_revision(
738 p_bom_rev => p_bom_rev,
739 p_rev => p_rev,
740 p_bom_rev_date => p_bom_rev_date,
741 p_item_id => p_item_id,
742 p_start_date => x_sched_cmp_date,
743 p_Org_id => p_org_id ) ;
744 --end if ;
745
746
747 if (p_rout_rev is null) and (x_success = 1) then
748
749 x_success := routing_revision(
750 p_rout_rev => p_rout_rev,
751 p_rout_rev_date => p_rout_rev_date,
752 p_item_id => p_item_id,
753 p_start_date => x_sched_cmp_date,
754 p_Org_id => p_org_id ) ;
755
756 end if;
757
758 /* The revision should be derived based on the transaction date, but for CFM the scheduled completion date has to be the same as the transaction date. We are using this fact here to derive the bill and routing revisions */
759
760 return x_success;
761
762 exception
763
764 when others then
765 return 0;
766
767 end Scheduled_Flow_Derivation ;
768
769
770
771
772 function Flow_Form_Defaulting(
773 p_txn_action_id IN NUMBER,-- CFM Scrap
774 p_txn_type_id in number,
775 p_item_id in number,
776 p_org_id in number,
777 p_start_date in date,
778 p_alt_rtg_des in varchar2,
779 p_bom_rev in out NOCOPY varchar2,
780 p_rev in out NOCOPY varchar2,
781 p_bom_rev_date in out NOCOPY date,
782 p_rout_rev in out NOCOPY varchar2,
783 p_rout_rev_date in out NOCOPY date,
784 p_comp_sub in out NOCOPY varchar2,
785 p_comp_loc in out NOCOPY number,
786 p_proj_id in number,
787 p_task_id in number) return number is
788
789 x_bom_ret number := 0 ;
790 x_rout_ret number := 0;
791 x_sub_ret number := 0;
792 x_loc_ret number := 0 ;
793
794 begin
795
796
797 x_bom_ret := bom_revision (
798 p_bom_rev => p_bom_rev,
799 p_rev => p_rev,
800 p_bom_rev_date => p_bom_rev_date,
801 p_item_id => p_item_id,
802 p_start_date => p_start_date,
803 p_Org_id => p_org_id );
804
805
806
807 x_rout_ret := routing_revision(
808 p_rout_rev => p_rout_rev,
809 p_rout_rev_date => p_rout_rev_date,
810 p_item_id => p_item_id,
811 p_start_date => p_start_date,
812 p_Org_id => p_org_id );
813
814
815
816 IF (p_txn_action_id <> 30) then
817 x_sub_ret := routing_completion_sub_loc(
818 p_rout_comp_sub => p_comp_sub,
819 p_rout_comp_loc => p_comp_loc,
820 p_item_id => p_item_id,
821 p_org_id => p_org_id,
822 p_alt_rtg_des => p_alt_rtg_des);
823
824
825
826 x_loc_ret := Completion_loc(
827 p_comp_loc => p_comp_loc,
828 p_item_id => p_item_id,
829 p_org_id => p_org_id,
830 p_alt_rtg_des => p_alt_rtg_des,
831 p_proj_id => p_proj_id,
832 p_task_id => p_task_id,
833 p_comp_sub => p_comp_sub,
834 p_txn_int_id => null) ;
835 /* for bug 3710003
836 check if subinventory is inactive, if yes then make subinv and locator null
837 */
838 if p_comp_sub is not null then
839 BEGIN
840 select null,null /*completion subinventory is inactive */
841 into p_comp_sub,p_comp_loc
842 from mtl_secondary_inventories
843 where organization_id = p_org_id
844 and secondary_inventory_name = p_comp_sub
845 and (
846 NVL(disable_date, TRUNC(p_start_date)+1) <= TRUNC(p_start_date)
847 OR
848 /*Bug3784658. Consider material status */
849 wip_utilities.is_status_applicable(NULL,
850 p_txn_type_id,
851 NULL,
852 NULL,
853 p_org_id,
854 p_item_id,
855 p_comp_sub,
856 NULL,
857 NULL,
858 NULL,
859 'Z') = 'N');
860 x_sub_ret := 1;
861 x_loc_ret := 1;
862 EXCEPTION
863 when no_data_found then /*completion subinventory is active */
864 null;
865 END;
866 end if;
867
868 ELSE
869 x_sub_ret := 1;
870 x_loc_ret := 1;
871 END IF;
872
873 return (x_bom_ret * x_rout_ret * x_sub_ret * x_loc_ret) ;
874
875 exception
876
877 when others then
878 return 0 ;
879
880 End Flow_Form_Defaulting ;
881
882
883
884
885
886 End Wip_Flow_Derive ;
[Home] [Help]