org.openbravo.test.accounting.RecordID2Test.java Source code

Java tutorial

Introduction

Here is the source code for org.openbravo.test.accounting.RecordID2Test.java

Source

/*
 *************************************************************************
 * The contents of this file are subject to the Openbravo  Public  License
 * Version  1.1  (the  "License"),  being   the  Mozilla   Public  License
 * Version 1.1  with a permitted attribution clause; you may not  use this
 * file except in compliance with the License. You  may  obtain  a copy of
 * the License at http://www.openbravo.com/legal/license.html 
 * Software distributed under the License  is  distributed  on  an "AS IS"
 * basis, WITHOUT WARRANTY OF ANY KIND, either express or implied. See the
 * License for the specific  language  governing  rights  and  limitations
 * under the License. 
 * The Original Code is Openbravo ERP. 
 * The Initial Developer of the Original Code is Openbravo SLU 
 * All portions are Copyright (C) 2014 Openbravo SLU 
 * All Rights Reserved. 
 * Contributor(s):  ______________________________________.
 ************************************************************************
 */

package org.openbravo.test.accounting;

import static org.junit.Assert.assertEquals;
import static org.junit.Assert.assertFalse;
import static org.junit.Assert.assertTrue;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Calendar;
import java.util.Date;

import javax.servlet.ServletException;

import org.apache.commons.lang.time.DateUtils;
import org.junit.Test;
import org.openbravo.base.secureApp.VariablesSecureApp;
import org.openbravo.dal.core.OBContext;
import org.openbravo.dal.service.OBDal;
import org.openbravo.database.ConnectionProvider;
import org.openbravo.erpCommon.ad_forms.AcctServer;
import org.openbravo.financial.ResetAccounting;
import org.openbravo.model.common.businesspartner.BusinessPartner;
import org.openbravo.model.common.invoice.Invoice;
import org.openbravo.model.financialmgmt.payment.FIN_FinaccTransaction;
import org.openbravo.model.financialmgmt.payment.FIN_FinancialAccount;
import org.openbravo.model.financialmgmt.payment.FIN_Payment;
import org.openbravo.model.financialmgmt.payment.FIN_Reconciliation;
import org.openbravo.service.db.DalConnectionProvider;
import org.openbravo.test.base.OBBaseTest;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * Tests cases to check RecordID2-DateBalanced functionality (Open balances Project)
 * 
 * 
 */
public class RecordID2Test extends OBBaseTest {
    final static private Logger log = LoggerFactory.getLogger(RecordID2Test.class);
    private static final String TABLE_INVOICE = "318";
    private static final String TABLE_TRANSACTION = "4D8C3B3C31D1410DA046140C9F024D17";
    private static final String TABLE_PAYMENT = "D1A97202E832470285C9B1EB026D54E2";
    private static final String TABLE_RECONCILIATION = "B1B7075C46934F0A9FD4C4D0F1457B42";

    /**
     * Verifies documents can be posted and unposted and that affects fact_acct (RecordID2 and
     * DateBalanced) Case 1: Invoice posted and just reconciliation posted. After both are posted
     * receivables entry should be balanced Invoice: I/1 Reconciliation: 1000032
     */
    @Test
    public void testRecordID2_case1() {
        // Invoice I/1 from QA dataset
        String strCInvoiceId = "FF808081328278B5013282A1A35800A0";
        String strCAcctSchemaId = "9A68A0F8D72D4580B3EC3CAA00A5E1F0";

        OBContext.setAdminMode(true);
        try {
            Invoice invoice = OBDal.getInstance().get(Invoice.class, strCInvoiceId);
            String strRecordID2 = invoice.getFINPaymentScheduleList().get(0).getId();
            assertEquals("Invoice Not Posted", "Y", invoice.getPosted());
            ResetAccounting.delete(invoice.getClient().getId(), invoice.getOrganization().getId(), "318",
                    invoice.getId(), "", "");
            invoice = OBDal.getInstance().get(Invoice.class, strCInvoiceId);
            String strReceivablesAccountId = getReceivablesAccount(invoice.getBusinessPartner(), strCAcctSchemaId);
            assertEquals("Invoice Could Not Be Unposted", "N", invoice.getPosted());
            assertTrue("RecordID2 not updated after unposting invoice", BigDecimal.ZERO.compareTo(
                    getBalance(strRecordID2, TABLE_INVOICE, strCAcctSchemaId, strReceivablesAccountId)) == 0);
            postDocument(strCInvoiceId, "318", invoice.getClient().getId(), invoice.getOrganization().getId());
            invoice = OBDal.getInstance().get(Invoice.class, strCInvoiceId);
            assertTrue("RecordID2 not updated after posting invoice", new BigDecimal("100").compareTo(
                    getBalance(strRecordID2, TABLE_INVOICE, strCAcctSchemaId, strReceivablesAccountId)) == 0);
            assertEquals("Invoice Could Not be Posted", "Y", invoice.getPosted());

            // Reconciliation: 1000032 - 02-07-2011 from QA Dataset. Paying previous invoice
            String strFINReconciliationId = "FF808081328278B5013282A3F67700C5";
            FIN_Reconciliation reconciliation = OBDal.getInstance().get(FIN_Reconciliation.class,
                    strFINReconciliationId);
            assertEquals("Reconciliation Not Posted", "Y", reconciliation.getPosted());
            ResetAccounting.delete(reconciliation.getClient().getId(), reconciliation.getOrganization().getId(),
                    TABLE_RECONCILIATION, reconciliation.getId(), "", "");
            reconciliation = OBDal.getInstance().get(FIN_Reconciliation.class, strFINReconciliationId);
            assertEquals("Reconciliation Could Not Be Unposted", "N", reconciliation.getPosted());
            assertTrue("RecordID2 not updated after unposting reconciliation",
                    BigDecimal.ZERO.compareTo(getBalance(strRecordID2, TABLE_RECONCILIATION, strCAcctSchemaId,
                            strReceivablesAccountId)) == 0);
            postDocument(strFINReconciliationId, TABLE_RECONCILIATION, reconciliation.getClient().getId(),
                    reconciliation.getOrganization().getId());
            reconciliation = OBDal.getInstance().get(FIN_Reconciliation.class, strFINReconciliationId);
            assertTrue("RecordID2 not updated after posting reconciliation",
                    new BigDecimal("-100").compareTo(getBalance(strRecordID2, TABLE_RECONCILIATION,
                            strCAcctSchemaId, strReceivablesAccountId)) == 0);
            assertEquals("Reconciliation Could Not be Posted", "Y", reconciliation.getPosted());
            assertEquals("Entry Not Balanced",
                    DateUtils.truncate(reconciliation.getAPRMFinaccTransactionVList().get(0).getTransactionDate(),
                            Calendar.DATE),
                    getBalancedDate(strRecordID2, strReceivablesAccountId));
        } catch (Exception e) {
            assertFalse(true);
            log.error("Error when executing query", e);
        } finally {
            OBContext.restorePreviousMode();
        }
    }

    /**
     * Verifies documents can be posted and unposted and that affects fact_acct (RecordID2 and
     * DateBalanced) Case 2: Invoice posted and transaction posted. After both are posted receivables
     * entry should be balanced Invoice: I/10 Transaction: line 50 - 400012 - 02-04-2011 - Customer A
     * - 146.00
     */
    @Test
    public void testRecordID2_case2() {
        // Invoice I/10 from QA dataset
        String strCInvoiceId = "FF8080813285D49A0132874B35C7030C";
        String strCAcctSchemaId = "9A68A0F8D72D4580B3EC3CAA00A5E1F0";

        OBContext.setAdminMode(true);
        try {
            Invoice invoice = OBDal.getInstance().get(Invoice.class, strCInvoiceId);
            String strRecordID2 = invoice.getFINPaymentScheduleList().get(0).getId();
            assertEquals("Invoice Not Posted", "Y", invoice.getPosted());
            ResetAccounting.delete(invoice.getClient().getId(), invoice.getOrganization().getId(), "318",
                    invoice.getId(), "", "");
            invoice = OBDal.getInstance().get(Invoice.class, strCInvoiceId);
            String strReceivablesAccountId = getReceivablesAccount(invoice.getBusinessPartner(), strCAcctSchemaId);
            assertEquals("Invoice Could Not Be Unposted", "N", invoice.getPosted());
            assertTrue("RecordID2 not updated after unposting invoice", BigDecimal.ZERO.compareTo(
                    getBalance(strRecordID2, TABLE_INVOICE, strCAcctSchemaId, strReceivablesAccountId)) == 0);
            postDocument(strCInvoiceId, "318", invoice.getClient().getId(), invoice.getOrganization().getId());
            invoice = OBDal.getInstance().get(Invoice.class, strCInvoiceId);
            assertTrue("RecordID2 not updated after posting invoice", new BigDecimal("146").compareTo(
                    getBalance(strRecordID2, TABLE_INVOICE, strCAcctSchemaId, strReceivablesAccountId)) == 0);
            assertEquals("Invoice Could Not be Posted", "Y", invoice.getPosted());

            // Transaction: 1000032 - 02-07-2011 from QA Dataset. Paying previous invoice
            String strFINFinaccTransactionId = "FF8080813285D49A013287518E1C033E";
            FIN_FinaccTransaction transaction = OBDal.getInstance().get(FIN_FinaccTransaction.class,
                    strFINFinaccTransactionId);
            assertEquals("Transaction Not Posted", "Y", transaction.getPosted());
            ResetAccounting.delete(transaction.getClient().getId(), transaction.getOrganization().getId(),
                    TABLE_TRANSACTION, transaction.getId(), "", "");
            transaction = OBDal.getInstance().get(FIN_FinaccTransaction.class, strFINFinaccTransactionId);
            assertEquals("Transaction Could Not Be Unposted", "N", transaction.getPosted());
            assertTrue("RecordID2 not updated after unposting transaction", BigDecimal.ZERO.compareTo(
                    getBalance(strRecordID2, TABLE_TRANSACTION, strCAcctSchemaId, strReceivablesAccountId)) == 0);
            postDocument(strFINFinaccTransactionId, TABLE_TRANSACTION, transaction.getClient().getId(),
                    transaction.getOrganization().getId());
            transaction = OBDal.getInstance().get(FIN_FinaccTransaction.class, strFINFinaccTransactionId);
            assertTrue("RecordID2 not updated after posting transaction", new BigDecimal("-146").compareTo(
                    getBalance(strRecordID2, TABLE_TRANSACTION, strCAcctSchemaId, strReceivablesAccountId)) == 0);
            assertEquals("Transaction Could Not be Posted", "Y", transaction.getPosted());
            assertEquals("Entry Not Balanced", DateUtils.truncate(transaction.getTransactionDate(), Calendar.DATE),
                    getBalancedDate(strRecordID2, strReceivablesAccountId));
        } catch (Exception e) {
            assertFalse(true);
            log.error("Error when executing query", e);
        } finally {
            OBContext.restorePreviousMode();
        }
    }

    /**
     * Verifies documents can be posted and unposted and that affects fact_acct (RecordID2 and
     * DateBalanced) Case 3: Invoice posted and payment posted. After both are posted receivables
     * entry should be balanced Invoice: I/11 Payment: 400013 - 02-04-2011 - Customer A - 30.00 Then
     * transaction is posted and in transit balance is checked
     */
    @Test
    public void testRecordID2_case3() {
        // Invoice I/11 from QA dataset
        String strCInvoiceId = "FF8080813285D49A0132875C1EF60374";
        String strCAcctSchemaId = "9A68A0F8D72D4580B3EC3CAA00A5E1F0";

        OBContext.setAdminMode(true);
        try {
            Invoice invoice = OBDal.getInstance().get(Invoice.class, strCInvoiceId);
            String strRecordID2 = invoice.getFINPaymentScheduleList().get(0).getId();
            assertEquals("Invoice Not Posted", "Y", invoice.getPosted());
            ResetAccounting.delete(invoice.getClient().getId(), invoice.getOrganization().getId(), "318",
                    invoice.getId(), "", "");
            invoice = OBDal.getInstance().get(Invoice.class, strCInvoiceId);
            String strReceivablesAccountId = getReceivablesAccount(invoice.getBusinessPartner(), strCAcctSchemaId);
            assertEquals("Invoice Could Not Be Unposted", "N", invoice.getPosted());
            assertTrue("RecordID2 not updated after unposting invoice", BigDecimal.ZERO.compareTo(
                    getBalance(strRecordID2, TABLE_INVOICE, strCAcctSchemaId, strReceivablesAccountId)) == 0);
            postDocument(strCInvoiceId, "318", invoice.getClient().getId(), invoice.getOrganization().getId());
            invoice = OBDal.getInstance().get(Invoice.class, strCInvoiceId);

            assertTrue("RecordID2 not updated after posting invoice", new BigDecimal("30").compareTo(
                    getBalance(strRecordID2, TABLE_INVOICE, strCAcctSchemaId, strReceivablesAccountId)) == 0);
            assertEquals("Invoice Could Not be Posted", "Y", invoice.getPosted());

            // Payment: 400013 - 02-04-2011 - Customer A - 30.00 from QA Dataset. Paying previous invoice
            String strFINPaymentId = "FF8080813285D49A0132875DB5780380";
            FIN_Payment payment = OBDal.getInstance().get(FIN_Payment.class, strFINPaymentId);
            String strRecordID2InTransit = payment.getId();
            String strInTransitAccountId = getInTransitAccount(payment.getAccount(), strCAcctSchemaId);
            assertEquals("Payment Not Posted", "Y", payment.getPosted());
            ResetAccounting.delete(payment.getClient().getId(), payment.getOrganization().getId(), TABLE_PAYMENT,
                    payment.getId(), "", "");
            payment = OBDal.getInstance().get(FIN_Payment.class, strFINPaymentId);

            assertEquals("Payment Could Not Be Unposted", "N", payment.getPosted());
            assertTrue("RecordID2 for receivables not updated after unposting Payment", BigDecimal.ZERO.compareTo(
                    getBalance(strRecordID2, TABLE_PAYMENT, strCAcctSchemaId, strReceivablesAccountId)) == 0);
            assertTrue("RecordID2 for In Transit not updated after unposting Payment",
                    BigDecimal.ZERO.compareTo(getBalance(strRecordID2InTransit, TABLE_PAYMENT, strCAcctSchemaId,
                            strInTransitAccountId)) == 0);

            postDocument(strFINPaymentId, TABLE_PAYMENT, payment.getClient().getId(),
                    payment.getOrganization().getId());
            payment = OBDal.getInstance().get(FIN_Payment.class, strFINPaymentId);

            assertTrue("RecordID2 for receivables not updated after posting Payment",
                    new BigDecimal("-30").compareTo(getBalance(strRecordID2, TABLE_PAYMENT, strCAcctSchemaId,
                            strReceivablesAccountId)) == 0);
            assertTrue("RecordID2 for In Transit not updated after posting Payment",
                    new BigDecimal("30").compareTo(getBalance(strRecordID2InTransit, TABLE_PAYMENT,
                            strCAcctSchemaId, strInTransitAccountId)) == 0);
            assertEquals("Payment Could Not be Posted", "Y", payment.getPosted());
            assertEquals("Entry Not Balanced for Receivables",
                    DateUtils.truncate(payment.getPaymentDate(), Calendar.DATE),
                    getBalancedDate(strRecordID2, strReceivablesAccountId));
            FIN_FinaccTransaction transaction = payment.getFINFinaccTransactionList().get(0);
            assertEquals("Transaction Not Posted", "Y", transaction.getPosted());
            ResetAccounting.delete(transaction.getClient().getId(), transaction.getOrganization().getId(),
                    TABLE_TRANSACTION, transaction.getId(), "", "");
            transaction = OBDal.getInstance().get(FIN_FinaccTransaction.class, transaction.getId());
            assertEquals("Transaction Could Not Be Unposted", "N", transaction.getPosted());
            assertTrue("RecordID2 not updated after unposting transaction",
                    BigDecimal.ZERO.compareTo(getBalance(strRecordID2InTransit, TABLE_TRANSACTION, strCAcctSchemaId,
                            strInTransitAccountId)) == 0);
            postDocument(transaction.getId(), TABLE_TRANSACTION, transaction.getClient().getId(),
                    transaction.getOrganization().getId());
            transaction = OBDal.getInstance().get(FIN_FinaccTransaction.class, transaction.getId());
            assertTrue("RecordID2 not updated after posting transaction",
                    new BigDecimal("-30").compareTo(getBalance(strRecordID2InTransit, TABLE_TRANSACTION,
                            strCAcctSchemaId, strInTransitAccountId)) == 0);
            assertEquals("Transaction Could Not be Posted", "Y", transaction.getPosted());
            assertEquals("Entry Not Balanced for In Transit ",
                    DateUtils.truncate(transaction.getTransactionDate(), Calendar.DATE),
                    getBalancedDate(strRecordID2InTransit, strInTransitAccountId));
        } catch (Exception e) {
            assertFalse(true);
            log.error("Error when executing query", e);
        } finally {
            OBContext.restorePreviousMode();
        }
    }

    /**
     * Verifies documents can be posted and unposted and that affects fact_acct (RecordID2 and
     * DateBalanced) Case 4: Invoice posted , transaction posted and reconciliation posted. After both
     * are posted receivables entry should be balanced Invoice: I/14 Transaction: Accounting Documents
     * EURO - EUR - 400016 - 02-04-2011 - Customer A - 40.00 - 0 Reconciliation: 1000042 . When
     * Transaction and reconciliation are posted in transit account balance should be 0
     */
    @Test
    public void testRecordID2_case4() {
        // Invoice I/14 from QA dataset
        String strCInvoiceId = "FF808081328B2FE901328B5AE4030098";
        String strCAcctSchemaId = "9A68A0F8D72D4580B3EC3CAA00A5E1F0";

        OBContext.setAdminMode(true);
        try {
            Invoice invoice = OBDal.getInstance().get(Invoice.class, strCInvoiceId);
            String strRecordID2 = invoice.getFINPaymentScheduleList().get(0).getId();
            assertEquals("Invoice Not Posted", "Y", invoice.getPosted());
            ResetAccounting.delete(invoice.getClient().getId(), invoice.getOrganization().getId(), "318",
                    invoice.getId(), "", "");
            invoice = OBDal.getInstance().get(Invoice.class, strCInvoiceId);
            String strReceivablesAccountId = getReceivablesAccount(invoice.getBusinessPartner(), strCAcctSchemaId);
            assertEquals("Invoice Could Not Be Unposted", "N", invoice.getPosted());
            assertTrue("RecordID2 not updated after unposting invoice", BigDecimal.ZERO.compareTo(
                    getBalance(strRecordID2, TABLE_INVOICE, strCAcctSchemaId, strReceivablesAccountId)) == 0);
            postDocument(strCInvoiceId, "318", invoice.getClient().getId(), invoice.getOrganization().getId());
            invoice = OBDal.getInstance().get(Invoice.class, strCInvoiceId);
            assertTrue("RecordID2 not updated after posting invoice", new BigDecimal("50").compareTo(
                    getBalance(strRecordID2, TABLE_INVOICE, strCAcctSchemaId, strReceivablesAccountId)) == 0);
            assertEquals("Invoice Could Not be Posted", "Y", invoice.getPosted());

            // Payment: 400016 - 02-04-2011 - Customer A - 40.00 from QA Dataset. Paying previous invoice
            String strFINPaymentId = "FF808081328B2FE901328B5D698000B1";
            FIN_Payment payment = OBDal.getInstance().get(FIN_Payment.class, strFINPaymentId);
            String strInTransitAccountId = getDepositAccount(payment.getAccount(), strCAcctSchemaId);
            FIN_FinaccTransaction transaction = payment.getFINFinaccTransactionList().get(0);
            String strRecordID2InTransit = transaction.getId();
            assertEquals("Transaction Not Posted", "Y", transaction.getPosted());
            ResetAccounting.delete(transaction.getClient().getId(), transaction.getOrganization().getId(),
                    TABLE_TRANSACTION, transaction.getId(), "", "");
            transaction = OBDal.getInstance().get(FIN_FinaccTransaction.class, transaction.getId());
            assertEquals("Transaction Could Not Be Unposted", "N", transaction.getPosted());
            assertTrue("RecordID2 for receivables not updated after unposting transaction",
                    BigDecimal.ZERO.compareTo(getBalance(strRecordID2, TABLE_TRANSACTION, strCAcctSchemaId,
                            strReceivablesAccountId)) == 0);
            assertTrue("RecordID2 for in transit not updated after unposting transaction",
                    BigDecimal.ZERO.compareTo(getBalance(strRecordID2InTransit, TABLE_TRANSACTION, strCAcctSchemaId,
                            strInTransitAccountId)) == 0);
            postDocument(transaction.getId(), TABLE_TRANSACTION, transaction.getClient().getId(),
                    transaction.getOrganization().getId());
            transaction = OBDal.getInstance().get(FIN_FinaccTransaction.class, transaction.getId());
            assertTrue("RecordID2 for receivables not updated after posting transaction",
                    new BigDecimal("-50").compareTo(getBalance(strRecordID2, TABLE_TRANSACTION, strCAcctSchemaId,
                            strReceivablesAccountId)) == 0);
            assertTrue("RecordID2 for in transit not updated after posting transaction",
                    new BigDecimal("40").compareTo(getBalance(strRecordID2InTransit, TABLE_TRANSACTION,
                            strCAcctSchemaId, strInTransitAccountId)) == 0);
            assertEquals("Transaction Could Not be Posted", "Y", transaction.getPosted());
            assertEquals("Entry Not Balanced", DateUtils.truncate(transaction.getTransactionDate(), Calendar.DATE),
                    getBalancedDate(strRecordID2, strReceivablesAccountId));
            FIN_Reconciliation reconciliation = transaction.getReconciliation();
            assertEquals("Reconciliation Not Posted", "Y", reconciliation.getPosted());
            ResetAccounting.delete(reconciliation.getClient().getId(), reconciliation.getOrganization().getId(),
                    TABLE_RECONCILIATION, reconciliation.getId(), "", "");
            reconciliation = OBDal.getInstance().get(FIN_Reconciliation.class, reconciliation.getId());
            assertEquals("Reconciliation Could Not Be Unposted", "N", reconciliation.getPosted());
            assertTrue("RecordID2 for in transit not updated after unposting reconciliation",
                    BigDecimal.ZERO.compareTo(getBalance(strRecordID2InTransit, TABLE_RECONCILIATION,
                            strCAcctSchemaId, strInTransitAccountId)) == 0);
            postDocument(reconciliation.getId(), TABLE_RECONCILIATION, reconciliation.getClient().getId(),
                    reconciliation.getOrganization().getId());
            reconciliation = OBDal.getInstance().get(FIN_Reconciliation.class, reconciliation.getId());
            assertTrue("RecordID2 for in transit not updated after posting reconciliation",
                    new BigDecimal("-40").compareTo(getBalance(strRecordID2InTransit, TABLE_RECONCILIATION,
                            strCAcctSchemaId, strInTransitAccountId)) == 0);
            assertEquals("Reconciliation Could Not be Posted", "Y", reconciliation.getPosted());
            assertEquals("Entry Not Balanced",
                    DateUtils.truncate(reconciliation.getAPRMFinaccTransactionVList().get(0).getTransactionDate(),
                            Calendar.DATE),
                    getBalancedDate(strRecordID2InTransit, strInTransitAccountId));

        } catch (Exception e) {
            assertFalse(true);
            log.error("Error when executing query", e);
        } finally {
            OBContext.restorePreviousMode();
        }
    }

    /**
     * Verifies documents can be posted and unposted and that affects fact_acct (RecordID2 and
     * DateBalanced) Case 4: Invoice posted , payment posted and reconciliation posted. After both are
     * posted receivables entry should be balanced. Invoice: I/3 Payment: 400002 Reconciliation:
     * 1000034. When Payment and reconciliation are posted in transit account balance should be 0
     */
    @Test
    public void testRecordID2_case5() {
        // Invoice I/3 from QA dataset
        String strCInvoiceId = "FF8080813282F9FE0132830158E20024";
        String strCAcctSchemaId = "9A68A0F8D72D4580B3EC3CAA00A5E1F0";

        OBContext.setAdminMode(true);
        try {
            Invoice invoice = OBDal.getInstance().get(Invoice.class, strCInvoiceId);
            String strRecordID2 = invoice.getFINPaymentScheduleList().get(0).getId();
            assertEquals("Invoice Not Posted", "Y", invoice.getPosted());
            ResetAccounting.delete(invoice.getClient().getId(), invoice.getOrganization().getId(), "318",
                    invoice.getId(), "", "");
            invoice = OBDal.getInstance().get(Invoice.class, strCInvoiceId);
            String strReceivablesAccountId = getReceivablesAccount(invoice.getBusinessPartner(), strCAcctSchemaId);
            assertEquals("Invoice Could Not Be Unposted", "N", invoice.getPosted());
            assertTrue("RecordID2 not updated after unposting invoice", BigDecimal.ZERO.compareTo(
                    getBalance(strRecordID2, TABLE_INVOICE, strCAcctSchemaId, strReceivablesAccountId)) == 0);
            postDocument(strCInvoiceId, "318", invoice.getClient().getId(), invoice.getOrganization().getId());
            invoice = OBDal.getInstance().get(Invoice.class, strCInvoiceId);
            assertTrue("RecordID2 not updated after posting invoice", new BigDecimal("100").compareTo(
                    getBalance(strRecordID2, TABLE_INVOICE, strCAcctSchemaId, strReceivablesAccountId)) == 0);
            assertEquals("Invoice Could Not be Posted", "Y", invoice.getPosted());

            // Payment: 400002 - 02-04-2011 - Customer A - 100.00 from QA Dataset. Paying previous invoice
            String strFINPaymentId = "FF8080813282F9FE013283022F3E0030";
            FIN_Payment payment = OBDal.getInstance().get(FIN_Payment.class, strFINPaymentId);
            String strInTransitAccountId = getInTransitAccount(payment.getAccount(), strCAcctSchemaId);
            String strRecordID2InTransit = payment.getId();
            assertEquals("Payment Not Posted", "Y", payment.getPosted());
            ResetAccounting.delete(payment.getClient().getId(), payment.getOrganization().getId(), TABLE_PAYMENT,
                    payment.getId(), "", "");
            payment = OBDal.getInstance().get(FIN_Payment.class, strFINPaymentId);
            assertEquals("Payment Could Not Be Unposted", "N", payment.getPosted());
            assertTrue("RecordID2 for receivables not updated after unposting Payment", BigDecimal.ZERO.compareTo(
                    getBalance(strRecordID2, TABLE_PAYMENT, strCAcctSchemaId, strReceivablesAccountId)) == 0);
            assertTrue("RecordID2 for In Transit not updated after unposting Payment",
                    BigDecimal.ZERO.compareTo(getBalance(strRecordID2InTransit, TABLE_PAYMENT, strCAcctSchemaId,
                            strInTransitAccountId)) == 0);
            postDocument(strFINPaymentId, TABLE_PAYMENT, payment.getClient().getId(),
                    payment.getOrganization().getId());
            payment = OBDal.getInstance().get(FIN_Payment.class, strFINPaymentId);
            assertTrue("RecordID2 for receivables not updated after posting Payment",
                    new BigDecimal("-100").compareTo(getBalance(strRecordID2, TABLE_PAYMENT, strCAcctSchemaId,
                            strReceivablesAccountId)) == 0);
            assertTrue("RecordID2 for In Transit not updated after posting Payment",
                    new BigDecimal("100").compareTo(getBalance(strRecordID2InTransit, TABLE_PAYMENT,
                            strCAcctSchemaId, strInTransitAccountId)) == 0);
            assertEquals("Payment Could Not be Posted", "Y", payment.getPosted());
            assertEquals("Entry Not Balanced for Receivables",
                    DateUtils.truncate(payment.getPaymentDate(), Calendar.DATE),
                    getBalancedDate(strRecordID2, strReceivablesAccountId));
            FIN_FinaccTransaction transaction = payment.getFINFinaccTransactionList().get(0);
            FIN_Reconciliation reconciliation = transaction.getReconciliation();
            assertEquals("Reconciliation Not Posted", "Y", reconciliation.getPosted());
            ResetAccounting.delete(reconciliation.getClient().getId(), reconciliation.getOrganization().getId(),
                    TABLE_RECONCILIATION, reconciliation.getId(), "", "");
            reconciliation = OBDal.getInstance().get(FIN_Reconciliation.class, reconciliation.getId());
            assertEquals("Reconciliation Could Not Be Unposted", "N", reconciliation.getPosted());
            assertTrue("RecordID2 for in transit not updated after unposting reconciliation",
                    BigDecimal.ZERO.compareTo(getBalance(strRecordID2InTransit, TABLE_RECONCILIATION,
                            strCAcctSchemaId, strInTransitAccountId)) == 0);
            postDocument(reconciliation.getId(), TABLE_RECONCILIATION, reconciliation.getClient().getId(),
                    reconciliation.getOrganization().getId());
            reconciliation = OBDal.getInstance().get(FIN_Reconciliation.class, reconciliation.getId());
            assertTrue("RecordID2 for in transit not updated after posting reconciliation",
                    new BigDecimal("-100").compareTo(getBalance(strRecordID2InTransit, TABLE_RECONCILIATION,
                            strCAcctSchemaId, strInTransitAccountId)) == 0);
            assertEquals("Reconciliation Could Not be Posted", "Y", reconciliation.getPosted());
            assertEquals("Entry Not Balanced", DateUtils.truncate(transaction.getTransactionDate(), Calendar.DATE),
                    getBalancedDate(strRecordID2InTransit, strInTransitAccountId));

        } catch (Exception e) {
            assertFalse(true);
            log.error("Error when executing query", e);
        } finally {
            OBContext.restorePreviousMode();
        }
    }

    private Date getBalancedDate(String strRecordID2, String strAccountId) {
        String sql = "select max(datebalanced) " //
                + "from fact_Acct where record_ID2 = ?" + " and account_id = ?";

        PreparedStatement sqlQuery = null;
        ResultSet rs = null;
        try {
            sqlQuery = new DalConnectionProvider(false).getPreparedStatement(sql);
            sqlQuery.setString(1, strRecordID2);
            sqlQuery.setString(2, strAccountId);
            sqlQuery.execute();
            sqlQuery.setMaxRows(1);
            rs = sqlQuery.getResultSet();
            while (rs.next()) {
                return rs.getDate(1);
            }
        } catch (Exception e) {
            assertFalse(true);
            log.error("Error when executing query", e);
        } finally {
            try {
                if (sqlQuery != null) {
                    sqlQuery.close();
                }
                if (rs != null) {
                    rs.close();
                }
            } catch (Exception e) {
                log.error("Error when closing statement", e);
            }
        }
        return null;
    }

    private String getReceivablesAccount(BusinessPartner businessPartner, String strCAcctSchemaId) {
        String sql = "select  account_id " //
                + "from c_bp_customer_acct, c_validcombination where c_receivable_acct = c_validcombination_id and c_bp_customer_acct.c_bpartner_id = ? and c_bp_customer_acct.c_acctschema_id = ?";

        PreparedStatement sqlQuery = null;
        ResultSet rs = null;
        try {
            sqlQuery = new DalConnectionProvider(false).getPreparedStatement(sql);
            sqlQuery.setString(1, businessPartner.getId());
            sqlQuery.setString(2, strCAcctSchemaId);
            sqlQuery.execute();
            sqlQuery.setMaxRows(1);
            rs = sqlQuery.getResultSet();
            while (rs.next()) {
                return rs.getString(1);
            }
        } catch (Exception e) {
            assertFalse(true);
            log.error("Error when executing query", e);
        } finally {
            try {
                if (sqlQuery != null) {
                    sqlQuery.close();
                }
                if (rs != null) {
                    rs.close();
                }
            } catch (Exception e) {
                log.error("Error when closing statement", e);
            }
        }
        return null;
    }

    private String getInTransitAccount(FIN_FinancialAccount account, String strCAcctSchemaId) {
        String sql = "select  account_id " //
                + "from fin_financial_account_acct, c_validcombination where fin_in_intransit_acct = c_validcombination_id and fin_financial_account_acct.fin_financial_account_id = ? and fin_financial_account_acct.c_acctschema_id = ?";

        PreparedStatement sqlQuery = null;
        ResultSet rs = null;
        try {
            sqlQuery = new DalConnectionProvider(false).getPreparedStatement(sql);
            sqlQuery.setString(1, account.getId());
            sqlQuery.setString(2, strCAcctSchemaId);
            sqlQuery.execute();
            sqlQuery.setMaxRows(1);
            rs = sqlQuery.getResultSet();
            while (rs.next()) {
                return rs.getString(1);
            }
        } catch (Exception e) {
            assertFalse(true);
            log.error("Error when executing query", e);
        } finally {
            try {
                if (sqlQuery != null) {
                    sqlQuery.close();
                }
                if (rs != null) {
                    rs.close();
                }
            } catch (Exception e) {
                log.error("Error when closing statement", e);
            }
        }
        return null;
    }

    private String getDepositAccount(FIN_FinancialAccount account, String strCAcctSchemaId) {
        String sql = "select  account_id " //
                + "from fin_financial_account_acct, c_validcombination where fin_deposit_acct = c_validcombination_id and fin_financial_account_acct.fin_financial_account_id = ? and fin_financial_account_acct.c_acctschema_id = ?";

        PreparedStatement sqlQuery = null;
        ResultSet rs = null;
        try {
            sqlQuery = new DalConnectionProvider(false).getPreparedStatement(sql);
            sqlQuery.setString(1, account.getId());
            sqlQuery.setString(2, strCAcctSchemaId);
            sqlQuery.execute();
            sqlQuery.setMaxRows(1);
            rs = sqlQuery.getResultSet();
            while (rs.next()) {
                return rs.getString(1);
            }
        } catch (Exception e) {
            assertFalse(true);
            log.error("Error when executing query", e);
        } finally {
            try {
                if (sqlQuery != null) {
                    sqlQuery.close();
                }
                if (rs != null) {
                    rs.close();
                }
            } catch (Exception e) {
                log.error("Error when closing statement", e);
            }
        }
        return null;
    }

    private BigDecimal getBalance(String strRecordID2, String strTableId, String strCAcctSchemaId,
            String strAccountId) {
        String sql = "select coalesce(sum(amtacctdr-amtacctcr),0) " //
                + "from fact_Acct where record_ID2 = ?" + " and ad_table_id = ?"
                + " and c_acctschema_id = ? and account_id = ?";

        PreparedStatement sqlQuery = null;
        ResultSet rs = null;
        try {
            sqlQuery = new DalConnectionProvider(false).getPreparedStatement(sql);
            sqlQuery.setString(1, strRecordID2);
            sqlQuery.setString(2, strTableId);
            sqlQuery.setString(3, strCAcctSchemaId);
            sqlQuery.setString(4, strAccountId);
            sqlQuery.execute();
            sqlQuery.setMaxRows(1);
            rs = sqlQuery.getResultSet();
            while (rs.next()) {
                return rs.getBigDecimal(1);
            }
        } catch (Exception e) {
            assertFalse(true);
            log.error("Error when executing query", e);
        } finally {
            try {
                if (sqlQuery != null) {
                    sqlQuery.close();
                }
                if (rs != null) {
                    rs.close();
                }
            } catch (Exception e) {
                log.error("Error when closing statement", e);
            }
        }
        return BigDecimal.ZERO;
    }

    private void postDocument(String strKey, String strTableId, String strClient, String strOrg)
            throws ServletException {
        ConnectionProvider conn = getConnectionProvider();

        Connection con = null;
        try {
            con = conn.getTransactionConnection();
            AcctServer acct = AcctServer.get(strTableId, strClient, strOrg, conn);
            if (acct == null) {
                conn.releaseRollbackConnection(con);
                return;
            } else if (!acct.post(strKey, false, new VariablesSecureApp("100", strClient, strOrg), conn, con)
                    || acct.errors != 0) {
                conn.releaseRollbackConnection(con);
                return;
            }
            conn.releaseCommitConnection(con);
            OBDal.getInstance().commitAndClose();
        } catch (Exception e) {
            try {
                conn.releaseRollbackConnection(con);
            } catch (Exception ignored) {
            }
        }
        return;
    }

}