View: CZ_COMMON_CHILDND_SYSPROPS_V

Product:	CZ - Configurator
Description:
Implementation/DBA Data:	APPS.CZ_COMMON_CHILDND_SYSPROPS_V

View Text

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)

Columns