Find Operator Tests In DB

When the PCOCDS Web Service is called, rows get created in 3 tables:

1.      TM110_PCOCTO_OPERATOR linked by PCOCTO_OPERATOR_CD to

2.      LM109_EXAM_GRP_TOKEN linked by EXAM_GROUP_ID to

3.      TM104_EXAM_GROUP_VERSIONS

In order to find the Operator test, we use this table group and query the columns which identify the relationships.

The operator information can be obtained by contacting the course provider which is usually done by our program group.

We start by finding the Operator by using the query described in the previous process, then, using the value in the PCOCTO_OPERATOR_CD column in the result set, we find the token in the TM110_EXAM_GROUP_TOKEN table using the following:

SELECT *

FROM Lm109_Exam_Grp_Token

WHERE PCOCTO_OPERATOR_CD = '001122';

In this result set, you can see if the exam has been started by looking at the RESULT_STATUS_TYPE_CD column and the TIMER_REMAINING_NBR column values.

When starting an exam, the initial timer value is set at 75 (minutes) and the result status is at 4 (not started).  RESULT_STATUS_TYPE_CD values are as follows:

1.      1 = PASSED

2.      2 = FAILED

3.      3 = INCOMPLETE

4.      4 = NOT STARTED

Using the value in the EXAM_GROUP_ID column in the result set, we find the exam in the TM104_EXAM_GROUP_VERSIONS table using:

SELECT *

From Tm104_Exam_Group_Versions

Where Exam_Group_Id = '221199';

To verify if the exam was started, you can then query another table (not included in the above drawing) which only gets written to upon exam startup.  This table is called TM107_XREF_EXAM_GRP_QUESTION and it contains all questions and answers for the exam from the previous table (TM_104_EXAM_GROUP_VERSIONS).

SELECT *

FROM TM107_XREF_EXAM_GRP_QUESTION

where exam_group_id = '221199';