ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN C6E085D4718D51A9E05362FB09050512 select p.organization_id org_id, p.business_group_id from per_organization_units p where ( p.organization_id = :p_parent_organization_id OR :p_parent_organization_id IS NULL ) and p.business_Group_id = :p_business_Group_id AND EXISTS ( SELECT 'x' FROM hr_lookups hrl, hr_lookups hrl2, hr_lookups hrl3, per_people_f peop1, per_contact_relationships pcs, per_people_f peop, per_assignments_f asg, per_cobra_coverage_statuses ccs, per_sched_cobra_payments scp, per_cobra_cov_enrollments cce WHERE cce.qualifying_date BETWEEN NVL(:p_q_from,TO_DATE('01-01-1901','DD-MM-YYYY')) AND NVL(:p_q_to, :C_END_OF_TIME) AND ( :p_qualifying_event IS NULL OR (:p_qualifying_event IS NOT NULL AND cce.qualifying_event = :p_qualifying_event) ) AND scp.cobra_coverage_enrollment_id = cce.cobra_coverage_enrollment_id AND scp.date_due BETWEEN NVL(:p_due_from,TO_DATE('01-01-1901','DD-MM-YYYY')) AND NVL(:p_due_to, :C_END_OF_TIME) AND scp.date_received BETWEEN NVL(:p_rcd_from,TO_DATE('01-01-1901','DD-MM-YYYY')) AND NVL(:p_rcd_to,:C_END_OF_TIME) AND ccs.cobra_coverage_enrollment_id = cce.cobra_coverage_enrollment_id AND ccs.cobra_coverage_status_id = ( SELECT MAX(pc2.cobra_coverage_status_id) FROM per_cobra_coverage_statuses pc2 WHERE pc2.cobra_coverage_enrollment_id = cce.cobra_coverage_enrollment_id AND pc2.effective_date <= :p_session_date ) AND ( :p_cobra_status IS NULL OR ( :p_cobra_status IS NOT NULL AND ccs.cobra_coverage_status_type = :p_cobra_status ) ) AND asg.assignment_id = cce.assignment_id AND asg.organization_id = p.organization_id AND cce.qualifying_date BETWEEN asg.effective_start_date AND asg.effective_end_date AND peop.person_id = asg.person_id AND :p_session_date BETWEEN peop.effective_start_date AND peop.effective_end_date AND pcs.contact_relationship_id(+)= cce.CONTACT_RELATIONSHIP_ID AND peop1.person_id (+) = pcs.contact_person_id AND peop1.effective_start_date (+) <= :p_session_date AND peop1.effective_end_date (+) >= :p_session_date AND hrl.lookup_code (+) = pcs.contact_type AND hrl.lookup_type (+) = 'CONTACT' AND hrl2.lookup_code = cce.qualifying_event AND hrl2.lookup_type = 'US_COBRA_EVENT' AND hrl3.lookup_code = ccs.cobra_coverage_status_type AND hrl3.lookup_type = 'US_COBRA_STATUS' ) UNION select str.organization_id_child org_id, str.organization_id_child from per_org_structure_elements str where str.business_group_id = :p_business_group_id AND EXISTS ( SELECT 'x' FROM hr_lookups hrl, hr_lookups hrl2, hr_lookups hrl3, per_people_f peop1, per_contact_relationships pcs, per_people_f peop, per_assignments_f asg, per_cobra_coverage_statuses ccs, per_sched_cobra_payments scp, per_cobra_cov_enrollments cce WHERE cce.qualifying_date BETWEEN NVL(:p_q_from,TO_DATE('01-01-1901','DD-MM-YYYY')) AND NVL(:p_q_to, :C_END_OF_TIME) AND ( :p_qualifying_event IS NULL OR (:p_qualifying_event IS NOT NULL AND cce.qualifying_event = :p_qualifying_event) ) AND scp.cobra_coverage_enrollment_id = cce.cobra_coverage_enrollment_id AND scp.date_due BETWEEN NVL(:p_due_from,TO_DATE('01-01-1901','DD-MM-YYYY')) AND NVL(:p_due_to, :C_END_OF_TIME) AND scp.date_received BETWEEN NVL(:p_rcd_from,TO_DATE('01-01-1901','DD-MM-YYYY')) AND NVL(:p_rcd_to, :C_END_OF_TIME) AND ccs.cobra_coverage_enrollment_id = cce.cobra_coverage_enrollment_id AND ccs.cobra_coverage_status_id = ( SELECT MAX(pc2.cobra_coverage_status_id) FROM per_cobra_coverage_statuses pc2 WHERE pc2.cobra_coverage_enrollment_id = cce.cobra_coverage_enrollment_id AND pc2.effective_date <= :p_session_date ) AND ( :p_cobra_status IS NULL OR ( :p_cobra_status IS NOT NULL AND ccs.cobra_coverage_status_type = :p_cobra_status ) ) AND asg.assignment_id = cce.assignment_id AND asg.organization_id = str.organization_id_child AND cce.qualifying_date BETWEEN asg.effective_start_date AND asg.effective_end_date AND peop.person_id = asg.person_id AND :p_session_date BETWEEN peop.effective_start_date AND peop.effective_end_date AND pcs.contact_relationship_id(+)= cce.CONTACT_RELATIONSHIP_ID AND peop1.person_id (+) = pcs.contact_person_id AND peop1.effective_start_date (+) <= :p_session_date AND peop1.effective_end_date (+) >= :p_session_date AND hrl.lookup_code (+) = pcs.contact_type AND hrl.lookup_type (+) = 'CONTACT' AND hrl2.lookup_code = cce.qualifying_event AND hrl2.lookup_type = 'US_COBRA_EVENT' AND hrl3.lookup_code = ccs.cobra_coverage_status_type AND hrl3.lookup_type = 'US_COBRA_STATUS' ) connect by str.organization_id_parent = prior str.organization_id_child and str.org_structure_version_id = :p_org_structure_version_id start with str.organization_id_parent = :p_parent_organization_id and str.org_structure_version_id = :p_org_structure_version_id Y PER PERRPCPR_XML US PER COBRA Payments Imported from BI Publisher Description: COBRA Payments Report Application: Human Resources Source: COBRA Payments Report (XML) Short Name: PERRPCPR_XML DB package: PER_PERUSCPR_XMLP_PKG BI Publisher :c_business_group_name :c_end_of_time :c_org_structure_name :c_parent_organization_name :c_qualifying_event :c_report_subtitle :p_business_group_id :p_cobra_status :p_conc_request_id :p_due_from :p_due_to :p_org_structure_version_id :p_parent_organization_id :p_q_from :p_q_to :p_qualifying_event :p_rcd_from :p_rcd_to :p_session_date 1 -30 :p_business_group_id Number fnd_profile.value('PER_BUSINESS_GROUP_ID') Y US PER_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_organization_structure_id LOV Oracle PER_ORG_STRUCTS Y select s.organization_structure_id id, s.name value, null description from per_organization_structures s where s.business_group_id=fnd_global.per_business_group_id order by value,description US Organization Structure Organization Structure 4 20 :p_org_structure_version_id LOV Oracle PER_ORG_STRUCT_VERS Y select v.org_structure_version_id id, to_char(v.version_number) value, fnd_date.date_to_chardate(v.date_from) || decode(v.date_to,null,null,' -> '||fnd_date.date_to_chardate(v.date_to)) description from per_org_structure_versions v where v.organization_structure_id=:$flex$.per_org_structs order by v.date_from desc US Version Version 5 30 :p_parent_organization_id LOV Oracle PER_ORG_STRUCTURE_ELEMENTS Y select o.organization_id id, o.name value, null description from hr_organization_units o where o.organization_id in ( select p.organization_id_parent from per_org_structure_elements p where p.org_structure_version_id=:$flex$.per_org_struct_vers union select c.organization_id_child from per_org_structure_elements c where c.org_structure_version_id=:$flex$.per_org_struct_vers ) order by o.name US Parent Organization Parent Organization 6 40 :p_qualifying_event LOV Oracle HR_US_COBRA_QUALIFYING_EVENT Y select cqe.qualifying_event id, l.meaning value, null description from hr_lookups l,per_cobra_qfying_events cqe where cqe.qualifying_event=l.lookup_code and l.lookup_type='US_COBRA_EVENT' and (cqe.business_group_id=:$flex$.per_id or cqe.business_group_id is null) and to_date(:$flex$.per_dates_standard,'YYYY/MM/DD HH24:MI:SS') between cqe.effective_start_date and cqe.effective_end_date order by l.meaning US Qualifying Event Qualifying Event 7 50 :p_q_from Date US Qualifying Date From Qualifying Date From 8 60 :p_q_to Date US Qualifying Date To Qualifying Date To 9 70 :p_due_from Date US Due Date From Due Date From 10 80 :p_due_to Date US Due Date To Due Date To 11 90 :p_rcd_from Date US Date Received From Date Received From 12 100 :p_rcd_to Date US Date Received To Date Received To :$flex$.per_dates_standard PER_DATES_STANDARD Y Qualifying Event :$flex$.per_id PER_ID Qualifying Event :$flex$.per_org_struct_vers Version Parent Organization :$flex$.per_org_structs Organization Structure Version