[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.
411: from per_people_f pap,
412: per_assignments_f paaf,
413: pay_payroll_actions ppa,
414: pay_payroll_actions ppa1,
415: pay_assignment_actions paa,
416: hr_organization_units hou,
417: per_periods_of_service pps
418: -- ,per_assignment_status_types past
419: where ppa.payroll_action_id = c_payroll_action_id
463: from per_people_f pap,
464: per_assignments_f paaf,
465: pay_payroll_actions ppa,
466: pay_payroll_actions ppa1,
467: pay_assignment_actions paa,
468: hr_organization_units hou,
469: per_periods_of_service pps,
470: pay_population_ranges ppr
471: where ppa.payroll_action_id = c_payroll_action_id
518: from per_people_f pap,
519: per_assignments_f paaf,
520: pay_payroll_actions ppa,
521: pay_payroll_actions ppa1,
522: pay_assignment_actions paa,
523: hr_organization_units hou,
524: per_periods_of_service pps
525: where ppa.payroll_action_id = c_payroll_action_id
526: and paa.assignment_id = paaf.assignment_id
565: from per_people_f pap,
566: per_assignments_f paaf,
567: pay_payroll_actions ppa,
568: pay_payroll_actions ppa1,
569: pay_assignment_actions paa,
570: hr_organization_units hou,
571: per_periods_of_service pps,
572: pay_population_ranges ppr
573: where ppa.payroll_action_id = c_payroll_action_id
615: from per_people_f pap,
616: per_assignments_f paaf,
617: pay_payroll_actions ppa,
618: pay_payroll_actions ppa1,
619: pay_assignment_actions paa,
620: per_periods_of_service pps
621: -- ,per_assignment_status_types past
622: where ppa.payroll_action_id = c_payroll_action_id
623: and paa.assignment_id = paaf.assignment_id
665: from per_people_f pap,
666: per_assignments_f paaf,
667: pay_payroll_actions ppa,
668: pay_payroll_actions ppa1,
669: pay_assignment_actions paa,
670: per_periods_of_service pps,
671: pay_population_ranges ppr
672: where ppa.payroll_action_id = c_payroll_action_id
673: and ppr.payroll_action_id = ppa.payroll_action_id
717: from per_people_f pap,
718: per_assignments_f paaf,
719: pay_payroll_actions ppa,
720: pay_payroll_actions ppa1,
721: pay_assignment_actions paa,
722: per_periods_of_service pps
723: where ppa.payroll_action_id = c_payroll_action_id
724: and paa.assignment_id = paaf.assignment_id
725: and pap.person_id between c_start_person and c_end_person
762: from per_people_f pap,
763: per_assignments_f paaf,
764: pay_payroll_actions ppa,
765: pay_payroll_actions ppa1,
766: pay_assignment_actions paa,
767: per_periods_of_service pps,
768: pay_population_ranges ppr
769: where ppa.payroll_action_id = c_payroll_action_id
770: and ppr.payroll_action_id = ppa.payroll_action_id
809: from per_people_f pap,
810: per_assignments_f paaf,
811: pay_payroll_actions ppa,
812: pay_payroll_actions ppa1,
813: pay_assignment_actions paa,
814: per_periods_of_service pps
815: -- per_assignment_status_types past
816: where ppa.payroll_action_id = c_payroll_action_id
817: and paa.assignment_id = paaf.assignment_id
863: pay_population_ranges ppr,
864: per_people_f pap,
865: per_assignments_f paaf,
866: per_periods_of_service pps,
867: pay_assignment_actions paa,
868: pay_payroll_actions ppa1
869: where ppa.payroll_action_id = c_payroll_action_id
870: and ppr.payroll_action_id = ppa.payroll_action_id
871: and ppr.chunk_number = c_chunk
914: from per_people_f pap,
915: per_assignments_f paaf,
916: pay_payroll_actions ppa,
917: pay_payroll_actions ppa1,
918: pay_assignment_actions paa,
919: per_periods_of_service pps
920: where ppa.payroll_action_id = c_payroll_action_id
921: and paa.assignment_id = paaf.assignment_id
922: and pap.person_id between c_start_person and c_end_person
960: from per_people_f pap,
961: per_assignments_f paaf,
962: pay_payroll_actions ppa,
963: pay_payroll_actions ppa1,
964: pay_assignment_actions paa,
965: per_periods_of_service pps,
966: pay_population_ranges ppr
967: where ppa.payroll_action_id = c_payroll_action_id
968: and ppr.payroll_action_id = ppa.payroll_action_id
1008: from per_people_f pap,
1009: per_assignments_f paaf,
1010: pay_payroll_actions ppa,
1011: pay_payroll_actions ppa1,
1012: pay_assignment_actions paa,
1013: per_periods_of_service pps
1014: -- ,per_assignment_status_types past
1015: where ppa.payroll_action_id = c_payroll_action_id
1016: and paa.assignment_id = paaf.assignment_id
1057: from per_people_f pap,
1058: per_assignments_f paaf,
1059: pay_payroll_actions ppa,
1060: pay_payroll_actions ppa1,
1061: pay_assignment_actions paa,
1062: per_periods_of_service pps,
1063: pay_population_ranges ppr
1064: where ppa.payroll_action_id = c_payroll_action_id
1065: and ppr.payroll_action_id = ppa.payroll_action_id
1108: from per_people_f pap,
1109: per_assignments_f paaf,
1110: pay_payroll_actions ppa,
1111: pay_payroll_actions ppa1,
1112: pay_assignment_actions paa,
1113: per_periods_of_service pps
1114: where ppa.payroll_action_id = c_payroll_action_id
1115: and paa.assignment_id = paaf.assignment_id
1116: and pap.person_id between c_start_person and c_end_person
1152: from per_people_f pap,
1153: per_assignments_f paaf,
1154: pay_payroll_actions ppa,
1155: pay_payroll_actions ppa1,
1156: pay_assignment_actions paa,
1157: per_periods_of_service pps,
1158: pay_population_ranges ppr
1159: where ppa.payroll_action_id = c_payroll_action_id
1160: and ppr.payroll_action_id = ppa.payroll_action_id
1198: from per_people_f pap,
1199: per_assignments_f paaf,
1200: pay_payroll_actions ppa,
1201: pay_payroll_actions ppa1,
1202: pay_assignment_actions paa,
1203: per_periods_of_service pps
1204: -- ,per_assignment_status_types past
1205: where ppa.payroll_action_id = c_payroll_action_id
1206: and paa.assignment_id = paaf.assignment_id
1245: from per_people_f pap,
1246: per_assignments_f paaf,
1247: pay_payroll_actions ppa,
1248: pay_payroll_actions ppa1,
1249: pay_assignment_actions paa,
1250: per_periods_of_service pps,
1251: pay_population_ranges ppr
1252: where ppa.payroll_action_id = c_payroll_action_id
1253: and ppr.payroll_action_id = ppa.payroll_action_id
1294: from per_people_f pap,
1295: per_assignments_f paaf,
1296: pay_payroll_actions ppa,
1297: pay_payroll_actions ppa1,
1298: pay_assignment_actions paa,
1299: per_periods_of_service pps
1300: where ppa.payroll_action_id = c_payroll_action_id
1301: and paa.assignment_id = paaf.assignment_id
1302: and pap.person_id between c_start_person and c_end_person
1337: from per_people_f pap,
1338: per_assignments_f paaf,
1339: pay_payroll_actions ppa,
1340: pay_payroll_actions ppa1,
1341: pay_assignment_actions paa,
1342: per_periods_of_service pps,
1343: pay_population_ranges ppr
1344: where ppa.payroll_action_id = c_payroll_action_id
1345: and ppr.payroll_action_id = ppa.payroll_action_id
1403: ppa.payroll_action_id = c_payroll_action_id;
1404:
1405:
1406: cursor csr_next_action_id is
1407: select pay_assignment_actions_s.nextval
1408: from dual;
1409:
1410: l_next_assignment_action_id pay_assignment_actions.assignment_action_id%type;
1411: l_procedure varchar2(200) ;
1406: cursor csr_next_action_id is
1407: select pay_assignment_actions_s.nextval
1408: from dual;
1409:
1410: l_next_assignment_action_id pay_assignment_actions.assignment_action_id%type;
1411: l_procedure varchar2(200) ;
1412: i number;
1413:
1414: l_action_information_id number;
2740:
2741: --------------------------------------------------------------------+
2742:
2743: procedure archive_code
2744: (p_assignment_action_id in pay_assignment_actions.assignment_action_id%type
2745: ,p_effective_date in pay_payroll_actions.effective_date%type
2746: ) is
2747:
2748:
2851: */
2852:
2853:
2854:
2855: 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)
2856: is
2857: 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 */
2858: from
2859: (select distinct -- Bug No: 4045910
2879: paa2.tax_unit_id /*Bug 3935471*/
2880: from pay_element_types_f pet
2881: ,pay_input_values_f piv
2882: ,pay_element_classifications pec
2883: ,pay_assignment_actions paa
2884: ,pay_assignment_actions paa2 /*Bug 3935471*/
2885: ,pay_payroll_actions ppa
2886: ,per_assignments_f paaf
2887: ,pay_run_results prr
2880: from pay_element_types_f pet
2881: ,pay_input_values_f piv
2882: ,pay_element_classifications pec
2883: ,pay_assignment_actions paa
2884: ,pay_assignment_actions paa2 /*Bug 3935471*/
2885: ,pay_payroll_actions ppa
2886: ,per_assignments_f paaf
2887: ,pay_run_results prr
2888: ,pay_run_result_values prrv
2930: c_tax_unit_id number)
2931: is
2932: select to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16))
2933: ,max(paa.action_sequence)
2934: from pay_assignment_actions paa
2935: , pay_payroll_actions ppa
2936: , per_assignments_f paf
2937: where paa.assignment_id = paf.assignment_id
2938: and paf.assignment_id = c_assignment_id
2950: c_tax_unit_id number)
2951: is
2952: select to_number(substr(max(lpad(paa.action_sequence,15,'0')||paa.assignment_action_id),16))
2953: ,max(paa.action_sequence)
2954: from pay_assignment_actions paa
2955: , pay_payroll_actions ppa
2956: , per_assignments_f paf
2957: where paa.assignment_id = paf.assignment_id
2958: and paf.assignment_id = c_assignment_id
3569: */
3570: -------------------------------------------------------------------------------------------
3571: FUNCTION get_element_payment_hours
3572: (
3573: p_assignment_action_id IN pay_assignment_actions.assignment_action_id%TYPE,
3574: p_element_type_id IN pay_element_entries_f.element_entry_id%TYPE,
3575: p_run_result_id IN pay_run_results.run_result_ID%TYPE,
3576: p_effective_date IN pay_payroll_actions.effective_date%TYPE
3577: )
3651: */
3652: -------------------------------------------------------------------------------------------
3653: FUNCTION get_element_payment_rate
3654: (
3655: p_assignment_action_id IN pay_assignment_actions.assignment_action_id%TYPE,
3656: p_element_type_id IN pay_element_entries_f.element_entry_id%TYPE,
3657: p_run_result_id IN pay_run_results.run_result_ID%TYPE,
3658: p_effective_date IN pay_payroll_actions.effective_date%TYPE
3659: )