Linking Purchase Receipt to Purchase Invoice

headley27

I am trying to run a report showing items that were received by a specific date that have not been invoiced by a specific date. I then need to reconcile the value of this list against the balance of one of our GL accounts. This used to be fairly easy.

Here is my dilemma.... ](*,) ](*,) ](*,)

I am trying to run a custom report (outside of Navision via a set of SQL Server Queries). It worked perfectly in version 3.7 but not in 5.0.

In 3.7, I could simply filter by Date and GL Account Number, Sum the Credit Amount by Document Number, Sum the Debit Amount by Document Number, figure out the difference and anything not equal to zero would be reconciled against the balance of the GL account in question.

This still works for transactions that occurred prior to our upgrade to 5.0.

In 3.7, the Purchase Receipt Number would exist as the Document Number for both records.
But now (using 5.0), the Purchase Receipt Number is entered into the GL for the Credit Record and the Purchase Invoice Number is entered into the GL for the Debit Record. Therefore, summing by Document Number does not work unless I can distinctly link the Purchase Receipt Line to the Purchase Invoice Line.

Is there a way to do so?

I can't see a way to use any combination of system generated numbers to provide a distinct link (i.e. Item Ledger Entry, Value Entry, Item Application Entry, etc.).

I can use the 'Quantity Received Not Invoiced' field on the Purchase Receipt Line, but I still can't find a link back to the exact Purchase Invoice Line.

I can navigate using the Purchase Order Number (via the header) but this isn't foolproof (the purchase order can be duplicated, multiple receipts can be consolidated on one invoice, etc).

Can anyone provide a suggestion to make this work in 5.0?

Thank you very much.

headley27

It looks like this works...

SELECT DISTINCT [Purch_ Rcpt_ Line].[Document No_], [Purch_ Inv_ Line].[Document No_]
FROM (([Purch_ Inv_ Line] INNER JOIN [Value Entry] ON ([Value Entry].[Document Line No_] = [Purch_ Inv_ Line].[Line No_]) AND ([Purch_ Inv_ Line].[Document No_] = [Value Entry].[Document No_])) INNER JOIN [Item Ledger Entry] ON [Value Entry].[Item Ledger Entry No_] = [Item Ledger Entry].[Entry No_]) INNER JOIN [Purch_ Rcpt_ Line] ON ([Purch_ Rcpt_ Line].[Line No_] = [Item Ledger Entry].[Document Line No_]) AND ([Item Ledger Entry].[Document No_] = [Purch_ Rcpt_ Line].[Document No_])
WHERE ((([Value Entry].[Document Type])=6) AND (([Value Entry].[Invoiced Quantity])<>"0") AND (([Item Ledger Entry].[Document Type])=5));