1 PACKAGE BODY pay_jp_formula_function_pkg AS
2 /* $Header: pyjpffuc.pkb 120.0.12010000.2 2009/02/09 05:50:38 keyazawa ship $ */
3 --
4 c_package constant varchar2(30) := 'pay_jp_formula_function_pkg.';
5 g_debug boolean := hr_utility.debug_enabled;
6 --
7 /* ------------------------------------------------------------------------------------ --
8 -- GET_TABLE_VALUE_WITH_DEFAULT
9 -- return the value of specified user defined table
10 -- if fetched return value is null,
11 -- if p_default_by_row is setted 'Y',
12 -- return the udt value by specifying row as p_default_value
13 -- if p_default_by_row is not setted or is setted 'Y' or else,
14 -- return the specified p_default_value directly.
15 -- USAGE: p_default_value : Set return default value
16 -- or column value to fetch udt value
17 -- when p_default_by_row is Y.
18 -- p_default_by_row : Set Y or N.
19 -- If set Y, Use p_default_value as parameter to fetch return value
20 -- If set N(null) or else, Use p_default_value as default return value.
21 -- ------------------------------------------------------------------------------------ */
22 FUNCTION get_table_value_with_default(
23 p_business_group_id IN NUMBER,
24 p_table_name IN VARCHAR2,
25 p_column_name IN VARCHAR2,
26 p_row_value IN VARCHAR2,
27 p_effective_date IN DATE DEFAULT NULL,
28 p_default_value IN VARCHAR2,
29 p_default_by_row IN VARCHAR2 DEFAULT 'N')
30 RETURN VARCHAR2
31 IS
32 --
33 l_effective_date date;
34 l_range_or_match pay_user_tables.range_or_match%type;
35 l_user_table_id pay_user_tables.user_table_id%type;
36 l_value pay_user_column_instances_f.value%type;
37 l_legislation_code per_business_groups.legislation_code%type;
38 --
39 cursor csr_value_match
40 is
41 select puci.value
42 from pay_user_column_instances_f puci,
43 pay_user_columns puc,
44 pay_user_rows_f pur,
45 pay_user_tables put
46 where put.user_table_id = l_user_table_id
47 and pur.user_table_id = put.user_table_id
48 and l_effective_date
49 between pur.effective_start_date and pur.effective_end_date
50 and nvl(pur.business_group_id, p_business_group_id) = p_business_group_id
51 and nvl(pur.legislation_code, l_legislation_code) = l_legislation_code
52 and decode(put.user_key_units,
53 'D', to_char(fnd_date.canonical_to_date(pur.row_low_range_or_name)),
54 'N', pur.row_low_range_or_name,
55 'T', pur.row_low_range_or_name,
56 null)
57 =
58 decode(put.user_key_units,
59 'D', to_char(fnd_date.canonical_to_date(p_row_value)),
60 'N', p_row_value,
61 'T', p_row_value,
62 null)
63 and puc.user_table_id = put.user_table_id
64 and puc.user_column_name = p_column_name
65 and nvl(puc.business_group_id, p_business_group_id) = p_business_group_id
66 and nvl(puc.legislation_code, l_legislation_code) = l_legislation_code
67 and puci.user_row_id = pur.user_row_id
68 and puci.user_column_id = puc.user_column_id
69 and l_effective_date
70 between puci.effective_start_date and puci.effective_end_date;
71 --
72 cursor csr_value_range
73 is
74 select puci.value
75 from pay_user_column_instances_f puci,
76 pay_user_columns puc,
77 pay_user_rows_f pur,
78 pay_user_tables put
79 where put.user_table_id = l_user_table_id
80 and put.user_key_units = 'N'
81 and pur.user_table_id = put.user_table_id
82 and l_effective_date
83 between pur.effective_start_date and pur.effective_end_date
84 and nvl(pur.business_group_id, p_business_group_id) = p_business_group_id
85 and nvl(pur.legislation_code, l_legislation_code) = l_legislation_code
86 and fnd_number.canonical_to_number(p_row_value)
87 between fnd_number.canonical_to_number(pur.row_low_range_or_name)
88 and fnd_number.canonical_to_number(pur.row_high_range)
89 and puc.user_table_id = put.user_table_id
90 and puc.user_column_name = p_column_name
91 and nvl(puc.business_group_id, p_business_group_id) = p_business_group_id
92 and nvl(puc.legislation_code, l_legislation_code) = l_legislation_code
93 and puci.user_row_id = pur.user_row_id
94 and puci.user_column_id = puc.user_column_id
95 and l_effective_date
96 between puci.effective_start_date and puci.effective_end_date;
97 BEGIN
98 --
99 /* Use either the supplied date, or the date from fnd_sessions */
100 if p_effective_date is null then
101 select effective_date
102 into l_effective_date
103 from fnd_sessions
104 where session_id = userenv('sessionid');
105 else
106 l_effective_date := p_effective_date;
107 end if;
108 --
109 /* Get the legislation code */
110 select legislation_code
111 into l_legislation_code
112 from per_business_groups
113 where business_group_id = p_business_group_id;
114 --
115 /* Get the type of query to be performed, either range or match */
116 select range_or_match,
117 user_table_id
118 into l_range_or_match,
119 l_user_table_id
120 from pay_user_tables
121 where user_table_name = p_table_name
122 and nvl(business_group_id, p_business_group_id) = p_business_group_id
123 and nvl(legislation_code, l_legislation_code) = l_legislation_code;
124 --
125 /* Get the value */
126 /* + Matched */
127 if l_range_or_match = 'M' then
128 open csr_value_match;
129 fetch csr_value_match into l_value;
130 close csr_value_match;
131 /* + Range */
132 else
133 open csr_value_range;
134 fetch csr_value_range into l_value;
135 close csr_value_range;
136 end if;
137 --
138 /* Get default value if the value is null */
139 if l_value is null then
140 if p_default_by_row = 'Y' then
141 l_value := hruserdt.get_table_value(
142 p_bus_group_id => p_business_group_id,
143 p_table_name => p_table_name,
144 p_col_name => p_column_name,
145 p_row_value => p_default_value,
146 p_effective_date => l_effective_date);
147 else
148 l_value := p_default_value;
149 end if;
150 end if;
151 --
152 RETURN l_value;
153 END get_table_value_with_default;
154 /* ------------------------------------------------------------------------------------ --
155 -- CHK_SMC
156 -- return the 'TRUE' or 'FALSE'
157 -- If a value which is confirmed is on UDT, returns 'TRUE'. If a value is not on UDT,
158 -- returns 'FALSE'.
159 -- USAGE:
160 -- Name Reqd Type Description
161 -- p_table_name Yes VARCHAR2 UDT table name.
162 -- p_column_name Yes VARCHAR2 UDT column name.
163 -- p_effective_date Yes DATE effective_date.
164 -- p_value Yes VARCHAR2 value to be confirmed.
165 -- ------------------------------------------------------------------------------------ */
166 FUNCTION chk_smc(
167 p_table_name IN VARCHAR2,
168 p_column_name IN VARCHAR2,
169 p_effective_date IN DATE,
170 p_value IN VARCHAR2) RETURN VARCHAR2
171 IS
172 --
173 l_value_exists VARCHAR2(1);
174 --
175 CURSOR udt_value_exists IS
176 select 'Y'
177 from pay_user_column_instances_f i,
178 pay_user_rows_f r,
179 pay_user_columns c,
180 pay_user_tables t
181 where t.legislation_code ='JP'
182 and t.business_group_id is null
183 and t.user_table_name = p_table_name
184 and c.legislation_code = 'JP'
185 and c.business_group_id is null
186 and c.user_table_id = t.user_table_id
187 and c.user_column_name = p_column_name
188 and r.user_table_id = t.user_table_id
189 and p_effective_date between r.effective_start_date and r.effective_end_date
190 and r.legislation_code = 'JP'
191 and r.business_group_id is null
192 and p_effective_date between i.effective_start_date and i.effective_end_date
193 and i.user_row_id = r.user_row_id
194 and i.user_column_id = c.user_column_id
195 and i.value = p_value
196 ;
197 BEGIN
198 OPEN udt_value_exists;
199 FETCH udt_value_exists INTO l_value_exists;
200 --
201 IF udt_value_exists%NOTFOUND THEN
202 --
203 return 'FALSE';
204 --
205 ELSE
206 return 'TRUE';
207 END IF;
208 CLOSE udt_value_exists;
209 END chk_smc;
210 /* ------------------------------------------------------------------------------------ */
211 FUNCTION get_jp_parameter(
212 p_owner IN VARCHAR2,
213 p_parameter_name IN VARCHAR2) RETURN VARCHAR2 IS
214 --
215 CURSOR cel_jp_parameter IS
216 SELECT parameter_value FROM hr_jp_parameters
217 WHERE owner = p_owner
218 AND parameter_name = p_parameter_name;
219 --
220 l_parameter_value hr_jp_parameters.parameter_value%TYPE;
221 --
222 BEGIN
223 --
224 OPEN cel_jp_parameter;
225 FETCH cel_jp_parameter INTO l_parameter_value;
226 --
227 IF cel_jp_parameter%NOTFOUND THEN
228 --
229 l_parameter_value := NULL;
230 --
231 END IF;
232 --
233 CLOSE cel_jp_parameter;
234 --
235 RETURN l_parameter_value;
236 --
237 END get_jp_parameter;
238 --
239 --
240 function get_global_value(
241 p_business_group_id in number,
242 p_global_name in varchar2,
243 p_effective_date in date default null)
244 return varchar2
245 is
246 --
247 l_proc varchar2(80) := c_package||'get_global_value';
248 --
249 l_value ff_globals_f.global_value%type;
250 --
251 l_skip boolean := false;
252 l_effective_date date := p_effective_date;
253 l_glb_tbl_cnt number;
254 --
255 cursor csr_global_value
256 is
257 select global_value
258 from ff_globals_f
259 where global_name = p_global_name
260 and nvl(legislation_code,g_legislation_code) = g_legislation_code
261 and nvl(business_group_id,p_business_group_id) = p_business_group_id
262 and l_effective_date
263 between effective_start_date and effective_end_date;
264 --
265 begin
266 --
267 if g_debug then
268 hr_utility.set_location(l_proc,0);
269 end if;
270 --
271 -- normally not pass here.
272 if l_effective_date is null then
273 --
274 if g_session_id is null
275 or g_session_id <> userenv('sessionid') then
276 --
277 g_session_id := userenv('sessionid');
278 --
279 select effective_date
280 into g_effective_date
281 from fnd_sessions
282 where session_id = g_session_id;
283 --
284 l_effective_date := g_effective_date;
285 --
286 end if;
287 --
288 end if;
289 --
290 if g_debug then
291 hr_utility.set_location(l_proc,10);
292 hr_utility.trace('l_effective_date : '||to_char(l_effective_date,'YYYY/MM/DD'));
293 end if;
294 --
295 if g_effective_date is not null
296 and g_effective_date = l_effective_date then
297 --
298 <<loop_glb>>
299 for i in 1..g_glb_tbl.count loop
300 --
301 -- no support for same global name between cust and prod.
302 if g_glb_tbl(i).global_name = p_global_name then
303 --
304 l_value := g_glb_tbl(i).global_value;
305 l_skip := true;
306 exit loop_glb;
307 --
308 end if;
309 --
310 end loop loop_glb;
311 --
312 if g_debug then
313 hr_utility.set_location(l_proc,20);
314 hr_utility.trace('l_value : '||l_value);
315 end if;
316 --
317 else
318 --
319 g_glb_tbl.delete;
320 --
321 end if;
322 --
323 if g_debug then
324 hr_utility.set_location(l_proc,30);
325 end if;
326 --
327 if not l_skip then
328 --
329 if g_business_group_id is null
330 or g_business_group_id <> p_business_group_id then
331 --
332 g_glb_tbl.delete;
333 g_business_group_id := p_business_group_id;
334 --
335 select legislation_code
336 into g_legislation_code
337 from per_business_groups_perf
338 where business_group_id = g_business_group_id;
339 --
340 end if;
341 --
342 open csr_global_value;
343 fetch csr_global_value into l_value;
344 --
345 if csr_global_value%found then
346 --
347 g_effective_date := l_effective_date;
348 --
349 l_glb_tbl_cnt := g_glb_tbl.count + 1;
350 g_glb_tbl(l_glb_tbl_cnt).global_name := p_global_name;
351 g_glb_tbl(l_glb_tbl_cnt).global_value := l_value;
352 --
353 end if;
354 --
355 close csr_global_value;
356 --
357 if g_debug then
358 hr_utility.set_location(l_proc,40);
359 hr_utility.trace('g_glb_tbl.count : '||g_glb_tbl.count);
360 hr_utility.trace('g_glb_tbl(g_glb_tbl.count).global_name : '||g_glb_tbl(g_glb_tbl.count).global_name);
361 hr_utility.trace('g_glb_tbl(g_glb_tbl.count).global_value : '||g_glb_tbl(g_glb_tbl.count).global_value);
362 end if;
363 --
364 end if;
365 --
366 if g_debug then
367 hr_utility.trace('final l_value : '||l_value);
368 hr_utility.set_location(l_proc,1000);
369 end if;
370 --
371 return l_value;
372 end get_global_value;
373 --
374 END pay_jp_formula_function_pkg;
[Home] [Help]