How to show Report Date in Local Timezone instead of Server Timezone in Oracle Apps

How to show Report Date in Local Timezone instead of Server Timezone in Oracle Apps

How to populate Report Date in Local Timezone instead of Server Timezone in Oracle EBS.


You can use below sql/plsql to derive that thing.

Below are the steps needs to be done to display local date-

From User’s side:  First user has to select ‘Timezone’ in Preferences and save ,refer below screenshot-

If user is not setting any time zone report will pick server time to display on report.

From Technical side: Define Formula column/sql/pl sql on report/package with character type(size u can keep 500) and add below code there-


-- Dervie user id of user logged in , here it is 754819

declare
p_timeid number;
p_timezone varchar2(500);
p_final varchar2(500);
begin
  SELECT FND_PROFILE.VALUE_SPECIFIC('CLIENT_TIMEZONE_ID',754819) into p_timeid  FROM DUAL;
--  srw.message (111, 'p_timeid:'||p_timeid );
  If p_timeid is not null then
  begin
  select timezone_code into p_timezone from FND_TIMEZONES_VL where upgrade_tz_id=p_timeid;
  -- srw.message (111, 'p_timezone:'||p_timezone );
  exception
  when others then
                p_timezone:=null;
      --          srw.message (111, 'p_timezone:'||sqlerrm );
  end;
  end if;
  if p_timezone is not null then
  select to_char(LOCALTIMESTAMP at time zone  TZ_OFFSET(p_timezone),'dd-mm-yyyy hh24:mi:ss') into p_final from dual;
  --srw.message (111, 'p_timezone:'||p_final );
  return(p_final);
  else
  return(to_char(sysdate,'dd-mm-yyyy hh24:mi:ss'));
  end if;
end;