Helpful Queries in Oracle Apps

/***********CODE TO DELETE XML TEMPLATE***************/

BEGIN
DBMS_OUTPUT.PUT_LINE('INSIDE BEGIN');
xdo_templates_pkg.DELETE_ROW (
X_APPLICATION_SHORT_NAME => 'XX',
X_TEMPLATE_CODE => 'XXRSDWIPSR-P'
) ;
DBMS_OUTPUT.PUT_LINE('DELETE SUCCESSFULL');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('EXCEPTION CAUGHT');
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;

/***********CODE TO DELETE XML DATA DEFINITION *******************/

BEGIN
DBMS_OUTPUT.PUT_LINE('INSIDE BEGIN');
XDO_DS_DEFINITIONS_PKG.DELETE_ROW (
X_APPLICATION_SHORT_NAME => 'XX',
X_DATA_SOURCE_CODE => 'XXRSDWIPSR-P'
);
DBMS_OUTPUT.PUT_LINE('DELETE SUCCESSFULL');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('EXCEPTION CAUGHT');
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;

/******************* DELETING TEMPLATES AND FILES ********************/

SELECT * FROM XDO_LOBS WHERE LOB_CODE = 'XXRSDWIPSR-P';

DELETE FROM XDO_LOBS
WHERE LOB_TYPE = 'TEMPLATE_SOURCE'
AND APPLICATION_SHORT_NAME = 'XX'
AND LOB_CODE = 'XXRSDWIPSR-P'
AND FILE_NAME = 'XX_PICK_SLIP_REPORT_PARENT.rtf'

DELETE FROM XDO_LOBS
WHERE LOB_TYPE = 'TEMPLATE'
AND APPLICATION_SHORT_NAME = 'XX'
AND LOB_CODE = 'XXRSDWIPSR-P'
AND FILE_NAME = 'XX_PICK_SLIP_REPORT_PARENT.xsl'

/******************* DELETING PROGRAM ********************/

SELECT * FROM FND_CONCURRENT_PROGRAMS_TL WHERE USER_CONCURRENT_PROGRAM_NAME = 'RRL - EFT (ICICI) Output Generation';

SELECT * FROM FND_CONCURRENT_PROGRAMS WHERE CONCURRENT_PROGRAM_ID = 67358;

SELECT * FROM FND_APPLICATION WHERE APPLICATION_ID = 20003;

SELECT OBJECT_NAME,OBJECT_TYPE FROM ALL_OBJECTS
WHERE OBJECT_NAME LIKE '%FND%APPLICATION%' AND OBJECT_TYPE IN ('TABLE')

BEGIN
DBMS_OUTPUT.PUT_LINE('INSIDE BEGIN');
FND_PROGRAM.DELETE_PROGRAM (
PROGRAM_SHORT_NAME => 'XXRSDWIPSR-P',
APPLICATION => 'XX'
);
DBMS_OUTPUT.PUT_LINE('DELETE SUCCESSFULL');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('EXCEPTION CAUGHT');
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;

/****************** DELETING EXECUTABLE **************/

SELECT * FROM FND_EXECUTABLES_TL WHERE USER_EXECUTABLE_NAME ='';

SELECT * FROM FND_EXECUTABLES WHERE EXECUTABLE_ID = 67358;

SELECT * FROM FND_APPLICATION WHERE APPLICATION_ID = 20003;

SELECT OBJECT_NAME,OBJECT_TYPE FROM ALL_OBJECTS
WHERE OBJECT_NAME LIKE '%FND%APPLICATION%' AND OBJECT_TYPE IN ('TABLE')

BEGIN
DBMS_OUTPUT.PUT_LINE('INSIDE BEGIN');
FND_PROGRAM.DELETE_EXECUTABLE (
EXECUTABLE_SHORT_NAME => 'XXRPGICICIEFT',
APPLICATION => 'XXRPG'
);
DBMS_OUTPUT.PUT_LINE('DELETE SUCCESSFULL');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('EXCEPTION CAUGHT');
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;

/************** ADDING LAYOUT TO A REQUEST FROM BACKEND ************/

ln_layout := fnd_request.add_layout (template_appl_name => 'SQLAP',
template_code => 'XXRPGICICIEFT',
template_language => 'en',
template_territory => 'US',
output_format => 'EXCEL');

/**************** SUBMITTING A REQUEST FROM BACKEND ************/

fnd_request.submit_request(application => 'SQLAP',
program => 'PROGRAM NAME
argument1 => 'argument1',
argument2 =>'argument2',
argument3 => 'argument3'
);

/************* FINDING REQUEST GROUP - CONCURRENT PROGRAM ***********/

SELECT fcpt.user_concurrent_program_name,frg.request_group_name
FROM fnd_request_groups frg,fnd_request_group_units frgu,fnd_concurrent_programs_tl fcpt
WHERE frg.request_group_id = frgu.request_group_id
AND fcpt.concurrent_program_id = frgu.request_unit_id

/************* FND_GLOBAL.APPS_INITIALIZE ****************/

select * from fnd_user where user_name = 'USER NAME'; --15647

SELECT * FROM FND_RESPONSIBILITY_TL WHERE RESPONSIBILITY_NAME = 'RESPONSIBILITY NAME'; --54204,660

FND_GLOBAL.APPS_INITIALIZE (FND_GLOBAL.USER_ID,FND_GLOBAL.RESP_ID,FND_GLOBAL.RESP_APPL_ID);

/********** TOTAL AMOUNT OF TIME FOR WAIT EVENT ******************/

select time_waited from v$system_event where event = 'PL/SQL lock timer';

/************ QUERY TO FIND COLUMN AND TABLE DESCRIPTION *******************/

SELECT fa.application_id
, fa.application_short_name
, fat.application_name
, table_name
, column_name
, ft.description table_description
, fc.description column_description
FROM fnd_tables ft
, fnd_columns fc
, fnd_application_tl fat
, fnd_application fa
WHERE ft.table_id = fc.table_id
AND fc.column_name = 'INVENTORY_ITEM_ID'
AND fat.application_id = ft.application_id
AND fat.LANGUAGE = USERENV ('LANG')
AND fa.application_id = fat.application_id

/*************** QUERY TO FIND CONCURRENT PROGRAM DETAILS ***************/

SELECT fcpl.user_concurrent_program_name
, fcp.concurrent_program_name
, par.column_seq_num
, par.end_user_column_name
, par.form_left_prompt prompt
, par.enabled_flag
, par.required_flag
, par.display_flag
, par.flex_value_set_id
, ffvs.flex_value_set_name
, flv.meaning default_type
, par.DEFAULT_VALUE
FROM fnd_concurrent_programs fcp
, fnd_concurrent_programs_tl fcpl
, fnd_descr_flex_col_usage_vl par
, fnd_flex_value_sets ffvs
, fnd_lookup_values flv
WHERE fcp.concurrent_program_id = fcpl.concurrent_program_id
AND fcpl.user_concurrent_program_name = 'FSD RSD Pick Slip Report'
AND fcpl.LANGUAGE = 'US'
AND par.descriptive_flexfield_name = '$SRS$.' || fcp.concurrent_program_name
AND ffvs.flex_value_set_id = par.flex_value_set_id
AND flv.lookup_type(+) = 'FLEX_DEFAULT_TYPE'
AND flv.lookup_code(+) = par.default_type
AND flv.LANGUAGE(+) = USERENV ('LANG')
ORDER BY par.column_seq_num

/*********** QUERY TO FIND LIST OF RESPONSIBILITIES ATTACHED TO A USER **********/

select fu.user_name, fr.responsibility_name, furg.START_DATE, furg.END_DATE
from fnd_user_resp_groups_direct furg, fnd_user fu, fnd_responsibility_tl fr
where fu.user_name = 'JOSPHIN'--:user_name
and furg.user_id = fu.user_id
and furg.responsibility_id = fr.responsibility_id
and fr.language = userenv('LANG')