oracle报表范例1 (转载)
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;
本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/long_li/archive/2005/08/03/444862.aspx
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;
本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/long_li/archive/2005/08/03/444862.aspx
