PER COBRA Payments

Description
Categories: BI Publisher, Human Resources
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 ( :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
Parameter Name SQL text Validation
PER_DATES_STANDARD
 
Date
PER_ID
 
Number
Date Received To
 
Date
Date Received From
 
Date
Due Date To
 
Date
Due Date From
 
Date
Qualifying Date To
 
Date
Qualifying Date From
 
Date
Qualifying Event
 
LOV Oracle
Parent Organization
 
LOV Oracle
Version
 
LOV Oracle
Organization Structure
 
LOV Oracle