-- Function: pu_create_sn_receive_goods(bigint, character varying, character varying, bigint, bigint, character varying)

-- DROP FUNCTION pu_create_sn_receive_goods(bigint, character varying, character varying, bigint, bigint, character varying);

CREATE OR REPLACE FUNCTION pu_create_sn_receive_goods(bigint, character varying, character varying, bigint, bigint, character varying)
  RETURNS void AS
$BODY$
DECLARE
    pTenantId           ALIAS FOR $1;
    pSessionId          ALIAS FOR $2;
    pDocDate            ALIAS FOR $3;
    pRgId               ALIAS FOR $4;
    pUserId             ALIAS FOR $5;
    pDatetime           ALIAS FOR $6;

    vEmptyId                bigint;
    vEmptyValue             character varying(1);
    vProductStatus          character varying(50);

    vCursorProduct          tt_pu_receive_goods_product_auto_sn%ROWTYPE;
    vCountProduct           integer;
    vStartSeq               bigint;
    vEndSeq                 bigint;
    vQtyProduct             bigint;
    vRgProductAutoSnId      bigint;
    vTotalQtyProduct        bigint;
BEGIN

    vCountProduct := 0;
    vEmptyId := -99;
    vEmptyValue := ' ';

    /*
     * NK, 31 Des 2014
     * diganti dng membaca m_product_status
     */
    --vProductStatus := 'GOOD';
    SELECT product_status_code INTO vProductStatus
    FROM m_product_status
    WHERE flg_buy = 'Y';

    DELETE FROM tt_pu_receive_goods_product_auto_sn WHERE session_id = pSessionId;
    DELETE FROM tt_pu_receive_goods_product WHERE session_id = pSessionId;

    INSERT INTO tt_pu_receive_goods_product_auto_sn
    (session_id, receive_goods_product_auto_sn_id, tenant_id, receive_goods_id, receive_goods_item_id,
    line_no, product_id, product_expired_date, product_year_made,
    lot_number, qty_rcv_po, po_uom_id, qty_rcv_int, base_uom_id)
    SELECT pSessionId, A.receive_goods_product_auto_sn_id, A.tenant_id, B.receive_goods_id, A.receive_goods_item_id,
        A.line_no, A.product_id, A.product_expired_date, A.product_year_made,
        A.lot_number, A.qty_rcv_po, A.po_uom_id, A.qty_rcv_int, A.base_uom_id
    FROM pu_receive_goods_product_auto_sn A, pu_receive_goods_item B, pu_receive_goods C
    WHERE C.receive_goods_id = pRgId AND
        C.receive_goods_id = B.receive_goods_id AND
        B.receive_goods_item_id = A.receive_goods_item_id;

    SELECT COUNT(*) INTO vCountProduct
    FROM tt_pu_receive_goods_product_auto_sn
    WHERE session_id = pSessionId;

    IF vCountProduct > 0 THEN

        SELECT SUM(qty_rcv_int) INTO vTotalQtyProduct
        FROM tt_pu_receive_goods_product_auto_sn
        WHERE session_id = pSessionId;

        vStartSeq := NEXTVAL ('product_sn_seq') - 1;
        PERFORM SETVAL('product_sn_seq', vTotalQtyProduct + vStartSeq);

        FOR vCursorProduct IN SELECT * FROM tt_pu_receive_goods_product_auto_sn
                            WHERE session_id = pSessionId
                                    ORDER BY line_no
        LOOP
            vRgProductAutoSnId := vCursorProduct.receive_goods_product_auto_sn_id;
            vQtyProduct := vCursorProduct.qty_rcv_int::bigint;

            INSERT INTO tt_pu_receive_goods_product
            (session_id, tenant_id, receive_goods_item_id,
            product_id, serial_number, product_expired_date, product_year_made,
            lot_number, qty_rcv_po, po_uom_id, qty_rcv_int, base_uom_id, remark)
            SELECT A.session_id, A.tenant_id, A.receive_goods_item_id,
                A.product_id, pDocDate || right('000000000'||s.a, 9), A.product_expired_date, A.product_year_made,
                A.lot_number, A.qty_rcv_po/A.qty_rcv_int, A.po_uom_id, 1, A.base_uom_id, A.remark
            FROM tt_pu_receive_goods_product_auto_sn A, generate_series(vStartSeq,vStartSeq+vQtyProduct-1) AS s(a)
            WHERE A.session_id = pSessionId AND
                A.receive_goods_product_auto_sn_id = vRgProductAutoSnId;

            vStartSeq := vStartSeq + vQtyProduct;

          END LOOP;

    END IF;

    INSERT INTO pu_receive_goods_product
    (tenant_id, receive_goods_item_id, line_no, product_id,
    serial_number, product_expired_date, product_year_made,
    lot_number, qty_rcv_po, po_uom_id, qty_rcv_int, base_uom_id, remark,
    "version", create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT tenant_id, receive_goods_item_id, ROW_NUMBER() OVER ( PARTITION BY receive_goods_item_id), product_id,
        serial_number, product_expired_date, product_year_made,
        lot_number, qty_rcv_po, po_uom_id, qty_rcv_int, base_uom_id, remark,
        0, pDatetime, pUserId, pDatetime, pUserId
    FROM tt_pu_receive_goods_product
    WHERE session_id = pSessionId;

    DELETE FROM tt_pu_receive_goods_product_auto_sn WHERE session_id = pSessionId;
    DELETE FROM tt_pu_receive_goods_product WHERE session_id = pSessionId;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION pu_create_sn_receive_goods(bigint, character varying, character varying, bigint, bigint, character varying)
  OWNER TO postgres;