Dynmic Sorting Based SQL Query

select
 employee_number EMPNO,
 full_name NAME
from per_people_f
order by
  decode(:P2_ORDER_BY,'EMPNO',empno,null)
, decode(:P2_ORDER_BY,'NAME',name,null)
, decode(:P2_ORDER_BY,'EMPNO,FULL_NAME',empno,
 name,null)

UTL_FILE

While UTL_FILE.FCOPY can be used to copy valid text files, it does not copy binary files or invalid text files.  An example of an invalid text file would be one that contains a character sequence that cannot be generated by the characters on your keyboard (i.e. the sequence of two CR s in a row on a Windows platform rather than CRLF, CRLF) and hence may be considered a binary file.

NOTE: As UTL_FILE first supported the reading and writing of binary files in 10gR1, this note can only be used with Oracle Database versions 10gR1 and later.
Solution
Create two directory objects for the IN and OUT file locations.
CREATE OR REPLACE DIRECTORY in_file_loc AS 'C:\temp\in';
CREATE OR REPLACE DIRECTORY out_file_loc AS 'C:\temp\out';

NOTE: You must have the CREATE ANY DIRECTORY privilege to execute these statements.

Create the procedure CopyFile.
-- Procedure to COPY a Text or Binary file

CREATE OR REPLACE PROCEDURE copyfile (
   in_filename                IN             VARCHAR2
 , out_filename               IN             VARCHAR2
)
IS
   in_file                                           UTL_FILE.file_type;
   out_file                                          UTL_FILE.file_type;
   buffer_size                              CONSTANT INTEGER := 32767;   -- Max Buffer Size = 32767
   buffer                                            RAW (32767);
   buffer_length                                     INTEGER;
BEGIN
   -- Open a handle to the location where you are going to read the Text or Binary file from
   -- NOTE: The 'rb' parameter means "read in byte mode" and is only available
   --       in the UTL_FILE package with Oracle 10g or later
   in_file                                                  :=
                                                              UTL_FILE.fopen ('IN_FILE_LOC', in_filename, 'rb', buffer_size);
   -- Open a handle to the location where you are going to write the Text or Binary file to
   -- NOTE: The 'wb' parameter means "write in byte mode" and is only available
   --       in the UTL_FILE package with Oracle 10g or later
   out_file                                                 :=
                                                            UTL_FILE.fopen ('OUT_FILE_LOC', out_filename, 'wb', buffer_size);
   -- Attempt to read the first chunk of the in_file
   UTL_FILE.get_raw (in_file, buffer, buffer_size);
   -- Determine the size of the first chunk read
   buffer_length                                            := UTL_RAW.LENGTH (buffer);

   -- Only write the chunk to the out_file if data exists
   WHILE buffer_length > 0
   LOOP
      -- Write one chunk of data
      UTL_FILE.put_raw (out_file, buffer, TRUE);

      -- Read the next chunk of data
      IF buffer_length = buffer_size
      THEN
         -- Buffer was full on last read, read another chunk
         UTL_FILE.get_raw (in_file, buffer, buffer_size);
         -- Determine the size of the current chunk
         buffer_length                                            := UTL_RAW.LENGTH (buffer);
      ELSE
         buffer_length                                            := 0;
      END IF;
   END LOOP;

   -- Close the file handles
   UTL_FILE.fclose (in_file);
   UTL_FILE.fclose (out_file);
EXCEPTION
   -- Raised when the size of the file is a multiple of the buffer_size
   WHEN NO_DATA_FOUND
   THEN
      -- Close the file handles
      UTL_FILE.fclose (in_file);
      UTL_FILE.fclose (out_file);
END;
/

Test procedure CopyFile with both Text and Binary files.

-- Test a TEXT file
EXEC CopyFile('original_textfilename.txt', 'new_textfilename.txt')

-- Test a BINARY File
EXEC CopyFile('original_binaryfilename.jpg', 'new_binaryfilename.jpg')

Check the IN_FILE_LOC and OUT_FILE_LOC and verify that the files were successfully copied.  Compare the actual size in bytes to make sure they are an exact copy. 

UTILFILE-INBOUND,OUTBOUND

=================== UTL FILE INBOUND===
USING UTL_FILE PACKAGE(LOAD THE DATA IN TO THE TABLE)
CREATE OR REPLACE package body APPS.xx_po_pu_det_pkg
is
/*
Procedure to read data from flat file
*/
    procedure pur_dat_prc(x_errbuf OUT VARCHAR2
                        ,X_RETCODE OUT VARCHAR2
                        ,P_FILE_PATH IN VARCHAR2
                        ,P_FIL_NAME IN VARCHAR2
                        )
is

v_file_type         utl_file.file_type;
v_data              varchar2(1000);

v_vendor_number         po_vendors.segment1%type;
v_vendor_name           po_vendors.vendor_name%type;
v_vendor_site_code      po_vendor_sites_all.vendor_site_code%type;
v_po_number             po_headers_all.segment1%type;

begin
    v_file_type := utl_file.fopen(P_FILE_PATH,P_FIL_NAME,'R');
    loop
        begin
 --       fnd_file.put_line(fnd_file.output,'Start Loop');
        utl_file.get_line(v_file_type,v_data);
        fnd_file.put_line(fnd_file.output,'Data => '||v_data);
    select substr(v_data,1,instr(v_data,',',1)-1)
    into v_vendor_number
    from dual;
   
    select substr(v_data,instr(v_data,',',1,1)+1,instr(v_data,',',1,2)-(instr(v_data,',',1,1)+1))
    into v_vendor_name
    from dual;

    select substr(v_data,instr(v_data,',',1,2)+1,instr(v_data,',',1,3)-(instr(v_data,',',1,2)+1))
    into v_vendor_site_code
    from dual;

    select substr(v_data,instr(v_data,',',1,3)+1,length(v_data)-(instr(v_data,',',1,3)))
    into v_po_number
    from dual;

    insert into XX_PO_PUR_DET_STG
    values(
    v_vendor_number
    ,v_vendor_name
    ,v_vendor_site_code
    ,v_po_number
    );
   
    exception
        when utl_file.invalid_path then
            fnd_file.put_line(fnd_file.output,'Invalid file path');      
        when utl_file.invalid_mode then
            fnd_file.put_line(fnd_file.output,'Invalid Mode');
        when utl_file.invalid_filehandle then
            fnd_file.put_line(fnd_file.output,'Invalid file handle');
        when utl_file.invalid_operation then
            fnd_file.put_line(fnd_file.output,'Invalid file operation');
        when utl_file.read_error then
            fnd_file.put_line(fnd_file.output,'Read error');
        when no_data_found then
            exit;
        when others then
           fnd_file.put_line(fnd_file.output,'Others exception => '||SQLERRM);    
        end;
    end loop;
--    fnd_file.put_line(fnd_file.output,'after end loop');
    utl_file.fclose(v_file_type);
--    fnd_file.put_line(fnd_file.output,'after close');
exception
    when others then
        fnd_file.put_line(fnd_file.log,'Exception in procedure pur_dat_prc => '||SQLERRM);
end pur_dat_prc;          
end xx_po_pu_det_pkg;
/



===================UTIL FILE OUT BOUND==============
USING UTL_FILE PACKAGE (OUT BOUND)
CREATE OR REPLACE procedure
APPS.xx_po_out(x_errbuf out varchar2
,p_retcode out varchar2
,p_file_path in varchar2
,p_file_name in varchar2
)
is
g_org_id number := fnd_profile.value('ORG_ID');
g_conc_request_id number := fnd_profile.value('CONC_REQUEST_ID');
cursor cur_podet
is
select vendor_name
,pov.segment1 vendor_number
,povs.VENDOR_SITE_CODE
,povs.ADDRESS_LINE1||' '||povs.ADDRESS_LINE2 address
,povs.country
,poh.SEGMENT1 po_number
from po_vendors pov,
po_headers_all poh,
po_vendor_sites_all povs
where pov.vendor_id = poh.vendor_id
and poh.vendor_site_id = povs.vendor_site_id
and poh.org_id = g_org_id;

v_file utl_file.file_type;
v_file_name varchar2(100) ;


begin
    fnd_file.put_line(fnd_file.log,'Concurrent Request Id => '||p_file_name||'_'||g_conc_request_id||'.txt');
    v_file_name  := p_file_name||'_'||g_conc_request_id||'.txt';
    v_file := utl_file.fopen(p_file_path,v_file_name ,'W');
    for rec_podet in cur_podet
    loop
        begin
        utl_file.PUT_LINE(v_file,
         rec_podet.vendor_name
        ||','||rec_podet.vendor_number
        ||','||rec_podet.VENDOR_SITE_CODE
        ||','||rec_podet.address
        ||','||rec_podet.country
        ||','||rec_podet.po_number
        );
    exception
        when utl_file.invalid_path then
            fnd_file.put_line(fnd_file.log,'Invalid Path');
        when utl_file.invalid_mode  then
            fnd_file.put_line(fnd_file.log,'Invalid Mode');
        when utl_file.invalid_filehandle then
            fnd_file.put_line(fnd_file.log,'Invalid file handle');
        when utl_file.invalid_operation  then
            fnd_file.put_line(fnd_file.log,'Invalid Operation');
        when utl_file.write_error        then
            fnd_file.put_line(fnd_file.log,'Write error');
        when others then
            fnd_file.put_line(fnd_file.log,'exception in loop => '||SQLERRM);
        end;  
    end loop;
    utl_file.FCLOSE(v_file);
exception
    when others then
        fnd_file.put_line(fnd_file.log,'exception in procedure => '||SQLERRM);  
end ;

SQL-MULTIPLE INSERTS

MULTIPLE INSERTS

We have table called DEPT with the following columns and data

DEPTNO         DNAME           LOC
--------            --------            ----
10                    accounting    new york
20                    research        dallas
30                    sales               Chicago
40                    operations    boston

a) CREATE STUDENT TABLE

     SQL> Create table student(no number(2),name varchar(2),marks number(3));

b) MULTI INSERT WITH ALL FIELDS

     SQL> Insert all
             Into student values(1,’a’,100)
             Into student values(2,’b’,200)
             Into student values(3,’c’,300)
             Select *from dept where deptno=10;

     -- This inserts 3 rows

c) MULTI INSERT WITH SPECIFIED FIELDS

     SQL> insert all
             Into student (no,name) values(4,’d’)
             Into student(name,marks) values(’e’,400)
             Into student values(3,’c’,300)
             Select *from dept where deptno=10;

     -- This inserts 3 rows

d) MULTI INSERT WITH DUPLICATE ROWS

     SQL> insert all
             Into student values(1,’a’,100)
             Into student values(2,’b’,200)
             Into student values(3,’c’,300)
             Select *from dept where deptno > 10;

     -- This inserts 9 rows because in the select statement retrieves 3 records (3 inserts for
         each row retrieved)

e) MULTI INSERT WITH CONDITIONS BASED

     SQL> Insert all
             When deptno > 10 then
             Into student1 values(1,’a’,100)
             When dname = ‘SALES’ then
             Into student2 values(2,’b’,200)
             When loc = ‘NEW YORK’ then
             Into student3 values(3,’c’,300)
             Select *from dept where deptno>10;

     -- This  inserts 4 rows because the first condition satisfied 3 times, second condition
         satisfied once and the last none.

f) MULTI INSERT WITH CONDITIONS BASED AND ELSE

    SQL> Insert all
            When deptno > 100 then
            Into student1 values(1,’a’,100)
            When dname = ‘S’ then
            Into student2 values(2,’b’,200)
            When loc = ‘NEW YORK’ then
            Into student3 values(3,’c’,300)
            Else
            Into student values(4,’d’,400)
            Select *from dept where deptno>10;

     -- This inserts 3 records because the else satisfied 3 times

g) MULTI INSERT WITH CONDITIONS BASED AND FIRST

     SQL> Insert first
             When deptno = 20 then
             Into student1 values(1,’a’,100)
             When dname = ‘RESEARCH’ then
             Into student2 values(2,’b’,200)
             When loc = ‘NEW YORK’ then
             Into student3 values(3,’c’,300)
             Select *from dept where deptno=20;
   
     -- This inserts 1 record because the first clause avoid to check the remaining
         conditions once the condition is satisfied.

h) MULTI INSERT WITH CONDITIONS BASED, FIRST AND ELSE

     SQL> Insert first
             When deptno = 30 then
              Into student1 values(1,’a’,100)
              When dname = ‘R’ then
              Into student2 values(2,’b’,200)
              When loc = ‘NEW YORK’ then
              Into student3 values(3,’c’,300)
              Else
              Into student values(4,’d’,400)
              Select *from dept where deptno=20;

     -- This inserts 1 record because the else clause satisfied once




i) MULTI INSERT WITH MULTIBLE TABLES

    SQL> Insert all
            Into student1 values(1,’a’,100)
            Into student2 values(2,’b’,200)
            Into student3 values(3,’c’,300)
            Select *from dept where deptno=10;

    -- This inserts 3 rows

    ** You can use multi tables with specified fields, with duplicate rows, with conditions,
         with first and else clauses.

SQL-FUNCTIONS1(DATE FUNCTIONS)

DATE FUNCTIONS

  Sysdate
  Current_date
  Current_timestamp
  Systimestamp
  Localtimestamp
  Dbtimezone
  Sessiontimezone
  To_char
  To_date
  Add_months
  Months_between
  Next_day
  Last_day
  Extract
  Greatest
  Least
  Round
  Trunc
  New_time
 Coalesce

Oracle default date format is DD-MON-YY.
We can change the default format to our desired format by using the following command.

SQL> alter session set nls_date_format = ‘DD-MONTH-YYYY’;
        But this will expire once the session was closed.

a) SYSDATE

     This will give the current date and time.
      Ex:
           SQL> select sysdate from dual;

SYSDATE
-----------
24-DEC-06

b) CURRENT_DATE

     This will returns the current date in the session’s timezone.

      Ex:
           SQL> select current_date from dual;

CURRENT_DATE
------------------
     24-DEC-06

c) CURRENT_TIMESTAMP

     This will returns the current timestamp with the active time zone information.

      Ex:
           SQL> select current_timestamp from dual;

CURRENT_TIMESTAMP
---------------------------------------------------------------------------
24-DEC-06 03.42.41.383369 AM +05:30


d) SYSTIMESTAMP

     This will returns the system date, including fractional seconds and time zone of the
      database.

      Ex:
           SQL> select systimestamp from dual;
SYSTIMESTAMP
---------------------------------------------------------------------------
24-DEC-06 03.49.31.830099 AM +05:30

e) LOCALTIMESTAMP

     This will returns local timestamp in the active time zone information, with no time
     zone information shown.

      Ex:
                   SQL> select localtimestamp from dual;

LOCALTIMESTAMP
---------------------------------------------------------------------------
24-DEC-06 03.44.18.502874 AM


f) DBTIMEZONE

    This will returns the current database time zone in UTC format. (Coordinated Universal
    Time)

    Ex:
                   SQL> select dbtimezone from dual;

DBTIMEZONE
---------------
   -07:00

g) SESSIONTIMEZONE

    This will returns the value of the current session’s time zone.

    Ex:
         SQL> select sessiontimezone from dual;

SESSIONTIMEZONE
------------------------------------
+05:30

     
       h) TO_CHAR

    This will be used to extract various date formats.
    The available date formats as follows.

    Syntax: to_char (date, format)
 
    DATE FORMATS
 
            D                      --         No of days in week
            DD                    --         No of days in month
            DDD                 --         No of days in year
            MM                   --         No of month
            MON                 --         Three letter abbreviation of month
            MONTH             --         Fully spelled out month
            RM                   --         Roman numeral month
            DY                    --         Three letter abbreviated day
            DAY                  --         Fully spelled out day
            Y                      --         Last one digit of the year
            YY                    --         Last two digits of the year
            YYY                  --         Last three digits of the year
            YYYY                --         Full four digit year
            SYYYY             --          Signed year
            I                       --         One digit year from ISO standard
            IY                     --         Two digit year from ISO standard
            IYY                   --         Three digit year from ISO standard
            IYYY                 --         Four digit year from ISO standard
            Y, YYY              --         Year with comma
            YEAR                --         Fully spelled out year
            CC                    --         Century
            Q                      --         No of quarters
            W                     --         No of weeks in month
            WW                  --         No of weeks in year
            IW                    --         No of weeks in year from ISO standard
            HH                    --         Hours
            MI                    --         Minutes
            SS                    --         Seconds
            FF                     --         Fractional seconds
            AM or PM         --         Displays AM or PM depending upon time of day
            A.M or P.M       --         Displays A.M or P.M depending upon time of day
            AD or BC          --         Displays AD or BC depending upon the date
            A.D or B.C        --         Displays AD or BC depending upon the date
            FM                    --         Prefix to month or day, suppresses padding of month or day
            TH                    --         Suffix to a number
            SP                    --         suffix to a number to be spelled out
            SPTH                --         Suffix combination of TH and SP to be both spelled out
            THSP                --         same as SPTH

Ex:
    SQL> select to_char(sysdate,'dd month yyyy hh:mi:ss am dy') from dual;

TO_CHAR(SYSDATE,'DD MONTH YYYYHH:MI
----------------------------------------------------
24 december  2006 02:03:23 pm sun  

    SQL> select to_char(sysdate,'dd month year') from dual;


TO_CHAR(SYSDATE,'DDMONTHYEAR')
-------------------------------------------------------
24 december  two thousand six

     SQL> select to_char(sysdate,'dd fmmonth year') from dual;

TO_CHAR(SYSDATE,'DD FMMONTH YEAR')
-------------------------------------------------------
24 december two thousand six

     SQL> select to_char(sysdate,'ddth DDTH') from dual;

TO_CHAR(S
------------
24th 24TH

     SQL> select to_char(sysdate,'ddspth DDSPTH') from dual;

TO_CHAR(SYSDATE,'DDSPTHDDSPTH
------------------------------------------
twenty-fourth TWENTY-FOURTH

     SQL> select to_char(sysdate,'ddsp Ddsp DDSP ') from dual;

TO_CHAR(SYSDATE,'DDSPDDSPDDSP')
------------------------------------------------
twenty-four Twenty-Four TWENTY-FOUR

i) TO_DATE

    This will be used to convert the string into data format.

    Syntax: to_date (date)

    Ex:
         SQL> select to_char(to_date('24/dec/2006','dd/mon/yyyy'), 'dd * month * day')
                 from dual;

TO_CHAR(TO_DATE('24/DEC/20
--------------------------
24 * december  * Sunday

-- If you are not using to_char oracle will display output in default date format.
     
j) ADD_MONTHS

    This will add the specified months to the given date.

    Syntax: add_months (date, no_of_months)

    Ex:
          SQL> select add_months(to_date('11-jan-1990','dd-mon-yyyy'), 5) from dual;

ADD_MONTHS
----------------
11-JUN-90

SQL> select add_months(to_date('11-jan-1990','dd-mon-yyyy'), -5) from dual;

ADD_MONTH
---------------
11-AUG-89        

Ø  If no_of_months is zero then it will display the same date.
Ø  If no_of_months is null then it will display nothing.

k) MONTHS_BETWEEN

    This will give difference of months between two dates.

    Syntax: months_between (date1, date2)

    Ex:
         SQL> select months_between(to_date('11-aug-1990','dd-mon-yyyy'), to_date('11-
                 jan-1990','dd-mon-yyyy')) from dual;



MONTHS_BETWEEN(TO_DATE('11-AUG-1990','DD-MON-YYYY'),TO_DATE('11-JAN-1990','DD-MON-YYYY'))
-----------------------------------------------------------------------------------------------
                                                                                        7
           SQL> select months_between(to_date('11-jan-1990','dd-mon-yyyy'), to_date('11-
                 aug-1990','dd-mon-yyyy')) from dual;

MONTHS_BETWEEN(TO_DATE('11-JAN-1990','DD-MON-YYYY'),TO_DATE('11-AUG-1990','DD-MON-YYYY'))
-------------------------------------------------------------------------------------------------
                                                                                       -7

l) NEXT_DAY
               
    This will produce next day of the given day from the specified date.

    Syntax: next_day (date,  day)

    Ex:
         SQL> select next_day(to_date('24-dec-2006','dd-mon-yyyy'),'sun') from dual;

NEXT_DAY(
-------------
31-DEC-06

-- If the day parameter is null then it will display nothing.

m) LAST_DAY

    This will produce last day of the given date.

    Syntax: last_day (date)

    Ex:
         SQL> select last_day(to_date('24-dec-2006','dd-mon-yyyy'),'sun') from dual;
LAST_DAY(
-------------
31-DEC-06

n) EXTRACT

    This is used to extract a portion of the date value.

    Syntax: extract ((year | month | day | hour | minute | second), date)

    Ex:
         SQL> select extract(year from sysdate) from dual;
EXTRACT(YEARFROMSYSDATE)
------------------------------------
                    2006

-- You can extract only one value at a time.

o) GREATEST

     This will give the greatest date.

     Syntax: greatest (date1, date2, date3 … daten)

     Ex:
         SQL> select greatest(to_date('11-jan-90','dd-mon-yy'),to_date('11-mar-90','dd-
                 mon-yy'),to_date('11-apr-90','dd-mon-yy')) from dual;

  GREATEST(
  -------------
   11-APR-90

p) LEAST

     This will give the least date.
     Syntax: least (date1, date2, date3 … daten)

     Ex:
         SQL> select least(to_date('11-jan-90','dd-mon-yy'),to_date('11-mar-90','dd-mon-
                 yy'),to_date('11-apr-90','dd-mon-yy')) from dual;

  LEAST(
  -------------
   11-JAN-90

q) ROUND

    Round will rounds the date to which it was equal to or greater than the given date.

    Syntax: round (date, (day | month | year))

    If the second parameter was year then round will checks the month of the given date in
    the following ranges.

JAN      --          JUN  
            JUL       --          DEC

    If the month falls between JAN and JUN then it returns the first day of the current year.
    If the month falls between JUL and DEC then it returns the first day of the next year.

    If the second parameter was month then round will checks the day of the given date in
    the following ranges.

            1          --         15    
            16        --         31

    If the day falls between 1 and 15 then it returns the first day of the current month.
    If the day falls between 16 and 31 then it returns the first day of the next month.

    If the second parameter was day then round will checks the week day of the given date
    in the following ranges.

            SUN      --          WED
            THU      --          SUN

    If the week day falls between SUN and WED then it returns the previous sunday.
    If the weekday falls between THU and SUN then it returns the next sunday.

Ø  If the second parameter was null then it returns nothing.
Ø  If the you are not specifying the second parameter then round will resets the time to the begining of the current day in case of user specified date.
Ø  If the you are not specifying the second parameter then round will resets the time to the begining of the next day in case of sysdate.

    Ex:
         SQL> select round(to_date('24-dec-04','dd-mon-yy'),'year'), round(to_date('11-mar-
                 06','dd-mon-yy'),'year') from dual;

ROUND(TO_ ROUND(TO_
------------   ---------------
01-JAN-05   01-JAN-06

           SQL> select round(to_date('11-jan-04','dd-mon-yy'),'month'), round(to_date('18-
                 jan-04','dd-mon-yy'),'month') from dual;

ROUND(TO_ ROUND(TO_
-------------  ---------------
01-JAN-04    01-FEB-04

           SQL> select round(to_date('26-dec-06','dd-mon-yy'),'day'), round(to_date('29-dec-
                 06','dd-mon-yy'),'day') from dual;

ROUND(TO_ ROUND(TO_
--------------  --------------
24-DEC-06     31-DEC-06

           SQL> select to_char(round(to_date('24-dec-06','dd-mon-yy')), 'dd mon yyyy
                 hh:mi:ss am') from dual;
TO_CHAR(ROUND(TO_DATE('
---------------------------------
24 dec 2006 12:00:00 am
r) TRUNC

    Trunc will chops off the date to which it was equal to or less than the given date.

    Syntax: trunc (date, (day | month | year))

  If the second parameter was year then it always returns the first day of the current year.
  If the second parameter was month then it always returns the first day of the current month.
  If the second parameter was day then it always returns the previous sunday.
  If the second parameter was null then it returns nothing.
  If the you are not specifying the second parameter then trunk will resets the time to the begining of the current day.

    Ex:
         SQL> select trunc(to_date('24-dec-04','dd-mon-yy'),'year'), trunc(to_date('11-mar-
                 06','dd-mon-yy'),'year') from dual;

TRUNC(TO_ TRUNC(TO_
-------------  --------------
01-JAN-04    01-JAN-06

                     SQL> select trunc(to_date('11-jan-04','dd-mon-yy'),'month'), trunc(to_date('18-jan-
                         04','dd-mon-yy'),'month') from dual;

TRUNC(TO_ TRUNC(TO_
-------------  -------------
01-JAN-04    01-JAN-04

  SQL> select trunc(to_date('26-dec-06','dd-mon-yy'),'day'), trunc(to_date('29-dec-
          06','dd-mon-yy'),'day') from dual;
                        TRUNC(TO_ TRUNC(TO_
-------------  --------------
24-DEC-06 24-DEC-06
       
          SQL> select to_char(trunc(to_date('24-dec-06','dd-mon-yy')), 'dd mon yyyy hh:mi:ss
                 am') from dual;

TO_CHAR(TRUNC(TO_DATE('
---------------------------------
24 dec 2006 12:00:00 am

s) NEW_TIME

     This will give the desired timezone’s date and time.

     Syntax: new_time (date, current_timezone, desired_timezone)

     Available timezones are as follows.

    TIMEZONES

                        AST/ADT         --         Atlantic standard/day light time
                        BST/BDT          --         Bering standard/day light time
                        CST/CDT          --         Central standard/day light time
                        EST/EDT          --         Eastern standard/day light time
                        GMT                --         Greenwich mean time
                        HST/HDT         --         Alaska-Hawaii standard/day light time
                        MST/MDT         --         Mountain standard/day light time
                        NST                 --          Newfoundland standard time
                        PST/PDT          --         Pacific standard/day light time
                        YST/YDT          --         Yukon standard/day light time

    Ex:
        SQL> select to_char(new_time(sysdate,'gmt','yst'),'dd mon yyyy hh:mi:ss am') from
                dual;

TO_CHAR(NEW_TIME(SYSDAT
-----------------------------------
24 dec 2006 02:51:20 pm

          SQL> select to_char(new_time(sysdate,'gmt','est'),'dd mon yyyy hh:mi:ss am') from
                dual;

TO_CHAR(NEW_TIME(SYSDAT
-----------------------
24 dec 2006 06:51:26 pm

t) COALESCE

    This will give the first non-null date.

    Syntax: coalesce (date1, date2, date3 … daten)

    Ex:
         SQL> select coalesce('12-jan-90','13-jan-99'), coalesce(null,'12-jan-90','23-mar-
                 98',null) from dual;

COALESCE( COALESCE(
-------------  ------------
12-jan-90     12-jan-90

Alert creation in oracle Apps

ALERTS IN ORACLE APPS

Oracle Alert is your complete exception control solution. Alerts signal important or unexpected activity in your database. They ensure that you are regularly and quickly informed about critical database events instead of sorting through length reports.

The basic function of alerts includes but not limited to

·         Keep you informed of critical activity in your database

·         Deliver key information from your applications, in the format you choose

·         Provide you with regular reports on your database information

·         Automate system maintenance, and routine online tasks

Alerts keep a constant check on your database information and prompt you when the

specified criteria are met. You can use either an Oracle application or a custom oracle

application to define alerts. However, few applications such as purchasing, allow you to

simply activate and use alerts supply by default.

You can define two types of alerts:

1.    Event alert

2.    Periodic alert.

An EVENT ALERT immediately notifies you of activity in your database as it occurs. When you

create an event alert, you specify the following:

·         A database event that you want to monitor, that is, an insert or an update to a specific database table.

·         A SQL Select statement that retrieves specific database information as a result of the database event.

·         Actions that you want Oracle Alert to perform as a result of the database event. An action can entail sending someone an electronic mail message, running a concurrent program, running an operating script, or running a SQL statement script. You include all the actions you want Oracle Alert to perform, in an action set.

A periodic alert, on the other hand, checks the database for information according to a

schedule you define. When you create a periodic alert, you specify the following:

·         A SQL Select statement that retrieves specific database information.

·         The frequency that you want the periodic alert to run the SQL statement.

·         Actions that you want Oracle Alert to perform once it runs the SQL statement. An action can entail sending the retrieved information to someone in an electronic mail message, running a concurrent program, running an operating script, or running a SQL statement script. You include all the actions you want Oracle Alert to perform, in an action set

By creating event alerts, you can have an immediate view of the activity in your

database, so you keep on top of important or unusual events as they happen. By

creating periodic alerts, you can have current measurements of staff and organization

performance, so you can zero in on potential trouble spots. You can automate routine

transactions, preserving your valuable time for more important issues. Oracle Alert gives

you the information you need online, so you do not have to contend with a pile of

paperwork.

Workflow vs. Alerts:

Unlike alerts, workflow is defined in a system to detect a condition and requires user

intervention. Every time a response is not recorded during a designated time period,

workflow may send a notification to the user's manager depending upon the workflow

definition. It is difficult to accomplish such notification using alerts.

Defining Alerts:

You can create alerts that are as simple or as complex as you need them to be, and you can tailor your alerts in a variety of ways so they perform the kind of exception reporting your organization needs. There are two types of alerts: event and periodic. Both types are defined by a SQL Select statement that you specify.

You can create event alerts that monitor your applications for the exception conditions you specify. You can create periodic alerts that check your database for predefined conditions according to the schedule you determine.

Major Features

Verify SQL

You can verify that your alert's SQL Select statement runs correctly, and returns the data you specify. You can do this verification directly in Oracle Alert immediately after you enter your Select statement - you don't have to suspend your Oracle Alert session or navigate to SQL*Plus.

Specify Installations

You can specify which Application installations you want your alert to run against, so you can control which Application installations your alert checks in a database with multiple Application installations.

Information Routing

With Oracle Alert, you can include a file created by another application as part of an alert message. You can also define an alert that distributes an electronic copy of a report, log file, or any other ASCII file.

Dynamic Message Distributions

Oracle Alert lets you define a message distribution list without knowing ahead of time who the actual individuals on the list will be. Oracle Alert can retrieve the appropriate electronic mail IDs from your application tables, and send the message automatically.

Duplicate Suppression

Oracle Alert can automatically determine which action to perform based on whether it locates the same exceptions during a sequence of alert checks. You can have Oracle Alert perform a different action during each alert check that finds the same database exception.

[

Customizable Inputs by Action Set

You can further customize your alerts by specifying parameters for each set of actions you define. These parameters, or inputs, provide you with extra flexibility in creating your alerts because you can assign specific values to them. For example, if a vendor delivery is overdue, Oracle Alert can notify the purchasing agent when it is two days late and the purchasing manager when it is seven days late. The number of days late is the input; two and seven are distinct input values you assign for each type of recipient.

Distribution Lists

Oracle Alert lets you create an electronic distribution list that you can use on many messages. At any time, you can add or remove names from your lists, or you can make copies of your lists and use the copies to create new lists.

Standard Message Address Formats

Oracle Alert lets you address messages using easily recognizable symbols: to, cc, bcc, just as you would use when writing a memo or sending electronic mail.

[[

Printed Alert Messages

Oracle Alert lets you send messages to people who do not use electronic mail. You can direct a message to a printer with the recipient's name on the burst page.

[

Electronic Mail Integration

Oracle Alert leverages the Workflow Notification Mailer to send and receive alert e-mail messages. The notification mailer uses the Simple Mail Transfer Protocol (SMTP) for outbound messages and the Internet Message Access Protocol (IMAP) for inbound messages.

Creating a Periodic Alert:

These Alerts are triggered hourly, daily, weekly, monthly or yearly based on your input.

To create a periodic alert, you perform the following tasks in the order listed:

·         Define your periodic alert and specify its frequency.

·         Specify the details for your alert.

·         Define actions for your alert.

·         Create action sets containing the actions you want your alert to perform.

Before you define a periodic alert, make sure you do the following:

·         Configure the Workflow Notification Mailer to send and receive e-mail messages according to your alert requirements.

·         Specify Oracle Alert options to configure how Oracle Alert checks alerts and handles alert messages.

Enter a SQL Select statement that retrieves all the data your alert needs to perform the actions you plan to define.

Your periodic alert Select statement must include an INTO clause that contains one output for each column selected by your Select statement. Identify any inputs with a colon before the name, for example, :INPUT_NAME. Identify any outputs with an ampersand (&) before the name, for example, &OUTPUT_NAME. Do not use set operators in your Select statement.

Tip: If you want to use an input value in an action for this alert, select the input into an output. Then you can use the output when you define actions for this alert.

When selecting number columns, Oracle Alert uses the number formats defined in your database. Optionally, you can format your number outputs as real numbers by specifying a SQL*Plus format mask in your Select statement. For each number output, simply add a pound sign (#) and format mask to your output name. For example, if you select purchase price into the output &PRICE, add "#9999.99" after &PRICE for Oracle Alert to display the value to two decimal places. Your number output looks like: &PRICE#9999.99. Here is an example of a periodic alert Select statement that looks for users who have not changed their passwords within the number of days specified by the value in :THRESHOLD_DAYS.:

SELECT user_name,

password_date,

:THRESHOLD_DAYS

INTO &USER,

&LASTDATE,

&NUMDAYS

FROM fnd_user

WHERE sysdate = NVL(password_date,

sysdate) + :THRESHOLD_DAYS

ORDER BY user_name

Note: Although Oracle Alert does not support PL/SQL statements as the alert SQL statement definition, you can create a PL/SQL packaged function that contains PL/SQL logic and enter a SQL Select statement that calls that packaged function. For example, you can enter a SQL Select statement that looks like:

SELECT package1.function1(:INPUT1, column1)

INTO &OUTPUT1

FROM table1

In this example, package1 is the name of the PL/SQL package and function1 is the name of user-defined PL/SQL function stored in the package.

Creating an Event Alert:

These Alerts are fired/triggered based on some change in data in the database.

To create an event alert, you perform the following tasks in the order listed:

·         Define the database events that will trigger your alert

·         Specify the details for your alert.

·         Define actions for your alert.

·         Create action sets containing the actions you want your alert to perform

·         This section focuses on the first task of defining the database events that trigger your event alert and divides the task into smaller sub-tasks.

Before you define an event alert, make sure you do the following: 

·         Configure the Workflow Notification Mailer to send and receive e-mail messages according to your alert requirements.

·         Specify Oracle Alert options to configure how Oracle Alert checks alerts and handles alert messages.

To specify an event table:

Specify the name of the application and the database table that you want Oracle Alert to monitor.

Although the application you enter here need not be the same application that owns the alert, both applications must reside in the same Oracle database and the application that owns the alert has to have Select privileges on the tables listed in the alert Select statement.

Important: You cannot use a view as the event table for your alert.

Important: Do not define an event alert on the table FND_CONCURRENT_REQUESTS. Oracle Alert submits a concurrent request to the concurrent manager when an event alert is triggered by an insert or update to an event table. For concurrent processing to occur, every submitted concurrent request automatically gets inserted as a row in the

FND_CONCURRENT_REQUESTS table. If you define an event alert on this table, you create a situation where the event alert will cause an exception to occur recursively.

Although Oracle Alert does not support PL/SQL statements as the alert SQL statement definition, you can create a PL/SQL packaged function that contains PL/SQL logic and enter a SQL Select statement that calls that packaged function. For example, you can enter a SQL Select statement that looks like:

SELECT package1.function1(:INPUT1, column1)

INTO &OUTPUT1

FROM table1

Specifying Alert Details

Once you define an event or periodic alert in the Alerts window, you need to display to the Alert Details window to complete the alert definition. The Alert Details window includes information such as which Application installations you want the alert to run against, what default values you want your inputs variables to use, and what additional characteristics you want your output variables to have.

In the Inputs tabbed region, Oracle Alert automatically displays the inputs used in your Select statement, unless they are the implicit inputs: :ROWID, :MAILID, :ORG_ID and :DATE_LAST_CHECKED. The values of the implicit inputs are as follows:

• ROWID-Contains the ID number of the row where the insert or update that triggers an event alert occurs.

• MAILID-Contains the email username of the person who enters an insert or updates that triggers an event alert.

• ORG_ID-Contains the organization ID that is selected when the alert runs.

• DATE_LAST_CHECKED-Contains the date and time that the alert was most recently checked

Major Features

Event Alerts

Event alerts immediately notify you of activity in your database as it happens. You define what a database event is - an insert or an update to a table - and Oracle Alert informs you when it happens. You can modify our precoded alert conditions or simply create your own, and Oracle Alert will send messages or perform predefined actions in

Periodic Alerts

Periodic alerts periodically report key information according to a schedule you define.

You can modify our precoded alerts or simply create your own, and Oracle Alert will send messages or perform predefined actions from an action set according to the schedule you set.

You can define periodic alerts on any Oracle Financials, Oracle Manufacturing, Oracle Human Resources, or Oracle Public Sector Financials application as well as any custom Oracle application.

Periodic alerts can be set to run as often as you need during a 24-hour period, or they can be set to run once a month - the frequency is up to you. Used over time, periodic alerts can provide a regular and reliable measure of performance.

For example, you can define a periodic alert for Oracle Purchasing that sends a message to the Purchasing Manager listing the number of approved requisition lines that each purchasing agent placed on purchase orders. You can define this alert to run weekly, and provide performance measurement on a consistent and timely basis.

Easy Alert Definition

Oracle Alert can load the SQL statement for your alert definition from an operating system file, allowing you to automatically perform the functions you currently do by hand. Oracle Alert will also transfer your entire alert definition across databases. You can instantly leverage the work done in one area to all your systems.

Customizable Alert Frequency

With Oracle Alert, you can choose the frequency of each periodic alert. You may want to check some alerts every day, some only once a month, still others only when you explicitly request them. You have the flexibility to monitor critical exceptions as frequently as necessary, even multiple times during a 24-hour period. You can also check less significant exceptions on a more infrequent schedule; for example, once a month.

Customizable Alert Actions

You can define a variety of actions for Oracle Alert to perform based on the exceptions it finds in your database. Oracle Alert can send an electronic mail message, run a SQL script or an operating system script, or submit a concurrent request, or any combination of the above. You can create your own message, SQL script, or operating system script actions in Oracle Alert, or have Oracle Alert send messages or perform scripts that reside in external files. Each action is fully customizable to the exceptions found in your database, so you have complete flexibility in your exception management.

Detail or Summary Actions

You can choose to have Oracle Alert perform actions based on a single exception or a combination of exceptions found in your database. You can define a detail action such that Oracle Alert performs that action for each individual exception found. You can also define a summary action such that Oracle Alert performs that action once for each unique combination of exceptions found. You decide which exceptions you want Oracle Alert to consider as a unique combination. You can format a detail or summary message action to display the exception(s) in an easy-to-read message.

No Exception Actions

Oracle Alert can perform actions if it finds no exceptions in your database. You can define Oracle Alert to send electronic mail messages, run SQL scripts or operating system scripts, or submit concurrent requests, or any combination of the above.

Alert History

Oracle Alert can keep a record of the actions it takes and the exceptions it finds in your database, for as many days as you specify. When you ask Oracle Alert to reconstruct alert history you see a complete record of alert activity exactly as it was performed. You can even review all responses Oracle Alert received to your messages and the actions they invoked. Oracle Alert also lets you decide which information you want to review.

You can narrow your review criteria so you see only the history you specifically want to examine, without sorting through all the history information available for an alert.

Duplicate Checking

Oracle Alert can search for exceptions that remain in your database over time, and can take certain actions based on the presence of those "duplicate exceptions." You can track exceptions in your database for the length of time that you save history for your alerts.

Action Escalation

You can define a sequence of actions and have Oracle Alert perform the next action in that sequence each time it finds the same exception or exceptions in your database. For example, you can have Oracle Alert send messages of increasing severity if it finds the same exceptions over a period of time. Using action escalation, you can make sure that exceptions needing attention don't languish unattended in your database.

Summary Threshold

Oracle Alert can automatically determine whether to perform a detail or a summary action based on the number of exceptions it finds in your database. If your alert locates few exceptions, it can simply perform detail actions-one for each exception. If your alert locates many exceptions, it can perform a summary action on all of those exceptions.

Oracle Alert automatically determines when it should perform a detail or a summary action.

Response Processing

Oracle Alert can take certain predefined actions based on a user's response to an alert message. The response can cause Oracle Alert to send another alert message, run a SQL script or an operating system script, or submit a concurrent request, or any combination of the above. Because Oracle Alert performs response actions automatically, you can delegate routine user transactions to Oracle Alert and thereby increase your organization's efficiency.

Self-Referencing Alerts

You can create an alert that checks for exceptions that are new in your database since the last time the alert was checked. The alert uses its own DATE_LAST_CHECKED value as the start time for checking for new exceptions.

Customizable Options and User Profile

You can specify exactly how you want your Oracle Alert user interface to look and behave. From choosing a printer to specifying the header text in your Oracle Alert messages.

Electronic Mail Integration

Oracle Alert allows you to send alert e-mail messages through your mail system using the Simple Mail Transfer Protocol (SMTP) for outbound messages and the Internet Message Access Protocol (IMAP) for inbound messages.

Important Alert Tables:

·           ALR_ALERTS

·           ALR_ACTIONS

·           ALR_ACTION_SETS

·           ALR_ACTION_SET_INPUTS

·           ALR_ACTION_SET_OUTPUTS

·           ALR_ACTION_SET_MEMBERS

·           ALR_ALERT_CHECKS

·           ALR_ALERT_INPUTS

·           ALR_ALERT_OUTPUTS

·           ALR_ACTION_SET_CHECKS

·           ALR_RESPONSE_SETS

·           ALR_RESPONSE_ACTIONS

·           ALR_VALID_RESONSES

Oracle Alert uses the following internal views:

·            ALR_ALERT_ACTIONS_VIEW

·            ALR_ALERT_HISTORY_VIEW

·            ALR_CHECK_ACTION_HISTORY_VIEW

·            ALR_INSTALLATIONS_VIEW

·            ALR_PERIODIC_ALERTS_VIEW

·            ALR_RESPONSE_ACTIONS_VIEW

·            ALR_SCHEDULED_PROGRAMS

·            ALR_VARIABLES_AND_OUTPUTS