ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN C6E085D4718B51A9E05362FB09050512Y select org.name org_name, hrl.meaning qal_evnt, pcce.cobra_coverage_enrollment_id enrol_id, per.person_id per_id from hr_lookups hrl, per_cobra_cov_enrollments pcce, per_business_groups org, per_assignments_f asg, per_people_f per where hrl.lookup_type = 'US_COBRA_EVENT' and hrl.lookup_code = pcce.qualifying_event and pcce.assignment_id = asg.assignment_id and pcce.qualifying_date = (select nvl(:p_qualifying_date ,max(pc2.qualifying_date)) from per_cobra_cov_enrollments pc2 where pc2.assignment_id = asg.assignment_id and pc2.qualifying_date <= nvl(:p_qualifying_date, :LP_SESSION_DATE)) and asg.person_id = per.person_id and pcce.qualifying_date between asg.effective_start_date and asg.effective_end_date and org.organization_id = per.business_group_id and per.person_id = nvl(:p_person_id,per.person_id) --and :lp_session_date between per.effective_start_date and and :LP_SESSION_DATE between per.effective_start_date and per.effective_end_date union select org.name org_name, hrl.meaning qal_evnt, pcce.cobra_coverage_enrollment_id enrol_id, con.contact_person_id per_id from hr_lookups hrl, per_cobra_cov_enrollments pcce, per_business_groups org, per_contact_relationships con, per_cobra_dependents_f pcs, per_assignments_f asg, per_people_f per where hrl.lookup_type = 'US_COBRA_EVENT' and hrl.lookup_code = pcce.qualifying_event and pcce.cobra_coverage_enrollment_id = pcs.cobra_coverage_enrollment_id and con.contact_relationship_id = pcs.contact_relationship_id and :lp_session_date between pcs.effective_start_date and pcs.effective_end_date and pcce.assignment_id = asg.assignment_id and pcce.qualifying_date = (select nvl(:p_qualifying_date ,max(pc2.qualifying_date)) from per_cobra_cov_enrollments pc2 where pc2.assignment_id = asg.assignment_id and pc2.qualifying_date <= nvl(:p_qualifying_date, :LP_SESSION_DATE)) and asg.person_id = per.person_id and pcce.qualifying_date between asg.effective_start_date and asg.effective_end_date and org.organization_id = per.business_group_id and per.person_id = nvl(:p_person_id,per.person_id) and :LP_SESSION_DATE between per.effective_start_date and per.effective_end_date PER PERRPCNL_XML US PER COBRA Notification Letter Imported from BI Publisher Description: COBRA Notification Letter Application: Human Resources Source: COBRA Notification Letter (XML) Short Name: PERRPCNL_XML DB package: PER_PERUSCNL_XMLP_PKG BI Publisher :c_business_group_name :c_employee_name :c_qualifying_date :lp_session_date :not_date :p_conc_request_id :p_person_id :p_qualifying_date :p_report_title :p_session_date :per_business_group_id 1 -30 :per_business_group_id Number fnd_profile.value('PER_BUSINESS_GROUP_ID') Y US Business Group Id Business Group Id 2 -20 :p_session_date Date select fnd_date.date_to_displaydate(effective_date) from fnd_sessions where session_id = userenv('sessionid') Y US PER_DATES_STANDARD Session Date 3 10 :p_person_id LOV Oracle PER_EMPLOYEES_COBRA Y Y select person_id id, full_name value, null description from per_people_f ppf where business_group_id=fnd_global.per_business_group_id and ((to_date(:$flex$.per_dates_standard,'YYYY/MM/DD HH24:MI:SS') between effective_start_date and effective_end_date and current_employee_flag='Y') or (nvl(current_employee_flag,'N')='N' and person_id=(select distinct(pps.person_id) from per_periods_of_service pps where pps.person_id=ppf.person_id and to_date(:$flex$.per_dates_standard,'YYYY/MM/DD HH24:MI:SS') between pps.actual_termination_date+1 and add_months(pps.actual_termination_date,18)))) order by value,description Y US Employee Name Employee Name 4 20 :p_qualifying_date Date US Qualifying Date Qualifying Date