PER COBRA Notification Letter
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
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 |