Fast Formula Boss

More Fast Formula Examples For Advanced Benefits, PTO Accruals and Payroll Calculations Bill Stratton BOSS Corporation [email protected] BOSS

2002 Fall OAUG BOSS Copyright  2002

Agenda • Introduction • Crash Course of Fast Formulas – Formula Types – Database Items – Syntax – Indirect Results – Payroll Tables

BOSS

2002 Fall OAUG BOSS Copyright  2002

Agenda (continued) • Real Live Scenarios • Payroll Calculations • • • • BOSS

– Student Federal Work Study Solution PTO Accrual – Accrual Rate for New Hires Advanced Benefits – Coverage Date Rule Fast Formula Debugging Techniques Q&A 2002 Fall OAUG BOSS Copyright  2002

Who is BOSS? •

About BOSS Corporation – BOSS is an acronym that stands for “Better Organization Service Solutions” –

Founded in 1995 by Stephen Adams and Jim Crum

–

Both Stephen and Jim were responsible for Oracle consulting in the Southeast US for Oracle Corporation before forming BOSS

–

BOSS service lines are sharply focused on Oracle Application implementations; Financials, HR/Payroll and Manufacturing, technology consulting, and database administration services

–

Headquarters are located in Atlanta, with additional offices in Dallas, Pittsburgh, Raleigh, and Redwood Shores

BOSS

2002 Fall OAUG BOSS Copyright  2002

•

About BOSS Corporation •

Author of two Oracle Application books • “Special Edition: Using Oracle Applications” • “Special Edition: Using Oracle 11i”

–

Major sponsor at OAUG conferences; National, European, and many regional and local groups – Eleven presentations, papers and panel involvement Fall 2001 OAUG in San Diego – Fourteen presentations, papers and panel involvement at Spring 2002 OAUG in San Diego – Ten presentations, papers and panel involvement at Fall 2002 OAUG in San Diego

BOSS

2002 Fall OAUG BOSS Copyright  2002

Crash Course in Fast Formula • Formula Types – Skip Rules – Validation – Payroll Calculations – Benefit Rules – PTO Accrual Calculations

BOSS

2002 Fall OAUG BOSS Copyright  2002

BOSS

2002 Fall OAUG BOSS Copyright  2002

Crash Course in Fast Formula • Database Items – Variable Names for Complex Joins – Flex Field Segments – Balances (with Dimensions) – Global Values – Element Entry Input Values

BOSS

2002 Fall OAUG BOSS Copyright  2002

Crash Course in Fast Formula • Database Items Examples – ASG_POSITION – POS_KF_FUNCTION_CODE – SCL_ASG_US_TIMECARD_REQUIRED – GROSS_EARNINGS_ASG_GRE_MONTH – LONG_TERM_DISABILITY_AMOUNT_ENTRY_VALUE

BOSS

2002 Fall OAUG BOSS Copyright  2002

Crash Course in Fast Formula • Syntax – – – –

Alias Section Default Section Inputs Section Calculation Section –Assignment Statements –IF/THEN/ELSE Statements –No GOTO or Case Statements – Return Section BOSS

2002 Fall OAUG BOSS Copyright  2002

BOSS

2002 Fall OAUG BOSS Copyright  2002

BOSS

2002 Fall OAUG BOSS Copyright  2002

BOSS

2002 Fall OAUG BOSS Copyright  2002

BOSS

2002 Fall OAUG BOSS Copyright  2002

BOSS

2002 Fall OAUG BOSS Copyright  2002

Crash Course in Fast Formula • Indirect Results – Pass Direct Result to Pay Value – Pass Other Calculations to Other Elements – Make sure other elements have higher priority – Use Formula Results screen to pass RETURN values to elements BOSS

2002 Fall OAUG BOSS Copyright  2002

BOSS

2002 Fall OAUG BOSS Copyright  2002

Crash Course in Fast Formula • Payroll Tables – Defined in the Application – Use Table Structure to define table – Use Table Value to load values – Date-tracked and can be updated by Function personnel – Great place to store rates by database item (Location, People Group) BOSS

2002 Fall OAUG BOSS Copyright  2002

Real Live Scenarios • Student Federal Work Study – Government supplies funds for student employment – Part of Financial Aid Package – Student System Maintains Award and balances – Payroll needs to capture “category” BOSS

2002 Fall OAUG BOSS Copyright  2002

Payroll Calculation Scenario • Step 1, Create EIT – Use Assignment EIT – Tracks Task, Rate and Costing – Tracks FWS Flag –Eligible –Ineligible –Excludable –Summer BOSS

2002 Fall OAUG BOSS Copyright  2002

•insert EIT screen prints here

BOSS

2002 Fall OAUG BOSS Copyright  2002

Payroll Calculation Scenario • Step 2, Fast Formula Function – Returns Rate and FWS Flag – PL/SQL Package – Registered with Oracle Apps – Can be called from Fast Formula

BOSS

2002 Fall OAUG BOSS Copyright  2002

•insert screen print of registered function

BOSS

2002 Fall OAUG BOSS Copyright  2002

Payroll Calculation Scenario • Step 3, Create Information Element – Used to capture dollars by FWS Flag – Will be populated by Indirect Results

BOSS

2002 Fall OAUG BOSS Copyright  2002

•insert screen prints of an information element

BOSS

2002 Fall OAUG BOSS Copyright  2002

Payroll Calculation Scenario • Step 4, Update Fast Formula – Use function to retrieve rate and FWS Flag – Determine which category to place dollars based on FWS Flag

BOSS

2002 Fall OAUG BOSS Copyright  2002

•insert screen prints of Fast Formula logic

BOSS

2002 Fall OAUG BOSS Copyright  2002

BOSS

2002 Fall OAUG BOSS Copyright  2002

BOSS

2002 Fall OAUG BOSS Copyright  2002

Payroll Calculation Scenario • Step 5, Setup Formula Results – Send each category to proper Input Value on Information Element – After payroll is complete, run interface to send records to Student System

BOSS

2002 Fall OAUG BOSS Copyright  2002

BOSS

2002 Fall OAUG BOSS Copyright  2002

•insert screen print of formula results and demonstrate run results

BOSS

2002 Fall OAUG BOSS Copyright  2002

PTO Accrual • Recent enhancements added Fast Formula functionality

• Three types – Calculation – Carryover – Ineligibility

BOSS

2002 Fall OAUG BOSS Copyright  2002

PTO Accrual Templates • PTO Payroll Balance Calculation – Same as 10.7 – One year term, starts on Jan 1 – Frequency based on payroll period – Amounts defined on screens – Carryover effective December 31 – Can now incorporate balances BOSS

2002 Fall OAUG BOSS Copyright  2002

PTO Accrual Templates • PTO Simple Balance Multiplier – Accrual term one year - June 1 – Accrual frequency one month – Accrual amounts in formula – Carryover effective May 31

BOSS

2002 Fall OAUG BOSS Copyright  2002

PTO Accrual Templates • PTO ROLLING ACCRUAL – One year term, no fixed start date – Accrual frequency one month – Accrual amounts in formula – No carryover (use it or lose it)

BOSS

2002 Fall OAUG BOSS Copyright  2002

PTO Accrual Scenario • Will use PTO Payroll Balance Calculation • Will modify sub formula PTO Payroll Period Accrual

• Will create a table to store rates for first year hires by month

BOSS

2002 Fall OAUG BOSS Copyright  2002

•Insert screen prints of table creation

BOSS

2002 Fall OAUG BOSS Copyright  2002

BOSS

2002 Fall OAUG BOSS Copyright  2002

BOSS

2002 Fall OAUG BOSS Copyright  2002

BOSS

2002 Fall OAUG BOSS Copyright  2002

BOSS

2002 Fall OAUG BOSS Copyright  2002

PTO Accrual Scenario • Copy PTO_PAYROLL_BALANCE_CALCULATION to OAUG_PAYROLL_BALANCE_CALCULATION

• Copy PTO_PAYROLL_PERIOD_ACCRUAL to OAUG_PAYROLL_PERIOD_ACCRUAL

• Modify Formulas • Define Plan BOSS

2002 Fall OAUG BOSS Copyright  2002

BOSS

2002 Fall OAUG BOSS Copyright  2002

BOSS

2002 Fall OAUG BOSS Copyright  2002

BOSS

2002 Fall OAUG BOSS Copyright  2002

BOSS

2002 Fall OAUG BOSS Copyright  2002

Advanced Benefits - Eligibility • OAB/OSB includes many opportunities to use FF

• Almost every definition screen includes a “Rule”

• Each screen uses a different Fast Formula Type

• Check FF guide for requirements BOSS

2002 Fall OAUG BOSS Copyright  2002

Advanced Benefits - Eligibility • In our scenario, Coverage starts on the first of the month following 30 days of service

• Exceptions for January and February

BOSS

2002 Fall OAUG BOSS Copyright  2002

•insert screen print of coverage definition screen

BOSS

2002 Fall OAUG BOSS Copyright  2002

•insert screen prints of fast formulas

BOSS

2002 Fall OAUG BOSS Copyright  2002

BOSS

2002 Fall OAUG BOSS Copyright  2002

BOSS

2002 Fall OAUG BOSS Copyright  2002

BOSS

2002 Fall OAUG BOSS Copyright  2002

BOSS

2002 Fall OAUG BOSS Copyright  2002

Fast Formula Debugging Tools • • • •

Use MESG variable to include needed values Place MESG variable in RETURN statement Modify Formula Results Message will be included in Payroll Message Report

• All values in MESG must be character

BOSS

2002 Fall OAUG BOSS Copyright  2002

•insert screen print of Fast Formula with MESG and Return statement

BOSS

2002 Fall OAUG BOSS Copyright  2002

•insert screen print of Formula Results

BOSS

2002 Fall OAUG BOSS Copyright  2002

•insert screen print of messages

BOSS

2002 Fall OAUG BOSS Copyright  2002

Fast Formula Debugging Tools • MESG does not work for OAB/OSB • Create FF Function to post message to custom table

• Can call multiple times

BOSS

2002 Fall OAUG BOSS Copyright  2002

Fast Formula Debugging Tools • • • •

BOSS

Step 1, Create custom table Step 2, Create PL/SQL procedure Step 3, Register Function Step 4, Use Function in FF

2002 Fall OAUG BOSS Copyright  2002

Create function FF_POST_MESSAGE ( FormulaName IN Varchar2, FormulaMessage IN Varchar2) Return Varchar2 IS Begin Insert into FF_POST_MESSAGE values FormulaName,FormulaMessage,SYSDATE); Commit; Return('S'); Exception When Others then Return('F'); End;

BOSS

2002 Fall OAUG BOSS Copyright  2002

•insert screen print of register function

BOSS

2002 Fall OAUG BOSS Copyright  2002

•insert screen print of Fast Formula with function

BOSS

2002 Fall OAUG BOSS Copyright  2002

•insert screen print of SQL query of custom table

BOSS

2002 Fall OAUG BOSS Copyright  2002

Fast Formula Primer

Had Enough???? Q&A Bill Stratton BOSS Corporation [email protected] BOSS

2002 Fall OAUG BOSS Copyright  2002