Batch control totals provide a mechanism for customizing the validation of batch contents to meet particular user requirements. This validation may be done for example, by doing total, or average operations on the batch lines and matching the values with values entered by the user.
Batches can be entered and viewed using the Batch Header window, and other windows available from it.
A control total type is predefined for checking the number of lines in a batch (control type = Total Lines).
You can create control totals to sum numerical element input values by defining a lookup for the lookup type CONTROL_TYPE. See: Setting Up BEE Validation Using Control Totals, Oracle HRMS Configuring, Reporting, and System Administration Guide
If you need other kinds of control totals, you can define lookups for them, but you must also write a validation procedure for checking the batch against the total. The next section explains how to write this validation procedure.
The following procedure is delivered with a null statement in it. Replace the null statement with your customized control total validation code:
Procedure: check_control
Package: pay_user_check
File: pyusrchk.pkb
The check_control procedure is executed during the batch validation phase of the BEE process. The parameters passed to this procedure are:
p_batch_id The batch ID.
p_control_type The name of the control total.
p_control_total The user entered value to match.
Two other parameters (p_status, p_message) are used in this procedure to return an error code and message to the system if the batch control total validation fails.
Each line of batch data is stored as a record in the pay_batch_lines table. The data is stored in the fields value_1 - value_15. The number of the field corresponds to the column in the Batch Lines window.
For example, if you want to validate a check digit, you could use the following PL/SQL code as a basis:
PROCEDURE check_control
(
p_batch_id IN NUMBER,
p_control_type IN VARCHAR2,
p_control_total IN VARCHAR2,
p_status IN OUT VARCHAR2,
p_message OUT VARCHAR2
) IS
total NUMBER;
BEGIN
-- Check the control type is the one we're expecting
IF p_control_type = 'CHECK_DIGIT' THEN
-- Calculate the MOD 10 of total values in value_1
SELECT MOD(NVL(SUM(value_1),0),10) INTO total FROM pay_batch_lines
WHERE batch_id = p_batch_id;
-- Compare with the user entered value
IF total <> p_control_total THEN
-- Create the error message to return and set the status to E(rror)
p_message := 'Control total TOT1 (' || p_control_total ||
'does not match calculated value (' || total ||
')';
p_status := 'E';
ENDIF;
ENDIF;
END check_control;
This, however, is a very simplistic example. If batch lines within the same batch are entered for more than one element then the value columns may vary between elements. Here is a more complex example to validate the check digit on the input value 'Identification':
PROCEDURE check_control
(
p_batch_id IN NUMBER,
p_control_type IN VARCHAR2,
p_control_total IN VARCHAR2,
p_status IN OUT VARCHAR2,
p_message OUT VARCHAR2
) IS
CURSOR c1 IS
SELECT DISTINCT element_type_id
FROM pay_batch_lines
WHERE batch_id = p_batch_id;
--
r1 c1%ROWTYPE;
total NUMBER;
value_num NUMBER;
sqlstr VARCHAR2(200);
c2 INTEGER;
ret INTEGER;
BEGIN
--
-- Check the control type is the one we're expecting
IF p_control_type = 'CHECK_DIGIT2' THEN
total := 0;
--
-- Loop through each element in the batch lines
FOR r1 IN c1 LOOP
--
-- Find out the value number that 'Identification' is in
SELECT display_sequence
INTO value_num
FROM pay_input_values iv,
pay_batch_headers bh,
pay_element_types et
WHERE bh.batch_id = p_batch_id AND
iv.business_group_id = bh.business_group_id AND
et.element_type_id = r1.element_type_id AND
iv.element_type_id = et.element_type_id AND
iv.name = 'Identification';
--
-- Create an SQL string to add the values
sqlstr := 'SELECT MOD(NVL(SUM(value_' || value_num ||
'),0),10) ' ||
'FROM pay_batch_lines ' ||
'WHERE batch_id = ' || p_batch_id || ' AND '
||'element_type_id = ''' ||
r1.element_type_id || '''';
--
-- Call the string using dynamic SQL and put the value in 'total'
c2 := dbms_sql.open_cursor;
dbms_sql.parse (c2,sqlstr,dbms_sql.v7);
dbms_sql.define_column (c2,1,total);
ret := dbms_sql.execute (c2);
ret := dbms_sql.fetch_rows (c2);
--
-- Check we got some values back
if ret > 0 then
dbms_sql.column_value (c2,1,total);
else
total := 0;
end if;
--
dbms_sql.close_cursor (c2);
--
-- Check the total matches the user entered value and create an
-- error message if it doesn't
IF total <> p_control_total THEN
p_message := 'Check digit expected '||p_control_total||
' but got '||to_char(total);
p_status := 'E';
END IF;
END LOOP;
END IF;
END check_control;