Tuesday, September 25, 2012

SOME HANDY DBA SCRIPTS

------------------------------------
TO LIST JOBS CURRENTLY RUNNING:

SELECT a.job "Job",
               a.sid,
               a.failures "Failures",
              Substr(To_Char(a.last_date,'DD-Mon-YYYY HH24:MI:SS'),1,20) "Last Date",
              Substr(To_Char(a.this_date,'DD-Mon-YYYY HH24:MI:SS'),1,20) "This Date"
FROM  dba_jobs_running a;

-------------------------------------
TO LIST ALL DB SESSIONS:

SELECT NVL(a.username, '(oracle)') AS username,
               a.osuser,
               a.sid,
               a.serial#,
               c.value AS,
               a.lockwait,
               a.status,
               a.module,
               a.machine,
               a.program,
               d.name,
               TO_CHAR(a.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM    v$session a,
               v$sesstat c,
               v$statname d
WHERE a.sid = c.sid
     AND c.statistic# = d.statistic#
ORDER BY c.value DESC;

-------------------------------------
TO DISPLAY DB SESSIONS AS A HIERARCHY:

SELECT  LPAD(' ', (LEVEL - 1) * 2, ' ') || NVL (s.username, '(oracle)') AS username,
              s.osuser,
              s.sid,
              s.serial#,
              s.lockwait,
              s.status,
              s.module,
              s.machine,
              s.program,
              TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM v$session s
CONNECT BY PRIOR s.sid = s.blocking_session
START WITH s.blocking_session IS NULL;
-----------------------------------
TO GRANT INSERT ON SCHEMA TABLES:

SPOOL temp.sql

SELECT 'GRANT INSERT ON "' ||u.table_name|| '" TO &1;'
FROM   user_tables u
WHERE NOT EXISTS (SELECT '1'
                                      FROM all_tab_privs a
                                     WHERE a.grantee = UPPER('&1') AND
                                                   a.privilege = 'INSERT' AND
                                                   a.table_name = u.table_name);

SPOOL OFF
-- Comment out following line to prevent immediate run
-- @temp.sql

-------------------------------------
TO GRANT EXECUTE ON SCHEMAS OBJECTS:

SPOOL temp.sql
SELECT 'GRANT EXECUTE ON "' ||u.object_name|| '" TO &1;'
FROM   user_objects u
WHERE u.object_type IN ('PACKAGE','PROCEDURE','FUNCTION')
    AND NOT EXISTS (SELECT '1'
                                     FROM all_tab_privs a
                                     WHERE a.grantee = UPPER('&1')
                                          AND a.privilege = 'EXECUTE'
                                          AND a.table_name = u.object_name);

SPOOL OFF
-- Comment out following line to prevent immediate run
-- @temp.sql

-------------------------------------
TO CREATE SYNONYMS IN CURRENT SCHEMA:

SPOOL temp.sql

SELECT 'CREATE SYNONYM "' ||a.object_name ||'" FOR "' ||a.owner|| '"."' ||a.object_name|| '";' FROM   all_objects a
WHERE a.object_type IN ('PACKAGE','PROCEDURE','FUNCTION')
     AND a.owner = UPPER('&1')
     AND NOT EXISTS (SELECT '1'
                                       FROM user_synonyms u
                                      WHERE u.synonym_name = a.object_name
                                           AND u.table_owner = UPPER('&1'));

SPOOL OFF
-- Comment out following line to prevent immediate run
-- @temp.sql

-------------------------------------
TO KNOW USER CONTEXT:

SELECT SYS_CONTEXT ('USERENV', 'SESSION_USER') FROM DUAL;

-------------------------------------
TO SHOW INDEXES:

SELECT table_owner,
               table_name,
               owner AS index_owner,
               index_name,
               tablespace_name,
               num_rows,
               status,
               index_type
FROM   dba_indexes
WHERE table_owner = UPPER('&1')
     AND table_name = DECODE(UPPER('&2'), 'ALL', table_name, UPPER('&2'))
ORDER BY table_owner, table_name, index_owner, index_name;

-------------------------------------

API FOR APPLICATION USER PASSWD RESET :

begin
      fnd_user_pkg.UpdateUser(
           x_user_name                             => <user login id>,
           x_owner                                    => null,
           x_unencrypted_password          => 'welcome1',
           x_start_date                              => to_date(sysdate,'MM/DD/YYYY'),
           x_end_date                               => fnd_user_pkg.null_date,
           x_last_logon_date                     => fnd_user_pkg.null_date,
           x_description                            => <user full name>,
           x_password_date                     => fnd_user_pkg.null_date,
           x_password_accesses_left        => fnd_user_pkg.null_number,
           x_password_lifespan_accesses => fnd_user_pkg.null_number,
           x_password_lifespan_days       => 180,
           x_employee_id                         => null,
           x_fax                                       => fnd_user_pkg.null_char,
           x_customer_id                         => fnd_user_pkg.null_number,
           x_supplier_id                           => fnd_user_pkg.null_number,
          x_email_address                      => <User Email Address>);
end;

------------------------------------


API FOR ASSIGN SYSADMIN RESPONSIBILITY TO AN APPLICATION USER:

begin
    fnd_user_pkg.AddResp(
               username => <user login id>,
               resp_app => 'SYSADMIN',
               resp_key => 'SYSTEM_ADMINISTRATOR',
               security_group => 'STANDARD',
               description => 'System Administrator',
               start_date => sysdate,
               end_date => null);
end;

begin
    fnd_user_pkg.AddResp(
               username => <user login id>,
               resp_app => 'ICX',
               resp_key => 'SYSTEM_ADMINISTRATION',
               security_group => 'STANDARD',
               description => 'System Administration',
               start_date => sysdate,
               end_date => null);
end;

------------------------------------

R12 COMPILE JAVA SERVER PAGES :

Go to $HTML_TOP or where the JSPs are located.
For example; to compile "abcdef_12.jsp"

$FND_TOP/patch/115/bin/ojspCompile.pl --compile -s 'abcdef_12.jsp' --flush

Check the log for any errors, correct them and compile.
Bounce the apache from the $ADMIN_SCRIPTS_HOME

------------------------------------

R12 USERS WITH NO LOG ON ACTIVITY IN APPLICATIONS FOR LAST 60 DAYS:

SELECT USER_NAME,
               DESCRIPTION FULL_NAME,
               TO_CHAR(LAST_LOGON_DATE, 'DD-MON-YYYY') LastLogon
  FROM  FND_USER
WHERE LAST_LOGON_DATE < SYSDATE - 60 AND
               USER_ID > 1100 AND
               END_DATE IS NULL
ORDER BY last_logon_date ASC


------------------------------------
Some Useful Links
http://www.orafaq.com/wiki/Scripts
http://oracle-base.com/dba/scripts.php
http://dbakevlar.com/scripts/
http://rafioracledba.blogspot.com/2010/08/database-health-check.html
------------------------------------

Thursday, September 6, 2012

SQL Trace with EBS

These MOS docs are handy for any performance issues:

How To Use SQL Trace And TKPROF For Performance Issues with EBusiness Suite [ID 980711.1] Author: Luis J. Gonzalez
https://support.oracle.com/epmos/faces/ui/km/DocContentDisplay.jspx?_afrLoop=1008981601705720&id=980711.1&_afrWindowMode=0&_adf.ctrl-state=pvt2x3r2b_74


How To Run Application Collection Tool (ACT/RDA) Diagnostics Test? [ID 559800.1]
https://support.oracle.com/epmos/faces/ui/km/DocContentDisplay.jspx?_afrLoop=1009691371364643&id=559800.1&_afrWindowMode=0&_adf.ctrl-state=pvt2x3r2b_313

Troubleshooting Oracle Applications Performance Issues [ID 169935.1] Author: Carlos Sierra
https://support.oracle.com/epmos/faces/ui/km/DocContentDisplay.jspx?_afrLoop=1100600262021534&id=169935.1&_afrWindowMode=0&_adf.ctrl-state=v9vy1cibx_388


https://support.oracle.com/epmos/faces/ui/km/DocContentDisplay.jspx?_afrLoop=1507179551130647&id=438652.1&_afrWindowMode=0&_adf.ctrl-state=19e01bmqeu_101

R12: How To Trace and TKPROF A Concurrent Program With Performance Issue
1) As System Administrator, go to Concurrent-> programs -> Define, and query program
     with the problem

2) Enable the check box called 'Enable Trace' and save

3) Before starting trace, make sure the following DB parameters are set:
    MAX_DUMP_FILE_SIZE = UNLIMITED
    TIMED_STATISTICS = TRUE
    STATISTICS_LEVEL = ALL

   If they are not set as above, run the following as SYS:
     SQL> alter system set MAX_DUMP_FILE_SIZE=UNLIMITED;
     SQL> alter system set TIMED_STATISTICS=TRUE;
     SQL> alter system set STATISTICS_LEVEL=ALL;

4) Run the report reproducing the issue


5) After the process finishes, get the trace file from the directory set from below:
       SELECT value
         FROM v$parameter
         WHERE name = 'user_dump_dest';  

     then run tkprof for the trace file
           with  sort=exeela,prsela,fchela and explain=apps/appspw.
    Use tkprof from DB Oracle Home, and that the process already finished.
    If the process has not finished the rows column in tkprof will be all zeros and useless.

6) Once the log file, raw trace and sorted tkprof trace files are ready,
     ZIP them into a single file and upload to My Oracle Support (Metalink).

7) Run RDA Collection Test per Note:559800.1/Note:732091.1 for corresponding modules
     for ex., AR and SLA

Another way of Tracing a Concurrent Program:


1. Ensure that patch# 8743459 is applied
(Patch 8743459: 12.1.1: 'SQL TRACE' IN 'DEBUG OPTIONS' IS UNLIKELY WORKING)

2. Next, Navigate to System Administrator - and set the profile - FND: Debug Enabled
     to No - at all levels (site and user) to ensure this is not turned on.

3. Move to Concurrent/Program/Define - query the concurrent program and uncheck the 'Enable trace' box

4. To create the trace:

a. Navigate to System Administrator responsibility
b. Navigate to Profiles->System
c. Query the Profile Option Concurrent: Allow Debugging and set it to Yes at User level
d. Navigate to responsibility to excute the concurrent program
e. From the Requests form, choose the Concurrent Program and set the required Parameters
f. Click the Debug button on the SUBMIT Form that is now enabled. This will open a new window
g. Check the SQL Trace checkbox and specify Trace with Binds and Waits
     - Choose Ok - then it will tell you debug rule has been created - close the window
h. now back at the Submit form - Submit the Concurrent program - let it complete
i. Reset the value of the profile Option Concurrent: Allow Debugging
j. Retrieve the trace file created - it should have the user-name and concurrent request
    number in the filename

Finding Trace Files

Trace files are stored in the Automatic Diagnostic Repository (ADR), in the trace directory under each ADR home. To locate individual trace files within this directory, one can use data dictionary views. For example, find the path to the current session's trace file or to the trace file for each Oracle Database process.


To find the trace file for your current session:
SELECT VALUE FROM V$DIAG_INFO WHERE NAME = 'Default Trace File';
The full path to the trace file is returned.

To find all trace files for the current instance:
SELECT VALUE FROM V$DIAG_INFO WHERE NAME = 'Diag Trace';
The path to the ADR trace directory for the current instance is returned.

To determine the trace file for each Oracle Database process:
SELECT PID, PROGRAM, TRACEFILE FROM V$PROCESS;

To find trace file directory path:
SELECT VALUE FROM SYS.V_$DIAG_INFO WHERE NAME = 'Diag Trace';

Thursday, August 30, 2012

FNDLOAD XDO Objects

Data Definition and Data Template

1) Download and Upload all the data definitions and corresponding templates in an instance.
FNDLOAD apps/apps_pwd@db 0 Y DOWNLOAD $XDO_TOP/patch/115/import/xdotmpl.lct targetldtfile.ldt XDO_DS_DEFINITIONS

FNDLOAD apps/apps_pwd@db 0 Y UPLOAD $XDO_TOP/patch/115/import/xdotmpl.lct targetldtfile.ldt

2) Download all the data definitions and the corresponding Templates in a module.
FNDLOAD apps/apps_pwd@db 0 Y DOWNLOAD $XDO_TOP/patch/115/import/xdotmpl.lct targetldtfile.ldt XDO_DS_DEFINITIONS APPLICATION_SHORT_NAME=XXX

3) Download a particular data definitions and its corresponding Templates.
FNDLOAD apps/apps_pwd@db 0 Y DOWNLOAD $XDO_TOP/patch/115/import/xdotmpl.lct targetldtfile.ldt XDO_DS_DEFINITIONS APPLICATION_SHORT_NAME=XXX DATA_SOURCE_CODE=EMP_DD

XDOLOADER :
File Download Only Mode
Syntax :
java oracle.apps.xdo.oa.util.XDOLoader DOWNLOAD \
-DB_USERNAME \
-DB_PASSWORD \
-JDBC_CONNECTION \
-LOB_TYPE \
-APPS_SHORT_NAME \
-LOB_CODE \
-LANGUAGE \
-TERRITORY \
-LOG_FILE


1) Download all the physical files in the instance.
java oracle.apps.xdo.oa.util.XDOLoader DOWNLOAD \
-DB_USERNAME apps \
-DB_PASSWORD apps \
-JDBC_CONNECTION apsrtc:1521:SID \
-LANGUAGE en \
-TERRITORY US

2) Download all the physical files for a specified module
java oracle.apps.xdo.oa.util.XDOLoader DOWNLOAD \
-DB_USERNAME apps \
-DB_PASSWORD apps \
-JDBC_CONNECTION apsrtc:1521:SID \
-APPS_SHORT_NAME AR \
-LANGUAGE en \
-TERRITORY US

3) Download all the physical files for a specified module and LOB Type
java oracle.apps.xdo.oa.util.XDOLoader DOWNLOAD \
-DB_USERNAME apps \
-DB_PASSWORD apps \
-JDBC_CONNECTION apsrtc:1521:SID \
-LOB_TYPE BURSTING_FILE \
-LANGUAGE en \
-TERRITORY US

File Download and LDT/DRVX Generation Mode
Syntax :
java oracle.apps.xdo.oa.util.XDOLoader DOWNLOAD \
-DB_USERNAME \
-DB_PASSWORD \
-JDBC_CONNECTION \
-APPS_SHORT_NAME \
-DS_CODE (data source code> \
-LCT_FILE \
-LDT_FILE \
-DRVX_FILE \
-LOG_FILE

    ________________________________________________________________________

1) Download all the physical files of a specified application in an instance.
java oracle.apps.xdo.oa.util.XDOLoader DOWNLOAD \
-DB_USERNAME apps \
-DB_PASSWORD welcome \
-JDBC_CONNECTION r12.com:1533:R124 \
-APPS_SHORT_NAME AR \
-LCT_FILE ${XDO_TOP}/patch/115/import/xdotmpl.lct

2) Download all the physical files of a specified application and in and data definition in an instance.
java oracle.apps.xdo.oa.util.XDOLoader DOWNLOAD \
-DB_USERNAME apps \
-DB_PASSWORD welcome \
-JDBC_CONNECTION r12.com:1533:R124 \
-APPS_SHORT_NAME AMW \
-LCT_FILE ${XDO_TOP}/patch/115/import/xdotmpl.lct \
-DS_CODE EMP_DD

Uploading Physical Files
Syntax :
java oracle.apps.xdo.oa.util.XDOLoader UPLOAD \
-DB_USERNAME \
-DB_PASSWORD \
-JDBC_CONNECTION \      -- db2197.oracle.com:43501:orcl
-LOB_TYPE \                                    --TEMPLATE_SOURCE
-DEBUG
-APPS_SHORT_NAME \  -- XXX
-LOB_CODE \
-LANGUAGE \                                         -- en
-TERRITORY \                                          --00
-XDO_FILE_TYPE \                        -- RTF
-NLS_LANG \
-FILE_CONTENT_TYPE \         -- application/rtf
-FILE_NAME \                                       -- myfilename.rtf
-OWNER \
-CUSTOM_MODE [FORCE NOFORCE] \
-LOG_FILE
    _________________________________________________________________________
      _______________________________________________________________________
1) Upload the RTF file.
java oracle.apps.xdo.oa.util.XDOLoader \
UPLOAD \
-DB_USERNAME apps \
-DB_PASSWORD welcome \
-JDBC_CONNECTION r12.com:1533:R124 \
-LOB_TYPE TEMPLATE \
-APPS_SHORT_NAME AMW \
-LOB_CODE EMP_DD \
-LANGUAGE en \
-TERRITORY US \
-NLS_LANG American_America.WE8ISO8859P1 \
-XDO_FILE_TYPE RTF \
-FILE_CONTENT_TYPE ’text/html’ \
-FILE_NAME /tmp/psomanat/TEMPLATE_SOURCE_AMW_Emp_Template_en_US.rtf


API to load Template Definition:

PROMPT Loading Template Definition
BEGIN
XDO_TEMPLATES_PKG.load_row (
X_APPLICATION_SHORT_NAME => 'XXX',
X_TEMPLATE_CODE => 'short_template_name',
X_DS_APP_SHORT_NAME => 'XXX',
X_DATA_SOURCE_CODE => 'short_data_source',
X_TEMPLATE_TYPE_CODE => 'RTF',
X_DEFAULT_LANGUAGE => 'en',
X_DEFAULT_TERRITORY => '00', -- 00 means no territory
X_MLS_LANGUAGE => NULL,
X_MLS_TERRITORY => NULL,
X_TEMPLATE_STATUS => 'E',
X_USE_ALIAS_TABLE => 'N',
X_START_DATE => TO_CHAR (SYSDATE, 'YYYY/MM/DD'),
X_END_DATE => NULL,
X_TEMPLATE_NAME => 'name of template',
X_DESCRIPTION => 'description of template',
X_OWNER => 'USERNAME', -- CUSTOM will make this non-updatable in the UI
X_LAST_UPDATE_DATE => NULL, -- should use system date here if null
X_CUSTOM_MODE => 'FORCE',
X_DEPENDENCY_FLAG => 'P', /* P for templates, C for subtemplates */
X_OBJECT_VERSION_NUMBER => 1,
X_ATTRIBUTE_CATEGORY => NULL,
X_ATTRIBUTE1 => NULL,
X_ATTRIBUTE2 => NULL,
X_ATTRIBUTE3 => NULL,
X_ATTRIBUTE4 => NULL,
X_ATTRIBUTE5 => NULL,
X_ATTRIBUTE6 => NULL,
X_ATTRIBUTE7 => NULL,
X_ATTRIBUTE8 => NULL,
X_ATTRIBUTE9 => NULL,
X_ATTRIBUTE10 => NULL,
X_ATTRIBUTE11 => NULL,
X_ATTRIBUTE12 => NULL,
X_ATTRIBUTE13 => NULL,
X_ATTRIBUTE14 => NULL,
X_ATTRIBUTE15 => NULL);
END;
/
COMMIT;
/

Tables to be handy:


select * from all_tables where table_name like 'XDO%DS%';
select * From xdo_lobs where lob_code like 'XX%_APXUATR_XMLP';

select * from xdo_templates_vl where template_code like '%APXUA%';
select * from xdo_templates_tl where template_code like ' %APXUA%';
select * from xdo_templates_b where template_code like '%APXUA%';

select * from xdo_ds_definitions_tl where data_source_code like '%APXUA%';
select * from xdo_ds_definitions_b where data_source_code like '%APXUA%';
select * from xdo_ds_definitions_vl where data_source_code like '%APXUA%';







FNDLOAD Common Objects


MENU:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct menu.ldt MENU MENU_NAME=BOM_NAV
FUNCTION:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct function.ldt FUNCTION FUNCTION_NAME=INV_INVTTMTX

FORM:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct form.ldt FORM FORM_NAME=${shortname}

PERSONALIZATION:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct formpersonalization.ldt FND_FORM_CUSTOM_RULES function_name=${shortname}

PRINTER:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcppstl.lct printer.ldt STYLE PRINTER_STYLE_NAME=${shortname}

CONCURRENT:
FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct concprog.ldt PROGRAM CONCURRENT_PROGRAM_NAME=${shortname}

LOOKUP:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct lookup.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME="FND" LOOKUP_TYPE=${shortname}

RESPONSIBILITY:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/admin/import/afscursp.lct responsible.ldt FND_RESPONSIBILITY RESP_KEY="${respkey}"

DESCFLEX:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct ${shortname}_flex.ldt DESC_FLEX DESCRIPTIVE_FLEXFIELD_NAME=${shortname}
KEYFLEX:
FNDLOAD apps/$PASSWD O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct keyflex.ldt KEY_FLEX P_LEVEL='COL_ALL:FQL_ALL:SQL_ALL:STR_ONE:WFP_ALL:SHA_ALL: CVR_ALL:SEG_ALL' APPLICATION_SHORT_NAME=${appshortname} ID_FLEX_CODE=MCAT P_STRUCTURE_CODE=${shortname}

VALUE:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct valset.ldt VALUE_SET FLEX_VALUE_SET_NAME=${shortname}

PROFILE:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct profile.ldt PROFILE PROFILE_NAME=${shortname}

MESSAGE:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct message.ldt FND_NEW_MESSAGES MESSAGE_NAME=${shortname}

REQUEST GROUP:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct reqgrp.ldt REQUEST_GROUP REQUEST_GROUP_NAME=${shortname}

REQUEST SET:
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct rqstset.ldt REQ_SET APPLICATION_SHORT_NAME="FND" REQUEST_SET_NAME=${shortname}

WEBADI:
FNDLOAD apps/apps 0 Y DOWNLOAD $BNE_TOP/admin/import/bnelay.lct XX_C_O_F_T.ldt BNE_LAYOUTS LAYOUT_ASN="PER" LAYOUT_CODE="XX_C_O_F_T"

Monday, May 7, 2012

To Set R12 oracle SQL Session Apps Context

SQL to get ORG_ID’s and OUs:

select ORGANIZATION_ID, 
          NAME
from HR_OPERATING_UNITS;

Pre-R12: To set SQL session context

BEGIN
  dbms_application_info.set_client_info(ORG_ID); -- #ORG_ID   
END;

OR

FND_GLOBAL.APPS_INITIALIZE

OR

exec dbms_application_info.set_client_info(ORG_ID); --#ORG_ID

R12: To set SQL session context for Single OU:

BEGIN
   execute mo_global.set_policy_context('S',ORG_ID);
END;

-- ‘S’ means Single Org Context
-- #ORG_ID

To set SQL session context for Multiple OU’s:

BEGIN
  execute mo_global.set_org_access(NULL,64,‘ONT');
END;

-- 64 is the Security Profile one wants to use
-- ‘ONT’ is the application short name associated with the responsibility

To get Security Profiles and OU Names as assigned:

select psp.SECURITY_PROFILE_NAME,
         psp.SECURITY_PROFILE_ID,
         hou.NAME,
         hou.ORGANIZATION_ID
from PER_SECURITY_PROFILES psp,
        PER_SECURITY_ORGANIZATIONS pso,
        HR_OPERATING_UNITS hou
where pso.SECURITY_PROFILE_ID = psp.SECURITY_PROFILE_ID
    and pso.ORGANIZATION_ID = hou.ORGANIZATION_ID;

SQL to get Security Profiles assigned through Profile Options:

select fnd_profile.value_specific('XLA_MO_SECURITY_PROFILE_LEVEL', :user_id, :resp_id, :appl_id)
 from dual;

--   Where:
--                user_id: FND_USER.USER_ID
--                resp_id: FND_RESPONSIBILITY_TL.RESPONSIBILITY_ID
--                appl_id: FND_APPLICATIONS.APPLICATION_ID

GOOD REFS:
MOS Note:  420787.1 Oracle Applications Multiple Organizations Access Control for Custom Code
Oracle Doc: ‘Oracle Applications Multiple Organizations’ Implementation Guide Release 12 Part No. B31183-02

Monday, February 13, 2012

To Find Applied Patch and Patch Levels

To find quicker way with the following Queries:
SELECT *
FROM ad_bugs
WHERE bug_number='XXXXXX';

SELECT *
FROM ad_applied_patches
WHERE patch_name='YYYYYY';

Note: Please enter Patch number in place of and , e.g '3453499'


To check patch level:
SELECT patch_level,
               application_name
FROM fnd_product_installations fpi,
            fnd_application_tl fat
WHERE patch_level IS NOT NULL AND
               fpi.application_id = fat.application_id
ORDER BY application_name;

Oracle Applications Current Patchset Comparison Utility - patchsets.sh [ID 139684.1]

As per ID 443761.1 there are Several Methods to check for a certain patch:

Method 1:
Check Patches applied from Oracle Applications Manager (OAM).
Connect to OAM:
Go to Site Map >> Maintenance >> Applied Patches - here search for specific patch

Method 2:
Use 'adphrept' utility documented in Metalink Note:181665.1:
Example: To get the complete patch details for patches applied in Dec 2011:

On UNIX:
$ cd $AD_TOP/patch/115/sql
$ sqlplus /
SQL> @adphrept.sql 3 ALL ALL 12/01/11 12/31/11 ALL ALL ALL ALL ALL N N N N N

On NT:
C:\> cd %AD_TOP%\patch\115\sql
C:\> sqlplus
SQL> @adphrept.sql 3 ALL ALL 12/01/11 12/31/11 ALL ALL ALL ALL ALL N N N N N

Method 3:
Use the following query, however methods 1 and 2 are more reliable.
sqlplus apps/
SQL>SELECT DISTINCT
                         RPAD (a.bug_number, 11)
                       || RPAD (e.patch_name, 11)
                       || RPAD (TRUNC (c.end_date), 12)
                       || RPAD (b.applied_flag, 4)    BUG_APPLIED
           FROM ad_bugs a,
                       ad_patch_run_bugs b,
                       ad_patch_runs c,
                       ad_patch_drivers d,
                       ad_applied_patches e
           WHERE a.bug_id = b.bug_id
                AND b.patch_run_id = c.patch_run_id
                AND c.patch_driver_id = d.patch_driver_id
                AND d.applied_patch_id = e.applied_patch_id
                AND a.bug_number IN  ('','')
          ORDER BY 1 DESC;
Note: Please enter Patch number in place of and , e.g '3453499'

Method 4:
In multi-node environment you are advised to use Patch Query from section "Checking the Patch Requirements on each Appl_Top" of the Metalink Note:364439.1. There are also good Tips and Queries for Troubleshooting Advanced Topologies.


Method 5:
Use the following query, to find Applied Patches in EBS 12.2
sqlplus apps/
SQL>
SELECT adop_session_id,
         bug_number,
         session_type,
         DECODE (status,
                 'N', 'Applied on other nodes',
                 'R', 'Running',
                 'H', 'Failed (Hard)',
                 'F', 'Failed (Jobs Skipped)',
                 'S', 'Success (Jobs Skipped)',
                 'Y', 'Success',
                 'C', 'Clone Complete')
             status,
         applied_file_system_base,
         patch_file_system_base,
         node_name,
         start_date,
         end_date,
         ROUND ((end_date - start_date) * 24 * 60, 2)
             exec_time,
         adpatch_options,
         autoconfig_status,
         driver_file_name
    FROM ad_adop_session_patches
   WHERE session_type IN ('ADPATCH',
                          'HOTPATCH',
                          'DOWNTIME',
                          'ONLINE')
ORDER BY adop_session_id, start_date, end_date;

Session type column was restricted to the following values:
§  ADPATCH: Patches applied using the traditional adpatch tool. It is used right after installing 12.2 and before online patching is enabled by applying patch 13543062.
§  HOTPATCH: Patches applied in hotpatch mode (aka. no online patching cycle is involved). It should be used only when instructed by Oracle.
§  DOWNTIME: Patches applied when all application services are down. Only used when instructed by Oracle, such as when applying 12.2.6 RUP.

§  ONLINE: Patches applied in a normal online patching cycle. This is the recommended method.

Tuesday, January 31, 2012

SQL to Get Request Groups and Some Useful Scripts

SQL query to get the Request Groups assigned to a Conc Program:
SELECT
   RG.APPLICATION_ID "Request Group Application ID",
   RG.REQUEST_GROUP_ID "Request Group - Group ID",
   RG.REQUEST_GROUP_NAME,
   RG.DESCRIPTION,
   rgu.unit_application_id,
   rgu.request_group_id "Request Group Unit - Group ID",
   rgu.request_unit_id,cp.concurrent_program_id,
   cp.concurrent_program_name,
   cpt.user_concurrent_program_name,
   DECODE(rgu.request_unit_type,'P','Program','S','Set',rgu.request_unit_type) "Unit Type"
FROM
   fnd_request_groups rg,
   fnd_request_group_units rgu,
   fnd_concurrent_programs cp,
   FND_CONCURRENT_PROGRAMS_TL CPT
WHERE rg.request_group_id = rgu.request_group_id
   AND rgu.request_unit_id = cp.concurrent_program_id
   AND cp.concurrent_program_id = cpt.concurrent_program_id
   AND upper(cpt.user_concurrent_program_name) like '%UNACCOUNTED%';

Query to Check Menu and Request Group attached to a Reponsilblity:

SELECT DISTINCT
               a.responsibility_name,
               c.user_menu_name,
               (SELECT request_group_name
                 FROM apps.fnd_request_groups g
                WHERE g.application_id = b.application_id
                  AND g.request_group_id = b.request_group_id) requet_grp
FROM apps.fnd_responsibility_tl a,
            apps.fnd_responsibility b,
            apps.fnd_menus_tl c,
            apps.fnd_menus d,
            apps.fnd_application_tl e,
            apps.fnd_application f
WHERE a.responsibility_id(+) = b.responsibility_id
    --AND a.responsibility_id = '20538'
      AND b.menu_id = c.menu_id
      AND b.menu_id = d.menu_id
      AND e.application_id = f.application_id
      AND f.application_id = b.application_id
      AND a.LANGUAGE = 'US';

API to add a request group:
If you want to script your request group in sql, rather than FNDLOAD, to be more dynamic… here’s a sample of the API… the api will fail if the program is already in the group, thus the IF check so the script can be run multiple times without failing after the first. Just thought I’d suggest an alternative to FNDLOAD, as below:


BEGIN
IF (NOT FND_PROGRAM.PROGRAM_IN_GROUP('ARACCPB', 'Receivables','F5 Receivables All', 'Receivables')) THEN
   dbms_output.put_line('Assigning ARACCPB to request group');
   FND_PROGRAM.ADD_TO_GROUP(
                          program_short_name => 'ARACCPB',
                          program_application => 'Receivables',
                          request_group => 'F5 Receivables All',
                          group_application => 'Receivables');
else
    dbms_output.put_line('ARACCPB already in request group');
END IF;
END;
/
commit
/

Seeded Report Set Used to Know Full Functional Details of a Responsibility:
Function Security Reports (Report Set)
- Function Security Function Report
- Function Security Menu Report
- Function Security Navigator Report

Params to be submitted: Application and Responsibility

Some useful scripts Available with the following link; also work in R12:
http://oracle.anilpassi.com/some-scripts-contributed-by-ahmad-bilal-16.html

Tuesday, January 24, 2012

SQL Behind R12 Self Service Application Web (OA Framework) Page

In R12 with the Oracle’s new OA framework one can find the sql behind the html or web page. Oracle has given almost the same functionality in the “About this Page” link. If you want to find the SQL query behind any web page, you need to enable personalization and then use “About this Page” to find the view object. Below is an example for daily rates page from the GL module:

Step 1: Enable the personalization profile
   a) Under the system admin responsibility, open the system profiles form
   b) Change the “Personalize Self-Service Defn” profile to yes for the responsibility

Step 2: Perform the business process as you get into GL responsibility as below:
   a) Using the GL Super User responsibility, open the Daily Rates web page:
           >> setup >> Currencies >> Currency Rate Manager >> Daily Rates
   b) Find your daily rates for a combination of given options

Step 3: Click the About the Page link

Step 4: Click the Page tab and then click the Expand button on the business components section

Step 5: Find you view object and click the link

Step 6: Select the SQL query and copy it to your notepad (TOAD, SQL Developer,..)

Step 7: Replace any variables (anything that has a colon in front of it) with your data and test

Step 8: Modify the query and use it.