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;

Some of the useful Unix Commands

A. Getting the Header file information using the '$Header' text for the specific file

i)   strings -a $AU_TOP/forms/US/POXSCASL.fmb | grep '$Header' 

ii)  more /u02/migrate/prod_release/2019/prod_20190205/po_rebrand/shell_runme.sh | grep 'obs_dir' 

iii) grep -ir abc*

iv)  find /tmp -name $file_name -mtime +7 -exec rm -vf {} \; >> $SCRIPT_LOG/trace_purge_files.log 

B. To list the Database running list

ps -ef|grep pmon 

C. To list the Size of the mount point

df -h  

D. To list the Scheduler Jobs

crontab -l & crontab - e 

E. Zip and Unzip the group of files

zip -rp filname.zip filename

unzip -rp filname.zip 

F. To List JAR Files

jar xvf customall.jar  

G. To send the Mail from Unix box

mail -S smtp=relay.stp.org.com -s "Space Alert For $(hostname): Disk space $usep%" person@orgcorp.com

H. FMB Compilation to get FMX using the frmcmp_batch command

frmcmp_batch userid=apps/<pwd> module=POXSCASL.fmb output_file=$PO_TOP/forms/US/POXSCASL.fmx module_type=form batch=no compile_all=special

I. To list the unix files under the certain path

ls -lrt | more

a) To list Top 25

ls -ltr | head -25 

b) To list Bottom 5

ls -ltr | tail -5

c) To list intermediate among the 50 files (ex. To list only the 20-25 files in the 50 listed files)

ls -ltr | head -25 | tail -5

Database Object query to fetch the details based on Limited Information

1. Find a Table/View name based on limited information using the '%Selective Search%'

SELECT * FROM all_objects WHERE object_type in ('TABLE','VIEW') AND object_name like '%%';

Note: If any special character in the Object Name like '_ or $' then use the below format

SELECT * FROM all_objects WHERE object_type in ('TABLE','VIEW') AND object_name like 'V%$%' escape '\'; '

2. Find a Column name from the Table based on limited information using the '%Selective Search%'

SELECT * FROM all_tab_columns WHERE column_name like '%FILE%NAME%' AND owner = 'AP';

3. Find a package name from the database based on limited information using the '%Selective Search%'

SELECT * FROM all_source WHERE type = 'PACKAGE BODY' AND text like '%<Custom Search>%'; 

4. Find the Arguments/Log/Output information for the Concurrent Request

SELECT * FROM fnd_concurrent_programs_tl WHERE user_concurrent_program_name like 'XX%Program%Name%';

SELECT * FROM fnd_concurrent_requests WHERE concurrent_program_id = <Concurrent Program ID> order by request_id desc;

5. Find the File information path based on File name given in the History section rather than Database Object Name <FVGTSUTB.pls / apinvutb.pls >

SELECT * FROM ad_files WHERE filename like '%apinvutb%';

Then find the $Header Information$ using the Unix Query from the respective server,

strings -a $SQLAP_TOP/patch/115/sql/apinvutb.pls |grep '$Header'

ap patch/115/sql apiimvtb.pls 120.219.12020000.39
ap patch/115/sql apiimvts.pls 120.36.12020000.6
ap patch/115/sql apinvutb.pls 120.79.12020000.13
ap patch/115/sql apinvuts.pls 120.23.12020000.2 

iExpense Report struck in Interface table with REJECTION CODE : LINE AMOUNT IS NULL

Follow the below Steps to fix the Expense Interface struck:

Step 1: Identify the Null Amount column by using the below query for the reported Expense,

SELECT report_header_id  

  FROM AP_EXPENSE_REPORT_HEADERS_ALL 

WHERE invoice_num  = <Expense Number>;

SELECT report_line_id 

   FROM AP_EXPENSE_REPORT_LINES_ALL 

WHERE report_header_id = <report_header_id from query 1>

   AND amount IS NULL;

Step 2: Delete the Junk entry from Expense Line and Distribution after taking the backup.

CREATE TABLE AERLA_20190715_BKP AS  SELECT * FROM AP_EXPENSE_REPORT_LINES_ALL WHERE REPORT_LINE_ID = report_line_id ;

DELETE AP_EXPENSE_REPORT_LINES_ALL WHERE REPORT_LINE_ID = report_line_id ;

CREATE TABLE AERDA_20190715_BKP AS SELECT * FROM AP_EXP_REPORT_DISTS_ALL WHERE REPORT_LINE_ID = report_line_id ;

DELETE AP_EXP_REPORT_DISTS_ALL WHERE REPORT_LINE_ID = report_line_id ;

COMMIT;

Step 3: Run the “Expense Report Export” Program from AP Manager Responsibility

After the program completion check the output of the picked expense, it will be in PAID status rather than "Invoice Amount is Null"

Then it will move the Expense report to Payable section by keeping Expense Number reference as Invoice Number.

My Group Requisition Functionality in iProcurement

To view the 'My Group Requisition' functionality, use the below setup under the Purchasing Super User Responsibility to achieve it.

Before changing the Security Level setup

Responsibility: ICX USD1 iProcurement User 

Navigation: iProcurement Home Page à Requisition

View LOV is not listing “My Group Requisition”

Cause: Security Option is setup at “Purchasing” level in the Document Type Setup.

Responsibility: Purchasing Super User

Navigation: Setup à Purchasing à  Document Types

To fix this, setup the Security Option at “Public” level in the Document Type Setup

Access Level Privilege:

Private: Only the document owner may access these documents.

Public: Any user may access these documents.

Purchasing:  Only the document owner and users listed as buyers in the Define Buyers window may access these documents.

After changing the Security Level setup

My Group Requisition LOV is listed in the iProcurement!

Retrieve and Reset the FND USER Application Login Password

A. RETRIEVE THE FND USER PASSWORD

To Retrieve the active user Application password from back-end, first compile the below package in the corresponding instance.

CREATE OR REPLACE PACKAGE APPS.XX_get_pwd
AS

FUNCTION decrypt ( key IN VARCHAR2,
                               value IN VARCHAR2 ) Return Varchar2;
END XX_get_pwd;
/

CREATE OR REPLACE PACKAGE BODY APPS.XX_get_pwd
AS

FUNCTION decrypt ( key IN VARCHAR2,
                               value IN VARCHAR2 )
RETURN VARCHAR2
AS
LANGUAGE JAVA NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String)
return java.lang.String';

END XX_get_pwd;
/

Once the package XX_get_pwd is complied then execute the below piece of snippet to retrieve the encrypted password for a particular user.

SELECT usr.user_name,
       XX_get_pwd.decrypt
          ((SELECT (SELECT XX_get_pwd.decrypt
                              (fnd_web_sec.get_guest_username_pwd,
                               usertable.encrypted_foundation_password
                              )
                      FROM DUAL) AS apps_password
              FROM fnd_user usertable
             WHERE usertable.user_name =
                      (SELECT SUBSTR
                                  (fnd_web_sec.get_guest_username_pwd,
                                   1,
                                     INSTR
                                          (fnd_web_sec.get_guest_username_pwd,
                                           '/'
                                          )
                                   - 1
                                  )
                         FROM DUAL)),
           usr.encrypted_user_password
          ) PASSWORD
  from FND_USER USR
where UPPER(USR.USER_NAME) = UPPER('A_USER');


B. RESET THE FND USER PASSWORD

To reset the password for a particular Application user then use the below anonymous block to achieve it.

DECLARE

      lv_ret_val           BOOLEAN;
      lv_user_name     VARCHAR2(50) := 'A_USER';
      lv_new_pwd       VARCHAR2(20) := 'welcome2oracle';

BEGIN
      lv_ret_val := fnd_user_pkg.changepassword(username => lv_user_name,
                                                                        newpassword => lv_new_pwd);
                           
     IF l_ret_val THEN
           DBMS_OUTPUT.PUT_LINE('The password is successfully reset to '||  lv_new_pwd);
           COMMIT;
     ELSE
           DBMS_OUTPUT.PUT_LINE('The password reset has failed ');
     End IF;

END;

Note: If you're not sure about the user name get the exact name from FND_USER table

select user_name from fnd_user where description like '%Senthil%Nathan%';

Oracle Application framework JDR_UTILS API

A. JDR_UTILS.LISTDOCUMENTS

This API will help to list all the OA Framework documents from the particular path/module.

Sample Script to use it in TOAD/SQL DEVELOPER

BEGIN
jdr_utils.listdocuments('/oracle/apps/ap/oie/creditCard/statement/webui/UnusedCCTrxnPG', TRUE);
END;

B. JDR_UTILS.LISTCUSTOMIZATIONS

To list all the Personalizations and Extensions for a specific custom enhancement use the jdr_utils.listcustomizations.

Sample Script to use it in TOAD/SQL DEVELOPER

BEGIN
jdr_utils.listcustomizations('/oracle/apps/ap/oie/creditCard/statement/webui/UnusedCCTrxnPG');
END;

C. JDR_UTILS.PRINTDOCUMENT

To view the actual implementation code or logic for a specific enhancement use the jdr_utils.printdocument.

Sample Script to use it in TOAD/SQL DEVELOPER

BEGIN
jdr_utils.printdocument('/oracle/apps/ap/oie/creditCard/statement/webui/UnusedCCTrxnPG');
END;

D. JDR_UTILS.DELETEDOCUMENT

To delete a document or a personalization we can use the jdr_utils.deletedocument API.

Sample Script to use it in TOAD/SQL DEVELOPER

BEGIN
jdr_utils.deletedocument('/oracle/apps/ap/oie/creditCard/statement/webui/customizations/user/12345/UnusedCCTrxnPG');
END;

Oracle Database Link to the External Server from Current Server

To create database link from Server A to Server B (External Server), follow the below approach:

DATABASE LINK CREATION SYNTAX:

CREATE DATABASE LINK <DB_LINK_SERVER_B> CONNECT TO <B_SERVER_USER> IDENTIFIED BY <B_SERVER_USER_PWD> USING '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=<serverB.domain@company.com>)(PORT=<1234>))(CONNECT_DATA=(SID=<SERVER_B_SID>)))';

ex: CREATE DATABASE LINK EXT_SERVER_B CONNECT TO USER_B IDENTIFIED BY USER_B_PWD USING '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=serverB.domain@company.com)(PORT=1234))(CONNECT_DATA=(SID=B_SID)))';

Test the connection from server A:

SQL> SELECT * FROM DUAL@EXT_SERVER_B;

DUMMY
--------
X  => This result is from Server B

Note:  If DB LINK is already available in the database for the external server then drop the existing DB LINK first and then re-create the new entry.

DROP DATABASE LINK <DB_LINK_SERVER_B>;

If you're planning to re-create the DB LINK using the existing TNS server property with different DB LINK name which you've dropped then follow the below steps to achieve that, otherwise you'll end up in ORA-02085: database link string connects to string error.

A. Connect to SQL and check the global_names value

SQL> show parameter global_names

NAME              TYPE     VALUE
----                   ----          -----
global_names  boolean  TRUE

B. Change the global_names parameter property to FALSE to allow the system to set the different DB LINK name for the old TNS server entry with you need to change

SQL> alter system set global_names=FALSE scope=both;

System altered.

SQL> show parameter global_names

NAME              TYPE     VALUE
----                    ----        -----
global_names  boolean  TRUE

C. Drop the database link and re-create the database link.

SQL> DROP DATABASE LINK <DB_LINK_SERVER_B>;

Database link dropped.

SQL> CREATE DATABASE LINK <DB_LINK_SERVER_B1> CONNECT TO <B_SERVER_USER> IDENTIFIED BY <B_SERVER_USER_PWD> USING '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=<serverB.domain@company.com>)(PORT=<1234>))(CONNECT_DATA=(SID=<SERVER_B_SID>)))';

Database link created.

Now check the DB LINK functionality by using the command from the current server A,

SQL> SELECT * FROM DUAL@<DB_LINK_SERVER_B1>;

DUMMY
-----
X