VJadhav20180208120828 asked a question.
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
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
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
@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
@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