Navision 2018 SQL pull

surbin004

I am seeking some help; not the best at sql by a long shot.
I had some help from a previous Macola ES programer that helped me get the base of our sql query started but is no longer able to help.
Currently, as this query excecutes it pulls the available date as 30 days from todays date. we would actually want that to be the expected receipt date from Nav purchase lines for the earliest date reported. additionally, i'd like to return the qty on that po line.

is this something that anyone on here would be able to help with or point me in the right direction.

how do i go about inserting the date and qty from the purchase lines?



SELECT 'MIBUSO', ix.[Cross-Reference Type No_] AS cus_no, i.[No_] AS item_no, max(ix.[Cross-Reference No_]) AS cus_item_no, (CASE WHEN sum(cast(COALESCE (qoh.qty_on_hand, 0.0)
- COALESCE (qoo.qty_on_salesorder, 0.0) AS decimal(14, 2))) < 10 THEN 0 ELSE sum(cast(COALESCE (qoh.qty_on_hand, 0.0) - COALESCE (qoo.qty_on_salesorder, 0.0) AS decimal(14, 2))) END) AS qty_on_hand,
max(i.[Base Unit of Measure]) AS uom, max(i.[GTIN]) AS upc_cd, (CASE WHEN sum(cast(COALESCE (qoh.qty_on_hand, 0.0) - COALESCE (qoo.qty_on_salesorder, 0.0) AS decimal(14, 2))) >= 10 THEN NULL
ELSE dateadd(d, 30, getdate()) END) AS available_date, '001' AS loc
FROM [MIBUSO$Item] i LEFT OUTER JOIN
(SELECT [Item No_] AS [No_], sum([Remaining Quantity]) AS qty_on_hand
FROM [MIBUSO$Item Ledger Entry]
WHERE [Location Code] = '01'
GROUP BY [Item No_]) qoh ON qoh.[No_] = i.[No_] LEFT OUTER JOIN
(SELECT [No_], sum([Outstanding Quantity]) AS qty_on_salesorder
FROM [MIBUSO$Sales Line]
WHERE [Location Code] = '01'
GROUP BY [No_]) qoo ON qoo.[No_] = i.[No_] INNER JOIN
[MIBUSO$Item Cross Reference] ix ON ix.[Item No_] = i.[No_] AND ix.[Cross-Reference Type] = 1
GROUP BY ix.[Cross-Reference Type No_], i.[No_]

thanks in advance!