[Home] [Help]
424: rel.subject_table_name = 'HZ_PARTIES' and
425: rel.object_table_name = 'HZ_PARTIES';
426:
427: l_event_id OTA_EVENTS.EVENT_ID%TYPE:= null;
428: l_test_id OTA_TESTS.TEST_ID%type:=null;
429: Cursor csr_attempt_info is
430: select attempt_id from ota_attempts where
431: event_id = l_event_id
432: and test_id = l_test_id
449: and evt.event_id = l_event_id
450: and (evt_eval.evaluation_id is not null or act_eval.evaluation_id is not null); --7172501
451:
452: l_proc varchar2(72) := g_package||'update_enroll_status_for_lo';
453: l_act_eval_id OTA_TESTS.TEST_ID%TYPE:=null;
454: l_evt_eval_id OTA_TESTS.TEST_ID%TYPE:=null;
455: l_eval_mand_flag varchar2(1);
456: l_dummy ota_attempts.attempt_id%type;
457: l_is_attempted boolean :=false;
450: and (evt_eval.evaluation_id is not null or act_eval.evaluation_id is not null); --7172501
451:
452: l_proc varchar2(72) := g_package||'update_enroll_status_for_lo';
453: l_act_eval_id OTA_TESTS.TEST_ID%TYPE:=null;
454: l_evt_eval_id OTA_TESTS.TEST_ID%TYPE:=null;
455: l_eval_mand_flag varchar2(1);
456: l_dummy ota_attempts.attempt_id%type;
457: l_is_attempted boolean :=false;
458: l_status varchar2(80) :='ATTENDED';
1065: p_user_id fnd_user.user_id%type,
1066: p_user_type ota_attempts.user_type%type,
1067: p_cert_prd_enrollment_id ota_attempts.cert_prd_enrollment_id%type default null) return boolean is
1068:
1069: v_max_attempts ota_tests.max_attempts%type;
1070: v_user_attempts number;
1071: begin
1072: begin
1073: select t.max_attempts
1071: begin
1072: begin
1073: select t.max_attempts
1074: into v_max_attempts
1075: from ota_tests t, ota_learning_objects lo
1076: where lo.learning_object_id = p_lo_id and
1077: lo.test_id = t.test_id;
1078: exception
1079: when NO_DATA_FOUND then -- the LO is not a test, so no attempt limit exists
1087: -- Count the user's attempts
1088: begin
1089: select count(*)
1090: into v_user_attempts
1091: from ota_attempts a, ota_tests t
1092: where a.user_id = p_user_id and
1093: a.user_type = p_user_type and
1094: a.learning_object_id = p_lo_id and
1095: nvl(a.cert_prd_enrollment_id, -1) = nvl(p_cert_prd_enrollment_id, -1) and
1121: v_earliest_attempt_date date;
1122: begin
1123: select nvl(max(a.timestamp), sysdate) + nvl(t.duration_between_attempt, 0)
1124: into v_earliest_attempt_date
1125: from ota_tests t,
1126: ota_learning_objects lo,
1127: ota_attempts a
1128: where t.test_id = a.test_id
1129: and a.initialized_flag = 'Y'
1842:
1843: --7622768
1844: cursor lo_type is
1845: select nvl(test_type_flag,'LO')
1846: from ota_tests ot,
1847: ota_learning_objects lo
1848: where ot.test_id(+) = lo.test_id
1849: and lo.learning_object_id = p_lo_id;
1850:
1929: l_formatted_sec varchar(20) := '';
1930: l_formatted_time varchar(20) := '';
1931: l_return_tree_title varchar(500) := '';
1932: l_max_performance_id ota_performances.performance_id%type;
1933: l_tst_grade_flag ota_tests.grade_flag%TYPE;
1934: l_var_score VARCHAR2(100);
1935: l_lo_completed_date VARCHAR2(100);
1936: l_lo_completed_time VARCHAR2(10);
1937: l_lo_completed_date_tz VARCHAR2(100);
1959: , to_char(opf.completed_date, 'HH24:MI:SS')
1960: , opf.lesson_status
1961: , ota_timezone_util.get_date_time(trunc(opf.completed_date), to_char(opf.completed_date, 'HH24:MI:SS'), 'Y', ota_timezone_util.get_server_timezone_code, 'HH24:MI:SS') Comp_Date_Tz
1962: From OTA_LEARNING_OBJECTS olo, OTA_PERFORMANCES opf,
1963: OTA_TESTS tst
1964: Where
1965: olo.learning_object_id = opf.learning_object_id(+)
1966: AND tst.test_id(+) = olo.test_id
1967: And olo.Learning_object_id = p_lo_id
2342: p_booking_status_type_id OTA_DELEGATE_BOOKINGS.BOOKING_STATUS_TYPE_ID%TYPE,
2343: p_object_id OTA_EVALUATIONS.OBJECT_ID%TYPE,
2344: p_object_type OTA_EVALUATIONS.OBJECT_TYPE%TYPE,
2345: p_mand_flag OTA_EVALUATIONS.EVAL_MANDATORY_FLAG%TYPE,
2346: p_test_id OTA_TESTS.TEST_ID%TYPE) return varchar2 is
2347:
2348: l_is_mandatory boolean:= true;
2349: l_is_attempted boolean:= false;
2350: l_act_mandatory OTA_EVALUATIONS.EVAL_MANDATORY_FLAG%TYPE;
2446: p_event_id OTA_EVENTS.EVENT_ID%TYPE,
2447: p_user_id fnd_user.user_id%type,
2448: p_booking_status_type_id OTA_DELEGATE_BOOKINGS.BOOKING_STATUS_TYPE_ID%TYPE,
2449: p_mand_flag OTA_EVALUATIONS.EVAL_MANDATORY_FLAG%TYPE,
2450: p_test_id OTA_TESTS.TEST_ID%TYPE) return varchar2 is
2451:
2452: l_is_mandatory boolean:= true;
2453: l_is_attempted boolean:= false;
2454: l_act_mandatory OTA_EVALUATIONS.EVAL_MANDATORY_FLAG%TYPE;
3674: l_formatted_sec varchar(20) := '';
3675: l_formatted_time varchar(20) := '';
3676: l_return_tree_title varchar(500) := '';
3677: l_max_performance_id ota_performances.performance_id%type;
3678: l_tst_grade_flag ota_tests.grade_flag%TYPE;
3679: l_var_score VARCHAR2(100);
3680: l_lo_completed_date VARCHAR2(100);
3681: l_lo_completed_time VARCHAR2(10);
3682: l_lo_completed_date_tz VARCHAR2(100);
3704: , to_char(opf.completed_date, 'HH24:MI:SS')
3705: , opf.lesson_status
3706: , ota_timezone_util.get_date_time(trunc(opf.completed_date), to_char(opf.completed_date, 'HH24:MI:SS'), 'Y', ota_timezone_util.get_server_timezone_code, 'HH24:MI:SS') Comp_Date_Tz
3707: From OTA_LEARNING_OBJECTS olo, OTA_PERFORMANCES opf,
3708: OTA_TESTS tst
3709: Where
3710: olo.learning_object_id = opf.learning_object_id(+)
3711: AND tst.test_id(+) = olo.test_id
3712: And olo.Learning_object_id = p_lo_id