ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN C6E085D4718C51A9E05362FB09050512 select org.name org_name, pcce.cobra_coverage_enrollment_id enrol_id, decode(pcce.CONTACT_RELATIONSHIP_ID,null,:p_person_id, pcs.contact_person_id) per_id from per_cobra_cov_enrollments pcce, per_organization_units org, per_contact_relationships pcs, per_assignments_f asg, per_people_f per where pcce.CONTACT_RELATIONSHIP_ID = pcs.CONTACT_RELATIONSHIP_ID (+) and pcce.assignment_id = asg.assignment_id and pcce.qualifying_date = (select nvl(:p_qual_date,max(pc2.qualifying_date)) from per_cobra_cov_enrollments pc2 where pc2.assignment_id = asg.assignment_id and pc2.qualifying_date <= nvl(:p_qual_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 = asg.organization_id and org.business_group_id = :p_business_group_id and per.person_id = :p_person_id and :P_SESSION_DATE between per.effective_start_date and per.effective_end_date Y PER PERRPCPE_XML US PER COBRA Grace Period Expiration Letter Imported from BI Publisher Description: COBRA Grace Period Expiration Letter Application: Human Resources Source: COBRA Grace Period Expiration Letter (XML) Short Name: PERRPCPE_XML DB package: PER_PERUSCPE_XMLP_PKG BI Publisher :c_business_group_name :c_employee_name :c_letter_date :c_qualifying_date :lp_session_date :p_business_group_id :p_conc_request_id :p_person_id :p_qual_date :p_report_title :p_session_date 1 -30 :p_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_qual_date Date US Qualifying Date Qualifying Date :$flex$.per_dates_standard PER_DATES_STANDARD Y Employee Name