PER COBRA Notification Letter

Description
Categories: BI Publisher, Human Resources
Application: Human Resources
Source: COBRA Notification Letter (XML)
Short Name: PERRPCNL_XML
DB package: PER_PERUSCNL_XMLP_PKG
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
PER_DATES_STANDARD
 
Date
Business Group Id
 
Number
Qualifying Date
 
Date
Employee Name
 
LOV Oracle