Clients First USA
Clients First knows their software, but they are also astute business people, and they really understand manufacturing and distribution in particular.
Jimmy Witcher, COO, Merrick
Read More
About UsService OfferingsProduct OfferingsIndustries ServedDownloadsBlogTestimonials
 

Subscribe to our mailing list

* indicates required
    Interested in    
Hot Topics
 

SAP Business One Query: Drill Downs from GL to the Sub-Ledgers

If you find that you need to access the sub-ledger (detailed transactions) for a query you would first start with the GL Accounts Table (OACT). Then you will need to join in the detailed journal entries in (JDT1) the join between those two tables looks like this:
SELECT *
FROM OACT
Inner Join JDT1 ON OACT.AcctCode = JDT1.account
Most clients will want to see the period information, so add in that table too:
SELECT *
FROM OACT
Inner Join JDT1 ON OACT.AcctCode = JDT1.account
Inner Join OFPR on JDT1.finncPriod = OFPR.AbsEntry
To access the underlying sub-ledgers, you will need to look at two fields in the Journal Detail file (JDT1), the BaseRef (Reference code in the subledger (i.e., the document number in the invoice table)) and the TransType (tells you which table to access). Below is an example on how to access the data in the AR Invoice table from the Journal Details File (JDT1 -> OINV):
SELECT *
FROM OACT
Inner Join JDT1 ON OACT.AcctCode = JDT1.account
Inner Join OFPR ON JDT1.finncPriod = OFPR.AbsEntry
Left Outer Join OINV
ON OINV.docnum=JDT1.baseref and JDT1.TransType = 13
Note the TransType number, there is a different one for each data source. Repeat the Left Outer Join for Each Source and you will be set. Put the query in something like SBO (for PLD), Crystal Reports, or MS Reporting Services and you can create a nicely formatted report.
Good Luck!

 

Thanks to Ed Monk of SBONotes.com

One Response to “SAP Business One Query: Drill Downs from GL to the Sub-Ledgers”

  1. Martin says:

    SELECT *
    FROM OACT
    Inner Join JDT1 ON OACT.AcctCode = JDT1.account
    Inner Join OFPR ON JDT1.finncPriod = OFPR.AbsEntry
    Left Outer Join OINV
    ON OINV.docnum=JDT1.baseref and JDT1.TransType = 13

    Can one include Inv1 table in above query?

Leave a Reply