eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.SSP_SMP_SUPPORT_PKG dependencies on HR_GBNICAR

Line 976: ((paa.date_start between hr_gbnicar.uk_tax_yr_start(sysdate) and hr_gbnicar.uk_tax_yr_end(sysdate))

972: and paat.absence_category = 'S'
973: and
974: /* SSP absences which span tax years or start in old tax year and are open-ended */
975: (
976: ((paa.date_start between hr_gbnicar.uk_tax_yr_start(sysdate) and hr_gbnicar.uk_tax_yr_end(sysdate))
977: and (paa.date_end > hr_gbnicar.uk_tax_yr_end(sysdate) or paa.date_end is null)
978: )
979: or
980: ((paa.sickness_start_date between hr_gbnicar.uk_tax_yr_start(sysdate) and hr_gbnicar.uk_tax_yr_end(sysdate))

Line 977: and (paa.date_end > hr_gbnicar.uk_tax_yr_end(sysdate) or paa.date_end is null)

973: and
974: /* SSP absences which span tax years or start in old tax year and are open-ended */
975: (
976: ((paa.date_start between hr_gbnicar.uk_tax_yr_start(sysdate) and hr_gbnicar.uk_tax_yr_end(sysdate))
977: and (paa.date_end > hr_gbnicar.uk_tax_yr_end(sysdate) or paa.date_end is null)
978: )
979: or
980: ((paa.sickness_start_date between hr_gbnicar.uk_tax_yr_start(sysdate) and hr_gbnicar.uk_tax_yr_end(sysdate))
981: and (paa.sickness_end_date > hr_gbnicar.uk_tax_yr_end(sysdate) or paa.sickness_end_date is null)

Line 980: ((paa.sickness_start_date between hr_gbnicar.uk_tax_yr_start(sysdate) and hr_gbnicar.uk_tax_yr_end(sysdate))

976: ((paa.date_start between hr_gbnicar.uk_tax_yr_start(sysdate) and hr_gbnicar.uk_tax_yr_end(sysdate))
977: and (paa.date_end > hr_gbnicar.uk_tax_yr_end(sysdate) or paa.date_end is null)
978: )
979: or
980: ((paa.sickness_start_date between hr_gbnicar.uk_tax_yr_start(sysdate) and hr_gbnicar.uk_tax_yr_end(sysdate))
981: and (paa.sickness_end_date > hr_gbnicar.uk_tax_yr_end(sysdate) or paa.sickness_end_date is null)
982: )
983: or
984: /* SSP absences which start in the new tax year */

Line 981: and (paa.sickness_end_date > hr_gbnicar.uk_tax_yr_end(sysdate) or paa.sickness_end_date is null)

977: and (paa.date_end > hr_gbnicar.uk_tax_yr_end(sysdate) or paa.date_end is null)
978: )
979: or
980: ((paa.sickness_start_date between hr_gbnicar.uk_tax_yr_start(sysdate) and hr_gbnicar.uk_tax_yr_end(sysdate))
981: and (paa.sickness_end_date > hr_gbnicar.uk_tax_yr_end(sysdate) or paa.sickness_end_date is null)
982: )
983: or
984: /* SSP absences which start in the new tax year */
985: (

Line 986: paa.date_start >= hr_gbnicar.uk_tax_yr_end(sysdate)

982: )
983: or
984: /* SSP absences which start in the new tax year */
985: (
986: paa.date_start >= hr_gbnicar.uk_tax_yr_end(sysdate)
987: or paa.date_projected_start >= hr_gbnicar.uk_tax_yr_end(sysdate)
988: )
989: )
990: /* Do not retrieve terminated employees whose actual termination dates have passed or are null */

Line 987: or paa.date_projected_start >= hr_gbnicar.uk_tax_yr_end(sysdate)

983: or
984: /* SSP absences which start in the new tax year */
985: (
986: paa.date_start >= hr_gbnicar.uk_tax_yr_end(sysdate)
987: or paa.date_projected_start >= hr_gbnicar.uk_tax_yr_end(sysdate)
988: )
989: )
990: /* Do not retrieve terminated employees whose actual termination dates have passed or are null */
991: and not exists

Line 1024: ((paa.date_start between hr_gbnicar.uk_tax_yr_start(sysdate) and hr_gbnicar.uk_tax_yr_end(sysdate))

1020: and nvl(mat.leave_type, 'MA') = p_leave_type
1021: and
1022: /* SMP absences which span tax years or start in old tax year and are open-ended */
1023: (
1024: ((paa.date_start between hr_gbnicar.uk_tax_yr_start(sysdate) and hr_gbnicar.uk_tax_yr_end(sysdate))
1025: and (paa.date_end > hr_gbnicar.uk_tax_yr_end(sysdate) or paa.date_end is null)
1026: )
1027: or
1028: ((paa.date_projected_start between hr_gbnicar.uk_tax_yr_start(sysdate) and hr_gbnicar.uk_tax_yr_end(sysdate))

Line 1025: and (paa.date_end > hr_gbnicar.uk_tax_yr_end(sysdate) or paa.date_end is null)

1021: and
1022: /* SMP absences which span tax years or start in old tax year and are open-ended */
1023: (
1024: ((paa.date_start between hr_gbnicar.uk_tax_yr_start(sysdate) and hr_gbnicar.uk_tax_yr_end(sysdate))
1025: and (paa.date_end > hr_gbnicar.uk_tax_yr_end(sysdate) or paa.date_end is null)
1026: )
1027: or
1028: ((paa.date_projected_start between hr_gbnicar.uk_tax_yr_start(sysdate) and hr_gbnicar.uk_tax_yr_end(sysdate))
1029: and (paa.date_projected_end > hr_gbnicar.uk_tax_yr_end(sysdate) or paa.date_projected_end is null)

Line 1028: ((paa.date_projected_start between hr_gbnicar.uk_tax_yr_start(sysdate) and hr_gbnicar.uk_tax_yr_end(sysdate))

1024: ((paa.date_start between hr_gbnicar.uk_tax_yr_start(sysdate) and hr_gbnicar.uk_tax_yr_end(sysdate))
1025: and (paa.date_end > hr_gbnicar.uk_tax_yr_end(sysdate) or paa.date_end is null)
1026: )
1027: or
1028: ((paa.date_projected_start between hr_gbnicar.uk_tax_yr_start(sysdate) and hr_gbnicar.uk_tax_yr_end(sysdate))
1029: and (paa.date_projected_end > hr_gbnicar.uk_tax_yr_end(sysdate) or paa.date_projected_end is null)
1030: and paa.date_start IS NULL -- use projected dates only when actual dates not available
1031: )
1032: or

Line 1029: and (paa.date_projected_end > hr_gbnicar.uk_tax_yr_end(sysdate) or paa.date_projected_end is null)

1025: and (paa.date_end > hr_gbnicar.uk_tax_yr_end(sysdate) or paa.date_end is null)
1026: )
1027: or
1028: ((paa.date_projected_start between hr_gbnicar.uk_tax_yr_start(sysdate) and hr_gbnicar.uk_tax_yr_end(sysdate))
1029: and (paa.date_projected_end > hr_gbnicar.uk_tax_yr_end(sysdate) or paa.date_projected_end is null)
1030: and paa.date_start IS NULL -- use projected dates only when actual dates not available
1031: )
1032: or
1033: /* SMP absences which start in the new tax year */

Line 1035: paa.date_start >= hr_gbnicar.uk_tax_yr_end(sysdate)

1031: )
1032: or
1033: /* SMP absences which start in the new tax year */
1034: (
1035: paa.date_start >= hr_gbnicar.uk_tax_yr_end(sysdate)
1036: or (paa.date_projected_start >= hr_gbnicar.uk_tax_yr_end(sysdate)
1037: and paa.date_start IS NULL) -- use projected dates only when actual dates not available
1038: )
1039: )

Line 1036: or (paa.date_projected_start >= hr_gbnicar.uk_tax_yr_end(sysdate)

1032: or
1033: /* SMP absences which start in the new tax year */
1034: (
1035: paa.date_start >= hr_gbnicar.uk_tax_yr_end(sysdate)
1036: or (paa.date_projected_start >= hr_gbnicar.uk_tax_yr_end(sysdate)
1037: and paa.date_start IS NULL) -- use projected dates only when actual dates not available
1038: )
1039: )
1040: /* Do not retrieve employees whose final process dates have passed */

Line 1106: and piv.effective_start_date > hr_gbnicar.uk_tax_yr_end(sysdate);

1102: pay_element_types_f petf
1103: where petf.element_type_id = piv.element_type_id
1104: and piv.name = 'Rate'
1105: and petf.element_name = 'Statutory Sick Pay'
1106: and piv.effective_start_date > hr_gbnicar.uk_tax_yr_end(sysdate);
1107:
1108:
1109: cursor csr_first_new_ssp_entry is
1110: select peev1.screen_entry_value

Line 1124: and piv.effective_start_date > hr_gbnicar.uk_tax_yr_end(sysdate)

1120: and peef.element_link_id = pelf.element_link_id
1121: and pelf.element_type_id = petf.element_type_id
1122: and piv.name = 'Rate'
1123: and petf.element_name = 'Statutory Sick Pay'
1124: and piv.effective_start_date > hr_gbnicar.uk_tax_yr_end(sysdate)
1125: and peev2.element_entry_value_id =
1126: (select peev3.element_entry_value_id
1127: from pay_element_entry_values_f peev3
1128: where input_value_id =

Line 1136: fnd_date.date_to_canonical(hr_gbnicar.uk_tax_yr_end(sysdate))

1132: where petf.element_type_id = piv.element_type_id
1133: and petf.element_name = 'Statutory Sick Pay'
1134: and piv.name = 'From')
1135: and peev3.screen_entry_value >
1136: fnd_date.date_to_canonical(hr_gbnicar.uk_tax_yr_end(sysdate))
1137: /* Retrieve only those entries that will be retrieved by main SSP query */
1138: and peev3.element_entry_id in
1139: ( select peef1.element_entry_id
1140: from pay_element_entries_f peef1,

Line 1228: and petf.effective_start_date >= trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4

1224: cursor csr_new_smp_rate is
1225: select petf.element_information10, petf.element_information9, petf.element_information16
1226: from pay_element_types_f petf
1227: where petf.element_name = 'Statutory Maternity Pay'
1228: and petf.effective_start_date >= trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4
1229: order by effective_start_date;
1230:
1231:
1232: /* 09/12/2003 rmakhija - commented out following cursor because element entry value

Line 1256: and petf.effective_start_date > hr_gbnicar.uk_tax_yr_end(sysdate) - 5

1252: and peef.element_link_id = pelf.element_link_id
1253: and pelf.element_type_id = petf.element_type_id
1254: and piv.name = 'Amount'
1255: and petf.element_name = 'Statutory Maternity Pay'
1256: and petf.effective_start_date > hr_gbnicar.uk_tax_yr_end(sysdate) - 5
1257: -- Time restriction - only rows after tax year end
1258: and peev2.input_value_id =
1259: (select input_value_id
1260: from pay_input_values_f piv

Line 1264: and peev2.screen_entry_value > fnd_date.date_to_canonical(hr_gbnicar.uk_tax_yr_end(sysdate) - 5)

1260: from pay_input_values_f piv
1261: where petf.element_type_id = piv.element_type_id
1262: and upper(piv.name) = upper('Week commencing')
1263: )
1264: and peev2.screen_entry_value > fnd_date.date_to_canonical(hr_gbnicar.uk_tax_yr_end(sysdate) - 5)
1265: -- Retrieve only those entries that main SMP query will retrieve
1266: and exists
1267: ( select 1
1268: from per_all_assignments_f paf,

Line 1297: and petf.effective_start_date < trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4

1293: cursor csr_old_smp_rate is
1294: select petf.element_information10, petf.element_information9, petf.element_information16
1295: from pay_element_types_f petf
1296: where petf.element_name = 'Statutory Maternity Pay'
1297: and petf.effective_start_date < trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4
1298: order by effective_start_date desc;
1299:
1300: l_new_SMP_rate number;
1301: l_old_SMP_rate number;

Line 1360: and petf.effective_start_date >= trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4

1356: cursor csr_new_sap_rate is
1357: select petf.element_information5, petf.element_information7
1358: from pay_element_types_f petf
1359: where petf.element_name = 'Statutory Adoption Pay'
1360: and petf.effective_start_date >= trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4
1361: order by effective_start_date;
1362: --
1363: /* 09/12/2003 rmakhija - commented out following cursor because element entry value
1364: for 'Amount' is a calculated value and it is not same

Line 1385: and petf.effective_start_date > hr_gbnicar.uk_tax_yr_end(sysdate) - 5

1381: and peef.element_link_id = pelf.element_link_id
1382: and pelf.element_type_id = petf.element_type_id
1383: and piv.name = 'Amount'
1384: and petf.element_name = 'Statutory Adoption Pay'
1385: and petf.effective_start_date > hr_gbnicar.uk_tax_yr_end(sysdate) - 5
1386: -- Time restriction - only rows after tax year end
1387: and peev2.input_value_id =
1388: (select input_value_id
1389: from pay_input_values_f piv

Line 1393: and peev2.screen_entry_value > fnd_date.date_to_canonical(hr_gbnicar.uk_tax_yr_end(sysdate) - 5)

1389: from pay_input_values_f piv
1390: where petf.element_type_id = piv.element_type_id
1391: and upper(piv.name) = upper('Week commencing')
1392: )
1393: and peev2.screen_entry_value > fnd_date.date_to_canonical(hr_gbnicar.uk_tax_yr_end(sysdate) - 5)
1394: -- Retrieve only those entries that main sap query will retrieve
1395: and exists
1396: ( select 1
1397: from per_all_assignments_f paf,

Line 1419: and petf.effective_start_date < hr_gbnicar.uk_tax_yr_end(sysdate) - 4

1415: cursor csr_old_sap_rate is
1416: select petf.element_information5, petf.element_information7
1417: from pay_element_types_f petf
1418: where petf.element_name = 'Statutory Adoption Pay'
1419: and petf.effective_start_date < hr_gbnicar.uk_tax_yr_end(sysdate) - 4
1420: order by effective_start_date desc;
1421: --
1422: --
1423: l_new_sap_rate number;

Line 1476: and petf.effective_start_date >= trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4

1472: cursor csr_new_sppa_rate is
1473: select petf.element_information6, petf.element_information8
1474: from pay_element_types_f petf
1475: where petf.element_name = 'Statutory Paternity Pay Adoption'
1476: and petf.effective_start_date >= trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4
1477: order by effective_start_date;
1478: --
1479: /* 09/12/2003 rmakhija - commented out following cursor because element entry value
1480: for 'Amount' is a calculated value and it is not same

Line 1501: and petf.effective_start_date > hr_gbnicar.uk_tax_yr_end(sysdate) - 5

1497: and peef.element_link_id = pelf.element_link_id
1498: and pelf.element_type_id = petf.element_type_id
1499: and piv.name = 'Amount'
1500: and petf.element_name = 'Statutory Paternity Pay Adoption'
1501: and petf.effective_start_date > hr_gbnicar.uk_tax_yr_end(sysdate) - 5
1502: -- Time restriction - only rows after tax year end
1503: and peev2.input_value_id =
1504: (select input_value_id
1505: from pay_input_values_f piv

Line 1509: and peev2.screen_entry_value > fnd_date.date_to_canonical(hr_gbnicar.uk_tax_yr_end(sysdate) - 5)

1505: from pay_input_values_f piv
1506: where petf.element_type_id = piv.element_type_id
1507: and upper(piv.name) = upper('Week commencing')
1508: )
1509: and peev2.screen_entry_value > fnd_date.date_to_canonical(hr_gbnicar.uk_tax_yr_end(sysdate) - 5)
1510: -- Retrieve only those entries that main sppa query will retrieve
1511: and exists
1512: ( select 1
1513: from per_all_assignments_f paf,

Line 1535: and petf.effective_start_date < trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4

1531: cursor csr_old_sppa_rate is
1532: select petf.element_information6, petf.element_information8
1533: from pay_element_types_f petf
1534: where petf.element_name = 'Statutory Paternity Pay Adoption'
1535: and petf.effective_start_date < trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4
1536: order by effective_start_date desc;
1537:
1538: --
1539: l_new_sppa_rate number;

Line 1591: and petf.effective_start_date >= trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4

1587: cursor csr_new_sppb_rate is
1588: select petf.element_information6, petf.element_information9
1589: from pay_element_types_f petf
1590: where petf.element_name = 'Statutory Paternity Pay Birth'
1591: and petf.effective_start_date >= trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4
1592: order by effective_start_date;
1593: --
1594: /* 09/12/2003 rmakhija - commented out following cursor because element entry value
1595: for 'Amount' is a calculated value and it is not same

Line 1616: and petf.effective_start_date > hr_gbnicar.uk_tax_yr_end(sysdate) - 5

1612: and peef.element_link_id = pelf.element_link_id
1613: and pelf.element_type_id = petf.element_type_id
1614: and piv.name = 'Amount'
1615: and petf.element_name = 'Statutory Paternity Pay Birth'
1616: and petf.effective_start_date > hr_gbnicar.uk_tax_yr_end(sysdate) - 5
1617: -- Time restriction - only rows after tax year end
1618: and peev2.input_value_id =
1619: (select input_value_id
1620: from pay_input_values_f piv

Line 1624: and peev2.screen_entry_value > fnd_date.date_to_canonical(hr_gbnicar.uk_tax_yr_end(sysdate) - 5)

1620: from pay_input_values_f piv
1621: where petf.element_type_id = piv.element_type_id
1622: and upper(piv.name) = upper('Week commencing')
1623: )
1624: and peev2.screen_entry_value > fnd_date.date_to_canonical(hr_gbnicar.uk_tax_yr_end(sysdate) - 5)
1625: -- Retrieve only those entries that main SPPB query will retrieve
1626: and exists
1627: ( select 1
1628: from per_all_assignments_f paf,

Line 1650: and petf.effective_start_date < trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4

1646: cursor csr_old_sppb_rate is
1647: select petf.element_information6, petf.element_information9
1648: from pay_element_types_f petf
1649: where petf.element_name = 'Statutory Paternity Pay Birth'
1650: and petf.effective_start_date < trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4
1651: order by effective_start_date desc;
1652: --
1653: l_new_sppb_rate number;
1654: l_old_sppb_rate number;

Line 1705: and petf.effective_start_date >= trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4

1701: cursor csr_new_asppb_rate is
1702: select petf.element_information6, petf.element_information9
1703: from pay_element_types_f petf
1704: where petf.element_name = 'Additional Statutory Paternity Pay Birth'
1705: and petf.effective_start_date >= trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4
1706: order by effective_start_date;
1707:
1708:
1709: cursor csr_old_asppb_rate is

Line 1713: and petf.effective_start_date < trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4

1709: cursor csr_old_asppb_rate is
1710: select petf.element_information6, petf.element_information9
1711: from pay_element_types_f petf
1712: where petf.element_name = 'Additional Statutory Paternity Pay Birth'
1713: and petf.effective_start_date < trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4
1714: order by effective_start_date desc;
1715: --
1716: l_new_asppb_rate number;
1717: l_old_asppb_rate number;

Line 1766: and petf.effective_start_date >= trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4

1762: cursor csr_new_asppa_rate is
1763: select petf.element_information6, petf.element_information8
1764: from pay_element_types_f petf
1765: where petf.element_name = 'Additional Statutory Paternity Pay Adoption'
1766: and petf.effective_start_date >= trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4
1767: order by effective_start_date;
1768:
1769:
1770: cursor csr_old_asppa_rate is

Line 1774: and petf.effective_start_date < trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4

1770: cursor csr_old_asppa_rate is
1771: select petf.element_information6, petf.element_information8
1772: from pay_element_types_f petf
1773: where petf.element_name = 'Additional Statutory Paternity Pay Adoption'
1774: and petf.effective_start_date < trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4
1775: order by effective_start_date desc;
1776:
1777: l_new_asppa_rate number;
1778: l_old_asppa_rate number;

Line 2264: ((paa.date_start between hr_gbnicar.uk_tax_yr_start(sysdate) and hr_gbnicar.uk_tax_yr_end(sysdate))

2260: and paat.absence_category = 'S'
2261: and
2262: /* SSP absences which span tax years or start in old tax year and are open-ended */
2263: (
2264: ((paa.date_start between hr_gbnicar.uk_tax_yr_start(sysdate) and hr_gbnicar.uk_tax_yr_end(sysdate))
2265: and (paa.date_end > hr_gbnicar.uk_tax_yr_end(sysdate) or paa.date_end is null)
2266: )
2267: or
2268: ((paa.sickness_start_date between hr_gbnicar.uk_tax_yr_start(sysdate) and hr_gbnicar.uk_tax_yr_end(sysdate))

Line 2265: and (paa.date_end > hr_gbnicar.uk_tax_yr_end(sysdate) or paa.date_end is null)

2261: and
2262: /* SSP absences which span tax years or start in old tax year and are open-ended */
2263: (
2264: ((paa.date_start between hr_gbnicar.uk_tax_yr_start(sysdate) and hr_gbnicar.uk_tax_yr_end(sysdate))
2265: and (paa.date_end > hr_gbnicar.uk_tax_yr_end(sysdate) or paa.date_end is null)
2266: )
2267: or
2268: ((paa.sickness_start_date between hr_gbnicar.uk_tax_yr_start(sysdate) and hr_gbnicar.uk_tax_yr_end(sysdate))
2269: and (paa.sickness_end_date > hr_gbnicar.uk_tax_yr_end(sysdate) or paa.sickness_end_date is null)

Line 2268: ((paa.sickness_start_date between hr_gbnicar.uk_tax_yr_start(sysdate) and hr_gbnicar.uk_tax_yr_end(sysdate))

2264: ((paa.date_start between hr_gbnicar.uk_tax_yr_start(sysdate) and hr_gbnicar.uk_tax_yr_end(sysdate))
2265: and (paa.date_end > hr_gbnicar.uk_tax_yr_end(sysdate) or paa.date_end is null)
2266: )
2267: or
2268: ((paa.sickness_start_date between hr_gbnicar.uk_tax_yr_start(sysdate) and hr_gbnicar.uk_tax_yr_end(sysdate))
2269: and (paa.sickness_end_date > hr_gbnicar.uk_tax_yr_end(sysdate) or paa.sickness_end_date is null)
2270: )
2271: or
2272: /* SSP absences which start in the new tax year */

Line 2269: and (paa.sickness_end_date > hr_gbnicar.uk_tax_yr_end(sysdate) or paa.sickness_end_date is null)

2265: and (paa.date_end > hr_gbnicar.uk_tax_yr_end(sysdate) or paa.date_end is null)
2266: )
2267: or
2268: ((paa.sickness_start_date between hr_gbnicar.uk_tax_yr_start(sysdate) and hr_gbnicar.uk_tax_yr_end(sysdate))
2269: and (paa.sickness_end_date > hr_gbnicar.uk_tax_yr_end(sysdate) or paa.sickness_end_date is null)
2270: )
2271: or
2272: /* SSP absences which start in the new tax year */
2273: (

Line 2274: paa.date_start >= hr_gbnicar.uk_tax_yr_end(sysdate)

2270: )
2271: or
2272: /* SSP absences which start in the new tax year */
2273: (
2274: paa.date_start >= hr_gbnicar.uk_tax_yr_end(sysdate)
2275: or paa.date_projected_start >= hr_gbnicar.uk_tax_yr_end(sysdate)
2276: )
2277: )
2278: /* Do not retrieve terminated employees whose actual termination dates have passed or are null */

Line 2275: or paa.date_projected_start >= hr_gbnicar.uk_tax_yr_end(sysdate)

2271: or
2272: /* SSP absences which start in the new tax year */
2273: (
2274: paa.date_start >= hr_gbnicar.uk_tax_yr_end(sysdate)
2275: or paa.date_projected_start >= hr_gbnicar.uk_tax_yr_end(sysdate)
2276: )
2277: )
2278: /* Do not retrieve terminated employees whose actual termination dates have passed or are null */
2279: and not exists

Line 2312: ((paa.date_start between hr_gbnicar.uk_tax_yr_start(sysdate) and hr_gbnicar.uk_tax_yr_end(sysdate))

2308: and nvl(mat.leave_type, 'MA') = p_leave_type
2309: and
2310: /* SMP absences which span tax years or start in old tax year and are open-ended */
2311: (
2312: ((paa.date_start between hr_gbnicar.uk_tax_yr_start(sysdate) and hr_gbnicar.uk_tax_yr_end(sysdate))
2313: and (paa.date_end > hr_gbnicar.uk_tax_yr_end(sysdate) or paa.date_end is null)
2314: )
2315: or
2316: ((paa.date_projected_start between hr_gbnicar.uk_tax_yr_start(sysdate) and hr_gbnicar.uk_tax_yr_end(sysdate))

Line 2313: and (paa.date_end > hr_gbnicar.uk_tax_yr_end(sysdate) or paa.date_end is null)

2309: and
2310: /* SMP absences which span tax years or start in old tax year and are open-ended */
2311: (
2312: ((paa.date_start between hr_gbnicar.uk_tax_yr_start(sysdate) and hr_gbnicar.uk_tax_yr_end(sysdate))
2313: and (paa.date_end > hr_gbnicar.uk_tax_yr_end(sysdate) or paa.date_end is null)
2314: )
2315: or
2316: ((paa.date_projected_start between hr_gbnicar.uk_tax_yr_start(sysdate) and hr_gbnicar.uk_tax_yr_end(sysdate))
2317: and (paa.date_projected_end > hr_gbnicar.uk_tax_yr_end(sysdate) or paa.date_projected_end is null)

Line 2316: ((paa.date_projected_start between hr_gbnicar.uk_tax_yr_start(sysdate) and hr_gbnicar.uk_tax_yr_end(sysdate))

2312: ((paa.date_start between hr_gbnicar.uk_tax_yr_start(sysdate) and hr_gbnicar.uk_tax_yr_end(sysdate))
2313: and (paa.date_end > hr_gbnicar.uk_tax_yr_end(sysdate) or paa.date_end is null)
2314: )
2315: or
2316: ((paa.date_projected_start between hr_gbnicar.uk_tax_yr_start(sysdate) and hr_gbnicar.uk_tax_yr_end(sysdate))
2317: and (paa.date_projected_end > hr_gbnicar.uk_tax_yr_end(sysdate) or paa.date_projected_end is null)
2318: and paa.date_start IS NULL -- use projected dates only when actual dates not available
2319: )
2320: or

Line 2317: and (paa.date_projected_end > hr_gbnicar.uk_tax_yr_end(sysdate) or paa.date_projected_end is null)

2313: and (paa.date_end > hr_gbnicar.uk_tax_yr_end(sysdate) or paa.date_end is null)
2314: )
2315: or
2316: ((paa.date_projected_start between hr_gbnicar.uk_tax_yr_start(sysdate) and hr_gbnicar.uk_tax_yr_end(sysdate))
2317: and (paa.date_projected_end > hr_gbnicar.uk_tax_yr_end(sysdate) or paa.date_projected_end is null)
2318: and paa.date_start IS NULL -- use projected dates only when actual dates not available
2319: )
2320: or
2321: /* SMP absences which start in the new tax year */

Line 2323: paa.date_start >= hr_gbnicar.uk_tax_yr_end(sysdate)

2319: )
2320: or
2321: /* SMP absences which start in the new tax year */
2322: (
2323: paa.date_start >= hr_gbnicar.uk_tax_yr_end(sysdate)
2324: or (paa.date_projected_start >= hr_gbnicar.uk_tax_yr_end(sysdate)
2325: and paa.date_start IS NULL) -- use projected dates only when actual dates not available
2326: )
2327: )

Line 2324: or (paa.date_projected_start >= hr_gbnicar.uk_tax_yr_end(sysdate)

2320: or
2321: /* SMP absences which start in the new tax year */
2322: (
2323: paa.date_start >= hr_gbnicar.uk_tax_yr_end(sysdate)
2324: or (paa.date_projected_start >= hr_gbnicar.uk_tax_yr_end(sysdate)
2325: and paa.date_start IS NULL) -- use projected dates only when actual dates not available
2326: )
2327: )
2328: /* Do not retrieve employees whose final process dates have passed */

Line 2407: and piv.effective_start_date > hr_gbnicar.uk_tax_yr_end(sysdate);

2403: pay_element_types_f petf
2404: where petf.element_type_id = piv.element_type_id
2405: and piv.name = 'Rate'
2406: and petf.element_name = 'Statutory Sick Pay'
2407: and piv.effective_start_date > hr_gbnicar.uk_tax_yr_end(sysdate);
2408:
2409:
2410: cursor csr_first_new_ssp_entry is
2411: select peev1.screen_entry_value

Line 2425: and piv.effective_start_date > hr_gbnicar.uk_tax_yr_end(sysdate)

2421: and peef.element_link_id = pelf.element_link_id
2422: and pelf.element_type_id = petf.element_type_id
2423: and piv.name = 'Rate'
2424: and petf.element_name = 'Statutory Sick Pay'
2425: and piv.effective_start_date > hr_gbnicar.uk_tax_yr_end(sysdate)
2426: and peev2.element_entry_value_id =
2427: (select peev3.element_entry_value_id
2428: from pay_element_entry_values_f peev3
2429: where input_value_id =

Line 2437: fnd_date.date_to_canonical(hr_gbnicar.uk_tax_yr_end(sysdate))

2433: where petf.element_type_id = piv.element_type_id
2434: and petf.element_name = 'Statutory Sick Pay'
2435: and piv.name = 'From')
2436: and peev3.screen_entry_value >
2437: fnd_date.date_to_canonical(hr_gbnicar.uk_tax_yr_end(sysdate))
2438: /* Retrieve only those entries that will be retrieved by main SSP query */
2439: and peev3.element_entry_id in
2440: ( select peef1.element_entry_id
2441: from pay_element_entries_f peef1,

Line 2529: and petf.effective_start_date >= trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4

2525: cursor csr_new_smp_rate is
2526: select petf.element_information10, petf.element_information9, petf.element_information16
2527: from pay_element_types_f petf
2528: where petf.element_name = 'Statutory Maternity Pay'
2529: and petf.effective_start_date >= trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4
2530: order by effective_start_date;
2531:
2532:
2533: /* 09/12/2003 rmakhija - commented out following cursor because element entry value

Line 2557: and petf.effective_start_date > hr_gbnicar.uk_tax_yr_end(sysdate) - 5

2553: and peef.element_link_id = pelf.element_link_id
2554: and pelf.element_type_id = petf.element_type_id
2555: and piv.name = 'Amount'
2556: and petf.element_name = 'Statutory Maternity Pay'
2557: and petf.effective_start_date > hr_gbnicar.uk_tax_yr_end(sysdate) - 5
2558: -- Time restriction - only rows after tax year end
2559: and peev2.input_value_id =
2560: (select input_value_id
2561: from pay_input_values_f piv

Line 2565: and peev2.screen_entry_value > fnd_date.date_to_canonical(hr_gbnicar.uk_tax_yr_end(sysdate) - 5)

2561: from pay_input_values_f piv
2562: where petf.element_type_id = piv.element_type_id
2563: and upper(piv.name) = upper('Week commencing')
2564: )
2565: and peev2.screen_entry_value > fnd_date.date_to_canonical(hr_gbnicar.uk_tax_yr_end(sysdate) - 5)
2566: -- Retrieve only those entries that main SMP query will retrieve
2567: and exists
2568: ( select 1
2569: from per_all_assignments_f paf,

Line 2598: and petf.effective_start_date < trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4

2594: cursor csr_old_smp_rate is
2595: select petf.element_information10, petf.element_information9, petf.element_information16
2596: from pay_element_types_f petf
2597: where petf.element_name = 'Statutory Maternity Pay'
2598: and petf.effective_start_date < trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4
2599: order by effective_start_date desc;
2600:
2601: l_new_SMP_rate number;
2602: l_old_SMP_rate number;

Line 2661: and petf.effective_start_date >= trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4

2657: cursor csr_new_sap_rate is
2658: select petf.element_information5, petf.element_information7
2659: from pay_element_types_f petf
2660: where petf.element_name = 'Statutory Adoption Pay'
2661: and petf.effective_start_date >= trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4
2662: order by effective_start_date;
2663: --
2664: /* 09/12/2003 rmakhija - commented out following cursor because element entry value
2665: for 'Amount' is a calculated value and it is not same

Line 2686: and petf.effective_start_date > hr_gbnicar.uk_tax_yr_end(sysdate) - 5

2682: and peef.element_link_id = pelf.element_link_id
2683: and pelf.element_type_id = petf.element_type_id
2684: and piv.name = 'Amount'
2685: and petf.element_name = 'Statutory Adoption Pay'
2686: and petf.effective_start_date > hr_gbnicar.uk_tax_yr_end(sysdate) - 5
2687: -- Time restriction - only rows after tax year end
2688: and peev2.input_value_id =
2689: (select input_value_id
2690: from pay_input_values_f piv

Line 2694: and peev2.screen_entry_value > fnd_date.date_to_canonical(hr_gbnicar.uk_tax_yr_end(sysdate) - 5)

2690: from pay_input_values_f piv
2691: where petf.element_type_id = piv.element_type_id
2692: and upper(piv.name) = upper('Week commencing')
2693: )
2694: and peev2.screen_entry_value > fnd_date.date_to_canonical(hr_gbnicar.uk_tax_yr_end(sysdate) - 5)
2695: -- Retrieve only those entries that main sap query will retrieve
2696: and exists
2697: ( select 1
2698: from per_all_assignments_f paf,

Line 2720: and petf.effective_start_date < hr_gbnicar.uk_tax_yr_end(sysdate) - 4

2716: cursor csr_old_sap_rate is
2717: select petf.element_information5, petf.element_information7
2718: from pay_element_types_f petf
2719: where petf.element_name = 'Statutory Adoption Pay'
2720: and petf.effective_start_date < hr_gbnicar.uk_tax_yr_end(sysdate) - 4
2721: order by effective_start_date desc;
2722: --
2723: --
2724: l_new_sap_rate number;

Line 2777: and petf.effective_start_date >= trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4

2773: cursor csr_new_sppa_rate is
2774: select petf.element_information6, petf.element_information8
2775: from pay_element_types_f petf
2776: where petf.element_name = 'Statutory Paternity Pay Adoption'
2777: and petf.effective_start_date >= trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4
2778: order by effective_start_date;
2779: --
2780: /* 09/12/2003 rmakhija - commented out following cursor because element entry value
2781: for 'Amount' is a calculated value and it is not same

Line 2802: and petf.effective_start_date > hr_gbnicar.uk_tax_yr_end(sysdate) - 5

2798: and peef.element_link_id = pelf.element_link_id
2799: and pelf.element_type_id = petf.element_type_id
2800: and piv.name = 'Amount'
2801: and petf.element_name = 'Statutory Paternity Pay Adoption'
2802: and petf.effective_start_date > hr_gbnicar.uk_tax_yr_end(sysdate) - 5
2803: -- Time restriction - only rows after tax year end
2804: and peev2.input_value_id =
2805: (select input_value_id
2806: from pay_input_values_f piv

Line 2810: and peev2.screen_entry_value > fnd_date.date_to_canonical(hr_gbnicar.uk_tax_yr_end(sysdate) - 5)

2806: from pay_input_values_f piv
2807: where petf.element_type_id = piv.element_type_id
2808: and upper(piv.name) = upper('Week commencing')
2809: )
2810: and peev2.screen_entry_value > fnd_date.date_to_canonical(hr_gbnicar.uk_tax_yr_end(sysdate) - 5)
2811: -- Retrieve only those entries that main sppa query will retrieve
2812: and exists
2813: ( select 1
2814: from per_all_assignments_f paf,

Line 2836: and petf.effective_start_date < trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4

2832: cursor csr_old_sppa_rate is
2833: select petf.element_information6, petf.element_information8
2834: from pay_element_types_f petf
2835: where petf.element_name = 'Statutory Paternity Pay Adoption'
2836: and petf.effective_start_date < trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4
2837: order by effective_start_date desc;
2838:
2839: --
2840: l_new_sppa_rate number;

Line 2892: and petf.effective_start_date >= trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4

2888: cursor csr_new_sppb_rate is
2889: select petf.element_information6, petf.element_information9
2890: from pay_element_types_f petf
2891: where petf.element_name = 'Statutory Paternity Pay Birth'
2892: and petf.effective_start_date >= trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4
2893: order by effective_start_date;
2894: --
2895: /* 09/12/2003 rmakhija - commented out following cursor because element entry value
2896: for 'Amount' is a calculated value and it is not same

Line 2917: and petf.effective_start_date > hr_gbnicar.uk_tax_yr_end(sysdate) - 5

2913: and peef.element_link_id = pelf.element_link_id
2914: and pelf.element_type_id = petf.element_type_id
2915: and piv.name = 'Amount'
2916: and petf.element_name = 'Statutory Paternity Pay Birth'
2917: and petf.effective_start_date > hr_gbnicar.uk_tax_yr_end(sysdate) - 5
2918: -- Time restriction - only rows after tax year end
2919: and peev2.input_value_id =
2920: (select input_value_id
2921: from pay_input_values_f piv

Line 2925: and peev2.screen_entry_value > fnd_date.date_to_canonical(hr_gbnicar.uk_tax_yr_end(sysdate) - 5)

2921: from pay_input_values_f piv
2922: where petf.element_type_id = piv.element_type_id
2923: and upper(piv.name) = upper('Week commencing')
2924: )
2925: and peev2.screen_entry_value > fnd_date.date_to_canonical(hr_gbnicar.uk_tax_yr_end(sysdate) - 5)
2926: -- Retrieve only those entries that main SPPB query will retrieve
2927: and exists
2928: ( select 1
2929: from per_all_assignments_f paf,

Line 2951: and petf.effective_start_date < trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4

2947: cursor csr_old_sppb_rate is
2948: select petf.element_information6, petf.element_information9
2949: from pay_element_types_f petf
2950: where petf.element_name = 'Statutory Paternity Pay Birth'
2951: and petf.effective_start_date < trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4
2952: order by effective_start_date desc;
2953: --
2954: l_new_sppb_rate number;
2955: l_old_sppb_rate number;

Line 3007: and petf.effective_start_date >= trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4

3003: cursor csr_new_asppb_rate is
3004: select petf.element_information6, petf.element_information9
3005: from pay_element_types_f petf
3006: where petf.element_name = 'Additional Statutory Paternity Pay Birth'
3007: and petf.effective_start_date >= trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4
3008: order by effective_start_date;
3009:
3010:
3011: cursor csr_old_asppb_rate is

Line 3015: and petf.effective_start_date < trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4

3011: cursor csr_old_asppb_rate is
3012: select petf.element_information6, petf.element_information9
3013: from pay_element_types_f petf
3014: where petf.element_name = 'Additional Statutory Paternity Pay Birth'
3015: and petf.effective_start_date < trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4
3016: order by effective_start_date desc;
3017: --
3018: l_new_asppb_rate number;
3019: l_old_asppb_rate number;

Line 3068: and petf.effective_start_date >= trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4

3064: cursor csr_new_asppa_rate is
3065: select petf.element_information6, petf.element_information8
3066: from pay_element_types_f petf
3067: where petf.element_name = 'Additional Statutory Paternity Pay Adoption'
3068: and petf.effective_start_date >= trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4
3069: order by effective_start_date;
3070:
3071:
3072: cursor csr_old_asppa_rate is

Line 3076: and petf.effective_start_date < trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4

3072: cursor csr_old_asppa_rate is
3073: select petf.element_information6, petf.element_information8
3074: from pay_element_types_f petf
3075: where petf.element_name = 'Additional Statutory Paternity Pay Adoption'
3076: and petf.effective_start_date < trunc(hr_gbnicar.uk_tax_yr_end(sysdate)) - 4
3077: order by effective_start_date desc;
3078: --
3079: l_new_asppa_rate number;
3080: l_old_asppa_rate number;