Hi all,
I am writing a query to match a forecast generated at the start of the month using the RDR1.ShipDate field as its essential data source.
I visualise the value of that forecast during the month moving from open RDR1 items to DLN1 items (with no target INV1 or OINV) to INV1 items. A snapshot of the total value should still match the forecast but have differing amounts sat in each area as the month progresses (with the ideal outcome being all value moved through to INV1 lines by end of month}
The purpose of the query is to "match" a forecast from a previous date [%2] by showing only value pertaining to items scheduled for delivery during that month [%0] to [%1]. NB. these aren't exactly calendar months we use as reporting periods here so hence the requirement for input fields to define query range.
I have the below code which is not working correctly. It appears to show only data from RDR1 lines.
SELECT
T3.[CardCode] AS CustomerCode,
T3.[CardName] AS CustomerName,
T3.[DocNum] AS DocNum,
'Order' AS DocType,
T0.[ItmsGrpNam] AS ItemGroup,
T2.[ShipDate] AS DeliveryDate,
((T2.[OpenQty]*T2.[Price]) / T3.[DocRate]) AS OpenSum
FROM OITB T0
INNER JOIN OITM T1 ON T0.ItmsGrpCod = T1.ItmsGrpCod
INNER JOIN RDR1 T2 ON T1.ItemCode = T2.ItemCode
INNER JOIN ORDR T3 ON T2.DocEntry = T3.DocEntry
WHERE
T2.[ShipDate] >=[%0]
AND
T2.[ShipDate] <=[%1]
AND
T3.[DocStatus] = 'O'
AND
T3.[Canceled] = 'N'
AND
T3.[DocDate] <[%2]
UNION ALL
SELECT
T3.[CardCode] AS CustomerCode,
T3.[CardName] AS CustomerName,
T0.[DocNum] AS DocNum,
'DNI' AS DocType,
T4.[ItmsGrpNam] AS ItemGroup,
T1.[ShipDate] AS DeliveryDate,
((T1.[Quantity] * T1.[Price]) / T0.[DocRate]) AS OpenSum
FROM ODLN T0
INNER JOIN DLN1 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode
INNER JOIN OCRD T3 ON T1.BaseCard = T3.CardCode
INNER JOIN OITB T4 ON T2.ItmsGrpCod = T4.ItmsGrpCod
INNER JOIN RDR1 T5 ON T1.BaseRef = T5.DocEntry
INNER JOIN ORDR T6 ON T5.DocEntry = T6.DocEntry
WHERE
T1.[TrgetEntry] IS NULL
AND
T0.[DocDate] >=[%0]
AND
T0.[DocDate] <=[%1]
AND
T1.[ShipDate] >=[%0]
AND
T1.[ShipDate] <=[%1]
AND
T0.[Canceled] = 'N'
AND
T6.[DocDate] <[%2]
UNION ALL
SELECT
T2.[CardCode] AS CustomerCode,
T2.[CardName] AS CustomerName,
T0.[DocNum] AS DocNum,
'Invoice' AS DocType,
T4.[ItmsGrpNam] As ItemGroup,
T1.[ShipDate] AS DeliveryDate,
((T1.[Quantity] * T1.[Price]) / T0.[DocRate]) AS OpenSum
FROM OINV T0
INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN OCRD T2 ON T0.CardCode = T2.CardCode
INNER JOIN OITM T3 ON T1.ItemCode = T3.ItemCode
INNER JOIN OITB T4 ON T3.ItmsGrpCod = T4.ItmsGrpCod
INNER JOIN RDR1 T5 ON T1.BaseRef = T5.DocEntry
INNER JOIN ORDR T6 ON T5.DocEntry = T6.DocEntry
WHERE
T1.[DocDate] >=[%0]
AND
T1.[DocDate] <=[%1]
AND
T1.[ShipDate] >=[%0]
AND
T1.[ShipDate] <=[%1]
AND
T0.[Canceled] = 'N'
AND
T6.[DocDate] <[%2]
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.