in_cancel_submit_return_note

/*
 * PS, 23 April 2015
 */
--Modified by Adrian, Jul 4, 2017, mengubah query hapus in_log_product_balance_stock

CREATE OR REPLACE FUNCTION in_cancel_submit_return_note(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; -- for update user id in transaction
    pDatetime               ALIAS FOR $4;
    pReturnNoteId           ALIAS FOR $5; -- Inventory id
    pRemarkApproval         ALIAS FOR $6; -- Remark for appear in approval reset history
    pPrevProcessNo          ALIAS FOR $7; -- Substitute for renaming prevous process no

    vReturnNoteDocTypeId    bigint;
    vFlgInvoice             character varying;
    vStatusDraft            character varying;
    vStatusRelease          character varying;
    vStatusInProgress       character varying;
    vWorkflowStatusDraft    character varying;
    vOuId                   bigint;
    vWarehouseId            bigint;
    vProductId              bigint;
    vProductBalanceId       bigint;
    vProductStatus          character varying;
    vDocNo                  character varying;
    vDocDate                character varying;
    result                  RECORD;

    vJournalTrxId           bigint;
    vScheme                 character varying;
    vDocJournal             DOC_JOURNAL%ROWTYPE;
    vOuStructure            OU_BU_STRUCTURE%ROWTYPE;
    vFunctionSubmit         character varying;
BEGIN
    vReturnNoteDocTypeId := 502;
    vStatusDraft := 'D';
    vStatusRelease := 'R';
    vStatusInProgress := 'I';
    vWorkflowStatusDraft := 'DRAFT';
    vFunctionSubmit := 'in_submit_return_note';

    /*
     * 1.  Ubah status_doc D
     * 2.  Create summary dari in_inventory_item untuk update yg sudah ada di in_product_balance_stock (sama)
     * X 3.  Update product_balance_stock (+ jadi -) X
     * 4.  Delete data from in_product_balance_stock
     * 5.  Delete data from in_log_product_balance_stock
     * 6.  Insert data temporer tt_in_so_balance_item
     * 7.  Update qty return di data sl_so_balance_item /->*, *->/
     * 8.  Hapus data sl_log_so_balance_item
     * 9.  Hapus data sl_so_balance_invoice (do_receipt_item_id ?????)
     * 10. Hapus data sl_so_balance_invoice_tax (do_receipt_item_id ????)
     * 11. PERFORM cancel gl_cancel_admin_journal_trx
     * 12. DELETE gl_journal_trx
     * 13. DELETE gl_journal_trx_item
     * 14. DELETE gl_journal_trx_mapping
     * 15. PERFORM f_reset_approval_to_draft
     * 16. UPDATE t_process_message
     */

    -- get data
    select f_get_ou_bu_structure(A.ou_to_id) AS ou,A.ou_to_id, A.warehouse_from_id, A.doc_no, A.doc_date, B.product_id,
           B.product_balance_id, B.product_status, f_get_document_journal(A.doc_type_id) as doc, C.scheme
    FROM in_inventory A, in_inventory_item B, m_document C
    WHERE A.inventory_id = B.inventory_id AND
        A.doc_type_id = C.doc_type_id AND
        A.inventory_id = pReturnNoteId INTO result;

    vOuStructure := result.ou;
    vOuId := result.ou_to_id;
    vWarehouseId := result.warehouse_from_id;
    vDocNo := result.doc_no;
    vDocDate := result.doc_date;
    vProductId := result.product_id;
    vProductBalanceId := result.product_balance_id;
    vProductStatus := result.product_status;
    vDocJournal := result.doc;
    vScheme := result.scheme;

    SELECT flg_invoice INTO vFlgInvoice
    FROM sl_so_balance_invoice
    WHERE ref_id = pReturnNoteId
    AND ref_doc_type_id = vReturnNoteDocTypeId;

    IF FOUND AND vFlgInvoice IN ('Y','I') THEN
        RAISE EXCEPTION 'Return Note document already been used by Return Sales Invoice';
    END IF;

    -- 1. ubah status_doc D
    UPDATE in_inventory SET status_doc = vStatusDraft, workflow_status = vWorkflowStatusDraft, version = version + 1, update_datetime = pDatetime, update_user_id = pUserId
    WHERE inventory_id = pReturnNoteId
        AND status_doc = vStatusRelease;

    /*
     * 2. create summary dari in_inventory_item untuk update yg sudah ada di in_product_balance_stock
     */
    INSERT INTO tt_in_product_balance_summary_stock
            (session_id, tenant_id, inventory_id, warehouse_id, product_id, product_balance_id, product_status, base_uom_id, qty)
    SELECT  pSessionId, A.tenant_id, A.inventory_id, A.warehouse_from_id, B.product_id, B.product_balance_id, B.product_status,
            B.base_uom_id, SUM(B.qty_realization)
    FROM    in_inventory A, in_inventory_item B
    WHERE   A.inventory_id = B.inventory_id
    AND     A.inventory_id = pReturnNoteId
    GROUP BY A.tenant_id, A.inventory_id, A.warehouse_from_id, B.product_id, B.product_balance_id, B.product_status, B.base_uom_id;

    /*
     * 3. update product_balance_stock
     */
    UPDATE in_product_balance_stock SET qty = in_product_balance_stock.qty - A.qty, update_datetime = pDatetime, update_user_id = pUserId,
        version = version + 1
    FROM tt_in_product_balance_summary_stock A
    WHERE A.session_id = pSessionId AND
        A.inventory_id = pReturnNoteId 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_id = A.product_id AND
        in_product_balance_stock.product_balance_id = A.product_balance_id AND
        in_product_balance_stock.product_status = A.product_status;

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

    /*
     * 4. delete data from in_product_balance_stock
     */
--  DELETE FROM in_product_balance_stock
--          WHERE tenant_id = pTenantId AND
--                warehouse_id = vWarehouseId AND
--                product_id = vProductId AND
--                product_balance_id = vProductBalanceId AND
--                product_status = vProductStatus;

    /*
     * 5. delete data in_log_product_balance_stock
     */
    /*DELETE FROM in_log_product_balance_stock
    WHERE tenant_id = pTenantId AND
          ou_id = vOuId AND
          doc_type_id = vReturnNoteDocTypeId AND
          ref_id = pReturnNoteId AND
          doc_no = vDocNo AND
          doc_date = vDocDate AND
          warehouse_id = vWarehouseId AND
          product_id = vProductId AND
          product_balance_id = vProductBalanceId AND
          product_status = vProductStatus;*/
    WITH grouped_in_inventory_item AS (
        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_to_id, B.product_status, B.base_uom_id, SUM(B.qty_realization) AS qty
        FROM in_inventory A, in_inventory_item B, m_warehouse_ou C
        WHERE A.inventory_id = pReturnNoteId AND
            A.inventory_id = B.inventory_id AND
            A.warehouse_to_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_to_id, B.product_status, B.base_uom_id
    )
    DELETE
    FROM in_log_product_balance_stock Z
    WHERE EXISTS (
        SELECT (1)
        FROM grouped_in_inventory_item 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.inventory_id
            AND Z.doc_no = A.doc_no
            AND Z.doc_date = A.doc_date
            AND Z.partner_id = A.partner_id
            AND Z.product_id = A.product_id
            AND Z.product_balance_id = A.product_balance_id
            AND Z.warehouse_id = A.warehouse_to_id
            AND Z.product_status = A.product_status
            AND Z.base_uom_id = A.base_uom_id
            AND Z.qty = A.qty
    );

    /*
     * 6.insert data temporer tt_in_so_balance_item
     */
    INSERT INTO tt_in_so_balance_item
    (session_id, tenant_id, ou_id, doc_type_id,
    doc_no, doc_date, inventory_id, partner_id,
    inventory_item_id, so_id, do_id, do_item_id,
    qty_return, base_uom_id, remark,
    so_item_id, curr_code, price,
    flg_tax_amount, qty_so, qty_int_so,
    so_uom_id, tax_id, tax_percentage)
    SELECT pSessionId, A.tenant_id, A.ou_from_id, A.doc_type_id,
            A.doc_no, A.doc_date, A.inventory_id, E.partner_bill_to_id,
            B.inventory_item_id, A.ref_id, B.ref_id, B.ref_item_id,
            SUM(B.qty_realization), B.base_uom_id, A.remark,
            D.so_item_id, D.curr_code, (D.gross_sell_price - D.discount_amount),
            D.flg_tax_amount, D.qty_so, D.qty_int,
            D.so_uom_id, D.tax_id, D.tax_percentage
    FROM in_inventory A, in_inventory_item B, in_balance_do_item C, sl_so_item D, sl_so E
    WHERE A.inventory_id = pReturnNoteId AND
          A.inventory_id = B.inventory_id AND
          B.ref_item_id = C.do_item_id AND
          C.so_item_id = D.so_item_id AND
          D.so_id = E.so_id
    GROUP BY A.tenant_id, A.ou_from_id, A.doc_type_id,
            A.doc_no, A.doc_date, A.inventory_id, E.partner_bill_to_id,
            B.inventory_item_id, A.ref_id, B.ref_id, B.ref_item_id,
            B.base_uom_id, D.so_item_id, D.curr_code, D.nett_sell_price, D.qty_so, D.qty_int, D.so_uom_id, D.tax_id ;

    /*
     * 7. update qty return di data sl_so_balance_item
     */
    UPDATE sl_so_balance_item SET qty_return = sl_so_balance_item.qty_return - ((A.qty_return * A.qty_int_so) / A.qty_so), qty_return_int = sl_so_balance_item.qty_return_int - A.qty_return, update_datetime = pDatetime, update_user_id = pUserId
    FROM tt_in_so_balance_item A
    WHERE A.session_id = pSessionId AND
        sl_so_balance_item.so_item_id = A.so_item_id;

    /*
     * 8. hapus data sl_log_so_balance_item
     */
--  DELETE FROM sl_log_so_balance_item
--  where tenant_id = pTenantId AND
--        ou_id = vOuId AND
--        ref_doc_type_id = vReturnNoteDocTypeId AND
--        ref_id = pReturnNoteId;

    DELETE FROM sl_log_so_balance_item B
        WHERE B.ref_doc_type_id = vReturnNoteDocTypeId AND
            EXISTS (SELECT 1 FROM tt_in_so_balance_item A
                        WHERE A.inventory_id = pReturnNoteId AND
                              A.ou_id = vOuId AND
                              A.doc_type_id = vReturnNoteDocTypeId AND
                              A.doc_no = vDocNo AND
                              A.doc_date = vDocDate AND
                              B.ref_id = A.inventory_id AND
                              B.ref_item_id = A.inventory_item_id AND
                              B.so_id = A.so_id AND
                              B.so_item_id = A.so_item_id AND
                              B.ref_doc_type_id=A.doc_type_id);

    /*
     * 9. hapus data sl_so_balance_invoice
     */
    DELETE FROM sl_so_balance_invoice B
        WHERE B.ref_doc_type_id = vReturnNoteDocTypeId AND
            EXISTS (SELECT 1 FROM tt_in_so_balance_item A
                        WHERE A.inventory_id = pReturnNoteId AND
                              A.ou_id = vOuId AND
                              A.doc_type_id = vReturnNoteDocTypeId AND
                              A.doc_no = vDocNo AND
                              A.doc_date = vDocDate AND
                              B.tenant_id = A.tenant_id AND
                              B.ref_id = A.inventory_id AND
                              B.ou_id = A.ou_id AND
                              B.partner_id = A.partner_id AND
                              B.ref_doc_type_id = A.doc_type_id AND
                              B.ref_id = A.inventory_id AND
                              B.ref_item_id = A.do_item_id);
    /*
     * 10. hapus data sl_so_balance_invoice_tax
     */
    DELETE FROM sl_so_balance_invoice_tax B
        WHERE B.ref_doc_type_id = vReturnNoteDocTypeId AND
            EXISTS (SELECT 1 FROM tt_in_so_balance_item A, m_tax D
                        WHERE A.session_id = pSessionId AND
                              A.tax_id = D.tax_id AND
                              A.inventory_id = pReturnNoteId AND
                              A.ou_id = vOuId AND
                              A.doc_type_id = vReturnNoteDocTypeId AND
                              A.doc_no = vDocNo AND
                              A.doc_date = vDocDate AND
                              B.tenant_id=A.tenant_id AND
                              B.ou_id=A.ou_id AND
                              B.partner_id=A.partner_id AND
                              B.ref_doc_type_id=A.doc_type_id AND
                              B.ref_id=A.inventory_id AND
                              B.ref_item_id=A.do_item_id AND
                              B.tax_id=A.tax_id);

    UPDATE in_balance_do_item SET status_item = vStatusInProgress
    FROM tt_in_so_balance_item A
    WHERE A.session_id = pSessionId AND
        in_balance_do_item.do_item_id = A.do_item_id;


    --* 11. PERFORM cancel gl_cancel_admin_journal_trx
    PERFORM gl_cancel_admin_journal_trx(pTenantId, (vOuStructure).ou_bu_id, vOuId, (vDocJournal).journal_type, f_get_year_month_date(vDocDate), 'DAILY', pDatetime, pUserId);


    -- pre 9 Find journal trx id
    SELECT journal_trx_id INTO vJournalTrxId
    FROM gl_journal_trx
    WHERE tenant_id = pTenantId
        AND journal_type = (vDocJournal).journal_type
        AND doc_type_id = vReturnNoteDocTypeId
        AND doc_id = pReturnNoteId
        AND doc_no = vDocNo
        AND doc_date = vDocDate
        AND ou_bu_id = (vOuStructure).ou_bu_id
        AND ou_branch_id = (vOuStructure).ou_branch_id
        AND ou_sub_bu_id = (vOuStructure).ou_sub_bu_id;

    -- * 12. DELETE gl_journal_trx
    DELETE FROM gl_journal_trx
    WHERE journal_trx_id = vJournalTrxId;

    -- * 13. DELETE gl_journal_trx_item
    DELETE FROM gl_journal_trx_item
    WHERE journal_trx_id = vJournalTrxId;

    -- * 14. DELETE gl_journal_trx_mapping
                    DELETE FROM gl_journal_trx_mapping
                    WHERE journal_trx_id = vJournalTrxId;

    -- * 15. PERFORM f_reset_approval_to_draft
    PERFORM f_reset_approval_to_draft(pTenantId, pSessionId, vScheme, pReturnNoteId, vDocNo, pDatetime, pRemarkApproval);

    -- * 16. UPDATE t_process_message
    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 = pReturnNoteId || '_' || vDocNo;

    DELETE FROM tt_in_product_balance_summary_stock WHERE session_id = pSessionId;
    DELETE FROM tt_in_so_balance_item WHERE session_id = pSessionId;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;