　幾經除錯，為記錄近期對於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

oracle sqr_久懸戶轉營業處收入示例程式碼

相關文章