[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:
1064: t_cagr_request request_table;
1065:
1066: BEGIN
1067: hr_utility.set_location('Entering:'||l_proc, 10);
1068: per_cagr_utility_pkg.put_log(' Preparing results cache',2);
1069:
1070: if p_switch = 'W' then
1071: -- we are 'updating' results for a specific item:
1072: -- previous results have been cleaned by csr_all_results, below (except SE mode which we do here)
1102: end loop;
1103: -- as we have deleted an entitlement result, also delete any
1104: -- log entries, for the results request_id.
1105: -- (we use the first fetched request_id, as it doesn't change).
1106: per_cagr_utility_pkg.remove_log_entries(v_delete_cagr_request_id);
1107: end if;
1108: close csr_item_results;
1109: end if;
1110:
1115: if v_future_start_date is not null then
1116: v_end_date := v_future_start_date -1;
1117: end if;
1118:
1119: per_cagr_utility_pkg.put_log(' New results have Start Date: '||p_params.effective_date||', End Date: '||v_end_date,2);
1120: -- now insert new set of results for the item
1121: write_results(p_structure,p_params.cagr_request_id,p_params.effective_date,v_end_date);
1122:
1123: elsif p_switch = 'C' then
1132: -- i.e. take the asg_id for the current item set
1133: -- which may change as we process different assignments
1134: v_assignment_id := p_structure(1).assignment_id;
1135: end if;
1136: per_cagr_utility_pkg.put_log(' Cleaning previous cache results found for asg id: '||v_assignment_id,2);
1137:
1138: open csr_all_results(v_assignment_id);
1139: loop
1140: fetch csr_all_results into v_start_date, v_cagr_request_id, v_element_type_id;
1178:
1179: if t_cagr_request.count > 0 then
1180: for j in 1 .. t_cagr_request.last loop
1181: -- as we have deleted an entitlement result, also delete any log entries, for the result's request_id.
1182: per_cagr_utility_pkg.remove_log_entries(t_cagr_request(j));
1183: end loop;
1184: end if;
1185:
1186: end if;
1183: end loop;
1184: end if;
1185:
1186: end if;
1187: per_cagr_utility_pkg.put_log(' Completed preparing results cache.',2);
1188:
1189: hr_utility.set_location('Leaving:'||l_proc, 50);
1190:
1191: EXCEPTION
1190:
1191: EXCEPTION
1192: WHEN e_resource_busy THEN
1193: -- raise resource busy message.
1194: per_cagr_utility_pkg.put_log(' ERROR: Another user is updating the entitlement results for the assignment.',1);
1195: per_cagr_utility_pkg.put_log(' Unable to lock result records exclusively. Please try again later.',1);
1196: fnd_message.set_name('PAY', 'HR_7165_OBJECT_LOCKED');
1197: fnd_message.set_token('TABLE_NAME', 'per_cagr_entitlement_results');
1198: fnd_message.raise_error;
1191: EXCEPTION
1192: WHEN e_resource_busy THEN
1193: -- raise resource busy message.
1194: per_cagr_utility_pkg.put_log(' ERROR: Another user is updating the entitlement results for the assignment.',1);
1195: per_cagr_utility_pkg.put_log(' Unable to lock result records exclusively. Please try again later.',1);
1196: fnd_message.set_name('PAY', 'HR_7165_OBJECT_LOCKED');
1197: fnd_message.set_token('TABLE_NAME', 'per_cagr_entitlement_results');
1198: fnd_message.raise_error;
1199:
1240:
1241: BEGIN
1242: hr_utility.set_location('Entering:'||l_proc, 10);
1243:
1244: per_cagr_utility_pkg.put_log('Identified entitlement line records, calling benmngle at: '||fnd_date.date_to_canonical(sysdate));
1245: per_cagr_utility_pkg.put_log(' p_person_id: '|| to_char(p_person_id));
1246: per_cagr_utility_pkg.put_log(' p_effective_date: '|| to_char(p_effective_date,'DD-MON-YYYY'));
1247: per_cagr_utility_pkg.put_log(' p_mode: '||l_mode);
1248: per_cagr_utility_pkg.put_log(' p_derivable_factors: '||l_derivable_factors_flag);
1241: BEGIN
1242: hr_utility.set_location('Entering:'||l_proc, 10);
1243:
1244: per_cagr_utility_pkg.put_log('Identified entitlement line records, calling benmngle at: '||fnd_date.date_to_canonical(sysdate));
1245: per_cagr_utility_pkg.put_log(' p_person_id: '|| to_char(p_person_id));
1246: per_cagr_utility_pkg.put_log(' p_effective_date: '|| to_char(p_effective_date,'DD-MON-YYYY'));
1247: per_cagr_utility_pkg.put_log(' p_mode: '||l_mode);
1248: per_cagr_utility_pkg.put_log(' p_derivable_factors: '||l_derivable_factors_flag);
1249: per_cagr_utility_pkg.put_log(' p_validate: '||l_validate_flag);
1242: hr_utility.set_location('Entering:'||l_proc, 10);
1243:
1244: per_cagr_utility_pkg.put_log('Identified entitlement line records, calling benmngle at: '||fnd_date.date_to_canonical(sysdate));
1245: per_cagr_utility_pkg.put_log(' p_person_id: '|| to_char(p_person_id));
1246: per_cagr_utility_pkg.put_log(' p_effective_date: '|| to_char(p_effective_date,'DD-MON-YYYY'));
1247: per_cagr_utility_pkg.put_log(' p_mode: '||l_mode);
1248: per_cagr_utility_pkg.put_log(' p_derivable_factors: '||l_derivable_factors_flag);
1249: per_cagr_utility_pkg.put_log(' p_validate: '||l_validate_flag);
1250: per_cagr_utility_pkg.put_log(' p_pl_id: '|| to_char(p_pl_id));
1243:
1244: per_cagr_utility_pkg.put_log('Identified entitlement line records, calling benmngle at: '||fnd_date.date_to_canonical(sysdate));
1245: per_cagr_utility_pkg.put_log(' p_person_id: '|| to_char(p_person_id));
1246: per_cagr_utility_pkg.put_log(' p_effective_date: '|| to_char(p_effective_date,'DD-MON-YYYY'));
1247: per_cagr_utility_pkg.put_log(' p_mode: '||l_mode);
1248: per_cagr_utility_pkg.put_log(' p_derivable_factors: '||l_derivable_factors_flag);
1249: per_cagr_utility_pkg.put_log(' p_validate: '||l_validate_flag);
1250: per_cagr_utility_pkg.put_log(' p_pl_id: '|| to_char(p_pl_id));
1251: per_cagr_utility_pkg.put_log(' p_cagr_id: '|| to_char( p_params.collective_agreement_id)); -- Bug # 5391298
1244: per_cagr_utility_pkg.put_log('Identified entitlement line records, calling benmngle at: '||fnd_date.date_to_canonical(sysdate));
1245: per_cagr_utility_pkg.put_log(' p_person_id: '|| to_char(p_person_id));
1246: per_cagr_utility_pkg.put_log(' p_effective_date: '|| to_char(p_effective_date,'DD-MON-YYYY'));
1247: per_cagr_utility_pkg.put_log(' p_mode: '||l_mode);
1248: per_cagr_utility_pkg.put_log(' p_derivable_factors: '||l_derivable_factors_flag);
1249: per_cagr_utility_pkg.put_log(' p_validate: '||l_validate_flag);
1250: per_cagr_utility_pkg.put_log(' p_pl_id: '|| to_char(p_pl_id));
1251: per_cagr_utility_pkg.put_log(' p_cagr_id: '|| to_char( p_params.collective_agreement_id)); -- Bug # 5391298
1252: per_cagr_utility_pkg.put_log(' p_opt_id: '|| to_char(p_opt_id));
1245: per_cagr_utility_pkg.put_log(' p_person_id: '|| to_char(p_person_id));
1246: per_cagr_utility_pkg.put_log(' p_effective_date: '|| to_char(p_effective_date,'DD-MON-YYYY'));
1247: per_cagr_utility_pkg.put_log(' p_mode: '||l_mode);
1248: per_cagr_utility_pkg.put_log(' p_derivable_factors: '||l_derivable_factors_flag);
1249: per_cagr_utility_pkg.put_log(' p_validate: '||l_validate_flag);
1250: per_cagr_utility_pkg.put_log(' p_pl_id: '|| to_char(p_pl_id));
1251: per_cagr_utility_pkg.put_log(' p_cagr_id: '|| to_char( p_params.collective_agreement_id)); -- Bug # 5391298
1252: per_cagr_utility_pkg.put_log(' p_opt_id: '|| to_char(p_opt_id));
1253: per_cagr_utility_pkg.put_log(' p_bg_id: '|| to_char(p_bg_id));
1246: per_cagr_utility_pkg.put_log(' p_effective_date: '|| to_char(p_effective_date,'DD-MON-YYYY'));
1247: per_cagr_utility_pkg.put_log(' p_mode: '||l_mode);
1248: per_cagr_utility_pkg.put_log(' p_derivable_factors: '||l_derivable_factors_flag);
1249: per_cagr_utility_pkg.put_log(' p_validate: '||l_validate_flag);
1250: per_cagr_utility_pkg.put_log(' p_pl_id: '|| to_char(p_pl_id));
1251: per_cagr_utility_pkg.put_log(' p_cagr_id: '|| to_char( p_params.collective_agreement_id)); -- Bug # 5391298
1252: per_cagr_utility_pkg.put_log(' p_opt_id: '|| to_char(p_opt_id));
1253: per_cagr_utility_pkg.put_log(' p_bg_id: '|| to_char(p_bg_id));
1254:
1247: per_cagr_utility_pkg.put_log(' p_mode: '||l_mode);
1248: per_cagr_utility_pkg.put_log(' p_derivable_factors: '||l_derivable_factors_flag);
1249: per_cagr_utility_pkg.put_log(' p_validate: '||l_validate_flag);
1250: per_cagr_utility_pkg.put_log(' p_pl_id: '|| to_char(p_pl_id));
1251: per_cagr_utility_pkg.put_log(' p_cagr_id: '|| to_char( p_params.collective_agreement_id)); -- Bug # 5391298
1252: per_cagr_utility_pkg.put_log(' p_opt_id: '|| to_char(p_opt_id));
1253: per_cagr_utility_pkg.put_log(' p_bg_id: '|| to_char(p_bg_id));
1254:
1255: ben_manage_life_events.internal_process
1248: per_cagr_utility_pkg.put_log(' p_derivable_factors: '||l_derivable_factors_flag);
1249: per_cagr_utility_pkg.put_log(' p_validate: '||l_validate_flag);
1250: per_cagr_utility_pkg.put_log(' p_pl_id: '|| to_char(p_pl_id));
1251: per_cagr_utility_pkg.put_log(' p_cagr_id: '|| to_char( p_params.collective_agreement_id)); -- Bug # 5391298
1252: per_cagr_utility_pkg.put_log(' p_opt_id: '|| to_char(p_opt_id));
1253: per_cagr_utility_pkg.put_log(' p_bg_id: '|| to_char(p_bg_id));
1254:
1255: ben_manage_life_events.internal_process
1256: (errbuf => l_errbuf,
1249: per_cagr_utility_pkg.put_log(' p_validate: '||l_validate_flag);
1250: per_cagr_utility_pkg.put_log(' p_pl_id: '|| to_char(p_pl_id));
1251: per_cagr_utility_pkg.put_log(' p_cagr_id: '|| to_char( p_params.collective_agreement_id)); -- Bug # 5391298
1252: per_cagr_utility_pkg.put_log(' p_opt_id: '|| to_char(p_opt_id));
1253: per_cagr_utility_pkg.put_log(' p_bg_id: '|| to_char(p_bg_id));
1254:
1255: ben_manage_life_events.internal_process
1256: (errbuf => l_errbuf,
1257: retcode => l_retcode,
1269: p_audit_log_flag => 'Y');
1270:
1271: Commit;
1272:
1273: per_cagr_utility_pkg.put_log('Completed benmngle at: '||
1274: fnd_date.date_to_canonical(sysdate)||' return code is :'||to_char(l_retcode));
1275: per_cagr_utility_pkg.put_log('benefit_action_id: '|| to_char(l_benefit_action_id));
1276: p_benefit_action_id := l_benefit_action_id;
1277:
1271: Commit;
1272:
1273: per_cagr_utility_pkg.put_log('Completed benmngle at: '||
1274: fnd_date.date_to_canonical(sysdate)||' return code is :'||to_char(l_retcode));
1275: per_cagr_utility_pkg.put_log('benefit_action_id: '|| to_char(l_benefit_action_id));
1276: p_benefit_action_id := l_benefit_action_id;
1277:
1278: hr_utility.set_location('Leaving:'||l_proc, 30);
1279:
1280: EXCEPTION
1281: when others then
1282: Rollback;
1283: hr_utility.set_location('Fatal Error: '||l_proc, 20);
1284: per_cagr_utility_pkg.put_log('ben_manage_life_events.process fatal error',1);
1285: per_cagr_utility_pkg.put_log('Error: '||sqlerrm,1);
1286: per_cagr_utility_pkg.write_log_file(p_params.cagr_request_id);
1287: raise;
1288:
1281: when others then
1282: Rollback;
1283: hr_utility.set_location('Fatal Error: '||l_proc, 20);
1284: per_cagr_utility_pkg.put_log('ben_manage_life_events.process fatal error',1);
1285: per_cagr_utility_pkg.put_log('Error: '||sqlerrm,1);
1286: per_cagr_utility_pkg.write_log_file(p_params.cagr_request_id);
1287: raise;
1288:
1289: END process_entitlement_lines;
1282: Rollback;
1283: hr_utility.set_location('Fatal Error: '||l_proc, 20);
1284: per_cagr_utility_pkg.put_log('ben_manage_life_events.process fatal error',1);
1285: per_cagr_utility_pkg.put_log('Error: '||sqlerrm,1);
1286: per_cagr_utility_pkg.write_log_file(p_params.cagr_request_id);
1287: raise;
1288:
1289: END process_entitlement_lines;
1290:
1330: p_eligibility_table(p_counter).OIPL_ID := v_elig_lines.OIPL_ID;
1331: p_eligibility_table(p_counter).ELIG_FLAG := v_elig_lines.ELIG_FLAG;
1332: end loop;
1333: end if;
1334: per_cagr_utility_pkg.put_log('Benmngle created '||to_char(p_counter)||' positive eligibility records');
1335:
1336: hr_utility.set_location('Leaving:'||l_proc, 30);
1337:
1338: END get_BEN_eligibility_info;
1377: BEGIN
1378: hr_utility.set_location('Entering:'||l_proc, 10);
1379:
1380: if p_oipl_id is null then
1381: per_cagr_utility_pkg.put_log(' ERROR: Option in plan id is null for criteria line ',1);
1382: hr_utility.set_message(800, 'HR_289415_CAGR_OIPL_NULL');
1383: hr_utility.raise_error;
1384: end if;
1385:
1388: for i in p_eligibility_table.FIRST .. p_eligibility_table.LAST loop
1389: if p_eligibility_table(i).OIPL_ID = p_oipl_id then
1390: v_found := TRUE;
1391: if p_eligibility_table(i).ELIG_FLAG = 'Y' then
1392: per_cagr_utility_pkg.put_log(' The criteria eligibility profile is satisfied.',1 );
1393: return TRUE;
1394: end if;
1395: end if;
1396: end loop;
1400: OPEN csr_check_elig;
1401: FETCH csr_check_elig INTO l_person_id;
1402: IF csr_check_elig%FOUND THEN
1403: CLOSE csr_check_elig;
1404: per_cagr_utility_pkg.put_log(' The criteria eligibility profile is satisfied.',1 );
1405: return TRUE;
1406: END IF;
1407: CLOSE csr_check_elig;
1408: OPEN csr_check_oipl;
1417: /* for i in p_eligibility_table.FIRST .. p_eligibility_table.LAST loop
1418: if p_eligibility_table(i).OIPL_ID = p_oipl_id then
1419: v_found := TRUE;
1420: if p_eligibility_table(i).PERSON_ID = p_person_id and p_eligibility_table(i).ELIG_FLAG = 'Y' then
1421: per_cagr_utility_pkg.put_log(' The criteria eligibility profile is satisfied.',1 );
1422: return TRUE;
1423: end if;
1424: end if;
1425: end loop; */
1425: end loop; */
1426: end if;
1427:
1428: if v_found = FALSE then
1429: per_cagr_utility_pkg.put_log(' ERROR: Option in plan id does not exist ',1);
1430: hr_utility.set_message(800, 'HR_289416_CAGR_OIPL_NOT_FOUND');
1431: hr_utility.raise_error;
1432: end if;
1433:
1430: hr_utility.set_message(800, 'HR_289416_CAGR_OIPL_NOT_FOUND');
1431: hr_utility.raise_error;
1432: end if;
1433:
1434: per_cagr_utility_pkg.put_log(' The criteria eligibility profile is not satisfied.',1);
1435: hr_utility.set_location('Leaving:'||l_proc, 50);
1436: return FALSE;
1437:
1438: END check_entitlement_eligible;
1589:
1590: hr_utility.set_location('Entering:'||l_proc, 10);
1591: l_category := p_results_table(1).category_name;
1592: if p_ben_rule is not null then
1593: per_cagr_utility_pkg.put_log(' Evaluating '||p_ben_rule||' beneficial rule on '||p_results_table.last||' results for this entitlement item',1);
1594: else
1595: per_cagr_utility_pkg.put_log(' No beneficial rule is defined for this entitlement item',1);
1596: end if;
1597:
1591: l_category := p_results_table(1).category_name;
1592: if p_ben_rule is not null then
1593: per_cagr_utility_pkg.put_log(' Evaluating '||p_ben_rule||' beneficial rule on '||p_results_table.last||' results for this entitlement item',1);
1594: else
1595: per_cagr_utility_pkg.put_log(' No beneficial rule is defined for this entitlement item',1);
1596: end if;
1597:
1598: -- first test to see whether we are processing value or step_id field, in result records
1599: if p_results_table(1).value is not null and p_results_table(1).step_id is null then
1600: l_ben_field := 'VALUE';
1601: elsif p_results_table(1).value is null and p_results_table(1).step_id is not null then
1602: l_ben_field := 'STEP_ID';
1603: else
1604: per_cagr_utility_pkg.put_log('Cannot determine either of value or step_id to process');
1605: p_rule_inconclusive := TRUE;
1606: goto end_of_procedure; -- don't raise an exception
1607: end if;
1608:
1605: p_rule_inconclusive := TRUE;
1606: goto end_of_procedure; -- don't raise an exception
1607: end if;
1608:
1609: per_cagr_utility_pkg.put_log(' Beneficial field is: '||l_ben_field);
1610:
1611: -- if only one record in the input table, default and skip processing
1612: if p_results_table.count = 1 then
1613: if l_ben_field = 'VALUE' then
1619: -- start processing the results as > 1 record in table
1620: -- and ben_rule is set for the item.
1621:
1622: if p_ben_rule_vs_id is not null then
1623: per_cagr_utility_pkg.put_log(' Beneficial rule uses ValueSet id: '||p_ben_rule_vs_id);
1624: -- we are using a data column so build list of id's from results for sql
1625: if l_ben_field = 'VALUE' then
1626: for i in p_results_table.first .. p_results_table.last loop
1627: l_list_str := l_list_str || p_results_table(i).VALUE ||',';
1633: end if;
1634: l_list_str := substr(l_list_str,1,(length(l_list_str) -1));
1635: l_list_str := '('||l_list_str||')';
1636: -- get the sql to be used to get the data column from value set
1637: l_sql := per_cagr_utility_pkg.get_sql_from_vset_id(p_ben_rule_vs_id);
1638: if l_sql is null then
1639: per_cagr_utility_pkg.put_log(' Could not determine SQL for ValueSet id');
1640: p_rule_inconclusive := TRUE;
1641: else
1635: l_list_str := '('||l_list_str||')';
1636: -- get the sql to be used to get the data column from value set
1637: l_sql := per_cagr_utility_pkg.get_sql_from_vset_id(p_ben_rule_vs_id);
1638: if l_sql is null then
1639: per_cagr_utility_pkg.put_log(' Could not determine SQL for ValueSet id');
1640: p_rule_inconclusive := TRUE;
1641: else
1642: -- replace BG_ID, and insert list of ids into the sql statement
1643: l_sql := replace(l_sql,':$PROFILES$.PER_BUSINESS_GROUP_ID',p_params.business_group_id);
1641: else
1642: -- replace BG_ID, and insert list of ids into the sql statement
1643: l_sql := replace(l_sql,':$PROFILES$.PER_BUSINESS_GROUP_ID',p_params.business_group_id);
1644: l_sql := replace(l_sql,'()',l_list_str);
1645: per_cagr_utility_pkg.put_log(l_sql);
1646:
1647: -- determine datatype of value set data column
1648: open csr_data(p_ben_rule_vs_id);
1649: fetch csr_data into l_col_data_type;
1648: open csr_data(p_ben_rule_vs_id);
1649: fetch csr_data into l_col_data_type;
1650: if csr_data%notfound then
1651: close csr_data;
1652: per_cagr_utility_pkg.put_log(' ValueSet column type not found');
1653: p_rule_inconclusive := TRUE;
1654: goto end_of_procedure;
1655: end if;
1656: per_cagr_utility_pkg.put_log(' Datatype of ValueSet data column is: '||l_col_data_type);
1652: per_cagr_utility_pkg.put_log(' ValueSet column type not found');
1653: p_rule_inconclusive := TRUE;
1654: goto end_of_procedure;
1655: end if;
1656: per_cagr_utility_pkg.put_log(' Datatype of ValueSet data column is: '||l_col_data_type);
1657:
1658: -- dynamic sql to get the value set data column values for list of id's
1659: -- and call relevant ben function for the datatype
1660: open l_dyn_csr for l_sql;
1687:
1688: else -- do regular processing on actual cagr data, using value only, and the
1689: -- value which is not treated as an ID (user must set up value set for ids
1690: -- within value or step_id).
1691: per_cagr_utility_pkg.put_log(' Ben rule uses cagr value column');
1692: per_cagr_utility_pkg.put_log(' Datatype of cagr column is: '||p_results_table(1).column_type);
1693:
1694: BEGIN
1695: if p_results_table(1).column_type = 'VAR' then
1688: else -- do regular processing on actual cagr data, using value only, and the
1689: -- value which is not treated as an ID (user must set up value set for ids
1690: -- within value or step_id).
1691: per_cagr_utility_pkg.put_log(' Ben rule uses cagr value column');
1692: per_cagr_utility_pkg.put_log(' Datatype of cagr column is: '||p_results_table(1).column_type);
1693:
1694: BEGIN
1695: if p_results_table(1).column_type = 'VAR' then
1696: for i in p_results_table.first..p_results_table.last loop
1709: do_date_beneficial(l_dyn_csr_table,p_ben_rule,l_ben_row);
1710: end if;
1711: EXCEPTION
1712: WHEN OTHERS THEN -- trap any value conversion exceptions
1713: per_cagr_utility_pkg.put_log(' Beneficial Rule evaluation error',1);
1714: per_cagr_utility_pkg.put_log(' ERROR: '||sqlerrm,1);
1715: p_rule_inconclusive := TRUE;
1716: goto end_of_procedure;
1717: END;
1710: end if;
1711: EXCEPTION
1712: WHEN OTHERS THEN -- trap any value conversion exceptions
1713: per_cagr_utility_pkg.put_log(' Beneficial Rule evaluation error',1);
1714: per_cagr_utility_pkg.put_log(' ERROR: '||sqlerrm,1);
1715: p_rule_inconclusive := TRUE;
1716: goto end_of_procedure;
1717: END;
1718: end if;
1723: -- mark the beneficial record and log the value
1724: p_results_table(l_ben_row).BENEFICIAL_FLAG := 'Y';
1725: p_ben_row := l_ben_row;
1726: if l_ben_field = 'VALUE' then
1727: per_cagr_utility_pkg.put_log(' Beneficial value is: '||p_results_table(l_ben_row).value,1);
1728: elsif l_ben_field = 'STEP_ID' then
1729: per_cagr_utility_pkg.put_log(' Beneficial step_id is: '||p_results_table(l_ben_row).step_id,1);
1730: end if;
1731: else
1725: p_ben_row := l_ben_row;
1726: if l_ben_field = 'VALUE' then
1727: per_cagr_utility_pkg.put_log(' Beneficial value is: '||p_results_table(l_ben_row).value,1);
1728: elsif l_ben_field = 'STEP_ID' then
1729: per_cagr_utility_pkg.put_log(' Beneficial step_id is: '||p_results_table(l_ben_row).step_id,1);
1730: end if;
1731: else
1732: if p_ben_rule is not null then
1733: p_rule_inconclusive := TRUE;
1828:
1829: BEGIN
1830:
1831: hr_utility.set_location('Entering:'||l_proc, 10);
1832: per_cagr_utility_pkg.put_log(' Evaluating related Retained Rights for the item ',1);
1833: -- We are processing RR for a dataitem for an ASG
1834: open csr_cagr_retained_rights;
1835: LOOP
1836: fetch csr_cagr_retained_rights into v_csr_rr_rec;
1836: fetch csr_cagr_retained_rights into v_csr_rr_rec;
1837: exit when csr_cagr_retained_rights%notfound;
1838:
1839: if v_csr_rr_rec.OIPL_ID <> 0 and v_csr_rr_rec.eligy_prfl_id <> 0 then
1840: l_source_name := per_cagr_utility_pkg.get_elig_source(v_csr_rr_rec.eligy_prfl_id
1841: ,v_csr_rr_rec.formula_id
1842: ,p_params.effective_date);
1843: else
1844: l_source_name := '*** Default ***';
1856: v_from_step_id := NULL;
1857: v_to_step_id := NULL;
1858:
1859: if v_csr_rr_rec.CAGR_ENTITLEMENT_LINE_ID is null then -- ent retained right
1860: per_cagr_utility_pkg.put_log(' found retained entitlement: '||l_source_name,1);
1861: v_units_of_measure := v_csr_rr_rec.UNITS_OF_MEASURE;
1862:
1863: per_cagr_utility_pkg.put_log(' Retained Right is for entitlement: '|| v_csr_rr_rec.cagr_entitlement_id);
1864:
1859: if v_csr_rr_rec.CAGR_ENTITLEMENT_LINE_ID is null then -- ent retained right
1860: per_cagr_utility_pkg.put_log(' found retained entitlement: '||l_source_name,1);
1861: v_units_of_measure := v_csr_rr_rec.UNITS_OF_MEASURE;
1862:
1863: per_cagr_utility_pkg.put_log(' Retained Right is for entitlement: '|| v_csr_rr_rec.cagr_entitlement_id);
1864:
1865: if v_csr_rr_rec.freeze_flag = 'N' then
1866: -- not frozen, so get the latest value from the latest formula_id
1867: -- for the retained entitlement
1864:
1865: if v_csr_rr_rec.freeze_flag = 'N' then
1866: -- not frozen, so get the latest value from the latest formula_id
1867: -- for the retained entitlement
1868: per_cagr_utility_pkg.put_log(' Retained Right is not frozen');
1869: open csr_cagr_ents(v_csr_rr_rec.CAGR_ENTITLEMENT_ID);
1870: fetch csr_cagr_ents into v_cagr_ents_rec;
1871: if csr_cagr_ents%found then
1872: v_units_of_measure := v_cagr_ents_rec.UNITS_OF_MEASURE;
1885: v_range_to := l_cagr_FF_record.range_to;
1886: v_write_flag := TRUE;
1887: else
1888: -- log message as the formula evaluated to null and continue with next entitlement record
1889: per_cagr_utility_pkg.put_log(' Fast Formula did not determine an eligible entitlement.',1);
1890: v_write_flag := FALSE;
1891: end if;
1892: elsif v_csr_rr_rec.category_name = 'PYS' then
1893: if l_cagr_FF_record.grade_spine_id is not null
1900: v_to_step_id := l_cagr_FF_record.to_step_id;
1901: v_write_flag := TRUE;
1902: else
1903: -- log error as the formula didn't evaluate and continue with next entitlement record
1904: per_cagr_utility_pkg.put_log(' ERROR: Fast Formula failed to produce expected output',1);
1905: v_write_flag := FALSE;
1906: end if;
1907: end if;
1908: end if;
1910:
1911: elsif v_csr_rr_rec.freeze_flag = 'Y' then
1912: -- frozen, so assign use the frozen value (instead of re-evaluating formula)
1913: -- and trigger this retained right entitlement result to be added to the process set.
1914: per_cagr_utility_pkg.put_log(' Retained Right is frozen');
1915: v_value := v_csr_rr_rec.value;
1916: v_range_from := v_csr_rr_rec.range_from;
1917: v_range_to := v_csr_rr_rec.range_to;
1918: v_units_of_measure := v_csr_rr_rec.units_of_measure;
1924: v_write_flag := TRUE;
1925: end if;
1926:
1927: else -- ent line retained right
1928: per_cagr_utility_pkg.put_log(' found retained criteria line: '||l_source_name,1);
1929: per_cagr_utility_pkg.put_log(' criteria line_id: '|| v_csr_rr_rec.cagr_entitlement_line_id);
1930:
1931: if v_csr_rr_rec.freeze_flag = 'N' then
1932: per_cagr_utility_pkg.put_log(' Retained Right is not frozen');
1925: end if;
1926:
1927: else -- ent line retained right
1928: per_cagr_utility_pkg.put_log(' found retained criteria line: '||l_source_name,1);
1929: per_cagr_utility_pkg.put_log(' criteria line_id: '|| v_csr_rr_rec.cagr_entitlement_line_id);
1930:
1931: if v_csr_rr_rec.freeze_flag = 'N' then
1932: per_cagr_utility_pkg.put_log(' Retained Right is not frozen');
1933: -- not frozen, so get the latest values for the item line
1928: per_cagr_utility_pkg.put_log(' found retained criteria line: '||l_source_name,1);
1929: per_cagr_utility_pkg.put_log(' criteria line_id: '|| v_csr_rr_rec.cagr_entitlement_line_id);
1930:
1931: if v_csr_rr_rec.freeze_flag = 'N' then
1932: per_cagr_utility_pkg.put_log(' Retained Right is not frozen');
1933: -- not frozen, so get the latest values for the item line
1934: open csr_cagr_lines(v_csr_rr_rec.cagr_entitlement_line_id, v_csr_rr_rec.collective_agreement_id);
1935: fetch csr_cagr_lines into v_cagr_lines_rec;
1936: if csr_cagr_lines%found then
1948: else
1949: close csr_cagr_lines;
1950: end if;
1951: elsif v_csr_rr_rec.freeze_flag = 'Y' then
1952: per_cagr_utility_pkg.put_log(' Retained Right is frozen');
1953: -- frozen, so use the values that was saved
1954: -- on the retained right start date.
1955: v_value := v_csr_rr_rec.value;
1956: v_range_from := v_csr_rr_rec.range_from;
2029: If v_dup_record is not null then
2030: -- delete the duplicate result and put RR in its place.
2031: p_structure.delete(v_dup_record);
2032: v_counter := v_dup_record;
2033: per_cagr_utility_pkg.put_log(' Removed duplicate result for this retained right.');
2034: End If;
2035: If v_counter is null then
2036: p_counter := p_counter +1;
2037: v_counter := p_counter;
2070: p_structure(v_counter).RETAINED_ENT_RESULT_ID := v_csr_rr_rec.CAGR_ENTITLEMENT_RESULT_ID;
2071: end if;
2072: END LOOP;
2073: close csr_cagr_retained_rights;
2074: per_cagr_utility_pkg.put_log(' Completed related Retained Rights.',1);
2075: hr_utility.set_location('Leaving:'||l_proc, 50);
2076:
2077: END add_related_ret_rights;
2078:
2187:
2188: BEGIN
2189:
2190: hr_utility.set_location('Entering:'||l_proc, 10);
2191: per_cagr_utility_pkg.put_log(' Evaluating Retained Rights for other items',1);
2192: open csr_cagr_other_ret_rights;
2193: LOOP
2194: fetch csr_cagr_other_ret_rights into v_csr_rr_rec;
2195: exit when csr_cagr_other_ret_rights%notfound;
2228:
2229: if v_rule_inconclusive then
2230: -- output warning message that beneficial could not be chosen
2231: -- and write results anyway, if profile option allows
2232: per_cagr_utility_pkg.put_log(' ERROR: Beneficial Rule was inconclusive',1);
2233: end if;
2234: update_result_set(t_results_table,p_params,'W');
2235: v_counter := 0;
2236: t_results_table.delete;
2288: v_range_to := l_cagr_FF_record.range_to;
2289: v_write_flag := TRUE;
2290: else
2291: -- log message as the formula evaluated to null and continue with next entitlement record
2292: per_cagr_utility_pkg.put_log(' Fast Formula did not determine an eligible entitlement.',1);
2293: v_write_flag := FALSE;
2294: end if;
2295: elsif v_csr_rr_rec.category_name = 'PYS' then
2296: if l_cagr_FF_record.grade_spine_id is not null
2303: v_to_step_id := l_cagr_FF_record.to_step_id;
2304: v_write_flag := TRUE;
2305: else
2306: -- log error as the formula didn't evaluate and continue with next entitlement record
2307: per_cagr_utility_pkg.put_log(' ERROR: Fast Formula failed to produce expected output',1);
2308: v_write_flag := FALSE;
2309: end if;
2310: end if;
2311: end if;
2420: -- and clear the plsql table.
2421: if v_rule_inconclusive then
2422: -- output warning message that beneficial could not be chosen
2423: -- and write results anyway..
2424: per_cagr_utility_pkg.put_log(' ERROR: Beneficial Rule was inconclusive',1);
2425: end if;
2426: update_result_set(t_results_table,p_params,'W');
2427: t_results_table.delete;
2428: v_counter := 0;
2426: update_result_set(t_results_table,p_params,'W');
2427: t_results_table.delete;
2428: v_counter := 0;
2429: end if;
2430: per_cagr_utility_pkg.put_log(' Completed Retained Rights for other items.',1);
2431: hr_utility.set_location('Leaving:'||l_proc, 50);
2432:
2433: END add_other_ret_rights;
2434:
2438:
2439: BEGIN
2440:
2441: hr_utility.set_location('Entering:'||l_proc, 5);
2442: per_cagr_utility_pkg.put_log(g_separator,1);
2443: per_cagr_utility_pkg.put_log('Starting Evaluation Process ('||fnd_date.date_to_canonical(sysdate)||')',1);
2444: --
2445: -- choose which cursor to open,
2446: -- depending upon operation mode
2439: BEGIN
2440:
2441: hr_utility.set_location('Entering:'||l_proc, 5);
2442: per_cagr_utility_pkg.put_log(g_separator,1);
2443: per_cagr_utility_pkg.put_log('Starting Evaluation Process ('||fnd_date.date_to_canonical(sysdate)||')',1);
2444: --
2445: -- choose which cursor to open,
2446: -- depending upon operation mode
2447: --
2479: ,p_eligibility_table => t_eligibility_table
2480: ,p_counter => v_eligibility_counter);
2481:
2482: else
2483: per_cagr_utility_pkg.put_log(' No active criteria lines found for the collective agreement.',1);
2484: close csr_SA_drive_benmngle;
2485: end if;
2486:
2487: -- open the cursor, to get current entitlements
2525:
2526: if v_rule_inconclusive then
2527: -- output warning message that beneficial could not be chosen
2528: -- and write results anyway..
2529: per_cagr_utility_pkg.put_log(' ERROR: Beneficial Rule was inconclusive',1);
2530: end if;
2531: apply_chosen_result(t_results_table,t_chosen_table,p_params.commit_flag);
2532: update_result_set(t_results_table,p_params,'W');
2533: v_counter := 0;
2537: v_last_dataitem_id := v_ents.CAGR_ENTITLEMENT_ITEM_ID;
2538: -- store new beneficial_rule
2539: v_beneficial_rule := v_ents.BENEFICIAL_RULE;
2540: v_beneficial_rule_vs_id := v_ents.BENEFICIAL_RULE_VALUE_SET_ID;
2541: per_cagr_utility_pkg.put_log(' ',1);
2542: per_cagr_utility_pkg.put_log(' Found active entitlement for item: '||v_ents.item_name,1);
2543: end if;
2544: else
2545: --dbms_output.put_line('first dataitem');
2538: -- store new beneficial_rule
2539: v_beneficial_rule := v_ents.BENEFICIAL_RULE;
2540: v_beneficial_rule_vs_id := v_ents.BENEFICIAL_RULE_VALUE_SET_ID;
2541: per_cagr_utility_pkg.put_log(' ',1);
2542: per_cagr_utility_pkg.put_log(' Found active entitlement for item: '||v_ents.item_name,1);
2543: end if;
2544: else
2545: --dbms_output.put_line('first dataitem');
2546: -- set dataitem and beneficial rule value on first iteration
2546: -- set dataitem and beneficial rule value on first iteration
2547: v_last_dataitem_id := v_ents.CAGR_ENTITLEMENT_ITEM_ID;
2548: v_beneficial_rule := v_ents.BENEFICIAL_RULE;
2549: v_beneficial_rule_vs_id := v_ents.BENEFICIAL_RULE_VALUE_SET_ID;
2550: per_cagr_utility_pkg.put_log(' ',1);
2551: per_cagr_utility_pkg.put_log(' Found active entitlement for item: '||v_ents.item_name,1);
2552: end if;
2553:
2554: v_value := NULL; -- clear result variables before eval
2547: v_last_dataitem_id := v_ents.CAGR_ENTITLEMENT_ITEM_ID;
2548: v_beneficial_rule := v_ents.BENEFICIAL_RULE;
2549: v_beneficial_rule_vs_id := v_ents.BENEFICIAL_RULE_VALUE_SET_ID;
2550: per_cagr_utility_pkg.put_log(' ',1);
2551: per_cagr_utility_pkg.put_log(' Found active entitlement for item: '||v_ents.item_name,1);
2552: end if;
2553:
2554: v_value := NULL; -- clear result variables before eval
2555: v_range_from := NULL;
2563: -- determine whether current record is just entitlement item
2564: -- or entitlement line, and exec ff accordingly...
2565: if v_ents.formula_criteria = 'C' then -- ent line record
2566: if v_ents.OIPL_ID <> 0 and v_ents.eligy_prfl_id <> 0 then
2567: l_source_name := per_cagr_utility_pkg.get_elig_source(v_ents.eligy_prfl_id
2568: ,NULL
2569: ,p_params.effective_date);
2570: else
2571: l_source_name := '*** Default ***';
2569: ,p_params.effective_date);
2570: else
2571: l_source_name := '*** Default ***';
2572: end if;
2573: per_cagr_utility_pkg.put_log(' Evaluating eligibility for criteria line: '||l_source_name,1);
2574: per_cagr_utility_pkg.put_log(' entitlement_id: '||v_ents.cagr_entitlement_id||', entitlement_line_id: '||v_ents.cagr_entitlement_line_id);
2575:
2576: if v_ents.OIPL_ID = 0 and v_ents.eligy_prfl_id = 0 then
2577: -- write the record as this is default elig line
2570: else
2571: l_source_name := '*** Default ***';
2572: end if;
2573: per_cagr_utility_pkg.put_log(' Evaluating eligibility for criteria line: '||l_source_name,1);
2574: per_cagr_utility_pkg.put_log(' entitlement_id: '||v_ents.cagr_entitlement_id||', entitlement_line_id: '||v_ents.cagr_entitlement_line_id);
2575:
2576: if v_ents.OIPL_ID = 0 and v_ents.eligy_prfl_id = 0 then
2577: -- write the record as this is default elig line
2578: v_value := v_ents.value;
2603: end if;
2604: else
2605: -- log error that there are no BEN eligibility result records returned
2606: -- from benmngle, for this compensation_object
2607: per_cagr_utility_pkg.put_log(' ERROR: No eligibility results were generated for the assignment',1);
2608: end if;
2609: end if;
2610:
2611: if v_ents.category_name = 'PYS' and v_write_flag = TRUE then
2612: -- check the asg grade matches the grade_spine grade, as well as elig profile
2613: -- being satisfied, in order to be eligible for this PYS criteria.
2614: if nvl(v_ents.grade_id,-2) <> nvl(get_PYS_grade_id (v_ents.grade_spine_id
2615: ,p_params.effective_date),-1) then
2616: per_cagr_utility_pkg.put_log(' Criteria line is ineligible as the assignment is not on the grade spine. ',1);
2617: v_write_flag := FALSE;
2618: end if;
2619: end if;
2620:
2619: end if;
2620:
2621: elsif v_ents.formula_criteria = 'F' then -- ent record
2622: if v_ents.FORMULA_ID is not null then
2623: per_cagr_utility_pkg.put_log(' entitlement_id: '||v_ents.cagr_entitlement_id);
2624: l_source_name := per_cagr_utility_pkg.get_elig_source(NULL
2625: ,v_ents.FORMULA_ID
2626: ,p_params.effective_date);
2627: per_cagr_utility_pkg.put_log(' Evaluating entitlement fast formula: '||l_source_name,1);
2620:
2621: elsif v_ents.formula_criteria = 'F' then -- ent record
2622: if v_ents.FORMULA_ID is not null then
2623: per_cagr_utility_pkg.put_log(' entitlement_id: '||v_ents.cagr_entitlement_id);
2624: l_source_name := per_cagr_utility_pkg.get_elig_source(NULL
2625: ,v_ents.FORMULA_ID
2626: ,p_params.effective_date);
2627: per_cagr_utility_pkg.put_log(' Evaluating entitlement fast formula: '||l_source_name,1);
2628:
2623: per_cagr_utility_pkg.put_log(' entitlement_id: '||v_ents.cagr_entitlement_id);
2624: l_source_name := per_cagr_utility_pkg.get_elig_source(NULL
2625: ,v_ents.FORMULA_ID
2626: ,p_params.effective_date);
2627: per_cagr_utility_pkg.put_log(' Evaluating entitlement fast formula: '||l_source_name,1);
2628:
2629:
2630: hr_cagr_ff_pkg.cagr_entitlement_ff(p_formula_id => v_ents.FORMULA_ID
2631: ,p_effective_date => p_params.effective_date
2641: v_range_to := l_cagr_FF_record.range_to;
2642: v_write_flag := TRUE;
2643: else
2644: -- log message as the formula evaluated to null and continue with next entitlement record
2645: per_cagr_utility_pkg.put_log(' Fast Formula did not determine an eligible entitlement.',1);
2646: v_write_flag := FALSE;
2647: end if;
2648: elsif v_ents.category_name = 'PYS' then
2649: if l_cagr_FF_record.grade_spine_id is not null
2656: v_to_step_id := l_cagr_FF_record.to_step_id;
2657: v_write_flag := TRUE;
2658: else
2659: -- log message as the formula didn't evaluated to null and continue with next entitlement record
2660: per_cagr_utility_pkg.put_log(' Fast Formula did not determine an eligible entitlement.',1);
2661: end if;
2662: end if;
2663: end if;
2664: end if;
2733:
2734: if v_rule_inconclusive then
2735: -- output warning message that beneficial could not be chosen
2736: -- and write results anyway..
2737: per_cagr_utility_pkg.put_log(' ERROR: Beneficial Rule was inconclusive',1);
2738: end if;
2739: apply_chosen_result(t_results_table,t_chosen_table,p_params.commit_flag);
2740: update_result_set(t_results_table,p_params,'W');
2741: t_results_table.delete;
2741: t_results_table.delete;
2742: v_counter := 0;
2743: end if;
2744: else
2745: per_cagr_utility_pkg.put_log(' No active entitlements found for the collective agreement.',1);
2746: end if;
2747:
2748: -- when not in SE mode, also need to add in any other retained rights for dataitems
2749: -- that are not related to the dataitems returned by the current entitlements
2787: ,p_params.effective_date);
2788: end if;
2789:
2790: if nvl(fnd_profile.value('PER_CHECK_ENTITLEMENT_CACHE'),'N') = 'Y' and v_primary_flag then
2791: per_cagr_utility_pkg.put_log(' Profile value set to check entitlement cache before evaluating');
2792:
2793: -- check the cache
2794: p_SE_rec := check_cache(p_params.assignment_id
2795: ,per_cagr_utility_pkg.get_collective_agreement_id(p_params.assignment_id,p_params.effective_date)
2791: per_cagr_utility_pkg.put_log(' Profile value set to check entitlement cache before evaluating');
2792:
2793: -- check the cache
2794: p_SE_rec := check_cache(p_params.assignment_id
2795: ,per_cagr_utility_pkg.get_collective_agreement_id(p_params.assignment_id,p_params.effective_date)
2796: ,p_params.entitlement_item_id
2797: ,p_params.effective_date);
2798:
2799: l_cache_checked := TRUE;
2803: l_evaluate := TRUE;
2804: end if;
2805: else
2806: p_SE_rec.error := 'HR_289577_CAGR_NO_DATA_FOUND';
2807: per_cagr_utility_pkg.put_log(' Profile value set to always re-evaluate');
2808: l_evaluate := TRUE;
2809: end if;
2810:
2811:
2834: ,p_eligibility_table => t_eligibility_table
2835: ,p_counter => v_eligibility_counter);
2836:
2837: else
2838: per_cagr_utility_pkg.put_log(' No active entitlement lines exist for collective agreement');
2839: close csr_SE_drive_benmngle;
2840: end if;
2841:
2842: -- open the cursor, to get single entitlement data
2858: v_to_step_id := NULL;
2859:
2860: -- determine whether current record is entitlement item (so run ff) or entitlement line
2861: if v_ents.formula_criteria = 'C' then -- line item record
2862: per_cagr_utility_pkg.put_log(' Processing entitlement: '||v_ents.cagr_entitlement_id||' '||v_ents.item_name
2863: ||', entitlement line: '||v_ents.cagr_entitlement_line_id);
2864:
2865: if v_ents.OIPL_ID = 0 and v_ents.eligy_prfl_id = 0 then
2866: -- write the record as this is default elig line
2891: v_write_flag := TRUE;
2892: end if;
2893: else
2894: -- log error that there are no BEN eligibility result records returned by benmngle
2895: per_cagr_utility_pkg.put_log(' ERROR: No eligibility results were generated for the assignment',1);
2896: end if;
2897: end if;
2898:
2899: if v_ents.category_name = 'PYS' and v_write_flag = TRUE then
2900: -- check the asg grade matches the grade_spine grade, as well as elig profile
2901: -- being satisfied, in order to be eligible for this PYS criteria.
2902: if nvl(v_ents.grade_id,-2) <> nvl(get_PYS_grade_id (v_ents.grade_spine_id
2903: ,p_params.effective_date),-1) then
2904: per_cagr_utility_pkg.put_log(' Criteria line is ineligible as the assignment is not on the grade spine. ',1);
2905: v_write_flag := FALSE;
2906: end if;
2907: end if;
2908:
2907: end if;
2908:
2909: elsif v_ents.formula_criteria = 'F' then -- item record
2910: if v_ents.FORMULA_ID is not null then
2911: per_cagr_utility_pkg.put_log(' Processing entitlement: '||v_ents.cagr_entitlement_id||' '
2912: ||v_ents.item_name||', calling ff');
2913:
2914: hr_cagr_ff_pkg.cagr_entitlement_ff(p_formula_id => v_ents.FORMULA_ID
2915: ,p_effective_date => p_params.effective_date
2925: v_range_to := l_cagr_FF_record.range_to;
2926: v_write_flag := TRUE;
2927: else
2928: -- log message as the formula evaluated to null and continue with next entitlement record
2929: per_cagr_utility_pkg.put_log(' Fast Formula did not determine an eligible entitlement.',1);
2930: v_write_flag := FALSE;
2931: end if;
2932: elsif v_ents.category_name = 'PYS' then
2933: if l_cagr_FF_record.grade_spine_id is not null
2940: v_to_step_id := l_cagr_FF_record.to_step_id;
2941: v_write_flag := TRUE;
2942: else
2943: -- log message as the formula didn't evaluated to null and continue with next entitlement record
2944: per_cagr_utility_pkg.put_log(' Fast Formula did not determine an eligible entitlement.',1);
2945: end if;
2946: end if;
2947: end if;
2948: end if;
3008:
3009: if not(l_cache_checked) then
3010: -- check the cache, if it wasn't done above
3011: p_SE_rec := check_cache(p_params.assignment_id
3012: ,per_cagr_utility_pkg.get_collective_agreement_id(p_params.assignment_id,p_params.effective_date)
3013: ,p_params.entitlement_item_id
3014: ,p_params.effective_date);
3015: end if;
3016:
3035: p_SE_rec.ERROR := NULL; -- do not return this error
3036: end if;
3037: EXCEPTION
3038: when resource_busy then
3039: per_cagr_utility_pkg.put_log(' WARNING: unable to obtain exclusive lock on per_cagr_entitlement_results');
3040: per_cagr_utility_pkg.put_log(' Cache was not updated with results, continuing...');
3041: -- but this is not fatal in this mode, so continue and pass out results
3042: END;
3043: else -- not updating the cache, but we have regenerated data so nullify this error now
3036: end if;
3037: EXCEPTION
3038: when resource_busy then
3039: per_cagr_utility_pkg.put_log(' WARNING: unable to obtain exclusive lock on per_cagr_entitlement_results');
3040: per_cagr_utility_pkg.put_log(' Cache was not updated with results, continuing...');
3041: -- but this is not fatal in this mode, so continue and pass out results
3042: END;
3043: else -- not updating the cache, but we have regenerated data so nullify this error now
3044: if p_SE_rec.error = 'HR_289577_CAGR_NO_DATA_FOUND' and t_results_table.count > 0 then
3082: -- return error if called for secondary asg
3083: if not(v_primary_flag) then
3084: p_SE_rec.ERROR := 'HR_289579_CAGR_SECONDARY_ASG';
3085: end if;
3086: per_cagr_utility_pkg.put_log(' Single Entitlement Mode return values: ');
3087:
3088: -- log return values...
3089: per_cagr_utility_pkg.put_log(' VALUE: '|| p_SE_rec.VALUE);
3090: per_cagr_utility_pkg.put_log(' RANGE_FROM: '|| p_SE_rec.RANGE_FROM);
3085: end if;
3086: per_cagr_utility_pkg.put_log(' Single Entitlement Mode return values: ');
3087:
3088: -- log return values...
3089: per_cagr_utility_pkg.put_log(' VALUE: '|| p_SE_rec.VALUE);
3090: per_cagr_utility_pkg.put_log(' RANGE_FROM: '|| p_SE_rec.RANGE_FROM);
3091: per_cagr_utility_pkg.put_log(' RANGE_TO: '|| p_SE_rec.RANGE_TO);
3092: per_cagr_utility_pkg.put_log(' GRADE_SPINE_ID: '|| p_SE_rec.GRADE_SPINE_ID);
3093: per_cagr_utility_pkg.put_log(' PARENT_SPINE_ID: '|| p_SE_rec.PARENT_SPINE_ID);
3086: per_cagr_utility_pkg.put_log(' Single Entitlement Mode return values: ');
3087:
3088: -- log return values...
3089: per_cagr_utility_pkg.put_log(' VALUE: '|| p_SE_rec.VALUE);
3090: per_cagr_utility_pkg.put_log(' RANGE_FROM: '|| p_SE_rec.RANGE_FROM);
3091: per_cagr_utility_pkg.put_log(' RANGE_TO: '|| p_SE_rec.RANGE_TO);
3092: per_cagr_utility_pkg.put_log(' GRADE_SPINE_ID: '|| p_SE_rec.GRADE_SPINE_ID);
3093: per_cagr_utility_pkg.put_log(' PARENT_SPINE_ID: '|| p_SE_rec.PARENT_SPINE_ID);
3094: per_cagr_utility_pkg.put_log(' STEP_ID: '|| p_SE_rec.STEP_ID);
3087:
3088: -- log return values...
3089: per_cagr_utility_pkg.put_log(' VALUE: '|| p_SE_rec.VALUE);
3090: per_cagr_utility_pkg.put_log(' RANGE_FROM: '|| p_SE_rec.RANGE_FROM);
3091: per_cagr_utility_pkg.put_log(' RANGE_TO: '|| p_SE_rec.RANGE_TO);
3092: per_cagr_utility_pkg.put_log(' GRADE_SPINE_ID: '|| p_SE_rec.GRADE_SPINE_ID);
3093: per_cagr_utility_pkg.put_log(' PARENT_SPINE_ID: '|| p_SE_rec.PARENT_SPINE_ID);
3094: per_cagr_utility_pkg.put_log(' STEP_ID: '|| p_SE_rec.STEP_ID);
3095: per_cagr_utility_pkg.put_log(' FROM_STEP_ID: '|| p_SE_rec.FROM_STEP_ID);
3088: -- log return values...
3089: per_cagr_utility_pkg.put_log(' VALUE: '|| p_SE_rec.VALUE);
3090: per_cagr_utility_pkg.put_log(' RANGE_FROM: '|| p_SE_rec.RANGE_FROM);
3091: per_cagr_utility_pkg.put_log(' RANGE_TO: '|| p_SE_rec.RANGE_TO);
3092: per_cagr_utility_pkg.put_log(' GRADE_SPINE_ID: '|| p_SE_rec.GRADE_SPINE_ID);
3093: per_cagr_utility_pkg.put_log(' PARENT_SPINE_ID: '|| p_SE_rec.PARENT_SPINE_ID);
3094: per_cagr_utility_pkg.put_log(' STEP_ID: '|| p_SE_rec.STEP_ID);
3095: per_cagr_utility_pkg.put_log(' FROM_STEP_ID: '|| p_SE_rec.FROM_STEP_ID);
3096: per_cagr_utility_pkg.put_log(' TO_STEP_ID: '|| p_SE_rec.TO_STEP_ID);
3089: per_cagr_utility_pkg.put_log(' VALUE: '|| p_SE_rec.VALUE);
3090: per_cagr_utility_pkg.put_log(' RANGE_FROM: '|| p_SE_rec.RANGE_FROM);
3091: per_cagr_utility_pkg.put_log(' RANGE_TO: '|| p_SE_rec.RANGE_TO);
3092: per_cagr_utility_pkg.put_log(' GRADE_SPINE_ID: '|| p_SE_rec.GRADE_SPINE_ID);
3093: per_cagr_utility_pkg.put_log(' PARENT_SPINE_ID: '|| p_SE_rec.PARENT_SPINE_ID);
3094: per_cagr_utility_pkg.put_log(' STEP_ID: '|| p_SE_rec.STEP_ID);
3095: per_cagr_utility_pkg.put_log(' FROM_STEP_ID: '|| p_SE_rec.FROM_STEP_ID);
3096: per_cagr_utility_pkg.put_log(' TO_STEP_ID: '|| p_SE_rec.TO_STEP_ID);
3097: per_cagr_utility_pkg.put_log(' ERROR: '|| p_SE_rec.ERROR);
3090: per_cagr_utility_pkg.put_log(' RANGE_FROM: '|| p_SE_rec.RANGE_FROM);
3091: per_cagr_utility_pkg.put_log(' RANGE_TO: '|| p_SE_rec.RANGE_TO);
3092: per_cagr_utility_pkg.put_log(' GRADE_SPINE_ID: '|| p_SE_rec.GRADE_SPINE_ID);
3093: per_cagr_utility_pkg.put_log(' PARENT_SPINE_ID: '|| p_SE_rec.PARENT_SPINE_ID);
3094: per_cagr_utility_pkg.put_log(' STEP_ID: '|| p_SE_rec.STEP_ID);
3095: per_cagr_utility_pkg.put_log(' FROM_STEP_ID: '|| p_SE_rec.FROM_STEP_ID);
3096: per_cagr_utility_pkg.put_log(' TO_STEP_ID: '|| p_SE_rec.TO_STEP_ID);
3097: per_cagr_utility_pkg.put_log(' ERROR: '|| p_SE_rec.ERROR);
3098: --
3091: per_cagr_utility_pkg.put_log(' RANGE_TO: '|| p_SE_rec.RANGE_TO);
3092: per_cagr_utility_pkg.put_log(' GRADE_SPINE_ID: '|| p_SE_rec.GRADE_SPINE_ID);
3093: per_cagr_utility_pkg.put_log(' PARENT_SPINE_ID: '|| p_SE_rec.PARENT_SPINE_ID);
3094: per_cagr_utility_pkg.put_log(' STEP_ID: '|| p_SE_rec.STEP_ID);
3095: per_cagr_utility_pkg.put_log(' FROM_STEP_ID: '|| p_SE_rec.FROM_STEP_ID);
3096: per_cagr_utility_pkg.put_log(' TO_STEP_ID: '|| p_SE_rec.TO_STEP_ID);
3097: per_cagr_utility_pkg.put_log(' ERROR: '|| p_SE_rec.ERROR);
3098: --
3099: --
3092: per_cagr_utility_pkg.put_log(' GRADE_SPINE_ID: '|| p_SE_rec.GRADE_SPINE_ID);
3093: per_cagr_utility_pkg.put_log(' PARENT_SPINE_ID: '|| p_SE_rec.PARENT_SPINE_ID);
3094: per_cagr_utility_pkg.put_log(' STEP_ID: '|| p_SE_rec.STEP_ID);
3095: per_cagr_utility_pkg.put_log(' FROM_STEP_ID: '|| p_SE_rec.FROM_STEP_ID);
3096: per_cagr_utility_pkg.put_log(' TO_STEP_ID: '|| p_SE_rec.TO_STEP_ID);
3097: per_cagr_utility_pkg.put_log(' ERROR: '|| p_SE_rec.ERROR);
3098: --
3099: --
3100: elsif p_params.operation_mode = 'SC' then
3093: per_cagr_utility_pkg.put_log(' PARENT_SPINE_ID: '|| p_SE_rec.PARENT_SPINE_ID);
3094: per_cagr_utility_pkg.put_log(' STEP_ID: '|| p_SE_rec.STEP_ID);
3095: per_cagr_utility_pkg.put_log(' FROM_STEP_ID: '|| p_SE_rec.FROM_STEP_ID);
3096: per_cagr_utility_pkg.put_log(' TO_STEP_ID: '|| p_SE_rec.TO_STEP_ID);
3097: per_cagr_utility_pkg.put_log(' ERROR: '|| p_SE_rec.ERROR);
3098: --
3099: --
3100: elsif p_params.operation_mode = 'SC' then
3101: --
3131: ,p_eligibility_table => t_eligibility_table
3132: ,p_counter => v_eligibility_counter);
3133:
3134: else
3135: per_cagr_utility_pkg.put_log(' No assignments use the collective agreement',1);
3136: per_cagr_utility_pkg.put_log(' or no active criteria lines found for the collective agreement.',1);
3137: close csr_SC_drive_benmngle;
3138: end if;
3139: g_benefit_action_id := v_benefit_action_id; -- Bug 13703461
3132: ,p_counter => v_eligibility_counter);
3133:
3134: else
3135: per_cagr_utility_pkg.put_log(' No assignments use the collective agreement',1);
3136: per_cagr_utility_pkg.put_log(' or no active criteria lines found for the collective agreement.',1);
3137: close csr_SC_drive_benmngle;
3138: end if;
3139: g_benefit_action_id := v_benefit_action_id; -- Bug 13703461
3140:
3137: close csr_SC_drive_benmngle;
3138: end if;
3139: g_benefit_action_id := v_benefit_action_id; -- Bug 13703461
3140:
3141: per_cagr_utility_pkg.put_log('Processing the following assignments on the collective agreement: ',1);
3142: --
3143: -- load all the assignment ids to be processed into pl/sql table.
3144: --
3145: open csr_assignments_to_process;
3146: loop
3147: v_counter := v_counter+1;
3148: fetch csr_assignments_to_process into t_assignments_table(v_counter);
3149: exit when csr_assignments_to_process%notfound;
3150: per_cagr_utility_pkg.put_log(' '||t_assignments_table(v_counter).assignment_id,1);
3151: end loop;
3152: close csr_assignments_to_process;
3153: v_counter := 0;
3154:
3152: close csr_assignments_to_process;
3153: v_counter := 0;
3154:
3155: -- write the log out and save the request_id
3156: per_cagr_utility_pkg.write_log_file(p_params.cagr_request_id);
3157: l_parent_request_id := p_params.cagr_request_id;
3158:
3159: -- could now break pl/sql table into varray subsets, ready for multiple threads
3160:
3170: -- 2) clean results cache
3171: -- 2) do SA style cursor processing and logging (in a function for the SA code)
3172: -- 3) commit AFTER each assignment
3173:
3174: per_cagr_utility_pkg.create_cagr_request(p_process_date => p_params.effective_date
3175: ,p_operation_mode => 'SA'
3176: ,p_business_group_id => p_params.business_group_id
3177: ,p_assignment_id => t_assignments_table(k).assignment_id
3178: ,p_assignment_set_id => NULL
3185: ,p_commit_flag => p_params.commit_flag
3186: ,p_denormalise_flag => p_params.denormalise_flag
3187: ,p_cagr_request_id => p_params.cagr_request_id);
3188: -- output log header
3189: per_cagr_utility_pkg.put_log(g_head_separator,1);
3190: per_cagr_utility_pkg.put_log('----------- Collective Agreement Process Log ('||fnd_date.date_to_canonical(sysdate)||') -----------',1);
3191: per_cagr_utility_pkg.put_log(g_head_separator,1);
3192: per_cagr_utility_pkg.put_log(' ',1);
3193: per_cagr_utility_pkg.put_log(' Evaluating Assignment '|| t_assignments_table(k).assignment_id ||
3186: ,p_denormalise_flag => p_params.denormalise_flag
3187: ,p_cagr_request_id => p_params.cagr_request_id);
3188: -- output log header
3189: per_cagr_utility_pkg.put_log(g_head_separator,1);
3190: per_cagr_utility_pkg.put_log('----------- Collective Agreement Process Log ('||fnd_date.date_to_canonical(sysdate)||') -----------',1);
3191: per_cagr_utility_pkg.put_log(g_head_separator,1);
3192: per_cagr_utility_pkg.put_log(' ',1);
3193: per_cagr_utility_pkg.put_log(' Evaluating Assignment '|| t_assignments_table(k).assignment_id ||
3194: ' during Single Collective Agreement mode.',1);
3187: ,p_cagr_request_id => p_params.cagr_request_id);
3188: -- output log header
3189: per_cagr_utility_pkg.put_log(g_head_separator,1);
3190: per_cagr_utility_pkg.put_log('----------- Collective Agreement Process Log ('||fnd_date.date_to_canonical(sysdate)||') -----------',1);
3191: per_cagr_utility_pkg.put_log(g_head_separator,1);
3192: per_cagr_utility_pkg.put_log(' ',1);
3193: per_cagr_utility_pkg.put_log(' Evaluating Assignment '|| t_assignments_table(k).assignment_id ||
3194: ' during Single Collective Agreement mode.',1);
3195: per_cagr_utility_pkg.put_log(' Parent SC mode request id is: '||l_parent_request_id);
3188: -- output log header
3189: per_cagr_utility_pkg.put_log(g_head_separator,1);
3190: per_cagr_utility_pkg.put_log('----------- Collective Agreement Process Log ('||fnd_date.date_to_canonical(sysdate)||') -----------',1);
3191: per_cagr_utility_pkg.put_log(g_head_separator,1);
3192: per_cagr_utility_pkg.put_log(' ',1);
3193: per_cagr_utility_pkg.put_log(' Evaluating Assignment '|| t_assignments_table(k).assignment_id ||
3194: ' during Single Collective Agreement mode.',1);
3195: per_cagr_utility_pkg.put_log(' Parent SC mode request id is: '||l_parent_request_id);
3196: per_cagr_utility_pkg.put_log(' SA mode request id is: '||p_params.cagr_request_id);
3189: per_cagr_utility_pkg.put_log(g_head_separator,1);
3190: per_cagr_utility_pkg.put_log('----------- Collective Agreement Process Log ('||fnd_date.date_to_canonical(sysdate)||') -----------',1);
3191: per_cagr_utility_pkg.put_log(g_head_separator,1);
3192: per_cagr_utility_pkg.put_log(' ',1);
3193: per_cagr_utility_pkg.put_log(' Evaluating Assignment '|| t_assignments_table(k).assignment_id ||
3194: ' during Single Collective Agreement mode.',1);
3195: per_cagr_utility_pkg.put_log(' Parent SC mode request id is: '||l_parent_request_id);
3196: per_cagr_utility_pkg.put_log(' SA mode request id is: '||p_params.cagr_request_id);
3197: per_cagr_utility_pkg.put_log(' ',1);
3191: per_cagr_utility_pkg.put_log(g_head_separator,1);
3192: per_cagr_utility_pkg.put_log(' ',1);
3193: per_cagr_utility_pkg.put_log(' Evaluating Assignment '|| t_assignments_table(k).assignment_id ||
3194: ' during Single Collective Agreement mode.',1);
3195: per_cagr_utility_pkg.put_log(' Parent SC mode request id is: '||l_parent_request_id);
3196: per_cagr_utility_pkg.put_log(' SA mode request id is: '||p_params.cagr_request_id);
3197: per_cagr_utility_pkg.put_log(' ',1);
3198:
3199: If p_params.commit_flag = 'Y' then
3192: per_cagr_utility_pkg.put_log(' ',1);
3193: per_cagr_utility_pkg.put_log(' Evaluating Assignment '|| t_assignments_table(k).assignment_id ||
3194: ' during Single Collective Agreement mode.',1);
3195: per_cagr_utility_pkg.put_log(' Parent SC mode request id is: '||l_parent_request_id);
3196: per_cagr_utility_pkg.put_log(' SA mode request id is: '||p_params.cagr_request_id);
3197: per_cagr_utility_pkg.put_log(' ',1);
3198:
3199: If p_params.commit_flag = 'Y' then
3200: -- first create pl/sql table with chosen results for all items for the asg from cache, if committing.
3193: per_cagr_utility_pkg.put_log(' Evaluating Assignment '|| t_assignments_table(k).assignment_id ||
3194: ' during Single Collective Agreement mode.',1);
3195: per_cagr_utility_pkg.put_log(' Parent SC mode request id is: '||l_parent_request_id);
3196: per_cagr_utility_pkg.put_log(' SA mode request id is: '||p_params.cagr_request_id);
3197: per_cagr_utility_pkg.put_log(' ',1);
3198:
3199: If p_params.commit_flag = 'Y' then
3200: -- first create pl/sql table with chosen results for all items for the asg from cache, if committing.
3201: t_chosen_table := store_chosen_results(p_params.assignment_id
3249:
3250: if v_rule_inconclusive then
3251: -- output warning message that beneficial could not be chosen
3252: -- and write results anyway..
3253: per_cagr_utility_pkg.put_log(' ERROR: Beneficial Rule was inconclusive',1);
3254: end if;
3255: apply_chosen_result(t_results_table,t_chosen_table,p_params.commit_flag);
3256: update_result_set(t_results_table,p_params,'W');
3257: v_counter := 0;
3261: v_last_dataitem_id := v_ents.CAGR_ENTITLEMENT_ITEM_ID;
3262: -- store new beneficial_rule
3263: v_beneficial_rule := v_ents.BENEFICIAL_RULE;
3264: v_beneficial_rule_vs_id := v_ents.BENEFICIAL_RULE_VALUE_SET_ID;
3265: per_cagr_utility_pkg.put_log(' ',1);
3266: per_cagr_utility_pkg.put_log(' Found active entitlement for item: '||v_ents.item_name,1);
3267: end if;
3268: else
3269: --dbms_output.put_line('first dataitem');
3262: -- store new beneficial_rule
3263: v_beneficial_rule := v_ents.BENEFICIAL_RULE;
3264: v_beneficial_rule_vs_id := v_ents.BENEFICIAL_RULE_VALUE_SET_ID;
3265: per_cagr_utility_pkg.put_log(' ',1);
3266: per_cagr_utility_pkg.put_log(' Found active entitlement for item: '||v_ents.item_name,1);
3267: end if;
3268: else
3269: --dbms_output.put_line('first dataitem');
3270: -- set dataitem and beneficial rule value on first iteration
3270: -- set dataitem and beneficial rule value on first iteration
3271: v_last_dataitem_id := v_ents.CAGR_ENTITLEMENT_ITEM_ID;
3272: v_beneficial_rule := v_ents.BENEFICIAL_RULE;
3273: v_beneficial_rule_vs_id := v_ents.BENEFICIAL_RULE_VALUE_SET_ID;
3274: per_cagr_utility_pkg.put_log(' ',1);
3275: per_cagr_utility_pkg.put_log(' Found active entitlement for item: '||v_ents.item_name,1);
3276: end if;
3277:
3278: v_value := NULL; -- clear result variables before eval
3271: v_last_dataitem_id := v_ents.CAGR_ENTITLEMENT_ITEM_ID;
3272: v_beneficial_rule := v_ents.BENEFICIAL_RULE;
3273: v_beneficial_rule_vs_id := v_ents.BENEFICIAL_RULE_VALUE_SET_ID;
3274: per_cagr_utility_pkg.put_log(' ',1);
3275: per_cagr_utility_pkg.put_log(' Found active entitlement for item: '||v_ents.item_name,1);
3276: end if;
3277:
3278: v_value := NULL; -- clear result variables before eval
3279: v_range_from := NULL;
3287: -- determine whether current record is just entitlement item
3288: -- or entitlement line, and exec ff accordingly...
3289: if v_ents.formula_criteria = 'C' then -- ent line record
3290: if v_ents.OIPL_ID <> 0 and v_ents.eligy_prfl_id <> 0 then
3291: l_source_name := per_cagr_utility_pkg.get_elig_source(v_ents.eligy_prfl_id
3292: ,NULL
3293: ,p_params.effective_date);
3294: else
3295: l_source_name := '*** Default ***';
3293: ,p_params.effective_date);
3294: else
3295: l_source_name := '*** Default ***';
3296: end if;
3297: per_cagr_utility_pkg.put_log(' Evaluating eligibility for criteria line: '||l_source_name,1);
3298: per_cagr_utility_pkg.put_log(' entitlement_id: '||v_ents.cagr_entitlement_id||', entitlement_line_id: '
3299: ||v_ents.cagr_entitlement_line_id);
3300:
3301: if v_ents.OIPL_ID = 0 and v_ents.eligy_prfl_id = 0 then
3294: else
3295: l_source_name := '*** Default ***';
3296: end if;
3297: per_cagr_utility_pkg.put_log(' Evaluating eligibility for criteria line: '||l_source_name,1);
3298: per_cagr_utility_pkg.put_log(' entitlement_id: '||v_ents.cagr_entitlement_id||', entitlement_line_id: '
3299: ||v_ents.cagr_entitlement_line_id);
3300:
3301: if v_ents.OIPL_ID = 0 and v_ents.eligy_prfl_id = 0 then
3302: -- write the record as this is default elig line
3329: end if;
3330: else
3331: -- log error that there are no BEN eligibility result records returned
3332: -- from benmngle, for this compensation_object
3333: per_cagr_utility_pkg.put_log(' ERROR: No eligibility results were generated for the assignment',1);
3334: end if;
3335: end if;
3336: if v_ents.category_name = 'PYS' and v_write_flag = TRUE then
3337: -- check the asg grade matches the grade_spine grade, as well as elig profile
3337: -- check the asg grade matches the grade_spine grade, as well as elig profile
3338: -- being satisfied, in order to be eligible for this PYS criteria.
3339: if nvl(t_assignments_table(k).grade_id,-2) <> nvl(get_PYS_grade_id (v_ents.grade_spine_id
3340: ,p_params.effective_date),-1) then
3341: per_cagr_utility_pkg.put_log(' Criteria line is ineligible as the assignment is not on the grade spi
3342: ne. ',1);
3343: v_write_flag := FALSE;
3344: end if;
3345: end if;
3345: end if;
3346:
3347: elsif v_ents.formula_criteria = 'F' then -- ent record
3348: if v_ents.FORMULA_ID is not null then
3349: per_cagr_utility_pkg.put_log(' entitlement_id: '||v_ents.cagr_entitlement_id);
3350: l_source_name := per_cagr_utility_pkg.get_elig_source(NULL
3351: ,v_ents.FORMULA_ID
3352: ,p_params.effective_date);
3353: per_cagr_utility_pkg.put_log(' Evaluating entitlement fast formula: '||l_source_name,1);
3346:
3347: elsif v_ents.formula_criteria = 'F' then -- ent record
3348: if v_ents.FORMULA_ID is not null then
3349: per_cagr_utility_pkg.put_log(' entitlement_id: '||v_ents.cagr_entitlement_id);
3350: l_source_name := per_cagr_utility_pkg.get_elig_source(NULL
3351: ,v_ents.FORMULA_ID
3352: ,p_params.effective_date);
3353: per_cagr_utility_pkg.put_log(' Evaluating entitlement fast formula: '||l_source_name,1);
3354:
3349: per_cagr_utility_pkg.put_log(' entitlement_id: '||v_ents.cagr_entitlement_id);
3350: l_source_name := per_cagr_utility_pkg.get_elig_source(NULL
3351: ,v_ents.FORMULA_ID
3352: ,p_params.effective_date);
3353: per_cagr_utility_pkg.put_log(' Evaluating entitlement fast formula: '||l_source_name,1);
3354:
3355:
3356: hr_cagr_ff_pkg.cagr_entitlement_ff(p_formula_id => v_ents.FORMULA_ID
3357: ,p_effective_date => p_params.effective_date
3367: v_range_to := l_cagr_FF_record.range_to;
3368: v_write_flag := TRUE;
3369: else
3370: -- log message as the formula evaluated to null and continue with next entitlement record
3371: per_cagr_utility_pkg.put_log(' Fast Formula did not determine an eligible entitlement.',1);
3372: v_write_flag := FALSE;
3373: end if;
3374: elsif v_ents.category_name = 'PYS' then
3375: if l_cagr_FF_record.grade_spine_id is not null
3382: v_to_step_id := l_cagr_FF_record.to_step_id;
3383: v_write_flag := TRUE;
3384: else
3385: -- log message as the formula didn't evaluated to null and continue with next entitlement record
3386: per_cagr_utility_pkg.put_log(' Fast Formula did not determine an eligible entitlement.',1);
3387: end if;
3388: end if;
3389: end if;
3390: end if;
3458:
3459: if v_rule_inconclusive then
3460: -- output warning message that beneficial could not be chosen
3461: -- and write results anyway..
3462: per_cagr_utility_pkg.put_log(' ERROR: Beneficial Rule was inconclusive',1);
3463: end if;
3464: apply_chosen_result(t_results_table,t_chosen_table,p_params.commit_flag);
3465: update_result_set(t_results_table,p_params,'W');
3466: t_results_table.delete;
3466: t_results_table.delete;
3467: v_counter := 0;
3468: end if;
3469: else
3470: per_cagr_utility_pkg.put_log(' No active entitlements found for the collective agreement.',1);
3471: end if;
3472:
3473: -- add in any other retained rights for dataitems
3474: -- that are not related to the dataitems returned by the current entitlements
3480: t_chosen_table.DELETE;
3481:
3482: -- reset write header flag
3483:
3484: per_cagr_utility_pkg.put_log(' ',1);
3485: per_cagr_utility_pkg.put_log(' Completed Processing assignment',1);
3486: per_cagr_utility_pkg.put_log(' ',1);
3487:
3488: --
3481:
3482: -- reset write header flag
3483:
3484: per_cagr_utility_pkg.put_log(' ',1);
3485: per_cagr_utility_pkg.put_log(' Completed Processing assignment',1);
3486: per_cagr_utility_pkg.put_log(' ',1);
3487:
3488: --
3489: -- Commit, if required, after every assignment.
3482: -- reset write header flag
3483:
3484: per_cagr_utility_pkg.put_log(' ',1);
3485: per_cagr_utility_pkg.put_log(' Completed Processing assignment',1);
3486: per_cagr_utility_pkg.put_log(' ',1);
3487:
3488: --
3489: -- Commit, if required, after every assignment.
3490: --
3489: -- Commit, if required, after every assignment.
3490: --
3491: if p_params.commit_flag = 'Y' then
3492: commit;
3493: per_cagr_utility_pkg.put_log(' Any changes have been saved.',1);
3494: elsif p_params.commit_flag = 'N' then
3495: rollback;
3496: per_cagr_utility_pkg.put_log(' Any changes have been discarded.',1);
3497: end if;
3492: commit;
3493: per_cagr_utility_pkg.put_log(' Any changes have been saved.',1);
3494: elsif p_params.commit_flag = 'N' then
3495: rollback;
3496: per_cagr_utility_pkg.put_log(' Any changes have been discarded.',1);
3497: end if;
3498:
3499: -- write the log file for this assignment
3500: per_cagr_utility_pkg.write_log_file(p_params.cagr_request_id);
3496: per_cagr_utility_pkg.put_log(' Any changes have been discarded.',1);
3497: end if;
3498:
3499: -- write the log file for this assignment
3500: per_cagr_utility_pkg.write_log_file(p_params.cagr_request_id);
3501:
3502: Exception
3503: WHEN OTHERS THEN
3504: -- write the log file for this assignment
3501:
3502: Exception
3503: WHEN OTHERS THEN
3504: -- write the log file for this assignment
3505: per_cagr_utility_pkg.write_log_file(p_params.cagr_request_id);
3506: -- reset to the parent request id and assignment
3507: p_params.cagr_request_id := l_parent_request_id;
3508: p_params.assignment_id := NULL;
3509: rollback;
3541: close csr_BE_plan;
3542:
3543: If l_opt_id is not null then
3544: -- start benmngle
3545: per_cagr_utility_pkg.put_log('Starting benmngle');
3546: process_entitlement_lines(p_pl_id => l_pl_id -- may run for a cagr
3547: ,p_opt_id => l_opt_id -- always run for an iteme
3548: ,p_person_id => NULL -- for all people on the cagr(s)
3549: ,p_benefit_action_id => v_benefit_action_id
3555: ,p_eligibility_table => t_eligibility_table
3556: ,p_counter => v_eligibility_counter);
3557:
3558: else
3559: per_cagr_utility_pkg.put_log(' No active criteria lines found for the item on any collective agreement.',1);
3560: end if;
3561: per_cagr_utility_pkg.put_log('Processing the following collective agreements that have entitlements using the item: ',1);
3562:
3563: --
3557:
3558: else
3559: per_cagr_utility_pkg.put_log(' No active criteria lines found for the item on any collective agreement.',1);
3560: end if;
3561: per_cagr_utility_pkg.put_log('Processing the following collective agreements that have entitlements using the item: ',1);
3562:
3563: --
3564: -- load all the cagrs and their assignments ids to be processed into pl/sql table.
3565: --
3569: fetch csr_BE_assignments_to_process into t_cagr_assignments_table(v_counter);
3570: exit when csr_BE_assignments_to_process%notfound;
3571: If t_cagr_assignments_table(v_counter).collective_agreement_id <> l_last_cagr_id then
3572: l_last_cagr_id := t_cagr_assignments_table(v_counter).collective_agreement_id;
3573: per_cagr_utility_pkg.put_log(' '||t_cagr_assignments_table(v_counter).collective_agreement_id,1);
3574: End If;
3575: end loop;
3576: close csr_BE_assignments_to_process;
3577: v_counter := 0;
3576: close csr_BE_assignments_to_process;
3577: v_counter := 0;
3578:
3579: -- write the log out
3580: per_cagr_utility_pkg.write_log_file(p_params.cagr_request_id);
3581:
3582: -- could now break pl/sql table into varray subsets, ready for multiple threads
3583:
3584: -- loop through table of assignment_id for each cagr
3593: -- 4) commit AFTER each assignment
3594:
3595: p_params.assignment_id := t_cagr_assignments_table(k).assignment_id;
3596:
3597: per_cagr_utility_pkg.put_log(' ',1);
3598: per_cagr_utility_pkg.put_log(' Evaluating Assignment '|| t_cagr_assignments_table(k).assignment_id ||
3599: ' on collective agreement '|| t_cagr_assignments_table(k).collective_agreement_id ||
3600: ' during Batch Entitlement mode.',1);
3601: per_cagr_utility_pkg.put_log(' ',1);
3594:
3595: p_params.assignment_id := t_cagr_assignments_table(k).assignment_id;
3596:
3597: per_cagr_utility_pkg.put_log(' ',1);
3598: per_cagr_utility_pkg.put_log(' Evaluating Assignment '|| t_cagr_assignments_table(k).assignment_id ||
3599: ' on collective agreement '|| t_cagr_assignments_table(k).collective_agreement_id ||
3600: ' during Batch Entitlement mode.',1);
3601: per_cagr_utility_pkg.put_log(' ',1);
3602:
3597: per_cagr_utility_pkg.put_log(' ',1);
3598: per_cagr_utility_pkg.put_log(' Evaluating Assignment '|| t_cagr_assignments_table(k).assignment_id ||
3599: ' on collective agreement '|| t_cagr_assignments_table(k).collective_agreement_id ||
3600: ' during Batch Entitlement mode.',1);
3601: per_cagr_utility_pkg.put_log(' ',1);
3602:
3603:
3604: -- for each asg on the cagr on the effective_date:
3605:
3622:
3623:
3624: -- determine whether current record is entitlement item (so run ff) or entitlement line
3625: if v_ents.formula_criteria = 'C' then -- line item record
3626: per_cagr_utility_pkg.put_log(' Processing entitlement: '||v_ents.cagr_entitlement_id||' '||v_ents.item_name
3627: ||', entitlement line: '||v_ents.cagr_entitlement_line_id);
3628:
3629: if v_ents.OIPL_ID = 0 and v_ents.eligy_prfl_id = 0 then
3630: -- write the record as this is default elig line
3656: v_write_flag := TRUE;
3657: end if;
3658: else
3659: -- log error that there are no BEN eligibility result records returned by benmngle
3660: per_cagr_utility_pkg.put_log(' ERROR: No eligibility results were generated for the assignment',1);
3661: end if;
3662: end if;
3663:
3664: if v_ents.category_name = 'PYS' and v_write_flag = TRUE then
3665: -- check the asg grade matches the grade_spine grade, as well as elig profile
3666: -- being satisfied, in order to be eligible for this PYS criteria.
3667: if nvl(v_ents.grade_id,-2) <> nvl(get_PYS_grade_id (v_ents.grade_spine_id
3668: ,p_params.effective_date),-1) then
3669: per_cagr_utility_pkg.put_log(' Criteria line is ineligible as the assignment is not on the grade spine. ',1);
3670: v_write_flag := FALSE;
3671: end if;
3672: end if;
3673:
3672: end if;
3673:
3674: elsif v_ents.formula_criteria = 'F' then -- item record
3675: if v_ents.FORMULA_ID is not null then
3676: per_cagr_utility_pkg.put_log(' Processing entitlement: '||v_ents.cagr_entitlement_id||' '
3677: ||v_ents.item_name||', calling ff');
3678:
3679: hr_cagr_ff_pkg.cagr_entitlement_ff(p_formula_id => v_ents.FORMULA_ID
3680: ,p_effective_date => p_params.effective_date
3690: v_range_to := l_cagr_FF_record.range_to;
3691: v_write_flag := TRUE;
3692: else
3693: -- log message as the formula evaluated to null and continue with next entitlement record
3694: per_cagr_utility_pkg.put_log(' Fast Formula did not determine an eligible entitlement.',1);
3695: v_write_flag := FALSE;
3696: end if;
3697: elsif v_ents.category_name = 'PYS' then
3698: if l_cagr_FF_record.grade_spine_id is not null
3705: v_to_step_id := l_cagr_FF_record.to_step_id;
3706: v_write_flag := TRUE;
3707: else
3708: -- log message as the formula didn't evaluated to null and continue with next entitlement record
3709: per_cagr_utility_pkg.put_log(' Fast Formula did not determine an eligible entitlement.',1);
3710: end if;
3711: end if;
3712: end if;
3713: end if;
3799: p_SE_rec.ERROR := NULL; -- do not return this error
3800: end if;
3801: EXCEPTION
3802: WHEN RESOURCE_BUSY THEN
3803: per_cagr_utility_pkg.put_log(' WARNING: unable to obtain exclusive lock on result for assignment:'
3804: ||p_params.assignment_id);
3805: per_cagr_utility_pkg.put_log(' Cache was not updated with results, continuing...');
3806: END;
3807: end if;
3801: EXCEPTION
3802: WHEN RESOURCE_BUSY THEN
3803: per_cagr_utility_pkg.put_log(' WARNING: unable to obtain exclusive lock on result for assignment:'
3804: ||p_params.assignment_id);
3805: per_cagr_utility_pkg.put_log(' Cache was not updated with results, continuing...');
3806: END;
3807: end if;
3808: end if;
3809: end if;
3815:
3816:
3817: -- reset write header flag
3818:
3819: per_cagr_utility_pkg.put_log(' ',1);
3820: per_cagr_utility_pkg.put_log(' Completed Processing assignment',1);
3821: per_cagr_utility_pkg.put_log(' ',1);
3822:
3823: --
3816:
3817: -- reset write header flag
3818:
3819: per_cagr_utility_pkg.put_log(' ',1);
3820: per_cagr_utility_pkg.put_log(' Completed Processing assignment',1);
3821: per_cagr_utility_pkg.put_log(' ',1);
3822:
3823: --
3824: -- Commit, if required, after every assignment.
3817: -- reset write header flag
3818:
3819: per_cagr_utility_pkg.put_log(' ',1);
3820: per_cagr_utility_pkg.put_log(' Completed Processing assignment',1);
3821: per_cagr_utility_pkg.put_log(' ',1);
3822:
3823: --
3824: -- Commit, if required, after every assignment.
3825: --
3824: -- Commit, if required, after every assignment.
3825: --
3826: if p_params.commit_flag = 'Y' then
3827: commit;
3828: per_cagr_utility_pkg.put_log(' Any changes have been saved.',1);
3829: elsif p_params.commit_flag = 'N' then
3830: rollback;
3831: per_cagr_utility_pkg.put_log(' Any changes have been discarded.',1);
3832: end if;
3827: commit;
3828: per_cagr_utility_pkg.put_log(' Any changes have been saved.',1);
3829: elsif p_params.commit_flag = 'N' then
3830: rollback;
3831: per_cagr_utility_pkg.put_log(' Any changes have been discarded.',1);
3832: end if;
3833:
3834: per_cagr_utility_pkg.write_log_file(p_params.cagr_request_id);
3835:
3830: rollback;
3831: per_cagr_utility_pkg.put_log(' Any changes have been discarded.',1);
3832: end if;
3833:
3834: per_cagr_utility_pkg.write_log_file(p_params.cagr_request_id);
3835:
3836: EXCEPTION
3837: WHEN OTHERS THEN
3838: -- write the log file for this assignment
3835:
3836: EXCEPTION
3837: WHEN OTHERS THEN
3838: -- write the log file for this assignment
3839: per_cagr_utility_pkg.write_log_file(p_params.cagr_request_id);
3840: -- reset to the parent request id and assignment
3841: p_params.assignment_id := NULL;
3842: rollback;
3843: raise;
3851: else -- Other modes....
3852: null;
3853: end if;
3854:
3855: per_cagr_utility_pkg.put_log(' ',1);
3856: per_cagr_utility_pkg.put_log('Completed Evaluation Process ('||fnd_date.date_to_canonical(sysdate)||')',1);
3857: per_cagr_utility_pkg.put_log(' ',1);
3858: per_cagr_utility_pkg.write_log_file(p_params.cagr_request_id);
3859: hr_utility.set_location('Leaving:'||l_proc, 100);
3852: null;
3853: end if;
3854:
3855: per_cagr_utility_pkg.put_log(' ',1);
3856: per_cagr_utility_pkg.put_log('Completed Evaluation Process ('||fnd_date.date_to_canonical(sysdate)||')',1);
3857: per_cagr_utility_pkg.put_log(' ',1);
3858: per_cagr_utility_pkg.write_log_file(p_params.cagr_request_id);
3859: hr_utility.set_location('Leaving:'||l_proc, 100);
3860:
3853: end if;
3854:
3855: per_cagr_utility_pkg.put_log(' ',1);
3856: per_cagr_utility_pkg.put_log('Completed Evaluation Process ('||fnd_date.date_to_canonical(sysdate)||')',1);
3857: per_cagr_utility_pkg.put_log(' ',1);
3858: per_cagr_utility_pkg.write_log_file(p_params.cagr_request_id);
3859: hr_utility.set_location('Leaving:'||l_proc, 100);
3860:
3861:
3854:
3855: per_cagr_utility_pkg.put_log(' ',1);
3856: per_cagr_utility_pkg.put_log('Completed Evaluation Process ('||fnd_date.date_to_canonical(sysdate)||')',1);
3857: per_cagr_utility_pkg.put_log(' ',1);
3858: per_cagr_utility_pkg.write_log_file(p_params.cagr_request_id);
3859: hr_utility.set_location('Leaving:'||l_proc, 100);
3860:
3861:
3862: EXCEPTION
3860:
3861:
3862: EXCEPTION
3863: WHEN OTHERS THEN
3864: per_cagr_utility_pkg.write_log_file(p_params.cagr_request_id);
3865: raise;
3866:
3867: END evaluation_process;
3868:
3895:
3896: --
3897: -- store params and cagr return cagr_request_id for use in this run
3898: --
3899: per_cagr_utility_pkg.create_cagr_request(p_process_date => p_process_date
3900: ,p_operation_mode => p_operation_mode
3901: ,p_business_group_id => p_business_group_id
3902: ,p_assignment_id => p_assignment_id
3903: ,p_assignment_set_id => p_assignment_set_id
3913:
3914: --
3915: -- Output log header
3916: --
3917: per_cagr_utility_pkg.put_log(g_head_separator,1);
3918: per_cagr_utility_pkg.put_log('----------- Collective Agreement Process Log ('||fnd_date.date_to_canonical(sysdate)||') -----------',1);
3919: --
3920: -- Ensure that all the mandatory arguments are not null
3921: --
3914: --
3915: -- Output log header
3916: --
3917: per_cagr_utility_pkg.put_log(g_head_separator,1);
3918: per_cagr_utility_pkg.put_log('----------- Collective Agreement Process Log ('||fnd_date.date_to_canonical(sysdate)||') -----------',1);
3919: --
3920: -- Ensure that all the mandatory arguments are not null
3921: --
3922: hr_api.mandatory_arg_error(p_api_name => l_proc
3932:
3933: -- test for required params for modes
3934:
3935: if not(p_operation_mode in ('SA','SE','SC','BE')) then
3936: per_cagr_utility_pkg.log_and_raise_error('HR_289420_CAGR_INV_MODE'
3937: ,p_cagr_request_id);
3938: end if;
3939: if (p_operation_mode = 'SA' and p_assignment_id is null) then
3940: per_cagr_utility_pkg.log_and_raise_error('HR_289421_CAGR_INV_SA_PARAM'
3936: per_cagr_utility_pkg.log_and_raise_error('HR_289420_CAGR_INV_MODE'
3937: ,p_cagr_request_id);
3938: end if;
3939: if (p_operation_mode = 'SA' and p_assignment_id is null) then
3940: per_cagr_utility_pkg.log_and_raise_error('HR_289421_CAGR_INV_SA_PARAM'
3941: ,p_cagr_request_id);
3942: elsif (p_operation_mode = 'SE') and
3943: (p_entitlement_item_id is null or p_assignment_id is null) then
3944: per_cagr_utility_pkg.log_and_raise_error('HR_289422_CAGR_INV_SE_PARAM'
3940: per_cagr_utility_pkg.log_and_raise_error('HR_289421_CAGR_INV_SA_PARAM'
3941: ,p_cagr_request_id);
3942: elsif (p_operation_mode = 'SE') and
3943: (p_entitlement_item_id is null or p_assignment_id is null) then
3944: per_cagr_utility_pkg.log_and_raise_error('HR_289422_CAGR_INV_SE_PARAM'
3945: ,p_cagr_request_id);
3946: elsif (p_operation_mode = 'BE' and (p_entitlement_item_id is null or p_apply_results_flag <> 'N')) then
3947: per_cagr_utility_pkg.log_and_raise_error('HR_289709_CAGR_INV_BE_PARAM'
3948: ,p_cagr_request_id);
3943: (p_entitlement_item_id is null or p_assignment_id is null) then
3944: per_cagr_utility_pkg.log_and_raise_error('HR_289422_CAGR_INV_SE_PARAM'
3945: ,p_cagr_request_id);
3946: elsif (p_operation_mode = 'BE' and (p_entitlement_item_id is null or p_apply_results_flag <> 'N')) then
3947: per_cagr_utility_pkg.log_and_raise_error('HR_289709_CAGR_INV_BE_PARAM'
3948: ,p_cagr_request_id);
3949: elsif (p_operation_mode = 'SC' and p_collective_agreement_id is null) then
3950: per_cagr_utility_pkg.log_and_raise_error('HR_289597_INV_SC_PARAM'
3951: ,p_cagr_request_id);
3946: elsif (p_operation_mode = 'BE' and (p_entitlement_item_id is null or p_apply_results_flag <> 'N')) then
3947: per_cagr_utility_pkg.log_and_raise_error('HR_289709_CAGR_INV_BE_PARAM'
3948: ,p_cagr_request_id);
3949: elsif (p_operation_mode = 'SC' and p_collective_agreement_id is null) then
3950: per_cagr_utility_pkg.log_and_raise_error('HR_289597_INV_SC_PARAM'
3951: ,p_cagr_request_id);
3952: end if;
3953:
3954: --
3954: --
3955: -- test for invalid params and values for modes
3956: --
3957: if not (p_apply_results_flag in('N','Y')) then
3958: per_cagr_utility_pkg.log_and_raise_error('HR_289418_CAGR_INV_DFLAG'
3959: ,p_cagr_request_id);
3960: elsif not (p_commit_flag in ('N','Y')) then
3961: per_cagr_utility_pkg.log_and_raise_error('HR_289419_CAGR_INV_CFLAG'
3962: ,p_cagr_request_id);
3957: if not (p_apply_results_flag in('N','Y')) then
3958: per_cagr_utility_pkg.log_and_raise_error('HR_289418_CAGR_INV_DFLAG'
3959: ,p_cagr_request_id);
3960: elsif not (p_commit_flag in ('N','Y')) then
3961: per_cagr_utility_pkg.log_and_raise_error('HR_289419_CAGR_INV_CFLAG'
3962: ,p_cagr_request_id);
3963: end if;
3964:
3965: if (p_assignment_id is not null and not(p_operation_mode in ('SE','SA'))) or
3970: (p_collective_agreement_id is not null and not(p_operation_mode in ('SC','BE'))) or
3971: (p_apply_results_flag <> 'N' and p_operation_mode in ('SE','BE')) or
3972: (p_collective_agreement_set_id is not null)
3973: then
3974: per_cagr_utility_pkg.log_and_raise_error('HR_289708_UNEXPECTED_PARAM',p_cagr_request_id);
3975: end if;
3976:
3977:
3978: --
3996:
3997: --
3998: -- Output parameter values to log
3999: --
4000: per_cagr_utility_pkg.put_log(g_head_separator,1);
4001: per_cagr_utility_pkg.put_log(' ',1);
4002: per_cagr_utility_pkg.put_log(' * Execution Parameter List * ',1);
4003: per_cagr_utility_pkg.put_log(' ',1);
4004: if p_params.operation_mode = 'SA' then
3997: --
3998: -- Output parameter values to log
3999: --
4000: per_cagr_utility_pkg.put_log(g_head_separator,1);
4001: per_cagr_utility_pkg.put_log(' ',1);
4002: per_cagr_utility_pkg.put_log(' * Execution Parameter List * ',1);
4003: per_cagr_utility_pkg.put_log(' ',1);
4004: if p_params.operation_mode = 'SA' then
4005: per_cagr_utility_pkg.put_log(' Mode: Single Assignment',1);
3998: -- Output parameter values to log
3999: --
4000: per_cagr_utility_pkg.put_log(g_head_separator,1);
4001: per_cagr_utility_pkg.put_log(' ',1);
4002: per_cagr_utility_pkg.put_log(' * Execution Parameter List * ',1);
4003: per_cagr_utility_pkg.put_log(' ',1);
4004: if p_params.operation_mode = 'SA' then
4005: per_cagr_utility_pkg.put_log(' Mode: Single Assignment',1);
4006: elsif p_params.operation_mode = 'SE' then
3999: --
4000: per_cagr_utility_pkg.put_log(g_head_separator,1);
4001: per_cagr_utility_pkg.put_log(' ',1);
4002: per_cagr_utility_pkg.put_log(' * Execution Parameter List * ',1);
4003: per_cagr_utility_pkg.put_log(' ',1);
4004: if p_params.operation_mode = 'SA' then
4005: per_cagr_utility_pkg.put_log(' Mode: Single Assignment',1);
4006: elsif p_params.operation_mode = 'SE' then
4007: per_cagr_utility_pkg.put_log(' Mode: Single Entitlement Item',1);
4001: per_cagr_utility_pkg.put_log(' ',1);
4002: per_cagr_utility_pkg.put_log(' * Execution Parameter List * ',1);
4003: per_cagr_utility_pkg.put_log(' ',1);
4004: if p_params.operation_mode = 'SA' then
4005: per_cagr_utility_pkg.put_log(' Mode: Single Assignment',1);
4006: elsif p_params.operation_mode = 'SE' then
4007: per_cagr_utility_pkg.put_log(' Mode: Single Entitlement Item',1);
4008: elsif p_params.operation_mode = 'BE' then
4009: per_cagr_utility_pkg.put_log(' Mode: Batch Entitlement Item',1);
4003: per_cagr_utility_pkg.put_log(' ',1);
4004: if p_params.operation_mode = 'SA' then
4005: per_cagr_utility_pkg.put_log(' Mode: Single Assignment',1);
4006: elsif p_params.operation_mode = 'SE' then
4007: per_cagr_utility_pkg.put_log(' Mode: Single Entitlement Item',1);
4008: elsif p_params.operation_mode = 'BE' then
4009: per_cagr_utility_pkg.put_log(' Mode: Batch Entitlement Item',1);
4010: elsif p_params.operation_mode = 'SC' then
4011: per_cagr_utility_pkg.put_log(' Mode: Single Collective Agreement',1);
4005: per_cagr_utility_pkg.put_log(' Mode: Single Assignment',1);
4006: elsif p_params.operation_mode = 'SE' then
4007: per_cagr_utility_pkg.put_log(' Mode: Single Entitlement Item',1);
4008: elsif p_params.operation_mode = 'BE' then
4009: per_cagr_utility_pkg.put_log(' Mode: Batch Entitlement Item',1);
4010: elsif p_params.operation_mode = 'SC' then
4011: per_cagr_utility_pkg.put_log(' Mode: Single Collective Agreement',1);
4012: end if;
4013: per_cagr_utility_pkg.put_log(' Business Group ID: '||p_params.business_group_id,1);
4007: per_cagr_utility_pkg.put_log(' Mode: Single Entitlement Item',1);
4008: elsif p_params.operation_mode = 'BE' then
4009: per_cagr_utility_pkg.put_log(' Mode: Batch Entitlement Item',1);
4010: elsif p_params.operation_mode = 'SC' then
4011: per_cagr_utility_pkg.put_log(' Mode: Single Collective Agreement',1);
4012: end if;
4013: per_cagr_utility_pkg.put_log(' Business Group ID: '||p_params.business_group_id,1);
4014: per_cagr_utility_pkg.put_log(' CAGR Request ID: '||p_params.cagr_request_id,1);
4015: per_cagr_utility_pkg.put_log(' Effective Date: '||p_params.effective_date,1);
4009: per_cagr_utility_pkg.put_log(' Mode: Batch Entitlement Item',1);
4010: elsif p_params.operation_mode = 'SC' then
4011: per_cagr_utility_pkg.put_log(' Mode: Single Collective Agreement',1);
4012: end if;
4013: per_cagr_utility_pkg.put_log(' Business Group ID: '||p_params.business_group_id,1);
4014: per_cagr_utility_pkg.put_log(' CAGR Request ID: '||p_params.cagr_request_id,1);
4015: per_cagr_utility_pkg.put_log(' Effective Date: '||p_params.effective_date,1);
4016: per_cagr_utility_pkg.put_log(' Assignment ID: '||p_params.assignment_id,1);
4017: per_cagr_utility_pkg.put_log(' Assignment Set ID: '||p_params.assignment_set_id);
4010: elsif p_params.operation_mode = 'SC' then
4011: per_cagr_utility_pkg.put_log(' Mode: Single Collective Agreement',1);
4012: end if;
4013: per_cagr_utility_pkg.put_log(' Business Group ID: '||p_params.business_group_id,1);
4014: per_cagr_utility_pkg.put_log(' CAGR Request ID: '||p_params.cagr_request_id,1);
4015: per_cagr_utility_pkg.put_log(' Effective Date: '||p_params.effective_date,1);
4016: per_cagr_utility_pkg.put_log(' Assignment ID: '||p_params.assignment_id,1);
4017: per_cagr_utility_pkg.put_log(' Assignment Set ID: '||p_params.assignment_set_id);
4018: per_cagr_utility_pkg.put_log(' Collective Agreement ID: '||p_params.collective_agreement_id,1);
4011: per_cagr_utility_pkg.put_log(' Mode: Single Collective Agreement',1);
4012: end if;
4013: per_cagr_utility_pkg.put_log(' Business Group ID: '||p_params.business_group_id,1);
4014: per_cagr_utility_pkg.put_log(' CAGR Request ID: '||p_params.cagr_request_id,1);
4015: per_cagr_utility_pkg.put_log(' Effective Date: '||p_params.effective_date,1);
4016: per_cagr_utility_pkg.put_log(' Assignment ID: '||p_params.assignment_id,1);
4017: per_cagr_utility_pkg.put_log(' Assignment Set ID: '||p_params.assignment_set_id);
4018: per_cagr_utility_pkg.put_log(' Collective Agreement ID: '||p_params.collective_agreement_id,1);
4019: per_cagr_utility_pkg.put_log(' Collective Agreement Set ID: '||p_params.cagr_set_id);
4012: end if;
4013: per_cagr_utility_pkg.put_log(' Business Group ID: '||p_params.business_group_id,1);
4014: per_cagr_utility_pkg.put_log(' CAGR Request ID: '||p_params.cagr_request_id,1);
4015: per_cagr_utility_pkg.put_log(' Effective Date: '||p_params.effective_date,1);
4016: per_cagr_utility_pkg.put_log(' Assignment ID: '||p_params.assignment_id,1);
4017: per_cagr_utility_pkg.put_log(' Assignment Set ID: '||p_params.assignment_set_id);
4018: per_cagr_utility_pkg.put_log(' Collective Agreement ID: '||p_params.collective_agreement_id,1);
4019: per_cagr_utility_pkg.put_log(' Collective Agreement Set ID: '||p_params.cagr_set_id);
4020: per_cagr_utility_pkg.put_log(' Payroll ID: '||p_params.payroll_id);
4013: per_cagr_utility_pkg.put_log(' Business Group ID: '||p_params.business_group_id,1);
4014: per_cagr_utility_pkg.put_log(' CAGR Request ID: '||p_params.cagr_request_id,1);
4015: per_cagr_utility_pkg.put_log(' Effective Date: '||p_params.effective_date,1);
4016: per_cagr_utility_pkg.put_log(' Assignment ID: '||p_params.assignment_id,1);
4017: per_cagr_utility_pkg.put_log(' Assignment Set ID: '||p_params.assignment_set_id);
4018: per_cagr_utility_pkg.put_log(' Collective Agreement ID: '||p_params.collective_agreement_id,1);
4019: per_cagr_utility_pkg.put_log(' Collective Agreement Set ID: '||p_params.cagr_set_id);
4020: per_cagr_utility_pkg.put_log(' Payroll ID: '||p_params.payroll_id);
4021: per_cagr_utility_pkg.put_log(' Person ID: '||p_params.person_id);
4014: per_cagr_utility_pkg.put_log(' CAGR Request ID: '||p_params.cagr_request_id,1);
4015: per_cagr_utility_pkg.put_log(' Effective Date: '||p_params.effective_date,1);
4016: per_cagr_utility_pkg.put_log(' Assignment ID: '||p_params.assignment_id,1);
4017: per_cagr_utility_pkg.put_log(' Assignment Set ID: '||p_params.assignment_set_id);
4018: per_cagr_utility_pkg.put_log(' Collective Agreement ID: '||p_params.collective_agreement_id,1);
4019: per_cagr_utility_pkg.put_log(' Collective Agreement Set ID: '||p_params.cagr_set_id);
4020: per_cagr_utility_pkg.put_log(' Payroll ID: '||p_params.payroll_id);
4021: per_cagr_utility_pkg.put_log(' Person ID: '||p_params.person_id);
4022: per_cagr_utility_pkg.put_log(' Entitlement Item ID: '||p_params.entitlement_item_id);
4015: per_cagr_utility_pkg.put_log(' Effective Date: '||p_params.effective_date,1);
4016: per_cagr_utility_pkg.put_log(' Assignment ID: '||p_params.assignment_id,1);
4017: per_cagr_utility_pkg.put_log(' Assignment Set ID: '||p_params.assignment_set_id);
4018: per_cagr_utility_pkg.put_log(' Collective Agreement ID: '||p_params.collective_agreement_id,1);
4019: per_cagr_utility_pkg.put_log(' Collective Agreement Set ID: '||p_params.cagr_set_id);
4020: per_cagr_utility_pkg.put_log(' Payroll ID: '||p_params.payroll_id);
4021: per_cagr_utility_pkg.put_log(' Person ID: '||p_params.person_id);
4022: per_cagr_utility_pkg.put_log(' Entitlement Item ID: '||p_params.entitlement_item_id);
4023: if p_params.denormalise_flag = 'Y' then
4016: per_cagr_utility_pkg.put_log(' Assignment ID: '||p_params.assignment_id,1);
4017: per_cagr_utility_pkg.put_log(' Assignment Set ID: '||p_params.assignment_set_id);
4018: per_cagr_utility_pkg.put_log(' Collective Agreement ID: '||p_params.collective_agreement_id,1);
4019: per_cagr_utility_pkg.put_log(' Collective Agreement Set ID: '||p_params.cagr_set_id);
4020: per_cagr_utility_pkg.put_log(' Payroll ID: '||p_params.payroll_id);
4021: per_cagr_utility_pkg.put_log(' Person ID: '||p_params.person_id);
4022: per_cagr_utility_pkg.put_log(' Entitlement Item ID: '||p_params.entitlement_item_id);
4023: if p_params.denormalise_flag = 'Y' then
4024: per_cagr_utility_pkg.put_log(' Apply entitlements to HRMS flag: Yes',1);
4017: per_cagr_utility_pkg.put_log(' Assignment Set ID: '||p_params.assignment_set_id);
4018: per_cagr_utility_pkg.put_log(' Collective Agreement ID: '||p_params.collective_agreement_id,1);
4019: per_cagr_utility_pkg.put_log(' Collective Agreement Set ID: '||p_params.cagr_set_id);
4020: per_cagr_utility_pkg.put_log(' Payroll ID: '||p_params.payroll_id);
4021: per_cagr_utility_pkg.put_log(' Person ID: '||p_params.person_id);
4022: per_cagr_utility_pkg.put_log(' Entitlement Item ID: '||p_params.entitlement_item_id);
4023: if p_params.denormalise_flag = 'Y' then
4024: per_cagr_utility_pkg.put_log(' Apply entitlements to HRMS flag: Yes',1);
4025: elsif p_params.denormalise_flag = 'N' then
4018: per_cagr_utility_pkg.put_log(' Collective Agreement ID: '||p_params.collective_agreement_id,1);
4019: per_cagr_utility_pkg.put_log(' Collective Agreement Set ID: '||p_params.cagr_set_id);
4020: per_cagr_utility_pkg.put_log(' Payroll ID: '||p_params.payroll_id);
4021: per_cagr_utility_pkg.put_log(' Person ID: '||p_params.person_id);
4022: per_cagr_utility_pkg.put_log(' Entitlement Item ID: '||p_params.entitlement_item_id);
4023: if p_params.denormalise_flag = 'Y' then
4024: per_cagr_utility_pkg.put_log(' Apply entitlements to HRMS flag: Yes',1);
4025: elsif p_params.denormalise_flag = 'N' then
4026: per_cagr_utility_pkg.put_log(' Apply entitlements to HRMS flag: No',1);
4020: per_cagr_utility_pkg.put_log(' Payroll ID: '||p_params.payroll_id);
4021: per_cagr_utility_pkg.put_log(' Person ID: '||p_params.person_id);
4022: per_cagr_utility_pkg.put_log(' Entitlement Item ID: '||p_params.entitlement_item_id);
4023: if p_params.denormalise_flag = 'Y' then
4024: per_cagr_utility_pkg.put_log(' Apply entitlements to HRMS flag: Yes',1);
4025: elsif p_params.denormalise_flag = 'N' then
4026: per_cagr_utility_pkg.put_log(' Apply entitlements to HRMS flag: No',1);
4027: end if;
4028: if p_params.commit_flag = 'Y' then
4022: per_cagr_utility_pkg.put_log(' Entitlement Item ID: '||p_params.entitlement_item_id);
4023: if p_params.denormalise_flag = 'Y' then
4024: per_cagr_utility_pkg.put_log(' Apply entitlements to HRMS flag: Yes',1);
4025: elsif p_params.denormalise_flag = 'N' then
4026: per_cagr_utility_pkg.put_log(' Apply entitlements to HRMS flag: No',1);
4027: end if;
4028: if p_params.commit_flag = 'Y' then
4029: per_cagr_utility_pkg.put_log(' Commit entitlements flag: Yes',1);
4030: elsif p_params.commit_flag = 'N' then
4025: elsif p_params.denormalise_flag = 'N' then
4026: per_cagr_utility_pkg.put_log(' Apply entitlements to HRMS flag: No',1);
4027: end if;
4028: if p_params.commit_flag = 'Y' then
4029: per_cagr_utility_pkg.put_log(' Commit entitlements flag: Yes',1);
4030: elsif p_params.commit_flag = 'N' then
4031: per_cagr_utility_pkg.put_log(' Commit entitlements flag: No',1);
4032: end if;
4033: per_cagr_utility_pkg.put_log(' ',1);
4027: end if;
4028: if p_params.commit_flag = 'Y' then
4029: per_cagr_utility_pkg.put_log(' Commit entitlements flag: Yes',1);
4030: elsif p_params.commit_flag = 'N' then
4031: per_cagr_utility_pkg.put_log(' Commit entitlements flag: No',1);
4032: end if;
4033: per_cagr_utility_pkg.put_log(' ',1);
4034:
4035: -- ****** This needs to be converted to a parameter passed to create_request,
4029: per_cagr_utility_pkg.put_log(' Commit entitlements flag: Yes',1);
4030: elsif p_params.commit_flag = 'N' then
4031: per_cagr_utility_pkg.put_log(' Commit entitlements flag: No',1);
4032: end if;
4033: per_cagr_utility_pkg.put_log(' ',1);
4034:
4035: -- ****** This needs to be converted to a parameter passed to create_request,
4036: -- rather than relying on a public package variable *******
4037:
4035: -- ****** This needs to be converted to a parameter passed to create_request,
4036: -- rather than relying on a public package variable *******
4037:
4038: if fnd_global.conc_request_id <> -1 then
4039: per_cagr_utility_pkg.put_log(' Executed from concurrent manager');
4040: else
4041: per_cagr_utility_pkg.put_log(' Executed from SQLPLUS session');
4042: end if;
4043: per_cagr_utility_pkg.put_log(' ');
4037:
4038: if fnd_global.conc_request_id <> -1 then
4039: per_cagr_utility_pkg.put_log(' Executed from concurrent manager');
4040: else
4041: per_cagr_utility_pkg.put_log(' Executed from SQLPLUS session');
4042: end if;
4043: per_cagr_utility_pkg.put_log(' ');
4044: per_cagr_utility_pkg.write_log_file(p_params.cagr_request_id);
4045:
4039: per_cagr_utility_pkg.put_log(' Executed from concurrent manager');
4040: else
4041: per_cagr_utility_pkg.put_log(' Executed from SQLPLUS session');
4042: end if;
4043: per_cagr_utility_pkg.put_log(' ');
4044: per_cagr_utility_pkg.write_log_file(p_params.cagr_request_id);
4045:
4046: --
4047: -- invoke evaluation processing;
4040: else
4041: per_cagr_utility_pkg.put_log(' Executed from SQLPLUS session');
4042: end if;
4043: per_cagr_utility_pkg.put_log(' ');
4044: per_cagr_utility_pkg.write_log_file(p_params.cagr_request_id);
4045:
4046: --
4047: -- invoke evaluation processing;
4048: --
4059: --
4060: -- Commit, if required.
4061: --
4062: if p_params.commit_flag = 'Y' then
4063: per_cagr_utility_pkg.put_log(' Any changes have been saved.',1);
4064: commit;
4065: elsif p_params.commit_flag = 'N' then
4066: per_cagr_utility_pkg.put_log(' Any changes have been discarded.',1);
4067: rollback;
4062: if p_params.commit_flag = 'Y' then
4063: per_cagr_utility_pkg.put_log(' Any changes have been saved.',1);
4064: commit;
4065: elsif p_params.commit_flag = 'N' then
4066: per_cagr_utility_pkg.put_log(' Any changes have been discarded.',1);
4067: rollback;
4068: end if;
4069:
4070: -- complete logging
4067: rollback;
4068: end if;
4069:
4070: -- complete logging
4071: per_cagr_utility_pkg.put_log(g_separator,1);
4072: per_cagr_utility_pkg.write_log_file(p_params.cagr_request_id);
4073:
4074: hr_utility.set_location('Leaving:'||l_proc, 50);
4075:
4068: end if;
4069:
4070: -- complete logging
4071: per_cagr_utility_pkg.put_log(g_separator,1);
4072: per_cagr_utility_pkg.write_log_file(p_params.cagr_request_id);
4073:
4074: hr_utility.set_location('Leaving:'||l_proc, 50);
4075:
4076: END initialise;