1 PACKAGE BODY pay_hk_exc AS
2 /* $Header: pyhkexch.pkb 120.1 2007/11/16 10:35:48 vamittal noship $ */
3 /*
4 PRODUCT
5 Oracle*Payroll
6 NAME
7 pyhkexch.pkb - PaYroll HK legislation EXpiry Checking code.
8 DESCRIPTION
9 Contains the expiry checking code associated with the HK
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 21/09/00 - first created. Based on PAYSGEXC (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_ri_period -----------------------------------
53 NAME
54 next_ri_period
55 DESCRIPTION
56 Given a payroll action id, returns the date of the day after
57 the end of the containing pay period.
58 NOTES
59 <none>
60 */
61 FUNCTION next_ri_period
62 (
63 p_pactid IN NUMBER
64 ) RETURN DATE is
65 l_return_val DATE := NULL;
66 BEGIN
67 select TP.end_date + 1
68 into l_return_val
69 from per_time_periods TP,
70 pay_payroll_actions PACT
71 where PACT.payroll_action_id = p_pactid
72 and PACT.payroll_id = TP.payroll_id
73 and PACT.date_earned between TP.start_date and TP.end_date;
74
75 RETURN l_return_val;
76
77 END next_ri_period;
78
79 /*---------------------------- next_month ------------------------------------
80 NAME
81 next_month
82 DESCRIPTION
83 Given a date, returns the date of the first day of the next month.
84 NOTES
85 <none>
86 */
87 FUNCTION next_month
88 (
89 p_date IN DATE
90 ) return DATE is
91 BEGIN
92
93 RETURN trunc(add_months(p_date,1),'MM');
94
95 END next_month;
96
97 /*---------------------------- next_ri_month ------------------------------------
98 NAME
99 next_ri_month
100 DESCRIPTION
101 Given a date, returns the date of the first day of the next month.
102 NOTES
103 <none>
104 */
105 FUNCTION next_ri_month
106 (
107 p_pactid IN NUMBER
108 ) RETURN DATE is
109 l_return_val DATE := NULL;
110 BEGIN
111 select trunc(add_months(PACT.date_earned,1),'MM')
112 into l_return_val
113 from pay_payroll_actions PACT
114 where PACT.payroll_action_id = p_pactid;
115
116 RETURN l_return_val;
117
118 END next_ri_month;
119
120 /*--------------------------- next_quarter -----------------------------------
121 NAME
122 next_quarter
123 DESCRIPTION
124 Given a date, returns the date of the first day of the next calendar
125 quarter.
126 NOTES
127 <none>
128 */
129 FUNCTION next_quarter
130 (
131 p_date IN DATE
132 ) RETURN DATE is
133 BEGIN
134
135 RETURN trunc(add_months(p_date,3),'Q');
136
137 END next_quarter;
138
139 /*--------------------------- next_ri_quarter -----------------------------------
140 NAME
141 next_ri_quarter
142 DESCRIPTION
143 Given a date, returns the date of the first day of the next calendar
144 quarter.
145 NOTES
146 <none>
147 */
148 FUNCTION next_ri_quarter
149 (
150 p_pactid IN NUMBER
151 ) RETURN DATE is
152 l_return_val DATE := NULL;
153 BEGIN
154 select trunc(add_months(PACT.date_earned,3),'Q')
155 into l_return_val
156 from pay_payroll_actions PACT
157 where PACT.payroll_action_id = p_pactid;
158
159 RETURN l_return_val;
160
161 END next_ri_quarter;
162
163 /*---------------------------- next_calendar_year ------------------------------------
164 NAME
165 next_calendar_year
166 DESCRIPTION
167 Given a date, returns the date of the first day of the next calendar
168 year.
169 NOTES
170 <none>
171 */
172 FUNCTION next_calendar_year
173 (
174 p_date IN DATE
175 ) RETURN DATE is
176 BEGIN
177
178 RETURN trunc(add_months(p_date,12),'Y');
179
180 END next_calendar_year;
181
182 /*------------------------- next_fiscal_quarter -----------------------------
183 NAME
184 next_fiscal_quarter
185 DESCRIPTION
186 Given a date, returns the date of the first day of the next fiscal
187 quarter.
188 NOTES
189 <none>
190 */
191 FUNCTION next_fiscal_quarter
192 (
193 p_beg_of_fiscal_year IN DATE,
194 p_date IN DATE
195 ) RETURN DATE is
196
197 BEGIN
198
199 RETURN (add_months(p_beg_of_fiscal_year, 3*(ceil(months_between(p_date+1,p_beg_of_fiscal_year)/3))));
200
201 END next_fiscal_quarter;
202
203 /*--------------------------- next_fiscal_year ------------------------------
204 NAME
205 next_fiscal_year
206 DESCRIPTION
207 Given a date, returns the date of the first day of the next fiscal year.
208 NOTES
209 <none>
210 */
211 FUNCTION next_fiscal_year
212 (
213 p_beg_of_fiscal_year IN DATE,
214 p_date IN DATE
215 ) RETURN DATE is
216
217 BEGIN
218
219 RETURN (add_months(p_beg_of_fiscal_year, 12*(ceil(months_between(p_date+1,p_beg_of_fiscal_year)/12))));
220
221 END next_fiscal_year;
222 /*------------------------------ date_ec ------------------------------------
223 NAME
224 date_ec
225 DESCRIPTION
226 Expiry checking code for the Hong Kong dimensions:
227 NOTES
228 This procedure assumes the date portion of the dimension name
229 is always at the end to allow accurate identification since
230 this is used for many dimensions.
231 */
232 PROCEDURE date_ec
233 (
234 p_owner_payroll_action_id in number, -- run created balance.
235 p_user_payroll_action_id in number, -- current run.
236 p_owner_assignment_action_id in number, -- assact created balance.
237 p_user_assignment_action_id in number, -- current assact..
238 p_owner_effective_date in date, -- eff date of balance.
239 p_user_effective_date in date, -- eff date of current run.
240 p_dimension_name in varchar2, -- balance dimension name.
241 p_expiry_information out nocopy number -- dimension expired flag.
242 ) is
243
244 l_beg_of_fiscal_year DATE := NULL;
245 l_expiry_date DATE := NULL;
246
247 BEGIN
248
249 IF p_dimension_name like '%RUN' THEN
250 -- must check for special case: if payroll action id's are the same,
251 -- then don't expire. This facilitates meaningful access of these
252 -- balances outside of runs.
253 IF p_owner_payroll_action_id <> p_user_payroll_action_id THEN
254 l_expiry_date := p_user_effective_date; -- always must expire.
255 ELSE
256 p_expiry_information := 0;
257 RETURN;
258 END IF;
259
260 ELSIF p_dimension_name like '%PAYMENTS' THEN
261 -- must check for special case: if payroll action id's are the same,
262 -- then don't expire. This facilitates meaningful access of these
263 -- balances outside of runs.
264 IF p_owner_payroll_action_id <> p_user_payroll_action_id THEN
265 l_expiry_date := p_user_effective_date; -- always must expire.
266 ELSE
267 p_expiry_information := 0;
268 RETURN;
269 END IF;
270
271 ELSIF p_dimension_name like '%MPF_PTD' THEN
272 l_expiry_date := next_ri_period(p_owner_payroll_action_id);
273
274 ELSIF p_dimension_name like '%MPF_MONTH' THEN
275 l_expiry_date := next_ri_month(p_owner_payroll_action_id);
276
277 ELSIF p_dimension_name like '%MPF_QTD' THEN
278 l_expiry_date := next_ri_quarter(p_owner_payroll_action_id);
279
280 ELSIF p_dimension_name like '%MPF_YTD' THEN
281 SELECT to_date('01-04-'||to_char(fnd_number.canonical_to_number(
282 to_char(PACT.date_earned,'YYYY'))+ decode(sign(PACT.date_earned
283 - to_date('01-04-'||to_char(PACT.date_earned,'YYYY'),'DD-MM-YYYY'))
284 ,-1,0,1)),'DD-MM-YYYY')
285 INTO l_expiry_date
286 FROM pay_payroll_actions PACT
287 WHERE PACT.payroll_action_id = p_owner_payroll_action_id;
288
289 ELSIF p_dimension_name like '%PTD' THEN
290 l_expiry_date := next_period(p_owner_payroll_action_id,
291 p_owner_effective_date);
292
293 ELSIF p_dimension_name like '%MONTH' THEN
294 l_expiry_date := next_month(p_owner_effective_date);
295
296 ELSIF p_dimension_name like '%FQTD' THEN
297 SELECT fnd_date.canonical_to_date(org_information11)
298 INTO l_beg_of_fiscal_year
299 FROM pay_payroll_actions PACT,
300 hr_organization_information HOI
301 WHERE UPPER(HOI.org_information_context) = 'BUSINESS GROUP INFORMATION'
302 AND HOI.organization_id = PACT.business_group_id
303 AND PACT.payroll_action_id = p_owner_payroll_action_id;
304
305 l_expiry_date := next_fiscal_quarter(l_beg_of_fiscal_year,
306 p_owner_effective_date);
307
308 ELSIF p_dimension_name like '%FYTD' THEN
309 SELECT fnd_date.canonical_to_date(org_information11)
310 INTO l_beg_of_fiscal_year
311 FROM pay_payroll_actions PACT,
312 hr_organization_information HOI
313 WHERE UPPER(HOI.org_information_context) = 'BUSINESS GROUP INFORMATION'
314 AND HOI.organization_id = PACT.business_group_id
315 AND PACT.payroll_action_id = p_owner_payroll_action_id;
316
317 l_expiry_date := next_fiscal_year(l_beg_of_fiscal_year,
318 p_owner_effective_date);
319
320 ELSIF p_dimension_name like '%_CAL_YTD' THEN
321 l_expiry_date := next_calendar_year(p_owner_effective_date);
322
323 ELSIF p_dimension_name like '%QTD' THEN
324 l_expiry_date := next_quarter(p_owner_effective_date);
325
326 ELSIF p_dimension_name like '%YTD' THEN
327 SELECT to_date('01-04-'||to_char(fnd_number.canonical_to_number(
328 to_char(PACT.effective_date,'YYYY'))+ decode(sign(PACT.effective_date
329 - to_date('01-04-'||to_char(PACT.effective_date,'YYYY'),'DD-MM-YYYY'))
330 ,-1,0,1)),'DD-MM-YYYY')
331 INTO l_expiry_date
332 FROM pay_payroll_actions PACT
333 WHERE PACT.payroll_action_id = p_owner_payroll_action_id;
334
335 ELSIF p_dimension_name like '%LTD' THEN
336 p_expiry_information := 0;
337 RETURN;
338
339 ELSE
340 hr_utility.set_message(801, 'NO_EXP_CHECK_FOR_DIMENSION');
341 hr_utility.raise_error;
342
343 END IF;
344
345 IF p_user_effective_date >= l_expiry_date THEN
346 p_expiry_information := 1;
347 ELSE
348 p_expiry_information := 0;
349 END IF;
350
351 END date_ec;
352
353
354 FUNCTION get_expiry_date
355 (
356 p_defined_balance_id in number, -- defined balance.
357 p_assignment_action_id in number -- assact created balance.
358 ) RETURN DATE is
359
360 l_dimension_name VARCHAR2(160);
361 l_payroll_action_id NUMBER;
362 l_effective_date DATE := NULL;
363 l_beg_of_fiscal_year DATE := NULL;
364 l_beg_of_tax_year DATE := NULL;
365 l_end_of_time CONSTANT DATE := to_date('31/12/4712','DD/MM/YYYY');
366
367 cursor dimension_name( c_defined_balance_id in number ) is
368 SELECT dimension_name
369 FROM pay_defined_balances pdb
370 , pay_balance_dimensions pbd
371 WHERE pdb.balance_dimension_id = pbd.balance_dimension_id
372 AND pdb.defined_balance_id = c_defined_balance_id;
373
374 cursor pact_effective_date( c_assignment_action_id in number ) is
375 SELECT ppa.payroll_action_id, ppa.effective_date
376 FROM pay_payroll_actions ppa, pay_assignment_actions paa
377 WHERE ppa.payroll_action_id = paa.payroll_action_id
378 AND paa.assignment_action_id = c_assignment_action_id;
379
380 BEGIN
381 --
382 open dimension_name ( p_defined_balance_id );
383 fetch dimension_name into l_dimension_name;
384 close dimension_name;
385 --
386 open pact_effective_date ( p_assignment_action_id );
387 fetch pact_effective_date into l_payroll_action_id, l_effective_date;
388 close pact_effective_date;
389 --
390 IF l_dimension_name like '%RUN' THEN
391 -- must check for special case: Will always expire on date of run.
392 RETURN l_effective_date; -- always must expire.
393
394 ELSIF l_dimension_name like '%PAYMENTS' THEN
395 -- must check for special case: Will always expire on date of run.
396 RETURN l_effective_date; -- always must expire.
397
398 ELSIF l_dimension_name like '%MPF_PTD' THEN
399 -- this will expire at the end of the period
400 RETURN next_ri_period(l_payroll_action_id) - 1;
401
402 ELSIF l_dimension_name like '%MPF_MONTH' THEN
403 -- this will expire at the end of the month
404 RETURN next_ri_month(l_payroll_action_id) - 1;
405
406 ELSIF l_dimension_name like '%MPF_QTD' THEN
407 RETURN next_ri_quarter(l_payroll_action_id) - 1;
408
409 ELSIF l_dimension_name like '%MPF_YTD' THEN
410 SELECT to_date('01-04-'||to_char(fnd_number.canonical_to_number(
411 to_char(PACT.date_earned,'YYYY'))+ decode(sign(PACT.date_earned
412 - to_date('01-04-'||to_char(PACT.date_earned,'YYYY'),'DD-MM-YYYY'))
413 ,-1,0,1)),'DD-MM-YYYY')
414 INTO l_beg_of_tax_year
415 FROM pay_payroll_actions PACT
416 WHERE PACT.payroll_action_id = l_payroll_action_id;
417
418 RETURN l_beg_of_tax_year - 1;
419
420 ELSIF l_dimension_name like '%PTD' THEN
421 -- this will expire at the end of the period
422 RETURN next_period(l_payroll_action_id, l_effective_date) - 1;
423
424 ELSIF l_dimension_name like '%MONTH' THEN
425 -- this will expire at the end of the month
426 RETURN next_month(l_effective_date) - 1;
427
428 ELSIF l_dimension_name like '%FQTD' THEN
429 SELECT fnd_date.canonical_to_date(org_information11)
430 INTO l_beg_of_fiscal_year
431 FROM pay_payroll_actions PACT,
432 hr_organization_information HOI
433 WHERE UPPER(HOI.org_information_context) = 'BUSINESS GROUP INFORMATION'
434 AND HOI.organization_id = PACT.business_group_id
435 AND PACT.payroll_action_id = l_payroll_action_id;
436
437 RETURN next_fiscal_quarter(l_beg_of_fiscal_year, l_effective_date) - 1;
438
439 ELSIF l_dimension_name like '%FYTD' THEN
440 SELECT fnd_date.canonical_to_date(org_information11)
441 INTO l_beg_of_fiscal_year
442 FROM pay_payroll_actions PACT,
443 hr_organization_information HOI
444 WHERE UPPER(HOI.org_information_context) = 'BUSINESS GROUP INFORMATION'
445 AND HOI.organization_id = PACT.business_group_id
446 AND PACT.payroll_action_id = l_payroll_action_id;
447
448 RETURN next_fiscal_year(l_beg_of_fiscal_year, l_effective_date) - 1;
449
450 ELSIF l_dimension_name like '%_CAL_YTD' THEN
451 RETURN next_calendar_year(l_effective_date) - 1;
452
453 ELSIF l_dimension_name like '%QTD' THEN
454 RETURN next_quarter(l_effective_date) - 1;
455
456 ELSIF l_dimension_name like '%YTD' THEN
457 SELECT to_date('01-04-'||to_char(fnd_number.canonical_to_number(
458 to_char(PACT.effective_date,'YYYY'))+ decode(sign(PACT.effective_date
459 - to_date('01-04-'||to_char(PACT.effective_date,'YYYY'),'DD-MM-YYYY'))
460 ,-1,0,1)),'DD-MM-YYYY')
461 INTO l_beg_of_tax_year
462 FROM pay_payroll_actions PACT
463 WHERE PACT.payroll_action_id = l_payroll_action_id;
464
465 RETURN l_beg_of_tax_year - 1;
466
467 ELSIF l_dimension_name like '%LTD' THEN
468 RETURN l_end_of_time;
469
470 ELSE
471 hr_utility.set_message(801, 'NO_EXPIRY_DATE_FOR_DIMENSION');
472 hr_utility.raise_error;
473
474 END IF;
475
476 END get_expiry_date;
477
478
479 FUNCTION calculated_value
480 (
481 p_defined_balance_id in number, -- defined balance.
482 p_assignment_action_id in number, -- assact created balance.
483 p_tax_unit_id in number, -- tax_unit
484 p_source_id in number, -- source_id
485 p_session_date in date
486 ) RETURN NUMBER is
487
488 l_calculated_value NUMBER;
489 l_expiry_date DATE := NULL;
490
491 BEGIN
492
493 l_calculated_value := pay_balance_pkg.get_value(p_defined_balance_id
494 ,p_assignment_action_id
495 ,p_tax_unit_id
496 ,null -- jurisdiction
497 ,p_source_id
498 ,null -- tax_group
499 ,null -- effective_date
500 );
501
502 l_expiry_date := get_expiry_date(p_defined_balance_id, p_assignment_action_id);
503
504 IF p_session_date > l_expiry_date THEN
505 l_calculated_value := 0;
506 END IF;
507
508 RETURN l_calculated_value;
509
510 END calculated_value;
511
512
513 PROCEDURE date_ec
514 (
515 p_owner_payroll_action_id in number, -- run created balance.
516 p_user_payroll_action_id in number, -- current run.
517 p_owner_assignment_action_id in number, -- assact created balance.
518 p_user_assignment_action_id in number, -- current assact.
519 p_owner_effective_date in date, -- eff date of balance.
520 p_user_effective_date in date, -- eff date of current run.
521 p_dimension_name in varchar2, -- balance dimension name.
522 p_expiry_date out nocopy date -- dimension expired date.
523 ) is
524
525 l_dimension_name VARCHAR2(160);
526 l_payroll_action_id NUMBER;
527 l_effective_date DATE := NULL;
528 l_beg_of_fiscal_year DATE := NULL;
529 l_beg_of_tax_year DATE := NULL;
530 l_end_of_time CONSTANT DATE := to_date('31/12/4712','DD/MM/YYYY');
531
532 BEGIN
533
534 hr_utility.trace('Entered the procedure date_ec');
535 hr_utility.trace('p_owner_payroll_action_id ===>' || p_owner_payroll_action_id);
536 hr_utility.trace('p_user_payroll_action_id ===>' || p_user_payroll_action_id);
537 hr_utility.trace('p_owner_assignment_action_id ===>' || p_owner_assignment_action_id);
538 hr_utility.trace('p_user_assignment_action_id ===>' || p_user_assignment_action_id);
539 hr_utility.trace('p_owner_effective_date ===>' || p_owner_effective_date);
540 hr_utility.trace('p_user_effective_date ===>' || p_user_effective_date);
541 hr_utility.trace('p_dimension_name ===>' || p_dimension_name);
542
543 IF p_dimension_name like '%MPF_PTD' THEN
544 p_expiry_date := next_ri_period(p_owner_payroll_action_id) - 1;
545 hr_utility.trace('p_expiry_date ===>' || p_expiry_date);
546
547 ELSIF p_dimension_name like '%MPF_MONTH' THEN
548 p_expiry_date := next_ri_month(p_owner_payroll_action_id) - 1;
549 hr_utility.trace('p_expiry_date ===>' || p_expiry_date);
550
551 ELSIF p_dimension_name like '%MPF_QTD' THEN
552 p_expiry_date := next_ri_quarter(p_owner_payroll_action_id) - 1;
553 hr_utility.trace('p_expiry_date ===>' || p_expiry_date);
554
555 ELSIF p_dimension_name like '%MPF_YTD' THEN
556 SELECT to_date('01-04-'||to_char(fnd_number.canonical_to_number(
557 to_char(PACT.date_earned,'YYYY'))+ decode(sign(PACT.date_earned
558 - to_date('01-04-'||to_char(PACT.date_earned,'YYYY'),'DD-MM-YYYY'))
559 ,-1,0,1)),'DD-MM-YYYY') - 1
560 INTO p_expiry_date
561 FROM pay_payroll_actions PACT
562 WHERE PACT.payroll_action_id = p_owner_payroll_action_id;
563 hr_utility.trace('p_expiry_date ===>' || p_expiry_date);
564
565 ELSIF p_dimension_name like '%PTD' THEN
566 p_expiry_date := next_period(p_owner_payroll_action_id,p_owner_effective_date) - 1;
567 hr_utility.trace('p_expiry_date ===>' || p_expiry_date);
568
569 ELSIF p_dimension_name like '%MONTH' THEN
570 p_expiry_date := next_month(p_owner_effective_date) - 1;
571 hr_utility.trace('p_expiry_date ===>' || p_expiry_date);
572
573 ELSIF p_dimension_name like '%FQTD' THEN
574 SELECT fnd_date.canonical_to_date(org_information11)
575 INTO l_beg_of_fiscal_year
576 FROM pay_payroll_actions PACT,
577 hr_organization_information HOI
578 WHERE UPPER(HOI.org_information_context) = 'BUSINESS GROUP INFORMATION'
579 AND HOI.organization_id = PACT.business_group_id
580 AND PACT.payroll_action_id = p_owner_payroll_action_id;
581 p_expiry_date := next_fiscal_quarter(l_beg_of_fiscal_year,p_owner_effective_date) - 1;
582 hr_utility.trace('p_expiry_date ===>' || p_expiry_date);
583
584 ELSIF p_dimension_name like '%FYTD' THEN
585 SELECT fnd_date.canonical_to_date(org_information11)
586 INTO l_beg_of_fiscal_year
587 FROM pay_payroll_actions PACT,
588 hr_organization_information HOI
589 WHERE UPPER(HOI.org_information_context) = 'BUSINESS GROUP INFORMATION'
590 AND HOI.organization_id = PACT.business_group_id
591 AND PACT.payroll_action_id = p_owner_payroll_action_id;
592
593 p_expiry_date := next_fiscal_year(l_beg_of_fiscal_year,p_owner_effective_date) - 1;
594 hr_utility.trace('p_expiry_date ===>' || p_expiry_date);
595
596 ELSIF p_dimension_name like '%_CAL_YTD' THEN
597 p_expiry_date := next_calendar_year(p_owner_effective_date) - 1;
598 hr_utility.trace('p_expiry_date ===>' || p_expiry_date);
599
600 ELSIF p_dimension_name like '%QTD' THEN
601 p_expiry_date := next_quarter(p_owner_effective_date) - 1;
602 hr_utility.trace('p_expiry_date ===>' || p_expiry_date);
603
604 ELSIF p_dimension_name like '%YTD' THEN
605 SELECT to_date('01-04-'||to_char(fnd_number.canonical_to_number(
606 to_char(PACT.effective_date,'YYYY'))+ decode(sign(PACT.effective_date
607 - to_date('01-04-'||to_char(PACT.effective_date,'YYYY'),'DD-MM-YYYY'))
608 ,-1,0,1)),'DD-MM-YYYY') - 1
609 INTO p_expiry_date
610 FROM pay_payroll_actions PACT
611 WHERE PACT.payroll_action_id = p_owner_payroll_action_id;
612
613 ELSIF p_dimension_name like '%LTD' THEN
614 p_expiry_date := fnd_date.canonical_to_date('4712/12/31');
615 hr_utility.trace('p_expiry_date ===>' || p_expiry_date);
616
617 ELSE
618 hr_utility.trace('Entered Exception section');
619 hr_utility.set_message(801, 'NO_EXP_CHECK_FOR_DIMENSION');
620 hr_utility.raise_error;
621
622 END IF;
623
624 END date_ec;
625
626 --------------------------------------------------------------------------
627 -- --
628 -- Name : START_CODE_12MTHS_PREV --
629 -- Type : PROCEDURE --
630 -- Access : Public --
631 -- Description : This procedure finds the start date based on the --
632 -- effective date for dimension _ASG_12MTHS_PREV --
633 -- --
634 -- Parameters : --
635 -- IN : p_effective_date DATE --
636 -- p_payroll_id NUMBER --
637 -- p_bus_grp NUMBER --
638 -- p_asg_action NUMBER --
639 -- OUT : p_start_date DATE --
640 -- RETURN : N/A --
641 -- --
642 -- Change History : --
643 --------------------------------------------------------------------------
644 -- Rev# Date Userid Description --
645 --------------------------------------------------------------------------
646 -- 10-oct-2007 vamittal Initial Version --
647 --------------------------------------------------------------------------
648 PROCEDURE start_code_12mths_prev( p_effective_date IN DATE
649 , p_start_date OUT NOCOPY DATE
650 , p_payroll_id IN NUMBER
651 , p_bus_grp IN NUMBER
652 , p_asg_action IN NUMBER
653 )
654 IS
655 l_start_date DATE := NULL ;
656 l_date_earned DATE;
657 l_assignment_id pay_assignment_actions.assignment_id%TYPE;
658
659 CURSOR get_date_earned
660 IS
661 SELECT ppa.date_earned,paa.assignment_id
662 FROM
663 pay_payroll_actions ppa,
664 pay_assignment_actions paa
665 WHERE paa.assignment_action_id = p_asg_action
666 AND ppa.payroll_action_id=paa.payroll_action_id;
667
668 BEGIN
669
670 OPEN get_date_earned;
671 FETCH get_date_earned INTO l_date_earned,l_assignment_id;
672 CLOSE get_date_earned;
673
674 /* To fetch the start_date from absence element*/
675 l_start_date := pay_hk_avg_pay.specified_date_absence(l_date_earned,l_assignment_id);
676 IF l_start_date IS NULL
677 THEN
678 /* If absence is not present then fetch the start_date from Specified Date element*/
679 l_start_date := pay_hk_avg_pay.specified_date_element(l_date_earned,l_assignment_id);
680 IF l_start_date IS NULL
681 THEN
682 /* If absence is not present then consider start_date as the effectice_date */
683 l_start_date := l_date_earned;
684
685 END IF;
686 END IF;
687
688 l_start_date := add_months(l_start_date,-13);
689 l_start_date := last_day(l_start_date) + 1;
690
691 END start_code_12mths_prev;
692
693 END pay_hk_exc;
[Home] [Help]