PER COBRA Grace Period Expiration Letter

select org.name                                             org_name,
       pcce.cobra_coverage_enrollment_id                    enrol_id,
       decode(pcce.CONTACT_RELATIONSHIP_ID,null,:p_person_id,
                                   pcs.contact_person_id)   per_id
from   per_cobra_cov_enrollments                            pcce,
       per_organization_units                               org,
       per_contact_relationships                                         pcs,
       per_assignments_f                                    asg,
       per_people_f                                         per
where  pcce.CONTACT_RELATIONSHIP_ID                  = pcs.CONTACT_RELATIONSHIP_ID (+)
and    pcce.assignment_id               = asg.assignment_id
and    pcce.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    pcce.qualifying_date  between asg.effective_start_date and
                                asg.effective_end_date
and    org.organization_id              = asg.organization_id
and    org.business_group_id    = :p_business_group_id
and    per.person_id                    = :p_person_id
and    :P_SESSION_DATE between per.effective_start_date and
                               per.effective_end_date