BEN Close Action Items Audit Log

select
a.person_id                                             r_person_id,
'Program: ' || pgm.name 		r_pgm_name,
'Plan: ' || pl.name 			r_pl_name,
'Option: ' || opt.name 		r_opt_name,
'Bnft amt: ' || a.dflt_val   		r_amt,
--'Cvg Start Dt:' ||  a.enrt_cvg_strt_dt  	r_cvg_strt_dt,
'Cvg Start Dt: ' ||  to_char(a.enrt_cvg_strt_dt,'DD-MON-YY')  	r_cvg_strt_dt,
--'Cvg End Dt:' ||   a.enrt_cvg_thru_dt 	r_cvg_thru_dt,
'Cvg End Dt: ' ||   to_char(a.enrt_cvg_thru_dt,'DD-MON-YY') 	r_cvg_thru_dt,
a.val                                                         r_prtt_enrt_rslt_id,
'Summary Actn: ' || decode(substr(a.actn_cd,1,1),
                                          'U', 'Enrollment unsuspended',
                                          'D', 'Suspended enrollment deleted',
                                          'N', 'No action',
                                          'No action')     r_summary_actn
from ben_batch_rate_info a,
        ben_benefit_actions c,
        ben_pgm_f pgm,
        ben_pl_f pl,
        ben_oipl_f oipl,
       ben_opt_f opt
where c.request_id = :P_CONCURRENT_REQUEST_ID
and  c.benefit_action_id = a.benefit_action_id
and  a.pgm_id =  pgm.pgm_id(+)
and c.process_date between
          nvl(pgm.effective_start_date, c.process_date) and nvl(pgm.effective_end_date, c.process_date)
and  a.pl_id =  pl.pl_id(+)
and c.process_date between
          nvl(pl.effective_start_date, c.process_date) and nvl(pl.effective_end_date, c.process_date)
and  a.oipl_id =  oipl.oipl_id(+)
and c.process_date between
          nvl(oipl.effective_start_date, c.process_date) and nvl(oipl.effective_end_date, c.process_date)
and  oipl.opt_id =  opt.opt_id(+)
and c.process_date between
          nvl(opt.effective_start_date, c.process_date) and nvl(opt.effective_end_date, c.process_date)
     --  ;
--and a.person_id=:PERSON_ID
and a.person_id=:PERSON_ID_MAS

select a.person_id r_person_id, 'Program: ' || pgm.name r_pgm_name, 'Plan: ' || pl.name r_pl_name, 'Option: ' || opt.name r_opt_name, 'Bnft amt: ' || a.dflt_val r_amt, --'Cvg Start Dt:' || a.enrt_cvg_strt_dt r_cvg_strt_dt, 'Cvg Start Dt: ' || to_char(a.enrt_cvg_strt_dt,'DD-MON-YY') r_cvg_strt_dt, --'Cvg End Dt:' || a.enrt_cvg_thru_dt r_cvg_thru_dt, 'Cvg End Dt: ' || to_char(a.enrt_cvg_thru_dt,'DD-MON-YY') r_cvg_thru_dt, a.val r_prtt_enrt_rslt_id, 'Summary Actn: ' || decode(substr(a.actn_cd,1,1), 'U', 'Enrollment unsuspended', 'D', 'Suspended enrollment deleted', 'N', 'No action', 'No action') r_summary_actn from ben_batch_rate_info a, ben_benefit_actions c, ben_pgm_f pgm, ben_pl_f pl, ben_oipl_f oipl, ben_opt_f opt where c.request_id = :P_CONCURRENT_REQUEST_ID and c.benefit_action_id = a.benefit_action_id and a.pgm_id = pgm.pgm_id(+) and c.process_date between nvl(pgm.effective_start_date, c.process_date) and nvl(pgm.effective_end_date, c.process_date) and a.pl_id = pl.pl_id(+) and c.process_date between nvl(pl.effective_start_date, c.process_date) and nvl(pl.effective_end_date, c.process_date) and a.oipl_id = oipl.oipl_id(+) and c.process_date between nvl(oipl.effective_start_date, c.process_date) and nvl(oipl.effective_end_date, c.process_date) and oipl.opt_id = opt.opt_id(+) and c.process_date between nvl(opt.effective_start_date, c.process_date) and nvl(opt.effective_end_date, c.process_date) -- ; --and a.person_id=:PERSON_ID and a.person_id=:PERSON_ID_MAS