APPS.SSP_SMP_SUPPORT_PKG dependencies on FND_DATE

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

1079: where petf.element_type_id = piv.element_type_id
1080: and petf.element_name = 'Statutory Sick Pay'
1081: and piv.name = 'From')
1082: and peev3.screen_entry_value >
1083: fnd_date.date_to_canonical(hr_gbnicar.uk_tax_yr_end(sysdate))
1084: /* Retrieve only those entries that will be retrieved by main SSP query */
1085: and peev3.element_entry_id in
1086: ( select peef1.element_entry_id
1087: from pay_element_entries_f peef1,

Line 1096: and peev3.screen_entry_value between fnd_date.date_to_canonical(ppf.effective_start_date)

1092: where peef1.assignment_id = paf.assignment_id
1093: and paf.person_id = ppf.person_id
1094: and ppf.person_id = pps.person_id
1095: and ppt.person_type_id = ppf.person_type_id
1096: and peev3.screen_entry_value between fnd_date.date_to_canonical(ppf.effective_start_date)
1097: and fnd_date.date_to_canonical(ppf.effective_end_date)
1098: and peev3.screen_entry_value between fnd_date.date_to_canonical(paf.effective_start_date)
1099: and fnd_date.date_to_canonical(paf.effective_end_date)
1100: and ppt.system_person_type = 'EMP'

Line 1097: and fnd_date.date_to_canonical(ppf.effective_end_date)

1093: and paf.person_id = ppf.person_id
1094: and ppf.person_id = pps.person_id
1095: and ppt.person_type_id = ppf.person_type_id
1096: and peev3.screen_entry_value between fnd_date.date_to_canonical(ppf.effective_start_date)
1097: and fnd_date.date_to_canonical(ppf.effective_end_date)
1098: and peev3.screen_entry_value between fnd_date.date_to_canonical(paf.effective_start_date)
1099: and fnd_date.date_to_canonical(paf.effective_end_date)
1100: and ppt.system_person_type = 'EMP'
1101: and nvl(pps.actual_termination_date,to_date('31/12/4712','DD/MM/YYYY')) >= sysdate

Line 1098: and peev3.screen_entry_value between fnd_date.date_to_canonical(paf.effective_start_date)

1094: and ppf.person_id = pps.person_id
1095: and ppt.person_type_id = ppf.person_type_id
1096: and peev3.screen_entry_value between fnd_date.date_to_canonical(ppf.effective_start_date)
1097: and fnd_date.date_to_canonical(ppf.effective_end_date)
1098: and peev3.screen_entry_value between fnd_date.date_to_canonical(paf.effective_start_date)
1099: and fnd_date.date_to_canonical(paf.effective_end_date)
1100: and ppt.system_person_type = 'EMP'
1101: and nvl(pps.actual_termination_date,to_date('31/12/4712','DD/MM/YYYY')) >= sysdate
1102: )

Line 1099: and fnd_date.date_to_canonical(paf.effective_end_date)

1095: and ppt.person_type_id = ppf.person_type_id
1096: and peev3.screen_entry_value between fnd_date.date_to_canonical(ppf.effective_start_date)
1097: and fnd_date.date_to_canonical(ppf.effective_end_date)
1098: and peev3.screen_entry_value between fnd_date.date_to_canonical(paf.effective_start_date)
1099: and fnd_date.date_to_canonical(paf.effective_end_date)
1100: and ppt.system_person_type = 'EMP'
1101: and nvl(pps.actual_termination_date,to_date('31/12/4712','DD/MM/YYYY')) >= sysdate
1102: )
1103: and rownum = 1);

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

1207: from pay_input_values_f piv
1208: where petf.element_type_id = piv.element_type_id
1209: and upper(piv.name) = upper('Week commencing')
1210: )
1211: and peev2.screen_entry_value > fnd_date.date_to_canonical(hr_gbnicar.uk_tax_yr_end(sysdate) - 5)
1212: -- Retrieve only those entries that main SMP query will retrieve
1213: and exists
1214: ( select 1
1215: from per_all_assignments_f paf,

Line 1223: and peev2.screen_entry_value between fnd_date.date_to_canonical(ppf.effective_start_date)

1219: where peef.assignment_id = paf.assignment_id
1220: and paf.person_id = ppf.person_id
1221: and ppf.person_id = pps.person_id
1222: and ppt.person_type_id = ppf.person_type_id
1223: and peev2.screen_entry_value between fnd_date.date_to_canonical(ppf.effective_start_date)
1224: and fnd_date.date_to_canonical(ppf.effective_end_date)
1225: and peev2.screen_entry_value between fnd_date.date_to_canonical(paf.effective_start_date)
1226: and fnd_date.date_to_canonical(paf.effective_end_date)
1227: and nvl(pps.final_process_date,to_date('31/12/4712','DD/MM/YYYY')) >= sysdate

Line 1224: and fnd_date.date_to_canonical(ppf.effective_end_date)

1220: and paf.person_id = ppf.person_id
1221: and ppf.person_id = pps.person_id
1222: and ppt.person_type_id = ppf.person_type_id
1223: and peev2.screen_entry_value between fnd_date.date_to_canonical(ppf.effective_start_date)
1224: and fnd_date.date_to_canonical(ppf.effective_end_date)
1225: and peev2.screen_entry_value between fnd_date.date_to_canonical(paf.effective_start_date)
1226: and fnd_date.date_to_canonical(paf.effective_end_date)
1227: and nvl(pps.final_process_date,to_date('31/12/4712','DD/MM/YYYY')) >= sysdate
1228: )

Line 1225: and peev2.screen_entry_value between fnd_date.date_to_canonical(paf.effective_start_date)

1221: and ppf.person_id = pps.person_id
1222: and ppt.person_type_id = ppf.person_type_id
1223: and peev2.screen_entry_value between fnd_date.date_to_canonical(ppf.effective_start_date)
1224: and fnd_date.date_to_canonical(ppf.effective_end_date)
1225: and peev2.screen_entry_value between fnd_date.date_to_canonical(paf.effective_start_date)
1226: and fnd_date.date_to_canonical(paf.effective_end_date)
1227: and nvl(pps.final_process_date,to_date('31/12/4712','DD/MM/YYYY')) >= sysdate
1228: )
1229: and peev3.input_value_id =

Line 1226: and fnd_date.date_to_canonical(paf.effective_end_date)

1222: and ppt.person_type_id = ppf.person_type_id
1223: and peev2.screen_entry_value between fnd_date.date_to_canonical(ppf.effective_start_date)
1224: and fnd_date.date_to_canonical(ppf.effective_end_date)
1225: and peev2.screen_entry_value between fnd_date.date_to_canonical(paf.effective_start_date)
1226: and fnd_date.date_to_canonical(paf.effective_end_date)
1227: and nvl(pps.final_process_date,to_date('31/12/4712','DD/MM/YYYY')) >= sysdate
1228: )
1229: and peev3.input_value_id =
1230: (select input_value_id

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

1336: from pay_input_values_f piv
1337: where petf.element_type_id = piv.element_type_id
1338: and upper(piv.name) = upper('Week commencing')
1339: )
1340: and peev2.screen_entry_value > fnd_date.date_to_canonical(hr_gbnicar.uk_tax_yr_end(sysdate) - 5)
1341: -- Retrieve only those entries that main sap query will retrieve
1342: and exists
1343: ( select 1
1344: from per_all_assignments_f paf,

Line 1352: and peev2.screen_entry_value between fnd_date.date_to_canonical(ppf.effective_start_date)

1348: where peef.assignment_id = paf.assignment_id
1349: and paf.person_id = ppf.person_id
1350: and ppf.person_id = pps.person_id
1351: and ppt.person_type_id = ppf.person_type_id
1352: and peev2.screen_entry_value between fnd_date.date_to_canonical(ppf.effective_start_date)
1353: and fnd_date.date_to_canonical(ppf.effective_end_date)
1354: and peev2.screen_entry_value between fnd_date.date_to_canonical(paf.effective_start_date)
1355: and fnd_date.date_to_canonical(paf.effective_end_date)
1356: and nvl(pps.final_process_date,to_date('31/12/4712','DD/MM/YYYY')) >= sysdate

Line 1353: and fnd_date.date_to_canonical(ppf.effective_end_date)

1349: and paf.person_id = ppf.person_id
1350: and ppf.person_id = pps.person_id
1351: and ppt.person_type_id = ppf.person_type_id
1352: and peev2.screen_entry_value between fnd_date.date_to_canonical(ppf.effective_start_date)
1353: and fnd_date.date_to_canonical(ppf.effective_end_date)
1354: and peev2.screen_entry_value between fnd_date.date_to_canonical(paf.effective_start_date)
1355: and fnd_date.date_to_canonical(paf.effective_end_date)
1356: and nvl(pps.final_process_date,to_date('31/12/4712','DD/MM/YYYY')) >= sysdate
1357: )

Line 1354: and peev2.screen_entry_value between fnd_date.date_to_canonical(paf.effective_start_date)

1350: and ppf.person_id = pps.person_id
1351: and ppt.person_type_id = ppf.person_type_id
1352: and peev2.screen_entry_value between fnd_date.date_to_canonical(ppf.effective_start_date)
1353: and fnd_date.date_to_canonical(ppf.effective_end_date)
1354: and peev2.screen_entry_value between fnd_date.date_to_canonical(paf.effective_start_date)
1355: and fnd_date.date_to_canonical(paf.effective_end_date)
1356: and nvl(pps.final_process_date,to_date('31/12/4712','DD/MM/YYYY')) >= sysdate
1357: )
1358: -- Get first row that matches all of the above criteria

Line 1355: and fnd_date.date_to_canonical(paf.effective_end_date)

1351: and ppt.person_type_id = ppf.person_type_id
1352: and peev2.screen_entry_value between fnd_date.date_to_canonical(ppf.effective_start_date)
1353: and fnd_date.date_to_canonical(ppf.effective_end_date)
1354: and peev2.screen_entry_value between fnd_date.date_to_canonical(paf.effective_start_date)
1355: and fnd_date.date_to_canonical(paf.effective_end_date)
1356: and nvl(pps.final_process_date,to_date('31/12/4712','DD/MM/YYYY')) >= sysdate
1357: )
1358: -- Get first row that matches all of the above criteria
1359: and rownum = 1;

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

1452: from pay_input_values_f piv
1453: where petf.element_type_id = piv.element_type_id
1454: and upper(piv.name) = upper('Week commencing')
1455: )
1456: and peev2.screen_entry_value > fnd_date.date_to_canonical(hr_gbnicar.uk_tax_yr_end(sysdate) - 5)
1457: -- Retrieve only those entries that main sppa query will retrieve
1458: and exists
1459: ( select 1
1460: from per_all_assignments_f paf,

Line 1468: and peev2.screen_entry_value between fnd_date.date_to_canonical(ppf.effective_start_date)

1464: where peef.assignment_id = paf.assignment_id
1465: and paf.person_id = ppf.person_id
1466: and ppf.person_id = pps.person_id
1467: and ppt.person_type_id = ppf.person_type_id
1468: and peev2.screen_entry_value between fnd_date.date_to_canonical(ppf.effective_start_date)
1469: and fnd_date.date_to_canonical(ppf.effective_end_date)
1470: and peev2.screen_entry_value between fnd_date.date_to_canonical(paf.effective_start_date)
1471: and fnd_date.date_to_canonical(paf.effective_end_date)
1472: and nvl(pps.final_process_date,to_date('31/12/4712','DD/MM/YYYY')) >= sysdate

Line 1469: and fnd_date.date_to_canonical(ppf.effective_end_date)

1465: and paf.person_id = ppf.person_id
1466: and ppf.person_id = pps.person_id
1467: and ppt.person_type_id = ppf.person_type_id
1468: and peev2.screen_entry_value between fnd_date.date_to_canonical(ppf.effective_start_date)
1469: and fnd_date.date_to_canonical(ppf.effective_end_date)
1470: and peev2.screen_entry_value between fnd_date.date_to_canonical(paf.effective_start_date)
1471: and fnd_date.date_to_canonical(paf.effective_end_date)
1472: and nvl(pps.final_process_date,to_date('31/12/4712','DD/MM/YYYY')) >= sysdate
1473: )

Line 1470: and peev2.screen_entry_value between fnd_date.date_to_canonical(paf.effective_start_date)

1466: and ppf.person_id = pps.person_id
1467: and ppt.person_type_id = ppf.person_type_id
1468: and peev2.screen_entry_value between fnd_date.date_to_canonical(ppf.effective_start_date)
1469: and fnd_date.date_to_canonical(ppf.effective_end_date)
1470: and peev2.screen_entry_value between fnd_date.date_to_canonical(paf.effective_start_date)
1471: and fnd_date.date_to_canonical(paf.effective_end_date)
1472: and nvl(pps.final_process_date,to_date('31/12/4712','DD/MM/YYYY')) >= sysdate
1473: )
1474: -- Get first row that matches all of the above criteria

Line 1471: and fnd_date.date_to_canonical(paf.effective_end_date)

1467: and ppt.person_type_id = ppf.person_type_id
1468: and peev2.screen_entry_value between fnd_date.date_to_canonical(ppf.effective_start_date)
1469: and fnd_date.date_to_canonical(ppf.effective_end_date)
1470: and peev2.screen_entry_value between fnd_date.date_to_canonical(paf.effective_start_date)
1471: and fnd_date.date_to_canonical(paf.effective_end_date)
1472: and nvl(pps.final_process_date,to_date('31/12/4712','DD/MM/YYYY')) >= sysdate
1473: )
1474: -- Get first row that matches all of the above criteria
1475: and rownum = 1;

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

1567: from pay_input_values_f piv
1568: where petf.element_type_id = piv.element_type_id
1569: and upper(piv.name) = upper('Week commencing')
1570: )
1571: and peev2.screen_entry_value > fnd_date.date_to_canonical(hr_gbnicar.uk_tax_yr_end(sysdate) - 5)
1572: -- Retrieve only those entries that main SPPB query will retrieve
1573: and exists
1574: ( select 1
1575: from per_all_assignments_f paf,

Line 1583: and peev2.screen_entry_value between fnd_date.date_to_canonical(ppf.effective_start_date)

1579: where peef.assignment_id = paf.assignment_id
1580: and paf.person_id = ppf.person_id
1581: and ppf.person_id = pps.person_id
1582: and ppt.person_type_id = ppf.person_type_id
1583: and peev2.screen_entry_value between fnd_date.date_to_canonical(ppf.effective_start_date)
1584: and fnd_date.date_to_canonical(ppf.effective_end_date)
1585: and peev2.screen_entry_value between fnd_date.date_to_canonical(paf.effective_start_date)
1586: and fnd_date.date_to_canonical(paf.effective_end_date)
1587: and nvl(pps.final_process_date,to_date('31/12/4712','DD/MM/YYYY')) >= sysdate

Line 1584: and fnd_date.date_to_canonical(ppf.effective_end_date)

1580: and paf.person_id = ppf.person_id
1581: and ppf.person_id = pps.person_id
1582: and ppt.person_type_id = ppf.person_type_id
1583: and peev2.screen_entry_value between fnd_date.date_to_canonical(ppf.effective_start_date)
1584: and fnd_date.date_to_canonical(ppf.effective_end_date)
1585: and peev2.screen_entry_value between fnd_date.date_to_canonical(paf.effective_start_date)
1586: and fnd_date.date_to_canonical(paf.effective_end_date)
1587: and nvl(pps.final_process_date,to_date('31/12/4712','DD/MM/YYYY')) >= sysdate
1588: )

Line 1585: and peev2.screen_entry_value between fnd_date.date_to_canonical(paf.effective_start_date)

1581: and ppf.person_id = pps.person_id
1582: and ppt.person_type_id = ppf.person_type_id
1583: and peev2.screen_entry_value between fnd_date.date_to_canonical(ppf.effective_start_date)
1584: and fnd_date.date_to_canonical(ppf.effective_end_date)
1585: and peev2.screen_entry_value between fnd_date.date_to_canonical(paf.effective_start_date)
1586: and fnd_date.date_to_canonical(paf.effective_end_date)
1587: and nvl(pps.final_process_date,to_date('31/12/4712','DD/MM/YYYY')) >= sysdate
1588: )
1589: -- Get first row that matches all of the above criteria

Line 1586: and fnd_date.date_to_canonical(paf.effective_end_date)

1582: and ppt.person_type_id = ppf.person_type_id
1583: and peev2.screen_entry_value between fnd_date.date_to_canonical(ppf.effective_start_date)
1584: and fnd_date.date_to_canonical(ppf.effective_end_date)
1585: and peev2.screen_entry_value between fnd_date.date_to_canonical(paf.effective_start_date)
1586: and fnd_date.date_to_canonical(paf.effective_end_date)
1587: and nvl(pps.final_process_date,to_date('31/12/4712','DD/MM/YYYY')) >= sysdate
1588: )
1589: -- Get first row that matches all of the above criteria
1590: and rownum = 1;

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

2129: where petf.element_type_id = piv.element_type_id
2130: and petf.element_name = 'Statutory Sick Pay'
2131: and piv.name = 'From')
2132: and peev3.screen_entry_value >
2133: fnd_date.date_to_canonical(hr_gbnicar.uk_tax_yr_end(sysdate))
2134: /* Retrieve only those entries that will be retrieved by main SSP query */
2135: and peev3.element_entry_id in
2136: ( select peef1.element_entry_id
2137: from pay_element_entries_f peef1,

Line 2146: and peev3.screen_entry_value between fnd_date.date_to_canonical(ppf.effective_start_date)

2142: where peef1.assignment_id = paf.assignment_id
2143: and paf.person_id = ppf.person_id
2144: and ppf.person_id = pps.person_id
2145: and ppt.person_type_id = ppf.person_type_id
2146: and peev3.screen_entry_value between fnd_date.date_to_canonical(ppf.effective_start_date)
2147: and fnd_date.date_to_canonical(ppf.effective_end_date)
2148: and peev3.screen_entry_value between fnd_date.date_to_canonical(paf.effective_start_date)
2149: and fnd_date.date_to_canonical(paf.effective_end_date)
2150: and ppt.system_person_type = 'EMP'

Line 2147: and fnd_date.date_to_canonical(ppf.effective_end_date)

2143: and paf.person_id = ppf.person_id
2144: and ppf.person_id = pps.person_id
2145: and ppt.person_type_id = ppf.person_type_id
2146: and peev3.screen_entry_value between fnd_date.date_to_canonical(ppf.effective_start_date)
2147: and fnd_date.date_to_canonical(ppf.effective_end_date)
2148: and peev3.screen_entry_value between fnd_date.date_to_canonical(paf.effective_start_date)
2149: and fnd_date.date_to_canonical(paf.effective_end_date)
2150: and ppt.system_person_type = 'EMP'
2151: and nvl(pps.actual_termination_date,to_date('31/12/4712','DD/MM/YYYY')) >= sysdate

Line 2148: and peev3.screen_entry_value between fnd_date.date_to_canonical(paf.effective_start_date)

2144: and ppf.person_id = pps.person_id
2145: and ppt.person_type_id = ppf.person_type_id
2146: and peev3.screen_entry_value between fnd_date.date_to_canonical(ppf.effective_start_date)
2147: and fnd_date.date_to_canonical(ppf.effective_end_date)
2148: and peev3.screen_entry_value between fnd_date.date_to_canonical(paf.effective_start_date)
2149: and fnd_date.date_to_canonical(paf.effective_end_date)
2150: and ppt.system_person_type = 'EMP'
2151: and nvl(pps.actual_termination_date,to_date('31/12/4712','DD/MM/YYYY')) >= sysdate
2152: )

Line 2149: and fnd_date.date_to_canonical(paf.effective_end_date)

2145: and ppt.person_type_id = ppf.person_type_id
2146: and peev3.screen_entry_value between fnd_date.date_to_canonical(ppf.effective_start_date)
2147: and fnd_date.date_to_canonical(ppf.effective_end_date)
2148: and peev3.screen_entry_value between fnd_date.date_to_canonical(paf.effective_start_date)
2149: and fnd_date.date_to_canonical(paf.effective_end_date)
2150: and ppt.system_person_type = 'EMP'
2151: and nvl(pps.actual_termination_date,to_date('31/12/4712','DD/MM/YYYY')) >= sysdate
2152: )
2153: and rownum = 1);

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

2257: from pay_input_values_f piv
2258: where petf.element_type_id = piv.element_type_id
2259: and upper(piv.name) = upper('Week commencing')
2260: )
2261: and peev2.screen_entry_value > fnd_date.date_to_canonical(hr_gbnicar.uk_tax_yr_end(sysdate) - 5)
2262: -- Retrieve only those entries that main SMP query will retrieve
2263: and exists
2264: ( select 1
2265: from per_all_assignments_f paf,

Line 2273: and peev2.screen_entry_value between fnd_date.date_to_canonical(ppf.effective_start_date)

2269: where peef.assignment_id = paf.assignment_id
2270: and paf.person_id = ppf.person_id
2271: and ppf.person_id = pps.person_id
2272: and ppt.person_type_id = ppf.person_type_id
2273: and peev2.screen_entry_value between fnd_date.date_to_canonical(ppf.effective_start_date)
2274: and fnd_date.date_to_canonical(ppf.effective_end_date)
2275: and peev2.screen_entry_value between fnd_date.date_to_canonical(paf.effective_start_date)
2276: and fnd_date.date_to_canonical(paf.effective_end_date)
2277: and nvl(pps.final_process_date,to_date('31/12/4712','DD/MM/YYYY')) >= sysdate

Line 2274: and fnd_date.date_to_canonical(ppf.effective_end_date)

2270: and paf.person_id = ppf.person_id
2271: and ppf.person_id = pps.person_id
2272: and ppt.person_type_id = ppf.person_type_id
2273: and peev2.screen_entry_value between fnd_date.date_to_canonical(ppf.effective_start_date)
2274: and fnd_date.date_to_canonical(ppf.effective_end_date)
2275: and peev2.screen_entry_value between fnd_date.date_to_canonical(paf.effective_start_date)
2276: and fnd_date.date_to_canonical(paf.effective_end_date)
2277: and nvl(pps.final_process_date,to_date('31/12/4712','DD/MM/YYYY')) >= sysdate
2278: )

Line 2275: and peev2.screen_entry_value between fnd_date.date_to_canonical(paf.effective_start_date)

2271: and ppf.person_id = pps.person_id
2272: and ppt.person_type_id = ppf.person_type_id
2273: and peev2.screen_entry_value between fnd_date.date_to_canonical(ppf.effective_start_date)
2274: and fnd_date.date_to_canonical(ppf.effective_end_date)
2275: and peev2.screen_entry_value between fnd_date.date_to_canonical(paf.effective_start_date)
2276: and fnd_date.date_to_canonical(paf.effective_end_date)
2277: and nvl(pps.final_process_date,to_date('31/12/4712','DD/MM/YYYY')) >= sysdate
2278: )
2279: and peev3.input_value_id =

Line 2276: and fnd_date.date_to_canonical(paf.effective_end_date)

2272: and ppt.person_type_id = ppf.person_type_id
2273: and peev2.screen_entry_value between fnd_date.date_to_canonical(ppf.effective_start_date)
2274: and fnd_date.date_to_canonical(ppf.effective_end_date)
2275: and peev2.screen_entry_value between fnd_date.date_to_canonical(paf.effective_start_date)
2276: and fnd_date.date_to_canonical(paf.effective_end_date)
2277: and nvl(pps.final_process_date,to_date('31/12/4712','DD/MM/YYYY')) >= sysdate
2278: )
2279: and peev3.input_value_id =
2280: (select input_value_id

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

2386: from pay_input_values_f piv
2387: where petf.element_type_id = piv.element_type_id
2388: and upper(piv.name) = upper('Week commencing')
2389: )
2390: and peev2.screen_entry_value > fnd_date.date_to_canonical(hr_gbnicar.uk_tax_yr_end(sysdate) - 5)
2391: -- Retrieve only those entries that main sap query will retrieve
2392: and exists
2393: ( select 1
2394: from per_all_assignments_f paf,

Line 2402: and peev2.screen_entry_value between fnd_date.date_to_canonical(ppf.effective_start_date)

2398: where peef.assignment_id = paf.assignment_id
2399: and paf.person_id = ppf.person_id
2400: and ppf.person_id = pps.person_id
2401: and ppt.person_type_id = ppf.person_type_id
2402: and peev2.screen_entry_value between fnd_date.date_to_canonical(ppf.effective_start_date)
2403: and fnd_date.date_to_canonical(ppf.effective_end_date)
2404: and peev2.screen_entry_value between fnd_date.date_to_canonical(paf.effective_start_date)
2405: and fnd_date.date_to_canonical(paf.effective_end_date)
2406: and nvl(pps.final_process_date,to_date('31/12/4712','DD/MM/YYYY')) >= sysdate

Line 2403: and fnd_date.date_to_canonical(ppf.effective_end_date)

2399: and paf.person_id = ppf.person_id
2400: and ppf.person_id = pps.person_id
2401: and ppt.person_type_id = ppf.person_type_id
2402: and peev2.screen_entry_value between fnd_date.date_to_canonical(ppf.effective_start_date)
2403: and fnd_date.date_to_canonical(ppf.effective_end_date)
2404: and peev2.screen_entry_value between fnd_date.date_to_canonical(paf.effective_start_date)
2405: and fnd_date.date_to_canonical(paf.effective_end_date)
2406: and nvl(pps.final_process_date,to_date('31/12/4712','DD/MM/YYYY')) >= sysdate
2407: )

Line 2404: and peev2.screen_entry_value between fnd_date.date_to_canonical(paf.effective_start_date)

2400: and ppf.person_id = pps.person_id
2401: and ppt.person_type_id = ppf.person_type_id
2402: and peev2.screen_entry_value between fnd_date.date_to_canonical(ppf.effective_start_date)
2403: and fnd_date.date_to_canonical(ppf.effective_end_date)
2404: and peev2.screen_entry_value between fnd_date.date_to_canonical(paf.effective_start_date)
2405: and fnd_date.date_to_canonical(paf.effective_end_date)
2406: and nvl(pps.final_process_date,to_date('31/12/4712','DD/MM/YYYY')) >= sysdate
2407: )
2408: -- Get first row that matches all of the above criteria

Line 2405: and fnd_date.date_to_canonical(paf.effective_end_date)

2401: and ppt.person_type_id = ppf.person_type_id
2402: and peev2.screen_entry_value between fnd_date.date_to_canonical(ppf.effective_start_date)
2403: and fnd_date.date_to_canonical(ppf.effective_end_date)
2404: and peev2.screen_entry_value between fnd_date.date_to_canonical(paf.effective_start_date)
2405: and fnd_date.date_to_canonical(paf.effective_end_date)
2406: and nvl(pps.final_process_date,to_date('31/12/4712','DD/MM/YYYY')) >= sysdate
2407: )
2408: -- Get first row that matches all of the above criteria
2409: and rownum = 1;

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

2502: from pay_input_values_f piv
2503: where petf.element_type_id = piv.element_type_id
2504: and upper(piv.name) = upper('Week commencing')
2505: )
2506: and peev2.screen_entry_value > fnd_date.date_to_canonical(hr_gbnicar.uk_tax_yr_end(sysdate) - 5)
2507: -- Retrieve only those entries that main sppa query will retrieve
2508: and exists
2509: ( select 1
2510: from per_all_assignments_f paf,

Line 2518: and peev2.screen_entry_value between fnd_date.date_to_canonical(ppf.effective_start_date)

2514: where peef.assignment_id = paf.assignment_id
2515: and paf.person_id = ppf.person_id
2516: and ppf.person_id = pps.person_id
2517: and ppt.person_type_id = ppf.person_type_id
2518: and peev2.screen_entry_value between fnd_date.date_to_canonical(ppf.effective_start_date)
2519: and fnd_date.date_to_canonical(ppf.effective_end_date)
2520: and peev2.screen_entry_value between fnd_date.date_to_canonical(paf.effective_start_date)
2521: and fnd_date.date_to_canonical(paf.effective_end_date)
2522: and nvl(pps.final_process_date,to_date('31/12/4712','DD/MM/YYYY')) >= sysdate

Line 2519: and fnd_date.date_to_canonical(ppf.effective_end_date)

2515: and paf.person_id = ppf.person_id
2516: and ppf.person_id = pps.person_id
2517: and ppt.person_type_id = ppf.person_type_id
2518: and peev2.screen_entry_value between fnd_date.date_to_canonical(ppf.effective_start_date)
2519: and fnd_date.date_to_canonical(ppf.effective_end_date)
2520: and peev2.screen_entry_value between fnd_date.date_to_canonical(paf.effective_start_date)
2521: and fnd_date.date_to_canonical(paf.effective_end_date)
2522: and nvl(pps.final_process_date,to_date('31/12/4712','DD/MM/YYYY')) >= sysdate
2523: )

Line 2520: and peev2.screen_entry_value between fnd_date.date_to_canonical(paf.effective_start_date)

2516: and ppf.person_id = pps.person_id
2517: and ppt.person_type_id = ppf.person_type_id
2518: and peev2.screen_entry_value between fnd_date.date_to_canonical(ppf.effective_start_date)
2519: and fnd_date.date_to_canonical(ppf.effective_end_date)
2520: and peev2.screen_entry_value between fnd_date.date_to_canonical(paf.effective_start_date)
2521: and fnd_date.date_to_canonical(paf.effective_end_date)
2522: and nvl(pps.final_process_date,to_date('31/12/4712','DD/MM/YYYY')) >= sysdate
2523: )
2524: -- Get first row that matches all of the above criteria

Line 2521: and fnd_date.date_to_canonical(paf.effective_end_date)

2517: and ppt.person_type_id = ppf.person_type_id
2518: and peev2.screen_entry_value between fnd_date.date_to_canonical(ppf.effective_start_date)
2519: and fnd_date.date_to_canonical(ppf.effective_end_date)
2520: and peev2.screen_entry_value between fnd_date.date_to_canonical(paf.effective_start_date)
2521: and fnd_date.date_to_canonical(paf.effective_end_date)
2522: and nvl(pps.final_process_date,to_date('31/12/4712','DD/MM/YYYY')) >= sysdate
2523: )
2524: -- Get first row that matches all of the above criteria
2525: and rownum = 1;

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

2617: from pay_input_values_f piv
2618: where petf.element_type_id = piv.element_type_id
2619: and upper(piv.name) = upper('Week commencing')
2620: )
2621: and peev2.screen_entry_value > fnd_date.date_to_canonical(hr_gbnicar.uk_tax_yr_end(sysdate) - 5)
2622: -- Retrieve only those entries that main SPPB query will retrieve
2623: and exists
2624: ( select 1
2625: from per_all_assignments_f paf,

Line 2633: and peev2.screen_entry_value between fnd_date.date_to_canonical(ppf.effective_start_date)

2629: where peef.assignment_id = paf.assignment_id
2630: and paf.person_id = ppf.person_id
2631: and ppf.person_id = pps.person_id
2632: and ppt.person_type_id = ppf.person_type_id
2633: and peev2.screen_entry_value between fnd_date.date_to_canonical(ppf.effective_start_date)
2634: and fnd_date.date_to_canonical(ppf.effective_end_date)
2635: and peev2.screen_entry_value between fnd_date.date_to_canonical(paf.effective_start_date)
2636: and fnd_date.date_to_canonical(paf.effective_end_date)
2637: and nvl(pps.final_process_date,to_date('31/12/4712','DD/MM/YYYY')) >= sysdate

Line 2634: and fnd_date.date_to_canonical(ppf.effective_end_date)

2630: and paf.person_id = ppf.person_id
2631: and ppf.person_id = pps.person_id
2632: and ppt.person_type_id = ppf.person_type_id
2633: and peev2.screen_entry_value between fnd_date.date_to_canonical(ppf.effective_start_date)
2634: and fnd_date.date_to_canonical(ppf.effective_end_date)
2635: and peev2.screen_entry_value between fnd_date.date_to_canonical(paf.effective_start_date)
2636: and fnd_date.date_to_canonical(paf.effective_end_date)
2637: and nvl(pps.final_process_date,to_date('31/12/4712','DD/MM/YYYY')) >= sysdate
2638: )

Line 2635: and peev2.screen_entry_value between fnd_date.date_to_canonical(paf.effective_start_date)

2631: and ppf.person_id = pps.person_id
2632: and ppt.person_type_id = ppf.person_type_id
2633: and peev2.screen_entry_value between fnd_date.date_to_canonical(ppf.effective_start_date)
2634: and fnd_date.date_to_canonical(ppf.effective_end_date)
2635: and peev2.screen_entry_value between fnd_date.date_to_canonical(paf.effective_start_date)
2636: and fnd_date.date_to_canonical(paf.effective_end_date)
2637: and nvl(pps.final_process_date,to_date('31/12/4712','DD/MM/YYYY')) >= sysdate
2638: )
2639: -- Get first row that matches all of the above criteria

Line 2636: and fnd_date.date_to_canonical(paf.effective_end_date)

2632: and ppt.person_type_id = ppf.person_type_id
2633: and peev2.screen_entry_value between fnd_date.date_to_canonical(ppf.effective_start_date)
2634: and fnd_date.date_to_canonical(ppf.effective_end_date)
2635: and peev2.screen_entry_value between fnd_date.date_to_canonical(paf.effective_start_date)
2636: and fnd_date.date_to_canonical(paf.effective_end_date)
2637: and nvl(pps.final_process_date,to_date('31/12/4712','DD/MM/YYYY')) >= sysdate
2638: )
2639: -- Get first row that matches all of the above criteria
2640: and rownum = 1;