[Home] [Help]
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'
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
749: ,pay_paywsmee_pkg.get_original_date_earned(peef.element_entry_id) date_earned
750: ,Decode (pei.eei_information12,'OPNP_65',5,'OPNP_W25',6,'OPNP_W50',7,
751: 'VSG',9,'FPU_E',4,'FPU_R',2,'FPU_S',1,'FPU_T',3,'FPU_B',1
752: ,'PPP',11) code
753: ,sum(fnd_number.canonical_to_number(peev.screen_entry_value)) amount
754: ,pty.ee_contribution_bal_type_id
755: ,pty.er_contribution_bal_type_id
756: FROM pay_element_type_extra_info pei,
757: pay_element_types_f pet,
834: ,c_asg_id IN Number) IS
835: SELECT pei.eei_information12 sub_cat
836: ,pay_paywsmee_pkg.get_original_date_earned(peef.element_entry_id) date_earned
837: ,9 code
838: ,sum(fnd_number.canonical_to_number(peev.screen_entry_value)) amount
839: ,pty.ee_contribution_bal_type_id
840: ,pty.er_contribution_bal_type_id
841: FROM pay_element_type_extra_info pei,
842: pay_element_types_f pet,
965: -- (for all numeric input values)
966: -- ============================================================================
967: CURSOR c_get_retro_num_value(c_element_entry_id in number
968: ,c_input_value_id in number) IS
969: SELECT fnd_number.canonical_to_number(nvl(screen_entry_value,'0'))
970: FROM pay_element_entry_values_f
971: WHERE element_entry_id = c_element_entry_id
972: AND input_value_id = c_input_value_id;
973:
1043: AND c_period_end
1044: AND asg.assignment_id = c_asg_id
1045: AND target.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
1046: AND target.enabled_flag = 'Y'
1047: AND fnd_number.canonical_to_number(NVL(target.SEGMENT29,'100')) > 0;
1048:
1049: -- ============================================================================
1050: -- Cursor to get the changes to the part time percentage in a retro period
1051: -- ============================================================================
1053: ,c_period_start in date
1054: ,c_period_end in date) IS
1055: SELECT asg.effective_start_date Start_Date
1056: ,asg.effective_end_date End_Date
1057: ,fnd_number.canonical_to_number(NVL(target.SEGMENT29,'100')) ptp
1058: FROM per_assignments_f asg
1059: ,per_assignment_status_types past
1060: ,hr_soft_coding_keyflex target
1061: WHERE asg.assignment_status_type_id = past.assignment_status_type_id
1075: ,c_input_val_id IN NUMBER ) IS
1076:
1077: SELECT to_date('1/'||to_char(pay_paywsmee_pkg.get_original_date_earned(peef.element_entry_id),'MM/YYYY'),'DD/MM/YYYY') start_date
1078: ,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
1079: ,fnd_number.canonical_to_number(peev.screen_entry_value) ptp
1080: FROM pay_element_entries_f peef,
1081: pay_element_links_f pelf,
1082: pay_element_entry_values_f peev
1083: WHERE c_effective_date BETWEEN peef.effective_start_date AND
1131: ,c_input_val_id IN NUMBER ) IS
1132:
1133: SELECT to_date('1/'||to_char(ppa.date_earned,'MM/YYYY'),'DD/MM/YYYY') start_date
1134: ,add_months(to_date('1/'||to_char(ppa.date_earned,'MM/YYYY'),'DD/MM/YYYY'),1) - 1 end_date
1135: ,fnd_number.canonical_to_number(prrv.result_value) ptp, 'Y' Yes
1136: FROM pay_element_entries_f peef,
1137: pay_element_links_f pelf,
1138: pay_element_entry_values_f peev,
1139: pay_run_results prr,
1158: AND paa.assignment_action_id = prr.assignment_action_id
1159: AND pelf.element_type_id = c_ele_type_id
1160: AND peev.input_value_id = c_input_val_id
1161: AND peef.assignment_id = c_asg_id
1162: AND fnd_number.canonical_to_number(prrv.result_value) > 0 ;
1163:
1164: l_ptp_chg_hrly_exist c_ptp_chg_hrly_exist%rowtype;
1165: --6501898
1166:
1217: FUNCTION IsNumber (p_data_value IN Varchar2)
1218: RETURN Boolean IS
1219: l_data_value Number;
1220: BEGIN
1221: l_data_value := Fnd_Number.Canonical_To_Number(Nvl(p_data_value,'0'));
1222: RETURN TRUE;
1223: EXCEPTION
1224: WHEN Value_Error THEN
1225: RETURN FALSE;
3209: ,c_asg_ed_dt IN DATE )IS
3210: SELECT assignment_id
3211: ,effective_start_date start_date
3212: ,effective_end_date end_date
3213: ,fnd_number.canonical_to_number(new_val1) ptp
3214: FROM per_all_assignments_f asg,
3215: ben_ext_chg_evt_log log
3216: ,per_assignment_status_types past
3217: ,hr_soft_coding_keyflex sck
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
3229: AND fnd_number.canonical_to_number(new_val1) =
3230: fnd_number.canonical_to_number(sck.segment29)
3231: order by effective_start_date;
3232:
3233: l_ptp_log_rows c_ptp_log_rows%ROWTYPE;
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
3229: AND fnd_number.canonical_to_number(new_val1) =
3230: fnd_number.canonical_to_number(sck.segment29)
3231: order by effective_start_date;
3232:
3233: l_ptp_log_rows c_ptp_log_rows%ROWTYPE;
3234:
3238: ,c_orig_st_date IN DATE
3239: ,c_orig_ed_date IN DATE
3240: ,c_ele_type_id IN NUMBER
3241: ,c_input_val_id IN NUMBER ) IS
3242: SELECT NVL(sum(round(fnd_number.canonical_to_number(peev.screen_entry_value),2)),0) prior_ptp
3243: FROM pay_element_entries_f peef,
3244: pay_element_links_f pelf,
3245: pay_element_entry_values_f peev
3246: WHERE peef.effective_start_date < c_effective_date
3270: ORDER BY ext_rslt_id DESC;
3271:
3272: CURSOR c_09_abp_data (c_eff_dt IN DATE
3273: ,c_pt_code IN VARCHAR2) IS
3274: SELECT fnd_number.canonical_to_number(val_06)/100 basis
3275: FROM ben_ext_rslt_dtl dtl
3276: ,ben_ext_rslt res
3277: ,ben_ext_rcd rcd
3278: ,ben_ext_rcd_in_file rin
3293: ORDER BY res.ext_rslt_id desc;
3294:
3295: CURSOR c_09_poj_abp_data (c_eff_dt IN DATE
3296: ,c_poj_cd IN VARCHAR2) IS
3297: SELECT fnd_number.canonical_to_number(val_06)/100 basis
3298: ,TRUNC(res.eff_dt) eff_dt
3299: , val_05 code
3300: FROM ben_ext_rslt_dtl dtl
3301: ,ben_ext_rslt res
3345: ORDER BY res.ext_rslt_id desc;
3346:
3347: CURSOR c_12_abp_data (c_eff_dt IN DATE
3348: ,c_code IN VARCHAR2) IS
3349: SELECT fnd_number.canonical_to_number(val_06)/100 amount
3350: FROM ben_ext_rslt_dtl dtl
3351: ,ben_ext_rslt res
3352: ,ben_ext_rcd rcd
3353: ,ben_ext_rcd_in_file rin
3372: CURSOR c_12_retro_abp_data (c_year IN VARCHAR2
3373: ,c_mon IN VARCHAR2
3374: ,c_eff_dt IN DATE
3375: ,c_code IN VARCHAR2) IS
3376: SELECT fnd_number.canonical_to_number(val_06)/100 amount
3377: FROM ben_ext_rslt_dtl dtl
3378: ,ben_ext_rslt res
3379: ,ben_ext_rcd rcd
3380: ,ben_ext_rcd_in_file rin
3399: CURSOR c_current_ptp_chgs (c_min_st_dt IN DATE ) IS
3400: SELECT asg.assignment_id
3401: ,effective_start_date start_date
3402: ,effective_end_date end_date
3403: ,least(fnd_number.canonical_to_number(nvl(sck.segment29,100)),125) ptp
3404: FROM per_all_assignments_f asg
3405: ,per_assignment_status_types past
3406: ,hr_soft_coding_keyflex sck
3407: WHERE asg.assignment_id = p_assignment_id
3435: AND asg1.effective_end_date = asg.effective_start_date - 1
3436: AND asg1.assignment_status_type_id = past1.assignment_status_type_id
3437: AND sck1.soft_coding_keyflex_id = asg1.soft_coding_keyflex_id
3438: AND past1.per_system_status = 'ACTIVE_ASSIGN'
3439: AND fnd_number.canonical_to_number(nvl(sck.segment29,'100'))
3440: <> fnd_number.canonical_to_number(nvl(sck1.segment29,'100'))
3441: )
3442: ORDER BY effective_start_date;
3443:
3436: AND asg1.assignment_status_type_id = past1.assignment_status_type_id
3437: AND sck1.soft_coding_keyflex_id = asg1.soft_coding_keyflex_id
3438: AND past1.per_system_status = 'ACTIVE_ASSIGN'
3439: AND fnd_number.canonical_to_number(nvl(sck.segment29,'100'))
3440: <> fnd_number.canonical_to_number(nvl(sck1.segment29,'100'))
3441: )
3442: ORDER BY effective_start_date;
3443:
3444: CURSOR c_get_hire_dt IS
3594:
3595: CURSOR c_ude_rec05_ptp(p_asg_id number, p_start_date date, p_end_date date) IS
3596: SELECT DISTINCT asg.effective_start_date Start_Date
3597: ,asg.effective_end_date End_Date
3598: ,fnd_number.canonical_to_number(NVL(target.SEGMENT29,'100'))*100 ptp
3599: FROM per_assignments_f asg
3600: ,hr_soft_coding_keyflex target
3601: WHERE asg.assignment_id = p_asg_id
3602: AND asg.effective_start_date BETWEEN p_start_date AND nvl(p_end_date,to_date('31-12-4712','dd-mm-rrrr'))
3636:
3637: CURSOR c_ret_rec05_ptp(p_asg_id number, p_start_date date, p_end_date date) IS
3638: SELECT DISTINCT asg.effective_start_date Start_Date
3639: ,asg.effective_end_date End_Date
3640: ,fnd_number.canonical_to_number(NVL(target.SEGMENT29,'100'))*100 ptp
3641: FROM per_assignments_f asg
3642: ,hr_soft_coding_keyflex target
3643: WHERE asg.assignment_id = p_asg_id
3644: AND asg.effective_start_date BETWEEN p_start_date AND nvl(p_end_date,to_date('31-12-4712','dd-mm-rrrr'))
4266: -- Previous Year Retro
4267: --
4268: FOR temp_rec IN c_get_retro_entry
4269: (c_element_type_id =>
4270: fnd_number.canonical_to_number(l_rec_09(i).py_retro_element_id)
4271: ,c_assignment_action_id => act_rec.assignment_action_id)
4272: LOOP
4273: hr_utility.set_location('..Found previous year retro entries',30);
4274: --
4274: --
4275: -- Fetch the input value id for ABP Employee Pension Basis input value
4276: --
4277: OPEN c_get_iv_id(c_element_type_id =>
4278: fnd_number.canonical_to_number(l_rec_09(i).py_retro_element_id));
4279: FETCH c_get_iv_id INTO l_basis_iv_id;
4280: CLOSE c_get_iv_id;
4281:
4282: --
4316: --
4317: -- Current Year Retro
4318: --
4319: FOR temp_rec IN c_get_retro_entry
4320: (c_element_type_id => fnd_number.canonical_to_number(
4321: l_rec_09(i).cy_retro_element_id)
4322: ,c_assignment_action_id => act_rec.assignment_action_id)
4323: LOOP
4324: hr_utility.set_location('...Found current year retro entries',30);
4325: --
4326: -- Fetch the input value id for ABP Employee Pension Basis input value
4327: --
4328: OPEN c_get_iv_id(c_element_type_id =>
4329: fnd_number.canonical_to_number(l_rec_09(i).cy_retro_element_id));
4330: FETCH c_get_iv_id INTO l_basis_iv_id;
4331: CLOSE c_get_iv_id;
4332:
4333: --
4769: --populate the PLSQL table with the retro basis entries
4770: -- and the start and end date of the corresponding retro paid period
4771: --for ABP (record 31)
4772: FOR temp_rec IN c_get_retro_entry
4773: (c_element_type_id => fnd_number.canonical_to_number(
4774: l_rec_31(i).py_retro_element_id)
4775: ,c_assignment_action_id => act_rec.assignment_action_id
4776: )
4777: LOOP
4775: ,c_assignment_action_id => act_rec.assignment_action_id
4776: )
4777: LOOP
4778: --fetch the input value id for ABP Employee Pension Basis input value
4779: OPEN c_get_iv_id(c_element_type_id => fnd_number.canonical_to_number(
4780: l_rec_31(i).py_retro_element_id));
4781: FETCH c_get_iv_id INTO l_basis_iv_id;
4782: CLOSE c_get_iv_id;
4783:
4806:
4807: END LOOP;
4808:
4809: FOR temp_rec IN c_get_retro_entry
4810: (c_element_type_id => fnd_number.canonical_to_number(
4811: l_rec_31(i).cy_retro_element_id)
4812: ,c_assignment_action_id => act_rec.assignment_action_id
4813: )
4814: LOOP
4812: ,c_assignment_action_id => act_rec.assignment_action_id
4813: )
4814: LOOP
4815: --fetch the input value id for ABP Employee Pension Basis input value
4816: OPEN c_get_iv_id(c_element_type_id => fnd_number.canonical_to_number(
4817: l_rec_31(i).cy_retro_element_id));
4818: FETCH c_get_iv_id INTO l_basis_iv_id;
4819: CLOSE c_get_iv_id;
4820:
4884: --populate the PLSQL table with the retro basis entries
4885: -- and the start and end date of the corresponding retro paid period
4886: --for ABP (record 41)
4887: FOR temp_rec IN c_get_retro_entry
4888: (c_element_type_id => fnd_number.canonical_to_number(
4889: l_basis_rec_41(i).py_retro_element_id)
4890: ,c_assignment_action_id => act_rec.assignment_action_id
4891: )
4892: LOOP
4890: ,c_assignment_action_id => act_rec.assignment_action_id
4891: )
4892: LOOP
4893: --fetch the input value id for ABP Employee Pension Basis input value
4894: OPEN c_get_iv_id(c_element_type_id => fnd_number.canonical_to_number(
4895: l_basis_rec_41(i).py_retro_element_id));
4896: FETCH c_get_iv_id INTO l_basis_iv_id;
4897: CLOSE c_get_iv_id;
4898:
4921:
4922: END LOOP;
4923:
4924: FOR temp_rec IN c_get_retro_entry
4925: (c_element_type_id => fnd_number.canonical_to_number(
4926: l_basis_rec_41(i).cy_retro_element_id)
4927: ,c_assignment_action_id => act_rec.assignment_action_id
4928: )
4929: LOOP
4927: ,c_assignment_action_id => act_rec.assignment_action_id
4928: )
4929: LOOP
4930: --fetch the input value id for ABP Employee Pension Basis input value
4931: OPEN c_get_iv_id(c_element_type_id => fnd_number.canonical_to_number(
4932: l_basis_rec_41(i).cy_retro_element_id));
4933: FETCH c_get_iv_id INTO l_basis_iv_id;
4934: CLOSE c_get_iv_id;
4935:
7692: ,p_assignment_action_id => p_asg_act);
7693: Hr_Utility.set_location(' l_balance_amount ' || l_balance_amount , 25);
7694: l_balance_amount := NVL(l_balance_amount,0);
7695: p_data_element_value :=
7696: Fnd_Number.number_to_canonical(l_balance_amount);
7697: l_ret_val := 0;
7698: ELSIF p_asg_act = - 1 THEN
7699:
7700: Hr_Utility.set_location(' l_asg_action_id ' || l_asg_action_id , 20);
7714: ,p_assignment_action_id => l_asg_action_id);
7715: Hr_Utility.set_location(' l_balance_amount ' || l_balance_amount , 25);
7716: l_balance_amount := NVL(l_balance_amount,0);
7717: p_data_element_value :=
7718: Fnd_Number.number_to_canonical(l_balance_amount);
7719: l_ret_val := 0;
7720: END IF;
7721:
7722: END IF;
7757: IF l_rec_12_values.count > 0 THEN
7758: j := l_rec_12_values.FIRST;
7759: IF l_rec_12_values.EXISTS(j) THEN
7760: p_data_element_value
7761: := Fnd_Number.number_to_canonical(l_rec_12_values(j).contrib_amount);
7762: END IF;
7763: END IF;
7764: ELSIF p_record_number = 41 THEN
7765: IF l_rec_41_contrib_values.count > 0 THEN
7765: IF l_rec_41_contrib_values.count > 0 THEN
7766: j := l_rec_41_contrib_values.FIRST;
7767: IF l_rec_41_contrib_values.EXISTS(j) THEN
7768: p_data_element_value
7769: := Fnd_Number.number_to_canonical(l_rec_41_contrib_values(j).contrib_amount);
7770: END IF;
7771: END IF;
7772: END IF;
7773: RETURN 0;
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
7851: AND chg_evt_cd = 'COAPS'
7852: AND ext_chg_evt_log_id =
7853: (SELECT Max(ext_chg_evt_log_id)
7852: AND ext_chg_evt_log_id =
7853: (SELECT Max(ext_chg_evt_log_id)
7854: FROM ben_ext_chg_evt_log
7855: WHERE person_id = g_person_id
7856: AND Fnd_Number.canonical_to_number(prmtr_01) = p_assignment_id
7857: AND chg_eff_dt BETWEEN p_start_date AND p_end_date
7858: AND chg_evt_cd = 'COAPS');
7859:
7860: l_ret_val NUMBER := -1;
8867: --
8868: -- Cursor to check if there are any changes in the ABP Pension Salary
8869: --
8870: CURSOR c_get_override_salary IS
8871: SELECT fnd_number.canonical_to_number(nvl(new_val1,'0'))
8872: FROM ben_ext_chg_evt_log
8873: WHERE person_id = g_person_id
8874: AND fnd_number.canonical_to_number(prmtr_01) = p_assignment_id
8875: AND chg_eff_dt BETWEEN g_extract_params(p_business_group_id).extract_start_date
8870: CURSOR c_get_override_salary IS
8871: SELECT fnd_number.canonical_to_number(nvl(new_val1,'0'))
8872: FROM ben_ext_chg_evt_log
8873: WHERE person_id = g_person_id
8874: AND fnd_number.canonical_to_number(prmtr_01) = p_assignment_id
8875: AND chg_eff_dt BETWEEN g_extract_params(p_business_group_id).extract_start_date
8876: AND g_extract_params(p_business_group_id).extract_end_date
8877: AND chg_evt_cd = 'COAPS'
8878: AND ext_chg_evt_log_id =
8878: AND ext_chg_evt_log_id =
8879: (SELECT MAX(ext_chg_evt_log_id)
8880: FROM ben_ext_chg_evt_log
8881: WHERE person_id = g_person_id
8882: AND fnd_number.canonical_to_number(prmtr_01) = p_assignment_id
8883: AND chg_eff_dt BETWEEN g_extract_params(p_business_group_id).extract_start_date
8884: AND g_extract_params(p_business_group_id).extract_end_date
8885: AND chg_evt_cd = 'COAPS');
8886:
9284: SELECT 1
9285: FROM ben_ext_chg_evt_log bec,pqp_pension_types_f pty
9286: WHERE chg_evt_cd = 'COAPPD'
9287: AND prmtr_01 = 'ASG'
9288: AND Fnd_Number.canonical_to_number(prmtr_02) = pty.pension_type_id
9289: AND pty.pension_sub_category = p_sub_cat
9290: AND prmtr_03 = 'Y'
9291: AND Fnd_Number.canonical_to_number(prmtr_10) = p_assignment_id
9292: AND person_id = (SELECT person_id
9287: AND prmtr_01 = 'ASG'
9288: AND Fnd_Number.canonical_to_number(prmtr_02) = pty.pension_type_id
9289: AND pty.pension_sub_category = p_sub_cat
9290: AND prmtr_03 = 'Y'
9291: AND Fnd_Number.canonical_to_number(prmtr_10) = p_assignment_id
9292: AND person_id = (SELECT person_id
9293: FROM per_all_assignments_f
9294: WHERE assignment_id = p_assignment_id
9295: AND p_effective_date BETWEEN effective_start_date
9302: SELECT 1
9303: FROM ben_ext_chg_evt_log bec,pqp_pension_types_f pty
9304: WHERE chg_evt_cd = 'COAPPD'
9305: AND prmtr_01 = 'ORG'
9306: AND Fnd_Number.canonical_to_number(prmtr_02) = pty.pension_type_id
9307: AND pty.pension_sub_category = p_sub_cat
9308: AND prmtr_03 = 'Y'
9309: AND Fnd_Number.canonical_to_number(prmtr_04) = c_org_id
9310: AND bec.business_group_id = p_business_group_id;
9305: AND prmtr_01 = 'ORG'
9306: AND Fnd_Number.canonical_to_number(prmtr_02) = pty.pension_type_id
9307: AND pty.pension_sub_category = p_sub_cat
9308: AND prmtr_03 = 'Y'
9309: AND Fnd_Number.canonical_to_number(prmtr_04) = c_org_id
9310: AND bec.business_group_id = p_business_group_id;
9311:
9312: --cursor to get the ASG EIT log rows
9313: CURSOR c_get_asg_rows IS
9314: SELECT 1
9315: FROM ben_ext_chg_evt_log bec,pqp_pension_types_f pty
9316: WHERE chg_evt_cd = 'COAPPD'
9317: AND prmtr_01 = 'ASG'
9318: AND Fnd_Number.canonical_to_number(prmtr_02) = pty.pension_type_id
9319: AND prmtr_03 = 'Y'
9320: AND Fnd_Number.canonical_to_number(prmtr_10) = p_assignment_id
9321: AND pty.pension_sub_category = p_sub_cat
9322: AND person_id = (SELECT person_id
9316: WHERE chg_evt_cd = 'COAPPD'
9317: AND prmtr_01 = 'ASG'
9318: AND Fnd_Number.canonical_to_number(prmtr_02) = pty.pension_type_id
9319: AND prmtr_03 = 'Y'
9320: AND Fnd_Number.canonical_to_number(prmtr_10) = p_assignment_id
9321: AND pty.pension_sub_category = p_sub_cat
9322: AND person_id = (SELECT person_id
9323: FROM per_all_assignments_f
9324: WHERE assignment_id = p_assignment_id
9335: SELECT 1
9336: FROM ben_ext_chg_evt_log bec,pqp_pension_types_f pty
9337: WHERE chg_evt_cd = 'COAPPD'
9338: AND prmtr_01 = 'ORG'
9339: AND Fnd_Number.canonical_to_number(prmtr_02) = pty.pension_type_id
9340: AND prmtr_03 = 'Y'
9341: AND pty.pension_sub_category = p_sub_cat
9342: AND Fnd_Number.canonical_to_number(prmtr_04) = c_org_id
9343: AND bec.business_group_id = p_business_group_id
9338: AND prmtr_01 = 'ORG'
9339: AND Fnd_Number.canonical_to_number(prmtr_02) = pty.pension_type_id
9340: AND prmtr_03 = 'Y'
9341: AND pty.pension_sub_category = p_sub_cat
9342: AND Fnd_Number.canonical_to_number(prmtr_04) = c_org_id
9343: AND bec.business_group_id = p_business_group_id
9344: AND chg_eff_dt BETWEEN g_extract_params(p_business_group_id).extract_start_date
9345: AND g_extract_params(p_business_group_id).extract_end_date
9346: AND chg_eff_dt >= c_hire_date
9411: AND paa.assignment_id = p_assignment_id
9412: AND pei.element_type_id = prr.element_type_id
9413: AND pei.information_type = 'PQP_NL_ABP_DEDUCTION'
9414: AND pei.eei_information_category = 'PQP_NL_ABP_DEDUCTION'
9415: AND pei.eei_information2 = Fnd_Number.number_to_canonical(pty.pension_type_id)
9416: AND pty.pension_sub_category = p_sub_cat;
9417:
9418:
9419: l_proc_name Varchar2(150) := 'Record40_30_Disp_Criteria';
9810: SELECT 1
9811: FROM ben_ext_chg_evt_log bec,pqp_pension_types_f pty
9812: WHERE chg_evt_cd = 'COAPPD'
9813: AND prmtr_01 = 'ASG'
9814: AND Fnd_Number.canonical_to_number(prmtr_02) = pty.pension_type_id
9815: AND pty.pension_sub_category = 'FUR_S'
9816: AND prmtr_03 = 'Y'
9817: AND Fnd_Number.canonical_to_number(prmtr_10) = p_assignment_id
9818: AND person_id = (SELECT person_id
9813: AND prmtr_01 = 'ASG'
9814: AND Fnd_Number.canonical_to_number(prmtr_02) = pty.pension_type_id
9815: AND pty.pension_sub_category = 'FUR_S'
9816: AND prmtr_03 = 'Y'
9817: AND Fnd_Number.canonical_to_number(prmtr_10) = p_assignment_id
9818: AND person_id = (SELECT person_id
9819: FROM per_all_assignments_f
9820: WHERE assignment_id = p_assignment_id
9821: AND p_effective_date BETWEEN effective_start_date
9828: SELECT 1
9829: FROM ben_ext_chg_evt_log bec,pqp_pension_types_f pty
9830: WHERE chg_evt_cd = 'COAPPD'
9831: AND prmtr_01 = 'ORG'
9832: AND Fnd_Number.canonical_to_number(prmtr_02) = pty.pension_type_id
9833: AND pty.pension_sub_category = 'FUR_S'
9834: AND prmtr_03 = 'Y'
9835: AND Fnd_Number.canonical_to_number(prmtr_04) = c_org_id
9836: AND bec.business_group_id = p_business_group_id;
9831: AND prmtr_01 = 'ORG'
9832: AND Fnd_Number.canonical_to_number(prmtr_02) = pty.pension_type_id
9833: AND pty.pension_sub_category = 'FUR_S'
9834: AND prmtr_03 = 'Y'
9835: AND Fnd_Number.canonical_to_number(prmtr_04) = c_org_id
9836: AND bec.business_group_id = p_business_group_id;
9837:
9838: --cursor to get the old and new start and end dates from the ASG EIT
9839: CURSOR c_get_asg_rows IS
9840: SELECT old_val1,new_val1,old_val2,new_val2
9841: FROM ben_ext_chg_evt_log bec,pqp_pension_types_f pty
9842: WHERE chg_evt_cd = 'COAPPD'
9843: AND prmtr_01 = 'ASG'
9844: AND Fnd_Number.canonical_to_number(prmtr_02) = pty.pension_type_id
9845: AND prmtr_03 = 'Y'
9846: AND Fnd_Number.canonical_to_number(prmtr_10) = p_assignment_id
9847: AND pty.pension_sub_category = 'FUR_S'
9848: AND person_id = (SELECT person_id
9842: WHERE chg_evt_cd = 'COAPPD'
9843: AND prmtr_01 = 'ASG'
9844: AND Fnd_Number.canonical_to_number(prmtr_02) = pty.pension_type_id
9845: AND prmtr_03 = 'Y'
9846: AND Fnd_Number.canonical_to_number(prmtr_10) = p_assignment_id
9847: AND pty.pension_sub_category = 'FUR_S'
9848: AND person_id = (SELECT person_id
9849: FROM per_all_assignments_f
9850: WHERE assignment_id = p_assignment_id
9861: SELECT old_val1,new_val1,old_val2,new_val2
9862: FROM ben_ext_chg_evt_log bec,pqp_pension_types_f pty
9863: WHERE chg_evt_cd = 'COAPPD'
9864: AND prmtr_01 = 'ORG'
9865: AND Fnd_Number.canonical_to_number(prmtr_02) = pty.pension_type_id
9866: AND prmtr_03 = 'Y'
9867: AND pty.pension_sub_category = 'FUR_S'
9868: AND Fnd_Number.canonical_to_number(prmtr_04) = c_org_id
9869: AND bec.business_group_id = p_business_group_id
9864: AND prmtr_01 = 'ORG'
9865: AND Fnd_Number.canonical_to_number(prmtr_02) = pty.pension_type_id
9866: AND prmtr_03 = 'Y'
9867: AND pty.pension_sub_category = 'FUR_S'
9868: AND Fnd_Number.canonical_to_number(prmtr_04) = c_org_id
9869: AND bec.business_group_id = p_business_group_id
9870: AND chg_eff_dt BETWEEN g_extract_params(p_business_group_id).extract_start_date
9871: AND g_extract_params(p_business_group_id).extract_end_date
9872: AND chg_eff_dt >= c_hire_date
9937: AND paa.assignment_id = p_assignment_id
9938: AND pei.element_type_id = prr.element_type_id
9939: AND pei.information_type = 'PQP_NL_ABP_DEDUCTION'
9940: AND pei.eei_information_category = 'PQP_NL_ABP_DEDUCTION'
9941: AND pei.eei_information2 = Fnd_Number.number_to_canonical(pty.pension_type_id)
9942: AND pty.pension_sub_category = 'FUR_S';
9943:
9944: l_proc_name Varchar2(150) := g_proc_name ||'get_fur_participation_dates';
9945: l_return_value Number := -1;
10245: WHERE chg_evt_cd = 'COAPPD'
10246: AND prmtr_01 = 'ASG'
10247: AND prmtr_04 = 'PPP'
10248: AND prmtr_03 = 'Y'
10249: AND fnd_number.canonical_to_number(prmtr_10) = p_assignment_id
10250: -- AND chg_eff_dt BETWEEN g_extract_params(p_business_group_id).extract_start_date
10251: -- AND g_extract_params(p_business_group_id).extract_end_date
10252: AND bec.business_group_id = p_business_group_id;
10253:
10257: pqp_pension_types_f pty
10258: WHERE paei.information_type = 'NL_ABP_PI'
10259: AND paei.aei_information_category = 'NL_ABP_PI'
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,
10280: AND paa.assignment_id = p_assignment_id
10281: AND pei.element_type_id = prr.element_type_id
10282: AND pei.information_type = 'PQP_NL_ABP_DEDUCTION'
10283: AND pei.eei_information_category = 'PQP_NL_ABP_DEDUCTION'
10284: AND pei.eei_information2 = Fnd_Number.number_to_canonical(pty.pension_type_id)
10285: AND pty.pension_sub_category = 'PPP';*/
10286:
10287: CURSOR c_rr_cur IS
10288: SELECT 1
10296: AND ppf.prl_information_category = 'NL'
10297: AND p_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
10298: AND (paf.organization_id = hoi.organization_id
10299: OR
10300: (fnd_number.canonical_to_number(ppf.prl_information1) = hoi.organization_id
10301: AND NOT EXISTS (SELECT 1
10302: FROM hr_organization_information hoi1
10303: WHERE hoi1.org_information_context = 'PQP_NL_ABP_PT'
10304: AND hoi1.org_information3 = TO_CHAR(pty.pension_type_id)
10423: AND paa.assignment_id = p_assignment_id
10424: AND pei.element_type_id = prr.element_type_id
10425: AND pei.information_type = 'PQP_NL_ABP_DEDUCTION'
10426: AND pei.eei_information_category = 'PQP_NL_ABP_DEDUCTION'
10427: AND pei.eei_information2 = Fnd_Number.number_to_canonical(pty.pension_type_id)
10428: AND pty.pension_sub_category IN ('FPU_B',
10429: 'FPU_E',
10430: 'FPU_R',
10431: 'FPU_S',
10448: AND ppf.prl_information_category = 'NL'
10449: AND p_effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
10450: AND (paf.organization_id = hoi.organization_id
10451: OR
10452: (fnd_number.canonical_to_number(ppf.prl_information1) = hoi.organization_id
10453: AND NOT EXISTS (SELECT 1
10454: FROM hr_organization_information hoi1
10455: WHERE hoi1.org_information_context = 'PQP_NL_ABP_PT'
10456: AND hoi1.org_information3 = TO_CHAR(pty.pension_type_id)
10495: pqp_pension_types_f pty
10496: WHERE paei.information_type = 'NL_ABP_PI'
10497: AND paei.aei_information_category = 'NL_ABP_PI'
10498: AND paei.assignment_id = p_assignment_id
10499: AND fnd_number.canonical_to_number(NVL(aei_information3,-1)) = pty.pension_type_id
10500: AND p_effective_date BETWEEN pty.effective_start_date and pty.effective_end_date
10501: AND pty.pension_sub_category IN ('FPU_B',
10502: 'FPU_E',
10503: 'FPU_R',
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
10622: AND aei_information4 IS NOT NULL
10623: AND paei.information_type = 'NL_ABP_PAR_INFO'
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
10645: AND aei_information4 IS NOT NULL
10646: AND paei.information_type = 'NL_ABP_PAR_INFO'
10650: ben_ext_chg_evt_log blog
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;
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'
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
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
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'
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
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 ;
10796: c_pt_id IN NUMBER) IS
10797: SELECT 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);
10987: -- Cursor to fetch the kind and value of participation from the ASG EIT
10988: --
10989: CURSOR c_get_participation_detl IS
10990: SELECT Nvl(aei_information4,'WNE') kind,
10991: LEAST(Nvl(fnd_number.canonical_to_number(aei_information5),1),1) VALUE
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
11026: AND paa.assignment_id = p_assignment_id
11027: AND pei.element_type_id = prr.element_type_id
11028: AND pei.information_type = 'PQP_NL_ABP_DEDUCTION'
11029: AND pei.eei_information_category = 'PQP_NL_ABP_DEDUCTION'
11030: AND pei.eei_information2 = Fnd_Number.number_to_canonical(pty.pension_type_id)
11031: AND (pty.pension_sub_category LIKE 'FPU%'
11032: OR pty.pension_sub_category LIKE 'OPNP%'
11033: OR pty.pension_sub_category = 'PPP');
11034:
11051:
11052: CURSOR c_hire_ptp_chg (c_asg_id IN NUMBER) IS
11053: SELECT asg.effective_start_date Start_Date
11054: ,asg.effective_end_date End_Date
11055: ,fnd_number.canonical_to_number(NVL(target.SEGMENT29,'100')) ptp
11056: FROM per_assignments_f asg
11057: ,hr_soft_coding_keyflex target
11058: ,per_assignment_status_types past
11059: WHERE target.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
11143: c_pt_id IN NUMBER) IS
11144: SELECT 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
11200: AND rin.seq_num = 5;
11201:
11202: CURSOR c_copos_ptp_chg (c_asg_id IN NUMBER
11203: ,c_eff_date IN DATE ) IS
11204: SELECT fnd_number.canonical_to_number(NVL(target.SEGMENT29,'100')) ptp
11205: FROM per_assignments_f asg
11206: ,hr_soft_coding_keyflex target
11207: ,per_assignment_status_types past
11208: WHERE target.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
11362: l_kind := 'WNE';
11363: l_value := '1';
11364: END IF;
11365:
11366: l_value_num := fnd_number.canonical_to_number(l_value);
11367: l_value_num := l_value_num * 100;
11368: l_value := fnd_number.number_to_canonical(l_value_num);
11369:
11370: Hr_Utility.set_location('....Value of l_value : '||l_value ,14);
11364: END IF;
11365:
11366: l_value_num := fnd_number.canonical_to_number(l_value);
11367: l_value_num := l_value_num * 100;
11368: l_value := fnd_number.number_to_canonical(l_value_num);
11369:
11370: Hr_Utility.set_location('....Value of l_value : '||l_value ,14);
11371: Hr_Utility.set_location('....Value of l_kind : '||l_kind ,16);
11372:
12156: ELSIF p_fetch_code = 'PART_TIME_PERC' THEN
12157: IF g_rec05_rows(g_index_05).part_time_perc IS NULL THEN
12158: p_data_element_value := ' ';
12159: ELSE
12160: p_data_element_value := fnd_number.number_to_canonical
12161: (g_rec05_rows(g_index_05).part_time_perc);
12162: END IF;
12163: END IF;
12164:
12200: SELECT 1
12201: FROM ben_ext_chg_evt_log bec,pqp_pension_types_f pty
12202: WHERE chg_evt_cd = 'COAPPD'
12203: AND prmtr_01 = 'ASG'
12204: AND Fnd_Number.canonical_to_number(prmtr_02) = pty.pension_type_id
12205: AND pty.pension_sub_category = 'IPAP'
12206: AND prmtr_03 = 'Y'
12207: AND Fnd_Number.canonical_to_number(prmtr_10) = p_assignment_id
12208: AND person_id = (SELECT person_id
12203: AND prmtr_01 = 'ASG'
12204: AND Fnd_Number.canonical_to_number(prmtr_02) = pty.pension_type_id
12205: AND pty.pension_sub_category = 'IPAP'
12206: AND prmtr_03 = 'Y'
12207: AND Fnd_Number.canonical_to_number(prmtr_10) = p_assignment_id
12208: AND person_id = (SELECT person_id
12209: FROM per_all_assignments_f
12210: WHERE assignment_id = p_assignment_id
12211: AND p_effective_date BETWEEN effective_start_date
12218: SELECT 1
12219: FROM ben_ext_chg_evt_log bec,pqp_pension_types_f pty
12220: WHERE chg_evt_cd = 'COAPPD'
12221: AND prmtr_01 = 'ORG'
12222: AND Fnd_Number.canonical_to_number(prmtr_02) = pty.pension_type_id
12223: AND pty.pension_sub_category = 'IPAP'
12224: AND prmtr_03 = 'Y'
12225: AND Fnd_Number.canonical_to_number(prmtr_04) = c_org_id
12226: AND bec.business_group_id = p_business_group_id;
12221: AND prmtr_01 = 'ORG'
12222: AND Fnd_Number.canonical_to_number(prmtr_02) = pty.pension_type_id
12223: AND pty.pension_sub_category = 'IPAP'
12224: AND prmtr_03 = 'Y'
12225: AND Fnd_Number.canonical_to_number(prmtr_04) = c_org_id
12226: AND bec.business_group_id = p_business_group_id;
12227:
12228: --cursor to get the old and new start and end dates from the ASG EIT
12229: CURSOR c_get_asg_rows IS
12230: SELECT old_val1,new_val1,old_val2,new_val2
12231: FROM ben_ext_chg_evt_log bec,pqp_pension_types_f pty
12232: WHERE chg_evt_cd = 'COAPPD'
12233: AND prmtr_01 = 'ASG'
12234: AND Fnd_Number.canonical_to_number(prmtr_02) = pty.pension_type_id
12235: AND prmtr_03 = 'Y'
12236: AND Fnd_Number.canonical_to_number(prmtr_10) = p_assignment_id
12237: AND pty.pension_sub_category = 'IPAP'
12238: AND person_id = (SELECT person_id
12232: WHERE chg_evt_cd = 'COAPPD'
12233: AND prmtr_01 = 'ASG'
12234: AND Fnd_Number.canonical_to_number(prmtr_02) = pty.pension_type_id
12235: AND prmtr_03 = 'Y'
12236: AND Fnd_Number.canonical_to_number(prmtr_10) = p_assignment_id
12237: AND pty.pension_sub_category = 'IPAP'
12238: AND person_id = (SELECT person_id
12239: FROM per_all_assignments_f
12240: WHERE assignment_id = p_assignment_id
12251: SELECT old_val1,new_val1,old_val2,new_val2
12252: FROM ben_ext_chg_evt_log bec,pqp_pension_types_f pty
12253: WHERE chg_evt_cd = 'COAPPD'
12254: AND prmtr_01 = 'ORG'
12255: AND Fnd_Number.canonical_to_number(prmtr_02) = pty.pension_type_id
12256: AND prmtr_03 = 'Y'
12257: AND pty.pension_sub_category = 'IPAP'
12258: AND Fnd_Number.canonical_to_number(prmtr_04) = c_org_id
12259: AND bec.business_group_id = p_business_group_id
12254: AND prmtr_01 = 'ORG'
12255: AND Fnd_Number.canonical_to_number(prmtr_02) = pty.pension_type_id
12256: AND prmtr_03 = 'Y'
12257: AND pty.pension_sub_category = 'IPAP'
12258: AND Fnd_Number.canonical_to_number(prmtr_04) = c_org_id
12259: AND bec.business_group_id = p_business_group_id
12260: AND chg_eff_dt BETWEEN g_extract_params(p_business_group_id).extract_start_date
12261: AND g_extract_params(p_business_group_id).extract_end_date
12262: AND chg_eff_dt >= c_hire_date
12327: AND paa.assignment_id = p_assignment_id
12328: AND pei.element_type_id = prr.element_type_id
12329: AND pei.information_type = 'PQP_NL_ABP_DEDUCTION'
12330: AND pei.eei_information_category = 'PQP_NL_ABP_DEDUCTION'
12331: AND pei.eei_information2 = Fnd_Number.number_to_canonical(pty.pension_type_id)
12332: AND pty.pension_sub_category = 'IPAP';
12333:
12334: l_proc_name Varchar2(150) := g_proc_name ||'get_ipap_participation_dates';
12335: l_return_value Number := -1;
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'))
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
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
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
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
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';
12968: ,p_error_message
12969: ,l_pension_sal_char);
12970:
12971: IF IsNumber(l_pension_sal_char) THEN
12972: l_pension_sal_char := Trim(To_Char(ABS(Fnd_Number.Canonical_To_Number
12973: (Nvl(l_pension_sal_char,'0')))
12974: ,'0999999V99'));
12975: END IF;
12976:
13734: (SELECT element_type_id
13735: FROM pay_element_type_extra_info,pqp_pension_types_f pty
13736: WHERE information_type = 'PQP_NL_ABP_DEDUCTION'
13737: AND eei_information_category = 'PQP_NL_ABP_DEDUCTION'
13738: AND eei_information2 = Fnd_Number.number_to_canonical(pty.pension_type_id)
13739: AND pty.pension_sub_category = 'FUR_S'
13740: )
13741: );
13742:
13766: --from the dimension _ASG_PTD
13767: CLOSE c_run_results_exist;
13768: Hr_Utility.set_location('found asg action id : '||l_asg_act_id,30);
13769: --from the assignment action id,and defined balance id fetched above , find the balance value
13770: p_data_element_value := Fnd_Number.number_to_canonical(
13771: Pay_Balance_Pkg.get_value(p_defined_balance_id => l_defined_balance_id
13772: ,p_assignment_action_id => l_asg_act_id
13773: ));
13774: Hr_Utility.set_location('found value for the contribution as : '||p_data_element_value,40);
13771: Pay_Balance_Pkg.get_value(p_defined_balance_id => l_defined_balance_id
13772: ,p_assignment_action_id => l_asg_act_id
13773: ));
13774: Hr_Utility.set_location('found value for the contribution as : '||p_data_element_value,40);
13775: g_fur_contribution := Fnd_Number.canonical_to_number(p_data_element_value);
13776: RETURN 0;
13777: ELSE
13778: CLOSE c_run_results_exist;
13779: Hr_Utility.set_location('could not find the asg action id',50);
13825: AND paa.assignment_id = p_assignment_id
13826: AND pei.element_type_id = prr.element_type_id
13827: AND pei.information_type = 'PQP_NL_ABP_DEDUCTION'
13828: AND pei.eei_information_category = 'PQP_NL_ABP_DEDUCTION'
13829: AND pei.eei_information2 = Fnd_Number.number_to_canonical(pty.pension_type_id)
13830: AND pty.pension_sub_category = p_sub_cat;
13831:
13832: l_asg_act_id Number;
13833: l_scheme_prefix Varchar2(150);
13854: IF csr_defined_bal%FOUND THEN
13855: CLOSE csr_defined_bal;
13856: Hr_Utility.set_location('found defined balance id : '||l_defined_balance_id,20);
13857: --from the assignment action id,and defined balance id fetched above , find the balance value
13858: p_data_element_value := Fnd_Number.number_to_canonical(
13859: Pay_Balance_Pkg.get_value(p_defined_balance_id => l_defined_balance_id
13860: ,p_assignment_action_id => l_asg_act_id
13861: ));
13862: Hr_Utility.set_location('found value for the contribution as : '||p_data_element_value,40);
13860: ,p_assignment_action_id => l_asg_act_id
13861: ));
13862: Hr_Utility.set_location('found value for the contribution as : '||p_data_element_value,40);
13863: IF p_sub_cat = 'FUR_S' THEN
13864: g_fur_contribution := Fnd_Number.canonical_to_number(p_data_element_value);
13865: RETURN 0;
13866: ELSIF p_sub_cat = 'IPAP' THEN
13867: g_ipap_contribution := Fnd_Number.canonical_to_number(p_data_element_value);
13868: RETURN 0;
13863: IF p_sub_cat = 'FUR_S' THEN
13864: g_fur_contribution := Fnd_Number.canonical_to_number(p_data_element_value);
13865: RETURN 0;
13866: ELSIF p_sub_cat = 'IPAP' THEN
13867: g_ipap_contribution := Fnd_Number.canonical_to_number(p_data_element_value);
13868: RETURN 0;
13869: END IF;
13870: ELSE
13871: CLOSE csr_defined_bal;
13913: IF l_rec_09_values.count > 0 THEN
13914: j := l_rec_09_values.FIRST;
13915: IF l_rec_09_values.EXISTS(j) THEN
13916: p_data_element_value :=
13917: Fnd_Number.number_to_canonical(l_rec_09_values(j).basis_amount);
13918: END IF;
13919: END IF;
13920: ELSIF p_record_number = 31 THEN
13921: IF l_rec_31_values.count > 0 THEN
13921: IF l_rec_31_values.count > 0 THEN
13922: j := l_rec_31_values.FIRST;
13923: IF l_rec_31_values.EXISTS(j) THEN
13924: p_data_element_value :=
13925: Fnd_Number.number_to_canonical(l_rec_31_values(j).basis_amount);
13926: END IF;
13927: END IF;
13928: ELSIF p_record_number = 41 THEN
13929: IF l_rec_41_basis_values.count > 0 THEN
13929: IF l_rec_41_basis_values.count > 0 THEN
13930: j := l_rec_41_basis_values.FIRST;
13931: IF l_rec_41_basis_values.EXISTS(j) THEN
13932: p_data_element_value :=
13933: Fnd_Number.number_to_canonical(l_rec_41_basis_values(j).basis_amount);
13934: END IF;
13935: END IF;
13936: END IF;
13937:
14117: (SELECT element_type_id
14118: FROM pay_element_type_extra_info,pqp_pension_types_f pty
14119: WHERE information_type = 'PQP_NL_ABP_DEDUCTION'
14120: AND eei_information_category = 'PQP_NL_ABP_DEDUCTION'
14121: AND eei_information2 = Fnd_Number.number_to_canonical(pty.pension_type_id)
14122: AND pty.pension_sub_category = 'IPAP'
14123: )
14124: );
14125:
14148: --from the dimension _ASG_PTD
14149: CLOSE c_run_results_exist;
14150: Hr_Utility.set_location('found asg action id : '||l_asg_act_id,30);
14151: --from the assignment action id,and defined balance id fetched above , find the balance value
14152: p_data_element_value := Fnd_Number.number_to_canonical(
14153: Pay_Balance_Pkg.get_value(p_defined_balance_id => l_defined_balance_id
14154: ,p_assignment_action_id => l_asg_act_id
14155: ));
14156: Hr_Utility.set_location('found value for the contribution as : '||p_data_element_value,40);
14153: Pay_Balance_Pkg.get_value(p_defined_balance_id => l_defined_balance_id
14154: ,p_assignment_action_id => l_asg_act_id
14155: ));
14156: Hr_Utility.set_location('found value for the contribution as : '||p_data_element_value,40);
14157: g_ipap_contribution := Fnd_Number.canonical_to_number(p_data_element_value);
14158: RETURN 0;
14159: ELSE
14160: CLOSE c_run_results_exist;
14161: Hr_Utility.set_location('could not find the asg action id',50);
15265: ,'NEW'
15266: ,p_error_message
15267: ,p_data_element_value);
15268: IF IsNumber(p_data_element_value) THEN
15269: p_data_element_value := Trim(To_Char(Fnd_Number.Canonical_To_Number
15270: (Nvl(p_data_element_value,'0'))
15271: ,'0999999'));
15272: END IF;
15273:
15298: ,p_data_element_value);
15299:
15300: IF p_data_element_value IS NOT NULL THEN
15301: IF IsNumber(p_data_element_value) THEN
15302: p_data_element_value := Trim(To_Char(Fnd_Number.Canonical_To_Number
15303: (p_data_element_value)
15304: ,'0999999'));
15305: END IF;
15306: ELSE
15573: ,p_error_message
15574: ,p_data_element_value);
15575:
15576: IF IsNumber(p_data_element_value) THEN
15577: p_data_element_value := Trim(To_Char(ABS(Fnd_Number.Canonical_To_Number
15578: (Nvl(p_data_element_value,'0')))
15579: ,'0999999V99'));
15580: END IF;
15581:
15656: ,p_error_message
15657: ,p_data_element_value);
15658:
15659: IF IsNumber(p_data_element_value) THEN
15660: p_data_element_value := Trim(To_Char(ABS(Fnd_Number.Canonical_To_Number
15661: (Nvl(p_data_element_value,'0')))
15662: ,'0999999V99'));
15663: END IF;
15664:
15739: ,p_error_message
15740: ,p_data_element_value);
15741:
15742: IF IsNumber(p_data_element_value) THEN
15743: p_data_element_value :=Trim( To_Char(ABS(Fnd_Number.Canonical_To_Number
15744: (Nvl(p_data_element_value,'0')))
15745: ,'0999999V99'));
15746: END IF;
15747:
15915: ,p_error_message
15916: ,p_data_element_value);
15917:
15918: IF IsNumber(p_data_element_value) THEN
15919: p_data_element_value := Trim(To_Char(ABS(Fnd_Number.Canonical_To_Number
15920: (Nvl(p_data_element_value,'0')))
15921: ,'0999999V99'));
15922: END IF;
15923:
15932: ,p_error_message
15933: ,p_data_element_value);
15934:
15935: IF IsNumber(p_data_element_value) THEN
15936: p_data_element_value := Trim(To_Char(ABS(Fnd_Number.Canonical_To_Number
15937: (Nvl(p_data_element_value,'0')))
15938: ,'0999999V99'));
15939: END IF;
15940:
15947: ,p_error_message
15948: ,p_data_element_value);
15949:
15950: IF IsNumber(p_data_element_value) THEN
15951: p_data_element_value :=Trim( To_Char(ABS(Fnd_Number.Canonical_To_Number
15952: (Nvl(p_data_element_value,'0')))
15953: ,'0999999V99'));
15954: END IF;
15955:
16290: ,p_error_message
16291: ,p_data_element_value);
16292:
16293: IF IsNumber(p_data_element_value) THEN
16294: p_data_element_value := Trim(To_Char(ABS(Fnd_Number.Canonical_To_Number
16295: (p_data_element_value))
16296: ,'09999'));
16297: END IF;
16298:
16410: ,p_error_message
16411: ,p_data_element_value);
16412:
16413: IF IsNumber(p_data_element_value) THEN
16414: p_data_element_value := Trim(To_Char(ABS(Fnd_Number.Canonical_To_Number
16415: (Nvl(p_data_element_value,'0')))
16416: ,'099'));
16417: END IF;
16418:
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);
16551: l_return_value := l_new_er_num;
16552: IF IsNumber(l_return_value) THEN
16553: l_return_value := Trim(To_Char(Fnd_Number.Canonical_To_Number
16554: (Nvl(l_return_value,'0'))
16555: ,'0999'));
16556: END IF;
16557:
16986: --Updating the employer name and employer number
16987: l_header_er_num :=Get_Header_Submit_Code(g_conc_prog_details(0).orgid);
16988:
16989: IF IsNumber(l_header_er_num) THEN
16990: l_header_er_num := Trim(To_Char(Fnd_Number.Canonical_To_Number
16991: (Nvl(l_header_er_num,'0'))
16992: ,'0999'));
16993:
16994: END IF;
17380:
17381: l_trailer_er_num :=Get_Header_EMR_Number(g_employer_list(l_employer_index).gre_org_id,g_conc_prog_details(0).endingdt);
17382:
17383: IF IsNumber(l_trailer_er_num) THEN
17384: l_trailer_er_num := Trim(To_Char(Fnd_Number.Canonical_To_Number
17385: (Nvl(l_trailer_er_num,'0'))
17386: ,'0999999'));
17387: END IF;
17388: