SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
ALTER PROCEDURE dbo.taRMTransactionTaxInsert
@I_vCUSTNMBR CHAR(15),
@I_vDOCNUMBR CHAR(20),
@I_vRMDTYPAL SMALLINT,
@I_vBACHNUMB CHAR(15),
@I_vTAXDTLID CHAR(15),
@I_vTAXAMNT NUMERIC(19, 5),
@I_vSTAXAMNT NUMERIC(19, 5),
@I_vFRTTXAMT NUMERIC(19, 5) = 0,
@I_vMSCTXAMT NUMERIC(19, 5) = 0,
@I_vTAXDTSLS NUMERIC(19, 5),
@I_vSEQNUMBR INT = 0,
@I_vACTINDX INT = 0,
@I_vACTNUMST VARCHAR(75) = '',
@I_vTDTTXSLS NUMERIC(19, 5) = NULL,
@I_vRequesterTrx SMALLINT = 0,
@I_vUSRDEFND1 CHAR(50) = '',
@I_vUSRDEFND2 CHAR(50) = '',
@I_vUSRDEFND3 CHAR(50) = '',
@I_vUSRDEFND4 VARCHAR(8000) = '',
@I_vUSRDEFND5 VARCHAR(8000) = '',
@O_iErrorState INT OUTPUT,
@oErrString VARCHAR(255) OUTPUT
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SET NOCOUNT ON;
DECLARE @CURNCYID CHAR(15),
@CURRNIDX SMALLINT,
@TRXSORCE CHAR(13),
@iStatus INT,
@iError INT,
@iCustomState INT,
@iCustomErrString VARCHAR(255),
@iStatement INT,
@iAddCodeErrState INT,
@O_oErrorState INT,
@ISMCREG TINYINT,
@TXDTLBSE TINYINT;
SELECT @O_oErrorState = 0,
@TRXSORCE = '',
@CURNCYID = '',
@CURRNIDX = 0,
@O_iErrorState = 0,
@iStatement = 0,
@iStatus = 0,
@ISMCREG = 0,
@TXDTLBSE = 0;
IF (@oErrString IS NULL)
BEGIN
SELECT @oErrString = '';
END;
EXEC @iStatus = taRMTransactionTaxInsertPre @I_vCUSTNMBR OUTPUT,
@I_vDOCNUMBR OUTPUT,
@I_vRMDTYPAL OUTPUT,
@I_vBACHNUMB OUTPUT,
@I_vTAXDTLID OUTPUT,
@I_vTAXAMNT OUTPUT,
@I_vSTAXAMNT OUTPUT,
@I_vFRTTXAMT OUTPUT,
@I_vMSCTXAMT OUTPUT,
@I_vTAXDTSLS OUTPUT,
@I_vSEQNUMBR OUTPUT,
@I_vACTINDX OUTPUT,
@I_vACTNUMST OUTPUT,
@I_vTDTTXSLS OUTPUT,
@I_vRequesterTrx OUTPUT,
@I_vUSRDEFND1 OUTPUT,
@I_vUSRDEFND2 OUTPUT,
@I_vUSRDEFND3 OUTPUT,
@I_vUSRDEFND4 OUTPUT,
@I_vUSRDEFND5 OUTPUT,
@O_iErrorState = @iCustomState OUTPUT,
@oErrString = @iCustomErrString OUTPUT;
SELECT @iError = @@error;
IF @iStatus = 0
AND @iError <> 0
BEGIN
SELECT @iStatus = @iError;
END;
IF (@iStatus <> 0)
OR (@iCustomState <> 0)
BEGIN
SELECT @oErrString = RTRIM(@oErrString) + ' ' + LTRIM(RTRIM(@iCustomErrString));
SELECT @O_iErrorState = 291;
EXEC @iStatus = taUpdateString @O_iErrorState,
@oErrString,
@oErrString OUTPUT,
@O_oErrorState OUTPUT;
RETURN (@O_iErrorState);
END;
IF (
@I_vCUSTNMBR IS NULL
OR @I_vDOCNUMBR IS NULL
OR @I_vRMDTYPAL IS NULL
OR @I_vBACHNUMB IS NULL
OR @I_vTAXDTLID IS NULL
OR @I_vTAXAMNT IS NULL
OR @I_vSTAXAMNT IS NULL
OR @I_vFRTTXAMT IS NULL
OR @I_vMSCTXAMT IS NULL
OR @I_vTAXDTSLS IS NULL
OR @I_vSEQNUMBR IS NULL
OR @I_vACTINDX IS NULL
OR @I_vACTNUMST IS NULL
OR @I_vRequesterTrx IS NULL
)
BEGIN
SELECT @O_iErrorState = 292;
EXEC @iStatus = taUpdateString @O_iErrorState,
@oErrString,
@oErrString OUTPUT,
@O_oErrorState OUTPUT;
RETURN (@O_iErrorState);
END;
IF (@I_vDOCNUMBR = '' AND @I_vTAXDTLID = '')
BEGIN
SELECT @O_iErrorState = 293;
EXEC @iStatus = taUpdateString @O_iErrorState,
@oErrString,
@oErrString OUTPUT,
@O_oErrorState OUTPUT;
RETURN (@O_iErrorState);
END;
SELECT @I_vCUSTNMBR = UPPER(@I_vCUSTNMBR),
@I_vDOCNUMBR = UPPER(@I_vDOCNUMBR),
@I_vBACHNUMB = UPPER(@I_vBACHNUMB),
@I_vTAXDTLID = UPPER(@I_vTAXDTLID);
IF @I_vRMDTYPAL NOT IN ( 1, 3, 4, 5, 6, 7, 8 )
BEGIN
SELECT @O_iErrorState = 294;
EXEC @iStatus = taUpdateString @O_iErrorState,
@oErrString,
@oErrString OUTPUT,
@O_oErrorState OUTPUT;
RETURN (@O_iErrorState);
END;
IF (@I_vBACHNUMB = '')
BEGIN
SELECT @O_iErrorState = 259;
EXEC @iStatus = taUpdateString @O_iErrorState,
@oErrString,
@oErrString OUTPUT,
@O_oErrorState OUTPUT;
RETURN (@O_iErrorState);
END;
IF NOT EXISTS
(
SELECT 1
FROM TX00201 (NOLOCK)
WHERE TAXDTLID = @I_vTAXDTLID
)
BEGIN
SELECT @O_iErrorState = 295;
EXEC @iStatus = taUpdateString @O_iErrorState,
@oErrString,
@oErrString OUTPUT,
@O_oErrorState OUTPUT;
RETURN (@O_iErrorState);
END;
IF (@I_vTAXDTLID <> '')
BEGIN
SELECT @TXDTLBSE = TXDTLBSE
FROM TX00201 (NOLOCK)
WHERE TAXDTLID = @I_vTAXDTLID;
END;
IF (@I_vRequesterTrx < 0 OR @I_vRequesterTrx > 1)
BEGIN
SELECT @O_iErrorState = 3724;
EXEC @iStatus = taUpdateString @O_iErrorState,
@oErrString,
@oErrString OUTPUT,
@iAddCodeErrState OUTPUT;
END;
IF (@I_vACTNUMST <> '')
BEGIN
SELECT @I_vACTINDX = ACTINDX
FROM GL00105 (NOLOCK)
WHERE ACTNUMST = @I_vACTNUMST;
SELECT @I_vACTINDX = ISNULL(@I_vACTINDX, 0);
IF (@I_vACTINDX = 0)
BEGIN
SELECT @O_iErrorState = 716;
EXEC @iStatus = taUpdateString @O_iErrorState,
@oErrString,
@oErrString OUTPUT,
@O_oErrorState OUTPUT;
END;
END;
ELSE
BEGIN
IF (@I_vACTINDX <> 0)
BEGIN
SELECT @I_vACTINDX = ACTINDX
FROM GL00105 (NOLOCK)
WHERE ACTINDX = @I_vACTINDX;
SELECT @I_vACTINDX = ISNULL(@I_vACTINDX, 0);
IF (@I_vACTINDX = 0)
BEGIN
SELECT @O_iErrorState = 455;
EXEC @iStatus = taUpdateString @O_iErrorState,
@oErrString,
@oErrString OUTPUT,
@O_oErrorState OUTPUT;
END;
END;
ELSE
BEGIN
SELECT @I_vACTINDX = ISNULL(ACTINDX, 0)
FROM TX00201 (NOLOCK)
WHERE TAXDTLID = @I_vTAXDTLID;
IF (@I_vACTINDX = 0)
BEGIN
SELECT @O_iErrorState = 296;
EXEC @iStatus = taUpdateString @O_iErrorState,
@oErrString,
@oErrString OUTPUT,
@O_oErrorState OUTPUT;
RETURN (@O_iErrorState);
END;
END;
END;
IF (@I_vSEQNUMBR = 0)
BEGIN
SELECT @I_vSEQNUMBR = MAX(SEQNUMBR) + 16384
FROM RM10601 (NOLOCK)
WHERE TAXDTLID = @I_vTAXDTLID
AND DOCNUMBR = @I_vDOCNUMBR
AND RMDTYPAL = @I_vRMDTYPAL;
SELECT @I_vSEQNUMBR = ISNULL(@I_vSEQNUMBR, 16384);
END;
IF EXISTS
(
SELECT 1
FROM RM10601 (NOLOCK)
WHERE DOCNUMBR = @I_vDOCNUMBR
AND SEQNUMBR = @I_vSEQNUMBR
AND RMDTYPAL = @I_vRMDTYPAL
AND TRXSORCE = @TRXSORCE
AND TAXDTLID = @I_vTAXDTLID
)
BEGIN
SELECT @O_iErrorState = 262;
EXEC @iStatus = taUpdateString @O_iErrorState,
@oErrString,
@oErrString OUTPUT,
@O_oErrorState OUTPUT;
RETURN (@O_iErrorState);
END;
IF (@I_vTDTTXSLS > @I_vTAXDTSLS)
BEGIN
SELECT @O_iErrorState = 5443;
EXEC @iStatus = taUpdateString @O_iErrorState,
@oErrString,
@oErrString OUTPUT,
@O_oErrorState OUTPUT;
RETURN (@O_iErrorState);
END;
IF (@I_vTDTTXSLS IS NOT NULL)
AND (@I_vTDTTXSLS < 0)
BEGIN
SELECT @O_iErrorState = 5444;
EXEC @iStatus = taUpdateString @O_iErrorState,
@oErrString,
@oErrString OUTPUT,
@O_oErrorState OUTPUT;
RETURN (@O_iErrorState);
END;
SELECT @CURNCYID = ISNULL(CURNCYID, '')
FROM RM00101 (NOLOCK)
WHERE CUSTNMBR = @I_vCUSTNMBR;
IF (@CURNCYID <> '')
BEGIN
SELECT @CURRNIDX = ISNULL(CURRNIDX, 0)
FROM DYNAMICS..MC40200 (NOLOCK)
WHERE CURNCYID = @CURNCYID;
END;
ELSE
BEGIN
SELECT @CURNCYID = ISNULL(FUNLCURR, ''),
@CURRNIDX = ISNULL(FUNCRIDX, 0)
FROM MC40000 (NOLOCK);
END;
IF (EXISTS (SELECT 1 FROM CM00100 (NOLOCK) WHERE CURNCYID <> ''))
BEGIN
SELECT @ISMCREG = 1;
END;
IF (@I_vRequesterTrx = 0)
BEGIN
EXEC @iStatus = eConnectOutVerify @I_vDOCTYPE = 'Receivables_Transaction',
@I_vINDEX1 = @I_vRMDTYPAL,
@I_vINDEX2 = @I_vDOCNUMBR,
@I_vINDEX3 = '',
@I_vINDEX4 = '',
@I_vINDEX5 = '',
@I_vINDEX6 = '',
@I_vINDEX7 = '',
@I_vINDEX8 = '',
@I_vINDEX9 = '',
@I_vINDEX10 = '',
@I_vINDEX11 = '',
@I_vINDEX12 = '',
@I_vINDEX13 = '',
@I_vINDEX14 = '',
@I_vINDEX15 = '',
@I_vDelete = 0,
@O_iErrorState = @iCustomState OUTPUT;
SELECT @iError = @@error;
IF @iStatus = 0
AND @iError <> 0
BEGIN
SELECT @iStatus = @iError;
END;
IF (@iStatus <> 0)
OR (@iCustomState <> 0)
BEGIN
SELECT @O_iErrorState = 2778;
EXEC @iStatus = taUpdateString @O_iErrorState,
@oErrString,
@oErrString OUTPUT,
@O_oErrorState OUTPUT;
RETURN (@O_iErrorState);
END;
END;
IF (@O_iErrorState = 0)
BEGIN
IF NOT EXISTS
(
SELECT 1
FROM RM10601 (NOLOCK)
WHERE RMDTYPAL = @I_vRMDTYPAL
AND DOCNUMBR = @I_vDOCNUMBR
AND TRXSORCE = @TRXSORCE
AND TAXDTLID = @I_vTAXDTLID
)
BEGIN
INSERT RM10601
(
BACHNUMB,
RMDTYPAL,
DOCNUMBR,
CUSTNMBR,
TAXDTLID,
TRXSORCE,
ACTINDX,
BKOUTTAX,
TAXAMNT,
ORTAXAMT,
STAXAMNT,
ORSLSTAX,
FRTTXAMT,
ORFRTTAX,
MSCTXAMT,
ORMSCTAX,
TAXDTSLS,
ORTOTSLS,
TDTTXSLS,
ORTXSLS,
POSTED,
SEQNUMBR,
CURRNIDX,
TXDTLPCTAMT
)
SELECT @I_vBACHNUMB,
@I_vRMDTYPAL,
@I_vDOCNUMBR,
@I_vCUSTNMBR,
@I_vTAXDTLID,
@TRXSORCE,
@I_vACTINDX,
CASE
WHEN @TXDTLBSE = 1 THEN
1
ELSE
0
END,
@I_vTAXAMNT,
@I_vTAXAMNT,
CASE
WHEN @I_vSTAXAMNT = 0 THEN
@I_vTAXAMNT
ELSE
@I_vSTAXAMNT
END,
CASE
WHEN @I_vSTAXAMNT = 0 THEN
@I_vTAXAMNT
ELSE
@I_vSTAXAMNT
END,
@I_vFRTTXAMT,
@I_vFRTTXAMT,
@I_vMSCTXAMT,
@I_vMSCTXAMT,
@I_vTAXDTSLS,
@I_vTAXDTSLS,
CASE
WHEN @I_vTDTTXSLS IS NULL THEN
@I_vTAXDTSLS
ELSE
@I_vTDTTXSLS
END,
CASE
WHEN @I_vTDTTXSLS IS NULL THEN
@I_vTAXDTSLS
ELSE
@I_vTDTTXSLS
END,
0,
@I_vSEQNUMBR,
CASE
WHEN @ISMCREG = 1 THEN
@CURRNIDX
ELSE
0
END,
ISNULL(
(
SELECT CASE TX00201.TXDTLBSE
WHEN 2 THEN
ISNULL(TX00201.TXDTLAMT, 0)
ELSE
ISNULL(TX00201.TXDTLPCT, 0)
END
FROM TX00201
WHERE TX00201.TAXDTLID = @I_vTAXDTLID
),
0
);
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 297;
SELECT @iStatement = 1;
EXEC @iStatus = taUpdateString @O_iErrorState,
@oErrString,
@oErrString OUTPUT,
@O_oErrorState OUTPUT;
RETURN (@O_iErrorState);
END;
END;
ELSE
BEGIN
SELECT @O_iErrorState = 7148;
EXEC @iStatus = taUpdateString @O_iErrorState,
@oErrString,
@oErrString OUTPUT,
@iAddCodeErrState OUTPUT;
END;
END;
IF @O_iErrorState <> 0
RETURN (@O_iErrorState);
EXEC @iStatus = taRMTransactionTaxInsertPost @I_vCUSTNMBR,
@I_vDOCNUMBR,
@I_vRMDTYPAL,
@I_vBACHNUMB,
@I_vTAXDTLID,
@I_vTAXAMNT,
@I_vSTAXAMNT,
@I_vFRTTXAMT,
@I_vMSCTXAMT,
@I_vTAXDTSLS,
@I_vSEQNUMBR,
@I_vACTINDX,
@I_vACTNUMST,
@I_vTDTTXSLS,
@I_vRequesterTrx,
@I_vUSRDEFND1,
@I_vUSRDEFND2,
@I_vUSRDEFND3,
@I_vUSRDEFND4,
@I_vUSRDEFND5,
@O_iErrorState = @iCustomState OUTPUT,
@oErrString = @iCustomErrString OUTPUT;
SELECT @iError = @@error;
IF @iStatus = 0
AND @iError <> 0
BEGIN
SELECT @iStatus = @iError;
END;
IF (@iStatus <> 0)
OR (@iCustomState <> 0)
BEGIN
SELECT @oErrString = RTRIM(@oErrString) + ' ' + LTRIM(RTRIM(@iCustomErrString));
SELECT @O_iErrorState = 298;
EXEC @iStatus = taUpdateString @O_iErrorState,
@oErrString,
@oErrString OUTPUT,
@O_oErrorState OUTPUT;
RETURN (@O_iErrorState);
END;
IF (@I_vRequesterTrx = 0)
BEGIN
EXEC @iStatus = eConnectOutVerify @I_vDOCTYPE = 'Receivables_Transaction',
@I_vINDEX1 = @I_vRMDTYPAL,
@I_vINDEX2 = @I_vDOCNUMBR,
@I_vINDEX3 = '',
@I_vINDEX4 = '',
@I_vINDEX5 = '',
@I_vINDEX6 = '',
@I_vINDEX7 = '',
@I_vINDEX8 = '',
@I_vINDEX9 = '',
@I_vINDEX10 = '',
@I_vINDEX11 = '',
@I_vINDEX12 = '',
@I_vINDEX13 = '',
@I_vINDEX14 = '',
@I_vINDEX15 = '',
@I_vDelete = 1,
@O_iErrorState = @iCustomState OUTPUT;
SELECT @iError = @@error;
IF @iStatus = 0
AND @iError <> 0
BEGIN
SELECT @iStatus = @iError;
END;
IF (@iStatus <> 0)
OR (@iCustomState <> 0)
BEGIN
SELECT @O_iErrorState = 2777;
EXEC @iStatus = taUpdateString @O_iErrorState,
@oErrString,
@oErrString OUTPUT,
@O_oErrorState OUTPUT;
RETURN (@O_iErrorState);
END;
END;
RETURN (@O_iErrorState);