费用表 财务 管理 销售
--费用表
select (case when substr(b.dispname,0,4) = 6606 then '6601' else substr(b.dispname,0,4) end) as costtype,substr(a.prepareddatev,0,4) as time_year,sum(a.debitamount/10000) as amount from gl_detail a
left join bd_accasoa b on a.pk_accasoa = b. pk_accasoa
where substr(b.dispname,0,4) in (6601,6602,6603,6606) and substr(a.prepareddatev,0,4) >= '2023' and substr(a.prepareddatev,0,4) <= '2024' and substr(prepareddatev,6,5) <= '12-31'
group by (case when substr(b.dispname,0,4) = 6606 then '6601' else substr(b.dispname,0,4) end),substr(a.prepareddatev,0,4)
分组织费用表
select (case when substr(b.dispname,0,4) = 6606 then '6601' else substr(b.dispname,0,4) end) as costtype,substr(a.prepareddatev,0,4) as time_year,e.name as company,sum(a.debitamount/10000) as amount from gl_detail a
left join bd_accasoa b on a.pk_accasoa = b. pk_accasoa
left join org_accountingbook e on a.pk_accountingbook = e.pk_accountingbook
where substr(a.prepareddatev,0,4) >= '2023' and substr(a.prepareddatev,0,4) <= '2024' and substr(prepareddatev,6,5) <= '12-31'
--and (case when substr(b.dispname,0,4) = 6606 then '6601' else substr(b.dispname,0,4) end) = '${costtype}'
group by e.name,(case when substr(b.dispname,0,4) = 6606 then '6601' else substr(b.dispname,0,4) end),substr(a.prepareddatev,0,4)
明细科目
select (case when substr(b.dispname,0,4) = 6606 then '6601' else substr(b.dispname,0,4) end) as costtype,e.name as company,b.name as capitaltype,substr(a.prepareddatev,0,4) as time_year,sum(a.debitamount/10000) as amount from gl_detail a
left join bd_accasoa b on a.pk_accasoa = b. pk_accasoa
left join org_accountingbook e on a.pk_accountingbook = e.pk_accountingbook
where 1=1
--and (case when substr(b.dispname,0,4) = 6606 then '6601' else substr(b.dispname,0,4) end) = '${costtype}'
and substr(a.prepareddatev,0,4) >= '2023' and substr(a.prepareddatev,0,4) <= '2024' and substr(prepareddatev,6,5) <= '12-31'
--and e.name = '${company}'
group by (case when substr(b.dispname,0,4) = 6606 then '6601' else substr(b.dispname,0,4) end),e.name,b.name,substr(a.prepareddatev,0,4)
资金表
select sum(a.debitamount)-sum(a.creditamount),substr(dispname,instr(dispname,'\')+1,4) as capitaltype from gl_detail a
left join bd_accasoa b on a.pk_accasoa = b. pk_accasoa
left join org_accountingbook e on a.pk_accountingbook = e.pk_accountingbook
left join gl_docfree1 f on a.assid = f.assid
left join bd_bankaccsub g on g.pk_bankaccsub = f.F11
left join bd_bankaccbas h on h.pk_bankaccbas = g.pk_bankaccbas
where (h.accattribute != '3' and h.accnum != '11710076801100000856' or h.accattribute is null )
------此处h.accattribute != '3'用于过滤掉专用账户，这个专用账户为客户暂存的预付款或者保证金，企业得暂管
and (substr(b.dispname,0,instr(dispname,'\')-1) in ('1002','112101','112102') or substr(b.dispname,0,instr(dispname,'\')-1) like ('1001%'))
and a.yearv ='2024'
group by substr(dispname,instr(dispname,'\')+1,4)
科目余额表
select substr(a.prepareddatev,0,7) as y_month,b.dispname,substr(b.dispname,instr(b.dispname,'\',-1,1)+1) as title,
(case when length(h.name) >0 then h.name end)||
(case when length(i.name) >0 then i.name end)||
(case when length(k.name) >0 then k.name end)||
(case when length(c.name) >0 then c.name end)||
(case when length(l.name) >0 then l.name end) as main
,replace(e.name,'-基准账簿','') as company,sum(a.debitamount)-sum(a.creditamount) as jie,sum(a.creditamount)-sum(a.debitamount) as dai from gl_detail a
left join bd_accasoa b on a.pk_accasoa = b.pk_accasoa
left join org_accountingbook e on a.pk_accountingbook = e.pk_accountingbook
left join gl_docfree1 f on a.assid = f.assid
left join bd_customer h on f.F13 = h.pk_customer--客户档案
left join bd_supplier i on f.F14 = i.pk_supplier--供应商档案
left join bd_bankaccsub j on f.F11 = j.pk_bankaccsub --银行账户
left join bd_bankaccbas k on k.pk_bankaccbas = j.pk_bankaccbas
left join bd_marbasclass c on f.F16 = c.pk_marbasclass--物料基本信息
left join bd_psndoc l on f.F2 = l.pk_psndoc--人员档案
where substr(a.prepareddatev,0,7) <= '2024-09' and a.yearv = substr('2024',0,4)
--where substr(a.prepareddatev,0,7) <= '${time2}' and a.yearv = substr('${time2}',0,4)
--${if(len(company2) != 0,"and replace(e.name,'-基准账簿','') in ('"+company2 +"')","")}
group by substr(a.prepareddatev,0,7), b.dispname,replace(e.name,'-基准账簿',''),
(case when length(h.name) >0 then h.name end)||
(case when length(i.name) >0 then i.name end)||
(case when length(k.name) >0 then k.name end)||
(case when length(c.name) >0 then c.name end)||
(case when length(l.name) >0 then l.name end)
order by b.dispname
财务科目切换版本存储过程
主要是找到新版本和旧版本的表进行切换处理
create or replace PROCEDURE GET_FYSJ_CW (
time3 IN NUMBER,
company2 IN VARCHAR2,
o_cursor OUT SYS_REFCURSOR
) AS
p_version NUMBER;
sql_query CLOB;
month_list VARCHAR2(4000);
BEGIN
SELECT MAX(TO_NUMBER(SUBSTR(BEGINPERIOD, 1, 4))) INTO p_version FROM bd_accchart;
--SELECT LISTAGG('''' || TRIM(REGEXP_SUBSTR(time2, '[^,]+', 1, LEVEL)) || '''', ',') WITHIN GROUP (ORDER BY LEVEL)
--INTO month_list
--FROM DUAL
--CONNECT BY REGEXP_SUBSTR(time2, '[^,]+', 1, LEVEL) IS NOT NULL;
sql_query := 'SELECT * FROM (' ||
'SELECT SUM(a.debitamount) AS debit_sum, b.name AS title, z.code, REPLACE(e.name, ''-基准账簿'', '''') AS companyname ,(case when a.debitamount >= 0 then 1 when a.debitamount < 0 then 0 end) as part ' ||
'FROM gl_detail a ' ||
'LEFT JOIN bd_accasoa b ON a.pk_accasoa = b.pk_accasoa AND b.endflag = ''Y'' ' ||
'LEFT JOIN bd_account z ON b.pk_account = z.pk_account ' ||
'LEFT JOIN org_accountingbook e ON a.pk_accountingbook = e.pk_accountingbook ' ||
'WHERE a.debitamount != 0 and SUBSTR(b.dispname, 1, 4) = ''6603'' ' ||
'AND a.yearv = '||time3 ||
'AND REPLACE(e.name, ''-基准账簿'', '''') = '''||company2||
''' GROUP BY z.code, b.name, e.name,(case when a.debitamount >= 0 then 1 when a.debitamount < 0 then 0 end) ' ||
'UNION ALL ' ||
'SELECT SUM(a.amount) AS amount_sum, b.kmc, a.code, a.company,(case when a.amount >= 0 then 1 when a.amount < 0 then 0 end) as part ' ||
'FROM budget_dep@fr a ' ||
'LEFT JOIN (SELECT a.name company, d.code code, e.name kmc FROM org_orgs a ' ||
'LEFT JOIN bd_accchart b ON a.pk_org = b.pk_org ' ||
'LEFT JOIN bd_accasoa e ON e.pk_accchart = b.pk_accchart ' ||
'LEFT JOIN bd_account d ON e.pk_account = d.pk_account) b ON a.company = b.company AND a.code = b.code ' ||
'WHERE SUBSTR(a.code, 1, 4) = ''6603'' ' ||
'AND a.year = '||time3 ||
'AND a.company ='''||company2||
''' GROUP BY b.kmc, a.code, a.company,(case when a.amount >= 0 then 1 when a.amount < 0 then 0 end) ) ' ||
'ORDER BY 3';
BEGIN
IF time3 >= p_version THEN
OPEN o_cursor FOR sql_query;
ELSE
sql_query := REPLACE(sql_query, 'bd_accasoa', 'bd_accasoahistory');
OPEN o_cursor FOR sql_query;
END IF;
END;
END GET_FYSJ_CW;
应收预收应付预付
oracle语法
--应收账款
select sum(a.debitamount/10000)-sum(a.creditamount/10000) as amount,substr(b.dispname,0,4) as capitaltype from gl_detail a
left join bd_accasoa b on a. pk_accasoa = b. pk_accasoa
left join org_accountingbook e on a.pk_accountingbook = e.pk_accountingbook
left join gl_docfree1 f on a.assid = f.assid
left join bd_customer g on f.F13 = g.pk_customer
where substr(b.dispname,0,4) in ('1122') and a.yearv = '2025'
group by substr(b.dispname,0,4)
union --预收账款
select sum(a.creditamount/10000)-sum(a.debitamount/10000) as amount,substr(b.dispname,0,4) as capitaltype from gl_detail a
left join bd_accasoa b on a. pk_accasoa = b. pk_accasoa
left join org_accountingbook e on a.pk_accountingbook = e.pk_accountingbook
left join gl_docfree1 f on a.assid = f.assid
left join bd_customer g on f.F13 = g.pk_customer
where substr(b.dispname,0,4) in ('2203') and a.yearv = '2025'
group by substr(b.dispname,0,4)
union --预付账款
select sum(a.debitamount/10000)-sum(a.creditamount/10000) as amount,substr(b.dispname,0,4) as capitaltype from gl_detail a
left join bd_accasoa b on a. pk_accasoa = b. pk_accasoa
left join org_accountingbook e on a.pk_accountingbook = e.pk_accountingbook
left join gl_docfree1 f on a.assid = f.assid
left join bd_supplier g on f.F14 = g.pk_supplier
where substr(b.dispname,0,4) in ('1123') and a.yearv = '2025'
group by substr(b.dispname,0,4)
union --应付账款
select sum(a.creditamount/10000)-sum(a.debitamount/10000) as amount,substr(b.dispname,0,4) as capitaltype from gl_detail a
left join bd_accasoa b on a. pk_accasoa = b. pk_accasoa
left join org_accountingbook e on a.pk_accountingbook = e.pk_accountingbook
left join gl_docfree1 f on a.assid = f.assid
left join bd_supplier g on f.F14 = g.pk_supplier
where substr(b.dispname,0,4) in ('2202') and a.yearv = '2025'
group by substr(b.dispname,0,4)
