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