EmpowHR: Section 17 - Excel to Component Interface

Awards

The EmpowHR Excel CI utility for Awards allows users to submit new award actions through the Excel CI utility to process a mass amount of awards simultaneously. When the records are submitted through the Excel CI utility, the data is converted into a readable format for EmpowHR and then saved onto a staging table. A batch job will run after the noon export and again after the nightly import to validate that the current Personnel Action Request (PAR) row does not have a transaction status of Ready, In Process, Sent, or Worklist Ready. EmpowHR will also validate that there is not a pending history correction package being processed for any employee included in the mass transaction. The records that pass this validation will be transmitted to PAR and updated to reflect an NFC Ready status. The records that do not pass the validation and cannot be transmitted to PAR due to the current existing PAR row or history correction package are available for the user to review by running a query. Once the cleanup has been done on the current existing PAR row, or the history correction package has applied, the user will need to reprocess the action using the Excel CI utility. The flowchart below shows how the Excel CI utility processes awards.

The EmpowHR Excel CI utility template for awards contains six worksheets.

• Coversheet - provides directions on using the Excel CI utility for awards.
• Connect Information - allows users to select the applicable environment, update the Nature of Action Code (NOAC) and generate the Data Input template.
• Template - used to manipulate the Data Input Worksheet.
• Data Input Worksheet - allows users to enter the applicable data to send to EmpowHR.
• Staging & Submission - provides users with a final view of the data, allows them to submit it, and then validates the results.
• Remarks Codes-Descriptions - provides users with a list of descriptions based on the remarks code, which enables users to copy and paste the descriptions in the appropriate fields.

This section contains the steps and tabs used to complete an award mass action transaction.

Note: Before beginning a worksheet, you must enable the macros by clicking Options at the top of each worksheet.

Connecting to the Award Mass Action Component Interface Utility

1. Access the CI utility on the EmpowHR Web site at www.empowhr.gov.
2. Select the Publications tab from the top menu.
3. Select EmpowHR Tools & Utilities.
4. Select EmpowHR AWD Mass Action CI.

Award Mass Action Coversheet

The Award Mass Action Coversheet tab provides users with an overview of the Award Mass Action workbook and a guide for processing award transactions.

1. Click the EmpowHR AWD Mass Action CI link to download the Award Mass Action workbook template. The Award Mass Action coversheet is displayed.

   

Award Mass Action Connect Information

The Connect Information tab provides users with required information to create a new template or submit data to the database.

Note: The only action permitted in Award Mass Action is Create since the data entered will be added to HR Processing/Personnel Action Request (PAR) Processing.

1. Select the Connect Information tab. The Award Mass Action worksheet (Connect Information tab) is displayed.

   

   Note: You must click Options to enable macros before you can generate data input.

2. Complete the fields as follows:

   Field	Description/Instruction
   Environment	Select the applicable environment. The valid values are as follows: AG Production Copy - USDA United States Department of Agriculture environment that mirror images production. This environment does not modify the production database. AG Production - USDA environment where processing of data takes place to add, modify, or delete information in EmpowHR. DHS Production Copy - DHS environment that mirror images production. This environment does not modify the production database. DHS Production - DHS environment where processing of data takes place to add, modify, or delete information in EmpowHR. USDA UAT - UAT user acceptance testing environment used to test modifications before moving the modification to the production environment. DHS UAT - environment used to test modifications before moving the modification to the production environment. HRUSDAQA - HR USDA United States Department of Agriculture QA environment used to test functionality. HRDHSQA - DHS QA environment used to test functionality. HRTEST9 - HR test environment. DHS Test - environment used by DHS to test functionality. Training - environment used to train EmpowHR users. Development - environment used to develop EmpowHR functionality.
   Web Server Machine Name	Field populated with the EmpowHR Web server. The server name will be different depending on the environment.
   Protocol	Access used for the Web server. The field is populated and cannot be changed.
   HTTP Port	The number used by the Web server. The field is populated and cannot be changed.
   Portal	The portal being used. The field is populated and cannot be changed
   PeopleSoft Site Name	The environment used in the component interface. This field is based on the Environment and Template type selected. The field is populated and cannot be changed.
   Node	Field defaults to the local node name. The field is populated and cannot be changed.
   Language Code	Code populated with English and cannot be changed.
   Chunking Factor	The number of rows of data submitted to the database at one time (1 collection of 10). The field can be changed.
   Error Threshold	The total number of errors that are permitted before the submission to the database ceases. When the error threshold is exceeded, an error message appears on the Staging & Submission Results tab.
   Action	Option defaults to Create. When processing awards, Create is the only option available.
   NOA	Enter the applicable NOAC Nature of Action Code for the award being processed (i.e., 840 for Individual Cash Award RB).
   Number of Remarks?	Enter the number of remarks, if applicable.
   Tangible/Intangible Benefit	Select the applicable radio button to designate whether or not there is a tangible or intangible benefit associated with the award. Valid values are Tangible Benefit, Intangible Benefit, and Not Applicable.
   Use Stored Accounting	Select the applicable radio button to designate whether or not to use stored accounting when paying out this award. Valid values are Yes and No.
   Check Mail Address	Select the applicable address for the payment to be made. Valid values are POI, Check Mail Address, and Specified Address.
   EmplID or SSN?	Select the applicable radio button to designate whether to use the employee's EmplID or Social Security number (SSN). Valid values are EmplID and SSN.

3. Click Generate Data Input to create the Data Input worksheet with all available entry fields. The AWD Mass Action worksheet (Data Input tab) is displayed.

   OR

   Click Reset to reset the Connect Information worksheet and clear all selected cells on the Template worksheet.

   

Award Mass Action Template

The Award Mass Action Template tab provides advanced users with the ability to create and modify the structure for the data that will be sent to EmpowHR. Advanced users can select additional fields used for transmission, omit fields, or enter default information that is applicable to all records. The template is automatically populated when the Generate Data Input button on the Connection Information worksheet is selected. The template is used to build the structure for the data that will be sent to EmpowHR. In this workbook, you will select fields used for transmission, omit fields, and enter any default or common values.

Note: Most users will be able to bypass this tab and continue to the Data Input tab.

Some columns in the Template worksheet have specific formatting or certain translation values. These fields will be identified with a triangle in the top right corner of the cell. To view this information, place the cursor on the top of the red triangle.

The data on the top of the workbook contains database details of the fields on the EmpowHR page.

The header row displays the field label visible in the EmpowHR component.

The Record Type fields represent the child and parent record levels within EmpowHR.

Note: It is not necessary to understand the hierarchical structure as the worksheet is structured so that the user may only select fields in the correct structure; all other fields are unavailable (locked).

The fields that are open at the Record Type 000 are key search fields. These fields are required when creating a new record. When updating a record, it is important to complete some of these fields to ensure that the correct record is being updated.

Use the Required Fields worksheet to ensure that all required fields (for the applicable action type) are selected on the Template worksheet.

1. Click the Template tab. The Award Mass Action worksheet (Template tab) is displayed.

   

2. Click Add-Ins from the top menu bar to display the custom toolbar for the Template worksheet. Below is a description of each option.

   Tool Bar Options	Description
   New Data Input	Builds a new Data Input worksheet based upon the selected input cells. This option is used, once the template is built, to begin data entry. This will erase any structure or data previously entered on the Data Input tab; therefore, it is recommended that the template is completed before entering data.
   Select Input Cell	Selects an individual cell to be updated on the Template worksheet. This is used for a list of values that are different from one row to the next (e.g., job code, position). Cells selected as input cells are highlighted. All cells highlighted on the template worksheet will appear on the Data Input worksheet. This option will make the highlighted cell available for data entry on the Data Input tab.
   Deselect Input Cell	Changes a cell that was previously selected as an input cell to a cell that is included on the Staging & Submission tab. The cell is no longer included on the Data Input worksheet, but appears as part of the structure on the Staging & Submission worksheet. This option will remove a previously selected cell from the Data Input tab.
   Restore Input Cells	Restores the Templates worksheet to its original state and clears default values. The fields in the template will be shaded, indicating that these fields will not be included for the Staging & Submission tab. This option restores the Template worksheet to the default values. It is recommended that this is selected to refresh the template before creating a new template.
   Include for Submission	Includes a single Property to be included on the Staging & Submission worksheet. Properties that use default values from the Templates worksheet must be included for submission. This is used when the value is not changing between rows on the spreadsheet (i.e., set ID or effective date). Cells that are included for submission generally are properties that contain default values or properties that could be seen in the structure of the Staging & Submission worksheet. Properties that are included for submission are highlighted. This option selects the highlighted cell to display on the Staging & Submission worksheet. This option does not select these cells for data input on the Data Input worksheet. This is generally used when the user wants certain default values to apply to all data input records (e.g., Agency, SubAgency, POI, etc.). If you are adding a default value: Select the cell Click Include for Submission Enter the default value in the cell
   Do Not Include for Submission	Does not include the selected property for submission to the database. If a property is not included for submission, it will not appear in the structure that is submitted to the database on the Staging & Submission worksheet. Properties that are not included for submission will only appear on the Template worksheet and not submitted to the database. Properties that are not included for submission are shaded. This option removes a previously selected cell from the Staging & Submission worksheet. This option also removes the cell from the Data Input worksheet.

   Note: Each option under the Add-Ins menu has help text that describes the purpose and use of each option when the cursor is placed over the option.

3. Modify the template structure for the data that will be sent to EmpowHR. Additional fields may be added and used for transmission, fields may be omitted, and default and common values may be entered.
4. After all the modifications are made to the template, click New Data Input to build a new Data Input worksheet that includes all the applicable modifications.
5. Click Yes on the confirmation popup if you want all existing data on the Data Input worksheet to be deleted.

Award Mass Action Data Input

The Data Input worksheet is automatically updated to include all relevant data entry fields for the type of award and options selected when the Generate Data Input button is selected. The Data Input tab provides users with the ability to enter data values for submission to EmpowHR. The user may use a stored query to populate data in these fields.

The Data Input sheet is also used to correct data to submit to the database. Errors that are flagged on the Staging & Submission Results worksheet are posted to the Data Input worksheet. When corrected, the items marked in error can be staged again on the Staging & Submission worksheet.

Some columns in the Data Input worksheet have specific formatting or certain translate values. These fields will be indicated with a red triangle in the top right corner of the cell. To view this information, place the cursor on the top of the red triangle.

The same record-type structure that is on the Template worksheet is displayed on the Data Input worksheet.

The fields listed as a Record Type 000 are key search fields. For updates, these fields are used to locate the record to be updated.

Note: The Effective Date must be equal to or greater than the current date.

1. Click the Data Input tab. The Award Mass Action worksheet (Data Input tab) is displayed.

   

2. Enter the data to be submitted to EmpowHR.
3. Click Add-Ins from the top menu bar to display the custom toolbar for the Data Input worksheet. For definitions on the Add-Ins options, see Award Mass Action Template.
4. Select Stage Data for Submission to apply the data to the Staging & Submission worksheet.

Award Mass Action Staging and Submission

This tab is where the Excel CI workbook reformats the data entered on the Data Input worksheets so that EmpowHR can update the record. The Staging & Submission worksheet displays all of the fields and data from the Data Input worksheet (and those selected for Submission Only). These fields have the same Record Type structure as the fields on the Template and Data Input worksheets. You must verify that the populated data fields are the same as the Data Input worksheets.

Note: There is a blank Effective Date column for the 000 Record Type on the Staging & Submission worksheet. This default is necessary for transmission.

1. Select the Staging & Submission tab. The Award Mass Action worksheet (Staging & Submission tab) is displayed.

   

2. Verify the data populated in the fields is the same as the data in the Data Input worksheet. Any changes must be made on either the Template worksheet or the Data Input worksheet.
3. Click Add-Ins from the menu at the top of the worksheet to display the custom toolbar for the worksheet. For definitions on the Add-Ins options, see Award Mass Action Template.
4. Click Submit Data to submit the award mass action. The Login popup appears.

   

5. Complete the fields as follows:

   Field	Description/Instruction
   User ID	Enter the applicable EmpowHR user ID.
   Password	Enter the applicable EmpowHR password.
   Component Interface Name	Populated based on the CI utility selected from the NFC Home Page.
   Generate Log	Box that can be changed. This checked box is used to create one log file for ExcelToCI.xls and for the SOAPTOCIWeb Library. These files are used for toubleshooting.

6. Click OK to submit the data to the EmpowHR database. Although a mass amount of awards is being submitted, each action is processed individually. Once this process is completed, the Status column will be updated. If no errors occur during the process, a box with OK will be displayed in the Status column next to the applicable action. If errors occur, a red box with Error will be displayed in the Status column next to the applicable action. Move the mouse over the red box to reveal a description of the error(s) that may have been encountered during the submission. A Warning may display during submission. The warning information can be viewed online. This is a rare occurrence.
7. Click Post Results to transmit the results of the submission to the Data Input worksheet, where users can view the status of each transaction and make any necessary corrections to rows that reflect an Error status.

   Note: Always review your data before submission to EmpowHR. Any updates to be made should be made on either the Template worksheet or the Data Input worksheet.

   Once this process is complete, the Status column of the Staging & Submission worksheet will be updated.

To validate results:

Once results have been posted to the Excel CI utility, you can view the status for each transaction. After the batch process runs, you can validate the results. For any rows that have been successfully updated, you can navigate to the record in EmpowHR and validate the changes. Results reflecting an OK status can be verified in EmpowHR by navigating to the employee’s record via the applicable Job Code or Position Information page. When the row is added, the record will reflect an NFC Ready status.

Results reflecting an Error status can be viewed in EmpowHR by navigating to Query Manager and running a Z_MASSLOAD_ERR_AWD query. The query will contain each data record that could not be applied and its error message(s). These records need to be corrected and resubmitted in the EmpowHR Excel CI utility.

Award Mass Action Remark Codes - Description

The Remark Codes-Description tab allows users to view the description for each Remark Code, and it helps users identify whether or not they should insert data for a particular Remark Code. The Remark Codes - Description tab also enables users to copy and paste its text to an applicable line on the Data Input worksheet.

1. Select the Remark Codes - Description tab. The Award Mass Action worksheet (Remark Codes - Description tab) is displayed.