API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Jul 9th, 2018
121
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
PostgreSQL 7.51 KB | None | 0 0
raw download clone embed print report
  1. CREATE OR REPLACE FUNCTION fundtrack.prc_report_0015(p_custodycd character varying, pv_tlid character varying, pv_tlname character varying, pv_language character varying)
  2. RETURNS SETOF refcursor
  3. LANGUAGE plpgsql
  4. AS $function$
  5.     DECLARE
  6.       ret refcursor;           -- Declare cursor variables
  7.       ref2 refcursor;  
  8.         v_logsctx           text;
  9.         v_logsbody          text;
  10.         v_exception         text;
  11.         l_frdate            date;
  12.         l_todate            date;
  13.     BEGIN
  14.      
  15.         v_logsctx := plog.init('prc_report_0015', 30, false, false, false);
  16.         v_logsbody:= 'prc_report_0015: '
  17.                     ||', pv_tlid = ' || pv_tlid
  18.                     ||', pv_tlname = ' || pv_tlname
  19.                     ||', pv_language = ' || pv_language;
  20.         v_logsctx   := plog.setbeginsection(v_logsctx, v_logsbody);
  21.         ret := 'ret';
  22.       OPEN ret FOR
  23.      
  24.       select
  25.        substr(cf.custodycd,1,1) as ac01,
  26.        substr(cf.custodycd,2,1) as ac02,
  27.        substr(cf.custodycd,3,1) as ac03,
  28.        substr(cf.custodycd,4,1) as ac04,
  29.        substr(cf.custodycd,5,1) as ac05,
  30.        substr(cf.custodycd,6,1) as ac06,
  31.        substr(cf.custodycd,7,1) as ac07,
  32.        substr(cf.custodycd,8,1) as ac08,               
  33.        substr(cf.custodycd,9,1) as ac09,
  34.        substr(cf.custodycd,10,1) as ac010,
  35.        cf.custid,
  36.        cf.custodycd,
  37.        cf.fullname,
  38.        cf.custtype,
  39.        'X' as custp,
  40.        cf.sex,
  41.        'X' aS sextp,
  42.         to_char( cf.birthdate,fn_systemnums('systemnums.c_date_format')) birthdate ,
  43.         cf.idcode,
  44.         to_char( cf.iddate,fn_systemnums('systemnums.c_date_format'))  iddate,
  45.         to_char( cf.idexpdated,fn_systemnums('systemnums.c_date_format'))  idexpdated,
  46.         cf.idplace,
  47.         cf.tradingcode,
  48.         cf.taxno,
  49.         cf.regaddress,
  50.         cf.address,
  51.         ct.position,
  52.         a1.cdcontent as country,
  53.         cf.phone,
  54.         cf.mobile,
  55.         cf.email,
  56.         cf.fax,
  57.         cf.bankacc,
  58.         cf.bankcode,
  59.         c.brname as citybank,
  60.         cf.fullname as bankacname,
  61.         b.mbname as bankname,
  62.         au.custname as au_custname,
  63.         au.position as au_position,
  64.         au.sex as au_sex,
  65.         'X' aS sex_au_tp,
  66.         to_char( au.birthdate,fn_systemnums('systemnums.c_date_format')) as au_birthdate ,
  67.         au.idcode as au_idcode,
  68.         to_char( au.iddate,fn_systemnums('systemnums.c_date_format'))  as au_iddate,
  69.         au.idplace as au_idplace,
  70.         au.regaddress as au_regaddress,
  71.         au.address as au_address,
  72.         a2.cdcontent as au_country,
  73.         au.mobile as au_mobile,
  74.         au.email as au_email,
  75.         au.fax as au_fax,
  76.         case when au.auth_all = 'Y' then 'X' else ' '
  77.         end auth_all,
  78.         case when au.auth_order = 'Y'
  79.         and au.auth_all = 'Y' then ' '
  80.         when au.auth_order = 'Y'
  81.         and au.auth_all <> 'Y' then 'X'
  82.         else ' '
  83.         end auth_order,
  84.         case when cf.rcv_email = 'Y' then 'X' else ' '
  85.         end as rcv_email,
  86.         case when cf.rcv_sms = 'Y' then 'X' else ' '
  87.         end as rcv_sms  ,
  88.         case when cf.rcv_mail = 'Y' then 'X' else ' '
  89.         end as rcv_mail,
  90.         sg.signature
  91.         From CFMAST cf
  92.         left join cfauth au on  cf.custid = au.custid
  93.         left join allcode a1 on cf.country = a1.cdval
  94.                     and a1.cdtype = 'CF'
  95.                     and a1.cdname = 'COUNTRY'
  96.         left join allcode a2 on au.country = a2.cdval
  97.                     and a2.cdtype = 'CF'
  98.                     and a2.cdname = 'COUNTRY'
  99.         left join members b on cf.bankcode = b.mbcode
  100.         left join brgrp c on cf.bankcode = c.mbid
  101.                  and cf.citybank = c.brid
  102.         left join cfsign sg on cf.custid = sg.custid
  103.         left join cfcontact ct
  104.         on cf.custid = ct.custid
  105.         where cf.custodycd = p_custodycd
  106.         --and b.mbtype = 'Y';
  107.         and sg.type = 'SIG';
  108.         v_logsctx   := plog.setEndsection(v_logsctx, 'SUCCESS');
  109.       -- Open the first cursor
  110.       RETURN NEXT ret;                                                                              -- Return the cursor to the caller
  111.  
  112.      
  113.         v_logsctx := plog.init('prc_report_0015', 30, false, false, false);
  114.         v_logsbody:= 'prc_report_0015: '
  115.                     ||', pv_tlid = ' || pv_tlid
  116.                     ||', pv_tlname = ' || pv_tlname
  117.                     ||', pv_language = ' || pv_language;
  118.         v_logsctx   := plog.setbeginsection(v_logsctx, v_logsbody);
  119.         ref2 := 'ref2';
  120.       OPEN ref2 FOR
  121.      
  122.       select
  123.        substr(cf.custodycd,1,1) as ac01,
  124.        substr(cf.custodycd,2,1) as ac02,
  125.        substr(cf.custodycd,3,1) as ac03,
  126.        substr(cf.custodycd,4,1) as ac04,
  127.        substr(cf.custodycd,5,1) as ac05,
  128.        substr(cf.custodycd,6,1) as ac06,
  129.        substr(cf.custodycd,7,1) as ac07,
  130.        substr(cf.custodycd,8,1) as ac08,               
  131.        substr(cf.custodycd,9,1) as ac09,
  132.        substr(cf.custodycd,10,1) as ac010,
  133.        cf.custid,
  134.        cf.custodycd,
  135.        cf.fullname,
  136.        cf.custtype,
  137.        'X' as custp,
  138.        cf.sex,
  139.        'X' aS sextp,
  140.         to_char( cf.birthdate,fn_systemnums('systemnums.c_date_format')) birthdate ,
  141.         cf.idcode,
  142.         to_char( cf.iddate,fn_systemnums('systemnums.c_date_format'))  iddate,
  143.         to_char( cf.idexpdated,fn_systemnums('systemnums.c_date_format'))  idexpdated,
  144.         cf.idplace,
  145.         cf.tradingcode,
  146.         cf.taxno,
  147.         cf.regaddress,
  148.         cf.address,
  149.         ct.position,
  150.         a1.cdcontent as country,
  151.         cf.phone,
  152.         cf.mobile,
  153.         cf.email,
  154.         cf.fax,
  155.         cf.bankacc,
  156.         cf.bankcode,
  157.         c.brname as citybank,
  158.         cf.fullname as bankacname,
  159.         b.mbname as bankname,
  160.         au.custname as au_custname,
  161.         au.position as au_position,
  162.         au.sex as au_sex,
  163.         'X' aS sex_au_tp,
  164.         to_char( au.birthdate,fn_systemnums('systemnums.c_date_format')) as au_birthdate ,
  165.         au.idcode as au_idcode,
  166.         to_char( au.iddate,fn_systemnums('systemnums.c_date_format'))  as au_iddate,
  167.         au.idplace as au_idplace,
  168.         au.regaddress as au_regaddress,
  169.         au.address as au_address,
  170.         a2.cdcontent as au_country,
  171.         au.mobile as au_mobile,
  172.         au.email as au_email,
  173.         au.fax as au_fax,
  174.         case when au.auth_all = 'Y' then 'X' else ' '
  175.         end auth_all,
  176.         case when au.auth_order = 'Y'
  177.         and au.auth_all = 'Y' then ' '
  178.         when au.auth_order = 'Y'
  179.         and au.auth_all <> 'Y' then 'X'
  180.         else ' '
  181.         end auth_order,
  182.         case when cf.rcv_email = 'Y' then 'X' else ' '
  183.         end as rcv_email,
  184.         case when cf.rcv_sms = 'Y' then 'X' else ' '
  185.         end as rcv_sms  ,
  186.         case when cf.rcv_mail = 'Y' then 'X' else ' '
  187.         end as rcv_mail,
  188.         sg.signature
  189.         From CFMAST cf
  190.         left join cfauth au on  cf.custid = au.custid
  191.         left join allcode a1 on cf.country = a1.cdval
  192.                     and a1.cdtype = 'CF'
  193.                     and a1.cdname = 'COUNTRY'
  194.         left join allcode a2 on au.country = a2.cdval
  195.                     and a2.cdtype = 'CF'
  196.                     and a2.cdname = 'COUNTRY'
  197.         left join members b on cf.bankcode = b.mbcode
  198.         left join brgrp c on cf.bankcode = c.mbid
  199.                  and cf.citybank = c.brid
  200.         left join cfsign sg on cf.custid = sg.custid
  201.         left join cfcontact ct
  202.         on cf.custid = ct.custid
  203.         where cf.custodycd = p_custodycd
  204.         --and b.mbtype = 'Y';
  205.         and sg.type = 'SIG';   -- Open the second cursor
  206.         v_logsctx   := plog.setEndsection(v_logsctx, 'SUCCESS');
  207.       RETURN NEXT ref2;                                                                              -- Return the cursor to the caller
  208.     Exception
  209.     when others then
  210.         GET STACKED DIAGNOSTICS v_exception = PG_EXCEPTION_CONTEXT;
  211.         v_logsbody := plog.setEndsection(v_logsctx,'prc_report_0015.Error:' || SQLERRM ||', SQLSTATE:'|| SQLSTATE
  212.                                     ||', PG_EXCEPTION_CONTEXT:' || v_exception);
  213.         OPEN ret FOR
  214.             select 'Data not found' p_return;
  215.     RETURN ;
  216.     END;
  217.     $function$
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!