SAP Business One Consultants | IT Support | 0843 886 1449

All posts in SAP Business One Consultancy

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))

This is a short and to the point Crystal Reports post to assist anyone trying to count the number of Groups in a report. If you are using Crystal Reports you will know that the Summary does not work on Groups. We hope you find the following useful!

Crystal Report Group Count

To count the number of Crystal Report group entries in a report, rather than the entries within a Group, use the following process:

1) Create a formula @GroupCountReset and place this formula in the page header and make it suppressed (hidden)

whileprintingrecords;

numbervar i:=0;

2) Create another formula @GroupCountEval place this in the group header where you want to count the entries and suppress if required

whileprintingrecords;

numbervar i:= i+1;

3) Create another formula @GroupCountDisplay and place this in report footer. Don’t suppress this one! You need to see it!

whileprintingrecords;

numbervar i;

 

You should now have a count of Group entries.

Useful information for Crystal Reports and Crystal Reports for SAP Business One.

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

 

 

Business Partner Email Report SQL HANA

Want a list of your Business Partners email address for marketing? Email your business partners from your favourite email marketing site by using this report query. It’s SQL that you run from within Query Generator or SQL Server.

Simply copy the code between the /* … and … */ for your version of SAP B1 and paste into the Query Generator.

Here’s something for free! Have a great bank holiday weekend!

 

SAP Business One SQL Server version –

/*

 

–Start. Marketing Email List V1.1

select t1.CardCode, t2.cardname, t1.E_MailL

from ocpr t1 inner join OCRD t2 on t1.CardCode = t2.cardcode

and t2.cardtype = ‘C’

–End. Author Stuart Folley www.1dash1.co.uk  0843 886 1449

 

 

 

 

SAP Business One HANA SQL version –

/*

 

–Start. Marketing Email List V1.1

SELECT T1.”CardCode”, T0.”CardCode”, T1.”E_MailL”

FROM OCRD T0  INNER JOIN OCPR T1 ON T0.”CardCode” = T1.”CardCode”

WHERE T0.”CardType” = ‘C’

–End. Author Stuart Folley www.1dash1.co.uk  0843 886 1449

 

 

 

Your Name (required)

Your Email (required)

Your Telephone

Your Company

Subject

Your Message

 

 

SAP Business One Year End Closing Instructions

SAP kindly provide a 50 page document which provides in depth instructions on the Year End process but most people only need to know how to run the actual Year End process in SAP B1.  Because it is a once yearly procedure, it is forgotten year to year. Here are the instructions for running the Year End process in Business One.

Year End Process in SAP B1

To carry forward the balances of the P&L accounts to the corresponding account in the balance sheet
(or several different balance sheets), you can use the period-end closing function. You can store the
results initially as a report and then post them at a later stage.
If you make postings after entering the balances carried forward, you need to restart the period-end
closing routine to include these subsequent postings.

EXAMPLE
Administration / Utilities / Period-End Closing

When you execute the report, the Period-End Closing window opens:

In the Period-End Closing – Selection Criteria window, you can specify the same account
in the Retained Earnings Account and the Period-End Closing Account fields.

Once you have executed the period-end closing for the selected expenses and revenue accounts, 2
transactions are created for each account and 2 journal entries are automatically created to reflect
those transactions.
The balances of those P&L accounts are posted to the period-end closing account on the same day
(the last day of the period) and the balances are reduced to zero. The first journal entry reflects this.
On the first day of the subsequent period, the period-end closing account is posted to the
corresponding balance carried forward to the retained earnings account in the balance sheet. The
second journal transaction reflects this.

The balance of the period-end closing account is zero.

This is how to run the SAP Business One Year End Process.

 

Contact Stuart Folley @ 1Dash1 if you have any questions or suggestions.

Your Name (required)

Your Email (required)

Your Telephone

Your Company

Subject

Your Message

 

 

We like to make things really simple to avoid confusion. Have a look at this quick vid and see it helps a bit. Although we install SAP Business One I guess our real talent is helping businesses improve their processes along the way.

Any questions please contact us by from our contact page, at contact@1dash1.co.uk, phone (at the top of the page) or carrier pigeon :)

 

ERP SAGE SAP B1 Implementation Consultant

Senior Implementation Consultant – SAP Business One (SAP B1 )

Home Based in Bedfordshire, Buckinghamshire, Cambridgeshire, Hertfordshire, Surrey, Oxfordshire, Northamptonshire
£35k – £50k + Bens

  • SAP Business One (SAP B1)
  • Implementation Consultant
  • SAP Business One Certified
  • ERP
  • Project Definition & Costing
  • Project Life Cycle
  • Pre-Sales
  • Data Migration
  • Installation 
  • Configuration
  • Product Training
  • Accounting background (ideally AAT)
  • SQL
  • CRM
  • SQL Server
  • Crystal Reports
  • DTW

ERP SAGE SAP B1 Implementation Consultant

Senior Implementation Consultant – SAP Business One, SAP B1

A Senior SAP Business One (SAP B1) implementation consultant is required by 1 dash 1.
We want an experienced and enthusiastic SAP B1 Senior Consultant with an ERP/ SAP Business One implementation background to join our team. An Accounting background would be an advantage.

ERP Consultant?

Do you have skills?

 

Do you know the difference between Net and Gross Profit?

As a SAP B1 certified Consultant you will have a proven and demonstrable track record in all aspects of the project life-cycle of SAP Business One implementations.

Your duties will include Pre-Sales, project definition and costing, installation and configuration, and training. Coupled with this, strong knowledge of Microsoft Windows operating systems and Microsoft Office products is essential for the successful Implementation Consultant. You’ll understand the accounting needs of an organisation from general accounting, A/R, A/P, ordering process and configuration, production, stock, delivery and distribution, and CRM.

Naturally you’ll have superb communication Skills and pride yourself in your consultative approach.

If you can convince us that you deserve the job, you’ll get it.

Rewards, bonuses and other incentives are on offer for the right SAP Business One consultant. If you have a background in ERP consultancy in Sage, Access, Dynamics or the larger ERP suites from SAP or Oracle – we still want to hear from you.

Enter your details below or send an email to contact@1dash1.co.uk

Your Name (required)

Your Email (required)

Your Telephone

Your Company

Subject

Your Message

We are currently taking bookings for training courses throughout August 2013. Our training courses can either be on your site for a maximum of 8 people or at an external location suitable for attendees from various companies.

On site training is excellent for your employees as it will be specific to your system as it will be based on your Test system.

External multi-company training is more suited for non specific training requirements and usually proves extremely useful for those candidates wishing to network with peers from other industries.

SAP Business One Training Courses

Our SAP Business One training courses can be specifically tailored to your system and, dependent on time, our time can be used for consultancy questions. We will listen and work with you to ensure that your users receive the training that will positively impact your business.

1 Dash 1 also provide Super User 1 week long training courses to enable one of your users to become the main point of contact for any SAP Business One related questions that arise within the business. The week long course will empower your user to answer questions and create SQL Queries for entry and intermediate reporting. The week long course is structured and priced differently to our offer training course offerings so please specify if the course is of interest and we will contact you with the relevant information.

Simply enter your details below with your training requirements and we will contact you to arrange dates and discover what training style is most suitable for you and your users.

Your Name (required)

Your Email (required)

Your Phone

Subject

Training Course Topic

Your Message

SAP’s announces that HANA powered versions of its ERP Business Suite range has gone on general sale.

The new version (Version 9) of Business One, which runs on Microsoft SQL and was announced yesterday at the Sapphire NOW conference in Florida, can be deployed on premise or in the cloud and is accessible from mobile devices.

The latest version of Business One also includes new capabilities for business partner and warehouse management, infrastructure and inventory management.

SAP Business One Powered by HANA

  • Run your entire business with a single solution that will scale as you grow
  • Drive measurable performance gains in all of your key business processes
  • Use analytics to get up-to-the second information on purchasing habits and more
  • Generate insightful, interactive reports in seconds – no delays, no waiting
  • Enjoy the power of the SAP HANA platform, priced specifically for small business
  • Access real-time business insights – for faster, smarter decision making
  • Empower employees to create standard and ad hoc reports with ease
  • Generate interactive reports in seconds – no delays, no waiting
  • Enjoy the power of the SAP HANA platform, priced for small business
  • Reduce your reliance on consultants and IT for reporting needs

The HANA in-memory database is currently SAP’s biggest technology initiative, and SAP co-CEO Jim McDermott, in a keynote Tuesday, said HANA would be the platform for all SAP product development going forward.

Version 9 SAP Business 1 powered by HANA

In a keynote Wednesday, SAP co-CEO Jim Hagemann-Snabe re-emphasized that point when he announced the general availability of Business Suite on HANA.

“I would claim that HANA is the biggest innovation in 20 years in business software,” Hagemann-Snabe said. Along with the technology’s ability process huge volumes of data, it also greatly speeds up the performance of Business Suite, SAP’s ERP and industry application set for bigger companies, he said. “This idea of putting data in main memory is a quantum leap.”

 

Find out more about the cost of SAP Business One.

Contact 1 Dash 1 to find out more about SAP Business One powered by HANA.

Your Name (required)

Your Email (required)

Your Telephone

Your Company

Subject

Your Message

Down Payment Interim Account & Down Payment Clearing Account

An explanation / definition of what the Down Payment Interim and Clearing Accounts are for and how they are used.

When a company receives or makes a down payment, the payment amount is recorded in an interim account before it’s deducted from the final transaction. For period end auditing purposes, the transactions that make up the value of the interim accounts must be easily auditable.

In SAP Business One, postings are made to the following two interim accounts in the down payment process:

  • Down payment clearing account
  • Down payment interim account

The postings to these two accounts are automatically reconciled as follows:

  • Down Payment Request ProcessWhen you create a payment for an A/R or A/P down payment request, the down payment interim account is debited or credited with the payment amount, and the down payment clearing account is credited or debited with the payment amount excluding tax.

    When you fully or partially draw the down payment to the final A/R or A/P invoice, the down payment interim account is credited or debited with the drawn amount, and the down payment clearing account is debited or credited with the drawn amount excluding tax.

    As a result, the postings to the down payment interim account and the down payment clearing account are fully or partially reconciled accordingly.

  • Down Payment Invoice ProcessWhen you create a full or partial payment for an A/R or A/P down payment invoice, the down payment clearing account is credited or debited with the invoice amount excluding tax.

    When you fully or partially draw the down payment to the final A/R or A/P invoice, the down payment clearing account is debited or credited with the drawn amount excluding tax.

    As a result, the postings to the down payment clearing account are fully or partially reconciled accordingly.

In the above two processes, if the final A/R or A/P invoice is then fully or partially drawn to a credit memo, the postings to the two accounts generated by the A/R or A/P invoice are proportionately reversed and reconciled.

However, if either of the two accounts has been fully, manually reconciled before the automatic system reconciliation takes place, SAP Business One does not perform the system reconciliation.

If you would like further help and support with SAP Business One contact 1 Dash 1 and a qualified SAP Business One consultant will contact you to discuss your requirements.

 

Your Name (required)

Your Email (required)

Your Telephone

Your Company

Subject

Your Message