Linkage between purchase order detail lines and license

[DFlynn]

I am attempting to write a SQL query against the Compliance db to assist our Operations Team.  As a result, I need to "process" the Purchase Order Detail record(s) listed within a given license on the Purchases Tab.  Unfortunately, I have not been able to determine how these records are linked/associated while reviewing the schema documentation.  Is anyone aware of this linkage?  

[mgunnels]

Good Afternoon

Is the objective to provide a report  of purchases to a license.

[DFlynn]

Hello Mr. Gunnels 🙂

Their objective is the find the latest expiry date from all po detail lines within the license that would then be copied to a custom field on the license that they could then utilize in reports/views.

[dhoagland1 · Accepted solution]

The linking table you're looking for is EntitlementTransaction.

You can find more info in the Schema Reference here: https://docs.flexera.com/FlexNetManagerSuite2023R1/EN/Schema/index.html#SysRef/schema/ComplianceSchema/Compliance.Logic.LicensingTables/reference/EntitlementTransaction.html#reference_FNMP_EntitlementTransaction
As a quick example, the below query would get you the license id, license name and the expiration date of any processed PO's. If you need something more specific, post back and I can work some SQL magic.

select sl.SoftwareLicenseID, sl.name as LicenseName,
pod.ExpiryDate
from entitlementtransaction et join softwarelicense sl
on et.SoftwareLicenseID = sl.SoftwareLicenseID join PurchaseOrderDetail pod
on et.PurchaseOrderDetailID = pod.PurchaseOrderDetailID

[DFlynn]

This is brilliant.  Thank you so very much.  I was able to utilize this as a starting point for a Business Adapter that is doing exactly what our team needed.