[Home] [Help]
60: --
61: /*Bug-13344652- Modified the cursor cur_rows_assg*/
62: CURSOR cur_rows_assg IS
63: SELECT count(*)
64: FROM per_all_assignments_f
65: WHERE person_id = p_person_id
66: AND ((assignment_type <> 'A' and assignment_type <> 'O')
67: or (assignment_type in ('A','O') and payroll_id is not null))
68: AND p_effective_date BETWEEN effective_start_date AND effective_end_date ;
70: CURSOR cur_tax_reference IS
71: SELECT COUNT(hsck.segment1) Num, hsck.segment1 tax_district
72: FROM hr_soft_coding_keyflex hsck,
73: pay_all_payrolls_f papf,
74: per_all_assignments_f paaf,
75: per_assignment_status_types past
76: WHERE hsck.soft_coding_keyflex_id = papf.soft_coding_keyflex_id
77: AND papf.payroll_id =paaf.payroll_id
78: AND past.assignment_status_type_id = paaf.assignment_status_type_id
108: pay_element_entries_f ele,
109: pay_element_entry_values_f eev,
110: pay_input_values_f inv,
111: pay_element_links_f lnk, pay_element_types_f elt,
112: per_all_assignments_f paa,
113: pay_all_payrolls_f ppf,
114: hr_soft_coding_keyflex scl
115: where ele.element_entry_id = eev.element_entry_id
116: -- and p_effective_date between ele.effective_start_date and ele.effective_end_date
145: and ppf.effective_start_date <= p_end_date
146: and ppf.effective_end_date >= p_start_date
147:
148: and exists ( SELECT 1
149: FROM per_all_assignments_f paaf,
150: pay_all_payrolls_f papf,
151: hr_soft_coding_keyflex hsck,
152: per_assignment_status_types past
153: WHERE paaf.person_id = p_person_id
180: select count(*)
181: from pay_element_entries_f ele,
182: pay_element_links_f lnk,
183: pay_element_types_f elt,
184: per_all_assignments_f paa
185: where elt.element_name = 'PAYE Details'
186: and p_effective_date between elt.effective_start_date and elt.effective_end_date
187: and elt.element_type_id = lnk.element_type_id
188: and p_effective_date between lnk.effective_start_date and lnk.effective_end_date
429: IS
430: SELECT COUNT(distinct(paa.assignment_id))
431: FROM pay_payroll_actions ppa,
432: pay_assignment_actions paact,
433: per_all_assignments_f paa
434: WHERE paa.person_id = p_person_id
435: AND paa.assignment_id =paact.assignment_id
436: AND ppa.payroll_action_id =paact.payroll_action_id
437: AND ppa.action_type IN ('R','Q')
558: --
559: cursor cur_chk_multiple_asg(c_person_id in number, c_start_date date, c_end_date date) IS
560: select 1
561: from pay_all_payrolls_f papf,
562: per_all_assignments_f paaf,
563: hr_soft_coding_keyflex hsck,
564: per_assignment_status_types past
565: where paaf.person_id = c_person_id
566: and paaf.effective_start_date <= c_end_date-1
571: and papf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
572: and c_end_date between papf.effective_start_date and papf.effective_end_date
573: and exists ( select 1
574: from pay_all_payrolls_f apf,
575: per_all_assignments_f aaf,
576: hr_soft_coding_keyflex sck,
577: per_assignment_status_types ast
578: where aaf.person_id = c_person_id
579: and aaf.assignment_id not in (paaf.assignment_id)
597: select min(ppa.effective_date),
598: max(ppa.effective_date)
599: from pay_assignment_actions paa,
600: pay_payroll_actions ppa,
601: per_all_assignments_f paaf
602: where paa.assignment_id = c_assignment_id
603: and paaf.assignment_id = c_assignment_id
604: and paa.payroll_action_id = ppa.payroll_action_id
605: and ppa.action_type in ('R','Q')
610: order by ppa.effective_date;
611:
612: CURSOR csr_all_assignments(c_person_id in number, c_start_date date, c_end_date date) IS
613: select assignment_id
614: from per_all_assignments_f
615: where person_id = c_person_id
616: and effective_end_date >= c_start_date
617: and effective_start_date <= c_end_date;
618:
645: -- to find whether any terminated assignment exists for a person on effective date of change
646: --
647: cursor cur_term_asg_dtls(c_person_id number, c_effective_date date) IS
648: select 1
649: from per_all_assignments_f a,
650: per_assignment_status_types past
651: where a.assignment_status_type_id = past.assignment_status_type_id
652: and past.per_system_status = 'TERM_ASSIGN'
653: and a.person_id = c_person_id
977: /* Identify the number of assignments for the person_id*/
978: cursor cur_asg_count(c_person_id in number,
979: c_effective_date in date) is
980: select count (distinct assignment_id)
981: from per_all_assignments_f
982: where person_id = c_person_id
983: and c_effective_date between effective_start_date and effective_end_date;
984:
985: /* Identify the TD details of the assignment newly created*/
988: SELECT --COUNT(hsck.segment1) Num,
989: hsck.segment1 tax_district
990: FROM hr_soft_coding_keyflex hsck,
991: pay_all_payrolls_f papf,
992: per_all_assignments_f paaf
993: WHERE paaf.assignment_id= c_assignment_id
994: AND papf.payroll_id =paaf.payroll_id
995: AND hsck.soft_coding_keyflex_id = papf.soft_coding_keyflex_id
996: AND c_effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
1024: pay_input_values_f inv,
1025: pay_element_links_f lnk,
1026: pay_element_types_f elt,
1027: pay_all_payrolls_f papf,
1028: per_all_assignments_f paaf,
1029: hr_soft_coding_keyflex hsck
1030: WHERE paaf.person_id = c_person_id
1031: AND c_effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
1032: AND paaf.payroll_id = papf.payroll_id
1077: pay_element_entry_values_f eev,
1078: pay_input_values_f inv,
1079: pay_element_links_f lnk,
1080: pay_element_types_f elt,
1081: per_all_assignments_f paaf
1082: WHERE ele.element_entry_id = eev.element_entry_id
1083: AND c_effective_date between ele.effective_start_date and ele.effective_end_date
1084: AND eev.input_value_id + 0 = inv.input_value_id
1085: AND c_effective_date between eev.effective_start_date and eev.effective_end_date
1106: IS
1107: SELECT COUNT(distinct(paa.assignment_id))
1108: FROM pay_payroll_actions ppa,
1109: pay_assignment_actions paact,
1110: per_all_assignments_f paa
1111: WHERE paa.person_id = p_person_id
1112: AND paa.assignment_id =paact.assignment_id
1113: AND ppa.payroll_action_id =paact.payroll_action_id
1114: AND ppa.action_type IN ('R','Q')
1525: RETURN fnd_date.date_to_displaydate (l_date_soy);
1526: END get_soy_date;
1527:
1528: PROCEDURE get_extra_info_exists
1529: (p_assignment_id IN per_all_assignments_f.assignment_id%type
1530: ,p_assignment_extra_info_id OUT NOCOPY number
1531: ,p_object_version_number OUT NOCOPY number) IS
1532: l_number number;
1533: BEGIN
1543: p_assignment_extra_info_id := - 1;
1544: END get_extra_info_exists;
1545:
1546: PROCEDURE get_extra_info_rti_flag_exists
1547: (p_assignment_id IN per_all_assignments_f.assignment_id%type
1548: ,p_assignment_extra_info_id OUT NOCOPY number
1549: ,p_object_version_number OUT NOCOPY number) IS
1550: l_number number;
1551: BEGIN
1576: cursor csr_primary_exists is
1577: select paf.assignment_id
1578: from
1579: per_all_people_f pap,
1580: per_all_assignments_f paf
1581: where paf.person_id = pap.person_id
1582: and paf.person_id = p_person_id
1583: and p_effective_date between paf.effective_start_date and paf.effective_end_date
1584: and p_effective_date between pap.effective_start_date and pap.effective_end_date
1587: cursor csr_primary_in_curr_paye is
1588: select paf.assignment_id
1589: from
1590: per_all_people_f pap,
1591: per_all_assignments_f paf,
1592: pay_all_payrolls_f pay,
1593: hr_soft_coding_keyflex hsc
1594: where paf.person_id = pap.person_id
1595: and paf.assignment_id = p_assignment_id
1602:
1603: -- find minimum assignment
1604: cursor csr_oldest_asg is
1605: select min(paf.assignment_id) from
1606: per_all_assignments_f paf,
1607: pay_all_payrolls_f pay,
1608: hr_soft_coding_keyflex hsc
1609: where paf.payroll_id= pay.payroll_id
1610: and pay.soft_coding_keyflex_id= hsc.soft_coding_keyflex_id
1624: INTO v_per_agg_flag
1625: ,v_per_ni_flag
1626: ,v_primary_flag
1627: FROM per_all_people_f pap
1628: ,per_all_assignments_f paf
1629: WHERE paf.person_id = pap.person_id
1630: AND paf.assignment_id = p_assignment_id
1631: AND p_effective_date BETWEEN paf.effective_start_date
1632: AND paf.effective_end_date
1677: END get_ni_reporting_flag;
1678:
1679: FUNCTION get_rti_payroll_id
1680: (p_person_id IN per_all_people_f.person_id%TYPE
1681: ,p_assignment_id IN per_all_assignments_f.assignment_id%type
1682: ,p_aggregation_flag IN varchar2
1683: ,p_effective_date IN date) RETURN varchar2 IS
1684: v_rti_payroll_id varchar2(30);
1685: v_rti_assignment_id varchar2(30);
1688: IF nvl (p_aggregation_flag
1689: ,'N') = 'Y' THEN
1690: SELECT assignment_number
1691: INTO v_rti_payroll_id
1692: FROM per_all_assignments_f
1693: WHERE person_id = p_person_id
1694: AND nvl (primary_flag
1695: ,'N') = 'Y'
1696: AND p_effective_date BETWEEN effective_start_date
1697: AND effective_end_date;
1698: ELSE
1699: SELECT assignment_number
1700: INTO v_rti_payroll_id
1701: FROM per_all_assignments_f
1702: WHERE assignment_id = p_assignment_id
1703: AND p_effective_date BETWEEN effective_start_date
1704: AND effective_end_date;
1705: END IF;
1707: RETURN v_rti_payroll_id;
1708: END get_rti_payroll_id;
1709:
1710: PROCEDURE update_rti_agg_update_asg
1711: (p_assignment_id IN per_all_assignments_f.assignment_id%type
1712: ,p_effective_date IN date) IS
1713: p_person_id number;
1714: BEGIN
1715: SELECT DISTINCT
1714: BEGIN
1715: SELECT DISTINCT
1716: person_id
1717: INTO p_person_id
1718: FROM per_all_assignments_f
1719: WHERE assignment_id = p_assignment_id
1720: AND p_effective_date BETWEEN effective_start_date
1721: AND effective_end_date;
1722:
1755: ,paf.effective_start_date assignment_start_date
1756: ,pap.business_group_id business_group_id
1757: ,nvl(
1758: (SELECT MIN(paaf2.assignment_number)
1759: FROM per_all_assignments_f paaf2,
1760: pay_all_payrolls_f papf1,
1761: hr_soft_coding_keyflex hsck1
1762: WHERE paaf2.person_id = paf.person_id
1763: AND papf1.payroll_id = paaf2.payroll_id
1768: AND paf.effective_start_date BETWEEN paaf2.effective_start_date
1769: AND paaf2.effective_end_date)
1770: ,
1771: (SELECT MIN(paaf2.assignment_number)
1772: FROM per_all_assignments_f paaf2,
1773: pay_all_payrolls_f papf1,
1774: hr_soft_coding_keyflex hsck1
1775: WHERE paaf2.person_id = paf.person_id
1776: AND papf1.payroll_id = paaf2.payroll_id
1781: AND paaf2.effective_end_date)
1782: ) primary_assignment_number
1783: ,hsck.segment1 paye_reference
1784: FROM per_all_people_f pap
1785: ,per_all_assignments_f paf
1786: ,pay_all_payrolls_f papf
1787: ,hr_soft_coding_keyflex hsck
1788: WHERE paf.person_id = pap.person_id
1789: AND pap.person_id = p_person_id
1798: )
1799: AND paf.effective_start_date =
1800: (
1801: SELECT MAX(paaf2.effective_start_date)
1802: FROM per_all_assignments_f paaf2
1803: WHERE paaf2.assignment_id = paf.assignment_id
1804: AND paaf2.assignment_type = 'E'
1805: )
1806: AND TRUNC(sysdate) BETWEEN papf.effective_start_date
1811: select paaf.assignment_id
1812: ,paaf.assignment_number
1813: ,paaf.business_group_id
1814: ,trim (papf.per_information9)
1815: from per_all_assignments_f paaf
1816: ,per_all_people_f papf
1817: where paaf.person_id = p_person_id
1818: and papf.person_id = paaf.person_id
1819: AND p_effective_date BETWEEN paaf.effective_start_date
1993: CURSOR csr_get_all_asg(p_business_group_id NUMBER)
1994: is
1995: select paaf.assignment_id assignment_id
1996: ,sck.segment1 paye_ref
1997: from per_all_assignments_f paaf,
1998: pay_all_payrolls_f papf,
1999: hr_soft_coding_keyflex sck
2000: where paaf.person_id = p_person_id
2001: and paaf.payroll_id = papf.payroll_id
2003: and papf.soft_coding_keyflex_id = sck.soft_coding_keyflex_id
2004: and p_effective_date between papf.effective_start_date and papf.effective_end_date
2005: and paaf.effective_start_date =
2006: ( select max(asg2.effective_start_date)
2007: from per_all_assignments_f asg2
2008: where asg2.assignment_id = paaf.assignment_id
2009: and asg2.assignment_type = 'E'
2010: and asg2.effective_start_date <= p_effective_date
2011: );
2021: --Fetches the assignment_id of the person within the same paye_ref
2022: CURSOR csr_get_agg_asg(p_assignment_id NUMBER, p_paye_ref varchar2,p_business_group_id NUMBER)
2023: is
2024: select paaf.assignment_id assignment_id
2025: from per_all_assignments_f paaf,
2026: pay_all_payrolls_f papf,
2027: hr_soft_coding_keyflex sck
2028: where paaf.person_id = p_person_id
2029: and paaf.assignment_id <> p_assignment_id
2033: and upper(sck.segment1) = upper(p_paye_ref)
2034: and p_effective_date between papf.effective_start_date and papf.effective_end_date
2035: and paaf.effective_start_date =
2036: ( select max(asg2.effective_start_date)
2037: from per_all_assignments_f asg2
2038: where asg2.assignment_id = paaf.assignment_id
2039: and asg2.assignment_type = 'E'
2040: and asg2.effective_start_date <= p_effective_date
2041: );
2241:
2242: END update_asg_rti_starter_flag;
2243: --
2244: PROCEDURE update_rti_starter_asg
2245: (p_assignment_id IN per_all_assignments_f.assignment_id%type
2246: ,p_effective_date IN date) IS
2247: p_person_id number;
2248: BEGIN
2249: SELECT DISTINCT
2248: BEGIN
2249: SELECT DISTINCT
2250: person_id
2251: INTO p_person_id
2252: FROM per_all_assignments_f
2253: WHERE assignment_id = p_assignment_id
2254: AND p_effective_date BETWEEN effective_start_date
2255: AND effective_end_date;
2256: