[go: up one dir, main page]
Scribd
Upload
28 views2 pages

1) Create Glfixed

Uploaded by

nagesh

This stored procedure creates general ledger fixed accounts and populates transaction details from history tables. It declares variables to store account codes, transaction dates, debit/credit totals, and running balances. Cursors are used to loop through distinct product codes and transaction dates to insert ledger account records and daily balance details for each combination of product code and date. Balances are calculated as credits minus debits, carrying forward the running balance from the previous day.

Copyright:

© All Rights Reserved
Download as TXT, PDF, TXT or read online from Scribd
Download as txt, pdf, or txt

1) Create Glfixed

Uploaded by

nagesh
28 views2 pages
This stored procedure creates general ledger fixed accounts and populates transaction details from history tables. It declares variables to store account codes, transaction dates, debit/credit totals, and running balances. Cursors are used to loop through distinct product codes and transaction dates to insert ledger account records and daily balance details for each combination of product code and date. Balances are calculated as credits minus debits, carrying forward the running balance from the previous day.

Original Title

1)create_glfixed

Copyright

© © All Rights Reserved

Available Formats

TXT, PDF, TXT or read online from Scribd

Did you find this document useful?

Is this content inappropriate?

This stored procedure creates general ledger fixed accounts and populates transaction details from history tables. It declares variables to store account codes, transaction dates, debit/credit totals, and running balances. Cursors are used to loop through distinct product codes and transaction dates to insert ledger account records and daily balance details for each combination of product code and date. Balances are calculated as credits minus debits, carrying forward the running balance from the previous day.

Copyright:

© All Rights Reserved
Download as TXT, PDF, TXT or read online from Scribd
Download as txt, pdf, or txt
28 views2 pages

1) Create Glfixed

Uploaded by

nagesh
This stored procedure creates general ledger fixed accounts and populates transaction details from history tables. It declares variables to store account codes, transaction dates, debit/credit totals, and running balances. Cursors are used to loop through distinct product codes and transaction dates to insert ledger account records and daily balance details for each combination of product code and date. Balances are calculated as credits minus debits, carrying forward the running balance from the previous day.

Copyright:

© All Rights Reserved
Download as TXT, PDF, TXT or read online from Scribd
Download as txt, pdf, or txt
You are on page 1/ 2

CREATE PROCEDURE [dbo].

[CREATEGLFixed]
AS
DECLARE @inc decimal
DECLARE @totalDebit money
DECLARE @totalCredit money
DECLARE @RunningBal money
DECLARE @transdate Datetime
DECLARE @test smallint
DECLARE @groupcode varchar(10)
DECLARE @acctype varchar(10)
DECLARE @headdesc varchar(80)
DECLARE @productcode varchar(6)
DECLARE @C1 cursor
DECLARE @C2 cursor
SET @C1 =CURSOR FAST_FORWARD for SELECT DISTINCT productcode FROM
FixedActhistorytable
SET @C2 = CURSOR FAST_FORWARD for SELECT DISTINCT TRANSDATE FROM
HISTORYTRANSACTIONTABLE ORDER BY TRANSDATE
BEGIN
set @inc=1
OPEN @C1
FETCH NEXT FROM @C1
INTO @productcode
WHILE @@FETCH_STATUS = 0
BEGIN
select top 1 @groupcode=group_code,@acctype=acc_type,@headdesc=CLASSN_DESC
from tbl_glclass where acc_subtype=@productcode

insert into
tbl_glmaster(group_code,acc_type,acc_subtype,accno,curr_code,head_desc)values

(@groupcode,@acctype,@productcode,'F'+cast(@inc as varchar),'INR',@headdesc)

set @test=0
set @RunningBal=0
OPEN @C2
FETCH NEXT FROM @C2
INTO @transdate
WHILE @@FETCH_STATUS = 0
BEGIN
select @totalcredit=isnull(sum(case when dbcrindicator='C' then
transamount end),0) ,

@totalDebit=isnull(sum(case when dbcrindicator='D' then

transamount end),0)
from historytransactiontable,historytransactionheadertable
where accounttype='F' and transstatus='V' and deleteflag!='Y' and

historytransactiontable.transdate=historytransactionheadertable.transdate and
historytransactiontable.transid=historytransactionheadertable.transid
AND historytransactiontable.TRANSDATE=@transdate
and accountid in(select accountid from fixedacthistorytable where
productcode=@productcode )
if @test<1
begin
set @RunningBal=@totalcredit-@totalDebit
set @test=6
insert into
tbl_GldayBal(accno,curr_code,proc_date,debits,credits,balance,base_curr_Equiv_bal,a
cc_subtype)values
('F'+cast(@inc as varchar),'INR',

@transdate,@totalDebit,@totalcredit,@RunningBal,@RunningBal,@productcode)
end
else
BEGIN
set @RunningBal=@RunningBal+(@totalcredit-@totalDebit)
insert into

tbl_GldayBal(accno,curr_code,proc_date,debits,credits,balance,base_curr_Equiv_bal,a
cc_subtype)values
('F'+cast(@inc as varchar),'INR',

@transdate,@totalDebit,@totalcredit,@RunningBal,@RunningBal,@productcode)
END
FETCH NEXT FROM @C2
INTO @transdate
end
CLOSE @C2
FETCH NEXT FROM @C1
INTO @productcode
set @inc=@inc+1
END
CLOSE @C1
end

GO

--1

You might also like