[Home] [Help]
44: *** Modified the logic of archiving YTD balances. Two new contexts introduced
45: *** AU_BALANCE_RECON_DETAILS_RUN and AU_BALANCE_RECON_DETAILS_YTD.
46: *** 06-JAN-05 abhkumar 1.24 4099317 Modified assignment CURSOR - added CHECK OF action_status = 'C'
47: *** Modified element details cursor - added CHECK OF action_status = 'C' on
48: *** pay_assignment_actions table
49: *** 13-JAN-05 avenkatk 1.25 4116833 Set the Report Request number of copies to be read from Archive Request.
50: *** 21-JAN-05 abhkumar 1.26 4132525 Modified cursor c_employee_details to archive organization name, payroll name and Legal Employer
51: *** name. Sorting of records in reports to be done on basis of Org. names, Payroll names, Legal Employer names.
52: *** 25-JAN-05 abhkumar 1.27 4142159 Introduced Parameter P_DELETE_ACTIONS.
339: from per_people_f pap,
340: per_assignments_f paaf,
341: pay_payroll_actions ppa,
342: pay_payroll_actions ppa1,
343: pay_assignment_actions paa,
344: hr_organization_units hou,
345: per_periods_of_service pps
346: -- ,per_assignment_status_types past
347: where ppa.payroll_action_id = c_payroll_action_id
392: from per_people_f pap,
393: per_assignments_f paaf,
394: pay_payroll_actions ppa,
395: pay_payroll_actions ppa1,
396: pay_assignment_actions paa,
397: hr_organization_units hou,
398: per_periods_of_service pps
399: where ppa.payroll_action_id = c_payroll_action_id
400: and paa.assignment_id = paaf.assignment_id
439: from per_people_f pap,
440: per_assignments_f paaf,
441: pay_payroll_actions ppa,
442: pay_payroll_actions ppa1,
443: pay_assignment_actions paa,
444: per_periods_of_service pps
445: -- ,per_assignment_status_types past
446: where ppa.payroll_action_id = c_payroll_action_id
447: and paa.assignment_id = paaf.assignment_id
490: from per_people_f pap,
491: per_assignments_f paaf,
492: pay_payroll_actions ppa,
493: pay_payroll_actions ppa1,
494: pay_assignment_actions paa,
495: per_periods_of_service pps
496: where ppa.payroll_action_id = c_payroll_action_id
497: and paa.assignment_id = paaf.assignment_id
498: and pap.person_id between c_start_person and c_end_person
534: from per_people_f pap,
535: per_assignments_f paaf,
536: pay_payroll_actions ppa,
537: pay_payroll_actions ppa1,
538: pay_assignment_actions paa,
539: per_periods_of_service pps
540: -- per_assignment_status_types past
541: where ppa.payroll_action_id = c_payroll_action_id
542: and paa.assignment_id = paaf.assignment_id
584: from per_people_f pap,
585: per_assignments_f paaf,
586: pay_payroll_actions ppa,
587: pay_payroll_actions ppa1,
588: pay_assignment_actions paa,
589: per_periods_of_service pps
590: where ppa.payroll_action_id = c_payroll_action_id
591: and paa.assignment_id = paaf.assignment_id
592: and pap.person_id between c_start_person and c_end_person
629: from per_people_f pap,
630: per_assignments_f paaf,
631: pay_payroll_actions ppa,
632: pay_payroll_actions ppa1,
633: pay_assignment_actions paa,
634: per_periods_of_service pps
635: -- ,per_assignment_status_types past
636: where ppa.payroll_action_id = c_payroll_action_id
637: and paa.assignment_id = paaf.assignment_id
678: from per_people_f pap,
679: per_assignments_f paaf,
680: pay_payroll_actions ppa,
681: pay_payroll_actions ppa1,
682: pay_assignment_actions paa,
683: per_periods_of_service pps
684: where ppa.payroll_action_id = c_payroll_action_id
685: and paa.assignment_id = paaf.assignment_id
686: and pap.person_id between c_start_person and c_end_person
721: from per_people_f pap,
722: per_assignments_f paaf,
723: pay_payroll_actions ppa,
724: pay_payroll_actions ppa1,
725: pay_assignment_actions paa,
726: per_periods_of_service pps
727: -- ,per_assignment_status_types past
728: where ppa.payroll_action_id = c_payroll_action_id
729: and paa.assignment_id = paaf.assignment_id
768: from per_people_f pap,
769: per_assignments_f paaf,
770: pay_payroll_actions ppa,
771: pay_payroll_actions ppa1,
772: pay_assignment_actions paa,
773: per_periods_of_service pps
774: where ppa.payroll_action_id = c_payroll_action_id
775: and paa.assignment_id = paaf.assignment_id
776: and pap.person_id between c_start_person and c_end_person
832: ppa.payroll_action_id = c_payroll_action_id;
833:
834:
835: cursor csr_next_action_id is
836: select pay_assignment_actions_s.nextval
837: from dual;
838:
839: l_next_assignment_action_id pay_assignment_actions.assignment_action_id%type;
840: l_procedure varchar2(200) ;
835: cursor csr_next_action_id is
836: select pay_assignment_actions_s.nextval
837: from dual;
838:
839: l_next_assignment_action_id pay_assignment_actions.assignment_action_id%type;
840: l_procedure varchar2(200) ;
841: i number;
842:
843: l_action_information_id number;
1541:
1542: --------------------------------------------------------------------+
1543:
1544: procedure archive_code
1545: (p_assignment_action_id in pay_assignment_actions.assignment_action_id%type
1546: ,p_effective_date in pay_payroll_actions.effective_date%type
1547: ) is
1548:
1549:
1648:
1649: /*Bug 5603254 - Removed tables piv2 and prrv2 and their joins from cursor , added a call to function pay_au_rec_det_archive.get_element_payment_hours to get the value for hours and rate */
1650:
1651:
1652: cursor c_element_details(c_business_group_id hr_all_organization_units.organization_id%TYPE,c_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE)
1653: is
1654: select element_name,label classification_name,sum(amount) payment,sum(hours) hours,source_action_id master_action_id, tax_unit_id master_tax_unit_id,rate /*Bug 3935471 ,5461557 */
1655: from
1656: (select distinct -- Bug No: 4045910
1675: paa2.tax_unit_id /*Bug 3935471*/
1676: from pay_element_types_f pet
1677: ,pay_input_values_f piv
1678: ,pay_element_classifications pec
1679: ,pay_assignment_actions paa
1680: ,pay_assignment_actions paa2 /*Bug 3935471*/
1681: ,pay_payroll_actions ppa
1682: ,per_assignments_f paaf
1683: ,pay_run_results prr
1676: from pay_element_types_f pet
1677: ,pay_input_values_f piv
1678: ,pay_element_classifications pec
1679: ,pay_assignment_actions paa
1680: ,pay_assignment_actions paa2 /*Bug 3935471*/
1681: ,pay_payroll_actions ppa
1682: ,per_assignments_f paaf
1683: ,pay_run_results prr
1684: ,pay_run_result_values prrv
1726: c_tax_unit_id number)
1727: is
1728: select to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16))
1729: ,max(paa.action_sequence)
1730: from pay_assignment_actions paa
1731: , pay_payroll_actions ppa
1732: , per_assignments_f paf
1733: where paa.assignment_id = paf.assignment_id
1734: and paf.assignment_id = c_assignment_id
1746: c_tax_unit_id number)
1747: is
1748: select to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16))
1749: ,max(paa.action_sequence)
1750: from pay_assignment_actions paa
1751: , pay_payroll_actions ppa
1752: , per_assignments_f paf
1753: where paa.assignment_id = paf.assignment_id
1754: and paf.assignment_id = c_assignment_id
2365: */
2366: -------------------------------------------------------------------------------------------
2367: FUNCTION get_element_payment_hours
2368: (
2369: p_assignment_action_id IN pay_assignment_actions.assignment_action_id%TYPE,
2370: p_element_type_id IN pay_element_entries_f.element_entry_id%TYPE,
2371: p_run_result_id IN pay_run_results.run_result_ID%TYPE,
2372: p_effective_date IN pay_payroll_actions.effective_date%TYPE
2373: )
2447: */
2448: -------------------------------------------------------------------------------------------
2449: FUNCTION get_element_payment_rate
2450: (
2451: p_assignment_action_id IN pay_assignment_actions.assignment_action_id%TYPE,
2452: p_element_type_id IN pay_element_entries_f.element_entry_id%TYPE,
2453: p_run_result_id IN pay_run_results.run_result_ID%TYPE,
2454: p_effective_date IN pay_payroll_actions.effective_date%TYPE
2455: )