app/sql/get_stock_summary.sql

-- FUNCTION: trans.get_stock_summary(character varying, character varying, character varying)

-- DROP FUNCTION IF EXISTS trans.get_stock_summary(character varying, character varying, character varying);

CREATE OR REPLACE FUNCTION trans.get_stock_summary(
	p_merchant_id character varying,
	p_warehouse_id character varying DEFAULT NULL::character varying,
	p_sku character varying DEFAULT NULL::character varying)
    RETURNS TABLE(stock_id uuid, warehouse_id character varying, catalogue_id uuid, sku character varying, batch_no character varying, qty_on_hand numeric, qty_reserved numeric, qty_available numeric, uom character varying, last_updated_at timestamp without time zone) 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
    ROWS 1000

AS $BODY$
BEGIN
    RETURN QUERY
    SELECT s.stock_id, s.warehouse_id, s.catalogue_id, s.sku, s.batch_no,
           s.qty_on_hand, s.qty_reserved, s.qty_available, s.uom, s.last_updated_at
    FROM trans.scm_stock s
    WHERE s.merchant_id = p_merchant_id
      AND (p_warehouse_id IS NULL OR s.warehouse_id = p_warehouse_id)
      AND (p_sku IS NULL OR s.sku = p_sku)
    ORDER BY s.sku, s.batch_no;
END;
$BODY$;

ALTER FUNCTION trans.get_stock_summary(character varying, character varying, character varying)
    OWNER TO trans_owner;