1 package body pay_nz_rec_pkg as
2 /* $Header: pynzrec.pkb 120.3 2005/09/21 01:55:42 snekkala noship $ */
3 ------------------------------------------------------------------------
4 -- Selects the Regular Payment Date for the Period Id that is passed in.
5 ------------------------------------------------------------------------
6 function period_payment_date
7 (p_time_period_id in pay_payroll_actions.time_period_id%type)
8 return per_time_periods.regular_payment_date%type is
9
10 v_payment_date per_time_periods.regular_payment_date%type;
11
12 cursor payment_date
13 (c_time_period_id in pay_payroll_actions.time_period_id%type) is
14
15 select regular_payment_date
16 from per_time_periods
17 where time_period_id = c_time_period_id;
18
19 begin
20 open payment_date (p_time_period_id);
21 fetch payment_date into v_payment_date;
22 close payment_date;
23
24 return v_payment_date;
25 end period_payment_date;
26
27 ------------------------------------------------------------------------
28 -- Selects the first Action Sequence of the current Financial Year for
29 -- that Assignment. The start of the Financial Year is obtained from the
30 -- call to another package function.
31 ------------------------------------------------------------------------
32 function first_action_sequence
33 (p_assignment_id in pay_assignment_actions.assignment_id%type,
34 p_time_period_id in pay_payroll_actions.time_period_id%type)
35 return pay_assignment_actions.action_sequence%type is
36
37 v_action_sequence pay_assignment_actions.action_sequence%type := null;
38 v_payment_date per_time_periods.regular_payment_date%type := null;
39
40 v_start_of_year_day constant varchar2(5) := '01-04';
41
42 /*Bug #3306269 - Added per_assignments_f with date_effective checks, and
43 added action_status check for ppa and pac */
44 /* Bug #4200412 - Added p_time_period_id */
45 cursor min_sequence
46 (c_assignment_id in pay_assignment_actions.assignment_id%type,
47 c_period_payment_date in per_time_periods.regular_payment_date%type) is
48
49 select min(pac.action_sequence)
50 from per_assignments_f paf,
51 per_time_periods ptp,
52 pay_payroll_actions ppa,
53 pay_assignment_actions pac
54 where paf.assignment_id = c_assignment_id
55 and pac.assignment_id = paf.assignment_id
56 and ptp.time_period_id = ppa.time_period_id
57 AND ppa.time_period_id = p_time_period_id
58 AND ppa.time_period_id = ptp.time_period_id
59 and ppa.payroll_action_id = pac.payroll_action_id
60 and ptp.regular_payment_date between paf.effective_Start_date
61 and paf.effective_end_date
62 and pac.action_status = 'C'
63 and ppa.action_status = 'C'
64 and ptp.regular_payment_date >=
65 hr_nz_routes.span_start(c_period_payment_date,
66 1, v_start_of_year_day);
67
68 begin
69 v_payment_date := period_payment_date (p_time_period_id);
70
71 if v_payment_date is not null then
72 open min_sequence (p_assignment_id, v_payment_date);
73 fetch min_sequence into v_action_sequence;
74 close min_sequence;
75 end if;
76
77 return v_action_sequence;
78 end first_action_sequence;
79
80 ------------------------------------------------------------------------
81 -- Selects the current Action Sequence for that Assignment.
82 ------------------------------------------------------------------------
83 function last_action_sequence
84 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
85 p_assignment_id in pay_assignment_actions.assignment_id%type)
86 return pay_assignment_actions.action_sequence%type is
87
88 v_action_sequence pay_assignment_actions.action_sequence%type;
89
90 cursor max_sequence
91 (c_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
92 c_assignment_id in pay_assignment_actions.assignment_id%type) is
93
94 select pac.action_sequence
95 from pay_payroll_actions ppa,
96 pay_assignment_actions pac
97 where ppa.payroll_action_id = pac.payroll_action_id
98 and ppa.payroll_action_id = c_payroll_action_id
99 and pac.assignment_id = c_assignment_id;
100
101 begin
102 open max_sequence (p_payroll_action_id, p_assignment_id);
103 fetch max_sequence into v_action_sequence;
107 end last_action_sequence;
104 close max_sequence;
105
106 return v_action_sequence;
108
109 ------------------------------------------------------------------------
110 -- Sums the Result Values for the Period-To-Date. This will be called for
111 -- Elements that are the only feed for their Balance.
112 ------------------------------------------------------------------------
113 function result_ptd
114 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
115 p_assignment_id in pay_assignment_actions.assignment_id%type,
116 p_element_type_id in pay_element_types_f.element_type_id%type)
117 return number is
118
119 v_input_value_name constant pay_input_values_f.name%type := 'Pay Value';
120 v_uom constant pay_input_values_f.uom%type := 'M';
121
122 -- It makes sense to only sum values that have Money as a Unit of Measure
123 v_ptd_results number := 0;
124
125 cursor sum_results
126 (c_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
127 c_assignment_id in pay_assignment_actions.assignment_id%type,
128 c_element_type_id in pay_element_types_f.element_type_id%type) is
129
130 select sum(prrv.result_value)
131 from pay_payroll_actions ppa,
132 pay_assignment_actions pac,
133 pay_run_results prr,
134 pay_run_result_values prrv,
135 pay_element_types_f pet,
136 pay_input_values_f piv
137 where ppa.payroll_action_id = c_payroll_action_id
138 and pac.assignment_id = c_assignment_id
139 and pet.element_type_id = c_element_type_id
140 and piv.uom = v_uom
141 and piv.name = v_input_value_name
142 and ppa.payroll_action_id = pac.payroll_action_id
143 and pac.assignment_action_id = prr.assignment_action_id
144 and prr.run_result_id = prrv.run_result_id
145 and pet.element_type_id = prr.element_type_id
146 and pet.element_type_id = piv.element_type_id
147 and piv.input_value_id = prrv.input_value_id;
148
149 begin
150 open sum_results (p_payroll_action_id, p_assignment_id, p_element_type_id);
151 fetch sum_results into v_ptd_results;
152
153 -- Bug 3776051 Changes start
154 -- Sparse matrix
155 --
156 IF sum_results%NOTFOUND THEN
157 v_ptd_results:=0;
158 END IF;
159
160 close sum_results;
161
162 return nvl(v_ptd_results,0);
163 --
164 -- Bug 3776051 Changes end
165 --
166
167 end result_ptd;
168
169 ------------------------------------------------------------------------
170 -- Sums the Result Values for the Year-To-Date. This will be called for
171 -- Elements that are the only feed for their Balance.
172 ------------------------------------------------------------------------
173 function result_ytd
174 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
175 p_assignment_id in pay_assignment_actions.assignment_id%type,
176 p_element_type_id in pay_element_types_f.element_type_id%type)
177 return number is
178
179 v_input_value_name constant pay_input_values_f.name%type := 'Pay Value';
180 v_uom constant pay_input_values_f.uom%type := 'M';
181
182 -- It makes sense to only sum values that have Money as a Unit of Measure
183 v_ytd_results number := 0;
184 v_time_period_id per_time_periods.time_period_id%type;
185
186 CURSOR sum_results
187 (c_payroll_action_id IN pay_payroll_actions.payroll_action_id%TYPE,
188 c_assignment_id IN pay_assignment_actions.assignment_id%TYPE,
189 c_element_type_id IN pay_element_types_f.element_type_id%TYPE,
190 c_time_period_id IN per_time_periods.time_period_id%TYPE)
191 IS
192 SELECT SUM(prrv.result_value)
193 FROM pay_payroll_actions ppa
194 , pay_assignment_actions pac
195 , pay_run_results prr
196 , pay_run_result_values prrv
197 , pay_element_types_f pet
198 , pay_input_values_f piv
199 WHERE pac.assignment_id = c_assignment_id
200 AND pet.element_type_id = c_element_type_id
201 AND piv.uom = v_uom
202 AND piv.name = v_input_value_name
203 AND ppa.payroll_action_id = pac.payroll_action_id
204 AND ppa.time_period_id <= c_time_period_id
205 AND ppa.payroll_action_id <= c_payroll_action_id
206 AND pac.assignment_action_id = prr.assignment_action_id
207 AND prr.run_result_id = prrv.run_result_id
208 AND pet.element_type_id = prr.element_type_id
209 AND pet.element_type_id = piv.element_type_id
210 AND piv.input_value_id = prrv.input_value_id
211 AND pac.action_sequence >= first_action_sequence(pac.assignment_id, c_time_period_id)
212 AND pac.action_sequence <= last_action_sequence(c_payroll_action_id, pac.assignment_id);
213
214 cursor get_time_period_id (c_payroll_action_id in
215 pay_payroll_actions.payroll_action_id%type) is
216 select time_period_id from pay_payroll_actions
217 where payroll_action_id = c_payroll_action_id;
218 begin
219 open get_time_period_id(p_payroll_action_id);
220 fetch get_time_period_id into v_time_period_id;
221 close get_time_period_id;
222
223 open sum_results (p_payroll_action_id, p_assignment_id, p_element_type_id,v_time_period_id);
224
225 fetch sum_results into v_ytd_results;
226
227 -- Bug 3776051 Changes start
231 v_ytd_results:=0;
228 -- Sparse matrix
229 --
230 IF sum_results%NOTFOUND THEN
232 END IF;
233 --
234 -- Bug 3776051 Changes end
235 --
236
237 close sum_results;
238
239 return nvl(v_ytd_results,0);
240 end result_ytd;
241
242 ------------------------------------------------------------------------
243 -- This function returns TRUE if the Element is the only Feed to its
244 -- Balance. Otherwise it returns FALSE. This is determined by executing
245 -- the cursor and if a row is returned, then it must be a single balance
246 -- feed, so return the necessary parameters required to call
247 -- hr_nzbal.calc_asg_ytd. Otherwise no records will be returned.
248 ------------------------------------------------------------------------
249 function single_feed_balance
250 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
251 p_assignment_id in pay_assignment_actions.assignment_id%type,
252 p_element_type_id in pay_element_types_f.element_type_id%type,
253 p_assignment_action_id out nocopy pay_assignment_actions.assignment_action_id%type,
254 p_balance_type_id out nocopy pay_balance_types.balance_type_id%type,
255 p_effective_start_date out nocopy pay_balance_feeds_f.effective_start_date%type)
256 return boolean is
257
258 v_input_value_name constant pay_input_values_f.name%type := 'Pay Value';
259 v_uom constant pay_input_values_f.uom%type := 'M';
260
261 v_single_feed boolean := FALSE;
262
263 cursor single_balance_feed
264 (c_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
265 c_assignment_id in pay_assignment_actions.assignment_id%type,
266 c_element_type_id in pay_element_types_f.element_type_id%type) is
267
268 select pac.assignment_action_id,
269 pbf.balance_type_id,
270 pbf.effective_start_date
271 from pay_element_types_f pet,
272 pay_input_values_f piv,
273 pay_balance_feeds_f pbf,
274 pay_payroll_actions ppa,
275 pay_assignment_actions pac,
276 pay_run_results prr
277 where ppa.payroll_action_id = c_payroll_action_id
278 and pet.element_type_id = c_element_type_id
279 and pac.assignment_id = c_assignment_id
280 and piv.name = v_input_value_name
281 and piv.uom = v_uom
282 and ppa.payroll_action_id = pac.payroll_action_id
283 and pac.assignment_action_id = prr.assignment_action_id
284 and pet.element_type_id = piv.element_type_id
285 and pet.element_type_id = prr.element_type_id
286 and piv.input_value_id = pbf.input_value_id
287 and not exists (select null
288 from pay_balance_feeds_f pbf_not
289 where pbf_not.balance_feed_id <> pbf.balance_feed_id
290 and pbf_not.balance_type_id = pbf.balance_type_id
291 and (ppa.effective_date between pbf_not.effective_start_date
292 and pbf_not.effective_end_date))
293 and not exists (select null
294 from pay_balance_classifications pbc_not
295 where pbc_not.balance_type_id = pbf.balance_type_id);
296
297 begin
298 open single_balance_feed (p_payroll_action_id,
299 p_assignment_id,
300 p_element_type_id);
301 fetch single_balance_feed into p_assignment_action_id,
302 p_balance_type_id,
303 p_effective_start_date;
304
305 if single_balance_feed%notfound then
306 close single_balance_feed;
307 v_single_feed := FALSE;
308 else
309 close single_balance_feed;
310 v_single_feed := TRUE;
311 end if;
312
313 return v_single_feed;
314 end single_feed_balance;
315
316 ------------------------------------------------------------------------
317 -- Checks to see if the Balance is fed by a single Element by calling
318 -- single_balance_feed. If it is, then the Balance PTD function (in the
319 -- hr_nzbal package) is called. Otherwise, the Result PTD function in this
320 -- package is called.
321 ------------------------------------------------------------------------
322 function value_ptd
323 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
324 p_assignment_id in pay_assignment_actions.assignment_id%type,
325 p_element_type_id in pay_element_types_f.element_type_id%type)
326 return number is
327
328 p_assignment_action_id pay_assignment_actions.assignment_action_id%type;
329 p_balance_type_id pay_balance_types.balance_type_id%type;
330 p_effective_start_date pay_balance_feeds_f.effective_start_date%type;
331 v_value_ptd number := 0;
332
333 begin
334 if single_feed_balance (p_payroll_action_id,
335 p_assignment_id,
336 p_element_type_id,
337 p_assignment_action_id,
338 p_balance_type_id,
339 p_effective_start_date) then
340
341 v_value_ptd := hr_nzbal.calc_asg_ptd
342 (p_assignment_action_id, p_balance_type_id,
343 p_effective_start_date, p_assignment_id);
344 else
345 v_value_ptd := result_ptd (p_payroll_action_id,
346 p_assignment_id,
347 p_element_type_id);
348 end if;
349
350 return v_value_ptd;
351 end value_ptd;
352
353 ------------------------------------------------------------------------
354 -- Checks to see if the Balance is fed by a single Element by calling
355 -- single_balance_feed. If it is, then the Balance YTD function (in the
356 -- hr_nzbal package) is called. Otherwise, the Result YTD function in this
357 -- package is called.
358 ------------------------------------------------------------------------
359 function value_ytd
360 (p_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
361 p_assignment_id in pay_assignment_actions.assignment_id%type,
362 p_element_type_id in pay_element_types_f.element_type_id%type)
363 return number is
364
365 p_assignment_action_id pay_assignment_actions.assignment_action_id%type;
366 p_balance_type_id pay_balance_types.balance_type_id%type;
367 p_effective_start_date pay_balance_feeds_f.effective_start_date%type;
368 v_value_ytd number := 0;
369
370 begin
371 if single_feed_balance (p_payroll_action_id,
372 p_assignment_id,
373 p_element_type_id,
374 p_assignment_action_id,
375 p_balance_type_id,
376 p_effective_start_date) then
377
378 v_value_ytd := hr_nzbal.calc_asg_ytd
379 (p_assignment_action_id, p_balance_type_id,
380 p_effective_start_date, p_assignment_id);
381 else
382 v_value_ytd := result_ytd (p_payroll_action_id,
383 p_assignment_id,
384 p_element_type_id);
385 end if;
386
387 return nvl( v_value_ytd,0);
388 end value_ytd;
389 ------------------------------------------------------------------------
390 end pay_nz_rec_pkg;
[Home] [Help]