EXM_PERDIEM_POLICIES
This table contains information about per diem policies, including fixed rate rules and rates.
Details
-
Schema: FUSION
-
Object owner: EXM
-
Object type: TABLE
-
Tablespace: FUSION_TS_TX_DATA
Primary Key
| Name | Columns |
|---|---|
|
EXM_PERDIEM_POLICIES_PK |
PERDIEM_POLICY_ID |
Columns
| Name | Datatype | Length | Precision | Not-null | Comments |
|---|---|---|---|---|---|
| PERDIEM_POLICY_ID | NUMBER | 18 | Yes | Unique identifier of per diem policy. | |
| OBJECT_VERSION_NUMBER | NUMBER | 9 | Yes | Used to implement optimistic locking. This number is incremented every time that the row is updated. The number is compared at the start and end of a transaction to detect whether another session has updated the row since it was queried. | |
| POLICY_NAME | VARCHAR2 | 60 | Yes | Name of a per diem policy. | |
| DESCRIPTION | VARCHAR2 | 240 | Description of a per diem policy. | ||
| STATUS | VARCHAR2 | 30 | Status of a per diem policy.Values are from the lookup EXM_POLICY_STATUS. | ||
| RATES_SOURCE_CODE | VARCHAR2 | 30 | Indicates if the policy is created based on rates entered by the user or from rates uploaded from a Conus or OConus file. | ||
| MEALS_RATE_SOURCE_CODE | VARCHAR2 | 30 | Rates that are based on local rates or proportional rates. | ||
| FREE_MEAL_DEDUCTION_CODE | VARCHAR2 | 30 | Type of deduction, by meal type and amount or by meal type and amount with percentage deductions over a specified limit. | ||
| MEALS_AMOUNT_LIMIT | NUMBER | Amount limit to determine deduction for free meal. | |||
| DAY_TRAVEL_ELIGIB_HOURS | NUMBER | 4 | Indicates the minimum hours of travel to be eligible for same day travel. | ||
| ALLOW_ACTUAL_EXPENSE_FLAG | VARCHAR2 | 1 | Indicates if actual expenses are allowed instead of per diem. | ||
| PERDIEM_POLICY_TYPE | VARCHAR2 | 30 | Type of travel for which a per diem policy is applicable, such as Generic, Domestic, or Foreign. Values are from the lookup ORA_EXM_PERDIEM_POLICY_TYPE. | ||
| TIME_BASED_RATE_MODEL_FLAG | VARCHAR2 | 1 | Reserved for future use. Indicates whether per diem policy rates vary based on travel time for each rate time period. | ||
| RATE_FORMULA_METHOD_CODE | VARCHAR2 | 30 | Formula calculation method for per diem allowance, such as Daily Rate, Sum Up Separate Rate Allowances, Daily Time Based Rate, or Sum Up Separate Rates -Time Based. Values are from the lookup ORA_EXM_RATE_FORMULA_METHOD. | ||
| CURRENCY_OPTION_CODE | VARCHAR2 | 30 | Currency option code indicating whether per diem policy rates in a policy are defined in a single currency or in a currency specific to locations. | ||
| CURRENCY_CODE | VARCHAR2 | 15 | Three-letter ISO currency code for the single currency in which rates for the policy are defined. | ||
| ALLOW_RATE_CONVERSION_FLAG | VARCHAR2 | 1 | Indicates whether a per diem policy allows the single currency rates defined in a policy to be converted to a different reimbursement currency. | ||
| RATE_MULTIPLIER_CODE | VARCHAR2 | 30 | Code that indicates whether the rate multiplier is number of days or nights when the per diem policy calculation method is set to Daily Rate. Values are from the lookup ORA_EXM_PERDIEM_MULTIPLIER. | ||
| RATE_INCLUDE_MEALS_FLAG | VARCHAR2 | 1 | Indicates whether the per diem allowance defined for the policy includes meals. | ||
| RATE_INCLUDE_ACCOM_FLAG | VARCHAR2 | 1 | Indicates whether the per diem reimbursement defined for the policy includes accommodations. | ||
| RATE_INCLUDE_OTHER_FLAG | VARCHAR2 | 1 | Indicates whether the per diem reimbursement defined for the policy includes other expenses. | ||
| MEAL_RATE_TYPE_CODE | VARCHAR2 | 30 | Code indicating meal allowance type for a policy, such as Daily Rate, Rates By Meal Type or Rates by Number of Meals. Values are from the lookup ORA_EXM_PERDIEM_MEAL_RATE. | ||
| MEAL_INCL_INCIDENTAL_FLAG | VARCHAR2 | 1 | Indicates whether the meals allowance also includes incidental expense charges. | ||
| MEAL_DEDUCTION_FLAG | VARCHAR2 | 1 | Indicates that the per diem policy deducts free meals from the calculated meals allowance. | ||
| ACCOM_DEDUCTION_FLAG | VARCHAR2 | 1 | Indicates whether this per diem policy deducts free accommodations from the calculated accommodation allowance. | ||
| ACCOM_FREE_ONLY_FLAG | VARCHAR2 | 1 | Indicates that the per diem policy permits free accommodations only allowance. | ||
| ACCOM_MIN_NIGHT_HOURS_STAY | NUMBER | 4 | Minimum number of hours of stay to qualify for accommodations allowance. | ||
| ENABLED_LOCATION_FLAG | VARCHAR2 | 1 | Indicates whether per diem policy rates vary by location type, such as geographical location or zone. | ||
| LOCATION_TYPE_CODE | VARCHAR2 | 30 | Lookup type that indicates policy rates vary by geographical locations. | ||
| ZONE_TYPE_CODE | VARCHAR2 | 30 | Lookup type that indicates policy rates vary by zones. | ||
| ENABLED_ROLE_FLAG | VARCHAR2 | 1 | Indicates whether per diem allowances vary based on a role type such as, job management level or salary threshold. | ||
| ROLE_CALC_METHOD | VARCHAR2 | 30 | Calculation method for role based allowances for a per diem policy, such as Rate, Percentage, or Spreadsheet. Values are from the lookup ORA_EXM_PERDIEM_CALCMETH. | ||
| POLICY_ROLE_CODE | VARCHAR2 | 30 | Role type by which per diem allowances vary, such as job management level or salary. | ||
| ENABLED_SEASON_RATE_FLAG | VARCHAR2 | 1 | Indicates whether accommodation rates vary by season when accommodations are included in per diem. | ||
| ENABLED_ACCOM_TYPE_FLAG | VARCHAR2 | 1 | Indicates whether per diem policy rates vary by type of accommodation. | ||
| ENABLED_LONG_TERM_FLAG | VARCHAR2 | 1 | Indicates whether per diem policy has rules or rates defined for long-term travel. | ||
| ENABLED_WEEKEND_HOLID_FLAG | VARCHAR2 | 1 | Indicates whether per diem policy has rules or rates defined for weekend and holiday travel. | ||
| MIN_TRIP_DURAT_FLAG | VARCHAR2 | 1 | Indicates whether this policy has a minimum trip duration for per diem eligibility. | ||
| MIN_DURATION_HOURS | NUMBER | 4 | Minimum travel time hours to qualify for per diem or meals rate. | ||
| MIN_DURATION_MINUTES | NUMBER | 4 | Minimum travel time minutes to qualify for per diem or meals rate. | ||
| MIN_DISTANCE_FROM_HOME | NUMBER | Minimum travel distance from home to qualify for per diem rate. | |||
| MIN_DISTANCE_FROM_OFFICE | NUMBER | Minimum travel distance from office to qualify for per diem rate. | |||
| WITHIN_EMPLOYEE_CITY_FLAG | VARCHAR2 | 1 | Indicates whether a policy allows travel within the employee's home city to qualify for per diem or meals allowance. | ||
| MEALS_CALC_METHOD_CODE | VARCHAR2 | 30 | Calculation method for per diem or meals allowances, such as Rate, Percentage, or Spreadsheet. Values are from the lookup ORA_EXM_PERDIEM_CALCMETH. | ||
| MEALS_DEDUCTION_CODE | VARCHAR2 | 30 | Code indicating meals deduction type for a per diem policy, such as Daily Meals Deduction, Deduction by Meal Type, or Deductions by Number of Meals. Values are from the lookup ORA_EXM_PERDIEM_MEAL_DEDUCTION. | ||
| MEALS_DEDUCT_CALC_METH_CODE | VARCHAR2 | 30 | Calculation method for meals deductions, such as Rate, Percentage, or Spreadsheet. Values are from the lookup ORA_EXM_PERDIEM_CALCMETH. | ||
| BREAKFAST_ELIG_START_HOUR | NUMBER | 4 | Starting time hour for breakfast to qualify for a breakfast allowance. | ||
| BREAKFAST_ELIG_START_MINUTE | NUMBER | 4 | Starting time minutes for breakfast to qualify for a breakfast allowance. | ||
| BREAKFAST_ELIG_END_HOUR | NUMBER | 4 | Ending time hour for breakfast to qualify for a breakfast allowance. | ||
| BREAKFAST_ELIG_END_MINUTE | NUMBER | 4 | Ending time minute for breakfast to qualify for a breakfast allowance. | ||
| LUNCH_ELIG_START_HOUR | NUMBER | 4 | Starting time hours for lunch to qualify for a lunch allowance. | ||
| LUNCH_ELIG_START_MINUTE | NUMBER | 4 | Starting time minutes for lunch to qualify for a lunch allowance. | ||
| LUNCH_ELIG_END_HOUR | NUMBER | 4 | Ending time hour for lunch to qualify for a lunch allowance. | ||
| LUNCH_ELIG_END_MINUTE | NUMBER | 4 | Ending time minutes for lunch to qualify for a lunch allowance. | ||
| DINNER_ELIG_START_HOUR | NUMBER | 4 | Starting time hours for dinner to qualify for a dinner allowance. | ||
| DINNER_ELIG_START_MINUTE | NUMBER | 4 | Starting time minutes for dinner to qualify for a dinner allowance. | ||
| DINNER_ELIG_END_HOUR | NUMBER | 4 | Ending time hours for dinner to qualify for a dinner allowance. | ||
| DINNER_ELIG_END_MINUTE | NUMBER | 4 | Ending time minutes for dinner to qualify for a dinner allowance. | ||
| MINIMUM_REIMBURSEMENT | NUMBER | Minimum per diem or meals allowance that overrides meals deduction rules to ensure employees are guaranteed a minimum reimbursement amount after deductions. | |||
| ACCOM_REQ_MEALS_ELIGIB_FLAG | VARCHAR2 | 1 | Indicates whether a per diem policy requires an employee to meet the meals allowance criteria before he is eligible for an accommodations allowance. | ||
| ACCOM_RATE_CALC_METHOD_CODE | VARCHAR2 | 30 | Calculation method for accommodations allowance, such as Rate, Percentage, or Spreadsheet. Values are from the lookup ORA_EXM_PERDIEM_CALCMETH. | ||
| ACCOM_DEDUCT_CALC_METH_CODE | VARCHAR2 | 30 | Calculation method for accommodations deduction, such as Rate, Percentage, or Spreadsheet. Values are from the lookup ORA_EXM_PERDIEM_CALCMETH. | ||
| ACCOM_MIN_TRIP_DURAT_FLAG | VARCHAR2 | 1 | Indicates whether there is a minimum trip duration requirement before an employee is eligible for an accommodations allowance. | ||
| ACCOM_MIN_DURATION_HOURS | NUMBER | 4 | Minimum travel time hours for trip to qualify for an accommodations allowance. | ||
| ACCOM_MIN_DURATION_MINUTES | NUMBER | 4 | Minimum travel time minutes for trip to qualify for an accommodations allowance. | ||
| ACCOM_ELIGIB_START_HOUR | NUMBER | 4 | Starting night time hour for trip to qualify for an accommodations allowance. | ||
| ACCOM_ELIGIB_START_MINUTE | NUMBER | 4 | Starting night time minutes for trip to qualify for an accommodations allowance. | ||
| ACCOM_ELIGIB_END_HOUR | NUMBER | 4 | Ending night time hours for trip to qualify for an accommodations allowance. | ||
| ACCOM_ELIGIB_END_MINUTE | NUMBER | 4 | Ending night time minutes for trip to qualify for an accommodations allowance. | ||
| OTHERS_ELIGIB_CRITERIA_CODE | VARCHAR2 | 30 | Eligibility criteria for receiving an incidental allowance, such as None, Meets Meals Eligibility, or Meets Meals and Accommodations Eligibility. Values are from the lookup ORA_EXM_PERDIEM_ELIG_CRITERIA. | ||
| OTHERS_CALC_METHOD_CODE | VARCHAR2 | 30 | Calculation method for an incidental allowance, such as Rate, Percentage, or Spreadsheet. Values are from the lookup ORA_EXM_PERDIEM_CALCMETH. | ||
| HOLIDAY_CALC_METHOD_CODE | VARCHAR2 | 30 | Calculation method for weekend and holiday allowances, such as Rate or Percentage. Values are from the lookup ORA_EXM_PERDIEM_CALCMETH. | ||
| WEEKEND_DAY1_CODE | VARCHAR2 | 30 | Code that indicates the first weekend day for a specific geographic region. For example, the first weekend day for a specific geographic region may not be Saturday as it is in the United States. | ||
| WEEKEND_DAY2_CODE | VARCHAR2 | 30 | Code that indicates the second weekend day for a specific geographic region. For example, the first weekend day for a specific geographic region may not be Sunday as it is in the United States. | ||
| LONG_TERM_CALC_METHOD | VARCHAR2 | 30 | Calculation method for long-term travel allowances. Values are from the lookup ORA_EXM_PERDIEM_CALCMETH. | ||
| SPECIFIC_FIRST_LAST_DAY_FLAG | VARCHAR2 | 1 | Indicates whether a per diem policy has specific rates for first day and last day. | ||
| TIME_PERIOD_CODE | VARCHAR2 | 30 | Code that indicates time period boundaries for a per diem day, such as Midnight to Midnight or Start Time Plus 24 Hours. Values are from the lookup ORA_EXM_PERDIEM_TIME_PERIOD. | ||
| TIME_CALC_METHOD_CODE | VARCHAR2 | 30 | Calculation method for time threshold allowances, such as Rate, Percentage, or Spreadsheet. Values are from the lookup ORA_EXM_PERDIEM_CALCMETH. | ||
| TIME_ALLOW_SAME_DAY_FLAG | VARCHAR2 | 1 | Indicates whether this per diem policy will allow same day trip. | ||
| TIME_SAME_DAY_RULE_CODE | VARCHAR2 | 30 | Code that indicates whether specific same day travel rates are applicable to trips less than 24 hours or to trips without an overnight. Values are from the lookup ORA_EXM_PERDIEM_SAME_DAY_RULE. | ||
| TIME_SAME_DAY_CALC_METHOD | VARCHAR2 | 30 | Calculation method for same day travel time threshold allowances, such as Rate, Percentage, or Spreadsheet. Values are from the lookup ORA_EXM_PERDIEM_CALCMETH. | ||
| ATTRIBUTE_CATEGORY | VARCHAR2 | 150 | Descriptive Flexfield: structure definition of the user descriptive flexfield. | ||
| ATTRIBUTE_CHAR1 | VARCHAR2 | 150 | Descriptive Flexfield: segment of the user descriptive flexfield. | ||
| ATTRIBUTE_CHAR2 | VARCHAR2 | 150 | Descriptive Flexfield: segment of the user descriptive flexfield. | ||
| ATTRIBUTE_CHAR3 | VARCHAR2 | 150 | Descriptive Flexfield: segment of the user descriptive flexfield. | ||
| ATTRIBUTE_CHAR4 | VARCHAR2 | 150 | Descriptive Flexfield: segment of the user descriptive flexfield. | ||
| ATTRIBUTE_CHAR5 | VARCHAR2 | 150 | Descriptive Flexfield: segment of the user descriptive flexfield. | ||
| ATTRIBUTE_CHAR6 | VARCHAR2 | 150 | Descriptive Flexfield: segment of the user descriptive flexfield. | ||
| ATTRIBUTE_CHAR7 | VARCHAR2 | 150 | Descriptive Flexfield: segment of the user descriptive flexfield. | ||
| ATTRIBUTE_CHAR8 | VARCHAR2 | 150 | Descriptive Flexfield: segment of the user descriptive flexfield. | ||
| ATTRIBUTE_CHAR9 | VARCHAR2 | 150 | Descriptive Flexfield: segment of the user descriptive flexfield. | ||
| ATTRIBUTE_CHAR10 | VARCHAR2 | 150 | Descriptive Flexfield: segment of the user descriptive flexfield. | ||
| ATTRIBUTE_CHAR11 | VARCHAR2 | 150 | Descriptive Flexfield: segment of the user descriptive flexfield. | ||
| ATTRIBUTE_CHAR12 | VARCHAR2 | 150 | Descriptive Flexfield: segment of the user descriptive flexfield. | ||
| ATTRIBUTE_CHAR13 | VARCHAR2 | 150 | Descriptive Flexfield: segment of the user descriptive flexfield. | ||
| ATTRIBUTE_CHAR14 | VARCHAR2 | 150 | Descriptive Flexfield: segment of the user descriptive flexfield. | ||
| ATTRIBUTE_CHAR15 | VARCHAR2 | 150 | Descriptive Flexfield: segment of the user descriptive flexfield. | ||
| ATTRIBUTE_NUMBER1 | NUMBER | Descriptive Flexfield: segment of the user descriptive flexfield. | |||
| ATTRIBUTE_NUMBER2 | NUMBER | Descriptive Flexfield: segment of the user descriptive flexfield. | |||
| ATTRIBUTE_NUMBER3 | NUMBER | Descriptive Flexfield: segment of the user descriptive flexfield. | |||
| ATTRIBUTE_NUMBER4 | NUMBER | Descriptive Flexfield: segment of the user descriptive flexfield. | |||
| ATTRIBUTE_NUMBER5 | NUMBER | Descriptive Flexfield: segment of the user descriptive flexfield. | |||
| ATTRIBUTE_DATE1 | DATE | Descriptive Flexfield: segment of the user descriptive flexfield. | |||
| ATTRIBUTE_DATE2 | DATE | Descriptive Flexfield: segment of the user descriptive flexfield. | |||
| ATTRIBUTE_DATE3 | DATE | Descriptive Flexfield: segment of the user descriptive flexfield. | |||
| ATTRIBUTE_DATE4 | DATE | Descriptive Flexfield: segment of the user descriptive flexfield. | |||
| ATTRIBUTE_DATE5 | DATE | Descriptive Flexfield: segment of the user descriptive flexfield. | |||
| ATTRIBUTE_DATETIME1 | TIMESTAMP | Descriptive Flexfield: segment of the user descriptive flexfield. | |||
| ATTRIBUTE_DATETIME2 | TIMESTAMP | Descriptive Flexfield: segment of the user descriptive flexfield. | |||
| CREATION_DATE | TIMESTAMP | Yes | Who column: indicates the date and time of the creation of the row. | ||
| CREATED_BY | VARCHAR2 | 64 | Yes | Who column: indicates the user who created the row. | |
| LAST_UPDATE_DATE | TIMESTAMP | Yes | Who column: indicates the date and time of the last update of the row. | ||
| LAST_UPDATED_BY | VARCHAR2 | 64 | Yes | Who column: indicates the user who last updated the row. | |
| LAST_UPDATE_LOGIN | VARCHAR2 | 32 | Who column: indicates the session login associated to the user who last updated the row. |
Indexes
| Index | Uniqueness | Tablespace | Columns |
|---|---|---|---|
| EXM_PERDIEM_POLICIES_N1 | Non Unique | FUSION_TS_TX_DATA | STATUS |
| EXM_PERDIEM_POLICIES_PK | Unique | FUSION_TS_TX_DATA | PERDIEM_POLICY_ID |
| EXM_PERDIEM_POLICIES_U1 | Unique | FUSION_TS_TX_DATA | UPPER("POLICY_NAME") |