66
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'
*This post is locked for comments
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. "
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
Share
Report
All responses (
Answers (
/**************************************************************************************************
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
Would you please share the script with me ? Thanks
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.
#1
Community Member
136
#2
Mohamed Amine Mahmoudi
102
Super User 2025 Season 1
#3
REUser
8
