1 PACKAGE BODY hr_dirbal AS
2 /* $Header: pydirbal.pkb 120.1.12000000.3 2007/07/05 12:34:49 sbairagi noship $ */
3 -------------------------------------------------------------------------------
4 -------------------------------------------------------------------------------
5 -- FUNCTION get_balance
6 -- Date Mode
7 -------------------------------------------------------------------------------
8 /* We can check whether the balance has
9 expired before getting it, due to the fact that we navigate back to the
10 last assignment action and use the effective (session) date passed in
11 as criteria for expiry checking.
12 Calls the asg action mode function, which now calls core BUE.
13 */
14 FUNCTION get_balance (p_assignment_id IN NUMBER,
15 p_defined_balance_id IN NUMBER,
16 p_effective_date IN DATE)
17 RETURN NUMBER IS
18 --
19 l_assignment_action_id NUMBER;
20 l_balance NUMBER;
21 l_expired BOOLEAN;
22 --
23 -- This gets the most recent assignment action of seq generating type, using
24 -- the effective date and assignment ID passed in
25 --
26 cursor get_latest_id is
27 SELECT
28 fnd_number.canonical_to_number(substr(max(lpad(paa.action_sequence,15,'0')||
29 paa.assignment_action_id),16))
30 FROM pay_assignment_actions paa,
31 pay_payroll_actions ppa
32 WHERE
33 paa.assignment_id = p_assignment_id
34 AND ppa.payroll_action_id = paa.payroll_action_id
35 AND ppa.effective_date <= p_effective_date
36 AND ppa.action_type in ('R', 'Q', 'I', 'V', 'B');
37 --
38 BEGIN
39 --
40 open get_latest_id;
41 fetch get_latest_id into l_assignment_action_id;
42 close get_latest_id;
43 if l_assignment_action_id is null then
44 l_balance := 0;
45 else
46 --Check expiry even before getting the VALUE, according to effective date
47 l_expired :=
48 balance_expired(p_assignment_action_id => l_assignment_action_id,
49 p_defined_balance_id => p_defined_balance_id,
50 p_effective_date => p_effective_date);
51 --
52 if l_expired = TRUE then
53 l_balance := 0;
54 else
55 --get the balance value using the latest assignment action
56 l_balance := get_balance(
57 p_assignment_action_id => l_assignment_action_id,
58 p_defined_balance_id => p_defined_balance_id);
59 end if;
60 end if;
61 --
62 RETURN l_balance;
63 --
64 END get_balance;
65 -------------------------------------------------------------------------------
66 -- FUNCTION get_balance
67 -- Assignment Action Mode
68 -- Now calls the Core BUE directly, this function now just a cover for this.
69 -------------------------------------------------------------------------------
70 FUNCTION get_balance (p_assignment_action_id IN NUMBER,
71 p_defined_balance_id IN NUMBER)
72 RETURN NUMBER IS
73 --
74 l_balance NUMBER;
75 --
76 BEGIN
77 --
78 -- Call the Core package with the relevant info, and ensure that the
79 -- exception no data found is handled by returning a null, as this is
80 -- the current UK method of implementation.
81 -- All balances can be retrieved by this method including USER-REGs.
82 --
83 BEGIN
84 l_balance := pay_balance_pkg.get_value(
85 p_assignment_action_id => p_assignment_action_id,
86 p_defined_balance_id => p_defined_balance_id);
87 EXCEPTION WHEN NO_DATA_FOUND THEN
88 l_balance := null;
89 END;
90 --
91 RETURN l_balance;
92 --
93 END get_balance;
94 -------------------------------------------------------------------------------
95 -- FUNCTION get_balance
96 -- This function is used only in PAYGBTPL report.
97 -------------------------------------------------------------------------------
98 FUNCTION get_balance (p_assignment_action_id IN NUMBER,
99 p_defined_balance_id IN NUMBER,
100 p_dimension_id IN NUMBER,
101 p_period_id IN NUMBER,
102 p_ptd_bal_dim_id IN NUMBER)
103 RETURN NUMBER IS
104 --
105 l_action_effective date;
106 l_period_start date;
107 l_period_end date;
108 l_balance number;
109
110 l_time_period_id number;
111 --
112 BEGIN
113 l_balance := 0;
114
115 select ppa.time_period_id
116 into l_time_period_id
117 from pay_payroll_actions ppa,
118 pay_assignment_actions paa
119 where paa.assignment_action_id = p_assignment_action_id
120 and ppa.payroll_action_id = paa.payroll_action_id;
121
122 /* select ptp.start_date,
123 ptp.regular_payment_date
124 into l_period_start,
125 l_period_end
126 from per_time_periods ptp
127 where ptp.time_period_id = p_period_id;*/
128
129 if p_dimension_id = p_ptd_bal_dim_id then
130 if (l_time_period_id=p_period_id) then
131 l_balance := hr_dirbal.get_balance(p_assignment_action_id,p_defined_balance_id);
132 end if;
133 else
134 l_balance := hr_dirbal.get_balance(p_assignment_action_id,p_defined_balance_id);
135 end if;
136 --
137 RETURN l_balance;
138 --
139 END get_balance;
140 -------------------------------------------------------------------------------
141 -- FUNCTION balance_expired
142 -- This function checks the expiry of an action's value ,
143 -- depending on which dimension type the value is for
144 -- This function is still required after addition of Core BUE changes.
145 -------------------------------------------------------------------------------
146 FUNCTION balance_expired (p_assignment_action_id IN NUMBER,
147 p_owning_action_id IN NUMBER DEFAULT NULL,
148 p_defined_balance_id IN NUMBER,
149 p_database_item_suffix IN VARCHAR2 DEFAULT NULL,
150 p_effective_date IN DATE,
151 p_action_effective_date IN DATE DEFAULT NULL)
152 --
153 RETURN BOOLEAN IS
154 --
155 --Check the expiry of an action depending on the defined balance's dimension
156 --type.
157 --
158 l_database_item_suffix VARCHAR2(30);
159 l_expired BOOLEAN := FALSE;
160 l_return_date DATE;
161 --
162 cursor get_dimension_type(c_defined_balance_id IN NUMBER) IS
163 select database_item_suffix
164 from pay_balance_dimensions pbd,
165 pay_defined_balances pdb
166 where pbd.balance_dimension_id = pdb.balance_dimension_id
167 and pdb.defined_balance_id = c_defined_balance_id;
168 --
169 BEGIN
170 --
171 if p_database_item_suffix is null then --date mode call
172 open get_dimension_type(p_defined_balance_id);
173 fetch get_dimension_type into l_database_item_suffix;
174 close get_dimension_type;
175 --
176 else
177 l_database_item_suffix := p_database_item_suffix;
178 end if;
179 --
180 -- On yearlys, check the beginning date of the tax yr of the
181 -- original asg action (the owning action). If the effective date
182 -- of the previous action is before the start yr date, then it
183 -- has expired and cannot be used.
184 -- However, if the owning action is null this means the call to this
185 -- function is in date mode, so the expiry of that action has to be checked.
186 -- PQP - pass in the db item suffix for 2 yearly check.
187 --
188 if instr(l_database_item_suffix,'YTD') > 0 then
189 -- yearly balance, so call yearly expiry
190 if p_owning_action_id is not null then
191 -- Asg Action call, use start date.
192 l_return_date := start_year_date(p_owning_action_id,
193 l_database_item_suffix);
194 if nvl(p_action_effective_date,get_action_date(p_assignment_action_id))
195 < l_return_date
196 or l_return_date is null then
197 l_expired := TRUE;
198 end if;
199 --
200 else
201 -- date mode call, so use expiry of single action
202 l_return_date :=
203 expired_year_date(nvl(p_action_effective_date,
204 (get_action_date(p_assignment_action_id))));
205 if p_effective_date >= l_return_date
206 or l_return_date is null then
207 l_expired := TRUE;
208 end if;
209 end if;
210 elsif instr(l_database_item_suffix,'PTD') > 0 then
211 --period balance, so call period expiry
212 if p_effective_date > expired_period_date(p_assignment_action_id) then
213 l_expired := TRUE;
214 end if;
215 elsif instr(l_database_item_suffix,'RUN') > 0 then
216 --run balance, so call period expiry
217 if p_effective_date > expired_period_date(p_assignment_action_id) then
218 l_expired := TRUE;
219 end if;
220 elsif instr(l_database_item_suffix,'PAYMENTS') > 0 then
221 --again call period expiry
222 if p_effective_date > expired_period_date(p_assignment_action_id) then
223 l_expired := TRUE;
224 end if;
225 elsif instr(l_database_item_suffix,'QTD') > 0 then
226 --quarterly balance, so call quarterly expiry
227 l_return_date := expired_quarter_date
228 (nvl(p_action_effective_date,(get_action_date(p_assignment_action_id))));
229 if p_effective_date > l_return_date
230 or l_return_date is null then
231 l_expired := TRUE;
232 end if;
233 end if;
234 --
235 RETURN l_expired;
236 --
237 END balance_expired;
238 -------------------------------------------------------------------------------
239 -- FUNCTION start_year_date
240 -- This function returns the Start of Year for an
241 -- Assignment Action.
242 -------------------------------------------------------------------------------
243 FUNCTION start_year_date(p_assignment_action_id IN NUMBER,
244 p_database_item_suffix IN VARCHAR2)
245
246 --
247 RETURN DATE IS
248 --
249 cursor csr_start_fin_yr(c_assignment_action_id in number) is
250 select to_date('06-04-' || to_char( to_number(
251 to_char( PTP.regular_payment_date,'YYYY'))
252 + decode(sign( PTP.regular_payment_date - to_date('06-04-'
253 || to_char(PTP.regular_payment_date,'YYYY'),'DD-MM-YYYY')),
254 -1,-1,0)),'DD-MM-YYYY') finyear, BACT.payroll_id
255 from
256 per_time_periods PTP,
257 pay_payroll_actions BACT,
258 pay_assignment_actions ACT
259 where PTP.time_period_id = BACT.time_period_id
260 and ACT.assignment_action_id = c_assignment_action_id
261 and BACT.payroll_action_id = ACT.payroll_action_id;
262 --
263 -- cursor to get the start of the expiry code year as current fin year
264 -- minus 1 if the current tax year is odd and cur tax year if the
265 -- year is even
266 --
267 CURSOR csr_start_fin_yr_odd_ytd(c_assignment_action_id in number ) is
268 SELECT to_date('06-04-' || to_char( fnd_number.canonical_to_number(
269 to_char( PTP.regular_payment_date,'YYYY'))
270 + decode(sign( PTP.regular_payment_date - to_date('06-04-'
271 || to_char(PTP.regular_payment_date,'YYYY'),'DD-MM-YYYY')),
272 -1,-1,0) -
273 mod(
274 fnd_number.canonical_to_number(
275 to_char( PTP.regular_payment_date,'YYYY'))
276 + decode(sign( PTP.regular_payment_date - to_date('06-04-'
277 || to_char(PTP.regular_payment_date,'YYYY'),'DD-MM-YYYY')),
278 -1,-1,0),2)
279 ),'DD-MM-YYYY') finyear, BACT.payroll_id
280 FROM
281 per_time_periods PTP,
282 pay_payroll_actions BACT,
283 pay_assignment_actions ACT
284 WHERE PTP.time_period_id = BACT.time_period_id
285 AND ACT.assignment_action_id = c_assignment_action_id
286 AND BACT.payroll_action_id = ACT.payroll_action_id;
287 --
288 -- cursor to get the start of the expiry code year as current fin year
289 -- minus 1 if the current tax year is even and cur tax year if the
290 -- year is odd
291 --
292 CURSOR csr_start_fin_yr_even_ytd(c_assignment_action_id in number ) is
293 SELECT to_date('06-04-' || to_char( fnd_number.canonical_to_number(
294 to_char( PTP.regular_payment_date,'YYYY'))
295 + decode(sign( PTP.regular_payment_date - to_date('06-04-'
296 || to_char(PTP.regular_payment_date,'YYYY'),'DD-MM-YYYY')),
297 -1,-1,0) -
298 mod(
299 fnd_number.canonical_to_number(
300 to_char( PTP.regular_payment_date,'YYYY'))
301 + decode(sign( PTP.regular_payment_date - to_date('06-04-'
302 || to_char(PTP.regular_payment_date,'YYYY'),'DD-MM-YYYY')),
303 -1,0,-1),2)
304 ),'DD-MM-YYYY') finyear, BACT.payroll_id
305 FROM
306 per_time_periods PTP,
307 pay_payroll_actions BACT,
308 pay_assignment_actions ACT
309 WHERE PTP.time_period_id = BACT.time_period_id
310 AND ACT.assignment_action_id = c_assignment_action_id
311 AND BACT.payroll_action_id = ACT.payroll_action_id;
312 --
313 --
314 cursor csr_start_pay_yr(c_tax_yr_start in date,
315 c_payroll_id in number) is
316 select min(TP.start_date)
317 from per_time_periods TP
318 where TP.payroll_id = c_payroll_id
319 and TP.regular_payment_date >= c_tax_yr_start;
320 --
321 l_year_add_no NUMBER;
322 l_pay_year_start DATE;
323 l_tax_year_start DATE;
324 l_payroll_id NUMBER;
325 --
326 BEGIN
327 --
328 -- PQP, check whether this is the two-year expiry
329 --
330 if p_database_item_suffix = '_ASG_TD_ODD_TWO_YTD' then
331 --
332 open csr_start_fin_yr_odd_ytd(p_assignment_action_id);
333 fetch csr_start_fin_yr_odd_ytd into l_tax_year_start, l_payroll_id;
334 close csr_start_fin_yr_odd_ytd;
335 --
336 elsif p_database_item_suffix = '_ASG_TD_EVEN_TWO_YTD' then
337 --
338 open csr_start_fin_yr_even_ytd(p_assignment_action_id);
339 fetch csr_start_fin_yr_even_ytd into l_tax_year_start, l_payroll_id;
340 close csr_start_fin_yr_even_ytd;
341 --
342 else
343 --
344 -- Get the start of the financial year for the regular YTD
345 --
346 open csr_start_fin_yr(p_assignment_action_id);
347 fetch csr_start_fin_yr into l_tax_year_start, l_payroll_id;
348 close csr_start_fin_yr;
349 --
350 end if;
351 --
352 -- Get the start of the first period in the financial year,
353 -- this is the expiry date.
354 --
355 open csr_start_pay_yr(l_tax_year_start, l_payroll_id);
356 fetch csr_start_pay_yr into l_pay_year_start;
357 close csr_start_pay_yr;
358 --
359 RETURN l_pay_year_start;
360 --
361 END start_year_date;
362 --
363 -------------------------------------------------------------------------------
364 -- FUNCTION expired_year_date
365 -- This function returns the expiry of an eff.date's tax year
366 -------------------------------------------------------------------------------
367 FUNCTION expired_year_date(p_action_effective_date IN DATE)
368 --
369 RETURN DATE IS
370 --
371 l_expired_date DATE;
372 l_year_add_no NUMBER;
373 --
374 BEGIN
375 --
376 if p_action_effective_date <
377 to_date('06-04-' || to_char(p_action_effective_date,'YYYY'),
378 'DD-MM-YYYY') then
379 l_year_add_no := 0;
380 else l_year_add_no := 1;
381 end if;
382 --
383 -- Set expired date to the 6th of April next.
384 --
385 l_expired_date :=
386 ( to_date('06-04-' || to_char( fnd_number.canonical_to_number(to_char(
387 p_action_effective_date,'YYYY')) + l_year_add_no),'DD-MM-YYYY'));
388 --
389 RETURN l_expired_date;
390 --
391 END expired_year_date;
392 --
393 -------------------------------------------------------------------------------
394 -- FUNCTION expired_period_date
395 -- This function returns the expiry of an action's time period
396 -------------------------------------------------------------------------------
397 FUNCTION expired_period_date(p_assignment_action_id IN NUMBER)
398 --
399 RETURN DATE IS
400 --
401 l_end_date DATE;
402 --
403 cursor expired_time_period (c_assignment_action_id IN NUMBER) is
404 select ptp.end_date
405 from per_time_periods ptp,
406 pay_payroll_actions ppa,
407 pay_assignment_actions paa
408 WHERE
409 paa.assignment_action_id = c_assignment_action_id
410 AND paa.payroll_action_id = ppa.payroll_action_id
411 AND ppa.time_period_id = ptp.time_period_id;
412 --
413 BEGIN
414 --
415 open expired_time_period(p_assignment_action_id);
416 fetch expired_time_period into l_end_date;
417 close expired_time_period;
418 --
419 RETURN l_end_date;
420 --
421 END expired_period_date;
422 -------------------------------------------------------------------------------
423 -- FUNCTION expired_quarter_date
424 -- This function returns the expiry of an eff.dates quarter
425 -------------------------------------------------------------------------------
426 FUNCTION expired_quarter_date(p_action_effective_date IN DATE)
427 --
428 RETURN DATE IS
429 --
430 l_expired_date DATE;
431 l_conv_us_gb_qd DATE;
432 --
433 BEGIN
434 --
435 --First convert to GB quarters
436 l_conv_us_gb_qd := (trunc(p_action_effective_date -5,'Q')+5);
437 --Then find the date of expiry of the quarter
438 l_expired_date := ((ROUND(ADD_MONTHS(l_conv_us_gb_qd,1) + 16,'Q')) -1) + 5;
439 --
440 RETURN l_expired_date;
441 --
442 END expired_quarter_date;
443 -------------------------------------------------------------------------------
444 -- FUNCTION get_action_date
445 -- This function gets the effective date of an assignment action
446 -------------------------------------------------------------------------------
447 FUNCTION get_action_date(p_assignment_action_id IN NUMBER)
448 RETURN DATE IS
449 --
450 l_effective_date date;
451 --
452 cursor c_bal_date is
453 SELECT ppa.effective_date
454 FROM pay_payroll_actions ppa,
455 pay_assignment_actions paa
456 WHERE paa.payroll_action_id = ppa.payroll_action_id
457 AND paa.assignment_action_id = p_assignment_action_id;
458 --
459 begin
460 --
461 OPEN c_bal_date;
462 FETCH c_bal_date into l_effective_date;
463 if c_bal_date%NOTFOUND then
464 --raise_application_error(-20000,'This assignment action is invalid');
465 --cant use as violates pragma wnds, so set date to null
466 l_effective_date := null;
467 end if;
468 CLOSE c_bal_date;
469 --
470 RETURN l_effective_date;
471 END get_action_date;
472 --
473 -------------------------------------------------------------------------------
474 --
475 END hr_dirbal;
[Home] [Help]