UBS: Difference between revisions

From eStream Software
 
(109 intermediate revisions by 2 users not shown)
Line 1: Line 1:
==UBS==
==Module that can be import from Sage UBS==
=='''Module that can be import from Sage UBS'''==
{| class="wikitable"
{| class="wikitable"
|-
|-
! Data Migration from Sage UBS  !! Module  
! Data Migration from Sage UBS  !! Module !! Data Migration from Sage UBS  !! Module !! Data Migration from Sage UBS  !! Module
|-
|-
| Chart of Account ||  
| Chart of Account || align="center" |Yes || Journal Entry || align="center" |Yes || Sales Debit Note || align="center" |Yes
* Yes
|-
|-
| Customer Master ||  
| Customer Master || align="center" |Yes || Bank Reconciliation || Coming Soon || Sales Credit Note || align="center" |Yes
*Yes
|-
|-
| Supplier Master ||  
| Supplier Master || align="center" |Yes || AR/ Customer Invoice || align="center" |Yes || Price History - Sales Invoice || align="center" |Yes
*Yes
|-
|-
| Stock Master ||  
| Stock Master || align="center" |Yes || AR/ Customer Debit Note || align="center" |Yes || Price History - Sales Delivery Order || align="center" |Yes
*Yes
|-
|-
| Agent Master ||  
| Agent Master || align="center" |Yes || AR/ Customer Credit Note || align="center" |Yes || AP/Supplier Invoice || align="center" |Yes
*Yes
|-
|-
| Project Master ||  
| Project Master || align="center" |Yes || AR/ Customer Payment || align="center" |Yes || AP/Supplier Debit Note  || align="center" |Yes
*Yes
|-
|-
| Area Master ||  
| Area Master || align="center" |Yes || Sales Quotation || align="center" |No || AP/Supplier Credit Note || align="center" |Yes
*Yes
|-
|-
| Currency Master ||  
| Currency Master || align="center" |Yes || Sales Order || align="center" |No || Purchase Order || align="center" |No
*Yes
|-
|-
| Terms Master ||  
| Terms Master || align="center" |Yes || Sales Delivery Order || align="center" |No || Purchase Invoice || align="center" |Yes
*Yes
|-
|-
| GL Opening Balance ||  
| GL Opening Balance || Coming Soon || Sales Invoice || align="center" |Yes || Purchase Debit & Credit Note || align="center" |Yes
*Coming Soon
|-
|-
| Cash Book Entry ||  
| Cash Book Entry || align="center" |Yes || Sales Cash Sales || align="center" |Yes || Price History - Purchase Invoice || align="center" |Yes
*Yes
|}
|-
 
| Journal Entry ||  
===Time Estimate===
*Yes
<small>Different pc might have different timing.</small>
{| class="wikitable"
|-
|-
| Bank Reconciliation ||
! Duration of migration from Sage UBS !! 1000 Records !! 10,000 Records
*Coming Soon
|-
|-
| AR/ Customer Invoice ||  
| Masterfile || 2-5 Mins || 8-10 Mins
*Yes
|-
|-
| AR/ Customer Debit Note ||  
| AR/AP Opening Balance || 5-10 Mins || 15-30 Mins
*Yes
|-
|-
| AR/ Customer Credit Note ||  
| Sales/ Purchase Price History || 5-10 Mins || 15-30 Mins
*Yes
|-
|-
| AR/ Customer Payment ||  
| Sales/ Purchase Current Transactions || 30-50 Mins || 1.5-2 Hours
*Yes
|-
|-
| Sales Quotation ||
| AR/AP Current Transactions || 30-40 Mins || 1.5-2 Hours
*No
|-
| Sales Order ||
*No
|-
| Sales Delivery Order ||
*Yes
|-
| Sales Invoice ||
*Yes
|-
| Sales Cash Sales ||
*Yes
|-
| Sales Debit Note ||
*Yes
|-
| Sales Credit Note ||
*Yes
|-
| Price History - Sales Invoice ||
*Yes
|-
| Price History - Sales Delivery Order ||
*Yes
|-
| AP/Supplier Invoice ||
*Yes
|-
| AP/Supplier Debit Note ||  
*Yes
|-
| AP/Supplier Credit Note ||
*Yes
|-
| AP/Supplier Payment ||
*Yes
|-
| Purchase Order ||
*No
|-
| Purchase Invoice ||
*Yes
|-
| Purchase Debit Note ||
*Yes
|-
| Purchase Credit Note ||
*Yes
|-
| Price History - Purchase Invoice ||
*Yes
|-
|-
| GL Current Transactions || 10-15 Mins || 25-40 Mins
|}
|}


=='''Limitation & Precaution'''==
===Flow of Import===
Account Master File ( GL, Customer, Supplier.. )--> Stock Master File ( Stock Group, Stock Item..)--> Stock ( Sales Price History..)
--> Stock ( Current Transaction)--> Account ( Opening Balance.. )--> Account (Current Transaction..)
 
==Limitation & Precaution==
<ol>
<ol>
<li>No support for opening balance with foreign currency.</li>
<li>No support for opening balance with foreign currency.</li>
Line 118: Line 56:
<li>Always import Price History first before AR/AP Opening Balance.</li>
<li>Always import Price History first before AR/AP Opening Balance.</li>
* This is to avoid system from auto-delete AR/AP opening balance when importing Price History with same document number.
* This is to avoid system from auto-delete AR/AP opening balance when importing Price History with same document number.
<li> This is not applicable for UBS Sage 50.</li>
</ol>
</ol>


=='''Housekeeping'''==
==Preparing SQL Account Before Import Master Data==
<ol>
<pre style="color: red; font-weight: bold">
<li>After UBS database is restored, run the following Housekeeping operation:-</li>
Please make sure that SQL Account version is version 728 and above.</pre>
* Index All Files
* Recover Data After Power Failure
* Delete Unwanted Transactions
</ol>
 
=='''Preparing SQL Account for Data Import'''==
<p>Here are the things to do before data import:-</p>
<p>Here are the things to do before data import:-</p>
<ol>
<ol>
<li><b>Open SQL Accounting and create a new empty database.</b></li>
<li><b>Open SQL Account and create a new empty database.</b></li>
</ol>
<p>The following steps is only needed if you are importing opening balance or current transactions:-</p>
<ol>
<li>Go to <b>File | Product Activation and insert your Activation Code</b> or <b>Season Code</b> and click Register.</li>
<li>If current transactions involve GST tax code, go to <b>GST | Start GST Now to activate GST module.</b></li>
<li>To minimize any pop-up dialog during import:-</li>
* <b>Go to Tools | Options | Unit Price. For all tabs (Sales, Purchase, Stock, Cash Sales & Cash Purchase), under the Option column, change all to blank</b> as shown below:-
[[File:Tips options uprice.JPG]]
* Go to <b>Tools | Options | Miscellaneous and untick <b>Prompt Negative Stock Quantity Dialog Box</b> and <b>Prompt Duplicate Cheque Number</b>
* Logout and login as the new user you created.
* You may revert the changes back to original after data import.
<li><b>Go to Tools | Options | General Ledger and change the Financial Start Period</b> and <b>System Conversion Date</b> if needed.</li>
</ol>
</ol>


=='''Restore database from UBS'''==
==Restore database from UBS==
<p>Here are the step-by-step guide how to restore database from UBS:-</p>
<p>Here are the step-by-step guide how to restore database from UBS:-</p>
<ol>
<ol>
<li>Get the UBS Backup file ( BACKUP.ACC & BACKUP.STK ) and place the UBS backup file to C:\UBSACC90\DB\20140704, every time create new folder under DB for new backup file.</li>
<li>Get the UBS Backup file ( BACKUP.ACC & BACKUP.STK ) and place the UBS backup file to the correct version database folder, example as below :</li>
  <b><u>Directory to place UBS Backup File as below :</u></b>
  <b><u>Directory to place UBS Backup File as below :</u></b>
  USB Account = C:\UBSACC90\DB or C:\UBSACC2015\DB
  USB Account Old Version = C:\UBSACC90\DB or C:\UBSACC2015\DB
  UBS Stock = C:\UBSSTK90\DB or C:\UBSSTK2015\DB
  UBS Stock Old Version = C:\UBSSTK90\DB or C:\UBSSTK2015\DB
Sage UBS Account = C:\UBSACC2015\create new folder for your backup file.
Sage UBS Stock = C:\UBSSTK2015\create new folder for your backup file.
<li>Download 7 Zip to extract UBS Backup file,( if you want to  Import Master File Only) or else you may restore to UBS program ( For price history and current transaction ).</li>
<li>Download 7 Zip to extract UBS Backup file,( if you want to  Import Master File Only) or else you may restore to UBS program ( For price history and current transaction ).</li>
<b><u>Download Path for 7 Zip :</u></b>
[https://www.7-zip.org/ Download Path for 7 Zip]
Window 32 Bits : http://www.sql.com.my/utility/7-zip.msi
Window 64 Bits: http://www.sql.com.my/utility/7-zip-x64.msi
<pre style="color: red; font-weight: bold">
<pre style="color: red; font-weight: bold">
* Make sure install the right one or else you cannot find 7 zip to Extract.
* Make sure install the right one or else you cannot find 7 zip to Extract.
</pre>
</pre>
[[File:Pic1.jpg]]
[[File:Pic1.jpg]]
<li>After extract the backup file or restore the backup file then in SQL Accounting Software you have to Create New Database and logon into the new company.
<li>After extract the backup file or restore the backup file then in SQL Account Software you have to Create New Database and logon into the new company. </li>
For more info you may refer to : https://www.youtube.com/watch?v=H7C14DzLyPE
Video Link may refer below : https://www.youtube.com/watch?v=H7C14DzLyPE
 
</ol>
</ol>


=='''Import Master Data'''==
==Import Master Data==
===Import Account Master Data===
<p>Here are the step-by-step guide to importing master data:-</p>
<p>Here are the step-by-step guide to importing master data:-</p>
<ol>
<ol>
Line 174: Line 97:
* Account which mean import COA, Maintain Customer, Maintain Supplier, Agent, Area, Term,  Project, Currency and Customer & Supplier Outstanding
* Account which mean import COA, Maintain Customer, Maintain Supplier, Agent, Area, Term,  Project, Currency and Customer & Supplier Outstanding
* Stock which mean import Stock Group, Stock Category, Stock Location, Stock Price Tag, Stock Item, Sales Invoice History, Sales Delivery Order History, Purchase Invoice History.
* Stock which mean import Stock Group, Stock Category, Stock Location, Stock Price Tag, Stock Item, Sales Invoice History, Sales Delivery Order History, Purchase Invoice History.
<li>Assign the UBS Account & UBS Stock backup folder accordingly with click on “Select Folder” then click Connect, first of all you must import Chart of Account.Tick Chart of Account and Execute.<br />
<li>Assign the UBS Account & UBS Stock backup folder accordingly with click on “Select Folder” then click Connect, first of all you must import Chart of Account.Tick Chart of Account and Execute.<br /></li>


[[File:UBSView2.jpg]]
[[File:UBSView2.jpg]]
<li>Once import is done, go to GL | Maintain Account and check the trade debtor/creditor control account, bank account & etc. if they exist and their respective <b>Special Account Type</b> is correct.
<li>Once you have done import Chart of Account, it’s prompt an message that successful and you have to do few setting according to the message.<br /></li>
[[File:UBSView3.jpg]]
<li>Go to GL | Maintain Account and check the trade debtor/creditor control account, bank account & etc. if they exist and their respective <b>Special Account Type</b> is correct.
* Go to Tools | Options | Customer  and change the <b>Default Control Account</b>. Repeat the same for Supplier.
* Go to Tools | Options | Customer  and change the <b>Default Control Account</b>. Repeat the same for Supplier.
* Go to Tools | Options | General Ledger and set the <b>Default Account</b> accordingly.
[[File:UBSView4.jpg]]
* Go to Tools | Options | General Ledger and set the <b>Default Account</b> accordingly</li>
[[File:UBSView5.png]]
<li>Now, go back to File | Data Import | UBS Account and import the remaining master data - Agent, Area, Project, Terms, Currency, Customer and Supplier Master Account</li>
<li>Now, go back to File | Data Import | UBS Account and import the remaining master data - Agent, Area, Project, Terms, Currency, Customer and Supplier Master Account</li>
<li>Click Execute and wait for the import to complete.</li>
<li>Click Execute and wait for the import to complete.</li>
<li>Go to  File | Data Import | UBS Stock and select the UBS Stock database folder.</li>
Video link may refer below : https://youtu.be/H7C14DzLyPE?t=1m26s
* Tick Stock Group, Category, Location, Price Tag and Stock Item.
 
* Also pay attention to the options below where you can choose to import extra Stock Item data such as <b>UOM with Rate < 1</b>, <b>Stock Opening Balance</b> & etc.
===Import Stock Master Data===
<ol>
<li>Go to  File | Data Import | UBS Stock.<br />
 
[[File:UBSView6.jpg]]
<li>select the UBS Stock database folder.</li>
* Tick Stock Group, Category, Location, Price Tag.
<pre style="color: red; font-weight: bold">
Please make sure user have purchase those module before import!</pre>
[[File:UBSView7.jpg]]
<li>When import stock item code, make sure you have choose others option as below :<br />
 
[[File:UBSView8.jpg]]
<pre style="color: red; font-weight: bold">
* Also pay attention to the options below where you can choose to import extra Stock Item data such as UOM with Rate < 1,
  Stock Opening Balance & etc.
</pre>
'''If you want to import Stock Customer & Supplier price:'''<br />
 
[[File:2016-10-18 1223.png]]
<pre style="color: red; font-weight: bold">
this feature only available in version 737 and above. </pre>
<li>Click Execute and wait for the import to complete.</li>
<li>Click Execute and wait for the import to complete.</li>
Video link may refer below :  https://www.youtube.com/watch?v=doPrsNK7N6M
</ol>
</ol>


=='''Preparing UBS for Sales & Purchase Price History Data Import'''==
==Import Sales & Purchase Price History and Opening Balance==
===Preparation Before Import Transactions===
<p>The following steps are required if you are importing opening balance or current transactions:-</p>
<ol>
<li>Go to <b>File | Product Activation and insert your Activation Code</b> or <b>Season Code</b> and click Register.</li>
<li>If current transactions involve GST tax code, go to <b>GST | Start GST Now to activate GST module.</b></li>
<li>To minimize any pop-up dialog during import:-</li>
* <b>Go to Tools | Options | Unit Price. For all tabs (Sales, Purchase, Stock, Cash Sales & Cash Purchase), under the Option column, change all to blank</b> as shown below:-
[[File:Tips options uprice.JPG]]
* Go to <b>Tools | Options | Miscellaneous and untick <b>Prompt Negative Stock Quantity Dialog Box</b> and <b>Prompt Duplicate Cheque Number</b>
* Logout and login as the new user you created.
* You may revert the changes back to original after data import.
<li><b>Go to Tools | Options | General Ledger and change the Financial Start Period</b> and <b>System Conversion Date</b> if needed.</li>
</ol>
 
===Import Sales & Purchase Price History Data===
<ol>
<ol>
<li>Before importing Sales & Purchase Price History, login into UBS and perform the following steps:-</li>
<li>Before importing Sales & Purchase Price History, login into UBS and perform the following steps:-</li>
* Transaction | Open Item Debtor | Edit B/F or O/I | Must tick mark all to O/I | Exit
<pre style="color: blue; font-weight: bold">
* Transaction | Open Item Debtor | Maintain B/F Bills | Click "List" (Exit "List" and click "Generate" if there are transactions)
1. Transaction | Open Item Debtor | Edit B/F or O/I | Must tick mark all to O/I | Exit
* Transaction | Open Item Debtor | Bill Payment Ledger | List Missing Records in Arpost.DBF | Press OK if there are items in the list or press Exit if non
2. Transaction | Open Item Debtor | Maintain B/F Bills | Click "List"  
<li>Repeat Step 3 for Creditor.</li>
  (Exit "List" and click "Generate" if there are transactions)
3. Transaction | Open Item Debtor | Bill Payment Ledger | List Missing Records in Arpost.DBF |
4. Press OK if there are items in the list or press Exit if non
</pre>
<li>Repeat above step for Creditor.</li>
<li>Please check with clients if they want to do UBS Account Year End Processing before importing Debtor or Creditor Opening Balance.</li>
<li>Please check with clients if they want to do UBS Account Year End Processing before importing Debtor or Creditor Opening Balance.</li>
<li><b>Login SQL Account, make sure you have insert season code for this database, and Start GST feature in SQL Account Software</b>
<li>Now, go to File | Data Import | UBS Stock. Under Stock Item, tick the <b>Price History - XXX</b> options you wish to import.</li>  
<li>Now, go to File | Data Import | UBS Stock. Under Stock Item, tick the <b>Price History - XXX</b> options you wish to import.</li>  
[[File:UBSView10.jpg]]
Video link may refer below :  https://www.youtube.com/watch?v=s0GR8MDF5J4
</ol>
</ol>


=='''Preparing UBS for Opening Balance & Current Transaction Data Import'''==
===Import Opening Balance===
====Year End Closing at UBS====
<ol>
<ol>
<li>Before importing Opening Balance, login into UBS and perform the following steps:-</li>
<li>Before importing Opening Balance, login into UBS and perform the following steps:-</li>
<li>Go to Housekeeping | Run Setup | General Setting.</li>
* <b>Old Version = Go to Housekeeping | Run Setup | General Setting (check when is the accounting period) then insert " Closing Period".</b>
* Put the "Closing Period"
* <b>Sage UBS = Go to Housekeeping | Setup | General Setup (check when is the accounting period) then insert " Closing Period".</b>
<li>Then, go to Periodic | Year End Closing.</li>
<u>Scenario 1 :</u>
<li>After done the above steps, go to File | Data Import | UBS Account, then select the UBS database folder. Beware of the following when selecting UBS database folder:-
In UBS this accounting year = 01/01/2015 to 31/12/2015 if you want to cut off until 31/12/2015,
* <b>DO NOT</b> select the folder under the UBS database folder. E.g. "DATA2013"
so closing period you have to insert 12.
* After select folder, under Customer/Supplier Master Account, tick the Opening Customer/Supplier - XXX options you wish to import.
<u>Scenario 2 :</u>
<li>Finally, run Tools | Analyse Data Integrity and backup.</li>
In UBS this accounting year = 01/01/2015 to 31/12/2015 if you want to cut off until 30/06/2015,
</ol>
so closing period you have to insert 6.
<li>Then, go to <b>Periodic | Year End Closing.</b></li>
<pre style="color: purple; font-weight: bold">
We strongly recommend that direct use the year end database to import instead of re-do the year end cut off at UBS.</pre>
Video link may refer below : https://youtu.be/s0GR8MDF5J4?t=1m57s
 
====After Year End at UBS====
<li>After done the above steps, go to <b>Tools | Options | General Ledger | to setup your Financial Start Period and System Conversion Date.</b></li>
<li>Next, File | Data Import | UBS Account, then select the UBS database folder. Beware of the following when selecting UBS database folder:-
<pre style="color: red; font-weight: bold">
DO NOT select the folder under the UBS database folder. E.g. "DATA2013"  
This is because after year end cut off UBS will auto generate a new folder for old financial cut off period.
</pre>
<li>After select folder, under Customer/Supplier Master Account, tick the Opening Customer/Supplier - XXX options you wish to import.
[[File:UBSView9.jpg]]
<li>You may tick all the opening at the same time. (Customer Payment takes longer time to import in).
* Note : After done. May compare Customer Aging  with UBS :
* In UBS Step : 2. Debtors | A. Open Item Menu Debtors | 5. Print Detail Aging (SQL follow UBS  Detail Aging)
* In Sage UBS : 2. Debtors | 7. Print Debtors Aging (SQL follow UBS  Detail Aging)<br />
Video link may refer below : https://youtu.be/s0GR8MDF5J4?t=2m54s


=='''Import Current Transactions'''==
==Current Transaction Data Import==
<p>Import Sales & Purchase Current Transaction</p>
===Import Sales & Purchase Current Transaction===
<ol>
<ol>
<li>Go to File | Data Import | UBS Stock and select the UBS Stock database folder.</li>
<li>Go to File | Data Import | UBS Stock and select the UBS Stock database folder.</li>
* Under Stock Item, tick the Current Transaction - XXX options that you wish to import.
<li>Under Stock Item, tick the Current Transaction - XXX options that you wish to import.
* Click Execute and wait for the import to complete.
<li>Click Execute and wait for the import to complete.<br />
 
[[File:UBSView11.jpg]]
</ol>
===Import AP,AR,GL Current Transaction===
<ol>
<li>Go to File | Data Import | UBS Account and select the UBS Account database folder.</li>
<li>Go to File | Data Import | UBS Account and select the UBS Account database folder.</li>
* Under <b>Customer Current Transaction</b>, tick the [BETA] Current Customer - XXX options that you wish to import.
<li>Under <b>Customer Current Transaction</b>, tick the [BETA] Current Customer - XXX options that you wish to import.
* Click Execute and wait for the import to complete.
<li>Click Execute and wait for the import to complete.
* Repeat the same steps for options under <b>Supplier Current Transaction</b> and <b>GL Current Transaction</b>.
<li>Repeat the same steps for options under <b>Supplier Current Transaction</b> and <b>GL Current Transaction</b>.
[[File:UBSView12.jpg]]
</ol>
</ol>
=='''Troubleshooting'''==
 
==Troubleshooting==
<ol>
<ol>
<li>First, it is best to tick and import one item at a time and perform backup before importing the next item.</li>
<li>First, it is best to tick and import one item at a time and perform backup before importing the next item.</li>
Line 243: Line 241:
<pre style="color: red; font-weight: bold">
<pre style="color: red; font-weight: bold">
- Please double check all data imported into SQL Account from UBS.
- Please double check all data imported into SQL Account from UBS.
- We will appreciate if you would help report any issue encountered with detailed information so we can improve the data import tool.
- We will appreciate if you would help report any issue encountered with detailed information
  so we can improve the data import tool.
- Please be aware that Import Current Transaction is under BETA.
- Please be aware that Import Current Transaction is under BETA.
</pre>
</pre>
==Housekeeping==
<ol>
<li>After UBS database is restored, run the following Housekeeping operation:-</li>
* Index All Files
* Recover Data After Power Failure
* Delete Unwanted Transactions
</ol>


===Steps===
===Steps===
http://www.sql.com.my/document/Import%20UBS%20Guides.pdf
http://www.sql.com.my/document/Import%20UBS%20Guides.pdf<br />
<br />
<br />
 
 
<small>Sage UBS are the trademark of its owner. For more information on Sage product, go to www.sage.my
</small>
 
==FAQ==
===Prompt message "Provider cannot be found. It may not be properly install" when try import===
: Please install "VFPOLEDBSetup.msi" from [https://download.sql.com.my/customer/Fairy/vfpoledbsetup.msi]


==See also==
==See also==
* Others [[Data Import]]
* Others [[Data Import]]

Latest revision as of 01:15, 31 July 2023

Module that can be import from Sage UBS

Data Migration from Sage UBS Module Data Migration from Sage UBS Module Data Migration from Sage UBS Module
Chart of Account Yes Journal Entry Yes Sales Debit Note Yes
Customer Master Yes Bank Reconciliation Coming Soon Sales Credit Note Yes
Supplier Master Yes AR/ Customer Invoice Yes Price History - Sales Invoice Yes
Stock Master Yes AR/ Customer Debit Note Yes Price History - Sales Delivery Order Yes
Agent Master Yes AR/ Customer Credit Note Yes AP/Supplier Invoice Yes
Project Master Yes AR/ Customer Payment Yes AP/Supplier Debit Note Yes
Area Master Yes Sales Quotation No AP/Supplier Credit Note Yes
Currency Master Yes Sales Order No Purchase Order No
Terms Master Yes Sales Delivery Order No Purchase Invoice Yes
GL Opening Balance Coming Soon Sales Invoice Yes Purchase Debit & Credit Note Yes
Cash Book Entry Yes Sales Cash Sales Yes Price History - Purchase Invoice Yes

Time Estimate

Different pc might have different timing.

Duration of migration from Sage UBS 1000 Records 10,000 Records
Masterfile 2-5 Mins 8-10 Mins
AR/AP Opening Balance 5-10 Mins 15-30 Mins
Sales/ Purchase Price History 5-10 Mins 15-30 Mins
Sales/ Purchase Current Transactions 30-50 Mins 1.5-2 Hours
AR/AP Current Transactions 30-40 Mins 1.5-2 Hours
GL Current Transactions 10-15 Mins 25-40 Mins

Flow of Import

Account Master File ( GL, Customer, Supplier.. )--> Stock Master File ( Stock Group, Stock Item..)--> Stock ( Sales Price History..)
--> Stock ( Current Transaction)--> Account ( Opening Balance.. )--> Account (Current Transaction..)

Limitation & Precaution

  1. No support for opening balance with foreign currency.
  • This is because UBS have two posting (1 local, 1 foreign) for foreign currency transaction and sometimes one of the posting might be missing.
  • Always import Price History first before AR/AP Opening Balance.
    • This is to avoid system from auto-delete AR/AP opening balance when importing Price History with same document number.
  • This is not applicable for UBS Sage 50.
  • Preparing SQL Account Before Import Master Data

    Please make sure that SQL Account version is version 728 and above.

    Here are the things to do before data import:-

    1. Open SQL Account and create a new empty database.

    Restore database from UBS

    Here are the step-by-step guide how to restore database from UBS:-

    1. Get the UBS Backup file ( BACKUP.ACC & BACKUP.STK ) and place the UBS backup file to the correct version database folder, example as below :
    2. Directory to place UBS Backup File as below : USB Account Old Version = C:\UBSACC90\DB or C:\UBSACC2015\DB UBS Stock Old Version = C:\UBSSTK90\DB or C:\UBSSTK2015\DB Sage UBS Account = C:\UBSACC2015\create new folder for your backup file. Sage UBS Stock = C:\UBSSTK2015\create new folder for your backup file.
    3. Download 7 Zip to extract UBS Backup file,( if you want to Import Master File Only) or else you may restore to UBS program ( For price history and current transaction ).
    4. Download Path for 7 Zip
      * Make sure install the right one or else you cannot find 7 zip to Extract.
      

      Pic1.jpg

    5. After extract the backup file or restore the backup file then in SQL Account Software you have to Create New Database and logon into the new company.
    6. Video Link may refer below : https://www.youtube.com/watch?v=H7C14DzLyPE

    Import Master Data

    Import Account Master Data

    Here are the step-by-step guide to importing master data:-

    1. Go to File | Data Import | UBS Account and select the UBS Account database folder.
    2. UBSView1.png
      Info :
    • Account which mean import COA, Maintain Customer, Maintain Supplier, Agent, Area, Term, Project, Currency and Customer & Supplier Outstanding
    • Stock which mean import Stock Group, Stock Category, Stock Location, Stock Price Tag, Stock Item, Sales Invoice History, Sales Delivery Order History, Purchase Invoice History.
  • Assign the UBS Account & UBS Stock backup folder accordingly with click on “Select Folder” then click Connect, first of all you must import Chart of Account.Tick Chart of Account and Execute.
  • UBSView2.jpg
  • Once you have done import Chart of Account, it’s prompt an message that successful and you have to do few setting according to the message.
  • UBSView3.jpg
  • Go to GL | Maintain Account and check the trade debtor/creditor control account, bank account & etc. if they exist and their respective Special Account Type is correct.
    • Go to Tools | Options | Customer and change the Default Control Account. Repeat the same for Supplier.
    UBSView4.jpg
    • Go to Tools | Options | General Ledger and set the Default Account accordingly
    UBSView5.png
  • Now, go back to File | Data Import | UBS Account and import the remaining master data - Agent, Area, Project, Terms, Currency, Customer and Supplier Master Account
  • Click Execute and wait for the import to complete.
  • Video link may refer below : https://youtu.be/H7C14DzLyPE?t=1m26s

    Import Stock Master Data

    1. Go to File | Data Import | UBS Stock.
      UBSView6.jpg
    2. select the UBS Stock database folder.
    • Tick Stock Group, Category, Location, Price Tag.
    Please make sure user have purchase those module before import!

    UBSView7.jpg

  • When import stock item code, make sure you have choose others option as below :
    UBSView8.jpg
    * Also pay attention to the options below where you can choose to import extra Stock Item data such as UOM with Rate < 1,
      Stock Opening Balance & etc.
    

    If you want to import Stock Customer & Supplier price:

    2016-10-18 1223.png

    this feature only available in version 737 and above. 
  • Click Execute and wait for the import to complete.
  • Video link may refer below : https://www.youtube.com/watch?v=doPrsNK7N6M

    Import Sales & Purchase Price History and Opening Balance

    Preparation Before Import Transactions

    The following steps are required if you are importing opening balance or current transactions:-

    1. Go to File | Product Activation and insert your Activation Code or Season Code and click Register.
    2. If current transactions involve GST tax code, go to GST | Start GST Now to activate GST module.
    3. To minimize any pop-up dialog during import:-
    • Go to Tools | Options | Unit Price. For all tabs (Sales, Purchase, Stock, Cash Sales & Cash Purchase), under the Option column, change all to blank as shown below:-
    Tips options uprice.JPG
    • Go to Tools | Options | Miscellaneous and untick Prompt Negative Stock Quantity Dialog Box and Prompt Duplicate Cheque Number
    • Logout and login as the new user you created.
    • You may revert the changes back to original after data import.
  • Go to Tools | Options | General Ledger and change the Financial Start Period and System Conversion Date if needed.
  • Import Sales & Purchase Price History Data

    1. Before importing Sales & Purchase Price History, login into UBS and perform the following steps:-
    2. 1. Transaction | Open Item Debtor | Edit B/F or O/I | Must tick mark all to O/I | Exit
      2. Transaction | Open Item Debtor | Maintain B/F Bills | Click "List" 
         (Exit "List" and click "Generate" if there are transactions)
      3. Transaction | Open Item Debtor | Bill Payment Ledger | List Missing Records in Arpost.DBF |
      4. Press OK if there are items in the list or press Exit if non
      
    3. Repeat above step for Creditor.
    4. Please check with clients if they want to do UBS Account Year End Processing before importing Debtor or Creditor Opening Balance.
    5. Login SQL Account, make sure you have insert season code for this database, and Start GST feature in SQL Account Software
    6. Now, go to File | Data Import | UBS Stock. Under Stock Item, tick the Price History - XXX options you wish to import.
    7. UBSView10.jpg Video link may refer below : https://www.youtube.com/watch?v=s0GR8MDF5J4

    Import Opening Balance

    Year End Closing at UBS

    1. Before importing Opening Balance, login into UBS and perform the following steps:-
    • Old Version = Go to Housekeeping | Run Setup | General Setting (check when is the accounting period) then insert " Closing Period".
    • Sage UBS = Go to Housekeeping | Setup | General Setup (check when is the accounting period) then insert " Closing Period".
    Scenario 1 : In UBS this accounting year = 01/01/2015 to 31/12/2015 if you want to cut off until 31/12/2015, so closing period you have to insert 12. Scenario 2 : In UBS this accounting year = 01/01/2015 to 31/12/2015 if you want to cut off until 30/06/2015, so closing period you have to insert 6.
  • Then, go to Periodic | Year End Closing.
  • We strongly recommend that direct use the year end database to import instead of re-do the year end cut off at UBS.
    Video link may refer below : https://youtu.be/s0GR8MDF5J4?t=1m57s
    

    After Year End at UBS

  • After done the above steps, go to Tools | Options | General Ledger | to setup your Financial Start Period and System Conversion Date.
  • Next, File | Data Import | UBS Account, then select the UBS database folder. Beware of the following when selecting UBS database folder:-
    DO NOT select the folder under the UBS database folder. E.g. "DATA2013" 
    This is because after year end cut off UBS will auto generate a new folder for old financial cut off period.
    
  • After select folder, under Customer/Supplier Master Account, tick the Opening Customer/Supplier - XXX options you wish to import. UBSView9.jpg
  • You may tick all the opening at the same time. (Customer Payment takes longer time to import in).
    • Note : After done. May compare Customer Aging with UBS :
    • In UBS Step : 2. Debtors | A. Open Item Menu Debtors | 5. Print Detail Aging (SQL follow UBS Detail Aging)
    • In Sage UBS : 2. Debtors | 7. Print Debtors Aging (SQL follow UBS Detail Aging)
    Video link may refer below : https://youtu.be/s0GR8MDF5J4?t=2m54s

    Current Transaction Data Import

    Import Sales & Purchase Current Transaction

    1. Go to File | Data Import | UBS Stock and select the UBS Stock database folder.
    2. Under Stock Item, tick the Current Transaction - XXX options that you wish to import.
    3. Click Execute and wait for the import to complete.
      UBSView11.jpg

    Import AP,AR,GL Current Transaction

    1. Go to File | Data Import | UBS Account and select the UBS Account database folder.
    2. Under Customer Current Transaction, tick the [BETA] Current Customer - XXX options that you wish to import.
    3. Click Execute and wait for the import to complete.
    4. Repeat the same steps for options under Supplier Current Transaction and GL Current Transaction. UBSView12.jpg

    Troubleshooting

    1. First, it is best to tick and import one item at a time and perform backup before importing the next item.
    2. This is what you can do if the import process stopped halfway because of some error:-
    • First, you can click "Copy log to clipboard" button and paste it into a notepad or just copy the last line of the log manually
    550
    • Then, you can skip the record with error as shown below:-
    1. Highlight the item you're importing
    2. Change the record no to the next number
    3. Click Save and then click Execute again to resume

    Sales & Purchase
    550

    GL, AR & AP
    550

    IMPORTANT NOTE

    - Please double check all data imported into SQL Account from UBS.
    - We will appreciate if you would help report any issue encountered with detailed information
      so we can improve the data import tool.
    - Please be aware that Import Current Transaction is under BETA.
    

    Housekeeping

    1. After UBS database is restored, run the following Housekeeping operation:-
    • Index All Files
    • Recover Data After Power Failure
    • Delete Unwanted Transactions

    Steps

    http://www.sql.com.my/document/Import%20UBS%20Guides.pdf



    Sage UBS are the trademark of its owner. For more information on Sage product, go to www.sage.my

    FAQ

    Prompt message "Provider cannot be found. It may not be properly install" when try import

    Please install "VFPOLEDBSetup.msi" from [1]

    See also