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 1171: SELECT substr(fnd_date.date_to_canonical(

1167: -- ============================================================================
1168: -- Cursor to get the original date earned for a retro element entry
1169: -- ============================================================================
1170: CURSOR c_get_retro_date_earned(c_element_entry_id in number) IS
1171: SELECT substr(fnd_date.date_to_canonical(
1172: trunc(pay_paywsmee_pkg.get_original_date_earned(c_element_entry_id))
1173: ),1,10)
1174: FROM dual;
1175:

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

2017: IF g_debug THEN
2018: Hr_Utility.set_location('..Applying Date format mask
2019: :ben_ext_fmt.apply_format_mask',55);
2020: END IF;
2021: l_ff_value_fmt := Ben_Ext_Fmt.apply_format_mask(Fnd_Date.canonical_to_date(l_ff_value),
2022: i.frmt_mask_cd);
2023: l_ff_value := l_ff_value_fmt;
2024: END IF;
2025: END IF;

Line 2383: AND fnd_date.canonical_to_date(prmtr_09)

2379: new_val1 new_date
2380: FROM ben_ext_chg_evt_log
2381: WHERE person_id = p_person_id
2382: AND chg_evt_cd = 'COPOS'
2383: AND fnd_date.canonical_to_date(prmtr_09)
2384: BETWEEN g_extract_params(p_business_group_id).extract_start_date AND
2385: g_extract_params(p_business_group_id).extract_end_date
2386: ORDER BY ext_chg_evt_log_id DESC;
2387:

Line 2616: Fnd_Date.canonical_to_date(aei_information1)AND

2612: FROM per_assignment_extra_info
2613: WHERE assignment_id = p_assignment_id
2614: AND information_type = 'NL_USZO_INFO'
2615: AND Trunc(p_effective_date) BETWEEN
2616: Fnd_Date.canonical_to_date(aei_information1)AND
2617: Nvl(Fnd_Date.canonical_to_date(aei_information2),
2618: To_Date('31/12/4712','DD/MM/YYYY'))
2619: AND ROWNUM = 1;
2620: -- Rownum clause has been added on purpose as it is possible that there

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

2613: WHERE assignment_id = p_assignment_id
2614: AND information_type = 'NL_USZO_INFO'
2615: AND Trunc(p_effective_date) BETWEEN
2616: Fnd_Date.canonical_to_date(aei_information1)AND
2617: Nvl(Fnd_Date.canonical_to_date(aei_information2),
2618: To_Date('31/12/4712','DD/MM/YYYY'))
2619: AND ROWNUM = 1;
2620: -- Rownum clause has been added on purpose as it is possible that there
2621: -- are two valid rows in the system ( There are no checks in the ASG EIT)

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

3199: new_val1 new_date
3200: FROM ben_ext_chg_evt_log
3201: WHERE person_id = c_person_id
3202: AND chg_evt_cd = 'COPOS'
3203: AND fnd_date.canonical_to_date(prmtr_09) BETWEEN c_start_date AND c_end_date
3204: ORDER BY ext_chg_evt_log_id desc;
3205:
3206: CURSOR c_ptp_log_rows (c_start_date IN DATE
3207: ,c_end_date IN DATE

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

3220: AND sck.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
3221: AND past.per_system_status = 'ACTIVE_ASSIGN'
3222: AND log.person_id = g_person_id
3223: AND log.chg_evt_cd = 'COPTP'
3224: AND fnd_date.canonical_to_date(log.prmtr_09)
3225: BETWEEN c_start_date AND c_end_date
3226: AND asg.effective_start_date between c_asg_st_dt AND c_asg_ed_dt
3227: AND asg.soft_coding_keyflex_id = log.prmtr_02
3228: AND asg.assignment_id = log.prmtr_01

Line 3572: SELECT fnd_date.canonical_to_date(peev.screen_entry_value) start_date, fnd_date.canonical_to_date(peev1.screen_entry_value) end_date

3568: AND p_effective_date between piv.effective_start_date AND piv.effective_end_date
3569: AND p_effective_date between piv1.effective_start_date AND piv1.effective_end_date;
3570:
3571: CURSOR c_ude_rec05(p_effective_date date, p_ele_type_id number, p_start_dt_id number, p_end_dt_id number, p_asg_id number) IS
3572: SELECT fnd_date.canonical_to_date(peev.screen_entry_value) start_date, fnd_date.canonical_to_date(peev1.screen_entry_value) end_date
3573: FROM pay_element_entries_f peef,
3574: pay_element_links_f pelf,
3575: pay_element_entry_values_f peev,
3576: pay_element_entry_values_f peev1

Line 4292: fnd_date.canonical_to_date(substr(substr(

4288: FETCH c_get_retro_num_value INTO l_retro_ptp_value;
4289: CLOSE c_get_retro_num_value;
4290:
4291: l_retro_age_cal_dt :=
4292: fnd_date.canonical_to_date(substr(substr(
4293: fnd_date.date_to_canonical(
4294: pay_paywsmee_pkg.get_original_date_earned(
4295: temp_rec.element_entry_id)),1,10),1,8)||'01');
4296:

Line 4293: fnd_date.date_to_canonical(

4289: CLOSE c_get_retro_num_value;
4290:
4291: l_retro_age_cal_dt :=
4292: fnd_date.canonical_to_date(substr(substr(
4293: fnd_date.date_to_canonical(
4294: pay_paywsmee_pkg.get_original_date_earned(
4295: temp_rec.element_entry_id)),1,10),1,8)||'01');
4296:
4297:

Line 4344: fnd_date.canonical_to_date(substr(substr(

4340: CLOSE c_get_retro_num_value;
4341:
4342:
4343: l_retro_age_cal_dt :=
4344: fnd_date.canonical_to_date(substr(substr(
4345: fnd_date.date_to_canonical(
4346: pay_paywsmee_pkg.get_original_date_earned(
4347: temp_rec.element_entry_id)),1,10),1,8)||'01');
4348:

Line 4345: fnd_date.date_to_canonical(

4341:
4342:
4343: l_retro_age_cal_dt :=
4344: fnd_date.canonical_to_date(substr(substr(
4345: fnd_date.date_to_canonical(
4346: pay_paywsmee_pkg.get_original_date_earned(
4347: temp_rec.element_entry_id)),1,10),1,8)||'01');
4348:
4349: l_ee_age_at_retro := Get_Age(

Line 4406: fnd_date.canonical_to_date(substr(substr(

4402: CLOSE c_get_retro_num_value;
4403:
4404:
4405: l_retro_age_cal_dt :=
4406: fnd_date.canonical_to_date(substr(substr(
4407: fnd_date.date_to_canonical(
4408: pay_paywsmee_pkg.get_original_date_earned(
4409: temp_rec.element_entry_id)),1,10),1,8)||'01');
4410:

Line 4407: fnd_date.date_to_canonical(

4403:
4404:
4405: l_retro_age_cal_dt :=
4406: fnd_date.canonical_to_date(substr(substr(
4407: fnd_date.date_to_canonical(
4408: pay_paywsmee_pkg.get_original_date_earned(
4409: temp_rec.element_entry_id)),1,10),1,8)||'01');
4410:
4411: l_ee_age_at_retro := Get_Age(

Line 4442: l_reg_09_age_cal_dt := fnd_date.canonical_to_date(

4438: END IF; -- AGe
4439:
4440: END LOOP;
4441:
4442: l_reg_09_age_cal_dt := fnd_date.canonical_to_date(
4443: substr(substr(fnd_date.date_to_canonical(
4444: act_rec.date_earned),1,10),1,8)||'01');
4445:
4446: l_reg_09_age := Get_Age(p_assignment_id

Line 4443: substr(substr(fnd_date.date_to_canonical(

4439:
4440: END LOOP;
4441:
4442: l_reg_09_age_cal_dt := fnd_date.canonical_to_date(
4443: substr(substr(fnd_date.date_to_canonical(
4444: act_rec.date_earned),1,10),1,8)||'01');
4445:
4446: l_reg_09_age := Get_Age(p_assignment_id
4447: ,trunc(l_reg_09_age_cal_dt)) ;

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

4503: IF trunc(l_new_start) < trunc(l_old_start) THEN
4504: --
4505: -- Derive the beginning date
4506: --
4507: l_beg_new_st := fnd_date.canonical_to_date(to_char(l_new_start,'YYYY/MM')||'/01');
4508: --
4509: -- If the beginnind date is less then the current extract start
4510: -- Loop through the assignment actions to derive contrib amounts
4511: -- of that period

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

4574:
4575: --
4576: -- Derive the beginning date
4577: --
4578: l_beg_new_st := fnd_date.canonical_to_date(to_char(l_old_start,'YYYY/MM')||'/01');
4579: l_loop_end_date := add_months(fnd_date.canonical_to_date(to_char(l_new_start,'YYYY/MM')||'/01'),1) - 1;
4580: l_loop_end_date := LEAST ( g_extract_params(p_business_group_id).extract_start_date -1
4581: ,l_loop_end_date);
4582: -- GZZ

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

4575: --
4576: -- Derive the beginning date
4577: --
4578: l_beg_new_st := fnd_date.canonical_to_date(to_char(l_old_start,'YYYY/MM')||'/01');
4579: l_loop_end_date := add_months(fnd_date.canonical_to_date(to_char(l_new_start,'YYYY/MM')||'/01'),1) - 1;
4580: l_loop_end_date := LEAST ( g_extract_params(p_business_group_id).extract_start_date -1
4581: ,l_loop_end_date);
4582: -- GZZ
4583: --

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

4625: l_rec_09_disp := 'Y';
4626: l_rec_09_values(k).processed := 'N';
4627: l_rec_09_values(k).code := l_rec_09(i).code;
4628: l_rec_09_values(k).date_earned :=
4629: substr(fnd_date.date_to_canonical(l_end_new_st),1,10);
4630:
4631: OPEN c_09_abp_data (l_end_new_st,l_rec_09(i).code);
4632: FETCH c_09_abp_data INTO l_09_basis_amt_sent_prev;
4633: IF c_09_abp_data%FOUND THEN

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

4660: l_rec_09_disp := 'Y';
4661: l_rec_09_values(k).processed := 'N';
4662: l_rec_09_values(k).code := l_rec_09(i).code;
4663: l_rec_09_values(k).date_earned :=
4664: substr(fnd_date.date_to_canonical(l_end_new_st),1,10);
4665: IF l_rec_09_values(k).basis_amount < 0 THEN
4666: l_rec_09_values(k).sign_code := 'C';
4667: END IF;
4668: k := k+ 1;

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

4718: l_rec_09_disp := 'Y';
4719: l_rec_09_values(k).processed := 'N';
4720: l_rec_09_values(k).code := l_09_poj_rec.code;
4721: l_rec_09_values(k).date_earned :=
4722: substr(fnd_date.date_to_canonical(l_09_poj_rec.eff_dt),1,10);
4723:
4724: IF l_rec_09_values(k).basis_amount < 0 THEN
4725: l_rec_09_values(k).sign_code := 'C';
4726: END IF;

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

4733: l_rec_09_disp := 'Y';
4734: l_rec_09_values(k).processed := 'N';
4735: l_rec_09_values(k).code := l_09_poj_rec.code;
4736: l_rec_09_values(k).date_earned :=
4737: substr(fnd_date.date_to_canonical(l_09_poj_rec.eff_dt),1,10);
4738:
4739: IF l_rec_09_values(k).basis_amount < 0 THEN
4740: l_rec_09_values(k).sign_code := 'C';
4741: END IF;

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

4792: l_rec_31_values(k).basis_amount := l_retro_ptp_value;
4793: l_rec_31_disp := 'Y';
4794: l_rec_31_values(k).processed := 'N';
4795: l_rec_31_values(k).code := l_rec_31(i).code;
4796: l_rec_31_values(k).date_earned := substr(fnd_date.date_to_canonical(
4797: pay_paywsmee_pkg.get_original_date_earned(
4798: temp_rec.element_entry_id)
4799: ),1,10);
4800:

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

4830: l_rec_31_disp := 'Y';
4831: l_rec_31_values(k).processed := 'N';
4832: l_rec_31_values(k).code := l_rec_31(i).code;
4833: l_rec_31_values(k).date_earned := ' ';
4834: l_rec_31_values(k).date_earned := substr(fnd_date.date_to_canonical(
4835: pay_paywsmee_pkg.get_original_date_earned(
4836: temp_rec.element_entry_id)
4837: ),1,10);
4838:

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

4907: l_rec_41_basis_values(k).basis_amount := l_retro_ptp_value;
4908: l_basis_rec_41_disp := 'Y';
4909: l_rec_41_basis_values(k).processed := 'N';
4910: l_rec_41_basis_values(k).code := l_basis_rec_41(i).code;
4911: l_rec_41_basis_values(k).date_earned := substr(fnd_date.date_to_canonical(
4912: pay_paywsmee_pkg.get_original_date_earned(
4913: temp_rec.element_entry_id)
4914: ),1,10);
4915:

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

4945: l_basis_rec_41_disp := 'Y';
4946: l_rec_41_basis_values(k).processed := 'N';
4947: l_rec_41_basis_values(k).code := l_basis_rec_41(i).code;
4948: l_rec_41_basis_values(k).date_earned := ' ';
4949: l_rec_41_basis_values(k).date_earned := substr(fnd_date.date_to_canonical(
4950: pay_paywsmee_pkg.get_original_date_earned(
4951: temp_rec.element_entry_id)
4952: ),1,10);
4953:

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

5121: END IF;
5122: ELSE
5123: IF temp_rec.amount <> 0 THEN
5124: l_rec_41_contrib_values(i_41).contrib_amount := temp_rec.amount;
5125: l_rec_41_contrib_values(i_41).date_earned := substr(fnd_date.date_to_canonical(
5126: temp_rec.date_earned),1,10);
5127: l_rec_41_contrib_values(i_41).code := temp_rec.code;
5128: i_41 := i_41 + 1;
5129: l_contrib_rec_41_disp := 'Y';

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

5151: IF trunc(l_new_start) < trunc(l_old_start) THEN
5152: --
5153: -- Derive the beginning date
5154: --
5155: l_beg_new_st := fnd_date.canonical_to_date(to_char(l_new_start,'YYYY/MM')||'/01');
5156: --
5157: -- If the beginning date is less then the current extract start
5158: -- Loop through the assignment actions to derive contrib amounts
5159: -- of that period

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

5241: ELSIF trunc(l_new_start) > trunc(l_old_start) THEN
5242: --
5243: -- Derive the beginning date
5244: --
5245: l_beg_new_st := fnd_date.canonical_to_date(to_char(l_old_start,'YYYY/MM')||'/01');
5246: l_loop_end_date := add_months(fnd_date.canonical_to_date(to_char(l_new_start,'YYYY/MM')||'/01'),1) - 1;
5247: l_loop_end_date := LEAST ( g_extract_params(p_business_group_id).extract_start_date -1
5248: ,l_loop_end_date);
5249: -- GZZ

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

5242: --
5243: -- Derive the beginning date
5244: --
5245: l_beg_new_st := fnd_date.canonical_to_date(to_char(l_old_start,'YYYY/MM')||'/01');
5246: l_loop_end_date := add_months(fnd_date.canonical_to_date(to_char(l_new_start,'YYYY/MM')||'/01'),1) - 1;
5247: l_loop_end_date := LEAST ( g_extract_params(p_business_group_id).extract_start_date -1
5248: ,l_loop_end_date);
5249: -- GZZ
5250: --

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

5312: hr_utility.set_location('chking amt : '||l_rec12_amt,10);
5313:
5314: IF l_rec12_amt <> 0 THEN
5315: l_rec_12_values(i_12).contrib_amount := l_rec12_amt;
5316: l_rec_12_values(i_12).date_earned := substr(fnd_date.date_to_canonical(l_end_new_st),1,10);
5317: l_rec_12_values(i_12).code := temp_rec.code;
5318: IF length(temp_rec.code) = 1 THEN
5319: l_gxx_code := '0'||temp_rec.code;
5320: ELSE

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

5359: FETCH c_12_abp_data INTO l_rec_12_amt_sent_prev;
5360: IF c_12_abp_data%FOUND THEN
5361: hr_utility.set_location('GXXXXX the amount is asg : '||l_rec_12_amt_sent_prev,10);
5362: l_rec_12_values(i_12).contrib_amount := -1 * l_rec_12_amt_sent_prev;
5363: l_rec_12_values(i_12).date_earned := substr(fnd_date.date_to_canonical(l_end_new_st),1,10);
5364: l_rec_12_values(i_12).code := temp_rec.code;
5365: i_12 := i_12 + 1;
5366: l_rec_12_disp := 'Y';
5367: ELSE

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

5370: l_end_new_st,l_gxx_code);
5371: FETCH c_12_retro_abp_data INTO l_rec_12_amt_sent_prev_r;
5372: IF c_12_retro_abp_data%FOUND THEN
5373: l_rec_12_values(i_12).contrib_amount := -1 * l_rec_12_amt_sent_prev_r;
5374: l_rec_12_values(i_12).date_earned := substr(fnd_date.date_to_canonical(l_end_new_st),1,10);
5375: l_rec_12_values(i_12).code := temp_rec.code;
5376: i_12 := i_12 + 1;
5377: l_rec_12_disp := 'Y';
5378: END IF;

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

5510: l_rec_09_disp := 'Y';
5511: l_rec_09_values(k).processed := 'N';
5512: l_rec_09_values(k).code := l_rec_09(i).code;
5513: l_rec_09_values(k).date_earned :=
5514: substr(fnd_date.date_to_canonical(l_end_new_st),1,10);
5515:
5516: OPEN c_09_abp_data (l_end_new_st,l_rec_09(i).code);
5517: FETCH c_09_abp_data INTO l_09_basis_amt_sent_prev;
5518: IF c_09_abp_data%FOUND THEN

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

5611: hr_utility.set_location('chking amt : '||l_rec12_amt,10);
5612:
5613: IF l_rec12_amt <> 0 THEN
5614: l_rec_12_values(i_12).contrib_amount := l_rec12_amt;
5615: l_rec_12_values(i_12).date_earned := substr(fnd_date.date_to_canonical(l_end_new_st),1,10);
5616: l_rec_12_values(i_12).code := temp_rec.code;
5617: i_12 := i_12 + 1;
5618: l_rec_12_disp := 'Y';
5619:

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

5928: g_extract_params(i).payroll_id := l_ext_params.value2;
5929: g_extract_params(i).con_set_id := l_ext_params.value3;
5930: g_extract_params(i).org_id := l_ext_params.value4;
5931: g_extract_params(i).extract_start_date :=
5932: Fnd_Date.canonical_to_date(l_ext_params.attribute3);
5933: g_extract_params(i).extract_end_date :=
5934: Fnd_Date.canonical_to_date(l_ext_params.attribute4);
5935: g_extract_params(i).extract_rec_01 := l_ext_params.attribute5;
5936: g_sort_position := nvl(l_ext_params.value5,1); --9278285

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

5930: g_extract_params(i).org_id := l_ext_params.value4;
5931: g_extract_params(i).extract_start_date :=
5932: Fnd_Date.canonical_to_date(l_ext_params.attribute3);
5933: g_extract_params(i).extract_end_date :=
5934: Fnd_Date.canonical_to_date(l_ext_params.attribute4);
5935: g_extract_params(i).extract_rec_01 := l_ext_params.attribute5;
5936: g_sort_position := nvl(l_ext_params.value5,1); --9278285
5937:
5938: OPEN csr_leg_code (c_business_group_id => p_business_group_id);

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

7621: -- Cursor to get the override ABP Pension Salary
7622: --
7623: CURSOR c_get_override_salary IS
7624: SELECT NVL(aei_information6,'0') ,
7625: TRUNC(fnd_date.canonical_to_date(aei_information1))
7626: FROM per_assignment_extra_info
7627: WHERE assignment_id = p_assignment_id
7628: AND information_type = 'NL_ABP_PAR_INFO'
7629: AND aei_information_category = 'NL_ABP_PAR_INFO'

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

7627: WHERE assignment_id = p_assignment_id
7628: AND information_type = 'NL_ABP_PAR_INFO'
7629: AND aei_information_category = 'NL_ABP_PAR_INFO'
7630: AND p_effective_date BETWEEN
7631: TRUNC(fnd_date.canonical_to_date(aei_information1))
7632: AND TRUNC(NVL(fnd_date.canonical_to_date(aei_information2),hr_api.g_eot))
7633: AND aei_information6 IS NOT NULL;
7634:
7635: l_ret_val NUMBER := 0;

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

7628: AND information_type = 'NL_ABP_PAR_INFO'
7629: AND aei_information_category = 'NL_ABP_PAR_INFO'
7630: AND p_effective_date BETWEEN
7631: TRUNC(fnd_date.canonical_to_date(aei_information1))
7632: AND TRUNC(NVL(fnd_date.canonical_to_date(aei_information2),hr_api.g_eot))
7633: AND aei_information6 IS NOT NULL;
7634:
7635: l_ret_val NUMBER := 0;
7636: l_asg_action_id pay_assignment_actions.assignment_action_id%TYPE;

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

7836: AND past.per_system_status = 'ACTIVE_ASSIGN'
7837: AND asg.assignment_id = c_assign_id;
7838:
7839: CURSOR cur_get_yr(c_effective_dt IN Date) IS
7840: SELECT fnd_date.canonical_to_date(TO_CHAR(c_effective_dt,'YYYY')||'/01/01')
7841: FROM dual ;
7842: --
7843: -- Cursor to check if there are any changes in the pension salary
7844: --

Line 7846: SELECT fnd_date.canonical_to_date(prmtr_02)

7842: --
7843: -- Cursor to check if there are any changes in the pension salary
7844: --
7845: CURSOR c_get_override_start_date IS
7846: SELECT fnd_date.canonical_to_date(prmtr_02)
7847: FROM ben_ext_chg_evt_log
7848: WHERE person_id = g_person_id
7849: AND Fnd_Number.canonical_to_number(prmtr_01) = p_assignment_id
7850: AND chg_eff_dt BETWEEN p_start_date AND p_end_date

Line 7973: (Fnd_Date.canonical_to_date(p_data_element_value),

7969: END IF;
7970:
7971: IF p_data_element_value <> ' ' THEN
7972: p_data_element_value := Ben_Ext_Fmt.apply_format_mask
7973: (Fnd_Date.canonical_to_date(p_data_element_value),
7974: 'YYYY');
7975: ELSE
7976: p_data_element_value := '0000';
7977: END IF;

Line 8032: (Fnd_Date.canonical_to_date(p_data_element_value),

8028: END IF;
8029:
8030: IF p_data_element_value <> ' ' THEN
8031: p_data_element_value := Ben_Ext_Fmt.apply_format_mask
8032: (Fnd_Date.canonical_to_date(p_data_element_value),
8033: 'MM');
8034: ELSE
8035: p_data_element_value := '00';
8036: END IF;

Line 8072: (Fnd_Date.canonical_to_date(p_data_element_value),

8068: IF l_rec_12_values.EXISTS(j) THEN
8069: p_data_element_value := l_rec_12_values(j).date_earned;
8070: IF p_data_element_value <> ' ' THEN
8071: p_data_element_value := Ben_Ext_Fmt.apply_format_mask
8072: (Fnd_Date.canonical_to_date(p_data_element_value),
8073: 'YYYY');
8074: ELSE
8075: p_data_element_value := '0000';
8076: END IF;

Line 8086: (Fnd_Date.canonical_to_date(p_data_element_value),

8082: IF l_rec_41_contrib_values.EXISTS(j) THEN
8083: p_data_element_value := l_rec_41_contrib_values(j).date_earned;
8084: IF p_data_element_value <> ' ' THEN
8085: p_data_element_value := Ben_Ext_Fmt.apply_format_mask
8086: (Fnd_Date.canonical_to_date(p_data_element_value),
8087: 'YYYY');
8088: ELSE
8089: p_data_element_value := '0000';
8090: END IF;

Line 8128: (Fnd_Date.canonical_to_date(p_data_element_value),

8124: IF l_rec_12_values.EXISTS(j) THEN
8125: p_data_element_value := l_rec_12_values(j).date_earned;
8126: IF p_data_element_value <> ' ' THEN
8127: p_data_element_value := Ben_Ext_Fmt.apply_format_mask
8128: (Fnd_Date.canonical_to_date(p_data_element_value),
8129: 'MM');
8130: ELSE
8131: p_data_element_value := '00';
8132: END IF;

Line 8143: (Fnd_Date.canonical_to_date(p_data_element_value),

8139: IF l_rec_41_contrib_values.EXISTS(j) THEN
8140: p_data_element_value := l_rec_41_contrib_values(j).date_earned;
8141: IF p_data_element_value <> ' ' THEN
8142: p_data_element_value := Ben_Ext_Fmt.apply_format_mask
8143: (Fnd_Date.canonical_to_date(p_data_element_value),
8144: 'MM');
8145: ELSE
8146: p_data_element_value := '00';
8147: END IF;

Line 8268: AND fnd_date.canonical_to_date(prmtr_09)

8264: FROM ben_ext_chg_evt_log
8265: WHERE person_id = c_person_id
8266: AND business_group_id = c_business_group_id
8267: AND chg_evt_cd IN ('COLN','COSS','COUN','COG','CODB')
8268: AND fnd_date.canonical_to_date(prmtr_09)
8269: BETWEEN c_ext_start_date AND c_ext_end_date;
8270: --
8271: -- Cursor to fetch the partner's person id
8272: --

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

8287: FROM ben_ext_chg_evt_log
8288: WHERE person_id = c_person_id
8289: AND business_group_id = p_business_group_id
8290: AND chg_evt_cd IN ('COUN','COLN','CCFN')
8291: AND fnd_date.canonical_to_date(prmtr_09) BETWEEN g_extract_params(p_business_group_id).extract_start_date
8292: AND g_extract_params(p_business_group_id).extract_end_date;
8293:
8294: --
8295: -- Cursor to check if Rec 02 was sent to ABP previously

Line 8469: AND fnd_date.canonical_to_date(prmtr_09)

8465: FROM ben_ext_chg_evt_log
8466: WHERE person_id = c_person_id
8467: AND business_group_id = c_business_group_id
8468: AND chg_evt_cd = 'COM'
8469: AND fnd_date.canonical_to_date(prmtr_09)
8470: BETWEEN c_ext_start_date AND c_ext_end_date;
8471: --
8472: -- Cursor to check if the country of residence is a foreign ( non NL ) country
8473: --

Line 8532: AND fnd_date.canonical_to_date(prmtr_09)

8528: FROM ben_ext_chg_evt_log log
8529: WHERE padr.person_id = log.person_id
8530: AND chg_evt_cd = 'COPR'
8531: AND padr.address_id = log.prmtr_01
8532: AND fnd_date.canonical_to_date(prmtr_09)
8533: BETWEEN c_ext_start_date AND c_ext_end_date);
8534:
8535: --
8536: -- Cursor to check if the address changed from Non NL Country to NL

Line 8559: AND fnd_date.canonical_to_date(prmtr_09)

8555: AND chg_evt_cd = 'COPR'
8556: AND padr.address_id = log.prmtr_01
8557: -- there is a change in primary address
8558: AND log.prmtr_02 IS NOT NULL
8559: AND fnd_date.canonical_to_date(prmtr_09)
8560: BETWEEN c_ext_start_date AND c_ext_end_date
8561: AND EXISTS (SELECT 1
8562: FROM per_addresses_v adr
8563: WHERE adr.person_id = log.person_id

Line 8580: AND fnd_date.canonical_to_date(prmtr_09)

8576: SELECT TO_NUMBER(prmtr_01) addr_id,new_val1 country
8577: FROM ben_ext_chg_evt_log log
8578: WHERE person_id = c_person_id
8579: AND chg_evt_cd = 'COCN'
8580: AND fnd_date.canonical_to_date(prmtr_09)
8581: BETWEEN c_ext_start_date AND c_ext_end_date
8582: ORDER BY ext_chg_evt_log_id DESC;
8583:
8584: --

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

9700: AND hoi.organization_id = c_org_id;
9701:
9702: --Cursor to find the participation end date from org level information
9703: CURSOR c_get_org_info(c_org_id IN hr_organization_information.organization_id%TYPE) IS
9704: SELECT Fnd_Date.canonical_to_date(Nvl(hoi.org_information2,Fnd_Date.date_to_canonical(Hr_Api.g_eot)))
9705: FROM hr_organization_information hoi
9706: WHERE hoi.org_information_context = 'PQP_NL_ABP_PT'
9707: AND hoi.org_information3 = To_Char(p_pension_type_id)
9708: AND hoi.org_information6 = 'Y'

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

9707: AND hoi.org_information3 = To_Char(p_pension_type_id)
9708: AND hoi.org_information6 = 'Y'
9709: AND NVL(hoi.org_information7,'Y') = 'Y'
9710: AND hoi.organization_id = c_org_id
9711: AND p_date_earned BETWEEN Fnd_Date.canonical_to_date(hoi.org_information1)
9712: AND Fnd_Date.canonical_to_date(Nvl(hoi.org_information2,Fnd_Date.date_to_canonical(Hr_Api.g_eot)));
9713:
9714: BEGIN
9715: -- find the org the assignment is attached to

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

9708: AND hoi.org_information6 = 'Y'
9709: AND NVL(hoi.org_information7,'Y') = 'Y'
9710: AND hoi.organization_id = c_org_id
9711: AND p_date_earned BETWEEN Fnd_Date.canonical_to_date(hoi.org_information1)
9712: AND Fnd_Date.canonical_to_date(Nvl(hoi.org_information2,Fnd_Date.date_to_canonical(Hr_Api.g_eot)));
9713:
9714: BEGIN
9715: -- find the org the assignment is attached to
9716: OPEN c_find_org_id;

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

10099: END IF;
10100: IF l_hired = 1 THEN
10101: Hr_Utility.set_location('hire date : '||l_hire_date,99);
10102: Hr_Utility.set_location('new date : '||g_fur_dates(i).new_start,100);
10103: Hr_Utility.set_location('greater date : '||Fnd_Date.date_to_canonical(Greatest(
10104: l_hire_date,Fnd_Date.canonical_to_date(g_fur_dates(i).new_start))),101);
10105: IF g_fur_dates(i).new_start IS NOT NULL THEN
10106: Hr_Utility.set_location('chking the new start date',102);
10107: g_fur_dates(i).new_start := Fnd_Date.date_to_canonical(Greatest(

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

10100: IF l_hired = 1 THEN
10101: Hr_Utility.set_location('hire date : '||l_hire_date,99);
10102: Hr_Utility.set_location('new date : '||g_fur_dates(i).new_start,100);
10103: Hr_Utility.set_location('greater date : '||Fnd_Date.date_to_canonical(Greatest(
10104: l_hire_date,Fnd_Date.canonical_to_date(g_fur_dates(i).new_start))),101);
10105: IF g_fur_dates(i).new_start IS NOT NULL THEN
10106: Hr_Utility.set_location('chking the new start date',102);
10107: g_fur_dates(i).new_start := Fnd_Date.date_to_canonical(Greatest(
10108: l_hire_date,Fnd_Date.canonical_to_date(g_fur_dates(i).new_start)));

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

10103: Hr_Utility.set_location('greater date : '||Fnd_Date.date_to_canonical(Greatest(
10104: l_hire_date,Fnd_Date.canonical_to_date(g_fur_dates(i).new_start))),101);
10105: IF g_fur_dates(i).new_start IS NOT NULL THEN
10106: Hr_Utility.set_location('chking the new start date',102);
10107: g_fur_dates(i).new_start := Fnd_Date.date_to_canonical(Greatest(
10108: l_hire_date,Fnd_Date.canonical_to_date(g_fur_dates(i).new_start)));
10109: Hr_Utility.set_location('new start date is : '||g_fur_dates(i).new_start,103);
10110: END IF;
10111: IF g_fur_dates(i).old_start IS NOT NULL THEN

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

10104: l_hire_date,Fnd_Date.canonical_to_date(g_fur_dates(i).new_start))),101);
10105: IF g_fur_dates(i).new_start IS NOT NULL THEN
10106: Hr_Utility.set_location('chking the new start date',102);
10107: g_fur_dates(i).new_start := Fnd_Date.date_to_canonical(Greatest(
10108: l_hire_date,Fnd_Date.canonical_to_date(g_fur_dates(i).new_start)));
10109: Hr_Utility.set_location('new start date is : '||g_fur_dates(i).new_start,103);
10110: END IF;
10111: IF g_fur_dates(i).old_start IS NOT NULL THEN
10112: g_fur_dates(i).old_start := Fnd_Date.date_to_canonical(Greatest(

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

10108: l_hire_date,Fnd_Date.canonical_to_date(g_fur_dates(i).new_start)));
10109: Hr_Utility.set_location('new start date is : '||g_fur_dates(i).new_start,103);
10110: END IF;
10111: IF g_fur_dates(i).old_start IS NOT NULL THEN
10112: g_fur_dates(i).old_start := Fnd_Date.date_to_canonical(Greatest(
10113: l_hire_date,Fnd_Date.canonical_to_date(g_fur_dates(i).old_start)));
10114: END IF;
10115: END IF;
10116: IF g_fur_dates(i).new_start = g_fur_dates(i).old_start THEN

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

10109: Hr_Utility.set_location('new start date is : '||g_fur_dates(i).new_start,103);
10110: END IF;
10111: IF g_fur_dates(i).old_start IS NOT NULL THEN
10112: g_fur_dates(i).old_start := Fnd_Date.date_to_canonical(Greatest(
10113: l_hire_date,Fnd_Date.canonical_to_date(g_fur_dates(i).old_start)));
10114: END IF;
10115: END IF;
10116: IF g_fur_dates(i).new_start = g_fur_dates(i).old_start THEN
10117: g_fur_dates(i).old_start := '';

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

10142: IF l_hired = 1 THEN
10143: --chk if there is any run result
10144: FOR c_rec IN c_run_results_exist
10145: LOOP
10146: g_fur_dates(i).new_start := Fnd_Date.date_to_canonical(l_hire_date);
10147: g_fur_dates(i).old_start := '';
10148: --get the end date corresponding to this enrollment
10149: l_ret_val := Get_Participation_End
10150: (p_assignment_id => p_assignment_id

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

10156: IF l_ret_val = 0 THEN
10157: IF l_end_date = hr_api.g_eot THEN
10158: g_fur_dates(i).new_end := '';
10159: ELSE
10160: g_fur_dates(i).new_end := Fnd_Date.date_to_canonical(l_end_date) ;
10161: END IF;
10162: ELSE
10163: g_fur_dates(i).new_end := '';
10164: END IF;

Line 10192: (Fnd_Date.canonical_to_date(p_data_element_value),

10188: END IF;
10189:
10190: -- p_data_element_value := substr(p_data_element_value,1,10);
10191: p_data_element_value := Ben_Ext_Fmt.apply_format_mask
10192: (Fnd_Date.canonical_to_date(p_data_element_value),
10193: 'YYYYMMDD');
10194:
10195: ELSE
10196: p_data_element_value := '';

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

10260: AND paei.assignment_id = p_assignment_id
10261: AND fnd_number.canonical_to_number(NVL(aei_information3,-1)) = pty.pension_type_id
10262: AND p_effective_date BETWEEN pty.effective_start_date and pty.effective_end_date
10263: AND pty.pension_sub_category IN ('PPP')
10264: AND p_effective_date between fnd_date.canonical_to_date(paei.aei_information1)
10265: AND fnd_date.canonical_to_date(NVL(paei.aei_information2,
10266: fnd_date.date_to_canonical(hr_api.g_eot)));
10267:
10268: /* Cursor changed for 6670714

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

10261: AND fnd_number.canonical_to_number(NVL(aei_information3,-1)) = pty.pension_type_id
10262: AND p_effective_date BETWEEN pty.effective_start_date and pty.effective_end_date
10263: AND pty.pension_sub_category IN ('PPP')
10264: AND p_effective_date between fnd_date.canonical_to_date(paei.aei_information1)
10265: AND fnd_date.canonical_to_date(NVL(paei.aei_information2,
10266: fnd_date.date_to_canonical(hr_api.g_eot)));
10267:
10268: /* Cursor changed for 6670714
10269: CURSOR c_rr_cur IS

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

10262: AND p_effective_date BETWEEN pty.effective_start_date and pty.effective_end_date
10263: AND pty.pension_sub_category IN ('PPP')
10264: AND p_effective_date between fnd_date.canonical_to_date(paei.aei_information1)
10265: AND fnd_date.canonical_to_date(NVL(paei.aei_information2,
10266: fnd_date.date_to_canonical(hr_api.g_eot)));
10267:
10268: /* Cursor changed for 6670714
10269: CURSOR c_rr_cur IS
10270: SELECT 1

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

10304: AND hoi1.org_information3 = TO_CHAR(pty.pension_type_id)
10305: AND hoi1.organization_id = paf.organization_id
10306: AND ( NVL(hoi1.org_information6,'N')= 'N'
10307: OR NVL(hoi1.org_information7,'N')= 'N')
10308: AND p_effective_date BETWEEN fnd_date.canonical_to_date(hoi1.org_information1)
10309: AND fnd_date.canonical_to_date(NVL(hoi1.org_information2,
10310: fnd_date.date_to_canonical(hr_api.g_eot))))
10311: ))
10312: AND hoi.org_information_context = 'PQP_NL_ABP_PT'

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

10305: AND hoi1.organization_id = paf.organization_id
10306: AND ( NVL(hoi1.org_information6,'N')= 'N'
10307: OR NVL(hoi1.org_information7,'N')= 'N')
10308: AND p_effective_date BETWEEN fnd_date.canonical_to_date(hoi1.org_information1)
10309: AND fnd_date.canonical_to_date(NVL(hoi1.org_information2,
10310: fnd_date.date_to_canonical(hr_api.g_eot))))
10311: ))
10312: AND hoi.org_information_context = 'PQP_NL_ABP_PT'
10313: AND hoi.org_information3 = TO_CHAR(pty.pension_type_id)

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

10306: AND ( NVL(hoi1.org_information6,'N')= 'N'
10307: OR NVL(hoi1.org_information7,'N')= 'N')
10308: AND p_effective_date BETWEEN fnd_date.canonical_to_date(hoi1.org_information1)
10309: AND fnd_date.canonical_to_date(NVL(hoi1.org_information2,
10310: fnd_date.date_to_canonical(hr_api.g_eot))))
10311: ))
10312: AND hoi.org_information_context = 'PQP_NL_ABP_PT'
10313: AND hoi.org_information3 = TO_CHAR(pty.pension_type_id)
10314: AND p_effective_date BETWEEN pty.effective_start_date AND pty.effective_end_date

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

10314: AND p_effective_date BETWEEN pty.effective_start_date AND pty.effective_end_date
10315: AND pty.pension_sub_category IN ('PPP')
10316: AND NVL(hoi.org_information6,'N') = 'Y'
10317: AND NVL(hoi.org_information7,'N') = 'Y'
10318: AND p_effective_date BETWEEN fnd_date.canonical_to_date(hoi.org_information1)
10319: AND fnd_date.canonical_to_date(NVL(hoi.org_information2,
10320: fnd_date.date_to_canonical(hr_api.g_eot)));
10321:
10322:

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

10315: AND pty.pension_sub_category IN ('PPP')
10316: AND NVL(hoi.org_information6,'N') = 'Y'
10317: AND NVL(hoi.org_information7,'N') = 'Y'
10318: AND p_effective_date BETWEEN fnd_date.canonical_to_date(hoi.org_information1)
10319: AND fnd_date.canonical_to_date(NVL(hoi.org_information2,
10320: fnd_date.date_to_canonical(hr_api.g_eot)));
10321:
10322:
10323: BEGIN

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

10316: AND NVL(hoi.org_information6,'N') = 'Y'
10317: AND NVL(hoi.org_information7,'N') = 'Y'
10318: AND p_effective_date BETWEEN fnd_date.canonical_to_date(hoi.org_information1)
10319: AND fnd_date.canonical_to_date(NVL(hoi.org_information2,
10320: fnd_date.date_to_canonical(hr_api.g_eot)));
10321:
10322:
10323: BEGIN
10324: Hr_Utility.set_location('Entering ------ : '||l_proc_name,10);

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

10456: AND hoi1.org_information3 = TO_CHAR(pty.pension_type_id)
10457: AND hoi1.organization_id = paf.organization_id
10458: AND ( NVL(hoi1.org_information6,'N')= 'N'
10459: OR NVL(hoi1.org_information7,'N')= 'N')
10460: AND p_effective_date BETWEEN fnd_date.canonical_to_date(hoi1.org_information1)
10461: AND fnd_date.canonical_to_date(NVL(hoi1.org_information2,
10462: fnd_date.date_to_canonical(hr_api.g_eot))))
10463: ))
10464: AND hoi.org_information_context = 'PQP_NL_ABP_PT'

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

10457: AND hoi1.organization_id = paf.organization_id
10458: AND ( NVL(hoi1.org_information6,'N')= 'N'
10459: OR NVL(hoi1.org_information7,'N')= 'N')
10460: AND p_effective_date BETWEEN fnd_date.canonical_to_date(hoi1.org_information1)
10461: AND fnd_date.canonical_to_date(NVL(hoi1.org_information2,
10462: fnd_date.date_to_canonical(hr_api.g_eot))))
10463: ))
10464: AND hoi.org_information_context = 'PQP_NL_ABP_PT'
10465: AND hoi.org_information3 = TO_CHAR(pty.pension_type_id)

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

10458: AND ( NVL(hoi1.org_information6,'N')= 'N'
10459: OR NVL(hoi1.org_information7,'N')= 'N')
10460: AND p_effective_date BETWEEN fnd_date.canonical_to_date(hoi1.org_information1)
10461: AND fnd_date.canonical_to_date(NVL(hoi1.org_information2,
10462: fnd_date.date_to_canonical(hr_api.g_eot))))
10463: ))
10464: AND hoi.org_information_context = 'PQP_NL_ABP_PT'
10465: AND hoi.org_information3 = TO_CHAR(pty.pension_type_id)
10466: AND p_effective_date BETWEEN pty.effective_start_date AND pty.effective_end_date

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

10470: 'FPU_S',
10471: 'FPU_T')
10472: AND NVL(hoi.org_information6,'N') = 'Y'
10473: AND NVL(hoi.org_information7,'N') = 'Y'
10474: AND p_effective_date BETWEEN fnd_date.canonical_to_date(hoi.org_information1)
10475: AND fnd_date.canonical_to_date(NVL(hoi.org_information2,
10476: fnd_date.date_to_canonical(hr_api.g_eot)))
10477: AND NOT EXISTS (SELECT 1
10478: FROM per_assignment_extra_info paei1

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

10471: 'FPU_T')
10472: AND NVL(hoi.org_information6,'N') = 'Y'
10473: AND NVL(hoi.org_information7,'N') = 'Y'
10474: AND p_effective_date BETWEEN fnd_date.canonical_to_date(hoi.org_information1)
10475: AND fnd_date.canonical_to_date(NVL(hoi.org_information2,
10476: fnd_date.date_to_canonical(hr_api.g_eot)))
10477: AND NOT EXISTS (SELECT 1
10478: FROM per_assignment_extra_info paei1
10479: WHERE paei1.information_type='NL_ABP_RI'

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

10472: AND NVL(hoi.org_information6,'N') = 'Y'
10473: AND NVL(hoi.org_information7,'N') = 'Y'
10474: AND p_effective_date BETWEEN fnd_date.canonical_to_date(hoi.org_information1)
10475: AND fnd_date.canonical_to_date(NVL(hoi.org_information2,
10476: fnd_date.date_to_canonical(hr_api.g_eot)))
10477: AND NOT EXISTS (SELECT 1
10478: FROM per_assignment_extra_info paei1
10479: WHERE paei1.information_type='NL_ABP_RI'
10480: AND paei1.aei_information_category='NL_ABP_RI'

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

10479: WHERE paei1.information_type='NL_ABP_RI'
10480: AND paei1.aei_information_category='NL_ABP_RI'
10481: AND paei1.assignment_id = p_assignment_id
10482: AND paei1.aei_information3 = 'G'
10483: AND p_effective_date BETWEEN fnd_date.canonical_to_date(paei1.aei_information1)
10484: AND fnd_date.canonical_to_date(NVL(paei1.aei_information2,
10485: fnd_date.date_to_canonical(hr_api.g_eot))));
10486:
10487: CURSOR c_fpu_asg IS

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

10480: AND paei1.aei_information_category='NL_ABP_RI'
10481: AND paei1.assignment_id = p_assignment_id
10482: AND paei1.aei_information3 = 'G'
10483: AND p_effective_date BETWEEN fnd_date.canonical_to_date(paei1.aei_information1)
10484: AND fnd_date.canonical_to_date(NVL(paei1.aei_information2,
10485: fnd_date.date_to_canonical(hr_api.g_eot))));
10486:
10487: CURSOR c_fpu_asg IS
10488: SELECT DISTINCT DECODE (pty.pension_sub_category,'FPU_B','S'

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

10481: AND paei1.assignment_id = p_assignment_id
10482: AND paei1.aei_information3 = 'G'
10483: AND p_effective_date BETWEEN fnd_date.canonical_to_date(paei1.aei_information1)
10484: AND fnd_date.canonical_to_date(NVL(paei1.aei_information2,
10485: fnd_date.date_to_canonical(hr_api.g_eot))));
10486:
10487: CURSOR c_fpu_asg IS
10488: SELECT DISTINCT DECODE (pty.pension_sub_category,'FPU_B','S'
10489: ,'FPU_C','S'

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

10502: 'FPU_E',
10503: 'FPU_R',
10504: 'FPU_S',
10505: 'FPU_T')
10506: AND p_effective_date between fnd_date.canonical_to_date(paei.aei_information1)
10507: AND fnd_date.canonical_to_date(NVL(paei.aei_information2,
10508: fnd_date.date_to_canonical(hr_api.g_eot)))
10509: AND NOT EXISTS (SELECT 1
10510: FROM per_assignment_extra_info paei1

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

10503: 'FPU_R',
10504: 'FPU_S',
10505: 'FPU_T')
10506: AND p_effective_date between fnd_date.canonical_to_date(paei.aei_information1)
10507: AND fnd_date.canonical_to_date(NVL(paei.aei_information2,
10508: fnd_date.date_to_canonical(hr_api.g_eot)))
10509: AND NOT EXISTS (SELECT 1
10510: FROM per_assignment_extra_info paei1
10511: WHERE paei1.information_type='NL_ABP_RI'

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

10504: 'FPU_S',
10505: 'FPU_T')
10506: AND p_effective_date between fnd_date.canonical_to_date(paei.aei_information1)
10507: AND fnd_date.canonical_to_date(NVL(paei.aei_information2,
10508: fnd_date.date_to_canonical(hr_api.g_eot)))
10509: AND NOT EXISTS (SELECT 1
10510: FROM per_assignment_extra_info paei1
10511: WHERE paei1.information_type='NL_ABP_RI'
10512: AND paei1.aei_information_category='NL_ABP_RI'

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

10511: WHERE paei1.information_type='NL_ABP_RI'
10512: AND paei1.aei_information_category='NL_ABP_RI'
10513: AND paei1.assignment_id = p_assignment_id
10514: AND paei1.aei_information3 = 'G'
10515: AND p_effective_date BETWEEN fnd_date.canonical_to_date(paei1.aei_information1)
10516: AND fnd_date.canonical_to_date(NVL(paei1.aei_information2,
10517: fnd_date.date_to_canonical(hr_api.g_eot))));
10518:
10519: BEGIN

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

10512: AND paei1.aei_information_category='NL_ABP_RI'
10513: AND paei1.assignment_id = p_assignment_id
10514: AND paei1.aei_information3 = 'G'
10515: AND p_effective_date BETWEEN fnd_date.canonical_to_date(paei1.aei_information1)
10516: AND fnd_date.canonical_to_date(NVL(paei1.aei_information2,
10517: fnd_date.date_to_canonical(hr_api.g_eot))));
10518:
10519: BEGIN
10520:

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

10513: AND paei1.assignment_id = p_assignment_id
10514: AND paei1.aei_information3 = 'G'
10515: AND p_effective_date BETWEEN fnd_date.canonical_to_date(paei1.aei_information1)
10516: AND fnd_date.canonical_to_date(NVL(paei1.aei_information2,
10517: fnd_date.date_to_canonical(hr_api.g_eot))));
10518:
10519: BEGIN
10520:
10521: Hr_Utility.set_location('Entering ------ : '||l_proc_name,5);

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

10574: CURSOR c_get_contrib_kind IS
10575: SELECT Substr(Nvl(aei_information4,'G'),0,1)
10576: FROM per_assignment_extra_info
10577: WHERE assignment_id = p_assignment_id
10578: AND p_effective_date BETWEEN Fnd_Date.canonical_to_date(aei_information1)
10579: AND Fnd_Date.canonical_to_date(Nvl(aei_information2,Fnd_Date.date_to_canonical(Hr_Api.g_eot)))
10580: AND aei_information_category = 'NL_ABP_RI'
10581: AND information_type = 'NL_ABP_RI';
10582:

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

10575: SELECT Substr(Nvl(aei_information4,'G'),0,1)
10576: FROM per_assignment_extra_info
10577: WHERE assignment_id = p_assignment_id
10578: AND p_effective_date BETWEEN Fnd_Date.canonical_to_date(aei_information1)
10579: AND Fnd_Date.canonical_to_date(Nvl(aei_information2,Fnd_Date.date_to_canonical(Hr_Api.g_eot)))
10580: AND aei_information_category = 'NL_ABP_RI'
10581: AND information_type = 'NL_ABP_RI';
10582:
10583: BEGIN

Line 10616: SELECT fnd_date.canonical_to_date(aei_information1) start_dt

10612: ,p_error_message OUT NOCOPY VARCHAR2
10613: ) RETURN NUMBER IS
10614:
10615: CURSOR c_asg_kind_info IS
10616: SELECT fnd_date.canonical_to_date(aei_information1) start_dt
10617: ,fnd_date.canonical_to_date(NVL(aei_information2,'4712/12/31 00:00:00')) end_dt
10618: ,aei_information4 kind
10619: ,LEAST(fnd_number.canonical_to_number(aei_information5),1) * 100 value
10620: FROM per_assignment_extra_info paei

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

10613: ) RETURN NUMBER IS
10614:
10615: CURSOR c_asg_kind_info IS
10616: SELECT fnd_date.canonical_to_date(aei_information1) start_dt
10617: ,fnd_date.canonical_to_date(NVL(aei_information2,'4712/12/31 00:00:00')) end_dt
10618: ,aei_information4 kind
10619: ,LEAST(fnd_number.canonical_to_number(aei_information5),1) * 100 value
10620: FROM per_assignment_extra_info paei
10621: WHERE paei.assignment_id = p_assignment_id

Line 10624: AND fnd_date.canonical_to_date(aei_information1)

10620: FROM per_assignment_extra_info paei
10621: WHERE paei.assignment_id = p_assignment_id
10622: AND aei_information4 IS NOT NULL
10623: AND paei.information_type = 'NL_ABP_PAR_INFO'
10624: AND fnd_date.canonical_to_date(aei_information1)
10625: BETWEEN g_extract_params(p_business_group_id).extract_start_date
10626: AND g_extract_params(p_business_group_id).extract_end_date
10627: AND NOT EXISTS ( SELECT 1
10628: FROM per_assignment_extra_info paei1

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

10627: AND NOT EXISTS ( SELECT 1
10628: FROM per_assignment_extra_info paei1
10629: WHERE paei1.assignment_id = p_assignment_id
10630: AND paei1.information_type = 'NL_ABP_PAR_INFO'
10631: AND fnd_date.canonical_to_date(paei1.aei_information1) <
10632: g_extract_params(p_business_group_id).extract_start_date
10633: AND paei1.aei_information4 IS NOT NULL
10634: and paei1.aei_information4 = paei.aei_information4
10635: AND fnd_date.canonical_to_date(NVL(paei1.aei_information2,'4712/12/31 00:00:00')) =

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

10631: AND fnd_date.canonical_to_date(paei1.aei_information1) <
10632: g_extract_params(p_business_group_id).extract_start_date
10633: AND paei1.aei_information4 IS NOT NULL
10634: and paei1.aei_information4 = paei.aei_information4
10635: AND fnd_date.canonical_to_date(NVL(paei1.aei_information2,'4712/12/31 00:00:00')) =
10636: fnd_date.canonical_to_date(NVL(paei.aei_information1,'4712/12/31 00:00:00')) - 1
10637: )
10638: UNION
10639: SELECT fnd_date.canonical_to_date(aei_information1) start_dt

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

10632: g_extract_params(p_business_group_id).extract_start_date
10633: AND paei1.aei_information4 IS NOT NULL
10634: and paei1.aei_information4 = paei.aei_information4
10635: AND fnd_date.canonical_to_date(NVL(paei1.aei_information2,'4712/12/31 00:00:00')) =
10636: fnd_date.canonical_to_date(NVL(paei.aei_information1,'4712/12/31 00:00:00')) - 1
10637: )
10638: UNION
10639: SELECT fnd_date.canonical_to_date(aei_information1) start_dt
10640: ,fnd_date.canonical_to_date(NVL(aei_information2,'4712/12/31 00:00:00')) end_dt

Line 10639: SELECT fnd_date.canonical_to_date(aei_information1) start_dt

10635: AND fnd_date.canonical_to_date(NVL(paei1.aei_information2,'4712/12/31 00:00:00')) =
10636: fnd_date.canonical_to_date(NVL(paei.aei_information1,'4712/12/31 00:00:00')) - 1
10637: )
10638: UNION
10639: SELECT fnd_date.canonical_to_date(aei_information1) start_dt
10640: ,fnd_date.canonical_to_date(NVL(aei_information2,'4712/12/31 00:00:00')) end_dt
10641: ,aei_information4 kind
10642: ,LEAST(fnd_number.canonical_to_number(aei_information5),1) * 100 value
10643: FROM per_assignment_extra_info paei

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

10636: fnd_date.canonical_to_date(NVL(paei.aei_information1,'4712/12/31 00:00:00')) - 1
10637: )
10638: UNION
10639: SELECT fnd_date.canonical_to_date(aei_information1) start_dt
10640: ,fnd_date.canonical_to_date(NVL(aei_information2,'4712/12/31 00:00:00')) end_dt
10641: ,aei_information4 kind
10642: ,LEAST(fnd_number.canonical_to_number(aei_information5),1) * 100 value
10643: FROM per_assignment_extra_info paei
10644: WHERE paei.assignment_id = p_assignment_id

Line 10647: AND fnd_date.canonical_to_date(aei_information1)

10643: FROM per_assignment_extra_info paei
10644: WHERE paei.assignment_id = p_assignment_id
10645: AND aei_information4 IS NOT NULL
10646: AND paei.information_type = 'NL_ABP_PAR_INFO'
10647: AND fnd_date.canonical_to_date(aei_information1)
10648: < g_extract_params(p_business_group_id).extract_start_date
10649: AND EXISTS ( SELECT 1 FROM
10650: ben_ext_chg_evt_log blog
10651: WHERE blog.person_id = g_person_id

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

10651: WHERE blog.person_id = g_person_id
10652: AND blog.chg_evt_cd = 'COAPKOP'
10653: AND blog.prmtr_10 = paei.assignment_id
10654: AND fnd_number.canonical_to_number(blog.prmtr_03) = paei.assignment_extra_info_id
10655: AND fnd_date.canonical_to_date(blog.prmtr_09) BETWEEN
10656: g_extract_params(p_business_group_id).extract_start_date
10657: AND g_extract_params(p_business_group_id).extract_end_date)
10658: ORDER BY start_dt;
10659:

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

10664: WHERE paei.assignment_id = p_assignment_id
10665: AND aei_information4 = c_ptpn_kind
10666: AND aei_information4 is not null
10667: AND paei.information_type = 'NL_ABP_PAR_INFO'
10668: AND fnd_date.canonical_to_date(aei_information1) = c_end_date + 1;
10669:
10670: l_subcat VARCHAR2(100);
10671: i NUMBER := 0;
10672: x NUMBER := 0;

Line 10756: SELECT fnd_date.canonical_to_date(aei_information1) start_dt

10752: AND pet.business_group_id = p_business_group_id
10753: AND pei.EEI_INFORMATION12 = p_sub_cat;
10754:
10755: CURSOR c_asg_info ( c_pt_id IN NUMBER) IS
10756: SELECT fnd_date.canonical_to_date(aei_information1) start_dt
10757: ,fnd_date.canonical_to_date(NVL(aei_information2,'4712/12/31 00:00:00')) end_dt
10758: FROM per_assignment_extra_info paei
10759: WHERE paei.assignment_id = p_assignment_id
10760: AND fnd_number.canonical_to_number(aei_information3) = c_pt_id

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

10753: AND pei.EEI_INFORMATION12 = p_sub_cat;
10754:
10755: CURSOR c_asg_info ( c_pt_id IN NUMBER) IS
10756: SELECT fnd_date.canonical_to_date(aei_information1) start_dt
10757: ,fnd_date.canonical_to_date(NVL(aei_information2,'4712/12/31 00:00:00')) end_dt
10758: FROM per_assignment_extra_info paei
10759: WHERE paei.assignment_id = p_assignment_id
10760: AND fnd_number.canonical_to_number(aei_information3) = c_pt_id
10761: AND paei.information_type = 'NL_ABP_PI'

Line 10762: AND fnd_date.canonical_to_date(aei_information1)

10758: FROM per_assignment_extra_info paei
10759: WHERE paei.assignment_id = p_assignment_id
10760: AND fnd_number.canonical_to_number(aei_information3) = c_pt_id
10761: AND paei.information_type = 'NL_ABP_PI'
10762: AND fnd_date.canonical_to_date(aei_information1)
10763: BETWEEN g_extract_params(p_business_group_id).extract_start_date
10764: AND g_extract_params(p_business_group_id).extract_end_date
10765: AND NOT EXISTS ( SELECT 1
10766: FROM per_assignment_extra_info paei1

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

10765: AND NOT EXISTS ( SELECT 1
10766: FROM per_assignment_extra_info paei1
10767: WHERE paei1.assignment_id = p_assignment_id
10768: AND paei1.information_type = 'NL_ABP_PI'
10769: AND fnd_date.canonical_to_date(paei1.aei_information1) <
10770: g_extract_params(p_business_group_id).extract_start_date
10771: AND fnd_number.canonical_to_number(paei1.aei_information3) = c_pt_id
10772: AND fnd_date.canonical_to_date(NVL(paei1.aei_information2,'4712/12/31 00:00:00')) =
10773: fnd_date.canonical_to_date(NVL(paei.aei_information1,'4712/12/31 00:00:00')) -1

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

10768: AND paei1.information_type = 'NL_ABP_PI'
10769: AND fnd_date.canonical_to_date(paei1.aei_information1) <
10770: g_extract_params(p_business_group_id).extract_start_date
10771: AND fnd_number.canonical_to_number(paei1.aei_information3) = c_pt_id
10772: AND fnd_date.canonical_to_date(NVL(paei1.aei_information2,'4712/12/31 00:00:00')) =
10773: fnd_date.canonical_to_date(NVL(paei.aei_information1,'4712/12/31 00:00:00')) -1
10774: )
10775: UNION
10776: SELECT fnd_date.canonical_to_date(aei_information1) start_dt

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

10769: AND fnd_date.canonical_to_date(paei1.aei_information1) <
10770: g_extract_params(p_business_group_id).extract_start_date
10771: AND fnd_number.canonical_to_number(paei1.aei_information3) = c_pt_id
10772: AND fnd_date.canonical_to_date(NVL(paei1.aei_information2,'4712/12/31 00:00:00')) =
10773: fnd_date.canonical_to_date(NVL(paei.aei_information1,'4712/12/31 00:00:00')) -1
10774: )
10775: UNION
10776: SELECT fnd_date.canonical_to_date(aei_information1) start_dt
10777: ,fnd_date.canonical_to_date(NVL(aei_information2,'4712/12/31 00:00:00')) end_dt

Line 10776: SELECT fnd_date.canonical_to_date(aei_information1) start_dt

10772: AND fnd_date.canonical_to_date(NVL(paei1.aei_information2,'4712/12/31 00:00:00')) =
10773: fnd_date.canonical_to_date(NVL(paei.aei_information1,'4712/12/31 00:00:00')) -1
10774: )
10775: UNION
10776: SELECT fnd_date.canonical_to_date(aei_information1) start_dt
10777: ,fnd_date.canonical_to_date(NVL(aei_information2,'4712/12/31 00:00:00')) end_dt
10778: FROM per_assignment_extra_info paei
10779: WHERE paei.assignment_id = p_assignment_id
10780: AND fnd_number.canonical_to_number(aei_information3) = c_pt_id

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

10773: fnd_date.canonical_to_date(NVL(paei.aei_information1,'4712/12/31 00:00:00')) -1
10774: )
10775: UNION
10776: SELECT fnd_date.canonical_to_date(aei_information1) start_dt
10777: ,fnd_date.canonical_to_date(NVL(aei_information2,'4712/12/31 00:00:00')) end_dt
10778: FROM per_assignment_extra_info paei
10779: WHERE paei.assignment_id = p_assignment_id
10780: AND fnd_number.canonical_to_number(aei_information3) = c_pt_id
10781: AND paei.information_type = 'NL_ABP_PI'

Line 10782: AND fnd_date.canonical_to_date(aei_information1)

10778: FROM per_assignment_extra_info paei
10779: WHERE paei.assignment_id = p_assignment_id
10780: AND fnd_number.canonical_to_number(aei_information3) = c_pt_id
10781: AND paei.information_type = 'NL_ABP_PI'
10782: AND fnd_date.canonical_to_date(aei_information1)
10783: < g_extract_params(p_business_group_id).extract_start_date
10784: AND EXISTS ( SELECT 1 FROM
10785: ben_ext_chg_evt_log blog
10786: WHERE blog.person_id = g_person_id

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

10786: WHERE blog.person_id = g_person_id
10787: AND blog.chg_evt_cd = 'COAPP'
10788: AND blog.prmtr_10 = paei.assignment_id
10789: AND fnd_number.canonical_to_number(blog.prmtr_03) = paei.assignment_extra_info_id
10790: AND fnd_date.canonical_to_date(blog.prmtr_09) BETWEEN
10791: g_extract_params(p_business_group_id).extract_start_date
10792: AND g_extract_params(p_business_group_id).extract_end_date)
10793: ORDER BY start_dt ;
10794:

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

10798: FROM per_assignment_extra_info paei
10799: WHERE paei.assignment_id = p_assignment_id
10800: AND fnd_number.canonical_to_number(aei_information3) = c_pt_id
10801: AND paei.information_type = 'NL_ABP_PI'
10802: AND fnd_date.canonical_to_date(aei_information1) = c_end_date + 1;
10803:
10804: l_subcat VARCHAR2(100);
10805: i NUMBER := 0;
10806: x NUMBER := 0;

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

10926: l_rec_09_disp := 'Y';
10927: l_rec_09_values(k).processed := 'N';
10928: l_rec_09_values(k).code := l_rec_09(i).code;
10929: l_rec_09_values(k).date_earned :=
10930: substr(fnd_date.date_to_canonical(l_end_new_st),1,10);
10931:
10932: OPEN c_09_abp_data (l_end_new_st,l_rec_09(i).code);
10933: FETCH c_09_abp_data INTO l_09_basis_amt_sent_prev;
10934: IF c_09_abp_data%FOUND THEN

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

10992: FROM per_assignment_extra_info
10993: WHERE information_type = 'NL_ABP_PAR_INFO'
10994: AND aei_information_category = 'NL_ABP_PAR_INFO'
10995: AND assignment_id = p_assignment_id
10996: AND p_effective_date BETWEEN Fnd_Date.canonical_to_date(aei_information1)
10997: AND Fnd_Date.canonical_to_date(Nvl(aei_information2,Fnd_Date.date_to_canonical(Hr_Api.g_eot)));
10998: --
10999: -- Cursor to get the hire date of the person
11000: --

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

10993: WHERE information_type = 'NL_ABP_PAR_INFO'
10994: AND aei_information_category = 'NL_ABP_PAR_INFO'
10995: AND assignment_id = p_assignment_id
10996: AND p_effective_date BETWEEN Fnd_Date.canonical_to_date(aei_information1)
10997: AND Fnd_Date.canonical_to_date(Nvl(aei_information2,Fnd_Date.date_to_canonical(Hr_Api.g_eot)));
10998: --
10999: -- Cursor to get the hire date of the person
11000: --
11001: CURSOR c_hire_dt IS

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

11135: new_val1 new_date
11136: FROM ben_ext_chg_evt_log
11137: WHERE person_id = c_person_id
11138: AND chg_evt_cd = 'COPOS'
11139: AND fnd_date.canonical_to_date(prmtr_09) BETWEEN c_start_date AND c_end_date
11140: ORDER BY ext_chg_evt_log_id desc;
11141:
11142: CURSOR c_chk_ptpn_continues (c_end_date IN DATE,
11143: c_pt_id IN NUMBER) IS

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

11145: FROM per_assignment_extra_info paei
11146: WHERE paei.assignment_id = p_assignment_id
11147: AND fnd_number.canonical_to_number(aei_information3) = c_pt_id
11148: AND paei.information_type = 'NL_ABP_PI'
11149: AND fnd_date.canonical_to_date(aei_information1) = c_end_date + 1;
11150:
11151: CURSOR c_chk_ptpn_continues_kind (c_end_date IN DATE
11152: ,c_ptpn_kind IN VARCHAR2) IS
11153: SELECT 1

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

11155: WHERE paei.assignment_id = p_assignment_id
11156: AND aei_information4 = c_ptpn_kind
11157: AND aei_information4 is not null
11158: AND paei.information_type = 'NL_ABP_PAR_INFO'
11159: AND fnd_date.canonical_to_date(aei_information1) = c_end_date + 1;
11160:
11161: CURSOR c_rec05_sub_cat IS
11162: SELECT lookup_code sub_cat, DECODE(lookup_code,'PPP','1'
11163: ,'OPNP','G'

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

11459: IF g_abp_processed_flag = 1 THEN
11460: --
11461: -- ABP Pensions has been processed on new hire. Create a new hire row
11462: --
11463: g_rec05_rows(i).new_start := Fnd_Date.date_to_canonical(l_hire_date);
11464: g_rec05_rows(i).dt_chg := NULL;
11465: g_rec05_rows(i).old_start := NULL;
11466: g_rec05_rows(i).end_reason := NULL;
11467: g_rec05_rows(i).eddt_chg := NULL;

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

11529: IF l_hired = 1 THEN
11530: --
11531: -- Update the existing row for reporting the change
11532: --
11533: g_rec05_rows(l_new_hire_row).old_start := Fnd_Date.date_to_canonical(l_hire_date);
11534: g_rec05_rows(l_new_hire_row).new_start := fnd_date.date_to_canonical(l_new_hire_dt);
11535:
11536: OPEN c_copos_ptp_chg (c_asg_id => p_assignment_id
11537: ,c_eff_date => trunc(l_new_hire_dt));

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

11530: --
11531: -- Update the existing row for reporting the change
11532: --
11533: g_rec05_rows(l_new_hire_row).old_start := Fnd_Date.date_to_canonical(l_hire_date);
11534: g_rec05_rows(l_new_hire_row).new_start := fnd_date.date_to_canonical(l_new_hire_dt);
11535:
11536: OPEN c_copos_ptp_chg (c_asg_id => p_assignment_id
11537: ,c_eff_date => trunc(l_new_hire_dt));
11538: FETCH c_copos_ptp_chg INTO l_copos_ptp;

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

11546: -- Create a new Record 05 for reporting the change in hire dt
11547: --
11548: g_rec05_rows(i).new_end := NULL;
11549: g_rec05_rows(i).old_end := NULL;
11550: g_rec05_rows(i).new_start := Fnd_Date.date_to_canonical(l_new_hire_dt);
11551: g_rec05_rows(i).old_start := Fnd_Date.date_to_canonical(l_old_hire_dt);
11552: g_rec05_rows(i).partn_kind := NULL;
11553: g_rec05_rows(i).partn_value := ' ';
11554: g_rec05_rows(i).part_time_perc := NULL;

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

11547: --
11548: g_rec05_rows(i).new_end := NULL;
11549: g_rec05_rows(i).old_end := NULL;
11550: g_rec05_rows(i).new_start := Fnd_Date.date_to_canonical(l_new_hire_dt);
11551: g_rec05_rows(i).old_start := Fnd_Date.date_to_canonical(l_old_hire_dt);
11552: g_rec05_rows(i).partn_kind := NULL;
11553: g_rec05_rows(i).partn_value := ' ';
11554: g_rec05_rows(i).part_time_perc := NULL;
11555: g_rec05_rows(i).dt_chg := NULL;

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

11617: g_rec05_rows(i).old_start := NULL;
11618: g_rec05_rows(i).old_end := NULL;
11619: g_rec05_rows(i).new_start := NULL;
11620: g_rec05_rows(i).new_end := NULL;
11621: g_rec05_rows(i).dt_chg := fnd_date.date_to_canonical
11622: (l_rec_05_retro_ptp(l_retro_ptp_index).start_date);
11623: hr_utility.set_location('....Change date st : '||g_rec05_rows(i).dt_chg,24);
11624: IF l_rec_05_retro_ptp(l_retro_ptp_index).end_date >=
11625: trunc(g_extract_params(p_business_group_id).extract_end_date) THEN

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

11626: g_rec05_rows(i).eddt_chg := NULL;
11627:
11628: --13571541
11629: IF nvl(l_ele_exists,'N') = 'Y' THEN
11630: g_rec05_rows(i).eddt_chg := fnd_date.date_to_canonical(l_rec_05_retro_ptp(l_retro_ptp_index).end_date);
11631: END IF;
11632: --13571541
11633:
11634: ELSE

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

11631: END IF;
11632: --13571541
11633:
11634: ELSE
11635: g_rec05_rows(i).eddt_chg := fnd_date.date_to_canonical(l_rec_05_retro_ptp(l_retro_ptp_index).end_date);
11636: END IF;
11637:
11638: hr_utility.set_location('....Change date End : '||g_rec05_rows(i).eddt_chg,24);
11639: g_rec05_rows(i).part_time_perc := l_rec_05_retro_ptp(l_retro_ptp_index).part_time_perc;

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

11691: IF c_prev_term_dt%NOTFOUND THEN
11692: --
11693: -- Termination was never reported to ABP Report it now.
11694: --
11695: g_rec05_rows(i).new_end := NVL(fnd_date.date_to_canonical(
11696: l_asg_termination_dt),'');
11697: g_rec05_rows(i).old_end := NULL;
11698: g_rec05_rows(i).new_start := '';
11699: g_rec05_rows(i).old_start := '';

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

11710: l_terminated_row := i;
11711: i := i + 1;
11712: g_count_05 := i;
11713: l_term_rows_exist := 1;
11714: ELSIF c_prev_term_dt%FOUND AND TRUNC(fnd_date.canonical_to_date(l_prev_term_dt))
11715: <> TRUNC(l_asg_termination_dt) + 1
11716: THEN
11717: --
11718: -- Termination date has changed from the prev reported value.

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

11717: --
11718: -- Termination date has changed from the prev reported value.
11719: -- Report the old and new dates
11720: --
11721: g_rec05_rows(i).new_end := nvl(fnd_date.date_to_canonical(
11722: l_asg_termination_dt),'');
11723: OPEN c_prev_term_rev(l_seq_num,l_prev_term_dt);
11724: FETCH c_prev_term_rev INTO l_prev_term_rev;
11725: IF c_prev_term_rev%NOTFOUND THEN

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

11722: l_asg_termination_dt),'');
11723: OPEN c_prev_term_rev(l_seq_num,l_prev_term_dt);
11724: FETCH c_prev_term_rev INTO l_prev_term_rev;
11725: IF c_prev_term_rev%NOTFOUND THEN
11726: g_rec05_rows(i).old_end := fnd_date.date_to_canonical(
11727: fnd_date.canonical_to_date(l_prev_term_dt) - 1);
11728: ELSE
11729: g_rec05_rows(i).old_end := NULL;
11730: END IF;

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

11723: OPEN c_prev_term_rev(l_seq_num,l_prev_term_dt);
11724: FETCH c_prev_term_rev INTO l_prev_term_rev;
11725: IF c_prev_term_rev%NOTFOUND THEN
11726: g_rec05_rows(i).old_end := fnd_date.date_to_canonical(
11727: fnd_date.canonical_to_date(l_prev_term_dt) - 1);
11728: ELSE
11729: g_rec05_rows(i).old_end := NULL;
11730: END IF;
11731: g_rec05_rows(i).new_start := '';

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

11767: --
11768: -- Termination reversal was never reported to ABP Report it now.
11769: --
11770: g_rec05_rows(i).new_end := NULL;
11771: g_rec05_rows(i).old_end := fnd_date.date_to_canonical(
11772: fnd_date.canonical_to_date(l_prev_term_dt) - 1);
11773: g_rec05_rows(i).new_start := '';
11774: g_rec05_rows(i).old_start := '';
11775: g_rec05_rows(i).partn_kind := NULL;

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

11768: -- Termination reversal was never reported to ABP Report it now.
11769: --
11770: g_rec05_rows(i).new_end := NULL;
11771: g_rec05_rows(i).old_end := fnd_date.date_to_canonical(
11772: fnd_date.canonical_to_date(l_prev_term_dt) - 1);
11773: g_rec05_rows(i).new_start := '';
11774: g_rec05_rows(i).old_start := '';
11775: g_rec05_rows(i).partn_kind := NULL;
11776: g_rec05_rows(i).partn_value := ' ';

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

11815: FETCH c_hf_pos_cur INTO l_hf_pos_dt;
11816: IF c_hf_pos_cur%FOUND THEN
11817: g_rec05_rows(l_terminated_row).new_start := NULL;
11818: g_rec05_rows(l_terminated_row).dt_chg := NULL;
11819: g_rec05_rows(l_terminated_row).old_start := fnd_date.date_to_canonical(l_hf_pos_dt);
11820: g_rec05_rows(l_terminated_row).end_reason := NULL;
11821: g_rec05_rows(l_terminated_row).eddt_chg := NULL;
11822: g_rec05_rows(l_terminated_row).end_reason := NULL;
11823: g_rec05_rows(l_terminated_row).old_end := NULL;

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

11860: g_rec05_rows(i).part_time_perc := NULL;
11861: g_rec05_rows(i).fpu_kind := ' ';
11862: g_rec05_rows(i).opnp_kind := ' ';
11863: g_rec05_rows(i).ppp_kind := ' ';
11864: g_rec05_rows(i).dt_chg := fnd_date.date_to_canonical(l_out_retro_ptpn_kind(w).start_date);
11865: IF fnd_date.date_to_canonical(TRUNC(l_out_retro_ptpn_kind(w).end_date)) =
11866: '4712/12/31 00:00:00' THEN
11867: g_rec05_rows(i).eddt_chg := NULL;
11868:

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

11861: g_rec05_rows(i).fpu_kind := ' ';
11862: g_rec05_rows(i).opnp_kind := ' ';
11863: g_rec05_rows(i).ppp_kind := ' ';
11864: g_rec05_rows(i).dt_chg := fnd_date.date_to_canonical(l_out_retro_ptpn_kind(w).start_date);
11865: IF fnd_date.date_to_canonical(TRUNC(l_out_retro_ptpn_kind(w).end_date)) =
11866: '4712/12/31 00:00:00' THEN
11867: g_rec05_rows(i).eddt_chg := NULL;
11868:
11869: ELSE

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

11871: OPEN c_chk_ptpn_continues_kind ( l_out_retro_ptpn_kind(w).end_date
11872: ,l_out_retro_ptpn_kind(w).ptpn_kind);
11873: FETCH c_chk_ptpn_continues_kind INTO l_dummy;
11874: IF c_chk_ptpn_continues_kind%NOTFOUND THEN
11875: g_rec05_rows(i).eddt_chg := fnd_date.date_to_canonical(l_out_retro_ptpn_kind(w).end_date);
11876: ELSE
11877: g_rec05_rows(i).eddt_chg := NULL;
11878: END IF;
11879: CLOSE c_chk_ptpn_continues_kind;

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

11935: g_rec05_rows(i).fpu_kind := ' ';
11936: g_rec05_rows(i).opnp_kind := l_rec05_sub_cat.code;
11937: END IF;
11938:
11939: g_rec05_rows(i).dt_chg := fnd_date.date_to_canonical(l_out_retro_ptpn(w).start_date);
11940: IF fnd_date.date_to_canonical(TRUNC(l_out_retro_ptpn(w).end_date)) =
11941: '4712/12/31 00:00:00' THEN
11942: g_rec05_rows(i).eddt_chg := NULL;
11943:

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

11936: g_rec05_rows(i).opnp_kind := l_rec05_sub_cat.code;
11937: END IF;
11938:
11939: g_rec05_rows(i).dt_chg := fnd_date.date_to_canonical(l_out_retro_ptpn(w).start_date);
11940: IF fnd_date.date_to_canonical(TRUNC(l_out_retro_ptpn(w).end_date)) =
11941: '4712/12/31 00:00:00' THEN
11942: g_rec05_rows(i).eddt_chg := NULL;
11943:
11944: ELSE

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

11946: OPEN c_chk_ptpn_continues ( l_out_retro_ptpn(w).end_date
11947: ,l_out_retro_ptpn(w).ptid);
11948: FETCH c_chk_ptpn_continues INTO l_dummy;
11949: IF c_chk_ptpn_continues%NOTFOUND THEN
11950: g_rec05_rows(i).eddt_chg := fnd_date.date_to_canonical(l_out_retro_ptpn(w).end_date);
11951: ELSE
11952: g_rec05_rows(i).eddt_chg := NULL;
11953: END IF;
11954: CLOSE c_chk_ptpn_continues;

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

12003: g_rec05_rows(i).old_start := NULL;
12004: g_rec05_rows(i).old_end := NULL;
12005: g_rec05_rows(i).new_start := NULL;
12006: g_rec05_rows(i).new_end := NULL;
12007: g_rec05_rows(i).dt_chg := fnd_date.date_to_canonical
12008: (hire_ptp_rec.start_date);
12009: hr_utility.set_location('....Start Date is : '||hire_ptp_rec.start_date,24);
12010: hr_utility.set_location('....End Date is : '||hire_ptp_rec.end_date,24);
12011:

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

12012: IF hire_ptp_rec.end_date >=
12013: trunc(g_extract_params(p_business_group_id).extract_end_date) THEN
12014: g_rec05_rows(i).eddt_chg := NULL;
12015: ELSE
12016: g_rec05_rows(i).eddt_chg := fnd_date.date_to_canonical
12017: (hire_ptp_rec.end_date);
12018: END IF;
12019:
12020: hr_utility.set_location('....PTP is : '||hire_ptp_rec.ptp,24);

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

12033: IF hire_ptp_rec.end_date >=
12034: trunc(g_extract_params(p_business_group_id).extract_end_date) THEN
12035: g_rec05_rows(i-1).eddt_chg := NULL;
12036: ELSE
12037: g_rec05_rows(i-1).eddt_chg := fnd_date.date_to_canonical
12038: (hire_ptp_rec.end_date);
12039: END IF;
12040:
12041: END IF;

Line 12079: (Fnd_Date.canonical_to_date(p_data_element_value),

12075: --
12076: IF p_fetch_code = 'NEW_ST' THEN
12077: p_data_element_value := g_rec05_rows(g_index_05).new_start;
12078: p_data_element_value := Ben_Ext_Fmt.apply_format_mask
12079: (Fnd_Date.canonical_to_date(p_data_element_value),
12080: 'YYYYMMDD');
12081: ELSIF p_fetch_code = 'OLD_ST' THEN
12082: p_data_element_value := g_rec05_rows(g_index_05).old_start;
12083: p_data_element_value := Ben_Ext_Fmt.apply_format_mask

Line 12084: (Fnd_Date.canonical_to_date(p_data_element_value),

12080: 'YYYYMMDD');
12081: ELSIF p_fetch_code = 'OLD_ST' THEN
12082: p_data_element_value := g_rec05_rows(g_index_05).old_start;
12083: p_data_element_value := Ben_Ext_Fmt.apply_format_mask
12084: (Fnd_Date.canonical_to_date(p_data_element_value),
12085: 'YYYYMMDD');
12086: ELSIF p_fetch_code = 'NEW_ED' THEN
12087: IF g_rec05_rows(g_index_05).new_end IS NOT NULL THEN
12088: g_rec05_rows(g_index_05).new_end :=

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

12085: 'YYYYMMDD');
12086: ELSIF p_fetch_code = 'NEW_ED' THEN
12087: IF g_rec05_rows(g_index_05).new_end IS NOT NULL THEN
12088: g_rec05_rows(g_index_05).new_end :=
12089: fnd_date.date_to_canonical(fnd_date.canonical_to_date(g_rec05_rows(g_index_05).new_end) + 1);
12090: END IF;
12091: p_data_element_value := g_rec05_rows(g_index_05).new_end;
12092: p_data_element_value := Ben_Ext_Fmt.apply_format_mask
12093: (Fnd_Date.canonical_to_date(p_data_element_value),

Line 12093: (Fnd_Date.canonical_to_date(p_data_element_value),

12089: fnd_date.date_to_canonical(fnd_date.canonical_to_date(g_rec05_rows(g_index_05).new_end) + 1);
12090: END IF;
12091: p_data_element_value := g_rec05_rows(g_index_05).new_end;
12092: p_data_element_value := Ben_Ext_Fmt.apply_format_mask
12093: (Fnd_Date.canonical_to_date(p_data_element_value),
12094: 'YYYYMMDD');
12095: ELSIF p_fetch_code = 'OLD_ED' THEN
12096: IF g_rec05_rows(g_index_05).old_end IS NOT NULL THEN
12097: g_rec05_rows(g_index_05).old_end :=

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

12094: 'YYYYMMDD');
12095: ELSIF p_fetch_code = 'OLD_ED' THEN
12096: IF g_rec05_rows(g_index_05).old_end IS NOT NULL THEN
12097: g_rec05_rows(g_index_05).old_end :=
12098: fnd_date.date_to_canonical(fnd_date.canonical_to_date(g_rec05_rows(g_index_05).old_end) + 1);
12099: END IF;
12100: p_data_element_value := g_rec05_rows(g_index_05).old_end;
12101: p_data_element_value := Ben_Ext_Fmt.apply_format_mask
12102: (Fnd_Date.canonical_to_date(p_data_element_value),

Line 12102: (Fnd_Date.canonical_to_date(p_data_element_value),

12098: fnd_date.date_to_canonical(fnd_date.canonical_to_date(g_rec05_rows(g_index_05).old_end) + 1);
12099: END IF;
12100: p_data_element_value := g_rec05_rows(g_index_05).old_end;
12101: p_data_element_value := Ben_Ext_Fmt.apply_format_mask
12102: (Fnd_Date.canonical_to_date(p_data_element_value),
12103: 'YYYYMMDD');
12104: ELSIF p_fetch_code = 'P_KIND' THEN
12105: p_data_element_value := g_rec05_rows(g_index_05).partn_kind;
12106: ELSIF p_fetch_code = 'P_VALUE' THEN

Line 12124: (Fnd_Date.canonical_to_date(p_data_element_value),

12120: END IF;
12121: ELSIF p_fetch_code = 'DT_CHG' THEN
12122: p_data_element_value := g_rec05_rows(g_index_05).dt_chg;
12123: p_data_element_value := Ben_Ext_Fmt.apply_format_mask
12124: (Fnd_Date.canonical_to_date(p_data_element_value),
12125: 'YYYYMMDD');
12126: ELSIF p_fetch_code = 'EDDT_CHG' THEN
12127:
12128: IF g_rec05_rows(g_index_05).eddt_chg IS NOT NULL THEN

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

12125: 'YYYYMMDD');
12126: ELSIF p_fetch_code = 'EDDT_CHG' THEN
12127:
12128: IF g_rec05_rows(g_index_05).eddt_chg IS NOT NULL THEN
12129: IF fnd_date.canonical_to_date(g_rec05_rows(g_index_05).eddt_chg) >
12130: trunc(g_extract_params(p_business_group_id).extract_end_date) THEN
12131: g_rec05_rows(g_index_05).eddt_chg := NULL;
12132: END IF;
12133: END IF;

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

12133: END IF;
12134:
12135: IF g_rec05_rows(g_index_05).eddt_chg IS NOT NULL THEN
12136: g_rec05_rows(g_index_05).eddt_chg :=
12137: fnd_date.date_to_canonical(fnd_date.canonical_to_date(g_rec05_rows(g_index_05).eddt_chg) + 1);
12138: END IF;
12139: p_data_element_value := g_rec05_rows(g_index_05).eddt_chg;
12140: p_data_element_value := Ben_Ext_Fmt.apply_format_mask
12141: (Fnd_Date.canonical_to_date(p_data_element_value),

Line 12141: (Fnd_Date.canonical_to_date(p_data_element_value),

12137: fnd_date.date_to_canonical(fnd_date.canonical_to_date(g_rec05_rows(g_index_05).eddt_chg) + 1);
12138: END IF;
12139: p_data_element_value := g_rec05_rows(g_index_05).eddt_chg;
12140: p_data_element_value := Ben_Ext_Fmt.apply_format_mask
12141: (Fnd_Date.canonical_to_date(p_data_element_value),
12142: 'YYYYMMDD');
12143: ELSIF p_fetch_code = 'END_REASON' THEN
12144: p_data_element_value := g_rec05_rows(g_index_05).end_reason;
12145: ELSIF p_fetch_code = 'PPP_KIND' THEN

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

12486: END IF;
12487: IF l_hired = 1 THEN
12488: Hr_Utility.set_location('hire date : '||l_hire_date,99);
12489: Hr_Utility.set_location('new date : '||g_ipap_dates(i).new_start,100);
12490: Hr_Utility.set_location('greater date : '||Fnd_Date.date_to_canonical(Greatest(
12491: l_hire_date,Fnd_Date.canonical_to_date(g_ipap_dates(i).new_start))),101);
12492: IF g_ipap_dates(i).new_start IS NOT NULL THEN
12493: Hr_Utility.set_location('chking the new start date',102);
12494: g_ipap_dates(i).new_start := Fnd_Date.date_to_canonical(Greatest(

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

12487: IF l_hired = 1 THEN
12488: Hr_Utility.set_location('hire date : '||l_hire_date,99);
12489: Hr_Utility.set_location('new date : '||g_ipap_dates(i).new_start,100);
12490: Hr_Utility.set_location('greater date : '||Fnd_Date.date_to_canonical(Greatest(
12491: l_hire_date,Fnd_Date.canonical_to_date(g_ipap_dates(i).new_start))),101);
12492: IF g_ipap_dates(i).new_start IS NOT NULL THEN
12493: Hr_Utility.set_location('chking the new start date',102);
12494: g_ipap_dates(i).new_start := Fnd_Date.date_to_canonical(Greatest(
12495: l_hire_date,Fnd_Date.canonical_to_date(g_ipap_dates(i).new_start)));

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

12490: Hr_Utility.set_location('greater date : '||Fnd_Date.date_to_canonical(Greatest(
12491: l_hire_date,Fnd_Date.canonical_to_date(g_ipap_dates(i).new_start))),101);
12492: IF g_ipap_dates(i).new_start IS NOT NULL THEN
12493: Hr_Utility.set_location('chking the new start date',102);
12494: g_ipap_dates(i).new_start := Fnd_Date.date_to_canonical(Greatest(
12495: l_hire_date,Fnd_Date.canonical_to_date(g_ipap_dates(i).new_start)));
12496: Hr_Utility.set_location('new start date is : '||g_ipap_dates(i).new_start,103);
12497: END IF;
12498: IF g_ipap_dates(i).old_start IS NOT NULL THEN

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

12491: l_hire_date,Fnd_Date.canonical_to_date(g_ipap_dates(i).new_start))),101);
12492: IF g_ipap_dates(i).new_start IS NOT NULL THEN
12493: Hr_Utility.set_location('chking the new start date',102);
12494: g_ipap_dates(i).new_start := Fnd_Date.date_to_canonical(Greatest(
12495: l_hire_date,Fnd_Date.canonical_to_date(g_ipap_dates(i).new_start)));
12496: Hr_Utility.set_location('new start date is : '||g_ipap_dates(i).new_start,103);
12497: END IF;
12498: IF g_ipap_dates(i).old_start IS NOT NULL THEN
12499: g_ipap_dates(i).old_start := Fnd_Date.date_to_canonical(Greatest(

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

12495: l_hire_date,Fnd_Date.canonical_to_date(g_ipap_dates(i).new_start)));
12496: Hr_Utility.set_location('new start date is : '||g_ipap_dates(i).new_start,103);
12497: END IF;
12498: IF g_ipap_dates(i).old_start IS NOT NULL THEN
12499: g_ipap_dates(i).old_start := Fnd_Date.date_to_canonical(Greatest(
12500: l_hire_date,Fnd_Date.canonical_to_date(g_ipap_dates(i).old_start)));
12501: END IF;
12502: END IF;
12503: IF g_ipap_dates(i).new_start = g_ipap_dates(i).old_start THEN

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

12496: Hr_Utility.set_location('new start date is : '||g_ipap_dates(i).new_start,103);
12497: END IF;
12498: IF g_ipap_dates(i).old_start IS NOT NULL THEN
12499: g_ipap_dates(i).old_start := Fnd_Date.date_to_canonical(Greatest(
12500: l_hire_date,Fnd_Date.canonical_to_date(g_ipap_dates(i).old_start)));
12501: END IF;
12502: END IF;
12503: IF g_ipap_dates(i).new_start = g_ipap_dates(i).old_start THEN
12504: g_ipap_dates(i).old_start := '';

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

12530: IF l_hired = 1 THEN
12531: --chk if there is any run result
12532: FOR c_rec IN c_run_results_exist
12533: LOOP
12534: g_ipap_dates(i).new_start := Fnd_Date.date_to_canonical(l_hire_date);
12535: g_ipap_dates(i).old_start := '';
12536: --get the end date corresponding to this enrollment
12537: l_ret_val := Get_Participation_End
12538: (p_assignment_id => p_assignment_id

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

12544: IF l_ret_val = 0 THEN
12545: IF l_end_date = hr_api.g_eot THEN
12546: g_ipap_dates(i).new_end := '';
12547: ELSE
12548: g_ipap_dates(i).new_end := Fnd_Date.date_to_canonical(l_end_date) ;
12549: END IF;
12550: ELSE
12551: g_ipap_dates(i).new_end := '';
12552: END IF;

Line 12580: (Fnd_Date.canonical_to_date(p_data_element_value),

12576: END IF;
12577:
12578: -- p_data_element_value := substr(p_data_element_value,1,10);
12579: p_data_element_value := Ben_Ext_Fmt.apply_format_mask
12580: (Fnd_Date.canonical_to_date(p_data_element_value),
12581: 'YYYYMMDD');
12582:
12583: ELSE
12584: p_data_element_value := '';

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

12839: new_val1 new_date
12840: FROM ben_ext_chg_evt_log
12841: WHERE person_id = c_person_id
12842: AND chg_evt_cd = 'COPOS'
12843: AND fnd_date.canonical_to_date(prmtr_09) BETWEEN c_start_date AND c_end_date
12844: ORDER BY ext_chg_evt_log_id desc;
12845:
12846: CURSOR or_pen_sal ( c_nh_date IN DATE) IS
12847: SELECT 1

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

12848: FROM per_assignment_extra_info
12849: WHERE assignment_id = p_assignment_id
12850: AND aei_information_category = 'NL_ABP_PAR_INFO'
12851: AND information_type = 'NL_ABP_PAR_INFO'
12852: AND trunc(c_nh_date) BETWEEN fnd_date.canonical_to_date(aei_information1)
12853: AND fnd_date.canonical_to_date(nvl(aei_information2,
12854: fnd_date.date_to_canonical(hr_api.g_eot)))
12855: AND aei_information6 IS NOT NULL;
12856:

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

12849: WHERE assignment_id = p_assignment_id
12850: AND aei_information_category = 'NL_ABP_PAR_INFO'
12851: AND information_type = 'NL_ABP_PAR_INFO'
12852: AND trunc(c_nh_date) BETWEEN fnd_date.canonical_to_date(aei_information1)
12853: AND fnd_date.canonical_to_date(nvl(aei_information2,
12854: fnd_date.date_to_canonical(hr_api.g_eot)))
12855: AND aei_information6 IS NOT NULL;
12856:
12857: CURSOR c_get_override_salary (c_start IN DATE, c_end IN DATE) IS

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

12850: AND aei_information_category = 'NL_ABP_PAR_INFO'
12851: AND information_type = 'NL_ABP_PAR_INFO'
12852: AND trunc(c_nh_date) BETWEEN fnd_date.canonical_to_date(aei_information1)
12853: AND fnd_date.canonical_to_date(nvl(aei_information2,
12854: fnd_date.date_to_canonical(hr_api.g_eot)))
12855: AND aei_information6 IS NOT NULL;
12856:
12857: CURSOR c_get_override_salary (c_start IN DATE, c_end IN DATE) IS
12858: SELECT fnd_number.canonical_to_number(nvl(new_val1,'0'))

Line 12859: ,fnd_date.canonical_to_date(prmtr_02)

12855: AND aei_information6 IS NOT NULL;
12856:
12857: CURSOR c_get_override_salary (c_start IN DATE, c_end IN DATE) IS
12858: SELECT fnd_number.canonical_to_number(nvl(new_val1,'0'))
12859: ,fnd_date.canonical_to_date(prmtr_02)
12860: FROM ben_ext_chg_evt_log
12861: WHERE person_id = g_person_id
12862: AND fnd_number.canonical_to_number(prmtr_01) = p_assignment_id
12863: AND chg_eff_dt BETWEEN g_extract_params(p_business_group_id).extract_start_date

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

12863: AND chg_eff_dt BETWEEN g_extract_params(p_business_group_id).extract_start_date
12864: AND g_extract_params(p_business_group_id).extract_end_date
12865: AND chg_evt_cd = 'COAPS'
12866: AND fnd_number.canonical_to_number(nvl(new_val1,'0')) <> 0
12867: AND fnd_date.canonical_to_date(prmtr_02) BETWEEN c_start and c_end;
12868:
12869: l_ret_val NUMBER := 0;
12870: l_proc_name VARCHAR2(150) := g_proc_name ||'Process_Mult_Rec08';
12871: l_rcd_id NUMBER;

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

14323: CURSOR c_get_contrib_kind IS
14324: SELECT Substr(Nvl(aei_information7,'D'),0,1)
14325: FROM per_assignment_extra_info
14326: WHERE assignment_id = p_assignment_id
14327: AND p_effective_date BETWEEN Fnd_Date.canonical_to_date(aei_information1)
14328: AND Fnd_Date.canonical_to_date(Nvl(aei_information2,Fnd_Date.date_to_canonical(Hr_Api.g_eot)))
14329: AND aei_information_category = 'NL_ABP_RI'
14330: AND information_type = 'NL_ABP_RI';
14331:

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

14324: SELECT Substr(Nvl(aei_information7,'D'),0,1)
14325: FROM per_assignment_extra_info
14326: WHERE assignment_id = p_assignment_id
14327: AND p_effective_date BETWEEN Fnd_Date.canonical_to_date(aei_information1)
14328: AND Fnd_Date.canonical_to_date(Nvl(aei_information2,Fnd_Date.date_to_canonical(Hr_Api.g_eot)))
14329: AND aei_information_category = 'NL_ABP_RI'
14330: AND information_type = 'NL_ABP_RI';
14331:
14332: BEGIN

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

14406: FROM per_assignment_extra_info
14407: WHERE assignment_id = p_assignment_id
14408: AND aei_information_category = 'NL_ABP_RI'
14409: AND information_type = 'NL_ABP_RI'
14410: AND p_effective_date BETWEEN Fnd_Date.canonical_to_date(aei_information1)
14411: AND Fnd_Date.canonical_to_date(Nvl(aei_information2,Fnd_Date.date_to_canonical(Hr_Api.g_eot)));
14412:
14413: l_proc_name Varchar2(30) := 'Get_Ins_Typ_Anw_Ipap';
14414: l_anw_type Varchar2(2) := '01';

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

14407: WHERE assignment_id = p_assignment_id
14408: AND aei_information_category = 'NL_ABP_RI'
14409: AND information_type = 'NL_ABP_RI'
14410: AND p_effective_date BETWEEN Fnd_Date.canonical_to_date(aei_information1)
14411: AND Fnd_Date.canonical_to_date(Nvl(aei_information2,Fnd_Date.date_to_canonical(Hr_Api.g_eot)));
14412:
14413: l_proc_name Varchar2(30) := 'Get_Ins_Typ_Anw_Ipap';
14414: l_anw_type Varchar2(2) := '01';
14415: l_ipap_type Varchar2(2) := '01';

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

14457: FROM per_assignment_extra_info
14458: WHERE information_type = 'NL_USZO_INFO'
14459: AND aei_information_category = 'NL_USZO_INFO'
14460: AND assignment_id = p_assignment_id
14461: AND p_effective_date BETWEEN Fnd_Date.canonical_to_date(aei_information1)
14462: AND Fnd_Date.canonical_to_date(Nvl(aei_information2,Fnd_Date.date_to_canonical(Hr_Api.g_eot)))
14463: AND ROWNUM = 1;
14464:
14465: l_incidental_worker Varchar2(1) := '0';

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

14458: WHERE information_type = 'NL_USZO_INFO'
14459: AND aei_information_category = 'NL_USZO_INFO'
14460: AND assignment_id = p_assignment_id
14461: AND p_effective_date BETWEEN Fnd_Date.canonical_to_date(aei_information1)
14462: AND Fnd_Date.canonical_to_date(Nvl(aei_information2,Fnd_Date.date_to_canonical(Hr_Api.g_eot)))
14463: AND ROWNUM = 1;
14464:
14465: l_incidental_worker Varchar2(1) := '0';
14466:

Line 14571: SELECT fnd_date.canonical_to_date(aei_information3)

14567: ,p_data_element_value OUT NOCOPY VARCHAR2
14568: ) RETURN NUMBER IS
14569:
14570: CURSOR csr_get_dt (c_assignment_id IN NUMBER) IS
14571: SELECT fnd_date.canonical_to_date(aei_information3)
14572: FROM per_assignment_extra_info
14573: WHERE assignment_id = p_assignment_id
14574: AND information_type = 'PQP_NL_ABP_OLD_EE_INFO';
14575:

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

16542: BEGIN
16543:
16544: Hr_Utility.set_location('Entering: '||l_proc_name, 5);
16545: IF p_header_type = 'HEADER_FILE_SUB_PERIOD' THEN
16546: l_return_value := To_Char(Fnd_Date.canonical_to_date(Fnd_Date.date_to_canonical(g_conc_prog_details(0).beginningdt)),'YYYYMM');
16547: ELSIF p_header_type = 'HEADER_SUB_IDEN' THEN
16548: l_return_value := g_conc_prog_details(0).orgname;
16549: ELSIF p_header_type = 'HEADER_EMR_REG_NUM' THEN
16550: l_new_er_num :=Get_Header_Submit_Code(g_conc_prog_details(0).orgid);