王善凯 每月底，系统中要求的导数据

SELECT '2025' 年, '2' 月, -- 修改年月
    t2.companyname as 客户名称,t1.* 
FROM (
    select
        customid 客户ID,
        sum(remmoney2) + sum(remmoney3) + sum(remmoney4) as 期初合计 ,
        sum(summoney2) + sum(summoney3) + sum(summoney4) as 发生合计,
        sum(money2) + sum(money3) + sum(money4) as 余额合计,

        sum(remmoney2) as 发出应收账期初,
        sum(money2) as 发出应收账余额,
        sum(summoney2) as 发出应收账发生,
        sum(sumcreditmoney2) as 发出应收账贷方发生额,
        sum(sumdebitmoney2) as 发出应收账借方发生额,

        sum(remmoney3) as 应收账期初,
        sum(money3) as 应收账余额,
        sum(summoney3) as 应收账发生,
        sum(sumcreditmoney3) as 应收账贷方发生额,
        sum(sumdebitmoney3) as 应收账借方发生额,

        sum(remmoney4) as 预收账期初,
        sum(money4) as 预收账余额,
        sum(summoney4) as 预收账发生,
        sum(sumcreditmoney4) as 预收账贷方发生额,
        sum(sumdebitmoney4) as 预收账借方发生额
    from (
        select
            TO_NCHAR(a.companyid) as customid,

            TO_NUMBER(0) as summoney2,
            TO_NUMBER(0) as sumcreditmoney2,
            TO_NUMBER(0) as sumdebitmoney2,
            TO_NUMBER(DECODE(b.acctype,2,nvl(a.remdebitmoney, 0) - nvl(a.remcreditmoney, 0),0)) as money2,
            TO_NUMBER(DECODE(b.acctype,2,nvl(a.remdebitmoney, 0) - nvl(a.remcreditmoney, 0),0)) as remmoney2,

            TO_NUMBER(0) as summoney3,
            TO_NUMBER(0) as sumcreditmoney3,
            TO_NUMBER(0) as sumdebitmoney3,
            TO_NUMBER(DECODE(b.acctype,3,nvl(a.remdebitmoney, 0) - nvl(a.remcreditmoney, 0),0)) as money3,
            TO_NUMBER(DECODE(b.acctype,3,nvl(a.remdebitmoney, 0) - nvl(a.remcreditmoney, 0),0)) as remmoney3,

            TO_NUMBER(0) as summoney4,
            TO_NUMBER(0) as sumcreditmoney4,
            TO_NUMBER(0) as sumdebitmoney4,
            TO_NUMBER(DECODE(b.acctype,4,nvl(a.remdebitmoney, 0) - nvl(a.remcreditmoney, 0),0)) as money4,
            TO_NUMBER(DECODE(b.acctype,4,nvl(a.remdebitmoney, 0) - nvl(a.remcreditmoney, 0),0)) as remmoney4

        from bms_acc_rem a, bms_acc_def b
        where a.accid = b.accid
            and a.usemm = 17486 -- 上月月序
            and a.logicmm = 24301 --上月逻辑月序
            and b.acctype in(2,3,4)

        UNION all

        select
            TO_NCHAR(a.companyid) as customid,

            TO_NUMBER(DECODE(b.acctype,2,nvl(a.sumdebitmoney, 0) - nvl(a.sumcreditmoney, 0),0)) as summoney2,
            TO_NUMBER(DECODE(b.acctype,2,nvl(a.sumcreditmoney, 0),0)) as sumcreditmoney2,
            TO_NUMBER(DECODE(b.acctype,2,nvl(a.sumdebitmoney, 0),0)) as sumdebitmoney2,
            TO_NUMBER(DECODE(b.acctype,2,nvl(a.sumdebitmoney, 0) - nvl(a.sumcreditmoney, 0),0)) as money2,
            TO_NUMBER(0) as remmoney2,

            TO_NUMBER(DECODE(b.acctype,3,nvl(a.sumdebitmoney, 0) - nvl(a.sumcreditmoney, 0),0)) as summoney3,
            TO_NUMBER(DECODE(b.acctype,3,nvl(a.sumcreditmoney, 0),0)) as sumcreditmoney3,
            TO_NUMBER(DECODE(b.acctype,3,nvl(a.sumdebitmoney, 0),0)) as sumdebitmoney3,
            TO_NUMBER(DECODE(b.acctype,3,nvl(a.sumdebitmoney, 0) - nvl(a.sumcreditmoney, 0),0)) as money3,
            TO_NUMBER(0) as remmoney3,

            TO_NUMBER(DECODE(b.acctype,4,nvl(a.sumdebitmoney, 0) - nvl(a.sumcreditmoney, 0),0)) as summoney4,
            TO_NUMBER(DECODE(b.acctype,4,nvl(a.sumcreditmoney, 0),0)) as sumcreditmoney4,
            TO_NUMBER(DECODE(b.acctype,4,nvl(a.sumdebitmoney, 0),0)) as sumdebitmoney4,
            TO_NUMBER(DECODE(b.acctype,4,nvl(a.sumdebitmoney, 0) - nvl(a.sumcreditmoney, 0),0)) as money4,
            TO_NUMBER(0) as remmoney4
        from bms_acc_sum a, bms_acc_def b
        where a.accid = b.accid
            and a.usemm = 18254 -- 导出月的月序
            and a.logicmm = 24302 -- 导出月的逻辑月序
            and exists (select 1 from pub_settle_account t where a.usemm = t.usemm and t.entryid = 96)
            and b.acctype in(2,3,4)
    )
    group by customid
) t1
-- 单位关联 名称
LEFT JOIN PUB_COMPANY t2 ON t2.companyid = t1.客户ID