1 PACKAGE BODY pay_ca_group_level_bal_pkg AS
2 /* $Header: pycatxbv.pkb 120.1.12000000.1 2007/01/17 17:37:22 appldev noship $ */
3
4 -------------------------------------------------------------------------------
5 -- Name: get_date_mask
6 --
7 -- Parameters: p_time_dimension
8 --
9 -- Return: VARCHAR2 - the new date mask
10 --
11 -- Description: This function calculates the correct date mask for the given
12 -- time dimension.
13 -------------------------------------------------------------------------------
14 FUNCTION get_date_mask (p_time_dimension VARCHAR2) RETURN VARCHAR2 IS
15
16 l_date_mask VARCHAR2(5) := '';
17 l_routine_name VARCHAR2(64) := 'pay_ca_group_level_bal_pkg.get_date_mask';
18
19 BEGIN
20 hr_utility.trace('Starting routine: '||l_routine_name);
21
22 IF p_time_dimension = 'YTD' THEN
23 l_date_mask := 'Y';
24 hr_utility.trace(l_routine_name||': 20');
25 ELSIF p_time_dimension = 'QTD' THEN
26 l_date_mask := 'Q';
27 hr_utility.trace(l_routine_name||': 30');
28 ELSIF p_time_dimension = 'MONTH' THEN
29 l_date_mask := 'MONTH';
30 hr_utility.trace(l_routine_name||': 40');
31 ELSE
32 pay_us_balance_view_pkg.debug_err('Invalid time dimension');
33 END IF;
34
35 hr_utility.trace('Ending routine: '||l_routine_name);
36 RETURN l_date_mask;
37
38 END get_date_mask;
39
40 -------------------------------------------------------------------------------
41 -- Name: get_asg_virtual_date
42 --
43 -- Parameters: p_assignment_id
44 -- p_date_earned
45 -- p_date_mask
46 --
47 -- Return: DATE - the new validated date
48 --
49 -- Description: This function finds the date of the last assignment with a
50 -- payroll.
51 -------------------------------------------------------------------------------
52 FUNCTION get_asg_virtual_date (p_assignment_id NUMBER,
53 p_date_earned DATE,
54 p_date_mask VARCHAR2) RETURN DATE IS
55
56 CURSOR csr_get_max_asg_end_date(p_asg_id NUMBER,
57 p_date DATE,
58 p_date_mask VARCHAR2) IS
59 SELECT MAX(asg.effective_end_date)
60 FROM per_all_assignments_f asg
61 WHERE asg.assignment_id = p_asg_id
62 AND asg.payroll_id IS NOT NULL
63 AND asg.effective_end_date BETWEEN TRUNC(p_date, p_date_mask)
64 AND p_date;
65
66 l_routine_name VARCHAR2(64):='pay_ca_group_level_bal_pkg.get_asg_virtual_date';
67
68 l_asg_virtual_date DATE;
69 e_no_valid_date_exists EXCEPTION;
70
71 BEGIN
72 hr_utility.trace('Starting routine: '||l_routine_name);
73
74 OPEN csr_get_max_asg_end_date(p_assignment_id,
75 p_date_earned,
76 p_date_mask);
77 FETCH csr_get_max_asg_end_date INTO l_asg_virtual_date;
78 IF csr_get_max_asg_end_date%NOTFOUND THEN
79 hr_utility.trace(l_routine_name||': 20');
80 RAISE e_no_valid_date_exists;
81 END IF;
82
83 hr_utility.trace
84 ('Ending routine: '||l_routine_name);
85 RETURN l_asg_virtual_date;
86 EXCEPTION
87 WHEN e_no_valid_date_exists THEN
88 hr_utility.trace(l_routine_name||': 40');
89 NULL;
90 END get_asg_virtual_date;
91
92 -------------------------------------------------------------------------------
93 -- Name: get_virtual_date
94 --
95 -- Parameters: p_assignment_id
96 -- p_virtual_date
97 -- p_date_mask
98 --
99 -- Return: DATE - the validated date
100 --
101 -- Description: This function ensures that the assignment is on a payroll on
102 -- effective date and if not, a valid date is found.
103 -- If no valid date can be found then an error is raised.
104 -------------------------------------------------------------------------------
105 FUNCTION get_virtual_date (p_assignment_id NUMBER,
106 p_virtual_date DATE,
107 p_date_mask VARCHAR2) RETURN DATE IS
108
109 CURSOR csr_asg_in_payroll(p_asg_id NUMBER,
110 p_date DATE) IS
111 SELECT 'X'
112 FROM per_all_assignments_f asg,
113 pay_all_payrolls_f pay
114 WHERE asg.assignment_id = p_asg_id
115 AND p_date BETWEEN asg.effective_start_date
116 AND asg.effective_end_date
117 AND asg.payroll_id = pay.payroll_id
118 AND p_date BETWEEN pay.effective_start_date
119 AND pay.effective_end_date;
120
121 CURSOR csr_get_virtual_date(p_asg_id NUMBER,
122 p_date DATE,
123 p_date_mask VARCHAR2) IS
124 SELECT MAX(pay.effective_end_date)
125 FROM pay_all_payrolls_f pay,
126 per_all_assignments_f asg
127 WHERE asg.assignment_id = p_asg_id
128 AND asg.payroll_id = pay.payroll_id
129 AND pay.effective_end_date BETWEEN TRUNC(p_date, p_date_mask)
130 AND p_date;
131
132 l_routine_name VARCHAR2(64):='pay_ca_group_level_bal_pkg.get_virtual_date';
133
134 l_asg_in_payroll VARCHAR2(1);
135 l_virtual_date DATE;
136 l_altered_date DATE;
137 l_res_date DATE;
138 e_no_valid_date_exists EXCEPTION;
139
140 BEGIN
141 hr_utility.trace('Starting routine: '||l_routine_name);
142
143 OPEN csr_asg_in_payroll(p_assignment_id,
144 p_virtual_date);
145 FETCH csr_asg_in_payroll INTO l_asg_in_payroll;
146 IF csr_asg_in_payroll%NOTFOUND THEN
147 hr_utility.trace(l_routine_name||': 20');
148 l_virtual_date := get_asg_virtual_date (p_assignment_id,
149 p_virtual_date,
150 p_date_mask);
151 OPEN csr_get_virtual_date(p_assignment_id,
152 p_virtual_date,
153 p_date_mask);
154 FETCH csr_get_virtual_date INTO l_altered_date;
155 IF l_virtual_date IS NULL THEN
156 hr_utility.trace(l_routine_name||': 30');
157 IF l_altered_date IS NULL THEN
158 hr_utility.trace(l_routine_name||': 40');
159 RAISE e_no_valid_date_exists;
160 ELSE
161 hr_utility.trace(l_routine_name||': 50');
162 l_res_date := l_virtual_date;
163 END IF;
164 ELSE
165 IF l_altered_date IS NULL THEN
166 hr_utility.trace(l_routine_name||': 60');
167 l_res_date := l_virtual_date;
168 ELSE
169 hr_utility.trace(l_routine_name||': 70');
170 l_res_date := LEAST (l_virtual_date, l_altered_date);
171 END IF;
172 END IF;
173 ELSE
174 hr_utility.trace(l_routine_name||': 80');
175 l_res_date := p_virtual_date;
176 END IF;
177
178 hr_utility.trace('Ending routine: '||l_routine_name);
179 RETURN l_res_date;
180 EXCEPTION
181 WHEN e_no_valid_date_exists THEN
182 hr_utility.trace(l_routine_name||': 100');
183 END get_virtual_date;
184
185 -------------------------------------------------------------------------------
186 -- Name: get_defined_balance
187 --
188 -- Parameters: p_balance_name
189 -- p_dimension
190 -- p_business_group_id
191 --
192 -- Return: NUMBER - Defined Balance Id
193 --
194 -- Description: This function finds the defined balance id given the balance
195 -- name and dimension.
196 -------------------------------------------------------------------------------
197 FUNCTION get_defined_balance (p_balance_name VARCHAR2,
198 p_dimension VARCHAR2,
199 p_business_group_id NUMBER DEFAULT NULL
200 ) RETURN NUMBER IS
201
202 CURSOR csr_get_def_bal_id(p_bal_name VARCHAR2,
203 p_dimension VARCHAR2,
204 p_bus_grp_id NUMBER) IS
205 SELECT dbl.defined_balance_id
206 FROM pay_defined_balances dbl
207 WHERE dbl.balance_type_id = (SELECT balance_type_id
208 FROM pay_balance_types blt
209 WHERE blt.balance_name = p_bal_name
210 AND (blt.legislation_code = 'CA'
211 OR blt.business_group_id = p_bus_grp_id))
212 AND dbl.balance_dimension_id =(SELECT balance_dimension_id
213 FROM pay_balance_dimensions bld
214 WHERE bld.database_item_suffix =
215 '_'|| p_dimension
216 AND (bld.legislation_code = 'CA'
217 OR bld.business_group_id = p_bus_grp_id))
218 AND (dbl.legislation_code = 'CA'
219 OR dbl.business_group_id = p_bus_grp_id);
220
221 l_routine_name VARCHAR2(64) := 'pay_ca_group_level_bal_pkg.get_defined_balance';
222
223 l_defined_balance_id NUMBER;
224 l_business_group_id NUMBER;
225
226 BEGIN
227 hr_utility.trace('Starting routine: '||l_routine_name);
228
229 hr_utility.trace('Balance type: '||p_balance_name);
230 hr_utility.trace('Balance Dimension: '||p_dimension);
231 OPEN csr_get_def_bal_id(p_balance_name,
232 p_dimension,
233 p_business_group_id);
234 FETCH csr_get_def_bal_id INTO l_defined_balance_id;
235 IF csr_get_def_bal_id%NOTFOUND THEN
236 pay_us_balance_view_pkg.debug_err
237 ('No defined balance exists.');
238 l_defined_balance_id := NULL;
239 END IF;
240 CLOSE csr_get_def_bal_id;
241
242 hr_utility.trace('Ending routine: '||l_routine_name);
243 RETURN l_defined_balance_id;
244
245 END get_defined_balance;
246
247 -------------------------------------------------------------------------------
248 -- Name: get_grp_pydate_with_aa
249 --
250 -- Parameters:
251 -- p_lb_dimension
252 -- p_balance_name
253 -- p_effective_date
254 -- p_start_date
255 -- p_jurisdiction_code
256 -- p_gre_id
257 -- p_source_id
258 -- p_organization_id
259 -- p_location_id
260 -- p_payroll_id
261 -- p_pay_basis_type
262 -- p_business_group_id
263 --
264 -- Return: NUMBER - The value of the group level balance
265 --
266 -- Description: This function calculates the balance value for all PYDATE level
267 -- balance wich have at least one parameter which is not a context.
268 -- This function is split into two parts: when the pay basis type
269 -- NULL and when it has a value.
270 -------------------------------------------------------------------------------
271 FUNCTION get_grp_pydate_with_aa
272 (p_lb_dimension VARCHAR2,
273 p_balance_name VARCHAR2,
274 p_effective_date DATE,
275 p_start_date DATE,
276 p_jurisdiction VARCHAR2,
277 p_gre_id NUMBER,
278 p_source_id NUMBER,
279 p_organization_id NUMBER,
280 p_location_id NUMBER,
281 p_payroll_id NUMBER,
282 p_pay_basis_type VARCHAR2,
283 p_business_group_id NUMBER) RETURN NUMBER IS
284
285 /*
286 * Select all the assignment actions in PAY_ACTION_CONTEXTS for a given GRE
287 * which have been run in a given time period.
288 * These are for the cases where at least one of the additional parameter is
289 * NULL, (ie. l,ocation_id, organization_id, payroll_id, pay_basis_type)
290 */
291 CURSOR csr_get_asg_gre_add(p_org_id NUMBER,
292 p_loc_id NUMBER,
293 p_pay_id NUMBER,
294 p_basis_type VARCHAR2,
295 p_gre_id NUMBER,
296 p_start_date DATE,
297 p_end_date DATE) IS
298 SELECT DISTINCT asa.assignment_action_id
299 FROM pay_payroll_actions pya,
300 pay_assignment_actions asa,
301 per_all_assignments_f asg
302 WHERE asg.organization_id = NVL(p_org_id, asg.organization_id)
303 AND asg.location_id = NVL(p_loc_id, asg.location_id)
304 AND asg.payroll_id = NVL(p_pay_id, asg.payroll_id)
305 AND ((p_basis_type = 'OTHER'
306 AND asg.pay_basis_id IS NULL)
307 OR (p_basis_type = 'HOURLY'
308 AND asg.pay_basis_id IN (SELECT pyb.pay_basis_id
309 FROM per_pay_bases pyb
310 WHERE pyb.pay_basis = 'HOURLY'))
311 OR (p_basis_type = 'SALARIED'
312 AND asg.pay_basis_id IN (SELECT pyb.pay_basis_id
313 FROM per_pay_bases pyb
314 WHERE pyb.pay_basis IN
315 ('ANNUAL','MONTHLY','PERIOD')))
316 OR (p_basis_type IS NULL))
317 AND asa.assignment_id = asg.assignment_id
318 AND asa.tax_unit_id = p_gre_id
319 AND pya.payroll_action_id = asa.payroll_action_id
320 AND pya.effective_date BETWEEN p_start_date
321 AND p_end_date;
322
323 CURSOR csr_get_asg_src_add(p_org_id NUMBER,
324 p_loc_id NUMBER,
325 p_pay_id NUMBER,
326 p_basis_type VARCHAR2,
327 p_src_id NUMBER,
328 p_start_date DATE,
329 p_end_date DATE) IS
330 SELECT DISTINCT asa.assignment_action_id
331 FROM per_all_assignments_f asg,
332 pay_payroll_actions pya,
333 pay_assignment_actions asa,
334 pay_action_contexts acx,
335 ff_contexts cxt
336 WHERE cxt.context_name = 'SOURCE_ID'
337 AND cxt.context_id = acx.context_id
338 AND acx.context_value = TO_CHAR(p_src_id)
339 AND asa.assignment_action_id = acx.assignment_action_id
340 AND pya.payroll_action_id = asa.payroll_action_id
341 AND pya.effective_date BETWEEN p_start_date
342 AND p_end_date
343 AND acx.assignment_id = asg.assignment_id
344 AND asg.organization_id = NVL(p_org_id, asg.organization_id)
345 AND asg.location_id = NVL(p_loc_id, asg.location_id)
346 AND asg.payroll_id = NVL(p_pay_id, asg.payroll_id)
347 AND ((p_basis_type = 'OTHER'
348 AND asg.pay_basis_id IS NULL)
349 OR (p_basis_type = 'HOURLY'
350 AND asg.pay_basis_id IN (SELECT pyb.pay_basis_id
351 FROM per_pay_bases pyb
352 WHERE pyb.pay_basis = 'HOURLY'))
353 OR (p_basis_type = 'SALARIED'
354 AND asg.pay_basis_id IN (SELECT pyb.pay_basis_id
355 FROM per_pay_bases pyb
356 WHERE pyb.pay_basis IN
357 ('ANNUAL','MONTHLY','PERIOD')))
358 OR (p_basis_type IS NULL));
359
360 /*
361 * Select all the assignments in PAY_ACTION_CONTEXTS for a given GRE and
362 * jurisdiction which have been run in a given time period.
363 * These are for the cases where at least one of the additional parameter is
364 * NULL, (ie. l,ocation_id, organization_id, payroll_id, pay_basis_type)
365 */
366 CURSOR csr_get_asg_jd_gre_add(p_org_id NUMBER,
367 p_loc_id NUMBER,
368 p_pay_id NUMBER,
369 p_basis_type VARCHAR2,
370 p_gre_id NUMBER,
371 p_jd VARCHAR2,
372 p_start_date DATE,
373 p_end_date DATE) IS
374 SELECT DISTINCT asa.assignment_action_id
375 FROM per_all_assignments_f asg,
376 pay_payroll_actions pya,
377 pay_assignment_actions asa,
378 pay_action_contexts acx,
379 ff_contexts cxt
380 WHERE cxt.context_name = 'JURISDICTION_CODE'
381 AND cxt.context_id = acx.context_id
382 AND acx.context_value = p_jd
383 AND acx.assignment_action_id = asa.assignment_action_id
384 AND asa.tax_unit_id = p_gre_id
385 AND pya.payroll_action_id = asa.payroll_action_id
386 AND pya.effective_date BETWEEN p_start_date
387 AND p_end_date
388 AND acx.assignment_id = asg.assignment_id
389 AND asg.organization_id = NVL(p_org_id, asg.organization_id)
390 AND asg.location_id = NVL(p_loc_id, asg.location_id)
391 AND asg.payroll_id = NVL(p_pay_id, asg.payroll_id)
392 AND ((p_basis_type = 'OTHER'
393 AND asg.pay_basis_id IS NULL)
394 OR (p_basis_type = 'HOURLY'
395 AND asg.pay_basis_id IN (SELECT pyb.pay_basis_id
396 FROM per_pay_bases pyb
397 WHERE pyb.pay_basis = 'HOURLY'))
398 OR (p_basis_type = 'SALARIED'
399 AND asg.pay_basis_id IN (SELECT pyb.pay_basis_id
400 FROM per_pay_bases pyb
401 WHERE pyb.pay_basis IN
402 ('ANNUAL','MONTHLY','PERIOD')))
403 OR (p_basis_type IS NULL));
404
405 CURSOR csr_get_asg_jd_src_add(p_org_id NUMBER,
406 p_loc_id NUMBER,
407 p_pay_id NUMBER,
408 p_basis_type VARCHAR2,
409 p_src_id NUMBER,
410 p_jd VARCHAR2,
411 p_start_date DATE,
412 p_end_date DATE) IS
413 SELECT DISTINCT asa.assignment_action_id
414 FROM per_all_assignments_f asg,
415 pay_payroll_actions pya,
416 pay_assignment_actions asa,
417 pay_action_contexts acx2,
418 ff_contexts cxt2,
419 pay_action_contexts acx1,
420 ff_contexts cxt1
421 WHERE cxt1.context_name = 'SOURCE_ID'
422 AND cxt1.context_id = acx1.context_id
423 AND acx1.context_value = TO_CHAR(p_src_id)
424 AND cxt2.context_name = 'JURISDICTION_CODE'
425 AND cxt2.context_id = acx2.context_id
426 AND acx2.context_value = p_jd
427 AND asa.assignment_action_id = acx1.assignment_action_id
428 AND asa.assignment_action_id = acx2.assignment_action_id
429 AND asa.tax_unit_id = p_gre_id
430 AND pya.payroll_action_id = asa.payroll_action_id
431 AND pya.effective_date BETWEEN p_start_date
432 AND p_end_date
433 AND acx1.assignment_id = asg.assignment_id
434 AND acx2.assignment_id = asg.assignment_id
435 AND asg.organization_id = NVL(p_org_id, asg.organization_id)
436 AND asg.location_id = NVL(p_loc_id, asg.location_id)
437 AND asg.payroll_id = NVL(p_pay_id, asg.payroll_id)
438 AND ((p_basis_type = 'OTHER'
439 AND asg.pay_basis_id IS NULL)
440 OR (p_basis_type = 'HOURLY'
441 AND asg.pay_basis_id IN (SELECT pyb.pay_basis_id
442 FROM per_pay_bases pyb
443 WHERE pyb.pay_basis = 'HOURLY'))
444 OR (p_basis_type = 'SALARIED'
445 AND asg.pay_basis_id IN (SELECT pyb.pay_basis_id
446 FROM per_pay_bases pyb
447 WHERE pyb.pay_basis IN
448 ('ANNUAL','MONTHLY','PERIOD')))
449 OR (p_basis_type IS NULL));
450
451
452 l_routine_name VARCHAR2(64) :=
453 'pay_ca_group_level_bal_pkg.get_grp_pydate_with_aa';
454
455 l_lb_defined_balance_id NUMBER(9);
456 l_balance_value NUMBER(38,10) := 0;
457 l_run_dimension VARCHAR2(30);
458
459 BEGIN
460 hr_utility.trace('Starting routine: '||l_routine_name);
461
462 l_run_dimension := REPLACE(p_lb_dimension, 'PYDATE', 'RUN');
463
464 l_lb_defined_balance_id := get_defined_balance(p_balance_name,
465 l_run_dimension,
466 p_business_group_id);
467 IF l_lb_defined_balance_id IS NULL THEN
468 RETURN NULL;
469 END IF;
470
471 IF p_source_id IS NOT NULL THEN
472 /*
473 * Loop through all the assignment actions for this Reporting Unit
474 * (Source Id) and sum the latest balance value for each one
475 */
476 hr_utility.trace(l_routine_name||': 30');
477 IF p_jurisdiction IS NULL THEN
478 FOR r_asg IN csr_get_asg_src_add(p_organization_id,
479 p_location_id,
480 p_payroll_id,
481 p_pay_basis_type,
482 p_source_id,
483 p_start_date,
484 p_effective_date) LOOP
485 hr_utility.trace(l_routine_name||': 60');
486 l_balance_value := l_balance_value +
487 pay_ca_balance_view_pkg.get_value
488 (p_assignment_action_id => r_asg.assignment_action_id,
489 p_defined_balance_id => l_lb_defined_balance_id,
490 p_dont_cache => 1,
491 p_always_get_dbi => 0);
492 END LOOP;
493 ELSE
494 FOR r_asg IN csr_get_asg_jd_src_add(p_organization_id,
495 p_location_id,
496 p_payroll_id,
497 p_pay_basis_type,
498 p_source_id,
499 p_jurisdiction,
500 p_start_date,
501 p_effective_date) LOOP
502 hr_utility.trace(l_routine_name||': 70');
503 l_balance_value := l_balance_value +
504 pay_ca_balance_view_pkg.get_value
505 (p_assignment_action_id => r_asg.assignment_action_id,
506 p_defined_balance_id => l_lb_defined_balance_id,
507 p_dont_cache => 1,
508 p_always_get_dbi => 0);
509 END LOOP;
510 END IF;
511 ELSE
512 /*
513 * Loop through all the assignments for this GRE and sum the latest
514 * balance value for each one
515 */
516 hr_utility.trace(l_routine_name||': 80');
517 IF p_jurisdiction IS NULL THEN
518 FOR r_asg IN csr_get_asg_gre_add(p_organization_id,
519 p_location_id,
520 p_payroll_id,
521 p_pay_basis_type,
522 p_gre_id,
523 p_start_date,
524 p_effective_date) LOOP
525 hr_utility.trace(l_routine_name||': 110');
526 l_balance_value := l_balance_value +
527 pay_ca_balance_view_pkg.get_value
528 (p_assignment_action_id => r_asg.assignment_action_id,
529 p_defined_balance_id => l_lb_defined_balance_id,
530 p_dont_cache => 1,
531 p_always_get_dbi => 0);
532 END LOOP;
533 ELSE
534 FOR r_asg IN csr_get_asg_jd_gre_add(p_organization_id,
535 p_location_id,
536 p_payroll_id,
537 p_pay_basis_type,
538 p_gre_id,
539 p_jurisdiction,
540 p_start_date,
541 p_effective_date) LOOP
542 hr_utility.trace(l_routine_name||': 120');
543 l_balance_value := l_balance_value +
544 pay_ca_balance_view_pkg.get_value
545 (p_assignment_action_id => r_asg.assignment_action_id,
546 p_defined_balance_id => l_lb_defined_balance_id,
547 p_dont_cache => 1,
548 p_always_get_dbi => 0);
549 END LOOP;
550 END IF;
551 END IF;
552
553 hr_utility.trace('Ending routine: '||l_routine_name);
554 RETURN l_balance_value;
555 END get_grp_pydate_with_aa;
556
557
558
559 -------------------------------------------------------------------------------
560 -- Name: get_grp_pydate_with_aa_rb
561 --
562 -- Parameters:
563 -- p_lb_dimension
564 -- p_balance_name
565 -- p_effective_date
566 -- p_start_date
567 -- p_jurisdiction_code
568 -- p_gre_id
569 -- p_source_id
570 -- p_organization_id
571 -- p_location_id
572 -- p_payroll_id
573 -- p_pay_basis_type
574 -- p_business_group_id
575 --
576 -- Return: NUMBER - The value of the group level balance
577 --
578 -- Description: This function calculates the balance value for all PYDATE level
579 -- balance wich have at least one parameter which is not a context.
580 -- This function is split into two parts: when the pay basis type
581 -- NULL and when it has a value.
582 -- Uses PAY_RUN_BALANCES tables for all cursors in this function.
583 -------------------------------------------------------------------------------
584 FUNCTION get_grp_pydate_with_aa_rb
585 (p_lb_dimension VARCHAR2,
586 p_balance_name VARCHAR2,
587 p_effective_date DATE,
588 p_start_date DATE,
589 p_jurisdiction VARCHAR2,
590 p_gre_id NUMBER,
591 p_source_id NUMBER,
592 p_organization_id NUMBER,
593 p_location_id NUMBER,
594 p_payroll_id NUMBER,
595 p_pay_basis_type VARCHAR2,
596 p_business_group_id NUMBER) RETURN NUMBER IS
597
598 /*
599 * Select all the assignment actions in PAY_ACTION_CONTEXTS for a given GRE
600 * which have been run in a given time period.
601 * These are for the cases where at least one of the additional parameter is
602 * NULL, (ie. l,ocation_id, organization_id, payroll_id, pay_basis_type)
603 */
604
605 -- NEW EBRA CURSOR csr_get_asg_gre_add definition with pay_run_balances
606 -- validation.
607 CURSOR csr_get_asg_gre_add_rb(p_org_id NUMBER,
608 p_loc_id NUMBER,
609 p_pay_id NUMBER,
610 p_basis_type VARCHAR2,
611 p_gre_id NUMBER,
612 p_start_date DATE,
613 p_end_date DATE,
614 p_def_bal_id NUMBER) IS
615 SELECT DISTINCT prb.assignment_action_id
616 FROM pay_run_balances prb,
617 per_all_assignments_f asg
618 WHERE asg.organization_id = NVL(p_org_id, asg.organization_id)
619 AND asg.location_id = NVL(p_loc_id, asg.location_id)
620 AND asg.payroll_id = NVL(p_pay_id, asg.payroll_id)
621 AND ((p_basis_type = 'OTHER'
622 AND asg.pay_basis_id IS NULL)
623 OR (p_basis_type = 'HOURLY'
624 AND asg.pay_basis_id IN (SELECT pyb.pay_basis_id
625 FROM per_pay_bases pyb
626 WHERE pyb.pay_basis = 'HOURLY'))
627 OR (p_basis_type = 'SALARIED'
628 AND asg.pay_basis_id IN (SELECT pyb.pay_basis_id
629 FROM per_pay_bases pyb
630 WHERE pyb.pay_basis IN
631 ('ANNUAL','MONTHLY','PERIOD')))
632 OR (p_basis_type IS NULL))
633 AND prb.assignment_id = asg.assignment_id
634 AND prb.tax_unit_id = p_gre_id
635 AND prb.defined_balance_id = p_def_bal_id
636 AND prb.effective_date BETWEEN p_start_date
637 AND p_end_date;
638
639
640 -- NEW EBRA CURSOR csr_get_asg_src_add definition with
641 -- pay_run_balances validation.
642 CURSOR csr_get_asg_src_add_rb(p_org_id NUMBER,
643 p_loc_id NUMBER,
644 p_pay_id NUMBER,
645 p_basis_type VARCHAR2,
646 p_src_id NUMBER,
647 p_start_date DATE,
648 p_end_date DATE,
649 p_def_bal_id NUMBER) IS
650 SELECT DISTINCT prb.assignment_action_id
651 FROM per_all_assignments_f asg,
652 pay_run_balances prb
653 WHERE prb.source_id = p_src_id
654 AND prb.effective_date BETWEEN p_start_date
655 AND p_end_date
656 AND prb.assignment_id = asg.assignment_id
657 AND prb.defined_balance_id = p_def_bal_id
658 AND asg.organization_id = NVL(p_org_id, asg.organization_id)
659 AND asg.location_id = NVL(p_loc_id, asg.location_id)
660 AND asg.payroll_id = NVL(p_pay_id, asg.payroll_id)
661 AND ((p_basis_type = 'OTHER'
662 AND asg.pay_basis_id IS NULL)
663 OR (p_basis_type = 'HOURLY'
664 AND asg.pay_basis_id IN (SELECT pyb.pay_basis_id
665 FROM per_pay_bases pyb
666 WHERE pyb.pay_basis = 'HOURLY'))
667 OR (p_basis_type = 'SALARIED'
668 AND asg.pay_basis_id IN (SELECT pyb.pay_basis_id
669 FROM per_pay_bases pyb
670 WHERE pyb.pay_basis IN
671 ('ANNUAL','MONTHLY','PERIOD')))
672 OR (p_basis_type IS NULL));
673
674
675 /*
676 * Select all the assignments in PAY_ACTION_CONTEXTS for a given GRE and
677 * jurisdiction which have been run in a given time period.
678 * These are for the cases where at least one of the additional parameter is
679 * NULL, (ie. l,ocation_id, organization_id, payroll_id, pay_basis_type)
680 */
681
682 -- NEW EBRA CURSOR csr_get_asg_jd_gre_add definition with
683 -- pay_run_balances validation
684 CURSOR csr_get_asg_jd_gre_add_rb(p_org_id NUMBER,
685 p_loc_id NUMBER,
686 p_pay_id NUMBER,
687 p_basis_type VARCHAR2,
688 p_gre_id NUMBER,
689 p_jd VARCHAR2,
690 p_start_date DATE,
691 p_end_date DATE,
692 p_def_bal_id NUMBER) IS
693 SELECT DISTINCT prb.assignment_action_id
694 FROM per_all_assignments_f asg,
695 pay_run_balances prb
696 WHERE prb.jurisdiction_code = p_jd
697 AND prb.tax_unit_id = p_gre_id
698 AND prb.defined_balance_id = p_def_bal_id
699 AND prb.effective_date BETWEEN p_start_date
700 AND p_end_date
701 AND prb.assignment_id = asg.assignment_id
702 AND asg.organization_id = NVL(p_org_id, asg.organization_id)
703 AND asg.location_id = NVL(p_loc_id, asg.location_id)
704 AND asg.payroll_id = NVL(p_pay_id, asg.payroll_id)
705 AND ((p_basis_type = 'OTHER'
706 AND asg.pay_basis_id IS NULL)
707 OR (p_basis_type = 'HOURLY'
708 AND asg.pay_basis_id IN (SELECT pyb.pay_basis_id
709 FROM per_pay_bases pyb
710 WHERE pyb.pay_basis = 'HOURLY'))
711 OR (p_basis_type = 'SALARIED'
712 AND asg.pay_basis_id IN (SELECT pyb.pay_basis_id
713 FROM per_pay_bases pyb
714 WHERE pyb.pay_basis IN
715 ('ANNUAL','MONTHLY','PERIOD')))
716 OR (p_basis_type IS NULL));
717
718
719
720 CURSOR csr_get_asg_jd_src_add(p_org_id NUMBER,
721 p_loc_id NUMBER,
722 p_pay_id NUMBER,
723 p_basis_type VARCHAR2,
724 p_src_id NUMBER,
725 p_jd VARCHAR2,
726 p_start_date DATE,
727 p_end_date DATE) IS
728 SELECT DISTINCT asa.assignment_action_id
729 FROM per_all_assignments_f asg,
730 pay_payroll_actions pya,
731 pay_assignment_actions asa,
732 pay_action_contexts acx2,
733 ff_contexts cxt2,
734 pay_action_contexts acx1,
735 ff_contexts cxt1
736 WHERE cxt1.context_name = 'SOURCE_ID'
737 AND cxt1.context_id = acx1.context_id
738 AND acx1.context_value = TO_CHAR(p_src_id)
739 AND cxt2.context_name = 'JURISDICTION_CODE'
740 AND cxt2.context_id = acx2.context_id
741 AND acx2.context_value = p_jd
742 AND asa.assignment_action_id = acx1.assignment_action_id
743 AND asa.assignment_action_id = acx2.assignment_action_id
744 AND asa.tax_unit_id = p_gre_id
745 AND pya.payroll_action_id = asa.payroll_action_id
746 AND pya.effective_date BETWEEN p_start_date
747 AND p_end_date
748 AND acx1.assignment_id = asg.assignment_id
749 AND acx2.assignment_id = asg.assignment_id
750 AND asg.organization_id = NVL(p_org_id, asg.organization_id)
751 AND asg.location_id = NVL(p_loc_id, asg.location_id)
752 AND asg.payroll_id = NVL(p_pay_id, asg.payroll_id)
753 AND ((p_basis_type = 'OTHER'
754 AND asg.pay_basis_id IS NULL)
755 OR (p_basis_type = 'HOURLY'
756 AND asg.pay_basis_id IN (SELECT pyb.pay_basis_id
757 FROM per_pay_bases pyb
758 WHERE pyb.pay_basis = 'HOURLY'))
759 OR (p_basis_type = 'SALARIED'
760 AND asg.pay_basis_id IN (SELECT pyb.pay_basis_id
761 FROM per_pay_bases pyb
762 WHERE pyb.pay_basis IN
763 ('ANNUAL','MONTHLY','PERIOD')))
764 OR (p_basis_type IS NULL));
765
766
767 l_routine_name VARCHAR2(64) :=
768 'pay_ca_group_level_bal_pkg.get_grp_pydate_with_aa_rb';
769
770 l_lb_defined_balance_id NUMBER(9);
771 l_balance_value NUMBER(38,10) := 0;
772 l_run_dimension VARCHAR2(30);
773 l_ge_def_bal_id NUMBER(20);
774
775 BEGIN
776 hr_utility.trace('Starting routine: '||l_routine_name);
777
778 l_run_dimension := REPLACE(p_lb_dimension, 'PYDATE', 'RUN');
779
780 l_lb_defined_balance_id := get_defined_balance(p_balance_name,
781 l_run_dimension,
782 p_business_group_id);
783
784 /* To check in run_balances table with def_bal_id and get valid
785 Assignment or Assignment Action for Balance calls */
786
787 If p_jurisdiction is NULL then
788 l_ge_def_bal_id := get_defined_balance(p_balance_name,
789 'ASG_GRE_RUN',
790 p_business_group_id);
791 hr_utility.trace('Def_bal_id of '||p_balance_name||'_ASG_GRE_RUN : '||to_char(l_ge_def_bal_id));
792
793 Else
794
795 l_ge_def_bal_id := get_defined_balance(p_balance_name,
796 'ASG_JD_GRE_RUN',
797 p_business_group_id);
798 hr_utility.trace('Def_bal_id of '||p_balance_name||'_ASG_JD_GRE_RUN : '||to_char(l_ge_def_bal_id));
799 End if;
800
801 hr_utility.trace('Def_bal_id of GROSS_EARNINGS_ASG_JD_GRE_RUN : '||to_char(l_ge_def_bal_id));
802
803 IF l_lb_defined_balance_id IS NULL THEN
804 RETURN NULL;
805 END IF;
806
807 IF p_source_id IS NOT NULL THEN
808 /*
809 * Loop through all the assignment actions for this Reporting Unit
810 * (Source Id) and sum the latest balance value for each one
811 */
812 hr_utility.trace(l_routine_name||': 30');
813 IF p_jurisdiction IS NULL THEN
814
815 FOR r_asg IN csr_get_asg_src_add_rb(p_organization_id,
816 p_location_id,
817 p_payroll_id,
818 p_pay_basis_type,
819 p_source_id,
820 p_start_date,
821 p_effective_date,
822 l_ge_def_bal_id) LOOP
823 hr_utility.trace(l_routine_name||': 60');
824 l_balance_value := l_balance_value +
825 pay_ca_balance_view_pkg.get_value
826 (p_assignment_action_id => r_asg.assignment_action_id,
827 p_defined_balance_id => l_lb_defined_balance_id,
828 p_dont_cache => 1,
829 p_always_get_dbi => 0);
830 END LOOP;
831 ELSE
832 FOR r_asg IN csr_get_asg_jd_src_add(p_organization_id,
833 p_location_id,
834 p_payroll_id,
835 p_pay_basis_type,
836 p_source_id,
837 p_jurisdiction,
838 p_start_date,
839 p_effective_date) LOOP
840 hr_utility.trace(l_routine_name||': 70');
841 l_balance_value := l_balance_value +
842 pay_ca_balance_view_pkg.get_value
843 (p_assignment_action_id => r_asg.assignment_action_id,
844 p_defined_balance_id => l_lb_defined_balance_id,
845 p_dont_cache => 1,
846 p_always_get_dbi => 0);
847 END LOOP;
848 END IF;
849 ELSE
850 /*
851 * Loop through all the assignments for this GRE and sum the latest
852 * balance value for each one
853 */
854 hr_utility.trace(l_routine_name||': 80');
855 IF p_jurisdiction IS NULL THEN
856 FOR r_asg IN csr_get_asg_gre_add_rb(p_organization_id,
857 p_location_id,
858 p_payroll_id,
859 p_pay_basis_type,
860 p_gre_id,
861 p_start_date,
862 p_effective_date,
863 l_ge_def_bal_id) LOOP
864 hr_utility.trace(l_routine_name||': 110');
865 l_balance_value := l_balance_value +
866 pay_ca_balance_view_pkg.get_value
867 (p_assignment_action_id => r_asg.assignment_action_id,
868 p_defined_balance_id => l_lb_defined_balance_id,
869 p_dont_cache => 1,
870 p_always_get_dbi => 0);
871 END LOOP;
872 ELSE
873 FOR r_asg IN csr_get_asg_jd_gre_add_rb(p_organization_id,
874 p_location_id,
875 p_payroll_id,
876 p_pay_basis_type,
877 p_gre_id,
878 p_jurisdiction,
879 p_start_date,
880 p_effective_date,
881 l_ge_def_bal_id) LOOP
882 hr_utility.trace(l_routine_name||': 120');
883 l_balance_value := l_balance_value +
884 pay_ca_balance_view_pkg.get_value
885 (p_assignment_action_id => r_asg.assignment_action_id,
886 p_defined_balance_id => l_lb_defined_balance_id,
887 p_dont_cache => 1,
888 p_always_get_dbi => 0);
889 END LOOP;
890 END IF;
891 END IF;
892
893 hr_utility.trace('Ending routine: '||l_routine_name);
894 RETURN l_balance_value;
895 END get_grp_pydate_with_aa_rb;
896
897
898 -------------------------------------------------------------------------------
899 -- Name: get_grp_non_pydate_with_asg
900 --
901 -- Parameters: p_assignment_id
902 -- p_time_dimension
903 -- p_lb_dimension
904 -- p_gl_defined_balance_id
905 -- p_balance_name
906 -- p_effective_date
907 -- p_start_date
908 -- p_jurisdiction_code
909 -- p_gre_id
910 -- p_source_id
911 -- p_organization_id
912 -- p_location_id
913 -- p_payroll_id
914 -- p_pay_basis_type
915 -- p_business_group_id
916 --
917 -- Return: NUMBER - The value of the group level balance
918 --
919 -- Description: This function calculates the balance value given an assignment
920 -- id and date. If the latest balances exist they will be utilised.
921 -------------------------------------------------------------------------------
922 FUNCTION get_grp_non_pydate_with_asg
923 (p_assignment_id NUMBER,
924 p_time_dimension VARCHAR2,
925 p_lb_dimension VARCHAR2,
926 p_gl_defined_balance_id NUMBER,
927 p_balance_name VARCHAR2,
928 p_effective_date DATE,
929 p_start_date DATE,
930 p_jurisdiction VARCHAR2,
931 p_gre_id NUMBER,
932 p_source_id NUMBER,
933 p_organization_id NUMBER,
934 p_location_id NUMBER,
935 p_payroll_id NUMBER,
936 p_pay_basis_type VARCHAR2,
937 p_business_group_id NUMBER) RETURN NUMBER IS
938
939 CURSOR csr_latest_bal_exists(p_asg_id NUMBER,
940 p_def_bal_id NUMBER,
941 p_start_date DATE,
942 p_end_date DATE) IS
943 SELECT 'X'
944 FROM SYS.DUAL
945 WHERE EXISTS (SELECT 'X'
946 FROM pay_payroll_actions pya,
947 pay_assignment_actions asa,
948 pay_assignment_latest_balances alb
949 WHERE alb.assignment_id = p_asg_id
950 AND alb.defined_balance_id = p_def_bal_id
951 AND alb.assignment_action_id = asa.assignment_action_id
952 AND asa.payroll_action_id = pya.payroll_action_id
953 AND pya.effective_date BETWEEN p_start_date
954 AND p_end_date);
955
956 /*
957 * Select all the assignments in PAY_ACTION_CONTEXTS for a given GRE
958 * which have been run in a given time period.
959 * These are for the cases where all the additional parameter ie. location_id,
960 * organization_id, payroll_id, pay_basis_type are NULL
961 */
962 CURSOR csr_get_asg_gre(p_gre_id NUMBER,
963 p_start_date DATE,
964 p_end_date DATE) IS
965 SELECT DISTINCT(asg.assignment_id) assignment_id
966 FROM per_all_assignments asg
967 WHERE EXISTS (SELECT 'X'
968 FROM pay_payroll_actions pya,
969 pay_assignment_actions asa
970 WHERE asa.assignment_id = asg.assignment_id
971 AND asa.tax_unit_id = p_gre_id
972 AND pya.payroll_action_id = asa.payroll_action_id
973 AND pya.effective_date BETWEEN p_start_date
974 AND p_end_date);
975
976 CURSOR csr_get_asg_src(p_src_id NUMBER,
977 p_start_date DATE,
978 p_end_date DATE) IS
979 SELECT DISTINCT(acx.assignment_id) assignment_id
980 FROM pay_action_contexts acx,
981 ff_contexts cxt
982 WHERE cxt.context_name = 'SOURCE_ID'
983 AND cxt.context_id = acx.context_id
984 AND acx.context_value = TO_CHAR(p_src_id)
985 AND EXISTS (SELECT 'X'
986 FROM pay_payroll_actions pya,
987 pay_assignment_actions asa
988 WHERE asa.assignment_action_id = acx.assignment_action_id
989 AND pya.payroll_action_id = asa.payroll_action_id
990 AND pya.effective_date BETWEEN p_start_date
991 AND p_end_date);
992
993 /*
994 * Select all the assignments in PAY_ACTION_CONTEXTS for a given GRE and
995 * jurisdiction which have been run in a given time period.
996 * These are for the cases where all the additional parameter ie. location_id,
997 * organization_id, payroll_id, pay_basis_type are NULL
998 */
999 CURSOR csr_get_asg_jd_gre(p_gre_id NUMBER,
1000 p_jd VARCHAR2,
1001 p_start_date DATE,
1002 p_end_date DATE) IS
1003 SELECT DISTINCT(acx.assignment_id) assignment_id
1004 FROM pay_action_contexts acx,
1005 ff_contexts cxt
1006 WHERE cxt.context_name = 'JURISDICTION_CODE'
1007 AND cxt.context_id = acx.context_id
1008 AND acx.context_value = p_jd
1009 AND EXISTS (SELECT 'X'
1010 FROM pay_payroll_actions pya,
1011 pay_assignment_actions asa
1012 WHERE asa.assignment_action_id = acx.assignment_action_id
1013 AND asa.tax_unit_id = p_gre_id
1014 AND pya.payroll_action_id = asa.payroll_action_id
1015 AND pya.effective_date BETWEEN p_start_date
1016 AND p_end_date);
1017
1018 CURSOR csr_get_asg_jd_src(p_src_id NUMBER,
1019 p_jd VARCHAR2,
1020 p_start_date DATE,
1021 p_end_date DATE) IS
1022 SELECT DISTINCT(acx1.assignment_id) assignment_id
1023 FROM pay_action_contexts acx1,
1024 pay_action_contexts acx2,
1025 ff_contexts cxt1,
1026 ff_contexts cxt2
1027 WHERE cxt1.context_name = 'SOURCE_ID'
1028 AND cxt1.context_id = acx1.context_id
1029 AND acx1.context_value = TO_CHAR(p_src_id)
1030 AND cxt2.context_name = 'JURISDICTION_CODE'
1031 AND cxt2.context_id = acx2.context_id
1032 AND acx2.context_value = p_jd
1033 AND acx1.assignment_action_id = acx2.assignment_action_id
1034 AND EXISTS (SELECT 'X'
1035 FROM pay_payroll_actions pya,
1036 pay_assignment_actions asa
1037 WHERE asa.assignment_action_id = acx1.assignment_action_id
1038 AND pya.payroll_action_id = asa.payroll_action_id
1039 AND pya.effective_date BETWEEN p_start_date
1040 AND p_end_date);
1041
1042 /*
1043 * Select all the assignments in PAY_ACTION_CONTEXTS for a given GRE
1044 * which have been run in a given time period.
1045 * These are for the cases where at least one of the additional parameter is
1046 * NULL, (ie. l,ocation_id, organization_id, payroll_id, pay_basis_type)
1047 */
1048 CURSOR csr_get_asg_gre_add(p_org_id NUMBER,
1049 p_loc_id NUMBER,
1050 p_pay_id NUMBER,
1051 p_basis_type VARCHAR2,
1052 p_gre_id NUMBER,
1053 p_start_date DATE,
1054 p_end_date DATE) IS
1055 SELECT DISTINCT(asg.assignment_id) assignment_id
1056 FROM per_all_assignments_f asg
1057 WHERE asg.organization_id = NVL(p_org_id, asg.organization_id)
1058 AND asg.location_id = NVL(p_loc_id, asg.location_id)
1059 AND asg.payroll_id = NVL(p_pay_id, asg.payroll_id)
1060 AND ((p_basis_type = 'OTHER'
1061 AND asg.pay_basis_id IS NULL)
1062 OR (p_basis_type = 'HOURLY'
1063 AND asg.pay_basis_id IN (SELECT pyb.pay_basis_id
1064 FROM per_pay_bases pyb
1065 WHERE pyb.pay_basis = 'HOURLY'))
1066 OR (p_basis_type = 'SALARIED'
1067 AND asg.pay_basis_id IN (SELECT pyb.pay_basis_id
1068 FROM per_pay_bases pyb
1069 WHERE pyb.pay_basis IN
1070 ('ANNUAL','MONTHLY','PERIOD')))
1071 OR (p_basis_type IS NULL))
1072 AND EXISTS (SELECT 'X'
1073 FROM pay_payroll_actions pya,
1074 pay_assignment_actions asa
1075 WHERE asa.assignment_id = asg.assignment_id
1076 AND asa.tax_unit_id = p_gre_id
1077 AND pya.payroll_action_id = asa.payroll_action_id
1078 AND pya.effective_date BETWEEN p_start_date
1079 AND p_end_date);
1080
1081 CURSOR csr_get_asg_src_add(p_org_id NUMBER,
1082 p_loc_id NUMBER,
1083 p_pay_id NUMBER,
1084 p_basis_type VARCHAR2,
1085 p_src_id NUMBER,
1086 p_start_date DATE,
1087 p_end_date DATE) IS
1088 SELECT DISTINCT(acx.assignment_id) assignment_id
1089 FROM per_all_assignments_f asg,
1090 pay_action_contexts acx,
1091 ff_contexts cxt
1092 WHERE cxt.context_name = 'SOURCE_ID'
1093 AND cxt.context_id = acx.context_id
1094 AND acx.context_value = TO_CHAR(p_src_id)
1095 AND EXISTS (SELECT 'X'
1096 FROM pay_payroll_actions pya,
1097 pay_assignment_actions asa
1098 WHERE asa.assignment_action_id = acx.assignment_action_id
1099 AND pya.payroll_action_id = asa.payroll_action_id
1100 AND pya.effective_date BETWEEN p_start_date
1101 AND p_end_date)
1102 AND acx.assignment_id = asg.assignment_id
1103 AND asg.organization_id = NVL(p_org_id, asg.organization_id)
1104 AND asg.location_id = NVL(p_loc_id, asg.location_id)
1105 AND asg.payroll_id = NVL(p_pay_id, asg.payroll_id)
1106 AND ((p_basis_type = 'OTHER'
1107 AND asg.pay_basis_id IS NULL)
1108 OR (p_basis_type = 'HOURLY'
1109 AND asg.pay_basis_id IN (SELECT pyb.pay_basis_id
1110 FROM per_pay_bases pyb
1111 WHERE pyb.pay_basis = 'HOURLY'))
1112 OR (p_basis_type = 'SALARIED'
1113 AND asg.pay_basis_id IN (SELECT pyb.pay_basis_id
1114 FROM per_pay_bases pyb
1115 WHERE pyb.pay_basis IN
1116 ('ANNUAL','MONTHLY','PERIOD')))
1117 OR (p_basis_type IS NULL));
1118
1119 /*
1120 * Select all the assignments in PAY_ACTION_CONTEXTS for a given GRE and
1121 * jurisdiction which have been run in a given time period.
1122 * These are for the cases where at least one of the additional parameter is
1123 * NULL, (ie. l,ocation_id, organization_id, payroll_id, pay_basis_type)
1124 */
1125 CURSOR csr_get_asg_jd_gre_add(p_org_id NUMBER,
1126 p_loc_id NUMBER,
1127 p_pay_id NUMBER,
1128 p_basis_type VARCHAR2,
1129 p_gre_id NUMBER,
1130 p_jd VARCHAR2,
1131 p_start_date DATE,
1132 p_end_date DATE) IS
1133 SELECT DISTINCT(asg.assignment_id) assignment_id
1134 FROM per_all_assignments_f asg,
1135 pay_action_contexts acx,
1136 ff_contexts cxt
1137 WHERE cxt.context_name = 'JURISDICTION_CODE'
1138 AND cxt.context_id = acx.context_id
1139 AND acx.context_value = p_jd
1140 AND EXISTS (SELECT 'X'
1141 FROM pay_payroll_actions pya,
1142 pay_assignment_actions asa
1143 WHERE acx.assignment_action_id = asa.assignment_action_id
1144 AND asa.tax_unit_id = p_gre_id
1145 AND pya.payroll_action_id = asa.payroll_action_id
1146 AND pya.effective_date BETWEEN p_start_date
1147 AND p_end_date)
1148 AND acx.assignment_id = asg.assignment_id
1149 AND asg.organization_id = NVL(p_org_id, asg.organization_id)
1150 AND asg.location_id = NVL(p_loc_id, asg.location_id)
1151 AND asg.payroll_id = NVL(p_pay_id, asg.payroll_id)
1152 AND ((p_basis_type = 'OTHER'
1153 AND asg.pay_basis_id IS NULL)
1154 OR (p_basis_type = 'HOURLY'
1155 AND asg.pay_basis_id+0 IN (SELECT pyb.pay_basis_id
1156 FROM per_pay_bases pyb
1157 WHERE pyb.pay_basis = 'HOURLY'))
1158 OR (p_basis_type = 'SALARIED'
1159 AND asg.pay_basis_id IN (SELECT pyb.pay_basis_id
1160 FROM per_pay_bases pyb
1161 WHERE pyb.pay_basis IN
1162 ('ANNUAL','MONTHLY','PERIOD')))
1163 OR (p_basis_type IS NULL));
1164
1165 CURSOR csr_get_asg_jd_src_add(p_org_id NUMBER,
1166 p_loc_id NUMBER,
1167 p_pay_id NUMBER,
1168 p_basis_type VARCHAR2,
1169 p_src_id NUMBER,
1170 p_jd VARCHAR2,
1171 p_start_date DATE,
1172 p_end_date DATE) IS
1173 SELECT DISTINCT(acx1.assignment_id) assignment_id
1174 FROM per_all_assignments_f asg,
1175 pay_action_contexts acx2,
1176 pay_action_contexts acx1,
1177 ff_contexts cxt2,
1178 ff_contexts cxt1
1179 WHERE cxt1.context_name = 'SOURCE_ID'
1180 AND cxt1.context_id = acx1.context_id
1181 AND acx1.context_value = TO_CHAR(p_src_id)
1182 AND acx1.assignment_id = asg.assignment_id
1183 AND cxt2.context_name = 'JURISDICTION_CODE'
1184 AND cxt2.context_id = acx2.context_id
1185 AND acx2.context_value = p_jd
1186 AND acx2.assignment_id = asg.assignment_id
1187 AND acx1.assignment_action_id = acx2.assignment_action_id
1188 AND EXISTS (SELECT 'X'
1189 FROM pay_payroll_actions pya,
1190 pay_assignment_actions asa
1191 WHERE asa.assignment_action_id = acx1.assignment_action_id
1192 AND asa.tax_unit_id = p_gre_id
1193 AND pya.payroll_action_id = asa.payroll_action_id
1194 AND pya.effective_date BETWEEN p_start_date
1195 AND p_end_date)
1196 AND asg.organization_id = NVL(p_org_id, asg.organization_id)
1197 AND asg.location_id = NVL(p_loc_id, asg.location_id)
1198 AND asg.payroll_id = NVL(p_pay_id, asg.payroll_id)
1199 AND ((p_basis_type = 'OTHER'
1200 AND asg.pay_basis_id IS NULL)
1201 OR (p_basis_type = 'HOURLY'
1202 AND asg.pay_basis_id IN (SELECT pyb.pay_basis_id
1203 FROM per_pay_bases pyb
1204 WHERE pyb.pay_basis = 'HOURLY'))
1205 OR (p_basis_type = 'SALARIED'
1206 AND asg.pay_basis_id IN (SELECT pyb.pay_basis_id
1207 FROM per_pay_bases pyb
1208 WHERE pyb.pay_basis IN
1209 ('ANNUAL','MONTHLY','PERIOD')))
1210 OR (p_basis_type IS NULL));
1211
1212
1213 l_routine_name VARCHAR2(64) := 'pay_ca_group_level_bal_pkg.get_grp_non_pydate_with_asg';
1214
1215 l_lb_defined_balance_id NUMBER(9);
1216 l_latest_bal_exists VARCHAR2(1);
1217 l_virtual_date DATE;
1218 l_balance_value NUMBER(38,10) := 0;
1219 l_date_mask VARCHAR2(5);
1220 l_additional_params VARCHAR2(1);
1221
1222 BEGIN
1223 hr_utility.trace('Starting routine: '||l_routine_name);
1224
1225 l_lb_defined_balance_id := get_defined_balance(p_balance_name,
1226 p_lb_dimension,
1227 p_business_group_id);
1228 IF l_lb_defined_balance_id IS NULL THEN
1229 RETURN NULL;
1230 END IF;
1231
1232 /*
1233 * Have any additional parmaeters been specified (location, organization,
1234 * payroll, paybasis type) ?
1235 */
1236 IF p_organization_id IS NULL AND
1237 p_location_id IS NULL AND
1238 p_payroll_id IS NULL AND
1239 p_pay_basis_type IS NULL THEN
1240 hr_utility.trace(l_routine_name||': 5');
1241 l_additional_params := 'N';
1242 ELSE
1243 hr_utility.trace(l_routine_name||': 10');
1244 l_additional_params := 'Y';
1245 END IF;
1246
1247 /*
1248 * First check whether latest balances exist for the given assignment
1249 * on the given date
1250 */
1251 OPEN csr_latest_bal_exists(p_assignment_id,
1252 l_lb_defined_balance_id,
1253 p_start_date,
1254 p_effective_date);
1255 FETCH csr_latest_bal_exists INTO l_latest_bal_exists;
1256 IF csr_latest_bal_exists%NOTFOUND AND
1257 l_additional_params = 'N' THEN
1258 hr_utility.trace(l_routine_name||': 20');
1259 /*
1260 * No latest balances found so calculate the group level balance from
1261 * first principles
1262 */
1263 l_balance_value := pay_ca_balance_view_pkg.get_value
1264 (p_assignment_id,
1265 p_gl_defined_balance_id,
1266 p_effective_date);
1267
1268 ELSIF p_source_id IS NOT NULL THEN
1269 /*
1270 * Loop through all the assignments for this Reporting Unit (Source Id)
1271 * and sum the latest balance value for each one
1272 */
1273 hr_utility.trace(l_routine_name||': 30');
1274 l_date_mask := get_date_mask(p_time_dimension);
1275 IF l_additional_params = 'N' THEN
1276 IF p_jurisdiction IS NULL THEN
1277 FOR r_asg IN csr_get_asg_src(p_source_id,
1278 p_start_date,
1279 p_effective_date) LOOP
1280 hr_utility.trace(l_routine_name||': 40');
1281
1282 IF p_time_dimension <> 'PYDATE' THEN
1283 l_virtual_date := get_virtual_date (r_asg.assignment_id,
1284 p_effective_date,
1285 l_date_mask);
1286 END IF;
1287 l_balance_value := l_balance_value +
1288 pay_ca_balance_view_pkg.get_value(r_asg.assignment_id,
1289 l_lb_defined_balance_id,
1290 l_virtual_date,
1291 1); /*turn caching off*/
1292 END LOOP;
1293 ELSE
1294 FOR r_asg IN csr_get_asg_jd_src(p_source_id,
1295 p_jurisdiction,
1296 p_start_date,
1297 p_effective_date) LOOP
1298 hr_utility.trace(l_routine_name||': 50');
1299 l_virtual_date := get_virtual_date (r_asg.assignment_id,
1300 p_effective_date,
1301 l_date_mask);
1302 l_balance_value := l_balance_value +
1303 pay_ca_balance_view_pkg.get_value(r_asg.assignment_id,
1304 l_lb_defined_balance_id,
1305 l_virtual_date,
1306 1); /*turn caching off*/
1307 END LOOP;
1308 END IF;
1309 ELSE
1310 IF p_jurisdiction IS NULL THEN
1311 FOR r_asg IN csr_get_asg_src_add(p_organization_id,
1312 p_location_id,
1313 p_payroll_id,
1314 p_pay_basis_type,
1315 p_source_id,
1316 p_start_date,
1317 p_effective_date) LOOP
1318 hr_utility.trace(l_routine_name||': 60');
1319 l_virtual_date := get_virtual_date (r_asg.assignment_id,
1320 p_effective_date,
1321 l_date_mask);
1322 l_balance_value := l_balance_value +
1323 pay_ca_balance_view_pkg.get_value (r_asg.assignment_id,
1324 l_lb_defined_balance_id,
1325 l_virtual_date,
1326 1); /*turn caching off*/
1327 END LOOP;
1328 ELSE
1329 FOR r_asg IN csr_get_asg_jd_src_add(p_organization_id,
1330 p_location_id,
1331 p_payroll_id,
1332 p_pay_basis_type,
1333 p_source_id,
1334 p_jurisdiction,
1335 p_start_date,
1336 p_effective_date) LOOP
1337 hr_utility.trace(l_routine_name||': 70');
1338 l_virtual_date := get_virtual_date (r_asg.assignment_id,
1339 p_effective_date,
1340 l_date_mask);
1341 l_balance_value := l_balance_value +
1342 pay_ca_balance_view_pkg.get_value (r_asg.assignment_id,
1343 l_lb_defined_balance_id,
1344 l_virtual_date,
1345 1); /*turn caching off*/
1346 END LOOP;
1347 END IF;
1348 END IF;
1349 ELSE
1350 /*
1351 * Loop through all the assignments for this GRE and sum the latest
1352 * balance value for each one
1353 */
1354 hr_utility.trace(l_routine_name||': 80');
1355 IF p_time_dimension <> 'PYDATE' THEN
1356 l_date_mask := get_date_mask(p_time_dimension);
1357 END IF;
1358 IF l_additional_params = 'N' THEN
1359 IF p_jurisdiction IS NULL THEN
1360 FOR r_asg IN csr_get_asg_gre(p_gre_id,
1361 p_start_date,
1362 p_effective_date) LOOP
1363 hr_utility.trace(l_routine_name||': 90');
1364 l_virtual_date := get_virtual_date (r_asg.assignment_id,
1365 p_effective_date,
1366 l_date_mask);
1367 l_balance_value := l_balance_value +
1368 pay_ca_balance_view_pkg.get_value
1369 (p_assignment_id => r_asg.assignment_id,
1370 p_defined_balance_id => l_lb_defined_balance_id,
1371 p_effective_date => l_virtual_date,
1372 p_dont_cache => 1); /* turn caching off */
1373 END LOOP;
1374 ELSE
1375 FOR r_asg IN csr_get_asg_jd_gre(p_gre_id,
1376 p_jurisdiction,
1377 p_start_date,
1378 p_effective_date) LOOP
1379 hr_utility.trace(l_routine_name||': 100');
1380 l_virtual_date := get_virtual_date (r_asg.assignment_id,
1381 p_effective_date,
1382 l_date_mask);
1383 l_balance_value := l_balance_value +
1384 pay_ca_balance_view_pkg.get_value
1385 (p_assignment_id => r_asg.assignment_id,
1386 p_defined_balance_id => l_lb_defined_balance_id,
1387 p_effective_date => l_virtual_date,
1388 p_dont_cache => 1); /* turn caching off */
1389 END LOOP;
1390 END IF;
1391 ELSE
1392 IF p_jurisdiction IS NULL THEN
1393 FOR r_asg IN csr_get_asg_gre_add(p_organization_id,
1394 p_location_id,
1395 p_payroll_id,
1396 p_pay_basis_type,
1397 p_gre_id,
1398 p_start_date,
1399 p_effective_date) LOOP
1400 hr_utility.trace(l_routine_name||': 110');
1401 l_virtual_date := get_virtual_date (r_asg.assignment_id,
1402 p_effective_date,
1403 l_date_mask);
1404 l_balance_value := l_balance_value +
1405 pay_ca_balance_view_pkg.get_value
1406 (p_assignment_id => r_asg.assignment_id,
1407 p_defined_balance_id => l_lb_defined_balance_id,
1408 p_effective_date => l_virtual_date,
1409 p_dont_cache => 1); /* turn caching off */
1410 END LOOP;
1411 ELSE
1412 FOR r_asg IN csr_get_asg_jd_gre_add(p_organization_id,
1413 p_location_id,
1414 p_payroll_id,
1415 p_pay_basis_type,
1416 p_gre_id,
1417 p_jurisdiction,
1418 p_start_date,
1419 p_effective_date) LOOP
1420 hr_utility.trace(l_routine_name||': 120');
1421 l_virtual_date := get_virtual_date (r_asg.assignment_id,
1422 p_effective_date,
1423 l_date_mask);
1424 l_balance_value := l_balance_value +
1425 pay_ca_balance_view_pkg.get_value
1426 (p_assignment_id => r_asg.assignment_id,
1427 p_defined_balance_id => l_lb_defined_balance_id,
1428 p_effective_date => l_virtual_date,
1429 p_dont_cache => 1); /* turn caching off */
1430 END LOOP;
1431 END IF;
1432 END IF;
1433 END IF;
1434
1435 hr_utility.trace('Ending routine: '||l_routine_name);
1436 RETURN l_balance_value;
1437 END get_grp_non_pydate_with_asg;
1438
1439
1440 -------------------------------------------------------------------------------
1441 -- Name: get_grp_non_pydate_with_asg_rb
1442 --
1443 -- Parameters: p_assignment_id
1444 -- p_time_dimension
1445 -- p_lb_dimension
1446 -- p_gl_defined_balance_id
1447 -- p_balance_name
1448 -- p_effective_date
1449 -- p_start_date
1450 -- p_jurisdiction_code
1451 -- p_gre_id
1452 -- p_source_id
1453 -- p_organization_id
1454 -- p_location_id
1455 -- p_payroll_id
1456 -- p_pay_basis_type
1457 -- p_business_group_id
1458 --
1459 -- Return: NUMBER - The value of the group level balance
1460 --
1461 -- Description: This function calculates the balance value given an assignment
1462 -- id and date. If the latest balances exist they will be utilised.
1463 -- Uses pay_run_balances validation in all cursor definitions (EBRA)
1464 -------------------------------------------------------------------------------
1465 FUNCTION get_grp_non_pydate_with_asg_rb
1466 (p_assignment_id NUMBER,
1467 p_time_dimension VARCHAR2,
1468 p_lb_dimension VARCHAR2,
1469 p_gl_defined_balance_id NUMBER,
1470 p_balance_name VARCHAR2,
1471 p_effective_date DATE,
1472 p_start_date DATE,
1473 p_jurisdiction VARCHAR2,
1474 p_gre_id NUMBER,
1475 p_source_id NUMBER,
1476 p_organization_id NUMBER,
1477 p_location_id NUMBER,
1478 p_payroll_id NUMBER,
1479 p_pay_basis_type VARCHAR2,
1480 p_business_group_id NUMBER) RETURN NUMBER IS
1481
1482 CURSOR csr_latest_bal_exists(p_asg_id NUMBER,
1483 p_def_bal_id NUMBER,
1484 p_start_date DATE,
1485 p_end_date DATE) IS
1486 SELECT 'X'
1487 FROM SYS.DUAL
1488 WHERE EXISTS (SELECT 'X'
1489 FROM pay_payroll_actions pya,
1490 pay_assignment_actions asa,
1491 pay_assignment_latest_balances alb
1492 WHERE alb.assignment_id = p_asg_id
1493 AND alb.defined_balance_id = p_def_bal_id
1494 AND alb.assignment_action_id = asa.assignment_action_id
1495 AND asa.payroll_action_id = pya.payroll_action_id
1496 AND pya.effective_date BETWEEN p_start_date
1497 AND p_end_date);
1498
1499 /*
1500 * Select all the assignments in PAY_ACTION_CONTEXTS for a given GRE
1501 * which have been run in a given time period.
1502 * These are for the cases where all the additional parameter ie. location_id,
1503 * organization_id, payroll_id, pay_basis_type are NULL
1504 */
1505
1506 -- NEW EBRA CURSOR csr_get_asg_gre definition with
1507 -- pay_run_balances validation.
1508 CURSOR csr_get_asg_gre_rb(p_gre_id NUMBER,
1509 p_start_date DATE,
1510 p_end_date DATE,
1511 p_def_bal_id NUMBER) IS
1512 SELECT DISTINCT(asg.assignment_id) assignment_id
1513 FROM per_all_assignments_f asg
1514 WHERE EXISTS (SELECT 'X'
1515 FROM pay_run_balances prb
1516 WHERE prb.assignment_id = asg.assignment_id
1517 AND prb.tax_unit_id = p_gre_id
1518 AND prb.defined_balance_id = p_def_bal_id
1519 AND prb.effective_date BETWEEN p_start_date
1520 AND p_end_date);
1521
1522
1523 -- NEW EBRA CURSOR csr_get_asg_src definition with
1524 -- pay_run_balances validation.
1525 CURSOR csr_get_asg_src_rb(p_src_id NUMBER,
1526 p_start_date DATE,
1527 p_end_date DATE
1528 ) IS
1529 SELECT DISTINCT(prb.assignment_id) assignment_id
1530 FROM pay_run_balances prb
1531 WHERE prb.source_id = p_src_id
1532 AND prb.effective_date BETWEEN p_start_date AND p_end_date;
1533
1534
1535 /*
1536 * Select all the assignments in PAY_ACTION_CONTEXTS for a given GRE and
1537 * jurisdiction which have been run in a given time period.
1538 * These are for the cases where all the additional parameter ie. location_id,
1539 * organization_id, payroll_id, pay_basis_type are NULL
1540 */
1541 -- NEW EBRA CURSOR csr_get_asg_jd_gre definition with
1542 -- pay_run_balances validation.
1543 CURSOR csr_get_asg_jd_gre_rb(p_gre_id NUMBER,
1544 p_jd VARCHAR2,
1545 p_start_date DATE,
1546 p_end_date DATE,
1547 p_def_bal_id NUMBER) IS
1548 SELECT DISTINCT(prb.assignment_id) assignment_id
1549 FROM pay_run_balances prb
1550 WHERE prb.tax_unit_id = p_gre_id
1551 AND prb.jurisdiction_code = p_jd
1552 AND prb.defined_balance_id = p_def_bal_id
1553 AND prb.effective_date BETWEEN p_start_date AND p_end_date;
1554
1555
1556
1557 CURSOR csr_get_asg_jd_src(p_src_id NUMBER,
1558 p_jd VARCHAR2,
1559 p_start_date DATE,
1560 p_end_date DATE) IS
1561 SELECT DISTINCT(acx1.assignment_id) assignment_id
1562 FROM pay_action_contexts acx1,
1563 pay_action_contexts acx2,
1564 ff_contexts cxt1,
1565 ff_contexts cxt2
1566 WHERE cxt1.context_name = 'SOURCE_ID'
1567 AND cxt1.context_id = acx1.context_id
1568 AND acx1.context_value = TO_CHAR(p_src_id)
1569 AND cxt2.context_name = 'JURISDICTION_CODE'
1570 AND cxt2.context_id = acx2.context_id
1571 AND acx2.context_value = p_jd
1572 AND acx1.assignment_action_id = acx2.assignment_action_id
1573 AND EXISTS (SELECT 'X'
1574 FROM pay_payroll_actions pya,
1575 pay_assignment_actions asa
1576 WHERE asa.assignment_action_id = acx1.assignment_action_id
1577 AND pya.payroll_action_id = asa.payroll_action_id
1578 AND pya.effective_date BETWEEN p_start_date
1579 AND p_end_date);
1580
1581 /*
1582 * Select all the assignments in PAY_ACTION_CONTEXTS for a given GRE
1583 * which have been run in a given time period.
1584 * These are for the cases where at least one of the additional parameter is
1585 * NULL, (ie. l,ocation_id, organization_id, payroll_id, pay_basis_type)
1586 */
1587
1588 -- NEW CURSOR csr_get_asg_gre_add definition with
1589 -- pay_run_balances validation.
1590 CURSOR csr_get_asg_gre_add_rb(p_org_id NUMBER,
1591 p_loc_id NUMBER,
1592 p_pay_id NUMBER,
1593 p_basis_type VARCHAR2,
1594 p_gre_id NUMBER,
1595 p_start_date DATE,
1596 p_end_date DATE,
1597 p_def_bal_id NUMBER) IS
1598 SELECT DISTINCT(asg.assignment_id) assignment_id
1599 FROM per_all_assignments_f asg
1600 WHERE asg.organization_id = NVL(p_org_id, asg.organization_id)
1601 AND asg.location_id = NVL(p_loc_id, asg.location_id)
1602 AND asg.payroll_id = NVL(p_pay_id, asg.payroll_id)
1603 AND ((p_basis_type = 'OTHER'
1604 AND asg.pay_basis_id IS NULL)
1605 OR (p_basis_type = 'HOURLY'
1606 AND asg.pay_basis_id IN (SELECT pyb.pay_basis_id
1607 FROM per_pay_bases pyb
1608 WHERE pyb.pay_basis = 'HOURLY'))
1609 OR (p_basis_type = 'SALARIED'
1610 AND asg.pay_basis_id IN (SELECT pyb.pay_basis_id
1611 FROM per_pay_bases pyb
1612 WHERE pyb.pay_basis IN
1613 ('ANNUAL','MONTHLY','PERIOD')))
1614 OR (p_basis_type IS NULL))
1615 AND EXISTS (SELECT 'X'
1616 FROM pay_run_balances prb
1617 WHERE prb.assignment_id = asg.assignment_id
1618 AND prb.tax_unit_id = p_gre_id
1619 AND prb.defined_balance_id = p_def_bal_id
1620 AND prb.effective_date BETWEEN p_start_date
1621 AND p_end_date);
1622
1623
1624 -- NEW EBRA CURSOR csr_get_asg_src_add definition with
1625 -- pay_run_balances validation
1626 CURSOR csr_get_asg_src_add_rb(p_org_id NUMBER,
1627 p_loc_id NUMBER,
1628 p_pay_id NUMBER,
1629 p_basis_type VARCHAR2,
1630 p_src_id NUMBER,
1631 p_start_date DATE,
1632 p_end_date DATE,
1633 p_def_bal_id NUMBER) IS
1634 SELECT DISTINCT(asg.assignment_id) assignment_id
1635 FROM per_all_assignments_f asg
1636 WHERE EXISTS (SELECT 'X'
1637 FROM pay_run_balances prb
1638 WHERE asg.assignment_id = prb.assignment_id
1639 AND prb.source_id = p_src_id
1640 AND prb.defined_balance_id = p_def_bal_id
1641 AND prb.effective_date BETWEEN p_start_date
1642 AND p_end_date)
1643 AND asg.organization_id = NVL(p_org_id, asg.organization_id)
1644 AND asg.location_id = NVL(p_loc_id, asg.location_id)
1645 AND asg.payroll_id = NVL(p_pay_id, asg.payroll_id)
1646 AND ((p_basis_type = 'OTHER'
1647 AND asg.pay_basis_id IS NULL)
1648 OR (p_basis_type = 'HOURLY'
1649 AND asg.pay_basis_id IN (SELECT pyb.pay_basis_id
1650 FROM per_pay_bases pyb
1651 WHERE pyb.pay_basis = 'HOURLY'))
1652 OR (p_basis_type = 'SALARIED'
1653 AND asg.pay_basis_id IN (SELECT pyb.pay_basis_id
1654 FROM per_pay_bases pyb
1655 WHERE pyb.pay_basis IN
1656 ('ANNUAL','MONTHLY','PERIOD')))
1657 OR (p_basis_type IS NULL));
1658
1659
1660
1661 /*
1662 * Select all the assignments in PAY_ACTION_CONTEXTS for a given GRE and
1663 * jurisdiction which have been run in a given time period.
1664 * These are for the cases where at least one of the additional parameter is
1665 * NULL, (ie. l,ocation_id, organization_id, payroll_id, pay_basis_type)
1666 */
1667
1668 -- NEW EBRA CURSOR csr_get_asg_jd_gre_add definition with
1669 -- pay_run_balances validation.
1670 CURSOR csr_get_asg_jd_gre_add_rb(p_org_id NUMBER,
1671 p_loc_id NUMBER,
1672 p_pay_id NUMBER,
1673 p_basis_type VARCHAR2,
1674 p_gre_id NUMBER,
1675 p_jd VARCHAR2,
1676 p_start_date DATE,
1677 p_end_date DATE,
1678 p_def_bal_id NUMBER) IS
1679 SELECT DISTINCT(asg.assignment_id) assignment_id
1680 FROM per_all_assignments_f asg
1681 WHERE EXISTS (SELECT 'X'
1682 FROM pay_run_balances prb
1683 WHERE prb.assignment_id = asg.assignment_id
1684 AND prb.tax_unit_id = p_gre_id
1685 AND prb.jurisdiction_code = p_jd
1686 AND prb.defined_balance_id = p_def_bal_id
1687 AND prb.effective_date BETWEEN p_start_date
1688 AND p_end_date)
1689 AND asg.organization_id = NVL(p_org_id, asg.organization_id)
1690 AND asg.location_id = NVL(p_loc_id, asg.location_id)
1691 AND asg.payroll_id = NVL(p_pay_id, asg.payroll_id)
1692 AND ((p_basis_type = 'OTHER'
1693 AND asg.pay_basis_id IS NULL)
1694 OR (p_basis_type = 'HOURLY'
1695 AND asg.pay_basis_id IN (SELECT pyb.pay_basis_id
1696 FROM per_pay_bases pyb
1697 WHERE pyb.pay_basis = 'HOURLY'))
1698 OR (p_basis_type = 'SALARIED'
1699 AND asg.pay_basis_id IN (SELECT pyb.pay_basis_id
1700 FROM per_pay_bases pyb
1701 WHERE pyb.pay_basis IN
1702 ('ANNUAL','MONTHLY','PERIOD')))
1703 OR (p_basis_type IS NULL));
1704
1705
1706 CURSOR csr_get_asg_jd_src_add(p_org_id NUMBER,
1707 p_loc_id NUMBER,
1708 p_pay_id NUMBER,
1709 p_basis_type VARCHAR2,
1710 p_src_id NUMBER,
1711 p_jd VARCHAR2,
1712 p_start_date DATE,
1713 p_end_date DATE) IS
1714 SELECT DISTINCT(acx1.assignment_id) assignment_id
1715 FROM per_all_assignments_f asg,
1716 pay_action_contexts acx2,
1717 pay_action_contexts acx1,
1718 ff_contexts cxt2,
1719 ff_contexts cxt1
1720 WHERE cxt1.context_name = 'SOURCE_ID'
1721 AND cxt1.context_id = acx1.context_id
1722 AND acx1.context_value = TO_CHAR(p_src_id)
1723 AND acx1.assignment_id = asg.assignment_id
1724 AND cxt2.context_name = 'JURISDICTION_CODE'
1725 AND cxt2.context_id = acx2.context_id
1726 AND acx2.context_value = p_jd
1727 AND acx2.assignment_id = asg.assignment_id
1728 AND acx1.assignment_action_id = acx2.assignment_action_id
1729 AND EXISTS (SELECT 'X'
1730 FROM pay_payroll_actions pya,
1731 pay_assignment_actions asa
1732 WHERE asa.assignment_action_id = acx1.assignment_action_id
1733 AND asa.tax_unit_id = p_gre_id
1734 AND pya.payroll_action_id = asa.payroll_action_id
1735 AND pya.effective_date BETWEEN p_start_date
1736 AND p_end_date)
1737 AND asg.organization_id = NVL(p_org_id, asg.organization_id)
1738 AND asg.location_id = NVL(p_loc_id, asg.location_id)
1739 AND asg.payroll_id = NVL(p_pay_id, asg.payroll_id)
1740 AND ((p_basis_type = 'OTHER'
1741 AND asg.pay_basis_id IS NULL)
1742 OR (p_basis_type = 'HOURLY'
1743 AND asg.pay_basis_id IN (SELECT pyb.pay_basis_id
1744 FROM per_pay_bases pyb
1745 WHERE pyb.pay_basis = 'HOURLY'))
1746 OR (p_basis_type = 'SALARIED'
1747 AND asg.pay_basis_id IN (SELECT pyb.pay_basis_id
1748 FROM per_pay_bases pyb
1749 WHERE pyb.pay_basis IN
1750 ('ANNUAL','MONTHLY','PERIOD')))
1751 OR (p_basis_type IS NULL));
1752
1753
1754 l_routine_name VARCHAR2(64) := 'pay_ca_group_level_bal_pkg.get_grp_non_pydate_with_asg_rb';
1755
1756 l_lb_defined_balance_id NUMBER(9);
1757 l_latest_bal_exists VARCHAR2(1);
1758 l_virtual_date DATE;
1759 l_balance_value NUMBER(38,10) := 0;
1760 l_date_mask VARCHAR2(5);
1761 l_additional_params VARCHAR2(1);
1762 l_ge_def_bal_id NUMBER(20);
1763
1764 BEGIN
1765 hr_utility.trace('Starting routine: '||l_routine_name);
1766
1767
1768 /* To check in run_balances table with def_val_id and get valid assignment
1769 or assignment_action for balance calls */
1770
1771 If p_jurisdiction is NULL then
1772 l_ge_def_bal_id := get_defined_balance(p_balance_name,
1773 'ASG_GRE_RUN',
1774 p_business_group_id);
1775 hr_utility.trace('Def_bal_id of '||p_balance_name||'_ASG_GRE_RUN : '||to_char(l_ge_def_bal_id));
1776 else
1777 l_ge_def_bal_id := get_defined_balance(p_balance_name,
1778 'ASG_JD_GRE_RUN',
1779 p_business_group_id);
1780 hr_utility.trace('Def_bal_id of '||p_balance_name||'_ASG_JD_GRE_RUN : '||to_char(l_ge_def_bal_id));
1781 End if;
1782
1783
1784 hr_utility.trace('Def_bal_id of GROSS_EARNINGS_ASG_JD_GRE_RUN : '||to_char(l_ge_def_bal_id));
1785
1786 l_lb_defined_balance_id := get_defined_balance(p_balance_name,
1787 p_lb_dimension,
1788 p_business_group_id);
1789 IF l_lb_defined_balance_id IS NULL THEN
1790 RETURN NULL;
1791 END IF;
1792
1793 /*
1794 * Have any additional parmaeters been specified (location, organization,
1795 * payroll, paybasis type) ?
1796 */
1797 IF p_organization_id IS NULL AND
1798 p_location_id IS NULL AND
1799 p_payroll_id IS NULL AND
1800 p_pay_basis_type IS NULL THEN
1801 hr_utility.trace(l_routine_name||': 5');
1802 l_additional_params := 'N';
1803 ELSE
1804 hr_utility.trace(l_routine_name||': 10');
1805 l_additional_params := 'Y';
1806 END IF;
1807
1808 /*
1809 * First check whether latest balances exist for the given assignment
1810 * on the given date
1811 */
1812 OPEN csr_latest_bal_exists(p_assignment_id,
1813 l_lb_defined_balance_id,
1814 p_start_date,
1815 p_effective_date);
1816 FETCH csr_latest_bal_exists INTO l_latest_bal_exists;
1817 IF csr_latest_bal_exists%NOTFOUND AND
1818 l_additional_params = 'N' THEN
1819 hr_utility.trace(l_routine_name||': 20');
1820 /*
1821 * No latest balances found so calculate the group level balance from
1822 * first principles
1823 */
1824 l_balance_value := pay_ca_balance_view_pkg.get_value
1825 (p_assignment_id,
1826 p_gl_defined_balance_id,
1827 p_effective_date);
1828
1829 ELSIF p_source_id IS NOT NULL THEN
1830 /*
1831 * Loop through all the assignments for this Reporting Unit (Source Id)
1832 * and sum the latest balance value for each one
1833 */
1834 hr_utility.trace(l_routine_name||': 30');
1835 l_date_mask := get_date_mask(p_time_dimension);
1836 IF l_additional_params = 'N' THEN
1837 IF p_jurisdiction IS NULL THEN
1838 FOR r_asg IN csr_get_asg_src_rb(p_source_id,
1839 p_start_date,
1840 p_effective_date) LOOP
1841 hr_utility.trace(l_routine_name||': 40');
1842
1843 IF p_time_dimension <> 'PYDATE' THEN
1844 l_virtual_date := get_virtual_date (r_asg.assignment_id,
1845 p_effective_date,
1846 l_date_mask);
1847 END IF;
1848 l_balance_value := l_balance_value +
1849 pay_ca_balance_view_pkg.get_value(r_asg.assignment_id,
1850 l_lb_defined_balance_id,
1851 l_virtual_date,
1852 1); /*turn caching off*/
1853 END LOOP;
1854 ELSE
1855 FOR r_asg IN csr_get_asg_jd_src(p_source_id,
1856 p_jurisdiction,
1857 p_start_date,
1858 p_effective_date) LOOP
1859 hr_utility.trace(l_routine_name||': 50');
1860 l_virtual_date := get_virtual_date (r_asg.assignment_id,
1861 p_effective_date,
1862 l_date_mask);
1863 l_balance_value := l_balance_value +
1864 pay_ca_balance_view_pkg.get_value(r_asg.assignment_id,
1865 l_lb_defined_balance_id,
1866 l_virtual_date,
1867 1); /*turn caching off*/
1868 END LOOP;
1869 END IF;
1870 ELSE
1871 IF p_jurisdiction IS NULL THEN
1872 FOR r_asg IN csr_get_asg_src_add_rb(p_organization_id,
1873 p_location_id,
1874 p_payroll_id,
1875 p_pay_basis_type,
1876 p_source_id,
1877 p_start_date,
1878 p_effective_date,
1879 l_ge_def_bal_id)
1880 LOOP
1881 hr_utility.trace(l_routine_name||': 60');
1882 l_virtual_date := get_virtual_date (r_asg.assignment_id,
1883 p_effective_date,
1884 l_date_mask);
1885 l_balance_value := l_balance_value +
1886 pay_ca_balance_view_pkg.get_value (r_asg.assignment_id,
1887 l_lb_defined_balance_id,
1888 l_virtual_date,
1889 1); /*turn caching off*/
1890 END LOOP;
1891 ELSE
1892 FOR r_asg IN csr_get_asg_jd_src_add(p_organization_id,
1893 p_location_id,
1894 p_payroll_id,
1895 p_pay_basis_type,
1896 p_source_id,
1897 p_jurisdiction,
1898 p_start_date,
1899 p_effective_date) LOOP
1900 hr_utility.trace(l_routine_name||': 70');
1901 l_virtual_date := get_virtual_date (r_asg.assignment_id,
1902 p_effective_date,
1903 l_date_mask);
1904 l_balance_value := l_balance_value +
1905 pay_ca_balance_view_pkg.get_value (r_asg.assignment_id,
1906 l_lb_defined_balance_id,
1907 l_virtual_date,
1908 1); /*turn caching off*/
1909 END LOOP;
1910 END IF;
1911 END IF;
1912 ELSE
1913 /*
1914 * Loop through all the assignments for this GRE and sum the latest
1915 * balance value for each one
1916 */
1917 hr_utility.trace(l_routine_name||': 80');
1918 IF p_time_dimension <> 'PYDATE' THEN
1919 l_date_mask := get_date_mask(p_time_dimension);
1920 END IF;
1921 IF l_additional_params = 'N' THEN
1922 IF p_jurisdiction IS NULL THEN
1923 FOR r_asg IN csr_get_asg_gre_rb(p_gre_id,
1924 p_start_date,
1925 p_effective_date,
1926 l_ge_def_bal_id) LOOP
1927 hr_utility.trace(l_routine_name||': 90');
1928 l_virtual_date := get_virtual_date (r_asg.assignment_id,
1929 p_effective_date,
1930 l_date_mask);
1931 l_balance_value := l_balance_value +
1932 pay_ca_balance_view_pkg.get_value
1933 (p_assignment_id => r_asg.assignment_id,
1934 p_defined_balance_id => l_lb_defined_balance_id,
1935 p_effective_date => l_virtual_date,
1936 p_dont_cache => 1); /* turn caching off */
1937 END LOOP;
1938 ELSE
1939 FOR r_asg IN csr_get_asg_jd_gre_rb(p_gre_id,
1940 p_jurisdiction,
1941 p_start_date,
1942 p_effective_date,
1943 l_ge_def_bal_id) LOOP
1944 hr_utility.trace(l_routine_name||': 100');
1945 l_virtual_date := get_virtual_date (r_asg.assignment_id,
1946 p_effective_date,
1947 l_date_mask);
1948 l_balance_value := l_balance_value +
1949 pay_ca_balance_view_pkg.get_value
1950 (p_assignment_id => r_asg.assignment_id,
1951 p_defined_balance_id => l_lb_defined_balance_id,
1952 p_effective_date => l_virtual_date,
1953 p_dont_cache => 1); /* turn caching off */
1954 END LOOP;
1955 END IF;
1956 ELSE
1957 IF p_jurisdiction IS NULL THEN
1958 FOR r_asg IN csr_get_asg_gre_add_rb(p_organization_id,
1959 p_location_id,
1960 p_payroll_id,
1961 p_pay_basis_type,
1962 p_gre_id,
1963 p_start_date,
1964 p_effective_date,
1965 l_ge_def_bal_id) LOOP
1966 hr_utility.trace(l_routine_name||': 110');
1967 l_virtual_date := get_virtual_date (r_asg.assignment_id,
1968 p_effective_date,
1969 l_date_mask);
1970 l_balance_value := l_balance_value +
1971 pay_ca_balance_view_pkg.get_value
1972 (p_assignment_id => r_asg.assignment_id,
1973 p_defined_balance_id => l_lb_defined_balance_id,
1974 p_effective_date => l_virtual_date,
1975 p_dont_cache => 1); /* turn caching off */
1976 END LOOP;
1977 ELSE
1978 FOR r_asg IN csr_get_asg_jd_gre_add_rb(p_organization_id,
1979 p_location_id,
1980 p_payroll_id,
1981 p_pay_basis_type,
1982 p_gre_id,
1983 p_jurisdiction,
1984 p_start_date,
1985 p_effective_date,
1986 l_ge_def_bal_id) LOOP
1987 hr_utility.trace(l_routine_name||': 120');
1988 l_virtual_date := get_virtual_date (r_asg.assignment_id,
1989 p_effective_date,
1990 l_date_mask);
1991 l_balance_value := l_balance_value +
1992 pay_ca_balance_view_pkg.get_value
1993 (p_assignment_id => r_asg.assignment_id,
1994 p_defined_balance_id => l_lb_defined_balance_id,
1995 p_effective_date => l_virtual_date,
1996 p_dont_cache => 1); /* turn caching off */
1997 END LOOP;
1998 END IF;
1999 END IF;
2000 END IF;
2001
2002 hr_utility.trace('Ending routine: '||l_routine_name);
2003 RETURN l_balance_value;
2004 END get_grp_non_pydate_with_asg_rb;
2005
2006
2007 -------------------------------------------------------------------------------
2008 -- Name: ca_group_level_balance
2009 --
2010 -- Parameters:
2011 -- p_balance_name - Mandatory
2012 -- p_time_dimension - Mandatory (MONTH, QTD, YTD, PYDATE, PTD)
2013 -- p_effective_date - Mandatory
2014 -- p_start_date - If this date is entered a PYDATE grouping is performed
2015 -- i.e. all assignments between p_start_date and
2016 -- p_effective_date are summed.
2017 -- These is always grouped within GRE - not Source Id.
2018 -- These field is mandatory if p_time_dimension is PYDATE.
2019 -- p_source_id - Either p_source_id or p_gre_id must be entered.
2020 -- p_time_dimension must be one of MONTH, QTD or YTD.
2021 -- p_gre_id - Either p_source_id or p_gre_id must be entered.
2022 -- p_time_dimension must be one of MONTH, QTD or YTD.
2023 -- p_jurisdiction - If the jurisdiction code is enter the dimension will
2024 -- be within jurisdiction.
2025 -- p_organization_id - If the organization id is specified then this
2026 -- routine will only get assignments for that
2027 -- organization.
2028 -- p_location_id - If the location id is specified then this routine
2029 -- will only get assignments for that location.
2030 -- p_payroll_id - If the payroll id is specified then this routine
2031 -- will only get assignments for that payroll.
2032 -- p_pay_basis_type - HOURLY - only assignments with hourly pay bases
2033 -- will be included
2034 -- SALARIED - only assignments with non-hourly pay
2035 -- bases will be included
2036 -- OTHER - only assignments with no pay bases
2037 -- will be included
2038 --
2039 -- Return: NUMBER - Group level balance total
2040 --
2041 -- Description: This is the main calling routine for calculating Canadian
2042 -- Group Level Balances. This routine will initially be used for
2043 -- the following reports:
2044 -- Provincial Medical
2045 -- Workers Compensation
2046 -- Tax Deduction
2047 -- Statistics
2048 -- Gross to Net
2049 -------------------------------------------------------------------------------
2050 FUNCTION ca_group_level_balance (p_balance_name VARCHAR2,
2051 p_time_dimension VARCHAR2,
2052 p_effective_date DATE,
2053 p_start_date DATE,
2054 p_source_id NUMBER,
2055 p_gre_id NUMBER,
2056 p_jurisdiction VARCHAR2,
2057 p_organization_id NUMBER,
2058 p_location_id NUMBER,
2059 p_payroll_id NUMBER,
2060 p_pay_basis_type VARCHAR2) RETURN NUMBER IS
2061 /*
2062 * Cursor to find a sample assignment id for the relevant PYDATE.
2063 */
2064 CURSOR csr_asg_exists_for_gre (p_org_id NUMBER,
2065 p_loc_id NUMBER,
2066 p_pay_id NUMBER,
2067 p_basis_type VARCHAR2,
2068 p_start_date DATE,
2069 p_end_date DATE,
2070 p_gre NUMBER,
2071 p_gre_type VARCHAR2) IS
2072 SELECT asg.assignment_id,
2073 asg.business_group_id
2074 FROM per_all_assignments_f asg,
2075 hr_soft_coding_keyflex sck
2076 WHERE decode(p_gre_type, 'T4A/RL1', sck.segment11, 'T4A/RL2', sck.segment12,
2077 sck.segment1) = TO_CHAR(p_gre)
2078 AND asg.soft_coding_keyflex_id = sck.soft_coding_keyflex_id
2079 AND EXISTS(SELECT 'X'
2080 FROM pay_assignment_actions asa,
2081 pay_payroll_actions pya
2082 WHERE asa.tax_unit_id = p_gre
2083 AND asg.assignment_id = asa.assignment_id
2084 AND pya.payroll_action_id = asa.payroll_action_id
2085 AND pya.effective_date BETWEEN p_start_date
2086 AND p_end_date
2087 AND pya.action_type IN ('R', 'Q', 'I', 'V', 'B')
2088 AND pya.effective_date BETWEEN asg.effective_start_date
2089 AND asg.effective_end_date)
2090 AND asg.organization_id = NVL(p_org_id, asg.organization_id)
2091 AND asg.location_id = NVL(p_loc_id, asg.location_id)
2092 AND asg.payroll_id = NVL(p_pay_id, asg.payroll_id)
2093 AND ((p_basis_type = 'OTHER'
2094 AND asg.pay_basis_id IS NULL)
2095 OR (p_basis_type = 'HOURLY'
2096 AND asg.pay_basis_id IN (SELECT pyb.pay_basis_id
2097 FROM per_pay_bases pyb
2098 WHERE pyb.pay_basis = 'HOURLY'))
2099 OR (p_basis_type = 'SALARIED'
2100 AND asg.pay_basis_id IN (SELECT pyb.pay_basis_id
2101 FROM per_pay_bases pyb
2102 WHERE pyb.pay_basis IN
2103 ('ANNUAL','MONTHLY','PERIOD')))
2104 OR (p_basis_type IS NULL))
2105 AND ROWNUM=1;
2106
2107 cursor c_get_bg_id1(cp_gre_id number) is
2108 select business_group_id
2109 from hr_all_organization_units
2110 where organization_id = cp_gre_id;
2111
2112 /* Cursor to get assignment id without the organization_id, location_id,
2113 payroll_id and pay_basis_type parameters and added this cursor
2114 to fix the bug#2391970 */
2115
2116 CURSOR csr_asg_for_gre_only_rb1 ( p_start_date DATE,
2117 p_end_date DATE,
2118 p_gre NUMBER,
2119 cp_def_bal_id NUMBER) IS
2120 select prb.assignment_id,paf.business_group_id
2121 from pay_run_balances prb,per_all_assignments_f paf
2122 where prb.defined_balance_id = cp_def_bal_id
2123 and prb.tax_unit_id = p_gre
2124 and prb.effective_date between p_start_date AND p_end_date
2125 and prb.assignment_id = paf.assignment_id
2126 and prb.effective_date between paf.effective_start_date AND
2127 paf.effective_end_date
2128 and rownum = 1;
2129
2130 CURSOR csr_asg_exists_for_gre_only ( p_start_date DATE,
2131 p_end_date DATE,
2132 p_gre NUMBER) IS
2133 SELECT asa.assignment_id,pya.business_group_id
2134 FROM pay_payroll_actions pya,
2135 pay_assignment_actions asa
2136 WHERE asa.tax_unit_id = p_gre
2137 AND pya.payroll_action_id = asa.payroll_action_id
2138 AND pya.effective_date BETWEEN p_start_date
2139 AND p_end_date
2140 AND pya.action_type IN ('R', 'Q', 'I', 'V', 'B')
2141 AND rownum = 1;
2142
2143 /*
2144 * Cursor to find a sample assignment id for the relevant Source Id.
2145 */
2146 CURSOR csr_asg_exists_for_src (p_org_id NUMBER,
2147 p_loc_id NUMBER,
2148 p_pay_id NUMBER,
2149 p_basis_type VARCHAR2,
2150 p_start_date DATE,
2151 p_end_date DATE,
2152 p_src NUMBER) IS
2153 SELECT asg.assignment_id,
2154 asg.business_group_id
2155 FROM pay_payroll_actions pya,
2156 per_all_assignments_f asg,
2157 pay_assignment_actions asa,
2158 pay_action_contexts acx,
2159 ff_contexts cxt
2160 WHERE acx.context_value = TO_CHAR(p_src)
2161 AND cxt.context_id = acx.context_id
2162 AND cxt.context_name = 'SOURCE_ID'
2163 AND asa.assignment_action_id = acx.assignment_action_id
2164 AND asg.assignment_id = asa.assignment_id
2165 AND asg.organization_id = NVL(p_organization_id, asg.organization_id)
2166 AND asg.location_id = NVL(p_location_id, asg.location_id)
2167 AND asg.payroll_id = NVL(p_payroll_id, asg.payroll_id)
2168 AND ((p_basis_type = 'OTHER'
2169 AND asg.pay_basis_id IS NULL)
2170 OR (p_basis_type = 'HOURLY'
2171 AND asg.pay_basis_id IN (SELECT pyb.pay_basis_id
2172 FROM per_pay_bases pyb
2173 WHERE pyb.pay_basis = 'HOURLY'))
2174 OR (p_basis_type = 'SALARIED'
2175 AND asg.pay_basis_id IN (SELECT pyb.pay_basis_id
2176 FROM per_pay_bases pyb
2177 WHERE pyb.pay_basis IN
2178 ('ANNUAL','MONTHLY','PERIOD')))
2179 OR (p_basis_type IS NULL))
2180 AND pya.payroll_action_id = asa.payroll_action_id
2181 AND pya.effective_date BETWEEN p_start_date
2182 AND p_end_date
2183 AND pya.action_type IN ('R', 'Q', 'I', 'V', 'B')
2184 AND pya.effective_date BETWEEN asg.effective_start_date
2185 AND asg.effective_end_date
2186 AND ROWNUM = 1;
2187
2188
2189 l_routine_name VARCHAR2(64) := 'pay_ca_group_level_bal_pkg.ca_group_level_balance';
2190
2191 l_dim_str1 VARCHAR2(60);
2192 l_gl_dimension VARCHAR2(60);
2193 l_lb_dimension VARCHAR2(60);
2194 l_assignment_id NUMBER(15);
2195 l_defined_balance_id NUMBER(15);
2196 l_balance_value NUMBER(38,10);
2197 l_gl_defined_balance_id NUMBER(15);
2198 l_balance_name VARCHAR(60);
2199 l_start_date DATE;
2200 l_date_context VARCHAR2(30);
2201 l_business_group_id NUMBER;
2202 lv_gre_type VARCHAR2(60);
2203 ln_bg_id number;
2204 ln_run_balance_status varchar2(1);
2205 ln_defbal_id number;
2206
2207 e_invalid_time_dimension EXCEPTION;
2208 e_no_gre_or_source_id EXCEPTION;
2209 e_no_assignments EXCEPTION;
2210 e_no_gre_specified EXCEPTION;
2211 e_no_period_specified EXCEPTION;
2212 e_no_source_id_specified EXCEPTION;
2213 e_jurisdiction_must_be_null EXCEPTION;
2214 e_invalid_dim_date_comb EXCEPTION;
2215 e_no_rpt_unit_for_pydate EXCEPTION;
2216 e_no_start_date_for_pydate EXCEPTION;
2217
2218
2219 BEGIN
2220
2221 /* hr_utility.trace_on(NULL,'ORASDR'); */
2222 hr_utility.trace('Starting routine: '||l_routine_name);
2223 pay_ca_balance_view_pkg.set_context('DATE_EARNED',
2224 fnd_date.date_to_canonical(p_effective_date));
2225
2226 l_balance_name := p_balance_name;
2227
2228 IF p_time_dimension <> 'PYDATE' AND
2229 p_time_dimension <> 'PTD' AND
2230 p_time_dimension <> 'MONTH' AND
2231 p_time_dimension <> 'QTD' AND
2232 p_time_dimension <> 'YTD' THEN
2233 RAISE e_invalid_time_dimension;
2234 END IF;
2235
2236 IF p_time_dimension <> 'PYDATE' AND
2237 p_start_date IS NULL THEN
2238 hr_utility.trace(l_routine_name||': 10');
2239 IF p_time_dimension = 'MONTH' THEN
2240 l_start_date := TRUNC(p_effective_date,'MONTH');
2241 ELSIF p_time_dimension = 'YTD' THEN
2242 l_start_date := TRUNC(p_effective_date,'Y');
2243 ELSIF p_time_dimension = 'QTD' THEN
2244 l_start_date := TRUNC(p_effective_date,'Q');
2245 END IF;
2246 ELSIF p_time_dimension = 'PYDATE' THEN
2247 hr_utility.trace(l_routine_name||': 20');
2248 IF p_start_date IS NULL THEN
2249 l_date_context := pay_ca_balance_view_pkg.get_context('BALANCE_DATE');
2250 IF l_date_context IS NOT NULL THEN
2251 l_start_date := fnd_date.canonical_to_date(l_date_context);
2252 ELSE
2253 RAISE e_no_start_date_for_pydate;
2254 END IF;
2255 ELSE
2256 l_start_date := p_start_date;
2257 pay_ca_balance_view_pkg.set_context('BALANCE_DATE',
2258 fnd_date.date_to_canonical(p_start_date));
2259 END IF;
2260 ELSIF p_time_dimension = 'PTD' THEN
2261 hr_utility.trace(l_routine_name||': 25');
2262 l_start_date := NULL;
2263 ELSE
2264 RAISE e_invalid_dim_date_comb;
2265 END IF;
2266
2267 IF p_gre_id IS NOT NULL THEN
2268 hr_utility.trace(l_routine_name||': 30');
2269
2270 pay_ca_balance_view_pkg.set_context('TAX_UNIT_ID',
2271 p_gre_id);
2272
2273 l_dim_str1 := 'GRE_';
2274
2275
2276 /* Getting GRE Type of tax unit for Multi GRE functionality
2277 Based on the gre type, the segment will be used in where clause.
2278
2279 T4/RL1 - Segment1
2280 T4A/RL1 - Segment11
2281 T4A/RL2 - Segment12
2282 */
2283
2284 begin
2285 select org_information5
2286 into lv_gre_type
2287 from hr_organization_information hoi
2288 where hoi.org_information_context = 'Canada Employer Identification'
2289 and hoi.organization_id = p_gre_id;
2290
2291 exception
2292 when others then
2293 null;
2294
2295 end;
2296
2297
2298 /*
2299 * Find one assignment that is included in this GRE
2300 */
2301 /* Commented out to fix the bug#2391970
2302 OPEN csr_asg_exists_for_gre(p_organization_id,
2303 p_location_id,
2304 p_payroll_id,
2305 p_pay_basis_type,
2306 l_start_date,
2307 p_effective_date,
2308 p_gre_id);
2309 FETCH csr_asg_exists_for_gre INTO l_assignment_id,
2310 l_business_group_id;
2311 CLOSE csr_asg_exists_for_gre;
2312 */
2313
2314 /* Added this condition to fix the bug#2391970 */
2315 IF p_organization_id is NULL and p_location_id is NULL
2316 and p_payroll_id is NULL and p_pay_basis_type IS null THEN
2317
2318 open c_get_bg_id1(p_gre_id);
2319
2320 fetch c_get_bg_id1 into ln_bg_id;
2321 hr_utility.trace('ln_bg_id: '||to_char(ln_bg_id));
2322 close c_get_bg_id1;
2323
2324 ln_run_balance_status := pay_us_payroll_utils.check_balance_status
2325 (l_start_date,
2326 ln_bg_id,
2327 'PAY_CA_YEPP_BALANCES',
2328 'CA');
2329
2330 IF ln_run_balance_status = 'Y' THEN
2331
2332 ln_defbal_id := get_defined_balance(l_balance_name,
2333 'ASG_GRE_RUN',
2334 ln_bg_id);
2335
2336
2337 hr_utility.trace('opening rb1 cursor');
2338 OPEN csr_asg_for_gre_only_rb1(l_start_date,
2339 p_effective_date,
2340 p_gre_id,
2341 ln_defbal_id);
2342
2343 FETCH csr_asg_for_gre_only_rb1 INTO l_assignment_id,
2344 l_business_group_id;
2345
2346 CLOSE csr_asg_for_gre_only_rb1;
2347 else
2348 hr_utility.trace('opening gre_only cursor');
2349 OPEN csr_asg_exists_for_gre_only(l_start_date,
2350 p_effective_date,
2351 p_gre_id);
2352
2353 FETCH csr_asg_exists_for_gre_only INTO l_assignment_id,
2354 l_business_group_id;
2355
2356 CLOSE csr_asg_exists_for_gre_only;
2357 end if;
2358 ELSE
2359 OPEN csr_asg_exists_for_gre(p_organization_id,
2360 p_location_id,
2361 p_payroll_id,
2362 p_pay_basis_type,
2363 l_start_date,
2364 p_effective_date,
2365 p_gre_id,
2366 lv_gre_type);
2367 FETCH csr_asg_exists_for_gre INTO l_assignment_id,
2368 l_business_group_id;
2369 CLOSE csr_asg_exists_for_gre;
2370
2371
2372 END IF;
2373
2374 ELSIF p_source_id IS NOT NULL THEN
2375 hr_utility.trace(l_routine_name||': 40');
2376
2377 pay_ca_balance_view_pkg.set_context('SOURCE_ID',
2378 p_source_id);
2379
2380 l_dim_str1 := 'RPT_UNIT_';
2381
2382 /*
2383 * Find one assignment that is included for this Source Id
2384 */
2385 OPEN csr_asg_exists_for_src(p_organization_id,
2386 p_location_id,
2387 p_payroll_id,
2388 p_pay_basis_type,
2389 l_start_date,
2390 p_effective_date,
2391 p_source_id);
2392
2393 FETCH csr_asg_exists_for_src INTO l_assignment_id,
2394 l_business_group_id;
2395 CLOSE csr_asg_exists_for_src;
2396
2397 ELSE
2398 RAISE e_no_gre_or_source_id;
2399 END IF;
2400
2401 /*
2402 * Build the dimension strings: one for the group level balance and
2403 * one for the related assignment level balance.
2404 * e.g. Group level balance - _GRE_MONTH
2405 * Assignment level balance - _ASG_GRE_MONTH
2406 * e.g. with JD Group - _GRE_JD_MONTH
2407 * Assignment - _ASG_JD_GRE_MONTH
2408 */
2409 IF p_jurisdiction IS NULL THEN
2410 hr_utility.trace(l_routine_name||': 50');
2411 l_gl_dimension := l_dim_str1 || p_time_dimension;
2412 l_lb_dimension := 'ASG_' || l_gl_dimension;
2413 ELSE
2414 hr_utility.trace(l_routine_name||': 60');
2415 pay_ca_balance_view_pkg.set_context('JURISDICTION_CODE',
2416 p_jurisdiction);
2417 l_gl_dimension := l_dim_str1 || 'JD_' || p_time_dimension;
2418 l_lb_dimension := 'ASG_JD_'|| l_dim_str1 || p_time_dimension;
2419 END IF;
2420 hr_utility.trace('Group Level Dimension: '||l_gl_dimension);
2421 hr_utility.trace('Latest Balance Dimension: '||l_lb_dimension);
2422
2423 IF l_assignment_id IS NOT NULL THEN
2424 l_gl_defined_balance_id := get_defined_balance(l_balance_name,
2425 l_gl_dimension,
2426 l_business_group_id);
2427 IF l_gl_defined_balance_id IS NULL THEN
2428 RETURN NULL;
2429 END IF;
2430
2431 hr_utility.trace(l_routine_name||': 70');
2432 IF p_time_dimension = 'PYDATE' OR
2433 p_time_dimension = 'PTD' THEN
2434 IF p_time_dimension = 'PTD' OR
2435 (p_organization_id IS NULL AND
2436 p_location_id IS NULL AND
2437 p_payroll_id IS NULL AND
2438 p_pay_basis_type IS NULL) THEN
2439
2440 hr_utility.trace(l_routine_name||': 80');
2441
2442 /*
2443 * All of the balance parameters are contexts so we can just call PYDATE
2444 * This will not use latest balances.
2445 */
2446 l_balance_value :=
2447 pay_ca_balance_view_pkg.get_value (l_assignment_id,
2448 l_gl_defined_balance_id,
2449 p_effective_date);
2450 ELSE
2451 /*
2452 * At least one of the balance parameters is not a context so we
2453 * must sum up all the relevant individual assignment action balance
2454 * values
2455 * We will will use the _ASG_GRE_RUN route since it is faster than
2456 * _ASG_GRE_PYDATE. Note the _ASG_GRE_PYDATE balance only sums values
2457 * on the specified date, not over a date range because of the link to
2458 * pre-payments
2459 * We can't use latest balances in this call because they won't exist
2460 * for any of the balances we are calling.
2461 */
2462 hr_utility.trace(l_routine_name||': 85');
2463
2464 l_balance_value := get_grp_pydate_with_aa
2465 (l_lb_dimension,
2466 l_balance_name,
2467 p_effective_date,
2468 l_start_date,
2469 p_jurisdiction,
2470 p_gre_id,
2471 p_source_id,
2472 p_organization_id,
2473 p_location_id,
2474 p_payroll_id,
2475 p_pay_basis_type,
2476 l_business_group_id);
2477 END IF;
2478 ELSE
2479 /*
2480 * For all non-PYDATE balances
2481 */
2482 hr_utility.trace(l_routine_name||': 90');
2483 l_balance_value := get_grp_non_pydate_with_asg
2484 (l_assignment_id,
2485 p_time_dimension,
2486 l_lb_dimension,
2487 l_gl_defined_balance_id,
2488 l_balance_name,
2489 p_effective_date,
2490 l_start_date,
2491 p_jurisdiction,
2492 p_gre_id,
2493 p_source_id,
2494 p_organization_id,
2495 p_location_id,
2496 p_payroll_id,
2497 p_pay_basis_type,
2498 l_business_group_id);
2499 END IF;
2500 ELSE
2501 hr_utility.trace(l_routine_name||': 100');
2502 hr_utility.trace('No Assignments to process');
2503 l_balance_value := 0;
2504 -- RAISE e_no_assignments;
2505 END IF;
2506
2507 hr_utility.trace('Ending routine: '||l_routine_name);
2508 RETURN l_balance_value;
2509
2510 EXCEPTION
2511 WHEN e_invalid_time_dimension THEN
2512 pay_us_balance_view_pkg.debug_err('The time dimension is invalid');
2513 WHEN e_no_assignments THEN
2514 pay_us_balance_view_pkg.debug_err('No Assignments to process');
2515 WHEN e_no_gre_or_source_id THEN
2516 pay_us_balance_view_pkg.debug_err('Either a GRE or a Reporting Unit '||
2517 '(Source Id) must be passed to this routine');
2518 WHEN e_no_gre_specified THEN
2519 pay_us_balance_view_pkg.debug_err('The GRE parameter must be specified');
2520 WHEN e_no_source_id_specified THEN
2521 pay_us_balance_view_pkg.debug_err('The Source Id parameter must be specified');
2522 WHEN e_jurisdiction_must_be_null THEN
2523 pay_us_balance_view_pkg.debug_err('The Jurisdiction parameter can not be entered for Reporting Unit balances');
2524 WHEN e_invalid_dim_date_comb THEN
2525 pay_us_balance_view_pkg.debug_err('The Start Date parameter must be entered only when the dimension is PYDATE');
2526 WHEN e_no_rpt_unit_for_pydate THEN
2527 pay_us_balance_view_pkg.debug_err('The Reporting Unit dimension can not be used for pay date range calculations');
2528 WHEN e_no_start_date_for_pydate THEN
2529 pay_us_balance_view_pkg.debug_err('The Start Date parameter MUST be entered when the dimension is PYDATE');
2530
2531 END ca_group_level_balance;
2532
2533
2534 -------------------------------------------------------------------------------
2535 -- Name: ca_group_level_balance_rb
2536 --
2537 -- Parameters:
2538 -- p_balance_name - Mandatory
2539 -- p_time_dimension - Mandatory (MONTH, QTD, YTD, PYDATE, PTD)
2540 -- p_effective_date - Mandatory
2541 -- p_start_date - If this date is entered a PYDATE grouping is performed
2542 -- i.e. all assignments between p_start_date and
2543 -- p_effective_date are summed.
2544 -- These is always grouped within GRE - not Source Id.
2545 -- These field is mandatory if p_time_dimension is PYDATE.
2546 -- p_source_id - Either p_source_id or p_gre_id must be entered.
2547 -- p_time_dimension must be one of MONTH, QTD or YTD.
2548 -- p_gre_id - Either p_source_id or p_gre_id must be entered.
2549 -- p_time_dimension must be one of MONTH, QTD or YTD.
2550 -- p_jurisdiction - If the jurisdiction code is enter the dimension will
2551 -- be within jurisdiction.
2552 -- p_organization_id - If the organization id is specified then this
2553 -- routine will only get assignments for that
2554 -- organization.
2555 -- p_location_id - If the location id is specified then this routine
2556 -- will only get assignments for that location.
2557 -- p_payroll_id - If the payroll id is specified then this routine
2558 -- will only get assignments for that payroll.
2559 -- p_pay_basis_type - HOURLY - only assignments with hourly pay bases
2560 -- will be included
2561 -- SALARIED - only assignments with non-hourly pay
2562 -- bases will be included
2563 -- OTHER - only assignments with no pay bases
2564 -- will be included
2565 --
2566 -- Return: NUMBER - Group level balance total
2567 --
2568 -- Description: This is the main calling routine for calculating Canadian
2569 -- Group Level Balances. This routine will initially be used for
2570 -- the following reports:
2571 -- Provincial Medical
2572 -- Workers Compensation
2573 -- Tax Deduction
2574 -- Business Payroll Survey
2575 -- Gross to Net
2576
2577 -- This function has been modified with
2578 -- pay_run_balances validation to check for valid assignments
2579 -- or assignment_actions. If p_flag is 'Y' this routine will
2580 -- use EBRA validation to avoid unnecessary balance calls for
2581 -- assignment_actions or assignments, if p_flas is 'N' it uses
2582 -- old routine ca_group_level_balance .
2583 -------------------------------------------------------------------------------
2584 FUNCTION ca_group_level_balance_rb (p_balance_name VARCHAR2,
2585 p_time_dimension VARCHAR2,
2586 p_effective_date DATE,
2587 p_start_date DATE,
2588 p_source_id NUMBER,
2589 p_gre_id NUMBER,
2590 p_jurisdiction VARCHAR2,
2591 p_organization_id NUMBER,
2592 p_location_id NUMBER,
2593 p_payroll_id NUMBER,
2594 p_pay_basis_type VARCHAR2,
2595 p_flag VARCHAR2) RETURN NUMBER IS
2596 /*
2597 * Cursor to find a sample assignment id for the relevant PYDATE.
2598 */
2599
2600 -- New EBRA csr_asg_exists_for_gre Cursor to find a sample assignment id
2601 -- for the relevant PYDATE.
2602
2603 CURSOR csr_asg_exists_for_gre_rb (p_org_id NUMBER,
2604 p_loc_id NUMBER,
2605 p_pay_id NUMBER,
2606 p_basis_type VARCHAR2,
2607 p_start_date DATE,
2608 p_end_date DATE,
2609 p_gre NUMBER,
2610 p_gre_type VARCHAR2,
2611 cp_def_bal_id NUMBER) IS
2612 SELECT asg.assignment_id,
2613 asg.business_group_id
2614 FROM per_all_assignments_f asg,
2615 pay_run_balances prb
2616 WHERE prb.defined_balance_id = cp_def_bal_id
2617 and prb.effective_date between p_start_date AND p_end_date
2618 and prb.tax_unit_id = p_gre
2619 and prb.assignment_id = asg.assignment_id
2620 and prb.effective_date between asg.effective_start_date
2621 AND asg.effective_end_date
2622 and exists (select 1
2623 from hr_soft_coding_keyflex sck
2624 where asg.soft_coding_keyflex_id = sck.soft_coding_keyflex_id
2625 AND decode(p_gre_type, 'T4A/RL1', sck.segment11,
2626 'T4A/RL2', sck.segment12,
2627 sck.segment1) = TO_CHAR(p_gre))
2628 AND asg.organization_id = NVL(p_org_id, asg.organization_id)
2629 AND asg.location_id = NVL(p_loc_id, asg.location_id)
2630 AND asg.payroll_id = NVL(p_pay_id, asg.payroll_id)
2631 AND ((p_basis_type = 'OTHER'
2632 AND asg.pay_basis_id IS NULL)
2633 OR (p_basis_type = 'HOURLY'
2634 AND asg.pay_basis_id IN (SELECT pyb.pay_basis_id
2635 FROM per_pay_bases pyb
2636 WHERE pyb.pay_basis = 'HOURLY')
2637 )
2638 OR (p_basis_type = 'SALARIED'
2639 AND asg.pay_basis_id IN (SELECT pyb.pay_basis_id
2640 FROM per_pay_bases pyb
2641 WHERE pyb.pay_basis IN
2642 ('ANNUAL','MONTHLY','PERIOD'))
2643 )
2644 OR (p_basis_type IS NULL)
2645 )
2646 AND ROWNUM=1;
2647
2648
2649 /* New EBRA Cursor csr_asg_exists_for_gre_only to get assignment id without the
2650 organization_id, location_id, payroll_id and pay_basis_type parameters and
2651 added this cursor to fix the bug#2391970 */
2652
2653 CURSOR csr_asg_exists_for_gre_only_rb ( p_start_date DATE,
2654 p_end_date DATE,
2655 p_gre NUMBER,
2656 cp_def_bal_id NUMBER) IS
2657 select prb.assignment_id,paf.business_group_id
2658 from pay_run_balances prb,per_all_assignments_f paf
2659 where prb.defined_balance_id = cp_def_bal_id
2660 and prb.tax_unit_id = p_gre
2661 and prb.effective_date between p_start_date AND p_end_date
2662 and prb.assignment_id = paf.assignment_id
2663 and prb.effective_date between paf.effective_start_date AND
2664 paf.effective_end_date
2665 and rownum = 1;
2666
2667
2668 /*
2669 * New EBRA csr_asg_exists_for_src Cursor to find a sample assignment id
2670 * for the relevant Source Id with pay_run_balance validations.
2671 */
2672 CURSOR csr_asg_exists_for_src_rb(p_org_id NUMBER,
2673 p_loc_id NUMBER,
2674 p_pay_id NUMBER,
2675 p_basis_type VARCHAR2,
2676 p_start_date DATE,
2677 p_end_date DATE,
2678 p_src NUMBER,
2679 cp_def_bal_id NUMBER) IS
2680 SELECT prb.assignment_id, asg.business_group_id
2681 FROM pay_run_balances prb,
2682 per_all_assignments_f asg
2683 WHERE prb.defined_balance_id = cp_def_bal_id
2684 and asg.assignment_id = prb.assignment_id
2685 and prb.source_id = TO_CHAR(p_src)
2686 AND asg.organization_id = NVL(p_org_id, asg.organization_id)
2687 AND asg.location_id = NVL(p_loc_id, asg.location_id)
2688 AND asg.payroll_id = NVL(p_pay_id, asg.payroll_id)
2689 AND ((p_basis_type = 'OTHER'
2690 AND asg.pay_basis_id IS NULL)
2691 OR (p_basis_type = 'HOURLY'
2692 AND asg.pay_basis_id IN (SELECT pyb.pay_basis_id
2693 FROM per_pay_bases pyb
2694 WHERE pyb.pay_basis = 'HOURLY'))
2695 OR (p_basis_type = 'SALARIED'
2696 AND asg.pay_basis_id IN (SELECT pyb.pay_basis_id
2697 FROM per_pay_bases pyb
2698 WHERE pyb.pay_basis IN
2699 ('ANNUAL','MONTHLY','PERIOD')))
2700 OR (p_basis_type IS NULL))
2701 AND prb.effective_date BETWEEN p_start_date AND p_end_date
2702 AND prb.effective_date BETWEEN asg.effective_start_date
2703 AND asg.effective_end_date
2704 AND ROWNUM = 1;
2705
2706 /* new cursor to get business_group_id to fix bug#3637426 */
2707 cursor c_get_bg_id(cp_gre_id number) is
2708 select business_group_id
2709 from hr_all_organization_units
2710 where organization_id = cp_gre_id;
2711
2712 l_routine_name VARCHAR2(64) := 'pay_ca_group_level_bal_pkg.ca_group_level_balance_rb';
2713
2714 l_dim_str1 VARCHAR2(60);
2715 l_gl_dimension VARCHAR2(60);
2716 l_lb_dimension VARCHAR2(60);
2717 l_assignment_id NUMBER(15);
2718 l_defined_balance_id NUMBER(15);
2719 l_balance_value NUMBER(38,10);
2720 l_gl_defined_balance_id NUMBER(15);
2721 l_balance_name VARCHAR(60);
2722 l_start_date DATE;
2723 l_date_context VARCHAR2(30);
2724 l_business_group_id NUMBER;
2725 lv_gre_type VARCHAR2(60);
2726 ln_newbg_id number;
2727 ln_bal_type_id number;
2728 ln_new_defbal_id number;
2729
2730 e_invalid_time_dimension EXCEPTION;
2731 e_no_gre_or_source_id EXCEPTION;
2732 e_no_assignments EXCEPTION;
2733 e_no_gre_specified EXCEPTION;
2734 e_no_period_specified EXCEPTION;
2735 e_no_source_id_specified EXCEPTION;
2736 e_jurisdiction_must_be_null EXCEPTION;
2737 e_invalid_dim_date_comb EXCEPTION;
2738 e_no_rpt_unit_for_pydate EXCEPTION;
2739 e_no_start_date_for_pydate EXCEPTION;
2740
2741
2742 BEGIN
2743
2744 hr_utility.trace('Starting routine: '||l_routine_name);
2745 hr_utility.trace('P_Flag for EBRA: '||p_flag);
2746 hr_utility.trace('p_time_dimension: '||p_time_dimension);
2747 hr_utility.trace('p_effective_date: '||to_char(p_effective_date));
2748 hr_utility.trace('p_balance_name: '||p_balance_name);
2749 hr_utility.trace('p_jurisdiction: '||p_jurisdiction);
2750 hr_utility.trace('p_gre_id: '||to_char(p_gre_id));
2751 hr_utility.trace('Starting routine: '||l_routine_name);
2752 hr_utility.trace('P_Flag for EBRA: '||p_flag);
2753
2754 -- Checking the EBRA Flag to use Run Balances cursors or
2755 -- Pay_Assignment_Actions cursors
2756 If p_flag = 'N' then
2757 l_balance_value := ca_group_level_balance(p_balance_name
2758 ,p_time_dimension
2759 ,p_effective_date
2760 ,p_start_date
2761 ,p_source_id
2762 ,p_gre_id
2763 ,p_jurisdiction
2764 ,p_organization_id
2765 ,p_location_id
2766 ,p_payroll_id
2767 ,p_pay_basis_type
2768 );
2769 Elsif p_flag = 'Y' then
2770 -- Process rb cursors to find assignments
2771 pay_ca_balance_view_pkg.set_context('DATE_EARNED',
2772 fnd_date.date_to_canonical(p_effective_date));
2773
2774 l_balance_name := p_balance_name;
2775
2776 IF p_time_dimension <> 'PYDATE' AND
2777 p_time_dimension <> 'PTD' AND
2778 p_time_dimension <> 'MONTH' AND
2779 p_time_dimension <> 'QTD' AND
2780 p_time_dimension <> 'YTD' THEN
2781 RAISE e_invalid_time_dimension;
2782 END IF;
2783
2784 IF p_time_dimension <> 'PYDATE' AND
2785 p_start_date IS NULL THEN
2786 hr_utility.trace(l_routine_name||': 10');
2787 IF p_time_dimension = 'MONTH' THEN
2788 l_start_date := TRUNC(p_effective_date,'MONTH');
2789 ELSIF p_time_dimension = 'YTD' THEN
2790 l_start_date := TRUNC(p_effective_date,'Y');
2791 ELSIF p_time_dimension = 'QTD' THEN
2792 l_start_date := TRUNC(p_effective_date,'Q');
2793 END IF;
2794 ELSIF p_time_dimension = 'PYDATE' THEN
2795 hr_utility.trace(l_routine_name||': 20');
2796 IF p_start_date IS NULL THEN
2797 l_date_context := pay_ca_balance_view_pkg.get_context('BALANCE_DATE');
2798 IF l_date_context IS NOT NULL THEN
2799 l_start_date := fnd_date.canonical_to_date(l_date_context);
2800 ELSE
2801 RAISE e_no_start_date_for_pydate;
2802 END IF;
2803 ELSE
2804 l_start_date := p_start_date;
2805 pay_ca_balance_view_pkg.set_context('BALANCE_DATE',
2806 fnd_date.date_to_canonical(p_start_date));
2807 END IF;
2808 ELSIF p_time_dimension = 'PTD' THEN
2809 hr_utility.trace(l_routine_name||': 25');
2810 l_start_date := NULL;
2811 ELSE
2812 RAISE e_invalid_dim_date_comb;
2813 END IF;
2814
2815 IF p_gre_id IS NOT NULL THEN
2816 hr_utility.trace(l_routine_name||': 30');
2817
2818 pay_ca_balance_view_pkg.set_context('TAX_UNIT_ID',
2819 p_gre_id);
2820
2821 l_dim_str1 := 'GRE_';
2822
2823
2824 /* Getting GRE Type of tax unit for Multi GRE functionality
2825 Based on the gre type, the segment will be used in where clause.
2826
2827 T4/RL1 - Segment1
2828 T4A/RL1 - Segment11
2829 T4A/RL2 - Segment12
2830 */
2831
2832 begin
2833 select org_information5
2834 into lv_gre_type
2835 from hr_organization_information hoi
2836 where hoi.org_information_context = 'Canada Employer Identification'
2837 and hoi.organization_id = p_gre_id;
2838
2839 hr_utility.trace('lv_gre_type: '||lv_gre_type);
2840 exception
2841 when others then
2842 null;
2843
2844 end;
2845
2846 /* added code to fix bug#3637426 */
2847 open c_get_bg_id(p_gre_id);
2848 fetch c_get_bg_id into ln_newbg_id;
2849 hr_utility.trace('ln_newbg_id: '||to_char(ln_newbg_id));
2850 close c_get_bg_id;
2851
2852 IF l_balance_name is not null THEN
2853 ln_new_defbal_id := get_defined_balance(l_balance_name,
2854 'ASG_GRE_RUN',
2855 ln_newbg_id);
2856 hr_utility.trace('ln_new_defbal_id: '||to_char(ln_new_defbal_id));
2857 End if;
2858
2859 /* end of code for bug#3637426 */
2860
2861
2862 /*
2863 * Find one assignment that is included in this GRE
2864 */
2865
2866 /* Added this condition to fix the bug#2391970 */
2867 IF p_organization_id is NULL and p_location_id is NULL
2868 and p_payroll_id is NULL and p_pay_basis_type IS null THEN
2869
2870 /* Added this defined_balance_id not null condition to improve
2871 performance of csr_asg_exists_for_gre_only_rb */
2872 if ln_new_defbal_id is not null then
2873 OPEN csr_asg_exists_for_gre_only_rb(l_start_date,
2874 p_effective_date,
2875 p_gre_id,
2876 ln_new_defbal_id);
2877
2878 FETCH csr_asg_exists_for_gre_only_rb INTO l_assignment_id,
2879 l_business_group_id;
2880
2881 hr_utility.trace('ran csr_asg_exists_for_gre_only_rb: ');
2882 hr_utility.trace('l_assignment_id: '||to_char(l_assignment_id));
2883 CLOSE csr_asg_exists_for_gre_only_rb;
2884 end if;
2885
2886 ELSE
2887 /* Added this defined_balance_id not null condition to improve
2888 performance of csr_asg_exists_for_gre_rb */
2889 if ln_new_defbal_id is not null then
2890 OPEN csr_asg_exists_for_gre_rb(p_organization_id,
2891 p_location_id,
2892 p_payroll_id,
2893 p_pay_basis_type,
2894 l_start_date,
2895 p_effective_date,
2896 p_gre_id,
2897 lv_gre_type,
2898 ln_new_defbal_id);
2899 FETCH csr_asg_exists_for_gre_rb INTO l_assignment_id,
2900 l_business_group_id;
2901 hr_utility.trace('ran csr_asg_exists_for_gre_rb: ');
2902 hr_utility.trace('l_assignment_id: '||to_char(l_assignment_id));
2903 CLOSE csr_asg_exists_for_gre_rb;
2904 end if;
2905
2906
2907 END IF;
2908
2909 ELSIF p_source_id IS NOT NULL THEN
2910 hr_utility.trace(l_routine_name||': 40');
2911
2912 pay_ca_balance_view_pkg.set_context('SOURCE_ID',
2913 p_source_id);
2914
2915 l_dim_str1 := 'RPT_UNIT_';
2916
2917 /* Added code to get the def_bal_id so that we can pass the
2918 def_bal_id to get the assignment_id from pay_run_balances.
2919 part of fix for bug#3637426
2920 */
2921 IF l_balance_name is not null THEN
2922 ln_new_defbal_id := get_defined_balance(l_balance_name,
2923 'ASG_RPT_UNIT_RUN',
2924 ln_newbg_id);
2925 hr_utility.trace('ln_new_defbal_id: '||to_char(ln_new_defbal_id));
2926 End if;
2927 /* end of adding code for bug#3637426 */
2928
2929 /*
2930 * Find one assignment that is included for this Source Id
2931 */
2932 /* Added this defined_balance_id not null condition to improve
2933 performance of csr_asg_exists_for_gre_rb */
2934 if ln_new_defbal_id is not null then
2935 OPEN csr_asg_exists_for_src_rb(p_organization_id,
2936 p_location_id,
2937 p_payroll_id,
2938 p_pay_basis_type,
2939 l_start_date,
2940 p_effective_date,
2941 p_source_id,
2942 ln_new_defbal_id);
2943 FETCH csr_asg_exists_for_src_rb INTO l_assignment_id,
2944 l_business_group_id;
2945 hr_utility.trace('ran csr_asg_exists_for_src_rb: ');
2946 hr_utility.trace('l_assignment_id: '||to_char(l_assignment_id));
2947 CLOSE csr_asg_exists_for_src_rb;
2948 end if;
2949
2950 ELSE
2951 RAISE e_no_gre_or_source_id;
2952 END IF;
2953
2954 /*
2955 * Build the dimension strings: one for the group level balance and
2956 * one for the related assignment level balance.
2957 * e.g. Group level balance - _GRE_MONTH
2958 * Assignment level balance - _ASG_GRE_MONTH
2959 * e.g. with JD Group - _GRE_JD_MONTH
2960 * Assignment - _ASG_JD_GRE_MONTH
2961 */
2962
2963 IF p_jurisdiction IS NULL THEN
2964 hr_utility.trace(l_routine_name||': 50');
2965 l_gl_dimension := l_dim_str1 || p_time_dimension;
2966 l_lb_dimension := 'ASG_' || l_gl_dimension;
2967 ELSE
2968 hr_utility.trace(l_routine_name||': 60');
2969 pay_ca_balance_view_pkg.set_context('JURISDICTION_CODE',
2970 p_jurisdiction);
2971 l_gl_dimension := l_dim_str1 || 'JD_' || p_time_dimension;
2972 l_lb_dimension := 'ASG_JD_'|| l_dim_str1 || p_time_dimension;
2973 END IF;
2974 hr_utility.trace('Group Level Dimension: '||l_gl_dimension);
2975 hr_utility.trace('Latest Balance Dimension: '||l_lb_dimension);
2976
2977
2978 IF l_assignment_id IS NOT NULL THEN
2979 l_gl_defined_balance_id := get_defined_balance(l_balance_name,
2980 l_gl_dimension,
2981 ln_newbg_id);
2982 hr_utility.trace('l_gl_defined_balance_id: '||to_char(l_gl_defined_balance_id));
2983
2984 hr_utility.trace('l_assignment_id is not null satisfied ');
2985 IF l_gl_defined_balance_id IS NULL THEN
2986 RETURN NULL;
2987 END IF;
2988
2989 hr_utility.trace(l_routine_name||': 70');
2990
2991 IF p_time_dimension = 'PYDATE' OR
2992 p_time_dimension = 'PTD' THEN
2993 IF p_time_dimension = 'PTD' OR
2994 (p_organization_id IS NULL AND
2995 p_location_id IS NULL AND
2996 p_payroll_id IS NULL AND
2997 p_pay_basis_type IS NULL) THEN
2998
2999 hr_utility.trace(l_routine_name||': 80');
3000
3001 /*
3002 * All of the balance parameters are contexts so we can just call PYDATE
3003 * This will not use latest balances.
3004 */
3005 hr_utility.trace('Calling pay_ca_balance_view_pkg.get_value ');
3006
3007 hr_utility.trace('l_assignment_id :'||to_char(l_assignment_id));
3008 l_balance_value :=
3009 pay_ca_balance_view_pkg.get_value (l_assignment_id,
3010 l_gl_defined_balance_id,
3011 p_effective_date);
3012 hr_utility.trace('l_balance_value :'||to_char(l_balance_value));
3013 ELSE
3014 /*
3015 * At least one of the balance parameters is not a context so we
3016 * must sum up all the relevant individual assignment action balance
3017 * values
3018 * We will will use the _ASG_GRE_RUN route since it is faster than
3019 * _ASG_GRE_PYDATE. Note the _ASG_GRE_PYDATE balance only sums values
3020 * on the specified date, not over a date range because of the link to
3021 * pre-payments
3022 * We can't use latest balances in this call because they won't exist
3023 * for any of the balances we are calling.
3024 */
3025 hr_utility.trace(l_routine_name||': 85');
3026 hr_utility.trace('Calling get_grp_pydate_with_aa_rb ');
3027
3028 l_balance_value := get_grp_pydate_with_aa_rb
3029 (l_lb_dimension,
3030 l_balance_name,
3031 p_effective_date,
3032 l_start_date,
3033 p_jurisdiction,
3034 p_gre_id,
3035 p_source_id,
3036 p_organization_id,
3037 p_location_id,
3038 p_payroll_id,
3039 p_pay_basis_type,
3040 l_business_group_id);
3041 hr_utility.trace('l_balance_value :'||to_char(l_balance_value));
3042 END IF;
3043 ELSE
3044 /*
3045 * For all non-PYDATE balances
3046 */
3047 hr_utility.trace(l_routine_name||': 90');
3048 hr_utility.trace('Calling get_grp_non_pydate_with_asg_rb ');
3049 l_balance_value := get_grp_non_pydate_with_asg_rb
3050 (l_assignment_id,
3051 p_time_dimension,
3052 l_lb_dimension,
3053 l_gl_defined_balance_id,
3054 l_balance_name,
3055 p_effective_date,
3056 l_start_date,
3057 p_jurisdiction,
3058 p_gre_id,
3059 p_source_id,
3060 p_organization_id,
3061 p_location_id,
3062 p_payroll_id,
3063 p_pay_basis_type,
3064 l_business_group_id);
3065 hr_utility.trace('l_balance_value :'||to_char(l_balance_value));
3066 END IF;
3067 ELSE
3068 hr_utility.trace(l_routine_name||': 100');
3069 hr_utility.trace('No Assignments to process');
3070 l_balance_value := 0;
3071 -- RAISE e_no_assignments;
3072 END IF;
3073
3074 End if; -- for EBRA p_flag check
3075
3076 hr_utility.trace('Ending routine: '||l_routine_name);
3077
3078 RETURN l_balance_value;
3079
3080 EXCEPTION
3081 WHEN e_invalid_time_dimension THEN
3082 hr_utility.trace('The time dimension is invalid');
3083 WHEN e_no_assignments THEN
3084 hr_utility.trace('No Assignments to process');
3085 WHEN e_no_gre_or_source_id THEN
3086 hr_utility.trace('Either a GRE or a Reporting Unit '||
3087 '(Source Id) must be passed to this routine');
3088 WHEN e_no_gre_specified THEN
3089 hr_utility.trace('The GRE parameter must be specified');
3090 WHEN e_no_source_id_specified THEN
3091 hr_utility.trace('The Source Id parameter must be specified');
3092 WHEN e_jurisdiction_must_be_null THEN
3093 hr_utility.trace('The Jurisdiction parameter can not be entered for Reporting Unit balances');
3094 WHEN e_invalid_dim_date_comb THEN
3095 hr_utility.trace('The Start Date parameter must be entered only when the dimension is PYDATE');
3096 WHEN e_no_rpt_unit_for_pydate THEN
3097 hr_utility.trace('The Reporting Unit dimension can not be used for pay date range calculations');
3098 WHEN e_no_start_date_for_pydate THEN
3099 hr_utility.trace('The Start Date parameter MUST be entered when the dimension is PYDATE');
3100
3101
3102 END ca_group_level_balance_rb;
3103
3104
3105 END pay_ca_group_level_bal_pkg;
3106
[Home] [Help]