Skip to Content
0

SAP Business one query for Chart of Account Heirarchy

Nov 24, 2016 at 09:45 AM

360

avatar image
Former Member

Hi Everyone,

i want a query to generate SAP Business one chart of account Heirarchy.

Select oact.FatherNum,oact2.AcctName as FatherGroupName,OACT.Segment_0+'-'+oact.Segment_1+'-'+oact.Segment_2 as AccountCode,OACT.AcctName

from OACT

left join OACT oact2 on oact2.FatherNum=oact.AcctCode

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Best Answer
DIEGO LOTHER Nov 25, 2016 at 06:59 PM
0

Hi Safeer,

You can accomplish this with a recursive query. A sample for your situation:

WITH ChartOfAccounts AS ( 
	SELECT 
		CONCAT('/', CAST(ActId AS NVARCHAR(MAX))) AS Path, 
		AcctCode, 
		AcctName, 
		FatherNum, 
		Levels
	FROM 
		OACT T0
	WHERE 
		FatherNum IS NULL
UNION ALL
	SELECT 
		T1.Path +'/'+ CAST(T0.ActId AS NVARCHAR(MAX)) AS Path, 
		T0.AcctCode, 
		T0.AcctName, 
		T0.FatherNum, 
		T0.Levels
	FROM 
		OACT T0
		INNER JOIN ChartOfAccounts T1 ON T0.FatherNum = T1.AcctCode
	WHERE 
		T0.FatherNum IS NOT NULL 
)
--Uncomment what you prefer
--Simple view
--SELECT AcctCode, AcctName FROM ChartOfAccounts ORDER BY path
--View with indentation
--SELECT AcctCode, CONCAT(REPLICATE(' ', Levels * 3), AcctName) AS Hierarchy FROM ChartOfAccounts ORDER BY path

Kind Regards,

Diego Lother

Show 1 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Thanks DIEGO LOTHER it's work Fine

0