VIEW: APPS.FEM_BR_DIS_MBR_HIER_REP_V

Source

View Text - Preformatted

SELECT R.REQUEST_ID, R.REQUEST_DATE, R.REQUEST_NAME, R.OBJECT_TYPE_CODE, R.OBJECT_TYPE_NAME, R.LEDGER_ID, R.LEDGER_NAME, R.EFFECTIVE_DATE, R.OBJECT_ID, R.OBJECT_NAME, R.FOLDER_ID, R.FOLDER_NAME, R.DIMENSION_ID, R.DIMENSION_NAME, M.HIERARCHY_OBJECT_ID, M.HIERARCHY_OBJECT_NAME, M.HIERARCHY_OBJ_DEF_ID, M.HIERARCHY_OBJ_DEF_NAME, M.PARENT_MEMBER_ID, M.PARENT_DISPLAY_CODE, M.PARENT_NAME, M.PARENT_VALUE_SET_ID, M.PARENT_VALUE_SET_NAME, M.CHILD_MEMBER_ID, M.CHILD_DISPLAY_CODE, M.CHILD_NAME, M.CHILD_VALUE_SET_ID, M.CHILD_VALUE_SET_NAME, M.DISABLED_FLAG, LPAD(' ', 5*LEVEL-1)||M.child_name||decode(M.disabled_flag,'Y','***') HIERARCHICAL_COLUMN FROM FEM_BR_DIS_MBR_REQUESTS R, fem_br_dis_mbr_hier M, fem_hierarchies H, fem_hier_definitions D WHERE R.REQUEST_ID = M.REQUEST_ID AND M.hierarchy_object_id = H.hierarchy_obj_id AND M.hierarchy_obj_def_id = D.hierarchy_obj_def_id AND H.flattened_rows_flag = 'Y' AND D.flattened_rows_completion_code = 'COMPLETED' START WITH M.child_member_id = M.parent_member_id CONNECT BY PRIOR M.child_member_id = M.parent_member_id AND PRIOR M.hierarchy_obj_def_id = M.hierarchy_obj_def_id AND PRIOR M.request_id = M.request_id AND M.child_member_id <> M.parent_member_id

View Text - HTML Formatted

SELECT R.REQUEST_ID
, R.REQUEST_DATE
, R.REQUEST_NAME
, R.OBJECT_TYPE_CODE
, R.OBJECT_TYPE_NAME
, R.LEDGER_ID
, R.LEDGER_NAME
, R.EFFECTIVE_DATE
, R.OBJECT_ID
, R.OBJECT_NAME
, R.FOLDER_ID
, R.FOLDER_NAME
, R.DIMENSION_ID
, R.DIMENSION_NAME
, M.HIERARCHY_OBJECT_ID
, M.HIERARCHY_OBJECT_NAME
, M.HIERARCHY_OBJ_DEF_ID
, M.HIERARCHY_OBJ_DEF_NAME
, M.PARENT_MEMBER_ID
, M.PARENT_DISPLAY_CODE
, M.PARENT_NAME
, M.PARENT_VALUE_SET_ID
, M.PARENT_VALUE_SET_NAME
, M.CHILD_MEMBER_ID
, M.CHILD_DISPLAY_CODE
, M.CHILD_NAME
, M.CHILD_VALUE_SET_ID
, M.CHILD_VALUE_SET_NAME
, M.DISABLED_FLAG
, LPAD(' '
, 5*LEVEL-1)||M.CHILD_NAME||DECODE(M.DISABLED_FLAG
, 'Y'
, '***') HIERARCHICAL_COLUMN
FROM FEM_BR_DIS_MBR_REQUESTS R
, FEM_BR_DIS_MBR_HIER M
, FEM_HIERARCHIES H
, FEM_HIER_DEFINITIONS D
WHERE R.REQUEST_ID = M.REQUEST_ID
AND M.HIERARCHY_OBJECT_ID = H.HIERARCHY_OBJ_ID
AND M.HIERARCHY_OBJ_DEF_ID = D.HIERARCHY_OBJ_DEF_ID
AND H.FLATTENED_ROWS_FLAG = 'Y'
AND D.FLATTENED_ROWS_COMPLETION_CODE = 'COMPLETED' START WITH M.CHILD_MEMBER_ID = M.PARENT_MEMBER_ID CONNECT BY PRIOR M.CHILD_MEMBER_ID = M.PARENT_MEMBER_ID
AND PRIOR M.HIERARCHY_OBJ_DEF_ID = M.HIERARCHY_OBJ_DEF_ID
AND PRIOR M.REQUEST_ID = M.REQUEST_ID
AND M.CHILD_MEMBER_ID <> M.PARENT_MEMBER_ID