Loading

Monday, May 3, 2010

How to get timezone based on ZIP code? (HZ_TIMEZONE_PUB.GET_TIMEZONE_ID)

Below script is tested in R12.1.1 instance.

SET serveroutput ON;

DECLARE
  P_API_VERSION    NUMBER;
  P_INIT_MSG_LIST  VARCHAR2(200);
  P_POSTAL_CODE    VARCHAR2(200);
  P_CITY           VARCHAR2(200);
  P_STATE          VARCHAR2(200);
  P_COUNTRY        VARCHAR2(200);
  X_TIMEZONE_ID    NUMBER;
  X_RETURN_STATUS  VARCHAR2(200);
  X_MSG_COUNT      NUMBER;
  X_MSG_DATA       VARCHAR2(200);
  P_COUNT          NUMBER;
BEGIN
  P_API_VERSION    := 1.0;
  P_INIT_MSG_LIST  := FND_API.G_FALSE;
  P_POSTAL_CODE    := NULL;
  P_CITY           := NULL;
  P_STATE          := NULL;
  P_COUNTRY        := 'CI';

  HZ_TIMEZONE_PUB.GET_TIMEZONE_ID(
    P_API_VERSION   => P_API_VERSION,
    P_INIT_MSG_LIST => P_INIT_MSG_LIST,
    P_POSTAL_CODE   => P_POSTAL_CODE,
    P_CITY          => P_CITY,
    P_STATE         => P_STATE,
    P_COUNTRY       => P_COUNTRY,
    X_TIMEZONE_ID   => X_TIMEZONE_ID,
    X_RETURN_STATUS => X_RETURN_STATUS,
    X_MSG_COUNT     => X_MSG_COUNT,
    X_MSG_DATA      => X_MSG_DATA
  );
 
  DBMS_OUTPUT.PUT_LINE('X_RETURN_STATUS = ' || X_RETURN_STATUS);
 
 
  IF X_return_status = 'S' THEN
   DBMS_OUTPUT.PUT_LINE('TIMEZONE_ID = ' || X_TIMEZONE_ID);
  ELSE
   DBMS_OUTPUT.put_line('Message count ' || x_msg_count);
   IF x_msg_count = 1 THEN
      DBMS_OUTPUT.put_line('x_msg_data '||x_msg_data);
   ELSIF x_msg_count > 1 THEN
   LOOP
      p_count := p_count+1;
      x_msg_data := FND_MSG_PUB.Get(FND_MSG_PUB.G_NEXT,FND_API.G_FALSE);
      IF x_msg_data IS NULL THEN
      EXIT;
      END IF;
      DBMS_OUTPUT.put_line('Message' || p_count ||' ---'||x_msg_data);
      END LOOP;
   END IF;
  END IF;
END;


“Quote for the Post”
"NEVER WORRY ABOUT NUMBERS. 
  HELP ONE PERSON AT A TIME,
  AND ALWAYS START WITH 
  THE PERSON NEAREST YOU." 
- MOTHER TERESA

0 Responses to “How to get timezone based on ZIP code? (HZ_TIMEZONE_PUB.GET_TIMEZONE_ID)”

Post a Comment

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.