1 PACKAGE BODY PAY_SE_EXPIRY_SUPPORT AS
2 /*$Header: pyseexsu.pkb 120.0.12000000.1 2007/04/24 06:53:32 rlingama noship $*/
3 -- --------------------------------------------------------------------------
4 -- This is the procedure called by the core logic that manages the expiry of
5 -- latest balances. Its interface is fixed as it is called dynamically.
6 --
7 -- It will return the following output indicating the latest balance expiration
8 -- status ...
9 --
10 -- p_expiry_information = 1 - Expired
11 -- p_expiry_information = 0 - OK
12 -- --------------------------------------------------------------------------
13 --
14 PROCEDURE holiday_pay_ec
15 (p_owner_payroll_action_id NUMBER
16 ,p_user_payroll_action_id NUMBER
17 ,p_owner_assignment_action_id NUMBER
18 ,p_user_assignment_action_id NUMBER
19 ,p_owner_effective_date DATE
20 ,p_user_effective_date DATE
21 ,p_dimension_name VARCHAR2
22 ,p_expiry_information OUT NOCOPY NUMBER) IS
23 --
24 --
25 -- Find the business group of the payroll action and also the period type of the
26 -- balance dimension.
27 --
28 CURSOR csr_info(p_payroll_action_id NUMBER, p_dimension_name VARCHAR2) IS
29 SELECT bd.period_type
30 ,pact.business_group_id
31 FROM pay_payroll_actions pact
32 ,hr_organization_information hoi
33 ,pay_balance_dimensions bd
34 WHERE pact.payroll_action_id = p_payroll_action_id
35 AND hoi.organization_id = pact.business_group_id
36 AND UPPER(hoi.org_information_context) = 'BUSINESS GROUP INFORMATION'
37 AND bd.dimension_name = p_dimension_name
38 AND bd.legislation_code = hoi.org_information9;
39 --
40 --
41 -- Local variables.
42 --
43 l_rec csr_info%ROWTYPE;
44 l_expiry_date DATE;
45 BEGIN
46
47 --
48 --
49 -- Find the business group and also the period type of the balance dimension.
50 --
51 OPEN csr_info(p_owner_payroll_action_id, p_dimension_name);
52 FETCH csr_info INTO l_rec;
53 CLOSE csr_info;
54 --
55 --
56 -- Based on the period type of the balance dimension get the expiry date.
57 --
58 IF l_rec.period_type = 'HYEAR' THEN
59
60 l_expiry_date := hyear_ec(p_owner_assignment_action_id,p_owner_effective_date);
61 --
62 END IF;
63 hr_utility.trace(' l_expiry_date => ' || l_expiry_date );
64 --
65 --
66 -- See if the current effective date is within the same span of time as the
67 -- balance's effective date. If yes then it is OK to use cached balance
68 -- otherwise the balance has expired.
69 --
70 IF p_user_effective_date >= l_expiry_date THEN
71 P_expiry_information := 1; -- Expired!
72 ELSE
73 P_expiry_information := 0; -- OK!
74 END IF;
75 /*
76 IF p_dimension_name ='Assignment Previous Holiday Year to Date' AND l_rec.period_type = 'HYEAR' THEN
77 P_expiry_information := 1; -- OK!
78 END IF;
79 */
80
81 EXCEPTION
82 WHEN OTHERS THEN
83 p_expiry_information := NULL;
84 END holiday_pay_ec;
85 --
86 -- ----------------------------------------------------------------------------
87 -- This is the overloaded procedure which returns actual expiry date
88 -- ----------------------------------------------------------------------------
89 --
90 PROCEDURE holiday_pay_ec
91 (p_owner_payroll_action_id NUMBER
92 ,p_user_payroll_action_id NUMBER
93 ,p_owner_assignment_action_id NUMBER
94 ,p_user_assignment_action_id NUMBER
95 ,p_owner_effective_date DATE
96 ,p_user_effective_date DATE
97 ,p_dimension_name VARCHAR2
98 ,p_expiry_information OUT NOCOPY DATE) IS
99 --
100 --
101 -- Find the business group of the payroll action and also the period type of the
102 -- balance dimension.
103 --
104 CURSOR csr_info(p_payroll_action_id NUMBER, p_dimension_name VARCHAR2) IS
105 SELECT bd.period_type
106 ,pact.business_group_id
107 FROM pay_payroll_actions pact
108 ,hr_organization_information hoi
109 ,pay_balance_dimensions bd
110 WHERE pact.payroll_action_id = p_payroll_action_id
111 AND hoi.organization_id = pact.business_group_id
112 AND UPPER(hoi.org_information_context) = 'BUSINESS GROUP INFORMATION'
113 AND bd.dimension_name = p_dimension_name
114 AND bd.legislation_code = hoi.org_information9;
115 --
116 --
117 -- Local variables.
118 --
119 l_rec csr_info%ROWTYPE;
120 BEGIN
121
122 --
123 --
124
125 -- Find the business group and also the period type of the balance dimension.
126 --
127 OPEN csr_info(p_owner_payroll_action_id, p_dimension_name);
128 FETCH csr_info INTO l_rec;
129 CLOSE csr_info;
130 --
131 --
132 -- Based on the period type of the balance dimension get the expiry date.
133 --
134 IF l_rec.period_type = 'HYEAR' THEN
135
136 p_expiry_information := hyear_ec(p_owner_assignment_action_id,p_owner_effective_date)-1;
137
138 /*
139 IF p_dimension_name ='Assignment Previous Holiday Year to Date' THEN
140
141 p_expiry_information := p_owner_effective_date - 36500;
142
143 END IF;
144 */
145
146 END IF;
147
148
149 hr_utility.trace(' p_expiry_information => ' || p_expiry_information);
150 --
151 --
152 -- See if the current effective date is within the same span of time as the
153 -- balance's effective date. If yes then it is OK to use cached balance
154 -- otherwise the balance has expired.
155 --
156 EXCEPTION
157 WHEN OTHERS THEN
158 p_expiry_information := NULL;
159 END holiday_pay_ec;
160
161 function hyear_ec
162 (p_assignment_action_id IN number,
163 p_effective_date IN date
164 ) RETURN DATE IS
165
166 l_assignment_id number;
167 l_business_group_id NUMBER;
168 l_start_month number;
169 l_assignment NUMBER;
170
171 CURSOR csr_assignment IS
172 SELECT assignment_id
173 FROM pay_assignment_actions
174 WHERE assignment_action_id=p_assignment_action_id;
175 CURSOR csr_holiday_start IS
176 SELECT substr(hoi4.ORG_INFORMATION3,4,2)
177 FROM HR_ORGANIZATION_UNITS o1
178 ,HR_ORGANIZATION_INFORMATION hoi1
179 ,HR_ORGANIZATION_INFORMATION hoi2
180 ,HR_ORGANIZATION_INFORMATION hoi3
181 ,HR_ORGANIZATION_INFORMATION hoi4
182 ,( SELECT TRIM(SCL.SEGMENT2) AS ORG_ID
183 FROM PER_ALL_ASSIGNMENTS_F ASG
184 ,HR_SOFT_CODING_KEYFLEX SCL
185 WHERE ASG.ASSIGNMENT_ID = l_assignment_id
186 AND ASG.SOFT_CODING_KEYFLEX_ID = SCL.SOFT_CODING_KEYFLEX_ID
187 AND p_effective_date BETWEEN ASG.EFFECTIVE_START_DATE AND ASG.EFFECTIVE_END_DATE ) X
188 WHERE o1.business_group_id = l_business_group_id
189 AND hoi1.organization_id = o1.organization_id
190 AND hoi1.organization_id = X.ORG_ID
191 AND hoi1.org_information1 = 'SE_LOCAL_UNIT'
192 AND hoi1.org_information_context = 'CLASS'
193 AND o1.organization_id = hoi2.org_information1
194 AND hoi2.ORG_INFORMATION_CONTEXT='SE_LOCAL_UNITS'
195 AND hoi2.organization_id = hoi3.organization_id
196 AND hoi3.ORG_INFORMATION_CONTEXT='CLASS'
197 AND hoi3.org_information1 = 'HR_LEGAL_EMPLOYER'
198 AND hoi3.organization_id = hoi4.organization_id
199 AND hoi4.ORG_INFORMATION_CONTEXT='SE_HOLIDAY_YEAR_DEFN'
200 AND hoi4.org_information1 IS NOT NULL;
201 BEGIN
202 OPEN csr_assignment;
203 FETCH csr_assignment INTO l_assignment;
204 CLOSE csr_assignment;
205 OPEN csr_holiday_start;
206 FETCH csr_holiday_start INTO l_start_month;
207 CLOSE csr_holiday_start;
208 IF to_number(to_char(p_effective_date,'MM'))< l_start_month THEN
209 return (to_date('01/' || l_start_month || '/' || to_char(p_effective_date,'YYYY') , 'DD/MM/YYYY'));
210 ELSE
211 return (to_date('01/' || l_start_month || '/' || to_number(to_char(p_effective_date,'YYYY'))+1 , 'DD/MM/YYYY'));
212 END IF;
213 END hyear_ec;
214
215 END PAY_SE_EXPIRY_SUPPORT;
216
[Home] [Help]