1 2 Previous Next 24 Replies Latest reply on Jan 2, 2018 5:59 PM by Danny Pancratz Go to original post
      • 15. Re: Useful SAP B1 queries
        Matt Roberts

        That's a useful query, Rob - thanks for adding it.

         

        Here is a query to answer the question: which customers bought which products? This shows each customer and the individual invoices upon which these items were purchased, but it would certainly be possible only to select DISTINCT customers, or aggregate by customer groups. Queries like this, if one is looking at larger groups and would like to switch in and out different customer segments (RFM, SAP customer group, demographic data, geographic data, etc.), would most likely be better for an OLAP cube, although this might be somewhat more difficult to build on an SAP database, given that they do not manage their "foreign keys" through the database structure.

         

        But if you have no access to this sort of solution, there is always a query such as...

         

        SELECT T1.CardCode, T1.CardName, T0.DocNum FROM OINV T0  INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode INNER JOIN INV1 T2 ON T0.DocEntry = T2.DocEntry INNER JOIN OITM T3 ON T2.ItemCode = T3.ItemCode WHERE T2.ItemCode Like '%%[%0]%%' ORDER BY T1.CardName

         

        Or, a more "generalized" query that will spot SAP customer groups, and do something like the quantity of an item sold for each group:

         

        SELECT T4.GroupName, SUM(T2.Quantity) AS 'Quantity', SUM(T2.LineTotal) AS 'Sales'
        FROM OINV T0  INNER JOIN OCRD T1
        ON T0.CardCode = T1.CardCode
        INNER JOIN INV1 T2
        ON T0.DocEntry = T2.DocEntry
        INNER JOIN OITM T3 ON T2.ItemCode = T3.ItemCode
        INNER JOIN OCRG T4 ON T1.GroupCode = T4.GroupCode
        WHERE T2.ItemCode Like '%%[%0]%%'
        GROUP BY T4.GroupName
        ORDER BY T4.GroupName

         

        This will show you the SAP group code name, the quantity, and the sales of a particular item, so that you can see which customer groups are purchasing how much of a particular item. This query is not restricted by time, but it would be a simple matter to add that to the WHERE clause, and even make that a variable, by cut & pasting it from some of the other queries in this thread.

        • 16. Re: Useful SAP B1 queries
          Zal Parchem

          Let’s say your Head of Finance suddenly discovers there are major payment issues with Customers from a specific Customer Group – maybe the “Small Accounts”.  The head’s final decision is that this Customer Group’s Sales Orders must be reviewed and approved before they are released for production and/or shipment.  There is no standard way in SAP B1 to do this type of check as the Approval Procedure is based solely on numeric/dollar values.  So the best approach is to first write SQL and then set up the approval procedure.

           

          Try doing this in your SAP B1 OEC Demo System since this is where I am picking up the specific codes used in the SQL example.  So here is the SQL and I will explain some things:

           

          --APR 1AD Appr Proc for Sls Ord Ver 1 ZP 2010 02 16

           

          --DESCRIPTION: SQL places Sales Order into the approval process. 

           

          --USAGE: Approval Stage STG01SO1 and Approval Template TMP01SO1.

           

          --VERSION(s):

          --Version 1 Zal Parchem 2010 02 16 for Small Accounts only

           

          SELECT 'TRUE'

           

          FROM OCRD T0

           

          WHERE T0.CardCode = $[$4.0.0] AND T0.GroupCode = '104'

           

           

          The SQL must return “TRUE” for the approval procedure to work correctly. The SQL is checking the CardCode field on the OCRD table (BP Header Information) to the Customer Number on the Sales Order (“$[$4.0.0]” refers to the “Customer” field on the Sales Order).  Although the Business Partner Master Data Window shows “Small Accounts” in the “Group” field, you must use the Group Code in the SQL. (How do you find the codes?  Use Query Generator with OCRG table and you can find the codes there.)  In this example, “104” is the “Small Accounts” Customer Group.

           

          Now save this query and, if you are on the ball, make sure you include the version in your SQL as I have done above because you know financial folks can change their mind often.  You will see why later in this example.  Then set up the Approval Stage and Approval Template in the Administration Module - review SAP's User Guide a bit before doing the set ups.  NOTE:  when setting up the Approval Template Launch Approval Procedure with “When the Following Applies” under the “Terms” Tab, be certain that NONE of the boxes are checked under the “Choose” column – this is very important or you can get the baffling “No Ratio Has Been Defined [Message 3261-7]” error message while adding the template.  You wrap up with a demo of the approval process and show the users the Messages function and the Document Drafts Report, so everyone is working smoothly.

           

          For weeks everything is going fine with the Small Customer Group Approval Procedure, but then the Head of Finance decides the approval must now be extended to the “High Tech” Customer Group as well.  I told you they change their minds often!  So what do you do?  A slight change in the SQL is all that is needed:

           

          --VERSION(s):

          --Version 1 Zal Parchem 2010 02 16 for Small Accounts only

          --Version 2 Zal Parchem 2010 03 15 added High Tech

           

          WHERE T0.CardCode = $[$4.0.0] AND (T0.GroupCode = '104' OR T0.GroupCode = '106')

           

          The first part of “$[$4.0.0]” we have already discussed, but let’s pay close attention to the second part.  Here is what the WHERE statement is now saying:

           

          The CardCode on the OCRD table is equal to the Customer on the ORDR table and the following condition is met: the OCRD GroupCode is either “104” or “106”.  SQL reads the condition in the parenthesis as one unit to meet the WHERE statement’s requirement.  You must use the parenthesis to do this and you can put as many “OR” parts within the parenthesis as you want.  This structure has to do with Boolean Logic which sometimes turns out to be a headache and a bunch of “Bool”, but that is the way SQL is!!!

           

          You change and test everything in Test and save/remove versions in Production – in Production you are saving new versions and removing the old, right???

           

          Months pass and the Head of Finance remains happy.  Then the head decides ALL Customers must go through the approval process except for those assigned to the “Large Account” Customer Group.   A quick change in the SQL gets things rolling:

           

          --VERSION(s):

          --Version 1 Zal Parchem 2010 02 16 for Small Accounts only

          --Version 2 Zal Parchem 2010 03 15 added High Tech

          --Version 3 Zal Parchem 2010 07 06 every Cust Grp except Large Accounts

           

          WHERE T0.CardCode = $[$4.0.0] AND T0.GroupCode < > = '102'

           

          IF in the following months the Head of Finance asks you to make another change to the mix of Customer Groups, say you have done a pretty good job up to this point, but the new requirements will be pretty complicated and the company needs to pay for you to get some extra training in SQL.  Besides, you deserve it with all these changes, and then you can share what you learned in this discussion!!!

           

          Take care all and hope this helps - Zal

          • 17. Re: Useful SAP B1 queries

            Mat,

            I am new to Queries and SAp BUSINESS ONE

            I saw some of  queries, I need your little guidance

            What is the number i can reach you out

            Kind Regards Sunil

            • 18. Re: Useful SAP B1 queries

              Hi, Matt..

               

              thanks for your great ideas!..

              let me know how can I write a sql for each month material cost, labor cost  of each product?

               

              please show me the way..

               

              my email address is mfureten@hotmail.com

               

              thanks in advance...

              mehmet ureten

              • 19. Re: Useful SAP B1 queries

                We need to skip the non inventory item for QC approval.

                Based on the OITM inventory Y/N provide the query to add in GRN approval.

                in 10 GRN rows if any one item is haveing the inventory Yes it need to proceed for apprvoal.

                in 10 grn rows all items are non-inventory items, skip the approval

                 

                • 20. Re: Useful SAP B1 queries

                  Hi Matt,

                   

                  can we create a query which provide average number of days for pick date and delivery date and by customer only.

                   

                  Regards.

                  Shahzad Ahmed

                  • 21. Re: Useful SAP B1 queries
                    Danny Pancratz

                    Hi, Shahzad.

                     

                    This post from Matt was started in 2009 and I'm not sure you'll get an answer from him. However, I've posted your question in the Q&A area of our community forum and you should see newer responses there from our community members and subject matter expert.

                     

                    Here's the link: Can we create a query which provide average number of days for pick date and delivery date and by customer only?

                    • 22. Re: Useful SAP B1 queries
                      Danny Pancratz

                      Hi, Chandra.

                       

                      This post from Matt was started in 2009 and I'm not sure you'll get an answer from him. However, I've posted your question in the Q&A area of our community forum and you should see newer responses there from our community members and subject matter expert.

                       

                      Here's the link: How to skip the non-inventory item for QC approval?

                      • 23. Re: Useful SAP B1 queries
                        Danny Pancratz

                        Hi, Mehmet.

                         

                        This post from Matt was started in 2009 and I'm not sure you'll get an answer from him. However, I've posted your question in the Q&A area of our community forum and you should see newer responses there from our community members and subject matter expert.

                         

                        Here's the link:How can I write a SQL for each month material cost, labor cost of each product?

                        • 24. Re: Useful SAP B1 queries
                          Danny Pancratz

                          Hi, Sunil.

                           

                          This post from Matt was started in 2009 and I'm not sure you'll get an answer from him. However, I've posted your question in the Q&A area of our community forum and you should see newer responses there from our community members and subject matter expert.

                           

                          Here's the link: I'm new to queries and SAP Business One - how do I get started?

                          1 2 Previous Next