The following article has been transferred to https://help.synergetic.net.au/s/article/Transfer-journals-from-one-database-to-another
How to setup Synergetic to transfer journals from one database to another.
Synergetic can be setup to transfer journals from one finance database to another. This is sometimes required by companies that run a separate finance database for fundraising or executive pays. They wish to process within a separate finance database but transfer details across to the main finance database for reporting purposes.
You achieve this in Synergetic by setting the accounts to be transferred within General Ledger Maintenance and then running a process to actually do the transfer.
The transfer will generate two postings. The first posting will be in the source database which will effectively reverse out the balances, and then another in the target database to transfer the balances into there.
When a journal in the source database is reversed, it is flagged with the posting number that was used to reverse it. This way the same journal is not processed twice. It also means that if you abandon the source posting the original journals can be "unflagged".
General Ledger Maintenance
Use General Ledger Maintenance to specify which codes get transferred. You need to be logged into the database that you are transferring from - this is called the source database. You are then setting detail to transfer into the target database. For example, if you wish to transfer journals from the foundation database to the main finance database then you must be logged into the foundation database.
If you are running version 65 onwards you will see the following screen, if you are running V64 you will need a user form created:-
Do not transfer Obvious
Summarised Journal Balance This will sum all the journals for a given general ledger code and put the result into the other database. Please note that tax code and sub-allocation code are also taken into account when summarising. If all journals have the same tax code and sub-allocation code then all will be summarised to a single journal. If tax codes and/or sub-allocation codes vary there will be more journals created.
Detailed Journals Will copy the journals across on a one for one basis, therefore 16 journals in the source database will result in 16 journals in the target database.
Target Finance Database
This is the finance database that the journals are to be transferred into. The list only contains finance databases other than the one you are logged into.
Target G/L Code
Specify the GL code to be used on the target database. If this is left blank then the GL code on the source database will be used. The only reason you may choose to populate this field is if you are trying to summarise a group of GL codes from the source database into a single entry on the target database. If this is the case then you would set the same Target G/L Code on all source G/L codes that summarise to it.
From the General Ledger menu select 'Transfer Journals to Other Finance Database' and the following screen appears:-
Here is where you define the parameters for your transfer. Please note that pressing the 'Apply' button will save your selections but will not run the transfer. This is useful if you have the transfer running as a scheduled SQL server job and do not want to run the transfer at this point in time.
Transfer to Database
Select the database you wish to transfer the journals too. There may be more than one database in the list and you will need to populate the details for each database you will be transferring to. Also, when the transfer gets run Synergetic will only transfer journals on the GL codes that match the database you have selected.
Minimum Journal Date
Set the minimum date to be processed for journals. Synergetic will not transfer any journals dated prior to this, also this date only needs to be set once. Once Synergetic has performed the transfer, the processed journals are flagged and will not be processed again.
Clearing GL Code
This is the General Ledger code used for the clearing journal generated as part of the postings. Eg if the sum of the accounts transferred came to $1000 debit, then a $1000 credit journal would be written into the source posting and $1000 debit to the target. You need to define the Clearing GL for both the source and target database.
Posting Descriptions used for the transfers. This description will also be used for summarised journals, eg if you select the summarised transfer mode all journals will receive this description, otherwise, just the clearing journal will receive it. You specify a description for both the source and target database.
Running the Transfer from within Synergetic
From the General Ledger menu select 'Transfer Journals to Other Finance Database' and the previously documented configuration screen appears. Select the database you wish to transfer to and press the 'Go' button.
This will generate the postings in both the source and target databases and as well as create the printouts.
Please note that when running the transfer from within Synergetic you will need to be logged in as a high-level user.
In v67 or earlier this a user who is a member of the database role SynergeticGLJournalTransfer. They will need to be a member of this role in both the source and target database.
In v68+ this is a user who is a member of the role Synergetic_JournalTransfer_finance_ReservedDBRole and the related finance database (ie. Synergetic_JournalTransfer_extrafinance_ReservedDBRole)
If you are using AD authentication, you will need to give permissions to the relevant AD group and not a SQL user.
If the user does not have an appropriate level of SQL permission it will cause errors similar to the following:
We do not use an application role when running the transfer because it processes multiple finance databases using dynamic SQL - and the app role is unable to do this.
Running the Transfer as a scheduled job
If you wish to run the transfer as a scheduled SQL server job, you need to call the stored procedure called 'sptGeneralLedgerJournalsTransfer'.
- Please note that the job MUST run from the SOURCE database. For example, if you are transferring from the Foundation database to the Main Finance Database then you must run the job from the Foundation database.
- ALSO, you will need to ensure that the SQL service account used for the agent job
The stored procedure 'sptGeneralLedgerJournalsTransfer' takes the following optional parameters:-
- @SynDatabaseCodeTarget – use this to specify the name of the database to be transferred into. This value is only needed if you transfer into more than one database and you wish to transfer them at different times. If you do transfer multiple databases at one time there will be a transfer done for each of them.
- @PostingNumberSource - returns the posting number of the source posting
- @PostingNumberTarget – returns the posting number of the target posting
No printouts are generated for the scheduled job. To obtain these you will need to run General Ledger Journals entry on both the source and target database. Select the 'Past G/L Journals Posting' radio button, highlight the generated posting and press the 'Preview Button'
Abandoning the Transfer
If you wish to abandon a transfer it will need to be done in both databases, this is because you need to manually abandon both the source and target postings. Do this using the General Ledger Journals entry program and select the posting from the Past Postings list. You can then abandon in the normal way.
When the source posting is abandoned, all original journals that were processed as part of the transfer are "unflagged". They will be processed again when the next transfer takes place.