1 package body pyieexc as
2 /* $Header: pyieexc.pkb 120.3 2009/06/05 06:33:33 knadhan ship $ */
3 /* Copyright (c) Oracle Corporation 1994. ll rights reserved. */
4 /*
5 PRODUCT
6 Oracle*Payroll
7 NAME
8 pyieexc.pkb - PaYroll IE legislation EXpiry Checking code.
9 DESCRIPTION
10 Contains the expiry checking code for the balance dimensions
11 created for IRELAND.
12 PUBLIC FUNCTIONS
13 <none>
14 PRIVATE FUNCTIONS
15 <none>
16 NOTES
17 expiry checking has been made much simpler than the dimensions it
18 supports by the imposition of 2 rules:
19 1) on payroll transfer latest balances are trashed ( so we don't
20 need to check for the date of transfer etc we need only worry
21 about the start date of the current payroll period and payroll
22 year.
23 2) legislative balances rely on the regular payment date of the period
24 to dictate the legislative poeriod they fall into - you can't transfer
25 onto a payroll until the new payrolls period regular payment date is
26 after the regular payment date of the last period processed. This
27 supports the rule that you can't transfer from a longer processing
28 period to a shorter one until the end of the longer period.
29
30 MODIFIED (DD/MM/YY)
31 rmakhija 15/06/01 - first created by editing similar package of GB Localization.
32 abhaduri 16/04/02 - added ASG_PROC_PTD_EC procedure for new dimension
33 _ELEMENT_PTD for Attachment of Earnings Order.
34 smrobins 30/09/02 - changed ASG_YTD_EC procedure, to derive expiry
35 from effective date of action, rather than
36 regular payment date.
37 mmahmad 24/01/03 - Added NOCOPY
38 vmkhande 16/04/03 Added expiry code for ASG_QTD
39 npershad 30/10/03 Added expiry code for ASG_TWO_YTD
40 rtulaban 26/05/04 Added start date code for ASG_TWO_YTD
41 rbhardwa 01/03/06 Made changes to accomodate offset payrolls.
42 Bug 5070091.
43 knadhan 05/06/09 Created overridden procedures for all procedure for preventing
44 loss of latest balance when an baalnce adjsutment done.
45 Bug 8522294.
46 */
47 /*------------------------------ ASG_PTD_EC ----------------------------*/
48 /*
49 NAME
50 ASG_PTD_EC - Assignment Period to Date expiry check.
51 DESCRIPTION
52 Expiry checking code for the following:
53 IE Assignment-level Period To Date Balance Dimension
54 NOTES
55 The associated dimension is expiry checked at assignment action level
56 */
57
58 /* 8522294 */
59 FUNCTION next_period
60 (
61 p_pactid IN NUMBER,
62 p_date IN DATE
63 ) RETURN DATE is
64 l_return_val DATE := NULL;
65 BEGIN
66 select TP.end_date + 1
67 into l_return_val
68 from per_time_periods TP,
69 pay_payroll_actions PACT
70 where PACT.payroll_action_id = p_pactid
71 and PACT.payroll_id = TP.payroll_id
72 and p_date between TP.start_date and TP.end_date;
73
74 RETURN l_return_val;
75
76 END next_period;
77
78 FUNCTION next_quarter
79 (
80 p_date IN DATE
81 ) RETURN DATE is
82 BEGIN
83
84 RETURN trunc(add_months(p_date,3),'Q');
85
86 END next_quarter;
87
88
89 FUNCTION next_year
90 (
91 p_date IN DATE
92 ) RETURN DATE is
93 BEGIN
94
95 RETURN trunc(add_months(p_date,12),'Y');
96
97 END next_year;
98
99 FUNCTION next_two_year
100 (
101 p_date IN DATE
102 ) RETURN DATE is
103 BEGIN
104
105 RETURN trunc(add_months(p_date,24),'Y');
106
107 END next_two_year;
108
109 procedure ASG_PTD_EC
110 (
111 p_owner_payroll_action_id in number, -- run created balance.
112 p_user_payroll_action_id in number, -- current run.
113 p_owner_assignment_action_id in number, -- assact created balance.
114 p_user_assignment_action_id in number, -- current assact..
115 p_owner_effective_date in date, -- eff date of balance.
116 p_user_effective_date in date, -- eff date of current run.
117 p_dimension_name in varchar2, -- balance dimension name.
118 p_expiry_information out NOCOPY number -- dimension expired flag.
119 ) is
120 l_period_start_date date;
121 begin
122 select ptp.start_date
123 into l_period_start_date
124 from per_time_periods ptp, pay_payroll_actions ppa
125 where ppa.payroll_action_id = p_user_payroll_action_id
126 and ppa.payroll_id = ptp.payroll_id
127 and p_user_effective_date between ptp.start_date and ptp.end_date;
128 -- see if balance was written in this period. If not it is expired
129 IF p_owner_effective_date >= l_period_start_date THEN
130 p_expiry_information := 0;
131 ELSE
132 p_expiry_information := 1;
133 END IF;
134 end ASG_PTD_EC;
135
136 /* 8522294 */
137 procedure ASG_PTD_EC
138 (
139 p_owner_payroll_action_id in number, -- run created balance.
140 p_user_payroll_action_id in number, -- current run.
141 p_owner_assignment_action_id in number, -- assact created balance.
142 p_user_assignment_action_id in number, -- current assact..
143 p_owner_effective_date in date, -- eff date of balance.
144 p_user_effective_date in date, -- eff date of current run.
145 p_dimension_name in varchar2, -- balance dimension name.
146 p_expiry_information out NOCOPY date -- dimension expired flag.
147 ) is
148 l_period_start_date date;
149 begin
150 hr_utility.set_location('k:p_owner_payroll_action_id ' || p_owner_payroll_action_id,10);
151 hr_utility.set_location('k:p_user_payroll_action_id ' || p_user_payroll_action_id,20);
152 hr_utility.set_location('k:p_owner_assignment_action_id ' || p_owner_assignment_action_id,30);
153 hr_utility.set_location('k:p_user_assignment_action_id ' || p_user_assignment_action_id,40);
154 hr_utility.set_location('k:p_user_effective_date ' || to_char(p_user_effective_date,'dd-mon-yyyy'),50);
155 hr_utility.set_location('k:p_owner_effective_date ' || to_char(p_owner_effective_date,'dd-mon-yyyy'),60);
156 hr_utility.set_location('k:p_dimension_name ' || p_dimension_name,70);
157 p_expiry_information := next_period(p_owner_payroll_action_id,
158 p_owner_effective_date) - 1;
159
160 hr_utility.set_location('k:p_expiry_information ' || to_char(p_expiry_information,'dd-mon-yyyy'),70);
161 end ASG_PTD_EC;
162
163 /*------------------------------ ASG_YTD_EC ----------------------------*/
164 /*
165 NAME
166 ASG_YTD_EC - Assignment Tax Year to Date expiry check.
167 DESCRIPTION
168 Expiry checking code for the following:
169 IE Assignment-level Tax Year To Date Balance Dimension
170 NOTES
171 The associated dimension is expiry checked at assignment action level
172 */
173 procedure ASG_YTD_EC
174 (
175 p_owner_payroll_action_id in number, -- run created balance.
176 p_user_payroll_action_id in number, -- current run.
177 p_owner_assignment_action_id in number, -- assact created balance.
178 p_user_assignment_action_id in number, -- current assact..
179 p_owner_effective_date in date, -- eff date of balance.
180 p_user_effective_date in date, -- eff date of current run.
181 p_dimension_name in varchar2, -- balance dimension name.
182 p_expiry_information out NOCOPY number -- dimension expired flag.
183 ) is
184 l_tax_year_start date;
185 l_user_payroll_id number;
186 begin
187 /* select the start of the financial year - if the owning action is
188 * before this or for a different payroll then its expired
189 */
190 Select to_date('01-01-' || to_char( fnd_number.canonical_to_number(
191 to_char( BACT.effective_date,'YYYY'))
192 + decode(sign( BACT.effective_date - to_date('01-01-'
193 || to_char(BACT.effective_date,'YYYY'),'DD-MM-YYYY')),
194 -1,-1,0)),'DD-MM-YYYY') finyear, BACT.payroll_id
195 into l_tax_year_start, l_user_payroll_id
196 from pay_payroll_actions BACT
197 where BACT.payroll_action_id = p_user_payroll_action_id;
198 --
199 --
200 if p_owner_effective_date < l_tax_year_start then
201 p_expiry_information := 1;
202 else
203 p_expiry_information := 0;
204 end if;
205 --
206
207 end ASG_YTD_EC;
208
209 /* 8522294 */
210
211 procedure ASG_YTD_EC
212 (
213 p_owner_payroll_action_id in number, -- run created balance.
214 p_user_payroll_action_id in number, -- current run.
215 p_owner_assignment_action_id in number, -- assact created balance.
216 p_user_assignment_action_id in number, -- current assact..
217 p_owner_effective_date in date, -- eff date of balance.
218 p_user_effective_date in date, -- eff date of current run.
219 p_dimension_name in varchar2, -- balance dimension name.
220 p_expiry_information out NOCOPY date -- dimension expired flag.
221 ) is
222 l_tax_year_start date;
223 l_user_payroll_id number;
224 begin
225 hr_utility.set_location('k:p_owner_payroll_action_id ' || p_owner_payroll_action_id,10);
226 hr_utility.set_location('k:p_user_payroll_action_id ' || p_user_payroll_action_id,20);
227 hr_utility.set_location('k:p_owner_assignment_action_id ' || p_owner_assignment_action_id,30);
228 hr_utility.set_location('k:p_user_assignment_action_id ' || p_user_assignment_action_id,40);
229 hr_utility.set_location('k:p_user_effective_date ' || to_char(p_user_effective_date,'dd-mon-yyyy'),50);
230 hr_utility.set_location('k:p_owner_effective_date ' || to_char(p_owner_effective_date,'dd-mon-yyyy'),60);
231 hr_utility.set_location('k:p_dimension_name ' || p_dimension_name,70);
232 p_expiry_information := next_year(p_owner_effective_date) -1;
233
234 hr_utility.set_location('k:p_expiry_information ' || to_char(p_expiry_information,'dd-mon-yyyy'),70);
235
236 end ASG_YTD_EC;
237
238 /*------------------------------ ASG_PROC_PTD_EC ----------------------------*/
239 /*
240 NAME
241 ASG_PROC_PTD_EC - Assignment Processing Period to Date expiry check.
242 DESCRIPTION
243 Expiry checking code for the following:
244 IE Assignment-level Process Period To Date Balance Dimension
245 NOTES
246 The associated dimension is expiry checked at payroll action level
247 */
248 procedure ASG_PROC_PTD_EC
249 (
250 p_owner_payroll_action_id in number, -- run created balance.
251 p_user_payroll_action_id in number, -- current run.
252 p_owner_assignment_action_id in number, -- assact created balance.
253 p_user_assignment_action_id in number, -- current assact..
254 p_owner_effective_date in date, -- eff date of balance.
255 p_user_effective_date in date, -- eff date of current run.
256 p_dimension_name in varchar2, -- balance dimension name.
257 p_expiry_information out NOCOPY number -- dimension expired flag.
258 ) is
259 l_user_time_period_id number;
260 l_owner_time_period_id number;
261 begin
262 /*
263 * Select the period of the owning and using action and if they are
264 * the same then the dimension has expired - either a prior period
265 * or a different payroll
266 */
267
268 select time_period_id
269 into l_user_time_period_id
270 from pay_payroll_actions
271 where payroll_action_id = p_user_payroll_action_id;
272
273 select time_period_id
274 into l_owner_time_period_id
275 from pay_payroll_actions
276 where payroll_action_id = p_owner_payroll_action_id;
277
278 if l_user_time_period_id = l_owner_time_period_id then
279 p_expiry_information := 0;
280 else
281 p_expiry_information := 1;
282 end if;
283
284 end ASG_PROC_PTD_EC;
285
286 /* 8522294 */
287
288 procedure ASG_PROC_PTD_EC
289 (
290 p_owner_payroll_action_id in number, -- run created balance.
291 p_user_payroll_action_id in number, -- current run.
292 p_owner_assignment_action_id in number, -- assact created balance.
293 p_user_assignment_action_id in number, -- current assact..
294 p_owner_effective_date in date, -- eff date of balance.
295 p_user_effective_date in date, -- eff date of current run.
296 p_dimension_name in varchar2, -- balance dimension name.
297 p_expiry_information out NOCOPY date -- dimension expired flag.
298 ) is
299 l_user_time_period_id number;
300 l_owner_time_period_id number;
301 begin
302 hr_utility.set_location('k:p_owner_payroll_action_id ' || p_owner_payroll_action_id,10);
303 hr_utility.set_location('k:p_user_payroll_action_id ' || p_user_payroll_action_id,20);
304 hr_utility.set_location('k:p_owner_assignment_action_id ' || p_owner_assignment_action_id,30);
305 hr_utility.set_location('k:p_user_assignment_action_id ' || p_user_assignment_action_id,40);
306 hr_utility.set_location('k:p_user_effective_date ' || to_char(p_user_effective_date,'dd-mon-yyyy'),50);
307 hr_utility.set_location('k:p_owner_effective_date ' || to_char(p_owner_effective_date,'dd-mon-yyyy'),60);
308 hr_utility.set_location('k:p_dimension_name ' || p_dimension_name,70);
309
310 p_expiry_information := next_period(p_owner_payroll_action_id,
311 p_owner_effective_date) - 1;
312 hr_utility.set_location('k:p_expiry_information ' || to_char(p_expiry_information,'dd-mon-yyyy'),70);
313 end ASG_PROC_PTD_EC;
314 /*------------------------------ ASG_QTD_EC ----------------------------*/
315 /*
316 NAME
317 ASG_QTD_EC - Assignment Quater to Date expiry check.
318 DESCRIPTION
319 Expiry checking code for the following:
320 IE Assignment-level Quater To Date Balance Dimension
321 NOTES
322 The associated dimension is expiry checked at assignment action level
323 */
324 procedure ASG_QTD_EC
325 (
326 p_owner_payroll_action_id in number, -- run created balance.
327 p_user_payroll_action_id in number, -- current run.
328 p_owner_assignment_action_id in number, -- assact created balance.
329 p_user_assignment_action_id in number, -- current assact..
330 p_owner_effective_date in date, -- eff date of balance.
331 p_user_effective_date in date, -- eff date of current run.
332 p_dimension_name in varchar2, -- balance dimension name.
333 p_expiry_information out NOCOPY number -- dimension expired flag.
334 ) is
335 begin
336 hr_utility.trace('p_user_effective_date ' || to_char(p_user_effective_date,'dd-mon-yyyy'));
337 hr_utility.trace('p_owner_effective_date ' || to_char(p_owner_effective_date,'dd-mon-yyyy'));
338 IF p_user_effective_date >= trunc(add_months(p_owner_effective_date,3),'Q')
339 THEN
340 hr_utility.trace(' 1 ');
341 P_expiry_information := 1; -- Expired!
342 ELSE
343 hr_utility.trace(' 0 ');
344 P_expiry_information := 0; -- OK!
345 END IF;
346 end ASG_QTD_EC;
347
348 /* 8522294 */
349 procedure ASG_QTD_EC
350 (
351 p_owner_payroll_action_id in number, -- run created balance.
352 p_user_payroll_action_id in number, -- current run.
353 p_owner_assignment_action_id in number, -- assact created balance.
354 p_user_assignment_action_id in number, -- current assact..
355 p_owner_effective_date in date, -- eff date of balance.
356 p_user_effective_date in date, -- eff date of current run.
357 p_dimension_name in varchar2, -- balance dimension name.
358 p_expiry_information out NOCOPY date -- dimension expired flag.
359 ) is
360 begin
361 hr_utility.set_location('k:p_owner_payroll_action_id ' || p_owner_payroll_action_id,10);
362 hr_utility.set_location('k:p_user_payroll_action_id ' || p_user_payroll_action_id,20);
363 hr_utility.set_location('k:p_owner_assignment_action_id ' || p_owner_assignment_action_id,30);
364 hr_utility.set_location('k:p_user_assignment_action_id ' || p_user_assignment_action_id,40);
365 hr_utility.set_location('k:p_user_effective_date ' || to_char(p_user_effective_date,'dd-mon-yyyy'),50);
366 hr_utility.set_location('k:p_owner_effective_date ' || to_char(p_owner_effective_date,'dd-mon-yyyy'),60);
367 hr_utility.set_location('k:p_dimension_name ' || p_dimension_name,70);
368 p_expiry_information := next_quarter(p_owner_effective_date) - 1;
369
370 hr_utility.set_location('k:p_expiry_information ' || to_char(p_expiry_information,'dd-mon-yyyy'),70);
371 end ASG_QTD_EC;
372 /*------------------------------ ASG_TWO_YTD_EC ----------------------------*/
373 /*
374 NAME
375 ASG_TWO_YTD_EC - Assignment Tax Year/Previous Year to Date expiry check.
376 DESCRIPTION
377 Expiry checking code for the following:
378 IE Assignment-level Tax Year/Previous Year To Date Balance Dimension
379 NOTES
380 The associated dimension is expiry checked at assignment action level
381 */
382 procedure ASG_TWO_YTD_EC
383 (
384 p_owner_payroll_action_id in number, -- run created balance.
385 p_user_payroll_action_id in number, -- current run.
386 p_owner_assignment_action_id in number, -- assact created balance.
387 p_user_assignment_action_id in number, -- current assact..
388 p_owner_effective_date in date, -- eff date of balance.
389 p_user_effective_date in date, -- eff date of current run.
390 p_dimension_name in varchar2, -- balance dimension name.
391 p_expiry_information out NOCOPY number -- dimension expired flag.
392 ) is
393 l_tax_year_start date;
394 l_user_payroll_id number;
395 begin
396 /* select the start of the financial year - if the owning action is
397 * before this or for a different payroll then its expired
398 */
399 Select to_date('01-01-' || to_char((fnd_number.canonical_to_number(
400 to_char( BACT.effective_date,'YYYY')) - 1)
401 + decode(sign( BACT.effective_date - to_date('01-01-'
402 || to_char(BACT.effective_date,'YYYY'),'DD-MM-YYYY')),
403 -1,-1,0)),'DD-MM-YYYY') finyear, BACT.payroll_id
404 into l_tax_year_start, l_user_payroll_id
405 from pay_payroll_actions BACT
406 where BACT.payroll_action_id = p_user_payroll_action_id;
407 --
408 --
409 if p_owner_effective_date < l_tax_year_start then
410 p_expiry_information := 1;
411 else
412 p_expiry_information := 0;
413 end if;
414 --
415
416 end ASG_TWO_YTD_EC;
417
418 /* 8522294 */
419
420 procedure ASG_TWO_YTD_EC
421 (
422 p_owner_payroll_action_id in number, -- run created balance.
423 p_user_payroll_action_id in number, -- current run.
424 p_owner_assignment_action_id in number, -- assact created balance.
425 p_user_assignment_action_id in number, -- current assact..
426 p_owner_effective_date in date, -- eff date of balance.
427 p_user_effective_date in date, -- eff date of current run.
428 p_dimension_name in varchar2, -- balance dimension name.
429 p_expiry_information out NOCOPY date -- dimension expired flag.
430 ) is
431 l_tax_year_start date;
432 l_user_payroll_id number;
433 begin
434 hr_utility.set_location('k:p_owner_payroll_action_id ' || p_owner_payroll_action_id,10);
435 hr_utility.set_location('k:p_user_payroll_action_id ' || p_user_payroll_action_id,20);
436 hr_utility.set_location('k:p_owner_assignment_action_id ' || p_owner_assignment_action_id,30);
437 hr_utility.set_location('k:p_user_assignment_action_id ' || p_user_assignment_action_id,40);
438 hr_utility.set_location('k:p_user_effective_date ' || to_char(p_user_effective_date,'dd-mon-yyyy'),50);
439 hr_utility.set_location('k:p_owner_effective_date ' || to_char(p_owner_effective_date,'dd-mon-yyyy'),60);
440 hr_utility.set_location('k:p_dimension_name ' || p_dimension_name,70);
441 p_expiry_information := next_two_year(p_owner_effective_date) -1;
442
443 hr_utility.set_location('k:p_expiry_information ' || to_char(p_expiry_information,'dd-mon-yyyy'),70);
444
445 end ASG_TWO_YTD_EC;
446 ----------------------------------------------------------------------------
447 -- Procedure: PROC_TWO_YTD_START
448 -- Description: used by TWO_YTD Dimensions for Run Level Balances only.
449 -- This procedure accepts a date and assignment action and other
450 -- params, and returns the start date of the Previous Tax Year, depending
451 -- on the regular payment date of the payroll action.
452
453 procedure proc_two_ytd_start(p_period_type in varchar2 default null,
454 p_effective_date in date default null,
455 p_start_date out nocopy date,
456 p_start_date_code in varchar2 default null,
457 p_payroll_id in number,
458 p_bus_grp in number default null,
459 p_action_type in varchar2 default null,
460 p_asg_action in number)
461 is
462 l_tax_year_start date;
463 begin
464 select to_date('01-01-' || to_char(( fnd_number.canonical_to_number(
465 to_char( PPA.effective_date,'YYYY'))- 1) -- Bug 5070091 Offset payroll change
466 + decode(sign( PPA.effective_date - to_date('01-01-'
467 || to_char(PPA.effective_date,'YYYY'),'DD-MM-YYYY')),
468 -1,-1,0)),'DD-MM-YYYY') finyear
469 into l_tax_year_start
470 from --per_time_periods PTP,
471 pay_payroll_actions ppa,
472 pay_assignment_actions paa
473 where ppa.payroll_action_id = paa.payroll_action_id
474 and paa.assignment_action_id = p_asg_action
475 and ppa.payroll_id = p_payroll_id;
476 -- and PTP.time_period_id = ppa.time_period_id;
477 --
478 p_start_date := l_tax_year_start;
479 --
480 end proc_two_ytd_start;
481 ----------------------------------------------------------------------------
482
483
484 end pyieexc;
[Home] [Help]