SOP transaction in History, but missing distributions and not in GL

(0) Share

Report

Posted on by Community Member

The user clicked post on the batch, the batch screen went away, but no screen print out displayed. When the user went to verify the batch posted to the GL it was missing.

If I go to Inquiry >> Sales >> Sales Documents and search using history and the invoice number in the batch, the invoice shows up. Looking at the SOP invoice details, the invoice looks correct except there is NO distribution information.

I verified that the GL2000 table does not have the invoice listed, Verified that the sales history header and line items does have the invoice, and the distribution table (work history) does not have the data.

Wondering what I should do. I am thinking use SQL and remove the SOP history records, but wish there was a GUI way to do this, or move the SOP invoice back to work tables and allow gui to try again.

Not sure on the cause, but suspect the PC had issues and interrupted the batch posting.

SQL

--Work -Showed no results
SELECT * FROM NET.dbo.SOP10100 WHERE SOPNUMBE = '00402825'
SELECT * FROM NET.dbo.SOP10200 WHERE SOPNUMBE = '00402825'

--History - Showd header and
SELECT * FROM NET.dbo.SOP30200 WHERE SOPNUMBE = '00402825'
SELECT * FROM NET.dbo.SOP30300 WHERE SOPNUMBE = '00402825'

--Distribution work and history - Shows no information on invoice
SELECT * FROM NET.dbo.SOP10102 WHERE SOPNUMBE = '00402825'

*This post is locked for comments

I have the same question (0)

All responses (17)

Answers (0)

gpyoungguns 115 on at

Like (0)

Report

Jay,

Is is only this one SOP doc? If so, you could always void out the document and then re-enter it. Make sure to check your posting settings to make sure GP doesn't try to create a JE. Go under Tools-->Setup-->Posting-->Posting. Select Sales for the Series and Sales Voided Transactions as the Origin, and then un-check the Post to GL checkbox.

If that doesn't work, you could try to reconcile the sales document (Tools --> Utilities --> Sales --> Reconcile - Remove Sales Documents.

If none of those work, I can send you a script I have used in the past to move the document back into the work tables.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

If I move the Invoice from History back to Work I was going to use this below SQL

DECLARE @SOPID as varchar(55)

SET @SOPID = '00402825'

DEClARE @SOPIDTemp as varchar(55)

SET @SOPIDTemp = @SOPID + '!'--Temporary ! sysmbol so there is no primary key conflict

INSERT INTO NET.dbo.SOP10100

SELECT SOPTYPE, @SOPIDTemp, ORIGTYPE, ORIGNUMB, DOCID, DOCDATE, GLPOSTDT, QUOTEDAT, QUOEXPDA, ORDRDATE, INVODATE, BACKDATE, RETUDATE, ReqShipDate, FUFILDAT, ACTLSHIP, DISCDATE, DUEDATE, REPTING, TRXFREQU, TIMEREPD, TIMETREP, DYSTINCR, DTLSTREP, DSTBTCH1, DSTBTCH2, USDOCID1, USDOCID2, DISCFRGT, ORDAVFRT, DISCMISC, ORDAVMSC, DISAVAMT, ORDAVAMT, DISCRTND, ORDISRTD, DISTKNAM, ORDISTKN, DSCPCTAM, DSCDLRAM, ORDDLRAT, DISAVTKN, ORDATKN, PYMTRMID, PRCLEVEL, LOCNCODE, BCHSOURC, BACHNUMB, CUSTNMBR, CUSTNAME, CSTPONBR, PROSPECT, MSTRNUMB, PCKSLPNO, PICTICNU, MRKDNAMT, ORMRKDAM, PRBTADCD, PRSTADCD, CNTCPRSN, ShipToName, ADDRESS1, ADDRESS2, ADDRESS3, CITY, STATE, ZIPCODE, CCode, COUNTRY, PHNUMBR1, PHNUMBR2, PHONE3, FAXNUMBR, COMAPPTO, COMMAMNT, OCOMMAMT, CMMSLAMT, ORCOSAMT, NCOMAMNT, ORNCMAMT, SHIPMTHD, TRDISAMT, ORTDISAM, TRDISPCT, SUBTOTAL, ORSUBTOT, REMSUBTO, OREMSUBT, EXTDCOST, OREXTCST, FRTAMNT, ORFRTAMT, MISCAMNT, ORMISCAMT, TXENGCLD, TAXEXMT1, TAXEXMT2, TXRGNNUM, TAXSCHID, TXSCHSRC, BSIVCTTL, FRTSCHID, FRTTXAMT, ORFRTTAX, FRGTTXBL, MSCSCHID, MSCTXAMT, ORMSCTAX, MISCTXBL, BKTFRTAM, ORBKTFRT, BKTMSCAM, ORBKTMSC, BCKTXAMT, OBTAXAMT, TXBTXAMT, OTAXTAMT, TAXAMNT, ORTAXAMT, ECTRX, DOCAMNT, ORDOCAMT, PYMTRCVD, ORPMTRVD, DEPRECVD, ORDEPRVD, CODAMNT, ORCODAMT, ACCTAMNT, ORACTAMT, SALSTERR, SLPRSNID, UPSZONE, TIMESPRT, PSTGSTUS, VOIDSTTS, ALLOCABY, NOTEINDX, CURNCYID, CURRNIDX, RATETPID, EXGTBLID, XCHGRATE, DENXRATE, EXCHDATE, TIME1, RTCLCMTD, MCTRXSTT, TRXSORCE, SOPHDRE1, SOPHDRE2, SOPLNERR, SOPHDRFL, COMMNTID, REFRENCE, POSTEDDT, PTDUSRID, USER2ENT, CREATDDT, MODIFDT, Tax_Date, APLYWITH, WITHHAMT, SHPPGDOC, CORRCTN, SIMPLIFD, DOCNCORR, SEQNCORR, SALEDATE, EXCEPTIONALDEMAND, Flags, SOPSTATUS, SHIPCOMPLETE, DIRECTDEBIT

FROM NET.dbo.SOP30200 WHERE SOPNUMBE = @SOPID

--Add History Line Item back in work

INSERT INTO NET.dbo.SOP10200

SELECT SOPTYPE, @SOPIDTemp, LNITMSEQ, CMPNTSEQ, ITEMNMBR, ITEMDESC, NONINVEN, DROPSHIP, UOFM, LOCNCODE, UNITCOST, ORUNTCST, UNITPRCE, ORUNTPRC, XTNDPRCE, OXTNDPRC, REMPRICE, OREPRICE, EXTDCOST, OREXTCST, MRKDNAMT, ORMRKDAM, MRKDNPCT, MRKDNTYP, INVINDX, CSLSINDX, SLSINDX, MKDNINDX, RTNSINDX, INUSINDX, INSRINDX, DMGDINDX, ITMTSHID, IVITMTXB, BKTSLSAM, ORBKTSLS, TAXAMNT, ORTAXAMT, TXBTXAMT, OTAXTAMT, BSIVCTTL, TRDISAMT, ORTDISAM, DISCSALE, ORDAVSLS, QUANTITY, ATYALLOC, QTYINSVC, QTYINUSE, QTYDMGED, QTYRTRND, QTYONHND, QTYCANCE, QTYCANOT, QTYORDER, QTYPRBAC, QTYPRBOO, QTYPRINV, QTYPRORD, QTYPRVRECVD, QTYRECVD, QTYREMAI, QTYREMBO, QTYTBAOR, QTYTOINV, QTYTORDR, QTYFULFI, QTYSLCTD, QTYBSUOM, EXTQTYAL, EXTQTYSEL, ReqShipDate, FUFILDAT, ACTLSHIP, SHIPMTHD, SALSTERR, SLPRSNID, PRCLEVEL, COMMNTID, BRKFLD1, BRKFLD2, BRKFLD3, CURRNIDX, TRXSORCE, SOPLNERR, DOCNCORR, ORGSEQNM, ITEMCODE, PURCHSTAT, DECPLQTY, DECPLCUR, ODECPLCU, EXCEPTIONALDEMAND, TAXSCHID, TXSCHSRC, PRSTADCD, ShipToName, CNTCPRSN, ADDRESS1, ADDRESS2, ADDRESS3, CITY, STATE, ZIPCODE, CCode, COUNTRY, PHONE1, PHONE2, PHONE3, FAXNUMBR, Flags, CONTNBR, CONTLNSEQNBR, CONTSTARTDTE, CONTENDDTE, CONTITEMNBR, CONTSERIALNBR, ISLINEINTRA

FROM NET.dbo.SOP30300 WHERE SOPNUMBE = @SOPID

--Delete History Header

DELETE FROM NET.dbo.SOP30200 WHERE DEX_ROW_ID=204447

--Delete History Line Items

DELETE FROM NET.dbo.SOP30300 WHERE DEX_ROW_ID=313214

DELETE FROM NET.dbo.SOP30300 WHERE DEX_ROW_ID=313215

DELETE FROM NET.dbo.SOP30300 WHERE DEX_ROW_ID=313216

--Remove Temporary ! sysmbol from Invoice ID

Update NET.dbo.SOP10100 SET SOPNUMBE=REPLACE(SOPNUMBE,'!','') WHERE SOPNUMBE = @SOPIDTemp

Update NET.dbo.SOP10200 SET SOPNUMBE=REPLACE(SOPNUMBE,'!','') WHERE SOPNUMBE = @SOPIDTemp

Was this reply helpful? Yes No
gpyoungguns 115 on at

Like (0)

Report

That looks pretty good to me. I would definitely run it on a test database first just to make sure it posts properly. This approach is definitely not recommended, but I have run into situations where I had no other choice. Also, check is the RM tables to make sure that document isn't there Depending on how far along it got in the posting process, it might show up. I would check both the RM00401 and RM20101 to see if it's in there.

Was this reply helpful? Yes No
Philippe Simoes 380 on at

Like (0)

Report

Hi,

Here is a great article about how to resolve a SOP Posting Interruption :

support.microsoft.com/.../850029

It contains a step by step procedure that is very easy to follow. I hope this helps.

Regards,

Philippe

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thanks for the feedback. It is the last thing I want to do, but I can't think of anything else to do.

I check both RM tables and did find a record in the RM00401. Guessing I should delete the RM data?

--Receivables Management

SELECT * FROM NET.dbo.RM00401 WHERE DOCNUMBR = '00402825'

DOCNUMBR RMDTYPAL DCSTATUS BCHSOURC TRXSORCE CUSTNMBR CHEKNMBR DOCDATE NEGQTYSOPINV DEX_ROW_ID

00402825 1 0 Sales Entry SLSTE00010471 xxxxx xxxxx 2013-04-29 00:00:00.000 0 345653

Was this reply helpful? Yes No
gpyoungguns 115 on at

Like (0)

Report

Yes, you will need to get it out of the RM tables. Once an SOP document posts in the SOP module, it gets written to the RM module. You would get a few duplicate errors if you tried to post it again. In addition to the RM00401 and RM20101 table, I would check the RM10101 and RM30301 tables. They are the GL distribution work and history tables. It would be interesting to see if they made it to those tables.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

No data made it to the RM10101 and RM30301

SELECT * FROM RM10101 WHERE DOCNUMBR = '00402825'

SELECT * FROM RM30301 WHERE DOCNUMBR = '00402825'

I moved the records from History back to work. The move went fine. I just had to do a couple other updates so the work records were not seen as posted.

Update NET.dbo.SOP10100 SET PSTGSTUS=0,glpostdt='1900-01-01 00:00:00.000',taxschid='AVATAX ',timesprt=0,trxsorce='',refrence='',ptdusrid='',flags=2

WHERE SOPNUMBE = @SOPIDTemp

Was this reply helpful? Yes No
gpyoungguns 115 on at

Like (0)

Report

Good catch! This is definitely a good script to keep handy!

Was this reply helpful? Yes No
L Vail 65,271 on at

Like (0)

Report

Hi,

I'm probably going to sound dumb here, but if your SOP records are OK (except for the missing distribution), and your A/R Trial Balance is OK, why not just make the journal entry to record it in GL?

Kind regards,

Leslie

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

We thought about that, but we also use WennSoft for job costing, which links up with SOP making some reports we use not tie out, so it was important that the distributions were there.

Was this reply helpful? Yes No