PER COBRA Payments

Description
Categories: BI Publisher, Human Resources
Columns: Org Id, Business Group Id ...
Application: Human Resources
Source: COBRA Payments Report (XML)
Short Name: PERRPCPR_XML
DB package: PER_PERUSCPR_XMLP_PKG
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