Annotation: core.count_sales

Information: core.count_sales

Implementation: core.count_sales

CREATE OR REPLACE FUNCTION core.count_sales(_item_id integer, _unit_id integer, _store_id integer)
 RETURNS numeric
 LANGUAGE plpgsql
 STABLE
AS $function$
        DECLARE _base_unit_id integer;
        DECLARE _credit decimal;
        DECLARE _factor decimal;
BEGIN
    --Get the base item unit
    SELECT 
        core.get_root_unit_id(core.items.unit_id) 
    INTO _base_unit_id
    FROM core.items
    WHERE core.items.item_id=$1;

    SELECT 
        COALESCE(SUM(base_quantity), 0)
    INTO _credit
    FROM transactions.stock_details
    INNER JOIN transactions.stock_master
    ON transactions.stock_master.stock_master_id = transactions.stock_details.stock_master_id
    INNER JOIN transactions.transaction_master
    ON transactions.stock_master.transaction_master_id = transactions.transaction_master.transaction_master_id
    WHERE transactions.transaction_master.verification_status_id > 0
    AND transactions.stock_details.item_id=$1
    AND transactions.stock_details.store_id=$3
    AND transactions.stock_details.tran_type='Cr';

    _factor = core.convert_unit($2, _base_unit_id);    
    RETURN _credit * _factor;
END
$function$