[Home] [Help]
25: cpe.cert_period_end_date,
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
93: tav.activity_version_id
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:
249: select cre.certification_status_code, cre.object_version_number, cre.completion_date,
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:
273:
274: l_complete_cert_prd_ok VARCHAR2(30);
275: l_period_status_code VARCHAR2(30);
276: n_period_status_code VARCHAR2(30) := 'ENROLLED';
277: l_certification_status_code ota_cert_enrollments.certification_status_code%type := 'ENROLLED';
278:
279: l_object_version_number1 number;
280: l_object_version_number2 number;
281: l_object_version_number3 number;
422: raise;
423: end update_cpe_status;
424:
425:
426: function is_period_renewable(p_cert_enrollment_id in ota_cert_enrollments.cert_enrollment_id%type)
427: return varchar2 is
428:
429: CURSOR csr_crt IS
430: select
432: , b.RENEWABLE_FLAG
433: , b.INITIAL_COMPLETION_DURATION
434: , cre.expiration_date
435: from ota_certifications_b b,
436: ota_cert_enrollments cre
437: where cre.certification_id = b.certification_id
438: and cre.cert_enrollment_id = p_cert_enrollment_id;
439:
440: CURSOR csr_max_cpe_exp_dt IS
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:
448: CURSOR csr_dupl_prd(csr_expiration_date ota_cert_enrollments.expiration_date%type) is
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:
448: CURSOR csr_dupl_prd(csr_expiration_date ota_cert_enrollments.expiration_date%type) is
449: select 'Y' as dupl_prd_exists
450: from ota_cert_enrollments
451: where cert_enrollment_id = p_cert_enrollment_id
452: and expiration_date = csr_expiration_date;
446: and cre.cert_enrollment_id = p_cert_enrollment_id;
447:
448: CURSOR csr_dupl_prd(csr_expiration_date ota_cert_enrollments.expiration_date%type) is
449: select 'Y' as dupl_prd_exists
450: from ota_cert_enrollments
451: where cert_enrollment_id = p_cert_enrollment_id
452: and expiration_date = csr_expiration_date;
453:
454: l_proc VARCHAR2(72) := g_package||'is_period_renewable';
452: and expiration_date = csr_expiration_date;
453:
454: l_proc VARCHAR2(72) := g_package||'is_period_renewable';
455:
456: l_earliest_enroll_date ota_cert_enrollments.earliest_enroll_date%type;
457: l_expiration_date ota_cert_enrollments.expiration_date%type;
458: l_curr_dt date;
459: l_dupl_prd_exists varchar2(1) := 'N';
460:
453:
454: l_proc VARCHAR2(72) := g_package||'is_period_renewable';
455:
456: l_earliest_enroll_date ota_cert_enrollments.earliest_enroll_date%type;
457: l_expiration_date ota_cert_enrollments.expiration_date%type;
458: l_curr_dt date;
459: l_dupl_prd_exists varchar2(1) := 'N';
460:
461: rec_crt csr_crt%rowtype;
530: RETURN null;
531:
532: End is_period_renewable;
533:
534: Function get_earl_enrl_dt(p_cert_enrollment_id in ota_cert_enrollments.cert_enrollment_id%type,
535: p_expiration_date in ota_cert_enrollments.expiration_date%type)
536: return date
537: IS
538:
531:
532: End is_period_renewable;
533:
534: Function get_earl_enrl_dt(p_cert_enrollment_id in ota_cert_enrollments.cert_enrollment_id%type,
535: p_expiration_date in ota_cert_enrollments.expiration_date%type)
536: return date
537: IS
538:
539: CURSOR csr_crt IS
552: , b.PUBLIC_FLAG
553: , b.START_DATE_ACTIVE
554: , b.END_DATE_ACTIVE
555: from ota_certifications_b b,
556: ota_cert_enrollments cre
557: where cre.certification_id = b.certification_id
558: and cre.cert_enrollment_id = p_cert_enrollment_id;
559:
560: l_proc VARCHAR2(72) := g_package||' get_earl_enrl_dt';
559:
560: l_proc VARCHAR2(72) := g_package||' get_earl_enrl_dt';
561: rec_crt csr_crt%rowtype;
562:
563: l_next_earliest_enroll_date ota_cert_enrollments.earliest_enroll_date%type;
564:
565:
566: BEGIN
567:
611: RETURN NULL;
612:
613: end get_earl_enrl_dt;
614:
615: PROCEDURE calc_cre_dates(p_cert_enrollment_id in ota_cert_enrollments.cert_enrollment_id%type,
616: p_certification_id in ota_cert_enrollments.certification_id%type,
617: p_mode in varchar2,
618: p_earliest_enroll_date OUT nocopy ota_cert_enrollments.earliest_enroll_date%type,
619: p_expiration_date OUT nocopy ota_cert_enrollments.expiration_date%type,
612:
613: end get_earl_enrl_dt;
614:
615: PROCEDURE calc_cre_dates(p_cert_enrollment_id in ota_cert_enrollments.cert_enrollment_id%type,
616: p_certification_id in ota_cert_enrollments.certification_id%type,
617: p_mode in varchar2,
618: p_earliest_enroll_date OUT nocopy ota_cert_enrollments.earliest_enroll_date%type,
619: p_expiration_date OUT nocopy ota_cert_enrollments.expiration_date%type,
620: p_cert_period_start_date in date default sysdate)
614:
615: PROCEDURE calc_cre_dates(p_cert_enrollment_id in ota_cert_enrollments.cert_enrollment_id%type,
616: p_certification_id in ota_cert_enrollments.certification_id%type,
617: p_mode in varchar2,
618: p_earliest_enroll_date OUT nocopy ota_cert_enrollments.earliest_enroll_date%type,
619: p_expiration_date OUT nocopy ota_cert_enrollments.expiration_date%type,
620: p_cert_period_start_date in date default sysdate)
621: IS
622:
615: PROCEDURE calc_cre_dates(p_cert_enrollment_id in ota_cert_enrollments.cert_enrollment_id%type,
616: p_certification_id in ota_cert_enrollments.certification_id%type,
617: p_mode in varchar2,
618: p_earliest_enroll_date OUT nocopy ota_cert_enrollments.earliest_enroll_date%type,
619: p_expiration_date OUT nocopy ota_cert_enrollments.expiration_date%type,
620: p_cert_period_start_date in date default sysdate)
621: IS
622:
623: CURSOR csr_crt IS
644: select
645: trunc(cre.earliest_enroll_date) earliest_enroll_date --bug#8775942
646: , trunc(cre.expiration_date) expiration_date
647: from ota_certifications_b b,
648: ota_cert_enrollments cre
649: where cre.certification_id = b.certification_id
650: and cre.cert_enrollment_id = p_cert_enrollment_id;
651:
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:
661: l_proc VARCHAR2(72) := g_package||'calc_cre_dates';
663: rec_cre csr_cre%rowtype;
664:
665: l_curr_dt date;
666:
667: l_earliest_enroll_date ota_cert_enrollments.earliest_enroll_date%type;
668: l_expiration_date ota_cert_enrollments.expiration_date%type;
669: l_max_expiration_date date;
670:
671: l_next_earliest_enroll_date ota_cert_enrollments.earliest_enroll_date%type;
664:
665: l_curr_dt date;
666:
667: l_earliest_enroll_date ota_cert_enrollments.earliest_enroll_date%type;
668: l_expiration_date ota_cert_enrollments.expiration_date%type;
669: l_max_expiration_date date;
670:
671: l_next_earliest_enroll_date ota_cert_enrollments.earliest_enroll_date%type;
672: l_next_expiration_date ota_cert_enrollments.expiration_date%type;
667: l_earliest_enroll_date ota_cert_enrollments.earliest_enroll_date%type;
668: l_expiration_date ota_cert_enrollments.expiration_date%type;
669: l_max_expiration_date date;
670:
671: l_next_earliest_enroll_date ota_cert_enrollments.earliest_enroll_date%type;
672: l_next_expiration_date ota_cert_enrollments.expiration_date%type;
673:
674: BEGIN
675: hr_utility.set_location('Entering :'||l_proc,5);
668: l_expiration_date ota_cert_enrollments.expiration_date%type;
669: l_max_expiration_date date;
670:
671: l_next_earliest_enroll_date ota_cert_enrollments.earliest_enroll_date%type;
672: l_next_expiration_date ota_cert_enrollments.expiration_date%type;
673:
674: BEGIN
675: hr_utility.set_location('Entering :'||l_proc,5);
676:
828: p_expiration_date := null;
829: RAISE;
830: END calc_cre_dates;
831:
832: Function get_next_prd_dur_days(p_cert_enrollment_id in ota_cert_enrollments.cert_enrollment_id%type,
833: p_cert_period_start_date in date default sysdate )
834: return varchar2
835: IS
836:
845: , b.END_DATE_ACTIVE
846: , cre.expiration_date
847: , b.renewable_flag --Bug 4545407
848: from ota_certifications_b b,
849: ota_cert_enrollments cre
850: where cre.certification_id = b.certification_id
851: and cre.cert_enrollment_id = p_cert_enrollment_id;
852:
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:
862: l_proc VARCHAR2(72) := g_package||' get_next_prd_dur_days';
863: rec_crt csr_crt%rowtype;
864:
865: l_curr_dt date;
866: l_prd_durn varchar2(10);
867: l_next_expiration_date ota_cert_enrollments.expiration_date%type;
868: l_max_expiration_date date;
869: l_due_date date;
870: l_elapsed_due_date date;
871:
995:
996: return l_member_name;
997: End get_cert_mbr_name;
998:
999: FUNCTION get_cre_status (p_cert_enrollment_id in ota_cert_enrollments.cert_enrollment_id%TYPE,
1000: p_mode in varchar2 default 'm')
1001: RETURN varchar2 is
1002:
1003: Cursor csr_cre_status is
1007: ota_utility.get_lookup_meaning('OTA_CERT_ENROLL_STATUS', cre.certification_status_code, 810) cre_status,
1008: crt.renewable_flag,
1009: cre.expiration_date,
1010: nvl(crt.end_date_active,to_date('4712/12/31','YYYY/MM/DD')) end_date_active
1011: From ota_cert_enrollments cre,
1012: ota_certifications_b crt
1013: where cre.cert_enrollment_id = p_cert_enrollment_id
1014: and crt.certification_id = cre.certification_id;
1015:
1079:
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:
1123: crt.renewable_flag,
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
1210: return l_return_status;
1211:
1212: END chk_prd_end_date;
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,
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
1222: select
1223: b.certification_id certification_id
1224: , b.INITIAL_COMPLETION_DATE
1225: , b.INITIAL_COMPLETION_DURATION
1236: , b.END_DATE_ACTIVE
1237: from ota_certifications_b b
1238: where b.certification_id = p_certification_id;
1239:
1240: CURSOR csr_cert_mbr(p_certification_id in ota_cert_enrollments.certification_id%type) IS
1241: select
1242: cmb.CERTIFICATION_MEMBER_ID
1243: , cmb.CERTIFICATION_ID
1244: , cmb.OBJECT_ID
1252:
1253: CURSOR csr_cert_enrl IS
1254: select certification_id, cert_enrollment_id, business_group_id, certification_status_code,
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
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),
1680: p_cert_upd_comp in varchar2 default 'N') is
1683: select cre.certification_status_code, cre.object_version_number, cre.completion_date,
1684: cre.certification_id,
1685: cre.person_id,
1686: cre.expiration_date
1687: FROM ota_cert_enrollments cre
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
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
1710:
1711: hr_utility.set_location(' Entering:' || l_proc,10);
1879: ,prd.period_status_code
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
1902: ,prd.cert_period_end_date
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
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;
2019:
2090: FND_FILE.PUT_LINE(FND_FILE.LOG,'Error occured in ' || l_proc
2091: ||','||SUBSTR(SQLERRM, 1, 500));
2092: end sync_cert_status_to_class_enrl;
2093: --
2094: FUNCTION get_latest_cpe_col(p_cert_enrollment_id in ota_cert_enrollments.cert_enrollment_id%TYPE,
2095: p_col_name in varchar2 default 'Period_Status_Meaning')
2096: RETURN varchar2 is
2097:
2098: Cursor csr_latest_cpe 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
2214: AND cme.cert_member_id = cmb.certification_member_id
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'
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,
2368: tdb.DATE_STATUS_CHANGED,
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,
2368: tdb.DATE_STATUS_CHANGED,
2369: evt.COURSE_START_DATE,
2391: OR (csr_contact_id IS NOT NULL AND tdb.delegate_contact_id = csr_contact_id)
2392: )
2393: order by status;
2394:
2395: CURSOR csr_cert_enrl(p_cert_enrollment_id ota_cert_enrollments.cert_enrollment_id%type) IS
2396: SELECT certification_id,
2397: cert_enrollment_id,
2398: business_group_id,
2399: certification_status_code,
2399: certification_status_code,
2400: object_version_number,
2401: completion_date,
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
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;
2422:
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
2424: