Finding a Time Admin Instance Number

SQL to find the Time Admin instance number for a particular run.

-- -------------------------------------------------------
-- Get the instance number for the Time Admin temp tables
-- prompting for the process instance number.
-- -------------------------------------------------------  
  select '1' as INSTANCE
    from PS_TL_IPT11
   where PROCESS_INSTANCE = :PROCESS_INSTANCE
union
  select '2' as INSTANCE
    from PS_TL_IPT12
   where PROCESS_INSTANCE = :PROCESS_INSTANCE
union
  select '3' as INSTANCE
    from PS_TL_IPT13
   where PROCESS_INSTANCE = :PROCESS_INSTANCE
union
  select '4' as INSTANCE
    from PS_TL_IPT14
   where PROCESS_INSTANCE = :PROCESS_INSTANCE
union
  select '5' as INSTANCE
    from PS_TL_IPT15
   where PROCESS_INSTANCE = :PROCESS_INSTANCE
union
  select '6' as INSTANCE
    from PS_TL_IPT16
   where PROCESS_INSTANCE = :PROCESS_INSTANCE
union
  select '7' as INSTANCE
    from PS_TL_IPT17
   where PROCESS_INSTANCE = :PROCESS_INSTANCE
union
  select '8' as INSTANCE
    from PS_TL_IPT18
   where PROCESS_INSTANCE = :PROCESS_INSTANCE
union
  select '9' as INSTANCE
    from PS_TL_IPT19
   where PROCESS_INSTANCE = :PROCESS_INSTANCE
union
  select '10' as INSTANCE
    from PS_TL_IPT110
   where PROCESS_INSTANCE = :PROCESS_INSTANCE
union
  select '11' as INSTANCE
    from PS_TL_IPT111
   where PROCESS_INSTANCE = :PROCESS_INSTANCE
union
  select '12'
    from PS_TL_IPT112
   where PROCESS_INSTANCE = :PROCESS_INSTANCE
 union
  select '13' as INSTANCE
    from PS_TL_IPT113
   where PROCESS_INSTANCE = :PROCESS_INSTANCE

Batchman

SQL to determine how many members are in each of the Batches for a Time Admin run. The example shown below is for instance 7. Change this number to be the instance of the run you are interested in.

select batch_num,
           count(*)
   from PS_TL_TA_BATCH7
 group by BATCH_NUM
 order by BATCH_NUM Asc

Note that you can limit the maximum size of a batch from: -

Home > Setup HRMS > Install > Product and Country Specific > Time and Labor Installation

To see which rules are being processed for a particular batch you can use the following SQL: -

select BATCH_NUM,
       RULE_PGM_ID,
       PRIORITY,
       TL_RULE_ID
  from PS_TL_RULE_MAP7
 where BATCH_NUM = :BATCH_NUM
 order by BATCH_NUM,
          PRIORITY

You can use the following SQL to work out the approximate percentage complete of a Time Admin run.

select ROUND( ( ( ( select SUM(BA.END_DT - BA.START_DT)
                      from PS_TL_TA_BATCH7 BA,
                           PS_TL_RULE_MAP7 RM
                    where BA.BATCH_NUM <= :COMPLETED_BATCHES
                      and BA.BATCH_NUM = RM.BATCH_NUM
                  ) /
                  ( select SUM(BA.END_DT - BA.START_DT)
                      from PS_TL_TA_BATCH7 BA,
                           PS_TL_RULE_MAP7 RM
                     where BA.BATCH_NUM = RM.BATCH_NUM
                  ) 
                ) * 100
              ), 2
            ) AS PERCENT_COMPLETE
  from DUAL

What it does is mutiplies the number of employees in the completed batches by the number of rules for each, does the same for the total, divides one by the other, multiplies the result by a 100 and bingo! Not perfect but better than a poke in the eye with a stick to help you work out how much time you need to wait.