[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
,p_select_flag IN varchar2 default 'B') IS
-- (record interface)
-- Coordinates denormalisation of per_cagr_entitlement_result records to HRMS tables.
-- Called by: PER_CAGR_APPLY_RESULTS_PKG.initialise (individual parameter interface) and
-- optionally by PER_CAGR_EVALUATION_PKG.initialise (dependent upon mode).
--
-- P_SELECT_FLAG now redundant - apply chosen, or beneficial if no chosen.
-- Defaults to processing all categories of result records existing in cache
-- for asg - cagr - effective_date params supplied, unless a specific category is
-- supplied to restrict processing.
--
-- P_SELECT_FLAG NOW REDUNDANT - apply chosen, or beneficial if no chosen.
-- (old processing: Selects the results according to select_flag
-- param, either 'B' (benefical_flag = Y) or 'C' (chosen_flag = 'Y').
--
TYPE assignment_rec IS RECORD (assignment_id number(15));
SELECT assignment_id
FROM per_all_assignments_f asg
WHERE asg.collective_agreement_id = p_params.collective_agreement_id
AND p_params.effective_date BETWEEN asg.effective_start_date
AND asg.effective_end_date
AND asg.PRIMARY_FLAG = 'Y';
SELECT cagr_request_id
FROM per_cagr_entitlement_results res
WHERE res.assignment_id = p_assignment_id
AND p_effective_date BETWEEN res.start_date and nvl(res.end_date,hr_general.end_of_time)
AND exists (select 'x'
from per_cagr_requests req
where req.cagr_request_id = cagr_request_id
and req.OPERATION_MODE = 'SA')
and rownum = 1;
l_sql := 'SELECT object_version_number, effective_start_date FROM '||p_table_name||' WHERE '||p_pk||' = :1 ';
p_mode := 'UPDATE'; -- default to this mode
l_sql := 'SELECT null FROM '||p_table_name||' WHERE '||p_pk||' = :1 and :2 < effective_start_date';
p_mode := 'UPDATE_CHANGE_INSERT';
l_sql := 'SELECT object_version_number FROM '||p_table_name||' WHERE '||p_pk||' = :1 ';
SELECT apip.parameter_name
,apip.cagr_api_param_id
,apip.column_type
,apip.uom_parameter
,api.api_name
FROM per_cagr_api_parameters apip, per_cagr_apis api
WHERE api.cagr_api_id = v_cagr_api_id
AND api.cagr_api_id = apip.cagr_api_id;
l_dt_mode VARCHAR2(30) := 'UPDATE';
l_spp_delete_warning BOOLEAN;
if l_api_details(1).api_name = 'HR_ASSIGNMENT_API.UPDATE_EMP_ASG' then
get_ovn_and_mode (p_table_name => 'PER_ALL_ASSIGNMENTS_F'
,p_dt_flag => 'Y'
,p_pk => 'assignment_id'
,p_pk_id => p_params.assignment_id
,p_effective_date => p_params.effective_date
,p_mode => l_dt_mode
,p_ovn => l_ovn);
per_cagr_utility_pkg.put_log(' calling HR_ASSIGNMENT_API.UPDATE_EMP_ASG in mode: '||l_dt_mode||' , OVN: '||l_ovn,1);
hr_assignment_api.update_emp_asg
(p_effective_date => p_params.effective_date
,p_datetrack_update_mode => l_dt_mode
,p_assignment_id => p_params.assignment_id
,p_object_version_number => l_ovn
,p_normal_hours => get_num_val('NORMAL_HOURS',l_mapping_table)
,p_frequency => get_char_val('FREQUENCY',l_mapping_table)
,p_date_probation_end => null /*Bug 5125705: End date does not get updated, if not passed */
,p_probation_period => get_num_val('PROBATION_PERIOD',l_mapping_table)
,p_probation_unit => get_char_val('PROBATION_UNIT',l_mapping_table)
,p_time_normal_start => get_char_val('TIME_NORMAL_START',l_mapping_table)
,p_time_normal_finish => get_char_val('TIME_NORMAL_FINISH',l_mapping_table)
,p_notice_period => get_num_val('NOTICE_PERIOD',l_mapping_table)
,p_notice_period_uom => get_char_val('NOTICE_PERIOD_UOM',l_mapping_table)
,p_bargaining_unit_code => get_char_val('BARGAINING_UNIT_CODE',l_mapping_table)
,p_labour_union_member_flag => get_char_val('LABOUR_UNION_MEMBER_FLAG',l_mapping_table)
,p_employee_category => get_char_val('EMPLOYEE_CATEGORY',l_mapping_table)
,p_ASS_ATTRIBUTE_CATEGORY => get_char_val('ASS_ATTRIBUTE_CATEGORY',l_mapping_table)
,p_ASS_ATTRIBUTE1 => get_char_val('ASS_ATTRIBUTE1',l_mapping_table)
,p_ASS_ATTRIBUTE2 => get_char_val('ASS_ATTRIBUTE2',l_mapping_table)
,p_ASS_ATTRIBUTE3 => get_char_val('ASS_ATTRIBUTE3',l_mapping_table)
,p_ASS_ATTRIBUTE4 => get_char_val('ASS_ATTRIBUTE4',l_mapping_table)
,p_ASS_ATTRIBUTE5 => get_char_val('ASS_ATTRIBUTE5',l_mapping_table)
,p_ASS_ATTRIBUTE6 => get_char_val('ASS_ATTRIBUTE6',l_mapping_table)
,p_ASS_ATTRIBUTE7 => get_char_val('ASS_ATTRIBUTE7',l_mapping_table)
,p_ASS_ATTRIBUTE8 => get_char_val('ASS_ATTRIBUTE8',l_mapping_table)
,p_ASS_ATTRIBUTE9 => get_char_val('ASS_ATTRIBUTE9',l_mapping_table)
,p_ASS_ATTRIBUTE10 => get_char_val('ASS_ATTRIBUTE10',l_mapping_table)
,p_ASS_ATTRIBUTE11 => get_char_val('ASS_ATTRIBUTE11',l_mapping_table)
,p_ASS_ATTRIBUTE12 => get_char_val('ASS_ATTRIBUTE12',l_mapping_table)
,p_ASS_ATTRIBUTE13 => get_char_val('ASS_ATTRIBUTE13',l_mapping_table)
,p_ASS_ATTRIBUTE14 => get_char_val('ASS_ATTRIBUTE14',l_mapping_table)
,p_ASS_ATTRIBUTE15 => get_char_val('ASS_ATTRIBUTE15',l_mapping_table)
,p_ASS_ATTRIBUTE16 => get_char_val('ASS_ATTRIBUTE16',l_mapping_table)
,p_ASS_ATTRIBUTE17 => get_char_val('ASS_ATTRIBUTE17',l_mapping_table)
,p_ASS_ATTRIBUTE18 => get_char_val('ASS_ATTRIBUTE18',l_mapping_table)
,p_ASS_ATTRIBUTE19 => get_char_val('ASS_ATTRIBUTE19',l_mapping_table)
,p_ASS_ATTRIBUTE20 => get_char_val('ASS_ATTRIBUTE20',l_mapping_table)
,p_ASS_ATTRIBUTE21 => get_char_val('ASS_ATTRIBUTE21',l_mapping_table)
,p_ASS_ATTRIBUTE22 => get_char_val('ASS_ATTRIBUTE22',l_mapping_table)
,p_ASS_ATTRIBUTE23 => get_char_val('ASS_ATTRIBUTE23',l_mapping_table)
,p_ASS_ATTRIBUTE24 => get_char_val('ASS_ATTRIBUTE24',l_mapping_table)
,p_ASS_ATTRIBUTE25 => get_char_val('ASS_ATTRIBUTE25',l_mapping_table)
,p_ASS_ATTRIBUTE26 => get_char_val('ASS_ATTRIBUTE26',l_mapping_table)
,p_ASS_ATTRIBUTE27 => get_char_val('ASS_ATTRIBUTE27',l_mapping_table)
,p_ASS_ATTRIBUTE28 => get_char_val('ASS_ATTRIBUTE28',l_mapping_table)
,p_ASS_ATTRIBUTE29 => get_char_val('ASS_ATTRIBUTE29',l_mapping_table)
,p_ASS_ATTRIBUTE30 => get_char_val('ASS_ATTRIBUTE30',l_mapping_table)
,p_SEGMENT1 => get_char_val('SEGMENT1',l_mapping_table)
,p_SEGMENT2 => get_char_val('SEGMENT2',l_mapping_table)
,p_SEGMENT3 => get_char_val('SEGMENT3',l_mapping_table)
,p_SEGMENT4 => get_char_val('SEGMENT4',l_mapping_table)
,p_SEGMENT5 => get_char_val('SEGMENT5',l_mapping_table)
,p_SEGMENT6 => get_char_val('SEGMENT6',l_mapping_table)
,p_SEGMENT7 => get_char_val('SEGMENT7',l_mapping_table)
,p_SEGMENT8 => get_char_val('SEGMENT8',l_mapping_table)
,p_SEGMENT9 => get_char_val('SEGMENT9',l_mapping_table)
,p_SEGMENT10 => get_char_val('SEGMENT10',l_mapping_table)
,p_SEGMENT11 => get_char_val('SEGMENT11',l_mapping_table)
,p_SEGMENT12 => get_char_val('SEGMENT12',l_mapping_table)
,p_SEGMENT13 => get_char_val('SEGMENT13',l_mapping_table)
,p_SEGMENT14 => get_char_val('SEGMENT14',l_mapping_table)
,p_SEGMENT15 => get_char_val('SEGMENT15',l_mapping_table)
,p_SEGMENT16 => get_char_val('SEGMENT16',l_mapping_table)
,p_SEGMENT17 => get_char_val('SEGMENT17',l_mapping_table)
,p_SEGMENT18 => get_char_val('SEGMENT18',l_mapping_table)
,p_SEGMENT19 => get_char_val('SEGMENT19',l_mapping_table)
,p_SEGMENT20 => get_char_val('SEGMENT20',l_mapping_table)
,p_SEGMENT21 => get_char_val('SEGMENT21',l_mapping_table)
,p_SEGMENT22 => get_char_val('SEGMENT22',l_mapping_table)
,p_SEGMENT23 => get_char_val('SEGMENT23',l_mapping_table)
,p_SEGMENT24 => get_char_val('SEGMENT24',l_mapping_table)
,p_SEGMENT25 => get_char_val('SEGMENT25',l_mapping_table)
,p_SEGMENT26 => get_char_val('SEGMENT26',l_mapping_table)
,p_SEGMENT27 => get_char_val('SEGMENT27',l_mapping_table)
,p_SEGMENT28 => get_char_val('SEGMENT28',l_mapping_table)
,p_SEGMENT29 => get_char_val('SEGMENT29',l_mapping_table)
,p_SEGMENT30 => get_char_val('SEGMENT30',l_mapping_table)
,P_CAGR_GRADE_DEF_ID => l_CAGR_GRADE_DEF_ID
,P_CAGR_CONCATENATED_SEGMENTS => l_CAGR_CONCATENATED_SEGMENTS
,p_comment_id => l_comment_id
,p_soft_coding_keyflex_id => l_soft_coding_keyflex_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_concatenated_segments => l_concatenated_segments
,p_no_managers_warning => l_no_managers_warning
,p_other_manager_warning => l_other_manager_warning);
per_cagr_utility_pkg.put_log(' done HR_ASSIGNMENT_API.UPDATE_EMP_ASG, OVN: '||l_ovn,1);
elsif l_api_details(1).api_name = 'HR_ASSIGNMENT_API.UPDATE_EMP_ASG_CRITERIA' then
get_ovn_and_mode (p_table_name => 'PER_ALL_ASSIGNMENTS_F'
,p_dt_flag => 'Y'
,p_pk => 'assignment_id'
,p_pk_id => p_params.assignment_id
,p_effective_date => p_params.effective_date
,p_mode => l_dt_mode
,p_ovn => l_ovn);
per_cagr_utility_pkg.put_log(' calling HR_ASSIGNMENT_API.UPDATE_EMP_ASG_CRITERIA in mode: '||l_dt_mode||', OVN: '||l_ovn,1);
hr_assignment_api.update_emp_asg_criteria
(p_effective_date => p_params.effective_date
,p_datetrack_update_mode => l_dt_mode
,p_assignment_id => p_params.assignment_id
,p_object_version_number => l_ovn
,p_grade_id => get_num_val('GRADE_ID',l_mapping_table)
,p_job_id => get_num_val('JOB_ID',l_mapping_table)
,p_payroll_id => get_num_val('PAYROLL_ID',l_mapping_table)
,p_organization_id => get_num_val('ORGANIZATION_ID',l_mapping_table)
,p_employment_category => get_char_val('EMPLOYMENT_CATEGORY',l_mapping_table)
,p_pay_basis_id => get_num_val('PAY_BASIS_ID',l_mapping_table)
,p_special_ceiling_step_id => l_special_ceiling_step_id
,p_group_name => l_group_name
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_people_group_id => l_people_group_id
,p_org_now_no_manager_warning => l_org_now_no_manager_warning
,p_other_manager_warning => l_other_manager_warning
,p_spp_delete_warning => l_spp_delete_warning
,p_entries_changed_warning => l_entries_changed_warning
,p_tax_district_changed_warning => l_tax_district_changed_warning);
elsif l_spp_delete_warning then
l_warn_message := fnd_message.get_string(
APPIN => 'PER'
, NAMEIN => 'HR_289826_SPP_DELETE_WARN_API');
per_cagr_utility_pkg.put_log(' done HR_ASSIGNMENT_API.UPDATE_EMP_ASG_CRITERIA, OVN: '||l_ovn,1);
l_api_details.delete;
l_mapping_table.delete;
SELECT apip.parameter_name
,apip.cagr_api_param_id
,apip.column_type
,apip.uom_parameter
,api.api_name
FROM per_cagr_api_parameters apip, per_cagr_apis api
WHERE api.api_name = v_api_name
AND api.cagr_api_id = apip.cagr_api_id;
SELECT placement_id
FROM per_spinal_point_placements_f
WHERE assignment_id = p_params.assignment_id
AND p_params.effective_date BETWEEN effective_start_date and nvl(effective_end_date,hr_general.end_of_time);
l_dt_mode VARCHAR2(30) := 'UPDATE';
l_api_name := 'HR_SP_PLACEMENT_API.UPDATE_SPP';
for v_api_details in csr_api('HR_SP_PLACEMENT_API.UPDATE_SPP') loop
l_count := l_count+1;
elsif l_api_name = 'HR_SP_PLACEMENT_API.UPDATE_SPP' then
per_cagr_utility_pkg.put_log(' calling HR_SP_PLACEMENT_API.UPDATE_SPP',1);
hr_sp_placement_api.update_spp
(p_effective_date => p_params.effective_date
,p_datetrack_mode => l_dt_mode
,p_placement_id => l_placement_id
,p_step_id => get_num_val('STEP_ID',l_mapping_table)
,p_object_version_number => l_ovn
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date);
per_cagr_utility_pkg.put_log(' done HR_SP_PLACEMENT_API.UPDATE_SPP',1);
l_api_details.delete;
l_mapping_table.delete;
SELECT legislation_code
FROM per_business_groups_perf
WHERE business_group_id = p_params.business_group_id
and rownum = 1; -- Added for bug 3387328 to improve performance.
SELECT ee.element_entry_id, ee.entry_type, el.element_link_id
FROM pay_element_links el, pay_element_entries ee
WHERE el.ELEMENT_TYPE_ID = p_element_type_id
AND ee.ELEMENT_LINK_ID = el.ELEMENT_LINK_ID
AND ee.ASSIGNMENT_ID = p_assignment_id
AND (p_entry_id is null or (p_entry_id is not null and ee.element_entry_id = p_entry_id))
AND p_params.effective_date BETWEEN el.EFFECTIVE_START_DATE
AND nvl(el.EFFECTIVE_END_DATE,hr_general.end_of_time)
AND p_params.effective_date BETWEEN ee.EFFECTIVE_START_DATE
AND nvl(ee.EFFECTIVE_END_DATE,hr_general.end_of_time);
Select distinct cer.element_type_id
From per_cagr_entitlement_results cer
Where cer.ASSIGNMENT_ID = p_assignment_id
and cer.end_date is null --fix for bug 5747086
And not exists (select 1
From pay_element_entries ee
Where cer.assignment_id = ee.assignment_id
And cer.element_type_id = ee.element_type_id
And ee.element_type_id = p_element_type_id
And p_params.effective_date BETWEEN ee.EFFECTIVE_START_DATE
AND nvl(ee.EFFECTIVE_END_DATE,hr_general.end_of_time))
Order by cer.element_type_id;
select cer.cagr_entitlement_item_id
from per_cagr_entitlement_results cer,
per_cagr_entitlement_items cei
where cer.assignment_id = p_assignment_id
and cer.element_type_id = p_element_type_id
and cer.cagr_entitlement_item_id = cei.cagr_entitlement_item_id
and nvl(cei.auto_create_entries_flag,'N') = 'Y'
order by cer.cagr_entitlement_item_id;
SELECT DISTINCT cagr_entitlement_item_id
FROM per_cagr_entitlement_results cer
WHERE ASSIGNMENT_ID = p_assignment_id
AND NOT EXISTS (SELECT 1 FROM per_cagr_entitlement_results
WHERE cer.cagr_entitlement_item_id = cagr_entitlement_item_id
AND start_date = trunc(p_params.effective_date)
AND ASSIGNMENT_ID = p_assignment_id)
AND EXISTS ( SELECT 1 FROM per_cagr_entitlement_results
WHERE cer.cagr_entitlement_item_id = cagr_entitlement_item_id
AND end_date = trunc(p_params.effective_date) - 1
AND ASSIGNMENT_ID = p_assignment_id);
SELECT cei.element_type_id,cer.start_date,cer.end_date
FROM per_cagr_entitlement_results cer,PER_CAGR_ENTITLEMENT_ITEMS cei
WHERE cer.ASSIGNMENT_ID = p_assignment_id
AND cer.end_date = trunc(p_params.effective_date)-1
AND cer.cagr_entitlement_item_id=cei.cagr_entitlement_item_id
AND cei.cagr_entitlement_item_id = p_entitlement_id;
SELECT element_entry_id,object_version_number
FROM pay_element_entries_f
WHERE element_type_id=p_element_type_id
AND assignment_id=p_assignment_id
AND effective_start_date<=p_end_date
AND effective_end_date>p_end_date;
l_dt_mode VARCHAR2(30) := 'UPDATE';
per_cagr_utility_pkg.put_log('Override entry exists - only this will be updated: '||v_entries.element_entry_id);
per_cagr_utility_pkg.put_log(' calling PY_ELEMENT_ENTRY_API.UPDATE_ELEMENT_ENTRY in mode: '||l_dt_mode,1);
py_element_entry_api.update_element_entry(
p_datetrack_update_mode => l_dt_mode,
p_effective_date => p_params.effective_date,
p_business_group_id => p_params.business_group_id,
p_element_entry_id => v_entries.element_entry_id,
p_object_version_number => l_ovn,
p_input_value_id1 => get_num_val('INPUT_VALUE_ID1',l_mapping_table),
p_input_value_id2 => get_num_val('INPUT_VALUE_ID2',l_mapping_table),
p_input_value_id3 => get_num_val('INPUT_VALUE_ID3',l_mapping_table),
p_input_value_id4 => get_num_val('INPUT_VALUE_ID4',l_mapping_table),
p_input_value_id5 => get_num_val('INPUT_VALUE_ID5',l_mapping_table),
p_input_value_id6 => get_num_val('INPUT_VALUE_ID6',l_mapping_table),
p_input_value_id7 => get_num_val('INPUT_VALUE_ID7',l_mapping_table),
p_input_value_id8 => get_num_val('INPUT_VALUE_ID8',l_mapping_table),
p_input_value_id9 => get_num_val('INPUT_VALUE_ID9',l_mapping_table),
p_input_value_id10 => get_num_val('INPUT_VALUE_ID10',l_mapping_table),
p_input_value_id11 => get_num_val('INPUT_VALUE_ID11',l_mapping_table),
p_input_value_id12 => get_num_val('INPUT_VALUE_ID12',l_mapping_table),
p_input_value_id13 => get_num_val('INPUT_VALUE_ID13',l_mapping_table),
p_input_value_id14 => get_num_val('INPUT_VALUE_ID14',l_mapping_table),
p_input_value_id15 => get_num_val('INPUT_VALUE_ID15',l_mapping_table),
p_entry_value1 => get_char_val('ENTRY_VALUE1',l_mapping_table),
p_entry_value2 => get_char_val('ENTRY_VALUE2',l_mapping_table),
p_entry_value3 => get_char_val('ENTRY_VALUE3',l_mapping_table),
p_entry_value4 => get_char_val('ENTRY_VALUE4',l_mapping_table),
p_entry_value5 => get_char_val('ENTRY_VALUE5',l_mapping_table),
p_entry_value6 => get_char_val('ENTRY_VALUE6',l_mapping_table),
p_entry_value7 => get_char_val('ENTRY_VALUE7',l_mapping_table),
p_entry_value8 => get_char_val('ENTRY_VALUE8',l_mapping_table),
p_entry_value9 => get_char_val('ENTRY_VALUE9',l_mapping_table),
p_entry_value10 => get_char_val('ENTRY_VALUE10',l_mapping_table),
p_entry_value11 => get_char_val('ENTRY_VALUE11',l_mapping_table),
p_entry_value12 => get_char_val('ENTRY_VALUE12',l_mapping_table),
p_entry_value13 => get_char_val('ENTRY_VALUE13',l_mapping_table),
p_entry_value14 => get_char_val('ENTRY_VALUE14',l_mapping_table),
p_entry_value15 => get_char_val('ENTRY_VALUE15',l_mapping_table),
p_effective_start_date => l_effective_start_date,
p_effective_end_date => l_effective_end_date,
p_update_warning => l_warning);
per_cagr_utility_pkg.put_log(' WARNING: p_update_warning',1);
per_cagr_utility_pkg.put_log(' done PY_ELEMENT_ENTRY_API.UPDATE_ELEMENT_ENTRY in mode: '||l_dt_mode,1);
l_mapping_table.delete;
l_mapping_table.delete;
py_element_entry_api.delete_element_entry
(p_validate => FALSE
,p_datetrack_delete_mode => 'DELETE'
,p_effective_date => l_end_date
,p_element_entry_id => l_element_entry_id
,p_object_version_number => l_ovn
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_delete_warning => l_warning
);
,p_select_flag IN varchar2) IS
--
-- This routine controls the apply processing for a particular assignment on the effective_date.
--
-- get ASG category results to be applied, for an asg.
-- either chosen or beneficial if none chosen
CURSOR csr_ASG_denorm_results IS
SELECT cagr_entitlement_result_id
,assignment_id
,cagr_entitlement_item_id
,cagr_api_id
,cagr_api_param_id
,value
,units_of_measure
FROM per_cagr_entitlement_results
WHERE category_name = 'ASG'
AND (p_params.assignment_id is not null and assignment_id = p_params.assignment_id)
AND (chosen_flag = 'Y'
OR (beneficial_flag = 'Y'
and not exists (select 'X' from per_cagr_entitlement_results res1
where res1.assignment_id = p_params.assignment_id
and res1.cagr_entitlement_item_id = cagr_entitlement_item_id
AND res1.chosen_flag = 'Y'
AND p_params.effective_date between start_date and nvl(end_date,hr_general.end_of_time))))
AND cagr_api_id is not null
AND p_params.effective_date between start_date and nvl(end_date,hr_general.end_of_time)
ORDER BY cagr_api_id;
SELECT cagr_entitlement_result_id
,assignment_id
,cagr_entitlement_item_id
,cagr_api_id
,cagr_api_param_id
,category_name
,grade_spine_id
,parent_spine_id
,step_id
,from_step_id
,to_step_id
,value
,units_of_measure
FROM per_cagr_entitlement_results
WHERE category_name = 'PYS'
AND (p_params.assignment_id is not null and assignment_id = p_params.assignment_id)
AND (chosen_flag = 'Y'
OR (beneficial_flag = 'Y'
and not exists (select 'X' from per_cagr_entitlement_results res1
where res1.assignment_id = p_params.assignment_id
and res1.cagr_entitlement_item_id = cagr_entitlement_item_id
AND res1.chosen_flag = 'Y'
AND p_params.effective_date between start_date and nvl(end_date,hr_general.end_of_time))))
AND cagr_api_id is not null
AND p_params.effective_date between start_date and nvl(end_date,hr_general.end_of_time)
ORDER BY cagr_api_id;
SELECT cagr_entitlement_result_id
,assignment_id
,cagr_entitlement_item_id
,category_name
,element_type_id
,input_value_id
,value
,multiple_entries_allowed_flag
FROM per_cagr_entitlement_results
WHERE category_name = 'PAY'
AND (p_params.assignment_id is not null and assignment_id = p_params.assignment_id)
AND (chosen_flag = 'Y'
OR (beneficial_flag = 'Y'
and not exists (select 'X' from per_cagr_entitlement_results res1
where res1.assignment_id = p_params.assignment_id
and res1.cagr_entitlement_item_id = cagr_entitlement_item_id
AND res1.chosen_flag = 'Y'
AND p_params.effective_date between start_date and nvl(end_date,hr_general.end_of_time))))
AND element_type_id is not null
AND input_value_id is not null
AND p_params.effective_date between start_date and nvl(end_date,hr_general.end_of_time)
ORDER BY element_type_id;
l_ASG_results.delete;
l_ASG_results.delete;
l_PYS_results.delete;
l_PYS_results.delete;
l_PAY_results.delete;
l_PAY_results.delete;
If p_select_flag = 'B' then
per_cagr_utility_pkg.put_log(' Beneficial values to be applied.',1);
elsif p_select_flag = 'C' then
per_cagr_utility_pkg.put_log(' Chosen values to be applied.',1);
do_apply_for_assignment(p_params, p_select_flag);
do_apply_for_assignment(p_params, p_select_flag);
,p_select_flag in varchar2 default 'B'
,p_commit_flag in varchar2 default 'N'
,p_cagr_request_id in out nocopy number) IS
-- (individual parameter interface)
-- This procedure is the main interface to the denormalization process, and is called
-- directly by the concurrent manager and the form PERWSCAR.fmb to apply values to HRMS
-- according to the control parameters supplied.
--
-- It calls PER_CAGR_DENORMALIZE_PKG.initialise (parameter interface) after
-- validating individual parameters, populating the control record structure structure, creating
-- a per_cagr_request record (if required) and initializing logging for the denormalisation run.
--
-- P_SELECT_FLAG IS NOW REDUNDANT - the beneficial flag result will be applied unless there is
-- another result for the entitlement that has been chosen, in which case that will be applied.
-- (old behaviour: process detects if it has been run from concurrent manager or from form and
-- differs in behaviour as follows: If run from form, the form user has chosen a particular record
-- (which may differ from the most beneficial) and so this process will trigger the result with
-- chosen_flag = Y to be applied (ignoring beneficial_flag) for each item. If run from conc manager,
-- of the set of results for each item in the cache the process will select the record with
-- beneficial_flag = 'Y' (if any, and ignoring chosen_flag values) for application to HRMS)
--
l_proc constant varchar2(61) := g_pkg || 'initialise';
/* select_flag is redundant
if not (p_select_flag in ('B','C')) then
per_cagr_utility_pkg.log_and_raise_error('HR_XXXXX_CAGR_INV_SFLAG'
,p_cagr_request_id);
If p_select_flag = 'B' then
per_cagr_utility_pkg.put_log(' Beneficial values to be applied.',1);
elsif p_select_flag = 'C' then
per_cagr_utility_pkg.put_log(' Chosen values to be applied.',1);
,p_select_flag => p_select_flag);