SAP Business One Consultants | IT Support | 0843 886 1449

All posts tagged SAP Business One

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

 

Sage Line 50 Upgrade

Most young or Start Up businesses buy Sage Line 50 but soon find that they outgrow the functionality available. Not to fear!

Upgrade from Sage Line 50 to SAP Business One and save 15%

If your small or medium sized business is running on Sage Line 50 then you can upgrade to the functionality rich SAP Business One software suite which will grow with your business as it grows… and grows. With integrated accounting (incl multi-currency, advanced nominal structure), stock control including Serial and Batch numbering and multiple warehouses, customer relationship management, production, sales and purchasing (plus much more!).

Sage Line 50 Upgrade

A Slice of life

If we could, we’d give you the software because we know it will help your business. That’s quite a statement but you can benefit and we can show you how.

With an amazing offer like this, is it worth missing out on something so good?

This offer has a limited time period so get in touch now!

…maybe you’d like SAP Business One HANA? Instant processing with In-memory computing. Want to know more?

Your Name (required)

Your Email (required)

Your Telephone

Your Company

Subject

Your Message

 

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

If you are interested in writing your own SQL queries for SAP Business One the following will assist you.

Basics of SQL Writing for SAP Business One

SQL query and related terms

Before going into the details of SQL query, I would like to briefly introduce some basic database concepts because SQL is a database language for managing data in Relational Database Management Systems(RDBMS).

RDBMS

RDBMS is a Database Management System that is based on the relation model. Relational here is a key word for RDBMS. You will find that data is stored in the form of Tables and the relationship among the data is also stored in the form of tables for RDBMS.

Table

Table is a key component within a database. One table or a group of tables represent one kind of data. For example, table OSLP within SAP Business One holds all Sales Employee Data. Tables are two-dimensional data storage place holders. You need to be familiar with their usage and their relationships with each other. If you are familiar with Microsoft Excel, the worksheet in Excel is a kind of two-dimensional table.

Table is also one of the most often used concepts. Relationships between each table may be more important than tables themselves because without relation, nothing could be of any value. One important function within SAP Business One is allowing User Defined Table (UDT). All UDTs start with “@”.

SAP Business One training

Field

A field is the lowest unit holding data within a table. A table can have many fields. It is also called acolumn. Field and column are interchangeable. A table is comprised of records, and all records have the same structure with specific fields. One important concept in SAP Business One is User Defined Field(UDF). All UDFs start with U_.

SQL

SQL is often referred to as Structured Query Language. It is pronounced as S-Q-L or as the word “Sequel”. There are many different revisions and extensions of SQL. The current revision is SQL: 2008, and the first major revision is SQL-92. Most of SQL extensions are built on top of SQL-92.

T-SQL

Since SAP Business One is built on Microsoft SQL Server database, SQL here means Transact-SQL or T-SQL in brief. It is a Microsoft’s/Sybase’s extension of general meaning for SQL.

Subsets of SQL

There are three main subsets of the SQL language:

  • Data Control Language (DCL)
  • Data Definition Language (DDL)
  • Data Manipulation Language (DML)

Each set of the SQL language has a special purpose:

  • DCL is used to control access to data in a database such as to grant or revoke specified users’ rights to perform specified tasks.
  • DDL is used to define data structures such as to create, alter, or drop tables.
  • DML is used to retrieve and manipulate data in the table such as to insert, delete, and update data.Select, however, becomes a special statement belonging to this subset even though it is a read-only command that will not manipulate data at all.

Query

Query is the most common operation in SQL. It could refer to all three SQL subsets.

You have to understand the risks of running any Add, Delete, or Update queries that could potentially alter system tables even if they are User Defined Fields. Only SELECT query is legitimate for SAP Business One system table.

The above extract provides the basics of SQL, to read the full article, read it here.

 

SAP Business One table for SQL Report writing

Administration

DunningLetters Dunning levels definition ODUN
Recipients Message recipient list AOB1
Messages Messages OALR
Currencies Currency codes OCRN
UserPermissionTree User authorizations for add-on forms OUPT
UserPermissionForms User permission forms UPT1
Users Users of SAP Business One application OUSR
UserPermission User authorizations USR3
AddonChecking(SBO-COMMON Database Only runned this table) Overall Addon Path Defining SARI
User Queries User Queries OUQR

Finance

Budget Budget management OBGT
Budget_Lines Budget item details of an account BGT1
BudgetDistribution Budget distribution methods OBGD
BudgetScenarios Budget scenarios OBGS
ChartOfAccounts G/L accounts OACT
JournalVouchers Journal vouchers OBTD
JournalEntries_Lines Journal transaction – rows JDT1
JournalEntries Journal transaction OJDT
SalesTaxAuthorities Sales tax jurisdictions OSTA
SalesTaxAuthoritiesTypes Sales tax authority types OSTT
SalesTaxCodes Inclusive sales tax codes OSTC

Marketing Documents and Receipts
(Sales Opportunities, Sales A/R, and Purchasing A/P)

Document_Lines Delivery note rows DLN1
Document_LinesAdditionalExpenses Delivery note rows – expenses DLN2
DocumentsAdditionalExpenses Delivery note – expenses DLN3
AdditionalExpenses Defines additional expenses OEXD
Document_Lines Draft – rows DRF1
Document_LinesAdditionalExpenses Draft rows – expenses DRF2
DocumentsAdditionalExpenses Draft – expenses DRF3
Document_Lines Invoice – rows INV1
Document_LinesAdditionalExpenses Invoice rows – expenses INV2
DocumentsAdditionalExpenses Invoice – expenses INV3
Document (oDeliveryNote) Delivery – header ODLN
Documents Drafts – header ODRF
Documents (oInvoice) Invoice – header OINV
Document (oPurchaseInvoices) AP invoice – header OPCH
Document (oPurchaseDeliveryNotes) Goods receipt purchase order – header OPDN
Document (oPurchaseOrders) Purchase order – header OPOR
Document (oQuotation) Quotation – header OQUT
Document (oReturn) Returns – header ORDN
Document (oOrder) Order – header ORDR
Document (oCreditNote) AR credit memo – header ORIN
Document (oPurchaseCreditNotes) AP credit memo – header ORPC
Document (oPurchaseReturns) Goods return – header ORPD
Document_Lines AP invoice – rows PCH1
Document_LinesAdditionalExpenses AP invoice rows – expenses PCH2
DocumentsAdditionalExpenses AP invoice – expenses PCH3
Document_Lines Goods receipt purchase order – row PDN1
Document_LinesAdditionalExpenses Goods receipt purchase order rows – expenses PDN2
DocumentsAdditionalExpenses Goods receipt purchase order – expenses PDN3
Document_Lines Purchase order – rows POR1
Document_LinesAdditionalExpenses Purchase order rows – expenses POR2
DocumentsAdditionalExpenses Purchase order – expenses POR3
Document_Lines Quotation – rows QUT1
Document_LinesAdditionalExpenses Quotation rows – expenses QUT2
DocumentsAdditionalExpenses Quotation – expenses QUT3
Document_Lines Returns – rows RDN1
Document_LinesAdditionalExpenses Return rows – expenses RDN2
DocumentsAdditionalExpenses Returns – expenses RDN3
Document_Lines Order – rows RDR1
Document_LinesAdditionalExpenses Order rows – expenses RDR2
DocumentsAdditionalExpenses Order – expenses RDR3
Document_Lines Credit memo – rows RIN1
Document_LinesAdditionalExpenses Credit memo rows -Expenses RIN2
DocumentsAdditionalExpenses Credit memo – expenses RIN3
Document_Lines AP credit memo – rows RPC1
Document_LinesAdditionalExpenses AP credit memo rows – expenses RPC2
DocumentsAdditionalExpenses AP credit memo – expenses RPC3
Document_Lines Goods return – rows RPD1
Document_LinesAdditionalExpenses Goods return rows – expenses RPD2
DocumentsAdditionalExpenses Goods return – expenses RPD3
BatchNumbers Batch number for item OIBT
Industries Industries list for sales opportunities OOND
Relationships Relationships for sales opportunities OORL
SerialNumbers Serial numbers for items OSRI
WithholdingTaxData Withholding tax data INV5
TaxInvoices (botit_Invoice) Master header data of a tax invoice of sales document OTSI
TaxInvoice_Lines (botit_Invoice) Tax invoice of sales document – rows TSI1
TaxInvoices (botit_Payment) Master header data of a tax invoice of purchase document OTPI
TaxInvoice_Lines (botit_Payment) Tax invoice of purchase document – rows TPI1
TaxInvoices (botit_JournalEntry) Master header data of a tax invoice of journal entry document OTXD
TaxInvoice_Lines (botit_JournalEntry) Tax invoice of journal entry document – rows TXD1

Business Partners

ActivityLocations Business partner activity locations OCLO
ActivityStatus Statuses for Task type activities OCLA
ActivityTypes Business partner activity types OCLT
AlternateCatNum Customer or vendor catalog number OSCN
BusinessPartners Business partners OCRD
BPAddresses Business partner addresses CRD1
BPPaymentMethods Business partner payment methods CRD2
BPAccountReceivablePayble Business partner account receivable payable CRD3
BPWithholdingTax Business partner withholding tax CRD4
BPPaymentDates Business partners payment dates CRD5
BPBankAccounts Business partners bank accounts OCRB
Contacts Contact customers and vendors OCLG
ContactEmployees Contact persons OCPR
SalesOpportunities Sales opportunities OOPR
SalesOpportunitiesLines Sales opportunity – rows OPR1
SalesOpportunitiesPartners Sales opportunity partners OPR2
SalesOpportunitiesCompetition Sales opportunity competition OPR3
SalesOpportunitiesInterests Sales opportunity interests range OPR4
SalesOpportunitiesReasons Sales opportunity failure reasons OPR5
Territories Territory segmentations OTER

Inventory and Production

Document_Lines Goods issue – rows IGE1
Document_LinesAdditionalExpenses Goods issue rows – expenses IGE2
DocumentsAdditionalExpenses Goods issue – expenses IGE3
Document_Lines Goods receipt – rows IGN1
Document_LinesAdditionalExpenses Goods receipt rows – expenses IGN2
DocumentsAdditionalExpenses Goods receipt – expenses IGN3
Document (oInvetoryGenExit) Goods issue OIGE
Document (oInvetoryGenEntry) Goods receipt OIGN
StockTransfer Stock transfers OWTR
StockTransfer_Lines Stock transfers – rows WTR1
ProductTrees_Lines Product tree – child ITT1
ProductTrees Product tree OITT
WorkOrders Work instructions OWKO
WorkOrder_Lines Work instructions – rows WKO1
Items_Prices Items – prices ITM1
Items Items OITM
ItemWarehouseInfo Items – warehouse OITW
ItemGroups Item groups definition OITB
MaterialRevaluation Stock revaluation OMRV
MaterialRevaluation_Lines Stock revaluation – rows MRV1
PackagesTypes Package types for deliveries OPKG
PriceLists Price lists management OPLN
StockTaking Items – warehouse OITW
SpecialPrices Special prices OSPP
SpecialPricesDataAreas Special prices for specified periods SPP1
SpecialPricesQuantityAreas Special prices for specified quantities SPP2
Warehouses Warehouses definition OWHS

MRP

SalesForecast Sales forecast OFCT
SalesForecast_Lines Sales forecast – rows FCT1

Banking

Payments Receipt ORCT
Payments Vendor payment OVPM
Payments_Checks Receipt – checks RCT1
Payments_Invoices Receipt – invoices RCT2
Payments_CreditCards Receipt – credit vouchers RCT3
Payments_Accounts Receipt – account list RCT4
Payments_Checks Payments – check rows VPM1
Payments_Invoices Payments – invoices VPM2
Payments_CreditCards Payments – credit memos VPM3
Payments_Accounts Payments – list of accounts VPM4
ChecksforPaymentLines Checks for payment – rows CHO1
BankPages External bank statement OBNK
ChecksforPayment Check for payment OCHO
PaymentTermsTypes Payment term types OCTG
PaymentRunExport Payment results table OPEX
PaymentRunExport_Lines Payment results table – rows PEX1
BillOfExchange Bill of exchange table OBOE
BillOfExchangeTransaction Bill of exchange transactions table OBOT
BillOfExchangeTransaction_Lines Bill of exchange transactions table – rows BOT1

Service

ContractTemplates Contract template (CRM) OCTT
CustomerEquipmentCards Customer equipment card OINS
KnowledgeBaseSolutions Knowledge base solution OSLT
Queue Queues list for assigning to a service calls OQUE
QueueMembers Queue member users QUE1
ServiceContracts Service contract OCTR
ServiceContracts_Lines Service contract – rows CTR1
ServiceCalls Service call OSCL
ServiceCallActivities Service call activities OCLG
ServiceCallInventoryExpenses Service call inventory expenses INV1

Human Resources

EmployeesInfo Employees information OHEM
EmployeeAbsenceInfo Employee absence information HEM1
EmployeeEducationInfo Employee education HEM2
EmployeeReviewsInfo Employee reviews HEM3
EmployeePrevEmpoymentInfo Employee previous employment information HEM4
EmployeeRolesInfo Employee rolls information HEM6
Teams List of teams OHTM
TeamMembers Employee membership role in a team HTM1
If you would like to receive SQL report writing training for SAP Business One, contact 1 Dash 1 by entering your details below or calling us on 0844 858 1080.

Your Name (required)

Your Email (required)

Your Phone

Subject

Training Course Topic

Your Message

SAP Business One on Hana leaves every other Small to Medium business software behind with it’s instant in-memory computing!

So what does it mean for you?

SAP Business One on Hana

Speed, accessibility and reporting within seconds for your business; Locally or Globally! Reports that take you days in other ERP software systems will take seconds on SAP Business One on Hana. This means that you will save money and time on the reports themselves but will also give you the edge over your competition.

How? You ask.

Let’s say a trend begins for one of your products that one of your competitors sell. You see the trend instantly and can react to the increase within minutes, maximising on sales and profitability. It will probably be at least a week to a month before your competitor even sees the increase!

What could this mean to your business? Find out by contacting us.

Get ahead, get SAP Business One on Hana.


 

SAP Business One on Hana

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

Videos introducing SAP Business One – A software package for Small Businesses.

Here is a more comprehensive video highlighting the financials functionality.

 

Your Name (required)

Your Email (required)

Your Telephone (required)

Company (required)

Your Website

Are you using SAP Business One already?

 Yes, I am reaping the benefits. No, but I am interested. I don't know.

Subject

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

SAP Definition

SAP stands for Systems Applications and Products in Data Processing.

It was Founded in 1972 by Wellenreuther, Hopp, Hector, Plattner and Tschira.

SAP by definition is also the name of the ERP (Enterprise Resource Planing) software as well the name of the company.

An SAP system comprises of a number of fully integrated modules which covers virtually every aspect of the management of a business.

SAP has solutions for micro to multi-billion dollar companies. These include; R3, All in One and Business One.

Want to know more about SAP Business One? Enter your details below and one of the 1 Dash 1 team will contact you shortly.

Your Name (required)

Your Email (required)

Your Telephone

Your Company

Subject

Your Message