PAY_CIR_LOOKUPS_VL

Details

• Schema: FUSION

• Object owner: PAY

• Object type: VIEW

Columns

Name
LOOKUP_TYPE LOOKUP_CODE MEANING DESCRIPTION ENABLED_FLAG DISPLAY_SEQUENCE START_DATE_ACTIVE END_DATE_ACTIVE TAG CREATED_BY CREATION_DATE LAST_UPDATED_BY LAST_UPDATE_LOGIN LAST_UPDATE_DATE MEANING_WITH_TAG EXTENDED_LOOKUP_CODE_NAME

Query

SQL_Statement

SELECT LOOKUP_TYPE, LOOKUP_CODE, MEANING, DESCRIPTION, ENABLED_FLAG, DISPLAY_SEQUENCE, START_DATE_ACTIVE, END_DATE_ACTIVE, TAG, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, LAST_UPDATE_DATE, CASE WHEN TAG IS NULL THEN NVL(EXTENDED_LOOKUP_CODE_NAME,MEANING) ELSE NVL(EXTENDED_LOOKUP_CODE_NAME,MEANING) || ' (' || TAG || ')' END MEANING_WITH_TAG, EXTENDED_LOOKUP_CODE_NAME FROM ( SELECT A.LOOKUP_TYPE, A.LOOKUP_CODE, A.MEANING, A.DESCRIPTION, A.ENABLED_FLAG, A.DISPLAY_SEQUENCE, A.START_DATE_ACTIVE, NVL2(( SELECT MIN(INFORMATION_DATE1) FROM HCM_EXTENDED_LOOKUP_CODES_VL B WHERE B.LOOKUP_TYPE = A.LOOKUP_TYPE AND B.LOOKUP_CODE = A.LOOKUP_CODE AND INFORMATION_DATE1 IS NOT NULL AND INFORMATION_DATE2 IS NOT NULL ),(( SELECT MIN(INFORMATION_DATE1) FROM HCM_EXTENDED_LOOKUP_CODES_VL B WHERE B.LOOKUP_TYPE = A.LOOKUP_TYPE AND B.LOOKUP_CODE = A.LOOKUP_CODE AND INFORMATION_DATE1 IS NOT NULL AND INFORMATION_DATE2 IS NOT NULL )) - 1, A.END_DATE_ACTIVE) END_DATE_ACTIVE, A.TAG, A.CREATED_BY, A.CREATION_DATE, A.LAST_UPDATED_BY, A.LAST_UPDATE_LOGIN, A.LAST_UPDATE_DATE, NULL AS MEANING_WITH_TAG, NULL AS EXTENDED_LOOKUP_CODE_NAME FROM HCM_LOOKUPS A UNION SELECT B.LOOKUP_TYPE, B.LOOKUP_CODE, B.EXTENDED_LOOKUP_CODE_NAME, NULL AS DESCRIPTION, NULL AS ENABLED_FLAG, NULL AS DISPLAY_SEQUENCE, B.INFORMATION_DATE1, B.INFORMATION_DATE2, NULL AS TAG, B.CREATED_BY, B.CREATION_DATE, B.LAST_UPDATED_BY, B.LAST_UPDATE_LOGIN, B.LAST_UPDATE_DATE, NULL AS MEANING_WITH_TAG, B.EXTENDED_LOOKUP_CODE_NAME FROM HCM_EXTENDED_LOOKUP_CODES_VL B ) ORDER BY LOOKUP_TYPE, LOOKUP_CODE, START_DATE_ACTIVE, END_DATE_ACTIVE