Highlighted
Occasional contributor

Foreign Key for License and Purchases

Jump to solution

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

Accepted Solutions
Highlighted
Rising star

Re: Foreign Key for License and Purchases

Jump to 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

Softline Group is Europe's leading independent expert in Software Asset Management.

View solution in original post

2 Replies
Highlighted
Rising star

Re: Foreign Key for License and Purchases

Jump to 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

Softline Group is Europe's leading independent expert in Software Asset Management.

View solution in original post

Highlighted
Occasional contributor

Re: Foreign Key for License and Purchases

Jump to solution

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

0 Kudos