PER COBRA Notification Letter

Description

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

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Copy

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

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