Sunday, August 30, 2020

How to find the Oracle user id and the description of the Oracle User

 SELECT * FROM FND_ORACLE_USERID

To find the concurrent request Arguments

 To find the current request arguments in 2 ways. 

1.SELECT  ARGUMENT_TEXT, ARGUMENT1,ARGUMENT2,ARGUMENT3, ARGUMENT4,ARGUMENT5 FROM FND_CONCURRENT_REQUESTS WHERE REQUEST_ID  = 4532223

2. select * from FND_CONC_REQUEST_ARGUMENTS WHERE REQUEST_ID = 112581918

Tuesday, October 29, 2019

API to end date the User in Oracle Apps - FND_USER_PKG.UPDATEUSER


Below script is used for updating User end date 


DECLARE
    lc_user_name       VARCHAR2(100)  := 'SGUDURU';
    ld_user_end_date   DATE  := SYSDATE+1000;
BEGIN
   fnd_user_pkg.updateuser
   (  x_user_name               => lc_user_name,
      x_owner                   => NULL,
      x_unencrypted_password    => NULL,
      x_start_date              => NULL,
      x_end_date                => sysdate+1000,
      x_password_date           => sysdate+1000,
      x_password_lifespan_days  => NULL,
      x_employee_id             => NULL,
      x_email_address           => NULL
   );

 COMMIT;

EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/

Wednesday, April 18, 2018

SQL Query to find Request Set for the given concurrent program

SQL Query to find  Request Set  for the given concurrent program :


SELECT
   DISTINCT  user_request_set_name
FROM
    fnd_request_sets_tl rs,
    fnd_request_set_programs rsp,
    fnd_concurrent_programs_tl cp
WHERE
    rsp.concurrent_program_id = cp.concurrent_program_id
    AND   rs.request_set_id = rsp.request_set_id
    AND   upper(user_concurrent_program_name) = upper(:enter_prog_name)
                       ;

Thursday, April 5, 2018

Script to Change Password of user from Backend In Oracle Applications Using API

SET serveroutput ON;
DECLARE
  v_user_name    VARCHAR2(30):= UPPER('SGUDURU');
  v_new_password VARCHAR2(30):= 'welcome1';
  v_status       BOOLEAN;
BEGIN
  v_status   := fnd_user_pkg.ChangePassword ( username => v_user_name, 
                                              newpassword => v_new_password 
                                            );
  IF v_status =TRUE THEN
    dbms_output.put_line ('The password reset successfully for the User:'||v_user_name);
    COMMIT;
  ELSE
    DBMS_OUTPUT.put_line ('Unable to reset password due to'||SQLCODE||' '||SUBSTR(SQLERRM, 1, 100));
    ROLLBACK;
  END IF;
END;

Query to list Active Responsibilities of a Active User In Oracle Applications


SELECT fu.user_name,
       frv.responsibility_name,
       frv.responsibility_key,
       TO_CHAR (furgd.start_date, 'DD-MON-YYYY') "START_DATE",
       TO_CHAR (furgd.end_date, 'DD-MON-YYYY') "END_DATE"
FROM fnd_user fu,
  fnd_user_resp_groups_direct furgd,
  fnd_responsibility_vl frv
WHERE fu.user_id                     = furgd.user_id
AND furgd.responsibility_id          = frv.responsibility_id
AND furgd.end_date                  IS NULL
AND fu.user_name                     = '&user_name'
AND furgd.start_date                <= sysdate
AND NVL(furgd.end_date, sysdate + 1) > sysdate
AND fu.start_date                   <= sysdate
AND NVL(fu.end_date, sysdate + 1)    > sysdate
AND frv.start_date                  <= sysdate
AND NVL(frv.end_date, sysdate + 1)   > sysdate;

Sunday, March 25, 2018

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');

Tuesday, January 30, 2018

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;

Monday, January 22, 2018

Tuesday, January 2, 2018

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'

Tuesday, October 31, 2017

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.

Tuesday, August 22, 2017

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;

Tuesday, November 1, 2016

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.





Sunday, September 20, 2015

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;

Wednesday, July 22, 2015

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

Friday, March 13, 2015

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;

Thursday, January 8, 2015

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