[Home] [Help]
PACKAGE BODY: APPS.ARP_ALOC_PKG
Source
1 PACKAGE BODY arp_aloc_pkg as
2 /* $Header: AROALOCB.pls 115.0 99/07/17 00:00:37 porting ship $ */
3 --
4 -- FUNCTION
5 -- user_value
6 -- PUBLIC
7 --
8 -- DESCRIPTION
9 -- This function returns the location_segment_user_value for a location_segemt_value.
10 -- Given any mixed case string this function will return the correct user_value
11 -- for it.
12 --
13 -- SCOPE -
14 --
15 -- EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
16 --
17 -- ARGUMENTS : IN: p_segment_qualifier
18 -- p_segment_value
19 --
20 -- OUT:
21 --
22 --
23 -- RETURNS : NONE
24 --
25 -- NOTES
26 --
27 -- MODIFICATION HISTORY - Created by Kevin Hudson
28 --
29 --
30 function user_value ( p_segment_qualifier in varchar2,
31 p_segment_value in varchar2 ) return varchar2 is
32 --
33 cursor c_user_value(p_segment_qualifier in varchar2,
34 p_segment_value in varchar2 ) is
35 --
39 and location_segment_value = upper(p_segment_value);
36 select distinct location_segment_user_value
37 from ar_location_values
38 where location_segment_qualifier = p_segment_qualifier
40 --
41 l_segment_user_value varchar2(60);
42 begin
43 open c_user_value( p_segment_qualifier,p_segment_value);
44 --
45 fetch c_user_value into l_segment_user_value;
46 --
47 close c_user_value;
48 --
49 return(l_segment_user_value);
50 end user_value;
51 --
52 -- FUNCTION
53 -- user_value_matches_id
54 -- PUBLIC
55 --
56 -- DESCRIPTION
57 -- This function returns checks to see if the location_segment_user_value and a
58 -- location_segement_id matche
59 -- for it.
60 --
61 -- SCOPE -
62 --
63 -- EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
64 --
65 -- ARGUMENTS : IN: p_segment_user_value
66 -- p_segment_is
67 --
68 -- OUT:
69 --
70 --
71 -- RETURNS : TRUE if location_segment_user_value and location_segement_id are compatible
72 -- : FALSE if location_segment_user_value and location_segement_id are NOT compatible
73 --
74 -- NOTES
75 --
76 -- MODIFICATION HISTORY - Created by Kevin Hudson
77 --
78 --
79 function user_value_matches_id ( p_segment_user_value in varchar2,
80 p_segment_id in number ) return boolean is
81 --
82 dummy number;
83 --
84 begin
85 select 1
86 into dummy
87 from ar_location_values
88 where location_segment_id = p_segment_id
89 and location_segment_user_value = p_segment_user_value;
90 --
91 return(TRUE);
92 exception
93 when NO_DATA_FOUND then
94 return(FALSE);
95
96 end user_value_matches_id;
97 --
98 -- FUNCTION
99 -- unique_postal_code
100 --
101 -- PUBLIC
102 --
103 -- DESCRIPTION
104 -- This function returns a postal_code if it is the only postal_code for a segment_value
105 --
106 -- SCOPE -
107 --
108 -- EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
109 --
110 -- ARGUMENTS : IN: - p_location_segment_id
111 --
112 -- OUT:
113 --
114 --
115 -- RETURNS : postal_code
116 --
117 -- NOTES
118 --
119 -- MODIFICATION HISTORY - Created by Kevin Hudson
120 --
121 --
122
123
124 function unique_postal_code ( p_segment_id in number ) return varchar2 is
125 --
126 l_to_postal_code varchar2(60);
127 l_from_postal_code varchar2(60);
128 --
129 begin
130 --
131 select from_postal_code,
132 to_postal_code
133 into l_from_postal_code,
134 l_to_postal_code
135 from ar_postal_code_ranges_v
136 where location_segment_id = p_segment_id;
137
138 if l_from_postal_code = substr(l_to_postal_code,1,length(l_from_postal_code)) then
139 return(l_from_postal_code);
140 else
141 return(null);
142 end if;
143
144 exception
145 when NO_DATA_FOUND or TOO_MANY_ROWS then
146 return(null);
147
148 end unique_postal_code;
149 --
150 -- FUNCTION
151 -- user_value_matches_id
152 -- PUBLIC
153 --
154 -- DESCRIPTION
155 -- This function returns a the parent segmenr_id and user_value for a child segment_id
156 --
157 -- SCOPE -
158 --
159 -- EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
160 --
161 -- ARGUMENTS : IN: - p_child_segment_id
162 --
163 -- OUT: p_parent_segment_id
164 -- p_parent_segment_user_value
165 --
166 --
167 -- RETURNS :
168 --
169 -- NOTES
170 --
171 -- MODIFICATION HISTORY - Created by Kevin Hudson
172 --
173 --
174 procedure parent_value_and_id ( p_child_segment_id in number,
175 p_parent_segment_id out number,
176 p_parent_segment_user_val out varchar2 ) is
177 --
178 begin
179 --
180 select parent_id,
181 parent_user_value
182 into p_parent_segment_id,
183 p_parent_segment_user_val
184 from ar_loc_two_level_v
185 where child_id = p_child_segment_id;
186 --
187 end parent_value_and_id;
188
189 end arp_aloc_pkg;