in_cancel_submit_claim_note

--Adrian, Jan 27, 2017
--Modified by Adrian, Jul 4, 2017, menambahkan hapus in_log_product_balance_stock

CREATE OR REPLACE FUNCTION in_cancel_submit_claim_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;
    pDatetime                           ALIAS FOR $4;
    pClaimNoteId                        ALIAS FOR $5;
    pRemarkApproval                     ALIAS FOR $6;
    pPrevProcessNo                      ALIAS FOR $7;

    vClaimNoteDocTypeId                 bigint;
    vEmptyId                            bigint;
    vFunctionSubmit                     character varying;
    vStatusDocRelease                   character varying;
    vStatusDocDraft                     character varying;
    vWorkflowStatusDraft                character varying;
    vInventoryLedgerCode                character varying;
    vStatusLedgerNotDone                character varying;

    vOuId                               bigint;
    vJournalTrxId                       bigint;
    vDocNo                              character varying;
    vDocDate                            character varying;
    vScheme                             character varying;

    vDocJournal                         DOC_JOURNAL%ROWTYPE;
    vOuStructure                        OU_BU_STRUCTURE%ROWTYPE;
    result                              RECORD;

    vStatusInProgress                   character varying;
    vYes                                character varying;

BEGIN

    vClaimNoteDocTypeId := 511;
    vEmptyId := -99;
    vFunctionSubmit := 'in_submit_claim_note';
    vStatusDocRelease := 'R';
    vStatusDocDraft := 'D';
    vWorkflowStatusDraft := 'DRAFT';
    vInventoryLedgerCode := 'INV';
    vStatusLedgerNotDone := '0';

    vStatusInProgress := 'I';
    vYes := 'Y';

    --RAISE EXCEPTION 'Function Submit For Document Claim Note';

    -- get data
    select f_get_ou_bu_structure(A.ou_from_id) AS ou, A.ou_from_id, A.doc_no, A.doc_date,
            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 = pClaimNoteId AND
        A.doc_type_id = vClaimNoteDocTypeId
        AND A.status_doc = vStatusDocRelease INTO result;

    IF FOUND THEN
        vOuStructure := result.ou;
        vOuId := result.ou_from_id;
        vDocNo := result.doc_no;
        vDocDate := result.doc_date;
        vDocJournal := result.doc;
        vScheme := result.scheme;

        -- Cek status ledger not yet closed
        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(vDocDate, 1, 6)
                AND e.ledger_code = vInventoryLedgerCode AND
                f.ou_id = g.ou_id AND e.status_ledger = vStatusLedgerNotDone) THEN

            DELETE FROM tt_in_po_balance_item WHERE session_id = pSessionId;

            /*
             * insert data temporer tt_in_po_balance_item
             */
            INSERT INTO tt_in_po_balance_item
            (session_id, tenant_id, ou_id, doc_type_id,
            doc_no, doc_date, inventory_id, partner_id,
            inventory_item_id, po_id, receive_goods_id, receive_goods_item_id,
            qty_return, base_uom_id, remark,
            po_item_id, curr_code, price,
            flg_tax_amount, qty_po, qty_int_po,
            po_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, A.partner_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.po_item_id, D.curr_code, D.gross_price_po - D.discount_amount,
                    D.flg_tax_amount, D.qty_po, D.qty_int,
                    D.po_uom_id, D.tax_id, D.tax_percentage
            FROM in_inventory A, in_inventory_item B, in_balance_receive_goods_item C, pu_po_item D
            WHERE A.inventory_id = pClaimNoteId AND
                  A.inventory_id = B.inventory_id AND
                  B.ref_item_id = C.receive_goods_item_id AND
                  C.po_item_id = D.po_item_id
            GROUP BY A.tenant_id, A.ou_from_id, A.doc_type_id,
                    A.doc_no, A.doc_date, A.inventory_id, A.partner_id,
                    B.inventory_item_id, A.ref_id, B.ref_id, B.ref_item_id,
                    B.base_uom_id, D.po_item_id, D.curr_code, D.nett_price_po, D.qty_po, D.qty_int, D.po_uom_id, D.tax_id ;

            /*
             * validasi belum dibuatkan invoice
             */
            IF NOT EXISTS (
                SELECT (1) FROM tt_in_po_balance_item B, pu_po_balance_invoice A
                WHERE A.tenant_id = B.tenant_id
                    AND A.ou_id = B.ou_id
                    AND A.po_id = B.po_id
                    AND A.ref_doc_type_id = B.doc_type_id
                    AND A.ref_id = B.inventory_id
                    AND A.ref_item_id = B.receive_goods_item_id
                    AND A.flg_invoice = vYes) THEN

                /*
                 * validasi status pu_po_balance_item bukan 'I'
                 */
                IF NOT EXISTS (
                    SELECT (1) FROM pu_po_balance_item B, tt_in_po_balance_item A
                    WHERE A.session_id = pSessionId AND
                        B.po_item_id = A.po_item_id AND
                        B.status_item = vStatusInProgress) THEN

                    /*
                     * validasi status in_balance_receive_goods_item bukan 'I'
                     */
                    IF NOT EXISTS (
                        SELECT (1) FROM in_balance_receive_goods_item B, tt_in_po_balance_item A
                        WHERE A.session_id = pSessionId AND
                            B.receive_goods_item_id = A.receive_goods_item_id AND
                            B.status_item = vStatusInProgress) THEN

                        /*
                         * hapus data log product balance stock
                         */
                        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_from_id, B.product_status, B.base_uom_id, (SUM(B.qty_realization) * -1) AS qty
                            FROM in_inventory A, in_inventory_item B, m_warehouse_ou C
                            WHERE A.inventory_id = pClaimNoteId AND
                                A.inventory_id = B.inventory_id AND
                                C.warehouse_id = A.warehouse_from_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
                        )
                        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_from_id
                                AND Z.product_status = A.product_status
                                AND Z.base_uom_id = A.base_uom_id
                                AND Z.qty = A.qty
                        )

                        /*
                         * update qty return di data pu_po_balance_item
                         */
                        UPDATE pu_po_balance_item SET qty_return = pu_po_balance_item.qty_return - ((A.qty_return * A.qty_po) / A.qty_int_po), qty_int_return = pu_po_balance_item.qty_int_return - A.qty_return, update_datetime = pDatetime, update_user_id = pUserId
                        FROM tt_in_po_balance_item A
                        WHERE A.session_id = pSessionId AND
                            pu_po_balance_item.po_item_id = A.po_item_id;

                        /*
                         * delete data pu_log_po_balance_item
                         */
                        DELETE FROM pu_log_po_balance_item A
                        WHERE EXISTS(
                            SELECT (1) FROM tt_in_po_balance_item B
                            WHERE A.tenant_id = B.tenant_id
                                AND A.po_id = B.po_id
                                AND A.po_item_id = B.po_item_id
                                AND A.ref_doc_type_id = B.doc_type_id
                                AND A.ref_id = B.inventory_id
                                AND A.ref_item_id = B.inventory_item_id
                                AND A.qty_trx = ((B.qty_return * B.qty_po) / B.qty_int_po )
                                AND A.trx_uom_id = B.po_uom_id
                                AND A.qty_int = B.qty_return
                                AND A.base_uom_id = B.base_uom_id
                                AND A.remark = B.remark
                        );

                        /*
                         * delete data pu_po_balance_invoice
                         */
                        DELETE FROM pu_po_balance_invoice A
                        WHERE EXISTS(
                            SELECT (1) FROM tt_in_po_balance_item B
                            WHERE A.tenant_id = B.tenant_id
                                AND A.ou_id = B.ou_id
                                AND A.po_id = B.po_id
                                AND A.ref_doc_type_id = B.doc_type_id
                                AND A.ref_id = B.inventory_id
                                AND A.ref_item_id = B.receive_goods_item_id
                        );


                        /*
                         * delete data pu_po_balance_invoice_tax
                         */
                        DELETE FROM pu_po_balance_invoice_tax A
                        WHERE EXISTS(
                            SELECT (1) FROM tt_in_po_balance_item B, m_tax C
                            WHERE B.tax_id = C.tax_id AND
                                A.tenant_id = B.tenant_id AND
                                A.ou_id = B.ou_id AND
                                A.po_id = B.po_id AND
                                A.ref_doc_type_id = B.doc_type_id AND
                                A.ref_id = B.inventory_id AND
                                A.ref_item_id = B.receive_goods_item_id AND
                                A.tax_id = C.tax_id
                        );

                        UPDATE in_balance_receive_goods_item SET status_item = vStatusInProgress
                        FROM tt_in_po_balance_item A
                        WHERE A.session_id = pSessionId AND
                            in_balance_receive_goods_item.receive_goods_item_id = A.receive_goods_item_id;


                        -- Update in_inventory, set doc_status to DRAFT
                        UPDATE in_inventory
                        SET status_doc = vStatusDocDraft,
                            workflow_status = vWorkflowStatusDraft,
                            version = version + 1,
                            update_datetime = pDatetime,
                            update_user_id = pUserId
                        WHERE inventory_id = pClaimNoteId;


                        -- 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), 'MONTHLY', pDatetime, pUserId);

                        -- 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 = vClaimNoteDocTypeId
                            AND doc_id = pClaimNoteId
                            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;

                        -- DELETE gl_journal_trx
                        DELETE FROM gl_journal_trx
                        WHERE journal_trx_id = vJournalTrxId;

                        -- DELETE gl_journal_trx_item
                        DELETE FROM gl_journal_trx_item
                        WHERE journal_trx_id = vJournalTrxId;

                        -- DELETE gl_journal_trx_mapping
                        DELETE FROM gl_journal_trx_mapping
                        WHERE journal_trx_id = vJournalTrxId;

                        -- PERFORM f_reset_approval_to_draft
                        PERFORM f_reset_approval_to_draft(pTenantId, pSessionId, vScheme, pClaimNoteId, vDocNo, pDatetime, pRemarkApproval);

                        -- 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 = pClaimNoteId || '_' || vDocNo;

                        DELETE FROM tt_in_po_balance_item WHERE session_id = pSessionId;

                    ELSE
                        RAISE EXCEPTION 'Document with id % has a final item', pClaimNoteId;
                    END IF;

                ELSE
                    RAISE EXCEPTION 'Document with id % has a final item', pClaimNoteId;
                END IF;

            ELSE
                RAISE EXCEPTION 'Document with id % is already used in Purchasing Invoice', pClaimNoteId;
            END IF;

        ELSE
            RAISE EXCEPTION 'Admin Process Ledger for Inventory in year month % is already closed', SUBSTR(vDocDate, 1, 6);
        END IF;

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

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