How to get table to index structure like toad

Here is the built in which will give you the table or index structure .

How to get tables / Index structure.
SELECT to_char(DBMS_METADATA.GET_DDL ('INDEX', index_name,index_owner))
FROM dba_indexes
WHERE table_owner=upper('APPS');

SELECT to_char(DBMS_METADATA.GET_DDL ('TABLE', TABLE_name, table_owner))
FROM dba_TABLES
WHERE table_owner=upper('ONT');

PL/SQL Script to Add System Administrator in Oracle Applications

BEGIN
fnd_user_pkg.addresp ('SGUDURU','SYSADMIN','SYSTEM_ADMINISTRATOR','STANDARD','Add Responsibility to USER using pl/sql',SYSDATE,SYSDATE + 100);
commit;
dbms_output.put_line('Responsibility Added Successfully');
exception
        WHEN others THEN
                dbms_output.put_line(' Responsibility is not added due to ' || SQLCODE || substr(SQLERRM, 1, 100));
                ROLLBACK;
END;
 

Pl/SQL Script to Create user in Oracle applications

DECLARE
  v_user_name  VARCHAR2(30):=UPPER('SGUDURU');
  v_password   VARCHAR2(30):='ORACLE123';
  v_session_id INTEGER     := USERENV('sessionid');
BEGIN
  fnd_user_pkg.createuser (
    x_user_name => v_user_name,
    x_owner => NULL,
    x_unencrypted_password => v_password,
    x_session_number => v_session_id,
    x_start_date => SYSDATE,
    x_end_date => NULL
  );
  COMMIT;
  DBMS_OUTPUT.put_line ('User:'||v_user_name||'Created Successfully');
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.put_line ('Unable to create User due to'||SQLCODE||' '||SUBSTR(SQLERRM, 1, 100));
    ROLLBACK;
END;

How to rebuild a partitioned index

alter index my_ind1 rebuild partition P_1 ;

How to verify the xml template file

select * from apps.xdo_templates_vl  where template_name like '%Service Contract%'

How to find the oracle workflow version using sql query

select * from   WF_RESOURCES where  NAME = 'WF_VERSION' and language = 'US'

Oracle Applications front end URL from Back end

SELECT profile_option_value
FROM fnd_profile_option_values
WHERE profile_option_id=
(SELECT profile_option_id
FROM fnd_profile_options WHERE
profile_option_name = 'APPS_FRAMEWORK_AGENT')
AND level_value = 0;

Or

SELECT home_url FROM icx_parameters;

In Some cases above query may not work properly.

Query to find current running SQL for given Concurrent Program

SELECT a.address,
  s.status,
  s.username,
  s.osuser,
  a.sql_text,
  s.SID
FROM v$sql a,
  v$session s
WHERE s.sql_address = a.address
AND s.SID          IN
  (SELECT d.SID
  FROM apps.fnd_concurrent_requests a,
    apps.fnd_concurrent_processes b,
    v$process c,
    v$session d
    --v$sql e
  WHERE a.controlling_manager = b.concurrent_process_id
  AND c.pid                   = b.oracle_process_id
  AND d.paddr                 = c.addr
    --AND d.sql_address = e.address
  AND a.request_id = 10803707 (provide the actual request id)
  and a.PHASE_CODE = 'R'
  )

Query to find Indexed Column names in table

SELECT c.index_name, i.uniqueness, c.column_name
FROM   user_indexes i, user_ind_columns c
WHERE  i.index_name = c.index_name
  AND  i.table_name = UPPER(:P_TABLE_NAME)
ORDER  BY c.index_name, c.column_position;

What is hold in OM ? What are the types of Holds in Order Management.

A Hold is to stop an order, return, order line, or return line from continuing to progress through its life cycle.

A Hold can be applied manually to a specific order by querying the order and then selecting the apply holds screen or can be applied automatically based on set of  criteria defined by the user (Hold source setup). User can define as many different Holds based on the business needs of the user/ company.

User can not apply a hold if the order has been pick released.

Types of Holds in Order Management

Credit Check Hold

    Order Entry performs an automatic credit check on the active customers, based on credit rules and credit limits defined by the user in your system. You can set credit limits for a total of all the customer's orders and of individual order amounts.

General Services Administration (GSA) Violation Hold

       The GSA hold ensures that a specific group of customers always receives the best pricing.

Configurations Holds

    If the configuration is invalid this hold will be applied on the order to prevent further processing.

How to progress Errored workflow using back end api

DECLARE
   CURSOR c1
   IS
     SELECT c.INSTANCE_LABEL,a.line_id
        FROM oe_order_lines_all a,
        apps.wf_item_activity_statuses b,
        apps.wf_process_activities c
       WHERE header_id = 602953547
       and b.item_key = to_char(a.line_id)
       and b.item_type ='OEOL'
       and b.activity_status='ERROR'
       and b.process_activity = c.instance_id;

BEGIN
   FOR c1_rec IN c1
   LOOP
      wf_engine.handleerror (
         'OEOL',
         TO_CHAR (c1_rec.line_id),
         C1_REC.INSTANCE_LABEL,
         'RETRY',
         NULL
      );
   END LOOP;
   commit;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (SQLERRM);
END;

How to release the Hold automatically when it reaches the hold until date

If any of the user enters the Hold Until date while applying the Hold then the hold should be released automatically when it reaches to the Hold Until Date. 

The below concurrent program should be scheduled based on the requirement. So that the program will check are there any holds which are reached hold until date and ready to release the holds. 

Concurrent Program Name : Release Expired Hold

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