1 PACKAGE BODY pay_ip_expiry_support AS
2 /* $Header: pyipexps.pkb 120.0 2005/05/29 05:59:33 appldev noship $ */
3 --
4 --
5 -- --------------------------------------------------------------------------
6 -- Returns the start of the next calendar month span relative to the effective
7 -- date.
8 -- --------------------------------------------------------------------------
9 --
10 FUNCTION month_ec
11 (p_effective_date DATE) RETURN DATE IS
12 BEGIN
13 RETURN TRUNC(ADD_MONTHS(p_effective_date, 1), 'MM');
14 END month_ec;
15 --
16 --
17 -- --------------------------------------------------------------------------
18 -- Returns the start of the next calendar quarter span relative to the effective
19 -- date.
20 -- --------------------------------------------------------------------------
21 --
22 FUNCTION quarter_ec
23 (p_effective_date DATE) RETURN DATE IS
24 BEGIN
25 RETURN TRUNC(ADD_MONTHS(p_effective_date, 3), 'Q');
26 END quarter_ec;
27 --
28 --
29 -- --------------------------------------------------------------------------
30 -- Returns the start of the next calendar year span relative to the effective
31 -- date.
32 -- --------------------------------------------------------------------------
33 --
34 FUNCTION year_ec
35 (p_effective_date DATE) RETURN DATE IS
36 BEGIN
37 RETURN TRUNC(ADD_MONTHS(p_effective_date, 12), 'Y');
38 END year_ec;
39 --
40 --
41 -- --------------------------------------------------------------------------
42 -- Returns the start of the next calendar year span relative to the effective
43 -- date.
44 -- --------------------------------------------------------------------------
45 --
46 FUNCTION period_ec
47 (p_owner_payroll_action_id NUMBER
48 ,p_owner_effective_date DATE) RETURN DATE IS
49 --
50 --
51 -- Local variables.
52 --
53 l_period_end_date DATE;
54 BEGIN
55 --
56 --
57 -- If the time periods are not the same for the two payroll actions then we need to expire
58 -- the latest balance NB. returning an expiry date matching p_user_effective_date will
59 -- result in the expiration of the balance.
60 --
61 SELECT TP.end_date
62 INTO l_period_end_date
63 FROM per_time_periods TP
64 ,pay_payroll_actions PACT
65 WHERE PACT.payroll_action_id = p_owner_payroll_action_id
66 AND PACT.payroll_id = TP.payroll_id
67 AND p_owner_effective_date BETWEEN TP.start_date AND TP.end_date;
68 --
69 return l_period_end_date + 1;
70 END period_ec;
71 --
72 --
73 -- --------------------------------------------------------------------------
74 -- Returns the start of the next tax year span relative to the effective date.
75 -- --------------------------------------------------------------------------
76 --
77 FUNCTION tyear_ec
78 (p_effective_date DATE
79 ,p_business_group_id NUMBER) RETURN DATE IS
80 BEGIN
81 RETURN ADD_MONTHS(pay_ip_route_support.tax_year(p_business_group_id, p_effective_date), 12);
82 END tyear_ec;
83 --
84 --
85 -- --------------------------------------------------------------------------
86 -- Returns the start of the next tax quarter span relative to the effective
87 -- date.
88 -- --------------------------------------------------------------------------
89 --
90 FUNCTION tquarter_ec
91 (p_effective_date DATE
92 ,p_business_group_id NUMBER) RETURN DATE IS
93 BEGIN
94 RETURN ADD_MONTHS(pay_ip_route_support.tax_quarter(p_business_group_id, p_effective_date), 3);
95 END tquarter_ec;
96 --
97 --
98 -- --------------------------------------------------------------------------
99 -- Returns the start of the next fiscal year span relative to the effective
100 -- date.
101 -- --------------------------------------------------------------------------
102 --
103 FUNCTION fyear_ec
104 (p_effective_date DATE
105 ,p_business_group_id NUMBER) RETURN DATE IS
106 BEGIN
107 RETURN ADD_MONTHS(pay_ip_route_support.fiscal_year(p_business_group_id, p_effective_date), 12);
108 END fyear_ec;
109 --
110 --
111 -- --------------------------------------------------------------------------
112 -- Returns the start of the next fiscal quarter span relative to the effective
113 -- date.
114 -- --------------------------------------------------------------------------
115 --
116 FUNCTION fquarter_ec
117 (p_effective_date DATE
118 ,p_business_group_id NUMBER) RETURN DATE IS
119 BEGIN
120 RETURN ADD_MONTHS(pay_ip_route_support.fiscal_quarter(p_business_group_id, p_effective_date), 3);
121 END fquarter_ec;
122 --
123 --
124 -- --------------------------------------------------------------------------
125 -- This is the procedure called by the core logic that manages the expiry of
126 -- latest balances. Its interface is fixed as it is called dynamically.
127 --
128 -- It will return the following output indicating the latest balance expiration
129 -- status ...
130 --
131 -- p_expiry_information = 1 - Expired
132 -- p_expiry_information = 0 - OK
133 -- --------------------------------------------------------------------------
134 --
135 PROCEDURE date_ec
136 (p_owner_payroll_action_id NUMBER
137 ,p_user_payroll_action_id NUMBER
138 ,p_owner_assignment_action_id NUMBER
139 ,p_user_assignment_action_id NUMBER
140 ,p_owner_effective_date DATE
141 ,p_user_effective_date DATE
142 ,p_dimension_name VARCHAR2
143 ,p_expiry_information OUT NOCOPY NUMBER) IS
144 --
145 --
146 -- Find the business group of the payroll action and also the period type of the
147 -- balance dimension.
148 --
149 CURSOR csr_info(p_payroll_action_id NUMBER, p_dimension_name VARCHAR2) IS
150 SELECT bd.period_type
151 ,pact.business_group_id
152 FROM pay_payroll_actions pact
153 ,hr_organization_information hoi
154 ,pay_balance_dimensions bd
155 WHERE pact.payroll_action_id = p_payroll_action_id
156 AND hoi.organization_id = pact.business_group_id
157 AND UPPER(hoi.org_information_context) = 'BUSINESS GROUP INFORMATION'
158 AND bd.dimension_name = p_dimension_name
159 AND bd.legislation_code = hoi.org_information9;
160 --
161 --
162 -- Local variables.
163 --
164 l_rec csr_info%ROWTYPE;
165 l_expiry_date DATE;
166 BEGIN
167 --
168 --
169 -- Find the business group and also the period type of the balance dimension.
170 --
171 OPEN csr_info(p_owner_payroll_action_id, p_dimension_name);
172 FETCH csr_info INTO l_rec;
173 CLOSE csr_info;
174 --
175 --
176 -- Based on the period type of the balance dimension get the expiry date.
177 --
178 IF l_rec.period_type = 'MONTH' THEN
179 l_expiry_date := month_ec(p_owner_effective_date);
180 --
181 ELSIF l_rec.period_type = 'QUARTER' THEN
182 l_expiry_date := quarter_ec(p_owner_effective_date);
183 --
184 ELSIF l_rec.period_type = 'YEAR' THEN
185 l_expiry_date := year_ec(p_owner_effective_date);
186 --
187 ELSIF l_rec.period_type = 'PERIOD' THEN
188 l_expiry_date := period_ec(p_owner_payroll_action_id, p_owner_effective_date);
189 --
190 ELSIF l_rec.period_type = 'TYEAR' THEN
191 l_expiry_date := tyear_ec(p_owner_effective_date, l_rec.business_group_id);
192 --
193 ELSIF l_rec.period_type = 'TQUARTER' THEN
194 l_expiry_date := tquarter_ec(p_owner_effective_date, l_rec.business_group_id);
195 --
196 ELSIF l_rec.period_type = 'FYEAR' THEN
197 l_expiry_date := fyear_ec(p_owner_effective_date, l_rec.business_group_id);
198 --
199 ELSIF l_rec.period_type = 'FQUARTER' THEN
200 l_expiry_date := fquarter_ec(p_owner_effective_date, l_rec.business_group_id);
201 END IF;
202 --
203 --
204 -- See if the current effective date is within the same span of time as the
205 -- balance's effective date. If yes then it is OK to use cached balance
206 -- otherwise the balance has expired.
207 --
208 IF p_user_effective_date >= l_expiry_date THEN
209 P_expiry_information := 1; -- Expired!
210 ELSE
211 P_expiry_information := 0; -- OK!
212 END IF;
213 EXCEPTION
214 WHEN OTHERS THEN
215 p_expiry_information := NULL;
216 END date_ec;
217 --
218 -- ----------------------------------------------------------------------------
219 -- This is the overloaded procedure which returns actual expiry date
220 -- ----------------------------------------------------------------------------
221 --
222 PROCEDURE date_ec
223 (p_owner_payroll_action_id NUMBER
224 ,p_user_payroll_action_id NUMBER
225 ,p_owner_assignment_action_id NUMBER
226 ,p_user_assignment_action_id NUMBER
227 ,p_owner_effective_date DATE
228 ,p_user_effective_date DATE
229 ,p_dimension_name VARCHAR2
230 ,p_expiry_information OUT NOCOPY DATE) IS
231 --
232 --
233 -- Find the business group of the payroll action and also the period type of the
234 -- balance dimension.
235 --
236 CURSOR csr_info(p_payroll_action_id NUMBER, p_dimension_name VARCHAR2) IS
237 SELECT bd.period_type
238 ,pact.business_group_id
239 FROM pay_payroll_actions pact
240 ,hr_organization_information hoi
241 ,pay_balance_dimensions bd
242 WHERE pact.payroll_action_id = p_payroll_action_id
243 AND hoi.organization_id = pact.business_group_id
244 AND UPPER(hoi.org_information_context) = 'BUSINESS GROUP INFORMATION'
245 AND bd.dimension_name = p_dimension_name
246 AND bd.legislation_code = hoi.org_information9;
247 --
248 --
249 -- Local variables.
250 --
251 l_rec csr_info%ROWTYPE;
252 BEGIN
253 --
254 --
255 -- Find the business group and also the period type of the balance dimension.
256 --
257 OPEN csr_info(p_owner_payroll_action_id, p_dimension_name);
258 FETCH csr_info INTO l_rec;
259 CLOSE csr_info;
260 --
261 --
262 -- Based on the period type of the balance dimension get the expiry date.
263 --
264 IF l_rec.period_type = 'MONTH' THEN
265 p_expiry_information := month_ec(p_owner_effective_date)-1;
266 --
267 ELSIF l_rec.period_type = 'QUARTER' THEN
268 p_expiry_information := quarter_ec(p_owner_effective_date)-1;
269 --
270 ELSIF l_rec.period_type = 'YEAR' THEN
271 p_expiry_information := year_ec(p_owner_effective_date)-1;
272 --
273 ELSIF l_rec.period_type = 'PERIOD' THEN
274 p_expiry_information := period_ec(p_owner_payroll_action_id, p_owner_effective_date) -1;
275 --
276 ELSIF l_rec.period_type = 'TYEAR' THEN
277 p_expiry_information := tyear_ec(p_owner_effective_date, l_rec.business_group_id)-1;
278 --
279 ELSIF l_rec.period_type = 'TQUARTER' THEN
280 p_expiry_information := tquarter_ec(p_owner_effective_date, l_rec.business_group_id)-1;
281 --
282 ELSIF l_rec.period_type = 'FYEAR' THEN
283 p_expiry_information := fyear_ec(p_owner_effective_date, l_rec.business_group_id)-1;
284 --
285 ELSIF l_rec.period_type = 'FQUARTER' THEN
286 p_expiry_information := fquarter_ec(p_owner_effective_date, l_rec.business_group_id)-1;
287 END IF;
288 --
289 --
290 -- See if the current effective date is within the same span of time as the
291 -- balance's effective date. If yes then it is OK to use cached balance
292 -- otherwise the balance has expired.
293 --
294 EXCEPTION
295 WHEN OTHERS THEN
296 p_expiry_information := NULL;
297 END date_ec;
298 END pay_ip_expiry_support;
[Home] [Help]