CANCEL APPROVE GOODS RECEIVE CONSIGNMENT

-- Adrian, Mar 17, 2017

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

    vStatusDocRelease       character varying := 'R';
    vStatusDocDraft         character varying := 'D';
    vStatusDocInProgress    character varying := 'I';
    vStatusDocFinal         character varying := 'F';
    vWorkflowStatusDraft    character varying := 'DRAFT';

    vDocTypeId              bigint;
    vOuId                   bigint;
    vDocNo                  character varying;
    vScheme                 character varying;
    vStatusDoc              character varying;
    vPoId                   bigint;

    vEmptyId                bigint := -99;
    vEmptyValue                 character varying := ' ';
    vInProgress             character varying := 'I';
    vYes                    character varying := 'Y';
    vDocDate                character varying ;
    vStatusLedgerNotDone    character varying := '0';
    vProductStatus          character varying(50);

    vFunctionSubmit         character varying := 'pu_submit_gr_consignment';
    vOutletId               bigint;
    vOuWarehouseId          bigint;
    vDbVersion              character varying(10);
    vUnfinishedItem         bigint;

BEGIN

    SELECT product_status_code INTO vProductStatus
    FROM m_product_status
    WHERE flg_buy = 'Y';

    --RAISE EXCEPTION 'Function Cancel submit for Goods Receive Consignment is not yet Created';

    /**
     * 1. Cek document
     * 2. If doc exists and status doc = R
     */
    SELECT A.ou_id, A.doc_date, A.doc_type_id, A.doc_no, A.status_doc, A.ref_id, B.scheme
    INTO vOuId, vDocDate, vDocTypeId, vDocNo, vStatusDoc, vPoId, vScheme
    FROM pu_receive_goods_consignment A
    INNER JOIN m_document B ON A.doc_type_id = B.doc_type_id
    WHERE A.receive_goods_consignment_id = pRgId
        AND A.status_doc = vStatusDocRelease;

    IF FOUND THEN

        -- check document already used in claim note
        IF EXISTS (
            SELECT 1
            FROM in_balance_receive_goods_consignment_item
            WHERE receive_goods_consignment_id = pRgId
                AND po_id = vPoId
                AND (qty_return <> 0 OR status_item <> 'R')
            ) THEN
                RAISE EXCEPTION 'Purchase Order already claim';
        ELSE

            /**
             * validasi tidak sedang digunakan oleh dokumen lain
             */
            IF NOT EXISTS (
                SELECT (1)
                FROM pu_po_balance_item_consignment A, pu_receive_goods_consignment_item B
                WHERE A.po_item_id = B.ref_id
                    AND A.tenant_id = B.tenant_id
                    AND B.receive_goods_consignment_id = pRgId
                    AND A.status_item <> vStatusDocRelease
            ) THEN

                /*
                 * 1. delete pu_log_po_balance_item_consignment
                 * 4. delete in_product_balance
                 * 6. delete/update in_product_balance_stock
                 * 7. delete in_log_product_consignment_balance_stock
                 * 9. update status pu_receive_goods_consignment
                 * 10.update status pu_po_balance_item_consignment
                 * 10b.update status pu_po
                 * 11.delete balance receive goods item
                 */

                -- 1. delete pu_log_po_balance_item_consignment
                DELETE FROM pu_log_po_balance_item_consignment Z
                WHERE EXISTS (
                    SELECT (1)
                    FROM pu_receive_goods_consignment A, pu_receive_goods_consignment_item B, pu_po_item C
                    WHERE Z.tenant_id = A.tenant_id
                        AND Z.po_id = C.po_id
                        AND Z.po_item_id = C.po_item_id
                        AND Z.ref_doc_type_id = A.doc_type_id
                        AND Z.ref_id = pRgId
                        AND Z.ref_item_id = B.receive_goods_consignment_item_id
                        AND Z.qty_trx = B.qty_rcv_po * -1
                        AND Z.trx_uom_id = B.po_uom_id
                        AND Z.qty_int = B.qty_rcv_int * -1
                        AND Z.base_uom_id = B.base_uom_id
                        AND Z.remark = B.remark
                        AND A.receive_goods_consignment_id = pRgId
                        AND A.receive_goods_consignment_id = B.receive_goods_consignment_id
                        AND B.ref_id = C.po_item_id
                );

                -- Insert untuk data product yang memiliki serial number dan serial number tidak di-generate
                INSERT INTO tt_pu_product_balance
                (session_id, warehouse_id, product_balance_id, tenant_id, ou_id, product_id,
                serial_number, lot_number, product_expired_date, product_year_made,
                product_price_balance_id, product_buy_date, partner_id,
                doc_type_id, ref_id, ref_item_id, doc_no, doc_date,
                po_id, po_no, po_date, po_item_id,
                curr_code, price, qty_rcv, po_uom_id, qty_int_rcv, base_uom_id, flg_stock)
                SELECT pSessionId, A.warehouse_id, vEmptyId, A.tenant_id, A.ou_id, B.product_id,
                    C.serial_number, C.lot_number, C.product_expired_date, C.product_year_made,
                    vEmptyId, A.doc_date, A.partner_id,
                    A.doc_type_id, A.receive_goods_consignment_id, B.receive_goods_consignment_item_id, A.doc_no, A.doc_date,
                    E.po_id, E.doc_no, E.doc_date, D.po_item_id,
                    D.curr_code, D.gross_price_po - D.discount_amount, SUM(C.qty_rcv_po), D.po_uom_id, SUM(C.qty_rcv_int), D.base_uom_id, D.flg_stock
                FROM pu_receive_goods_consignment A, pu_receive_goods_consignment_item B, pu_receive_goods_consignment_product C, pu_po_item D, pu_po E
                WHERE A.receive_goods_consignment_id = pRgId AND
                    A.receive_goods_consignment_id = B.receive_goods_consignment_id AND
                    B.receive_goods_consignment_item_id = C.receive_goods_consignment_item_id AND
                    B.ref_id = D.po_item_id AND
                    D.po_id = E.po_id
                GROUP BY A.warehouse_id, A.tenant_id, A.ou_id, B.product_id,
                    C.serial_number, C.lot_number, C.product_expired_date, C.product_year_made,
                    A.doc_date, A.partner_id, A.doc_type_id, a.receive_goods_consignment_id, B.receive_goods_consignment_item_id, A.doc_no,
                    E.po_id, E.doc_no, E.doc_date, D.po_item_id,
                    D.curr_code, D.nett_price_po, D.po_uom_id, D.base_uom_id, D.flg_stock;

                -- validasi item tidak digunakan pada DO, GTO atau Adjs Stock
                IF EXISTS (
                    SELECT 1
                    FROM sl_do_product
                    WHERE product_balance_id IN (
                        SELECT A.product_balance_id
                        FROM tt_pu_product_balance A
                        WHERE A.session_id = pSessionId
                    )
                )
                THEN
                    RAISE EXCEPTION 'Product already used in delivery order';
                END IF;

                IF EXISTS (
                    SELECT 1
                    FROM in_inventory_item
                    WHERE product_balance_id IN (
                        SELECT A.product_balance_id
                        FROM tt_pu_product_balance A
                        WHERE A.session_id = pSessionId
                    )
                )
                THEN
                    RAISE EXCEPTION 'Product already used in GTO or Adjustment Stock';
                END IF;

                /*
                 * update product_balance_id dari in_product_balance
                 */
                UPDATE tt_pu_product_balance SET product_balance_id = A.product_balance_id
                FROM in_product_balance A
                WHERE tt_pu_product_balance.session_id = pSessionId AND
                    tt_pu_product_balance.tenant_id = A.tenant_id AND
                    tt_pu_product_balance.product_id = A.product_id AND
                    tt_pu_product_balance.serial_number = A.serial_number AND
                    tt_pu_product_balance.lot_number = A.lot_number;

                -- 4. delete in_product_balance
                DELETE FROM in_product_balance A
                WHERE A.tenant_id = pTenantId
                    AND product_balance_id IN (
                        SELECT A.product_balance_id
                        FROM tt_pu_product_balance A
                        WHERE A.session_id = pSessionId
                    );

                -- 6. delete/update in_product_balance_stock
                DELETE FROM in_product_balance_stock
                WHERE tenant_id = pTenantId
                    AND product_balance_id IN (
                        SELECT A.product_balance_id
                        FROM tt_pu_product_balance A
                        WHERE A.session_id = pSessionId
                            AND A.flg_stock = 'Y'
                    );

                -- Insert untuk data product yang tidak memiliki serial number
                INSERT INTO tt_pu_product_balance
                    (session_id, warehouse_id, product_balance_id, tenant_id, ou_id, product_id,
                    serial_number, lot_number, product_expired_date, product_year_made,
                    product_price_balance_id, product_buy_date, partner_id,
                    doc_type_id, ref_id, ref_item_id, doc_no, doc_date,
                    po_id, po_no, po_date, po_item_id,
                    curr_code, price, qty_rcv, po_uom_id, qty_int_rcv, base_uom_id, flg_stock)
                SELECT pSessionId, A.warehouse_id, vEmptyId, A.tenant_id, A.ou_id, B.product_id,
                    vEmptyValue, vEmptyValue, vEmptyValue, vEmptyValue,
                    vEmptyId, A.doc_date, A.partner_id,
                    A.doc_type_id, A.receive_goods_consignment_id, B.receive_goods_consignment_item_id, A.doc_no, A.doc_date,
                    E.po_id, E.doc_no, E.doc_date, D.po_item_id,
                    D.curr_code, D.gross_price_po - D.discount_amount, SUM(B.qty_rcv_po), D.po_uom_id, SUM(B.qty_rcv_int), D.base_uom_id, D.flg_stock
                FROM pu_receive_goods_consignment A, pu_receive_goods_consignment_item B, pu_po_item D, pu_po E
                WHERE A.receive_goods_consignment_id = pRgId AND
                    A.receive_goods_consignment_id = B.receive_goods_consignment_id AND
                    NOT EXISTS (SELECT 1 FROM pu_receive_goods_consignment_product C
                                WHERE B.receive_goods_consignment_item_id = C.receive_goods_consignment_item_id) AND
                    NOT EXISTS (SELECT 1 FROM pu_receive_goods_consignment_product_auto_sn C
                                WHERE B.receive_goods_consignment_item_id = C.receive_goods_consignment_item_id) AND
                    B.ref_id = D.po_item_id AND
                    D.po_id = E.po_id
                GROUP BY A.warehouse_id, A.tenant_id, A.ou_id, B.product_id,
                    A.doc_date, A.partner_id, A.doc_type_id, A.receive_goods_consignment_id, B.receive_goods_consignment_item_id, A.doc_no,
                    E.po_id, E.doc_no, E.doc_date, D.po_item_id,
                    D.curr_code, D.nett_price_po, D.po_uom_id, D.base_uom_id, D.flg_stock;

                /*
                 * update product_balance_id dari in_product_balance
                 */
                UPDATE tt_pu_product_balance SET product_balance_id = A.product_balance_id
                FROM in_product_balance A
                WHERE tt_pu_product_balance.session_id = pSessionId AND
                    tt_pu_product_balance.tenant_id = A.tenant_id AND
                    tt_pu_product_balance.product_id = A.product_id AND
                    tt_pu_product_balance.serial_number = A.serial_number AND
                    tt_pu_product_balance.lot_number = A.lot_number;

                /*
                 * create summary dari tt_pu_product_balance untuk update yg sudah ada di in_product_balance_stock
                 */
                INSERT INTO tt_pu_product_balance_summary_stock
                        (session_id, warehouse_id, product_balance_id, tenant_id, product_id,
                        product_price_balance_id, qty_rcv, po_uom_id, qty_int_rcv, base_uom_id, flg_stock)
                SELECT  pSessionId, A.warehouse_id, A.product_balance_id, A.tenant_id, A.product_id,
                        vEmptyId, SUM(A.qty_rcv), A.po_uom_id, SUM(A.qty_int_rcv), A.base_uom_id, A.flg_stock
                FROM    tt_pu_product_balance A
                WHERE   A.session_id = pSessionId
                GROUP BY A.warehouse_id, A.product_balance_id, A.tenant_id, A.product_id,
                         A.po_uom_id, A.base_uom_id, A.flg_stock;

                -- 6. delete/update in_product_balance_stock
                UPDATE in_product_balance_stock
                SET qty = qty - A.qty_int_rcv,
                    update_datetime = pDatetime,
                    update_user_id = pUserId
                FROM tt_pu_product_balance_summary_stock A
                WHERE A.session_id = pSessionId AND
                    in_product_balance_stock.product_id = A.product_id AND
                    in_product_balance_stock.tenant_id = A.tenant_id AND
                    in_product_balance_stock.warehouse_id = A.warehouse_id AND
                    in_product_balance_stock.product_balance_id = A.product_balance_id AND
                    in_product_balance_stock.product_status = vProductStatus AND
                    A.flg_stock = 'Y';

                -- QTY tidak boleh < 0
                IF EXISTS (
                    SELECT 1
                    FROM in_product_balance_stock A
                    INNER JOIN tt_pu_product_balance B ON A.product_balance_id = B.product_balance_id
                    WHERE B.session_id = pSessionId
                        AND A.qty < 0
                )
                THEN
                    RAISE EXCEPTION 'Qty product in stock product less than zero';
                END IF;

                -- 7. delete in_log_product_consignment_balance_stock
                DELETE FROM in_log_product_consignment_balance_stock Z
                WHERE EXISTS (
                    SELECT (1)
                    FROM tt_pu_product_balance A
                    WHERE Z.tenant_id = A.tenant_id
                        AND Z.ou_id = A.ou_id
                        AND Z.doc_type_id = A.doc_type_id
                        AND Z.ref_id = A.ref_id
                        AND Z.doc_no = A.doc_no
                        AND Z.doc_date = A.doc_date
                        AND Z.warehouse_id = A.warehouse_id
                        AND Z.product_id = A.product_id
                        AND Z.product_balance_id = A.product_balance_id
                        AND Z.product_status = vProductStatus
                        AND A.session_id = pSessionId
                        AND A.flg_stock = 'Y'
                );

                DELETE FROM in_balance_receive_goods_consignment_item
                WHERE tenant_id = pTenantId
                    AND receive_goods_consignment_item_id IN (
                        SELECT A.ref_item_id
                        FROM tt_pu_product_balance A
                        WHERE A.session_id = pSessionId
                    );

                -- 9. update status pu_receive_goods_consignment
                UPDATE pu_receive_goods_consignment
                SET status_doc = vStatusDocDraft,
                    workflow_status = vWorkflowStatusDraft,
                    update_datetime = pDatetime,
                    update_user_id = pUserId
                WHERE receive_goods_consignment_id = pRgId
                    AND status_doc = vStatusDocRelease;

                -- 10.update status pu_po_balance_item_consignment
                UPDATE pu_po_balance_item_consignment
                SET status_item = vStatusDocInProgress,
                    update_datetime = pDatetime,
                    update_user_id = pUserId
                FROM pu_receive_goods_consignment_item A
                WHERE pu_po_balance_item_consignment.po_item_id = A.ref_id AND
                    pu_po_balance_item_consignment.tenant_id = A.tenant_id AND
                    A.receive_goods_consignment_id = pRgId;

                -- do reset approval to draft
                PERFORM f_reset_approval_to_draft(pTenantId, pSessionId, vScheme, pRgId, vDocNo, pDatetime, pRemarkApproval);

                -- update previous process no
                UPDATE t_process_message
                SET process_no = pPrevProcessNo,
                    update_datetime = pDatetime,
                    update_user_id = pUserId,
                    version = version + 1
                WHERE tenant_id = pTenantId
                    AND process_name = vFunctionSubmit
                    AND process_no = pRgId || '_' || vDocNo;


            ELSE
                RAISE EXCEPTION 'Item in purchase order with id % is already used in other document', vPoId;
            END IF;

        END IF;

    ELSE
        RAISE EXCEPTION 'Document with id % is not found or document is on approval progress', pRgId;
    END IF;

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