CMF_INTENDED_USE

Details

Schema: FUSION
Object owner: CMF
Object type: VIEW

Columns

Name
STRUCTURE_INSTANCE_NUMBER CATEGORY_ID CLASSIFICATION_NAME CLASSIFICATION_CODE COUNTRY_CODE EFFECTIVE_FROM EFFECTIVE_TO CLASSIFICATION_ID CATEGORY_NAME

Name

STRUCTURE_INSTANCE_NUMBER

CATEGORY_ID

CLASSIFICATION_NAME

CLASSIFICATION_CODE

COUNTRY_CODE

EFFECTIVE_FROM

EFFECTIVE_TO

CLASSIFICATION_ID

CATEGORY_NAME

Query

SQL_Statement
SELECT MC.STRUCTURE_INSTANCE_NUMBER, MC.CATEGORY_ID, MCT.DESCRIPTION CLASSIFICATION_NAME, MC.CATEGORY_CODE CLASSIFICATION_CODE, NULL COUNTRY_CODE, ZFT.EFFECTIVE_FROM EFFECTIVE_FROM, MC.END_DATE_ACTIVE EFFECTIVE_TO, MC.CATEGORY_ID CLASSIFICATION_ID, MCT.CATEGORY_NAME FROM EGP_CATEGORIES_TL MCT, EGP_CATEGORIES_B MC, ZX_FC_TYPES_B ZFT, EGP_CATEGORY_SETS_B MCS, EGP_CATEGORY_SET_VALID_CATS MCSV WHERE ZFT.CLASSIFICATION_TYPE_CODE = 'INTENDED_USE' AND MCS.CATEGORY_SET_ID = ZFT.OWNER_ID_NUM AND MC.CATEGORY_ID = MCT.CATEGORY_ID AND MCT.LANGUAGE = USERENV ( 'LANG') AND MC.ENABLED_FLAG = 'Y' AND ZFT.OWNER_TABLE_CODE ='EGP_CATEGORY_SETS_B' AND MCSV.CATEGORY_ID = MC.CATEGORY_ID AND MCSV.CATEGORY_SET_ID = MCS.CATEGORY_SET_ID UNION SELECT -1 STRUCTURE_INSTANCE_NUMBER, null CATEGORY_ID, FC.CLASSIFICATION_NAME CLASSIFICATION_NAME, FC.CLASSIFICATION_CODE CLASSIFICATION_CODE, FC.COUNTRY_CODE COUNTRY_CODE, FC.EFFECTIVE_FROM EFFECTIVE_FROM, FC.EFFECTIVE_TO EFFECTIVE_TO, FC.CLASSIFICATION_ID CLASSIFICATION_ID, ' ' CATEGORY_NAME FROM ZX_FC_CODES_VL FC, ZX_FC_TYPES_B ZFT WHERE FC.CLASSIFICATION_TYPE_CODE = 'INTENDED_USE' AND FC.CLASSIFICATION_TYPE_CODE = ZFT.CLASSIFICATION_TYPE_CODE AND (ZFT.OWNER_TABLE_CODE IS NULL OR ZFT.OWNER_TABLE_CODE = 'ZX_FC_TYPES_B')

SQL_Statement

SELECT MC.STRUCTURE_INSTANCE_NUMBER,

MC.CATEGORY_ID,

MCT.DESCRIPTION CLASSIFICATION_NAME,

MC.CATEGORY_CODE CLASSIFICATION_CODE,

NULL COUNTRY_CODE,

ZFT.EFFECTIVE_FROM EFFECTIVE_FROM,

MC.END_DATE_ACTIVE EFFECTIVE_TO,

MC.CATEGORY_ID CLASSIFICATION_ID,

MCT.CATEGORY_NAME

FROM EGP_CATEGORIES_TL MCT,

EGP_CATEGORIES_B MC,

ZX_FC_TYPES_B ZFT,

EGP_CATEGORY_SETS_B MCS,

EGP_CATEGORY_SET_VALID_CATS MCSV

WHERE ZFT.CLASSIFICATION_TYPE_CODE = 'INTENDED_USE'

AND MCS.CATEGORY_SET_ID = ZFT.OWNER_ID_NUM

AND MC.CATEGORY_ID = MCT.CATEGORY_ID

AND MCT.LANGUAGE = USERENV ( 'LANG')

AND MC.ENABLED_FLAG = 'Y'

AND ZFT.OWNER_TABLE_CODE ='EGP_CATEGORY_SETS_B'

AND MCSV.CATEGORY_ID = MC.CATEGORY_ID

AND MCSV.CATEGORY_SET_ID = MCS.CATEGORY_SET_ID

UNION

SELECT -1 STRUCTURE_INSTANCE_NUMBER,

null CATEGORY_ID,

FC.CLASSIFICATION_NAME CLASSIFICATION_NAME,

FC.CLASSIFICATION_CODE CLASSIFICATION_CODE,

FC.COUNTRY_CODE COUNTRY_CODE,

FC.EFFECTIVE_FROM EFFECTIVE_FROM,

FC.EFFECTIVE_TO EFFECTIVE_TO,

FC.CLASSIFICATION_ID CLASSIFICATION_ID,

' ' CATEGORY_NAME

FROM ZX_FC_CODES_VL FC, ZX_FC_TYPES_B ZFT

WHERE FC.CLASSIFICATION_TYPE_CODE = 'INTENDED_USE'

AND FC.CLASSIFICATION_TYPE_CODE = ZFT.CLASSIFICATION_TYPE_CODE

AND (ZFT.OWNER_TABLE_CODE IS NULL OR ZFT.OWNER_TABLE_CODE =

'ZX_FC_TYPES_B')