1 PACKAGE BODY pay_mx_expiry_support AS
2 /*$Header: paymxbalexpccode.pkb 120.0.12020000.2 2012/07/04 19:50:20 amnaraya ship $
3 */
4 /*---------------------------- next_bimonth_period ----------------------------
5 NAME
6 next_bimonth_period
7 DESCRIPTION
8 Given a date and a payroll action id, returns the date of the day after
9 the end of the containing pay period.
10 NOTES
11 <none>
12 */
13 FUNCTION next_bimonth_period
14 (
15 p_pactid IN NUMBER,
16 p_date IN DATE
17 ) RETURN DATE is
18 l_return_val DATE := NULL;
19 BEGIN
20 SELECT TRUNC(ADD_MONTHS(p_date, DECODE(MOD(TO_CHAR(p_date, 'MM'),2),
21 0, 1,
22 2)), 'MM')
23 INTO l_return_val
24 FROM pay_payroll_actions
25 WHERE payroll_action_id = p_pactid
26 AND effective_date = p_date;
27
28 RETURN l_return_val;
29
30 END next_bimonth_period;
31 /*------------------------------ date_ec ------------------------------------
32 NAME
33 date_ec
34 DESCRIPTION
35 Mexico specific Expiry checking code for the following date-related
36 dimensions:
37 Person and GRE and Bi-Month.
38 The Expiry checking code for the rest of the dimensions uses functions
39 delivered in PAY_IP_EXPIRY_SUPPORT.
40
41
42 NOTES
43 This procedure assumes the date portion of the dimension name
44 is always at the end to allow accurate identification since
45 this is used for many dimensions.
46 */
47 PROCEDURE date_ec
48 (
49 p_owner_payroll_action_id IN NUMBER, -- run created balance.
50 p_user_payroll_action_id IN NUMBER, -- current run.
51 p_owner_assignment_action_id IN NUMBER, -- assact created balance.
52 p_user_assignment_action_id IN NUMBER, -- current assact..
53 p_owner_effective_date IN DATE, -- eff date of balance.
54 p_user_effective_date IN DATE, -- eff date of current run.
55 p_dimension_name IN VARCHAR2, -- balance dimension name.
56 p_expiry_information OUT NOCOPY NUMBER-- dimension expired flag.
57 ) is
58
59 l_beg_of_fiscal_year DATE := NULL;
60 l_expiry_date DATE := NULL;
61
62 BEGIN
63
64 IF p_dimension_name like '%Social Security Bi-Month' THEN
65 l_expiry_date := next_bimonth_period(p_owner_payroll_action_id,
66 p_owner_effective_date);
67 ELSE
68 hr_utility.set_message(801, 'NO_EXP_CHECK_FOR_THIS_DIMENSION');
69 hr_utility.raise_error;
70
71 END IF;
72
73 IF p_user_effective_date >= l_expiry_date THEN
74 p_expiry_information := 1;
75 ELSE
76 p_expiry_information := 0;
77 END IF;
78
79 END date_ec;
80
81
82 /* This procedure is the overlaoded function that will take care of the
83 of the requirement of Balance adjustment process.*/
84
85 PROCEDURE date_ec
86 (
87 p_owner_payroll_action_id IN NUMBER, -- run created balance.
88 p_user_payroll_action_id IN NUMBER, -- current run.
89 p_owner_assignment_action_id IN NUMBER, -- assact created balance.
90 p_user_assignment_action_id IN NUMBER, -- current assact..
91 p_owner_effective_date IN DATE, -- eff date of balance.
92 p_user_effective_date IN DATE, -- eff date of current run.
93 p_dimension_name IN VARCHAR2, -- balance dimension name.
94 p_expiry_information OUT NOCOPY DATE -- dimension expired date.
95 ) is
96
97 l_beg_of_fiscal_year DATE := NULL;
98 l_expiry_date DATE := NULL;
99
100 BEGIN
101
102
103 IF p_dimension_name like '%Social Security Bi-Month' THEN
104 p_expiry_information := next_bimonth_period(p_owner_payroll_action_id,
105 p_owner_effective_date)-1;
106 ELSE
107
108 hr_utility.set_message(801, 'NO_EXP_CHECK_FOR_THIS_DIMENSION');
109 hr_utility.raise_error;
110
111 END IF;
112
113
114 END date_ec; /* date p_expiry information procedure overloaded function */
115 END pay_mx_expiry_support;