You’re offline. This is a read only version of the page.
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.
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.
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,134
Super User 2024 Season 2
#2
Martin Dráb
229,928
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (