Simplicity is the secret !
I believe the terms used such as voucher number and bank payment is more of a sub ledgers, but anyway, even if the request is to manage the journal entry on the GL level, it is doable as well (excluding the voucher type). After all, the journal entry allows 8 digits. Including the year and month would occupy 4 digits, leaving only 4 digits for the serial number, which I believe is not enough at all. For instance (15070023)
- 15 refers to 2015
- 07 refers to July
- 0023 refers to the serial number
The above implies that you are limited to 9999 journal entries within a month, which I believe is not quite logical ..
Although, supposing that 4 digit serial is enough, and the type is excluded from the format, this can be fulfilled. There is no need for massive customization and lines of codes to get this handled, I have managed to accomplish this by building the following job
USE [msdb]
GO
/****** Object: Job [Jb_ChangeNextJournalEntryNumber] Script Date: 8/7/15 3:32:30 AM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 8/7/15 3:32:30 AM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'Jb_ChangeNextJournalEntryNumber',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'MahmoudSaadi\Mahmoud', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Step One] Script Date: 8/7/15 3:32:31 AM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Step One',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'BEGIN
IF ( DAY(GETDATE()) = 1
AND ( SELECT CONVERT(CHAR, GETDATE(), 14)
) BETWEEN ''00:00:00:000''
AND ''00:05:00:000''
)
BEGIN
UPDATE dbo.GL40000
SET NJRNLENT = CONCAT(RIGHT(FORMAT(YEAR(GETDATE()), ''0000''), 2),
FORMAT(MONTH(GETDATE()), ''00''), ''0001'')
END
END
',
@database_name=N'FIVER',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'Default',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=1,
@freq_subday_interval=1,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20150701,
@active_end_date=99991231,
@active_start_time=100,
@active_end_time=235959,
@schedule_uid=N'934f6467-0247-4e89-9b58-c14d8037bd1e'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO
It works perfectly,
Your feedback is highly appreciated,
