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
Share
Report
All responses (
Answers (
5,985
