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;
         l_now_dr_t:=0;
         l_now_cr_t:=0;
         l_year_dr_t:=0;
         l_year_cr_t:=0;
         l_end_dr_t:=0;
         l_end_cr_t:=0;
        
         For i In cr1 Loop
        
            -- 帐户类型
            --  A  (资产) 1
            --  E  (费用类) 4
            SELECT Count(Distinct gcc.account_type)
                INTO p_account_type
             FROM gl_code_combinations gcc
            WHERE gcc.Code_Combination_Id=i.accid
              And (gcc.Segment3 Like '1%' Or gcc.Segment3 Like '4%');
           
            IF p_account_type<>0 THEN
                p_sign := 1;
            ELSE
                p_sign := -1;
            END IF;
     
            -- 期初余额YTD
            SELECT p_sign*nvl(sum((gc.begin_balance_dr-gc.begin_balance_cr)+(gc.period_net_dr-gc.period_net_cr)),0)
                into l_total
            FROM GL_BALANCES gc
            WHERE gc.Set_Of_Books_Id = p_set_of_gl_id
              AND gc.code_combination_id = i.accid
              AND gc.period_name = substr(to_char(add_months(to_date(substr(p_start_date,4,4)||'-'||substr(p_start_date,1,2)||'-01','yyyy-mm-dd'),-1),'YYYY-MM-DD'),6,2)||'-'||substr(to_char(add_months(to_date(substr(p_start_date,4,4)||'-'||substr(p_start_date,1,2)||'-01','yyyy-mm-dd'),-1),'YYYY-MM-DD'),1,4)
              AND gc.actual_flag = 'A';
             
            IF p_account_type<>0 THEN
               l_start_dr:=l_total;
               l_start_cr:=0;
            Else
               l_start_dr:=0;
               l_start_cr:=l_total;
            end if;
           
            -- 本期发生额
            Select nvl(sum(gv.Line_Entered_Dr),0),
                   nvl(sum(gv.Line_Entered_cr),0)
                 Into l_now_dr,
                      l_now_cr
            From Gl_Je_Journal_Lines_v gv,
                 Gl_Account_Hierarchies gah
            Where to_number(substr(gv.Period_Name,4,4)||substr(gv.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 gv.Actual_Flag='A'
              And GV.Batch_Status='P'
              And gv.Line_Code_Combination_Id=gah.Detail_Code_Combination_Id
              And gah.Summary_Code_Combination_Id=i.accid;
           
            -- 年发生额
            Select nvl(sum(gv.Line_Entered_Dr),0),
                   nvl(sum(gv.Line_Entered_cr),0)
                 Into l_year_dr,
                      l_year_cr
            From Gl_Je_Journal_Lines_v gv,
                 Gl_Account_Hierarchies gah
            Where gv.Period_Year=substr(p_start_date,4,4)
              And gv.Actual_Flag='A'
              And GV.Batch_Status='P'
              And gv.Line_Code_Combination_Id=gah.Detail_Code_Combination_Id
              And gah.Summary_Code_Combination_Id=i.accid;

            -- 期末数
            IF p_account_type<>0 THEN
               l_qm_dr:=l_start_dr+l_now_dr-l_now_cr;
               l_qm_cr:=0;
            Else
               l_qm_dr:=0;
               l_qm_cr:=l_start_cr+l_now_cr-l_now_dr;
            End If;              
             
            l_SHOW := rpad('汇总', 20, ' ') || rpad(i.A2, 30, ' ') ||
                      rpad(i.A3, 100, ' ') ||
                      rpad('汇总', 10, ' ') ||
                      rpad(to_char(l_start_dr,'999,999,999,990.00'), 20, ' ') || rpad(to_char(l_start_cr,'999,999,999,990.00'), 20, ' ') ||
                      rpad(to_char(l_now_dr,'999,999,999,990.00'), 20, ' ') || rpad(to_char(l_now_cr,'999,999,999,990.00'), 20, ' ') ||
                      rpad(to_char(l_year_dr,'999,999,999,990.00'), 20, ' ') || rpad(to_char(l_year_cr,'999,999,999,990.00'), 20, ' ') ||
                      rpad(to_char(l_qm_dr,'999,999,999,990.00'), 20, ' ') || rpad(to_char(l_qm_cr,'999,999,999,990.00'), 20, ' ');
            cux_my_public_pkg.OUT(l_SHOW);
     
            -- 汇总
            l_start_dr_t:=l_start_dr_t+l_start_dr;
            l_start_cr_t:=l_start_cr_t+l_start_cr;
           
            l_now_dr_t:=l_now_dr_t+l_now_dr;
            l_now_cr_t:=l_now_cr_t+l_now_cr;
           
            l_year_dr_t:=l_year_dr_t+l_year_dr;
            l_year_cr_t:=l_year_cr_t+l_year_cr;
            l_end_dr_t:=l_end_dr_t+l_qm_dr;
            l_end_cr_t:=l_end_cr_t+l_qm_cr;
     
         END LOOP;
         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);
         
         l_SHOW := rpad('合计', 20, ' ') || rpad(' ', 30, ' ') ||
                    rpad(' ', 100, ' ') ||
                    rpad(' ', 10, ' ') ||
                    rpad(to_char(l_start_dr_t,'999,999,999,990.00'), 20, ' ') || rpad(to_char(l_start_cr_t,'999,999,999,990.00'), 20, ' ') ||
                    rpad(to_char(l_now_dr_t,'999,999,999,990.00'), 20, ' ') || rpad(to_char(l_now_cr_t,'999,999,999,990.00'), 20, ' ') ||
                    rpad(to_char(l_year_dr_t,'999,999,999,990.00'), 20, ' ') || rpad(to_char(l_year_cr_t,'999,999,999,990.00'), 20, ' ') ||
                    rpad(to_char(l_end_dr_t,'999,999,999,990.00'), 20, ' ') || rpad(to_char(l_end_cr_t,'999,999,999,990.00'), 20, ' ');
         cux_my_public_pkg.OUT(l_SHOW);
      End If;
  End cuxgllistgath;
 
  --==================================
  --   总帐明细
  --==================================
  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
  ) Is
    l_show          Varchar2(2000);
    l_org_name      Varchar2(2000);
   
    p_sign          Varchar2(200);
    p_account_type  Number:=0;
   
    -- 帐户的范围
    l_acct_desc1    Varchar2(200);
    l_acct_desc2    Varchar2(200);
    l_acct_desc     Varchar2(2000);
    l_acct_name     Varchar2(2000);
   
    -- 总帐值
    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
                 decode(gjh.Je_Source,'Payables','应付款','Receivables','应收款','Spreadsheet','电子表格','Manual','人工',gjh.Je_Source) A3,
                 gjl.Code_Combination_Id accid,
                 gccb.Segment1||'.'||gccb.Segment2||'.'||gccb.Segment3||'.'||gccb.Segment4||'.'||gccb.Segment5||'.'||gccb.Segment6||'.'||gccb.Segment7||'.'||gccb.Segment8 A1,
                 ffvl1.Description||'.'||ffvl2.Description||'.'||ffvl3.Description||'.'||ffvl4.Description||'.'||ffvl5.Description||'.'||ffvl6.Description||'.'||ffvl7.Description||'.'||ffvl8.Description A2,
                 decode(gjh.Status,'P','已过账','未过账') A4,
                 Sum(nvl(gjl.Entered_Dr,0)) A5,
                 Sum(nvl(gjl.Entered_Cr,0)) A6
            From gl_je_batches gjb 
                ,gl_je_headers gjh
                ,gl_je_lines gjl
                ,Gl_Account_Hierarchies gah
                ,Gl_Code_Combinations gccb
                ,fnd_flex_values_vl ffvl1
                ,fnd_flex_values_vl ffvl2
                ,fnd_flex_values_vl ffvl3
                ,fnd_flex_values_vl ffvl4
                ,fnd_flex_values_vl ffvl5
                ,fnd_flex_values_vl ffvl6
                ,fnd_flex_values_vl ffvl7
                ,fnd_flex_values_vl ffvl8
            Where to_number(substr(gjh.Period_Name,4,4)||substr(gjh.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 gjh.je_header_id = gjl.je_header_id
              and gjl.code_combination_id = gccb.code_combination_id
              and gjh.je_batch_id = gjb.je_batch_id
              and gjb.org_id = p_org_id
              And gjh.Actual_Flag='A'
              And gjl.Code_Combination_Id=gah.Detail_Code_Combination_Id
              And gah.Template_Id=75
              And (ffvl1.flex_value=gccb.segment1 And ffvl1.FLEX_VALUE_SET_ID='1007720')  -- 公司段
              And (ffvl2.flex_value=gccb.segment2 And ffvl2.FLEX_VALUE_SET_ID='1007721')  -- 部门段
              And (ffvl3.flex_value=gccb.segment3 And ffvl3.FLEX_VALUE_SET_ID='1007722')  -- 科目段
              And (ffvl4.flex_value=gccb.segment4 And ffvl4.FLEX_VALUE_SET_ID='1007723')  -- 子科目段
              And (ffvl5.flex_value=gccb.segment5 And ffvl5.FLEX_VALUE_SET_ID='1007724')  -- 公司间段
              And (ffvl6.flex_value=gccb.segment6 And ffvl6.FLEX_VALUE_SET_ID='1007725')  -- 产品段
              And (ffvl7.flex_value=gccb.segment7 And ffvl7.FLEX_VALUE_SET_ID='1007726')  -- 项目段
              And (ffvl8.flex_value=gccb.segment8 And ffvl8.FLEX_VALUE_SET_ID='1007727') 
              And gjh.Je_Source = decode(p_je_source,'',gjh.Je_Source,Null,gjh.Je_Source,p_je_source)
              And decode(gjb.status,'P','已过账','未过账') Like decode(p_to_gl,'Y','已过账','N','未过账','%过账')
              -- 科目查询
              and gccb.segment1 between nvl(substr(p_Account_From,1,instr(p_Account_From,'.',1,1)-1),gccb.segment1)
                                   and nvl(substr(p_Account_to,1,instr(p_Account_to,'.',1,1)-1),gccb.segment1)
              and gccb.segment2 between nvl(substr(p_Account_From,instr(p_Account_From,'.',1,1)+1,((instr(p_Account_From,'.',1,2))- (instr(p_Account_From,'.',1,1)+1))),gccb.segment2)
                                   and nvl(substr(p_Account_to,instr(p_Account_to,'.',1,1)+1,((instr(p_Account_to,'.',1,2))- (instr(p_Account_to,'.',1,1)+1))),gccb.segment2)
              and gccb.segment3 between nvl(substr(p_Account_From,instr(p_Account_From,'.',1,2)+1,((instr(p_Account_From,'.',1,3))- (instr(p_Account_From,'.',1,2)+1))),gccb.segment3)
                                  and nvl(substr(p_Account_to,instr(p_Account_to,'.',1,2)+1,((instr(p_Account_to,'.',1,3))- (instr(p_Account_to,'.',1,2)+1))),gccb.segment3)
              and gccb.segment4 between nvl(substr(p_Account_From,instr(p_Account_From,'.',1,3)+1,((instr(p_Account_From,'.',1,4))- (instr(p_Account_From,'.',1,3)+1))),gccb.segment4)
                                   and nvl(substr(p_Account_to,instr(p_Account_to,'.',1,3)+1,((instr(p_Account_to,'.',1,4))- (instr(p_Account_to,'.',1,3)+1))),gccb.segment4)
              and gccb.segment5 between nvl(substr(p_Account_From,instr(p_Account_From,'.',1,4)+1,((instr(p_Account_From,'.',1,5))- (instr(p_Account_From,'.',1,4)+1))),gccb.segment5)
                                   and nvl(substr(p_Account_to,instr(p_Account_to,'.',1,4)+1,((instr(p_Account_to,'.',1,5))- (instr(p_Account_to,'.',1,4)+1))),gccb.segment5)
              and gccb.segment6 between nvl(substr(p_Account_From,instr(p_Account_From,'.',1,5)+1,((instr(p_Account_From,'.',1,6))- (instr(p_Account_From,'.',1,5)+1))),gccb.segment6)
                                   and nvl(substr(p_Account_to,instr(p_Account_to,'.',1,5)+1,((instr(p_Account_to,'.',1,6))- (instr(p_Account_to,'.',1,5)+1))),gccb.segment6)
              and gccb.segment7 between nvl(substr(p_Account_From,instr(p_Account_From,'.',1,6)+1,((instr(p_Account_From,'.',1,7))- (instr(p_Account_From,'.',1,6)+1))),gccb.segment7)
                                   and nvl(substr(p_Account_to,instr(p_Account_to,'.',1,6)+1,((instr(p_Account_to,'.',1,7))- (instr(p_Account_to,'.',1,6)+1))),gccb.segment7)
              and gccb.segment8 between nvl(substr(p_Account_From,instr(p_Account_From,'.',1,7)+1,10),gccb.segment8)
                                   and nvl(substr(p_Account_to,instr(p_Account_to,'.',1,7)+1,10),gccb.segment8)
              And gah.Summary_Code_Combination_Id In (select  
                                                       gccv.Code_Combination_Id accid
                                                   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)
                                                   )
            Group By  gjl.Code_Combination_Id,
                      gccb.Segment1||'.'||gccb.Segment2||'.'||gccb.Segment3||'.'||gccb.Segment4||'.'||gccb.Segment5||'.'||gccb.Segment6||'.'||gccb.Segment7||'.'||gccb.Segment8,
                      ffvl1.Description||'.'||ffvl2.Description||'.'||ffvl3.Description||'.'||ffvl4.Description||'.'||ffvl5.Description||'.'||ffvl6.Description||'.'||ffvl7.Description||'.'||ffvl8.Description,
                      gjh.Je_Source,
                      gjh.Status
            Order By decode(gjh.Je_Source,'Payables','应付款','Receivables','应收款','Spreadsheet','电子表格','Manual','人工',gjh.Je_Source),
                     gccb.Segment1||'.'||gccb.Segment2||'.'||gccb.Segment3||'.'||gccb.Segment4||'.'||gccb.Segment5||'.'||gccb.Segment6||'.'||gccb.Segment7||'.'||gccb.Segment8;
  Begin
      -- 取得单位名称
      select a.name into l_org_name from hr_organization_units a
      where a.organization_id=p_org_id;
     
      -- 取得查询的科目   l_acct_desc1
      --       说明范围   l_acct_desc2  
      If p_Account_From Is Not Null Then
           Select
                 fvl1.Description||'.'||fvl2.Description||'.'||fvl3.Description||'.'||fvl4.Description||'.'||fvl5.Description||'.'||fvl6.Description||'.'||fvl7.Description||'.'||fvl8.Description A3
             Into l_acct_desc1
            From gl_code_combinations_kfv gcck
                 ,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 gcck.Concatenated_Segments=p_Account_From
             And (fvl1.flex_value=gcck.segment1 And fvl1.FLEX_VALUE_SET_ID='1007720')                        -- 公司段
             And (fvl2.flex_value=gcck.segment2 And fvl2.FLEX_VALUE_SET_ID='1007721')                        -- 部门段
             And (fvl3.flex_value=gcck.segment3 And fvl3.FLEX_VALUE_SET_ID='1007722')                        -- 科目段
             And (fvl4.flex_value=gcck.segment4 And fvl4.FLEX_VALUE_SET_ID='1007723')                        -- 子科目段
             And (fvl5.flex_value=gcck.segment5 And fvl5.FLEX_VALUE_SET_ID='1007724')                        -- 公司间段
             And (fvl6.flex_value=gcck.segment6 And fvl6.FLEX_VALUE_SET_ID='1007725')                        -- 产品段
             And (fvl7.flex_value=gcck.segment7 And fvl7.FLEX_VALUE_SET_ID='1007726')                        -- 项目段
             And (fvl8.flex_value=gcck.segment8 And fvl8.FLEX_VALUE_SET_ID='1007727');
            
           Select
                 fvl1.Description||'.'||fvl2.Description||'.'||fvl3.Description||'.'||fvl4.Description||'.'||fvl5.Description||'.'||fvl6.Description||'.'||fvl7.Description||'.'||fvl8.Description A3
             Into l_acct_desc2
            From gl_code_combinations_kfv gcck
                 ,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 gcck.Concatenated_Segments=p_Account_to
             And (fvl1.flex_value=gcck.segment1 And fvl1.FLEX_VALUE_SET_ID='1007720')                        -- 公司段
             And (fvl2.flex_value=gcck.segment2 And fvl2.FLEX_VALUE_SET_ID='1007721')                        -- 部门段
             And (fvl3.flex_value=gcck.segment3 And fvl3.FLEX_VALUE_SET_ID='1007722')                        -- 科目段
             And (fvl4.flex_value=gcck.segment4 And fvl4.FLEX_VALUE_SET_ID='1007723')                        -- 子科目段
             And (fvl5.flex_value=gcck.segment5 And fvl5.FLEX_VALUE_SET_ID='1007724')                        -- 公司间段
             And (fvl6.flex_value=gcck.segment6 And fvl6.FLEX_VALUE_SET_ID='1007725')                        -- 产品段
             And (fvl7.flex_value=gcck.segment7 And fvl7.FLEX_VALUE_SET_ID='1007726')                        -- 项目段
             And (fvl8.flex_value=gcck.segment8 And fvl8.FLEX_VALUE_SET_ID='1007727');
      End If;
     
      l_acct_name:=p_Account_From||'---'||p_Account_to;
      l_acct_desc:=l_acct_desc1||'---'||l_acct_desc2;
     
      -- 输出报表头
      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);
      l_SHOW := rpad('会计科目', 20, ' ') || rpad(l_acct_name,100, ' ');
      cux_my_public_pkg.Out(l_SHOW);
      l_SHOW := rpad('科目说明', 20, ' ') || rpad(l_acct_desc,100, ' ');
      cux_my_public_pkg.Out(l_SHOW);
      cux_my_public_pkg.OUT(' ');
      
      l_SHOW := rpad('来源', 20, ' ') || rpad('科目', 50, ' ') ||rpad('科目说明', 150, ' ') ||
      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(' ', 50, '-') ||
              lpad(' ', 150, '-') ||
              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;
         l_now_dr_t:=0;
         l_now_cr_t:=0;
         l_year_dr_t:=0;
         l_year_cr_t:=0;
         l_end_dr_t:=0;
         l_end_cr_t:=0;
        
         For i In cr1 Loop
        
            -- 帐户类型  A  (资产) E  (费用类)
            SELECT Count(Distinct gcc.account_type)
                INTO p_account_type
             FROM gl_code_combinations gcc
            WHERE gcc.Code_Combination_Id=i.accid
              And (gcc.Segment3 Like '1%' Or gcc.Segment3 Like '4%');
           
            IF p_account_type>0 THEN
                p_sign := 1;
            ELSE
                p_sign := -1;
            END IF;

            -- 期初余额
            SELECT p_sign*nvl(Sum(Distinct (gc.begin_balance_dr-gc.begin_balance_cr)+(gc.period_net_dr-gc.period_net_cr)),0)
                into l_total
             FROM GL_BALANCES gc
            WHERE gc.Set_Of_Books_Id = p_set_of_gl_id
              AND gc.code_combination_id = i.accid
              AND gc.period_name = substr(to_char(add_months(to_date(substr(p_start_date,4,4)||'-'||substr(p_start_date,1,2)||'-01','yyyy-mm-dd'),-1),'YYYY-MM-DD'),6,2)||'-'||substr(to_char(add_months(to_date(substr(p_start_date,4,4)||'-'||substr(p_start_date,1,2)||'-01','yyyy-mm-dd'),-1),'YYYY-MM-DD'),1,4)
              AND gc.actual_flag = 'A';
             
            IF p_sign=1 THEN
               l_start_dr:=l_total;
               l_start_cr:=0;
            End If;
           
            If p_sign=-1 Then
               l_start_dr:=0;
               l_start_cr:=l_total;
            end if;
           
            -- 年发生额
            Select nvl(sum(gv.Line_Entered_Dr),0),
                   nvl(sum(gv.Line_Entered_cr),0)
                 Into l_year_dr,
                      l_year_cr
            From Gl_Je_Journal_Lines_v gv
            Where gv.Period_Year=substr(p_start_date,4,4)
              And gv.Actual_Flag='A'
              And gv.Batch_Status='P'
              And gv.Line_Code_Combination_Id=i.accid;

            -- 期末数
           IF p_account_type>0 THEN
              l_qm_dr:=l_start_dr+i.A5-i.A6;
              l_qm_cr:=0;
           Else
              l_qm_dr:=0;
              l_qm_cr:=l_start_cr+i.A6-i.A5;
           End If;              
             
            l_SHOW := rpad(i.A3, 20, ' ') || rpad(i.A1, 50, ' ') ||
                      rpad(i.A2, 150, ' ') ||
                      rpad(i.A4, 10, ' ') ||
                      rpad(to_char(l_start_dr,'999,999,999,990.00'), 20, ' ') || rpad(to_char(l_start_cr,'999,999,999,990.00'), 20, ' ') ||
                      rpad(to_char(i.A5,'999,999,999,990.00'), 20, ' ') || rpad(to_char(i.A6,'999,999,999,990.00'), 20, ' ') ||
                      rpad(to_char(l_year_dr,'999,999,999,990.00'), 20, ' ') || rpad(to_char(l_year_cr,'999,999,999,990.00'), 20, ' ') ||
                      rpad(to_char(l_qm_dr,'999,999,999,990.00'), 20, ' ') || rpad(to_char(l_qm_cr,'999,999,999,990.00'), 20, ' ');
            cux_my_public_pkg.OUT(l_SHOW);
     
            -- 汇总
            l_start_dr_t:=l_start_dr_t+l_start_dr;
            l_start_cr_t:=l_start_cr_t+l_start_cr;
           
            l_now_dr_t:=l_now_dr_t+i.A5;
            l_now_cr_t:=l_now_cr_t+i.A6;
           
            l_year_dr_t:=l_year_dr_t+l_year_dr;
            l_year_cr_t:=l_year_cr_t+l_year_cr;
            l_end_dr_t:=l_end_dr_t+l_qm_dr;
            l_end_cr_t:=l_end_cr_t+l_qm_cr;
     
         END LOOP;
         l_SHOW := lpad(' ', 20, '-') || lpad(' ', 50, '-') ||
                    lpad(' ', 150, '-') ||
                    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);
         
         l_SHOW := rpad('合计', 20, ' ') || rpad(' ', 50, ' ') ||
                    rpad(' ', 150, ' ') ||
                    rpad(' ', 10, ' ') ||
                    rpad(to_char(l_start_dr_t,'999,999,999,990.00'), 20, ' ') || rpad(to_char(l_start_cr_t,'999,999,999,990.00'), 20, ' ') ||
                    rpad(to_char(l_now_dr_t,'999,999,999,990.00'), 20, ' ') || rpad(to_char(l_now_cr_t,'999,999,999,990.00'), 20, ' ') ||
                    rpad(to_char(l_year_dr_t,'999,999,999,990.00'), 20, ' ') || rpad(to_char(l_year_cr_t,'999,999,999,990.00'), 20, ' ') ||
                    rpad(to_char(l_end_dr_t,'999,999,999,990.00'), 20, ' ') || rpad(to_char(l_end_cr_t,'999,999,999,990.00'), 20, ' ');
         cux_my_public_pkg.OUT(l_SHOW);
      End If;
  End cuxgllistgathmx;
 
end CUX_GL_REP_LISTPKG;