BEN Participation Error Detail Report by Error Type

select a.national_identifier NATIONAL_IDENTIFIER,
          a.error_message_code ERROR_MESSAGE_CODEM2,
          b.full_name FULL_NAME,
         a.text TEXT,
         decode(a.rep_typ_cd,'WARNING',1,0) reptype
from  ben_reporting a,
         per_all_people_f b
where  a.benefit_action_id = :p_benefit_action_id
and      a.rep_typ_cd in ('ERROR','WARNING')
and      a.person_id = b.person_id
and      a.error_message_code = :ERROR_MESSAGE_CODE
and      :p_process_date  between b.effective_start_date  and  b.effective_end_date
order    by a.national_identifier asc