7. Query to get Database port number:
select
listener_name, port db_port from FND_TNS_LISTENERS FTL, FND_TNS_LISTENER_PORTS
FTLP
where
ftl.listener_guid = ftlp.listener_guid
and
listener_name like '%_DB';
8. Query to get Database Host/SID:
select
HOST||'.'||domain HOST_NAME, VIRTUAL_IP||'.'||domain HOST_NAME1, INSTANCE_NAME
SERVICE_ID from v$instance ins, FND_NODES fn
where
upper(fn.host) = upper(ins.host_name)
and
support_db = 'Y';
9. Query to get Supplier bank details:
----query to
fetch AP supplier bank details with assignment level (Supplier/Site level)
SELECT
APS.VENDOR_ID,
VENDOR_NAME "Supplier Name",
IEP.supplier_site_id VENDOR_SITE_ID,
ASSA.VENDOR_SITE_CODE "Site Name",
bank.bank_name "bank name",
branch.bank_branch_name "Branch
Name",
ieb.bank_account_num "Account
Number",
DECODE(IEP.supplier_site_id, NULL,
'Supplier', 'Site') assignment_level
FROM
IBY_PMT_INSTR_USES_ALL IPI,
AP_SUPPLIERS APS,
AP_SUPPLIER_SITES_ALL ASSA,
iby_external_payees_all IEP,
iby_ext_bank_accounts ieb,
IBY_EXT_BANKS_V bank,
IBY_EXT_BANK_BRANCHES_V branch
WHERE
IPI.instrument_type = 'BANKACCOUNT'
AND
IPI.payment_function = 'PAYABLES_DISB'
AND
IPI.EXT_PMT_PARTY_ID = IEP.EXT_PAYEE_ID
AND
IEP.supplier_site_id = ASSA.VENDOR_SITE_ID(+)
AND
APS.VENDOR_ID =
NVL(ASSA.VENDOR_ID, APS.VENDOR_ID)
AND
bank.bank_party_id = ieb.bank_id
AND
branch.branch_party_id = ieb.branch_id
AND
IEP.PAYEE_PARTY_ID = APS.PARTY_ID
AND
IPI.instrument_id =
ieb.ext_bank_account_id;
10. Query to get locked session in ORACLE:
SELECT
VS.SID ||', '||VS.SERIAL# KILL_SESSION,
VS.USERNAME,
DBO.owner, DBO.object_name, DBO.object_type, vs.module, vs.machine,
VS.OSUSER,
VLO.ORACLE_USERNAME, FU.USER_NAME
LOCKING_FND_USER_NAME, fl.start_time locking_fnd_user_login_time,
vp.pid, vp.spid AS os_process, vs.status,
vs.saddr, vs.audsid, vs.process
FROM
V$SESSION VS,
V$PROCESS VP,
V$LOCKED_OBJECT VLO,
DBA_OBJECTS DBO,
FND_LOGINS FL,
fnd_user fu
WHERE
VS.PADDR = VP.ADDR
AND
VS.SID = VLO.SESSION_ID
AND
DBO.OBJECT_ID = VLO.OBJECT_ID
AND
vp.spid = fl.process_spid(+)
AND
vp.pid = fl.pid(+)
AND
FL.USER_ID = FU.USER_ID(+)
AND
DBO.OBJECT_NAME LIKE NVL('%'|| UPPER (:OBJECT_NAME)|| '%', DBO.OBJECT_NAME)
AND NVL
(VS.STATUS, 'XX') != 'KILLED';
11. Query to find all running concurrent requests
select request_id,
fcpt.user_concurrent_program_name,
completion_text,
actual_start_date,
actual_completion_date,
to_date((actual_completion_date - actual_start_date), 'HH:MM:SS') duration
from fnd_concurrent_requests fcr,
fnd_concurrent_programs fcp,
fnd_concurrent_programs_tl fcpt
where fcr.concurrent_program_id = fcp.concurrent_program_id
and fcp.concurrent_program_id = fcpt.concurrent_program_id
and fcr.actual_start_date > sysdate - 1
order by actual_completion_date - actual_start_date desc;
12.
QUERY to get the programs submitted by users
SELECT REQUEST_ID ,
REQUESTOR ,
PROGRAM ,
DECODE (phase_code, 'C', 'Completed', 'I', 'Inactive', 'P', 'Pending', 'R', 'Running') Phase,
DECODE (STATUS_CODE, 'A', 'Waiting', 'B', 'Resuming', 'C', 'Normal', 'D', 'Cancelled', 'E', 'Error', 'F', 'Scheduled', 'G', 'Warning', 'H', 'On Hold', 'I', 'Normal', 'M', 'No Manager', 'Q', 'Standby', 'R', 'Normal', 'S', 'Suspended', 'T', 'Terminating', 'U', 'Disabled', 'W', 'Paused', 'X', 'Terminated', 'Z', 'Waiting') status,
COMPLETION_TEXT ,
TO_CHAR(actual_start_date,'DD-MON-YYYY HH24:MI:SS') ACTUAL_START_DATE ,
TO_CHAR(actual_completion_date,'DD-MON-YYYY HH24:MI:SS') ACTUAL_COMPLETION_DATE ,
APPLICATIONS_SYSTEM_NAME
FROM apps.fnd_conc_req_summary_v req,
apps.FND_PRODUCT_GROUPS db
WHERE 1 =1
AND REQUESTOR = 'SYSADMIN' --- Pass the requestor USER_NAME
AND PROGRAM = 'Workflow Background Process' --- Pass the concurrent program name
AND TRUNC(actual_completion_date) >= TRUNC(SYSDATE-7)
ORDER BY request_id ASC;
13.
QUERY to get concurrent programs created or updated by users
SELECT * FROM (
SELECT
papp.application_name,
user_concurrent_program_name,
'Header Details' CONTEXT,
fcpv.creation_date,
(select user_name from fnd_user fu where user_id = fcpv.created_by) created_by,
fcpv.last_update_date,
(select user_name from fnd_user fu where user_id = fcpv.last_updated_by) updated_by
FROM apps.fnd_application_vl papp,
apps.fnd_concurrent_programs_vl fcpv
WHERE papp.application_id = fcpv.application_id
UNION
SELECT papp.application_name,
user_concurrent_program_name,
'Parameter Details: SEQ:'
||COLUMN_SEQ_NUM CONTEXT,
fcpv.creation_date,
(select user_name from fnd_user fu where user_id = fcpv.created_by) created_by,
fcpv.last_update_date,
(select user_name from fnd_user fu where user_id = fcpv.last_updated_by) updated_by
FROM apps.fnd_application_vl papp,
apps.fnd_concurrent_programs_vl fcpv,
apps.FND_DESCR_FLEX_COLUMN_USAGES fdc
WHERE papp.application_id = fcpv.application_id
AND fdc.descriptive_flexfield_name = '$SRS$.'||fcpv.concurrent_program_name
AND fdc.application_id = fcpv.application_id)
WHERE USER_CONCURRENT_PROGRAM_NAME = '1096 - Annual Summary and Transmittal of U.S. Information Returns' --- Pass the concurrent program name
order by 2;
