[Home] [Help]
PACKAGE BODY: APPS.PAY_KR_REPORT_PKG
Source
1 package body pay_kr_report_pkg as
2 /* $Header: pykrrept.pkb 120.2 2005/08/12 02:50:52 mmark noship $ */
3 --
4 -- Global Variables.
5 --
6 g_business_group_id number;
7 g_legislation_code varchar2(2);
8 g_effective_date date;
9 g_assignment_action_id number;
10 g_xassignment_action_id number;
11 g_pre_get_balance_value_index number;
12 g_pre_get_dbitem_value_index number;
13 g_debug constant boolean := hr_utility.debug_enabled;
14 --
15 type value_tbl is table of ff_archive_items.value%type index by binary_integer;
16 type balance_name_tbl is table of pay_balance_types.balance_name%type index by binary_integer;
17 type dimension_name_tbl is table of pay_balance_dimensions.dimension_name%type index by binary_integer;
18 type defined_balance_id_tbl is table of pay_defined_balances.defined_balance_id%type index by binary_integer;
19 type user_entity_id_tbl is table of ff_user_entities.user_entity_id%type index by binary_integer;
20 type user_entity_name_tbl is table of ff_user_entities.user_entity_name%type index by binary_integer;
21 type user_name_tbl is table of ff_database_items.user_name%type index by binary_integer;
22 /*
23 type user_entity_id_tbl is table of ff_archive_items.user_entity_id%type index by binary_integer;
24 type archive_item_rec is record(
25 user_entity_id user_entity_id_tbl,
26 value value_tbl);
27 g_archive_item archive_item_rec;
28 */
29 g_archive_item_value_tbl value_tbl;
30 type pre_get_balance_value_rec is record(
31 balance_name balance_name_tbl,
32 dimension_name dimension_name_tbl,
33 defined_balance_id defined_balance_id_tbl,
34 user_entity_id user_entity_id_tbl);
35 g_pre_get_balance_value pre_get_balance_value_rec;
36 type pre_get_dbitem_value_rec is record(
37 user_entity_id user_entity_id_tbl,
38 user_entity_name user_entity_name_tbl,
39 xuser_entity_id user_entity_id_tbl,
40 xuser_entity_name user_entity_name_tbl,
41 user_name user_name_tbl);
42 g_pre_get_dbitem_value pre_get_dbitem_value_rec;
43 --------------------------------------------------------------------------------
44 function legislation_code(p_business_group_id in number) return varchar2
45 --------------------------------------------------------------------------------
46 is
47 l_legislation_code varchar2(2);
48 cursor csr_legislation_code
49 is
50 select legislation_code
51 from per_business_groups_perf
52 where business_group_id = p_business_group_id;
53 begin
54 hr_api.mandatory_arg_error('get_balance_value', 'business_group_id', p_business_group_id);
55 --
56 open csr_legislation_code;
57 fetch csr_legislation_code into l_legislation_code;
58 if csr_legislation_code%NOTFOUND then
59 close csr_legislation_code;
60 raise no_data_found;
61 end if;
62 close csr_legislation_code;
63 --
64 return l_legislation_code;
65 --
66 end legislation_code;
67 --------------------------------------------------------------------------------
68 procedure pre_get_balance_value(p_business_group_id in number)
69 --------------------------------------------------------------------------------
70 is
71 --
72 l_found boolean := FALSE;
73 --
74 -- cursor modified for bug 3829372
75 --
76 cursor csr_pre_get_balance_value
77 is
78 select
79 pbt.balance_name balance_name,
80 pbd.dimension_name dimension_name,
81 pdb.defined_balance_id defined_balance_id,
82 fue.user_entity_id user_entity_id
83 from ff_user_entities fue,
84 pay_balance_dimensions pbd,
85 pay_defined_balances pdb,
86 pay_balance_types pbt
87 where decode(pbt.business_group_id, null, g_business_group_id, pbt.business_group_id) = g_business_group_id
88 and decode(pbt.legislation_code, null, g_legislation_code, pbt.legislation_code) = g_legislation_code
89 and pdb.balance_type_id = pbt.balance_type_id
90 and pbd.balance_dimension_id = pdb.balance_dimension_id
91 and decode(pbd.business_group_id, null, g_business_group_id, pbd.business_group_id) = g_business_group_id
92 and decode(pbd.legislation_code, null, g_legislation_code, pbd.legislation_code) = g_legislation_code
93 and fue.user_entity_name = 'A_'||pbt.balance_name||pbd.dimension_name
94 and fue.user_entity_name like 'A%'
95 and fue.creator_type = 'X';
96 --
97 begin
98 --
99 if g_business_group_id is null or p_business_group_id <> g_business_group_id then
100 g_business_group_id := p_business_group_id;
101 g_legislation_code := legislation_code(p_business_group_id => g_business_group_id);
102 g_pre_get_balance_value.balance_name.delete;
103 g_pre_get_balance_value.dimension_name.delete;
104 g_pre_get_balance_value.defined_balance_id.delete;
105 g_pre_get_balance_value.user_entity_id.delete;
106 open csr_pre_get_balance_value;
107 fetch csr_pre_get_balance_value bulk collect into g_pre_get_balance_value.balance_name,
108 g_pre_get_balance_value.dimension_name,
109 g_pre_get_balance_value.defined_balance_id,
110 g_pre_get_balance_value.user_entity_id;
111 close csr_pre_get_balance_value;
112 end if;
113 --
114 end pre_get_balance_value;
115 --------------------------------------------------------------------------------
116 procedure pre_get_dbitem_value(p_business_group_id in number)
117 --------------------------------------------------------------------------------
118 is
119 --
120 l_found boolean := FALSE;
121 --
122 cursor csr_pre_get_dbitem_value
123 is
124 select
125 fue.user_entity_id user_entity_id,
126 fue.user_entity_name user_entity_name,
127 xfue.user_entity_id xuser_entity_id,
128 xfue.user_entity_name xuser_entity_name,
129 fdi.user_name user_name
130 from ff_database_items fdi,
131 ff_user_entities xfue,
132 ff_user_entities fue
133 where nvl(fue.business_group_id,g_business_group_id) = g_business_group_id
134 and nvl(fue.legislation_code,g_legislation_code) = g_legislation_code
135 and xfue.user_entity_name = 'A_'||fue.user_entity_name
136 and xfue.user_entity_name like 'A_%'
137 and nvl(xfue.business_group_id,g_business_group_id) = g_business_group_id
138 and nvl(xfue.legislation_code,g_legislation_code) = g_legislation_code
139 and fdi.user_entity_id = fue.user_entity_id
140 and xfue.creator_type = 'X';
141 --
142 begin
143 --
144 if g_business_group_id is null or p_business_group_id <> g_business_group_id then
145 g_business_group_id := p_business_group_id;
146 g_legislation_code := legislation_code(p_business_group_id => g_business_group_id);
147 g_pre_get_dbitem_value.user_entity_id.delete;
148 g_pre_get_dbitem_value.user_entity_name.delete;
149 g_pre_get_dbitem_value.xuser_entity_id.delete;
150 g_pre_get_dbitem_value.xuser_entity_name.delete;
151 g_pre_get_dbitem_value.user_name.delete;
152 open csr_pre_get_dbitem_value;
153 fetch csr_pre_get_dbitem_value bulk collect into g_pre_get_dbitem_value.user_entity_id,
154 g_pre_get_dbitem_value.user_entity_name,
155 g_pre_get_dbitem_value.xuser_entity_id,
156 g_pre_get_dbitem_value.xuser_entity_name,
157 g_pre_get_dbitem_value.user_name;
158 close csr_pre_get_dbitem_value;
159 end if;
160 --
161 end pre_get_dbitem_value;
162 --------------------------------------------------------------------------------
163 function get_defined_balance_id(p_balance_name in varchar2,
164 p_dimension_name in varchar2,
165 p_business_group_id in number) return number
166 --------------------------------------------------------------------------------
167 is
168 --
169 l_defined_balance_id pay_defined_balances.defined_balance_id%type;
170 l_index binary_integer;
171 l_found boolean := FALSE;
172 --
173 cursor csr_defined_balance_id
174 is
175 select pdb.defined_balance_id defined_balance_id
176 from pay_balance_dimensions pbd,
177 pay_defined_balances pdb,
178 pay_balance_types pbt
179 where pbt.balance_name = p_balance_name
180 and nvl(pbt.business_group_id, g_business_group_id) = g_business_group_id
181 and nvl(pbt.legislation_code, g_legislation_code) = g_legislation_code
182 and pdb.balance_type_id = pbt.balance_type_id
183 and pbd.balance_dimension_id = pdb.balance_dimension_id
184 and pbd.dimension_name = p_dimension_name
185 and nvl(pbd.business_group_id, g_business_group_id) = g_business_group_id
186 and nvl(pbd.legislation_code, g_legislation_code) = g_legislation_code;
187 --
188 begin
189 --
190 g_pre_get_balance_value_index := null;
191 --
192 if g_business_group_id is null or p_business_group_id <> g_business_group_id then
193 g_business_group_id := p_business_group_id;
194 g_legislation_code := legislation_code(p_business_group_id => g_business_group_id);
195 g_pre_get_balance_value.balance_name.delete;
196 g_pre_get_balance_value.dimension_name.delete;
197 g_pre_get_balance_value.defined_balance_id.delete;
198 g_pre_get_balance_value.user_entity_id.delete;
199 end if;
200 --
201 -- If pre_get_balance_value has been done during the same session
202 -- before running this function, cache value will be used.
203 --
204 -- pre_get_balance_value(p_business_group_id => g_business_group_id);
205 --
206 l_index := g_pre_get_balance_value.defined_balance_id.count;
207 if l_index > 0 then
208 for i in 1..l_index loop
209 if g_pre_get_balance_value.balance_name(i) = p_balance_name
210 and g_pre_get_balance_value.dimension_name(i) = p_dimension_name then
211 l_defined_balance_id := g_pre_get_balance_value.defined_balance_id(i);
212 g_pre_get_balance_value_index := i;
213 l_found := TRUE;
214 exit;
215 end if;
216 end loop;
217 end if;
218 --
219 if not l_found then
220 open csr_defined_balance_id;
221 fetch csr_defined_balance_id into l_defined_balance_id;
222 close csr_defined_balance_id;
223 end if;
224 --
225 return l_defined_balance_id;
226 --
227 end get_defined_balance_id;
228 --------------------------------------------------------------------------------
229 function get_xbal_user_entity_id(p_defined_balance_id in number,
230 p_business_group_id in number) return number
231 --------------------------------------------------------------------------------
232 is
233 --
234 l_user_entity_id ff_user_entities.user_entity_id%type;
235 l_index binary_integer;
236 l_found boolean := FALSE;
237 --
238 cursor csr_user_entity_id
239 is
240 select fue.user_entity_id user_entity_id
241 from ff_user_entities fue,
242 pay_balance_dimensions pbd,
243 pay_balance_types pbt,
244 pay_defined_balances pdb
245 where pdb.defined_balance_id = p_defined_balance_id
246 and pbt.balance_type_id = pdb.balance_type_id
247 and pbd.balance_dimension_id = pdb.balance_dimension_id
248 /* If creator_id is same as source user_entity_id, it might be simple. */
249 and fue.user_entity_name = 'A_'||pbt.balance_name||pbd.dimension_name
250 and fue.creator_type = 'X';
251 --
252 begin
253 --
254 if g_business_group_id is null or p_business_group_id <> g_business_group_id then
255 g_business_group_id := p_business_group_id;
256 g_legislation_code := legislation_code(p_business_group_id => g_business_group_id);
257 g_pre_get_balance_value.balance_name.delete;
258 g_pre_get_balance_value.dimension_name.delete;
259 g_pre_get_balance_value.defined_balance_id.delete;
260 g_pre_get_balance_value.user_entity_id.delete;
261 end if;
262 --
263 -- If pre_get_balance_value has been done during the same session
264 -- before running this function, cache value will be used.
265 --
266 -- pre_get_balance_value(p_business_group_id => g_business_group_id);
267 --
268 l_index := g_pre_get_balance_value.defined_balance_id.count;
269 if l_index > 0 then
270 if g_pre_get_balance_value_index is not null then
271 if g_pre_get_balance_value.defined_balance_id(g_pre_get_balance_value_index) = p_defined_balance_id then
272 l_user_entity_id := g_pre_get_balance_value.user_entity_id(g_pre_get_balance_value_index);
273 l_found := TRUE;
274 end if;
275 end if;
276 if not l_found then
277 for i in 1..l_index loop
278 if g_pre_get_balance_value.defined_balance_id(i) = p_defined_balance_id then
279 l_user_entity_id := g_pre_get_balance_value.user_entity_id(i);
280 l_found := TRUE;
281 exit;
282 end if;
283 end loop;
284 end if;
285 end if;
286 --
287 if not l_found then
288 open csr_user_entity_id;
289 fetch csr_user_entity_id into l_user_entity_id;
290 close csr_user_entity_id;
291 end if;
292 --
293 return l_user_entity_id;
294 --
295 end get_xbal_user_entity_id;
296 --------------------------------------------------------------------------------
297 function get_user_entity_id(p_user_name in varchar2,
298 p_business_group_id in number) return number
299 --------------------------------------------------------------------------------
300 is
301 --
302 l_user_entity_id ff_user_entities.user_entity_id%type;
303 l_index binary_integer;
304 l_found boolean := FALSE;
305 --
306 cursor csr_user_entity_id
307 is
308 select fue.user_entity_id user_entity_id
309 from ff_user_entities fue,
310 ff_database_items fdi
311 where fdi.user_name = p_user_name
312 and fue.user_entity_id = fdi.user_entity_id
313 and nvl(fue.business_group_id,g_business_group_id) = g_business_group_id
314 and nvl(fue.legislation_code,g_legislation_code) = g_legislation_code;
315 --
316 begin
317 --
318 if g_business_group_id is null or p_business_group_id <> g_business_group_id then
319 g_business_group_id := p_business_group_id;
320 g_legislation_code := legislation_code(p_business_group_id => g_business_group_id);
321 g_pre_get_dbitem_value.user_entity_id.delete;
322 g_pre_get_dbitem_value.user_entity_name.delete;
323 g_pre_get_dbitem_value.xuser_entity_id.delete;
324 g_pre_get_dbitem_value.xuser_entity_name.delete;
325 g_pre_get_dbitem_value.user_name.delete;
326 end if;
327 --
328 -- If pre_get_dbitem_value has been done during the same session
329 -- before running this function, cache value will be used.
330 --
331 -- pre_get_dbitem_value(p_business_group_id => g_business_group_id);
332 --
333 l_index := g_pre_get_dbitem_value.user_entity_id.count;
334 if l_index > 0 then
335 for i in 1..l_index loop
336 if g_pre_get_dbitem_value.user_name(i) = p_user_name then
337 l_user_entity_id := g_pre_get_dbitem_value.user_entity_id(i);
338 g_pre_get_dbitem_value_index := i;
339 l_found := TRUE;
340 exit;
341 end if;
342 end loop;
343 end if;
344 --
345 if not l_found then
346 open csr_user_entity_id;
347 fetch csr_user_entity_id into l_user_entity_id;
348 close csr_user_entity_id;
349 end if;
350 --
351 return l_user_entity_id;
352 --
353 end get_user_entity_id;
354 --------------------------------------------------------------------------------
355 function get_xdbitem_user_entity_id(p_user_entity_id in number,
356 p_business_group_id in number) return number
357 --------------------------------------------------------------------------------
358 is
359 --
360 l_xuser_entity_id ff_user_entities.user_entity_id%type;
361 l_index binary_integer;
362 l_found boolean := FALSE;
363 --
364 cursor csr_xuser_entity_id
365 is
366 select xfue.user_entity_id xuser_entity_id
367 from ff_user_entities xfue,
368 ff_user_entities fue
369 where fue.user_entity_id = p_user_entity_id
370 and xfue.user_entity_name = 'A_'||fue.user_entity_name
371 and nvl(xfue.business_group_id,g_business_group_id) = g_business_group_id
372 and nvl(xfue.legislation_code,g_legislation_code) = g_legislation_code
373 and xfue.creator_type = 'X';
374 --
375 begin
376 --
377 if g_business_group_id is null or p_business_group_id <> g_business_group_id then
378 g_business_group_id := p_business_group_id;
379 g_legislation_code := legislation_code(p_business_group_id => g_business_group_id);
380 g_pre_get_dbitem_value.user_entity_id.delete;
381 g_pre_get_dbitem_value.user_entity_name.delete;
382 g_pre_get_dbitem_value.xuser_entity_id.delete;
383 g_pre_get_dbitem_value.xuser_entity_name.delete;
384 g_pre_get_dbitem_value.user_name.delete;
385 end if;
386 --
387 -- If pre_get_dbitem_value has been done during the same session
388 -- before running this function, cache value will be used.
389 --
390 -- pre_get_dbitem_value(p_business_group_id => g_business_group_id);
391 --
392 l_index := g_pre_get_dbitem_value.user_entity_id.count;
393 if l_index > 0 then
394 if g_pre_get_dbitem_value_index is not null then
395 if g_pre_get_dbitem_value.user_entity_id(g_pre_get_dbitem_value_index) = p_user_entity_id then
396 l_xuser_entity_id := g_pre_get_dbitem_value.xuser_entity_id(g_pre_get_dbitem_value_index);
397 l_found := TRUE;
398 end if;
399 end if;
400 if not l_found then
401 for i in 1..l_index loop
402 if g_pre_get_dbitem_value.user_entity_id(i) = p_user_entity_id then
403 l_xuser_entity_id := g_pre_get_dbitem_value.xuser_entity_id(i);
404 l_found := TRUE;
405 exit;
406 end if;
407 end loop;
408 end if;
409 end if;
410 --
411 if not l_found then
412 open csr_xuser_entity_id;
413 fetch csr_xuser_entity_id into l_xuser_entity_id;
414 close csr_xuser_entity_id;
415 end if;
416 --
417 return l_xuser_entity_id;
418 --
419 end get_xdbitem_user_entity_id;
420 --------------------------------------------------------------------------------
421 function get_latest_assact(p_assignment_id in number,
422 p_business_group_id in number,
423 p_effective_date_from in date,
424 p_effective_date_to in date,
425 p_type in varchar2) return number
426 /* p_type : Run Type Name or Report Category||Report Type */
427 /* (NYEA,RYEA,IYEA,NHIA,RHIA,IHIA) */
428 --------------------------------------------------------------------------------
429 is
430 --
431 l_run_type_id number;
432 l_assignment_action_id number;
433 --
434 cursor csr_run_type
435 is
436 select run_type_id
437 from pay_run_types_f
438 where run_type_name = p_type
439 and g_effective_date
440 between effective_start_date and effective_end_date
441 and nvl(business_group_id, g_business_group_id) = g_business_group_id
442 and nvl(legislation_code, g_legislation_code) = g_legislation_code;
443 --
444 -- Bug 3899570 : Optimized query for csr_latest_assact_run_type
445 -- (From performance repository SQLID: 9609734)
446 cursor csr_latest_assact_run_type
447 is
448 select assignment_action_id
449 from pay_assignment_actions paa
450 where paa.assignment_id = p_assignment_id
451 and paa.run_type_id = l_run_type_id
452 and paa.action_sequence =
453 (
454 select max(paa2.action_sequence)
455 from pay_payroll_actions ppa2,
456 pay_assignment_actions paa2
457 where paa2.assignment_id = p_assignment_id
458 and paa2.run_type_id = l_run_type_id
459 and paa2.action_status in ('C', 'S') -- Bug 4442484: Include 'S'kipped assacts
460 and ppa2.payroll_action_id = paa2.payroll_action_id
461 and ppa2.effective_date
462 between p_effective_date_from and p_effective_date_to
463 and ppa2.action_type in ('R','Q','I','V','B')
464 ) ;
465 -- End of 3899570
466 --
467 cursor csr_latest_assact_report_type
468 is
469 select paa.assignment_action_id
470 from pay_payroll_actions ppa,
471 pay_assignment_actions paa
472 where paa.assignment_id = p_assignment_id
473 and paa.run_type_id is null
474 and paa.action_status in ('C', 'S') -- Bug 4442484: Include 'S'kipped assacts
475 and ppa.payroll_action_id = paa.payroll_action_id
476 and ppa.effective_date
477 between p_effective_date_from and p_effective_date_to
478 and ppa.action_type = 'B'
479 and ppa.report_type = decode(substr(lpad(p_type,4),2,4),'HIA','HIA','YEA')
480 and ppa.report_category = decode(substr(lpad(p_type,4),1,1),'I','I','R','R','N')
481 and ppa.report_qualifier = 'KR'
482 and not exists(
483 select null
484 from pay_payroll_actions ppa2,
485 pay_assignment_actions paa2
486 where paa2.assignment_id = paa.assignment_id
487 and paa2.run_type_id is null
488 and paa2.action_status in ('C', 'S') -- Bug 4442484: Include 'S'kipped assacts
489 and ppa2.payroll_action_id = paa2.payroll_action_id
490 and ppa2.effective_date
491 between p_effective_date_from and p_effective_date_to
492 and ppa2.action_type = 'B'
493 and ppa2.report_type = decode(substr(lpad(p_type,4),2,4),'HIA','HIA','YEA')
494 and ppa2.report_category = decode(substr(lpad(p_type,4),1,1),'I','I','R','R','N')
495 and ppa2.report_qualifier = 'KR'
496 and paa2.action_sequence > paa.action_sequence);
497 --
498 begin
499 --
500 if g_effective_date is null then
501 select effective_date
502 into g_effective_date
503 from fnd_sessions
504 where session_id = userenv('sessionid');
505 end if;
506 --
507 if g_business_group_id is null or p_business_group_id <> g_business_group_id then
508 g_business_group_id := p_business_group_id;
509 g_legislation_code := legislation_code(p_business_group_id => g_business_group_id);
510 end if;
511 --
512 open csr_run_type;
513 fetch csr_run_type into l_run_type_id;
514 close csr_run_type;
515 --
516 if l_run_type_id is not null then
517 open csr_latest_assact_run_type;
518 fetch csr_latest_assact_run_type into l_assignment_action_id;
519 close csr_latest_assact_run_type;
520 else
521 open csr_latest_assact_report_type;
522 fetch csr_latest_assact_report_type into l_assignment_action_id;
523 close csr_latest_assact_report_type;
524 end if;
525 --
526 return l_assignment_action_id;
527 --
528 end get_latest_assact;
529 --------------------------------------------------------------------------------
530 function get_balance_value_asg_run(p_assignment_action_id in number,
531 p_balance_type_id in number) return number
532 --------------------------------------------------------------------------------
533 is
534 --
535 l_value number;
536 --
537 -- There is no latest balance for _ASG_RUN.
538 -- Therefore, Collect balance result directly instead of using pay_balance_pkg.get_value.
539 --
540 cursor csr_balance_value
541 is
542 select
543 nvl(sum(fnd_number.canonical_to_number(prrv.result_value) * pbf.scale),0) value
544 from pay_balance_feeds_f pbf,
545 pay_run_result_values prrv,
546 pay_run_results prr,
547 pay_payroll_actions ppa,
548 pay_assignment_actions paa
549 where paa.assignment_action_id = p_assignment_action_id
550 and ppa.payroll_action_id = paa.payroll_action_id
551 and prr.assignment_action_id = paa.assignment_action_id
552 and prr.status in ('P','PA')
553 and prrv.run_result_id = prr.run_result_id
554 and nvl(prrv.result_value,'0') <> '0'
555 and pbf.input_value_id = prrv.input_value_id
556 and pbf.balance_type_id = p_balance_type_id
557 and ppa.effective_date
558 between pbf.effective_start_date and pbf.effective_end_date;
559 --
560 begin
561 --
562 open csr_balance_value;
563 fetch csr_balance_value into l_value;
564 close csr_balance_value;
565 --
566 return l_value;
567 --
568 end get_balance_value_asg_run;
569 --------------------------------------------------------------------------------
570 function get_archive_items(p_assignment_action_id in number,
571 p_user_entity_id in number) return varchar2
572 --------------------------------------------------------------------------------
573 is
574 --
575 -- l_value ff_archive_items.value%type;
576 -- l_index binary_integer;
577 -- l_found boolean := FALSE;
578 type user_entity_id_tbl is table of ff_archive_items.user_entity_id%type index by binary_integer;
579 l_user_entity_id_tbl user_entity_id_tbl;
580 l_archive_item_value_tbl value_tbl;
581 --
582 cursor csr_archive
583 is
584 select fai.user_entity_id,
585 fai.value
586 from ff_archive_items fai
587 where fai.context1 = p_assignment_action_id
588 and fai.value is not null;
589 --
590 begin
591 --
592 hr_api.mandatory_arg_error('get_archive_items', 'assignment_action_id', p_assignment_action_id);
593 hr_api.mandatory_arg_error('get_archive_items', 'user_entity_id', p_user_entity_id);
594
595 if g_debug then
596 hr_utility.trace('get_archive_items assignment_action_id : ' || p_assignment_action_id);
597 hr_utility.trace('get_archive_items user_entity_id : ' || p_user_entity_id);
598 end if;
599 --
600 -- Cache new information into global variables if cache information is old.
601 --
602 if g_assignment_action_id is null or p_assignment_action_id <> g_assignment_action_id then
603 --
604 -- Bulk collect statement is efficient for better performance.
605 --
606 open csr_archive;
607 fetch csr_archive bulk collect into l_user_entity_id_tbl, l_archive_item_value_tbl;
608 close csr_archive;
609 --
610 -- Re-construct archive item values to user_entity_id indexed PL/SQL table.
611 --
612 g_archive_item_value_tbl.delete;
613 for i in 1..l_user_entity_id_tbl.count loop
614 g_archive_item_value_tbl(l_user_entity_id_tbl(i)) := l_archive_item_value_tbl(i);
615 end loop;
616 g_assignment_action_id := p_assignment_action_id;
617 end if;
618 --
619 if g_archive_item_value_tbl.exists(p_user_entity_id) then
620 return g_archive_item_value_tbl(p_user_entity_id);
621 else
622 return null;
623 end if;
624 /*
625 l_index := g_archive_item.user_entity_id.count;
626 for i in 1..l_index loop
627 if g_archive_item.user_entity_id(i) = p_user_entity_id then
628 l_value := g_archive_item.value(i);
629 l_found := TRUE;
630 exit;
631 end if;
632 end loop;
633 --
634 if not l_found then
635 open csr_archive;
636 fetch csr_archive bulk collect into g_archive_item.user_entity_id, g_archive_item.value;
637 close csr_archive;
638 --
639 for i in 1..g_archive_item.user_entity_id.count loop
640 if g_archive_item.user_entity_id(i) = p_user_entity_id then
641 l_value := g_archive_item.value(i);
642 exit;
643 end if;
644 end loop;
645 --
646 end if;
647 --
648 return l_value;
649 */
650 --
651 end get_archive_items;
652 --------------------------------------------------------------------------------
653 function get_balance_value(p_assignment_action_id in number,
654 p_defined_balance_id in number) return varchar2
655 --------------------------------------------------------------------------------
656 is
657 --
658 l_value varchar2(240);
659 l_user_entity_id number;
660 l_business_group_id number;
661 l_cache varchar2(1);
662 l_archive varchar2(1);
663 --
664 -- Not support for multi action for same action_id
665 --
666 cursor csr_xassact
667 is
668 select ppa.business_group_id,
669 paa.assignment_action_id,
670 nvl(xpaa.assignment_action_id,-1)
671 from pay_payroll_actions xppa,
672 pay_assignment_actions xpaa,
673 pay_action_interlocks pai,
674 pay_payroll_actions ppa,
675 pay_assignment_actions paa
676 where paa.assignment_action_id = p_assignment_action_id
677 and ppa.payroll_action_id = paa.payroll_action_id
678 and pai.locked_action_id (+) = paa.assignment_action_id
679 and xpaa.assignment_action_id (+) = pai.locking_action_id
680 and xpaa.action_status (+) in ('C', 'S') -- Bug 4442484: Include 'S'kipped assacts
681 and xppa.payroll_action_id (+) = xpaa.payroll_action_id
682 and xppa.action_type (+) = 'X';
683 --
684 begin
685 --
686 l_cache := 'N';
687 l_archive := 'N';
688 --
689 hr_api.mandatory_arg_error('get_balance_value', 'assignment_action_id', p_assignment_action_id);
690 hr_api.mandatory_arg_error('get_balance_value', 'defined_balance_id', p_defined_balance_id);
691 --
692 if g_business_group_id is null then
693 l_cache := 'Y';
694 else
695 l_business_group_id := g_business_group_id;
696 end if;
697 if g_business_group_id is null
698 or g_assignment_action_id is null
699 or g_xassignment_action_id < 0
700 or p_assignment_action_id <> g_assignment_action_id then
701 open csr_xassact;
702 fetch csr_xassact into g_business_group_id, g_assignment_action_id, g_xassignment_action_id;
703 close csr_xassact;
704 if l_cache = 'N' and l_business_group_id <> g_business_group_id then
705 l_cache := 'Y';
706 end if;
707 if g_xassignment_action_id > 0 then
708 l_archive := 'Y';
709 else
710 l_archive := 'N';
711 end if;
712 g_legislation_code := legislation_code(p_business_group_id => g_business_group_id);
713 end if;
714 --
715 if g_debug then
716 hr_utility.trace('get_balance_value g_pre_get_balance_value_perf : ' || g_pre_get_balance_value_perf);
717 end if;
718 --
719 if g_pre_get_balance_value_perf = 'Y' then
720 l_business_group_id := g_business_group_id;
721 if l_cache = 'Y' then
722 g_business_group_id := null;
723 end if;
724 pre_get_balance_value(p_business_group_id => l_business_group_id);
725 end if;
726 --
727 if g_xassignment_action_id > 0 and l_archive = 'Y' then
728 l_user_entity_id := get_xbal_user_entity_id(p_defined_balance_id => p_defined_balance_id,
729 p_business_group_id => g_business_group_id);
730 l_value := get_archive_items(p_assignment_action_id => g_xassignment_action_id,
731 p_user_entity_id => l_user_entity_id);
732 else
733 l_value := pay_balance_pkg.get_value(p_defined_balance_id => p_defined_balance_id,
734 p_assignment_action_id => p_assignment_action_id);
735 end if;
736 --
737 return l_value;
738 --
739 end get_balance_value;
740 --------------------------------------------------------------------------------
741 function get_balance_value(p_assignment_action_id in number,
742 p_balance_name in varchar2,
743 p_dimension_name in varchar2) return varchar2
744 --------------------------------------------------------------------------------
745 is
746 --
747 l_value varchar2(240);
748 l_defined_balance_id number;
749 l_business_group_id number;
750 l_cache varchar2(1);
751 --
752 -- Not support for multi action for same action_id
753 --
754 cursor csr_assact
755 is
756 select ppa.business_group_id,
757 paa.assignment_action_id
758 from pay_payroll_actions ppa,
759 pay_assignment_actions paa
760 where paa.assignment_action_id = p_assignment_action_id
761 and ppa.payroll_action_id = paa.payroll_action_id;
762 --
763 begin
764 --
765 l_cache := 'N';
766 --
767 hr_api.mandatory_arg_error('get_balance_value', 'assignment_action_id', p_assignment_action_id);
768 hr_api.mandatory_arg_error('get_balance_value', 'balance_name', p_balance_name);
769 hr_api.mandatory_arg_error('get_balance_value', 'dimension_name', p_dimension_name);
770 --
771 if g_business_group_id is null then
772 l_cache := 'Y';
773 else
774 l_business_group_id := g_business_group_id;
775 end if;
776 if g_business_group_id is null
777 or g_assignment_action_id is null
778 or g_assignment_action_id <> p_assignment_action_id then
779 open csr_assact;
780 fetch csr_assact into g_business_group_id, g_assignment_action_id;
781 close csr_assact;
782 if l_cache = 'N' and l_business_group_id <> g_business_group_id then
783 l_cache := 'Y';
784 end if;
785 g_legislation_code := legislation_code(p_business_group_id => g_business_group_id);
786 end if;
787 --
788 if g_debug then
789 hr_utility.trace('get_balance_value g_pre_get_balance_value_perf : ' || g_pre_get_balance_value_perf);
790 end if;
791 --
792 if g_pre_get_balance_value_perf = 'Y' then
793 l_business_group_id := g_business_group_id;
794 if l_cache = 'Y' then
795 g_business_group_id := null;
796 end if;
797 pre_get_balance_value(p_business_group_id => l_business_group_id);
798 end if;
799 --
800 l_defined_balance_id := get_defined_balance_id(p_balance_name => p_balance_name,
801 p_dimension_name => p_dimension_name,
802 p_business_group_id => g_business_group_id);
803 --
804 l_value := get_balance_value(p_assignment_action_id => g_assignment_action_id,
805 p_defined_balance_id => l_defined_balance_id);
806 --
807 return l_value;
808 --
809 end get_balance_value;
810 --------------------------------------------------------------------------------
811 function get_dbitem_value(p_assignment_action_id in number,
812 p_user_entity_id in number) return varchar2
813 --------------------------------------------------------------------------------
814 is
815 --
816 l_value ff_archive_items.value%type;
817 l_xuser_entity_id number;
818 l_business_group_id number;
819 l_archive varchar2(1);
820 l_cache varchar2(1);
821 --
822 l_user_name ff_database_items.user_name%type;
823 l_payroll_id pay_payroll_actions.payroll_id%type;
824 l_payroll_action_id pay_payroll_actions.payroll_action_id%type;
825 l_assignment_id pay_assignment_actions.assignment_id%type;
826 l_date_earned pay_payroll_actions.date_earned%type;
827 l_tax_unit_id pay_assignment_actions.tax_unit_id%type;
828 --
829 -- Not support for multi action for same action_id
830 --
831 cursor csr_xassact
832 is
833 select ppa.business_group_id,
834 paa.assignment_action_id,
835 nvl(xpaa.assignment_action_id,-1)
836 from pay_payroll_actions xppa,
837 pay_assignment_actions xpaa,
838 pay_action_interlocks pai,
839 pay_payroll_actions ppa,
840 pay_assignment_actions paa
841 where paa.assignment_action_id = p_assignment_action_id
842 and ppa.payroll_action_id = paa.payroll_action_id
843 and pai.locked_action_id (+) = paa.assignment_action_id
844 and xpaa.assignment_action_id (+) = pai.locking_action_id
845 and xpaa.action_status (+) in ('C', 'S') -- Bug 4442484: Include 'S'kipped assacts
846 and xppa.payroll_action_id (+) = xpaa.payroll_action_id
847 and xppa.action_type (+) = 'X';
848 --
849 -- Not Support multiple dbitem for one user entity like max and min and default
850 -- and multiple action.
851 --
852 cursor csr_context
853 is
854 select ppa.payroll_id payroll_id,
855 ppa.payroll_action_id payroll_action_id,
856 paa.assignment_id assignment_id,
857 ppa.date_earned date_earned,
858 paa.tax_unit_id tax_unit_id,
859 fdi.user_name user_name
860 from ff_database_items fdi,
861 pay_payroll_actions ppa,
862 pay_assignment_actions paa
863 where paa.assignment_action_id = g_assignment_action_id
864 and ppa.payroll_action_id = paa.payroll_action_id
865 and fdi.user_entity_id = p_user_entity_id;
866 --
867 begin
868 --
869 l_archive := 'N';
870 l_cache := 'N';
871 --
872 hr_api.mandatory_arg_error('get_dbitem_value', 'assignment_action_id', p_assignment_action_id);
873 hr_api.mandatory_arg_error('get_dbitem_value', 'user_entity_id', p_user_entity_id);
874
875 if g_debug then
876 hr_utility.trace('get_dbitem_value assignment_action_id : ' || p_assignment_action_id);
877 hr_utility.trace('get_dbitem_value user_entity_id : ' || p_user_entity_id);
878 end if;
879 --
880 -- Cache new information into global variables if cache information is old.
881 --
882 if g_business_group_id is null then
883 l_cache := 'Y';
884 else
885 l_business_group_id := g_business_group_id;
886 end if;
887 if g_business_group_id is null
888 or g_assignment_action_id is null
889 or g_xassignment_action_id < 0
890 or p_assignment_action_id <> g_assignment_action_id then
891 open csr_xassact;
892 fetch csr_xassact into g_business_group_id, g_assignment_action_id, g_xassignment_action_id;
893 close csr_xassact;
894 if l_cache = 'N' and l_business_group_id <> g_business_group_id then
895 l_cache := 'Y';
896 end if;
897 if g_xassignment_action_id > 0 then
898 l_archive := 'Y';
899 else
900 l_archive := 'N';
901 end if;
902 g_legislation_code := legislation_code(p_business_group_id => g_business_group_id);
903 end if;
904 --
905 if g_pre_get_dbitem_value_perf = 'Y' then
906 l_business_group_id := g_business_group_id;
907 if l_cache = 'Y' then
908 g_business_group_id := null;
909 end if;
910 pre_get_dbitem_value(p_business_group_id => l_business_group_id);
911 end if;
912 --
913 if g_xassignment_action_id > 0 and l_archive = 'Y' then
914 l_xuser_entity_id := get_xdbitem_user_entity_id(p_user_entity_id => p_user_entity_id,
915 p_business_group_id => g_business_group_id);
916 l_value := get_archive_items(p_assignment_action_id => g_xassignment_action_id,
917 p_user_entity_id => l_xuser_entity_id);
918 else
919 open csr_context;
920 fetch csr_context into l_payroll_id,
921 l_payroll_action_id,
922 l_assignment_id,
923 l_date_earned,
924 l_tax_unit_id,
925 l_user_name;
926 close csr_context;
927 pay_balance_pkg.set_context('BUSINESS_GROUP_ID', g_business_group_id);
928 pay_balance_pkg.set_context('PAYROLL_ID', l_payroll_id);
929 pay_balance_pkg.set_context('PAYROLL_ACTION_ID', l_payroll_action_id);
930 pay_balance_pkg.set_context('ASSIGNMENT_ID', l_assignment_id);
931 pay_balance_pkg.set_context('ASSIGNMENT_ACTION_ID', g_assignment_action_id);
932 pay_balance_pkg.set_context('DATE_EARNED', fnd_date.date_to_canonical(l_date_earned));
933 pay_balance_pkg.set_context('TAX_UNIT_ID', l_tax_unit_id);
934 l_value := pay_balance_pkg.run_db_item(p_database_name => l_user_name,
935 p_bus_group_id => g_business_group_id,
936 p_legislation_code => g_legislation_code);
937 end if;
938 --
939 return l_value;
940 --
941 end get_dbitem_value;
942 --------------------------------------------------------------------------------
943 function get_dbitem_value(p_assignment_action_id in number,
944 p_user_name in varchar2) return varchar2
945 --------------------------------------------------------------------------------
946 is
947 --
948 l_value ff_archive_items.value%type;
949 l_user_entity_id number;
950 l_business_group_id number;
951 l_cache varchar2(1);
952 --
953 -- Not support for multi action for same action_id
954 --
955 cursor csr_assact
956 is
957 select ppa.business_group_id,
958 paa.assignment_action_id
959 from pay_payroll_actions ppa,
960 pay_assignment_actions paa
961 where paa.assignment_action_id = p_assignment_action_id
962 and ppa.payroll_action_id = paa.payroll_action_id;
963 --
964 begin
965 --
966 l_cache := 'N';
967 --
968 hr_api.mandatory_arg_error('get_dbitem_value', 'assignment_action_id', p_assignment_action_id);
969 hr_api.mandatory_arg_error('get_dbitem_value', 'user_name', p_user_name);
970
971 if g_debug then
972 hr_utility.trace('get_dbitem_value assignment_action_id : ' || p_assignment_action_id);
973 hr_utility.trace('get_dbitem_value user_name : ' || p_user_name);
974 end if;
975 --
976 if g_business_group_id is null then
977 l_cache := 'Y';
978 else
979 l_business_group_id := g_business_group_id;
980 end if;
981 if g_business_group_id is null
982 or g_assignment_action_id is null
983 or g_xassignment_action_id is null
984 or g_assignment_action_id <> p_assignment_action_id then
985 g_xassignment_action_id := null;
986 open csr_assact;
987 fetch csr_assact into g_business_group_id, g_assignment_action_id;
988 close csr_assact;
989 if l_cache = 'N' and l_business_group_id <> g_business_group_id then
990 l_cache := 'Y';
991 end if;
992 g_legislation_code := legislation_code(p_business_group_id => g_business_group_id);
993 end if;
994 --
995 if g_pre_get_dbitem_value_perf = 'Y' then
996 l_business_group_id := g_business_group_id;
997 if l_cache = 'Y' then
998 g_business_group_id := null;
999 end if;
1000 pre_get_dbitem_value(p_business_group_id => l_business_group_id);
1001 end if;
1002 --
1003 l_user_entity_id := get_user_entity_id(p_user_name => p_user_name,
1004 p_business_group_id => g_business_group_id);
1005 --
1006 l_value := get_dbitem_value(p_assignment_action_id => g_assignment_action_id,
1007 p_user_entity_id => l_user_entity_id);
1008 --
1009 return l_value;
1010 --
1011 end get_dbitem_value;
1012 --------------------------------------------------------------------------------
1013 function get_result_value_date(p_assignment_action_id in number,
1014 p_business_group_id in number,
1015 p_element_type_name in varchar2,
1016 p_input_value_name in varchar2) return date
1017 --------------------------------------------------------------------------------
1018 is
1019 --
1020 l_element_type_id number;
1021 l_input_value_id number;
1022 l_value date;
1023 --
1024 cursor csr_input_value
1025 is
1026 select pet.element_type_id,
1027 piv.input_value_id
1028 from pay_input_values_f piv,
1029 pay_element_types_f pet,
1030 pay_payroll_actions ppa,
1031 pay_assignment_actions paa
1032 where paa.assignment_action_id = p_assignment_action_id
1033 and ppa.payroll_action_id = paa.payroll_action_id
1034 and pet.element_name = p_element_type_name
1035 and nvl(pet.business_group_id, g_business_group_id) = g_business_group_id
1036 and nvl(pet.legislation_code, g_legislation_code) = g_legislation_code
1037 and ppa.effective_date
1038 between pet.effective_start_date and pet.effective_end_date
1039 and piv.element_type_id = pet.element_type_id
1040 and piv.name = p_input_value_name
1041 and ppa.effective_date
1042 between piv.effective_start_date and piv.effective_end_date;
1043 --
1044 begin
1045 --
1046 l_value := NULL;
1047 --
1048 if g_business_group_id is null or p_business_group_id <> g_business_group_id then
1049 g_business_group_id := p_business_group_id;
1050 g_legislation_code := legislation_code(p_business_group_id => g_business_group_id);
1051 end if;
1052 --
1053 open csr_input_value;
1054 fetch csr_input_value into l_element_type_id,l_input_value_id;
1055 if csr_input_value%notfound then
1056 raise no_data_found;
1057 end if;
1058 close csr_input_value;
1059 --
1060 l_value := get_result_value_date(p_assignment_action_id => p_assignment_action_id,
1061 p_business_group_id => p_business_group_id,
1062 p_element_type_id => l_element_type_id,
1063 p_input_value_id => l_input_value_id);
1064 --
1065 return l_value;
1066 --
1067 end get_result_value_date;
1068 --------------------------------------------------------------------------------
1069 function get_result_value_date(p_assignment_action_id in number,
1070 p_business_group_id in number,
1071 p_element_type_id in number,
1072 p_input_value_id in number) return date
1073 --------------------------------------------------------------------------------
1074 is
1075 --
1076 l_value date;
1077 --
1078 cursor csr_result_value
1079 is
1080 select
1081 fnd_date.canonical_to_date(prrv.result_value) value
1082 from pay_run_result_values prrv,
1083 pay_run_results prr,
1084 pay_payroll_actions ppa,
1085 pay_assignment_actions paa
1086 where paa.assignment_action_id = p_assignment_action_id
1087 and ppa.payroll_action_id = paa.payroll_action_id
1088 and prr.assignment_action_id = paa.assignment_action_id
1089 and prr.status in ('P','PA')
1090 and prr.element_type_id = p_element_type_id
1091 and prrv.run_result_id = prr.run_result_id
1092 and prrv.input_value_id = p_input_value_id;
1093 --
1094 begin
1095 --
1096 l_value := NULL;
1097 --
1098 if g_business_group_id is null or p_business_group_id <> g_business_group_id then
1099 g_business_group_id := p_business_group_id;
1100 g_legislation_code := legislation_code(p_business_group_id => g_business_group_id);
1101 end if;
1102 --
1103 open csr_result_value;
1104 fetch csr_result_value into l_value;
1105 if csr_result_value%notfound then
1106 l_value := NULL;
1107 end if;
1108 close csr_result_value;
1109 --
1110 return l_value;
1111 --
1112 end get_result_value_date;
1113 --------------------------------------------------------------------------------
1114 function get_result_value_number(p_assignment_action_id in number,
1115 p_business_group_id in number,
1116 p_element_type_name in varchar2,
1117 p_input_value_name in varchar2) return number
1118 --------------------------------------------------------------------------------
1119 is
1120 --
1121 l_element_type_id number;
1122 l_input_value_id number;
1123 l_value number;
1124 --
1125 cursor csr_input_value
1126 is
1127 select pet.element_type_id,
1128 piv.input_value_id
1129 from pay_input_values_f piv,
1130 pay_element_types_f pet,
1131 pay_payroll_actions ppa,
1132 pay_assignment_actions paa
1133 where paa.assignment_action_id = p_assignment_action_id
1134 and ppa.payroll_action_id = paa.payroll_action_id
1135 and pet.element_name = p_element_type_name
1136 and nvl(pet.business_group_id, g_business_group_id) = g_business_group_id
1137 and nvl(pet.legislation_code, g_legislation_code) = g_legislation_code
1138 and ppa.effective_date
1139 between pet.effective_start_date and pet.effective_end_date
1140 and piv.element_type_id = pet.element_type_id
1141 and piv.name = p_input_value_name
1142 and ppa.effective_date
1143 between piv.effective_start_date and piv.effective_end_date;
1144 --
1145 begin
1146 --
1147 l_value := NULL;
1148 --
1149 if g_business_group_id is null or p_business_group_id <> g_business_group_id then
1150 g_business_group_id := p_business_group_id;
1151 g_legislation_code := legislation_code(p_business_group_id => g_business_group_id);
1152 end if;
1153 --
1154 open csr_input_value;
1155 fetch csr_input_value into l_element_type_id,l_input_value_id;
1156 if csr_input_value%notfound then
1157 raise no_data_found;
1158 end if;
1159 close csr_input_value;
1160 --
1161 l_value := get_result_value_number(p_assignment_action_id => p_assignment_action_id,
1162 p_business_group_id => p_business_group_id,
1163 p_element_type_id => l_element_type_id,
1164 p_input_value_id => l_input_value_id);
1165 --
1166 return l_value;
1167 --
1168 end get_result_value_number;
1169 --------------------------------------------------------------------------------
1170 function get_result_value_number(p_assignment_action_id in number,
1171 p_business_group_id in number,
1172 p_element_type_id in number,
1173 p_input_value_id in number) return number
1174 --------------------------------------------------------------------------------
1175 is
1176 --
1177 l_value number;
1178 --
1179 cursor csr_result_value
1180 is
1181 select
1182 fnd_number.canonical_to_number(prrv.result_value) value
1183 from pay_run_result_values prrv,
1184 pay_run_results prr,
1185 pay_payroll_actions ppa,
1186 pay_assignment_actions paa
1187 where paa.assignment_action_id = p_assignment_action_id
1188 and ppa.payroll_action_id = paa.payroll_action_id
1189 and prr.assignment_action_id = paa.assignment_action_id
1190 and prr.status in ('P','PA')
1191 and prr.element_type_id = p_element_type_id
1192 and prrv.run_result_id = prr.run_result_id
1193 and prrv.input_value_id = p_input_value_id;
1194 --
1195 begin
1196 --
1197 l_value := NULL;
1198 --
1199 if g_business_group_id is null or p_business_group_id <> g_business_group_id then
1200 g_business_group_id := p_business_group_id;
1201 g_legislation_code := legislation_code(p_business_group_id => g_business_group_id);
1202 end if;
1203 --
1204 open csr_result_value;
1205 fetch csr_result_value into l_value;
1206 if csr_result_value%notfound then
1207 l_value := NULL;
1208 end if;
1209 close csr_result_value;
1210 --
1211 return l_value;
1212 --
1213 end get_result_value_number;
1214 --------------------------------------------------------------------------------
1215 function get_result_value_char(p_assignment_action_id in number,
1216 p_business_group_id in number,
1217 p_element_type_name in varchar2,
1218 p_input_value_name in varchar2) return varchar2
1219 --------------------------------------------------------------------------------
1220 is
1221 --
1222 l_element_type_id number;
1223 l_input_value_id number;
1224 l_value pay_run_result_values.result_value%type;
1225 --
1226 cursor csr_input_value
1227 is
1228 select pet.element_type_id,
1229 piv.input_value_id
1230 from pay_input_values_f piv,
1231 pay_element_types_f pet,
1232 pay_payroll_actions ppa,
1233 pay_assignment_actions paa
1234 where paa.assignment_action_id = p_assignment_action_id
1235 and ppa.payroll_action_id = paa.payroll_action_id
1236 and pet.element_name = p_element_type_name
1237 and nvl(pet.business_group_id, g_business_group_id) = g_business_group_id
1238 and nvl(pet.legislation_code, g_legislation_code) = g_legislation_code
1239 and ppa.effective_date
1240 between pet.effective_start_date and pet.effective_end_date
1241 and piv.element_type_id = pet.element_type_id
1242 and piv.name = p_input_value_name
1243 and ppa.effective_date
1244 between piv.effective_start_date and piv.effective_end_date;
1245 --
1246 begin
1247 --
1248 l_value := NULL;
1249 --
1250 if g_business_group_id is null or p_business_group_id <> g_business_group_id then
1251 g_business_group_id := p_business_group_id;
1252 g_legislation_code := legislation_code(p_business_group_id => g_business_group_id);
1253 end if;
1254 --
1255 open csr_input_value;
1256 fetch csr_input_value into l_element_type_id,l_input_value_id;
1257 if csr_input_value%notfound then
1258 raise no_data_found;
1259 end if;
1260 close csr_input_value;
1261 --
1262 l_value := get_result_value_char(p_assignment_action_id => p_assignment_action_id,
1263 p_business_group_id => p_business_group_id,
1264 p_element_type_id => l_element_type_id,
1265 p_input_value_id => l_input_value_id);
1266 --
1267 return l_value;
1268 --
1269 end get_result_value_char;
1270 --------------------------------------------------------------------------------
1271 function get_result_value_char(p_assignment_action_id in number,
1272 p_business_group_id in number,
1273 p_element_type_id in number,
1274 p_input_value_id in number) return varchar2
1275 --------------------------------------------------------------------------------
1276 is
1277 --
1278 l_value pay_run_result_values.result_value%type;
1279 --
1280 cursor csr_result_value
1281 is
1282 select
1283 prrv.result_value value
1284 from pay_run_result_values prrv,
1285 pay_run_results prr,
1286 pay_payroll_actions ppa,
1287 pay_assignment_actions paa
1288 where paa.assignment_action_id = p_assignment_action_id
1289 and ppa.payroll_action_id = paa.payroll_action_id
1290 and prr.assignment_action_id = paa.assignment_action_id
1291 and prr.status in ('P','PA')
1292 and prr.element_type_id = p_element_type_id
1293 and prrv.run_result_id = prr.run_result_id
1294 and prrv.input_value_id = p_input_value_id;
1295 --
1296 begin
1297 --
1298 l_value := NULL;
1299 --
1300 if g_business_group_id is null or p_business_group_id <> g_business_group_id then
1301 g_business_group_id := p_business_group_id;
1302 g_legislation_code := legislation_code(p_business_group_id => g_business_group_id);
1303 end if;
1304 --
1305 open csr_result_value;
1306 fetch csr_result_value into l_value;
1307 if csr_result_value%notfound then
1308 l_value := NULL;
1309 end if;
1310 close csr_result_value;
1311 --
1312 return l_value;
1313 --
1314 end get_result_value_char;
1315
1316 --
1317 -- Bug 4442482: Added new function get_result_value
1318
1319 function get_result_value (
1320 p_run_result_id in pay_run_results.run_result_id%type,
1321 p_input_value_id in pay_input_values_f.input_value_id%type
1322 ) return varchar2 is
1323 cursor csr_result_value is
1324 select result_value
1325 from pay_run_result_values
1326 where run_result_id = p_run_result_id
1327 and input_value_id = p_input_value_id ;
1328 --
1329 l_result pay_run_result_values.result_value%type ;
1330 --
1331 begin
1332 --
1333 open csr_result_value ;
1334 fetch csr_result_value into l_result ;
1335 if csr_result_value%notfound then
1336 l_result := null ;
1337 end if ;
1338 close csr_result_value ;
1339 --
1340 return l_result ;
1341 end get_result_value ;
1342 -- End of 4442482
1343 --
1344 end pay_kr_report_pkg;