Skip to Content
avatar image
Former Member

SAP Business one query for Chart of Account Heirarchy

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Nov 25, 2016 at 06:59 PM

    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

    Add comment
    10|10000 characters needed characters exceeded