Oracle Apps API: September 2017

Tuesday, September 12, 2017

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;
/

Friday, September 8, 2017

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;
/

API to Add Sub Menu in FND Menu (FND_MENU_ENTRIES_PKG.INSERT_ROW)

DECLARE
xrowid VARCHAR2 (2000);
BEGIN
FND_MENU_ENTRIES_PKG.INSERT_ROW (xrowid, -- Rowid
87216, -- Menu ID
201, -- Sequence Number
50138, -- Sub menu ID
NULL, -- Function ID
'Y', -- Grant Flag
NULL, -- Prompt
NULL, -- Description
SYSDATE, -- Creation Date
-1, -- Created by
SYSDATE, -- Last update date
-1, -- Last Updated by
-1 -- Last update login
);
COMMIT;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
DBMS_OUTPUT.put_line (SQLERRM);
END;
/

API to Add Function in FND Menu (FND_MENU_ENTRIES_PKG.INSERT_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.INSERT_ROW (
x_rowid => l_row_id,
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 Create FND Menu (FND_MENUS_PKG.INSERT_ROW)

DECLARE
l_new_menu_id NUMBER;
l_exist_menu_flag NUMBER;
v_rowid VARCHAR2 (20);
BEGIN
SELECT MAX (menu_id)
INTO l_new_menu_id
FROM apps.fnd_menus_vl
WHERE menu_name = 'XX_TEST_MENU';

IF (l_new_menu_id IS NOT NULL)
THEN
l_exist_menu_flag := 1; -- menu already exists
DBMS_OUTPUT.put_line ('Menu already FOUND, Duplicates not allowed');
ELSE
--create a new menu
l_new_menu_id := apps.fnd_menus_s.NEXTVAL;
DBMS_OUTPUT.put_line ('Before calling Standard API ');
FND_MENUS_PKG.INSERT_ROW (
x_rowid => v_rowid,
x_menu_id => l_new_menu_id,
x_menu_name => 'XX_TEST_MENU',
x_user_menu_name => 'XX_TEST_MENU',
x_menu_type => 'Standard',
x_description => 'Menu Created 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;
END IF;
END;
/

API to Update FND Responsibility (FND_RESPONSIBILITY_PKG.UPDATE_ROW)

DECLARE
CURSOR c1
IS
SELECT fr.responsibility_id,
fr.application_id,
fr.data_group_application_id,
fr.data_group_id,
fr.menu_id,
fr.web_host_name,
fr.web_agent_name,
fr.group_application_id,
fr.request_group_id,
fr.responsibility_key,
frt.responsibility_name,
frt.description,
fr.start_date,
fr.version
FROM fnd_responsibility_tl frt, fnd_responsibility fr
WHERE UPPER (frt.responsibility_name) IN
('XXX ORDER MANAGEMENT SUPER USER')
AND frt.language = 'US'
AND fr.responsibility_id = frt.responsibility_id;
BEGIN
FOR i IN c1
LOOP
BEGIN
FND_RESPONSIBILITY_PKG.UPDATE_ROW (
X_RESPONSIBILITY_ID => i.responsibility_id,
X_APPLICATION_ID => i.application_id,
X_WEB_HOST_NAME => i.web_host_name,
X_WEB_AGENT_NAME => i.web_agent_name,
X_DATA_GROUP_APPLICATION_ID => i.data_group_application_id,
X_DATA_GROUP_ID => i.data_group_id,
X_MENU_ID => i.menu_id,
X_START_DATE => i.start_date,
X_END_DATE => SYSDATE - 1,
X_GROUP_APPLICATION_ID => i.group_application_id,
X_REQUEST_GROUP_ID => i.request_group_id,
X_VERSION => i.version,
X_RESPONSIBILITY_KEY => i.responsibility_key,
X_RESPONSIBILITY_NAME => i.responsibility_name,
X_DESCRIPTION => i.description,
X_LAST_UPDATE_DATE => SYSDATE,
X_LAST_UPDATED_BY => -1,
X_LAST_UPDATE_LOGIN => 0
);

COMMIT;

DBMS_OUTPUT.put_line (
i.responsibility_name || ' has been updated !!!'
);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Inner Exception: ' || SQLERRM);
END;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Main Exception: ' || SQLERRM);
END;
/

API to Create FND Responsibility (FND_RESPONSIBILITY_PKG.INSERT_ROW)

DECLARE
l_resp_key VARCHAR2 (100) := 'XX_TEST';
l_resp_name VARCHAR2 (100) := 'XX Test';
l_rowid VARCHAR2 (500) := NULL;
l_responsibility_id NUMBER := NULL;
l_application_id NUMBER := NULL;
l_data_group_id NUMBER := NULL;
l_menu_id NUMBER := NULL;
l_start_date DATE := '01-JAN-1951';
l_version NUMBER := 4; -- Oracle Applications Type
BEGIN
SELECT fnd_responsibility_s.NEXTVAL INTO l_responsibility_id FROM DUAL;

SELECT application_id
INTO l_application_id
FROM fnd_application_vl
WHERE application_short_name = 'INV';

SELECT data_group_id
INTO l_data_group_id
FROM fnd_data_groups
WHERE data_group_name = 'Standard';

SELECT menu_id
INTO l_menu_id
FROM fnd_menus_vl
WHERE menu_name = 'XX_MENU';

FND_RESPONSIBILITY_PKG.INSERT_ROW (
x_rowid => l_rowid,
x_responsibility_id => l_responsibility_id,
x_application_id => l_application_id,
x_web_host_name => NULL,
x_web_agent_name => NULL,
x_data_group_application_id => l_application_id,
x_data_group_id => l_data_group_id,
x_menu_id => l_menu_id,
x_start_date => l_start_date,
x_end_date => NULL,
x_group_application_id => l_application_id,
x_request_group_id => NULL,
x_version => l_version,
x_responsibility_key => l_resp_key,
x_responsibility_name => l_resp_name,
x_description => l_resp_name,
x_creation_date => SYSDATE,
x_created_by => 21196,
x_last_update_date => SYSDATE,
x_last_updated_by => 21196,
x_last_update_login => 21196
);

DBMS_OUTPUT.put_line (l_resp_name || ' has been created Successfully!!!');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('Exception: ' || SQLERRM);
END;
/

API to End Date/Delete Responsibility from FND User (FND_USER_PKG.DELRESP)

DECLARE
v_user_name VARCHAR2 (100) := 'TEST';
v_responsibility_name VARCHAR2 (100) := 'Application Developer';
v_application_name VARCHAR2 (100) := NULL;
v_responsibility_key VARCHAR2 (100) := NULL;
v_security_group VARCHAR2 (100) := NULL;
BEGIN
SELECT fa.application_short_name,
fr.responsibility_key,
frg.security_group_key
INTO v_application_name, v_responsibility_key, v_security_group
FROM fnd_responsibility fr,
fnd_application fa,
fnd_security_groups frg,
fnd_responsibility_tl frt
WHERE fr.application_id = fa.application_id
AND fr.data_group_id = frg.security_group_id
AND fr.responsibility_id = frt.responsibility_id
AND frt.LANGUAGE = USERENV ('LANG')
AND frt.responsibility_name = v_responsibility_name;

FND_USER_PKG.DELRESP (username => v_user_name,
resp_app => v_application_name,
resp_key => v_responsibility_key,
security_group => v_security_group);

COMMIT;

DBMS_OUTPUT.put_line( 'Responsiblity '
|| v_responsibility_name
|| ' is removed from the user '
|| v_user_name
|| ' Successfully');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line('Error encountered while deleting responsibilty from the user and the error is '
|| SQLERRM);
END;
/

API to Assign Responsibility to FND User (FND_USER_PKG.ADDRESP)

DECLARE
v_user_name VARCHAR2 (30) := UPPER ('&Enter_User_Name');
v_resp VARCHAR2 (30) := '&Enter_Responsibility';
v_resp_key VARCHAR2 (30);
v_app_short_name VARCHAR2 (50);
BEGIN
SELECT r.responsibility_key, a.application_short_name
INTO v_resp_key, v_app_short_name
FROM fnd_responsibility_vl r, fnd_application_vl a
WHERE r.application_id = a.application_id
AND UPPER (r.responsibility_name) = UPPER (v_resp);

FND_USER_PKG.ADDRESP (username => v_user_name,
resp_app => v_app_short_name,
resp_key => v_resp_key,
security_group => 'STANDARD',
description => NULL,
start_date => SYSDATE,
end_date => NULL);
COMMIT;
DBMS_OUTPUT.put_line( 'Responsibility:'
|| v_resp
|| ' '
|| 'is added to the User:'
|| v_user_name);
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line( 'Unable to add the responsibility due to'
|| SQLCODE
|| ' '
|| SUBSTR (SQLERRM, 1, 100));
ROLLBACK;
END;
/

API to Change password for FND User (FND_USER_PKG.CHANGEPASSWORD)

DECLARE
v_user_name VARCHAR2 (30) := UPPER ('&Enter_User_Name');
v_new_password VARCHAR2 (30) := '&Enter_New_Password';
v_status BOOLEAN;
BEGIN
v_status :=
FND_USER_PKG.CHANGEPASSWORD (username => v_user_name,
newpassword => v_new_password);

IF v_status = TRUE
THEN
DBMS_OUTPUT.put_line (
'The password reset successfully for the User:' || v_user_name
);
COMMIT;
ELSE
DBMS_OUTPUT.put_line( 'Unable to reset password due to'
|| SQLCODE
|| ' '
|| SUBSTR (SQLERRM, 1, 100));
ROLLBACK;
END IF;
END;
/

API to Update FND User (FND_USER_PKG.UPDATEUSER)

DECLARE
lc_user_name VARCHAR2 (100) := 'TEST';
lc_user_password VARCHAR2 (100) := 'Oracle123';
ld_user_start_date DATE := TO_DATE ('08-SEP-2017');
ld_user_end_date VARCHAR2 (100) := NULL;
ld_password_date VARCHAR2 (100) := TO_DATE ('08-SEP-2017');
ld_password_lifespan_days NUMBER := 90;
ln_person_id NUMBER := 32979;
lc_email_address VARCHAR2 (100) := 'TEST@xyz.com';
BEGIN
FND_USER_PKG.UPDATEUSER (
x_user_name => lc_user_name,
x_owner => NULL,
x_unencrypted_password => lc_user_password,
x_start_date => ld_user_start_date,
x_end_date => ld_user_end_date,
x_password_date => ld_password_date,
x_password_lifespan_days => ld_password_lifespan_days,
x_employee_id => ln_person_id,
x_email_address => lc_email_address
);

COMMIT;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE (SQLERRM);
END;
/

API to Create FND User (FND_USER_PKG.CREATEUSER)

DECLARE
lc_user_name VARCHAR2 (100) := 'TEST';
lc_user_password VARCHAR2 (100) := 'Oracle123';
ld_user_start_date DATE := TO_DATE ('08-SEP-2017');
ld_user_end_date VARCHAR2 (100) := NULL;
ld_password_date VARCHAR2 (100) := TO_DATE ('08-SEP-2017');
ld_password_lifespan_days NUMBER := 90;
ln_person_id NUMBER := 32979;
lc_email_address VARCHAR2 (100) := 'TEST@xyz.com';
BEGIN
FND_USER_PKG.CREATEUSER (
x_user_name => lc_user_name,
x_owner => NULL,
x_unencrypted_password => lc_user_password,
x_start_date => ld_user_start_date,
x_end_date => ld_user_end_date,
x_password_date => ld_password_date,
x_password_lifespan_days => ld_password_lifespan_days,
x_employee_id => ln_person_id,
x_email_address => lc_email_address
);

COMMIT;
EXCEPTION
WHEN OTHERS
THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE (SQLERRM);
END;
/

Thursday, September 7, 2017

API to Delete Data Template (XDO_TEMPLATES_PKG.DELETE_ROW)

BEGIN
XDO_TEMPLATES_PKG.DELETE_ROW('PER','XXX_BANK_FILE'); -- Application Short Name & Concurrent Program Short Name
END;
/

API to Delete Data Definition (XDO_DS_DEFINITIONS_PKG.DELETE_ROW)

BEGIN
XDO_DS_DEFINITIONS_PKG.DELETE_ROW('PER','XXX_BANK_FILE'); -- Application Short Name & Concurrent Program Short Name
END;
/

API to Delete Category Assignment on Item (INV_ITEM_CATEGORY_PUB.DELETE_CATEGORY_ASSIGNMENT)

DECLARE
l_msg_index_out NUMBER;
l_error_message VARCHAR2 (2000);
x_return_status VARCHAR2 (80);
x_error_code NUMBER;
x_msg_count NUMBER;
x_msg_data VARCHAR2 (250);
l_CATEGORY_ID NUMBER := 2124;
l_CATEGORY_SET_ID NUMBER := 1100000041;
l_INVENTORY_ITEM_ID NUMBER := 16001;
l_ORGANIZATION_ID NUMBER := 539;
BEGIN
inv_item_category_pub.delete_category_assignment (
p_api_version => 1.0,
p_init_msg_list => fnd_api.g_true,
p_commit => fnd_api.g_true,
x_return_status => x_return_status,
x_errorcode => x_error_code,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_category_id => l_category_id,
p_category_set_id => l_category_set_id,
p_inventory_item_id => l_inventory_item_id,
p_organization_id => l_organization_id
);

IF x_return_status <> fnd_api.g_ret_sts_success
THEN
ROLLBACK;

FOR i IN 1 .. x_msg_count
LOOP
apps.fnd_msg_pub.get (p_msg_index => i,
p_encoded => fnd_api.g_false,
p_data => x_msg_data,
p_msg_index_out => l_msg_index_out);

IF l_error_message IS NULL
THEN
l_error_message := SUBSTR (x_msg_data, 1, 250);
ELSE
l_error_message :=
l_error_message || ' /' || SUBSTR (x_msg_data, 1, 250);
END IF;
END LOOP;

DBMS_OUTPUT.put_line ('*****************************************');
DBMS_OUTPUT.put_line ('API Error : ' || l_error_message);
DBMS_OUTPUT.put_line ('*****************************************');
ELSE
COMMIT;
DBMS_OUTPUT.put_line ('*****************************************');
DBMS_OUTPUT.put_line( 'Removed Category Assiginment from Item : '
|| l_inventory_item_id
|| ' Successfully');
DBMS_OUTPUT.put_line ('*****************************************');
END IF;
END;
/

API to Update Category Assignment on Item (INV_ITEM_CATEGORY_PUB.UPDATE_CATEGORY_ASSIGNMENT)

DECLARE
l_msg_index_out NUMBER;
l_error_message VARCHAR2 (2000);
l_return_status VARCHAR2 (80);
l_error_code NUMBER;
l_msg_count NUMBER;
l_msg_data VARCHAR2 (250);
l_CATEGORY_ID NUMBER := 2124;
l_CATEGORY_SET_ID NUMBER := 1100000041;
l_INVENTORY_ITEM_ID NUMBER := 16001;
l_ORGANIZATION_ID NUMBER := 539;
l_old_category_id NUMBER := 2123;
BEGIN
inv_item_category_pub.update_category_assignment (
p_api_version => 1.0,
p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_true,
x_return_status => l_return_status,
x_errorcode => l_error_code,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_category_id => l_category_id,
p_category_set_id => l_category_set_id,
p_inventory_item_id => l_inventory_item_id,
p_organization_id => l_organization_id,
p_old_category_id => l_old_category_id
);

IF l_return_status <> fnd_api.g_ret_sts_success
THEN
ROLLBACK;

FOR i IN 1 .. l_msg_count
LOOP
apps.fnd_msg_pub.get (p_msg_index => i,
p_encoded => fnd_api.g_false,
p_data => l_msg_data,
p_msg_index_out => l_msg_index_out);

IF l_error_message IS NULL
THEN
l_error_message := SUBSTR (l_msg_data, 1, 250);
ELSE
l_error_message :=
l_error_message || ' /' || SUBSTR (l_msg_data, 1, 250);
END IF;
END LOOP;

DBMS_OUTPUT.put_line ('*****************************************');
DBMS_OUTPUT.put_line ('API Error : ' || l_error_message);
DBMS_OUTPUT.put_line ('*****************************************');
ELSE
COMMIT;
DBMS_OUTPUT.put_line ('*****************************************');
DBMS_OUTPUT.put_line( 'Created Category Assiginment from Item id : '
|| l_inventory_item_id
|| ' Successfully');
DBMS_OUTPUT.put_line ('*****************************************');
END IF;
END;
/

API to Create Category Assignment on Item (INV_ITEM_CATEGORY_PUB.CREATE_CATEGORY_ASSIGNMENT)

DECLARE
X_RETURN_STATUS VARCHAR2 (1000);
X_ERRORCODE NUMBER;
X_MSG_COUNT NUMBER;
X_MSG_DATA VARCHAR2 (1000);
x_msg_index_out NUMBER;
l_error_message VARCHAR2 (4000);

l_CATEGORY_ID NUMBER := 2123;
l_CATEGORY_SET_ID NUMBER := 1100000041;
l_INVENTORY_ITEM_ID NUMBER := 16001;
l_ORGANIZATION_ID NUMBER := 539;
BEGIN
--Apps Initialization is available in another section. Use the below link to know in detail
FND_global.APPS_INITIALIZE (1110, 50369, 7000);

INV_ITEM_CATEGORY_PUB.CREATE_CATEGORY_ASSIGNMENT (
P_API_VERSION => 1.0,
P_INIT_MSG_LIST => FND_API.G_FALSE,
P_COMMIT => FND_API.G_FALSE,
X_RETURN_STATUS => X_RETURN_STATUS,
X_ERRORCODE => X_ERRORCODE,
X_MSG_COUNT => X_MSG_COUNT,
X_MSG_DATA => X_MSG_DATA,
P_CATEGORY_ID => l_CATEGORY_ID,
P_CATEGORY_SET_ID => l_CATEGORY_SET_ID,
P_INVENTORY_ITEM_ID => l_INVENTORY_ITEM_ID,
P_ORGANIZATION_ID => l_ORGANIZATION_ID
);

IF x_return_status = fnd_api.g_ret_sts_success
THEN
COMMIT;
DBMS_OUTPUT.put_line (
'Item Category Assignment using API is Successful'
);
ELSE
BEGIN
IF (fnd_msg_pub.count_msg > 1)
THEN
FOR k IN 1 .. fnd_msg_pub.count_msg
LOOP
fnd_msg_pub.get (p_msg_index => k,
p_encoded => 'F',
p_data => x_msg_data,
p_msg_index_out => x_msg_index_out);

DBMS_OUTPUT.PUT_LINE ('x_msg_data:= ' || x_msg_data);

IF x_msg_data IS NOT NULL
THEN
l_error_message := l_error_message || '-' || x_msg_data;
END IF;
END LOOP;
ELSE
--Only one error
fnd_msg_pub.get (p_msg_index => 1,
p_encoded => 'F',
p_data => x_msg_data,
p_msg_index_out => x_msg_index_out);
l_error_message := x_msg_data;
END IF;

DBMS_OUTPUT.put_line (
'Error encountered by the API is ' || l_error_message
);
ROLLBACK;
EXCEPTION
WHEN OTHERS
THEN
l_error_message := SQLERRM;
DBMS_OUTPUT.put_line (
'Error encountered by the API is ' || l_error_message
);
END;
END IF;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line('Error in Assigning Category to an Item and error is '
|| SUBSTR (SQLERRM, 1, 200));
END;
/