IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'FP_SOPTotal'
AND type = 'P')
DROP PROCEDURE FP_SOPTotal
GO
CREATE PROCEDURE FP_SOPTotal
@sopnumbe varchar(21),
@soptype int,
@userID varchar(15)= 'Used for Logging'
as
set nocount on
set transaction isolation level read uncommitted
if not exists (select 1 from SOP10100 where SOPNUMBE = @sopnumbe and SOPTYPE = @soptype) begin
return
end
DECLARE @RC int
DECLARE @I_vSOPTYPE smallint
DECLARE @I_vSOPNUMBE char(21)
DECLARE @I_vTAXSCHID char(15)
DECLARE @I_vFRTSCHID char(15)
DECLARE @I_vMSCSCHID char(15)
DECLARE @I_vSHIPMTHD char(15)
DECLARE @I_vLOCNCODE char(10)
DECLARE @I_vDOCDATE datetime
DECLARE @I_vFREIGHT numeric(19,5)
DECLARE @I_vMISCAMNT numeric(19,5)
DECLARE @I_vTRDISAMT numeric(19,5)
DECLARE @I_vTRADEPCT numeric(19,2)
DECLARE @I_vDISTKNAM numeric(19,5)
DECLARE @I_vCUSTNAME char(64)
DECLARE @I_vCSTPONBR char(20)
DECLARE @I_vShipToName char(64)
DECLARE @I_vADDRESS1 char(60)
DECLARE @I_vADDRESS2 char(60)
DECLARE @I_vADDRESS3 char(60)
DECLARE @I_vCNTCPRSN char(60)
DECLARE @I_vFAXNUMBR char(21)
DECLARE @I_vCITY char(35)
DECLARE @I_vSTATE char(29)
DECLARE @I_vZIPCODE char(10)
DECLARE @I_vCOUNTRY char(60)
DECLARE @I_vPHNUMBR1 char(21)
DECLARE @I_vPHNUMBR2 char(21)
DECLARE @I_vPHNUMBR3 char(21)
DECLARE @I_vPYMTRCVD numeric(19,5)
DECLARE @I_vSALSTERR char(15)
DECLARE @I_vSLPRSNID char(15)
DECLARE @I_vUPSZONE char(3)
DECLARE @I_vBACHNUMB char(15)
DECLARE @I_vPRBTADCD char(15)
DECLARE @I_vPRSTADCD char(15)
DECLARE @I_vORDRDATE datetime
DECLARE @I_vPYMTRMID char(20)
DECLARE @I_vDUEDATE datetime
DECLARE @I_vDISCDATE datetime
DECLARE @I_vREFRENCE char(30)
DECLARE @I_vBatchCHEKBKID char(15)
DECLARE @I_vRECREATECOMM smallint
DECLARE @I_vCOMPRCNT numeric(19,2)
DECLARE @I_vRECREATEDIST smallint
DECLARE @I_vRECREATETAXES smallint
DECLARE @I_vDEFTAXSCHDS smallint
DECLARE @I_vXCHGRATE numeric(19,7)
DECLARE @I_vRATETPID char(15)
DECLARE @I_vEXPNDATE datetime
DECLARE @I_vEXCHDATE datetime
DECLARE @I_vEXGTBDSC char(30)
DECLARE @I_vEXTBLSRC char(50)
DECLARE @I_vRATEEXPR smallint
DECLARE @I_vDYSTINCR smallint
DECLARE @I_vRATEVARC numeric(19,7)
DECLARE @I_vTRXDTDEF smallint
DECLARE @I_vRTCLCMTD smallint
DECLARE @I_vPRVDSLMT smallint
DECLARE @I_vDATELMTS smallint
DECLARE @I_vTIME1 datetime
DECLARE @I_vDISAVAMT numeric(19,5)
DECLARE @I_vDSCDLRAM numeric(19,5)
DECLARE @I_vDSCPCTAM numeric(19,2)
DECLARE @I_vFREIGTBLE int
DECLARE @I_vMISCTBLE int
DECLARE @I_vCOMMNTID char(15)
DECLARE @I_vCOMMENT_1 char(50)
DECLARE @I_vCOMMENT_2 char(50)
DECLARE @I_vCOMMENT_3 char(50)
DECLARE @I_vCOMMENT_4 char(50)
DECLARE @I_vGPSFOINTEGRATIONID char(30)
DECLARE @I_vINTEGRATIONSOURCE smallint
DECLARE @I_vINTEGRATIONID char(30)
DECLARE @I_vReqShipDate datetime
DECLARE @I_vRequesterTrx smallint
DECLARE @I_vQUOEXPDA datetime
DECLARE @I_vQUOTEDAT datetime
DECLARE @I_vINVODATE datetime
DECLARE @I_vBACKDATE datetime
DECLARE @I_vRETUDATE datetime
DECLARE @I_vCMMTTEXT varchar(500)
DECLARE @I_vRECALCBTCHTOT int
DECLARE @I_vCHGAMNT numeric(19,5)
DECLARE @I_vCKCreditLimit tinyint
DECLARE @I_vCKHOLD tinyint
DECLARE @I_vPRCLEVEL char(10)
DECLARE @I_vTAXEXMT1 char(25)
DECLARE @I_vTAXEXMT2 char(25)
DECLARE @I_vTXRGNNUM char(25)
DECLARE @I_vREPTING tinyint
DECLARE @I_vTRXFREQU smallint
DECLARE @I_vTIMETREP smallint
DECLARE @I_vQUOTEDYSTINCR smallint
DECLARE @I_vNOTETEXT varchar(8000)
DECLARE @I_vUSRDEFND1 char(50)
DECLARE @I_vUSRDEFND2 char(50)
DECLARE @I_vUSRDEFND3 char(50)
DECLARE @I_vUSRDEFND4 varchar(8000)
DECLARE @I_vUSRDEFND5 varchar(8000)
DECLARE @O_iErrorState int
DECLARE @oErrString varchar(255)
-- TODO: Set parameter values here.
select
@I_vSOPTYPE = h.soptype
,@I_vSOPNUMBE = h.SOPNUMBE
,@I_vTAXSCHID = h.TAXSCHID
,@I_vFRTSCHID = h.FRTSCHID
,@I_vRECREATETAXES = 1
,@I_vDEFTAXSCHDS = 0
,@I_vMSCSCHID = h.MSCSCHID
,@I_vSHIPMTHD = h.SHIPMTHD
,@I_vLOCNCODE = h.LOCNCODE
,@I_vDOCDATE = h.DOCDATE
,@I_vFREIGHT = h.FRTAMNT
,@I_vMISCAMNT = h.MISCAMNT
,@I_vTRDISAMT = null
,@I_vTRADEPCT = null
,@I_vDISTKNAM = h.DISTKNAM
,@I_vCUSTNAME = h.CUSTNAME
,@I_vCSTPONBR = h.CSTPONBR
,@I_vShipToName = h.CNTCPRSN
,@I_vADDRESS1 = h.ADDRESS1
,@I_vADDRESS2 = h.ADDRESS2
,@I_vADDRESS3 = h.ADDRESS3
,@I_vCNTCPRSN = h.CNTCPRSN
,@I_vFAXNUMBR = h.FAXNUMBR
,@I_vCITY = h.CITY
,@I_vSTATE = h.STATE
,@I_vZIPCODE = h.ZIPCODE
,@I_vCOUNTRY = h.COUNTRY
,@I_vPHNUMBR1 = h.PHNUMBR1
,@I_vPHNUMBR2 = h.PHNUMBR1
,@I_vPHNUMBR3 = h.PHNUMBR2
,@I_vPYMTRCVD = h.PYMTRCVD
,@I_vSALSTERR = h.SALSTERR
,@I_vSLPRSNID = h.SLPRSNID
,@I_vUPSZONE = h.UPSZONE
,@I_vBACHNUMB = h.BACHNUMB
,@I_vPRBTADCD = h.PRBTADCD
,@I_vPRSTADCD = h.PRSTADCD
,@I_vORDRDATE = h.DOCDATE
,@I_vPYMTRMID = h.PYMTRMID
,@I_vDUEDATE = h.DUEDATE
,@I_vDISCDATE = h.DISCDATE
,@I_vREFRENCE = h.REFRENCE
,@I_vBatchCHEKBKID = isnull(b.CHEKBKID,'')
,@I_vRECREATECOMM = 0
,@I_vCOMPRCNT = 0
,@I_vRECREATEDIST = 0
,@I_vXCHGRATE = 0
,@I_vRATETPID = ''
,@I_vEXPNDATE = '1/1/1900'
,@I_vEXCHDATE = '1/1/1900'
,@I_vEXGTBDSC = null
,@I_vEXTBLSRC = null
,@I_vRATEEXPR = null
,@I_vDYSTINCR = null
,@I_vRATEVARC = null
,@I_vTRXDTDEF = null
,@I_vRTCLCMTD = null
,@I_vPRVDSLMT = null
,@I_vDATELMTS = null
,@I_vTIME1 = '1/1/1900'
,@I_vDISAVAMT = 0
,@I_vDSCDLRAM = 0
,@I_vDSCPCTAM = 0
,@I_vFREIGTBLE = 1
,@I_vMISCTBLE = 0
,@I_vCOMMNTID = h.COMMNTID
,@I_vCOMMENT_1 = isnull(ud.COMMENT_1,'')
,@I_vCOMMENT_2 = isnull(ud.COMMENT_2,'')
,@I_vCOMMENT_3 = isnull(ud.COMMENT_3,'')
,@I_vCOMMENT_4 = isnull(ud.COMMENT_4,'')
,@I_vGPSFOINTEGRATIONID = ''
,@I_vINTEGRATIONSOURCE = ''
,@I_vINTEGRATIONID = ''
,@I_vReqShipDate = h.ReqShipDate
,@I_vRequesterTrx = 0
,@I_vQUOEXPDA = h.QUOEXPDA
,@I_vQUOTEDAT = h.QUOTEDAT
,@I_vINVODATE = h.INVODATE
,@I_vBACKDATE = h.BACKDATE
,@I_vRETUDATE = h.RETUDATE
,@I_vCMMTTEXT = ud.cmmttext
,@I_vRECALCBTCHTOT = 1
,@I_vCHGAMNT = h.ACCTAMNT
,@I_vCKCreditLimit = 0
,@I_vCKHOLD = 0
,@I_vPRCLEVEL = h.PRCLEVEL
,@I_vTAXEXMT1 = 0
,@I_vTAXEXMT2 = 0
,@I_vTXRGNNUM = ''
,@I_vREPTING = 0
,@I_vTRXFREQU = 0
,@I_vTIMETREP = 0
,@I_vQUOTEDYSTINCR= 0
,@I_vNOTETEXT = ''
from SOP10100 h
left join SOP10106 ud on ud.SOPNUMBE = h.SOPNUMBE and ud.SOPTYPE = h.SOPTYPE
left join SY00500 b on b.BACHNUMB = h.BACHNUMB and b.SERIES = 3 and b.BCHSOURC = 'Sales Entry'
where h.SOPNUMBE = @sopnumbe
and h.SOPTYPE = @soptype
EXECUTE @RC = taSopHdrRecalc
--select
@I_vSOPTYPE
,@I_vSOPNUMBE
,@I_vTAXSCHID
,@I_vFRTSCHID
,@I_vMSCSCHID
,@I_vSHIPMTHD
,@I_vLOCNCODE
,@I_vDOCDATE
,@I_vFREIGHT
,@I_vMISCAMNT
,@I_vTRDISAMT
,@I_vTRADEPCT
,@I_vDISTKNAM
,@I_vCUSTNAME
,@I_vCSTPONBR
,@I_vShipToName
,@I_vADDRESS1
,@I_vADDRESS2
,@I_vADDRESS3
,@I_vCNTCPRSN
,@I_vFAXNUMBR
,@I_vCITY
,@I_vSTATE
,@I_vZIPCODE
,@I_vCOUNTRY
,@I_vPHNUMBR1
,@I_vPHNUMBR2
,@I_vPHNUMBR3
,@I_vPYMTRCVD
,@I_vSALSTERR
,@I_vSLPRSNID
,@I_vUPSZONE
,@I_vBACHNUMB
,@I_vPRBTADCD
,@I_vPRSTADCD
,@I_vORDRDATE
,@I_vPYMTRMID
,@I_vDUEDATE
,@I_vDISCDATE
,@I_vREFRENCE
,@I_vBatchCHEKBKID
,@I_vRECREATECOMM
,@I_vCOMPRCNT
,@I_vRECREATEDIST
,@I_vRECREATETAXES
,@I_vDEFTAXSCHDS
,@I_vXCHGRATE
,@I_vRATETPID
,@I_vEXPNDATE
,@I_vEXCHDATE
,@I_vEXGTBDSC
,@I_vEXTBLSRC
,@I_vRATEEXPR
,@I_vDYSTINCR
,@I_vRATEVARC
,@I_vTRXDTDEF
,@I_vRTCLCMTD
,@I_vPRVDSLMT
,@I_vDATELMTS
,@I_vTIME1
,@I_vDISAVAMT
,@I_vDSCDLRAM
,@I_vDSCPCTAM
,@I_vFREIGTBLE
,@I_vMISCTBLE
,@I_vCOMMNTID
,@I_vCOMMENT_1
,@I_vCOMMENT_2
,@I_vCOMMENT_3
,@I_vCOMMENT_4
,@I_vGPSFOINTEGRATIONID
,@I_vINTEGRATIONSOURCE
,@I_vINTEGRATIONID
,@I_vReqShipDate
,@I_vRequesterTrx
,@I_vQUOEXPDA
,@I_vQUOTEDAT
,@I_vINVODATE
,@I_vBACKDATE
,@I_vRETUDATE
,@I_vCMMTTEXT
,@I_vRECALCBTCHTOT
,@I_vCHGAMNT
,@I_vCKCreditLimit
,@I_vCKHOLD
,@I_vPRCLEVEL
,@I_vTAXEXMT1
,@I_vTAXEXMT2
,@I_vTXRGNNUM
,@I_vREPTING
,@I_vTRXFREQU
,@I_vTIMETREP
,@I_vQUOTEDYSTINCR
,@I_vNOTETEXT
,@I_vUSRDEFND1
,@I_vUSRDEFND2
,@I_vUSRDEFND3
,@I_vUSRDEFND4
,@I_vUSRDEFND5
,@O_iErrorState OUTPUT
,@oErrString OUTPUT
--test to see if there were errors
if @oErrString > '' begin
if @oErrString = '1189' begin --payments don't match
--we've chosen to ignore this error
return
end
declare @msg varchar(1000)
select @msg = 'Error retotalling ' + rtrim(@sopnumbe )
--this query access the DYNAMICS error code table and retrieves the error text.
select @msg = @msg + '. Code ' + convert(varchar(10),rtrim(@oErrString) ) + ' - ' + ErrorDesc
from dynamics..taerrorcode ec
where ec.errorcode = @oErrString
and ec.SourceProc = 'taSopHdrRecalc'
raiserror (@msg,16,2)
end
GO
Grant EXEC on FP_SOPTotal to public