Sunday, 27 December 2015

How to run concurrent request through PL/SQL

declare
lv_ret Number;
RESULT Boolean;
lv_msg VARCHAR2(1000);
begin

FND_GLOBAL.APPS_INITIALIZE(FND_GLOBAL.USER_ID, FND_GLOBAL.RESP_ID, FND_GLOBAL.RESP_APPL_ID);
        --FND_GLOBAL.Apps_Initialize(1741, 20420, 1);
        RESULT := APPS.fnd_request.set_mode (TRUE);
        lv_ret := FND_REQUEST.SUBMIT_REQUEST(application => 'PER', --Relevant application short name
                                             program     => 'XX_TICKET_BALANCE', --Your program short Name
                                             description => 'XX TICKET BALANCE', --Any Description
                                             start_time  => sysdate, --standard
                                             sub_request => false, --standard
                                             argument1 => '3890', --Relevant Paramter in sequence
                                             argument2 => '01-Jan-2015',
                                             argument3 => NULL,
                                             argument4 => '31-Dec-2015');
                                           
                                           
                                             dbms_output.put_line('1st Message: '||lv_ret);
                                             FND_MESSAGE.RETRIEVE(MSGOUT => lv_msg);
                                             dbms_output.put_line('2nd Message: ' || lv_msg);
          commit;
end;

Wednesday, 23 December 2015

Difference between RR, YY and RRRR, YYYY in oracle database

'RR' format was designed to complement 'YY' format; it does the same sort of thing inside a different window of years.

In 'YY' format, a 2-digit year is assumed to be in the 100 consecutive years starting with the most recent xx00 and ending with the next xx99.
In 'RR' format, a 2-digit year is assumed to be in the 100 consecutive years starting with the most recent xx50 and ending with the next xx49. That is, the window of possible dates is shifted by 50 years.
Currently, and until until 2049 (inclusive), 'RR' years will always be in the range 1950 through 2049, inclusive. That is, the years returned by TO_DATE (x, 'RR') will be between 1950 and 2049.
Starting in 2050, 'RR' years will always be in the range 2050 through 2149, inclusive.

Both 'YY' and 'RR' format are bad. You should not be using 2-digit years. Always use 4-digit years. The small amount of extra typing that users have to do whenever they enter a 4-digit year is very little compared to the work needed to correct errors that always happen when you use 2-digit years.

Hence in case of RRRR it does not matter if you use RR or RRRR it will return the same result.

Example: 
select to_date('20-Mar-59','DD-MON-RR') test FROM DUAL
03/20/1959

select to_date('20-Mar-59','DD-MON-RRRR') test FROM DUAL
03/20/1959

select to_date('20-Mar-49','DD-MON-RR') test FROM DUAL
03/20/2049

select to_date('20-Mar-49','DD-MON-RRRR') test FROM DUAL
03/20/2049

NOTE:
For YY & YYYY formats it does matter.
select to_date('20-Mar-49','DD-MON-YY') test FROM DUAL
03/20/2049

select to_date('20-Mar-49','DD-MON-YYYY') test FROM DUAL
03/20/0049

select to_date('20-Mar-49','DD-MON-YY') test FROM DUAL
03/20/2049

select to_date('20-Mar-59','DD-MON-YY') test FROM DUAL
03/20/2059


select to_date('20-Mar-59','DD-MON-YYYY') test FROM DUAL
03/20/0059



Reference: http://www.club-oracle.com/resources/difference-between-%E2%80%98yyyy%E2%80%99-and-%E2%80%98rrrr%E2%80%99-in-date-format.10872/
https://community.oracle.com/thread/974281?start=0&tstart=0