VIEW: APPS.CZ_PSNODE_RULE_REFS_V

Source

View Text - Preformatted

SELECT exnd.model_id, exnd.model_ref_expl_id, exnd.ps_node_id, exnd.devl_project_id as psn_model_id, exnd.psn_ps_node_type, exnd.PARENT_ID, exnd.name as ps_node_name, rul.devl_project_id as rule_devl_project_id, decode (rul.devl_project_id, exnd.model_id, '0', '1') as referenced_rule_flag, decode (rul.devl_project_id, exnd.devl_project_id, '1', '0') as rule_in_enclosing_model_flag, rul.rule_id, rul.rule_folder_id, rul.RULE_FOLDER_TYPE, rul.name as rule_name, rul.rule_type, ( select rdtlv.value_label from cz_detailedruletypes_lkv rdtlv where rdtlv.numeric_id_value = rpart.detailed_rule_type ) as rule_type_string, rul.desc_text, rul.disabled_flag, exnd.parent_psnode_expl_id, exnd.effective_parent_id, chxpl.MODEL_REF_EXPL_ID as descendant_expl_node_id, rpart.detailed_rule_type, rpart.participant_role_list, rul.UNSATISFIED_MSG_SOURCE, rul.REASON_ID, rul.INVALID_FLAG, rul.COMPONENT_ID, rpart.MODEL_REF_EXPL_ID as rule_expl_node_id, rul.REASON_TYPE, rul.ORIG_SYS_REF, rul.CHECKOUT_USER, ( SELECT RRFL.TREE_SEQ FROM CZ_RULE_FOLDERS RRFL WHERE RRFL.OBJECT_TYPE NOT IN ('RSQ', 'RFL', 'FNC') AND RRFL.RULE_FOLDER_ID = RUL.RULE_ID ) AS SEQ_NBR, rul.EFFECTIVITY_SET_ID, rul.EFFECTIVE_FROM, rul.EFFECTIVE_UNTIL, rul.EFFECTIVE_USAGE_MASK, rul.PERSISTENT_RULE_ID, rul.UNSATISFIED_MSG_ID, rul.TEMPLATE_PRIMITIVE_FLAG, rul.PRESENTATION_FLAG, rul.signature_id, rul.mutable_flag, rul.template_token, rul.RULE_TEXT, rul.NOTES, rul.CLASS_NAME, rul.INSTANTIATION_SCOPE, rul.SEEDED_FLAG from cz_rules rul, cz_rule_participants_v rpart, cz_explmodel_nodes_v exnd, cz_model_ref_expls chxpl where rul.rule_id = rpart.rule_id and rul.deleted_flag = '0' and rpart.PARTICIPANT_EXPL_ID = chxpl.model_ref_expl_id and rpart.PARTICIPANT_NODE_ID = exnd.ps_node_id and chxpl.deleted_flag = '0' and exists ( select 1 from cz_model_ref_expls findxpl where findxpl.model_ref_expl_id = chxpl.model_ref_expl_id start with findxpl.model_ref_expl_id = exnd.model_ref_expl_id connect by findxpl.model_ref_expl_id = prior findxpl.child_model_expl_id and findxpl.deleted_flag = '0' ) UNION ALL SELECT exndf.model_id, exndf.model_ref_expl_id, exndf.ps_node_id, exndf.devl_project_id as psn_model_id, exndf.psn_ps_node_type, exndf.PARENT_ID, exndf.name as ps_node_name, fnc.devl_project_id as rule_devl_project_id, decode (fnc.devl_project_id, exndf.model_id, '0', '1') as referenced_rule_flag, decode (fnc.devl_project_id, exndf.devl_project_id, '1', '0') as rule_in_enclosing_model_flag, fnc.func_comp_id as rule_id, fnc.rule_folder_id, fnc.RULE_FOLDER_TYPE, fnc.name as rule_name, 29 as rule_type, ( select rdtlv.value_label from cz_detailedruletypes_lkv rdtlv where rdtlv.numeric_id_value = 29 ) as rule_type_string, fnc.desc_text, '0' as disabled_flag, exndf.parent_psnode_expl_id, exndf.effective_parent_id, chxplf.MODEL_REF_EXPL_ID as descendant_expl_node_id, fpart.detailed_rule_type, fpart.participant_role_list, '1' as UNSATISFIED_MSG_SOURCE, to_number (null) as REASON_ID, '0' as INVALID_FLAG, fnc.COMPONENT_ID, fnc.MODEL_REF_EXPL_ID as rule_expl_node_id, 0 as REASON_TYPE, '' as ORIG_SYS_REF, fnc.CHECKOUT_USER, ( SELECT RRFL.TREE_SEQ FROM CZ_RULE_FOLDERS RRFL WHERE RRFL.OBJECT_TYPE = 'FNC' AND RRFL.RULE_FOLDER_ID = fnc.func_comp_id ) AS SEQ_NBR, to_number (null) as EFFECTIVITY_SET_ID, cz_utils.EPOCH_BEGIN as EFFECTIVE_FROM, cz_utils.epoch_end as EFFECTIVE_UNTIL, '0000000000000000' as EFFECTIVE_USAGE_MASK, fnc.func_comp_id as PERSISTENT_RULE_ID, to_number (null) as UNSATISFIED_MSG_ID, '0' as TEMPLATE_PRIMITIVE_FLAG, '' as PRESENTATION_FLAG, to_number (null) as signature_id, '0' as mutable_flag, '' as template_token, empty_clob () as RULE_TEXT, empty_clob () as NOTES, fnc.program_string as CLASS_NAME, to_number (null) as INSTANTIATION_SCOPE, '0' as SEEDED_FLAG from cz_func_comp_specs fnc, cz_rule_participants_v fpart, cz_explmodel_nodes_v exndf, cz_model_ref_expls chxplf WHERE fnc.func_comp_id = fpart.rule_id and fnc.deleted_flag = '0' and fpart.PARTICIPANT_EXPL_ID = chxplf.model_ref_expl_id and fpart.PARTICIPANT_NODE_ID = exndf.ps_node_id and chxplf.deleted_flag = '0' and exists ( select 1 from cz_model_ref_expls findxplf where findxplf.model_ref_expl_id = chxplf.model_ref_expl_id start with findxplf.model_ref_expl_id = exndf.model_ref_expl_id connect by findxplf.model_ref_expl_id = prior findxplf.child_model_expl_id and findxplf.deleted_flag = '0' )

View Text - HTML Formatted

SELECT EXND.MODEL_ID
, EXND.MODEL_REF_EXPL_ID
, EXND.PS_NODE_ID
, EXND.DEVL_PROJECT_ID AS PSN_MODEL_ID
, EXND.PSN_PS_NODE_TYPE
, EXND.PARENT_ID
, EXND.NAME AS PS_NODE_NAME
, RUL.DEVL_PROJECT_ID AS RULE_DEVL_PROJECT_ID
, DECODE (RUL.DEVL_PROJECT_ID
, EXND.MODEL_ID
, '0'
, '1') AS REFERENCED_RULE_FLAG
, DECODE (RUL.DEVL_PROJECT_ID
, EXND.DEVL_PROJECT_ID
, '1'
, '0') AS RULE_IN_ENCLOSING_MODEL_FLAG
, RUL.RULE_ID
, RUL.RULE_FOLDER_ID
, RUL.RULE_FOLDER_TYPE
, RUL.NAME AS RULE_NAME
, RUL.RULE_TYPE
, ( SELECT RDTLV.VALUE_LABEL
FROM CZ_DETAILEDRULETYPES_LKV RDTLV
WHERE RDTLV.NUMERIC_ID_VALUE = RPART.DETAILED_RULE_TYPE ) AS RULE_TYPE_STRING
, RUL.DESC_TEXT
, RUL.DISABLED_FLAG
, EXND.PARENT_PSNODE_EXPL_ID
, EXND.EFFECTIVE_PARENT_ID
, CHXPL.MODEL_REF_EXPL_ID AS DESCENDANT_EXPL_NODE_ID
, RPART.DETAILED_RULE_TYPE
, RPART.PARTICIPANT_ROLE_LIST
, RUL.UNSATISFIED_MSG_SOURCE
, RUL.REASON_ID
, RUL.INVALID_FLAG
, RUL.COMPONENT_ID
, RPART.MODEL_REF_EXPL_ID AS RULE_EXPL_NODE_ID
, RUL.REASON_TYPE
, RUL.ORIG_SYS_REF
, RUL.CHECKOUT_USER
, ( SELECT RRFL.TREE_SEQ
FROM CZ_RULE_FOLDERS RRFL
WHERE RRFL.OBJECT_TYPE NOT IN ('RSQ'
, 'RFL'
, 'FNC')
AND RRFL.RULE_FOLDER_ID = RUL.RULE_ID ) AS SEQ_NBR
, RUL.EFFECTIVITY_SET_ID
, RUL.EFFECTIVE_FROM
, RUL.EFFECTIVE_UNTIL
, RUL.EFFECTIVE_USAGE_MASK
, RUL.PERSISTENT_RULE_ID
, RUL.UNSATISFIED_MSG_ID
, RUL.TEMPLATE_PRIMITIVE_FLAG
, RUL.PRESENTATION_FLAG
, RUL.SIGNATURE_ID
, RUL.MUTABLE_FLAG
, RUL.TEMPLATE_TOKEN
, RUL.RULE_TEXT
, RUL.NOTES
, RUL.CLASS_NAME
, RUL.INSTANTIATION_SCOPE
, RUL.SEEDED_FLAG
FROM CZ_RULES RUL
, CZ_RULE_PARTICIPANTS_V RPART
, CZ_EXPLMODEL_NODES_V EXND
, CZ_MODEL_REF_EXPLS CHXPL
WHERE RUL.RULE_ID = RPART.RULE_ID
AND RUL.DELETED_FLAG = '0'
AND RPART.PARTICIPANT_EXPL_ID = CHXPL.MODEL_REF_EXPL_ID
AND RPART.PARTICIPANT_NODE_ID = EXND.PS_NODE_ID
AND CHXPL.DELETED_FLAG = '0'
AND EXISTS ( SELECT 1
FROM CZ_MODEL_REF_EXPLS FINDXPL
WHERE FINDXPL.MODEL_REF_EXPL_ID = CHXPL.MODEL_REF_EXPL_ID START WITH FINDXPL.MODEL_REF_EXPL_ID = EXND.MODEL_REF_EXPL_ID CONNECT BY FINDXPL.MODEL_REF_EXPL_ID = PRIOR FINDXPL.CHILD_MODEL_EXPL_ID
AND FINDXPL.DELETED_FLAG = '0' ) UNION ALL SELECT EXNDF.MODEL_ID
, EXNDF.MODEL_REF_EXPL_ID
, EXNDF.PS_NODE_ID
, EXNDF.DEVL_PROJECT_ID AS PSN_MODEL_ID
, EXNDF.PSN_PS_NODE_TYPE
, EXNDF.PARENT_ID
, EXNDF.NAME AS PS_NODE_NAME
, FNC.DEVL_PROJECT_ID AS RULE_DEVL_PROJECT_ID
, DECODE (FNC.DEVL_PROJECT_ID
, EXNDF.MODEL_ID
, '0'
, '1') AS REFERENCED_RULE_FLAG
, DECODE (FNC.DEVL_PROJECT_ID
, EXNDF.DEVL_PROJECT_ID
, '1'
, '0') AS RULE_IN_ENCLOSING_MODEL_FLAG
, FNC.FUNC_COMP_ID AS RULE_ID
, FNC.RULE_FOLDER_ID
, FNC.RULE_FOLDER_TYPE
, FNC.NAME AS RULE_NAME
, 29 AS RULE_TYPE
, ( SELECT RDTLV.VALUE_LABEL
FROM CZ_DETAILEDRULETYPES_LKV RDTLV
WHERE RDTLV.NUMERIC_ID_VALUE = 29 ) AS RULE_TYPE_STRING
, FNC.DESC_TEXT
, '0' AS DISABLED_FLAG
, EXNDF.PARENT_PSNODE_EXPL_ID
, EXNDF.EFFECTIVE_PARENT_ID
, CHXPLF.MODEL_REF_EXPL_ID AS DESCENDANT_EXPL_NODE_ID
, FPART.DETAILED_RULE_TYPE
, FPART.PARTICIPANT_ROLE_LIST
, '1' AS UNSATISFIED_MSG_SOURCE
, TO_NUMBER (NULL) AS REASON_ID
, '0' AS INVALID_FLAG
, FNC.COMPONENT_ID
, FNC.MODEL_REF_EXPL_ID AS RULE_EXPL_NODE_ID
, 0 AS REASON_TYPE
, '' AS ORIG_SYS_REF
, FNC.CHECKOUT_USER
, ( SELECT RRFL.TREE_SEQ
FROM CZ_RULE_FOLDERS RRFL
WHERE RRFL.OBJECT_TYPE = 'FNC'
AND RRFL.RULE_FOLDER_ID = FNC.FUNC_COMP_ID ) AS SEQ_NBR
, TO_NUMBER (NULL) AS EFFECTIVITY_SET_ID
, CZ_UTILS.EPOCH_BEGIN AS EFFECTIVE_FROM
, CZ_UTILS.EPOCH_END AS EFFECTIVE_UNTIL
, '0000000000000000' AS EFFECTIVE_USAGE_MASK
, FNC.FUNC_COMP_ID AS PERSISTENT_RULE_ID
, TO_NUMBER (NULL) AS UNSATISFIED_MSG_ID
, '0' AS TEMPLATE_PRIMITIVE_FLAG
, '' AS PRESENTATION_FLAG
, TO_NUMBER (NULL) AS SIGNATURE_ID
, '0' AS MUTABLE_FLAG
, '' AS TEMPLATE_TOKEN
, EMPTY_CLOB () AS RULE_TEXT
, EMPTY_CLOB () AS NOTES
, FNC.PROGRAM_STRING AS CLASS_NAME
, TO_NUMBER (NULL) AS INSTANTIATION_SCOPE
, '0' AS SEEDED_FLAG
FROM CZ_FUNC_COMP_SPECS FNC
, CZ_RULE_PARTICIPANTS_V FPART
, CZ_EXPLMODEL_NODES_V EXNDF
, CZ_MODEL_REF_EXPLS CHXPLF
WHERE FNC.FUNC_COMP_ID = FPART.RULE_ID
AND FNC.DELETED_FLAG = '0'
AND FPART.PARTICIPANT_EXPL_ID = CHXPLF.MODEL_REF_EXPL_ID
AND FPART.PARTICIPANT_NODE_ID = EXNDF.PS_NODE_ID
AND CHXPLF.DELETED_FLAG = '0'
AND EXISTS ( SELECT 1
FROM CZ_MODEL_REF_EXPLS FINDXPLF
WHERE FINDXPLF.MODEL_REF_EXPL_ID = CHXPLF.MODEL_REF_EXPL_ID START WITH FINDXPLF.MODEL_REF_EXPL_ID = EXNDF.MODEL_REF_EXPL_ID CONNECT BY FINDXPLF.MODEL_REF_EXPL_ID = PRIOR FINDXPLF.CHILD_MODEL_EXPL_ID
AND FINDXPLF.DELETED_FLAG = '0' )