Select t0.ItemCode, t1.ItemName, isnull(t3.OnHandQty,0) as 'In Stock', t2.BinCode, t2.SL1Code, t2.SL2Code from OITW T0 I don't know anything about B1 but thought I'd share just a couple of suggestions from my years of meddling with SQL in various guises.Īre you able to control the table aliases used by the Query generator? If so, I'd probably re-write your statements such that:
#BALANCE CLOSING TRANSTYPE SAP B1 HOW TO#
If anyone has any suggestions on how to improve these, or you would like to share some of your own SQL Queries please feel free.
SELECT T1., T1., T1., T0.įROM OSLP T0 INNER JOIN ORDR T1 ON T0.SlpCode = T1.SlpCode This handy report helps me identify these quickly and easily Some of our Sales People forget to enter their name when adding a Sales Order. SELECT T0., T0.,T0., T2., T2., T2., T1.įROM ORDR T0 INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode Yes I know this exists in Sales Analysis reports but I prefer my layout: SELECT TOP 10 T0.CardCode, MAX(T0.Cardname) as Customer, SUM(T0.doctotal) as “Amount(LC)” Top 10 Customers (Thanks to Zal Parchem for this) If you want to run the same report but on Purchase Orders, simply substitute OPCH for OPOR This report shows all AR Invoices for a set period for a particular Business Partner in the currency on the Invoice (FC): The SAP Purchase Analysis report is in System Currency, mine is GBP. This has helped our Warehouse Manager to ensure ALL stock has a Default Bin: We want to report on all items in SAP not assigned a Default Bin Location. Order by t2.SL1Code, t2.SL2Code, t0.ItemCode Left outer join OIBQ t3 on t0.ItemCode=t3.ItemCode and t0.WhsCode = t3.WhsCode Inner join OBIN t2 on t2.AbsEntry = T0.DftBinAbs Inner join OITM t1 on t0.ItemCode = t1.ItemCode Select t0.ItemCode, t1.ItemName, isnull(t3.OnHandQty,0) as ‘In Stock’, t2.BinCode, t2.SL1Code, t2.SL2Code from OITW T0 The Bin Locations Content report in SAP only shows items in stock: We needed a way to list items in stock AND items not in stock for stock taking so we can run an inventory report between locations. WHERE t0.DocStatus = ‘O’ and (t0.Printed = ‘N’ and (SELECT count(*) SELECT t0.DocEntry, t0.CardCode, t0.CardName, t0.DocDueDate as ‘Due Date’, t0.DocCur, t0.DocTotal Please note we are using Boyum Print and Deliver module:
It’s easy to forget to send invoices and this report has helped us be more efficient. This query shows all AP Invoices added to SAP that are Open but have not been sent. If you like SQL want to learn more we’d love to see you at the next Business One User Group at SAP UK: UK and Ireland SAP User Group – Developing, Benefiting and Serving the SAP User Community
#BALANCE CLOSING TRANSTYPE SAP B1 GENERATOR#
Just copy and paste these in to your own Query Generator I wanted to share with you some of my most frequently used queries and why I like them. Now I use SQL queries in Business One almost daily. I started out as a relative novice to SQL a few months ago.
0 kommentar(er)
