IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'taUpdateCreateCustomerRcdWrapper'
AND type = 'P')
DROP PROCEDURE taUpdateCreateCustomerRcdWrapper
GO
CREATE PROCEDURE taUpdateCreateCustomerRcdWrapper
@custnmbr varchar(15)
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
--we expect the data to be in the custom DDtaUpdateCreateCustomerRcd table
--this table exactly mirrors the parameters for the taUpdateCreateCustomerRcd schema
--this code is designed to query that table and run the data into Dynamics
BEGIN TRY
BEGIN TRAN
-- be carefull not to exit this or the transaction will not get committed, you'll lock the table up
--declare the variables we'll need
DECLARE @RC int
declare @ErrorDesc varchar(255)
DECLARE @I_vCUSTNMBR char(15)
DECLARE @I_vHOLD tinyint
DECLARE @I_vINACTIVE tinyint
DECLARE @I_vCUSTNAME char(64)
DECLARE @I_vSHRTNAME char(15)
DECLARE @I_vSTMTNAME char(64)
DECLARE @I_vCUSTCLAS char(15)
DECLARE @I_vCUSTPRIORITY smallint
DECLARE @I_vADRSCODE char(15)
DECLARE @I_vCNTCPRSN char(60)
DECLARE @I_vADDRESS1 char(60)
DECLARE @I_vADDRESS2 char(60)
DECLARE @I_vADDRESS3 char(60)
DECLARE @I_vCITY char(35)
DECLARE @I_vSTATE char(29)
DECLARE @I_vZIPCODE char(10)
DECLARE @I_vCCode char(6)
DECLARE @I_vCOUNTRY char(60)
DECLARE @I_vPHNUMBR1 char(21)
DECLARE @I_vPHNUMBR2 char(21)
DECLARE @I_vPHNUMBR3 char(21)
DECLARE @I_vFAX char(21)
DECLARE @I_vUPSZONE char(3)
DECLARE @I_vSHIPMTHD char(15)
DECLARE @I_vTAXSCHID char(15)
DECLARE @I_vSHIPCOMPLETE tinyint
DECLARE @I_vPRSTADCD char(15)
DECLARE @I_vPRBTADCD char(15)
DECLARE @I_vSTADDRCD char(15)
DECLARE @I_vSLPRSNID char(15)
DECLARE @I_vSALSTERR char(15)
DECLARE @I_vUSERDEF1 char(20)
DECLARE @I_vUSERDEF2 char(20)
DECLARE @I_vCOMMENT1 char(30)
DECLARE @I_vCOMMENT2 char(30)
DECLARE @I_vCUSTDISC numeric(19,2)
DECLARE @I_vPYMTRMID char(20)
DECLARE @I_vDISGRPER smallint
DECLARE @I_vDUEGRPER smallint
DECLARE @I_vPRCLEVEL char(10)
DECLARE @I_vNOTETEXT varchar(8000)
DECLARE @I_vBALNCTYP tinyint
DECLARE @I_vFNCHATYP smallint
DECLARE @I_vFNCHPCNT numeric(19,2)
DECLARE @I_vFINCHDLR numeric(19,5)
DECLARE @I_vMINPYTYP smallint
DECLARE @I_vMINPYPCT numeric(19,2)
DECLARE @I_vMINPYDLR numeric(19,5)
DECLARE @I_vCRLMTTYP smallint
DECLARE @I_vCRLMTAMT numeric(19,5)
DECLARE @I_vCRLMTPER smallint
DECLARE @I_vCRLMTPAM numeric(19,5)
DECLARE @I_vMXWOFTYP smallint
DECLARE @I_vMXWROFAM numeric(19,5)
DECLARE @I_vRevalue_Customer tinyint
DECLARE @I_vPost_Results_To smallint
DECLARE @I_vORDERFULFILLDEFAULT tinyint
DECLARE @I_vINCLUDEINDP tinyint
DECLARE @I_vCRCARDID char(15)
DECLARE @I_vCRCRDNUM char(20)
DECLARE @I_vCCRDXPDT datetime
DECLARE @I_vBANKNAME char(30)
DECLARE @I_vBNKBRNCH char(20)
DECLARE @I_vUSERLANG smallint
DECLARE @I_vTAXEXMT1 char(25)
DECLARE @I_vTAXEXMT2 char(25)
DECLARE @I_vTXRGNNUM char(25)
DECLARE @I_vCURNCYID char(15)
DECLARE @I_vRATETPID char(15)
DECLARE @I_vSTMTCYCL smallint
DECLARE @I_vKPCALHST tinyint
DECLARE @I_vKPERHIST tinyint
DECLARE @I_vKPTRXHST tinyint
DECLARE @I_vKPDSTHST tinyint
DECLARE @I_vSend_Email_Statements tinyint
DECLARE @I_vToEmail_Recipient char(80)
DECLARE @I_vCcEmail_Recipient char(80)
DECLARE @I_vBccEmail_Recipient char(80)
DECLARE @I_vCHEKBKID char(15)
DECLARE @I_vDEFCACTY smallint
DECLARE @I_vRMCSHACTNUMST varchar(75)
DECLARE @I_vRMARACTNUMST varchar(75)
DECLARE @I_vRMSLSACTNUMST varchar(75)
DECLARE @I_vRMCOSACTNUMST varchar(75)
DECLARE @I_vRMIVACTNUMST varchar(75)
DECLARE @I_vRMTAKACTNUMST varchar(75)
DECLARE @I_vRMAVACTNUMST varchar(75)
DECLARE @I_vRMFCGACTNUMST varchar(75)
DECLARE @I_vRMWRACTNUMST varchar(75)
DECLARE @I_vRMSORACTNUMST varchar(75)
DECLARE @I_vRMOvrpymtWrtoffACTNUMST varchar(75)
DECLARE @I_vGPSFOINTEGRATIONID char(30)
DECLARE @I_vINTEGRATIONSOURCE smallint
DECLARE @I_vINTEGRATIONID char(30)
DECLARE @I_vUseCustomerClass tinyint
DECLARE @I_vCreateAddress tinyint
DECLARE @I_vUpdateIfExists tinyint
DECLARE @I_vRequesterTrx smallint
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)
--call the data from the table into variables
SELECT
@I_vCUSTNMBR = CUSTNMBR,
@I_vHOLD = HOLD,
@I_vINACTIVE = INACTIVE,
@I_vCUSTNAME = CUSTNAME,
@I_vSHRTNAME = SHRTNAME,
@I_vSTMTNAME = STMTNAME,
@I_vCUSTCLAS = CUSTCLAS,
@I_vCUSTPRIORITY = CUSTPRIORITY,
@I_vADRSCODE = ADRSCODE,
@I_vCNTCPRSN = CNTCPRSN,
@I_vADDRESS1 = ADDRESS1,
@I_vADDRESS2 = ADDRESS2,
@I_vADDRESS3 = ADDRESS3,
@I_vCITY = CITY,
@I_vSTATE = STATE,
@I_vZIPCODE = ZIPCODE,
@I_vCCode = CCode,
@I_vCOUNTRY = COUNTRY,
@I_vPHNUMBR1 = PHNUMBR1,
@I_vPHNUMBR2 = PHNUMBR2,
@I_vPHNUMBR3 = PHNUMBR3,
@I_vFAX = FAX,
@I_vUPSZONE = UPSZONE,
@I_vSHIPMTHD = SHIPMTHD,
@I_vTAXSCHID = TAXSCHID,
@I_vSHIPCOMPLETE = SHIPCOMPLETE,
@I_vPRSTADCD = PRSTADCD,
@I_vPRBTADCD = PRBTADCD,
@I_vSTADDRCD = STADDRCD,
@I_vSLPRSNID = SLPRSNID,
@I_vSALSTERR = SALSTERR,
@I_vUSERDEF1 = USERDEF1,
@I_vUSERDEF2 = USERDEF2,
@I_vCOMMENT1 = COMMENT1,
@I_vCOMMENT2 = COMMENT2,
@I_vCUSTDISC = CUSTDISC,
@I_vPYMTRMID = PYMTRMID,
@I_vDISGRPER = DISGRPER,
@I_vDUEGRPER = DUEGRPER,
@I_vPRCLEVEL = PRCLEVEL,
@I_vNOTETEXT = NOTETEXT,
@I_vBALNCTYP = BALNCTYP,
@I_vFNCHATYP = FNCHATYP,
@I_vFNCHPCNT = FNCHPCNT,
@I_vFINCHDLR = FINCHDLR,
@I_vMINPYTYP = MINPYTYP,
@I_vMINPYPCT = MINPYPCT,
@I_vMINPYDLR = MINPYDLR,
@I_vCRLMTTYP = CRLMTTYP,
@I_vCRLMTAMT = CRLMTAMT,
@I_vCRLMTPER = CRLMTPER,
@I_vCRLMTPAM = CRLMTPAM,
@I_vMXWOFTYP = MXWOFTYP,
@I_vMXWROFAM = MXWROFAM,
@I_vRevalue_Customer = Revalue_Customer,
@I_vPost_Results_To = Post_Results_To,
@I_vORDERFULFILLDEFAULT = ORDERFULFILLDEFAULT,
@I_vINCLUDEINDP = INCLUDEINDP,
@I_vCRCARDID = CRCARDID,
@I_vCRCRDNUM = CRCRDNUM,
@I_vCCRDXPDT = CCRDXPDT,
@I_vBANKNAME = BANKNAME,
@I_vBNKBRNCH = BNKBRNCH,
@I_vUSERLANG = USERLANG,
@I_vTAXEXMT1 = TAXEXMT1,
@I_vTAXEXMT2 = TAXEXMT2,
@I_vTXRGNNUM = TXRGNNUM,
@I_vCURNCYID = CURNCYID,
@I_vRATETPID = RATETPID,
@I_vSTMTCYCL = STMTCYCL,
@I_vKPCALHST = KPCALHST,
@I_vKPERHIST = KPERHIST,
@I_vKPTRXHST = KPTRXHST,
@I_vKPDSTHST = KPDSTHST,
@I_vSend_Email_Statements = Send_Email_Statements ,
@I_vToEmail_Recipient = ToEmail_Recipient,
@I_vCcEmail_Recipient = CcEmail_Recipient,
@I_vBccEmail_Recipient = BccEmail_Recipient,
@I_vCHEKBKID = CHEKBKID,
@I_vDEFCACTY = DEFCACTY,
@I_vRMCSHACTNUMST = RMCSHACTNUMST,
@I_vRMARACTNUMST = RMARACTNUMST,
@I_vRMSLSACTNUMST = RMSLSACTNUMST,
@I_vRMCOSACTNUMST = RMCOSACTNUMST,
@I_vRMIVACTNUMST = RMIVACTNUMST,
@I_vRMTAKACTNUMST = RMTAKACTNUMST,
@I_vRMAVACTNUMST = RMAVACTNUMST,
@I_vRMFCGACTNUMST = RMFCGACTNUMST,
@I_vRMWRACTNUMST = RMWRACTNUMST,
@I_vRMSORACTNUMST = RMSORACTNUMST,
@I_vRMOvrpymtWrtoffACTNUMST = RMOvrpymtWrtoffACTNUMST,
@I_vGPSFOINTEGRATIONID = GPSFOINTEGRATIONID,
@I_vINTEGRATIONSOURCE = INTEGRATIONSOURCE,
@I_vINTEGRATIONID = INTEGRATIONID,
@I_vUseCustomerClass = UseCustomerClass,
@I_vCreateAddress = CreateAddress,
@I_vUpdateIfExists = UpdateIfExists,
@I_vRequesterTrx = RequesterTrx,
@I_vUSRDEFND1 = USRDEFND1,
@I_vUSRDEFND2 = USRDEFND2,
@I_vUSRDEFND3 = USRDEFND3,
@I_vUSRDEFND4 = USRDEFND4,
@I_vUSRDEFND5 = USRDEFND5
FROM DDtaUpdateCreateCustomerRcd
where custnmbr = @custnmbr
--call eConnect
EXECUTE @RC = taUpdateCreateCustomerRcd @I_vCUSTNMBR ,@I_vHOLD ,@I_vINACTIVE ,@I_vCUSTNAME ,@I_vSHRTNAME ,@I_vSTMTNAME ,@I_vCUSTCLAS ,@I_vCUSTPRIORITY ,@I_vADRSCODE ,@I_vCNTCPRSN ,@I_vADDRESS1 ,@I_vADDRESS2 ,@I_vADDRESS3 ,@I_vCITY ,@I_vSTATE ,@I_vZIPCODE ,@I_vCCode ,@I_vCOUNTRY ,@I_vPHNUMBR1 ,@I_vPHNUMBR2 ,@I_vPHNUMBR3 ,@I_vFAX ,@I_vUPSZONE ,@I_vSHIPMTHD ,@I_vTAXSCHID ,@I_vSHIPCOMPLETE ,@I_vPRSTADCD ,@I_vPRBTADCD ,@I_vSTADDRCD ,@I_vSLPRSNID ,@I_vSALSTERR ,@I_vUSERDEF1 ,@I_vUSERDEF2 ,@I_vCOMMENT1 ,@I_vCOMMENT2 ,@I_vCUSTDISC ,@I_vPYMTRMID ,@I_vDISGRPER ,@I_vDUEGRPER ,@I_vPRCLEVEL ,@I_vNOTETEXT ,@I_vBALNCTYP ,@I_vFNCHATYP ,@I_vFNCHPCNT ,@I_vFINCHDLR ,@I_vMINPYTYP ,@I_vMINPYPCT ,@I_vMINPYDLR ,@I_vCRLMTTYP ,@I_vCRLMTAMT ,@I_vCRLMTPER ,@I_vCRLMTPAM ,@I_vMXWOFTYP ,@I_vMXWROFAM ,@I_vRevalue_Customer ,@I_vPost_Results_To ,@I_vORDERFULFILLDEFAULT ,@I_vINCLUDEINDP ,@I_vCRCARDID ,@I_vCRCRDNUM ,@I_vCCRDXPDT ,@I_vBANKNAME ,@I_vBNKBRNCH ,@I_vUSERLANG ,@I_vTAXEXMT1 ,@I_vTAXEXMT2 ,@I_vTXRGNNUM ,@I_vCURNCYID ,@I_vRATETPID ,@I_vSTMTCYCL ,@I_vKPCALHST ,@I_vKPERHIST ,@I_vKPTRXHST ,@I_vKPDSTHST ,@I_vSend_Email_Statements ,@I_vToEmail_Recipient ,@I_vCcEmail_Recipient ,@I_vBccEmail_Recipient ,@I_vCHEKBKID ,@I_vDEFCACTY ,@I_vRMCSHACTNUMST ,@I_vRMARACTNUMST ,@I_vRMSLSACTNUMST ,@I_vRMCOSACTNUMST ,@I_vRMIVACTNUMST ,@I_vRMTAKACTNUMST ,@I_vRMAVACTNUMST ,@I_vRMFCGACTNUMST ,@I_vRMWRACTNUMST ,@I_vRMSORACTNUMST ,@I_vRMOvrpymtWrtoffACTNUMST ,@I_vGPSFOINTEGRATIONID ,@I_vINTEGRATIONSOURCE ,@I_vINTEGRATIONID ,@I_vUseCustomerClass ,@I_vCreateAddress ,@I_vUpdateIfExists ,@I_vRequesterTrx ,@I_vUSRDEFND1 ,@I_vUSRDEFND2 ,@I_vUSRDEFND3 ,@I_vUSRDEFND4 ,@I_vUSRDEFND5 ,
@O_iErrorState = @O_iErrorState output,
@oErrString = @oErrString output
--if there is an error, @O_iErrorState will have the number and we'll look up the text for that error in DYNAMICS..taErrorCode
IF @O_iErrorState <> 0 BEGIN
SELECT @ErrorDesc = rtrim(@oErrString) + '/' + tec.ErrorDesc
FROM DYNAMICS..taErrorCode tec
WHERE tec.ErrorCode = @O_iErrorState
raiserror(@ErrorDesc,16,2)
end
COMMIT TRAN
END TRY
BEGIN CATCH
print 'taUpdateCreateCustomerRcdWrapper error handler'
SELECT
@ErrorDesc = 'taUpdateCreateCustomerRcdWrapper error: ' + ERROR_MESSAGE()
--roll everything back
ROLLBACK TRAN
raiserror(@ErrorDesc,16,2)
END CATCH
GO
grant exec on taUpdateCreateCustomerRcdWrapper to public
if exists (select 1 from INFORMATION_SCHEMA.TABLES where table_name = 'DDtaUpdateCreateCustomerRcd' ) BEGIN
drop table DDtaUpdateCreateCustomerRcd
end
GO
CREATE TABLE DDtaUpdateCreateCustomerRcd
(
RowID int NOT NULL IDENTITY (1, 1),
HeaderID int NOT NULL,
CUSTNMBR char (15) ,
HOLD tinyint ,
INACTIVE tinyint ,
CUSTNAME char (64) ,
SHRTNAME char (15) ,
STMTNAME char (64) ,
CUSTCLAS char (15) ,
CUSTPRIORITY smallint ,
ADRSCODE char (15) ,
CNTCPRSN char (60) ,
ADDRESS1 char (60) ,
ADDRESS2 char (60) ,
ADDRESS3 char (60) ,
CITY char (35) ,
STATE char (29) ,
ZIPCODE char (10) ,
CCode char (6) ,
COUNTRY char (60) ,
PHNUMBR1 char (21) ,
PHNUMBR2 char (21) ,
PHNUMBR3 char (21) ,
FAX char (21) ,
UPSZONE char (3) ,
SHIPMTHD char (15) ,
TAXSCHID char (15) ,
SHIPCOMPLETE tinyint ,
PRSTADCD char (15) ,
PRBTADCD char (15) ,
STADDRCD char (15) ,
SLPRSNID char (15) ,
SALSTERR char (15) ,
USERDEF1 char (20) ,
USERDEF2 char (20) ,
COMMENT1 char (30) ,
COMMENT2 char (30) ,
CUSTDISC numeric (19,2) ,
PYMTRMID char (20) ,
DISGRPER smallint ,
DUEGRPER smallint ,
PRCLEVEL char (10) ,
NOTETEXT varchar (8000) ,
BALNCTYP tinyint ,
FNCHATYP smallint ,
FNCHPCNT numeric (19,2) ,
FINCHDLR numeric (19,5) ,
MINPYTYP smallint ,
MINPYPCT numeric (19,2) ,
MINPYDLR numeric (19,5) ,
CRLMTTYP smallint ,
CRLMTAMT numeric (19,5) ,
CRLMTPER smallint ,
CRLMTPAM numeric (19,5) ,
MXWOFTYP smallint ,
MXWROFAM numeric (19,5) ,
Revalue_Customer tinyint ,
Post_Results_To smallint ,
ORDERFULFILLDEFAULT tinyint ,
INCLUDEINDP tinyint ,
CRCARDID char (15) ,
CRCRDNUM char (20) ,
CCRDXPDT datetime ,
BANKNAME char (30) ,
BNKBRNCH char (20) ,
USERLANG smallint ,
TAXEXMT1 char (25) ,
TAXEXMT2 char (25) ,
TXRGNNUM char (25) ,
CURNCYID char (15) ,
RATETPID char (15) ,
STMTCYCL smallint ,
KPCALHST tinyint ,
KPERHIST tinyint ,
KPTRXHST tinyint ,
KPDSTHST tinyint ,
Send_Email_Statements tinyint ,
ToEmail_Recipient char (80) ,
CcEmail_Recipient char (80) ,
BccEmail_Recipient char (80) ,
CHEKBKID char (15) ,
DEFCACTY smallint ,
RMCSHACTNUMST varchar (75) ,
RMARACTNUMST varchar (75) ,
RMSLSACTNUMST varchar (75) ,
RMCOSACTNUMST varchar (75) ,
RMIVACTNUMST varchar (75) ,
RMTAKACTNUMST varchar (75) ,
RMAVACTNUMST varchar (75) ,
RMFCGACTNUMST varchar (75) ,
RMWRACTNUMST varchar (75) ,
RMSORACTNUMST varchar (75) ,
RMOvrpymtWrtoffACTNUMST varchar (75) ,
GPSFOINTEGRATIONID char (30) default '' ,
INTEGRATIONSOURCE smallint default 0,
INTEGRATIONID char (30) default '',
UseCustomerClass tinyint ,
CreateAddress tinyint ,
UpdateIfExists tinyint default 0,
RequesterTrx smallint default 0,
USRDEFND1 char (50) ,
USRDEFND2 char (50) ,
USRDEFND3 char (50) ,
USRDEFND4 varchar (8000) ,
USRDEFND5 varchar (8000)
) ON [PRIMARY]
GO
ALTER TABLE DDtaUpdateCreateCustomerRcd ADD CONSTRAINT
PK_taUpdateCreateCustomerRcd PRIMARY KEY CLUSTERED
(
RowID
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO