-------------------------------------------------------------------
--Checking A/C code wise
-- Written by Sabu john
--------------------------------------------------------------
--
--checking wheather closing balance in summary table (account_balance)
-- matches with the balance of ledger
--Use these steps to find problem accounts if Trial Balance doesn't match

--Step 1
--sum of all op - dr-cr group by accountcode
select a.c_accountcode , sum(a.m_dramount) - sum(a.m_cramount) acbal 
into #ledger
from (select c_accountcode , m_dramount,m_cramount ,"type" = 'L' from general_ledger
	where  finyr = '14'
	and d_cancelldate = '01 jan 1900'
	and d_transactiondate >= '01 jul 2006'
	and d_transactiondate < '06 jan 2007'
      union all
	select c_accountcode ,  case when opening > 0 then abs(opening) else 0 end , 
    				case when opening < 0 then abs(opening) else 0 end  ,"type" = 'O'  
	from account_balance
	where d_date = '01 jul 2006' ) a
group by a.c_accountcode 

--Step 2
--get account balance from summary table


go

select c_accountcode ,closing
into #bal
from account_balance a
where finyr  = '14'
and d_date = (select max(d_date) from account_balance b 
		where a.c_accountcode = b.c_accountcode 
		and finyr	= '14' )

--Step 3
-- Select all accounts that r not matching
go

select a.*,b.* ,c.c_name from #ledger a, #bal b , account_master c
where a.c_accountcode = b.c_accountcode
and a.c_accountcode = c.c_accountcode
and a.acbal <> b.closing

drop table  #ledger
drop table  #bal

----EXECUTE -------------------------------------------------------
commit
begin tran

execute  prc_balance_update_from_gl 'AIS' , '14','11121' , '01 Aug 2006'
	@c_schoolcode 	char(7)	,
	@c_yearcode	char(2)	,
	@c_accountcode	char(6)	,
	@d_startdate	datetime
	
----------------------------------------------------
--Check debit and credit matches for all invoices in general_ledger
----------------------------------------------------------------
	SELECT 	c_transactionno			, 
		credit 	= sum(m_cramount)	, 
		debit 	= sum(m_dramount)	, 
		diff 	= sum(m_cramount)-sum(m_dramount)
	FROM 	general_ledger
	WHERE 	d_cancelldate = '01/01/1900'
	and	finyr = '13'
	GROUP BY c_transactionno
	HAVING 	(sum(m_cramount)-sum(m_dramount)) <> 0
	ORDER BY c_transactionno
		
-------------------------------------------------------------------
-- Checking A/C datewise for each account
---checking daily total in acbal and ledger for single account
--------------------------------------------------------------
----first select daily total as from account_balance table
select c_accountcode , d_date , 'daily_acbal' = opening - closing  
into   #acbal_dily 
from account_balance
where c_accountcode = '11121'
and   finyr = '14'
and d_date between '01 jul 2006' and '06 jan 2007'

----next select daily total as from general_ledger table
select c_accountcode , d_transactiondate , 'gl_dailybal' = sum(m_cramount - m_dramount)
into #ledger_daily
from general_ledger
where c_accountcode = '11121'
and d_cancelldate  = '01 jan 1900'
and   finyr = '14'
and d_transactiondate between '01 jul 2006' and '06 jan 2007'
group by c_accountcode , d_transactiondate 

---compare the results and return the rows with difference

select a.c_accountcode , d_transactiondate , gl_dailybal , daily_acbal , 'diff' = gl_dailybal - daily_acbal
from #ledger_daily a ,  #acbal_dily  b
where a.d_transactiondate = b.d_date
and   gl_dailybal <> daily_acbal


drop table #ledger_daily
drop table #acbal_dily

---------------------------------------------------------------------
-- Check if closing for a day is equal to opening for next day.
------------------------------------------------------------------
select d_date ,dateadd(day,1,a.d_date),closing - ( select opening from account_balance b where 
		a.c_accountcode = b.c_accountcode
		and b.d_date = ( select min(d_date) from account_balance c where 
				a.c_accountcode = c.c_accountcode 
				and c.d_date> a.d_date ))
from account_balance a
where c_accountcode = '111101'
and finyr = '13'
----------------------------------------------------------------------------------

