PER COBRA Expiration Letter

Description
Categories: BI Publisher, Human Resources
Application: Human Resources
Source: COBRA Expiration Letter (XML)
Short Name: PERRPCNE_XML
DB package: PER_PERUSCNE_XMLP_PKG
SELECT
       org.name                                             org_name,
       cce.cobra_coverage_enrollment_id                    enrol_id,
       DECODE(cce.contact_relationship_id,null,:p_person_id,
                                   pcs.contact_person_id)   per_id
FROM
       per_organization_units                               org,
       per_cobra_cov_enrollments                            cce,
       per_contact_relationships                                         pcs,
       per_assignments_f                                    asg,
       per_people_f                                         per
WHERE
       org.organization_id              = asg.organization_id
AND
       cce.contact_relationship_id     = pcs.contact_relationship_id (+) AND
       cce.assignment_id               = asg.assignment_id               AND
       cce.qualifying_date             =
        ( SELECT NVL(:p_qual_date,MAX(pc2.qualifying_date))
           FROM  per_cobra_cov_enrollments pc2
           WHERE pc2.assignment_id   = asg.assignment_id
            and pc2.qualifying_date <= NVL(:p_qual_date,:lp_session_date)
        )
AND
       asg.person_id                    = per.person_id
AND
       cce.qualifying_date BETWEEN asg.effective_start_date AND
                               asg.effective_end_date
AND
       per.person_id                    = :p_person_id AND
      :lp_session_date BETWEEN per.effective_start_date AND
      per.effective_end_date
Parameter Name SQL text Validation
PER_DATES_STANDARD
 
Date
Business Group Id
 
Number
Qualifying Date
 
Date
Employee Name
 
LOV Oracle