CREATE PROCEDURE dbo.taCreateCustomerItems
@I_vITEMNMBR CHAR(30),
@I_vCUSTNMBR CHAR(15),
@I_vCUSTITEMNMBR CHAR(30) = '',
@I_vCUSTITEMDESC CHAR(50) = '',
@I_vCUSTITEMSHORNAME CHAR(15) = '',
@I_vCUSTITEMGENERICDESC CHAR(10) = '',
@I_vUSERDEF1 CHAR(20) = '',
@I_vUSERDEF2 CHAR(20) = '',
@I_vUSRDEF03 CHAR(20) = '',
@I_vUSRDEF04 CHAR(20) = '',
@I_vUSRDEF05 CHAR(20) = '',
@I_vSUBITEMNMBR CHAR(30) = '',
@I_vSTRTDATE DATETIME = '',
@I_vENDDATE DATETIME = '',
@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 @SUBITEMDESC CHAR(50),
@NOTEINDX DECIMAL(19, 5),
@CMPANYID SMALLINT,
@iGetNextNoteIdxErrState INT,
@iAddCodeErrState INT,
@iStatus INT,
@iCustomState INT,
@iCustomErrString VARCHAR(255),
@O_oErrorState INT,
@iError INT;
SELECT @SUBITEMDESC = '',
@NOTEINDX = 0,
@iStatus = 0,
@O_iErrorState = 0;
IF (@oErrString IS NULL)
BEGIN
SELECT @oErrString = '';
END;
EXEC @iStatus = taCreateCustomerItemsPre @I_vITEMNMBR OUTPUT,
@I_vCUSTNMBR OUTPUT,
@I_vCUSTITEMNMBR OUTPUT,
@I_vCUSTITEMDESC OUTPUT,
@I_vCUSTITEMSHORNAME OUTPUT,
@I_vCUSTITEMGENERICDESC OUTPUT,
@I_vUSERDEF1 OUTPUT,
@I_vUSERDEF2 OUTPUT,
@I_vUSRDEF03 OUTPUT,
@I_vUSRDEF04 OUTPUT,
@I_vUSRDEF05 OUTPUT,
@I_vSUBITEMNMBR OUTPUT,
@I_vSTRTDATE OUTPUT,
@I_vENDDATE 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) OR (@iCustomState <> 0) OR (@iError <> 0))
BEGIN
SELECT @oErrString = RTRIM(@oErrString) + ' ' + LTRIM(RTRIM(@iCustomErrString));
SELECT @O_iErrorState = 3651;
EXEC @iStatus = taUpdateString @O_iErrorState,
@oErrString,
@oErrString OUTPUT,
@O_oErrorState OUTPUT;
RETURN (@O_iErrorState);
END;
IF (
@I_vITEMNMBR IS NULL
OR @I_vCUSTNMBR IS NULL
OR @I_vCUSTITEMNMBR IS NULL
OR @I_vCUSTITEMDESC IS NULL
OR @I_vCUSTITEMSHORNAME IS NULL
OR @I_vCUSTITEMGENERICDESC IS NULL
OR @I_vUSERDEF1 IS NULL
OR @I_vUSERDEF2 IS NULL
OR @I_vUSRDEF03 IS NULL
OR @I_vUSRDEF04 IS NULL
OR @I_vUSRDEF05 IS NULL
OR @I_vSUBITEMNMBR IS NULL
OR @I_vSTRTDATE IS NULL
OR @I_vENDDATE IS NULL
OR @I_vRequesterTrx IS NULL
OR @I_vUSRDEFND1 IS NULL
OR @I_vUSRDEFND2 IS NULL
OR @I_vUSRDEFND3 IS NULL
OR @I_vUSRDEFND4 IS NULL
OR @I_vUSRDEFND5 IS NULL
)
BEGIN
SELECT @O_iErrorState = 3652;
EXEC @iStatus = taUpdateString @O_iErrorState,
@oErrString,
@oErrString OUTPUT,
@O_oErrorState OUTPUT;
RETURN (@O_iErrorState);
END;
SELECT @I_vITEMNMBR = UPPER(@I_vITEMNMBR),
@I_vCUSTNMBR = UPPER(@I_vCUSTNMBR),
@I_vCUSTITEMNMBR = UPPER(@I_vCUSTITEMNMBR),
@I_vSUBITEMNMBR = UPPER(@I_vSUBITEMNMBR);
SELECT @CMPANYID = CMPANYID
FROM DYNAMICS..SY01500 (NOLOCK)
WHERE INTERID = DB_NAME();
EXEC @iStatus = DYNAMICS..tasmGetNextNoteIndex @I_sCompanyID = @CMPANYID,
@I_iSQLSessionID = 0,
@I_noteincrement = 1,
@O_mNoteIndex = @NOTEINDX OUTPUT,
@O_iErrorState = @iGetNextNoteIdxErrState OUTPUT;
SELECT @iError = @@error;
IF @iStatus = 0
AND @iError <> 0
BEGIN
SELECT @iStatus = @iError;
END;
IF (@iStatus <> 0)
OR (@iGetNextNoteIdxErrState <> 0)
BEGIN
IF (@iGetNextNoteIdxErrState <> 0)
BEGIN
EXEC @iStatus = taUpdateString @iGetNextNoteIdxErrState,
@oErrString,
@oErrString OUTPUT,
@O_oErrorState OUTPUT;
END;
SELECT @O_iErrorState = 5344;
EXEC @iStatus = taUpdateString @O_iErrorState,
@oErrString,
@oErrString OUTPUT,
@O_oErrorState OUTPUT;
END;
IF (@I_vITEMNMBR = '')
BEGIN
SELECT @O_iErrorState = 3653;
EXEC @iStatus = taUpdateString @O_iErrorState,
@oErrString,
@oErrString OUTPUT,
@iAddCodeErrState OUTPUT;
END;
IF (@I_vITEMNMBR <> '')
BEGIN
IF (NOT EXISTS
(
SELECT 1
FROM IV00101 (NOLOCK)
WHERE ITEMNMBR = @I_vITEMNMBR
)
)
BEGIN
SELECT @O_iErrorState = 3654;
EXEC @iStatus = taUpdateString @O_iErrorState,
@oErrString,
@oErrString OUTPUT,
@O_oErrorState OUTPUT;
END;
END;
IF (@I_vSUBITEMNMBR <> '')
BEGIN
IF (NOT EXISTS
(
SELECT 1
FROM IV00101 (NOLOCK)
WHERE ITEMNMBR = @I_vSUBITEMNMBR
)
)
BEGIN
SELECT @O_iErrorState = 5316;
EXEC @iStatus = taUpdateString @O_iErrorState,
@oErrString,
@oErrString OUTPUT,
@O_oErrorState OUTPUT;
END;
END;
SELECT @SUBITEMDESC = ITEMDESC
FROM IV00101 (NOLOCK)
WHERE ITEMNMBR = @I_vSUBITEMNMBR;
IF (@I_vCUSTITEMDESC = '')
BEGIN
SELECT @I_vCUSTITEMDESC = ITEMDESC
FROM IV00101 (NOLOCK)
WHERE ITEMNMBR = @I_vITEMNMBR;
END;
IF (@I_vITEMNMBR = @I_vSUBITEMNMBR)
BEGIN
SELECT @O_iErrorState = 5317;
EXEC @iStatus = taUpdateString @O_iErrorState,
@oErrString,
@oErrString OUTPUT,
@iAddCodeErrState OUTPUT;
END;
IF (@I_vCUSTNMBR = '')
BEGIN
SELECT @O_iErrorState = 3656;
EXEC @iStatus = taUpdateString @O_iErrorState,
@oErrString,
@oErrString OUTPUT,
@iAddCodeErrState OUTPUT;
END;
IF (@I_vCUSTNMBR <> '')
BEGIN
IF (NOT EXISTS
(
SELECT 1
FROM RM00101 (NOLOCK)
WHERE CUSTNMBR = @I_vCUSTNMBR
)
)
BEGIN
SELECT @O_iErrorState = 3657;
EXEC @iStatus = taUpdateString @O_iErrorState,
@oErrString,
@oErrString OUTPUT,
@O_oErrorState OUTPUT;
END;
END;
IF (@I_vENDDATE < @I_vSTRTDATE)
BEGIN
SELECT @O_iErrorState = 5318;
EXEC @iStatus = taUpdateString @O_iErrorState,
@oErrString,
@oErrString OUTPUT,
@iAddCodeErrState OUTPUT;
END;
IF (@I_vCUSTITEMNMBR <> '')
BEGIN
IF (EXISTS
(
SELECT 1
FROM IV00101 (NOLOCK)
WHERE ITEMNMBR = @I_vCUSTITEMNMBR
)
)
BEGIN
SELECT @O_iErrorState = 5475;
EXEC @iStatus = taUpdateString @O_iErrorState,
@oErrString,
@oErrString OUTPUT,
@O_oErrorState OUTPUT;
END;
END;
IF (@I_vRequesterTrx < 0 OR @I_vRequesterTrx > 1)
BEGIN
SELECT @O_iErrorState = 3658;
EXEC @iStatus = taUpdateString @O_iErrorState,
@oErrString,
@oErrString OUTPUT,
@iAddCodeErrState OUTPUT;
END;
IF (@I_vSUBITEMNMBR <> '')
BEGIN
IF NOT EXISTS
(
SELECT 1
FROM SOP00300 (NOLOCK)
WHERE ITEMNMBR = @I_vITEMNMBR
AND CUSTNMBR = @I_vCUSTNMBR
)
BEGIN
INSERT INTO SOP00300
(
CUSTNMBR,
ITEMNMBR,
SEQNUMBR,
SUBITEMNMBR,
SUBITEMDESC,
STRTDATE,
ENDDATE
)
SELECT @I_vCUSTNMBR,
@I_vITEMNMBR,
1,
@I_vSUBITEMNMBR,
@SUBITEMDESC,
@I_vSTRTDATE,
@I_vENDDATE;
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 5319;
EXEC @iStatus = taUpdateString @O_iErrorState,
@oErrString,
@oErrString OUTPUT,
@O_oErrorState OUTPUT;
RETURN (@O_iErrorState);
END;
END;
ELSE
BEGIN
SELECT @O_iErrorState = 5345;
EXEC @iStatus = taUpdateString @O_iErrorState,
@oErrString,
@oErrString OUTPUT,
@iAddCodeErrState OUTPUT;
END;
END;
IF (@O_iErrorState = 0)
BEGIN
IF NOT EXISTS
(
SELECT 1
FROM SOP60300 (NOLOCK)
WHERE ITEMNMBR = @I_vITEMNMBR
AND CUSTNMBR = @I_vCUSTNMBR
)
BEGIN
INSERT INTO SOP60300
(
ITEMNMBR,
CUSTNMBR,
CUSTITEMNMBR,
CUSTITEMDESC,
CUSTITEMSHORNAME,
CUSTITEMGENERICDESC,
USERDEF1,
USERDEF2,
USRDEF03,
USRDEF04,
USRDEF05,
USERDEFKEY1,
USERDEFKEY2,
USERDEFKEY3,
USERDEFKEY4,
NOTEINDX
)
SELECT @I_vITEMNMBR,
@I_vCUSTNMBR,
@I_vCUSTITEMNMBR,
@I_vCUSTITEMDESC,
@I_vCUSTITEMSHORNAME,
@I_vCUSTITEMGENERICDESC,
@I_vUSERDEF1,
@I_vUSERDEF2,
@I_vUSRDEF03,
@I_vUSRDEF04,
@I_vUSRDEF05,
'',
'',
'',
'',
@NOTEINDX;
IF @@error <> 0
BEGIN
SELECT @O_iErrorState = 3659;
EXEC @iStatus = taUpdateString @O_iErrorState,
@oErrString,
@oErrString OUTPUT,
@O_oErrorState OUTPUT;
RETURN (@O_iErrorState);
END;
END;
ELSE
BEGIN
SELECT @O_iErrorState = 3660;
EXEC @iStatus = taUpdateString @O_iErrorState,
@oErrString,
@oErrString OUTPUT,
@iAddCodeErrState OUTPUT;
END;
END;
IF @O_iErrorState <> 0
RETURN (@O_iErrorState);
EXEC @iStatus = taCreateCustomerItemsPost @I_vITEMNMBR,
@I_vCUSTNMBR,
@I_vCUSTITEMNMBR,
@I_vCUSTITEMDESC,
@I_vCUSTITEMSHORNAME,
@I_vCUSTITEMGENERICDESC,
@I_vUSERDEF1,
@I_vUSERDEF2,
@I_vUSRDEF03,
@I_vUSRDEF04,
@I_vUSRDEF05,
@I_vSUBITEMNMBR,
@I_vSTRTDATE,
@I_vENDDATE,
@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) OR (@iCustomState <> 0) OR (@iError <> 0))
BEGIN
SELECT @oErrString = RTRIM(@oErrString) + ' ' + LTRIM(RTRIM(@iCustomErrString));
SELECT @O_iErrorState = 3661;
EXEC @iStatus = taUpdateString @O_iErrorState,
@oErrString,
@oErrString OUTPUT,
@O_oErrorState OUTPUT;
RETURN (@O_iErrorState);
END;
RETURN (@O_iErrorState);
GO