PO Receiving Hangs During Posting

 

Sometimes I feel like an idiot while standing behind such a strange issue! I been in a situation with one of my clients where they have a rare case that occur once a week or once every two weeks.

When they post the Receiving Batch, the posting screen is shown and never finalize posting, GP remain responding and the user can work, process monitor has nothing pending and everything looks like the transaction was successfully posted.

When returning back to the Receiving screen you will find that the transaction remain un-posted, posting it again will return tons of errors that duplicate document numbers are exist.

Investigating this issue further shown that transaction has the below impact:

1. GL Batch was created!

2. Payables invoice was created.

3. Receiving Posted Transactions Table Header (POP30300) is affected while Receiving Posted Transactions Line Items Table (POP30330) was not.

4. Inventory Cost Layers Table (IV10200) was partially affected.

5. Inventory Transactions History Table (IV30300) was partially affected.

Client used to perform manual operation like creating an inventory adjustment with differences to fix this miss, but this actually will cover the issue but will not correct the transaction.

Current installation with existing customizations done internally does not allow for application reinstallation specially since the client is using terminal services environment, therefore we been unable to identify the actual reason behind this issue which most probably due to a corrupted dictionaries that cause such an error.

the good thing is I gave them the script below which will remove the effect of posting the transaction from the inventory and allow to repost the transaction which will be posted correctly on the second try! For sure they will still have to manually void the AP invoice and will need to manually delete the GL Journal.

 

CREATE PROCEDURE FIXPOHANG (

@POPRCTNM VARCHAR(500)

)

AS

DECLARE @BACHNUMB VARCHAR(500)

SELECT @BACHNUMB = BACHNUMB FROM POP30300

DELETE FROM POP30300

WHERE POPRCTNM =@POPRCTNM

DELETE FROM POP30310

WHERE POPRCTNM =@POPRCTNM

DELETE FROM POP30330

WHERE POPRCTNM =@POPRCTNM

DELETE FROM POP30700

WHERE POPRCTNM =@POPRCTNM

DELETE FROM POP30390

WHERE POPRCTNM = @POPRCTNM

DELETE FROM IV30300

WHERE DOCNUMBR = @POPRCTNM

DELETE FROM IV10200

WHERE RCPTNMBR = @POPRCTNM

DELETE FROM SEE30303

WHERE DOCNUMBR = @POPRCTNM

DELETE FROM DYNAMICS..SY00800

WHERE BACHNUMB= @BACHNUMB

DELETE FROM DYNAMICS..SY00801

UPDATE SY00500 SET MKDTOPST='0', BCHSTTUS='0', USERID=''

WHERE BACHNUMB = @BACHNUMB

 

Hope that this helps people have such an issue.

Regards,
--
Mohammad R. Daoud MVP - MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85
me@mohdaoud.com
www.mohdaoud.com

Inventory Reset Utility– HITB Did not match Staging Report and actual GL Journals!

 

While doing the inventory reset for one of my clients, I shocked when finalizing the last step when my Historical Inventory Trial Balance did not match the General Ledger upon finalizing the journals posting which cannot be correct, we have initiated the reset process from the beginning to match GL to Inventory but been able to achieve this.

I did the reset for many clients so far without having any issues, however the client has huge number of daily transactions, around 2.5 records in IV30300 per year and has some issues in uncompleted transactions and posting interruption, but this does not represent the fact that the reset utility much handle such an issue.

While investigating the issue further I have found that many transactions in SEE30303 does not have corresponding GL Journals and found that those transactions are actually duplicated, I used the script below to clear all transactions from SEE30303 that does not have corresponding Journal and managed to tie both numbers:

DELETE FROM SEE30303 WHERE JRNENTRY NOT IN (SELECT JRNENTRY FROM GL20000)

It worked! Hope that will help the community.

Regards,
--
Mohammad R. Daoud MVP - MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85
me@mohdaoud.com
www.mohdaoud.com

Vendor Notifications on EFT Bank Transfer

 

I got a request from one of my clients to notify vendors by mail once they release his payment to the EFT bank, where I had to create a trigger on CM20202 to monitor payments and send the mail to the vendor, below the script I used:

Create TRIGGER [dbo].[SendVendorMails]
   ON  [dbo].[CM20202]
   FOR INSERT
AS
BEGIN
 
DECLARE @MAILPROFILE VARCHAR(8000)
DECLARE @ToMAIL  VARCHAR(8000)
DECLARE @MESSAGE     VARCHAR(8000)
DECLARE @HEADER         VARCHAR(8000)

SET @HEADER = 'Payment Transfer'
SET @MESSAGE = 'Dear Esteemed Vendor,' + char(10) + char(10)

+ 'Kindly be advised that we have processed payment with the amount of (' + CONVERT(VARCHAR(500), (SELECT [Checkbook_Amount] FROM INSERTED)) + ') to your account. ' + char(10) + char(10)
+ 'Your kind confirmation of subject payment to the following email is highly appreciated (payables@XXXX.com)' + char(10)
+ char(10)
+ char(10)
+ 'Regards,' + char(10) + char(10)
+ 'Accounts Payable Unit' + char(10)

SELECT @ToMAIL = COMMENT1 FROM PM00200 WHERE VENDORID = (SELECT [CustomerVendor_ID] FROM INSERTED)
SET @MAILPROFILE        = 'Administrator'
EXEC msdb.dbo.sp_send_dbmail
@Profile_Name = @MAILPROFILE,
@recipients = @ToMAIL,
@body = @MESSAGE,
@subject = @HEADER;

END

Note: the vendor e-mail address was saved in COMMENT1 field in the vendor card, you can modify the query to pull vendor mail from different field.

Regards,
--
Mohammad R. Daoud MVP - MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85
me@mohdaoud.com
www.mohdaoud.com

Inventory Reset - Average Item receipt QTY on hand does not equal IV QTY on hand.

 

As a part of the inventory reset utility you might get an issue with “Run Data Checks” step, that the Average Item Receipt Quantity On Hand (Table IV10200) does not equal Inventory Quantity On Hand (Table IV00102), this is really confusing specially after doing a full inventory reconcile as a part of the inventory reset process.

While investigating this I found indeed that Quantity at IV00102 does not match the IV10200 quantity and the Inventory Reconcile Process does not cover this part, where I had to find an alternative method to proceed.

On of the community users did face this issue and posted this question on one of the community portal and got an answer from WAQAS who went through the stored procedures of the reset tool and extracted a query that detects variances between IV10200 and IV00102, I have used this query to create the below cursor to collect and fix differences automatically:

DECLARE @ITEMNUMBER VARCHAR(500)

DECLARE @DEXROWID BIGINT

DECLARE QTYFIX CURSOR FOR

SELECT 

A.DEX_ROW_ID,

A.ITEMNMBR 

FROM IV10200 A 

INNER JOIN IV00101 B ON A.ITEMNMBR = B.ITEMNMBR

INNER JOIN IV00102 C ON A.ITEMNMBR = C.ITEMNMBR

WHERE B.VCTNMTHD = 3 AND A.QTYTYPE = 1 AND C.LOCNCODE = '' 

AND A.QTYONHND <> C.QTYONHND

AND EXISTS (SELECT 1 FROM IV10200 WHERE QTYTYPE = 1 AND ITEMNMBR = B.ITEMNMBR 

AND VCTNMTHD <> 0

GROUP BY ITEMNMBR HAVING MAX(DATERECD) = A.DATERECD)

AND EXISTS (SELECT 1 FROM IV10200 WHERE QTYTYPE = 1 AND ITEMNMBR =B.ITEMNMBR 

AND DATERECD = A.DATERECD AND VCTNMTHD <> 0

GROUP BY ITEMNMBR, DATERECD HAVING MAX(RCTSEQNM) = A.RCTSEQNM)

AND EXISTS (SELECT 1 FROM IV10200

WHERE QTYTYPE = 1 AND ITEMNMBR = B.ITEMNMBR AND DATERECD = A.DATERECD 

AND RCTSEQNM = A.RCTSEQNM 

AND VCTNMTHD <> 0

GROUP BY ITEMNMBR, DATERECD, RCTSEQNM HAVING MAX(DEX_ROW_ID) = A.DEX_ROW_ID)

OPEN QTYFIX

FETCH NEXT FROM QTYFIX INTO @DEXROWID, @ITEMNUMBER

WHILE @@FETCH_STATUS = 0 

BEGIN

UPDATE IV10200 SET QTYONHND=(SELECT QTYONHND FROM IV00102 WHERE ITEMNMBR=@ITEMNUMBER AND RCRDTYPE='1') WHERE DEX_ROW_ID= @DEXROWID

FETCH NEXT FROM QTYFIX INTO @DEXROWID, @ITEMNUMBER

END

CLOSE QTYFIX

DEALLOCATE QTYFIX

Enjoy!

Regards,
--
Mohammad R. Daoud MVP - MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85
me@mohdaoud.com
www.mohdaoud.com

Recovered!

 

Last couple of months, I been very very very much engaged, mainly with the below:

1. Did huge enhancements to my house, it took me around 21 days to finalize.

2. Setup new offices for my Dynamics Innovations www.di.jo, it took years!

3. Upgraded 5 clients to Dynamics GP 2010 and enhanced their environment by implementing Portals, Workflows and Reporting.

4. Went a trip around the Middle East for some clients (Bahrain, Lebanon, Saudi Arabia Jeddah and Makah and finally Syria)

5. Did a big task in reconciling one of my clients inventories to General Ledger along with identifying reasons behind having such a difference which was the nightmare.

6. Answered around 9 RFP’s.

7. Did more than 20 presentations for Microsoft Dynamics GP in my home country, 3 in Lebanon and 1 presentation in Bahrain.

I feel guilty not being an active blogger for the last couple of months, but will make sure to make it up for blog follower in the upcoming days!

Regards,
--
Mohammad R. Daoud MVP - MCT
MCP, MCBMSP, MCTS, MCBMSS
+962 - 79 - 999 65 85
me@mohdaoud.com
www.mohdaoud.com