Main menu

Transaction Open Units Stored Procedure

Submitted by mikehostetler on Thu, 04/26/2007 - 08:53

This function was easy to write after writing the Transaction Open Batches Stored Procedure.

<?php
--
-- Transaction Open Units Stored Procedure
--

USE <

code>cics_app</code>;

DROP PROCEDURE IF EXISTS Transaction_OpenUnits;

DELIMITER |
CREATE PROCEDURE Transaction_OpenUnits (IN unit_account_id INT)
BEGIN
SELECT
        <code>transaction</code>.<code>unit_number</code> AS unit_no,
        <code>category</code>.<code>category_id</code> AS category_id,
       <code>category</code>.<code>calc_name</code> AS category,

      

IFNULL(
            IFNULL((
               SELECT SUM(<code>transaction</code>.<code>amount</code>) FROM <code>transaction</code>
              WHERE <code>unit_number</code> = <code>unit_no</code> AND <code>credit_account_id</code> = unit_account_id
          ),0)
       -
          IFNULL((
               SELECT SUM(<code>transaction</code>.<code>amount</code>) FROM <code>transaction</code>
              WHERE <code>unit_number</code> = <code>unit_no</code> AND <code>debit_account_id</code> = unit_account_id
           ),0)
       ,0) AS unit_balance
    FROM
       <code>transaction</code>

LEFT JOIN <code>category</code> ON <code>transaction</code>.<code>category_id</code> = <code>category</code>.<code>category_id</code>

   

WHERE
     <code>transaction</code>.<code>unit_number</code> > 0
GROUP BY
       <code>transaction</code>.<code>unit_number</code>
    HAVING
     unit_balance > 0;
END
|

DELIMITER ;
?>
Taxonomy upgrade extras: 
mysql
stored procedures
cics
mysqli