1. Tables and Views for HCM
  2. HWM_TM_REP_ATRB_VARCHAR_VAL_V

HWM_TM_REP_ATRB_VARCHAR_VAL_V

Details

  • Schema: FUSION

  • Object owner: HWM

  • Object type: VIEW

Columns

Name

TM_REC_ID

TM_REC_VERSION

TM_REP_ATRB_ID

ATTRIBUTE_NAME

ATTRIBUTE_VARCHAR_VALUE

TM_ATRB_FLD_ID

OBJECT_VERSION_NUMBER

ENTERPRISE_ID

MODULE_ID

TCSMRS_ID

NAME

CLASS

PARENT_TM_ATRB_FLD_ID

GLOBAL_TM_ATRB_FLD_ID

VALUE_LOCATION

ATTRIBUTE_CATEGORY

ALLOWED_SCOPE

MANDATORY_FOR_TCSMRS

DESCRIPTION

ATTRIBUTE_TYPE

ATTRIBUTE_GROUP

VALUE_SET_ID

COMP_DISP_CODE

CREATED_BY

CREATION_DATE

LAST_UPDATE_DATE

LAST_UPDATE_LOGIN

LAST_UPDATED_BY

Query

SQL_Statement

SELECT TM_REC_ID,

TM_REC_VERSION,

TM_REP_ATRB_ID,

ATTRIBUTE_NAME,

ATTRIBUTE_VARCHAR_VALUE,

TM_ATRB_FLD_ID,

OBJECT_VERSION_NUMBER,

ENTERPRISE_ID,

MODULE_ID,

TCSMRS_ID,

NAME,

CLASS,

PARENT_TM_ATRB_FLD_ID,

GLOBAL_TM_ATRB_FLD_ID,

VALUE_LOCATION,

ATTRIBUTE_CATEGORY,

ALLOWED_SCOPE,

MANDATORY_FOR_TCSMRS,

DESCRIPTION,

ATTRIBUTE_TYPE,

ATTRIBUTE_GROUP,

VALUE_SET_ID,

COMP_DISP_CODE,

CREATED_BY,

CREATION_DATE,

LAST_UPDATE_DATE,

LAST_UPDATE_LOGIN,

LAST_UPDATED_BY

FROM

(SELECT U.USAGES_SOURCE_ID TM_REC_ID,

U.USAGES_SOURCE_VERSION TM_REC_VERSION,

U.TM_REP_ATRB_ID,

NVL(TimeAttributeField.DISPLAY_NAME,DETINS.DISPLAY_NAME2) ATTRIBUTE_NAME,

ATT.VARCHAR_ATT_VALUE ATTRIBUTE_VARCHAR_VALUE,

NVL(TimeAttributeField.TM_ATRB_FLD_ID, DETINS.TM_ATRB_FLD_ID2) TM_ATRB_FLD_ID,

NVL(TimeAttributeField.OBJECT_VERSION_NUMBER, DETINS.OBJECT_VERSION_NUMBER) OBJECT_VERSION_NUMBER,

NVL(TimeAttributeField.ENTERPRISE_ID, DETINS.ENTERPRISE_ID) ENTERPRISE_ID,

NVL(TimeAttributeField.MODULE_ID, DETINS.MODULE_ID) MODULE_ID,

NVL(TimeAttributeField.TCSMRS_ID, DETINS.TCSMRS_ID) TCSMRS_ID,

NVL(TimeAttributeField.NAME, DETINS.NAME2) NAME,

NVL(TimeAttributeField.CLASS, DETINS.CLASS) CLASS,

NVL(TimeAttributeField.PARENT_TM_ATRB_FLD_ID, DETINS.PARENT_TM_ATRB_FLD_ID) PARENT_TM_ATRB_FLD_ID,

NVL(TimeAttributeField.GLOBAL_TM_ATRB_FLD_ID, DETINS.GLOBAL_TM_ATRB_FLD_ID) GLOBAL_TM_ATRB_FLD_ID,

NVL(TimeAttributeField.VALUE_LOCATION, DETINS.VALUE_LOCATION) VALUE_LOCATION,

NVL(TimeAttributeField.ATTRIBUTE_CATEGORY, DETINS.ATTRIBUTE_CATEGORY) ATTRIBUTE_CATEGORY,

NVL(TimeAttributeField.ALLOWED_SCOPE, DETINS.ALLOWED_SCOPE) ALLOWED_SCOPE,

NVL(TimeAttributeField.MANDATORY_FOR_TCSMRS, DETINS.MANDATORY_FOR_TCSMRS) MANDATORY_FOR_TCSMRS,

NVL(TimeAttributeField.DESCRIPTION, DETINS.DESCRIPTION2) DESCRIPTION,

NVL(TimeAttributeField.ATTRIBUTE_TYPE, DETINS.ATTRIBUTE_TYPE) ATTRIBUTE_TYPE,

NVL(TimeAttributeField.ATTRIBUTE_GROUP, DETINS.ATTRIBUTE_GROUP) ATTRIBUTE_GROUP,

NVL(TimeAttributeField.VALUE_SET_ID, DETINS.VALUE_SET_ID) VALUE_SET_ID,

NVL(TimeAttributeField.COMP_DISP_CODE, DETINS.COMP_DISP_CODE) COMP_DISP_CODE,

NVL(TimeAttributeField.CREATED_BY, DETINS.CREATED_BY) CREATED_BY,

NVL(TimeAttributeField.CREATION_DATE, DETINS.CREATION_DATE) CREATION_DATE,

NVL(TimeAttributeField.LAST_UPDATE_DATE, DETINS.LAST_UPDATE_DATE) LAST_UPDATE_DATE,

NVL(TimeAttributeField.LAST_UPDATE_LOGIN, DETINS.LAST_UPDATE_LOGIN) LAST_UPDATE_LOGIN,

NVL(TimeAttributeField.LAST_UPDATED_BY, DETINS.LAST_UPDATED_BY) LAST_UPDATED_BY

FROM

(SELECT TM_REP_ATRB_ID,

ATTRIBUTE_CATEGORY,

MASTER_ATTRIBUTE_ID,

VALUE_LOC,

VARCHAR_ATT_VALUE

FROM HWM_TM_REP_ATRBS unpivot (VARCHAR_ATT_VALUE FOR VALUE_LOC IN (ATTRIBUTE_VARCHAR1 AS 'AttributeVarchar1', ATTRIBUTE_VARCHAR2 AS 'AttributeVarchar2', ATTRIBUTE_VARCHAR3 AS 'AttributeVarchar3', ATTRIBUTE_VARCHAR4 AS 'AttributeVarchar4', ATTRIBUTE_VARCHAR5 AS 'AttributeVarchar5', ATTRIBUTE_VARCHAR6 AS 'AttributeVarchar6', ATTRIBUTE_VARCHAR7 AS 'AttributeVarchar7', ATTRIBUTE_VARCHAR8 AS 'AttributeVarchar8', ATTRIBUTE_VARCHAR9 AS 'AttributeVarchar9', ATTRIBUTE_VARCHAR10 AS 'AttributeVarchar10'))

) ATT

INNER JOIN HWM_TM_REP_ATRB_USAGES U

ON ATT.TM_REP_ATRB_ID = U.TM_REP_ATRB_ID

LEFT JOIN HWM_TM_ATRB_FLDS_VL TimeAttributeField

ON TimeAttributeField.CLASS = 'SIMPLE'

AND TimeAttributeField.VALUE_LOCATION = ATT.VALUE_LOC

AND TimeAttributeField.ATTRIBUTE_CATEGORY = ATT.ATTRIBUTE_CATEGORY

LEFT JOIN

(SELECT NVL(GlobalDetTimeAttributeFieldPEO.TM_ATRB_FLD_ID,TimeAttributeFieldMasterRefe1.TM_ATRB_FLD_ID) TM_ATRB_FLD_ID2,

TimeAttributeFieldMasterRefe1.DET_INS_VALUE_LOCATION VALUE_LOC2,

TimeAttributeFieldMasterRefe1.MASTER_INSTANCE_IDENTIFIER MASTER_INST_ID,

MasterTimeAttributeFieldPEO.TM_ATRB_FLD_ID TM_ATRB_FLD_ID3,

NVL(GlobalDetTimeAttributeFieldPEO.DISPLAY_NAME,DetInsTimeAttributeFieldPEO.DISPLAY_NAME) DISPLAY_NAME2,

NVL(GlobalDetTimeAttributeFieldPEO.NAME,DetInsTimeAttributeFieldPEO.NAME) NAME2,

NVL(GlobalDetTimeAttributeFieldPEO.DESCRIPTION,DetInsTimeAttributeFieldPEO.DESCRIPTION) DESCRIPTION2,

DetailTimeAttributeFieldPEO.*

FROM HWM_TM_ATRB_FLD_MSTR_REF_VL TimeAttributeFieldMasterRefe1,

HWM_TM_ATRB_FLDS_VL DetInsTimeAttributeFieldPEO,

HWM_TM_ATRB_FLDS_VL MasterTimeAttributeFieldPEO,

HWM_TM_ATRB_FLDS_VL DetailTimeAttributeFieldPEO,

HWM_TM_ATRB_FLDS_VL GlobalDetTimeAttributeFieldPEO

WHERE MasterTimeAttributeFieldPEO.CLASS = 'MASTER'

AND DetailTimeAttributeFieldPEO.CLASS = 'DETAIL'

AND DetInsTimeAttributeFieldPEO.CLASS ='DETAIL_INSTANCE'

AND DetailTimeAttributeFieldPEO.PARENT_TM_ATRB_FLD_ID = MasterTimeAttributeFieldPEO.TM_ATRB_FLD_ID

AND DetInsTimeAttributeFieldPEO.PARENT_TM_ATRB_FLD_ID = DetailTimeAttributeFieldPEO.TM_ATRB_FLD_ID

AND TimeAttributeFieldMasterRefe1.TM_ATRB_FLD_ID = DetInsTimeAttributeFieldPEO.TM_ATRB_FLD_ID

AND DetInsTimeAttributeFieldPEO.GLOBAL_TM_ATRB_FLD_ID = GlobalDetTimeAttributeFieldPEO.TM_ATRB_FLD_ID(+)

AND EXISTS

(SELECT 1

FROM HWM_TM_REP_S_SEC_ATRBS_V

WHERE SECURITY_LEG_DATA_GROUP = TimeAttributeFieldMasterRefe1.LEGISLATIVE_DATA_GROUP_ID

)

) DETINS

ON DETINS.MASTER_INST_ID = ATT.ATTRIBUTE_CATEGORY

AND DETINS.TM_ATRB_FLD_ID3 = ATT.MASTER_ATTRIBUTE_ID

AND DETINS.VALUE_LOC2 = ATT.VALUE_LOC

)

WHERE TM_ATRB_FLD_ID IS NOT NULL