ODI 10g Scenario Mapping Query (Work Repository)

/* WORK REPOSITORY SCENARIO MAPPING */
SELECT
    DISTINCT
    ODIWD.SNP_PROJECT.PROJECT_NAME,
    ODIWD.SNP_FOLDER.FOLDER_NAME,
    ODIWD.SNP_PACKAGE.PACK_NAME,  
    ODIWD.SNP_SCEN.SCEN_NAME,
    ODIWD.SNP_SCEN.SCEN_VERSION,
    CASE
        WHEN ODIWD.SNP_SCEN_STEP.STEP_TYPE = 'VS' THEN 'Set Variable'
        WHEN ODIWD.SNP_SCEN_STEP.STEP_TYPE = 'VE' THEN 'Evaluate Variable'
        WHEN ODIWD.SNP_SCEN_STEP.STEP_TYPE = 'VP' THEN 'Populate Variable'
        WHEN ODIWD.SNP_SCEN_STEP.STEP_TYPE = 'OE' THEN 'Execute OS command'
        WHEN ODIWD.SNP_SCEN_STEP.STEP_TYPE = 'SE' THEN 'Execute Sunopsis command'
        WHEN ODIWD.SNP_SCEN_STEP.STEP_TYPE = 'V' THEN 'Refresh Variable'
        WHEN ODIWD.SNP_SCEN_STEP.STEP_TYPE = 'F' THEN 'Flow'
        WHEN ODIWD.SNP_SCEN_STEP.STEP_TYPE = 'T' THEN 'Procedure'
        WHEN ODIWD.SNP_SCEN_STEP.STEP_TYPE = 'MR' THEN 'Model Reverse'
        WHEN ODIWD.SNP_SCEN_STEP.STEP_TYPE = 'MC' THEN 'Model Check'
    END AS STEP_TYPE,
    ODIWD.SNP_SCEN_STEP.NNO,
    ODIWD.SNP_SCEN_STEP.STEP_NAME,
    ODIWD.SNP_SCEN_STEP.VAR_NAME,
    ODIWD.SNP_SOURCE_TAB.LSCHEMA_NAME AS SOURCE_SCHEMA,
    ODIWD.SNP_TABLE.TABLE_NAME AS SOURCE_TABLE,
    ODIWD.SNP_SOURCE_TAB.MOD_CODE AS SOURCE_MODEL,  
    ODIWD.SNP_POP.LSCHEMA_NAME AS TARGET_SCHEMA,
    ODIWD.SNP_POP.TABLE_NAME AS TARGET_TABLE,
    ODIWD.SNP_POP.MOD_CODE AS TARGET_MODEL,
    ODIWD.SNP_PLAN_AGENT.LAGENT_NAME,
    ODIWD.SNP_PLAN_AGENT.CONTEXT_CODE,
    ODIWD.SNP_PLAN_AGENT.S_HOUR || ':' || ODIWD.SNP_PLAN_AGENT.S_MINUTE || ':' || ODIWD.SNP_PLAN_AGENT.S_SECOND AS SCHED_TIME,
    CASE
        WHEN SNP_PLAN_AGENT.R_TIME = 1 THEN 'Infinite'
        WHEN SNP_PLAN_AGENT.R_TIME = 0 THEN 'One run'
        WHEN SNP_PLAN_AGENT.R_TIME = 1 THEN 'One repetition (two runs)' /* could be -1 */
    END AS REPEAT_TIME,
    SNP_PLAN_AGENT.S_TYPE,
    CASE
        WHEN SNP_PLAN_AGENT.S_TYPE = 'H' THEN 'Hourly'
        WHEN SNP_PLAN_AGENT.S_TYPE = 'D' THEN 'Daily'
        WHEN SNP_PLAN_AGENT.S_TYPE = 'W' THEN 'Weekly'
        WHEN SNP_PLAN_AGENT.S_TYPE = 'M' THEN 'Monthly'
        WHEN SNP_PLAN_AGENT.S_TYPE = 'Y' THEN 'Yearly'
        WHEN SNP_PLAN_AGENT.S_TYPE = 'S' THEN 'Simple (see exec.cycle)'
        ELSE 'Unknown'
    END AS SCHED_TYPE,
    R_DUR_INTERVAL,
    CASE
        WHEN SNP_PLAN_AGENT.R_INTERVAL_UNIT = 'H' THEN 'Hour(s)'
        WHEN SNP_PLAN_AGENT.R_INTERVAL_UNIT = 'M' THEN 'Minute(s)'
        WHEN SNP_PLAN_AGENT.R_INTERVAL_UNIT = 'S' THEN 'Second(s)'
    END AS INTERVAL_UNIT,
    R_DUR_CYCLE,
    R_CYCLE_UNIT
FROM ODIWD.SNP_SCEN
INNER JOIN ODIWD.SNP_PLAN_AGENT ON ODIWD.SNP_SCEN.SCEN_NAME = ODIWD.SNP_PLAN_AGENT.SCEN_NAME
                         AND ODIWD.SNP_SCEN.SCEN_VERSION = ODIWD.SNP_PLAN_AGENT.SCEN_VERSION
LEFT OUTER JOIN ODIWD.SNP_PACKAGE ON ODIWD.SNP_SCEN.I_PACKAGE = ODIWD.SNP_PACKAGE.I_PACKAGE
LEFT OUTER JOIN ODIWD.SNP_FOLDER ON ODIWD.SNP_PACKAGE.I_FOLDER = ODIWD.SNP_FOLDER.I_FOLDER
LEFT OUTER JOIN ODIWD.SNP_PROJECT ON ODIWD.SNP_FOLDER.I_PROJECT = ODIWD.SNP_PROJECT.I_PROJECT
LEFT OUTER JOIN ODIWD.SNP_SCEN_STEP ON ODIWD.SNP_SCEN.SCEN_NO = ODIWD.SNP_SCEN_STEP.SCEN_NO
LEFT OUTER JOIN ODIWD.SNP_POP ON ODIWD.SNP_SCEN_STEP.STEP_NAME = ODIWD.SNP_POP.POP_NAME
LEFT OUTER JOIN ODIWD.SNP_POP_COL ON ODIWD.SNP_POP_COL.I_POP = ODIWD.SNP_POP.I_POP
LEFT OUTER JOIN ODIWD.SNP_POP_CLAUSE ON ODIWD.SNP_POP_CLAUSE.I_POP = ODIWD.SNP_POP.I_POP
LEFT OUTER JOIN ODIWD.SNP_TXT S_TXT ON ODIWD.S_TXT.I_TXT = ODIWD.SNP_POP_CLAUSE.I_TXT_SQL
LEFT OUTER JOIN ODIWD.SNP_TXT ON ODIWD.SNP_TXT.I_TXT = ODIWD.SNP_POP_COL.I_TXT_MAP
LEFT OUTER JOIN ODIWD.SNP_SOURCE_TAB ON ODIWD.SNP_SOURCE_TAB.I_POP = ODIWD.SNP_POP.I_POP
LEFT OUTER JOIN ODIWD.SNP_TXT_CROSSR ON ODIWD.SNP_TXT_CROSSR.I_TXT = ODIWD.SNP_TXT.I_TXT
LEFT OUTER JOIN ODIWD.SNP_COL ON ODIWD.SNP_COL.I_COL = ODIWD.SNP_TXT_CROSSR.I_COL
LEFT OUTER JOIN ODIWD.SNP_TABLE ON ODIWD.SNP_TABLE.I_TABLE = ODIWD.SNP_COL.I_TABLE
WHERE 1=1
--AND SNP_PLAN_AGENT.STAT_PLAN = 'E' /* Status of planning: Enabled/Disabled */
--AND SNP_SCEN_STEP.STEP_TYPE != 'SE' /* Exclude SE-Execute Sunopsis command */
ORDER BY ODIWD.SNP_SCEN.SCEN_NAME, ODIWD.SNP_SCEN.SCEN_VERSION, ODIWD.SNP_SCEN_STEP.NNO