SAP Business One Consultants | IT Support | 0843 886 1449

All posts tagged SAP Business One Tips

An SAP Business One Stock Report with All Items

The following stock report will display all Items with or without quantities in Stock.

 

select t0.ItemCode, t1.ItemName, isnull(t3.OnHandQty,0) as ‘In Stock’, t2.BinCode, t2.SL1Code, t2.SL2Code from OITW T0

inner join OITM t1 on t0.ItemCode = t1.ItemCode

inner join OBIN t2 on t2.AbsEntry = T0.DftBinAbs

left outer join OIBQ t3 on t0.ItemCode=t3.ItemCode and t0.WhsCode = t3.WhsCode

where t2.BinCode between [%0] and [%1]

order by t2.SL1Code, t2.SL2Code, t0.ItemCode

Shared by Owen Slater on the SCN

SAP Business One SQL Batch or Serial Number Quantities in a specific Bin Location

declare @WhsCode nvarchar(9), @ItemCode nvarchar(21), @BinLocation nvarchar(228)

set @WhsCode = ’01’
set @ItemCode = ‘B10000’
set @BinLocation = ’01-ABC’

select
T0.BinAbs, T0.ItemCode, T2.onHandQty, T4.DistNumber, T4.MnfSerial,
T4.LotNumber, T5.DistNumber, T5.MnfSerial, T5.LotNumber, T4.AbsEntry,
T1.BinCode, T4.AbsEntry, T5.AbsEntry, T1.WhsCode
from
OIBQ T0
inner join OBIN T1 on T0.BinAbs = T1.AbsEntry and T0.onHandQty <> 0
left outer join OBBQ T2 on T0.BinAbs = T2.BinAbs and T0.ItemCode = T2.ItemCode and T2.onHandQty <> 0
left outer join OSBQ T3 on T0.BinAbs = T3.BinAbs and T0.ItemCode = T3.ItemCode and T3.onHandQty <> 0
left outer join OBTN T4 on T2.SnBMDAbs = T4.AbsEntry and T2.ItemCode = T4.ItemCode
left outer join OSRN T5 on T3.SnBMDAbs = T5.AbsEntry and T3.ItemCode = T5.ItemCode
where
T1.AbsEntry >= 0 and T1.WhsCode >= @WhsCode and T1.WhsCode <= @WhsCode and (T2.AbsEntry is not null)
and T0.ItemCode in((select U0.ItemCode from OITM U0 inner join OITB U1 on U0.ItmsGrpCod = U1.ItmsGrpCod
      where U0.ItemCode is not null and U0.ItemCode >= @ItemCode and U0.ItemCode <= @ItemCode))
and T1.BinCode = @BinLocation
union all
select
T0.BinAbs, T0.ItemCode, T3.onHandQty, T4.DistNumber, T4.MnfSerial,
T4.LotNumber, T5.DistNumber, T5.MnfSerial, T5.LotNumber, T5.AbsEntry,
T1.BinCode, T4.AbsEntry, T5.AbsEntry, T1.WhsCode
from
OIBQ T0
inner join OBIN T1 on T0.BinAbs = T1.AbsEntry and T0.onHandQty <> 0
left outer join OBBQ T2 on T0.BinAbs = T2.BinAbs and T0.ItemCode = T2.ItemCode and T2.onHandQty <> 0
left outer join OSBQ T3 on T0.BinAbs = T3.BinAbs and T0.ItemCode = T3.ItemCode and T3.onHandQty <> 0
left outer join OBTN T4 on T2.SnBMDAbs = T4.AbsEntry and T2.ItemCode = T4.ItemCode
left outer join OSRN T5 on T3.SnBMDAbs = T5.AbsEntry and T3.ItemCode = T5.ItemCode
where
T1.AbsEntry >= 0 and T1.WhsCode >= @WhsCode and T1.WhsCode <= @WhsCode and (T3.AbsEntry is not null)
and T0.ItemCode in((select U0.ItemCode from OITM U0 inner join OITB U1 on U0.ItmsGrpCod = U1.ItmsGrpCod
      where U0.ItemCode is not null and U0.ItemCode >= @ItemCode and U0.ItemCode <= @ItemCode))
and T1.BinCode = @BinLocation
union all
select
T0.BinAbs, T0.ItemCode, T0.onHandQty, T4.DistNumber, T4.MnfSerial,
T4.LotNumber, T5.DistNumber, T5.MnfSerial, T5.LotNumber, T4.AbsEntry,
T1.BinCode, T4.AbsEntry, T5.AbsEntry, T1.WhsCode
from
OIBQ T0 inner join OBIN T1 on T0.BinAbs = T1.AbsEntry and T0.onHandQty <> 0
left outer join OBBQ T2 on T0.BinAbs = T2.BinAbs and T0.ItemCode = T2.ItemCode and T2.onHandQty <> 0
left outer join OSBQ T3 on T0.BinAbs = T3.BinAbs and T0.ItemCode = T3.ItemCode and T3.onHandQty <> 0
left outer join OBTN T4 on T2.SnBMDAbs = T4.AbsEntry and T2.ItemCode = T4.ItemCode
left outer join OSRN T5 on T3.SnBMDAbs = T5.AbsEntry and T3.ItemCode = T5.ItemCode
where
T1.AbsEntry >= 0 and T1.WhsCode >= @WhsCode and T1.WhsCode <= @WhsCode
and (T2.AbsEntry is null and T3.AbsEntry is null)
and T0.ItemCode in((select U0.ItemCode from OITM U0 inner join OITB U1 on U0.ItmsGrpCod = U1.ItmsGrpCod
and T1.BinCode = @BinLocation
where
U0.ItemCode is not null and U0.ItemCode >= @ItemCode and U0.ItemCode <= @ItemCode))

SAP Business One 9.0 Business Accounting System

SAP Business One 9.0 Advanced GL Account Determination

SAP Business One 9.0 provides a flexible and centralised system to manage inventory G/L account determination. With the advanced G/L determination rules, you can assign inventory G/L accounts by item groups, items, warehouses, business partner groups, ship-to countries, and various combinations of all the above criteria.

 

SAP Business One 9.0 Financials

Deferred Tax Enablement in Manual JE

SAP Business One 9.0 enables you to manage deferred tax in manual journal entries, journal vouchers, posting templates, and recurring postings

 

SAP Business One 9.0 Fixed Assets

SAP Business One 9.0 allows you to manage fixed assets using a fully integrated solution. In addition to the functions provided by the Fixed Asset Solution in SAP Business One 8.82, the fixed assets functionality implemented in SAP Business One 9.0 enables you to do many things that were out of reach with previous versions of SAP Business One.

In order for you to better manage your fixed assets in SAP Business One, SAP Business One 9.0 has also enhanced some functionality from the add-on, such as the use of the ‚ ‘Declining Balance‛‚ ‘Special Depreciation‛, and ‘Multilevel‛ depreciation methods, depreciation forecast, depreciation run, asset history sheet, and so on.

 

Internal Reconciliation in SAP Business One 9.0

The internal reconciliation functionality has been enhanced to support the following system currency aspects:

  • When performing internal reconciliation, conversion difference postings are automatically created
  • You can display system currency balances due, in various windows, and reports
  • The Conversion Differences report has been enhanced with an optimised calculation of the unrealised conversion difference

 

SAP Business One 9.0 Sales and Purchasing

Cancellation of Sales and Purchasing Documents

You can now cancel most sales and purchasing documents that normally trigger postings. Upon cancelling such a document, a corresponding cancelation document is created to reverse the associated accounting, tax, and inventory changes. The base documents belonging to the cancelled documents are reopened with balances due restored. You can now copy these base documents to new documents.

 

Adjusting Total Freight in Goods Receipt POs Based on A/P Reserve Invoices

SAP Business One 9.0 supports adjustment of the total freight when creating a goods receipt PO based on A/P reserve invoices under the following conditions:

  • The freight of the goods receipt PO does not exceed the unallocated freight of the base A/P reserve invoices
  • If you have allocated all open quantities of the base A/P reserve invoices to this goods receipt PO, you must allocate all the remaining freight of the reserve invoices to the goods receipt PO

Adjusting Item Cost in Non-Based A/R Return and A/R Credit Memo

The item cost in non-based A/R return and an A/R credit memo is determined by item cost at the time of the document creation. Starting from SAP Business One 9.0, the user can adjust the item cost in these documents. This enhancement applies only to items managed by Moving Average Price or FIFO methods.

 

SAP Business One 9.0 Banking

Payment, Cheque, and Deposit Cancellation Improvements

With SAP Business One 9.0, you are able to do the following:

  • Cancel incoming payments with deposited cheques
  • Cancel deposits partially
  • Cancel deposited cheques
  • Cancel non-deposited cheques with automatic journal entries

 

Down Payment Request Reconciliation

SAP Business One 9.0 enables you to manually reconcile, fully or partially, a payment of down payment request with an invoice (regular invoice or reserve invoice, correction invoice is not supported).

 

SAP Business One 9.0 Inventory

Bin locations in SAP Business One 9.0 allows you to manage bin locations for your warehouses. You can enable the bin location function for individual warehouses.

 

Bin Location Related Documents

After you enable the bin location function for intended warehouses, you need to record bin locations in those warehouses for all receipts and issues of inventory, including the processing of the following documents or transactions:

  • Goods receipt PO, goods return, A/P invoice, A/P credit memo, delivery, return, A/R invoice, A/R invoice + payment, A/R credit memo
  • Goods receipt, goods issue, inventory transfer, inventory taking
  • Receipt from production, issue for production
  • Pick and Pack

During the receipt, issue, and transfer of goods, SAP Business One 9.0 lets you automate the process of allocating goods from and to bin locations. You also can perform the allocation manually.

 

Inventory in Bin Locations

To better manage the inventory in your bin locations, SAP Business One 9.0 enables you to do the following:

 

  • View the information of stored items, such as the item quantity, and number of items
  • Restrict the storage of your bin locations, for example, to a particular batch, item, or item group
  • Restrict the use of your bin locations, for example, to receiving or issuing goods only
  • Replenish the inventory in your bin locations if the inventory level falls below the minimum

Bin Location Management

To facilitate the management of bin locations, SAP Business One 9.0 supports the generation, update, and deletion of bin locations in batches.

 

Serials and Batches

SAP Business One 9.0 lets you maintain serials and batches in your bin locations. When you issue serials and batches from bin locations, you can decide whether you want to pick the items according to their bin locations, or their serial and batch information.SAP Business One 9.0 lets you maintain serials and batches in your bin locations. When you issue serials and batches from bin locations, you can decide whether you want to pick the items according to their bin locations, or their serial and batch information.

 

Receiving Bin Locations
SAP Business One 9.0 allows you to designate certain bin locations as receiving bin locations. When you receive goods, these goods can first be automatically placed in receiving bin locations.

 

Reports

You can get an overview of all bin locations, and check the inventory statuses and movements in bin locations using the following reports:

 

  • Bin Location List
  • Bin Location Content List
  • Inventory Posting List

Multiple Unit of Measurement (UoM) in SAP Business One 9.0

SAP Business One 9.0 provides a high degree of flexibility to your management of products in multiple units of measurement (UoM). The Multiple UoM feature enables you to sell, purchase, receive and ship products in any UoM that your business needs, and to define default UoMs for various types of transactions. All sales and purchasing documents support items with multi-UoM definitions.The highlights of this new feature are as follows:

  • Unlimited global and product-specific UoMs
  • Setup of UoM groups with conversion rules between base UoMs and alternative UoMs within the groups
  • These UoM groups are used in item master data, marketing documents, and inventory documents
  • Default sales UoMs, purchasing UoMs, inventory UoMs, and automatic conversion to other applicable UoMs in transaction processing
  • Related enhancement of packaging, bar code and pricing functions:
  • Package settings for multiple UoMs are incorporated into item master data and are used in sales documents to recommend the number of packages
  • For an item using multiple UoMs, you can define multiple bar codes and different prices for each of its UoMs

Note that production transactions are processed in a single inventory UoM.

Price List and Discount Enhancements

In SAP Business One 9.0, the price list and the discount-groups functionality has been enhanced to improve efficiency and to support specific business processes. Using the enhanced price list function you can now do the following:

  • Assign a price list to a business partner group
  • Define multiple currency prices in a specific price list or in a special prices list
  • Define prices and special prices for different units of measurement, of a specific item
  • Define price lists as active or inactive

In addition, you can now decide whether to remove un-priced items from the database in order to reduce the database size and impact on performance when running applications on the database.Using the enhanced discount groups function you can now do the following:

  • Define discount groups for all business partners, for customer groups or for vendor groups
  • Define discounts for specific items
  • Define variable discounts based on quantity bought
  • Define discount groups as active or inactive
  • Define discountable control for a business partner and for an item
  • Allocate multiple discounts

You can get an overview of all prices and discounts using the following reports:

  • Price report
  • Discount report

These reports are also available from within marketing documents, enabling the user to track the source price and discount of each item during document creation.

Serial and Batch Items in Drop Ship Warehouses

SAP Business One 9.0 now lets you manage serial and batch items in drop ship warehouses. After you enable the serial and batch management for your drop ship warehouses, you can do the following:

  • Allocate serial and batch items in drop ship warehouses to newly created documents. The documents include the following:
  • Sales order, delivery, return, A/R invoice, A/R invoice + payment, A/R credit memo, A/R Debit Memo, A/R reserve invoice
  • Goods receipt PO, goods return, A/P invoice , A/P credit memo, A/P Debit Memo
  • Maintain the serial and batch information for items in drop ship warehouses using Serial Number Management and Batch Management
  • View the transactions of the serial and batch items in drop ship warehouses using Serial Number Transactions Report and Batch Number Transactions Report

In addition, if your serial items in drop ship warehouses are delivered, SAP Business One can create customer equipment cardsautomatic
ally for these items in the same way as for the items in non-drop ship warehouses.

Inventory Tracking

SAP Business One 9.0 provides new capabilities for running a complete inventory taking process. It starts with cycle counting alerts and continues with well-structured tasks and innovated reporting.Major enhancements include the following:

  • Providing documents to record and manage inventory counting and inventory posting
  • Freezing items during inventory counting
  • Recording counting results and variance by two different inventory takers
  • Improving the cycle count function to enable continuation of counting process from cycle count alerts and recommendations
  • Counting the exact serial numbers and batches
  • Supporting inventory taking for bin locations
  • Providing inventory-taking reports to consolidate and analyse existing inventory counting and inventory posting documents
  • Providing an Inventory Counting Recommendation dashboard report
  • Providing documents to record and manage inventory opening balances
  • Counting by unit of measurement
Locked Business one license SAP multiple users

SAP B1 Version 9 and above users – take note!

SAP Business One users who are temporarily locked out whilst another machine is logged in as the same user isn’t an error! It is new, core functionality to stop license over usage (read as abuse!).  Once the second machine has logged off the first user unfreezes.

Please note:
If a SAP B1 named user is being used for interfaces/ APIs then there are possibilities that locks or blocks will occur! Not good for an interface and there are better ways of overcoming this.

A bit about the SAP Business One license model.

SAP Business One licenses are based on a named user licensing model. Each user name needs to have a license granted to it.   A named user is an employee who is authorized to access directly or indirectly the licensed SAP Business One software, which is enforced by License Policy Technical Enforcement features in version 9.0.

Employees may not share a single user name to logon to SAP Business One. Licenses assigned to a named user are not specific to a single company database.

Locked Business one license SAP multiple users

So What/Why has it changed?

In prior releases of SAP Business One up to version 8.82 using the same user name, a named user can log on to SAP Business One twice, for example to access a second company. In reality however, this is not the case. Up to the 8.82 SAP Business One version level, even with a clear emphasis of a named user licensing model (one named user utilizing/assigned one SAP Business One license)   the system allows 2 possible login to the same database company using the same user name and password. This then allows customers to have a work around of having two employees using the same login and password to process things within an SAP Business One database company. The drawback in this case is that instead of one license needed for the two employees it now only requires each user to have their own login and password.

Now with the release of SAP Business One version 9.0, inquiries started to pour in as more customers from the SAP Business One 8.8 versions started upgrading to the latest release of SAP Business One. The scenario where the same name user and password logging in as two separate instances to the same database does not work anymore. This has led to many customers asking the questions “Why I have been allowed to do this in SAP Business One 8.82 version and earlier why not now?”.

What to do?

Basically, if you are under licensed, you will need to purchase more licenses!

If you don’t currently benefit from SAP Business One (err, why on Earth did you find this page??!!) but would like a powerful and easy business system to use for your business, get in touch!

 

Your Name (required)

Your Email (required)

Your Telephone

Your Company

Subject

Your Message

 

 

SAP Business One Missing Months / Periods in Reports

Have you run a Balance Sheet, Profit and Loss or Trial Balance in B1 V8.1, 8.2 or a recently upgraded V9 and discovered that you are missing periods or months from the report? Weird, eh? It is as much use as an Octopus tentacle snooker cue, but don’t worry, it can be fixed!

The issue appeared in V8.x and is fixed in V9. The problem is, you upgrade V9 and the problem still exists. The problem has been fixed and will not occur again but you need to fix the cause of the issue to see the months again. All this requires is a quick fix to a users settings.

  1. Find the formid, itemid of the problematic grid
    a) Open SBO, find the problematic grid in a form
    b) Enable the System/Debug information(Go to Menu->View, check System/Debug information)
    c) Put the mouse on any field of the problematic grid, find the formid (The number after ‘Form=’) and the itemid(The number after ‘Item=’) on the status bar at the left bottom of SBO
    d) Record this formid and itemid
  2. Close SBO. Make sure no SBO clients are connected to the server. (This step is very important! If SBO is not closed, this solution will not work!)
  3. Open SQL Analyzer, run the update query below


Open SQL Analyzer, run the update query below.
Before running the query, do please replace:
/!Formid found in Step 1!/ to the formid found in step 1.
/!Itemid found in Step 1!/ to the itemid found in step 1.
/!Problematic User Code!/ to the user code of problematic user.
–begin
–delete all the user settings
delete cprf where formid=’/!Formid found in Step 1!/’ — for this form
and itemid=’/!Itemid found in Step 1!/’ — and this document
and usersign =
(
select internal_k from ousr where
user_code = ‘/!Problematic User Code!/’
)
–endMissing months financial report fix

 

Using variables in SQL queries for SAP Business One is straight forward for simple select statements but what about the more complex variables or if you want to declare a variable for your SQL?

Answer: You have to get a bit more creative! See the example below.

To select a from and to date as a variable in a declare statement you have to add a nifty bit of extra code.

/*

/SELECT FROM [dbo].[VPM1] T0/

declare  @fromdate as datetime

/* WHERE */

set @fromdate = /* T0.DueDate */ ‘[%0]’

 

/SELECT FROM [dbo].[VPM1] T1/

declare  @tilldate as datetime

/* WHERE */

set @tilldate = /* T0.DueDate */ ‘[%1]’

 

 

select  OVPM.docnum, VPM1.dueDate,VPM1.CheckNum, VPM1.Branch,

VPM1.BankCode, VPM1.CheckSum, OVPM.CardName,

case

when OPCH.docnum is null then VPM2.docEntry

else OPCH.docnum

end as ‘docnum’

from OVPM INNER join VPM1 on OVPM.docnum = VPM1.docnum

left join  VPM2 LEFT JOIN OPCH ON VPM2.DocEntry = OPCH.DocEntry

on OVPM.docnum = VPM2.docnum

where OVPM.Canceled = ‘N’ AND VPM1.DueDate >=@fromdate AND

VPM1.DueDate <=@tilldate

*/

 

1 Dash 1 can provide you and your SAP Business One users with excellent training of the usage of SQL and Crystal Reports. If you would like to find out more simply enter your details in the boxes below or call us.

Your Name (required)

Your Email (required)

Your Phone

Subject

Training Course Topic

Your Message

SAP Business One – Date Deviates From Permissible Range

The ever popular Date Deviates From Permissible Range error message appears for two reasons and IS easily solved!

The usual occurrence is in the New Year when new posting periods need to be created. To resolve this simply create a new posting year/period in (and watch out for your sub-periods!):

Within SAP Business One in the Main Menu or top menu Modules option go to –

Administration –> System Initialisation –> Posting Periods

The second occurrence is when you are trying to post documents with a due date that is not covered by the created Posting Periods Due Date range. This usually occurs towards the end of the year. You can solve this problem with the following solution.

Administration –> System Initialisation –> Posting Periods

Change the Due Date range to allow you to enter the document.

 

We understand SAP Business One, so you don’t have to.

SAP Business One, the SAP offering for Micro businesses and SME’s is a powerful product with immense potential. With more and more businesses using SAP Business One we have listened and pleased to offer consultancy services to provide training, audits and general advice on how to get the most out of the system.

Do you know which Partner or Gold Partner is best for you?

With our market knowledge and contacts in many of the SAP Business One partners throughout the UK, we can assist you in finding the best match for you, your staff and your business. Every business is different and requires a different approach. The SAP Business One implementation consultant assigned to you has to work with you – we will find the best match.

Contact us to discuss your requirements. We’d love to hear from you.