This is a procedure that simply gets the balance of a batch. A batch is a business level object that is reflected at this level. This procedure is a direct descendant of the Account Balance Stored Procedure.
<?php
--
-- Transaction Batch Balance Stored Procedure
--
USE `cics_app`;
DROP PROCEDURE IF EXISTS Transaction_BatchBalance;
DELIMITER |
CREATE PROCEDURE Transaction_BatchBalance (IN batch_number INT, IN batch_account_id INT)
BEGIN
DECLARE batch_balance, batch_credit, batch_debit INT;
SELECT
SUM(t.amount) INTO batch_credit
FROM
transaction AS t
WHERE
t.batch_number = batch_number
AND
t.credit_account_id = batch_account_id;
SELECT
SUM(t.amount) INTO batch_debit
FROM
transaction AS t
WHERE
t.batch_number = batch_number
AND
t.debit_account_id = batch_account_id;
SET batch_debit = IFNULL(batch_debit,0);
SET batch_credit = IFNULL(batch_credit,0);
SET batch_balance = batch_debit - batch_credit;
SET batch_balance = IFNULL(batch_balance,0);
SELECT batch_balance;
END;
|
DELIMITER ;
?>
