Application Root objects conversion for new objects

This is script is used in converting the new DL objects during patch-set based on the deployment model of the application during installation.

Syntax

/*    Script    for    Shared    Object    Conversion    for    patch-set    */
SET VERIFY ON
SET HEAD ON
SET FEEDBACK 1
SET ARRAY 1
SET LINESIZE 10000
SET PAGESIZE 50000
SET LONG 10000
SET ECHO ON
SET TRIMSPOOL ON
SET COLSEP ';'
SET SERVEROUT OFF
clear screen
SPOOL ON
SET SQLBLANKLINES ON
SET SERVEROUTPUT ON
SET ERRORLOGGING ON
SET ECHO ON
prompt Welcome to Upgrade New object Conversion
SPOOL    "&SPOOL_PATH"

DECLARE
    l_count          NUMBER;
    l_app_deployment VARCHAR2(30);
BEGIN
    SELECT count(*)
	  INTO l_count
	  FROM user_objects
	 WHERE sharing = 'NONE' --to get the new set of DL approot objects if any
	   AND object_name IN
		   (SELECT DISTINCT a.object_name
			  FROM cstm_approot_objects a
			 WHERE sharing = 'DL'
			   AND UPPER(object_type) = 'TABLE'
			   AND EXISTS (SELECT 1
					  FROM user_objects b
					 WHERE b.object_name = a.object_name)
			   AND EXISTS (SELECT 1
					  FROM cstm_approot_functions_menu c
					 WHERE c.function_id = a.function_id
					   AND c.modifiable IN ('Y', 'S')));
    dbms_output.put_line('l_count:    '    ||    l_count);
	
    IF l_count > 0 THEN
        dbms_output.put_line('New DL objects are available');
        SELECT param_val
		  INTO l_app_deployment
		  FROM cstb_param
		 WHERE param_name = 'MULTI_TENANT_DEPLOYMENT_MODEL';
        dbms_output.put_line('l_app_deployment: '||l_app_deployment);
        
        IF l_app_deployment IS NOT NULL AND l_app_deployment = 'SAUA' THEN
            UPDATE smtb_menu menu
               SET menu.approot_flg = 'Y'
             WHERE menu.function_id IN
                   (SELECT function_id
                      FROM cstm_approot_functions_menu
                     WHERE modifiable = 'S'
                    UNION
                    SELECT summary_fn_id
                      FROM cstm_approot_functions_menu
                     WHERE modifiable = 'S'
                       AND summary_fn_id IS NOT NULL) --SMS function id 'S'
               AND menu.approot_flg <> 'Y'; --excluding the already modified approot function ids in menu.
        ELSIF l_app_deployment IS NOT NULL AND l_app_deployment = 'SASDD' THEN
            UPDATE smtb_menu menu
               SET menu.approot_flg = 'Y'
             WHERE menu.function_id IN
                   (SELECT function_id
                      FROM cstm_approot_functions_menu
                    UNION
                    SELECT summary_fn_id
                      FROM cstm_approot_functions_menu
                     WHERE summary_fn_id IS NOT NULL)
               AND menu.approot_flg <> 'Y'; --excluding the already modified approot function ids in menu.
        ELSIF l_app_deployment IS NOT NULL AND l_app_deployment = 'SASDC' THEN
		/*Assumption new table cstm_approot_menu_custom_movedtopdb will be available
            and is populated with the function ids which are moved to PDB as part of custom deployment
            It has 2 columns FUNCTION_ID and SUMMARY_FN_ID*/
      
            UPDATE smtb_menu menu
               SET menu.approot_flg = 'Y'
             WHERE menu.function_id IN
                   (SELECT function_id
                      FROM cstm_approot_functions_menu
                    UNION
                    SELECT summary_fn_id
                      FROM cstm_approot_functions_menu
                     WHERE summary_fn_id IS NOT NULL)
               AND menu.function_id NOT IN --excluding the function ids moved to PDB already.
                   (SELECT function_id
                      FROM cstm_approot_menu_movedtopdb
                    UNION
                    SELECT summary_fn_id
                      FROM cstm_approot_menu_movedtopdb
                     WHERE summary_fn_id IS NOT NULL)
               AND menu.approot_flg <> 'Y'; --excluding the already modified approot function ids in menu.
        END IF;
        
        BEGIN
			FOR I IN (SELECT 'BEGIN ' || chr(10) ||
						   'DBMS_PDB.SET_DATA_LINKED(''&P_APPROOT_USER''' || ',''' ||
						   Object_Name || ''',' || Namespace || '); ' || chr(10) ||
						   'EXCEPTION ' || chr(10) ||
						   'WHEN OTHERS THEN ' || chr(10) ||
						   'DBMS_OUTPUT.PUT_LINE(''ERROR ->''|| SQLERRM); ' ||
						   chr(10) || 'END;' sqlobject
					  FROM user_objects
					 WHERE sharing = 'NONE' --to get the new set of DL approot objects if any
					   AND object_name IN
						   (SELECT DISTINCT a.object_name
							  FROM cstm_approot_objects a
							 WHERE sharing = 'DL'
							   AND UPPER(object_type) = 'TABLE'
							   AND EXISTS (SELECT 1
									  FROM user_objects b
									 WHERE b.object_name = a.object_name)
							   AND EXISTS
							 (SELECT 1
									  FROM cstm_approot_functions_menu c
									 WHERE c.function_id = a.function_id
									   AND c.modifiable IN ('Y', 'S')))) LOOP
				DBMS_OUTPUT.PUT_LINE(chr(10));
				EXECUTE IMMEDIATE I.sqlobject;
				DBMS_OUTPUT.PUT_LINE(I.sqlobject);
			END LOOP;
		EXCEPTION
			WHEN OTHERS THEN
				DBMS_OUTPUT.PUT_LINE('Error --->' || SQLERRM);
		END;
    ELSE
        dbms_output.put_line('No new DL objects available');
    END IF;
EXCEPTION    
    WHEN OTHERS THEN
        dbms_output.put_line('Error --->'||SQLERRM);
END;
/

SET ERRORLOGGING OFF
SPOOL OFF