[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select transaction_category_id
from pqh_transaction_categories
where short_name = 'BEN_PDCPWZ';
select copy_entity_txn_id
from pqh_copy_entity_txns
where transaction_category_id = p_transaction_category_id
and context_business_group_id = 0
and display_name = 'GHR_TSP_SEED_PROGRAM_DESIGN';
Cursor update_program_status is
select * from ben_pgm_f
where name = 'Federal Thrift Savings Plan (TSP)'
and business_group_id = p_target_business_group_id;
For i in update_program_status Loop
ben_Program_api.update_program(
p_pgm_id => i.pgm_id
,p_effective_start_date => l_effective_start_date
,p_effective_end_date => l_effective_end_date
,p_pgm_stat_cd => 'A'
,p_object_version_number => i.object_version_number
,p_effective_date => i.effective_start_date
,p_datetrack_mode => 'CORRECTION'
);
select 'Y' from ben_pgm_f
where name = 'Federal Thrift Savings Plan (TSP)'
and business_group_id = p_target_business_group_id;
select pgm.pgm_id
from ben_pgm_f pgm
where pgm.name = 'Federal Thrift Savings Plan (TSP)'
and pgm.business_group_id = p_business_group_id;
select ler_id
from ben_ler_f
where name = 'Termination of Appointment'
and business_group_id = p_business_group_id
and trunc(p_effective_date) between effective_start_date and effective_end_date;
select 1
from ben_prtt_enrt_rslt_f
where person_id = p_person_id
and business_group_id = p_business_group_id
and pgm_id = p_pgm_id
and enrt_cvg_thru_dt = hr_api.g_eot
and trunc(p_effective_date) between effective_start_date and effective_end_date
and prtt_enrt_rslt_stat_cd is null;
select pl_id,pl_typ_id,ptip_id,prtt_enrt_rslt_id,oipl_id
from ben_prtt_enrt_rslt_f
where person_id = p_person_id
and business_group_id = p_business_group_id
and pgm_id = p_pgm_id
and ler_id = l_term_ler_id
order by prtt_enrt_rslt_id desc;
select rt_val
from ben_prtt_rt_val
where prtt_enrt_rslt_id = get_ee_last_tsp_enrt_rec.prtt_enrt_rslt_id
order by prtt_rt_val_id desc;
select elig_per_elctbl_chc_id,
pil.per_in_ler_id
from ben_elig_per_ELCTBL_chc chc ,
ben_per_in_ler pil
where chc.pgm_id = p_pgm_id
and chc.pl_typ_id = get_ee_last_tsp_enrt_rec.pl_typ_id
and chc.pl_id = get_ee_last_tsp_enrt_rec.pl_id
and chc.oipl_id = get_ee_last_tsp_enrt_rec.oipl_id
--and chc.plip_id = l_plip_id
and chc.ptip_id = get_ee_last_tsp_enrt_rec.ptip_id
and pil.per_in_ler_id = chc.per_in_ler_id
and pil.ler_id = p_ler_id
and pil.person_id = p_person_id;
select enrt_rt_id
from ben_enrt_rt
where elig_per_elctbl_chc_id = l_elig_per_elctbl_chc_id;
select assignment_id
from per_all_assignments_f
where person_id = p_person_id
and trunc (p_effective_date - 1) between effective_start_date and effective_end_date
and assignment_type = 'E';
,p_datetrack_mode => 'INSERT'
,p_suspend_flag => l_suspend_flag
,p_effective_start_date => l_esd
,p_effective_end_date => l_eed
,p_object_version_number => l_ovn
,p_prtt_enrt_interim_id => l_prtt_enrt_interim_id
,p_business_group_id => p_business_group_id
,p_dpnt_actn_warning => l_Boolean
,p_bnf_actn_warning => l_Boolean
,p_ctfn_actn_warning => l_Boolean
);
select pgm.pgm_id
from ben_pgm_f pgm
where pgm.name = 'Federal Thrift Savings Plan (TSP)'
and pgm.business_group_id = l_business_group_id;
select person_id,business_group_id,ler_id,lf_evt_ocrd_dt
from ben_ptnl_ler_for_per
where ler_id in (select ler_id from ben_ler_f where name = 'TSP Continuation of Coverage'
and sysdate between effective_start_date and effective_end_date)
and ptnl_ler_for_per_stat_cd = 'UNPROCD'
and lf_evt_ocrd_dt <= sysdate;
select
e.assignment_id assignment_id,
decode(name,'Rate','Percentage',
'Status','Terminate Contributions',
name) Name,
decode (screen_entry_value,'T',0,'S',0,screen_entry_value) screen_entry_value,
e.effective_start_date,
g.person_id
from pay_element_types_f a,
pay_input_values_f b,
pay_element_links_f c,
pay_link_input_values_f d,
pay_element_entries_f e,
pay_element_entry_values_f f,
per_all_assignments_f g
where a.element_type_id = b.element_type_id
and a.element_type_id = c.element_type_id
and c.element_link_id = d.element_link_id
and b.input_value_id = d.input_value_id
and e.element_link_id = c.element_link_id
and f.element_entry_id = e.element_entry_id
and f.input_value_id = b.input_value_id
and g.business_group_id = c_business_group_id
and e.effective_end_date = hr_api.g_eot
--and trunc(sysdate) between e.effective_start_date and e.effective_End_date
and trunc(e.effective_start_date) between f.effective_start_date and f.effective_End_date
and g.assignment_id = e.assignment_id
and trunc(e.effective_start_date) between g.effective_start_date and g.effective_end_date
--and a.element_name = 'TSP'
and a.element_name = c_element_name
and ( (b.name in ('Rate','Amount') and (ghr_general.return_number(screen_entry_value) > 0))
or (b.name = 'Status' and screen_entry_value in ('T','S')))
order by 1, 2 desc ;