cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

Foreign Key for License and Purchases

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?

(1) Solution
mfranz
By Level 17 Champion
Level 17 Champion

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

View solution in original post

(2) Replies
mfranz
By Level 17 Champion
Level 17 Champion

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

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