The Broken Promises of ERP eBook

 


SAP B1 Report – Production Order Status for related Sales Orders

In SAP Business One, you have the ability to link Product Orders to Sales Orders. Additionally, at release 8.82 of SAP Business One, the Procurement Confirmation Wizard can automatically create Production Orders directly from Sales Orders, or after Sales Orders have been posted. However, this is not a report that list all Production Orders associated […]


SAP Business One – Sales Summary total for last 24 hours

Sales Summary total for last 24 hours This query in SAP Business One returns 1 value, the total sales dollars in the last 24 hours. This is a great query to turn into an alert, or use in a Dashboard on release 8.8 or SAP Business One. SELECT sum(T0.[DocTotal]) AS ‘SO$’ FROM ORDR T0 WHERE […]


SAP Business One – Query – Production Planning Board

Query – Production Planning Board SAP Business One Production Production Planning Board. This is a great report and drill down, providing production planners a tool to help manage their planned and released production orders. SELECT T3.[CardName], T3.[CardCode], T0.[DocNum], T0.[Status], T0.[ItemCode], T0.[DueDate], T0.[PlannedQty], T0.[CmpltQty], T0.[RjctQty], T1.[ItemCode], T1.[PlannedQty], T2.[OnHand] FROM OWOR T0 INNER JOIN WOR1 T1 ON […]


SAP Business One – Query Generator

Sub Queries can be extremely useful. Let’s say you want to get a list of business partners (Customers) who have ordered 1000 or more cases of your products in total. Because your report would be getting data from the RDR1 table, without a sub query, the best you could do is sum quantities for all […]


SAP Business One – Change Item Descriptions

SAP Business One Change Item Descriptions on Marketing Documents Did you know that you can change an item description on all marketing documents such as Purchase Quotations, Goods Receipt, Goods Returns, AP Invoice, Sales Quotations, Purchase Orders, AP Invoice, Sales Orders, Delivery Notes, etc?) This nice feature is either forgotten or never learned by many […]


SAP Business One – Incoming Payments with Payments On Account

SELECT T0.DocNum AS ‘Inc Pay Numb’, T0.DocDate AS ‘Post Date’, T0.CardCode AS ‘Cust Numb’, T0.CardName AS ‘Customer Name’, T0.DocTotal AS ‘Total Check Recd’, T0.NoDocSum AS ‘Overpayment’ FROM ORCT T0 WHERE T0.[NoDocSum] < > 0 AND T0.DocTotal < > T0.NoDocSum AND T0.DocDate >= ‘[%0]’ AND T0.DocDate ORDER BY T0.CardName, T0.DocDate FOR BROWSE We hope you find […]


SAP Business One Training Tips #6

Finally, an Inventory Aging report by warehouse SELECT T0.ITEMCODE , T0.ONHAND as ‘Total Qty’, CASE WHEN Datediff(day, T1.LASTPURDAT,GETDATE())= 45 AND DateDiff(day, T1.LASTPURDAT,GETDATE()) < 90 THEN T0.ONHAND END ’45 to 90 Days(Qty)’ , CASE WHEN DateDiff(day, T1.LASTPURDAT,GETDATE()) >= 45 AND DateDiff(day, T1.LASTPURDAT,GETDATE()) < 90 THEN T0.ONHAND*T0.AVGPRICE END ’45 to 90 Days(Value)’, CASE WHEN Datediff(day, T1.LASTPURDAT,GETDATE()) >= […]


SAP Business One Training Tips #5

SAP Business One – Planned Production Order Shortages Query This query uses only standard fields, and lists for any production order in Planned status, component shortages. The “AND T0.[Status] = ‘P’” can be changed to ” AND T0.[Status] = ‘R’” to show release production order shortages, or ” AND T0.[Status] <> ‘L’” to show Planned […]


SAP Business One Training Tips #4

Negative Gross Profit and Sales Report from A/R Credit Memos SELECT T0.[DocDate], T4.[GroupName], T0.[CardCode], T0.[CardName], ((T1.[Quantity]* -100)/100) AS ‘Qty Returned’, (T1.[Price]*((T1.[Quantity]* -100)/100)) AS ‘Net Sales’, (T1.[PriceBefDi]*((T1.[Quantity]* -100)/100)) AS ‘Gross Sales’, T2.[SlpName] FROM ORIN T0 INNER JOIN RIN1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OSLP T2 ON T1.SlpCode = T2.SlpCode INNER JOIN OCRD T3 ON […]


Phone: 888.239.2818 | Fax: 312.621.9200

 Copyright © 2012 Clients First Business Solutions (North America) LLC. All rights reserved

CFBS Locations: New Jersey (Head Office)Alabama | California | FloridaIllinois | Minnesota | TennesseeTexas