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

This question has suggested answer(s)

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'

All Replies
  • 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.

  • 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

  • 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.

  • 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

  • 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

  • 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.

  • 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

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

  • 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

    Leslie Vail, CPA, MVP, MCT, MCITP, MCP, MCITS
    ASCI, Inc.    *    PO Box 600965   *   Dallas, TX 75360    *   972-814-8550   *   leslievail@earthlink.net

  • 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.

  • Hi Jay,

    I would be very tempted to just change it with SQL and move on. I'm not usually a supporter of this sort of thing, but sometimes it's just not worth the effort. Maybe it's just too late for me and I'm tired. :)

    Leslie

    Leslie Vail, CPA, MVP, MCT, MCITP, MCP, MCITS
    ASCI, Inc.    *    PO Box 600965   *   Dallas, TX 75360    *   972-814-8550   *   leslievail@earthlink.net

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

    /**

    References

    victoriayudin.com/.../sop-tables

    community.dynamics.com/.../105575.aspx

    Script Purpose

    If something  happens during a posting of a batch, an invoices distributions can disapear. NOT sure how or why.

    Make sure to check the distributions table to see if the SQL relationship is broken, or the distribution has truly disapeared (Dexterity black box artifact)

    **/

    --Set the Invoice to Move

    DECLARE @SOPID as varchar(55)

    SET @SOPID = '00418351'

    --Set the Temp SOP Number

    DECLARE @SOPIDTemp as varchar(55)

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

    Print @SOPIDTemp

    ----Receivables Management, check to see if there are receivables, you will have to delete these if you are moving invoice to work from history

    SELECT * FROM NET.dbo.RM00401 WHERE DOCNUMBR =  @SOPID

    --Whats in the Distributions table for this invoice, if nothing than run script, and repair open invoice re adding distributions. Then re post

    SELECT * FROM NET.dbo.SOP10102 WHERE SOPNUMBE=@SOPID

    --Get the Dex Row ID for the Header

    DECLARE @DEX_ROW_ID_Header as int

    SELECT @DEX_ROW_ID_Header= DEX_ROW_ID FROM NET.dbo.SOP30200 WHERE SOPNUMBE = @SOPID--Header Detail

    SELECT DEX_ROW_ID FROM NET.dbo.SOP30300 WHERE SOPNUMBE = @SOPID--Line Detail

    --Clone history Invoice to work table

    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

    --Clone history lines to work table

    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=@DEX_ROW_ID_Header

    --Delete History Line Items

    DELETE FROM NET.dbo.SOP30300 WHERE  SOPNUMBE = @SOPID

    --Remove Temporary ! sysmbol from Invoice ID in work tables

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

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

    --Remove Receivables Management Data

    DELETE FROM NET.dbo.RM00401 WHERE DOCNUMBR =  @SOPID

    --NOTE This does nothing for distributions. Script written for when a work invoice has been posted partially

    --Distributions where never moded.

    --SOP10102 – GL Distributions Work and History

  • I updated this script one more time. Added some transaction and error checks. Plus I forgot a couple update fields which make the SOP invoice not show as already posted even though in the work tables.

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

    /**

    References

    victoriayudin.com/.../sop-tables

    community.dynamics.com/.../105575.aspx

    Script Purpose

    If something  happens during a posting of a batch, an invoices distributions can disapear. NOT sure how or why.

    Make sure to check the distributions table to see if the SQL relationship is broken, or the distribution has truly disapeared (Dexterity black box artifact)

    **/

    --Set the Invoice to Move

    BEGIN TRANSACTION;

    DECLARE @SOPID as varchar(55)

    SET @SOPID = '00418351'

    --Set the Temp SOP Number

    DECLARE @SOPIDTemp as varchar(55)

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

    Print @SOPIDTemp

    ----Receivables Management, check to see if there are receivables, you will have to delete these if you are moving invoice to work from history

    SELECT * FROM NET.dbo.RM00401 WHERE DOCNUMBR =  @SOPID

    --Whats in the Distributions table for this invoice, if nothing than run script, and repair open invoice re adding distributions. Then re post

    SELECT * FROM NET.dbo.SOP10102 WHERE SOPNUMBE=@SOPID

    --Get the Dex Row ID for the Header

    DECLARE @DEX_ROW_ID_Header as int

    SELECT @DEX_ROW_ID_Header= DEX_ROW_ID FROM NET.dbo.SOP30200 WHERE SOPNUMBE = @SOPID--Header Detail

    SELECT DEX_ROW_ID FROM NET.dbo.SOP30300 WHERE SOPNUMBE = @SOPID--Line Detail

    BEGIN TRY

    --Clone history Invoice to work table

    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, 0 as SOPMCERR, COMMNTID, REFRENCE, POSTEDDT, PTDUSRID, USER2ENT, CREATDDT, MODIFDT, Tax_Date, APLYWITH, WITHHAMT, SHPPGDOC, CORRCTN, SIMPLIFD, '' as CORRNXST, DOCNCORR, SEQNCORR, SALEDATE, 0 as SOPHDRE3, EXCEPTIONALDEMAND, Flags, 0 as BackoutTradeDisc, 0 as OrigBackoutTradeDisc, '' as GPSFOINTEGRATIONID, 0 as INTEGRATIONSOURCE, '' as INTEGRATIONID, SOPSTATUS, SHIPCOMPLETE, DIRECTDEBIT

    FROM NET.dbo.SOP30200 WHERE SOPNUMBE = @SOPID

    --Clone history lines to work table

    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, 0 as QTYONPO, 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, ORGSEQNM, ITEMCODE, PURCHSTAT, DECPLQTY, DECPLCUR, ODECPLCU, 0 as QTYTOSHP, 0 as XFRSHDOC, EXCEPTIONALDEMAND, TAXSCHID, TXSCHSRC, PRSTADCD, ShipToName, CNTCPRSN, ADDRESS1, ADDRESS2, ADDRESS3, CITY, STATE, ZIPCODE, CCode, COUNTRY, PHONE1, PHONE2, PHONE3, FAXNUMBR, Flags, 0 as BackoutTradeDisc, 0 as OrigBackoutTradeDisc, '' as GPSFOINTEGRATIONID, 0 as INTEGRATIONSOURCE, '' as INTEGRATIONID, CONTNBR, CONTLNSEQNBR, CONTSTARTDTE, CONTENDDTE, CONTITEMNBR, CONTSERIALNBR, 0 as BULKPICKPRNT, 0 as INDPICKPRNT, ISLINEINTRA, '' as SOFULFILLMENTBIN, 0 as MULTIPLEBINS

    FROM NET.dbo.SOP30300 WHERE SOPNUMBE = @SOPID

    IF @@ERROR = 0

    BEGIN

    --Delete History Header

    DELETE FROM NET.dbo.SOP30200 WHERE DEX_ROW_ID=@DEX_ROW_ID_Header

    --Delete History Line Items

    DELETE FROM NET.dbo.SOP30300 WHERE  SOPNUMBE = @SOPID

    --Remove Temporary ! sysmbol from Invoice ID in work tables

    Update  NET.dbo.SOP10100 SET SOPNUMBE=REPLACE(SOPNUMBE,'!',''),PSTGSTUS=0, TRXSORCE='',REFRENCE='',PTDUSRID='',POSTEDDT='2013-11-13 00:00:00.000' WHERE SOPNUMBE = @SOPIDTemp

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

    --Remove Receivables Management Data

    DELETE FROM NET.dbo.RM00401 WHERE DOCNUMBR =  @SOPID

    --NOTE This does nothing for distributions. Script written for when a work invoice has been posted partially

    --Distributions where never moded.

    --SOP10102 – GL Distributions Work and History

    END

    END TRY

    BEGIN CATCH

    Print 'There was an error in cloning the work and history table'

    END CATCH

    If @@TRANCOUNT > 0

    COMMIT TRANSACTION;

    GO