• Login
• Become a member
• RSS
• Part of the TechTarget network

In a General Ledger (GL) system it is always critical to use procedures to compile frequent analysis reports like Cash/Bank Summary Reports, where amounts for corresponding accounts will appear instead of amounts for target accounts.

This report should also handle a situation where voucher (JV) has multiple accounts on both the debit and credit side. The business rules that have been incorporated to handle the above situation are:

1. The weighted age of each account amount on the debit side with reference to the debit balance of the voucher is calculated.
2. The above weighted age is applied to the amount of each individual account on the credit side and a proportionate amount is calculated.
3. The above calculated amounts are shown with respective accounts as corresponding accounts of the account appearing on debit side.

4. The usual practice is to write procedures to compile all the transactions. This needs to have one or more temporary tables where transactions will be placed for opening balances and running balances separately. Then we need to compute the opening balance for the report based on the date range requested. Procedures are a costly solution in this situation in terms of more round trips for disk I/O and time to compile the results.

   Instead, we can generate reports using a listed query. This query produces results in less than a minute on a data volume of 100,000 transactions within the fiscal period. I'm using MS-SQLSever 7.0 and TSQL on Windows NT 4.0:

   CREATE TABLE gl_chart (
          company_code	    varchar(4) NOT NULL,
          account_code         varchar(14) NOT NULL,
          currency_code        varchar(4) NOT NULL,
          level_no             varchar(1) NOT NULL,
          master_company       varchar(4) NULL,
          master_account       varchar(14) NULL,
          account_type         varchar(1) NOT NULL,
          account_category     varchar(1) NOT NULL,
          bspl_type            varchar(1) NOT NULL,
          account_name         varchar(80) NULL,
          opening_cr           numeric(14,2) NULL,
          opening_dr           numeric(14,2) NULL,
          current_cr           numeric(14,2) NULL,
          current_dr           numeric(14,2) NULL,
          opening_f_cr         numeric(14,2) NULL,
          opening_f_dr         numeric(14,2) NULL,
          current_f_cr         numeric(14,2) NULL,
          current_f_dr         numeric(14,2) NULL,
          shadow_cr            numeric(14,2) NULL,
          shadow_dr            numeric(14,2) NULL,
          shadow_f_cr          numeric(14,2) NULL,
          shadow_f_dr          numeric(14,2) NULL,
          Bank_Limit           numeric(14,2) NULL,
          prev_dr              numeric(14,2) NULL,
          prev_cr              numeric(14,2) NULL,
          prev_f_dr            numeric(14,2) NULL,
          prev_f_cr            numeric(14,2) NULL,
          doc                  datetime NULL,
          user_code            varchar(10) NULL,
          last_update          datetime NULL,
          update_by            varchar(8) NULL,
          active               varchar(1) NULL,
          account_distributed  varchar(1) NULL,
          check_limit          varchar(1) NULL,
          PRIMARY KEY (company_code,account_code) 
   )
   go
   
   
   CREATE TABLE gl_voucher (
          company_code         varchar(4) NOT NULL,
          voucher_type         varchar(4) NOT NULL,
          voucher_seq          varchar(6) NOT NULL,
          voucher_date         datetime NOT NULL,
          doc                  datetime NULL,
          posting_date         datetime NULL,
          voucher_remarks      varchar(240) NULL,
          voucher_amount       numeric(14,2) NULL,
          voucher_module       varchar(2) NULL,
          user_code            varchar(8) NULL,
          last_update          datetime NULL,
          update_by            varchar(8) NULL,
          checked              varchar(1) NULL,
          checked_by           varchar(8) NULL,
          checked_on           datetime NULL,
          approved             varchar(1) NULL,
          approved_by          varchar(8) NULL,
          approved_on          datetime NULL,
          verified             varchar(1) NULL,
          verified_by          varchar(8) NULL,
          verified_on          datetime NULL,
          voucher_reference    varchar(80) NULL,
          voucher_distributed  varchar(1) NULL,
          reconsile            varchar(1) NULL,
          reconsile_by         varchar(4) NULL,
          reconsile_date       datetime NULL,
          bank_ref             varchar(20) NULL,
          bank_date            datetime NULL,
          PRIMARY KEY (company_code,voucher_type, voucher_seq) 
   )
   go
   
   
   CREATE TABLE gl_voucher_accounts (
          company_code         varchar(4) NOT NULL,
          voucher_type         varchar(4) NOT NULL,
          voucher_seq          varchar(6) NOT NULL,
          seq_no               varchar(2) NOT NULL,
          account_code         varchar(14) NOT NULL,
          currency_dr          numeric(14,2) NULL,
          currency_cr          numeric(14,2) NULL,
          credit               numeric(14,2) NULL,
          debit                numeric(14,2) NULL,
          naration             varchar(80) NULL,
          account_distributed  varchar(1) NULL,
          amount_distributed   numeric(14,2) NULL,
          currency_code        varchar(4) NULL,
          currency_rate        numeric(10,6) NULL,
          PRIMARY KEY (company_code,voucher_type, voucher_seq, seq_no, 
                 account_code), 
          FOREIGN KEY (company_code, voucher_type, voucher_seq)
                                REFERENCES gl_voucher, 
          FOREIGN KEY (company_code, account_code)
                                REFERENCES gl_chart
   )
   go
   --
   ***********
   declare @account	varchar(14),			--	account code for which a reverse ledger is required
   	@company	varchar(4),			--	company code
   	@sd		datetime,			--	start date
   	@ed		datetime			--	end date
   
   select	@account	= 	'50101010001',		
   	@company	=	'C002',			
   	@sd		=	'01-feb-1999',		
   	@ed		=	'28-feb-1999'		
   
   --
   --
   
   select 	c.account_code,
   	unposted = case when (shadow_dr<>0 or shadow_cr <> 0) then '*' else ' ' end,
   	opening_balance	= (opening_dr + isnull(o.odb,0))- (opening_cr + isnull(o.ocr,0)),
   	account_name 	= upper(account_name),
   	r.unrecon,
   	r.voucher_date,
   	r.voucher_type,
   	r.voucher_seq,
   	r.account_code,
   	r.naration,
   	r.dr,
   	r.cr
     from 	gl_chart c, 	
   		(select 	v.company_code,
   				account_code,
   				odb=sum(debit),
   				ocr=sum(credit) 
   		   from 	gl_voucher_accounts a ,gl_voucher v
                     where		a.company_code = v.company_code and
   				a.voucher_type = v.voucher_type and
   				a.voucher_seq  = v.voucher_seq  and
   				v.voucher_date < @sd            and
   				v.company_code = @company       and
   				a.account_code = @account
     		  group  by 	v.company_code,account_code) O,
   
   --
   		(SELECT	v.company_code,
   			v.account_code,
   			unrecon	= case when reconsile = '0' then '*' else '' end,
   			voucher_date,
   			V.VOUCHER_TYPE,
   			V.VOUCHER_SEQ,
   			v.naration,
   			debit,
   			amountdr,
   			credit,
   			amountcr,
   			dr=Credit*AmountDr,
   			cr=Debit*amountCr
   		  FROM	GL_VOUCHER_ACCOUNTS V, 
   			(SELECT	COMPANY_CODE,VOUCHER_TYPE,VOUCHER_SEQ,
   				DB=SUM(DEBIT),CR=SUM(CREDIT),
   				AmountDr=abs(sum(case when account_code = @account then (debit)  else 0 end))/sum(debit),
   				AmountCr=abs(sum(case when account_code = @account then (credit) else 0 end))/sum(Credit)
   			   FROM GL_VOUCHER_ACCOUNTS
   			  GROUP BY COMPANY_CODE,VOUCHER_TYPE,VOUCHER_SEQ) A  ,
   				GL_VOUCHER H
   		 WHERE	v.company_code = a.company_code
   		   and  v.voucher_type = a.voucher_type
   		   and  v.voucher_seq  = a.voucher_Seq
   		   and	v.company_code = h.company_code
   		   and  v.voucher_type = h.voucher_type
   		   and  v.voucher_seq  = h.voucher_Seq
   		   and  (h.voucher_date >= @sd and h.voucher_date <=  @ed)
   		   and	v.ACCOUNT_CODE <> @account
    		   and	v.company_code = @company
   		   and	exists (select account_code from gl_voucher_accounts
   				where company_code = v.company_code
   				  and voucher_type = v.voucher_type
   				  and voucher_seq  = v.voucher_seq
   				  and account_code = @account) ) R
    where c.company_code *= O.company_code
      and c.account_code *= O.account_code
      and c.company_code *= R.company_code
      and c.account_code  = @account
      and c.company_code  = @company
      and c.account_code  = @account
      and (r.dr > 0 or r.cr > 0)
   order by c.account_code,voucher_date,voucher_type,voucher_seq
   

    

   For More Information

   • What do you think about this tip? E-mail us at [email protected] with your feedback.
   • The Best Microsoft SQL Server Web Links: tips, tutorials, scripts, and more.
   • The Best SQL Web Links
   • Have a SQL Server tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
   • Ask your technical SQL Server questions--or help out your peers by answering them--in our live discussion forums.
   • Ask the Experts yourself: Our SQL Server gurus are waiting to answer your toughest questions.
   
   

Join the conversationComment

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.