Calling SqlPlus From Unix Shell Script

In this post we will discuss how we can call sqlplus from within a unix shell script. Here is a post on how to read concurrent program parameters from shell script and this is a continuation to that.
The syntax to call a SQLPLUS from shell script is

sqlplus -s $login <<-EOF
set feedback off
set serveroutput on
declare
--  variable declarations
....
....
begin
--sql statements..
....
....
end;
/
exit
EOF

In the syntax above,
$login is the unix variable which stores username/password.
EOF is the identifier to indicate start and end of sql statements.
UNIX variables can be referred inside sqlplus by using $ sign. Any dbms_output.put_line statements are printed in the log file if script is run through concurrent program.

Hide a Button in the form using Forms Personlization

In one of the forum, there was a requirement to allow bookings only by certain user.
This can be achieved using forms personalization for users on 11.5.10 or higher.
Open the sales Order Form and click on Help->Diagnostics->Custom Code->Personalize to open the forms personlization screen.
(Click on the image to zoom it)






P.S.
In the example above I have hardcoded user name which is not the advisable way.
The better approach could be to create a custom profile and assign a value to the custom profile at user level. Based on the value entered the button can be enabled or disabled.

Note: The same result can also be achieved by using custom.pll

Custom Title Bar

Profile option "Site Name" can be used to display custom message in the title bar.
Often used in DEV/Test instance to track when the instance was refreshed/cloned.

Read Concurrent Parameters in UNIX shell script

Found this question in one of the forum and thought of creating a blog for same.
Often when concurrent program is created on a shell script there is a need to extract parameters passed from concurrent program in the shell script.
In Shell Script, $1 is the variable that stores following information
- Concurrent Program Short Name
- Request ID
- Login Information(APPS username and password)
- User Id
- User Name
- Printer Name
- Save output Flag
- Print number of Copies
- List of concurrent program parameters

Following code can be used to extract different values

requestid=`(echo $1 | cut -f2 -d' ' | cut -f2 -d= | tr -d '"' )`   #request_id
login_usr_pwd=`(echo $1 | cut -f3 -d' ' | cut -f2 -d= | tr -d '"' )` #database username/password
conc_user_id=`(echo $1 | cut -f4 -d' ' | cut -f2 -d= | tr -d '"' | cut -c1-8)` #userid
conc_user_name=`(echo $1 | cut -f5 -d' ' | cut -f2 -d= | tr -d '"' | cut -c1-8)` #username
prog_param1=`(echo $1 | cut -f9 -d' ' | tr -d '"' )`  #parameter 1 
prog_param2=`(echo $1 | cut -f10 -d' ' | tr -d '"' )`  #parameter 2 
prog_param3=`(echo $1 | cut -f11 -d' ' | tr -d '"' )`  #parameter 3 
prog_param4=`(echo $1 | cut -f12 -d' ' | tr -d '"' )`  #parameter 4 
prog_param5=`(echo $1 | cut -f13 -d' ' | tr -d '"' )`           #parameter 5 
prog_param6=`(echo $1 | cut -f14 -d' ' | tr -d '"' )`           #parameter 6

When connecting to SQLPLUS in shell script, it is always advisable to extract database username and password from parameter($login in our example above) and not hard-code the value in program.

Replace Keyboard with Mouse - Type using Mouse

Here is something interesting for you.
Replace Keyboard with Mouse and type letters using mouse.
Steps:
Click Start --> Select Run --> Type OSK --> Press OK

A Keyboard will Popup on the screen that can be used for typing. Isn't that interesting and useful sometimes.

External Table in Oracle

From Version 9i Oracle has come with a new feature facilitating us to query directly from the FLAT File. This is know as external tables.
Here are the simple steps for creating external table
1) Create Directory

create or replace directory sv_ext_dir as '/home/svaishya/external_table'

2) Grant access to Directory

grant read, write on directory sv_ext_dir to apps

The user must have a read and write permission the directory('/home/svaishya/external_table' in this case).
3) Create External Table

CREATE TABLE sv_external_Table
(col1 VARCHAR2(50)
,col2 VARCHAR2(50)
)
ORGANIZATION EXTERNAL
( TYPE oracle_loader
DEFAULT DIRECTORY sv_ext_dir
ACCESS PARAMETERS
( FIELDS TERMINATED BY '|' )
LOCATION ('abc.txt')
)

abc.txt is the name of the file that will be located in the path mentioned in the directory sv_ext_dir

4) Place the file in the Directory Path

abc.txt (used in above create table command) is copied the directory.

5) Thats it, query table to extract data.

SELECT * FROM sv_external_table

Everytime the file is updated with new data the data in the table will be automatically refreshed.

Different Types of Table in Oracle Apps

In oracle applications there are tables that ends with similar suffixes. Here I am trying to list the meaning of those, please provide your suggestion and help me if I have missed anything.

_B	The Main base tables
_ALL	Contains multi org data. There will be similar table without _ALL. Before querying this data the environment variable needs to be set. Dbms_application_info.set_client_info('org_id'), or apps_initialize can be used to set the environment variable.common column.
_V	View created on base table. Mostly forms are created based on this views
_TL	Tables that support multi language.
_VL	View created on multi language tables. The view generally uses the base table and _tl table
_F	This indicates that these are the date tracking tables. These tables are generally seen for HRMS and contain 2 common columns effective_start_date and effective_end_date
_S	sequence related tables
_DFV /_KFV	The DFF/KFF table created on the base table. This is the best way to get the concatenated value of DFF/KFF. Also using this table the values can be queried based on the DFF/KFF name and not attributes column.

See Parameters for the Submitted Request

A concurrent program is submitted which has many parameters. After the program is submitted I need to map the values with the parameter.
Click "Find Request" -> "View Details...", click on "Parameters" field, the parameters form will popup.




Sometimes you may feel that the forms have gone crazy and the parameters form does not pop up.This is because you are not in the correct responsibility that enable you to launch that request. The parameters form popup only from the responsibility where reports are registered.

View Report Output Submitted by others

Many times it happens that we need to see output submitted by others. Through system administrator View all concurrent program option we can search for the request but the problem is that the view output button is grayed off. How do we enable this button and see the output.
The answer is simple as profile does a magic here.

Change the Profile Option value for Concurrent:Report Access Level. Default value for the profile is "User", which means View Output is only accessible by the particular user that submitted the request. Set it to "Responsibility", now the View Output button is enabled from responsiblity where the report is registered.

Proposed Reverse Life Cycle

Found something interesting and thought of sharing with all of you. Hope you like it :-)

Remove Control M (^M) Characters From Unix File

If you see control M characters(^M) in a file in UNIX, don't worry there is a very simple way to convert the file in UNIX format.
The command is

dos2unix filename [newfilename]

e.g. remove control characters from file abc.txt

dos2unix abc.txt abc.txt  #this will replace abc.txt and remove control characters

dos2unix abc.txt xyz.txt  #a new file xyz.txt will be created after removing control characters

So isn't that simple lovely command took off all the worries.

Find Running, Pending, On Hold and Scheduled Requests

There are several instances where we need to get the list of scheduled concurrent request. Below is another handy query that displays all the concurrent request that are in running, pending or scheduled status.

SELECT   fcr.request_id,
         DECODE (fcr.phase_code,
                 'P', DECODE (fcr.hold_flag, 'Y', 'Inactive', fl_pend.meaning),
                 fl_pend.meaning
                ) phase,
         DECODE (fcr.phase_code,
                 'P', DECODE (fcr.hold_flag,
                              'Y', 'On Hold',
                              DECODE (SIGN (fcr.requested_start_date - SYSDATE),
                                      1, 'Scheduled',
                                      fl_stat.meaning
                                     )
                             ),
                 fl_stat.meaning
                ) status,
         fcpt.user_concurrent_program_name, fcr.increment_dates,
         fcr.resubmit_interval, fcr.resubmit_interval_unit_code,
         fcr.resubmit_interval_type_code, parent_request_id,
         fcr.requested_start_date, fu.user_name requested_by
    FROM fnd_concurrent_requests fcr,
         fnd_concurrent_programs_tl fcpt,
         fnd_lookups fl_pend,
         fnd_lookups fl_stat,
         fnd_user fu
   WHERE 1 = 1
     AND fcpt.concurrent_program_id = fcr.concurrent_program_id
     AND fcpt.LANGUAGE = USERENV ('LANG')
     AND fcr.phase_code = fl_pend.lookup_code
     AND fl_pend.lookup_type = 'CP_PHASE_CODE'
     AND fcr.status_code = fl_stat.lookup_code
     AND fl_stat.lookup_type = 'CP_STATUS_CODE'
     AND fl_pend.meaning != 'Completed'
     AND fu.user_id = fcr.requested_by
ORDER BY fcr.request_id DESC

PS: The query below is not fully tested, hence please provide your feedback incase you find any unexpected result.

Display Numbers in Figures (words)

It happens very often that there is a requirement to display amounts in words, but in most of the forms we have amounts displayed in numbers. Here I will discuss on various options/methods to meet this.

1) Using Julian method

select to_char(to_date(11221873,'J'),'JSP') from dual  -- For upper-case letters

select to_char(to_date(11221873,'J'),'Jsp') from dual  -- For mixed-case letters

select to_char(to_date(11221873,'J'),'jsp') from dual  -- For lower-case letters 

The Julian Method is limited to display text only till million, so 9,999,999 is the maximum value that can be displayed in words.

2) Using Data and Time Suffixes. Listed is the option for Data and Time Suffix
TH - converts numbers to ordinal numbers
SP - converts numbers to words
SPTH - converts numbers to ordinal words

SELECT TO_CHAR(TO_TIMESTAMP(LPAD(99999999, 9, '0'), 'FF9'),'FFSP') AS amt_in_words
  FROM DUAL -- For Upper Case Letters

SELECT TO_CHAR(TO_TIMESTAMP(LPAD(99999999, 9, '0'), 'FF9'),'Ffsp') AS amt_in_words
  FROM DUAL -- For Mixed Case Letters

SELECT TO_CHAR(TO_TIMESTAMP(LPAD(99999999, 9, '0'), 'FF9'),'ffsp') AS amt_in_words
  FROM DUAL -- For Lower Case Letters

The maximum value that can be displayed is 99,999,999. So we added another number as against the Julian function.

3) This is the best option but limited only to Oracle Application users.
Oracle Apps has provided an inbuilt function AP_AMOUNT_UTILITIES_PKG which can be used to achieve the result.

SELECT Upper(ap_amount_utilities_pkg.ap_convert_number (111234234324)) AS amt_in_words
  FROM DUAL  -- For Upper Case Letters

SELECT InitCap(ap_amount_utilities_pkg.ap_convert_number (111234234324)) AS amt_in_words
  FROM DUAL -- For Mixed Case Letters

SELECT Lower(ap_amount_utilities_pkg.ap_convert_number (111234234324)) AS amt_in_words
  FROM DUAL -- For Lower Case Letters

Well as there is an end to everything, even this function has a limitation but it can display big enough as it goes upto hundred billions. 999,999,999,999 is the maximum allowed value.