SAP Business One Consultants | IT Support | 0843 886 1449

All posts tagged SAP Business One SQL

SAP Business One – Improving The Speed Of Reports

Along with general consultancy, system implementations, training, interfaces and development of SAP Business One, one of the most requested jobs is to improve the speed of reports. Reports can be written in SQL Queries, Crystal Reports or any other reporting tool. As a rule of thumb, if a report is running slowly, it is due to either the way it was originally written or the data it is reporting on. With some out of the box thinking it is usually possible to massively reduce the way a report runs by approaching it from a different view point.

I have personally managed to reduce report run times from 90 minutes to 30 seconds with a slight rewrite of the report – anyone who has been in a situation where there screen is frozen for 90 minutes will know how frustrating it is and what a massive improvement this is.

Ways to improve your reporting:

  • Is it possible to improve the Joins?
  • Is it possible to reduce the data calls?
  • Will a database View reduce the run time?
  • Can a temporary table(s) be created to reduce the run time?
  • Is the report written in the best possible way?
  • Are there unnecessary joins?
  • Can the data be accessed from ‘better’ sources than already used?
  • Are you able to create a better report from multiple sources rather than a nested, cascading report?

If you still have slow reports after addressing the above points then contact us and we will help you reduce the run time.

SAP Business One SQL Prompt Variables

/* SELECT FROM [dbo].[OSLP] T1 */
declare @slpName as varchar(155)
/*WHERE*/
set @SlpName = /* T1.SlpName */ ‘[%0]’

/* SELECT FROM [dbo].[OCRD] T2 */
declare @CardCode as varchar(15)
/* WHERE */
set @CardCode = /* T2.CardCode */ ‘[%1]’

 

SAP Business One HANA Prompt Variables

/* select from [dbo].[OINV] T0 */

/* select from [dbo].[OSLP] T1 */

Declare FromDate timestamp;

Declare ToDate timestamp;

Declare SALESEMP char(155);

 

SALESEMP := /* T1.”SlpName” */ ‘[%0]’;

FromDate := /* T0.”DocDate” */ ‘[%1]’;

ToDate := /* T0.”DocDate” */ ‘[%2]’;

 

!! Remember to end your HANA SQL Script with a semicolon and replace the single and double quotes above which are enforced formatting !!

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

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

     

     

    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

        Here are a few basic SQL queries to be used in SAP Business One. You can copy and paste the queries into the Query window of Business One. Simply copy between from “–begin…” to “…–end” and paste into your Query window.

        SQL for Open Sales Orders

        –begin
        select t0.docnum, t0.cardcode, t0.cardname, t0.sysdoctotal
        from ordr t0
        where t0.docstatus = ‘O’
        –end

        By Date

        –begin
        select t0.docnum, t0.cardcode, t0.cardname, t0.sysdoctotal
        from ordr t0
        where t0,docstatus = ‘O’
        and t0.docdate between [%0] and [%1]
        order by t0.docdate
        –end

        SQL for Open Deliveries

        –begin
        select t0.docnum, t0.cardcode, t0.cardname, t0.sysdoctotal
        from odln t0
        where t0.docstatus = ‘O’
        –end

        SQL for Open Invoices

        –begin
        select t0.docnum, t0.cardcode, t0.cardname, t0.sysdoctotal
        from oinv t0
        where t0.docstatus = ‘O’
        –end

          Your Name (required)

          Your Email (required)

          Your Telephone

          Your Company

          Subject

          Your Message