REPORT O/S GTI RECEIPT

-- Adrian, Mar 16, 2017

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

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

    pOu                 ALIAS FOR $6;
    pDateFrom           ALIAS FOR $7;
    pDateTo             ALIAS FOR $8;
    pProductCodeName    ALIAS FOR $9;

    vFilterProduct      text = '';

    vEmptyString        character varying := '';
    vEmptyDate          character varying := '-';
    vFlgInProgress      character varying := 'I';
    vStatusInProgress   character varying := 'In Progress';
    vFlgDraft           character varying := 'D';
    vStatusDraft        character varying := 'Draft';
    vFLgApprove         character varying := 'R';
    vStatusApprove      character varying := 'Approve';
    vFlgNo              character varying := 'N';

    vDocTypeIdtransferIn  bigint := 535;
    vZero  bigint := 0;

BEGIN

    OPEN vRefHeader FOR
        SELECT f_get_ou_name(pOu) AS ou_name, pDateFrom AS date_from, pDateTo AS date_to, pDatetime AS datetime,
            f_get_username(pUserId) AS username, pProductCodeName AS product_code_name;

    RETURN NEXT vRefHeader;

    IF (pProductCodeName <> vEmptyString) THEN
        vFilterProduct := ' AND (UPPER(f_get_product_code(A.product_id)) LIKE UPPER(''%' || pProductCodeName || '%'') OR UPPER(f_get_product_name(A.product_id)) LIKE UPPER(''%' || pProductCodeName || '%''))';
    END IF;

    OPEN vRefDetail FOR

    EXECUTE '
        SELECT B.doc_date AS transfer_in_date, B.doc_no AS transfer_in_no,
            f_get_warehouse_name(A.warehouse_from_id) AS warehouse_from,
            f_get_warehouse_name(A.warehouse_to_id) AS warehouse_to,
            f_get_product_name(A.product_id) AS product_name,
            f_get_product_code(A.product_id) AS product_code,
            f_get_uom_name(A.base_uom_id) AS base_uom,
            A.qty_out AS qty_out, A.qty_in AS qty_in, (A.qty_out - A.qty_in) AS qty_deviation
        FROM in_balance_transfer_in_item A
        INNER JOIN in_inventory B ON B.inventory_id = A.inventory_id
        WHERE A.tenant_id = $1 AND
            A.ou_from_id = $2 AND
            B.doc_type_id = $3 AND
            A.flg_receipt = $4 AND
            B.doc_date BETWEEN $5 AND $6 AND
            (A.qty_out - A.qty_in) <> $7 '
            || vFilterProduct ||'
        ORDER BY B.doc_date'
    USING pTenantId, pOu, vDocTypeIdtransferIn, vFlgNo, pDateFrom, pDateTo, vZero;

    RETURN NEXT vRefDetail;

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