Query Purchasing Report per Project

[Former Member]

Dear all,

I want to make query that can give me information about purchasing report per project. The data should come from AP invoice and AP credit memo (item and service type). I use project code field in the form to filter data.

Thanks before for your kind help

[Former Member · Accepted solution]

haiiii allll pls help...thankss

[Former Member · Accepted solution]

Try this one:

SELECT Distinct T0.CardCode,T0.CardName,T0.DocNum 'Invoice#', T0.DocDate 'Invoice Date', T0.DocTotal 'Invoice Total', T0.NumAtCard,T3.DocNum 'Credit Memo#', T3.DocDate 'Credit Date', T3.DocTotal 'Credit Total'

FROM dbo.OPCH T0

INNER JOIN dbo.PCH1 T1 ON T1.DocEntry = T0.DocEntry

LEFT JOIN dbo.RPC1 T2 ON T2.BaseEntry = T1.DocEntry

LEFT JOIN dbo.ORPC T3 ON T3.DocEntry = T2.DocEntry

WHERE T0.Project = '[%0\]'

[Former Member · Accepted solution]

Dear Gordon,

thanks for the query but the information that I need is purchasing report per item not per vendor.

Pls can you modified the query ?

Thanks for your help

[Former Member · Accepted solution]

Hai!

Try this

Declare @project as nvarchar(30)
set @Project = (select max(S0.PrjCode) from OPRJ S0 where S0.PrjCode='[%0]')
Select
a.Itemcode,
max(a.Name) Name,
sum(a.quantity) Quantity,
sum(a.price) Price,
sum(a.linetotal) Line_Total from (
SELECT 
T1.ItemCode,
max(T1.Dscription) Name,
sum(T1.Quantity) Quantity,
sum(T1.Price) Price,
sum(T1.LineTotal) LineTotal
From
PCH1 T1,OPRJ P0
where
T1.Project=P0.PrjCode and
T1.Project = @Project
Group By
T1.ItemCode
UNION All
SELECT 
T1.ItemCode,
max(T1.Dscription) Name,
sum(T1.Quantity) Quantity,
sum(T1.Price) Price,
sum(T1.LineTotal) Line_Total
From
RPC1 T1,OPRJ P0
where
T1.Project=P0.PrjCode and
T1.Project = @Project
Group By
T1.ItemCode) a
Group By
a.ItemCode

Regards,

Thanga Raj.K

[Former Member · Accepted solution]

Dear Thanga

Thanks a lot...your query has solved the problem

[Former Member]

Try this one:

SELECT "Invoice" AS 'TYPE',T0.CardCode,T0.CardName,T0.DocNum, T0.DocDate, T0.DocTotal, T0.NumAtCard

FROM dbo.OINV T0 WHERE T0.Project = '[%0\]'

UNION ALL

SELECT "Credit Memo",T0.CardCode,T0.CardName,T0.DocNum, T0.DocDate, -T0.DocTotal, T0.NumAtCard

FROM dbo.ORIN T0 WHERE T0.Project = '[%0\]'

Thanks,

Gordon

[Former Member]

Dear Gordon,

Thanks for your reply, I tried that and the result is if one item (example item 010010) has transaction receiving and credit memo, the report will show in two line. Is it possible to group the transaction in to one, so the report will show one item code that summarize receiving and credit memo transaction.

Thanks

[Former Member]

hai...is there any one out there that can help me about this ? thanks a lot for your help

[Former Member]

You can use Financials -> Financial Reports ->Accounting ->Transaction Report by Projects .

or

Financials -> Financial Reports ->Accounting ->Document Journal.

Click on 'Expanded' button and select the relevant 'Original Journal' and 'Project'

[Former Member]

Dear Sridhran,

The information I need is the list of purchase (item and service) that I ever bought for one project. Not the list of transaction. I want to create Purchase Report per project