Loading Benchmark Data

PeopleSoft Expenses accepts benchmark data from two sources: Runzheimer International and the United States federal government. Benchmark data become expense location amounts for cost comparisons and expense management.

Runzheimer International is a consulting firm that distributes travel benchmark data used to establish spending averages and limits. The federal government provides expense rates for the continental United States (CONUS) and foreign countries (OCONUS).

Page Name	Definition Name	Usage
File Inbound Page	EO_FILE_INBOUND	Set up file identifiers for inbound file processing.
Inbound File	EO_FILETOMSG	Initiate inbound flat file processing. This file-to-message processing function reads the file row set and publishes it as a message. PeopleSoft FSCM : Enterprise Components

Use the File Inbound page (EO_FILE_INBOUND) to set up file identifiers for inbound file processing.

PeopleSoft Expenses accepts airline ticket data that populates expense lines. This data is used to reconcile airline tickets with business trips that employees take. To process airline ticket data in your expense system, obtain a comma-delimited file (a .CSV file) from your supplier.

Navigation:

Enterprise Components > Integration Definitions > Inbound File Rule

Loading Runzheimer data

When loading Runzheimer data, select and enter these parameters:

Location	File Identifier	Inbound File	Definition Name	Message Name
United States	BENCHMARK_RNZ_USA_LOAD	BENCHMARK_RNZ_USA_DAT	BENCHMARK_RNZ_USA	BENCHMARK_RNZ_LOAD
Non-United States	BENCHMARK_RNZ_NONUSA_LOAD	BENCHMARK_RNZ_NONUSA_DAT	BENCHMARK_RNZ_NONUSA	BENCHMARK_RNZ_LOAD

After you set up the inbound file rule, access the Inbound File page to run the EOP_PUBLISHF Application Engine process, which publishes the data and uses subscription codes to populate the staging table (EX_BNCHMARK_TAO) and these temporary tables:

EX_BNCHMARK_MRK1_TAO
EX_BNCHMARK_MRK2_TAO
EX_BNCHMARK_MRK3_TAO
EX_BNCHMARK_MRK4_TAO

Subscription PeopleCode on the BENCHMARK_RNZ_LOAD message triggers a PeopleSoft Application Engine program that loads Runzheimer data into the EX_LOC_AMOUNT and EX_LOC_AMT_DTL tables.

Note: Because Runzheimer International provides seasonal data, there may be more than one row of data for an expense location amount. The loading process does not replace existing rows; it adds rows and uses the effective date to differentiate rows of the same expense location amount.

Runzheimer Data Format

When you import Runzheimer data, the comma-delimited input file is formatted as shown in this table:

Field Name	Type	Length	Comments
DATE_STRING	Char - Mixed	25	Date string.
CITY	Char - Mixed	30	City.
STATE	Char - Upper	6	State.
COUNTRY_FLD	Char - Upper	18	Country field.
COUNTRY_NAME	Char - Mixed	30	Country.
RPT_DT	Char - Upper	15	Report date.
FLAG_SEASONAL	Char - Upper	2	Seasonal indicator.
START_DT_JULIAN	Nbr	3	Julian start date.
END_DT_JULIAN	Nbr	3	Julian end date.
CURRENCY_ABRV	Char - Upper	5	Currency abbreviation.
DESCR	Char - Mixed	30	Description.
CUR_EXCHNG_RT	Nbr	7.8	Exchange rate.
AVG_BREAKFAST_AMT	Sign	13.2	Average breakfast amount.
LOW_BREAKFAST_AMT	Sign	13.2	Low breakfast amount.
HIGH_BREAKFAST_AMT	Sign	13.2	High breakfast amount.
AVG_LUNCH_AMT	Sign	13.2	Average lunch amount.
LOW_LUNCH_AMT	Sign	13.2	Low lunch amount.
HIGH_LUNCH_AMT	Sign	13.2	High lunch amount.
AVG_DINNER_AMT	Sign	13.2	Average dinner amount.
LOW_DINNER_AMT	Sign	13.2	Low dinner amount.
HIGH_DINNER_AMT	Sign	13.2	High dinner amount.
AVG_DLX_HOTEL_AMT	Sign	13.2	Average deluxe hotel amount.
LOW_DLX_HOTEL_AMT	Sign	13.2	Low deluxe hotel amount.
HIGH_DLX_HOTEL_AMT	Sign	13.2	High deluxe hotel amount.
AVG_1ST_HOTEL_AMT	Sign	13.2	First class hotel, average amount.
LOW_1ST_HOTEL_AMT	Sign	13.2	First class hotel, low amount.
HIGH_1ST_HOTEL_AMT	Sign	13.2	First class hotel, high amount.
AVG_ECON_HOTEL_AMT	Sign	13.2	Economy hotel, average amount.
LOW_ECON_HOTEL_AMT	Sign	13.2	Economy hotel, low amount.
HIGH_ECONHOTEL_AMT	Sign	13.2	Economy hotel, high amount.
STD_3MEAL_AMT	Sign	13.2	Standard three meal amount.
STD_PER_DIEM_AMT	Sign	13.2	Standard per diem amount
AMT1	Sign	23.3	Amount 1.
DESCR_LN1	Char - Mixed	50	Description line 1.
DESCR_LN2	Char - Mixed	50	Description line 2.
AMT2	Sign	23.3	Amount 2.
DESCR_LN3	Char - Mixed	50	Description line 3.
AMT3	Sign	23.3	Amount 3.
DESCR_LN4	Char - Mixed	50	Description line 4.
AMT4	Sign	23.3	Amount 4.
AMT5	Sign	23.3	Amount 5.
AMT6	Sign	23.3	Amount 6.
LODGING_TAX	Sign	7.8	Typical lodging tax.
START_OF_SEASON	Char	5	Start of season.
END_OF_SEASON	Char	5	End of season.

Loading CONUS Data

When loading CONUS data, select and enter these parameters:

File Identifier	Inbound File	Definition Name	Message Name
CONUS_LOAD	CONUSNM.TXT	CONUS_DATA_FL	CONUS_LOAD

CONUS Data Format

When you import CONUS data, the input file is formatted as shown in this table:

Field Name	Type	Length	Comments
STATE	Char	60	State
LOCALITY/CITY	Char	60	Locality or city
COUNTY and, or other Defined Location	Char	5	County
SEASON BEGIN	Char	5	Beginning date of season
SEASON END	Char	5	Ending date of season.
MAX LODGING	Nbr	17	Maximum lodging amount.
LOCAL MEALS	Nbr	17	No government meals.
PROPORTIONAL MEALS	Nbr	17	Proportional meals.
LOCAL INCIDENTAL	Nbr	17	Local Incidental Expense
MAX PER DIEM RATE	Nbr	17	Maximum per diem rate.
EFFECTIVE DATE	Char	10	Effective date.

Loading Airline Ticket Data

Select and enter these parameters:

File Identifier	Inbound File	Definition Name	Message Name
AIRLINE_TKT	AIRLINETKT.CSV	EX_AIRLINE_TKT	AIRLINE_TKT

Then Use the Inbound File page and run the staging program (EOP_PUBLISHF), which publishes the data and uses subscription codes to populate the EX_AIRLINE_TKT table.

OCONUS Data Format

Select and enter these parameters:

File Identifier	Inbound File	Definition Name	Message Name
OCONUS_LOAD	OCONUSNM.TXT	OCONUS_DATA_FL	OCONUS_LOAD

Note: Because OCONUS provides monthly updates, there may be more than one row of data for an expense location amount. The loading process does not replace existing rows. Instead, it adds rows and uses the effective date to differentiate rows of the same expense location amount.

When you import OCONUS data, the input file is formatted as shown in this table.

Field Name	Type	Length	Comments
COUNTRY	Char	60	Country.
LOCALITY/CITY	Char	60	Locality or city.
SEASON BEGIN	Char	5	Beginning date of season.
SEASON END	Char	5	Ending date of season.
MAX LODGING	Nbr	17	Maximum lodging amount.
NO GOVERNMENT MEALS	Nbr	17	No government meals.
PROPORTIONAL MEALS	Nbr	17	Proportional meals.
OFF BASE INC.	Nbr	17	Off base.
FOOTNOTE	Nbr	17	Footnote.
FOOTNOTE RATE	Nbr	17	Footnote rate.
MAX PER DIEM RATE	Nbr	17	Maximum per diem rate.
EFFECTIVE DATE	Char	10	Effective date.

Airline Ticket Data (AIRLINETKT.CSV) Format

When you import airline ticket data, the input file must be formatted as shown in this table:

Field Name	Type	Length	Comments
AIRFARE_RCPT_NBR	Char	16	Airfare receipt number.
EMPLID	Char	11	Employee ID.
AIR_TKT_RECON_STAT	Char	1	Airline ticket reconciliation.
MERCHANT	Char	40	Merchant.
TRAN_DATE	Char	10	Transaction date.
MONETARY_AMOUNT	Sign	28	Monetary amount.
CURRENCY_CD	Char	3	Currency code.
CRDMEM_ACCT_NBR	Char	20	Cardmember account number.
AUDIT_ACTN	Char	1	Audit action