PER COBRA Coverage

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