SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[SP_GENERALLEDGER]
@ACCode NVARCHAR(500),
@FROM DATETIME,
@TO DATETIME,
@BRANCHCODE NVARCHAR(500)
AS
BEGIN
SELECT ISNULL(accounts.acgroup,'') as acgroup,ISNULL(accounts.flag,'') as acflag,isnull(accounts.type,'') as AcType
, dbo.accounts.Code,dbo.accounts.account as Name, 

((SELECT ISNULL(SUM(ISNULL((Case DbCr  when 'DEBIT' then +Amount when 'CREDIT' then -Amount end),0)),0) FROM AccountOPEntryUpdated
 WHERE AccountCode=@AcCode AND ISNULL(Branchcode,'')=ISNULL(@Branchcode,ISNULL(Branchcode,'')))
+isnull(dbo.udf_SelectLedgeropbal(@FROM,accounts.Code,@BRANCHCODE),0)) AS OpeningBal
,'' AS VoucherNo,0 AS Amount,'OPENING' AS Type,'' AS Narration,'01/01/2000' AS ENTRYDATE 
  , '' as   Branchname 
  FROM dbo.accounts
 WHERE ISNULL(dbo.accounts.Code,0)=ISNULL(@ACCode,ISNULL(Code,0))
 UNION ALL
SELECT ISNULL(accounts.acgroup,'') as acgroup,ISNULL(accounts.flag,'') as acflag,isnull(accounts.type,'') as AcType
 , dbo.accounts.Code,dbo.accounts.account as Name,
 ((SELECT ISNULL(SUM(ISNULL((Case DbCr  when 'DEBIT' then +Amount when 'CREDIT' then -Amount end),0)),0) FROM AccountOPEntryUpdated 
 WHERE AccountOPEntryUpdated.AccountCode=@AcCode AND ISNULL(Branchcode,'')=ISNULL(@Branchcode,ISNULL(Branchcode,'')))

+isnull(dbo.udf_SelectLedgeropbal(@FROM,accounts.Code,@BRANCHCODE),0)) AS OpeningBal
,dbo.TBLACCOUNTING.VoucherNo,Amount,dbo.TBLACCOUNTING.Type,dbo.TBLACCOUNTING.Narration,TBLACCOUNTING.ENTRYDATE
    ,(select top 1 [name] from branchmaster where code=TBLACCOUNTING.branchname) as Branchname 
  FROM dbo.accounts
LEFT  JOIN TBLACCOUNTING ON dbo.accounts.Code=dbo.TBLACCOUNTING.AcCode 
 WHERE ISNULL(dbo.accounts.Code,0)=ISNULL(@ACCode,ISNULL(Code,0))
and TBLACCOUNTING.EntryDate between @FROM and @TO
AND ISNULL(TBLACCOUNTING.Branchcode,'')=ISNULL(@Branchcode,TBLACCOUNTING.Branchcode)
END;