Registration

Dear SAP Community Member,
In order to fully benefit from what the SAP Community has to offer, please register at:
http://scn.sap.com
Thank you,
The SAP Community team.
Skip to end of metadata
Go to start of metadata

(Back to H-IN Inventory Module SQL Posted to Wiki Listing - H-IN Inventory Module SQL Posted to Wiki   )

TYPE: Monthly Special

AUTHOR NAME: Zal Parchem

PURPOSE AND BACKGROUND:  This SQL was created in 2007A SP01 PL05.  The End User was doing Cycle Counts and wanted one place where they could review the postings made on each occurrence within the General Ledger.  Currently the Journal Entry is just one lump sum for all postings made in Inventory Transactions and there was a need for a better listing during the Inventory Department reviews to determine what kind of financial impact Cycle Counts were having.  A historical pattern can be determined by sorting the "Item" column.  One side benefit to this SQL is that it can be used when your company is audited in case the Inventory is selected as a point on the auditor's checklist.  A way to concatenate two data fields in SAP B1 is highlighted in the SQL.  Please make sure you add more segments if your company has more than two which is listed in this SQL.  If you find this SQL helpful, then use “copy and paste” to place and save it using the Query Generator of your SAP B1 installation. 

Here is a screen print of the SQL results:

H-IN Item Inventory Details for ST Postings During Inventory.png  

Copy and Paste this SQL:

--H-IN Item Inventory Details for ST Postings During Inventory Ver 1 ZP 2010 08 20

--DESCRIPTION:  SQL displays the details of the ST transactions found on the Inventory Posting List.

--AUTHOR(s):
--Version 1 Zal Parchem 2010 08 20

SELECT

T1.TransId AS 'JE Num',
T0.TransNum AS 'JE Detail ID',
--T0.TransType,
--T0.CreatedBy,
T0.DocDate AS 'Post Date',
T0.ItemCode AS 'Item',
T1.Memo AS 'Remarks',

CASE
WHEN T0.InQty > 0.000
THEN (T0.TransValue/T0.InQty)
ELSE (T0.TransValue/T0.OutQty) * -1
END AS 'Per Each',

T0.InQty AS 'Incr Inv Count',
T0.OutQty AS 'Decr Inv Count',
T0.TransValue AS 'Dlr Affect',
T2.Segment_0 + '-' + T2.Segment_1 AS 'Offset Acct'

FROM OINM T0

INNER JOIN OJDT T1
ON T0.CreatedBy = T1.TransID

INNER JOIN OACT T2
ON T0.InvntAct = T2.AcctCode

WHERE

T0.TransType = 58
AND T0.DocDate >= '[%0]'
AND T0.DocDate <= '[%1]'

FOR BROWSE

  • No labels