A handy little query to export your SAP Business One Chart of Accounts setup to a format easily readable by Excel and other spreadsheet applications.
The query will show a level by level basis (hierarchical) output that can be used to edit/improve or adjust the Chart of Accounts setup.
Once again,
The query can be used as a base line and starting point to develop into more complex and robust reporting.
Cost Centers, Balances, External Codes and other account details can be incorporated into the query.
SELECT
CASE WHEN T0."Postable" = 'Y' THEN 'Active' ELSE 'Title' END AS "Account Type",
T0."AcctCode", T0."AcctName", T0."GroupMask" AS "Drawer", T0."Levels",
CASE WHEN T0."Levels" = 1 THEN T0."AcctCode" ELSE '' END AS "LEVEL 1 Code",
CASE WHEN T0."Levels" = 1 THEN T0."AcctName" ELSE '' END AS "LEVEL 1 Name",
CASE WHEN T0."Levels" = 2 THEN T0."AcctCode" ELSE '' END AS "LEVEL 2 Code",
CASE WHEN T0."Levels" = 2 THEN T0."AcctName" ELSE '' END AS "LEVEL 2 Name",
CASE WHEN T0."Levels" = 3 THEN T0."AcctCode" ELSE '' END AS "LEVEL 3 Code",
CASE WHEN T0."Levels" = 3 THEN T0."AcctName" ELSE '' END AS "LEVEL 3 Name",
CASE WHEN T0."Levels" = 4 THEN T0."AcctCode" ELSE '' END AS "LEVEL 4 Code",
CASE WHEN T0."Levels" = 4 THEN T0."AcctName" ELSE '' END AS "LEVEL 4 Name",
CASE WHEN T0."Levels" = 5 THEN T0."AcctCode" ELSE '' END AS "LEVEL 5 Code",
CASE WHEN T0."Levels" = 5 THEN T0."AcctName" ELSE '' END AS "LEVEL 5 Name",
CASE WHEN T0."Levels" = 6 THEN T0."AcctCode" ELSE '' END AS "LEVEL 6 Code",
CASE WHEN T0."Levels" = 6 THEN T0."AcctName" ELSE '' END AS "LEVEL 6 Name",
CASE WHEN T0."Levels" = 7 THEN T0."AcctCode" ELSE '' END AS "LEVEL 7 Code",
CASE WHEN T0."Levels" = 7 THEN T0."AcctName" ELSE '' END AS "LEVEL 7 Name",
CASE WHEN T0."Levels" = 8 THEN T0."AcctCode" ELSE '' END AS "LEVEL 8 Code",
CASE WHEN T0."Levels" = 8 THEN T0."AcctName" ELSE '' END AS "LEVEL 8 Name",
CASE WHEN T0."Levels" = 9 THEN T0."AcctCode" ELSE '' END AS "LEVEL 9 Code",
CASE WHEN T0."Levels" = 9 THEN T0."AcctName" ELSE '' END AS "LEVEL 9 Name"
FROM OACT T0
ORDER BY T0."GroupMask", T0."GrpLine"
Signing Out,
Dário