Query To Get List of all Responsibility attached for user

 SELECT fuser.user_name,
         frt.responsibility_name,
         furgd.start_date,
         furgd.end_date,
         fresp.responsibility_key,
         fapp.application_short_name,
         furgd.*
    FROM apps.fnd_user_resp_groups_direct furgd,
         apps.fnd_user fuser,
         apps.fnd_responsibility fresp,
         apps.fnd_responsibility_tl frt,
         apps.fnd_application fapp,
         apps.fnd_application_tl fat
   WHERE     1 = 1
         AND furgd.user_id = fuser.user_id
         AND furgd.responsibility_id = frt.responsibility_id
         AND fresp.responsibility_id = frt.responsibility_id
         AND fapp.application_id = fat.application_id
         AND fresp.application_id = fat.application_id
         AND frt.language = USERENV ('LANG')
         AND UPPER (fuser.user_name) = UPPER ('&Enter_User_Name')
--AND (furgd.end_date IS NULL OR furgd.end_date >= TRUNC(SYSDATE))
ORDER BY frt.responsibility_name;

Database Table Lock Alter Query

This query will generate the ALTER SYSTEM KILL SESSION command for the locked objects with correct SID and SERIAL number. Once the result generate, execute the kill command from the different session but not in the same TOAD/SQL Developer session.

SELECT object_name,
  'ALTER SYSTEM KILL SESSION '
  ||''''
  ||sid
  ||','
  ||serial
  ||''';'
FROM
  (SELECT c.owner,
    b.username,
    c.object_name,
    c.object_type,
    b.sid sid,
    b.serial# serial,
    b.status,
    b.osuser,
    b.machine
  FROM v$locked_object a ,
    v$session b,
    dba_objects c
  WHERE b.sid     = a.session_id
  AND a.object_id = c.object_id
  );

Ex:

FND_CONCURRENT_QUEUES         ALTER SYSTEM KILL SESSION '155,61';
FND_CONCURRENT_REQUESTS ALTER SYSTEM KILL SESSION '935,31';

DEBUG Log Script in WORKFLOW

This custom procedure which helps us to find the critical logs while working in WORKFLOW

Step 1: Create a Sequence

DROP SEQUENCE XX_DEBUG_S
/
CREATE SEQUENCE XX_DEBUG_S START WITH 1
/

Step 2: Create a Table

DROP TABLE xx_debug_log
/
CREATE TABLE xx_debug_log
(id        NUMBER
,log      VARCHAR2(1000)
,ldate   DATE
)
/

Step 3: Create a Custom Procedure

CREATE OR REPLACE PROCEDURE log_error(p_err_msg VARCHAR2)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
     INSERT INTO xx_debug_log VALUES(XX_DEBUG_S.nextval,p_err_msg,sysdate);
     COMMIT;
END;
/

USAGE : Call the below statement in the workflow dependent package so we will understand the workflow node and it will list all the point it reached before getting stuck with any error !

BEGIN
log_error('Custom Message - Stage 1');
.....
<Some Logic>
.....
log_error('Custom Message - Stage 2');
END;

Select * from xx_debug_log;

Request Group Name based on previous Concurrent Program run

i) To find the Request Group Name based on previous Concurrent Program run

SELECT cpt.user_concurrent_program_name     "Concurrent Program Name",
       DECODE(rgu.request_unit_type,
              'P', 'Program',
              'S', 'Set',
              rgu.request_unit_type)        "Unit Type",
       cp.concurrent_program_name           "Concurrent Program Short Name",
       rg.application_id                    "Application ID",
       rg.request_group_name                "Request Group Name",
       fat.application_name                 "Application Name",
       fa.application_short_name            "Application Short Name",
       fa.basepath                          "Basepath"
FROM fnd_request_groups          rg,
            fnd_request_group_units     rgu,
            fnd_concurrent_programs     cp,
            fnd_concurrent_programs_tl  cpt,
            fnd_application             fa,
            fnd_application_tl          fat
WHERE rg.request_group_id       =  rgu.request_group_id
   AND rgu.request_unit_id       =  cp.concurrent_program_id
   AND cp.concurrent_program_id  =  cpt.concurrent_program_id
   AND rg.application_id         =  fat.application_id
   AND fa.application_id         =  fat.application_id
   AND cpt.language              =  USERENV('LANG')
   AND fat.language              =  USERENV('LANG')
   AND cpt.user_concurrent_program_name = '<Custom Program/Report Name>';

Delete Data Templates, Definition from XDO_LOBS table

 End-date the Data Definition / Template is the right approach to disable the existing code but sometimes we are forced to use the new CODE rather than the end-dated one.

To prevent this, sometimes we should use the base table deletion to re-use the older one without changing most of the things.

Use the below approach to get it done

SELECT * FROM XDO_LOBS WHERE lob_code = 'XXAP_SUPP_MTD_UPD_BIP';
                    
SELECT * FROM XDO_TEMPLATES_B WHERE data_source_code = 'XXAP_SUPP_MTD_UPD_BIP';

SELECT * FROM XDO_TEMPLATES_TL WHERE template_code = 'XXAP_SUPP_MTD_UPD_BIP';
                    
SELECT * FROM XDO_DS_DEFINITIONS_B WHERE data_source_code = 'XXAP_SUPP_MTD_UPD_BIP';

SELECT * FROM XDO_DS_DEFINITIONS_TL WHERE data_source_code = 'XXAP_SUPP_MTD_UPD_BIP';


DELETE FROM XDO_LOBS WHERE lob_code = 'XXAP_SUPP_MTD_UPD_BIP';

DELETE FROM XDO_TEMPLATES_B WHERE data_source_code = 'XXAP_SUPP_MTD_UPD_BIP';

DELETE FROM XDO_TEMPLATES_TL WHERE template_code = 'XXAP_SUPP_MTD_UPD_BIP';
                    
DELETE FROM XDO_DS_DEFINITIONS_B WHERE data_source_code = 'XXAP_SUPP_MTD_UPD_BIP';

DELETE FROM XDO_DS_DEFINITIONS_TL WHERE data_source_code = 'XXAP_SUPP_MTD_UPD_BIP';

COMMIT;

FND USER EBS Application Login creation with SYSTEM ADMINISTRATOR Responsibility

-------- Start of script --------------

DECLARE

  v_session_id INTEGER := userenv('sessionid');
  v_user_name  VARCHAR2(30) := upper('<FND LOGIN NAME>');

BEGIN

  --Note, can be executed only when you have apps password.
  -- Call the procedure to Create FND User

  fnd_user_pkg.createuser(
     x_user_name                  => v_user_name
    ,x_owner                         => ''
    ,x_unencrypted_password  => '<password>'
    ,x_session_number             => v_session_id
    ,x_start_date                  => SYSDATE - 10
    ,x_end_date                   => SYSDATE + 100
    ,x_last_logon_date        => SYSDATE - 10
    ,x_description                => 'Senthil Nathan'
    ,x_password_date          => SYSDATE - 10
    ,x_password_accesses_left        => 10000
    ,x_password_lifespan_accesses => 10000
    ,x_password_lifespan_days        => 10000
    ,x_employee_id                          => 53670 

/*Change this id by running below SQL*/

     /* 
     SELECT person_id
           ,full_name
     FROM   per_all_people_f
     WHERE  upper(full_name) LIKE '%' || upper('<ampersand>full_name') || '%'
     GROUP  BY person_id
              ,full_name
     */

    ,x_email_address => 'SenthilNathan.Ranganathan@orgcorp.com'
    ,x_fax                   => ''
    ,x_customer_id    => ''
    ,x_supplier_id      => '');

  fnd_user_pkg.addresp(username         => v_user_name
                                      ,resp_app          => 'SYSADMIN'
                                      ,resp_key          => 'SYSTEM_ADMINISTRATOR'
                                      ,security_group => 'STANDARD'
                                      ,description       => 'Auto Assignment'
                                      ,start_date        => SYSDATE - 10
                                      ,end_date         => SYSDATE + 1000);
END;
/

Oracle SQL Special Functions - LISTAGG / CONNECT BY LEVEL / CONNECT BY PRIOR

A. LISTAGG - Convert rows into Columns

SELECT LISTAGG(vendor_name,', ') WITHIN GROUP (ORDER BY vendor_name) VENDOR_LIST
  FROM ap_suppliers 
 WHERE nvl(end_date_active,sysdate) <= sysdate 
   AND vendor_type_lookup_code = 'EMPLOYEE BENEFITS' 
   AND pay_group_lookup_code = 'EMPLOYEE' 
   AND payment_currency_code = 'INR'
   AND vendor_name like 'A%';

B. CONNECT BY LEVEL

SELECT level
  FROM dual
connect by level <= 10;

SELECT level, sys_connect_by_path( 'Senthil', '/' ) as path, 'Senthil'
  FROM dual
connect by level <= 3
order by level, path;

C. CONNECT BY PRIOR

SELECT PPX.person_id,
       PPX.full_name,
       PAAF.supervisor_id
  FROM per_people_x PPX,
   per_all_assignments_f PAAF
  WHERE PPX.person_id = PAAF.person_id 
  START WITH PPX.person_id = 53670
CONNECT BY PRIOR PAAF.supervisor_id = PPX.person_id 
    AND LEVEL = 2;