Open Forum

Journal Transaction > PA type > Missing Batch Nbr > can't select in Initialize Mode

  • 1.  Journal Transaction > PA type > Missing Batch Nbr > can't select in Initialize Mode

    BRONZE CONTRIBUTOR
    Posted May 15, 2019 07:35 PM
    Hi Folks,

    We have an issue where a Percent Complete entry from the project module (PA) posted to project, but the corresponding GL batch has hit with a set up issue I haven't encountered before. The Status came over as H (on hold), but in Initialize mode where we would normally correct these types of issues, if I try and select the batch, it won't allow it. When I query the batch, i see it hung up as H and with a batch number assigned, but cannot select:


    I have seen a hot fix applied to CU2 SL15 (which we have installed) so this is known to some extent.

    Question - What I'm wondering, has anyone encountered this scenario and if so, is there an approach I can use to adjust this entry in the short-term while we figure out how to prevent it in the future?

    Many thanks,
    Justin

    ------------------------------
    Justin Fishwick
    Manager of Business Analysis
    The Brattle Group
    ------------------------------


  • 2.  RE: Journal Transaction > PA type > Missing Batch Nbr > can't select in Initialize Mode

    BRONZE CONTRIBUTOR
    Posted May 16, 2019 01:55 AM
    Hi Justin,

    Looks like the batch has a blank batch.batnbr, which is why you can't select it.

    Assuming all other Rev Rec batches are posted in GL Post Transactions screen, if you query GLtran for unposted Rev Recog batches, can you find anything?

    select * from gltran where module='PA' and trantype='RE' and posted='U'

    If so, could they be for this batch?  If they are, then update the batch record for that blank batch with the batch number found in gltran.  After that you should be able to select it.

    Update batch set batnbr='xxxxx' where module='PA' and batnbr='' and status='H'

    If there is no unposted RevRecog gltran, then you can either delete that blank batch record or set it to void (status='V', rlsed=1).

    Hope this helps.

    ------------------------------
    John Lian
    Principal Consultant
    The ERP Group, LLC
    portland
    ------------------------------



  • 3.  RE: Journal Transaction > PA type > Missing Batch Nbr > can't select in Initialize Mode

    TOP CONTRIBUTOR
    Posted May 16, 2019 06:32 AM
    FWIW:    We found this situation so annoying that I wrote a SQL Job that checks nightly for this error condition.

    select gltran.CpnyID, gltran.OrigCpnyID, gltran.batnbr, batch.rlsed, batch.status,
    batch.module, gltran.module as GLTran_Module, gltran.posted, gltran.rlsed as GLTran_Rlsed,gltran.Crtd_DateTime,gltran.crtd_user,
    gltran.acct, gltran.sub, gltran.ProjectID, gltran.TaskID, gltran.dramt - cramt as NetAmt, gltran.perpost
    from  gltran
    left outer join  batch on gltran.CpnyID = batch.CpnyID and gltran.module = batch.module and batch.batnbr = gltran.batnbr
    where ( batch.status is null)
        
    If it finds it, it sends an alert that the batch needed repair and inserts the missing record.   We have never found the issue in any module except PA, which is why it isn't a selection criteria.

    We are wondering whether having a batch record with no batch number is a proximate cause of the issue, but in any case, it is a known bug in V2015.

    Here's hoping it's fixed in V2018 because we're working on that upgrade!

    Best regards,

    ------------------------------
    Gail Jones-Nemeth
    Financial Systems Analyst
    Creative Associates Int'l
    Washington DC
    ------------------------------



  • 4.  RE: Journal Transaction > PA type > Missing Batch Nbr > can't select in Initialize Mode

    BRONZE CONTRIBUTOR
    Posted May 17, 2019 05:16 PM
    Thanks to you both, John & Gail! The combo of both really helped me figure it out. There was indeed a corresponding doc in BATCH table with missing info.

    Steps to resolve:
    1. Get missing batch number for BATCH table from gltran
      1. select * from batch where module='PA' and batnbr='' and status='H'

      2. verify this corresponds with expected missing batch by module...
    2. Update BATCH.batnbr record as listed in gltran
    3. Activate Initialize Mode > reopen Journal Transactions > can now select batch
    4. Release from Hold to post
    5. (Ours was missing the PerEnt in BATCH, needed to update that as well)
    6. Post transactions
    Gail - I may indeed borrow your approach on setting a system alert to notify if/when this happens again!

    Best,
    Justin

    ------------------------------
    Justin Fishwick
    Manager of Business Analysis
    The Brattle Group
    ------------------------------