See if any of this helps. I had to make a lot of guesses and changes to the sample data to get it to work.
;WITH
cteGeneralLedger
(GNLID,GNLCLIID,GNLCOAID,GNLFSMID,GNLBalanceBase)
AS (
SELECT 1,1385,576,113,-4845.0000 UNION ALL
SELECT 2,1385,713,114,-395.8500 UNION ALL
SELECT 3,3139,39,115,8703.3400 UNION ALL
SELECT 4,727,39,116,-1321.6500 UNION ALL
SELECT 5,727,39,117,-8811.0000 UNION ALL
SELECT 6,3139,713,118,-15416.5200 UNION ALL
SELECT 7,1480,713,119,18429.9200 UNION ALL
SELECT 8,3144,39,120,1321.6500 UNION ALL
SELECT 9,1480,713,121,-10799.0000 UNION ALL
SELECT 10,3144,576,122,4371.0000 UNION ALL
SELECT 11,3139,713,123,1619.8500 UNION ALL
SELECT 12,727,45,124,1786.1400 UNION ALL
SELECT 13,3139,39,125,46.4200 UNION ALL
SELECT 14,727,576,127,-12802.4000 UNION ALL
SELECT 15,1480,713,128,1198.2600 UNION ALL
SELECT 16,1480,713,129,-1785.0000 UNION ALL
SELECT 17,3139,713,130,800.0000
),
cteClients
(CLIID,CLICode,CLIName)
AS (
SELECT 727,'HARK','HARK CONSULTING' UNION ALL
SELECT 1385,'3HPARC','3HARCHITECTS' UNION ALL
SELECT 1480,'GUYCAR','GUY CARPENTER' UNION ALL
SELECT 3139,'ABNAP','ABB PTE LTD' UNION ALL
SELECT 3144,'SYSACC','SYSTEM ACCESS'
),
cteChartOfAccount
(COAID,COANAME1,COACode2Code,COACode2NAme2,COAAGPID)
AS (
SELECT 39,'Total Billings','RV10','RV10 Billings',1 UNION ALL
SELECT 45,'Prod Billing','RV40','RV40 Revenue',2 UNION ALL
SELECT 576,'Loan','FD100G','FD100G Financial Debts',3 UNION ALL
SELECT 713,'Receivables','FA301G','FA301G Loans Advances',4
),
cteAccountGroup
(AGPID,AGPNAME1)
AS (
SELECT 1,'Assets' UNION ALL
SELECT 2,'Goodwill' UNION ALL
SELECT 3,'Deferred' UNION ALL
SELECT 4,'Trade' UNION ALL
SELECT 5,'Cash'
),
cteFiscalMonth
(FSMID,FSMNAME1)
AS (
SELECT 113,'Opening Balance 12' UNION ALL
SELECT 114,'Jan 12' UNION ALL
SELECT 115,'Feb 12' UNION ALL
SELECT 116,'Mar 12' UNION ALL
SELECT 117,'Apr 12' UNION ALL
SELECT 118,'May 12' UNION ALL
SELECT 119,'Jun 12' UNION ALL
SELECT 120,'Jul 12' UNION ALL
SELECT 121,'Aug 12' UNION ALL
SELECT 122,'Sep 12' UNION ALL
SELECT 123,'Oct 12' UNION ALL
SELECT 124,'Nov 12' UNION ALL
SELECT 125,'Dec 12' UNION ALL
SELECT 127,'Opening Balance 13' UNION ALL
SELECT 128,'Jan 13' UNION ALL
SELECT 129,'Feb 13' UNION ALL
SELECT 130,'Mar 13'
),
cteClientOwner
(CLOID,CLOCLIID,CLODIVID)
AS (
SELECT 1,727,1 UNION ALL
SELECT 2,1385,2 UNION ALL
SELECT 3,1480,3 UNION ALL
SELECT 4,3139,3 UNION ALL
SELECT 5,3144,4 UNION ALL
SELECT 6,727,5
),
cteDivision
(DIVID,DIVName1)
AS (
SELECT 1,'Digital' UNION ALL
SELECT 2,'Zenith' UNION ALL
SELECT 3,'Stars' UNION ALL
SELECT 4,'MSL' UNION ALL
SELECT 5,'Leo'
)
SELECT DISTINCT
c.CLIName AS 'Client'
,c.CLICode AS 'Client Code'
,l.GNLID AS 'GL Account ID'
,g.AGPName1 as 'GL Account Group'
,a.COAName1 AS 'GL Account'
,SUM(l.GNLBalanceBase) OVER (PARTITION BY l.GNLFSMID) AS 'Closing Balance'
,d.DIVName1 AS 'Division'
,a.[COACode2Code] AS 'GLChart Code 2'
,a.COACode2NAme2 AS 'GLChart Code 2 Name'
,(CASE
WHEN l.GNLFSMID IN (113,114,115,116,117,118,119,120,121,122,123,124,125)
THEN SUM(l.GNLBalanceBase) OVER (PARTITION BY l.GNLFSMID)
ELSE '0'
END) AS 'Opening Balance'
FROM
cteClients c
INNER JOIN [cteGeneralLedger] l
ON l.GNLCLIID = c.CLIID
INNER JOIN [cteChartOfAccount] a
ON l.GNLCOAID = a.COAID
INNER JOIN [cteAccountGroup] g
ON g.AGPID = a.COAAGPID
INNER JOIN [cteclientOwner] o
ON o.CLOCLIID = c.CLIID
INNER JOIN [cteDivision] d
ON d.DIVID = o.CLODIVID
INNER JOIN [cteFiscalMonth] m
ON l.GNLFSMID = m.FSMID