HRQ_QSTNR_PCPT_RESPONSES

Details

Schema: FUSION
Object owner: HRQ
Object type: VIEW

Columns

Name
QUESTIONNAIRE_CODE QSTNR_VERSION_NUM QSTNR_QUESTION_ID QSTNR_SECTION_ID KEEP_WITH_PREV RESPONSE_TYPE_ID MANDATORY QUESTION_CODE QUESTION_TYPE QSTNR_PARTICIPANT_ID QUESTIONNAIRE_ID PARTICIPANT_TYPE PARTICIPANT_ID SUBSCRIBER_ID SUBJECT_CODE SUBJECT_ID QSTNR_RESPONSE_ID ATTEMPT_NUM STATUS SUBMITTED_DATE_TIME CREATION_DATE LAST_UPDATE_DATE TOTAL_SCORE QSTN_RESPONSE_ID QSTN_ANSWER_ID SUB_QUESTION_ID ANSWER_CLOB ANSWER_LIST ANSWER_CODE LONG_TEXT QUESTION_ID SEQ_NUM CORRECT_FLAG RATING_LEVEL_ID SHORT_TEXT QSTN_VERSION_NUM ADDL_QSTNR_ID

Name

QUESTIONNAIRE_CODE

QSTNR_VERSION_NUM

QSTNR_QUESTION_ID

QSTNR_SECTION_ID

KEEP_WITH_PREV

RESPONSE_TYPE_ID

MANDATORY

QUESTION_CODE

QUESTION_TYPE

QSTNR_PARTICIPANT_ID

QUESTIONNAIRE_ID

PARTICIPANT_TYPE

PARTICIPANT_ID

SUBSCRIBER_ID

SUBJECT_CODE

SUBJECT_ID

QSTNR_RESPONSE_ID

ATTEMPT_NUM

STATUS

SUBMITTED_DATE_TIME

CREATION_DATE

LAST_UPDATE_DATE

TOTAL_SCORE

QSTN_RESPONSE_ID

QSTN_ANSWER_ID

SUB_QUESTION_ID

ANSWER_CLOB

ANSWER_LIST

ANSWER_CODE

LONG_TEXT

QUESTION_ID

SEQ_NUM

CORRECT_FLAG

RATING_LEVEL_ID

SHORT_TEXT

QSTN_VERSION_NUM

ADDL_QSTNR_ID

Query

SQL_Statement
SELECT qsnr.QUESTIONNAIRE_CODE, qsnr.QSTNR_VERSION_NUM, qqs.QSTNR_QUESTION_ID, qqs.QSTNR_SECTION_ID, qqs.KEEP_WITH_PREV, qqs.RESPONSE_TYPE_ID, qqs.MANDATORY, qsn.QUESTION_CODE, qsn.QUESTION_TYPE, qprt.QSTNR_PARTICIPANT_ID, qprt.QUESTIONNAIRE_ID, qprt.PARTICIPANT_TYPE, qprt.PARTICIPANT_ID, qprt.SUBSCRIBER_ID, qprt.SUBJECT_CODE, qprt.SUBJECT_ID, qrsp.QSTNR_RESPONSE_ID, qrsp.ATTEMPT_NUM, qrsp.STATUS, qrsp.SUBMITTED_DATE_TIME, qrsp.CREATION_DATE, qrsp.LAST_UPDATE_DATE, qrsp.TOTAL_SCORE, qid.QSTN_RESPONSE_ID, qid.QSTN_ANSWER_ID, qid.SUB_QUESTION_ID, qid.ANSWER_CLOB, qid.ANSWER_LIST, qans.ANSWER_CODE, qans.LONG_TEXT, qans.QUESTION_ID, qans.SEQ_NUM, qans.CORRECT_FLAG, qans.RATING_LEVEL_ID, qans.SHORT_TEXT, qans.QSTN_VERSION_NUM, qans.ADDL_QSTNR_ID FROM HRQ_QUESTIONNAIRES_B qsnr, HRQ_QSTNR_SECTIONS_B qsec, HRQ_QSTNR_QUESTIONS qqs, HRQ_QUESTIONS_B qsn, HRQ_QSTNR_PARTICIPANTS qprt, HRQ_QSTNR_RESPONSES qrsp, HRQ_QSTN_RESPONSES qid, HRQ_QSTN_ANSWERS_VL qans WHERE qsec.QUESTIONNAIRE_ID = qsnr.QUESTIONNAIRE_ID AND qsec.QSTNR_VERSION_NUM = qsnr.QSTNR_VERSION_NUM AND qsec.BUSINESS_GROUP_ID = qsnr.BUSINESS_GROUP_ID AND qsnr.QUESTIONNAIRE_ID = qprt.QUESTIONNAIRE_ID AND qsnr.BUSINESS_GROUP_ID = qprt.BUSINESS_GROUP_ID AND qqs.QSTNR_SECTION_ID = qsec.QSTNR_SECTION_ID AND qqs.BUSINESS_GROUP_ID = qsec.BUSINESS_GROUP_ID AND qqs.QUESTION_ID = qsn.QUESTION_ID AND qqs.QSTN_VERSION_NUM = qsn.QSTN_VERSION_NUM AND qqs.BUSINESS_GROUP_ID = qsn.BUSINESS_GROUP_ID AND qid.QSTNR_QUESTION_ID = qqs.QSTNR_QUESTION_ID AND qid.QSTN_ANSWER_ID = qans.QSTN_ANSWER_ID (+) AND qid.BUSINESS_GROUP_ID = qans.BUSINESS_GROUP_ID(+) AND qid.QSTNR_RESPONSE_ID = qrsp.QSTNR_RESPONSE_ID AND qprt.QSTNR_PARTICIPANT_ID = qrsp.QSTNR_PARTICIPANT_ID AND qprt.BUSINESS_GROUP_ID = qrsp.BUSINESS_GROUP_ID AND qrsp.STATUS = 'S' AND qrsp.ATTEMPT_NUM = (SELECT MAX(x.ATTEMPT_NUM) FROM HRQ_QSTNR_RESPONSES x WHERE x.QSTNR_PARTICIPANT_ID = qprt.QSTNR_PARTICIPANT_ID AND x.STATUS = 'S' ) ORDER BY PARTICIPANT_TYPE

SQL_Statement

SELECT qsnr.QUESTIONNAIRE_CODE,

qsnr.QSTNR_VERSION_NUM,

qqs.QSTNR_QUESTION_ID,

qqs.QSTNR_SECTION_ID,

qqs.KEEP_WITH_PREV,

qqs.RESPONSE_TYPE_ID,

qqs.MANDATORY,

qsn.QUESTION_CODE,

qsn.QUESTION_TYPE,

qprt.QSTNR_PARTICIPANT_ID,

qprt.QUESTIONNAIRE_ID,

qprt.PARTICIPANT_TYPE,

qprt.PARTICIPANT_ID,

qprt.SUBSCRIBER_ID,

qprt.SUBJECT_CODE,

qprt.SUBJECT_ID,

qrsp.QSTNR_RESPONSE_ID,

qrsp.ATTEMPT_NUM,

qrsp.STATUS,

qrsp.SUBMITTED_DATE_TIME,

qrsp.CREATION_DATE,

qrsp.LAST_UPDATE_DATE,

qrsp.TOTAL_SCORE,

qid.QSTN_RESPONSE_ID,

qid.QSTN_ANSWER_ID,

qid.SUB_QUESTION_ID,

qid.ANSWER_CLOB,

qid.ANSWER_LIST,

qans.ANSWER_CODE,

qans.LONG_TEXT,

qans.QUESTION_ID,

qans.SEQ_NUM,

qans.CORRECT_FLAG,

qans.RATING_LEVEL_ID,

qans.SHORT_TEXT,

qans.QSTN_VERSION_NUM,

qans.ADDL_QSTNR_ID

FROM HRQ_QUESTIONNAIRES_B qsnr,

HRQ_QSTNR_SECTIONS_B qsec,

HRQ_QSTNR_QUESTIONS qqs,

HRQ_QUESTIONS_B qsn,

HRQ_QSTNR_PARTICIPANTS qprt,

HRQ_QSTNR_RESPONSES qrsp,

HRQ_QSTN_RESPONSES qid,

HRQ_QSTN_ANSWERS_VL qans

WHERE qsec.QUESTIONNAIRE_ID = qsnr.QUESTIONNAIRE_ID

AND qsec.QSTNR_VERSION_NUM = qsnr.QSTNR_VERSION_NUM

AND qsec.BUSINESS_GROUP_ID = qsnr.BUSINESS_GROUP_ID

AND qsnr.QUESTIONNAIRE_ID = qprt.QUESTIONNAIRE_ID

AND qsnr.BUSINESS_GROUP_ID = qprt.BUSINESS_GROUP_ID

AND qqs.QSTNR_SECTION_ID = qsec.QSTNR_SECTION_ID

AND qqs.BUSINESS_GROUP_ID = qsec.BUSINESS_GROUP_ID

AND qqs.QUESTION_ID = qsn.QUESTION_ID

AND qqs.QSTN_VERSION_NUM = qsn.QSTN_VERSION_NUM

AND qqs.BUSINESS_GROUP_ID = qsn.BUSINESS_GROUP_ID

AND qid.QSTNR_QUESTION_ID = qqs.QSTNR_QUESTION_ID

AND qid.QSTN_ANSWER_ID = qans.QSTN_ANSWER_ID (+)

AND qid.BUSINESS_GROUP_ID = qans.BUSINESS_GROUP_ID(+)

AND qid.QSTNR_RESPONSE_ID = qrsp.QSTNR_RESPONSE_ID

AND qprt.QSTNR_PARTICIPANT_ID = qrsp.QSTNR_PARTICIPANT_ID

AND qprt.BUSINESS_GROUP_ID = qrsp.BUSINESS_GROUP_ID

AND qrsp.STATUS = 'S'

AND qrsp.ATTEMPT_NUM =

(SELECT MAX(x.ATTEMPT_NUM)

FROM HRQ_QSTNR_RESPONSES x

WHERE x.QSTNR_PARTICIPANT_ID = qprt.QSTNR_PARTICIPANT_ID

AND x.STATUS = 'S'

)

ORDER BY PARTICIPANT_TYPE