Deliveries Not Invoiced Report

[Former Member]

Hi

Please help

I need to create a query that gives all open or partially open deliveries and their outstanding values for which no invoice had been received on a particular date. In B1 I can get a report showing current open delivery notes but I need the equivalent for an earlier date.

Thanks

Adrian Lauchlan

[former_member325312]

Hi Adrian

here is ur query delivery is raised but invoice has not done

SELECT ODLN.DocNum AS 'Delivery No ', CONVERT(varchar(11), ODLN.DocDate, 103) AS 'Delivery Date',

OINV.DocNum AS 'Invoice No', CONVERT(varchar(11), OINV.DocDate, 103) AS 'Invoice Date',

OINV.DocTotal

FROM

dbo.OINV AS OINV INNER JOIN dbo.INV1 AS INV1 ON OINV.DocEntry = INV1.DocEntry

RIGHT OUTER JOIN dbo.ODLN AS ODLN INNER JOIN dbo.DLN1 AS DLN1 ON

ODLN.DocEntry = DLN1.DocEntry ON INV1.BaseEntry = DLN1.DocEntry AND

INV1.BaseLine = DLN1.LineNum AND INV1.BaseType = DLN1.ObjType

WHERE OINV.DocNum is null

ORDER BY ODLN.DocNum ASC

Regards

Jenny

[Former Member]

Hi Jenny

Thanks for your reply however the problem with your solution is that it doesn't allow me to find out what deliveries were open on a particular day - say 31st Aug 2010 - if an invoice has been received since that day and entered then it won't show up

Any further thoughts?

Adrian

[Former Member]

As I said, it is going to be a tough query because you have to recreate the whole history from day 1 till the date you are looking for.

Using temporary table to try.

[Former Member]

Hi Adrian Lauchlan,

What is your definition for no invoice had been received on a particular date ? You may receive A/P invoice from Vendor but not A/R invoice. You send them to customer.

Thanks,

Gordon

[Former Member]

Hi Gordon

Sorry, didn't make myself clear. These are deliveries into the company for ites purchased

Adrian

[Former Member]

Those delivery called Goods Receipt PO. Please use standard term here. Otherwise, you cause confusion.

As for the date, do you mean any historical dates? It will be extremely difficult to get such report because system may only hold current info.

[Former Member]

Hi Gordon

The Auditors want a list of open GRPO's on specific dates. I have created the following query:

SELECT T0.[DocNum], T0.[DocStatus], T0.[TaxDate],T0.[DocDate], T0.[DocDueDate], T0.[CardCode], T0.[CardName], T0.[DocTotal], T0.[VatSum],T0.[DocTotal] - T0.[VatSum] as 'Net Total', T0.PaidtoDate FROM OPDN T0 WHERE T0.[TaxDate] <= '[%1]' and (T0.[UpdateDate] > '[%1]' or T0.DocStatus = 'O') ORDER BY T0.[TaxDate]

I can create another column to give the difference between the original total and the PaidtoDate total however this wouldn't identify if this happened after the date entered. When I look at the line data in the GRPO I can't see a date entry for entering the invoice. I suspect I will have to link to the Invoice and then look at the create date for each line

[Former Member]

This is going to be a tough query. Play with it first. When you find too difficult, post another tread.