DROP FUNCTION IF EXISTS system.fDetermineTransactionUsername;
GO

/*
    Function returns the name of an existing active User in OCT Usermanamgent or the string 403.

    Determine the name of user, for whom a transaction is done.
    The resulting name may be
        - an existing and active user of the OCT Usermanagement (including the special one with name "public")
        - an login who exists not in Usermanagment, but in the database as user with the role FactorySerivce or db_owner (priviledged user)
        - the string 'public' if no business user were found an a user public is declared
        - the string '403' if no authorized user was found

    Rules:
        - User with the Role db_octservice or db_owner are allowed to act in own name or in foreign name
        - to signalize that they act in own name, they pass in the username 'SQL'
        - User with other roles can act only as themself (the user which is connected)
        - even a user with role sysadmin can't pass in a foreign username

    Keep in Mind:
        - if a user with role db_owner is also also owner of the database he will be not appear with his login, instead with username 'system'
        - a user may be db_owner and has the role db_octservice
        - a user may be sysadmin, than he is not an db_owner
        - a user may be internal OCTUser and also db_owner
        - in OCT Usermanagement 
            - never a user with Name '403' must be created - this user would always been blocked
            - a User with the Name of the db_octservice must not be created (but he may be there, due to later db role assignment)
            - a User with the role db_owner may exist in the OCT Usermangement

    TODO for later editions where XLS Client must no longer be supported
    -- Return User with an role to determine if db_octservice is acting for itself or not

    Don't use the function IS_ROLEMEMBER on SQL Server, its buggy

    Saxess Software GmbH

    Testcall 
        SELECT system.fDetermineTransactionUsername ('SQL')
        SELECT system.fDetermineTransactionUsername ('W10\admin')

    Testcall Documentation
        SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'system', 'FUNCTION', 'fDetermineTransactionUsername', NULL, NULL)
        UNION ALL
        SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'system', 'FUNCTION', 'fDetermineTransactionUsername', 'PARAMETER', NULL)

*/

CREATE FUNCTION system.fDetermineTransactionUsername (
    @Username   NVARCHAR(255) = N''
)
RETURNS NVARCHAR (255)
AS
    BEGIN
        DECLARE @TransactionUsername    NVARCHAR (255) = N'403';
        DECLARE @IsPrivilegedUser       INT = 0;
        DECLARE @UserBlocked            INT = 0;
        DECLARE @UserIsActiveOCTUser    INT = 0;
        
        -- trim Username
        SET @Username = LTRIM(RTRIM(@Username));

        -- 1. Determine if a privileged User is acting - the dbo is never member of any role, thats why its tested separat
        IF IS_MEMBER('db_octservice') = 1 OR IS_MEMBER('db_owner') = 1 OR USER_NAME() = 'system'
        BEGIN
            SET @IsPrivilegedUser = 1;
        END;
            
        -- 2. If privileged user is acting, every Username is accepted, she string 'SQL' signalise they are acting as themself 
        IF @IsPrivilegedUser = 1
            IF @Username = 'SQL'
            BEGIN
                SET @TransactionUsername = ORIGINAL_LOGIN();
            END
            ELSE
            BEGIN
                SET @TransactionUsername = @Username;
            END
        ELSE
        -- else only the Original_Login() is accepted - this happens to enable Working with the Excel Client or direct in SSMS (if user is GRANTEd to Execute SPs)
        BEGIN
            SET @TransactionUsername = ORIGINAL_LOGIN();
        END

        -- 3. Check if the user is an active OCT User
        IF (
            SELECT Count(UserKey)
            FROM system.trUser
            WHERE
                UserName = @TransactionUsername
                AND Status = 'Active'
        ) = 1
        BEGIN 
            SET @UserIsActiveOCTUser = 1;
        END

        -- privileged users are always exepted when they act for thereself  (even if they exists in usermanagement as inactive or don't exists there)        
        IF @IsPrivilegedUser = 1 AND @Username = 'SQL'
        BEGIN
            SET @TransactionUsername = @TransactionUsername; -- do nothing, accept the user
        END

        -- When privileged user is acting for a User, only active users are accepted
        IF @IsPrivilegedUser = 1 AND @Username <> 'SQL' AND @UserIsActiveOCTUser = 0
        BEGIN
            SET @TransactionUsername = '403';
            SET @UserBlocked = 1;
        END

        -- When a non privileged User is connected directly, he is blocked if he is not an active user 
        IF @IsPrivilegedUser = 0 AND @UserIsActiveOCTUser = 0
        BEGIN
            SET @TransactionUsername = '403';
            SET @UserBlocked = 1;
        END
                
        -- 4. If the user was blocked, check if a public user exists, which can be used for the transaction
        IF @UserBlocked = 1
        BEGIN
            IF EXISTS(
                SELECT UserKey
                FROM system.trUser
                WHERE
                    UserName = 'public'
                    AND Status = 'Active'
            )
            BEGIN
                SET @TransactionUsername = 'public';
            END
        END

        RETURN @TransactionUsername
    END
GO

GRANT EXECUTE ON system.fDetermineTransactionUsername TO db_octservice;
GO


-- SET documentation variables      ***********************************************************************
DECLARE
     @level0name        NVARCHAR(255)   = N'system'            -- enter schema name of the table
    ,@level1name        NVARCHAR(255)   = N'fDetermineTransactionUsername'            -- enter function name
    ,@SX_Owner            NVARCHAR(255)   = N'OCT.core'       -- enter owner name of the function 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
                                                             
 
-- KEEP this default constants     *************************************************************************
DECLARE
     @name          NVARCHAR(255)   = N'MS_Description'
    ,@level0type    NVARCHAR(255)   = N'SCHEMA'
    ,@level1type    NVARCHAR(255)   = N'FUNCTION'
    ,@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 documententation             *************************************************************************
 
-- SET function documentation
    SET @value = N'Function and determine the business username for the transaction - 
        only for active users and the priviledged database roles db_octservice and db_owner an username is returnd.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name;
 
 
-- SET parameter documentation
    SET @level2name = N'@Username';
    SET @value      = N'The name of the user, in which name the transaction shall happen. Pass in "SQL" if the Service shall act for himself.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;
GO

DROP FUNCTION IF EXISTS system.fPivotStringIntoTable;
GO

/*
    Function to split a String into a TableVariable
    The Delimiter String will be replace by a CHAR(28) and the string splitted on this

    Saxess Software GmbH
 
    Testcall Function
        SELECT *
        FROM system.fPivotStringIntoTable('xxxNxxx', 'N')

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

CREATE FUNCTION system.fPivotStringIntoTable (
    @String     NVARCHAR(MAX)
  , @Delimiter  NVARCHAR(5)
)
RETURNS @tblSplitValues TABLE (
    txtValues NVARCHAR(MAX)
)
AS
BEGIN
    DECLARE @spr        NCHAR(1)        = CHAR(28);
    DECLARE @auxString  NVARCHAR (MAX)  = '';

    SET @auxString = REPLACE(@String, @Delimiter, @spr);

    WITH Split(stpos, endpos) AS (
        SELECT
            0                          AS stpos
          , CHARINDEX(@spr,@auxString) AS endpos
        
        UNION ALL
        SELECT
            CAST(endpos AS INT) + 1,
            CHARINDEX(@spr, @auxString, endpos + 1)
        FROM Split
        WHERE endpos > 0
    )
    INSERT @tblSplitValues
    SELECT SUBSTRING(@auxString, stpos, COALESCE(NULLIF(endpos, 0), LEN(@auxString) + 1) - stpos)
    FROM Split
    OPTION (MAXRECURSION 32766) -- it works ONLY up to 32.766 rows

    RETURN
END
GO

GRANT SELECT ON system.fPivotStringIntoTable TO db_octservice;
GO

-- SET documentation variables      ***********************************************************************
DECLARE
     @level0name        NVARCHAR(255)   = N'system'                            -- enter schema name of the table
    ,@level1name        NVARCHAR(255)   = N'fPivotStringIntoTable'        -- enter function name
    ,@SX_Owner            NVARCHAR(255)   = N'OCT.core'                        -- enter owner name of the function 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
                                                             
-- KEEP this default constants     *************************************************************************
DECLARE
     @name          NVARCHAR(255)   = N'MS_Description'
    ,@level0type    NVARCHAR(255)   = N'SCHEMA'
    ,@level1type    NVARCHAR(255)   = N'FUNCTION'
    ,@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 documententation             *************************************************************************
 
-- SET function documentation
    SET @value = N'Function to split a String into a TableVariable.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name;
 
-- SET parameter documentation
    SET @level2name = N'@String';
    SET @value      = N'The pivot string.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;
 
    SET @level2name = N'@Delimiter';
    SET @value      = N'The delimiter to split the string.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;
GO


DROP FUNCTION IF EXISTS system.fMaskSQL;
GO

/*
    Function to mask strings, which will be part of an SQL Statement @StringValue  - it just doubles all inverted commas.
    returns a masked string
 
    Saxess Software GmbH
 
    Testcall Function
        DECLARE @String NVARCHAR(255);
        SET @String = N'ToDo''s';
        PRINT @String;
        SELECT system.fMaskSQL(@String);

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

CREATE FUNCTION system.fMaskSQL (
    @StringValue    NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    RETURN REPLACE(@StringValue, N'''', N'''''')
END
GO

GRANT EXECUTE ON system.fMaskSQL TO db_octservice;
GO

-- SET documentation variables      ***********************************************************************
DECLARE
     @level0name        NVARCHAR(255)   = N'system'            -- enter schema name of the table
    ,@level1name        NVARCHAR(255)   = N'fMaskSQL'    -- enter function name
    ,@SX_Owner            NVARCHAR(255)   = N'OCT.core'       -- enter owner name of the function 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
                                                             
-- KEEP this default constants     *************************************************************************
DECLARE
     @name          NVARCHAR(255)   = N'MS_Description'
    ,@level0type    NVARCHAR(255)   = N'SCHEMA'
    ,@level1type    NVARCHAR(255)   = N'FUNCTION'
    ,@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 documententation             *************************************************************************
 
-- SET function documentation
    SET @value = N'Function to mask strings, which will be part of an SQL Statement - it just doubles all inverted commas.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name;
 
-- SET parameter documentation
    SET @level2name = N'@StringValue';
    SET @value      = N'String, which will be returned with doubled inverted commas.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;
GO


DROP FUNCTION IF EXISTS system.fProtectBoolean;
GO

/*
    Function returns a '0' when the passed parameter is not either '0' or '1'
    @BooleanValue ='<#NV>'is ignored and not changed
 
    Saxess Software GmbH
 
    Testcall Function
        SELECT system.fProtectBoolean (2)

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

CREATE FUNCTION system.fProtectBoolean (
    @BooleanValue NVARCHAR(255)
)
RETURNS NVARCHAR(255)
AS
BEGIN
    IF @BooleanValue <>'<#NV>'
    BEGIN
        IF @BooleanValue <>'0' AND @BooleanValue <>'1'
        BEGIN
            SET @BooleanValue = '0';
        END
    END

    RETURN @BooleanValue
END
GO

GRANT EXECUTE ON system.fProtectBoolean TO db_octservice;
GO

-- SET documentation variables      ***********************************************************************
DECLARE
     @level0name        NVARCHAR(255)   = N'system'                    -- enter schema name of the table
    ,@level1name        NVARCHAR(255)   = N'fProtectBoolean'    -- enter function name
    ,@SX_Owner            NVARCHAR(255)   = N'OCT.core'                -- enter owner name of the function 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
                                                             
-- KEEP this default constants     *************************************************************************
DECLARE
     @name          NVARCHAR(255)   = N'MS_Description'
    ,@level0type    NVARCHAR(255)   = N'SCHEMA'
    ,@level1type    NVARCHAR(255)   = N'FUNCTION'
    ,@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 documententation             *************************************************************************
 
-- SET function documentation
    SET @value = N'Function returns a 0 when the passed parameter is not either 0 or 1. @BooleanValue =<#NV>is ignored and not changed.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name;
 
-- SET parameter documentation
    SET @level2name = N'@BooleanValue';
    SET @value      = N'Boolean parameter.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;
GO


DROP FUNCTION IF EXISTS system.fProtectID;
GO

/*
    Function to replace all special signs from ID
    An ID may contain Letters, Numbers,'_' and '-', but nothing else
    Function returns a clean string
 
    Saxess Software GmbH
 
    Testcall Function
        SELECT system.fProtectID('T-1/3')

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

CREATE FUNCTION system.fProtectID (
    @ElementID  NVARCHAR(255)
)
RETURNS NVARCHAR(255)
AS
BEGIN
    SET @ElementID = RTRIM(LTRIM(@ElementID));

    RETURN REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
        @ElementID
      , '|','')
      , ';','')
      , '''','')
      , '/','')
      , '\','')
      , '"','')
      , ',','')
      , '>','')
      , '<','')
      , '[','')
      , ']','')
      , '(','')
      , ')','')
      , '{','')
      , '}','')
      , '!','')
      , '$','')
      , '&','')
      , '^','')
      , '@','')
      , '%','')
      , ':','')
      , '`','')
      , '´','')
      , '?','')
      , '§','')
      , '#','')
      , '~','')
      , '+','')
      , '=','')
      , '.','')
      , '€','')
      , ' ','')
END
GO

GRANT EXECUTE ON system.fProtectID TO db_octservice;
GO

-- SET documentation variables      ***********************************************************************
DECLARE
     @level0name        NVARCHAR(255)   = N'system'                -- enter schema name of the table
    ,@level1name        NVARCHAR(255)   = N'fProtectID'    -- enter function name
    ,@SX_Owner            NVARCHAR(255)   = N'OCT.core'            -- enter owner name of the function 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
                                                             
-- KEEP this default constants     *************************************************************************
DECLARE
     @name          NVARCHAR(255)   = N'MS_Description'
    ,@level0type    NVARCHAR(255)   = N'SCHEMA'
    ,@level1type    NVARCHAR(255)   = N'FUNCTION'
    ,@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 documententation             *************************************************************************
 
-- SET function documentation
    SET @value = N'Function to replace all special signs from ID.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name;
 
-- SET parameter documentation
    SET @level2name = N'@ElementID';
    SET @value      = N'Element ID Parameter.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;
GO


DROP FUNCTION IF EXISTS system.fProtectInt;
GO

/*
    Function cast all expression to int or return 0 for non-int values, except the <#NV> String this is passed anytime
 
    Saxess Software GmbH
 
    Testcalls Function
        SELECT system.fProtectInt('-1')
        SELECT system.fProtectInt('1')
        SELECT system.fProtectInt('Hase')
        SELECT system.fProtectInt('<#NV>')
        SELECT system.fProtectInt('')
        SELECT system.fProtectInt('0')
        SELECT system.fProtectInt('9999999999999999999999999999999999999999999999999999999999')

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

CREATE FUNCTION system.fProtectInt (
    @IntValue   NVARCHAR(255)
)
RETURNS NVARCHAR(255)
AS
BEGIN
    IF @IntValue <>'<#NV>' 
    BEGIN
        SET @IntValue = COALESCE(CAST(TRY_CAST(@IntValue AS INT) AS NVARCHAR(255)), 0)
    END

    RETURN @IntValue
END
GO

GRANT EXECUTE ON system.fProtectInt TO db_octservice;
GO

-- SET documentation variables      ***********************************************************************
DECLARE
     @level0name        NVARCHAR(255)   = N'system'                -- enter schema name of the table
    ,@level1name        NVARCHAR(255)   = N'fProtectInt'    -- enter function name
    ,@SX_Owner            NVARCHAR(255)   = N'OCT.core'            -- enter owner name of the function 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
                                                             
-- KEEP this default constants     *************************************************************************
DECLARE
     @name          NVARCHAR(255)   = N'MS_Description'
    ,@level0type    NVARCHAR(255)   = N'SCHEMA'
    ,@level1type    NVARCHAR(255)   = N'FUNCTION'
    ,@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 documententation             *************************************************************************
 
-- SET function documentation
    SET @value = N'Function cast all expression to int or return 0 for non-int values, except the <#NV> String this is passed anytime.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name;
 
-- SET parameter documentation
    SET @level2name = N'@IntValue';
    SET @value      = N'Parameter int value.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;
GO


DROP FUNCTION IF EXISTS system.fProtectString;
GO

/*
    Function to eliminate all not permitted special characters (Hochkommata, '|','),(', ';' ) and line breaks at the end from @StringValue
    Function returns a string without the not permitted special characters
 
    Saxess Software GmbH

    Testcall Function
        SELECT system.fProtectString ('X),(Y;Z')

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

CREATE FUNCTION system.fProtectString (
    @StringValue    NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
    -- remove line break only at the end of the string
    IF LEN(@StringValue) > 1
    BEGIN
        SET @StringValue = SUBSTRING(@StringValue, 0, LEN(@StringValue) - 2) + REPLACE(REPLACE(SUBSTRING(@StringValue, LEN(@StringValue) - 2, 3), CHAR(10), ''), CHAR(13), '')
    END

    -- remove special characters
    RETURN REPLACE(REPLACE(REPLACE(@StringValue, '|', ''), '),(', ''), '--', '')
END
GO

GRANT EXECUTE ON system.fProtectString TO db_octservice;
GO

-- SET documentation variables      ***********************************************************************
DECLARE
     @level0name        NVARCHAR(255)   = N'system'                    -- enter schema name of the table
    ,@level1name        NVARCHAR(255)   = N'fProtectString'    -- enter function name
    ,@SX_Owner            NVARCHAR(255)   = N'OCT.core'                -- enter owner name of the function 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
                                                             
-- KEEP this default constants     *************************************************************************
DECLARE
     @name          NVARCHAR(255)   = N'MS_Description'
    ,@level0type    NVARCHAR(255)   = N'SCHEMA'
    ,@level1type    NVARCHAR(255)   = N'FUNCTION'
    ,@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 documententation             *************************************************************************
 
-- SET function documentation
    SET @value = N'Function to eliminate all not permitted special characters from @StringValue.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name;
 
-- SET parameter documentation
    SET @level2name = N'@StringValue';
    SET @value      = N'Parameter string value.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;
GO


  
DROP PROCEDURE IF EXISTS system.spPOST_Right;
GO

/*
	POST Operation for UserRight
	UserRight will be created always, not fitting other rights will be deleted to ensure enheritance
 
	Gerd Tautenhahn for Saxess Software GmbH
	Last modified: 04/2023 for OCT 5.9

	Test call
	DECLARE  @RC					INT
			,@UserName				NVARCHAR(255) = 'SQL'
			,@PostUserName			NVARCHAR(255) = 'sxs'
			,@FactoryID				NVARCHAR(255) = 'ZT'
			,@ProductLineID			NVARCHAR(255) = 'U'
			,@Right					NVARCHAR(255) = 'Write'
			,@ReadCommentMandatory	NVARCHAR(255) = ''
			,@WriteCommentMandatory	NVARCHAR(255) = ''
		
	EXECUTE @RC = system.spPOST_Right 
			@UserName,
			@PostUserName,
			@FactoryID,
			@ProductLineID,
			@Right,
			@ReadCommentMandatory,
			@WriteCommentMandatory
	PRINT @RC

	Testcall tables
		SELECT * FROM system.trRights;
		SELECT TOP 10 * FROM system.tAPILog;

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

CREATE PROCEDURE system.spPOST_Right
	@UserName				NVARCHAR(255),
	@PostUserName			NVARCHAR(255),
	@FactoryID				NVARCHAR(255),
    @ProductLineID			NVARCHAR(255),
    @Right					NVARCHAR(255),
    @ReadCommentMandatory	NVARCHAR(255),
    @WriteCommentMandatory  NVARCHAR(255)
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE  @PostUserKey		INT				= 0
			,@FactoryKey		INT				= 0
			,@ProductLineKey	INT				= 0
			,@TransactUsername	NVARCHAR(255)	= N''
			,@ClusterRight		NVARCHAR(255)	= N''
			,@FactoryRight		NVARCHAR(255)	= N''
			,@ProcedureName		NVARCHAR(255)	= OBJECT_SCHEMA_NAME(@@PROCID) + N'.' + OBJECT_NAME(@@PROCID)
			,@ParameterString	NVARCHAR(MAX)	= N'''' 
				+ ISNULL(@Username,					N'ISNULL') + N''',''' + ISNULL(@PostUserName,			N'ISNULL') + N''',''' 
				+ ISNULL(@FactoryID,				N'ISNULL') + N''',''' + ISNULL(@ProductLineID,			N'ISNULL') + N''',''' 
				+ ISNULL(@Right,					N'ISNULL') + N''',''' + ISNULL(@ReadCommentMandatory,	N'ISNULL') + N''',''' 
				+ ISNULL(@WriteCommentMandatory,	N'ISNULL') + N''''
			,@EffectedRows		INT				= 0	
			,@ResultCode		INT				= 501		
			,@TimestampCall		DATETIME		= GETUTCDATE()
			,@Comment			NVARCHAR(2000)	= N''
			,@Message			NVARCHAR(2000)	= N''
			,@PostUserIsAdmin	INT				= 0;				

	-- STEP 0.1 - NULL Protection
	IF @Username				IS NULL SET @Username				= N'';
	IF @PostUserName			IS NULL SET @PostUserName			= N'';
	IF @ProductLineID			IS NULL SET @ProductLineID			= N'';
	IF @FactoryID				IS NULL SET @FactoryID				= N'';
	IF @Right					IS NULL SET @Right					= N'';
	IF @ReadCommentMandatory	IS NULL SET @ReadCommentMandatory	= N'';
	IF @WriteCommentMandatory	IS NULL SET @WriteCommentMandatory	= N'';

	-- Capitlize the word Read / Write, lower case for other letters
	SET @Right = UPPER(LEFT(@Right,1))+LOWER(SUBSTRING(@Right,2,LEN(@Right)));

	BEGIN TRY
		BEGIN TRANSACTION sx_pf_POST_Right;	

		-- STEP 0.2 - Protect input parameters	
		SET @PostUserName			= system.fProtectString	(@PostUserName);
		SET @ProductLineID			= system.fProtectID		(@ProductLineID);
		SET @FactoryID				= system.fProtectID		(@FactoryID);
		SET @Right					= system.fProtectString	(@Right);
		SET @ReadCommentMandatory	= system.fProtectBoolean (@ReadCommentMandatory);
		SET @WriteCommentMandatory	= system.fProtectBoolean (@WriteCommentMandatory);
		
		-- STEP 0.3 - Clean IDs
		SET @Right			= LTRIM(RTRIM(@Right));
		SET @PostUserName	= LTRIM(RTRIM(@PostUserName));
		SET @FactoryID		= LTRIM(RTRIM(@FactoryID));
		SET @ProductLineID	= LTRIM(RTRIM(@ProductLineID));

		IF	@Right = N'' 
			OR @PostUserName = N''
		BEGIN
			SET @ResultCode = 404;
			RAISERROR('Empty input parameters', 16, 10);
		END;	
				
		-- It`s forbidden to post rights for system users with name 'SQL'
		IF	@PostUserName = N'SQL' 
		BEGIN
			SET @ResultCode = 403;
			RAISERROR('Forbidden to post rights for system users reserved name.', 16, 10, @PostUserName);
		END;	

		-- Determine transaction user, the transactuser 'public' is not allowed to post rights
		SELECT @TransactUsername = system.fDetermineTransactionUsername (@Username);

		IF @TransactUsername  = N'403'
		BEGIN
			SET @ResultCode = 403;
			RAISERROR('Transaction user don`t exists', 16, 10);
		END 
		ELSE IF @TransactUsername = N'public'
		BEGIN
			SET @ResultCode = 403;
			RAISERROR('''Public'' user is not allowed to post rights.', 16, 10);
		END;
		
		-- Only OCT Administrators can manage users
		IF NOT EXISTS
			(SELECT 
				UserKey
			FROM system.trUser
			WHERE 
					UserName			= @TransactUsername 
				AND IsAdministratorFlag	= 1
				AND Status				= 'Active'
			)
		BEGIN
			SET @Message = CONCAT('Invalid rights, User "',@Transactusername,'" is not an OCT Administrator.');
			RAISERROR(@Message, 16, 10);
		END;

		-- It`s forbidden for a ClusterAdmin to change his own rights
		IF @PostUserName = @TransactUsername	
		BEGIN
			SET @ResultCode = 403;
			RAISERROR('Forbidden for a ClusterAdmin to change his own rights', 16, 10);
		END;

		-- Its forbidden to change other administrators rights
		SELECT @PostUserIsAdmin = COUNT(UserName) FROM system.trUser WHERE UserName = @PostUserName AND IsAdministratorFlag = 1;
		IF @PostUserIsAdmin = 1	
			BEGIN
				SET @ResultCode = 403;
				RAISERROR(N'Forbidden to change the rights of an Administrator.', 16, 10);
			END;

		-- Empty factory ID must always be connected to empty ProductLineID
		IF	@FactoryID = N'' 
			AND @ProductLineID <> N''
		BEGIN
			SET @ResultCode = 401;
			RAISERROR('Empty factory ID must always be connected to empty ProductLineID.', 16, 10);
		END;
		
		-- STEP 2.3 - Check keys
		IF @FactoryID <> N''
		BEGIN
			SELECT @FactoryKey = FactoryKey FROM planning.tdFactories WHERE FactoryID = @FactoryID;

			IF @FactoryKey = 0 
			BEGIN
				SET @ResultCode = 404;
				RAISERROR('Keys not exists', 16, 10);
			END;
		END;

		IF @ProductLineID <> N'' 
		BEGIN
			SELECT @ProductLineKey = ProductLineKey FROM planning.tdProductLines WHERE ProductLineID = @ProductLineID AND FactoryKey = @FactoryKey;
			
			IF @ProductLineKey = 0 
			BEGIN
				SET @ResultCode = 404;
				RAISERROR('Keys not exists', 16, 10);
			END;
		END;

		SELECT @PostUserKey = UserKey FROM system.trUser WHERE UserName = @PostUserName;

		IF @PostUserKey  = 0	
		BEGIN
			SET @ResultCode = 404;
			RAISERROR('Keys not exists', 16, 10);
		END;

		-- STEP 3 - Set Rule
		IF @Right = N'Deny'
		BEGIN
			-- 'Deny' will delete a right, but never create an entry

			IF @FactoryID = N''
				DELETE FROM system.trRights WHERE UserKey = @PostUserKey;
			ELSE IF @ProductLineID = N''
				DELETE FROM system.trRights WHERE UserKey = @PostUserKey AND FactoryID = @FactoryID;
			ELSE
				DELETE FROM system.trRights WHERE UserKey = @PostUserKey AND FactoryID = @FactoryID AND ProductLineID = @ProductLineID;

			SET @EffectedRows += @@ROWCOUNT;
			SET @ResultCode = 204;
		END
		ELSE
		BEGIN
			-- Saves only explicit rights on objects and deletes rights which get invalid through right definition on upper levels
			-- Check always Clusteright 
			
			SELECT TOP (1) @ClusterRight = COALESCE([Right], N'None') 
			FROM system.trRights 
			WHERE 
				UserKey = @PostUserKey 
				AND FactoryID = N''
			ORDER BY [Right];

			SET @ResultCode = 204;

			-- CLUSTER RIGHTS are posted +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
			-- Write / Read -> delete all other Rights and insert
			IF	@FactoryID = N'' AND @ProductLineID = N''
			BEGIN
				DELETE FROM system.trRights WHERE UserKey = @PostUserKey;

				SET @EffectedRows += @@ROWCOUNT;

				INSERT INTO system.trRights
						(UserKey
						,UserName
						,FactoryID
						,ProductLineID
						,ProductID
						,[Right]
						,ReadCommentMandatory
						,WriteCommentMandatory)
				VALUES  (@PostUserKey			-- UserKey					int
						,@PostUserName			-- UserName					nvarchar(255)
						,N''					-- FactoryID				nvarchar(255)
						,N''					-- ProductLineID			nvarchar(255)
						,N''					-- ProductID				nvarchar(255)
						,@Right					-- Right					nvarchar(255)
						,@ReadCommentMandatory  -- ReadCommentMandatory		int
						,@WriteCommentMandatory -- WriteCommentMandatory	int
						);
				
				SET @EffectedRows += @@ROWCOUNT;
				SET @ResultCode = 200;
			END;

			-- FACTORY RIGHTS are posted +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
			IF @FactoryID <> N'' AND @ProductLineID = N''
			BEGIN
				-- Write -> delete all other Right in this Factory, dont accept if user has write on Cluster
				IF @Right = N'Write' AND @ClusterRight <> N'Write'
				BEGIN
					DELETE FROM system.trRights WHERE UserKey = @PostUserKey AND FactoryID = @FactoryID;
					SET @EffectedRows += @@ROWCOUNT;

					INSERT INTO system.trRights
							( UserKey
							,UserName
							,FactoryID
							,ProductLineID
							,ProductID
							,[Right]
							,ReadCommentMandatory
							,WriteCommentMandatory)
					VALUES  (@PostUserKey			-- UserKey					int
							,@PostUserName			-- UserName					nvarchar(255)
							,@FactoryID				-- FactoryID				nvarchar(255)
							,N''					-- ProductLineID			nvarchar(255)
							,N''					-- ProductID				nvarchar(255)
							,@Right					-- Right					nvarchar(255)
							,@ReadCommentMandatory  -- ReadCommentMandatory		int
							,@WriteCommentMandatory -- WriteCommentMandatory	int
							);

					SET @EffectedRows += @@ROWCOUNT;
					SET @ResultCode = 200;
				END;

				-- Read if no cluster rights set -> delete all other Rights in this Factory, dont accept if user has write or read on Cluster
				IF @Right = N'Read' AND @ClusterRight <> N'Write' AND @ClusterRight <> N'Read'
				BEGIN
					DELETE FROM system.trRights WHERE UserKey = @PostUserKey AND FactoryID = @FactoryID;
					SET @EffectedRows += @@ROWCOUNT;

					INSERT INTO system.trRights
							(UserKey
							,UserName
							,FactoryID
							,ProductLineID
							,ProductID
							,[Right]
							,ReadCommentMandatory
							,WriteCommentMandatory)
					VALUES  ( @PostUserKey			-- UserKey					int
							,@PostUserName			-- UserName					nvarchar(255)
							,@FactoryID				-- FactoryID				nvarchar(255)
							,N''					-- ProductLineID			nvarchar(255)
							,N''					-- ProductID				nvarchar(255)
							,@Right					-- Right					nvarchar(255)
							,@ReadCommentMandatory  -- ReadCommentMandatory		int
							,@WriteCommentMandatory -- WriteCommentMandatory	int
							);

					SET @EffectedRows += @@ROWCOUNT;
					SET @ResultCode = 200;
				END;

				-- Read if Clusterwrite rights -> delete all other Rights in this Factory, dont accept if user has write or read on Cluster
				IF @Right = N'Read' AND @ClusterRight = N'Read'
				BEGIN
					DELETE FROM system.trRights WHERE UserKey = @PostUserKey AND FactoryID = @FactoryID;

					SET @EffectedRows += @@ROWCOUNT;
					SET @ResultCode = 200;
				END
			END;

			-- PRODUCTLINE RIGHT is posted +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
			IF @FactoryID <> N'' AND @ProductLineID <> N''
			BEGIN
				-- Look for Rights on Factory Level
				SELECT TOP (1)  @FactoryRight = COALESCE([Right], N'None') 
				FROM system.trRights 
				WHERE 
						UserKey = @PostUserKey 
					AND FactoryID = @FactoryID 
					AND ProductLineID = N''
				ORDER BY [Right];

				-- Write -> dont accept if user has already write on Cluster or Factory
				IF @Right = N'Write' AND @ClusterRight <> N'Write' AND @FactoryRight <> N'Write'
				BEGIN
					DELETE FROM system.trRights WHERE UserKey = @PostUserKey AND FactoryID = @FactoryID AND ProductLineID = @ProductLineID;
					SET @EffectedRows += @@ROWCOUNT;

					INSERT INTO system.trRights
							( UserKey
							,UserName
							,FactoryID
							,ProductLineID
							,ProductID
							,[Right]
							,ReadCommentMandatory
							,WriteCommentMandatory)
					VALUES  ( @PostUserKey			-- UserKey					int
							,@PostUserName			-- UserName					nvarchar(255)
							,@FactoryID				-- FactoryID				nvarchar(255)
							,@ProductLineID			-- ProductLineID			nvarchar(255)
							,N''					-- ProductID				nvarchar(255)
							,@Right					-- Right					nvarchar(255)
							,@ReadCommentMandatory  -- ReadCommentMandatory		int
							,@WriteCommentMandatory -- WriteCommentMandatory	int
							);

					SET @EffectedRows += @@ROWCOUNT;
					SET @ResultCode = 200;
				END;

				-- Read -> don`t accept if user has read or write on Cluster or Factory
				IF	@Right = N'Read' AND @ClusterRight <> N'Write' AND @ClusterRight <> N'Read' 
					AND @FactoryRight <> N'Write' AND @FactoryRight <> N'Read'
				BEGIN
					DELETE FROM system.trRights WHERE UserKey = @PostUserKey AND FactoryID = @FactoryID AND ProductLineID = @ProductLineID;
					SET @EffectedRows += @@ROWCOUNT;

					INSERT INTO system.trRights
							( UserKey
							,UserName
							,FactoryID
							,ProductLineID
							,ProductID
							,[Right]
							,ReadCommentMandatory
							,WriteCommentMandatory)
					VALUES  ( @PostUserKey				-- UserKey					int
							,@PostUserName				-- UserName					nvarchar(255)
							,@FactoryID					-- FactoryID				nvarchar(255)
							,@ProductLineID				-- ProductLineID			nvarchar(255)
							,N''						-- ProductID				nvarchar(255)
							,@Right						-- Right					nvarchar(255)
							,@ReadCommentMandatory		-- ReadCommentMandatory		int
							,@WriteCommentMandatory		-- WriteCommentMandatory	int
							);

					SET @EffectedRows += @@ROWCOUNT;
					SET @ResultCode = 200;
				END;
						
				-- Read -> if read should only kill an existing write in Factories with global Read Rights, just delete Write Right
				IF @Right = N'Read' AND (@FactoryRight = N'Read' OR (@ClusterRight = N'Read' AND @FactoryRight = N''))
				BEGIN
					DELETE FROM system.trRights WHERE UserKey = @PostUserKey AND FactoryID = @FactoryID AND ProductLineID = @ProductLineID;
					SET @EffectedRows += @@ROWCOUNT;
				END;
			END;
		END;

		COMMIT TRANSACTION sx_pf_POST_Right;
	END TRY
	BEGIN CATCH
		DECLARE @Error_state INT = ERROR_STATE();
		SET @Comment = ERROR_MESSAGE();

		ROLLBACK TRANSACTION sx_pf_POST_Right;		

		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 system.spPOST_APILogEntry @Username, @TransactUsername, @ProcedureName, @ParameterString, @EffectedRows, @ResultCode, @TimestampCall, @Comment;
	RETURN @ResultCode;
END
GO

-- SET documentation variables      ***********************************************************************
DECLARE
     @level0name        NVARCHAR(255)  = N'system'							-- enter schema name of the table
    ,@level1name        NVARCHAR(255)  = N'spPOST_Right'					-- 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 UserRight
         UserRight will be created always, not fitting other rights will be deleted to ensure enheritance';
    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'@PostUserName';
    SET @value      = N'PostUserName';
    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'@ProductLineID';
    SET @value      = N'ProductLineID';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;
 
    SET @level2name = N'@Right';
    SET @value      = N'Right';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;
 
    SET @level2name = N'@ReadCommentMandatory';
    SET @value      = N'ReadCommentMandatory';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;
 
    SET @level2name = N'@WriteCommentMandatory';
    SET @value      = N'WriteCommentMandatory';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;
 
GO



DROP PROCEDURE IF EXISTS system.spDELETE_DataSource;
GO

/*
	Gerd Tautenhahn for Saxess Software GmbH
	Last modified: 10/2022 for OCT 5.8

	Procedure to delete a DataSource an all data which belong to it. 

	A Datasource contains mostly three types of data
		- itselft, its definition of a connnection to an Datasource
		- the data extracted from this datasource (Companies, Dimensions, Facts)
		- process definitions for this datasource (Pipelines, Steps..)
 
	Testcall Procedure
		DECLARE  @RC INT;
		EXEC	 @RC = system.spDELETE_DataSource
				 @Username		= 'SQL'
				,@DataSourceID	= '2'
		PRINT    @RC

		SELECT * FROM system.tDataSources

	Testcall Documentation
		SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'system', 'PROCEDURE', 'spDELETE_DataSource',NULL,NULL)
		UNION ALL
		SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'system', 'PROCEDURE', 'spDELETE_DataSource','PARAMETER',NULL)
*/
 
CREATE PROCEDURE system.spDELETE_DataSource
    (
         @Username			NVARCHAR(255)
        ,@DataSourceID		NVARCHAR(50)
    )

AS 
	BEGIN	

		-- Standard declaration for logging
		DECLARE 
				 @ProcedureName		NVARCHAR(255)	= OBJECT_SCHEMA_NAME(@@PROCID) + N'.' + OBJECT_NAME(@@PROCID)

				,@ParameterString	NVARCHAR(MAX)	= N''''					--set Strings inside in single quotes (N''','''), Numbers without strings inside without quotes (N','), end list with '''' in case of string or '' in case of number on last position
				+ ISNULL(@Username					,N'NULL')	+ N''','''											 
				+ ISNULL(@DataSourceID 				,N'NULL')	+ N''''

				,@EffectedRows		INT				= 0			
				,@ResultCode		INT				= 501				
				,@TimestampCall		DATETIME		= GETUTCDATE()
				,@Comment			NVARCHAR(2000)	= N''
				,@TransactUsername	NVARCHAR(255)	= N''
				,@Tablename			NVARCHAR(255)	= N''
				,@SQL				NVARCHAR(MAX)	= N''
				,@DataSourceKey			INT;

		-- NULL Protection for all Input parameters
		IF @Username				IS NULL SET @Username			= N'';
		IF @DataSourceID			IS NULL SET @DataSourceID		= N'';


		-- START TRANSACTION			***********************************************************************************
		BEGIN TRY
			BEGIN TRANSACTION spDELETE_DataSource
				-- check transaction user existence
				SELECT @TransactUsername = system.fDetermineTransactionUsername (@Username);
					IF @TransactUsername  = N'403' 
						BEGIN
							SET @ResultCode = 403;
							RAISERROR(N'Transaction user don`t exists', 16, 10);
						END;
				-- Check for Cluster Write Rights

				-- Check if Datasource exits
				SELECT @DataSourceKey = DataSourceKey
				FROM system.tDataSources 
				WHERE DataSourceID = @DataSourceID;

				IF @DataSourceKey IS NULL
					BEGIN
						SET @ResultCode = 404;
						RAISERROR(N'Datasource don`t exists', 16, 10);
					END;



				-- Generic delete is still done over DatasourceKey, as DatasourceID was till 5.5 the DatasourceKey
				PRINT '';
				PRINT 'Starting generic Delete Process for all Tables outside system containing the Column "DatasourceKey"';
				PRINT '#####################################################################################################';
				PRINT '';
				-- DELETE Date in all Non-Core Tables where the column DataSourceKey is used (usually global and integration schema)
				-- Generic delete over dynamic SQL is possible in all OCT schema except "system" in default

				DECLARE TableCursor CURSOR FOR
					SELECT      
						 CONCAT(infs.TABLE_SCHEMA COLLATE DATABASE_DEFAULT,N'.',infs.TABLE_NAME COLLATE DATABASE_DEFAULT)	AS Tablename

					FROM INFORMATION_SCHEMA.COLUMNS infs
						-- Determination Objekttype
						LEFT JOIN 
								(
								SELECT 
									 Name	  				COLLATE DATABASE_DEFAULT    AS Objektname
									,SCHEMA_NAME(Schema_id) COLLATE DATABASE_DEFAULT	AS Schemaname
									,type_desc				COLLATE DATABASE_DEFAULT	AS Objekttyp
								FROM sys.objects
								) syo ON 
											syo.Schemaname = infs.TABLE_SCHEMA
										AND syo.Objektname = infs.TABLE_NAME

					WHERE 
							syo.Objekttyp		= N'USER_TABLE'
						AND infs.COLUMN_NAME	= N'DataSourceKey'
						-- ToDo: Determination not a Core table should be moved to metadata 
						AND infs.TABLE_SCHEMA  <> N'system'
						AND CONCAT(infs.TABLE_SCHEMA,N'.',infs.TABLE_NAME) <> N'global.tCompanies'

				OPEN TableCursor;
				FETCH TableCursor
				INTO @Tablename;
				IF @@CURSOR_ROWS = 0
					BEGIN
						PRINT CONCAT(N'No Tables found for generic deletion over DataSourceKey ',@DataSourceKey,N'.')
					END
				WHILE @@FETCH_STATUS = 0
				BEGIN
					-- clear variables
					SET @SQL = N'';
					SET @SQL = CONCAT(N'DELETE FROM ', @Tablename, N' WHERE DataSourceKey =', @DataSourceKey,';') -- ToDo: ID is numeric at the moment, change to string when changed					
					
					PRINT CONCAT(N'Delete all Rows with DatasourceKey ', @DataSourceKey, N' in Table ',@Tablename)
					EXEC (@SQL);

					FETCH TableCursor
					INTO @Tablename;
				END;
				CLOSE TableCursor;
				DEALLOCATE TableCursor;

				-- Generic delete is still done over CompanyKey
				-- GET all Non-Core Tables where the column CompanyKey is used (usually result schema) and delete the Values for this ComanpanyKey
				-- The Company Key is an combinded Key "DataSourceKey | CompanyID"
				-- some are deleted the second time, as they have the Datasource Key also !!

				PRINT '';
				PRINT 'Starting generic Delete Process for all Tables outside system containing the Column "CompanyKey" which is a concated Key with the Datasource.';
				PRINT '#####################################################################################################';
				PRINT '';
				DECLARE TableCursor CURSOR FOR
					SELECT      
							CONCAT(infs.TABLE_SCHEMA COLLATE DATABASE_DEFAULT,N'.',infs.TABLE_NAME COLLATE DATABASE_DEFAULT)	AS Tablename

					FROM INFORMATION_SCHEMA.COLUMNS infs
						-- Determination Objekttype
						LEFT JOIN 
								(
								SELECT 
									 Name					COLLATE DATABASE_DEFAULT AS Objektname
									,SCHEMA_NAME(Schema_id) COLLATE DATABASE_DEFAULT AS Schemaname
									,type_desc				COLLATE DATABASE_DEFAULT AS Objekttyp
								FROM sys.objects
								) syo ON 
											syo.Schemaname = infs.TABLE_SCHEMA
										AND syo.Objektname = infs.TABLE_NAME

				WHERE 
							syo.Objekttyp		= N'USER_TABLE'
						AND infs.COLUMN_NAME	= N'CompanyKey'
						-- ToDo: Determination not a Core table should be moved to metadata 
						AND infs.TABLE_SCHEMA  <> N'system'
						AND CONCAT(infs.TABLE_SCHEMA,N'.',infs.TABLE_NAME) <> N'global.tCompanies';
				OPEN TableCursor;
				FETCH TableCursor
				INTO @Tablename;
					IF @@CURSOR_ROWS = 0
						BEGIN
							PRINT CONCAT(N'No Tables found for generic deletion over CompanyKeys starting with ',@DataSourceKey,N'| .')
						END
				WHILE @@FETCH_STATUS = 0
					BEGIN
						-- clear variables
						SET @SQL = N'';
						SET @SQL = CONCAT(N'DELETE FROM ', @Tablename, N' WHERE CompanyKey LIKE ''', @DataSourceKey,N'|%'';') 

						PRINT CONCAT(N'Delete all Rows with DatasourceKey ', @DataSourceKey, N' in CompanyKey in Table ',@Tablename)
						EXEC (@SQL);

						FETCH TableCursor
						INTO @Tablename;
					END;
				CLOSE TableCursor;
				DEALLOCATE TableCursor;

				PRINT ''
				PRINT 'End of generic delition'
				PRINT  '######################################################################'
				PRINT ''

				PRINT '';
				PRINT 'Starting Delete for Core Data'
				PRINT '#####################################################################################################';
				PRINT '';
								
				-- PIPELINES - Company selection is deleted in all steps, but not the steps themself using the datasource

				-- system.tPipelineSteps  - TODO - in new field CompaniesJSON
			
				-- global.tCompanies
                IF OBJECT_ID('global.tCompanies') IS NOT NULL
                BEGIN
				    DELETE FROM global.tCompanies WHERE DataSourceKey = @DataSourceKey;
                END

				PRINT 'Delete the datasource itself'
				-- DELETE Datasource (as last due to calculated / dependend columns)
				DELETE FROM system.tDataSources WHERE DataSourceID = @DataSourceID;

				SET @ResultCode = 200;
			COMMIT TRANSACTION spDELETE_DataSource
		END TRY

		-- START CATCH				***********************************************************************************
		BEGIN CATCH
			DECLARE @Error_state INT = ERROR_STATE();
			SET @Comment = ERROR_MESSAGE();
			
			ROLLBACK TRANSACTION spDELETE_DataSource	

			IF @Error_state <> 10 
			BEGIN
				SET @ResultCode = 500;		
				PRINT N'Rollback due to not executable command.';
			END
			ELSE IF @ResultCode IS NULL OR @ResultCode/100 = 2
			BEGIN
				SET @ResultCode = 500;	
			END;
		END CATCH

		EXEC system.spPOST_APILogEntry @Username, @TransactUsername, @ProcedureName, @ParameterString, @EffectedRows, @ResultCode, @TimestampCall, @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_DataSource'         -- 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 Datasouce - the data from its proceeded companies is deleted in all tables with Columns DatasourceKey or CompanyKey too, but the datesource is keept in Process Steps.';    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name;
  
-- optional SET parameter documentation (only for Core / Standardmodules)
    SET @level2name = N'@DataSourceID';
    SET @value      = N'ID of the DataSource to be deleted.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;


	GO


DROP PROCEDURE IF EXISTS system.spEXPORT_Action;
GO

/*
	Procedure to EXPORT Action information in JSON format
 
	Saxess Software GmbH

	Testcall
		DECLARE @RC INT;
		EXEC @RC = system.spEXPORT_Action
			 @Username = 'SQL',
			 @ActionIDJSON = '["A1", "A2"]',
			 @ExportType = 'JSON';
		SELECT @RC;

	Testcall Documentation
		SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'system', 'PROCEDURE', 'spEXPORT_Action', NULL, NULL)
		UNION ALL
		SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'system', 'PROCEDURE', 'spEXPORT_Action', 'PARAMETER', NULL)
*/
 
CREATE PROCEDURE system.spEXPORT_Action
    @Username     NVARCHAR(255),
	@ActionIDJSON NVARCHAR(MAX),
    @ExportType   NVARCHAR(50) = 'JSON'
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, @ActionIDJSON, @ExportType;
		EXEC system.spGET_TransactUsername @TransactUsername OUTPUT, @Username;
		
		-- Input parameter handling
        IF ISJSON(@ActionIDJSON) = 0
			EXEC system.spSEND_Message 'ERROR', 'Invalid JSON in ActionIDJSON parameter.';

        IF @ExportType = 'JSON'
        BEGIN
		    -- Return Action information
		    SELECT (
                SELECT
                   a.ActionID
                 , a.OrderIndex
                 , a.Active
                 , a.Name
                 , a.Description
                 , a.RequiresClusterWriteRights
                 , a.RequiresWriteRights
                 , a.Condition
                 , a.ListCustomTitle
                 , a.ListSQL
                 , a.ListLayout
                 , a.ListExcelExport
                 , a.SetCellValueAsFilter
                 , a.ReloadListAfterChangingFilter
                 , a.ActionSQL
                 , a.ApplyFromField
                 , a.SaveBeforeAction
                 , a.SaveAfterAction
                 , a.ReloadTreeAfterAction
                 , a.ReloadDetailsAfterAction
                FROM system.tActions AS a
                JOIN OPENJSON(@ActionIDJSON)
                WITH (ActionID NVARCHAR(50) '$') AS ActionIDList
                ON ActionIDList.ActionID = a.ActionID
                FOR JSON PATH, ROOT('Actions')
            ) AS Command;

		    SET @AffectedRows = @@ROWCOUNT;

		    IF @AffectedRows = 0
			    EXEC system.spSEND_Message 'ERROR', 'No Action found';
        END
        
		IF @ExportType = 'SQL'
		BEGIN
			-- Create a temporary table to store the collection of export commands
			DROP TABLE IF EXISTS #tExport;
			CREATE TABLE #tExport (
				RowKey 			BIGINT IDENTITY(1,1)
			  , MainOrderNumber	INT 										NOT NULL
			  , SubOrderNumber	INT											NOT NULL
			  , Command			NVARCHAR(MAX)	COLLATE DATABASE_DEFAULT	NOT NULL
			  , PRIMARY KEY CLUSTERED (RowKey)
			);

			INSERT INTO #tExport
			VALUES 
				(10, 1, N'-- CONFIG: Adjust variable values manually to fit your needs!')
			  , (10, 2, N'DECLARE @Username NVARCHAR(255) = ''SQL''')
			  , (10, 3, N'')

			-- Create the Action as new Action
			INSERT INTO #tExport
			SELECT 
				1000	AS MainOrderNumber
			  , 1000	AS SubOrderNumber
			  , CONCAT(
			  		'EXEC system.spDELETE_Action'
			  	  , ' @Username = @Username'
			  	  , ', @ActionID = ''', a.ActionID, ''';'
				  , CHAR(13), CHAR(10)
			  	  , 'EXEC system.spPOST_Action'
			  	  , ' @Username = @Username'
			  	  , ', @ActionID = ''', a.ActionID, ''''
			  	  , ', @OrderIndex = ', a.OrderIndex
			  	  , ', @Active = ', a.Active
			  	  , ', @Name = ''', system.fMaskSQL(a.Name), ''''
			  	  , ', @Description = ''', system.fMaskSQL(a.Description), ''''
			  	  , ', @RequiresClusterWriteRights = ', a.RequiresClusterWriteRights
			  	  , ', @RequiresWriteRights = ', a.RequiresWriteRights
			  	  , ', @Condition = ''', system.fMaskSQL(a.Condition), ''''
			  	  , ', @ListCustomTitle = ''', system.fMaskSQL(a.ListCustomTitle), ''''
			  	  , ', @ListSQL = ''', system.fMaskSQL(a.ListSQL), ''''
			  	  , ', @ListLayout = ''', system.fMaskSQL(a.ListLayout), ''''
			  	  , ', @ListExcelExport = ', a.ListExcelExport
			  	  , ', @SetCellValueAsFilter = ', a.SetCellValueAsFilter
			  	  , ', @ReloadListAfterChangingFilter = ', a.ReloadListAfterChangingFilter
			  	  , ', @ActionSQL = ''', system.fMaskSQL(a.ActionSQL), ''''
			  	  , ', @ApplyFromField = ''', system.fMaskSQL(a.ApplyFromField), ''''
			  	  , ', @SaveBeforeAction = ''', a.SaveBeforeAction, ''''
			  	  , ', @SaveAfterAction = ', a.SaveAfterAction
			  	  , ', @ReloadTreeAfterAction = ', a.ReloadTreeAfterAction
			  	  , ', @ReloadDetailsAfterAction = ', a.ReloadDetailsAfterAction
				) AS Command
			FROM system.tActions AS a
			JOIN OPENJSON(@ActionIDJSON)
			WITH (ActionID NVARCHAR(50) '$') AS ActionIDList
			ON ActionIDList.ActionID = a.ActionID;

			-- Final GO
			INSERT INTO #tExport
			VALUES (9999, 1, N'GO');

			-- Data Transaction
			SELECT Command
			FROM #tExport
			ORDER BY 
			    MainOrderNumber
			  , SubOrderNumber
		END

		SET @ResultCode = 200
	END TRY
	BEGIN CATCH
		SET @ResultCode = 500;
		SET @Comment = ERROR_MESSAGE();
	END CATCH;
	
	EXEC system.spPOST_APILogEntry @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'system'			    -- enter schema name of the table
    ,@level1name        NVARCHAR(255)  = N'spEXPORT_Action'    -- enter procedure name
    ,@SX_Owner	        NVARCHAR(255)  = N'OCT.core'		    -- enter owner name of the procedure from list (OCT.core, OCT.Actions, Custom)
    ,@SX_Action			NVARCHAR(255)  = N'CORE'			    -- enter Action 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_Action;
    EXEC sys.sp_addextendedproperty N'SX_Action'		,@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 EXPORT an Action in JSON format.';
    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'@ActionIDJSON';
    SET @value      = N'ID of the requested Actions in JSON format.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;
GO


DROP PROCEDURE IF EXISTS system.spEXPORT_Pipeline;
GO

/*
	Procedure to create an OCT Importscript for an selected Pipeline - used for export  / import of pipelines
		- exports the pipeline with its steps an schedules
		- NO global parameters used by the pipeline are exported (would be possible, but complex JSON operation)
		- conditions and consequences
			- a new Pipeline with the next free ID is created
			- the user must after the import or before
				- create a datasource(s) with type fitting to the used steps
				- download / activate modules needed by the steps
				- select companies in the datasource an in the steps
				- create global parameters used in the steps

	Saxess Software GmbH
 
	Testcall Procedure
		DECLARE  @RC INT;
		EXEC	 @RC = system.spEXPORT_Pipeline
				 @Username		= 'SQL'
				,@PipelineID	= 'P1'
				,@ExportType	= 'SQL'
		PRINT    @RC

	Testcall Documentation
		SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'system', 'PROCEDURE', 'spEXPORT_Pipeline',NULL,NULL)
		UNION ALL
		SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'system', 'PROCEDURE', 'spEXPORT_Pipeline','PARAMETER',NULL)
*/
 
CREATE PROCEDURE system.spEXPORT_Pipeline
    @Username      NVARCHAR(255),
    @PipelineID    NVARCHAR(50),
    @ExportType    NVARCHAR(50) = 'SQL'  -- aus Kompatibilitätsgründen
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, @PipelineID, @ExportType;
		EXEC system.spGET_TransactUsername @TransactUsername OUTPUT, @Username;

		-- Procedure specific declarations
		DECLARE @SourceClusterName		NVARCHAR(255)
		DECLARE @SourceClusterAPI		NVARCHAR(255);

		-- NULL Protection for all Input parameters
		IF @Username IS NULL SET @Username = N'';
		IF @PipelineID IS NULL SET @PipelineID = N'';
		
		IF @ExportType = 'SQL'
		BEGIN
			-- Create a temporary table to store the collection of export commands
			DROP TABLE IF EXISTS #tExport;
			CREATE TABLE #tExport
				(
						RowKey					BIGINT IDENTITY (1,1)
					,MainOrderNumber		INT						NOT NULL
					,SubOrderNumber			INT						NOT NULL
					,Command				NVARCHAR(MAX)	COLLATE DATABASE_DEFAULT	NOT NULL

					,PRIMARY KEY CLUSTERED (RowKey)
				);

			-- Determine Metadata
			SELECT @SourceClusterName = ValueText FROM system.tSettings WHERE SettingID = 'Clustername'; -- May be empty if not defined
			SET @SourceClusterName = CONCAT(DB_Name(),' ',@SourceClusterName);
			SELECT @SourceClusterAPI = ValueText FROM system.tSettings WHERE SettingID = 'DBVersion';

			-- MetaHeader
			INSERT INTO #tExport
				VALUES 
						(0, 1, N'-- {')
					,(0, 2, N'-- "Type": "Pipeline",')
					,(0, 3, CONCAT(N'-- "SourceClusterName": "', @SourceClusterName,'",'))
					,(0, 4, CONCAT(N'-- "SourceClusterAPI": "',@SourceClusterAPI,'",'))
					,(0, 5, CONCAT(N'-- "SourcePipelineID": "',@PipelineID,'"'))
					,(0, 6, N'-- }')

			INSERT INTO #tExport
				VALUES 
						(10, 1, N'--CONFIG: Adjust variable values manually to fit your needs !')
					,(10, 2, N'DECLARE @Username NVARCHAR(255)			= ''SQL''')
					,(10, 3, N'DECLARE @PipelineID NVARCHAR(255)			= ''' + @PipelineID + N'''')
					,(10, 4, N'--This PipelneID will be deleted during import, if it exists. You should be sure !')
					,(10, 5, N'DECLARE @ReturnedPipelineID NVARCHAR (50);');

			-- Try_Delete existing Pipeline 
			INSERT INTO #tExport
				SELECT 
						900	AS MainOrderNumber
					,1000	AS SubOrderNumber
					,CONCAT(
						'EXEC system.spDELETE_Pipeline '
						,'''SQL'''
						,',@PipelineID'
						,';'
						)	AS Command
				FROM system.tPipelines
				WHERE PipelineID = @PipelineID;

			-- Create the Pipeline as new Pipeline and catch the ID
			INSERT INTO #tExport
				SELECT 
						1000	AS MainOrderNumber
					,1000	AS SubOrderNumber
					,CONCAT(
						'EXEC system.spPOST_Pipeline '
						,'''SQL'''
						,',@PipelineID'
						,','''	,system.fMaskSQL(PipelineName)		,''''
						,','''	,system.fMaskSQL(PipelineDescription),''''
						,','''	,PipelineDescriptionIconColor			,''''
						,','	,'NULL'									---NULL leads to positon at the end
						,','''	,EmailNotificationCODE					, ''''
						,','''	,ExcludedExecutionTimesJSON					, ''''
						,', @OutputID = @ReturnedPipelineID OUTPUT;'	
						)	AS Command
				FROM system.tPipelines
				WHERE PipelineID = @PipelineID;
		
			-- Create the Steps
			INSERT INTO #tExport
				SELECT 
						2000	AS MainOrderNumber
					,1000	AS SubOrderNumber
					,CONCAT(
						'EXEC system.spPOST_PipelineStep '
						,'''SQL'''
						,','	,'@ReturnedPipelineID'		
						,','''  ,tPS.StepID, ''''
						,','''	,system.fMaskSQL(StepName)			,''''
						,','''	,system.fMaskSQL(StepDescription)	,''''
						,','	,OrderOfExecution										
						,','''	,StepDataSourceID						, ''''
						,','''	,StepCompanyIDsJSON						, ''''
						,','''	,system.fMaskSQL(StepDetailsJSON)	, ''''  --inverted commas inside sql statements etc. are handled by the pMaskSQL function
						,','	,TimeExecutionLimit			
						,','	,Active
						,',''''' -- keep old ModuleID parameter for compatibility reasons
						,','''	,RunConditionCODE					    ,''''
						,','    ,RestartOnError                         ,';'	
					)	AS Command
				FROM system.tPipelineSteps tPS
					INNER JOIN system.tPipelines tP
						ON tPS.PipelineKey = tP.PipelineKey
				WHERE tP.PipelineID = @PipelineID
				ORDER BY OrderOfExecution;

			-- Create the Schedulers

			INSERT INTO #tExport
				SELECT 
						3000	AS MainOrderNumber
					,1000	AS SubOrderNumber
					,CONCAT(
						'EXEC system.spPOST_PipelineSchedule '
						,'''SQL'''
						,','	,'@ReturnedPipelineID'		
						,',''*'''
						,','''	,ScheduleName							,''''
						,','''	,system.fMaskSQL(ScheduleDescription),''''
						,','''	,system.fMaskSQL(ScheduleDetailsJSON),''''
						,','	,Active									,';'	
						
						)	AS Command
				FROM system.tPipelineSchedules tPS
					INNER JOIN system.tPipelines tP
						ON tPS.PipelineKey = tP.PipelineKey
				WHERE tP.PipelineID = @PipelineID;

			-- final GO
			INSERT INTO #tExport
				VALUES 
						(9999, 1, N'GO');

			-- Data Transaction
			SELECT 
				Command

			FROM #tExport
			ORDER BY 
					MainOrderNumber
				,SubOrderNumber

		END

		IF @ExportType = 'JSON'
		BEGIN
		    -- Return Pipeline information as JSON
		    SELECT (
                SELECT
                    p.PipelineID
                  , p.PipelineName
                  , p.PipelineDescription
                  , p.PipelineDescriptionIconColor
                  , p.OrderOfDisplay
                  , p.EmailNotificationCODE
                  , p.ExcludedExecutionTimesJSON
				  , (
					    SELECT
				    		ps.StepID,
				    		ps.StepName,
				    		ps.StepDescription,
				    		ps.OrderOfExecution,
				    		ps.StepDataSourceID,
				    		ps.StepCompanyIDsJSON,
				    		ps.StepDetailsJSON,
				    		ps.TimeExecutionLimit,
				    		ps.Active,
				    		ps.RunConditionCODE,
				    		ps.RestartOnError
				    	FROM system.tPipelineSteps AS ps
				    	WHERE ps.PipelineKey = p.PipelineKey
				    	FOR JSON PATH
				    ) AS Steps
				  , (
						SELECT
							psch.ScheduleID,
							psch.ScheduleName,
							psch.ScheduleDescription,
							psch.ScheduleDetailsJSON,
							psch.Active
						FROM system.tPipelineSchedules AS psch
						WHERE psch.PipelineKey = p.PipelineKey
						FOR JSON PATH
					) AS Schedules
				FROM system.tPipelines AS p
                FOR JSON PATH, ROOT('Pipelines')
            ) AS Command;

		    SET @AffectedRows = @@ROWCOUNT;

		    IF @AffectedRows = 0
			    EXEC system.spSEND_Message 'ERROR', 'No Action found';
		END

		SET @ResultCode = 200;
	END TRY
	BEGIN CATCH
		SET @ResultCode = 500;
		SET @Comment = ERROR_MESSAGE();
	END CATCH;
	
	EXEC system.spPOST_APILogEntry @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'system'    -- enter schema name of the table
    ,@level1name        NVARCHAR(255)   = N'spEXPORT_Pipeline'         -- 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 an SQL Script or JSON object which can be exported.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name;
 
-- optional SET parameter documentation (only for Core / Standardmodules)
    SET @level2name = N'@PipelineID';
    SET @value      = N'ID of the Pipeline to be exported.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;

    SET @level2name = N'@ExportType';
    SET @value      = N'As export type either SQL (default) or JSON.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;
GO


DROP PROCEDURE IF EXISTS system.spGET_UserRight;
GO

/*
    GET Operation for User rights in DataEntry

    Saxess Software GmbH
 
    Testcall Procedure
        DECLARE @RC INT;
        EXEC @RC = system.spGET_UserRight
            @Username = N'SQL'
          , @RequestedUserName = N'W10\admin';
        PRINT @RC;

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

CREATE PROCEDURE system.spGET_UserRight
    @Username           NVARCHAR(255)
  , @RequestedUserName  NVARCHAR(255) = NULL
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, 2, @Username, @RequestedUserName;
        EXEC system.spGET_TransactUsername @TransactUsername OUTPUT, @Username;        

        -- Variables
        DECLARE @UserKey INT = 0;
        DECLARE @Right   NVARCHAR(50) = N'';

        -- Input parameter handling
        SET @Username = COALESCE(system.fProtectString(@Username), N'');
        SET @RequestedUserName = COALESCE(system.fProtectString(@RequestedUserName), N'');

        -- Error handling
        IF @RequestedUserName = N''
            EXEC system.spSEND_Message N'ERROR', N'RequestedUserName is not set.';
        
        -- Determine UserKey
        SELECT @UserKey = UserKey
        FROM system.trUser
        WHERE UserName = @RequestedUserName;
        
        IF @UserKey = 0
            EXEC system.spSEND_Message N'ERROR', N'RequestedUserName doesn''t exist.';

        -- Check rights 
        IF @TransactUsername = @RequestedUserName
        BEGIN
            -- read only for himself (is administrator anyways)
            SET @Right = N'Read';
        END
        ELSE
        BEGIN
            EXEC @ResultCode = system.spGET_ClusterWriteRightTransactionUser @TransactUsername;
        
            IF @ResultCode = 200
            BEGIN
                SET @Right = N'Write';
            END
            ELSE
            BEGIN
                EXEC system.spSEND_Message N'ERROR', N'Insufficient rights.';
            END;
        END;

        -- Temp table
        DROP TABLE IF EXISTS #FullStructure;

        CREATE TABLE #FullStructure (
            RowKey          BIGINT          IDENTITY (1,1)              NOT NULL
          , Level           NVARCHAR(255)   COLLATE DATABASE_DEFAULT    NOT NULL
          , FactoryKey      BIGINT                                      NOT NULL
          , ProductLineKey  BIGINT                                      NOT NULL
          , FactoryID       NVARCHAR(255)   COLLATE DATABASE_DEFAULT    NOT NULL
          , ProductLineID   NVARCHAR(255)   COLLATE DATABASE_DEFAULT    NOT NULL
        );

        -- Cluster level
        INSERT INTO #FullStructure
        SELECT 
            'Cluster' AS Level
          , 0         AS FactoryKey
          , 0         AS ProductLineKey
          , ''        AS FactoryID
          , ''        AS ProductLineID;
        
        -- Factory level
        INSERT INTO #FullStructure
        SELECT
            'Factory'  AS 'Level'
          , FactoryKey AS FactoryKey
          , 0          AS ProductLineKey
          , FactoryID  AS FactoryID
          , ''         AS ProductLineID
        FROM planning.tdFactories;
                
        -- ProductLine level
        INSERT INTO #FullStructure
        SELECT
            'ProductLine'      AS Level
          , dF.FactoryKey      AS FactoryKey
          , dPL.ProductLineKey AS ProductLineKey
          , dF.FactoryID       AS FactoryID
          , dPL.ProductLineID  AS ProductLineID
        FROM planning.tdProductLines   AS dPL 
        LEFT JOIN planning.tdFactories AS dF
        ON dPL.FactoryKey = dF.FactoryKey;
        
        -- User himself  
        IF @Right = N'Read'
        BEGIN
            SELECT 
                vUR.UserName                AS UserName
              , vUR.FactoryID               AS FactoryID
              , COALESCE(dF.NameShort, '')  AS FactoryName
              , vUR.ProductLineID           AS ProductLineID
              , COALESCE(dPL.NameShort, '') AS ProductLineName
              , vUR."Right"                 AS "Right"
              , vUR.ReadCommentMandatory    AS ReadCommentMandatory
              , vUR.WriteCommentMandatory   AS WriteCommentMandatory
            FROM system.trUserRights          AS vUR
            LEFT JOIN planning.tdFactories    AS dF
            ON vUR.FactoryID = dF.FactoryID
            LEFT JOIN planning.tdProductLines AS dPL
            ON vUR.FactoryID = dPL.FactoryID 
                AND vUR.ProductLineID = dPL.ProductLineID
            WHERE vUR.UserKey = @UserKey 
            ORDER BY 
                CASE
                    WHEN vUR.FactoryID = '' THEN 0
                    ELSE 1
                END
              , ISNULL(TRY_CAST(vUR.FactoryID AS INT), 999999999)
              , vUR.FactoryID
              , ISNULL(TRY_CAST(vUR.ProductLineID AS INT), 999999999)
              , vUR.ProductLineID;
        END
        
        -- Other user
        IF @Right = N'Write'
        BEGIN
            SELECT
                @RequestedUsername                     AS UserName
              , fs.FactoryID                           AS FactoryID
              , COALESCE(dF.NameShort, '')             AS FactoryName
              , fs.ProductLineID                       AS ProductLineID
              , COALESCE(dPL.NameShort, '')            AS ProductLineName
              , COALESCE(vUR."Right", 'Deny')          AS "Right"
              , COALESCE(vUR.ReadCommentMandatory, 0)  AS ReadCommentMandatory
              , COALESCE(vUR.WriteCommentMandatory, 0) AS WriteCommentMandatory
            FROM #FullStructure               AS fs
            LEFT JOIN planning.tdFactories    AS dF
            ON fs.FactoryID = dF.FactoryID
            LEFT JOIN planning.tdProductLines AS dPL
            ON fs.FactoryID = dPL.FactoryID 
                AND fs.ProductLineID = dPL.ProductLineID
            -- positive Rechte
            LEFT JOIN (
                SELECT
                    vUR.FactoryID
                  , vUR.ProductLineID
                  , vUR."Right"
                  , vUR.ReadCommentMandatory
                  , vUR.WriteCommentMandatory
                FROM system.trUserRights AS vUR
                WHERE vUR.UserKey = @UserKey
            )                                 AS vUR
            ON fs.FactoryID = vUR.FactoryID
                AND fs.ProductLineID = vUR.ProductLineID
            ORDER BY
                CASE
                    WHEN fs.FactoryID = '' THEN 0
                    ELSE 1
                END
              , ISNULL(TRY_CAST(fs.FactoryID AS INT), 999999999)
              , fs.FactoryID
              , ISNULL(TRY_CAST(fs.ProductLineID AS INT), 999999999)
              , fs.ProductLineID;
        END

        SET @AffectedRows = @@ROWCOUNT;

        SET @ResultCode = 200
    END TRY
    BEGIN CATCH
        SET @ResultCode = 500;
        SET @Comment = ERROR_MESSAGE();
    END CATCH;
    
    EXEC system.spPOST_APILogEntry @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'system'                -- enter schema name of the table
    ,@level1name        NVARCHAR(255)  = N'spGET_UserRight'        -- 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'GET Operation for User rights';
    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'@RequestedUserName';
    SET @value      = N'RequestedUserName';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;
GO


 DROP PROCEDURE IF EXISTS system.spMOVE_PipelineStep;
GO

/*
    Procedure to MOVE a Pipeline Step

    Saxess Software GmbH

    Testcall
        DECLARE @RC INT;
        EXEC @RC = system.spMOVE_PipelineStep
                @Username = N'SQL'
              , @PipelineID = N'P1'
              , @SourceStepID = N'S1'
              , @TargetStepID = N'Step1'
        SELECT @RC;

    Testcall Documentation
        SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'system', 'PROCEDURE', 'spMOVE_PipelineStep', NULL, NULL)
        UNION ALL
        SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'system', 'PROCEDURE', 'spMOVE_PipelineStep', 'PARAMETER', NULL)
*/
 
CREATE PROCEDURE system.spMOVE_PipelineStep
    @Username       NVARCHAR(255)
  , @PipelineID     NVARCHAR(255)
  , @SourceStepID   NVARCHAR(255)
  , @TargetStepID   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, 4, @Username, @PipelineID, @SourceStepID, @TargetStepID;
        EXEC system.spGET_TransactUsername @TransactUsername OUTPUT, @Username;
        
        -- Input parameter handling
        SET @PipelineID = COALESCE(system.fProtectID(@PipelineID), N'');
        SET @SourceStepID = COALESCE(system.fProtectID(@SourceStepID), N'');
        SET @TargetStepID = COALESCE(system.fProtectID(@TargetStepID), N'');
        
        -- Error handling
        IF @PipelineID = N''
            EXEC system.spSEND_Message N'ERROR', N'No PipelineID provided'

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

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

        IF EXISTS(
            SELECT 1
            FROM system.tPipelineSteps
            WHERE
                PipelineKey = (
                    SELECT PipelineKey
                    FROM system.tPipelines
                    WHERE PipelineID = @PipelineID
                )
                AND StepID = @TargetStepID
        )
            EXEC system.spSEND_Message N'ERROR', N'TargetStepID already exists'
        
        -- Rename Pipeline
        UPDATE system.tPipelineSteps
        SET StepID = @TargetStepID
        WHERE
            PipelineKey = (
                SELECT PipelineKey
                FROM system.tPipelines
                WHERE PipelineID = @PipelineID
            )
            AND StepID = @SourceStepID;

        SET @AffectedRows = @@ROWCOUNT;

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

        SET @ResultCode = 200
    END TRY
    BEGIN CATCH
        SET @ResultCode = 500;
        SET @Comment = ERROR_MESSAGE();
    END CATCH;

    EXEC system.spPOST_APILogEntry @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_PipelineStep'    -- enter procedure name
    ,@SX_Owner	        NVARCHAR(255)  = N'OCT.core'		    -- enter owner name of the procedure from list (OCT.core, OCT.Pipelines, Custom)
    ,@SX_Pipeline		NVARCHAR(255)  = N'CORE'			    -- enter Pipeline 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_Pipeline;
    EXEC sys.sp_addextendedproperty N'SX_Pipeline'		,@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 Pipeline Step';
    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'@PipelineID';
    SET @value      = N'PipelineID of the step that should be renamed.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;

    SET @level2name = N'@SourceStepID';
    SET @value      = N'StepID of the step that should be renamed.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;

    SET @level2name = N'@TargetStepID';
    SET @value      = N'New StepID of the step.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;
GO


DROP PROCEDURE IF EXISTS system.spPOST_Action;
GO

/*
    Procedure to POST Action information
 
    Saxess Software GmbH

    Testcall
        DECLARE @RC INT;
        DECLARE @ReturnedActionID NVARCHAR(50);
        EXEC @RC = system.spPOST_Action
            @Username = 'SQL'
          , @ActionID = '*'
          , @OrderIndex = NULL
          , @Active = 1
          , @Name = 'ActionTest'
          , @Description = 'Short description.'
          , @RequiresClusterWriteRights = 0
          , @RequiresWriteRights = 1
          , @Condition = ''
          , @ListCustomTitle = 'Eine Action'
          , @ListSQL = 'SELECT * FROM global.tCodes'
          , @ListLayout = ''
          , @ListExcelExport = 1
          , @SetCellValueAsFilter = 0
          , @ReloadListAfterChangingFilter = 0
          , @ActionSQL = ''
          , @ApplyFromField = ''
          , @SaveBeforeAction = 'NO'
          , @SaveAfterAction = 0
          , @ReloadTreeAfterAction = 0
          , @ReloadDetailsAfterAction = 0
          , @OutputID = @ReturnedActionID OUTPUT;
        SELECT @ReturnedActionID;
        SELECT @RC;

    Testcall Documentation
        SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'system', 'PROCEDURE', 'spPOST_Action', NULL, NULL)
        UNION ALL
        SELECT * FROM ::fn_listextendedproperty (NULL, 'SCHEMA', 'system', 'PROCEDURE', 'spPOST_Action', 'PARAMETER', NULL)
*/
 
CREATE PROCEDURE system.spPOST_Action
    @Username                       NVARCHAR(255)
  , @ActionID                       NVARCHAR(255)  = NULL
  , @OrderIndex                     INT            = NULL
  , @Active                         BIT            = NULL
  , @Name                           NVARCHAR(255)  = NULL
  , @Description                    NVARCHAR(4000) = NULL
  , @RequiresClusterWriteRights     BIT            = NULL
  , @RequiresWriteRights            BIT            = NULL
  , @Condition                      NVARCHAR(MAX)  = NULL
  , @ListCustomTitle                NVARCHAR(255)  = NULL
  , @ListSQL                        NVARCHAR(MAX)  = NULL
  , @ListLayout                     NVARCHAR(MAX)  = NULL
  , @ListExcelExport                BIT            = NULL
  , @SetCellValueAsFilter           BIT            = NULL
  , @ReloadListAfterChangingFilter  BIT            = NULL
  , @ActionSQL                      NVARCHAR(MAX)  = NULL
  , @ApplyFromField                 NVARCHAR(255)  = NULL
  , @SaveBeforeAction               NVARCHAR(20)   = NULL
  , @SaveAfterAction                BIT            = NULL
  , @ReloadTreeAfterAction          BIT            = NULL
  , @ReloadDetailsAfterAction       BIT            = NULL
  , @OutputID                       NVARCHAR(50)   = NULL OUTPUT
AS
BEGIN
    BEGIN TRY
        -- Start transaction
        BEGIN TRANSACTION spPOST_Action

        -- 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, 21, @Username, @ActionID, @OrderIndex, @Active, @Name, @Description, @RequiresClusterWriteRights, @RequiresWriteRights, @Condition, @ListCustomTitle, @ListSQL, @ListLayout, @ListExcelExport, @SetCellValueAsFilter, @ReloadListAfterChangingFilter, @ActionSQL, @ApplyFromField, @SaveBeforeAction, @SaveAfterAction, @ReloadTreeAfterAction, @ReloadDetailsAfterAction;
        EXEC system.spGET_TransactUsername @TransactUsername OUTPUT, @Username;
        
        -- Variables
        DECLARE @ActionKey        BIGINT = 0;
        DECLARE @OldOrderIndex    INT    = 0;
        
        -- Input parameter handling
        SET @ActionID = COALESCE(system.fProtectID(@ActionID), N'');
        SET @Name = COALESCE(system.fProtectString(@Name), N'');
        SET @Description = COALESCE(system.fProtectString(@Description), N'');

        IF COALESCE(@ActionID, N'') = N''
            EXEC system.spSEND_Message 'ERROR', 'No ActionID provided';

        IF COALESCE(@Name, N'') = N''
            EXEC system.spSEND_Message 'ERROR', 'No name provided';

        -- Determine existing Action
        SELECT
            @ActionKey = ActionKey
          , @OldOrderIndex = OrderIndex
        FROM system.tActions
        WHERE ActionID = @ActionID;
        
        IF @ActionKey <> 0
        BEGIN
            -- Update existing data
            UPDATE system.tActions
            SET
                OrderIndex = @OrderIndex
              , Active = @Active
              , Name = @Name
              , Description = @Description
              , RequiresClusterWriteRights = @RequiresClusterWriteRights
              , RequiresWriteRights = @RequiresWriteRights
              , Condition = @Condition
              , ListCustomTitle = @ListCustomTitle
              , ListSQL = @ListSQL
              , ListLayout = @ListLayout
              , ListExcelExport = @ListExcelExport
              , SetCellValueAsFilter = @SetCellValueAsFilter
              , ReloadListAfterChangingFilter = @ReloadListAfterChangingFilter
              , ActionSQL = @ActionSQL
              , ApplyFromField = @ApplyFromField
              , SaveBeforeAction = @SaveBeforeAction
              , SaveAfterAction = @SaveAfterAction
              , ReloadTreeAfterAction = @ReloadTreeAfterAction
              , ReloadDetailsAfterAction = @ReloadDetailsAfterAction
            WHERE ActionID = @ActionID;

            SET @AffectedRows = @@ROWCOUNT
        END
        ELSE
        BEGIN
            -- Insert new data
            -- Get next free ActionID
            IF @ActionID IN (N'', N'*')
            BEGIN
                SET @ActionID = (
                    SELECT TOP 1 TRY_CAST(RIGHT(ActionID, LEN(ActionID) - 1) AS INT) + 1
                    FROM system.tActions
                    WHERE
                        ActionID LIKE N'A%'
                        AND LEN(ActionID) > 1
                        AND TRY_CAST(RIGHT(ActionID, LEN(ActionID) - 1) AS INT) IS NOT NULL
                    ORDER BY TRY_CAST(RIGHT(ActionID, LEN(ActionID) - 1) AS INT) DESC
                );

                SET @ActionID = COALESCE(N'A' + @ActionID, N'A1');
            END

            INSERT INTO system.tActions (
                ActionID
              , OrderIndex
              , Active
              , Name
              , Description
              , RequiresClusterWriteRights
              , RequiresWriteRights
              , Condition
              , ListCustomTitle
              , ListSQL
              , ListLayout
              , ListExcelExport
              , SetCellValueAsFilter
              , ReloadListAfterChangingFilter
              , ActionSQL
              , ApplyFromField
              , SaveBeforeAction
              , SaveAfterAction
              , ReloadTreeAfterAction
              , ReloadDetailsAfterAction
            )
            VALUES (
                @ActionID
              , (SELECT COALESCE(@OrderIndex, MAX(OrderIndex), 0) + 1 FROM system.tActions)
              , COALESCE(@Active, 0)
              , COALESCE(@Name, N'Unknown')
              , COALESCE(@Description, N'')
              , COALESCE(@RequiresWriteRights, 0)
              , COALESCE(@RequiresWriteRights, 0)
              , COALESCE(@Condition, N'')
              , COALESCE(@ListCustomTitle, N'')
              , COALESCE(@ListSQL, N'')
              , COALESCE(@ListLayout, N'')
              , COALESCE(@ListExcelExport, 1)
              , COALESCE(@SetCellValueAsFilter, 0)
              , COALESCE(@ReloadListAfterChangingFilter, 0)
              , COALESCE(@ActionSQL, N'')
              , COALESCE(@ApplyFromField, N'')
              , COALESCE(@SaveBeforeAction, N'NO')
              , COALESCE(@SaveAfterAction, 0)
              , COALESCE(@ReloadTreeAfterAction, 0)
              , COALESCE(@ReloadDetailsAfterAction, 0)
            );

            SET @AffectedRows = @@ROWCOUNT;
        END

        -- Update other numbers if Number is changed or new
        IF @OldOrderIndex <> @OrderIndex
        BEGIN
            -- case if OrderIndex is bigger now and existed before - decrement all in move window
            IF @OldOrderIndex < @OrderIndex AND @ActionKey <> 0
            BEGIN
                UPDATE system.tActions
                SET Orderindex = Orderindex - 1 
                WHERE 
                    Orderindex >= @OldOrderIndex 
                    AND Orderindex <= @OrderIndex
                    AND ActionID <> @ActionID;
            END

            -- case if OrderIndex is smaller now and existed before - increment all in move window
            IF @OldOrderIndex > @OrderIndex AND @ActionKey <> 0
            BEGIN
                UPDATE system.tActions
                SET Orderindex = Orderindex + 1 
                WHERE 
                    Orderindex <= @OldOrderIndex 
                    AND Orderindex >= @OrderIndex
                    AND ActionID <> @ActionID;
            END

            -- case new Action - increment all following
            IF @ActionKey = 0
            BEGIN
                UPDATE system.tActions
                SET Orderindex = Orderindex + 1 
                WHERE 
                    Orderindex >= @OrderIndex
                    AND ActionID <> @ActionID;
            END

            -- eliminate all Gaps
            UPDATE system.tActions 
            SET Orderindex = Rownumbers.Number
            FROM system.tActions AS tA
            INNER JOIN (
                SELECT 
                    ROW_NUMBER() OVER (ORDER BY OrderIndex) AS Number
                  , ActionID
                FROM system.tActions
            ) AS Rownumbers
            ON tA.ActionID = Rownumbers.ActionID
        END

        SET @OutputID = @ActionID;

        SET @ResultCode = 200

        COMMIT TRANSACTION spPOST_Action
    END TRY
    BEGIN CATCH
        ROLLBACK TRANSACTION spPOST_Action
        SET @ResultCode = 500;
        SET @Comment = ERROR_MESSAGE();
    END CATCH;
    
    EXEC system.spPOST_APILogEntry @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'system'                -- enter schema name of the table
    ,@level1name        NVARCHAR(255)  = N'spPOST_Action'    -- enter procedure name
    ,@SX_Owner            NVARCHAR(255)  = N'OCT.core'            -- enter owner name of the procedure from list (OCT.core, OCT.Actions, Custom)
    ,@SX_Action            NVARCHAR(255)  = N'CORE'                -- enter Action 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_Action;
    EXEC sys.sp_addextendedproperty N'SX_Action'        ,@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 POST Action information';
    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'@ActionID';
    SET @value      = N'Unique ActionID for row identity.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;

    SET @level2name = N'@OrderIndex';
    SET @value      = N'Index which determines the order when displaying the list of all actions.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;

    SET @level2name = N'@Active';
    SET @value      = N'Determines if the action is enabled or disabled.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;
    
    SET @level2name = N'@Name';
    SET @value      = N'Name for the action which is displayed in the selection menu.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;

    SET @level2name = N'@Description';
    SET @value      = N'Details about the action. Displayed in tooltips.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;
 
    SET @level2name = N'@RequiresClusterWriteRights';
    SET @value      = N'Determines if the action is available only if the user has cluster write rights.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;
 
    SET @level2name = N'@RequiresWriteRights';
    SET @value      = N'Determines if the action is available only if the user has write rights on the current object.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;
 
    SET @level2name = N'@Condition';
    SET @value      = N'The condition JSON object that is created by the filter builder.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;
    
    SET @level2name = N'@ListCustomTitle';
    SET @value      = N'Custom title for list SQL dialog.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;
    
    SET @level2name = N'@ListSQL';
    SET @value      = N'SQL script which is executed when displaying the intial list.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;

    SET @level2name = N'@ListLayout';
    SET @value      = N'Layout information for the list as JSON object.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;

    SET @level2name = N'@ListExcelExport';
    SET @value      = N'Allow export of list dialog to Excel.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;

    SET @level2name = N'@SetCellValueAsFilter';
    SET @value      = N'Apply cell value as filter for the list table.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;

    SET @level2name = N'@ReloadListAfterChangingFilter ';
    SET @value      = N'Determines if the list should be reloaded each time the filter in the list dialog is changed.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;

    SET @level2name = N'@ActionSQL';
    SET @value      = N'SQL script which is executed during the action.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;

    SET @level2name = N'@ApplyFromField';
    SET @value      = N'The value of this field is applied to the cell where the action originates from.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;

    SET @level2name = N'@SaveBeforeAction';
    SET @value      = N'Save the system tab or PDT before executing the ActionSQL.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;

    SET @level2name = N'@SaveAfterAction';
    SET @value      = N'Save the system tab or PDT after executing the ActionSQL.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;

    SET @level2name = N'@ReloadTreeAfterAction';
    SET @value      = N'Reload the tree after executing the ActionSQL.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;

    SET @level2name = N'@ReloadDetailsAfterAction';
    SET @value      = N'Reload the system tab or PDT after executing the ActionSQL.';
    EXEC sys.sp_addextendedproperty @name,@value,@level0type,@level0name,@level1type,@level1name,@level2type,@level2name;
GO


UPDATE system.tSettings SET ValueText = '2026.06.0', ValueInt = 2026060 WHERE SettingID = 'DBVersion'