useful-qry/function-in-db/procedures/common/fnc_curr_item_bal.sql

drop procedure if exists fnc_curr_item_bal;
DELIMITER //
create procedure fnc_curr_item_bal( _curr_date varchar(8), _storage int, _target int)

SQL SECURITY INVOKER
BEGIN

Declare _last_amt decimal(20,4) default "0.0000";
Declare _last_bad decimal(20,4) default "0.0000";
Declare _sum_amt decimal(20,4) default "0.0000";
Declare _sum_bad decimal(20,4) default "0.0000";

Declare _bal_date varchar(8);

if _is_first_date(_curr_date) then
	set _bal_date = '19000101';
else
	set _bal_date = _curr_date;
end if;


select bal_qty, bad_bal_qty
into _last_amt, _last_bad
from dbr_bal_item
where item_id = _target
    and yyyy_mm = _prev_month(_curr_date) and storage_id = _storage;


-- 당월 변동잔액
select sum(io_qty*stock_status), sum(io_qty*badstk_status)
into _sum_amt, _sum_bad
from dbr_stock_io
    inner join dbr_deal_type as deal on deal.id = deal_type_id
where item_id = _target
    and io_date between _first_date(_bal_date) and _bal_date
    and storage_id = _storage
group by item_id;

select _last_amt + _sum_amt as c1, _last_bad + _sum_bad as c2;

END;
//
DELIMITER ;

call fnc_curr_item_bal('20230228', 1, 1) ;