We have NAV 2009 R2 recently installed and our problem is that we can't find the way to identifie the purchase invoice line related with its purchase receipt line as we can do with sales lines.
We need to control, for audit purposes, in the opposite sense too, the source of any purchase invoice line.
Thanks in advance
*This post is locked for comments
I experienced the same issue some time ago.
I did the query that the Line | Item Receipt Line do.
It use very complex way to find the Receipt Line, using the Value Entry. What a very complex way, isn't it?
Unfortunately, since it uses Value Entry as the compass, it should only work just for ITEM.
But, how about G/L Account, etc?
I then find a little bit workaround here. I did a little customisation:
Add a new field Purch. Receipt No. and Purch. Receipt Line No., and add a code to fill these fields with those standard fields "Receipt No." and "Receipt Line No.".
Add these new 2 fields into the Purch. Inv. Line table too.
So, once you Get the Receipt Lines, these new fields are populated, and when you post the Invoice, you could pull that information later :)
From now on, leave that complicated Value Entry query ;)
I have tried it, and it works like a charms! Give it a try!
Hi,
This query works and get the related receipt line from an invoice line, but it seems that only works with lines type=2, which triggered the item ledger entry and value entry table's line, and what about the rest? Do you know any other structure link for those types?
Really, I couldn’t believe such difficulties and workaround just to get this basic information, in my opinion with easy design solutions like adding two columns (document no_ and line no_) to either Purchase Receipt or Invoice line table, or maybe I’m not aware of the problem.
Best regards,
Abedul53
Hi,
NAV 2009 Standard Structure (and 2013)
ON "Purch. Rcpt. Line"
"Item Rcpt. Entry No." > link to item ledger entry no. ex Entry No 323
ON "Item ledger entry"
Entry NO. > linked to Purch Rcpt Line ex Entey No 323
Document Type > Purchase Receipt
Entry Type > Purchase
Source No "10000"
Document No 107031
Document Line No. "20000" > purchase receipt line > lookup for header No.
ON "Value entry"
item ledger entry No > "Linked to item ledger entry No." (order line no.)
Purchase Invoice Number e line
> Document Type "Purchase Invoice"
> Document Line No. "line Number ex 20000"
Purchase Invoice Number e line
> Document Type "Purchase Invoice"
> Document No. > posted pruch invoice no.
> Document Line No. "line Number ex 20000" > lookup for posted pruch inv. header No.
SQL VIEW (with same structure)
You can add this view using NAV Linked Tables
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))
Roberto Thanks for your post.
We are talking about posted documents and we post invoices with 'Get receipts' function.
I don't know what do you mean with 'some issues from network: Relationship between Purchase Invoices and purchase Orders'. Can you explain it a little bit more?
About the Description field of the first invoice line, the content we have varys depend on document type and series-number series we use for each one, so we need to apply different search patterns to extract this information. In any case, we'll get only the receipt number, not the line number, and we continue without identifie the source, don´t we?
In the opposite way, kown a receipt line with its quantity not invoiced=0: how we can get its invoice line?
Best regards,
Abedul53
Hi,
if you are talking about "not posted documents"
on "not posted" purchase invoice lines when you do "Get Receipts function", the Receipt number & Receipt line number are on the Invoice Line ("Receipt No." & "Receipt Line No." table 39 fields), if you don't have this fields on form\page you can add (need development you don' have in standard form\pages)
if you are talking about "posted documents" and missing links, some issues from network:
"Relationship between Post Purchase Invoices and Purchase Orders".
if you post purchase invoice by using function "Get Receipt Lines", only one alternative solution, in first posted purchase invoice line description field, you'll get posted purchase receipt no., then from posted purchase receipt you'll Purchase order no (PO No.)
When you do Get Receipts, the Receipt number is on the Invoice Line. You can retrieve the Receipt Header from there, which in turn should have a reference to the original order in the "Order No." field.
Open the Purchase Order form, click on the "Order" button and then click "Invoices". This opens the purchase invoice list populated with all related posted invoices, and they should all be there. In the RTC that is under "Related Information".
Here is the relationship in SQL format:
SELECT pil.[Document No_]
,LEFT(RIGHT(pil.Description, 9), 8) gr
,prh.[Order No_] PO
FROM [Purch_ Inv_ Line] pil
INNER JOIN [Purch_ Rcpt_ Header] prh ON prh.No_ = LEFT(RIGHT(pil.Description, 9), 8)
WHERE pil.Description LIKE 'Receipt No. GR%'
#1
André Arnaud de Cal...
292,516
Super User 2025 Season 1
#2
Martin Dráb
231,432
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (
