Purchase Invoice report

(0) Share

Report

Posted on by Community Member Microsoft Employee

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

All responses (7)

Answers (5)

Verified answer

Alex A 2,348 on at

Like (0)

Report

RE: Purchase Invoice report

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
Community Member Microsoft Employee on at

Like (0)

Report

RE: Purchase Invoice report

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.
Verified answer

TharangaC 23,116 on at

Like (0)

Report

RE: Purchase Invoice report

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.
Verified answer

mmv 11,465 on at

Like (0)

Report

RE: Purchase Invoice report

Hi,

You need to open the NAV in the designer and open the above page and add those two columns.
Community Member Microsoft Employee on at

Like (1)

Report

RE: Purchase Invoice report

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
Verified answer

TharangaC 23,116 on at

Like (0)

Report

RE: Purchase Invoice report

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.
Verified answer

mmv 11,465 on at

Like (0)

Report

RE: Purchase Invoice report

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.