PeopleSoft Enterprise Global Options and Reports 9.1 PeopleBook

Creating Regulatory Ledger Reports

This chapter provides an overview of regulatory ledger reporting and discusses how to:

Set up regulatory ledger report data.
Create a regulatory ledger file and reports.

Understanding Regulatory Ledger Reporting

Regulatory Ledger Reports restate ledger balances that are stored in the PS_LEDGER table based on external regulatory requirements. This means that you can restate ledger balances based on PeopleSoft ChartFields (such as Account and Department ID) as ledger balances based on external ChartFields that a regulatory body (such as a government organization) mandates for reporting purposes. You can use the Regulatory Ledger Reporting feature to:

Generate ledger file extracts that are specifically designed for the Ontario Provincial Ministry of Health in Canada.
Build a regulatory ledger.
Review the translated data.
Review the translation errors.

To accomplish this, you:

Define the regulatory ledger and associated external ChartFields.
Set up ChartField attributes and attach them to PeopleSoft ChartField values.
Set up a regulatory ledger ChartField mapping structure and define rules that translate PeopleSoft ChartField values into external ChartField values.

Note. For organizations that are required to report to other regulatory bodies, you could use the ledger file extract that is provided specifically for the Ontario Provincial Ministry of Health in Canada as the basis of creating new customized ledger file extracts.

Prerequisites

For regulatory reporting, you must set up the:

SetID for the target PeopleSoft ChartField values that is used in ChartField value mapping.
Account types for any new setIDs.

Setting Up Regulatory Ledger Report Data

To set up regulatory ledger report data, use the following components:

Definition (REG_LEDGER_DEFN)
Mapping Structure (REG_LEDGER_MAP)
Mapping Rules (REG_LEDGER_RULE)
Submission Information (REG_LEDGER_CONTACT)

This section discusses how to:

Set up the regulatory ledger and external ChartFields.
Create a ChartField attribute for each set of accounts.
Attach ChartField attribute values to PeopleSoft ChartField values.
Define a value set name.
Define the regulatory ledger mapping structure.
Define the regulatory ledger mapping rules.
Define submission information.

See Also

Using ChartField Value Sets

Pages Used to Set Up Regulatory Ledger Report Data

Page Name	Definition Name	Navigation	Usage
Ledger ChartFields	REG_LEDGER_DEFN	General Ledger, Regulatory Ledger Reports, Definition	Set up the regulatory ledger and define the external ChartFields into which the PeopleSoft ChartFields must be translated.
ChartField Attributes	CF_ATTRIBUTES	Set up Financials/Supply Chain, Common Definitions, Design ChartFields, Configure, Attributes	Define the attribute values for the ACCOUNT ChartField.
Account - ChartField Attributes	CF_ATTRIB_VALUES	Set up Financials/Supply Chain, Common Definitions, Design ChartFields, Define Values, ChartField Values, Account and click the Attributes link.	Attache the ChartField attribute value to the PeopleSoft ChartField values.
ChartField Value Mapping	CF_VALUE_MAPPING	General Ledger, Consolidate Financial Data, Load Ledgers, ChartField Value Mapping, ChartField Value Mapping	Define a ChartField value mapping set that is associated with a PeopleSoft ChartField for mapping multiple setIDs and values to a target setID and value.
Reg Ledger Mapping Structure (regulatory ledger mapping structure)	REG_LEDGER_MAP	General Ledger, Regulatory Ledger Reports, Mapping Structure	Map the external ChartFields to the PeopleSoft ChartFields. Define the positional concatenation rules.
Reg Ledger Mapping Rules (regulatory ledger mapping rules)	REG_LEDGER_RULE	General Ledger, Regulatory Ledger Reports, Mapping Rules	Define the translation of a combination of PeopleSoft ChartField values into external ChartField values.
Regulatory Ledger Contact	REG_LEDGER_CONTACT	General Ledger, Regulatory Ledger Reports, Submission Information	Identify the regulatory body that receives the regulatory ledger report and indicates that the submission information to be specified is based on the requirements of the Ontario Ministry of Health.

Setting Up the Regulatory Ledger and External ChartFields

Access the Ledger ChartFields page (General Ledger, Regulatory Ledger Reports, Definition).

Select the setID and enter a regulatory ledger ID and description.

Field Nbr (field number)

This number is sequenced automatically. You can set up only 10 external ChartFields for each regulatory ledger. You set up these fields in the REG_LEDGER_DATA table; they are identified as CF_NAME1 through CF_NAME10. If you run SQL against this table using a SQL query tool, note that the column storing data for a particular external ChartField is CF_NAMEx, where x represents the field number value (Field Nbr) that appears on this page.

External ChartField

You can enter up to 10 external ChartFields for the regulatory ledger.

Creating a ChartField Attribute for Each Set of Accounts

Access the ChartField Attribute page (Set up Financials/Supply Chain, Common Definitions, Design ChartFields, Configure, Attributes).

At least one ChartField attribute is required for each account setID involved in the translation of account ChartField values.

Select the Account ChartField setID and enter ACCOUNT for the field name. Enter the ChartField Attribute name. This should be the regulatory ledger ID.

Allow Multiple Values per Attr (allow multiple values per attribute)

Select the check box if you want to include more than one attribute value per ChartField attribute.

ChartField Attribute Value

Define the ChartField attribute values to use for mapping purposes on the Reg Ledger Mapping Structure page, where you create mapping structure definitions. This page example includes:

BAL (Balance Sheet)
PL (Profit and Loss)
ST (Statistics)

These three types of accounts are translated differently into external ChartField values, necessitating separate mapping structure definitions for a particular ledger. When the regulatory ledger process builds the regulatory ledger and ChartField value mapping is not specified for the Account ChartField, the ChartField attribute value is selected from the ChartField Attribute table, where the ChartField is Account, the ChartField attribute is the regulatory ledger ID, the ChartField value is the account value of each row in PS_LEDGER, and the setID is the setID for the record group containing the Account ChartField for the business unit value of each row in PS_LEDGER. The process of selecting the ChartField attribute value, when ChartField value mapping is specified for the Account ChartField, is described subsequently in the ChartField Value Mapping subsection dealing with creating a ChartField Attribute for each set of accounts.

Attaching ChartField Attribute Values to PeopleSoft ChartField Values

Access the Account - ChartField Attributes page (Set up Financials/Supply Chain, Common Definitions, Design ChartFields, Define Values, ChartField Values, Account and click the Attributes link.)

Select the ChartField attribute and ChartField attribute value to attach to the PeopleSoft Account ChartField Value (411111 in the example).

See Also

Adding Account Values

Defining a Value Set Name

Access the ChartField Value Mapping page (General Ledger, Consolidate Financial Data, Load Ledgers, ChartField Value Mapping, ChartField Value Mapping).

This page is optional. If you select a value set name on the Reg Ledger Mapping Structure page for the ACCOUNT ChartField, then you must define the same value set name for each occurrence of the ACCOUNT ChartField within all mapping IDs for the specified ledger. This consistency is not required for other PeopleSoft ChartFields. The regulatory ledger build process uses the value set name on the Reg Ledger Mapping Structure page, if it is not blank, to translate the PeopleSoft ChartField value as it exists in the PS_LEDGER table to the PeopleSoft Target ChartField value on this page (ChartField Value Mapping). Next, it selects the ChartField attribute value from the ChartField Attribute table where the ChartField is Account, the ChartField attribute is the regulatory ledger ID, the ChartField value is the target account value from the ChartField value mapping that is associated with the Account value of each row in PS_LEDGER, and the setID is the target setID from the ChartField value mapping. This is a method of converting a PeopleSoft ChartField value in PS_LEDGER to an interim ChartField value, which is then subject to the concatenation rules that are defined in the mapping structure to form the external ChartField value.

Enter a value set name. Specify the mapping ChartField and target setID. The mapping ChartField is the internal ChartField that needs to be translated from the values in PS_LEDGER to an interim ChartField value. This translation maps the source setID and range from and to values to the target setID and target values. In the page example, all accounts from 100000 through 119999 for the setID SHARE are translated on an interim basis to account 11111 for SetID MIS. Then the concatenation rules defined in the mapping structure use the interim translated value of 11111 for account when building the external ChartField values.

In the Mapped Business Units group box, we recommend that the Business Unit and SetID fields be left blank.

In the Mapped Values group box, specify values in the ChartField Value, SetID From, Range From and Range To fields. The ChartField value mapping setup is part of a core process within the PeopleSoft General Ledger system to assist in the process of consolidating across dissimilar charts of accounts. We use the setup part in Regulatory Ledger Reporting as an optional first step in the translation process. This translation takes an internal ChartField value, for example 102030, and maps it to 12345. The second step uses the mapping structure, for example the first four characters of the interim translated Account value 12345, to arrive at an external ChartField value of 1234. The ChartField value mapping is a first step to getting potentially hugely dissimilar accounts into a set of numbers that can be concatenated together with other ChartField values to make up the external ChartField value.

Note. Be careful! The ChartField Value Sets pages under the Design ChartFields menu do not have anything to do with the value set name described here. Be sure to navigate to General Ledger, Consolidate Financial Data, Load Ledgers, ChartField Value Mapping, ChartField Value Mapping to define the value set name that is used for regulatory reporting.

Defining the Regulatory Ledger Mapping Structure

Access the Reg Ledger Mapping Structure page (General Ledger, Regulatory Ledger Reports, Mapping Structure).

On this page you set up a regulatory ledger ChartField mapping structure that concatenates PeopleSoft ChartField values into external ChartField values. Concatenation rules enable you to construct external ChartField values by piecing together portions of PeopleSoft ChartField values.

Enter values in the SetID, Reg Ledger ID (the external regulatory ledger ID that you set up on the Ledger ChartFields page), and Ledger fields.

Mapping ID (mapping identification)	Enter a mapping ID for this regulatory ledger. Add an identical mapping ID for each ChartField attribute value that is defined on the ChartField Attributes page.
Description	Enter a description.
Effective Date	Enter the date that you want this information to become effective.
Status	The system displays the default, Active, or you can select Inactive.
Sequence	This field is automatically generated and determines the order in which fields are processed.
Ext ChartField (external ChartField)	Select one of the External ChartFields that you set up for this regulatory ledger. The values for these ChartFields are ultimately constructed using portions of the PeopleSoft ChartField values.
ChartField	Select a PeopleSoft ChartField to map to the specified external ChartField.
Value Set Name	Select the applicable value set name that you defined on the ChartField Value Mapping page, but only if you need to translate the PeopleSoft ChartField value in PS_LEDGER to an interim value that can be concatenated to form the external ChartField value. If you can concatenate the PeopleSoft ChartField value as it exists in PS_LEDGER into the external ChartField value, then leave the Value Set Name field blank.
Start Position	Enter the starting point within the PeopleSoft ChartFields for this external ChartField.
Length	Enter the length of the portion of the PeopleSoft ChartFields to use for the external ChartFields. If the specified length is greater than the significant characters of a particular ChartField value, the translation inserts trailing blanks to meet the desired length.
Required Flag	Select to indicate that this portion of the external ChartField is required in the REG_LEDGER_DATA table when this mapping ID applies. If a required portion of an external ChartField value ends up with blanks, it is considered an error in translation. If a nonrequired portion of an external ChartField value ends up with all blanks, it is filled with blanks during translation.

See Also

Mapping Dissimilar Charts of Accounts

Defining the Regulatory Ledger Mapping Rules

Access the Reg Ledger Mapping Rules page (General Ledger, Regulatory Ledger Reports, Mapping Rules).

Note. On this page you define the regulatory ledger ChartField mapping rules that translate PeopleSoft ChartField values in the PS_LEDGER table directly into external ChartField values. This enables you to bypass the optional ChartField value mapping and the concatenation approach in the mapping structure. Translation rules enable you to restate specific PeopleSoft ChartField combinations as a combination of external ChartFields. You can express these PeopleSoft ChartField combinations as any combination of detail values, wildcard values, or tree nodes.

Rule and Rule Description

Enter a rule ID and rule description for the selected setID and regulatory ledger ID of the regulatory ledger definition.

ChartField Values

The PeopleSoft ChartFields and the external ChartFields that make up the regulatory ledger ID appear in the ChartField column along with the Int/Ext field value identifying whether the ChartField is internal (a PeopleSoft ChartField) or external. For internal ChartFields, you can associate either a detail value or tree name, level name, and tree node values. For external ChartFields, you can identify only a target detail value.

You can create mapping rules to address any and all exceptions to the mapping structure, where a specific combination or a set of combinations of PeopleSoft ChartField values is to be translated into a combination of external ChartField values that differ from the translation that would otherwise be performed using the optional ChartField value mapping and the required mapping structure. The combination of PeopleSoft ChartField values can consist of blanks, a valid value or a wildcard value (a partial value followed by %), or a tree name, level name, and tree node value. A blank value means that the ChartField in PS_LEDGER can contain any value. A wildcard value can be interpreted as starting with whatever you enter.

You can define and use trees specifically for external translation purposes to minimize the number of mapping rules that you set up. You must enter the full detail value for all external ChartFields that need a value (that is, wildcards are not allowed for external ChartFields). Each rule must contain a value for at least one external ChartField.

ChartField Value Translation Process

The ledger rows translate from PeopleSoft ChartField values into external ChartField values in the following order:

Apply rules with detail values only (no trees).
Apply rules with detail values and trees.
Apply mapping structure concatenation rules.

Defining Submission Information

Access the Regulatory Ledger Contact page (General Ledger, Regulatory Ledger Reports, Submission Information).

Reg Body ID (regulatory body identification)

Enter the regulatory organization that defines the regulatory values. The information on this sample page is specific to Canadian Healthcare MIS Guidelines requirements. PeopleSoft customers who have a need for other regulatory ledger reporting can use this page as the basis for creating a customized submission information page.

Ext Entity (external entity)

Enter the business entity, which submits information to meet the regulatory ledger requirements that are established by the regulatory body. This identifies the reporting business as assigned by the regulatory body that is to be included on the regulatory ledger file. If you have more than one business unit reporting to the regulatory body, and therefore more than one external entity ID, we recommend that you create multiple regulatory bodies, one for each entity ID. Alternatively, you can create multiple regulatory ledger definitions, mapping structures, or both, especially if the ledger value in PS_LEDGER or the mapping structure itself is different for each business unit.

Submission Seq (submission sequence)

Enter a number included on the regulatory ledger file that identifies in what sequence it has been sent to the regulatory body.

Submission Type

Select from three predefined Canadian Healthcare MIS Guidelines translate values, Year-End, December, or September, to include in the regulatory ledger file.

City and Facility Name

Enter the business city and facility name reporting to the regulatory body in the regulatory ledger file.

Submission Narrative Text

Enter comments that are to be included in the submission file.

Sequence Number

Enter a number that is included on the regulatory ledger file and used to sort the contact information sequentially in ascending order.

Contact Person Role

Select from the two predefined Canadian Healthcare MIS Guidelines translate values, Approval and Technical, for inclusion in the regulatory ledger file.

Contact Value Code

Select from the four predefined Canadian Healthcare MIS Guidelines translate values, Job Title, FAX number, Name, and Voice Number, for inclusion in the regulatory ledger file.

Contact Value

Enter this information for inclusion in the regulatory ledger file.

Note. The submission information is specific to Canadian healthcare customers. Customers not in Canadian healthcare, but with regulatory reporting requirements, may need to customize this page to capture all of the submission information that their regulatory bodies require.

Creating a Regulatory Ledger File and Reports

This section discusses how to:

Build the regulatory ledger.
Generate the regulatory ledger file.
Review a list of translation errors.
Review a list of translated data.
Generate the Data Error Report (FIN 5101 Crystal and FSX5101 XML Publisher).
Generate the ChartField value mapping report (FIN 5102).

See Also

General Ledger Reports

Pages Used to Create a Regulatory Ledger File and Reports

Page Name	Definition Name	Navigation	Usage
Build Reg Ledger (build regulatory ledger)	RUN_REGLEDBLD	General Ledger, Regulatory Ledger Reports, Build Regulatory Ledger	Launch the Regulatory Ledger Build process (FS_REGLEDBLD), which selects the relevant detail ledger data and translates it based on the mapping structure and mapping rules definitions.
Generate Reg Led File (generate regulatory ledger file)	RUN_REGLEDFILE	General Ledger, Regulatory Ledger Reports, Ledger File Extract	Create a flat file based on the specifications of the Ontario Ministry of Health, Ontario, Canada and post it to the report repository.
Translation Error	REG_LEDGER_DATAERR	General Ledger, Regulatory Ledger Reports, Review Ledger Errors	List the ledger rows that were not translated into external ChartFields. This helps identify the problem in order to refine the translation setup.
Data Inquiry	REG_LEDGER_DATAINQ	General Ledger, Regulatory Ledger Reports, Review Ledgers	List successfully translated rows.
Data Error Report	RUN_REGLEDERR	General Ledger, Regulatory Ledger Reports, Ledger Errors	Run a Crystal or XML Publisher report of the Regulatory Ledger Error Inquiry to aid in the correction of translation setup problems. The XML Publisher report does not require manual changes to support ChartField configuration and uses the same criteria as the Crystal reports.
Chartfield Reports	RUN_REGLEDCFRPT	General Ledger, Regulatory Ledger Reports, ChartField Value Mapping	Identifies potential problems with the ChartField value mapping definitions on these reports.

Building the Regulatory Ledger

Access the Build Reg Ledger page (General Ledger, Regulatory Ledger Reports, Build Regulatory Ledger).

SetID

Select the setID that you used to create the regulatory ledger definition.

Reg Ledger ID (regulatory ledger ID)

Select the ID that you have set up for this regulatory ledger.

Ext Entry Type (external entry type)

Select the type of data that you want to include in the regulatory ledger. Values are Financial, Statistical, and Financial and Statistical.

Regulatory Body ID

Select the ID that you set up on the Regulatory Ledger Contact page.

External Entity

Displays the organization identification number that will be included in the ledger file that is created and sent to the Canadian healthcare regulatory body.

Business Unit

Select the business unit for which you must report to the regulatory body.

Ledger

Select the ledger in PS_LEDGER from which you want to build the regulatory ledger. This value should be the same as the one that you used in the mapping structure. When the regulatory ledger process builds the regulatory ledger, the ChartField attribute value is selected from the ChartField Attribute table where the ChartField is Account, the ChartField attribute is the regulatory ledger ID, the ChartField value is the target account value from the ChartField value mapping that is associated with the account value of each row in PS_LEDGER, and the setID is the target setID from the ChartField value mapping.

As of Date

Enter an appropriate date to select the effective-dated rows from the various translation setup tables. The default is the current date.

Fiscal Year

Enter the fiscal year for which you want to build the regulatory ledger.

From Period and To Period

When you build the regulatory ledger, the translated rows include this range and not the specific accounting period from the detail ledger. For example, when you run the process with the parameters that are shown on the Build Reg Ledger sample page, the process includes all rows with an accounting period of 8 within the detail ledger. However, when the process translates and stores that row in the REG_LEDGER_DATA table, it does not provide a reference to accounting period 8, but only to the range of 1 through 12.

Enter values in the From Period and To Period fields for the fiscal year for this regulatory ledger.

Delete Previous Matching Build

Select to delete the results of a prior Build Reg Ledger process that used the same run control parameters.

Generating the Regulatory Ledger File

Access the Generate Reg Led File page (General Ledger, Regulatory Ledger Reports, Ledger File Extract).

The information that you enter on this page is similar to the information that you enter on the Build Reg Ledger page except for the Submission Year field, in which you enter the year as labeled by the regulatory body and included in the regulatory ledger file.

Note. This process is specific to the Canadian healthcare customers. Customers requiring other types of regulatory reporting could use this process as the basis for creating a customized regulatory ledger file generation process that produces the exact file specifications for the non-Canadian healthcare regulatory body.

Reviewing a List of Translation Errors

Access the Translation Error page (General Ledger, Regulatory Ledger Reports, Review Ledger Errors).

This page lists the internal ChartFields and amounts that did not translate amounts to the external ChartFields. The Trans Error Cd column displays the reason why a row could not be translated and indicates what must be changed in the translation setup data to translate the row successfully.

Reviewing a List of Translated Data

Access the Data Inquiry page (General Ledger, Regulatory Ledger Reports, Review Ledgers).

This is a dynamic page that expands to include all external ChartFields defined for the regulatory ledger. When you enter values in the From Period and To Period fields for the regulatory ledger build process, the translated rows include this range and not the specific accounting periods from the detail ledger.

Generating the Data Error Report (FIN 5101 Crystal and FSX5101 XML Publisher)

Access the Data Error Report page (General Ledger, Regulatory Ledger Reports, Ledger Errors).

Note. This page generates the Data Error report (FIN5101 Crystal and FSX5101 XML Publisher) that lists the internal ChartFields that were not translated.

Generating the ChartField Value Mapping Report (FIN 5102)

Access the Chartfield Reports page (General Ledger, Regulatory Ledger Reports, ChartField Value Mapping).

These reports are useful for finding errors in the ChartField value mapping that you used to map internal ChartField values to target ChartField values for your regulatory reports. The report lists:

ChartField values that are linked to more than one target ChartField value.
ChartField values that are not linked to a target ChartField value.

Enter one or more value set names to limit the information that is included in the report.

Note. This generates the FIN5102.SQR report, which enables you to identify problems for a specific ChartField value set or a range of ChartField value sets.

Reg Body ID (regulatory body identification)	Enter the regulatory organization that defines the regulatory values. The information on this sample page is specific to Canadian Healthcare MIS Guidelines requirements. PeopleSoft customers who have a need for other regulatory ledger reporting can use this page as the basis for creating a customized submission information page.
Ext Entity (external entity)	Enter the business entity, which submits information to meet the regulatory ledger requirements that are established by the regulatory body. This identifies the reporting business as assigned by the regulatory body that is to be included on the regulatory ledger file. If you have more than one business unit reporting to the regulatory body, and therefore more than one external entity ID, we recommend that you create multiple regulatory bodies, one for each entity ID. Alternatively, you can create multiple regulatory ledger definitions, mapping structures, or both, especially if the ledger value in PS_LEDGER or the mapping structure itself is different for each business unit.
Submission Seq (submission sequence)	Enter a number included on the regulatory ledger file that identifies in what sequence it has been sent to the regulatory body.
Submission Type	Select from three predefined Canadian Healthcare MIS Guidelines translate values, Year-End, December, or September, to include in the regulatory ledger file.
City and Facility Name	Enter the business city and facility name reporting to the regulatory body in the regulatory ledger file.
Submission Narrative Text	Enter comments that are to be included in the submission file.
Sequence Number	Enter a number that is included on the regulatory ledger file and used to sort the contact information sequentially in ascending order.
Contact Person Role	Select from the two predefined Canadian Healthcare MIS Guidelines translate values, Approval and Technical, for inclusion in the regulatory ledger file.
Contact Value Code	Select from the four predefined Canadian Healthcare MIS Guidelines translate values, Job Title, FAX number, Name, and Voice Number, for inclusion in the regulatory ledger file.
Contact Value	Enter this information for inclusion in the regulatory ledger file.

SetID	Select the setID that you used to create the regulatory ledger definition.
Reg Ledger ID (regulatory ledger ID)	Select the ID that you have set up for this regulatory ledger.
Ext Entry Type (external entry type)	Select the type of data that you want to include in the regulatory ledger. Values are Financial, Statistical, and Financial and Statistical.
Regulatory Body ID	Select the ID that you set up on the Regulatory Ledger Contact page.
External Entity	Displays the organization identification number that will be included in the ledger file that is created and sent to the Canadian healthcare regulatory body.
Business Unit	Select the business unit for which you must report to the regulatory body.
Ledger	Select the ledger in PS_LEDGER from which you want to build the regulatory ledger. This value should be the same as the one that you used in the mapping structure. When the regulatory ledger process builds the regulatory ledger, the ChartField attribute value is selected from the ChartField Attribute table where the ChartField is Account, the ChartField attribute is the regulatory ledger ID, the ChartField value is the target account value from the ChartField value mapping that is associated with the account value of each row in PS_LEDGER, and the setID is the target setID from the ChartField value mapping.
As of Date	Enter an appropriate date to select the effective-dated rows from the various translation setup tables. The default is the current date.
Fiscal Year	Enter the fiscal year for which you want to build the regulatory ledger.
From Period and To Period	When you build the regulatory ledger, the translated rows include this range and not the specific accounting period from the detail ledger. For example, when you run the process with the parameters that are shown on the Build Reg Ledger sample page, the process includes all rows with an accounting period of 8 within the detail ledger. However, when the process translates and stores that row in the REG_LEDGER_DATA table, it does not provide a reference to accounting period 8, but only to the range of 1 through 12. Enter values in the From Period and To Period fields for the fiscal year for this regulatory ledger.
Delete Previous Matching Build	Select to delete the results of a prior Build Reg Ledger process that used the same run control parameters.