ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN C6E085D4718951A9E05362FB09050512Y 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_cobra_coverage_statuses ccs_not, per_cobra_coverage_statuses ccs, per_people_f pp1, per_contact_relationships pcs, per_people_f per, per_assignments_f asg, pay_element_types_f et, per_cobra_coverage_benefits_f ccb, per_cobra_cov_enrollments cce WHERE cce.qualifying_date BETWEEN NVL(:p_ql_date_from,TO_DATE('01-01-1901','DD-MM-YYYY')) AND NVL(:p_ql_date_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 NVL(cce.coverage_start_date, :p_session_date1) BETWEEN NVL(:p_cov_start_from1,TO_DATE('01-01-1901','DD-MM-YYYY')) AND NVL(:p_cov_start_to1, :C_END_OF_TIME) AND NVL(cce.coverage_end_date, :p_session_date1) BETWEEN NVL(:p_cov_end_from1,TO_DATE('01-01-1901','DD-MM-YYYY')) AND NVL(:p_cov_end_to1, :C_END_OF_TIME) AND ccb.cobra_coverage_enrollment_id = cce.cobra_coverage_enrollment_id AND :p_session_date1 BETWEEN ccb.effective_start_date AND ccb.effective_end_date AND ccb.element_type_id = NVL(:p_benefit_plan_type_id, ccb.element_type_id) AND ccb.accept_reject_flag = 'ACC' AND et.element_type_id = ccb.element_type_id AND ET.PROCESSING_TYPE = 'R' AND :p_session_date1 BETWEEN et.effective_start_date AND et.effective_end_date 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 per.person_id = asg.person_id AND :p_session_date1 BETWEEN per.effective_start_date AND per.effective_end_date AND pcs.CONTACT_RELATIONSHIP_ID(+)= cce.CONTACT_RELATIONSHIP_ID AND pp1.person_id (+) = pcs.contact_person_id AND pp1.effective_start_date (+) <= :p_session_date1 AND pp1.effective_end_date (+) >= :p_session_date1 AND ccs.cobra_coverage_enrollment_id = cce.cobra_coverage_enrollment_id AND ( :p_cobra_status IS NULL OR ( :p_cobra_status IS NOT NULL AND ccs.cobra_coverage_status_type = :p_cobra_status ) ) 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_date1 ) AND ccs_not.cobra_coverage_enrollment_id(+) = cce.cobra_coverage_enrollment_id AND ccs_not.cobra_coverage_status_type (+) = 'NOT' 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_cobra_coverage_statuses ccs_not, per_cobra_coverage_statuses ccs, per_people_f pp1, per_contact_relationships pcs, per_people_f per, per_assignments_f asg, pay_element_types_f et, per_cobra_coverage_benefits_f ccb, per_cobra_cov_enrollments cce WHERE cce.qualifying_date BETWEEN NVL(:p_ql_date_from,TO_DATE('01-01-1901','DD-MM-YYYY')) AND NVL(:p_ql_date_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 NVL(cce.coverage_start_date, :p_session_date1) BETWEEN NVL(:p_cov_start_from1,TO_DATE('01-01-1901','DD-MM-YYYY')) AND NVL(:p_cov_start_to1, :C_END_OF_TIME) AND NVL(cce.coverage_end_date, :p_session_date1) BETWEEN NVL(:p_cov_end_from1,TO_DATE('01-01-1901','DD-MM-YYYY')) AND NVL(:p_cov_end_to1, :C_END_OF_TIME) AND ccb.cobra_coverage_enrollment_id = cce.cobra_coverage_enrollment_id AND :p_session_date1 BETWEEN ccb.effective_start_date AND ccb.effective_end_date AND ccb.element_type_id = NVL(:p_benefit_plan_type_id, ccb.element_type_id) AND ccb.accept_reject_flag = 'ACC' AND et.element_type_id = ccb.element_type_id AND ET.PROCESSING_TYPE = 'R' AND :p_session_date1 BETWEEN et.effective_start_date AND et.effective_end_date 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 per.person_id = asg.person_id AND :p_session_date1 BETWEEN per.effective_start_date AND per.effective_end_date AND pcs.CONTACT_RELATIONSHIP_ID(+)= cce.CONTACT_RELATIONSHIP_ID AND pp1.person_id (+) = pcs.contact_person_id AND pp1.effective_start_date (+) <= :p_session_date1 AND pp1.effective_end_date (+) >= :p_session_date1 AND ccs.cobra_coverage_enrollment_id = cce.cobra_coverage_enrollment_id AND ( :p_cobra_status IS NULL OR ( :p_cobra_status IS NOT NULL AND ccs.cobra_coverage_status_type = :p_cobra_status ) ) 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_date1 ) AND ccs_not.cobra_coverage_enrollment_id(+) = cce.cobra_coverage_enrollment_id AND ccs_not.cobra_coverage_status_type (+) = 'NOT' 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 PER PERRPCCR_XML US PER COBRA Coverage Imported from BI Publisher Description: COBRA Coverage Report Application: Human Resources Source: COBRA Coverage Report (XML) Short Name: PERRPCCR_XML DB package: PER_PERUSCCR_XMLP_PKG BI Publisher :c_benefit_plan_name :c_business_group_name :c_cobra_status :c_end_of_time :c_org_structure_name :c_parent_organization_name :c_qualifying_event :c_report_subtitle :p_ben_plan_type_id :p_benefit_plan_type_id :p_business_group_id :p_cobra_status :p_conc_request_id :p_cov_end_from :p_cov_end_from1 :p_cov_end_to1 :p_cov_start_from :p_cov_start_from1 :p_cov_start_to :p_cov_start_to1 :p_coverage_end_to :p_org_structure_version_id :p_parent_organization_id :p_ql_date_from :p_ql_date_from1 :p_ql_date_to :p_ql_date_to1 :p_qualifying_event :p_session_date :p_session_date1 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_ql_date_from Date US Qualifying Date From Qualifying Date From 8 60 :p_ql_date_to Date US Qualifying Date To Qualifying Date To 9 70 :p_cobra_status LOV Oracle HR_US_COBRA_STATUS Y select l.lookup_code id, l.meaning value, null description from hr_lookups l where l.lookup_type='US_COBRA_STATUS' order by l.meaning US COBRA Status COBRA Status 10 80 :p_ben_plan_type_id LOV Oracle HR_US_BENEFIT_PLAN_TYPE_ID Y select et.element_type_id id, et.element_name value, bc.benefit_classification_name description from ben_benefit_classifications bc,pay_element_types et where bc.benefit_classification_id= et.benefit_classification_id and et.business_group_id=:$flex$.per_id order by et.element_name US Benefit Plan Benefit Plan 11 90 :p_cov_start_from Date US Coverage Start From Coverage Start From 12 100 :p_cov_start_to Date US Coverage Start To Coverage Start To 13 110 :p_cov_end_from Date US Coverage End From Coverage End From 14 120 :p_coverage_end_to Date US Coverage End To Coverage End To