4 Replies Latest reply on May 1, 2008 3:35 AM by 557833

    OTL Self Service Error Message

    gizzmogeek
      We use OTL Timekeeper functionality for entering time, but want to switch to some Self Serve OTL time entry also. The error messages we are currently using work fine in OTL. The overtime rule goes out, validates the time entered, and returns an error apparopriately. When I try this in Self Service, I get the following errors:

      The Oracle error is: ORA-20000: ORA-20001:ORA-20001: Formula KCI_JEAN_TEST_TOKEN: ORA-01403: no data found ORA-01403: no data found Cause: During the PL/SQL execution part, an Oracle error was detected. Action: Please refer to your system administrator..

      I am unsure why. If I enter time in Timekeeper, it tells me I don't have enough regular time to charge overtime. However in self serve, I just get this weird message.

      Ideas? I can post my entire fast formula (it calls a pl/sql function to do the actual verification that return any errors found to the fast formula) if you are interested.

      Jean Farmer
      KCI Technologies
        • 1. Re: OTL Self Service Error Message
          557833
          Your formula code will help in finding what is going wrong. between check where exactly the exception is getting raised in your code.

          --Shiv                                                                                                                                                                                                                                                                                               
          • 2. Re: OTL Self Service Error Message
            92207
            I wrote this code to test the passing of Tokens and it works perfectly. The messages come out condensed into two messages with tokens.
            /********************SAMPLE1*************************************/
            Default for MESG1 is ' '
            Default for MESG2 is ' '
            Default for MESG3 IS ' '
            Default for MESG4 IS ' '

            INPUTS ARE Resource_id,
            submission_date(TEXT),
            MESG1(TEXT),
            MESG2(TEXT),
            MESG3(TEXT),
            MESG4(TEXT)

            JEAN1 = 'JEAN LINE 1'
            JEAN2 = ' '
            JEAN3 = ' '
            JEAN4 = ' '
            JEAN5 = 'JEAN LINE 5'
            JEAN6 = ' '

            /*Squish the messages into just two lines, jean1,jean2*/
            JEAN6=KCI_OTL_MESSAGE_ORDER(JEAN1,JEAN2,JEAN3,JEAN4,JEAN5)

            IF LENGTH(JEAN1) > 3 THEN
            (
            RULE_STATUS = 'E'
            MESSAGE1='HXC_XXKCI_OTL_ERR3'
            TOKEN_NAME1='KCI_ERR3'
            TOKEN_VALUE1=JEAN1
            )

            IF LENGTH(JEAN2) > 3 THEN
            (
            MESSAGE2='HXC_XXKCI_OTL_ERR4'
            TOKEN_NAME2='KCI_ERR4'
            TOKEN_VALUE2=JEAN2
            )

            RETURN RULE_STATUS, MESSAGE1, TOKEN_NAME1, TOKEN_VALUE1, MESSAGE2, TOKEN_NAME2, TOKEN_VALUE2
            /*******************END SAMPLE 1*******************************/

            This one keeps returning the no data found error I listed up top whenever it runs. I'm not really understanding what the issue is though... obviously something with the KCI_OVERTIME_VALIDATION. However the KCI_OVERTIME_VALIDATION runs currently in the current fastformula and it works great without tokens. It returns message1..5 without an error and reads correctly. However as soon as I try and put it in here with tokens, it starts giving me the no data found errors.

            Please note - this works great with Timekeeper, but in Self Service OTL it returns the error.

            /**************** SAMPLE 2 - WHICH RESULTS IN ERRORS ***************/
            Default for MESG1 is ' '
            Default for MESG2 is ' '
            Default for MESG3 IS ' '
            Default for MESG4 IS ' '

            INPUTS ARE Resource_id,
            submission_date(TEXT),
            MESG1(TEXT),
            MESG2(TEXT),
            MESG3(TEXT),
            MESG4(TEXT)

            JEAN1 = ' '
            JEAN2 = ' '
            JEAN3 = ' '
            JEAN4 = ' '
            JEAN5 = ' '
            JEAN6 = ' '

            JEAN5=KCI_OVERTIME_VALIDATION(RESOURCE_ID,submission_date,JEAN1,JEAN2,JEAN3,JEAN4)

            JEAN6=KCI_OTL_MESSAGE_ORDER(JEAN1,JEAN2,JEAN3,JEAN4,JEAN5)

            IF LENGTH(JEAN1) > 3 THEN
            (
            RULE_STATUS = 'E'
            MESSAGE1='HXC_XXKCI_OTL_ERR3'
            TOKEN_NAME1='KCI_ERR3'
            TOKEN_VALUE1=JEAN1
            )

            IF LENGTH(JEAN2) > 3 THEN
            (
            MESSAGE2='HXC_XXKCI_OTL_ERR4'
            TOKEN_NAME2='KCI_ERR4'
            TOKEN_VALUE2=JEAN2
            )

            RETURN RULE_STATUS, MESSAGE1, TOKEN_NAME1, TOKEN_VALUE1, MESSAGE2, TOKEN_NAME2, TOKEN_VALUE2

            /*******************END SAMPLE 2 ********************************/
            • 3. Re: OTL Self Service Error Message
              92207
              I think I figured out the issue.

              The KCI_OVERTIME_VALIDATION function would sometimes return a NULL value. I realized last night that fast formula's don't know how to handle NULL values.

              I changed the function to never return a null and the error went away. I think I am good to go. Thanks!
              • 4. Re: OTL Self Service Error Message
                557833
                Good to see its resolved.

                --Shiv