Cancel Submit DO

CREATE OR REPLACE FUNCTION sl_cancel_submit_do(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;
    pDeliveryOrderId                    ALIAS FOR $5;
    pRemarkApproval                     ALIAS FOR $6;
    pPrevProcessNo                      ALIAS FOR $7;

    vDeliveryOrderDocTypeId             bigint;
    vEmptyId                            bigint;
    vFunctionSubmit                     character varying;
    vStatusDocReleased                  character varying;
    vStatusDocDraft                     character varying;
    vStatusDocInProgress                character varying;
    vWorkflowStatusDraft                character varying;
    vYes                                character varying;
    vNo                                 character varying;
    vZero                               bigint;

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

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

    vSalesLedgerCode                    character varying;
    vStatusLedgerNotDone                character varying;

    vOuStructureJournalItem             OU_BU_STRUCTURE%ROWTYPE;

BEGIN

    vDeliveryOrderDocTypeId := 311;
    vEmptyId := -99;
    vFunctionSubmit := 'sl_submit_do';
    vStatusDocReleased := 'R';
    vStatusDocDraft := 'D';
    vStatusDocInProgress := 'I';
    vWorkflowStatusDraft := 'DRAFT';
    vYes := 'Y';
    vNo := 'N';
    vZero := 0;

    vSalesLedgerCode := 'SALES';
    vStatusLedgerNotDone := '0';

    --RAISE EXCEPTION 'Function Submit For Document Delivery Order is not yet Created';

    -- get data
    select f_get_ou_bu_structure(A.ou_id) AS ou, A.ou_id, A.doc_no, A.doc_date,
            f_get_document_journal(A.doc_type_id) as doc, C.scheme,
            A.ref_id AS so_id,
            D.ou_id AS warehouse_ou_id
    FROM sl_do A, sl_do_item B, m_document C, m_warehouse_ou D
    WHERE A.do_id = B.do_id AND
        A.doc_type_id = C.doc_type_id AND
        A.warehouse_id = D.warehouse_id AND
        A.do_id = pDeliveryOrderId AND
        A.doc_type_id = vDeliveryOrderDocTypeId
        AND A.status_doc = vStatusDocReleased INTO result;

    IF FOUND THEN
        vOuStructure := result.ou;
        vOuId := result.ou_id;
        vDocNo := result.doc_no;
        vDocDate := result.doc_date;
        vDocJournal := result.doc;
        vScheme := result.scheme;
        vSalesOrderId := result.so_id;
        VWarehouseOuId := result.warehouse_ou_id;

        -- 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 = vSalesLedgerCode AND
                f.ou_id = g.ou_id AND e.status_ledger = vStatusLedgerNotDone) THEN

            /*
             * validasi belum digunakan untuk invoice
             */
            IF NOT EXISTS (
                SELECT (1)
                FROM sl_so_balance_invoice A, sl_do_item B, sl_do C, sl_so D
                WHERE C.do_id = pDeliveryOrderId
                    AND B.do_id = C.do_id
                    AND C.ref_id = D.so_id
                    AND A.tenant_id = C.tenant_id
                    AND A.ou_id = C.ou_id
                    AND A.partner_id = D.partner_bill_to_id
                    AND A.ref_doc_type_id = C.doc_type_id
                    AND A.ref_id = C.do_id
                    AND A.ref_item_id = B.do_item_id
                    AND A.flg_invoice <> vNo
                    AND A.invoice_id <> vEmptyId
                ) THEN

                /*
                 * validasi tax belum digunakan untuk invoice
                 */
                IF NOT EXISTS (
                    SELECT (1)
                    FROM sl_so_balance_invoice_tax A, sl_do_item B, sl_do C, sl_so D, sl_so_item E
                    WHERE C.do_id = pDeliveryOrderId
                        AND B.do_id = C.do_id
                        AND C.ref_id = D.so_id
                        AND E.so_id = D.so_id
                        AND A.tenant_id = C.tenant_id
                        AND A.ou_id = C.ou_id
                        AND A.partner_id = D.partner_bill_to_id
                        AND A.ref_doc_type_id = C.doc_type_id
                        AND A.ref_id = C.do_id
                        AND A.ref_item_id = B.do_item_id
                        AND A.tax_id = E.tax_id
                        AND A.flg_invoice <> vNo
                        AND A.invoice_id <> vEmptyId
                    ) THEN

                    /*
                     * validasi item belum digunakan
                     */
                    IF NOT EXISTS (
                        SELECT (1)
                        FROM in_balance_do_item A, sl_do_item B
                        WHERE B.do_id = pDeliveryOrderId
                            AND A.do_item_id = B.do_item_id
                            AND (
                                (A.qty_return + A.qty_return_int) > vZero
                                OR A.status_item <> vStatusDocReleased
                            )
                        ) THEN

                        /*
                         * 1. update status doc sl_do
                         * 2. remove sl_log_so_balance_item
                         * 3. remove sl_so_balance_invoice
                         * 4. remove sl_so_balance_invoice_tax
                         * 5. remove in_log_product_balance_stock
                         * 6. remove in_balance_do_item
                         * 7. update status sl_so_balance_item
                         * 8. update status sl_so.
                         * 9. remove gl_journal_trx
                         * 10. remove gl_journal_trx_item
                         * 11. remove gl_journal_trx_mapping
                         *
                         */

                        UPDATE sl_do SET status_doc = vStatusDocDraft, version = version + 1, update_datetime = pDatetime, update_user_id = pUserId
                        WHERE do_id = pDeliveryOrderId
                            AND status_doc = vStatusDocReleased;

                        DELETE
                        FROM sl_log_so_balance_item Z
                        WHERE EXISTS (
                            SELECT (1)
                            FROM sl_do_item A
                            INNER JOIN sl_do B ON B.do_id = A.do_id
                            WHERE A.do_id = pDeliveryOrderId
                                AND Z.ref_id = A.do_id
                                AND Z.ref_doc_type_id = B.doc_type_id
                                AND Z.ref_item_id = A.do_item_id
                        );

                        DELETE
                        FROM sl_so_balance_invoice Z
                        WHERE EXISTS (
                            SELECT (1)
                            FROM sl_do A
                            INNER JOIN sl_do_item B ON B.do_id = A.do_id
                            INNER JOIN sl_so C ON C.so_id = A.ref_id
                            WHERE A.do_id = pDeliveryOrderId
                                AND Z.tenant_id = A.tenant_id
                                AND Z.ou_id = A.ou_id
                                AND Z.partner_id = C.partner_bill_to_id
                                AND Z.ref_doc_type_id = A.doc_type_id
                                AND Z.ref_id = A.do_id
                                AND Z.ref_item_id = B.do_item_id
                                AND Z.flg_invoice = vNo
                                AND Z.invoice_id = vEmptyId
                        );

                        DELETE
                        FROM sl_so_balance_invoice_tax Z
                        WHERE EXISTS (
                            SELECT (1)
                            FROM sl_do A
                            INNER JOIN sl_do_item B ON B.do_id = A.do_id
                            INNER JOIN sl_so C ON C.so_id = A.ref_id
                            INNER JOIN sl_so_item D ON D.so_id = C.so_id
                            WHERE A.do_id = pDeliveryOrderId
                                AND Z.tenant_id = A.tenant_id
                                AND Z.ou_id = A.ou_id
                                AND Z.partner_id = C.partner_bill_to_id
                                AND Z.ref_doc_type_id = A.doc_type_id
                                AND Z.ref_id = A.do_id
                                AND Z.ref_item_id = B.do_item_id
                                AND Z.tax_id = D.tax_id
                                AND Z.flg_invoice = vNo
                                AND Z.invoice_id = vEmptyId
                        );

                        DELETE
                        FROM in_log_product_balance_stock Z
                        WHERE EXISTS (
                            SELECT (1)
                            FROM sl_do A
                            INNER JOIN sl_do_item B ON B.do_id = A.do_id
                            INNER JOIN sl_do_product C ON C.do_item_id = B.do_item_id
                            WHERE A.do_id = pDeliveryOrderId
                                AND 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.do_id
                                AND Z.doc_date = A.doc_date
                                AND Z.warehouse_id = A.warehouse_id
                                AND Z.product_id = C.product_id
                                AND Z.product_balance_id = C.product_balance_id
                                AND Z.product_status = C.product_status
                        );

                        DELETE
                        FROM in_balance_do_item Z
                        WHERE EXISTS (
                            SELECT (1)
                            FROM sl_do_item A
                            WHERE A.do_id = pDeliveryOrderId
                                AND Z.do_item_id = A.do_item_id
                        )
                        AND Z.qty_return = vZero
                        AND Z.qty_return_int = vZero
                        AND Z.status_item = vStatusDocReleased;

                        UPDATE sl_so_balance_item SET status_item = vStatusDocInProgress, update_datetime = pDatetime, update_user_id = pUserId
                        FROM sl_do_item A
                        WHERE sl_so_balance_item.so_item_id = A.ref_id AND
                            sl_so_balance_item.tenant_id = A.tenant_id AND
                            A.do_id = pDeliveryOrderId;

                        UPDATE sl_so SET status_doc = vStatusDocReleased
                        WHERE so_id = vSalesOrderId;

                        IF (vOuId <> vWarehouseOuId) THEN
                            SELECT f_get_ou_bu_structure(vWarehouseOuId) as ou_structure INTO result;
                            vOuStructureJournalItem := result.ou_structure;
                        ELSE
                            vOuStructureJournalItem := ROW(-99, -99, -99);
                        END IF;

                        -- 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 = vDeliveryOrderDocTypeId
                            AND doc_id = pDeliveryOrderId
                            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, pDeliveryOrderId, 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 = pDeliveryOrderId || '_' || vDocNo;

                    ELSE
                        RAISE EXCEPTION 'Document with id % is aready used in another document', pDeliveryOrderId;
                    END IF;

                ELSE
                    RAISE EXCEPTION 'Document with id % is aready used for invoice', pDeliveryOrderId;
                END IF;

            ELSE
                RAISE EXCEPTION 'Document with id % is aready used for invoice', pDeliveryOrderId;
            END IF;

        ELSE
            RAISE EXCEPTION 'Admin Process Ledger for Sales 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', pDeliveryOrderId;
    END IF;

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