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
‎Jun 09, 2020 03:49 PM
‎Jun 10, 2020 09:24 AM
@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
‎Jun 10, 2020 09:58 AM
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
‎Jun 10, 2020 09:08 AM
‎Jun 10, 2020 09:24 AM
@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
‎Jun 10, 2020 09:58 AM
Thank you both for responses. It would help!
Regards
Vinod
‎Jun 10, 2020 10:13 AM