[Home] [Help]
SELECT XPLPRJ.MODEL_ID, XPLPRJ.NODE_DEPTH, TRFL.RULE_FOLDER_ID, TRFL.NAME, TRFL.DESC_TEXT, TRFL.PARENT_RULE_FOLDER_ID, TRFL.DEVL_PROJECT_ID, DECODE (TRFL.DEVL_PROJECT_ID, XPLPRJ.MODEL_ID, '0', '1') AS REFERENCED_RULE_FOLDER_FLAG, TRFL.TREE_SEQ, TRFL.DELETED_FLAG, TRFL.CREATION_DATE, TRFL.CREATED_BY, TRFL.LAST_UPDATE_DATE, TRFL.LAST_UPDATED_BY, TRFL.LAST_UPDATE_LOGIN, TRFL.FOLDER_TYPE, TRFL.EFFECTIVE_USAGE_MASK, TRFL.EFFECTIVE_FROM, TRFL.EFFECTIVE_UNTIL, TRFL.PERSISTENT_RULE_FOLDER_ID, TRFL.EFFECTIVITY_SET_ID, TRFL.OBJECT_TYPE, TRFL.DISABLED_FLAG, TRFL.ORIG_SYS_REF, TRFL.DETAILED_RULE_TYPE, TRFL.RULE_TYPE_LABEL, TRFL.RULE_TYPE_DESCRIPTION, trfl.invalid_flag, substr (trfl.presentation_flag, 1, 1) as presentation_flag, TRFL.RULE_TYPE, trfl.seq_nbr, TRFL.RULE_CLASS, TRFL.CLASS_SEQ FROM ( SELECT RFL.RULE_FOLDER_ID, RFL.NAME, RFL.DESC_TEXT, RFL.PARENT_RULE_FOLDER_ID, RFL.DEVL_PROJECT_ID, RFL.TREE_SEQ, RFL.DELETED_FLAG, RFL.CREATION_DATE, RFL.CREATED_BY, RFL.LAST_UPDATE_DATE, RFL.LAST_UPDATED_BY, RFL.LAST_UPDATE_LOGIN, RFL.FOLDER_TYPE, RFL.EFFECTIVE_USAGE_MASK, RFL.EFFECTIVE_FROM, RFL.EFFECTIVE_UNTIL, RFL.PERSISTENT_RULE_FOLDER_ID, RFL.EFFECTIVITY_SET_ID, RFL.OBJECT_TYPE, RFL.DISABLED_FLAG, RFL.ORIG_SYS_REF, RTLV.NUMERIC_ID_VALUE AS DETAILED_RULE_TYPE, RTLV.VALUE_LABEL AS RULE_TYPE_LABEL, RTLV.VALUE_DESCRIPTION AS RULE_TYPE_DESCRIPTION, '0' as invalid_flag, to_char (null) as presentation_flag, RTLV.NUMERIC_ID_VALUE AS RULE_TYPE, rfl.tree_seq as seq_nbr, NULL AS RULE_CLASS, NULL AS CLASS_SEQ FROM CZ_RULE_FOLDERS RFL, CZ_RULETYPECODES_LKV RTLV WHERE RFL.OBJECT_TYPE IN ('RFL', 'RSQ', 'FNC') AND RFL.DELETED_FLAG = '0' AND RFL.OBJECT_TYPE = RTLV.DATA_VALUE UNION ALL SELECT TRUL.RULE_ID AS RULE_FOLDER_ID, TRUL.RULE_NAME AS NAME, TRUL.RULE_DESCRIPTION AS DESC_TEXT, TRUL.RULE_FOLDER_ID AS PARENT_RULE_FOLDER_ID, TRUL.DEVL_PROJECT_ID, ( select min (subrf.tree_seq) from cz_rule_folders subrf where subrf.rule_folder_id = TRUL.RULE_ID and subrf.OBJECT_TYPE not in ('RFL', 'RSQ', 'FNC') ) AS TREE_SEQ, '0' AS DELETED_FLAG, TRUL.CREATION_DATE, TRUL.CREATED_BY, TRUL.LAST_UPDATE_DATE, TRUL.LAST_UPDATED_BY, TRUL.LAST_UPDATE_LOGIN, TRUL.RULE_FOLDER_TYPE, TRUL.EFFECTIVE_USAGE_MASK, TRUL.EFFECTIVE_FROM, TRUL.EFFECTIVE_UNTIL, TRUL.PERSISTENT_RULE_ID, TRUL.EFFECTIVITY_SET_ID, decode (trul.rule_type, 200, 'RUL', TRUL.RULE_TYPE_CODE) AS OBJECT_TYPE, TRUL.DISABLED_FLAG, TRUL.ORIG_SYS_REF, TRUL.DETAILED_RULE_TYPE, TRUL.RULE_TYPE_LABEL, TRUL.RULE_TYPE_DESCRIPTION, trul.invalid_flag, trul.presentation_flag, TRUL.RULE_TYPE, trul.seq_nbr, TRUL.RULE_CLASS, TRUL.CLASS_SEQ FROM CZ_TYPED_RULES_V TRUL ) TRFL, ( SELECT MODEL_ID, COMPONENT_ID, MIN (NODE_DEPTH) AS NODE_DEPTH FROM CZ_MODEL_REF_EXPLS WHERE DELETED_FLAG = '0' GROUP BY MODEL_ID, COMPONENT_ID ) XPLPRJ WHERE TRFL.DELETED_FLAG = '0' AND TRFL.DEVL_PROJECT_ID = XPLPRJ.COMPONENT_ID
SELECT XPLPRJ.MODEL_ID
, XPLPRJ.NODE_DEPTH
, TRFL.RULE_FOLDER_ID
, TRFL.NAME
, TRFL.DESC_TEXT
, TRFL.PARENT_RULE_FOLDER_ID
, TRFL.DEVL_PROJECT_ID
, DECODE (TRFL.DEVL_PROJECT_ID
, XPLPRJ.MODEL_ID
, '0'
, '1') AS REFERENCED_RULE_FOLDER_FLAG
, TRFL.TREE_SEQ
, TRFL.DELETED_FLAG
, TRFL.CREATION_DATE
, TRFL.CREATED_BY
, TRFL.LAST_UPDATE_DATE
, TRFL.LAST_UPDATED_BY
, TRFL.LAST_UPDATE_LOGIN
, TRFL.FOLDER_TYPE
, TRFL.EFFECTIVE_USAGE_MASK
, TRFL.EFFECTIVE_FROM
, TRFL.EFFECTIVE_UNTIL
, TRFL.PERSISTENT_RULE_FOLDER_ID
, TRFL.EFFECTIVITY_SET_ID
, TRFL.OBJECT_TYPE
, TRFL.DISABLED_FLAG
, TRFL.ORIG_SYS_REF
, TRFL.DETAILED_RULE_TYPE
, TRFL.RULE_TYPE_LABEL
, TRFL.RULE_TYPE_DESCRIPTION
, TRFL.INVALID_FLAG
, SUBSTR (TRFL.PRESENTATION_FLAG
, 1
, 1) AS PRESENTATION_FLAG
, TRFL.RULE_TYPE
, TRFL.SEQ_NBR
, TRFL.RULE_CLASS
, TRFL.CLASS_SEQ
FROM ( SELECT RFL.RULE_FOLDER_ID
, RFL.NAME
, RFL.DESC_TEXT
, RFL.PARENT_RULE_FOLDER_ID
, RFL.DEVL_PROJECT_ID
, RFL.TREE_SEQ
, RFL.DELETED_FLAG
, RFL.CREATION_DATE
, RFL.CREATED_BY
, RFL.LAST_UPDATE_DATE
, RFL.LAST_UPDATED_BY
, RFL.LAST_UPDATE_LOGIN
, RFL.FOLDER_TYPE
, RFL.EFFECTIVE_USAGE_MASK
, RFL.EFFECTIVE_FROM
, RFL.EFFECTIVE_UNTIL
, RFL.PERSISTENT_RULE_FOLDER_ID
, RFL.EFFECTIVITY_SET_ID
, RFL.OBJECT_TYPE
, RFL.DISABLED_FLAG
, RFL.ORIG_SYS_REF
, RTLV.NUMERIC_ID_VALUE AS DETAILED_RULE_TYPE
, RTLV.VALUE_LABEL AS RULE_TYPE_LABEL
, RTLV.VALUE_DESCRIPTION AS RULE_TYPE_DESCRIPTION
, '0' AS INVALID_FLAG
, TO_CHAR (NULL) AS PRESENTATION_FLAG
, RTLV.NUMERIC_ID_VALUE AS RULE_TYPE
, RFL.TREE_SEQ AS SEQ_NBR
, NULL AS RULE_CLASS
, NULL AS CLASS_SEQ
FROM CZ_RULE_FOLDERS RFL
, CZ_RULETYPECODES_LKV RTLV
WHERE RFL.OBJECT_TYPE IN ('RFL'
, 'RSQ'
, 'FNC')
AND RFL.DELETED_FLAG = '0'
AND RFL.OBJECT_TYPE = RTLV.DATA_VALUE UNION ALL SELECT TRUL.RULE_ID AS RULE_FOLDER_ID
, TRUL.RULE_NAME AS NAME
, TRUL.RULE_DESCRIPTION AS DESC_TEXT
, TRUL.RULE_FOLDER_ID AS PARENT_RULE_FOLDER_ID
, TRUL.DEVL_PROJECT_ID
, ( SELECT MIN (SUBRF.TREE_SEQ)
FROM CZ_RULE_FOLDERS SUBRF
WHERE SUBRF.RULE_FOLDER_ID = TRUL.RULE_ID
AND SUBRF.OBJECT_TYPE NOT IN ('RFL'
, 'RSQ'
, 'FNC') ) AS TREE_SEQ
, '0' AS DELETED_FLAG
, TRUL.CREATION_DATE
, TRUL.CREATED_BY
, TRUL.LAST_UPDATE_DATE
, TRUL.LAST_UPDATED_BY
, TRUL.LAST_UPDATE_LOGIN
, TRUL.RULE_FOLDER_TYPE
, TRUL.EFFECTIVE_USAGE_MASK
, TRUL.EFFECTIVE_FROM
, TRUL.EFFECTIVE_UNTIL
, TRUL.PERSISTENT_RULE_ID
, TRUL.EFFECTIVITY_SET_ID
, DECODE (TRUL.RULE_TYPE
, 200
, 'RUL'
, TRUL.RULE_TYPE_CODE) AS OBJECT_TYPE
, TRUL.DISABLED_FLAG
, TRUL.ORIG_SYS_REF
, TRUL.DETAILED_RULE_TYPE
, TRUL.RULE_TYPE_LABEL
, TRUL.RULE_TYPE_DESCRIPTION
, TRUL.INVALID_FLAG
, TRUL.PRESENTATION_FLAG
, TRUL.RULE_TYPE
, TRUL.SEQ_NBR
, TRUL.RULE_CLASS
, TRUL.CLASS_SEQ
FROM CZ_TYPED_RULES_V TRUL ) TRFL
, ( SELECT MODEL_ID
, COMPONENT_ID
, MIN (NODE_DEPTH) AS NODE_DEPTH
FROM CZ_MODEL_REF_EXPLS
WHERE DELETED_FLAG = '0' GROUP BY MODEL_ID
, COMPONENT_ID ) XPLPRJ
WHERE TRFL.DELETED_FLAG = '0'
AND TRFL.DEVL_PROJECT_ID = XPLPRJ.COMPONENT_ID
|
|
|
|