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