ODI流程清单

select * from (
select DISTINCT 
pro.project_name 项目,
yb.TABLE_NAME 源表,
jk.pop_name 接口,
CASE WHEN xm2.PAR_I_FOLDER is not null then (SELECT XM3.FOLDER_NAME FROM ly_odi_gzk.SNP_FOLDER XM3 
        WHERE XM3.I_FOLDER=XM2.PAR_I_FOLDER)--三层根文件夹
        when XM.PAR_I_FOLDER IS NOT NULL THEN xm2.FOLDER_NAME
        ELSE XM.FOLDER_NAME END 项目文件夹,
CASE WHEN xm2.PAR_I_FOLDER IS NOT NULL THEN xm2.FOLDER_NAME||'-'||XM.FOLDER_NAME
        WHEN XM.PAR_I_FOLDER IS NOT NULL THEN XM.FOLDER_NAME ELSE '' END 项目子文件夹,
ymx.mod_name 源表模型,
mmx.mod_name 目标表模型,
SESS.AGENT_NAME 物理代理,
agen.lagent_name 逻辑代理,
pschena.EXT_NAME 源物理体系架构,
yb.LSCHEMA_NAME 源逻辑体系架构,
mpschena.EXT_NAME 目标物理体系结构,
jk.LSCHEMA_NAME 目标逻辑体系结构,
ymx.REV_CONTEXT 上下文,
cxb.pack_name 程序包,
--SCEN.SCEN_NAME||' '||SCEN.SCEN_VERSION 场景,
case when agen.I_PLAN_AGENT is null then  '无调度' when agen.stat_plan = 'D' then '非活动' 
  when agen.R_TIME||agen.s_type||agen.stat_plan = '1SE' then  '活动但无设置循环执行' 
  when agen.stat_plan = 'E' then  '活动' else agen.stat_plan end 场景调度,--调度状态
case when agen.I_PLAN_AGENT is null then  '/' --无调度
  when agen.R_TIME||agen.s_type='1D' then agen.s_hour||':'||agen.s_minute||':'||agen.s_second||'/D'
        when agen.R_TIME||agen.s_type='1S' then agen.s_hour||':'||agen.s_minute||':'||agen.s_second
        when agen.R_TIME||agen.s_type='1W' then agen.s_hour||':'||agen.s_minute||':'||agen.s_second||'/W-'||agen.S_WEEK_DAY
        when agen.R_TIME||agen.s_type='1M' then agen.s_hour||':'||agen.s_minute||':'||agen.s_second||'/M-'||agen.S_MONTH_DAY
        else  agen.r_dur_interval||agen.r_interval_unit||(
          case when agen.s_type='S' then ''--' & '||agen.s_hour||':'||agen.s_minute||':'||agen.s_second
            when agen.s_type='W' then ' & '||agen.s_hour||':'||agen.s_minute||':'||agen.s_second||'/W-'||agen.S_WEEK_DAY
              when agen.s_type='M' then ' & '||agen.s_hour||':'||agen.s_minute||':'||agen.s_second||'/M-'||agen.S_MONTH_DAY
                else ' & '||agen.s_type end
          ) end 调度周期,
--pschena.EXT_NAME 物理结构连接信息,
jk.TABLE_NAME 目标表
from 
ly_odi_gzk.snp_pop jk
left join  ly_odi_gzk.snp_folder xm on jk.I_folder =xm.I_folder
left join ly_odi_gzk.snp_folder xm2 on xm.PAR_I_FOLDER = xm2.I_FOLDER
left join ly_odi_gzk.snp_source_tab yb on yb.I_data_set=jk.I_POP
left join ly_odi_gzk.snp_table ytb on yb.I_TABLE=ytb.I_TABLE
left join ly_odi_gzk.snp_table mtb on jk.I_TABLE=mtb.I_TABLE
left join ly_odi_gzk.snp_model ymx on ytb.I_MOD=ymx.I_MOD
left join ly_odi_gzk.snp_model mmx on mtb.I_MOD=mmx.I_MOD
left join ly_odi_gzk.SNP_STEP step on step.I_POP=jk.I_POP
left join ly_odi_gzk.SNP_PACKAGE cxb on step.I_PACKAGE=cxb.I_PACKAGE
left join ly_odi_gzk.SNP_SCEN SCEN ON (CASE WHEN SCEN.I_PACKAGE IS NULL THEN SCEN.I_POP ELSE SCEN.I_PACKAGE END) = (CASE WHEN SCEN.I_PACKAGE IS NULL THEN JK.I_POP ELSE CXB.I_PACKAGE END)
left join ly_odi_gzk.SNP_PLAN_AGENT agen on UPPER(SCEN.SCEN_NAME||SCEN.SCEN_VERSION) = UPPER(AGEN.SCEN_NAME||AGEN.SCEN_VERSION)
--left join (select distinct SCEN_NAME,agent_name from ly_odi_gzk.SNP_SESSION) sess on UPPER(sess.SCEN_NAME) = UPPER(case when cxb.pack_name is null then jk.pop_name else cxb.pack_name end)
LEFT JOIN (SELECT * FROM (SELECT T.*,RANK()OVER(PARTITION BY SCEN_NAME||SCEN_VERSION ORDER BY SESS_BEG DESC) RANKNUM FROM ly_odi_gzk.SNP_SESSION T WHERE SESS_NAME <> 'CXB') WHERE RANKNUM = '1') SESS ON UPPER(SESS.SESS_NAME) =UPPER (case when cxb.pack_name is null then jk.pop_name else cxb.pack_name end)
left join ly_odi_zlk.snp_lschema lschema on UPPER(yb.LSCHEMA_NAME) = UPPER(lschema.LSCHEMA_NAME)--源逻辑结构
left join LY_ODI_ZLK.SNP_PSCHEMA_CONT pschena_cont on lschema.I_LSCHEMA = pschena_cont.I_LSCHEMA--源物理逻辑结构映射
left join LY_ODI_ZLK.SNP_PSCHEMA pschena on pschena_cont.I_PSCHEMA = pschena.I_PSCHEMA--源物理逻辑
left join ly_odi_zlk.snp_lschema mlschema on UPPER(jk.LSCHEMA_NAME) = UPPER(mlschema.LSCHEMA_NAME)--目源逻辑结构
left join LY_ODI_ZLK.SNP_PSCHEMA_CONT mpschena_cont on mlschema.I_LSCHEMA = mpschena_cont.I_LSCHEMA--目标物理逻辑结构映射
left join LY_ODI_ZLK.SNP_PSCHEMA mpschena on mpschena_cont.I_PSCHEMA = mpschena.I_PSCHEMA--目标物理逻辑
left join ly_odi_gzk.snp_project pro on xm.i_project = pro.i_project
)

order by 项目文件夹 ,项目子文件夹, 接口;