[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT asg.COLLECTIVE_AGREEMENT_ID
,asg.ASSIGNMENT_ID
,asg.PERSON_ID
,res.VALUE
,res.RANGE_FROM
,res.RANGE_TO
,res.GRADE_SPINE_ID
,res.PARENT_SPINE_ID
,res.STEP_ID
,res.FROM_STEP_ID
,res.TO_STEP_ID
,res.CHOSEN_FLAG
,res.BENEFICIAL_FLAG
FROM per_all_assignments_f asg, per_cagr_entitlement_results res
WHERE asg.assignment_id = res.assignment_id
AND asg.primary_flag = 'Y'
AND p_process_date between asg.effective_start_date and asg.effective_end_date
AND res.cagr_entitlement_item_id = p_entitlement_item_id
AND p_process_date between res.start_date and nvl(res.end_date, hr_general.end_of_time)
AND (p_collective_agreement_id is null or asg.collective_agreement_id = p_collective_agreement_id)
AND (p_value is null or res.value = p_value)
AND (p_step_id is null or res.step_id = p_step_id)
order by asg.PERSON_ID;
SELECT item.opt_id
FROM per_cagr_entitlement_items item
WHERE item.cagr_entitlement_item_id = p_params.entitlement_item_id
AND exists (select 'x'
from per_collective_agreements cagr, per_all_assignments_f asg
where cagr.status = 'A'
and p_params.effective_date >= cagr.start_date
and cagr.collective_agreement_id in (select distinct(pce.collective_agreement_id)
from per_cagr_entitlements pce, per_cagr_entitlement_lines_f pcel
where pce.CAGR_ENTITLEMENT_ID = pcel.CAGR_ENTITLEMENT_ID
and pce.STATUS = 'A'
and p_params.effective_date between pce.start_date
and nvl(pce.end_date,hr_general.end_of_time)
and pcel.STATUS = 'A'
and pcel.OIPL_ID <> 0 and pcel.ELIGY_PRFL_ID <> 0
and p_params.effective_date between pcel.effective_start_date
and pcel.effective_end_date
and pce.cagr_entitlement_item_id = p_params.entitlement_item_id
and (p_params.collective_agreement_id is null or
pce.collective_agreement_id = p_params.collective_agreement_id))
and cagr.collective_agreement_id = asg.collective_agreement_id
and asg.PRIMARY_FLAG = 'Y'
and p_params.effective_date BETWEEN asg.effective_start_date
AND asg.effective_end_date);
SELECT agr.pl_id
FROM per_collective_agreements agr
WHERE agr.collective_agreement_id = p_params.collective_agreement_id;
SELECT cagr.collective_agreement_id, asg.assignment_id, asg.grade_id, asg.person_id
FROM per_collective_agreements cagr, per_all_assignments_f asg
WHERE cagr.status = 'A'
AND p_params.effective_date >= cagr.start_date
AND cagr.collective_agreement_id in (select distinct(pce.collective_agreement_id)
from per_cagr_entitlements pce
where pce.cagr_entitlement_item_id = p_params.entitlement_item_id
and pce.STATUS = 'A'
and p_params.effective_date between pce.start_date
and nvl(pce.end_date,hr_general.end_of_time)
and (p_params.collective_agreement_id is null or
pce.collective_agreement_id = p_params.collective_agreement_id))
AND cagr.collective_agreement_id = asg.collective_agreement_id
AND asg.PRIMARY_FLAG = 'Y'
AND p_params.effective_date BETWEEN asg.effective_start_date
AND asg.effective_end_date
ORDER BY cagr.collective_agreement_id;
SELECT pl_id
FROM per_collective_agreements cagr
WHERE cagr.COLLECTIVE_AGREEMENT_ID = p_params.COLLECTIVE_AGREEMENT_ID
AND p_params.effective_date BETWEEN cagr.START_DATE
AND nvl(cagr.END_DATE, hr_general.end_of_time)
AND cagr.STATUS = 'A'
AND exists (select 'x'
from per_all_assignments_f asg
where p_params.effective_date BETWEEN asg.effective_start_date
AND asg.effective_end_date
and asg.COLLECTIVE_AGREEMENT_ID = p_params.COLLECTIVE_AGREEMENT_ID
and asg.PRIMARY_FLAG = 'Y')
AND exists (select 'x'
from per_cagr_entitlements pce, per_cagr_entitlement_lines_f pcel
where pce.CAGR_ENTITLEMENT_ID = pcel.CAGR_ENTITLEMENT_ID
and pce.STATUS = 'A'
and p_params.effective_date between pce.start_date
and nvl(pce.end_date,hr_general.end_of_time)
and pcel.STATUS = 'A'
and pcel.OIPL_ID <> 0 and pcel.ELIGY_PRFL_ID <> 0
and p_params.effective_date between pcel.effective_start_date and pcel.effective_end_date
and pce.collective_agreement_id = p_params.COLLECTIVE_AGREEMENT_ID);
SELECT assignment_id, grade_id, person_id
FROM per_all_assignments_f asg
WHERE 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.NAME,
cagr.PL_ID,
pce.CAGR_ENTITLEMENT_ITEM_ID,
pce.CAGR_ENTITLEMENT_ID,
pce.FORMULA_CRITERIA,
pce.FORMULA_ID,
pcei.ITEM_NAME,
pcei.BUSINESS_GROUP_ID,
pcei.FLEX_VALUE_SET_ID,
pcei.BENEFICIAL_RULE,
pcei.UOM "UNITS_OF_MEASURE",
pcei.CAGR_API_ID,
pcei.CAGR_API_PARAM_ID,
pcei.ELEMENT_TYPE_ID,
pcei.INPUT_VALUE_ID,
pcei.CATEGORY_NAME,
pcei.COLUMN_TYPE,
pcei.COLUMN_SIZE,
pcei.MULTIPLE_ENTRIES_ALLOWED_FLAG,
pcei.BENEFICIAL_RULE_VALUE_SET_ID,
pcel.CAGR_ENTITLEMENT_LINE_ID,
pcel.VALUE,
pcel.RANGE_FROM,
pcel.RANGE_TO,
pcel.GRADE_SPINE_ID,
pcel.PARENT_SPINE_ID,
pcel.STEP_ID,
pcel.FROM_STEP_ID,
pcel.TO_STEP_ID,
pcel.OIPL_ID,
pcel.ELIGY_PRFL_ID -- BEN elig profile
FROM per_collective_agreements cagr,
per_cagr_entitlements pce,
per_cagr_entitlement_items pcei,
per_cagr_entitlement_lines_f pcel
WHERE cagr.COLLECTIVE_AGREEMENT_ID = p_params.collective_agreement_id
AND p_params.effective_date BETWEEN cagr.START_DATE
AND nvl(cagr.END_DATE, hr_general.end_of_time)
AND cagr.STATUS = 'A'
AND cagr.COLLECTIVE_AGREEMENT_ID = pce.COLLECTIVE_AGREEMENT_ID
AND pce.STATUS = 'A'
AND p_params.effective_date BETWEEN pce.START_DATE
AND nvl(pce.END_DATE,hr_general.end_of_time)
AND pce.CAGR_ENTITLEMENT_ITEM_ID = pcei.CAGR_ENTITLEMENT_ITEM_ID
AND pce.CAGR_ENTITLEMENT_ID = pcel.CAGR_ENTITLEMENT_ID (+)
AND ((pcel.CAGR_ENTITLEMENT_ID IS NOT NULL
AND p_params.effective_date BETWEEN pcel.effective_start_date
AND pcel.effective_end_date
AND pcel.STATUS = 'A'
OR pcel.CAGR_ENTITLEMENT_ID IS NULL))
ORDER BY pce.CAGR_ENTITLEMENT_ITEM_ID;
SELECT asg.COLLECTIVE_AGREEMENT_ID,
asg.PERSON_ID,
cagr.PL_ID
FROM per_all_assignments_f asg, per_collective_agreements cagr
WHERE asg.ASSIGNMENT_ID = p_params.assignment_id
AND p_params.effective_date BETWEEN asg.effective_start_date
AND asg.effective_end_date
AND asg.PRIMARY_FLAG = 'Y'
AND asg.COLLECTIVE_AGREEMENT_ID = cagr.COLLECTIVE_AGREEMENT_ID
AND p_params.effective_date BETWEEN cagr.START_DATE
AND nvl(cagr.END_DATE, hr_general.end_of_time)
AND cagr.STATUS = 'A'
AND exists (select 'x'
from per_cagr_entitlements pce, per_cagr_entitlement_lines_f pcel
where pce.CAGR_ENTITLEMENT_ID = pcel.CAGR_ENTITLEMENT_ID
and pce.STATUS = 'A'
and p_params.effective_date between pce.start_date
and nvl(pce.end_date,hr_general.end_of_time)
and pcel.STATUS = 'A'
and pcel.OIPL_ID <> 0 and pcel.ELIGY_PRFL_ID <> 0
and p_params.effective_date between pcel.effective_start_date and pcel.effective_end_date
and pce.collective_agreement_id = asg.COLLECTIVE_AGREEMENT_ID);
SELECT asg.COLLECTIVE_AGREEMENT_ID,
asg.GRADE_ID, -- for PYS eligibility
cagr.NAME,
cagr.PL_ID, -- BEN comp obj
pce.CAGR_ENTITLEMENT_ITEM_ID,
pce.CAGR_ENTITLEMENT_ID,
pce.FORMULA_CRITERIA,
pce.FORMULA_ID,
pcei.ITEM_NAME,
pcei.BUSINESS_GROUP_ID,
pcei.FLEX_VALUE_SET_ID,
pcei.BENEFICIAL_RULE,
pcei.UOM "UNITS_OF_MEASURE",
pcei.CAGR_API_ID, -- set for denorm item
pcei.CAGR_API_PARAM_ID,
pcei.ELEMENT_TYPE_ID,
pcei.INPUT_VALUE_ID,
pcei.CATEGORY_NAME,
pcei.COLUMN_TYPE,
pcei.COLUMN_SIZE,
pcei.MULTIPLE_ENTRIES_ALLOWED_FLAG,
pcei.BENEFICIAL_RULE_VALUE_SET_ID,
pcel.CAGR_ENTITLEMENT_LINE_ID,
pcel.VALUE,
pcel.RANGE_FROM,
pcel.RANGE_TO,
pcel.GRADE_SPINE_ID,
pcel.PARENT_SPINE_ID,
pcel.STEP_ID,
pcel.FROM_STEP_ID,
pcel.TO_STEP_ID,
pcel.OIPL_ID, -- BEN comp obj
pcel.ELIGY_PRFL_ID -- BEN elig profile
FROM per_all_assignments_f asg,
per_collective_agreements cagr,
per_cagr_entitlements pce,
per_cagr_entitlement_items pcei,
per_cagr_entitlement_lines_f pcel
WHERE asg.ASSIGNMENT_ID = p_params.assignment_id
AND p_params.effective_date BETWEEN asg.effective_start_date
AND asg.effective_end_date
AND asg.PRIMARY_FLAG = 'Y'
AND asg.COLLECTIVE_AGREEMENT_ID = cagr.COLLECTIVE_AGREEMENT_ID
AND p_params.effective_date BETWEEN cagr.START_DATE
AND nvl(cagr.END_DATE, hr_general.end_of_time)
AND cagr.STATUS = 'A'
AND asg.COLLECTIVE_AGREEMENT_ID = pce.COLLECTIVE_AGREEMENT_ID
AND pce.STATUS = 'A'
AND p_params.effective_date BETWEEN pce.START_DATE
AND nvl(pce.END_DATE,hr_general.end_of_time)
AND pce.CAGR_ENTITLEMENT_ITEM_ID = pcei.CAGR_ENTITLEMENT_ITEM_ID
AND pce.CAGR_ENTITLEMENT_ID = pcel.CAGR_ENTITLEMENT_ID (+)
AND ((pcel.CAGR_ENTITLEMENT_ID IS NOT NULL
AND p_params.effective_date BETWEEN pcel.effective_start_date
AND pcel.effective_end_date
AND pcel.STATUS = 'A'
OR pcel.CAGR_ENTITLEMENT_ID IS NULL))
ORDER BY pce.CAGR_ENTITLEMENT_ITEM_ID;
SELECT asg.COLLECTIVE_AGREEMENT_ID,
asg.PERSON_ID,
cagr.PL_ID
FROM per_all_assignments_f asg, per_collective_agreements cagr
WHERE asg.assignment_id = p_params.assignment_id
AND p_params.effective_date BETWEEN asg.effective_start_date
AND asg.effective_end_date
AND asg.PRIMARY_FLAG = 'Y'
AND asg.collective_agreement_id = cagr.collective_agreement_id
AND p_params.effective_date BETWEEN cagr.start_date
AND nvl(cagr.end_date, hr_general.end_of_time)
AND cagr.status = 'A'
AND exists (select 'x'
from per_cagr_entitlements pce, per_cagr_entitlement_lines_f pcel
where pce.CAGR_ENTITLEMENT_ID = pcel.CAGR_ENTITLEMENT_ID
and pce.CAGR_ENTITLEMENT_ITEM_ID = p_params.entitlement_item_id -- only 1 entitlement
and pce.STATUS = 'A'
and p_params.effective_date between pce.start_date
and nvl(pce.end_date,hr_general.end_of_time)
and pcel.STATUS = 'A'
and pcel.OIPL_ID <> 0 and pcel.ELIGY_PRFL_ID <> 0 -- ignore default elig lines
and p_params.effective_date between pcel.effective_start_date
and pcel.effective_end_date);
SELECT asg.COLLECTIVE_AGREEMENT_ID,
asg.GRADE_ID, -- for PYS eligibility
cagr.NAME,
cagr.PL_ID, -- BEN comp obj
pce.CAGR_ENTITLEMENT_ITEM_ID,
pce.CAGR_ENTITLEMENT_ID,
pce.FORMULA_CRITERIA,
pce.FORMULA_ID,
pcei.ITEM_NAME,
pcei.BUSINESS_GROUP_ID,
pcei.FLEX_VALUE_SET_ID,
pcei.BENEFICIAL_RULE,
pcei.UOM "UNITS_OF_MEASURE",
pcei.CAGR_API_ID, -- set for denorm item
pcei.CAGR_API_PARAM_ID,
pcei.ELEMENT_TYPE_ID,
pcei.INPUT_VALUE_ID,
pcei.CATEGORY_NAME,
pcei.COLUMN_TYPE,
pcei.COLUMN_SIZE,
pcei.MULTIPLE_ENTRIES_ALLOWED_FLAG,
pcei.BENEFICIAL_RULE_VALUE_SET_ID,
pcel.CAGR_ENTITLEMENT_LINE_ID,
pcel.VALUE,
pcel.RANGE_FROM,
pcel.RANGE_TO,
pcel.GRADE_SPINE_ID,
pcel.PARENT_SPINE_ID,
pcel.STEP_ID,
pcel.FROM_STEP_ID,
pcel.TO_STEP_ID,
pcel.OIPL_ID, -- BEN comp obj
pcel.ELIGY_PRFL_ID -- BEN elig profile
FROM per_all_assignments_f asg,
per_collective_agreements cagr,
per_cagr_entitlements pce,
per_cagr_entitlement_items pcei,
per_cagr_entitlement_lines_f pcel
WHERE asg.ASSIGNMENT_ID = p_params.assignment_id
AND p_params.effective_date BETWEEN asg.effective_start_date
AND asg.effective_end_date
AND asg.PRIMARY_FLAG = 'Y'
AND asg.COLLECTIVE_AGREEMENT_ID = cagr.COLLECTIVE_AGREEMENT_ID
AND p_params.effective_date BETWEEN cagr.START_DATE
AND nvl(cagr.END_DATE, hr_general.end_of_time)
AND cagr.STATUS = 'A'
AND asg.COLLECTIVE_AGREEMENT_ID = pce.COLLECTIVE_AGREEMENT_ID
AND pce.STATUS = 'A'
AND pce.CAGR_ENTITLEMENT_ITEM_ID = p_params.entitlement_item_id -- only 1 entitlement
AND p_params.effective_date BETWEEN pce.START_DATE
AND nvl(pce.END_DATE,hr_general.end_of_time)
AND pce.CAGR_ENTITLEMENT_ITEM_ID = pcei.CAGR_ENTITLEMENT_ITEM_ID
AND pce.CAGR_ENTITLEMENT_ID = pcel.CAGR_ENTITLEMENT_ID (+)
AND ((pcel.CAGR_ENTITLEMENT_ID IS NOT NULL
AND p_params.effective_date BETWEEN pcel.effective_start_date
AND pcel.effective_end_date
AND pcel.STATUS = 'A'
OR pcel.CAGR_ENTITLEMENT_ID IS NULL))
ORDER BY pce.CAGR_ENTITLEMENT_ITEM_ID;
SELECT 'X'
FROM per_all_assignments_f asg
WHERE asg.assignment_id = p_params.assignment_id
AND p_params.effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date
AND asg.primary_flag = 'Y';
l_update_cache BOOLEAN := FALSE;
SELECT cagr_entitlement_item_id,
cagr_entitlement_id,
cagr_entitlement_line_id,
value,
grade_spine_id,
parent_spine_id,
step_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 chosen_flag = 'Y';
SELECT erl.VALUE
,erl.RANGE_FROM
,erl.RANGE_TO
,erl.GRADE_SPINE_ID
,erl.PARENT_SPINE_ID
,erl.STEP_ID
,erl.FROM_STEP_ID
,erl.TO_STEP_ID
,erl.BENEFICIAL_FLAG
FROM per_cagr_entitlement_results erl
WHERE erl.ASSIGNMENT_ID = p_assignment_id
AND erl.COLLECTIVE_AGREEMENT_ID = p_cagr_id
AND erl.CAGR_ENTITLEMENT_ITEM_ID = p_entitlement_item_id
AND p_effective_date BETWEEN erl.START_DATE
AND nvl(erl.END_DATE,hr_general.end_of_time);
SELECT gs.grade_id
FROM per_grade_spines_f gs
WHERE gs.grade_spine_id = p_grade_spine_id
AND p_effective_date between gs.effective_start_date and gs.effective_end_date;
INSERT INTO per_cagr_entitlement_results(CAGR_ENTITLEMENT_RESULT_ID
,CAGR_REQUEST_ID
,START_DATE
,END_DATE
,COLLECTIVE_AGREEMENT_ID
,CAGR_ENTITLEMENT_ITEM_ID
,ELEMENT_TYPE_ID
,INPUT_VALUE_ID
,CAGR_API_ID
,CAGR_API_PARAM_ID
,CATEGORY_NAME
,CAGR_ENTITLEMENT_ID
,CAGR_ENTITLEMENT_LINE_ID
,ASSIGNMENT_ID
,VALUE
,UNITS_OF_MEASURE
,RANGE_FROM
,RANGE_TO
,GRADE_SPINE_ID
,PARENT_SPINE_ID
,STEP_ID
,FROM_STEP_ID
,TO_STEP_ID
,BENEFICIAL_FLAG
,OIPL_ID
,ELIGY_PRFL_ID
,FORMULA_ID
,CHOSEN_FLAG
,COLUMN_TYPE
,COLUMN_SIZE
,MULTIPLE_ENTRIES_ALLOWED_FLAG
,BUSINESS_GROUP_ID
,FLEX_VALUE_SET_ID
,RETAINED_ENT_RESULT_ID
,OBJECT_VERSION_NUMBER)
VALUES (PER_CAGR_ENTITLEMENT_RESULTS_S.nextval
,p_cagr_request_id
,p_effective_date
,p_end_date
,p_structure(i).COLLECTIVE_AGREEMENT_ID
,p_structure(i).CAGR_ENTITLEMENT_ITEM_ID
,p_structure(i).ELEMENT_TYPE_ID
,p_structure(i).INPUT_VALUE_ID
,p_structure(i).CAGR_API_ID
,p_structure(i).CAGR_API_PARAM_ID
,p_structure(i).CATEGORY_NAME
,p_structure(i).CAGR_ENTITLEMENT_ID
,p_structure(i).CAGR_ENTITLEMENT_LINE_ID
,p_structure(i).ASSIGNMENT_ID
,p_structure(i).VALUE
,p_structure(i).UNITS_OF_MEASURE
,p_structure(i).RANGE_FROM
,p_structure(i).RANGE_TO
,p_structure(i).GRADE_SPINE_ID
,p_structure(i).PARENT_SPINE_ID
,p_structure(i).STEP_ID
,p_structure(i).FROM_STEP_ID
,p_structure(i).TO_STEP_ID
,p_structure(i).BENEFICIAL_FLAG
,p_structure(i).OIPL_ID
,p_structure(i).ELIGY_PRFL_ID
,p_structure(i).FORMULA_ID
,p_structure(i).CHOSEN_FLAG
,p_structure(i).COLUMN_TYPE
,p_structure(i).COLUMN_SIZE
,p_structure(i).MULTIPLE_ENTRIES_ALLOWED_FLAG
,p_structure(i).BUSINESS_GROUP_ID
,p_structure(i).FLEX_VALUE_SET_ID
,p_structure(i).RETAINED_ENT_RESULT_ID
,l_ovn);
PROCEDURE insert_result_set (p_structure IN results_table
,p_params IN control_structure) IS
--
-- Accept table of result records for an item and insert new records into the cache table
-- starting on the effctive date (end_date may be EOT or start_date -1 of any future result)
--
-- (Calls write_results to insert new set as no results exist for the item-asg-date combination.)
--
-- test if any future result(s) exists for the item
-- and return the start_date of earliest future result set
CURSOR csr_future_results IS
SELECT min(er.start_date)
FROM per_cagr_entitlement_results er
WHERE er.cagr_entitlement_item_id = p_params.entitlement_item_id
AND er.assignment_id = p_params.assignment_id
AND p_params.effective_date < er.START_DATE;
l_proc constant VARCHAR2(61) := g_pkg || '.insert_result_set';
per_cagr_utility_pkg.put_log(' Executing insert_result_set');
per_cagr_utility_pkg.put_log(' Completed insert_result_set');
END insert_result_set;
PROCEDURE update_result_set (p_structure IN results_table
,p_params in control_structure
,p_switch IN VARCHAR2) IS
--
-- This routine performs two distinct functions, controlled by params supplied:
-- 1) 'Clean' results cache by end_dating (to eff_date - 1) all item results for the asg that are
-- found on effective_date. This is used prior to starting a new engine run (except SE or BE)
-- or when cagr has been removed (nullified) from the asg.
-- 2) 'Write' calls write_results to write new records when 'updating' existing records for an item.
-- This is used by all modes (but check_cache determines whether called by SE or BE).
--
-- update all results for any item found in cache
-- taking exclusive lock out, with nowait, will hang until rows is freed,
-- but this is NOT used by 'SE' mode
CURSOR csr_all_results (v_assignment_id in number) IS
SELECT er.start_date, er.cagr_request_id
FROM per_cagr_entitlement_results er
WHERE er.assignment_id = v_assignment_id
AND p_params.effective_date BETWEEN er.START_DATE AND nvl(er.END_DATE,hr_general.END_OF_TIME)
ORDER BY er.cagr_request_id
FOR UPDATE OF END_DATE NOWAIT;
SELECT er.start_date, er.cagr_request_id
FROM per_cagr_entitlement_results er
WHERE er.assignment_id = p_params.assignment_id
AND er.cagr_entitlement_item_id = p_params.entitlement_item_id
AND p_params.effective_date BETWEEN er.START_DATE AND nvl(er.END_DATE,hr_general.END_OF_TIME)
FOR UPDATE OF END_DATE NOWAIT;
SELECT min(er.start_date)
FROM per_cagr_entitlement_results er
WHERE er.cagr_entitlement_item_id = v_entitlement_item_id
AND er.assignment_id = v_assignment_id
AND p_params.effective_date < er.START_DATE;
l_proc constant VARCHAR2(61) := g_pkg || '.update_result_set';
v_delete_cagr_request_id NUMBER(15);
fetch csr_item_results into v_start_date, v_delete_cagr_request_id;
update per_cagr_entitlement_results set end_date = p_params.effective_date -1
where current of csr_item_results;
update per_cagr_entitlement_results set end_date = p_params.effective_date -1
where current of csr_item_results;
delete from per_cagr_entitlement_results
where current of csr_item_results;
delete from per_cagr_entitlement_results
where current of csr_item_results;
per_cagr_utility_pkg.remove_log_entries(v_delete_cagr_request_id);
update per_cagr_entitlement_results set end_date = p_params.effective_date -1
where current of csr_all_results;
delete from per_cagr_entitlement_results
where current of csr_all_results;
if v_delete_cagr_request_id is null then
v_delete_cagr_request_id := v_cagr_request_id;
t_cagr_request(i) := v_delete_cagr_request_id;
elsif v_delete_cagr_request_id <> v_cagr_request_id then
v_delete_cagr_request_id := v_cagr_request_id;
t_cagr_request(i) := v_delete_cagr_request_id;
END update_result_set;
SELECT batch_elig_id,
BENEFIT_ACTION_ID,
PERSON_ID,
PGM_ID,
PL_ID,
OIPL_ID,
ELIG_FLAG
from ben_batch_elig_info bbe
where bbe.BENEFIT_ACTION_ID = p_benefit_action_id
and bbe.OIPL_ID is not null;
SELECT value_column_type
FROM fnd_flex_validation_tables
WHERE flex_value_set_id = vs_id;
l_dyn_csr_table.delete; -- clear pl/sql table
SELECT * from per_cagr_retained_rights pcrr
WHERE pcrr.assignment_id = p_assignment_id
AND cagr_entitlement_item_id = p_cagr_entitlement_item_id
AND p_params.effective_date BETWEEN pcrr.START_DATE AND nvl(pcrr.END_DATE,hr_general.end_of_time)
AND EXISTS (select 'x'
from per_collective_agreements pca
where pca.collective_agreement_id = pcrr.collective_agreement_id
and pca.STATUS = 'A'
and p_params.effective_date >= pca.START_DATE)
AND EXISTS (select 'x'
from per_cagr_entitlements pce
where pce.cagr_entitlement_id = pcrr.cagr_entitlement_id
and pce.STATUS = 'A'
and p_params.effective_date between pce.START_DATE and nvl(pce.END_DATE,hr_general.end_of_time))
AND ((pcrr.cagr_entitlement_line_id is not null
and EXISTS (select 'x'
from per_cagr_entitlement_lines_f pcel
where pcel.cagr_entitlement_line_id = pcrr.cagr_entitlement_line_id
and pcel.STATUS = 'A'
and p_params.effective_date between pcel.effective_start_date
and pcel.effective_end_date))
OR pcrr.cagr_entitlement_line_id is null);
SELECT *
FROM per_cagr_entitlements pce
WHERE pce.CAGR_ENTITLEMENT_ID = v_ent_id;
SELECT *
FROM per_cagr_entitlement_lines_f pcel
WHERE pcel.CAGR_ENTITLEMENT_LINE_ID = v_line_id
AND p_effective_date BETWEEN pcel.EFFECTIVE_START_DATE
AND nvl(pcel.EFFECTIVE_END_DATE,hr_general.end_of_time);
p_structure.delete(v_dup_record);
SELECT *
FROM per_cagr_retained_rights pcrr
WHERE pcrr.assignment_id = p_params.assignment_id
AND p_params.effective_date BETWEEN pcrr.START_DATE
AND nvl(pcrr.END_DATE,hr_general.end_of_time)
AND EXISTS (select 'X' from per_all_assignments_f asg
where asg.assignment_id = p_params.assignment_id
and p_params.effective_date BETWEEN asg.effective_start_date
AND asg.effective_end_date
and asg.PRIMARY_FLAG = 'Y')
AND EXISTS (select 'x'
from per_collective_agreements pca
where pca.collective_agreement_id = pcrr.collective_agreement_id
and pca.STATUS = 'A'
and p_params.effective_date >= pca.START_DATE)
AND EXISTS (select 'x'
from per_cagr_entitlements pce
where pce.cagr_entitlement_id = pcrr.cagr_entitlement_id
and pce.STATUS = 'A'
and p_params.effective_date BETWEEN pce.START_DATE
AND nvl(pce.END_DATE,hr_general.end_of_time))
AND ((pcrr.cagr_entitlement_line_id is not null
and EXISTS (select 'x'
from per_cagr_entitlement_lines_f pcel
where pcel.cagr_entitlement_line_id = pcrr.cagr_entitlement_line_id
and pcel.STATUS = 'A'
and p_params.effective_date between pcel.effective_start_date
and pcel.effective_end_date))
OR pcrr.cagr_entitlement_line_id is null)
AND 'N' = per_cagr_evaluation_pkg.new_entitlement(pcrr.cagr_entitlement_item_id)
ORDER BY pcrr.cagr_entitlement_item_id;
SELECT *
FROM per_cagr_entitlements pce
WHERE pce.CAGR_ENTITLEMENT_ID = v_ent_id
AND p_params.effective_date BETWEEN pce.START_DATE
AND nvl(pce.END_DATE,hr_general.end_of_time);
SELECT *
FROM per_cagr_entitlement_lines_f pcel
WHERE pcel.CAGR_ENTITLEMENT_LINE_ID = v_line_id
AND p_params.effective_date BETWEEN pcel.EFFECTIVE_START_DATE
AND nvl(pcel.EFFECTIVE_END_DATE,hr_general.end_of_time);
SELECT beneficial_rule, beneficial_rule_value_set_id
from per_cagr_entitlement_items pcei
where pcei.cagr_entitlement_item_id = l_cagr_entitlement_item_id;
update_result_set(t_results_table,p_params,'W');
t_results_table.delete;
update_result_set(t_results_table,p_params,'W');
t_results_table.delete;
update_result_set(t_results_table,p_params,'C');
update_result_set(t_results_table,p_params,'W');
t_results_table.delete;
update_result_set(t_results_table,p_params,'W');
t_results_table.delete;
g_entitlement_items.DELETE;
t_chosen_table.DELETE;
l_update_cache := TRUE;
l_update_cache := FALSE;
if l_update_cache then
-- only write new results to cache if p_commit_flag allows
BEGIN
if p_SE_rec.error = 'HR_289577_CAGR_NO_DATA_FOUND' then
-- write new result set to cache, as none was found
insert_result_set(t_results_table, p_params);
update_result_set(t_results_table,p_params,'W');
per_cagr_utility_pkg.put_log(' Cache was not updated with results, continuing...');
g_entitlement_items.DELETE;
t_chosen_table.DELETE;
t_results_table.DELETE;
update_result_set(t_results_table,p_params,'C');
update_result_set(t_results_table,p_params,'W');
t_results_table.delete;
update_result_set(t_results_table,p_params,'W');
t_results_table.delete;
g_entitlement_items.DELETE;
t_chosen_table.DELETE;
insert_result_set(t_results_table, p_params);
t_results_table.DELETE;
update_result_set(t_results_table,p_params,'W');
t_results_table.DELETE;
per_cagr_utility_pkg.put_log(' Cache was not updated with results, continuing...');
t_chosen_table.DELETE;