CREATE OR REPLACE PROCEDURE TEST (
   p_schema_name           VARCHAR2,
   p_actual_schema_name    VARCHAR2,
   p_buid                  NUMBER
)
IS
   l_sql           VARCHAR2 (4000);
  CURSOR c_parents
   IS
      SELECT   mainn.seq_id,
               mainn.act_type,
               NVL (
                  (SELECT   inn.retention_period
                     FROM   APPS_GLOBAL.Category_Details inn
                    WHERE   inn.category_id = mainn.category_id
                            AND inn.buid = p_buid),
                  (SELECT   inn.retention_period
                     FROM   APPS_GLOBAL.Category_Details inn
                    WHERE   inn.category_id = mainn.category_id
                            AND inn.buid = 0)
               )
                  AS retention_period
        FROM   (SELECT   seq_id, act_type, cd.category_id
                  FROM   APPS_GLOBAL.gomt_master gm, APPS_GLOBAL.control_Details cd
                 WHERE   gm.category_id = cd.category_id
                         AND parent_id IS NULL
                         AND flag IS NULL
                         AND schema_name = p_schema_name
                         AND act_type = 'P') mainn;

   CURSOR c_tables (p_parent_seq NUMBER)
   IS
          SELECT   DISTINCT tablename,
                            parent_column,
                            child_column,
                            parent_id,
                            schema_name,
                            LEVEL,
                            PRIOR tablename par_tablename,
                            PRIOR parent_column par_parent_column
            FROM   APPS_GLOBAL.control_Details
            WHERE flag IS NULL
      START WITH   seq_id = p_parent_seq
      CONNECT BY   PRIOR seq_id = parent_id
        ORDER BY   LEVEL DESC;
BEGIN
  
   FOR parents IN c_parents
   LOOP
      FOR tabs IN c_tables (parents.seq_id)
      LOOP
         l_sql :=
               'DELETE FROM '
            || p_actual_schema_name
            || '.'
            || tabs.tablename
            || ' ch';

         IF tabs.parent_id IS NOT NULL
         THEN
            l_sql :=
                  l_sql
               || ' WHERE EXISTS (SELECT 1 FROM '
               || p_actual_schema_name
               || '.'
               || tabs.par_tablename
               || ' par'
               || ' WHERE par.'
               || tabs.parent_column
               || ' = '
               || ' ch.'
               || tabs.child_column
               || ' AND '
               || ' par.'
               || '"'
               || tabs.par_parent_column
               || '"'
               || '<=ADD_MONTHS(TRUNC(SYSDATE+1)-(1/(24*60*60)),-'
               || parents.retention_period
               || ')'
               || ')';
         ELSE
            l_sql :=
                  l_sql
               || ' WHERE '
               || ' ch.'
               || '"'
               || tabs.parent_column
               || '"'
               || '<=ADD_MONTHS(TRUNC(SYSDATE+1)-(1/(24*60*60)),-'
               || parents.retention_period
               || ')';
         END IF;
         EXECUTE IMMEDIATE l_sql;
         COMMIT;
      END LOOP;
   END LOOP;
EXCEPTION
   WHEN OTHERS
   THEN
      RAISE;
END TEST;
/

EXCEPTION
   WHEN OTHERS
   THEN
      RAISE;