[Home] [Help]
630: FOR v_chosen IN csr_chosen_results LOOP
631: t_chosen_table(csr_chosen_results%rowcount) := v_chosen;
632: END LOOP;
633:
634: per_cagr_utility_pkg.put_log(' Stored '||t_chosen_table.count||' chosen results');
635:
636: hr_utility.set_location('Leaving:'||l_proc, 40);
637: RETURN t_chosen_table;
638:
785: l_rec := NULL; -- clear out result
786: l_rec.error := 'HR_289578_CAGR_NO_BENEFICIAL';
787: end if;
788: end if;
789: per_cagr_utility_pkg.put_log(' check_cache reports: '||nvl(l_rec.error,'RESULTS EXIST'));
790:
791: hr_utility.set_location('Leaving:'||l_proc, 50);
792: RETURN l_rec;
793:
920: ,p_structure(i).RETAINED_ENT_RESULT_ID
921: ,l_ovn);
922: l_num := l_num +1;
923: END LOOP;
924: per_cagr_utility_pkg.put_log(' Created '||l_num||' entitlement result records for the item ',1);
925: per_cagr_utility_pkg.put_log(' item_id : ' ||p_structure(1).CAGR_ENTITLEMENT_ITEM_ID);
926:
927: hr_utility.set_location('Leaving:'||l_proc, 30);
928:
921: ,l_ovn);
922: l_num := l_num +1;
923: END LOOP;
924: per_cagr_utility_pkg.put_log(' Created '||l_num||' entitlement result records for the item ',1);
925: per_cagr_utility_pkg.put_log(' item_id : ' ||p_structure(1).CAGR_ENTITLEMENT_ITEM_ID);
926:
927: hr_utility.set_location('Leaving:'||l_proc, 30);
928:
929: EXCEPTION
927: hr_utility.set_location('Leaving:'||l_proc, 30);
928:
929: EXCEPTION
930: WHEN OTHERS THEN
931: per_cagr_utility_pkg.put_log(' Failed to write result record',1);
932: per_cagr_utility_pkg.put_log(' ERROR: '||sqlerrm,1);
933:
934:
935: END write_results;
928:
929: EXCEPTION
930: WHEN OTHERS THEN
931: per_cagr_utility_pkg.put_log(' Failed to write result record',1);
932: per_cagr_utility_pkg.put_log(' ERROR: '||sqlerrm,1);
933:
934:
935: END write_results;
936:
963:
964:
965: BEGIN
966: hr_utility.set_location('Entering:'||l_proc, 10);
967: per_cagr_utility_pkg.put_log(' Executing insert_result_set');
968:
969: open csr_future_results;
970: fetch csr_future_results into v_future_start_date;
971: close csr_future_results;
972: if v_future_start_date is not null then
973: v_end_date := v_future_start_date -1;
974: end if;
975:
976: per_cagr_utility_pkg.put_log(' Start Date: '||p_params.effective_date||', End Date: '||v_end_date);
977:
978: write_results(p_structure,p_params.cagr_request_id,p_params.effective_date,v_end_date);
979:
980: per_cagr_utility_pkg.put_log(' Completed insert_result_set');
976: per_cagr_utility_pkg.put_log(' Start Date: '||p_params.effective_date||', End Date: '||v_end_date);
977:
978: write_results(p_structure,p_params.cagr_request_id,p_params.effective_date,v_end_date);
979:
980: per_cagr_utility_pkg.put_log(' Completed insert_result_set');
981: hr_utility.set_location('Leaving:'||l_proc, 30);
982:
983: END insert_result_set;
984:
1045: t_cagr_request request_table;
1046:
1047: BEGIN
1048: hr_utility.set_location('Entering:'||l_proc, 10);
1049: per_cagr_utility_pkg.put_log(' Preparing results cache',2);
1050:
1051: if p_switch = 'W' then
1052: -- we are 'updating' results for a specific item:
1053: -- previous results have been cleaned by csr_all_results, below (except SE mode which we do here)
1083: end loop;
1084: -- as we have deleted an entitlement result, also delete any
1085: -- log entries, for the results request_id.
1086: -- (we use the first fetched request_id, as it doesn't change).
1087: per_cagr_utility_pkg.remove_log_entries(v_delete_cagr_request_id);
1088: end if;
1089: close csr_item_results;
1090: end if;
1091:
1096: if v_future_start_date is not null then
1097: v_end_date := v_future_start_date -1;
1098: end if;
1099:
1100: per_cagr_utility_pkg.put_log(' New results have Start Date: '||p_params.effective_date||', End Date: '||v_end_date,2);
1101: -- now insert new set of results for the item
1102: write_results(p_structure,p_params.cagr_request_id,p_params.effective_date,v_end_date);
1103:
1104: elsif p_switch = 'C' then
1113: -- i.e. take the asg_id for the current item set
1114: -- which may change as we process different assignments
1115: v_assignment_id := p_structure(1).assignment_id;
1116: end if;
1117: per_cagr_utility_pkg.put_log(' Cleaning previous cache results found for asg id: '||v_assignment_id,2);
1118:
1119: open csr_all_results(v_assignment_id);
1120: loop
1121: fetch csr_all_results into v_start_date, v_cagr_request_id;
1143:
1144: if t_cagr_request.count > 0 then
1145: for j in 1 .. t_cagr_request.last loop
1146: -- as we have deleted an entitlement result, also delete any log entries, for the result's request_id.
1147: per_cagr_utility_pkg.remove_log_entries(t_cagr_request(j));
1148: end loop;
1149: end if;
1150:
1151: end if;
1148: end loop;
1149: end if;
1150:
1151: end if;
1152: per_cagr_utility_pkg.put_log(' Completed preparing results cache.',2);
1153:
1154: hr_utility.set_location('Leaving:'||l_proc, 50);
1155:
1156: EXCEPTION
1155:
1156: EXCEPTION
1157: WHEN e_resource_busy THEN
1158: -- raise resource busy message.
1159: per_cagr_utility_pkg.put_log(' ERROR: Another user is updating the entitlement results for the assignment.',1);
1160: per_cagr_utility_pkg.put_log(' Unable to lock result records exclusively. Please try again later.',1);
1161: fnd_message.set_name('PAY', 'HR_7165_OBJECT_LOCKED');
1162: fnd_message.set_token('TABLE_NAME', 'per_cagr_entitlement_results');
1163: fnd_message.raise_error;
1156: EXCEPTION
1157: WHEN e_resource_busy THEN
1158: -- raise resource busy message.
1159: per_cagr_utility_pkg.put_log(' ERROR: Another user is updating the entitlement results for the assignment.',1);
1160: per_cagr_utility_pkg.put_log(' Unable to lock result records exclusively. Please try again later.',1);
1161: fnd_message.set_name('PAY', 'HR_7165_OBJECT_LOCKED');
1162: fnd_message.set_token('TABLE_NAME', 'per_cagr_entitlement_results');
1163: fnd_message.raise_error;
1164:
1205:
1206: BEGIN
1207: hr_utility.set_location('Entering:'||l_proc, 10);
1208:
1209: per_cagr_utility_pkg.put_log('Identified entitlement line records, calling benmngle at: '||fnd_date.date_to_canonical(sysdate));
1210: per_cagr_utility_pkg.put_log(' p_person_id: '|| to_char(p_person_id));
1211: per_cagr_utility_pkg.put_log(' p_effective_date: '|| to_char(p_effective_date,'DD-MON-YYYY'));
1212: per_cagr_utility_pkg.put_log(' p_mode: '||l_mode);
1213: per_cagr_utility_pkg.put_log(' p_derivable_factors: '||l_derivable_factors_flag);
1206: BEGIN
1207: hr_utility.set_location('Entering:'||l_proc, 10);
1208:
1209: per_cagr_utility_pkg.put_log('Identified entitlement line records, calling benmngle at: '||fnd_date.date_to_canonical(sysdate));
1210: per_cagr_utility_pkg.put_log(' p_person_id: '|| to_char(p_person_id));
1211: per_cagr_utility_pkg.put_log(' p_effective_date: '|| to_char(p_effective_date,'DD-MON-YYYY'));
1212: per_cagr_utility_pkg.put_log(' p_mode: '||l_mode);
1213: per_cagr_utility_pkg.put_log(' p_derivable_factors: '||l_derivable_factors_flag);
1214: per_cagr_utility_pkg.put_log(' p_validate: '||l_validate_flag);
1207: hr_utility.set_location('Entering:'||l_proc, 10);
1208:
1209: per_cagr_utility_pkg.put_log('Identified entitlement line records, calling benmngle at: '||fnd_date.date_to_canonical(sysdate));
1210: per_cagr_utility_pkg.put_log(' p_person_id: '|| to_char(p_person_id));
1211: per_cagr_utility_pkg.put_log(' p_effective_date: '|| to_char(p_effective_date,'DD-MON-YYYY'));
1212: per_cagr_utility_pkg.put_log(' p_mode: '||l_mode);
1213: per_cagr_utility_pkg.put_log(' p_derivable_factors: '||l_derivable_factors_flag);
1214: per_cagr_utility_pkg.put_log(' p_validate: '||l_validate_flag);
1215: per_cagr_utility_pkg.put_log(' p_pl_id: '|| to_char(p_pl_id));
1208:
1209: per_cagr_utility_pkg.put_log('Identified entitlement line records, calling benmngle at: '||fnd_date.date_to_canonical(sysdate));
1210: per_cagr_utility_pkg.put_log(' p_person_id: '|| to_char(p_person_id));
1211: per_cagr_utility_pkg.put_log(' p_effective_date: '|| to_char(p_effective_date,'DD-MON-YYYY'));
1212: per_cagr_utility_pkg.put_log(' p_mode: '||l_mode);
1213: per_cagr_utility_pkg.put_log(' p_derivable_factors: '||l_derivable_factors_flag);
1214: per_cagr_utility_pkg.put_log(' p_validate: '||l_validate_flag);
1215: per_cagr_utility_pkg.put_log(' p_pl_id: '|| to_char(p_pl_id));
1216: per_cagr_utility_pkg.put_log(' p_cagr_id: '|| to_char( p_params.collective_agreement_id)); -- Bug # 5391298
1209: per_cagr_utility_pkg.put_log('Identified entitlement line records, calling benmngle at: '||fnd_date.date_to_canonical(sysdate));
1210: per_cagr_utility_pkg.put_log(' p_person_id: '|| to_char(p_person_id));
1211: per_cagr_utility_pkg.put_log(' p_effective_date: '|| to_char(p_effective_date,'DD-MON-YYYY'));
1212: per_cagr_utility_pkg.put_log(' p_mode: '||l_mode);
1213: per_cagr_utility_pkg.put_log(' p_derivable_factors: '||l_derivable_factors_flag);
1214: per_cagr_utility_pkg.put_log(' p_validate: '||l_validate_flag);
1215: per_cagr_utility_pkg.put_log(' p_pl_id: '|| to_char(p_pl_id));
1216: per_cagr_utility_pkg.put_log(' p_cagr_id: '|| to_char( p_params.collective_agreement_id)); -- Bug # 5391298
1217: per_cagr_utility_pkg.put_log(' p_opt_id: '|| to_char(p_opt_id));
1210: per_cagr_utility_pkg.put_log(' p_person_id: '|| to_char(p_person_id));
1211: per_cagr_utility_pkg.put_log(' p_effective_date: '|| to_char(p_effective_date,'DD-MON-YYYY'));
1212: per_cagr_utility_pkg.put_log(' p_mode: '||l_mode);
1213: per_cagr_utility_pkg.put_log(' p_derivable_factors: '||l_derivable_factors_flag);
1214: per_cagr_utility_pkg.put_log(' p_validate: '||l_validate_flag);
1215: per_cagr_utility_pkg.put_log(' p_pl_id: '|| to_char(p_pl_id));
1216: per_cagr_utility_pkg.put_log(' p_cagr_id: '|| to_char( p_params.collective_agreement_id)); -- Bug # 5391298
1217: per_cagr_utility_pkg.put_log(' p_opt_id: '|| to_char(p_opt_id));
1218: per_cagr_utility_pkg.put_log(' p_bg_id: '|| to_char(p_bg_id));
1211: per_cagr_utility_pkg.put_log(' p_effective_date: '|| to_char(p_effective_date,'DD-MON-YYYY'));
1212: per_cagr_utility_pkg.put_log(' p_mode: '||l_mode);
1213: per_cagr_utility_pkg.put_log(' p_derivable_factors: '||l_derivable_factors_flag);
1214: per_cagr_utility_pkg.put_log(' p_validate: '||l_validate_flag);
1215: per_cagr_utility_pkg.put_log(' p_pl_id: '|| to_char(p_pl_id));
1216: per_cagr_utility_pkg.put_log(' p_cagr_id: '|| to_char( p_params.collective_agreement_id)); -- Bug # 5391298
1217: per_cagr_utility_pkg.put_log(' p_opt_id: '|| to_char(p_opt_id));
1218: per_cagr_utility_pkg.put_log(' p_bg_id: '|| to_char(p_bg_id));
1219:
1212: per_cagr_utility_pkg.put_log(' p_mode: '||l_mode);
1213: per_cagr_utility_pkg.put_log(' p_derivable_factors: '||l_derivable_factors_flag);
1214: per_cagr_utility_pkg.put_log(' p_validate: '||l_validate_flag);
1215: per_cagr_utility_pkg.put_log(' p_pl_id: '|| to_char(p_pl_id));
1216: per_cagr_utility_pkg.put_log(' p_cagr_id: '|| to_char( p_params.collective_agreement_id)); -- Bug # 5391298
1217: per_cagr_utility_pkg.put_log(' p_opt_id: '|| to_char(p_opt_id));
1218: per_cagr_utility_pkg.put_log(' p_bg_id: '|| to_char(p_bg_id));
1219:
1220: ben_manage_life_events.internal_process
1213: per_cagr_utility_pkg.put_log(' p_derivable_factors: '||l_derivable_factors_flag);
1214: per_cagr_utility_pkg.put_log(' p_validate: '||l_validate_flag);
1215: per_cagr_utility_pkg.put_log(' p_pl_id: '|| to_char(p_pl_id));
1216: per_cagr_utility_pkg.put_log(' p_cagr_id: '|| to_char( p_params.collective_agreement_id)); -- Bug # 5391298
1217: per_cagr_utility_pkg.put_log(' p_opt_id: '|| to_char(p_opt_id));
1218: per_cagr_utility_pkg.put_log(' p_bg_id: '|| to_char(p_bg_id));
1219:
1220: ben_manage_life_events.internal_process
1221: (errbuf => l_errbuf,
1214: per_cagr_utility_pkg.put_log(' p_validate: '||l_validate_flag);
1215: per_cagr_utility_pkg.put_log(' p_pl_id: '|| to_char(p_pl_id));
1216: per_cagr_utility_pkg.put_log(' p_cagr_id: '|| to_char( p_params.collective_agreement_id)); -- Bug # 5391298
1217: per_cagr_utility_pkg.put_log(' p_opt_id: '|| to_char(p_opt_id));
1218: per_cagr_utility_pkg.put_log(' p_bg_id: '|| to_char(p_bg_id));
1219:
1220: ben_manage_life_events.internal_process
1221: (errbuf => l_errbuf,
1222: retcode => l_retcode,
1234: p_audit_log_flag => 'Y');
1235:
1236: Commit;
1237:
1238: per_cagr_utility_pkg.put_log('Completed benmngle at: '||
1239: fnd_date.date_to_canonical(sysdate)||' return code is :'||to_char(l_retcode));
1240: per_cagr_utility_pkg.put_log('benefit_action_id: '|| to_char(l_benefit_action_id));
1241: p_benefit_action_id := l_benefit_action_id;
1242:
1236: Commit;
1237:
1238: per_cagr_utility_pkg.put_log('Completed benmngle at: '||
1239: fnd_date.date_to_canonical(sysdate)||' return code is :'||to_char(l_retcode));
1240: per_cagr_utility_pkg.put_log('benefit_action_id: '|| to_char(l_benefit_action_id));
1241: p_benefit_action_id := l_benefit_action_id;
1242:
1243: hr_utility.set_location('Leaving:'||l_proc, 30);
1244:
1245: EXCEPTION
1246: when others then
1247: Rollback;
1248: hr_utility.set_location('Fatal Error: '||l_proc, 20);
1249: per_cagr_utility_pkg.put_log('ben_manage_life_events.process fatal error',1);
1250: per_cagr_utility_pkg.put_log('Error: '||sqlerrm,1);
1251: per_cagr_utility_pkg.write_log_file(p_params.cagr_request_id);
1252: raise;
1253:
1246: when others then
1247: Rollback;
1248: hr_utility.set_location('Fatal Error: '||l_proc, 20);
1249: per_cagr_utility_pkg.put_log('ben_manage_life_events.process fatal error',1);
1250: per_cagr_utility_pkg.put_log('Error: '||sqlerrm,1);
1251: per_cagr_utility_pkg.write_log_file(p_params.cagr_request_id);
1252: raise;
1253:
1254: END process_entitlement_lines;
1247: Rollback;
1248: hr_utility.set_location('Fatal Error: '||l_proc, 20);
1249: per_cagr_utility_pkg.put_log('ben_manage_life_events.process fatal error',1);
1250: per_cagr_utility_pkg.put_log('Error: '||sqlerrm,1);
1251: per_cagr_utility_pkg.write_log_file(p_params.cagr_request_id);
1252: raise;
1253:
1254: END process_entitlement_lines;
1255:
1295: p_eligibility_table(p_counter).OIPL_ID := v_elig_lines.OIPL_ID;
1296: p_eligibility_table(p_counter).ELIG_FLAG := v_elig_lines.ELIG_FLAG;
1297: end loop;
1298: end if;
1299: per_cagr_utility_pkg.put_log('Benmngle created '||to_char(p_counter)||' positive eligibility records');
1300:
1301: hr_utility.set_location('Leaving:'||l_proc, 30);
1302:
1303: END get_BEN_eligibility_info;
1323: BEGIN
1324: hr_utility.set_location('Entering:'||l_proc, 10);
1325:
1326: if p_oipl_id is null then
1327: per_cagr_utility_pkg.put_log(' ERROR: Option in plan id is null for criteria line ',1);
1328: hr_utility.set_message(800, 'HR_289415_CAGR_OIPL_NULL');
1329: hr_utility.raise_error;
1330: end if;
1331:
1334: for i in p_eligibility_table.FIRST .. p_eligibility_table.LAST loop
1335: if p_eligibility_table(i).OIPL_ID = p_oipl_id then
1336: v_found := TRUE;
1337: if p_eligibility_table(i).ELIG_FLAG = 'Y' then
1338: per_cagr_utility_pkg.put_log(' The criteria eligibility profile is satisfied.',1 );
1339: return TRUE;
1340: end if;
1341: end if;
1342: end loop;
1345: for i in p_eligibility_table.FIRST .. p_eligibility_table.LAST loop
1346: if p_eligibility_table(i).OIPL_ID = p_oipl_id then
1347: v_found := TRUE;
1348: if p_eligibility_table(i).PERSON_ID = p_person_id and p_eligibility_table(i).ELIG_FLAG = 'Y' then
1349: per_cagr_utility_pkg.put_log(' The criteria eligibility profile is satisfied.',1 );
1350: return TRUE;
1351: end if;
1352: end if;
1353: end loop;
1353: end loop;
1354: end if;
1355:
1356: if v_found = FALSE then
1357: per_cagr_utility_pkg.put_log(' ERROR: Option in plan id does not exist ',1);
1358: hr_utility.set_message(800, 'HR_289416_CAGR_OIPL_NOT_FOUND');
1359: hr_utility.raise_error;
1360: end if;
1361:
1358: hr_utility.set_message(800, 'HR_289416_CAGR_OIPL_NOT_FOUND');
1359: hr_utility.raise_error;
1360: end if;
1361:
1362: per_cagr_utility_pkg.put_log(' The criteria eligibility profile is not satisfied.',1);
1363: hr_utility.set_location('Leaving:'||l_proc, 50);
1364: return FALSE;
1365:
1366: END check_entitlement_eligible;
1517:
1518: hr_utility.set_location('Entering:'||l_proc, 10);
1519: l_category := p_results_table(1).category_name;
1520: if p_ben_rule is not null then
1521: per_cagr_utility_pkg.put_log(' Evaluating '||p_ben_rule||' beneficial rule on '||p_results_table.last||' results for this entitlement item',1);
1522: else
1523: per_cagr_utility_pkg.put_log(' No beneficial rule is defined for this entitlement item',1);
1524: end if;
1525:
1519: l_category := p_results_table(1).category_name;
1520: if p_ben_rule is not null then
1521: per_cagr_utility_pkg.put_log(' Evaluating '||p_ben_rule||' beneficial rule on '||p_results_table.last||' results for this entitlement item',1);
1522: else
1523: per_cagr_utility_pkg.put_log(' No beneficial rule is defined for this entitlement item',1);
1524: end if;
1525:
1526: -- first test to see whether we are processing value or step_id field, in result records
1527: if p_results_table(1).value is not null and p_results_table(1).step_id is null then
1528: l_ben_field := 'VALUE';
1529: elsif p_results_table(1).value is null and p_results_table(1).step_id is not null then
1530: l_ben_field := 'STEP_ID';
1531: else
1532: per_cagr_utility_pkg.put_log('Cannot determine either of value or step_id to process');
1533: p_rule_inconclusive := TRUE;
1534: goto end_of_procedure; -- don't raise an exception
1535: end if;
1536:
1533: p_rule_inconclusive := TRUE;
1534: goto end_of_procedure; -- don't raise an exception
1535: end if;
1536:
1537: per_cagr_utility_pkg.put_log(' Beneficial field is: '||l_ben_field);
1538:
1539: -- if only one record in the input table, default and skip processing
1540: if p_results_table.count = 1 then
1541: if l_ben_field = 'VALUE' then
1547: -- start processing the results as > 1 record in table
1548: -- and ben_rule is set for the item.
1549:
1550: if p_ben_rule_vs_id is not null then
1551: per_cagr_utility_pkg.put_log(' Beneficial rule uses ValueSet id: '||p_ben_rule_vs_id);
1552: -- we are using a data column so build list of id's from results for sql
1553: if l_ben_field = 'VALUE' then
1554: for i in p_results_table.first .. p_results_table.last loop
1555: l_list_str := l_list_str || p_results_table(i).VALUE ||',';
1561: end if;
1562: l_list_str := substr(l_list_str,1,(length(l_list_str) -1));
1563: l_list_str := '('||l_list_str||')';
1564: -- get the sql to be used to get the data column from value set
1565: l_sql := per_cagr_utility_pkg.get_sql_from_vset_id(p_ben_rule_vs_id);
1566: if l_sql is null then
1567: per_cagr_utility_pkg.put_log(' Could not determine SQL for ValueSet id');
1568: p_rule_inconclusive := TRUE;
1569: else
1563: l_list_str := '('||l_list_str||')';
1564: -- get the sql to be used to get the data column from value set
1565: l_sql := per_cagr_utility_pkg.get_sql_from_vset_id(p_ben_rule_vs_id);
1566: if l_sql is null then
1567: per_cagr_utility_pkg.put_log(' Could not determine SQL for ValueSet id');
1568: p_rule_inconclusive := TRUE;
1569: else
1570: -- replace BG_ID, and insert list of ids into the sql statement
1571: l_sql := replace(l_sql,':$PROFILES$.PER_BUSINESS_GROUP_ID',p_params.business_group_id);
1569: else
1570: -- replace BG_ID, and insert list of ids into the sql statement
1571: l_sql := replace(l_sql,':$PROFILES$.PER_BUSINESS_GROUP_ID',p_params.business_group_id);
1572: l_sql := replace(l_sql,'()',l_list_str);
1573: per_cagr_utility_pkg.put_log(l_sql);
1574:
1575: -- determine datatype of value set data column
1576: open csr_data(p_ben_rule_vs_id);
1577: fetch csr_data into l_col_data_type;
1576: open csr_data(p_ben_rule_vs_id);
1577: fetch csr_data into l_col_data_type;
1578: if csr_data%notfound then
1579: close csr_data;
1580: per_cagr_utility_pkg.put_log(' ValueSet column type not found');
1581: p_rule_inconclusive := TRUE;
1582: goto end_of_procedure;
1583: end if;
1584: per_cagr_utility_pkg.put_log(' Datatype of ValueSet data column is: '||l_col_data_type);
1580: per_cagr_utility_pkg.put_log(' ValueSet column type not found');
1581: p_rule_inconclusive := TRUE;
1582: goto end_of_procedure;
1583: end if;
1584: per_cagr_utility_pkg.put_log(' Datatype of ValueSet data column is: '||l_col_data_type);
1585:
1586: -- dynamic sql to get the value set data column values for list of id's
1587: -- and call relevant ben function for the datatype
1588: open l_dyn_csr for l_sql;
1615:
1616: else -- do regular processing on actual cagr data, using value only, and the
1617: -- value which is not treated as an ID (user must set up value set for ids
1618: -- within value or step_id).
1619: per_cagr_utility_pkg.put_log(' Ben rule uses cagr value column');
1620: per_cagr_utility_pkg.put_log(' Datatype of cagr column is: '||p_results_table(1).column_type);
1621:
1622: BEGIN
1623: if p_results_table(1).column_type = 'VAR' then
1616: else -- do regular processing on actual cagr data, using value only, and the
1617: -- value which is not treated as an ID (user must set up value set for ids
1618: -- within value or step_id).
1619: per_cagr_utility_pkg.put_log(' Ben rule uses cagr value column');
1620: per_cagr_utility_pkg.put_log(' Datatype of cagr column is: '||p_results_table(1).column_type);
1621:
1622: BEGIN
1623: if p_results_table(1).column_type = 'VAR' then
1624: for i in p_results_table.first..p_results_table.last loop
1637: do_date_beneficial(l_dyn_csr_table,p_ben_rule,l_ben_row);
1638: end if;
1639: EXCEPTION
1640: WHEN OTHERS THEN -- trap any value conversion exceptions
1641: per_cagr_utility_pkg.put_log(' Beneficial Rule evaluation error',1);
1642: per_cagr_utility_pkg.put_log(' ERROR: '||sqlerrm,1);
1643: p_rule_inconclusive := TRUE;
1644: goto end_of_procedure;
1645: END;
1638: end if;
1639: EXCEPTION
1640: WHEN OTHERS THEN -- trap any value conversion exceptions
1641: per_cagr_utility_pkg.put_log(' Beneficial Rule evaluation error',1);
1642: per_cagr_utility_pkg.put_log(' ERROR: '||sqlerrm,1);
1643: p_rule_inconclusive := TRUE;
1644: goto end_of_procedure;
1645: END;
1646: end if;
1651: -- mark the beneficial record and log the value
1652: p_results_table(l_ben_row).BENEFICIAL_FLAG := 'Y';
1653: p_ben_row := l_ben_row;
1654: if l_ben_field = 'VALUE' then
1655: per_cagr_utility_pkg.put_log(' Beneficial value is: '||p_results_table(l_ben_row).value,1);
1656: elsif l_ben_field = 'STEP_ID' then
1657: per_cagr_utility_pkg.put_log(' Beneficial step_id is: '||p_results_table(l_ben_row).step_id,1);
1658: end if;
1659: else
1653: p_ben_row := l_ben_row;
1654: if l_ben_field = 'VALUE' then
1655: per_cagr_utility_pkg.put_log(' Beneficial value is: '||p_results_table(l_ben_row).value,1);
1656: elsif l_ben_field = 'STEP_ID' then
1657: per_cagr_utility_pkg.put_log(' Beneficial step_id is: '||p_results_table(l_ben_row).step_id,1);
1658: end if;
1659: else
1660: if p_ben_rule is not null then
1661: p_rule_inconclusive := TRUE;
1756:
1757: BEGIN
1758:
1759: hr_utility.set_location('Entering:'||l_proc, 10);
1760: per_cagr_utility_pkg.put_log(' Evaluating related Retained Rights for the item ',1);
1761: -- We are processing RR for a dataitem for an ASG
1762: open csr_cagr_retained_rights;
1763: LOOP
1764: fetch csr_cagr_retained_rights into v_csr_rr_rec;
1764: fetch csr_cagr_retained_rights into v_csr_rr_rec;
1765: exit when csr_cagr_retained_rights%notfound;
1766:
1767: if v_csr_rr_rec.OIPL_ID <> 0 and v_csr_rr_rec.eligy_prfl_id <> 0 then
1768: l_source_name := per_cagr_utility_pkg.get_elig_source(v_csr_rr_rec.eligy_prfl_id
1769: ,v_csr_rr_rec.formula_id
1770: ,p_params.effective_date);
1771: else
1772: l_source_name := '*** Default ***';
1784: v_from_step_id := NULL;
1785: v_to_step_id := NULL;
1786:
1787: if v_csr_rr_rec.CAGR_ENTITLEMENT_LINE_ID is null then -- ent retained right
1788: per_cagr_utility_pkg.put_log(' found retained entitlement: '||l_source_name,1);
1789: v_units_of_measure := v_csr_rr_rec.UNITS_OF_MEASURE;
1790:
1791: per_cagr_utility_pkg.put_log(' Retained Right is for entitlement: '|| v_csr_rr_rec.cagr_entitlement_id);
1792:
1787: if v_csr_rr_rec.CAGR_ENTITLEMENT_LINE_ID is null then -- ent retained right
1788: per_cagr_utility_pkg.put_log(' found retained entitlement: '||l_source_name,1);
1789: v_units_of_measure := v_csr_rr_rec.UNITS_OF_MEASURE;
1790:
1791: per_cagr_utility_pkg.put_log(' Retained Right is for entitlement: '|| v_csr_rr_rec.cagr_entitlement_id);
1792:
1793: if v_csr_rr_rec.freeze_flag = 'N' then
1794: -- not frozen, so get the latest value from the latest formula_id
1795: -- for the retained entitlement
1792:
1793: if v_csr_rr_rec.freeze_flag = 'N' then
1794: -- not frozen, so get the latest value from the latest formula_id
1795: -- for the retained entitlement
1796: per_cagr_utility_pkg.put_log(' Retained Right is not frozen');
1797: open csr_cagr_ents(v_csr_rr_rec.CAGR_ENTITLEMENT_ID);
1798: fetch csr_cagr_ents into v_cagr_ents_rec;
1799: if csr_cagr_ents%found then
1800: v_units_of_measure := v_cagr_ents_rec.UNITS_OF_MEASURE;
1813: v_range_to := l_cagr_FF_record.range_to;
1814: v_write_flag := TRUE;
1815: else
1816: -- log message as the formula evaluated to null and continue with next entitlement record
1817: per_cagr_utility_pkg.put_log(' Fast Formula did not determine an eligible entitlement.',1);
1818: v_write_flag := FALSE;
1819: end if;
1820: elsif v_csr_rr_rec.category_name = 'PYS' then
1821: if l_cagr_FF_record.grade_spine_id is not null
1828: v_to_step_id := l_cagr_FF_record.to_step_id;
1829: v_write_flag := TRUE;
1830: else
1831: -- log error as the formula didn't evaluate and continue with next entitlement record
1832: per_cagr_utility_pkg.put_log(' ERROR: Fast Formula failed to produce expected output',1);
1833: v_write_flag := FALSE;
1834: end if;
1835: end if;
1836: end if;
1838:
1839: elsif v_csr_rr_rec.freeze_flag = 'Y' then
1840: -- frozen, so assign use the frozen value (instead of re-evaluating formula)
1841: -- and trigger this retained right entitlement result to be added to the process set.
1842: per_cagr_utility_pkg.put_log(' Retained Right is frozen');
1843: v_value := v_csr_rr_rec.value;
1844: v_range_from := v_csr_rr_rec.range_from;
1845: v_range_to := v_csr_rr_rec.range_to;
1846: v_units_of_measure := v_csr_rr_rec.units_of_measure;
1852: v_write_flag := TRUE;
1853: end if;
1854:
1855: else -- ent line retained right
1856: per_cagr_utility_pkg.put_log(' found retained criteria line: '||l_source_name,1);
1857: per_cagr_utility_pkg.put_log(' criteria line_id: '|| v_csr_rr_rec.cagr_entitlement_line_id);
1858:
1859: if v_csr_rr_rec.freeze_flag = 'N' then
1860: per_cagr_utility_pkg.put_log(' Retained Right is not frozen');
1853: end if;
1854:
1855: else -- ent line retained right
1856: per_cagr_utility_pkg.put_log(' found retained criteria line: '||l_source_name,1);
1857: per_cagr_utility_pkg.put_log(' criteria line_id: '|| v_csr_rr_rec.cagr_entitlement_line_id);
1858:
1859: if v_csr_rr_rec.freeze_flag = 'N' then
1860: per_cagr_utility_pkg.put_log(' Retained Right is not frozen');
1861: -- not frozen, so get the latest values for the item line
1856: per_cagr_utility_pkg.put_log(' found retained criteria line: '||l_source_name,1);
1857: per_cagr_utility_pkg.put_log(' criteria line_id: '|| v_csr_rr_rec.cagr_entitlement_line_id);
1858:
1859: if v_csr_rr_rec.freeze_flag = 'N' then
1860: per_cagr_utility_pkg.put_log(' Retained Right is not frozen');
1861: -- not frozen, so get the latest values for the item line
1862: open csr_cagr_lines(v_csr_rr_rec.cagr_entitlement_line_id, v_csr_rr_rec.collective_agreement_id);
1863: fetch csr_cagr_lines into v_cagr_lines_rec;
1864: if csr_cagr_lines%found then
1876: else
1877: close csr_cagr_lines;
1878: end if;
1879: elsif v_csr_rr_rec.freeze_flag = 'Y' then
1880: per_cagr_utility_pkg.put_log(' Retained Right is frozen');
1881: -- frozen, so use the values that was saved
1882: -- on the retained right start date.
1883: v_value := v_csr_rr_rec.value;
1884: v_range_from := v_csr_rr_rec.range_from;
1957: If v_dup_record is not null then
1958: -- delete the duplicate result and put RR in its place.
1959: p_structure.delete(v_dup_record);
1960: v_counter := v_dup_record;
1961: per_cagr_utility_pkg.put_log(' Removed duplicate result for this retained right.');
1962: End If;
1963: If v_counter is null then
1964: p_counter := p_counter +1;
1965: v_counter := p_counter;
1998: p_structure(v_counter).RETAINED_ENT_RESULT_ID := v_csr_rr_rec.CAGR_ENTITLEMENT_RESULT_ID;
1999: end if;
2000: END LOOP;
2001: close csr_cagr_retained_rights;
2002: per_cagr_utility_pkg.put_log(' Completed related Retained Rights.',1);
2003: hr_utility.set_location('Leaving:'||l_proc, 50);
2004:
2005: END add_related_ret_rights;
2006:
2115:
2116: BEGIN
2117:
2118: hr_utility.set_location('Entering:'||l_proc, 10);
2119: per_cagr_utility_pkg.put_log(' Evaluating Retained Rights for other items',1);
2120: open csr_cagr_other_ret_rights;
2121: LOOP
2122: fetch csr_cagr_other_ret_rights into v_csr_rr_rec;
2123: exit when csr_cagr_other_ret_rights%notfound;
2156:
2157: if v_rule_inconclusive then
2158: -- output warning message that beneficial could not be chosen
2159: -- and write results anyway, if profile option allows
2160: per_cagr_utility_pkg.put_log(' ERROR: Beneficial Rule was inconclusive',1);
2161: end if;
2162: update_result_set(t_results_table,p_params,'W');
2163: v_counter := 0;
2164: t_results_table.delete;
2216: v_range_to := l_cagr_FF_record.range_to;
2217: v_write_flag := TRUE;
2218: else
2219: -- log message as the formula evaluated to null and continue with next entitlement record
2220: per_cagr_utility_pkg.put_log(' Fast Formula did not determine an eligible entitlement.',1);
2221: v_write_flag := FALSE;
2222: end if;
2223: elsif v_csr_rr_rec.category_name = 'PYS' then
2224: if l_cagr_FF_record.grade_spine_id is not null
2231: v_to_step_id := l_cagr_FF_record.to_step_id;
2232: v_write_flag := TRUE;
2233: else
2234: -- log error as the formula didn't evaluate and continue with next entitlement record
2235: per_cagr_utility_pkg.put_log(' ERROR: Fast Formula failed to produce expected output',1);
2236: v_write_flag := FALSE;
2237: end if;
2238: end if;
2239: end if;
2348: -- and clear the plsql table.
2349: if v_rule_inconclusive then
2350: -- output warning message that beneficial could not be chosen
2351: -- and write results anyway..
2352: per_cagr_utility_pkg.put_log(' ERROR: Beneficial Rule was inconclusive',1);
2353: end if;
2354: update_result_set(t_results_table,p_params,'W');
2355: t_results_table.delete;
2356: v_counter := 0;
2354: update_result_set(t_results_table,p_params,'W');
2355: t_results_table.delete;
2356: v_counter := 0;
2357: end if;
2358: per_cagr_utility_pkg.put_log(' Completed Retained Rights for other items.',1);
2359: hr_utility.set_location('Leaving:'||l_proc, 50);
2360:
2361: END add_other_ret_rights;
2362:
2366:
2367: BEGIN
2368:
2369: hr_utility.set_location('Entering:'||l_proc, 5);
2370: per_cagr_utility_pkg.put_log(g_separator,1);
2371: per_cagr_utility_pkg.put_log('Starting Evaluation Process ('||fnd_date.date_to_canonical(sysdate)||')',1);
2372: --
2373: -- choose which cursor to open,
2374: -- depending upon operation mode
2367: BEGIN
2368:
2369: hr_utility.set_location('Entering:'||l_proc, 5);
2370: per_cagr_utility_pkg.put_log(g_separator,1);
2371: per_cagr_utility_pkg.put_log('Starting Evaluation Process ('||fnd_date.date_to_canonical(sysdate)||')',1);
2372: --
2373: -- choose which cursor to open,
2374: -- depending upon operation mode
2375: --
2407: ,p_eligibility_table => t_eligibility_table
2408: ,p_counter => v_eligibility_counter);
2409:
2410: else
2411: per_cagr_utility_pkg.put_log(' No active criteria lines found for the collective agreement.',1);
2412: close csr_SA_drive_benmngle;
2413: end if;
2414:
2415: -- open the cursor, to get current entitlements
2453:
2454: if v_rule_inconclusive then
2455: -- output warning message that beneficial could not be chosen
2456: -- and write results anyway..
2457: per_cagr_utility_pkg.put_log(' ERROR: Beneficial Rule was inconclusive',1);
2458: end if;
2459: apply_chosen_result(t_results_table,t_chosen_table,p_params.commit_flag);
2460: update_result_set(t_results_table,p_params,'W');
2461: v_counter := 0;
2465: v_last_dataitem_id := v_ents.CAGR_ENTITLEMENT_ITEM_ID;
2466: -- store new beneficial_rule
2467: v_beneficial_rule := v_ents.BENEFICIAL_RULE;
2468: v_beneficial_rule_vs_id := v_ents.BENEFICIAL_RULE_VALUE_SET_ID;
2469: per_cagr_utility_pkg.put_log(' ',1);
2470: per_cagr_utility_pkg.put_log(' Found active entitlement for item: '||v_ents.item_name,1);
2471: end if;
2472: else
2473: --dbms_output.put_line('first dataitem');
2466: -- store new beneficial_rule
2467: v_beneficial_rule := v_ents.BENEFICIAL_RULE;
2468: v_beneficial_rule_vs_id := v_ents.BENEFICIAL_RULE_VALUE_SET_ID;
2469: per_cagr_utility_pkg.put_log(' ',1);
2470: per_cagr_utility_pkg.put_log(' Found active entitlement for item: '||v_ents.item_name,1);
2471: end if;
2472: else
2473: --dbms_output.put_line('first dataitem');
2474: -- set dataitem and beneficial rule value on first iteration
2474: -- set dataitem and beneficial rule value on first iteration
2475: v_last_dataitem_id := v_ents.CAGR_ENTITLEMENT_ITEM_ID;
2476: v_beneficial_rule := v_ents.BENEFICIAL_RULE;
2477: v_beneficial_rule_vs_id := v_ents.BENEFICIAL_RULE_VALUE_SET_ID;
2478: per_cagr_utility_pkg.put_log(' ',1);
2479: per_cagr_utility_pkg.put_log(' Found active entitlement for item: '||v_ents.item_name,1);
2480: end if;
2481:
2482: v_value := NULL; -- clear result variables before eval
2475: v_last_dataitem_id := v_ents.CAGR_ENTITLEMENT_ITEM_ID;
2476: v_beneficial_rule := v_ents.BENEFICIAL_RULE;
2477: v_beneficial_rule_vs_id := v_ents.BENEFICIAL_RULE_VALUE_SET_ID;
2478: per_cagr_utility_pkg.put_log(' ',1);
2479: per_cagr_utility_pkg.put_log(' Found active entitlement for item: '||v_ents.item_name,1);
2480: end if;
2481:
2482: v_value := NULL; -- clear result variables before eval
2483: v_range_from := NULL;
2491: -- determine whether current record is just entitlement item
2492: -- or entitlement line, and exec ff accordingly...
2493: if v_ents.formula_criteria = 'C' then -- ent line record
2494: if v_ents.OIPL_ID <> 0 and v_ents.eligy_prfl_id <> 0 then
2495: l_source_name := per_cagr_utility_pkg.get_elig_source(v_ents.eligy_prfl_id
2496: ,NULL
2497: ,p_params.effective_date);
2498: else
2499: l_source_name := '*** Default ***';
2497: ,p_params.effective_date);
2498: else
2499: l_source_name := '*** Default ***';
2500: end if;
2501: per_cagr_utility_pkg.put_log(' Evaluating eligibility for criteria line: '||l_source_name,1);
2502: per_cagr_utility_pkg.put_log(' entitlement_id: '||v_ents.cagr_entitlement_id||', entitlement_line_id: '||v_ents.cagr_entitlement_line_id);
2503:
2504: if v_ents.OIPL_ID = 0 and v_ents.eligy_prfl_id = 0 then
2505: -- write the record as this is default elig line
2498: else
2499: l_source_name := '*** Default ***';
2500: end if;
2501: per_cagr_utility_pkg.put_log(' Evaluating eligibility for criteria line: '||l_source_name,1);
2502: per_cagr_utility_pkg.put_log(' entitlement_id: '||v_ents.cagr_entitlement_id||', entitlement_line_id: '||v_ents.cagr_entitlement_line_id);
2503:
2504: if v_ents.OIPL_ID = 0 and v_ents.eligy_prfl_id = 0 then
2505: -- write the record as this is default elig line
2506: v_value := v_ents.value;
2531: end if;
2532: else
2533: -- log error that there are no BEN eligibility result records returned
2534: -- from benmngle, for this compensation_object
2535: per_cagr_utility_pkg.put_log(' ERROR: No eligibility results were generated for the assignment',1);
2536: end if;
2537: end if;
2538:
2539: if v_ents.category_name = 'PYS' and v_write_flag = TRUE then
2540: -- check the asg grade matches the grade_spine grade, as well as elig profile
2541: -- being satisfied, in order to be eligible for this PYS criteria.
2542: if nvl(v_ents.grade_id,-2) <> nvl(get_PYS_grade_id (v_ents.grade_spine_id
2543: ,p_params.effective_date),-1) then
2544: per_cagr_utility_pkg.put_log(' Criteria line is ineligible as the assignment is not on the grade spine. ',1);
2545: v_write_flag := FALSE;
2546: end if;
2547: end if;
2548:
2547: end if;
2548:
2549: elsif v_ents.formula_criteria = 'F' then -- ent record
2550: if v_ents.FORMULA_ID is not null then
2551: per_cagr_utility_pkg.put_log(' entitlement_id: '||v_ents.cagr_entitlement_id);
2552: l_source_name := per_cagr_utility_pkg.get_elig_source(NULL
2553: ,v_ents.FORMULA_ID
2554: ,p_params.effective_date);
2555: per_cagr_utility_pkg.put_log(' Evaluating entitlement fast formula: '||l_source_name,1);
2548:
2549: elsif v_ents.formula_criteria = 'F' then -- ent record
2550: if v_ents.FORMULA_ID is not null then
2551: per_cagr_utility_pkg.put_log(' entitlement_id: '||v_ents.cagr_entitlement_id);
2552: l_source_name := per_cagr_utility_pkg.get_elig_source(NULL
2553: ,v_ents.FORMULA_ID
2554: ,p_params.effective_date);
2555: per_cagr_utility_pkg.put_log(' Evaluating entitlement fast formula: '||l_source_name,1);
2556:
2551: per_cagr_utility_pkg.put_log(' entitlement_id: '||v_ents.cagr_entitlement_id);
2552: l_source_name := per_cagr_utility_pkg.get_elig_source(NULL
2553: ,v_ents.FORMULA_ID
2554: ,p_params.effective_date);
2555: per_cagr_utility_pkg.put_log(' Evaluating entitlement fast formula: '||l_source_name,1);
2556:
2557:
2558: hr_cagr_ff_pkg.cagr_entitlement_ff(p_formula_id => v_ents.FORMULA_ID
2559: ,p_effective_date => p_params.effective_date
2569: v_range_to := l_cagr_FF_record.range_to;
2570: v_write_flag := TRUE;
2571: else
2572: -- log message as the formula evaluated to null and continue with next entitlement record
2573: per_cagr_utility_pkg.put_log(' Fast Formula did not determine an eligible entitlement.',1);
2574: v_write_flag := FALSE;
2575: end if;
2576: elsif v_ents.category_name = 'PYS' then
2577: if l_cagr_FF_record.grade_spine_id is not null
2584: v_to_step_id := l_cagr_FF_record.to_step_id;
2585: v_write_flag := TRUE;
2586: else
2587: -- log message as the formula didn't evaluated to null and continue with next entitlement record
2588: per_cagr_utility_pkg.put_log(' Fast Formula did not determine an eligible entitlement.',1);
2589: end if;
2590: end if;
2591: end if;
2592: end if;
2661:
2662: if v_rule_inconclusive then
2663: -- output warning message that beneficial could not be chosen
2664: -- and write results anyway..
2665: per_cagr_utility_pkg.put_log(' ERROR: Beneficial Rule was inconclusive',1);
2666: end if;
2667: apply_chosen_result(t_results_table,t_chosen_table,p_params.commit_flag);
2668: update_result_set(t_results_table,p_params,'W');
2669: t_results_table.delete;
2669: t_results_table.delete;
2670: v_counter := 0;
2671: end if;
2672: else
2673: per_cagr_utility_pkg.put_log(' No active entitlements found for the collective agreement.',1);
2674: end if;
2675:
2676: -- when not in SE mode, also need to add in any other retained rights for dataitems
2677: -- that are not related to the dataitems returned by the current entitlements
2715: ,p_params.effective_date);
2716: end if;
2717:
2718: if nvl(fnd_profile.value('PER_CHECK_ENTITLEMENT_CACHE'),'N') = 'Y' and v_primary_flag then
2719: per_cagr_utility_pkg.put_log(' Profile value set to check entitlement cache before evaluating');
2720:
2721: -- check the cache
2722: p_SE_rec := check_cache(p_params.assignment_id
2723: ,per_cagr_utility_pkg.get_collective_agreement_id(p_params.assignment_id,p_params.effective_date)
2719: per_cagr_utility_pkg.put_log(' Profile value set to check entitlement cache before evaluating');
2720:
2721: -- check the cache
2722: p_SE_rec := check_cache(p_params.assignment_id
2723: ,per_cagr_utility_pkg.get_collective_agreement_id(p_params.assignment_id,p_params.effective_date)
2724: ,p_params.entitlement_item_id
2725: ,p_params.effective_date);
2726:
2727: l_cache_checked := TRUE;
2731: l_evaluate := TRUE;
2732: end if;
2733: else
2734: p_SE_rec.error := 'HR_289577_CAGR_NO_DATA_FOUND';
2735: per_cagr_utility_pkg.put_log(' Profile value set to always re-evaluate');
2736: l_evaluate := TRUE;
2737: end if;
2738:
2739:
2762: ,p_eligibility_table => t_eligibility_table
2763: ,p_counter => v_eligibility_counter);
2764:
2765: else
2766: per_cagr_utility_pkg.put_log(' No active entitlement lines exist for collective agreement');
2767: close csr_SE_drive_benmngle;
2768: end if;
2769:
2770: -- open the cursor, to get single entitlement data
2786: v_to_step_id := NULL;
2787:
2788: -- determine whether current record is entitlement item (so run ff) or entitlement line
2789: if v_ents.formula_criteria = 'C' then -- line item record
2790: per_cagr_utility_pkg.put_log(' Processing entitlement: '||v_ents.cagr_entitlement_id||' '||v_ents.item_name
2791: ||', entitlement line: '||v_ents.cagr_entitlement_line_id);
2792:
2793: if v_ents.OIPL_ID = 0 and v_ents.eligy_prfl_id = 0 then
2794: -- write the record as this is default elig line
2819: v_write_flag := TRUE;
2820: end if;
2821: else
2822: -- log error that there are no BEN eligibility result records returned by benmngle
2823: per_cagr_utility_pkg.put_log(' ERROR: No eligibility results were generated for the assignment',1);
2824: end if;
2825: end if;
2826:
2827: if v_ents.category_name = 'PYS' and v_write_flag = TRUE then
2828: -- check the asg grade matches the grade_spine grade, as well as elig profile
2829: -- being satisfied, in order to be eligible for this PYS criteria.
2830: if nvl(v_ents.grade_id,-2) <> nvl(get_PYS_grade_id (v_ents.grade_spine_id
2831: ,p_params.effective_date),-1) then
2832: per_cagr_utility_pkg.put_log(' Criteria line is ineligible as the assignment is not on the grade spine. ',1);
2833: v_write_flag := FALSE;
2834: end if;
2835: end if;
2836:
2835: end if;
2836:
2837: elsif v_ents.formula_criteria = 'F' then -- item record
2838: if v_ents.FORMULA_ID is not null then
2839: per_cagr_utility_pkg.put_log(' Processing entitlement: '||v_ents.cagr_entitlement_id||' '
2840: ||v_ents.item_name||', calling ff');
2841:
2842: hr_cagr_ff_pkg.cagr_entitlement_ff(p_formula_id => v_ents.FORMULA_ID
2843: ,p_effective_date => p_params.effective_date
2853: v_range_to := l_cagr_FF_record.range_to;
2854: v_write_flag := TRUE;
2855: else
2856: -- log message as the formula evaluated to null and continue with next entitlement record
2857: per_cagr_utility_pkg.put_log(' Fast Formula did not determine an eligible entitlement.',1);
2858: v_write_flag := FALSE;
2859: end if;
2860: elsif v_ents.category_name = 'PYS' then
2861: if l_cagr_FF_record.grade_spine_id is not null
2868: v_to_step_id := l_cagr_FF_record.to_step_id;
2869: v_write_flag := TRUE;
2870: else
2871: -- log message as the formula didn't evaluated to null and continue with next entitlement record
2872: per_cagr_utility_pkg.put_log(' Fast Formula did not determine an eligible entitlement.',1);
2873: end if;
2874: end if;
2875: end if;
2876: end if;
2936:
2937: if not(l_cache_checked) then
2938: -- check the cache, if it wasn't done above
2939: p_SE_rec := check_cache(p_params.assignment_id
2940: ,per_cagr_utility_pkg.get_collective_agreement_id(p_params.assignment_id,p_params.effective_date)
2941: ,p_params.entitlement_item_id
2942: ,p_params.effective_date);
2943: end if;
2944:
2963: p_SE_rec.ERROR := NULL; -- do not return this error
2964: end if;
2965: EXCEPTION
2966: when resource_busy then
2967: per_cagr_utility_pkg.put_log(' WARNING: unable to obtain exclusive lock on per_cagr_entitlement_results');
2968: per_cagr_utility_pkg.put_log(' Cache was not updated with results, continuing...');
2969: -- but this is not fatal in this mode, so continue and pass out results
2970: END;
2971: else -- not updating the cache, but we have regenerated data so nullify this error now
2964: end if;
2965: EXCEPTION
2966: when resource_busy then
2967: per_cagr_utility_pkg.put_log(' WARNING: unable to obtain exclusive lock on per_cagr_entitlement_results');
2968: per_cagr_utility_pkg.put_log(' Cache was not updated with results, continuing...');
2969: -- but this is not fatal in this mode, so continue and pass out results
2970: END;
2971: else -- not updating the cache, but we have regenerated data so nullify this error now
2972: if p_SE_rec.error = 'HR_289577_CAGR_NO_DATA_FOUND' and t_results_table.count > 0 then
3010: -- return error if called for secondary asg
3011: if not(v_primary_flag) then
3012: p_SE_rec.ERROR := 'HR_289579_CAGR_SECONDARY_ASG';
3013: end if;
3014: per_cagr_utility_pkg.put_log(' Single Entitlement Mode return values: ');
3015:
3016: -- log return values...
3017: per_cagr_utility_pkg.put_log(' VALUE: '|| p_SE_rec.VALUE);
3018: per_cagr_utility_pkg.put_log(' RANGE_FROM: '|| p_SE_rec.RANGE_FROM);
3013: end if;
3014: per_cagr_utility_pkg.put_log(' Single Entitlement Mode return values: ');
3015:
3016: -- log return values...
3017: per_cagr_utility_pkg.put_log(' VALUE: '|| p_SE_rec.VALUE);
3018: per_cagr_utility_pkg.put_log(' RANGE_FROM: '|| p_SE_rec.RANGE_FROM);
3019: per_cagr_utility_pkg.put_log(' RANGE_TO: '|| p_SE_rec.RANGE_TO);
3020: per_cagr_utility_pkg.put_log(' GRADE_SPINE_ID: '|| p_SE_rec.GRADE_SPINE_ID);
3021: per_cagr_utility_pkg.put_log(' PARENT_SPINE_ID: '|| p_SE_rec.PARENT_SPINE_ID);
3014: per_cagr_utility_pkg.put_log(' Single Entitlement Mode return values: ');
3015:
3016: -- log return values...
3017: per_cagr_utility_pkg.put_log(' VALUE: '|| p_SE_rec.VALUE);
3018: per_cagr_utility_pkg.put_log(' RANGE_FROM: '|| p_SE_rec.RANGE_FROM);
3019: per_cagr_utility_pkg.put_log(' RANGE_TO: '|| p_SE_rec.RANGE_TO);
3020: per_cagr_utility_pkg.put_log(' GRADE_SPINE_ID: '|| p_SE_rec.GRADE_SPINE_ID);
3021: per_cagr_utility_pkg.put_log(' PARENT_SPINE_ID: '|| p_SE_rec.PARENT_SPINE_ID);
3022: per_cagr_utility_pkg.put_log(' STEP_ID: '|| p_SE_rec.STEP_ID);
3015:
3016: -- log return values...
3017: per_cagr_utility_pkg.put_log(' VALUE: '|| p_SE_rec.VALUE);
3018: per_cagr_utility_pkg.put_log(' RANGE_FROM: '|| p_SE_rec.RANGE_FROM);
3019: per_cagr_utility_pkg.put_log(' RANGE_TO: '|| p_SE_rec.RANGE_TO);
3020: per_cagr_utility_pkg.put_log(' GRADE_SPINE_ID: '|| p_SE_rec.GRADE_SPINE_ID);
3021: per_cagr_utility_pkg.put_log(' PARENT_SPINE_ID: '|| p_SE_rec.PARENT_SPINE_ID);
3022: per_cagr_utility_pkg.put_log(' STEP_ID: '|| p_SE_rec.STEP_ID);
3023: per_cagr_utility_pkg.put_log(' FROM_STEP_ID: '|| p_SE_rec.FROM_STEP_ID);
3016: -- log return values...
3017: per_cagr_utility_pkg.put_log(' VALUE: '|| p_SE_rec.VALUE);
3018: per_cagr_utility_pkg.put_log(' RANGE_FROM: '|| p_SE_rec.RANGE_FROM);
3019: per_cagr_utility_pkg.put_log(' RANGE_TO: '|| p_SE_rec.RANGE_TO);
3020: per_cagr_utility_pkg.put_log(' GRADE_SPINE_ID: '|| p_SE_rec.GRADE_SPINE_ID);
3021: per_cagr_utility_pkg.put_log(' PARENT_SPINE_ID: '|| p_SE_rec.PARENT_SPINE_ID);
3022: per_cagr_utility_pkg.put_log(' STEP_ID: '|| p_SE_rec.STEP_ID);
3023: per_cagr_utility_pkg.put_log(' FROM_STEP_ID: '|| p_SE_rec.FROM_STEP_ID);
3024: per_cagr_utility_pkg.put_log(' TO_STEP_ID: '|| p_SE_rec.TO_STEP_ID);
3017: per_cagr_utility_pkg.put_log(' VALUE: '|| p_SE_rec.VALUE);
3018: per_cagr_utility_pkg.put_log(' RANGE_FROM: '|| p_SE_rec.RANGE_FROM);
3019: per_cagr_utility_pkg.put_log(' RANGE_TO: '|| p_SE_rec.RANGE_TO);
3020: per_cagr_utility_pkg.put_log(' GRADE_SPINE_ID: '|| p_SE_rec.GRADE_SPINE_ID);
3021: per_cagr_utility_pkg.put_log(' PARENT_SPINE_ID: '|| p_SE_rec.PARENT_SPINE_ID);
3022: per_cagr_utility_pkg.put_log(' STEP_ID: '|| p_SE_rec.STEP_ID);
3023: per_cagr_utility_pkg.put_log(' FROM_STEP_ID: '|| p_SE_rec.FROM_STEP_ID);
3024: per_cagr_utility_pkg.put_log(' TO_STEP_ID: '|| p_SE_rec.TO_STEP_ID);
3025: per_cagr_utility_pkg.put_log(' ERROR: '|| p_SE_rec.ERROR);
3018: per_cagr_utility_pkg.put_log(' RANGE_FROM: '|| p_SE_rec.RANGE_FROM);
3019: per_cagr_utility_pkg.put_log(' RANGE_TO: '|| p_SE_rec.RANGE_TO);
3020: per_cagr_utility_pkg.put_log(' GRADE_SPINE_ID: '|| p_SE_rec.GRADE_SPINE_ID);
3021: per_cagr_utility_pkg.put_log(' PARENT_SPINE_ID: '|| p_SE_rec.PARENT_SPINE_ID);
3022: per_cagr_utility_pkg.put_log(' STEP_ID: '|| p_SE_rec.STEP_ID);
3023: per_cagr_utility_pkg.put_log(' FROM_STEP_ID: '|| p_SE_rec.FROM_STEP_ID);
3024: per_cagr_utility_pkg.put_log(' TO_STEP_ID: '|| p_SE_rec.TO_STEP_ID);
3025: per_cagr_utility_pkg.put_log(' ERROR: '|| p_SE_rec.ERROR);
3026: --
3019: per_cagr_utility_pkg.put_log(' RANGE_TO: '|| p_SE_rec.RANGE_TO);
3020: per_cagr_utility_pkg.put_log(' GRADE_SPINE_ID: '|| p_SE_rec.GRADE_SPINE_ID);
3021: per_cagr_utility_pkg.put_log(' PARENT_SPINE_ID: '|| p_SE_rec.PARENT_SPINE_ID);
3022: per_cagr_utility_pkg.put_log(' STEP_ID: '|| p_SE_rec.STEP_ID);
3023: per_cagr_utility_pkg.put_log(' FROM_STEP_ID: '|| p_SE_rec.FROM_STEP_ID);
3024: per_cagr_utility_pkg.put_log(' TO_STEP_ID: '|| p_SE_rec.TO_STEP_ID);
3025: per_cagr_utility_pkg.put_log(' ERROR: '|| p_SE_rec.ERROR);
3026: --
3027: --
3020: per_cagr_utility_pkg.put_log(' GRADE_SPINE_ID: '|| p_SE_rec.GRADE_SPINE_ID);
3021: per_cagr_utility_pkg.put_log(' PARENT_SPINE_ID: '|| p_SE_rec.PARENT_SPINE_ID);
3022: per_cagr_utility_pkg.put_log(' STEP_ID: '|| p_SE_rec.STEP_ID);
3023: per_cagr_utility_pkg.put_log(' FROM_STEP_ID: '|| p_SE_rec.FROM_STEP_ID);
3024: per_cagr_utility_pkg.put_log(' TO_STEP_ID: '|| p_SE_rec.TO_STEP_ID);
3025: per_cagr_utility_pkg.put_log(' ERROR: '|| p_SE_rec.ERROR);
3026: --
3027: --
3028: elsif p_params.operation_mode = 'SC' then
3021: per_cagr_utility_pkg.put_log(' PARENT_SPINE_ID: '|| p_SE_rec.PARENT_SPINE_ID);
3022: per_cagr_utility_pkg.put_log(' STEP_ID: '|| p_SE_rec.STEP_ID);
3023: per_cagr_utility_pkg.put_log(' FROM_STEP_ID: '|| p_SE_rec.FROM_STEP_ID);
3024: per_cagr_utility_pkg.put_log(' TO_STEP_ID: '|| p_SE_rec.TO_STEP_ID);
3025: per_cagr_utility_pkg.put_log(' ERROR: '|| p_SE_rec.ERROR);
3026: --
3027: --
3028: elsif p_params.operation_mode = 'SC' then
3029: --
3059: ,p_eligibility_table => t_eligibility_table
3060: ,p_counter => v_eligibility_counter);
3061:
3062: else
3063: per_cagr_utility_pkg.put_log(' No assignments use the collective agreement',1);
3064: per_cagr_utility_pkg.put_log(' or no active criteria lines found for the collective agreement.',1);
3065: close csr_SC_drive_benmngle;
3066: end if;
3067:
3060: ,p_counter => v_eligibility_counter);
3061:
3062: else
3063: per_cagr_utility_pkg.put_log(' No assignments use the collective agreement',1);
3064: per_cagr_utility_pkg.put_log(' or no active criteria lines found for the collective agreement.',1);
3065: close csr_SC_drive_benmngle;
3066: end if;
3067:
3068: per_cagr_utility_pkg.put_log('Processing the following assignments on the collective agreement: ',1);
3064: per_cagr_utility_pkg.put_log(' or no active criteria lines found for the collective agreement.',1);
3065: close csr_SC_drive_benmngle;
3066: end if;
3067:
3068: per_cagr_utility_pkg.put_log('Processing the following assignments on the collective agreement: ',1);
3069: --
3070: -- load all the assignment ids to be processed into pl/sql table.
3071: --
3072: open csr_assignments_to_process;
3073: loop
3074: v_counter := v_counter+1;
3075: fetch csr_assignments_to_process into t_assignments_table(v_counter);
3076: exit when csr_assignments_to_process%notfound;
3077: per_cagr_utility_pkg.put_log(' '||t_assignments_table(v_counter).assignment_id,1);
3078: end loop;
3079: close csr_assignments_to_process;
3080: v_counter := 0;
3081:
3079: close csr_assignments_to_process;
3080: v_counter := 0;
3081:
3082: -- write the log out and save the request_id
3083: per_cagr_utility_pkg.write_log_file(p_params.cagr_request_id);
3084: l_parent_request_id := p_params.cagr_request_id;
3085:
3086: -- could now break pl/sql table into varray subsets, ready for multiple threads
3087:
3097: -- 2) clean results cache
3098: -- 2) do SA style cursor processing and logging (in a function for the SA code)
3099: -- 3) commit AFTER each assignment
3100:
3101: per_cagr_utility_pkg.create_cagr_request(p_process_date => p_params.effective_date
3102: ,p_operation_mode => 'SA'
3103: ,p_business_group_id => p_params.business_group_id
3104: ,p_assignment_id => t_assignments_table(k).assignment_id
3105: ,p_assignment_set_id => NULL
3112: ,p_commit_flag => p_params.commit_flag
3113: ,p_denormalise_flag => p_params.denormalise_flag
3114: ,p_cagr_request_id => p_params.cagr_request_id);
3115: -- output log header
3116: per_cagr_utility_pkg.put_log(g_head_separator,1);
3117: per_cagr_utility_pkg.put_log('----------- Collective Agreement Process Log ('||fnd_date.date_to_canonical(sysdate)||') -----------',1);
3118: per_cagr_utility_pkg.put_log(g_head_separator,1);
3119: per_cagr_utility_pkg.put_log(' ',1);
3120: per_cagr_utility_pkg.put_log(' Evaluating Assignment '|| t_assignments_table(k).assignment_id ||
3113: ,p_denormalise_flag => p_params.denormalise_flag
3114: ,p_cagr_request_id => p_params.cagr_request_id);
3115: -- output log header
3116: per_cagr_utility_pkg.put_log(g_head_separator,1);
3117: per_cagr_utility_pkg.put_log('----------- Collective Agreement Process Log ('||fnd_date.date_to_canonical(sysdate)||') -----------',1);
3118: per_cagr_utility_pkg.put_log(g_head_separator,1);
3119: per_cagr_utility_pkg.put_log(' ',1);
3120: per_cagr_utility_pkg.put_log(' Evaluating Assignment '|| t_assignments_table(k).assignment_id ||
3121: ' during Single Collective Agreement mode.',1);
3114: ,p_cagr_request_id => p_params.cagr_request_id);
3115: -- output log header
3116: per_cagr_utility_pkg.put_log(g_head_separator,1);
3117: per_cagr_utility_pkg.put_log('----------- Collective Agreement Process Log ('||fnd_date.date_to_canonical(sysdate)||') -----------',1);
3118: per_cagr_utility_pkg.put_log(g_head_separator,1);
3119: per_cagr_utility_pkg.put_log(' ',1);
3120: per_cagr_utility_pkg.put_log(' Evaluating Assignment '|| t_assignments_table(k).assignment_id ||
3121: ' during Single Collective Agreement mode.',1);
3122: per_cagr_utility_pkg.put_log(' Parent SC mode request id is: '||l_parent_request_id);
3115: -- output log header
3116: per_cagr_utility_pkg.put_log(g_head_separator,1);
3117: per_cagr_utility_pkg.put_log('----------- Collective Agreement Process Log ('||fnd_date.date_to_canonical(sysdate)||') -----------',1);
3118: per_cagr_utility_pkg.put_log(g_head_separator,1);
3119: per_cagr_utility_pkg.put_log(' ',1);
3120: per_cagr_utility_pkg.put_log(' Evaluating Assignment '|| t_assignments_table(k).assignment_id ||
3121: ' during Single Collective Agreement mode.',1);
3122: per_cagr_utility_pkg.put_log(' Parent SC mode request id is: '||l_parent_request_id);
3123: per_cagr_utility_pkg.put_log(' SA mode request id is: '||p_params.cagr_request_id);
3116: per_cagr_utility_pkg.put_log(g_head_separator,1);
3117: per_cagr_utility_pkg.put_log('----------- Collective Agreement Process Log ('||fnd_date.date_to_canonical(sysdate)||') -----------',1);
3118: per_cagr_utility_pkg.put_log(g_head_separator,1);
3119: per_cagr_utility_pkg.put_log(' ',1);
3120: per_cagr_utility_pkg.put_log(' Evaluating Assignment '|| t_assignments_table(k).assignment_id ||
3121: ' during Single Collective Agreement mode.',1);
3122: per_cagr_utility_pkg.put_log(' Parent SC mode request id is: '||l_parent_request_id);
3123: per_cagr_utility_pkg.put_log(' SA mode request id is: '||p_params.cagr_request_id);
3124: per_cagr_utility_pkg.put_log(' ',1);
3118: per_cagr_utility_pkg.put_log(g_head_separator,1);
3119: per_cagr_utility_pkg.put_log(' ',1);
3120: per_cagr_utility_pkg.put_log(' Evaluating Assignment '|| t_assignments_table(k).assignment_id ||
3121: ' during Single Collective Agreement mode.',1);
3122: per_cagr_utility_pkg.put_log(' Parent SC mode request id is: '||l_parent_request_id);
3123: per_cagr_utility_pkg.put_log(' SA mode request id is: '||p_params.cagr_request_id);
3124: per_cagr_utility_pkg.put_log(' ',1);
3125:
3126: If p_params.commit_flag = 'Y' then
3119: per_cagr_utility_pkg.put_log(' ',1);
3120: per_cagr_utility_pkg.put_log(' Evaluating Assignment '|| t_assignments_table(k).assignment_id ||
3121: ' during Single Collective Agreement mode.',1);
3122: per_cagr_utility_pkg.put_log(' Parent SC mode request id is: '||l_parent_request_id);
3123: per_cagr_utility_pkg.put_log(' SA mode request id is: '||p_params.cagr_request_id);
3124: per_cagr_utility_pkg.put_log(' ',1);
3125:
3126: If p_params.commit_flag = 'Y' then
3127: -- first create pl/sql table with chosen results for all items for the asg from cache, if committing.
3120: per_cagr_utility_pkg.put_log(' Evaluating Assignment '|| t_assignments_table(k).assignment_id ||
3121: ' during Single Collective Agreement mode.',1);
3122: per_cagr_utility_pkg.put_log(' Parent SC mode request id is: '||l_parent_request_id);
3123: per_cagr_utility_pkg.put_log(' SA mode request id is: '||p_params.cagr_request_id);
3124: per_cagr_utility_pkg.put_log(' ',1);
3125:
3126: If p_params.commit_flag = 'Y' then
3127: -- first create pl/sql table with chosen results for all items for the asg from cache, if committing.
3128: t_chosen_table := store_chosen_results(p_params.assignment_id
3176:
3177: if v_rule_inconclusive then
3178: -- output warning message that beneficial could not be chosen
3179: -- and write results anyway..
3180: per_cagr_utility_pkg.put_log(' ERROR: Beneficial Rule was inconclusive',1);
3181: end if;
3182: apply_chosen_result(t_results_table,t_chosen_table,p_params.commit_flag);
3183: update_result_set(t_results_table,p_params,'W');
3184: v_counter := 0;
3188: v_last_dataitem_id := v_ents.CAGR_ENTITLEMENT_ITEM_ID;
3189: -- store new beneficial_rule
3190: v_beneficial_rule := v_ents.BENEFICIAL_RULE;
3191: v_beneficial_rule_vs_id := v_ents.BENEFICIAL_RULE_VALUE_SET_ID;
3192: per_cagr_utility_pkg.put_log(' ',1);
3193: per_cagr_utility_pkg.put_log(' Found active entitlement for item: '||v_ents.item_name,1);
3194: end if;
3195: else
3196: --dbms_output.put_line('first dataitem');
3189: -- store new beneficial_rule
3190: v_beneficial_rule := v_ents.BENEFICIAL_RULE;
3191: v_beneficial_rule_vs_id := v_ents.BENEFICIAL_RULE_VALUE_SET_ID;
3192: per_cagr_utility_pkg.put_log(' ',1);
3193: per_cagr_utility_pkg.put_log(' Found active entitlement for item: '||v_ents.item_name,1);
3194: end if;
3195: else
3196: --dbms_output.put_line('first dataitem');
3197: -- set dataitem and beneficial rule value on first iteration
3197: -- set dataitem and beneficial rule value on first iteration
3198: v_last_dataitem_id := v_ents.CAGR_ENTITLEMENT_ITEM_ID;
3199: v_beneficial_rule := v_ents.BENEFICIAL_RULE;
3200: v_beneficial_rule_vs_id := v_ents.BENEFICIAL_RULE_VALUE_SET_ID;
3201: per_cagr_utility_pkg.put_log(' ',1);
3202: per_cagr_utility_pkg.put_log(' Found active entitlement for item: '||v_ents.item_name,1);
3203: end if;
3204:
3205: v_value := NULL; -- clear result variables before eval
3198: v_last_dataitem_id := v_ents.CAGR_ENTITLEMENT_ITEM_ID;
3199: v_beneficial_rule := v_ents.BENEFICIAL_RULE;
3200: v_beneficial_rule_vs_id := v_ents.BENEFICIAL_RULE_VALUE_SET_ID;
3201: per_cagr_utility_pkg.put_log(' ',1);
3202: per_cagr_utility_pkg.put_log(' Found active entitlement for item: '||v_ents.item_name,1);
3203: end if;
3204:
3205: v_value := NULL; -- clear result variables before eval
3206: v_range_from := NULL;
3214: -- determine whether current record is just entitlement item
3215: -- or entitlement line, and exec ff accordingly...
3216: if v_ents.formula_criteria = 'C' then -- ent line record
3217: if v_ents.OIPL_ID <> 0 and v_ents.eligy_prfl_id <> 0 then
3218: l_source_name := per_cagr_utility_pkg.get_elig_source(v_ents.eligy_prfl_id
3219: ,NULL
3220: ,p_params.effective_date);
3221: else
3222: l_source_name := '*** Default ***';
3220: ,p_params.effective_date);
3221: else
3222: l_source_name := '*** Default ***';
3223: end if;
3224: per_cagr_utility_pkg.put_log(' Evaluating eligibility for criteria line: '||l_source_name,1);
3225: per_cagr_utility_pkg.put_log(' entitlement_id: '||v_ents.cagr_entitlement_id||', entitlement_line_id: '
3226: ||v_ents.cagr_entitlement_line_id);
3227:
3228: if v_ents.OIPL_ID = 0 and v_ents.eligy_prfl_id = 0 then
3221: else
3222: l_source_name := '*** Default ***';
3223: end if;
3224: per_cagr_utility_pkg.put_log(' Evaluating eligibility for criteria line: '||l_source_name,1);
3225: per_cagr_utility_pkg.put_log(' entitlement_id: '||v_ents.cagr_entitlement_id||', entitlement_line_id: '
3226: ||v_ents.cagr_entitlement_line_id);
3227:
3228: if v_ents.OIPL_ID = 0 and v_ents.eligy_prfl_id = 0 then
3229: -- write the record as this is default elig line
3256: end if;
3257: else
3258: -- log error that there are no BEN eligibility result records returned
3259: -- from benmngle, for this compensation_object
3260: per_cagr_utility_pkg.put_log(' ERROR: No eligibility results were generated for the assignment',1);
3261: end if;
3262: end if;
3263: if v_ents.category_name = 'PYS' and v_write_flag = TRUE then
3264: -- check the asg grade matches the grade_spine grade, as well as elig profile
3264: -- check the asg grade matches the grade_spine grade, as well as elig profile
3265: -- being satisfied, in order to be eligible for this PYS criteria.
3266: if nvl(t_assignments_table(k).grade_id,-2) <> nvl(get_PYS_grade_id (v_ents.grade_spine_id
3267: ,p_params.effective_date),-1) then
3268: per_cagr_utility_pkg.put_log(' Criteria line is ineligible as the assignment is not on the grade spi
3269: ne. ',1);
3270: v_write_flag := FALSE;
3271: end if;
3272: end if;
3272: end if;
3273:
3274: elsif v_ents.formula_criteria = 'F' then -- ent record
3275: if v_ents.FORMULA_ID is not null then
3276: per_cagr_utility_pkg.put_log(' entitlement_id: '||v_ents.cagr_entitlement_id);
3277: l_source_name := per_cagr_utility_pkg.get_elig_source(NULL
3278: ,v_ents.FORMULA_ID
3279: ,p_params.effective_date);
3280: per_cagr_utility_pkg.put_log(' Evaluating entitlement fast formula: '||l_source_name,1);
3273:
3274: elsif v_ents.formula_criteria = 'F' then -- ent record
3275: if v_ents.FORMULA_ID is not null then
3276: per_cagr_utility_pkg.put_log(' entitlement_id: '||v_ents.cagr_entitlement_id);
3277: l_source_name := per_cagr_utility_pkg.get_elig_source(NULL
3278: ,v_ents.FORMULA_ID
3279: ,p_params.effective_date);
3280: per_cagr_utility_pkg.put_log(' Evaluating entitlement fast formula: '||l_source_name,1);
3281:
3276: per_cagr_utility_pkg.put_log(' entitlement_id: '||v_ents.cagr_entitlement_id);
3277: l_source_name := per_cagr_utility_pkg.get_elig_source(NULL
3278: ,v_ents.FORMULA_ID
3279: ,p_params.effective_date);
3280: per_cagr_utility_pkg.put_log(' Evaluating entitlement fast formula: '||l_source_name,1);
3281:
3282:
3283: hr_cagr_ff_pkg.cagr_entitlement_ff(p_formula_id => v_ents.FORMULA_ID
3284: ,p_effective_date => p_params.effective_date
3294: v_range_to := l_cagr_FF_record.range_to;
3295: v_write_flag := TRUE;
3296: else
3297: -- log message as the formula evaluated to null and continue with next entitlement record
3298: per_cagr_utility_pkg.put_log(' Fast Formula did not determine an eligible entitlement.',1);
3299: v_write_flag := FALSE;
3300: end if;
3301: elsif v_ents.category_name = 'PYS' then
3302: if l_cagr_FF_record.grade_spine_id is not null
3309: v_to_step_id := l_cagr_FF_record.to_step_id;
3310: v_write_flag := TRUE;
3311: else
3312: -- log message as the formula didn't evaluated to null and continue with next entitlement record
3313: per_cagr_utility_pkg.put_log(' Fast Formula did not determine an eligible entitlement.',1);
3314: end if;
3315: end if;
3316: end if;
3317: end if;
3385:
3386: if v_rule_inconclusive then
3387: -- output warning message that beneficial could not be chosen
3388: -- and write results anyway..
3389: per_cagr_utility_pkg.put_log(' ERROR: Beneficial Rule was inconclusive',1);
3390: end if;
3391: apply_chosen_result(t_results_table,t_chosen_table,p_params.commit_flag);
3392: update_result_set(t_results_table,p_params,'W');
3393: t_results_table.delete;
3393: t_results_table.delete;
3394: v_counter := 0;
3395: end if;
3396: else
3397: per_cagr_utility_pkg.put_log(' No active entitlements found for the collective agreement.',1);
3398: end if;
3399:
3400: -- add in any other retained rights for dataitems
3401: -- that are not related to the dataitems returned by the current entitlements
3407: t_chosen_table.DELETE;
3408:
3409: -- reset write header flag
3410:
3411: per_cagr_utility_pkg.put_log(' ',1);
3412: per_cagr_utility_pkg.put_log(' Completed Processing assignment',1);
3413: per_cagr_utility_pkg.put_log(' ',1);
3414:
3415: --
3408:
3409: -- reset write header flag
3410:
3411: per_cagr_utility_pkg.put_log(' ',1);
3412: per_cagr_utility_pkg.put_log(' Completed Processing assignment',1);
3413: per_cagr_utility_pkg.put_log(' ',1);
3414:
3415: --
3416: -- Commit, if required, after every assignment.
3409: -- reset write header flag
3410:
3411: per_cagr_utility_pkg.put_log(' ',1);
3412: per_cagr_utility_pkg.put_log(' Completed Processing assignment',1);
3413: per_cagr_utility_pkg.put_log(' ',1);
3414:
3415: --
3416: -- Commit, if required, after every assignment.
3417: --
3416: -- Commit, if required, after every assignment.
3417: --
3418: if p_params.commit_flag = 'Y' then
3419: commit;
3420: per_cagr_utility_pkg.put_log(' Any changes have been saved.',1);
3421: elsif p_params.commit_flag = 'N' then
3422: rollback;
3423: per_cagr_utility_pkg.put_log(' Any changes have been discarded.',1);
3424: end if;
3419: commit;
3420: per_cagr_utility_pkg.put_log(' Any changes have been saved.',1);
3421: elsif p_params.commit_flag = 'N' then
3422: rollback;
3423: per_cagr_utility_pkg.put_log(' Any changes have been discarded.',1);
3424: end if;
3425:
3426: -- write the log file for this assignment
3427: per_cagr_utility_pkg.write_log_file(p_params.cagr_request_id);
3423: per_cagr_utility_pkg.put_log(' Any changes have been discarded.',1);
3424: end if;
3425:
3426: -- write the log file for this assignment
3427: per_cagr_utility_pkg.write_log_file(p_params.cagr_request_id);
3428:
3429: Exception
3430: WHEN OTHERS THEN
3431: -- write the log file for this assignment
3428:
3429: Exception
3430: WHEN OTHERS THEN
3431: -- write the log file for this assignment
3432: per_cagr_utility_pkg.write_log_file(p_params.cagr_request_id);
3433: -- reset to the parent request id and assignment
3434: p_params.cagr_request_id := l_parent_request_id;
3435: p_params.assignment_id := NULL;
3436: rollback;
3468: close csr_BE_plan;
3469:
3470: If l_opt_id is not null then
3471: -- start benmngle
3472: per_cagr_utility_pkg.put_log('Starting benmngle');
3473: process_entitlement_lines(p_pl_id => l_pl_id -- may run for a cagr
3474: ,p_opt_id => l_opt_id -- always run for an iteme
3475: ,p_person_id => NULL -- for all people on the cagr(s)
3476: ,p_benefit_action_id => v_benefit_action_id
3482: ,p_eligibility_table => t_eligibility_table
3483: ,p_counter => v_eligibility_counter);
3484:
3485: else
3486: per_cagr_utility_pkg.put_log(' No active criteria lines found for the item on any collective agreement.',1);
3487: end if;
3488: per_cagr_utility_pkg.put_log('Processing the following collective agreements that have entitlements using the item: ',1);
3489:
3490: --
3484:
3485: else
3486: per_cagr_utility_pkg.put_log(' No active criteria lines found for the item on any collective agreement.',1);
3487: end if;
3488: per_cagr_utility_pkg.put_log('Processing the following collective agreements that have entitlements using the item: ',1);
3489:
3490: --
3491: -- load all the cagrs and their assignments ids to be processed into pl/sql table.
3492: --
3496: fetch csr_BE_assignments_to_process into t_cagr_assignments_table(v_counter);
3497: exit when csr_BE_assignments_to_process%notfound;
3498: If t_cagr_assignments_table(v_counter).collective_agreement_id <> l_last_cagr_id then
3499: l_last_cagr_id := t_cagr_assignments_table(v_counter).collective_agreement_id;
3500: per_cagr_utility_pkg.put_log(' '||t_cagr_assignments_table(v_counter).collective_agreement_id,1);
3501: End If;
3502: end loop;
3503: close csr_BE_assignments_to_process;
3504: v_counter := 0;
3503: close csr_BE_assignments_to_process;
3504: v_counter := 0;
3505:
3506: -- write the log out
3507: per_cagr_utility_pkg.write_log_file(p_params.cagr_request_id);
3508:
3509: -- could now break pl/sql table into varray subsets, ready for multiple threads
3510:
3511: -- loop through table of assignment_id for each cagr
3520: -- 4) commit AFTER each assignment
3521:
3522: p_params.assignment_id := t_cagr_assignments_table(k).assignment_id;
3523:
3524: per_cagr_utility_pkg.put_log(' ',1);
3525: per_cagr_utility_pkg.put_log(' Evaluating Assignment '|| t_cagr_assignments_table(k).assignment_id ||
3526: ' on collective agreement '|| t_cagr_assignments_table(k).collective_agreement_id ||
3527: ' during Batch Entitlement mode.',1);
3528: per_cagr_utility_pkg.put_log(' ',1);
3521:
3522: p_params.assignment_id := t_cagr_assignments_table(k).assignment_id;
3523:
3524: per_cagr_utility_pkg.put_log(' ',1);
3525: per_cagr_utility_pkg.put_log(' Evaluating Assignment '|| t_cagr_assignments_table(k).assignment_id ||
3526: ' on collective agreement '|| t_cagr_assignments_table(k).collective_agreement_id ||
3527: ' during Batch Entitlement mode.',1);
3528: per_cagr_utility_pkg.put_log(' ',1);
3529:
3524: per_cagr_utility_pkg.put_log(' ',1);
3525: per_cagr_utility_pkg.put_log(' Evaluating Assignment '|| t_cagr_assignments_table(k).assignment_id ||
3526: ' on collective agreement '|| t_cagr_assignments_table(k).collective_agreement_id ||
3527: ' during Batch Entitlement mode.',1);
3528: per_cagr_utility_pkg.put_log(' ',1);
3529:
3530:
3531: -- for each asg on the cagr on the effective_date:
3532:
3549:
3550:
3551: -- determine whether current record is entitlement item (so run ff) or entitlement line
3552: if v_ents.formula_criteria = 'C' then -- line item record
3553: per_cagr_utility_pkg.put_log(' Processing entitlement: '||v_ents.cagr_entitlement_id||' '||v_ents.item_name
3554: ||', entitlement line: '||v_ents.cagr_entitlement_line_id);
3555:
3556: if v_ents.OIPL_ID = 0 and v_ents.eligy_prfl_id = 0 then
3557: -- write the record as this is default elig line
3583: v_write_flag := TRUE;
3584: end if;
3585: else
3586: -- log error that there are no BEN eligibility result records returned by benmngle
3587: per_cagr_utility_pkg.put_log(' ERROR: No eligibility results were generated for the assignment',1);
3588: end if;
3589: end if;
3590:
3591: if v_ents.category_name = 'PYS' and v_write_flag = TRUE then
3592: -- check the asg grade matches the grade_spine grade, as well as elig profile
3593: -- being satisfied, in order to be eligible for this PYS criteria.
3594: if nvl(v_ents.grade_id,-2) <> nvl(get_PYS_grade_id (v_ents.grade_spine_id
3595: ,p_params.effective_date),-1) then
3596: per_cagr_utility_pkg.put_log(' Criteria line is ineligible as the assignment is not on the grade spine. ',1);
3597: v_write_flag := FALSE;
3598: end if;
3599: end if;
3600:
3599: end if;
3600:
3601: elsif v_ents.formula_criteria = 'F' then -- item record
3602: if v_ents.FORMULA_ID is not null then
3603: per_cagr_utility_pkg.put_log(' Processing entitlement: '||v_ents.cagr_entitlement_id||' '
3604: ||v_ents.item_name||', calling ff');
3605:
3606: hr_cagr_ff_pkg.cagr_entitlement_ff(p_formula_id => v_ents.FORMULA_ID
3607: ,p_effective_date => p_params.effective_date
3617: v_range_to := l_cagr_FF_record.range_to;
3618: v_write_flag := TRUE;
3619: else
3620: -- log message as the formula evaluated to null and continue with next entitlement record
3621: per_cagr_utility_pkg.put_log(' Fast Formula did not determine an eligible entitlement.',1);
3622: v_write_flag := FALSE;
3623: end if;
3624: elsif v_ents.category_name = 'PYS' then
3625: if l_cagr_FF_record.grade_spine_id is not null
3632: v_to_step_id := l_cagr_FF_record.to_step_id;
3633: v_write_flag := TRUE;
3634: else
3635: -- log message as the formula didn't evaluated to null and continue with next entitlement record
3636: per_cagr_utility_pkg.put_log(' Fast Formula did not determine an eligible entitlement.',1);
3637: end if;
3638: end if;
3639: end if;
3640: end if;
3726: p_SE_rec.ERROR := NULL; -- do not return this error
3727: end if;
3728: EXCEPTION
3729: WHEN RESOURCE_BUSY THEN
3730: per_cagr_utility_pkg.put_log(' WARNING: unable to obtain exclusive lock on result for assignment:'
3731: ||p_params.assignment_id);
3732: per_cagr_utility_pkg.put_log(' Cache was not updated with results, continuing...');
3733: END;
3734: end if;
3728: EXCEPTION
3729: WHEN RESOURCE_BUSY THEN
3730: per_cagr_utility_pkg.put_log(' WARNING: unable to obtain exclusive lock on result for assignment:'
3731: ||p_params.assignment_id);
3732: per_cagr_utility_pkg.put_log(' Cache was not updated with results, continuing...');
3733: END;
3734: end if;
3735: end if;
3736: end if;
3742:
3743:
3744: -- reset write header flag
3745:
3746: per_cagr_utility_pkg.put_log(' ',1);
3747: per_cagr_utility_pkg.put_log(' Completed Processing assignment',1);
3748: per_cagr_utility_pkg.put_log(' ',1);
3749:
3750: --
3743:
3744: -- reset write header flag
3745:
3746: per_cagr_utility_pkg.put_log(' ',1);
3747: per_cagr_utility_pkg.put_log(' Completed Processing assignment',1);
3748: per_cagr_utility_pkg.put_log(' ',1);
3749:
3750: --
3751: -- Commit, if required, after every assignment.
3744: -- reset write header flag
3745:
3746: per_cagr_utility_pkg.put_log(' ',1);
3747: per_cagr_utility_pkg.put_log(' Completed Processing assignment',1);
3748: per_cagr_utility_pkg.put_log(' ',1);
3749:
3750: --
3751: -- Commit, if required, after every assignment.
3752: --
3751: -- Commit, if required, after every assignment.
3752: --
3753: if p_params.commit_flag = 'Y' then
3754: commit;
3755: per_cagr_utility_pkg.put_log(' Any changes have been saved.',1);
3756: elsif p_params.commit_flag = 'N' then
3757: rollback;
3758: per_cagr_utility_pkg.put_log(' Any changes have been discarded.',1);
3759: end if;
3754: commit;
3755: per_cagr_utility_pkg.put_log(' Any changes have been saved.',1);
3756: elsif p_params.commit_flag = 'N' then
3757: rollback;
3758: per_cagr_utility_pkg.put_log(' Any changes have been discarded.',1);
3759: end if;
3760:
3761: per_cagr_utility_pkg.write_log_file(p_params.cagr_request_id);
3762:
3757: rollback;
3758: per_cagr_utility_pkg.put_log(' Any changes have been discarded.',1);
3759: end if;
3760:
3761: per_cagr_utility_pkg.write_log_file(p_params.cagr_request_id);
3762:
3763: EXCEPTION
3764: WHEN OTHERS THEN
3765: -- write the log file for this assignment
3762:
3763: EXCEPTION
3764: WHEN OTHERS THEN
3765: -- write the log file for this assignment
3766: per_cagr_utility_pkg.write_log_file(p_params.cagr_request_id);
3767: -- reset to the parent request id and assignment
3768: p_params.assignment_id := NULL;
3769: rollback;
3770: raise;
3778: else -- Other modes....
3779: null;
3780: end if;
3781:
3782: per_cagr_utility_pkg.put_log(' ',1);
3783: per_cagr_utility_pkg.put_log('Completed Evaluation Process ('||fnd_date.date_to_canonical(sysdate)||')',1);
3784: per_cagr_utility_pkg.put_log(' ',1);
3785: per_cagr_utility_pkg.write_log_file(p_params.cagr_request_id);
3786: hr_utility.set_location('Leaving:'||l_proc, 100);
3779: null;
3780: end if;
3781:
3782: per_cagr_utility_pkg.put_log(' ',1);
3783: per_cagr_utility_pkg.put_log('Completed Evaluation Process ('||fnd_date.date_to_canonical(sysdate)||')',1);
3784: per_cagr_utility_pkg.put_log(' ',1);
3785: per_cagr_utility_pkg.write_log_file(p_params.cagr_request_id);
3786: hr_utility.set_location('Leaving:'||l_proc, 100);
3787:
3780: end if;
3781:
3782: per_cagr_utility_pkg.put_log(' ',1);
3783: per_cagr_utility_pkg.put_log('Completed Evaluation Process ('||fnd_date.date_to_canonical(sysdate)||')',1);
3784: per_cagr_utility_pkg.put_log(' ',1);
3785: per_cagr_utility_pkg.write_log_file(p_params.cagr_request_id);
3786: hr_utility.set_location('Leaving:'||l_proc, 100);
3787:
3788:
3781:
3782: per_cagr_utility_pkg.put_log(' ',1);
3783: per_cagr_utility_pkg.put_log('Completed Evaluation Process ('||fnd_date.date_to_canonical(sysdate)||')',1);
3784: per_cagr_utility_pkg.put_log(' ',1);
3785: per_cagr_utility_pkg.write_log_file(p_params.cagr_request_id);
3786: hr_utility.set_location('Leaving:'||l_proc, 100);
3787:
3788:
3789: EXCEPTION
3787:
3788:
3789: EXCEPTION
3790: WHEN OTHERS THEN
3791: per_cagr_utility_pkg.write_log_file(p_params.cagr_request_id);
3792: raise;
3793:
3794: END evaluation_process;
3795:
3822:
3823: --
3824: -- store params and cagr return cagr_request_id for use in this run
3825: --
3826: per_cagr_utility_pkg.create_cagr_request(p_process_date => p_process_date
3827: ,p_operation_mode => p_operation_mode
3828: ,p_business_group_id => p_business_group_id
3829: ,p_assignment_id => p_assignment_id
3830: ,p_assignment_set_id => p_assignment_set_id
3840:
3841: --
3842: -- Output log header
3843: --
3844: per_cagr_utility_pkg.put_log(g_head_separator,1);
3845: per_cagr_utility_pkg.put_log('----------- Collective Agreement Process Log ('||fnd_date.date_to_canonical(sysdate)||') -----------',1);
3846: --
3847: -- Ensure that all the mandatory arguments are not null
3848: --
3841: --
3842: -- Output log header
3843: --
3844: per_cagr_utility_pkg.put_log(g_head_separator,1);
3845: per_cagr_utility_pkg.put_log('----------- Collective Agreement Process Log ('||fnd_date.date_to_canonical(sysdate)||') -----------',1);
3846: --
3847: -- Ensure that all the mandatory arguments are not null
3848: --
3849: hr_api.mandatory_arg_error(p_api_name => l_proc
3859:
3860: -- test for required params for modes
3861:
3862: if not(p_operation_mode in ('SA','SE','SC','BE')) then
3863: per_cagr_utility_pkg.log_and_raise_error('HR_289420_CAGR_INV_MODE'
3864: ,p_cagr_request_id);
3865: end if;
3866: if (p_operation_mode = 'SA' and p_assignment_id is null) then
3867: per_cagr_utility_pkg.log_and_raise_error('HR_289421_CAGR_INV_SA_PARAM'
3863: per_cagr_utility_pkg.log_and_raise_error('HR_289420_CAGR_INV_MODE'
3864: ,p_cagr_request_id);
3865: end if;
3866: if (p_operation_mode = 'SA' and p_assignment_id is null) then
3867: per_cagr_utility_pkg.log_and_raise_error('HR_289421_CAGR_INV_SA_PARAM'
3868: ,p_cagr_request_id);
3869: elsif (p_operation_mode = 'SE') and
3870: (p_entitlement_item_id is null or p_assignment_id is null) then
3871: per_cagr_utility_pkg.log_and_raise_error('HR_289422_CAGR_INV_SE_PARAM'
3867: per_cagr_utility_pkg.log_and_raise_error('HR_289421_CAGR_INV_SA_PARAM'
3868: ,p_cagr_request_id);
3869: elsif (p_operation_mode = 'SE') and
3870: (p_entitlement_item_id is null or p_assignment_id is null) then
3871: per_cagr_utility_pkg.log_and_raise_error('HR_289422_CAGR_INV_SE_PARAM'
3872: ,p_cagr_request_id);
3873: elsif (p_operation_mode = 'BE' and (p_entitlement_item_id is null or p_apply_results_flag <> 'N')) then
3874: per_cagr_utility_pkg.log_and_raise_error('HR_289709_CAGR_INV_BE_PARAM'
3875: ,p_cagr_request_id);
3870: (p_entitlement_item_id is null or p_assignment_id is null) then
3871: per_cagr_utility_pkg.log_and_raise_error('HR_289422_CAGR_INV_SE_PARAM'
3872: ,p_cagr_request_id);
3873: elsif (p_operation_mode = 'BE' and (p_entitlement_item_id is null or p_apply_results_flag <> 'N')) then
3874: per_cagr_utility_pkg.log_and_raise_error('HR_289709_CAGR_INV_BE_PARAM'
3875: ,p_cagr_request_id);
3876: elsif (p_operation_mode = 'SC' and p_collective_agreement_id is null) then
3877: per_cagr_utility_pkg.log_and_raise_error('HR_289597_INV_SC_PARAM'
3878: ,p_cagr_request_id);
3873: elsif (p_operation_mode = 'BE' and (p_entitlement_item_id is null or p_apply_results_flag <> 'N')) then
3874: per_cagr_utility_pkg.log_and_raise_error('HR_289709_CAGR_INV_BE_PARAM'
3875: ,p_cagr_request_id);
3876: elsif (p_operation_mode = 'SC' and p_collective_agreement_id is null) then
3877: per_cagr_utility_pkg.log_and_raise_error('HR_289597_INV_SC_PARAM'
3878: ,p_cagr_request_id);
3879: end if;
3880:
3881: --
3881: --
3882: -- test for invalid params and values for modes
3883: --
3884: if not (p_apply_results_flag in('N','Y')) then
3885: per_cagr_utility_pkg.log_and_raise_error('HR_289418_CAGR_INV_DFLAG'
3886: ,p_cagr_request_id);
3887: elsif not (p_commit_flag in ('N','Y')) then
3888: per_cagr_utility_pkg.log_and_raise_error('HR_289419_CAGR_INV_CFLAG'
3889: ,p_cagr_request_id);
3884: if not (p_apply_results_flag in('N','Y')) then
3885: per_cagr_utility_pkg.log_and_raise_error('HR_289418_CAGR_INV_DFLAG'
3886: ,p_cagr_request_id);
3887: elsif not (p_commit_flag in ('N','Y')) then
3888: per_cagr_utility_pkg.log_and_raise_error('HR_289419_CAGR_INV_CFLAG'
3889: ,p_cagr_request_id);
3890: end if;
3891:
3892: if (p_assignment_id is not null and not(p_operation_mode in ('SE','SA'))) or
3897: (p_collective_agreement_id is not null and not(p_operation_mode in ('SC','BE'))) or
3898: (p_apply_results_flag <> 'N' and p_operation_mode in ('SE','BE')) or
3899: (p_collective_agreement_set_id is not null)
3900: then
3901: per_cagr_utility_pkg.log_and_raise_error('HR_289708_UNEXPECTED_PARAM',p_cagr_request_id);
3902: end if;
3903:
3904:
3905: --
3923:
3924: --
3925: -- Output parameter values to log
3926: --
3927: per_cagr_utility_pkg.put_log(g_head_separator,1);
3928: per_cagr_utility_pkg.put_log(' ',1);
3929: per_cagr_utility_pkg.put_log(' * Execution Parameter List * ',1);
3930: per_cagr_utility_pkg.put_log(' ',1);
3931: if p_params.operation_mode = 'SA' then
3924: --
3925: -- Output parameter values to log
3926: --
3927: per_cagr_utility_pkg.put_log(g_head_separator,1);
3928: per_cagr_utility_pkg.put_log(' ',1);
3929: per_cagr_utility_pkg.put_log(' * Execution Parameter List * ',1);
3930: per_cagr_utility_pkg.put_log(' ',1);
3931: if p_params.operation_mode = 'SA' then
3932: per_cagr_utility_pkg.put_log(' Mode: Single Assignment',1);
3925: -- Output parameter values to log
3926: --
3927: per_cagr_utility_pkg.put_log(g_head_separator,1);
3928: per_cagr_utility_pkg.put_log(' ',1);
3929: per_cagr_utility_pkg.put_log(' * Execution Parameter List * ',1);
3930: per_cagr_utility_pkg.put_log(' ',1);
3931: if p_params.operation_mode = 'SA' then
3932: per_cagr_utility_pkg.put_log(' Mode: Single Assignment',1);
3933: elsif p_params.operation_mode = 'SE' then
3926: --
3927: per_cagr_utility_pkg.put_log(g_head_separator,1);
3928: per_cagr_utility_pkg.put_log(' ',1);
3929: per_cagr_utility_pkg.put_log(' * Execution Parameter List * ',1);
3930: per_cagr_utility_pkg.put_log(' ',1);
3931: if p_params.operation_mode = 'SA' then
3932: per_cagr_utility_pkg.put_log(' Mode: Single Assignment',1);
3933: elsif p_params.operation_mode = 'SE' then
3934: per_cagr_utility_pkg.put_log(' Mode: Single Entitlement Item',1);
3928: per_cagr_utility_pkg.put_log(' ',1);
3929: per_cagr_utility_pkg.put_log(' * Execution Parameter List * ',1);
3930: per_cagr_utility_pkg.put_log(' ',1);
3931: if p_params.operation_mode = 'SA' then
3932: per_cagr_utility_pkg.put_log(' Mode: Single Assignment',1);
3933: elsif p_params.operation_mode = 'SE' then
3934: per_cagr_utility_pkg.put_log(' Mode: Single Entitlement Item',1);
3935: elsif p_params.operation_mode = 'BE' then
3936: per_cagr_utility_pkg.put_log(' Mode: Batch Entitlement Item',1);
3930: per_cagr_utility_pkg.put_log(' ',1);
3931: if p_params.operation_mode = 'SA' then
3932: per_cagr_utility_pkg.put_log(' Mode: Single Assignment',1);
3933: elsif p_params.operation_mode = 'SE' then
3934: per_cagr_utility_pkg.put_log(' Mode: Single Entitlement Item',1);
3935: elsif p_params.operation_mode = 'BE' then
3936: per_cagr_utility_pkg.put_log(' Mode: Batch Entitlement Item',1);
3937: elsif p_params.operation_mode = 'SC' then
3938: per_cagr_utility_pkg.put_log(' Mode: Single Collective Agreement',1);
3932: per_cagr_utility_pkg.put_log(' Mode: Single Assignment',1);
3933: elsif p_params.operation_mode = 'SE' then
3934: per_cagr_utility_pkg.put_log(' Mode: Single Entitlement Item',1);
3935: elsif p_params.operation_mode = 'BE' then
3936: per_cagr_utility_pkg.put_log(' Mode: Batch Entitlement Item',1);
3937: elsif p_params.operation_mode = 'SC' then
3938: per_cagr_utility_pkg.put_log(' Mode: Single Collective Agreement',1);
3939: end if;
3940: per_cagr_utility_pkg.put_log(' Business Group ID: '||p_params.business_group_id,1);
3934: per_cagr_utility_pkg.put_log(' Mode: Single Entitlement Item',1);
3935: elsif p_params.operation_mode = 'BE' then
3936: per_cagr_utility_pkg.put_log(' Mode: Batch Entitlement Item',1);
3937: elsif p_params.operation_mode = 'SC' then
3938: per_cagr_utility_pkg.put_log(' Mode: Single Collective Agreement',1);
3939: end if;
3940: per_cagr_utility_pkg.put_log(' Business Group ID: '||p_params.business_group_id,1);
3941: per_cagr_utility_pkg.put_log(' CAGR Request ID: '||p_params.cagr_request_id,1);
3942: per_cagr_utility_pkg.put_log(' Effective Date: '||p_params.effective_date,1);
3936: per_cagr_utility_pkg.put_log(' Mode: Batch Entitlement Item',1);
3937: elsif p_params.operation_mode = 'SC' then
3938: per_cagr_utility_pkg.put_log(' Mode: Single Collective Agreement',1);
3939: end if;
3940: per_cagr_utility_pkg.put_log(' Business Group ID: '||p_params.business_group_id,1);
3941: per_cagr_utility_pkg.put_log(' CAGR Request ID: '||p_params.cagr_request_id,1);
3942: per_cagr_utility_pkg.put_log(' Effective Date: '||p_params.effective_date,1);
3943: per_cagr_utility_pkg.put_log(' Assignment ID: '||p_params.assignment_id,1);
3944: per_cagr_utility_pkg.put_log(' Assignment Set ID: '||p_params.assignment_set_id);
3937: elsif p_params.operation_mode = 'SC' then
3938: per_cagr_utility_pkg.put_log(' Mode: Single Collective Agreement',1);
3939: end if;
3940: per_cagr_utility_pkg.put_log(' Business Group ID: '||p_params.business_group_id,1);
3941: per_cagr_utility_pkg.put_log(' CAGR Request ID: '||p_params.cagr_request_id,1);
3942: per_cagr_utility_pkg.put_log(' Effective Date: '||p_params.effective_date,1);
3943: per_cagr_utility_pkg.put_log(' Assignment ID: '||p_params.assignment_id,1);
3944: per_cagr_utility_pkg.put_log(' Assignment Set ID: '||p_params.assignment_set_id);
3945: per_cagr_utility_pkg.put_log(' Collective Agreement ID: '||p_params.collective_agreement_id,1);
3938: per_cagr_utility_pkg.put_log(' Mode: Single Collective Agreement',1);
3939: end if;
3940: per_cagr_utility_pkg.put_log(' Business Group ID: '||p_params.business_group_id,1);
3941: per_cagr_utility_pkg.put_log(' CAGR Request ID: '||p_params.cagr_request_id,1);
3942: per_cagr_utility_pkg.put_log(' Effective Date: '||p_params.effective_date,1);
3943: per_cagr_utility_pkg.put_log(' Assignment ID: '||p_params.assignment_id,1);
3944: per_cagr_utility_pkg.put_log(' Assignment Set ID: '||p_params.assignment_set_id);
3945: per_cagr_utility_pkg.put_log(' Collective Agreement ID: '||p_params.collective_agreement_id,1);
3946: per_cagr_utility_pkg.put_log(' Collective Agreement Set ID: '||p_params.cagr_set_id);
3939: end if;
3940: per_cagr_utility_pkg.put_log(' Business Group ID: '||p_params.business_group_id,1);
3941: per_cagr_utility_pkg.put_log(' CAGR Request ID: '||p_params.cagr_request_id,1);
3942: per_cagr_utility_pkg.put_log(' Effective Date: '||p_params.effective_date,1);
3943: per_cagr_utility_pkg.put_log(' Assignment ID: '||p_params.assignment_id,1);
3944: per_cagr_utility_pkg.put_log(' Assignment Set ID: '||p_params.assignment_set_id);
3945: per_cagr_utility_pkg.put_log(' Collective Agreement ID: '||p_params.collective_agreement_id,1);
3946: per_cagr_utility_pkg.put_log(' Collective Agreement Set ID: '||p_params.cagr_set_id);
3947: per_cagr_utility_pkg.put_log(' Payroll ID: '||p_params.payroll_id);
3940: per_cagr_utility_pkg.put_log(' Business Group ID: '||p_params.business_group_id,1);
3941: per_cagr_utility_pkg.put_log(' CAGR Request ID: '||p_params.cagr_request_id,1);
3942: per_cagr_utility_pkg.put_log(' Effective Date: '||p_params.effective_date,1);
3943: per_cagr_utility_pkg.put_log(' Assignment ID: '||p_params.assignment_id,1);
3944: per_cagr_utility_pkg.put_log(' Assignment Set ID: '||p_params.assignment_set_id);
3945: per_cagr_utility_pkg.put_log(' Collective Agreement ID: '||p_params.collective_agreement_id,1);
3946: per_cagr_utility_pkg.put_log(' Collective Agreement Set ID: '||p_params.cagr_set_id);
3947: per_cagr_utility_pkg.put_log(' Payroll ID: '||p_params.payroll_id);
3948: per_cagr_utility_pkg.put_log(' Person ID: '||p_params.person_id);
3941: per_cagr_utility_pkg.put_log(' CAGR Request ID: '||p_params.cagr_request_id,1);
3942: per_cagr_utility_pkg.put_log(' Effective Date: '||p_params.effective_date,1);
3943: per_cagr_utility_pkg.put_log(' Assignment ID: '||p_params.assignment_id,1);
3944: per_cagr_utility_pkg.put_log(' Assignment Set ID: '||p_params.assignment_set_id);
3945: per_cagr_utility_pkg.put_log(' Collective Agreement ID: '||p_params.collective_agreement_id,1);
3946: per_cagr_utility_pkg.put_log(' Collective Agreement Set ID: '||p_params.cagr_set_id);
3947: per_cagr_utility_pkg.put_log(' Payroll ID: '||p_params.payroll_id);
3948: per_cagr_utility_pkg.put_log(' Person ID: '||p_params.person_id);
3949: per_cagr_utility_pkg.put_log(' Entitlement Item ID: '||p_params.entitlement_item_id);
3942: per_cagr_utility_pkg.put_log(' Effective Date: '||p_params.effective_date,1);
3943: per_cagr_utility_pkg.put_log(' Assignment ID: '||p_params.assignment_id,1);
3944: per_cagr_utility_pkg.put_log(' Assignment Set ID: '||p_params.assignment_set_id);
3945: per_cagr_utility_pkg.put_log(' Collective Agreement ID: '||p_params.collective_agreement_id,1);
3946: per_cagr_utility_pkg.put_log(' Collective Agreement Set ID: '||p_params.cagr_set_id);
3947: per_cagr_utility_pkg.put_log(' Payroll ID: '||p_params.payroll_id);
3948: per_cagr_utility_pkg.put_log(' Person ID: '||p_params.person_id);
3949: per_cagr_utility_pkg.put_log(' Entitlement Item ID: '||p_params.entitlement_item_id);
3950: if p_params.denormalise_flag = 'Y' then
3943: per_cagr_utility_pkg.put_log(' Assignment ID: '||p_params.assignment_id,1);
3944: per_cagr_utility_pkg.put_log(' Assignment Set ID: '||p_params.assignment_set_id);
3945: per_cagr_utility_pkg.put_log(' Collective Agreement ID: '||p_params.collective_agreement_id,1);
3946: per_cagr_utility_pkg.put_log(' Collective Agreement Set ID: '||p_params.cagr_set_id);
3947: per_cagr_utility_pkg.put_log(' Payroll ID: '||p_params.payroll_id);
3948: per_cagr_utility_pkg.put_log(' Person ID: '||p_params.person_id);
3949: per_cagr_utility_pkg.put_log(' Entitlement Item ID: '||p_params.entitlement_item_id);
3950: if p_params.denormalise_flag = 'Y' then
3951: per_cagr_utility_pkg.put_log(' Apply entitlements to HRMS flag: Yes',1);
3944: per_cagr_utility_pkg.put_log(' Assignment Set ID: '||p_params.assignment_set_id);
3945: per_cagr_utility_pkg.put_log(' Collective Agreement ID: '||p_params.collective_agreement_id,1);
3946: per_cagr_utility_pkg.put_log(' Collective Agreement Set ID: '||p_params.cagr_set_id);
3947: per_cagr_utility_pkg.put_log(' Payroll ID: '||p_params.payroll_id);
3948: per_cagr_utility_pkg.put_log(' Person ID: '||p_params.person_id);
3949: per_cagr_utility_pkg.put_log(' Entitlement Item ID: '||p_params.entitlement_item_id);
3950: if p_params.denormalise_flag = 'Y' then
3951: per_cagr_utility_pkg.put_log(' Apply entitlements to HRMS flag: Yes',1);
3952: elsif p_params.denormalise_flag = 'N' then
3945: per_cagr_utility_pkg.put_log(' Collective Agreement ID: '||p_params.collective_agreement_id,1);
3946: per_cagr_utility_pkg.put_log(' Collective Agreement Set ID: '||p_params.cagr_set_id);
3947: per_cagr_utility_pkg.put_log(' Payroll ID: '||p_params.payroll_id);
3948: per_cagr_utility_pkg.put_log(' Person ID: '||p_params.person_id);
3949: per_cagr_utility_pkg.put_log(' Entitlement Item ID: '||p_params.entitlement_item_id);
3950: if p_params.denormalise_flag = 'Y' then
3951: per_cagr_utility_pkg.put_log(' Apply entitlements to HRMS flag: Yes',1);
3952: elsif p_params.denormalise_flag = 'N' then
3953: per_cagr_utility_pkg.put_log(' Apply entitlements to HRMS flag: No',1);
3947: per_cagr_utility_pkg.put_log(' Payroll ID: '||p_params.payroll_id);
3948: per_cagr_utility_pkg.put_log(' Person ID: '||p_params.person_id);
3949: per_cagr_utility_pkg.put_log(' Entitlement Item ID: '||p_params.entitlement_item_id);
3950: if p_params.denormalise_flag = 'Y' then
3951: per_cagr_utility_pkg.put_log(' Apply entitlements to HRMS flag: Yes',1);
3952: elsif p_params.denormalise_flag = 'N' then
3953: per_cagr_utility_pkg.put_log(' Apply entitlements to HRMS flag: No',1);
3954: end if;
3955: if p_params.commit_flag = 'Y' then
3949: per_cagr_utility_pkg.put_log(' Entitlement Item ID: '||p_params.entitlement_item_id);
3950: if p_params.denormalise_flag = 'Y' then
3951: per_cagr_utility_pkg.put_log(' Apply entitlements to HRMS flag: Yes',1);
3952: elsif p_params.denormalise_flag = 'N' then
3953: per_cagr_utility_pkg.put_log(' Apply entitlements to HRMS flag: No',1);
3954: end if;
3955: if p_params.commit_flag = 'Y' then
3956: per_cagr_utility_pkg.put_log(' Commit entitlements flag: Yes',1);
3957: elsif p_params.commit_flag = 'N' then
3952: elsif p_params.denormalise_flag = 'N' then
3953: per_cagr_utility_pkg.put_log(' Apply entitlements to HRMS flag: No',1);
3954: end if;
3955: if p_params.commit_flag = 'Y' then
3956: per_cagr_utility_pkg.put_log(' Commit entitlements flag: Yes',1);
3957: elsif p_params.commit_flag = 'N' then
3958: per_cagr_utility_pkg.put_log(' Commit entitlements flag: No',1);
3959: end if;
3960: per_cagr_utility_pkg.put_log(' ',1);
3954: end if;
3955: if p_params.commit_flag = 'Y' then
3956: per_cagr_utility_pkg.put_log(' Commit entitlements flag: Yes',1);
3957: elsif p_params.commit_flag = 'N' then
3958: per_cagr_utility_pkg.put_log(' Commit entitlements flag: No',1);
3959: end if;
3960: per_cagr_utility_pkg.put_log(' ',1);
3961:
3962: -- ****** This needs to be converted to a parameter passed to create_request,
3956: per_cagr_utility_pkg.put_log(' Commit entitlements flag: Yes',1);
3957: elsif p_params.commit_flag = 'N' then
3958: per_cagr_utility_pkg.put_log(' Commit entitlements flag: No',1);
3959: end if;
3960: per_cagr_utility_pkg.put_log(' ',1);
3961:
3962: -- ****** This needs to be converted to a parameter passed to create_request,
3963: -- rather than relying on a public package variable *******
3964:
3962: -- ****** This needs to be converted to a parameter passed to create_request,
3963: -- rather than relying on a public package variable *******
3964:
3965: if fnd_global.conc_request_id <> -1 then
3966: per_cagr_utility_pkg.put_log(' Executed from concurrent manager');
3967: else
3968: per_cagr_utility_pkg.put_log(' Executed from SQLPLUS session');
3969: end if;
3970: per_cagr_utility_pkg.put_log(' ');
3964:
3965: if fnd_global.conc_request_id <> -1 then
3966: per_cagr_utility_pkg.put_log(' Executed from concurrent manager');
3967: else
3968: per_cagr_utility_pkg.put_log(' Executed from SQLPLUS session');
3969: end if;
3970: per_cagr_utility_pkg.put_log(' ');
3971: per_cagr_utility_pkg.write_log_file(p_params.cagr_request_id);
3972:
3966: per_cagr_utility_pkg.put_log(' Executed from concurrent manager');
3967: else
3968: per_cagr_utility_pkg.put_log(' Executed from SQLPLUS session');
3969: end if;
3970: per_cagr_utility_pkg.put_log(' ');
3971: per_cagr_utility_pkg.write_log_file(p_params.cagr_request_id);
3972:
3973: --
3974: -- invoke evaluation processing;
3967: else
3968: per_cagr_utility_pkg.put_log(' Executed from SQLPLUS session');
3969: end if;
3970: per_cagr_utility_pkg.put_log(' ');
3971: per_cagr_utility_pkg.write_log_file(p_params.cagr_request_id);
3972:
3973: --
3974: -- invoke evaluation processing;
3975: --
3986: --
3987: -- Commit, if required.
3988: --
3989: if p_params.commit_flag = 'Y' then
3990: per_cagr_utility_pkg.put_log(' Any changes have been saved.',1);
3991: commit;
3992: elsif p_params.commit_flag = 'N' then
3993: per_cagr_utility_pkg.put_log(' Any changes have been discarded.',1);
3994: rollback;
3989: if p_params.commit_flag = 'Y' then
3990: per_cagr_utility_pkg.put_log(' Any changes have been saved.',1);
3991: commit;
3992: elsif p_params.commit_flag = 'N' then
3993: per_cagr_utility_pkg.put_log(' Any changes have been discarded.',1);
3994: rollback;
3995: end if;
3996:
3997: -- complete logging
3994: rollback;
3995: end if;
3996:
3997: -- complete logging
3998: per_cagr_utility_pkg.put_log(g_separator,1);
3999: per_cagr_utility_pkg.write_log_file(p_params.cagr_request_id);
4000:
4001: hr_utility.set_location('Leaving:'||l_proc, 50);
4002:
3995: end if;
3996:
3997: -- complete logging
3998: per_cagr_utility_pkg.put_log(g_separator,1);
3999: per_cagr_utility_pkg.write_log_file(p_params.cagr_request_id);
4000:
4001: hr_utility.set_location('Leaving:'||l_proc, 50);
4002:
4003: END initialise;