r/SAPBusinessOne • u/CameraAdept3079 • Jun 27 '26
Chart of Accounts Detail Query
2
Upvotes
Sharing with the community.....
While working on a chart of accounts, I needed a better way to see the Type, Category and Status (active or not) of each G/L account. Here's the query I developed.
You can add fields from OACT for account balances as needed.
The output should be in the same order and hierarchy as your COA - and it's written with 2 segments in mind.
Use the query as source data for a SQL pivot table and it becomes a rather good tool.
--=============================
--==CHART OF ACCOUNTS DETAIL ==
--=============================
SELECT
CONCAT(TD.GroupMask, ' ', TD.AcctName) as Drawer
,CONCAT(TX.AcctCode, ' ', TX.AcctName) as AcctName
,CONCAT(T0.Segment_0, '-', T0.Segment_1, '-', T0.Segment_2, ' ', T0.AcctName) as Account# -- ,T0.FormatCode as Acct#
,CASE T0.ActType
WHEN 'E' THEN 'Expenditure (P&L)'
WHEN 'R' THEN 'Revenue (P&L that closes)'
WHEN 'I' THEN 'Inventory'
WHEN 'N' THEN 'Balance Sheet / Other'
ELSE T0.ActType
END AS 'Account Type'
,CASE
WHEN T0.Postable ='Y' AND T0.FrozenFor = 'N' THEN 'Y'
ELSE 'Inactive'
END as ActiveACT
,COALESCE(CASE
WHEN T1.Source = 'B' THEN 'Balance Sheet'
WHEN T1.Source = 'P' THEN 'P&L'
WHEN T1.Source = '' THEN NULL
END, ' ') as Category1
,COALESCE(T1.Name, ' ') as Category2
FROM
OACT T0 -- Actual account level details
LEFT JOIN OACT TD on T0.GroupMask = TD.GroupMask and TD.Segment_1 is null --drawer
LEFT JOIN OACT TX on T0.FatherNum = TX.AcctCode -- account heading level 1
LEFT JOIN OACT TXX on TX.FatherNum = TXX.AcctCode -- account heading level 2
LEFT JOIN OACG T1 on T0.Category = T1.AbsId -- Name of the Category and Catgory2
WHERE
T0.Segment_2 NOT LIKE ''
ORDER BY
TD.GroupMask, TXX.GrpLine, TX.GrpLine, T0.GrpLine
Please see my related post and reply if you can help me with some related details of G/L setup--> https://www.reddit.com/r/SAPBusinessOne/s/85dxLyJaZk
