[Home] [Help]
77: Where assignment_id = p_asg_id
78: and p_effective_date between effective_start_date and effective_end_date;
79: begin
80: --
81: hr_utility.set_location ('Entering '||l_proc,10);
82: --
83: ff_exec.init_formula
84: (p_formula_id => p_formula_id,
85: p_effective_date => p_effective_date,
85: p_effective_date => p_effective_date,
86: p_inputs => l_inputs,
87: p_outputs => l_outputs);
88: --
89: hr_utility.set_location ('After Init Formula '||l_proc,10);
90: --
91: l_param_tab_count := p_param_tab.count;
92: --
93: -- Account for case where formula has no contexts or inputs
93: -- Account for case where formula has no contexts or inputs
94: --
95: for l_count in nvl(l_inputs.first,0)..nvl(l_inputs.last,-1) loop
96: --
97: hr_utility.set_location ('Current Context'||l_inputs(l_count).name,10);
98: --
99: if l_inputs(l_count).name = 'BUSINESS_GROUP_ID' then
100: --
101: l_inputs(l_count).value := nvl(g_business_group_id, -1);
123: --
124: -- Note that you must pass the date as a string, that is because
125: -- of the canonical date change of 11.5
126: --
127: -- hr_utility.set_location ('Date Earned '||to_char(p_effective_date),10);
128: -- Still the fast formula does't accept the full canonical form.
129: -- l_inputs(l_count).value := fnd_date.date_to_canonical(p_effective_date);
130: l_inputs(l_count).value := to_char(p_effective_date, 'YYYY/MM/DD');
131: --
185: ff_exec.run_formula(p_inputs => l_inputs,
186: p_outputs => l_outputs,
187: p_use_dbi_cache => false); -- bug# 2430017
188: --
189: hr_utility.set_location ('Leaving '||l_proc,10);
190: --
191: return l_outputs;
192: --
193: end exec_pref_rate_formula;
238: end;
239: --
240: Begin
241: --
242: hr_utility.set_location('Entering:'||l_proc, 5);
243: --
244: l_rt_counter := p_eligible_rates.count;
245: hr_utility.set_location('Total Eligible Rates:'||to_char(l_rt_counter), 6);
246: --
241: --
242: hr_utility.set_location('Entering:'||l_proc, 5);
243: --
244: l_rt_counter := p_eligible_rates.count;
245: hr_utility.set_location('Total Eligible Rates:'||to_char(l_rt_counter), 6);
246: --
247: p_rate_factor_cnt := 0;
248: --
249: If p_preferential_rate_cd = 'HIGHEST' then
270: p_rate_factors(1).rate_matrix_rate_id := l_rate_matrix_rate_id;
271: p_rate_factors(1).default_rate := l_rate;
272: p_rate_factor_cnt := 1;
273: --
274: hr_utility.set_location('Highest Rate:'||to_char(l_rate), 6);
275: Elsif p_preferential_rate_cd = 'LOWEST' then
276: l_rate := p_eligible_rates(1).rate_value;
277: p_preferential_rt := p_eligible_rates(1).rate_value;
278: p_preferential_min_rt := p_eligible_rates(1).min_rate_value;
296: p_rate_factors(1).rate_matrix_rate_id := l_rate_matrix_rate_id;
297: p_rate_factors(1).default_rate := l_rate;
298: p_rate_factor_cnt := 1;
299: --
300: hr_utility.set_location('Lowest Rate:'||to_char(l_rate), 7);
301: Elsif p_preferential_rate_cd = 'AVERAGE' then
302: For rec_no in 1..l_rt_counter loop
303: p_preferential_rt := p_preferential_rt + p_eligible_rates(rec_no).rate_value;
304: p_preferential_min_rt := p_preferential_min_rt + p_eligible_rates(rec_no).min_rate_value;
317: l_rate := p_preferential_rt;
318: --
319: p_rate_factor_cnt := l_rt_counter;
320: --
321: hr_utility.set_location('Average Rate:'||to_char(l_rate), 8);
322: Elsif p_preferential_rate_cd = 'RULE' then
323: hr_utility.set_location('Calling fast formula', 9);
324: --
325: -- Setting fast formula inputs. We are reading only the first 5 eligible rates.
319: p_rate_factor_cnt := l_rt_counter;
320: --
321: hr_utility.set_location('Average Rate:'||to_char(l_rate), 8);
322: Elsif p_preferential_rate_cd = 'RULE' then
323: hr_utility.set_location('Calling fast formula', 9);
324: --
325: -- Setting fast formula inputs. We are reading only the first 5 eligible rates.
326: --
327: l_rl_rt_counter := l_rt_counter;
339: End loop;
340: --
341: /**
342: For rec_no in 1..l_param_tab.count loop
343: hr_utility.set_location('Name = '|| l_param_tab(rec_no).name,280);
344: hr_utility.set_location('Value = '|| l_param_tab(rec_no).value,280);
345: end loop;
346: **/
347: --
340: --
341: /**
342: For rec_no in 1..l_param_tab.count loop
343: hr_utility.set_location('Name = '|| l_param_tab(rec_no).name,280);
344: hr_utility.set_location('Value = '|| l_param_tab(rec_no).value,280);
345: end loop;
346: **/
347: --
348: l_outputs := exec_pref_rate_formula
355: p_param_tab => l_param_tab );
356:
357: for l_count in nvl(l_outputs.first,0)..nvl(l_outputs.last,-1) loop
358: --
359: hr_utility.set_location ('Current Context'||l_outputs(l_count).name,10);
360: --
361: if l_outputs(l_count).name = 'MIN' then
362: --
363: p_preferential_min_rt := l_outputs(l_count).value;
374: End if;
375: End loop;
376: --
377: Else
378: hr_utility.set_location('Invalid Preferential Rate Code:', 10);
379: p_preferential_rt := 0;
380: p_preferential_min_rt := 0;
381: p_preferential_mid_rt := 0;
382: p_preferential_max_rt := 0;
381: p_preferential_mid_rt := 0;
382: p_preferential_max_rt := 0;
383: End if;
384: --
385: hr_utility.set_location('Leaving:'||l_proc, 20);
386: --
387: End;
388: -------------------------< calculate_preferential_rate >----------------------------------
389: --
416: Where criteria_rate_defn_id = p_crit_rt_defn_id;
417: --
418: Begin
419: --
420: hr_utility.set_location('Entering:'||l_proc, 5);
421: --
422: l_rt_counter := p_eligible_rates.count;
423: hr_utility.set_location('Total Eligible Rates:'||to_char(l_rt_counter), 6);
424: --
419: --
420: hr_utility.set_location('Entering:'||l_proc, 5);
421: --
422: l_rt_counter := p_eligible_rates.count;
423: hr_utility.set_location('Total Eligible Rates:'||to_char(l_rt_counter), 6);
424: --
425: If l_rt_counter = 0 then
426: -- No matching rates found.
427: p_preferential_rt := 0;
449: If p_eligible_rates(rec_no).level_number > l_highest_level then
450: l_highest_level := p_eligible_rates(rec_no).level_number;
451: End if;
452: End loop;
453: hr_utility.set_location('Highest level number:'||to_char(l_highest_level), 6);
454: --
455: -- 2) Copy rates at the highest level into temporary table.
456: --
457: l_cnt := 0;
465: l_temp_rate_tab(l_cnt).rate_value := p_eligible_rates(rec_no).rate_value;
466: l_temp_rate_tab(l_cnt).level_number := p_eligible_rates(rec_no).level_number;
467: End if;
468: End loop;
469: hr_utility.set_location('Rates at highest level number:'||to_char(l_cnt), 6);
470: --
471: If l_cnt = 1 then
472: -- Only one eligible rate found at the highest level.
473: p_preferential_rt := l_temp_rate_tab(l_cnt).rate_value;
504: End if;
505: --
506: End if;
507: --
508: hr_utility.set_location('Eligible Rate:'||to_char(p_preferential_rt), 6);
509: hr_utility.set_location('Leaving:'||l_proc, 10);
510: --
511: End;
512: --
505: --
506: End if;
507: --
508: hr_utility.set_location('Eligible Rate:'||to_char(p_preferential_rt), 6);
509: hr_utility.set_location('Leaving:'||l_proc, 10);
510: --
511: End;
512: --
513: -------------------------< determine_crit_rate_defn_rt >----------------------------------
543: l_proc varchar2(72) := g_package||'determine_crit_rate_defn_rt';
544: --
545: Begin
546: --
547: hr_utility.set_location('Entering:'||l_proc, 5);
548: --
549: -- Find the eligible rates for the current rate type.
550: --
551: ben_env_object.init
585: p_min_rate := l_min_rate;
586: p_mid_rate := l_mid_rate;
587: p_max_rate := l_max_rate;
588: --
589: hr_utility.set_location('Leaving:'||l_proc, 10);
590: --
591: End;
592: --
593: --
640: Where assignment_id = p_asg_id
641: and p_effective_date between effective_start_date and effective_end_date;
642: begin
643: --
644: hr_utility.set_location ('Entering '||l_proc,10);
645: --
646: ff_exec.init_formula
647: (p_formula_id => p_formula_id,
648: p_effective_date => p_effective_date,
648: p_effective_date => p_effective_date,
649: p_inputs => l_inputs,
650: p_outputs => l_outputs);
651: --
652: hr_utility.set_location ('After Init Formula '||l_proc,10);
653: --
654: l_param_tab_count := p_param_tab.count;
655: --
656: -- Account for case where formula has no contexts or inputs
656: -- Account for case where formula has no contexts or inputs
657: --
658: for l_count in nvl(l_inputs.first,0)..nvl(l_inputs.last,-1) loop
659: --
660: hr_utility.set_location ('Current Context'||l_inputs(l_count).name,10);
661: --
662: if l_inputs(l_count).name = 'BUSINESS_GROUP_ID' then
663: --
664: l_inputs(l_count).value := nvl(g_business_group_id, -1);
686: --
687: -- Note that you must pass the date as a string, that is because
688: -- of the canonical date change of 11.5
689: --
690: -- hr_utility.set_location ('Date Earned '||to_char(p_effective_date),10);
691: -- Still the fast formula does't accept the full canonical form.
692: -- l_inputs(l_count).value := fnd_date.date_to_canonical(p_effective_date);
693: l_inputs(l_count).value := to_char(p_effective_date, 'YYYY/MM/DD');
694: --
748: ff_exec.run_formula(p_inputs => l_inputs,
749: p_outputs => l_outputs,
750: p_use_dbi_cache => false); -- bug# 2430017
751: --
752: hr_utility.set_location ('Leaving '||l_proc,10);
753: --
754: return l_outputs;
755: --
756: end exec_rate_calc_formula;
833: l_outputs ff_exec.outputs_t;
834: --
835: Begin
836: --
837: hr_utility.set_location('Entering:'||l_proc, 5);
838: --
839: -- Get the calculation method for the rate type. For Norway, the calc method is flat amt.
840: --
841: Open csr_crd_details(p_crit_rt_defn_id);
936: l_freq_conv := l_rt_freq_ann/l_ref_freq_ann;
937: else
938: l_freq_conv := 1;
939: end if;
940: hr_utility.set_location('freq conv fctr is '||l_freq_conv,46);
941: if l_t_currency_code <> l_currency_code then
942: -- get the conv factor between currencies from gl_daily_rates
943: begin
944: select conversion_rate
952: and to_currency = l_currency_code
953: and conversion_date <= p_effective_date);
954: exception
955: when no_data_found then
956: hr_utility.set_location('rates not exist',25);
957: l_curr_conv := 1;
958: when others then
959: hr_utility.set_location('daily rates pull error',25);
960: raise;
955: when no_data_found then
956: hr_utility.set_location('rates not exist',25);
957: l_curr_conv := 1;
958: when others then
959: hr_utility.set_location('daily rates pull error',25);
960: raise;
961: end;
962: else
963: l_curr_conv := 1;
961: end;
962: else
963: l_curr_conv := 1;
964: end if;
965: hr_utility.set_location('curr conv factr is'||l_curr_conv,28);
966: l_t_min_rate := (l_freq_conv * nvl(l_t_min_rate,0)*l_curr_conv);
967: l_t_mid_rate := (l_freq_conv * nvl(l_t_mid_rate,0)*l_curr_conv);
968: l_t_max_rate := (l_freq_conv * nvl(l_t_max_rate,0)*l_curr_conv);
969: l_t_dflt_rate := (l_freq_conv * nvl(l_t_dflt_rate,0)*l_curr_conv);
968: l_t_max_rate := (l_freq_conv * nvl(l_t_max_rate,0)*l_curr_conv);
969: l_t_dflt_rate := (l_freq_conv * nvl(l_t_dflt_rate,0)*l_curr_conv);
970: --
971: Else
972: hr_utility.set_location('This should not be happening', 5);
973: End if;
974: --
975: l_parent_rates(l_cnt).min_rate_value := l_t_min_rate;
976: l_parent_rates(l_cnt).mid_rate_value := l_t_mid_rate;
981: End if;
982: --
983: -- Apply Calculation method and find the final rate for the given rate type.
984: --
985: hr_utility.set_location('No ofParent Rates:'||to_char(l_cnt), 10);
986: If l_rate_calc_cd = 'PERCENT' then
987: If l_cnt > 0 then
988: hr_utility.set_location('Percent', 10);
989: l_dflt_rate := (l_dflt_rate/100) * l_parent_rates(1).rate_value;
984: --
985: hr_utility.set_location('No ofParent Rates:'||to_char(l_cnt), 10);
986: If l_rate_calc_cd = 'PERCENT' then
987: If l_cnt > 0 then
988: hr_utility.set_location('Percent', 10);
989: l_dflt_rate := (l_dflt_rate/100) * l_parent_rates(1).rate_value;
990: l_min_rate := (l_min_rate/100) * l_parent_rates(1).min_rate_value;
991: l_mid_rate := (l_mid_rate/100) * l_parent_rates(1).mid_rate_value;
992: l_max_rate := (l_max_rate/100) * l_parent_rates(1).max_rate_value;
992: l_max_rate := (l_max_rate/100) * l_parent_rates(1).max_rate_value;
993: End if;
994: Elsif l_rate_calc_cd = 'ADD_TO' OR l_rate_calc_cd = 'SUM' then
995: If l_cnt > 0 then
996: hr_utility.set_location('Sum or Add_To', 10);
997: For l_t_cnt in 1..l_cnt loop
998: l_min_rate := l_min_rate + l_parent_rates(l_t_cnt).min_rate_value ;
999: l_mid_rate := l_mid_rate + l_parent_rates(l_t_cnt).mid_rate_value ;
1000: l_max_rate := l_max_rate + l_parent_rates(l_t_cnt).max_rate_value ;
1002: End loop;
1003: End if;
1004: Elsif l_rate_calc_cd = 'RULE' then
1005: -- Use Calculation Rule.
1006: hr_utility.set_location('Rule', 10);
1007: l_outputs := exec_rate_calc_formula
1008: (p_formula_id => l_rate_calc_rule,
1009: p_effective_date => p_effective_date,
1010: p_param1 => 'PQH_RBC_PERSON_ID',
1013: p_param2_value => to_char(nvl(p_crit_rt_defn_id, -1) ));
1014:
1015: for l_count in nvl(l_outputs.first,0)..nvl(l_outputs.last,-1) loop
1016: --
1017: hr_utility.set_location ('Current Context'||l_outputs(l_count).name,10);
1018: --
1019: if l_outputs(l_count).name = 'MIN' then
1020: --
1021: l_min_rate := l_outputs(l_count).value;
1033: --
1034: End loop;
1035: --
1036: Elsif l_rate_calc_cd = 'AMOUNT' then
1037: hr_utility.set_location('Amount', 10);
1038: null;
1039: Else
1040: hr_utility.set_message(8302,'PQH_RBC_INVALID_RT_CALC_METHOD');
1041: hr_utility.raise_error;
1036: Elsif l_rate_calc_cd = 'AMOUNT' then
1037: hr_utility.set_location('Amount', 10);
1038: null;
1039: Else
1040: hr_utility.set_message(8302,'PQH_RBC_INVALID_RT_CALC_METHOD');
1041: hr_utility.raise_error;
1042: End if;
1043: --
1044: -- Apply Rounding.
1037: hr_utility.set_location('Amount', 10);
1038: null;
1039: Else
1040: hr_utility.set_message(8302,'PQH_RBC_INVALID_RT_CALC_METHOD');
1041: hr_utility.raise_error;
1042: End if;
1043: --
1044: -- Apply Rounding.
1045: --
1044: -- Apply Rounding.
1045: --
1046: If l_rounding_cd <> 'NONE' then
1047: --
1048: hr_utility.set_location('Perform Rounding', 10);
1049: --
1050: p_min_rate := benutils.do_rounding
1051: (p_rounding_cd => l_rounding_cd,
1052: p_rounding_rl => l_rounding_rule ,
1076: p_effective_date => p_effective_date);
1077: --
1078: Else
1079: --
1080: hr_utility.set_location('No Rounding', 10);
1081: --
1082: p_min_rate := l_min_rate;
1083: p_mid_rate := l_mid_rate;
1084: p_max_rate := l_max_rate;
1084: p_max_rate := l_max_rate;
1085: p_rate := l_dflt_rate;
1086: End if;
1087: --
1088: hr_utility.set_location('Leaving:'||l_proc, 10);
1089: --
1090: End;
1091: --
1092: ----------------------------------------------------------------------------------------------------
1101: cnt number(15) := 0;
1102: --
1103: Begin
1104: --
1105: hr_utility.set_location('Entering:'||l_proc, 5);
1106: --
1107: -- If the override list is already populated , populate the global override table for BEN to read.
1108: --
1109:
1130:
1131: End loop;
1132: --
1133: --
1134: hr_utility.set_location('Leaving:'||l_proc, 10);
1135: --
1136: End;
1137: ----------------------------------------------------------------------------------------------------
1138: --
1179: Where cost_allocation_keyflex_id = p_cost_allocation_keyflex_id;
1180: --
1181: Begin
1182: --
1183: hr_utility.set_location('Entering:'||l_proc, 5);
1184: --
1185: -- Check if there are any criteria in business group that has timecard override info specified.
1186: -- and fetch its details
1187: --
1195: l_crit_dtls_tbl(l_crit_cnt).crit_col2_datatype := csr_tc_rec.crit_col2_datatype;
1196: l_crit_dtls_tbl(l_crit_cnt).time_entry_access_table_name2 := csr_tc_rec.time_entry_access_table_name2;
1197: l_crit_dtls_tbl(l_crit_cnt).time_entry_access_col_name2 := csr_tc_rec.time_entry_access_col_name2;
1198: --
1199: hr_utility.set_location('time_entry_access_col_name1:'||l_crit_dtls_tbl(l_crit_cnt).time_entry_access_col_name1, 20);
1200: hr_utility.set_location('time_entry_access_col_name2:'||l_crit_dtls_tbl(l_crit_cnt).time_entry_access_col_name2, 20);
1201: --
1202: End loop;
1203: --
1196: l_crit_dtls_tbl(l_crit_cnt).time_entry_access_table_name2 := csr_tc_rec.time_entry_access_table_name2;
1197: l_crit_dtls_tbl(l_crit_cnt).time_entry_access_col_name2 := csr_tc_rec.time_entry_access_col_name2;
1198: --
1199: hr_utility.set_location('time_entry_access_col_name1:'||l_crit_dtls_tbl(l_crit_cnt).time_entry_access_col_name1, 20);
1200: hr_utility.set_location('time_entry_access_col_name2:'||l_crit_dtls_tbl(l_crit_cnt).time_entry_access_col_name2, 20);
1201: --
1202: End loop;
1203: --
1204: If l_crit_cnt > 0 then
1236: l_sql_stmt := l_sql_stmt || 'pqh_rbc_rate_retrieval.g_entry_val_tbl('||to_char(l_val_cnt)||').col_value := pqh_rbc_rate_retrieval.g_entry_rec.'||l_tc_ovrrd_col||';';
1237: l_sql_stmt := l_sql_stmt || 'End;';
1238: EXECUTE IMMEDIATE l_sql_stmt;
1239: --
1240: hr_utility.set_location(l_sql_stmt,100);
1241: hr_utility.set_location('Value is:'||g_entry_val_tbl(l_val_cnt).col_value,100);
1242: --
1243: End loop;
1244: --
1237: l_sql_stmt := l_sql_stmt || 'End;';
1238: EXECUTE IMMEDIATE l_sql_stmt;
1239: --
1240: hr_utility.set_location(l_sql_stmt,100);
1241: hr_utility.set_location('Value is:'||g_entry_val_tbl(l_val_cnt).col_value,100);
1242: --
1243: End loop;
1244: --
1245: For l_temp_cnt in 1..30 loop
1251: l_sql_stmt := l_sql_stmt || 'pqh_rbc_rate_retrieval.g_entry_val_tbl('||to_char(l_val_cnt)||').col_value := pqh_rbc_rate_retrieval.g_entry_rec.'||l_tc_ovrrd_col||';';
1252: l_sql_stmt := l_sql_stmt || 'End;';
1253: EXECUTE IMMEDIATE l_sql_stmt;
1254: --
1255: hr_utility.set_location(l_sql_stmt,90);
1256: hr_utility.set_location('Value is:'||g_entry_val_tbl(l_val_cnt).col_value,91);
1257: --
1258: End loop;
1259: --
1252: l_sql_stmt := l_sql_stmt || 'End;';
1253: EXECUTE IMMEDIATE l_sql_stmt;
1254: --
1255: hr_utility.set_location(l_sql_stmt,90);
1256: hr_utility.set_location('Value is:'||g_entry_val_tbl(l_val_cnt).col_value,91);
1257: --
1258: End loop;
1259: --
1260: -- Add Cost allocation flexfield segments.
1275: l_sql_stmt := l_sql_stmt || 'pqh_rbc_rate_retrieval.g_entry_val_tbl('||to_char(l_val_cnt)||').col_value := pqh_rbc_rate_retrieval.g_ckf_rec.'||l_tc_ovrrd_col||';';
1276: l_sql_stmt := l_sql_stmt || 'End;';
1277: EXECUTE IMMEDIATE l_sql_stmt;
1278: --
1279: hr_utility.set_location(l_sql_stmt,90);
1280: hr_utility.set_location('Value is:'||g_entry_val_tbl(l_val_cnt).col_value,91);
1281: --
1282: End loop;
1283: --
1276: l_sql_stmt := l_sql_stmt || 'End;';
1277: EXECUTE IMMEDIATE l_sql_stmt;
1278: --
1279: hr_utility.set_location(l_sql_stmt,90);
1280: hr_utility.set_location('Value is:'||g_entry_val_tbl(l_val_cnt).col_value,91);
1281: --
1282: End loop;
1283: --
1284: End if;
1293: l_dummy_value1 := null;
1294: l_dummy_value2 := null;
1295: For l_temp_cnt2 in 1..l_val_cnt loop
1296: -- Find the value;
1297: hr_utility.set_location('Comparing:'||l_crit_dtls_tbl(l_temp_cnt).time_entry_access_col_name1||','||g_entry_val_tbl(l_temp_cnt2).column_name,91);
1298: If l_crit_dtls_tbl(l_temp_cnt).time_entry_access_col_name1 = g_entry_val_tbl(l_temp_cnt2).column_name then
1299: l_dummy_value1 := g_entry_val_tbl(l_temp_cnt2).col_value;
1300: End if;
1301: If l_crit_dtls_tbl(l_temp_cnt).time_entry_access_col_name2 = g_entry_val_tbl(l_temp_cnt2).column_name then
1302: l_dummy_value2 := g_entry_val_tbl(l_temp_cnt2).col_value;
1303: End if;
1304: --
1305: End loop;
1306: hr_utility.set_location('Criteria Short Code is:'|| l_crit_val_rec.criteria_short_code,100);
1307: hr_utility.set_location('Override Value1 is:'||l_dummy_value1,101);
1308: hr_utility.set_location('Override Value2 is:'||l_dummy_value2,102);
1309: --
1310: l_crit_val_rec.number_value1 := null;
1303: End if;
1304: --
1305: End loop;
1306: hr_utility.set_location('Criteria Short Code is:'|| l_crit_val_rec.criteria_short_code,100);
1307: hr_utility.set_location('Override Value1 is:'||l_dummy_value1,101);
1308: hr_utility.set_location('Override Value2 is:'||l_dummy_value2,102);
1309: --
1310: l_crit_val_rec.number_value1 := null;
1311: l_crit_val_rec.number_value2 := null;
1304: --
1305: End loop;
1306: hr_utility.set_location('Criteria Short Code is:'|| l_crit_val_rec.criteria_short_code,100);
1307: hr_utility.set_location('Override Value1 is:'||l_dummy_value1,101);
1308: hr_utility.set_location('Override Value2 is:'||l_dummy_value2,102);
1309: --
1310: l_crit_val_rec.number_value1 := null;
1311: l_crit_val_rec.number_value2 := null;
1312: l_crit_val_rec.char_value1 := null;
1313: l_crit_val_rec.char_value2 := null;
1314: l_crit_val_rec.date_value1 := null;
1315: l_crit_val_rec.date_value2 := null;
1316: --
1317: hr_utility.set_location('Initialise override rec ',103);
1318: --
1319: If l_crit_dtls_tbl(l_temp_cnt).crit_col1_datatype = 'N' then
1320: hr_utility.set_location('Copy to number ',103);
1321: l_crit_val_rec.number_value1 := l_dummy_value1;
1316: --
1317: hr_utility.set_location('Initialise override rec ',103);
1318: --
1319: If l_crit_dtls_tbl(l_temp_cnt).crit_col1_datatype = 'N' then
1320: hr_utility.set_location('Copy to number ',103);
1321: l_crit_val_rec.number_value1 := l_dummy_value1;
1322: l_crit_val_rec.number_value2 := l_dummy_value2;
1323: elsif l_crit_dtls_tbl(l_temp_cnt).crit_col1_datatype = 'C' then
1324: hr_utility.set_location('Copy to char ',103);
1320: hr_utility.set_location('Copy to number ',103);
1321: l_crit_val_rec.number_value1 := l_dummy_value1;
1322: l_crit_val_rec.number_value2 := l_dummy_value2;
1323: elsif l_crit_dtls_tbl(l_temp_cnt).crit_col1_datatype = 'C' then
1324: hr_utility.set_location('Copy to char ',103);
1325: l_crit_val_rec.char_value1 := l_dummy_value1;
1326: l_crit_val_rec.char_value2 := l_dummy_value2;
1327: elsif l_crit_dtls_tbl(l_temp_cnt).crit_col1_datatype = 'D' then
1328: hr_utility.set_location('Copy to date ',103);
1324: hr_utility.set_location('Copy to char ',103);
1325: l_crit_val_rec.char_value1 := l_dummy_value1;
1326: l_crit_val_rec.char_value2 := l_dummy_value2;
1327: elsif l_crit_dtls_tbl(l_temp_cnt).crit_col1_datatype = 'D' then
1328: hr_utility.set_location('Copy to date ',103);
1329: l_crit_val_rec.date_value1 := to_date(l_dummy_value1,'yyyymmdd');
1330: l_crit_val_rec.date_value2 := to_date(l_dummy_value2,'yyyymmdd');
1331: End if;
1332: --
1337: End if;
1338: --
1339: End loop;
1340: End if; --If l_crit_cnt > 0
1341: hr_utility.set_location('Leaving:'||l_proc, 10);
1342: --
1343: End;
1344: --
1345: -------------------------< determine_rbc_rate >----------------------------------
1388: l_proc varchar2(72) := g_package||'determine_rbc_rate';
1389: --
1390: Begin
1391: --
1392: hr_utility.set_location('Entering:'||l_proc, 5);
1393: --
1394: g_person_id := null;
1395: g_assignment_id := null;
1396: g_element_type_id := null;
1409: --
1410: Open csr_linked_crd (g_element_type_id);
1411: Fetch csr_linked_crd into l_crit_rt_defn_id;
1412: If csr_linked_crd%notfound then
1413: hr_utility.set_message(8302,'PQH_RBC_ELE_NOT_LNKD_RT_TYP');
1414: hr_utility.raise_error;
1415: Else
1416: g_criteria_rate_defn_id := l_crit_rt_defn_id;
1417: End if;
1410: Open csr_linked_crd (g_element_type_id);
1411: Fetch csr_linked_crd into l_crit_rt_defn_id;
1412: If csr_linked_crd%notfound then
1413: hr_utility.set_message(8302,'PQH_RBC_ELE_NOT_LNKD_RT_TYP');
1414: hr_utility.raise_error;
1415: Else
1416: g_criteria_rate_defn_id := l_crit_rt_defn_id;
1417: End if;
1418: Close csr_linked_crd;
1423: --
1424: Open csr_asg_details(l_assignment_id);
1425: Fetch csr_asg_details into g_asg_rec;
1426: If csr_asg_details%notfound then
1427: hr_utility.set_message(8302,'PQH_RBC_INVALID_ASG_ID');
1428: hr_utility.raise_error;
1429: Else
1430: g_assignment_id := g_asg_rec.assignment_id;
1431: End if;
1424: Open csr_asg_details(l_assignment_id);
1425: Fetch csr_asg_details into g_asg_rec;
1426: If csr_asg_details%notfound then
1427: hr_utility.set_message(8302,'PQH_RBC_INVALID_ASG_ID');
1428: hr_utility.raise_error;
1429: Else
1430: g_assignment_id := g_asg_rec.assignment_id;
1431: End if;
1432: Close csr_asg_details;
1436: l_person_id := g_asg_rec.person_id;
1437: Open csr_per_details(l_person_id);
1438: Fetch csr_per_details into g_per_rec;
1439: If csr_per_details%notfound then
1440: hr_utility.set_message(8302,'PQH_RBC_INVALID_PERSON_ID');
1441: hr_utility.raise_error;
1442: Else
1443: g_person_id := l_person_id;
1444: End if;
1437: Open csr_per_details(l_person_id);
1438: Fetch csr_per_details into g_per_rec;
1439: If csr_per_details%notfound then
1440: hr_utility.set_message(8302,'PQH_RBC_INVALID_PERSON_ID');
1441: hr_utility.raise_error;
1442: Else
1443: g_person_id := l_person_id;
1444: End if;
1445: Close csr_per_details;
1470: p_min_rate => p_min_rate,
1471: p_mid_rate => p_mid_rate,
1472: p_max_rate => p_max_rate);
1473: --
1474: hr_utility.set_location('Leaving:'||l_proc, 10);
1475: --
1476: End;
1477: --
1478: -------------------------< determine_rbc_rate >----------------------------------
1526: l_proc varchar2(72) := g_package||'determine_rbc_rate';
1527: --
1528: Begin
1529: --
1530: hr_utility.set_location('Entering:'||l_proc, 5);
1531: --
1532: g_person_id := null;
1533: g_assignment_id := null;
1534: g_element_type_id := null;
1538: -- At least element type or rate type must be passed, but not both.
1539: --
1540: If p_element_type_id is NULL and p_crit_rt_defn_id is NULL then
1541: --
1542: hr_utility.set_message(8302,'PQH_RBC_NO_ELE_AND_RT_TYP');
1543: hr_utility.raise_error;
1544: --
1545: Elsif p_element_type_id IS NOT NULL and p_crit_rt_defn_id IS NULL then
1546: --
1539: --
1540: If p_element_type_id is NULL and p_crit_rt_defn_id is NULL then
1541: --
1542: hr_utility.set_message(8302,'PQH_RBC_NO_ELE_AND_RT_TYP');
1543: hr_utility.raise_error;
1544: --
1545: Elsif p_element_type_id IS NOT NULL and p_crit_rt_defn_id IS NULL then
1546: --
1547: -- If element type is passed, get the rate type linked to the element.
1548: --
1549: Open csr_linked_crd;
1550: Fetch csr_linked_crd into l_crit_rt_defn_id;
1551: If csr_linked_crd%notfound then
1552: hr_utility.set_message(8302,'PQH_RBC_ELE_NOT_LNKD_RT_TYP');
1553: hr_utility.raise_error;
1554: Else
1555: g_criteria_rate_defn_id := l_crit_rt_defn_id;
1556: End if;
1549: Open csr_linked_crd;
1550: Fetch csr_linked_crd into l_crit_rt_defn_id;
1551: If csr_linked_crd%notfound then
1552: hr_utility.set_message(8302,'PQH_RBC_ELE_NOT_LNKD_RT_TYP');
1553: hr_utility.raise_error;
1554: Else
1555: g_criteria_rate_defn_id := l_crit_rt_defn_id;
1556: End if;
1557: Close csr_linked_crd;
1575: -- Evaluate rate for primary assignment.
1576: --
1577: If p_person_id is NULL and p_assignment_id is NULL then
1578: --
1579: hr_utility.set_message(8302,'PQH_RBC_NO_PER_AND_ASG');
1580: hr_utility.raise_error;
1581: --
1582: Elsif p_person_id IS NOT NULL and p_assignment_id IS NULL then
1583: --
1576: --
1577: If p_person_id is NULL and p_assignment_id is NULL then
1578: --
1579: hr_utility.set_message(8302,'PQH_RBC_NO_PER_AND_ASG');
1580: hr_utility.raise_error;
1581: --
1582: Elsif p_person_id IS NOT NULL and p_assignment_id IS NULL then
1583: --
1584: -- Find the primary assignment and cache its details
1585: --
1586: Open csr_primary_asg_details;
1587: Fetch csr_primary_asg_details into g_asg_rec;
1588: If csr_primary_asg_details%notfound then
1589: hr_utility.set_message(8302,'PQH_RBC_NO_PRIM_ASG');
1590: hr_utility.raise_error;
1591: Else
1592: g_assignment_id := g_asg_rec.assignment_id;
1593: l_assignment_id := g_asg_rec.assignment_id;
1586: Open csr_primary_asg_details;
1587: Fetch csr_primary_asg_details into g_asg_rec;
1588: If csr_primary_asg_details%notfound then
1589: hr_utility.set_message(8302,'PQH_RBC_NO_PRIM_ASG');
1590: hr_utility.raise_error;
1591: Else
1592: g_assignment_id := g_asg_rec.assignment_id;
1593: l_assignment_id := g_asg_rec.assignment_id;
1594: End if;
1598: --
1599: Open csr_per_details(p_person_id);
1600: Fetch csr_per_details into g_per_rec;
1601: If csr_per_details%notfound then
1602: hr_utility.set_message(8302,'PQH_RBC_INVALID_PERSON_ID');
1603: hr_utility.raise_error;
1604: Else
1605: g_person_id := p_person_id;
1606: l_person_id := p_person_id;
1599: Open csr_per_details(p_person_id);
1600: Fetch csr_per_details into g_per_rec;
1601: If csr_per_details%notfound then
1602: hr_utility.set_message(8302,'PQH_RBC_INVALID_PERSON_ID');
1603: hr_utility.raise_error;
1604: Else
1605: g_person_id := p_person_id;
1606: l_person_id := p_person_id;
1607: End if;
1613: --
1614: Open csr_asg_details;
1615: Fetch csr_asg_details into g_asg_rec;
1616: If csr_asg_details%notfound then
1617: hr_utility.set_message(8302,'PQH_RBC_INVALID_ASG_ID');
1618: hr_utility.raise_error;
1619: Else
1620: g_assignment_id := g_asg_rec.assignment_id;
1621: l_assignment_id := g_asg_rec.assignment_id;
1614: Open csr_asg_details;
1615: Fetch csr_asg_details into g_asg_rec;
1616: If csr_asg_details%notfound then
1617: hr_utility.set_message(8302,'PQH_RBC_INVALID_ASG_ID');
1618: hr_utility.raise_error;
1619: Else
1620: g_assignment_id := g_asg_rec.assignment_id;
1621: l_assignment_id := g_asg_rec.assignment_id;
1622: End if;
1626: --
1627: Open csr_per_details(g_asg_rec.person_id);
1628: Fetch csr_per_details into g_per_rec;
1629: If csr_per_details%notfound then
1630: hr_utility.set_message(8302,'PQH_RBC_INVALID_PERSON_ID');
1631: hr_utility.raise_error;
1632: Else
1633: g_person_id := g_per_rec.person_id;
1634: l_person_id := g_per_rec.person_id;
1627: Open csr_per_details(g_asg_rec.person_id);
1628: Fetch csr_per_details into g_per_rec;
1629: If csr_per_details%notfound then
1630: hr_utility.set_message(8302,'PQH_RBC_INVALID_PERSON_ID');
1631: hr_utility.raise_error;
1632: Else
1633: g_person_id := g_per_rec.person_id;
1634: l_person_id := g_per_rec.person_id;
1635: End if;
1655: p_min_rate => p_min_rate,
1656: p_mid_rate => p_mid_rate,
1657: p_max_rate => p_max_rate);
1658: --
1659: hr_utility.set_location('Leaving:'||l_proc, 10);
1660: --
1661: End;
1662: --
1663: -------------------------< determine_rbc_rate >----------------------------------
1689: l_proc varchar2(72) := g_package||'determine_rbc_rate';
1690: --
1691: Begin
1692: --
1693: hr_utility.set_location('Entering:'||l_proc, 5);
1694: --
1695: g_person_id := null;
1696: g_assignment_id := null;
1697: g_element_type_id := null;
1701: -- At least element type or rate type must be passed, but not both.
1702: --
1703: If p_element_type_id is NULL and p_crit_rt_defn_id is NULL then
1704: --
1705: hr_utility.set_message(8302,'PQH_RBC_NO_ELE_AND_RT_TYP');
1706: hr_utility.raise_error;
1707: --
1708: Elsif p_element_type_id IS NOT NULL and p_crit_rt_defn_id IS NULL then
1709: --
1702: --
1703: If p_element_type_id is NULL and p_crit_rt_defn_id is NULL then
1704: --
1705: hr_utility.set_message(8302,'PQH_RBC_NO_ELE_AND_RT_TYP');
1706: hr_utility.raise_error;
1707: --
1708: Elsif p_element_type_id IS NOT NULL and p_crit_rt_defn_id IS NULL then
1709: --
1710: -- If element type is passed, get the rate type linked to the element.
1711: --
1712: Open csr_linked_crd;
1713: Fetch csr_linked_crd into l_crit_rt_defn_id;
1714: If csr_linked_crd%notfound then
1715: hr_utility.set_message(8302,'PQH_RBC_ELE_NOT_LNKD_RT_TYP');
1716: hr_utility.raise_error;
1717: Else
1718: g_criteria_rate_defn_id := l_crit_rt_defn_id;
1719: End if;
1712: Open csr_linked_crd;
1713: Fetch csr_linked_crd into l_crit_rt_defn_id;
1714: If csr_linked_crd%notfound then
1715: hr_utility.set_message(8302,'PQH_RBC_ELE_NOT_LNKD_RT_TYP');
1716: hr_utility.raise_error;
1717: Else
1718: g_criteria_rate_defn_id := l_crit_rt_defn_id;
1719: End if;
1720: Close csr_linked_crd;
1754: p_mid_rate => p_mid_rate,
1755: p_max_rate => p_max_rate);
1756: --
1757: --
1758: hr_utility.set_location('Leaving:'||l_proc, 10);
1759: --
1760: End;
1761: --
1762: ----------------------------------------------------------------------------------------------------
1783: --
1784: Begin
1785: --
1786: --
1787: hr_utility.set_location('Entering:'||l_proc, 10);
1788: --
1789: determine_rbc_rate(
1790: p_element_type_id => p_element_type_id,
1791: p_crit_rt_defn_id => p_crit_rt_defn_id,
1800: p_mid_rate => l_mid_rate,
1801: p_max_rate => l_max_rate);
1802: --
1803: --
1804: hr_utility.set_location('Leaving:'||l_proc, 10);
1805: --
1806: Return l_rate;
1807: --
1808: End get_persons_rbc_rate;
1828: --
1829: Begin
1830: --
1831: --
1832: hr_utility.set_location('Entering:'||l_proc, 10);
1833: --
1834: determine_rbc_rate(
1835: p_element_type_id => p_element_type_id,
1836: p_crit_rt_defn_id => p_crit_rt_defn_id,
1844: p_mid_rate => l_mid_rate,
1845: p_max_rate => l_max_rate);
1846: --
1847: --
1848: hr_utility.set_location('Leaving:'||l_proc, 10);
1849: --
1850: Return l_rate;
1851: --
1852: End get_persons_rbc_rate;
1871: --
1872: Begin
1873: --
1874: --
1875: hr_utility.set_location('Entering:'||l_proc, 10);
1876: --
1877: determine_rbc_rate(
1878: p_element_entry_id => p_element_entry_id,
1879: p_element_type_id => null,
1886: p_mid_rate => l_mid_rate,
1887: p_max_rate => l_max_rate);
1888: --
1889: --
1890: hr_utility.set_location('Leaving:'||l_proc, 10);
1891: --
1892: Return l_rate;
1893: --
1894: End get_ele_entry_rbc_rate;
1912: --
1913: Begin
1914: --
1915: --
1916: hr_utility.set_location('Entering:'||l_proc, 10);
1917: --
1918: determine_rbc_rate(
1919: p_element_entry_id => p_element_entry_id,
1920: p_element_type_id => p_element_type_id,
1927: p_mid_rate => l_mid_rate,
1928: p_max_rate => l_max_rate);
1929: --
1930: --
1931: hr_utility.set_location('Leaving:'||l_proc, 10);
1932: --
1933: Return l_rate;
1934: --
1935: End get_ele_entry_rbc_rate;