eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.OTA_CPE_UTIL dependencies on OTA_CERT_PRD_ENROLLMENTS

Line 30: ota_cert_prd_enrollments cpe,

26: cre.certification_id
27: from
28: ota_activity_versions tav,
29: ota_cert_enrollments cre,
30: ota_cert_prd_enrollments cpe,
31: ota_cert_mbr_enrollments cme,
32: ota_certification_members cmb,
33: ota_events evt
34:

Line 82: Function is_cert_success_complete(p_cert_prd_enrollment_id in ota_cert_prd_enrollments.cert_prd_enrollment_id%type,

78: -- ---------------------------------------------------------------------------
79: -- |----------------------< is_cert_success_complete >-----------------------------|
80: -- ---------------------------------------------------------------------------
81: --
82: Function is_cert_success_complete(p_cert_prd_enrollment_id in ota_cert_prd_enrollments.cert_prd_enrollment_id%type,
83: p_cert_period_start_date in ota_cert_prd_enrollments.cert_period_start_date%type,
84: p_cert_period_end_date in ota_cert_prd_enrollments.cert_period_start_date%type,
85: p_person_id in number)
86: return varchar2

Line 83: p_cert_period_start_date in ota_cert_prd_enrollments.cert_period_start_date%type,

79: -- |----------------------< is_cert_success_complete >-----------------------------|
80: -- ---------------------------------------------------------------------------
81: --
82: Function is_cert_success_complete(p_cert_prd_enrollment_id in ota_cert_prd_enrollments.cert_prd_enrollment_id%type,
83: p_cert_period_start_date in ota_cert_prd_enrollments.cert_period_start_date%type,
84: p_cert_period_end_date in ota_cert_prd_enrollments.cert_period_start_date%type,
85: p_person_id in number)
86: return varchar2
87: IS

Line 84: p_cert_period_end_date in ota_cert_prd_enrollments.cert_period_start_date%type,

80: -- ---------------------------------------------------------------------------
81: --
82: Function is_cert_success_complete(p_cert_prd_enrollment_id in ota_cert_prd_enrollments.cert_prd_enrollment_id%type,
83: p_cert_period_start_date in ota_cert_prd_enrollments.cert_period_start_date%type,
84: p_cert_period_end_date in ota_cert_prd_enrollments.cert_period_start_date%type,
85: p_person_id in number)
86: return varchar2
87: IS
88:

Line 98: ota_cert_prd_enrollments cpe,

94:
95: FROM
96: ota_activity_versions tav,
97: -- ota_cert_enrollments cre,
98: ota_cert_prd_enrollments cpe,
99: ota_cert_mbr_enrollments cme,
100: ota_certification_members cmb
101:
102: WHERE

Line 181: Function chk_cert_prd_compl(p_cert_prd_enrollment_id in ota_cert_prd_enrollments.cert_prd_enrollment_id%type)

177: -- ---------------------------------------------------------------------------
178: -- |----------------------< chk_cert_prd_compl >-----------------------------|
179: -- ---------------------------------------------------------------------------
180: --
181: Function chk_cert_prd_compl(p_cert_prd_enrollment_id in ota_cert_prd_enrollments.cert_prd_enrollment_id%type)
182: return varchar2
183: IS
184:
185: Cursor any_child is

Line 228: procedure update_cpe_status(p_cert_prd_enrollment_id in ota_cert_prd_enrollments.cert_prd_enrollment_id%type

224: RETURN NULL;
225:
226: end chk_cert_prd_compl;
227:
228: procedure update_cpe_status(p_cert_prd_enrollment_id in ota_cert_prd_enrollments.cert_prd_enrollment_id%type
229: ,p_certification_status_code OUT NOCOPY VARCHAR2
230: ,p_enroll_from in varchar2 default null
231: ,p_cert_period_end_date in ota_cert_prd_enrollments.cert_period_end_date%type default null
232: ,p_child_update_flag in varchar2 default 'Y'

Line 231: ,p_cert_period_end_date in ota_cert_prd_enrollments.cert_period_end_date%type default null

227:
228: procedure update_cpe_status(p_cert_prd_enrollment_id in ota_cert_prd_enrollments.cert_prd_enrollment_id%type
229: ,p_certification_status_code OUT NOCOPY VARCHAR2
230: ,p_enroll_from in varchar2 default null
231: ,p_cert_period_end_date in ota_cert_prd_enrollments.cert_period_end_date%type default null
232: ,p_child_update_flag in varchar2 default 'Y'
233: ,p_completion_date in date default sysdate)
234: IS
235:

Line 254: ota_cert_prd_enrollments cpe

250: cre.certification_id,
251: cre.person_id,
252: cre.expiration_date, cre.unenrollment_date
253: FROM ota_cert_enrollments cre,
254: ota_cert_prd_enrollments cpe
255: where cpe.cert_prd_enrollment_id = p_cert_prd_enrollment_id
256: and cpe.cert_enrollment_id = cre.cert_enrollment_id;
257:
258: CURSOR csr_prd_enrl IS

Line 260: FROM ota_cert_prd_enrollments

256: and cpe.cert_enrollment_id = cre.cert_enrollment_id;
257:
258: CURSOR csr_prd_enrl IS
259: select period_status_code, object_version_number, completion_date, cert_enrollment_id, cert_period_end_date
260: FROM ota_cert_prd_enrollments
261: where cert_prd_enrollment_id = p_cert_prd_enrollment_id;
262:
263: Cursor get_mbr_completion_date is
264: Select min(cme.completion_date)

Line 443: from ota_cert_prd_enrollments cpe,

439:
440: CURSOR csr_max_cpe_exp_dt IS
441: select
442: max(cpe.expiration_date)
443: from ota_cert_prd_enrollments cpe,
444: ota_cert_enrollments cre
445: where cpe.cert_enrollment_id = cre.cert_enrollment_id
446: and cre.cert_enrollment_id = p_cert_enrollment_id;
447:

Line 656: from ota_cert_prd_enrollments cpe,

652:
653: CURSOR csr_max_cpe_exp_dt IS
654: select
655: max(cpe.expiration_date)
656: from ota_cert_prd_enrollments cpe,
657: ota_cert_enrollments cre
658: where cpe.cert_enrollment_id = cre.cert_enrollment_id
659: and cre.cert_enrollment_id = p_cert_enrollment_id;
660:

Line 857: from ota_cert_prd_enrollments cpe,

853:
854: CURSOR csr_max_cpe_exp_dt IS
855: select
856: max(cpe.expiration_date)
857: from ota_cert_prd_enrollments cpe,
858: ota_cert_enrollments cre
859: where cpe.cert_enrollment_id = cre.cert_enrollment_id
860: and cre.cert_enrollment_id = p_cert_enrollment_id;
861:

Line 942: p_cert_prd_enrollment_id in ota_cert_prd_enrollments.cert_prd_enrollment_id%TYPE,

938:
939: end get_next_prd_dur_days;
940: --
941: FUNCTION get_cert_mbr_status (p_cert_mbr_id in ota_certification_members.certification_member_id%TYPE,
942: p_cert_prd_enrollment_id in ota_cert_prd_enrollments.cert_prd_enrollment_id%TYPE,
943: p_code in number default 1)
944: RETURN varchar2 is
945:
946: Cursor get_cert_mbr_status is

Line 1077: FUNCTION get_cpe_edit_enabled(p_cert_prd_enrollment_id in ota_cert_prd_enrollments.cert_prd_enrollment_id%TYPE)

1073: RETURN null;
1074:
1075: End get_cre_status;
1076:
1077: FUNCTION get_cpe_edit_enabled(p_cert_prd_enrollment_id in ota_cert_prd_enrollments.cert_prd_enrollment_id%TYPE)
1078: RETURN varchar2 is
1079:
1080: Cursor csr_cpe is
1081: Select

Line 1084: ota_cert_prd_enrollments cpe

1080: Cursor csr_cpe is
1081: Select
1082: get_cre_status(cre.cert_enrollment_id, 'c') certification_status_code
1083: From ota_cert_enrollments cre,
1084: ota_cert_prd_enrollments cpe
1085: where cpe.cert_prd_enrollment_id = p_cert_prd_enrollment_id
1086: and cre.cert_enrollment_id = cpe.cert_enrollment_id;
1087:
1088: l_proc VARCHAR2(72) := g_package||' get_cpe_edit_enabled';

Line 1112: FUNCTION chk_prd_end_date(p_cert_prd_enrollment_id in ota_cert_prd_enrollments.cert_prd_enrollment_id%TYPE,

1108: RETURN null;
1109:
1110: End get_cpe_edit_enabled;
1111:
1112: FUNCTION chk_prd_end_date(p_cert_prd_enrollment_id in ota_cert_prd_enrollments.cert_prd_enrollment_id%TYPE,
1113: p_cert_period_end_date in ota_cert_prd_enrollments.cert_period_end_date%TYPE,
1114: p_mass_update_flag in varchar2 default 'N') return varchar2 IS
1115:
1116: CURSOR csr_cert_enrl IS

Line 1113: p_cert_period_end_date in ota_cert_prd_enrollments.cert_period_end_date%TYPE,

1109:
1110: End get_cpe_edit_enabled;
1111:
1112: FUNCTION chk_prd_end_date(p_cert_prd_enrollment_id in ota_cert_prd_enrollments.cert_prd_enrollment_id%TYPE,
1113: p_cert_period_end_date in ota_cert_prd_enrollments.cert_period_end_date%TYPE,
1114: p_mass_update_flag in varchar2 default 'N') return varchar2 IS
1115:
1116: CURSOR csr_cert_enrl IS
1117: select cre.certification_status_code,

Line 1128: ota_cert_prd_enrollments cpe,

1124: crt.initial_completion_duration,
1125: crt.validity_duration,
1126: crt.validity_start_type
1127: FROM ota_cert_enrollments cre,
1128: ota_cert_prd_enrollments cpe,
1129: ota_certifications_b crt
1130: where cpe.cert_prd_enrollment_id = p_cert_prd_enrollment_id
1131: and cre.certification_id = crt.certification_id
1132: and cpe.cert_enrollment_id = cre.cert_enrollment_id;

Line 1192: if hr_multi_message.exception_add(p_associated_column1 => 'OTA_CERT_PRD_ENROLLMENTS.CERT_PERIOD_END_DATE') then

1188: WHEN app_exception.application_exception THEN
1189: hr_utility.set_location(' Leaving:'||l_proc, 25);
1190:
1191: if p_mass_update_flag = 'N' then
1192: if hr_multi_message.exception_add(p_associated_column1 => 'OTA_CERT_PRD_ENROLLMENTS.CERT_PERIOD_END_DATE') then
1193: return hr_multi_message.get_return_status_disable;
1194: end if;
1195: l_return_status := hr_multi_message.get_return_status_disable;
1196: else

Line 1217: p_cert_prd_enrollment_id OUT NOCOPY ota_cert_prd_enrollments.cert_prd_enrollment_id%type,

1213:
1214: procedure create_cpe_rec(p_cert_enrollment_id in ota_cert_enrollments.cert_enrollment_id%type,
1215: p_expiration_date in date,
1216: p_cert_period_start_date in date default sysdate,
1217: p_cert_prd_enrollment_id OUT NOCOPY ota_cert_prd_enrollments.cert_prd_enrollment_id%type,
1218: p_certification_status_code OUT NOCOPY VARCHAR2,
1219: p_is_recert in varchar2 default 'N')
1220: IS
1221: CURSOR csr_cert_info(p_certification_id in ota_cert_enrollments.certification_id%type) IS

Line 1259: CURSOR csr_prd_enrl(csr_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type) IS

1255: object_version_number, completion_date, expiration_date
1256: FROM ota_cert_enrollments
1257: where cert_enrollment_id = p_cert_enrollment_id;
1258:
1259: CURSOR csr_prd_enrl(csr_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type) IS
1260: select period_status_code, object_version_number, completion_date
1261: FROM ota_cert_prd_enrollments
1262: where cert_prd_enrollment_id = csr_cert_prd_enrollment_id;
1263:

Line 1261: FROM ota_cert_prd_enrollments

1257: where cert_enrollment_id = p_cert_enrollment_id;
1258:
1259: CURSOR csr_prd_enrl(csr_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type) IS
1260: select period_status_code, object_version_number, completion_date
1261: FROM ota_cert_prd_enrollments
1262: where cert_prd_enrollment_id = csr_cert_prd_enrollment_id;
1263:
1264: Cursor one_child_active(l_cert_prd_enrollment_id in number) IS
1265: Select cme.cert_mbr_enrollment_id

Line 1272: FROM ota_cert_prd_enrollments

1268: and cert_prd_enrollment_id = l_cert_prd_enrollment_id and rownum=1;
1269:
1270: CURSOR csr_prd_enrl_count IS
1271: select count(cert_prd_enrollment_id)
1272: FROM ota_cert_prd_enrollments
1273: where cert_enrollment_id = p_cert_enrollment_id;
1274:
1275: Cursor get_mbr_completion_date(l_cert_prd_enrollment_id in number) is
1276: Select min(cme.completion_date)

Line 1368: ,flex_field_name => 'OTA_CERT_PRD_ENROLLMENTS'

1364: end if;
1365: */
1366: ota_utility.Get_Default_Value_Dff(
1367: appl_short_name => 'OTA'
1368: ,flex_field_name => 'OTA_CERT_PRD_ENROLLMENTS'
1369: ,p_attribute_category => l_attribute_category
1370: ,p_attribute1 => l_attribute1
1371: ,p_attribute2 => l_attribute2
1372: ,p_attribute3 => l_attribute3

Line 1597: PROCEDURE delete_prd_cascade(p_cert_prd_enrollment_id IN ota_cert_prd_enrollments.cert_prd_enrollment_id%type,

1593: hr_utility.set_location(' Leaving:' || l_proc,50);
1594: raise;
1595: end create_cpe_rec;
1596:
1597: PROCEDURE delete_prd_cascade(p_cert_prd_enrollment_id IN ota_cert_prd_enrollments.cert_prd_enrollment_id%type,
1598: p_return_code OUT NOCOPY varchar2) IS
1599:
1600: CURSOR csr_attempt IS
1601: SELECT attempt_id

Line 1663: DELETE FROM ota_cert_prd_enrollments where cert_prd_enrollment_id = p_cert_prd_enrollment_id;

1659: --delete cert mbr enrollments
1660: DELETE FROM ota_cert_mbr_enrollments where cert_prd_enrollment_id = p_cert_prd_enrollment_id;
1661:
1662: --delete cert prd enrollments
1663: DELETE FROM ota_cert_prd_enrollments where cert_prd_enrollment_id = p_cert_prd_enrollment_id;
1664:
1665: p_return_code := l_return_code;
1666: EXCEPTION
1667: WHEN others THEN

Line 1674: procedure update_admin_changes(p_cert_enrollment_id in ota_cert_prd_enrollments.cert_enrollment_id%type,

1670: --
1671: p_return_code := 'E';
1672: END delete_prd_cascade;
1673:
1674: procedure update_admin_changes(p_cert_enrollment_id in ota_cert_prd_enrollments.cert_enrollment_id%type,
1675: p_cert_prd_enrollment_id in ota_cert_prd_enrollments.cert_prd_enrollment_id%type,
1676: p_certification_status_code in ota_cert_enrollments.certification_status_code%type,
1677: p_cert_period_end_date in ota_cert_prd_enrollments.cert_period_end_date%type default null,
1678: p_return_status out NOCOPY VARCHAR2,

Line 1675: p_cert_prd_enrollment_id in ota_cert_prd_enrollments.cert_prd_enrollment_id%type,

1671: p_return_code := 'E';
1672: END delete_prd_cascade;
1673:
1674: procedure update_admin_changes(p_cert_enrollment_id in ota_cert_prd_enrollments.cert_enrollment_id%type,
1675: p_cert_prd_enrollment_id in ota_cert_prd_enrollments.cert_prd_enrollment_id%type,
1676: p_certification_status_code in ota_cert_enrollments.certification_status_code%type,
1677: p_cert_period_end_date in ota_cert_prd_enrollments.cert_period_end_date%type default null,
1678: p_return_status out NOCOPY VARCHAR2,
1679: p_cert_period_completion_date in ota_cert_prd_enrollments.completion_date%type default trunc(sysdate),

Line 1677: p_cert_period_end_date in ota_cert_prd_enrollments.cert_period_end_date%type default null,

1673:
1674: procedure update_admin_changes(p_cert_enrollment_id in ota_cert_prd_enrollments.cert_enrollment_id%type,
1675: p_cert_prd_enrollment_id in ota_cert_prd_enrollments.cert_prd_enrollment_id%type,
1676: p_certification_status_code in ota_cert_enrollments.certification_status_code%type,
1677: p_cert_period_end_date in ota_cert_prd_enrollments.cert_period_end_date%type default null,
1678: p_return_status out NOCOPY VARCHAR2,
1679: p_cert_period_completion_date in ota_cert_prd_enrollments.completion_date%type default trunc(sysdate),
1680: p_cert_upd_comp in varchar2 default 'N') is
1681:

Line 1679: p_cert_period_completion_date in ota_cert_prd_enrollments.completion_date%type default trunc(sysdate),

1675: p_cert_prd_enrollment_id in ota_cert_prd_enrollments.cert_prd_enrollment_id%type,
1676: p_certification_status_code in ota_cert_enrollments.certification_status_code%type,
1677: p_cert_period_end_date in ota_cert_prd_enrollments.cert_period_end_date%type default null,
1678: p_return_status out NOCOPY VARCHAR2,
1679: p_cert_period_completion_date in ota_cert_prd_enrollments.completion_date%type default trunc(sysdate),
1680: p_cert_upd_comp in varchar2 default 'N') is
1681:
1682: CURSOR csr_cert_enrl IS
1683: select cre.certification_status_code, cre.object_version_number, cre.completion_date,

Line 1692: FROM ota_cert_prd_enrollments

1688: where cre.cert_enrollment_id = p_cert_enrollment_id;
1689:
1690: CURSOR csr_prd_enrl IS
1691: select period_status_code, object_version_number, completion_date, cert_enrollment_id, cert_period_start_date, cert_period_end_date
1692: FROM ota_cert_prd_enrollments
1693: where cert_prd_enrollment_id = p_cert_prd_enrollment_id;
1694:
1695: l_proc varchar2(72) := g_package || ' update_admin_changes';
1696: l_cert_enrl_rec csr_cert_enrl%ROWTYPE;

Line 1701: l_cert_period_end_date ota_cert_prd_enrollments.cert_period_end_date%type;

1697: l_prd_enrl_rec csr_prd_enrl%ROWTYPE;
1698: l_return_status varchar2(1) := 'F';
1699: l_new_cre_status_code varchar2(30);
1700: l_new_cpe_status_code varchar2(30);
1701: l_cert_period_end_date ota_cert_prd_enrollments.cert_period_end_date%type;
1702: l_curr_date date;
1703: l_certification_status_code varchar2(30);
1704: l_is_period_update boolean := false;
1705: l_cert_period_completion_date ota_cert_prd_enrollments.completion_date%type;

Line 1705: l_cert_period_completion_date ota_cert_prd_enrollments.completion_date%type;

1701: l_cert_period_end_date ota_cert_prd_enrollments.cert_period_end_date%type;
1702: l_curr_date date;
1703: l_certification_status_code varchar2(30);
1704: l_is_period_update boolean := false;
1705: l_cert_period_completion_date ota_cert_prd_enrollments.completion_date%type;
1706: l_item_key wf_items.item_key%type;
1707: l_old_status_code ota_cert_enrollments.certification_status_code%type;
1708: l_cert_upd_comp varchar2(1);
1709: Begin

Line 1884: ,ota_cert_prd_enrollments prd

1880: ,prd.cert_period_end_date
1881: ,prd.object_version_number
1882: From ota_certifications_vl ceb
1883: ,ota_cert_enrollments enr
1884: ,ota_cert_prd_enrollments prd
1885: Where
1886: ceb.certification_id = enr.certification_id
1887: and enr.cert_enrollment_id = prd.cert_enrollment_id
1888: and trunc(sysdate) between nvl(trunc(ceb.start_date_active), trunc(sysdate)) and

Line 1892: cursor get_data_for_expired_records(p_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type) is

1888: and trunc(sysdate) between nvl(trunc(ceb.start_date_active), trunc(sysdate)) and
1889: nvl(trunc(ceb.end_date_active), trunc(sysdate))
1890: and prd.period_status_code in ('ACTIVE', 'ENROLLED', 'INCOMPLETE');
1891:
1892: cursor get_data_for_expired_records(p_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type) is
1893: Select ceb.certification_id
1894: ,ceb.name
1895: ,enr.cert_enrollment_id
1896: ,enr.person_id

Line 1907: ,ota_cert_prd_enrollments prd

1903: ,ceb.initial_completion_date
1904: ,enr.object_version_number
1905: From ota_certifications_vl ceb
1906: ,ota_cert_enrollments enr
1907: ,ota_cert_prd_enrollments prd
1908: Where
1909: ceb.certification_id = enr.certification_id
1910: and enr.cert_enrollment_id = prd.cert_enrollment_id
1911: and trunc(sysdate) between nvl(trunc(ceb.start_date_active), trunc(sysdate))

Line 1916: from ota_cert_prd_enrollments cpex

1912: and nvl(trunc(ceb.end_date_active), trunc(sysdate))
1913: and enr.certification_status_code in ('ENROLLED', 'CERTIFIED', 'RENEWING') -- Bug#7303995
1914: --check if this is max/latest prd then update CRE accordingly
1915: and prd.cert_prd_enrollment_id = (select max(cpex.cert_prd_enrollment_id)
1916: from ota_cert_prd_enrollments cpex
1917: where cpex.cert_enrollment_id = enr.cert_enrollment_id
1918: and trunc(cpex.cert_period_end_date) < trunc(sysdate)
1919: )
1920: and prd.cert_prd_enrollment_id = p_cert_prd_enrollment_id;

Line 2014: from ota_cert_prd_enrollments cpe,

2010: cursor csr_cpe_status is
2011: select cpe.cert_prd_enrollment_id,
2012: cpe.period_status_code,
2013: cpe.cert_period_start_date
2014: from ota_cert_prd_enrollments cpe,
2015: ota_cert_enrollments cre
2016: where cpe.period_status_code not in ('COMPLETED', 'CANCELLED') and
2017: cpe.cert_enrollment_id = cre.cert_enrollment_id --bug#6338987
2018: order by cre.certification_id;

Line 2020: cursor csr_acty_info(p_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type)

2016: where cpe.period_status_code not in ('COMPLETED', 'CANCELLED') and
2017: cpe.cert_enrollment_id = cre.cert_enrollment_id --bug#6338987
2018: order by cre.certification_id;
2019:
2020: cursor csr_acty_info(p_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type)
2021:
2022: is
2023: select cme.cert_mbr_enrollment_id,
2024: cmb.object_id

Line 2034: l_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%TYPE;

2030:
2031: l_booking_status_type ota_booking_status_types.type%TYPE;
2032: l_date_status_changed ota_delegate_bookings.date_status_changed%TYPE;
2033: do_update boolean := false;
2034: l_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%TYPE;
2035:
2036: begin
2037:
2038: hr_utility.set_location('Entering:'||l_proc, 5);

Line 2102: From ota_cert_prd_enrollments cpe

2098: Cursor csr_latest_cpe is
2099: Select cpe.period_status_code Period_Status_Code,
2100: ota_utility.get_lookup_meaning('OTA_CERT_PRD_ENROLL_STATUS', cpe.period_status_code, 810) Period_Status_Meaning,
2101: cpe.cert_prd_enrollment_id
2102: From ota_cert_prd_enrollments cpe
2103: where cpe.cert_enrollment_id = p_cert_enrollment_id
2104: -- and rownum = 1 --Bug#6356854
2105: order by cpe.cert_prd_enrollment_id desc;
2106:

Line 2209: ota_cert_prd_enrollments cpe,

2205: CURSOR csr_chk_prds(l_activity_version_id number) IS
2206: SELECT null
2207: FROM ota_certification_members cmb,
2208: ota_cert_mbr_enrollments cme,
2209: ota_cert_prd_enrollments cpe,
2210: ota_cert_enrollments cre
2211: WHERE
2212: cre.cert_enrollment_id = cpe.cert_enrollment_id
2213: AND cpe.cert_prd_enrollment_id = cme.cert_prd_enrollment_id

Line 2340: FROM ota_certifications_vl ocb, ota_cert_enrollments oce, ota_cert_prd_enrollments ocpe

2336: ocpe.cert_prd_enrollment_id,
2337: ocpe.cert_period_start_date,
2338: ocpe.cert_period_end_date,
2339: ocpe.expiration_date
2340: FROM ota_certifications_vl ocb, ota_cert_enrollments oce, ota_cert_prd_enrollments ocpe
2341: WHERE ocb.certification_id = oce.certification_id
2342: AND oce.cert_enrollment_id = ocpe.cert_enrollment_id
2343: AND trunc(SYSDATE) BETWEEN trunc(ocb.start_date_active) AND trunc(nvl(ocb.end_date_active, SYSDATE))
2344: AND ocb.renewable_flag = 'Y'

Line 2348: AND ocpe.cert_prd_enrollment_id = (SELECT MAX(cert_prd_enrollment_id) FROM ota_cert_prd_enrollments ocpe1

2344: AND ocb.renewable_flag = 'Y'
2345: AND oce.person_id is not null
2346: AND oce.certification_status_code <> 'CERTIFIED'
2347: AND ocpe.period_status_code not in ('COMPLETED', 'CANCELLED')
2348: AND ocpe.cert_prd_enrollment_id = (SELECT MAX(cert_prd_enrollment_id) FROM ota_cert_prd_enrollments ocpe1
2349: WHERE oce.cert_enrollment_id = ocpe1.cert_enrollment_id);
2350:
2351: CURSOR csr_course_info(p_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type) is
2352: SELECT cme.cert_mbr_enrollment_id,

Line 2351: CURSOR csr_course_info(p_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type) is

2347: AND ocpe.period_status_code not in ('COMPLETED', 'CANCELLED')
2348: AND ocpe.cert_prd_enrollment_id = (SELECT MAX(cert_prd_enrollment_id) FROM ota_cert_prd_enrollments ocpe1
2349: WHERE oce.cert_enrollment_id = ocpe1.cert_enrollment_id);
2350:
2351: CURSOR csr_course_info(p_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type) is
2352: SELECT cme.cert_mbr_enrollment_id,
2353: cmb.object_id
2354: FROM ota_cert_mbr_enrollments cme,
2355: ota_certification_members cmb

Line 2362: csr_cert_period_start_date in ota_cert_prd_enrollments.cert_period_start_date%type,

2358: AND object_type = 'H'
2359: AND member_status_code = 'PLANNED';
2360:
2361: CURSOR get_enrl_status(csr_activity_version_id IN ota_activity_versions.activity_version_id%TYPE,
2362: csr_cert_period_start_date in ota_cert_prd_enrollments.cert_period_start_date%type,
2363: csr_cert_period_end_date in ota_cert_prd_enrollments.cert_period_end_date%type,
2364: csr_person_id in ota_cert_enrollments.person_id%TYPE,
2365: csr_contact_id in ota_cert_enrollments.contact_id%TYPE) IS
2366: SELECT DECODE(bst.type,'C','Z',bst.type) status,

Line 2363: csr_cert_period_end_date in ota_cert_prd_enrollments.cert_period_end_date%type,

2359: AND member_status_code = 'PLANNED';
2360:
2361: CURSOR get_enrl_status(csr_activity_version_id IN ota_activity_versions.activity_version_id%TYPE,
2362: csr_cert_period_start_date in ota_cert_prd_enrollments.cert_period_start_date%type,
2363: csr_cert_period_end_date in ota_cert_prd_enrollments.cert_period_end_date%type,
2364: csr_person_id in ota_cert_enrollments.person_id%TYPE,
2365: csr_contact_id in ota_cert_enrollments.contact_id%TYPE) IS
2366: SELECT DECODE(bst.type,'C','Z',bst.type) status,
2367: evt.event_type,

Line 2406: CURSOR csr_prd_enrl(csr_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type) IS

2402: earliest_enroll_date
2403: FROM ota_cert_enrollments
2404: WHERE cert_enrollment_id = p_cert_enrollment_id;
2405:
2406: CURSOR csr_prd_enrl(csr_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type) IS
2407: select cert_enrollment_id, cert_prd_enrollment_id, period_status_code, object_version_number, completion_date
2408: FROM ota_cert_prd_enrollments
2409: where cert_prd_enrollment_id = csr_cert_prd_enrollment_id;
2410:

Line 2408: FROM ota_cert_prd_enrollments

2404: WHERE cert_enrollment_id = p_cert_enrollment_id;
2405:
2406: CURSOR csr_prd_enrl(csr_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type) IS
2407: select cert_enrollment_id, cert_prd_enrollment_id, period_status_code, object_version_number, completion_date
2408: FROM ota_cert_prd_enrollments
2409: where cert_prd_enrollment_id = csr_cert_prd_enrollment_id;
2410:
2411: l_cert_enrl_rec csr_cert_enrl%ROWTYPE;
2412: l_prd_enrl_rec csr_prd_enrl%ROWTYPE;

Line 2416: l_cert_period_start_date ota_cert_prd_enrollments.cert_period_start_date%TYPE;

2412: l_prd_enrl_rec csr_prd_enrl%ROWTYPE;
2413: l_booking_status_type ota_booking_status_types.type%TYPE;
2414: l_date_status_changed ota_delegate_bookings.date_status_changed%TYPE;
2415:
2416: l_cert_period_start_date ota_cert_prd_enrollments.cert_period_start_date%TYPE;
2417: l_cert_period_end_date ota_cert_prd_enrollments.cert_period_end_date%TYPE;
2418: l_cert_completion_date ota_cert_enrollments.completion_date%type;
2419: l_cert_prd_completion_date ota_cert_prd_enrollments.completion_date%type;
2420: l_earliest_enroll_date ota_cert_enrollments.earliest_enroll_date%type;

Line 2417: l_cert_period_end_date ota_cert_prd_enrollments.cert_period_end_date%TYPE;

2413: l_booking_status_type ota_booking_status_types.type%TYPE;
2414: l_date_status_changed ota_delegate_bookings.date_status_changed%TYPE;
2415:
2416: l_cert_period_start_date ota_cert_prd_enrollments.cert_period_start_date%TYPE;
2417: l_cert_period_end_date ota_cert_prd_enrollments.cert_period_end_date%TYPE;
2418: l_cert_completion_date ota_cert_enrollments.completion_date%type;
2419: l_cert_prd_completion_date ota_cert_prd_enrollments.completion_date%type;
2420: l_earliest_enroll_date ota_cert_enrollments.earliest_enroll_date%type;
2421: l_expiration_date ota_cert_prd_enrollments.expiration_date%type;

Line 2419: l_cert_prd_completion_date ota_cert_prd_enrollments.completion_date%type;

2415:
2416: l_cert_period_start_date ota_cert_prd_enrollments.cert_period_start_date%TYPE;
2417: l_cert_period_end_date ota_cert_prd_enrollments.cert_period_end_date%TYPE;
2418: l_cert_completion_date ota_cert_enrollments.completion_date%type;
2419: l_cert_prd_completion_date ota_cert_prd_enrollments.completion_date%type;
2420: l_earliest_enroll_date ota_cert_enrollments.earliest_enroll_date%type;
2421: l_expiration_date ota_cert_prd_enrollments.expiration_date%type;
2422:
2423: begin

Line 2421: l_expiration_date ota_cert_prd_enrollments.expiration_date%type;

2417: l_cert_period_end_date ota_cert_prd_enrollments.cert_period_end_date%TYPE;
2418: l_cert_completion_date ota_cert_enrollments.completion_date%type;
2419: l_cert_prd_completion_date ota_cert_prd_enrollments.completion_date%type;
2420: l_earliest_enroll_date ota_cert_enrollments.earliest_enroll_date%type;
2421: l_expiration_date ota_cert_prd_enrollments.expiration_date%type;
2422:
2423: begin
2424:
2425: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Sync Late Subscription to certification with classes');