app/sql/fn_get_po_items_details.sql

-- FUNCTION: trans.fn_get_po_item_details(uuid)

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

CREATE OR REPLACE FUNCTION trans.fn_get_po_item_details(
	p_po_id uuid)
    RETURNS TABLE(po_id uuid, catalogue_code text, catalogue_name text, hsn_code text, gst_rate numeric, ord_qty numeric, dispatched_qty numeric, rcvd_qty numeric, rejected_qty numeric, returned_qty numeric, net_accepted_qty numeric, unit_price numeric, taxable_amount numeric, gst_amount numeric, final_amount numeric) 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
    ROWS 1000

AS $BODY$
BEGIN
    RETURN QUERY
    SELECT
        poi.po_id,

        cr.catalogue_code,
        cr.catalogue_name,
        cr.hsn_code,
        cr.gst_rate,

        poi.ord_qty,
        poi.dispatched_qty,
        poi.rcvd_qty,
        poi.rejected_qty,
        poi.returned_qty,
        (poi.rcvd_qty - poi.rejected_qty - poi.returned_qty) AS net_accepted_qty,

        poi.unit_price,
        poi.line_amt AS taxable_amount,
        poi.tax_amt AS gst_amount,

        (poi.line_amt + poi.tax_amt) AS final_amount
    FROM trans.scm_po_item poi
    JOIN trans.catalogue_ref cr
        ON cr.catalogue_id = poi.catalogue_id
    WHERE poi.po_id = p_po_id;
END;
$BODY$;

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