EBS:BI Publisher模板文件查询

BI Publisher模板文件查询 

--N: Oracle XML Publisher>>主页>>模板 
SELECT XT.TEMPLATE_ID,
       XT.TEMPLATE_NAME AS "模板-名称",
       XT.TEMPLATE_CODE AS "模板-代码",
       XT.APPLICATION_ID, 
       XT.APPLICATION_SHORT_NAME AS "产品代码",
       (SELECT FA.APPLICATION_NAME FROM FND_APPLICATION_VL FA
        WHERE FA.APPLICATION_ID = XT.APPLICATION_ID
         AND ROWNUM =1
       ) AS "应用产品",
       XT.TEMPLATE_TYPE_CODE AS "模板-类型",
       XT.DATA_SOURCE_CODE AS "数据定义代码",
       XDD.DATA_SOURCE_NAME AS "数据定义",
       XT.DEFAULT_OUTPUT_TYPE AS "默认输出类型",
       XL.FILE_NAME AS "文件名",
       XL.LANGUAGE AS "语言代码",
       (SELECT FIL.NAME FROM FND_ISO_LANGUAGES_VL FIL
         WHERE FIL.ISO_LANGUAGE_2 = XL.LANGUAGE
        AND ROWNUM = 1 ) AS "语言",
       XL.TERRITORY AS "地区代码",
       (SELECT FT.TERRITORY_SHORT_NAME FROM fnd_territories_vl FT
         WHERE FT.TERRITORY_CODE = XL.TERRITORY
         AND ROWNUM =1 ) AS "地区",
       XT.CREATION_DATE
       ,XT.*
  FROM APPS.XDO_TEMPLATES_VL XT,  -- 模板
       XDO_DS_DEFINITIONS_VL  XDD,  -- 数据定义
       XDO.XDO_LOBS XL  -- 模板文件
 WHERE XT.APPLICATION_SHORT_NAME = 'CUX'
  -- AND XT.TEMPLATE_NAME LIKE 'CUX:%'
  AND XDD.APPLICATION_SHORT_NAME = XT.APPLICATION_SHORT_NAME 
  AND XDD.DATA_SOURCE_CODE = XT.DATA_SOURCE_CODE
  AND XT.TEMPLATE_CODE = XL.LOB_CODE(+) -- 可能未上传模板文件
  AND XL.LOB_TYPE(+) = 'TEMPLATE_SOURCE' -- 模板文件源 
  AND XT.TEMPLATE_NAME IN (
        'CUX:工序跟踪单订单')