[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select lookup_type,
lookup_code
from hr_lookups
where lookup_type in (nvl(p_lookup_type_1,'DUMMY_VALUE'),
nvl(p_lookup_type_2,'DUMMY_VALUE'),
nvl(p_lookup_type_3,'DUMMY_VALUE'),
nvl(p_lookup_type_4,'DUMMY_VALUE'),
nvl(p_lookup_type_5,'DUMMY_VALUE'),
nvl(p_lookup_type_6,'DUMMY_VALUE'),
nvl(p_lookup_type_7,'DUMMY_VALUE'),
nvl(p_lookup_type_8,'DUMMY_VALUE'),
nvl(p_lookup_type_9,'DUMMY_VALUE'),
nvl(p_lookup_type_10,'DUMMY_VALUE'))
and enabled_flag = 'Y'
and p_effective_date
between nvl(start_date_active,p_effective_date)
and nvl(end_date_active, p_effective_date);
g_cache_lookup_object.delete;
select pil.lf_evt_ocrd_dt
from ben_per_in_ler pil
where pil.person_id = p_person_id
and pil.business_group_id + 0 = p_business_group_id
and pil.ler_id = nvl(p_ler_id,pil.ler_id)
and pil.per_in_ler_stat_cd = 'STRTD';
select pil.per_in_ler_id
from ben_per_in_ler pil,
ben_ler_f ler
where pil.person_id = p_person_id
and pil.ler_id = nvl(p_ler_id,pil.ler_id)
and pil.ler_id = ler.ler_id
and pil.per_in_ler_stat_cd = 'STRTD'
and ler.typ_cd <> 'SCHEDDU'
and p_effective_date between
ler.effective_start_date and ler.effective_end_date;
select pil.per_in_ler_id,
pil.lf_evt_ocrd_dt,
pil.ntfn_dt,
pil.ler_id,
ler.name,
ler.typ_cd,
ler.ovridg_le_flag,
ler.ptnl_ler_trtmt_cd,
pil.object_version_number,
pil.ptnl_ler_for_per_id,
ler.qualg_evt_flag
from ben_per_in_ler pil,
ben_ler_f ler
where pil.person_id = p_person_id
and ler.ler_id = pil.ler_id
and ler.ler_id = p_ler_id
and p_effective_date
between ler.effective_start_date
and ler.effective_end_date
and pil.lf_evt_ocrd_dt = p_lf_evt_ocrd_dt
and pil.per_in_ler_stat_cd = 'STRTD'
and ler.typ_cd = 'COMP';
select pil.per_in_ler_id
from ben_per_in_ler pil,
ben_ler_f ler
where pil.person_id = p_person_id
and pil.business_group_id = p_business_group_id
and pil.ler_id = nvl(p_ler_id,pil.ler_id)
and pil.ler_id = ler.ler_id
and pil.per_in_ler_stat_cd = 'STRTD'
and ler.typ_cd = 'SCHEDDU'
and p_effective_date between
ler.effective_start_date and ler.effective_end_date;
select pil.per_in_ler_id,
pil.lf_evt_ocrd_dt,
pil.ntfn_dt,
pil.ler_id,
ler.name,
ler.typ_cd,
ler.ovridg_le_flag,
ler.ptnl_ler_trtmt_cd,
pil.object_version_number,
pil.ptnl_ler_for_per_id,
ler.qualg_evt_flag
from ben_per_in_ler pil,
ben_ler_f ler
where pil.person_id = p_person_id
and ler.ler_id = pil.ler_id
and p_effective_date
between ler.effective_start_date
and ler.effective_end_date
and pil.per_in_ler_stat_cd = 'STRTD'
--
-- CWB Changes GRADE - added 2 more values.
-- iRec Added mode iRecruitment (I)
and ler.typ_cd not in ('SCHEDDU', 'COMP', 'GSP', 'ABS', 'IREC');
select pil.per_in_ler_id,
pil.lf_evt_ocrd_dt,
pil.ntfn_dt,
pil.ler_id,
ler.name,
ler.typ_cd,
ler.ovridg_le_flag,
ler.ptnl_ler_trtmt_cd,
pil.object_version_number,
pil.ptnl_ler_for_per_id,
ler.qualg_evt_flag
from ben_per_in_ler pil,
ben_ler_f ler
where pil.person_id = p_person_id
and pil.business_group_id = p_business_group_id
and ler.ler_id = pil.ler_id
and ler.business_group_id = pil.business_group_id
and p_effective_date
between ler.effective_start_date
and ler.effective_end_date
and pil.per_in_ler_stat_cd = 'STRTD'
-- GSP : make use of the same function for GSP
and ((p_lf_event_mode in ('U','D') and ler.typ_cd = 'SCHEDDU') or -- ICM Change
(p_lf_event_mode = 'M' and ler.typ_cd = 'ABS') or
(p_lf_event_mode = 'G' and ler.typ_cd = 'GSP') or
(p_lf_event_mode = 'I' and ler.typ_cd = 'IREC'
and pil.assignment_id = ben_manage_life_events.g_irec_ass_rec.assignment_id) ); -- iRec
select ler.ler_id,
ler.ler_eval_rl,
ler.name
from ben_ler_f ler
where ler.business_group_id = p_business_group_id
and ler.ler_id = p_ler_id
and p_effective_date
between ler.effective_start_date
and ler.effective_end_date;
select ler.ler_id,
ler.ler_eval_rl,
ler.name
from ben_ler_f ler
where ler.business_group_id = p_business_group_id
and ler.typ_cd = p_typ_cd
and p_effective_date
between ler.effective_start_date
and ler.effective_end_date
and ( p_typ_cd <> 'GSP' /* GSP Rate Sync */
or ( p_typ_cd = 'GSP' and nvl(ler.lf_evt_oper_cd, 'PROG') = nvl(p_lf_evt_oper_cd, 'PROG') ) /* GSP Rate Sync */
);
select ptnl_ler_for_per_id,
object_version_number
from ben_ptnl_ler_for_per ptn
where ptn.business_group_id = p_business_group_id
and ptn.person_id = p_person_id
and ptn.ler_id = p_ler_id
and ptn.lf_evt_ocrd_dt = p_effective_date;
select paf.assignment_id
from per_all_assignments_f paf, per_assignment_status_types pat
where paf.primary_flag = 'Y'
and paf.assignment_type <> 'C'
and paf.business_group_id = p_business_group_id
and paf.person_id = p_person_id
and paf.assignment_status_type_id = pat.assignment_status_type_id(+)
and pat.per_system_status(+) = 'ACTIVE_ASSIGN'
and p_effective_date between paf.effective_start_date and paf.effective_end_date
and hr_security.show_record ('PER_ALL_ASSIGNMENTS_F',
paf.assignment_id,
paf.person_id,
paf.assignment_type
)
= 'TRUE'
order by assignment_type desc, effective_start_date desc;
select paf.assignment_id
from per_all_assignments_f paf, per_assignment_status_types pat
where paf.primary_flag = 'Y'
and paf.assignment_type <> 'C'
and paf.business_group_id = p_business_group_id
and paf.person_id = p_person_id
and paf.assignment_status_type_id = pat.assignment_status_type_id(+)
and pat.per_system_status(+) = 'ACTIVE_ASSIGN'
and p_effective_date between paf.effective_start_date and paf.effective_end_date
order by assignment_type desc, effective_start_date desc;
select null
from ff_formulas_f ff,
per_business_groups pbg
where ff.formula_id = p_formula_id
and ff.formula_type_id = p_formula_type_id
and pbg.business_group_id = p_business_group_id
and nvl(ff.business_group_id,p_business_group_id) =
p_business_group_id
and nvl(ff.legislation_code,pbg.legislation_code) =
pbg.legislation_code
and p_effective_date
between ff.effective_start_date
and ff.effective_end_date;
select ler.name
from ben_ler_f ler
where ler.business_group_id = p_business_group_id
and ler.typ_cd = p_typ_cd
and sysdate
between ler.effective_start_date
and ler.effective_end_date;
g_report_table_object.delete;
g_batch_elig_table_object.delete;
g_batch_ler_table_object.delete;
g_batch_proc_table_object.delete;
g_batch_action_table_object.delete;
g_batch_elctbl_table_object.delete;
g_batch_rate_table_object.delete;
g_batch_dpnt_table_object.delete;
g_batch_commu_table_object.delete;
select ben_reporting_s.nextval into
l_num1_col(l_count)
from sys.dual;
insert into ben_reporting
(reporting_id,
benefit_action_id,
thread_id,
sequence,
text,
object_version_number,
rep_typ_cd,
error_message_code,
national_identifier,
related_person_ler_id,
temporal_ler_id,
ler_id,
person_id,
pgm_id,
pl_id,
related_person_id,
oipl_id,
pl_typ_id,
actl_prem_id,
val,
mo_num,
yr_num)
values
(l_num1_col(l_count),
l_num2_col(l_count),
l_num3_col(l_count),
l_num4_col(l_count),
l_var1_col(l_count),
l_num5_col(l_count),
l_var2_col(l_count),
l_var3_col(l_count),
l_var4_col(l_count),
l_num6_col(l_count),
l_num7_col(l_count),
l_num8_col(l_count),
l_num9_col(l_count),
l_num10_col(l_count),
l_num11_col(l_count),
l_num12_col(l_count),
l_num13_col(l_count),
l_num14_col(l_count),
l_num15_col(l_count),
l_num16_col(l_count),
l_num17_col(l_count),
l_num18_col(l_count));
g_report_table_object.delete;
l_num1_col.delete;
l_num2_col.delete;
l_num3_col.delete;
l_num4_col.delete;
l_num5_col.delete;
l_num6_col.delete;
l_num7_col.delete;
l_num8_col.delete;
l_var1_col.delete;
l_var2_col.delete;
insert into ben_batch_elig_info
(batch_elig_id,
benefit_action_id,
person_id,
pgm_id,
pl_id,
oipl_id,
elig_flag,
inelig_text,
business_group_id,
object_version_number)
values
(l_num1_col(l_count),
l_num2_col(l_count),
l_num3_col(l_count),
l_num4_col(l_count),
l_num5_col(l_count),
l_num6_col(l_count),
l_var1_col(l_count),
l_var2_col(l_count),
l_num7_col(l_count),
l_num8_col(l_count));
g_batch_elig_table_object.delete;
l_num1_col.delete;
l_num2_col.delete;
l_num3_col.delete;
l_num4_col.delete;
l_num5_col.delete;
l_num6_col.delete;
l_num7_col.delete;
l_var1_col.delete;
l_var2_col.delete;
l_var3_col.delete;
l_var4_col.delete;
l_var5_col.delete;
l_var6_col.delete;
l_var7_col.delete;
l_var8_col.delete;
l_var9_col.delete;
l_var10_col.delete;
l_var11_col.delete;
l_var12_col.delete;
l_dat1_col.delete;
insert into ben_batch_ler_info
(batch_ler_id,
benefit_action_id,
person_id,
ler_id,
lf_evt_ocrd_dt,
replcd_flag,
crtd_flag,
tmprl_flag,
dltd_flag,
open_and_clsd_flag,
not_crtd_flag,
stl_actv_flag,
clsd_flag,
clpsd_flag,
clsn_flag,
no_effect_flag,
cvrge_rt_prem_flag,
per_in_ler_id,
business_group_id,
object_version_number)
values
(l_num1_col(l_count),
l_num2_col(l_count),
l_num3_col(l_count),
l_num4_col(l_count),
l_dat1_col(l_count),
l_var1_col(l_count),
l_var2_col(l_count),
l_var3_col(l_count),
l_var4_col(l_count),
l_var5_col(l_count),
l_var6_col(l_count),
l_var7_col(l_count),
l_var8_col(l_count),
l_var9_col(l_count),
l_var10_col(l_count),
l_var11_col(l_count),
l_var12_col(l_count),
l_num5_col(l_count),
l_num6_col(l_count),
l_num7_col(l_count));
g_batch_ler_table_object.delete;
l_num1_col.delete;
l_var1_col.delete;
l_num2_col.delete;
update ben_person_actions
set action_status_cd = l_var1_col(l_count),
object_version_number = l_num2_col(l_count)
where person_action_id = l_num1_col(l_count);
g_batch_action_table_object.delete;
l_num1_col.delete;
l_num2_col.delete;
l_num3_col.delete;
l_num4_col.delete;
l_num5_col.delete;
l_num6_col.delete;
l_num7_col.delete;
l_num8_col.delete;
l_var1_col.delete;
l_var2_col.delete;
l_var3_col.delete;
l_var4_col.delete;
l_dat1_col.delete;
l_dat2_col.delete;
l_dat3_col.delete;
l_dat4_col.delete;
l_dat5_col.delete;
insert into ben_batch_elctbl_chc_info
(batch_elctbl_id,
benefit_action_id,
person_id,
pgm_id,
pl_id,
oipl_id,
enrt_cvg_strt_dt,
enrt_perd_strt_dt,
enrt_perd_end_dt,
erlst_deenrt_dt,
dflt_enrt_dt,
enrt_typ_cycl_cd,
comp_lvl_cd,
mndtry_flag,
dflt_flag,
business_group_id,
object_version_number)
values
(l_num1_col(l_count),
l_num2_col(l_count),
l_num3_col(l_count),
l_num4_col(l_count),
l_num5_col(l_count),
l_num6_col(l_count),
l_dat1_col(l_count),
l_dat2_col(l_count),
l_dat3_col(l_count),
l_dat4_col(l_count),
l_dat5_col(l_count),
l_var1_col(l_count),
l_var2_col(l_count),
l_var3_col(l_count),
l_var4_col(l_count),
l_num7_col(l_count),
l_num8_col(l_count));
g_batch_elctbl_table_object.delete;
l_num1_col.delete;
l_num2_col.delete;
l_num3_col.delete;
l_num4_col.delete;
l_num5_col.delete;
l_num6_col.delete;
l_num7_col.delete;
l_num8_col.delete;
l_num9_col.delete;
l_num10_col.delete;
l_num11_col.delete;
l_num12_col.delete;
l_num13_col.delete;
l_num14_col.delete;
l_var1_col.delete;
l_var2_col.delete;
l_var3_col.delete;
l_var4_col.delete;
l_var5_col.delete;
l_dat1_col.delete;
l_dat2_col.delete;
l_dat3_col.delete;
l_dat4_col.delete;
insert into ben_batch_rate_info
(batch_rt_id,
benefit_action_id,
person_id,
pgm_id,
pl_id,
oipl_id,
bnft_rt_typ_cd,
dflt_flag,
val,
tx_typ_cd,
acty_typ_cd,
mn_elcn_val,
mx_elcn_val,
incrmt_elcn_val,
dflt_val,
rt_strt_dt,
enrt_cvg_strt_dt,
enrt_cvg_thru_dt,
actn_cd,
close_actn_itm_dt,
business_group_id,
object_version_number,
old_val)
values
(l_num1_col(l_count),
l_num2_col(l_count),
l_num3_col(l_count),
l_num4_col(l_count),
l_num5_col(l_count),
l_num6_col(l_count),
l_var1_col(l_count),
l_var2_col(l_count),
l_num7_col(l_count),
l_var3_col(l_count),
l_var4_col(l_count),
l_num8_col(l_count),
l_num9_col(l_count),
l_num10_col(l_count),
l_num11_col(l_count),
l_dat1_col(l_count),
l_dat2_col(l_count),
l_dat3_col(l_count),
l_var5_col(l_count),
l_dat4_col(l_count),
l_num12_col(l_count),
l_num13_col(l_count),
l_num14_col(l_count));
g_batch_rate_table_object.delete;
l_num1_col.delete;
l_num2_col.delete;
l_num3_col.delete;
l_num4_col.delete;
l_num5_col.delete;
l_num6_col.delete;
l_num7_col.delete;
l_num8_col.delete;
l_num9_col.delete;
l_var1_col.delete;
l_var2_col.delete;
l_dat1_col.delete;
l_dat2_col.delete;
insert into ben_batch_dpnt_info
(batch_dpnt_id,
benefit_action_id,
person_id,
pgm_id,
pl_id,
oipl_id,
contact_typ_cd,
dpnt_person_id,
enrt_cvg_strt_dt,
enrt_cvg_thru_dt,
actn_cd,
business_group_id,
object_version_number)
values
(l_num1_col(l_count),
l_num2_col(l_count),
l_num3_col(l_count),
l_num4_col(l_count),
l_num5_col(l_count),
l_num6_col(l_count),
l_var1_col(l_count),
l_num7_col(l_count),
l_dat1_col(l_count),
l_dat2_col(l_count),
l_var2_col(l_count),
l_num8_col(l_count),
l_num9_col(l_count));
g_batch_dpnt_table_object.delete;
l_num1_col.delete;
l_num2_col.delete;
l_num3_col.delete;
l_num4_col.delete;
l_num5_col.delete;
l_num6_col.delete;
l_dat1_col.delete;
l_num7_col.delete;
l_num8_col.delete;
insert into ben_batch_commu_info
(batch_commu_id,
benefit_action_id,
person_id,
per_cm_id,
cm_typ_id,
per_cm_prvdd_id,
to_be_sent_dt,
business_group_id,
object_version_number)
values
(l_num1_col(l_count),
l_num2_col(l_count),
l_num3_col(l_count),
l_num4_col(l_count),
l_num5_col(l_count),
l_num6_col(l_count),
l_dat1_col(l_count),
l_num7_col(l_count),
l_num8_col(l_count));
g_batch_commu_table_object.delete;
select ben_reporting_s.nextval into
g_report_table_object(l_count).reporting_id
from sys.dual;
select PROCESS_DATE,
MODE_CD,
DERIVABLE_FACTORS_FLAG,
VALIDATE_FLAG,
PERSON_ID,
PERSON_TYPE_ID,
PGM_ID,
BUSINESS_GROUP_ID,
PL_ID,
POPL_ENRT_TYP_CYCL_ID,
NO_PROGRAMS_FLAG,
NO_PLANS_FLAG,
COMP_SELECTION_RL,
PERSON_SELECTION_RL,
LER_ID,
ORGANIZATION_ID,
BENFTS_GRP_ID,
LOCATION_ID,
PSTL_ZIP_RNG_ID,
RPTG_GRP_ID,
PL_TYP_ID,
OPT_ID,
ELIGY_PRFL_ID,
VRBL_RT_PRFL_ID,
LEGAL_ENTITY_ID,
PAYROLL_ID,
CM_TRGR_TYP_CD,
DEBUG_MESSAGES_FLAG,
CM_TYP_ID,
AGE_FCTR_ID,
MIN_AGE,
MAX_AGE,
LOS_FCTR_ID,
MIN_LOS,
MAX_LOS,
CMBN_AGE_LOS_FCTR_ID,
MIN_CMBN,
MAX_CMBN,
DATE_FROM,
ELIG_ENROL_CD,
ACTN_TYP_ID,
AUDIT_LOG_FLAG,
LF_EVT_OCRD_DT,
LMT_PRPNIP_BY_ORG_FLAG,
INELG_ACTION_CD
from ben_benefit_actions
where benefit_action_id = p_benefit_action_id;
select ben_batch_elig_info_s.nextval into
g_batch_elig_table_object(l_count).batch_elig_id
from sys.dual;
select ben_batch_elctbl_chc_info_s.nextval into
g_batch_elctbl_table_object(l_count).batch_elctbl_id
from sys.dual;
select ben_batch_rate_info_s.nextval into
g_batch_rate_table_object(l_count).batch_rt_id
from sys.dual;
select ben_batch_dpnt_info_s.nextval into
g_batch_dpnt_table_object(l_count).batch_dpnt_id
from sys.dual;
select ben_batch_commu_info_s.nextval into
g_batch_commu_table_object(l_count).batch_commu_id
from sys.dual;
select ben_batch_ler_info_s.nextval into
g_batch_ler_table_object(l_count).batch_ler_id
from sys.dual;
select ben_reporting_s.nextval into
g_report_table_object(l_count).reporting_id
from sys.dual;
procedure update_life_event_cache
(p_open_and_closed in varchar2 ) is
--
l_proc varchar2(80) := 'benutils.update_life_event_cache';
end update_life_event_cache;
select nvl(bbp.thread_cnt_num,
decode(p_batch_exe_cd,'BENGCMOD',1,3)),
nvl(bbp.chunk_size,10),
nvl(bbp.max_err_num,20)
from ben_batch_parameter bbp
where bbp.batch_exe_cd = p_batch_exe_cd
and bbp.business_group_id = p_business_group_id;
select a.comments
from all_tab_comments a
where a.table_name = p_tablename
and a.owner = upper(l_oracle_schema);
select t.column_name
from all_tab_columns t,
all_cons_columns c,
all_constraints a
where a.constraint_type = 'P'
and a.table_name = p_tablename
and a.constraint_name = c.constraint_name
and t.table_name = c.table_name
and t.column_name = c.column_name
and t.table_name = a.table_name
and t.owner = upper(l_oracle_schema)
and c.owner = upper(l_oracle_schema)
and a.owner = upper(l_oracle_schema)
order by c.position;*/
select col.column_name
from --all_tab_columns t,
user_synonyms syn,
dba_tab_columns col,
all_cons_columns c,
all_constraints a
where a.constraint_type = 'P'
and a.table_name =p_tablename
and a.constraint_name = c.constraint_name
-- and t.table_name = c.table_name
and syn.synonym_name = c.table_name
and col.table_name = syn.table_name
and col.owner = syn.table_owner
-- and t.column_name = c.column_name
and col.column_name=c.column_name
-- and t.table_name = a.table_name
and syn.synonym_name=a.table_name
and col.owner = upper(l_oracle_schema)
and c.owner = upper(l_oracle_schema)
and a.owner = upper(l_oracle_schema)
order by c.position;
/* select null
from all_tab_columns t
where t.table_name = p_tablename
and (substr(t.column_name,length(t.column_name)-2,3) = '_CD'
or substr(t.column_name,length(t.column_name)-3,4) = '_IND'
or substr(t.column_name,length(t.column_name)-3,4) = '_UOM'
or substr(t.column_name,length(t.column_name)-2,3) = '_RL'
or substr(t.column_name,length(t.column_name)-4,5) = '_FLAG')
and t.owner = upper(l_oracle_schema);*/
select null
from --all_tab_columns t
user_synonyms syn,dba_tab_columns col
where-- t.table_name = p_tablename
syn.synonym_name = p_tablename
and col.owner = syn.table_owner
and col.table_name = syn.table_name
and (substr(col.column_name,length(col.column_name)-2,3) = '_CD'
or substr(col.column_name,length(col.column_name)-3,4) = '_IND'
or substr(col.column_name,length(col.column_name)-3,4) = '_UOM'
or substr(col.column_name,length(col.column_name)-2,3) = '_RL'
or substr(col.column_name,length(col.column_name)-4,5) = '_FLAG')
and col.owner = upper(l_oracle_schema);
/* select c.column_name
from all_tab_columns t,
all_cons_columns c,
all_constraints a
where a.constraint_type = 'P'
and a.table_name = p_tablename
and a.constraint_name = c.constraint_name
and t.column_name = c.column_name
and t.table_name = a.table_name
and t.owner = upper(l_oracle_schema)
and c.owner = upper(l_oracle_schema)
and a.owner = upper(l_oracle_schema)
order by c.position;*/
select c.column_name
from --all_tab_columns t,
user_synonyms syn,
dba_tab_columns col,
all_cons_columns c,
all_constraints a
where a.constraint_type = 'P'
and a.table_name =p_tablename
and a.constraint_name = c.constraint_name
and syn.synonym_name = a.table_name
and col.table_name = syn.table_name
and col.owner = syn.table_owner
-- and t.column_name = c.column_name
and col.column_name=c.column_name
-- and t.table_name = a.table_name
and col.owner = upper(l_oracle_schema)
and c.owner = upper(l_oracle_schema)
and a.owner = upper(l_oracle_schema)
order by c.position;
/* select null
from all_tab_columns a
where a.column_name = 'BUSINESS_GROUP_ID'
and a.table_name = p_tablename
and a.owner = upper(l_oracle_schema);*/
select null
from user_synonyms syn,dba_tab_columns col
where syn.synonym_name = p_tablename
and col.owner = syn.table_owner
and col.table_name = syn.table_name
and col.column_name = 'BUSINESS_GROUP_ID'
and col.owner = upper(l_oracle_schema);
/* select null
from all_tab_columns utc
where utc.table_name = p_tablename
and utc.column_name like '%ATTRIBUTE%'
and utc.owner = upper(l_oracle_schema);*/
select null
from -- all_tab_columns utc
user_synonyms syn,dba_tab_columns col
where syn.synonym_name = p_tablename
and col.owner = syn.table_owner
and col.table_name = syn.table_name
and col.column_name like '%ATTRIBUTE%'
and col.owner = upper(l_oracle_schema);
select con.constraint_name
from all_constraints con
where con.table_name = p_tablename
and con.constraint_type = 'P'
and con.owner = upper(l_oracle_schema);
select asg.assignment_id,asg.organization_id,loc.region_2
from per_all_assignments_f asg,hr_locations_all loc
where asg.assignment_id = p_assignment_id
and asg.primary_flag = 'Y'
and asg.location_id = loc.location_id(+)
and p_effective_date
between asg.effective_start_date
and asg.effective_end_date;
select bg.legislation_code
from per_business_groups bg
where bg.business_group_id = p_business_group_id;
SELECT O3.ORG_INFORMATION9
FROM HR_ALL_ORGANIZATION_UNITS O ,
HR_ORGANIZATION_INFORMATION O3
where O.ORGANIZATION_ID = O3.ORGANIZATION_ID
and O3.ORG_INFORMATION_CONTEXT = 'Business Group Information'
and o.ORGANIZATION_ID = p_business_group_id
and o.business_group_id = p_business_group_id;
select paf.*
from per_all_assignments_f paf
where paf.person_id = p_person_id
and paf.business_group_id = p_business_group_id
and paf.primary_flag = 'Y'
and paf.assignment_type = 'E'
and p_effective_date
between paf.effective_start_date
and paf.effective_end_date;
select nvl(prv.cmcd_rt_val,0)
from ben_prtt_rt_val prv
where prv.prtt_rt_val_id = p_prtt_rt_val_id
and prv.per_in_ler_id = p_per_in_ler_id
and prv.prtt_rt_val_stat_cd is null -- Added for Bug 6048854
and prv.rt_strt_dt <= prv.rt_end_dt;
select nvl(prv.cmcd_rt_val,0)
from ben_prtt_rt_val prv
where prv.prtt_rt_val_id = p_prtt_rt_val_id
and prv.per_in_ler_id = p_per_in_ler_id
Bug 5376185 : Pick the latest non-recurring rate
and prv.rt_strt_dt = prv.rt_end_dt
and prv.rt_end_dt <> hr_api.g_eot;
select nvl(prv.ann_rt_val,0)
from ben_prtt_rt_val prv
where prv.prtt_rt_val_id = p_prtt_rt_val_id
and prv.per_in_ler_id = p_per_in_ler_id
and prv.prtt_rt_val_stat_cd is null -- Added for Bug 6048854
and prv.rt_strt_dt <= prv.rt_end_dt;
select nvl(prv.ann_rt_val,0)
from ben_prtt_rt_val prv
where prv.prtt_rt_val_id = p_prtt_rt_val_id
and prv.per_in_ler_id = p_per_in_ler_id
Bug 5376185 : Pick the latest non-recurring rate
and prv.rt_strt_dt = prv.rt_end_dt
and prv.rt_end_dt <> hr_api.g_eot;
select to_char(nvl(prv.ann_rt_val,0))||'^'|| to_char(nvl(prv.cmcd_rt_val,0))||'^'||to_char(nvl(prv.rt_val,0))
from ben_prtt_rt_val prv
where prv.prtt_rt_val_id = p_prtt_rt_val_id
and prv.per_in_ler_id = p_per_in_ler_id
and prv.rt_end_dt = hr_api.g_eot;
select nvl(prv.rt_val,0)
from ben_prtt_rt_val prv
where prv.prtt_rt_val_id = p_prtt_rt_val_id
and prv.per_in_ler_id = p_per_in_ler_id
and prv.prtt_rt_val_stat_cd is null -- Added for Bug 6048854
and prv.rt_strt_dt <= prv.rt_end_dt;
select nvl(prv.rt_val,0)
from ben_prtt_rt_val prv
where prv.prtt_rt_val_id = p_prtt_rt_val_id
and prv.per_in_ler_id = p_per_in_ler_id
Bug 5376185 : Pick the latest non-recurring rate
and prv.rt_strt_dt = prv.rt_end_dt
and prv.rt_end_dt <> hr_api.g_eot;
select pen.bnft_amt
from ben_prtt_enrt_rslt_f pen,
ben_elig_per_elctbl_chc epe,
ben_enrt_bnft enb
where epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
and enb.enrt_bnft_id = p_enrt_bnft_id
and epe.elig_per_elctbl_chc_id = enb.elig_per_elctbl_chc_id
-- and enb.mx_wo_ctfn_flag = 'N'
-- and enb.cvg_mlt_cd = 'ERL'
-- commented, so that we retrieve SAAEAR cvgs based on ERL rates.
and nvl(epe.prtt_enrt_rslt_id,
enb.prtt_enrt_rslt_id) = pen.prtt_enrt_rslt_id
and p_effective_date between
pen.effective_start_date and pen.effective_end_date
and pen.prtt_enrt_rslt_stat_cd is null
and pen.enrt_cvg_thru_dt <= pen.effective_end_date;
select enrt_rt_id,
nvl(prv.rt_val, 0) val,
nvl(prv.cmcd_rt_val, 0) rt_val,
nvl(prv.ann_rt_val, 0) ann_rt_val
from ben_prtt_rt_val prv,
ben_enrt_rt ecr
where prv.prtt_rt_val_id = ecr.prtt_rt_val_id
and ecr.enrt_rt_id in (p_enrt_rt_id, p_enrt_rt_id2,
p_enrt_rt_id3, p_enrt_rt_id4);
select 'Y'
from wf_item_activity_statuses process
,wf_item_attribute_values choice_attribute
,wf_item_attribute_values submit_attribute
,wf_process_activities activity
,hr_api_transaction_steps step
where activity.activity_name = 'HR_INDIVIDUAL_COMP_PRC'
and activity.process_item_type = activity.activity_item_type
and activity.instance_id = process.process_activity
and process.activity_status = 'ACTIVE'
and process.item_key = choice_attribute.item_key
and choice_attribute.item_type = process.item_type
and choice_attribute.name = 'COMP_CHOICE_ID'
and choice_attribute.text_value = p_elig_per_elctbl_chc_id
and submit_attribute.item_key = process.item_key
and submit_attribute.item_type = process.item_type
and submit_attribute.name = 'TRAN_SUBMIT'
and submit_attribute.text_value = 'Y'
and step.item_type = choice_attribute.item_type
and choice_attribute.item_key = step.item_key
and step.api_name = 'BEN_PROCESS_COMPENSATION_W.PROCESS_API';
select 'Y'
from wf_item_activity_statuses process ,
wf_process_activities activity ,
hr_api_transactions txn,
hr_api_transaction_steps step ,
wf_item_attribute_values submit_attribute
where activity.process_name = 'ROOT'
and activity.process_item_type = activity.activity_item_type
and activity.instance_id = process.process_activity
and process.activity_status = 'ACTIVE'
and txn.item_type = process.item_type
and txn.item_key = process.item_key
and txn.selected_person_id = p_person_id
and txn.transaction_id = step.transaction_id
and step.api_name = 'BEN_PROCESS_COMPENSATION_W.PROCESS_API'
and submit_attribute.text_value = 'Y'
and txn.item_type = submit_attribute.item_type
and txn.item_key = submit_attribute.item_key
and submit_attribute.name = 'TRAN_SUBMIT';
select pl.nip_acty_ref_perd_cd
,nvl(cur.precision,2)
from ben_pl_f pl
,fnd_currencies cur
where pl.pl_id = p_pl_id
and p_effective_date between pl.effective_start_date
and pl.effective_end_date
and cur.CURRENCY_CODE(+) = pl.nip_pl_uom
;
select ppb.pay_annualization_factor
from per_all_assignments_f asg
,per_pay_bases ppb
where asg.assignment_id = p_assignment_id
and p_effective_date between asg.effective_start_date
and asg.effective_end_date
and ppb.pay_basis_id = asg.pay_basis_id
;
select pl.nip_acty_ref_perd_cd
,nvl(cur.precision,2)
from ben_pl_f pl
,fnd_currencies cur
where pl.pl_id = p_pl_id
and p_effective_date between pl.effective_start_date
and pl.effective_end_date
and cur.CURRENCY_CODE(+) = pl.nip_pl_uom
;
select ppb.pay_annualization_factor
from per_all_assignments_f asg
,per_pay_bases ppb
where asg.assignment_id = p_assignment_id
and p_effective_date between asg.effective_start_date
and asg.effective_end_date
and ppb.pay_basis_id = asg.pay_basis_id
;
select upper(substr(pap.parameter_value,1,1))
from pay_action_parameters pap
where pap.parameter_name = 'DATA_MIGRATOR_MODE';
select upper(substr(pap.parameter_value,1,1))
from pay_action_parameter_values pap
where pap.parameter_name = 'DATA_MIGRATOR_MODE'
and pap.ACTION_PARAMETER_GROUP_ID = p_pap_grp_id ;
Select bpp.pgm_id
Into lv_pgm_id
From ben_bnft_prvdr_pool_f bpp,
fnd_sessions se
Where se.session_id = p_session_id
And bpp.bnft_prvdr_pool_id = p_bnft_prvdr_pool_id
And se.effective_date Between bpp.effective_start_date And bpp.effective_End_date;
Select bpp.pl_name, bpp.opt_name, bpp.abr_name, bpp.meaning
Into lv_pl_name, lv_opt_name, lv_abr_name, lv_meaning
From
(Select plip.pgm_id pgm_id, abr.acty_base_rt_id acty_base_rt_id,
abr.business_group_id business_group_id,
pl.name pl_name, Null opt_name, abr.name abr_name,
substr(hr_general.decode_lookup('BEN_TX_TYP',abr.tx_typ_cd),1,60) meaning
From ben_acty_base_rt_f abr,
ben_plip_f plip,
ben_pl_f pl,
fnd_sessions se
Where se.session_id = p_session_id
And plip.pgm_id = lv_pgm_id
And plip.pl_id = pl.pl_id
And abr.pl_id = pl.pl_id
And abr.acty_base_rt_id = p_acty_base_rt_id
/* And pl.invk_dcln_prtn_pl_flag = 'N' */
And pl.invk_flx_cr_pl_flag = 'N'
And pl.imptd_incm_calc_cd is Null
And abr.rt_usg_cd = 'STD'
And abr.asn_on_enrt_flag = 'Y'
And abr.business_group_id = p_business_group_id
And se.effective_date Between abr.effective_start_date And abr.effective_End_date
And se.effective_date Between plip.effective_start_date And plip.effective_End_date
And se.effective_date Between pl.effective_start_date And pl.effective_End_date
Union
Select plip.pgm_id pgm_id, abr.acty_base_rt_id acty_base_rt_id,
abr.business_group_id,
pl.name pl_name, Null opt_name, abr.name abr_name,
substr(hr_general.decode_lookup('BEN_TX_TYP',abr.tx_typ_cd),1,60) meaning
From ben_acty_base_rt_f abr,
ben_plip_f plip,
ben_pl_f pl,
fnd_sessions se
Where se.session_id = p_session_id
And plip.pgm_id = lv_pgm_id
And plip.pl_id = pl.pl_id
And abr.plip_id = plip.plip_id
And abr.acty_base_rt_id = p_acty_base_rt_id
/* And pl.invk_dcln_prtn_pl_flag = 'N' */
And pl.invk_flx_cr_pl_flag = 'N'
And pl.imptd_incm_calc_cd is Null
And abr.rt_usg_cd = 'STD'
And abr.asn_on_enrt_flag = 'Y'
And abr.business_group_id = p_business_group_id
And se.effective_date Between abr.effective_start_date And abr.effective_End_date
And se.effective_date Between plip.effective_start_date And plip.effective_End_date
And se.effective_date Between pl.effective_start_date And pl.effective_End_date
Union
Select plip.pgm_id pgm_id, abr.acty_base_rt_id acty_base_rt_id,
abr.business_group_id,
pl.name pl_name, opt.name opt_name, abr.name abr_name,
substr(hr_general.decode_lookup('BEN_TX_TYP',abr.tx_typ_cd),1,60) meaning
From ben_acty_base_rt_f abr,
ben_plip_f plip,
ben_pl_f pl,
ben_oipl_f oipl,
ben_opt_f opt,
fnd_sessions se
Where se.session_id = p_session_id
And plip.pgm_id = lv_pgm_id
And plip.pl_id = pl.pl_id
And oipl.pl_id = pl.pl_id
And abr.oipl_id = oipl.oipl_id
And abr.acty_base_rt_id = p_acty_base_rt_id
And oipl.opt_id = opt.opt_id
/* And pl.invk_dcln_prtn_pl_flag = 'N' */
And pl.invk_flx_cr_pl_flag = 'N'
And pl.imptd_incm_calc_cd is Null
And abr.rt_usg_cd = 'STD'
And abr.asn_on_enrt_flag = 'Y'
And abr.business_group_id = p_business_group_id
And se.effective_date Between abr.effective_start_date And abr.effective_End_date
And se.effective_date Between plip.effective_start_date And plip.effective_End_date
And se.effective_date Between pl.effective_start_date And pl.effective_End_date
And se.effective_date Between oipl.effective_start_date And oipl.effective_End_date
And se.effective_date Between opt.effective_start_date And opt.effective_End_date
Union
Select plip.pgm_id pgm_id, abr.acty_base_rt_id acty_base_rt_id,
abr.business_group_id,
pl.name pl_name, opt.name opt_name, abr.name abr_name,
substr(hr_general.decode_lookup('BEN_TX_TYP',abr.tx_typ_cd),1,60) meaning
From ben_acty_base_rt_f abr,
ben_plip_f plip,
ben_pl_f pl,
ben_oipl_f oipl,
ben_oiplip_f oiplip,
ben_opt_f opt,
fnd_sessions se
Where se.session_id = p_session_id
And plip.pgm_id = lv_pgm_id
And plip.pl_id = pl.pl_id
And oipl.pl_id = pl.pl_id
And abr.oiplip_id = oiplip.oiplip_id
And abr.acty_base_rt_id = p_acty_base_rt_id
And oiplip.oipl_id = oipl.oipl_id
And oipl.opt_id = opt.opt_id
And plip.plip_id = oiplip.plip_id
/* And pl.invk_dcln_prtn_pl_flag = 'N' */
And pl.invk_flx_cr_pl_flag = 'N'
And pl.imptd_incm_calc_cd is Null
And abr.rt_usg_cd = 'STD'
And abr.asn_on_enrt_flag = 'Y'
And abr.business_group_id = p_business_group_id
And se.effective_date Between abr.effective_start_date And abr.effective_End_date
And se.effective_date Between plip.effective_start_date And plip.effective_End_date
And se.effective_date Between pl.effective_start_date And pl.effective_End_date
And se.effective_date Between oipl.effective_start_date And oipl.effective_End_date
And se.effective_date Between opt.effective_start_date And opt.effective_End_date
And se.effective_date Between oiplip.effective_start_date And oiplip.effective_End_date
) BPP;
select se.effective_date
from fnd_sessions se
where session_id = userenv('SESSIONID');
select pil.lf_evt_ocrd_dt
from ben_per_in_ler pil
where per_in_ler_id = p_per_in_ler_id;
select max(last_update_date)
from (select max(nvl(last_update_date,p_pil_last_upd_date)) last_update_date
from per_addresses
where person_id = p_person_id
and business_group_id = p_business_group_id
union
select max(nvl(last_update_date,p_pil_last_upd_date)) last_update_date
from per_all_assignments_f
where person_id = p_person_id
and business_group_id = p_business_group_id
union
select max(nvl(last_update_date,p_pil_last_upd_date)) last_update_date
from per_all_people_f
where person_id = p_person_id
and business_group_id = p_business_group_id
union
select max(nvl(last_update_date,p_pil_last_upd_date)) last_update_date
from per_contact_relationships
where person_id = p_person_id
and business_group_id = p_business_group_id
union
select max(nvl(psl.last_update_date,p_pil_last_upd_date)) last_update_date
from per_pay_proposals psl, per_all_assignments_f asn
where psl.assignment_id = asn.assignment_id
and asn.person_id = p_person_id
and asn.business_group_id = p_business_group_id
union
select max(nvl(last_update_date,p_pil_last_upd_date)) last_update_date
from per_periods_of_service
where person_id = p_person_id
and business_group_id = p_business_group_id
union
select max(nvl(last_update_date,p_pil_last_upd_date)) last_update_date
from per_qualifications
where person_id = p_person_id
and business_group_id = p_business_group_id
union
select max(nvl(last_update_date,p_pil_last_upd_date)) last_update_date
from ben_per_bnfts_bal_f
where person_id = p_person_id
and business_group_id = p_business_group_id
union
select max(nvl(last_update_date,p_pil_last_upd_date)) last_update_date
from per_absence_attendances
where person_id = p_person_id
and business_group_id = p_business_group_id
union
select max(nvl(last_update_date,p_pil_last_upd_date)) last_update_date
from per_person_type_usages_f
where person_id = p_person_id
);
select pil.lf_evt_ocrd_dt lf_evt_ocrd_dt,
pil.last_update_date last_update_date
from ben_per_in_ler pil , ben_ler_f ler
where pil.person_id = p_person_id
and pil.business_group_id = p_business_group_id
and pil.per_in_ler_stat_cd = 'STRTD'
and ler.ler_id = pil.ler_id
and ler.typ_cd = 'SCHEDDU'
and p_effective_date between ler.effective_start_date and ler.effective_end_date;
Select bcpi.full_name,
bcpi.brief_name,
bcpi.custom_name
From ben_cwb_person_info bcpi,
ben_cwb_group_hrchy bcgh
where bcgh.emp_per_in_ler_id = p_emp_per_in_ler_id
and bcgh.lvl_num = (select max(lvl_num) - p_level + 1
from ben_cwb_group_hrchy
where emp_per_in_ler_id = p_emp_per_in_ler_id)
and bcgh.lvl_num > 0
and bcgh.mgr_per_in_ler_id = bcpi.group_per_in_ler_id;
select epo.ENRT_PERD_STRT_DT
from ben_elig_per_elctbl_chc epe,
ben_pil_elctbl_chc_popl epo
where epe.elig_per_elctbl_chc_id = p_elig_per_elctbl_chc_id
and epe.pil_elctbl_chc_popl_id = epo.pil_elctbl_chc_popl_id ;
select 'Y'
from ben_elig_cvrd_dpnt_f pdp,
ben_per_in_ler pil
where pdp.elig_cvrd_dpnt_id = p_elig_cvrd_dpnt_id
and pdp.cvg_thru_dt = hr_api.g_eot
-- and pdp.effective_end_date <> hr_api.g_eot In the unrestricted enrollment. may continue without per_in_ler update.why
-- and pdp.per_in_ler_id = p_per_in_ler_id
and (l_enrt_perd_strt_dt -1 ) between
pdp.effective_start_date and pdp.effective_end_date
and pdp.per_in_ler_id = pil.per_in_ler_id
and pil.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT');
select 'Y'
from dual where exists( select null
from ben_elig_cvrd_dpnt_f pdp,
ben_per_in_ler pil,
ben_prtt_enrt_rslt_f pnr,
ben_prtt_enrt_rslt_f pen
where pdp.dpnt_person_id = p_dpnt_person_id
--and pdp.cvg_thru_dt = hr_api.g_eot
and pdp.effective_end_date = hr_api.g_eot
and pdp.prtt_enrt_rslt_id = pnr.prtt_enrt_rslt_id
--and (l_enrt_perd_strt_dt -1 ) between pdp.effective_start_date
-- and pdp.effective_end_date
and pnr.pl_typ_id = pen.pl_typ_id
and pnr.pl_id = pen.pl_id --9905474
and pnr.prtt_enrt_rslt_id <> pen.prtt_enrt_rslt_id
and pnr.prtt_enrt_rslt_stat_cd IS NULL
--and pen.prtt_enrt_rslt_stat_cd IS NULL
and pen.prtt_enrt_rslt_id = p_prtt_enrt_rslt_id -- epe.pen
and pen.effective_end_date = hr_api.g_eot --new pen
and pnr.effective_end_date = hr_api.g_eot --old
and pdp.per_in_ler_id = p_per_in_ler_id --9905474
and pdp.per_in_ler_id = pil.per_in_ler_id
and pil.per_in_ler_stat_cd not in ('VOIDD', 'BCKDT') );