Hi Dani, Pls follow the steps below. Step 1:- Create the First View.CREATE OR REPLACE VIEW UV_OBS_LEVELS
(LEVEL1_ID, LEVEL1_NAME, LEVEL1_DEPTH, LEVEL2_ID, LEVEL2_NAME,
LEVEL2_DEPTH, LEVEL3_ID, LEVEL3_NAME, LEVEL3_DEPTH, LEVEL4_ID,
LEVEL4_NAME, LEVEL4_DEPTH, LEVEL5_ID, LEVEL5_NAME, LEVEL5_DEPTH,
LEVEL6_ID, LEVEL6_NAME, LEVEL6_DEPTH, LEVEL7_ID, LEVEL7_NAME,
LEVEL7_DEPTH, LEVEL8_ID, LEVEL8_NAME, LEVEL8_DEPTH, LEVEL9_ID,
LEVEL9_NAME, LEVEL9_DEPTH, LEVEL10_ID, LEVEL10_NAME, LEVEL10_DEPTH,
OBS_PATH)
AS
SELECT A.ID LEVEL1_ID, A.NAME LEVEL1_NAME, A.DEPTH LEVEL1_DEPTH, B.ID LEVEL2_ID, B.NAME LEVEL2_NAME, B.DEPTH LEVEL2_DEPTH
, C.ID LEVEL3_ID, C.NAME LEVEL3_NAME, C.DEPTH LEVEL3_DEPTH, D.ID LEVEL4_ID, D.NAME LEVEL4_NAME, D.DEPTH LEVEL4_DEPTH
, E.ID LEVEL5_ID, E.NAME LEVEL5_NAME, E.DEPTH LEVEL5_DEPTH, F.ID LEVEL6_ID, F.NAME LEVEL6_NAME, F.DEPTH LEVEL6_DEPTH
, G.ID LEVEL7_ID, G.NAME LEVEL7_NAME, G.DEPTH LEVEL7_DEPTH, H.ID LEVEL8_ID, H.NAME LEVEL8_NAME, H.DEPTH LEVEL8_DEPTH
, I.ID LEVEL9_ID, I.NAME LEVEL9_NAME, I.DEPTH LEVEL9_DEPTH, J.ID LEVEL10_ID, J.NAME LEVEL10_NAME, J.DEPTH LEVEL10_DEPTH
, CASE WHEN J.ID IS NOT NULL THEN J.NAME || '/' END || CASE WHEN I.ID IS NOT NULL THEN I.NAME || '/' END
|| CASE WHEN H.ID IS NOT NULL THEN H.NAME || '/' END || CASE WHEN G.ID IS NOT NULL THEN G.NAME || '/' END
|| CASE WHEN F.ID IS NOT NULL THEN F.NAME || '/' END || CASE WHEN E.ID IS NOT NULL THEN E.NAME || '/' END
|| CASE WHEN D.ID IS NOT NULL THEN D.NAME || '/' END || CASE WHEN C.ID IS NOT NULL THEN C.NAME || '/' END
|| CASE WHEN B.ID IS NOT NULL THEN B.NAME || '/' END || CASE WHEN A.ID IS NOT NULL THEN A.NAME || '/' END OBS_PATH
FROM
PRJ_OBS_UNITS A
, PRJ_OBS_UNITS B
, PRJ_OBS_UNITS C
, PRJ_OBS_UNITS D
, PRJ_OBS_UNITS E
, PRJ_OBS_UNITS F
, PRJ_OBS_UNITS G
, PRJ_OBS_UNITS H
, PRJ_OBS_UNITS I
, PRJ_OBS_UNITS J
WHERE
A.PARENT_ID = B.ID(+)
AND B.PARENT_ID = C.ID(+)
AND C.PARENT_ID = D.ID(+)
AND D.PARENT_ID = E.ID(+)
AND E.PARENT_ID = F.ID(+)
AND F.PARENT_ID = G.ID(+)
AND G.PARENT_ID = H.ID(+)
AND H.PARENT_ID = I.ID(+)
AND I.PARENT_ID = J.ID(+)
/Step 2:- Create the 2nd View.CREATE OR REPLACE VIEW UV_OBS_DETAILS
(TYPE_ID, TYPE_UNIQUE_NAME, TYPE_NAME, OBS_UNIQUE_NAME, OBS_NAME,
DEPTH, OBS_PARENT_ID, OBS_ID)
AS
SELECT
OBU.TYPE_ID,
OBT.UNIQUE_NAME TYPE_UNIQUE_NAME,
OBT.NAME TYPE_NAME ,
-- OBA.UNIT_ID OBS_ID,
OBU.UNIQUE_NAME OBS_UNIQUE_NAME,
OBU.NAME OBS_NAME,
OBU.DEPTH,
OBU.PARENT_ID OBS_PARENT_ID,
OBU.ID OBS_ID
FROM
PRJ_OBS_UNITS OBU ,
PRJ_OBS_TYPES OBT
WHERE
OBU.TYPE_ID = OBT.ID (+)
/-- Create the Function For fetching the Resource OBS.Pass the id from srm_resources or you can use the below Query itself. CREATE OR REPLACE FUNCTION Uf_Get_RT_OBS
(RESOURCE_ID NUMBER)
RETURN VARCHAR2
IS
RT_OBS_NAME VARCHAR2(240);BEGIN SELECT SUBSTR(OL.OBS_PATH,1,LENGTH(OL.OBS_PATH)-1) INTO RT_OBS_NAME
FROM
UV_PRJ_RSM_OBS_DETAILS OD, UV_OBS_LEVELS OL
WHERE
OD.OBS_ID = OL.LEVEL1_ID (+)
AND OD.TABLE_NAME = 'SRM_RESOURCES' AND OD.TYPE_UNIQUE_NAME = 'RT_OBS'
AND OD.RECORD_ID = RESOURCE_ID; RETURN RT_OBS_NAME;EXCEPTION
WHEN NO_DATA_FOUND THEN RETURN 'N/A';
WHEN OTHERS THEN RETURN NULL;
END;
/ select unique_name,first_name,last_name,Uf_Get_RT_OBS(id) from srm_resources where unique_name =''; Hope this helps you........................ Regards,J.sundar