cust_cciw_sp_setTagPermissions

/***

Original Author: nairdo
Per: http://community.shelbysystems.com/arena/m/arena_shared_files_modules/2679
Modifed By: Tony Visconti
5/31/19 - added @organization_id to input and script

Remove existing permissions on all child entities
Add permissions from parent entity
Set the @parent variable below to the profile_id of the parent entity

***/

CREATE PROC [dbo].[cust_cciw_sp_setTagPermissions]
@parent INT, @organization_id INT

AS

DECLARE @profile INT;

DECLARE ProfileCursor CURSOR READ_ONLY FOR
WITH profiletree (
                  [Root]
                , [Parent]
                , [Name]
                , [Level]
                , [ID]
                , [Branch])  AS
(SELECT
    profiles.profile_name AS [Root]
  , CONVERT(VARCHAR(100),'') AS [Parent]
  , profiles.profile_name AS [Name]
  , CONVERT(INT,'0') AS [Level]
  , profiles.profile_id AS [ID]
  , CONVERT(VARCHAR,profiles.profile_id) AS [Branch]
 FROM
    dbo.core_profile AS profiles
 WHERE
    profiles.parent_profile_id = @parent
UNION ALL
 SELECT
    tree.[Root]
  , tree.Name AS [Parent]
  , profiles.profile_name AS [Name]
  , tree.[Level] + 1
  , profiles.profile_id
  , CONVERT(VARCHAR, tree.[Branch] + '.' + CONVERT(VARCHAR,profiles.profile_id))
 FROM
    dbo.core_profile AS profiles
    INNER JOIN profiletree AS tree
        ON profiles.parent_profile_id =  tree.[id])
SELECT id FROM profiletree;

OPEN ProfileCursor

FETCH NEXT FROM ProfileCursor
INTO @profile

WHILE (@@FETCH_STATUS <> -1)
BEGIN

    IF (@@FETCH_STATUS = 0)
    BEGIN

DELETE FROM dbo.secu_permission
WHERE object_type=3 AND object_key =@profile

INSERT INTO dbo.secu_permission (created_by, modified_by, object_type, object_key, operation_type, subject_type, subject_key,organization_id)
SELECT 'AS-IP', 'AS-IP',object_type, @profile, operation_type, subject_type, subject_key, @organization_id
FROM dbo.secu_permission
WHERE object_type=3 and organization_id = @organization_id
AND object_key =@parent

    END

    FETCH NEXT FROM ProfileCursor
    INTO @profile

END

CLOSE ProfileCursor
DEALLOCATE ProfileCursor