总账凭证之间核销的SQL

问题描述

最近有个很奇葩的需求，财务针对应收账款和收款的业务记账不是通过EBS AR模块做的，而是直接在GL总账录入凭证，但是又想出应收款账龄报表。我们面对的数据是一堆日记账，有借：应收科目的，有贷应收科目的，借代表有应收款，贷表示收款后核销应收，由于已经无从知道到底核销哪一笔，采用按借应收款的入账日期进行核销，考虑写个FUNCTION来逐笔计算核销，每笔日记账循环性能会有问题，所以想用SQL直接计算出借应收款核销余额。

解决方法

首先吐槽财务管理之混乱无序无规则。能出现这种记账方式居然还是实施了EBS的公司，叹息。

假设我们已经有一个表A，存储了科目是应收账款的journal line， A可以是个临时表，也可以用WITH AS 语句获得。下面介绍如何在A基础上得到核销金额和核销余额。基本的思路是：
1 将A按金额分组并且按组编号，金额>0的一组，<0的一组，>0代表应收款， <0代表收款准备要核销应收款的记录
2 应收款的一组组内按记账日期排序
3 收款的一组计算每组的收款总和
4 核销，计算核销金额和核销余额

具体的SQL如下：

with a as
(select .....),

c AS
 (SELECT a.*,
         --按某些字段分组，根据业务需求，dense_rank()是各个组的编号，即组内每行记录的编号是一样的
         dense_rank() over(ORDER BY ou_name, currency_code, currency_conversion_rate, segment3, batch_name) group_number
  FROM   a),

d AS
 (SELECT c.*,
 -- rn 是组内按记账日期排序序号
         rank() over(PARTITION BY c.group_number ORDER BY default_effective_date) rn, 
         --计算每组记账日期排序每行前序金额总和，这样后面可以计算出该行能核销多少
         SUM(acc_dr_cr) over(PARTITION BY c.group_number 
                        ORDER BY default_effective_date 
                        RANGE BETWEEN unbounded preceding AND CURRENT ROW) sum_dr_cr 
  FROM   c
  WHERE  acc_dr_cr > 0),
e AS
 (SELECT c.group_number,
         SUM(abs(acc_dr_cr)) sum_dr_cr
  FROM   c
  WHERE  acc_dr_cr < 0
  GROUP BY group_number)
select f.*, 
       f.applied_amt, 
       f.acc_dr_cr-f.applied_amt amt_remaining
from 
(SELECT d.*,
       CASE
         
         WHEN d.sum_dr_cr >= e.sum_dr_cr + d.acc_dr_cr THEN
          0
         WHEN d.sum_dr_cr < e.sum_dr_cr THEN
          d.acc_dr_cr
         ELSE
          e.sum_dr_cr + d.acc_dr_cr - d.sum_dr_cr
       END applied_amt
FROM   d,
       e
WHERE  d.group_number = e.group_number(+)) f
where f.acc_dr_cr-applied_amt<>0
order by f.group_number,f.rn