Query to find Request Group for concurrent program

 SELECT cpt.user_concurrent_program_name     "Concurrent Program Name",
       DECODE(rgu.request_unit_type,
              'P', 'Program',
              'S', 'Set',
              rgu.request_unit_type)        "Unit Type",
       cp.concurrent_program_name           "Concurrent Program Short Name",
       rg.application_id                    "Application ID",
       rg.request_group_name                "Request Group Name",
       fat.application_name                 "Application Name",
       fa.application_short_name            "Application Short Name",
       fa.basepath                          "Basepath"
  FROM fnd_request_groups          rg,
       fnd_request_group_units     rgu,
       fnd_concurrent_programs     cp,
       fnd_concurrent_programs_tl  cpt,
       fnd_application             fa,
       fnd_application_tl          fat
 WHERE rg.request_group_id       =  rgu.request_group_id
   AND rgu.request_unit_id       =  cp.concurrent_program_id
   AND cp.concurrent_program_id  =  cpt.concurrent_program_id
   AND rg.application_id         =  fat.application_id
   AND fa.application_id         =  fat.application_id
   AND cpt.language              =  USERENV('LANG')
   AND fat.language              =  USERENV('LANG')
   AND cpt.user_concurrent_program_name = 'IFFCO FIN : Customer SOA Open Invoices - Malaysia';

Ur's
Amar Alam

PL/SQL - Date & Time

PL/SQL provides two classes of date and time related data types:

• Datetime data types
• Interval data types

The Datetime data types are:

• DATE
• TIMESTAMP
• TIMESTAMP WITH TIME ZONE
• TIMESTAMP WITH LOCAL TIME ZONE

The Interval data types are:

• INTERVAL YEAR TO MONTH
• INTERVAL DAY TO SECOND

Field Values for Datetime and Interval Data Types

Both datetime and interval data types consist of fields. The values of these fields determine the value of the datatype. The following table lists the fields and their possible values for datetimes and intervals.

Field Name	Valid Datetime Values	Valid Interval Values
YEAR	-4712 to 9999 (excluding year 0)	Any nonzero integer
MONTH	01 to 12	0 to 11
DAY	01 to 31 (limited by the values of MONTH and YEAR, according to the rules of the calendar for the locale)	Any nonzero integer
HOUR	00 to 23	0 to 23
MINUTE	00 to 59	0 to 59
SECOND	00 to 59.9(n), where 9(n) is the precision of time fractional seconds The 9(n) portion is not applicable for DATE.	0 to 59.9(n), where 9(n) is the precision of interval fractional seconds
TIMEZONE_HOUR	-12 to 14 (range accommodates daylight savings time changes) Not applicable for DATE or TIMESTAMP.	Not applicable
TIMEZONE_MINUTE	00 to 59 Not applicable for DATE or TIMESTAMP.	Not applicable
TIMEZONE_REGION	Not applicable for DATE or TIMESTAMP.	Not applicable
TIMEZONE_ABBR	Not applicable for DATE or TIMESTAMP.	Not applicable

The Datetime Data Types and Functions

Following are the Datetime data types:

• DATE - it stores date and time information in both character and number datatypes. It is made of information on century, year, month, date, hour, minute, and second. It is specified as:
• TIMESTAMP - it is an extension of the DATE datatype. It stores the year, month, and day of the DATE datatype, along with hour, minute, and second values. It is useful for storing precise time values.
• TIMESTAMP WITH TIME ZONE - it is a variant of TIMESTAMP that includes a time zone region name or a time zone offset in its value. The time zone offset is the difference (in hours and minutes) between local time and UTC. This datatype is useful for collecting and evaluating date information across geographic regions.
• TIMESTAMP WITH LOCAL TIME ZONE - it is another variant of TIMESTAMP that includes a time zone offset in its value.

Following table provides the Datetime functions (where, x has datetime value):

S.N	Function Name & Description
1	ADD_MONTHS(x, y); Adds y months to x.
2	LAST_DAY(x); Returns the last day of the month.
3	MONTHS_BETWEEN(x, y); Returns the number of months between x and y.
4	NEXT_DAY(x, day); Returns the datetime of the next day after x.
5	NEW_TIME; Returns the time/day value from a time zone specified by the user.
6	ROUND(x [, unit]); Rounds x;
7	SYSDATE(); Returns the current datetime.
8	TRUNC(x [, unit]); Truncates x.

Timestamp functions (where, x has a timestamp value):

S.N	Function Name & Description
1	CURRENT_TIMESTAMP(); Returns a TIMESTAMP WITH TIME ZONE containing the current session time along with the session time zone.
2	EXTRACT({ YEAR | MONTH | DAY | HOUR | MINUTE | SECOND } | { TIMEZONE_HOUR | TIMEZONE_MINUTE } | { TIMEZONE_REGION | } TIMEZONE_ABBR ) FROM x) Extracts and returns a year, month, day, hour, minute, second, or time zone from x;
3	FROM_TZ(x, time_zone); Converts the TIMESTAMP x and time zone specified by time_zone to a TIMESTAMP WITH TIMEZONE.
4	LOCALTIMESTAMP(); Returns a TIMESTAMP containing the local time in the session time zone.
5	SYSTIMESTAMP(); Returns a TIMESTAMP WITH TIME ZONE containing the current database time along with the database time zone.
6	SYS_EXTRACT_UTC(x); Converts the TIMESTAMP WITH TIMEZONE x to a TIMESTAMP containing the date and time in UTC.
7	TO_TIMESTAMP(x, [format]); Converts the string x to a TIMESTAMP.
8	TO_TIMESTAMP_TZ(x, [format]); Converts the string x to a TIMESTAMP WITH TIMEZONE.

Examples:

The following code snippets illustrate the use of the above functions:

SELECT SYSDATE FROM DUAL;

Output:

08/31/2012 5:25:34 PM

SELECT TO_CHAR(CURRENT_DATE, 'DD-MM-YYYY HH:MI:SS') FROM DUAL;

Output:

31-08-2012 05:26:14

SELECT ADD_MONTHS(SYSDATE, 5) FROM DUAL;

Output:

01/31/2013 5:26:31 PM

SELECT LOCALTIMESTAMP FROM DUAL;

Output:

8/31/2012 5:26:55.347000 PM

The Interval Data Types and Functions

Following are the Interval data types:

• INTERVAL YEAR TO MONTH - it stores a period of time using the YEAR and MONTH datetime fields.
• INTERVAL DAY TO SECOND - it stores a period of time in terms of days, hours, minutes, and seconds.

Interval functions:

S.N	Function Name & Description
1	NUMTODSINTERVAL(x, interval_unit); Converts the number x to an INTERVAL DAY TO SECOND.
2	NUMTOYMINTERVAL(x, interval_unit); Converts the number x to an INTERVAL YEAR TO MONTH.
3	TO_DSINTERVAL(x); Converts the string x to an INTERVAL DAY TO SECOND.
4	TO_YMINTERVAL(x); Converts the string x to an INTERVAL YEAR TO MONTH.

Ur's
Amar Alam

Order Hold Information Query

SELECT
      ooh.order_number "Order Number",
       ooh.ordered_date "Ordered Date",
       ooh.flow_status_code "Order Status",
       ohd.name "Hold Name",
       ohs.released_flag "Hold Release Flag",
       ohr.release_reason_code "Hold Release Reason",
       ohr.creation_date "Hold Release Date",
       ooh.payment_type_code "Payment Type Code"
FROM apps.oe_order_headers_all ooh,
     apps.oe_order_holds_all hld,
     apps.oe_hold_sources_all ohs,
     apps.oe_hold_definitions ohd,
     apps.oe_hold_releases ohr
WHERE ooh.ordered_date BETWEEN :P_DATE_FROM AND :P_DATE_TO
      AND ooh.order_category_code = 'ORDER'
      AND ooh.header_id=hld.header_id(+)
      AND hld.hold_source_id=ohs.hold_source_id(+)
      AND ohs.hold_id=ohd.hold_id(+)
      AND ohs.hold_release_id=ohr.hold_release_id(+)
ORDER BY ooh.order_number

Ur's
Amar Alam

How to use MO_GLOBAL.INIT

Purpose of mo_global.init :-

It will check if new Multi Org Security Profile is set, to decide if new Security Profile method will be used.

If the new MO security profile is set, then mo_global.init inserts one record, for each Organization in Org Hierarchy, in table mo_glob_org_access_tmp

When & from where is mo_global.init called ?

This package procedure will be called as soon as you login or as soon as you switch responsibility. Just like

FND_GLOBAL.INITIALIZE is called. It is safe to assume that Oracle will invoke MO_GLOBAL.INIT after FND_GLOBAL.INITIALIZE


From SQL*Plus, you can simulate login to a specific responsibility by calling
a. Call FND_GLOBAL.INITIALIZEThis will set your responsibility id, user_id etc
b. call MO_GLOBAL.INITThis will read the MO profile option values for your responsibility/user, and will initialize the Multi Org Access.



Ur's
Amar Alam (Nani)

Caused by: java.io.UTFDataFormatException: Invalid UTF8 encoding in Oracle Reports

Usually we are facing the issue, while generating XML Tags, the issue is Caused by: java.io.UTFDataFormatException: Invalid UTF8 encoding.  This is Caused because of the version of Oracle Reports or Language we need to set in NLS_LANG.

For a Time Being we can go for a below step, we need to change the XML Prolog Value in Oracle Reports Builder.

 XML Prolog Value: xml version="1.0" encoding="iso-8859-1"

This will help in the short term but in my experience it will come back and bite you. you might get lucky but remember what you have done if you hit another problem with your XML in the future.

You are now treating a single report - Oracle support can help you address all of them at once :).

Thanks
Amar Alam.

Few Examples on Dynamic SQL

------------------------------------------------------------------
DECLARE
v_table_name varchar2(100) := 'test_dept';
v_stmt varchar2(1000);
BEGIN
v_stmt := 'insert into '||v_table_name||' values(50,''HR'')';
execute immediate v_stmt;
dbms_output.put_line(v_stmt);
END;

-------------------------------------------------------------------
DECLARE
type sob_record is record
(
name gl_sets_of_books.NAME%type,
currency_code gl_sets_of_books.CURRENCY_CODE%type,
set_of_books gl_sets_of_books.SET_OF_BOOKS_ID%type
);
v_stmt varchar2(1000);
curr sys_refcursor;
row_rec sob_record;
BEGIN
v_stmt := 'select name,currency_code,set_of_books_id from gl_sets_of_books where set_of_books_id=:input';
open curr for v_stmt using 2023;
loop
fetch curr into row_rec;
exit when curr%notfound;
dbms_output.put_line(row_rec.currency_code || '    '||row_rec.name);
end loop;
END;


-------------------------------------------------------------------------------

DECLARE
type status_record is record
(
cid xx_account_status_swap.customer_number%type,
acc_status xx_account_status_swap.new_account_status%type
);
type status_table is table of status_record;

v_stmt varchar2(1000);
curr sys_refcursor;
status_coll status_table;
BEGIN
v_stmt := 'select customer_number,new_account_status from xx_account_status_swap where success_status=:input';

open curr for v_stmt using 'P';
fetch curr bulk collect into status_coll;

for i in 1..status_coll.count loop
dbms_output.put_line(status_coll(i).cid||'   '||status_coll(i).acc_status);
end loop;

dbms_output.put_line('----------------------------------------------');
dbms_output.put_line(status_coll.count);
dbms_output.put_line('----------------------------------------------');
END;

--------------------------------------------------------------------------------


Ur's
AmarAlam

Oracle Applications Idle Session Timeout

What is Session Idle time?

If Oracle Apps client is idle for some time (Eg. Application user goes for a coffee break) session during that time is called as Idle Session & because of security reason, performance issues and to free up system resource Oracle Applications terminates client session (both forms & self service) after idle time value is reached to the one mentioned in configuration file.

To enter into application, profile option "ICX Session Timeout" is used.

ICX Session Time out mentioned in profile option ICX: Session Timeout is in minutes, so ICX session timeout=30

From where ICX: Session Timeout & session.timeout get values?

Autoconfig determines value for profile option "ICX: Session Timeout" and "session.timeout" from entry in context file ($APPL_TOP/admin/SID_hostname.xml) with parameter s_sesstimeout where value mentioned is in milliseconds so profile option ICX: Session Timeout value should be s_sesstimeout/ (1000 * 60) which means here its 10 Minutes. This value is also set in zone.properties in $IAS_ORACLE_HOME/Apache/Jserv where number mentioned is in milli second i.e. 600000 (equal to 10 Minutes) session.timeout = 600000.

Eg.  Session Timeout is in minutes, so ICX session timeout=480 is 8Hrs. 

Ur's
Amar Alam