1 package body pay_nz_exc as
2 /* $Header: pynzexc.pkb 120.11 2011/03/01 07:50:04 jmarupil noship $ */
3 --
4 -- Change List
5 -- ----------
6 -- DATE Name Vers Bug No Description
7 -- -----------+---------------+--------+--------+-----------------------+
8 -- 13-Aug-1999 sclarke 1.0 Created
9 -- 03-Dec-2002 srrajago 1.1 2689221 Included 'nocopy' option for the 'out'
10 -- parameters of all the procedures,dbdrv
11 -- and checkfile commands.
12 -- 22-Jul-2003 puchil 1.2 3004603 Added overloaded function to populate
13 -- latest balances for balance adjustment
14 -- correctly
15 -- 05-Aug-2003 puchil 1.3 3062941 Changed the package name from pynzexc
16 -- to pay_nz_exc
17 -- 10-Aug-2004 sshankar 1.4 3181581 Changed function ASG_SPAN_EC to use
18 -- effective_date instead of
19 -- regular_payment_date and removed function
20 -- ASG_PTD_EC, expiry checking code for
21 -- _ASG_PTD.
22 -- 11-Aug-2004 sshankar 1.5 3181581 Removed gscc warnings.
23 -- 16-Nov-2004 snekkala 1.6 3828575 Added Start Date Code Procedures
24 -- 25-Nov-2004 snekkala 1.7 3998117 Changed the p_expiry_information
25 -- Values in asg_span_ec
26 -- 27-Nov-2004 snekkala 1.8 3998117 Changed p_user_effective_date to
27 -- p_owncer_effective_date and reset
28 -- p_expiry_information in asg_span_ec
29 -- 01-Aug-2005 snekkala 1.9 4259438 Modified cursor csr_get_business_group
30 -- as part of performance
31 -- 12-Apr-2007 dduvvuri 1.10 5846247 Added Procedure start_code_11mths_prev
32 -- for KiwiSaver Statutory Requirement from
33 -- 1st Jul 2007
34 -- 27-Apr-2007 dduvvuri 1.11 5846247 Changed procedure start_code_11mths_prev.
35 -- Changed Tab characters to spaces
36 -- 02-AUG-2007 dduvvuri 1.12 6263808 Added Expiry Check for Dimension _ASG_TD in procedure asg_span_ec
37 -- 30-Jul-2008 avenkatk 1.14 7260523 Modified Start Date returned by start_code_12mths_prev.
38 -- 03-Feb-2009 dduvvuri 1.15 7509317 Modified Start Date returned by start_code_11mths_prev
39 -- 28-Feb-2011 jmarupil 1.16 11062054 Added Procedure start_code_52weeks_prev for statutory update
40 -- -----------+---------------+--------+--------+-----------------------+
41 --
42 --
43 g_nz_fin_year_start constant varchar2(6) := '01-04-';
44 --
45 --
46 -------------------------------- asg_span_ec -----------------------------------------------
47 --
48 -- name
49 -- asg_span_ec - assignment processing year to date expiry check.
50 -- description
51 -- expiry checking code for the following:
52 -- nz assignment-level process year to date balance dimension
53 -- notes
54 -- the associated dimension is expiry checked at payroll action level
55 --
56 procedure asg_span_ec
57 ( p_owner_payroll_action_id in number -- run created balance.
58 , p_user_payroll_action_id in number -- current run.
59 , p_owner_assignment_action_id in number -- assact created balance.
60 , p_user_assignment_action_id in number -- current assact.
61 , p_owner_effective_date in date -- eff date of balance.
62 , p_user_effective_date in date -- eff date of current run.
63 , p_dimension_name in varchar2 -- balance dimension name.
64 , p_expiry_information out nocopy number -- dimension expired flag.
65 ) is
66 --
67 CURSOR csr_get_business_group
68 IS
69 SELECT ppa.business_group_id
70 FROM pay_assignment_actions paa
71 , pay_payroll_actions ppa
72 WHERE paa.assignment_action_id = p_user_assignment_action_id
73 AND ppa.payroll_action_id = paa.payroll_action_id;
74
75 --
76 -- Bug 3181581
77 -- Removed cursors csr_user_span_start and csr_owner_start
78 --
79
80 --
81 l_user_span_start date;
82 l_owner_start date;
83 l_date_dd_mm varchar2(11);
84 l_fy_user_span_start date;
85 l_frequency number;
86 l_dimension_name pay_balance_dimensions.dimension_name%type ;
87 l_business_group_id pay_payroll_actions.business_group_id%type;
88 --
89 begin
90
91 l_dimension_name := upper(p_dimension_name);
92
93 --
94 -- select the start span for the using action.
95 -- if the owning action associated with the latest balance, is
96 -- before the start of the span for the using regular payment date
97 -- then it has expired.
98 --
99 if lower(l_dimension_name) = '_asg_td' then -- Added for bug 6263808
100 p_expiry_information := 0;
101 RETURN;
102 elsif lower(l_dimension_name) = '_asg_ytd' then
103 --
104 l_frequency := 1;
105 l_date_dd_mm := g_nz_fin_year_start;
106 --
107 elsif lower(l_dimension_name) in ('_asg_fy_ytd','_asg_fy_qtd') then
108 --
109 open csr_get_business_group;
110 fetch csr_get_business_group into l_business_group_id;
111 close csr_get_business_group;
112 --
113 l_fy_user_span_start := hr_nz_routes.get_fiscal_date( l_business_group_id);
114 --
115 if lower(l_dimension_name) = '_asg_fy_ytd' then
116 l_frequency := 1;
117 else
118 l_frequency := 4;
119 end if;
120 --
121 l_date_dd_mm := to_char(l_fy_user_span_start,'dd-mm-');
122 --
123 elsif lower(l_dimension_name) = '_asg_hol_ytd' then
124 --
125 l_frequency := 1;
126 l_date_dd_mm := to_char(hr_nz_routes.get_anniversary_date ( p_user_assignment_action_id
127 , p_user_effective_date), 'dd-mm-');
128 --
129 end if;
130 --
131
132 --
133 -- Bug 3181581
134 -- Changed to use effective date instead of regular_payment_date
135 --
136 l_user_span_start := hr_nz_routes.span_start(p_user_effective_date
137 ,l_frequency
138 ,l_date_dd_mm);
139 if p_owner_effective_date < l_user_span_start then
140 --
141 p_expiry_information := 1;
142 else
143 --
144 p_expiry_information := 0;
145 --
146 end if;
147 --
148 -- Bug 3181581
149 -- End
150 --
151 end asg_span_ec;
152 --
153
154 /*
155 * Bug 3004603 - Overloaded procedures added to prevent loss of latest balances.
156 * Also added supporting functions.
157 */
158 -------------------------------- next_period ------------------------------------
159 /*
160 * NAME : next_period
161 * DESCRIPTION : Given a date and a payroll action id, returns the date after the
162 * end of the containing payroll action id's pay period.
163 */
164 --
165 function next_period
166 ( p_payroll_action_id in number
167 , p_given_date in date )
168 RETURN date is
169 l_next_to_end_date date := NULL;
170
171 /* Get the date next to the end date of the given period,
172 having the payroll action id */
173 cursor csr_end_date is
174 select PTP.end_date
175 from per_time_periods ptp,
176 pay_payroll_actions pact
177 where pact.payroll_action_id = p_payroll_action_id
178 and pact.payroll_id = ptp.payroll_id
179 and p_given_date between ptp.start_date and ptp.end_date;
180
181 begin
182 open csr_end_date;
183 fetch csr_end_date into l_next_to_end_date;
184 close csr_end_date;
185
186 return l_next_to_end_date;
187 end next_period;
188 ------------------------------- next_quarter --------------------------------------------------
189 /*
190 * NAME : next_quarter
191 * DESCRIPTION : Given a date returns the next quarter's start date.
192 */
193 --
194 function next_quarter
195 (p_given_date in date)
196 RETURN date is
197 begin
198 /* Return the next quarter's start date */
199 RETURN trunc(add_months(p_given_date,3),'Q');
200 end next_quarter;
201
202 ------------------------------ next_year -----------------------------------------------------
203 /*
204 * NAME : next_year
205 * DESCRIPTION : Given a date returns the next year's start date.
206 */
207 --
208 function next_year
209 (p_given_date in date)
210 RETURN date is
211 begin
212 /* Return the next year's start date */
213 RETURN trunc(add_months(p_given_date,12),'Y');
214 end next_year;
215
216 ------------------------------- next_fin_quarter -------------------------------------------
217 /*
218 * NAME : next_fin_quarter
219 * DESCRIPTION : Given a date returns the next fiscal quarter's start date.
220 */
221 --
222 function next_fin_quarter
223 ( p_beg_of_the_year in date
224 , p_given_date in date )
225 RETURN date is
226
227 -- get offset of fin year start with reference to calender year in months and days
228 l_finyr_months_offset NUMBER(2) ;
229 l_finyr_days_offset NUMBER(2) ;
230
231 begin
232
233 l_finyr_months_offset := to_char(p_beg_of_the_year,'MM') - 1;
234 l_finyr_days_offset := to_char(p_beg_of_the_year,'DD') - 1;
235
236 /* Return the next fiscal quarter's start date */
237 RETURN (add_months(next_quarter(add_months(p_given_date,-l_finyr_months_offset)
238 -l_finyr_days_offset),l_finyr_months_offset)+ l_finyr_days_offset);
239 end next_fin_quarter;
240
241 ------------------------------- next_fin_year -------------------------------------------
242 /*
243 * NAME : next_fin_year
244 * DESCRIPTION : Given a date returns the next fiscal quarter's start date.
245 */
246 --
247 function next_fin_year
248 ( p_beg_of_the_year in date
249 , p_given_date in date )
250 RETURN date is
251
252 -- get offset of fin year start with reference to calender year in months and days
253 l_finyr_months_offset NUMBER(2);
254 l_finyr_days_offset NUMBER(2);
255
256 begin
257 l_finyr_months_offset := to_char(p_beg_of_the_year,'MM') - 1;
258 l_finyr_days_offset := to_char(p_beg_of_the_year,'DD') - 1;
259
260 /* Return the next fiscal quarter's start date */
261 RETURN (add_months(next_year(add_months(p_given_date,-l_finyr_months_offset)
262 -l_finyr_days_offset),l_finyr_months_offset)+ l_finyr_days_offset);
263 end next_fin_year;
264 --
265 ---------------------------- Overloaded asg_span_ec ------------------------------------
266 /*
267 *
268 * name
269 * asg_span_ec - assignment processing span to date expiry check.
270 * description
271 * Overloaded expiry checking code for the following:
272 * nz assignment-level process year to date balance dimension
273 * nz assignment-level process fiscal year to date balance dimension
274 * nz assignment-level process fiscal quarter to date balance dimension
275 * nz assignment-level process holiday year to date balance dimension
276 * notes
277 * the associated dimension is expiry checked at assignment action level
278 */
279 --
280 procedure asg_span_ec
281 ( p_owner_payroll_action_id in number -- run created balance.
282 , p_user_payroll_action_id in number -- current run.
283 , p_owner_assignment_action_id in number -- assact created balance.
284 , p_user_assignment_action_id in number -- current assact.
285 , p_owner_effective_date in date -- eff date of balance.
286 , p_user_effective_date in date -- eff date of current run.
287 , p_dimension_name in varchar2 -- balance dimension name.
288 , p_expiry_information out nocopy date -- dimension expired flag.
289 ) is
290 l_beg_of_fiscal_year date;
291 l_dimension_name pay_balance_dimensions.dimension_name%type ;
292
293 cursor get_beg_of_fiscal_year(c_owner_payroll_action_id number) is
294 select fnd_date.canonical_to_date(org_information11)
295 from pay_payroll_actions PACT,
296 hr_organization_information HOI
297 where UPPER(HOI.org_information_context) = 'BUSINESS GROUP INFORMATION'
298 and HOI.organization_id = PACT.business_group_id
299 and PACT.payroll_action_id = c_owner_payroll_action_id;
300 begin
301 l_dimension_name := upper(p_dimension_name);
302
303 IF l_dimension_name = '_ASG_TD' THEN --Added check for bug 6263808
304 p_expiry_information := fnd_date.canonical_to_date('4712/12/31'); --Added check for bug 6263808
305 elsif l_dimension_name = '_ASG_YTD' then
306 p_expiry_information := next_fin_year(to_date(g_nz_fin_year_start,'DD-MM-'), p_owner_effective_date)-1;
307 elsif l_dimension_name = '_ASG_FY_QTD' then
308 open get_beg_of_fiscal_year(p_owner_payroll_action_id);
309 fetch get_beg_of_fiscal_year into l_beg_of_fiscal_year;
310 close get_beg_of_fiscal_year;
311
312 p_expiry_information := next_fin_quarter(l_beg_of_fiscal_year, p_owner_effective_date)-1;
313 elsif l_dimension_name = '_ASG_FY_YTD' then
314 open get_beg_of_fiscal_year(p_owner_payroll_action_id);
315 fetch get_beg_of_fiscal_year into l_beg_of_fiscal_year;
316 close get_beg_of_fiscal_year;
317
318 p_expiry_information := next_fin_year(l_beg_of_fiscal_year, p_owner_effective_date)-1;
319 end if;
320 end asg_span_ec;
321 --
322 --------------------------------------------------------------------------
323 -- --
324 -- Name : START_CODE_4WEEK --
325 -- Type : PROCEDURE --
326 -- Access : Public --
327 -- Description : This procedure finds the start date based on the --
328 -- effective date for the dimension name _ASG_4WEEK --
329 -- --
330 -- Parameters : --
331 -- IN : p_effective_date DATE --
332 -- p_payroll_id NUMBER --
333 -- p_bus_grp NUMBER --
334 -- p_asg_action NUMBER --
335 -- OUT : p_start_date DATE --
336 -- RETURN : N/A --
337 -- --
338 -- Change History : --
339 --------------------------------------------------------------------------
340 -- Rev# Date Userid Description --
341 --------------------------------------------------------------------------
342 -- 1.0 16-Nov-2004 snekkala Created the procedure --
343 --------------------------------------------------------------------------
344 PROCEDURE start_code_4week( p_effective_date IN DATE
345 , p_start_date OUT NOCOPY DATE
346 , p_payroll_id IN NUMBER
347 , p_bus_grp IN NUMBER
348 , p_asg_action IN NUMBER
349 )
350 IS
351 BEGIN
352 p_start_date := p_effective_date - 28;
353 END start_code_4week;
354
355 --------------------------------------------------------------------------
356 -- --
357 -- Name : START_CODE_4WEEKS_PREV --
358 -- Type : PROCEDURE --
362 -- --
359 -- Access : Public --
360 -- Description : This procedure finds the start date based on the --
361 -- effective date for dimension _ASG_4WEEKS_PREV --
363 -- Parameters : --
364 -- IN : p_effective_date DATE --
365 -- p_payroll_id NUMBER --
366 -- p_bus_grp NUMBER --
367 -- p_asg_action NUMBER --
368 -- OUT : p_start_date DATE --
369 -- RETURN : N/A --
370 -- --
371 -- Change History : --
372 --------------------------------------------------------------------------
373 -- Rev# Date Userid Description --
374 --------------------------------------------------------------------------
375 -- 1.0 16-Nov-2004 snekkala Created the procedure --
376 --------------------------------------------------------------------------
377 PROCEDURE start_code_4weeks_prev( p_effective_date IN DATE
378 , p_start_date OUT NOCOPY DATE
379 , p_payroll_id IN NUMBER
380 , p_bus_grp IN NUMBER
381 , p_asg_action IN NUMBER
382 )
383 IS
384 CURSOR csr_get_start_date
385 IS
386 SELECT ptp.start_date - 28
387 FROM per_time_periods ptp
388 WHERE ptp.payroll_id = p_payroll_id
389 AND p_effective_date between ptp.start_date
390 and ptp.end_date;
391 BEGIN
392
393 OPEN csr_get_start_date;
394 FETCH csr_get_start_date INTO p_start_date;
395 CLOSE csr_get_start_date;
396
397 END start_code_4weeks_prev;
398
399 --------------------------------------------------------------------------
400 -- --
401 -- Name : START_CODE_HOL_YTD --
402 -- Type : PROCEDURE --
403 -- Access : Public --
404 -- Description : This procedure finds the start date based on the --
405 -- effective date for the dimension name _ASG_HOL_YTD --
406 -- --
407 -- Parameters : --
408 -- IN : p_effective_date DATE --
409 -- p_payroll_id NUMBER --
410 -- p_bus_grp NUMBER --
411 -- p_asg_action NUMBER --
412 -- OUT : p_start_date DATE --
413 -- RETURN : N/A --
414 -- --
415 -- Change History : --
416 --------------------------------------------------------------------------
417 -- Rev# Date Userid Description --
418 --------------------------------------------------------------------------
419 -- 1.0 16-Nov-2004 snekkala Created the procedure --
420 --------------------------------------------------------------------------
421 PROCEDURE start_code_hol_ytd( p_effective_date IN DATE
422 , p_start_date OUT NOCOPY DATE
423 , p_payroll_id IN NUMBER
424 , p_bus_grp IN NUMBER
425 , p_asg_action IN NUMBER
426 )
427 IS
428 BEGIN
429 p_start_date:= hr_nz_routes.anniversary_span_start(
430 p_asg_action
431 ,p_effective_date);
432
433 END start_code_hol_ytd;
434
435 --------------------------------------------------------------------------
436 -- --
437 -- Name : START_CODE_12MTHS_PREV --
438 -- Type : PROCEDURE --
439 -- Access : Public --
440 -- Description : This procedure finds the start date based on the --
441 -- effective date for dimension _ASG_12MTHS_PREV --
442 -- --
443 -- Parameters : --
444 -- IN : p_effective_date DATE --
445 -- p_payroll_id NUMBER --
449 -- RETURN : N/A --
446 -- p_bus_grp NUMBER --
447 -- p_asg_action NUMBER --
448 -- OUT : p_start_date DATE --
450 -- --
451 -- Change History : --
452 --------------------------------------------------------------------------
453 -- Rev# Date Userid Description --
454 --------------------------------------------------------------------------
455 -- 1.0 16-Nov-2004 snekkala Created the procedure --
456 -- 2.0 30-Jul-2008 avenkatk Modified the Start Date
457 --------------------------------------------------------------------------
458 PROCEDURE start_code_12mths_prev( p_effective_date IN DATE
459 , p_start_date OUT NOCOPY DATE
460 , p_payroll_id IN NUMBER
461 , p_bus_grp IN NUMBER
462 , p_asg_action IN NUMBER
463 )
464 IS
465 BEGIN
466 p_start_date := last_day(add_months(p_effective_date,-14)) + 1;
467 END start_code_12mths_prev;
468
469 --------------------------------------------------------------------------
470 -- --
471 -- Name : START_CODE_11MTHS_PREV --
472 -- Type : PROCEDURE --
473 -- Access : Public --
474 -- Description : This procedure finds the start date based on the --
475 -- effective date for dimension _ASG_11MTHS_PREV --
476 -- --
477 -- Parameters : --
478 -- IN : p_effective_date DATE --
479 -- p_payroll_id NUMBER --
480 -- p_bus_grp NUMBER --
481 -- p_asg_action NUMBER --
482 -- OUT : p_start_date DATE --
483 -- RETURN : N/A --
484 -- --
485 -- Change History : --
486 --------------------------------------------------------------------------
487 -- Rev# Date Userid Description --
488 --------------------------------------------------------------------------
489 -- 1.0 12-Apr-2007 dduvvuri Created the procedure --
490 -- 1.1 27-Apr-2007 dduvvuri Changed the code --
491 -- 1.2 03-Feb-2009 dduvvuri Changed the code for bug 7509317
492 -- 1.3 03-Feb-2009 dduvvuri Reverted the changes done in previous version
493 --------------------------------------------------------------------------
494 PROCEDURE start_code_11mths_prev( p_effective_date IN DATE
495 , p_start_date OUT NOCOPY DATE
496 , p_payroll_id IN NUMBER
497 , p_bus_grp IN NUMBER
498 , p_asg_action IN NUMBER
499 )
500 IS
501 BEGIN
502
503 p_start_date := last_day(add_months(p_effective_date,-12))+1;
504
505 END start_code_11mths_prev;
506
507 --------------------------------------------------------------------------
508 -- --
509 -- Name : START_CODE_52WEEKS_PREV --
510 -- Type : PROCEDURE --
511 -- Access : Public --
512 -- Description : This procedure finds the start date based on the --
513 -- effective date for dimension _ASG_52WEEKS_PREV --
514 -- --
515 -- Parameters : --
516 -- IN : p_effective_date DATE --
517 -- p_payroll_id NUMBER --
518 -- p_bus_grp NUMBER --
519 -- p_asg_action NUMBER --
520 -- OUT : p_start_date DATE --
521 -- RETURN : N/A --
522 -- --
523 -- Change History : --
524 --------------------------------------------------------------------------
525 -- Rev# Date Userid Description --
526 --------------------------------------------------------------------------
527 -- 1.0 28-Feb-2011 jmarupil Created the procedure --
528 --------------------------------------------------------------------------
529 PROCEDURE start_code_52weeks_prev( p_effective_date IN DATE
530 , p_start_date OUT NOCOPY DATE
531 , p_payroll_id IN NUMBER
532 , p_bus_grp IN NUMBER
533 , p_asg_action IN NUMBER
534 )
535 IS
536 CURSOR csr_get_start_date
537 IS
538 SELECT ptp.start_date - 364
539 FROM per_time_periods ptp
540 WHERE ptp.payroll_id = p_payroll_id
541 AND p_effective_date between ptp.start_date
542 and ptp.end_date;
543 BEGIN
544
545 OPEN csr_get_start_date;
546 FETCH csr_get_start_date INTO p_start_date;
547 CLOSE csr_get_start_date;
548
549 END start_code_52weeks_prev;
550 --
551 end pay_nz_exc;
[Home] [Help]