[Home] [Help]
1644: l_proc varchar2(72) := g_package || ' process_cert_alert';
1645:
1646: l_certification_id ota_certifications_b.certification_id%type;
1647: l_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type;
1648: l_person_id ota_cert_enrollments.person_id%type;
1649:
1650: l_item_type VARCHAR2(5) := 'OTWF';
1651:
1652: --get all enrollment records in current period for reminder ntf
1654: Select ceb.certification_id
1655: ,prd.cert_prd_enrollment_id
1656: ,enr.person_id
1657: From ota_certifications_b ceb
1658: ,ota_cert_enrollments enr
1659: ,ota_cert_prd_enrollments prd
1660: Where
1661: ceb.certification_id = enr.certification_id
1662: and enr.cert_enrollment_id = prd.cert_enrollment_id
1675: Select ceb.certification_id
1676: ,prd.cert_prd_enrollment_id
1677: ,enr.person_id
1678: From ota_certifications_b ceb
1679: ,ota_cert_enrollments enr
1680: ,ota_cert_prd_enrollments prd
1681: Where
1682: ceb.certification_id = enr.certification_id
1683: and enr.cert_enrollment_id = prd.cert_enrollment_id
1693: Select ceb.certification_id
1694: ,prd.cert_prd_enrollment_id
1695: ,enr.person_id
1696: From ota_certifications_b ceb
1697: ,ota_cert_enrollments enr
1698: ,ota_cert_prd_enrollments prd
1699: Where
1700: ceb.certification_id = enr.certification_id
1701: and enr.cert_enrollment_id = prd.cert_enrollment_id
1712: Select ceb.certification_id
1713: ,prd.cert_prd_enrollment_id
1714: ,enr.person_id
1715: From ota_certifications_b ceb
1716: ,ota_cert_enrollments enr
1717: ,ota_cert_prd_enrollments prd
1718: Where
1719: ceb.certification_id = enr.certification_id
1720: and enr.cert_enrollment_id = prd.cert_enrollment_id
1727: cursor get_data_for_cancellation_ntf is
1728: Select ceb.certification_id
1729: ,enr.person_id
1730: From ota_certifications_b ceb
1731: ,ota_cert_enrollments enr
1732: Where
1733: ceb.certification_id = enr.certification_id
1734: and enr.person_id is not null
1735: and (trunc(sysdate) + nvl(ceb.notify_days_before_expire, 0)) = trunc(ceb.end_date_active);