Foreign Key for License and Purchases

[Bdavey]

I have been creating custom SQL queries for our own reporting tools and I am having a hard time pulling the total purchase costs on a per license basis.

My question is what IDs and or Tables are being used in FNMS to link these tables (foreign keys for these tables) does anyone have experience here?

[mfranz · Accepted solution]

Hi,

This should get you started.

SELECT
	sl.Name LicenseName
	,SUM(pod.TotalPrice) LicenseValue
FROM SoftwareLicense sl
LEFT JOIN EntitlementTransaction et
	ON sl.SoftwareLicenseID = et.SoftwareLicenseID
LEFT JOIN PurchaseOrderDetail pod
	ON et.PurchaseOrderDetailID = pod.PurchaseOrderDetailID
GROUP BY sl.Name

There are a few more things to consider.

•  I am not sure if entitlements not linked via "Unprocessed Purchases" show up in the "EntitlementTransaction" table at all
• Multiple entitlements from a purchase can be linked to different licenses, so you'll have to get the actual "Adjustment" per transaction and multiply it by the "UnitPrice" from the purchase oder line (PurchaseOrderDetail)

Best regards,

Markward

[Bdavey]

This is what I was looking for, I can customize and incorporate this.  Thanks!