Oracle数据库函数和Python两种方式实现单据无尾差折扣费用分摊

一、引言：业务场景与痛点

1. 需求背景
   • 我们在做数据集成时，订单折扣费用分摊的常见场景（如电商平台订单拆分、物流费用分摊）。
   • 传统分摊方法的尾差问题（如四舍五入导致总金额与原始金额不一致），财务对账有差异。
2. 无尾差分摊的核心目标
   • 确保分摊后总金额 = 原始金额，避免财务对账差异。

二、计算逻辑说明

1. 步骤
   • 计算每行分摊比例。
   • 按比例分配折扣，保留小数位。
   • 处理尾差：将剩余金额分配给最后一行或按特定规则分配。

三、代码实现

1. Oracle函数代码实现示例

CREATE OR REPLACE FUNCTION get_setdtlzk (n_sasetdtlid in number)

RETURN number

AS

v_setdocid number(10,0);
v_salesid number(10,0);
v_goodsid number(10,0);
v_dtlcount number(10,0);
v_mindtlid number(10,0);
v_goodsqty NUMBER(16,6);
v_sumgoodsqty NUMBER(16,6);
v_setdoczk number(10,2);
v_setdtlzktmp number(10,2);
v_setfmindtlzk number(10,2);
v_setdtlzk number(10,2);

BEGIN

select sasettleid,salesid,goodsid,nvl(zxreturnqty,goodsqty) into v_setdocid,v_salesid,v_goodsid,v_goodsqty from zx_bms_sa_settle_dtl_v where sasettledtlid = n_sasetdtlid;
select count(1) into v_dtlcount from zx_bms_sa_settle_dtl_v  where sasettleid = v_setdocid AND salesid=v_salesid and goodsid = v_goodsid;
select min(sasettledtlid) into v_mindtlid from zx_bms_sa_settle_dtl_v  where sasettleid = v_setdocid AND salesid=v_salesid and goodsid = v_goodsid;
select sum(nvl(zxreturnqty,goodsqty)) into v_sumgoodsqty from zx_bms_sa_settle_dtl_v  where sasettleid = v_setdocid AND salesid=v_salesid and goodsid = v_goodsid;
select nvl(total_line,0) into v_setdoczk from zx_sasetdocdtl_zhk_v where sasettleid = v_setdocid AND zx_distosaid = v_salesid and goodsid = v_goodsid;
select nvl(round( v_setdoczk * v_goodsqty / v_sumgoodsqty ,2),0) into v_setdtlzktmp from  zx_sasettle_dtl_htjstmp where sasettledtlid = n_sasetdtlid;
select sum(ZKHSJE) into v_setfmindtlzk from zx_sasettle_dtl_htjstmp where sasettleid = v_setdocid AND salesid = v_salesid and goodsid = v_goodsid AND sasettledtlid <> v_mindtlid;


-- 没有折扣 
IF v_setdtlzktmp = 0
THEN
v_setdtlzk := 0;

-- 有这个只有只有一行不需要分摊
ELSIF v_setdtlzktmp <> 0 and v_dtlcount = 1
THEN
v_setdtlzk := v_setdoczk;

-- 有折扣多行，非最小的一行
ELSIF v_setdtlzktmp <> 0 and v_dtlcount > 1 and n_sasetdtlid <> v_mindtlid
THEN 
v_setdtlzk := v_setdtlzktmp;

--有折扣多行，最小ID一行
ELSIF v_setdtlzktmp <> 0 and v_dtlcount > 1 and n_sasetdtlid = v_mindtlid
THEN
v_setdtlzk := v_setdoczk - v_setfmindtlzk;

ELSE
v_setdtlzk  := 0;
END IF;


RETURN v_setdtlzk;

END get_setdtlzk;

3. Python代码解析

#print(total_sale_amount)
if total_sale_amount>0:
    for k in range(len(docdtl["items"])):
        if k != max(range(len(docdtl["items"]))):
             docdtl["items"][k]["pay_amount"] = round(float(docdtl["total_pay_amount_zs"])*float(docdtl["items"][k]["sale_amount"])/total_sale_amount,2)
        else:
            docdtl["items"][k]["pay_amount"] = round(docdtl["total_pay_amount_zs"] -  sum(float(item['pay_amount']) for item in docdtl["items"][:-1]) ,2)
            docdtl["items"][k]["pay_price"] = round(float(docdtl["items"][k]["pay_amount"]/docdtl["items"][k]["qty"]),10)
else:    
    for k in range(len(docdtl["items"])):
         docdtl["items"][k]["pay_amount"] = 0.0
         docdtl["items"][k]["pay_price"] = 0.0