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_PENSION_EXTRACTS dependencies on FND_DATE

Line 451: AND fnd_date.canonical_to_date(prmtr_09) BETWEEN

447: FROM ben_ext_chg_evt_log bec
448: WHERE chg_evt_cd = 'DAT'
449: AND person_id = g_person_id
450: AND bec.business_group_id = p_business_group_id
451: AND fnd_date.canonical_to_date(prmtr_09) BETWEEN
452: g_extract_params(p_business_group_id).extract_start_date
453: AND g_extract_params(p_business_group_id).extract_end_date
454: AND EXISTS(SELECT 1
455: FROM per_periods_of_service pps

Line 534: AND fnd_date.canonical_to_date(bec.prmtr_09)

530: ,per_all_assignments_f asg
531: WHERE bec.chg_evt_cd = 'AAT'
532: AND bec.person_id = g_person_id
533: AND bec.business_group_id = p_business_group_id
534: AND fnd_date.canonical_to_date(bec.prmtr_09)
535: BETWEEN g_extract_params(p_business_group_id).extract_start_date
536: AND g_extract_params(p_business_group_id).extract_end_date
537: AND pps.person_id = g_person_id
538: AND asg.assignment_id = p_assignment_id

Line 567: AND fnd_date.canonical_to_date(prmtr_09)

563: FROM ben_ext_chg_evt_log bec
564: WHERE chg_evt_cd = 'AAT'
565: AND person_id = g_person_id
566: AND bec.business_group_id = p_business_group_id
567: AND fnd_date.canonical_to_date(prmtr_09)
568: BETWEEN g_extract_params(p_business_group_id).extract_start_date
569: AND g_extract_params(p_business_group_id).extract_end_date )
570: AND NOT EXISTS( SELECT 1
571: FROM per_all_assignments_f asg1

Line 612: AND fnd_date.canonical_to_date(prmtr_09)

608: FROM ben_ext_chg_evt_log bec
609: WHERE chg_evt_cd = 'AAT'
610: AND person_id = g_person_id
611: AND bec.business_group_id = p_business_group_id
612: AND fnd_date.canonical_to_date(prmtr_09)
613: BETWEEN g_extract_params(p_business_group_id).extract_start_date
614: AND g_extract_params(p_business_group_id).extract_end_date )
615: --
616: -- Reporting Retro Termination of sec asg

Line 1152: SELECT substr(fnd_date.date_to_canonical(

1148: -- ============================================================================
1149: -- Cursor to get the original date earned for a retro element entry
1150: -- ============================================================================
1151: CURSOR c_get_retro_date_earned(c_element_entry_id in number) IS
1152: SELECT substr(fnd_date.date_to_canonical(
1153: trunc(pay_paywsmee_pkg.get_original_date_earned(c_element_entry_id))
1154: ),1,10)
1155: FROM dual;
1156:

Line 2000: l_ff_value_fmt := Ben_Ext_Fmt.apply_format_mask(Fnd_Date.canonical_to_date(l_ff_value),

1996: IF g_debug THEN
1997: Hr_Utility.set_location('..Applying Date format mask
1998: :ben_ext_fmt.apply_format_mask',55);
1999: END IF;
2000: l_ff_value_fmt := Ben_Ext_Fmt.apply_format_mask(Fnd_Date.canonical_to_date(l_ff_value),
2001: i.frmt_mask_cd);
2002: l_ff_value := l_ff_value_fmt;
2003: END IF;
2004: END IF;

Line 2362: AND fnd_date.canonical_to_date(prmtr_09)

2358: new_val1 new_date
2359: FROM ben_ext_chg_evt_log
2360: WHERE person_id = p_person_id
2361: AND chg_evt_cd = 'COPOS'
2362: AND fnd_date.canonical_to_date(prmtr_09)
2363: BETWEEN g_extract_params(p_business_group_id).extract_start_date AND
2364: g_extract_params(p_business_group_id).extract_end_date
2365: ORDER BY ext_chg_evt_log_id DESC;
2366:

Line 2595: Fnd_Date.canonical_to_date(aei_information1)AND

2591: FROM per_assignment_extra_info
2592: WHERE assignment_id = p_assignment_id
2593: AND information_type = 'NL_USZO_INFO'
2594: AND Trunc(p_effective_date) BETWEEN
2595: Fnd_Date.canonical_to_date(aei_information1)AND
2596: Nvl(Fnd_Date.canonical_to_date(aei_information2),
2597: To_Date('31/12/4712','DD/MM/YYYY'))
2598: AND ROWNUM = 1;
2599: -- Rownum clause has been added on purpose as it is possible that there

Line 2596: Nvl(Fnd_Date.canonical_to_date(aei_information2),

2592: WHERE assignment_id = p_assignment_id
2593: AND information_type = 'NL_USZO_INFO'
2594: AND Trunc(p_effective_date) BETWEEN
2595: Fnd_Date.canonical_to_date(aei_information1)AND
2596: Nvl(Fnd_Date.canonical_to_date(aei_information2),
2597: To_Date('31/12/4712','DD/MM/YYYY'))
2598: AND ROWNUM = 1;
2599: -- Rownum clause has been added on purpose as it is possible that there
2600: -- are two valid rows in the system ( There are no checks in the ASG EIT)

Line 3182: AND fnd_date.canonical_to_date(prmtr_09) BETWEEN c_start_date AND c_end_date

3178: new_val1 new_date
3179: FROM ben_ext_chg_evt_log
3180: WHERE person_id = c_person_id
3181: AND chg_evt_cd = 'COPOS'
3182: AND fnd_date.canonical_to_date(prmtr_09) BETWEEN c_start_date AND c_end_date
3183: ORDER BY ext_chg_evt_log_id desc;
3184:
3185: CURSOR c_ptp_log_rows (c_start_date IN DATE
3186: ,c_end_date IN DATE

Line 3203: AND fnd_date.canonical_to_date(log.prmtr_09)

3199: AND sck.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
3200: AND past.per_system_status = 'ACTIVE_ASSIGN'
3201: AND log.person_id = g_person_id
3202: AND log.chg_evt_cd = 'COPTP'
3203: AND fnd_date.canonical_to_date(log.prmtr_09)
3204: BETWEEN c_start_date AND c_end_date
3205: AND asg.effective_start_date between c_asg_st_dt AND c_asg_ed_dt
3206: AND asg.soft_coding_keyflex_id = log.prmtr_02
3207: AND asg.assignment_id = log.prmtr_01

Line 4104: fnd_date.canonical_to_date(substr(substr(

4100: FETCH c_get_retro_num_value INTO l_retro_ptp_value;
4101: CLOSE c_get_retro_num_value;
4102:
4103: l_retro_age_cal_dt :=
4104: fnd_date.canonical_to_date(substr(substr(
4105: fnd_date.date_to_canonical(
4106: pay_paywsmee_pkg.get_original_date_earned(
4107: temp_rec.element_entry_id)),1,10),1,8)||'01');
4108:

Line 4105: fnd_date.date_to_canonical(

4101: CLOSE c_get_retro_num_value;
4102:
4103: l_retro_age_cal_dt :=
4104: fnd_date.canonical_to_date(substr(substr(
4105: fnd_date.date_to_canonical(
4106: pay_paywsmee_pkg.get_original_date_earned(
4107: temp_rec.element_entry_id)),1,10),1,8)||'01');
4108:
4109:

Line 4156: fnd_date.canonical_to_date(substr(substr(

4152: CLOSE c_get_retro_num_value;
4153:
4154:
4155: l_retro_age_cal_dt :=
4156: fnd_date.canonical_to_date(substr(substr(
4157: fnd_date.date_to_canonical(
4158: pay_paywsmee_pkg.get_original_date_earned(
4159: temp_rec.element_entry_id)),1,10),1,8)||'01');
4160:

Line 4157: fnd_date.date_to_canonical(

4153:
4154:
4155: l_retro_age_cal_dt :=
4156: fnd_date.canonical_to_date(substr(substr(
4157: fnd_date.date_to_canonical(
4158: pay_paywsmee_pkg.get_original_date_earned(
4159: temp_rec.element_entry_id)),1,10),1,8)||'01');
4160:
4161: l_ee_age_at_retro := Get_Age(

Line 4218: fnd_date.canonical_to_date(substr(substr(

4214: CLOSE c_get_retro_num_value;
4215:
4216:
4217: l_retro_age_cal_dt :=
4218: fnd_date.canonical_to_date(substr(substr(
4219: fnd_date.date_to_canonical(
4220: pay_paywsmee_pkg.get_original_date_earned(
4221: temp_rec.element_entry_id)),1,10),1,8)||'01');
4222:

Line 4219: fnd_date.date_to_canonical(

4215:
4216:
4217: l_retro_age_cal_dt :=
4218: fnd_date.canonical_to_date(substr(substr(
4219: fnd_date.date_to_canonical(
4220: pay_paywsmee_pkg.get_original_date_earned(
4221: temp_rec.element_entry_id)),1,10),1,8)||'01');
4222:
4223: l_ee_age_at_retro := Get_Age(

Line 4254: l_reg_09_age_cal_dt := fnd_date.canonical_to_date(

4250: END IF; -- AGe
4251:
4252: END LOOP;
4253:
4254: l_reg_09_age_cal_dt := fnd_date.canonical_to_date(
4255: substr(substr(fnd_date.date_to_canonical(
4256: act_rec.date_earned),1,10),1,8)||'01');
4257:
4258: l_reg_09_age := Get_Age(p_assignment_id

Line 4255: substr(substr(fnd_date.date_to_canonical(

4251:
4252: END LOOP;
4253:
4254: l_reg_09_age_cal_dt := fnd_date.canonical_to_date(
4255: substr(substr(fnd_date.date_to_canonical(
4256: act_rec.date_earned),1,10),1,8)||'01');
4257:
4258: l_reg_09_age := Get_Age(p_assignment_id
4259: ,trunc(l_reg_09_age_cal_dt)) ;

Line 4319: l_beg_new_st := fnd_date.canonical_to_date(to_char(l_new_start,'YYYY/MM')||'/01');

4315: IF trunc(l_new_start) < trunc(l_old_start) THEN
4316: --
4317: -- Derive the beginning date
4318: --
4319: l_beg_new_st := fnd_date.canonical_to_date(to_char(l_new_start,'YYYY/MM')||'/01');
4320: --
4321: -- If the beginnind date is less then the current extract start
4322: -- Loop through the assignment actions to derive contrib amounts
4323: -- of that period

Line 4390: l_beg_new_st := fnd_date.canonical_to_date(to_char(l_old_start,'YYYY/MM')||'/01');

4386:
4387: --
4388: -- Derive the beginning date
4389: --
4390: l_beg_new_st := fnd_date.canonical_to_date(to_char(l_old_start,'YYYY/MM')||'/01');
4391: l_loop_end_date := add_months(fnd_date.canonical_to_date(to_char(l_new_start,'YYYY/MM')||'/01'),1) - 1;
4392: l_loop_end_date := LEAST ( g_extract_params(p_business_group_id).extract_start_date -1
4393: ,l_loop_end_date);
4394: -- GZZ

Line 4391: l_loop_end_date := add_months(fnd_date.canonical_to_date(to_char(l_new_start,'YYYY/MM')||'/01'),1) - 1;

4387: --
4388: -- Derive the beginning date
4389: --
4390: l_beg_new_st := fnd_date.canonical_to_date(to_char(l_old_start,'YYYY/MM')||'/01');
4391: l_loop_end_date := add_months(fnd_date.canonical_to_date(to_char(l_new_start,'YYYY/MM')||'/01'),1) - 1;
4392: l_loop_end_date := LEAST ( g_extract_params(p_business_group_id).extract_start_date -1
4393: ,l_loop_end_date);
4394: -- GZZ
4395: --

Line 4441: substr(fnd_date.date_to_canonical(l_end_new_st),1,10);

4437: l_rec_09_disp := 'Y';
4438: l_rec_09_values(k).processed := 'N';
4439: l_rec_09_values(k).code := l_rec_09(i).code;
4440: l_rec_09_values(k).date_earned :=
4441: substr(fnd_date.date_to_canonical(l_end_new_st),1,10);
4442:
4443: OPEN c_09_abp_data (l_end_new_st,l_rec_09(i).code);
4444: FETCH c_09_abp_data INTO l_09_basis_amt_sent_prev;
4445: IF c_09_abp_data%FOUND THEN

Line 4476: substr(fnd_date.date_to_canonical(l_end_new_st),1,10);

4472: l_rec_09_disp := 'Y';
4473: l_rec_09_values(k).processed := 'N';
4474: l_rec_09_values(k).code := l_rec_09(i).code;
4475: l_rec_09_values(k).date_earned :=
4476: substr(fnd_date.date_to_canonical(l_end_new_st),1,10);
4477: IF l_rec_09_values(k).basis_amount < 0 THEN
4478: l_rec_09_values(k).sign_code := 'C';
4479: END IF;
4480: k := k+ 1;

Line 4534: substr(fnd_date.date_to_canonical(l_09_poj_rec.eff_dt),1,10);

4530: l_rec_09_disp := 'Y';
4531: l_rec_09_values(k).processed := 'N';
4532: l_rec_09_values(k).code := l_09_poj_rec.code;
4533: l_rec_09_values(k).date_earned :=
4534: substr(fnd_date.date_to_canonical(l_09_poj_rec.eff_dt),1,10);
4535:
4536: IF l_rec_09_values(k).basis_amount < 0 THEN
4537: l_rec_09_values(k).sign_code := 'C';
4538: END IF;

Line 4549: substr(fnd_date.date_to_canonical(l_09_poj_rec.eff_dt),1,10);

4545: l_rec_09_disp := 'Y';
4546: l_rec_09_values(k).processed := 'N';
4547: l_rec_09_values(k).code := l_09_poj_rec.code;
4548: l_rec_09_values(k).date_earned :=
4549: substr(fnd_date.date_to_canonical(l_09_poj_rec.eff_dt),1,10);
4550:
4551: IF l_rec_09_values(k).basis_amount < 0 THEN
4552: l_rec_09_values(k).sign_code := 'C';
4553: END IF;

Line 4608: l_rec_31_values(k).date_earned := substr(fnd_date.date_to_canonical(

4604: l_rec_31_values(k).basis_amount := l_retro_ptp_value;
4605: l_rec_31_disp := 'Y';
4606: l_rec_31_values(k).processed := 'N';
4607: l_rec_31_values(k).code := l_rec_31(i).code;
4608: l_rec_31_values(k).date_earned := substr(fnd_date.date_to_canonical(
4609: pay_paywsmee_pkg.get_original_date_earned(
4610: temp_rec.element_entry_id)
4611: ),1,10);
4612:

Line 4646: l_rec_31_values(k).date_earned := substr(fnd_date.date_to_canonical(

4642: l_rec_31_disp := 'Y';
4643: l_rec_31_values(k).processed := 'N';
4644: l_rec_31_values(k).code := l_rec_31(i).code;
4645: l_rec_31_values(k).date_earned := ' ';
4646: l_rec_31_values(k).date_earned := substr(fnd_date.date_to_canonical(
4647: pay_paywsmee_pkg.get_original_date_earned(
4648: temp_rec.element_entry_id)
4649: ),1,10);
4650:

Line 4723: l_rec_41_basis_values(k).date_earned := substr(fnd_date.date_to_canonical(

4719: l_rec_41_basis_values(k).basis_amount := l_retro_ptp_value;
4720: l_basis_rec_41_disp := 'Y';
4721: l_rec_41_basis_values(k).processed := 'N';
4722: l_rec_41_basis_values(k).code := l_basis_rec_41(i).code;
4723: l_rec_41_basis_values(k).date_earned := substr(fnd_date.date_to_canonical(
4724: pay_paywsmee_pkg.get_original_date_earned(
4725: temp_rec.element_entry_id)
4726: ),1,10);
4727:

Line 4761: l_rec_41_basis_values(k).date_earned := substr(fnd_date.date_to_canonical(

4757: l_basis_rec_41_disp := 'Y';
4758: l_rec_41_basis_values(k).processed := 'N';
4759: l_rec_41_basis_values(k).code := l_basis_rec_41(i).code;
4760: l_rec_41_basis_values(k).date_earned := ' ';
4761: l_rec_41_basis_values(k).date_earned := substr(fnd_date.date_to_canonical(
4762: pay_paywsmee_pkg.get_original_date_earned(
4763: temp_rec.element_entry_id)
4764: ),1,10);
4765:

Line 4936: l_rec_41_contrib_values(i_41).date_earned := substr(fnd_date.date_to_canonical(

4932: END IF;
4933: ELSE
4934: IF temp_rec.amount <> 0 THEN
4935: l_rec_41_contrib_values(i_41).contrib_amount := temp_rec.amount;
4936: l_rec_41_contrib_values(i_41).date_earned := substr(fnd_date.date_to_canonical(
4937: temp_rec.date_earned),1,10);
4938: l_rec_41_contrib_values(i_41).code := temp_rec.code;
4939: i_41 := i_41 + 1;
4940: l_contrib_rec_41_disp := 'Y';

Line 4966: l_beg_new_st := fnd_date.canonical_to_date(to_char(l_new_start,'YYYY/MM')||'/01');

4962: IF trunc(l_new_start) < trunc(l_old_start) THEN
4963: --
4964: -- Derive the beginning date
4965: --
4966: l_beg_new_st := fnd_date.canonical_to_date(to_char(l_new_start,'YYYY/MM')||'/01');
4967: --
4968: -- If the beginning date is less then the current extract start
4969: -- Loop through the assignment actions to derive contrib amounts
4970: -- of that period

Line 5056: l_beg_new_st := fnd_date.canonical_to_date(to_char(l_old_start,'YYYY/MM')||'/01');

5052: ELSIF trunc(l_new_start) > trunc(l_old_start) THEN
5053: --
5054: -- Derive the beginning date
5055: --
5056: l_beg_new_st := fnd_date.canonical_to_date(to_char(l_old_start,'YYYY/MM')||'/01');
5057: l_loop_end_date := add_months(fnd_date.canonical_to_date(to_char(l_new_start,'YYYY/MM')||'/01'),1) - 1;
5058: l_loop_end_date := LEAST ( g_extract_params(p_business_group_id).extract_start_date -1
5059: ,l_loop_end_date);
5060: -- GZZ

Line 5057: l_loop_end_date := add_months(fnd_date.canonical_to_date(to_char(l_new_start,'YYYY/MM')||'/01'),1) - 1;

5053: --
5054: -- Derive the beginning date
5055: --
5056: l_beg_new_st := fnd_date.canonical_to_date(to_char(l_old_start,'YYYY/MM')||'/01');
5057: l_loop_end_date := add_months(fnd_date.canonical_to_date(to_char(l_new_start,'YYYY/MM')||'/01'),1) - 1;
5058: l_loop_end_date := LEAST ( g_extract_params(p_business_group_id).extract_start_date -1
5059: ,l_loop_end_date);
5060: -- GZZ
5061: --

Line 5127: l_rec_12_values(i_12).date_earned := substr(fnd_date.date_to_canonical(l_end_new_st),1,10);

5123: hr_utility.set_location('chking amt : '||l_rec12_amt,10);
5124:
5125: IF l_rec12_amt <> 0 THEN
5126: l_rec_12_values(i_12).contrib_amount := l_rec12_amt;
5127: l_rec_12_values(i_12).date_earned := substr(fnd_date.date_to_canonical(l_end_new_st),1,10);
5128: l_rec_12_values(i_12).code := temp_rec.code;
5129: IF length(temp_rec.code) = 1 THEN
5130: l_gxx_code := '0'||temp_rec.code;
5131: ELSE

Line 5174: l_rec_12_values(i_12).date_earned := substr(fnd_date.date_to_canonical(l_end_new_st),1,10);

5170: FETCH c_12_abp_data INTO l_rec_12_amt_sent_prev;
5171: IF c_12_abp_data%FOUND THEN
5172: hr_utility.set_location('GXXXXX the amount is asg : '||l_rec_12_amt_sent_prev,10);
5173: l_rec_12_values(i_12).contrib_amount := -1 * l_rec_12_amt_sent_prev;
5174: l_rec_12_values(i_12).date_earned := substr(fnd_date.date_to_canonical(l_end_new_st),1,10);
5175: l_rec_12_values(i_12).code := temp_rec.code;
5176: i_12 := i_12 + 1;
5177: l_rec_12_disp := 'Y';
5178: ELSE

Line 5185: l_rec_12_values(i_12).date_earned := substr(fnd_date.date_to_canonical(l_end_new_st),1,10);

5181: l_end_new_st,l_gxx_code);
5182: FETCH c_12_retro_abp_data INTO l_rec_12_amt_sent_prev_r;
5183: IF c_12_retro_abp_data%FOUND THEN
5184: l_rec_12_values(i_12).contrib_amount := -1 * l_rec_12_amt_sent_prev_r;
5185: l_rec_12_values(i_12).date_earned := substr(fnd_date.date_to_canonical(l_end_new_st),1,10);
5186: l_rec_12_values(i_12).code := temp_rec.code;
5187: i_12 := i_12 + 1;
5188: l_rec_12_disp := 'Y';
5189: END IF;

Line 5325: substr(fnd_date.date_to_canonical(l_end_new_st),1,10);

5321: l_rec_09_disp := 'Y';
5322: l_rec_09_values(k).processed := 'N';
5323: l_rec_09_values(k).code := l_rec_09(i).code;
5324: l_rec_09_values(k).date_earned :=
5325: substr(fnd_date.date_to_canonical(l_end_new_st),1,10);
5326:
5327: OPEN c_09_abp_data (l_end_new_st,l_rec_09(i).code);
5328: FETCH c_09_abp_data INTO l_09_basis_amt_sent_prev;
5329: IF c_09_abp_data%FOUND THEN

Line 5426: l_rec_12_values(i_12).date_earned := substr(fnd_date.date_to_canonical(l_end_new_st),1,10);

5422: hr_utility.set_location('chking amt : '||l_rec12_amt,10);
5423:
5424: IF l_rec12_amt <> 0 THEN
5425: l_rec_12_values(i_12).contrib_amount := l_rec12_amt;
5426: l_rec_12_values(i_12).date_earned := substr(fnd_date.date_to_canonical(l_end_new_st),1,10);
5427: l_rec_12_values(i_12).code := temp_rec.code;
5428: i_12 := i_12 + 1;
5429: l_rec_12_disp := 'Y';
5430:

Line 5680: Fnd_Date.canonical_to_date(l_ext_params.attribute3);

5676: g_extract_params(i).payroll_id := l_ext_params.value2;
5677: g_extract_params(i).con_set_id := l_ext_params.value3;
5678: g_extract_params(i).org_id := l_ext_params.value4;
5679: g_extract_params(i).extract_start_date :=
5680: Fnd_Date.canonical_to_date(l_ext_params.attribute3);
5681: g_extract_params(i).extract_end_date :=
5682: Fnd_Date.canonical_to_date(l_ext_params.attribute4);
5683: g_extract_params(i).extract_rec_01 := l_ext_params.attribute5;
5684:

Line 5682: Fnd_Date.canonical_to_date(l_ext_params.attribute4);

5678: g_extract_params(i).org_id := l_ext_params.value4;
5679: g_extract_params(i).extract_start_date :=
5680: Fnd_Date.canonical_to_date(l_ext_params.attribute3);
5681: g_extract_params(i).extract_end_date :=
5682: Fnd_Date.canonical_to_date(l_ext_params.attribute4);
5683: g_extract_params(i).extract_rec_01 := l_ext_params.attribute5;
5684:
5685: OPEN csr_leg_code (c_business_group_id => p_business_group_id);
5686: FETCH csr_leg_code INTO g_extract_params(i).legislation_code,

Line 7286: TRUNC(fnd_date.canonical_to_date(aei_information1))

7282: -- Cursor to get the override ABP Pension Salary
7283: --
7284: CURSOR c_get_override_salary IS
7285: SELECT NVL(aei_information6,'0') ,
7286: TRUNC(fnd_date.canonical_to_date(aei_information1))
7287: FROM per_assignment_extra_info
7288: WHERE assignment_id = p_assignment_id
7289: AND information_type = 'NL_ABP_PAR_INFO'
7290: AND aei_information_category = 'NL_ABP_PAR_INFO'

Line 7292: TRUNC(fnd_date.canonical_to_date(aei_information1))

7288: WHERE assignment_id = p_assignment_id
7289: AND information_type = 'NL_ABP_PAR_INFO'
7290: AND aei_information_category = 'NL_ABP_PAR_INFO'
7291: AND p_effective_date BETWEEN
7292: TRUNC(fnd_date.canonical_to_date(aei_information1))
7293: AND TRUNC(NVL(fnd_date.canonical_to_date(aei_information2),hr_api.g_eot))
7294: AND aei_information6 IS NOT NULL;
7295:
7296: l_ret_val NUMBER := 0;

Line 7293: AND TRUNC(NVL(fnd_date.canonical_to_date(aei_information2),hr_api.g_eot))

7289: AND information_type = 'NL_ABP_PAR_INFO'
7290: AND aei_information_category = 'NL_ABP_PAR_INFO'
7291: AND p_effective_date BETWEEN
7292: TRUNC(fnd_date.canonical_to_date(aei_information1))
7293: AND TRUNC(NVL(fnd_date.canonical_to_date(aei_information2),hr_api.g_eot))
7294: AND aei_information6 IS NOT NULL;
7295:
7296: l_ret_val NUMBER := 0;
7297: l_asg_action_id pay_assignment_actions.assignment_action_id%TYPE;

Line 7501: SELECT fnd_date.canonical_to_date(TO_CHAR(c_effective_dt,'YYYY')||'/01/01')

7497: AND past.per_system_status = 'ACTIVE_ASSIGN'
7498: AND asg.assignment_id = c_assign_id;
7499:
7500: CURSOR cur_get_yr(c_effective_dt IN Date) IS
7501: SELECT fnd_date.canonical_to_date(TO_CHAR(c_effective_dt,'YYYY')||'/01/01')
7502: FROM dual ;
7503: --
7504: -- Cursor to check if there are any changes in the pension salary
7505: --

Line 7507: SELECT fnd_date.canonical_to_date(prmtr_02)

7503: --
7504: -- Cursor to check if there are any changes in the pension salary
7505: --
7506: CURSOR c_get_override_start_date IS
7507: SELECT fnd_date.canonical_to_date(prmtr_02)
7508: FROM ben_ext_chg_evt_log
7509: WHERE person_id = g_person_id
7510: AND Fnd_Number.canonical_to_number(prmtr_01) = p_assignment_id
7511: AND chg_eff_dt BETWEEN p_start_date AND p_end_date

Line 7634: (Fnd_Date.canonical_to_date(p_data_element_value),

7630: END IF;
7631:
7632: IF p_data_element_value <> ' ' THEN
7633: p_data_element_value := Ben_Ext_Fmt.apply_format_mask
7634: (Fnd_Date.canonical_to_date(p_data_element_value),
7635: 'YYYY');
7636: ELSE
7637: p_data_element_value := '0000';
7638: END IF;

Line 7693: (Fnd_Date.canonical_to_date(p_data_element_value),

7689: END IF;
7690:
7691: IF p_data_element_value <> ' ' THEN
7692: p_data_element_value := Ben_Ext_Fmt.apply_format_mask
7693: (Fnd_Date.canonical_to_date(p_data_element_value),
7694: 'MM');
7695: ELSE
7696: p_data_element_value := '00';
7697: END IF;

Line 7733: (Fnd_Date.canonical_to_date(p_data_element_value),

7729: IF l_rec_12_values.EXISTS(j) THEN
7730: p_data_element_value := l_rec_12_values(j).date_earned;
7731: IF p_data_element_value <> ' ' THEN
7732: p_data_element_value := Ben_Ext_Fmt.apply_format_mask
7733: (Fnd_Date.canonical_to_date(p_data_element_value),
7734: 'YYYY');
7735: ELSE
7736: p_data_element_value := '0000';
7737: END IF;

Line 7747: (Fnd_Date.canonical_to_date(p_data_element_value),

7743: IF l_rec_41_contrib_values.EXISTS(j) THEN
7744: p_data_element_value := l_rec_41_contrib_values(j).date_earned;
7745: IF p_data_element_value <> ' ' THEN
7746: p_data_element_value := Ben_Ext_Fmt.apply_format_mask
7747: (Fnd_Date.canonical_to_date(p_data_element_value),
7748: 'YYYY');
7749: ELSE
7750: p_data_element_value := '0000';
7751: END IF;

Line 7789: (Fnd_Date.canonical_to_date(p_data_element_value),

7785: IF l_rec_12_values.EXISTS(j) THEN
7786: p_data_element_value := l_rec_12_values(j).date_earned;
7787: IF p_data_element_value <> ' ' THEN
7788: p_data_element_value := Ben_Ext_Fmt.apply_format_mask
7789: (Fnd_Date.canonical_to_date(p_data_element_value),
7790: 'MM');
7791: ELSE
7792: p_data_element_value := '00';
7793: END IF;

Line 7804: (Fnd_Date.canonical_to_date(p_data_element_value),

7800: IF l_rec_41_contrib_values.EXISTS(j) THEN
7801: p_data_element_value := l_rec_41_contrib_values(j).date_earned;
7802: IF p_data_element_value <> ' ' THEN
7803: p_data_element_value := Ben_Ext_Fmt.apply_format_mask
7804: (Fnd_Date.canonical_to_date(p_data_element_value),
7805: 'MM');
7806: ELSE
7807: p_data_element_value := '00';
7808: END IF;

Line 7929: AND fnd_date.canonical_to_date(prmtr_09)

7925: FROM ben_ext_chg_evt_log
7926: WHERE person_id = c_person_id
7927: AND business_group_id = c_business_group_id
7928: AND chg_evt_cd IN ('COLN','COSS','COUN','COG','CODB')
7929: AND fnd_date.canonical_to_date(prmtr_09)
7930: BETWEEN c_ext_start_date AND c_ext_end_date;
7931: --
7932: -- Cursor to fetch the partner's person id
7933: --

Line 7952: AND fnd_date.canonical_to_date(prmtr_09) BETWEEN g_extract_params(p_business_group_id).extract_start_date

7948: FROM ben_ext_chg_evt_log
7949: WHERE person_id = c_person_id
7950: AND business_group_id = p_business_group_id
7951: AND chg_evt_cd IN ('COUN','COLN','CCFN')
7952: AND fnd_date.canonical_to_date(prmtr_09) BETWEEN g_extract_params(p_business_group_id).extract_start_date
7953: AND g_extract_params(p_business_group_id).extract_end_date;
7954:
7955: --
7956: -- Cursor to check if Rec 02 was sent to ABP previously

Line 8130: AND fnd_date.canonical_to_date(prmtr_09)

8126: FROM ben_ext_chg_evt_log
8127: WHERE person_id = c_person_id
8128: AND business_group_id = c_business_group_id
8129: AND chg_evt_cd = 'COM'
8130: AND fnd_date.canonical_to_date(prmtr_09)
8131: BETWEEN c_ext_start_date AND c_ext_end_date;
8132: --
8133: -- Cursor to check if the country of residence is a foreign ( non NL ) country
8134: --

Line 8193: AND fnd_date.canonical_to_date(prmtr_09)

8189: FROM ben_ext_chg_evt_log log
8190: WHERE padr.person_id = log.person_id
8191: AND chg_evt_cd = 'COPR'
8192: AND padr.address_id = log.prmtr_01
8193: AND fnd_date.canonical_to_date(prmtr_09)
8194: BETWEEN c_ext_start_date AND c_ext_end_date);
8195:
8196: --
8197: -- Cursor to check if the address changed from Non NL Country to NL

Line 8220: AND fnd_date.canonical_to_date(prmtr_09)

8216: AND chg_evt_cd = 'COPR'
8217: AND padr.address_id = log.prmtr_01
8218: -- there is a change in primary address
8219: AND log.prmtr_02 IS NOT NULL
8220: AND fnd_date.canonical_to_date(prmtr_09)
8221: BETWEEN c_ext_start_date AND c_ext_end_date
8222: AND EXISTS (SELECT 1
8223: FROM per_addresses_v adr
8224: WHERE adr.person_id = log.person_id

Line 8241: AND fnd_date.canonical_to_date(prmtr_09)

8237: SELECT TO_NUMBER(prmtr_01) addr_id,new_val1 country
8238: FROM ben_ext_chg_evt_log log
8239: WHERE person_id = c_person_id
8240: AND chg_evt_cd = 'COCN'
8241: AND fnd_date.canonical_to_date(prmtr_09)
8242: BETWEEN c_ext_start_date AND c_ext_end_date
8243: ORDER BY ext_chg_evt_log_id DESC;
8244:
8245: --

Line 9365: SELECT Fnd_Date.canonical_to_date(Nvl(hoi.org_information2,Fnd_Date.date_to_canonical(Hr_Api.g_eot)))

9361: AND hoi.organization_id = c_org_id;
9362:
9363: --Cursor to find the participation end date from org level information
9364: CURSOR c_get_org_info(c_org_id IN hr_organization_information.organization_id%TYPE) IS
9365: SELECT Fnd_Date.canonical_to_date(Nvl(hoi.org_information2,Fnd_Date.date_to_canonical(Hr_Api.g_eot)))
9366: FROM hr_organization_information hoi
9367: WHERE hoi.org_information_context = 'PQP_NL_ABP_PT'
9368: AND hoi.org_information3 = To_Char(p_pension_type_id)
9369: AND hoi.org_information6 = 'Y'

Line 9372: AND p_date_earned BETWEEN Fnd_Date.canonical_to_date(hoi.org_information1)

9368: AND hoi.org_information3 = To_Char(p_pension_type_id)
9369: AND hoi.org_information6 = 'Y'
9370: AND NVL(hoi.org_information7,'Y') = 'Y'
9371: AND hoi.organization_id = c_org_id
9372: AND p_date_earned BETWEEN Fnd_Date.canonical_to_date(hoi.org_information1)
9373: AND Fnd_Date.canonical_to_date(Nvl(hoi.org_information2,Fnd_Date.date_to_canonical(Hr_Api.g_eot)));
9374:
9375: BEGIN
9376: -- find the org the assignment is attached to

Line 9373: AND Fnd_Date.canonical_to_date(Nvl(hoi.org_information2,Fnd_Date.date_to_canonical(Hr_Api.g_eot)));

9369: AND hoi.org_information6 = 'Y'
9370: AND NVL(hoi.org_information7,'Y') = 'Y'
9371: AND hoi.organization_id = c_org_id
9372: AND p_date_earned BETWEEN Fnd_Date.canonical_to_date(hoi.org_information1)
9373: AND Fnd_Date.canonical_to_date(Nvl(hoi.org_information2,Fnd_Date.date_to_canonical(Hr_Api.g_eot)));
9374:
9375: BEGIN
9376: -- find the org the assignment is attached to
9377: OPEN c_find_org_id;

Line 9764: Hr_Utility.set_location('greater date : '||Fnd_Date.date_to_canonical(Greatest(

9760: END IF;
9761: IF l_hired = 1 THEN
9762: Hr_Utility.set_location('hire date : '||l_hire_date,99);
9763: Hr_Utility.set_location('new date : '||g_fur_dates(i).new_start,100);
9764: Hr_Utility.set_location('greater date : '||Fnd_Date.date_to_canonical(Greatest(
9765: l_hire_date,Fnd_Date.canonical_to_date(g_fur_dates(i).new_start))),101);
9766: IF g_fur_dates(i).new_start IS NOT NULL THEN
9767: Hr_Utility.set_location('chking the new start date',102);
9768: g_fur_dates(i).new_start := Fnd_Date.date_to_canonical(Greatest(

Line 9765: l_hire_date,Fnd_Date.canonical_to_date(g_fur_dates(i).new_start))),101);

9761: IF l_hired = 1 THEN
9762: Hr_Utility.set_location('hire date : '||l_hire_date,99);
9763: Hr_Utility.set_location('new date : '||g_fur_dates(i).new_start,100);
9764: Hr_Utility.set_location('greater date : '||Fnd_Date.date_to_canonical(Greatest(
9765: l_hire_date,Fnd_Date.canonical_to_date(g_fur_dates(i).new_start))),101);
9766: IF g_fur_dates(i).new_start IS NOT NULL THEN
9767: Hr_Utility.set_location('chking the new start date',102);
9768: g_fur_dates(i).new_start := Fnd_Date.date_to_canonical(Greatest(
9769: l_hire_date,Fnd_Date.canonical_to_date(g_fur_dates(i).new_start)));

Line 9768: g_fur_dates(i).new_start := Fnd_Date.date_to_canonical(Greatest(

9764: Hr_Utility.set_location('greater date : '||Fnd_Date.date_to_canonical(Greatest(
9765: l_hire_date,Fnd_Date.canonical_to_date(g_fur_dates(i).new_start))),101);
9766: IF g_fur_dates(i).new_start IS NOT NULL THEN
9767: Hr_Utility.set_location('chking the new start date',102);
9768: g_fur_dates(i).new_start := Fnd_Date.date_to_canonical(Greatest(
9769: l_hire_date,Fnd_Date.canonical_to_date(g_fur_dates(i).new_start)));
9770: Hr_Utility.set_location('new start date is : '||g_fur_dates(i).new_start,103);
9771: END IF;
9772: IF g_fur_dates(i).old_start IS NOT NULL THEN

Line 9769: l_hire_date,Fnd_Date.canonical_to_date(g_fur_dates(i).new_start)));

9765: l_hire_date,Fnd_Date.canonical_to_date(g_fur_dates(i).new_start))),101);
9766: IF g_fur_dates(i).new_start IS NOT NULL THEN
9767: Hr_Utility.set_location('chking the new start date',102);
9768: g_fur_dates(i).new_start := Fnd_Date.date_to_canonical(Greatest(
9769: l_hire_date,Fnd_Date.canonical_to_date(g_fur_dates(i).new_start)));
9770: Hr_Utility.set_location('new start date is : '||g_fur_dates(i).new_start,103);
9771: END IF;
9772: IF g_fur_dates(i).old_start IS NOT NULL THEN
9773: g_fur_dates(i).old_start := Fnd_Date.date_to_canonical(Greatest(

Line 9773: g_fur_dates(i).old_start := Fnd_Date.date_to_canonical(Greatest(

9769: l_hire_date,Fnd_Date.canonical_to_date(g_fur_dates(i).new_start)));
9770: Hr_Utility.set_location('new start date is : '||g_fur_dates(i).new_start,103);
9771: END IF;
9772: IF g_fur_dates(i).old_start IS NOT NULL THEN
9773: g_fur_dates(i).old_start := Fnd_Date.date_to_canonical(Greatest(
9774: l_hire_date,Fnd_Date.canonical_to_date(g_fur_dates(i).old_start)));
9775: END IF;
9776: END IF;
9777: IF g_fur_dates(i).new_start = g_fur_dates(i).old_start THEN

Line 9774: l_hire_date,Fnd_Date.canonical_to_date(g_fur_dates(i).old_start)));

9770: Hr_Utility.set_location('new start date is : '||g_fur_dates(i).new_start,103);
9771: END IF;
9772: IF g_fur_dates(i).old_start IS NOT NULL THEN
9773: g_fur_dates(i).old_start := Fnd_Date.date_to_canonical(Greatest(
9774: l_hire_date,Fnd_Date.canonical_to_date(g_fur_dates(i).old_start)));
9775: END IF;
9776: END IF;
9777: IF g_fur_dates(i).new_start = g_fur_dates(i).old_start THEN
9778: g_fur_dates(i).old_start := '';

Line 9807: g_fur_dates(i).new_start := Fnd_Date.date_to_canonical(l_hire_date);

9803: IF l_hired = 1 THEN
9804: --chk if there is any run result
9805: FOR c_rec IN c_run_results_exist
9806: LOOP
9807: g_fur_dates(i).new_start := Fnd_Date.date_to_canonical(l_hire_date);
9808: g_fur_dates(i).old_start := '';
9809: --get the end date corresponding to this enrollment
9810: l_ret_val := Get_Participation_End
9811: (p_assignment_id => p_assignment_id

Line 9821: g_fur_dates(i).new_end := Fnd_Date.date_to_canonical(l_end_date) ;

9817: IF l_ret_val = 0 THEN
9818: IF l_end_date = hr_api.g_eot THEN
9819: g_fur_dates(i).new_end := '';
9820: ELSE
9821: g_fur_dates(i).new_end := Fnd_Date.date_to_canonical(l_end_date) ;
9822: END IF;
9823: ELSE
9824: g_fur_dates(i).new_end := '';
9825: END IF;

Line 9853: (Fnd_Date.canonical_to_date(p_data_element_value),

9849: END IF;
9850:
9851: -- p_data_element_value := substr(p_data_element_value,1,10);
9852: p_data_element_value := Ben_Ext_Fmt.apply_format_mask
9853: (Fnd_Date.canonical_to_date(p_data_element_value),
9854: 'YYYYMMDD');
9855:
9856: ELSE
9857: p_data_element_value := '';

Line 9925: AND p_effective_date between fnd_date.canonical_to_date(paei.aei_information1)

9921: AND paei.assignment_id = p_assignment_id
9922: AND fnd_number.canonical_to_number(NVL(aei_information3,-1)) = pty.pension_type_id
9923: AND p_effective_date BETWEEN pty.effective_start_date and pty.effective_end_date
9924: AND pty.pension_sub_category IN ('PPP')
9925: AND p_effective_date between fnd_date.canonical_to_date(paei.aei_information1)
9926: AND fnd_date.canonical_to_date(NVL(paei.aei_information2,
9927: fnd_date.date_to_canonical(hr_api.g_eot)));
9928:
9929: /* Cursor changed for 6670714

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

9922: AND fnd_number.canonical_to_number(NVL(aei_information3,-1)) = pty.pension_type_id
9923: AND p_effective_date BETWEEN pty.effective_start_date and pty.effective_end_date
9924: AND pty.pension_sub_category IN ('PPP')
9925: AND p_effective_date between fnd_date.canonical_to_date(paei.aei_information1)
9926: AND fnd_date.canonical_to_date(NVL(paei.aei_information2,
9927: fnd_date.date_to_canonical(hr_api.g_eot)));
9928:
9929: /* Cursor changed for 6670714
9930: CURSOR c_rr_cur IS

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

9923: AND p_effective_date BETWEEN pty.effective_start_date and pty.effective_end_date
9924: AND pty.pension_sub_category IN ('PPP')
9925: AND p_effective_date between fnd_date.canonical_to_date(paei.aei_information1)
9926: AND fnd_date.canonical_to_date(NVL(paei.aei_information2,
9927: fnd_date.date_to_canonical(hr_api.g_eot)));
9928:
9929: /* Cursor changed for 6670714
9930: CURSOR c_rr_cur IS
9931: SELECT 1

Line 9969: AND p_effective_date BETWEEN fnd_date.canonical_to_date(hoi1.org_information1)

9965: AND hoi1.org_information3 = TO_CHAR(pty.pension_type_id)
9966: AND hoi1.organization_id = paf.organization_id
9967: AND ( NVL(hoi1.org_information6,'N')= 'N'
9968: OR NVL(hoi1.org_information7,'N')= 'N')
9969: AND p_effective_date BETWEEN fnd_date.canonical_to_date(hoi1.org_information1)
9970: AND fnd_date.canonical_to_date(NVL(hoi1.org_information2,
9971: fnd_date.date_to_canonical(hr_api.g_eot))))
9972: ))
9973: AND hoi.org_information_context = 'PQP_NL_ABP_PT'

Line 9970: AND fnd_date.canonical_to_date(NVL(hoi1.org_information2,

9966: AND hoi1.organization_id = paf.organization_id
9967: AND ( NVL(hoi1.org_information6,'N')= 'N'
9968: OR NVL(hoi1.org_information7,'N')= 'N')
9969: AND p_effective_date BETWEEN fnd_date.canonical_to_date(hoi1.org_information1)
9970: AND fnd_date.canonical_to_date(NVL(hoi1.org_information2,
9971: fnd_date.date_to_canonical(hr_api.g_eot))))
9972: ))
9973: AND hoi.org_information_context = 'PQP_NL_ABP_PT'
9974: AND hoi.org_information3 = TO_CHAR(pty.pension_type_id)

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

9967: AND ( NVL(hoi1.org_information6,'N')= 'N'
9968: OR NVL(hoi1.org_information7,'N')= 'N')
9969: AND p_effective_date BETWEEN fnd_date.canonical_to_date(hoi1.org_information1)
9970: AND fnd_date.canonical_to_date(NVL(hoi1.org_information2,
9971: fnd_date.date_to_canonical(hr_api.g_eot))))
9972: ))
9973: AND hoi.org_information_context = 'PQP_NL_ABP_PT'
9974: AND hoi.org_information3 = TO_CHAR(pty.pension_type_id)
9975: AND p_effective_date BETWEEN pty.effective_start_date AND pty.effective_end_date

Line 9979: AND p_effective_date BETWEEN fnd_date.canonical_to_date(hoi.org_information1)

9975: AND p_effective_date BETWEEN pty.effective_start_date AND pty.effective_end_date
9976: AND pty.pension_sub_category IN ('PPP')
9977: AND NVL(hoi.org_information6,'N') = 'Y'
9978: AND NVL(hoi.org_information7,'N') = 'Y'
9979: AND p_effective_date BETWEEN fnd_date.canonical_to_date(hoi.org_information1)
9980: AND fnd_date.canonical_to_date(NVL(hoi.org_information2,
9981: fnd_date.date_to_canonical(hr_api.g_eot)));
9982:
9983:

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

9976: AND pty.pension_sub_category IN ('PPP')
9977: AND NVL(hoi.org_information6,'N') = 'Y'
9978: AND NVL(hoi.org_information7,'N') = 'Y'
9979: AND p_effective_date BETWEEN fnd_date.canonical_to_date(hoi.org_information1)
9980: AND fnd_date.canonical_to_date(NVL(hoi.org_information2,
9981: fnd_date.date_to_canonical(hr_api.g_eot)));
9982:
9983:
9984: BEGIN

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

9977: AND NVL(hoi.org_information6,'N') = 'Y'
9978: AND NVL(hoi.org_information7,'N') = 'Y'
9979: AND p_effective_date BETWEEN fnd_date.canonical_to_date(hoi.org_information1)
9980: AND fnd_date.canonical_to_date(NVL(hoi.org_information2,
9981: fnd_date.date_to_canonical(hr_api.g_eot)));
9982:
9983:
9984: BEGIN
9985: Hr_Utility.set_location('Entering ------ : '||l_proc_name,10);

Line 10121: AND p_effective_date BETWEEN fnd_date.canonical_to_date(hoi1.org_information1)

10117: AND hoi1.org_information3 = TO_CHAR(pty.pension_type_id)
10118: AND hoi1.organization_id = paf.organization_id
10119: AND ( NVL(hoi1.org_information6,'N')= 'N'
10120: OR NVL(hoi1.org_information7,'N')= 'N')
10121: AND p_effective_date BETWEEN fnd_date.canonical_to_date(hoi1.org_information1)
10122: AND fnd_date.canonical_to_date(NVL(hoi1.org_information2,
10123: fnd_date.date_to_canonical(hr_api.g_eot))))
10124: ))
10125: AND hoi.org_information_context = 'PQP_NL_ABP_PT'

Line 10122: AND fnd_date.canonical_to_date(NVL(hoi1.org_information2,

10118: AND hoi1.organization_id = paf.organization_id
10119: AND ( NVL(hoi1.org_information6,'N')= 'N'
10120: OR NVL(hoi1.org_information7,'N')= 'N')
10121: AND p_effective_date BETWEEN fnd_date.canonical_to_date(hoi1.org_information1)
10122: AND fnd_date.canonical_to_date(NVL(hoi1.org_information2,
10123: fnd_date.date_to_canonical(hr_api.g_eot))))
10124: ))
10125: AND hoi.org_information_context = 'PQP_NL_ABP_PT'
10126: AND hoi.org_information3 = TO_CHAR(pty.pension_type_id)

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

10119: AND ( NVL(hoi1.org_information6,'N')= 'N'
10120: OR NVL(hoi1.org_information7,'N')= 'N')
10121: AND p_effective_date BETWEEN fnd_date.canonical_to_date(hoi1.org_information1)
10122: AND fnd_date.canonical_to_date(NVL(hoi1.org_information2,
10123: fnd_date.date_to_canonical(hr_api.g_eot))))
10124: ))
10125: AND hoi.org_information_context = 'PQP_NL_ABP_PT'
10126: AND hoi.org_information3 = TO_CHAR(pty.pension_type_id)
10127: AND p_effective_date BETWEEN pty.effective_start_date AND pty.effective_end_date

Line 10135: AND p_effective_date BETWEEN fnd_date.canonical_to_date(hoi.org_information1)

10131: 'FPU_S',
10132: 'FPU_T')
10133: AND NVL(hoi.org_information6,'N') = 'Y'
10134: AND NVL(hoi.org_information7,'N') = 'Y'
10135: AND p_effective_date BETWEEN fnd_date.canonical_to_date(hoi.org_information1)
10136: AND fnd_date.canonical_to_date(NVL(hoi.org_information2,
10137: fnd_date.date_to_canonical(hr_api.g_eot)))
10138: AND NOT EXISTS (SELECT 1
10139: FROM per_assignment_extra_info paei1

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

10132: 'FPU_T')
10133: AND NVL(hoi.org_information6,'N') = 'Y'
10134: AND NVL(hoi.org_information7,'N') = 'Y'
10135: AND p_effective_date BETWEEN fnd_date.canonical_to_date(hoi.org_information1)
10136: AND fnd_date.canonical_to_date(NVL(hoi.org_information2,
10137: fnd_date.date_to_canonical(hr_api.g_eot)))
10138: AND NOT EXISTS (SELECT 1
10139: FROM per_assignment_extra_info paei1
10140: WHERE paei1.information_type='NL_ABP_RI'

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

10133: AND NVL(hoi.org_information6,'N') = 'Y'
10134: AND NVL(hoi.org_information7,'N') = 'Y'
10135: AND p_effective_date BETWEEN fnd_date.canonical_to_date(hoi.org_information1)
10136: AND fnd_date.canonical_to_date(NVL(hoi.org_information2,
10137: fnd_date.date_to_canonical(hr_api.g_eot)))
10138: AND NOT EXISTS (SELECT 1
10139: FROM per_assignment_extra_info paei1
10140: WHERE paei1.information_type='NL_ABP_RI'
10141: AND paei1.aei_information_category='NL_ABP_RI'

Line 10144: AND p_effective_date BETWEEN fnd_date.canonical_to_date(paei1.aei_information1)

10140: WHERE paei1.information_type='NL_ABP_RI'
10141: AND paei1.aei_information_category='NL_ABP_RI'
10142: AND paei1.assignment_id = p_assignment_id
10143: AND paei1.aei_information3 = 'G'
10144: AND p_effective_date BETWEEN fnd_date.canonical_to_date(paei1.aei_information1)
10145: AND fnd_date.canonical_to_date(NVL(paei1.aei_information2,
10146: fnd_date.date_to_canonical(hr_api.g_eot))));
10147:
10148: CURSOR c_fpu_asg IS

Line 10145: AND fnd_date.canonical_to_date(NVL(paei1.aei_information2,

10141: AND paei1.aei_information_category='NL_ABP_RI'
10142: AND paei1.assignment_id = p_assignment_id
10143: AND paei1.aei_information3 = 'G'
10144: AND p_effective_date BETWEEN fnd_date.canonical_to_date(paei1.aei_information1)
10145: AND fnd_date.canonical_to_date(NVL(paei1.aei_information2,
10146: fnd_date.date_to_canonical(hr_api.g_eot))));
10147:
10148: CURSOR c_fpu_asg IS
10149: SELECT DISTINCT DECODE (pty.pension_sub_category,'FPU_B','S'

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

10142: AND paei1.assignment_id = p_assignment_id
10143: AND paei1.aei_information3 = 'G'
10144: AND p_effective_date BETWEEN fnd_date.canonical_to_date(paei1.aei_information1)
10145: AND fnd_date.canonical_to_date(NVL(paei1.aei_information2,
10146: fnd_date.date_to_canonical(hr_api.g_eot))));
10147:
10148: CURSOR c_fpu_asg IS
10149: SELECT DISTINCT DECODE (pty.pension_sub_category,'FPU_B','S'
10150: ,'FPU_C','S'

Line 10167: AND p_effective_date between fnd_date.canonical_to_date(paei.aei_information1)

10163: 'FPU_E',
10164: 'FPU_R',
10165: 'FPU_S',
10166: 'FPU_T')
10167: AND p_effective_date between fnd_date.canonical_to_date(paei.aei_information1)
10168: AND fnd_date.canonical_to_date(NVL(paei.aei_information2,
10169: fnd_date.date_to_canonical(hr_api.g_eot)))
10170: AND NOT EXISTS (SELECT 1
10171: FROM per_assignment_extra_info paei1

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

10164: 'FPU_R',
10165: 'FPU_S',
10166: 'FPU_T')
10167: AND p_effective_date between fnd_date.canonical_to_date(paei.aei_information1)
10168: AND fnd_date.canonical_to_date(NVL(paei.aei_information2,
10169: fnd_date.date_to_canonical(hr_api.g_eot)))
10170: AND NOT EXISTS (SELECT 1
10171: FROM per_assignment_extra_info paei1
10172: WHERE paei1.information_type='NL_ABP_RI'

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

10165: 'FPU_S',
10166: 'FPU_T')
10167: AND p_effective_date between fnd_date.canonical_to_date(paei.aei_information1)
10168: AND fnd_date.canonical_to_date(NVL(paei.aei_information2,
10169: fnd_date.date_to_canonical(hr_api.g_eot)))
10170: AND NOT EXISTS (SELECT 1
10171: FROM per_assignment_extra_info paei1
10172: WHERE paei1.information_type='NL_ABP_RI'
10173: AND paei1.aei_information_category='NL_ABP_RI'

Line 10176: AND p_effective_date BETWEEN fnd_date.canonical_to_date(paei1.aei_information1)

10172: WHERE paei1.information_type='NL_ABP_RI'
10173: AND paei1.aei_information_category='NL_ABP_RI'
10174: AND paei1.assignment_id = p_assignment_id
10175: AND paei1.aei_information3 = 'G'
10176: AND p_effective_date BETWEEN fnd_date.canonical_to_date(paei1.aei_information1)
10177: AND fnd_date.canonical_to_date(NVL(paei1.aei_information2,
10178: fnd_date.date_to_canonical(hr_api.g_eot))));
10179:
10180: BEGIN

Line 10177: AND fnd_date.canonical_to_date(NVL(paei1.aei_information2,

10173: AND paei1.aei_information_category='NL_ABP_RI'
10174: AND paei1.assignment_id = p_assignment_id
10175: AND paei1.aei_information3 = 'G'
10176: AND p_effective_date BETWEEN fnd_date.canonical_to_date(paei1.aei_information1)
10177: AND fnd_date.canonical_to_date(NVL(paei1.aei_information2,
10178: fnd_date.date_to_canonical(hr_api.g_eot))));
10179:
10180: BEGIN
10181:

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

10174: AND paei1.assignment_id = p_assignment_id
10175: AND paei1.aei_information3 = 'G'
10176: AND p_effective_date BETWEEN fnd_date.canonical_to_date(paei1.aei_information1)
10177: AND fnd_date.canonical_to_date(NVL(paei1.aei_information2,
10178: fnd_date.date_to_canonical(hr_api.g_eot))));
10179:
10180: BEGIN
10181:
10182: Hr_Utility.set_location('Entering ------ : '||l_proc_name,5);

Line 10239: AND p_effective_date BETWEEN Fnd_Date.canonical_to_date(aei_information1)

10235: CURSOR c_get_contrib_kind IS
10236: SELECT Substr(Nvl(aei_information4,'G'),0,1)
10237: FROM per_assignment_extra_info
10238: WHERE assignment_id = p_assignment_id
10239: AND p_effective_date BETWEEN Fnd_Date.canonical_to_date(aei_information1)
10240: AND Fnd_Date.canonical_to_date(Nvl(aei_information2,Fnd_Date.date_to_canonical(Hr_Api.g_eot)))
10241: AND aei_information_category = 'NL_ABP_RI'
10242: AND information_type = 'NL_ABP_RI';
10243:

Line 10240: AND Fnd_Date.canonical_to_date(Nvl(aei_information2,Fnd_Date.date_to_canonical(Hr_Api.g_eot)))

10236: SELECT Substr(Nvl(aei_information4,'G'),0,1)
10237: FROM per_assignment_extra_info
10238: WHERE assignment_id = p_assignment_id
10239: AND p_effective_date BETWEEN Fnd_Date.canonical_to_date(aei_information1)
10240: AND Fnd_Date.canonical_to_date(Nvl(aei_information2,Fnd_Date.date_to_canonical(Hr_Api.g_eot)))
10241: AND aei_information_category = 'NL_ABP_RI'
10242: AND information_type = 'NL_ABP_RI';
10243:
10244: BEGIN

Line 10277: SELECT fnd_date.canonical_to_date(aei_information1) start_dt

10273: ,p_error_message OUT NOCOPY VARCHAR2
10274: ) RETURN NUMBER IS
10275:
10276: CURSOR c_asg_kind_info IS
10277: SELECT fnd_date.canonical_to_date(aei_information1) start_dt
10278: ,fnd_date.canonical_to_date(NVL(aei_information2,'4712/12/31 00:00:00')) end_dt
10279: ,aei_information4 kind
10280: ,LEAST(fnd_number.canonical_to_number(aei_information5),1) * 100 value
10281: FROM per_assignment_extra_info paei

Line 10278: ,fnd_date.canonical_to_date(NVL(aei_information2,'4712/12/31 00:00:00')) end_dt

10274: ) RETURN NUMBER IS
10275:
10276: CURSOR c_asg_kind_info IS
10277: SELECT fnd_date.canonical_to_date(aei_information1) start_dt
10278: ,fnd_date.canonical_to_date(NVL(aei_information2,'4712/12/31 00:00:00')) end_dt
10279: ,aei_information4 kind
10280: ,LEAST(fnd_number.canonical_to_number(aei_information5),1) * 100 value
10281: FROM per_assignment_extra_info paei
10282: WHERE paei.assignment_id = p_assignment_id

Line 10285: AND fnd_date.canonical_to_date(aei_information1)

10281: FROM per_assignment_extra_info paei
10282: WHERE paei.assignment_id = p_assignment_id
10283: AND aei_information4 IS NOT NULL
10284: AND paei.information_type = 'NL_ABP_PAR_INFO'
10285: AND fnd_date.canonical_to_date(aei_information1)
10286: BETWEEN g_extract_params(p_business_group_id).extract_start_date
10287: AND g_extract_params(p_business_group_id).extract_end_date
10288: AND NOT EXISTS ( SELECT 1
10289: FROM per_assignment_extra_info paei1

Line 10292: AND fnd_date.canonical_to_date(paei1.aei_information1) <

10288: AND NOT EXISTS ( SELECT 1
10289: FROM per_assignment_extra_info paei1
10290: WHERE paei1.assignment_id = p_assignment_id
10291: AND paei1.information_type = 'NL_ABP_PAR_INFO'
10292: AND fnd_date.canonical_to_date(paei1.aei_information1) <
10293: g_extract_params(p_business_group_id).extract_start_date
10294: AND paei1.aei_information4 IS NOT NULL
10295: and paei1.aei_information4 = paei.aei_information4
10296: AND fnd_date.canonical_to_date(NVL(paei1.aei_information2,'4712/12/31 00:00:00')) =

Line 10296: AND fnd_date.canonical_to_date(NVL(paei1.aei_information2,'4712/12/31 00:00:00')) =

10292: AND fnd_date.canonical_to_date(paei1.aei_information1) <
10293: g_extract_params(p_business_group_id).extract_start_date
10294: AND paei1.aei_information4 IS NOT NULL
10295: and paei1.aei_information4 = paei.aei_information4
10296: AND fnd_date.canonical_to_date(NVL(paei1.aei_information2,'4712/12/31 00:00:00')) =
10297: fnd_date.canonical_to_date(NVL(paei.aei_information1,'4712/12/31 00:00:00')) - 1
10298: )
10299: UNION
10300: SELECT fnd_date.canonical_to_date(aei_information1) start_dt

Line 10297: fnd_date.canonical_to_date(NVL(paei.aei_information1,'4712/12/31 00:00:00')) - 1

10293: g_extract_params(p_business_group_id).extract_start_date
10294: AND paei1.aei_information4 IS NOT NULL
10295: and paei1.aei_information4 = paei.aei_information4
10296: AND fnd_date.canonical_to_date(NVL(paei1.aei_information2,'4712/12/31 00:00:00')) =
10297: fnd_date.canonical_to_date(NVL(paei.aei_information1,'4712/12/31 00:00:00')) - 1
10298: )
10299: UNION
10300: SELECT fnd_date.canonical_to_date(aei_information1) start_dt
10301: ,fnd_date.canonical_to_date(NVL(aei_information2,'4712/12/31 00:00:00')) end_dt

Line 10300: SELECT fnd_date.canonical_to_date(aei_information1) start_dt

10296: AND fnd_date.canonical_to_date(NVL(paei1.aei_information2,'4712/12/31 00:00:00')) =
10297: fnd_date.canonical_to_date(NVL(paei.aei_information1,'4712/12/31 00:00:00')) - 1
10298: )
10299: UNION
10300: SELECT fnd_date.canonical_to_date(aei_information1) start_dt
10301: ,fnd_date.canonical_to_date(NVL(aei_information2,'4712/12/31 00:00:00')) end_dt
10302: ,aei_information4 kind
10303: ,LEAST(fnd_number.canonical_to_number(aei_information5),1) * 100 value
10304: FROM per_assignment_extra_info paei

Line 10301: ,fnd_date.canonical_to_date(NVL(aei_information2,'4712/12/31 00:00:00')) end_dt

10297: fnd_date.canonical_to_date(NVL(paei.aei_information1,'4712/12/31 00:00:00')) - 1
10298: )
10299: UNION
10300: SELECT fnd_date.canonical_to_date(aei_information1) start_dt
10301: ,fnd_date.canonical_to_date(NVL(aei_information2,'4712/12/31 00:00:00')) end_dt
10302: ,aei_information4 kind
10303: ,LEAST(fnd_number.canonical_to_number(aei_information5),1) * 100 value
10304: FROM per_assignment_extra_info paei
10305: WHERE paei.assignment_id = p_assignment_id

Line 10308: AND fnd_date.canonical_to_date(aei_information1)

10304: FROM per_assignment_extra_info paei
10305: WHERE paei.assignment_id = p_assignment_id
10306: AND aei_information4 IS NOT NULL
10307: AND paei.information_type = 'NL_ABP_PAR_INFO'
10308: AND fnd_date.canonical_to_date(aei_information1)
10309: < g_extract_params(p_business_group_id).extract_start_date
10310: AND EXISTS ( SELECT 1 FROM
10311: ben_ext_chg_evt_log blog
10312: WHERE blog.person_id = g_person_id

Line 10316: AND fnd_date.canonical_to_date(blog.prmtr_09) BETWEEN

10312: WHERE blog.person_id = g_person_id
10313: AND blog.chg_evt_cd = 'COAPKOP'
10314: AND blog.prmtr_10 = paei.assignment_id
10315: AND fnd_number.canonical_to_number(blog.prmtr_03) = paei.assignment_extra_info_id
10316: AND fnd_date.canonical_to_date(blog.prmtr_09) BETWEEN
10317: g_extract_params(p_business_group_id).extract_start_date
10318: AND g_extract_params(p_business_group_id).extract_end_date)
10319: ORDER BY start_dt;
10320:

Line 10329: AND fnd_date.canonical_to_date(aei_information1) = c_end_date + 1;

10325: WHERE paei.assignment_id = p_assignment_id
10326: AND aei_information4 = c_ptpn_kind
10327: AND aei_information4 is not null
10328: AND paei.information_type = 'NL_ABP_PAR_INFO'
10329: AND fnd_date.canonical_to_date(aei_information1) = c_end_date + 1;
10330:
10331: l_subcat VARCHAR2(100);
10332: i NUMBER := 0;
10333: x NUMBER := 0;

Line 10417: SELECT fnd_date.canonical_to_date(aei_information1) start_dt

10413: AND pet.business_group_id = p_business_group_id
10414: AND pei.EEI_INFORMATION12 = p_sub_cat;
10415:
10416: CURSOR c_asg_info ( c_pt_id IN NUMBER) IS
10417: SELECT fnd_date.canonical_to_date(aei_information1) start_dt
10418: ,fnd_date.canonical_to_date(NVL(aei_information2,'4712/12/31 00:00:00')) end_dt
10419: FROM per_assignment_extra_info paei
10420: WHERE paei.assignment_id = p_assignment_id
10421: AND fnd_number.canonical_to_number(aei_information3) = c_pt_id

Line 10418: ,fnd_date.canonical_to_date(NVL(aei_information2,'4712/12/31 00:00:00')) end_dt

10414: AND pei.EEI_INFORMATION12 = p_sub_cat;
10415:
10416: CURSOR c_asg_info ( c_pt_id IN NUMBER) IS
10417: SELECT fnd_date.canonical_to_date(aei_information1) start_dt
10418: ,fnd_date.canonical_to_date(NVL(aei_information2,'4712/12/31 00:00:00')) end_dt
10419: FROM per_assignment_extra_info paei
10420: WHERE paei.assignment_id = p_assignment_id
10421: AND fnd_number.canonical_to_number(aei_information3) = c_pt_id
10422: AND paei.information_type = 'NL_ABP_PI'

Line 10423: AND fnd_date.canonical_to_date(aei_information1)

10419: FROM per_assignment_extra_info paei
10420: WHERE paei.assignment_id = p_assignment_id
10421: AND fnd_number.canonical_to_number(aei_information3) = c_pt_id
10422: AND paei.information_type = 'NL_ABP_PI'
10423: AND fnd_date.canonical_to_date(aei_information1)
10424: BETWEEN g_extract_params(p_business_group_id).extract_start_date
10425: AND g_extract_params(p_business_group_id).extract_end_date
10426: AND NOT EXISTS ( SELECT 1
10427: FROM per_assignment_extra_info paei1

Line 10430: AND fnd_date.canonical_to_date(paei1.aei_information1) <

10426: AND NOT EXISTS ( SELECT 1
10427: FROM per_assignment_extra_info paei1
10428: WHERE paei1.assignment_id = p_assignment_id
10429: AND paei1.information_type = 'NL_ABP_PI'
10430: AND fnd_date.canonical_to_date(paei1.aei_information1) <
10431: g_extract_params(p_business_group_id).extract_start_date
10432: AND fnd_number.canonical_to_number(paei1.aei_information3) = c_pt_id
10433: AND fnd_date.canonical_to_date(NVL(paei1.aei_information2,'4712/12/31 00:00:00')) =
10434: fnd_date.canonical_to_date(NVL(paei.aei_information1,'4712/12/31 00:00:00')) -1

Line 10433: AND fnd_date.canonical_to_date(NVL(paei1.aei_information2,'4712/12/31 00:00:00')) =

10429: AND paei1.information_type = 'NL_ABP_PI'
10430: AND fnd_date.canonical_to_date(paei1.aei_information1) <
10431: g_extract_params(p_business_group_id).extract_start_date
10432: AND fnd_number.canonical_to_number(paei1.aei_information3) = c_pt_id
10433: AND fnd_date.canonical_to_date(NVL(paei1.aei_information2,'4712/12/31 00:00:00')) =
10434: fnd_date.canonical_to_date(NVL(paei.aei_information1,'4712/12/31 00:00:00')) -1
10435: )
10436: UNION
10437: SELECT fnd_date.canonical_to_date(aei_information1) start_dt

Line 10434: fnd_date.canonical_to_date(NVL(paei.aei_information1,'4712/12/31 00:00:00')) -1

10430: AND fnd_date.canonical_to_date(paei1.aei_information1) <
10431: g_extract_params(p_business_group_id).extract_start_date
10432: AND fnd_number.canonical_to_number(paei1.aei_information3) = c_pt_id
10433: AND fnd_date.canonical_to_date(NVL(paei1.aei_information2,'4712/12/31 00:00:00')) =
10434: fnd_date.canonical_to_date(NVL(paei.aei_information1,'4712/12/31 00:00:00')) -1
10435: )
10436: UNION
10437: SELECT fnd_date.canonical_to_date(aei_information1) start_dt
10438: ,fnd_date.canonical_to_date(NVL(aei_information2,'4712/12/31 00:00:00')) end_dt

Line 10437: SELECT fnd_date.canonical_to_date(aei_information1) start_dt

10433: AND fnd_date.canonical_to_date(NVL(paei1.aei_information2,'4712/12/31 00:00:00')) =
10434: fnd_date.canonical_to_date(NVL(paei.aei_information1,'4712/12/31 00:00:00')) -1
10435: )
10436: UNION
10437: SELECT fnd_date.canonical_to_date(aei_information1) start_dt
10438: ,fnd_date.canonical_to_date(NVL(aei_information2,'4712/12/31 00:00:00')) end_dt
10439: FROM per_assignment_extra_info paei
10440: WHERE paei.assignment_id = p_assignment_id
10441: AND fnd_number.canonical_to_number(aei_information3) = c_pt_id

Line 10438: ,fnd_date.canonical_to_date(NVL(aei_information2,'4712/12/31 00:00:00')) end_dt

10434: fnd_date.canonical_to_date(NVL(paei.aei_information1,'4712/12/31 00:00:00')) -1
10435: )
10436: UNION
10437: SELECT fnd_date.canonical_to_date(aei_information1) start_dt
10438: ,fnd_date.canonical_to_date(NVL(aei_information2,'4712/12/31 00:00:00')) end_dt
10439: FROM per_assignment_extra_info paei
10440: WHERE paei.assignment_id = p_assignment_id
10441: AND fnd_number.canonical_to_number(aei_information3) = c_pt_id
10442: AND paei.information_type = 'NL_ABP_PI'

Line 10443: AND fnd_date.canonical_to_date(aei_information1)

10439: FROM per_assignment_extra_info paei
10440: WHERE paei.assignment_id = p_assignment_id
10441: AND fnd_number.canonical_to_number(aei_information3) = c_pt_id
10442: AND paei.information_type = 'NL_ABP_PI'
10443: AND fnd_date.canonical_to_date(aei_information1)
10444: < g_extract_params(p_business_group_id).extract_start_date
10445: AND EXISTS ( SELECT 1 FROM
10446: ben_ext_chg_evt_log blog
10447: WHERE blog.person_id = g_person_id

Line 10451: AND fnd_date.canonical_to_date(blog.prmtr_09) BETWEEN

10447: WHERE blog.person_id = g_person_id
10448: AND blog.chg_evt_cd = 'COAPP'
10449: AND blog.prmtr_10 = paei.assignment_id
10450: AND fnd_number.canonical_to_number(blog.prmtr_03) = paei.assignment_extra_info_id
10451: AND fnd_date.canonical_to_date(blog.prmtr_09) BETWEEN
10452: g_extract_params(p_business_group_id).extract_start_date
10453: AND g_extract_params(p_business_group_id).extract_end_date)
10454: ORDER BY start_dt ;
10455:

Line 10463: AND fnd_date.canonical_to_date(aei_information1) = c_end_date + 1;

10459: FROM per_assignment_extra_info paei
10460: WHERE paei.assignment_id = p_assignment_id
10461: AND fnd_number.canonical_to_number(aei_information3) = c_pt_id
10462: AND paei.information_type = 'NL_ABP_PI'
10463: AND fnd_date.canonical_to_date(aei_information1) = c_end_date + 1;
10464:
10465: l_subcat VARCHAR2(100);
10466: i NUMBER := 0;
10467: x NUMBER := 0;

Line 10591: substr(fnd_date.date_to_canonical(l_end_new_st),1,10);

10587: l_rec_09_disp := 'Y';
10588: l_rec_09_values(k).processed := 'N';
10589: l_rec_09_values(k).code := l_rec_09(i).code;
10590: l_rec_09_values(k).date_earned :=
10591: substr(fnd_date.date_to_canonical(l_end_new_st),1,10);
10592:
10593: OPEN c_09_abp_data (l_end_new_st,l_rec_09(i).code);
10594: FETCH c_09_abp_data INTO l_09_basis_amt_sent_prev;
10595: IF c_09_abp_data%FOUND THEN

Line 10657: AND p_effective_date BETWEEN Fnd_Date.canonical_to_date(aei_information1)

10653: FROM per_assignment_extra_info
10654: WHERE information_type = 'NL_ABP_PAR_INFO'
10655: AND aei_information_category = 'NL_ABP_PAR_INFO'
10656: AND assignment_id = p_assignment_id
10657: AND p_effective_date BETWEEN Fnd_Date.canonical_to_date(aei_information1)
10658: AND Fnd_Date.canonical_to_date(Nvl(aei_information2,Fnd_Date.date_to_canonical(Hr_Api.g_eot)));
10659: --
10660: -- Cursor to get the hire date of the person
10661: --

Line 10658: AND Fnd_Date.canonical_to_date(Nvl(aei_information2,Fnd_Date.date_to_canonical(Hr_Api.g_eot)));

10654: WHERE information_type = 'NL_ABP_PAR_INFO'
10655: AND aei_information_category = 'NL_ABP_PAR_INFO'
10656: AND assignment_id = p_assignment_id
10657: AND p_effective_date BETWEEN Fnd_Date.canonical_to_date(aei_information1)
10658: AND Fnd_Date.canonical_to_date(Nvl(aei_information2,Fnd_Date.date_to_canonical(Hr_Api.g_eot)));
10659: --
10660: -- Cursor to get the hire date of the person
10661: --
10662: CURSOR c_hire_dt IS

Line 10800: AND fnd_date.canonical_to_date(prmtr_09) BETWEEN c_start_date AND c_end_date

10796: new_val1 new_date
10797: FROM ben_ext_chg_evt_log
10798: WHERE person_id = c_person_id
10799: AND chg_evt_cd = 'COPOS'
10800: AND fnd_date.canonical_to_date(prmtr_09) BETWEEN c_start_date AND c_end_date
10801: ORDER BY ext_chg_evt_log_id desc;
10802:
10803: CURSOR c_chk_ptpn_continues (c_end_date IN DATE,
10804: c_pt_id IN NUMBER) IS

Line 10810: AND fnd_date.canonical_to_date(aei_information1) = c_end_date + 1;

10806: FROM per_assignment_extra_info paei
10807: WHERE paei.assignment_id = p_assignment_id
10808: AND fnd_number.canonical_to_number(aei_information3) = c_pt_id
10809: AND paei.information_type = 'NL_ABP_PI'
10810: AND fnd_date.canonical_to_date(aei_information1) = c_end_date + 1;
10811:
10812: CURSOR c_chk_ptpn_continues_kind (c_end_date IN DATE
10813: ,c_ptpn_kind IN VARCHAR2) IS
10814: SELECT 1

Line 10820: AND fnd_date.canonical_to_date(aei_information1) = c_end_date + 1;

10816: WHERE paei.assignment_id = p_assignment_id
10817: AND aei_information4 = c_ptpn_kind
10818: AND aei_information4 is not null
10819: AND paei.information_type = 'NL_ABP_PAR_INFO'
10820: AND fnd_date.canonical_to_date(aei_information1) = c_end_date + 1;
10821:
10822: CURSOR c_rec05_sub_cat IS
10823: SELECT lookup_code sub_cat, DECODE(lookup_code,'PPP','1'
10824: ,'OPNP','G'

Line 11110: g_rec05_rows(i).new_start := Fnd_Date.date_to_canonical(l_hire_date);

11106: IF g_abp_processed_flag = 1 THEN
11107: --
11108: -- ABP Pensions has been processed on new hire. Create a new hire row
11109: --
11110: g_rec05_rows(i).new_start := Fnd_Date.date_to_canonical(l_hire_date);
11111: g_rec05_rows(i).dt_chg := NULL;
11112: g_rec05_rows(i).old_start := NULL;
11113: g_rec05_rows(i).end_reason := NULL;
11114: g_rec05_rows(i).eddt_chg := NULL;

Line 11180: g_rec05_rows(l_new_hire_row).old_start := Fnd_Date.date_to_canonical(l_hire_date);

11176: IF l_hired = 1 THEN
11177: --
11178: -- Update the existing row for reporting the change
11179: --
11180: g_rec05_rows(l_new_hire_row).old_start := Fnd_Date.date_to_canonical(l_hire_date);
11181: g_rec05_rows(l_new_hire_row).new_start := fnd_date.date_to_canonical(l_new_hire_dt);
11182:
11183: OPEN c_copos_ptp_chg (c_asg_id => p_assignment_id
11184: ,c_eff_date => trunc(l_new_hire_dt));

Line 11181: g_rec05_rows(l_new_hire_row).new_start := fnd_date.date_to_canonical(l_new_hire_dt);

11177: --
11178: -- Update the existing row for reporting the change
11179: --
11180: g_rec05_rows(l_new_hire_row).old_start := Fnd_Date.date_to_canonical(l_hire_date);
11181: g_rec05_rows(l_new_hire_row).new_start := fnd_date.date_to_canonical(l_new_hire_dt);
11182:
11183: OPEN c_copos_ptp_chg (c_asg_id => p_assignment_id
11184: ,c_eff_date => trunc(l_new_hire_dt));
11185: FETCH c_copos_ptp_chg INTO l_copos_ptp;

Line 11197: g_rec05_rows(i).new_start := Fnd_Date.date_to_canonical(l_new_hire_dt);

11193: -- Create a new Record 05 for reporting the change in hire dt
11194: --
11195: g_rec05_rows(i).new_end := NULL;
11196: g_rec05_rows(i).old_end := NULL;
11197: g_rec05_rows(i).new_start := Fnd_Date.date_to_canonical(l_new_hire_dt);
11198: g_rec05_rows(i).old_start := Fnd_Date.date_to_canonical(l_old_hire_dt);
11199: g_rec05_rows(i).partn_kind := NULL;
11200: g_rec05_rows(i).partn_value := ' ';
11201: g_rec05_rows(i).part_time_perc := NULL;

Line 11198: g_rec05_rows(i).old_start := Fnd_Date.date_to_canonical(l_old_hire_dt);

11194: --
11195: g_rec05_rows(i).new_end := NULL;
11196: g_rec05_rows(i).old_end := NULL;
11197: g_rec05_rows(i).new_start := Fnd_Date.date_to_canonical(l_new_hire_dt);
11198: g_rec05_rows(i).old_start := Fnd_Date.date_to_canonical(l_old_hire_dt);
11199: g_rec05_rows(i).partn_kind := NULL;
11200: g_rec05_rows(i).partn_value := ' ';
11201: g_rec05_rows(i).part_time_perc := NULL;
11202: g_rec05_rows(i).dt_chg := NULL;

Line 11260: g_rec05_rows(i).dt_chg := fnd_date.date_to_canonical

11256: g_rec05_rows(i).old_start := NULL;
11257: g_rec05_rows(i).old_end := NULL;
11258: g_rec05_rows(i).new_start := NULL;
11259: g_rec05_rows(i).new_end := NULL;
11260: g_rec05_rows(i).dt_chg := fnd_date.date_to_canonical
11261: (l_rec_05_retro_ptp(l_retro_ptp_index).start_date);
11262: hr_utility.set_location('....Change date st : '||g_rec05_rows(i).dt_chg,24);
11263: IF l_rec_05_retro_ptp(l_retro_ptp_index).end_date >=
11264: trunc(g_extract_params(p_business_group_id).extract_end_date) THEN

Line 11267: g_rec05_rows(i).eddt_chg := fnd_date.date_to_canonical(l_rec_05_retro_ptp(l_retro_ptp_index).end_date);

11263: IF l_rec_05_retro_ptp(l_retro_ptp_index).end_date >=
11264: trunc(g_extract_params(p_business_group_id).extract_end_date) THEN
11265: g_rec05_rows(i).eddt_chg := NULL;
11266: ELSE
11267: g_rec05_rows(i).eddt_chg := fnd_date.date_to_canonical(l_rec_05_retro_ptp(l_retro_ptp_index).end_date);
11268: END IF;
11269: hr_utility.set_location('....Change date End : '||g_rec05_rows(i).eddt_chg,24);
11270: g_rec05_rows(i).part_time_perc := l_rec_05_retro_ptp(l_retro_ptp_index).part_time_perc;
11271: hr_utility.set_location('....Changed PTP : '||g_rec05_rows(i).part_time_perc,24);

Line 11326: g_rec05_rows(i).new_end := NVL(fnd_date.date_to_canonical(

11322: IF c_prev_term_dt%NOTFOUND THEN
11323: --
11324: -- Termination was never reported to ABP Report it now.
11325: --
11326: g_rec05_rows(i).new_end := NVL(fnd_date.date_to_canonical(
11327: l_asg_termination_dt),'');
11328: g_rec05_rows(i).old_end := NULL;
11329: g_rec05_rows(i).new_start := '';
11330: g_rec05_rows(i).old_start := '';

Line 11345: ELSIF c_prev_term_dt%FOUND AND TRUNC(fnd_date.canonical_to_date(l_prev_term_dt))

11341: l_terminated_row := i;
11342: i := i + 1;
11343: g_count_05 := i;
11344: l_term_rows_exist := 1;
11345: ELSIF c_prev_term_dt%FOUND AND TRUNC(fnd_date.canonical_to_date(l_prev_term_dt))
11346: <> TRUNC(l_asg_termination_dt) + 1
11347: THEN
11348: --
11349: -- Termination date has changed from the prev reported value.

Line 11352: g_rec05_rows(i).new_end := nvl(fnd_date.date_to_canonical(

11348: --
11349: -- Termination date has changed from the prev reported value.
11350: -- Report the old and new dates
11351: --
11352: g_rec05_rows(i).new_end := nvl(fnd_date.date_to_canonical(
11353: l_asg_termination_dt),'');
11354: OPEN c_prev_term_rev(l_seq_num,l_prev_term_dt);
11355: FETCH c_prev_term_rev INTO l_prev_term_rev;
11356: IF c_prev_term_rev%NOTFOUND THEN

Line 11357: g_rec05_rows(i).old_end := fnd_date.date_to_canonical(

11353: l_asg_termination_dt),'');
11354: OPEN c_prev_term_rev(l_seq_num,l_prev_term_dt);
11355: FETCH c_prev_term_rev INTO l_prev_term_rev;
11356: IF c_prev_term_rev%NOTFOUND THEN
11357: g_rec05_rows(i).old_end := fnd_date.date_to_canonical(
11358: fnd_date.canonical_to_date(l_prev_term_dt) - 1);
11359: ELSE
11360: g_rec05_rows(i).old_end := NULL;
11361: END IF;

Line 11358: fnd_date.canonical_to_date(l_prev_term_dt) - 1);

11354: OPEN c_prev_term_rev(l_seq_num,l_prev_term_dt);
11355: FETCH c_prev_term_rev INTO l_prev_term_rev;
11356: IF c_prev_term_rev%NOTFOUND THEN
11357: g_rec05_rows(i).old_end := fnd_date.date_to_canonical(
11358: fnd_date.canonical_to_date(l_prev_term_dt) - 1);
11359: ELSE
11360: g_rec05_rows(i).old_end := NULL;
11361: END IF;
11362: g_rec05_rows(i).new_start := '';

Line 11402: g_rec05_rows(i).old_end := fnd_date.date_to_canonical(

11398: --
11399: -- Termination reversal was never reported to ABP Report it now.
11400: --
11401: g_rec05_rows(i).new_end := NULL;
11402: g_rec05_rows(i).old_end := fnd_date.date_to_canonical(
11403: fnd_date.canonical_to_date(l_prev_term_dt) - 1);
11404: g_rec05_rows(i).new_start := '';
11405: g_rec05_rows(i).old_start := '';
11406: g_rec05_rows(i).partn_kind := NULL;

Line 11403: fnd_date.canonical_to_date(l_prev_term_dt) - 1);

11399: -- Termination reversal was never reported to ABP Report it now.
11400: --
11401: g_rec05_rows(i).new_end := NULL;
11402: g_rec05_rows(i).old_end := fnd_date.date_to_canonical(
11403: fnd_date.canonical_to_date(l_prev_term_dt) - 1);
11404: g_rec05_rows(i).new_start := '';
11405: g_rec05_rows(i).old_start := '';
11406: g_rec05_rows(i).partn_kind := NULL;
11407: g_rec05_rows(i).partn_value := ' ';

Line 11450: g_rec05_rows(l_terminated_row).old_start := fnd_date.date_to_canonical(l_hf_pos_dt);

11446: FETCH c_hf_pos_cur INTO l_hf_pos_dt;
11447: IF c_hf_pos_cur%FOUND THEN
11448: g_rec05_rows(l_terminated_row).new_start := NULL;
11449: g_rec05_rows(l_terminated_row).dt_chg := NULL;
11450: g_rec05_rows(l_terminated_row).old_start := fnd_date.date_to_canonical(l_hf_pos_dt);
11451: g_rec05_rows(l_terminated_row).end_reason := NULL;
11452: g_rec05_rows(l_terminated_row).eddt_chg := NULL;
11453: g_rec05_rows(l_terminated_row).end_reason := NULL;
11454: g_rec05_rows(l_terminated_row).old_end := NULL;

Line 11495: g_rec05_rows(i).dt_chg := fnd_date.date_to_canonical(l_out_retro_ptpn_kind(w).start_date);

11491: g_rec05_rows(i).part_time_perc := NULL;
11492: g_rec05_rows(i).fpu_kind := ' ';
11493: g_rec05_rows(i).opnp_kind := ' ';
11494: g_rec05_rows(i).ppp_kind := ' ';
11495: g_rec05_rows(i).dt_chg := fnd_date.date_to_canonical(l_out_retro_ptpn_kind(w).start_date);
11496: IF fnd_date.date_to_canonical(TRUNC(l_out_retro_ptpn_kind(w).end_date)) =
11497: '4712/12/31 00:00:00' THEN
11498: g_rec05_rows(i).eddt_chg := NULL;
11499:

Line 11496: IF fnd_date.date_to_canonical(TRUNC(l_out_retro_ptpn_kind(w).end_date)) =

11492: g_rec05_rows(i).fpu_kind := ' ';
11493: g_rec05_rows(i).opnp_kind := ' ';
11494: g_rec05_rows(i).ppp_kind := ' ';
11495: g_rec05_rows(i).dt_chg := fnd_date.date_to_canonical(l_out_retro_ptpn_kind(w).start_date);
11496: IF fnd_date.date_to_canonical(TRUNC(l_out_retro_ptpn_kind(w).end_date)) =
11497: '4712/12/31 00:00:00' THEN
11498: g_rec05_rows(i).eddt_chg := NULL;
11499:
11500: ELSE

Line 11506: g_rec05_rows(i).eddt_chg := fnd_date.date_to_canonical(l_out_retro_ptpn_kind(w).end_date);

11502: OPEN c_chk_ptpn_continues_kind ( l_out_retro_ptpn_kind(w).end_date
11503: ,l_out_retro_ptpn_kind(w).ptpn_kind);
11504: FETCH c_chk_ptpn_continues_kind INTO l_dummy;
11505: IF c_chk_ptpn_continues_kind%NOTFOUND THEN
11506: g_rec05_rows(i).eddt_chg := fnd_date.date_to_canonical(l_out_retro_ptpn_kind(w).end_date);
11507: ELSE
11508: g_rec05_rows(i).eddt_chg := NULL;
11509: END IF;
11510: CLOSE c_chk_ptpn_continues_kind;

Line 11570: g_rec05_rows(i).dt_chg := fnd_date.date_to_canonical(l_out_retro_ptpn(w).start_date);

11566: g_rec05_rows(i).fpu_kind := ' ';
11567: g_rec05_rows(i).opnp_kind := l_rec05_sub_cat.code;
11568: END IF;
11569:
11570: g_rec05_rows(i).dt_chg := fnd_date.date_to_canonical(l_out_retro_ptpn(w).start_date);
11571: IF fnd_date.date_to_canonical(TRUNC(l_out_retro_ptpn(w).end_date)) =
11572: '4712/12/31 00:00:00' THEN
11573: g_rec05_rows(i).eddt_chg := NULL;
11574:

Line 11571: IF fnd_date.date_to_canonical(TRUNC(l_out_retro_ptpn(w).end_date)) =

11567: g_rec05_rows(i).opnp_kind := l_rec05_sub_cat.code;
11568: END IF;
11569:
11570: g_rec05_rows(i).dt_chg := fnd_date.date_to_canonical(l_out_retro_ptpn(w).start_date);
11571: IF fnd_date.date_to_canonical(TRUNC(l_out_retro_ptpn(w).end_date)) =
11572: '4712/12/31 00:00:00' THEN
11573: g_rec05_rows(i).eddt_chg := NULL;
11574:
11575: ELSE

Line 11581: g_rec05_rows(i).eddt_chg := fnd_date.date_to_canonical(l_out_retro_ptpn(w).end_date);

11577: OPEN c_chk_ptpn_continues ( l_out_retro_ptpn(w).end_date
11578: ,l_out_retro_ptpn(w).ptid);
11579: FETCH c_chk_ptpn_continues INTO l_dummy;
11580: IF c_chk_ptpn_continues%NOTFOUND THEN
11581: g_rec05_rows(i).eddt_chg := fnd_date.date_to_canonical(l_out_retro_ptpn(w).end_date);
11582: ELSE
11583: g_rec05_rows(i).eddt_chg := NULL;
11584: END IF;
11585: CLOSE c_chk_ptpn_continues;

Line 11638: g_rec05_rows(i).dt_chg := fnd_date.date_to_canonical

11634: g_rec05_rows(i).old_start := NULL;
11635: g_rec05_rows(i).old_end := NULL;
11636: g_rec05_rows(i).new_start := NULL;
11637: g_rec05_rows(i).new_end := NULL;
11638: g_rec05_rows(i).dt_chg := fnd_date.date_to_canonical
11639: (hire_ptp_rec.start_date);
11640: hr_utility.set_location('....Start Date is : '||hire_ptp_rec.start_date,24);
11641: hr_utility.set_location('....End Date is : '||hire_ptp_rec.end_date,24);
11642:

Line 11647: g_rec05_rows(i).eddt_chg := fnd_date.date_to_canonical

11643: IF hire_ptp_rec.end_date >=
11644: trunc(g_extract_params(p_business_group_id).extract_end_date) THEN
11645: g_rec05_rows(i).eddt_chg := NULL;
11646: ELSE
11647: g_rec05_rows(i).eddt_chg := fnd_date.date_to_canonical
11648: (hire_ptp_rec.end_date);
11649: END IF;
11650:
11651: hr_utility.set_location('....PTP is : '||hire_ptp_rec.ptp,24);

Line 11668: g_rec05_rows(i-1).eddt_chg := fnd_date.date_to_canonical

11664: IF hire_ptp_rec.end_date >=
11665: trunc(g_extract_params(p_business_group_id).extract_end_date) THEN
11666: g_rec05_rows(i-1).eddt_chg := NULL;
11667: ELSE
11668: g_rec05_rows(i-1).eddt_chg := fnd_date.date_to_canonical
11669: (hire_ptp_rec.end_date);
11670: END IF;
11671:
11672: END IF;

Line 11710: (Fnd_Date.canonical_to_date(p_data_element_value),

11706: --
11707: IF p_fetch_code = 'NEW_ST' THEN
11708: p_data_element_value := g_rec05_rows(g_index_05).new_start;
11709: p_data_element_value := Ben_Ext_Fmt.apply_format_mask
11710: (Fnd_Date.canonical_to_date(p_data_element_value),
11711: 'YYYYMMDD');
11712: ELSIF p_fetch_code = 'OLD_ST' THEN
11713: p_data_element_value := g_rec05_rows(g_index_05).old_start;
11714: p_data_element_value := Ben_Ext_Fmt.apply_format_mask

Line 11715: (Fnd_Date.canonical_to_date(p_data_element_value),

11711: 'YYYYMMDD');
11712: ELSIF p_fetch_code = 'OLD_ST' THEN
11713: p_data_element_value := g_rec05_rows(g_index_05).old_start;
11714: p_data_element_value := Ben_Ext_Fmt.apply_format_mask
11715: (Fnd_Date.canonical_to_date(p_data_element_value),
11716: 'YYYYMMDD');
11717: ELSIF p_fetch_code = 'NEW_ED' THEN
11718: IF g_rec05_rows(g_index_05).new_end IS NOT NULL THEN
11719: g_rec05_rows(g_index_05).new_end :=

Line 11720: fnd_date.date_to_canonical(fnd_date.canonical_to_date(g_rec05_rows(g_index_05).new_end) + 1);

11716: 'YYYYMMDD');
11717: ELSIF p_fetch_code = 'NEW_ED' THEN
11718: IF g_rec05_rows(g_index_05).new_end IS NOT NULL THEN
11719: g_rec05_rows(g_index_05).new_end :=
11720: fnd_date.date_to_canonical(fnd_date.canonical_to_date(g_rec05_rows(g_index_05).new_end) + 1);
11721: END IF;
11722: p_data_element_value := g_rec05_rows(g_index_05).new_end;
11723: p_data_element_value := Ben_Ext_Fmt.apply_format_mask
11724: (Fnd_Date.canonical_to_date(p_data_element_value),

Line 11724: (Fnd_Date.canonical_to_date(p_data_element_value),

11720: fnd_date.date_to_canonical(fnd_date.canonical_to_date(g_rec05_rows(g_index_05).new_end) + 1);
11721: END IF;
11722: p_data_element_value := g_rec05_rows(g_index_05).new_end;
11723: p_data_element_value := Ben_Ext_Fmt.apply_format_mask
11724: (Fnd_Date.canonical_to_date(p_data_element_value),
11725: 'YYYYMMDD');
11726: ELSIF p_fetch_code = 'OLD_ED' THEN
11727: IF g_rec05_rows(g_index_05).old_end IS NOT NULL THEN
11728: g_rec05_rows(g_index_05).old_end :=

Line 11729: fnd_date.date_to_canonical(fnd_date.canonical_to_date(g_rec05_rows(g_index_05).old_end) + 1);

11725: 'YYYYMMDD');
11726: ELSIF p_fetch_code = 'OLD_ED' THEN
11727: IF g_rec05_rows(g_index_05).old_end IS NOT NULL THEN
11728: g_rec05_rows(g_index_05).old_end :=
11729: fnd_date.date_to_canonical(fnd_date.canonical_to_date(g_rec05_rows(g_index_05).old_end) + 1);
11730: END IF;
11731: p_data_element_value := g_rec05_rows(g_index_05).old_end;
11732: p_data_element_value := Ben_Ext_Fmt.apply_format_mask
11733: (Fnd_Date.canonical_to_date(p_data_element_value),

Line 11733: (Fnd_Date.canonical_to_date(p_data_element_value),

11729: fnd_date.date_to_canonical(fnd_date.canonical_to_date(g_rec05_rows(g_index_05).old_end) + 1);
11730: END IF;
11731: p_data_element_value := g_rec05_rows(g_index_05).old_end;
11732: p_data_element_value := Ben_Ext_Fmt.apply_format_mask
11733: (Fnd_Date.canonical_to_date(p_data_element_value),
11734: 'YYYYMMDD');
11735: ELSIF p_fetch_code = 'P_KIND' THEN
11736: p_data_element_value := g_rec05_rows(g_index_05).partn_kind;
11737: ELSIF p_fetch_code = 'P_VALUE' THEN

Line 11755: (Fnd_Date.canonical_to_date(p_data_element_value),

11751: END IF;
11752: ELSIF p_fetch_code = 'DT_CHG' THEN
11753: p_data_element_value := g_rec05_rows(g_index_05).dt_chg;
11754: p_data_element_value := Ben_Ext_Fmt.apply_format_mask
11755: (Fnd_Date.canonical_to_date(p_data_element_value),
11756: 'YYYYMMDD');
11757: ELSIF p_fetch_code = 'EDDT_CHG' THEN
11758:
11759: IF g_rec05_rows(g_index_05).eddt_chg IS NOT NULL THEN

Line 11760: IF fnd_date.canonical_to_date(g_rec05_rows(g_index_05).eddt_chg) >

11756: 'YYYYMMDD');
11757: ELSIF p_fetch_code = 'EDDT_CHG' THEN
11758:
11759: IF g_rec05_rows(g_index_05).eddt_chg IS NOT NULL THEN
11760: IF fnd_date.canonical_to_date(g_rec05_rows(g_index_05).eddt_chg) >
11761: trunc(g_extract_params(p_business_group_id).extract_end_date) THEN
11762: g_rec05_rows(g_index_05).eddt_chg := NULL;
11763: END IF;
11764: END IF;

Line 11768: fnd_date.date_to_canonical(fnd_date.canonical_to_date(g_rec05_rows(g_index_05).eddt_chg) + 1);

11764: END IF;
11765:
11766: IF g_rec05_rows(g_index_05).eddt_chg IS NOT NULL THEN
11767: g_rec05_rows(g_index_05).eddt_chg :=
11768: fnd_date.date_to_canonical(fnd_date.canonical_to_date(g_rec05_rows(g_index_05).eddt_chg) + 1);
11769: END IF;
11770: p_data_element_value := g_rec05_rows(g_index_05).eddt_chg;
11771: p_data_element_value := Ben_Ext_Fmt.apply_format_mask
11772: (Fnd_Date.canonical_to_date(p_data_element_value),

Line 11772: (Fnd_Date.canonical_to_date(p_data_element_value),

11768: fnd_date.date_to_canonical(fnd_date.canonical_to_date(g_rec05_rows(g_index_05).eddt_chg) + 1);
11769: END IF;
11770: p_data_element_value := g_rec05_rows(g_index_05).eddt_chg;
11771: p_data_element_value := Ben_Ext_Fmt.apply_format_mask
11772: (Fnd_Date.canonical_to_date(p_data_element_value),
11773: 'YYYYMMDD');
11774: ELSIF p_fetch_code = 'END_REASON' THEN
11775: p_data_element_value := g_rec05_rows(g_index_05).end_reason;
11776: ELSIF p_fetch_code = 'PPP_KIND' THEN

Line 12121: Hr_Utility.set_location('greater date : '||Fnd_Date.date_to_canonical(Greatest(

12117: END IF;
12118: IF l_hired = 1 THEN
12119: Hr_Utility.set_location('hire date : '||l_hire_date,99);
12120: Hr_Utility.set_location('new date : '||g_ipap_dates(i).new_start,100);
12121: Hr_Utility.set_location('greater date : '||Fnd_Date.date_to_canonical(Greatest(
12122: l_hire_date,Fnd_Date.canonical_to_date(g_ipap_dates(i).new_start))),101);
12123: IF g_ipap_dates(i).new_start IS NOT NULL THEN
12124: Hr_Utility.set_location('chking the new start date',102);
12125: g_ipap_dates(i).new_start := Fnd_Date.date_to_canonical(Greatest(

Line 12122: l_hire_date,Fnd_Date.canonical_to_date(g_ipap_dates(i).new_start))),101);

12118: IF l_hired = 1 THEN
12119: Hr_Utility.set_location('hire date : '||l_hire_date,99);
12120: Hr_Utility.set_location('new date : '||g_ipap_dates(i).new_start,100);
12121: Hr_Utility.set_location('greater date : '||Fnd_Date.date_to_canonical(Greatest(
12122: l_hire_date,Fnd_Date.canonical_to_date(g_ipap_dates(i).new_start))),101);
12123: IF g_ipap_dates(i).new_start IS NOT NULL THEN
12124: Hr_Utility.set_location('chking the new start date',102);
12125: g_ipap_dates(i).new_start := Fnd_Date.date_to_canonical(Greatest(
12126: l_hire_date,Fnd_Date.canonical_to_date(g_ipap_dates(i).new_start)));

Line 12125: g_ipap_dates(i).new_start := Fnd_Date.date_to_canonical(Greatest(

12121: Hr_Utility.set_location('greater date : '||Fnd_Date.date_to_canonical(Greatest(
12122: l_hire_date,Fnd_Date.canonical_to_date(g_ipap_dates(i).new_start))),101);
12123: IF g_ipap_dates(i).new_start IS NOT NULL THEN
12124: Hr_Utility.set_location('chking the new start date',102);
12125: g_ipap_dates(i).new_start := Fnd_Date.date_to_canonical(Greatest(
12126: l_hire_date,Fnd_Date.canonical_to_date(g_ipap_dates(i).new_start)));
12127: Hr_Utility.set_location('new start date is : '||g_ipap_dates(i).new_start,103);
12128: END IF;
12129: IF g_ipap_dates(i).old_start IS NOT NULL THEN

Line 12126: l_hire_date,Fnd_Date.canonical_to_date(g_ipap_dates(i).new_start)));

12122: l_hire_date,Fnd_Date.canonical_to_date(g_ipap_dates(i).new_start))),101);
12123: IF g_ipap_dates(i).new_start IS NOT NULL THEN
12124: Hr_Utility.set_location('chking the new start date',102);
12125: g_ipap_dates(i).new_start := Fnd_Date.date_to_canonical(Greatest(
12126: l_hire_date,Fnd_Date.canonical_to_date(g_ipap_dates(i).new_start)));
12127: Hr_Utility.set_location('new start date is : '||g_ipap_dates(i).new_start,103);
12128: END IF;
12129: IF g_ipap_dates(i).old_start IS NOT NULL THEN
12130: g_ipap_dates(i).old_start := Fnd_Date.date_to_canonical(Greatest(

Line 12130: g_ipap_dates(i).old_start := Fnd_Date.date_to_canonical(Greatest(

12126: l_hire_date,Fnd_Date.canonical_to_date(g_ipap_dates(i).new_start)));
12127: Hr_Utility.set_location('new start date is : '||g_ipap_dates(i).new_start,103);
12128: END IF;
12129: IF g_ipap_dates(i).old_start IS NOT NULL THEN
12130: g_ipap_dates(i).old_start := Fnd_Date.date_to_canonical(Greatest(
12131: l_hire_date,Fnd_Date.canonical_to_date(g_ipap_dates(i).old_start)));
12132: END IF;
12133: END IF;
12134: IF g_ipap_dates(i).new_start = g_ipap_dates(i).old_start THEN

Line 12131: l_hire_date,Fnd_Date.canonical_to_date(g_ipap_dates(i).old_start)));

12127: Hr_Utility.set_location('new start date is : '||g_ipap_dates(i).new_start,103);
12128: END IF;
12129: IF g_ipap_dates(i).old_start IS NOT NULL THEN
12130: g_ipap_dates(i).old_start := Fnd_Date.date_to_canonical(Greatest(
12131: l_hire_date,Fnd_Date.canonical_to_date(g_ipap_dates(i).old_start)));
12132: END IF;
12133: END IF;
12134: IF g_ipap_dates(i).new_start = g_ipap_dates(i).old_start THEN
12135: g_ipap_dates(i).old_start := '';

Line 12165: g_ipap_dates(i).new_start := Fnd_Date.date_to_canonical(l_hire_date);

12161: IF l_hired = 1 THEN
12162: --chk if there is any run result
12163: FOR c_rec IN c_run_results_exist
12164: LOOP
12165: g_ipap_dates(i).new_start := Fnd_Date.date_to_canonical(l_hire_date);
12166: g_ipap_dates(i).old_start := '';
12167: --get the end date corresponding to this enrollment
12168: l_ret_val := Get_Participation_End
12169: (p_assignment_id => p_assignment_id

Line 12179: g_ipap_dates(i).new_end := Fnd_Date.date_to_canonical(l_end_date) ;

12175: IF l_ret_val = 0 THEN
12176: IF l_end_date = hr_api.g_eot THEN
12177: g_ipap_dates(i).new_end := '';
12178: ELSE
12179: g_ipap_dates(i).new_end := Fnd_Date.date_to_canonical(l_end_date) ;
12180: END IF;
12181: ELSE
12182: g_ipap_dates(i).new_end := '';
12183: END IF;

Line 12211: (Fnd_Date.canonical_to_date(p_data_element_value),

12207: END IF;
12208:
12209: -- p_data_element_value := substr(p_data_element_value,1,10);
12210: p_data_element_value := Ben_Ext_Fmt.apply_format_mask
12211: (Fnd_Date.canonical_to_date(p_data_element_value),
12212: 'YYYYMMDD');
12213:
12214: ELSE
12215: p_data_element_value := '';

Line 12474: AND fnd_date.canonical_to_date(prmtr_09) BETWEEN c_start_date AND c_end_date

12470: new_val1 new_date
12471: FROM ben_ext_chg_evt_log
12472: WHERE person_id = c_person_id
12473: AND chg_evt_cd = 'COPOS'
12474: AND fnd_date.canonical_to_date(prmtr_09) BETWEEN c_start_date AND c_end_date
12475: ORDER BY ext_chg_evt_log_id desc;
12476:
12477: CURSOR or_pen_sal ( c_nh_date IN DATE) IS
12478: SELECT 1

Line 12483: AND trunc(c_nh_date) BETWEEN fnd_date.canonical_to_date(aei_information1)

12479: FROM per_assignment_extra_info
12480: WHERE assignment_id = p_assignment_id
12481: AND aei_information_category = 'NL_ABP_PAR_INFO'
12482: AND information_type = 'NL_ABP_PAR_INFO'
12483: AND trunc(c_nh_date) BETWEEN fnd_date.canonical_to_date(aei_information1)
12484: AND fnd_date.canonical_to_date(nvl(aei_information2,
12485: fnd_date.date_to_canonical(hr_api.g_eot)))
12486: AND aei_information6 IS NOT NULL;
12487:

Line 12484: AND fnd_date.canonical_to_date(nvl(aei_information2,

12480: WHERE assignment_id = p_assignment_id
12481: AND aei_information_category = 'NL_ABP_PAR_INFO'
12482: AND information_type = 'NL_ABP_PAR_INFO'
12483: AND trunc(c_nh_date) BETWEEN fnd_date.canonical_to_date(aei_information1)
12484: AND fnd_date.canonical_to_date(nvl(aei_information2,
12485: fnd_date.date_to_canonical(hr_api.g_eot)))
12486: AND aei_information6 IS NOT NULL;
12487:
12488: CURSOR c_get_override_salary (c_start IN DATE, c_end IN DATE) IS

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

12481: AND aei_information_category = 'NL_ABP_PAR_INFO'
12482: AND information_type = 'NL_ABP_PAR_INFO'
12483: AND trunc(c_nh_date) BETWEEN fnd_date.canonical_to_date(aei_information1)
12484: AND fnd_date.canonical_to_date(nvl(aei_information2,
12485: fnd_date.date_to_canonical(hr_api.g_eot)))
12486: AND aei_information6 IS NOT NULL;
12487:
12488: CURSOR c_get_override_salary (c_start IN DATE, c_end IN DATE) IS
12489: SELECT fnd_number.canonical_to_number(nvl(new_val1,'0'))

Line 12490: ,fnd_date.canonical_to_date(prmtr_02)

12486: AND aei_information6 IS NOT NULL;
12487:
12488: CURSOR c_get_override_salary (c_start IN DATE, c_end IN DATE) IS
12489: SELECT fnd_number.canonical_to_number(nvl(new_val1,'0'))
12490: ,fnd_date.canonical_to_date(prmtr_02)
12491: FROM ben_ext_chg_evt_log
12492: WHERE person_id = g_person_id
12493: AND fnd_number.canonical_to_number(prmtr_01) = p_assignment_id
12494: AND chg_eff_dt BETWEEN g_extract_params(p_business_group_id).extract_start_date

Line 12498: AND fnd_date.canonical_to_date(prmtr_02) BETWEEN c_start and c_end;

12494: AND chg_eff_dt BETWEEN g_extract_params(p_business_group_id).extract_start_date
12495: AND g_extract_params(p_business_group_id).extract_end_date
12496: AND chg_evt_cd = 'COAPS'
12497: AND fnd_number.canonical_to_number(nvl(new_val1,'0')) <> 0
12498: AND fnd_date.canonical_to_date(prmtr_02) BETWEEN c_start and c_end;
12499:
12500: l_ret_val NUMBER := 0;
12501: l_proc_name VARCHAR2(150) := g_proc_name ||'Process_Mult_Rec08';
12502: l_rcd_id NUMBER;

Line 13958: AND p_effective_date BETWEEN Fnd_Date.canonical_to_date(aei_information1)

13954: CURSOR c_get_contrib_kind IS
13955: SELECT Substr(Nvl(aei_information7,'D'),0,1)
13956: FROM per_assignment_extra_info
13957: WHERE assignment_id = p_assignment_id
13958: AND p_effective_date BETWEEN Fnd_Date.canonical_to_date(aei_information1)
13959: AND Fnd_Date.canonical_to_date(Nvl(aei_information2,Fnd_Date.date_to_canonical(Hr_Api.g_eot)))
13960: AND aei_information_category = 'NL_ABP_RI'
13961: AND information_type = 'NL_ABP_RI';
13962:

Line 13959: AND Fnd_Date.canonical_to_date(Nvl(aei_information2,Fnd_Date.date_to_canonical(Hr_Api.g_eot)))

13955: SELECT Substr(Nvl(aei_information7,'D'),0,1)
13956: FROM per_assignment_extra_info
13957: WHERE assignment_id = p_assignment_id
13958: AND p_effective_date BETWEEN Fnd_Date.canonical_to_date(aei_information1)
13959: AND Fnd_Date.canonical_to_date(Nvl(aei_information2,Fnd_Date.date_to_canonical(Hr_Api.g_eot)))
13960: AND aei_information_category = 'NL_ABP_RI'
13961: AND information_type = 'NL_ABP_RI';
13962:
13963: BEGIN

Line 14041: AND p_effective_date BETWEEN Fnd_Date.canonical_to_date(aei_information1)

14037: FROM per_assignment_extra_info
14038: WHERE assignment_id = p_assignment_id
14039: AND aei_information_category = 'NL_ABP_RI'
14040: AND information_type = 'NL_ABP_RI'
14041: AND p_effective_date BETWEEN Fnd_Date.canonical_to_date(aei_information1)
14042: AND Fnd_Date.canonical_to_date(Nvl(aei_information2,Fnd_Date.date_to_canonical(Hr_Api.g_eot)));
14043:
14044: l_proc_name Varchar2(30) := 'Get_Ins_Typ_Anw_Ipap';
14045: l_anw_type Varchar2(2) := '01';

Line 14042: AND Fnd_Date.canonical_to_date(Nvl(aei_information2,Fnd_Date.date_to_canonical(Hr_Api.g_eot)));

14038: WHERE assignment_id = p_assignment_id
14039: AND aei_information_category = 'NL_ABP_RI'
14040: AND information_type = 'NL_ABP_RI'
14041: AND p_effective_date BETWEEN Fnd_Date.canonical_to_date(aei_information1)
14042: AND Fnd_Date.canonical_to_date(Nvl(aei_information2,Fnd_Date.date_to_canonical(Hr_Api.g_eot)));
14043:
14044: l_proc_name Varchar2(30) := 'Get_Ins_Typ_Anw_Ipap';
14045: l_anw_type Varchar2(2) := '01';
14046: l_ipap_type Varchar2(2) := '01';

Line 14092: AND p_effective_date BETWEEN Fnd_Date.canonical_to_date(aei_information1)

14088: FROM per_assignment_extra_info
14089: WHERE information_type = 'NL_USZO_INFO'
14090: AND aei_information_category = 'NL_USZO_INFO'
14091: AND assignment_id = p_assignment_id
14092: AND p_effective_date BETWEEN Fnd_Date.canonical_to_date(aei_information1)
14093: AND Fnd_Date.canonical_to_date(Nvl(aei_information2,Fnd_Date.date_to_canonical(Hr_Api.g_eot)))
14094: AND ROWNUM = 1;
14095:
14096: l_incidental_worker Varchar2(1) := '0';

Line 14093: AND Fnd_Date.canonical_to_date(Nvl(aei_information2,Fnd_Date.date_to_canonical(Hr_Api.g_eot)))

14089: WHERE information_type = 'NL_USZO_INFO'
14090: AND aei_information_category = 'NL_USZO_INFO'
14091: AND assignment_id = p_assignment_id
14092: AND p_effective_date BETWEEN Fnd_Date.canonical_to_date(aei_information1)
14093: AND Fnd_Date.canonical_to_date(Nvl(aei_information2,Fnd_Date.date_to_canonical(Hr_Api.g_eot)))
14094: AND ROWNUM = 1;
14095:
14096: l_incidental_worker Varchar2(1) := '0';
14097:

Line 14202: SELECT fnd_date.canonical_to_date(aei_information3)

14198: ,p_data_element_value OUT NOCOPY VARCHAR2
14199: ) RETURN NUMBER IS
14200:
14201: CURSOR csr_get_dt (c_assignment_id IN NUMBER) IS
14202: SELECT fnd_date.canonical_to_date(aei_information3)
14203: FROM per_assignment_extra_info
14204: WHERE assignment_id = p_assignment_id
14205: AND information_type = 'PQP_NL_ABP_OLD_EE_INFO';
14206:

Line 16169: l_return_value := To_Char(Fnd_Date.canonical_to_date(Fnd_Date.date_to_canonical(g_conc_prog_details(0).beginningdt)),'YYYYMM');

16165: BEGIN
16166:
16167: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
16168: IF p_header_type = 'HEADER_FILE_SUB_PERIOD' THEN
16169: l_return_value := To_Char(Fnd_Date.canonical_to_date(Fnd_Date.date_to_canonical(g_conc_prog_details(0).beginningdt)),'YYYYMM');
16170: ELSIF p_header_type = 'HEADER_SUB_IDEN' THEN
16171: l_return_value := g_conc_prog_details(0).orgname;
16172: ELSIF p_header_type = 'HEADER_EMR_REG_NUM' THEN
16173: l_new_er_num :=Get_Header_Submit_Code(g_conc_prog_details(0).orgid);