1. Tables and Views for HCM
  2. HWM_INTERACT_SET_QSNR_PCPT_V

HWM_INTERACT_SET_QSNR_PCPT_V

Details

  • Schema: FUSION

  • Object owner: HWM

  • Object type: VIEW

Columns

Name

ITR_SET_ID

ITR_SET_LN_ID

PROCESSING_ORDER

QSTNR_PARTICIPANT_ID

QSTNR_RESPONSE_ID

ATTEMPT_NUM

QSTNR_VERSION_NUM

OBJECT_VERSION_GSET

ITR_SET_NAME

ITR_SET_DESCRIPTION

ENTERPRISE_ID

QUESTIONNAIRE_CODE

DISPLAY_LEVEL

TIME_CATEGORY_ID

TRIGGER_EVENT_CS

OBJECT_VERSION_GSLN

BUSINESS_GROUP_ID

QUESTIONNAIRE_ID

SUBSCRIBER_ID

SUBJECT_ID

SUBJECT_CODE

PARTICIPANT_ID

PARTICIPANT_TYPE

STATUS_RESP

STATUS_PART

SUBMITTED_DATE_TIME_PART

SUBMITTED_DATE_TIME_RESP

SI_RESP_SRC

SI_QUESTIONNAIRE_ID

SI_ITR_SET_ID

SI_RESOURCE_ID

SI_SUB_RESOURCE_ID

SI_DATE_START

SI_DATE_END

SI_DATE_TIME

SI_DATE_TIME_CHAR

OBJECT_VERSION_QRESP

Query

SQL_Statement

SELECT gset.ITR_SET_ID,

gsln.ITR_SET_LN_ID,

gsln.PROCESSING_ORDER,

Qpart.QSTNR_PARTICIPANT_ID,

nvl( Qresp.QSTNR_RESPONSE_ID,0) AS QSTNR_RESPONSE_ID,

nvl( Qresp.ATTEMPT_NUM, -1) AS ATTEMPT_NUM,

Qresp.QSTNR_VERSION_NUM,

gset.OBJECT_VERSION_NUMBER AS OBJECT_VERSION_gset,

gset.ITR_SET_NAME,

gset.ITR_SET_DESCRIPTION,

gset.enterprise_id,

gsln.QUESTIONNAIRE_CODE,

gsln.DISPLAY_LEVEL,

gsln.TIME_CATEGORY_ID,

gsln.TRIGGER_EVENT_CS,

gsln.OBJECT_VERSION_NUMBER AS OBJECT_VERSION_gsln,

Qpart.BUSINESS_GROUP_ID,

Qpart.QUESTIONNAIRE_ID,

Qpart.SUBSCRIBER_ID ,

Qpart.SUBJECT_ID,

Qpart.SUBJECT_CODE,

Qpart.PARTICIPANT_ID,

Qpart.PARTICIPANT_TYPE,

Qresp.STATUS AS STATUS_RESP,

Qpart.STATUS AS STATUS_PART,

Qpart.SUBMITTED_DATE_TIME AS SUBMITTED_DATE_TIME_PART,

Qresp.SUBMITTED_DATE_TIME AS SUBMITTED_DATE_TIME_RESP,

Ltrim (RTRIM ( REGEXP_SUBSTR(Qpart.SUBJECT_ID, 'SRC<[^;]+>', 1 ,1), '>' ) , 'SRC<' ) AS SI_RESP_SRC,

TO_NUMBER(NVL(Ltrim (RTRIM ( REGEXP_SUBSTR(Qpart.SUBJECT_ID, 'QID<[^;]+>', 1 ,1), '>' ) , 'QID<' ),0)) AS SI_QUESTIONNAIRE_ID,

TO_NUMBER(NVL(Ltrim (RTRIM ( REGEXP_SUBSTR(Qpart.SUBJECT_ID, 'GID<[^;]+>', 1 ,1), '>' ) , 'GID<' ),0)) AS SI_ITR_SET_ID,

TO_NUMBER(NVL(Ltrim (RTRIM ( REGEXP_SUBSTR(Qpart.SUBJECT_ID, 'RES<[^;]+>', 1 ,1), '>' ) , 'RES<' ),0)) AS SI_RESOURCE_ID,

TO_NUMBER(NVL(Ltrim (RTRIM ( REGEXP_SUBSTR(Qpart.SUBJECT_ID, 'SUB<[^;]+>', 1 ,1), '>' ) , 'SUB<' ),0)) AS SI_SUB_RESOURCE_ID,

to_date (NVL(Ltrim (RTRIM ( REGEXP_SUBSTR(Qpart.SUBJECT_ID, 'DTS<[^;]+>', 1 ,1), '>' ) , 'DTS<' ),TRUNC(Qpart.CREATION_DATE)) ,'yyyy-MM-dd' ) AS SI_DATE_START,

to_date (NVL(Ltrim (RTRIM ( REGEXP_SUBSTR(Qpart.SUBJECT_ID, 'DTE<[^;]+>', 1 ,1), '>' ) , 'DTE<' ),TRUNC(Qpart.CREATION_DATE)) ,'yyyy-MM-dd' ) AS SI_DATE_END,

TO_TIMESTAMP (NVL(Ltrim (RTRIM ( REGEXP_SUBSTR(Qpart.SUBJECT_ID, 'TMS<[^;]+>', 1 ,1), '>' ) , 'TMS<' ),'1900-01-01 01:00:00') ,'yyyy-MM-dd HH24:MI:SS.FF' ) AS SI_DATE_TIME,

Ltrim (RTRIM ( REGEXP_SUBSTR(Qpart.SUBJECT_ID, 'TMS<[^;]+>', 1 ,1), '>' ) , 'TMS<' ) AS SI_DATE_TIME_CHAR,

Qresp.OBJECT_VERSION_NUMBER AS OBJECT_VERSION_Qresp

FROM HWM_INTERACT_SETS gset,

HWM_INTERACT_SET_LNS gsln,

HRQ_QSTNR_PARTICIPANTS Qpart,

HRQ_QSTNR_RESPONSES Qresp

WHERE gset.ITR_SET_ID = gsln.ITR_SET_ID

AND to_char(gsln.ITR_SET_ID) = Qpart.PARTICIPANT_type

AND gsln.QUESTIONNAIRE_ID = Qpart.QUESTIONNAIRE_ID

AND Qpart.QSTNR_PARTICIPANT_ID = Qresp.QSTNR_PARTICIPANT_ID (+)

AND Qpart.SUBSCRIBER_ID = 11