1. Administering Fast Formulas
  2. Workforce Management Functions

Workforce Management Functions

Fast formula methods work only between fast formula calls. Workforce Management passes records from Java programs and these values must be stored during the call, so you must use these workforce management functions to store and retrieve values between calls.

Don't use fast formula working storage area methods, such as WSA_GET and WSA_SET.

Function Description 
SET_WRK_NUM(
P_FFS_ID    , 
P_PARM_NAME , 
P_PARM_SEQ  , 
P_PARM_VALUE )

Set the numeric value for the item called P_PARM_NAME. Any existing item with the same name is overwritten.

Parameters:

  • P_FFS_ID: Use the HWM_FFS_ID context (see Appendix 2: Contexts).
  • P_PARM_NAME: Any alphanumeric name.
  • P_PARM_SEQ: Set to 0 unless storing multiple values with the same PARM_NAME.
  • P_PARM_VALUE: Numeric value.
 
SET_WRK_DATE(
P_FFS_ID    , 
P_PARM_NAME , 
P_PARM_SEQ  , 
P_PARM_VALUE )

Same as SET_WRK_NUM, except it's used to store date values.

Parameters:

  • P_FFS_ID: Use the HWM_FFS_ID context (see Appendix 2: Contexts).
  • P_PARM_NAME: Any alphanumeric name.
  • P_PARM_SEQ: Set to 0 unless storing multiple values with the same PARM_NAME.
  • P_PARM_VALUE: Date value.
 
SET_WRK_TEXT(
P_FFS_ID    , 
P_PARM_NAME , 
P_PARM_SEQ  , 
P_PARM_VALUE )

Same as SET_WRK_NUM, except it's used to store string values.

Parameters:

  • P_FFS_ID: Use the HWM_FFS_ID context (see Appendix 2: Contexts).
  • P_PARM_NAME: Any alphanumeric name.
  • P_PARM_SEQ: Set to 0 unless storing multiple values with the same PARM_NAME.
  • P_PARM_VALUE: String value.
 
GET_WRK_TEXT(
P_FFS_ID    , 
P_PARM_NAME , 
P_PARM_SEQ  , 
DEFVAL      ) RETURN VARCHAR2

Get the stored value for the item called P_PARM_NAME. If there's no item called P_PARM_NAME, then return the default-value. The data type of the default value is the expected data type for the item.

Parameters:

  • P_FFS_ID: Use the HWM_FFS_ID context (see Appendix 2: Contexts).
  • P_PARM_NAME: Any alphanumeric name.
  • P_PARM_SEQ: Set to 0 unless storing multiple values with the same PARM_NAME.
  • DEFVAL: Default value VARCHAR2
  • RETURN VARCHAR2: Return a varchar value
 
GET_WRK_NUM(
P_FFS_ID    , 
P_PARM_NAME , 
P_PARM_SEQ  , 
DEFVAL      ) RETURN NUMBER

Same as GET_WRK_TEXT, except it's used to return numeric values.

Parameters:

  • P_FFS_ID: Use the HWM_FFS_ID context (see Appendix 2: Contexts).
  • P_PARM_NAME: Any alphanumeric name.
  • P_PARM_SEQ: Set to 0 unless storing multiple values with the same PARM_NAME.
  • DEFVAL: Default value NUMBER
  • RETURN VARCHAR2: Return a NUMBER value
 
GET_WRK_DATE(
P_FFS_ID    , 
P_PARM_NAME , 
P_PARM_SEQ  , 
DEFVAL      ) RETURN DATE

Same as GET_WRK_TEXT, except it's used to return date values.

Parameters:

  • P_FFS_ID: Use the HWM_FFS_ID context (see Appendix 2: Contexts).
  • P_PARM_NAME: Any alphanumeric name.
  • P_PARM_SEQ: Set to 0 unless storing multiple values with the same PARM_NAME.
  • DEFVAL: Default value DATE
  • RETURN VARCHAR2: Return a DATE value

Rule Parameter and Header Values Functions

Use these workforce management functions to get rule header and parameter values. Any names used in a rule parameter function appear as input parameter values on the rule template Parameters page.

Function Description 
GET_HDR_TEXT( 
P_RULE_ID     ,
P_COLUMN_NAME ,
DEFVAL       )
RETURN VARCHAR2

Use this function to get these text field values, which are set in the basic information section of the rule template definition page.

Parameters:

  • P_RULE_ID: Use HWM_RULE_ID context (see Appendix 3: Contexts)
  • P_COLUMN_NAME: Use one of these valid column names.
Column Name Description Possible Return Values
RUN_TBB_LEVEL Reporting level DAY, DETAIL, TIMECARD
RULE_EXEC_TYPE Rule execution type CREATE, UPDATE
SUPPRESS_DUP_MSGS Suppress duplicate messages display Y, N
INCLUDE_EMPTY_TC Process empty time cards Y, N
  • DEFVAL: Default value
  • RETURN VARCHAR2: Return varchar value
 
FUNCTION get_hdr_num(
P_rule_Id  In Number, p_column_name IN VARCHAR2,
defval       IN Number )
  RETURN Number;

Use this function to get the numeric field value set in the basic information section of the rule or rule template definition page.

Parameters:

  • P_RULE_ID: Use HWM_RULE_ID context (see Appendix 3: Contexts)
  • P_COLUMN_NAME: Use this valid column name.
Column Name Description Possibl Return Values
RUN_TBB_LEVEL Reporting level DAY, DETAIL, TIMECARD
  • DEFVAL: Default value
  • RETURN Number: Return numeric value
 
GET_RVALUE_NUMBER(
P_RULE_ID     ,
P_VALUE_NAME ,
DEFVAL       )
RETURN NUMBER

Use this function to get numeric values; set in the Rule Parameters section of the rule.

Note: A rule template uses the name from P_VALUE_NAME to populate the Parameters section of the rule template.

Parameters:

  • P_RULE_ID: Use the HWM_RULE_ID context (see Appendix 2: Contexts).
  • P_VALUE_NAME: Use only upper case alpha characters and underscores. The rule template uses this name to populate the Formula Parameter Name section of the Parameter page.
  • DEFVAL: Default value
  • RETURN NUMBER: Return numeric value
 
GET_RVALUE_TEXT(
P_RULE_ID     ,
P_VALUE_NAME ,
DEFVAL       ) 
RETURN VARCHAR2

Same as GET_RVALUE_NUMBER, except it returns string values.

Parameters:

  • P_RULE_ID: Use the HWM_RULE_ID context (see Appendix 2: Contexts).
  • P_VALUE_NAME: Use only upper case alpha characters and underscores. The rule template uses this name to populate the Formula Parameter Name section of the Parameter page.
  • DEFVAL: Default value
  • RETURN NUMBER: Return varchar value
 
GET_RVALUE_DATE(
P_RULE_ID     ,
P_VALUE_NAME ,
DEFVAL       )
RETURN DATE

Same as GET_RVALUE_NUMBER, except it returns date values.

Parameters:

  • P_RULE_ID: Use the HWM_RULE_ID context (see Appendix 2: Contexts).
  • P_VALUE_NAME: Use only upper case alpha characters and underscores. The rule template uses this name to populate the Formula Parameter Name section of the Parameter page.
  • DEFVAL: Default value
  • RETURN NUMBER: Return date value

Miscellaneous Functions

Function Description 
ADD_RLOG(
P_FFS_ID    ,  
P_RULE_ID   ,
P_LOG_TEXT )

Create a log for tracing and debug. All logs with this function appear on the Analyze Rule Processing Details page of the Time Management work area. They're in the Rule Processing Log column of the Processing Results section.

Parameters:

  • P_FFS_ID: Use the HWM_FFS_ID context (see Appendix 2: Contexts).
  • P_RULE_ID: Use the HWM_RULE_ID context (see Appendix 2: Contexts).
  • P_LOG_TEXT: Any string for log or debug purposes.
 
GET_OUTPUT_MSG1(P_APP_SHORT_NAME,
P_MESSAGE_NAME  ,
TOKEN1_NAME     ,
TOKEN1_VALUE   )
RETURN VARCHAR2

In time entry rule formulas, use this function to format the data repository message for output.

Note: Any message that is passed as output for the data repository MUST use the appropriate GET_OUTPUT_MSG function for proper formatting.

Use this function for the message code with a single token. It creates a single message string with one token for the output, formatted for use in workforce management.

Parameters:

  • P_APP_SHORT_NAME: 3-character application short name, in most cases FND
  • P_MESSAGE_NAME: The message code as defined in the FND_MESSAGES table
  • TOKEN1_NAME: The token name as defined in in the FND_MESSAGES table
  • TOKEN1_VALUE: The token Value
  • RETURN VARCHAR2: Formatted message ready for output

Example:

message_name: 
'HWM_FF_TER_PERIOD_GT_MAX_WRN'

 message_text :  
 The hours entered for the period exceed the maximum limit {DEF_LIMIT} defined for the time card.

  from fnd_messages 

P_MESSAGE_NAME: HWM_FF_TER_PERIOD_GT_MAX_WRN
TOKEN1_NAME:    'DEF_LIMIT'
TOKEN1_VALUE:    8

    OUT_MSG = get_output_msg1 ( 'HWM' , P_MESSAGE_NAME, TOKEN1_NAME  , TOKEN1_VALUE)

GET_OUTPUT_MSG2(P_APP_SHORT_NAME,
P_MESSAGE_NAME  ,
TOKEN1_NAME     ,
TOKEN1_VALUE  ,
TOKEN2_NAME     ,
TOKEN2_VALUE   )
RETURN VARCHAR2

In time entry rule formulas, use this function to format the data repository message for output.

Same as GET_OUTPUT_MSG1, except for messages that use two tokens.

 
GET_OUTPUT_MSG( P_APP_SHORT_NAME ,
P_MESSAGE_NAME   )
RETURN VARCHAR2

In time entry rule formulas, use this function to format the data repository message for output.

Same as GET_OUTPUT_MSG1, except for messages with no tokens.

 
get_msg_tags 
p_msg_tags   IN VARCHAR2 )
  RETURN VARCHAR2

Used only with workforce compliance rules. Add comma-separated tags to the message.

Sample:

 pTagVals = ' tag1, tag2, Compliance '
 tm = get_msg_tags(pTagVals)  ||  get_output_msg1 ( 'FND' ,pMsgCd ,tkn , val )

FUNCTION time_hhmm_to_dec(
time_dec IN NUMBER )
  RETURN NUMBER;
This function converts time to decimal, for example, 2.59 hhmm to 2.983 dec. 
FUNCTION raise_error(
    p_ffs_id   IN NUMBER ,
    p_rule_id   IN NUMBER ,
    p_message_text IN varchar2 )
return number ;
Stop processing and raise a fatal error. 
FUNCTION get_measure_from_time(
start_time IN date,
stop_time IN date )
  RETURN NUMBER;
Calculate duration (measure) using the start and stop date_time stamps. 
FUNCTION get_date_day_of_Week(
 p_in_date   IN date )
  RETURN VARCHAR2;
Return the short (3 character), upper case day of week from the date, such as MON and TUE. 
FUNCTION get_is_date_same_as_dow(
    p_in_date   IN date ,
    p_in_day_of_week   IN VARCHAR2 )
  RETURN VARCHAR2;
Compare the date with the string day of week and return YES or No. 
FUNCTION is_date_between(
compare_date  IN date, 
start_time  IN date,
stop_time   IN date  )
  RETURN number;

Verify if compare_date is between the start_time and stop_time.

  • If the date time is between the start and stop dates, the function returns 1.
  • If the date time is NOT between the start and stop dates, the function returns 0.
 
FUNCTION get_current_date
  RETURN date;
Return the current server date and time. The date time isn't based on user Time zone. 
FUNCTION get_current_time (
 o_staus_log	varchar2,
 o_current_time  date,
 resource_id	 NUMBER,
 zone_type	IN varchar2,
 zone_code  	IN varchar2,
 gmt_offset   IN  number,
)
  RETURN  O_STATUS  VARCHAR2;

Get the current time stamp for the given resource, time zone type, and so on.

Input:

resource_id: if resource Id provided function will return current time based on resource (person) time zone.
If Resource ID is not provided or to get current time for specific time zone:
 zone_type - valid Values ' ZONE' , ' OFFSET'
zone_code   Zone code, if Zone type set to 'Zone'
 gmt_offset   gmt Offset, if zone type is set to ' OFFSET'

Outputs
o_current_time : current time for specified user/time zone, If Status is ''SUCCESS'

Return  O_STATUS   (   'SUCCESS',            'FAILED') - Status
o_staus_log: If Status is Failed, information for failure

FUNCTION get_duration_start_to_now (
 o_staus_log    varchar2,
 o_calculated_Hours   number,
 resource_id  IN NUMBER,
  start_Time  IN date,
 start_zone_type   IN  varchar2,
 start_zone_code  IN  varchar2,
  start_gmt_offset  IN  number  )
  RETURN O_STATUS  VARCHAR2;

Calculate the duration from start_time to current time based on the user's time zone or the supplied zone and offset.

Input:

resource_id: if resource Id provided function will return current time based on resource (person) time zone.
start_Time: Start Date time, of duration to current time
If Resource ID is not provided or to get current time for specific time zone:
 zone_type - valid Values ' ZONE' , ' OFFSET'
zone_code   Zone code, if Zone type set to 'Zone'
 gmt_offset   gmt Offset, if zone type is set to ' OFFSET'

Outputs 
o_calculated_Hours: Calculated duration(hours) time for specified user/time zone, If Status is ''SUCCESS'

Return  O_STATUS   (   'SUCCESS',            'FAILED') - Status
o_staus_log: If Status is Failed, information for failure

get_unprocessed_event_set 
( staus_log out varchar2,
 row_count  out number,
 event_rs_key out varchar2,
  resource_id   IN NUMBER,
 start_date      IN date,
  end_date        IN date )
  RETURN STATUS  VARCHAR2;

Select unprocessed events for a given resource and date range, similar to (executeQuery). The function caches the identified data. Use the get_unprocessed_event_rec function to loop through and retrieve each row.

STATUS TEXT Output. Request status, possible values are:

  • 'NO_REC_FOUND'
  • 'SUCCESS'
  • 'FAILED'

status_log TEXT The output log file containing detailed information related to the 'FAILED' status and corresponding process data.

row_count  NUMBER  Number of rows selected
event_rs_key  Text  - Output row set key required for get_unprocessed_event_rec function
  resource_id  NUMBER  Input Resource Id used to select records
 start_date   date  Start Date for row selection
  end_date     date   End date for row selection

get_unprocessed_event_rec 
(staus_log  out   varchar2,
 Event_Type out   varchar2,
 Event_Time out   date,
 Event_InOut  out   varchar2,
 event_rs_key  IN VARCHAR2 ,
 row_index  IN NUMBER  ) 
RETURN STATUS  VARCHAR2;

Get individual rows selected by the get_unprocessed_event_set function.

STATUS TEXT Output. Request status, possible values are:

  • 'NO_REC_FOUND'
  • 'SUCCESS'
  • 'FAILED'

staus_log TEXT The output log file containing detailed information related to the 'FAILED' status and corresponding process data.

Event output information: Event_Type, Event_Time, Event_InOut 
event_rs_key  Text  - Input row set key from get_unprocessed_event_set function

row_index   use index from 0 to row_count from get_unprocessed_event_set function to loop through and select rows

get_null_ff_text()
  RETURN VARCHAR2;
Get the time rule or formula null value. The returned value is text. 
get_null_ff_num ()
  RETURN NUMBER  ;
Get the time rule or formula null value. The returned value is numeric. 
get_null_ff_date ()
  RETURN date;
Get the time rule or formula null value. The returned value is a date.

Data Access View Entry (DAVE) Functions

These functions let you filter and summarize time repository data that aren't available in the current time card period. The details for each DAVE function are included in DAVE Functions for Workforce Management Fast Formulas chapter.