Dynamics Community Forum Thread Details

Several Orders were not transferring properly to the history table when they were completed. I attempted to reconcile each order, but it did not work. I ran the following SQL Statement below and all data is now in the appropriate locations. However, the SOPSTATUS for these orders is still 8 even though they are now in the history table, in which they should be 9.

Questions:

1. Is the fix simply updating the orders' SOPSTATUS to 9 or are there other back-end processes that occur when the SOPSTATUS is changed to 9. If there are other back-end processes what are they/can I make the changes manually?

2. Is there a better solution to the problem that what I have done?

SQL CODE:

--copy line items from expired quotes to history

INSERT INTO SOP30300
(SOPTYPE,SOPNUMBE,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,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)
SELECT
SOP10200.SOPTYPE,SOP10200.SOPNUMBE,SOP10200.LNITMSEQ,SOP10200.CMPNTSEQ,SOP10200.ITEMNMBR,SOP10200.ITEMDESC,SOP10200.NONINVEN,SOP10200.DROPSHIP,SOP10200.UOFM,SOP10200.LOCNCODE,SOP10200.UNITCOST,SOP10200.ORUNTCST,SOP10200.UNITPRCE,SOP10200.ORUNTPRC,SOP10200.XTNDPRCE,SOP10200.OXTNDPRC,SOP10200.REMPRICE,SOP10200.OREPRICE,SOP10200.EXTDCOST,SOP10200.OREXTCST,SOP10200.MRKDNAMT,SOP10200.ORMRKDAM,SOP10200.MRKDNPCT,SOP10200.MRKDNTYP,SOP10200.INVINDX,SOP10200.CSLSINDX,SOP10200.SLSINDX,SOP10200.MKDNINDX,SOP10200.RTNSINDX,SOP10200.INUSINDX,SOP10200.INSRINDX,SOP10200.DMGDINDX,SOP10200.ITMTSHID,SOP10200.IVITMTXB,SOP10200.BKTSLSAM,SOP10200.ORBKTSLS,SOP10200.TAXAMNT,SOP10200.ORTAXAMT,SOP10200.TXBTXAMT,SOP10200.OTAXTAMT,SOP10200.BSIVCTTL,SOP10200.TRDISAMT,SOP10200.ORTDISAM,SOP10200.DISCSALE,SOP10200.ORDAVSLS,SOP10200.QUANTITY,SOP10200.ATYALLOC,SOP10200.QTYINSVC,SOP10200.QTYINUSE,SOP10200.QTYDMGED,SOP10200.QTYRTRND,SOP10200.QTYONHND,SOP10200.QTYCANCE,SOP10200.QTYCANOT,SOP10200.QTYORDER,SOP10200.QTYPRBAC,SOP10200.QTYPRBOO,SOP10200.QTYPRINV,SOP10200.QTYPRORD,SOP10200.QTYPRVRECVD,SOP10200.QTYRECVD,SOP10200.QTYREMAI,SOP10200.QTYREMBO,SOP10200.QTYTBAOR,SOP10200.QTYTOINV,SOP10200.QTYTORDR,SOP10200.QTYFULFI,SOP10200.QTYSLCTD,SOP10200.QTYBSUOM,SOP10200.EXTQTYAL,SOP10200.EXTQTYSEL,SOP10200.ReqShipDate,SOP10200.FUFILDAT,SOP10200.ACTLSHIP,SOP10200.SHIPMTHD,SOP10200.SALSTERR,SOP10200.SLPRSNID,SOP10200.PRCLEVEL,SOP10200.COMMNTID,SOP10200.BRKFLD1,SOP10200.BRKFLD2,SOP10200.BRKFLD3,SOP10200.CURRNIDX,SOP10200.TRXSORCE,SOP10200.SOPLNERR,SOP10200.ORGSEQNM,SOP10200.ITEMCODE,SOP10200.PURCHSTAT,SOP10200.DECPLQTY,SOP10200.DECPLCUR,SOP10200.ODECPLCU,SOP10200.EXCEPTIONALDEMAND,SOP10200.TAXSCHID,SOP10200.TXSCHSRC,SOP10200.PRSTADCD,SOP10200.ShipToName,SOP10200.CNTCPRSN,SOP10200.ADDRESS1,SOP10200.ADDRESS2,SOP10200.ADDRESS3,SOP10200.CITY,SOP10200.STATE,SOP10200.ZIPCODE,SOP10200.CCode,SOP10200.COUNTRY,SOP10200.PHONE1,SOP10200.PHONE2,SOP10200.PHONE3,SOP10200.FAXNUMBR,SOP10200.Flags,SOP10200.CONTNBR,SOP10200.CONTLNSEQNBR,SOP10200.CONTSTARTDTE,SOP10200.CONTENDDTE,SOP10200.CONTITEMNBR,SOP10200.CONTSERIALNBR,SOP10200.ISLINEINTRA
FROM SOP10100 LEFT OUTER JOIN
SOP10200 ON SOP10100.SOPTYPE = SOP10200.SOPTYPE AND SOP10100.SOPNUMBE = SOP10200.SOPNUMBE
WHERE SOP10200.SOPTYPE = 2 AND SOP10200.SOPNUMBE='order#'

--HEADER
--copy header records from expired quotes to history

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

--LINE
--delete line items of expired quotes from WORK

DELETE SOP10200
FROM SOP10100 LEFT OUTER JOIN
SOP10200 ON SOP10100.SOPTYPE = SOP10200.SOPTYPE AND SOP10100.SOPNUMBE = SOP10200.SOPNUMBE
WHERE SOP10200.SOPTYPE = 2 AND SOP10200.SOPNUMBE='order#'

--HEADER
--delete header records of expired quotes from WORK
DELETE SOP10100
WHERE SOP10100.SOPTYPE = 2 AND SOP10100.SOPNUMBE='order#'