You’re offline. This is a read only version of the page.
How to link link Purch_ Inv_ Line and Purch_ Rcpt_line navision ?
*This post is locked for comments
Jaweher89,
Here's how you do it in T-SQL at the FROM statement:
Note: I pulled out my company name, which prefixes the table names, and replaced it with 'xxx', so you'll need to insert your company name as prefix to the table names.
Share
Report
All responses (
Answers (
FROM
[xxx$Purch_ Inv_ Header] AS [PIH]
INNER JOIN [xxx$Purch_ Inv_ Line] AS [PIL]
ON [PIL].[Document No_] = [PIH].[No_]
LEFT JOIN [xxx$Purch_ Rcpt_ Line] AS [PRL]
ON [PIH].[Order No_] = [PRL].[Order No_]
AND [PIL].[Line No_] = [PRL].[Order Line No_]
AND [PIL].[No_] = [PRL].[No_]
AND ISNULL([PIL].[Revision No_], '') = ISNULL([PRL].[Revision No_], '')
In your WHERE statement you'll also need some appropriate filters:
WHERE [PIL].[Type] = 2 -- Line TYPE: ITEM
AND [PIL].[Quantity] > 0 -- Filter out blank quantity items on Purch Invoices
AND ([PRL0].[Quantity] > 0 OR [PRL0].[Quantity] IS NULL)
This approach will handle multiple deliveries that came in for the same Purchase Order line item, however remember that if you're basing your query only on the Purchase Invoice tables then only Purchase Order lines that have been received AND invoiced will show on your report.
To make a query like this that includes the live outstanding Purchase Order lines along with the completed and fully received Purchase Order lines requires a bit more complicated SQL. Queries related to Purchase Orders, Purchase Order Archives, Purchase Receipts, and Purchase Invoices can get tricky with NAV depending on what you're trying to do because NAV deletes the original Purchase Order once it has been fully received (with a copy in the Archive that doesn't have all the same fields as the original PO).
Regards,
You can derive the relation from Item Ledger Entry & Value Entry Table.
Filter the Value Entry Table by Purchase Invoice No. > Fetch the Item Ledger Entry No. > Get the ILE > Get the Purchase Receipt No.
Can you suggest your exact requirement ?
.
There is no direct one-to-one relationships between those, as you mighht have several partial receipts which might have one total invoice posted. As mentioned by members earlier, you can try to trace them through initial Order No., but not in all cases. Also, if you operate with items, you can try to trace it through Item Ledger Entry and Value Entry tables, by item entry nos.
Hi,
Just to add on that -
1. The approach provided above will work on following cases -
> If you receipt and Invoice from Purchase Order.
> If you receipt via warehouse and Invoice from Purchase Order.
2. It will not work if -
> You receive from Warehouse and Invoice using Purchase Invoice and Get Receipt Lines
If your customer does not use warehouse or Invoice and Order Creation is done by same person in your customer organization then Order No will work.
But if there are different roles like below-
1. Create Purchase Order By One Person.
2. Receipt Product By Another Person in Warehouse.
3. Create Purchase Invoice By Another person.
Then Order No. concept does not work.
From Purchase Invoice line you can go to Purchase Invoice Header - There's a field on Purchase Invoice Header named Order No from this you can filter to Purchase Receipt Header.Order No and then from Purchase Recipt header No to Purchase Receipt Line
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,253
Super User 2024 Season 2
#2
Martin Dráb
230,188
Most Valuable Professional
#3
nmaenpaa
101,156