一个Oracle 报表的PL/SQL代码
分类： Oracle 开发 2005-08-03 12:23 2118人阅读 评论(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_idNumber, 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_Fromvarchar2, p_Account_to varchar2, p_to_gl varchar2, p_je_source Varchar2, p_set_of_gl_idNumber, 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_idNumber, p_org_id Number, p_report_type Number) Is l_show Varchar2(2000); l_org_name Varchar2(2000); p_sign Varchar2(200); p_account_typeNumber:=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 Fromgl_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_Fromvarchar2, p_Account_to varchar2, p_to_gl varchar2, p_je_source Varchar2, p_set_of_gl_idNumber, p_org_id Number, p_report_type Number) Is l_show Varchar2(2000); l_org_name Varchar2(2000); p_sign Varchar2(200); p_account_typeNumber:=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 Fromgl_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 Bygjl.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;
页:
[1]