Below is a listing of SQL which you can review for ideas or use in your SAP B1 System:
(Back to SAP B1 SQL Tips and Tricks Main Page - SAP B1 SQL Tips and Tricks )
SAP B1 SQL H-IN Item Inventory Details for ST Postings During Inventory . Frequently Requested SQL. Some might consider this SQL belongs in the Financial Module, but this SQL was actually written for the End Users doing Cycle Counts. The SQL uses the OJDT/JDT1 (Journal Entry records) to provide an audit trail of what transactions were done against Inventory within a specified date range. The SQL could also be good as a documentation trail for when the company is audited by their outside Auditing and Certification Partners.
SAP B1 SQL H-IN Item Inventory Opening and Closing Stock per warehouse . Frequently requested SQL. Even this query is helpful to know the monthly closing stock of the Stores department. This query involves OINM table, so it may take some time to execute according to the number of transactions you have in the database. As all aware that SAP is not storing the Opening and closing stock in any table, this query uses past transactions also. For example, if you select the date range as 01-02-2011 to 28-02-2011, opening stock will arrive by taking all the transactions taken before 01-02-2011.
SAP B1 SQL H-IN Item Prices in Each Price List . Monthly Special. This SQL script list Item Prices in each price list as seperate column with Stock and Item Group Details.