VIEW: APPS.CZ_COMMON_CHILDND_SYSPROPS_V

Source

View Text - Preformatted

SELECT parent.parent_node_id as parent_id, ntsys.sys_prop_RULE_ID as sysprop_rule_id, ntsys.SYS_PROP_NAME, ntsys.rule_type, ntsys.RULE_TYPE_CODE, ntsys.rule_type_label, ntsys.SYS_PROP_RETTYPE_ID as SYS_PROP_DATA_TYPE_ID, ntsys.SYS_PROP_SEQ_NBR, ntsys.instance_set_flag , ntsys.SYS_PROP_RETTYPE_NAME as SYS_PROP_DATA_TYPE_NAME, parent.parent_instantiable_flag FROM cz_rul_typedpsn_v psnrty1, cz_signature_sysprops_v ntsys, ( select ps_node_id as parent_node_id, instantiable_flag as parent_instantiable_flag, decode (ps_node_type, 263, reference_id, 264, reference_id, devl_project_id) as child_devl_project_id, decode (ps_node_type, 263, reference_id, 264, null, ps_node_id) as child_parent_node_id from cz_ps_nodes getpar where getpar.deleted_flag = '0' and (getpar.instantiable_flag is null or getpar.instantiable_flag in ('2', '3')) ) parent WHERE parent.child_devl_project_id = psnrty1.devl_project_id and 1 = decode (parent.child_parent_node_id, psnrty1.parent_id, 1, 0) and psnrty1.detailed_type_id = ntsys.DETAILED_signature_ID and psnrty1.devl_project_id = ntsys.p_model_id and ntsys.instance_set_flag IN (2,3) group by parent.parent_node_id , parent.child_parent_node_id , ntsys.sys_prop_rule_id , ntsys.SYS_PROP_SEQ_NBR , ntsys.sys_prop_name , ntsys.rule_type , ntsys.RULE_TYPE_CODE , ntsys.rule_type_label , ntsys.SYS_PROP_RETTYPE_ID , ntsys.SYS_PROP_RETTYPE_NAME , ntsys.SYS_PROP_SEQ_NBR , parent.parent_instantiable_flag , ntsys.instance_set_flag having count (*) = ( select count (*) from cz_ps_nodes psn1 where psn1.deleted_flag = '0' and psn1.parent_id = parent.child_parent_node_id ) union all SELECT psnrty2.ps_node_id as parent_id , intsys.sys_prop_RULE_ID as sysprop_rule_id , intsys.SYS_PROP_NAME, intsys.rule_type , intsys.RULE_TYPE_CODE , intsys.rule_type_label , intsys.SYS_PROP_RETTYPE_ID as SYS_PROP_DATA_TYPE_ID , intsys.SYS_PROP_SEQ_NBR , intsys.instance_set_flag , intsys.SYS_PROP_RETTYPE_NAME as SYS_PROP_DATA_TYPE_NAME , psnrty2.instantiable_flag as parent_instantiable_flag FROM cz_rul_typedpsn_v psnrty2, cz_signature_sysprops_v intsys where psnrty2.instantiable_flag in ('1', '4') and psnrty2.detailed_type_id = intsys.detailed_signature_id and psnrty2.devl_project_id = intsys.p_model_id and intsys.instance_set_flag IN (1,2)

View Text - HTML Formatted

SELECT PARENT.PARENT_NODE_ID AS PARENT_ID
, NTSYS.SYS_PROP_RULE_ID AS SYSPROP_RULE_ID
, NTSYS.SYS_PROP_NAME
, NTSYS.RULE_TYPE
, NTSYS.RULE_TYPE_CODE
, NTSYS.RULE_TYPE_LABEL
, NTSYS.SYS_PROP_RETTYPE_ID AS SYS_PROP_DATA_TYPE_ID
, NTSYS.SYS_PROP_SEQ_NBR
, NTSYS.INSTANCE_SET_FLAG
, NTSYS.SYS_PROP_RETTYPE_NAME AS SYS_PROP_DATA_TYPE_NAME
, PARENT.PARENT_INSTANTIABLE_FLAG
FROM CZ_RUL_TYPEDPSN_V PSNRTY1
, CZ_SIGNATURE_SYSPROPS_V NTSYS
, ( SELECT PS_NODE_ID AS PARENT_NODE_ID
, INSTANTIABLE_FLAG AS PARENT_INSTANTIABLE_FLAG
, DECODE (PS_NODE_TYPE
, 263
, REFERENCE_ID
, 264
, REFERENCE_ID
, DEVL_PROJECT_ID) AS CHILD_DEVL_PROJECT_ID
, DECODE (PS_NODE_TYPE
, 263
, REFERENCE_ID
, 264
, NULL
, PS_NODE_ID) AS CHILD_PARENT_NODE_ID
FROM CZ_PS_NODES GETPAR
WHERE GETPAR.DELETED_FLAG = '0'
AND (GETPAR.INSTANTIABLE_FLAG IS NULL OR GETPAR.INSTANTIABLE_FLAG IN ('2'
, '3')) ) PARENT
WHERE PARENT.CHILD_DEVL_PROJECT_ID = PSNRTY1.DEVL_PROJECT_ID
AND 1 = DECODE (PARENT.CHILD_PARENT_NODE_ID
, PSNRTY1.PARENT_ID
, 1
, 0)
AND PSNRTY1.DETAILED_TYPE_ID = NTSYS.DETAILED_SIGNATURE_ID
AND PSNRTY1.DEVL_PROJECT_ID = NTSYS.P_MODEL_ID
AND NTSYS.INSTANCE_SET_FLAG IN (2
, 3) GROUP BY PARENT.PARENT_NODE_ID
, PARENT.CHILD_PARENT_NODE_ID
, NTSYS.SYS_PROP_RULE_ID
, NTSYS.SYS_PROP_SEQ_NBR
, NTSYS.SYS_PROP_NAME
, NTSYS.RULE_TYPE
, NTSYS.RULE_TYPE_CODE
, NTSYS.RULE_TYPE_LABEL
, NTSYS.SYS_PROP_RETTYPE_ID
, NTSYS.SYS_PROP_RETTYPE_NAME
, NTSYS.SYS_PROP_SEQ_NBR
, PARENT.PARENT_INSTANTIABLE_FLAG
, NTSYS.INSTANCE_SET_FLAG HAVING COUNT (*) = ( SELECT COUNT (*)
FROM CZ_PS_NODES PSN1
WHERE PSN1.DELETED_FLAG = '0'
AND PSN1.PARENT_ID = PARENT.CHILD_PARENT_NODE_ID ) UNION ALL SELECT PSNRTY2.PS_NODE_ID AS PARENT_ID
, INTSYS.SYS_PROP_RULE_ID AS SYSPROP_RULE_ID
, INTSYS.SYS_PROP_NAME
, INTSYS.RULE_TYPE
, INTSYS.RULE_TYPE_CODE
, INTSYS.RULE_TYPE_LABEL
, INTSYS.SYS_PROP_RETTYPE_ID AS SYS_PROP_DATA_TYPE_ID
, INTSYS.SYS_PROP_SEQ_NBR
, INTSYS.INSTANCE_SET_FLAG
, INTSYS.SYS_PROP_RETTYPE_NAME AS SYS_PROP_DATA_TYPE_NAME
, PSNRTY2.INSTANTIABLE_FLAG AS PARENT_INSTANTIABLE_FLAG
FROM CZ_RUL_TYPEDPSN_V PSNRTY2
, CZ_SIGNATURE_SYSPROPS_V INTSYS
WHERE PSNRTY2.INSTANTIABLE_FLAG IN ('1'
, '4')
AND PSNRTY2.DETAILED_TYPE_ID = INTSYS.DETAILED_SIGNATURE_ID
AND PSNRTY2.DEVL_PROJECT_ID = INTSYS.P_MODEL_ID
AND INTSYS.INSTANCE_SET_FLAG IN (1
, 2)