app/sql/fn_get_po_items_for_grn.sql

-- FUNCTION: trans.fn_get_po_items_for_grn(uuid)

-- DROP FUNCTION IF EXISTS trans.fn_get_po_items_for_grn(uuid);

CREATE OR REPLACE FUNCTION trans.fn_get_po_items_for_grn(
	p_po_id uuid)
    RETURNS TABLE(po_item_id uuid, catalogue_id uuid, catalogue_name text, ordered_qty numeric, received_qty numeric, rejected_qty numeric, pending_grn numeric, cost_price numeric) 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
    ROWS 1000

AS $BODY$
BEGIN
    RETURN QUERY
    SELECT
        po.id AS po_item_id,
        po.catalogue_id,
        cr.catalogue_name,
        po.ord_qty        AS ordered_qty,
        po.rcvd_qty       AS received_qty,
        po.rejected_qty,
        (po.ord_qty - po.rcvd_qty - po.rejected_qty) AS pending_grn,
        po.cost_price
    FROM scm_po_item po
    JOIN catalogue_ref cr
        ON po.catalogue_id = cr.catalogue_id
    WHERE po.po_id = p_po_id
      AND (po.ord_qty - po.rcvd_qty - po.rejected_qty) > 0
    ORDER BY cr.catalogue_name;
END;
$BODY$;

ALTER FUNCTION trans.fn_get_po_items_for_grn(uuid)
    OWNER TO trans_owner;