3 Replies Latest reply on Oct 7, 2014 1:55 AM by FPonte

    ODI User Functions - Please Help !!

    user10719205

      Hi All,

       

      I am using ODI 11g (Latest version).

       

      I have the following tables in screen shot below:

      ODI Source tables.jpg

       

      I have the following code for DATE_VAL_EXP_RETIRE (target table), As you can see from the code below I am checking/using 3 different fields in MPP_PARTICIPANT_INFO  and one filed from MPP_PLAN_INFO. The fields are highlighted in purple above. I would like to apply this code on the source instead of staging, however ODI stops me from doing that. In addition, if i continue with Staging and execute my interface, I get error: "FISCAL_QRTR_END_F": invalid identifier. (Also screen shot below). Can you please help ?? The goal here is to apply the SQL code below to DATE_VAL_EXP_RETIRE field in target using FISCAL_QRTR_END_F function on Source, not staging. Thanks in advance.

       

      NVL((CASE WHEN
        (CASE WHEN LEAST (
        NVL(MPP_PARTICIPANT_INFO.EARLIEST_DATE_FOR_PAY,TO_DATE('01-DEC-2099','DD-MON-YYYY')),
        NVL(MPP_PARTICIPANT_INFO.SPOUSE_EARLIEST_START_DATE,TO_DATE('01-DEC-2099','DD-MON-YYYY')),
        NVL(MPP_PARTICIPANT_INFO.EARLIEST_ERD, TO_DATE('01-DEC-2099','DD-MON-YYYY'))) < TO_DATE('01-JAN-1996','DD-MON-YYYY')
        THEN SYSDATE ELSE LEAST (
        NVL(MPP_PARTICIPANT_INFO.EARLIEST_DATE_FOR_PAY,TO_DATE('01-DEC-2099','DD-MON-YYYY')),
        NVL(MPP_PARTICIPANT_INFO.SPOUSE_EARLIEST_START_DATE,TO_DATE('01-DEC-2099','DD-MON-YYYY')),
        NVL(MPP_PARTICIPANT_INFO.EARLIEST_ERD, TO_DATE('01-DEC-2099','DD-MON-YYYY'))) END) = TO_DATE('01-DEC-2099','DD-MON-YYYY') THEN MPP_PLAN_INFO.DEEMED_DISTRIB_DATE
        ELSE (CASE WHEN LEAST (
        NVL(MPP_PARTICIPANT_INFO.EARLIEST_DATE_FOR_PAY,TO_DATE('01-DEC-2099','DD-MON-YYYY')),
        NVL(MPP_PARTICIPANT_INFO.SPOUSE_EARLIEST_START_DATE,TO_DATE('01-DEC-2099','DD-MON-YYYY')),
        NVL(MPP_PARTICIPANT_INFO.EARLIEST_ERD, TO_DATE('01-DEC-2099','DD-MON-YYYY'))) < TO_DATE('01-JAN-1996','DD-MON-YYYY')
        THEN Fiscal_QRTR_End_F (SYSDATE) ELSE LEAST (
        NVL(MPP_PARTICIPANT_INFO.EARLIEST_DATE_FOR_PAY,TO_DATE('01-DEC-2099','DD-MON-YYYY')),
        NVL(MPP_PARTICIPANT_INFO.SPOUSE_EARLIEST_START_DATE, TO_DATE('01-DEC-2099','DD-MON-YYYY')),
        NVL(MPP_PARTICIPANT_INFO.EARLIEST_ERD,TO_DATE('01-DEC-2099','DD-MON-YYYY'))) END)
      END),   Fiscal_QRTR_End_F (SYSDATE))
      

       

      Here is my Fiscal_QRTR_End_F function:

       

      fiscal_function.jpg

       

      ODI Error:

      ODI-1228: Task CUSTOMER_VAL_INFO_2 (Integration) fails on the target ORACLE connection IPVIPVF3.
      Caused By: java.sql.SQLSyntaxErrorException: ORA-00904: "FISCAL_QRTR_END_F": invalid identifier
      
      
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:462)
        at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:405)
        at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:931)
        at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:481)
        at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:205)