Author Archive

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

Bill of Materials and Quantities in Dynamics GP 10

Here’s a SQL query that will allow you to see what components go into a given finished part number along with the available, on hand, allocated, and back ordered quantities.

This is useful if you have a rush job with not enough quantity available in GP and want to see what’s actually in house.

Just define @needed as how many items you’re trying to manufacture and set @item to the finished part number. You’ll also need to enter in your own location code in the left join for ItemQuantities. The “Assembly” column will let you know if the component is itself an assembly. When I make a report out of this in Business Intelligence Development Studio, I use this column to show or hide a symbol to navigate to this report with the part number of the component (pretty much “scoping in” to that part) to see if the material is available to make that.
DECLARE @needed as INT
DECLARE @item as char(20)

SET @needed = 1
SET @item = '115-0001'

SELECT
Item."Item Description",
Bill."FINISHED GOOD ITEM NUMBER",
Bill."Component",
(Bill."QTY" * @needed) as "QTY",
Item."QTY Available",
Item."QTY On Order",
Item."QTY On Hand",
Item."QTY Allocated",
CASE
WHEN Sub.SubParts IS NOT NULL
THEN 1
ELSE 0
END as "Assembly"
FROM dbo.BillofMaterials as Bill
LEFT JOIN dbo.ItemQuantities as Item ON Bill."Component" = Item."Item Number" AND Item."Location Code" = 'YOUR LOCATION CODE'
LEFT JOIN
(
SELECT "Finished Good Item Number", COUNT(*) as SubParts FROM dbo.BillofMaterials
GROUP BY "Finished Good Item Number") as Sub
ON
Sub."Finished Good Item Number" = Bill."Component"

WHERE Bill."FINISHED GOOD ITEM NUMBER" = @item

Looking at Manufacturing Orders Associated with Sales Order Lines in Dynamics GP 10

Here’s a query that will allow you to see which Open SO Lines have MOs associated with them and which MO exactly it is.

This is helpful when you’re looking to find “lost orders” that haven’t yet been processed in Manufacturing.

SELECT 
MO."MANUFACTUREORDER_I",
Sales."SOP Number",
Sales."Item Number",
Sales."Order Date",
Sales."QTY",
Sales."QTY Remaining",
Sales."Customer Name",
Sales."Item Description",
Sales."Due Date"
FROM dbo.SalesLineItems Sales
LEFT JOIN dbo.SOP10202ON Sales."SOP Number" = dbo.SOP10202.SOPNUMBEAND Sales."Line Item Sequence" = dbo.SOP10202.LNITMSEQ
LEFT JOIN dbo.IS010001 As MOON Sales."SOP Number" = MO.SOPNUMBEAND Sales."Line Item Sequence" = MO.LNITMSEQAND Sales."Item Number" = MO.ITEMNMBR
WHERE Sales."SOP Type" = 'Order' AND Sales."Sales Document Status" != 'Complete'AND Sales."QTY Remaining" > 0
ORDER BY "SOP Number" ASC

Facebook Developer Subscriptions – Part 1

So Facebook has an API.  It’s pretty full featured although the documentation seems to be a little on the weak side.

After a little glancing, I thought “I can do this” and followed the sample php code to create a “Login” page for my test site.  I then spent all kinds of time and notebook paper coming up with an elaborate series of cron jobs to use the Access Token to make requests on the user’s behalf so I can periodically check for updates while they’re away.

Then I read the manual.

Facebook offers a subscription service via Real-Time Updates where you can request to be notified of certain changes as they happen.  Facebook doesn’t actually tell you what the changes are, but they notify you that a certain type of change has occurred (new friends, activities, etc.) Cool.

Great.  So all I need to do is get a user authenticated to my application using some example code from the php SDK, let Facebook know what kinds of updates I’m interested in, and set up an endpoint to get that data.  Easy enough, right? Ha.

So the way the Facebook API works is that you request a user’s data using their Access Token (that the developer gets when the user allows a certain application access).  The main problem with using this vanilla approach with subscriptions is that the access token given when using the example code in the php SDK expires after a little while.  Well, I want to get user updates all the time so I need a token that doesn’t expire.  This requires using Extended Permissions.  A quick look through the list tells me I need the “offline_access” permission.

Unfortunately, this is where I realized that the example code in the php SDK starts to lack.  The login URL built into the SDK $facebook->getLoginURL() doesn’t allow you add your requested permission in.  We’ll need to take a few extra steps to get what we’re looking for.

First, we need to construct our own login URL. The format will look something like this:

https://graph.facebook.com/oauth/authorize/client_id={your application ID}&redirect_URL={where to send the user after they do OR don’t authenticate}&scope=offline_access

Using this in place of the function call will cause the user to see something like this when they click on “Login”:

Request for Permissions

When your user clicks “Allow”, they will be redirected back to your redirect_url with an additional parameter called “code”.  Our neverending access token! Well, almost.  Now we need to make another request, server side time, to Facebook to turn this code into an actual access token.  This is done by requesting from a URL like this:

https://graph.facebook.com/oauth/access_token/client_id={your application ID}&redirect_URL={where to send the user after they do OR don’t authenticate}&client_secret={your secret code made when setting up developer account}&code={the code you just got in your return URL from Facebook}

Getting this URL returns the following data:

access_token=178416385509226|bbf513b2bf0c667340e80b40-15123454|9-ha-O6KUwHVtY51PtNxUR_Fkz4

So you’re application will have to parse our your actual access token from the string, but that should be a good start.

Update: after playing with this for a little bit, I found that the facebook session will return the access_token with extended permissions to you when your user is redirected back to your page.  This means you can do something like access $facebook->session->[‘access_token’] and save that instead of having to use curl for that second URL.

Next up: setting up your subscriptions and endpoint.