--Adrian, Feb 1 2017

CREATE OR REPLACE FUNCTION r_barcode_warranty(character varying, bigint, bigint, bigint, character varying, bigint)
  RETURNS SETOF refcursor AS
$BODY$
DECLARE
    pRefHeader          REFCURSOR := 'refHeader';
    pRefDetail          REFCURSOR := 'refDetail';

    pSessionId          ALIAS FOR $1;
    pTenantId           ALIAS FOR $2;
    pUserId             ALIAS FOR $3;
    pRoleId             ALIAS FOR $4;
    pDatetime           ALIAS FOR $5;

    pDoId               ALIAS FOR $6;

    vEmptyString        character varying := '';
    vZero               bigint := 0;

BEGIN

    Open pRefHeader FOR
    SELECT 1;
    RETURN NEXT pRefHeader;

    Open pRefDetail FOR
        EXECUTE '
            SELECT f_get_product_code(A.product_id)||''#''||F.serial_number AS qr_code,
                f_get_product_code(A.product_id) AS model, F.serial_number AS serial_number,
                D.warranty_type_name AS warranty_type_name,
                $3 AS upgrade,
                $3 AS help,
                $4 AS time,
                $3 AS location,
                $3 AS support
            FROM sl_do_item A
            INNER JOIN sl_so_item B ON B.so_item_id = A.ref_id AND B.product_id = A.product_id
            INNER JOIN sl_so_warranty_item C ON C.so_item_id = B.so_item_id
            INNER JOIN m_warranty_type D ON D.warranty_type_id = C.warranty_type_id
            INNER JOIN sl_do_product E ON E.do_item_id = A.do_item_id
            INNER JOIN in_product_balance F ON F.product_balance_id = E.product_balance_id
            WHERE A.do_id = $1 AND A.tenant_id = $2
            'USING pDoId, pTenantId, vEmptyString, vZero;
    RETURN NEXT pRefDetail ;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
/