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 FND_DATE

Line 633: IF fnd_date.canonical_to_date(p_org_information1)

629: END LOOP;
630:
631: -- if the start/end date is not between the least start date and
632: -- greatest end date,of all element EIT rows of the PT, raise an error
633: IF fnd_date.canonical_to_date(p_org_information1)
634: < l_min_schm_st
635: OR fnd_date.canonical_to_date(nvl(p_org_information2,'4712/12/31'))
636: > l_max_schm_end THEN
637: hr_utility.set_message(8303,'PQP_230070_INV_SCHM_DATES');

Line 635: OR fnd_date.canonical_to_date(nvl(p_org_information2,'4712/12/31'))

631: -- if the start/end date is not between the least start date and
632: -- greatest end date,of all element EIT rows of the PT, raise an error
633: IF fnd_date.canonical_to_date(p_org_information1)
634: < l_min_schm_st
635: OR fnd_date.canonical_to_date(nvl(p_org_information2,'4712/12/31'))
636: > l_max_schm_end THEN
637: hr_utility.set_message(8303,'PQP_230070_INV_SCHM_DATES');
638: hr_utility.raise_error;
639: END IF;

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 1122: fnd_date.canonical_to_date(p_org_information1_o)

1118: SELECT paa.assignment_id,ppa.date_earned
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

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 1251: IF fnd_date.canonical_to_date(p_org_information1)

1247: END LOOP;
1248:
1249: -- if the start/end date is not between the least start date and
1250: -- greatest end date,of all element EIT rows of the PT, raise an error
1251: IF fnd_date.canonical_to_date(p_org_information1)
1252: < l_min_schm_st
1253: OR fnd_date.canonical_to_date(nvl(p_org_information2,'4712/12/31'))
1254: > l_max_schm_end THEN
1255: hr_utility.set_message(8303,'PQP_230070_INV_SCHM_DATES');

Line 1253: OR fnd_date.canonical_to_date(nvl(p_org_information2,'4712/12/31'))

1249: -- if the start/end date is not between the least start date and
1250: -- greatest end date,of all element EIT rows of the PT, raise an error
1251: IF fnd_date.canonical_to_date(p_org_information1)
1252: < l_min_schm_st
1253: OR fnd_date.canonical_to_date(nvl(p_org_information2,'4712/12/31'))
1254: > l_max_schm_end THEN
1255: hr_utility.set_message(8303,'PQP_230070_INV_SCHM_DATES');
1256: hr_utility.raise_error;
1257: END IF;

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 1861: AND to_char(trunc(fnd_date.canonical_to_date(aei_information1)),'YYYY')

1857: WHERE assignment_id = p_assignment_id
1858: AND assignment_extra_info_id <> p_assignment_extra_info_id
1859: AND aei_information_category = 'NL_ABP_PAR_INFO'
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

Line 1863: AND to_char(trunc(fnd_date.canonical_to_date(nvl(aei_information2,

1859: AND aei_information_category = 'NL_ABP_PAR_INFO'
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:

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 2060: IF fnd_date.canonical_to_date(p_aei_information1)

2056: END LOOP;
2057:
2058: -- if the start/end date is not between the least start date and
2059: -- greatest end date,of all element EIT rows of the PT, raise an error
2060: IF fnd_date.canonical_to_date(p_aei_information1)
2061: < l_min_schm_st
2062: OR fnd_date.canonical_to_date(nvl(p_aei_information2,'4712/12/31'))
2063: > l_max_schm_end THEN
2064: hr_utility.set_message(8303,'PQP_230070_INV_SCHM_DATES');

Line 2062: OR fnd_date.canonical_to_date(nvl(p_aei_information2,'4712/12/31'))

2058: -- if the start/end date is not between the least start date and
2059: -- greatest end date,of all element EIT rows of the PT, raise an error
2060: IF fnd_date.canonical_to_date(p_aei_information1)
2061: < l_min_schm_st
2062: OR fnd_date.canonical_to_date(nvl(p_aei_information2,'4712/12/31'))
2063: > l_max_schm_end THEN
2064: hr_utility.set_message(8303,'PQP_230070_INV_SCHM_DATES');
2065: hr_utility.raise_error;
2066: END IF;

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 2303: (p_effective_date => fnd_date.canonical_to_date(to_char(TRUNC(

2299: --
2300: -- Derive the next abp reporting date
2301: --
2302: l_ret_val := get_reporting_date
2303: (p_effective_date => fnd_date.canonical_to_date(to_char(TRUNC(
2304: fnd_date.canonical_to_date(p_aei_information1)),'YYYY/MM')||'/01')
2305: ,p_assignment_id => fnd_number.canonical_to_number(p_assignment_id)
2306: ,p_person_id => l_person_id
2307: ,p_reporting_date => l_abp_rep_date );

Line 2304: fnd_date.canonical_to_date(p_aei_information1)),'YYYY/MM')||'/01')

2300: -- Derive the next abp reporting date
2301: --
2302: l_ret_val := get_reporting_date
2303: (p_effective_date => fnd_date.canonical_to_date(to_char(TRUNC(
2304: fnd_date.canonical_to_date(p_aei_information1)),'YYYY/MM')||'/01')
2305: ,p_assignment_id => fnd_number.canonical_to_number(p_assignment_id)
2306: ,p_person_id => l_person_id
2307: ,p_reporting_date => l_abp_rep_date );
2308:

Line 2310: l_abp_rep_date := fnd_date.canonical_to_date(p_aei_information22);

2306: ,p_person_id => l_person_id
2307: ,p_reporting_date => l_abp_rep_date );
2308:
2309: IF p_aei_information22 IS NOT NULL THEN
2310: l_abp_rep_date := fnd_date.canonical_to_date(p_aei_information22);
2311: ELSE
2312: l_abp_rep_date := greatest(l_abp_rep_date,fnd_date.canonical_to_date(p_aei_information1));
2313: END IF;
2314:

Line 2312: l_abp_rep_date := greatest(l_abp_rep_date,fnd_date.canonical_to_date(p_aei_information1));

2308:
2309: IF p_aei_information22 IS NOT NULL THEN
2310: l_abp_rep_date := fnd_date.canonical_to_date(p_aei_information22);
2311: ELSE
2312: l_abp_rep_date := greatest(l_abp_rep_date,fnd_date.canonical_to_date(p_aei_information1));
2313: END IF;
2314:
2315: --call the procedure to insert rows into the ben_ext_chg_evt_log table
2316: pqp_nl_ext_functions.create_asg_info_ins_chg_evt

Line 2338: l_start_year := to_char(fnd_date.canonical_to_date(p_aei_information1),'YYYY');

2334: ELSIF p_information_type = 'NL_ABP_PAR_INFO' THEN
2335:
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

Line 2339: l_end_year := to_char(fnd_date.canonical_to_date(nvl(p_aei_information2,

2335:
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

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 2459: IF l_eff_date > trunc(fnd_date.canonical_to_date(

2455: hr_utility.raise_error;
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;*/

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 2487: l_min_sal_start := fnd_date.canonical_to_date(temp_rec.aei_information1);

2483: l_counter := 0;
2484: FOR temp_rec IN cur_get_sal_rows(l_curr_year)
2485: LOOP
2486: IF l_counter = 0 THEN
2487: l_min_sal_start := fnd_date.canonical_to_date(temp_rec.aei_information1);
2488: l_max_sal_end := fnd_date.canonical_to_date(temp_rec.aei_information2);
2489: ELSE
2490: IF trunc(fnd_date.canonical_to_date(temp_rec.aei_information1))
2491: < l_min_sal_start THEN

Line 2488: l_max_sal_end := fnd_date.canonical_to_date(temp_rec.aei_information2);

2484: FOR temp_rec IN cur_get_sal_rows(l_curr_year)
2485: LOOP
2486: IF l_counter = 0 THEN
2487: l_min_sal_start := fnd_date.canonical_to_date(temp_rec.aei_information1);
2488: l_max_sal_end := fnd_date.canonical_to_date(temp_rec.aei_information2);
2489: ELSE
2490: IF trunc(fnd_date.canonical_to_date(temp_rec.aei_information1))
2491: < l_min_sal_start THEN
2492: l_min_sal_start := trunc(fnd_date.canonical_to_date(temp_rec.aei_information1));

Line 2490: IF trunc(fnd_date.canonical_to_date(temp_rec.aei_information1))

2486: IF l_counter = 0 THEN
2487: l_min_sal_start := fnd_date.canonical_to_date(temp_rec.aei_information1);
2488: l_max_sal_end := fnd_date.canonical_to_date(temp_rec.aei_information2);
2489: ELSE
2490: IF trunc(fnd_date.canonical_to_date(temp_rec.aei_information1))
2491: < l_min_sal_start THEN
2492: l_min_sal_start := trunc(fnd_date.canonical_to_date(temp_rec.aei_information1));
2493: END IF;
2494: IF trunc(fnd_date.canonical_to_date(p_aei_information2))

Line 2492: l_min_sal_start := trunc(fnd_date.canonical_to_date(temp_rec.aei_information1));

2488: l_max_sal_end := fnd_date.canonical_to_date(temp_rec.aei_information2);
2489: ELSE
2490: IF trunc(fnd_date.canonical_to_date(temp_rec.aei_information1))
2491: < l_min_sal_start THEN
2492: l_min_sal_start := trunc(fnd_date.canonical_to_date(temp_rec.aei_information1));
2493: END IF;
2494: IF trunc(fnd_date.canonical_to_date(p_aei_information2))
2495: > l_max_sal_end THEN
2496: l_max_sal_end := trunc(fnd_date.canonical_to_date(temp_rec.aei_information2));

Line 2494: IF trunc(fnd_date.canonical_to_date(p_aei_information2))

2490: IF trunc(fnd_date.canonical_to_date(temp_rec.aei_information1))
2491: < l_min_sal_start THEN
2492: l_min_sal_start := trunc(fnd_date.canonical_to_date(temp_rec.aei_information1));
2493: END IF;
2494: IF trunc(fnd_date.canonical_to_date(p_aei_information2))
2495: > l_max_sal_end THEN
2496: l_max_sal_end := trunc(fnd_date.canonical_to_date(temp_rec.aei_information2));
2497: END IF;
2498: END IF;

Line 2496: l_max_sal_end := trunc(fnd_date.canonical_to_date(temp_rec.aei_information2));

2492: l_min_sal_start := trunc(fnd_date.canonical_to_date(temp_rec.aei_information1));
2493: END IF;
2494: IF trunc(fnd_date.canonical_to_date(p_aei_information2))
2495: > l_max_sal_end THEN
2496: l_max_sal_end := trunc(fnd_date.canonical_to_date(temp_rec.aei_information2));
2497: END IF;
2498: END IF;
2499: l_counter := l_counter + 1;
2500: END LOOP;

Line 2505: IF trunc(fnd_date.canonical_to_date(p_aei_information1))

2501:
2502: -- Check to see if the records are in continuous order
2503: -- and there are no gaps
2504: IF l_min_sal_start IS NOT NULL AND l_max_sal_end IS NOT NULL THEN
2505: IF trunc(fnd_date.canonical_to_date(p_aei_information1))
2506: > l_max_sal_end + 1 THEN
2507: hr_utility.set_message(8303, 'PQP_230157_GAP_IN_SAL_ROW');
2508: hr_utility.raise_error;
2509: ELSIF trunc(fnd_date.canonical_to_date(p_aei_information2))

Line 2509: ELSIF trunc(fnd_date.canonical_to_date(p_aei_information2))

2505: IF trunc(fnd_date.canonical_to_date(p_aei_information1))
2506: > l_max_sal_end + 1 THEN
2507: hr_utility.set_message(8303, 'PQP_230157_GAP_IN_SAL_ROW');
2508: hr_utility.raise_error;
2509: ELSIF trunc(fnd_date.canonical_to_date(p_aei_information2))
2510: < l_min_sal_start - 1 THEN
2511: hr_utility.set_message(8303, 'PQP_230157_GAP_IN_SAL_ROW');
2512: hr_utility.raise_error;
2513: END IF;

Line 2516: IF nvl(l_min_sal_start,fnd_date.canonical_to_date(p_aei_information1))

2512: hr_utility.raise_error;
2513: END IF;
2514: END IF;
2515:
2516: IF nvl(l_min_sal_start,fnd_date.canonical_to_date(p_aei_information1))
2517: > fnd_date.canonical_to_date(p_aei_information1) THEN
2518: l_min_sal_start := fnd_date.canonical_to_date(p_aei_information1);
2519: END IF;
2520:

Line 2517: > fnd_date.canonical_to_date(p_aei_information1) THEN

2513: END IF;
2514: END IF;
2515:
2516: IF nvl(l_min_sal_start,fnd_date.canonical_to_date(p_aei_information1))
2517: > fnd_date.canonical_to_date(p_aei_information1) THEN
2518: l_min_sal_start := fnd_date.canonical_to_date(p_aei_information1);
2519: END IF;
2520:
2521: --verify that the minimum date is correct date of the year

Line 2518: l_min_sal_start := fnd_date.canonical_to_date(p_aei_information1);

2514: END IF;
2515:
2516: IF nvl(l_min_sal_start,fnd_date.canonical_to_date(p_aei_information1))
2517: > fnd_date.canonical_to_date(p_aei_information1) THEN
2518: l_min_sal_start := fnd_date.canonical_to_date(p_aei_information1);
2519: END IF;
2520:
2521: --verify that the minimum date is correct date of the year
2522: IF (nvl(l_min_sal_start,fnd_date.canonical_to_date(p_aei_information1))

Line 2522: IF (nvl(l_min_sal_start,fnd_date.canonical_to_date(p_aei_information1))

2518: l_min_sal_start := fnd_date.canonical_to_date(p_aei_information1);
2519: END IF;
2520:
2521: --verify that the minimum date is correct date of the year
2522: IF (nvl(l_min_sal_start,fnd_date.canonical_to_date(p_aei_information1))
2523: <> get_valid_start_date(p_assignment_id,l_eff_date,l_error_status,l_error_message)) THEN
2524: hr_utility.set_message(8303,'PQP_230158_ST_DT_JAN_01');
2525: hr_utility.raise_error;
2526: Else

Line 2539: (p_effective_date => fnd_date.canonical_to_date(to_char(TRUNC(

2535: --
2536: -- Derive the next abp reporting date
2537: --
2538: l_ret_val := get_reporting_date
2539: (p_effective_date => fnd_date.canonical_to_date(to_char(TRUNC(
2540: fnd_date.canonical_to_date(p_aei_information1)),'YYYY/MM')||'/01')
2541: ,p_assignment_id => fnd_number.canonical_to_number(p_assignment_id)
2542: ,p_person_id => l_person_id
2543: ,p_reporting_date => l_abp_rep_date );

Line 2540: fnd_date.canonical_to_date(p_aei_information1)),'YYYY/MM')||'/01')

2536: -- Derive the next abp reporting date
2537: --
2538: l_ret_val := get_reporting_date
2539: (p_effective_date => fnd_date.canonical_to_date(to_char(TRUNC(
2540: fnd_date.canonical_to_date(p_aei_information1)),'YYYY/MM')||'/01')
2541: ,p_assignment_id => fnd_number.canonical_to_number(p_assignment_id)
2542: ,p_person_id => l_person_id
2543: ,p_reporting_date => l_abp_rep_date );
2544:

Line 2619: --l_start_year := to_char(fnd_date.canonical_to_date(p_aei_information1),'YYYY');

2615: ELSIF p_information_type = 'NL_ADDL_CALC' THEN
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

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 2793: AND to_char(trunc(fnd_date.canonical_to_date(aei_information1)),'YYYY')

2789: WHERE assignment_id = p_assignment_id
2790: AND assignment_extra_info_id <> p_assignment_extra_info_id
2791: AND aei_information_category = 'NL_ABP_PAR_INFO'
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

Line 2795: AND to_char(trunc(fnd_date.canonical_to_date(nvl(aei_information2,

2791: AND aei_information_category = 'NL_ABP_PAR_INFO'
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:

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 2897: fnd_date.canonical_to_date(p_aei_information1_o)

2893: FROM pay_assignment_actions paa,pay_payroll_actions ppa
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

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 3045: IF fnd_date.canonical_to_date(p_aei_information1)

3041: END LOOP;
3042:
3043: -- if the start/end date is not between the least start date and
3044: -- greatest end date,of all element EIT rows of the PT, raise an error
3045: IF fnd_date.canonical_to_date(p_aei_information1)
3046: < l_min_schm_st
3047: OR fnd_date.canonical_to_date(nvl(p_aei_information2,'4712/12/31'))
3048: > l_max_schm_end THEN
3049: hr_utility.set_message(8303,'PQP_230070_INV_SCHM_DATES');

Line 3047: OR fnd_date.canonical_to_date(nvl(p_aei_information2,'4712/12/31'))

3043: -- if the start/end date is not between the least start date and
3044: -- greatest end date,of all element EIT rows of the PT, raise an error
3045: IF fnd_date.canonical_to_date(p_aei_information1)
3046: < l_min_schm_st
3047: OR fnd_date.canonical_to_date(nvl(p_aei_information2,'4712/12/31'))
3048: > l_max_schm_end THEN
3049: hr_utility.set_message(8303,'PQP_230070_INV_SCHM_DATES');
3050: hr_utility.raise_error;
3051: END IF;

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 3368: (p_effective_date => fnd_date.canonical_to_date(to_char(TRUNC(

3364: --
3365: -- Derive the next abp reporting date
3366: --
3367: l_ret_val := get_reporting_date
3368: (p_effective_date => fnd_date.canonical_to_date(to_char(TRUNC(
3369: fnd_date.canonical_to_date(p_aei_information1)),'YYYY/MM')||'/01')
3370: ,p_assignment_id => fnd_number.canonical_to_number(p_assignment_id)
3371: ,p_person_id => l_person_id
3372: ,p_reporting_date => l_abp_rep_date );

Line 3369: fnd_date.canonical_to_date(p_aei_information1)),'YYYY/MM')||'/01')

3365: -- Derive the next abp reporting date
3366: --
3367: l_ret_val := get_reporting_date
3368: (p_effective_date => fnd_date.canonical_to_date(to_char(TRUNC(
3369: fnd_date.canonical_to_date(p_aei_information1)),'YYYY/MM')||'/01')
3370: ,p_assignment_id => fnd_number.canonical_to_number(p_assignment_id)
3371: ,p_person_id => l_person_id
3372: ,p_reporting_date => l_abp_rep_date );
3373:

Line 3375: l_abp_rep_date := fnd_date.canonical_to_date(p_aei_information22);

3371: ,p_person_id => l_person_id
3372: ,p_reporting_date => l_abp_rep_date );
3373:
3374: IF p_aei_information22 IS NOT NULL THEN
3375: l_abp_rep_date := fnd_date.canonical_to_date(p_aei_information22);
3376: ELSE
3377: l_abp_rep_date := greatest(l_abp_rep_date,fnd_date.canonical_to_date(p_aei_information1));
3378: END IF;
3379:

Line 3377: l_abp_rep_date := greatest(l_abp_rep_date,fnd_date.canonical_to_date(p_aei_information1));

3373:
3374: IF p_aei_information22 IS NOT NULL THEN
3375: l_abp_rep_date := fnd_date.canonical_to_date(p_aei_information22);
3376: ELSE
3377: l_abp_rep_date := greatest(l_abp_rep_date,fnd_date.canonical_to_date(p_aei_information1));
3378: END IF;
3379:
3380: --Call the procedure to insert the rows in the ben_ext_chg_evt_log table
3381: pqp_nl_ext_functions.create_asg_info_upd_chg_evt

Line 3407: l_start_year := to_char(fnd_date.canonical_to_date(p_aei_information1),'YYYY');

3403: ELSIF p_information_type = 'NL_ABP_PAR_INFO' THEN
3404:
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

Line 3408: l_end_year := to_char(fnd_date.canonical_to_date(nvl(p_aei_information2,

3404:
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

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 3560: l_min_sal_start := fnd_date.canonical_to_date(temp_rec.aei_information1);

3556: l_counter := 0;
3557: FOR temp_rec IN cur_get_sal_rows(l_curr_year)
3558: LOOP
3559: IF l_counter = 0 THEN
3560: l_min_sal_start := fnd_date.canonical_to_date(temp_rec.aei_information1);
3561: l_max_sal_end := fnd_date.canonical_to_date(temp_rec.aei_information2);
3562: ELSE
3563: IF trunc(fnd_date.canonical_to_date(temp_rec.aei_information1))
3564: < l_min_sal_start THEN

Line 3561: l_max_sal_end := fnd_date.canonical_to_date(temp_rec.aei_information2);

3557: FOR temp_rec IN cur_get_sal_rows(l_curr_year)
3558: LOOP
3559: IF l_counter = 0 THEN
3560: l_min_sal_start := fnd_date.canonical_to_date(temp_rec.aei_information1);
3561: l_max_sal_end := fnd_date.canonical_to_date(temp_rec.aei_information2);
3562: ELSE
3563: IF trunc(fnd_date.canonical_to_date(temp_rec.aei_information1))
3564: < l_min_sal_start THEN
3565: l_min_sal_start := trunc(fnd_date.canonical_to_date(temp_rec.aei_information1));

Line 3563: IF trunc(fnd_date.canonical_to_date(temp_rec.aei_information1))

3559: IF l_counter = 0 THEN
3560: l_min_sal_start := fnd_date.canonical_to_date(temp_rec.aei_information1);
3561: l_max_sal_end := fnd_date.canonical_to_date(temp_rec.aei_information2);
3562: ELSE
3563: IF trunc(fnd_date.canonical_to_date(temp_rec.aei_information1))
3564: < l_min_sal_start THEN
3565: l_min_sal_start := trunc(fnd_date.canonical_to_date(temp_rec.aei_information1));
3566: END IF;
3567: IF trunc(fnd_date.canonical_to_date(p_aei_information2))

Line 3565: l_min_sal_start := trunc(fnd_date.canonical_to_date(temp_rec.aei_information1));

3561: l_max_sal_end := fnd_date.canonical_to_date(temp_rec.aei_information2);
3562: ELSE
3563: IF trunc(fnd_date.canonical_to_date(temp_rec.aei_information1))
3564: < l_min_sal_start THEN
3565: l_min_sal_start := trunc(fnd_date.canonical_to_date(temp_rec.aei_information1));
3566: END IF;
3567: IF trunc(fnd_date.canonical_to_date(p_aei_information2))
3568: > l_max_sal_end THEN
3569: l_max_sal_end := trunc(fnd_date.canonical_to_date(temp_rec.aei_information2));

Line 3567: IF trunc(fnd_date.canonical_to_date(p_aei_information2))

3563: IF trunc(fnd_date.canonical_to_date(temp_rec.aei_information1))
3564: < l_min_sal_start THEN
3565: l_min_sal_start := trunc(fnd_date.canonical_to_date(temp_rec.aei_information1));
3566: END IF;
3567: IF trunc(fnd_date.canonical_to_date(p_aei_information2))
3568: > l_max_sal_end THEN
3569: l_max_sal_end := trunc(fnd_date.canonical_to_date(temp_rec.aei_information2));
3570: END IF;
3571: END IF;

Line 3569: l_max_sal_end := trunc(fnd_date.canonical_to_date(temp_rec.aei_information2));

3565: l_min_sal_start := trunc(fnd_date.canonical_to_date(temp_rec.aei_information1));
3566: END IF;
3567: IF trunc(fnd_date.canonical_to_date(p_aei_information2))
3568: > l_max_sal_end THEN
3569: l_max_sal_end := trunc(fnd_date.canonical_to_date(temp_rec.aei_information2));
3570: END IF;
3571: END IF;
3572: l_counter := l_counter + 1;
3573: END LOOP;

Line 3578: IF fnd_date.canonical_to_date(p_aei_information1_o)

3574:
3575: -- Check to see if the records are in continuous order
3576: -- and there are no gaps
3577: IF l_min_sal_start IS NOT NULL AND l_max_sal_end IS NOT NULL THEN
3578: IF fnd_date.canonical_to_date(p_aei_information1_o)
3579: > l_min_start_date THEN
3580:
3581: IF fnd_date.canonical_to_date(p_aei_information1)
3582: > fnd_date.canonical_to_date(p_aei_information1_o) THEN

Line 3581: IF fnd_date.canonical_to_date(p_aei_information1)

3577: IF l_min_sal_start IS NOT NULL AND l_max_sal_end IS NOT NULL THEN
3578: IF fnd_date.canonical_to_date(p_aei_information1_o)
3579: > l_min_start_date THEN
3580:
3581: IF fnd_date.canonical_to_date(p_aei_information1)
3582: > fnd_date.canonical_to_date(p_aei_information1_o) THEN
3583:
3584: hr_utility.set_message(8303,'PQP_230157_GAP_IN_SAL_ROW');
3585: hr_utility.raise_error;

Line 3582: > fnd_date.canonical_to_date(p_aei_information1_o) THEN

3578: IF fnd_date.canonical_to_date(p_aei_information1_o)
3579: > l_min_start_date THEN
3580:
3581: IF fnd_date.canonical_to_date(p_aei_information1)
3582: > fnd_date.canonical_to_date(p_aei_information1_o) THEN
3583:
3584: hr_utility.set_message(8303,'PQP_230157_GAP_IN_SAL_ROW');
3585: hr_utility.raise_error;
3586:

Line 3587: ELSIF fnd_date.canonical_to_date(p_aei_information1)

3583:
3584: hr_utility.set_message(8303,'PQP_230157_GAP_IN_SAL_ROW');
3585: hr_utility.raise_error;
3586:
3587: ELSIF fnd_date.canonical_to_date(p_aei_information1)
3588: < fnd_date.canonical_to_date(p_aei_information1_o) THEN
3589:
3590: hr_utility.set_message(8303,'PQP_230134_EIT_OVERLAP_ROWS');
3591: hr_utility.raise_error;

Line 3588: < fnd_date.canonical_to_date(p_aei_information1_o) THEN

3584: hr_utility.set_message(8303,'PQP_230157_GAP_IN_SAL_ROW');
3585: hr_utility.raise_error;
3586:
3587: ELSIF fnd_date.canonical_to_date(p_aei_information1)
3588: < fnd_date.canonical_to_date(p_aei_information1_o) THEN
3589:
3590: hr_utility.set_message(8303,'PQP_230134_EIT_OVERLAP_ROWS');
3591: hr_utility.raise_error;
3592:

Line 3595: ELSIF fnd_date.canonical_to_date(p_aei_information2_o)

3591: hr_utility.raise_error;
3592:
3593: END IF;
3594:
3595: ELSIF fnd_date.canonical_to_date(p_aei_information2_o)
3596: < l_max_end_date THEN
3597:
3598: IF fnd_date.canonical_to_date(p_aei_information2)
3599: < fnd_date.canonical_to_date(p_aei_information2_o) THEN

Line 3598: IF fnd_date.canonical_to_date(p_aei_information2)

3594:
3595: ELSIF fnd_date.canonical_to_date(p_aei_information2_o)
3596: < l_max_end_date THEN
3597:
3598: IF fnd_date.canonical_to_date(p_aei_information2)
3599: < fnd_date.canonical_to_date(p_aei_information2_o) THEN
3600:
3601: hr_utility.set_message(8303,'PQP_230157_GAP_IN_SAL_ROW');
3602: hr_utility.raise_error;

Line 3599: < fnd_date.canonical_to_date(p_aei_information2_o) THEN

3595: ELSIF fnd_date.canonical_to_date(p_aei_information2_o)
3596: < l_max_end_date THEN
3597:
3598: IF fnd_date.canonical_to_date(p_aei_information2)
3599: < fnd_date.canonical_to_date(p_aei_information2_o) THEN
3600:
3601: hr_utility.set_message(8303,'PQP_230157_GAP_IN_SAL_ROW');
3602: hr_utility.raise_error;
3603:

Line 3604: ELSIF fnd_date.canonical_to_date(p_aei_information2)

3600:
3601: hr_utility.set_message(8303,'PQP_230157_GAP_IN_SAL_ROW');
3602: hr_utility.raise_error;
3603:
3604: ELSIF fnd_date.canonical_to_date(p_aei_information2)
3605: > fnd_date.canonical_to_date(p_aei_information2_o) THEN
3606:
3607: hr_utility.set_message(8303,'PQP_230134_EIT_OVERLAP_ROWS');
3608: hr_utility.raise_error;

Line 3605: > fnd_date.canonical_to_date(p_aei_information2_o) THEN

3601: hr_utility.set_message(8303,'PQP_230157_GAP_IN_SAL_ROW');
3602: hr_utility.raise_error;
3603:
3604: ELSIF fnd_date.canonical_to_date(p_aei_information2)
3605: > fnd_date.canonical_to_date(p_aei_information2_o) THEN
3606:
3607: hr_utility.set_message(8303,'PQP_230134_EIT_OVERLAP_ROWS');
3608: hr_utility.raise_error;
3609:

Line 3615: IF nvl(l_min_sal_start,fnd_date.canonical_to_date(p_aei_information1))

3611:
3612: END IF;
3613: END IF;/*only if other rows exist*/
3614:
3615: IF nvl(l_min_sal_start,fnd_date.canonical_to_date(p_aei_information1))
3616: > fnd_date.canonical_to_date(p_aei_information1) THEN
3617: l_min_sal_start := fnd_date.canonical_to_date(p_aei_information1);
3618: END IF;
3619:

Line 3616: > fnd_date.canonical_to_date(p_aei_information1) THEN

3612: END IF;
3613: END IF;/*only if other rows exist*/
3614:
3615: IF nvl(l_min_sal_start,fnd_date.canonical_to_date(p_aei_information1))
3616: > fnd_date.canonical_to_date(p_aei_information1) THEN
3617: l_min_sal_start := fnd_date.canonical_to_date(p_aei_information1);
3618: END IF;
3619:
3620: --verify that the minimum date is correct date or not

Line 3617: l_min_sal_start := fnd_date.canonical_to_date(p_aei_information1);

3613: END IF;/*only if other rows exist*/
3614:
3615: IF nvl(l_min_sal_start,fnd_date.canonical_to_date(p_aei_information1))
3616: > fnd_date.canonical_to_date(p_aei_information1) THEN
3617: l_min_sal_start := fnd_date.canonical_to_date(p_aei_information1);
3618: END IF;
3619:
3620: --verify that the minimum date is correct date or not
3621:

Line 3622: IF (nvl(l_min_sal_start,fnd_date.canonical_to_date(p_aei_information1))

3618: END IF;
3619:
3620: --verify that the minimum date is correct date or not
3621:
3622: IF (nvl(l_min_sal_start,fnd_date.canonical_to_date(p_aei_information1))
3623: <> get_valid_start_date(p_assignment_id,l_eff_date,l_error_status,l_error_message)) THEN
3624: hr_utility.set_message(8303,'PQP_230158_ST_DT_JAN_01');
3625: hr_utility.raise_error;
3626: Else

Line 3639: (p_effective_date => fnd_date.canonical_to_date(to_char(TRUNC(

3635: --
3636: -- Derive the next abp reporting date
3637: --
3638: l_ret_val := get_reporting_date
3639: (p_effective_date => fnd_date.canonical_to_date(to_char(TRUNC(
3640: fnd_date.canonical_to_date(p_aei_information1)),'YYYY/MM')||'/01')
3641: ,p_assignment_id => fnd_number.canonical_to_number(p_assignment_id)
3642: ,p_person_id => l_person_id
3643: ,p_reporting_date => l_abp_rep_date );

Line 3640: fnd_date.canonical_to_date(p_aei_information1)),'YYYY/MM')||'/01')

3636: -- Derive the next abp reporting date
3637: --
3638: l_ret_val := get_reporting_date
3639: (p_effective_date => fnd_date.canonical_to_date(to_char(TRUNC(
3640: fnd_date.canonical_to_date(p_aei_information1)),'YYYY/MM')||'/01')
3641: ,p_assignment_id => fnd_number.canonical_to_number(p_assignment_id)
3642: ,p_person_id => l_person_id
3643: ,p_reporting_date => l_abp_rep_date );
3644:

Line 3877: ,TRUNC(fnd_date.canonical_to_date(aei_information1)) St_Dt

3873: --
3874: -- Rows that start in the current period
3875: --
3876: SELECT fnd_number.canonical_to_number(nvl(aei_information5,'1')) VOP
3877: ,TRUNC(fnd_date.canonical_to_date(aei_information1)) St_Dt
3878: ,TRUNC(NVL(fnd_date.canonical_to_date(aei_information2),c_ed_dt)) Ed_Dt
3879: FROM per_assignment_extra_info
3880: WHERE assignment_id = p_assignment_id
3881: AND information_type = 'NL_ABP_PAR_INFO'

Line 3878: ,TRUNC(NVL(fnd_date.canonical_to_date(aei_information2),c_ed_dt)) Ed_Dt

3874: -- Rows that start in the current period
3875: --
3876: SELECT fnd_number.canonical_to_number(nvl(aei_information5,'1')) VOP
3877: ,TRUNC(fnd_date.canonical_to_date(aei_information1)) St_Dt
3878: ,TRUNC(NVL(fnd_date.canonical_to_date(aei_information2),c_ed_dt)) Ed_Dt
3879: FROM per_assignment_extra_info
3880: WHERE assignment_id = p_assignment_id
3881: AND information_type = 'NL_ABP_PAR_INFO'
3882: AND aei_information_category = 'NL_ABP_PAR_INFO'

Line 3883: AND TRUNC(fnd_date.canonical_to_date(aei_information1))

3879: FROM per_assignment_extra_info
3880: WHERE assignment_id = p_assignment_id
3881: AND information_type = 'NL_ABP_PAR_INFO'
3882: AND aei_information_category = 'NL_ABP_PAR_INFO'
3883: AND TRUNC(fnd_date.canonical_to_date(aei_information1))
3884: BETWEEN c_st_dt AND c_ed_dt
3885: UNION
3886: --
3887: -- Rows that end in the current period

Line 3890: ,TRUNC(fnd_date.canonical_to_date(aei_information1)) St_Dt

3886: --
3887: -- Rows that end in the current period
3888: --
3889: SELECT fnd_number.canonical_to_number(nvl(aei_information5,'1')) VOP
3890: ,TRUNC(fnd_date.canonical_to_date(aei_information1)) St_Dt
3891: ,TRUNC(NVL(fnd_date.canonical_to_date(aei_information2),c_ed_dt)) Ed_Dt
3892: FROM per_assignment_extra_info
3893: WHERE assignment_id = p_assignment_id
3894: AND information_type = 'NL_ABP_PAR_INFO'

Line 3891: ,TRUNC(NVL(fnd_date.canonical_to_date(aei_information2),c_ed_dt)) Ed_Dt

3887: -- Rows that end in the current period
3888: --
3889: SELECT fnd_number.canonical_to_number(nvl(aei_information5,'1')) VOP
3890: ,TRUNC(fnd_date.canonical_to_date(aei_information1)) St_Dt
3891: ,TRUNC(NVL(fnd_date.canonical_to_date(aei_information2),c_ed_dt)) Ed_Dt
3892: FROM per_assignment_extra_info
3893: WHERE assignment_id = p_assignment_id
3894: AND information_type = 'NL_ABP_PAR_INFO'
3895: AND aei_information_category = 'NL_ABP_PAR_INFO'

Line 3896: AND TRUNC(NVL(fnd_date.canonical_to_date(aei_information2),c_ed_dt))

3892: FROM per_assignment_extra_info
3893: WHERE assignment_id = p_assignment_id
3894: AND information_type = 'NL_ABP_PAR_INFO'
3895: AND aei_information_category = 'NL_ABP_PAR_INFO'
3896: AND TRUNC(NVL(fnd_date.canonical_to_date(aei_information2),c_ed_dt))
3897: BETWEEN c_st_dt AND c_ed_dt
3898: UNION
3899: --
3900: -- Rows that neither start or end in the current period

Line 3904: ,TRUNC(fnd_date.canonical_to_date(aei_information1)) St_Dt

3900: -- Rows that neither start or end in the current period
3901: -- but the data in the EIT is valid for the current period
3902: --
3903: SELECT fnd_number.canonical_to_number(nvl(aei_information5,'1')) VOP
3904: ,TRUNC(fnd_date.canonical_to_date(aei_information1)) St_Dt
3905: ,TRUNC(NVL(fnd_date.canonical_to_date(aei_information2),c_ed_dt)) Ed_Dt
3906: FROM per_assignment_extra_info
3907: WHERE assignment_id = p_assignment_id
3908: AND information_type = 'NL_ABP_PAR_INFO'

Line 3905: ,TRUNC(NVL(fnd_date.canonical_to_date(aei_information2),c_ed_dt)) Ed_Dt

3901: -- but the data in the EIT is valid for the current period
3902: --
3903: SELECT fnd_number.canonical_to_number(nvl(aei_information5,'1')) VOP
3904: ,TRUNC(fnd_date.canonical_to_date(aei_information1)) St_Dt
3905: ,TRUNC(NVL(fnd_date.canonical_to_date(aei_information2),c_ed_dt)) Ed_Dt
3906: FROM per_assignment_extra_info
3907: WHERE assignment_id = p_assignment_id
3908: AND information_type = 'NL_ABP_PAR_INFO'
3909: AND aei_information_category = 'NL_ABP_PAR_INFO'

Line 3910: AND c_ed_dt BETWEEN TRUNC(fnd_date.canonical_to_date(aei_information1))

3906: FROM per_assignment_extra_info
3907: WHERE assignment_id = p_assignment_id
3908: AND information_type = 'NL_ABP_PAR_INFO'
3909: AND aei_information_category = 'NL_ABP_PAR_INFO'
3910: AND c_ed_dt BETWEEN TRUNC(fnd_date.canonical_to_date(aei_information1))
3911: AND TRUNC(NVL(fnd_date.canonical_to_date(aei_information2),c_ed_dt))
3912: ORDER BY st_dt;
3913:
3914: -- Bug# 6506736 Start

Line 3911: AND TRUNC(NVL(fnd_date.canonical_to_date(aei_information2),c_ed_dt))

3907: WHERE assignment_id = p_assignment_id
3908: AND information_type = 'NL_ABP_PAR_INFO'
3909: AND aei_information_category = 'NL_ABP_PAR_INFO'
3910: AND c_ed_dt BETWEEN TRUNC(fnd_date.canonical_to_date(aei_information1))
3911: AND TRUNC(NVL(fnd_date.canonical_to_date(aei_information2),c_ed_dt))
3912: ORDER BY st_dt;
3913:
3914: -- Bug# 6506736 Start
3915: CURSOR c_vop_current_or (c_st_dt IN DATE

Line 3921: ,TRUNC(fnd_date.canonical_to_date(aei_information1)) St_Dt

3917: --
3918: -- Rows that start in the current period
3919: --
3920: SELECT fnd_number.canonical_to_number(nvl(aei_information23,'1')) VOP
3921: ,TRUNC(fnd_date.canonical_to_date(aei_information1)) St_Dt
3922: ,TRUNC(NVL(fnd_date.canonical_to_date(aei_information2),c_ed_dt)) Ed_Dt
3923: FROM per_assignment_extra_info aei,
3924: pqp_pension_types_f pty
3925: WHERE assignment_id = p_assignment_id

Line 3922: ,TRUNC(NVL(fnd_date.canonical_to_date(aei_information2),c_ed_dt)) Ed_Dt

3918: -- Rows that start in the current period
3919: --
3920: SELECT fnd_number.canonical_to_number(nvl(aei_information23,'1')) VOP
3921: ,TRUNC(fnd_date.canonical_to_date(aei_information1)) St_Dt
3922: ,TRUNC(NVL(fnd_date.canonical_to_date(aei_information2),c_ed_dt)) Ed_Dt
3923: FROM per_assignment_extra_info aei,
3924: pqp_pension_types_f pty
3925: WHERE assignment_id = p_assignment_id
3926: AND aei_information3 = p_pension_type_id

Line 3935: AND TRUNC(fnd_date.canonical_to_date(aei_information1))

3931: AND c_ed_dt BETWEEN pty.effective_start_date AND NVL(pty.effective_end_date,to_date('31/12/4712','DD/MM/YYYY'))
3932: AND aei_information23 IS NOT NULL
3933: AND information_type = 'NL_ABP_PI'
3934: AND aei_information_category = 'NL_ABP_PI'
3935: AND TRUNC(fnd_date.canonical_to_date(aei_information1))
3936: BETWEEN c_st_dt AND c_ed_dt
3937: UNION
3938: --
3939: -- Rows that end in the current period

Line 3942: ,TRUNC(fnd_date.canonical_to_date(aei_information1)) St_Dt

3938: --
3939: -- Rows that end in the current period
3940: --
3941: SELECT fnd_number.canonical_to_number(nvl(aei_information23,'1')) VOP
3942: ,TRUNC(fnd_date.canonical_to_date(aei_information1)) St_Dt
3943: ,TRUNC(NVL(fnd_date.canonical_to_date(aei_information2),c_ed_dt)) Ed_Dt
3944: FROM per_assignment_extra_info aei,
3945: pqp_pension_types_f pty
3946: WHERE assignment_id = p_assignment_id

Line 3943: ,TRUNC(NVL(fnd_date.canonical_to_date(aei_information2),c_ed_dt)) Ed_Dt

3939: -- Rows that end in the current period
3940: --
3941: SELECT fnd_number.canonical_to_number(nvl(aei_information23,'1')) VOP
3942: ,TRUNC(fnd_date.canonical_to_date(aei_information1)) St_Dt
3943: ,TRUNC(NVL(fnd_date.canonical_to_date(aei_information2),c_ed_dt)) Ed_Dt
3944: FROM per_assignment_extra_info aei,
3945: pqp_pension_types_f pty
3946: WHERE assignment_id = p_assignment_id
3947: AND aei_information3 = p_pension_type_id

Line 3956: AND TRUNC(NVL(fnd_date.canonical_to_date(aei_information2),c_ed_dt))

3952: AND c_ed_dt BETWEEN pty.effective_start_date AND NVL(pty.effective_end_date ,to_date('31/12/4712','DD/MM/YYYY'))
3953: AND aei_information23 IS NOT NULL
3954: AND information_type = 'NL_ABP_PI'
3955: AND aei_information_category = 'NL_ABP_PI'
3956: AND TRUNC(NVL(fnd_date.canonical_to_date(aei_information2),c_ed_dt))
3957: BETWEEN c_st_dt AND c_ed_dt
3958: UNION
3959: --
3960: -- Rows that neither start or end in the current period

Line 3964: ,TRUNC(fnd_date.canonical_to_date(aei_information1)) St_Dt

3960: -- Rows that neither start or end in the current period
3961: -- but the data in the EIT is valid for the current period
3962: --
3963: SELECT fnd_number.canonical_to_number(nvl(aei_information23,'1')) VOP
3964: ,TRUNC(fnd_date.canonical_to_date(aei_information1)) St_Dt
3965: ,TRUNC(NVL(fnd_date.canonical_to_date(aei_information2),c_ed_dt)) Ed_Dt
3966: FROM per_assignment_extra_info aei,
3967: pqp_pension_types_f pty
3968: WHERE assignment_id = p_assignment_id

Line 3965: ,TRUNC(NVL(fnd_date.canonical_to_date(aei_information2),c_ed_dt)) Ed_Dt

3961: -- but the data in the EIT is valid for the current period
3962: --
3963: SELECT fnd_number.canonical_to_number(nvl(aei_information23,'1')) VOP
3964: ,TRUNC(fnd_date.canonical_to_date(aei_information1)) St_Dt
3965: ,TRUNC(NVL(fnd_date.canonical_to_date(aei_information2),c_ed_dt)) Ed_Dt
3966: FROM per_assignment_extra_info aei,
3967: pqp_pension_types_f pty
3968: WHERE assignment_id = p_assignment_id
3969: AND aei_information3 = p_pension_type_id

Line 3978: AND c_ed_dt BETWEEN TRUNC(fnd_date.canonical_to_date(aei_information1))

3974: AND c_ed_dt BETWEEN pty.effective_start_date and NVL(pty.effective_end_date ,to_date('31/12/4712','DD/MM/YYYY'))
3975: AND aei_information23 IS NOT NULL
3976: AND information_type = 'NL_ABP_PI'
3977: AND aei_information_category = 'NL_ABP_PI'
3978: AND c_ed_dt BETWEEN TRUNC(fnd_date.canonical_to_date(aei_information1))
3979: AND TRUNC(NVL(fnd_date.canonical_to_date(aei_information2),c_ed_dt))
3980: ORDER BY st_dt;
3981: -- Bug# 6506736 End
3982:

Line 3979: AND TRUNC(NVL(fnd_date.canonical_to_date(aei_information2),c_ed_dt))

3975: AND aei_information23 IS NOT NULL
3976: AND information_type = 'NL_ABP_PI'
3977: AND aei_information_category = 'NL_ABP_PI'
3978: AND c_ed_dt BETWEEN TRUNC(fnd_date.canonical_to_date(aei_information1))
3979: AND TRUNC(NVL(fnd_date.canonical_to_date(aei_information2),c_ed_dt))
3980: ORDER BY st_dt;
3981: -- Bug# 6506736 End
3982:
3983: l_period_start_date DATE;

Line 4189: AND p_date_earned BETWEEN trunc(fnd_date.canonical_to_date(aei_information1))

4185: FROM per_assignment_extra_info
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:

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 4436: WHERE p_date_earned between fnd_date.canonical_to_date(aei_information1)

4432:
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:

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 4541: AND p_date_earned BETWEEN fnd_date.canonical_to_date(hoi.org_information1)

4537: AND hoi.org_information3 = TO_CHAR(p_pt_id)
4538: AND NVL(hoi.org_information6,'N') = 'Y'
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

Line 4542: AND fnd_date.canonical_to_date(NVL(hoi.org_information2,

4538: AND NVL(hoi.org_information6,'N') = 'Y'
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

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 4560: AND p_date_earned BETWEEN fnd_date.canonical_to_date(hoi.org_information1)

4556: AND hoi.org_information3 = TO_CHAR(p_pt_id)
4557: AND NVL(hoi.org_information6,'N') = 'Y'
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

Line 4561: AND fnd_date.canonical_to_date(NVL(hoi.org_information2,

4557: AND NVL(hoi.org_information6,'N') = 'Y'
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'

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 4570: AND p_date_earned BETWEEN fnd_date.canonical_to_date(hoi.org_information1)

4566: AND hoi.org_information3 = TO_CHAR(p_pt_id)
4567: AND ( NVL(hoi.org_information6,'N')= 'N'
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;

Line 4571: AND fnd_date.canonical_to_date(NVL(hoi.org_information2,

4567: AND ( NVL(hoi.org_information6,'N')= 'N'
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:

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 4656: AND p_date_earned BETWEEN fnd_date.canonical_to_date(hoi.org_information1)

4652: WHERE hoi.org_information_context = 'PQP_NL_ABP_PT'
4653: AND hoi.org_information3 = TO_CHAR(p_pension_type_id)
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: --

Line 4657: AND fnd_date.canonical_to_date(NVL(hoi.org_information2,

4653: AND hoi.org_information3 = TO_CHAR(p_pension_type_id)
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

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 4949: AND c_date_earned between fnd_date.canonical_to_date(paei.aei_information1)

4945: WHERE paei.information_type = 'NL_ABP_PI'
4946: AND paei.aei_information_category = 'NL_ABP_PI'
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: --

Line 4950: AND fnd_date.canonical_to_date(NVL(paei.aei_information2,

4946: AND paei.aei_information_category = 'NL_ABP_PI'
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

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 4970: AND c_date_earned BETWEEN fnd_date.canonical_to_date(hoi.org_information1)

4966: AND hoi.org_information3 = TO_CHAR(c_pt_id)
4967: AND hoi.org_information6 = 'Y'
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

Line 4971: AND fnd_date.canonical_to_date(NVL(hoi.org_information2,

4967: AND hoi.org_information6 = 'Y'
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

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 5600: SELECT fnd_date.canonical_to_date(paei.aei_information1)

5596: and hoi.organization_id = c_org_id;
5597:
5598: --Cursor to find the participation start date from assignment level information
5599: CURSOR c_get_asg_info Is
5600: SELECT fnd_date.canonical_to_date(paei.aei_information1)
5601: FROM per_assignment_extra_info paei
5602: WHERE paei.information_type = 'NL_ABP_PI'
5603: and paei.aei_information_category = 'NL_ABP_PI'
5604: and paei.aei_information3 = to_char(p_pension_type_id)

Line 5606: and p_date_earned between fnd_date.canonical_to_date(paei.aei_information1)

5602: WHERE paei.information_type = 'NL_ABP_PI'
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

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 5611: SELECT fnd_date.canonical_to_date(hoi.org_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)
5612: FROM hr_organization_information hoi
5613: where hoi.org_information_context = 'PQP_NL_ABP_PT'
5614: and hoi.org_information3 = to_char(p_pension_type_id)
5615: and hoi.org_information6 = 'Y'

Line 5618: and p_date_earned between fnd_date.canonical_to_date(hoi.org_information1)

5614: and hoi.org_information3 = to_char(p_pension_type_id)
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

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 6697: TRUNC(fnd_date.canonical_to_date(prmtr_05)))

6693: AND prmtr_01 = c_asg_id
6694: AND prmtr_02 = p_old_val1
6695: AND DECODE(prmtr_05,'4712/12/31 00:00:00',
6696: TRUNC(p_chg_eff_dt) - 1,
6697: TRUNC(fnd_date.canonical_to_date(prmtr_05)))
6698: = TRUNC(p_chg_eff_dt) - 1;
6699:
6700: CURSOR c_upd_log_kff(c_asg_id IN ben_ext_chg_evt_log.prmtr_01%TYPE ) IS
6701: SELECT ext_chg_evt_log_id

Line 6744: (p_effective_date => fnd_date.canonical_to_date(to_char(TRUNC(p_chg_eff_dt),'YYYY/MM')||'/01')

6740: --
6741: -- Get the reporting date
6742: --
6743: l_ret_val := get_reporting_date
6744: (p_effective_date => fnd_date.canonical_to_date(to_char(TRUNC(p_chg_eff_dt),'YYYY/MM')||'/01')
6745: ,p_assignment_id => fnd_number.canonical_to_number(p_prmtr_01)
6746: ,p_person_id => p_person_id
6747: ,p_reporting_date => l_out_rep_dt );
6748:

Line 6749: l_reporting_dt := fnd_date.date_to_canonical(l_out_rep_dt);

6745: ,p_assignment_id => fnd_number.canonical_to_number(p_prmtr_01)
6746: ,p_person_id => p_person_id
6747: ,p_reporting_date => l_out_rep_dt );
6748:
6749: l_reporting_dt := fnd_date.date_to_canonical(l_out_rep_dt);
6750: l_new_log_cre := 0;
6751:
6752: hr_utility.set_location('GAA -- The change event code is COSCKFF',10 );
6753: hr_utility.set_location('GAA -- Reporting date is '||l_reporting_dt,10 );

Line 6810: IF TRUNC(fnd_date.canonical_to_date(l_asg_st_dt)) >

6806:
6807: -- Make sure that the reporting date is set to the correct value
6808: -- if the changes are being made for a future date then we use that date
6809: -- to report the change.
6810: IF TRUNC(fnd_date.canonical_to_date(l_asg_st_dt)) >
6811: TRUNC(fnd_date.canonical_to_date(l_reporting_dt)) THEN
6812: l_reporting_dt := fnd_date.date_to_canonical(add_months(fnd_date.canonical_to_date(TO_CHAR(TRUNC(fnd_date.canonical_to_date(l_asg_st_dt)),'YYYY/MM')||'/01'),1) - 1);
6813: END IF;
6814:

Line 6811: TRUNC(fnd_date.canonical_to_date(l_reporting_dt)) THEN

6807: -- Make sure that the reporting date is set to the correct value
6808: -- if the changes are being made for a future date then we use that date
6809: -- to report the change.
6810: IF TRUNC(fnd_date.canonical_to_date(l_asg_st_dt)) >
6811: TRUNC(fnd_date.canonical_to_date(l_reporting_dt)) THEN
6812: l_reporting_dt := fnd_date.date_to_canonical(add_months(fnd_date.canonical_to_date(TO_CHAR(TRUNC(fnd_date.canonical_to_date(l_asg_st_dt)),'YYYY/MM')||'/01'),1) - 1);
6813: END IF;
6814:
6815: -- The old KFF id is being replaced by a new one. But it

Line 6812: l_reporting_dt := fnd_date.date_to_canonical(add_months(fnd_date.canonical_to_date(TO_CHAR(TRUNC(fnd_date.canonical_to_date(l_asg_st_dt)),'YYYY/MM')||'/01'),1) - 1);

6808: -- if the changes are being made for a future date then we use that date
6809: -- to report the change.
6810: IF TRUNC(fnd_date.canonical_to_date(l_asg_st_dt)) >
6811: TRUNC(fnd_date.canonical_to_date(l_reporting_dt)) THEN
6812: l_reporting_dt := fnd_date.date_to_canonical(add_months(fnd_date.canonical_to_date(TO_CHAR(TRUNC(fnd_date.canonical_to_date(l_asg_st_dt)),'YYYY/MM')||'/01'),1) - 1);
6813: END IF;
6814:
6815: -- The old KFF id is being replaced by a new one. But it
6816: -- does not nencessarily mean that there has been a change

Line 6957: IF trunc(fnd_date.canonical_to_date(upd_rec.prmtr_09)) =

6953: -- Update any existing log rows based on the changes made
6954: -- also set the end date in case of an update
6955: --
6956: FOR upd_rec IN c_upd_log_kff_upd(c_asg_id => p_prmtr_01) LOOP
6957: IF trunc(fnd_date.canonical_to_date(upd_rec.prmtr_09)) =
6958: trunc(fnd_date.canonical_to_date(l_reporting_dt)) THEN
6959: UPDATE ben_ext_chg_evt_log
6960: SET prmtr_05 = fnd_date.date_to_canonical(TRUNC(p_chg_eff_dt)-1)
6961: ,prmtr_09 = l_reporting_dt

Line 6958: trunc(fnd_date.canonical_to_date(l_reporting_dt)) THEN

6954: -- also set the end date in case of an update
6955: --
6956: FOR upd_rec IN c_upd_log_kff_upd(c_asg_id => p_prmtr_01) LOOP
6957: IF trunc(fnd_date.canonical_to_date(upd_rec.prmtr_09)) =
6958: trunc(fnd_date.canonical_to_date(l_reporting_dt)) THEN
6959: UPDATE ben_ext_chg_evt_log
6960: SET prmtr_05 = fnd_date.date_to_canonical(TRUNC(p_chg_eff_dt)-1)
6961: ,prmtr_09 = l_reporting_dt
6962: WHERE ext_chg_evt_log_id = upd_rec.ext_chg_evt_log_id;

Line 6960: SET prmtr_05 = fnd_date.date_to_canonical(TRUNC(p_chg_eff_dt)-1)

6956: FOR upd_rec IN c_upd_log_kff_upd(c_asg_id => p_prmtr_01) LOOP
6957: IF trunc(fnd_date.canonical_to_date(upd_rec.prmtr_09)) =
6958: trunc(fnd_date.canonical_to_date(l_reporting_dt)) THEN
6959: UPDATE ben_ext_chg_evt_log
6960: SET prmtr_05 = fnd_date.date_to_canonical(TRUNC(p_chg_eff_dt)-1)
6961: ,prmtr_09 = l_reporting_dt
6962: WHERE ext_chg_evt_log_id = upd_rec.ext_chg_evt_log_id;
6963: ELSE
6964: UPDATE ben_ext_chg_evt_log

Line 6965: SET prmtr_05 = fnd_date.date_to_canonical(TRUNC(p_chg_eff_dt)-1)

6961: ,prmtr_09 = l_reporting_dt
6962: WHERE ext_chg_evt_log_id = upd_rec.ext_chg_evt_log_id;
6963: ELSE
6964: UPDATE ben_ext_chg_evt_log
6965: SET prmtr_05 = fnd_date.date_to_canonical(TRUNC(p_chg_eff_dt)-1)
6966: WHERE ext_chg_evt_log_id = upd_rec.ext_chg_evt_log_id;
6967: END IF;
6968: END LOOP;
6969: hr_utility.set_location('GAA -- Updated the existing log to set the end as eff dt -1 ',10 );

Line 6977: IF trunc(fnd_date.canonical_to_date(upd_rec.prmtr_09)) =

6973: -- Update any existing log rows based on the changes made
6974: --
6975: IF l_new_log_cre = 0 THEN
6976: FOR upd_rec IN c_upd_log_kff(c_asg_id => p_prmtr_01) LOOP
6977: IF trunc(fnd_date.canonical_to_date(upd_rec.prmtr_09)) =
6978: trunc(fnd_date.canonical_to_date(l_reporting_dt)) THEN
6979: UPDATE ben_ext_chg_evt_log
6980: SET prmtr_02 = p_new_val1
6981: ,prmtr_09 = l_reporting_dt

Line 6978: trunc(fnd_date.canonical_to_date(l_reporting_dt)) THEN

6974: --
6975: IF l_new_log_cre = 0 THEN
6976: FOR upd_rec IN c_upd_log_kff(c_asg_id => p_prmtr_01) LOOP
6977: IF trunc(fnd_date.canonical_to_date(upd_rec.prmtr_09)) =
6978: trunc(fnd_date.canonical_to_date(l_reporting_dt)) THEN
6979: UPDATE ben_ext_chg_evt_log
6980: SET prmtr_02 = p_new_val1
6981: ,prmtr_09 = l_reporting_dt
6982: WHERE ext_chg_evt_log_id = upd_rec.ext_chg_evt_log_id;

Line 6991: IF trunc(fnd_date.canonical_to_date(upd_rec.prmtr_09)) =

6987: END IF;
6988: END LOOP;
6989: ELSIF l_new_log_cre = 1 THEN
6990: FOR upd_rec IN c_upd_log_kff_upd(c_asg_id => p_prmtr_01) LOOP
6991: IF trunc(fnd_date.canonical_to_date(upd_rec.prmtr_09)) =
6992: trunc(fnd_date.canonical_to_date(l_reporting_dt)) THEN
6993: UPDATE ben_ext_chg_evt_log
6994: SET prmtr_05 = fnd_date.date_to_canonical(TRUNC(p_chg_eff_dt)-1)
6995: ,prmtr_09 = l_reporting_dt

Line 6992: trunc(fnd_date.canonical_to_date(l_reporting_dt)) THEN

6988: END LOOP;
6989: ELSIF l_new_log_cre = 1 THEN
6990: FOR upd_rec IN c_upd_log_kff_upd(c_asg_id => p_prmtr_01) LOOP
6991: IF trunc(fnd_date.canonical_to_date(upd_rec.prmtr_09)) =
6992: trunc(fnd_date.canonical_to_date(l_reporting_dt)) THEN
6993: UPDATE ben_ext_chg_evt_log
6994: SET prmtr_05 = fnd_date.date_to_canonical(TRUNC(p_chg_eff_dt)-1)
6995: ,prmtr_09 = l_reporting_dt
6996: WHERE ext_chg_evt_log_id = upd_rec.ext_chg_evt_log_id;

Line 6994: SET prmtr_05 = fnd_date.date_to_canonical(TRUNC(p_chg_eff_dt)-1)

6990: FOR upd_rec IN c_upd_log_kff_upd(c_asg_id => p_prmtr_01) LOOP
6991: IF trunc(fnd_date.canonical_to_date(upd_rec.prmtr_09)) =
6992: trunc(fnd_date.canonical_to_date(l_reporting_dt)) THEN
6993: UPDATE ben_ext_chg_evt_log
6994: SET prmtr_05 = fnd_date.date_to_canonical(TRUNC(p_chg_eff_dt)-1)
6995: ,prmtr_09 = l_reporting_dt
6996: WHERE ext_chg_evt_log_id = upd_rec.ext_chg_evt_log_id;
6997: ELSE
6998: UPDATE ben_ext_chg_evt_log

Line 6999: SET prmtr_05 = fnd_date.date_to_canonical(TRUNC(p_chg_eff_dt)-1)

6995: ,prmtr_09 = l_reporting_dt
6996: WHERE ext_chg_evt_log_id = upd_rec.ext_chg_evt_log_id;
6997: ELSE
6998: UPDATE ben_ext_chg_evt_log
6999: SET prmtr_05 = fnd_date.date_to_canonical(TRUNC(p_chg_eff_dt)-1)
7000: WHERE ext_chg_evt_log_id = upd_rec.ext_chg_evt_log_id;
7001: END IF;
7002: END LOOP;
7003: END IF; -- check if new logs are created

Line 7019: (p_effective_date => fnd_date.canonical_to_date(to_char(TRUNC(p_chg_eff_dt),'YYYY/MM')||'/01')

7015: --
7016: -- Get the reporting date
7017: --
7018: l_ret_val := get_reporting_date
7019: (p_effective_date => fnd_date.canonical_to_date(to_char(TRUNC(p_chg_eff_dt),'YYYY/MM')||'/01')
7020: ,p_person_id => p_person_id
7021: ,p_reporting_date => l_out_rep_dt );
7022:
7023: l_reporting_dt := fnd_date.date_to_canonical(l_out_rep_dt);

Line 7023: l_reporting_dt := fnd_date.date_to_canonical(l_out_rep_dt);

7019: (p_effective_date => fnd_date.canonical_to_date(to_char(TRUNC(p_chg_eff_dt),'YYYY/MM')||'/01')
7020: ,p_person_id => p_person_id
7021: ,p_reporting_date => l_out_rep_dt );
7022:
7023: l_reporting_dt := fnd_date.date_to_canonical(l_out_rep_dt);
7024:
7025: hr_utility.set_location('... Reporting date is '||l_reporting_dt,10 );
7026: --
7027: -- Update the change event log row so that prmtr_09 is

Line 7058: (p_effective_date => fnd_date.canonical_to_date(to_char(TRUNC(p_chg_eff_dt),'YYYY/MM')||'/01')

7054: --
7055: -- Get the reporting date
7056: --
7057: l_ret_val := get_reporting_date
7058: (p_effective_date => fnd_date.canonical_to_date(to_char(TRUNC(p_chg_eff_dt),'YYYY/MM')||'/01')
7059: ,p_person_id => p_person_id
7060: ,p_reporting_date => l_out_rep_dt );
7061:
7062: IF TRUNC(l_out_rep_dt) < TRUNC(to_nl_date(p_old_val1,'DD-MM-RRRR')) THEN

Line 7066: l_reporting_dt := fnd_date.date_to_canonical(l_out_rep_dt);

7062: IF TRUNC(l_out_rep_dt) < TRUNC(to_nl_date(p_old_val1,'DD-MM-RRRR')) THEN
7063: l_out_rep_dt := TRUNC(to_nl_date(p_old_val1,'DD-MM-RRRR'));
7064: END IF;
7065:
7066: l_reporting_dt := fnd_date.date_to_canonical(l_out_rep_dt);
7067:
7068: hr_utility.set_location('... Reporting date is '||l_reporting_dt,10 );
7069: --
7070: -- Update the change event log row so that prmtr_09 is

Line 7324: --AND p_date_earned BETWEEN fnd_date.canonical_to_date(aei_information1) AND fnd_date.canonical_to_date(nvl(aei_information2,'4712/12/31'));

7320: SELECT NVL(aei_information3,'Y') aei_information3
7321: FROM per_assignment_extra_info
7322: WHERE information_type = 'NL_ADDL_CALC'
7323: AND assignment_id = p_asg_id
7324: --AND p_date_earned BETWEEN fnd_date.canonical_to_date(aei_information1) AND fnd_date.canonical_to_date(nvl(aei_information2,'4712/12/31'));
7325: AND p_date_earned BETWEEN trunc(to_date(substr(aei_information1,1,10),'YYYY/MM/DD'))
7326: AND trunc(to_date(substr(nvl(aei_information2,'4712/12/31'),1,10),'YYYY/MM/DD'));
7327:
7328: l_addl_calc_flag per_assignment_extra_info.aei_information3%TYPE;