app/sql/get_stock_ledger.sql

-- FUNCTION: trans.get_stock_ledger(character varying, character varying, character varying, integer)

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

CREATE OR REPLACE FUNCTION trans.get_stock_ledger(
	p_merchant_id character varying,
	p_sku character varying DEFAULT NULL::character varying,
	p_batch_no character varying DEFAULT NULL::character varying,
	p_limit integer DEFAULT 100)
    RETURNS TABLE(ledger_id uuid, warehouse_id character varying, catalogue_id uuid, sku character varying, batch_no character varying, txn_type character varying, qty numeric, uom character varying, ref_type character varying, ref_id uuid, ref_no character varying, remarks text, created_by character varying, created_at timestamp without time zone) 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
    ROWS 1000

AS $BODY$
BEGIN
    RETURN QUERY
    SELECT l.ledger_id, l.warehouse_id, l.catalogue_id, l.sku, l.batch_no,
           l.txn_type, l.qty, l.uom, l.ref_type, l.ref_id, l.ref_no,
           l.remarks, l.created_by, l.created_at
    FROM trans.scm_stock_ledger l
    WHERE l.merchant_id = p_merchant_id
      AND (p_sku IS NULL OR l.sku = p_sku)
      AND (p_batch_no IS NULL OR l.batch_no = p_batch_no)
    ORDER BY l.created_at DESC
    LIMIT p_limit;
END;
$BODY$;

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