You’re offline. This is a read only version of the page.
Is it possible to have customized voucher numbers. For example like YYYY/MM/VoucherType/0001; so that first JV of Feb 2015 has the number 2015/02/JV/0001; and second bank payment of Mar 2015 has the number 2015/03/BP/0002
*This post is locked for comments
The sequence is not a requirement, I can barely think of any international accounting standards which implies such requirements. If it was, It would be an audit request.
Now, if the client is aware of the 9999 limit and their transactions would not exceed this limit, then they are definitely okay to go with this solution.
We have already applied it in a number of sites with limited number of journal without neither confusion no problems.
So, every first day of month we need to update the next JV number to "YYMM0001". If so, then it will not come to sequence number and also it will not suitable for JV with more than 9999.
For example, July month I have posted 9999 JVs(15079999), after that the next number will change to 15080000. So, it will make confusion and problems.
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)
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
Share
Report
All responses (
Answers (
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,
Hi,
There is an out of the box feature in GP that might meet your requirements. It's useful in countries like Germany.
Tools - Setup - Company - Company - Options:
Enable Posting Numbers in General Ledger
Mark this option to assign each transaction a unique sequence number during posting, in addition to the journal entry number. Use this option if you want solid numbering of transactions, without gaps. Solid numbering is a legal requirement in some countries, for auditing purposes.
If you use posting numbers, each fiscal year or fiscal period has it own sequence, usually beginning at 1. After you enable posting numbers, you must select to display posting numbers per fiscal year or per fiscal period on inquiries and reports.
=====================================
This essentially updates the GL20000/30000 PSTGNMBR and PPSGNMBR columns with the Posting Number and Posting Period. If you combine this information with what most of us are familiar with in GL20000/30000 (as Soma mentions: Source Document, Originating Transaction Source, Trx date, etc.), the this may meet the requirements.
I recommend testing this out in a test environment and letting us know if this will work.
Thanks,
Justin
Hi Mohmoud,
rehanessa is wanted the Custom voucher number in General Ledger module (JV number). Means while posting any transaction from subledger module the automatic JV number should be the specified format like "YYYY/MM/VoucherType/0001".
Custom document number generation in subledger module is doable. But, how about JV number generation which was posted from subledger?
It is doable, we have done this exact customization and it is provided as a product by Dynamics Innovations
Although, the requirements were different in terms of the voucher number parts, which includes as follows:
Group Prefix - Transaction Reference - Serial Number
For instance HDQRT_APINV0000123
This indicates that this is an AP Invoice for the Head Quarter, and the serial number is 123.
The setup is defined on the company level as shown below:
This product encompasses all standard module forms including AP, AR, POP, SOP, IV.
If you are interested, contact Dynamics Innovations for any required details.
May I know the reason for this Journal Entry Number update?
This is very difficult to customize. Because, we can do it easily for manual Journal entry transaction. But, automatic journal creation/posting from sub-ledger module is very difficult. But we can achieve. We need to consider many things (It will take time).
If you want to identify whether this JV is belongs to which transaction such as Bank, AP, AR, IV, etc (Subledger) and month & Year, you can easily find out based some fields Source Document, Originating Transaction Source, Trx date, etc., from GL table.
If you explain your reason for this changes, we will give you the good suggestions.
Hope this helps!!!
There is no built in functionality in GP but you can achieve this thru customization. Also consider the field length of the Voucher number.
The out of the box answer is no - but I would hold out hope that someone in the community knows of a customization that might make that possible.
I would suggest that it is a lot of work for an internal counter when there are other options that might make tracking simpler. I am sure that knowing the dates of the voucher is key to finding them, but all vouchers have enter and post dates. I might suggest looking at the Smartlist tool and see what you can use to search for your vouchers.
Customization of this nature might be pretty costly and it might also be restrictive by version and an upgrade could again cost.
Just my thoughts.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,253
Super User 2024 Season 2
#2
Martin Dráb
230,188
Most Valuable Professional
#3
nmaenpaa
101,156