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.PQP_NL_ABP_FUNCTIONS dependencies on HR_API

Line 761: IF l_eff_date > fnd_date.canonical_to_date(nvl(p_org_information2,fnd_date.date_to_canonical(hr_api.g_eot))) THEN

757: END IF;
758:
759: END LOOP;
760: /*--validate that the end date should be greater than or equal to the eff date
761: IF l_eff_date > fnd_date.canonical_to_date(nvl(p_org_information2,fnd_date.date_to_canonical(hr_api.g_eot))) THEN
762: hr_utility.set_message(8303,'PQP_230099_DT_TO_BEF_END_DT');
763: hr_utility.raise_error;
764: END IF; */
765: --hr_utility.trace_off;

Line 1123: AND fnd_date.canonical_to_date(nvl(p_org_information2_o,fnd_date.date_to_canonical(hr_api.g_eot)))

1119: FROM pay_assignment_actions paa,pay_payroll_actions ppa
1120: WHERE paa.payroll_action_id = ppa.payroll_action_id
1121: AND ppa.date_earned between
1122: fnd_date.canonical_to_date(p_org_information1_o)
1123: AND fnd_date.canonical_to_date(nvl(p_org_information2_o,fnd_date.date_to_canonical(hr_api.g_eot)))
1124: AND paa.assignment_action_id IN
1125: (SELECT assignment_action_id
1126: FROM pay_run_results
1127: WHERE element_type_id IN

Line 1452: /* ELSIF fnd_date.canonical_to_date(nvl(p_org_information2,fnd_date.date_to_canonical(hr_api.g_eot)))

1448: hr_utility.raise_error;
1449: END IF;
1450: END IF;
1451:
1452: /* ELSIF fnd_date.canonical_to_date(nvl(p_org_information2,fnd_date.date_to_canonical(hr_api.g_eot)))
1453: <> fnd_date.canonical_to_date(nvl(p_org_information2_o,fnd_date.date_to_canonical(hr_api.g_eot))) THEN
1454: IF l_eff_date <= l_date_earned OR fnd_date.canonical_to_date(nvl(p_org_information2,fnd_date.date_to_canonical(hr_api.g_eot)))
1455: <= l_date_earned THEN
1456: hr_utility.set_message(8303,'PQP_230102_DT_TO_AFTER_PAY_RUN');

Line 1453: <> fnd_date.canonical_to_date(nvl(p_org_information2_o,fnd_date.date_to_canonical(hr_api.g_eot))) THEN

1449: END IF;
1450: END IF;
1451:
1452: /* ELSIF fnd_date.canonical_to_date(nvl(p_org_information2,fnd_date.date_to_canonical(hr_api.g_eot)))
1453: <> fnd_date.canonical_to_date(nvl(p_org_information2_o,fnd_date.date_to_canonical(hr_api.g_eot))) THEN
1454: IF l_eff_date <= l_date_earned OR fnd_date.canonical_to_date(nvl(p_org_information2,fnd_date.date_to_canonical(hr_api.g_eot)))
1455: <= l_date_earned THEN
1456: hr_utility.set_message(8303,'PQP_230102_DT_TO_AFTER_PAY_RUN');
1457: hr_utility.raise_error;

Line 1454: IF l_eff_date <= l_date_earned OR fnd_date.canonical_to_date(nvl(p_org_information2,fnd_date.date_to_canonical(hr_api.g_eot)))

1450: END IF;
1451:
1452: /* ELSIF fnd_date.canonical_to_date(nvl(p_org_information2,fnd_date.date_to_canonical(hr_api.g_eot)))
1453: <> fnd_date.canonical_to_date(nvl(p_org_information2_o,fnd_date.date_to_canonical(hr_api.g_eot))) THEN
1454: IF l_eff_date <= l_date_earned OR fnd_date.canonical_to_date(nvl(p_org_information2,fnd_date.date_to_canonical(hr_api.g_eot)))
1455: <= l_date_earned THEN
1456: hr_utility.set_message(8303,'PQP_230102_DT_TO_AFTER_PAY_RUN');
1457: hr_utility.raise_error;
1458: ELSIF l_eff_date > fnd_date.canonical_to_date(nvl(p_org_information2,fnd_date.date_to_canonical(hr_api.g_eot))) THEN

Line 1458: ELSIF l_eff_date > fnd_date.canonical_to_date(nvl(p_org_information2,fnd_date.date_to_canonical(hr_api.g_eot))) THEN

1454: IF l_eff_date <= l_date_earned OR fnd_date.canonical_to_date(nvl(p_org_information2,fnd_date.date_to_canonical(hr_api.g_eot)))
1455: <= l_date_earned THEN
1456: hr_utility.set_message(8303,'PQP_230102_DT_TO_AFTER_PAY_RUN');
1457: hr_utility.raise_error;
1458: ELSIF l_eff_date > fnd_date.canonical_to_date(nvl(p_org_information2,fnd_date.date_to_canonical(hr_api.g_eot))) THEN
1459: hr_utility.set_message(8303,'PQP_230099_DT_TO_BEF_END_DT');
1460: hr_utility.raise_error;
1461: END IF;
1462: END IF;

Line 1464: IF (fnd_date.canonical_to_date(nvl(p_org_information2,fnd_date.date_to_canonical(hr_api.g_eot)))

1460: hr_utility.raise_error;
1461: END IF;
1462: END IF;
1463: ELSIF l_allow_update = 1 THEN
1464: IF (fnd_date.canonical_to_date(nvl(p_org_information2,fnd_date.date_to_canonical(hr_api.g_eot)))
1465: <> fnd_date.canonical_to_date(nvl(p_org_information2_o,fnd_date.date_to_canonical(hr_api.g_eot))))
1466: AND (l_eff_date > fnd_date.canonical_to_date(nvl(p_org_information2,fnd_date.date_to_canonical(hr_api.g_eot)))) THEN
1467: hr_utility.set_message(8303,'PQP_230099_DT_TO_BEF_END_DT');
1468: hr_utility.raise_error;

Line 1465: <> fnd_date.canonical_to_date(nvl(p_org_information2_o,fnd_date.date_to_canonical(hr_api.g_eot))))

1461: END IF;
1462: END IF;
1463: ELSIF l_allow_update = 1 THEN
1464: IF (fnd_date.canonical_to_date(nvl(p_org_information2,fnd_date.date_to_canonical(hr_api.g_eot)))
1465: <> fnd_date.canonical_to_date(nvl(p_org_information2_o,fnd_date.date_to_canonical(hr_api.g_eot))))
1466: AND (l_eff_date > fnd_date.canonical_to_date(nvl(p_org_information2,fnd_date.date_to_canonical(hr_api.g_eot)))) THEN
1467: hr_utility.set_message(8303,'PQP_230099_DT_TO_BEF_END_DT');
1468: hr_utility.raise_error;
1469: END IF;

Line 1466: AND (l_eff_date > fnd_date.canonical_to_date(nvl(p_org_information2,fnd_date.date_to_canonical(hr_api.g_eot)))) THEN

1462: END IF;
1463: ELSIF l_allow_update = 1 THEN
1464: IF (fnd_date.canonical_to_date(nvl(p_org_information2,fnd_date.date_to_canonical(hr_api.g_eot)))
1465: <> fnd_date.canonical_to_date(nvl(p_org_information2_o,fnd_date.date_to_canonical(hr_api.g_eot))))
1466: AND (l_eff_date > fnd_date.canonical_to_date(nvl(p_org_information2,fnd_date.date_to_canonical(hr_api.g_eot)))) THEN
1467: hr_utility.set_message(8303,'PQP_230099_DT_TO_BEF_END_DT');
1468: hr_utility.raise_error;
1469: END IF;
1470: END IF; */

Line 1838: AND nvl(effective_end_date,hr_api.g_eot)

1834: (SELECT payroll_id
1835: FROM per_all_assignments_f
1836: WHERE assignment_id = p_assignment_id
1837: AND c_eff_date BETWEEN effective_start_date
1838: AND nvl(effective_end_date,hr_api.g_eot)
1839: )
1840: AND c_eff_date BETWEEN effective_start_date
1841: AND nvl(effective_end_date,hr_api.g_eot)
1842: )

Line 1841: AND nvl(effective_end_date,hr_api.g_eot)

1837: AND c_eff_date BETWEEN effective_start_date
1838: AND nvl(effective_end_date,hr_api.g_eot)
1839: )
1840: AND c_eff_date BETWEEN effective_start_date
1841: AND nvl(effective_end_date,hr_api.g_eot)
1842: )
1843: AND hrl.lookup_type = 'PROC_PERIOD_TYPE'
1844: AND hrl.meaning = period_type;
1845:

Line 1864: fnd_date.date_to_canonical(hr_api.g_eot)))),'YYYY')

1860: AND information_type = 'NL_ABP_PAR_INFO'
1861: AND to_char(trunc(fnd_date.canonical_to_date(aei_information1)),'YYYY')
1862: = c_year
1863: AND to_char(trunc(fnd_date.canonical_to_date(nvl(aei_information2,
1864: fnd_date.date_to_canonical(hr_api.g_eot)))),'YYYY')
1865: = c_year
1866: AND aei_information6 IS NOT NULL;
1867:
1868: --Cursor to find the pension sub category for the ABP Pension Type

Line 2294: IF l_eff_date > fnd_date.canonical_to_date(nvl(p_aei_information2,fnd_date.date_to_canonical(hr_api.g_eot))) THEN

2290: hr_utility.raise_error;
2291: END IF;
2292:
2293: /*--validate that the eff date is lesser or equal to the end date
2294: IF l_eff_date > fnd_date.canonical_to_date(nvl(p_aei_information2,fnd_date.date_to_canonical(hr_api.g_eot))) THEN
2295: hr_utility.set_message(8303,'PQP_230099_DT_TO_BEF_END_DT');
2296: hr_utility.raise_error;
2297: END IF; */
2298:

Line 2340: fnd_date.date_to_canonical(hr_api.g_eot))),'YYYY');

2336: --fetch the current year from the eff date
2337: l_curr_year := to_char(l_eff_date,'YYYY');
2338: l_start_year := to_char(fnd_date.canonical_to_date(p_aei_information1),'YYYY');
2339: l_end_year := to_char(fnd_date.canonical_to_date(nvl(p_aei_information2,
2340: fnd_date.date_to_canonical(hr_api.g_eot))),'YYYY');
2341:
2342: -- Check if the End Date is Less than or equal
2343: -- to the Start Date
2344: IF trunc(to_date(substr(nvl(p_aei_information2,'4712/12/31'),1,10),'YYYY/MM/DD')) <

Line 2460: nvl(p_aei_information2,fnd_date.date_to_canonical(hr_api.g_eot)))) THEN

2456: END IF;
2457:
2458: /*--validate that the eff date is lesser or equal to the end date
2459: IF l_eff_date > trunc(fnd_date.canonical_to_date(
2460: nvl(p_aei_information2,fnd_date.date_to_canonical(hr_api.g_eot)))) THEN
2461: hr_utility.set_message(8303,'PQP_230099_DT_TO_BEF_END_DT');
2462: hr_utility.raise_error;
2463: END IF;*/
2464:

Line 2620: --l_end_year := to_char(fnd_date.canonical_to_date(nvl(p_aei_information2,fnd_date.date_to_canonical(hr_api.g_eot))),'YYYY');

2616:
2617: --fetch the current year from the eff date
2618: --l_curr_year := to_char(l_eff_date,'YYYY');
2619: --l_start_year := to_char(fnd_date.canonical_to_date(p_aei_information1),'YYYY');
2620: --l_end_year := to_char(fnd_date.canonical_to_date(nvl(p_aei_information2,fnd_date.date_to_canonical(hr_api.g_eot))),'YYYY');
2621:
2622: -- Check if the End Date is Less than or equal
2623: -- to the Start Date
2624: IF trunc(to_date(substr(nvl(p_aei_information2,'4712/12/31'),1,10),'YYYY/MM/DD')) <

Line 2796: fnd_date.date_to_canonical(hr_api.g_eot)))),'YYYY')

2792: AND information_type = 'NL_ABP_PAR_INFO'
2793: AND to_char(trunc(fnd_date.canonical_to_date(aei_information1)),'YYYY')
2794: = c_year
2795: AND to_char(trunc(fnd_date.canonical_to_date(nvl(aei_information2,
2796: fnd_date.date_to_canonical(hr_api.g_eot)))),'YYYY')
2797: = c_year
2798: AND aei_information6 IS NOT NULL;
2799:
2800: --cursor to fetch the number of periods per year

Line 2812: AND nvl(effective_end_date,hr_api.g_eot)

2808: (SELECT payroll_id
2809: FROM per_all_assignments_f
2810: WHERE assignment_id = p_assignment_id
2811: AND c_eff_date BETWEEN effective_start_date
2812: AND nvl(effective_end_date,hr_api.g_eot)
2813: )
2814: AND c_eff_date BETWEEN effective_start_date
2815: AND nvl(effective_end_date,hr_api.g_eot)
2816: )

Line 2815: AND nvl(effective_end_date,hr_api.g_eot)

2811: AND c_eff_date BETWEEN effective_start_date
2812: AND nvl(effective_end_date,hr_api.g_eot)
2813: )
2814: AND c_eff_date BETWEEN effective_start_date
2815: AND nvl(effective_end_date,hr_api.g_eot)
2816: )
2817: AND hrl.lookup_type = 'PROC_PERIOD_TYPE'
2818: AND hrl.meaning = period_type;
2819:

Line 2898: AND nvl(fnd_date.canonical_to_date(p_aei_information2_o),hr_api.g_eot)

2894: WHERE paa.payroll_action_id = ppa.payroll_action_id
2895: AND paa.assignment_id = p_assignment_id
2896: AND ppa.date_earned between
2897: fnd_date.canonical_to_date(p_aei_information1_o)
2898: AND nvl(fnd_date.canonical_to_date(p_aei_information2_o),hr_api.g_eot)
2899: AND paa.assignment_action_id IN
2900: (SELECT assignment_action_id
2901: FROM pay_run_results
2902: WHERE element_type_id IN

Line 3339: /* ELSIF fnd_date.canonical_to_date(nvl(p_aei_information2,fnd_date.date_to_canonical(hr_api.g_eot))) <>

3335: hr_utility.raise_error;
3336: END IF;
3337: END IF;
3338:
3339: /* ELSIF fnd_date.canonical_to_date(nvl(p_aei_information2,fnd_date.date_to_canonical(hr_api.g_eot))) <>
3340: fnd_date.canonical_to_date(nvl(p_aei_information2_o,fnd_date.date_to_canonical(hr_api.g_eot))) THEN
3341: IF l_eff_date <= l_date_earned OR fnd_date.canonical_to_date(nvl(p_aei_information2,fnd_date.date_to_canonical(hr_api.g_eot)))
3342: <= l_date_earned THEN
3343: hr_utility.set_message(8303,'PQP_230102_DT_TO_AFTER_PAY_RUN');

Line 3340: fnd_date.canonical_to_date(nvl(p_aei_information2_o,fnd_date.date_to_canonical(hr_api.g_eot))) THEN

3336: END IF;
3337: END IF;
3338:
3339: /* ELSIF fnd_date.canonical_to_date(nvl(p_aei_information2,fnd_date.date_to_canonical(hr_api.g_eot))) <>
3340: fnd_date.canonical_to_date(nvl(p_aei_information2_o,fnd_date.date_to_canonical(hr_api.g_eot))) THEN
3341: IF l_eff_date <= l_date_earned OR fnd_date.canonical_to_date(nvl(p_aei_information2,fnd_date.date_to_canonical(hr_api.g_eot)))
3342: <= l_date_earned THEN
3343: hr_utility.set_message(8303,'PQP_230102_DT_TO_AFTER_PAY_RUN');
3344: hr_utility.raise_error;

Line 3341: IF l_eff_date <= l_date_earned OR fnd_date.canonical_to_date(nvl(p_aei_information2,fnd_date.date_to_canonical(hr_api.g_eot)))

3337: END IF;
3338:
3339: /* ELSIF fnd_date.canonical_to_date(nvl(p_aei_information2,fnd_date.date_to_canonical(hr_api.g_eot))) <>
3340: fnd_date.canonical_to_date(nvl(p_aei_information2_o,fnd_date.date_to_canonical(hr_api.g_eot))) THEN
3341: IF l_eff_date <= l_date_earned OR fnd_date.canonical_to_date(nvl(p_aei_information2,fnd_date.date_to_canonical(hr_api.g_eot)))
3342: <= l_date_earned THEN
3343: hr_utility.set_message(8303,'PQP_230102_DT_TO_AFTER_PAY_RUN');
3344: hr_utility.raise_error;
3345: ELSIF l_eff_date > fnd_date.canonical_to_date(nvl(p_aei_information2,fnd_date.date_to_canonical(hr_api.g_eot))) THEN

Line 3345: ELSIF l_eff_date > fnd_date.canonical_to_date(nvl(p_aei_information2,fnd_date.date_to_canonical(hr_api.g_eot))) THEN

3341: IF l_eff_date <= l_date_earned OR fnd_date.canonical_to_date(nvl(p_aei_information2,fnd_date.date_to_canonical(hr_api.g_eot)))
3342: <= l_date_earned THEN
3343: hr_utility.set_message(8303,'PQP_230102_DT_TO_AFTER_PAY_RUN');
3344: hr_utility.raise_error;
3345: ELSIF l_eff_date > fnd_date.canonical_to_date(nvl(p_aei_information2,fnd_date.date_to_canonical(hr_api.g_eot))) THEN
3346: hr_utility.set_message(8303,'PQP_230099_DT_TO_BEF_END_DT');
3347: hr_utility.raise_error;
3348: END IF;
3349: END IF;

Line 3352: IF fnd_date.canonical_to_date(nvl(p_aei_information2_o,fnd_date.date_to_canonical(hr_api.g_eot))) <>

3348: END IF;
3349: END IF;
3350: --else update is allowed
3351: ELSE
3352: IF fnd_date.canonical_to_date(nvl(p_aei_information2_o,fnd_date.date_to_canonical(hr_api.g_eot))) <>
3353: fnd_date.canonical_to_date(nvl(p_aei_information2,fnd_date.date_to_canonical(hr_api.g_eot))) THEN
3354: IF l_eff_date > fnd_date.canonical_to_date(nvl(p_aei_information2,fnd_date.date_to_canonical(hr_api.g_eot))) THEN
3355: hr_utility.set_message(8303,'PQP_230099_DT_TO_BEF_END_DT');
3356: hr_utility.raise_error;

Line 3353: fnd_date.canonical_to_date(nvl(p_aei_information2,fnd_date.date_to_canonical(hr_api.g_eot))) THEN

3349: END IF;
3350: --else update is allowed
3351: ELSE
3352: IF fnd_date.canonical_to_date(nvl(p_aei_information2_o,fnd_date.date_to_canonical(hr_api.g_eot))) <>
3353: fnd_date.canonical_to_date(nvl(p_aei_information2,fnd_date.date_to_canonical(hr_api.g_eot))) THEN
3354: IF l_eff_date > fnd_date.canonical_to_date(nvl(p_aei_information2,fnd_date.date_to_canonical(hr_api.g_eot))) THEN
3355: hr_utility.set_message(8303,'PQP_230099_DT_TO_BEF_END_DT');
3356: hr_utility.raise_error;
3357: END IF;

Line 3354: IF l_eff_date > fnd_date.canonical_to_date(nvl(p_aei_information2,fnd_date.date_to_canonical(hr_api.g_eot))) THEN

3350: --else update is allowed
3351: ELSE
3352: IF fnd_date.canonical_to_date(nvl(p_aei_information2_o,fnd_date.date_to_canonical(hr_api.g_eot))) <>
3353: fnd_date.canonical_to_date(nvl(p_aei_information2,fnd_date.date_to_canonical(hr_api.g_eot))) THEN
3354: IF l_eff_date > fnd_date.canonical_to_date(nvl(p_aei_information2,fnd_date.date_to_canonical(hr_api.g_eot))) THEN
3355: hr_utility.set_message(8303,'PQP_230099_DT_TO_BEF_END_DT');
3356: hr_utility.raise_error;
3357: END IF;
3358: END IF;

Line 3409: fnd_date.date_to_canonical(hr_api.g_eot))),'YYYY');

3405: --fetch the current year from the eff date
3406: l_curr_year := to_char(l_eff_date,'YYYY');
3407: l_start_year := to_char(fnd_date.canonical_to_date(p_aei_information1),'YYYY');
3408: l_end_year := to_char(fnd_date.canonical_to_date(nvl(p_aei_information2,
3409: fnd_date.date_to_canonical(hr_api.g_eot))),'YYYY');
3410:
3411: -- Check if the End Date is Less than or equal
3412: -- to the Start Date
3413: IF trunc(to_date(substr(nvl(p_aei_information2,'4712/12/31'),1,10),'YYYY/MM/DD')) <

Line 4190: AND trunc(nvl(fnd_date.canonical_to_date(aei_information2),hr_api.g_eot));

4186: WHERE assignment_id = p_assignment_id
4187: AND information_type = 'NL_ABP_PAR_INFO'
4188: AND aei_information_category = 'NL_ABP_PAR_INFO'
4189: AND p_date_earned BETWEEN trunc(fnd_date.canonical_to_date(aei_information1))
4190: AND trunc(nvl(fnd_date.canonical_to_date(aei_information2),hr_api.g_eot));
4191:
4192: BEGIN
4193:
4194: --if the attribute is stored in the participation EIT then

Line 4437: AND fnd_date.canonical_to_date(nvl(aei_information2,fnd_date.date_to_canonical(hr_api.g_eot)))

4433: CURSOR c_get_part_st_age IS
4434: SELECT aei_information3
4435: FROM per_assignment_extra_info
4436: WHERE p_date_earned between fnd_date.canonical_to_date(aei_information1)
4437: AND fnd_date.canonical_to_date(nvl(aei_information2,fnd_date.date_to_canonical(hr_api.g_eot)))
4438: AND assignment_id = p_assignment_id
4439: AND aei_information_category = 'NL_ABP_PAR_INFO';
4440:
4441:

Line 4543: fnd_date.date_to_canonical(hr_api.g_eot)));

4539: AND NVL(hoi.org_information7,'N') = 'Y'
4540: AND hoi.organization_id = p_hr_org_org_id
4541: AND p_date_earned BETWEEN fnd_date.canonical_to_date(hoi.org_information1)
4542: AND fnd_date.canonical_to_date(NVL(hoi.org_information2,
4543: fnd_date.date_to_canonical(hr_api.g_eot)));
4544: --
4545: -- Cursor to find the validity of a PT at the Legal ER Level
4546: -- Note that this qry considers the "Valid" and
4547: -- "Applicable to all EE" flags

Line 4562: fnd_date.date_to_canonical(hr_api.g_eot)))

4558: AND NVL(hoi.org_information7,'N') = 'Y'
4559: AND hoi.organization_id = p_le_org_id
4560: AND p_date_earned BETWEEN fnd_date.canonical_to_date(hoi.org_information1)
4561: AND fnd_date.canonical_to_date(NVL(hoi.org_information2,
4562: fnd_date.date_to_canonical(hr_api.g_eot)))
4563: AND NOT EXISTS ( SELECT 1
4564: FROM hr_organization_information hoi
4565: WHERE hoi.org_information_context = 'PQP_NL_ABP_PT'
4566: AND hoi.org_information3 = TO_CHAR(p_pt_id)

Line 4572: fnd_date.date_to_canonical(hr_api.g_eot))));

4568: OR NVL(hoi.org_information7,'N')= 'N')
4569: AND hoi.organization_id = p_hr_org_org_id
4570: AND p_date_earned BETWEEN fnd_date.canonical_to_date(hoi.org_information1)
4571: AND fnd_date.canonical_to_date(NVL(hoi.org_information2,
4572: fnd_date.date_to_canonical(hr_api.g_eot))));
4573: l_pt_valid NUMBER;
4574: l_ret_val NUMBER;
4575:
4576: BEGIN

Line 4658: fnd_date.date_to_canonical(hr_api.g_eot)));

4654: AND hoi.org_information6 = 'Y'
4655: AND hoi.organization_id = c_org_id
4656: AND p_date_earned BETWEEN fnd_date.canonical_to_date(hoi.org_information1)
4657: AND fnd_date.canonical_to_date(NVL(hoi.org_information2,
4658: fnd_date.date_to_canonical(hr_api.g_eot)));
4659:
4660: --
4661: -- Cursor to find the org id,payroll and legal ER from the assignment
4662: --

Line 4951: fnd_date.date_to_canonical(hr_api.g_eot)));

4947: AND paei.assignment_id = p_assignment_id
4948: AND fnd_number.canonical_to_number(NVL(aei_information3,-1)) = c_pty_id
4949: AND c_date_earned between fnd_date.canonical_to_date(paei.aei_information1)
4950: AND fnd_date.canonical_to_date(NVL(paei.aei_information2,
4951: fnd_date.date_to_canonical(hr_api.g_eot)));
4952:
4953: --
4954: -- Cursor to find the contrib% for a given org id
4955: -- Note that this fetches the values for the information

Line 4972: fnd_date.date_to_canonical(hr_api.g_eot)));

4968: AND NVL(hoi.org_information7,'Y') = 'Y'
4969: AND hoi.organization_id = c_org_id
4970: AND c_date_earned BETWEEN fnd_date.canonical_to_date(hoi.org_information1)
4971: AND fnd_date.canonical_to_date(NVL(hoi.org_information2,
4972: fnd_date.date_to_canonical(hr_api.g_eot)));
4973:
4974: CURSOR c_pt_dtls (c_pty_id IN NUMBER
4975: ,c_date_earned IN DATE) IS
4976: SELECT NVL(pty.ee_age_contribution,'N')

Line 5558: and nvl(date_to,hr_api.g_eot);

5554: select ORG_STRUCTURE_VERSION_ID
5555: from per_org_structure_versions_v
5556: where organization_structure_id = c_hierarchy_id
5557: and p_date_earned between date_from
5558: and nvl(date_to,hr_api.g_eot);
5559:
5560: --Cursor to find the valid version id for a particular business group
5561: CURSOR c_find_ver_frm_bg Is
5562: select ORG_STRUCTURE_VERSION_ID

Line 5566: and nvl( date_to,hr_api.g_eot);

5562: select ORG_STRUCTURE_VERSION_ID
5563: from per_org_structure_versions_v
5564: where business_group_id = p_business_group_id
5565: and p_date_earned between date_from
5566: and nvl( date_to,hr_api.g_eot);
5567:
5568: --Cursor to find the parent id from the org id
5569: CURSOR c_find_parent_id(c_org_id in number
5570: ,c_version_id in number) IS

Line 5607: and fnd_date.canonical_to_date(nvl(paei.aei_information2,fnd_date.date_to_canonical(hr_api.g_eot)));

5603: and paei.aei_information_category = 'NL_ABP_PI'
5604: and paei.aei_information3 = to_char(p_pension_type_id)
5605: and paei.assignment_id = p_assignment_id
5606: and p_date_earned between fnd_date.canonical_to_date(paei.aei_information1)
5607: and fnd_date.canonical_to_date(nvl(paei.aei_information2,fnd_date.date_to_canonical(hr_api.g_eot)));
5608:
5609: --Cursor to find the participation start date from org level information
5610: CURSOR c_get_org_info(c_org_id in hr_organization_information.organization_id%type) Is
5611: SELECT fnd_date.canonical_to_date(hoi.org_information1)

Line 5619: and fnd_date.canonical_to_date(nvl(hoi.org_information2,fnd_date.date_to_canonical(hr_api.g_eot)));

5615: and hoi.org_information6 = 'Y'
5616: and NVL(hoi.org_information7,'Y') = 'Y'
5617: and hoi.organization_id = c_org_id
5618: and p_date_earned between fnd_date.canonical_to_date(hoi.org_information1)
5619: and fnd_date.canonical_to_date(nvl(hoi.org_information2,fnd_date.date_to_canonical(hr_api.g_eot)));
5620:
5621: BEGIN
5622: -- first check to see if any row exists at the ASG attribute level
5623: OPEN c_get_valid_asg_info;

Line 5635: p_start_date := hr_api.g_eot;

5631: IF c_get_asg_info%FOUND THEN
5632: l_ret_value := 0;
5633: CLOSE c_get_asg_info;
5634: ELSE
5635: p_start_date := hr_api.g_eot;
5636: l_ret_value := 1;
5637: CLOSE c_get_asg_info;
5638: END IF;
5639: ELSE -- no row at ASG level on this date,so search up the org hierarchy

Line 5766: and nvl(date_to,hr_api.g_eot);

5762: select ORG_STRUCTURE_VERSION_ID
5763: from per_org_structure_versions_v
5764: where organization_structure_id = c_hierarchy_id
5765: and p_date_earned between date_from
5766: and nvl(date_to,hr_api.g_eot);
5767:
5768: --Cursor to find the valid version id for a particular business group
5769: CURSOR c_find_ver_frm_bg(c_bgid in number) Is
5770: select ORG_STRUCTURE_VERSION_ID

Line 5774: and nvl( date_to,hr_api.g_eot);

5770: select ORG_STRUCTURE_VERSION_ID
5771: from per_org_structure_versions_v
5772: where business_group_id = c_bgid
5773: and p_date_earned between date_from
5774: and nvl( date_to,hr_api.g_eot);
5775:
5776: --Cursor to find the parent id from the org id
5777: CURSOR c_find_parent_id(c_org_id in number
5778: ,c_version_id in number

Line 6475: and nvl(date_to,hr_api.g_eot);

6471: select ORG_STRUCTURE_VERSION_ID
6472: from per_org_structure_versions_v
6473: where organization_structure_id = c_hierarchy_id
6474: and p_date_earned between date_from
6475: and nvl(date_to,hr_api.g_eot);
6476:
6477: --Cursor to find the valid version id for a particular business group
6478: CURSOR c_find_ver_frm_bg Is
6479: select ORG_STRUCTURE_VERSION_ID

Line 6483: and nvl( date_to,hr_api.g_eot);

6479: select ORG_STRUCTURE_VERSION_ID
6480: from per_org_structure_versions_v
6481: where business_group_id = p_business_group_id
6482: and p_date_earned between date_from
6483: and nvl( date_to,hr_api.g_eot);
6484:
6485: --Cursor to find the parent id from the org id
6486: CURSOR c_find_parent_id(c_org_id in number
6487: ,c_version_id in number) IS