20230511销售订单审核-信用额度 - 发货单部分

select
top 5  -- 加了限制n条
cvouchtype,cdlcode as ccode,ddate,min(a.dgatheringdate) as dgatheringdate,
sum(case when isnull(iquantity,0)+isnull(tbquantity,0)<>0 then isnull(iUnitPrice,0) * (isnull(iquantity,0)+isnull(tbquantity,0)-isnull(isettlequantity,0)-isnull(fretqtywkp,0))
else (isnull(isum,0)-(isnull(fretsum,0)+isnull(isettlenum,0)))
end+(isnull(isettlenum,0)-isnull(iexchsum,0))
) as fmoney,
sum(isnull(isum,0)-(case when isnull(isum,0)=0 then 0 else cast(isnull(isettlenum,0) as decimal(26,9)) end)) uninvoice_money,
sum((case when isnull(isum,0)=0 then 0 else cast(isnull(isettlenum,0) as decimal(26,9)) end)-isnull(iexchsum,0)) unpaid_money

from (dispatchlist a with (nolock)
inner join dispatchlists b with (nolock) on (a.dlid=b.dlid))

left join customer with(nolock) on a.ccuscode=customer.ccuscode

where isnull(bcashsale,0) = 0
and (a.bFirst=1 or (a.bFirst=0 and a.dDate>=(select cvalue from accinformation where csysid=N'SA' and cName=N'dStartDate'))) and a.cvouchtype<>N'00' and a.bcredit=1 and a.breturnflag=0
and ccuscreditcompany = '11072'   -- 传入客户编码进行筛选
and isnull(isum,0) <> 0
and a.dlid <> 0
and isnull(b.bsettleall,0) = 0
and isnull(dcreditstart,N'') <> N''
and isnull(cverifier,'') <> N''
AND dgatheringdate < dateadd(day, -30, getdate())  -- 加了限制日期,可以配置，默认30
group by cvouchtype,cdlcode,ddate,dgatheringdate
having sum(isnull(isum,0)) > 0
and (
    sum(isnull(isum,0)-(case when isnull(isum,0)=0 then 0 else cast(isnull(isettlenum,0) as decimal(26,9)) end))>0 -- 还有未结算金额，发票？
    or sum((case when isnull(isum,0)=0 then 0 else cast(isnull(isettlenum,0) as decimal(26,9)) end)-isnull(iexchsum,0))>0
);