-- =============================================
--
-- =============================================
--6/01/2017
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'fp_SOP10100_UnVoid'
AND type = 'P')
DROP PROCEDURE fp_SOP10100_UnVoid
GO
CREATE PROCEDURE fp_SOP10100_UnVoid
-- exec fp_SOP10100_UnVoid "INVFC0086575",3
@sopnumbe VARCHAR(21),
@soptype int
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON
DECLARE @sopnumbeNEW varchar(21),
@O_iErrorState INT,
@docid VARCHAR(11)
DECLARE @msg VARCHAR(100)
SELECT @sopnumbeNEW ='',
@O_iErrorState =0
SELECT @docid = docid FROM sop30200 WHERE sopnumbe = @sopnumbe AND soptype = @soptype
EXEC sopgetidnumber @soptype,@docid,1,@sopnumbeNEW OUT,@O_iErrorState OUT
IF @sopnumbeNEW IS NULL begin
SET @msg = 'Failed to Unvoid ' + @sopnumbe
RAISERROR (@msg,16,2)
return
end
BEGIN TRY
BEGIN TRAN
-- be carefull not to exit this or the transaction will not get committed, you'll lock the table up
INSERT INTO sop10100 (SOPTYPE ,SOPNUMBE ,BACHNUMB ,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 ,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 ,Workflow_Status ,ContractExchangeRateStat ,Print_Phone_NumberGB )
SELECT h.SOPTYPE, @sopnumbeNEW ,'UNVOIDED' ,h.ORIGTYPE ,h.ORIGNUMB ,h.DOCID ,h.DOCDATE ,h.GLPOSTDT ,h.QUOTEDAT ,h.QUOEXPDA ,h.ORDRDATE ,h.INVODATE ,h.BACKDATE ,h.RETUDATE ,h.ReqShipDate ,h.FUFILDAT ,h.ACTLSHIP ,h.DISCDATE ,h.DUEDATE ,h.REPTING ,h.TRXFREQU ,h.TIMEREPD ,h.TIMETREP ,h.DYSTINCR ,h.DTLSTREP ,h.DSTBTCH1 ,h.DSTBTCH2 ,h.USDOCID1 ,h.USDOCID2 ,h.DISCFRGT ,h.ORDAVFRT ,h.DISCMISC ,h.ORDAVMSC ,h.DISAVAMT ,h.ORDAVAMT ,h.DISCRTND ,h.ORDISRTD ,h.DISTKNAM ,h.ORDISTKN ,h.DSCPCTAM ,h.DSCDLRAM ,h.ORDDLRAT ,h.DISAVTKN ,h.ORDATKN ,h.PYMTRMID ,h.PRCLEVEL ,h.LOCNCODE ,'Sales Entry' ,h.CUSTNMBR ,h.CUSTNAME ,h.CSTPONBR ,h.PROSPECT ,h.MSTRNUMB ,h.PCKSLPNO ,h.PICTICNU ,h.MRKDNAMT ,h.ORMRKDAM ,h.PRBTADCD ,h.PRSTADCD ,h.CNTCPRSN ,h.ShipToName ,h.ADDRESS1 ,h.ADDRESS2 ,h.ADDRESS3 ,h.CITY ,h.STATE ,h.ZIPCODE ,h.CCode ,h.COUNTRY ,h.PHNUMBR1 ,h.PHNUMBR2 ,h.PHONE3 ,h.FAXNUMBR ,h.COMAPPTO ,h.COMMAMNT ,h.OCOMMAMT ,h.CMMSLAMT ,h.ORCOSAMT ,h.NCOMAMNT ,h.ORNCMAMT ,h.SHIPMTHD ,h.TRDISAMT ,h.ORTDISAM ,h.TRDISPCT ,h.SUBTOTAL ,h.ORSUBTOT ,h.REMSUBTO ,h.OREMSUBT ,h.EXTDCOST ,h.OREXTCST ,h.FRTAMNT ,h.ORFRTAMT ,h.MISCAMNT ,h.ORMISCAMT ,h.TXENGCLD ,h.TAXEXMT1 ,h.TAXEXMT2 ,h.TXRGNNUM ,h.TAXSCHID ,h.TXSCHSRC ,h.BSIVCTTL ,h.FRTSCHID ,h.FRTTXAMT ,h.ORFRTTAX ,h.FRGTTXBL ,h.MSCSCHID ,h.MSCTXAMT ,h.ORMSCTAX ,h.MISCTXBL ,h.BKTFRTAM ,h.ORBKTFRT ,h.BKTMSCAM ,h.ORBKTMSC ,h.BCKTXAMT ,h.OBTAXAMT ,h.TXBTXAMT ,h.OTAXTAMT ,h.TAXAMNT ,h.ORTAXAMT ,h.ECTRX ,h.DOCAMNT ,h.ORDOCAMT ,h.PYMTRCVD ,h.ORPMTRVD ,h.DEPRECVD ,h.ORDEPRVD ,h.CODAMNT ,h.ORCODAMT ,h.ACCTAMNT ,h.ORACTAMT ,h.SALSTERR ,h.SLPRSNID ,h.UPSZONE ,h.TIMESPRT ,h.PSTGSTUS ,0 ,h.ALLOCABY ,h.NOTEINDX ,h.CURNCYID ,h.CURRNIDX ,h.RATETPID ,h.EXGTBLID ,h.XCHGRATE ,h.DENXRATE ,h.EXCHDATE ,h.TIME1 ,h.RTCLCMTD ,h.MCTRXSTT ,'' ,h.SOPHDRE1 ,h.SOPHDRE2 ,h.SOPLNERR ,h.SOPHDRFL ,h.COMMNTID ,h.REFRENCE ,'1/1/1900' ,'' ,h.USER2ENT ,h.CREATDDT ,h.MODIFDT ,h.Tax_Date ,h.APLYWITH ,h.WITHHAMT ,h.SHPPGDOC ,h.CORRCTN ,h.SIMPLIFD ,h.DOCNCORR ,h.SEQNCORR ,h.SALEDATE ,h.EXCEPTIONALDEMAND ,h.Flags ,h.SOPSTATUS ,h.SHIPCOMPLETE ,h.DIRECTDEBIT ,h.WorkflowApprStatCreditLm ,h.WorkflowPriorityCreditLm ,h.WorkflowApprStatusQuote ,h.WorkflowPriorityQuote ,h.Workflow_Status ,h.ContractExchangeRateStat ,h.Print_Phone_NumberGB
FROM SOP30200 h
WHERE h.SOPNUMBE = @sopnumbe AND h.soptype = @soptype
INSERT INTO sop10200 (l.SOPTYPE ,l.SOPNUMBE ,l.LNITMSEQ ,l.CMPNTSEQ ,l.ITEMNMBR ,l.ITEMDESC ,l.NONINVEN ,l.DROPSHIP ,l.UOFM ,l.LOCNCODE ,l.UNITCOST ,l.ORUNTCST ,l.UNITPRCE ,l.ORUNTPRC ,l.XTNDPRCE ,l.OXTNDPRC ,l.REMPRICE ,l.OREPRICE ,l.EXTDCOST ,l.OREXTCST ,l.MRKDNAMT ,l.ORMRKDAM ,l.MRKDNPCT ,l.MRKDNTYP ,l.INVINDX ,l.CSLSINDX ,l.SLSINDX ,l.MKDNINDX ,l.RTNSINDX ,l.INUSINDX ,l.INSRINDX ,l.DMGDINDX ,l.ITMTSHID ,l.IVITMTXB ,l.BKTSLSAM ,l.ORBKTSLS ,l.TAXAMNT ,l.ORTAXAMT ,l.TXBTXAMT ,l.OTAXTAMT ,l.BSIVCTTL ,l.TRDISAMT ,l.ORTDISAM ,l.DISCSALE ,l.ORDAVSLS ,l.QUANTITY ,l.ATYALLOC ,l.QTYINSVC ,l.QTYINUSE ,l.QTYDMGED ,l.QTYRTRND ,l.QTYONHND ,l.QTYCANCE ,l.QTYCANOT ,l.QTYORDER ,l.QTYPRBAC ,l.QTYPRBOO ,l.QTYPRINV ,l.QTYPRORD ,l.QTYPRVRECVD ,l.QTYRECVD ,l.QTYREMAI ,l.QTYREMBO ,l.QTYTBAOR ,l.QTYTOINV ,l.QTYTORDR ,l.QTYFULFI ,l.QTYSLCTD ,l.QTYBSUOM ,l.EXTQTYAL ,l.EXTQTYSEL ,l.ReqShipDate ,l.FUFILDAT ,l.ACTLSHIP ,l.SHIPMTHD ,l.SALSTERR ,l.SLPRSNID ,l.PRCLEVEL ,l.COMMNTID ,l.BRKFLD1 ,l.BRKFLD2 ,l.BRKFLD3 ,l.CURRNIDX ,l.TRXSORCE ,l.SOPLNERR ,l.ORGSEQNM ,l.ITEMCODE ,l.PURCHSTAT ,l.DECPLQTY ,l.DECPLCUR ,l.ODECPLCU ,l.EXCEPTIONALDEMAND ,l.TAXSCHID ,l.TXSCHSRC ,l.PRSTADCD ,l.ShipToName ,l.CNTCPRSN ,l.ADDRESS1 ,l.ADDRESS2 ,l.ADDRESS3 ,l.CITY ,l.STATE ,l.ZIPCODE ,l.CCode ,l.COUNTRY ,l.PHONE1 ,l.PHONE2 ,l.PHONE3 ,l.FAXNUMBR ,l.Flags ,l.CONTNBR ,l.CONTLNSEQNBR ,l.CONTSTARTDTE ,l.CONTENDDTE ,l.CONTITEMNBR ,l.CONTSERIALNBR ,l.ISLINEINTRA ,l.Print_Phone_NumberGB )
SELECT l.SOPTYPE ,@sopnumbeNEW,l.LNITMSEQ ,l.CMPNTSEQ ,l.ITEMNMBR ,l.ITEMDESC ,l.NONINVEN ,l.DROPSHIP ,l.UOFM ,l.LOCNCODE ,l.UNITCOST ,l.ORUNTCST ,l.UNITPRCE ,l.ORUNTPRC ,l.XTNDPRCE ,l.OXTNDPRC ,l.REMPRICE ,l.OREPRICE ,l.EXTDCOST ,l.OREXTCST ,l.MRKDNAMT ,l.ORMRKDAM ,l.MRKDNPCT ,l.MRKDNTYP ,l.INVINDX ,l.CSLSINDX ,l.SLSINDX ,l.MKDNINDX ,l.RTNSINDX ,l.INUSINDX ,l.INSRINDX ,l.DMGDINDX ,l.ITMTSHID ,l.IVITMTXB ,l.BKTSLSAM ,l.ORBKTSLS ,l.TAXAMNT ,l.ORTAXAMT ,l.TXBTXAMT ,l.OTAXTAMT ,l.BSIVCTTL ,l.TRDISAMT ,l.ORTDISAM ,l.DISCSALE ,l.ORDAVSLS ,l.QUANTITY ,l.ATYALLOC ,l.QTYINSVC ,l.QTYINUSE ,l.QTYDMGED ,l.QTYRTRND ,l.QTYONHND ,l.QTYCANCE ,l.QTYCANOT ,l.QTYORDER ,l.QTYPRBAC ,l.QTYPRBOO ,l.QTYPRINV ,l.QTYPRORD ,l.QTYPRVRECVD ,l.QTYRECVD ,l.QTYREMAI ,l.QTYREMBO ,l.QTYTBAOR ,l.QTYTOINV ,l.QTYTORDR ,l.QTYFULFI ,l.QTYSLCTD ,l.QTYBSUOM ,l.EXTQTYAL ,l.EXTQTYSEL ,l.ReqShipDate ,l.FUFILDAT ,l.ACTLSHIP ,l.SHIPMTHD ,l.SALSTERR ,l.SLPRSNID ,l.PRCLEVEL ,l.COMMNTID ,l.BRKFLD1 ,l.BRKFLD2 ,l.BRKFLD3 ,l.CURRNIDX ,l.TRXSORCE ,l.SOPLNERR ,l.ORGSEQNM ,l.ITEMCODE ,l.PURCHSTAT ,l.DECPLQTY ,l.DECPLCUR ,l.ODECPLCU ,l.EXCEPTIONALDEMAND ,l.TAXSCHID ,l.TXSCHSRC ,l.PRSTADCD ,l.ShipToName ,l.CNTCPRSN ,l.ADDRESS1 ,l.ADDRESS2 ,l.ADDRESS3 ,l.CITY ,l.STATE ,l.ZIPCODE ,l.CCode ,l.COUNTRY ,l.PHONE1 ,l.PHONE2 ,l.PHONE3 ,l.FAXNUMBR ,l.Flags ,l.CONTNBR ,l.CONTLNSEQNBR ,l.CONTSTARTDTE ,l.CONTENDDTE ,l.CONTITEMNBR ,l.CONTSERIALNBR ,l.ISLINEINTRA ,l.Print_Phone_NumberGB
FROM SOP30300 l
WHERE l.SOPNUMBE = @sopnumbe AND l.soptype = @soptype
EXEC fp_SY00500_UPD 'UNVOIDED','Sales Entry', 'OED',3,'COWETA OPER'
COMMIT TRAN
END TRY
BEGIN CATCH
--find out what went wrong
PRINT ERROR_NUMBER()
PRINT ERROR_SEVERITY() --AS ErrorSeverity,
PRINT ERROR_STATE() --AS ErrorState,
PRINT ERROR_PROCEDURE()-- AS ErrorProcedure,
PRINT ERROR_LINE()-- AS ErrorLine,
PRINT ERROR_MESSAGE()-- AS ErrorMessage;
--roll everything back
ROLLBACK TRAN
SET @msg = 'Failed to unvoid ' + @sopnumbe
RAISERROR (@msg,16,2)
return
-- exec fp_SOP10100_UnVoid "INVFC0085376",3
END CATCH
--SELECT sopnumbe, soptype FROM sop30200 WHERE VOIDSTTS = 1 AND DEX_ROW_TS > '5/31/2017' AND BACHNUMB = 'sphillips' ORDER BY SOPNUMBE
PRINT @sopnumbe + ' unvoided to ' + @sopnumbeNEW
GO
grant exec on fp_SOP10100_UnVoid to public