To get all objects under Folder from Repository Tables

Query to get workflow, Session, Mapping, SRC instance, Target instance, SRC (Physical) and Target (Physical) names by passing folder name as input.

SELECT F.SUBJ_NAME        AS FOLDER_NAME,

       WF.TASK_NAME       AS WORKFLOW_NAME,

       SESS.INSTANCE_NAME AS SESSION_NAME,

       M.MAPPING_NAME     AS MAPPING_NAME,

       SRC.INSTANCE_NAME  AS SOURCE_NAME,

       TGT.INSTANCE_NAME  AS TARGET_NAME,

       PS.SOURCE_NAME     AS PHY_SRC_NAME,

       PT.TARGET_NAME     AS PHY_TARGET_NAME

FROM   OPB_SUBJECT F,

       (SELECT TASK_NAME,TASK_ID,

               SUBJECT_ID,IS_VISIBLE,

               TASK_TYPE,MAX(VERSION_NUMBER)

        FROM   OPB_TASK

        GROUP  BY TASK_NAME,TASK_ID,

                  SUBJECT_ID,IS_VISIBLE,

                  TASK_TYPE) WF,

       (SELECT WORKFLOW_ID,INSTANCE_ID,

               TASK_ID, INSTANCE_NAME

        FROM   OPB_TASK_INST

        WHERE  ( WORKFLOW_ID, INSTANCE_ID, TASK_ID, VERSION_NUMBER ) IN 

       (SELECT WORKFLOW_ID,INSTANCE_ID,

               TASK_ID,VERSION_NUMBER

                     FROM

               (SELECT WORKFLOW_ID,INSTANCE_ID,

                       TASK_ID,

                       MAX(VERSION_NUMBER) AS VERSION_NUMBER

               FROM    OPB_TASK_INST

                       GROUP  BY WORKFLOW_ID,INSTANCE_ID,

                                 TASK_ID))) WL,

       (SELECT WORKFLOW_ID,INSTANCE_ID,

               TASK_ID,INSTANCE_NAME,

               TASK_TYPE

        FROM   OPB_TASK_INST

        WHERE  ( WORKFLOW_ID, INSTANCE_ID, TASK_ID, VERSION_NUMBER ) IN 

       (SELECT WORKFLOW_ID,INSTANCE_ID,

               TASK_ID,VERSION_NUMBER

                          FROM

               (SELECT WORKFLOW_ID,INSTANCE_ID,

                       TASK_ID,

                       MAX(VERSION_NUMBER) AS VERSION_NUMBER

               FROM    OPB_TASK_INST

                       GROUP  BY WORKFLOW_ID,INSTANCE_ID,

                                 TASK_ID))) SESS,

       (SELECT SESSION_ID,MAPPING_ID,

               MAX(VERSION_NUMBER)

        FROM   OPB_SESSION

        GROUP  BY SESSION_ID,MAPPING_ID) S,

       (SELECT MAPPING_NAME,IS_VISIBLE,

               MAPPING_ID,MAX(VERSION_NUMBER)

        FROM   OPB_MAPPING

        GROUP  BY MAPPING_NAME,IS_VISIBLE,

                  MAPPING_ID) M,

       (SELECT MAPPING_ID,INSTANCE_NAME,

               WIDGET_TYPE,WIDGET_ID,

               MAX(VERSION_NUMBER)

        FROM   OPB_WIDGET_INST

        GROUP  BY MAPPING_ID,INSTANCE_NAME,

                  WIDGET_TYPE,WIDGET_ID) SRC,

       (SELECT MAPPING_ID,INSTANCE_NAME,

               WIDGET_TYPE,WIDGET_ID,

               MAX(VERSION_NUMBER)

        FROM   OPB_WIDGET_INST

        GROUP  BY MAPPING_ID,INSTANCE_NAME,

                  WIDGET_TYPE,WIDGET_ID) TGT,

       OPB_SRC PS,

       OPB_TARG PT

WHERE  WF.IS_VISIBLE = 1

       AND WF.SUBJECT_ID = F.SUBJ_ID

       AND WF.TASK_ID = WL.WORKFLOW_ID

       AND WF.TASK_TYPE = 71

       AND ( WL.TASK_ID = SESS.WORKFLOW_ID

              OR WL.WORKFLOW_ID = SESS.WORKFLOW_ID )

       AND WF.IS_VISIBLE = 1

       AND SESS.TASK_TYPE IN ( 58, 68 )

       AND SESS.TASK_ID = S.SESSION_ID

       AND S.MAPPING_ID = M.MAPPING_ID

       AND M.IS_VISIBLE = 1

       AND SRC.MAPPING_ID = M.MAPPING_ID

       AND SRC.WIDGET_TYPE = 1

       AND TGT.MAPPING_ID = M.MAPPING_ID

       AND TGT.WIDGET_TYPE = 2

       AND TGT.WIDGET_ID = PT.TARGET_ID

       AND SRC.WIDGET_ID = PS.SRC_ID

       AND F.SUBJ_NAME = 'FOLDER_NAME'