USE [DB01022012]
GO
/****** Object: StoredProcedure [dbo].[SPPostingJournalByType] Script Date: 04/30/2012 15:02:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[SPPostingJournalByType]
@yearprd VARCHAR (4),
@monthprd VARCHAR (2),
@journaltypeno VARCHAR (20)
as
declare
@query nvarchar(4000),
@query_db nvarchar(1000),
@query_cd nvarchar(1000),
@column_name_bb VARCHAR (4),
@column_name_db VARCHAR (4),
@column_name_cd VARCHAR (4),
@column_name_eb VARCHAR (4),
@exist_bb numeric,
@exist_db money,
@exist_cd money,
@exist_eb money,
@subledgertype int,
@employeeId int,
@partnerNo VARCHAR (20),
@journalNo VARCHAR (20),
@count int,
@month VARCHAR (2),
@coa_no VARCHAR (20),
@coa_no_parent VARCHAR (20),
@coa_no_parent_old VARCHAR (20),
@amount_db money,
@amount_cd money
–SET @monthprd = ‘9’
–SET @yearprd = ‘2011’
IF (datalength(@monthprd) = 1)
BEGIN
SET @month = ‘0’+@monthprd;
END
SET @column_name_bb = ‘BB’ + @month
SET @column_name_db = ‘DB’ + @month
SET @column_name_cd = ‘CD’ + @month
SET @column_name_eb = ‘EB’ + @month
DECLARE jurnalItemCs CURSOR FOR
SELECT JournalNo, AccountNo, AmountOfDebit, AmountOfCredit, SubLedgerType, CSIEmployeeId, PartnerNo
FROM CSIACJournalEntryItem
WHERE JournalNo IN
(select JournalNo from CSIACJournalEntry where YearPeriod=@yearprd and
MonthPeriod=@monthprd and IsPosted =’false’ and IsClosed = ‘false’
and IsValidated =’true’ and JournalTypeNo=@journaltypeno )
and IsValidated = ‘true’ and IsPosted = ‘false’ and IsClosed = ‘false’
OPEN jurnalItemCs
FETCH jurnalItemCs INTO @journalNo, @coa_no, @amount_db, @amount_cd, @subledgertype, @employeeId, @partnerNo
WHILE (@@Fetch_Status = 0)
BEGIN
SET @exist_bb = 0
SET @exist_db = 0
SET @exist_cd = 0
SET @exist_eb = 0
— Insert ke Ac Ledger
IF((select count(*) from CSIACLedger where YearPeriod = @yearprd and AccountNo = @coa_no) = 0)
BEGIN
SET @exist_eb = abs(@amount_db – @amount_cd)
SET @query =’INSERT INTO CSIACLedger ([yearperiod],[AccountNo],[‘+@column_name_db+’],[‘+@column_name_cd+’],
[‘+@column_name_bb+’],[‘+@column_name_eb+’],[lastupdated], [lastupdater] )
values
(”’+@yearPrd+”’,”’+@coa_no+”’,’+convert(varchar,@amount_db)+’,’+convert(varchar,@amount_cd)+’,
‘+convert(varchar,@exist_bb)+’, ‘+convert(varchar,@exist_eb)+’,{fn NOW()},1) ‘
EXEC(@query)
END
ELSE
BEGIN
SET @query_db = ‘((SELECT ISNULL(‘+@column_name_db+’,0) FROM CSIACLedger WHERE [YearPeriod]=’+@yearprd+’ and [AccountNo]=’+@coa_no+’)
+ ‘+CONVERT(VARCHAR,@amount_db)+’) ‘
SET @query_cd = ‘((SELECT ISNULL(‘+@column_name_cd+’,0) FROM CSIACLedger WHERE [YearPeriod]=’+@yearprd+’ and [AccountNo]=’+@coa_no+’)
+ ‘+CONVERT(VARCHAR,@amount_cd)+’) ‘
SET @query = ‘UPDATE CSIACLedger set [‘+@column_name_db+’] = ‘+@query_db+’,
[‘+@column_name_cd+’] = ‘+@query_cd+’
where [YearPeriod]=’+@yearprd+’ and [AccountNo]=’+@coa_no+’ ‘
EXEC(@query)
SET @query = ‘UPDATE CSIACLedger set
[‘+@column_name_eb+’] = abs([‘+@column_name_db+’] – [‘+@column_name_cd+’])
where [YearPeriod]=’+@yearprd+’ and [AccountNo]=’+@coa_no+’ ‘
EXEC(@query)
END
–Insert Sub Ledger
SET @exist_bb = 0;
SET @exist_db = 0;
SET @exist_cd = 0;
SET @exist_eb = 0;
IF(@subledgertype = 2)
BEGIN
IF((select count(*) from CSIACSubLedgerEmployee where YearPeriod = @yearprd and
AccountNo = @coa_no and CSIEmployeeId = @employeeId) = 0)
BEGIN
SET @exist_eb = abs(@amount_db – @amount_cd)
SET @query =’INSERT INTO CSIACSubLedgerEmployee ([yearperiod],[AccountNo],[CSIEmployeeId],[‘+@column_name_db+’],[‘+@column_name_cd+’],
[‘+@column_name_bb+’],[‘+@column_name_eb+’],[lastupdated], [lastupdater] )
values
(”’+@yearPrd+”’,”’+@coa_no+”’,’+convert(varchar,@employeeId)+’, ‘+convert(varchar,@amount_db)+’,’+convert(varchar,@amount_cd)+’,
‘+convert(varchar,@exist_bb)+’, ‘+convert(varchar,@exist_eb)+’,{fn NOW()},1) ‘
EXEC(@query)
END
ELSE
BEGIN
SET @query_db = ‘((SELECT ISNULL(‘+@column_name_db+’,0) FROM CSIACSubLedgerEmployee WHERE [YearPeriod]=’+@yearprd+’ and [AccountNo]=’+@coa_no+’
and CSIEmployeeId = ‘+convert(varchar,@employeeId)+’) + ‘+CONVERT(VARCHAR,@amount_db)+’) ‘
SET @query_cd = ‘((SELECT ISNULL(‘+@column_name_cd+’,0) FROM CSIACSubLedgerEmployee WHERE [YearPeriod]=’+@yearprd+’ and [AccountNo]=’+@coa_no+’
and CSIEmployeeId = ‘+convert(varchar,@employeeId)+’) + ‘+CONVERT(VARCHAR,@amount_cd)+’) ‘
SET @query = ‘UPDATE CSIACSubLedgerEmployee set [‘+@column_name_db+’] = ‘+@query_db+’,
[‘+@column_name_cd+’] = ‘+@query_cd+’
where [YearPeriod]=’+@yearprd+’ and [AccountNo]=’+@coa_no+’ and CSIEmployeeId=’+convert(varchar,@employeeId)
EXEC(@query)
SET @query = ‘UPDATE CSIACSubLedgerEmployee set
[‘+@column_name_eb+’] = abs([‘+@column_name_db+’] – [‘+@column_name_cd+’])
where [YearPeriod]=’+@yearprd+’ and [AccountNo]=’+@coa_no+’ and CSIEmployeeId=’+convert(varchar,@employeeId)
EXEC(@query)
END
END
IF(@subledgertype = 3)
BEGIN
IF((select count(*) from CSIACSubLedgerPartner where YearPeriod = @yearprd and
AccountNo = @coa_no and PartnerNo = @partnerNo) = 0)
BEGIN
SET @exist_eb = abs(@amount_db – @amount_cd)
SET @query =’INSERT INTO CSIACSubLedgerPartner ([yearperiod],[AccountNo],[PartnerNo],[‘+@column_name_db+’],[‘+@column_name_cd+’],
[‘+@column_name_bb+’],[‘+@column_name_eb+’],[lastupdated], [lastupdater] )
values
(”’+@yearPrd+”’,”’+@coa_no+”’,”’+@partnerNo+”’, ‘+convert(varchar,@amount_db)+’,’+convert(varchar,@amount_cd)+’,
‘+convert(varchar,@exist_bb)+’, ‘+convert(varchar,@exist_eb)+’,{fn NOW()},1) ‘
EXEC(@query)
END
ELSE
BEGIN
SET @query_db = ‘((SELECT ISNULL(‘+@column_name_db+’,0) FROM CSIACSubLedgerPartner WHERE [YearPeriod]=’+@yearprd+’ and [AccountNo]=’+@coa_no+’
and PartnerNo = ”’+@partnerNo+”’) + ‘+CONVERT(VARCHAR,@amount_db)+’) ‘
SET @query_cd = ‘((SELECT ISNULL(‘+@column_name_cd+’,0) FROM CSIACSubLedgerPartner WHERE [YearPeriod]=’+@yearprd+’ and [AccountNo]=’+@coa_no+’
and PartnerNo = ”’+@partnerNo+’) + ”’+CONVERT(VARCHAR,@amount_cd)+’) ‘
SET @query = ‘UPDATE CSIACSubLedgerPartner set [‘+@column_name_db+’] = ‘+@query_db+’,
[‘+@column_name_cd+’] = ‘+@query_cd+’
where [YearPeriod]=’+@yearprd+’ and [AccountNo]=’+@coa_no+’ and PartnerNo=”’+@partnerNo+”’ ‘
EXEC(@query)
SET @query = ‘UPDATE CSIACSubLedgerPartner set
[‘+@column_name_eb+’] = abs([‘+@column_name_db+’] – [‘+@column_name_cd+’])
where [YearPeriod]=’+@yearprd+’ and [AccountNo]=’+@coa_no+’ and PartnerNo=”’+@partnerNo+”’ ‘
EXEC(@query)
END
END
–Ledger for Account Parent
SET @coa_no_parent_old = @coa_no
IF((select (LevelNo-1) from CSIAccount where AccountNo=@coa_no) >= 0)
BEGIN
SET @count = 0
WHILE((select ((LevelNo-1)-@count) from CSIAccount where AccountNo=@coa_no) > 0)
BEGIN
SET @exist_bb = 0;
SET @exist_db = 0;
SET @exist_cd = 0;
SET @exist_eb = 0;
select @coa_no_parent = AccountNoGroup from CSIAccount where AccountNo=@coa_no_parent_old
IF((select count(*) from CSIACLedger where YearPeriod = @yearprd and AccountNo = @coa_no_parent) = 0)
BEGIN
SET @exist_eb = abs(@amount_db – @amount_cd)
SET @query =’INSERT INTO CSIACLedger ([yearperiod],[AccountNo],[‘+@column_name_db+’],[‘+@column_name_cd+’],
[‘+@column_name_bb+’],[‘+@column_name_eb+’],[lastupdated], [lastupdater] )
values
(”’+@yearPrd+”’,”’+@coa_no_parent+”’,’+convert(varchar,@amount_db)+’,’+convert(varchar,@amount_cd)+’,
‘+convert(varchar,@exist_bb)+’, ‘+convert(varchar,@exist_eb)+’,{fn NOW()},1) ‘
EXEC(@query)
END
ELSE
BEGIN
SET @query_db = ‘((SELECT ISNULL(‘+@column_name_db+’,0) FROM CSIACLedger WHERE [YearPeriod]=’+@yearprd+’ and [AccountNo]=’+@coa_no_parent+’)
+ ‘+CONVERT(VARCHAR,@amount_db)+’) ‘
SET @query_cd = ‘((SELECT ISNULL(‘+@column_name_cd+’,0) FROM CSIACLedger WHERE [YearPeriod]=’+@yearprd+’ and [AccountNo]=’+@coa_no_parent+’)
+ ‘+CONVERT(VARCHAR,@amount_cd)+’) ‘
SET @query = ‘UPDATE CSIACLedger set [‘+@column_name_db+’] = ‘+@query_db+’,
[‘+@column_name_cd+’] = ‘+@query_cd+’
where [YearPeriod]=’+@yearprd+’ and [AccountNo]=’+@coa_no_parent+’ ‘
EXEC(@query)
SET @query = ‘UPDATE CSIACLedger set
[‘+@column_name_eb+’] = abs([‘+@column_name_db+’] – [‘+@column_name_cd+’])
where [YearPeriod]=’+@yearprd+’ and [AccountNo]=’+@coa_no_parent+’ ‘
EXEC(@query)
END
select @coa_no_parent_old = AccountNo from CSIAccount where AccountNo = @coa_no_parent
SET @count = @count + 1
END
END
FETCH jurnalItemCs INTO @journalNo, @coa_no, @amount_db, @amount_cd, @subledgertype, @employeeId, @partnerNo
END
CLOSE jurnalItemCs
DEALLOCATE jurnalItemCs
–Update Journal Status
SET @query = ‘update CSIACJournalEntryItem set IsPosted = ”true” where JournalNo = ”’+@journalNo+”’ ‘;
EXEC (@query)
SET @query = ‘update CSIACJournalEntry set IsPosted=”true” where JournalNo = ”’+@journalNo+”’ ‘;
EXEC (@query)
RETURN 1