API to Delete FND Application (FND_APPLICATION_PKG.DELETE_ROW)

BEGIN
   FND_APPLICATION_PKG.DELETE_ROW (X_APPLICATION_ID => 20003);
END;
/

API to Update FND Application (FND_APPLICATION_PKG.UPDATE_ROW)

BEGIN
   FND_APPLICATION_PKG.UPDATE_ROW (X_APPLICATION_ID           => 20003,
                                   X_APPLICATION_SHORT_NAME   => 'TEST1',
                                   X_BASEPATH                 => 'TST_TOP',
                                   X_APPLICATION_NAME         => 'Test1',
                                   X_DESCRIPTION              => 'Test123',
                                   X_LAST_UPDATE_DATE         => SYSDATE,
                                   X_LAST_UPDATED_BY          => -1,
                                   X_LAST_UPDATE_LOGIN        => -1,
                                   X_PRODUCT_CODE             => 'TEST');
END;
/

API to Create FND Application (FND_APPLICATION_PKG.LOAD_ROW)

BEGIN
   FND_APPLICATION_PKG.LOAD_ROW (X_APPLICATION_SHORT_NAME   => 'TEST1',
                                 X_OWNER                    => '-1',
                                 X_BASEPATH                 => 'TST_TOP',
                                 X_APPLICATION_NAME         => 'Test1',
                                 X_DESCRIPTION              => 'Test1',
                                 X_PRODUCT_CODE             => 'TEST');
END;
/

API to Delete FND Message (FND_NEW_MESSAGES_PKG.DELETE_ROW)

BEGIN
   FND_NEW_MESSAGES_PKG.DELETE_ROW (X_APPLICATION_ID   => 0,
                                    X_LANGUAGE_CODE    => 'US',
                                    X_MESSAGE_NAME     => 'TEST1');
END;
/

API to Create FND Message (FND_NEW_MESSAGES_PKG.LOAD_ROW)

BEGIN
   FND_NEW_MESSAGES_PKG.LOAD_ROW (x_application_id     => 0,
                                  x_message_name       => 'TEST1',
                                  x_message_number     => 0,
                                  x_message_text       => 'Test1',
                                  x_description        => 'Test1',
                                  x_type               => NULL,
                                  x_max_length         => NULL,
                                  x_category           => NULL,
                                  x_severity           => NULL,
                                  x_fnd_log_severity   => NULL,
                                  x_owner              => -1,
                                  x_custom_mode        => 'FORCE',
                                  x_last_update_date   => NULL);
END;
/

API to Create Table Value Set (FND_FLEX_VAL_API.CREATE_VALUESET_TABLE)

BEGIN
   FND_FLEX_VAL_API.SET_SESSION_MODE ('customer_data');          /*Mandatory*/
   FND_FLEX_VAL_API.CREATE_VALUESET_TABLE (
      value_set_name            => 'XX_VALUESET',
      description               => 'createdfrombackend',
      security_available        => 'N',
      enable_longlist           => 'N',
      format_type               => 'Char',
      maximum_size              => 20,
      PRECISION                 => NULL,
      numbers_only              => 'N',
      uppercase_only            => 'N',
      right_justify_zero_fill   => 'N',
      min_value                 => NULL,
      max_value                 => NULL,
      table_application         => 'Application Object Library',
      table_appl_short_name     => 'AOL',
      table_name                => 'FND_LOOKUP_VALUES FND',
      allow_parent_values       => 'N',
      value_column_name         => 'FND.LOOKUP_CODE',
      value_column_type         => 'Char',
      value_column_size         => 40,
      meaning_column_name       => NULL,
      meaning_column_type       => NULL,
      meaning_column_size       => 40,
      id_column_name            => 'FND.MEANING',
      id_column_type            => 'Char',
      id_column_size            => 40,
      where_order_by            => 'where lookup_type=''XXTEST_LOOKUP''',
      additional_columns        => NULL
   );
   COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (SQLERRM);
END;
/

API to Create Dependent Value Set (FND_FLEX_VAL_API.CREATE_VALUESET_DEPENDENT)

DECLARE
   value_set_name            VARCHAR2 (200);
   parent_value_set_name     VARCHAR2 (200);
   description               VARCHAR2 (200);
   security_available        VARCHAR2 (200);
   enable_longlist           VARCHAR2 (200);
   format_type               VARCHAR2 (200);
   maximum_size              NUMBER;
   PRECISION                 NUMBER;
   numbers_only              VARCHAR2 (200);
   uppercase_only            VARCHAR2 (200);
   right_justify_zero_fill   VARCHAR2 (200);
   min_value                 VARCHAR2 (200);
   max_value                 VARCHAR2 (200);
   v_session_mode            VARCHAR2 (20) := 'customer_data';
   x                         VARCHAR2 (200);
   v_msg                     VARCHAR2 (2000);
BEGIN
   FND_FLEX_VAL_API.SET_SESSION_MODE (v_session_mode);

   value_set_name := 'CUST_DEPENDENT_VAL_SET';
   parent_value_set_name := 'CUST_VAL_SET_DEMO';
   description := 'Dependent Value Set';
   security_available := 'N';
   enable_longlist := 'N';
   format_type := 'C';
   maximum_size := 30;
   PRECISION := 1;
   numbers_only := 'N';
   uppercase_only := 'N';
   right_justify_zero_fill := 'N';
   min_value := NULL;
   max_value := NULL;

   IF (FND_FLEX_VAL_API.VALUESET_EXISTS (value_set_name))
   THEN
      DBMS_OUTPUT.put_line ('Value set exists.. Deleting it...');
      FND_FLEX_VAL_API.DELETE_VALUESET (value_set_name);
   END IF;

   IF NOT (FND_FLEX_VAL_API.VALUESET_EXISTS (value_set_name))
   THEN
      DBMS_OUTPUT.put_line ('Value set doesn''t exists.. Creating it...');
      FND_FLEX_VAL_API.CREATE_VALUESET_DEPENDENT (value_set_name,
                                                  description,
                                                  security_available,
                                                  enable_longlist,
                                                  format_type,
                                                  maximum_size,
                                                  PRECISION,
                                                  numbers_only,
                                                  uppercase_only,
                                                  right_justify_zero_fill,
                                                  min_value,
                                                  max_value,
                                                  parent_value_set_name,
                                                  'DEFAULT',
                                                  'Default');
      COMMIT;

      IF (FND_FLEX_VAL_API.VALUESET_EXISTS (value_set_name))
      THEN
         DBMS_OUTPUT.put_line (
            'Value set:' || value_set_name || ' got created'
         );
      END IF;
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      v_msg := fnd_flex_val_api.MESSAGE;
      DBMS_OUTPUT.put_line (v_msg);
      DBMS_OUTPUT.put_line (SQLERRM);
END;

API to Create Independent Value Set (FND_FLEX_VAL_API.CREATE_VALUESET_INDEPENDENT)

DECLARE
   value_set_name            VARCHAR2 (200);
   description               VARCHAR2 (200);
   security_available        VARCHAR2 (200);
   enable_longlist           VARCHAR2 (200);
   format_type               VARCHAR2 (200);
   maximum_size              NUMBER;
   PRECISION                 NUMBER;
   numbers_only              VARCHAR2 (200);
   uppercase_only            VARCHAR2 (200);
   right_justify_zero_fill   VARCHAR2 (200);
   min_value                 VARCHAR2 (200);
   max_value                 VARCHAR2 (200);
   v_session_mode            VARCHAR2 (20) := 'customer_data';
   x                         VARCHAR2 (200);
   v_msg                     VARCHAR2 (2000);
BEGIN
   FND_FLEX_VAL_API.SET_SESSION_MODE (v_session_mode);
  
   value_set_name := 'CUST_VAL_SET_DEMO';
   description := 'Custom Valueset';
   security_available := 'N';
   enable_longlist := 'N';
   format_type := 'C';
   maximum_size := 30;
   PRECISION := 1;
   numbers_only := 'N';
   uppercase_only := 'N';
   right_justify_zero_fill := 'N';
   min_value := '1';
   max_value := '30';
   FND_FLEX_VAL_API.CREATE_VALUESET_INDEPENDENT (value_set_name,
                                                 description,
                                                 security_available,
                                                 enable_longlist,
                                                 format_type,
                                                 maximum_size,
                                                 PRECISION,
                                                 numbers_only,
                                                 uppercase_only,
                                                 right_justify_zero_fill,
                                                 min_value,
                                                 max_value);
   COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN
      ROLLBACK;
      v_msg := fnd_flex_val_api.MESSAGE;
      DBMS_OUTPUT.put_line (v_msg);
      DBMS_OUTPUT.put_line (SQLERRM);
END;

API to get Accounting Key Flexfield Concatenated Segment Description (GL_FLEXFIELDS_PKG.GET_CONCAT_DESCRIPTION)

SELECT gcc.concatenated_segments,
       GL_FLEXFIELDS_PKG.GET_CONCAT_DESCRIPTION( gcc.chart_of_accounts_id,
                                                 gcc.code_combination_id
                                               ) acc_description
FROM gl_code_combinations_kfv gcc;

API to Delete FND Form (FND_FORM_PKG.DELETE_ROW)

DECLARE
   l_form_id   NUMBER := 1001;
BEGIN
   FND_FORM_PKG.DELETE_ROW (X_APPLICATION_ID       => 7000,
                            X_FORM_ID              => l_form_id);
   COMMIT;
END;
/

API to Update FND Form (FND_FORM_PKG.UPDATE_ROW)

DECLARE
   l_form_id   NUMBER := 1001;
BEGIN
   FND_FORM_PKG.UPDATE_ROW (X_APPLICATION_ID       => 7000,
                            X_FORM_ID              => l_form_id,
                            X_FORM_NAME            => 'TEST',
                            X_AUDIT_ENABLED_FLAG   => 'N',
                            X_USER_FORM_NAME       => 'Test',
                            X_DESCRIPTION          => 'Test',
                            X_LAST_UPDATE_DATE     => SYSDATE,
                            X_LAST_UPDATED_BY      => -1,
                            X_LAST_UPDATE_LOGIN    => 0);
   COMMIT;
END;
/

API to Create FND Form (FND_FORM_PKG.INSERT_ROW)

DECLARE
   l_row_id    VARCHAR2 (100);
   l_form_id   NUMBER;
BEGIN
   FND_FORM_PKG.INSERT_ROW (X_ROWID                => l_row_id,
                            X_APPLICATION_ID       => 7000,
                            X_FORM_ID              => l_form_id,
                            X_FORM_NAME            => 'TEST',
                            X_AUDIT_ENABLED_FLAG   => 'N',
                            X_USER_FORM_NAME       => 'Test',
                            X_DESCRIPTION          => 'Test',
                            X_CREATION_DATE        => SYSDATE,
                            X_CREATED_BY           => -1,
                            X_LAST_UPDATE_DATE     => SYSDATE,
                            X_LAST_UPDATED_BY      => -1,
                            X_LAST_UPDATE_LOGIN    => 0);
   COMMIT;
END;
/

API to Delete Form Function (FND_FORM_FUNCTIONS_PKG.DELETE_ROW)

DECLARE
   l_function_id   NUMBER := 1001;
BEGIN
   FND_FORM_FUNCTIONS_PKG.DELETE_ROW (X_FUNCTION_ID => l_function_id);

   COMMIT;
END;
/

API to Update Form Function (FND_FORM_FUNCTIONS_PKG.UPDATE_ROW)

DECLARE
   l_new_menu_id       NUMBER;
   l_last_menu_id      NUMBER;
   l_row_id            VARCHAR2 (100);
   l_descr             VARCHAR2 (400);
   l_flag_exist_menu   NUMBER := 0;
   x_function_id       NUMBER;
   x_row_id            ROWID;
   x_form_id           NUMBER;
   lc_status           VARCHAR2 (1);

   CURSOR C1
   IS
      SELECT   * FROM FND_FORM_FUNCTIONS;
BEGIN
   FOR CREC IN C1
   LOOP
      lc_status := 'Y';

      IF CREC.form_id IS NOT NULL
      THEN
         BEGIN
            SELECT   form_id
              INTO   x_form_id
              FROM   fnd_form
             WHERE   form_id = CREC.form_id;
         EXCEPTION
            WHEN OTHERS
            THEN
               DBMS_OUTPUT.put_line ('failed to get x_form_id');
               lc_status := 'E';
         END;
      END IF;

      IF lc_status = 'Y'
      THEN
         FND_FORM_FUNCTIONS_PKG.UPDATE_ROW (
            X_FUNCTION_ID              => CREC.FUNCTION_ID,
            X_WEB_HOST_NAME            => NULL,
            X_WEB_AGENT_NAME           => NULL,
            X_WEB_HTML_CALL            => CREC.WEB_HTML_CALL,
            X_WEB_ENCRYPT_PARAMETERS   => 'N',
            X_WEB_SECURED              => 'N',
            X_WEB_ICON                 => NULL,
            X_OBJECT_ID                => NULL,
            X_REGION_APPLICATION_ID    => NULL,
            X_REGION_CODE              => NULL,
            X_FUNCTION_NAME            => CREC.FUNCTION_NAME,
            -- find unique function name using query
            X_APPLICATION_ID           => CREC.APPLICATION_ID,
            -- find application id using the query for appliaion names and pass 'FND'
            X_FORM_ID                  => x_form_id, -- find from id using formname 'XXCO'
            X_PARAMETERS               => CREC.PARAMETERS,
            X_TYPE                     => CREC.TYPE,
            X_USER_FUNCTION_NAME       => CREC.USER_FUNCTION_NAME,
            X_DESCRIPTION              => CREC.DESCRIPTION,
            X_CREATION_DATE            => SYSDATE,
            X_CREATED_BY               => -1,
            X_LAST_UPDATE_DATE         => SYSDATE,
            X_LAST_UPDATED_BY          => -1,
            X_LAST_UPDATE_LOGIN        => 0
         );

         DBMS_OUTPUT.put_line ('FUNCTION_NAME ' || CREC.FUNCTION_NAME);
      END IF;
   END LOOP;

   COMMIT;
END;
/

API to Create Form Function (FND_FORM_FUNCTIONS_PKG.INSERT_ROW)

DECLARE
   l_new_menu_id       NUMBER;
   l_last_menu_id      NUMBER;
   l_row_id            VARCHAR2 (100);
   l_descr             VARCHAR2 (400);
   l_flag_exist_menu   NUMBER := 0;
   x_function_id       NUMBER;
   x_row_id            ROWID;
   x_form_id           NUMBER;
   lc_status           VARCHAR2 (1);

   CURSOR C1
   IS
      SELECT   *
        FROM   XX_FND_FUNCTIONS
       WHERE   FUNCTION_NAME NOT IN
                     (SELECT   FUNCTION_NAME FROM FND_FORM_FUNCTIONS);
BEGIN
   FOR CREC IN C1
   LOOP
      lc_status := 'Y';

      BEGIN
         SELECT   fnd_form_functions_s.NEXTVAL INTO x_function_id FROM sys.DUAL;
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line ('failed to fnd_form_functions_s');
            lc_status := 'E';
      END;

      IF CREC.form_id IS NOT NULL
      THEN
         BEGIN
            SELECT   form_id
              INTO   x_form_id
              FROM   fnd_form
             WHERE   form_id = CREC.form_id;
         EXCEPTION
            WHEN OTHERS
            THEN
               DBMS_OUTPUT.put_line ('failed to get x_form_id');
               lc_status := 'E';
         END;
      END IF;

      IF lc_status = 'Y'
      THEN
         FND_FORM_FUNCTIONS_PKG.INSERT_ROW (
            X_ROWID                    => x_row_id,
            X_FUNCTION_ID              => X_FUNCTION_ID,
            X_WEB_HOST_NAME            => NULL,
            X_WEB_AGENT_NAME           => NULL,
            X_WEB_HTML_CALL            => CREC.WEB_HTML_CALL,
            X_WEB_ENCRYPT_PARAMETERS   => 'N',
            X_WEB_SECURED              => 'N',
            X_WEB_ICON                 => NULL,
            X_OBJECT_ID                => NULL,
            X_REGION_APPLICATION_ID    => NULL,
            X_REGION_CODE              => NULL,
            X_FUNCTION_NAME            => CREC.FUNCTION_NAME,
            -- find unique function name using query
            X_APPLICATION_ID           => CREC.APPLICATION_ID,
            -- find application id using the query for appliaion names and pass 'FND'
            X_FORM_ID                  => x_form_id, -- find from id using formname 'XXCO'
            X_PARAMETERS               => CREC.PARAMETERS,
            X_TYPE                     => CREC.TYPE,
            X_USER_FUNCTION_NAME       => CREC.USER_FUNCTION_NAME,
            X_DESCRIPTION              => CREC.DESCRIPTION,
            X_CREATION_DATE            => SYSDATE,
            X_CREATED_BY               => -1,
            X_LAST_UPDATE_DATE         => SYSDATE,
            X_LAST_UPDATED_BY          => -1,
            X_LAST_UPDATE_LOGIN        => 0
         );

         DBMS_OUTPUT.put_line ('FUNCTION_NAME ' || CREC.FUNCTION_NAME);
      END IF;
   END LOOP;

   COMMIT;
END;
/

API to Delete Function/Sub Menu in FND Menu (FND_MENU_ENTRIES_PKG.DELETE_ROW)

DECLARE
   l_seq   NUMBER := 1;
BEGIN
   BEGIN
      FND_MENU_ENTRIES_PKG.DELETE_ROW (x_menu_id          => 1026906,
                                       -- Menu ID
                                       x_entry_sequence   => l_seq -- Sequence Number
                                                                  );
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line (SQLERRM || ' Is the error');
   END;
END;
/

API to Update Sub Menu in FND Menu (FND_MENU_ENTRIES_PKG.UPDATE_ROW)

DECLARE
   l_menu_enttry_seq   NUMBER;
   l_seq               NUMBER;
BEGIN
   SELECT   NVL (MAX (entry_sequence), 1)
     INTO   l_seq
     FROM   apps.fnd_menu_entries
    WHERE   menu_id = 1026906;
   
   BEGIN
      FND_MENU_ENTRIES_PKG.UPDATE_ROW (
         x_menu_id             => 1026906,
         -- Menu ID
         x_entry_sequence      => l_seq,
         -- Sequence Number
         x_sub_menu_id         => 12345,
         -- Sub menu ID
         x_function_id         => NULL,
         -- Function ID
         x_grant_flag          => 'Y',                           -- Grant Flag
         x_prompt              => 'Sub Menu for Testing',            -- Prompt
         x_description         => 'Sub Menu for Testing',
         -- Description                                                 x_creation_date          => SYSDATE,
         -- Creation Date
         x_created_by          => fnd_global.user_id,
         -- Created by
         x_last_update_date    => SYSDATE,
         x_last_updated_by     => fnd_global.user_id,
         -- Last Updated by
         x_last_update_login   => fnd_global.login_id
      -- Last update login
      );
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line (SQLERRM || ' Is the error');
   END;
END;
/

API to Update Function in FND Menu (FND_MENU_ENTRIES_PKG.UPDATE_ROW)

DECLARE
   l_menu_enttry_seq   NUMBER;
   l_row_id            VARCHAR2 (20);
   l_function_id       NUMBER;
   l_seq               NUMBER;
BEGIN
   SELECT   NVL (MAX (entry_sequence), 1)
     INTO   l_seq
     FROM   apps.fnd_menu_entries
    WHERE   menu_id = 1026906;

   SELECT   function_id
     INTO   l_function_id
     FROM   apps.fnd_form_functions
    WHERE   function_name = 'XXEMP';

   BEGIN
      FND_MENU_ENTRIES_PKG.UPDATE_ROW (
         x_menu_id             => 1026906,
         -- Menu ID
         x_entry_sequence      => l_seq,
         -- Sequence Number
         x_sub_menu_id         => NULL,
         -- Sub menu ID
         x_function_id         => l_function_id,
         -- Function ID
         x_grant_flag          => 'Y',                           -- Grant Flag
         x_prompt              => 'Function for Testing',            -- Prompt
         x_description         => 'Function for Testing',
         -- Description                                                 x_creation_date          => SYSDATE,
         -- Creation Date
         x_created_by          => fnd_global.user_id,
         -- Created by
         x_last_update_date    => SYSDATE,
         x_last_updated_by     => fnd_global.user_id,
         -- Last Updated by
         x_last_update_login   => fnd_global.login_id
      -- Last update login
      );
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line (SQLERRM || ' Is the error');
   END;
END;
/

API to Delete FND Menu (FND_MENUS_PKG.DELETE_ROW)

DECLARE
   xrowid   VARCHAR2 (2000);
BEGIN
   FND_MENUS_PKG.DELETE_ROW (87216-- Menu ID
   );
   COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN
      ROLLBACK;
      DBMS_OUTPUT.put_line (SQLERRM);
END;
/

API to Update FND Menu (FND_MENUS_PKG.UPDATE_ROW)

DECLARE
   xrowid   VARCHAR2 (2000);
BEGIN
   FND_MENUS_PKG.UPDATE_ROW (87216,                                 -- Menu ID
                             'XX_TEST_MENU',                      -- Menu Name
                             'XX Test Menu',                 -- User Menu Name
                             x_menu_type           => 'Standard',
                             x_description         => 'Menu Updated from Backend',
                             x_creation_date       => SYSDATE,
                             x_created_by          => fnd_global.user_id,
                             x_last_update_date    => SYSDATE,
                             x_last_updated_by     => fnd_global.user_id, --userid
                             x_last_update_login   => fnd_global.login_id);
   COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN
      ROLLBACK;
      DBMS_OUTPUT.put_line (SQLERRM);
END;
/