[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_comp_object_selection_rule out nocopy varchar2,
p_person_selection_rule out nocopy varchar2,
p_life_event_reason out nocopy varchar2,
p_organization out nocopy varchar2,
p_postal_zip_range out nocopy varchar2,
p_reporting_group out nocopy varchar2,
p_plan_type out nocopy varchar2,
p_option out nocopy varchar2,
p_eligibility_profile out nocopy varchar2,
p_variable_rate_profile out nocopy varchar2,
p_legal_entity out nocopy varchar2,
p_payroll out nocopy varchar2,
p_status out nocopy varchar2) is
--
l_proc varchar2(80) := g_package||'.standard_header';
select bft.process_date,
hr.meaning,
hr1.meaning,
hr2.meaning,
/* Default null return columns using local variables
declared above */
nvl(ppf.full_name,l_all),
nvl(ppt.user_person_type,l_all),
nvl(pgm.name,l_all),
pbg.name,
nvl(pln.name,l_all),
decode(hr5.meaning,
null,
l_all,
hr5.meaning||
' '||
pln2.name||
' '||
pgm2.name||
' '||
epo.strt_dt||
' '||
epo.end_dt),
hr3.meaning,
hr4.meaning,
nvl(ff.formula_name,l_none),
nvl(ff2.formula_name,l_none),
nvl(ler.name,l_all),
nvl(org.name,l_all),
decode(rzr.from_value||'-'||rzr.to_value,
'-',
l_all,
rzr.from_value||'-'||rzr.to_value),
nvl(bnr.name,l_all),
nvl(ptp.name,l_all),
nvl(opt.name,l_all),
nvl(elp.name,l_all),
nvl(vpf.name,l_all),
nvl(org2.name,l_all),
nvl(pay.payroll_name,l_all),
conc.user_concurrent_program_name,
fnd1.meaning
from ben_benefit_actions bft,
hr_lookups hr,
hr_lookups hr1,
hr_lookups hr2,
hr_lookups hr3,
hr_lookups hr4,
hr_lookups hr5,
fnd_lookups fnd1,
per_people_f ppf,
per_person_types ppt,
ben_pgm_f pgm,
per_business_groups pbg,
ben_pl_f pln,
ff_formulas_f ff,
ff_formulas_f ff2,
ben_ler_f ler,
hr_all_organization_units_vl org,
ben_rptg_grp_v bnr,
ben_pl_typ_f ptp,
ben_opt_f opt,
ben_eligy_prfl_f elp,
ben_vrbl_rt_prfl_f vpf,
pay_payrolls_f pay,
ben_pstl_zip_rng_f rzr,
hr_all_organization_units_tl org2,
ben_popl_enrt_typ_cycl_f pop,
ben_enrt_perd epo,
ben_pl_f pln2,
ben_pgm_f pgm2,
fnd_concurrent_requests fnd,
fnd_concurrent_programs_tl conc
where fnd.request_id = p_concurrent_request_id
and conc.concurrent_program_id = fnd.concurrent_program_id
and conc.application_id = 805
and userenv('LANG') = conc.language --Bug 2394141
and bft.request_id = fnd.request_id
and hr.lookup_code = bft.mode_cd
-- bug fix 1560336
-- and hr.lookup_type = 'BEN_BENMNGLE_MD'
and hr.lookup_type = l_mode_cd_lookup_type
and hr1.lookup_code (+)= bft.derivable_factors_flag -- Bug 3517604 Added outer join
-- bug fix 1560336
-- and hr1.lookup_type = 'BEN_DTCT_TMPRL_LER_TYP'
and hr1.lookup_type (+)= l_drvbl_fctrs_lookup_type -- Bug 3517604 Added outer join
and hr2.lookup_code = bft.validate_flag
and hr2.lookup_type = 'YES_NO'
and hr3.lookup_code = bft.no_programs_flag
and hr3.lookup_type = 'YES_NO'
and hr4.lookup_code = bft.no_plans_flag
and hr4.lookup_type = 'YES_NO'
and hr5.lookup_code(+) = pop.enrt_typ_cycl_cd
and hr5.lookup_type(+) = 'BEN_ENRT_TYP_CYCL'
and fnd.status_code = fnd1.lookup_code
and fnd1.lookup_type = 'CP_STATUS_CODE'
and pop.popl_enrt_typ_cycl_id(+) = epo.popl_enrt_typ_cycl_id
and bft.process_date
between nvl(pop.effective_start_date,bft.process_date)
and nvl(pop.effective_end_date,bft.process_date)
and epo.enrt_perd_id(+) = bft.popl_enrt_typ_cycl_id
and pln2.pl_id(+) = pop.pl_id
and bft.process_date
between nvl(pln2.effective_start_date,bft.process_date)
and nvl(pln2.effective_end_date,bft.process_date)
and pgm2.pgm_id(+) = pop.pgm_id
and bft.process_date
between nvl(pgm2.effective_start_date,bft.process_date)
and nvl(pgm2.effective_end_date,bft.process_date)
and ppf.person_id(+) = bft.person_id
and bft.process_date
between nvl(ppf.effective_start_date,bft.process_date)
and nvl(ppf.effective_end_date,bft.process_date)
and pay.payroll_id(+) = bft.payroll_id
and bft.process_date
between nvl(pay.effective_start_date,bft.process_date)
and nvl(pay.effective_end_date,bft.process_date)
and ppt.person_type_id(+) = bft.person_type_id
and pgm.pgm_id(+) = bft.pgm_id
and bft.process_date
between nvl(pgm.effective_start_date,bft.process_date)
and nvl(pgm.effective_end_date,bft.process_date)
and pbg.business_group_id = bft.business_group_id
and org2.organization_id(+) = bft.legal_entity_id
and decode(org2.language,null,'1',org2.language)
= decode(org2.language,null,'1',userenv('LANG'))
and pln.pl_id(+) = bft.pl_id
and bft.process_date
between nvl(pln.effective_start_date,bft.process_date)
and nvl(pln.effective_end_date,bft.process_date)
and ler.ler_id(+) = bft.ler_id
and bft.process_date
between nvl(ler.effective_start_date,bft.process_date)
and nvl(ler.effective_end_date,bft.process_date)
and rzr.pstl_zip_rng_id(+) = bft.pstl_zip_rng_id
and bft.process_date
between nvl(rzr.effective_start_date,bft.process_date)
and nvl(rzr.effective_end_date,bft.process_date)
and ptp.pl_typ_id(+) = bft.pl_typ_id
and bft.process_date
between nvl(ptp.effective_start_date,bft.process_date)
and nvl(ptp.effective_end_date,bft.process_date)
and opt.opt_id(+) = bft.opt_id
and bft.process_date
between nvl(opt.effective_start_date,bft.process_date)
and nvl(opt.effective_end_date,bft.process_date)
and ff.formula_id(+) = bft.comp_selection_rl
and bft.process_date
between nvl(ff.effective_start_date,bft.process_date)
and nvl(ff.effective_end_date,bft.process_date)
and ff2.formula_id(+) = bft.person_selection_rl
and bft.process_date
between nvl(ff2.effective_start_date,bft.process_date)
and nvl(ff2.effective_end_date,bft.process_date)
and bnr.rptg_grp_id(+) = bft.rptg_grp_id
and elp.eligy_prfl_id(+) = bft.eligy_prfl_id
and bft.process_date
between nvl(elp.effective_start_date,bft.process_date)
and nvl(elp.effective_end_date,bft.process_date)
and vpf.vrbl_rt_prfl_id(+) = bft.vrbl_rt_prfl_id
and bft.process_date
between nvl(vpf.effective_start_date,bft.process_date)
and nvl(vpf.effective_end_date,bft.process_date)
and org.organization_id(+) = bft.organization_id
and bft.process_date
between nvl(org.date_from,bft.process_date)
and nvl(org.date_to,bft.process_date);
select conc.concurrent_program_name
from fnd_concurrent_requests fnd,
fnd_concurrent_programs conc
where fnd.request_id = p_concurrent_request_id
and conc.concurrent_program_id = fnd.concurrent_program_id
and conc.application_id = 805;
p_comp_object_selection_rule,
p_person_selection_rule,
p_life_event_reason,
p_organization,
p_postal_zip_range,
p_reporting_group,
p_plan_type,
p_option,
p_eligibility_profile,
p_variable_rate_profile,
p_legal_entity,
p_payroll,
p_concurrent_program_name,
p_status;
select benefit_action_id
from ben_benefit_actions bft
where bft.request_id = p_concurrent_request_id ;
select count(*) amount,ler.typ_cd
from ben_batch_ler_info bli,
ben_benefit_actions bft,
ben_ler_f ler,
ben_person_actions bpa
where bft.benefit_action_id = bli.benefit_action_id
and bft.benefit_action_id = l_c0.benefit_action_id
and bpa.benefit_action_id = bft.benefit_action_id
and ler.ler_id = bli.ler_id
and bft.process_date between ler.effective_start_date and ler.effective_end_date
and bli.tmprl_flag = 'Y'
and bpa.benefit_action_id = bli.benefit_action_id
and bpa.person_id = bli.person_id
and bpa.action_status_cd <> 'E'
group by ler.typ_cd;
SELECT /*+ BEN_BATCH_REPORTING.temporal_life_events.c1 */
COUNT(*) AMOUNT,LER.TYP_CD
FROM BEN_BATCH_LER_INFO BLI,
BEN_BENEFIT_ACTIONS BFT,
BEN_LER_F LER
WHERE BFT.BENEFIT_ACTION_ID = BLI.BENEFIT_ACTION_ID
AND BLI.BENEFIT_ACTION_ID = l_c0.benefit_action_id
AND LER.LER_ID = BLI.LER_ID
AND BFT.PROCESS_DATE BETWEEN LER.EFFECTIVE_START_DATE AND LER.EFFECTIVE_END_DATE
AND BLI.TMPRL_FLAG = 'Y'
and BLI.PERSON_ID in
(select BPA.PERSON_ID
from BEN_PERSON_ACTIONS BPA
where BPA.ACTION_STATUS_CD in ('P','U')
AND BPA.BENEFIT_ACTION_ID = l_c0.benefit_action_id
)
GROUP BY LER.TYP_CD;
select count(*)
from ben_person_actions pac,
ben_benefit_actions bft
where bft.benefit_action_id = pac.benefit_action_id
/* and bft.request_id = p_concurrent_request_id */
and bft.benefit_action_id = l_c0.benefit_action_id
and pac.action_status_cd = 'P'
and exists (select null
from ben_batch_ler_info bli
where bli.benefit_action_id = bft.benefit_action_id
and bli.person_id = pac.person_id
and bli.tmprl_flag = 'Y');
select count(*)
from ben_person_actions pac,
ben_benefit_actions bft
where bft.benefit_action_id = pac.benefit_action_id
/* and bft.request_id = p_concurrent_request_id */
and bft.benefit_action_id = l_c0.benefit_action_id
and pac.action_status_cd = 'P'
and not exists (select null
from ben_batch_ler_info bli
where bli.benefit_action_id = bft.benefit_action_id
and bli.person_id = pac.person_id
and bli.tmprl_flag = 'Y');
p_persons_selected out nocopy varchar2,
p_persons_processed out nocopy varchar2,
p_persons_unprocessed out nocopy varchar2,
p_persons_processed_succ out nocopy varchar2,
p_persons_errored out nocopy varchar2
) is
--
l_proc varchar2(80) := g_package||'.process_information';
select bpi.strt_dt,
bpi.end_dt,
bpi.strt_tm,
bpi.end_tm,
bpi.elpsd_tm,
bpi.per_slctd,
bpi.per_proc,
bpi.per_unproc,
bpi.per_proc_succ,
bpi.per_err
from ben_batch_proc_info bpi,
ben_benefit_actions bft
where bft.benefit_action_id = bpi.benefit_action_id
and bft.request_id = p_concurrent_request_id;
p_persons_selected,
p_persons_processed,
p_persons_unprocessed,
p_persons_processed_succ,
p_persons_errored;
select benefit_action_id
from ben_benefit_actions bft
where bft.request_id = p_concurrent_request_id ;
select replcd_flag,
crtd_flag,
not_crtd_flag,
stl_actv_flag,
clsd_flag,
open_and_clsd_flag,
bli.benefit_action_id,
bli.person_id
from ben_batch_ler_info bli,
ben_benefit_actions bft,
ben_person_actions bpa
where bft.benefit_action_id = bli.benefit_action_id
and bli.tmprl_flag = 'N'
and bft.benefit_action_id = l_c0.benefit_action_id
and bpa.benefit_action_id = bft.benefit_action_id
and bpa.person_id = bli.person_id
and bpa.action_status_cd <> 'E';
select null
from ben_batch_elctbl_chc_info epe,
ben_per_in_ler pil,
ben_elig_per_elctbl_chc epe1
where epe.benefit_action_id = l_ler_info.benefit_action_id
and epe.person_id= l_ler_info.person_id
and pil.person_id=epe.person_id
and epe1.per_in_ler_id=pil.per_in_ler_id
and epe1.elctbl_flag='Y'
and rownum=1;
select NULL
from ben_batch_ler_info bli
where bli.benefit_action_id = l_ler_info.benefit_action_id
and bli.person_id = l_ler_info.person_id
and exists (
select null
from ben_prtt_enrt_rslt_f pen
where pen.per_in_ler_id=bli.per_in_ler_id
and rownum=1
union
select null
from ben_prtt_rt_val prv
where prv.per_in_ler_id=bli.per_in_ler_id
and rownum=1
union
select null
from ben_prtt_prem_f ppe
where ppe.per_in_ler_id=bli.per_in_ler_id
and rownum=1
)
and rownum=1;
select replcd_flag,
crtd_flag,
not_crtd_flag,
stl_actv_flag,
clsd_flag,
open_and_clsd_flag,
clpsd_flag,
clsn_flag,
bli.benefit_action_id,
bli.person_id
from ben_batch_ler_info bli,
ben_benefit_actions bft,
/* Bug 2243050: Check whether the person has errored out nocopy */
ben_person_actions bpa
/* Bug 2243050: Check whether the person has errored out nocopy */
where bft.benefit_action_id = bli.benefit_action_id
and bli.tmprl_flag = 'N'
and bft.request_id = p_concurrent_request_id
/* Bug 2243050: Check whether the person has errored out nocopy */
and bpa.benefit_action_id = bli.benefit_action_id
and bpa.person_id = bli.person_id
and bpa.action_status_cd <> 'E';
select null
from ben_batch_elctbl_chc_info epe,
ben_per_in_ler pil,
ben_elig_per_elctbl_chc epe1
where epe.benefit_action_id = l_ler_info.benefit_action_id
and epe.person_id= l_ler_info.person_id
and pil.person_id=epe.person_id
and epe1.per_in_ler_id=pil.per_in_ler_id
and epe1.elctbl_flag='Y'
and rownum=1;
select NULL
from ben_batch_ler_info bli
where bli.benefit_action_id = l_ler_info.benefit_action_id
and bli.person_id = l_ler_info.person_id
and exists (
select null
from ben_prtt_enrt_rslt_f pen
where pen.per_in_ler_id=bli.per_in_ler_id
and rownum=1
union
select null
from ben_prtt_rt_val prv
where prv.per_in_ler_id=bli.per_in_ler_id
and rownum=1
union
select null
from ben_prtt_prem_f ppe
where ppe.per_in_ler_id=bli.per_in_ler_id
and rownum=1
)
and rownum=1;
select replcd_flag,
crtd_flag,
not_crtd_flag,
stl_actv_flag,
clsd_flag,
open_and_clsd_flag,
ler.name
from ben_batch_ler_info bli,
ben_benefit_actions bft,
ben_ler_f ler
where bft.benefit_action_id = bli.benefit_action_id
and bft.request_id = p_concurrent_request_id
and bli.ler_id=ler.ler_id
and trunc(sysdate) between
ler.effective_start_date and ler.effective_end_date
;