/* Update-Schritte 5.11.20: 1. Dashboards Tabelle und Prozeduren entfernen 2. Reports Tabelle und Prozeduren entfernen */ GO DROP TABLE IF EXISTS system.tDashboards; DROP PROCEDURE IF EXISTS system.spCOPY_Dashboard; DROP PROCEDURE IF EXISTS system.spDELETE_Dashboard; DROP PROCEDURE IF EXISTS system.spGET_Dashboards; DROP PROCEDURE IF EXISTS system.spPOST_Dashboard; GO DROP TABLE IF EXISTS system.tReports; DROP PROCEDURE IF EXISTS system.spCOPY_Report; DROP PROCEDURE IF EXISTS system.spDELETE_Report; DROP PROCEDURE IF EXISTS system.spGET_Reports; DROP PROCEDURE IF EXISTS system.spPOST_Report; GO DROP PROCEDURE IF EXISTS agent.pCreateJSONView; GO /* Procedure to create a helper table for the agent to resolve a JSON Column to single columns - the JSON Column must be named "CustomValuesJSON" - the resulting table will be named agent.vJSON_[schema]_[table] Saxess Software GmbH Last modified: 09/2023 for OCT 5.10 Testcall Procedure DECLARE @RC INT; EXEC @RC = agent.pCreateJSONView @Username = 'SQL' ,@Schema = 'global' ,@Table = 'tCompanies' ,@UseRowKey = 1 PRINT @RC SELECT * FROM agent.vJSON_global_tCompanies Testcall Documentation SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'agent', 'PROCEDURE', 'pCreateJSONView',NULL,NULL) UNION ALL SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'agent', 'PROCEDURE', 'pCreateJSONView','PARAMETER',NULL) */ CREATE PROCEDURE agent.pCreateJSONView @Username NVARCHAR(255) , @Schema NVARCHAR(50) , @Table NVARCHAR(50) , @UseRowKey INT = 0 AS BEGIN BEGIN TRY -- Logging DECLARE @TimestampCall DATETIME = GETUTCDATE(); DECLARE @ProcedureName NVARCHAR(255) = OBJECT_SCHEMA_NAME(@@PROCID) + N'.' + OBJECT_NAME(@@PROCID); DECLARE @AffectedRows INT = 0; DECLARE @ResultCode INT = 501; DECLARE @Comment NVARCHAR(4000) = N''; DECLARE @ParameterString NVARCHAR(MAX) = N''; DECLARE @TransactUsername NVARCHAR(255) = N''; EXEC system.spGET_ParameterString @ParameterString OUTPUT, 4, @Username, @Schema, @Table, @UseRowKey; EXEC system.spGET_TransactUsername @TransactUsername OUTPUT, @Username; -- Variables DECLARE @KeyCol NVARCHAR(50); DECLARE @ColName NVARCHAR(50); DECLARE @ColType INT; DECLARE @SQLDrop NVARCHAR(MAX); DECLARE @SQLHeader NVARCHAR(MAX); DECLARE @SQL NVARCHAR(MAX); DECLARE @SQLFooter NVARCHAR(MAX); DECLARE @ErrorRow NVARCHAR(MAX); -- Create temp table with JSON columns DECLARE @JSONColsTbl NVARCHAR(MAX) = N'[##' + CAST(NEWID() AS NVARCHAR(MAX)) + N']'; -- Check for invalid JSON SET @SQL = 'SELECT TOP 1 @JSONErrorRow = CAST(RowKey AS NVARCHAR(20)) + '': '' + CustomValuesJSON FROM ' + @Schema + '.' + @Table + ' WHERE ISJSON(CustomValuesJSON) = 0 AND CustomValuesJSON <> ''''' EXEC sp_executesql @SQL, N'@JSONErrorRow NVARCHAR(MAX) OUT', @ErrorRow OUT; IF @ErrorRow IS NOT NULL BEGIN SET @Comment = 'Invalid JSON value in table ' + @Schema + '.' + @Table + ' for RowKey ' + @ErrorRow EXEC system.spSEND_Message 'ERROR', @Comment END EXEC ('CREATE TABLE ' + @JSONColsTbl + ' (ColName NVARCHAR(50), ColType int)'); EXEC ('INSERT INTO ' + @JSONColsTbl + ' SELECT DISTINCT b.[Key], b.Type FROM ' + @Schema + '.' + @Table + ' a CROSS APPLY OPENJSON(a.CustomValuesJSON) b'); EXEC ('SELECT 1 FROM ' + @JSONColsTbl); -- Get key columns SELECT @KeyCol = ColName FROM system.fGetColumns(@Schema, @Table) WHERE ( @UseRowKey = 0 AND IsCalculatedKey = 1 ) OR ( @UseRowKey = 1 AND ColName = 'RowKey' ); -- Define script SET @SQLDrop = N'DROP VIEW IF EXISTS agent.vJSON_' + @Schema + N'_' + @Table; SET @SQLHeader = N'CREATE VIEW agent.vJSON_' + @Schema + N'_' + @Table + N' AS SELECT a.[' + @KeyCol + N']'; SET @SQLFooter = N' FROM ' + @Schema + N'.' + @Table + N' a '; SET @SQL = N''; EXEC ('SELECT 1 FROM ' + @JSONColsTbl); IF @@ROWCOUNT > 0 BEGIN SET @SQLFooter = @SQLFooter + N' OUTER APPLY OPENJSON (a.CustomValuesJSON) WITH ('; -- iterate through JSON columns and add them to the script EXEC ('DECLARE MyCursor CURSOR FOR' + ' SELECT ColName, ColType' + ' FROM ' + @JSONColsTbl + ' WHERE ColType IN (1,2,3) ORDER BY 1'); OPEN MyCursor; FETCH MyCursor INTO @ColName , @ColType; WHILE @@FETCH_STATUS = 0 BEGIN SET @SQL = @SQL + N',ISNULL([' + @ColName + N'],'''') ' + CASE @ColType WHEN 1 THEN 'COLLATE DATABASE_DEFAULT' ELSE '' END + N' AS [' + @ColName + N'_INT]'; SET @SQLFooter = @SQLFooter + N' [' + @ColName + N'] ' + CASE @ColType WHEN 1 THEN 'nvarchar(255)' WHEN 2 THEN 'money' WHEN 3 THEN 'bit' END + N','; FETCH MyCursor INTO @ColName , @ColType; END; CLOSE MyCursor; DEALLOCATE MyCursor; SET @SQL = @SQLHeader + @SQL + LEFT(@SQLFooter, LEN(@SQLFooter) - 1) + N')'; END; ELSE BEGIN -- no custom columns SET @SQL = @SQLHeader + @SQL + @SQLFooter; END; --PRINT @SQL; EXEC (@SQLDrop); EXEC (@SQL); EXEC ('DROP TABLE IF EXISTS ' + @JSONColsTbl); SET @ResultCode = 200; END TRY BEGIN CATCH SET @ResultCode = 500; SET @Comment = ERROR_MESSAGE(); END CATCH EXEC dbo.sx_pf_pPOST_API_LogEntry @Username, @TransactUsername, @ProcedureName, @ParameterString, @AffectedRows, @ResultCode, @TimestampCall, @Comment; IF @ResultCode >= 500 EXEC system.spSEND_Message 'ERROR', @Comment RETURN @ResultCode; END; GO -- SET documentation variables *********************************************************************** DECLARE @level0name NVARCHAR(255) = N'agent' -- enter schema name of the table ,@level1name NVARCHAR(255) = N'pCreateJSONView' -- enter procedure name ,@SX_Owner NVARCHAR(255) = N'OCT.core' -- enter owner name of the procedure from list (OCT.core, OCT.modules, Custom) ,@SX_Module NVARCHAR(255) = N'CORE' -- enter module name as free text (CORE,FIN, DEBKRED, HR, ...) ,@SX_ShipmentFlag INT = 1 -- 0 = Demo object - out of shipment process -- STANDARD OBJECTS -- 1 = shiped from saxess standard without modification -- 2 = shiped from saxess standard modified FOR customer from saxess -- 3 = shiped from saxess standard modified FOR customer from partner -- 4 = shiped from saxess standard modified FROM customer themself for own needs -- CUSTOM OBJECTS -- 10 = shiped from saxess as customer specific object -- 11 = shiped from partner as customer specific object -- 12 = shiped from customer as own specific object ,@SX_UserHint NVARCHAR(2000) = N'' -- optional, fill if Procedure shall be offerend for end user (e.g. for Pivot / Datagrid usage) -- KEEP this default constants ************************************************************************* DECLARE @name NVARCHAR(255) = N'MS_Description' ,@level0type NVARCHAR(255) = N'SCHEMA' ,@level1type NVARCHAR(255) = N'PROCEDURE' ,@level2type NVARCHAR(255) = N'PARAMETER' ,@level2name NVARCHAR(255) = N'' ,@value NVARCHAR(1000) = N''; SET @value = @SX_Owner; EXEC sys.sp_addextendedproperty N'SX_Owner' ,@value,@level0type,@level0name,@level1type,@level1name; SET @value = @SX_Module; EXEC sys.sp_addextendedproperty N'SX_Module' ,@value,@level0type,@level0name,@level1type,@level1name; SET @value = @SX_ShipmentFlag; EXEC sys.sp_addextendedproperty N'SX_ShipmentFlag' ,@value,@level0type,@level0name,@level1type,@level1name; SET @value = @SX_UserHint; EXEC sys.sp_addextendedproperty N'SX_UserHint' ,@value,@level0type,@level0name,@level1type,@level1name; -- SET documententation ************************************************************************* -- SET Procedure documentation SET @value = N'Procedure to create a helper table for the agent to resolve a JSON Column to single columns - the JSON Column must be named "CustomValuesJSON" - the resulting table will be named agent.vJSON_[schema]_[table]'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name; -- SET parameter documentation SET @level2name = N'@Username'; SET @value = N'the users name'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@Schema'; SET @value = N'the schemas name'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@Table'; SET @value = N'name of the table'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@UseRowKey'; SET @value = N'use row key'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; GO DROP PROCEDURE IF EXISTS dbo.sx_pf_POST_ProductDataTableValues; GO /* POST Operation for all values of the ProductDataTable The method can be switched between incremental POST and full POST Gerd Tautenhahn for Saxess Software GmbH Last modified: 12/2022 for OCT 5.8 Concept: - all Values are deliverd as Textarray - this requires a big split operation - the Text array must also deliver deleted values if it does a incrementel transmisson (deleted Values are delivered with empty ValueInt, ValueText, ValueFormula) - Values may be sended with '<#NV>' to keep existing values @ValuesInBracketsCommaSeparated format is: (ValueSeriesID,TimeID,Formula,Value,ValueText,ValueComment),(ValueSeriesIDTimeID,Formula,Value,ValueText,ValueComment) The whole string is single quoted and every parameter inside except the TimeID is single quoted if you check this in SQL Server Profiler or Table sx_pf_APILog, every String must have two inverted commas, the whole Parameter must have Single inverted comma this must show profiler: '[(''ValueSeriesID'',TimeID,''Formula'',''ValueInt'',''ValueText'',''ValueComment'')],[(''ValueSeriesID'',TimeID,''Formula'',''ValueInt'',''ValueText'',''ValueComment'')]' Informal: In further releases the )],[( delimiter was planend be replaced with CHAR(29) - which is no longer likely, as we will use JSON But CHAR(29) is at the moment used at internal temporary delimiter Test call 1. Empty FactoryID / ProductLineID / ProductID / ValueSeriesID / TimeID => 404 Not Found 2. TransactionUsername not determined for User => 403 Forbidden 3. Username does not have write access to ProductLine => 401 Unauthorized 4. Non exists Factory / ProductLine / Product keys => 404 Not Found 5. If ValueSeriesID not exists in planning.tdValueSeries for this ProductKey => 403 Forbidden 6. Product updated => 200 OK Test case 1 - Incremental Sending *************************************************************** 1. One Value for an existing Value must replace them 2. One Value for an not exiting Value must create it 3. If all Parameter are posted empty, the value is deleted 4. If one or more of the parameter are sended with <#NV> there Value is replaced with the existing value (or nothing if it exists not) 5. An ZeroEmpty Value in old or sended values is deleted DECLARE @RC INT DECLARE @Username NVARCHAR (255) = 'SQL' DECLARE @ProductID NVARCHAR (255) = '1' DECLARE @ProductLineID NVARCHAR (255) = 'U' DECLARE @FactoryID NVARCHAR (255) = 'ZT' DECLARE @IsIncrementalValuesFlag INT = 1 -- 3 Values with comments - NEVER place a linebreak in the sample array ! -- DECLARE @ValuesInBracketsCommaSeparated nvarchar(max) = '[(''K1'',20170115,''=IF(H25=4,3,2.1)'',''6'',''HaseTextValue'',''HaseComment'')],[(''K2'',20170215,''=IF(H25=4,3,2)'',''555'',''HaseTextValue'',''HaseComment2'')],[(''E1'',20170515,'''','''',''Text'',''Comment'')]' -- Delete the three values by sending them empty -- DECLARE @ValuesInBracketsCommaSeparated nvarchar(max) = '[(''K1'',20170115,'''','''','''',''HaseComment'')],[(''K2'',20170215,'''','''','''',''HaseComment2'')],[(''E1'',20170515,'''','''','''','''')]' -- Send an <#NV> Value for an existing numeric and an Text Value and delete one -- DECLARE @ValuesInBracketsCommaSeparated nvarchar(max) = '[(''K1'',20170115,'''',''<#NV>'','''',''HaseComment'')],[(''K2'',20170215,'''','''','''',''HaseComment2'')],[(''E1'',20170515,'''','''',''<#NV>'','''')]' EXECUTE @RC = dbo.sx_pf_POST_ProductDataTableValues @Username ,@ProductID ,@ProductLineID ,@FactoryID ,@IsIncrementalValuesFlag ,@ValuesInBracketsCommaSeparated; PRINT @RC; Test case 2 - fill all Values of an Product ************************************************ DECLARE @RC INT DECLARE @Username NVARCHAR(255) = 'SQL' DECLARE @ProductID NVARCHAR(255) = '1' DECLARE @ProductLineID NVARCHAR(255) = 'U' DECLARE @FactoryID NVARCHAR(255) = 'ZT' DECLARE @IsIncrementalValuesFlag INT = 0 DECLARE @ValuesInBracketsCommaSeparated nvarchar(max) = '' DECLARE @start_time DATETIME -- Generate fictive values for all TimeIDs of Products SELECT @ValuesInBracketsCommaSeparated = @ValuesInBracketsCommaSeparated + '[(''' + dVS.ValueSeriesID +''',' + CAST(TimeID AS nvarchar(255)) +',''=A2'',''1000'',''Hase | Maus'',''Komment|ar'')],' FROM planning.tdValueSeries dVS LEFT JOIN planning.tdTime dT ON dVS.ProductKey = dT.ProductKey WHERE dVS.FactoryID = @FactoryID AND dVS.ProductLineID = @ProductLineID AND dVS.ProductID = @ProductID SET @ValuesInBracketsCommaSeparated = LEFT(@ValuesInBracketsCommaSeparated, LEN(@ValuesInBracketsCommaSeparated) -1) PRINT @ValuesInBracketsCommaSeparated SET @start_time = GETUTCDATE() EXECUTE @RC = dbo.sx_pf_POST_ProductDataTableValues @Username ,@ProductID ,@ProductLineID ,@FactoryID ,@IsIncrementalValuesFlag ,@ValuesInBracketsCommaSeparated; PRINT @RC; SELECT RTRIM(CAST(DATEDIFF(MS, @start_time, GETUTCDATE()) AS CHAR(10))) AS 'TimeTaken' -- Testcase 3 - delete all values DECLARE @RC INT DECLARE @Username NVARCHAR (255) = 'SQL' DECLARE @ProductID NVARCHAR (255) = '1' DECLARE @ProductLineID NVARCHAR (255) = 'U' DECLARE @FactoryID NVARCHAR (255) = 'ZT' DECLARE @IsIncrementalValuesFlag INT = 0 DECLARE @ValuesInBracketsCommaSeparated NVARCHAR (255) = '' EXECUTE @RC = dbo.sx_pf_POST_ProductDataTableValues @Username ,@ProductID ,@ProductLineID ,@FactoryID ,@IsIncrementalValuesFlag ,@ValuesInBracketsCommaSeparated; PRINT @RC; Testcall Documentation SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'dbo', 'PROCEDURE', 'sx_pf_POST_ProductDataTableValues',NULL,NULL) UNION ALL SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'dbo', 'PROCEDURE', 'sx_pf_POST_ProductDataTableValues','PARAMETER',NULL) */ CREATE PROCEDURE dbo.sx_pf_POST_ProductDataTableValues @Username NVARCHAR(255), @ProductID NVARCHAR(255), @ProductLineID NVARCHAR(255), @FactoryID NVARCHAR(255), @IsIncrementalValuesFlag INT, @ValuesInBracketsCommaSeparated NVARCHAR(MAX) /* */ AS BEGIN SET NOCOUNT ON; DECLARE @TransactUsername NVARCHAR(255) = N''; DECLARE @FactoryKey INT = 0; DECLARE @ProductLineKey INT = 0; DECLARE @ProductKey INT = 0; DECLARE @errorvalue NVARCHAR(255); DECLARE @errormessage NVARCHAR(2000); DECLARE @ProcedureName NVARCHAR(255) = OBJECT_SCHEMA_NAME(@@PROCID) + N'.' + OBJECT_NAME(@@PROCID); DECLARE @ParameterString NVARCHAR(MAX) = N'''' + ISNULL(@Username , N'NULL') + N''',''' + ISNULL(@ProductID , N'NULL') + N''',''' + ISNULL(@ProductLineID , N'NULL') + N''',''' + ISNULL(@FactoryID , N'NULL') + N''',' + ISNULL(CAST(@IsIncrementalValuesFlag AS NVARCHAR(255)) , N'NULL') + N',''' + ISNULL(REPLACE(@ValuesInBracketsCommaSeparated, N'''', N'''''') , N'NULL') + N''''; -- WHY the replace - as there are double inverted comma's ? DECLARE @EffectedRows INT = 0; DECLARE @ResultCode INT = 501; DECLARE @TimestampCall DATETIME = GETUTCDATE(); DECLARE @Comment NVARCHAR(2000) = N''; -- SET Debug Flag for debugging and disable Transcation DECLARE @DebugFlag INT = 0 /* --Use for debugging SELECT * FROM ##Debug_ValuesInBracketsReceived SELECT * FROM ##Debug_ValueMergeTableFilled ORDER BY ValueSeriesID,TimeID SELECT * FROM ##Debug_ValueMergeTableBeforeTransmissions ORDER BY ValueSeriesID,TimeID */ -- STEP 0.1 - NULL Protection IF @Username IS NULL SET @Username = N''; IF @ProductID IS NULL SET @ProductID = N''; IF @ProductLineID IS NULL SET @ProductLineID = N''; IF @FactoryID IS NULL SET @FactoryID = N''; IF @IsIncrementalValuesFlag IS NULL SET @IsIncrementalValuesFlag = 0; IF @ValuesInBracketsCommaSeparated IS NULL SET @ValuesInBracketsCommaSeparated = N''; -- To delete Values in NOT Incremental Transmission, an empty @ValuesInBracketsCommaSeparated Parameter is sended IF @ValuesInBracketsCommaSeparated = '' AND @IsIncrementalValuesFlag = 0 BEGIN SET @ValuesInBracketsCommaSeparated = '[(''DeleteFlagID'',99999999,'''',''0'','''','''')]' --special ID with prevents from rejection from errorhandler END BEGIN TRY BEGIN TRANSACTION POST_PDT; -- STEP 0.2 - Protect input parameters SET @FactoryID = dbo.sx_pf_pProtectID (@FactoryID); SET @ProductID = dbo.sx_pf_pProtectID (@ProductID); SET @ProductLineID = dbo.sx_pf_pProtectID (@ProductLineID); IF @FactoryID = N'' OR @ProductLineID = N'' OR @ProductID = N'' BEGIN SET @ResultCode = 404; RAISERROR('Empty input parameters, one or more IDs are empty', 16, 10); END; -- STEP 1.1 - Determine transaction user SELECT @TransactUsername = dbo.sx_pf_Determine_TransactionUsername (@Username); IF @TransactUsername = N'403' BEGIN SET @ResultCode = 403; RAISERROR('Transaction user don`t exists', 16, 10); END; -- STEP 1.2 - Determine keys SELECT @FactoryKey = FactoryKey FROM planning.tdFactories WHERE FactoryID = @FactoryID; SELECT @ProductLineKey = ProductLineKey FROM planning.tdProductLines WHERE FactoryKey = @FactoryKey AND ProductLineID = @ProductLineID; SELECT @ProductKey = ProductKey FROM planning.tdProducts WHERE ProductLineKey = @ProductLineKey AND ProductID = @ProductID; IF @FactoryKey = 0 OR @ProductLineKey = 0 OR @ProductKey = 0 BEGIN SET @ResultCode = 404; RAISERROR('One or more IDs dont exist (No Keys found)', 16, 10); END; -- STEP 2 - Check rights EXEC @ResultCode = sx_pf_pGET_ProductLineWriteRight @TransactUsername, @FactoryID, @ProductLineID; IF @ResultCode <> 200 BEGIN SET @errormessage = 'Invalid rights - no write Rights for ' + @TransactUsername + '.'; RAISERROR(@errormessage, 16, 10); END -- STEP 3.0 - Store Timeline, only TimeID, as Timeline is not changed during Posting IF OBJECT_ID('tempdb..#Timeline') IS NOT NULL DROP TABLE #Timeline; CREATE TABLE #Timeline ( TimeID NVARCHAR(10) COLLATE DATABASE_DEFAULT NOT NULL UNIQUE CLUSTERED ); INSERT INTO #Timeline SELECT TimeID FROM planning.tdTime WHERE ProductKey = @ProductKey; -- STEP 3.1 - ValueMergeTable Merge the current values of the product into the ValueMergeTable IF OBJECT_ID('tempdb..#ValueMergeTable') IS NOT NULL DROP TABLE #ValueMergeTable; CREATE TABLE #ValueMergeTable ( ValueSeriesKey BIGINT NOT NULL ,ValueSeriesID NVARCHAR(255) COLLATE DATABASE_DEFAULT NOT NULL ,TimeID NVARCHAR(10) COLLATE DATABASE_DEFAULT NOT NULL ,ValueFormula NVARCHAR(MAX) COLLATE DATABASE_DEFAULT NOT NULL ,ValueInt NVARCHAR(255) COLLATE DATABASE_DEFAULT NOT NULL ,ValueText NVARCHAR(MAX) COLLATE DATABASE_DEFAULT NOT NULL ,ValueComment NVARCHAR(MAX) COLLATE DATABASE_DEFAULT NOT NULL ,[IsNumeric] INT NOT NULL ,IsFormula INT NOT NULL ,ProcessCode NVARCHAR(20) COLLATE DATABASE_DEFAULT NOT NULL ,DocuCode NVARCHAR(20) COLLATE DATABASE_DEFAULT NOT NULL ,ValueSeriesID_TimeID NVARCHAR(255) COLLATE DATABASE_DEFAULT NOT NULL ,AllowZero INT NOT NULL ); -- First Type "Old Values" is loaded in ValueMergeTable INSERT INTO #ValueMergeTable SELECT 0 AS ValueSeriesKey -- No Values from dValueSeries, this Informations are later added when old and sended Values are together ,ValueSeriesID ,v.TimeID ,ValueFormula ,ValueInt ,ValueText ,ValueComment ,0 AS IsNumeric ,0 AS IsFormula ,N'OLD VALUES' AS ProcessCode ,N'NONE' AS DocuCode ,ValueSeriesID + CAST(v.TimeID AS NVARCHAR(10)) AS ValueSeries_TimeID ,0 AS AllowZero FROM planning.tfValues v INNER JOIN #Timeline tl ON v.TimeID = tl.TimeID WHERE ProductKey = @ProductKey; -- Store Header, which is dValueSeries Information which will added to old and sended Values IF OBJECT_ID('tempdb..#Header') IS NOT NULL DROP TABLE #Header; CREATE TABLE #Header ( ValueSeriesKey BIGINT NOT NULL ,ValueSeriesID NVARCHAR(255) COLLATE DATABASE_DEFAULT NOT NULL ,[IsNumeric] INT NOT NULL ,IsFormula INT NOT NULL ,AllowZero INT NOT NULL ,UNIQUE CLUSTERED (ValueSeriesID, ValueSeriesKey, [IsNumeric], IsFormula) ); INSERT INTO #Header SELECT ValueSeriesKey ,ValueSeriesID ,[IsNumeric] ,IIF(ValueSource IN (N'XLS',N'XLS-Strict'), 1, 0) AS IsFormula ,AllowZero FROM planning.tdValueSeries WHERE ProductKey = @ProductKey; -- STEP 3.3 - Check for count of delimiter in Array DECLARE @OpenDelimetersCount INT = 0; DECLARE @DelimetersIndex INT = CHARINDEX(N'[(', @ValuesInBracketsCommaSeparated); WHILE @DelimetersIndex > 0 BEGIN SET @OpenDelimetersCount += 1; SET @DelimetersIndex = CHARINDEX(N'[(', @ValuesInBracketsCommaSeparated, @DelimetersIndex + 1); END; IF @OpenDelimetersCount > 32765 BEGIN SET @ResultCode = 403; RAISERROR('ValueArray to big (More then 32750 Values need special API Update.)', 16, 10); END; IF @OpenDelimetersCount = 0 BEGIN SET @ResultCode = 404; RAISERROR('No delimiters found in @ValuesInBracketsCommaSeparated', 16, 10); END; SET @DelimetersIndex = CHARINDEX(N')]', @ValuesInBracketsCommaSeparated); WHILE (@DelimetersIndex > 0) BEGIN SET @OpenDelimetersCount -= 1; SET @DelimetersIndex = CHARINDEX(N')]', @ValuesInBracketsCommaSeparated, @DelimetersIndex + 1); END; IF @OpenDelimetersCount <> 0 BEGIN SET @ResultCode = 404; RAISERROR('Invalid count of delimiter in @ValuesInBracketsCommaSeparated', 16, 10); END; -- STEP 3.4 - Splitt the Array with the new values for the Product to a temporary table ####################################################################################################################### -- cut leading/following brackets and ' from ValueSeriesID SET @ValuesInBracketsCommaSeparated = LEFT(@ValuesInBracketsCommaSeparated, LEN(@ValuesInBracketsCommaSeparated)-2); SET @ValuesInBracketsCommaSeparated = RIGHT(@ValuesInBracketsCommaSeparated, LEN(@ValuesInBracketsCommaSeparated)-2); DROP TABLE IF EXISTS #Values1; CREATE TABLE #Values1 ( txtValuesPipe NVARCHAR(MAX) COLLATE DATABASE_DEFAULT NOT NULL ); DROP TABLE IF EXISTS #Values2; CREATE TABLE #Values2 ( ValueSeriesID NVARCHAR(255) COLLATE DATABASE_DEFAULT NOT NULL ,txtValuesPipe NVARCHAR(MAX) COLLATE DATABASE_DEFAULT NOT NULL ); DROP TABLE IF EXISTS #Values3; CREATE TABLE #Values3 ( ValueSeriesID NVARCHAR(255) COLLATE DATABASE_DEFAULT NOT NULL ,TimeID NVARCHAR(10) COLLATE DATABASE_DEFAULT NOT NULL ,txtValuesPipe NVARCHAR(MAX) COLLATE DATABASE_DEFAULT NOT NULL ); DROP TABLE IF EXISTS #Values4; CREATE TABLE #Values4 ( ValueSeriesID NVARCHAR(255) COLLATE DATABASE_DEFAULT NOT NULL ,TimeID NVARCHAR(10) COLLATE DATABASE_DEFAULT NOT NULL ,ValueFormula NVARCHAR(MAX) COLLATE DATABASE_DEFAULT NOT NULL ,txtValuesPipe NVARCHAR(MAX) COLLATE DATABASE_DEFAULT NOT NULL ); DROP TABLE IF EXISTS #Values5; CREATE TABLE #Values5 ( ValueSeriesID NVARCHAR(255) COLLATE DATABASE_DEFAULT NOT NULL ,TimeID NVARCHAR(10) COLLATE DATABASE_DEFAULT NOT NULL ,ValueFormula NVARCHAR(MAX) COLLATE DATABASE_DEFAULT NOT NULL ,ValueInt NVARCHAR(255) COLLATE DATABASE_DEFAULT NOT NULL ,txtValuesPipe NVARCHAR(MAX) COLLATE DATABASE_DEFAULT NOT NULL ); DROP TABLE IF EXISTS #Values6; CREATE TABLE #Values6 ( ValueSeriesID NVARCHAR(255) COLLATE DATABASE_DEFAULT NOT NULL ,TimeID NVARCHAR(10) COLLATE DATABASE_DEFAULT NOT NULL ,ValueFormula NVARCHAR(MAX) COLLATE DATABASE_DEFAULT NOT NULL ,ValueInt NVARCHAR(255) COLLATE DATABASE_DEFAULT NOT NULL ,ValueText NVARCHAR(MAX) COLLATE DATABASE_DEFAULT NOT NULL ,ValueComment NVARCHAR(MAX) COLLATE DATABASE_DEFAULT NOT NULL ); DECLARE @spr NCHAR (1) = CHAR(29) -- Remove existing CHAR(29) = not visible character from string SET @ValuesInBracketsCommaSeparated = REPLACE(@ValuesInBracketsCommaSeparated,@spr,N''); --PRINT @ValuesInBracketsCommaSeparated -- Split String into Rows - all Values except the TimeID are single quoted after that an separated by @spr INSERT INTO #Values1 SELECT REPLACE(REPLACE(txtValues,N',''', @spr + ''''), N''',', '''' + @spr) FROM dbo.sx_pf_PivotStringIntoTable(@ValuesInBracketsCommaSeparated, N')],[('); -- Split string into two Columns (separate first column ValueSeriesID) INSERT INTO #Values2 SELECT LEFT(txtValuesPipe, CHARINDEX(@spr, txtValuesPipe) - 1) ,RIGHT(txtValuesPipe, LEN(txtValuesPipe) - CHARINDEX(@spr, txtValuesPipe)) FROM #Values1; -- Split last columns once more (separate TimeID) INSERT INTO #Values3 SELECT ValueSeriesID ,LEFT(txtValuesPipe, CHARINDEX(@spr, txtValuesPipe) - 1) ,RIGHT(txtValuesPipe, LEN(txtValuesPipe) - CHARINDEX(@spr, txtValuesPipe)) FROM #Values2; -- Delete Implicit sended periods (dont exit in current Product) DELETE FROM #Values3 WHERE TimeID NOT IN (SELECT CAST(TimeID AS NVARCHAR(10)) FROM #Timeline); -- Split last columns once more (separate ValueFormula) INSERT INTO #Values4 SELECT ValueSeriesID ,TimeID ,LEFT(txtValuesPipe, CHARINDEX(@spr, txtValuesPipe) - 1) ,RIGHT(txtValuesPipe, LEN(txtValuesPipe) - CHARINDEX(@spr, txtValuesPipe)) FROM #Values3; -- Split last columns once more (separate ValueInt) INSERT INTO #Values5 SELECT ValueSeriesID ,TimeID ,ValueFormula ,LEFT(txtValuesPipe, CHARINDEX(@spr, txtValuesPipe) - 1) ,RIGHT(txtValuesPipe, LEN(txtValuesPipe) - CHARINDEX(@spr, txtValuesPipe)) FROM #Values4; -- Split last columns once more (Separate ValueText and ValueComment) INSERT INTO #Values6 SELECT ValueSeriesID ,TimeID ,ValueFormula ,ValueInt ,LEFT(txtValuesPipe, CHARINDEX(@spr, txtValuesPipe) - 1) ,RIGHT(txtValuesPipe, LEN(txtValuesPipe) - CHARINDEX(@spr, txtValuesPipe)) FROM #Values5; -- Replace Quotes from all strings (all except TimeID) UPDATE #Values6 SET ValueSeriesID = RIGHT(LEFT(ValueSeriesID, (LEN(ValueSeriesID)-1)), LEN(ValueSeriesID)-2) ,ValueFormula = RIGHT(LEFT(ValueFormula, (LEN(ValueFormula)-1)), LEN(ValueFormula)-2) ,ValueInt = RIGHT(LEFT(ValueInt, (LEN(ValueInt)-1)), LEN(ValueInt)-2) ,ValueText = RIGHT(LEFT(ValueText, (LEN(ValueText)-1)), LEN(ValueText)-2) ,ValueComment = RIGHT(LEFT(ValueComment, (LEN(ValueComment)-1)), LEN(ValueComment)-2); -- Replace Linebreaks in ValueText, but keep them in ValueComment UPDATE #Values6 SET ValueText =REPLACE(REPLACE(ValueText,CHAR(13),''),CHAR(10),''); IF @DebugFlag = 1 BEGIN IF OBJECT_ID('tempdb..##Debug_Values6') IS NOT NULL DROP TABLE ##Debug_Values6; SELECT * INTO ##Debug_Values6 FROM #Values6; END; -- STEP 3.5 - UNION both temporary tables ####################################################################################################################### -- Second Typ "Sended Values" is loaded in ValueMergeTable INSERT INTO #ValueMergeTable SELECT 0 AS ValueSeriesKey ,ValueSeriesID ,TimeID ,ValueFormula ,ValueInt ,ValueText ,ValueComment ,0 AS [IsNumeric] ,0 AS IsFormula ,N'SENDED VALUES' ,N'NONE' ,ValueSeriesID+TimeID ,0 AS AllowZero FROM #Values6; -- Add the header informations to the values UPDATE VMT SET VMT.ValueSeriesKey = H.ValueSeriesKey ,VMT.[IsNumeric] = H.[IsNumeric] ,VMT.IsFormula = H.IsFormula ,VMT.AllowZero = H.AllowZero FROM #ValueMergeTable VMT INNER JOIN #Header H ON VMT.ValueSeriesID = H.ValueSeriesID; -- check for not existing ValueSeriesIDs which may have been sended IF EXISTS(SELECT TOP (1) ValueSeriesID FROM #ValueMergeTable WHERE ValueSeriesKey = 0) BEGIN SELECT TOP(1) @errorvalue = ValueSeriesID FROM #ValueMergeTable WHERE ValueSeriesKey = 0 ORDER BY TimeID; IF @errorvalue = 'DeleteFlagID' BEGIN DELETE FROM #ValueMergeTable END ELSE BEGIN SET @errormessage = 'One or more sended ValueSeriesIDs were not found, e.g. '+ @errorvalue +'.' SET @ResultCode = 403; RAISERROR(@errormessage, 16, 10); END END; -- check if more than one value is sended for a ValueSeries/TimeID combination IF EXISTS( SELECT COUNT(TimeID) FROM #ValueMergeTable WHERE ProcessCode = 'SENDED VALUES' GROUP BY ValueSeriesID_TimeID HAVING COUNT(TimeID) > 1 ) BEGIN SET @ResultCode = 403; RAISERROR('Douplicate Values found in ValueArray', 16, 10); END; -- STEP 3.6 - Prepare the ValueMergeTable ####################################################################################################################### -- FLAG OLD Values the user want to delete in Case of Numeric Series without formula -- Value 0 maybe sended as '000' UPDATE #ValueMergeTable SET ProcessCode = N'OLD VALUES TO DELETE' WHERE ProcessCode = N'OLD VALUES' AND [IsNumeric] = 1 AND IsFormula = 0 AND ValueSeriesID_TimeID IN ( SELECT ValueSeriesID_TimeID FROM #ValueMergeTable WHERE ProcessCode = N'SENDED VALUES' AND (TRY_CAST(ValueInt AS BIGINT) = 0 OR ValueInt = N'') ); -- FLAG OLD Values the user want to delete in Case of Numeric Series with formula UPDATE #ValueMergeTable SET ProcessCode = N'OLD VALUES TO DELETE' WHERE ProcessCode = N'OLD VALUES' AND [IsNumeric] = 1 AND IsFormula = 1 AND ValueSeriesID_TimeID IN ( SELECT ValueSeriesID_TimeID FROM #ValueMergeTable WHERE ProcessCode = N'SENDED VALUES' AND ValueFormula = N'' AND (TRY_CAST(ValueInt AS BIGINT) = 0 OR ValueInt = N'') ); -- FLAG OLD Values the user want to delete in Case of NON-Numeric Series without formula UPDATE #ValueMergeTable SET ProcessCode = N'OLD VALUES TO DELETE' WHERE ProcessCode = N'OLD VALUES' AND [IsNumeric] = 0 AND IsFormula = 0 AND ValueSeriesID_TimeID IN ( SELECT ValueSeriesID_TimeID FROM #ValueMergeTable WHERE ProcessCode = N'SENDED VALUES' AND ValueText = N'' ); -- FLAG OLD Values the user want to delete in Case of NON-Numeric Series with formula UPDATE #ValueMergeTable SET ProcessCode = N'OLD VALUES TO DELETE' WHERE ProcessCode = N'OLD VALUES' AND [IsNumeric] = 0 AND IsFormula = 1 AND ValueSeriesID_TimeID IN ( SELECT ValueSeriesID_TimeID FROM #ValueMergeTable WHERE ProcessCode = N'SENDED VALUES' AND ValueText = N'' AND ValueFormula = N'' ); -- Debugging Output IF @DebugFlag = 1 BEGIN IF OBJECT_ID('tempdb..##Debug_ValueMergeTableFilled') IS NOT NULL DROP TABLE ##Debug_ValueMergeTableFilled; SELECT * INTO ##Debug_ValueMergeTableFilled FROM #ValueMergeTable; END; -- STEP 3.7 - Handle VMT ####################################################################################################################### -- Documentation of changes in log table /* -- NEW is a Value which is not contained in existing Values UPDATE #ValueMergeTable SET DocuCode = N'NEW VALUE' WHERE ProcessCode = N'SENDED VALUES' AND DocuCode = N'NONE' AND ValueSeriesID + N'_' + TimeID NOT IN ( SELECT ValueSeriesID + N'_' + TimeID FROM #ValueMergeTable WHERE ProcessCode = N'OLD VALUES' ); -- CHANGED is a Value which is contained in existing Values UPDATE #ValueMergeTable SET DocuCode = N'CHANGED VALUE' WHERE ProcessCode = N'SENDED VALUES' AND DocuCode = N'NONE' AND ValueSeriesID + N'_' + TimeID IN ( SELECT ValueSeriesID + N'_' + TimeID FROM #ValueMergeTable WHERE ProcessCode = N'OLD VALUES' ); INSERT INTO sx_pf_AuditLog VALUES SELECT * FROM #ValueMergeTable WHERE ValueSeriesID + '_' + CAST(TimeID AS NVARCHAR(10)) IN ( SELECT ValueSeriesID + '_' + CAST(TimeID AS NVARCHAR(10)) FROM #ValueMergeTable WHERE ProcessCode = 'SENDED VALUES' AND DocuCode != 'NONE' ) */ -- Handle <#NV> Parameters - Replace them with the old Values (if exist !) UPDATE VMT SET VMT.ValueInt = OV.ValueInt FROM #ValueMergeTable VMT , (SELECT ValueInt, ValueSeriesID, TimeID FROM #ValueMergeTable WHERE ProcessCode IN (N'OLD VALUES', N'OLD VALUES TO DELETE')) OV WHERE VMT.ProcessCode = N'SENDED VALUES' AND VMT.ValueInt LIKE N'<#NV>' AND VMT.ValueSeriesID = OV.ValueSeriesID AND VMT.TimeID = OV.TimeID; UPDATE VMT SET VMT.ValueText = OV.ValueText FROM #ValueMergeTable VMT , (SELECT ValueText, ValueSeriesID, TimeID FROM #ValueMergeTable WHERE ProcessCode IN (N'OLD VALUES', N'OLD VALUES TO DELETE')) OV WHERE VMT.ProcessCode = N'SENDED VALUES' AND VMT.ValueText = N'<#NV>' AND VMT.ValueSeriesID = OV.ValueSeriesID AND VMT.TimeID = OV.TimeID; UPDATE VMT SET VMT.ValueFormula = OV.ValueFormula FROM #ValueMergeTable VMT , (SELECT ValueFormula, ValueSeriesID, TimeID FROM #ValueMergeTable WHERE ProcessCode IN (N'OLD VALUES', N'OLD VALUES TO DELETE')) OV WHERE VMT.ProcessCode = N'SENDED VALUES' AND VMT.ValueFormula = N'<#NV>' AND VMT.ValueSeriesID = OV.ValueSeriesID AND VMT.TimeID = OV.TimeID; UPDATE VMT SET VMT.ValueComment = OV.ValueComment FROM #ValueMergeTable VMT , (SELECT ValueComment, ValueSeriesID, TimeID FROM #ValueMergeTable WHERE ProcessCode IN (N'OLD VALUES', N'OLD VALUES TO DELETE')) OV WHERE VMT.ProcessCode = N'SENDED VALUES' AND VMT.ValueComment = N'<#NV>' AND VMT.ValueSeriesID = OV.ValueSeriesID AND VMT.TimeID = OV.TimeID; -- Handle <#NV> Parameter, where no old Values existed UPDATE #ValueMergeTable SET ValueInt = N'0' WHERE ValueInt = N'<#NV>' OR [IsNumeric] = 0; UPDATE #ValueMergeTable SET ValueText = N'' WHERE ValueText = N'<#NV>' OR [IsNumeric] = 1; UPDATE #ValueMergeTable SET ValueFormula = N'' WHERE ValueFormula = N'<#NV>' OR IsFormula = 0; UPDATE #ValueMergeTable SET ValueComment = N'' WHERE ValueComment = N'<#NV>'; -- Debugging Output IF @DebugFlag = 1 BEGIN IF OBJECT_ID('tempdb..##Debug_ValueMergeTableBeforeTransmissions') IS NOT NULL DROP TABLE ##Debug_ValueMergeTableBeforeTransmissions; SELECT * INTO ##Debug_ValueMergeTableBeforeTransmissions FROM #ValueMergeTable; END; -- STEP 3.8 - Save data IF @IsIncrementalValuesFlag <> 1 BEGIN -- CASE FULL transmission ######################################### -- Delete all Values DELETE FROM planning.tfValues WHERE ProductKey = @ProductKey; SET @EffectedRows += @@ROWCOUNT; -- DELETE Useless NullEmpty Values in ALL Values -- einfache Nullwerte DELETE FROM #ValueMergeTable WHERE [IsNumeric] = 1 AND IsFormula = 0 AND COALESCE(TRY_CAST(ValueInt AS BIGINT),0) = 0 AND (RIGHT(ValueSeriesID,2) <> '_0' AND AllowZero = 0); -- Numerische Formelzellen ohne Formel mit Wert 0 DELETE FROM #ValueMergeTable WHERE [IsNumeric] = 1 AND IsFormula = 1 AND ValueFormula = '' AND COALESCE(TRY_CAST(ValueInt AS BIGINT),0) = 0 AND (RIGHT(ValueSeriesID,2) <> '_0' AND AllowZero = 0); -- Gelöschte Werte in numerischen Zellen DELETE FROM #ValueMergeTable WHERE [IsNumeric] = 1 AND IsFormula = 0 AND ValueInt = N''; -- einfach Leertexte DELETE FROM #ValueMergeTable WHERE [IsNumeric] = 0 AND IsFormula = 0 AND ValueText = N''; -- Nichtnumerische Formelzellen ohne Formel mit Leertext DELETE FROM #ValueMergeTable WHERE [IsNumeric] = 0 AND IsFormula = 1 AND ValueFormula = '' AND ValueText = N''; DELETE FROM #ValueMergeTable WHERE ProcessCode = N'SENDED VALUES' AND [IsNumeric] = 1 AND IsFormula = 1 AND ValueInt = N'' AND ValueFormula = N''; -- Insert all Values INSERT INTO planning.tfValues ( ValueSeriesKey ,ProductKey ,ProductLineKey ,FactoryKey ,ProductID ,ProductLineID ,FactoryID ,ValueSeriesID ,TimeID ,ValueFormula ,ValueInt ,ValueText ,ValueComment ) SELECT ValueSeriesKey ,@ProductKey ,@ProductLineKey ,@FactoryKey ,@ProductID ,@ProductLineID ,@FactoryID ,ValueSeriesID ,TimeID ,ValueFormula ,ValueInt ,ValueText ,ValueComment FROM #ValueMergeTable WHERE ProcessCode IN (N'SENDED VALUES'); SET @EffectedRows += @@ROWCOUNT; END ELSE BEGIN -- CASE INCREMENTAL Transmission - it also leads to a full delete and insert !! ######################################### -- DELETE Values, which are sended to delete in OLD VALUES DELETE FROM #ValueMergeTable WHERE ProcessCode = N'OLD VALUES TO DELETE'; -- DELETE sended non-formula values DELETE FROM #ValueMergeTable WHERE ProcessCode = N'SENDED VALUES' AND [IsNumeric] = 1 AND IsFormula = 0 AND COALESCE(TRY_CAST(ValueInt AS BIGINT),0) = 0 AND (RIGHT(ValueSeriesID,2) <> '_0' AND AllowZero = 0); -- send Null Values for ValueSeries *_0; --empty values must delete 0 in _0 Series DELETE FROM #ValueMergeTable WHERE ProcessCode = N'SENDED VALUES' AND [IsNumeric] = 1 AND IsFormula = 0 AND ValueInt = '' AND (RIGHT(ValueSeriesID,2) = '_0' AND AllowZero = 0); -- delete deleted formulas DELETE FROM #ValueMergeTable WHERE ProcessCode = N'SENDED VALUES' AND [IsNumeric] = 1 AND IsFormula = 1 AND COALESCE(TRY_CAST(ValueInt AS BIGINT),0) = 0 AND ValueFormula = N'' AND (RIGHT(ValueSeriesID,2) <> '_0' AND AllowZero = 0); -- send Null Values for ValueSeries *_0; -- Delete DELETE FROM #ValueMergeTable WHERE ProcessCode = N'SENDED VALUES' AND [IsNumeric] = 0 AND IsFormula = 0 AND ValueText = N'' DELETE FROM #ValueMergeTable WHERE ProcessCode = N'SENDED VALUES' AND [IsNumeric] = 0 AND IsFormula = 1 AND ValueText = N'' AND ValueFormula = N''; DELETE FROM #ValueMergeTable WHERE ProcessCode = N'SENDED VALUES' AND [IsNumeric] = 1 AND IsFormula = 0 AND ValueInt = N''; DELETE FROM #ValueMergeTable WHERE ProcessCode = N'SENDED VALUES' AND [IsNumeric] = 1 AND IsFormula = 1 AND ValueInt = N'' AND ValueFormula = N''; -- DELETE Useless NullEmpty Values in OLD Values DELETE FROM #ValueMergeTable WHERE ProcessCode = N'OLD VALUES' AND [IsNumeric] = 1 AND IsFormula = 0 AND COALESCE(TRY_CAST(ValueInt AS BIGINT),0) = 0 AND (RIGHT(ValueSeriesID,2) <> '_0' AND AllowZero = 0); -- send Null Values for ValueSeries *_0 DELETE FROM #ValueMergeTable WHERE ProcessCode = N'OLD VALUES' AND [IsNumeric] = 0 AND IsFormula = 0 AND ValueText = N''; -- DELETE all Values DELETE FROM planning.tfValues WHERE ProductKey = @ProductKey; SET @EffectedRows += @@ROWCOUNT; -- Delete OLD VALUES which are new sended DELETE FROM #ValueMergeTable WHERE ProcessCode = N'OLD VALUES' AND ValueSeriesID_TimeID IN ( SELECT ValueSeriesID_TimeID FROM #ValueMergeTable WHERE ProcessCode = N'SENDED VALUES' ); -- Insert all Values INSERT INTO planning.tfValues ( ValueSeriesKey ,ProductKey ,ProductLineKey ,FactoryKey ,ProductID ,ProductLineID ,FactoryID ,ValueSeriesID ,TimeID ,ValueFormula ,ValueInt ,ValueText ,ValueComment ) SELECT ValueSeriesKey ,@ProductKey ,@ProductLineKey ,@FactoryKey ,@ProductID ,@ProductLineID ,@FactoryID ,ValueSeriesID ,TimeID ,ValueFormula ,CAST(ValueInt AS BIGINT) ,ValueText ,ValueComment FROM #ValueMergeTable WHERE ProcessCode IN (N'SENDED VALUES', N'OLD VALUES'); SET @EffectedRows += @@ROWCOUNT; END; --- materialize Product, if it is in ZT-PARAM IF @FactoryID = 'ZT' AND @ProductLineID LIKE 'PARAM%' BEGIN EXEC control.spParamTables @ProductID END COMMIT TRANSACTION POST_PDT; END TRY BEGIN CATCH DECLARE @Error_state INT = ERROR_STATE(); SET @Comment = ERROR_MESSAGE(); ROLLBACK TRANSACTION POST_PDT; IF @Error_state <> 10 BEGIN SET @ResultCode = 500; PRINT 'Rollback due to not executable command.'; END ELSE IF @ResultCode IS NULL OR @ResultCode/100 = 2 BEGIN SET @ResultCode = 500; END; END CATCH EXEC dbo.sx_pf_pPOST_API_LogEntry @Username, @TransactUsername, @ProcedureName, @ParameterString, @EffectedRows, @ResultCode, @TimestampCall, @Comment; RETURN @ResultCode; END GO ALTER AUTHORIZATION ON OBJECT ::dbo.sx_pf_POST_ProductDataTableValues TO db_octservice; GO -- SET documentation variables *********************************************************************** DECLARE @level0name NVARCHAR(255) = N'dbo' -- enter schema name of the table ,@level1name NVARCHAR(255) = N'sx_pf_POST_ProductDataTableValues' -- enter procedure name ,@SX_Owner NVARCHAR(255) = N'OCT.core' -- enter owner name of the procedure from list (OCT.core, OCT.modules, Custom) ,@SX_Module NVARCHAR(255) = N'CORE' -- enter module name as free text (CORE,FIN, DEBKRED, HR, ...) ,@SX_ShipmentFlag INT = 1 -- 0 = Demo object - out of shipment process -- STANDARD OBJECTS -- 1 = shiped from saxess standard without modification -- 2 = shiped from saxess standard modified FOR customer from saxess -- 3 = shiped from saxess standard modified FOR customer from partner -- 4 = shiped from saxess standard modified FROM customer themself for own needs -- CUSTOM OBJECTS -- 10 = shiped from saxess as customer specific object -- 11 = shiped from partner as customer specific object -- 12 = shiped from customer as own specific object ,@SX_UserHint NVARCHAR(2000) = N'' -- optional, fill if Procedure shall be offerend for end user (e.g. for Pivot / Datagrid usage) -- KEEP this default constants ************************************************************************* DECLARE @name NVARCHAR(255) = N'MS_Description' ,@level0type NVARCHAR(255) = N'SCHEMA' ,@level1type NVARCHAR(255) = N'PROCEDURE' ,@level2type NVARCHAR(255) = N'PARAMETER' ,@level2name NVARCHAR(255) = N'' ,@value NVARCHAR(1000) = N''; SET @value = @SX_Owner; EXEC sys.sp_addextendedproperty N'SX_Owner' ,@value,@level0type,@level0name,@level1type,@level1name; SET @value = @SX_Module; EXEC sys.sp_addextendedproperty N'SX_Module' ,@value,@level0type,@level0name,@level1type,@level1name; SET @value = @SX_ShipmentFlag; EXEC sys.sp_addextendedproperty N'SX_ShipmentFlag' ,@value,@level0type,@level0name,@level1type,@level1name; SET @value = @SX_UserHint; EXEC sys.sp_addextendedproperty N'SX_UserHint' ,@value,@level0type,@level0name,@level1type,@level1name; -- SET documententation ************************************************************************* -- SET Procedure documentation SET @value = N'POST Operation for all values of the ProductDataTable The method can be switched between incremental POST and full POST'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name; SET @level2name = N'@Username'; SET @value = N'Username'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@ProductID'; SET @value = N'ProductID'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@ProductLineID'; SET @value = N'ProductLineID'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@FactoryID'; SET @value = N'FactoryID'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@IsIncrementalValuesFlag'; SET @value = N'IsIncrementalValuesFlag'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@ValuesInBracketsCommaSeparated'; SET @value = N'ValuesInBracketsCommaSeparated'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; GO DROP PROCEDURE IF EXISTS dbo.sx_pf_POST_ProductLine; GO /* POST Operation for ProductLine Not existing ProductLine will be created, existing gets an Update If attributes passed with value <#NV>, it will be ignored during the update and maintain the existing value, during insert the attribute is created empty Gerd Tautenhahn for Saxess Software GmbH Last modified: 10/2022 for OCT 5.8 Test call 1. Empty FactoryID / ProductLineID => 404 Not Found 2. TransactionUsername not determined for User => 403 Forbidden 3. Username does not have write access to ProductLine => 401 Unauthorized 3. Username does not have write access to Factory on Create => 401 Unauthorized 5. Non exists Factory / ProductLine keys => 404 Not Found 6. ProductLine created => 201 Created 7. ProductLine updated => 200 OK DECLARE @RC INT ,@Username NVARCHAR(255) = 'SQL' ,@ProductLineID NVARCHAR(255) = 'ee' ,@FactoryID NVARCHAR(255) = 'ZT' ,@NameShort NVARCHAR(255) = '' ,@NameLong NVARCHAR(255) = '' ,@CommentUser NVARCHAR(255) = '' ,@CommentDev NVARCHAR(255) = '' ,@ResponsiblePerson NVARCHAR(255) = '' ,@ImageName NVARCHAR(255) = '' ,@DefaultTemplate NVARCHAR(255) = '' ,@GlobalAttributeSource1 NVARCHAR(255) = '' ,@GlobalAttributeAlias1 NVARCHAR(255) = '' ,@GlobalAttributeSource2 NVARCHAR(255) = '' ,@GlobalAttributeAlias2 NVARCHAR(255) = '' ,@GlobalAttributeSource3 NVARCHAR(255) = '' ,@GlobalAttributeAlias3 NVARCHAR(255) = '' ,@GlobalAttributeSource4 NVARCHAR(255) = '' ,@GlobalAttributeAlias4 NVARCHAR(255) = '' ,@GlobalAttributeSource5 NVARCHAR(255) = '' ,@GlobalAttributeAlias5 NVARCHAR(255) = '' ,@GlobalAttributeSource6 NVARCHAR(255) = '' ,@GlobalAttributeAlias6 NVARCHAR(255) = '' ,@GlobalAttributeSource7 NVARCHAR(255) = '' ,@GlobalAttributeAlias7 NVARCHAR(255) = '' ,@GlobalAttributeSource8 NVARCHAR(255) = '' ,@GlobalAttributeAlias8 NVARCHAR(255) = '' ,@GlobalAttributeSource9 NVARCHAR(255) = '' ,@GlobalAttributeAlias9 NVARCHAR(255) = '' ,@GlobalAttributeSource10 NVARCHAR(255) = '' ,@GlobalAttributeAlias10 NVARCHAR(255) = '' ,@GlobalAttributeSource11 NVARCHAR(255) = '' ,@GlobalAttributeAlias11 NVARCHAR(255) = '' ,@GlobalAttributeSource12 NVARCHAR(255) = '' ,@GlobalAttributeAlias12 NVARCHAR(255) = '' ,@GlobalAttributeSource13 NVARCHAR(255) = '' ,@GlobalAttributeAlias13 NVARCHAR(255) = '' ,@GlobalAttributeSource14 NVARCHAR(255) = '' ,@GlobalAttributeAlias14 NVARCHAR(255) = '' ,@GlobalAttributeSource15 NVARCHAR(255) = '' ,@GlobalAttributeAlias15 NVARCHAR(255) = '' ,@GlobalAttributeSource16 NVARCHAR(255) = '' ,@GlobalAttributeAlias16 NVARCHAR(255) = '' ,@GlobalAttributeSource17 NVARCHAR(255) = '' ,@GlobalAttributeAlias17 NVARCHAR(255) = '' ,@GlobalAttributeSource18 NVARCHAR(255) = '' ,@GlobalAttributeAlias18 NVARCHAR(255) = '' ,@GlobalAttributeSource19 NVARCHAR(255) = '' ,@GlobalAttributeAlias19 NVARCHAR(255) = '' ,@GlobalAttributeSource20 NVARCHAR(255) = '' ,@GlobalAttributeAlias20 NVARCHAR(255) = '' ,@GlobalAttributeSource21 NVARCHAR(255) = '' ,@GlobalAttributeAlias21 NVARCHAR(255) = '' ,@GlobalAttributeSource22 NVARCHAR(255) = '' ,@GlobalAttributeAlias22 NVARCHAR(255) = '' ,@GlobalAttributeSource23 NVARCHAR(255) = '' ,@GlobalAttributeAlias23 NVARCHAR(255) = '' ,@GlobalAttributeSource24 NVARCHAR(255) = '' ,@GlobalAttributeAlias24 NVARCHAR(255) = '' ,@GlobalAttributeSource25 NVARCHAR(255) = '' ,@GlobalAttributeAlias25 NVARCHAR(255) = '' ,@LayoutJSON NVARCHAR(MAX) = '' EXECUTE @RC = dbo.sx_pf_POST_ProductLine @Username, @ProductLineID, @FactoryID, @NameShort, @NameLong, @CommentUser, @CommentDev, @ResponsiblePerson, @ImageName, @DefaultTemplate, @GlobalAttributeSource1, @GlobalAttributeAlias1, @GlobalAttributeSource2, @GlobalAttributeAlias2, @GlobalAttributeSource3, @GlobalAttributeAlias3, @GlobalAttributeSource4, @GlobalAttributeAlias4, @GlobalAttributeSource5, @GlobalAttributeAlias5, @GlobalAttributeSource6, @GlobalAttributeAlias6, @GlobalAttributeSource7, @GlobalAttributeAlias7, @GlobalAttributeSource8, @GlobalAttributeAlias8, @GlobalAttributeSource9, @GlobalAttributeAlias9, @GlobalAttributeSource10, @GlobalAttributeAlias10, @GlobalAttributeSource11, @GlobalAttributeAlias11, @GlobalAttributeSource12, @GlobalAttributeAlias12, @GlobalAttributeSource13, @GlobalAttributeAlias13, @GlobalAttributeSource14, @GlobalAttributeAlias14, @GlobalAttributeSource15, @GlobalAttributeAlias15, @GlobalAttributeSource16, @GlobalAttributeAlias16, @GlobalAttributeSource17, @GlobalAttributeAlias17, @GlobalAttributeSource18, @GlobalAttributeAlias18, @GlobalAttributeSource19, @GlobalAttributeAlias19, @GlobalAttributeSource20, @GlobalAttributeAlias20, @GlobalAttributeSource21, @GlobalAttributeAlias21, @GlobalAttributeSource22, @GlobalAttributeAlias22, @GlobalAttributeSource23, @GlobalAttributeAlias23, @GlobalAttributeSource24, @GlobalAttributeAlias24, @GlobalAttributeSource25, @GlobalAttributeAlias25, @LayoutJSON PRINT @RC Testcall Documentation SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'dbo', 'PROCEDURE', 'sx_pf_POST_ProductLine',NULL,NULL) UNION ALL SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'dbo', 'PROCEDURE', 'sx_pf_POST_ProductLine','PARAMETER',NULL) */ CREATE PROCEDURE dbo.sx_pf_POST_ProductLine @Username NVARCHAR(255), @ProductLineID NVARCHAR(255), @FactoryID NVARCHAR(255), @NameShort NVARCHAR(255), @NameLong NVARCHAR(255), @CommentUser NVARCHAR(MAX), @CommentDev NVARCHAR(MAX), @ResponsiblePerson NVARCHAR(255), @ImageName NVARCHAR(255), @DefaultTemplate NVARCHAR(255), @GlobalAttributeSource1 NVARCHAR(255), @GlobalAttributeAlias1 NVARCHAR(255), @GlobalAttributeSource2 NVARCHAR(255), @GlobalAttributeAlias2 NVARCHAR(255), @GlobalAttributeSource3 NVARCHAR(255), @GlobalAttributeAlias3 NVARCHAR(255), @GlobalAttributeSource4 NVARCHAR(255), @GlobalAttributeAlias4 NVARCHAR(255), @GlobalAttributeSource5 NVARCHAR(255), @GlobalAttributeAlias5 NVARCHAR(255), @GlobalAttributeSource6 NVARCHAR(255), @GlobalAttributeAlias6 NVARCHAR(255), @GlobalAttributeSource7 NVARCHAR(255), @GlobalAttributeAlias7 NVARCHAR(255), @GlobalAttributeSource8 NVARCHAR(255), @GlobalAttributeAlias8 NVARCHAR(255), @GlobalAttributeSource9 NVARCHAR(255), @GlobalAttributeAlias9 NVARCHAR(255), @GlobalAttributeSource10 NVARCHAR(255), @GlobalAttributeAlias10 NVARCHAR(255), @GlobalAttributeSource11 NVARCHAR(255), @GlobalAttributeAlias11 NVARCHAR(255), @GlobalAttributeSource12 NVARCHAR(255), @GlobalAttributeAlias12 NVARCHAR(255), @GlobalAttributeSource13 NVARCHAR(255), @GlobalAttributeAlias13 NVARCHAR(255), @GlobalAttributeSource14 NVARCHAR(255), @GlobalAttributeAlias14 NVARCHAR(255), @GlobalAttributeSource15 NVARCHAR(255), @GlobalAttributeAlias15 NVARCHAR(255), @GlobalAttributeSource16 NVARCHAR(255), @GlobalAttributeAlias16 NVARCHAR(255), @GlobalAttributeSource17 NVARCHAR(255), @GlobalAttributeAlias17 NVARCHAR(255), @GlobalAttributeSource18 NVARCHAR(255), @GlobalAttributeAlias18 NVARCHAR(255), @GlobalAttributeSource19 NVARCHAR(255), @GlobalAttributeAlias19 NVARCHAR(255), @GlobalAttributeSource20 NVARCHAR(255), @GlobalAttributeAlias20 NVARCHAR(255), @GlobalAttributeSource21 NVARCHAR(255), @GlobalAttributeAlias21 NVARCHAR(255), @GlobalAttributeSource22 NVARCHAR(255), @GlobalAttributeAlias22 NVARCHAR(255), @GlobalAttributeSource23 NVARCHAR(255), @GlobalAttributeAlias23 NVARCHAR(255), @GlobalAttributeSource24 NVARCHAR(255), @GlobalAttributeAlias24 NVARCHAR(255), @GlobalAttributeSource25 NVARCHAR(255), @GlobalAttributeAlias25 NVARCHAR(255), @LayoutJSON NVARCHAR(MAX) = NULL -- Kompatibilität für alte Versionen AS BEGIN SET NOCOUNT ON; DECLARE @TransactUsername NVARCHAR(255)= N''; DECLARE @FactoryKey INT = 0; DECLARE @ProductLineKey INT = 0; DECLARE @ProcedureName NVARCHAR (255) = OBJECT_SCHEMA_NAME(@@PROCID) + N'.' + OBJECT_NAME(@@PROCID); DECLARE @ParameterString NVARCHAR (MAX) = N'''' + ISNULL(@Username, N'NULL') + N''',''' + ISNULL(@ProductLineID, N'NULL') + N''',''' + ISNULL(@FactoryID, N'NULL') + N''',''' + ISNULL(@NameShort, N'NULL') + N''',''' + ISNULL(@NameLong, N'NULL') + N''',''' + ISNULL(@CommentUser, N'NULL') + N''',''' + ISNULL(@CommentDev, N'NULL') + N''',''' + ISNULL(@ResponsiblePerson, N'NULL') + N''',''' + ISNULL(@ImageName, N'NULL') + N''',''' + ISNULL(@DefaultTemplate, N'NULL') + N''',''' + ISNULL(@GlobalAttributeSource1, N'NULL') + N''',''' + ISNULL(@GlobalAttributeAlias1, N'NULL') + N''',''' + ISNULL(@GlobalAttributeSource2, N'NULL') + N''',''' + ISNULL(@GlobalAttributeAlias2, N'NULL') + N''',''' + ISNULL(@GlobalAttributeSource3, N'NULL') + N''',''' + ISNULL(@GlobalAttributeAlias3, N'NULL') + N''',''' + ISNULL(@GlobalAttributeSource4, N'NULL') + N''',''' + ISNULL(@GlobalAttributeAlias4, N'NULL') + N''',''' + ISNULL(@GlobalAttributeSource5, N'NULL') + N''',''' + ISNULL(@GlobalAttributeAlias5, N'NULL') + N''',''' + ISNULL(@GlobalAttributeSource6, N'NULL') + N''',''' + ISNULL(@GlobalAttributeAlias6, N'NULL') + N''',''' + ISNULL(@GlobalAttributeSource7, N'NULL') + N''',''' + ISNULL(@GlobalAttributeAlias7, N'NULL') + N''',''' + ISNULL(@GlobalAttributeSource8, N'NULL') + N''',''' + ISNULL(@GlobalAttributeAlias8, N'NULL') + N''',''' + ISNULL(@GlobalAttributeSource9, N'NULL') + N''',''' + ISNULL(@GlobalAttributeAlias9, N'NULL') + N''',''' + ISNULL(@GlobalAttributeSource10, N'NULL') + N''',''' + ISNULL(@GlobalAttributeAlias10, N'NULL') + N''',''' + ISNULL(@GlobalAttributeSource11, N'NULL') + N''',''' + ISNULL(@GlobalAttributeAlias11, N'NULL') + N''',''' + ISNULL(@GlobalAttributeSource12, N'NULL') + N''',''' + ISNULL(@GlobalAttributeAlias12, N'NULL') + N''',''' + ISNULL(@GlobalAttributeSource13, N'NULL') + N''',''' + ISNULL(@GlobalAttributeAlias13, N'NULL') + N''',''' + ISNULL(@GlobalAttributeSource14, N'NULL') + N''',''' + ISNULL(@GlobalAttributeAlias14, N'NULL') + N''',''' + ISNULL(@GlobalAttributeSource15, N'NULL') + N''',''' + ISNULL(@GlobalAttributeAlias15, N'NULL') + N''',''' + ISNULL(@GlobalAttributeSource16, N'NULL') + N''',''' + ISNULL(@GlobalAttributeAlias16, N'NULL') + N''',''' + ISNULL(@GlobalAttributeSource17, N'NULL') + N''',''' + ISNULL(@GlobalAttributeAlias17, N'NULL') + N''',''' + ISNULL(@GlobalAttributeSource18, N'NULL') + N''',''' + ISNULL(@GlobalAttributeAlias18, N'NULL') + N''',''' + ISNULL(@GlobalAttributeSource19, N'NULL') + N''',''' + ISNULL(@GlobalAttributeAlias19, N'NULL') + N''',''' + ISNULL(@GlobalAttributeSource20, N'NULL') + N''',''' + ISNULL(@GlobalAttributeAlias20, N'NULL') + N''',''' + ISNULL(@GlobalAttributeSource21, N'NULL') + N''',''' + ISNULL(@GlobalAttributeAlias21, N'NULL') + N''',''' + ISNULL(@GlobalAttributeSource22, N'NULL') + N''',''' + ISNULL(@GlobalAttributeAlias22, N'NULL') + N''',''' + ISNULL(@GlobalAttributeSource23, N'NULL') + N''',''' + ISNULL(@GlobalAttributeAlias23, N'NULL') + N''',''' + ISNULL(@GlobalAttributeSource24, N'NULL') + N''',''' + ISNULL(@GlobalAttributeAlias24, N'NULL') + N''',''' + ISNULL(@GlobalAttributeSource25, N'NULL') + N''',''' + ISNULL(@GlobalAttributeAlias25, N'NULL') + N''''; DECLARE @EffectedRows INT = 0; -- SET during Execution DECLARE @ResultCode INT = 501; -- SET during Execution DECLARE @TimestampCall DATETIME = GETUTCDATE(); DECLARE @Comment NVARCHAR(2000) = N''; -- SET during Execution -- STEP 0.1 - NULL Protection IF @Username IS NULL SET @Username = N''; IF @ProductLineID IS NULL SET @ProductLineID = N''; IF @FactoryID IS NULL SET @FactoryID = N''; IF @NameShort IS NULL SET @NameShort = N''; IF @NameLong IS NULL SET @NameLong = N''; IF @CommentUser IS NULL SET @CommentUser = N''; IF @CommentDev IS NULL SET @CommentDev = N''; IF @ResponsiblePerson IS NULL SET @ResponsiblePerson = N''; IF @ImageName IS NULL SET @ImageName = N''; IF @DefaultTemplate IS NULL SET @DefaultTemplate = N''; IF @GlobalAttributeSource1 IS NULL SET @GlobalAttributeSource1 = N''; IF @GlobalAttributeAlias1 IS NULL SET @GlobalAttributeAlias1 = N''; IF @GlobalAttributeSource2 IS NULL SET @GlobalAttributeSource2 = N''; IF @GlobalAttributeAlias2 IS NULL SET @GlobalAttributeAlias2 = N''; IF @GlobalAttributeSource3 IS NULL SET @GlobalAttributeSource3 = N''; IF @GlobalAttributeAlias3 IS NULL SET @GlobalAttributeAlias3 = N''; IF @GlobalAttributeSource4 IS NULL SET @GlobalAttributeSource4 = N''; IF @GlobalAttributeAlias4 IS NULL SET @GlobalAttributeAlias4 = N''; IF @GlobalAttributeSource5 IS NULL SET @GlobalAttributeSource5 = N''; IF @GlobalAttributeAlias5 IS NULL SET @GlobalAttributeAlias5 = N''; IF @GlobalAttributeSource6 IS NULL SET @GlobalAttributeSource6 = N''; IF @GlobalAttributeAlias6 IS NULL SET @GlobalAttributeAlias6 = N''; IF @GlobalAttributeSource7 IS NULL SET @GlobalAttributeSource7 = N''; IF @GlobalAttributeAlias7 IS NULL SET @GlobalAttributeAlias7 = N''; IF @GlobalAttributeSource8 IS NULL SET @GlobalAttributeSource8 = N''; IF @GlobalAttributeAlias8 IS NULL SET @GlobalAttributeAlias8 = N''; IF @GlobalAttributeSource9 IS NULL SET @GlobalAttributeSource9 = N''; IF @GlobalAttributeAlias9 IS NULL SET @GlobalAttributeAlias9 = N''; IF @GlobalAttributeSource10 IS NULL SET @GlobalAttributeSource10 = N''; IF @GlobalAttributeAlias10 IS NULL SET @GlobalAttributeAlias10 = N''; IF @GlobalAttributeSource11 IS NULL SET @GlobalAttributeSource11 = N''; IF @GlobalAttributeAlias11 IS NULL SET @GlobalAttributeAlias11 = N''; IF @GlobalAttributeSource12 IS NULL SET @GlobalAttributeSource12 = N''; IF @GlobalAttributeAlias12 IS NULL SET @GlobalAttributeAlias12 = N''; IF @GlobalAttributeSource13 IS NULL SET @GlobalAttributeSource13 = N''; IF @GlobalAttributeAlias13 IS NULL SET @GlobalAttributeAlias13 = N''; IF @GlobalAttributeSource14 IS NULL SET @GlobalAttributeSource14 = N''; IF @GlobalAttributeAlias14 IS NULL SET @GlobalAttributeAlias14 = N''; IF @GlobalAttributeSource15 IS NULL SET @GlobalAttributeSource15 = N''; IF @GlobalAttributeAlias15 IS NULL SET @GlobalAttributeAlias15 = N''; IF @GlobalAttributeSource16 IS NULL SET @GlobalAttributeSource16 = N''; IF @GlobalAttributeAlias16 IS NULL SET @GlobalAttributeAlias16 = N''; IF @GlobalAttributeSource17 IS NULL SET @GlobalAttributeSource17 = N''; IF @GlobalAttributeAlias17 IS NULL SET @GlobalAttributeAlias17 = N''; IF @GlobalAttributeSource18 IS NULL SET @GlobalAttributeSource18 = N''; IF @GlobalAttributeAlias18 IS NULL SET @GlobalAttributeAlias18 = N''; IF @GlobalAttributeSource19 IS NULL SET @GlobalAttributeSource19 = N''; IF @GlobalAttributeAlias19 IS NULL SET @GlobalAttributeAlias19 = N''; IF @GlobalAttributeSource20 IS NULL SET @GlobalAttributeSource20 = N''; IF @GlobalAttributeAlias20 IS NULL SET @GlobalAttributeAlias20 = N''; IF @GlobalAttributeSource21 IS NULL SET @GlobalAttributeSource21 = N''; IF @GlobalAttributeAlias21 IS NULL SET @GlobalAttributeAlias21 = N''; IF @GlobalAttributeSource22 IS NULL SET @GlobalAttributeSource22 = N''; IF @GlobalAttributeAlias22 IS NULL SET @GlobalAttributeAlias22 = N''; IF @GlobalAttributeSource23 IS NULL SET @GlobalAttributeSource23 = N''; IF @GlobalAttributeAlias23 IS NULL SET @GlobalAttributeAlias23 = N''; IF @GlobalAttributeSource24 IS NULL SET @GlobalAttributeSource24 = N''; IF @GlobalAttributeAlias24 IS NULL SET @GlobalAttributeAlias24 = N''; IF @GlobalAttributeSource25 IS NULL SET @GlobalAttributeSource25 = N''; IF @GlobalAttributeAlias25 IS NULL SET @GlobalAttributeAlias25 = N''; IF @LayoutJSON IS NULL SET @LayoutJSON = N''; BEGIN TRY BEGIN TRANSACTION sx_pf_POST_ProductLine; -- STEP 0.2 - Protect input parameters SET @ProductLineID = dbo.sx_pf_pProtectID (@ProductLineID); SET @FactoryID = dbo.sx_pf_pProtectID (@FactoryID); SET @NameShort = dbo.sx_pf_pProtectString (@NameShort); SET @NameLong = dbo.sx_pf_pProtectString (@NameLong); SET @CommentUser = dbo.sx_pf_pProtectString (@CommentUser); SET @CommentDev = dbo.sx_pf_pProtectString (@CommentDev); SET @ResponsiblePerson = dbo.sx_pf_pProtectString (@ResponsiblePerson); SET @ImageName = dbo.sx_pf_pProtectString (@ImageName); SET @DefaultTemplate = dbo.sx_pf_pProtectString (@DefaultTemplate); SET @GlobalAttributeAlias1 = dbo.sx_pf_pProtectString (@GlobalAttributeAlias1); SET @GlobalAttributeAlias2 = dbo.sx_pf_pProtectString (@GlobalAttributeAlias2); SET @GlobalAttributeAlias3 = dbo.sx_pf_pProtectString (@GlobalAttributeAlias3); SET @GlobalAttributeAlias4 = dbo.sx_pf_pProtectString (@GlobalAttributeAlias4); SET @GlobalAttributeAlias5 = dbo.sx_pf_pProtectString (@GlobalAttributeAlias5); SET @GlobalAttributeAlias6 = dbo.sx_pf_pProtectString (@GlobalAttributeAlias6); SET @GlobalAttributeAlias7 = dbo.sx_pf_pProtectString (@GlobalAttributeAlias7); SET @GlobalAttributeAlias8 = dbo.sx_pf_pProtectString (@GlobalAttributeAlias8); SET @GlobalAttributeAlias9 = dbo.sx_pf_pProtectString (@GlobalAttributeAlias9); SET @GlobalAttributeAlias10 = dbo.sx_pf_pProtectString (@GlobalAttributeAlias10); SET @GlobalAttributeAlias11 = dbo.sx_pf_pProtectString (@GlobalAttributeAlias11); SET @GlobalAttributeAlias12 = dbo.sx_pf_pProtectString (@GlobalAttributeAlias12); SET @GlobalAttributeAlias13 = dbo.sx_pf_pProtectString (@GlobalAttributeAlias13); SET @GlobalAttributeAlias14 = dbo.sx_pf_pProtectString (@GlobalAttributeAlias14); SET @GlobalAttributeAlias15 = dbo.sx_pf_pProtectString (@GlobalAttributeAlias15); SET @GlobalAttributeAlias16 = dbo.sx_pf_pProtectString (@GlobalAttributeAlias16); SET @GlobalAttributeAlias17 = dbo.sx_pf_pProtectString (@GlobalAttributeAlias17); SET @GlobalAttributeAlias18 = dbo.sx_pf_pProtectString (@GlobalAttributeAlias18); SET @GlobalAttributeAlias19 = dbo.sx_pf_pProtectString (@GlobalAttributeAlias19); SET @GlobalAttributeAlias20 = dbo.sx_pf_pProtectString (@GlobalAttributeAlias20); SET @GlobalAttributeAlias21 = dbo.sx_pf_pProtectString (@GlobalAttributeAlias21); SET @GlobalAttributeAlias22 = dbo.sx_pf_pProtectString (@GlobalAttributeAlias22); SET @GlobalAttributeAlias23 = dbo.sx_pf_pProtectString (@GlobalAttributeAlias23); SET @GlobalAttributeAlias24 = dbo.sx_pf_pProtectString (@GlobalAttributeAlias24); SET @GlobalAttributeAlias25 = dbo.sx_pf_pProtectString (@GlobalAttributeAlias25); SET @LayoutJSON = dbo.sx_pf_pProtectString (@LayoutJSON); IF @GlobalAttributeSource1 !='<#NV>' SET @GlobalAttributeSource1 = dbo.sx_pf_pProtectID (@GlobalAttributeSource1) IF @GlobalAttributeSource2 !='<#NV>' SET @GlobalAttributeSource2 = dbo.sx_pf_pProtectID (@GlobalAttributeSource2) IF @GlobalAttributeSource3 !='<#NV>' SET @GlobalAttributeSource3 = dbo.sx_pf_pProtectID (@GlobalAttributeSource3) IF @GlobalAttributeSource4 !='<#NV>' SET @GlobalAttributeSource4 = dbo.sx_pf_pProtectID (@GlobalAttributeSource4) IF @GlobalAttributeSource5 !='<#NV>' SET @GlobalAttributeSource5 = dbo.sx_pf_pProtectID (@GlobalAttributeSource5) IF @GlobalAttributeSource6 !='<#NV>' SET @GlobalAttributeSource6 = dbo.sx_pf_pProtectID (@GlobalAttributeSource6) IF @GlobalAttributeSource7 !='<#NV>' SET @GlobalAttributeSource7 = dbo.sx_pf_pProtectID (@GlobalAttributeSource7) IF @GlobalAttributeSource8 !='<#NV>' SET @GlobalAttributeSource8 = dbo.sx_pf_pProtectID (@GlobalAttributeSource8) IF @GlobalAttributeSource9 !='<#NV>' SET @GlobalAttributeSource9 = dbo.sx_pf_pProtectID (@GlobalAttributeSource9) IF @GlobalAttributeSource10 !='<#NV>' SET @GlobalAttributeSource10 = dbo.sx_pf_pProtectID (@GlobalAttributeSource10) IF @GlobalAttributeSource11 !='<#NV>' SET @GlobalAttributeSource11 = dbo.sx_pf_pProtectID (@GlobalAttributeSource11) IF @GlobalAttributeSource12 !='<#NV>' SET @GlobalAttributeSource12 = dbo.sx_pf_pProtectID (@GlobalAttributeSource12) IF @GlobalAttributeSource13 !='<#NV>' SET @GlobalAttributeSource13 = dbo.sx_pf_pProtectID (@GlobalAttributeSource13) IF @GlobalAttributeSource14 !='<#NV>' SET @GlobalAttributeSource14 = dbo.sx_pf_pProtectID (@GlobalAttributeSource14) IF @GlobalAttributeSource15 !='<#NV>' SET @GlobalAttributeSource15 = dbo.sx_pf_pProtectID (@GlobalAttributeSource15) IF @GlobalAttributeSource16 !='<#NV>' SET @GlobalAttributeSource16 = dbo.sx_pf_pProtectID (@GlobalAttributeSource16) IF @GlobalAttributeSource17 !='<#NV>' SET @GlobalAttributeSource17 = dbo.sx_pf_pProtectID (@GlobalAttributeSource17) IF @GlobalAttributeSource18 !='<#NV>' SET @GlobalAttributeSource18 = dbo.sx_pf_pProtectID (@GlobalAttributeSource18) IF @GlobalAttributeSource19 !='<#NV>' SET @GlobalAttributeSource19 = dbo.sx_pf_pProtectID (@GlobalAttributeSource19) IF @GlobalAttributeSource20 !='<#NV>' SET @GlobalAttributeSource20 = dbo.sx_pf_pProtectID (@GlobalAttributeSource20) IF @GlobalAttributeSource21 !='<#NV>' SET @GlobalAttributeSource21 = dbo.sx_pf_pProtectID (@GlobalAttributeSource21) IF @GlobalAttributeSource22 !='<#NV>' SET @GlobalAttributeSource22 = dbo.sx_pf_pProtectID (@GlobalAttributeSource22) IF @GlobalAttributeSource23 !='<#NV>' SET @GlobalAttributeSource23 = dbo.sx_pf_pProtectID (@GlobalAttributeSource23) IF @GlobalAttributeSource24 !='<#NV>' SET @GlobalAttributeSource24 = dbo.sx_pf_pProtectID (@GlobalAttributeSource24) IF @GlobalAttributeSource25 !='<#NV>' SET @GlobalAttributeSource25 = dbo.sx_pf_pProtectID (@GlobalAttributeSource25) IF @FactoryID = N'' OR @ProductLineID = N'' BEGIN SET @ResultCode = 404; RAISERROR('Empty input parameters', 16, 10); END; -- STEP 1.1 - Determine transaction user SELECT @TransactUsername = dbo.sx_pf_Determine_TransactionUsername (@Username); IF @TransactUsername = N'403' BEGIN SET @ResultCode = 403; RAISERROR('Transaction user don`t exists', 16, 10); END; -- STEP 1.2 - Determine keys SELECT @FactoryKey = FactoryKey FROM planning.tdFactories WHERE FactoryID = @FactoryID; SELECT @ProductLineKey = ProductLineKey FROM planning.tdProductLines WHERE FactoryKey = @FactoryKey AND ProductLineID = @ProductLineID; IF @FactoryKey = 0 BEGIN SET @ResultCode = 404; RAISERROR('Keys don`t exists', 16, 10); END; -- STEP 2 - Check rights IF @ProductLineKey = 0 EXEC @ResultCode = sx_pf_pGET_FactoryWriteRight @TransactUsername, @FactoryID; ELSE EXEC @ResultCode = sx_pf_pGET_ProductLineWriteRight @TransactUsername, @FactoryID, @ProductLineID; IF @ResultCode <> 200 RAISERROR('Invalid rights', 16, 10); -- STEP 3.1 - If Product Line does not yet exist, it will be created in minimal configuration IF @ProductLineKey = 0 BEGIN DECLARE @Keys AS TABLE ([Key] INT NOT NULL); INSERT INTO planning.tdProductLines (FactoryKey, ProductLineID, FactoryID, NameShort, NameLong, CommentUser, CommentDev, ResponsiblePerson, ImageName, DefaultTemplate , GlobalAttributeSource1, GlobalAttributeAlias1, GlobalAttributeSource2, GlobalAttributeAlias2, GlobalAttributeSource3, GlobalAttributeAlias3, GlobalAttributeSource4, GlobalAttributeAlias4, GlobalAttributeSource5, GlobalAttributeAlias5 , GlobalAttributeSource6, GlobalAttributeAlias6, GlobalAttributeSource7, GlobalAttributeAlias7, GlobalAttributeSource8, GlobalAttributeAlias8, GlobalAttributeSource9, GlobalAttributeAlias9, GlobalAttributeSource10, GlobalAttributeAlias10 , GlobalAttributeSource11, GlobalAttributeAlias11, GlobalAttributeSource12, GlobalAttributeAlias12, GlobalAttributeSource13, GlobalAttributeAlias13, GlobalAttributeSource14, GlobalAttributeAlias14, GlobalAttributeSource15, GlobalAttributeAlias15 , GlobalAttributeSource16, GlobalAttributeAlias16, GlobalAttributeSource17, GlobalAttributeAlias17, GlobalAttributeSource18, GlobalAttributeAlias18, GlobalAttributeSource19, GlobalAttributeAlias19, GlobalAttributeSource20, GlobalAttributeAlias20 , GlobalAttributeSource21, GlobalAttributeAlias21, GlobalAttributeSource22, GlobalAttributeAlias22, GlobalAttributeSource23, GlobalAttributeAlias23, GlobalAttributeSource24, GlobalAttributeAlias24, GlobalAttributeSource25, GlobalAttributeAlias25 , LayoutJSON ) OUTPUT INSERTED.ProductLineKey INTO @Keys([Key]) VALUES (@FactoryKey, @ProductLineID, @FactoryID, @NameShort, '', '', '', '', '', '' , '', '', '', '', '', '', '', '', '', '' , '', '', '', '', '', '', '', '', '', '' , '', '', '', '', '', '', '', '', '', '' , '', '', '', '', '', '', '', '', '', '' , '', '', '', '', '', '', '', '', '', '' , '' ); SELECT TOP (1) @ProductLineKey = [Key] FROM @Keys ORDER BY [Key]; SET @EffectedRows = 1; SET @Resultcode = 201; END; -- STEP 3.2 - Update values UPDATE planning.tdProductLines SET NameShort = IIF(@NameShort = N'<#NV>', NameShort, @NameShort) , NameLong = IIF(@NameLong = N'<#NV>', NameLong, @NameLong) , CommentUser = IIF(@CommentUser = N'<#NV>', CommentUser, @CommentUser) , CommentDev = IIF(@CommentDev = N'<#NV>', CommentDev, @CommentDev) , ResponsiblePerson = IIF(@ResponsiblePerson = N'<#NV>', ResponsiblePerson, @ResponsiblePerson) , ImageName = IIF(@ImageName = N'<#NV>', ImageName, @ImageName) , DefaultTemplate = IIF(@DefaultTemplate = N'<#NV>', DefaultTemplate, @DefaultTemplate) , GlobalAttributeSource1 = IIF(@GlobalAttributeSource1 = N'<#NV>', GlobalAttributeSource1, @GlobalAttributeSource1) , GlobalAttributeAlias1 = IIF(@GlobalAttributeAlias1 = N'<#NV>', GlobalAttributeAlias1, @GlobalAttributeAlias1) , GlobalAttributeSource2 = IIF(@GlobalAttributeSource2 = N'<#NV>', GlobalAttributeSource2, @GlobalAttributeSource2) , GlobalAttributeAlias2 = IIF(@GlobalAttributeAlias2 = N'<#NV>', GlobalAttributeAlias2, @GlobalAttributeAlias2) , GlobalAttributeSource3 = IIF(@GlobalAttributeSource3 = N'<#NV>', GlobalAttributeSource3, @GlobalAttributeSource3) , GlobalAttributeAlias3 = IIF(@GlobalAttributeAlias3 = N'<#NV>', GlobalAttributeAlias3, @GlobalAttributeAlias3) , GlobalAttributeSource4 = IIF(@GlobalAttributeSource4 = N'<#NV>', GlobalAttributeSource4, @GlobalAttributeSource4) , GlobalAttributeAlias4 = IIF(@GlobalAttributeAlias4 = N'<#NV>', GlobalAttributeAlias4, @GlobalAttributeAlias4) , GlobalAttributeSource5 = IIF(@GlobalAttributeSource5 = N'<#NV>', GlobalAttributeSource5, @GlobalAttributeSource5) , GlobalAttributeAlias5 = IIF(@GlobalAttributeAlias5 = N'<#NV>', GlobalAttributeAlias5, @GlobalAttributeAlias5) , GlobalAttributeSource6 = IIF(@GlobalAttributeSource6 = N'<#NV>', GlobalAttributeSource6, @GlobalAttributeSource6) , GlobalAttributeAlias6 = IIF(@GlobalAttributeAlias6 = N'<#NV>', GlobalAttributeAlias6, @GlobalAttributeAlias6) , GlobalAttributeSource7 = IIF(@GlobalAttributeSource7 = N'<#NV>', GlobalAttributeSource7, @GlobalAttributeSource7) , GlobalAttributeAlias7 = IIF(@GlobalAttributeAlias7 = N'<#NV>', GlobalAttributeAlias7, @GlobalAttributeAlias7) , GlobalAttributeSource8 = IIF(@GlobalAttributeSource8 = N'<#NV>', GlobalAttributeSource8, @GlobalAttributeSource8) , GlobalAttributeAlias8 = IIF(@GlobalAttributeAlias8 = N'<#NV>', GlobalAttributeAlias8, @GlobalAttributeAlias8) , GlobalAttributeSource9 = IIF(@GlobalAttributeSource9 = N'<#NV>', GlobalAttributeSource9, @GlobalAttributeSource9) , GlobalAttributeAlias9 = IIF(@GlobalAttributeAlias9 = N'<#NV>', GlobalAttributeAlias9, @GlobalAttributeAlias9) , GlobalAttributeSource10 = IIF(@GlobalAttributeSource10 = N'<#NV>', GlobalAttributeSource10, @GlobalAttributeSource10) , GlobalAttributeAlias10 = IIF(@GlobalAttributeAlias10 = N'<#NV>', GlobalAttributeAlias10, @GlobalAttributeAlias10) , GlobalAttributeSource11 = IIF(@GlobalAttributeSource11 = N'<#NV>', GlobalAttributeSource11, @GlobalAttributeSource11) , GlobalAttributeAlias11 = IIF(@GlobalAttributeAlias11 = N'<#NV>', GlobalAttributeAlias11, @GlobalAttributeAlias11) , GlobalAttributeSource12 = IIF(@GlobalAttributeSource12 = N'<#NV>', GlobalAttributeSource12, @GlobalAttributeSource12) , GlobalAttributeAlias12 = IIF(@GlobalAttributeAlias12 = N'<#NV>', GlobalAttributeAlias12, @GlobalAttributeAlias12) , GlobalAttributeSource13 = IIF(@GlobalAttributeSource13 = N'<#NV>', GlobalAttributeSource13, @GlobalAttributeSource13) , GlobalAttributeAlias13 = IIF(@GlobalAttributeAlias13 = N'<#NV>', GlobalAttributeAlias13, @GlobalAttributeAlias13) , GlobalAttributeSource14 = IIF(@GlobalAttributeSource14 = N'<#NV>', GlobalAttributeSource14, @GlobalAttributeSource14) , GlobalAttributeAlias14 = IIF(@GlobalAttributeAlias14 = N'<#NV>', GlobalAttributeAlias14, @GlobalAttributeAlias14) , GlobalAttributeSource15 = IIF(@GlobalAttributeSource15 = N'<#NV>', GlobalAttributeSource15, @GlobalAttributeSource15) , GlobalAttributeAlias15 = IIF(@GlobalAttributeAlias15 = N'<#NV>', GlobalAttributeAlias15, @GlobalAttributeAlias15) , GlobalAttributeSource16 = IIF(@GlobalAttributeSource16 = N'<#NV>', GlobalAttributeSource16, @GlobalAttributeSource16) , GlobalAttributeAlias16 = IIF(@GlobalAttributeAlias16 = N'<#NV>', GlobalAttributeAlias16, @GlobalAttributeAlias16) , GlobalAttributeSource17 = IIF(@GlobalAttributeSource17 = N'<#NV>', GlobalAttributeSource17, @GlobalAttributeSource17) , GlobalAttributeAlias17 = IIF(@GlobalAttributeAlias17 = N'<#NV>', GlobalAttributeAlias17, @GlobalAttributeAlias17) , GlobalAttributeSource18 = IIF(@GlobalAttributeSource18 = N'<#NV>', GlobalAttributeSource18, @GlobalAttributeSource18) , GlobalAttributeAlias18 = IIF(@GlobalAttributeAlias18 = N'<#NV>', GlobalAttributeAlias18, @GlobalAttributeAlias18) , GlobalAttributeSource19 = IIF(@GlobalAttributeSource19 = N'<#NV>', GlobalAttributeSource19, @GlobalAttributeSource19) , GlobalAttributeAlias19 = IIF(@GlobalAttributeAlias19 = N'<#NV>', GlobalAttributeAlias19, @GlobalAttributeAlias19) , GlobalAttributeSource20 = IIF(@GlobalAttributeSource20 = N'<#NV>', GlobalAttributeSource20, @GlobalAttributeSource20) , GlobalAttributeAlias20 = IIF(@GlobalAttributeAlias20 = N'<#NV>', GlobalAttributeAlias20, @GlobalAttributeAlias20) , GlobalAttributeSource21 = IIF(@GlobalAttributeSource21 = N'<#NV>', GlobalAttributeSource21, @GlobalAttributeSource21) , GlobalAttributeAlias21 = IIF(@GlobalAttributeAlias21 = N'<#NV>', GlobalAttributeAlias21, @GlobalAttributeAlias21) , GlobalAttributeSource22 = IIF(@GlobalAttributeSource22 = N'<#NV>', GlobalAttributeSource22, @GlobalAttributeSource22) , GlobalAttributeAlias22 = IIF(@GlobalAttributeAlias22 = N'<#NV>', GlobalAttributeAlias22, @GlobalAttributeAlias22) , GlobalAttributeSource23 = IIF(@GlobalAttributeSource23 = N'<#NV>', GlobalAttributeSource23, @GlobalAttributeSource23) , GlobalAttributeAlias23 = IIF(@GlobalAttributeAlias23 = N'<#NV>', GlobalAttributeAlias23, @GlobalAttributeAlias23) , GlobalAttributeSource24 = IIF(@GlobalAttributeSource24 = N'<#NV>', GlobalAttributeSource24, @GlobalAttributeSource24) , GlobalAttributeAlias24 = IIF(@GlobalAttributeAlias24 = N'<#NV>', GlobalAttributeAlias24, @GlobalAttributeAlias24) , GlobalAttributeSource25 = IIF(@GlobalAttributeSource25 = N'<#NV>', GlobalAttributeSource25, @GlobalAttributeSource25) , GlobalAttributeAlias25 = IIF(@GlobalAttributeAlias25 = N'<#NV>', GlobalAttributeAlias25, @GlobalAttributeAlias25) , LayoutJSON = IIF(@LayoutJSON = N'<#NV>', LayoutJSON, @LayoutJSON) FROM planning.tdProductLines WHERE ProductLineKey = @ProductLineKey; SET @EffectedRows += @@ROWCOUNT; -- STEP 3.3 - Rights materialize (only possible for users with Factory WriteRight or Cluster Admin) EXEC system.spMaterialize_trUserRights; COMMIT TRANSACTION sx_pf_POST_ProductLine; END TRY BEGIN CATCH DECLARE @Error_state INT = ERROR_STATE(); SET @Comment = ERROR_MESSAGE(); ROLLBACK TRANSACTION sx_pf_POST_ProductLine; IF @Error_state <> 10 BEGIN SET @ResultCode = 500; PRINT 'Rollback due to not executable command.'; END ELSE IF @ResultCode IS NULL OR @ResultCode/100 = 2 BEGIN SET @ResultCode = 500; END; END CATCH EXEC dbo.sx_pf_pPOST_API_LogEntry @Username, @TransactUsername, @ProcedureName, @ParameterString, @EffectedRows, @ResultCode, @TimestampCall, @Comment; RETURN @ResultCode; END GO ALTER AUTHORIZATION ON OBJECT ::dbo.sx_pf_POST_ProductLine TO db_octservice; GO -- SET documentation variables *********************************************************************** DECLARE @level0name NVARCHAR(255) = N'dbo' -- enter schema name of the table ,@level1name NVARCHAR(255) = N'sx_pf_POST_ProductLine' -- enter procedure name ,@SX_Owner NVARCHAR(255) = N'OCT.core' -- enter owner name of the procedure from list (OCT.core, OCT.modules, Custom) ,@SX_Module NVARCHAR(255) = N'CORE' -- enter module name as free text (CORE,FIN, DEBKRED, HR, ...) ,@SX_ShipmentFlag INT = 1 -- 0 = Demo object - out of shipment process -- STANDARD OBJECTS -- 1 = shiped from saxess standard without modification -- 2 = shiped from saxess standard modified FOR customer from saxess -- 3 = shiped from saxess standard modified FOR customer from partner -- 4 = shiped from saxess standard modified FROM customer themself for own needs -- CUSTOM OBJECTS -- 10 = shiped from saxess as customer specific object -- 11 = shiped from partner as customer specific object -- 12 = shiped from customer as own specific object ,@SX_UserHint NVARCHAR(2000) = N'' -- optional, fill if Procedure shall be offerend for end user (e.g. for Pivot / Datagrid usage) -- KEEP this default constants ************************************************************************* DECLARE @name NVARCHAR(255) = N'MS_Description' ,@level0type NVARCHAR(255) = N'SCHEMA' ,@level1type NVARCHAR(255) = N'PROCEDURE' ,@level2type NVARCHAR(255) = N'PARAMETER' ,@level2name NVARCHAR(255) = N'' ,@value NVARCHAR(1000) = N''; SET @value = @SX_Owner; EXEC sys.sp_addextendedproperty N'SX_Owner' ,@value,@level0type,@level0name,@level1type,@level1name; SET @value = @SX_Module; EXEC sys.sp_addextendedproperty N'SX_Module' ,@value,@level0type,@level0name,@level1type,@level1name; SET @value = @SX_ShipmentFlag; EXEC sys.sp_addextendedproperty N'SX_ShipmentFlag' ,@value,@level0type,@level0name,@level1type,@level1name; SET @value = @SX_UserHint; EXEC sys.sp_addextendedproperty N'SX_UserHint' ,@value,@level0type,@level0name,@level1type,@level1name; -- SET documententation ************************************************************************* -- SET Procedure documentation SET @value = N'POST Operation for ProductLine Not existing ProductLine will be created, existing gets an Update If attributes passed with value <#NV>, it will be ignored during the update and maintain the existing value, during insert the attribute is created empty'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name; SET @level2name = N'@Username'; SET @value = N'Username'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@ProductLineID'; SET @value = N'ProductLineID'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@FactoryID'; SET @value = N'FactoryID'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@NameShort'; SET @value = N'NameShort'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@NameLong'; SET @value = N'NameLong'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@CommentUser'; SET @value = N'CommentUser'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@CommentDev'; SET @value = N'CommentDev'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@ResponsiblePerson'; SET @value = N'ResponsiblePerson'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@ImageName'; SET @value = N'ImageName'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@DefaultTemplate'; SET @value = N'DefaultTemplate'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@GlobalAttributeSource1'; SET @value = N'GlobalAttributeSource1'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@GlobalAttributeAlias1'; SET @value = N'GlobalAttributeAlias1'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@GlobalAttributeSource2'; SET @value = N'GlobalAttributeSource2'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@GlobalAttributeAlias2'; SET @value = N'GlobalAttributeAlias2'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@GlobalAttributeSource3'; SET @value = N'GlobalAttributeSource3'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@GlobalAttributeAlias3'; SET @value = N'GlobalAttributeAlias3'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@GlobalAttributeSource4'; SET @value = N'GlobalAttributeSource4'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@GlobalAttributeAlias4'; SET @value = N'GlobalAttributeAlias4'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@GlobalAttributeSource5'; SET @value = N'GlobalAttributeSource5'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@GlobalAttributeAlias5'; SET @value = N'GlobalAttributeAlias5'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@GlobalAttributeSource6'; SET @value = N'GlobalAttributeSource6'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@GlobalAttributeAlias6'; SET @value = N'GlobalAttributeAlias6'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@GlobalAttributeSource7'; SET @value = N'GlobalAttributeSource7'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@GlobalAttributeAlias7'; SET @value = N'GlobalAttributeAlias7'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@GlobalAttributeSource8'; SET @value = N'GlobalAttributeSource8'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@GlobalAttributeAlias8'; SET @value = N'GlobalAttributeAlias8'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@GlobalAttributeSource9'; SET @value = N'GlobalAttributeSource9'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@GlobalAttributeAlias9'; SET @value = N'GlobalAttributeAlias9'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@GlobalAttributeSource10'; SET @value = N'GlobalAttributeSource10'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@GlobalAttributeAlias10'; SET @value = N'GlobalAttributeAlias10'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@GlobalAttributeSource11'; SET @value = N'GlobalAttributeSource11'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@GlobalAttributeAlias11'; SET @value = N'GlobalAttributeAlias11'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@GlobalAttributeSource12'; SET @value = N'GlobalAttributeSource12'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@GlobalAttributeAlias12'; SET @value = N'GlobalAttributeAlias12'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@GlobalAttributeSource13'; SET @value = N'GlobalAttributeSource13'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@GlobalAttributeAlias13'; SET @value = N'GlobalAttributeAlias13'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@GlobalAttributeSource14'; SET @value = N'GlobalAttributeSource14'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@GlobalAttributeAlias14'; SET @value = N'GlobalAttributeAlias14'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@GlobalAttributeSource15'; SET @value = N'GlobalAttributeSource15'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@GlobalAttributeAlias15'; SET @value = N'GlobalAttributeAlias15'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@GlobalAttributeSource16'; SET @value = N'GlobalAttributeSource16'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@GlobalAttributeAlias16'; SET @value = N'GlobalAttributeAlias16'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@GlobalAttributeSource17'; SET @value = N'GlobalAttributeSource17'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@GlobalAttributeAlias17'; SET @value = N'GlobalAttributeAlias17'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@GlobalAttributeSource18'; SET @value = N'GlobalAttributeSource18'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@GlobalAttributeAlias18'; SET @value = N'GlobalAttributeAlias18'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@GlobalAttributeSource19'; SET @value = N'GlobalAttributeSource19'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@GlobalAttributeAlias19'; SET @value = N'GlobalAttributeAlias19'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@GlobalAttributeSource20'; SET @value = N'GlobalAttributeSource20'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@GlobalAttributeAlias20'; SET @value = N'GlobalAttributeAlias20'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@GlobalAttributeSource21'; SET @value = N'GlobalAttributeSource21'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@GlobalAttributeAlias21'; SET @value = N'GlobalAttributeAlias21'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@GlobalAttributeSource22'; SET @value = N'GlobalAttributeSource22'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@GlobalAttributeAlias22'; SET @value = N'GlobalAttributeAlias22'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@GlobalAttributeSource23'; SET @value = N'GlobalAttributeSource23'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@GlobalAttributeAlias23'; SET @value = N'GlobalAttributeAlias23'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@GlobalAttributeSource24'; SET @value = N'GlobalAttributeSource24'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@GlobalAttributeAlias24'; SET @value = N'GlobalAttributeAlias24'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@GlobalAttributeSource25'; SET @value = N'GlobalAttributeSource25'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; SET @level2name = N'@GlobalAttributeAlias25'; SET @value = N'GlobalAttributeAlias25'; EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name; GO UPDATE system.tSettings SET ValueText = '5.11.20', ValueInt = 51120 WHERE SettingID = 'DBVersion'