Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics SL forum
Unanswered

Inventory (issue screen): the detail line items are missing from the screen and database

Posted on by

one of user complains about the detail items are missing from the screen either after she saved the batch or released the batch on the Issue screen.

Any idea why the data is missing ?  here are the screen prints:

select * from intran where batnbr=172627 >>>>> no record

 select * from batch where batnbr=172627 and module='IN'

  • Ginny Liao Profile Picture
    Ginny Liao on at
    RE: Inventory (issue screen): the detail line items are missing from the screen and database

    Thanks for the script.

    Here is the response from the MSFT support. Just wanted to share with you.

    " This looks like one of those anomaly cases where the batch exists in the BATCH table and never wrote anything to INTRAN. The batch is showing as Completed and Released in batch, all with 0 amounts. This batch record is finished as far as SL believes it to be. You can re-enter the record that was originally input and it will process as normal.

    Everything that I see in this batch looks like it will be fine and shouldn’t bother anything. I believe the form post was on the right track when it said that leaving the screen open for too many transactions is likely the cause. We have seen that sporadically in various screens but have never been able to consistently reproduce it in house.

    If you like, you can delete this batch from the BATCH table.

    But there is also not a problem with that batch existing, either. It’s just an empty one. If you want to clear it out of the PV List for the Issues screen you can run a delete statement to take it out of the BATCH Table. Or you can leave it where it is. Either way it’s not causing any harm. "

  • Erich Strelow F Profile Picture
    Erich Strelow F 1,642 on at
    RE: Inventory (issue screen): the detail line items are missing from the screen and database

    Here's the script. Please remember this assumes you found out the missing data from a source outside the system. Therefore, any meaningful data really is expected as a parameter on the script.

    As a disclaimer, this script was meant to solve a specific issue in my company. In fact, there's a lot of hard coded data that won't fit you.

    To run, the command is something as in 

    sqlcmd -S <server>-E -i add-intran.sql -v invtid=IEPCA004 batnbr=661570 line=19 q=1 ref=20170404 srclineref=00001 srcnbr=067092 srctype=POR task=Z9180 uom=PAR

    /**************************************************************************************************
        recupera-intran.sql
    
        Recupera detalle de INTran
    
        Parámetros:
           $invtid
           $batnbr
           $line
           $q
           $ref
           $srclineref
           $srcnbr
           $srctype
           $task
           $uom
    ***************************************************************************************************/
    
    declare @acct char(10)
    declare @desc char(30)
    
    select @desc= descr, @acct=COGSAcct from inventory where invtid='$(invtid)'
    
    INSERT INTO [dbo].[INTran]
               ([Acct]
               ,[AcctDist]
               ,[ARDocType]
               ,[ARLineID]
               ,[ARLineRef]
               ,[BatNbr]
               ,[BMICuryID]
               ,[BMIEffDate]
               ,[BMIEstimatedCost]
               ,[BMIExtCost]
               ,[BMIMultDiv]
               ,[BMIRate]
               ,[BMIRtTp]
               ,[BMITranAmt]
               ,[BMIUnitPrice]
               ,[CmmnPct]
               ,[CnvFact]
               ,[COGSAcct]
               ,[COGSSub]
               ,[CostType]
               ,[CpnyID]
               ,[Crtd_DateTime]
               ,[Crtd_Prog]
               ,[Crtd_User]
               ,[DrCr]
               ,[EstimatedCost]
               ,[Excpt]
               ,[ExtCost]
               ,[ExtRefNbr]
               ,[FiscYr]
               ,[FlatRateLineNbr]
               ,[ID]
               ,[InsuffQty]
               ,[InvtAcct]
               ,[InvtID]
               ,[InvtMult]
               ,[InvtSub]
               ,[IRProcessed]
               ,[JrnlType]
               ,[KitID]
               ,[KitStdQty]
               ,[LayerType]
               ,[LineID]
               ,[LineNbr]
               ,[LineRef]
               ,[LotSerCntr]
               ,[LUpd_DateTime]
               ,[LUpd_Prog]
               ,[LUpd_User]
               ,[NoteID]
               ,[OrigBatNbr]
               ,[OrigJrnlType]
               ,[OrigLineRef]
               ,[OrigRefNbr]
               ,[OvrhdAmt]
               ,[OvrhdFlag]
               ,[PC_Flag]
               ,[PC_ID]
               ,[PC_Status]
               ,[PerEnt]
               ,[PerPost]
               ,[PoNbr]
               ,[PostingOption]
               ,[ProjectID]
               ,[Qty]
               ,[QtyUnCosted]
               ,[RcptDate]
               ,[RcptNbr]
               ,[ReasonCd]
               ,[RefNbr]
               ,[Retired]
               ,[Rlsed]
               ,[S4Future01]
               ,[S4Future02]
               ,[S4Future03]
               ,[S4Future04]
               ,[S4Future05]
               ,[S4Future06]
               ,[S4Future07]
               ,[S4Future08]
               ,[S4Future09]
               ,[S4Future10]
               ,[S4Future11]
               ,[S4Future12]
               ,[ServiceCallID]
               ,[ShipperCpnyID]
               ,[ShipperID]
               ,[ShipperLineRef]
               ,[ShortQty]
               ,[SiteID]
               ,[SlsperID]
               ,[SpecificCostID]
               ,[SrcDate]
               ,[SrcLineRef]
               ,[SrcNbr]
               ,[SrcType]
               ,[StdTotalQty]
               ,[Sub]
               ,[SvcContractID]
               ,[SvcLineNbr]
               ,[TaskID]
               ,[ToSiteID]
               ,[ToWhseLoc]
               ,[TranAmt]
               ,[TranDate]
               ,[TranDesc]
               ,[TranType]
               ,[UnitCost]
               ,[UnitDesc]
               ,[UnitMultDiv]
               ,[UnitPrice]
               ,[User1]
               ,[User2]
               ,[User3]
               ,[User4]
               ,[User5]
               ,[User6]
               ,[User7]
               ,[User8]
               ,[UseTranCost]
               ,[WhseLoc])
         VALUES
               (@acct -- Acct
               ,0 --<AcctDist, smallint,>
               ,'' --<ARDocType, char(2),>
               ,0 --<ARLineID, int,>
               ,''--<ARLineRef, char(5),>
               ,'$(batnbr)' --<BatNbr, char(10),>
               ,'' --<BMICuryID, char(4),>
               ,0 --<BMIEffDate, smalldatetime,>
               ,0 --<BMIEstimatedCost, float,>
               ,0 --<BMIExtCost, float,>
               ,'' --<BMIMultDiv, char(1),>
               ,0 --<BMIRate, float,>
               ,'' --<BMIRtTp, char(6),>
               ,0 --<BMITranAmt, float,>
               ,0 --<BMIUnitPrice, float,>
               ,0 --<CmmnPct, float,>
               ,1 --<CnvFact, float,>
               ,@acct --<COGSAcct, char(10),>
               ,'0000000ZZZZZZZ' --<COGSSub, char(24),>
               ,'' --<CostType, char(8),>
               ,'MPCSA' --<CpnyID, char(10),>
               ,getdate() --<Crtd_DateTime, smalldatetime,>
               ,'10020' --<Crtd_Prog, char(8),>
               ,'ESTRELOW' --<Crtd_User, char(10),>
               ,'C' --<DrCr, char(1),>
               ,0 --<EstimatedCost, float,>
               ,0 --<Excpt, smallint,>
               ,0 --<ExtCost, float,>
               ,'' --<ExtRefNbr, char(15),>
               ,'2017' --<FiscYr, char(4),>
               ,0 --<FlatRateLineNbr, smallint,>
               ,'' --<ID, char(15),>
               ,0 --<InsuffQty, smallint,>
               ,'110401' --<InvtAcct, char(10),>
               ,'$(invtid)' --<InvtID, char(30),>
               ,-1 --<InvtMult, smallint,>
               ,'1CT0009ZZZZZZZ' --<InvtSub, char(24),>
               ,0 --<IRProcessed, smallint,>
               ,'IN' --<JrnlType, char(3),>
               ,'' --<KitID, char(30),>
               ,0 --<KitStdQty, float,>
               ,'S' --<LayerType, char(1),>
               ,$(line) --<LineID, int,>
               ,-32768+($(line)-1)*256 --<LineNbr, smallint,>
               ,REPLACE(STR($(line),5),' ','0') --<LineRef, char(5),>
               ,0 --<LotSerCntr, smallint,>
               ,getdate() --<LUpd_DateTime, smalldatetime,>
               ,'10020' --<LUpd_Prog, char(8),>
               ,'ESTRELOW' --<LUpd_User, char(10),>
               ,0 --<NoteID, int,>
               ,'' --<OrigBatNbr, char(10),>
               ,'' --<OrigJrnlType, char(3),>
               ,'' --<OrigLineRef, char(5),>
               ,'' --<OrigRefNbr, char(10),>
               ,0 --<OvrhdAmt, float,>
               ,0 --<OvrhdFlag, smallint,>
               ,'Y' --<PC_Flag, char(1),>
               ,'' --<PC_ID, char(20),>
               ,1 --<PC_Status, char(1),>
               ,'201704' --<PerEnt, char(6),>
               ,'201704' --<PerPost, char(6),>
               ,'' --<PoNbr, char(10),>
               ,'' --<PostingOption, smallint,>
               ,'CT0009' --<ProjectID, char(16),>
               ,$(q) --<Qty, float,>
               ,0 --<QtyUnCosted, float,>
               ,0 --<RcptDate, smalldatetime,>
               ,'' --<RcptNbr, char(15),>
               ,left('$(invtid)',3) --<ReasonCd, char(6),>
               ,'$(ref)' --<RefNbr, char(15),>
               ,0 --<Retired, smallint,>
               ,0 --<Rlsed, smallint,>
               ,'' --<S4Future01, char(30),>
               ,'' --<S4Future02, char(30),>
               ,0 --<S4Future03, float,>
               ,0 --<S4Future04, float,>
               ,0 --<S4Future05, float,>
               ,0 --<S4Future06, float,>
               ,0 --<S4Future07, smalldatetime,>
               ,0 --<S4Future08, smalldatetime,>
               ,0 --<S4Future09, int,>
               ,0 --<S4Future10, int,>
               ,'' --<S4Future11, char(10),>
               ,'' --<S4Future12, char(10),>
               ,'' --<ServiceCallID, char(10),>
               ,'' --<ShipperCpnyID, char(10),>
               ,'' --<ShipperID, char(15),>
               ,'' --<ShipperLineRef, char(5),>
               ,0 --<ShortQty, float,>
               ,'CT0009' --<SiteID, char(10),>
               ,'' --<SlsperID, char(10),>
               ,'' --<SpecificCostID, char(25),>
               ,0 --<SrcDate, smalldatetime,>
               ,'$(srclineref)' --<SrcLineRef, char(5),>
               ,'$(srcnbr)' --<SrcNbr, char(15),>
               ,'$(srctype)' --<SrcType, char(3),>
               ,0 -- <StdTotalQty, float,>
               ,'1CT0009ZZZZZZZ' --<Sub, char(24),>
               ,'' --<SvcContractID, char(10),>
               ,0 --<SvcLineNbr, smallint,>
               ,'$(task)' --<TaskID, char(32),>
               ,'' --<ToSiteID, char(10),>
               ,'' --<ToWhseLoc, char(10),>
               ,'' --<TranAmt, float,>
               ,'20170404' --<TranDate, smalldatetime,>
               ,@desc --<TranDesc, char(30),>
               ,'II' --<TranType, char(2),>
               ,0 --<UnitCost, float,>
               ,'$(uom)' --<UnitDesc, char(6),>
               ,'M' --<UnitMultDiv, char(1),>
               ,0 --<UnitPrice, float,>
               ,'' --<User1, char(30),>
               ,'' --<User2, char(30),>
               ,0 --<User3, float,>
               ,0 --<User4, float,>
               ,'' --<User5, char(10),>
               ,'' --<User6, char(10),>
               ,0 --<User7, smalldatetime,>
               ,0 --<User8, smalldatetime,>
               ,0 --<UseTranCost, smallint,>
               ,'01' --<WhseLoc, char(10),>
    	   )
    GO
    
    


  • Ginny Liao Profile Picture
    Ginny Liao on at
    RE: Inventory (issue screen): the detail line items are missing from the screen and database

    Would you please share the script with me ? Thanks

  • Erich Strelow F Profile Picture
    Erich Strelow F 1,642 on at
    RE: Inventory (issue screen): the detail line items are missing from the screen and database

    Sorry to say that this happens to us from time to time.

    The symptom is an existing IN batch record with zero debit and zero credit with the right date and userid stamps, but no intran nor any real transaction recorded whatsoever. It's like the user was shooting blanks.

    The only additional info I have is that this happens when the same instance of the 1002000 Issue screen its being used for some time ( > 3 hours). After that time, it fails to record the details sheet. In my case, it's likely that there are more than one batch affected in a single event.

    This happens about once every quarter. Haven't open a support case.

    I even have a script to recover the lines, but it depends on the user having a hard copy of the transaction. So it's equivalent to start a new transaction from scratch.

Helpful resources

Quick Links

Replay now available! Dynamics 365 Community Call (CRM Edition)

Catch up on the first D365 Community Call held on 7/10

Community Spotlight of the Month

Kudos to Saurav Dhyani!

Congratulations to the June Top 10 community leaders!

These stars go above and beyond . . .

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 287,835 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 225,526 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans