2 Replies Latest reply on Dec 12, 2012 4:05 AM by Hussein Sawwan-Oracle

    EBS R12.1.3 & dbtimezone related questions

    user593399
      Hi,

      a few questions about EBS R12.1.3 & dbtimezone database setting.

      Background:
      We have EBS R12.1.3 installed, mostly with default settings.

      Workflow status diagram was not working. We implemented the solution described in:
      http://appsdbastuff.blogspot.fi/2010/05/unable-to-view-status-diagram-id.html

      SQL> select dbtimezone from dual;

      DBTIME
      ------
      -07:00

      For Server Timezone profile option, we did not choose Phoenix, but the other one (Mountain time) with the same value (-07:00). After that we bounced the whole application tier. After that status diagram started to work.

      Questions:

      1. Why is dbtimezone set to -07:00? Is it EBS R12 default value? We are in +02:00 timezone, and systimestamp shows the correct value:

      SQL> select systimestamp from dual;

      SYSTIMESTAMP
      ---------------------------------------------------------------------------
      05-DEC-12 11.21.00.641697 AM +02:00

      2. Should it be changed to the correct value +02:00? Can this be done safely? Is it done simply with database update, or does it require something else as well? Does the change affect EBS functionalities?

      3. We have noticed inconsistent timestamps e.g. in the following:
      - wf_bpel_qtab table
      - EBS GUI: OAM -> Patch Wizard -> Recommended Patches Results -> Completion Date

      Are these issues related to dbtimezone, or are they separate issues?

      Thanks for your answers in advance.

      BR,
      TH
        • 1. Re: EBS R12.1.3 & dbtimezone related questions
          Srini Chavali-Oracle
          user593399 wrote:
          Hi,

          a few questions about EBS R12.1.3 & dbtimezone database setting.

          Background:
          We have EBS R12.1.3 installed, mostly with default settings.

          Workflow status diagram was not working. We implemented the solution described in:
          http://appsdbastuff.blogspot.fi/2010/05/unable-to-view-status-diagram-id.html

          SQL> select dbtimezone from dual;

          DBTIME
          ------
          -07:00

          For Server Timezone profile option, we did not choose Phoenix, but the other one (Mountain time) with the same value (-07:00). After that we bounced the whole application tier. After that status diagram started to work.

          Questions:

          1. Why is dbtimezone set to -07:00? Is it EBS R12 default value? We are in +02:00 timezone, and systimestamp shows the correct value:

          SQL> select systimestamp from dual;

          SYSTIMESTAMP
          ---------------------------------------------------------------------------
          05-DEC-12 11.21.00.641697 AM +02:00

          2. Should it be changed to the correct value +02:00? Can this be done safely? Is it done simply with database update, or does it require something else as well? Does the change affect EBS functionalities?
          The DBTIMEZONE value is set at the time of creation of the database

          http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions048.htm#SQLRF00630

          Since EBS databases are seeded as part of the install media, this is a fixed value - my EBS databases also show "-07:00". Oracle does not recommend changing this value as there are ramifications for data stored with timestamps/timezones in the database.

          http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_1004.htm#i2197908
          3. We have noticed inconsistent timestamps e.g. in the following:
          - wf_bpel_qtab table
          - EBS GUI: OAM -> Patch Wizard -> Recommended Patches Results -> Completion Date

          Are these issues related to dbtimezone, or are they separate issues?
          Unlikely these are related to DBTIMEZONE setting. What are the settings of these profile options at the various levels ?

          Server Timezone
          Enable Timezone Conversions
          Client Timezone

          User-Preferred Time Zone Support in Oracle E-Business Suite Release 12 [ID 402650.1]
          Thanks for your answers in advance.

          BR,
          TH
          HTH
          Srini
          • 2. Re: EBS R12.1.3 & dbtimezone related questions
            Hussein Sawwan-Oracle
            1. Why is dbtimezone set to -07:00? Is it EBS R12 default value? We are in +02:00 timezone, and systimestamp shows the correct value:

            SQL> select systimestamp from dual;

            SYSTIMESTAMP
            ---------------------------------------------------------------------------
            05-DEC-12 11.21.00.641697 AM +02:00
            Please see (Timestamps & time zones - Frequently Asked Questions [ID 340512.1]).
            2. Should it be changed to the correct value +02:00? Can this be done safely? Is it done simply with database update, or does it require something else as well? Does the change affect EBS functionalities?
            Please see the same docs referenced above -- 6)Why is the database time zone the value it is, and how can I change it?
            3. We have noticed inconsistent timestamps e.g. in the following:
            - wf_bpel_qtab table
            - EBS GUI: OAM -> Patch Wizard -> Recommended Patches Results -> Completion Date

            Are these issues related to dbtimezone, or are they separate issues?
            Those are related to the application tier time zone settings. Please see (How To Change Time Zone Of An E-Business Instance [ID 1320286.1]) for this question and the previous one.

            Thanks,
            Hussein