2 Replies Latest reply: Aug 4, 2011 10:49 AM by Tessa Cristobal RSS

how to extract transaction price in OINM

Tessa Cristobal
Currently Being Moderated

Hi experts!

 

We have a crystal report. One portion of the report is supposed to show the total inventory issued out. It shows the correct quantity, but picks up the current price of the item instead of the price used during the transaction. Can anyone please help me edit the script? I'm relatively new to scripting..

 

select CAST(MONTH(a.Docdate) AS varchar(2)) + '/' + CAST(YEAR(a.Docdate) AS varchar(4))as DATEGROUP,

a.Docdate,

a.transtype,

a.itemcode,

b.itmsgrpcod,

c.U_CMR_Code,

sum(a.inqty) as 'INQTY',

sum(a.outqty) as 'OUTQTY',

sum(a.outqty)-sum(a.inqty)as 'Qty Difference',

a.price,

c.cyclecode,

d.code

FROM OINM a

LEFT OUTER JOIN OITM b on a.itemcode=b.itemcode

LEFT OUTER JOIN OITB c on b.itmsgrpcod=c.itmsgrpcod

LEFT OUTER JOIN [dbo].[@CMR_CODE] d on c.u_cmr_code=d.code

where a.transtype in ('59','60')

GROUP BY a.Docdate, a.transtype, a.itemcode, a.price, b.itmsgrpcod,c.u_cmr_code,c.cyclecode,d.code

 

 

thanks!

 

Tessa

Actions