[Home] [Help]
PACKAGE BODY: APPS.PAY_JP_RETRO_PKG
Source
1 package body pay_jp_retro_pkg as
2 /* $Header: pyjpretr.pkb 120.3 2005/11/16 23:47:48 keyazawa noship $ */
3 --------------------------------------------------------------------------------
4 g_assignment_id number;
5 g_effective_date date;
6 g_assignment_action_id number;
7 --
8 g_sal3_diff number;
9 g_sal2_diff number;
10 g_sal1_diff number;
11 g_sal3_paid number;
12 g_sal2_paid number;
13 g_sal1_paid number;
14 g_material3_diff number;
15 g_material2_diff number;
16 g_material1_diff number;
17 g_material3_paid number;
18 g_material2_paid number;
19 g_material1_paid number;
20 -- In current version of "Retro pay by action" only supports UOM="Money".
21 -- That is, it's impossible to backpay UOM="Number".
22 -- g_payment_days3_diff number;
23 -- g_payment_days2_diff number;
24 -- g_payment_days1_diff number;
25 -- g_payment_days3_paid number;
26 -- g_payment_days2_paid number;
27 -- g_payment_days1_paid number;
28 -- No need to backpay fixed salary.
29 -- g_fixed_sal3_diff number;
30 -- g_fixed_sal2_diff number;
31 -- g_fixed_sal1_diff number;
32 -- g_fixed_sal3_paid number;
33 -- g_fixed_sal2_paid number;
34 -- g_fixed_sal1_paid number;
35 g_retro_diff_value number;
36 --
37 -- Modified length for UTF8.
38 -- g_sal varchar2(30);
39 -- g_material varchar2(30);
40 g_sal varchar2(40);
41 g_material varchar2(40);
42 -- g_payment_days varchar2(30);
43 -- g_fixed_sal varchar2(30);
44
45 --
46 g_id_fetched BOOLEAN := FALSE;
47 --
48 g_retro_paid3 number;
49 g_retro_paid2 number;
50 g_retro_paid1 number;
51
52 g_sal_id number := hr_jp_id_pkg.balance_type_id('B_COM_SAN_GEP_SAL_ERN_MONEY', null, 'JP');
53 g_material_id number := hr_jp_id_pkg.balance_type_id('B_COM_SAN_GEP_SAL_ERN_KIND', null, 'JP');
54 -- g_payment_days_id number := hr_jp_id_pkg.balance_type_id('B_COM_SAN_GEP_PAY_BASE_DAYS', null, 'JP');
55 -- g_fixed_sal_id number := hr_jp_id_pkg.balance_type_id('B_GEP_FIXED_WAGE', null, 'JP');
56
57 -------------------------------------------------------------
58 FUNCTION get_retropayments(
59 -------------------------------------------------------------
60 p_assignment_id number,
61 p_date_earned date)
62 RETURN number
63 IS
64 l_apply_mth_3 varchar2(7);
65 l_apply_mth_2 varchar2(7);
66 l_apply_mth_1 varchar2(7);
67 p_apply_mth varchar2(7);
68 l_start_gtr date;
69 l_end_gtr date;
70 l_return_flg number;
74 l_min_effective_date date;
71 -- l_min_sal_action_seq number;
72 -- l_min_material_action_seq number;
73 -- l_min_action_seq number;
75
76 --
77 --cursor csr_sg_sal_si is
78 -- select balance_type_id
79 -- from pay_balance_types
80 -- where balance_name = g_sal;
81 --cursor csr_sg_sal_material_si is
82 -- select balance_type_id
83 -- from pay_balance_types
84 -- where balance_name = g_material;
85 -- --cursor csr_sg_payment_days is
86 -- -- select balance_type_id
87 -- -- from pay_balance_types
88 -- -- where balance_name = g_payment_days;
89 -- --cursor csr_sg_fixed_sal is
90 -- -- select balance_type_id
91 -- -- from pay_balance_types
92 -- -- where balance_name = g_fixed_sal;
93 BEGIN
94 p_apply_mth := to_char(p_date_earned, 'YYYY-MM');
95 l_apply_mth_3 := to_char(add_months(p_date_earned, -3), 'YYYY-MM');
96 l_apply_mth_2 := to_char(add_months(p_date_earned, -2), 'YYYY-MM');
97 l_apply_mth_1 := to_char(add_months(p_date_earned, -1), 'YYYY-MM');
98 l_start_gtr := add_months(p_date_earned, -3);
99 l_end_gtr := last_day(add_months(p_date_earned, -1));
100
101 --if not g_id_fetched then
102 --open csr_sg_sal_si;
103 --fetch csr_sg_sal_si into g_sal_id;
104 --close csr_sg_sal_si;
105
106 --open csr_sg_sal_material_si;
107 --fetch csr_sg_sal_material_si into g_material_id;
108 --close csr_sg_sal_material_si;
109
110 -- --open csr_sg_payment_days;
111 -- --fetch csr_sg_payment_days into g_payment_days_id;
112 -- --close csr_sg_payment_days;
113
114 -- --open csr_sg_fixed_sal;
115 -- --fetch csr_sg_fixed_sal into g_fixed_sal_id;
116 -- --close csr_sg_fixed_sal;
117
118 -- g_id_fetched := TRUE;
119 --end if;
120
121 /*
122 dbms_output.put_line('g_sal_id : '||g_sal_id);
123 dbms_output.put_line('g_material_id : '||g_material_id);
124 dbms_output.put_line('g_payment_days_id : '||g_payment_days_id);
125 dbms_output.put_line('g_fixed_sal_id : '||g_fixed_sal_id);
126 */
127 --
128 --
129 -- calculate the adjustments to the balance this is made up of 2 figures
130 -- 1. what retropayments have been made by other runs that should
131 -- should have been paid on this run
132 -- 2 minus retropayments paid in this run that should have been
133 -- paid on another run - note these are not just excluded they are
134 -- extra negative adjustments that need to be applied to the
135 -- unadjusted balance to arrive at an adjusted balance
136 -- we achieve this in a single select for performance reasons. The
137 -- decode implements the complex rules saying
138 -- is it for this balance
139 -- is it for this month
140 -- is it plus or minus
141 -- BACT payroll action where the money should have been earned
142 -- PACT Payroll action where the money was earned
143 -- PEE element entry for the retropay element
144 -- One rule that is true for Geppen/Santei and not true for
145 -- ASG_RETRO_RUN is that we don't include retropayments paid
146 -- later that should have been paid in this period. These
147 -- are excluded in the where clause(i.e. no retro-Geppen)
148
149
150 SELECT /*+ ORDERED
151 INDEX(BACT_ASSACT PAY_ASSIGNMENT_ACTIONS_N1)
152 INDEX(PEPD PAY_ENTRY_PROCESS_DETAILS_FK2)
153 INDEX(BACT PAY_PAYROLL_ACTIONS_PK)
154 INDEX(RR PAY_RUN_RESULTS_N51)
155 INDEX(ASSACT PAY_ASSIGNMENT_ACTIONS_PK)
156 INDEX(PACT PAY_PAYROLL_ACTIONS_PK)
157 INDEX(PEE PAY_ELEMENT_ENTRIES_F_PK)
158 INDEX(TARGET PAY_RUN_RESULT_VALUES_N50)
159 INDEX(FEED PAY_BALANCE_FEEDS_F_N2) */
160 -- retro salary diff(adjusted amount to pay actually)
161 -- = retro salary to pay at target month(BACT) - retro salary paid at target month(PACT)
162 nvl(sum(decode(FEED.balance_type_id,
163 g_sal_id,fnd_number.canonical_to_number(TARGET.result_value)
164 * FEED.scale
165 * (decode(to_char(BACT.effective_date,'YYYY-MM'),l_apply_mth_3,1,0)
166 + decode(to_char(PACT.effective_date,'YYYY-MM'),l_apply_mth_3,-1,0)))),0),
167 nvl(sum(decode(FEED.balance_type_id,
168 g_sal_id,fnd_number.canonical_to_number(TARGET.result_value)
169 * FEED.scale
170 * (decode(to_char(BACT.effective_date,'YYYY-MM'),l_apply_mth_2,1,0)
171 + decode(to_char(PACT.effective_date,'YYYY-MM'),l_apply_mth_2,-1,0)))),0),
172 nvl(sum(decode(FEED.balance_type_id,
173 g_sal_id,fnd_number.canonical_to_number(TARGET.result_value)
174 * FEED.scale
175 * (decode(to_char(BACT.effective_date,'YYYY-MM'),l_apply_mth_1,1,0)
176 + decode(to_char(PACT.effective_date,'YYYY-MM'),l_apply_mth_1,-1,0)))),0),
177 -- retro salary paid(actual paid retro salary at the month)(PACT)
178 nvl(sum(decode(FEED.balance_type_id,
179 g_sal_id,fnd_number.canonical_to_number(TARGET.result_value)
180 * FEED.scale
181 * decode(to_char(PACT.effective_date,'YYYY-MM'),l_apply_mth_3,1))),0),
182 nvl(sum(decode(FEED.balance_type_id,
183 g_sal_id,fnd_number.canonical_to_number(TARGET.result_value)
184 * FEED.scale
185 * decode(to_char(PACT.effective_date,'YYYY-MM'),l_apply_mth_2,1))),0),
186 nvl(sum(decode(FEED.balance_type_id,
187 g_sal_id,fnd_number.canonical_to_number(TARGET.result_value)
188 * FEED.scale
192 nvl(sum(decode(FEED.balance_type_id,
189 * decode(to_char(PACT.effective_date,'YYYY-MM'),l_apply_mth_1,1))),0),
190 -- retro salary materials diff
191 -- = retro salary to pay at target month(BACT) - retro salary paid at target month(PACT)
193 g_material_id,fnd_number.canonical_to_number(TARGET.result_value)
194 * FEED.scale
195 * (decode(to_char(BACT.effective_date,'YYYY-MM'),l_apply_mth_3,1,0)
196 + decode(to_char(PACT.effective_date,'YYYY-MM'),l_apply_mth_3,-1,0)))),0),
197 nvl(sum(decode(FEED.balance_type_id,
198 g_material_id,fnd_number.canonical_to_number(TARGET.result_value)
199 * FEED.scale
200 * (decode(to_char(BACT.effective_date,'YYYY-MM'),l_apply_mth_2,1,0)
201 + decode(to_char(PACT.effective_date,'YYYY-MM'),l_apply_mth_2,-1,0)))),0),
202 nvl(sum(decode(FEED.balance_type_id,
203 g_material_id,fnd_number.canonical_to_number(TARGET.result_value)
204 * FEED.scale
205 * (decode(to_char(BACT.effective_date,'YYYY-MM'),l_apply_mth_1,1,0)
206 + decode(to_char(PACT.effective_date,'YYYY-MM'),l_apply_mth_1,-1,0)))),0),
207 -- retro salary materials paid(actual paid retro salary at the month)(PACT)
208 nvl(sum(decode(FEED.balance_type_id,
209 g_material_id,fnd_number.canonical_to_number(TARGET.result_value)
210 * FEED.scale
211 * decode(to_char(PACT.effective_date,'YYYY-MM'),l_apply_mth_3,1))),0),
212 nvl(sum(decode(FEED.balance_type_id,
213 g_material_id,fnd_number.canonical_to_number(TARGET.result_value)
214 * FEED.scale
215 * decode(to_char(PACT.effective_date,'YYYY-MM'),l_apply_mth_2,1))),0),
216 nvl(sum(decode(FEED.balance_type_id,
217 g_material_id,fnd_number.canonical_to_number(TARGET.result_value)
218 * FEED.scale
219 * decode(to_char(PACT.effective_date,'YYYY-MM'),l_apply_mth_1,1))),0),
220 -- Minimum Action Sequence of Retro assact(not Target assact)
221 min(decode(balance_type_id, g_sal_id, BACT.effective_date, g_material_id, BACT.effective_date)),
222 -- exists check for retro salary paid (paid retro salary at the month)(PACT)
223 nvl(sum(decode(to_char(PACT.effective_date,'YYYY-MM'),l_apply_mth_3,1)),0),
224 nvl(sum(decode(to_char(PACT.effective_date,'YYYY-MM'),l_apply_mth_2,1)),0),
225 nvl(sum(decode(to_char(PACT.effective_date,'YYYY-MM'),l_apply_mth_1,1)),0)
226 INTO g_sal3_diff,
227 g_sal2_diff,
228 g_sal1_diff,
229 g_sal3_paid,
230 g_sal2_paid,
231 g_sal1_paid,
232 g_material3_diff,
233 g_material2_diff,
234 g_material1_diff,
235 g_material3_paid,
236 g_material2_paid,
237 g_material1_paid,
238 l_min_effective_date,
239 g_retro_paid3,
240 g_retro_paid2,
241 g_retro_paid1
242 -- 1) pay_element_entries_f.source_id is no index so that all query cause bad performance. reject.
243 -- target assact(use assignment_id)
244 -- target pact(use effective_date)
245 -- target result(use status)
246 -- retro entries(use creator_type,entry_type)
247 -- retro assact
248 -- retro pact
249 -- target result value
250 -- target feed(use balance_type_id)
251 -- 2) probably this way will perform better.
252 -- retro entries(use assignment_id,creator_type,entry_type)
253 -- target result(use status)
254 -- target assact
255 -- target pact(use effective_date)
256 -- retro assact
257 -- retro pact
258 -- target result value
259 -- target feed(use balance_type_id)
260 FROM pay_assignment_actions BACT_ASSACT, -- retro assact
261 pay_entry_process_details PEPD, -- retro entries
262 pay_payroll_actions BACT, -- retro pact
263 pay_run_results RR, -- target result
264 pay_assignment_actions ASSACT, -- target assact
265 pay_payroll_actions PACT, -- target pact
266 pay_element_entries_f PEE, -- retro element entries
267 pay_run_result_values TARGET, -- target result value
268 pay_balance_feeds_f FEED
269 WHERE BACT_ASSACT.assignment_id = p_assignment_id
270 and PEPD.source_asg_action_id = BACT_ASSACT.assignment_action_id
271 and BACT.payroll_action_id = BACT_ASSACT.payroll_action_id
272 and RR.source_id = PEPD.element_entry_id
273 and RR.status in ('P','PA')
274 and ASSACT.assignment_action_id = RR.assignment_action_id
275 and PACT.payroll_action_id = ASSACT.payroll_action_id
276 and PACT.effective_date
277 between l_start_gtr and l_end_gtr
278 and PEE.element_entry_id = PEPD.element_entry_id
279 and PACT.date_earned
280 between PEE.effective_start_date and PEE.effective_end_date
281 and TARGET.run_result_id = RR.run_result_id
282 and nvl(TARGET.result_value,'0') <> '0'
283 and FEED.input_value_id = TARGET.input_value_id
284 and FEED.balance_type_id in (g_sal_id, g_material_id)
285 and PACT.effective_date
286 between FEED.effective_start_date and FEED.effective_end_date;
287
288 l_return_flg := 0;
289
290 if l_min_effective_date is not NULL then
291
292 SELECT /*+ ORDERED
293 INDEX(BACT_ASSACT PAY_ASSIGNMENT_ACTIONS_N1)
294 INDEX(PEPD PAY_ENTRY_PROCESS_DETAILS_FK2)
295 INDEX(BACT PAY_PAYROLL_ACTIONS_PK)
296 INDEX(RR PAY_RUN_RESULTS_N51)
300 INDEX(TARGET PAY_RUN_RESULT_VALUES_N50)
297 INDEX(ASSACT PAY_ASSIGNMENT_ACTIONS_PK)
298 INDEX(PACT PAY_PAYROLL_ACTIONS_PK)
299 INDEX(PEE PAY_ELEMENT_ENTRIES_F_PK)
301 INDEX(FEED PAY_BALANCE_FEEDS_F_N2) */
302 nvl(sum(fnd_number.canonical_to_number(target.result_value)),0)
303 INTO g_retro_diff_value
304 FROM pay_assignment_actions BACT_ASSACT,
305 pay_entry_process_details PEPD,
306 pay_payroll_actions BACT,
307 pay_run_results RR,
308 pay_assignment_actions ASSACT,
309 pay_payroll_actions PACT,
310 pay_element_entries_f PEE,
311 pay_run_result_values TARGET,
312 pay_balance_feeds_f FEED
313 WHERE BACT_ASSACT.assignment_id = p_assignment_id
314 and PEPD.source_asg_action_id = BACT_ASSACT.assignment_action_id
315 and BACT.payroll_action_id = BACT_ASSACT.payroll_action_id
316 and to_char(BACT.effective_date,'YYYYMM')=to_char(l_min_effective_date,'YYYYMM')
317 and RR.source_id = PEPD.element_entry_id
318 and RR.status in ('P','PA')
319 and ASSACT.assignment_action_id = RR.assignment_action_id
320 and PACT.payroll_action_id = ASSACT.payroll_action_id
321 and PACT.effective_date
322 between l_start_gtr and l_end_gtr
323 and PEE.element_entry_id = PEPD.element_entry_id
324 and PACT.date_earned
325 between PEE.effective_start_date and PEE.effective_end_date
326 and TARGET.run_result_id = RR.run_result_id
327 and nvl(TARGET.result_value,'0') <> '0'
328 and FEED.input_value_id = TARGET.input_value_id
329 and FEED.balance_type_id in (g_sal_id, g_material_id)
330 and PACT.effective_date
331 between FEED.effective_start_date and FEED.effective_end_date;
332
333 else
334 g_retro_diff_value := 0;
335 end if;
336
337 return l_return_flg;
338 END;
339
340
341 --
342 -- GET_PLSQL_GLOBAL
343 -- retrieve a PLSQL global from the session
344 --
345 -------------------------------------------------------------
346 FUNCTION GET_PLSQL_GLOBAL(
347 -------------------------------------------------------------
348 p_global_name in varchar2,
349 p_mth_ago in number,
350 p_type in varchar2 )
351 -- p_type('DIFF', 'PAID', 'SUPPOSED')
352 RETURN number is
353 l_value number;
354
355 cursor csr_sg_sal is
356 select balance_name
357 from pay_balance_types
358 where balance_type_id = g_sal_id;
359 cursor csr_sg_sal_material is
360 select balance_name
361 from pay_balance_types
362 where balance_type_id = g_material_id;
363 -- --cursor csr_sg_payment_days is
364 -- -- select balance_name
365 -- -- from pay_balance_types
366 -- -- where balance_type_id = g_payment_days_id;
367 -- --cursor csr_sg_fixed_sal is
368 -- -- select balance_name
369 -- -- from pay_balance_types
370 -- -- where balance_type_id = g_fixed_sal_id;
371 BEGIN
372 l_value := 0;
373
374 if not g_id_fetched then
375 open csr_sg_sal;
376 fetch csr_sg_sal into g_sal;
377 close csr_sg_sal;
378
379 open csr_sg_sal_material;
380 fetch csr_sg_sal_material into g_material;
381 close csr_sg_sal_material;
382
383 -- open csr_sg_payment_days;
384 -- fetch csr_sg_payment_days into g_payment_days;
385 -- close csr_sg_payment_days;
386
387 -- open csr_sg_fixed_sal;
388 -- fetch csr_sg_fixed_sal into g_fixed_sal;
389 -- close csr_sg_fixed_sal;
390
391 g_id_fetched := TRUE;
392 end if;
393
394 if p_global_name = g_sal then
395 if p_type = 'DIFF' then
396 if p_mth_ago = 1 then
397 l_value := g_sal1_diff;
398 elsif p_mth_ago = 2 then
399 l_value := g_sal2_diff;
400 elsif p_mth_ago = 3 then
401 l_value := g_sal3_diff;
402 end if;
403 elsif p_type = 'PAID' then
404 if p_mth_ago = 1 then
405 l_value := g_sal1_paid;
406 elsif p_mth_ago = 2 then
407 l_value := g_sal2_paid;
408 elsif p_mth_ago = 3 then
409 l_value := g_sal3_paid;
410 end if;
411 end if;
412 elsif p_global_name = g_material then
413 if p_type = 'DIFF' then
414 if p_mth_ago = 1 then
415 l_value := g_material1_diff;
416 elsif p_mth_ago = 2 then
417 l_value := g_material2_diff;
418 elsif p_mth_ago = 3 then
419 l_value := g_material3_diff;
420 end if;
421 elsif p_type = 'PAID' then
422 if p_mth_ago = 1 then
423 l_value := g_material1_paid;
424 elsif p_mth_ago = 2 then
425 l_value := g_material2_paid;
426 elsif p_mth_ago = 3 then
427 l_value := g_material3_paid;
428 end if;
429 end if;
430 -- elsif p_global_name = g_payment_days then
431 -- if p_type = 'DIFF' then
432 -- if p_mth_ago = 1 then
433 -- l_value := g_payment_days1_diff;
434 -- elsif p_mth_ago = 2 then
435 -- l_value := g_payment_days2_diff;
436 -- elsif p_mth_ago = 3 then
437 -- l_value := g_payment_days3_diff;
438 -- end if;
439 -- elsif p_type = 'PAID' then
443 -- l_value := g_payment_days2_paid;
440 -- if p_mth_ago = 1 then
441 -- l_value := g_payment_days1_paid;
442 -- elsif p_mth_ago = 2 then
444 -- elsif p_mth_ago = 3 then
445 -- l_value := g_payment_days3_paid;
446 -- end if;
447 -- end if;
448 -- elsif p_global_name = g_fixed_sal then
449 -- if p_type = 'DIFF' then
450 -- if p_mth_ago = 1 then
451 -- l_value := g_fixed_sal1_diff;
452 -- elsif p_mth_ago = 2 then
453 -- l_value := g_fixed_sal2_diff;
454 -- elsif p_mth_ago = 3 then
455 -- l_value := g_fixed_sal3_diff;
456 -- end if;
457 -- elsif p_type = 'PAID' then
458 -- if p_mth_ago = 1 then
459 -- l_value := g_fixed_sal1_paid;
460 -- elsif p_mth_ago = 2 then
461 -- l_value := g_fixed_sal2_paid;
462 -- elsif p_mth_ago = 3 then
463 -- l_value := g_fixed_sal3_paid;
464 -- end if;
465 -- end if;
466 end if;
467
468 RETURN l_value ;
469 END GET_PLSQL_GLOBAL;
470
471
472 -------------------------------------------------------------
473 FUNCTION get_retro_mth(p_mth_ago in number)
474 -------------------------------------------------------------
475 RETURN number is
476 l_value number;
477 BEGIN
478 l_value := 0;
479
480 if p_mth_ago = 1 then
481 l_value := g_sal1_paid + g_material1_paid;
482 elsif p_mth_ago = 2 then
483 l_value := g_sal2_paid + g_material2_paid;
484 elsif p_mth_ago = 3 then
485 l_value := g_sal3_paid + g_material3_paid;
486 end if;
487
488 RETURN l_value ;
489 END get_retro_mth;
490
491
492 -------------------------------------------------------------
493 FUNCTION get_first_retro_amt
494 -------------------------------------------------------------
495 RETURN number is
496 BEGIN
497 RETURN g_retro_diff_value;
498 END get_first_retro_amt;
499
500
501 -------------------------------------------------------------
502 FUNCTION get_first_retro_mth
503 -------------------------------------------------------------
504 RETURN number is
505 l_value number;
506 BEGIN
507 if g_retro_paid3 = 0 then
508 if g_retro_paid2 = 0 then
509 if g_retro_paid1 = 0 then
510 l_value := 0;
511 else
512 l_value := 1;
513 end if;
514 else
515 l_value := 2;
516 end if;
517 else
518 l_value := 3;
519 end if;
520
521 RETURN l_value;
522 END get_first_retro_mth;
523
524 /************************************************************
525 Following code is added by T.Tagawa
526 ************************************************************/
527 -------------------------------------------------------------
528 FUNCTION get_legislation_code(
529 -------------------------------------------------------------
530 p_business_group_id IN NUMBER)
531 RETURN VARCHAR2
532 IS
533 l_legislation_code PER_BUSINESS_GROUPS.legislation_code%TYPE;
534 CURSOR csr_legislation_code IS
535 select org_information9
536 from hr_organization_information
537 where organization_id=p_business_group_id
538 and org_information_context='Business Group Information';
539 BEGIN
540 open csr_legislation_code;
541 fetch csr_legislation_code into l_legislation_code;
542 if csr_legislation_code%NOTFOUND then
543 l_legislation_code := NULL;
544 end if;
545 close csr_legislation_code;
546
547 return l_legislation_code;
548 END get_legislation_code;
549
550 -------------------------------------------------------------
551 FUNCTION get_defined_balance_id(
552 -------------------------------------------------------------
553 p_business_group_id IN NUMBER,
554 p_item_name IN VARCHAR2)
555 RETURN NUMBER
556 IS
557 l_defined_balance_id NUMBER;
558 l_legislation_code PER_BUSINESS_GROUPS.legislation_code%TYPE;
559 cursor csr_defined_balance_id is
560 select UE.creator_id
561 from ff_user_entities UE,
562 ff_database_items DI
563 where DI.user_name = p_item_name
564 and UE.user_entity_id = DI.user_entity_id
565 and UE.creator_type = 'B'
566 and nvl(UE.business_group_id,p_business_group_id) = p_business_group_id
567 and nvl(UE.legislation_code,l_legislation_code) = l_legislation_code;
568 BEGIN
569 l_legislation_code := get_legislation_code(p_business_group_id);
570 open csr_defined_balance_id;
571 fetch csr_defined_balance_id into l_defined_balance_id;
572 if csr_defined_balance_id%NOTFOUND then
573 l_defined_balance_id := NULL;
574 end if;
575 close csr_defined_balance_id;
576
577 return l_defined_balance_id;
578 END get_defined_balance_id;
579
580 -------------------------------------------------------------
581 FUNCTION get_last_assact(
582 -------------------------------------------------------------
583 p_assignment_id IN NUMBER,
584 p_effective_date_from IN DATE,
585 p_effective_date_to IN DATE)
586 RETURN NUMBER
587 IS
588 l_last_assact NUMBER;
589 CURSOR csr_last_assact IS
590 select paa.assignment_action_id
591 from pay_action_classifications pac,
592 pay_payroll_actions ppa,
593 pay_assignment_actions paa
594 where paa.assignment_id=p_assignment_id
595 and ppa.payroll_action_id=paa.payroll_action_id
596 and ppa.effective_date
597 between p_effective_date_from and p_effective_date_to
598 and pac.action_type= ppa.action_type
599 and pac.classification_name='SEQUENCED'
600 order by paa.action_sequence desc;
601 BEGIN
602 open csr_last_assact;
603 fetch csr_last_assact into l_last_assact;
604 if csr_last_assact%NOTFOUND then
605 l_last_assact := -1;
606 end if;
607 close csr_last_assact;
608
609 return l_last_assact;
610 END get_last_assact;
611
612 /**************************************************************************
613 Contents : BALANCE_FETCH - get a balance at a specified date
614
615 Date Name Vers Bug No Description
616 -----------+---------------+--------+--------+-----------------------+
617 21-Jan-1997 ASnell 1 created
618 -----------+---------------+--------+--------+-----------------------+
619 --
620 --------------------------------------------------------------------------------
621 --
622 BALANCE FETCH --
623 cover for PLS balance user exit so it can be called from formula to
624 evaluate balances at a date other than the effective date of the payroll run
625 parameters: assignment_id formula context
626 item_name database item name for defined balance
627 effective_date date to evaluate balance for
628 -- --
629 ****************************************************************************/
630 -------------------------------------------------------------
631 FUNCTION balance_fetch (
632 -------------------------------------------------------------
633 p_assignment_id in number,
634 p_item_name in varchar2,
635 p_effective_date in date )
636 return number is
637 cursor csr_defined_balance is
638 select UE.creator_id
639 from ff_user_entities UE,
640 ff_database_items DI
641 where DI.user_name = p_item_name
642 and UE.user_entity_id = DI.user_entity_id
643 and UE.creator_type = 'B';
644
645 l_defined_balance_id pay_defined_balances.defined_balance_id%type;
646 g_message_text varchar2(240); -- make global
647 l_balance number;
648 l_effective_date date;
649 begin
650 open csr_defined_balance;
651 fetch csr_defined_balance into l_defined_balance_id;
652 if csr_defined_balance%notfound then
653 close csr_defined_balance;
654 g_message_text := 'Balance DB item does not exist';
655 raise hr_utility.hr_error;
656 end if;
657 close csr_defined_balance;
658
659 if p_effective_date is NULL then
660 select effective_date
661 into l_effective_date
662 from fnd_sessions
663 where session_id = userenv('sessionid');
664 else
665 l_effective_date := p_effective_date;
666 end if;
667
668 BEGIN
669 l_balance := pay_balance_pkg.get_value(
670 l_defined_balance_id,
671 p_assignment_id,
672 l_effective_date);
673 EXCEPTION
674 when NO_DATA_FOUND then
675 l_balance := 0;
676 END;
677
678 return l_balance;
679 end balance_fetch;
680
681 -------------------------------------------------------------
682 FUNCTION get_balance_value(
683 -------------------------------------------------------------
684 p_business_group_id IN NUMBER,
685 p_item_name IN VARCHAR2,
686 p_assignment_action_id IN NUMBER)
687 RETURN NUMBER
688 IS
689 l_defined_balance_id NUMBER;
690 l_balance_value NUMBER;
691 BEGIN
692 l_balance_value := 0;
693
694 if p_assignment_action_id > 0 then
695 l_defined_balance_id := get_defined_balance_id(p_business_group_id,p_item_name);
696
697 if l_defined_balance_id is not NULL then
698 BEGIN
699 l_balance_value := pay_balance_pkg.get_value(
700 P_DEFINED_BALANCE_ID => l_defined_balance_id,
701 P_ASSIGNMENT_ACTION_ID => p_assignment_action_id);
702 EXCEPTION
703 when NO_DATA_FOUND then
704 l_balance_value := 0;
705 END;
706 end if;
707 end if;
708
709 return l_balance_value;
710 END get_balance_value;
711
712 END pay_jp_retro_pkg;