f_get_status_monitoring_order_pembelian

CREATE OR REPLACE FUNCTION pb.f_get_status_monitoring_order_pembelian(bigint)
  RETURNS character varying AS
$BODY$
DECLARE

  pPoId                 ALIAS FOR $1;

  vStatusDocOp              character varying;
  vStatusDocPb              character varying;
  vStatusString             character varying;

  vStatusFinal              character varying := 'F';
  vStatusDraft              character varying := 'D';
  vStatusRelease            character varying := 'R';
  vQty                  bigInt;

BEGIN

    SELECT status_doc INTO vStatusDocOp FROM pu_po WHERE po_id = pPoId;

    IF vStatusDocOp = vStatusDraft THEN
        vStatusString:= 'Draft';
    ELSIF vStatusDocOp = vStatusRelease THEN
        SELECT SUM(A.qty_po) - SUM(A.qty_cancel) + SUM(A.qty_add) - SUM(A.qty_rcv) INTO vQty FROM pu_po_balance_item A
            INNER JOIN pu_po_item B ON B.po_item_id = A.po_item_id
            WHERE B.po_id = pPoId;

        IF vQty > 0 THEN
            vStatusString:= 'SUDAH DITERIMA SEBAGIAN';
        ELSE
            SELECT status_doc INTO vStatusDocPb FROM pu_receive_goods WHERE ref_id = pPoId;

            IF NOT FOUND THEN
                vStatusString:= 'DISETUJUI';
            ELSIF vStatusDocPb = vStatusDraft THEN
                vStatusString:= 'SEDANG DALAM PROSES PENERIMAAN';
            END IF;
        END IF;

    ELSIF vStatusDocOp = vStatusFinal THEN
        vStatusString:= 'FINAL';
    END IF;

    RETURN vStatusString;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
SET search_path=public, mstr, pb, batch
  COST 100;