Session SQL and Lock

                    SELECT a.request_id, d.sid, d.serial# ,d.osuser,d.process , c.SPID ,d.inst_id, a.concurrent_program_id, d.event
                    , d.*
                    FROM apps.fnd_concurrent_requests a,
                    apps.fnd_concurrent_processes b,
                    gv$process c,
                    gv$session d
                    WHERE a.controlling_manager = b.concurrent_process_id
                    AND c.pid = b.oracle_process_id
                    --AND concurrent_program_id = 32766
                    AND b.session_id=d.audsid
                    AND c.inst_id = d.inst_id
                    AND a.request_id = 197521679
                   
                             SELECT s2.sid
                             ,      s2.lockwait
                             ,      s1.sql_text
                             ,      s1.piece
                             FROM   gv$SQLtext s1
                             ,      gv$session s2
                             WHERE  s1.address =  s2.sql_address
                             AND    s1.inst_id = s2.inst_id
                             --AND    s2.sid = 1380
                             --AND    s2.inst_id = 2
                             AND module IN ('e:XXX:cp:inv/INCOIN')
                             ORDER BY s2.sid, s1.piece


                             SELECT *
                             FROM   gv$session
                             WHERE  1=1
                             --AND    module like '%cp:%INV%'
                             AND    sid in (1380, 731)
                                                       
                            SELECT event, state, p1, p2, p3
                            FROM gv$session_wait
                            WHERE sid = 1380                            
                                       
                    SELECT description, USER_CONCURRENT_PROGRAM_NAME,CONCURRENT_PROGRAM_NAME
                    FROM   fnd_concurrent_programs_vl
                    WHERE  1=1
                    --AND    concurrent_program_id = 32766
                    AND    USER_CONCURRENT_PROGRAM_NAME like '%Receiving Transaction%'
                   


                        SELECT
                           l1.sid || ' is blocking ' || l2.sid blocking_sessions
                        FROM
                           gv$lock l1, gv$lock l2
                        WHERE
                           l1.block = 1 AND
                           l2.request > 0 AND
                           l1.id1 = l2.id1 AND
                           l1.id2 = l2.id2 AND
                           l1.inst_id = l2.inst_id                                    
                            

Query to get dependencies on an object

SELECT DISTINCT a.referenced_name main_parent, a.NAME main_child,b.referenced_name next_level_parent, b.NAME next_level_child
FROM all_dependencies a, all_dependencies b
WHERE 1 = 1
AND b.referenced_name = a.NAME
AND a.referenced_name IN('YYY_CIB_FLAGS_AGV','YYY_CIB_AGV','YYY_SLA_HOURS_AGV','YYY_SPL_PARTS_AGV')
ORDER BY 1, 3

FND_PROFILE_OPTION_VALUES

Column	Description
Profile Level	Level 1 = Site Level 2 = Application Level 3 = Responsibility Level 4 = User

SELECT substr(pro1.user_profile_option_name,1,35) Profile, decode(pov.level_id, 10001,'Site', 10002,'Application', 10003,'Resp', 10004,'User') Option_Level, decode(pov.level_id, 10001,'Site', 10002,appl.application_short_name, 10003,resp.responsibility_name, 10004,u.user_name) Level_Value, nvl(pov.profile_option_value,'Is Null') Profile_option_Value FROM  fnd_profile_option_values pov, fnd_responsibility_tl resp, fnd_application appl, fnd_user u, fnd_profile_options pro, fnd_profile_options_tl pro1 WHERE pro.profile_option_name = pro1.profile_option_name and  pro.profile_option_id = pov.profile_option_id and  resp.responsibility_name like '%General%Ledger%' and  pov.level_value = resp.responsibility_id (+) and  pov.level_value = appl.application_id (+) and  pov.level_value = u.user_id (+) order by 1,2; SELECT substr(pro1.user_profile_option_name,1,35) Profile, decode(pov.level_id, 10001,'Site', 10002,'Application', 10003,'Resp', 10004,'User') Option_Level, decode(pov.level_id, 10001,'Site', 10002,appl.application_short_name, 10003,resp.responsibility_name, 10004,u.user_name) Level_Value, nvl(pov.profile_option_value,'Is Null') Profile_option_Value FROM  fnd_profile_option_values pov, fnd_responsibility_tl resp, fnd_application appl, fnd_user u, fnd_profile_options pro, fnd_profile_options_tl pro1 WHERE pro1.user_profile_option_name like ('%Ledger%') and  pro.profile_option_name = pro1.profile_option_name and  pro.profile_option_id = pov.profile_option_id and  resp.responsibility_name like '%General%Ledger%' /* comment this line  if you need to check profiles for all responsibilities */ and  pov.level_value = resp.responsibility_id (+) and  pov.level_value = appl.application_id (+) and  pov.level_value = u.user_id (+) order by 1,2;

exec FND_USER_RESP_GROUPS_API.INSERT_ASSIGNMENT(17879,
20420,1,0,trunc(sysdate),null,'Adding in ERPDEV');

CREATE OR REPLACE package APPS.get_test
as
FUNCTION open (KEY IN VARCHAR2, VALUE IN VARCHAR2 ) RETURN VARCHAR2;
end ;
/


CREATE OR REPLACE PACKAGE BODY APPS.get_test
AS
FUNCTION open (
KEY IN VARCHAR2
,VALUE IN VARCHAR2
)
RETURN VARCHAR2
AS
LANGUAGE JAVA
NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String';
END get_test;
/

Add Concurrent Program to Request Group

fnd_program.add_to_group ('XX_PO_NOTIFY_RECEIVE','PO','All Reports','PO');

Menu->Responsibility

SELECT responsibility_name
FROM fnd_responsibility_vl
WHERE menu_id IN (SELECT menu_id
FROM fnd_menu_entries_vl
WHERE function_id IN (SELECT function_id
FROM fnd_menu_entries_vl
WHERE prompt = 'Define'))
UNION
SELECT responsibility_name
FROM fnd_responsibility_vl
WHERE menu_id IN (
SELECT menu_id
FROM fnd_menu_entries_vl
WHERE sub_menu_id IN (
SELECT menu_id
FROM fnd_menu_entries_vl
WHERE function_id IN (
SELECT function_id
FROM fnd_menu_entries_vl
WHERE prompt = 'Define')));

Users -> Responsibilities

SELECT SUM (num), user_name, tot_num
FROM (SELECT 1 num, frgd.user_id, fu.user_name, frgd.responsibility_id,
tot.tot_num
FROM fnd_user_resp_groups_direct frgd,
fnd_user fu,
fnd_responsibility_tl frt,
(SELECT COUNT (1) tot_num
FROM fnd_responsibility_tl
WHERE responsibility_name IN ('Application Developer', 'Purchasing')
AND LANGUAGE = 'US') tot
WHERE 1 = 1
AND frgd.user_id = fu.user_id
AND frgd.responsibility_id = frt.responsibility_id
AND frgd.responsibility_application_id = frt.application_id
--AND FRGD.USER_ID = 15732
--AND fu.user_name like 'PGARG'
AND LANGUAGE = 'US'
AND frt.responsibility_name IN ('Application Developer', 'Purchasing'))
GROUP BY user_name, tot_num
HAVING SUM (num) = tot_num

unix script from sql

SELECT apps.host_command(‘ls -ltr’) FROM dual;
--
SELECT apps.host_command(‘chmod 777 /devel/appl/xxcus/1.0.0/bin/filename’) FROM dual;
--
CREATE OR REPLACE FUNCTION host_command( cmd IN VARCHAR2 )
RETURN INTEGER
IS
STATUS NUMBER;
errormsg VARCHAR2(80);
pipe_name VARCHAR2(30);
BEGIN
pipe_name := ‘HOST_PIPE’;
dbms_pipe.pack_message( cmd );
STATUS := dbms_pipe.send_message(pipe_name);
RETURN STATUS;
END;

Add Request Set to Request Group

set serveroutput on
column date_column new_value today_var

select to_char(sysdate,'YYYYMMDDHHMI') date_column from dual
/
--
spool XXX_171_BOM_REQUEST_SET_&today_var
--
BEGIN
-- Add Request Set to request group.
BEGIN
fnd_set.add_set_to_group (request_set => 'XXX_171_BOM_REQUEST_SET',
set_application => 'XXX',
request_group => 'Bills of Material',
group_application => 'BOM'
);
DBMS_OUTPUT.PUT_LINE ('"XXX_171_BOM_REQUEST_SET" attached to request group uccessfully ');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.PUT_LINE ('Error in attaching "XXX_171_BOM_REQUEST_SET" to equest group ' || SQLERRM);
END;
--
COMMIT;
--
END;
/
Spool off