How to raise business event manually using script

DECLARE
   l_event_name             VARCHAR2 (240)
                                  := 'oracle.apps.ont.genesis.outbound.update';
   l_event_parameter_list   wf_parameter_list_t := wf_parameter_list_t ();

BEGIN
      wf_event.addparametertolist (
         p_name=> 'HEADER_ID',
         p_value=> '96567456745',
         p_parameterlist=> l_event_parameter_list
      );
      wf_event.addparametertolist (
         p_name=> 'LINE_ID',
         p_value=> '865875768',
         p_parameterlist=> l_event_parameter_list
      );
      wf_event.addparametertolist (
         p_name=> 'HOLD_SOURCE_ID',
         p_value=>'54326456',
         p_parameterlist=> l_event_parameter_list
      );
      wf_event.addparametertolist (
         p_name=> 'CHANGE_TYPE',
         p_value=> 'APPLY',
         p_parameterlist=> l_event_parameter_list
      );

      BEGIN
         wf_event.RAISE (
            p_event_name=> l_event_name,
            p_event_key=> SYS_GUID (),
            p_parameters=> l_event_parameter_list
         );
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.put_line (
                  ' Unexpected Error  : '
               || SUBSTR (SQLERRM, 1, 250)
            );
      END;


   COMMIT;
   l_event_parameter_list.DELETE;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (
            ' Unexpected Error in main  block  : '
         || SUBSTR (SQLERRM, 1, 250)
      );
END;

Concurrent Request Status Codes

Concurrent Request Status Codes:

SELECT LOOKUP_CODE STATUS_CODE, MEANING
 FROM FND_LOOKUP_VALUES
 WHERE LOOKUP_TYPE = 'CP_STATUS_CODE'
   AND ENABLED_FLAG = 'Y'
   AND VIEW_APPLICATION_ID = 0
ORDER BY LOOKUP_CODE;

STATUS_CODE	MEANING
A	Waiting
B	Resuming
C	Normal
D	Cancelled
E	Error
G	Warning
H	On Hold
I	Normal
M	No Manager
P	Scheduled
Q	Standby
R	Normal
S	Suspended
T	Terminating
U	Disabled
W	Paused
X	Terminated
Z	Waiting

Concurrent Request Phase Codes:

SELECT LOOKUP_CODE PHASE_CODE, MEANING
 FROM FND_LOOKUP_VALUES
 WHERE LOOKUP_TYPE = 'CP_PHASE_CODE'
   AND ENABLED_FLAG = 'Y'
   AND VIEW_APPLICATION_ID = 0
ORDER BY PHASE_CODE;

STATUS_CODE	MEANING
C	Completed
I	Inactive
P	Pending
R	Running

How to find the inbound and out bound mailer details

SELECT b.component_name,
c.parameter_name,
a.parameter_value
FROM apps.fnd_svc_comp_param_vals a,
apps.fnd_svc_components b,
apps.fnd_svc_comp_params_b c
WHERE b.component_id = a.component_id
AND b.component_type = c.component_type
AND c.parameter_id = a.parameter_id
AND c.encrypted_flag = 'N'
AND b.component_name like '%Mailer%'
AND c.parameter_name in ('INBOUND_SERVER','OUTBOUND_SERVER', 'REPLYTO')
ORDER BY c.parameter_name;

Concurrent Request Status and Phase Codes

Concurrent Request Status and Phase Codes

Concurrent Request Status Codes:

SELECT LOOKUP_CODE STATUS_CODE, MEANING
 FROM FND_LOOKUP_VALUES
 WHERE LOOKUP_TYPE = 'CP_STATUS_CODE'
   AND ENABLED_FLAG = 'Y'
   AND VIEW_APPLICATION_ID = 0
ORDER BY LOOKUP_CODE;

STATUS_CODE	MEANING
A	Waiting
B	Resuming
C	Normal
D	Cancelled
E	Error
G	Warning
H	On Hold
I	Normal
M	No Manager
P	Scheduled
Q	Standby
R	Normal
S	Suspended
T	Terminating
U	Disabled
W	Paused
X	Terminated
Z	Waiting

Concurrent Request Phase Codes:

SELECT LOOKUP_CODE PHASE_CODE, MEANING
 FROM FND_LOOKUP_VALUES
 WHERE LOOKUP_TYPE = 'CP_PHASE_CODE'
   AND ENABLED_FLAG = 'Y'
   AND VIEW_APPLICATION_ID = 0
ORDER BY PHASE_CODE;

STATUS_CODE	MEANING
C	Completed
I	Inactive
P	Pending
R	Running

checking the Environment Variable from back end query

SELECT variable_name,
  VALUE
FROM apps.fnd_env_context
WHERE concurrent_process_id =
  (SELECT MAX(concurrent_process_id) FROM apps.fnd_env_context
  )
ORDER BY variable_name;

To Find Latest Query Executing/Running in Database for Specific Concurrent Request

Find Latest Query Executing/Running in Database for Specific Concurrent Request

If one concurrent request is running  and you want to find currently which query of that request is running in session then you can run below query in toad or sql developer by putting request ifd of that concurrent request-

select

   to_char(inst_id) rac_inst_id,

   to_char(sid) sid,

   to_char(serial#) serial#,

   sql_text

from

   apps.fnd_concurrent_requests fcr,

   gv$session vs,

   v$sqltext vq

where

   vs.process = fcr.os_process_id

   and vs.sql_address = vq.address

   and fcr.status_code = 'R'

   and fcr.phase_code = 'R'

   and request_id in

  (select fcr.request_id

  from (select /*+ index (fcr1 FND_CONCURRENT_REQUESTS_N3) */

          fcr1.request_id

       from fnd_concurrent_requests fcr1

         where 1=1

         start with fcr1.request_id = &request_id

          connect by prior fcr1.request_id = fcr1.parent_request_id) x,

          fnd_concurrent_requests fcr,

          fnd_concurrent_programs fcp,

          fnd_concurrent_programs_tl fcptl

          where fcr.request_id = x.request_id

          and fcr.concurrent_program_id = fcp.concurrent_program_id

          and fcr.program_application_id = fcp.application_id

          and fcp.application_id = fcptl.application_id

          and fcp.concurrent_program_id = fcptl.concurrent_program_id

          and fcptl.language = 'US')

Order By Serial#;

This will be very useful for debugging purposes means we can derive what part of code is taking time or where program got stuck and for other purposes.

SQL Query to Find Scheduled Concurrent Programs in System

SQL Query to Find Scheduled Concurrent Programs in System

SELECT fcr.request_id
     , fcpt.user_concurrent_program_name|| NVL2(fcr.description, ' (' || fcr.description || ')', NULL) conc_prog
     , fu.user_name requestor
     , fu.description requested_by
     , fu.email_address
     , frt.responsibility_name requested_by_resp
     , trim(fl.meaning) status
     , fcr.phase_code
     , fcr.status_code
     , fcr.argument_text "PARAMETERS"
      , TO_CHAR(fcr.request_date, 'DD-MON-YYYY HH24:MI:SS') requested
     , TO_CHAR(fcr.requested_start_date, 'DD-MON-YYYY HH24:MI:SS') requested_start   
     , TO_CHAR((fcr.requested_start_date), 'HH24:MI:SS') start_time
     , DECODE(fcr.hold_flag, 'Y', 'Yes', 'N', 'No') on_hold
     , CASE
          WHEN fcr.hold_flag = 'Y'
             Then Substr(
                    fu.description
                  , 0
                  , 40
                 )
       END last_update_by
     , CASE
          WHEN fcr.hold_flag = 'Y'
             THEN fcr.last_update_date
       END last_update_date
     , fcr.increment_dates
     , CASE WHEN fcrc.CLASS_INFO IS NULL THEN
        'Yes: ' || TO_CHAR(fcr.requested_start_date, 'DD-MON-YYYY HH24:MI:SS')
       ELSE
        'n/a'
       END run_once
     , CASE WHEN fcrc.class_type = 'P' THEN
        'Repeat every ' ||
        substr(fcrc.class_info, 1, instr(fcrc.class_info, ':') - 1) ||
        decode(substr(fcrc.class_info, instr(fcrc.class_info, ':', 1, 1) + 1, 1),
               'N', ' minutes',
               'M', ' months',
               'H', ' hours',
               'D', ' days') ||
        decode(substr(fcrc.class_info, instr(fcrc.class_info, ':', 1, 2) + 1, 1),
               'S', ' from the start of the prior run',
               'C', ' from the completion of the prior run')
       ELSE
         'n/a'
       END set_days_of_week
       , CASE WHEN fcrc.class_type = 'S' AND instr(substr(fcrc.class_info, 33),'1',1) > 0 THEN
          'Days of week: ' ||
                  decode(substr(fcrc.class_info, 33, 1), '1', 'Sun, ') ||
                  decode(substr(fcrc.class_info, 34, 1), '1', 'Mon, ') ||
                  decode(substr(fcrc.class_info, 35, 1), '1', 'Tue, ') ||
                  decode(substr(fcrc.class_info, 36, 1), '1', 'Wed, ') ||
                  decode(substr(fcrc.class_info, 37, 1), '1', 'Thu, ') ||
                  decode(substr(fcrc.class_info, 38, 1), '1', 'Fri, ') ||
                  decode(substr(fcrc.class_info, 39, 1), '1', 'Sat ')
         ELSE
           'n/a'
         end  days_of_week 
  FROM fnd_concurrent_requests fcr
     , fnd_user fu
     , fnd_concurrent_programs fcp
     , fnd_concurrent_programs_tl fcpt
     , fnd_printer_styles_tl fpst
     , fnd_conc_release_classes fcrc
     , fnd_responsibility_tl frt
     , fnd_lookups fl
 WHERE fcp.application_id = fcpt.application_id
   AND fcr.requested_by = fu.user_id
   AND fcr.concurrent_program_id = fcp.concurrent_program_id
   AND fcr.program_application_id = fcp.application_id
   AND fcr.concurrent_program_id = fcpt.concurrent_program_id
   AND fcr.responsibility_id = frt.responsibility_id
   AND fcr.print_style = fpst.printer_style_name(+)
   AND fcr.release_class_id = fcrc.release_class_id(+)
   AND fcr.status_code = fl.lookup_code
   AND fl.lookup_type = 'CP_STATUS_CODE'
   AND fcr.phase_code = 'P'
   AND 1=1
Order By Fu.Description, Fcr.Requested_Start_Date Asc;

WFLOAD – Oracle Workflow Loader utility

WFLOAD Command to download Oracle Workflow

Syntax-

WFLOAD <apps/pwd>@<connectstring> 0 Y DOWNLOAD FILE_NAME.wft item_type

Modes-

UPGRADE – Protection and Customization levels of data

UPLOAD  - Only protection level of data.Not supporting customisation

FORCE   - Force upload, protection or customization not supported

For example-

WFLOAD apps/apps@VIS 0 Y DOWNLOAD POAPPRV_V1.wft POAPPRV

WFLOAD Command to upload Oracle Workflow

Syntax-

WFLOAD <apps/pwd>@<connectstring> 0 Y {UPLOAD | UPGRADE | FORCE} FILE_NAME.wft

For Example-

WFLOAD apps/apps@VIS 0 Y UPLOAD POAPPRV_V1.wft

Seeded Workflow Files Location

You can also download seeded workflow definition files directly from below directory-

$<Application_TOP>/patch/115/import/<LANG>

For example- Account Payables workflow file can be found at-

$AP_TOP/patch/115/import/US

RESULT_CACHE hint on Oracle 11G

Result Cache is a new feature in Oracle 11g.  It caches the results of queries and puts it into shared pool. If you are executing the same query without changes then it uses the result of shared pool. 

http://docs.oracle.com/cd/E16655_01/server.121/e15857/tune_result_cache.htm#TGDBA648

How to find all the valid hold release reason codes from back end

  SELECT  *
               FROM    OE_LOOKUPS
          WHERE   LOOKUP_TYPE = 'RELEASE_REASON'
          and enabled_flag ='Y'
          and end_date_active is null

How to find the tables which are registered with Oracle Apps

select * from APPLSYS.FND_TABLES

oracle application module short name and description using back end table

select * from APPLSYS.FND_ORACLE_USERID

How to get the table or index syntax from sql

We can use the DBMS_METADATA.GET_DDL which will retrieve the complete syntax of the table / index

Here is the example.

 SELECT DBMS_METADATA.GET_DDL('TABLE','TEST_PRA','APPS') ddl FROM dual;

Oracle Alerts back end tables and it is description

ALR_ALERTS -  It stores the alerts definition and the query
ALR_PERIODIC_ALERTS_VIEW -- It stores the periodic information of an alert
ALR_ACTIONS - It stores alert action information, ex. email subject, body and other infromation
ALR_ACTIONS_V -- It is a view based on ALR_ACTIONS

Adding a form function to menu or submenu using back end script

DECLARE
   xrowid   VARCHAR2 (2000);
BEGIN
   fnd_menu_entries_pkg.insert_row (
      xrowid, -- Rowid
      87216, -- Menu ID
      201, -- Sequence Number
      NULL, -- Sub menu ID
      50138, -- Function ID
      'Y', -- Grant Flag
      NULL, -- Prompt
      NULL, -- Description
      SYSDATE, -- Creation Date
      -1, -- Created by
      SYSDATE, Last update date
      -1, -- Last Updated by
      -1 -- Last update login
   );
   COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN
      ROLLBACK;
      DBMS_OUTPUT.put_line (SQLERRM);
END;

What is difference between AD_BUGS and AD_APPLID_PATCHES ?

AD_BUGS holds information about the various Oracle Applications bugs whose fixes have been applied (ie. patched) in the Oracle Applications installation. 

AD_APPLIED_PATCHES holds information about the "distinct" Oracle Applications patches that have been applied. If 2 patches happen to have the same name but are different in content (eg. "merged" patches), then they are considered distinct and this table will therefore hold 2 records.

How add a concurrent program to a concurrent manager using back end script

BEGIN
   fnd_manager.specialize (
      concurrent_manager_short_name,
      conc_manager_application_name,
      program_type, --(include/exclude)
      object_type, -- 'Program'
      concurrent_program_short_name,
      concurrent_program_application,
      last_update_date,
      last_update_by
   );
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (SQLERRM);
END;

How to find how which concurrent manager picked up which concurrent program

SELECT fcqt.user_concurrent_queue_name, fcqc.include_flag, fcqc.type_code,
       fcpt.user_concurrent_program_name
  FROM fnd_concurrent_queues_tl fcqt,
       fnd_concurrent_queue_content fcqc,
       fnd_concurrent_programs_tl fcpt
 WHERE fcqt.application_id = fcqc.queue_application_id
   AND fcqt.concurrent_queue_id = fcqc.concurrent_queue_id
   AND fcpt.LANGUAGE = 'US'
   AND fcpt.application_id = fcqc.type_application_id
   AND fcpt.concurrent_program_id = fcqc.type_id
   AND fcpt.user_concurrent_program_name LIKE 'Concurrent program name%'

Steps to develop a form in using 10g forms

Step 1: Download the TEMPLATE.fmb.
Step 2: Rename the TEMPLATE.fmb as per your company naming standards.
Step 3: Change the below information on the PER-FORM trigger at form level trigger
      FND_STANDARD.FORM_INFO('$Revision: <Number>$', 

                   '<Form Name>', 
                   '<Application Shortname>', 
                   '$Date: <YY/MM/DD HH24:MI:SS> $',
                   '$Author: <developer name> $');
APP_STANDARD.EVENT('PRE-FORM');  
APP_WINDOW.SET_WINDOW_POSITION('BLOCKNAME',
                   'FIRST_WINDOW');

 Step 4: Add the custom blocks as per your requirement. 

 Step 5: Assign the proper property classes to the items defined in the block

 Step 6: Write the custom logic to achive the desired functionality.

 Step 7: Register the form with Oracle Apps.

 Step 8: Assign the form function to a responsibility

 Step 9: Upload the form in to custom top of unix / Liunx box 

 Step 10: Compile the form using the below compilation script

frmcmp_batch module=$AU_TOP/forms/US/ABC.fmb userid=APPS/apps output_file=$CUSTOM_TOP/forms/US/ABC.fmx module_type=form

How to find the sql where clause of a value set from back end

select application_table_name,
       value_column_name,
       meaning_column_name,
       additional_where_clause
  from fnd_flex_validation_tables a, fnd_flex_value_sets b
 where a.flex_value_set_id = b.flex_value_set_id
       and b.flex_value_set_name = :flex_value_set_name