Handy SQL for the Oracle Applications

Goal
@ To Support: @ Do you have suggestions on some helpful SQL scripts when working with the Oracle Applications?
Solution
Yes, there are a number of helpful SQL scripts that we will share below. The scripts are segmented by foundation technology scripts (FND/ATG/AOL), then other product areas like manufacturing. Many of the scripts prompt for information. None of the scripts do any updates. Note that occasionally table names or columns change overtime. Where possible, the scripts will note if they will work only on a specific release. Otherwise, they should work across 11i to R12 though the scripts will be focused on the latest code.
1.5 Helpful SQL Syntax
1.5.1 Rowid: Working with Rowids: rowidtochar(rowid) and chartorowid('AAABUeAAQAAACsjAAg') 1.5.2 Date Columns a. Converting dates to show date with time: to_char(<date> 'DD-MON-YYYY HH24:MI:SS') For example: select to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') from dual; b. Comparing date columns -- Sometimes handy to skip the time portion when searching for dates. You can use the trunc command to strip the time off. This resets the time to midnight. For example: select to_char(trunc(sysdate), 'DD-MON-YYYY HH24:MI:SS') from dual;
PROMPT Trigger details on a table. select TABLE_NAME, COLUMN_NAME, trigger_name, trigger_type, TRIGGER_BODY from dba_TRIGGERS WHERE TABLE_NAME like '%&EnterTableName%';
Handy SQL for the Oracle Applications [ID 731190.1] Modified 16-SEP-2010 In this Document Goal Solution 1. Database 1.1 General Objects / Tables / Columns 1.2 Invalids / Compiling 1 .3 Bugs and Patching 1.4 Other Objects 1.5 Helpful SQL Syntax 2. Foundation (FND/ATG/AOL) 2.1 SQL related to Oracle Application Messages 2.2. Review Oracle Application Programs 2.3. Users and Responsibilities 2.4 Set Context / Initialize 3. Manufacturing 3.1 Manufacturing General 3.2. Setup 3.2.1. Inventory 3.3. Transactions 3.3.1. Inventory Transactions References Type HOWTO Status PUBLISHED
Applies to:
Oracle Inventory Management - Version: 11.5.9 and later [Release: 11.5 and later ] Information in this document applies to any platform. This document includes all versions of 11i and R12 and later
NOTE: A specific bug maybe fixed by multiple patches so it might be good to look for the bug number, instead of the patch number to see if that bug is fixed already on your system. Another way is to look at the file version mentioned in the patch and check if you have that version or higher.
1. Database 1.1 General Objects / Tables / Columns
PROMPT Find Object by Type, Name, Status select object_name, object_type, owner, status from dba_objects where upper(object_name) like upper('%&object_name%') and object_type like upper('%'|| NVL('&object_type', 'TABLE')|| '%') and status like upper('%'|| NVL('&Status', '%')|| '%') order by object_name, object_type; PROMPT Find table indexes, keys, columns select index_name,column_position,column_name from all_ind_columns where table_name = '&EnterTableName' order by index_name, column_position; PROMPT Find tables and columns that include a table and/or column name specified. select table_name, column_name from dba_tab_columns where column_name like NVL(UPPER('&COLUMN_NAME'), column_name) and table_name like NVL(UPPER('&TABLE_NAME'), table_name);
1 .3 Bugs and Patching
PROMPT Find specific package version select text from dba_source where name = upper( '&PackageName') and line < 3; PROMPT Find bugs already installed fixed in your system select bug_number from ad_bugs where bug_number ='&Bug_Number'; PROMPT select from where Find patches applied substr(patch_name,1,12) patch_num ad_applied_patches patch_name like '%&Patch_Number%';
1.4 Other Objects
PROMPT Query view details of a specific view. SELECT VIEW_NAME, TEXT FROM DBA_VIEWS WHERE VIEW_NAME = UPPER('&VIEW_NAME') and owner = 'APPS';
1.2 Invalids / Compiling
PROMPT Find Invalids
select object_name, object_type, owner, status from dba_objects where status = 'INVALID'; PROMPT Compile Packages select 'alter package '|| object_name || ' compile '|| decode(object_type, 'PACKAGE', '', 'PACKAGE BODY', 'body')|| ';' from dba_objects where staቤተ መጻሕፍቲ ባይዱus = 'INVALID' and object_type like 'PACK%';