Dave,
Give this one a try, it copies the SOP10100 & SOP10200 records to history and delete them in the open table.
If it's an invoice make sure everything has posted thru to the GL and check the status of all other records related to this document.
Change the SOPTYPE and SOPNUMBER variables to the ones on your document.
Make sure to run this in a test environment before trying in production.
Ron
-- Move SOP Document from Work to History_Fetch.sql
Declare @INSOPType smallint
Declare @INSOPNumber char(21)
Declare @SOPType smallint
Declare @SOPNumber char(21)
Declare @LineItemSeq int
Declare @ComponentSeq int
Declare @DexRow int
Declare @RowNumber int
Set @SOPType = 2
Set @SOPNumber = 'OR000131263'
Set @INSOPType = 2
Set @INSOPNumber = 'OR000131263'
Begin Transaction
print 'Start of Push to History'
SET IDENTITY_INSERT am.dbo.sop30200 ON
set @DexRow = (select max(dex_row_id) from sop30200) + 1
print 'New SOP30200 dex_row_id = ' + cast(@DexRow as varchar)
-- Copy record from SOP10100 to SOP30200
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,DEX_ROW_ID
)
Select
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, @DexRow
from SOP10100 s1
Where s1.soptype = @SOPType and s1.sopnumbe = @SOPNumber
if @@error > 0
Begin
print 'Insert from SOP10100 error'
SET IDENTITY_INSERT am.dbo.sop30200 OFF
SET IDENTITY_INSERT am.dbo.sop30300 OFF
Rollback Transaction
Return
End
SET IDENTITY_INSERT am.dbo.sop30200 OFF
print 'SOP30200 Insert Complete'
-- Copy record from SOP10200 to SOP30300
SET IDENTITY_INSERT am.dbo.sop30300 ON
Declare SOP10200_Lines CURSOR for
select SOPTYPE, SOPNUMBE, LNITMSEQ, CMPNTSEQ
from SOP10200
where SOPTYPE= @INSOPType and SOPNUMBE = @INSOPNumber
Open SOP10200_Lines
print 'Fetch First Record'
FETCH NEXT FROM SOP10200_Lines INTO @SOPType, @SOPNumber, @LineItemSeq, @ComponentSeq
set @RowNumber = 0
print 'Fetch Status = ' + cast(@@FETCH_STATUS as varchar)
if @@FETCH_STATUS <> 0
begin
print 'Fetch Status Abort'
SET IDENTITY_INSERT am.dbo.sop30200 OFF
SET IDENTITY_INSERT am.dbo.sop30300 OFF
CLOSE SOP10200_Lines
DEALLOCATE SOP10200_Lines
Rollback Transaction
Return
end
WHILE @@FETCH_STATUS = 0
Begin
begin transaction
set @RowNumber = @RowNumber + 1
-- set @DexRow = (select max(dex_row_id) from sop30200) + @RowNumber
set @DexRow = (select max(dex_row_id) from sop30300) + 1
print 'Dex Row = ' + cast(@DexRow as varchar)
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, 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, DEX_ROW_ID
)
select
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, @DexRow
from sop10200
where SOPTYPE = @SOPType and SOPNUMBE = @SOPNumber and LNITMSEQ = @LineItemSeq and CMPNTSEQ = @ComponentSeq
if @@ERROR > 0
begin
SET IDENTITY_INSERT am.dbo.sop30300 OFF
CLOSE SOP10200_Lines
DEALLOCATE SOP10200_Lines
rollback transaction
Print 'Error'
Return
end
else
begin
print 'Row = ' + cast(@RowNumber as varchar) + ' Type = ' + cast(@SOPTYPE as varchar)
commit transaction
FETCH NEXT FROM SOP10200_Lines INTO @SOPType, @SOPNumber, @LineItemSeq, @ComponentSeq
end
End
CLOSE SOP10200_Lines
DEALLOCATE SOP10200_Lines
SET IDENTITY_INSERT am.dbo.sop30300 OFF
print 'SOP30300 Insert Complete'
-- Remove record from SOP10200
Delete from SOP10200
Where soptype = @SOPType and sopnumbe = @SOPNumber
if @@error > 0
Begin
print 'Remove from SOP10200 error'
SET IDENTITY_INSERT am.dbo.sop30200 OFF
SET IDENTITY_INSERT am.dbo.sop30300 OFF
Rollback Transaction
Return
End
print 'SOP10200 Delete Complete'
-- Remove record from SOP10100
Delete from SOP10100
Where soptype = @SOPType and sopnumbe = @SOPNumber
if @@error > 0
Begin
print 'Remove from SOP10100 error'
SET IDENTITY_INSERT am.dbo.sop30200 OFF
SET IDENTITY_INSERT am.dbo.sop30300 OFF
Rollback Transaction
Return
End
print 'SOP10100 Delete Complete'
-- Commit Transaction
Commit Transaction
print 'Transaction Comitted'
