pu_remove_approved_receive_goods_item_for_dlg

--function untuk remove item receive good yg sudah approved
--fitra 2018-04-26
CREATE OR REPLACE FUNCTION pu_remove_approved_receive_goods_item_for_dlg(character varying, bigint, character varying, character varying, bigint, character varying)
  RETURNS void AS
$BODY$
DECLARE
    pSessionId          ALIAS FOR $1;
    pTenantId           ALIAS FOR $2;
    pRgDocNo            ALIAS FOR $3;
    pProductCode        ALIAS FOR $4;
    pUserId             ALIAS for $5;
    pDatetime           ALIAS for $6;

    vDraft          character varying := 'D';
    vRelease            character varying := 'R';
    vFinal              character varying := 'F';
    vRgId               bigint;
    vRgItemId           bigint;
    vRgDocDate          character varying;
    vPurchLedgerCode    character varying := 'PURCH';
    vStatusLedgerDone   character varying := '1';
    vSpaceValue         character varying := ' ';
    vProductStatus      character varying(50);
    vRgDocTypeId        bigint;
    vOuId               bigint;
    vEmptyId            bigint := -99;

    vRtoNonOutletDocTypeId bigint := 538;

BEGIN
    DELETE FROM tt_pu_product_balance WHERE session_id = pSessionId;
    DELETE FROM tt_pu_product_balance_summary_stock WHERE session_id = pSessionId;

    -- mendapatkan product status code
    SELECT product_status_code INTO vProductStatus
    FROM m_product_status
    WHERE flg_buy = 'Y';

    SELECT receive_goods_id, doc_date, doc_type_id, ou_id INTO vRgId, vRgDocDate, vRgDocTypeId, vOuId
    FROM pu_receive_goods
    WHERE tenant_id = pTenantId AND
          doc_no = pRgDocNo AND
          status_doc = vRelease;

    --validasi status dokumen receive goods harus R
    IF NOT FOUND THEN
        RAISE EXCEPTION 'Document Receive Goods with doc no % is not found or document is on approval progress', pRgDocNo;
    END IF;

    --validasi belum tutup bulan purch
    IF EXISTS (SELECT 1 FROM m_admin_process_ledger e, m_ou_structure f, t_ou g
            WHERE e.tenant_id = pTenantId AND e.ou_id = f.ou_bu_id AND f.ou_id = vOuId AND
                e.date_year_month = SUBSTR(vRgDocDate, 1, 6) AND e.ledger_code = vPurchLedgerCode AND
                f.ou_id = g.ou_id AND e.status_ledger = vStatusLedgerDone) THEN
        RAISE EXCEPTION 'Admin Process Ledger for Purchasing in year month % is already closed', SUBSTR(vRgDocDate, 1, 6);
    END IF;

    -- validasi item belum dibuatkan claim note
    SELECT receive_goods_item_id INTO vRgItemId
    FROM pu_receive_goods_item
    WHERE receive_goods_id = vRgId AND f_get_product_code(product_id) = pProductCode;
    IF EXISTS (
                SELECT 1
                FROM in_balance_receive_goods_item
                WHERE receive_goods_item_id = vRgItemId AND
                      (qty_return <> 0 OR status_item <> 'R')
              )
    THEN
        RAISE EXCEPTION 'Receive Good Item with product code % Already claimed',pProductCode;
    END IF;

    -- validasi item belum dibuatkan invoice
    IF EXISTS (SELECT 1 FROM pu_po_balance_invoice WHERE ref_id = vRgId AND ref_item_id = vRgItemId AND flg_invoice <> 'N') THEN
        RAISE EXCEPTION 'Receive Good Item with product code % already invoiced', pProductCode;
    END IF;

    -- mendapatkan data untuk delete data balance, yang produknya punya serial numbernya
    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, F.product_balance_id, A.tenant_id, A.ou_id, B.product_id,
        C.serial_number, C.lot_number, C.product_expired_date, C.product_year_made,
        G.product_price_balance_id, A.doc_date, A.partner_id,
        A.doc_type_id, A.receive_goods_id, B.receive_goods_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 A
    INNER JOIN pu_receive_goods_item B ON A.receive_goods_id = B.receive_goods_id AND B.receive_goods_item_id = vRgItemId
    INNER JOIN pu_receive_goods_product C ON B.receive_goods_item_id = C.receive_goods_item_id
    INNER JOIN pu_po_item D ON B.ref_id = D.po_item_id
    INNER JOIN pu_po E ON D.po_id = E.po_id
    INNER JOIN in_product_balance F ON B.product_id = F.product_id AND C.serial_number = F.serial_number AND C.lot_number = F.lot_number
    INNER JOIN in_product_price_balance G ON A.ou_id = G.ou_id AND F.product_balance_id = G.product_balance_id AND A.partner_id = G.partner_id AND
        A.doc_type_id = G.doc_type_id AND B.receive_goods_item_id = G.ref_id AND A.doc_no = G.doc_no
        AND A.doc_date = G.doc_date AND B.product_id = G.product_id
    WHERE A.receive_goods_id = vRgId
    GROUP BY A.warehouse_id, F.product_balance_id, A.tenant_id, A.ou_id, B.product_id,
        C.serial_number, C.lot_number, C.product_expired_date, C.product_year_made, G.product_price_balance_id,
        A.doc_date, A.partner_id, A.doc_type_id, a.receive_goods_id, B.receive_goods_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 EXISTS (
                    SELECT 1
                    FROM tt_pu_product_balance A
                    WHERE A.session_id = pSessionId AND
                    sl_do_product.product_balance_id = A.product_balance_id ))
    THEN
        RAISE EXCEPTION 'Product code % already used in delivery order', pProductCode;
    END IF;

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



    -- DELETE in_product_balance_stock yg ada serial nya
    DELETE FROM in_product_balance_stock
    WHERE tenant_id = pTenantId
        AND EXISTS (SELECT 1
                    FROM tt_pu_product_balance A
                    WHERE A.session_id = pSessionId
                        AND A.flg_stock = 'Y'
                        AND in_product_balance_stock.product_balance_id = A.product_balance_id);

    -- DELETE in_product_balance  yg ada serial nya
    DELETE FROM in_product_balance A
    WHERE A.tenant_id = pTenantId
        AND EXISTS (SELECT 1
                    FROM tt_pu_product_balance B
                    WHERE B.session_id = pSessionId
                          AND A.product_balance_id = B.product_balance_id);


    -- mendapatkan data untuk delete data balance, yang produknya tidak punya serial numbernya
    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, F.product_balance_id, A.tenant_id, A.ou_id, B.product_id,
        vSpaceValue, vSpaceValue, vSpaceValue, vSpaceValue,
        G.product_price_balance_id, A.doc_date, A.partner_id,
        A.doc_type_id, A.receive_goods_id, B.receive_goods_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 A
    INNER JOIN pu_receive_goods_item B ON A.receive_goods_id = B.receive_goods_id AND B.receive_goods_item_id = vRgItemId
    INNER JOIN pu_po_item D ON B.ref_id = D.po_item_id
    INNER JOIN pu_po E ON D.po_id = E.po_id
    INNER JOIN in_product_balance F ON B.product_id = F.product_id AND F.serial_number = vSpaceValue AND F.lot_number = vSpaceValue
    INNER JOIN in_product_price_balance G ON A.ou_id = G.ou_id AND F.product_balance_id = G.product_balance_id AND A.partner_id = G.partner_id AND
        A.doc_type_id = G.doc_type_id AND B.receive_goods_item_id = G.ref_id AND A.doc_no = G.doc_no
        AND A.doc_date = G.doc_date AND B.product_id = G.product_id
    WHERE A.receive_goods_id = vRgId AND
        NOT EXISTS (SELECT 1 FROM pu_receive_goods_product C
                    WHERE B.receive_goods_item_id = C.receive_goods_item_id) AND
        NOT EXISTS (SELECT 1 FROM pu_receive_goods_product_auto_sn C
                    WHERE B.receive_goods_item_id = C.receive_goods_item_id)
    GROUP BY A.warehouse_id, F.product_balance_id, A.tenant_id, A.ou_id, B.product_id, G.product_price_balance_id,
        A.doc_date, A.partner_id, A.doc_type_id, A.receive_goods_id, B.receive_goods_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;

    -- menyiapkan data untuk update tabel 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;

    -- validasi QTY balance stock 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 - B.qty_int_rcv < 0 AND
                  A.warehouse_id = B.warehouse_id AND
                  A.tenant_id = B.tenant_id AND
                  A.product_id = B.product_id AND
                  A.product_status = vProductStatus )
    THEN
        RAISE EXCEPTION 'Qty product code % in balance product stock less than zero', pProductCode;
    END IF;

    -- UPDATE in_product_balance_stock
    UPDATE in_product_balance_stock
    SET qty = qty - A.qty_int_rcv,
        update_user_id = pUserId,
        update_datetime = pDatetime,
        version = version + 1
    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';

    -- UPDATE pu_po
    UPDATE pu_po SET
        status_doc = vRelease,
        update_user_id = pUserId,
        update_datetime = pDatetime,
        version = A.version + 1
    FROM pu_receive_goods A
    WHERE pu_po.po_id = A.ref_id AND
        pu_po.status_doc = vFinal AND
        A.receive_goods_id = vRgId
        ;

    -- DELETE pu_log_po_balance_item
    DELETE FROM  pu_log_po_balance_item
    WHERE tenant_id = pTenantId
        AND ref_id = vRgId
        AND ref_doc_type_id = vRgDocTypeId
        AND ref_item_id = vRgItemId;

    -- DELETE pu_po_balance_invoice
    DELETE FROM pu_po_balance_invoice
    WHERE tenant_id = pTenantId
        AND ref_id = vRgId
        AND ref_doc_type_id = vRgDocTypeId
        AND ref_item_id = vRgItemId;

    -- DELETE pu_po_balance_invoice_tax
    DELETE FROM pu_po_balance_invoice_tax
    WHERE tenant_id = pTenantId
        AND ref_id = vRgId
        AND ref_doc_type_id = vRgDocTypeId
        AND ref_item_id = vRgItemId;

    -- DELETE pu_receive_goods_product (dengan serial number yg di generate)
    DELETE FROM pu_receive_goods_product
    WHERE tenant_id = pTenantId
        AND EXISTS (SELECT 1
                    FROM pu_receive_goods A
                    INNER JOIN pu_receive_goods_item B ON A.receive_goods_id = B.receive_goods_id AND B.receive_goods_item_id = vRgItemId
                    INNER JOIN pu_receive_goods_product_auto_sn C ON B.receive_goods_item_id = C.receive_goods_item_id
                    WHERE A.receive_goods_id = vRgId AND
                          B.receive_goods_item_id = pu_receive_goods_product.receive_goods_item_id);

    -- DELETE in_product_price_balance
    DELETE FROM in_product_price_balance A
    WHERE A.tenant_id = pTenantId
        AND EXISTS (
            SELECT 1
            FROM tt_pu_product_balance B
            WHERE B.session_id = pSessionId
                AND A.product_price_balance_id = B.product_price_balance_id);

    -- DELETE in_product_price_balance_stock
    DELETE FROM in_product_price_balance_stock A
    WHERE A.tenant_id = pTenantId
        AND EXISTS (
            SELECT 1
            FROM tt_pu_product_balance B
            WHERE B.session_id = pSessionId
                AND A.warehouse_id = B.warehouse_id
                AND A.product_id = B.product_id
                AND A.product_balance_id = B.product_balance_id
                AND A.product_price_balance_id = B.product_price_balance_id
                AND A.product_status = vProductStatus
                AND B.flg_stock = 'Y');

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

    -- DELETE in_log_product_price_balance_stock
    DELETE FROM in_log_product_price_balance_stock A
    WHERE A.tenant_id = pTenantId
        AND EXISTS (SELECT 1
                    FROM tt_pu_product_balance B
                    WHERE B.session_id = pSessionId
                    AND A.ou_id = B.ou_id
                    AND A.doc_type_id = B.doc_type_id
                    AND A.ref_id = B.ref_id
                    AND A.doc_no = B.doc_no
                    AND A.doc_date = B.doc_date
                    AND A.warehouse_id = B.warehouse_id
                    AND A.product_id = B.product_id
                    AND A.product_balance_id = B.product_balance_id
                    AND A.product_price_balance_id = B.product_price_balance_id
                    AND B.flg_stock = 'Y');

    -- DELETE in_balance_receive_goods_item
    DELETE FROM in_balance_receive_goods_item
    WHERE tenant_id = pTenantId
        AND receive_goods_id = vRgId
        AND receive_goods_item_id =vRgItemId;


    --Update Balance item RTO Non Outlet -- RG dari PO
    UPDATE in_req_trf_out_po_balance_item A SET
        qty_rcv_int = A.qty_rcv_int - B.qty_rcv_int,
        update_user_id = pUserId,
        update_datetime = pDatetime,
        version = A.version + 1
    FROM pu_receive_goods_item B
    INNER JOIN pu_po_item C ON B.ref_id = C.po_item_id AND C.ref_doc_type_id = vRtoNonOutletDocTypeId
    WHERE A.inventory_item_id = C.ref_id AND
          B.receive_goods_id = vRgId AND B.receive_goods_item_id = vRgItemId;


    -- UPDATE gl_journal_trx_mapping
    UPDATE gl_journal_trx_mapping A
    SET amount = A.amount - B.amount,
        update_user_id = pUserId,
        update_datetime = pDatetime,
        version = A.version + 1
    FROM gl_journal_trx_item B
    INNER JOIN gl_journal_trx C ON C.journal_trx_id = B.journal_trx_id
    WHERE A.journal_trx_id = B.journal_trx_id
        AND C.doc_type_id = vRgDocTypeId
        AND C.doc_id = vRgId
        AND B.ref_id = vRgItemId;

    -- DELETE gl_journal_trx_item
    DELETE FROM gl_journal_trx_item A
    WHERE A.ref_id = vRgItemId AND
          EXISTS( SELECT 1 FROM gl_journal_trx B
                  WHERE A.journal_trx_id  = B.journal_trx_id
                        AND B.doc_type_id = vRgDocTypeId
                        AND B.doc_id = vRgId
                );

    -- UPDATE gl_journal_trx
    UPDATE gl_journal_trx
    SET status_doc = vDraft,
        update_user_id = pUserId,
        update_datetime = pDatetime,
        version = version + 1
    WHERE doc_type_id = vRgDocTypeId AND
         doc_id = vRgId;

---------------------------------PROCESS DELETE ITEM-----------------------------------------------
    --update pu_po_balance_item
    UPDATE pu_po_balance_item A
    SET qty_rcv = A.qty_rcv - B.qty_rcv_po,
        qty_int_rcv = A.qty_int_rcv - B.qty_rcv_int,
        status_item = vRelease,
        update_user_id = pUserId,
        update_datetime = pDatetime,
        version = A.version + 1
    FROM pu_receive_goods_item B
    WHERE A.po_item_id = b.ref_id
        AND b.receive_goods_item_id = vRgItemId;

    --delete pu_receive_goods_item
    DELETE FROM pu_receive_goods_item
    WHERE receive_goods_item_id = vRgItemId;

    --delete pu_receive_goods_item_additional_for_dlg
    DELETE FROM pu_receive_goods_item_additional_for_dlg
    WHERE receive_goods_item_id = vRgItemId;

    DELETE FROM tt_pu_product_balance WHERE session_id = pSessionId;
    DELETE FROM tt_pu_product_balance_summary_stock WHERE session_id = pSessionId;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
/