‘Customization’ Category

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