Dynamics GP - Stock Count Distributions

The distribution accounts for stock count come directly from the IV00101 table.

IVIVINDX - Inventory Account
IVVARIDX - Variance Account


Example of how to find the correct account and update all item distributions with the correct account index

select * from iv00101

select * from gl00105 where ACTNUMST = '1-00-00-1-00-6170'

select * from iv00101 where IVVARIDX > 0

update IV00101 set IVVARIDX = 10

Dynamics GP - SQL to identify old items to be discontinued

--Use this to view the items that will be discontinued
select ITEMNMBR, ITEMDESC, itemtype from iv00101 where ITEMTYPE = 1
and ITEMNMBR in (select distinct itemnmbr from iv00102 where QTYONHND = 0 and QTYONORD = 0 and ATYALLOC = 0 and LOCNCODE = '')
and ITEMNMBR not in (select distinct itemnmbr from iv10201 where getdate()-DOCDATE <= 365)

--Use this to actually update the items and flag them as "Discontinued"
update iv00101 set itemtype = 2 where ITEMTYPE = 1
and ITEMNMBR in (select distinct itemnmbr from iv00102 where QTYONHND = 0 and QTYONORD = 0 and ATYALLOC = 0 and LOCNCODE = '')
and ITEMNMBR not in (select distinct itemnmbr from iv10201 where getdate()-DOCDATE <= 365)

--View to show all items with no activity in last 365 days
----------------------------------------------------------------
SELECT     dbo.IV00101.ITEMNMBR, dbo.IV00101.ITEMDESC, dbo.IV00101.ITEMTYPE, dbo.IV00101.ITMCLSCD, dbo.IV40400.ITMCLSDC
FROM         dbo.IV00101 INNER JOIN
                      dbo.IV40400 ON dbo.IV00101.ITMCLSCD = dbo.IV40400.ITMCLSCD
WHERE     (dbo.IV00101.ITEMTYPE = 1) AND (dbo.IV00101.ITEMNMBR NOT IN
                          (SELECT DISTINCT ITEMNMBR
                            FROM          dbo.IV10201 AS IV10201_1
                            WHERE      (GETDATE() - DOCDATE <= 365)))

Dynamics GP - How to reset all Average costed inventory costs. Zero out and reset stock using stock count entry.

Adjust out all stock
Adjust Stock accounts to zero or transfer balances to suspense accounts
Set the qtyonhnd for the last record for each item in the IV10200 table to zero to reset the cost calculations for the next transaction entered.

-------------------------------------------------

SELECT     IV10200.ITEMNMBR, IV10200.RCTSEQNM AS LRSNM, IV10200.DATERECD, IV10200.QTYONHND, IV10200.DEX_ROW_ID AS Lastentry
FROM         IV10200 INNER JOIN
                          (SELECT     IV10200_2.ITEMNMBR, MAX(IV10200_2.RCTSEQNM) AS LRSNM
                            FROM          IV10200 AS IV10200_2 INNER JOIN
                                                       (SELECT     ITEMNMBR, MAX(DATERECD) AS LDR
                                                         FROM          IV10200 AS IV10200_1
                                                         GROUP BY ITEMNMBR) AS LDR ON IV10200_2.ITEMNMBR = LDR.ITEMNMBR AND IV10200_2.DATERECD = LDR.LDR
                            GROUP BY IV10200_2.ITEMNMBR) AS LRSNM ON IV10200.ITEMNMBR = LRSNM.ITEMNMBR AND IV10200.RCTSEQNM = LRSNM.LRSNM
------------------------------------------------
update iv10200 set qtyonhnd = 0 where dex_row_id in(
select lastentry from(
SELECT     IV10200.ITEMNMBR, IV10200.RCTSEQNM AS LRSNM, IV10200.DATERECD, IV10200.QTYONHND, IV10200.DEX_ROW_ID AS Lastentry
FROM         IV10200 INNER JOIN
                          (SELECT     IV10200_2.ITEMNMBR, MAX(IV10200_2.RCTSEQNM) AS LRSNM
                            FROM          IV10200 AS IV10200_2 INNER JOIN
                                                       (SELECT     ITEMNMBR, MAX(DATERECD) AS LDR
                                                         FROM          IV10200 AS IV10200_1
                                                         GROUP BY ITEMNMBR) AS LDR ON IV10200_2.ITEMNMBR = LDR.ITEMNMBR AND IV10200_2.DATERECD = LDR.LDR
                            GROUP BY IV10200_2.ITEMNMBR) AS LRSNM ON IV10200.ITEMNMBR = LRSNM.ITEMNMBR AND IV10200.RCTSEQNM = LRSNM.LRSNM) as LastCost)
-------------------------------------------
--To see the last record that needs to be reset for each item
-------------------------------------------
select * from IV10200 where ITEMNMBR = 'myitemnum' order by daterecd,rctseqnm

------------------------------------------------

Adjust In all stock

Check the costs, check the gl balance

You can use the Stock count entry window to quickly gather all items, quantities and lot numbers to create the adjustment to remove all stock

Use the IV10300, IV10301, IV10302, IV10303 tables to gather the information you need to create a positive adjustment to put back in all stock at the correct cost


Create a stock count schedule for all items
Create a stock count entry

Backup the stock count entry tables
select * into iv10301_all from iv10301
select * into iv10302_all from iv10302

Update the stock count entry

Update iv10301 set VERIFIED = 1,VARIANCEQTY = capturedqty*-1, STCKSRLLTVRNC = CAPTUREDQTY * -1
Update iv10302 set VERIFIED = 1,VARIANCEQTY = capturedqty*-1

----
Use this to grab non-lot stock count data if required
select * from iv10301 where STCKCNTID = 'SB' and CAPTUREDQTY <> 0
----

Open stock count entry
Process stock count entry
Confirm variance accounts
Print GL TB
Post adjustment
Print GL TB
Run script to wipe out last stock cost

update iv10200 set qtyonhnd = 0 where dex_row_id in(

select lastentry from(

SELECT     ITEMNMBR, MAX(DEX_ROW_ID) AS Lastentry

FROM         IV10200

GROUP BY ITEMNMBR) as LastCost)

Start the stock count again
Run this to populate with the original amounts from the backup
Change the costs if necessary


--Update all count records
UPDATE    IV10301
SET              VERIFIED = 1, varianceQTY = iv10301_all.CAPTUREDQTY, COUNTEDQTY = iv10301_all.CAPTUREDQTY, STCKSRLLTVRNC = IV10301_all.CAPTUREDQTY, Stock_Serial_Lot_Count  = IV10301_all.CAPTUREDQTY
FROM         IV10301 INNER JOIN
                      iv10301_all ON IV10301.STCKCNTID = iv10301_all.STCKCNTID AND IV10301.ITEMNMBR = iv10301_all.ITEMNMBR AND IV10301.LOCNCODE = iv10301_all.LOCNCODE

--Create all lot entries from backups
insert into iv10302(STCKCNTID, ITEMNMBR, LOCNCODE,ITMTRKOP,SERLTNUM,SERIALSTATUS,QTYTYPE)
select stckcntid,ITEMNMBR, LOCNCODE,3,'20140201',1,1 from iv10301_all where varianceQTY <> 0                  


 --Update lot entries
 UPDATE    IV10302
SET              VERIFIED = 1, VARIANCEQTY = IV10301_all.CAPTUREDQTY, COUNTEDQTY = IV10301_all.CAPTUREDQTY, SERLTNUM = '20140201'
FROM         IV10302 INNER JOIN
                      iv10301_all ON IV10302.ITEMNMBR = iv10301_all.ITEMNMBR AND IV10302.LOCNCODE = iv10301_all.LOCNCODE AND IV10302.STCKCNTID = iv10301_all.STCKCNTID
               
           

Check the inventory adjustment, adjust costs as necessary

Dynamics GP - Table with ALL inventory transactions across all modules

select * from iv10201

Dynamics GP - Remove Empty Item/Site assignments

select * from iv00102 where QTYONHND = 0 and ATYALLOC = 0 and qtysold=0 and lsordqty= 0 and QTYONORD = 0 and LOCNCODE <> ''

delete  from iv00102 where QTYONHND = 0 and ATYALLOC = 0 and qtysold=0  and lsordqty= 0 and QTYONORD = 0 and LOCNCODE <> ''

Dynamics GP - MRP Runs for days, freezes or stops responding

http://support.microsoft.com/kb/942508

Go Manufacturing>Transactions>MRP>Security
Select the task and delete it to stop the MRP from running

Run this to set the Low Level Codes to 100
----------------------------------------------------------------
execute mbomLLCUtility 110

Dynamics GP - Work Center Calendar Error

"The global calendar for your organization has not been created, and the default work center calendar cannot be used in its place."


http://support.microsoft.com/kb/851590


Run This
---------------------------------------------------------------
INSERT INTO DD020000 

VALUES (0,0) 

/*0=NONE, 1=SUNDAYS, 2=SATURDAY AND SUNDAYS*/ 

eOne Extender - New solutions do not import correctly. Old solutions do not delete properly.

I had experienced problems loading new solutions even after deleting old solutions.
I resolved the issue by running the grant.sql on all databases, and then manually going into the extender tables and deleting any data i found in them pertaining to the old solutions.

Once the tables were cleared out, the new solution imported correctly.

truncate table ext01200
truncate table ext01201
truncate table ext01203
truncate table ext01211
truncate table ext01212
truncate table ext01213
truncate table ext20010
truncate table EXT00900
truncate table EXT10200
truncate table EXT20010
truncate table EXT20021
truncate table EXT20110
truncate table EXT20200
truncate table EXT20400
truncate table EXT20401
truncate table EXT20402
truncate table EXT20403
truncate table EXT20500
truncate table EXT20700
truncate table EXT21000
truncate table EXT21010

eOne Extender SQL Tables

Each of the tables in this series holds data for a different data type

EXT01200
EXT01201
EXT01203
EXT01211
EXT01212
EXT01213

EXT00900 - Lookup table values
EXT10200 - Lookup Tables
EXT20010 - Window Field Layout
EXT20021 - Item List Values
EXT20110 - Windows
EXT20120 - "Additional" Menu Entries - Check here if you get duplicated menus
EXT20200 - Resource list
EXT20400 - Event List - update the companyid here if restored
EXT20401,2,3 - Event Actions
EXT20500 - Views
EXT20700 - Imports
EXT21000 - Solutions
EXT21010 - Extender Solution Details


When restoring from one company to another, run these scripts to update any company references. Replace "TEST" with your new database id.

 update [EXT20401] set interid = 'TEST'

Dynamics GP - How to do a Stock Count

1. Understanding Inventory Setup options
1. Inventory>Setup>Inventory Control
2. Sales>Setup>Sales Order Processing
2. Understanding how other transactions affect the Inventory Quantities
1. What affects Allocated Quantities?

1. Sales Order Quantities Allocated by Line Item or Batch
2. Unposted Purchase Return Quantities
3. Unposted Sales Invoice Quantities Allocated by Line Item or Batch
4. Unposted Inventory Transfer Quantities
5. Unposted In-Transit Transfer Quantities
6. Unposted Inventory Transaction Quantities
7. Unposted Inventory Assemblies
8. Unposted Manufacturing Issues
9. Processed Stock Counts WITHOUT Autopost ticked (Will generate unposted Inventory Transactions)
1. What Affects On Hand Quantities?
1. Posted Purchase Receipts
2. Posted Purchase Returns
3. Posted Sales Invoice Quantities Allocated by Line Item or Batch
4. Posted Sales Returns
5. Posted Inventory Transfer Quantities
6. Posted/Shipped In-Transit Transfer Quantities
7. Posted Inventory Transaction Quantities
8. Posted Inventory Assemblies
9. Posted Manufacturing Issues
10. Processed Stock Counts WITH Autopost ticked (Will generate and post Inventory Transactions)
2. What does not Affect Inventory Allocated, or On Hand Quantities?
1. Sales Quotes
2. Non-allocated Sales Orders
3. Non-allocated Sales Invoices
4. Unposted Purchase Receipts
5. Unposted Sales Returns
6. Stock Count Schedules
7. Unprocessed Stock Count Entries
1. Useful Inventory Inquiry Screens
1. Item - Shows quantities in locations
2. Item Transaction - Shows all item transaction history
3. Serial/Lot Trace - Shows movement of a serial/Lot through all transactions
4. Inventory All-in-One View - Shows all inventory transactions and their movement
5. Bin Quantities - Shows item quantities in bins
6. Item Allocation - Shows all item allocations
7. Item Stock - Shows all item transactions and a running stock total
8. Price Trace - Test extended pricing
2. Pre-Requisites
1. All transactions affecting inventory on hand should be entered into the system
2. Post as many Receivings Batches as possible
3. Post as many Inventory Batches as possible (Adjustments and Transfers)
4. Post as many Sales Batches as possible (Invoices and Returns)
5. Post as many Assembly Batches as possible
6. Post as many Manufacturing Issues as possible
7. Post as many Manufacturing Receipts as possible
8. Run a full check links
9. Run a reconcile against sales and inventory
3. Actual Stock Count
1. Create a Stock Count Schedule for a Site and collection of Items
1. Inventory>Transaction>Stock Count Schedule
2. Tick Reuse Stock Count if you want to always use this list as a template
3. Click Start Count when you're ready to snapshot your stock and begin counting
4. Print your stock count list 
2. Ideally, you should refrain from doing any transactions that will affect inventory until the stock count is completed. Why?
1. Stock that should not be allocated may become allocated, and may prevent the stock count adjustments from going through. Eg. Stock incorrectly shows 100, physical is 0. When they count, -100 adjustment must go through, but users may have allocated the incorrect 100, resulting in orders for stock that does not exist, and pushing available stock into negative when the stock count is posted. This is further compounded if serials and lots are in use.
3. Count your Stock
4. Enter your stock count
1. Select stock Count ID
2. For each line, you can review the stock data that was captured by pressing the expansion button on the right of the column labels
1. Captured Qty = On Hand Qty when "Start count" was clicked
2. On Hand Qty = Current On Hand Qty (Informational Only)
3. Allocated Qty = Current Allocated Qty (Informational Only)
3. Enter your count for each item
1. Variance is calculated as Captured Qty - Counted Qty
2. When your variance is posted, it will affect the Current On Hand Qty
4. Tick Verify
5. Enter Lot or Serial Numbers
6. If you tick "Autopost stock count variances", it will automatically post the adjustment when you click "process"
7. Process your stock count
4.  If you are finished with all transactions for the previous periods, you should close the fiscal periods accordingly.
1.  Tools>Setup>Company>Fiscal Periods
2. Select the year
3. Tick any periods you want to prevent posting to.

Dynamics GP - Change the PO Ship to Address. Modify PO to show company address by default as ship to.

The PO Ship to Address is taken from the Company Addresses that have been setup.
If you add additional company addresses, you can select them in the company ship to selection on the PO.

Delete the existing ship-to fields. Modify the PO form to use these fields as the ship-to fields.
These fields correspond to the ship to address id blue arrow in the Vendor Blue Arrow in the PO header. If a user changes the ship to address id in here, it updates the values in the ship to address id blue arrow.

• Purchase Order Work
• Company Name
• Address1
• Address2
• Address3

Dynamics GP - Error message when you try to write off documents by using the write-off process in Microsoft Dynamics GP: "No documents were found matching the criteria selected"

Check the customer cards>options, the customer is probably set to not allow writeoffs.

Use the customer class to update the status to unlimited, or set a limit to the allowed writeoffs

SQL Server - Connect to SQL server via internet connection

Determine what port your SQL server is listening on by running

USE master
GO
xp_readerrorlog 0, 1, N'Server is listening on'
GO

1433 is the default
Port forward this through your router/firewall

Connect to it by using a connection string in your odbc or code

Provider=sqloledb;Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;Initial Catalog=pubs;User ID=myUsername;Password=myPassword;

Dynamics GP - This Cost of goods sold distribution amount is incorrect and will not be posted

This problem occurs when the EXTDCST, OREXTCST column in the SOP10100 table for this transaction does not match the actual originating and functional total for the line items for this transaction.

Update this field to the value shown in the distribution window to resolve the issue.

SELECT     SUM(EXTDCOST) AS LineExtdCost, SUM(OREXTCST) AS LineOrigCost
FROM         SOP10200
WHERE     (SOPNUMBE = 'RTN000530')

select EXTDCOST, orextcst from sop10100 where SOPNUMBE='RTN000530'

update sop10100 set EXTDCOST = 33522.45
--, orextcst = 5157.30
where SOPNUMBE='RTN000530'

Dynamics GP - The sales distribution amount is incorrect

Problem:
You get this error even if you default the distributions correctly.

Solution:
this is happening because the MRKDNAMT and ORMRKDAM fields do not match the total markdown for all the line items.
This generally occurs if the transaction has been imported, and the MRKDNAMT field has been set.

To resolve this issue, default the distributions, and note the total markdown amount.
Run this script and set the fields to the correct markdown amount

update SOP10100 set MRKDNAMT = 1.25,ORMRKDAM = 1.25  where SOPNUMBE = 'inv00012'

Dynamics GP - Remove CEIP and Reminders - Slow login or startup due to reminder list

http://support.microsoft.com/kb/916997

SQL - Attempt to fetch logical page in database 7 failed. It belongs to allocation unit

SQL 2008 R2 - Failed to flush the commit table to disk

Other errors
Failed to flush the commit table to disk in dbid 10 due to error 2601
Failed to flush the commit table to disk in dbid 8 due to error 8630

Solution 1 (does absolutely nothing):
This issue is resolved in the latest service pack

http://support.microsoft.com/kb/2527041

If the service pack does not resolve the issue, you must repair your SQL installation
Launch SQL Install>Maintenance>Repair


Solution 2 (this one worked for me):
Run a

DBCC CHECKDB (two) WITH NO_INFOMSGS, ALL_ERRORMSGS

then most likely a

ALTER DATABASE two SET SINGLE_USER
GO
DBCC CHECKDB (two,repair_rebuild)
GO
ALTER two SET MULTI_USER
GO

Solution 3 (others have reported this, i tried it, it did not work):
You can also disable change tracking on all objects and the database, and delete the uncommitted transactions

--Find all objects with change tracking
select (select OBJECT_NAME(object_id)) from  sys.change_tracking_tables

--Disable change tracking on each object

alter table GL00102 disable change_tracking

alter table MC00200 disable change_tracking

alter table GL10001 disable change_tracking

alter table GL10100 disable change_tracking

alter table GL10101 disable change_tracking

alter table MC40000 disable change_tracking

alter table SY00300 disable change_tracking

alter table GL40000 disable change_tracking

alter table GL12000 disable change_tracking

alter table GL00201 disable change_tracking

alter table GL12001 disable change_tracking

alter table GL10000 disable change_tracking

alter table GL32000 disable change_tracking

alter table GL40200 disable change_tracking

alter table GL20000 disable change_tracking

alter table GL00200 disable change_tracking

alter table GL00100 disable change_tracking

alter table GL30000 disable change_tracking

alter table SY40100 disable change_tracking

alter table SY40101 disable change_tracking

--Disable Change tracking on database

ALTER DATABASE AdventureWorks2012
SET CHANGE_TRACKING = OFF

--Delete the sys.syscommittab table

Click New Query
Click Change connection to bring up the login screen for the query
enter ADMIN: before your sql server name to use the DAC connection
Select your company
--backup the syscommittab table
select * into dbo.syscommittab_bak from sys.syscommittab
delete sys.syscommittab

--Enable change tracking on each object

alter table GL00102 enable change_tracking
alter table MC00200 enable change_tracking
alter table GL10001 enable change_tracking
alter table GL10100 enable change_tracking
alter table GL10101 enable change_tracking
alter table MC40000 enable change_tracking
alter table SY00300 enable change_tracking
alter table GL40000 enable change_tracking
alter table GL12000 enable change_tracking
alter table GL00201 enable change_tracking
alter table GL12001 enable change_tracking
alter table GL10000 enable change_tracking
alter table GL32000 enable change_tracking
alter table GL40200 enable change_tracking
alter table GL20000 enable change_tracking
alter table GL00200 enable change_tracking
alter table GL00100 enable change_tracking
alter table GL30000 enable change_tracking
alter table SY40100 enable change_tracking
alter table SY40101 enable change_tracking








--Enable Change tracking on database

ALTER DATABASE AdventureWorks2012
SET CHANGE_TRACKING = ON

Dynamics GP - Reconcile to GL - Inventory - Violation of Primary Key contstraint... Cannot insert duplicate key in object

Related Issues

• The excel sheet will still be generated after clearing the error
• The reconciled excel sheet shows no matched transactions
• All transactions appear as unmatched

--The problem occurs when this script is trying to run, but returns duplicates

BEGIN DECLARE @stored_proc_name char(28) DECLARE @retstat int set nocount on SELECT @stored_proc_name = 'ATLT.dbo.glGetIVTransactions'
EXEC @retstat = @stored_proc_name '2013.11.01', '2013.11.30', '##1765136' SELECT @retstat set nocount on END


--The constraint in question is this one
ALTER TABLE ##1765136 ADD CONSTRAINT PK##1765136 PRIMARY KEY NONCLUSTERED (TRXSORCE, DOCNUMBR, DOCTYPE, JRNENTRY, ACTINDX, ITEMNMBR)


--Send the output to a clean temp table

select * into temp_GLIVREC from dbo.##1765136
BEGIN DECLARE @stored_proc_name char(28) DECLARE @retstat int set nocount on SELECT @stored_proc_name = 'ATLT.dbo.glGetIVTransactions'
EXEC @retstat = @stored_proc_name '2013.11.01', '2013.11.30', 'temp_GLIVREC' SELECT @retstat set nocount on END


--Review the output by running this query to identify any duplicates

SELECT     TRXSORCE, DOCNUMBR, DOCTYPE, JRNENTRY, ACTINDX, ITEMNMBR
FROM         temp_GLIVREC
GROUP BY TRXSORCE, DOCNUMBR, DOCTYPE, JRNENTRY, ACTINDX, ITEMNMBR
HAVING      (COUNT(TRXSORCE) > 1)

--I found that i was getting duplicates due to transctions in the SEE30303 table having blank TRXSORCE values. To resolve the issue, i populated the trxsorce with the dexrowid because i was unsure if the records should be deleted or not. I kept a backup of the SEE30303 table in case we needed to reset the data

select * into see30303_bak from SEE30303
update see30303 set TRXSORCE = DEX_ROW_ID where TRXSORCE = ''

Dynamics GP - SOP Posting - Unable to obtain Receivables Document Number

Solution here

http://support.microsoft.com/kb/856940

Run these scripts

--Confirm document started posting
SELECT * from RM00401 where DOCNUMBR = '509924'
--Confirm status is not 0
SELECT PSTGSTUS,* from SOP10100 where PSTGSTUS <> 0
--Set the PSTGSTUS back to 0, then post
UPDATE SOP10100 SET PSTGSTUS = 0 where SOPNUMBE in ('509924','510048')


PSTNGSTUS Values
http://dynamicsgpblogster.blogspot.com/2009/05/sales-order-processing-posting-statuses.html

0 - Unposted
2 - Posted
12 - Error during posting
14 - Error during posting

Dynamics GP - SOP Edit List Errors

All of the errors are stored in the SOP10200 table.
the error code is in the SOPLNERR field.


http://support.microsoft.com/kb/943954