PER COBRA Notification Letter

➜ Back to Library
Description
Categories: BI Publisher
Imported from BI Publisher
Description: COBRA Notification Letter
Application: Human Resources
Source: COBRA Notification Letter (XML)
Short Name: PERRPCNL_XML
DB package: PER_PERUSCNL_XMLP_PKG
Run PER COBRA Notification Letter and other Oracle EBS reports with Blitz Report™ on our demo environment
select org.name                                             org_name,
       hrl.meaning                                          qal_evnt,
       pcce.cobra_coverage_enrollment_id                    enrol_id,
       per.person_id per_id
from   hr_lookups                                         hrl,
       per_cobra_cov_enrollments                 pcce,
       per_business_groups                           org,
       per_assignments_f                               asg,
       per_people_f                                         per
where  hrl.lookup_type                  = 'US_COBRA_EVENT'
and    hrl.lookup_code                  = pcce.qualifying_event
and    pcce.assignment_id               = asg.assignment_id
and    pcce.qualifying_date             =
        (select nvl(:p_qualifying_date ,max(pc2.qualifying_date))
           from per_cobra_cov_enrollments pc2
          where pc2.assignment_id       = asg.assignment_id
            and pc2.qualifying_date    <= nvl(:p_qualifying_date, :LP_SESSION_DATE))
and    asg.person_id                    = per.person_id
and    pcce.qualifying_date  between asg.effective_start_date and
                                asg.effective_end_date
and    org.organization_id              = per.business_group_id
and    per.person_id                    = nvl(:p_person_id,per.person_id)
--and    :lp_session_date between per.effective_start_date and
and    :LP_SESSION_DATE between per.effective_start_date and
                               per.effective_end_date
union
select org.name                                             org_name,
       hrl.meaning                                          qal_evnt,
       pcce.cobra_coverage_enrollment_id                    enrol_id,
       con.contact_person_id per_id
from   hr_lookups                                         hrl,
       per_cobra_cov_enrollments                 pcce,
       per_business_groups                           org,
       per_contact_relationships con,
       per_cobra_dependents_f pcs,
       per_assignments_f                               asg,
       per_people_f                                         per
where  hrl.lookup_type                  = 'US_COBRA_EVENT'
and    hrl.lookup_code                  = pcce.qualifying_event
and    pcce.cobra_coverage_enrollment_id = pcs.cobra_coverage_enrollment_id
and    con.contact_relationship_id = pcs.contact_relationship_id
and    :lp_session_date
          between pcs.effective_start_date
          and         pcs.effective_end_date
and    pcce.assignment_id               = asg.assignment_id
and    pcce.qualifying_date             =
        (select nvl(:p_qualifying_date ,max(pc2.qualifying_date))
           from per_cobra_cov_enrollments pc2
          where pc2.assignment_id       = asg.assignment_id
            and pc2.qualifying_date    <= nvl(:p_qualifying_date, :LP_SESSION_DATE))
and    asg.person_id                    = per.person_id
and    pcce.qualifying_date  between asg.effective_start_date and
                                asg.effective_end_date
and    org.organization_id              = per.business_group_id
and    per.person_id                    = nvl(:p_person_id,per.person_id)
and    :LP_SESSION_DATE between per.effective_start_date and
                               per.effective_end_date
Parameter Name SQL text Validation
Employee Name 
 
 LOV Oracle
Qualifying Date 
 
 Date
Download
Related Blitz Reports
PER COBRA Coverage PER COBRA Payments PER COBRA Grace Period Expiration Letter PER COBRA Expiration Letter BEN COBRA Benefit Initial Information CSD Depot Repair Traveler PER Invalid Address BEN COBRA Benefit Notification Letter PQP OSS HRMS Comparison
Blitz Report™

Blitz Report™ provides multiple benefits: