query to find responsibility name from user

SELECT fu.user_name,
frt.responsibility_name,
furg.start_date,
furg.end_date,
fr.responsibility_key,
fa.application_short_name "Application Short Name"
FROM apps.fnd_user_resp_groups_direct furg,
apps.fnd_user fu,
apps.fnd_responsibility_tl frt,
apps.fnd_responsibility fr,
apps.fnd_application_tl fat,
apps.fnd_application fa
WHERE furg.user_id = fu.user_id
AND furg.responsibility_id = frt.responsibility_id
AND fr.responsibility_id = frt.responsibility_id
AND fa.application_id = fat.application_id
AND fr.application_id = fat.application_id
AND frt.language = USERENV('LANG')
AND UPPER(fu.user_name) = UPPER('SSHARMA')
ORDER BY furg.start_date;

Query to find concurrent programs using a value set in its parameter list

SELECT
fcp.user_concurrent_program_name,
fat.application_name,
fdfc.column_seq_num,
fdfc.form_left_prompt parameter_name,
fdfc.enabled_flag
FROM
apps.fnd_descr_flex_col_usage_vl fdfc,
apps.fnd_flex_value_sets ffvs,
apps.fnd_concurrent_programs_vl fcp,
apps.fnd_application_tl fat
WHERE
1 = 1
AND ffvs.flex_value_set_id = fdfc.flex_value_set_id
AND fdfc.descriptive_flexfield_name = '$SRS$.' || fcp.concurrent_program_name
AND fcp.application_id = fat.application_id
AND ffvs.flex_value_set_name = 'JEIT_CALENDAR_MONTH' --value set name

how to compile sql, pkb, ldt and wft file from shell script

#!/bin/bash

#* $Header:FACutomizationScript.sh  99.99 MM/DD/YYYY HH:MI:SS  *

########################################################################################################################

#

# Shell Script Name : XXX_CUSTOM.sh

#

# Purpose           : XXX_CUSTOM Custom Program Migration script

# Change History    : 04-Apr-2022 

#  

########################################################################################################################

APPS_USER='apps'

read -s -p "Enter Apps Password: " APPS_PASSWD

read -s -p "Enter SID: "  DB_SID

#APPS_PASSWD

#DB_SID=

date

who am i

cd $XXX_TOP/UPGRADE

echo "Compiling plsql" 

echo "Compiling view XXX_OE_PKG_SPEC.sql"

echo " "

sqlplus -s $APPS_USER/$APPS_PASSWD@$DB_SID @$XXX_TOP/R12UPGRADE/XXX_OE_PKG_SPEC.sql

echo "Compiling XXX_OE_PKG_BODY.sql"

echo " "

sqlplus -s $APPS_USER/$APPS_PASSWD@$DB_SID @$XXX_TOP/R12UPGRADE/XXX_OE_PKG_BODY.sql

echo "Executing file XXX_POXRQERQ.ldt"

echo " "

FNDLOAD $APPS_USER/$APPS_PASSWD 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct XXX_POXRQERQ.ldt

echo "Compiling XXX_Lookup_Code.sql"

echo " "

sqlplus -s $APPS_USER/$APPS_PASSWD@$DB_SID @$XXX_TOP/R12UPGRADE/XXX_Lookup_Code.sql

echo "Compiling XXX_ACCT_GEN_PKG.pks"

echo " "

sqlplus -s $APPS_USER/$APPS_PASSWD@$DB_SID @$OVT_TOP/R12UPGRADE/XXX_ACCT_GEN_PKG.pks

echo "Compiling XXX_ACCT_GEN_PKG.pkb"

echo " "

sqlplus -s $APPS_USER/$APPS_PASSWD@$DB_SID @$OVT_TOP/R12UPGRADE/XXX_ACCT_GEN_PKG.pkb

echo "copy XXXRWMO.rdf"

echo " "

cp $XXX_TOP/R12UPGRADE/XXXRWMO.rdf $XXX_TOP/reports/US

echo "copy XXXTOPKL.rdf"

echo " "

cp $XXX_TOP/R12UPGRADE/XXXTOPKL.rdf $XXX_TOP/reports/US

echo "Upload XXX_POWFACCT_MODIFIED.wft"

echo " "

WFLOAD $APPS_USER/$APPS_PASSWD 0 Y FORCE XXX_POWFACCT_MODIFIED.wft

echo "Upload XXX_CRP_V3.wft"

echo " "

WFLOAD $APPS_USER/$APPS_PASSWD 0 Y FORCE XXX_CRP_V3.wft

date

who am i

echo "End of script"

# --------------------------------------------------------------------

#  End of Script

# --------------------------------------------------------------------

Convert XML to rows and columns in pl/sql

 with t as

(

select

'<responsibility>

<responsibility_data><responsibility_name>@Advanced Supply Chain Planner</responsibility_name><end_date>2008-07-11</end_date></responsibility_data>

<responsibility_data><responsibility_name>Advanced Planning Scenario Manager</responsibility_name><end_date>2017-02-27</end_date></responsibility_data>

</responsibility>' myxml from dual

)

SELECT x.*

FROM t,

XMLTABLE ('/responsibility/responsibility_data'

PASSING XMLPARSE (DOCUMENT t.myxml)

COLUMNS "responsibility_name" VARCHAR2 (2000) PATH 'responsibility_name',

"end_date" DATE PATH 'end_date') x;

How To Run the .sh file shell script In Linux

 sh file_name.sh

Script to update function and sub menu in FND Menu

 DECLARE
    l_menu_id     NUMBER;
    l_row_id      VARCHAR2(20);
    l_function_id NUMBER;
    l_seq         NUMBER;
    l_prompt      fnd_menu_entries_vl.prompt%TYPE;
    l_description fnd_menu_entries_vl.description%TYPE;
    l_fnc_cnt     NUMBER := 0;
    l_sm_cnt      NUMBER := 0;
l_smy_cnt     NUMBER := 0;

    CURSOR c1 IS
    SELECT
    fm.user_menu_name||'-'|| fff.user_function_name menu_function_name,
        fm.menu_id,
        fme.function_id,
        fme.entry_sequence,
        fme.prompt,
        fme.description
    FROM
        fnd_menus_vl          fm,
        fnd_menu_entries_vl   fme,
        fnd_form_functions_vl fff
    WHERE
        1 = 1
        AND fm.menu_id = fme.menu_id
        AND fme.function_id = fff.function_id
        AND fm.user_menu_name||'-'|| fff.user_function_name IN (

'General Pref Menu-Manage Proxies Page',
'General Pref Menu-User Management - My Application Access'
);

    CURSOR c2 IS
    SELECT
    fm.user_menu_name||'-'|| fm1.user_menu_name menu_submenu_name,
        fm.menu_id,
        fme.sub_menu_id,
        fme.entry_sequence,
        fme.prompt,
        fme.description
    FROM
        fnd_menus_vl        fm,
        fnd_menus_vl        fm1,
        fnd_menu_entries_vl fme
    WHERE
        1 = 1
        AND fm.menu_id = fme.menu_id
        AND fme.sub_menu_id = fm1.menu_id
        AND fm.user_menu_name||'-'|| fm1.user_menu_name IN (

'IEX: Collections Agent Responsibility Menu-TeleSales Collateral Menu',
'IEX: Collections Agent Responsibility Menu-TeleSales Contracts Tab Menu'
);
    CURSOR c3 IS
    SELECT
    fm.user_menu_name||'-'|| fm1.user_menu_name menu_submenu_name,
        fm.menu_id,
        fme.sub_menu_id,
        fme.entry_sequence,
        fme.prompt,
        fme.description
    FROM
        fnd_menus_vl        fm,
        fnd_menus_vl        fm1,
        fnd_menu_entries_vl fme
    WHERE
        1 = 1
        AND fm.menu_id = fme.menu_id
        AND fme.sub_menu_id = fm1.menu_id
        AND fm.user_menu_name||'-'|| fm1.user_menu_name IN 

( 'Setup:-iProcurement Administration: Home Page' );

BEGIN
    dbms_output.put_line('Main Program start');
    FOR i IN c1 LOOP
        BEGIN
            dbms_output.put_line('cursor start for menu_function:'|| i.menu_function_name);
            fnd_menu_entries_pkg.update_row(
                                           x_menu_id           => i.menu_id,
                                           x_entry_sequence    => i.entry_sequence,
                                           x_sub_menu_id       => NULL,
                                           x_function_id       => i.function_id,
                                           x_grant_flag        => 'N',                 -- Grant Flag
                                           x_prompt            => i.prompt,            -- Prompt
                                           x_description       => i.description,
                                           x_last_update_date  => sysdate,
                                           x_last_updated_by   => - 1,--fnd_global.user_id,
                                           x_last_update_login => - 1--fnd_global.login_id
            );
            l_fnc_cnt := l_fnc_cnt + 1;
        EXCEPTION
            WHEN OTHERS THEN
                dbms_output.put_line('Inner Exception: ' || sqlerrm);
        END;
        COMMIT;
    END LOOP;
    dbms_output.put_line('totla function updated l_fnc_cnt:' || l_fnc_cnt);

    FOR i IN c2 LOOP
        BEGIN
            dbms_output.put_line('cursor start for menu_submenu:'|| i.menu_submenu_name);
            fnd_menu_entries_pkg.update_row(
                                           x_menu_id           => i.menu_id,
                                           x_entry_sequence    => i.entry_sequence,
                                           x_sub_menu_id       => i.sub_menu_id,
                                           x_function_id       => NULL,
                                           x_grant_flag        => 'N',                 -- Grant Flag
                                           x_prompt            => i.prompt,            -- Prompt
                                           x_description       => i.description,
                                           x_last_update_date  => sysdate,
                                           x_last_updated_by   => - 1,--fnd_global.user_id,
                                           x_last_update_login => - 1--fnd_global.login_id
            );
            l_sm_cnt := l_sm_cnt + 1;
        EXCEPTION
            WHEN OTHERS THEN
                dbms_output.put_line('Inner Exception: ' || sqlerrm);
        END;
        COMMIT;
    END LOOP;
    dbms_output.put_line('totla function updated l_sm_cnt:' || l_sm_cnt);

    FOR i IN c3 LOOP
        BEGIN
            dbms_output.put_line('cursor start for Y menu_submenu:'|| i.menu_submenu_name);
            fnd_menu_entries_pkg.update_row(
                                           x_menu_id           => i.menu_id,
                                           x_entry_sequence    => i.entry_sequence,
                                           x_sub_menu_id       => i.sub_menu_id,
                                           x_function_id       => NULL,
                                           x_grant_flag        => 'Y',                 -- Grant Flag
                                           x_prompt            => i.prompt,            -- Prompt
                                           x_description       => i.description,
                                           x_last_update_date  => sysdate,
                                           x_last_updated_by   => - 1,--fnd_global.user_id,
                                           x_last_update_login => - 1--fnd_global.login_id
            );
            l_smy_cnt := l_smy_cnt + 1;
        EXCEPTION
            WHEN OTHERS THEN
                dbms_output.put_line('Inner Exception: ' || sqlerrm);
        END;
        COMMIT;
    END LOOP;
    dbms_output.put_line('totla function updated l_smy_cnt:' || l_smy_cnt);

    dbms_output.put_line('Main Program end');
EXCEPTION
    WHEN OTHERS THEN
        dbms_output.put_line('Main Exception:' || sqlerrm);
END;
/

Script to update responsibility end date

 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
                     upper('OM Super User')
               AND frt.language = userenv('LANG')
               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,
            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;
/