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.