[Home] [Help]
225: Hire indicator flag
226: 4-NOV-2005 pganguly 115.79 Fixed bug# 4033041. Commented out
227: archiver code for T4_BOX50.
228: 3-MAR-2006 ssmukher 115.80 Fixed Bug #5041252 .Removed the
229: per_all_assignments_f table check
230: from the select statement in the
231: procedure eoy_archive_data to fetch
232: the CPP/QPP exempt flag
233: from pay_ca_emp_prov_tax_info_f.
637: SELECT ASG.person_id person_id,
638: ASG.assignment_id assignment_id,
639: ASG.effective_end_date effective_end_date
640: FROM
641: per_all_assignments_f ASG
642: WHERE
643: ASG.business_group_id = l_bus_group_id AND
644: asg.assignment_type = 'E' AND
645: ASG.person_id between stperson and endperson AND
666: INDEX(paa PAY_ASSIGNMENT_ACTIONS_N51)
667: INDEX(ppa PAY_PAYROLL_ACTIONS_PK) */
668: DISTINCT ASG.person_id person_id
669: FROM
670: per_all_assignments_f ASG,
671: pay_assignment_actions paa,
672: pay_payroll_actions ppa
673:
674: WHERE ppa.effective_date between l_period_start
693: CURSOR c_eoy_gre_range IS
694: SELECT /*+ leading(ppr,asg,paa) */ --Modified for bug 13615110
695: DISTINCT ASG.person_id person_id
696: FROM
697: per_all_assignments_f ASG,
698: pay_assignment_actions paa,
699: pay_payroll_actions ppa,
700: pay_population_ranges ppr
701: WHERE ppa.effective_date between l_period_start
719: --Original query:
720: /* SELECT DISTINCT
721: ASG.person_id person_id
722: FROM
723: per_all_assignments_f ASG,
724: pay_all_payrolls_f PPY
725: WHERE exists
726: (select /*+ INDEX(paa PAY_ASSIGNMENT_ACTIONS_N51)
727: INDEX(ppa PAY_PAYROLL_ACTIONS_PK) */
752: SELECT ASG.person_id person_id,
753: ASG.assignment_id assignment_id,
754: to_number(SCL.segment1) tax_unit_id,
755: ASG.effective_end_date effective_end_date
756: FROM per_all_assignments_f ASG,
757: hr_soft_coding_keyflex SCL,
758: pay_all_payrolls_f PPY
759: WHERE ASG.business_group_id + 0 = l_bus_group_id
760: AND ASG.person_id between stperson and endperson
768: /* Get the primary assignment for the given person_id */
769:
770: CURSOR c_get_asg_id (p_person_id number) IS
771: SELECT assignment_id
772: from per_all_assignments_f paf
773: where person_id = p_person_id
774: and primary_flag = 'Y'
775: and paf.effective_start_date <= l_period_end
776: and paf.effective_end_date >= l_period_start
784: INDEX (asg PER_ASSIGNMENTS_F_N12)
785: INDEX(paa PAY_ASSIGNMENT_ACTIONS_N51)
786: INDEX(ppa PAY_PAYROLL_ACTIONS_PK) */
787: paa.assignment_action_id
788: from per_all_assignments_f paf,
789: pay_assignment_actions paa,
790: pay_payroll_actions ppa,
791: pay_action_classifications pac
792: where paf.person_id = p_person_id
811:
812: /* Original Query:
813: select paa.assignment_action_id
814: from pay_assignment_actions paa,
815: per_all_assignments_f paf,
816: pay_payroll_actions ppa,
817: pay_action_classifications pac
818: where paf.person_id = p_person_id
819: and paa.assignment_id = paf.assignment_id
2292: PEOPLE.middle_names,
2293: ASSIGN.organization_id,
2294: ASSIGN.location_id
2295: FROM
2296: per_all_assignments_f ASSIGN,
2297: per_all_people_f PEOPLE
2298: WHERE ASSIGN.assignment_id = p_asgid
2299: and l_date_earned BETWEEN ASSIGN.effective_start_date
2300: AND ASSIGN.effective_end_date
2382: cursor c_balance_feed_info (p_balance_name varchar2,p_each_asgid number) is
2383: select nvl(pet.element_information20,'NOT FOUND'),pbtl.balance_name,rv.result_value
2384: from pay_assignment_actions pa,
2385: pay_payroll_actions ppa,
2386: per_all_assignments_f paf,
2387: pay_element_types_f pet,
2388: pay_run_results r,
2389: pay_run_result_values rv,
2390: pay_input_values_f piv,
2408: -- CURSOR c_diff_assignments is used to retrive all assignments for
2409: -- perticular person in the same GRE
2410: cursor c_diff_assignments(p_person_id number) is
2411: select distinct(paf.assignment_id)
2412: from per_all_assignments_f paf
2413: ,per_all_people_f ppf
2414: ,hr_soft_coding_keyflex_kfv hsc
2415: ,hr_locations_all hl
2416: where ppf.person_id = p_person_id
2465: p_jurisdiction varchar2) IS
2466: select /*+ Ordered */
2467: paa.assignment_action_id,
2468: ppa.date_earned
2469: from per_all_assignments_f paf,
2470: pay_assignment_actions paa,
2471: pay_payroll_actions ppa,
2472: pay_action_classifications pac,
2473: pay_action_contexts pac1,
2502: cp_jurisdiction varchar2) is
2503: select paa.assignment_action_id,
2504: ppa.date_earned
2505: from pay_assignment_actions paa,
2506: per_all_assignments_f paf,
2507: per_all_people_f ppf,
2508: pay_payroll_actions ppa,
2509: pay_action_classifications pac,
2510: pay_action_contexts pac1,
2693: INTO
2694: l_aaid
2695: FROM
2696: pay_assignment_actions paa1,
2697: per_all_assignments_f paf2
2698: WHERE
2699: paa1.assignment_id = paf2.assignment_id
2700: and paa1.tax_unit_id = l_tax_unit_id
2701: and (paa1.action_sequence , paf2.person_id) =
2702: (SELECT MAX(paa.action_sequence), paf.person_id
2703: FROM pay_action_classifications pac,
2704: pay_payroll_actions ppa,
2705: pay_assignment_actions paa,
2706: per_all_assignments_f paf1,
2707: per_all_assignments_f paf
2708: WHERE paf.assignment_id = l_asgid
2709: AND paf1.person_id = paf.person_id
2710: AND paa.tax_unit_id = l_tax_unit_id
2703: FROM pay_action_classifications pac,
2704: pay_payroll_actions ppa,
2705: pay_assignment_actions paa,
2706: per_all_assignments_f paf1,
2707: per_all_assignments_f paf
2708: WHERE paf.assignment_id = l_asgid
2709: AND paf1.person_id = paf.person_id
2710: AND paa.tax_unit_id = l_tax_unit_id
2711: AND paa.assignment_id = paf1.assignment_id
3629: end if;
3630:
3631: -- Bug 13501503
3632: select distinct(person_id) into l_each_person_id
3633: from per_all_assignments_f where assignment_id = l_asgid;
3634: hr_utility.trace('l_each_person_id ' || l_each_person_id);
3635:
3636: open c_diff_assignments(l_each_person_id);
3637: fetch c_diff_assignments into l_each_asgid;
3647: BEGIN
3648: hr_utility.trace('l_each_asgid ' || l_each_asgid);
3649: BEGIN
3650:
3651: /* Removed the per_all_assignments_f join the select stmt for bug fix 5041252 */
3652: SELECT decode(target.CPP_QPP_EXEMPT_FLAG,'Y','X',NULL),
3653: decode(target.EI_EXEMPT_FLAG,'Y','X',NULL)
3654: INTO l_cpp_exempt_flag,
3655: l_ei_exempt_flag
3910: hr_utility.trace('l_jurisdiction '||l_jurisdiction);
3911:
3912: -- adding this logic to resolving bug 13797428
3913: select distinct(person_id) into l_each_person_id
3914: from per_all_assignments_f where assignment_id = l_asgid;
3915: hr_utility.trace('l_each_person_id ' || l_each_person_id);
3916:
3917: open c_diff_assignments(l_each_person_id);
3918: fetch c_diff_assignments into l_each_asgid;
4420: distinct asg.person_id
4421: from pay_all_payrolls_f ppy,
4422: pay_payroll_actions ppa,
4423: pay_assignment_actions paa,
4424: per_all_assignments_f asg,
4425: pay_payroll_actions ppa1
4426: where ppa1.payroll_action_id = :payroll_action_id
4427: and ppa.effective_date between
4428: fnd_date.canonical_to_date('''||