Determining Month to Date Sales in Dynamics GP 10 with Project Accounting
This was a tough one. I needed to find month to date sales in GP compiling Sales Orders and entries in Project Accounting. To complicated things a little more, I needed to use the Invoicing amount in Sales Orders if they’ve been fully invoiced and the Invoiced amount from Project Accounting when it is more than the total Fee Schedule.
The SQL gets a little messy so I’ve tried to add some comments to clarify exactly what I’m trying to do. The basic idea is that I’m getting the total sales order numbers and performing a UNION with the total Project Accounting numbers then summing them with an outer query. You can modify this to include previous months by changing any references to a date field from “DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)” (meaning, the first day of the current month) to “DATEADD(mm, DATEDIFF(mm,0,getdate()) -1, 0)” (the first day of the previous month) or can change that -1 to a -2 to look 2 months back.
There are likely many better ways to get this done, but this has seemed to work well so far.
Let me know if you have any questions or comments.
SELECT SUM("total") as "MTD Sales"
FROM (
SELECT myMonth, myMonthNUm, SUM("Totals") as total
FROM
-- Sales Orders Entered this month
(
SELECT
-- Name of Month
DateName(month,CASE WHEN Sales."CREATED DATE" < Sales."Order Date"
THEN Sales."Created Date"
ELSE Sales."Order Date"
END) as myMonth,
-- Number of Month
month(CASE WHEN Sales."CREATED DATE" < Sales."Order Date"
THEN Sales."Created Date"
ELSE Sales."Order Date"
END
) as myMonthNum,
-- Here, we look to see if the SO has been invoiced. If it has, we use the invoice amount
-- otherwise, we use the subtotal.
CASE WHEN Sales."Sales Document Status" != 'Complete'
THEN (Sales."Subtotal" - Sales."Trade Discount Amount")
ELSE (SUM(ISNULL(INV1."Subtotal",0)) + SUM(ISNULL(WORK."Subtotal",0)) - SUM(ISNULL(INV1."TRDISAMT",0)) )
END
AS Totals
FROM dbo.SalesTransactions Sales
LEFT JOIN dbo.SOP30200 SO
ON SALES."SOP Number" = SO."SOPNUMBE"
LEFT JOIN dbo.SOP10100 WORK
ON SALES."SOP Number" = WORK."ORIGNUMB"
LEFT JOIN dbo.SOP30200 INV1
ON SO."SOPNUMBE" = INV1."ORIGNUMB"
-- Look at orders made this month using the earlier date between created and order date
WHERE
CASE WHEN Sales."CREATED DATE" < Sales."Order Date"
THEN Sales."Created Date"
ELSE Sales."Order Date"
END < DATEADD(mm, DATEDIFF(mm,0,getdate()) + 1, 0)
AND CASE WHEN Sales."Created Date" < Sales."Order Date"
THEN Sales."Created Date"
ELSE Sales."Order Date"
END
>= DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)
AND Sales."SOP Type" = 'Order'
AND (INV1."BCHSOURC" IS NULL OR INV1."BCHSOURC" = 'Sales Entry')
GROUP BY Sales."SOP Number", Sales."Subtotal", Sales."Customer Name", Sales."Sales Document Status", Sales."Trade Discount Amount",
CASE WHEN Sales."CREATED DATE" < Sales."Order Date"
THEN Sales."Created Date"
ELSE Sales."Order Date"
END
) as T
GROUP BY myMonth,mymonthnum
-- Now we'll include contracts entered in project accounting
UNION
(
SELECT Datename(month,(T."SuperDate")) as myMonth, month(T."SuperDate") as myMonthNum,
-- Just as in Sales Orders, we'll use the billed amount if it is higher than the original contract amount
SUM(CASE
WHEN T."Total" > ISNULL(B.Billing,0)
THEN "Total"
ELSE ISNULL(B.Billing,0)
END
)
as Totals
FROM
( -- Here, we're grabbing the "as sold" amounts from the contract fee schedule for each contract line item
SELECT
Glue."Contract ID",
MIN(SuperGlue."PA Forecast Begin Date") as SuperDate,
SUM(Fee."PAFeeAmount") as Total
FROM dbo.PA02101 Fee
LEFT JOIN dbo.PAProjects Glue
ON Fee."PAPROJNUMBER" = Glue."Project Number"
LEFT JOIN dbo.PAContracts SuperGlue
ON Glue."Contract ID" = SuperGlue."Contract ID"
WHERE "PA Forecast Begin Date" >= DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)
AND "PA Forecast Begin Date" < DATEADD(mm, DATEDIFF(mm,0,getdate()) + 1, 0)
GROUP BY Glue."Contract ID"
) as T
LEFT JOIN
( -- Here, we're looking at our total billed amount
SELECT "PACONTNUMBER", SUM("PABillingAmount") as Billing
FROM dbo.PA11500
GROUP BY "PACONTNUMBER"
) as B
ON T."Contract ID" = B."PACONTNUMBER"
GROUP BY Datename(month,(T."SuperDate")), month(T."SuperDate")
)
) as P
GROUP BY myMonth, myMonthNum
ORDER BY myMonthNum