[Home] [Help]
1642: l_proc varchar2(72) := g_package || ' process_cert_alert';
1643:
1644: l_certification_id ota_certifications_b.certification_id%type;
1645: l_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type;
1646: l_person_id ota_cert_enrollments.person_id%type;
1647:
1648: l_item_type VARCHAR2(5) := 'OTWF';
1649:
1650: --get all enrollment records in current period for reminder ntf
1652: Select ceb.certification_id
1653: ,prd.cert_prd_enrollment_id
1654: ,enr.person_id
1655: From ota_certifications_b ceb
1656: ,ota_cert_enrollments enr
1657: ,ota_cert_prd_enrollments prd
1658: Where
1659: ceb.certification_id = enr.certification_id
1660: and enr.cert_enrollment_id = prd.cert_enrollment_id
1672: Select ceb.certification_id
1673: ,prd.cert_prd_enrollment_id
1674: ,enr.person_id
1675: From ota_certifications_b ceb
1676: ,ota_cert_enrollments enr
1677: ,ota_cert_prd_enrollments prd
1678: Where
1679: ceb.certification_id = enr.certification_id
1680: and enr.cert_enrollment_id = prd.cert_enrollment_id
1689: Select ceb.certification_id
1690: ,prd.cert_prd_enrollment_id
1691: ,enr.person_id
1692: From ota_certifications_b ceb
1693: ,ota_cert_enrollments enr
1694: ,ota_cert_prd_enrollments prd
1695: Where
1696: ceb.certification_id = enr.certification_id
1697: and enr.cert_enrollment_id = prd.cert_enrollment_id
1708: Select ceb.certification_id
1709: ,prd.cert_prd_enrollment_id
1710: ,enr.person_id
1711: From ota_certifications_b ceb
1712: ,ota_cert_enrollments enr
1713: ,ota_cert_prd_enrollments prd
1714: Where
1715: ceb.certification_id = enr.certification_id
1716: and enr.cert_enrollment_id = prd.cert_enrollment_id
1723: cursor get_data_for_cancellation_ntf is
1724: Select ceb.certification_id
1725: ,enr.person_id
1726: From ota_certifications_b ceb
1727: ,ota_cert_enrollments enr
1728: Where
1729: ceb.certification_id = enr.certification_id
1730: and enr.person_id is not null
1731: and (trunc(sysdate) + nvl(ceb.notify_days_before_expire, 0)) = trunc(ceb.end_date_active);