f_get_product_balance_stock_exclude_reserved

CREATE OR REPLACE FUNCTION f_get_product_balance_stock_exclude_reserved(bigint, bigint, bigint)
  RETURNS numeric AS
$BODY$
DECLARE
    pTenantId       alias for $1;
    pProductId      alias for $2;
    pOuId           alias for $3;

    vResult         numeric;

BEGIN

    vResult := 0;

    IF EXISTS (SELECT 1
                FROM in_product_balance_stock A
                    INNER JOIN m_warehouse_ou B ON A.warehouse_id = B.warehouse_id
                WHERE A.tenant_id = pTenantId AND
                    A.product_id = pProductId AND
                    B.ou_id = pOuId AND
                    A.product_status =  'GOOD') THEN

        WITH data_product_balance_stock AS(
            SELECT SUM(A.qty) AS qty_product_balance_stock, A.product_id, B.ou_id, A.tenant_id, A.product_status
            FROM in_product_balance_stock A
            INNER JOIN m_warehouse_ou B ON A.warehouse_id = B.warehouse_id
            WHERE A.tenant_id = pTenantId
                AND A.product_id = pProductId
                AND B.ou_id = pOuId
                AND A.product_status = 'GOOD'
            GROUP BY A.product_id, B.ou_id, A.tenant_id, A.product_status

        ), data_product_balance_stock_reserved AS(
            SELECT SUM(A.qty) AS qty_product_balance_stock_reserved, A.product_id, A.ou_id, A.tenant_id, A.product_status
            FROM in_product_balance_stock_reserved A
            WHERE A.tenant_id = pTenantId
                AND A.product_id = pProductId
                AND A.ou_id = pOuId
                AND A.product_status = 'GOOD'
            GROUP BY A.product_id, A.ou_id, A.tenant_id, A.product_status
        )

        SELECT (SUM(A.qty_product_balance_stock) - SUM(COALESCE(B.qty_product_balance_stock_reserved, 0))) AS stock_product INTO vResult
        FROM data_product_balance_stock A
            LEFT OUTER JOIN data_product_balance_stock_reserved B
                ON A.product_id = B.product_id AND A.tenant_id = B.tenant_id AND A.product_status = B.product_status
        WHERE A.tenant_id = pTenantId
            AND A.product_id = pProductId
            AND A.ou_id = pOuId
            AND A.product_status = 'GOOD';

    END IF;

    return vResult;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
  /