Batch Element Entry (BEE) in Oracle HRMS

1Introduction

The document gives an overview of creating element entries for an employee with the help of the seeded program BEE (Batch Element Entry).

The document explains in detail the method to be followed, mandatory fields to be populated, the required set-ups to be done in the setup and the way the data should be processed populated into the batch tables.

Batch Element Entry is functionality available in the Oracle HRMS, which provides the fastest way of entering the batches of elements on a regular basis. E.g. if there are any changes to the salary or the commissions, these can be handled effectively with the help of the BEE functionality.

Each batch has a header and as many number of lines as required. A batch line is an element entry for an assignment.

The batch header needs to be entered manually or created with the help of an automated Pl/SQL program. There are various ways with which the batch lines could be populated for the available batch header. These are discussed in detail in the later sections of the document.

Note: The data file creation and the custom table population with the data is not in the scope of this document. A separate program would be needed to populate the custom tables with the data. However, a sample data file has been given in the program components section.

2 Batch Element Entry

2.1Approach

The BEE could be implemented with the help of following approaches:

1. Programmatically populating the header records and the line records in the BEE tables based on the data file available.

2. Entering input values and other data for one element, and running a concurrent process to create identical batch lines for all the assignments in an assignment set.

3. Creating an element set and making entries for each element in the set, one assignment at a time.

4. Selecting an element and creating or updating lines for this element using defaults to speed entry, and changing defaults as necessary when working through the batch.

The first approach of populating the header and lines data programmatically is discussed in detail in this document.

2.2Custom Tables

Following Custom Tables will be used:

1. xxcustom_pay_batch_headers – structure will be same as pay_batch_headers. The column batch_status will be updated with a value ‘E’ (error) if the batch fails during the custom validation. If any of the lines for the batch fail during the custom validation, the batch line status in the lines custom table will be set to ‘E’, along with the batch header status.

2. cccustom_pay_batch_lines - structure will be same as pay_batch_lines. The column batch_line_status will be updated with a value ‘E’ (error) if the batch line fails during the custom validation. If any of the lines for the batch fail during the custom validation, the batch line status in the lines custom table will be set to ‘E’, along with the batch header status in the header custom table.

2.3Entering a Batch Header

Navigation: Mass Information eXchange: MIX  Batch Element Entry

Assumption: Custom table xxcustom_batch_headers having the same structure as that of the base table pay_batch_headers is populated with the data from the data file. The custom table will contain the batch header data that needs to be populated into the base table pay_batch_headers. Please, refer the custom table section for the details of the columns in the custom table.

A batch header can be entered using the screen provided through the front end. Programmatically, the batch header can be populated with the help of the API: PAY_BATCH_ELEMENT_ENTRY_API.CREATE_HEADER

Alternatively, a record can be inserted into the table PAY_BATCH_HEADERS after carrying out the necessary validations.

2.3.1Mandatory Fields

PAY_BATCH_HEADERS
Column Name	Direct Insertion	API Parameter
BATCH_ID (Not Null in the table)	PAY_BATCH_HEADERS_S.NEXTVAL	P_BATCH_ID, out parameter. A batch Id will be created and the value will be made available with the help of the out parameter.
BUSINESS_GROUP_ID (Not Null in the table)	Business Group Id of the Employee Assignment, for which the element entries need to be created.	P_BUSINESS_GROUP_ID, in parameter.
BATCH_NAME (Not Null in the table)	Name of the batch	P_BATCH_NAME, in parameter.
BATCH_STATUS (Not Null in the table)	Status of the Batch Possible values: U – Unprocessed V – Valid P – Processed	P_BATCH_STATUS, in parameter.
ACTION_IF_EXISTS	Action that needs to be taken in case an element entry as of the effective day already exists. Possible Values: I – Create a New Entry, even if the one already exists R – Reject the Entry U – Update the existing entry with the new one.	P_ACTION_IF_EXISTS, in parameter.
PURGE_AFTER_TRANSFER	The interface tables should be purged after the records are transferred to the element entry base tables. Possible Values: Y – Yes N – No	P_REJECT_IF_FUTURE_CHANGES, in parameter.
REJECT_IF_FUTURE_CHANGES	The batch should be rejected if there are any future changes existing for the assignment. Possible Values: Y – Yes N – No	P_ REJECT_IF_FUTURE_CHANGES, in parameter.
DATE_EFFECTIVE_CHANGES	Value should be available if the Action If Exists is U	P_DATE_EFFECTIVE_CHANGES, in parameter

2.4Entering Batch Lines

Navigation: Mass Information eXchange: MIX  Batch Element Entry  Element Lines

Assumption: Custom table xxcustom_batch_lines having the same structure as that of the base table pay_batch_lines is populated with the data from the data file. The custom table will contain the batch header data that needs to be populated into the base table pay_batch_lines. Please, refer the custom table section for the details of the columns in the custom table.

Batch lines can be populated using the element sets or assignment sets by running concurrent processes. Programmatically, the batch lines can be populated with the help of the API: PAY_BATCH_ELEMENT_ENTRY_API.CREATE_BATCH_LINE

Alternatively, a record can be inserted into the table PAY_BATCH_LINES after carrying out the necessary validations.

2.4.1Mandatory Fields

PAY_BATCH_LINES
Column Name	Direct Insertion	API Parameter
BATCH_LINE_ID (Not Null in the table)	PAY_BATCH_LINES_S.NEXTVAL	P_BATCH_LINE_ID, out parameter. A batch Id will be created and the value will be made available with the help of the out parameter.
BATCH_ID (Not Null in the table)	PAY_BATCH_HEADERS.BATCH_ID	BATCH_ID from PAY_BATCH_HEADERS
BATCH_STATUS (Not Null in the table)	Status of the Batch Possible values: U – Unprocessed V – Valid P – Processed	P_BATCH_STATUS, in parameter.
BATCH_SEQUENCE	General counter, increment by one for every line.	P_BATCH_SEQUENCE, in parameter. General counter, increment by one for every line.
ELEMENT_TYPE_ID	Element Type Id of the element.	P_ELEMENT_TYPE_ID, in parameter. Element Type Id of the element.
ASSIGNMENT_ID	Employee Assignment Id	P_ASSIGNMENT_ID, in parameter. Employee assignment id.
ASSIGNMENT_NUMBER	Employee assignment Number.	P_ASSIGNMENT_NUMBER, in parameter. Employee assignment number.
EFFECTIVE_DATE	Date from which the element entry needs to be effective.	P_EFFECTIVE_DATE, in parameter.
ELEMENT_NAME	Name of the element for which the entry needs to be done.	P_ELEMENT_NAME, in parameter.
ENTRY_TYPE	Element Entry Type. Possible Values: E - Earning	P_ENTRY_TYPE, in parameter.
VALUE_1 … VALUE_15	Input values.	P_VALUE1 … P_VALUE15, in parameters.

2.5Validating a Batch

Batch validation can be done in two phases:

1. Custom validations to be done before populating the batch tables.

2. Seeded Validation process available.

2.5.1Custom Validation

PAY_BATCH_HEADERS
Column Name	Selected Id	Validation
BATCH_ID	PAY_BATCH_HEADERS_S.NEXTVAL	None
BUSINESS_GROUP	HR_ALL_ORGANIZATION_UNITS.organization_id	WHERE hr_organization_information.org_information_context = ‘Business Group Information’ AND hoi.organization_id = haou.organization_id AND haou.name = ‘<Business Group Name>’
BATCH_NAME	Custom batch name	None
BATCH_STATUS	‘U’	None
ACTION_IF_EXISTS	‘I’ : For multiple entry allowed elements ‘R’	None
DATE_EFFECTIVE_CHANGES	Possible values: U – Update C – Correct	None

PAY_BATCH_LINES
Column Name	Selected Id	Validation
BATCH_LINE_ID	PAY_BATCH_LINES_S.NEXTVAL	None
BATCH_ID	PAY_BATCH_HEADERS.BATCH_ID	WHERE pbh.batch_name = ‘<Name of the Batch>’
BATCH_STATUS	PAY_BATCH_HEADERS.BATCH_STATUS	WHERE pbh.batch_name = ‘<Name of the Batch>’
BATCH_SEQUENCE	Custom counter, increment by 1 for every line.	None
ELEMENT_TYPE_ID	PAY_ELEMENT_TYPES_F.ELEMENT_TYPE_ID	WHERE element_name = element_name from the data file AND business_group_id = business_group_id for the business group in the data file
ASSIGNMENT_ID	PER_ALL_ASSIGNMENTS_F.assignment_id	WHERE assignment_number = assignment_number from the data file AND business_group_id = business_group_id for the business group in the data file AND effective_start_date of the element entry between paaf.effective_start_date and paaf.effective_end_date
ASSIGNMENT_NUMBER	PER_ALL_ASSIGNMENTS_F.assignment_number	WHERE assignment_number = assignment_number from the data file AND business_group_id = business_group_id for the business group in the data file AND effective_start_date of the element entry between paaf.effective_start_date and paaf.effective_end_date
EFFECTIVE_DATE	Effective date from the data file.	None
ELEMENT_NAME	PAY_ELEMENT_TYPES_F.element_name	WHERE element_name = element_name from the data file AND business_group_id = business_group_id for the business group in the data file
ENTRY_TYPE	E, D	None
VALUE_1 … VALUE_15	Value 1 … value 15 from the data file.	Refer the input value validation logic.

2.5.2Seeded Validation

The validation process tests each batch line against certain predefined rules about element entries, and also against any customized rules. These customized rules could be created in the additional validation procedures.

In addition, several batches could be validated together from the Batch Summary window.

To validate a batch from the front-end:

1. Query the batch in the Batch Header window, and choose the Process button.

2. Select Validate, and choose Start. The system displays the concurrent request ID so that it can be queried in the Requests window.

3. When the concurrent request is completed, query the batch in the Batch Header window. If there are several batches to review, query them in the Batch Summary window.

The concurrent program BEE Batch Process (Validate) can be submitted with the following parameters to validate the batch:

Batch Id: This is the Batch Id for the batch that needs to be validated. Get the batch id from the pay_batch_headers base table.

2.6Processing a Batch

• Query the batch in the Batch Header window, and choose the Process button.

• Select Transfer, and choose Start. The system displays the concurrent request ID so that it can be queried in the Requests window.

• When the concurrent request is completed, query the batch in the Batch Header window. If the Batch Status is Transferred, there were no errors in the batch and the process has created the element entries. The process may have issued messages associated with the batch header, lines, or control totals.

• If the Batch Status is Error, the process has set the status of at least one line, or control total to Error. Check the status fields in the Batch Lines window and the Control Totals window, and review the messages issued by the process.

• Alternatively, the batch can be processed by submitting a concurrent program: BEE Batch Process (Transfer) by passing the batch id as an input parameter. The batch id is for the batch that needs to be transferred.

• It is advised that the batch be validated before transferring so that the errors could be trapped earlier.

2.7AOL Details

2.7.1Concurrent Program Executable

Executable name and Short name	Application	Description	Application Method	Execution File Name
XXPAY_BEE_CUSTOM_PROGRAM	Application Object Library	XXPAY – Custom Program to Populate the Batch Element Entry base tables.	PL/SQL Stored Procedure	xxpay_bee_pkg.xxcustom_element_entry_main

2.7.2Concurrent Program

Program Name	Short Name	Application	Executable name
XXPAY – Custom Program to Populate the Batch Element Entry base tables.	XXPAY_BEE_CUSTOM_PROGRAM	Application Object Library	XXPAY_BEE_CUSTOM_PROGRAM

2.7.3Concurrent Program Parameters

1. Sequence => 10

2. Parameter => Batch Name

3. Enabled => Checked

4. Value Set => XXPAY_BATCH_NAME

5. Required => Not Checked

6. Display size => 25

7. Description size => 50

8. Concatenated Description Size => 25

9. Prompt => Batch Name

1. Sequence => 20

2. Parameter => Element Name

3. Enabled => Checked

4. Value Set => XXPAY_ELEMENT_NAME

5. Required => Not Checked

6. Display size => 25

7. Description size => 50

8. Concatenated Description Size => 25

9. Prompt => Element Name

1. Sequence => 30

2. Parameter => Business Group

3. Enabled => Checked

4. Value Set => FV_10_Numeric

5. Default Type => Profile

6. Value => PER_BUSINESS_GROUP_ID

7. Required =>Checked

8. Displayed => Not Checked

9. Display size => 25

10. Description size => 50

11. Concatenated Description Size => 25

12. Prompt => Business Group

2.7.4Value Sets

Value Set Name	Type	Table	SQL Statement
XXPAY_BATCH_NAME	Table	XXPAY_PAY_BATCH_HEADERS	SELECT DISTINCT batch_name FROM XXPAY_PAY_BATCH_HEADERS
XXPAY_ELEMENT_NAME	Table	XXPAY_PAY_BATCH_LINES	SELECT DISTINCT element_name FROM XXPAY_PAY_BATCH_LINES

2.7.5Request Group

The concurrent program has been added to the request Group: US SHRMS Reports & Processes

2.7.6Responsibility

The custom program and the seeded program are run from the responsibility: US Super HRMS Manager for business group Vision Corporation.

2.8Observations

Following set ups need to be in place for the BEE program to work:

1. Business group should be set up

2. Work structures should be in place

3. People data should be available with assignments, for which the element entries need to me made

4. Elements to be used should be set up

5. If costing KFF is enabled at the element level, the segments to be created should be identified and provided in the data file.

Related Articles to Read