Query to fetch the bi report output destination details(Email- from mail, to mail, FTP- Server details and output name etc.)

with data as

(select distinct VALUE xmlval,requestid

from fusion_ora_ess.request_property_view

  where UPPER(NAME) = 'CONTROL_XML'),

 x as (select PARAMETER1,DEL_CHANNEL,Value,url.requestid,PARAMETER2,PARAMETER3,PARAMETER4,PARAMETER5,PARAMETER6,PARAMETER7,PARAMETER8

  from data d, 

       xmltable('/ROWSET/*' passing xmltype(d.xmlval) 

        columns

    PARAMETER1 varchar2(254) path 'PARAMETER1',

    PARAMETER2 varchar2(254) path 'PARAMETER2',

    PARAMETER3 varchar2(254) path 'PARAMETER3',

    PARAMETER4 varchar2(254) path 'PARAMETER4',

    PARAMETER5 varchar2(254) path 'PARAMETER5',

    PARAMETER6 varchar2(254) path 'PARAMETER6',

    PARAMETER7 varchar2(254) path 'PARAMETER7',

    PARAMETER8 varchar2(254) path 'PARAMETER8',

    DEL_CHANNEL varchar2(254) path 'DEL_CHANNEL'),

    fusion_ora_ess.request_property_view url

    where

    url.requestid = d.requestid

    --and url.requestid = '457286'

    and url.name = 'report_url'

   

    )

 

    select * from x where DEL_CHANNEL <> 'DB'