Payment : Query to get Layout Templates - Metadata and RTF/eText files for all Outbound Payment Instruction Layout Templates

SELECT
 xtv.application_short_name tmpl_app,
 xtv.template_code,
 xtv.ds_app_short_name ds_app,
 xtv.data_source_code,
 xtv.template_type_code tmpl_type,
 xtv.default_language def_lang,
 xtv.default_territory def_terr,
 xtv.template_status,
 xtv.start_date,
 xtv.end_date,
 xtv.template_name,
 xtv.description,
 xtv.created_by,
 xtv.creation_date,
 xtv.last_updated_by,
 xtv.last_update_date,
 xtv.last_update_login,
 (SELECT
  application_name 
 FROM
  fnd_application_vl 
 WHERE
  application_short_name = xtv.application_short_name) application_name,
 (SELECT
  meaning 
 FROM
  fnd_lookups 
 WHERE
  lookup_type = 'XDO_TEMPLATE_TYPE' 
  AND lookup_code = xtv.template_type_code) template_type,
 (SELECT
  data_source_name 
 FROM
  xdo_ds_definitions_vl 
 WHERE
  data_source_code = xtv.data_source_code 
  AND application_short_name = xtv.ds_app_short_name) data_source_name,
 (SELECT
  file_name 
 FROM
  xdo_lobs 
 WHERE
  (
   (
    lob_type = 'TEMPLATE' 
    AND xdo_file_type != 'RTF' 
    AND xdo_file_type = xtv.template_type_code 
    AND xdo_lobs.LANGUAGE = xtv.default_language 
    AND xdo_lobs.territory = xtv.default_territory
   ) 
   OR (
    lob_type = 'TEMPLATE_SOURCE' 
    AND xdo_file_type IN (
     'RTF', 'RTF-ETEXT'
    ) 
    AND xdo_lobs.LANGUAGE = xtv.default_language 
    AND xdo_lobs.territory = xtv.default_territory
   ) 
   OR (
    xdo_file_type = 'RTF' 
    AND lob_type = 'TEMPLATE_SOURCE' 
    AND LANGUAGE = xtv.mls_language 
    AND territory = xtv.mls_territory 
    AND EXISTS (
     SELECT
      mls.lob_code 
     FROM
      xdo_lobs mls 
     WHERE
      mls.lob_type = 'MLS_TEMPLATE' 
      AND mls.lob_code = xtv.template_code 
      AND mls.application_short_name = xtv.application_short_name 
      AND mls.LANGUAGE = xtv.default_language 
      AND mls.territory = xtv.default_territory
    ) 
    AND NOT EXISTS (
     SELECT
      LOCAL.lob_code 
     FROM
      xdo_lobs LOCAL 
     WHERE
      LOCAL.lob_type = 'TEMPLATE_SOURCE' 
      AND LOCAL.lob_code = xtv.template_code 
      AND LOCAL.application_short_name = xtv.application_short_name 
      AND LOCAL.LANGUAGE = xtv.default_language 
      AND LOCAL.territory = xtv.default_territory
    )
   )
  ) 
  AND lob_code = xtv.template_code 
  AND xdo_lobs.application_short_name = xtv.application_short_name
 ) AS default_template_file, (
  SELECT
   file_name 
  FROM
   xdo_lobs 
  WHERE
   lob_type = 'TEMPLATE_SOURCE' 
   AND lob_code = xtv.template_code 
   AND xdo_lobs.application_short_name =xtv.application_short_name 
   AND xdo_lobs.LANGUAGE = xtv.mls_language 
   AND xdo_lobs.territory = xtv.mls_territory
 ) AS mls_template_file, (
  SELECT
   NAME 
  FROM
   fnd_iso_languages_vl 
  WHERE
   iso_language_2 = xtv.default_language
 ) AS default_file_lang, DECODE (xtv.default_territory, '00', '', ftv.territory_short_name) AS default_file_terr, xtv.mls_language, xtv.mls_territory, xtv.default_output_type 
FROM
 xdo_templates_vl xtv,
 fnd_application_vl fav,
 fnd_territories_vl ftv 
WHERE
 1 = 1 
 AND fav.application_short_name = xtv.application_short_name 
 AND ftv.territory_code(+) = xtv.default_territory -- Only Outbound Payment Instructions 
 AND xtv.application_short_name = 'IBY' 
 AND data_source_code = 'IBY_FD_INSTRUCTION_1_0';