DROP PROCEDURE IF EXISTS system.spDELETE_Connector;
GO

/*
    Procedure to DELETE a connector including all queries

    Saxess Software GmbH

    Testcall Procedure
        DECLARE @RC INT;
        EXEC @RC = system.spDELETE_Connector
                @Username = N'SQL'
              , @SourceSystemID = N'NAVISION'
              , @SourceSystemType = N'MSSQL'
              , @ModuleID = N'FIN'
        PRINT @RC

        SELECT * FROM system.tConnectors
        SELECT * FROM system.tConnectorQueries

    Testcall Documentation
        SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'system', 'PROCEDURE', 'spDELETE_Connector', NULL, NULL)
        UNION ALL
        SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'system', 'PROCEDURE', 'spDELETE_Connector', 'PARAMETER', NULL)
*/

CREATE PROCEDURE system.spDELETE_Connector
    @Username           NVARCHAR(255)
  , @SourceSystemID     NVARCHAR(50) = N''
  , @SourceSystemType   NVARCHAR(50) = N''
  , @ModuleID           NVARCHAR(50) = N''
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, @SourceSystemID, @SourceSystemType, @ModuleID;
        EXEC system.spGET_TransactUsername @TransactUsername OUTPUT, @Username;
        
        -- Input parameter handling
        SET @SourceSystemID = COALESCE(dbo.sx_pf_pProtectID(@SourceSystemID), N'');
        SET @SourceSystemType = COALESCE(@SourceSystemType, N'');
        SET @ModuleID = COALESCE(dbo.sx_pf_pProtectID(@ModuleID), N'');

        -- Delete connectors, this will also delete connector queries due to key relation
        DELETE FROM system.tConnectors
        WHERE
            SourceSystemID = @SourceSystemID
            AND SourceSystemType = @SourceSystemType
            AND ModuleID = @ModuleID;

        SET @AffectedRows = @@ROWCOUNT;

        IF @AffectedRows = 0
            EXEC system.spSEND_Message N'ERROR', N'Connector doesn''t exist'

        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 N'ERROR', @Comment;

    RETURN @ResultCode;
END;
GO

-- SET documentation variables      ***********************************************************************
DECLARE
     @level0name        NVARCHAR(255)   = N'system'    -- enter schema name of the table
    ,@level1name        NVARCHAR(255)   = N'spDELETE_Connector'         -- 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 delete a connector including all connector queries.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name;
 
 
-- optional SET parameter documentation (only for Core / Standardmodules)
    SET @level2name = N'@SourceSystemID';
    SET @value      = N'SourceSystemID - ID of the source ERP system.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;

    SET @level2name = N'@SourceSystemType';
    SET @value      = N'SourceSystemType - type of connection to the source ERP system (MSSQL, ORACLE, ODBC).';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;

    SET @level2name = N'@ModuleID';
    SET @value      = N'ModuleID - ID of the module.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;
GO


DROP PROCEDURE IF EXISTS system.spDELETE_ConnectorQuery;
GO

/*
    Procedure to DELETE a connector query

    Saxess Software GmbH

    Testcall Procedure
        DECLARE @RC INT;
        EXEC @RC = system.spDELETE_ConnectorQuery
                @Username = N'SQL'
              , @SourceSystemID = N'NAVISION'
              , @SourceSystemType = N'MSSQL'
              , @ModuleID = N'FIN'
              , @QueryName = N'Accounts'
        PRINT @RC

        SELECT * FROM system.tConnectorQueries

    Testcall Documentation
        SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'system', 'PROCEDURE', 'spDELETE_ConnectorQuery', NULL, NULL)
        UNION ALL
        SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'system', 'PROCEDURE', 'spDELETE_ConnectorQuery', 'PARAMETER', NULL)
*/

CREATE PROCEDURE system.spDELETE_ConnectorQuery
    @Username           NVARCHAR(255)
  , @SourceSystemID     NVARCHAR(50) = N''
  , @SourceSystemType   NVARCHAR(50) = N''
  , @ModuleID           NVARCHAR(50) = N''
  , @QueryName          NVARCHAR(255) = N''
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, 5, @Username, @SourceSystemID, @SourceSystemType, @ModuleID, @QueryName;
        EXEC system.spGET_TransactUsername @TransactUsername OUTPUT, @Username;
        
        -- Input parameter handling
        SET @SourceSystemID = COALESCE(dbo.sx_pf_pProtectID(@SourceSystemID), N'');
        SET @SourceSystemType = COALESCE(@SourceSystemType, N'');
        SET @ModuleID = COALESCE(dbo.sx_pf_pProtectID(@ModuleID), N'');

        IF @QueryName = 'GetCompanies'
            EXEC system.spSEND_Message N'ERROR', N'GetCompanies query can''t be deleted'

        -- Look up ConnectorKey
        DECLARE @ConnectorKey BIGINT;
        SELECT @ConnectorKey = ConnectorKey
        FROM system.tConnectors
        WHERE
            SourceSystemID = @SourceSystemID
            AND SourceSystemType = @SourceSystemType
            AND ModuleID = @ModuleID;

        -- Delete connector query
        DELETE FROM system.tConnectorQueries
        WHERE
            ConnectorKey = @ConnectorKey
            AND QueryName = @QueryName;

        SET @AffectedRows = @@ROWCOUNT;

        -- only show error if connector query is not predefined by the module
        IF @AffectedRows = 0 AND NOT EXISTS (
            SELECT 1
            FROM sys.tables t
            JOIN sys.schemas s ON s.schema_id = t.schema_id
            WHERE
                s.name = N'integration'
                AND t.name = N't' + @ModuleID + N'_' + @QueryName
        )
        BEGIN
            EXEC system.spSEND_Message N'ERROR', N'ConnectorQuery doesn''t exist'
        END

        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 N'ERROR', @Comment;

    RETURN @ResultCode;
END;
GO

-- SET documentation variables      ***********************************************************************
DECLARE
     @level0name        NVARCHAR(255)   = N'system'    -- enter schema name of the table
    ,@level1name        NVARCHAR(255)   = N'spDELETE_ConnectorQuery'         -- 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 delete a connector query.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name;
 
 
-- optional SET parameter documentation (only for Core / Standardmodules)
    SET @level2name = N'@SourceSystemID';
    SET @value      = N'SourceSystemID - ID of the source ERP system.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;

    SET @level2name = N'@SourceSystemType';
    SET @value      = N'SourceSystemType - type of connection to the source ERP system (MSSQL, ORACLE, ODBC).';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;

    SET @level2name = N'@ModuleID';
    SET @value      = N'ModuleID - ID of the module.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;

    SET @level2name = N'@QueryName';
    SET @value      = N'Name of the query.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;
GO


DROP PROCEDURE IF EXISTS system.spGET_ConnectorQueryFields;
GO

/*
    Procedure to GET target fields for a connector query

    Saxess Software GmbH

    Testcall Procedure
        DECLARE @RC INT;
        EXEC @RC = system.spGET_ConnectorQueryFields
                @Username = N'SQL'
              , @ModuleID = N'FIN'
              , @QueryName = N'AccountingJournal'
        PRINT @RC

    Testcall Documentation
        SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'system', 'PROCEDURE', 'spGET_ConnectorQueryFields', NULL, NULL)
        UNION ALL
        SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'system', 'PROCEDURE', 'spGET_ConnectorQueryFields', 'PARAMETER', NULL)
*/
 
CREATE PROCEDURE system.spGET_ConnectorQueryFields
    @Username     NVARCHAR(255)
  , @ModuleID     NVARCHAR(50)
  , @QueryName    NVARCHAR(255)
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, 3, @Username, @ModuleID, @QueryName;
        EXEC system.spGET_TransactUsername @TransactUsername OUTPUT, @Username;
        
        -- Input parameter handling
        SET @ModuleID = COALESCE(dbo.sx_pf_pProtectID(@ModuleID), N'');
        SET @QueryName = COALESCE(@QueryName, N'');

        -- check if QueryName is targeting the exact table or we are using a suffix
        DECLARE @ResolvedQueryName NVARCHAR(255) = @QueryName;
        DECLARE @BaseQueryName NVARCHAR(255) = @QueryName;

        IF CHARINDEX('_', @QueryName) > 0
        BEGIN
            SET @BaseQueryName =
                LEFT(@QueryName, LEN(@QueryName) - CHARINDEX('_', REVERSE(@QueryName)));
        END;

        IF @QueryName <> N'GetCompanies'
        BEGIN
            IF EXISTS (
                SELECT 1
                FROM sys.tables t
                JOIN sys.schemas s ON t.schema_id = s.schema_id
                WHERE s.name = N'integration'
                AND t.name = N't' + @ModuleID + N'_' + @QueryName
            )
            BEGIN
                SET @ResolvedQueryName = @QueryName;
            END
            ELSE IF @BaseQueryName <> @QueryName
                AND EXISTS (
                    SELECT 1
                    FROM sys.tables t
                    JOIN sys.schemas s ON t.schema_id = s.schema_id
                    WHERE s.name = N'integration'
                    AND t.name = N't' + @ModuleID + N'_' + @BaseQueryName
                )
            BEGIN
                SET @ResolvedQueryName = @BaseQueryName;
            END
            ELSE
            BEGIN
                SET @Comment = CONCAT(N'Can''t get field list of table ''integration.t', @ModuleID, N'_', @QueryName, N''' because it doesn''t exist. Please check if the ''', @ModuleID, N''' module is installed correctly.');
                EXEC system.spSEND_Message N'ERROR', @Comment;
            END
        END;

        -- Data Transaction
        SELECT
            SCHEMA_NAME(t.schema_id) + N'.' + t.name AS TableName
          , c.name AS ColumnName
          , UPPER(TYPE_NAME(c.system_type_id)) +
                CASE
                    WHEN c.precision = 0 AND c.scale = 0 AND max_length > 0 THEN
                        N'(' + CAST(c.max_length / 2 AS NVARCHAR(10)) + N')'
                    WHEN c.precision > 0 AND c.scale > 0 THEN
                        N'(' + CAST(c.precision AS NVARCHAR(10)) + N', ' + CAST(c.scale AS NVARCHAR(10)) + N')'
                    ELSE N''
                END AS DataType
          , CASE WHEN c.is_nullable = 1 THEN N'NULL' ELSE N'NOT NULL' END AS NULLAble
          , p.value AS ColumnInformation
          , RANK() OVER (ORDER BY c.column_id) AS OrderNumber
        FROM sys.tables AS t
        JOIN sys.columns AS c
        ON c.object_id = t.object_id
        LEFT JOIN sys.extended_properties AS p
        ON p.major_id = t.object_id
        AND p.minor_id = c.column_id
        AND p.class = 1
        WHERE
            SCHEMA_NAME(t.schema_id) = N'integration'
            AND t.name = N't' + @ModuleID + N'_' + @ResolvedQueryName
            AND c.name <> N'RowKey'
            AND @QueryName <> N'GetCompanies'

        UNION ALL

        SELECT
            SCHEMA_NAME(t.schema_id) + N'.' + t.name AS TableName
          , c.name AS ColumnName
          , UPPER(TYPE_NAME(c.system_type_id)) +
                CASE
                    WHEN c.precision = 0 AND c.scale = 0 AND max_length > 0 THEN
                        N'(' + CAST(c.max_length / 2 AS NVARCHAR(10)) + N')'
                    WHEN c.precision > 0 AND c.scale > 0 THEN
                        N'(' + CAST(c.precision AS NVARCHAR(10)) + N', ' + CAST(c.scale AS NVARCHAR(10)) + N')'
                    ELSE N''
                END AS DataType
          , CASE WHEN c.is_nullable = 1 THEN N'NULL' ELSE N'NOT NULL' END AS NULLAble
          , p.value AS ColumnInformation
          , RANK() OVER (ORDER BY c.column_id) AS OrderNumber
        FROM sys.tables AS t
        JOIN sys.columns AS c
        ON c.object_id = t.object_id
        LEFT JOIN sys.extended_properties AS p
        ON p.major_id = t.object_id
        AND p.minor_id = c.column_id
        AND p.class = 1
        WHERE
            SCHEMA_NAME(t.schema_id) = N'global'
            AND t.name = N'tCompanies'
            AND c.name NOT IN (N'RowKey', N'DataSourceKey', N'CompanyKey', N'CustomValuesJSONUser', N'OwnerCODE')
            AND @QueryName = N'GetCompanies'
        ORDER BY OrderNumber ASC;

        SET @AffectedRows = @@ROWCOUNT;
        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 N'ERROR', @Comment;

    RETURN @ResultCode;
END;
GO

-- SET documentation variables      ***********************************************************************
DECLARE
     @level0name        NVARCHAR(255)   = N'system'    -- enter schema name of the table
    ,@level1name        NVARCHAR(255)   = N'spGET_ConnectorQueryFields'         -- 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 get one or all ConnectorQueries.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name;
 
-- optional SET parameter documentation (only for Core / Standardmodules)
    SET @level2name = N'@ModuleID';
    SET @value      = N'ModuleID - ID of the module.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;

    SET @level2name = N'@QueryName';
    SET @value      = N'QueryName - name of the query and target integration table.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;
 
GO


DROP PROCEDURE IF EXISTS system.spGET_ConnectorTree;
GO

/*
    Procedure to GET the tree structure for all connectors

    Saxess Software GmbH

    Testcall Procedure
        DECLARE @RC INT;
        EXEC @RC = system.spGET_ConnectorTree
                @Username = N'SQL'
        PRINT @RC

    Testcall Documentation
        SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'system', 'PROCEDURE', 'spGET_ConnectorTree', NULL, NULL)
        UNION ALL
        SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'system', 'PROCEDURE', 'spGET_ConnectorTree', 'PARAMETER', NULL)
*/

CREATE PROCEDURE system.spGET_ConnectorTree
    @Username   NVARCHAR(255)
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, 1, @Username;
        EXEC system.spGET_TransactUsername @TransactUsername OUTPUT, @Username;

        -- Temp table to store all integration tables of all modules
        DROP TABLE IF EXISTS #ModuleTables;

        SELECT
            SUBSTRING(t.name, 2, CHARINDEX(N'_', t.name) - 2) COLLATE DATABASE_DEFAULT AS ModuleID,
            SUBSTRING(t.name, CHARINDEX(N'_', t.name) + 1, 255) COLLATE DATABASE_DEFAULT AS IntegrationTable
        INTO #ModuleTables
        FROM sys.tables t
        JOIN sys.schemas s ON s.schema_id = t.schema_id
        WHERE
            s.name = N'integration'
            AND t.name LIKE N't%[_]%'
            AND NOT EXISTS (
                SELECT 1
                FROM system.tConnectorQueries AS cq
                JOIN system.tConnectors AS c
                ON c.ConnectorKey = cq.ConnectorKey
                WHERE
                    c.ModuleID = SUBSTRING(t.name, 2, CHARINDEX(N'_', t.name) - 2)
                    AND cq.QueryName LIKE SUBSTRING(t.name, CHARINDEX(N'_', t.name) + 1, 255) + '\_%' ESCAPE '\'
                    AND cq.QueryName NOT LIKE SUBSTRING(t.name, CHARINDEX(N'_', t.name) + 1, 255) + '\_%\_%' ESCAPE '\'
            )
        ;

        -- Add GetCompanies to all modules as default
        INSERT INTO #ModuleTables
        SELECT DISTINCT
            ModuleID
          , N'GetCompanies'
        FROM #ModuleTables;
            
        -- Data output
        SELECT
            SourceSystemID
          , SourceSystemType
          , ModuleID
          , ConnectorDescription
          , ConnectorShortDescription
          , ConnectorVersion
          , QueryName
          , Manufacturer
        FROM (
            SELECT
                c.SourceSystemID
              , c.SourceSystemType
              , c.ModuleID
              , c.ConnectorDescription
              , c.ConnectorShortDescription
              , c.ConnectorVersion
              , mt.IntegrationTable AS QueryName
              , CASE WHEN mt.IntegrationTable = N'GetCompanies' THEN 0 ELSE 1 END IsGetCompanies
              , c.Manufacturer
            FROM system.tConnectors AS c
            CROSS APPLY #ModuleTables AS mt
            LEFT JOIN system.tConnectorQueries AS ct
            ON ct.ConnectorKey = c.ConnectorKey
            AND ct.QueryName = mt.IntegrationTable
            WHERE c.ModuleID = mt.ModuleID

            UNION
        
            SELECT
                c.SourceSystemID
              , c.SourceSystemType
              , c.ModuleID
              , c.ConnectorDescription
              , c.ConnectorShortDescription
              , c.ConnectorVersion
              , CASE WHEN ct.ConnectorKey IS NULL THEN N'GetCompanies' ELSE ct.QueryName END AS QueryName
              , CASE WHEN ct.QueryName = N'GetCompanies' THEN 0 ELSE 1 END IsGetCompanies
              , c.Manufacturer
            FROM system.tConnectors AS c
            LEFT JOIN system.tConnectorQueries AS ct
            ON ct.ConnectorKey = c.ConnectorKey
            AND ct.ConnectorKey IS NOT NULL
        ) AS Queries
        ORDER BY
            SourceSystemID
          , ModuleID
          , SourceSystemType
          , IsGetCompanies
          , QueryName;

        SET @AffectedRows = @@ROWCOUNT;
        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 N'ERROR', @Comment;

    RETURN @ResultCode;
END;
GO

-- SET documentation variables      ***********************************************************************
DECLARE
     @level0name        NVARCHAR(255)   = N'system'    -- enter schema name of the table
    ,@level1name        NVARCHAR(255)   = N'spGET_ConnectorTree'         -- 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 get one or all Connectors.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name;
 

	GO


DROP PROCEDURE IF EXISTS system.spMOVE_Connector;
GO

/*
    Procedure to MOVE a Connector

    Saxess Software GmbH

    Testcall
        DECLARE @RC INT;
        EXEC @RC = system.spMOVE_Connector
                @Username = N'SQL'
              , @SourceSourceSystemID = N'SAGE100DE'
              , @SourceSourceSystemType = N'MSSQL'
              , @SourceModuleID = N'FIN'
              , @TargetSourceSystemID = N'SAGE100DERENAMED'
              , @TargetSourceSystemType = N'ORACLE'
        SELECT @RC;

    Testcall Documentation
        SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'system', 'PROCEDURE', 'spMOVE_Connector', NULL, NULL)
        UNION ALL
        SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'system', 'PROCEDURE', 'spMOVE_Connector', 'PARAMETER', NULL)
*/
 
CREATE PROCEDURE system.spMOVE_Connector
    @Username               NVARCHAR(255)
  , @SourceSourceSystemID   NVARCHAR(255)
  , @SourceSourceSystemType NVARCHAR(255)
  , @SourceModuleID         NVARCHAR(255)
  , @TargetSourceSystemID   NVARCHAR(255)
  , @TargetSourceSystemType NVARCHAR(255)
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, 6, @Username, @SourceSourceSystemID, @SourceSourceSystemType, @SourceModuleID, @TargetSourceSystemID, @TargetSourceSystemType;
        EXEC system.spGET_TransactUsername @TransactUsername OUTPUT, @Username;
        
        -- Input parameter handling
        SET @SourceSourceSystemID = COALESCE(dbo.sx_pf_pProtectID(@SourceSourceSystemID), N'');
        SET @SourceSourceSystemType = COALESCE(@SourceSourceSystemType, N'');
        SET @SourceModuleID = COALESCE(dbo.sx_pf_pProtectID(@SourceModuleID), N'');
        SET @TargetSourceSystemID = COALESCE(dbo.sx_pf_pProtectID(@TargetSourceSystemID), N'');
        SET @TargetSourceSystemType = COALESCE(@TargetSourceSystemType, N'')
        
        -- Error handling
        IF @SourceSourceSystemID = N''
            EXEC system.spSEND_Message N'ERROR', N'No SourceSourceSystemID provided'

        IF @SourceSourceSystemType = N''
            EXEC system.spSEND_Message N'ERROR', N'No SourceSourceSystemType provided'

        IF @SourceModuleID = N''
            EXEC system.spSEND_Message N'ERROR', N'No SourceModuleID provided'

        IF @TargetSourceSystemID = N''
            EXEC system.spSEND_Message N'ERROR', N'No TargetSourceSystemID provided'

        IF @TargetSourceSystemType = N''
            EXEC system.spSEND_Message N'ERROR', N'No TargetSourceSystemType provided'

        IF EXISTS(
            SELECT 1
            FROM system.tConnectors
            WHERE
                SourceSystemID = @TargetSourceSystemID
                AND SourceSystemType = @TargetSourceSystemType
                AND ModuleID = @SourceModuleID
        )
            EXEC system.spSEND_Message N'ERROR', N'Connector already exists'
        
        -- Rename Connector
        UPDATE system.tConnectors
        SET
            SourceSystemID = @TargetSourceSystemID
            , SourceSystemType = @TargetSourceSystemType
        WHERE
            SourceSystemID = @SourceSourceSystemID
            AND SourceSystemType = @SourceSourceSystemType
            AND ModuleID = @SourceModuleID;

        SET @AffectedRows = @@ROWCOUNT;

        IF @AffectedRows = 0
            EXEC system.spSEND_Message N'ERROR', N'Connector doesn''t exist'

        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 N'ERROR', @Comment;

    RETURN @ResultCode;
END;
GO

-- SET documentation variables      ***********************************************************************
DECLARE
     @level0name        NVARCHAR(255)  = N'system'			    -- enter schema name of the table
    ,@level1name        NVARCHAR(255)  = N'spMOVE_Connector'    -- enter procedure name
    ,@SX_Owner	        NVARCHAR(255)  = N'OCT.core'		    -- enter owner name of the procedure from list (OCT.core, OCT.Connectors, Custom)
    ,@SX_Connector		NVARCHAR(255)  = N'CORE'			    -- enter Connector 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_Connector;
    EXEC sys.sp_addextendedproperty N'SX_Connector'		,@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 MOVE a Connector';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name;

-- SET parameter documentation
    SET @level2name = N'@Username';
    SET @value      = N'Username';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;

    SET @level2name = N'@SourceSourceSystemID';
    SET @value      = N'Source System ID of the connector that should be renamed.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;

    SET @level2name = N'@SourceSourceSystemType';
    SET @value      = N'Source System Type of the connector that should be renamed.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;

    SET @level2name = N'@SourceModuleID';
    SET @value      = N'Module ID of the connector that should be renamed.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;

    SET @level2name = N'@TargetSourceSystemID';
    SET @value      = N'New Source System ID of the connector.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;

    SET @level2name = N'@TargetSourceSystemType';
    SET @value      = N'New Source System Type of the connector.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;
GO


DROP PROCEDURE IF EXISTS system.spMOVE_ConnectorQuery;
GO

/*
    Procedure to MOVE a Connector query

    Saxess Software GmbH

    Testcall
        DECLARE @RC INT;
        EXEC @RC = system.spMOVE_ConnectorQuery
                @Username = N'SQL'
              , @SourceSystemID = N'SAGE100DE'
              , @SourceSystemType = N'MSSQL'
              , @ModuleID = N'FIN'
              , @QueryName = N'Accounts'
              , @TargetQueryName = N'Accounts_FIBU'
        SELECT @RC;

    Testcall Documentation
        SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'system', 'PROCEDURE', 'spMOVE_ConnectorQuery', NULL, NULL)
        UNION ALL
        SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'system', 'PROCEDURE', 'spMOVE_ConnectorQuery', 'PARAMETER', NULL)
*/
 
CREATE PROCEDURE system.spMOVE_ConnectorQuery
    @Username           NVARCHAR(255)
  , @SourceSystemID     NVARCHAR(255)
  , @SourceSystemType   NVARCHAR(255)
  , @ModuleID           NVARCHAR(255)
  , @QueryName          NVARCHAR(255)
  , @TargetQueryName    NVARCHAR(255)
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, 6, @Username, @SourceSystemID, @SourceSystemType, @ModuleID, @QueryName, @TargetQueryName;
        EXEC system.spGET_TransactUsername @TransactUsername OUTPUT, @Username;
        
        -- Input parameter handling
        SET @SourceSystemID = COALESCE(dbo.sx_pf_pProtectID(@SourceSystemID), N'');
        SET @SourceSystemType = COALESCE(@SourceSystemType, N'');
        SET @ModuleID = COALESCE(dbo.sx_pf_pProtectID(@ModuleID), N'');
        
        -- Error handling
        IF @SourceSystemID = N''
            EXEC system.spSEND_Message N'ERROR', N'No SourceSystemID provided'

        IF @SourceSystemType = N''
            EXEC system.spSEND_Message N'ERROR', N'No SourceSystemType provided'

        IF @ModuleID = N''
            EXEC system.spSEND_Message N'ERROR', N'No ModuleID provided'

        IF @QueryName = N''
            EXEC system.spSEND_Message N'ERROR', N'No QueryName provided'

        IF @TargetQueryName = N''
            EXEC system.spSEND_Message N'ERROR', N'No TargetQueryName provided'

        IF @QueryName = N'GetCompanies'
            EXEC system.spSEND_Message N'ERROR', N'GetCompanies query can''t be renamed'

        -- Look up ConnectorKey
        DECLARE @ConnectorKey BIGINT;
        SELECT @ConnectorKey = ConnectorKey
        FROM system.tConnectors
        WHERE
            SourceSystemID = @SourceSystemID
            AND SourceSystemType = @SourceSystemType
            AND ModuleID = @ModuleID;

        -- Check if connector query already exists
        IF EXISTS(
            SELECT 1
            FROM system.tConnectorQueries
            WHERE
                ConnectorKey = @ConnectorKey
                AND QueryName = @TargetQueryName
        )
            EXEC system.spSEND_Message N'ERROR', N'ConnectorQuery already exists'
        
        -- Rename connector query
        UPDATE system.tConnectorQueries
        SET
            QueryName = @TargetQueryName
        WHERE
            ConnectorKey = @ConnectorKey
            AND QueryName = @QueryName;

        SET @AffectedRows = @@ROWCOUNT;

        -- connector query doesn't exist in the database - check if integration table exists
        IF @AffectedRows = 0
        BEGIN
            IF EXISTS(
                SELECT 1
                FROM sys.tables t
                JOIN sys.schemas s ON s.schema_id = t.schema_id
                WHERE
                    s.name = N'integration'
                    AND t.name LIKE N't' + @ModuleID + '\_' + @QueryName + '\_%' ESCAPE '\'
            )
            OR EXISTS(
                SELECT 1
                FROM sys.tables t
                JOIN sys.schemas s ON s.schema_id = t.schema_id
                WHERE
                    s.name = N'integration'
                    AND t.name = N't' + @ModuleID + '_' + @QueryName
            )
            BEGIN
                EXEC system.spPOST_ConnectorQuery
                    @Username = N'SQL'
                  , @SourceSystemID = @SourceSystemID
                  , @SourceSystemType = @SourceSystemType
                  , @ModuleID = @ModuleID
                  , @QueryName = @TargetQueryName
                  , @QuerySQL = N''
                  , @QueryVersionComment = N'Initial';
            END
            ELSE
            BEGIN
                EXEC system.spSEND_Message N'ERROR', N'ConnectorQuery doesn''t exist'
            END
        END

        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 N'ERROR', @Comment;

    RETURN @ResultCode;
END;
GO

-- SET documentation variables      ***********************************************************************
DECLARE
     @level0name        NVARCHAR(255)  = N'system'			    -- enter schema name of the table
    ,@level1name        NVARCHAR(255)  = N'spMOVE_ConnectorQuery' -- enter procedure name
    ,@SX_Owner	        NVARCHAR(255)  = N'OCT.core'		    -- enter owner name of the procedure from list (OCT.core, OCT.Connectors, Custom)
    ,@SX_Connector		NVARCHAR(255)  = N'CORE'			    -- enter Connector 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_Connector;
    EXEC sys.sp_addextendedproperty N'SX_Connector'		,@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 MOVE a connector query';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name;

-- SET parameter documentation
    SET @level2name = N'@Username';
    SET @value      = N'Username';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;

    SET @level2name = N'@SourceSystemID';
    SET @value      = N'Source System ID of the connector query that should be renamed.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;

    SET @level2name = N'@SourceSystemType';
    SET @value      = N'Source System Type of the connector query that should be renamed.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;

    SET @level2name = N'@ModuleID';
    SET @value      = N'Module ID of the connector query that should be renamed.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;

    SET @level2name = N'@QueryName';
    SET @value      = N'Query name of the connector query that should be renamed.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;

    SET @level2name = N'@TargetQueryName';
    SET @value      = N'New name of the connector query.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;
GO


UPDATE system.tSettings SET ValueText = '2026.03.0', ValueInt = 2026030 WHERE SettingID = 'DBVersion'