SET USERENV ('CLIENT_INFO')

exec dbms_application_info.set_client_info(<org_id>);

Concurrent program trace in Oracle Applications

Below query provides trace, session details of concurrent program in Oracle applications.

Enable trace flag of your concurrent program before running the program in order to get trace location from query below.

.


 SELECT 'Request id: ' || request_id
      , 'Trace id: ' || oracle_process_id
      , 'Trace Flag: ' || req.enable_trace
      ,    'Trace Name: '
        || dest.VALUE
        || '/'
        || LOWER (dbnm.VALUE)
        || '_ora_'
        || oracle_process_id
        || '.trc'
      , 'Prog. Name: ' || prog.user_concurrent_program_name
      ,    'File Name: '
        || execname.execution_file_name
        || execname.subroutine_name
      ,    'Status : '
        || DECODE (phase_code, 'R', 'Running')
        || '-'
        || DECODE (status_code, 'R', 'Normal')
      , 'SID Serial: ' || ses.SID || ',' || ses.serial#
      , 'Module : ' || ses.module
  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 req.request_id = &request_id -----put your request id here
   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 -- Remove comment if multiple rows returned
   AND prog.executable_id = execname.executable_id;

Initialize Apps in Oracle Applications

Apps initialize is used to set applications context in standalone sessions that were not initialized.This procedure sets up global variables and profile values in a database session. You can use this routine in independent programs like pl-sql, java in order to adopt apps related properties.

begin
 fnd_global.apps_initialize(&user_id,&resp_id,&resp_appl_id);
end;

Use below sql's to derive values used in apps_initialize.

SELECT fnd_profile.value (‘USER_ID’) FROM dual;
SELECT fnd_profile.value (‘RESP_ID’) FROM dual;
SELECT fnd_profile.value (‘RESP_APPL_ID’) FROM dual;

 or use below statement in your apps routine.

 
 fnd_global.apps_initialize(fnd_profile.value (‘USER_ID’) ,fnd_profile.value (‘RESP_ID’),fnd_profile.value (‘RESP_APPL_ID’) );
 

fnd_profile.value  cannot be accessed from SQL. These values will get initialize / passed to a routine only from Oracle Apps.

If a stand alone program needed to be initialized, hard code values in apps_initialize.

Corresponding values can be found using below SQL

user_id 

select user_id from fnd_user where user_name = <'user_name'>;

resp_id, resp_appl_id

 

select responsibility_id,application_id from fnd_responsibility_tl where responsibility_name = <'Your Resp Name'>

Initialze ORG specific values in Oracle Reports

Use USER_EXIT('FND SRWINIT') in before report trigger along with 2 user parameters p_conc_request_id and p_org_id to initialize org specific values and data.

FRM-40301: Query caused no records to be retrieved fix

Step 1
Check if your data block query is correct. To do so, navigate to Help --> Diagnostics --> Examine on the toolbar. When prompted enter your database password. Check you last query using the SYSTEM block.



If your query retrieves correct data then go to below debugging steps else fix your query.

Step 2:
Debugging

1. Check the length of fields used in data block. If a non-database/database item is defined in the block and value passed exceeding its maximum length defined then query fails.

   Eg: Customer_Name defined max. length as 30 chars where as query populates more than 30 characters.

   
    
2.  Check if block has non database fields but set database item to yes.
3.  Check if all the required fields are populated else set required property to no.
4.  Check if initialization of form is done set-org-context-in-oracle-apps-11i/R12.

Hopefully above debugging steps would resolve the error.

Custom Message in Oracle forms

FND_MESSAGE is used to display a custom message that needs user response in oracle forms. It's an alternative to "message" function in forms.



FND_MESSAGE.SET_NAME('FND','ERROR');
FND_MESSAGE.SET_TOKEN('MSG', 'Error Message');
FND_MESSAGE.SHOW;

Close Parent window while closing child window

Below pseudo code can be only used if child window is Standard Oracle form. If child window is custom then use close_window() or exit_form.

Pseudo logic
==========

Declare a parameter/global variable.
Initialize variable on PUSH_BUTTON  before opening child window.

When child window closes the cursor returns to the same item(PUSH_BUTTON) on main window.

Use below code in WHEN-NEW-ITEM-INSTANCE trigger of PUSH_BUTTON on main window to close parent window as soon as cursor returns back from child.


declare
  form_id FORMMODULE;
BEGIN
  IF :parameter.form_close = 'Y' THEN
    form_id := FIND_FORM(NAME_IN('SYSTEM.CURRENT_FORM'));
    IF NOT ID_NULL(form_id) THEN
      CLOSE_FORM(form_id);
    END IF;
  END IF;
END;

You can also use exit_form instead of close_form;