PER COBRA Payments

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