Skip to main content

Reference Library

123 articles

Custom SQL Fields Commands

Custom SQL fields - Command to a static value to a column
Custom SQL fields - Command to add st, nd, rd or th after a day
Custom SQL fields - Command to show full name of the month
Custom SQL fields - Command to display different date formats beginning with day
Custom SQL fields - List of commands for date types
Custom SQL fields - Command to display different date formats beginning with month
Custom SQL fields - Command to calculate duration in months between two dates
Custom SQL fields - Command to calculate duration in days between two dates
Custom SQL fields - Command to display the number of days in the month of a certain date
Custom SQL fields - Command to extend date by X number (day, week, year)
Custom SQL fields - Command to display different date formats beginning with year
DBO function to return Exchange rate for date
DBO function to return first and last name from user id
DBO function to return host country 2 character ISO code from assignment id
DBO function to return lookup code from custom field with lookup values
DBO function to return account taxability from account id and country
Overview and List of DBO Functions
DBO function to return email address from user id
DBO function to return user type from user id
DBO function to return currency name from 3 character currency code
DBO function to return home country 2 character ISO code from assignment Id
DBO function to return 2 letter code for country and state/province from country name and state/province name
DBO function to return 3 character code from country name
DBO function to return dependent details from assignment id and date
DBO function to return first and last name of current user in the system
DBO function to return dependent details codes from assignment id and date
DBO function to return lookup maintenance client code from field name with lookup values
DBO function to return lookup maintenance code from field name with lookup values
DBO function to return segment lookup code from segment display value, segment level and company id
DBO function to return segment lookup code from assignment id, segment level and value of 'FROM'/'TO'
DBO function to return resource contact information from assignment id and the resource code
DBO function to retrieve an annualized line item value from any cost estimate or compensation calculation
DBO function to retrieve any value from the Compensation Data Repository
DBO function to return comments and notes for rejected expenses for an expense entry
DBO function to convert any number to English word
DBO function to convert any number to French word
COALESCE() Function
DATEPART() Function
REPLACE() Function
SUBSTRING() Function
LEN() Function
DATEADD() Function
CHARINDEX() Function
CASE Statement Function
GETDATE() Function
LEFT() and RIGHT() Functions
ABS() Function
DAY() Function
ROUND() Function
LOWER() and UPPER() Functions
DATENAME() Function
DATEDIFF() Function
MONTH() Function
YEAR() Function
Useful SQL functions
CONVERT( ) Function
CAST( ) Function
FORMAT() Function
STRING_AGG Function

Views

Overview of Views
Assignment Derived Calculations View
How is Calculated Assignment Start Date derived for Assignment Derived Calculations view
How is Calculated Assignment End Date derived for Assignment Derived Calculations view
Assignment Contact 1-10 View
Tax Estimator Calculation Details View
Assignment Compensation and Payroll Information View
Vendor Integration Orders View
Latest Passports by Assignment View
Tax Equalization Refund Payment View
Cost Estimate Line Item Amount Per Column Year View
Compensation Calculation View
List of views
Assignment Document View
Checklist Template Audit View
Assignment Dependents View
Company Segment Level Audit View
Assignment Resource View
Assignment Resource User Identifier View
Authorization Resource User Identifier View
Employee Assignments 1 line per Employee View
Merge Document User Report Detail View
Move Management User Report View
Tax Detail Briefing and Return View
Visa/Work Permits User Report View
Comp Worksheet Line Item Current and Previous Effective View
Cost Estimate View
Compensation Worksheet Transaction Changes View
User's Most Recent Successful Login View
How to create a report to return a list of field names for a given view
Account Taxability with Override View
How is the number of Days / Months Remaining on Assignment derived for Assignment Derived Calculations view
How is the number of Days / Months on Assignment derived for Assignment Derived Calculations view
Tax Estimator Hypo Calculation Detail
Comp Worksheet To Pay Register Reconciliation View
Comp Worksheet with Batch Payroll Reporting Detail View