VIEW: APPS.CZ_MODEL_BOMREF_COUNTS_V

Source

View Text - Preformatted

SELECT get_bom_refs.model_id , get_bom_refs.model_type , get_bom_refs.name , get_bom_refs.bomrefs , sign (get_bom_refs.bomrefs) as has_bom_refs from ( select prj.devl_project_id as model_id , prj.model_type , prj.name , ( select count (*) from cz_model_ref_expls climbexpls where climbexpls.deleted_flag = '0' and climbexpls.parent_expl_node_id is null start with climbexpls.deleted_flag = '0' and climbexpls.ps_node_type != 264 and climbexpls.model_id = prj.devl_project_id and exists ( select 1 from cz_devl_projects bommodels where climbexpls.component_id = bommodels.devl_project_id and bommodels.model_type in ('A', 'P') and bommodels.deleted_flag = '0' ) connect by climbexpls.model_ref_expl_id = prior climbexpls.parent_expl_node_id and climbexpls.model_id = prior climbexpls.model_id and climbexpls.ps_node_type != 264 and climbexpls.deleted_flag = '0' ) bomrefs from cz_devl_projects prj where prj.deleted_flag = '0' ) get_bom_refs

View Text - HTML Formatted

SELECT GET_BOM_REFS.MODEL_ID
, GET_BOM_REFS.MODEL_TYPE
, GET_BOM_REFS.NAME
, GET_BOM_REFS.BOMREFS
, SIGN (GET_BOM_REFS.BOMREFS) AS HAS_BOM_REFS
FROM ( SELECT PRJ.DEVL_PROJECT_ID AS MODEL_ID
, PRJ.MODEL_TYPE
, PRJ.NAME
, ( SELECT COUNT (*)
FROM CZ_MODEL_REF_EXPLS CLIMBEXPLS
WHERE CLIMBEXPLS.DELETED_FLAG = '0'
AND CLIMBEXPLS.PARENT_EXPL_NODE_ID IS NULL START WITH CLIMBEXPLS.DELETED_FLAG = '0'
AND CLIMBEXPLS.PS_NODE_TYPE != 264
AND CLIMBEXPLS.MODEL_ID = PRJ.DEVL_PROJECT_ID
AND EXISTS ( SELECT 1
FROM CZ_DEVL_PROJECTS BOMMODELS
WHERE CLIMBEXPLS.COMPONENT_ID = BOMMODELS.DEVL_PROJECT_ID
AND BOMMODELS.MODEL_TYPE IN ('A'
, 'P')
AND BOMMODELS.DELETED_FLAG = '0' ) CONNECT BY CLIMBEXPLS.MODEL_REF_EXPL_ID = PRIOR CLIMBEXPLS.PARENT_EXPL_NODE_ID
AND CLIMBEXPLS.MODEL_ID = PRIOR CLIMBEXPLS.MODEL_ID
AND CLIMBEXPLS.PS_NODE_TYPE != 264
AND CLIMBEXPLS.DELETED_FLAG = '0' ) BOMREFS
FROM CZ_DEVL_PROJECTS PRJ
WHERE PRJ.DELETED_FLAG = '0' ) GET_BOM_REFS