[Home] [Help]
PACKAGE BODY: APPS.PY_ZA_ROUTES
Source
1 PACKAGE BODY py_za_routes AS
2 /* $Header: pyzarout.pkb 120.2 2005/07/04 02:29:29 kapalani noship $ */
3 ------------------------------------------------------------------------------
4 --
5 -- Summed data for the ASSIGNMENT-LEVEL PAYROLL TAX PERIOD balance dimension
6 --
7 FUNCTION ASG_TAX_PTD (p_assignment_action_id NUMBER,
8 p_balance_type_id NUMBER)
9 RETURN NUMBER
10 IS
11 --
12 l_balance NUMBER;
13 --
14 cursor cur_ASG_TAX_PTD (assact_id IN NUMBER,
15 baltype IN NUMBER) is
16 --
17 select
18 nvl(sum(TARGET.result_value * FEED.scale),0)
19 from
20 pay_balance_feeds_f FEED,
21 pay_run_result_values TARGET,
22 pay_run_results RR,
23 pay_payroll_actions PACT,
24 pay_assignment_actions ASSACT,
25 pay_payroll_actions BACT,
26 pay_assignment_actions BAL_ASSACT
27 where BAL_ASSACT.assignment_action_id = assact_id
28 and BAL_ASSACT.payroll_action_id = BACT.payroll_action_id
29 and FEED.balance_type_id = baltype
30 and FEED.input_value_id = TARGET.input_value_id
31 and TARGET.run_result_id = RR.run_result_id
32 and RR.assignment_action_id = ASSACT.assignment_action_id
33 and ASSACT.payroll_action_id = PACT.payroll_action_id
34 and PACT.effective_date between
35 FEED.effective_start_date and FEED.effective_end_date
36 and RR.status in ('P','PA')
37 and PACT.time_period_id = BACT.time_period_id
38 and ASSACT.action_sequence <= BAL_ASSACT.action_sequence
39 and ASSACT.assignment_id = BAL_ASSACT.assignment_id;
40 --
41 BEGIN
42 --
43 open cur_ASG_TAX_PTD(p_assignment_action_id, p_balance_type_id);
44 FETCH cur_ASG_TAX_PTD INTO l_balance;
45 close cur_ASG_TAX_PTD;
46 --
47 RETURN l_balance;
48 --
49 END ASG_TAX_PTD;
50 --
51 ------------------------------------------------------------------------------
52 --
53 -- Summed data for the ASSIGNMENT-LEVEL PAYROLL TAX YEAR balance dimension
54 --
55 FUNCTION ASG_TAX_YTD(p_assignment_action_id NUMBER,
56 p_balance_type_id NUMBER)
57 RETURN NUMBER
58 IS
59 --
60 l_balance NUMBER;
61 --
62 cursor cur_ASG_TAX_YTD (assact_id IN NUMBER,
63 baltype IN NUMBER) is
64 --
65 select
66 nvl(sum(TARGET.result_value * FEED.scale),0)
67 from
68 pay_balance_feeds_f FEED,
69 pay_run_result_values TARGET,
70 pay_run_results RR,
71 pay_payroll_actions PACT,
72 pay_assignment_actions ASSACT,
73 pay_payroll_actions BACT,
74 per_time_periods BPTP,
75 per_time_periods PPTP,
76 pay_assignment_actions BAL_ASSACT
77 where BAL_ASSACT.assignment_action_id = assact_id
78 and BAL_ASSACT.payroll_action_id = BACT.payroll_action_id
79 and FEED.balance_type_id = baltype
80 and FEED.input_value_id = TARGET.input_value_id
81 and TARGET.run_result_id = RR.run_result_id
82 and RR.assignment_action_id = ASSACT.assignment_action_id
83 and ASSACT.payroll_action_id = PACT.payroll_action_id
84 and PACT.effective_date between
85 FEED.effective_start_date and FEED.effective_end_date
86 and BACT.time_period_id = BPTP.time_period_id
87 and PACT.time_period_id = PPTP.time_period_id
88 and RR.status in ('P','PA')
89 and PPTP.prd_information1 = BPTP.prd_information1
90 and ASSACT.action_sequence <= BAL_ASSACT.action_sequence
91 and ASSACT.assignment_id = BAL_ASSACT.assignment_id;
92 --
93 BEGIN
94 --
95 open cur_ASG_TAX_YTD(p_assignment_action_id, p_balance_type_id);
96 FETCH cur_ASG_TAX_YTD INTO l_balance;
97 close cur_ASG_TAX_YTD;
98 --
99 RETURN l_balance;
100 --
101 END ASG_TAX_YTD;
102 --
103 -----------------------------------------------------------------------------
104 --
105 -- Summed data for the ASSIGNMENT-LEVEL PAYROLL TAX QUARTER balance dimension
106 --
107 FUNCTION ASG_TAX_QTD(p_assignment_action_id NUMBER,
108 p_balance_type_id NUMBER)
109 RETURN NUMBER
110 IS
111 --
112 l_balance NUMBER;
113 --
114 cursor cur_ASG_TAX_QTD (assact_id IN NUMBER,
115 baltype IN NUMBER) is
116 --
117 select
118 nvl(sum(TARGET.result_value * FEED.scale),0)
119 from
120 pay_balance_feeds_f FEED,
121 pay_run_result_values TARGET,
122 pay_run_results RR,
123 pay_payroll_actions PACT,
124 pay_assignment_actions ASSACT,
125 pay_payroll_actions BACT,
126 per_time_periods BPTP,
127 per_time_periods PPTP,
128 pay_assignment_actions BAL_ASSACT
129 where BAL_ASSACT.assignment_action_id = assact_id
130 and BAL_ASSACT.payroll_action_id = BACT.payroll_action_id
131 and FEED.balance_type_id = baltype
132 and FEED.input_value_id = TARGET.input_value_id
133 and TARGET.run_result_id = RR.run_result_id
134 and RR.assignment_action_id = ASSACT.assignment_action_id
135 and ASSACT.payroll_action_id = PACT.payroll_action_id
136 and PACT.effective_date between
137 FEED.effective_start_date and FEED.effective_end_date
138 and BACT.time_period_id = BPTP.time_period_id
139 and PACT.time_period_id = PPTP.time_period_id
140 and RR.status in ('P','PA')
141 and PPTP.prd_information1 = BPTP.prd_information1
142 and PPTP.prd_information2 = BPTP.prd_information2
143 and ASSACT.action_sequence <= BAL_ASSACT.action_sequence
144 and ASSACT.assignment_id = BAL_ASSACT.assignment_id;
145 --
146 BEGIN
147 --
148 open cur_ASG_TAX_QTD(p_assignment_action_id, p_balance_type_id);
149 FETCH cur_ASG_TAX_QTD INTO l_balance;
150 close cur_ASG_TAX_QTD;
151 --
152 RETURN l_balance;
153 --
154 END ASG_TAX_QTD;
155 --
156 -----------------------------------------------------------------------------
157 --
158 -- Summed data for the ASSIGNMENT-LEVEL PAYROLL TAX MONTH balance dimension
159 --
160 FUNCTION ASG_TAX_MTD(p_assignment_action_id NUMBER,
161 p_balance_type_id NUMBER)
162 RETURN NUMBER
163 IS
164 --
165 l_balance NUMBER;
166 --
167 cursor cur_ASG_TAX_MTD (assact_id IN NUMBER,
168 baltype IN NUMBER) is
169 --
170 select
171 nvl(sum(TARGET.result_value * FEED.scale),0)
172 from
173 pay_balance_feeds_f FEED,
174 pay_run_result_values TARGET,
175 pay_run_results RR,
176 pay_payroll_actions PACT,
177 pay_assignment_actions ASSACT,
178 pay_payroll_actions BACT,
179 per_time_periods BPTP,
180 per_time_periods PPTP,
181 pay_assignment_actions BAL_ASSACT
182 where BAL_ASSACT.assignment_action_id = assact_id
183 and BAL_ASSACT.payroll_action_id = BACT.payroll_action_id
184 and FEED.balance_type_id = baltype
185 and FEED.input_value_id = TARGET.input_value_id
186 and TARGET.run_result_id = RR.run_result_id
187 and RR.assignment_action_id = ASSACT.assignment_action_id
188 and ASSACT.payroll_action_id = PACT.payroll_action_id
189 and PACT.effective_date between
190 FEED.effective_start_date and FEED.effective_end_date
191 and BACT.time_period_id = BPTP.time_period_id
192 and PACT.time_period_id = PPTP.time_period_id
193 and RR.status in ('P','PA')
194 and PPTP.pay_advice_date = BPTP.pay_advice_date
195 and ASSACT.action_sequence <= BAL_ASSACT.action_sequence
196 and ASSACT.assignment_id = BAL_ASSACT.assignment_id;
197 --
198 BEGIN
199 --
200 open cur_ASG_TAX_MTD(p_assignment_action_id, p_balance_type_id);
201 FETCH cur_ASG_TAX_MTD INTO l_balance;
202 close cur_ASG_TAX_MTD;
203 --
204 RETURN l_balance;
205 --
206 END ASG_TAX_MTD;
207 --
208 --------------------------------------------------------------------------------
209 --------------------------------------------------------------------------------
210 --
211 -- Summed data for the ASSIGNMENT-LEVEL PAYROLL CALENDAR PERIOD balance dimension
212 --
213 FUNCTION ASG_CAL_PTD (p_assignment_action_id NUMBER,
214 p_balance_type_id NUMBER)
215 RETURN NUMBER
216 IS
217 --
218 l_balance NUMBER;
219 --
220 cursor cur_ASG_CAL_PTD (assact_id IN NUMBER,
221 baltype IN NUMBER) is
222 --
223 select
224 nvl(sum(TARGET.result_value * FEED.scale),0)
225 from
226 pay_balance_feeds_f FEED,
227 pay_run_result_values TARGET,
228 pay_run_results RR,
229 pay_payroll_actions PACT,
230 pay_assignment_actions ASSACT,
231 pay_payroll_actions BACT,
232 pay_assignment_actions BAL_ASSACT
233 where BAL_ASSACT.assignment_action_id = assact_id
234 and BAL_ASSACT.payroll_action_id = BACT.payroll_action_id
235 and FEED.balance_type_id = baltype
236 and FEED.input_value_id = TARGET.input_value_id
237 and TARGET.run_result_id = RR.run_result_id
238 and RR.assignment_action_id = ASSACT.assignment_action_id
239 and ASSACT.payroll_action_id = PACT.payroll_action_id
240 and PACT.effective_date between
241 FEED.effective_start_date and FEED.effective_end_date
242 and RR.status in ('P','PA')
243 and PACT.time_period_id = BACT.time_period_id
244 and ASSACT.action_sequence <= BAL_ASSACT.action_sequence
245 and ASSACT.assignment_id = BAL_ASSACT.assignment_id;
246 --
247 BEGIN
248 --
249 open cur_ASG_CAL_PTD(p_assignment_action_id, p_balance_type_id);
250 FETCH cur_ASG_CAL_PTD INTO l_balance;
251 close cur_ASG_CAL_PTD;
252 --
253 RETURN l_balance;
254 --
255 END ASG_CAL_PTD;
256 --
257 --------------------------------------------------------------------------------
258 --
259 -- Summed data for the ASSIGNMENT-LEVEL PAYROLL CALENDAR YEAR balance dimension
260 --
261 FUNCTION ASG_CAL_YTD (p_assignment_action_id NUMBER,
262 p_balance_type_id NUMBER)
263 RETURN NUMBER
264 IS
265 --
266 l_balance NUMBER;
267 --
268 cursor cur_ASG_CAL_YTD (assact_id IN NUMBER,
269 baltype IN NUMBER) is
270 --
271 select
272 nvl(sum(TARGET.result_value * FEED.scale),0)
273 from
274 pay_balance_feeds_f FEED,
275 pay_run_result_values TARGET,
276 pay_run_results RR,
277 pay_payroll_actions PACT,
278 pay_assignment_actions ASSACT,
279 pay_payroll_actions BACT,
280 per_time_periods BPTP,
281 per_time_periods PPTP,
282 pay_assignment_actions BAL_ASSACT
283 where BAL_ASSACT.assignment_action_id = assact_id
284 and BAL_ASSACT.payroll_action_id = BACT.payroll_action_id
285 and FEED.balance_type_id = baltype
286 and FEED.input_value_id = TARGET.input_value_id
287 and TARGET.run_result_id = RR.run_result_id
288 and RR.assignment_action_id = ASSACT.assignment_action_id
289 and ASSACT.payroll_action_id = PACT.payroll_action_id
290 and PACT.effective_date between
291 FEED.effective_start_date and FEED.effective_end_date
292 and BACT.time_period_id = BPTP.time_period_id
293 and PACT.time_period_id = PPTP.time_period_id
294 and RR.status in ('P','PA')
295 and PPTP.prd_information3 = BPTP.prd_information3
296 and ASSACT.action_sequence <= BAL_ASSACT.action_sequence
297 and ASSACT.assignment_id = BAL_ASSACT.assignment_id;
298 --
299 BEGIN
300 --
301 open cur_ASG_CAL_YTD(p_assignment_action_id, p_balance_type_id);
302 FETCH cur_ASG_CAL_YTD INTO l_balance;
303 close cur_ASG_CAL_YTD;
304 --
305 RETURN l_balance;
306 --
307 END ASG_CAL_YTD;
308 --
309 --------------------------------------------------------------------------------
310 --
311 -- Summed data for the ASSIGNMENT-LEVEL PAYROLL CALENDAR MONTH balance dimension
312 --
313 FUNCTION ASG_CAL_MTD (p_assignment_action_id NUMBER,
314 p_balance_type_id NUMBER)
315 RETURN NUMBER
316 IS
317 --
318 l_balance NUMBER;
319 --
320 cursor cur_ASG_CAL_MTD (assact_id IN NUMBER,
321 baltype IN NUMBER) is
322 --
323 select
324 nvl(sum(TARGET.result_value * FEED.scale),0)
325 from
326 pay_balance_feeds_f FEED,
327 pay_run_result_values TARGET,
328 pay_run_results RR,
329 pay_payroll_actions PACT,
330 pay_assignment_actions ASSACT,
331 pay_payroll_actions BACT,
332 per_time_periods BPTP,
333 per_time_periods PPTP,
334 pay_assignment_actions BAL_ASSACT
335 where BAL_ASSACT.assignment_action_id = assact_id
336 and BAL_ASSACT.payroll_action_id = BACT.payroll_action_id
337 and FEED.balance_type_id = baltype
338 and FEED.input_value_id = TARGET.input_value_id
339 and TARGET.run_result_id = RR.run_result_id
340 and RR.assignment_action_id = ASSACT.assignment_action_id
341 and ASSACT.payroll_action_id = PACT.payroll_action_id
342 and PACT.effective_date between
343 FEED.effective_start_date and FEED.effective_end_date
344 and BACT.time_period_id = BPTP.time_period_id
345 and PACT.time_period_id = PPTP.time_period_id
346 and RR.status in ('P','PA')
347 and PPTP.pay_advice_date = BPTP.pay_advice_date
348 and ASSACT.action_sequence <= BAL_ASSACT.action_sequence
349 and ASSACT.assignment_id = BAL_ASSACT.assignment_id;
350 --
351 BEGIN
352 --
353 open cur_ASG_CAL_MTD(p_assignment_action_id, p_balance_type_id);
354 FETCH cur_ASG_CAL_MTD INTO l_balance;
355 close cur_ASG_CAL_MTD;
356 --
357 RETURN l_balance;
358 --
359 END ASG_CAL_MTD;
360 --
361 --------------------------------------------------------------------------------
362 --------------------------------------------------------------------------------
363 --
364 -- Summed data for the ASSIGNMENT-LEVEL PAYROLL INCEPTION-TO-DATE balance dimension
365 --
366 FUNCTION ASG_ITD (p_assignment_action_id NUMBER,
367 p_balance_type_id NUMBER)
368 RETURN NUMBER
369 IS
370 --
371 l_balance NUMBER;
372 --
373 cursor cur_ASG_ITD (assact_id IN NUMBER,
374 baltype IN NUMBER) is
375 --
376 select
377 nvl(sum(TARGET.result_value * FEED.scale),0)
381 pay_run_results RR,
378 from
379 pay_balance_feeds_f FEED,
380 pay_run_result_values TARGET,
382 pay_payroll_actions PACT,
383 pay_assignment_actions ASSACT,
384 pay_payroll_actions BACT,
385 pay_assignment_actions BAL_ASSACT
386 where BAL_ASSACT.assignment_action_id = assact_id
387 and BAL_ASSACT.payroll_action_id = BACT.payroll_action_id
388 and FEED.balance_type_id = baltype
389 and FEED.input_value_id = TARGET.input_value_id
390 and TARGET.run_result_id = RR.run_result_id
391 and RR.assignment_action_id = ASSACT.assignment_action_id
392 and ASSACT.payroll_action_id = PACT.payroll_action_id
393 and PACT.effective_date between
394 FEED.effective_start_date and FEED.effective_end_date
395 and RR.status in ('P','PA')
396 and ASSACT.action_sequence <= BAL_ASSACT.action_sequence
397 and ASSACT.assignment_id = BAL_ASSACT.assignment_id;
398 --
399 BEGIN
400 --
401 open cur_ASG_ITD(p_assignment_action_id, p_balance_type_id);
402 FETCH cur_ASG_ITD INTO l_balance;
403 close cur_ASG_ITD;
404 --
405 RETURN l_balance;
406 --
407 END ASG_ITD;
408 --
409 --------------------------------------------------------------------------------
410 --------------------------------------------------------------------------------
411 --
412 -- Summed data for the ASSIGNMENT-LEVEL PAYROLL RUN balance dimension
413 --
414 FUNCTION ASG_RUN (p_assignment_action_id NUMBER,
415 p_balance_type_id NUMBER)
416 RETURN NUMBER
417 IS
418 --
419 l_balance NUMBER;
420 --
421 cursor cur_ASG_RUN (assact_id IN NUMBER,
422 baltype IN NUMBER) is
423 --
424 select
425 nvl(sum(TARGET.result_value * FEED.scale),0)
426 from
427 pay_balance_feeds_f FEED,
428 pay_run_result_values TARGET,
429 pay_run_results RR,
430 pay_payroll_actions PACT,
431 pay_assignment_actions ASSACT
432 where ASSACT.assignment_action_id = assact_id
433 and FEED.balance_type_id = baltype
434 and FEED.input_value_id = TARGET.input_value_id
435 and TARGET.run_result_id = RR.run_result_id
436 and RR.assignment_action_id = ASSACT.assignment_action_id
437 and ASSACT.payroll_action_id = PACT.payroll_action_id
438 and PACT.effective_date between
439 FEED.effective_start_date and FEED.effective_end_date
440 and RR.status in ('P','PA');
441 --
442 BEGIN
443 --
444 open cur_ASG_RUN(p_assignment_action_id, p_balance_type_id);
445 FETCH cur_ASG_RUN INTO l_balance;
446 close cur_ASG_RUN;
447 --
448 RETURN l_balance;
449 --
450 END ASG_RUN;
451 --
452 --------------------------------------------------------------------------------
453 --------------------------------------------------------------------------------
454 --
455 -- Summed data for the ASSIGNMENT-LEVEL PAYROLL PAYMENTS balance dimension
456 --
457 FUNCTION PAYMENTS (p_assignment_action_id NUMBER,
458 p_balance_type_id NUMBER)
459 RETURN NUMBER
460 IS
461 --
462 l_balance NUMBER;
463 --
464 cursor cur_PAYMENTS (assact_id IN NUMBER,
465 baltype IN NUMBER) is
466 --
467 select
468 nvl(sum(TARGET.result_value * FEED.scale),0)
469 from
470 pay_balance_feeds_f FEED,
471 pay_run_result_values TARGET,
472 pay_run_results RR,
473 pay_payroll_actions PACT,
474 pay_assignment_actions ASSACT,
475 pay_action_interlocks INTLK,
476 pay_payroll_actions BACT,
477 pay_assignment_actions BAL_ASSACT
478 where BAL_ASSACT.assignment_action_id = assact_id
479 and BAL_ASSACT.payroll_action_id = BACT.payroll_action_id
480 and FEED.balance_type_id = baltype
481 and FEED.input_value_id = TARGET.input_value_id
482 and TARGET.run_result_id = RR.run_result_id
483 and RR.assignment_action_id = ASSACT.assignment_action_id
484 and ASSACT.payroll_action_id = PACT.payroll_action_id
485 and PACT.effective_date between
486 FEED.effective_start_date and FEED.effective_end_date
487 and RR.status in ('P','PA')
488 and ASSACT.assignment_action_id = INTLK.locked_action_id
489 and INTLK.locking_action_id = BAL_ASSACT.assignment_action_id
490 and BACT.action_type in ('P','U')
491 and PACT.action_type <> 'V' /* not reversals */
492 and ASSACT.assignment_id = BAL_ASSACT.assignment_id
493 and not exists
494 (select null
495 from pay_payroll_actions RPACT,
496 pay_assignment_actions RASSACT,
497 pay_action_interlocks RINTLK
498 where ASSACT.assignment_action_id = RINTLK.locked_action_id
499 and RINTLK.locking_action_id = RASSACT.assignment_action_id
500 and RPACT.payroll_action_id = RASSACT.payroll_action_id
501 and RPACT.action_type = 'V');
502 --
503 BEGIN
504 --
505 open cur_PAYMENTS(p_assignment_action_id, p_balance_type_id);
506 FETCH cur_PAYMENTS INTO l_balance;
507 close cur_PAYMENTS;
508 --
509 RETURN l_balance;
510 --
511 END PAYMENTS;
512 --
513 --------------------------------------------------------------------------------
514 --
515 END py_za_routes;