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

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 D-SL AR Inv and CM List by Gross Profit .  Monthly Special.  A list of all Invoices and Credit Memos within a specific date range selected by the user.  The Invoices and Credit Memos are sorted by Gross Profit Percentage.  This SQL also demonstrates one way to avoid getting an error of "divide by zero" when doing some calculations.

SAP B1 SQL D-SL AR Invoices and Credit Memos by Sales Person and Date Range .  Frequently Requested SQL.  This list allows Sales Management to see the AR Invoices and Credit Memos split into two separate lines so they can see not only what was sold but what was returned by Sales Person and Date Range.  This SQL also demonstrates how to use a special WHERE clause to see individual Sales Persons or all Sales Personnel.  There is a special note on how SAP calculates Total Open IN on the Sales Analysis Report.

SAP B1 SQL D-SL Lapsed Days Order to Deliveries Order Details Ver 3 .  Monthly Special.  This SQL lists out the lapsed days between the date a shipment to the Customer is expected and when the shipment actually occurs.  There is a good example of how to use the DATEDIFF and CASE functions in SQL to assign different values within a column.

SAP B1 SQL D-SL Late Sales Order by Date Range .  Frequently Requested SQL.  A list used by Sales Personnel when contacting the Customer about possible late shipments.  Data is obtained from the Sales Order on SAP B1 and contains a few more fields than what the standard SAP B1 "Open Items List" does.

SAP B1 SQL D-SL Sales Register by Invoices for Indian Localization .  Frequently Requested SQL.  In a typical Indian scenario, we need sale and purchase registers to check the excise payable and receivable. The following is a sample which can be used  as a basic outline and modified per your requirements by Billings (Invoices). 

SAP B1 SQL D-SL Sales Register by Item for Indian Localization .  Frequently Requested SQL.  In a typical Indian scenario, we need sale and purchase registers to check the excise payable and receivable. The following is a sample which can be used  as a basic outline and modified per your requirements for reporing by Items.

SAP B1 D-SL Sales and Buy FIFO Price for Batch managed Items . Frequently Requested SQL. This SQL can be used  as a basic outline and modified per your requirements to display the Open FIFO layers of items managed by batches also which FIFO layer is used at particular invoice.

SAP B1 D-SL Sales and Purchase FIFO Price for Non-Batch Items . Frequently Requested SQL. This SQL can be used  as a basic outline to get sales/purchase FIFo price for items not managed by batches and can be modified per your requirements.

SAP B1 SQL D-SL Calculate Sales profitability based on B2B Orders . Monthly Special : This SQL can be used as basic outline by Finance/Sales persons to get Sales Profitability based on Back to Back orders.

  • No labels