Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

SAP B1 - Goods Add/Rem through inventory tracking need show in this report.

team I wand to add the details of Stock add /remove through Inventory tracing and porsted details.

SELECT T0.[ItemCode], T1.[ItemName], T1.[SWW], t1.[ValidComm],

/* A/R Credit Memo */

sum(case when T0.[DocDate]  < [%3] then (T0.[InQty]-T0.[OutQty]) else 0 end) 'OB',

/* Purchse data */

sum(case when T0.[TransType]=20 and (T0.[DocDate]  between [%3] and [%4]) then (T0.[InQty]-T0.[OutQty]) else 0 end) 'Pur',

/* Purchse Returndata */

sum(case when T0.[TransType]=21 and (T0.[DocDate]  between [%3] and [%4]) then (T0.[InQty]-T0.[OutQty]) else 0 end) 'Ret',

/* Add Stock */

sum(case when (T0.[TransType]=59 and (T0.[DocDate]  between [%3] and [%4]) and T0.[JrnlMemo]='Goods Receipt') then (T0.[InQty]-T0.[OutQty]) else 0 end) 'Add',

/* Goods Issue */

sum(case when (T0.[TransType]=60 and (T0.[DocDate]  between [%3] and [%4]) and T0.[JrnlMemo]='Goods Issue') then (T0.[InQty]-T0.[OutQty]) else 0 end) 'Remove',

/* Receipt from Production */

sum(case when (T0.[TransType]=59 and (T0.[DocDate]  between [%3] and [%4]) and T0.[JrnlMemo]='Receipt from Production') then (T0.[InQty]-T0.[OutQty]) else 0 end) 'Prod',

/* Issue for Production */

sum(case when (T0.[TransType]=60 and (T0.[DocDate]  between [%3] and [%4]) and T0.[JrnlMemo]='Issue for Production') then (T0.[InQty]-T0.[OutQty]) else 0 end) 'ISs-Prod',

/* Deliveries */

sum(case when T0.[TransType]=15 and (T0.[DocDate]  between [%3] and [%4]) then (T0.[InQty]-T0.[OutQty]) else 0 end) 'sALE',

/* A/R Invoice */

sum(case when T0.[TransType]=13 and (T0.[DocDate]  between [%3] and [%4]) then (T0.[InQty]-T0.[OutQty]) else 0 end) 'A/R',

/* A/R Credit Memo */

sum(case when T0.[TransType]=14 and (T0.[DocDate]  between [%3] and [%4]) then (T0.[InQty]-T0.[OutQty]) else 0 end) 'A/R Credit',

/* Returns */

sum(case when T0.[TransType]=16 and (T0.[DocDate]  between [%3] and [%4]) then (T0.[InQty]-T0.[OutQty]) else 0 end) 'Returns',

/* A/P Invoice */

sum(case when T0.[TransType]=18 and (T0.[DocDate]  between [%3] and [%4]) then (T0.[InQty]-T0.[OutQty]) else 0 end) 'A/P INV',

/* A/P Invoice Credit Memo */

sum(case when T0.[TransType]=19 and (T0.[DocDate]  between [%3] and [%4]) then (T0.[InQty]-T0.[OutQty]) else 0 end) 'A/P Cr Memo',

/* Transfer */

sum(case when T0.[TransType]=67 and (T0.[DocDate]  between [%3] and [%4]) then (T0.[InQty]-T0.[OutQty]) else 0 end) 'Transfer',

/* Closing Stock */

sum(case when T0.[DocDate] <= [%4] then (T0.[InQty]-T0.[OutQty]) else 0 end) 'Cl Stock',

/* A/R Credit Memo */

sum(case when T0.[DocDate]  < [%3] then T0.TransValue else 0 end) 'Amount OB',

/* Purchse data */

sum(case when T0.[TransType]=20 and (T0.[DocDate]  between [%3] and [%4]) then T0.TransValue else 0 end) 'Amount Pur',

/* Purchse Returndata */

sum(case when T0.[TransType]=21 and (T0.[DocDate]  between [%3] and [%4]) then T0.TransValue else 0 end) 'Amount Ret',

/* Add Stock */

sum(case when (T0.[TransType]=59 and (T0.[DocDate]  between [%3] and [%4]) and T0.[JrnlMemo]='Goods Receipt') then T0.TransValue else 0 end) 'Amount Add',

/* Goods Issue */

sum(case when (T0.[TransType]=60 and (T0.[DocDate]  between [%3] and [%4]) and T0.[JrnlMemo]='Goods Issue') then T0.TransValue else 0 end) 'Amount Remove',

/* Receipt from Production */

sum(case when (T0.[TransType]=59 and (T0.[DocDate]  between [%3] and [%4]) and T0.[JrnlMemo]='Receipt from Production') then T0.TransValue else 0 end) 'Amount Prod',

/* Issue for Production */

sum(case when (T0.[TransType]=60 and (T0.[DocDate]  between [%3] and [%4]) and T0.[JrnlMemo]='Issue for Production') then T0.TransValue else 0 end) 'Amount ISs-Prod',

/* Deliveries */

sum(case when T0.[TransType]=15 and (T0.[DocDate]  between [%3] and [%4]) then T0.TransValue else 0 end) 'Amount sALE',

/* A/R Invoice */

sum(case when T0.[TransType]=13 and (T0.[DocDate]  between [%3] and [%4]) then T0.TransValue else 0 end) 'Amount A/R',

/* A/R Credit Memo */

sum(case when T0.[TransType]=14 and (T0.[DocDate]  between [%3] and [%4]) then T0.TransValue else 0 end) 'Amount A/R Credit',

/* Returns */

sum(case when T0.[TransType]=16 and (T0.[DocDate]  between [%3] and [%4]) then T0.TransValue else 0 end) 'Amount Returns',

/* A/P Invoice */

sum(case when T0.[TransType]=18 and (T0.[DocDate]  between [%3] and [%4]) then T0.TransValue else 0 end) 'Amount A/P INV',

/* A/P Invoice Credit Memo */

sum(case when T0.[TransType]=19 and (T0.[DocDate]  between [%3] and [%4]) then T0.TransValue else 0 end) 'Amount A/P Cr Memo',

/* Transfer */

sum(case when T0.[TransType]=67 and (T0.[DocDate]  between [%3] and [%4]) then T0.TransValue else 0 end) 'Amount Transfer',

/* Closing Stock */

sum(case when T0.[DocDate] <= [%4] then T0.TransValue else 0 end) 'Amount Cl Stock'

/* From */

FROM [dbo].[OINM]  T0

INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode

INNER JOIN OITB T2 ON T1.ItmsGrpCod = T2.ItmsGrpCod

WHERE

(T0.ItemCode = '[%0]' OR '[%0]' = '') and (T2.[ItmsGrpNam] = '[%2]' OR '[%2]'='')

/* Group */

GROUP BY T0.[ItemCode], T1.[ItemName], T1.[SWW], t1.[ValidComm]

/* Order */

ORDER BY T0.[ItemCode]

Tags:
Former Member
Not what you were looking for? View more on this topic or Ask a question