Tuesday 10 February 2015

Convert the time zone Based on the Organization

CREATE OR REPLACE FUNCTION APPS.xx_org_tzone_conv (
   p_organization_id   NUMBER,
   pdate               DATE
)
   RETURN DATE
IS
   l_local_time   DATE;
   l_tz_time      VARCHAR2 (50);
   l_local_tz     VARCHAR2 (10);
BEGIN
   IF p_organization_id = 120
   THEN
      BEGIN
         SELECT SUBSTR (TO_CHAR (TZ_OFFSET ('US/Pacific')), 1, 6)
           INTO l_tz_time
           FROM DUAL;
      EXCEPTION
         WHEN OTHERS
         THEN
            l_tz_time := NULL;
      END;

      IF l_tz_time = '-07:00'
      THEN
         l_local_tz := 'PDT';
      ELSIF l_tz_time = '-08:00'
      THEN
         l_local_tz := 'PST';
      END IF;
   ELSIF p_organization_id = 200
   THEN
      BEGIN
         SELECT SUBSTR (TO_CHAR (TZ_OFFSET ('US/Eastern')), 1, 6)
           INTO l_tz_time
           FROM DUAL;
      EXCEPTION
         WHEN OTHERS
         THEN
            l_tz_time := NULL;
      END;

      IF l_tz_time = '-04:00'
      THEN
         l_local_tz := 'EDT';
      ELSIF l_tz_time = '-05:00'
      THEN
         l_local_tz := 'EST';
      END IF;

   END IF;

   dbms_output.put_line (l_tz_time || 'l_tz_time');
   dbms_output.put_line (l_local_tz || 'l_local_tz');

   BEGIN
      SELECT NEW_TIME (pdate, 'GMT', l_local_tz)
        INTO l_local_time
        FROM DUAL;
   EXCEPTION
      WHEN OTHERS
      THEN
         l_local_time := NULL;
   END;

   dbms_output.put_line (l_local_time || 'l_local_time');

   RETURN l_local_time;

EXCEPTION
   WHEN OTHERS
   THEN
      RETURN NULL;
      dbms_output.put_line (SQLCODE || ':' ||SQLERRM);
END xx_org_tzone_conv;
/

1 comment:

  1. Best Casinos Near Harrah's Casino & Spa - Mapyro
    Compare Harrah's Casino & 구미 출장마사지 Spa search by location 계룡 출장샵 and 서산 출장마사지 the area you're visiting. Find 공주 출장안마 the best casinos and other local gems 양주 출장마사지 in MI and beyond.

    ReplyDelete