Hi Community,
I am trying to get a report of Purchase invoices(all PIs which are not posted and having open/released status). I need this report to include total amount for all invoices also. How can I get this report.
I need this report to do accrual at the month end with the total amount against these purchase invoices. Please suggest me.
thanks
Khushwant
*This post is locked for comments
Hi Khushwant,
I wrote some quick SQL for you to run to get you this information. You can execute the following query using Visual Studio, SQL Management Studio, use Excel Power Query or paste it into Microsoft Query (behind Excel), or whatever program you have that you can use to run queries.
Note: You need to replace the XXXXXXXXXXXXX with the actual prefix of your table names (unique to your database installation):
SELECT
CONVERT(VARCHAR(10),[PH].[Posting Date],110) AS [Posting Date],
[PH].[No_],
[PH].[Pay-to Vendor No_] AS [Vendor #],
[PL].[Line No_],
[PL].[Description],
CAST([PL].[Quantity] AS DECIMAL(18,2)) AS [Quantity],
CAST([PL].[Unit Cost] AS DECIMAL(18,2)) AS [Unit Cost],
CAST([PL].[Direct Unit Cost] AS DECIMAL(18,2)) AS [Direct Unit Cost],
CAST([PL].[Line Amount] AS DECIMAL(18,2)) AS [Line Amount],
CAST([PL].[Line Discount Amount] AS DECIMAL(18,2)) AS [Line Discount Amount],
CAST([PL].[Amount] AS DECIMAL(18,2)) AS [Amount],
CAST([PL].[Amount Including VAT] AS DECIMAL(18,2)) AS [Amount Incl Tax],
[PH].[Vendor Invoice No_]
FROM [XXXXXXXXXXXXX$Purchase Header] [PH]
LEFT JOIN [XXXXXXXXXXXXX$Purchase Line] [PL]
ON [PH].[No_] = [PL].[Document No_]
WHERE
[PL].[Document Type] = 2 -- Header/Line DOCUMENT TYPE: INVOICE
AND ([PH].[Status] = 0 OR [PH].[Status] = 1) -- Header STATUS: OPEN or RELEASED
ORDER BY [PH].[Posting Date] ASC, [PH].[No_] ASC
The Reconcile AP to GL report will give you the Accrual entries for PO's where goods have been received but the vendor invoice has not.
If you have the developer license you can go to design view and add the fields, if you do not have developer license then talk to your Dynamics NAV partner and request them to add the fields for you.
Hi,
You need to open the NAV in the designer and open the above page and add those two columns.
Hi Mahesh,
thanks for your response. "Amount" and "Amount including VAT" columns are not available in our "Choose Column" box. So what is the procedure to add any specific column in the box. Is this to be done by our System provider or we as a user can do it?
thanks
Khushwant
Rather than developing a report what you can do is use the Purchase Invoice list page. With the List page you can export all the details to excel or word with a click of a button.
- To fulfil your requirement you need to add Amount and Amount Including VAT column to the Purchase List (9308) page.
Hi,
If you are talking about Purchase Invoices, you may add in the "Amount" and "Amount Including VAT" columns in the Purchase Invoices List page (object id : 9308), which will serve your above purpose.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
André Arnaud de Cal... 291,134 Super User 2024 Season 2
Martin Dráb 229,928 Most Valuable Professional
nmaenpaa 101,156