Hello Experts,
I have wrote below Query for pending orders.
SELECT
(case when T1.[U_AMTUOM]='mtr' then T1.[U_TotalLength]
when T1.[U_AMTUOM]='nos' then T1.[U_Nos]
when T1.[U_AMTUOM]='kgs' then T1.[U_TotalWeight]
when T1.[U_AMTUOM]='Feet' then T1.[U_TotalLengthFt]
else T1.[Quantity] end) as 'Order Quantity',
(CASE When (T1.[Quantity]- T1.[OpenQty]) = 0 then (T1.[Quantity]- T1.[OpenQty]) else T4.[Quantity] END) as 'Dispatched QTY',
((case when T1.[U_AMTUOM]='mtr' then T1.[U_TotalLength]
when T1.[U_AMTUOM]='nos' then T1.[U_Nos]
when T1.[U_AMTUOM]='kgs' then T1.[U_TotalWeight]
when T1.[U_AMTUOM]='Feet' then T1.[U_TotalLengthFt] else T1.[Quantity] end) - (CASE When (T1.[Quantity]- T1.[OpenQty]) = 0 then (T1.[Quantity]- T1.[OpenQty])
else T4.[Quantity] END) ) as 'Pending',
(CASE WHEN T1.[U_AMTUOM]='kgs' THEN (T1.[OpenQty] * T1.[U_Kg_Mtr] * T1.[U_Length]) WHEN T1.[U_AMTUOM]='mtr' THEN (T1.[OpenQty] * T1.[U_Length]) WHEN T1.[U_AMTUOM]='nos' THEN T1.[OpenQty] WHEN T1.[U_AMTUOM]='Feet' THEN (T1.[OpenQty] * 20) ELSE T1.[OpenQty] END) as 'Pending Quantity ',
(T1.[Price] * (CASE WHEN T1.[U_AMTUOM]='kgs' THEN (T1.[OpenQty] * T1.[U_Kg_Mtr] * T1.[U_Length]) WHEN T1.[U_AMTUOM]='mtr' THEN (T1.[OpenQty] * T1.[U_Length]) WHEN T1.[U_AMTUOM]='nos' THEN T1.[OpenQty] WHEN T1.[U_AMTUOM]='Feet' THEN (T1.[OpenQty] * 20) ELSE T1.[OpenQty] END) ) as 'Amount equivalent as per Pending Qty',
T1.[U_TOTALWEIGHT] AS 'ORDERED QTY IN KGS' ,
(CASE When (T1.[Quantity]- T1.[OpenQty]) = 0 then (T1.[Quantity]- T1.[OpenQty]) else T4.[U_TotalWeight] END) as 'Dispatched QTY In KGs',
( (T1.[U_TOTALWEIGHT]) - (CASE When (T1.[Quantity]- T1.[OpenQty]) = 0 then (T1.[Quantity]- T1.[OpenQty]) else T4.[U_TotalWeight] END) ) AS 'PENDING QTY IN KGS',
FROM ORDR T0 INNER JOIN RDR1 T1 ON T0.[DocEntry] = T1.[DocEntry] INNER JOIN OITM T2 ON T1.[ItemCode] = T2.[ItemCode] INNER JOIN OITB T3 ON T2.[ItmsGrpCod] = T3.[ItmsGrpCod] left join DLN1 T4 ON T1.DocEntry = T4.BaseEntry
WHERE (CASE WHEN T1.[U_AMTUOM]='kgs' THEN (T1.[OpenQty] * T1.[U_Kg_Mtr] * T1.[U_Length]) WHEN T1.[U_AMTUOM]='mtr' THEN (T1.[OpenQty] * T1.[U_Length]) WHEN T1.[U_AMTUOM]='nos' THEN T1.[OpenQty] WHEN T1.[U_AMTUOM]='Feet' THEN (T1.[OpenQty] * 20 ) ELSE T1.[OpenQty] END) > 0 AND T3.[ItmsGrpNam] <> 'COIL'
When i am running it, it shows exact report for those pending orders which has 0 dispatch till date, but for orders which has partial dispatch it shows multiple lines which is unnecessary for the report.
Pl help
Regards
Ambesh Negi