Oracle apps standard query

To Find invalid objects:
select owner, object_name, object_type,status from all_objects
where status != 'VALID' order by owner, object_type, object_name;

Validate username/password combination:
select fnd_web_sec.validate_login('AK185109','ak185109') from dual ;

If it return N then look the error message by this query
select fnd_message.get from dual;

Find the Instance:
select NODE_NAME, NODE_ID , SERVER_ID , SERVER_ADDRESS from FND_NODES;

Validate the ICX_PARAMETERS table:
select * from icx_parameters;

Find the Responsiblity :
select responsibility_id,responsibility_key,responsibility_name,description from fnd_responsibility_vl where responsibility_key = 'SERVICE'

Find the Menu, Function:
SELECT * fnd_responsibility_vl a,
fnd_menu_entries_vl b,
fnd_form_functions_vl c
where a.responsibility_id=20638
and a.menu_id=b.menu_id
and b.function_id=c.function_id

Pl/SQL Debugging/Logging method

What will you do If you want to debug your pl/sql code? You have to create you own debugging technique, but Oracle Applications module used its own debugging technique.

So We can also use the same.

Please read the below question and answer, and Use this If it is useful to you.
What is the use of FND Debug Log?
1. It helps you pinpoint the cause of error in standard Oracle Code, by making debug messages to appear in a centralized table named FND_LOG_MESSAGES.
2. You can design and build your custom extensions in a manner that can easily be debugged. This can be done by calling Oracle delivered API’s in your custom code.

Where is the debug message stored, once the logging is turned on?
Debug messages are stored in a table called FND_LOG_MESSAGES
A program written in any technology, either form, or report, or pl/sql or java concurrent program or OAF…all their debug messages will be stored in fnd_log_messages.

How to debug the issue being faced in Oracle Application?
Step 1 Set the following profile options at your user level(your fnd_user)
FND: Debug Log Level
Following possible values are available, but I suggest you set this to "Statement" level when debugging code.
LEVEL_UNEXPECTED : Internal Level Id is 6
LEVEL_ERROR : Internal Level Id is 5
LEVEL_EXCEPTION : Internal Level Id is 4
LEVEL_EVENT : Internal Level Id is 3
LEVEL_PROCEDURE : Internal Level Id is 2
LEVEL_STATEMENT : Internal Level Id is 1
FND: Debug Log Enabled
Set this profile to Yes
FND: Debug Log Module
Set this to %
Step 2
Login to the application and reproduce the problem.
Step 3
SELECT * FROM fnd_log_messages WHERE user_id = 209122 /*your FND_USER user_id here*/
AND TIMESTAMP > SYSDATE - .3
ORDER BY log_sequence DESC /*note the order by clause here*/
The result of this select statement will provide the list of all the debug messages, on top will appear the most recent debug messages..
Why should I setup the module name to %, in profile option?
You can set this to po%, if you know for sure that the error was caused by code written in po module. However po code might be internally calling hr code which might inturn be calling fnd code.
Hence it’s best to set this profile value as %.
You may also use comma delimited values i.e po%,hr%,fnd%
Why must I bother debugging Oracle's Standard code when I can quickly raise a tar.
If the issue is with Standard Oracle Code, first thing you must do is to search into Metalink. However having the debug information on error helps your searching ability further. Uploading the debug messages upfront during Tar creation will also help Oracle speedily understand and fix your issues.
Why to set the profile option to statement level?
This profile option has following main levels.-
Error
Warning
Procedure
Statement
I like setting this to "Statement" level as it extracts debug messages at all levels, in one glance. You can latter filter those debug messages by using below SQL for example
select * from fnd_log_messages where user_id = 111 and LOG_LEVEL =5
What if the piece of code causing the error is not appearing in fnd_log_messages?
This is very much possible. The fnd_log_messages might have helped you get close to the culprit piece of code , but may not be able to pinpoint the error as there may not be enough debug messages implanted by Oracle.
You can do one of the below:-
A. Run the database sql trace for the session with bind variables and see the last meaningful SQL statement in the raw trace file.
Please note that PL/SQL statements will not appear in trace, only the SQL Statements will appear, hence you may consider option (b) below
B. Add your own debug messages to the pl/sql code that was delivered by oracle, which you suspect is causing problem. This is a temporary change, and must only be done on development environment, NEVER DO THIS CHANGE ON PRODUCTION.
The size of table FND_LOG_MESSAGES will keep on increasing?
You can run concurrent program "Purge Debug Log and System Alerts".
I have written a pl/sql concurrent process to interface Purchase Orders from 3rd Party System. How will add debug messages?
fnd_log.STRING(log_level => fnd_log.level_statement ,module => 'xxpo.packagename.procedurename' ,message => 'debug message here');
Will the above debug command create an entry into fnd_log_messages ?
Debug records will be created in fnd_log_messages if and only if you run the interface program after setting the profile options as suggested above.
What if a rollback occurs due to unhandled exception. Will the inserts done to fnd_log_messages be lost?
fnd_log.string eventually calls procedure FND_LOG.STRING_UNCHECKED_INTERNAL2. This procedure uses pragma AUTONOMOUS_TRANSACTION with a commit.
Hence your debug messages will not be lost despite a rollback in parent session.
What if an exception is incurred within the Debug Logging API itself?
Oracle takes well care of this by handling the WHEN OTHERS exception.
It is evident from below Oracle Code for debug API
PROCEDURE STRING(LOG_LEVEL IN NUMBER,
MODULE IN VARCHAR2,
MESSAGE IN VARCHAR2) is
begin
/* Short circuit if logging not turned on at this level */
if (LOG_LEVEL < log_level =""> LOG_LEVEL,
MODULE => MODULE,
MESSAGE_TEXT => MESSAGE);
end if;
exception
when others then
NULL; /* supress the exception */
end;

Helpful layer in oracle application

When developing with the Oracle eBusiness Suite, there are situations where Developers can use "hooks" in standard Applications code to insert or redirect to custom code. Oracle Applications provide some helpful layers of abstraction to make life easier for customization. Where possible it is nice for this to be done using a supported method. If a true blue customization is required, then its best to try to preserve the existing code so that any future upgrades are more likely to break your custom code by reverting to standard code, plus if you use a "hook" to independent code it is easier to maintain and upgrade the custom code. I regularly use a "copy and modify" approach to Apps Development where I use hooks to call the new code.

• Concurrent Program Executable. Where an concurrent program is automatically submitted from a form or another concurrent process, e.g. "Print Pack Slip" in Oracle Shipping actions, as long as parameter requirements are the same, then a quick an easy method to code your own report is:
  1).Create a new executable registered under your custom application, e.g. XMODS_WSHRDPAK
  2).Query the called concurrent program and update the executable to your custom executable,e.g. Query WSHRDPAK and replace executable with XMODS_WSHRDPAK
• Personalization - Forms and Framework (OAF) plus CUSTOM.pll. Forms PL/SQL Library. Personalization has provided functionality to cover a lot of the customizations traditionally coded to CUSTOM.pll, but both Personalization and CUSTOM.pll provide a number of hooks into the front end logic.
• Database Triggers. Triggers on tables used carefully can provide hooks where all other methods don't dare to tread. Of course watch out for the exception and try to avoid putting triggers on fnd_concurrent_requests!
• Menu. Provides a prominent method to call new Forms, while potentially hiding behind the same prompt, Reports, Discoverer Workbooks, external links/URLs, etc. Gives the ability to save in the "Favourites" list. If you need to customize a standard form, copy it and create a new menu entry where possible.
• Unix Softlink. An alternative to the concurrent program executable hook.
  1). Backup an existing standard Oracle file.
  2). Remove the existing standard Oracle file.
  3). Replace with a softlink to a file under you custom application code "top" directory
• Workflow. Workflow customizations are "allowed" and a method I use here is to take a standard function call in workflow, copy and modify the underlying package, then change the function call in workflow to the custom function. E.g. AP Remittance Advice workflow (APPEWF) has "Get Check Info" function calling AP_PAYMENT_EVENT_WF_PKG.get_check_info. Copy AP_PAYMENT_EVENT_WF_PKG to XMODS_AP_PAYMENT_EVENT_WF_PKG and change function in workflow function "Get Check Info"
• Printer Driver. Whenever concurrent request file post processing is required, e.g. to FTP or email a file - printer drivers provide an excellent way to perform post processing. Create a new printer driver with the appropriate command. Remember to restart the concurrent manager to pickup updates to print drivers.
• Business Events. Not a widely used mechanism, but provides supported hooks into key events such as Payment Confirmations (AP Payment event).