Useful Rules

SELECT 
  userTable.name as Name,
  accessSubject.CODE as Code
FROM @TOKEN_SYSTEMDOMAINUSER@.ASF_USER userTable
left join @TOKEN_SYSTEMDOMAINUSER@.ASF_ACCESSSUBJECT accessSubject on accessSubject.ACCESSSUBJECTID = userTable.ACCESSSUBJECTID

Select up.FIRSTNAME as FIRSTNAME, up.LASTNAME as LASTNAME, asu.CODE as ACCESSSUBJECTCODE
From @TOKEN_SYSTEMDOMAINUSER@.Asf_User U
Inner Join @TOKEN_SYSTEMDOMAINUSER@.Asf_Userrole Ur On U.Userid = Ur.Userid
Inner Join @TOKEN_SYSTEMDOMAINUSER@.Asf_Role R On Ur.Roleid = R.Roleid
Inner Join @TOKEN_SYSTEMDOMAINUSER@.User_Profile Up On U.Userid = Up.Userid
inner join @TOKEN_SYSTEMDOMAINUSER@.ASF_ACCESSSUBJECT asu on u.ACCESSSUBJECTID = asu.ACCESSSUBJECTID
where r.CODE = :RoleCode

Select A.Code As Activitycode, A.Name As Activityname, W.Shortname As Workflowname, Count(Wi.Col_Id) As Ownerwicount
  From @TOKEN_SYSTEMDOMAINUSER@.Wf_Activity A
    Inner Join @TOKEN_SYSTEMDOMAINUSER@.Asf_Accessobject Ao On (A.Accessobjectid = Ao.Accessobjectid)
    Inner Join @TOKEN_SYSTEMDOMAINUSER@.Wf_Workflow W On (A.Workflowid = W.Workflowid)
    Left Join Tbl_Ticket Wi On (Wi.Col_Activity = A.Code And Wi.Col_Instancetype = 1 And (Wi.Col_Owner = '@TOKEN_USERACCESSSUBJECT@'
      Or Wi.Col_Owner In (Select Column_Value From Table(@TOKEN_SYSTEMDOMAINUSER@.Asf_Split('@TOKEN_GROUPACCESSSUBJECTS@',',')))))
  where Ao.AccessObjectId in (Select Column_Value From (Table(@TOKEN_SYSTEMDOMAINUSER@.Asf_Getallowedaoforlogini('@TOKEN_SYSTEMDOMAIN@', '@TOKEN_DOMAIN@', '@TOKEN_LOGIN@', '@TOKEN_DOMAIN@_perm_activity_execute'))))
Group By A.Code, A.Name, W.Shortname
Order By A.Name

SELECT P.*
FROM @TOKEN_SYSTEMDOMAINUSER@.ASF_ROLE R
    INNER JOIN @TOKEN_SYSTEMDOMAINUSER@.ASF_ACCESSSUBJECT RAS ON (R.AccessSubjectId = RAS.AccessSubjectId)
    INNER JOIN @TOKEN_SYSTEMDOMAINUSER@.ASF_POOLROLE PR ON (PR.RoleId = R.RoleId)
    INNER JOIN @TOKEN_SYSTEMDOMAINUSER@.ASF_POOL P ON (P.PoolId = PR.PoolId)
    WHERE RAS.CODE IN
    (SELECT COLUMN_VALUE FROM TABLE(@TOKEN_SYSTEMDOMAINUSER@.ASF_SPLITCLOB('@TOKEN_ACCESSSUBJECTS@', ',')))

BEGIN
    DECLARE
     v_InstanceId NVARCHAR2(255);
  BEGIN
      FOR rec IN
      (SELECT COL_INSTANCEID FROM tbl_Ticket
      WHERE COL_ACTIVITY = :ActivityCode AND COL_OWNER = :PoolAccessSubject AND ROWNUM <= :PoolQty)
      LOOP
		  UPDATE tbl_Ticket 
		  SET COL_OWNER = '@TOKEN_USERACCESSSUBJECT@',
		  COL_MODIFIEDDATE = sysdate,
		  COL_MODIFIEDBY = '@TOKEN_USERACCESSSUBJECT@'
		  WHERE COL_INSTANCEID = rec.COL_INSTANCEID
		  AND COL_OWNER = :PoolAccessSubject;
		  
		  INSERT INTO tbl_Ticket_History (
        	COL_ACTIVITY,
        	COL_INSTANCEID,
        	COL_OWNER,
        	COL_CREATEDBY,
			COL_CREATEDDATE,
			COL_INSTANCETYPE
          )
          VALUES (
            :ActivityCode,
            rec.COL_INSTANCEID,
            '@TOKEN_USERACCESSSUBJECT@',
            '@TOKEN_USERACCESSSUBJECT@',
            sysdate,
            1
          );
		  
     END LOOP;
          :ErrorCode := 0;
          :ErrorMessage := '';
  EXCEPTION
          WHEN OTHERS THEN
  	      :ErrorCode := 100;
          :ErrorMessage := SUBSTR(SQLERRM, 1, 200);
  END;
END;