Sales Person Commission Payout Report
Good Day Mentors,
I am currently working on a report that ought to compute for Sales Agent commission.
Sources for gross sales computation is A/R Invoice(OINV) and A/R Credit Memo (ORIN).
Client also wants to exclude the VAT from the computation of gross sales as well as all credit memos issued for a specific period in which the commission of the agent will be based on.
DocTotal in both OINV and ORIN already have the VAT(VatSumSy) included in them upon commit into the databse. So i subtracted it from the DocTotal. The formula I came up with
OINV.DocTotal - OINV.VatSumSy - ORIN.DocTotal - ORIN.VatSumSy
Now, the main source of the problem is the part in which the report ought to have a date range facility. Since the commissions for the sales people will be computed every month.
Edited by: Sean Yu on Apr 4, 2009 9:46 AM
Simon Hill replied
Hi, I am not an expert, but I think your problem is because you have WHERE clauses that include fields on your ORIN file, therefore it will only extract customers where they have credits. You could imbed the link to credits (and invoices) as sub-reports... Similar example...
SELECT T0.[GroupCode], T0.CardCode,
(SELECT Count(T1.DocNum) FROM [ORIN] T1 WHERE T1.[CardCode]=T0.[CardCode]),
(SELECT Count(T2.DocNum) FROM [OINV] T2 WHERE T2.[CardCode]=T0.[CardCode])
FROM OCRD T0
WHERE T0.[GroupCode] IS NOT NULL
This will list all customer regardless of whether they have any credits and/or invoices...