[ BEN Financials ]
503.GA: Segment Values Extract
Documentation :|: BEN Balances Report Documentation :|: Quick View
* Return to Quick View List
The 503.GA extract provides the capability to create a file
containing values and descriptions for one or more of the chart
of accounts (COA) segments. Five out of the seven segments are
available: ORGanization, FUND, Object, PROGram and CREF (center
reference). All active values, with the exception of Parents, are
available for selection. Segment values that are expired or
disabled will not be extracted.
Parameters offer flexibility by giving options to specify the
segment(s) to extract, the range of values in the segment(s), how
the file should be sorted within each segment(s), the file format
to be created, and the name of the file where the extract data
will be placed (i.e., filename). The parameters are described in
more detail below.
One, more than one, or all of the available segments may be
extracted. The result of the request is
one file
containing the active segment(s) values and value descriptions.
The data in the file will be sorted numerically or alphabetically
based on the option supplied in the sort parameter. If more than
one segment is selected, the data will be sorted within each
respective segment.
The extract uses a standard ASCII, comma delimited file format
with alpha characters surrounded in quotes. The quotes and comma
are the defaults; however, the user has the option to change this
in the parameter provided.
The extract is submitted as a report set from the "Run
Reports" screen in the BEN Financials General Ledger. Instead of
supplying an "R" for report in the "Type"
column, select "S" for set. In the "Name"
column, use the quick pick option to display the list of
available extracts. Select 503.GA Segment Values Extract. Once
the request successfully completes, the file will be available to
download and import into a personal computer spreadsheet, word
processing, or database tool. The filename will be the name
provided in the parameter at the time the extract was submitted.
Sort Order:
The extract can be sorted Alphabetically, using the value
description or Numerically, using the segment value. If multiple
segments are extracted, the descriptions/values are sorted within
each segment. The default sort order is Alphabetic.
Sort order is a parameter option and is described in detail
below.
Parameters:
The extract takes the following parameters:
- Sort Order
Valid values are ‘A’ (Alphabetic) or
‘N’ (Numeric). The default value is
‘A’.
A = The extract will use the value
description to
sort the data alphabetically.
N = The extract will use the segment(s)
value to
sort the data numerically.
If multiple segments are selected, the extract will sort
the data within each individual segment.
- Delimiter
A character used to separate the fields of information in
the extract. Any valid printable character is allowed.
The default character is a comma (‘,’).
- Surround Data With Quotes?
Valid values are ‘Y’ (yes) or ‘N’
(no). The default value is ‘Y’. Use this
parameter to indicate how the fields in the extract file
should be formatted. Select ‘N’ if none of the
fields should be surrounded in quotes. Use the default
value ‘Y’ to surround all fields with quotes.
- Filename
The name of the file where the extract data will be
placed. This name can be up to 30 characters and is case
sensitive.
- Select All Segments?
Valid values are ‘Y’ (yes) or ‘N’
(no). The default value is ‘N’.
This parameter tells the program to extract all available
segments. Only segment values that are active, not
expired, or disabled will be extracted. It is used in
combination with the individual segment Yes/No parameters
which follow it on the screen.
Y = Setting this parameter to
‘Y’ will
cause all available segments to be extracted: ORGanization,
FUND, Object, PROGram and CREF (center reference). Ignore
the remaining individual segment Yes/No parameters. This
choice will override those parameters.
To specify a range of values to be extracted on one or
more of the segments, enter the range in the parameters
provided. Leave the ranges blank if you intend to extract
all active values. The range parameters are described
below.
N = Use this option when selecting one or more segments,
but not all, for the extract.
- ORG Segment? (Yes/No)
Valid values are ‘Y’ (yes) or ‘N’
(no). The default value is ‘N’. Use this
parameter to select the ORGanization segment.
- ORG LOW/HIGH
Use these parameters to limit the range of values to be
extracted. Leave them blank if all ORG values are
desired. Only active segment values will be extracted.
- FUND Segment? (Yes/No)
Valid values are ‘Y’ (yes) or ‘N’
(no). The default value is ‘N’. Use this
parameter to select the FUND segment.
- FUND LOW/HIGH
Use these parameters to limit the range of values to be
extracted. Leave them blank if all FUND values are
desired. Only active segment values will be extracted.
- Object Segment? (Yes/No)
Valid values are ‘Y’ (yes) or ‘N’
(no). The default value is ‘N’. Use this
parameter to select the Object segment.
- Object LOW/HIGH
Use these parameters to limit the range of values to be
extracted. Leave them blank if all Object values are
desired. Only active segment values will be extracted.
- Program Segment? (Yes/No)
Valid values are ‘Y’ (yes) or ‘N’
(no). The default value is ‘N’. Use this
parameter to select the PROGram segment.
- Program LOW/HIGH
Use these parameters to limit the range of values to be
extracted. Leave them blank if all PROGram values are
desired. Only active segment values will be extracted.
- For CREF - CREF Segment (Yes/No)
Valid values are ‘Y’ (yes) or ‘N’
(no). The default value is ‘N’. Use this
parameter to select CREF’s for a specific CNAC
segment.
- CNAC LOW/HIGH
This parameter is provided for the purpose of specifying
the CNAC values associated with the CREF LOW/HIGH
parameter.
CREF values are only meaningful when they are associated
with a School/Center. Each school/center has three CNAC
values, one for each net asset class (NAC). Although a
CREF value has a different meaning across school codes,
it carries the same meaning across all three NAC values
within a specific school code.
CNAC CNAC Description CREF Meaning
020 02 = SAS, 0 = Unrestricted 0001 - SAS Smith, Dr.
021 02 = SAS, 1 = Temporarily Restricted 0001 - SAS
Smith, Dr.
022 02 = SAS, 2 = Permanently Restricted 0001 - SAS
Smith, Dr.
400 02 = MED, 0 = Unrestricted 0001 - MED Jones, Dr.
401 02 = MED, 1 = Temporarily Restricted 0001 - MED
Jones, Dr.
402 02 = MED, 2 = Permanently Restricted 0001 - MED
Jones, Dr.
To extract CREF values for your school/center, simply
enter the same value in CNAC Low/High: CNAC Low: 020,
CNAC High: 020. Remember that CREF’s are the same
across all three NAC values. Entering a CNAC range for
your school/center, CNAC Low: 020, CNAC High: 022, will
result in a duplication of the same CREF values for 020,
021, and 022. This is also true when entering a CNAC
range that will cross more than one school/center.
Do not leave the CNAC Low/High range blank. The result
will be an extract of CREF values for all schools/centers
across all NAC’s.
- CREF LOW/HIGH
Use these parameters to indicate which CREF values should
be extracted. Only the CREF’s associated with the
range of CNAC’s provided in the CNAC parameter will
be selected. Only active segment values will be
extracted.
File Format:
The extract uses a standard ASCII file format. Values are
surrounded by quotes (") and fields are separated
(delimited) by a comma ( , ). This format is recognized by most
personal computer software packages (e.g., Spreadsheet, Word
Processing, Database). The quotes and comma are the defaults and
can be changed as indicated in the parameters section of this
document.
Whether one, more than one, or all segments are selected for
the extract,
one file will be created using the file name
provided as a parameter at the time the request is made.
Record Layout:
The records in the file are variable length and follow one of
three formats:
1. ORG, PROG, Object Record Layout:
Each record will begin with a segment identifier,
followed by the segment value, and the value description.
Example 1: One Segment Selected.
Create an extract file called "Example1.txt" .
Select the ORG segment only for the extract. Sort the
data numerically. The parameters are as follows:
- Sort = N
- Delimiter = , (the default)
- Surround Data With Quotes? = ‘Y’
- Filename = Example1.txt
- Select All Segments = ‘N’
- ORG Segment (Yes/No) = ‘Y’
- The remaining parameters are left blank.
Example1.txt contains records that look like the
following. Note: A few records have been selected to
demonstrate the concept. The real file will contain all
ORG’s consistent with the parameters shown in the example.
"ORG","0001","GEN
University"
"ORG","0011","NYC Penn
Club"
"ORG","0012","UNIV City
ASSOC"
2. FUND Record Layout:
In the case of the FUND segment, three additional
fields containing the Donor Restriction Codes are
included on the end of the record.
Donor restriction codes are associated with Endowment
(funds beginning with ‘4’) and Gift (funds
beginning with ‘6’) funds only. Fund extract
records will contain these codes for endowment and gift
funds which have restriction codes entered into the
BEN Financials system. For funds that do not have donor
restriction codes, three sets of double quotes ( ""
), separated by a comma, will appear on the end of the
record.
Each record will begin with a segment identifier,
followed by the segment value, the value description,
donor restriction codes ORG, PROG, and Object in this
order.
Example 2: One Segment Selected.
Create an extract file called "Example2.txt".
Select the FUND segment only for the extract. Sort the
data numerically. The parameters are as follows:
- Sort = N
- Delimiter = , (the default)
- Surround Data With Quotes? = ‘Y’
- Filename = Example2.txt
- Select All Segments = ‘N’
- FUND Segment (Yes/No) = ‘Y’
- The remaining parameters are left blank.
Example2.txt contains records that look like the
following. Note: A few records have been selected to
demonstrate the concept. The real file will contain all
Funds consistent with the parameters shown in the example.
"FUND","520000","SP
216793","","",""
"FUND","520002","Smith Kline
BEEC","","",""
"FUND","520003","5-K04-NS01537-05","","",""
"FUND","600000","AAA FDTN -
Dinges","4431","6603","TEXP"
"FUND","600001","Books For
Penn","5033","5710","TEXP"
"FUND","600002","ARRIS Pharmaceuticals","4245","2433","TEXP"
3. CREF Layout:
A CREF value is dependent on a CNAC to make it
meaningful. A CREF value of ‘0001’ will have
one meaning in the School of Arts and Sciences (CNAC 020)
and another meaning in the School of Medicine (CNAC 400).
To provide useful data for CREF, it must be used in combination
with a specific CNAC. The parameters, therefore, request the
input of CNAC when the CREF segment is being extracted
and the extract file contains the CNAC value in the
record.
Each record will begin with a segment identifier,
followed by CNAC value, the CREF segment value, and the
CREF value description in that order.
Example 3: One Segment Selected.
Create an extract file called "Example3.txt".
Select all CREFs for CNAC 020. Sort the data numerically.
The parameters are as follows:
- Sort = N
- Delimiter = , (the default)
- Surround Data With Quotes? = ‘Y’
- Filename = Example3.txt
- Select All Segments = ‘N’
- For CREF - CREF Segment (Yes/No) = ‘Y’
- CNAC LOW/HIGH = 020/020
- The remaining parameters are left blank.
Example3.txt contains records that look like the
following. Note: A few records have been selected to
demonstrate the concept. The real file will contain all
ORG’s consistent with the parameters shown in the example.
"CREF","020","0000","
Undefined"
"CREF","020","0001","
Barnes,Sandra Theis"
"CREF","020","0002","
Johnston,Francis E"
4. Multi-Segment Example:
This final example is included to demonstrate what the
extract file will look like when more than one segment is
selected for the extract. This example combines all three
examples presented above. Again, a few of the records are
being used to demonstrate the concept. The real file will contain
all of the selected segments and values consistent with
the parameters shown.
Example 4: Multiple Segments Selected.
Create an extract file called "Example4.txt".
Select the ORG, FUND, and CREF segments. For CREF, select
only those that pertain to CNAC 020. Sort the data
numerically. The parameters are as follows:
- Sort = N
- Delimiter = , (the default)
- Surround Data With Quotes? = ‘Y’
- Filename = Example4.txt
- Select All Segments = ‘N’
- ORG Segment (Yes/No) = ‘Y’
- FUND Segment (Yes/No) = ‘Y’
- For CREF - CREF Segment (Yes/No) = ‘Y’
- CNAC LOW/HIGH = 020/020
- The remaining parameters are left blank.
The extract file called "Example4.txt" will
contain the following records:
"ORG","0001","GEN
University"
"ORG","0011","NYC Penn
Club"
"ORG","0012","UNIV City
ASSOC"
"FUND","520000","SP
216793","","",""
"FUND","520002","Smith Kline
BEEC","","",""
"FUND","520003","5-K04-NS01537-05","","",""
"FUND","600000","AAA FDTN
-Dinges","4431","6603","TEXP"
"FUND","600001","Books For
Penn","5033","5710","TEXP"
"FUND","600002","ARRIS Pharmaceuticals","4245","2433","TEXP"
"CREF","020","0000","
Undefined"
"CREF","020","0001","
Barnes,Sandra Theis"
"CREF","020","0002","
Johnston,Francis E"
File Transfer:
When the request completes successfully and the file is
created using the file name provided as a parameter, it will
automatically be placed in the user home directory on
findata.umis.upenn.edu. The user home directory is determined by
the user id used to logon to BEN Financials. A user home directory will look
like this:
/home/smith.
|_____ BEN Financials user Id.
|___________ Always stays the same.
To transfer the file to your personal computer, use your
personal computer file transfer software (e.g., FTP, Host
Presenter, Fetch). When entering the host name, this is the name
of the machine where your extract file sits, enter:
findata.umis.upenn.edu
You will be required to provide your user id and password.
When your file transfer software logs on, you will automatically
be placed in the right user home directory. The file transfer
software will take over and transfer the file from
findata.umis.upenn.edu to your personal computer. Once the file
is on your personal computer, it is now available to you to
import into your spreadsheet, word processing, or database tool.
|
 |
|
Comptroller Spotlights
BEN Offices
|
|
