SQL Useful tips.

FND_GLOBAL variables.
Request ID => fnd_global.conc_request_id
Program Application ID => fnd_global.prog_appl_id
Program Id => fnd_global.conc_program_id
Last Updated By / Created_By => fnd_global.user_id
Last Update Login => fnd_global.login_id
Organization ID => fnd_global.org_id

Script to run Gather Table Statistics

dbms_stats.gather_table_stats('OSM'
,'AS_TERR_RESOURCES_TMP'
,estimate_percent => 10
,degree => 8
,granularity => 'GLOBAL'
,cascade =>TRUE) ;


Query to get installed Oracle Products.

SELECT PRODUCT_GROUP_ID
,PRODUCT_GROUP_NAME
,RELEASE_NAME
,PRODUCT_GROUP_TYPE
,MULTI_ORG_FLAG
,MULTI_LINGUAL_FLAG
,MULTI_CURRENCY_FLAG
,APPLICATIONS_SYSTEM_NAME
FROM FND_PRODUCT_GROUPS;

SQL Query to find the instance patch set level:
select patch_level from fnd_product_installations where patch_level like '%QOT%'

select *
from fnd_product_installations a
,fnd_application_tl b
where a.application_id = b.application_id
and a.application_id = 170

Question: How do you know if a specific Oracle patch has been applied in apps to your environment.
Answer: Use table ad_bugs, in which column bug_number is the patch number.
SELECT bug_number
,to_char(creation_date, 'DD-MON-YYYY HH24:MI:SS') dated
FROM apps.ad_bugs
WHERE bug_number = TRIM('&bug_number') ;

Ref Cursor::

Reffering cursor declared in another package is called Ref Cursor.

Example:
DECLARE
v_cursor com_dir_details_report_pkg.repcode_ref_cur;
l_repcode varchar2(100);

BEGIN
com_dir_details_report_pkg.get_repcodes(
'12'
, '2005',16,v_cursor);
LOOP
FETCH v_cursor
INTO l_repcode;
EXIT WHEN v_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(l_repcode);
END LOOP;
CLOSE v_cursor;
END;