Difference between revisions of "GL Entries for SME Inventory"
(→Setup) |
|||
(10 intermediate revisions by 2 users not shown) | |||
Line 1: | Line 1: | ||
Inventory items can be maintained solely in SME and this setting is only for items maintained in SME, not for items in both SME and QuickBooks. This approach uses journal entries to keep QuickBooks Accounting up to date financially while SME tracks inventory counts. | Inventory items can be maintained solely in SME and this setting is only for items maintained in SME, not for items in both SME and QuickBooks. This approach uses journal entries to keep QuickBooks Accounting up to date financially while SME tracks inventory counts. | ||
− | <br>To | + | <br>To configure SME to correctly record the inventory accounting transactions in QuickBooks you must set the correct options in SQLink. Set the '''Use General Ledger entries for Default Material Item''' as shown in the image below. With this setting, SQLink will create General Ledger Account entries in QuickBooks when items are received on SME Purchase Orders (Debit Inventory Asset / Credit Accounts Payable) and when you invoice material in SME (Credit inventory Asset / Debit Cost of Goods Sold). These settings require that the default material item must be a Non-Inventory item. You can use the default established when SME/SQLink is installed, or you can set up an item of your own choosing, but it must be a Non-Inventory item. |
<br>Using this option applies only if you keep your inventory items in SME only, not in both SME and QuickBooks. | <br>Using this option applies only if you keep your inventory items in SME only, not in both SME and QuickBooks. | ||
Line 58: | Line 58: | ||
<br>[[File:IncludeSMPOnPO_GLSettings.png]] | <br>[[File:IncludeSMPOnPO_GLSettings.png]] | ||
− | <h3>Set if you want the COGS entry to be made on the invoice date or the date the material was added to the invoice | + | <h3>Set if you want the COGS entry to be made on the invoice date or the date the material was added to the invoice</h3> |
+ | Go to Setup > Company > Inventory and choose the option for Cost of Goods Sold GL Entry date | ||
<br>[[File:COGSDate_GLENtriesSetting.png]] | <br>[[File:COGSDate_GLENtriesSetting.png]] | ||
+ | |||
+ | <br>If Base COGS on Date of Invoice is unchecked, then the date on the COGS entry in QuickBooks will be the date the material was added to the order or itemized invoice. (image below) | ||
+ | <br>[[File:BaseCOGSOnDateOfInvoice_UNCHECKED.png]] | ||
+ | |||
+ | <br>If Base COGS on Date of Invoice is checked, then the date on the COGS entry in QuickBooks will be the invoice date, not the date the material was added to the service order or itemized invoice/ | ||
+ | <br>In the image below, the material was added to the order on 5/29 and the order was invoiced on 6/5. The COGS entry is using the Invoice Date. | ||
+ | <br>[[File:BaseCogsOnDateOfInvoice_CHECKED.png]] | ||
== Purchase Order for Inventory and Non-Inventory Items == | == Purchase Order for Inventory and Non-Inventory Items == | ||
Line 78: | Line 86: | ||
<br>A Credit was made to the Inventory Asset account and a Debit made to the Cost of Goods Sold account as shown in the report below. | <br>A Credit was made to the Inventory Asset account and a Debit made to the Cost of Goods Sold account as shown in the report below. | ||
<br>[[File:InvoiceGL.png]] | <br>[[File:InvoiceGL.png]] | ||
+ | |||
+ | == Voiding or Reexporting an Invoice in SME == | ||
+ | The COGS and Inventory Asset entries go over to QuickBooks when the invoice is synced from SME to QuickBooks. | ||
+ | <br>For SQLink versions of 8.0.3.7 or higher: | ||
+ | <br>- If you Void the invoice in SME and then do a full sync, the COGS and Inventory Asset entries will be cleared in QuickBooks. | ||
+ | <br>- When you re invoice the order in SME, the COGS and Inventory Asset entries will be entered in QuickBooks. | ||
+ | |||
+ | <br>- If you re-export an invoice from SME to QB, the original GL Entries will be cleared and recreated. | ||
+ | |||
+ | |||
== To Convert from syncing items to journal/GL entry == | == To Convert from syncing items to journal/GL entry == | ||
If you are an existing SME customer syncing SME items with QB items, you can run the following script to "disconnect" SME items from QB item. | If you are an existing SME customer syncing SME items with QB items, you can run the following script to "disconnect" SME items from QB item. | ||
Note: USE WITH CAUTION, Run backups prior, should be ONLY done by qualified database administrators or SME support. | Note: USE WITH CAUTION, Run backups prior, should be ONLY done by qualified database administrators or SME support. | ||
+ | |||
+ | |||
+ | |||
Run these queries: | Run these queries: | ||
− | |||
− | Select UID, Name, InventoryID From Inventory Where Name Like 'SMP_Item%' | + | Select UID, Name, InventoryID, IsActive From Inventory Where Name Like 'SMP_Item%' and IsActive = 1; |
− | - | + | -- Put the QB Item InventoryID in the Where and run the query. |
− | + | ||
− | Disable Trigger All on Inventory; | + | Disable Trigger All on Inventory; |
− | Update Inventory Set | + | Update Inventory |
− | Where Name Not Like 'SMP_Item%'; | + | Set QBExport = 'false', |
− | Enable Trigger All on Inventory; | + | QBInventory = 'false', |
+ | QBModified = NULL, | ||
+ | QBItem = 'false', | ||
+ | InventoryID = '' | ||
+ | Where | ||
+ | Name Not Like 'SMP_Item%'; --- or whatever your default item is named Enable Trigger All on Inventory; |
Latest revision as of 21:58, 10 August 2017
Inventory items can be maintained solely in SME and this setting is only for items maintained in SME, not for items in both SME and QuickBooks. This approach uses journal entries to keep QuickBooks Accounting up to date financially while SME tracks inventory counts.
To configure SME to correctly record the inventory accounting transactions in QuickBooks you must set the correct options in SQLink. Set the Use General Ledger entries for Default Material Item as shown in the image below. With this setting, SQLink will create General Ledger Account entries in QuickBooks when items are received on SME Purchase Orders (Debit Inventory Asset / Credit Accounts Payable) and when you invoice material in SME (Credit inventory Asset / Debit Cost of Goods Sold). These settings require that the default material item must be a Non-Inventory item. You can use the default established when SME/SQLink is installed, or you can set up an item of your own choosing, but it must be a Non-Inventory item.
Using this option applies only if you keep your inventory items in SME only, not in both SME and QuickBooks.
Setup
Setup your default Inventory Asset and COGS Account
1. Open SQLink
2. Click File > Setup
3. Click the Classes and Accounts Options tab
4. Click Edit at the bottom
5. Set your Default Inventory Asset Account and Default COGS Account
Setup your Default Material Line Item
1. In SQLink, click the Invoice/ Proposal Options tab
2. Locate the Default Material Line Item field. The item set in that field by default is a non-inventory item called “SMP_Item”. You can leave that item or set another non-inventory item you already have setup, however the item must be a Non-Inventory item.
Set Income Account on your Default Material Line Item
If you keep SMP_Item as your Default Material Line Item you will need to set the income account on SMP_Item in QuickBooks.
Note: Do NOT check "This item is used in assemblies or is purchased for a specific customer/job"
1. Open QuickBooks
2. Click Lists > Item List
3. Look for SMP_Item and open the item.
4. Set the Income account in the Account field (image below).
5. Make sure you keep the item as a non-inventory part. Do not change this.
Setup your Purchase Order Options in SME and SQLink
1. In SME, go to the Setup module > Company > Order
2. Click Edit
3. Check Default Export Bills on POs (Image below)
4. Click Save.
SQLink PO Bill Settings:
1. In SQLink, click File > Setup
2. Click Edit at the bottom
3. In the Purchase Order Options section, check Export Bills Only (image below)
4. Click Save
To see how Bills for POs export to QuickBooks, view the video at this link:
http://high5software.com/mediawiki/index.php?title=Default_Export_to_QuickBooks_Settings#Export_Options_for_Purchase_Orders
Set Purchase Orders Settings in SQLink
1. In SQLink, click File > Setup
2. Click Edit at the bottom
3. Check Include SMP Items on Purchase Orders (image below)
4. Click Save
Set if you want the COGS entry to be made on the invoice date or the date the material was added to the invoice
Go to Setup > Company > Inventory and choose the option for Cost of Goods Sold GL Entry date
If Base COGS on Date of Invoice is unchecked, then the date on the COGS entry in QuickBooks will be the date the material was added to the order or itemized invoice. (image below)
If Base COGS on Date of Invoice is checked, then the date on the COGS entry in QuickBooks will be the invoice date, not the date the material was added to the service order or itemized invoice/
In the image below, the material was added to the order on 5/29 and the order was invoiced on 6/5. The COGS entry is using the Invoice Date.
Purchase Order for Inventory and Non-Inventory Items
If you create a PO in SME, the PO will be in SME only.
Receive an item on the PO and on your next sync, a bill will go to QB for the item you received. The image below shows the bill in QuickBooks for the SME Purchase Order #4128.
The following entries are made in QB from the Bill:
A Credit was made to Accounts Payable account and a Debit made to the Inventory Asset account as shown in the report below.
Invoice for the Inventory Item - GL Entries
Invoice the material in SME. On your next sync, the invoice will sync to QB (image below)
The following GL entries are made in QuickBooks:
A Credit was made to the Inventory Asset account and a Debit made to the Cost of Goods Sold account as shown in the report below.
Voiding or Reexporting an Invoice in SME
The COGS and Inventory Asset entries go over to QuickBooks when the invoice is synced from SME to QuickBooks.
For SQLink versions of 8.0.3.7 or higher:
- If you Void the invoice in SME and then do a full sync, the COGS and Inventory Asset entries will be cleared in QuickBooks.
- When you re invoice the order in SME, the COGS and Inventory Asset entries will be entered in QuickBooks.
- If you re-export an invoice from SME to QB, the original GL Entries will be cleared and recreated.
To Convert from syncing items to journal/GL entry
If you are an existing SME customer syncing SME items with QB items, you can run the following script to "disconnect" SME items from QB item. Note: USE WITH CAUTION, Run backups prior, should be ONLY done by qualified database administrators or SME support.
Run these queries:
Select UID, Name, InventoryID, IsActive From Inventory Where Name Like 'SMP_Item%' and IsActive = 1;
-- Put the QB Item InventoryID in the Where and run the query.
Disable Trigger All on Inventory; Update Inventory Set QBExport = 'false', QBInventory = 'false', QBModified = NULL, QBItem = 'false', InventoryID = Where Name Not Like 'SMP_Item%'; --- or whatever your default item is named Enable Trigger All on Inventory;