Query to Get eText Template Details

SELECT

    f.FORMAT_NAME

  , f.FORMAT_CODE

  , NVL(f.SEEDED_FLAG,'N') SEEDED_FORMAT

  , t.TEMPLATE_NAME

  , t.TEMPLATE_CODE

  , t.TEMPLATE_TYPE_CODE

  , x.XDO_FILE_TYPE

  , x.FILE_NAME

FROM

    IBY_FORMATS_VL f

  , XDO_TEMPLATES_VL t

  , XDO_LOBS x

WHERE

    f.FORMAT_TYPE_CODE           = 'OUTBOUND_PAYMENT_INSTRUCTION'

    AND f.FORMAT_TEMPLATE_CODE   = t.TEMPLATE_CODE

    AND t.APPLICATION_SHORT_NAME = 'IBY'

    AND t.TEMPLATE_CODE          = x.LOB_CODE

    AND t.TEMPLATE_TYPE_CODE     = 'ETEXT'

    AND x.XDO_FILE_TYPE         <> 'RTF-ETEXT'

    AND x.LOB_TYPE               = 'TEMPLATE_SOURCE'

ORDER BY

    t.TEMPLATE_NAME

  , f.FORMAT_NAME;