一个Oracle 报表的PL/SQL代码

PL/SQL数据包：

create or replace package CUX_GL_REP_LISTPKG is

  -- Author  : LIJINQIAN
  -- Created : 2005-7-12 9:50:00
  -- Purpose : 总帐科目列表
 
    -- 总帐汇总
  Procedure cuxgllistgath(
    errbuf      Out Varchar2,
    retcode     Out Varchar2,
    p_start_date    Varchar2,
    p_end_date      Varchar2,
    p_set_of_gl_id  Number,
    p_org_id        Number,
    p_report_type   Number);   
   
    --总帐明细
  Procedure cuxgllistgathmx(
    errbuf      Out Varchar2,
    retcode     Out Varchar2,
    p_start_date    Varchar2,
    p_end_date      Varchar2,
    Chart_of_Accounts_ID number,
    p_Account_From  varchar2,
    p_Account_to    varchar2,  
    p_to_gl         varchar2,
    p_je_source     Varchar2, 
    p_set_of_gl_id  Number,
    p_org_id        Number,
    p_report_type   Number);
   
end CUX_GL_REP_LISTPKG;

包体：

create or replace package body CUX_GL_REP_LISTPKG is

  --=======================================
  --   总帐科目汇总
  --=======================================
  Procedure cuxgllistgath(
    errbuf      Out Varchar2,
    retcode     Out Varchar2,
    p_start_date    Varchar2,
    p_end_date      Varchar2,
    p_set_of_gl_id  Number,
    p_org_id        Number,
    p_report_type   Number
  ) Is
    l_show          Varchar2(2000);
    l_org_name      Varchar2(2000);
   
    p_sign          Varchar2(200);
    p_account_type  Number:=0;
   
    l_start_dr    number:=0;
    l_start_cr    number:=0;
    l_now_dr      number:=0;
    l_now_cr      number:=0;
    l_year_dr     number:=0;
    l_year_cr     number:=0;
   
    l_qm_dr       Number:=0;
    l_qm_cr       Number:=0;
   
    l_start_dr_t    number:=0;
    l_start_cr_t    number:=0;
    l_now_dr_t      number:=0;
    l_now_cr_t      number:=0;
    l_year_dr_t     number:=0;
    l_year_cr_t     number:=0;
    l_end_dr_t      Number:=0;
    l_end_cr_t      Number:=0;
   
    l_total         Number:=0;
   
    Cursor cr1 Is
       select Distinct 
           gccv.Code_Combination_Id accid,
           gccv.segment1||'.'||'T'||'.'||SUBSTR(gccv.segment3,1,4)||'.'||'T'||'.'||'T'||'.'||'T'||'.'||'T'||'.'||'T' A2,
           fvl1.Description||'.'||fvl2.Description||'.'||fvl3.Description||'.'||fvl4.Description||'.'||fvl5.Description||'.'||fvl6.Description||'.'||fvl7.Description||'.'||fvl8.Description A3
       from
           gl_balances gl_ba                    -- 总帐表
           ,Gl_Code_Combinations gccv
           ,fnd_flex_values_vl fvl1
           ,fnd_flex_values_vl fvl2
           ,fnd_flex_values_vl fvl3
           ,fnd_flex_values_vl fvl4
           ,fnd_flex_values_vl fvl5
           ,fnd_flex_values_vl fvl6
           ,fnd_flex_values_vl fvl7
           ,fnd_flex_values_vl fvl8
       where gl_ba.code_combination_id=gccv.CODE_COMBINATION_ID
         And (fvl1.flex_value=gccv.segment1 And fvl1.FLEX_VALUE_SET_ID='1007720')              -- 公司段
         And (fvl2.flex_value='T' And fvl2.FLEX_VALUE_SET_ID='1007721')                        -- 部门段
         And (fvl3.flex_value=substr(gccv.segment3,1,4) And fvl3.FLEX_VALUE_SET_ID='1007722')  -- 科目段
         And (fvl4.flex_value='T' And fvl4.FLEX_VALUE_SET_ID='1007723')                        -- 子科目段
         And (fvl5.flex_value='T' And fvl5.FLEX_VALUE_SET_ID='1007724')                        -- 公司间段
         And (fvl6.flex_value='T' And fvl6.FLEX_VALUE_SET_ID='1007725')                        -- 产品段
         And (fvl7.flex_value='T' And fvl7.FLEX_VALUE_SET_ID='1007726')                        -- 项目段
         And (fvl8.flex_value='T' And fvl8.FLEX_VALUE_SET_ID='1007727')                        -- 备用段
         And to_number(substr(gl_ba.Period_Name,4,4)||substr(gl_ba.Period_Name,1,2)) between to_number(substr(p_start_date,4,4)||substr(p_start_date,1,2)) and to_number(substr(p_end_date,4,4)||substr(p_end_date,1,2))
         And gl_ba.Actual_Flag='A'                  --  取实际数
         And gccv.Summary_Flag='Y'                  --  取汇总科目
         And gl_ba.Template_Id='75'                 --  一级科目汇总
         And gccv.segment1=( Select Distinct
                                  gcc.Segment1
                             From  gl_je_lines lines
                                  ,gl_je_headers h
                                  ,gl_je_batches b
                                  ,Gl_Code_Combinations gcc
                             Where b.je_batch_id = h.je_batch_id
                               And h.je_header_id = lines.je_header_id
                               And lines.Code_Combination_Id=gcc.Code_Combination_Id
                               And b.org_id=p_org_id)
       Order By gccv.segment1||'.'||'T'||'.'||SUBSTR(gccv.segment3,1,4)||'.'||'T'||'.'||'T'||'.'||'T'||'.'||'T'||'.'||'T';   
  Begin
      -- 取得单位名称
      select a.name into l_org_name from hr_organization_units a
      where a.organization_id=p_org_id; 
     
      -- 输出报表头
      l_SHOW := lpad('客户化 总帐科目余额列表', 100, ' ');
      cux_my_public_pkg.OUT(l_SHOW);
      cux_my_public_pkg.OUT(' '); 
   
      l_SHOW := rpad('会计日期', 10, ' ') || rpad(p_start_date, 10, ' ')|| rpad('至'|| p_end_date, 30, ' ');
      cux_my_public_pkg.OUT(l_SHOW);
      cux_my_public_pkg.OUT(' ');
   
      --帐户名称
      l_SHOW := rpad('单位名称', 20, ' ') || rpad(l_org_name, 100, ' ');
      cux_my_public_pkg.OUT(l_SHOW);
      cux_my_public_pkg.Out(' ');
      
      l_SHOW := rpad('来源', 20, ' ') || rpad('科目', 30, ' ') ||rpad('科目说明', 100, ' ') ||
      rpad('过帐状态', 10, ' ') ||
      rpad('期初余额借方', 20, ' ') || rpad('期初余额贷方', 20, ' ') ||
      rpad('本期发生借方', 20, ' ') || rpad('本期发生贷方', 20, ' ') ||
      rpad('本年累计借方', 20, ' ') || rpad('本年累计贷方', 20, ' ') ||
      rpad('期末余额借方', 20, ' ') || rpad('期末余额贷方', 20, ' ');
      cux_my_public_pkg.OUT(l_SHOW);
     
      l_SHOW := lpad(' ', 20, '-') || lpad(' ', 30, '-') ||
              lpad(' ', 100, '-') ||
              lpad(' ', 10, '-') ||
              lpad(' ', 20, '-') || lpad(' ', 20, '-') ||
              lpad(' ', 20, '-') || lpad(' ', 20, '-') ||
              lpad(' ', 20, '-') || lpad(' ', 20, '-') ||
              lpad(' ', 20, '-') || lpad(' ', 20, '-');
      cux_my_public_pkg.OUT(l_SHOW);
  
      -- 汇总科目
      If p_report_type=1 Then
        
         -- 汇总init
         l_start_dr_t:=0;
         l_start_cr_t:=0