CREATE OR REPLACE FUNCTION f_get_outstanding_documents(bigint, bigint)
   RETURNS SETOF refcursor AS
$BODY$
DECLARE
    pRefDetail    REFCURSOR := 'refDetail';
    pTenantId     ALIAS FOR     $1;
    pOuId         ALIAS FOR     $2;

BEGIN

  OPEN pRefDetail FOR

    SELECT Z.doc_no, Z.doc_date, Z.doc_type, Z.username, Z.status_doc, Z.remark
    FROM (
      SELECT A.doc_no, A.doc_date, f_get_doc_desc(A.doc_type_id) AS doc_type, f_get_username(A.create_user_id) AS username,
            A.status_doc, A.remark
      FROM in_inventory A
      WHERE A.tenant_id = pTenantId
        AND A.status_doc IN ('D', 'I')
        AND (A.doc_type_id = ANY (ARRAY[521, 511, 535, 533, 569, 502, 511]))
      UNION
      SELECT A.doc_no, A.doc_date, f_get_doc_desc(A.doc_type_id) AS doc_type, f_get_username(A.create_user_id) AS username,
            A.status_doc, A.remark
      FROM sl_do A
      WHERE A.tenant_id = pTenantId
        AND A.status_doc IN ('D', 'I')
        AND (A.doc_type_id = ANY (ARRAY[311]))
      UNION
      SELECT A.doc_no, A.doc_date, f_get_doc_desc(A.doc_type_id) AS doc_type, f_get_username(A.create_user_id) AS username,
            A.status_doc, A.remark
      FROM in_do_receipt A
      WHERE A.tenant_id = pTenantId
        AND A.status_doc IN ('D', 'I')
        AND (A.doc_type_id = ANY (ARRAY[526]))
      UNION
      SELECT A.doc_no, A.doc_date, f_get_doc_desc(A.doc_type_id) AS doc_type, f_get_username(A.create_user_id) AS username,
            A.status_doc, A.remark
      FROM pu_receive_goods A
      WHERE A.tenant_id = pTenantId
        AND A.status_doc IN ('D', 'I')
        AND (A.doc_type_id = ANY (ARRAY[111]))
      UNION
      SELECT A.doc_no, A.doc_date, f_get_doc_desc(A.doc_type_id) AS doc_type, f_get_username(A.create_user_id) AS username,
            A.status_doc, A.remark
      FROM pu_receive_goods_consignment A
      WHERE A.tenant_id = pTenantId
        AND A.status_doc IN ('D', 'I')
        AND (A.doc_type_id = ANY (ARRAY[113]))
    ) Z
    ORDER BY Z.username, Z.doc_no, Z.doc_date, Z.doc_type, Z.status_doc;

  RETURN NEXT pRefDetail;
END;

$BODY$
    LANGUAGE plpgsql VOLATILE
    COST 100
    ROWS 10000;
/