[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT count(1)
INTO l_count
FROM pqh_rank_processes
WHERE pgm_id = p_pgm_id
AND (p_benefit_action_id IS NULL or benefit_action_id = p_benefit_action_id);
PROCEDURE create_update_rank_approvals (
p_rank_process_id in NUMBER,
p_rank in number,
p_population_count in number) IS
Cursor csr_ranks is
Select rank_process_approval_id,object_version_number
From pqh_rank_process_approvals
Where rank_process_id = p_rank_process_id;
pqh_rank_process_approval_api.update_rank_process_approval (
p_effective_date => sysdate
,p_rank_process_approval_id => rec_ranks.rank_process_approval_id
,p_rank_process_id => p_rank_process_id
,p_approval_date => sysdate
,p_system_rank => p_rank
,p_population_count => p_population_count
,p_proposed_rank => p_rank
,p_object_version_number => rec_ranks.object_version_number );
select pqh_rank_process_approvals_s.nextval
into l_rank_process_approval_id from dual;
END create_update_rank_approvals;
SELECT pgi_information1 wf_enabled,
pgi_information2 rank_enabled,
NVL(pgi_information4,'SUM') duplicate_handling,
pgi_information3 group_score
FROM ben_pgm_extra_info
WHERE pgm_id = p_pgm_id
AND information_type = 'PQH_GSP_EXTRA_INFO';
SELECT distinct pgm_id
FROM pqh_rank_processes
WHERE benefit_action_id = p_benefit_action_id;
select distinct rnk.pgm_id
FROM pqh_rank_processes rnk,
ben_per_in_ler pil
where (p_pl_id is null OR rnk.pl_id = p_pl_id)
and rnk.per_in_ler_id = pil.per_in_ler_id
and pil.business_group_id = p_business_group_id
and pil.per_in_ler_stat_cd = 'STRTD'
and pil.strtd_dt between c_dt_start and c_dt_end ;
SELECT pl_id, count(1) emp_cnt
FROM pqh_rank_processes
WHERE (p_benefit_action_id is null or benefit_action_id = p_benefit_action_id)
AND pgm_id = c_pgm_id
GROUP BY pl_id;
select rnk.pl_id, count(1) emp_cnt
FROM pqh_rank_processes rnk,
ben_per_in_ler pil
where rnk.pgm_id = c_pgm_id
and (c_pl_id is null OR rnk.pl_id = c_pl_id)
and rnk.per_in_ler_id = pil.per_in_ler_id
and pil.business_group_id = p_business_group_id
and pil.per_in_ler_stat_cd = 'STRTD'
and pil.strtd_dt between c_dt_start and c_dt_end
group by pl_id;
SELECT rank_process_id, total_score
FROM pqh_rank_processes
WHERE benefit_action_id = p_benefit_action_id
AND pgm_id = c_pgm_id
ORDER BY total_score DESC;
SELECT rnk.rank_process_id,rnk.total_score
FROM pqh_rank_processes rnk,
ben_per_in_ler pil,
ben_elig_per_elctbl_chc epec
WHERE rnk.pgm_id = c_pgm_id
AND rnk.per_in_ler_id = pil.per_in_ler_id
AND per_in_ler_stat_cd = 'STRTD'
AND pil.business_group_id = p_business_group_id
AND epec.per_in_ler_id = pil.per_in_ler_id
AND epec.business_group_id = pil.business_group_id
AND epec.pgm_id = c_pgm_id
AND epec.dflt_flag = 'Y'
AND approval_status_cd IS NULL
AND ( nvl(p_rank_wf_pending,'N') = 'Y' OR
in_pndg_wkflow_flag <> 'Y')
AND pil.strtd_dt between c_dt_start and c_dt_end
ORDER BY total_score DESC;
SELECT rank_process_id, total_score
FROM pqh_rank_processes
WHERE benefit_action_id = p_benefit_action_id
AND pgm_id = c_pgm_id
AND pl_id = c_pl_id
ORDER BY total_score DESC;
SELECT rnk.rank_process_id,rnk.total_score
FROM pqh_rank_processes rnk,
ben_per_in_ler pil,
ben_elig_per_elctbl_chc epec
WHERE rnk.pgm_id = c_pgm_id
AND rnk.pl_id = c_pl_id
AND rnk.per_in_ler_id = pil.per_in_ler_id
AND per_in_ler_stat_cd = 'STRTD'
AND pil.business_group_id = p_business_group_id
AND epec.per_in_ler_id = pil.per_in_ler_id
AND epec.business_group_id = pil.business_group_id
AND epec.pgm_id = c_pgm_id
AND epec.pl_id = c_pl_id
AND epec.dflt_flag = 'Y'
AND approval_status_cd IS NULL
AND ( nvl(p_rank_wf_pending,'N') = 'Y' OR
in_pndg_wkflow_flag <> 'Y')
and pil.strtd_dt between c_dt_start and c_dt_end
ORDER BY total_score DESC;
create_update_rank_approvals(rec_rnk.rank_process_id
, l_rank
, l_cnt );
create_update_rank_approvals(rec_rnk.rank_process_id
, l_rank
, rec.emp_cnt );
create_update_rank_approvals(rec_rnk.rank_process_id
, l_rank
, l_cnt );
create_update_rank_approvals(rec_rnk.rank_process_id
, l_rank
, rec.emp_cnt );
create_update_rank_approvals(rec_rnk.rank_process_id
, l_rank
, l_cnt );
create_update_rank_approvals(rec_rnk.rank_process_id
, l_rank
, l_cnt );
create_update_rank_approvals(rec_rnk.rank_process_id
, l_rank
, rec.emp_cnt );
create_update_rank_approvals(rec_rnk.rank_process_id
, l_rank
, l_cnt );
select assignment_id
from per_all_assignments_f
where person_id = p_person_id
And primary_flag = 'Y'
and p_effective_date between effective_start_date and effective_end_date;
INSERT INTO pqh_rank_processes (
rank_process_id, process_cd, pgm_id, pl_id,
benefit_action_id, process_date, person_id,
per_in_ler_id, total_score, object_version_number)
Select pqh_rank_processes_s.nextval, 'GSP', p_pgm_id, pl_id,
p_benefit_action_id, lf_evt_ocrd_dt,
person_id, per_in_ler_id, total_score, 1
FROM (
select ec.pl_id, ec.per_in_ler_id,
ep.person_id, pil.lf_evt_ocrd_dt,
sum(sr.computed_score) total_score
from ben_elig_per_elctbl_chc ec,
ben_elig_scre_wtg_f sr,
ben_elig_per_f ep,
ben_per_in_ler pil
where ec. elig_flag = 'Y'
and ec.dflt_flag = 'Y'
and ec.pgm_id = p_pgm_id
and ec.per_in_ler_id = p_per_in_ler_id
--
and ep.per_in_ler_id = ec.per_in_ler_id
and ep.pgm_id = ec.pgm_id
and (ep.pl_id is null or ep.pl_id = ec.pl_id )
and (ep.plip_id is null or ep.plip_id = ec.plip_id )
--
and sr.elig_per_id = ep.elig_per_id
--
and pil.per_in_ler_id = ec.per_in_ler_id
and pil.per_in_ler_stat_cd = 'STRTD'
group by ec.pl_id, ec.per_in_ler_id,ep.person_id,
pil.lf_evt_ocrd_dt
);
INSERT INTO pqh_rank_processes (
rank_process_id, process_cd, pgm_id,pl_id,
benefit_action_id,process_date, person_id,
per_in_ler_id, total_score, object_version_number)
Select pqh_rank_processes_s.nextval, 'GSP', p_pgm_id, pl_id,
p_benefit_action_id, lf_evt_ocrd_dt ,
person_id, per_in_ler_id, total_score,1
FROM ( select pl_id, per_in_ler_id, person_id,
lf_evt_ocrd_dt, sum(GROUP_score) total_score
from (
select ec.pl_id, ec.per_in_ler_id, ep.person_id,
pil.lf_evt_ocrd_dt, crit_tab_short_name,
decode(p_handle_duplicate,'MAX', MAX(sr.computed_score)
,'MIN', MIN(sr.computed_score)
,'AVG', AVG(sr.computed_score) ) GROUP_score
from ben_elig_per_elctbl_chc ec,
ben_elig_scre_wtg_f sr,
ben_elig_per_f ep,
ben_per_in_ler pil
where ec. elig_flag = 'Y'
and ec.dflt_flag = 'Y'
and ec.pgm_id = p_pgm_id
and ec.per_in_ler_id = p_per_in_ler_id
--
and ep.per_in_ler_id = ec.per_in_ler_id
and ep.pgm_id = ec.pgm_id
and (ep.pl_id is null or ep.pl_id = ec.pl_id )
and (ep.plip_id is null or ep.plip_id = ec.plip_id )
--
and sr.elig_per_id = ep.elig_per_id
--
and pil.per_in_ler_id = ec.per_in_ler_id
and pil.per_in_ler_stat_cd = 'STRTD'
group by ec.pl_id, ec.per_in_ler_id, ep.person_id ,
pil.lf_evt_ocrd_dt, crit_tab_short_name
) GRP
GROUP BY pl_id, per_in_ler_id, person_id , lf_evt_ocrd_dt
);
INSERT INTO pqh_rank_processes (
rank_process_id, process_cd, pgm_id,
benefit_action_id, process_date, person_id,
per_in_ler_id, total_score,object_version_number)
Select pqh_rank_processes_s.nextval, 'GSP', pgm_id, p_benefit_action_id,
lf_evt_ocrd_dt, person_id, per_in_ler_id, total_score,1
FROM (
select ec.pgm_id, ec.per_in_ler_id, ep.person_id,
pil.lf_evt_ocrd_dt, sum(sr.computed_score) total_score
from ben_elig_per_elctbl_chc ec,
ben_elig_scre_wtg_f sr,
ben_elig_per_f ep,
ben_per_in_ler pil
where ec. elig_flag = 'Y'
and ec.dflt_flag = 'Y'
and ec.pgm_id = p_pgm_id
--
and ec.per_in_ler_id = p_per_in_ler_id
--
and ep.per_in_ler_id = ec.per_in_ler_id
and ep.pgm_id = ec.pgm_id
-- NS: 05/12/2005: Don't need this join, for it to work when elpro is attached
-- to the program
--and ep.pl_id = ec.pl_id
--and ep.plip_id = ec.plip_id
--
and sr.elig_per_id = ep.elig_per_id
--
and pil.per_in_ler_id = ec.per_in_ler_id
and pil.per_in_ler_stat_cd = 'STRTD'
group by ec.pgm_id, ec.per_in_ler_id, ep.person_id,
pil.lf_evt_ocrd_dt
) ;
INSERT INTO pqh_rank_processes (
rank_process_id, process_cd, pgm_id,
benefit_action_id, process_date, person_id,
per_in_ler_id, total_score, object_version_number)
Select pqh_rank_processes_s.nextval, 'GSP', pgm_id,p_benefit_action_id,
lf_evt_ocrd_dt, person_id, per_in_ler_id, total_score,1
FROM ( select pgm_id, per_in_ler_id, person_id, lf_evt_ocrd_dt,
sum(GROUP_score) total_score
from (
select ec.pgm_id, ec.per_in_ler_id, ep.person_id,
pil.lf_evt_ocrd_dt, crit_tab_short_name,
decode(p_handle_duplicate,'MAX', MAX(sr.computed_score)
,'MIN', MIN(sr.computed_score)
,'AVG', AVG(sr.computed_score) ) GROUP_score
from ben_elig_per_elctbl_chc ec,
ben_elig_scre_wtg_f sr,
ben_elig_per_f ep,
ben_per_in_ler pil
where ec. elig_flag = 'Y'
and ec.dflt_flag = 'Y'
and ec.pgm_id = p_pgm_id
and ec.per_in_ler_id = p_per_in_ler_id
--
and ep.per_in_ler_id = ec.per_in_ler_id
and ep.pgm_id = ec.pgm_id
--and ep.pl_id = ec.pl_id
-- and ep.plip_id = ec.plip_id
--
and sr.elig_per_id = ep.elig_per_id
--
and pil.per_in_ler_id = ec.per_in_ler_id
and pil.per_in_ler_stat_cd = 'STRTD'
group by ec.pgm_id, ec.per_in_ler_id, ep.person_id ,
pil.lf_evt_ocrd_dt, crit_tab_short_name
) GRP
GROUP BY pgm_id, per_in_ler_id, person_id, lf_evt_ocrd_dt
) ;
update pqh_rank_processes
set assignment_id = i.assignment_id
where person_id = p_person_id
and benefit_action_id = p_benefit_action_id
and pgm_id = p_pgm_id;
Insert into pqh_rank_processes (
Rank_process_id, process_cd, pl_id, person_id, assignment_id,
Process_date, total_score)
SELECT pqh_rank_processes_s.nextval,'CWB', group_pl_id, person_id, assignment_id,
lf_evt_ocrd_dt, total_score
FROM (
Select pr.group_pl_id, pr.person_id, pr.assignment_id,
pr.lf_evt_ocrd_dt, sum(sr.score) total_score
From ben_cwb_person_rates pr,
ben_benefit_actions ba,
ben_elig_per_opt_f epo,
ben_elig_per_f ep,
ben_elig_scre_wtg_f sr
Where ba.benefit_action_id = p_benefit_action_id
And pr.group_pl_id = ba.pl_id
And pr.pl_id = pr.group_pl_id
--
And ep.per_in_ler_id = pr.group_per_in_ler_id
And ep.request_id = ba.request_id
And ep.pl_id = pr.group_pl_id
And ep.person_id = pr.person_id
And pr.lf_evt_ocrd_dt between ep.effective_start_date and ep.effective_end_date
--
And epo.elig_per_id = ep.elig_per_id
And epo.request_id = ba.request_id
And pr.lf_evt_ocrd_dt between epo.effective_start_date and epo.effective_end_date
--
And sr.benefit_action_id = ba.benefit_action_id
And sr.elig_per_opt_id = epo.elig_per_opt_id
And pr.lf_evt_ocrd_dt between sr.effective_start_date and sr.effective_end_date
--
Group by pr.group_pl_id, pr.lf_evt_ocrd_dt,pr.person_id, pr.assignment_id);
SELECT pgm_id
FROM ben_elig_per_elctbl_chc
WHERE per_in_ler_id = p_per_in_ler_id
AND dflt_flag = 'Y'
AND rownum < 2;
SELECT mgr_per_in_ler_id, pi.person_id supervisor_id
FROM ben_cwb_group_hrchy gh,
ben_cwb_person_info pi
WHERE gh.lvl_num = 0
AND gh.mgr_per_in_ler_id = pi.group_per_in_ler_id;
INSERT into pqh_rank_process_approvals (
rank_process_approval_id, rank_process_id, supervisor_id,system_rank,
proposed_rank )
SELECT pqh_rank_process_approvals_s.NEXTVAL, rank_process_id, supervisor_id, system_rank,
system_rank
FROM (
select rp.rank_process_id, rec_rnk.supervisor_id, rownum system_rank
from pqh_rank_processes rp,
ben_cwb_group_hrchy gh,
ben_cwb_person_rates pr
where gh.mgr_per_in_ler_id = rec_rnk.mgr_per_in_ler_id
and gh.lvl_num > 0 -- don't include self
and pr.group_per_in_ler_id = gh.emp_per_in_ler_id
and rp.benefit_action_id = p_benefit_action_id
and rp.assignment_id = pr.assignment_id
and rp.process_date = pr.lf_evt_ocrd_dt
and rp.pl_id = pr.group_pl_id
order by rp.total_score desc);
select trtl.display_name
from pqh_table_route_tl trtl,
pqh_table_route tr
where trtl.table_route_id = tr.table_route_id
and from_clause = 'OAB'
and language = userenv('lang')
and tr.table_alias = p_tab_short_name;