3 Replies Latest reply: Jun 25, 2013 9:51 AM by 1000614 RSS

    BG Absence Duration in SSHR

    931002
      I am having problems with BG absence duration formula in SSHR. It is working fine in forms and not working in SSHR. It is behaving weird in SSHR with some part of it working and some part not working and I am wondering if it is because of the functions or database items that I have used in the formula. I have recompiled all the functions and made sure there are no invalid functions. We have also restarted the database and ran compile formula program for BG absence duration but that didn't help. We are on 12.0.6 and I am wondering if we are allowed or not to use ABS_INFORMATION_CATEGORY and ABS_INFORMATION fields as database items. Can someone help me understand the problem? Thanks in advance


      /* Main Body of Formula */
      INPUTS ARE days_or_hours(text),
      date_start (date),
      date_end (date),
      time_start (text),
      time_end (text),
      ABS_INFORMATION_CATEGORY (text),
      ABS_INFORMATION1 (text),
      ABS_INFORMATION2 (text),
      ABS_INFORMATION3 (text),
      ABS_INFORMATION4 (text),
      ABS_INFORMATION5 (text),
      ABS_INFORMATION6 (text),
           ABSENCE_ATTENDANCE_TYPE_ID

      /* default values */
      DEFAULT FOR days_or_hours IS 'D'
      DEFAULT FOR time_start IS '08:00'
      DEFAULT FOR time_end IS '16:30'
      DEFAULT FOR date_start IS '0001/01/01 08:00:00' (DATE)
      DEFAULT FOR date_end IS '4712/12/31 16:30:00' (DATE)
      DEFAULT FOR ABS_INFORMATION_CATEGORY IS ' '
      DEFAULT FOR ABS_INFORMATION1 IS ' '
      DEFAULT FOR ABS_INFORMATION2 IS ' '
      DEFAULT FOR ABS_INFORMATION3 IS ' '
      DEFAULT FOR ABS_INFORMATION4 IS ' '
      DEFAULT FOR ABS_INFORMATION5 IS ' '
      DEFAULT FOR ABS_INFORMATION6 IS ' '


      /* database items */
      DEFAULT FOR asg_start_time IS '08:00'
      DEFAULT FOR asg_end_time IS '16:30'
      DEFAULT FOR asg_pos_start_time IS '08:00'
      DEFAULT FOR asg_pos_end_time IS '16:30'
      /* local variables */
      error_or_warning = ' '
      invalid_msg = ' '
      duration = '0'
      number_of_days = 0
      first_day_hours = 0
      last_day_hours = 0
      /* Defaults Section */
      /* default values for working day, these are only used if no
      working conditions can be found */
      begin_day = '08:00'
      end_day = '16:30'

      IF date_end < date_start
      THEN
      (
      duration = 'FAILED'
      invalid_msg = 'Invalid Entry - The End Date cannot be before Start Date.'
      return duration, invalid_msg
      )


      VALID_YEAR = XXTQ_CA_GET_VALID_DAYS(date_start,date_end)
      IF VALID_YEAR = 'N'
      THEN
      (
      duration = 'FAILED'
      invalid_msg = 'Invalid Entry - The end date cannot be in a different year. Please enter separate requests for each year.'
      return duration, invalid_msg
      )


      HOLIDAY_DAYS = XXTQ_CA_TNO_fnc_hday_dates (date_start,date_end)
      HOLIDAY_HALF_DAYS = XXTQ_CA_TNO_FNC_HALFDAY_DATES(date_start,date_end)

      /*
      IF ABS_INFORMATION_CATEGORY = 'CA'
      then ( If(ABS_INFORMATION3 = 'Half Day')
      then DEDUCT_DAYS = 0.5
      Else deduct_days = 0)
      Else DEDUCT_DAYS = 0*/

      REDUCE_DAYS=HOLIDAY_DAYS+(HOLIDAY_HALF_DAYS*0.5)

      Days_Between = GET_WORKING_DAYS(date_start,date_end)-REDUCE_DAYS


      IF Days_Between <0 THEN
      (
      duration = 'FAILED'
      invalid_msg = 'Please enter valid dates.'
      )

      IF ABS_INFORMATION_CATEGORY = 'CA'
      THEN
      (If ABS_INFORMATION3 = 'Half Day'
      THEN
      ( IF date_start = date_end THEN
      (deduct_days = 0.5
      days_between = days_between - deduct_days
      duration = to_char(days_between) return duration, invalid_msg
      )
      ELSE
      ( duration = 'FAILED'
      invalid_msg = 'Half day leave can be applied only for a day'
      return duration, invalid_msg
      )
      )
      ELSE
      (
      duration = to_char(days_between)
      return duration, invalid_msg
      )
      )

      ABSENCE_NAME=XXHR_TQ_ABSENCE_NAME(ABSENCE_ATTENDANCE_TYPE_ID)
      ANNUAL_BALANCE= XXHR_TQ_GET_LEAVE_BALANCE(absence_name,date_start)
      LEAVE_USED = XXTQ_CA_GET_LEAVE_AVAILED(ABSENCE_ATTENDANCE_TYPE_ID,date_start)

      IF ABSENCE_NAME LIKE 'Flex Day'
      THEN
      (If days_between > ANNUAL_BALANCE
      THEN
      ( IF Annual_Balance =0 THEN
      (duration = 'FAILED'
      invalid_msg = 'Your flex day balance is 0 so you cannot apply for any more flex days. Please contact HR if you have a question about this.'
      return duration, invalid_msg
      )
      ELSE
      ( duration = 'FAILED'
      invalid_msg = 'Invalid Claim - Your flex day balance is '||to_text(ANNUAL_BALANCE)||' days. Please adjust the dates and
      submit a request within this limit.'
      return duration, invalid_msg
      )
      )
      ELSE
      (
      duration = to_char(days_between)
      return duration, invalid_msg
      )
      )

      IF ABSENCE_NAME LIKE 'Vacation Day'
      THEN
      (If days_between >ANNUAL_BALANCE
      THEN
      ( IF Annual_Balance =0 THEN
      (duration = 'FAILED'
      invalid_msg = 'Your vacation day balance is 0 so you cannot apply for any more vacation days. Please contact HR
      if you have a question about this.'
      return duration, invalid_msg
      )
      ELSE
      ( duration = 'FAILED'
      invalid_msg = 'Invalid Claim - Your vacation day balance is '||to_text(ANNUAL_BALANCE)||' days. Please adjust the dates
      and submit a request within this limit.'
      return duration, invalid_msg
      )
      )
      ELSE
      (
      duration = to_char(days_between)
      return duration, invalid_msg
      )
      )

      IF ABSENCE_NAME LIKE 'Sick Day'
      then duration = to_char(days_between)

      IF ABSENCE_NAME LIKE 'Training Day'
      then duration = to_char(days_between)

      IF ABSENCE_NAME LIKE 'Volunteering Day'
      THEN
      (
      VD_MAX_HOURS = XX_TNO_VOLUNTEERING_DAY_MAX - leave_used

      IF days_between > VD_MAX_HOURS
      THEN
      (
      duration = 'FAILED'
      invalid_msg = 'You can only claim '||to_text(VD_MAX_HOURS)||' volunteering days.'
      return duration, invalid_msg
      )
      ELSE
      (
      duration = to_char(days_between)
      return duration, invalid_msg
      ))
      return duration, invalid_msg




      Jay
        • 1. Re: BG Absence Duration in SSHR
          igwe
          strange. first make sure you set the profile options below to the values specified.

          HR: Absence Duration Auto Overwrite - Yes (for automatic absence duration calculation)
          HR: Schedule Based Absence Calculation - No

          at responsibility level
          • 2. Re: BG Absence Duration in SSHR
            931002
            For some reason, the absence category dff is giving problem. The formula is getting executed until that part and skipping the remaining. I have updated it to remove the else condition and now it is working fine.

            Thanks
            Jay
            • 3. Re: BG Absence Duration in SSHR
              1000614

              Hi Jay,

               

              were you finally able to access DFF in BG_ABSENCE_DURATION , did the following piece of code worked ?

               

              **************************************************************************************************

              IF ABS_INFORMATION_CATEGORY = 'CA'

              THEN

              (If ABS_INFORMATION3 = 'Half Day'

              THEN

              ( IF date_start = date_end THEN

              (deduct_days = 0.5

              days_between = days_between - deduct_days

              duration = to_char(days_between) return duration, invalid_msg

              )

              ELSE

              ( duration = 'FAILED'

              invalid_msg = 'Half day leave can be applied only for a day'

              return duration, invalid_msg

              )

              **************************************************************************************************