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;

Tuesday, January 2, 2018

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;