1 package body wip_cfm_filter as
2 /* $Header: wipnocfb.pls 115.8 2002/12/12 15:12:53 rmahidha ship $ */
3
4
5 function org_item_only_rtg_is_cfm
6 (
7 p_organization_id in number,
8 p_item_id in number
9 )
10 return number
11 is
12 any_cfm_routings number := 2 ;
13
14 /* This cursor finds the cfm_routing_flag value (default SYS_NO) for every
15 * match to the item and organization.
16 */
17 cursor routing_flags is
18 select nvl(cfm_routing_flag,2) as value
19 from bom_operational_routings
20 where assembly_item_id = p_item_id
21 and organization_id = p_organization_id
22 and alternate_routing_designator is null;
23
24 cursor routing_flags1 is
25 select nvl(cfm_routing_flag,2) as value
26 from bom_operational_routings
27 where assembly_item_id = p_item_id
28 and organization_id = p_organization_id
29 and alternate_routing_designator is not null;
30
31 begin
32
33 for routing_flag in routing_flags loop
34 if (routing_flag.value = 3) then --N/w routing exists
35 for routing_flag1 in routing_flags1 loop
36 if (routing_flag1.value = 2) then -- check if a std routing exists for the routing with N/w
37 return 3;
38 end if;
39 end loop;
40 any_cfm_routings := 1;
41 elsif (routing_flag.value = 2) then
42 any_cfm_routings := 2;
43 elsif (routing_flag.value = 1) then
44 any_cfm_routings := 1 ; /* Fixed Bug#2183541 */
45 end if;
46 end loop;
47
48 return any_cfm_routings;
49
50 end org_item_only_rtg_is_cfm ;
51
52
53 function org_item_alt_is_cfm
54 (
55 p_organization_id in number,
56 p_item_id in number,
57 p_alternate_routing_designator in varchar2
58 )
59 return number
60 is
61 is_cfm number ;
62
63 /* Find the routing flags for all matches to the parameters.
64 * There should be at most one match.
65 */
66 cursor routing_flags is
67 select nvl(cfm_routing_flag,2)
68 from bom_operational_routings
69 where assembly_item_id = p_item_id
70 and organization_id = p_organization_id
71 and ((p_alternate_routing_designator is null
72 and alternate_routing_designator is null)
73 or
74 (p_alternate_routing_designator = alternate_routing_designator)) ;
75
76 begin
77
78 open routing_flags ;
79 fetch routing_flags into is_cfm ;
80 if (routing_flags%notfound) then
81 is_cfm := 2 ;
82 end if ;
83 close routing_flags ;
84
85 return is_cfm ;
86
87 end org_item_alt_is_cfm ;
88
89
90 end wip_cfm_filter ;
[Home] [Help]