f_confirm_goods_receipt

CREATE OR REPLACE FUNCTION pb.f_confirm_goods_receipt(bigint, character varying, character varying, character varying, character varying, text)
  RETURNS void AS
$BODY$
DECLARE

  pReceiveGoodsId           ALIAS FOR $1;
  pUsername             ALIAS FOR $2;
  pDateTime             ALIAS FOR $3;
  pSessionId                ALIAS FOR $4;
  pDateConfirm              ALIAS FOR $5;
  pRemarkConfirm            ALIAS FOR $6;

  vStatusRelease            character varying := 'R';
  vStatusFinal              character varying := 'F';
  vUnfinishedItem           bigint;
  vPoId                 bigint;
  vProductStatusGood            character varying := 'GOOD';
  vModeActionUpdate         character varying := 'U';
  vModeActionAdd            character varying := 'A';
  vEmptySpace               character varying := '';

BEGIN

    SELECT ref_id INTO vPoId FROM pu_receive_goods WHERE receive_goods_id = pReceiveGoodsId;

    -- update status item menjadi R jika qty_rcv < qty_po
    UPDATE pu_po_balance_item A SET status_item = vStatusRelease, version = A.version+1, update_datetime = pDateTime, update_username = pUsername
        FROM pu_receive_goods_item B
        WHERE A.po_item_id = B.ref_item_id AND
        B.receive_goods_id = pReceiveGoodsId AND
        A.qty_po - A.qty_cancel + A.qty_add - A.qty_rcv > 0;

    -- update status item menjadi F jika qty_rcv = qty_po
    UPDATE pu_po_balance_item A SET status_item = vStatusFinal, version = A.version+1, update_datetime = pDateTime, update_username = pUsername
        FROM pu_receive_goods_item B
        WHERE A.po_item_id = B.ref_item_id AND
        B.receive_goods_id = pReceiveGoodsId AND
        A.qty_po - A.qty_cancel + A.qty_add - A.qty_rcv <= 0;

    -- Update pu_receive_goods ubah status menjadi R, doc_date & remark_confirm
    UPDATE pu_receive_goods set status_doc = vStatusRelease, doc_date = pDateConfirm, remark_confirm = pRemarkConfirm, update_datetime = pDateTime, update_username = pUsername
        WHERE receive_goods_id = pReceiveGoodsId;

    SELECT COUNT(1) INTO vUnfinishedItem
        FROM pu_po_balance_item A, pu_po_item B
        WHERE A.po_item_id = B.po_item_id AND
        B.po_id = vPoId AND
        A.status_item = vStatusRelease;

    IF vUnfinishedItem = 0 THEN
        UPDATE pu_po SET status_doc = vStatusFinal, update_datetime = pDateTime, update_username = pUsername
        WHERE po_id = vPoId;
    END IF;

    -- insert ke table pu_po_balance_invoice
    INSERT INTO pu_po_balance_invoice(
        record_owner_id, ref_doc_type_id, ref_id, qty_recieve, buy_price, total_gross_amount, flg_invoice, invoice_id,
        version, create_datetime, create_username, update_datetime, update_username)
    SELECT A.record_owner_id, A.doc_type_id, A.receive_goods_id, B.qty_receive, D.unit_price, D.unit_price * B.qty_receive, 'N', -99,
        0, pDateTime, pUsername, pDateTime, pUsername
    FROM pu_receive_goods A
    INNER JOIN pu_receive_goods_item B ON B.receive_goods_id = A.receive_goods_id
    INNER JOIN pu_po_balance_item C ON C.po_item_id = B.ref_item_id
    INNER JOIN pu_po_item D ON D.po_item_id = C.po_item_id
    WHERE A.receive_goods_id = pReceiveGoodsId;

    -- insert ke table in_product_balance
    INSERT INTO in_product_balance(
        record_owner_id, product_id, serial_number, lot_number, expired_date, year_made,
        version, create_datetime, create_username, update_datetime, update_username)
    SELECT A.record_owner_id, B.product_id, vEmptySpace, vEmptySpace, vEmptySpace, vEmptySpace,
    0, pDateTime, pUsername, pDateTime, pUsername
    FROM pu_receive_goods A
    INNER JOIN pu_receive_goods_item B ON B.receive_goods_id = A.receive_goods_id
    WHERE A.receive_goods_id = pReceiveGoodsId
    AND NOT EXISTS (SELECT 1 FROM in_product_balance C WHERE C.record_owner_id = A.record_owner_id AND C.product_id = B.product_id);

    -- insert ke table temp dengan mode Add
    INSERT INTO tt_product_balance(
        session_id, record_owner_id, ref_doc_type_id, ref_doc_id, ref_item_id, ref_doc_no, ref_doc_date, product_balance_id,
        product_balance_stock_id, product_id, qty, warehouse_id, serial_number, lot_number, expired_date, year_made, product_status, mode_action)
        SELECT pSessionId, A.record_owner_id, A.doc_type_id, A.receive_goods_id, B.receive_goods_item_id, A.doc_no, A.doc_date, C.product_balance_id,
        -99, B.product_id, B.qty_receive, E.warehouse_id, vEmptySpace, vEmptySpace, vEmptySpace, vEmptySpace, vProductStatusGood, vModeActionAdd
    FROM pu_receive_goods A
    INNER JOIN pu_receive_goods_item B ON B.receive_goods_id = A.receive_goods_id
    INNER JOIN in_product_balance C ON C.product_id = B.product_id
    INNER JOIN m_record_owner E ON E.record_owner_id = A.record_owner_id
    WHERE A.receive_goods_id = pReceiveGoodsId AND A.record_owner_id = C.record_owner_id
    AND NOT EXISTS (SELECT 1 FROM in_product_balance_stock F
        WHERE F.record_owner_id = A.record_owner_id AND F.product_balance_id = C.product_balance_id AND F.product_id = C.product_id);

    -- insert ke table temp dengan mode Update
    INSERT INTO tt_product_balance(
        session_id, record_owner_id, ref_doc_type_id, ref_doc_id, ref_item_id, ref_doc_no, ref_doc_date, product_balance_id,
        product_balance_stock_id, product_id, qty, warehouse_id, serial_number, lot_number, expired_date, year_made, product_status, mode_action)
        SELECT pSessionId, A.record_owner_id, A.doc_type_id, A.receive_goods_id, B.receive_goods_item_id, A.doc_no, A.doc_date, C.product_balance_id,
        D.product_balance_stock_id, B.product_id, B.qty_receive, E.warehouse_id, vEmptySpace, vEmptySpace, vEmptySpace, vEmptySpace, vProductStatusGood, vModeActionUpdate
    FROM pu_receive_goods A
    INNER JOIN pu_receive_goods_item B ON B.receive_goods_id = A.receive_goods_id
    INNER JOIN in_product_balance C ON C.product_id = B.product_id
    INNER JOIN in_product_balance_stock D ON D.product_balance_id = C.product_balance_id
    INNER JOIN m_record_owner E ON E.record_owner_id = A.record_owner_id
    WHERE A.receive_goods_id = pReceiveGoodsId AND A.record_owner_id = C.record_owner_id;

    -- insert ke table in_product_balance_stock
    INSERT INTO in_product_balance_stock(
        record_owner_id, product_balance_id, product_id, warehouse_id, product_status, qty,
        version, create_datetime, create_username, update_datetime, update_username)
    SELECT record_owner_id, product_balance_id, product_id, warehouse_id, vProductStatusGood, qty,
        0, pDateTime, pUsername, pDateTime, pUsername
    FROM tt_product_balance
    WHERE session_id = pSessionId AND mode_action = vModeActionAdd;

    -- update ke table in_product_balance_stock
    UPDATE in_product_balance_stock A SET qty = A.qty + B.qty , version = version+1, update_datetime = pDateTime, update_username = pUsername
    FROM tt_product_balance B
    WHERE B.session_id = pSessionId AND
    A.product_balance_id = B.product_balance_id AND
    A.product_id = B.product_id
    AND B.mode_action = vModeActionUpdate;

    -- insert log
    INSERT INTO in_log_product_balance_stock(
        record_owner_id, product_balance_id, product_id, warehouse_id, product_status, ref_doc_type_id, ref_id,
        ref_doc_no, ref_doc_date, qty, version, create_datetime, create_username, update_datetime, update_username)
    SELECT record_owner_id, product_balance_id, product_id, warehouse_id, product_status, ref_doc_type_id, ref_doc_id,
        ref_doc_no, ref_doc_date, qty, 0,  pDateTime, pUsername, pDateTime, pUsername
    FROM tt_product_balance
    WHERE session_id = pSessionId;

    -- delete table temp
    DELETE FROM tt_product_balance WHERE session_id = pSessionId;

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