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