BEN Back-out Life Events Audit Log
Description
Categories: BI Publisher
Application: Advanced Benefits
Source: Back-out Life Events Audit Log (XML)
Short Name: BENBOAUD_XML
DB package: BEN_BENBOAUD_XMLP_PKG
Source: Back-out Life Events Audit Log (XML)
Short Name: BENBOAUD_XML
DB package: BEN_BENBOAUD_XMLP_PKG
select ppf.full_name person_full_name, ler.name||'('||ler.ler_id||')' life_event_name, ppf.person_id person_id_Master, ppf.national_identifier, ppf.date_of_birth, hr_general.decode_lookup('SEX',ppf.sex) gender, hr_general.decode_lookup('MAR_STATUS',ppf.marital_status) marital_status, ppt.user_person_type person_type, pps.date_start hire_date, pad.postal_code postal_code, job.name job_name, pos.name position_name, gra.name grade_name, pap.payroll_name, loc.location_code, org.name org_name, org2.name legal_entity_name, bng.name benefit_group_name from per_all_people_f ppf, ben_benfts_grp bng, per_periods_of_service pps, per_addresses pad, per_all_assignments_f paf, per_jobs_vl job, hr_all_positions_f_vl pos, per_grades_vl gra, pay_all_payrolls_f pap, hr_all_organization_units_vl org, hr_all_organization_units_vl org2, hr_soft_coding_keyflex soft, hr_locations loc, per_person_types ppt, ben_ler_f ler, ben_benefit_actions bac, ben_reporting rpt where bac.request_id = :p_concurrent_request_id and bac.business_group_id = nvl(ler.business_group_id,bac.business_group_id) and ler.ler_id(+) = rpt.ler_id and paf.person_id(+) = ppf.person_id and nvl(paf.primary_flag,'Y') = 'Y' and paf.assignment_type <> 'C' and bac.process_date between nvl(paf.effective_start_date,bac.process_date) and nvl(paf.effective_end_date,bac.process_date) and paf.job_id = job.job_id(+) and bac.process_date between nvl(job.date_from,bac.process_date) and nvl(job.date_to,bac.process_date) and paf.position_id = pos.position_id(+) and bac.process_date between nvl(pos.effective_start_date,bac.process_date) and nvl(pos.effective_end_date,bac.process_date) and paf.grade_id = gra.grade_id(+) and bac.process_date between nvl(gra.date_from,bac.process_date) and nvl(gra.date_to,bac.process_date) and paf.payroll_id = pap.payroll_id(+) and bac.process_date between nvl(pap.effective_start_date,bac.process_date) and nvl(pap.effective_end_date,bac.process_date) and paf.location_id = loc.location_id(+) and paf.organization_id = org.organization_id(+) and bac.process_date between nvl(org.date_from,bac.process_date) and nvl(org.date_to,bac.process_date) and paf.soft_coding_keyflex_id = soft.soft_coding_keyflex_id(+) and org2.organization_id(+) = soft.segment1 and bac.process_date between nvl(ler.effective_start_date,bac.process_date) and nvl(ler.effective_end_date,bac.process_date) and rpt.person_id = ppf.person_id and bac.business_group_id = ppf.business_group_id +0 and bac.process_date between ppf.effective_start_date and ppf.effective_end_date and ppf.benefit_group_id = bng.benfts_grp_id(+) and ppf.person_type_id = ppt.person_type_id and ppt.business_group_id = ppf.business_group_id and pps.person_id(+) = ppf.person_id and ppf.business_group_id = nvl(pps.business_group_id, ppf.business_group_id) and pad.person_id(+) = ppf.person_id and nvl(pad.primary_flag,'Y') = 'Y' and bac.process_date between nvl(pad.date_from,bac.process_date) and nvl(pad.date_to,bac.process_date) and bac.benefit_action_id= rpt.benefit_action_id and rpt.rep_typ_cd='LFBO' order by ppf.full_name |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Concurrent Request ID | LOV Oracle |