CREATE PROCEDURE SP_Fin_Get_All_AccountHeads_Group_Hierarchy
@Group VARCHAR(100),
@CompID VARCHAR(100)
AS
---SP_Fin_Get_All_AccountHeads_Group_Hierarchy 'Admnistrative Expenses', 'ANOOS'
BEGIN
DECLARE @primary_account_head_group_id VARCHAR(100)
DECLARE @MaxDepth INT
DECLARE @CurrentFiscal VARCHAR(50)
SET @MaxDepth = 6;
;WITH LedgerList AS(
SELECT isnull(account_heads_parent_id,0) AS ParentId,
account_heads_id AS Id,
account_heads_code AS Code,
description AS Description, 1 AS Hierarchy
FROM fin_account_heads
WHERE account_heads_parent_id is null
AND primary_account_head_group_id=(select account_heads_group_id from fin_account_heads_group where account_heads_group_name =@Group)
and company_code = @CompID
AND is_active=1
UNION ALL
SELECT AH.account_heads_parent_id AS ParentId,
AH.account_heads_id AS Id,
AH.account_heads_code AS Code,
AH.description AS Description,
LedgerList.Hierarchy + 1 AS Hierarchy
FROM fin_account_heads AH
INNER JOIN LedgerList
ON AH.account_heads_parent_id = LedgerList.ID
WHERE AH.company_code = @CompID
AND LedgerList.Hierarchy < @MaxDepth
AND is_active=1
)
SELECT * FROM LedgerList
End