This query will shows concurrent program processing time

SELECT f.request_id ,

pt.user_concurrent_program_name user_concurrent_program_name

, f.actual_start_date actual_start_date

, f.actual_completion_date actual_completion_date,

floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)

|| ‘ HOURS ‘ ||

floor((((f.actual_completion_date-f.actual_start_date)*24*60*60) -

floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600)/60)

|| ‘ MINUTES ‘ ||

round((((f.actual_completion_date-f.actual_start_date)*24*60*60) -

floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600 -

(floor((((f.actual_completion_date-f.actual_start_date)*24*60*60) -

floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600)/60)*60) ))

|| ‘ SECS ‘ time_difference,

DECODE(p.concurrent_program_name,’ALECDC’,p.concurrent_program_name||’['||f.descriptio

n||']‘,p.concurrent_program_name) concurrent_program_name

, decode(f.phase_code,’R',’Running’,'C’,'Complete’,f.phase_code) Phase

, f.status_code

FROM apps.fnd_concurrent_programs p

, apps.fnd_concurrent_programs_tl pt

, apps.fnd_concurrent_requests f

WHERE f.concurrent_program_id = p.concurrent_program_id

and f.program_application_id = p.application_id

and f.concurrent_program_id = pt.concurrent_program_id

and f.program_application_id = pt.application_id

AND pt.language = USERENV(‘Lang’)

and f.actual_start_date is not null

ORDER by f.actual_completion_date-f.actual_start_date desc;

Check Current Applied Patch

SELECT patch_name, patch_type,

maint_pack_level,

creation_date

FROM applsys.ad_applied_patches

ORDER BY creation_date DESC

Query used to view the patch level status of all modules

SELECT a.application_name,

DECODE (b.status, ‘I’, ‘Installed’, ‘S’, ‘Shared’, ‘N/A’) status,

patch_level

FROM apps.fnd_application_vl a,

apps.fnd_product_installations b

WHERE a.application_id = b.application_id;

Query used for audit point of view i.e. when a profile is changed and by which user

SELECT t.user_profile_option_name,

profile_option_value,

v.creation_date,

v.last_update_date,

v.creation_date – v.last_update_date “Change Date”,

(SELECT UNIQUE user_name

FROM fnd_user

WHERE user_id = v.created_by) “Created By”,

(SELECT user_name

FROM fnd_user

WHERE user_id = v.last_updated_by) “Last Update By”

FROM fnd_profile_options o,

fnd_profile_option_values v,

fnd_profile_options_tl t

WHERE o.profile_option_id = v.profile_option_id

AND o.application_id = v.application_id

AND start_date_active <= SYSDATE

AND NVL (end_date_active, SYSDATE) >= SYSDATE

AND o.profile_option_name = t.profile_option_name

AND level_id =  :p_value

AND t.LANGUAGE IN (SELECT language_code

FROM fnd_languages

WHERE installed_flag = ‘B’

UNION

SELECT nls_language

FROM fnd_languages

WHERE installed_flag = ‘B’)

ORDER BY user_profile_option_name;

:p_value is (10001 -> site level, 10002 -> application level, 10003 -> responsibility level, 10004 – user level)

Script to find Oracle API’s for any module

select substr(a.OWNER,1,20)
, substr(a.NAME,1,30)
, substr(a.TYPE,1,20)
, substr(u.status,1,10) Stat
, u.last_ddl_time
, substr(text,1,80) Description
from dba_source a, dba_objects u
WHERE 2=2
and u.object_name = a.name
and a.text like ‘%Header%’
and a.type = u.object_type
and a.name like ‘AR_%API%’ –- Checking for AR Related APIs
order by
a.owner, a.name

Some of the important Join conditions between Oracle apps modules

GL AND AP    

GL_CODE_COMBINATIONS    AP_INVOICES_ALL

code_combination_id = acct_pay_code_combination_id

GL_CODE_COMBINATIONS    AP_INVOICES_DISTRIBUTIONS_ALL

code_combination_id = dist_code_combination_id

GL_SETS_OF_BOOKS AP_INVOICES_ALL

set_of_books_id = set_of_books_id

GL AND AR

GL_CODE_COMBINATIONS    RA_CUST_TRX_LINE__GL_DIST_ALL

code_combination_id = code_combination_id

GL AND INV

GL_CODE_COMBINATIONS    MTL_SYSTEM_ITEMS_B

code_combination_id = cost_of_sales_account

GL AND PO

GL_CODE_COMBINATIONS    PO_DISTRIBUTIONS_ALL

code_combination_id = code_combination_id

PO AND AP

PO_DISTRIBUTIONS_ALL      AP_INVOICE_DISTRIBUTIONS_ALL

Po_distribution_id = po_distribution_id

PO_VENDORS           AP_INVOICES_ALL

vendor_id = vendor_id

PO AND SHIPMENTS

PO_HEADERS_ALL     RCV_TRANSACTIONS

Po_header_id = po_header_id

PO_DISTRIBUTIONS_ALL      RCV_TRANSACTIONS

Po_distribution_id = po_distribution_id

SHIPMENTS AND AP INVOICE

RCV_TRANSACTIONS           AP_INVOICE_DISTRIBUTIONS_ALL

RCV_TRANSACTION_ID = RCV_TRANSACTION_ID

PO AND  INV

PO_REQUISITION_LINES_ALL MTL_SYSTEM_ITEMS_B

item_id = inventory_item_id

org_id = organization_id

PO AND HRMS

PO_HEADERS_ALL     HR_EMPLOYEES

Agent_id = employee_id

PO AND REQUISITION

PO_DISTRIBUTIONS_ALL      PO_REQ_DISTRIBUTIONS_ALL

req_distribution_id = distribution_id

SHIPMENTS AND INV

RCV_TRANSACTIONS           MTL_SYSTEM_ITEMS_B

Organization_id         =        organization_id

INV AND HRMS

MTL_SYSTEM_ITEMS_B       HR_EMPLOYEES

buyer_id        =        employee_id

OM  AND  AR

OE_ORDER_HEADERS_ALL              RA_CUSTOMER_TRX_LINES_ALL

TO_CHAR( Order_number)    =        interface_line_attribute1

OE_ORDER_LINES_ALL                   RA_CUSTOMER_TRX_LINES_ALL

TO_CHAR(Line_id)     =        interface_line_attribute6  

OE_ORDER_LINES_ALL                   RA_CUSTOMER_TRX_LINES_ALL

reference_customer_trx_line_id       =        customer_trx_line_id

OM AND SHIPPING

OE_ORDER_HEADERS_ALL               WSH_DELIVARY_DETAILS

HEADER_ID     =        SOURCE_HEADER_ID

OE_ORDER_HEADERS_ALL              WSH_DELIVARY_DETAILS

LINE_ID         =        SOURCE_LINE_ID

AP AND AR (BANKS)

AR_CASH_RECEIPTS_ALL               AP_BANK_ACCOUNTS

REMITTANCE_BANK_ACCOUNT_ID    =        ABA.BANK_ACCOUNT_ID

AP AND AR

HZ_PARTIES                      AP_INVOICES_ALL

PARTY_ID      =        PARTY_ID

OM AND CRM

OE_ORDER_LINES_ALL         CSI_ITEM_INSTANCES(Install Base)

LINE_ID         =        LAST_OE_ORDER_LINE_ID