Covert to Operating Unit Timezone

Below is the function to get current operating unit Timezone.

FUNCTION convert_to_ou_tz(p_date DATE, p_org_id NUMBER DEFAULT fnd_global.org_id)
RETURN DATE IS
l_timezone VARCHAR2(50);
l_new_date DATE;
BEGIN
SELECT attribute8
INTO l_timezone
FROM hr_all_organization_units
WHERE organization_id = p_org_id;

–write_log(‘l_timezone=’|| l_timezone);

SELECT CAST(from_tz(CAST(p_date AS TIMESTAMP), ‘US/Central’) at TIME ZONE (l_timezone) AS DATE)
INTO l_new_date
FROM dual;

RETURN l_new_date;

EXCEPTION
WHEN OTHERS THEN
RETURN p_date;
END convert_to_ou_tz;