Oracle EBS - SQL Query to get custom top directory path

select  

variable_name, value

from fnd_env_context

where variable_name like '%CUST%_TOP' 

GROUP BY variable_name, value

order by variable_name;

Oracle - SQL Query to get List of Dates based on Start and End Date

Let's Take Assumptions for the Start Date and End Date.

START DATE: SYSDATE-5

END DATE: SYSDATE

Syntax:

select (SYSDATE-5) + level - 1 dt

from   dual

connect by level <= (

  (SYSDATE) - (SYSDATE-5) + 1

);

Example:

select date'2021-06-08' + level - 1 dt

from   dual

connect by level <= (

  date'2021-06-14' - date'2021-06-08' + 1

);

Oracle Fusion Date Format Conversion based Time Zone

 1) System Preference Defined Timezone based Date

SELECT

(cast(SYSDATE as timestamp) AT TIME ZONE FND_GLOBAL.TIMEZONE) DT

FROM Dual

2) Convert Date From One Timezone to another Timezone

select 

TO_CHAR(FROM_TZ(CAST(SYSDATE AS TIMESTAMP), 'GMT') at time zone 'Asia/Dubai','dd-Mon-yyyy HH24:mm:ss') D2 

from dual