1 2 Previous Next 20 Replies Latest reply on Jun 23, 2006 7:10 PM by Andee DiCola

    SQL Query for Monthly Sales Report

    Frank Romano

      We implemented SAP B1 2005 and I try to create Monthly Sales Report by Document in Query Manager.
      We are using just A/R Invoices and A/R Credit Memo.

      I was not able to get result which match system P&L Revenue total.
      Can anybody help with this SQL ?

      Thank you.


        • 1. SQL Query for Monthly Sales Report
          Michael Shen

          To my understanding, just using A/R Invoices and A/R Credit Memo alone will not make the Sales Report match the P/L Revenue total.
          P/L revenue total may involve Incoming Payment, Journal Entry, Outgoing Payment even AP believe or not. In our company, I wrote the Sales Report include all above.
          The bottom line is that Sales Report has to include all the transactions that were posted to Revenue G/L Accounts in order to match the P/L.
          Sorry, I can not attach the query because it'wasn't just one simple query, I actually created a bunch of views in the SQl db and combine them all together to make up the final view...
          Hint: you can check if the transaction is a P/L transaction in tables OJDT, JDT1 and OACT ...

          Michael Shen

          • 2. SQL Query for Monthly Sales Report
            Andee DiCola

            Do you have simpler version of the view commands in SQL Server. I am trying to use that as well with the AR credit memos and AR invoices and I can't get the syntax correct. I thought if I could see an example I could figure it out.

            Andee DiCola

            • 3. SQL Query for Monthly Sales Report
              Michael Shen

              Following is the statement of one of the views to create the Incoming Payment part of the Sales Report.

              CREATE VIEW dbo.RevenuesInPmt
              SELECT 'In Coming Payment' AS TransType, dbo.JDT1.Ref1 AS DocNum, dbo.ORCT.CardCode,dbo.ORCT.CardName,
              dbo.OACT.AcctName AS ItemCode,dbo.OJDT.Memo AS Dscription,dbo.ORCT.DocDate, -(dbo.JDT1.Debit-dbo.JDT1.Credit) AS ExtAmt,1 AS Quantity,
              -(dbo.JDT1.Debit-dbo.JDT1.Credit) AS UnitPrice, '' AS SalesPerson, '' AS CustRef
              FROM dbo.JDT1 LEFT OUTER JOIN
              dbo.OJDT ON dbo.JDT1.TransId = dbo.OJDT.TransId LEFT OUTER JOIN
              dbo.OACT ON dbo.JDT1.Account = dbo.OACT.AcctCode
              LEFT OUTER JOIN dbo.ORCT ON dbo.JDT1.Ref1 = dbo.ORCT.DocNum
              WHERE dbo.OACT.GroupMask = 4 AND dbo.OJDT.TransType = 24

              Note: dbo.OACT.GroupMask = 4 is very important, cuz it restricts result set to return only "Revenue related" records.

              Michael Shen

              • 4. SQL Query for Monthly Sales Report
                Frank Romano

                Hi Michel

                In our situation P&L Revenue total is represent only A/R Invoices and A/R Credit Memo.
                I am trying to match Sustem Report : Sales A/R - Sales reports - Sales Analysis - Customers

                This report is agree with GL Revenue accounts total.
                I review Revenue GL accounts by transaction and find anly entry from Invoice processing (Credits) and
                Debits from Credit Memos.

                Other Question - When I am runing same system report for Items - total is diferent.
                Any idea?

                Thank you.

                Alex Gan

                • 5. SQL Query for Monthly Sales Report
                  Michael Shen

                  Hi,Alex :
                  Well, I think if you run Customer Sales Analysis report, it will include those Service Invoices and Service Credit memo too. I guess that's why it didn't agree only the item sales analysis...

                  Michael Shen

                  • 6. SQL Query for Monthly Sales Report
                    Gregory Utley


                    We've had the same problem getting an Invoices & Credit Memos report or query, among other reports and queries, to match the GL account balance. Our B1 Partner has contacted SAP about this and they say it is a problem they are aware of and are working on. We also had Excel Reporter and SQL Manager reports designed, but they have the same script problem. We are still waiting for an answer (months later). Maybe you could have your Partner contact SAP also so they know you need this fixed, too.

                    Bev Porter

                    • 7. SQL Query for Monthly Sales Report
                      Andee DiCola

                      This is a great example of when we need to get together and influence SAP via the Influence Council. We have strength in numbers if many SAP customers have the same issue.  We need some volunteers for this Council.  We need  your help in knowing what the problems are for your companies.......

                      • 8. SQL Query for Monthly Sales Report
                        Michael Shen

                        Yes, I agree.
                        Since we started to use SBO, we've had tons of problems especially in the reporting area.
                        The built-in reports in SBO could not help us at all in most cases. So we had to create our own customized reports.
                        We have been using Crytal reports intensively.
                        Not only the reporting did give us hard time. We found out that SBO lacks a lot of standard functions for example RMA, Rebate process, Bank recon, etc.
                        Again, we had to create our own customized solutions.
                        From what I heard, SBO 2006 improves a lot.
                        We will see how SBO 2006 version turns out.
                        Otherwise I guess we have to turn to mySAP instead of SBO...

                        Michael Shen

                        • 9. SQL Query for Monthly Sales Report
                          Frank Romano

                          Hi Andee

                          I agree with your idea.

                          We difenetly need have a way to deliver to SAP information directly.


                          • 10. SQL Query for Monthly Sales Report
                            Frank Romano

                            Hi Michel

                            We also using Crystal and XL Reporter and trying to Customize print Layout Designer Reports and...
                            Can not get what we need.
                            System Financial Reports looks agly and I trying to do some research for FRx Reporter to write custom Financial.

                            Reference to Item/ Custom Invoices - we are using only Item type.
                            My system Sales Report by Customer is LESS than System Sales Report by Item.

                            I think we can get right result with Custom Sales Report if we will be able to recognize difrence between Paid and Credited for Invoice with credit
                            and Paid Invoice. (Paid Invoice does not make any affect on Sales total - Credit Does.)



                            • 11. SQL Query for Monthly Sales Report
                              Andee DiCola

                              Alex and Michael:
                              So can I count on you two to be on the Influence Council with me??
                              We need at least 10 volunteers . We want to get together all our issues and prioritize them and send to SAP for resolution. Right now we only have 2 volunteers. I have meet with the ASUG Director of Influence,Rick Lloyd, and he is very interested in working hard in the SAP Business One area this year. I need the help of other users to understand what is the hottest issues that we need to be bringing to SAP.


                              • 12. SQL Query for Monthly Sales Report
                                James McCord


                                I was an R/3 user and like you foud the banking side of SBO weak. As a work arround we post all outgoing checks to a G/L account titled "Outsanding Checks".
                                On a daily basis when I look at the bank statments I enter the total checks cleared that day using the outgoing payment process and use the outstanding checks account as the offset.
                                Thus my main bank acount is credited and the outstanding checks account is debited.
                                I then do a reconciliation of the outstanding checks account using the detail form my bank statement of which checks made up the total cleared for the day.
                                The end result should be that the main bank account always agrees to the bank statement and the "outstanding checks" account always represents the checks which have not cleared. The General Ledger shows both accounts under Cash at bank and will give you your "accounting cash balance".
                                R/3 does the clearing and posting for you but of course it costs a lot more for this  privilege.

                                • 13. SQL Query for Monthly Sales Report
                                  Andee DiCola

                                  When we updated to SAP 2005, we got the bank reconciliation added with the upgrade. Are you still on 2004?


                                  • 14. SQL Query for Monthly Sales Report
                                    Gregory Utley


                                    Last summer I met with Valerie Arguin of SAP North America and gave her a list of important issues we have identified, primarily since we were Navision users from 1996-2004 and are missing some features we had and expected to also have in SAP. One of the biggest is security, but reports are giving us quite a problem too. I would be glad to send you my list and help in any way I can to influence SAP.

                                    Bev Porter

                                    1 2 Previous Next