Version related scripts from database

How to find out version of a package.

select text from dba_source
where line=2
and name=’AP_IMPORT_INVOICES_PKG’;


How to find out Package Header.
select name,text from dba_source where text like ‘%Header: %’
and owner = ‘APPS’ and name = ‘INVALID_OBJECT_NAME’;

How to check whether the product is install,shared and Not installed in Apps.

select t.application_name
, t.application_id
, i.patch_level
, decode(i.status,’I',’Fully Installed’,
‘N’,'Not Installed’,'S’,'Shared’,'Undetermined’) status
from fnd_product_installations i
, fnd_application_vl t
where i.application_id = t.application_id
order by t.application_id;

/* To find the latest application version */

select ARU_RELEASE_NAME||’.'||MINOR_VERSION||’.'||TAPE_VERSION version, START_DATE_ACTIVE updated,ROW_SOURCE_COMMENTS “how it is done”, BASE_RELEASE_FLAG “Base version” FROM AD_RELEASES where END_DATE_ACTIVE IS NULL

/* to find the base application version */

select ARU_RELEASE_NAME||’.'||MINOR_VERSION||’.'||TAPE_VERSION version, START_DATE_ACTIVE when updated, ROW_SOURCE_COMMENTS “how it is done” from AD_RELEASES where BASE_RELEASE_FLAG = ‘Y’

/* To find all available application version */

select ARU_RELEASE_NAME||’.'||MINOR_VERSION||’.'||TAPE_VERSION version, START_DATE_ACTIVE when updated, END_DATE_ACTIVE “when lasted”, CASE WHEN BASE_RELEASE_FLAG = ‘Y’ Then ‘BASE VERSION’ ELSE ‘Upgrade’ END “BASE/UPGRADE”, ROW_SOURCE_COMMENTS “how it is done” from AD_RELEASES


Workflow version embedded in 11i
************************
Run following SQL from apps user ;
SQL>select TEXT from WF_RESOURCES where NAME='WF_VERSION';

You should see output like
TEXT
-----------------------
2.6.0
Which means you are on Workflow Version 2.6.0

You can also use script wfver.sql in FND_TOP/sql to find version of workflow in Apps.

To check if if your database contains an Identity Management Metadata Repository
enter the following query (a non-null result means you should perform the step):
SQL> select USERNAME from dba_users where upper(USERNAME) like '%ODS%';


An eg of import command:
SQL> drop user DBO cascade;
User dropped
imp system/xxxxxx FILE=exp_tw01_042109.dmp log=imp_DBO_TW02.log full=y


How to backup the defination of a View before droping a view or index.

select dbms_metadata.get_ddl(’VIEW’,'RG_View’,'APPS’) from dual;

To get defination of index
SQL> select dbms_metadata.GET_DDL('INDEX','FND_LOBS_CTX','APPLSYS') from dual;

DBMS_METADATA.GET_DDL('INDEX','FND_LOBS_CTX','APPLSYS')
--------------------------------------------------------------------------------

CREATE INDEX ""APPLSYS"".""FND_LOBS_CTX"" ON ""APPLSYS"".""FND_LOBS"" (""FILE_DATA"")


To display options that are installed (the value column is ‘FALSE’ if the
option is not installed)
SELECT parameter, value FROM v$option;


To know database version query
seelct version from v$instance;
select banner from v$version;
select comp_name,version,status from dba_registry;