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
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 435: from ota_cert_prd_enrollments cpe,

431:
432: CURSOR csr_max_cpe_exp_dt IS
433: select
434: max(cpe.expiration_date)
435: from ota_cert_prd_enrollments cpe,
436: ota_cert_enrollments cre
437: where cpe.cert_enrollment_id = cre.cert_enrollment_id
438: and cre.cert_enrollment_id = p_cert_enrollment_id;
439:

Line 648: from ota_cert_prd_enrollments cpe,

644:
645: CURSOR csr_max_cpe_exp_dt IS
646: select
647: max(cpe.expiration_date)
648: from ota_cert_prd_enrollments cpe,
649: ota_cert_enrollments cre
650: where cpe.cert_enrollment_id = cre.cert_enrollment_id
651: and cre.cert_enrollment_id = p_cert_enrollment_id;
652:

Line 849: from ota_cert_prd_enrollments cpe,

845:
846: CURSOR csr_max_cpe_exp_dt IS
847: select
848: max(cpe.expiration_date)
849: from ota_cert_prd_enrollments cpe,
850: ota_cert_enrollments cre
851: where cpe.cert_enrollment_id = cre.cert_enrollment_id
852: and cre.cert_enrollment_id = p_cert_enrollment_id;
853:

Line 934: p_cert_prd_enrollment_id in ota_cert_prd_enrollments.cert_prd_enrollment_id%TYPE,

930:
931: end get_next_prd_dur_days;
932: --
933: FUNCTION get_cert_mbr_status (p_cert_mbr_id in ota_certification_members.certification_member_id%TYPE,
934: p_cert_prd_enrollment_id in ota_cert_prd_enrollments.cert_prd_enrollment_id%TYPE,
935: p_code in number default 1)
936: RETURN varchar2 is
937:
938: Cursor get_cert_mbr_status is

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

1065: RETURN null;
1066:
1067: End get_cre_status;
1068:
1069: FUNCTION get_cpe_edit_enabled(p_cert_prd_enrollment_id in ota_cert_prd_enrollments.cert_prd_enrollment_id%TYPE)
1070: RETURN varchar2 is
1071:
1072: Cursor csr_cpe is
1073: Select

Line 1076: ota_cert_prd_enrollments cpe

1072: Cursor csr_cpe is
1073: Select
1074: get_cre_status(cre.cert_enrollment_id, 'c') certification_status_code
1075: From ota_cert_enrollments cre,
1076: ota_cert_prd_enrollments cpe
1077: where cpe.cert_prd_enrollment_id = p_cert_prd_enrollment_id
1078: and cre.cert_enrollment_id = cpe.cert_enrollment_id;
1079:
1080: l_proc VARCHAR2(72) := g_package||' get_cpe_edit_enabled';

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

1100: RETURN null;
1101:
1102: End get_cpe_edit_enabled;
1103:
1104: FUNCTION chk_prd_end_date(p_cert_prd_enrollment_id in ota_cert_prd_enrollments.cert_prd_enrollment_id%TYPE,
1105: p_cert_period_end_date in ota_cert_prd_enrollments.cert_period_end_date%TYPE,
1106: p_mass_update_flag in varchar2 default 'N') return varchar2 IS
1107:
1108: CURSOR csr_cert_enrl IS

Line 1105: p_cert_period_end_date in ota_cert_prd_enrollments.cert_period_end_date%TYPE,

1101:
1102: End get_cpe_edit_enabled;
1103:
1104: FUNCTION chk_prd_end_date(p_cert_prd_enrollment_id in ota_cert_prd_enrollments.cert_prd_enrollment_id%TYPE,
1105: p_cert_period_end_date in ota_cert_prd_enrollments.cert_period_end_date%TYPE,
1106: p_mass_update_flag in varchar2 default 'N') return varchar2 IS
1107:
1108: CURSOR csr_cert_enrl IS
1109: select cre.certification_status_code,

Line 1120: ota_cert_prd_enrollments cpe,

1116: crt.initial_completion_duration,
1117: crt.validity_duration,
1118: crt.validity_start_type
1119: FROM ota_cert_enrollments cre,
1120: ota_cert_prd_enrollments cpe,
1121: ota_certifications_b crt
1122: where cpe.cert_prd_enrollment_id = p_cert_prd_enrollment_id
1123: and cre.certification_id = crt.certification_id
1124: and cpe.cert_enrollment_id = cre.cert_enrollment_id;

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

1180: WHEN app_exception.application_exception THEN
1181: hr_utility.set_location(' Leaving:'||l_proc, 25);
1182:
1183: if p_mass_update_flag = 'N' then
1184: if hr_multi_message.exception_add(p_associated_column1 => 'OTA_CERT_PRD_ENROLLMENTS.CERT_PERIOD_END_DATE') then
1185: return hr_multi_message.get_return_status_disable;
1186: end if;
1187: l_return_status := hr_multi_message.get_return_status_disable;
1188: else

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

1205:
1206: procedure create_cpe_rec(p_cert_enrollment_id in ota_cert_enrollments.cert_enrollment_id%type,
1207: p_expiration_date in date,
1208: p_cert_period_start_date in date default sysdate,
1209: p_cert_prd_enrollment_id OUT NOCOPY ota_cert_prd_enrollments.cert_prd_enrollment_id%type,
1210: p_certification_status_code OUT NOCOPY VARCHAR2)
1211: IS
1212: CURSOR csr_cert_info(p_certification_id in ota_cert_enrollments.certification_id%type) IS
1213: select

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

1245: select certification_id, cert_enrollment_id, business_group_id, certification_status_code, object_version_number, completion_date
1246: FROM ota_cert_enrollments
1247: where cert_enrollment_id = p_cert_enrollment_id;
1248:
1249: CURSOR csr_prd_enrl(csr_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type) IS
1250: select period_status_code, object_version_number, completion_date
1251: FROM ota_cert_prd_enrollments
1252: where cert_prd_enrollment_id = csr_cert_prd_enrollment_id;
1253:

Line 1251: FROM ota_cert_prd_enrollments

1247: where cert_enrollment_id = p_cert_enrollment_id;
1248:
1249: CURSOR csr_prd_enrl(csr_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type) IS
1250: select period_status_code, object_version_number, completion_date
1251: FROM ota_cert_prd_enrollments
1252: where cert_prd_enrollment_id = csr_cert_prd_enrollment_id;
1253:
1254: Cursor one_child_active(l_cert_prd_enrollment_id in number) IS
1255: Select cme.cert_mbr_enrollment_id

Line 1262: FROM ota_cert_prd_enrollments

1258: and cert_prd_enrollment_id = l_cert_prd_enrollment_id and rownum=1;
1259:
1260: CURSOR csr_prd_enrl_count IS
1261: select count(cert_prd_enrollment_id)
1262: FROM ota_cert_prd_enrollments
1263: where cert_enrollment_id = p_cert_enrollment_id;
1264:
1265: Cursor get_mbr_completion_date(l_cert_prd_enrollment_id in number) is
1266: Select min(cme.completion_date)

Line 1357: ,flex_field_name => 'OTA_CERT_PRD_ENROLLMENTS'

1353: end if;
1354: */
1355: ota_utility.Get_Default_Value_Dff(
1356: appl_short_name => 'OTA'
1357: ,flex_field_name => 'OTA_CERT_PRD_ENROLLMENTS'
1358: ,p_attribute_category => l_attribute_category
1359: ,p_attribute1 => l_attribute1
1360: ,p_attribute2 => l_attribute2
1361: ,p_attribute3 => l_attribute3

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

1579: hr_utility.set_location(' Leaving:' || l_proc,50);
1580: raise;
1581: end create_cpe_rec;
1582:
1583: PROCEDURE delete_prd_cascade(p_cert_prd_enrollment_id IN ota_cert_prd_enrollments.cert_prd_enrollment_id%type,
1584: p_return_code OUT NOCOPY varchar2) IS
1585:
1586: CURSOR csr_attempt IS
1587: SELECT attempt_id

Line 1649: DELETE FROM ota_cert_prd_enrollments where cert_prd_enrollment_id = p_cert_prd_enrollment_id;

1645: --delete cert mbr enrollments
1646: DELETE FROM ota_cert_mbr_enrollments where cert_prd_enrollment_id = p_cert_prd_enrollment_id;
1647:
1648: --delete cert prd enrollments
1649: DELETE FROM ota_cert_prd_enrollments where cert_prd_enrollment_id = p_cert_prd_enrollment_id;
1650:
1651: p_return_code := l_return_code;
1652: EXCEPTION
1653: WHEN others THEN

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

1656: --
1657: p_return_code := 'E';
1658: END delete_prd_cascade;
1659:
1660: procedure update_admin_changes(p_cert_enrollment_id in ota_cert_prd_enrollments.cert_enrollment_id%type,
1661: p_cert_prd_enrollment_id in ota_cert_prd_enrollments.cert_prd_enrollment_id%type,
1662: p_certification_status_code in ota_cert_enrollments.certification_status_code%type,
1663: p_cert_period_end_date in ota_cert_prd_enrollments.cert_period_end_date%type default null,
1664: p_return_status out NOCOPY VARCHAR2,

Line 1661: p_cert_prd_enrollment_id in ota_cert_prd_enrollments.cert_prd_enrollment_id%type,

1657: p_return_code := 'E';
1658: END delete_prd_cascade;
1659:
1660: procedure update_admin_changes(p_cert_enrollment_id in ota_cert_prd_enrollments.cert_enrollment_id%type,
1661: p_cert_prd_enrollment_id in ota_cert_prd_enrollments.cert_prd_enrollment_id%type,
1662: p_certification_status_code in ota_cert_enrollments.certification_status_code%type,
1663: p_cert_period_end_date in ota_cert_prd_enrollments.cert_period_end_date%type default null,
1664: p_return_status out NOCOPY VARCHAR2,
1665: p_cert_period_completion_date in ota_cert_prd_enrollments.completion_date%type default trunc(sysdate) ) is

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

1659:
1660: procedure update_admin_changes(p_cert_enrollment_id in ota_cert_prd_enrollments.cert_enrollment_id%type,
1661: p_cert_prd_enrollment_id in ota_cert_prd_enrollments.cert_prd_enrollment_id%type,
1662: p_certification_status_code in ota_cert_enrollments.certification_status_code%type,
1663: p_cert_period_end_date in ota_cert_prd_enrollments.cert_period_end_date%type default null,
1664: p_return_status out NOCOPY VARCHAR2,
1665: p_cert_period_completion_date in ota_cert_prd_enrollments.completion_date%type default trunc(sysdate) ) is
1666:
1667: CURSOR csr_cert_enrl IS

Line 1665: p_cert_period_completion_date in ota_cert_prd_enrollments.completion_date%type default trunc(sysdate) ) is

1661: p_cert_prd_enrollment_id in ota_cert_prd_enrollments.cert_prd_enrollment_id%type,
1662: p_certification_status_code in ota_cert_enrollments.certification_status_code%type,
1663: p_cert_period_end_date in ota_cert_prd_enrollments.cert_period_end_date%type default null,
1664: p_return_status out NOCOPY VARCHAR2,
1665: p_cert_period_completion_date in ota_cert_prd_enrollments.completion_date%type default trunc(sysdate) ) is
1666:
1667: CURSOR csr_cert_enrl IS
1668: select cre.certification_status_code, cre.object_version_number, cre.completion_date,
1669: cre.certification_id,

Line 1677: FROM ota_cert_prd_enrollments

1673: where cre.cert_enrollment_id = p_cert_enrollment_id;
1674:
1675: CURSOR csr_prd_enrl IS
1676: select period_status_code, object_version_number, completion_date, cert_enrollment_id, cert_period_start_date, cert_period_end_date
1677: FROM ota_cert_prd_enrollments
1678: where cert_prd_enrollment_id = p_cert_prd_enrollment_id;
1679:
1680: l_proc varchar2(72) := g_package || ' update_admin_changes';
1681: l_cert_enrl_rec csr_cert_enrl%ROWTYPE;

Line 1686: l_cert_period_end_date ota_cert_prd_enrollments.cert_period_end_date%type;

1682: l_prd_enrl_rec csr_prd_enrl%ROWTYPE;
1683: l_return_status varchar2(1) := 'F';
1684: l_new_cre_status_code varchar2(30);
1685: l_new_cpe_status_code varchar2(30);
1686: l_cert_period_end_date ota_cert_prd_enrollments.cert_period_end_date%type;
1687: l_curr_date date;
1688: l_certification_status_code varchar2(30);
1689: l_is_period_update boolean := false;
1690: l_cert_period_completion_date ota_cert_prd_enrollments.completion_date%type;

Line 1690: l_cert_period_completion_date ota_cert_prd_enrollments.completion_date%type;

1686: l_cert_period_end_date ota_cert_prd_enrollments.cert_period_end_date%type;
1687: l_curr_date date;
1688: l_certification_status_code varchar2(30);
1689: l_is_period_update boolean := false;
1690: l_cert_period_completion_date ota_cert_prd_enrollments.completion_date%type;
1691: Begin
1692:
1693: hr_utility.set_location(' Entering:' || l_proc,10);
1694:

Line 1845: ,ota_cert_prd_enrollments prd

1841: ,prd.cert_period_end_date
1842: ,prd.object_version_number
1843: From ota_certifications_vl ceb
1844: ,ota_cert_enrollments enr
1845: ,ota_cert_prd_enrollments prd
1846: Where
1847: ceb.certification_id = enr.certification_id
1848: and enr.cert_enrollment_id = prd.cert_enrollment_id
1849: and trunc(sysdate) between nvl(trunc(ceb.start_date_active), trunc(sysdate)) and

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

1849: and trunc(sysdate) between nvl(trunc(ceb.start_date_active), trunc(sysdate)) and
1850: nvl(trunc(ceb.end_date_active), trunc(sysdate))
1851: and prd.period_status_code in ('ACTIVE', 'ENROLLED', 'INCOMPLETE');
1852:
1853: cursor get_data_for_expired_records(p_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type) is
1854: Select ceb.certification_id
1855: ,ceb.name
1856: ,enr.cert_enrollment_id
1857: ,enr.person_id

Line 1868: ,ota_cert_prd_enrollments prd

1864: ,ceb.initial_completion_date
1865: ,enr.object_version_number
1866: From ota_certifications_vl ceb
1867: ,ota_cert_enrollments enr
1868: ,ota_cert_prd_enrollments prd
1869: Where
1870: ceb.certification_id = enr.certification_id
1871: and enr.cert_enrollment_id = prd.cert_enrollment_id
1872: and trunc(sysdate) between nvl(trunc(ceb.start_date_active), trunc(sysdate))

Line 1877: from ota_cert_prd_enrollments cpex

1873: and nvl(trunc(ceb.end_date_active), trunc(sysdate))
1874: and enr.certification_status_code in ('ENROLLED', 'CERTIFIED', 'RENEWING') -- Bug#7303995
1875: --check if this is max/latest prd then update CRE accordingly
1876: and prd.cert_prd_enrollment_id = (select max(cpex.cert_prd_enrollment_id)
1877: from ota_cert_prd_enrollments cpex
1878: where cpex.cert_enrollment_id = enr.cert_enrollment_id
1879: and trunc(cpex.cert_period_end_date) < trunc(sysdate)
1880: )
1881: and prd.cert_prd_enrollment_id = p_cert_prd_enrollment_id;

Line 1975: from ota_cert_prd_enrollments cpe,

1971: cursor csr_cpe_status is
1972: select cpe.cert_prd_enrollment_id,
1973: cpe.period_status_code,
1974: cpe.cert_period_start_date
1975: from ota_cert_prd_enrollments cpe,
1976: ota_cert_enrollments cre
1977: where cpe.period_status_code not in ('COMPLETED', 'CANCELLED') and
1978: cpe.cert_enrollment_id = cre.cert_enrollment_id --bug#6338987
1979: order by cre.certification_id;

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

1977: where cpe.period_status_code not in ('COMPLETED', 'CANCELLED') and
1978: cpe.cert_enrollment_id = cre.cert_enrollment_id --bug#6338987
1979: order by cre.certification_id;
1980:
1981: cursor csr_acty_info(p_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type)
1982:
1983: is
1984: select cme.cert_mbr_enrollment_id,
1985: cmb.object_id

Line 1995: l_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%TYPE;

1991:
1992: l_booking_status_type ota_booking_status_types.type%TYPE;
1993: l_date_status_changed ota_delegate_bookings.date_status_changed%TYPE;
1994: do_update boolean := false;
1995: l_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%TYPE;
1996:
1997: begin
1998:
1999: hr_utility.set_location('Entering:'||l_proc, 5);

Line 2063: From ota_cert_prd_enrollments cpe

2059: Cursor csr_latest_cpe is
2060: Select cpe.period_status_code Period_Status_Code,
2061: ota_utility.get_lookup_meaning('OTA_CERT_PRD_ENROLL_STATUS', cpe.period_status_code, 810) Period_Status_Meaning,
2062: cpe.cert_prd_enrollment_id
2063: From ota_cert_prd_enrollments cpe
2064: where cpe.cert_enrollment_id = p_cert_enrollment_id
2065: -- and rownum = 1 --Bug#6356854
2066: order by cpe.cert_prd_enrollment_id desc;
2067:

Line 2170: ota_cert_prd_enrollments cpe,

2166: CURSOR csr_chk_prds(l_activity_version_id number) IS
2167: SELECT null
2168: FROM ota_certification_members cmb,
2169: ota_cert_mbr_enrollments cme,
2170: ota_cert_prd_enrollments cpe,
2171: ota_cert_enrollments cre
2172: WHERE
2173: cre.cert_enrollment_id = cpe.cert_enrollment_id
2174: AND cpe.cert_prd_enrollment_id = cme.cert_prd_enrollment_id

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

2297: ocpe.cert_prd_enrollment_id,
2298: ocpe.cert_period_start_date,
2299: ocpe.cert_period_end_date,
2300: ocpe.expiration_date
2301: FROM ota_certifications_vl ocb, ota_cert_enrollments oce, ota_cert_prd_enrollments ocpe
2302: WHERE ocb.certification_id = oce.certification_id
2303: AND oce.cert_enrollment_id = ocpe.cert_enrollment_id
2304: AND trunc(SYSDATE) BETWEEN trunc(ocb.start_date_active) AND trunc(nvl(ocb.end_date_active, SYSDATE))
2305: AND ocb.renewable_flag = 'Y'

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

2305: AND ocb.renewable_flag = 'Y'
2306: AND oce.person_id is not null
2307: AND oce.certification_status_code <> 'CERTIFIED'
2308: AND ocpe.period_status_code not in ('COMPLETED', 'CANCELLED')
2309: AND ocpe.cert_prd_enrollment_id = (SELECT MAX(cert_prd_enrollment_id) FROM ota_cert_prd_enrollments ocpe1
2310: WHERE oce.cert_enrollment_id = ocpe1.cert_enrollment_id);
2311:
2312: CURSOR csr_course_info(p_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type) is
2313: SELECT cme.cert_mbr_enrollment_id,

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

2308: AND ocpe.period_status_code not in ('COMPLETED', 'CANCELLED')
2309: AND ocpe.cert_prd_enrollment_id = (SELECT MAX(cert_prd_enrollment_id) FROM ota_cert_prd_enrollments ocpe1
2310: WHERE oce.cert_enrollment_id = ocpe1.cert_enrollment_id);
2311:
2312: CURSOR csr_course_info(p_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type) is
2313: SELECT cme.cert_mbr_enrollment_id,
2314: cmb.object_id
2315: FROM ota_cert_mbr_enrollments cme,
2316: ota_certification_members cmb

Line 2323: csr_cert_period_start_date in ota_cert_prd_enrollments.cert_period_start_date%type,

2319: AND object_type = 'H'
2320: AND member_status_code = 'PLANNED';
2321:
2322: CURSOR get_enrl_status(csr_activity_version_id IN ota_activity_versions.activity_version_id%TYPE,
2323: csr_cert_period_start_date in ota_cert_prd_enrollments.cert_period_start_date%type,
2324: csr_cert_period_end_date in ota_cert_prd_enrollments.cert_period_end_date%type,
2325: csr_person_id in ota_cert_enrollments.person_id%TYPE,
2326: csr_contact_id in ota_cert_enrollments.contact_id%TYPE) IS
2327: SELECT DECODE(bst.type,'C','Z',bst.type) status,

Line 2324: csr_cert_period_end_date in ota_cert_prd_enrollments.cert_period_end_date%type,

2320: AND member_status_code = 'PLANNED';
2321:
2322: CURSOR get_enrl_status(csr_activity_version_id IN ota_activity_versions.activity_version_id%TYPE,
2323: csr_cert_period_start_date in ota_cert_prd_enrollments.cert_period_start_date%type,
2324: csr_cert_period_end_date in ota_cert_prd_enrollments.cert_period_end_date%type,
2325: csr_person_id in ota_cert_enrollments.person_id%TYPE,
2326: csr_contact_id in ota_cert_enrollments.contact_id%TYPE) IS
2327: SELECT DECODE(bst.type,'C','Z',bst.type) status,
2328: evt.event_type,

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

2363: earliest_enroll_date
2364: FROM ota_cert_enrollments
2365: WHERE cert_enrollment_id = p_cert_enrollment_id;
2366:
2367: CURSOR csr_prd_enrl(csr_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type) IS
2368: select cert_enrollment_id, cert_prd_enrollment_id, period_status_code, object_version_number, completion_date
2369: FROM ota_cert_prd_enrollments
2370: where cert_prd_enrollment_id = csr_cert_prd_enrollment_id;
2371:

Line 2369: FROM ota_cert_prd_enrollments

2365: WHERE cert_enrollment_id = p_cert_enrollment_id;
2366:
2367: CURSOR csr_prd_enrl(csr_cert_prd_enrollment_id ota_cert_prd_enrollments.cert_prd_enrollment_id%type) IS
2368: select cert_enrollment_id, cert_prd_enrollment_id, period_status_code, object_version_number, completion_date
2369: FROM ota_cert_prd_enrollments
2370: where cert_prd_enrollment_id = csr_cert_prd_enrollment_id;
2371:
2372: l_cert_enrl_rec csr_cert_enrl%ROWTYPE;
2373: l_prd_enrl_rec csr_prd_enrl%ROWTYPE;

Line 2377: l_cert_period_start_date ota_cert_prd_enrollments.cert_period_start_date%TYPE;

2373: l_prd_enrl_rec csr_prd_enrl%ROWTYPE;
2374: l_booking_status_type ota_booking_status_types.type%TYPE;
2375: l_date_status_changed ota_delegate_bookings.date_status_changed%TYPE;
2376:
2377: l_cert_period_start_date ota_cert_prd_enrollments.cert_period_start_date%TYPE;
2378: l_cert_period_end_date ota_cert_prd_enrollments.cert_period_end_date%TYPE;
2379: l_cert_completion_date ota_cert_enrollments.completion_date%type;
2380: l_cert_prd_completion_date ota_cert_prd_enrollments.completion_date%type;
2381: l_earliest_enroll_date ota_cert_enrollments.earliest_enroll_date%type;

Line 2378: l_cert_period_end_date ota_cert_prd_enrollments.cert_period_end_date%TYPE;

2374: l_booking_status_type ota_booking_status_types.type%TYPE;
2375: l_date_status_changed ota_delegate_bookings.date_status_changed%TYPE;
2376:
2377: l_cert_period_start_date ota_cert_prd_enrollments.cert_period_start_date%TYPE;
2378: l_cert_period_end_date ota_cert_prd_enrollments.cert_period_end_date%TYPE;
2379: l_cert_completion_date ota_cert_enrollments.completion_date%type;
2380: l_cert_prd_completion_date ota_cert_prd_enrollments.completion_date%type;
2381: l_earliest_enroll_date ota_cert_enrollments.earliest_enroll_date%type;
2382: l_expiration_date ota_cert_prd_enrollments.expiration_date%type;

Line 2380: l_cert_prd_completion_date ota_cert_prd_enrollments.completion_date%type;

2376:
2377: l_cert_period_start_date ota_cert_prd_enrollments.cert_period_start_date%TYPE;
2378: l_cert_period_end_date ota_cert_prd_enrollments.cert_period_end_date%TYPE;
2379: l_cert_completion_date ota_cert_enrollments.completion_date%type;
2380: l_cert_prd_completion_date ota_cert_prd_enrollments.completion_date%type;
2381: l_earliest_enroll_date ota_cert_enrollments.earliest_enroll_date%type;
2382: l_expiration_date ota_cert_prd_enrollments.expiration_date%type;
2383:
2384: begin

Line 2382: l_expiration_date ota_cert_prd_enrollments.expiration_date%type;

2378: l_cert_period_end_date ota_cert_prd_enrollments.cert_period_end_date%TYPE;
2379: l_cert_completion_date ota_cert_enrollments.completion_date%type;
2380: l_cert_prd_completion_date ota_cert_prd_enrollments.completion_date%type;
2381: l_earliest_enroll_date ota_cert_enrollments.earliest_enroll_date%type;
2382: l_expiration_date ota_cert_prd_enrollments.expiration_date%type;
2383:
2384: begin
2385:
2386: FND_FILE.PUT_LINE(FND_FILE.LOG, 'Sync Late Subscription to certification with classes');