Skip to main content

Notifications

Microsoft Dynamics NAV (Archived)

Purchase Invoice report

Posted on by 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

  • Verified answer
    Alex A Profile Picture
    Alex A 2,348 on at
    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 Profile Picture
    Community Member Microsoft Employee on at
    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 Profile Picture
    TharangaC 23,116 on at
    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 Profile Picture
    mmv 11,465 on at
    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 Profile Picture
    Community Member Microsoft Employee on at
    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 Profile Picture
    TharangaC 23,116 on at
    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 Profile Picture
    mmv 11,465 on at
    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.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Community AMA December 12th

Join us as we continue to demystify the Dynamics 365 Contact Center

New! Quick response templatesâš¡

Save time with the new custom templates!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,134 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 229,928 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans