1 package body pay_nz_qes_pkg as
2 /* $Header: pynzqes.pkb 120.0 2005/05/29 02:12:27 appldev noship $ */
3 ------------------------------------------------------------------------
4 g_ordinary_time_hours_id pay_defined_balances.defined_balance_id%type;
5 g_ordinary_time_payout_id pay_defined_balances.defined_balance_id%type;
6 ------------------------------------------------------------------------
7 -- Counts the number of people per group.
8 ------------------------------------------------------------------------
9 function count_employees
10 (p_organization_id in hr_organization_units.organization_id%type,
11 p_payroll_id in pay_all_payrolls_f.payroll_id%type,
12 p_time_period_id in per_time_periods.time_period_id%type,
13 p_location_id in per_all_assignments_f.location_id%type,
14 p_emp_cat_code in per_all_people_f.per_information7%type,
15 p_work_time_code in per_all_people_f.per_information8%type,
16 p_sex in per_all_people_f.sex%type,
17 p_survey_date in date)
18 return number is
19
20 v_emp_count number(20) := 0;
21
22 cursor emp_count
23 (c_organization_id hr_organization_units.organization_id%type,
24 c_payroll_id pay_all_payrolls_f.payroll_id%type,
25 c_time_period_id per_time_periods.time_period_id%type,
26 c_location_id per_all_assignments_f.location_id%type,
27 c_emp_cat_code per_all_people_f.per_information7%type,
28 c_work_time_code per_all_people_f.per_information8%type,
29 c_sex per_all_people_f.sex%type,
30 c_survey_date date)
31 is
32 select count(*)
33 from hr_soft_coding_keyflex hsck,
34 pay_payrolls_f pay, /*Bug 2920728*/
35 per_time_periods ptp,
36 per_people_f pap, /*Bug 2920728*/
37 per_assignments_f paa /*Bug 2920728*/
38 where hsck.soft_coding_keyflex_id = paa.soft_coding_keyflex_id
39 and hsck.segment1 = to_char(c_organization_id)
40 and pay.payroll_id = c_payroll_id
41 and ptp.time_period_id = c_time_period_id
42 and paa.location_id = c_location_id
43 and pap.per_information7 = c_emp_cat_code
44 and pap.per_information8 = c_work_time_code
45 and pap.sex = c_sex
46 and ptp.regular_payment_date between paa.effective_start_date
47 and paa.effective_end_date
48 and pap.effective_start_date <= c_survey_date
49 and pap.effective_end_date >= c_survey_date
50 and pap.current_employee_flag ='Y'
51 and pay.payroll_id = ptp.payroll_id
52 and pap.person_id = paa.person_id
53 and pay.payroll_id = paa.payroll_id;
54
55 begin
56 open emp_count (p_organization_id,
57 p_payroll_id,
58 p_time_period_id,
59 p_location_id,
60 p_emp_cat_code,
61 p_work_time_code,
62 p_sex,
63 p_survey_date );
64 fetch emp_count into v_emp_count;
65 close emp_count;
66
67 return v_emp_count;
68 end count_employees;
69 ------------------------------------------------------------------------
70 -- Bug 1921492, will use Ordinary hours to calculate the hours worked
71 -- for employee to see if the employee is part time employee or full
72 -- time employee
73 ------------------------------------------------------------------------
74 function count_employees_using_balance
75 (p_organization_id in hr_organization_units.organization_id%type,
76 p_payroll_id in pay_all_payrolls_f.payroll_id%type,
77 p_time_period_id in per_time_periods.time_period_id%type,
78 p_location_id in per_all_assignments_f.location_id%type,
79 p_emp_cat_code in per_all_people_f.per_information7%type,
80 p_work_time_code in per_all_people_f.per_information8%type,
81 p_sex in per_all_people_f.sex%type,
82 p_week_hours in per_all_assignments_f.normal_hours%type,
83 p_week_frequency in per_all_assignments_f.frequency%type,
84 p_survey_date in date)
85 return number is
86
87 v_defined_balance_id pay_defined_balances.defined_balance_id%type;
88 v_emp_count number(20) := 0;
89 v_emp_balance number(20,5) := 0;
90 v_total_balance number(20,5) := 0;
91 v_full_time_count number(20) := 0;
92 v_part_time_count number(20) := 0;
93 v_standard_hours number(20) := 0;
94
95 cursor emp_balance
96 (c_organization_id hr_organization_units.organization_id%type,
97 c_payroll_id pay_all_payrolls_f.payroll_id%type,
98 c_time_period_id per_time_periods.time_period_id%type,
99 c_location_id per_all_assignments_f.location_id%type,
100 c_defined_balance_id pay_defined_balances.defined_balance_id%type,
101 c_sex per_all_people_f.sex%type,
102 c_survey_date date) is
103
104 select paa.assignment_id,
105 ptp.regular_payment_date,
106 pay.period_type payroll_frequency,
107 hr_nzbal.calc_all_balances(ptp.regular_payment_date,
108 paa.assignment_id,
109 c_defined_balance_id) balance
110 from hr_soft_coding_keyflex hsck,
111 pay_payrolls_f pay, /*Bug 2929728*/
112 per_time_periods ptp,
113 per_people_f pap, /*Bug 2920728*/
114 per_assignments_f paa /*Bug 2920728*/
115 where hsck.soft_coding_keyflex_id = paa.soft_coding_keyflex_id
116 and hsck.segment1 = to_char(c_organization_id)
117 and pay.payroll_id = c_payroll_id
118 and ptp.time_period_id = c_time_period_id
119 and paa.location_id = c_location_id
120 and pap.sex = c_sex
121 and ptp.regular_payment_date between paa.effective_start_date
122 and paa.effective_end_date
123 and pap.effective_start_date<=c_survey_date
124 and pap.effective_end_date >=c_survey_date
125 and pap.current_employee_flag='Y'
126 and pay.payroll_id = ptp.payroll_id
127 and pap.person_id = paa.person_id
128 and pay.payroll_id = paa.payroll_id
129 and pap.per_information7 = 'E';
130
131 begin
132
133 -- Determine the balance ID for OT hours .
134
135 v_defined_balance_id := id_for_defined_balance('Statistics NZ Ordinary Time Hours','_ASG_PTD');
136
137 for balance_rec in emp_balance (p_organization_id,
138 p_payroll_id,
139 p_time_period_id,
140 p_location_id,
141 v_defined_balance_id,
142 p_sex,
143 p_survey_date) loop
144
145 if balance_rec.balance = 0 then
146
147 -- Determine the Balance Id for OT Hours and check if the same
148 -- as Balance Id passed in
149
150 -- If it is then check if the OT Payout is 0 as well. If it is,
151 -- that's okay, but if not, then the Assignment is for a salaried
152 -- Employee, therefore determine the hours worked.
153 if ordinary_time_payout (balance_rec.regular_payment_date,
154 balance_rec.assignment_id) <> 0 then
155 v_emp_balance := hours_worked (balance_rec.assignment_id,
156 balance_rec.payroll_frequency);
157 else
158
159 -- Not OT Hours Balance OR OT Payout Balance = 0 as well.
160 v_emp_balance := balance_rec.balance;
161 end if;
162
163 else
164 v_emp_balance := balance_rec.balance;
165 end if;
166
167 v_standard_hours := convert_hours(p_week_hours,
168 balance_rec.payroll_frequency,
169 p_week_frequency);
170
171 if v_emp_balance > v_standard_hours then
172 v_full_time_count := v_full_time_count + 1;
173 elsif v_emp_balance <> 0 then
174 v_part_time_count := v_part_time_count + 1;
175 end if;
176
177
178 end loop;
179
180 if p_work_time_code = 'F' then
181 return v_full_time_count;
182 elsif p_work_time_code = 'P' then
183 return v_part_time_count;
184 end if;
185
186 end count_employees_using_balance;
187 ------------------------------------------------------------------------
188 -- Sums the Balance amount per group by each individual Assignment.
189 -- This is done so that if the Balance for Ordinary Time Hours is 0,
190 -- then a check can be performed to determine if the Balance for
191 -- Ordinary Time Payout is 0. If it is, then the Hours for the
192 -- Assignment are calculated, according to its Frequency and the
193 -- Payroll Frequency.
194 ------------------------------------------------------------------------
195 function sum_balances
196 (p_organization_id in hr_organization_units.organization_id%type,
197 p_payroll_id in pay_all_payrolls_f.payroll_id%type,
198 p_time_period_id in per_time_periods.time_period_id%type,
199 p_location_id in per_all_assignments_f.location_id%type,
200 p_defined_balance_id in pay_defined_balances.defined_balance_id%type,
201 p_sex in per_all_people_f.sex%type,
202 p_survey_date in date)
203 return number is
204
205 v_emp_balance number(20,5) := 0;
206 v_total_balance number(20,5) := 0;
207
208 cursor emp_balance
209 (c_organization_id hr_organization_units.organization_id%type,
210 c_payroll_id pay_all_payrolls_f.payroll_id%type,
211 c_time_period_id per_time_periods.time_period_id%type,
212 c_location_id per_all_assignments_f.location_id%type,
213 c_defined_balance_id pay_defined_balances.defined_balance_id%type,
214 c_sex per_all_people_f.sex%type,
215 c_survey_date date) is
216
217 select paa.assignment_id,
218 ptp.regular_payment_date,
219 pay.period_type payroll_frequency,
220 hr_nzbal.calc_all_balances(ptp.regular_payment_date,
221 paa.assignment_id,
222 c_defined_balance_id) balance
223 from hr_soft_coding_keyflex hsck,
224 pay_payrolls_f pay, /*Bug 2920728*/
225 per_time_periods ptp,
226 per_people_f pap, /*Bug 2920728*/
227 per_assignments_f paa /*Bug 2920728*/
228 where hsck.soft_coding_keyflex_id = paa.soft_coding_keyflex_id
229 and hsck.segment1 = to_char(c_organization_id)
230 and pay.payroll_id = c_payroll_id
231 and ptp.time_period_id = c_time_period_id
232 and paa.location_id = c_location_id
233 and pap.sex = c_sex
234 and ptp.regular_payment_date between paa.effective_start_date
235 and paa.effective_end_date
236 and pap.effective_start_date<=c_survey_date
237 and pap.effective_end_date >=c_survey_date
238 and pap.current_employee_flag='Y'
239 and pay.payroll_id = ptp.payroll_id
240 and pap.person_id = paa.person_id
241 and pay.payroll_id = paa.payroll_id
242 and pap.per_information7 = 'E';
243
244 begin
245 for balance_rec in emp_balance (p_organization_id,
246 p_payroll_id,
247 p_time_period_id,
248 p_location_id,
249 p_defined_balance_id,
250 p_sex,
251 p_survey_date) loop
252
253 if balance_rec.balance = 0 then
254
255 -- Determine the Balance Id for OT Hours and check if the same
256 -- as Balance Id passed in
257 if g_ordinary_time_hours_id is null then
258 g_ordinary_time_hours_id :=
259 id_for_defined_balance('Statistics NZ Ordinary Time Hours','_ASG_PTD');
260 end if;
261
262 -- If it is then check if the OT Payout is 0 as well. If it is,
263 -- that's okay, but if not, then the Assignment is for a salaried
264 -- Employee, therefore determine the hours worked.
265 if p_defined_balance_id = g_ordinary_time_hours_id and
266 ordinary_time_payout (balance_rec.regular_payment_date,
267 balance_rec.assignment_id) <> 0 then
268 v_emp_balance := hours_worked (balance_rec.assignment_id,
269 balance_rec.payroll_frequency);
270 else
271
272 -- Not OT Hours Balance OR OT Payout Balance = 0 as well.
273 v_emp_balance := balance_rec.balance;
274 end if;
275
276 else
277 v_emp_balance := balance_rec.balance;
278 end if;
279
280 v_total_balance := v_total_balance + v_emp_balance;
281 end loop;
282
283 return v_total_balance;
284 end sum_balances;
285 ------------------------------------------------------------------------
286 -- Returns the Defined Balance Id for the Balance Name and Database Item
287 -- Suffix passed in.
288 ------------------------------------------------------------------------
289 function id_for_defined_balance
290 (p_balance_name in pay_balance_types.balance_name%type,
291 p_balance_dimension in pay_balance_dimensions.database_item_suffix%type)
292 return pay_balance_types.balance_type_id%type is
293
294 v_balance_id pay_balance_types.balance_type_id%type;
295
296 cursor balance_id
297 (c_balance_name pay_balance_types.balance_name%type,
298 c_balance_dimension pay_balance_dimensions.database_item_suffix%type) is
299 select pdb.defined_balance_id
300 from pay_balance_dimensions pbd,
301 pay_defined_balances pdb,
302 pay_balance_types pbt
303 where pbt.balance_type_id = pdb.balance_type_id
304 and pbd.balance_dimension_id = pdb.balance_dimension_id
305 and pbt.balance_name = c_balance_name
306 and pbd.database_item_suffix = c_balance_dimension;
307
308 begin
309 open balance_id (p_balance_name, p_balance_dimension);
310 fetch balance_id into v_balance_id;
311 close balance_id;
312
313 return v_balance_id;
314 end id_for_defined_balance;
315 ------------------------------------------------------------------------
316 -- Returns the Balance for the Ordinary Time Payout for the Effective
317 -- Date and Assignment.
318 ------------------------------------------------------------------------
319 function ordinary_time_payout
320 (p_regular_payment_date in per_time_periods.regular_payment_date%type,
321 p_assignment_id in per_all_assignments_f.assignment_id%type)
322 return number is
323
324 v_payout number(20,5) := 0;
325
326 begin
327 if g_ordinary_time_payout_id is null then
328 g_ordinary_time_payout_id :=
329 id_for_defined_balance('Statistics NZ Ordinary Time Payout','_ASG_PTD');
333 p_assignment_id,
330 end if;
331
332 v_payout := hr_nzbal.calc_all_balances(p_regular_payment_date,
334 g_ordinary_time_payout_id);
335 return v_payout;
336 end ordinary_time_payout;
337 ------------------------------------------------------------------------
338 -- Returns the Hours for an Assignment. If the Assignment Frequency is
339 -- not equal to W (Week), then return 0. If the Assignment Frequency is
340 -- equal to W, but differs from the Payroll Frequency, then call
341 -- convert_hours.
342 ------------------------------------------------------------------------
343 function hours_worked
344 (p_assignment_id in per_all_assignments_f.assignment_id%type,
345 p_payroll_frequency in pay_all_payrolls_f.period_type%type)
346 return number is
347
348 v_hours_worked number(20,5) := 0;
349
350 cursor standard_conditions
351 (c_assignment_id per_all_assignments_f.assignment_id%type) is
352
353 select paa.normal_hours,
354 paa.frequency frequency_code, -- to pass to convert_hours
355 hl.meaning frequency -- to compare to payroll_frequency
356 from hr_lookups hl,
357 per_assignments_f paa /*Bug 2920728*/
358 where hl.application_id = 800
359 and paa.assignment_id = c_assignment_id
360 and paa.frequency = hl.lookup_code
361 and hl.lookup_type = 'FREQUENCY';
362
363 v_standard_conditions_rec standard_conditions%rowtype;
364 begin
365 open standard_conditions (p_assignment_id);
366 fetch standard_conditions into v_standard_conditions_rec;
367 close standard_conditions;
368
369 if v_standard_conditions_rec.frequency_code <> 'W' then
370 v_hours_worked := 0;
371 else
372 if v_standard_conditions_rec.frequency = p_payroll_frequency then
373 v_hours_worked := v_standard_conditions_rec.normal_hours;
374 else
375 v_hours_worked := convert_hours (v_standard_conditions_rec.normal_hours,
376 p_payroll_frequency,
377 v_standard_conditions_rec.frequency_code);
378 end if;
379 end if;
380
381 return v_hours_worked;
382 end hours_worked;
383 ------------------------------------------------------------------------
384 -- Converts the Assignment Hours so that it is of the same Frequency as
385 -- the Payroll Frequency. This function assumes there are 52 weeks per
386 -- year.
387 ------------------------------------------------------------------------
388 function convert_hours
389 (p_assignment_hours in per_all_assignments_f.normal_hours%type,
390 p_payroll_frequency in pay_all_payrolls_f.period_type%type,
391 p_assignment_frequency in per_all_assignments_f.frequency%type)
392 return number is
393
394 v_converted_hours number(20,5) := 0;
395 v_number_per_year per_time_period_types.number_per_fiscal_year%type := 52;
396 v_periods_per_year per_time_period_types.number_per_fiscal_year%type := 1;
397
398 begin
399 v_periods_per_year := no_periods_per_year (p_payroll_frequency);
400 v_converted_hours := p_assignment_hours /
401 v_periods_per_year *
402 v_number_per_year;
403
404 return v_converted_hours;
405 end convert_hours;
406 ------------------------------------------------------------------------
407 -- To determine which figures to use for conversion, the Payroll Period
408 -- Types needs to be queried to determine the number of Periods per
409 -- fiscal year.
410 ------------------------------------------------------------------------
411 function no_periods_per_year
412 (p_period_type in per_time_period_types.period_type%type)
413 return number is
414
415 v_number_per_year per_time_period_types.number_per_fiscal_year%type := 0;
416
417 cursor no_of_periods
418 (c_period_type per_time_period_types.period_type%type) is
419
420 select number_per_fiscal_year
421 from per_time_period_types
422 where period_type = c_period_type;
423
424 begin
425 open no_of_periods (p_period_type);
426 fetch no_of_periods into v_number_per_year;
427 close no_of_periods;
428
429 return v_number_per_year;
430 end no_periods_per_year;
431 ------------------------------------------------------------------------
432 end pay_nz_qes_pkg;
[Home] [Help]