[Home] [Help]
PACKAGE BODY: APPS.PER_DISPLAY_ACCRUAL_BALANCE
Source
1 PACKAGE BODY PER_DISPLAY_ACCRUAL_BALANCE AS
2 /* $Header: peraccbal.pkb 120.9.12020000.2 2013/02/07 09:49:11 aammoham ship $ */
3
4 PROCEDURE GET_ACCRUAL_BALANCES(p_resource_id IN NUMBER,
5 p_element_set_id in Number,
6 p_evaluation_function in varchar2,
7 p_evaluation_date IN DATE ,
8 p_accrual_balance_table OUT NOCOPY PER_ACCRUAL_BALANCE_TABLE_TYPE,
9 p_error_message OUT NOCOPY VARCHAR2)IS
10
11 l_business_group_id per_all_assignments_f.BUSINESS_GROUP_ID%type;
12 l_element_type_id pay_element_types_f.ELEMENT_TYPE_ID%type;
13 l_assignment_id per_all_assignments_f.ASSIGNMENT_ID%type;
14 l_payroll_id pay_element_types_f.ELEMENT_TYPE_ID%type;
15
16 l_plan_id pay_accrual_plans.ACCRUAL_PLAN_ID%type;
17 l_name pay_accrual_plans.ACCRUAL_PLAN_NAME%type;
18
19 l_accrual number;
20 l_net_accrual number;
21 l_start_date Date;
22 l_end_date Date;
23 l_acc_end_date Date;
24 l_index Number ;
25
26 l_legislation_code per_business_groups_perf.legislation_code%type;
27 l_return Number;
28
29 l_leave_type_balance pay_accrual_plans.information1%type; /* 4767298 */
30 l_information_Category pay_accrual_plans.information_category%type; /* 4767298 */
31
32 CURSOR c_element_set(p_element_set IN NUMBER,
33 p_evaluation_date IN DATE,
34 p_business_group_id in varchar2
35 ) IS
36 select pet.element_type_id
37 from pay_element_types_f pet,
38 pay_element_type_rules per
39 where per.element_set_id = p_element_set_id
40 AND pet.business_group_id=p_business_group_id
41 and per.include_or_exclude = 'I'
42 and per.element_type_id = pet.element_type_id
43 and p_evaluation_date between
44 effective_start_date and effective_end_date;
45
46 CURSOR c_assignments(p_resource_id In Number,
47 p_evaluation_date In Date) IS
48 SELECT pas.ASSIGNMENT_ID,
49 pas.payroll_id,
50 pas.business_group_id
51 FROM PER_ALL_ASSIGNMENTS_F pas,
52 per_assignment_status_types typ
53 WHERE pas.PERSON_ID = p_resource_id
54 AND pas.ASSIGNMENT_TYPE in ('E','C')
55 AND pas.PRIMARY_FLAG = 'Y'
56 AND pas.ASSIGNMENT_STATUS_TYPE_ID = typ.ASSIGNMENT_STATUS_TYPE_ID
57 AND typ.PER_SYSTEM_STATUS IN ( 'ACTIVE_ASSIGN','ACTIVE_CWK')
58 AND p_evaluation_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE ;
59
60 CURSOR c_accrual_plans(p_assignment_id In Number,
61 p_element_type_id In Number,
62 p_evaluation_date In Date)
63 IS
64 SELECT pap.accrual_plan_id,
65 pap_tl.accrual_plan_name,
66 pap.information1, /* 4767298 */
67 pap.information_Category /* 4767298 */
68 FROM pay_accrual_plans pap
69 ,pay_element_types_f pet
70 ,pay_element_links_f pel
71 ,pay_element_entries_f pee
72 ,pay_accrual_plans_tl pap_tl /* 14387376 */
73 WHERE
74 pee.assignment_id = p_assignment_id
75 AND
76 pet.ELEMENT_TYPE_ID=p_element_type_id
77 AND
78 pap.accrual_plan_element_type_id = pet.element_type_id
79 AND
80 pap.accrual_plan_element_type_id = pel.element_type_id
81 AND
82 pel.element_type_id = pet.element_type_id
83 AND
84 pee.element_type_id = pet.element_type_id
85 AND
86 pel.element_link_id = pee.element_link_id
87 AND
88 pap.accrual_plan_id = pap_tl.accrual_plan_id
89 AND
90 pap_tl.LANGUAGE(+) = userenv('LANG')
91 AND
92 p_evaluation_date BETWEEN pee.effective_start_date
93 AND pee.effective_end_date
94 AND
95 pee.effective_start_date BETWEEN pet.effective_start_date
96 AND pet.effective_end_date
97 AND
98 pee.effective_start_date BETWEEN pel.effective_start_date
99 AND pel.effective_end_date;
100 l_message_count number;
101 l_message varchar2(250);
102
103 BEGIN
104
105 HR_UTIL_MISC_SS.setEffectiveDate(p_evaluation_date);
106
107 p_accrual_balance_table := PER_ACCRUAL_BALANCE_TABLE_TYPE();
108
109
110 l_index:=0;
111
112 OPEN c_assignments(p_resource_id,p_evaluation_date);
113 LOOP
114 FETCH c_assignments INTO l_assignment_id,l_payroll_id,l_business_group_id;
115 EXIT WHEN c_assignments%NOTFOUND;
116
117 OPEN c_element_set(p_element_set_id,p_evaluation_date,l_business_group_id);
118 LOOP
119 FETCH c_element_set INTO l_element_type_id;
120 EXIT WHEN c_element_set%NOTFOUND;
121 OPEN c_accrual_plans(l_assignment_id,l_element_type_id,p_evaluation_date);
122 LOOP
123 FETCH c_accrual_plans INTO l_plan_id,l_name, l_leave_type_balance, l_information_category;
124 EXIT WHEN c_accrual_plans%NOTFOUND;
125 l_index:=l_index+1;
126 p_accrual_balance_table.extend;
127 l_legislation_code := hr_api.return_legislation_code(l_business_group_id);
128 if( l_legislation_code ='NZ') THEN
129 l_return := hr_nz_holidays.get_accrual_entitlement
130 (P_Assignment_ID=>l_assignment_id
131 ,P_Payroll_ID=>l_payroll_id
132 ,P_Business_Group_ID=>l_business_group_id
133 ,P_Plan_ID=>l_plan_id
134 ,P_Calculation_Date=>p_evaluation_date
135 ,P_net_Accrual =>l_accrual
136 ,P_Net_Entitlement => l_net_accrual
137 ,P_calc_Start_Date=>l_start_date
138 ,P_last_accrual =>l_acc_end_date
139 ,P_next_period_End=> l_end_date);
140
141 l_net_accrual := l_net_accrual + l_accrual;
142 p_accrual_balance_table(l_index) := PER_ACCRUAL_BALANCE_TYPE(l_name,round(NVL(l_net_accrual,0), 3));
143
144 ELSIF(l_legislation_code ='AU') THEN
145 l_return := hr_au_holidays.get_accrual_entitlement
146 (P_Assignment_ID=>l_assignment_id
147 ,P_Plan_ID=>l_plan_id
148 ,P_Payroll_ID=>l_payroll_id
149 ,P_Business_Group_ID=>l_business_group_id
150 ,P_Calculation_Date=>p_evaluation_date
151 ,P_calc_Start_Date=>l_start_date
152 ,P_next_period_End=> l_end_date
153 ,P_last_accrual =>l_acc_end_date
154 ,P_net_Accrual =>l_accrual
155 ,P_Net_Entitlement => l_net_accrual);
156
157 /* 4767298 */
158 if l_information_category in ('AU_AUAL' , 'AU_AULSL' , 'AU_AUSL')
159 then
160 if nvl(l_leave_type_balance, 'EA') = 'EA'
161 then
162 l_net_accrual := l_net_accrual + l_accrual;
163 else
164 l_net_accrual := l_net_accrual;
165 end if;
166 else
167 l_net_accrual := l_net_accrual + l_accrual;
168 end if;
169
170 p_accrual_balance_table(l_index) := PER_ACCRUAL_BALANCE_TYPE(l_name,round(NVL(l_net_accrual,0), 3));
171
172 ELSE
173 per_accrual_calc_functions.Get_Net_Accrual
174 (P_Assignment_ID=>l_assignment_id
175 ,P_Plan_ID=>l_plan_id
176 ,P_Payroll_ID=>l_payroll_id
177 ,P_Business_Group_ID=>l_business_group_id
178 ,P_Calculation_Date=>p_evaluation_date
179 ,P_Start_Date=>l_start_date
180 ,P_End_Date=> l_end_date
181 ,P_Accrual_End_Date =>l_acc_end_date
182 ,P_Accrual =>l_accrual
183 ,P_Net_Entitlement => l_net_accrual);
184
185 p_accrual_balance_table(l_index) := PER_ACCRUAL_BALANCE_TYPE(l_name,round(NVL(l_net_accrual,0), 3));
186 end if;
187
188 END LOOP;
189 CLOSE c_accrual_plans;
190 END LOOP;
191 CLOSE c_element_set;
192 END LOOP;
193 CLOSE c_assignments;
194 l_message_count := per_accrual_message_pkg.count_messages;
195
196 for i in 1..l_message_count loop
197 l_message := per_accrual_message_pkg.get_message(i);
198 if (l_message is not null and (l_message = 'HR_52797_PTO_FML_ACT_ACCRUAL' or l_message = 'HR_52793_PTO_FML_ASG_INELIG') or l_message ='HR_52795_PTO_FML_CALC_DATE') Then
199 p_error_message := l_message;
200 end if;
201 end loop;
202 per_accrual_message_pkg.clear_table;
203
204 END GET_ACCRUAL_BALANCES;
205 function IsTerminatedEmployee(p_resource_id IN NUMBER,
206 p_evaluation_date IN DATE)
207 return varchar2
208 is
209 l_assignment_id per_all_assignments_f.ASSIGNMENT_ID%type;
210 CURSOR c_assignments(p_resource_id In Number,
211 p_evaluation_date In Date) IS
212 SELECT pas.ASSIGNMENT_ID
213 FROM PER_ALL_ASSIGNMENTS_F pas,
214 per_assignment_status_types typ
215 WHERE pas.PERSON_ID = p_resource_id
216 AND pas.ASSIGNMENT_TYPE in ('E','C')
217 AND pas.PRIMARY_FLAG = 'Y'
218 AND pas.ASSIGNMENT_STATUS_TYPE_ID = typ.ASSIGNMENT_STATUS_TYPE_ID
219 AND typ.PER_SYSTEM_STATUS IN ( 'ACTIVE_ASSIGN','ACTIVE_CWK')
220 AND p_evaluation_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE ;
221 begin
222
223 OPEN c_assignments(p_resource_id,p_evaluation_date);
224 FETCH c_assignments INTO l_assignment_id;
225 IF c_assignments%NOTFOUND Then
226 return 'Y';
227 else
228 return 'N';
229 end if;
230
231 exception
232 when others then
233 raise;
234 end IsTerminatedEmployee;
235 PROCEDURE GET_ACCRUAL_BALANCES(p_resource_id IN NUMBER,
236 p_evaluation_function in varchar2,
237 p_evaluation_date IN DATE ,
238 p_accrual_balance_table OUT NOCOPY PER_ACCRUAL_BALANCE_TABLE_TYPE)IS
239
240 l_business_group_id per_all_assignments_f.BUSINESS_GROUP_ID%type;
241 l_element_type_id pay_element_types_f.ELEMENT_TYPE_ID%type;
242 l_assignment_id per_all_assignments_f.ASSIGNMENT_ID%type;
243 l_payroll_id pay_element_types_f.ELEMENT_TYPE_ID%type;
244
245 l_plan_id pay_accrual_plans.ACCRUAL_PLAN_ID%type;
246 l_name pay_accrual_plans.ACCRUAL_PLAN_NAME%type;
247
248 l_accrual number;
249 l_net_accrual number;
250 l_start_date Date;
251 l_end_date Date;
252 l_acc_end_date Date;
253 l_index Number ;
254
255 l_legislation_code per_business_groups_perf.legislation_code%type;
256 l_return Number;
257
258 l_leave_type_balance pay_accrual_plans.information1%type; /* 4767298 */
259 l_information_Category pay_accrual_plans.information_category%type; /* 4767298 */
260
261 CURSOR c_assignments(p_resource_id In Number,
262 p_evaluation_date In Date) IS
263 SELECT pas.ASSIGNMENT_ID,
264 pas.payroll_id,
265 pas.business_group_id
266 FROM PER_ASSIGNMENTS_F2 pas,
267 per_assignment_status_types typ
268 WHERE pas.PERSON_ID = p_resource_id
269 AND pas.ASSIGNMENT_TYPE in ('E','C')
270 -- AND pas.PRIMARY_FLAG = 'Y'
271 AND pas.ASSIGNMENT_STATUS_TYPE_ID = typ.ASSIGNMENT_STATUS_TYPE_ID
272 AND typ.PER_SYSTEM_STATUS IN ( 'ACTIVE_ASSIGN','ACTIVE_CWK')
273 AND p_evaluation_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE ;
274
275 CURSOR c_accrual_plans(p_assignment_id In Number,
276 p_evaluation_date In Date)
277 IS
278 SELECT pap_v.accrual_plan_id,
279 pap_tl.accrual_plan_name,
280 pap_v.information1, /* 4767298 */
281 pap_v.information_Category /* 4767298 */
282 FROM
283 PAY_VIEW_ACCRUAL_PLANS_V pap_v
284 ,pay_accrual_plans_tl pap_tl /* 14387376 */
285 WHERE pap_v.ASSIGNMENT_ID = p_assignment_id
286 AND pap_v.accrual_plan_id = pap_tl.accrual_plan_id
287 AND pap_tl.LANGUAGE(+) = userenv('LANG')
288 AND p_evaluation_date BETWEEN pap_v.ASG_EFFECTIVE_START_DATE AND pap_v.ASG_EFFECTIVE_END_DATE
289 AND p_evaluation_date BETWEEN pap_v.IV_EFFECTIVE_START_DATE AND pap_v.IV_EFFECTIVE_END_DATE
290 AND p_evaluation_date BETWEEN pap_v.E_ENTRY_EFFECTIVE_START_DATE AND pap_v.E_ENTRY_EFFECTIVE_END_DATE
291 AND p_evaluation_date BETWEEN pap_v.E_TYPE_EFFECTIVE_START_DATE AND pap_v.E_TYPE_EFFECTIVE_END_DATE
292 AND p_evaluation_date BETWEEN pap_v.E_LINK_EFFECTIVE_START_DATE AND pap_v.E_LINK_EFFECTIVE_END_DATE ;
293 BEGIN
294
295 HR_UTIL_MISC_SS.setEffectiveDate(p_evaluation_date);
296
297 p_accrual_balance_table := PER_ACCRUAL_BALANCE_TABLE_TYPE();
298
299
300 l_index:=0;
301
302 OPEN c_assignments(p_resource_id,p_evaluation_date);
303 LOOP
304 FETCH c_assignments INTO l_assignment_id,l_payroll_id,l_business_group_id;
305 EXIT WHEN c_assignments%NOTFOUND;
306
307 OPEN c_accrual_plans(l_assignment_id,p_evaluation_date);
308 LOOP
309 FETCH c_accrual_plans INTO l_plan_id,l_name, l_leave_type_balance, l_information_category;
310 EXIT WHEN c_accrual_plans%NOTFOUND;
311 l_index:=l_index+1;
312 p_accrual_balance_table.extend;
313 l_legislation_code := hr_api.return_legislation_code(l_business_group_id);
314 if( l_legislation_code ='NZ') THEN
315 l_return := hr_nz_holidays.get_accrual_entitlement
316 (P_Assignment_ID=>l_assignment_id
317 ,P_Payroll_ID=>l_payroll_id
318 ,P_Business_Group_ID=>l_business_group_id
319 ,P_Plan_ID=>l_plan_id
320 ,P_Calculation_Date=>p_evaluation_date
321 ,P_net_Accrual =>l_accrual
322 ,P_Net_Entitlement => l_net_accrual
323 ,P_calc_Start_Date=>l_start_date
324 ,P_last_accrual =>l_acc_end_date
325 ,P_next_period_End=> l_end_date);
326
327 l_net_accrual := l_net_accrual + l_accrual;
328 p_accrual_balance_table(l_index) := PER_ACCRUAL_BALANCE_TYPE(l_name,round(NVL(l_net_accrual,0), 3));
329
330 ELSIF(l_legislation_code ='AU') THEN
331 l_return := hr_au_holidays.get_accrual_entitlement
332 (P_Assignment_ID=>l_assignment_id
333 ,P_Plan_ID=>l_plan_id
334 ,P_Payroll_ID=>l_payroll_id
335 ,P_Business_Group_ID=>l_business_group_id
336 ,P_Calculation_Date=>p_evaluation_date
337 ,P_calc_Start_Date=>l_start_date
338 ,P_next_period_End=> l_end_date
339 ,P_last_accrual =>l_acc_end_date
340 ,P_net_Accrual =>l_accrual
341 ,P_Net_Entitlement => l_net_accrual);
342
343 /* 4767298 */
344 if l_information_category in ('AU_AUAL' , 'AU_AULSL' , 'AU_AUSL')
345 then
346 if nvl(l_leave_type_balance, 'EA') = 'EA'
347 then
348 l_net_accrual := l_net_accrual + l_accrual;
349 else
350 l_net_accrual := l_net_accrual;
351 end if;
352 else
353 l_net_accrual := l_net_accrual + l_accrual;
354 end if;
355
356 p_accrual_balance_table(l_index) := PER_ACCRUAL_BALANCE_TYPE(l_name,round(NVL(l_net_accrual,0), 3));
357
358 ELSE
359 per_accrual_calc_functions.Get_Net_Accrual
360 (P_Assignment_ID=>l_assignment_id
361 ,P_Plan_ID=>l_plan_id
362 ,P_Payroll_ID=>l_payroll_id
363 ,P_Business_Group_ID=>l_business_group_id
364 ,P_Calculation_Date=>p_evaluation_date
365 ,P_Start_Date=>l_start_date
366 ,P_End_Date=> l_end_date
367 ,P_Accrual_End_Date =>l_acc_end_date
368 ,P_Accrual =>l_accrual
369 ,P_Net_Entitlement => l_net_accrual);
370
371 p_accrual_balance_table(l_index) := PER_ACCRUAL_BALANCE_TYPE(l_name,round(NVL(l_net_accrual,0), 3));
372 end if;
373
374 END LOOP;
375 CLOSE c_accrual_plans;
376 END LOOP;
377 CLOSE c_assignments;
378 END GET_ACCRUAL_BALANCES;
379 END PER_DISPLAY_ACCRUAL_BALANCE;