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