Our company pays its sales reps based on cash receipts when they are received, in some cases prior to an invoice being generated. This motivates them to collect payments and makes cash flow more manageable.
The accounting dept. needs a report that shows them:
A) Total payments received
B) Payments received, invoices applied to and the memo field from the payment as this indicates if the payment was for an aged invoice or postage. In those two cases commission is not paid.
Thanks in large part to an applied payables view created by Victoria Yudin we have a report that is 99% there. The challenge we have is that the total payments is counting some records multiple times. This is occurring when a payment is applied to multiple invoices. For example if I have 4 payments of $100 each, but one of the payments was applied to two invoices, my total shows as $500 and not $400. Below is a screen grab of what it looks like.
As you can see the first payment is counted 4 times as it was applied to 4 different invoices.
I read about an option to create a 2nd dataset and create the sum field based off of this, but when I do that I get the total for all payments made in the specified time period.
I am hoping there is a simpler way to sum the fields.
Any thoughts or suggestions would be much appreciated. Thanks!
Below is the report itself.
SELECT
RM20101.CUSTNMBR
,RM20101.DOCNUMBR
,RM20101.TRXDSCRN
,RM20101.DOCDATE
,RM20101.ORTRXAMT
,RM20101.CURTRXAM
,[View AR Applied Detail].Total_Applied_Amount
,[View AR Applied Detail].Amount_Applied
,[View AR Applied Detail].Applied_to_Doc_Number
,[View AR Applied Detail].Applied_to_Document_Date
,[View AR Applied Detail].Applied_To_Doc_Total
,[View AR Applied Detail].Applied_To_Doc_Unapplied_Amount
,[View AR Applied Detail].Customer_PO_Number
,[View AR Applied Detail].Document_Number
,[View AR Applied Detail].Document_Date
,[View AR Applied Detail].Customer_Name
,[View AR Applied Detail].Original_Trx_Amount
,[View AR Applied Detail].Current_Trx_Amount
,[View AR Applied Detail].Customer_ID
,RM00101.SLPRSNID
FROM
RM00101
FULL OUTER JOIN RM20101
ON RM00101.CUSTNMBR = RM20101.CUSTNMBR
LEFT OUTER JOIN [View AR Applied Detail]
ON RM20101.DOCNUMBR = [View AR Applied Detail].Document_Number
WHERE
RM20101.RMDTYPAL = 9
AND RM20101.DOCDATE >= @DOCDATE
AND RM20101.DOCDATE <= @DOCDATE2
AND RM00101.SLPRSNID >= @SLPRSNID
AND RM00101.SLPRSNID <= @SLPRSNID2
