Skip to Content
1
Oct 21, 2020 at 01:59 AM

Remove all Transaction Cancel in OINM when create a report Inventory Posting

98 Views Last edit Oct 21, 2020 at 02:49 PM 2 rev

Hi all,

I have a problem when create a report Inventory Posting. I want to remove all transaction cancel when using OINM table. Almost transaction such as Purchase Order, Delivery, GRPO, Return, A/P Invoice, A/R Invoice, ... when I canceled these transaction in OINM have 2 rows and I want to hide all in report. Other case in Inventory Transaction when I canceled this transaction in OINM have 4 rows and I want to hide all in report.

If you have any idea of this problem. Please help me.

Thanks for your help

Best Regards,

Huy Vu

SELECT 
T0.ItemCode, T0.ItemName, T0.FrgnName,T0.InvntryUom, 
 isnull(SUM(InQty-OutQty),0) As BegQty, (sum(isnull(T1.TransValue,0))) As BegAmt,-- chinh + - tren layout
0 As InQty, 0 As InAmt, 0 As OutQty, 0 As OutAmt,
T1.Warehouse, T2.WhsName, 
T0.CodeBars, T1.TransType,t1.BASE_REF, T1.DocDate

FROM OITM T0 with(nolock)
INNER JOIN OINM T1 with(nolock) ON T0.ItemCode = T1.ItemCode 
INNER JOIN OWHS T2 with(nolock) ON T1.Warehouse = T2.WhsCode 
left JOIN OLCT T3 with(nolock) ON T1.Location = T3.Code<br>