IRC_PROFILE_CERT_ITEMS

Details

Schema: FUSION
Object owner: IRC
Object type: VIEW

Columns

Name
CERTIFICATION_ID BUSINESS_GROUP_ID PROFILE_ID PARENT_PROFILE_ITEM_ID SECTION_ID CONTENT_TYPE_ID CONTENT_ITEM_ID CERTIFICATION_NAME STATE_PROVINCE_ID COUNTRY_ID ISSUED_BY CERTIFICATION_NUMBER ISSUE_DATE ORIGINAL_ISSUE_YEAR EXPIRATION_DATE RENEWAL_DATE LAST_RENEWAL_DATE RENEWAL_INPROGRESS_FLAG RENEWAL_REQUIRED_FLAG ACTUAL_COMPLETION_DATE STATUS VERIFIED_FLAG COMMENTS EDUCATIONAL_ESTABLISHMENT_ID EDUCATIONAL_ESTABLISHMENT COMPLETED_TRAINING_UNITS RESTRICTIONS TOTAL_AMOUNT COMPLETED_AMOUNT CERTIFICATION_URL TITLE IMPORTANCE DATE_FROM DATE_TO OBJECT_VERSION_NUMBER CREATED_BY CREATION_DATE LAST_UPDATED_BY LAST_UPDATE_DATE LAST_UPDATE_LOGIN STATE_PROVINCE_CODE COUNTRY_CODE

Name

CERTIFICATION_ID

BUSINESS_GROUP_ID

PROFILE_ID

PARENT_PROFILE_ITEM_ID

SECTION_ID

CONTENT_TYPE_ID

CONTENT_ITEM_ID

CERTIFICATION_NAME

STATE_PROVINCE_ID

COUNTRY_ID

ISSUED_BY

CERTIFICATION_NUMBER

ISSUE_DATE

ORIGINAL_ISSUE_YEAR

EXPIRATION_DATE

RENEWAL_DATE

LAST_RENEWAL_DATE

RENEWAL_INPROGRESS_FLAG

RENEWAL_REQUIRED_FLAG

ACTUAL_COMPLETION_DATE

STATUS

VERIFIED_FLAG

COMMENTS

EDUCATIONAL_ESTABLISHMENT_ID

EDUCATIONAL_ESTABLISHMENT

COMPLETED_TRAINING_UNITS

RESTRICTIONS

TOTAL_AMOUNT

COMPLETED_AMOUNT

CERTIFICATION_URL

TITLE

IMPORTANCE

DATE_FROM

DATE_TO

OBJECT_VERSION_NUMBER

CREATED_BY

CREATION_DATE

LAST_UPDATED_BY

LAST_UPDATE_DATE

LAST_UPDATE_LOGIN

STATE_PROVINCE_CODE

COUNTRY_CODE

Query

SQL_Statement
SELECT PROFILE_ITEM_ID AS CERTIFICATION_ID , BUSINESS_GROUP_ID , PROFILE_ID , PARENT_PROFILE_ITEM_ID , SECTION_ID , CONTENT_TYPE_ID , CONTENT_ITEM_ID AS CONTENT_ITEM_ID , ITEM_TEXT240_3 AS CERTIFICATION_NAME , STATE_PROVINCE_ID AS STATE_PROVINCE_ID , COUNTRY_ID AS COUNTRY_ID , ITEM_TEXT240_4 AS ISSUED_BY , ITEM_TEXT240_2 AS CERTIFICATION_NUMBER , ITEM_DATE_6 AS ISSUE_DATE , ITEM_NUMBER_4 AS ORIGINAL_ISSUE_YEAR , ITEM_DATE_3 AS EXPIRATION_DATE , ITEM_DATE_4 AS RENEWAL_DATE , ITEM_DATE_8 AS LAST_RENEWAL_DATE , ITEM_TEXT30_5 AS RENEWAL_INPROGRESS_FLAG , NVL(ITEM_TEXT30_1,ITEM_TEXT30_4) AS RENEWAL_REQUIRED_FLAG , ITEM_DATE_9 AS ACTUAL_COMPLETION_DATE , ITEM_TEXT30_8 AS STATUS , ITEM_TEXT30_6 AS VERIFIED_FLAG , NVL(ITEM_CLOB_1,TO_CLOB(ITEM_TEXT2000_3)) AS COMMENTS , ITEM_NUMBER_9 AS EDUCATIONAL_ESTABLISHMENT_ID , ITEM_TEXT2000_1 AS EDUCATIONAL_ESTABLISHMENT , ITEM_TEXT240_7 AS COMPLETED_TRAINING_UNITS , NVL(ITEM_CLOB_2,TO_CLOB(ITEM_TEXT2000_4)) AS RESTRICTIONS , ITEM_NUMBER_3 AS TOTAL_AMOUNT , ITEM_NUMBER_2 AS COMPLETED_AMOUNT , ITEM_TEXT2000_2 AS CERTIFICATION_URL , ITEM_TEXT240_1 AS TITLE , IMPORTANCE AS IMPORTANCE, DATE_FROM, DATE_TO, OBJECT_VERSION_NUMBER , CREATED_BY , CREATION_DATE , LAST_UPDATED_BY , LAST_UPDATE_DATE , LAST_UPDATE_LOGIN, STATE_PROVINCE_CODE AS STATE_PROVINCE_CODE , COUNTRY_CODE AS COUNTRY_CODE FROM HRT_PROFILE_ITEMS PI WHERE CONTENT_TYPE_ID = 103 AND SECTION_ID IN (SELECT SECTION_ID FROM IRC_PROFILE_CONT_SECTIONS_V PTS WHERE PTS.PROFILE_TYPE_ID = 1 AND PTS.CONTENT_TYPE_ID = 103 AND PTS.BUSINESS_GROUP_ID = PI.BUSINESS_GROUP_ID)

SQL_Statement

SELECT PROFILE_ITEM_ID AS CERTIFICATION_ID ,

BUSINESS_GROUP_ID ,

PROFILE_ID ,

PARENT_PROFILE_ITEM_ID ,

SECTION_ID ,

CONTENT_TYPE_ID ,

CONTENT_ITEM_ID AS CONTENT_ITEM_ID ,

ITEM_TEXT240_3 AS CERTIFICATION_NAME ,

STATE_PROVINCE_ID AS STATE_PROVINCE_ID ,

COUNTRY_ID AS COUNTRY_ID ,

ITEM_TEXT240_4 AS ISSUED_BY ,

ITEM_TEXT240_2 AS CERTIFICATION_NUMBER ,

ITEM_DATE_6 AS ISSUE_DATE ,

ITEM_NUMBER_4 AS ORIGINAL_ISSUE_YEAR ,

ITEM_DATE_3 AS EXPIRATION_DATE ,

ITEM_DATE_4 AS RENEWAL_DATE ,

ITEM_DATE_8 AS LAST_RENEWAL_DATE ,

ITEM_TEXT30_5 AS RENEWAL_INPROGRESS_FLAG ,

NVL(ITEM_TEXT30_1,ITEM_TEXT30_4) AS RENEWAL_REQUIRED_FLAG ,

ITEM_DATE_9 AS ACTUAL_COMPLETION_DATE ,

ITEM_TEXT30_8 AS STATUS ,

ITEM_TEXT30_6 AS VERIFIED_FLAG ,

NVL(ITEM_CLOB_1,TO_CLOB(ITEM_TEXT2000_3)) AS COMMENTS ,

ITEM_NUMBER_9 AS EDUCATIONAL_ESTABLISHMENT_ID ,

ITEM_TEXT2000_1 AS EDUCATIONAL_ESTABLISHMENT ,

ITEM_TEXT240_7 AS COMPLETED_TRAINING_UNITS ,

NVL(ITEM_CLOB_2,TO_CLOB(ITEM_TEXT2000_4)) AS RESTRICTIONS ,

ITEM_NUMBER_3 AS TOTAL_AMOUNT ,

ITEM_NUMBER_2 AS COMPLETED_AMOUNT ,

ITEM_TEXT2000_2 AS CERTIFICATION_URL ,

ITEM_TEXT240_1 AS TITLE ,

IMPORTANCE AS IMPORTANCE,

DATE_FROM,

DATE_TO,

OBJECT_VERSION_NUMBER ,

CREATED_BY ,

CREATION_DATE ,

LAST_UPDATED_BY ,

LAST_UPDATE_DATE ,

LAST_UPDATE_LOGIN,

STATE_PROVINCE_CODE AS STATE_PROVINCE_CODE ,

COUNTRY_CODE AS COUNTRY_CODE

FROM HRT_PROFILE_ITEMS PI

WHERE CONTENT_TYPE_ID = 103

AND SECTION_ID IN (SELECT SECTION_ID FROM IRC_PROFILE_CONT_SECTIONS_V PTS WHERE PTS.PROFILE_TYPE_ID = 1 AND PTS.CONTENT_TYPE_ID = 103 AND PTS.BUSINESS_GROUP_ID = PI.BUSINESS_GROUP_ID)