SQL to find which query is running while in the concurrent request

SELECT vst.sql_text, SYSDATE, fcr.request_id, fcp.concurrent_program_name,
fcr.oracle_process_id traceid, fcr.requested_start_date,
fcr.actual_start_date, fcr.actual_completion_date,
fcr.status_code status,
ROUND ( ( NVL (fcr.actual_completion_date, SYSDATE)
- fcr.actual_start_date
)
* 24
* 60,
2
) run_time,
vst.sql_text, vs.SID, vs.serial#, vs.status, fcr.parent_request_id,
fcr.os_process_id
FROM v$sqlarea vst,
v$session vs,
apps.fnd_concurrent_programs fcp,
apps.fnd_concurrent_requests fcr
WHERE vst.address(+) = vs.sql_address
AND vs.process(+) = fcr.os_process_id
AND fcp.concurrent_program_id = fcr.concurrent_program_id
AND fcr.request_id = --Request ID--;

Concurrent Program Specific SQL Queries

To fetch responsibility assigned to a concurrent program
===========================================================
select resp.responsibility_name
from fnd_responsibility_vl resp, fnd_request_group_units rgu, fnd_concurrent_programs_vl prog
where resp.request_group_id = rgu.request_group_id
AND rgu.request_unit_id = prog.concurrent_program_id
AND rgu.unit_application_id = prog.application_id
AND prog.user_concurrent_program_name = ;


To fetch parameters assigned to a concurrent program
=====================================================

select cols.end_user_column_name,
cols.form_left_prompt,
fvs.flex_value_set_name,
cols.default_value
from fnd_descr_flex_col_usage_vl cols,
fnd_flex_value_sets fvs,
fnd_concurrent_programs_vl prog
where cols.flex_value_set_id = fvs.flex_value_set_id
AND cols.descriptive_flexfield_name = '$SRS$.' || prog.concurrent_program_name
And prog.user_concurrent_program_name like ;


To fetch details of executables and concurrent programs
===========================================================

select
prog.user_concurrent_program_name "program name",
prog.concurrent_program_name "program short name",
appl.application_name "program application name",
prog.description "program description",
exe.executable_name "executable name",
exe.execution_file_name "executable file name",
exe.description "executable description",
decode( exe.execution_method_code, 'I', 'PLSQL Stored Procedure', 'P', 'Report', 'L', 'SQL Loader', exe.execution_method_code) "execution method"
from
fnd_executables exe,
fnd_application_tl appl,
fnd_concurrent_programs_vl prog
where prog.application_id = appl.application_id
AND exe.executable_id = prog.executable_id
AND prog.user_concurrent_program_name =
AND prog.concurrent_program_name =
AND exe.execution_file_name =

Find a concurrent program's trace file

SELECT
req.request_id
,req.logfile_node_name node
,req.oracle_Process_id
,req.enable_trace
,dest.VALUE||'/'||LOWER(dbnm.VALUE)||'_ora_'||oracle_process_id||'.trc' trace_filename
,prog.user_concurrent_program_name
,execname.execution_file_name
,execname.subroutine_name
,phase_code
,status_code
,ses.SID
,ses.serial#
,ses.module
,ses.machine
FROM
fnd_concurrent_requests req
,v$session ses
,v$process proc
,v$parameter dest
,v$parameter dbnm
,fnd_concurrent_programs_vl prog
,fnd_executables execname
WHERE 1=1
AND req.request_id = &request
AND req.oracle_process_id=proc.spid(+)
AND proc.addr = ses.paddr(+)
AND dest.NAME='user_dump_dest'
AND dbnm.NAME='db_name'
AND req.concurrent_program_id = prog.concurrent_program_id
AND req.program_application_id = prog.application_id
AND prog.application_id = execname.application_id
AND prog.executable_id=execname.executable_id

How to make synonym status from invalid to valid

ALTER SYNONYM <> COMPILE;

Address Formatting using HZ_LOCATIONS.LOCATION_ID

HZ_FORMAT_PUB.format_address (
-- input parameters
p_location_id IN NUMBER,
p_style_code IN VARCHAR2,
p_style_format_code IN VARCHAR2,
p_line_break IN VARCHAR2,
p_space_replace IN VARCHAR2,
-- optional context parameters
p_to_language_code IN VARCHAR2,
p_country_name_lang IN VARCHAR2,
p_from_territory_code IN VARCHAR2,
-- output parameters
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
x_formatted_address OUT NOCOPY VARCHAR2,
x_formatted_lines_cnt OUT NOCOPY NUMBER,
x_formatted_address_tbl OUT NOCOPY string_tbl_type
);
Pass LOCATION_ID to this procedure and it returns formatted address through OUT parameter X_FORMATTED_ADDRESS. You may also pass line break character CHR(10) so that different address lines are returned with carriage return at appropriate places.

To activate audit trail in Oracle for standard tables

DECLARE l_app_id NUMBER; l_fnd_audit_groups NUMBER; l_table_id1 NUMBER; l_table_id2 NUMBER; l_col_ven_id NUMBER; l_col_inv_amt_lmt NUMBER; l_col_ven_nm NUMBER; l_col_bnk_uses_id NUMBER; l_col_bnk_id NUMBER; l_col_bnk_vndr NUMBER; l_col_bnk_site NUMBER; l_col_bnk_org NUMBER; l_schema_id NUMBER; l_oracle_id NUMBER; l_cnt_id NUMBER; l_audit_group_app_id NUMBER; l_col_ven_id1 NUMBER; l_col_ven_id2 NUMBER; l_col_ven_id3 NUMBER; l_num_request_id NUMBER; l_num_user_id NUMBER; l_num_appl_id NUMBER; l_num_resp_id NUMBER; BEGIN/*Enable audit for PO user updating FND_AUDIT_SCHEMAS table for PO username. */
SELECT DECODE(install_group_num, 0, 900, 899 + NVL(install_group_num,1) ) INTO l_schema_id FROM fnd_oracle_userid WHERE oracle_username = '<>';
SELECT oracle_id INTO l_oracle_id FROM fnd_oracle_userid WHERE oracle_username = '<>';
SELECT application_id INTO l_app_id FROM fnd_application_vl WHERE application_short_name = '<>';
SELECT application_id INTO l_audit_group_app_id FROM fnd_application_vl WHERE application_short_name = '<>';
SELECT COUNT(oracle_id) INTO l_cnt_id FROM fnd_audit_schemas WHERE oracle_id = l_oracle_id;
IF l_cnt_id <> 0 THENUPDATE fnd_audit_schemas SET state = 'R',last_update_date = SYSDATE,last_updated_by = -1,last_update_login = -1WHERE oracle_id = l_oracle_id ;
DBMS_OUTPUT.PUT_LINE('No Of Records Updated in fnd_audit_schemas : ' SQL%ROWCOUNT ); ELSE INSERT INTO fnd_audit_schemas(schema_id,oracle_id,application_id, state,last_update_date,last_updated_by,last_update_login,created_by,creation_date) VALUES (l_schema_id,l_oracle_id,l_app_id,'R',SYSDATE,-1,-1,-1,SYSDATE);DBMS_OUTPUT.PUT_LINE('No Of Records Inserted in fnd_audit_schemas : ' SQL%ROWCOUNT ); END IF; SELECT audit_group_id INTO l_fnd_audit_groups FROM fnd_audit_groups WHERE group_name = 'Test Audit';
UPDATE fnd_audit_groups set STATE = 'R' where group_name = 'Test Audit';
/*Getting table_id for PO_VENDOR_SITES_ALL table*/ SELECT table_id INTO l_table_id1 FROM fnd_tables WHERE table_name = '<>' AND application_id = l_app_id;
SELECT table_id INTO l_table_id2 FROM fnd_tables WHERE table_name = '<>' AND application_id = l_audit_group_app_id;
/*Insert records into FND_AUDIT_TABLES table*/ INSERT INTO fnd_audit_tables(audit_group_id, audit_group_app_id, table_app_id, table_id, state, last_update_date, last_updated_by, last_update_login, created_by, creation_date) VALUES (l_fnd_audit_groups, l_audit_group_app_id, l_app_id, l_table_id1, 'R', SYSDATE, -1, -1, -1, SYSDATE); DBMS_OUTPUT.put_line ('No of records Inserted into FND_AUDIT_TABLES table : ' SQL%ROWCOUNT); /*Selecting column ID for columns */
SELECT column_id INTO l_col_ven_id3 FROM fnd_columns WHERE column_name = 'VENDOR_SITE_ID' AND application_id = l_app_id AND table_id = l_table_id1;
SELECT column_id INTO l_col_ven_id1 FROM fnd_columns WHERE column_name = 'PAYMENT_METHOD_LOOKUP_CODE' AND application_id = l_app_id AND table_id = l_table_id1;
SELECT column_id INTO l_col_ven_id2 FROM fnd_columns WHERE column_name = 'PRIMARY_FLAG' AND application_id = l_audit_group_app_id AND table_id = l_table_id2 ;
/*Insert records into FND_AUDIT_COLUMNS table for AP_BANK_ACCOUNTS_ALL*/
INSERT INTO fnd_audit_columns(table_app_id, table_id, column_id, state, sequence_id, schema_id, last_update_date, last_updated_by, last_update_login,created_by, creation_date) VALUES (l_app_id, l_table_id1, l_col_ven_id3, 'P', -1, -1, SYSDATE, -1, -1, -1, SYSDATE);
INSERT INTO fnd_audit_columns(table_app_id, table_id, column_id, state, sequence_id, schema_id, last_update_date, last_updated_by, last_update_login,created_by, creation_date) VALUES (l_app_id, l_table_id1, l_col_ven_id1, 'P', -1, -1, SYSDATE, -1, -1, -1, SYSDATE);
INSERT INTO fnd_audit_columns(table_app_id, table_id, column_id, state, sequence_id, schema_id, last_update_date, last_updated_by, last_update_login,created_by, creation_date) VALUES (l_audit_group_app_id, l_table_id2, l_col_ven_id2, 'P', -1, -1, SYSDATE, -1, -1, -1, SYSDATE);
BEGINSELECT application_id,responsibility_idINTO l_num_appl_id,l_num_resp_idFROM fnd_responsibility_vlWHERE responsibility_name = 'System Administrator';
EXCEPTIONWHEN others THENDBMS_OUTPUT.PUT_LINE('Error when fetching the Responsibility details :' SQLERRM);END;
BEGINSELECT user_id INTO l_num_user_idFROM fnd_userWHERE user_name = 'FIN_ADMIN';EXCEPTIONWHEN others THENDBMS_OUTPUT.PUT_LINE('Error when fetching the user details :' SQLERRM);END;
fnd_global.apps_initialize(user_id => l_num_user_id,resp_id => l_num_resp_id,resp_appl_id => l_num_appl_id,security_group_id => 0);
l_num_request_id := Fnd_Request.submit_request (application => 'FND',program => 'FNDATUPD');
COMMIT;EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line (SQLCODE '-' SQLERRM); ROLLBACK;END;/