Batch detail report Purchase against sale problem

84 Views

Hello

i want batch detail as follows

Batch Num , ItemCode, Pur Qty,Pur Price, Vendor Name, Sale Qty , Sale Price, Cust Code/Name Balance QTY

When i enter the Doc no of Purchase Invoice the result should shows as follows,(Items are Batchwise ) (I want to show Purchase Invoice against sale)

I done in crystal Report for that I take OPCH,PCH1,IBT1,INV1,OINV,IBT1_SALE

The Query i copy from Crystal is as follows, My result is as i want but only sale price can't display properly

SELECT "IBT1"."BatchNum", "IBT1"."DocDate", "IBT1"."ItemName", "IBT1"."Quantity", "PCH1"."Price", "IBT1"."ItemCode", "IBT1_SALE"."Direction", "IBT1_SALE"."Quantity", "IBT1"."CardName", "IBT1"."CardCode", "IBT1_SALE"."CardCode", "IBT1_SALE"."CardName", "OPCH"."DocNum", "OPCH"."NumAtCard"

FROM (("satyam"."dbo"."OPCH" "OPCH" INNER JOIN "satyam"."dbo"."PCH1" "PCH1" ON "OPCH"."DocEntry"="PCH1"."DocEntry") INNER JOIN "satyam"."dbo"."IBT1" "IBT1" ON (("PCH1"."ItemCode"="IBT1"."ItemCode") AND ("PCH1"."DocEntry"="IBT1"."BaseEntry")) AND ("PCH1"."LineNum"="IBT1"."BaseLinNum")) INNER JOIN "satyam"."dbo"."IBT1" "IBT1_SALE" ON ("IBT1"."BatchNum"="IBT1_SALE"."BatchNum") AND ("IBT1"."ItemCode"="IBT1_SALE"."ItemCode")

WHERE "OPCH"."DocNum"=541 AND "IBT1"."DocDate">={ts '2011-04-01 00:00:01'}

ORDER BY "IBT1"."BatchNum"

Suppose i enter Doc No 540 result show without attaching INV1.Pice in Sale price

Batch Num , ItemCode, Pur Qty,Pur Price, Vendor Name, Sale Qty , Sale Price, Cust Code/Name,Balance QTY

PU540-1 I01 05 100 ABCD 3 ZXCV 02

PU540-2 I02 10 120 ABCD - - 10

PU540-3 I03 08 100 ABCD - - 08

But When I attach INV1.Price in sale Price field then result Show,

Batch Num , ItemCode, Pur Qty,Pur Price, Vendor Name, Sale Qty , Sale Price, Cust Code/Name,Balance QTY

PU540-1 I01 05 100 ABCD 3 150 ZXCV 02

I create group by IBT1.BatchNum

But i want it should be Show as,

Batch Num , ItemCode, Pur Qty,Pur Price, Vendor Name, Sale Qty , Sale Price, Cust Code/Name,Balance QTY

PU540-1 I01 05 100 ABCD 3 150 ZXCV 02

PU540-2 I02 10 120 ABCD - - 10

PU540-3 I03 08 100 ABCD - - 08

For Cust Code i create formula as

if {IBT1_SALE.Direction}=1 then

{IBT1_SALE.CardCode}

ELSE

""

For sale Qty i create formula as,

If {IBT1_SALE.Direction}=1 then

{IBT1_SALE.Quantity}

And for Balance Qty formula are,

{IBT1.Quantity}-{@Saleqty}