Share
Report SQL Percentage and Fixed Allocation Distribution Amount Calculations are not working out for me.
*This post is locked for comments
You’re offline. This is a read only version of the page.
750
*This post is locked for comments
Below in Red are the calculations that are not accurate.
select
h.VENDORID as "Vendor ID",
mas.VENDNAME as "Vendor Name",
CAST(inv.docdate as DATE) as "DOC DATE",
inv.DOCNUMBR AS "INV/Doc NUM.",
an.ACTNUMST Fixed_Allocation_Account,
a.ACTDESCR Fixed_Allocation_Account_Name,
da.ACTNUMST Distribution_Account,
dn.ACTDESCR Distribution_Account_Name,
CONVERT(VARCHAR, ROUND(f.PRCNTAGE,2),2) + '%' Distribution_Percentage,
inv.DOCAMNT * f.PRCNTAGE as "Distribution Amount",
h.DEBITAMT,
h.CRDTAMNT,
h.DISTTYPE,
h.VCHRNMBR as "Voucher Num",
inv.PORDNMBR as "PO Number",
inv.TRXDSCRN as "Description",
inv.PYMTRMID as "Payment Terms",
inv.DOCAMNT as "Inv Amount"
from GL00100 a --account master
inner join GL00105 an on a.ACTINDX = an.ACTINDX--account number
inner join GL00103 f on a.ACTINDX = f.ACTINDX --fixed allocation account setup
inner join GL00105 da on f.DSTINDX = da.ACTINDX--distribution account
inner join GL00100 dn on f.DSTINDX = dn.ACTINDX--distribution account name
inner join PM30600 as h ON a.ACTINDX=h.DSTINDX--historical ap table
inner join PM00200 as mas ON mas.vendorid = h.vendorid--master ap records
inner join PM30200 as inv ON h.VCHRNMBR=inv.VCHRNMBR--historical paid transactions
where a.ACCTTYPE = 3 and a.ACTIVE = 1 and a.ACTINDX not in (select ACTINDX from GL00104) and h.DISTTYPE = 6 and h.VENDORID = 'ADTSECS010559'
order by h.VENDORID,inv.docdate, inv.DOCNUMBR, an.ACTNUMST
go
DistributionPercentage - Distribution Amount - Inv Amount
3.19% 737.9427 231.33
My sql shows the above output. The Distribution amount should be $7.379.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,280
Super User 2024 Season 2
#2
Martin Dráb
230,235
Most Valuable Professional
#3
nmaenpaa
101,156
All responses (
Answers (
