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_NUMBER

Line 527: ,fnd_number.canonical_to_number(prmtr_01)

523: ,c_asg_seq_num IN VARCHAR) IS
524: SELECT old_val1
525: ,to_char(pps.actual_termination_date,'DD/MM/YYYY') term_date
526: ,ext_chg_evt_log_id
527: ,fnd_number.canonical_to_number(prmtr_01)
528: FROM ben_ext_chg_evt_log bec
529: ,per_periods_of_service pps
530: ,per_all_assignments_f asg
531: WHERE bec.chg_evt_cd = 'AAT'

Line 662: SELECT LEAST(fnd_number.canonical_to_number(NVL(target.SEGMENT29,'100')),125) ptp

658: -- a declerant (hourly EE)
659: --
660: CURSOR c_cur_ptp (c_eff_dt IN DATE
661: ,c_asg_id IN NUMBER) IS
662: SELECT LEAST(fnd_number.canonical_to_number(NVL(target.SEGMENT29,'100')),125) ptp
663: FROM per_assignments_f asg
664: ,hr_soft_coding_keyflex target
665: WHERE target.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
666: AND asg.assignment_id = c_asg_id

Line 734: ,sum(fnd_number.canonical_to_number(peev.screen_entry_value)) amount

730: ,pay_paywsmee_pkg.get_original_date_earned(peef.element_entry_id) date_earned
731: ,Decode (pei.eei_information12,'OPNP_65',5,'OPNP_W25',6,'OPNP_W50',7,
732: 'VSG',9,'FPU_E',4,'FPU_R',2,'FPU_S',1,'FPU_T',3,'FPU_B',1
733: ,'PPP',11) code
734: ,sum(fnd_number.canonical_to_number(peev.screen_entry_value)) amount
735: ,pty.ee_contribution_bal_type_id
736: ,pty.er_contribution_bal_type_id
737: FROM pay_element_type_extra_info pei,
738: pay_element_types_f pet,

Line 819: ,sum(fnd_number.canonical_to_number(peev.screen_entry_value)) amount

815: ,c_asg_id IN Number) IS
816: SELECT pei.eei_information12 sub_cat
817: ,pay_paywsmee_pkg.get_original_date_earned(peef.element_entry_id) date_earned
818: ,9 code
819: ,sum(fnd_number.canonical_to_number(peev.screen_entry_value)) amount
820: ,pty.ee_contribution_bal_type_id
821: ,pty.er_contribution_bal_type_id
822: FROM pay_element_type_extra_info pei,
823: pay_element_types_f pet,

Line 950: SELECT fnd_number.canonical_to_number(nvl(screen_entry_value,'0'))

946: -- (for all numeric input values)
947: -- ============================================================================
948: CURSOR c_get_retro_num_value(c_element_entry_id in number
949: ,c_input_value_id in number) IS
950: SELECT fnd_number.canonical_to_number(nvl(screen_entry_value,'0'))
951: FROM pay_element_entry_values_f
952: WHERE element_entry_id = c_element_entry_id
953: AND input_value_id = c_input_value_id;
954:

Line 1028: AND fnd_number.canonical_to_number(NVL(target.SEGMENT29,'100')) > 0;

1024: AND c_period_end
1025: AND asg.assignment_id = c_asg_id
1026: AND target.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
1027: AND target.enabled_flag = 'Y'
1028: AND fnd_number.canonical_to_number(NVL(target.SEGMENT29,'100')) > 0;
1029:
1030: -- ============================================================================
1031: -- Cursor to get the changes to the part time percentage in a retro period
1032: -- ============================================================================

Line 1038: ,fnd_number.canonical_to_number(NVL(target.SEGMENT29,'100')) ptp

1034: ,c_period_start in date
1035: ,c_period_end in date) IS
1036: SELECT asg.effective_start_date Start_Date
1037: ,asg.effective_end_date End_Date
1038: ,fnd_number.canonical_to_number(NVL(target.SEGMENT29,'100')) ptp
1039: FROM per_assignments_f asg
1040: ,per_assignment_status_types past
1041: ,hr_soft_coding_keyflex target
1042: WHERE asg.assignment_status_type_id = past.assignment_status_type_id

Line 1060: ,fnd_number.canonical_to_number(peev.screen_entry_value) ptp

1056: ,c_input_val_id IN NUMBER ) IS
1057:
1058: SELECT to_date('1/'||to_char(pay_paywsmee_pkg.get_original_date_earned(peef.element_entry_id),'MM/YYYY'),'DD/MM/YYYY') start_date
1059: ,add_months(to_date('1/'||to_char(pay_paywsmee_pkg.get_original_date_earned(peef.element_entry_id),'MM/YYYY'),'DD/MM/YYYY'),1) - 1 end_date
1060: ,fnd_number.canonical_to_number(peev.screen_entry_value) ptp
1061: FROM pay_element_entries_f peef,
1062: pay_element_links_f pelf,
1063: pay_element_entry_values_f peev
1064: WHERE c_effective_date BETWEEN peef.effective_start_date AND

Line 1116: ,fnd_number.canonical_to_number(prrv.result_value) ptp, 'Y' Yes

1112: ,c_input_val_id IN NUMBER ) IS
1113:
1114: SELECT to_date('1/'||to_char(ppa.date_earned,'MM/YYYY'),'DD/MM/YYYY') start_date
1115: ,add_months(to_date('1/'||to_char(ppa.date_earned,'MM/YYYY'),'DD/MM/YYYY'),1) - 1 end_date
1116: ,fnd_number.canonical_to_number(prrv.result_value) ptp, 'Y' Yes
1117: FROM pay_element_entries_f peef,
1118: pay_element_links_f pelf,
1119: pay_element_entry_values_f peev,
1120: pay_run_results prr,

Line 1143: AND fnd_number.canonical_to_number(prrv.result_value) > 0 ;

1139: AND paa.assignment_action_id = prr.assignment_action_id
1140: AND pelf.element_type_id = c_ele_type_id
1141: AND peev.input_value_id = c_input_val_id
1142: AND peef.assignment_id = c_asg_id
1143: AND fnd_number.canonical_to_number(prrv.result_value) > 0 ;
1144:
1145: l_ptp_chg_hrly_exist c_ptp_chg_hrly_exist%rowtype;
1146: --6501898
1147:

Line 1201: l_data_value := Fnd_Number.Canonical_To_Number(Nvl(p_data_value,'0'));

1197: FUNCTION IsNumber (p_data_value IN Varchar2)
1198: RETURN Boolean IS
1199: l_data_value Number;
1200: BEGIN
1201: l_data_value := Fnd_Number.Canonical_To_Number(Nvl(p_data_value,'0'));
1202: RETURN TRUE;
1203: EXCEPTION
1204: WHEN Value_Error THEN
1205: RETURN FALSE;

Line 3192: ,fnd_number.canonical_to_number(new_val1) ptp

3188: ,c_asg_ed_dt IN DATE )IS
3189: SELECT assignment_id
3190: ,effective_start_date start_date
3191: ,effective_end_date end_date
3192: ,fnd_number.canonical_to_number(new_val1) ptp
3193: FROM per_all_assignments_f asg,
3194: ben_ext_chg_evt_log log
3195: ,per_assignment_status_types past
3196: ,hr_soft_coding_keyflex sck

Line 3208: AND fnd_number.canonical_to_number(new_val1) =

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
3208: AND fnd_number.canonical_to_number(new_val1) =
3209: fnd_number.canonical_to_number(sck.segment29)
3210: order by effective_start_date;
3211:
3212: l_ptp_log_rows c_ptp_log_rows%ROWTYPE;

Line 3209: fnd_number.canonical_to_number(sck.segment29)

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
3208: AND fnd_number.canonical_to_number(new_val1) =
3209: fnd_number.canonical_to_number(sck.segment29)
3210: order by effective_start_date;
3211:
3212: l_ptp_log_rows c_ptp_log_rows%ROWTYPE;
3213:

Line 3221: SELECT NVL(sum(round(fnd_number.canonical_to_number(peev.screen_entry_value),2)),0) prior_ptp

3217: ,c_orig_st_date IN DATE
3218: ,c_orig_ed_date IN DATE
3219: ,c_ele_type_id IN NUMBER
3220: ,c_input_val_id IN NUMBER ) IS
3221: SELECT NVL(sum(round(fnd_number.canonical_to_number(peev.screen_entry_value),2)),0) prior_ptp
3222: FROM pay_element_entries_f peef,
3223: pay_element_links_f pelf,
3224: pay_element_entry_values_f peev
3225: WHERE peef.effective_start_date < c_effective_date

Line 3253: SELECT fnd_number.canonical_to_number(val_06)/100 basis

3249: ORDER BY ext_rslt_id DESC;
3250:
3251: CURSOR c_09_abp_data (c_eff_dt IN DATE
3252: ,c_pt_code IN VARCHAR2) IS
3253: SELECT fnd_number.canonical_to_number(val_06)/100 basis
3254: FROM ben_ext_rslt_dtl dtl
3255: ,ben_ext_rslt res
3256: ,ben_ext_rcd rcd
3257: ,ben_ext_rcd_in_file rin

Line 3276: SELECT fnd_number.canonical_to_number(val_06)/100 basis

3272: ORDER BY res.ext_rslt_id desc;
3273:
3274: CURSOR c_09_poj_abp_data (c_eff_dt IN DATE
3275: ,c_poj_cd IN VARCHAR2) IS
3276: SELECT fnd_number.canonical_to_number(val_06)/100 basis
3277: ,TRUNC(res.eff_dt) eff_dt
3278: , val_05 code
3279: FROM ben_ext_rslt_dtl dtl
3280: ,ben_ext_rslt res

Line 3328: SELECT fnd_number.canonical_to_number(val_06)/100 amount

3324: ORDER BY res.ext_rslt_id desc;
3325:
3326: CURSOR c_12_abp_data (c_eff_dt IN DATE
3327: ,c_code IN VARCHAR2) IS
3328: SELECT fnd_number.canonical_to_number(val_06)/100 amount
3329: FROM ben_ext_rslt_dtl dtl
3330: ,ben_ext_rslt res
3331: ,ben_ext_rcd rcd
3332: ,ben_ext_rcd_in_file rin

Line 3355: SELECT fnd_number.canonical_to_number(val_06)/100 amount

3351: CURSOR c_12_retro_abp_data (c_year IN VARCHAR2
3352: ,c_mon IN VARCHAR2
3353: ,c_eff_dt IN DATE
3354: ,c_code IN VARCHAR2) IS
3355: SELECT fnd_number.canonical_to_number(val_06)/100 amount
3356: FROM ben_ext_rslt_dtl dtl
3357: ,ben_ext_rslt res
3358: ,ben_ext_rcd rcd
3359: ,ben_ext_rcd_in_file rin

Line 3382: ,least(fnd_number.canonical_to_number(nvl(sck.segment29,100)),125) ptp

3378: CURSOR c_current_ptp_chgs (c_min_st_dt IN DATE ) IS
3379: SELECT asg.assignment_id
3380: ,effective_start_date start_date
3381: ,effective_end_date end_date
3382: ,least(fnd_number.canonical_to_number(nvl(sck.segment29,100)),125) ptp
3383: FROM per_all_assignments_f asg
3384: ,per_assignment_status_types past
3385: ,hr_soft_coding_keyflex sck
3386: WHERE asg.assignment_id = p_assignment_id

Line 3418: AND fnd_number.canonical_to_number(nvl(sck.segment29,'100'))

3414: AND asg1.effective_end_date = asg.effective_start_date - 1
3415: AND asg1.assignment_status_type_id = past1.assignment_status_type_id
3416: AND sck1.soft_coding_keyflex_id = asg1.soft_coding_keyflex_id
3417: AND past1.per_system_status = 'ACTIVE_ASSIGN'
3418: AND fnd_number.canonical_to_number(nvl(sck.segment29,'100'))
3419: <> fnd_number.canonical_to_number(nvl(sck1.segment29,'100'))
3420: )
3421: ORDER BY effective_start_date;
3422:

Line 3419: <> fnd_number.canonical_to_number(nvl(sck1.segment29,'100'))

3415: AND asg1.assignment_status_type_id = past1.assignment_status_type_id
3416: AND sck1.soft_coding_keyflex_id = asg1.soft_coding_keyflex_id
3417: AND past1.per_system_status = 'ACTIVE_ASSIGN'
3418: AND fnd_number.canonical_to_number(nvl(sck.segment29,'100'))
3419: <> fnd_number.canonical_to_number(nvl(sck1.segment29,'100'))
3420: )
3421: ORDER BY effective_start_date;
3422:
3423: CURSOR c_get_hire_dt IS

Line 4082: fnd_number.canonical_to_number(l_rec_09(i).py_retro_element_id)

4078: -- Previous Year Retro
4079: --
4080: FOR temp_rec IN c_get_retro_entry
4081: (c_element_type_id =>
4082: fnd_number.canonical_to_number(l_rec_09(i).py_retro_element_id)
4083: ,c_assignment_action_id => act_rec.assignment_action_id)
4084: LOOP
4085: hr_utility.set_location('..Found previous year retro entries',30);
4086: --

Line 4090: fnd_number.canonical_to_number(l_rec_09(i).py_retro_element_id));

4086: --
4087: -- Fetch the input value id for ABP Employee Pension Basis input value
4088: --
4089: OPEN c_get_iv_id(c_element_type_id =>
4090: fnd_number.canonical_to_number(l_rec_09(i).py_retro_element_id));
4091: FETCH c_get_iv_id INTO l_basis_iv_id;
4092: CLOSE c_get_iv_id;
4093:
4094: --

Line 4132: (c_element_type_id => fnd_number.canonical_to_number(

4128: --
4129: -- Current Year Retro
4130: --
4131: FOR temp_rec IN c_get_retro_entry
4132: (c_element_type_id => fnd_number.canonical_to_number(
4133: l_rec_09(i).cy_retro_element_id)
4134: ,c_assignment_action_id => act_rec.assignment_action_id)
4135: LOOP
4136: hr_utility.set_location('...Found current year retro entries',30);

Line 4141: fnd_number.canonical_to_number(l_rec_09(i).cy_retro_element_id));

4137: --
4138: -- Fetch the input value id for ABP Employee Pension Basis input value
4139: --
4140: OPEN c_get_iv_id(c_element_type_id =>
4141: fnd_number.canonical_to_number(l_rec_09(i).cy_retro_element_id));
4142: FETCH c_get_iv_id INTO l_basis_iv_id;
4143: CLOSE c_get_iv_id;
4144:
4145: --

Line 4585: (c_element_type_id => fnd_number.canonical_to_number(

4581: --populate the PLSQL table with the retro basis entries
4582: -- and the start and end date of the corresponding retro paid period
4583: --for ABP (record 31)
4584: FOR temp_rec IN c_get_retro_entry
4585: (c_element_type_id => fnd_number.canonical_to_number(
4586: l_rec_31(i).py_retro_element_id)
4587: ,c_assignment_action_id => act_rec.assignment_action_id
4588: )
4589: LOOP

Line 4591: OPEN c_get_iv_id(c_element_type_id => fnd_number.canonical_to_number(

4587: ,c_assignment_action_id => act_rec.assignment_action_id
4588: )
4589: LOOP
4590: --fetch the input value id for ABP Employee Pension Basis input value
4591: OPEN c_get_iv_id(c_element_type_id => fnd_number.canonical_to_number(
4592: l_rec_31(i).py_retro_element_id));
4593: FETCH c_get_iv_id INTO l_basis_iv_id;
4594: CLOSE c_get_iv_id;
4595:

Line 4622: (c_element_type_id => fnd_number.canonical_to_number(

4618:
4619: END LOOP;
4620:
4621: FOR temp_rec IN c_get_retro_entry
4622: (c_element_type_id => fnd_number.canonical_to_number(
4623: l_rec_31(i).cy_retro_element_id)
4624: ,c_assignment_action_id => act_rec.assignment_action_id
4625: )
4626: LOOP

Line 4628: OPEN c_get_iv_id(c_element_type_id => fnd_number.canonical_to_number(

4624: ,c_assignment_action_id => act_rec.assignment_action_id
4625: )
4626: LOOP
4627: --fetch the input value id for ABP Employee Pension Basis input value
4628: OPEN c_get_iv_id(c_element_type_id => fnd_number.canonical_to_number(
4629: l_rec_31(i).cy_retro_element_id));
4630: FETCH c_get_iv_id INTO l_basis_iv_id;
4631: CLOSE c_get_iv_id;
4632:

Line 4700: (c_element_type_id => fnd_number.canonical_to_number(

4696: --populate the PLSQL table with the retro basis entries
4697: -- and the start and end date of the corresponding retro paid period
4698: --for ABP (record 41)
4699: FOR temp_rec IN c_get_retro_entry
4700: (c_element_type_id => fnd_number.canonical_to_number(
4701: l_basis_rec_41(i).py_retro_element_id)
4702: ,c_assignment_action_id => act_rec.assignment_action_id
4703: )
4704: LOOP

Line 4706: OPEN c_get_iv_id(c_element_type_id => fnd_number.canonical_to_number(

4702: ,c_assignment_action_id => act_rec.assignment_action_id
4703: )
4704: LOOP
4705: --fetch the input value id for ABP Employee Pension Basis input value
4706: OPEN c_get_iv_id(c_element_type_id => fnd_number.canonical_to_number(
4707: l_basis_rec_41(i).py_retro_element_id));
4708: FETCH c_get_iv_id INTO l_basis_iv_id;
4709: CLOSE c_get_iv_id;
4710:

Line 4737: (c_element_type_id => fnd_number.canonical_to_number(

4733:
4734: END LOOP;
4735:
4736: FOR temp_rec IN c_get_retro_entry
4737: (c_element_type_id => fnd_number.canonical_to_number(
4738: l_basis_rec_41(i).cy_retro_element_id)
4739: ,c_assignment_action_id => act_rec.assignment_action_id
4740: )
4741: LOOP

Line 4743: OPEN c_get_iv_id(c_element_type_id => fnd_number.canonical_to_number(

4739: ,c_assignment_action_id => act_rec.assignment_action_id
4740: )
4741: LOOP
4742: --fetch the input value id for ABP Employee Pension Basis input value
4743: OPEN c_get_iv_id(c_element_type_id => fnd_number.canonical_to_number(
4744: l_basis_rec_41(i).cy_retro_element_id));
4745: FETCH c_get_iv_id INTO l_basis_iv_id;
4746: CLOSE c_get_iv_id;
4747:

Line 7357: Fnd_Number.number_to_canonical(l_balance_amount);

7353: ,p_assignment_action_id => p_asg_act);
7354: Hr_Utility.set_location(' l_balance_amount ' || l_balance_amount , 25);
7355: l_balance_amount := NVL(l_balance_amount,0);
7356: p_data_element_value :=
7357: Fnd_Number.number_to_canonical(l_balance_amount);
7358: l_ret_val := 0;
7359: ELSIF p_asg_act = - 1 THEN
7360:
7361: Hr_Utility.set_location(' l_asg_action_id ' || l_asg_action_id , 20);

Line 7379: Fnd_Number.number_to_canonical(l_balance_amount);

7375: ,p_assignment_action_id => l_asg_action_id);
7376: Hr_Utility.set_location(' l_balance_amount ' || l_balance_amount , 25);
7377: l_balance_amount := NVL(l_balance_amount,0);
7378: p_data_element_value :=
7379: Fnd_Number.number_to_canonical(l_balance_amount);
7380: l_ret_val := 0;
7381: END IF;
7382:
7383: END IF;

Line 7422: := Fnd_Number.number_to_canonical(l_rec_12_values(j).contrib_amount);

7418: IF l_rec_12_values.count > 0 THEN
7419: j := l_rec_12_values.FIRST;
7420: IF l_rec_12_values.EXISTS(j) THEN
7421: p_data_element_value
7422: := Fnd_Number.number_to_canonical(l_rec_12_values(j).contrib_amount);
7423: END IF;
7424: END IF;
7425: ELSIF p_record_number = 41 THEN
7426: IF l_rec_41_contrib_values.count > 0 THEN

Line 7430: := Fnd_Number.number_to_canonical(l_rec_41_contrib_values(j).contrib_amount);

7426: IF l_rec_41_contrib_values.count > 0 THEN
7427: j := l_rec_41_contrib_values.FIRST;
7428: IF l_rec_41_contrib_values.EXISTS(j) THEN
7429: p_data_element_value
7430: := Fnd_Number.number_to_canonical(l_rec_41_contrib_values(j).contrib_amount);
7431: END IF;
7432: END IF;
7433: END IF;
7434: RETURN 0;

Line 7510: AND Fnd_Number.canonical_to_number(prmtr_01) = p_assignment_id

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
7512: AND chg_evt_cd = 'COAPS'
7513: AND ext_chg_evt_log_id =
7514: (SELECT Max(ext_chg_evt_log_id)

Line 7517: AND Fnd_Number.canonical_to_number(prmtr_01) = p_assignment_id

7513: AND ext_chg_evt_log_id =
7514: (SELECT Max(ext_chg_evt_log_id)
7515: FROM ben_ext_chg_evt_log
7516: WHERE person_id = g_person_id
7517: AND Fnd_Number.canonical_to_number(prmtr_01) = p_assignment_id
7518: AND chg_eff_dt BETWEEN p_start_date AND p_end_date
7519: AND chg_evt_cd = 'COAPS');
7520:
7521: l_ret_val NUMBER := -1;

Line 8532: SELECT fnd_number.canonical_to_number(nvl(new_val1,'0'))

8528: --
8529: -- Cursor to check if there are any changes in the ABP Pension Salary
8530: --
8531: CURSOR c_get_override_salary IS
8532: SELECT fnd_number.canonical_to_number(nvl(new_val1,'0'))
8533: FROM ben_ext_chg_evt_log
8534: WHERE person_id = g_person_id
8535: AND fnd_number.canonical_to_number(prmtr_01) = p_assignment_id
8536: AND chg_eff_dt BETWEEN g_extract_params(p_business_group_id).extract_start_date

Line 8535: AND fnd_number.canonical_to_number(prmtr_01) = p_assignment_id

8531: CURSOR c_get_override_salary IS
8532: SELECT fnd_number.canonical_to_number(nvl(new_val1,'0'))
8533: FROM ben_ext_chg_evt_log
8534: WHERE person_id = g_person_id
8535: AND fnd_number.canonical_to_number(prmtr_01) = p_assignment_id
8536: AND chg_eff_dt BETWEEN g_extract_params(p_business_group_id).extract_start_date
8537: AND g_extract_params(p_business_group_id).extract_end_date
8538: AND chg_evt_cd = 'COAPS'
8539: AND ext_chg_evt_log_id =

Line 8543: AND fnd_number.canonical_to_number(prmtr_01) = p_assignment_id

8539: AND ext_chg_evt_log_id =
8540: (SELECT MAX(ext_chg_evt_log_id)
8541: FROM ben_ext_chg_evt_log
8542: WHERE person_id = g_person_id
8543: AND fnd_number.canonical_to_number(prmtr_01) = p_assignment_id
8544: AND chg_eff_dt BETWEEN g_extract_params(p_business_group_id).extract_start_date
8545: AND g_extract_params(p_business_group_id).extract_end_date
8546: AND chg_evt_cd = 'COAPS');
8547:

Line 8949: AND Fnd_Number.canonical_to_number(prmtr_02) = pty.pension_type_id

8945: SELECT 1
8946: FROM ben_ext_chg_evt_log bec,pqp_pension_types_f pty
8947: WHERE chg_evt_cd = 'COAPPD'
8948: AND prmtr_01 = 'ASG'
8949: AND Fnd_Number.canonical_to_number(prmtr_02) = pty.pension_type_id
8950: AND pty.pension_sub_category = p_sub_cat
8951: AND prmtr_03 = 'Y'
8952: AND Fnd_Number.canonical_to_number(prmtr_10) = p_assignment_id
8953: AND person_id = (SELECT person_id

Line 8952: AND Fnd_Number.canonical_to_number(prmtr_10) = p_assignment_id

8948: AND prmtr_01 = 'ASG'
8949: AND Fnd_Number.canonical_to_number(prmtr_02) = pty.pension_type_id
8950: AND pty.pension_sub_category = p_sub_cat
8951: AND prmtr_03 = 'Y'
8952: AND Fnd_Number.canonical_to_number(prmtr_10) = p_assignment_id
8953: AND person_id = (SELECT person_id
8954: FROM per_all_assignments_f
8955: WHERE assignment_id = p_assignment_id
8956: AND p_effective_date BETWEEN effective_start_date

Line 8967: AND Fnd_Number.canonical_to_number(prmtr_02) = pty.pension_type_id

8963: SELECT 1
8964: FROM ben_ext_chg_evt_log bec,pqp_pension_types_f pty
8965: WHERE chg_evt_cd = 'COAPPD'
8966: AND prmtr_01 = 'ORG'
8967: AND Fnd_Number.canonical_to_number(prmtr_02) = pty.pension_type_id
8968: AND pty.pension_sub_category = p_sub_cat
8969: AND prmtr_03 = 'Y'
8970: AND Fnd_Number.canonical_to_number(prmtr_04) = c_org_id
8971: AND bec.business_group_id = p_business_group_id;

Line 8970: AND Fnd_Number.canonical_to_number(prmtr_04) = c_org_id

8966: AND prmtr_01 = 'ORG'
8967: AND Fnd_Number.canonical_to_number(prmtr_02) = pty.pension_type_id
8968: AND pty.pension_sub_category = p_sub_cat
8969: AND prmtr_03 = 'Y'
8970: AND Fnd_Number.canonical_to_number(prmtr_04) = c_org_id
8971: AND bec.business_group_id = p_business_group_id;
8972:
8973: --cursor to get the ASG EIT log rows
8974: CURSOR c_get_asg_rows IS

Line 8979: AND Fnd_Number.canonical_to_number(prmtr_02) = pty.pension_type_id

8975: SELECT 1
8976: FROM ben_ext_chg_evt_log bec,pqp_pension_types_f pty
8977: WHERE chg_evt_cd = 'COAPPD'
8978: AND prmtr_01 = 'ASG'
8979: AND Fnd_Number.canonical_to_number(prmtr_02) = pty.pension_type_id
8980: AND prmtr_03 = 'Y'
8981: AND Fnd_Number.canonical_to_number(prmtr_10) = p_assignment_id
8982: AND pty.pension_sub_category = p_sub_cat
8983: AND person_id = (SELECT person_id

Line 8981: AND Fnd_Number.canonical_to_number(prmtr_10) = p_assignment_id

8977: WHERE chg_evt_cd = 'COAPPD'
8978: AND prmtr_01 = 'ASG'
8979: AND Fnd_Number.canonical_to_number(prmtr_02) = pty.pension_type_id
8980: AND prmtr_03 = 'Y'
8981: AND Fnd_Number.canonical_to_number(prmtr_10) = p_assignment_id
8982: AND pty.pension_sub_category = p_sub_cat
8983: AND person_id = (SELECT person_id
8984: FROM per_all_assignments_f
8985: WHERE assignment_id = p_assignment_id

Line 9000: AND Fnd_Number.canonical_to_number(prmtr_02) = pty.pension_type_id

8996: SELECT 1
8997: FROM ben_ext_chg_evt_log bec,pqp_pension_types_f pty
8998: WHERE chg_evt_cd = 'COAPPD'
8999: AND prmtr_01 = 'ORG'
9000: AND Fnd_Number.canonical_to_number(prmtr_02) = pty.pension_type_id
9001: AND prmtr_03 = 'Y'
9002: AND pty.pension_sub_category = p_sub_cat
9003: AND Fnd_Number.canonical_to_number(prmtr_04) = c_org_id
9004: AND bec.business_group_id = p_business_group_id

Line 9003: AND Fnd_Number.canonical_to_number(prmtr_04) = c_org_id

8999: AND prmtr_01 = 'ORG'
9000: AND Fnd_Number.canonical_to_number(prmtr_02) = pty.pension_type_id
9001: AND prmtr_03 = 'Y'
9002: AND pty.pension_sub_category = p_sub_cat
9003: AND Fnd_Number.canonical_to_number(prmtr_04) = c_org_id
9004: AND bec.business_group_id = p_business_group_id
9005: AND chg_eff_dt BETWEEN g_extract_params(p_business_group_id).extract_start_date
9006: AND g_extract_params(p_business_group_id).extract_end_date
9007: AND chg_eff_dt >= c_hire_date

Line 9076: AND pei.eei_information2 = Fnd_Number.number_to_canonical(pty.pension_type_id)

9072: AND paa.assignment_id = p_assignment_id
9073: AND pei.element_type_id = prr.element_type_id
9074: AND pei.information_type = 'PQP_NL_ABP_DEDUCTION'
9075: AND pei.eei_information_category = 'PQP_NL_ABP_DEDUCTION'
9076: AND pei.eei_information2 = Fnd_Number.number_to_canonical(pty.pension_type_id)
9077: AND pty.pension_sub_category = p_sub_cat;
9078:
9079:
9080: l_proc_name Varchar2(150) := 'Record40_30_Disp_Criteria';

Line 9475: AND Fnd_Number.canonical_to_number(prmtr_02) = pty.pension_type_id

9471: SELECT 1
9472: FROM ben_ext_chg_evt_log bec,pqp_pension_types_f pty
9473: WHERE chg_evt_cd = 'COAPPD'
9474: AND prmtr_01 = 'ASG'
9475: AND Fnd_Number.canonical_to_number(prmtr_02) = pty.pension_type_id
9476: AND pty.pension_sub_category = 'FUR_S'
9477: AND prmtr_03 = 'Y'
9478: AND Fnd_Number.canonical_to_number(prmtr_10) = p_assignment_id
9479: AND person_id = (SELECT person_id

Line 9478: AND Fnd_Number.canonical_to_number(prmtr_10) = p_assignment_id

9474: AND prmtr_01 = 'ASG'
9475: AND Fnd_Number.canonical_to_number(prmtr_02) = pty.pension_type_id
9476: AND pty.pension_sub_category = 'FUR_S'
9477: AND prmtr_03 = 'Y'
9478: AND Fnd_Number.canonical_to_number(prmtr_10) = p_assignment_id
9479: AND person_id = (SELECT person_id
9480: FROM per_all_assignments_f
9481: WHERE assignment_id = p_assignment_id
9482: AND p_effective_date BETWEEN effective_start_date

Line 9493: AND Fnd_Number.canonical_to_number(prmtr_02) = pty.pension_type_id

9489: SELECT 1
9490: FROM ben_ext_chg_evt_log bec,pqp_pension_types_f pty
9491: WHERE chg_evt_cd = 'COAPPD'
9492: AND prmtr_01 = 'ORG'
9493: AND Fnd_Number.canonical_to_number(prmtr_02) = pty.pension_type_id
9494: AND pty.pension_sub_category = 'FUR_S'
9495: AND prmtr_03 = 'Y'
9496: AND Fnd_Number.canonical_to_number(prmtr_04) = c_org_id
9497: AND bec.business_group_id = p_business_group_id;

Line 9496: AND Fnd_Number.canonical_to_number(prmtr_04) = c_org_id

9492: AND prmtr_01 = 'ORG'
9493: AND Fnd_Number.canonical_to_number(prmtr_02) = pty.pension_type_id
9494: AND pty.pension_sub_category = 'FUR_S'
9495: AND prmtr_03 = 'Y'
9496: AND Fnd_Number.canonical_to_number(prmtr_04) = c_org_id
9497: AND bec.business_group_id = p_business_group_id;
9498:
9499: --cursor to get the old and new start and end dates from the ASG EIT
9500: CURSOR c_get_asg_rows IS

Line 9505: AND Fnd_Number.canonical_to_number(prmtr_02) = pty.pension_type_id

9501: SELECT old_val1,new_val1,old_val2,new_val2
9502: FROM ben_ext_chg_evt_log bec,pqp_pension_types_f pty
9503: WHERE chg_evt_cd = 'COAPPD'
9504: AND prmtr_01 = 'ASG'
9505: AND Fnd_Number.canonical_to_number(prmtr_02) = pty.pension_type_id
9506: AND prmtr_03 = 'Y'
9507: AND Fnd_Number.canonical_to_number(prmtr_10) = p_assignment_id
9508: AND pty.pension_sub_category = 'FUR_S'
9509: AND person_id = (SELECT person_id

Line 9507: AND Fnd_Number.canonical_to_number(prmtr_10) = p_assignment_id

9503: WHERE chg_evt_cd = 'COAPPD'
9504: AND prmtr_01 = 'ASG'
9505: AND Fnd_Number.canonical_to_number(prmtr_02) = pty.pension_type_id
9506: AND prmtr_03 = 'Y'
9507: AND Fnd_Number.canonical_to_number(prmtr_10) = p_assignment_id
9508: AND pty.pension_sub_category = 'FUR_S'
9509: AND person_id = (SELECT person_id
9510: FROM per_all_assignments_f
9511: WHERE assignment_id = p_assignment_id

Line 9526: AND Fnd_Number.canonical_to_number(prmtr_02) = pty.pension_type_id

9522: SELECT old_val1,new_val1,old_val2,new_val2
9523: FROM ben_ext_chg_evt_log bec,pqp_pension_types_f pty
9524: WHERE chg_evt_cd = 'COAPPD'
9525: AND prmtr_01 = 'ORG'
9526: AND Fnd_Number.canonical_to_number(prmtr_02) = pty.pension_type_id
9527: AND prmtr_03 = 'Y'
9528: AND pty.pension_sub_category = 'FUR_S'
9529: AND Fnd_Number.canonical_to_number(prmtr_04) = c_org_id
9530: AND bec.business_group_id = p_business_group_id

Line 9529: AND Fnd_Number.canonical_to_number(prmtr_04) = c_org_id

9525: AND prmtr_01 = 'ORG'
9526: AND Fnd_Number.canonical_to_number(prmtr_02) = pty.pension_type_id
9527: AND prmtr_03 = 'Y'
9528: AND pty.pension_sub_category = 'FUR_S'
9529: AND Fnd_Number.canonical_to_number(prmtr_04) = c_org_id
9530: AND bec.business_group_id = p_business_group_id
9531: AND chg_eff_dt BETWEEN g_extract_params(p_business_group_id).extract_start_date
9532: AND g_extract_params(p_business_group_id).extract_end_date
9533: AND chg_eff_dt >= c_hire_date

Line 9602: AND pei.eei_information2 = Fnd_Number.number_to_canonical(pty.pension_type_id)

9598: AND paa.assignment_id = p_assignment_id
9599: AND pei.element_type_id = prr.element_type_id
9600: AND pei.information_type = 'PQP_NL_ABP_DEDUCTION'
9601: AND pei.eei_information_category = 'PQP_NL_ABP_DEDUCTION'
9602: AND pei.eei_information2 = Fnd_Number.number_to_canonical(pty.pension_type_id)
9603: AND pty.pension_sub_category = 'FUR_S';
9604:
9605: l_proc_name Varchar2(150) := g_proc_name ||'get_fur_participation_dates';
9606: l_return_value Number := -1;

Line 9910: AND fnd_number.canonical_to_number(prmtr_10) = p_assignment_id

9906: WHERE chg_evt_cd = 'COAPPD'
9907: AND prmtr_01 = 'ASG'
9908: AND prmtr_04 = 'PPP'
9909: AND prmtr_03 = 'Y'
9910: AND fnd_number.canonical_to_number(prmtr_10) = p_assignment_id
9911: -- AND chg_eff_dt BETWEEN g_extract_params(p_business_group_id).extract_start_date
9912: -- AND g_extract_params(p_business_group_id).extract_end_date
9913: AND bec.business_group_id = p_business_group_id;
9914:

Line 9922: AND fnd_number.canonical_to_number(NVL(aei_information3,-1)) = pty.pension_type_id

9918: pqp_pension_types_f pty
9919: WHERE paei.information_type = 'NL_ABP_PI'
9920: AND paei.aei_information_category = 'NL_ABP_PI'
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,

Line 9945: AND pei.eei_information2 = Fnd_Number.number_to_canonical(pty.pension_type_id)

9941: AND paa.assignment_id = p_assignment_id
9942: AND pei.element_type_id = prr.element_type_id
9943: AND pei.information_type = 'PQP_NL_ABP_DEDUCTION'
9944: AND pei.eei_information_category = 'PQP_NL_ABP_DEDUCTION'
9945: AND pei.eei_information2 = Fnd_Number.number_to_canonical(pty.pension_type_id)
9946: AND pty.pension_sub_category = 'PPP';*/
9947:
9948: CURSOR c_rr_cur IS
9949: SELECT 1

Line 9961: (fnd_number.canonical_to_number(ppf.prl_information1) = hoi.organization_id

9957: AND ppf.prl_information_category = 'NL'
9958: AND p_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
9959: AND (paf.organization_id = hoi.organization_id
9960: OR
9961: (fnd_number.canonical_to_number(ppf.prl_information1) = hoi.organization_id
9962: AND NOT EXISTS (SELECT 1
9963: FROM hr_organization_information hoi1
9964: WHERE hoi1.org_information_context = 'PQP_NL_ABP_PT'
9965: AND hoi1.org_information3 = TO_CHAR(pty.pension_type_id)

Line 10088: AND pei.eei_information2 = Fnd_Number.number_to_canonical(pty.pension_type_id)

10084: AND paa.assignment_id = p_assignment_id
10085: AND pei.element_type_id = prr.element_type_id
10086: AND pei.information_type = 'PQP_NL_ABP_DEDUCTION'
10087: AND pei.eei_information_category = 'PQP_NL_ABP_DEDUCTION'
10088: AND pei.eei_information2 = Fnd_Number.number_to_canonical(pty.pension_type_id)
10089: AND pty.pension_sub_category IN ('FPU_B',
10090: 'FPU_E',
10091: 'FPU_R',
10092: 'FPU_S',

Line 10113: (fnd_number.canonical_to_number(ppf.prl_information1) = hoi.organization_id

10109: AND ppf.prl_information_category = 'NL'
10110: AND p_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
10111: AND (paf.organization_id = hoi.organization_id
10112: OR
10113: (fnd_number.canonical_to_number(ppf.prl_information1) = hoi.organization_id
10114: AND NOT EXISTS (SELECT 1
10115: FROM hr_organization_information hoi1
10116: WHERE hoi1.org_information_context = 'PQP_NL_ABP_PT'
10117: AND hoi1.org_information3 = TO_CHAR(pty.pension_type_id)

Line 10160: AND fnd_number.canonical_to_number(NVL(aei_information3,-1)) = pty.pension_type_id

10156: pqp_pension_types_f pty
10157: WHERE paei.information_type = 'NL_ABP_PI'
10158: AND paei.aei_information_category = 'NL_ABP_PI'
10159: AND paei.assignment_id = p_assignment_id
10160: AND fnd_number.canonical_to_number(NVL(aei_information3,-1)) = pty.pension_type_id
10161: AND p_effective_date BETWEEN pty.effective_start_date and pty.effective_end_date
10162: AND pty.pension_sub_category IN ('FPU_B',
10163: 'FPU_E',
10164: 'FPU_R',

Line 10280: ,LEAST(fnd_number.canonical_to_number(aei_information5),1) * 100 value

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
10283: AND aei_information4 IS NOT NULL
10284: AND paei.information_type = 'NL_ABP_PAR_INFO'

Line 10303: ,LEAST(fnd_number.canonical_to_number(aei_information5),1) * 100 value

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
10306: AND aei_information4 IS NOT NULL
10307: AND paei.information_type = 'NL_ABP_PAR_INFO'

Line 10315: AND fnd_number.canonical_to_number(blog.prmtr_03) = paei.assignment_extra_info_id

10311: ben_ext_chg_evt_log blog
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;

Line 10421: AND fnd_number.canonical_to_number(aei_information3) = c_pt_id

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'
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

Line 10432: AND fnd_number.canonical_to_number(paei1.aei_information3) = c_pt_id

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
10435: )
10436: UNION

Line 10441: AND fnd_number.canonical_to_number(aei_information3) = c_pt_id

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'
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

Line 10450: AND fnd_number.canonical_to_number(blog.prmtr_03) = paei.assignment_extra_info_id

10446: ben_ext_chg_evt_log blog
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 ;

Line 10461: AND fnd_number.canonical_to_number(aei_information3) = c_pt_id

10457: c_pt_id IN NUMBER) IS
10458: SELECT 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);

Line 10652: LEAST(Nvl(fnd_number.canonical_to_number(aei_information5),1),1) VALUE

10648: -- Cursor to fetch the kind and value of participation from the ASG EIT
10649: --
10650: CURSOR c_get_participation_detl IS
10651: SELECT Nvl(aei_information4,'WNE') kind,
10652: LEAST(Nvl(fnd_number.canonical_to_number(aei_information5),1),1) VALUE
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

Line 10691: AND pei.eei_information2 = Fnd_Number.number_to_canonical(pty.pension_type_id)

10687: AND paa.assignment_id = p_assignment_id
10688: AND pei.element_type_id = prr.element_type_id
10689: AND pei.information_type = 'PQP_NL_ABP_DEDUCTION'
10690: AND pei.eei_information_category = 'PQP_NL_ABP_DEDUCTION'
10691: AND pei.eei_information2 = Fnd_Number.number_to_canonical(pty.pension_type_id)
10692: AND (pty.pension_sub_category LIKE 'FPU%'
10693: OR pty.pension_sub_category LIKE 'OPNP%'
10694: OR pty.pension_sub_category = 'PPP');
10695:

Line 10716: ,fnd_number.canonical_to_number(NVL(target.SEGMENT29,'100')) ptp

10712:
10713: CURSOR c_hire_ptp_chg (c_asg_id IN NUMBER) IS
10714: SELECT asg.effective_start_date Start_Date
10715: ,asg.effective_end_date End_Date
10716: ,fnd_number.canonical_to_number(NVL(target.SEGMENT29,'100')) ptp
10717: FROM per_assignments_f asg
10718: ,hr_soft_coding_keyflex target
10719: ,per_assignment_status_types past
10720: WHERE target.soft_coding_keyflex_id = asg.soft_coding_keyflex_id

Line 10808: AND fnd_number.canonical_to_number(aei_information3) = c_pt_id

10804: c_pt_id IN NUMBER) IS
10805: SELECT 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

Line 10865: SELECT fnd_number.canonical_to_number(NVL(target.SEGMENT29,'100')) ptp

10861: AND rin.seq_num = 5;
10862:
10863: CURSOR c_copos_ptp_chg (c_asg_id IN NUMBER
10864: ,c_eff_date IN DATE ) IS
10865: SELECT fnd_number.canonical_to_number(NVL(target.SEGMENT29,'100')) ptp
10866: FROM per_assignments_f asg
10867: ,hr_soft_coding_keyflex target
10868: ,per_assignment_status_types past
10869: WHERE target.soft_coding_keyflex_id = asg.soft_coding_keyflex_id

Line 11013: l_value_num := fnd_number.canonical_to_number(l_value);

11009: l_kind := 'WNE';
11010: l_value := '1';
11011: END IF;
11012:
11013: l_value_num := fnd_number.canonical_to_number(l_value);
11014: l_value_num := l_value_num * 100;
11015: l_value := fnd_number.number_to_canonical(l_value_num);
11016:
11017: Hr_Utility.set_location('....Value of l_value : '||l_value ,14);

Line 11015: l_value := fnd_number.number_to_canonical(l_value_num);

11011: END IF;
11012:
11013: l_value_num := fnd_number.canonical_to_number(l_value);
11014: l_value_num := l_value_num * 100;
11015: l_value := fnd_number.number_to_canonical(l_value_num);
11016:
11017: Hr_Utility.set_location('....Value of l_value : '||l_value ,14);
11018: Hr_Utility.set_location('....Value of l_kind : '||l_kind ,16);
11019:

Line 11791: p_data_element_value := fnd_number.number_to_canonical

11787: ELSIF p_fetch_code = 'PART_TIME_PERC' THEN
11788: IF g_rec05_rows(g_index_05).part_time_perc IS NULL THEN
11789: p_data_element_value := ' ';
11790: ELSE
11791: p_data_element_value := fnd_number.number_to_canonical
11792: (g_rec05_rows(g_index_05).part_time_perc);
11793: END IF;
11794: END IF;
11795:

Line 11835: AND Fnd_Number.canonical_to_number(prmtr_02) = pty.pension_type_id

11831: SELECT 1
11832: FROM ben_ext_chg_evt_log bec,pqp_pension_types_f pty
11833: WHERE chg_evt_cd = 'COAPPD'
11834: AND prmtr_01 = 'ASG'
11835: AND Fnd_Number.canonical_to_number(prmtr_02) = pty.pension_type_id
11836: AND pty.pension_sub_category = 'IPAP'
11837: AND prmtr_03 = 'Y'
11838: AND Fnd_Number.canonical_to_number(prmtr_10) = p_assignment_id
11839: AND person_id = (SELECT person_id

Line 11838: AND Fnd_Number.canonical_to_number(prmtr_10) = p_assignment_id

11834: AND prmtr_01 = 'ASG'
11835: AND Fnd_Number.canonical_to_number(prmtr_02) = pty.pension_type_id
11836: AND pty.pension_sub_category = 'IPAP'
11837: AND prmtr_03 = 'Y'
11838: AND Fnd_Number.canonical_to_number(prmtr_10) = p_assignment_id
11839: AND person_id = (SELECT person_id
11840: FROM per_all_assignments_f
11841: WHERE assignment_id = p_assignment_id
11842: AND p_effective_date BETWEEN effective_start_date

Line 11853: AND Fnd_Number.canonical_to_number(prmtr_02) = pty.pension_type_id

11849: SELECT 1
11850: FROM ben_ext_chg_evt_log bec,pqp_pension_types_f pty
11851: WHERE chg_evt_cd = 'COAPPD'
11852: AND prmtr_01 = 'ORG'
11853: AND Fnd_Number.canonical_to_number(prmtr_02) = pty.pension_type_id
11854: AND pty.pension_sub_category = 'IPAP'
11855: AND prmtr_03 = 'Y'
11856: AND Fnd_Number.canonical_to_number(prmtr_04) = c_org_id
11857: AND bec.business_group_id = p_business_group_id;

Line 11856: AND Fnd_Number.canonical_to_number(prmtr_04) = c_org_id

11852: AND prmtr_01 = 'ORG'
11853: AND Fnd_Number.canonical_to_number(prmtr_02) = pty.pension_type_id
11854: AND pty.pension_sub_category = 'IPAP'
11855: AND prmtr_03 = 'Y'
11856: AND Fnd_Number.canonical_to_number(prmtr_04) = c_org_id
11857: AND bec.business_group_id = p_business_group_id;
11858:
11859: --cursor to get the old and new start and end dates from the ASG EIT
11860: CURSOR c_get_asg_rows IS

Line 11865: AND Fnd_Number.canonical_to_number(prmtr_02) = pty.pension_type_id

11861: SELECT old_val1,new_val1,old_val2,new_val2
11862: FROM ben_ext_chg_evt_log bec,pqp_pension_types_f pty
11863: WHERE chg_evt_cd = 'COAPPD'
11864: AND prmtr_01 = 'ASG'
11865: AND Fnd_Number.canonical_to_number(prmtr_02) = pty.pension_type_id
11866: AND prmtr_03 = 'Y'
11867: AND Fnd_Number.canonical_to_number(prmtr_10) = p_assignment_id
11868: AND pty.pension_sub_category = 'IPAP'
11869: AND person_id = (SELECT person_id

Line 11867: AND Fnd_Number.canonical_to_number(prmtr_10) = p_assignment_id

11863: WHERE chg_evt_cd = 'COAPPD'
11864: AND prmtr_01 = 'ASG'
11865: AND Fnd_Number.canonical_to_number(prmtr_02) = pty.pension_type_id
11866: AND prmtr_03 = 'Y'
11867: AND Fnd_Number.canonical_to_number(prmtr_10) = p_assignment_id
11868: AND pty.pension_sub_category = 'IPAP'
11869: AND person_id = (SELECT person_id
11870: FROM per_all_assignments_f
11871: WHERE assignment_id = p_assignment_id

Line 11886: AND Fnd_Number.canonical_to_number(prmtr_02) = pty.pension_type_id

11882: SELECT old_val1,new_val1,old_val2,new_val2
11883: FROM ben_ext_chg_evt_log bec,pqp_pension_types_f pty
11884: WHERE chg_evt_cd = 'COAPPD'
11885: AND prmtr_01 = 'ORG'
11886: AND Fnd_Number.canonical_to_number(prmtr_02) = pty.pension_type_id
11887: AND prmtr_03 = 'Y'
11888: AND pty.pension_sub_category = 'IPAP'
11889: AND Fnd_Number.canonical_to_number(prmtr_04) = c_org_id
11890: AND bec.business_group_id = p_business_group_id

Line 11889: AND Fnd_Number.canonical_to_number(prmtr_04) = c_org_id

11885: AND prmtr_01 = 'ORG'
11886: AND Fnd_Number.canonical_to_number(prmtr_02) = pty.pension_type_id
11887: AND prmtr_03 = 'Y'
11888: AND pty.pension_sub_category = 'IPAP'
11889: AND Fnd_Number.canonical_to_number(prmtr_04) = c_org_id
11890: AND bec.business_group_id = p_business_group_id
11891: AND chg_eff_dt BETWEEN g_extract_params(p_business_group_id).extract_start_date
11892: AND g_extract_params(p_business_group_id).extract_end_date
11893: AND chg_eff_dt >= c_hire_date

Line 11962: AND pei.eei_information2 = Fnd_Number.number_to_canonical(pty.pension_type_id)

11958: AND paa.assignment_id = p_assignment_id
11959: AND pei.element_type_id = prr.element_type_id
11960: AND pei.information_type = 'PQP_NL_ABP_DEDUCTION'
11961: AND pei.eei_information_category = 'PQP_NL_ABP_DEDUCTION'
11962: AND pei.eei_information2 = Fnd_Number.number_to_canonical(pty.pension_type_id)
11963: AND pty.pension_sub_category = 'IPAP';
11964:
11965: l_proc_name Varchar2(150) := g_proc_name ||'get_ipap_participation_dates';
11966: l_return_value Number := -1;

Line 12489: SELECT fnd_number.canonical_to_number(nvl(new_val1,'0'))

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'))
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

Line 12493: AND fnd_number.canonical_to_number(prmtr_01) = p_assignment_id

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

Line 12497: AND fnd_number.canonical_to_number(nvl(new_val1,'0')) <> 0

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
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';

Line 12603: l_pension_sal_char := Trim(To_Char(ABS(Fnd_Number.Canonical_To_Number

12599: ,p_error_message
12600: ,l_pension_sal_char);
12601:
12602: IF IsNumber(l_pension_sal_char) THEN
12603: l_pension_sal_char := Trim(To_Char(ABS(Fnd_Number.Canonical_To_Number
12604: (Nvl(l_pension_sal_char,'0')))
12605: ,'0999999V99'));
12606: END IF;
12607:

Line 13369: AND eei_information2 = Fnd_Number.number_to_canonical(pty.pension_type_id)

13365: (SELECT element_type_id
13366: FROM pay_element_type_extra_info,pqp_pension_types_f pty
13367: WHERE information_type = 'PQP_NL_ABP_DEDUCTION'
13368: AND eei_information_category = 'PQP_NL_ABP_DEDUCTION'
13369: AND eei_information2 = Fnd_Number.number_to_canonical(pty.pension_type_id)
13370: AND pty.pension_sub_category = 'FUR_S'
13371: )
13372: );
13373:

Line 13401: p_data_element_value := Fnd_Number.number_to_canonical(

13397: --from the dimension _ASG_PTD
13398: CLOSE c_run_results_exist;
13399: Hr_Utility.set_location('found asg action id : '||l_asg_act_id,30);
13400: --from the assignment action id,and defined balance id fetched above , find the balance value
13401: p_data_element_value := Fnd_Number.number_to_canonical(
13402: Pay_Balance_Pkg.get_value(p_defined_balance_id => l_defined_balance_id
13403: ,p_assignment_action_id => l_asg_act_id
13404: ));
13405: Hr_Utility.set_location('found value for the contribution as : '||p_data_element_value,40);

Line 13406: g_fur_contribution := Fnd_Number.canonical_to_number(p_data_element_value);

13402: Pay_Balance_Pkg.get_value(p_defined_balance_id => l_defined_balance_id
13403: ,p_assignment_action_id => l_asg_act_id
13404: ));
13405: Hr_Utility.set_location('found value for the contribution as : '||p_data_element_value,40);
13406: g_fur_contribution := Fnd_Number.canonical_to_number(p_data_element_value);
13407: RETURN 0;
13408: ELSE
13409: CLOSE c_run_results_exist;
13410: Hr_Utility.set_location('could not find the asg action id',50);

Line 13460: AND pei.eei_information2 = Fnd_Number.number_to_canonical(pty.pension_type_id)

13456: AND paa.assignment_id = p_assignment_id
13457: AND pei.element_type_id = prr.element_type_id
13458: AND pei.information_type = 'PQP_NL_ABP_DEDUCTION'
13459: AND pei.eei_information_category = 'PQP_NL_ABP_DEDUCTION'
13460: AND pei.eei_information2 = Fnd_Number.number_to_canonical(pty.pension_type_id)
13461: AND pty.pension_sub_category = p_sub_cat;
13462:
13463: l_asg_act_id Number;
13464: l_scheme_prefix Varchar2(150);

Line 13489: p_data_element_value := Fnd_Number.number_to_canonical(

13485: IF csr_defined_bal%FOUND THEN
13486: CLOSE csr_defined_bal;
13487: Hr_Utility.set_location('found defined balance id : '||l_defined_balance_id,20);
13488: --from the assignment action id,and defined balance id fetched above , find the balance value
13489: p_data_element_value := Fnd_Number.number_to_canonical(
13490: Pay_Balance_Pkg.get_value(p_defined_balance_id => l_defined_balance_id
13491: ,p_assignment_action_id => l_asg_act_id
13492: ));
13493: Hr_Utility.set_location('found value for the contribution as : '||p_data_element_value,40);

Line 13495: g_fur_contribution := Fnd_Number.canonical_to_number(p_data_element_value);

13491: ,p_assignment_action_id => l_asg_act_id
13492: ));
13493: Hr_Utility.set_location('found value for the contribution as : '||p_data_element_value,40);
13494: IF p_sub_cat = 'FUR_S' THEN
13495: g_fur_contribution := Fnd_Number.canonical_to_number(p_data_element_value);
13496: RETURN 0;
13497: ELSIF p_sub_cat = 'IPAP' THEN
13498: g_ipap_contribution := Fnd_Number.canonical_to_number(p_data_element_value);
13499: RETURN 0;

Line 13498: g_ipap_contribution := Fnd_Number.canonical_to_number(p_data_element_value);

13494: IF p_sub_cat = 'FUR_S' THEN
13495: g_fur_contribution := Fnd_Number.canonical_to_number(p_data_element_value);
13496: RETURN 0;
13497: ELSIF p_sub_cat = 'IPAP' THEN
13498: g_ipap_contribution := Fnd_Number.canonical_to_number(p_data_element_value);
13499: RETURN 0;
13500: END IF;
13501: ELSE
13502: CLOSE csr_defined_bal;

Line 13548: Fnd_Number.number_to_canonical(l_rec_09_values(j).basis_amount);

13544: IF l_rec_09_values.count > 0 THEN
13545: j := l_rec_09_values.FIRST;
13546: IF l_rec_09_values.EXISTS(j) THEN
13547: p_data_element_value :=
13548: Fnd_Number.number_to_canonical(l_rec_09_values(j).basis_amount);
13549: END IF;
13550: END IF;
13551: ELSIF p_record_number = 31 THEN
13552: IF l_rec_31_values.count > 0 THEN

Line 13556: Fnd_Number.number_to_canonical(l_rec_31_values(j).basis_amount);

13552: IF l_rec_31_values.count > 0 THEN
13553: j := l_rec_31_values.FIRST;
13554: IF l_rec_31_values.EXISTS(j) THEN
13555: p_data_element_value :=
13556: Fnd_Number.number_to_canonical(l_rec_31_values(j).basis_amount);
13557: END IF;
13558: END IF;
13559: ELSIF p_record_number = 41 THEN
13560: IF l_rec_41_basis_values.count > 0 THEN

Line 13564: Fnd_Number.number_to_canonical(l_rec_41_basis_values(j).basis_amount);

13560: IF l_rec_41_basis_values.count > 0 THEN
13561: j := l_rec_41_basis_values.FIRST;
13562: IF l_rec_41_basis_values.EXISTS(j) THEN
13563: p_data_element_value :=
13564: Fnd_Number.number_to_canonical(l_rec_41_basis_values(j).basis_amount);
13565: END IF;
13566: END IF;
13567: END IF;
13568:

Line 13752: AND eei_information2 = Fnd_Number.number_to_canonical(pty.pension_type_id)

13748: (SELECT element_type_id
13749: FROM pay_element_type_extra_info,pqp_pension_types_f pty
13750: WHERE information_type = 'PQP_NL_ABP_DEDUCTION'
13751: AND eei_information_category = 'PQP_NL_ABP_DEDUCTION'
13752: AND eei_information2 = Fnd_Number.number_to_canonical(pty.pension_type_id)
13753: AND pty.pension_sub_category = 'IPAP'
13754: )
13755: );
13756:

Line 13783: p_data_element_value := Fnd_Number.number_to_canonical(

13779: --from the dimension _ASG_PTD
13780: CLOSE c_run_results_exist;
13781: Hr_Utility.set_location('found asg action id : '||l_asg_act_id,30);
13782: --from the assignment action id,and defined balance id fetched above , find the balance value
13783: p_data_element_value := Fnd_Number.number_to_canonical(
13784: Pay_Balance_Pkg.get_value(p_defined_balance_id => l_defined_balance_id
13785: ,p_assignment_action_id => l_asg_act_id
13786: ));
13787: Hr_Utility.set_location('found value for the contribution as : '||p_data_element_value,40);

Line 13788: g_ipap_contribution := Fnd_Number.canonical_to_number(p_data_element_value);

13784: Pay_Balance_Pkg.get_value(p_defined_balance_id => l_defined_balance_id
13785: ,p_assignment_action_id => l_asg_act_id
13786: ));
13787: Hr_Utility.set_location('found value for the contribution as : '||p_data_element_value,40);
13788: g_ipap_contribution := Fnd_Number.canonical_to_number(p_data_element_value);
13789: RETURN 0;
13790: ELSE
13791: CLOSE c_run_results_exist;
13792: Hr_Utility.set_location('could not find the asg action id',50);

Line 14892: p_data_element_value := Trim(To_Char(Fnd_Number.Canonical_To_Number

14888: ,'NEW'
14889: ,p_error_message
14890: ,p_data_element_value);
14891: IF IsNumber(p_data_element_value) THEN
14892: p_data_element_value := Trim(To_Char(Fnd_Number.Canonical_To_Number
14893: (Nvl(p_data_element_value,'0'))
14894: ,'0999999'));
14895: END IF;
14896:

Line 14925: p_data_element_value := Trim(To_Char(Fnd_Number.Canonical_To_Number

14921: ,p_data_element_value);
14922:
14923: IF p_data_element_value IS NOT NULL THEN
14924: IF IsNumber(p_data_element_value) THEN
14925: p_data_element_value := Trim(To_Char(Fnd_Number.Canonical_To_Number
14926: (p_data_element_value)
14927: ,'0999999'));
14928: END IF;
14929: ELSE

Line 15200: p_data_element_value := Trim(To_Char(ABS(Fnd_Number.Canonical_To_Number

15196: ,p_error_message
15197: ,p_data_element_value);
15198:
15199: IF IsNumber(p_data_element_value) THEN
15200: p_data_element_value := Trim(To_Char(ABS(Fnd_Number.Canonical_To_Number
15201: (Nvl(p_data_element_value,'0')))
15202: ,'0999999V99'));
15203: END IF;
15204:

Line 15283: p_data_element_value := Trim(To_Char(ABS(Fnd_Number.Canonical_To_Number

15279: ,p_error_message
15280: ,p_data_element_value);
15281:
15282: IF IsNumber(p_data_element_value) THEN
15283: p_data_element_value := Trim(To_Char(ABS(Fnd_Number.Canonical_To_Number
15284: (Nvl(p_data_element_value,'0')))
15285: ,'0999999V99'));
15286: END IF;
15287:

Line 15366: p_data_element_value :=Trim( To_Char(ABS(Fnd_Number.Canonical_To_Number

15362: ,p_error_message
15363: ,p_data_element_value);
15364:
15365: IF IsNumber(p_data_element_value) THEN
15366: p_data_element_value :=Trim( To_Char(ABS(Fnd_Number.Canonical_To_Number
15367: (Nvl(p_data_element_value,'0')))
15368: ,'0999999V99'));
15369: END IF;
15370:

Line 15542: p_data_element_value := Trim(To_Char(ABS(Fnd_Number.Canonical_To_Number

15538: ,p_error_message
15539: ,p_data_element_value);
15540:
15541: IF IsNumber(p_data_element_value) THEN
15542: p_data_element_value := Trim(To_Char(ABS(Fnd_Number.Canonical_To_Number
15543: (Nvl(p_data_element_value,'0')))
15544: ,'0999999V99'));
15545: END IF;
15546:

Line 15559: p_data_element_value := Trim(To_Char(ABS(Fnd_Number.Canonical_To_Number

15555: ,p_error_message
15556: ,p_data_element_value);
15557:
15558: IF IsNumber(p_data_element_value) THEN
15559: p_data_element_value := Trim(To_Char(ABS(Fnd_Number.Canonical_To_Number
15560: (Nvl(p_data_element_value,'0')))
15561: ,'0999999V99'));
15562: END IF;
15563:

Line 15574: p_data_element_value :=Trim( To_Char(ABS(Fnd_Number.Canonical_To_Number

15570: ,p_error_message
15571: ,p_data_element_value);
15572:
15573: IF IsNumber(p_data_element_value) THEN
15574: p_data_element_value :=Trim( To_Char(ABS(Fnd_Number.Canonical_To_Number
15575: (Nvl(p_data_element_value,'0')))
15576: ,'0999999V99'));
15577: END IF;
15578:

Line 15917: p_data_element_value := Trim(To_Char(ABS(Fnd_Number.Canonical_To_Number

15913: ,p_error_message
15914: ,p_data_element_value);
15915:
15916: IF IsNumber(p_data_element_value) THEN
15917: p_data_element_value := Trim(To_Char(ABS(Fnd_Number.Canonical_To_Number
15918: (p_data_element_value))
15919: ,'09999'));
15920: END IF;
15921:

Line 16037: p_data_element_value := Trim(To_Char(ABS(Fnd_Number.Canonical_To_Number

16033: ,p_error_message
16034: ,p_data_element_value);
16035:
16036: IF IsNumber(p_data_element_value) THEN
16037: p_data_element_value := Trim(To_Char(ABS(Fnd_Number.Canonical_To_Number
16038: (Nvl(p_data_element_value,'0')))
16039: ,'099'));
16040: END IF;
16041:

Line 16176: l_return_value := Trim(To_Char(Fnd_Number.Canonical_To_Number

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);
16174: l_return_value := l_new_er_num;
16175: IF IsNumber(l_return_value) THEN
16176: l_return_value := Trim(To_Char(Fnd_Number.Canonical_To_Number
16177: (Nvl(l_return_value,'0'))
16178: ,'0999'));
16179: END IF;
16180:

Line 16567: l_header_er_num := Trim(To_Char(Fnd_Number.Canonical_To_Number

16563: --Updating the employer name and employer number
16564: l_header_er_num :=Get_Header_Submit_Code(g_conc_prog_details(0).orgid);
16565:
16566: IF IsNumber(l_header_er_num) THEN
16567: l_header_er_num := Trim(To_Char(Fnd_Number.Canonical_To_Number
16568: (Nvl(l_header_er_num,'0'))
16569: ,'0999'));
16570:
16571: END IF;

Line 16924: l_trailer_er_num := Trim(To_Char(Fnd_Number.Canonical_To_Number

16920: --Updating the current ER Num
16921: l_trailer_er_num :=Get_Header_EMR_Number(g_employer_list(l_employer_index).gre_org_id,g_conc_prog_details(0).endingdt);
16922:
16923: IF IsNumber(l_trailer_er_num) THEN
16924: l_trailer_er_num := Trim(To_Char(Fnd_Number.Canonical_To_Number
16925: (Nvl(l_trailer_er_num,'0'))
16926: ,'0999999'));
16927: END IF;
16928: