oracle sqr_久悬户转营业处收入示例代码

  • wisdomone1

    2011-12-22 11:29:42
  • Linux操作系统
  • 原创
　几经调试，为记录近期对于SQR相应工作的学习，完整存放如题代码，供后期参考：
  
#define addtlsetup
#include 'Rptstub.lib'
#include '{libpath}tkit_batchstartup.inc'
#include '{libpath}tkit_rptheader.inc'
#include '{libpath}tkit_queappl.inc'
#include '{libpath}tkit_vars_opts.inc'
#include '{libpath}tkit_Sqrfunc.inc'
!----------------------------------------------------------------------------------------------------------
!开发日期：2011-12-12
!开发人员：***
!功能描述：二期【久悬户转营业外收入清单，调用存储过程和产生报表
!久悬户转营业外收入清单SQT：RDP_DORMTOOUTINCOME.SQT
!
!修改日期：2011-12-20 星期二
!          2011-12-21 星期三
!修改人员：***
!
!----------------------------------------------------------------------------------------------------------
!报表显示布局的初始化
begin-setup
 Declare-layout batch-land
   rientation=landscape
   left-margin=0
   top-margin=0
   max-lines=60
   max-columns=180
 end-declare
end-setup

Begin-Heading 5 name = myHeading
  print $filename (2,35)  BOLD  center
  Let $Global-HaveIWrittenAPage = 'Y'
  print '─' (+1,2,84) fill
  Print '提交日期:' (+1,118)
  Print &FmtdEffDate (0, 0)
  print '─' (+1,2,84) fill
End-Heading

!存储过程执行出错报表头 2011-12-20 ***
begin-heading 5 name=procerrheading
    print '执行存储过程出错:' (+1,10)
        print '错误号:' (+1,10)
        print #errNbr (,+1)
        print '批处理错误信息:' (+1,10)
        print $errmsg (,+1)
        print 'oracle错误信息:' (+1,10)
        print $oramsg (,+1)
        Let $QueApplChkpt =  $oramsg
        Let $QueApplRc = #errNbr
        Do Final-QueAppl-Update
end-heading

!临久悬户转营业处收入1个月提前通知
begin-procedure aheadnotice
   !以银行分支机构分组
        begin-select distinct
p.branchorgnbr &a1 () ON-BREAK PRINT=NEVER AFTER=aheadnp($a1) save = $a1
            !from OSIBANK.acctinactprocess p where p.state='IACT' and p.totindate is null and trunc(to_date($eff,'yyyymmdd'))-trunc(p.toinactdate)=1825
            !from OSIBANK.acctinactprocess p where p.state='IACT' and p.totindate is null and p.acctnbr=201081101401400918 !用于测试
            from OSIBANK.acctinactprocess p
            where p.state='IACT'
                  and p.totindate is null
                  and months_between($eff,p.toinactdate)=12*(select to_number(bankoptionvalue)
                                                             from bankoption
                                                             where bankoptioncd='ITOP')
                                                          -
                                                            (select to_number(bankoptionvalue)
                                                             from bankoption
                                                             where bankoptioncd='ITAT')
        end-select
end-procedure

begin-procedure aheadnp($a1)
    do Get-The-PostDate($dt)
        move $dt to $dt 'YYYYMMDD'
        do GET-APPL-NAME($filename)
    let $out=$a1||'_'||$filename||'_'||$_quenbr||'_'||$_ApplNbr||'_'||'提前1个月通知'||'.LIS'
    NEW-report $out
    do GETBANKNAME1($a1)
    use-procedure before-page = aheadnoticestart !暂注解此行，不用报表头了
        do selectaheaddifforg($a1) !只打印不同银行分支机构的报表信息
end-procedure

!报表表头的初始化
begin-procedure outsideheading
  print $filename (0) center
  print '─' (+1,2,84) fill
  position (+1)
  let $bankorg_name = '机构名称:'|| $bankorgname
  print $bankorg_name (,2)
  Print '提交日期:' (,118)
  Print &FmtdEffDate (0, 0)
  print '─' (+1,2,84) fill
  !介质号，账号，户名，产品大类，产品小类，状态 转营业外输入日期 ，金额
    position (+1)
    print '介质号'  (,2,19)
    print '账号'  (,23,20)
    print '户名'  (,45)
    print '产品大类'  (,90,10)
    print '产品小类'  (,100,10)
    print '状态'  (,120,8)
    print '转营业外收入日期'  (,130,10)
    print '金额'  (,142,30)
    print '─' (+1,2,84) fill
end-procedure

!提前通知报表表头的初始化
begin-procedure aheadnoticestart
  print $filename (0) center
  print '─' (+1,2,84) fill
  position (+1)
  let $bankorg_name = '机构名称:'|| $bankorgname1
  print $bankorg_name (,2)
  Print '提交日期:' (,118)
  Print &FmtdEffDate (0, 0)
  print '─' (+1,2,84) fill
  !介质号，账号，户名，产品大类，产品小类，状态 转营业外输入日期 ，金额
    position (+1)
    print '介质号'  (,2,19)
    print '账号'  (,23,20)
    print '户名'  (,45)
    print '产品大类'  (,90,10)
    print '产品小类'  (,100,10)
    print '转营业外收入剩余天数'  (,120,30)
    print '金额'  (,160,30)
    print '─' (+1,2,84) fill
end-procedure

begin-procedure osi-main
 begin-sql
  begin
        OSIBANK.proc_batchdormaccttooutincome($QueNbr,$ApplNbr,$QuesubNbr,$cab,$eff,#errNbr,$errmsg,$oramsg);;
  end;;
 end-sql
 
 if #errnbr!=0
      !执行存储过程出错，产生错误文件.lis
      alter-report heading=procerrheading
      let $out='err'||'_'||$QueNbr||'_'||$ApplNbr||'.LIS'
      new-report $out
      print '执行存储过程OSIBANK.proc_batchdormaccttooutincome出错' (+1,0) center
 else
         !调用提前1个月通知转营业处收入之过程
         begin-select
count(*) &rownums1    
            from OSIBANK.acctinactprocess p
            where p.state='IACT'
                  and p.totindate is null
                  and months_between($eff,p.toinactdate)=12*(select to_number(bankoptionvalue)
                                                             from bankoption
                                                             where bankoptioncd='ITOP')
                                                          -
                                                            (select to_number(bankoptionvalue)
                                                             from bankoption
                                                             where bankoptioncd='ITAT')
      end-select
     
         if &rownums1!=0
             do aheadnotice !暂注解，此代码未起作用
         end-if
 
      do submain
 end-if  
 end-procedure

!2011-12-20 星期二　***　修改过程名自osi-main为submain,供osi-main调用
begin-procedure submain
     Move 'In submain' To $Global-ProcName
     !---------------- 获取定义报表输出路径需要的数据---开始---------------------
    do Get-The-PostDate($dt)
    move $dt to $dt 'YYYYMMDD'
    do GET-APPL-NAME($filename)
    Do Get-Bank-Option('RPTT',$Len)
     !----------------  获取定义报表输出路径需要的数据---结束---------------------

    do selectdiffbankorg  
    Let $QueApplChkpt = 'Report Complete'
    Let $QueApplRc = 0
    Do Final-QueAppl-Update    

    move 'Done submain' To $Global-ProcName    
end-procedure

begin-procedure selectdiffbankorg
    begin-select
count(*) &rownums  
        from osibank.rpt_acctinact_process p where p.state='TINC'
       end-select
    
     if &rownums = 0
         alter-report heading = myHeading !改变页眉
       let $out = $Len || $dt || '\' || $QueNbr || '\' || $filename || '.LIS'
       NEW-REPORT $out
       print '没有符合条件的数据' (+1,) center
     else
        !以银行分支机构分组
        begin-select distinct
p.branchorgnbr &a () ON-BREAK PRINT=NEVER AFTER=NP($a) save = $a
            from OSIBANK.rpt_acctinact_process p where p.state='TINC'
        end-select
    end-if
end-procedure

!文件输出路径、文件名设定
BEGIN-procedure NP($a)
  do GETBANKNAME($a)
  let $out=$_Len||$_dt||'\'||$a||'_'||$_filename|| '.LIS'
    use-procedure before-page = outsideheading
    NEW-REPORT $out
    do selectdifforg($a) !只打印不同银行分支机构的报表信息
end-procedure        

BEGIN-PROCEDURE GETBANKNAME($A)
    begin-select
o.orgname &bankorgname
       move &bankorgname to $_bankorgname
    FROM ORG o,branch b WHERE  o.ORGNBR = b.orgnbr and  b.orgidnbr=$A
    end-select
END-PROCEDURE

!参考getbankname过程编写，用于提取提前1个月通知的银行机构
BEGIN-PROCEDURE GETBANKNAME1($A1)
    begin-select
o.orgname &bankorgname1
       move &bankorgname1 to $_bankorgname1
    FROM ORG o,branch b WHERE  o.ORGNBR = b.orgnbr and  b.orgidnbr=$A1
    end-select
END-PROCEDURE

begin-procedure selectdifforg($a)

begin-select
   position (+1)
p.mediumid &mediumid
   move &mediumid to  $mediumid
   print $mediumid  (,2,19)
to_char(p.acctnbr)  &acctnbr
   move  &acctnbr to  $acctnbr
   print $acctnbr (,23,20)
p.orgname  &orgname
   move  &orgname to  $orgname
   print $orgname (,45)

p.mjaccttypcd &mjaccttypcd
   move  &mjaccttypcd  to $mjaccttypcd
   print $mjaccttypcd (,90,10)

p.mjmiaccttypcd &mjmiaccttypcd  
   move &mjmiaccttypcd  to $mjmiaccttypcd
   print $mjmiaccttypcd (,100,10)

p.state &state
   move &state  to $state
   print $state (,120,8)

p.totindate  &totindate
   move &totindate  to $totindate 'YYYY-MM-DD'
   print $totindate (,130,10)

p.accttotalamt  &accttotalamt
   move   &accttotalamt  to $accttotalamt 88,888,888,888,888.88
   print $accttotalamt  (,142,30)

   let #count=#count+1
   let #accttotalamt=#accttotalamt + &accttotalamt
   from osibank.rpt_acctinact_process p
   where p.state='TINC' and p.branchorgnbr=$a

end-select
 
     print '─' (+1,2,84) fill
  move #count to $count 999,999,999
  let $co ='总记录数：' || $count
  print $co (+1,2)
  move 0 to #count
 
  move #accttotalamt to $accttotalamt 999,999,999,999.99
  let $totalamt ='总金额：' || $accttotalamt
  print $totalamt (,100)

end-procedure

!提前通知打印提前1个月通知 暂把过程名修改为selectaheaddifforg_modify，还原
begin-procedure selectaheaddifforg($a1)
begin-select
   position (+1)
p.mediumid &mediumid
   move &mediumid to  $mediumid
   print $mediumid  (,2,19)
to_char(p.acctnbr)  &acctnbr
   move  &acctnbr to  $acctnbr
   print $acctnbr (,23,20)
p.orgname  &orgname
   move  &orgname to  $orgname
   print $orgname (,45)

p.mjaccttypcd &mjaccttypcd
   move  &mjaccttypcd  to $mjaccttypcd
   print $mjaccttypcd (,90,10)

p.mjmiaccttypcd &mjmiaccttypcd  
   move &mjmiaccttypcd  to $mjmiaccttypcd
   print $mjmiaccttypcd (,100,10)

30 &aheaddays
   move &aheaddays  to $aheaddays  8,888
   print $aheaddays (,120,8)
   
p.accttotalamt  &accttotalamt
   move   &accttotalamt  to $accttotalamt 88,888,888,888,888.88
   print $accttotalamt  (,160,30)

   let #count=#count+1
   let #accttotalamt=#accttotalamt + &accttotalamt
   
   from OSIBANK.acctinactprocess p
   where p.state='IACT'
                  and p.totindate is null
                  and months_between($eff,p.toinactdate)=12*(select to_number(bankoptionvalue)
                                                             from bankoption
                                                             where bankoptioncd='ITOP')
                                                          -
                                                            (select to_number(bankoptionvalue)
                                                             from bankoption
                                                             where bankoptioncd='ITAT')
end-select
 
  print '─' (+1,2,84) fill
  move #count to $count 999,999,999
  let $co ='总记录数：' || $count
  print $co (+1,2)
  move 0 to #count
 
  move #accttotalamt to $accttotalamt 999,999,999,999.99
  let $totalamt ='总金额：' || $accttotalamt
  print $totalamt (,100)
  move 0 to $totalamt
end-procedure

begin-footing 3
  print '─' (,2,84) fill
  !page-number ()  (+1,126) '当前页为第' '页，'
  page-number (+1,126)  '当前页为第' '页，'
  last-page () '共' '页。'
  print '打印时间:' (,+1)
 
  let $printDate = datenow()
  move $printDate to $printDate 'YYYY-MM-DD'
  print $printDate (,)
end-footing

begin-procedure OSI-Startup   
End-procedure




请使用浏览器的分享功能分享到微信等