Open Forum

Like what you see? Discover the benefits of the GPUG Community. Learn More

Problem posting check batch

  • 1.  Problem posting check batch

    TOP CONTRIBUTOR
    Posted 11 days ago
    Hi everyone-

    Our AP person is having a problem posting an EFT check batch in Payables. When she posts from the Print Remittance screen, the progress box comes up and everything just stops. No errors appear on the screen.

    Logging out and back in doesn't fix it using Batch Recovery either.

    I ran an edit list on the batch and there are no errors and also verified all the other common things -- fiscal period is open, etc. Checked for duplicate transactions between the work, open and history tables -- no duplicates.

    Ran a DEXSQL log and this error comes up (I've copy/pasted the parts around it too):

    /* Date: 11/30/2018 Time: 12:11:22
    stmt(10343904):*/
    BEGIN INSERT INTO OEA.dbo.PM30300 (VENDORID, DOCDATE, DATE1, GLPOSTDT, TIME1, VCHRNMBR, DOCTYPE, APFRDCNM, ApplyFromGLPostDate, FROMCURR, APFRMAPLYAMT, APFRMDISCTAKEN, APFRMDISCAVAIL, APFRMWROFAMT, ActualApplyToAmount, ActualDiscTakenAmount, ActualDiscAvailTaken, ActualWriteOffAmount, APFRMEXRATE, APFRMDENRATE, APFRMRTCLCMETH, APFRMMCTRXSTT, APTVCHNM, APTODCTY, APTODCNM, APTODCDT, ApplyToGLPostDate, CURNCYID, CURRNIDX, APPLDAMT, DISTKNAM, DISAVTKN, WROFAMNT, ORAPPAMT, ORDISTKN, ORDATKN, ORWROFAM, APTOEXRATE, APTODENRATE, APTORTCLCMETH, APTOMCTRXSTT, PPSAMDED, GSTDSAMT, TAXDTLID, POSTED, TEN99AMNT, RLGANLOS, APYFRMRNDAMT, APYTORNDAMT, APYTORNDDISC, OAPYFRMRNDAMT, OAPYTORNDAMT, OAPYTORNDDISC, Settled_Gain_CreditCurrT, Settled_Loss_CreditCurrT, Settled_Gain_DebitCurrTr, Settled_Loss_DebitCurrTr, Settled_Gain_DebitDiscAv, Settled_Loss_DebitDiscAv, Revaluation_Status, Credit1099Amount, DEFTEN99TYPE, DEFTEN99BOXNUMBER) VALUES ( '52MARKS001', '2018.12.07', '2018.11.30', '2018.12.07', '00:00:00', '0056800', 6, 'EFT0000000002285', '2018.12.07', '', 6383.00000, 0.00000, 0.00000, 0.00000, 6383.00000, 0.00000, 0.00000, 0.00000, 0.0000000, 0.0000000, 0, 0, '44754', 1, '11-18 LEASE & EXP''S', '2018.11.13', '2018.11.13', '', 0, 6383.00000, 0.00000, 0.00000, 0.00000, 6383.00000, 0.00000, 0.00000, 0.00000, 0.0000000, 0.0000000, 0, 0, 0.00000, 0.00000, '', 1, 0.00000, 0.00000, 0.00000, 0.00000, 0.00000, 0.00000, 0.00000, 0.00000, 0.00000, 0.00000, 0.00000, 0.00000, 0.00000, 0.00000, 0, 0.00000, 1, 0) ; SELECT @@IDENTITY ; END

    /* Date: 11/30/2018 Time: 12:11:22
    stmt(10343904):*/
    BEGIN INSERT INTO OEA.dbo.PM30300 (VENDORID, DOCDATE, DATE1, GLPOSTDT, TIME1, VCHRNMBR, DOCTYPE, APFRDCNM, ApplyFromGLPostDate, FROMCURR, APFRMAPLYAMT, APFRMDISCTAKEN, APFRMDISCAVAIL, APFRMWROFAMT, ActualApplyToAmount, ActualDiscTakenAmount, ActualDiscAvailTaken, ActualWriteOffAmount, APFRMEXRATE, APFRMDENRATE, APFRMRTCLCMETH, APFRMMCTRXSTT, APTVCHNM, APTODCTY, APTODCNM, APTODCDT, ApplyToGLPostDate, CURNCYID, CURRNIDX, APPLDAMT, DISTKNAM, DISAVTKN, WROFAMNT, ORAPPAMT, ORDISTKN, ORDATKN, ORWROFAM, APTOEXRATE, APTODENRATE, APTORTCLCMETH, APTOMCTRXSTT, PPSAMDED, GSTDSAMT, TAXDTLID, POSTED, TEN99AMNT, RLGANLOS, APYFRMRNDAMT, APYTORNDAMT, APYTORNDDISC, OAPYFRMRNDAMT, OAPYTORNDAMT, OAPYTORNDDISC, Settled_Gain_CreditCurrT, Settled_Loss_CreditCurrT, Settled_Gain_DebitCurrTr, Settled_Loss_DebitCurrTr, Settled_Gain_DebitDiscAv, Settled_Loss_DebitDiscAv, Revaluation_Status, Credit1099Amount, DEFTEN99TYPE, DEFTEN99BOXNUMBER) VALUES ( '52MARKS001', '2018.12.07', '2018.11.30', '2018.12.07', '00:00:00', '0056800', 6, 'EFT0000000002285', '2018.12.07', '', 6383.00000, 0.00000, 0.00000, 0.00000, 6383.00000, 0.00000, 0.00000, 0.00000, 0.0000000, 0.0000000, 0, 0, '44754', 1, '11-18 LEASE & EXP''S', '2018.11.13', '2018.11.13', '', 0, 6383.00000, 0.00000, 0.00000, 0.00000, 6383.00000, 0.00000, 0.00000, 0.00000, 0.0000000, 0.0000000, 0, 0, 0.00000, 0.00000, '', 1, 0.00000, 0.00000, 0.00000, 0.00000, 0.00000, 0.00000, 0.00000, 0.00000, 0.00000, 0.00000, 0.00000, 0.00000, 0.00000, 0.00000, 0, 0.00000, 1, 0) ; SELECT @@IDENTITY ; END
    /*
    /* Date: 11/30/2018 Time: 12:11:22
    SQLSTATE:(23000) Native Err:(2627) stmt(10343904):*/
    [Microsoft][SQL Server Native Client 11.0][SQL Server]Violation of PRIMARY KEY constraint 'PKPM30300'. Cannot insert duplicate key in object 'dbo.PM30300'. The duplicate key value is (44754 , 1, 0056800 , 6).*/
    /*
    /* Date: 11/30/2018 Time: 12:11:22
    SQLSTATE:(01000) Native Err:(3621) stmt(10343904):*/
    [Microsoft][SQL Server Native Client 11.0][SQL Server]The statement has been terminated.*/
    /*
    /* Date: 11/30/2018 Time: 12:11:22
    SQLSTATE:(00000) Native Err:(3621) stmt(10343904):*/
    */
    /*
    /* Date: 11/30/2018 Time: 12:11:22
    SQLSTATE:(00000) Native Err:(3621) status(54):*/

    Is GP freezing during posting and this error showing up because GP is trying to run the insert statement into PM30300 twice?

    We are running GP 2015 R2.

    Thank you.
    -Joe

    ------------------------------
    Joseph Markovich
    IT Manager
    Okaya Electric America
    Valparaiso IN
    ------------------------------


  • 2.  RE: Problem posting check batch

    TOP CONTRIBUTOR
    Posted 11 days ago
    Hi @Joseph Markovich

    Looks like the issue may not be a duplicate in Work/Open or History, but there is an issue with the PM Apply to table - as in it sees some kind of duplication - probably because you guys have attempted this a few times.

    Try looking for any of those vouchers or doc numbers in the PM30300 table and look for the payments there - have you checked to see if the payments appear in any inquiry screens - GP may think they are paid.​

    ------------------------------
    Kindest Regards,
    Jo deRuiter , MCP, DCP
    "That GP Red Head"
    AISLING DYNAMICS CONSULTING, LLC
    WEBSITE: https://aislingdynamics.com/
    BLOG: https://community.dynamics.com/gp/b/gplife
    GPUG Academy Instructor
    770-906-4504 (Cell)
    ------------------------------



  • 3.  RE: Problem posting check batch

    TOP CONTRIBUTOR
    Posted 11 days ago
    Hi Jo-

    Since I posted my message, I went into the Purchasing Batches and deleted it in GP. Started over with another one of the vendors and their invoice to pay. It still locks up and gives the error in the DEXSQL log.

    Looked in PM30300 and nothing in there showing for both the invoice and the payment.

    In Inquiry for the same vendor, it shows the open posted invoice and the payment in work. ​

    In my test company, I am running checklinks on the whole Purchasing series to see if anything comes up.

    Can you think of anything else that is causing this?

    Thank you.
    Joe

    ------------------------------
    Joseph Markovich
    IT Manager
    Okaya Electric America
    Valparaiso IN
    ------------------------------



  • 4.  RE: Problem posting check batch

    TOP CONTRIBUTOR
    Posted 11 days ago
    So, after running checklinks, nothing was found.

    I created the check batch in my testing company after running checklinks and attempted to post. It posted just fine, so this leads me to believe it is a SQL Server issue. Am going to restart the server this weekend and try again.

    -Joe

    ------------------------------
    Joseph Markovich
    IT Manager
    Okaya Electric America
    Valparaiso IN
    ------------------------------



  • 5.  RE: Problem posting check batch

    GPUG ALL STAR
    Posted 8 days ago
    Hi @Joseph Markovich
    This is quite an annoying one and I couldn't find much solutions about it, as every case is slightly different..
    FWIW, have you tried David's SQL scripts about locating duplicate entries ?
    https://blogs.msdn.microsoft.com/developingfordynamicsgp/2008/12/04/identifying-duplicate-transactions/

    I found a similar report from the Dynamics Community, but it was never answered :-(
    https://community.dynamics.com/gp/f/32/t/150419

    let us know your findings..
    ​​

    ------------------------------
    Beat Bucher
    Business Analyst, Dynamics GP SME
    Montreal QC/Canada
    @GP_Beat http://www.gp-geek.com
    Montreal QC GPUG Chapter Leader
    MBS MVP (2015-2018)
    ------------------------------



  • 6.  RE: Problem posting check batch

    TOP CONTRIBUTOR
    Posted 8 days ago
    Edited by Jo deRuiter 8 days ago
    Good Morning @Joseph Markovich

    I see I'm late to the game and you may have resolved the issue enough to post.

    I wanted to post this response in the event others have similar issues and find this later through a web-search.

    I've also seen these type errors occur if your "Next Numbers" set up in Payables, etc. get out of sync.  For instance in Payables Setup under "Options" your next numbers may no longer be the next MAX number in the PM00400 or any of the sub-tables for Payables.

    One fix is to just find the next MAX number in the SQL tables and then manually plug in the next number in the voucher area in options or you can just pick a new next number that's a couple hundred advanced from the next number in the payables options.

    For instance in my screenshot my Next Payment Number in 00000000000000439, I would manually advance that to 00000000000000639​ or something.


    For your particular issue you may also need to check the "Next EFT Numbers" for Payables in the Checkbook setup.

    Have a great day!!

    ------------------------------
    Kindest Regards,
    Jo deRuiter , MCP, DCP
    "That GP Red Head"
    AISLING DYNAMICS CONSULTING, LLC
    WEBSITE: https://aislingdynamics.com/
    BLOG: https://community.dynamics.com/gp/b/gplife
    GPUG Academy Instructor
    770-906-4504 (Cell)

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



  • 7.  RE: Problem posting check batch

    GPUG ALL STAR
    Posted 8 days ago
    Edited by Steve Erbach 8 days ago
    @Jo deRuiter,

    ​>> For instance in my screenshot my Next Payment Number in 00000000000000439, I would manually advance that to 00000000000000639​ or something. <<

    GP's numbering schemes make me chuckle. That 17-digit payment number, for instance, if you make, say, 50 payments per business day, you could make payments for 7.8 trillion years (with 255 business days/year). Even if you made a billion times that many payments per day, that number would still last you over 7800 years.

    Like, we'll all be hooked into a global inter-mind network by then, eh? You'll just have to think about buying something, et voilà! Ze merchandise, she is purchased! And it's delivered to your orbital habitat by transporter!

    Regards,

    ------------------------------
    "Sparkly" Steve Erbach - QMS Manager and Business Development Analyst
    GLC Minerals, LLC - Green Bay, WI
    Co-Chair, GPUG WI (Green Bay) Chapter
    GP 2016 R2 (16.00.0579) / MR 2012 CU16
    Blog: https://www.gpug.com/blogs/steve-erbach
    Twitter: twitter.com/serbach
    GoldAllStar85pxhigh.png 2018-09-30_13_42_40-Deer_in_the_Headlights_-_Opera.jpg TopContrib85.jpg


    ------------------------------
    Welcome to Dynamics!
    You do not have permission.
    Call Steve.
    (hat tip: Lou Spevack)
    ------------------------------



  • 8.  RE: Problem posting check batch

    TOP CONTRIBUTOR
    Posted 8 days ago
    Hi @Jo deRuiter-

    It was the SQL Server -- for whatever reason. A restart and everything is back to normal again! Isn't that the fix for everything? A restart?

    Nice tip on the duplicate numbers too! I checked it out for grins and the numbers were all in sync.

    @Beat Bucher I found David's scripts for the duplicate numbers and used them too -- there wasn't any duplicates. Kind of a relief...a SQL Server restart is a whole lot easier.

    Thanks everyone!
    -Joe
    ​​

    ------------------------------
    Joseph Markovich
    IT Manager
    Okaya Electric America
    Valparaiso IN
    ------------------------------



  • 9.  RE: Problem posting check batch

    TOP CONTRIBUTOR
    Posted 7 days ago
    Edited by Jo deRuiter 7 days ago
    Good Morning @Joseph Markovich

    Just reinforces the notion that many things work if you just turn them off and then on again...LOL

    I'm glad it was such a simple resolution!!

    ------------------------------
    Kindest Regards,
    Jo deRuiter , MCP, DCP
    "That GP Red Head"
    AISLING DYNAMICS CONSULTING, LLC
    WEBSITE: https://aislingdynamics.com/
    BLOG: https://community.dynamics.com/gp/b/gplife
    GPUG Academy Instructor
    Dynamics GP Credentialing Council-Vice Chair
    770-906-4504 (Cell)

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