[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
persons_selected NUMBER (15), -- PER_SLCTD
persons_proc_succ NUMBER (15), -- PER_PROC_SUCC
persons_errored NUMBER (15), -- PER_ERR
business_group_id NUMBER (15),
benefit_action_id NUMBER (15),
start_date DATE,
end_date DATE,
start_time VARCHAR (90),
end_time VARCHAR (90)
);
SELECT
COUNT(UNIQUE(person_id)) cnt_ppl
FROM ben_tcs_report_details
WHERE benefit_action_id = v_benefit_action_id
AND stmt_created ='Y' ;
SELECT
COUNT(UNIQUE(person_id)) cnt_ppl
FROM ben_tcs_report_details
WHERE benefit_action_id = v_benefit_action_id
AND stmt_created ='E' ;
SELECT per_perd.stmt_id
FROM ben_tcs_per_stmt_perd per_perd, ben_tcs_stmt_perd perd
WHERE per_perd.stmt_id <> p_stmt_id
AND per_perd.person_id = p_person_id
AND per_perd.end_date = p_period_end_date
AND perd.stmt_perd_id = per_perd.stmt_perd_id
AND perd.start_date = p_period_start_date ;
SELECT bg.name bg_name
INTO p_bg_name
FROM per_business_groups_perf bg
WHERE
bg.business_group_id = p_bg_id
AND v_period_end_date >= bg.date_from
AND ( bg.date_to IS NULL OR bg.date_to >= v_period_end_date);
SELECT eligy.name
INTO p_ee_name
FROM BEN_ELIGY_PRFL_F eligy
WHERE
eligy.ELIGY_PRFL_ID(+) = v_ee_id
AND v_period_end_date >= eligy.effective_start_date(+)
AND (eligy.effective_end_date IS NULL OR eligy.effective_end_date >= v_period_end_date) ;
SELECT jobs.name
INTO p_job
FROM per_jobs_tl jobs , per_all_assignments_f assign,
( SELECT assignment.assignment_id , max(assignment.effective_end_date)end_date
FROM per_all_assignments_f assignment
WHERE assignment.assignment_id = p_assignment_id GROUP BY assignment_id) b
WHERE
assign.effective_end_date = b.end_date
AND assign.assignment_id = b.assignment_id
AND assign.job_id=jobs.job_id(+)
AND jobs.language (+) = userenv('lang')
ORDER BY jobs.name;
SELECT employee_number ,full_name ,bg.name
INTO p_emp_num , p_emp_name , p_bg
FROM per_all_people_f ppl ,per_business_groups_perf bg
WHERE
ppl.person_id = p_person_id
AND trunc(sysdate) between ppl.effective_start_date and ppl.effective_end_date
AND bg.business_group_id = ppl.business_group_id
AND p_period_end_date >= bg.date_from
AND ( bg.date_to IS NULL OR bg.date_to >= p_period_end_date);
INSERT INTO BEN_TCS_REPORT_DETAILS(
BENEFIT_ACTION_ID,
BUSINESS_GROUP_ID,
BUSINESS_GROUP_NAME,
ELIGY_ID,
ELIGY_PROF_NAME,
STMT_ID,
STMT_NAME,
SETUP_VALID,
TOTAL_PERSONS,
BEN_TCS_RPT_DET_ID)
VALUES
(
l_rep_rec(i).BENEFIT_ACTION_ID,
l_rep_rec(i).BUSINESS_GROUP_ID,
l_rep_rec(i).BUSINESS_GROUP_NAME,
l_rep_rec(i).ELIGY_ID,
l_rep_rec(i).ELIGY_PROF_NAME,
l_rep_rec(i).STMT_ID,
l_rep_rec(i).STMT_NAME,
l_rep_rec(i).SETUP_VALID,
l_rep_rec(i).TOTAL_PERSONS,
BEN_TCS_REPORT_DETAILS_S.NEXTVAL
);
INSERT INTO BEN_TCS_REPORT_DETAILS(
BENEFIT_ACTION_ID,
BUSINESS_GROUP_ID ,
BUSINESS_GROUP_NAME,
ASSIGNMENT_NUMBER,
STMT_CREATED,
ASSIGNMENT_ID ,
PERSON_ID,
STMT_ID,
STMT_NAME,
FULL_NAME,
EMPLOYEE_NUMBER,
JOB_NAME,
BEN_TCS_RPT_DET_ID,
ERROR
)
VALUES
(
l_rep_rec(i).BENEFIT_ACTION_ID,
l_rep_rec(i).BUSINESS_GROUP_ID,
l_rep_rec(i).BUSINESS_GROUP_NAME,
l_rep_rec(i).ASSIGNMENT_NUMBER,
l_rep_rec(i).STMT_CREATED,
l_rep_rec(i).ASSIGNMENT_ID,
l_rep_rec(i).PERSON_ID,
l_rep_rec(i).STMT_ID,
l_rep_rec(i).STMT_NAME,
l_rep_rec(i).FULL_NAME,
l_rep_rec(i).EMPLOYEE_NUMBER,
l_rep_rec(i).JOB_NAME,
BEN_TCS_REPORT_DETAILS_S.NEXTVAL,
l_rep_rec(i).ERROR
);
SELECT DISTINCT item_id
FROM ben_tcs_per_item
WHERE item_id = v_item_id
AND stmt_perd_id = v_period_id ;
SELECT stmt_id ,stmt_perd_id
FROM ben_tcs_per_stmt_perd
WHERE stmt_id = v_stmt_id
AND stmt_perd_id = v_perd_id;
SELECT stmt_perd_id
FROM ben_tcs_per_stmt_perd
WHERE stmt_perd_id = v_perd_id ;
SELECT stmt_id
FROM ben_tcs_per_stmt_perd
WHERE stmt_id = v_stmt_id ;
SELECT row_in_cat_id , all_objects_in_cat_id
FROM ben_tcs_cat_item_hrchy
WHERE stmt_perd_id = v_period_id
AND lvl_num = 1;
SELECT row_in_cat_id row_cat_id
FROM ben_tcs_cat_subcat_hrchy
WHERE stmt_perd_id = v_period_id
AND lvl_num = 1;
SELECT distinct nvl(item.stmt_perd_id,cat.stmt_perd_id )row_cat_id
FROM ben_tcs_cat_item_hrchy item ,ben_tcs_cat_subcat_hrchy cat
WHERE (item.row_in_cat_id =row_id
AND item.lvl_num =1
AND item.stmt_perd_id <> v_period_id )
OR( cat.row_in_cat_id =row_id
AND cat.lvl_num =1 AND cat.stmt_perd_id <> v_period_id);
SELECT all_objects_in_cat_id
FROM ben_tcs_cat_item_hrchy item
WHERE item.all_objects_in_cat_id =obj_id
AND item.lvl_num =1
AND item.stmt_perd_id <> v_period_id;
processed_cat.DELETE;
UPDATE BEN_TCS_STMT
SET stmt_generated_flag = 'Y'
WHERE stmt_id = stmt_perd_rec.stmt_id ;
UPDATE BEN_TCS_STMT_PERD
SET stmt_generated_flag = 'STMTGEN'
WHERE stmt_perd_id = stmt_perd_rec.stmt_perd_id ;
DELETE
FROM ben_tcs_cat_item_hrchy
WHERE item_id = item_hrchy_values(i).item_id
AND stmt_perd_id = item_hrchy_values(i).perd_id ;
hr_utility.set_location('before inserting into cat_item_hrchy: ',55);
INSERT INTO ben_tcs_cat_item_hrchy
(stmt_id, cat_id,
item_id,
lvl_num ,cntr_cd, row_in_cat_id , all_objects_in_cat_id, stmt_perd_id
)
VALUES (item_hrchy_values(i).stmt_id,
item_hrchy_values(i).cat_id,
item_hrchy_values(i).item_id,
item_hrchy_values(i).lvl_num,
item_hrchy_values(i).cntr_cd ,
item_hrchy_values(i).row_cat_id,
item_hrchy_values(i).all_objects_id,
item_hrchy_values(i).perd_id
);
UPDATE BEN_TCS_ALL_OBJECTS_IN_CAT
SET stmt_generated_flag = 'Y'
WHERE all_objects_in_cat_id = item_hrchy_values(i).all_objects_id;
UPDATE BEN_TCS_ROW_IN_CAT
SET stmt_generated = 'Y'
WHERE row_in_cat_id =item_hrchy_values(i).row_cat_id;
hr_utility.set_location('after inserting into cat_item_hrchy: ',58);
SELECT cat_type_cd into cat_type
FROM BEN_TCS_CAT
WHERE CAT_ID = subcat_hrchy_values(i).subcat_id;
DELETE
FROM ben_tcs_cat_subcat_hrchy
WHERE sub_cat_id =subcat_hrchy_values(i).subcat_id
AND stmt_perd_id = subcat_hrchy_values(i).perd_id ;
hr_utility.set_location('before inserting into cat_subcat_hrchy: ',60);
INSERT INTO ben_tcs_cat_subcat_hrchy
(stmt_id, cat_id,
sub_cat_id,
lvl_num,row_in_cat_id ,stmt_perd_id
)
VALUES (subcat_hrchy_values(i).stmt_id,
subcat_hrchy_values(i).cat_id,
subcat_hrchy_values(i).subcat_id,
subcat_hrchy_values(i).lvl_num,
subcat_hrchy_values(i).row_cat_id,
subcat_hrchy_values(i).perd_id );
UPDATE BEN_TCS_ROW_IN_CAT
SET stmt_generated = 'Y'
WHERE row_in_cat_id = subcat_hrchy_values(i).row_cat_id;
row_in_cat.DELETE ;
all_obj_cat.DELETE ;
UPDATE BEN_TCS_STMT_PERD
SET stmt_generated_flag = 'STMTNGEN'
WHERE stmt_perd_id = l_rep_rec(i).PERIOD_ID ;
DELETE
FROM ben_tcs_cat_item_hrchy
WHERE stmt_perd_id = l_rep_rec(i).PERIOD_ID;
DELETE
FROM ben_tcs_cat_subcat_hrchy
WHERE stmt_perd_id = l_rep_rec(i).PERIOD_ID;
UPDATE ben_tcs_row_in_cat
SET stmt_generated = null
WHERE row_in_cat_id = row_in_cat(j);
UPDATE ben_tcs_all_objects_in_cat
SET stmt_generated_flag = null
WHERE all_objects_in_cat_id = all_obj_cat(j);
UPDATE BEN_TCS_STMT
SET stmt_generated_flag = 'N'
WHERE stmt_id = l_rep_rec(i).STMT_ID;
PROCEDURE delete_hrchy
IS
BEGIN
DELETE FROM ben_tcs_cat_item_hrchy
WHERE cat_id = -999 and lvl_num = -1;
END delete_hrchy;
p_person_selected IN NUMBER,
p_business_group_id IN NUMBER DEFAULT NULL
)
IS
l_batch_proc_id NUMBER;
, p_per_slctd => p_person_selected
, p_per_proc => g_exec_param_rec.Number_Of_BGs
, p_per_unproc => g_exec_param_rec.stmt_errors
, p_per_proc_succ => g_exec_param_rec.persons_proc_succ
, p_per_err => g_exec_param_rec.persons_errored
, p_business_group_id => nvl(p_business_group_id,HR_GENERAL.GET_BUSINESS_GROUP_ID)
, p_object_version_number => l_object_version_number);
delete_hrchy;
SELECT ASG_STMT_ID ,assignment_number, assignment_id
FROM ben_tcs_asg_stmt
WHERE per_stmt_perd_id = v_per_stmt_perd_id;
SELECT per_item_id
FROM ben_tcs_per_item
WHERE ASG_STMT_ID = v_asg_stmt_id;
SELECT per_stmt_perd_id
INTO l_per_stmt_perd
FROM ben_tcs_per_stmt_perd
WHERE
stmt_id = p_stmt_id
AND stmt_perd_id = p_stmt_perd_id
AND person_id = p_person_id ;
hr_utility.set_location ( 'delete per_stmt_perd_id entry for per_stmt_perd_id :' || l_per_stmt_perd,72);
DELETE
FROM ben_tcs_per_stmt_perd
WHERE per_stmt_perd_id = l_per_stmt_perd;
hr_utility.set_location ( 'delete ben_tcs_per_item_value and ben_tcs_per_item entry for per_item_id :' || l_per_item,73);
DELETE FROM ben_tcs_per_item_value
WHERE per_item_id = l_per_item;
DELETE FROM ben_tcs_per_item
WHERE per_item_id = l_per_item;
hr_utility.set_location ( 'delete ben_tcs_asg_stmt entry for ASG_STMT_ID :' || asg_rec.asg_stmt_id,74);
DELETE FROM
ben_tcs_asg_stmt
WHERE ASG_STMT_ID = asg_rec.asg_stmt_id;
CURSOR c_assignment_selection ( v_person_id IN NUMBER,
v_period_start_date IN DATE,
v_period_end_date IN DATE)
IS
SELECT DISTINCT(assignment_id) , assignment_number FROM
per_all_assignments_f assign ,
PER_ASSIGNMENT_STATUS_TYPES status
WHERE assignment_type IN ('B','C','E')
AND assign.person_id = v_person_id
AND nvl(status.business_group_id,assign.business_group_id) = assign.business_group_id
AND status.active_flag = 'Y'
AND assign.ASSIGNMENT_STATUS_TYPE_ID = status.ASSIGNMENT_STATUS_TYPE_ID
AND status.per_system_status IN ('ACTIVE_ASSIGN' , 'ACTIVE_CWK')
AND assign.effective_end_date > v_period_start_date
AND assign.effective_start_date < v_period_end_date;
SELECT sum(current_shares_outstanding) total
FROM
ben_cwb_stock_optn_dtls
WHERE ( person_id = v_person_id
OR (business_group_id = v_business_group
AND employee_number = p_emp_num) )
AND ( grant_date BETWEEN ( SELECT START_DATE FROM ben_tcs_stmt_perd
WHERE stmt_perd_id = v_stmt_perd_id )
AND v_period_end_date );
l_assign c_assignment_selection%ROWTYPE;
SELECT item_id,cntr_cd
FROM ben_tcs_cat_item_hrchy
WHERE cat_id = -999
AND stmt_id = v_stmt_id;
OPEN c_assignment_selection(p_person_id,p_start_date,p_period_end_date);
FETCH c_assignment_selection
INTO l_assign;
EXIT WHEN c_assignment_selection%NOTFOUND;
SELECT source_cd , source_key ,uom ,nnmntry_uom,comp_type_cd,name
INTO l_source_cd , l_source_key ,l_uom ,l_nnmntry_uom,l_comp_type_cd ,item_name
FROM ben_tcs_item
WHERE item_id = item_id_process.item_id;
SAVEPOINT insertion;
hr_utility.set_location('inserting INTO ben_tcs_per_stmt_perd person id , period id , stmt id '||
p_person_id|| p_stmt_perd_id||p_stmt_id,87);
INSERT INTO ben_tcs_per_stmt_perd
( per_stmt_perd_id ,
stmt_id ,
stmt_perd_id,
person_id,
show_wlcm_pg_flag,
end_date )
VALUES
( ben_tcs_per_stmt_perd_s.NEXTVAL ,
p_stmt_id,
p_stmt_perd_id,
p_person_id,
null,
p_period_end_date);
hr_utility.set_location('After inserting INTO ben_tcs_per_stmt_perd ',88);
hr_utility.set_location('Inserting INTO ben_tcs_asg_stmt assignment_id '||l_assign.assignment_id,89);
INSERT INTO ben_tcs_asg_stmt(
asg_stmt_id ,
stmt_id ,
assignment_id,
per_stmt_perd_id,
assignment_number )
VALUES
( ben_tcs_asg_stmt_s.NEXTVAL ,
p_stmt_id,
l_assign.assignment_id,
ben_tcs_per_stmt_perd_s.CURRVAL,
l_assign.assignment_number);
hr_utility.set_location('After inserting INTO ben_tcs_asg_stmt ',90);
hr_utility.set_location('Inserting INTO ben_tcs_per_item item_id '||item_id_process.item_id,91);
INSERT INTO ben_tcs_per_item
( per_item_id,
asg_stmt_id ,
stmt_id ,
item_id,
person_id,
stmt_perd_id ,
assignment_id,
uom ,
nnmntry_uom )
VALUES
( ben_tcs_per_item_s.NEXTVAL ,
ben_tcs_asg_stmt_s.CURRVAL ,
p_stmt_id,
item_id_process.item_id,
p_person_id,
p_stmt_perd_id,
l_assign.assignment_id ,
l_uom ,
l_nnmntry_uom );
hr_utility.set_location(' After inserting INTO ben_tcs_per_item ',92);
hr_utility.set_location(' Inserting INTO ben_tcs_per_item_value ' ,92.1);
INSERT INTO ben_tcs_per_item_value
( per_item_value_id,
per_item_id ,
source_from_date ,
source_to_date,
seq_num,
date_value,
source_key ,
source_cd,
display_date)
VALUES
( ben_tcs_per_item_value_s.NEXTVAL ,
ben_tcs_per_item_s.CURRVAL ,
l_result(i).start_date,
l_result(i).end_date ,
i,
to_date(l_result(i).value,'yyyy/mm/dd'),
l_result(i).output_key,
l_source_cd ,l_result(i).start_date);
INSERT INTO ben_tcs_per_item_value
( per_item_value_id,
per_item_id ,
source_from_date ,
source_to_date,
seq_num,
text_value,
source_key ,
source_cd,
display_date)
VALUES
( ben_tcs_per_item_value_s.NEXTVAL ,
ben_tcs_per_item_s.CURRVAL ,
l_result(i).start_date,
l_result(i).end_date ,
i,
l_result(i).value,
l_result(i).output_key,
l_source_cd ,
l_result(i).start_date);
INSERT INTO ben_tcs_per_item_value
( per_item_value_id,
per_item_id ,
source_from_date ,
source_to_date,
seq_num,
num_value,
source_key ,
source_cd,
display_date)
VALUES
( ben_tcs_per_item_value_s.NEXTVAL ,
ben_tcs_per_item_s.CURRVAL ,
l_result(i).start_date,
l_result(i).end_date ,
i,
fnd_number.canonical_to_number(l_result(i).value),
l_result(i).output_key,
l_source_cd ,
l_result(i).start_date);
hr_utility.set_location(' After inserting INTO ben_tcs_per_item_value ',93 );
hr_utility.set_location('Inserting INTO ben_tcs_per_item_value ',94.1);
INSERT INTO ben_tcs_per_item_value
( per_item_value_id,
per_item_id ,
source_from_date ,
source_to_date,
seq_num,
date_value,
source_key ,
source_cd,
display_date)
VALUES
( ben_tcs_per_item_value_s.NEXTVAL ,
ben_tcs_per_item_s.CURRVAL ,
l_result(i).start_date,
l_result(i).end_date ,
i,
fnd_date.canonical_to_date(l_result(i).value),
l_result(i).output_key,
l_source_cd ,
l_result(i).end_date);
INSERT INTO ben_tcs_per_item_value
( per_item_value_id,
per_item_id ,
source_from_date ,
source_to_date,
seq_num,
text_value,
source_key ,
source_cd,
display_date)
VALUES
( ben_tcs_per_item_value_s.NEXTVAL ,
ben_tcs_per_item_s.CURRVAL ,
l_result(i).start_date,
l_result(i).end_date ,
i,
l_result(i).value,
l_result(i).output_key,
l_source_cd ,
l_result(i).end_date);
INSERT INTO ben_tcs_per_item_value
( per_item_value_id,
per_item_id ,
source_from_date ,
source_to_date,
seq_num,num_value,
source_key ,
source_cd,
display_date)
VALUES
( ben_tcs_per_item_value_s.NEXTVAL ,
ben_tcs_per_item_s.CURRVAL ,
l_result(i).start_date,
l_result(i).end_date ,
i,
fnd_number.canonical_to_number(l_result(i).value),
l_result(i).output_key,
l_source_cd ,
l_result(i).end_date);
hr_utility.set_location('inserting INTO ben_tcs_per_stmt_perd person id , period id , stmt id '||
p_person_id|| p_stmt_perd_id||p_stmt_id ||' subcategory part' ,96);
INSERT INTO ben_tcs_per_stmt_perd
( per_stmt_perd_id ,
stmt_id ,
stmt_perd_id,
person_id,
show_wlcm_pg_flag,
end_date )
VALUES
( ben_tcs_per_stmt_perd_s.NEXTVAL ,
p_stmt_id,
p_stmt_perd_id,
p_person_id,
null,
p_period_end_date);
hr_utility.set_location('After inserting INTO ben_tcs_per_stmt_perd ',97);
hr_utility.set_location('inserting INTO ben_tcs_asg_stmt assignment_id '||l_assign.assignment_id||' subcategory',98);
INSERT INTO ben_tcs_asg_stmt(
asg_stmt_id ,
stmt_id ,
assignment_id,
per_stmt_perd_id,
assignment_number )
VALUES
( ben_tcs_asg_stmt_s.NEXTVAL ,
p_stmt_id,
l_assign.assignment_id,
ben_tcs_per_stmt_perd_s.CURRVAL,
l_assign.assignment_number);
hr_utility.set_location('After inserting INTO ben_tcs_asg_stmt ',99);
CLOSE c_assignment_selection;
SELECT show_wlcm_pg_flag
INTO wlcm_flag
FROM ben_tcs_per_stmt_perd
WHERE stmt_id = p_stmt_id
AND stmt_perd_id = p_stmt_perd_id
AND person_id = p_person_id ;
UPDATE ben_tcs_per_stmt_perd
SET show_wlcm_pg_flag = null
WHERE stmt_id = p_stmt_id
AND stmt_perd_id = p_stmt_perd_id
AND person_id = p_person_id ;
SELECT ran.range_id, ran.starting_person_action_id,
ran.ending_person_action_id
FROM ben_batch_ranges ran
WHERE ran.range_status_cd = 'U'
AND ran.benefit_action_id = v_benefit_action_id
AND ROWNUM < 2
FOR UPDATE OF ran.range_status_cd;
SELECT ben.person_id, ben.person_action_id, ben.object_version_number,
ben.chunk_number ,ben.ler_id , ben.non_person_cd
FROM ben_person_actions ben
WHERE ben.benefit_action_id = v_benefit_action_id
AND ben.action_status_cd <> 'P'
AND ben.person_action_id BETWEEN v_start_person_action_id
AND v_end_person_action_id
ORDER BY ben.person_action_id;
SELECT ben.*
FROM ben_benefit_actions ben
WHERE ben.benefit_action_id = v_benefit_action_id;
|| ' is selected'
);
UPDATE ben_batch_ranges ran
SET ran.range_status_cd = 'P'
WHERE ran.range_id = l_range_id;
WRITE ( 'Number of Persons selected in this range '
|| g_cache_person_process.COUNT
);
PROCEDURE insert_person_actions (
p_per_actn_id_array IN g_number_type,
p_per_id IN g_number_type,
p_benefit_action_id IN NUMBER,
p_perd_id IN g_number_type,
p_stmt_id IN g_number_type,
p_bg_id IN g_number_type
)
IS
l_num_rows NUMBER := p_per_actn_id_array.COUNT;
g_proc := 'insert_person_actions';
WRITE ( 'Time before inserting person actions '
|| TO_CHAR (SYSDATE, 'yyyy/mm/dd:hh:mi:ssam'));
INSERT INTO ben_person_actions
(person_action_id,
person_id,
benefit_action_id,
action_status_cd,
chunk_number,
LER_ID,
non_person_cd ,
object_version_number)
VALUES (
p_per_actn_id_array (l_count),
p_per_id (l_count),
p_benefit_action_id,
'U',
p_stmt_id (l_count),
p_perd_id (l_count),
p_bg_id (l_count),
1);
WRITE ( 'Time before inserting ben batch ranges '
|| TO_CHAR (SYSDATE, 'yyyy/mm/dd:hh:mi:ssam'));
INSERT INTO ben_batch_ranges
(range_id,
benefit_action_id,
range_status_cd,
starting_person_action_id,
ending_person_action_id,
object_version_number)
VALUES (
ben_batch_ranges_s.NEXTVAL,
p_benefit_action_id,
'U',
p_per_actn_id_array (1),
p_per_actn_id_array (l_num_rows),
1);
WRITE ( 'Time at end of insert person actions '
|| TO_CHAR (SYSDATE, 'yyyy/mm/dd:hh:mi:ssam'));
WRITE ('Leaving insert_person_actions');
END insert_person_actions;
CURSOR c_person_selection (
v_stmt_id IN NUMBER,
v_period_start_date IN DATE,
v_period_end_date IN DATE,
v_bg_id IN NUMBER,
--vkodedal added args 14-sep-2007 ER
v_ben_grp_id IN NUMBER,
v_position_id IN NUMBER,
v_job_id IN NUMBER,
v_payroll_id IN NUMBER,
v_location_id IN NUMBER,
v_supervisor_id IN NUMBER,
v_org_id IN NUMBER
)
IS
SELECT DISTINCT per.person_id,
per_nm.full_name NAME
--vkodedal added these additional attributes
--per.benefit_group_id,
--asgnmt.position_id,
--asgnmt.job_id,
--asgnmt.payroll_id,
--asgnmt.location_id,
--asgnmt.supervisor_id,
--asgnmt.organization_id
FROM per_people_f per,
per_person_types ppt,
per_person_type_usages_f usg,
per_all_people_f per_nm,
per_assignments_f asgnmt
WHERE per.business_group_id = v_bg_id
AND ppt.business_group_id = per.business_group_id
AND usg.person_type_id = ppt.person_type_id
AND usg.person_id = per.person_id
AND usg.effective_start_date <= per.effective_start_date
AND usg.effective_end_date >= per.effective_end_date
AND ( ppt.system_person_type IN ('EMP', 'CWK')
OR ( ppt.system_person_type IN ('EX_EMP')
AND EXISTS (
SELECT 1
FROM per_periods_of_service perd,
per_assignments_f assign
WHERE perd.person_id = per.person_id
AND perd.final_process_date >
v_period_start_date
AND assign.person_id = per.person_id
AND assign.effective_end_date >
v_period_start_date
AND assign.effective_start_date <
v_period_end_date
AND assign.primary_flag = 'Y'
AND perd.period_of_service_id =
assign.period_of_service_id)
)
)
AND asgnmt.person_id = per.person_id
AND asgnmt.primary_flag = 'Y'
AND asgnmt.assignment_type IN ('E', 'C')
AND asgnmt.effective_end_date > v_period_start_date
AND asgnmt.effective_start_date < v_period_end_date
AND per.effective_start_date < v_period_end_date
AND per.effective_end_date > v_period_start_date
AND per.person_id = per_nm.person_id
AND TRUNC (SYSDATE) BETWEEN per_nm.effective_start_date
AND per_nm.effective_end_date
AND (v_ben_grp_id IS NULL OR per.benefit_group_id=v_ben_grp_id)
AND (v_position_id IS NULL OR asgnmt.position_id=v_position_id)
AND (v_job_id IS NULL OR asgnmt.job_id=v_job_id)
AND (v_payroll_id IS NULL OR asgnmt.payroll_id=v_payroll_id)
AND (v_location_id IS NULL OR asgnmt.location_id=v_location_id)
AND (v_supervisor_id IS NULL OR asgnmt.supervisor_id=v_supervisor_id)
AND (v_org_id IS NULL OR asgnmt.organization_id=v_org_id);
SELECT stmt.stmt_id stmt_id, v_period_id period_id ,stmt.ee_profile_id ee_id,'Y' valid_flag,
stmt.business_group_id bg_id
FROM ben_tcs_stmt stmt
WHERE stmt.stmt_id = v_stmt_id ;
SELECT stmt.stmt_id stmt_id, period.stmt_perd_id period_id ,stmt.ee_profile_id ee_id,'Y' valid_flag,
stmt.business_group_id bg_id
FROM ben_tcs_stmt stmt , ben_tcs_stmt_perd period
WHERE stmt.NAME = v_stmt_name
AND period.start_date = v_period_start_date
AND period.end_date = v_period_end_date
AND period.stmt_id = stmt.stmt_id
AND stmt.stat_cd = 'CO';
SELECT stmt.stmt_id stmt_id, period.stmt_perd_id period_id ,stmt.business_group_id bg_id
FROM ben_tcs_stmt stmt , ben_tcs_stmt_perd period
WHERE stmt.NAME = v_stmt_name
AND period.start_date = v_period_start_date
AND period.end_date = v_period_end_date
AND period.stmt_id = stmt.stmt_id;
SELECT DISTINCT ppl.person_id ,per.full_name name FROM per_all_people_f ppl ,per_all_people_f per ,
per_person_types ppt , per_person_type_usages_f usg
WHERE ppl.business_group_id = ppt.business_group_id
AND usg.person_type_id = ppt.person_type_id
and usg.person_id = ppl.person_id
and usg.effective_start_date <= ppl.effective_start_date
and usg.effective_end_date >= ppl.effective_end_date
AND (ppt.SYSTEM_PERSON_TYPE in ('EMP' ,'CWK') OR
( ppt.SYSTEM_PERSON_TYPE in ('EX_EMP' ) and
exists (
select 1 from per_periods_of_service perd ,per_all_assignments_f assign where perd.person_id =
v_person_id
AND perd.final_process_date > v_period_st_dt
AND assign.person_id = ppl.person_id
AND assign.effective_end_date > v_period_st_dt
AND assign.effective_start_date < v_period_end_dt
AND assign.primary_flag ='Y'
AND perd.period_of_service_id = assign.period_of_service_id) ))
AND ppl.person_id = v_person_id
AND ppl.effective_end_date > v_period_st_dt
AND ppl.effective_start_date < v_period_end_dt
AND per.person_id = ppl.person_id
AND trunc(sysdate) between per.effective_start_date
and per.effective_end_date ;
SELECT business_group_id
FROM ben_tcs_stmt stmt
WHERE stmt.stmt_id = v_stmt_id;
SELECT stmt.person_id ,per.full_name name
FROM BEN_TCS_PER_STMT_PERD stmt,per_all_people_f per
WHERE stmt.stmt_id = v_stmt_id
AND stmt.stmt_perd_id = v_perd_id
AND stmt.person_id = v_person_id
and per.person_id = stmt.person_id
AND trunc(sysdate) between per.effective_start_date and per.effective_end_date;
SELECT stmt.person_id, per.full_name name
FROM BEN_TCS_PER_STMT_PERD stmt,per_all_people_f per
WHERE stmt.stmt_id = v_stmt_id
AND stmt.stmt_perd_id = v_perd_id
AND per.person_id = stmt.person_id
AND trunc(sysdate) between per.effective_start_date and per.effective_end_date;
per_rec c_person_selection%ROWTYPE;
SELECT start_date , end_date
INTO l_period_start_date,
l_actual_end_date
FROM ben_tcs_stmt_perd
WHERE stmt_perd_id = p_period_id ;
g_exec_param_rec.persons_selected := 0;
p_program_update_date => SYSDATE,
p_bft_attribute1 => p_run_type,
p_uneai_effective_date => to_date(p_partial_end,'DD/MM/YYYY'),
p_bft_attribute3 => p_stmt_name,
p_bft_attribute4 => p_period_id,
p_bft_attribute7 => All_Bg,
p_per_sel_dt_from => l_period_start_date,
p_per_sel_dt_to => l_actual_end_date
);
g_actn := 'Inserting Person Actions...';
WRITE ( 'Time before processing the person selections '
|| TO_CHAR (SYSDATE, 'yyyy/mm/dd:hh:mi:ssam')
);
SELECT name
INTO l_stmt_name
FROM BEN_TCS_STMT
WHERE stmt_id = p_stmt_id
AND name = p_stmt_name;
SELECT ELIGY_PRFL_ID INTO stmt_record_rec (l_loop_cnt).statement_rec.ee_id
FROM BEN_ELIGY_PRFL_F
WHERE STAT_CD='A'
AND ELIGY_PRFL_ID = stmt_record_rec (l_loop_cnt).statement_rec.ee_id
AND l_period_end_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
SELECT ELIGY_PRFL_ID INTO temp_ee_id
FROM BEN_ELIGY_PRFL_F
WHERE STAT_CD='A'
AND ELIGY_PRFL_ID = stmt_rec.ee_id
AND l_period_end_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
SELECT ben_person_actions_s.NEXTVAL
INTO l_person_action_ids (l_num_rows)
FROM DUAL;
hr_utility.set_location('inserting INTO person actions :..',170);
g_actn := 'inserting INTO person actions : person id ' || p_person_id ;
insert_person_actions
(p_per_actn_id_array => l_person_action_ids,
p_per_id => l_person_ids,
p_benefit_action_id => l_benefit_action_id,
p_stmt_id =>l_stmt_ids,
p_perd_id =>l_perd_ids,
p_bg_id =>l_bg_ids
);
l_person_action_ids.DELETE;
l_person_ids.DELETE;
l_stmt_ids.DELETE;
l_bg_ids.DELETE;
l_perd_ids.DELETE;
SELECT ELIGY_PRFL_ID INTO temp_ee_id
FROM BEN_ELIGY_PRFL_F
WHERE STAT_CD='A'
AND ELIGY_PRFL_ID = stmt_rec.ee_id
AND l_period_end_date BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE;
OPEN c_person_selection (stmt_rec.stmt_id,
l_period_start_date,
l_period_end_date ,
l_business_group_id,
p_ben_grp_id,
p_position_id,
p_job_id,
p_payroll_id,
p_location_id,
p_supervisor_id,
p_org_id
);
FETCH c_person_selection
INTO per_rec;
EXIT WHEN c_person_selection%NOTFOUND;
SELECT ben_person_actions_s.NEXTVAL
INTO l_person_action_ids (l_num_rows)
FROM DUAL;
g_actn := 'inserting INTO person actions ';
insert_person_actions
(p_per_actn_id_array => l_person_action_ids,
p_per_id => l_person_ids,
p_benefit_action_id => l_benefit_action_id,
p_stmt_id =>l_stmt_ids,
p_perd_id =>l_perd_ids,
p_bg_id =>l_bg_ids
);
l_person_action_ids.DELETE;
l_person_ids.DELETE;
l_stmt_ids.DELETE;
l_perd_ids.DELETE;
l_bg_ids.DELETE;
CLOSE c_person_selection;
OPEN c_person_selection (stmt_record_rec (l_loop_cnt).statement_rec.stmt_id,
l_period_start_date,
l_period_end_date ,
stmt_record_rec (l_loop_cnt).statement_rec.bg_id,
p_ben_grp_id,
p_position_id,
p_job_id,
p_payroll_id,
p_location_id,
p_supervisor_id,
p_org_id);
FETCH c_person_selection
INTO per_rec;
EXIT WHEN c_person_selection%NOTFOUND;
SELECT ben_person_actions_s.NEXTVAL
INTO l_person_action_ids (l_num_rows)
FROM DUAL;
g_actn := 'inserting INTO person actions ';
insert_person_actions
(p_per_actn_id_array => l_person_action_ids,
p_per_id => l_person_ids,
p_benefit_action_id => l_benefit_action_id,
p_stmt_id =>l_stmt_ids,
p_perd_id =>l_perd_ids,
p_bg_id =>l_bg_ids
);
l_person_action_ids.DELETE;
l_person_ids.DELETE;
l_stmt_ids.DELETE;
l_bg_ids.DELETE;
l_perd_ids.DELETE;
CLOSE c_person_selection;
SELECT name
INTO l_stmt_name
FROM BEN_TCS_STMT
WHERE stmt_id = p_stmt_id
AND name = p_stmt_name;
SELECT ben_person_actions_s.NEXTVAL
INTO l_person_action_ids (l_num_rows)
FROM DUAL;
g_actn := 'inserting INTO person actions : person id ' || p_person_id;
insert_person_actions
(p_per_actn_id_array => l_person_action_ids,
p_per_id => l_person_ids,
p_benefit_action_id => l_benefit_action_id,
p_stmt_id =>l_stmt_ids,
p_perd_id =>l_perd_ids,
p_bg_id =>l_bg_ids
);
l_person_action_ids.DELETE;
l_person_ids.DELETE;
l_stmt_ids.DELETE;
l_perd_ids.DELETE;
l_bg_ids.DELETE;
SELECT ben_person_actions_s.NEXTVAL
INTO l_person_action_ids (l_num_rows)
FROM DUAL;
g_actn := 'inserting INTO person actions ';
insert_person_actions
(p_per_actn_id_array => l_person_action_ids,
p_per_id => l_person_ids,
p_benefit_action_id => l_benefit_action_id,
p_stmt_id =>l_stmt_ids,
p_perd_id =>l_perd_ids,
p_bg_id =>l_bg_ids
);
l_person_action_ids.DELETE;
l_person_ids.DELETE;
l_stmt_ids.DELETE;
l_perd_ids.DELETE;
l_bg_ids.DELETE;
SELECT ben_person_actions_s.NEXTVAL
INTO l_person_action_ids (l_num_rows)
FROM DUAL;
g_actn := 'inserting INTO person actions ';
insert_person_actions
(p_per_actn_id_array => l_person_action_ids,
p_per_id => l_person_ids,
p_benefit_action_id => l_benefit_action_id,
p_stmt_id =>l_stmt_ids,
p_perd_id =>l_perd_ids,
p_bg_id =>l_bg_ids
);
l_person_action_ids.DELETE;
l_person_ids.DELETE;
l_stmt_ids.DELETE;
l_perd_ids.DELETE;
l_bg_ids.DELETE;
WRITE ('Total no of person selected - ' || l_num_persons);
g_actn := 'Inserting the last range of persons IF exists...';
WRITE ( 'Time after processing the person selections '
|| TO_CHAR (SYSDATE, 'yyyy/mm/dd:hh:mi:ssam'));
insert_person_actions
(p_per_actn_id_array => l_person_action_ids,
p_per_id => l_person_ids,
p_benefit_action_id => l_benefit_action_id,
p_stmt_id =>l_stmt_ids,
p_perd_id =>l_perd_ids,
p_bg_id =>l_bg_ids
);
l_person_action_ids.DELETE;
l_person_ids.DELETE;
l_stmt_ids.DELETE;
l_perd_ids.DELETE;
l_bg_ids.DELETE;
ben_batch_utils.g_processes_tbl.DELETE;
WRITE ('||p_person_selected- ' || l_num_persons);
g_actn := 'Calling delete hrchy ...';
delete_hrchy;
, p_person_selected => l_num_persons
, p_business_group_id => l_business_group_id
);
delete_hrchy;
, p_person_selected => l_num_persons
, p_business_group_id => l_business_group_id
);
, p_person_selected => l_num_persons
, p_business_group_id => l_business_group_id
);