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

Query for extracting PurchaseOrderNo and associated license count

Hello,

Please pardon my lack of knowledge in the FNMS data model.

I want to extract the PO Number and associated number of contracts with it. Can you please help with the query? I can locate PurchaseOrder table but not able to find link between Purchase --> License for taking a count. We have recently migrated to OnPremise setup, hence not yet exposed to data model.

Thanks

Vinod

 

 

(2) Solutions
Hello @vinod_jadhav,

Is the output you've posted there from a custom report?

If so, you can check the ComplianceSavedSearch_MT table in the Compliance DB. This contains data concerning custom views or reports.

You will note a column 'SearchSQL' - this contains the backend SQL queries that are being executed when a report is run.

This should give you an idea of the tables involved in the generation of the report data.

HTH,
Joseph
If my response answered your question satisfactorily, please click "ACCEPT AS SOLUTION" to heighten visibility for future customers!

View solution in original post

@vinod_jadhav - Your SQL Query needs to be a join across several views:

You need to go from PurchaseOrder to PODetail, to SoftwareLicensePODetail to SoftwareLicense:

PurchaseOrder  (the header of a PO, containing PO Number, Date and Vendor)

PurchaseOrderDetail Join to the Purchase Order view on the PurchaseOrderID column

SoftwareLicensePurchaseOrderDetailInfo Join to PurchaseOrderDetail viewon the PurchaseOrderDetailID column

SoftwareLicense Join to SoftwareLicensePurchaseOrderDetailInfo on the SoftwareLicenseID column

 

 

View solution in original post

(4) Replies

Hello,

 

Just to avoid confusion: I want Purchase Order No and Associated License Count.

FNMS Report output is as below:

Purchase - Purchase order no. Purchase - Licenses (number)
40305399                                                 1
40305399                                                 1
40305399                                                 1
40305399                                                 1

 

I want this output from the database.

Which tables can give me this info? I could not find link between Purchase Order table and License directly.

Thanks

Vinod 

 

Hello @vinod_jadhav,

Is the output you've posted there from a custom report?

If so, you can check the ComplianceSavedSearch_MT table in the Compliance DB. This contains data concerning custom views or reports.

You will note a column 'SearchSQL' - this contains the backend SQL queries that are being executed when a report is run.

This should give you an idea of the tables involved in the generation of the report data.

HTH,
Joseph
If my response answered your question satisfactorily, please click "ACCEPT AS SOLUTION" to heighten visibility for future customers!

@vinod_jadhav - Your SQL Query needs to be a join across several views:

You need to go from PurchaseOrder to PODetail, to SoftwareLicensePODetail to SoftwareLicense:

PurchaseOrder  (the header of a PO, containing PO Number, Date and Vendor)

PurchaseOrderDetail Join to the Purchase Order view on the PurchaseOrderID column

SoftwareLicensePurchaseOrderDetailInfo Join to PurchaseOrderDetail viewon the PurchaseOrderDetailID column

SoftwareLicense Join to SoftwareLicensePurchaseOrderDetailInfo on the SoftwareLicenseID column

 

 

Thank you both for responses. It would help!

 

Regards

Vinod