3 Replies Latest reply: Apr 15, 2013 6:02 AM by Veeresh Rayan RSS

    ORA: 01843 Not a valid month while run SDE_ORA_GLBalanceFact_Full

    Yu Yang
      While informatica run the select sql of this task , there is a exception said the month is valid ,in the where condition the sql is :
      BAL.LAST_UPDATE_DATE >=
      TO_DATE('2013-04-11 20:45:33', 'MM/DD/YYYY HH24:MI:SS')
      OR PER.LAST_UPDATE_DATE >=
      TO_DATE('2013-04-11 20:45:33', 'MM/DD/YYYY HH24:MI:SS')

      if I change the date for format , then it will ok
      BAL.LAST_UPDATE_DATE >=
      TO_DATE('2013-04-11 20:45:33', 'YYYY/MM/DD HH24:MI:SS')
      OR PER.LAST_UPDATE_DATE >=
      TO_DATE('2013-04-11 20:45:33', 'YYYY/MM/DD HH24:MI:SS')
      I am confused whether the date is wrong here or the date format is wrong , how should I fix this problem?

      the whole sql is :

      SELECT
      BAL.LEDGER_ID,
      BAL.CODE_COMBINATION_ID,
      BAL.CURRENCY_CODE,
      LED.CURRENCY_CODE,
      PER.PERIOD_NAME,
      BAL.ACTUAL_FLAG,
      BAL.TRANSLATED_FLAG,
      BAL.TEMPLATE_ID,
      BAL.PERIOD_NET_DR,
      BAL.PERIOD_NET_CR,
      ( BAL.BEGIN_BALANCE_DR + BAL.PERIOD_NET_DR ),
      ( BAL.BEGIN_BALANCE_CR + BAL.PERIOD_NET_CR) ,
      BAL.PERIOD_NET_DR_BEQ,
      BAL.PERIOD_NET_CR_BEQ,
      ( BAL.BEGIN_BALANCE_DR_BEQ + BAL.PERIOD_NET_DR_BEQ ) PERIOD_END_BALANCE_DR_BEQ,
      ( BAL.BEGIN_BALANCE_CR_BEQ + BAL.PERIOD_NET_CR_BEQ ) PERIOD_END_BALANCE_CR_BEQ,
      PER.START_DATE,
      PER.END_DATE,
      BAL.LAST_UPDATE_DATE AS LAST_UPDATE_DATE_BAL,
      BAL.LAST_UPDATED_BY AS LAST_UPDATED_BY_BAL,
      PER.LAST_UPDATE_DATE AS LAST_UPDATE_DATE_PERIODS,
      PER.LAST_UPDATED_BY AS LAST_UPDATED_BY_PERIODS,
      LED.LAST_UPDATE_DATE AS LAST_UPDATE_DATE_SOB,
      LED.LAST_UPDATED_BY AS LAST_UPDATED_BY_SOB,
      BAL.BUDGET_VERSION_ID AS BUDGET_VERSION_ID,
      PER.ADJUSTMENT_PERIOD_FLAG AS ADJUSTMENT_PERIOD_FLAG,
      CASE WHEN
      BAL.TRANSLATED_FLAG = 'Y' THEN 'TRANSLATED'
      WHEN
      BAL.TRANSLATED_FLAG = 'R' THEN 'ENTERED_FOREIGN'
      WHEN
      BAL.CURRENCY_CODE = 'STAT' THEN 'STAT'
      WHEN
      (BAL.PERIOD_NET_DR_BEQ IS NULL) AND
      (BAL.PERIOD_NET_CR_BEQ IS NULL) AND
      (BAL.BEGIN_BALANCE_DR_BEQ IS NULL) AND
      (BAL.BEGIN_BALANCE_CR_BEQ IS NULL)
      THEN 'BASE'
      ELSE 'ENTERED_LEDGER'
      END CURRENCY_BALANCE_TYPE
      FROM
      GL_BALANCES BAL
      , GL_LEDGERS LED
      , GL_PERIODS PER
      WHERE LED.LEDGER_ID = BAL.LEDGER_ID
      AND PER.PERIOD_SET_NAME = LED.PERIOD_SET_NAME
      AND BAL.PERIOD_NAME = PER.PERIOD_NAME
      AND BAL.PERIOD_TYPE = PER.PERIOD_TYPE
      AND NVL(BAL.TRANSLATED_FLAG, 'X') IN ('Y', 'X', 'R')
      AND BAL.ACTUAL_FLAG IN ( 'A','B')
      AND BAL.TEMPLATE_ID IS NULL
      AND
      (
      BAL.LAST_UPDATE_DATE >=
      TO_DATE('2013-04-11 20:45:33', 'YYYY/MM/DD HH24:MI:SS')
      OR PER.LAST_UPDATE_DATE >=
      TO_DATE('2013-04-11 20:45:33', 'YYYY/MM/DD HH24:MI:SS')
      )
      AND DECODE('Y', 'Y', LED.LEDGER_ID, 1) IN (2021,2123)
      AND DECODE('Y', 'Y', LED.LEDGER_CATEGORY_CODE, 'NONE') IN ('PRIMARY','NONE')