[Home] [Help]
52: AND p_reporting_date BETWEEN t.effective_start_date AND t.effective_end_date
53: AND p_reporting_date BETWEEN l.effective_start_date AND l.effective_end_date
54: GROUP BY e.assignment_id;
55: --
56: l_assignment_id per_all_assignments_f.assignment_id%TYPE;
57: --
58: BEGIN
59: --
60: OPEN csr_get_ss_details;
79: CURSOR csr_get_disability_degree IS
80: SELECT pdf.degree
81: FROM per_all_people_f pap
82: ,per_disabilities_f pdf
83: ,per_all_assignments_f paa
84: WHERE pap.person_id = pdf.person_id
85: AND pap.person_id = paa.person_id
86: AND paa.assignment_id = p_assignment_id
87: AND p_reporting_date BETWEEN pdf.effective_start_date
284: AND hloc.location_id (+) = hou.location_id
285: AND hoi.organization_id = nvl(c_organization_id,hoi.organization_id)
286: AND EXISTS (SELECT asg_run.assignment_id
287: FROM per_assignment_extra_info asg_extra
288: ,per_all_assignments_f asg_run
289: WHERE asg_extra.aei_information_category = 'ES_SS_REP'
290: AND asg_extra.INFORMATION_TYPE = 'ES_SS_REP'
291: AND asg_extra.aei_information5 = 'Y'
292: AND asg_run.assignment_id = asg_extra.assignment_id
405: ,paa.assignment_id assignment_id
406: FROM per_all_people_f pap
407: ,(SELECT DISTINCT asg_run.assignment_id assignment_id, asg_run.person_id person_id
408: FROM per_assignment_extra_info asg_extra
409: ,per_all_assignments_f asg_run
410: WHERE asg_extra.aei_information_category = 'ES_SS_REP'
411: AND asg_extra.aei_information5 = 'Y'
412: AND asg_run.business_group_id = g_business_group_id
413: AND asg_run.assignment_id = asg_extra.assignment_id
425: ,p_reporting_date DATE
426: ,p_payroll_id NUMBER) IS
427: SELECT paf.assignment_id assignment_id,
428: leg.organization_id legal_employer
429: FROM per_all_assignments_f paf
430: ,hr_soft_coding_keyflex sck
431: ,hr_organization_information wcr
432: ,hr_organization_information leg
433: WHERE paf.effective_start_date = (SELECT max (paf1.effective_start_date)
430: ,hr_soft_coding_keyflex sck
431: ,hr_organization_information wcr
432: ,hr_organization_information leg
433: WHERE paf.effective_start_date = (SELECT max (paf1.effective_start_date)
434: FROM per_all_assignments_f paf1
435: WHERE paf.assignment_id = paf1.assignment_id
436: AND paf1.effective_start_date <= p_reporting_date)
437: AND sck.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
438: AND sck.segment2 = wcr.org_information1
451: SELECT pcf.effective_start_date
452: ,pcf.ctr_information6 replaced_person_id
453: ,pcf.ctr_information7 replacement_reason_code
454: FROM per_contracts_f pcf
455: ,per_all_assignments_f paf
456: WHERE paf.assignment_id = p_assignment_id
457: AND paf.person_id = pcf.person_id
458: AND p_reporting_date BETWEEN paf.effective_start_date AND paf.effective_end_date
459: AND p_reporting_date BETWEEN pcf.effective_start_date AND pcf.effective_end_date;
464: ,pay_input_values_f piv
465: ,pay_element_types_f pet
466: ,pay_element_entries_f pee
467: ,pay_element_links_f pel
468: ,per_all_assignments_f paf
469: WHERE paf.person_id = c_person_id
470: AND pee.assignment_id = paf.assignment_id
471: AND pev.element_entry_id = pee.element_entry_id
472: AND piv.input_value_id = pev.input_value_id
545: --
546: --Assignment number to display in the Audit Report
547: CURSOR csr_get_asg_details(c_assignment_id NUMBER) IS
548: SELECT paf.assignment_number asg_no
549: FROM per_all_assignments_f paf
550: WHERE paf.assignment_id = c_assignment_id
551: ORDER BY paf.effective_start_date DESC;
552: --
553: --Fetching the payroll id for the assignment_set
568: ,c_reporting_date DATE) IS
569: SELECT region_2
570: FROM per_addresses pas
571: ,per_all_people_f pap
572: ,per_all_assignments_f paa
573: WHERE paa.person_id = pap.person_id
574: AND pas.person_id = pap.person_id
575: AND paa.assignment_id = c_assignment_id
576: AND pas.business_group_id = c_business_group_id
579: AND pap.effective_end_date
580: AND c_reporting_date BETWEEN paa.effective_start_date
581: AND paa.effective_end_date;
582: --
583: l_assignment_number per_all_assignments_f.assignment_number%TYPE;
584: l_actid pay_assignment_actions.assignment_action_id%TYPE;
585: l_unused_number NUMBER;
586: l_action_info_id pay_action_information.action_information_id%TYPE;
587: l_ovn pay_action_information.object_version_number%TYPE;
588: l_contract_start_date DATE;
589: l_replace_person_id per_all_people_f.person_id%TYPE;
590: l_replacement_reason_code per_contracts_f.ctr_information7%TYPE;
591: l_ss_identifier pay_element_entry_values_f.screen_entry_value%TYPE;
592: l_assignment_id per_all_assignments_f.assignment_id%TYPE;
593: l_relationship_type pay_element_entry_values_f.screen_entry_value%TYPE;
594: l_retirement_age_reduction pay_element_entry_values_f.screen_entry_value%TYPE;
595: l_assignment_set_id hr_assignment_sets.assignment_set_id%TYPE;
596: l_payroll_id hr_assignment_sets.payroll_id%TYPE;
754: ,nvl(paa.employment_category, 'X') employment_category
755: ,paa.soft_coding_keyflex_id
756: ,paa.employee_category employee_category
757: ,paa.collective_agreement_id
758: FROM per_all_assignments_f paa
759: ,per_all_people_f pap
760: WHERE paa.assignment_id = c_assignment_id
761: AND paa.person_id = pap.person_id
762: AND paa.effective_start_date = fnd_date.canonical_to_date(c_effective_start_date)
829: l_disability_degree NUMBER;
830: l_date_of_birth per_all_people_f.date_of_birth%TYPE;
831: l_sex per_all_people_f.sex%TYPE;
832: l_system_status per_assignment_status_types.per_system_status%TYPE;
833: l_assignment_id per_all_assignments_f.assignment_id%TYPE;
834: l_contract_key pay_element_entry_values_f.screen_entry_value%TYPE;
835: --
836: BEGIN
837: --
975: sql_str := 'select paa.assignment_status_type_id asg_value
976: ,paa.effective_start_date actual_date
977: ,pap.date_of_birth date_of_birth
978: ,pap.sex sex
979: from per_all_assignments_f paa
980: ,per_all_people_f pap
981: ,per_assignment_status_types pas
982: where paa.assignment_id = '||p_assignment_id||'
983: and paa.person_id = pap.person_id
1007: from pay_element_entry_values_f pev
1008: ,pay_input_values_f piv
1009: ,pay_element_types_f pet
1010: ,pay_element_entries_f pee
1011: ,per_all_assignments_f paa
1012: ,per_all_people_f pap
1013: where pev.element_entry_id = pee.element_entry_id
1014: and paa.person_id = pap.person_id
1015: and paa.assignment_id = pee.assignment_id
1046: sql_str := 'select nvl(sck.segment5, ''X'') asg_value
1047: ,paa.effective_start_date actual_date
1048: ,pap.date_of_birth date_of_birth
1049: ,pap.sex sex
1050: from per_all_assignments_f paa
1051: ,per_all_people_f pap
1052: ,hr_soft_coding_keyflex sck
1053: where paa.assignment_id = '||p_assignment_id||'
1054: and paa.person_id = pap.person_id
1073: sql_str := 'select paa.assignment_status_type_id asg_value
1074: ,pps.actual_termination_date actual_date
1075: ,pap.date_of_birth date_of_birth
1076: ,pap.sex sex
1077: from per_all_assignments_f paa
1078: ,per_all_people_f pap
1079: ,per_periods_of_service pps
1080: where paa.assignment_id = '||p_assignment_id||'
1081: and paa.person_id = pap.person_id
1106: from pay_element_entry_values_f pev
1107: ,pay_input_values_f piv
1108: ,pay_element_types_f pet
1109: ,pay_element_entries_f pee
1110: ,per_all_assignments_f paa
1111: ,per_all_people_f pap
1112: where pev.element_entry_id = pee.element_entry_id
1113: and paa.person_id = pap.person_id
1114: and paa.assignment_id = pee.assignment_id
1161: --
1162: CURSOR csr_get_asg_values (c_assignment_id NUMBER, c_reporting_date DATE) IS
1163: SELECT paa.employee_category employee_category
1164: ,paa.collective_agreement_id
1165: FROM per_all_assignments_f paa
1166: WHERE paa.assignment_id = c_assignment_id
1167: AND c_reporting_date BETWEEN paa.effective_start_date
1168: AND paa.effective_end_date;
1169: --
1171: ,c_business_group_id NUMBER
1172: ,c_actual_termination_dt DATE ) IS
1173: SELECT leaving_reason
1174: FROM per_periods_of_service pps
1175: ,per_all_assignments_f paa
1176: WHERE paa.period_of_service_id = pps.period_of_service_id
1177: AND paa.assignment_id = c_assignment_id
1178: AND pps.business_group_id = c_business_group_id
1179: AND c_actual_termination_dt BETWEEN paa.effective_start_date
1486: ,pai.action_information1 action_status
1487: ,max(pai.effective_date) current_reporting_date
1488: FROM pay_payroll_actions ppa
1489: ,pay_assignment_actions paa
1490: ,per_all_assignments_f asg_run
1491: ,pay_action_information pai
1492: WHERE ppa.payroll_action_id = c_payroll_action_id
1493: AND paa.payroll_action_id = ppa.payroll_action_id
1494: AND asg_run.business_group_id = ppa.business_group_id
1560:
1561: l_unused_number := 0;
1562: SELECT count(assignment_status_type_id), min(effective_start_date)
1563: INTO l_unused_number, l_effective_start_date
1564: FROM per_all_assignments_f
1565: WHERE assignment_status_type_id <> l_assignment_status_type_id
1566: AND effective_start_date >= qualifying_assignments.current_reporting_date
1567: AND assignment_id = qualifying_assignments.assignment_id;
1568:
1588: l_unused_number := 0;
1589: --
1590: /* SELECT count(employment_category), min(effective_start_date)
1591: INTO l_unused_number, l_effective_start_date
1592: FROM per_all_assignments_f
1593: WHERE nvl(employment_category, 'X') <> nvl(l_employment_category, 'X')
1594: AND effective_start_date >= qualifying_assignments.current_reporting_date
1595: AND assignment_id = qualifying_assignments.assignment_id;*/
1596:
1636:
1637: l_unused_number := 0;
1638: SELECT count(sck.segment5), min(paa.effective_start_date)
1639: INTO l_unused_number, l_effective_start_date
1640: FROM per_all_assignments_f paa
1641: ,hr_soft_coding_keyflex sck
1642: WHERE nvl(sck.segment5,'X') <> nvl(l_contribution_group,'X')
1643: AND paa.soft_coding_keyflex_id = sck.soft_coding_keyflex_id
1644: AND paa.effective_start_date >= qualifying_assignments.current_reporting_date
1705: ELSIF qualifying_assignments.action_status = 'MA' THEN
1706: l_unused_number := 0;
1707: SELECT count(assignment_status_type_id), min(effective_start_date)
1708: INTO l_unused_number, l_effective_start_date
1709: FROM per_all_assignments_f
1710: WHERE assignment_status_type_id <> l_assignment_status_type_id
1711: AND effective_start_date >= qualifying_assignments.current_reporting_date
1712: AND assignment_id = qualifying_assignments.assignment_id;
1713: IF l_unused_number = 0 THEN
1744: --
1745: l_unused_number := 0;
1746: /*SELECT count(employment_category), min(effective_start_date)
1747: INTO l_unused_number, l_effective_start_date
1748: FROM per_all_assignments_f
1749: WHERE nvl(employment_category, 'X') <> nvl(l_employment_category, 'X')
1750: AND effective_start_date >= qualifying_assignments.current_reporting_date
1751: AND assignment_id = qualifying_assignments.assignment_id;*/
1752: SELECT count(pev.screen_entry_value), min(pev.effective_start_date)
1791: --
1792: l_unused_number := 0;
1793: SELECT count(sck.segment5), min(paa.effective_start_date)
1794: INTO l_unused_number, l_effective_start_date
1795: FROM per_all_assignments_f paa
1796: ,hr_soft_coding_keyflex sck
1797: WHERE nvl(sck.segment5, 'X') <> nvl(l_contribution_group, 'X')
1798: AND paa.soft_coding_keyflex_id = sck.soft_coding_keyflex_id
1799: AND paa.effective_start_date >= qualifying_assignments.current_reporting_date