-- MOdified by Adrian, June 13, 2017, ambil partner_id dari in_inventory pada insert/update in_borrowed_product_balance_stock

CREATE OR REPLACE FUNCTION in_submit_delivery_goods_borrowing(bigint, character varying, character varying)
  RETURNS void AS
$BODY$
DECLARE
    pTenantId           ALIAS FOR $1;
    pSessionId          ALIAS FOR $2;
    pProcessNo          ALIAS FOR $3;

    vProcessId      bigint;
    vBorrowId           bigint;
    vReqDGBDocTypeId    bigint;
    vUserId         bigint;
    vDatetime       character varying(14);
    vFlagNo         character varying(1);
    vStatusRelease  character varying(1);
    vStatusFinal    character varying(1);
BEGIN

    vFlagNo := 'N';
    vStatusRelease := 'R';
    vStatusFinal := 'F';
    vReqDGBDocTypeId := 553;

    SELECT A.process_message_id INTO vProcessId
    FROM t_process_message A
    WHERE A.tenant_id = pTenantId AND
        A.process_name = 'in_submit_delivery_goods_borrowing' AND
        A.process_no = pProcessNo;

    SELECT CAST(A.process_parameter_value AS bigint) INTO vBorrowId
    FROM t_process_parameter A
    WHERE A.process_message_id = vProcessId AND
        A.process_parameter_key = 'deliveryGoodsBorrowingId';

    SELECT CAST(A.process_parameter_value AS bigint) INTO vUserId
    FROM t_process_parameter A
    WHERE A.process_message_id = vProcessId AND
        A.process_parameter_key = 'userId';

    SELECT CAST(A.process_parameter_value AS character varying(14)) INTO vDatetime
    FROM t_process_parameter A
    WHERE A.process_message_id = vProcessId AND
        A.process_parameter_key = 'datetime';

    /*
     * 1.update status doc in_inventory
     * 2.insert data ke in_log_product_balance_stock --> untuk barang yang keluar
     * 3.add saldo stok barang pinjaman
     * 4.add log saldo stok barang pinjaman
     * 5.update status item di in_balance_req_delivery_goods_borrowing jadi 'R'
     * 6.update status item di in_balance_req_delivery_goods_borrowing jadi 'F' kalo udah full qty nya
     */

    UPDATE in_inventory SET status_doc = vStatusRelease, update_datetime = vDatetime, update_user_id = vUserId
    WHERE inventory_id = vBorrowId;

    INSERT INTO in_log_product_balance_stock
    (tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
     product_id, product_balance_id, warehouse_id, product_status, base_uom_id, qty,
    "version", create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
        B.product_id, B.product_balance_id, A.warehouse_from_id, B.product_status, B.base_uom_id, SUM(B.qty_realization) * -1,
        0, vDatetime, vUserId, vDatetime, vUserId
    FROM in_inventory A, in_inventory_item B, m_warehouse_ou C
    WHERE A.inventory_id = vBorrowId AND
        A.inventory_id = B.inventory_id AND
        A.warehouse_from_id = C.warehouse_id
    GROUP BY A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
        B.product_id, B.product_balance_id, A.warehouse_from_id, B.product_status, B.base_uom_id;

    INSERT INTO in_borrowed_product_balance_stock
    (tenant_id, ou_id, partner_id, partner_ship_address_id,
    product_id, product_balance_id, product_status,
    base_uom_id, qty,
    "version", create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT A.tenant_id, A.ou_from_id, A.partner_id, A.partner_id,
        B.product_id, B.product_balance_id, B.product_status,
        B.base_uom_id, 0,
        0, vDatetime, vUserId, vDatetime, vUserId
    FROM in_inventory A, in_inventory_item B, in_inventory_borrow C
    WHERE A.inventory_id = vBorrowId AND
        A.inventory_id = B.inventory_id AND
        A.inventory_id = C.inventory_id AND
        NOT EXISTS (
            SELECT 1
            FROM in_borrowed_product_balance_stock Z
            WHERE A.tenant_id = Z.tenant_id AND
                A.ou_from_id = Z.ou_id AND
                A.partner_id = Z.partner_id AND
                A.partner_id = Z.partner_ship_address_id AND
                B.product_id = Z.product_id AND
                B.product_balance_id = Z.product_balance_id AND
                B.product_status = Z.product_status
        );

    UPDATE in_borrowed_product_balance_stock
        SET qty = in_borrowed_product_balance_stock.qty + B.qty_realization,
            version = in_borrowed_product_balance_stock.version + 1,
            update_datetime = vDateTime, update_user_id = vUserId
    FROM in_inventory A, in_inventory_item B, in_inventory_borrow C
    WHERE A.inventory_id = vBorrowId AND
        A.inventory_id = B.inventory_id AND
        A.inventory_id = C.inventory_id AND
        A.tenant_id = in_borrowed_product_balance_stock.tenant_id AND
        A.ou_from_id = in_borrowed_product_balance_stock.ou_id AND
        A.partner_id = in_borrowed_product_balance_stock.partner_id AND
        A.partner_id = in_borrowed_product_balance_stock.partner_ship_address_id AND
        B.product_id = in_borrowed_product_balance_stock.product_id AND
        B.product_balance_id = in_borrowed_product_balance_stock.product_balance_id AND
        B.product_status = in_borrowed_product_balance_stock.product_status;

    INSERT INTO in_log_borrowed_product_balance_stock
    (tenant_id, ou_id, doc_type_id, ref_id,
    doc_no, doc_date, partner_id, partner_ship_address_id,
    product_id, product_balance_id, product_status,
    base_uom_id, qty,
    "version", create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT A.tenant_id, A.ou_from_id, A.doc_type_id, A.inventory_id,
        A.doc_no, A.doc_date, C.partner_ship_to_id, C.partner_ship_address_id,
        B.product_id, B.product_balance_id, B.product_status,
        B.base_uom_id, B.qty_realization,
        0, vDatetime, vUserId, vDatetime, vUserId
    FROM in_inventory A, in_inventory_item B, in_inventory_borrow C
    WHERE A.inventory_id = vBorrowId AND
        A.inventory_id = B.inventory_id AND
        A.inventory_id = C.inventory_id;

    INSERT INTO in_inventory_borrow_balance_item
    (inventory_item_id, tenant_id, inventory_id,
    partner_id, partner_ship_address_id, ou_id,
    qty_do, qty_return, qty_sales, due_date, base_uom_id, status_item,
    "version", create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT B.inventory_item_id, A.tenant_id, A.inventory_id,
        C.partner_ship_to_id, C.partner_ship_address_id, A.ou_from_id,
        B.qty_realization, 0, 0, C.due_date, B.base_uom_id, vStatusRelease,
        0, vDatetime, vUserId, vDatetime, vUserId
    FROM in_inventory A, in_inventory_item B, in_inventory_borrow C
    WHERE A.inventory_id = vBorrowId AND
        A.inventory_id = B.inventory_id AND
        A.inventory_id = C.inventory_id;

    --5.update status item di in_balance_req_delivery_goods_borrowing
    UPDATE in_balance_req_delivery_goods_borrowing SET status_item = vStatusRelease, update_datetime = vDatetime, update_user_id = vUserId
    FROM in_inventory A
    JOIN in_inventory_item B ON a.inventory_id = b.inventory_id
    WHERE B.ref_id = in_balance_req_delivery_goods_borrowing.request_delivery_goods_borrowing_item_id
        AND A.ref_id = in_balance_req_delivery_goods_borrowing.request_delivery_goods_borrowing_id
        AND A.ref_doc_type_id = vReqDGBDocTypeId
        AND A.inventory_id = vBorrowId;

    --6.update status item di in_balance_req_delivery_goods_borrowing jadi 'F' kalo udah full qty nya
    UPDATE in_balance_req_delivery_goods_borrowing SET status_item = vStatusFinal
    FROM in_inventory A
    JOIN in_inventory_item B ON a.inventory_id = b.inventory_id
    WHERE B.ref_id = in_balance_req_delivery_goods_borrowing.request_delivery_goods_borrowing_item_id
        AND A.ref_id = in_balance_req_delivery_goods_borrowing.request_delivery_goods_borrowing_id
        AND A.ref_doc_type_id = vReqDGBDocTypeId
        AND A.inventory_id = vBorrowId
        AND in_balance_req_delivery_goods_borrowing.qty_request = in_balance_req_delivery_goods_borrowing.qty_realization;


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