Skip to Content
Author's profile photo Former Member

My Top SQL Queries for SAP Business One

I started out as a relative novice to SQL a few months ago. Now I use SQL queries in Business One almost daily. I wanted to share with you some of my most frequently used queries and why I like them. Just copy and paste these in to your own Query Generator

If you like SQL want to learn more we’d love to see you at the next Business One User Group at SAP UK: UK and Ireland SAP User Group – Developing, Benefiting and Serving the SAP User Community

We are on SAP 9.0 PL07

Open Invoices Not Emailed:

This query shows all AP Invoices added to SAP that are Open but have not been sent. It’s easy to forget to send invoices and this report has helped us be more efficient. Please note we are using Boyum Print and Deliver module:

SELECT t0.DocEntry, t0.CardCode, t0.CardName, t0.DocDueDate as ‘Due Date’, t0.DocCur, t0.DocTotal

FROM OINV t0

WHERE t0.DocStatus = ‘O’ and (t0.Printed = ‘N’ and (SELECT count(*)

FROM [dbo].[@BOY_85_DEL_LOG]  s0

WHERE s0.[U_BOY_id]  = t0.DocEntry and  s0.[U_BOY_OBJECTID]  = ’13’ and  s0.[U_BOY_ACTION]  = 4) = 0)

Partial Stock Take

We needed a way to list items in stock AND items not in stock for stock taking so we can run an inventory report between locations. The Bin Locations Content report in SAP only shows items 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

Items with No Default Bin Location

We want to report on all items in SAP not assigned a Default Bin Location. This has helped our Warehouse Manager to ensure ALL stock has a Default Bin:

SELECT T0.[ItemCode], T0.[WhsCode], T0.[DftBinAbs] FROM OITW T0 WHERE T0.[WhsCode]  = ‘main’ and  T0.[DftBinAbs]  is null

Purchase Invoice Analysis

The SAP Purchase Analysis report is in System Currency, mine is GBP. No use to me when I buy in EUR. This report shows all AR Invoices for a set period for a particular Business Partner in the currency on the Invoice (FC):

SELECT T0.[DocEntry], T0.[CardCode], T0.[DocDate], T0.[DocTotalFC]

FROM OPCH T0

WHERE T0.[DocDate] BETWEEN [%0] AND [%1]  AND T0.[CardCode] =[%2]

If you want to run the same report but on Purchase Orders, simply substitute OPCH for OPOR

Top 10 Customers (Thanks to Zal Parchem for this)

SELECT TOP 10 T0.CardCode, MAX(T0.Cardname) as Customer,  SUM(T0.doctotal) as “Amount(LC)”

FROM dbo.OINV T0

WHERE t0.docdate BETWEEN [%0] AND [%1]

GROUP BY T0.CardCode

Order by SUM(T0.doctotal) DESC

Sales History by Item Group

Yes I know this exists in Sales Analysis reports but I prefer my layout:

SELECT T0.[DocEntry], T0.[DocDate],T0.[CardName], T2.[ItmsGrpCod], T2.[ItemCode], T2.[ItemName], T1.[Quantity]

FROM ORDR T0  INNER JOIN RDR1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode

WHERE T2.[ItmsGrpCod] = [%0] AND T0.[DocDate] BETWEEN [%1] AND [%2]

Sales Orders with no SLP Code

Some of our Sales People forget to enter their name when adding a Sales Order. This handy report helps me identify these quickly and easily

SELECT T1.[DocNum], T1.[CardName], T1.[DocDate], T0.[SlpCode]

FROM OSLP T0  INNER JOIN ORDR T1 ON T0.SlpCode = T1.SlpCode

WHERE T0.[SlpCode] = ‘-1’

ORDER BY T0.[SlpCode]

If anyone has any suggestions on how to improve these, or you would like to share some of your own SQL Queries please feel free. It’s good to share!

Assigned tags

      42 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      I would love to learn your secret on writing queries.

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Dude, make sure you go to "View" then "System Information" it will give you the name of the tables and fields when you hover over a Business Object. Like OCRD,CardCode is BP account number.

      Then go to Query Generator, enter the names of the tables you want to query and click attributes to be brought in. SAP makes the Joins for you.

      I also refer to "Mastering SQL Queries for SAP Business One" book by Gordon Du very often

      Author's profile photo Former Member
      Former Member

      Thanks! Still trying to figure it out. I have the book also.

      Author's profile photo Former Member
      Former Member

      Hi Tim,

      I don't know anything about B1 but thought I'd share just a couple of suggestions from my years of meddling with SQL in various guises...

      Are you able to control the table aliases used by the Query generator?  If so, I'd probably re-write your statements such that:

      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

      becomes:

      select    OITW.ItemCode,

                      OITM.ItemName,

                      isnull(OIBQ.OnHandQty,0) as 'In Stock',

                      OBIN.BinCode,

                      OBIN.SL1Code,

                      OBIN.SL2Code

                      from OITW OITW

                                      inner join OITM OITM on OITW.ItemCode = OITM.ItemCode

                                      inner join OBIN OBIN on OBIN.AbsEntry = OITW.DftBinAbs

                                      left outer join OIBQ OIBQ on OITW.ItemCode=OIBQ.ItemCode

                                                      and OITW.WhsCode = OIBQ.WhsCode

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

                      order by OBIN.SL1Code,

                                       OBIN.SL2Code,

                                       OITW.ItemCode

      You will notice I've changed the t0, t1, etc. aliases to be the actual table names.  I just think this makes queries more readable, especially when you have numerous joins or where conditions.  You don't have to use the table names but something more meaningful than t0, t1, etc. is often a big help.

      Also, you may already know but the "as 'In Stock'" addition to a selected field can be used against all fields in your select statement, not just those that are calculated as part of the SQL execution.  So you could append the statement to be something like:

      select    OITW.ItemCode as 'Item Code',

                      OITM.ItemName as 'Item Description/Name',


      etc.  Sometimes this just helps to get better column headers or if you are then moving the query results into something else, such as Lumira or Excel or whatever.


      Of course, YMMV. 🙂


      Cheers,

      Gareth.

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Gareth - Thanks for the input. The end result is the same but I agree it is so much easier to read. We are a bit restricted in Business One as Query Generator does a lot of the work for us but I can see how tidying it up will make it more readable.

      Author's profile photo Former Member
      Former Member

      Hi Tim,

      Yes of course, the outcome is the same regardless and often more important than how your SQL looks.  I just know from painful experience how much easier it is to figure out issues when you can read your statement without squinting!

      I wonder if there is any mileage in using an external SQL editor, such as the Oracle SQL Developer or MySQL Workbench?  So you could use the B1 query generator to get the framework and then a bit of copying and pasting you could knock up quite extensive SQL statements.

      All depends on whether this sort of interoperability is possible and whether you need this level of control I guess.

      Cheers,

      Gareth.

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      We're looking at SQL during our next User Group meeting so I will mention it to the expert. (From USA) At present and at my level I'll just stick to the basic tools!

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Gareth - After trying this way in the real world I'm going to have to disagree with you. I've got several long queries set up that I use on Sales Orders ORDR table. If I want to run the same query but for Quotes I simply change ORDR to OQUT in the FROM section once. With your way I have to change it many times! 🙂

      I do agree yours is easier to understand though.

      Author's profile photo Johan Hakkesteegt
      Johan Hakkesteegt

      Hi Tim,

      With this table alias business you can actually have your cake and eat it too.

      Gareth used the table names as aliases, which in a sense is the same as not really using aliases. However, using the table names is indeed the most common "best practice" in query writing.

      Now about eating the cake, here is how you can make the query readable, and maintain interchangeability:

      SELECT i.ItemCode

                    ,i.ItemName

                    ,SUM(r.Quantity) AS Quantity

      FROM OITM i /*<-- "i" as in items */

                 LEFT OUTER JOIN RDR1 r /*<-- "r" as in document rows */

                                               ON i.ItemCode = r.ItemCode

                LEFT OUTER JOIN ORDR h /*<-- "h" as in document header */

                                               ON r.DocEntry = h.DocEntry

      WHERE h.DocDate = [%0]

      GROUP BY i.ItemCode

                        ,i.ItemName


      Now the aliases tell you the most important basic property of the field's identity in a glance, and you can still replace RDR1 with DLN1, or INV1, etc. without having to completely rewrite your query.

      Additionally, by the time you are ready to start using MS SQL Management Studio to write those queries, it is a lot quicker to write "i" than "OITM".

      Regards,

      Johan

      Author's profile photo Former Member
      Former Member

      Hii Experts,

      Please help with this query.

      My query :- I want to send automatic email to those BP whose Invoice due date has come. If they have made the payment then message will not send.

      Please help me.

      Thanks

      Author's profile photo Nagarajan K
      Nagarajan K

      Hi,

      Please post as discussion to get quick response.

      Thanks & Regards,

      Nagarajan

      Author's profile photo Former Member
      Former Member

      Hii Experts,

      Please help with this query.

      My query :- I want to send automatic email to those BP whose Invoice due date has come. If they have made the payment then message will not send.

      Please help me.

      Thanks

      Author's profile photo Sarah Alkhatib
      Sarah Alkhatib

      Hello!

       

      Were you able to find this? If so, can you share the query?

      Author's profile photo Agustin Marcos Cividanes
      Agustin Marcos Cividanes

      Hi Tim

      thanks for sharing your knowledge.

      Kind regards.

      Agustín Marcos Cividanes

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      No problem my friend.

      Author's profile photo Former Member
      Former Member

      Hi Tim,

      Thanks for the wonderful queries listed. Even I am  learning from the Query generator and the default tables.

      In the query of top 10 Customers listed above when we execute it the last column

      Amount(LC) is taken from which document and what exactly the amount signifies in the context.

      As that understanding is important for me to know

      Thanks & Regards

      Darshan Desai

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Hi Sir, Amount (LC) is the total amount taken from OINV AR Invoice. LC is your system currency.

      I am working on taking away in AR Credit Notes to get the figures more accurate.

      Any suggestions would be appreciated.

      Author's profile photo Former Member
      Former Member

      Hii Tim,

      Thanks for the reply I got it. In default sales analysis report based on A/R Invoice report the A/R Credit memo are deducted and it shows the deducted. So there is total difference  Even I will work on it and will post any ways thank you so much for the rest of the queries.

      Regards

      Darshan Desai

      Author's profile photo Nagarajan K
      Nagarajan K

      Hi Tim,

      Why did you select t0.docentry in Purchase invoice analysis? You can use T0.docnumber instead of t0.docentry.

      Thanks & Regards,

      Nagarajan

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Because DocEntry returns the Golden Arrows to open the base document. I don't think DocNumber does this.

      Author's profile photo Nagarajan K
      Nagarajan K

      Hi,

      Thanks for reply. If docnum is first field in the query, then golden arrow will appear.

      Refer SAP note: 1410595 - Orange arrow missing in query results

      Thanks & Regards,

      Nagarajan

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Thanks Dude, you are The Master!

      Author's profile photo Nagarajan K
      Nagarajan K

      Hi Tim,

      NO. Still learning.

      Thanks & Regards,

      Nagarajan

      Author's profile photo Navneet Dhami
      Navneet Dhami

      Hi Tim,

      Can you add your queries to wiki page we created long back ago  for generic SQL queries used for SAP

      SAP B1 SQL Tips and Tricks - Business One - SCN Wiki

      Thanks

      Neetu

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Hi Neetu - I'm just working on the format of the queries then will post to the Wiki

      I think this WiKi needs to be publicized more. So many Business One users have useful SQL it's really great if we can share.

      Author's profile photo Former Member
      Former Member

      Hi Tim,

      Thank you for sharing those queries.

      There is an important factor that we all should be aware when writing SQL queries both for reporting, alerts or approbations, but almost all of us forgot: the row lock.

      In a nutshell, when a user adds/changes a document, the rows in header and lines tables are locked.

      Even when you launch a simple report (SELECT), by the time the query reads the rows, they are locked.

      So in order to not penalising users transacting in SAP Business One, we should add "with nolock" to all the queries.

      SELECT * FROM RDR1 WITH (NOLOCK) WHERE....

      This works fine when you do not care if the data has not yet been 'committed' to the database, for instance a report showing the top 5 customers (certainly, even with a missing (Not Committed yet) row, the customer will still be in the top 5).

      But when using more precise reports, i.e. an approbation on customer credit limit that will stop (or not) a POS transaction, you would not use the (NOLOCK).

      This, I promise, will help on your database health and SQL server speed.

      Thanks and regards,

      Frédéric Bada

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Thanks Frederic. I had not heard about NOLOCK before so I will be sure to give this a try. 🙂

      Author's profile photo Former Member
      Former Member

      I didn't either until years ago when I had a WebEx session with SAP support regarding a slow server with huge reporting queries on SQL 2008.

      We are learning every day 🙂

      Looking forwards to your next blog,

      Frédéric

      Author's profile photo PIYUSH AGRAWAL
      PIYUSH AGRAWAL

      Hi all

      Pls guide me create the query of Vender Outstanding report

      Author's profile photo PIYUSH AGRAWAL
      PIYUSH AGRAWAL

      Hi , Tim Guest

      pls guide me how to purchase "Mastering SQL Queries for SAP Business One"

      Author's profile photo Nagarajan K
      Nagarajan K
      Author's profile photo Former Member
      Former Member

      Hi Tim Guest

      I am trying to make modification an excel report as from Inventory Audit Report Criteria  the table OINM & OITM I had used to find the results. I have attached a picture which I need as a output into SAP system.On the below is my desire output needed report.JPG from the Query but not getting proper one. Any one have any suggestions for this.

      Right now getting this

      report 2.PNG

      Author's profile photo Former Member
      Former Member

      I have several queries. They worked fine.

      But now the year has changed several queries don't work any more.

      I think I need to add a year selection to the query some how, but I have no idea how to do this.

      Can anyone help me, to get the queries working again?

      For example, I have this query for revenue per customer per month.

      I want to run it to show the results for the whole of 2014. An of course later on also the months in 2015.

      SELECT     TOP (100) PERCENT t1.ShortName, t2.CardName,

      SUM(CASE WHEN t1.transtype IN (13, 14) AND month(t1.duedate) = 1 AND year(duedate) = year(getdate()) THEN debit - credit END) AS Januari,

      SUM(CASE WHEN t1.transtype IN (13, 14) AND month(t1.duedate) = 2 AND year(duedate) = year(getdate()) THEN debit - credit END) AS Februari,

      SUM(CASE WHEN t1.transtype IN (13, 14) AND month(t1.duedate) = 3 AND year(duedate) = year(getdate()) THEN debit - credit END) AS Maart,

      SUM(CASE WHEN t1.transtype IN (13, 14) AND month(t1.duedate) = 4 AND year(duedate) = year(getdate()) THEN debit - credit END) AS April,

      SUM(CASE WHEN t1.transtype IN (13, 14) AND month(t1.duedate) = 5 AND year(duedate) = year(getdate()) THEN debit - credit END) AS Mei,

      SUM(CASE WHEN t1.transtype IN (13, 14) AND month(t1.duedate) = 6 AND year(duedate) = year(getdate()) THEN debit - credit END) AS Juni,

      SUM(CASE WHEN t1.transtype IN (13, 14) AND month(t1.duedate) = 7 AND year(duedate) = year(getdate()) THEN debit - credit END) AS Juli,

      SUM(CASE WHEN t1.transtype IN (13, 14) AND month(t1.duedate) = 8 AND year(duedate) = year(getdate()) THEN debit - credit END) AS Augustus,

      SUM(CASE WHEN t1.transtype IN (13, 14) AND month(t1.duedate) = 9 AND year(duedate) = year(getdate()) THEN debit - credit END) AS September,

      SUM(CASE WHEN t1.transtype IN (13, 14) AND month(t1.duedate) = 10 AND year(duedate) = year(getdate()) THEN debit - credit END) AS Oktober,

      SUM(CASE WHEN t1.transtype IN (13, 14) AND month(t1.duedate) = 11 AND year(duedate) = year(getdate()) THEN debit - credit END) AS November,

      SUM(CASE WHEN t1.transtype IN (13, 14) AND month(t1.duedate) = 12 AND year(duedate) = year(getdate()) THEN debit - credit END) AS December,

      FROM dbo.JDT1 AS t1

      LEFT OUTER JOIN dbo.OCRD AS t2 ON t2.CardCode = t1.ShortName

      LEFT OUTER JOIN dbo.OCRD AS t3 ON t3.CardCode = t2.CardName

      WHERE     (t2.CardType = 'c')

      GROUP BY t1.ShortName,t2.CardName

      ORDER BY t1.ShortName

      Author's profile photo Jos Dielemans
      Jos Dielemans

      Hello José

      Here is your Query for 2014:

      SELECT     TOP (100) PERCENT t1.ShortName, t2.CardName,

      SUM(CASE WHEN t1.transtype IN (13, 14) AND month(t1.duedate) = 1 AND year(duedate) = 2014 THEN debit - credit END) AS Januari,

      SUM(CASE WHEN t1.transtype IN (13, 14) AND month(t1.duedate) = 2 AND year(duedate) = 2014 THEN debit - credit END) AS Februari,

      SUM(CASE WHEN t1.transtype IN (13, 14) AND month(t1.duedate) = 3 AND year(duedate) = 2014 THEN debit - credit END) AS Maart,

      SUM(CASE WHEN t1.transtype IN (13, 14) AND month(t1.duedate) = 4 AND year(duedate) = 2014 THEN debit - credit END) AS April,

      SUM(CASE WHEN t1.transtype IN (13, 14) AND month(t1.duedate) = 5 AND year(duedate) = 2014 THEN debit - credit END) AS Mei,

      SUM(CASE WHEN t1.transtype IN (13, 14) AND month(t1.duedate) = 6 AND year(duedate) = 2014 THEN debit - credit END) AS Juni,

      SUM(CASE WHEN t1.transtype IN (13, 14) AND month(t1.duedate) = 7 AND year(duedate) = 2014 THEN debit - credit END) AS Juli,

      SUM(CASE WHEN t1.transtype IN (13, 14) AND month(t1.duedate) = 8 AND year(duedate) = 2014 THEN debit - credit END) AS Augustus,

      SUM(CASE WHEN t1.transtype IN (13, 14) AND month(t1.duedate) = 9 AND year(duedate) = 2014 THEN debit - credit END) AS September,

      SUM(CASE WHEN t1.transtype IN (13, 14) AND month(t1.duedate) = 10 AND year(duedate) = 2014 THEN debit - credit END) AS Oktober,

      SUM(CASE WHEN t1.transtype IN (13, 14) AND month(t1.duedate) = 11 AND year(duedate) = 2014 THEN debit - credit END) AS November,

      SUM(CASE WHEN t1.transtype IN (13, 14) AND month(t1.duedate) = 12 AND year(duedate) = 2014 THEN debit - credit END) AS December

      FROM dbo.JDT1 AS t1

      LEFT OUTER JOIN dbo.OCRD AS t2 ON t2.CardCode = t1.ShortName

      LEFT OUTER JOIN dbo.OCRD AS t3 ON t3.CardCode = t2.CardName

      WHERE     (t2.CardType = 'c')

      GROUP BY t1.ShortName,t2.CardName

      ORDER BY t1.ShortName

      I changed the year in 2014 and took the comma away after the word December.

      Next time open a new discussion. That suits better for questions!

      Author's profile photo Jos Dielemans
      Jos Dielemans

      Instead of 2014 as a fixed query you can better use this dynamic query for the same results for the past year:

      SELECT     TOP (100) PERCENT t1.ShortName, t2.CardName,

      SUM(CASE WHEN t1.transtype IN (13, 14) AND month(t1.duedate) = 1 AND year(duedate) = (year(getdate())-1) THEN debit - credit END) AS Januari,

      SUM(CASE WHEN t1.transtype IN (13, 14) AND month(t1.duedate) = 2 AND year(duedate) = (year(getdate())-1) THEN debit - credit END) AS Februari,

      SUM(CASE WHEN t1.transtype IN (13, 14) AND month(t1.duedate) = 3 AND year(duedate) = (year(getdate())-1) THEN debit - credit END) AS Maart,

      SUM(CASE WHEN t1.transtype IN (13, 14) AND month(t1.duedate) = 4 AND year(duedate) = (year(getdate())-1) THEN debit - credit END) AS April,

      SUM(CASE WHEN t1.transtype IN (13, 14) AND month(t1.duedate) = 5 AND year(duedate) = (year(getdate())-1) THEN debit - credit END) AS Mei,

      SUM(CASE WHEN t1.transtype IN (13, 14) AND month(t1.duedate) = 6 AND year(duedate) = (year(getdate())-1) THEN debit - credit END) AS Juni,

      SUM(CASE WHEN t1.transtype IN (13, 14) AND month(t1.duedate) = 7 AND year(duedate) = (year(getdate())-1) THEN debit - credit END) AS Juli,

      SUM(CASE WHEN t1.transtype IN (13, 14) AND month(t1.duedate) = 8 AND year(duedate) = (year(getdate())-1) THEN debit - credit END) AS Augustus,

      SUM(CASE WHEN t1.transtype IN (13, 14) AND month(t1.duedate) = 9 AND year(duedate) = (year(getdate())-1) THEN debit - credit END) AS September,

      SUM(CASE WHEN t1.transtype IN (13, 14) AND month(t1.duedate) = 10 AND year(duedate) = (year(getdate())-1) THEN debit - credit END) AS Oktober,

      SUM(CASE WHEN t1.transtype IN (13, 14) AND month(t1.duedate) = 11 AND year(duedate) = (year(getdate())-1) THEN debit - credit END) AS November,

      SUM(CASE WHEN t1.transtype IN (13, 14) AND month(t1.duedate) = 12 AND year(duedate) = (year(getdate())-1) THEN debit - credit END) AS December

      FROM dbo.JDT1 AS t1

      LEFT OUTER JOIN dbo.OCRD AS t2 ON t2.CardCode = t1.ShortName

      LEFT OUTER JOIN dbo.OCRD AS t3 ON t3.CardCode = t2.CardName

      WHERE     (t2.CardType = 'c')

      GROUP BY t1.ShortName,t2.CardName

      ORDER BY t1.ShortName

      Good Luck Jos Dielemans

      Author's profile photo Former Member
      Former Member

      Hi, Just found your 10 fav queries they are dated 2014 so not sure if you are still logging on. With regard to top 10 customers - which works perfectly - what I need is not a summed total but a list of the invoices that go to make up the total. Is this possible?

      thanks

      Author's profile photo Former Member
      Former Member

      Hi, Can any one test my query to check if an invoice has been sent using both boyum and SAP ways of sending. We need to query to tell if an invoice has been emailed or printed.

      SELECT

      DISTINCT

      t0.DocEntry,

      t0.DocNum,

      t0.CardCode,

      t0.CardName,

      t0.DocDate,

      t0.DocDueDate as 'Due Date',

      T1.U_BOY_DATE,

      t0.DocCur,

      t0.DocTotal,

      T0.DocStatus,

      T0.CANCELED,

      T0.Printed,

      CASE

      WHEN T1.[U_BOY_Action] = 2 THEN 'Printed'

      WHEN T1.[U_BOY_Action] = 4 THEN 'Emailed'

      ELSE CONVERT(VARCHAR,T1.[U_BOY_Action]) End AS 'Boyum_Action',

      T3.WasSent AS 'SAP_Sent',

      T4.Confirmed2

      FROM OINV t0

      LEFT JOIN [dbo].[@BOY_85_DEL_LOG]  T1  ON T0.DocNum = T1.[U_BOY_id]

      LEFT JOIN dbo.OALR T2 ON CONVERT(VARCHAR,T0.DocNum) = LEFT(CONVERT(VARCHAR,T2.MsgData),9)

      LEFT JOIN OAOB T3 ON T2.Code = T3.AlertCode

      LEFT JOIN AOB1 T4 ON T3.AlertCode = T4.AlertCode 

      Author's profile photo Danielle Ostach
      Danielle Ostach

      Just to share one that I created, this gives usage for items by year.

      It was restricted to usage defined aS Issues to production, shipped via delivery or shipped via direct invoice.

      It would not be hard though to modify this to fact in returns, credits or inventory adjustments.  (just change the sum to outqty-inqty instead of outqty and add the extra doctypes to the 'where' clause')

      It runs fast for company that gets about 15-100 orders a day.

      select oinm.itemcode, oitb.itmsgrpnam,docyear = YEAR(docdate),

          JanQty = sum(CASE WHEN Month(docdate) = 1 THEN outqty else 0 END),

          febQty = sum(CASE WHEN Month(docdate) = 2 THEN outqty else 0 END),

          marQty = sum(CASE WHEN Month(docdate) = 3 THEN outqty else 0 END),

          AprQty = sum(CASE WHEN Month(docdate) = 4 THEN outqty else 0 END),

          MayQty = sum(CASE WHEN Month(docdate) = 5 THEN outqty else 0 END),

          JunQty = sum(CASE WHEN Month(docdate) = 6 THEN outqty else 0 END),

          JulQty = sum(CASE WHEN Month(docdate) = 7 THEN outqty else 0 END),

          AugQty = sum(CASE WHEN Month(docdate) = 8 THEN outqty else 0 END),

          SepQty = sum(CASE WHEN Month(docdate) = 9 THEN outqty else 0 END),

          OctQty = sum(CASE WHEN Month(docdate) = 10 THEN outqty else 0 END),

          NovQty = sum(CASE WHEN Month(docdate) = 11 THEN outqty else 0 END),

          DecQty = sum(CASE WHEN Month(docdate) = 12 THEN outqty else 0 END)

      FROM OINM

      inner join oitm on oinm.itemcode = oitm.itemcode

      inner join oitb on oitb.itmsgrpcod = oitm.itmsgrpcod

      WHERE (transtype =59 or transtype = 13 OR  transtype = 15)

      group by oinm.itemcode, oitb.itmsgrpnam, YEAR(docdate)

      Order by oinm.itemcode, year(docdate)

      Author's profile photo Sapna Chaudhari
      Sapna Chaudhari

      Hi,

      Can anyone help me for my query to restrict the service call to add for perticular customer who already have service call in open status...

      I tried the following query in SP_SBO_TransactionNotification

      IF ((@transaction_type in ( N'A')) AND @Object_type = '191')

      Begin
      IF EXISTS((SELECT * FROM OSCL T0 WHERE T0.[customer] = @list_of_cols_val_tab_del AND T0.status<>(-1)))
      set @TotalCall=(SELECT count(T0.callID) FROM OSCL T0 WHERE T0.[customer] = @list_of_cols_val_tab_del AND T0.status<>(-1))

      IF ( @TotalCall>1)
      SELECT @error = 2, @error_message = 'There is service call open for Customer'

      End

      I just wan to restrict to add more than one service call for perticular customer who already have open service call.

       

      Regards,

      Sapna

       

       

      Author's profile photo Victor Tabares
      Victor Tabares

      Hi Sapna Chaudhari

      The problem that I see in the query is that the value you receive is a CallID and you equal it to the customer

      Try this

      DECLARE @OpenCall int;

      SELECT TOP 1 @OpenCall = callID FROM OSCL T0 WHERE T0.[customer] = (SELECT customer FROM OSCL WHERE callID = @list_of_cols_val_tab_del AND T0.status<>(-1));

      SELECT @OpenCall

      IF(@OpenCall IS NOT NULL)
      SELECT @error = 2, @error_message = 'The service call '+CAST(@OpenCall AS NVARCHAR) +' is open for Customer'

      Regards,

      Víctor Tabares

       

      Author's profile photo Sapna Chaudhari
      Sapna Chaudhari

      Hi Víctor Tabares,

      Thank you...

      But the above query you suggested not allow me to add the record even though I create new record for new customer..

      I want to block customer who already have open service call...

      Author's profile photo Sapna Chaudhari
      Sapna Chaudhari

      Hi Victor Tabares,

      The problems get solved.

      Thank you.Your query help me lots.

      Regards,

      Sapna