1 PACKAGE BODY pay_sg_exc AS
2 /* $Header: pysgexch.pkb 120.0 2005/05/29 08:46:02 appldev noship $ */
3 /*
4 PRODUCT
5 Oracle*Payroll
6 NAME
7 pysgexch.pkb - Payroll SG legislation Expiry Checking code.
8 DESCRIPTION
9 Contains the expiry checking code associated with the SG
10 balance dimensions. Following the change
11 to latest balance functionality, these need to be contained
12 as packaged procedures.
13 PUBLIC FUNCTIONS
14 <none>
15 PRIVATE FUNCTIONS
16 <none>
17 NOTES
18 <none>
19 MODIFIED (DD/MM/YY)
20 jbailie 25/04/00 - first created. Based on PAYUSEXC (115)
21
22 */
23
24 /*---------------------------- next_period -----------------------------------
25 NAME
26 next_period
27 DESCRIPTION
28 Given a date and a payroll action id, returns the date of the day after
29 the end of the containing pay period.
30 NOTES
31 <none>
32 */
33 FUNCTION next_period
34 (
35 p_pactid IN NUMBER,
36 p_date IN DATE
37 ) RETURN DATE is
38 l_return_val DATE := NULL;
39 BEGIN
40 select TP.end_date + 1
41 into l_return_val
42 from per_time_periods TP,
43 pay_payroll_actions PACT
44 where PACT.payroll_action_id = p_pactid
45 and PACT.payroll_id = TP.payroll_id
46 and p_date between TP.start_date and TP.end_date;
47
48 RETURN l_return_val;
49
50 END next_period;
51
52 /*---------------------------- next_month ------------------------------------
53 NAME
54 next_month
55 DESCRIPTION
56 Given a date, returns the date of the first day of the next month.
57 NOTES
58 <none>
59 */
60 FUNCTION next_month
61 (
62 p_date IN DATE
63 ) return DATE is
64 BEGIN
65
66 RETURN trunc(add_months(p_date,1),'MM');
67
68 END next_month;
69
70 /*--------------------------- next_quarter -----------------------------------
71 NAME
72 next_quarter
73 DESCRIPTION
74 Given a date, returns the date of the first day of the next calendar
75 quarter.
76 NOTES
77 <none>
78 */
79 FUNCTION next_quarter
80 (
81 p_date IN DATE
82 ) RETURN DATE is
83 BEGIN
84
85 RETURN trunc(add_months(p_date,3),'Q');
86
87 END next_quarter;
88
89 /*---------------------------- next_year ------------------------------------
90 NAME
91 next_year
92 DESCRIPTION
93 Given a date, returns the date of the first day of the next calendar
94 year.
95 NOTES
96 <none>
97 */
98 FUNCTION next_year
99 (
100 p_date IN DATE
101 ) RETURN DATE is
102 BEGIN
103
104 RETURN trunc(add_months(p_date,12),'Y');
105
106 END next_year;
107
108 /*------------------------- next_fiscal_quarter -----------------------------
109 NAME
110 next_fiscal_quarter
111 DESCRIPTION
112 Given a date, returns the date of the first day of the next fiscal
113 quarter.
114 NOTES
115 <none>
116 */
117 FUNCTION next_fiscal_quarter
118 (
119 p_beg_of_fiscal_year IN DATE,
120 p_date IN DATE
121 ) RETURN DATE is
122
123 BEGIN
124
125 RETURN (add_months(p_beg_of_fiscal_year, 3*(ceil(months_between(p_date+1,p_beg_of_fiscal_year)/3))));
126
127 END next_fiscal_quarter;
128
129 /*--------------------------- next_fiscal_year ------------------------------
130 NAME
131 next_fiscal_year
132 DESCRIPTION
133 Given a date, returns the date of the first day of the next fiscal year.
134 NOTES
135 <none>
136 */
137 FUNCTION next_fiscal_year
138 (
139 p_beg_of_fiscal_year IN DATE,
140 p_date IN DATE
141 ) RETURN DATE is
142
143 BEGIN
144
145 RETURN (add_months(p_beg_of_fiscal_year, 12*(ceil(months_between(p_date+1,p_beg_of_fiscal_year)/12))));
146
147 END next_fiscal_year;
148 /*------------------------------ date_ec ------------------------------------
149 NAME
150 date_ec
151 DESCRIPTION
152 Expiry checking code for the Singapore dimensions:
153 NOTES
154 This procedure assumes the date portion of the dimension name
155 is always at the end to allow accurate identification since
156 this is used for many dimensions.
157 */
158 PROCEDURE date_ec
159 (
160 p_owner_payroll_action_id in number, -- run created balance.
161 p_user_payroll_action_id in number, -- current run.
162 p_owner_assignment_action_id in number, -- assact created balance.
163 p_user_assignment_action_id in number, -- current assact..
164 p_owner_effective_date in date, -- eff date of balance.
165 p_user_effective_date in date, -- eff date of current run.
166 p_dimension_name in varchar2, -- balance dimension name.
167 p_expiry_information out nocopy number -- dimension expired flag.
168 ) is
169
170 l_beg_of_fiscal_year DATE := NULL;
171 l_expiry_date DATE := NULL;
172
173 BEGIN
174
175 IF p_dimension_name like '%RUN' THEN
176 -- must check for special case: if payroll action id's are the same,
177 -- then don't expire. This facilitates meaningful access of these
178 -- balances outside of runs.
179 IF p_owner_payroll_action_id <> p_user_payroll_action_id THEN
180 l_expiry_date := p_user_effective_date; -- always must expire.
181 ELSE
182 p_expiry_information := 0;
183 RETURN;
184 END IF;
185
186 ELSIF p_dimension_name like '%PAYMENTS' THEN
187 -- must check for special case: if payroll action id's are the same,
188 -- then don't expire. This facilitates meaningful access of these
189 -- balances outside of runs.
190 IF p_owner_payroll_action_id <> p_user_payroll_action_id THEN
191 l_expiry_date := p_user_effective_date; -- always must expire.
192 ELSE
193 p_expiry_information := 0;
194 RETURN;
195 END IF;
196
197 ELSIF p_dimension_name like '%PTD' THEN
198 l_expiry_date := next_period(p_owner_payroll_action_id,
199 p_owner_effective_date);
200
201 ELSIF p_dimension_name like '%MONTH' THEN
202 l_expiry_date := next_month(p_owner_effective_date);
203
204 ELSIF p_dimension_name like '%FQTD' THEN
205 SELECT fnd_date.canonical_to_date(org_information11)
206 INTO l_beg_of_fiscal_year
207 FROM pay_payroll_actions PACT,
208 hr_organization_information HOI
209 WHERE UPPER(HOI.org_information_context) = 'BUSINESS GROUP INFORMATION'
210 AND HOI.organization_id = PACT.business_group_id
211 AND PACT.payroll_action_id = p_owner_payroll_action_id;
212
213 l_expiry_date := next_fiscal_quarter(l_beg_of_fiscal_year,
214 p_owner_effective_date);
215
216 ELSIF p_dimension_name like '%FYTD' THEN
217 SELECT fnd_date.canonical_to_date(org_information11)
218 INTO l_beg_of_fiscal_year
219 FROM pay_payroll_actions PACT,
220 hr_organization_information HOI
221 WHERE UPPER(HOI.org_information_context) = 'BUSINESS GROUP INFORMATION'
222 AND HOI.organization_id = PACT.business_group_id
223 AND PACT.payroll_action_id = p_owner_payroll_action_id;
224
225 l_expiry_date := next_fiscal_year(l_beg_of_fiscal_year,
226 p_owner_effective_date);
227
228 ELSIF p_dimension_name like '%QTD' THEN
229 l_expiry_date := next_quarter(p_owner_effective_date);
230
231 ELSIF p_dimension_name like '%YTD' THEN
232 l_expiry_date := next_year(p_owner_effective_date);
233
234 ELSIF p_dimension_name like '%LTD' THEN
235 p_expiry_information := 0;
236 RETURN;
237
238 ELSE
239 hr_utility.set_message(801, 'NO_EXP_CHECK_FOR_DIMENSION');
240 hr_utility.raise_error;
241
242 END IF;
243
244 IF p_user_effective_date >= l_expiry_date THEN
245 p_expiry_information := 1;
246 ELSE
247 p_expiry_information := 0;
248 END IF;
249
250 END date_ec;
251
252
253 /*------------------------------ date_ec ------------------------------------
254
255 NAME
256 date_ec
257 DESCRIPTION
258 Expiry checking code for the Singapore dimensions:
259 NOTES
260 This procedure assumes the date portion of the dimension name
261 is always at the end to allow accurate identification since
262 this is used for many dimensions.
263 This procedure has been added for Balance Adjustment Process Enhancement,
264 Bug 2797863
265 */
266 PROCEDURE date_ec
267 (
268 p_owner_payroll_action_id in number, -- run created balance.
269
270 p_user_payroll_action_id in number, -- current run.
271 p_owner_assignment_action_id in number, -- assact created balance.
272
273 p_user_assignment_action_id in number, -- current assact..
274 p_owner_effective_date in date, -- eff date of balance.
275
276 p_user_effective_date in date, -- eff date of current run.
277
278 p_dimension_name in varchar2, -- balance dimension name.
279
280 p_expiry_information out nocopy date -- dimension expired date.
281
282 ) is
283
284 l_beg_of_fiscal_year DATE := NULL;
285 l_expiry_date DATE := NULL;
286
287 BEGIN
288
289 hr_utility.set_location('Entering: date_ec', 10);
290 hr_utility.set_location('p_owner_payroll_action_id :'||p_owner_payroll_action_id, 20);
291 hr_utility.set_location('p_user_payroll_action_id :'||p_user_payroll_action_id, 20);
292 hr_utility.set_location('p_owner_assignment_action_id :'||p_owner_assignment_action_id, 20);
293 hr_utility.set_location('p_user_assignment_action_id :'||p_user_assignment_action_id, 20);
294 hr_utility.set_location('p_owner_effective_date :'||p_owner_effective_date, 20);
295 hr_utility.set_location('p_user_effective_date :'||p_user_effective_date, 20);
296 hr_utility.set_location('p_dimension_name :'||p_dimension_name, 20);
297
298 IF p_dimension_name like '%RUN' THEN
299 -- must check for special case: if payroll action id's are the same,
300 -- then don't expire. This facilitates meaningful access of these
301 -- balances outside of runs.
302
303 p_expiry_information := p_owner_effective_date;
304 hr_utility.set_location('p_expiry_information'||p_dimension_name||':'||p_expiry_information, 30);
305
306 ELSIF p_dimension_name like '%PAYMENTS' THEN
307
308 p_expiry_information := p_owner_effective_date;
309 hr_utility.set_location('p_expiry_information'||p_dimension_name||':'||p_expiry_information, 30);
310
311 ELSIF p_dimension_name like '%PTD' THEN
312
313 p_expiry_information := next_period(p_owner_payroll_action_id,
314 p_owner_effective_date) - 1;
315 hr_utility.set_location('p_expiry_information'||p_dimension_name||':'||p_expiry_information, 30);
316
317 ELSIF p_dimension_name like '%MONTH' THEN
318
319 p_expiry_information := next_month(p_owner_effective_date) -1 ;
320 hr_utility.set_location('p_expiry_information'||p_dimension_name||':'||p_expiry_information, 30);
321
322 ELSIF p_dimension_name like '%FQTD' THEN
323 SELECT fnd_date.canonical_to_date(org_information11)
324 INTO l_beg_of_fiscal_year
325 FROM pay_payroll_actions PACT,
326 hr_organization_information HOI
327 WHERE UPPER(HOI.org_information_context) = 'BUSINESS GROUP INFORMATION'
328 AND HOI.organization_id = PACT.business_group_id
329 AND PACT.payroll_action_id = p_owner_payroll_action_id;
330
331 p_expiry_information := next_fiscal_quarter(l_beg_of_fiscal_year,
332 p_owner_effective_date) - 1;
333 hr_utility.set_location('p_expiry_information'||p_dimension_name||':'||p_expiry_information, 30);
334
335 ELSIF p_dimension_name like '%FYTD' THEN
336 SELECT fnd_date.canonical_to_date(org_information11)
337 INTO l_beg_of_fiscal_year
338 FROM pay_payroll_actions PACT,
339 hr_organization_information HOI
340 WHERE UPPER(HOI.org_information_context) = 'BUSINESS GROUP INFORMATION'
341
342 AND HOI.organization_id = PACT.business_group_id
343 AND PACT.payroll_action_id = p_owner_payroll_action_id;
344
345 p_expiry_information := next_fiscal_year(l_beg_of_fiscal_year, p_owner_effective_date) - 1;
346 hr_utility.set_location('p_expiry_information'||p_dimension_name||':'||p_expiry_information, 30);
347
348 ELSIF p_dimension_name like '%QTD' THEN
349
350 p_expiry_information := next_quarter(p_owner_effective_date) - 1;
351 hr_utility.set_location('p_expiry_information'||p_dimension_name||':'||p_expiry_information, 30);
352
353 ELSIF p_dimension_name like '%YTD' THEN
354
355 p_expiry_information := next_year(p_owner_effective_date) - 1;
356 hr_utility.set_location('p_expiry_information'||p_dimension_name||':'||p_expiry_information, 30);
357
358 ELSIF p_dimension_name like '%LTD' THEN
359
360 p_expiry_information := fnd_date.canonical_to_date('4712/12/31');
361 hr_utility.set_location('p_expiry_information'||p_dimension_name||':'||p_expiry_information, 30);
362
363 ELSE
364
365 hr_utility.set_message(801, 'NO_EXP_CHECK_FOR_DIMENSION');
366 hr_utility.raise_error;
367
368 END IF;
369
370 hr_utility.set_location('Ending: date_ec', 40);
371
372 END date_ec; /* bug 2797863 */
373
374
375 FUNCTION get_expiry_date
376 (
377 p_defined_balance_id in number, -- defined balance.
378 p_assignment_action_id in number -- assact created balance.
379 ) RETURN DATE is
380
381 l_dimension_name VARCHAR2(160);
382 l_payroll_action_id NUMBER;
383 l_effective_date DATE := NULL;
384 l_beg_of_fiscal_year DATE := NULL;
385 l_end_of_time CONSTANT DATE := to_date('31/12/4712','DD/MM/YYYY');
386
387 cursor dimension_name( c_defined_balance_id in number ) is
388 SELECT dimension_name
389 FROM pay_defined_balances pdb
390 , pay_balance_dimensions pbd
391 WHERE pdb.balance_dimension_id = pbd.balance_dimension_id
392 AND pdb.defined_balance_id = c_defined_balance_id;
393
394 cursor pact_effective_date( c_assignment_action_id in number ) is
395 SELECT ppa.payroll_action_id, ppa.effective_date
396 FROM pay_payroll_actions ppa, pay_assignment_actions paa
397 WHERE ppa.payroll_action_id = paa.payroll_action_id
398 AND paa.assignment_action_id = c_assignment_action_id;
399
400 BEGIN
401 --
402 open dimension_name ( p_defined_balance_id );
403 fetch dimension_name into l_dimension_name;
404 close dimension_name;
405 --
406 open pact_effective_date ( p_assignment_action_id );
407 fetch pact_effective_date into l_payroll_action_id, l_effective_date;
408 close pact_effective_date;
409 --
410 IF l_dimension_name like '%RUN' THEN
411 -- must check for special case: Will always expire on date of run.
412 RETURN l_effective_date; -- always must expire.
413
414 ELSIF l_dimension_name like '%PAYMENTS' THEN
415 -- must check for special case: Will always expire on date of run.
416 RETURN l_effective_date; -- always must expire.
417
418 ELSIF l_dimension_name like '%PTD' THEN
419 -- this will expire at the end of the period
420 RETURN next_period(l_payroll_action_id, l_effective_date) - 1;
421
422 ELSIF l_dimension_name like '%MONTH' THEN
423 -- this will expire at the end of the month
424 RETURN next_month(l_effective_date) - 1;
425
426 ELSIF l_dimension_name like '%FQTD' THEN
427 SELECT fnd_date.canonical_to_date(org_information11)
428 INTO l_beg_of_fiscal_year
429 FROM pay_payroll_actions PACT,
430 hr_organization_information HOI
431 WHERE UPPER(HOI.org_information_context) = 'BUSINESS GROUP INFORMATION'
432 AND HOI.organization_id = PACT.business_group_id
433 AND PACT.payroll_action_id = l_payroll_action_id;
434
435 RETURN next_fiscal_quarter(l_beg_of_fiscal_year, l_effective_date) - 1;
436
437 ELSIF l_dimension_name like '%FYTD' THEN
438 SELECT fnd_date.canonical_to_date(org_information11)
439 INTO l_beg_of_fiscal_year
440 FROM pay_payroll_actions PACT,
441 hr_organization_information HOI
442 WHERE UPPER(HOI.org_information_context) = 'BUSINESS GROUP INFORMATION'
443 AND HOI.organization_id = PACT.business_group_id
444 AND PACT.payroll_action_id = l_payroll_action_id;
445
446 RETURN next_fiscal_year(l_beg_of_fiscal_year, l_effective_date) - 1;
447
448 ELSIF l_dimension_name like '%QTD' THEN
449 RETURN next_quarter(l_effective_date) - 1;
450
451 ELSIF l_dimension_name like '%YTD' THEN
452 RETURN next_year(l_effective_date) - 1;
453
454 ELSIF l_dimension_name like '%LTD' THEN
455 RETURN l_end_of_time;
456
457 ELSE
458 hr_utility.set_message(801, 'NO_EXPIRY_DATE_FOR_DIMENSION');
459 hr_utility.raise_error;
460
461 END IF;
462
463 END get_expiry_date;
464
465
466 FUNCTION calculated_value
467 (
468 p_defined_balance_id in number, -- defined balance.
469 p_assignment_action_id in number, -- assact created balance.
470 p_tax_unit_id in number,
471 p_session_date in date
472 ) RETURN NUMBER is
473
474 l_calculated_value NUMBER;
475 l_expiry_date DATE := NULL;
476
477 BEGIN
478
479 l_calculated_value := pay_balance_pkg.get_value(p_defined_balance_id
480 ,p_assignment_action_id
481 ,p_tax_unit_id
482 ,null --jurisdiction
483 ,null --source_id
484 ,null --tax_group
485 ,null --date_earned
486 );
487
488 l_expiry_date := get_expiry_date(p_defined_balance_id, p_assignment_action_id);
489
490 IF p_session_date > l_expiry_date THEN
491 l_calculated_value := 0;
492 END IF;
493
494 RETURN l_calculated_value;
495
496 END calculated_value;
497
498
499
500 END pay_sg_exc;
[Home] [Help]