FindOverCreditLimitAmountForSoApproval

package org.jleaf.erp.sls.bo.salesorder;

import javax.persistence.Query;

import org.jleaf.core.AbstractBusinessFunction;
import org.jleaf.core.BusinessFunction;
import org.jleaf.core.Dto;
import org.jleaf.core.GeneralConstants;
import org.jleaf.core.annotation.ErrorList;
import org.jleaf.core.annotation.Info;
import org.jleaf.core.annotation.InfoIn;
import org.jleaf.core.annotation.InfoOut;
import org.jleaf.core.dao.QueryBuilder;
import org.jleaf.erp.inv.InventoryConstants;
import org.jleaf.erp.inv.dao.DeliveryOrderReceiptDao;
import org.jleaf.erp.inv.dao.InventoryDao;
import org.jleaf.erp.inv.entity.BalanceDeliveryOrderItem;
import org.jleaf.erp.inv.entity.DeliveryOrderReceipt;
import org.jleaf.erp.inv.entity.DeliveryOrderReceiptItem;
import org.jleaf.erp.inv.entity.Inventory;
import org.jleaf.erp.inv.entity.InventoryItem;
import org.jleaf.erp.master.entity.PartnerType;
import org.jleaf.erp.master.entity.Tax;
import org.jleaf.erp.sls.SalesConstants;
import org.jleaf.erp.sls.SalesConstantsForXcom;
import org.jleaf.erp.sls.dao.DeliveryOrderDao;
import org.jleaf.erp.sls.dao.ManageSalesOrderItemDao;
import org.jleaf.erp.sls.dao.SalesOrderBalanceInvoiceDao;
import org.jleaf.erp.sls.dao.SalesOrderBalanceInvoiceTaxDao;
import org.jleaf.erp.sls.dao.SalesOrderBalanceItemDao;
import org.jleaf.erp.sls.dao.SalesOrderDao;
import org.jleaf.erp.sls.dao.SalesOrderItemDao;
import org.jleaf.erp.sls.entity.DeliveryOrder;
import org.jleaf.erp.sls.entity.DeliveryOrderItem;
import org.jleaf.erp.sls.entity.ManageSalesOrder;
import org.jleaf.erp.sls.entity.ManageSalesOrderHeader;
import org.jleaf.erp.sls.entity.ManageSalesOrderItem;
import org.jleaf.erp.sls.entity.SalesOrder;
import org.jleaf.erp.sls.entity.SalesOrderBalanceInvoice;
import org.jleaf.erp.sls.entity.SalesOrderBalanceInvoiceTax;
import org.jleaf.erp.sls.entity.SalesOrderBalanceItem;
import org.jleaf.erp.sls.entity.SalesOrderItem;
import org.jleaf.util.Calc;
import org.jleaf.util.ValidationUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

/**
 *
 * @author Adrian
 * Apr 3, 2017
 */
/**
 * Menambahkan perhitungan:
 * - nilai AR saat ini
 * - nilai outstanding AR
 * - nilai SO yang belum di-DO
 * - nilai SO yang sedang dibuat saat ini
 * modified by Adrian
 * Dec 6, 2017
 */

//@formatter:off
@Service
@InfoIn(value = {
        @Info(name = "tenantId", description = "tenant id", type = Long.class),
        @Info(name = "ouId", description = "ou id", type = Long.class),
        @Info(name = "partnerId", description = "partner id", type = Long.class),
        @Info(name = "manageSoId", description = "manage so id", type = Long.class),
        @Info(name = "manageSoDocTypeId", description = "manage so doc type id", type = Long.class)
})
@InfoOut(value = {
        @Info(name = "overCreditLimitAmount", description = "over credit limit amount", type = Double.class) })
@ErrorList(errorKeys = {

})
//@formatter:on
public class FindOverCreditLimitAmountForSoApproval extends AbstractBusinessFunction implements BusinessFunction {

    @Autowired
    SalesOrderDao salesOrderDao;

    @Autowired
    SalesOrderBalanceInvoiceDao salesOrderBalanceInvoiceDao;

    @Autowired
    SalesOrderBalanceInvoiceTaxDao salesOrderBalanceInvoiceTaxDao;

    @Autowired
    DeliveryOrderDao deliveryOrderDao;

    @Autowired
    DeliveryOrderReceiptDao deliveryOrderReceiptDao;

    @Autowired
    InventoryDao inventoryDao;

    @Autowired
    SalesOrderBalanceItemDao salesOrderBalanceItemDao;

    @Autowired
    SalesOrderItemDao salesOrderItemDao;

    @Autowired
    ManageSalesOrderItemDao manageSalesOrderItemDao;

    public String getDescription() {
        // write description of this business function here
        return "find over credit limit amount";
    }

    @SuppressWarnings("unchecked")
    public Dto execute(Dto inputDto) throws Exception {
        // validate inputDto have key in @InfoIn
        ValidationUtil.valDtoContainsKey(inputDto, "tenantId");
        ValidationUtil.valDtoContainsKey(inputDto, "ouId");
        ValidationUtil.valDtoContainsKey(inputDto, "partnerId");
        ValidationUtil.valDtoContainsKey(inputDto, "manageSoId");
        ValidationUtil.valDtoContainsKey(inputDto, "manageSoDocTypeId");

        Long manageSoId = inputDto.getLong("manageSoId");
        Long manageSoDocTypeId = inputDto.getLong("manageSoDocTypeId");

        // @formatter:off
        // create native sql
        // Mendapatkan limit customer
        // dan mengurangkannya dengan nilai AR saat ini
        QueryBuilder builder = new QueryBuilder();
        builder.add(" WITH os_invoice AS ( ")
                .add(" SELECT COALESCE(SUM(A.amount - A.payment_amount), 0) AS os_amount FROM ")
                .add(SalesConstantsForXcom.INVOICE_AR_BALANCE_TABLE_NAME)
                .add(" A LEFT OUTER JOIN ")
                .add(SalesOrder.TABLE_NAME)
                .add(" B ON A.ref_doc_type_id = B.doc_type_id AND A.ref_id = B.so_id ")
                .add(" WHERE A.tenant_id = :tenantId ")
                .add(" AND A.ou_id = :ouId ")
                .add(" AND A.partner_id = :partnerId ")
                .add(" AND A.due_date BETWEEN :startDate AND :endDate ")
                .add(" AND A.flg_payment = :NO )")
                .add(" SELECT (A.amount_limit - B.os_amount) FROM ")
                .add(PartnerType.TABLE_NAME)
                .add(" A, os_invoice B ")
                .add(" WHERE A.partner_id = :partnerId ")
                .add(" AND A.group_partner = :CUSTOMER ");
        // @formatter:on

        // create native query
        Query q = salesOrderDao.createNativeQuery(builder.toString());

        // set query parameter (if any)
        q.setParameter("tenantId", inputDto.getLong("tenantId"));
        q.setParameter("ouId", inputDto.getLong("ouId"));
        q.setParameter("partnerId", inputDto.getLong("partnerId"));
        q.setParameter("startDate", GeneralConstants.START_DATE);
        q.setParameter("endDate", GeneralConstants.END_DATE);
        q.setParameter("NO", GeneralConstants.NO);
        q.setParameter("CUSTOMER", SalesConstantsForXcom.GROUP_PARTNER_CUSTOMER);

        Object amount = q.getSingleResult();

        // @formatter:off
        // create native sql
        // Mendapatkan nilai tax AR saat ini
        QueryBuilder builderTax = new QueryBuilder();
        builderTax.add(" SELECT COALESCE(SUM(A.gov_tax_amount - A.payment_amount), 0) AS os_amount FROM ")
                .add(SalesConstantsForXcom.INVOICE_TAX_AR_BALANCE_TABLE_NAME)
                .add(" A INNER JOIN ")
                .add(SalesConstantsForXcom.INVOICE_AR_BALANCE_TABLE_NAME)
                .add(" B ON A.invoice_ar_balance_id = B.invoice_ar_balance_id ")
                .add(" WHERE A.tenant_id = :tenantId ")
                .add(" AND A.ou_id = :ouId ")
                .add(" AND A.partner_id = :partnerId ")
                .add(" AND A.due_date BETWEEN :startDate AND :endDate ")
                .add(" AND A.flg_payment = :NO ");
        // @formatter:on

        // create native query
        Query qTax = salesOrderDao.createNativeQuery(builderTax.toString());

        // set query parameter (if any)
        qTax.setParameter("tenantId", inputDto.getLong("tenantId"));
        qTax.setParameter("ouId", inputDto.getLong("ouId"));
        qTax.setParameter("partnerId", inputDto.getLong("partnerId"));
        qTax.setParameter("startDate", GeneralConstants.START_DATE);
        qTax.setParameter("endDate", GeneralConstants.END_DATE);
        qTax.setParameter("NO", GeneralConstants.NO);

        Object taxAmount = qTax.getSingleResult();

        // limit customer - nilai AR saat ini - nilai tax AR saat ini
        Calc overCreditLimitAmount = new Calc(Double.valueOf(amount.toString())).subtract(Double.valueOf(taxAmount.toString()));


        //Mendapatkan nilai outstanding AR (DO yang belum jadi invoice)
        QueryBuilder builderOsAr = new QueryBuilder();
        builderOsAr.add(" SELECT COALESCE(SUM( ")
                    .add(" CASE WHEN A.ref_doc_type_id = :RETURN_NOTE_DOC_TYPE_ID THEN ")
                        .add(" -1 * A.item_amount ")
                    .add(" ELSE ")
                        .add(" A.item_amount ")
                    .add(" END ")
                    .add(" ), 0) FROM ")
                .add(SalesOrderBalanceInvoice.TABLE_NAME)
                .add(" A ")
                .add(" WHERE A.tenant_id = :tenantId ")
                .add(" AND A.ou_id = :ouId ")
                .add(" AND A.partner_id = :partnerId ")
                .add(" AND A.flg_invoice IN (:NO, :IN_PROGRESS) ")
                .add(" AND A.invoice_id = :EMPTY_ID ");

        Query queryOsAr = salesOrderBalanceInvoiceDao.createNativeQuery(builderOsAr.toString());

        queryOsAr.setParameter("tenantId", inputDto.getLong("tenantId"));
        queryOsAr.setParameter("ouId", inputDto.getLong("ouId"));
        queryOsAr.setParameter("partnerId", inputDto.getLong("partnerId"));
        queryOsAr.setParameter("NO", GeneralConstants.NO);
        queryOsAr.setParameter("IN_PROGRESS", SalesConstants.IN_PROGRESS_TRANSACTION);
        queryOsAr.setParameter("EMPTY_ID", GeneralConstants.NULL_REF_VALUE_LONG);
        queryOsAr.setParameter("RETURN_NOTE_DOC_TYPE_ID", InventoryConstants.DOCUMENT_RETURN_NOTE);

        Object osArAmount = queryOsAr.getSingleResult();

        //over credit limit amount dikurangi dengan nilai outstanding AR
        overCreditLimitAmount = overCreditLimitAmount.subtract(Double.valueOf(osArAmount.toString()));


        //Mendapatkan nilai tax outstanding AR (DO yang belum jadi invoice)
        QueryBuilder builderOsArTax = new QueryBuilder();
        builderOsArTax.add(" SELECT COALESCE(SUM( ")
                    .add(" CASE WHEN A.ref_doc_type_id = :RETURN_NOTE_DOC_TYPE_ID THEN ")
                        .add(" -1 * A.tax_amount ")
                    .add(" ELSE ")
                        .add(" A.tax_amount ")
                    .add(" END ")
                .add(" ), 0) FROM ")
                .add(SalesOrderBalanceInvoiceTax.TABLE_NAME)
                .add(" A ")
                .add(" WHERE A.tenant_id = :tenantId ")
                .add(" AND A.ou_id = :ouId ")
                .add(" AND A.partner_id = :partnerId ")
                .add(" AND A.flg_invoice IN (:NO, :IN_PROGRESS) ")
                .add(" AND A.invoice_id = :EMPTY_ID ");

        Query queryOsArTax = salesOrderBalanceInvoiceTaxDao.createNativeQuery(builderOsArTax.toString());

        queryOsArTax.setParameter("tenantId", inputDto.getLong("tenantId"));
        queryOsArTax.setParameter("ouId", inputDto.getLong("ouId"));
        queryOsArTax.setParameter("partnerId", inputDto.getLong("partnerId"));
        queryOsArTax.setParameter("NO", GeneralConstants.NO);
        queryOsArTax.setParameter("IN_PROGRESS", SalesConstants.IN_PROGRESS_TRANSACTION);
        queryOsArTax.setParameter("EMPTY_ID", GeneralConstants.NULL_REF_VALUE_LONG);
        queryOsArTax.setParameter("RETURN_NOTE_DOC_TYPE_ID", InventoryConstants.DOCUMENT_RETURN_NOTE);

        Object osArTaxAmount = queryOsArTax.getSingleResult();

        //over credit limit amount dikurangi dengan nilai tax outstanding AR
        overCreditLimitAmount = overCreditLimitAmount.subtract(Double.valueOf(osArTaxAmount.toString()));


        //Mendapatkan nilai outstanding AR (DO yang belum APPROVED)
        QueryBuilder builderOsArNotApprovedDo = new QueryBuilder();
        builderOsArNotApprovedDo.add(" SELECT COALESCE(SUM( ")
                    .add(" f_get_amount_before_tax_and_disc( ")
                        .add(" B.qty_dlv_so * C.gross_sell_price, ")
                        .add(" B.qty_dlv_so * C.discount_amount, ")
                        .add(" C.flg_tax_amount, ")
                        .add(" C.tax_percentage, ")
                        .add(" f_get_digit_decimal_doc_curr(:DO_DOC_TYPE_ID, C.curr_code), ")
                        .add(" f_get_value_system_config_by_param_code(:tenantId, :ROUNDING_MODE_NON_TAX))), 0) ")
                .add(" FROM ")
                .add(DeliveryOrder.TABLE_NAME)
                .add(" A ")
                .add(" INNER JOIN ")
                .add(DeliveryOrderItem.TABLE_NAME)
                .add(" B ON A.do_id = B.do_id ")
                .add(" INNER JOIN ")
                .add(SalesOrderItem.TABLE_NAME)
                .add(" C ON B.ref_id = C.so_item_id ")
                .add(" INNER JOIN ")
                .add(SalesOrder.TABLE_NAME)
                .add(" D ON C.so_id = D.so_id ")
                .add(" WHERE A.tenant_id = :tenantId ")
                .add(" AND A.ou_id = :ouId ")
                .add(" AND D.partner_bill_to_id = :partnerId ")
                .add(" AND A.status_doc IN (:DRAFT, :IN_PROGRESS) ");

        Query queryOsArNotApprovedDo = deliveryOrderDao.createNativeQuery(builderOsArNotApprovedDo.toString());

        queryOsArNotApprovedDo.setParameter("tenantId", inputDto.getLong("tenantId"));
        queryOsArNotApprovedDo.setParameter("ouId", inputDto.getLong("ouId"));
        queryOsArNotApprovedDo.setParameter("partnerId", inputDto.getLong("partnerId"));
        queryOsArNotApprovedDo.setParameter("DO_DOC_TYPE_ID", SalesConstants.DOCUMENT_DELIVERY_ORDER);
        queryOsArNotApprovedDo.setParameter("ROUNDING_MODE_NON_TAX", "rounding.mode.non.tax");
        queryOsArNotApprovedDo.setParameter("DRAFT", SalesConstants.DRAFT_TRANSACTION);
        queryOsArNotApprovedDo.setParameter("IN_PROGRESS", SalesConstants.IN_PROGRESS_TRANSACTION);

        Object osArNotApprovedDoAmount = queryOsArNotApprovedDo.getSingleResult();

        //over credit limit amount dikurangi dengan nilai outstanding AR (DO yang belum APPROVED)
        overCreditLimitAmount = overCreditLimitAmount.subtract(Double.valueOf(osArNotApprovedDoAmount.toString()));


        //Mendapatkan nilai tax outstanding AR (DO yang belum APPROVED)
        QueryBuilder builderOsArTaxNotApprovedDo = new QueryBuilder();
        builderOsArTaxNotApprovedDo.add(" SELECT COALESCE(SUM( ")
                .add(" f_tax_rounding( ")
                        .add(" A.tenant_id, ")
                        .add(" f_get_amount_before_tax( ")
                                .add(" B.qty_dlv_so * (C.gross_sell_price - C.discount_amount), ")
                                .add(" C.flg_tax_amount, ")
                                .add(" C.tax_percentage, ")
                                .add(" f_get_digit_decimal_doc_curr(:DO_DOC_TYPE_ID, C.curr_code), ")
                                .add(" f_get_value_system_config_by_param_code(:tenantId, :ROUNDING_MODE_NON_TAX)), ")
                        .add(" C.tax_percentage)), 0) ")
                .add(" FROM ")
                .add(DeliveryOrder.TABLE_NAME)
                .add(" A ")
                .add(" INNER JOIN ")
                .add(DeliveryOrderItem.TABLE_NAME)
                .add(" B ON A.do_id = B.do_id ")
                .add(" INNER JOIN ")
                .add(SalesOrderItem.TABLE_NAME)
                .add(" C ON B.ref_id = C.so_item_id ")
                .add(" INNER JOIN ")
                .add(Tax.TABLE_NAME)
                .add(" D ON C.tax_id = D.tax_id ")
                .add(" INNER JOIN ")
                .add(SalesOrder.TABLE_NAME)
                .add(" E ON C.so_id = E.so_id ")
                .add(" WHERE A.tenant_id = :tenantId ")
                .add(" AND A.ou_id = :ouId ")
                .add(" AND E.partner_bill_to_id = :partnerId ")
                .add(" AND A.status_doc IN (:DRAFT, :IN_PROGRESS) ");

        Query queryOsArTaxNotApprovedDo = deliveryOrderDao.createNativeQuery(builderOsArTaxNotApprovedDo.toString());

        queryOsArTaxNotApprovedDo.setParameter("tenantId", inputDto.getLong("tenantId"));
        queryOsArTaxNotApprovedDo.setParameter("ouId", inputDto.getLong("ouId"));
        queryOsArTaxNotApprovedDo.setParameter("partnerId", inputDto.getLong("partnerId"));
        queryOsArTaxNotApprovedDo.setParameter("DO_DOC_TYPE_ID", SalesConstants.DOCUMENT_DELIVERY_ORDER);
        queryOsArTaxNotApprovedDo.setParameter("ROUNDING_MODE_NON_TAX", "rounding.mode.non.tax");
        queryOsArTaxNotApprovedDo.setParameter("DRAFT", SalesConstants.DRAFT_TRANSACTION);
        queryOsArTaxNotApprovedDo.setParameter("IN_PROGRESS", SalesConstants.IN_PROGRESS_TRANSACTION);

        Object osArTaxNotApprovedDoAmount = queryOsArTaxNotApprovedDo.getSingleResult();

        //over credit limit amount dikurangi dengan nilai tax outstanding AR (DO yang belum APPROVED)
        overCreditLimitAmount = overCreditLimitAmount.subtract(Double.valueOf(osArTaxNotApprovedDoAmount.toString()));


        //Mendapatkan nilai outstanding AR (DO Receipt yang belum APPROVED)
        QueryBuilder builderOsArNotApprovedDoReceipt = new QueryBuilder();
        builderOsArNotApprovedDoReceipt.add(" SELECT COALESCE(SUM( ")
                    .add(" -1 * f_get_amount_before_tax_and_disc( ")
                            .add(" (B.qty_return * D.qty_so / D.qty_int) * D.gross_sell_price, ")
                            .add(" (G.regular_disc_amount + G.promo_disc_amount) * (B.qty_return * D.qty_so / D.qty_int) / G.qty_dlv_so, ")
                            .add(" D.flg_tax_amount, ")
                            .add(" D.tax_percentage, ")
                            .add(" f_get_digit_decimal_doc_curr(:DO_RECEIPT_DOC_TYPE_ID, D.curr_code), ")
                            .add(" f_get_value_system_config_by_param_code(:tenantId, :ROUNDING_MODE_NON_TAX))), 0) ")
                .add(" FROM ")
                .add(DeliveryOrderReceipt.TABLE_NAME)
                .add(" A ")
                .add(" INNER JOIN ")
                .add(DeliveryOrderReceiptItem.TABLE_NAME)
                .add(" B ON A.do_receipt_id = B.do_receipt_id ")
                .add(" INNER JOIN ")
                .add(BalanceDeliveryOrderItem.TABLE_NAME)
                .add(" C ON B.ref_id = C.do_item_id ")
                .add(" INNER JOIN ")
                .add(SalesOrderItem.TABLE_NAME)
                .add(" D ON C.so_item_id = D.so_item_id ")
                .add(" INNER JOIN ")
                .add(SalesOrder.TABLE_NAME)
                .add(" E ON D.so_id = E.so_id ")
                .add(" INNER JOIN ")
                .add(DeliveryOrder.TABLE_NAME)
                .add(" F ON A.ref_id = F.do_id ")
                .add(" INNER JOIN ")
                .add(SalesOrderBalanceInvoice.TABLE_NAME)
                .add(" G ON G.ref_id = F.do_id AND G.ref_item_id = C.do_item_id ")
                .add(" WHERE A.tenant_id = :tenantId ")
                .add(" AND A.ou_id = :ouId ")
                .add(" AND E.partner_bill_to_id = :partnerId ")
                .add(" AND A.status_doc IN (:DRAFT, :IN_PROGRESS) ")
                .add(" AND B.qty_return > 0 ")
                .add(" AND G.do_receipt_item_id = :EMPTY_ID ");

        Query queryOsArNotApprovedDoReceipt = deliveryOrderReceiptDao.createNativeQuery(builderOsArNotApprovedDoReceipt.toString());

        queryOsArNotApprovedDoReceipt.setParameter("tenantId", inputDto.getLong("tenantId"));
        queryOsArNotApprovedDoReceipt.setParameter("ouId", inputDto.getLong("ouId"));
        queryOsArNotApprovedDoReceipt.setParameter("partnerId", inputDto.getLong("partnerId"));
        queryOsArNotApprovedDoReceipt.setParameter("DO_RECEIPT_DOC_TYPE_ID", InventoryConstants.DOCUMENT_DELIVERY_ORDER_RECEIPT);
        queryOsArNotApprovedDoReceipt.setParameter("ROUNDING_MODE_NON_TAX", "rounding.mode.non.tax");
        queryOsArNotApprovedDoReceipt.setParameter("DRAFT", SalesConstants.DRAFT_TRANSACTION);
        queryOsArNotApprovedDoReceipt.setParameter("IN_PROGRESS", SalesConstants.IN_PROGRESS_TRANSACTION);
        queryOsArNotApprovedDoReceipt.setParameter("EMPTY_ID", GeneralConstants.NULL_REF_VALUE_LONG);

        Object osArNotApprovedDoReceiptAmount = queryOsArNotApprovedDoReceipt.getSingleResult();

        //over credit limit amount dikurangi dengan nilai outstanding AR (DO Receipt yang belum APPROVED)
        overCreditLimitAmount = overCreditLimitAmount.subtract(Double.valueOf(osArNotApprovedDoReceiptAmount.toString()));


        //Mendapatkan nilai tax outstanding AR (DO Receipt yang belum APPROVED)
        QueryBuilder builderOsArTaxNotApprovedDoReceipt = new QueryBuilder();
        builderOsArTaxNotApprovedDoReceipt.add(" SELECT COALESCE(SUM( ")
                    .add(" -1 * f_tax_rounding( ")
                            .add(" A.tenant_id, ")
                            .add(" f_get_amount_before_tax( ")
                                .add(" (B.qty_return * D.qty_so / D.qty_int) * (D.gross_sell_price - D.discount_amount), ")
                                .add(" D.flg_tax_amount, ")
                                .add(" D.tax_percentage, ")
                                .add(" f_get_digit_decimal_doc_curr(:DO_RECEIPT_DOC_TYPE_ID, D.curr_code), ")
                                .add(" f_get_value_system_config_by_param_code(:tenantId, :ROUNDING_MODE_NON_TAX)), ")
                            .add(" D.tax_percentage)), 0) ")
                .add(" FROM ")
                .add(DeliveryOrderReceipt.TABLE_NAME)
                .add(" A ")
                .add(" INNER JOIN ")
                .add(DeliveryOrderReceiptItem.TABLE_NAME)
                .add(" B ON A.do_receipt_id = B.do_receipt_id ")
                .add(" INNER JOIN ")
                .add(BalanceDeliveryOrderItem.TABLE_NAME)
                .add(" C ON B.ref_id = C.do_item_id ")
                .add(" INNER JOIN ")
                .add(SalesOrderItem.TABLE_NAME)
                .add(" D ON C.so_item_id = D.so_item_id ")
                .add(" INNER JOIN ")
                .add(Tax.TABLE_NAME)
                .add(" E ON D.tax_id = E.tax_id ")
                .add(" INNER JOIN ")
                .add(SalesOrder.TABLE_NAME)
                .add(" F ON  D.so_id = F.so_id ")
                .add(" WHERE A.tenant_id = :tenantId ")
                .add(" AND A.ou_id = :ouId ")
                .add(" AND F.partner_bill_to_id = :partnerId ")
                .add(" AND A.status_doc IN (:DRAFT, :IN_PROGRESS) ")
                .add(" AND B.qty_return > 0 ");

        Query queryOsArTaxNotApprovedDoReceipt = deliveryOrderReceiptDao.createNativeQuery(builderOsArTaxNotApprovedDoReceipt.toString());

        queryOsArTaxNotApprovedDoReceipt.setParameter("tenantId", inputDto.getLong("tenantId"));
        queryOsArTaxNotApprovedDoReceipt.setParameter("ouId", inputDto.getLong("ouId"));
        queryOsArTaxNotApprovedDoReceipt.setParameter("partnerId", inputDto.getLong("partnerId"));
        queryOsArTaxNotApprovedDoReceipt.setParameter("DO_RECEIPT_DOC_TYPE_ID", InventoryConstants.DOCUMENT_DELIVERY_ORDER_RECEIPT);
        queryOsArTaxNotApprovedDoReceipt.setParameter("ROUNDING_MODE_NON_TAX", "rounding.mode.non.tax");
        queryOsArTaxNotApprovedDoReceipt.setParameter("DRAFT", SalesConstants.DRAFT_TRANSACTION);
        queryOsArTaxNotApprovedDoReceipt.setParameter("IN_PROGRESS", SalesConstants.IN_PROGRESS_TRANSACTION);

        Object osArTaxNotApprovedDoReceiptAmount = queryOsArTaxNotApprovedDoReceipt.getSingleResult();

        //over credit limit amount dikurangi dengan nilai tax outstanding AR (DO Receipt yang belum APPROVED)
        overCreditLimitAmount = overCreditLimitAmount.subtract(Double.valueOf(osArTaxNotApprovedDoReceiptAmount.toString()));


        //Mendapatkan nilai outstanding AR (Return Note yang belum APPROVED)
        QueryBuilder builderOsArNotApprovedReturnNote = new QueryBuilder();
        builderOsArNotApprovedReturnNote.add(" SELECT COALESCE(SUM( ")
                    .add(" -1 * f_get_amount_before_tax_and_disc( ")
                        .add(" (B.qty_realization * D.qty_so * D.gross_sell_price) / D.qty_int, ")
                        .add(" (F.regular_disc_amount + F.promo_disc_amount) * (B.qty_realization * D.qty_so) / (D.qty_int / F.qty_dlv_so), ")
                        .add(" D.flg_tax_amount, ")
                        .add(" D.tax_percentage, ")
                        .add(" f_get_digit_decimal_doc_curr(:RETURN_NOTE_DOC_TYPE_ID, D.curr_code), ")
                        .add(" f_get_value_system_config_by_param_code(:tenantId, :ROUNDING_MODE_NON_TAX))), 0) ")
                .add(" FROM ")
                .add(Inventory.TABLE_NAME)
                .add(" A ")
                .add(" INNER JOIN ")
                .add(InventoryItem.TABLE_NAME)
                .add(" B ON A.inventory_id = B.inventory_id ")
                .add(" INNER JOIN ")
                .add(BalanceDeliveryOrderItem.TABLE_NAME)
                .add(" C ON B.ref_id = C.do_item_id ")
                .add(" INNER JOIN ")
                .add(SalesOrderItem.TABLE_NAME)
                .add(" D ON C.so_item_id = D.so_item_id ")
                .add(" INNER JOIN ")
                .add(SalesOrder.TABLE_NAME)
                .add(" E ON D.so_id = E.so_id ")
                .add(" INNER JOIN ")
                .add(SalesOrderBalanceInvoice.TABLE_NAME)
                .add(" F ON F.ref_id = B.ref_id AND F.ref_item_id = B.ref_item_id AND F.do_receipt_item_id = :EMPTY_ID ")
                .add(" WHERE A.tenant_id = :tenantId ")
                .add(" AND A.ou_from_id = :ouId ")
                .add(" AND E.partner_bill_to_id = :partnerId ")
                .add(" AND A.status_doc IN (:DRAFT, :IN_PROGRESS) ");

        Query queryOsArNotApprovedReturnNote = inventoryDao.createNativeQuery(builderOsArNotApprovedReturnNote.toString());

        queryOsArNotApprovedReturnNote.setParameter("tenantId", inputDto.getLong("tenantId"));
        queryOsArNotApprovedReturnNote.setParameter("ouId", inputDto.getLong("ouId"));
        queryOsArNotApprovedReturnNote.setParameter("partnerId", inputDto.getLong("partnerId"));
        queryOsArNotApprovedReturnNote.setParameter("RETURN_NOTE_DOC_TYPE_ID", InventoryConstants.DOCUMENT_RETURN_NOTE);
        queryOsArNotApprovedReturnNote.setParameter("ROUNDING_MODE_NON_TAX", "rounding.mode.non.tax");
        queryOsArNotApprovedReturnNote.setParameter("DRAFT", SalesConstants.DRAFT_TRANSACTION);
        queryOsArNotApprovedReturnNote.setParameter("IN_PROGRESS", SalesConstants.IN_PROGRESS_TRANSACTION);
        queryOsArNotApprovedReturnNote.setParameter("EMPTY_ID", GeneralConstants.NULL_REF_VALUE_LONG);

        Object osArNotApprovedReturnNoteAmount = queryOsArNotApprovedReturnNote.getSingleResult();

        //over credit limit amount dikurangi dengan nilai outstanding AR (Return Note yang belum APPROVED)
        overCreditLimitAmount = overCreditLimitAmount.subtract(Double.valueOf(osArNotApprovedReturnNoteAmount.toString()));


        //Mendapatkan nilai tax outstanding AR (Return Note yang belum APPROVED)
        QueryBuilder builderOsArTaxNotApprovedReturnNote = new QueryBuilder();
        builderOsArTaxNotApprovedReturnNote.add(" SELECT COALESCE(SUM( ")
                    .add(" -1 * f_tax_rounding( ")
                        .add(" A.tenant_id, ")
                        .add(" f_get_amount_before_tax( ")
                            .add(" (B.qty_realization * D.qty_so * (D.gross_sell_price - G.discount_amount)) / D.qty_int, ")
                            .add(" D.flg_tax_amount, ")
                            .add(" D.tax_percentage, ")
                            .add(" f_get_digit_decimal_doc_curr(:RETURN_NOTE_DOC_TYPE_ID, D.curr_code), ")
                            .add(" f_get_value_system_config_by_param_code(:tenantId, :ROUNDING_MODE_NON_TAX)), ")
                        .add(" D.tax_percentage)), 0) ")
                .add(" FROM ")
                .add(Inventory.TABLE_NAME)
                .add(" A ")
                .add(" INNER JOIN ")
                .add(InventoryItem.TABLE_NAME)
                .add(" B ON A.inventory_id = B.inventory_id ")
                .add(" INNER JOIN ")
                .add(BalanceDeliveryOrderItem.TABLE_NAME)
                .add(" C ON B.ref_id = C.do_item_id ")
                .add(" INNER JOIN ")
                .add(SalesOrderItem.TABLE_NAME)
                .add(" D ON C.so_item_id = D.so_item_id ")
                .add(" INNER JOIN ")
                .add(SalesOrder.TABLE_NAME)
                .add(" E ON D.so_id = E.so_id ")
                .add(" INNER JOIN ")
                .add(Tax.TABLE_NAME)
                .add(" F ON D.tax_id = F.tax_id ")
                .add(" INNER JOIN ")
                .add(SalesOrderItem.TABLE_NAME)
                .add(" G ON D.so_item_id = G.so_item_id ")
                .add(" WHERE A.tenant_id = :tenantId ")
                .add(" AND A.ou_from_id = :ouId ")
                .add(" AND E.partner_bill_to_id = :partnerId ")
                .add(" AND A.status_doc IN (:DRAFT, :IN_PROGRESS) ");

        Query queryOsArTaxNotApprovedReturnNote = inventoryDao.createNativeQuery(builderOsArTaxNotApprovedReturnNote.toString());

        queryOsArTaxNotApprovedReturnNote.setParameter("tenantId", inputDto.getLong("tenantId"));
        queryOsArTaxNotApprovedReturnNote.setParameter("ouId", inputDto.getLong("ouId"));
        queryOsArTaxNotApprovedReturnNote.setParameter("partnerId", inputDto.getLong("partnerId"));
        queryOsArTaxNotApprovedReturnNote.setParameter("RETURN_NOTE_DOC_TYPE_ID", InventoryConstants.DOCUMENT_RETURN_NOTE);
        queryOsArTaxNotApprovedReturnNote.setParameter("ROUNDING_MODE_NON_TAX", "rounding.mode.non.tax");
        queryOsArTaxNotApprovedReturnNote.setParameter("DRAFT", SalesConstants.DRAFT_TRANSACTION);
        queryOsArTaxNotApprovedReturnNote.setParameter("IN_PROGRESS", SalesConstants.IN_PROGRESS_TRANSACTION);

        Object osArTaxNotApprovedReturnNoteAmount = queryOsArTaxNotApprovedReturnNote.getSingleResult();

        //over credit limit amount dikurangi dengan nilai tax outstanding AR (Return Note yang belum APPROVED)
        overCreditLimitAmount = overCreditLimitAmount.subtract(Double.valueOf(osArTaxNotApprovedReturnNoteAmount.toString()));


        //Mendapatkan nilai SO yang belum di-DO
        QueryBuilder builderOsSo = new QueryBuilder();
        builderOsSo.add(" SELECT COALESCE(SUM((A.qty_so - A.qty_cancel + A.qty_add - A.qty_dlv) * (B.gross_sell_price - B.discount_amount)), 0)")
                .add(" FROM ")
                .add(SalesOrderBalanceItem.TABLE_NAME)
                .add(" A ")
                .add(" INNER JOIN ")
                .add(SalesOrderItem.TABLE_NAME)
                .add(" B ON A.so_item_id = B.so_item_id ")
                .add(" INNER JOIN ")
                .add(SalesOrder.TABLE_NAME)
                .add(" C ON B.so_id = C.so_id ")
                .add(" WHERE A.tenant_id = :tenantId ")
                .add(" AND A.ou_id = :ouId ")
                .add(" AND C.partner_bill_to_id = :partnerId ")
                .add(" AND A.status_item = :RELEASED ");

        Query queryOsSo = salesOrderBalanceItemDao.createNativeQuery(builderOsSo.toString());

        queryOsSo.setParameter("tenantId", inputDto.getLong("tenantId"));
        queryOsSo.setParameter("ouId", inputDto.getLong("ouId"));
        queryOsSo.setParameter("partnerId", inputDto.getLong("partnerId"));
        queryOsSo.setParameter("RELEASED", SalesConstantsForXcom.STATUS_DOC_RELEASED);

        Object osSoAmount = queryOsSo.getSingleResult();

        //over credit limit amount dikurangi dengan nilai SO yang belum di-DO
        overCreditLimitAmount = overCreditLimitAmount.subtract(Double.valueOf(osSoAmount.toString()));


        //Mendapatkan nilai SO yang belum APPROVED atau sedang di-manage
        if(GeneralConstants.NULL_REF_VALUE_LONG.equals(manageSoId)){
            //Semua SO yang belum APPROVED, termasuk SO yang sedang dibuat
            QueryBuilder builderNotApprovedSo = new QueryBuilder();
            builderNotApprovedSo.add(" SELECT COALESCE(SUM(A.qty_so * (A.gross_sell_price - A.discount_amount)), 0)")
                    .add(" FROM ")
                    .add(SalesOrderItem.TABLE_NAME)
                    .add(" A ")
                    .add(" INNER JOIN ")
                    .add(SalesOrder.TABLE_NAME)
                    .add(" B ON A.so_id = B.so_id ")
                    .add(" WHERE B.tenant_id = :tenantId ")
                    .add(" AND B.ou_id = :ouId ")
                    .add(" AND B.partner_bill_to_id = :partnerId ")
                    .add(" AND B.status_doc IN (:DRAFT, :IN_PROGRESS) ");

            Query queryNotApprovedSo = salesOrderItemDao.createNativeQuery(builderNotApprovedSo.toString());

            queryNotApprovedSo.setParameter("tenantId", inputDto.getLong("tenantId"));
            queryNotApprovedSo.setParameter("ouId", inputDto.getLong("ouId"));
            queryNotApprovedSo.setParameter("partnerId", inputDto.getLong("partnerId"));
            queryNotApprovedSo.setParameter("DRAFT", SalesConstants.DRAFT_TRANSACTION);
            queryNotApprovedSo.setParameter("IN_PROGRESS", SalesConstants.IN_PROGRESS_TRANSACTION);

            Object notApprovedSoAmount = queryNotApprovedSo.getSingleResult();

            //over credit limit amount dikurangi dengan nilai SO yang belum APPROVED
            overCreditLimitAmount = overCreditLimitAmount.subtract(Double.valueOf(notApprovedSoAmount.toString()));
        } else {
            //Semua SO yang belum APPROVED, kecuali SO yang sedang di-manage
            QueryBuilder builderNotApprovedSoOther = new QueryBuilder();
            builderNotApprovedSoOther.add(" SELECT COALESCE(SUM(A.qty_so * (A.gross_sell_price - A.discount_amount)), 0)")
                    .add(" FROM ")
                    .add(SalesOrderItem.TABLE_NAME)
                    .add(" A ")
                    .add(" INNER JOIN ")
                    .add(SalesOrder.TABLE_NAME)
                    .add(" B ON A.so_id = B.so_id ")
                    .add(" WHERE B.tenant_id = :tenantId ")
                    .add(" AND B.ou_id = :ouId ")
                    .add(" AND B.partner_bill_to_id = :partnerId ")
                    .add(" AND B.status_doc IN (:DRAFT, :IN_PROGRESS) ")
                    .add(" AND B.so_id NOT IN( ")
                        .add(" SELECT C.so_id FROM ")
                        .add(ManageSalesOrder.TABLE_NAME)
                        .add(" C ")
                        .add(" WHERE C.manage_so_id = :manageSoId ")
                    .add(" ) ");

            Query queryNotApprovedSoOther = salesOrderItemDao.createNativeQuery(builderNotApprovedSoOther.toString());

            queryNotApprovedSoOther.setParameter("tenantId", inputDto.getLong("tenantId"));
            queryNotApprovedSoOther.setParameter("ouId", inputDto.getLong("ouId"));
            queryNotApprovedSoOther.setParameter("partnerId", inputDto.getLong("partnerId"));
            queryNotApprovedSoOther.setParameter("DRAFT", SalesConstants.DRAFT_TRANSACTION);
            queryNotApprovedSoOther.setParameter("IN_PROGRESS", SalesConstants.IN_PROGRESS_TRANSACTION);
            queryNotApprovedSoOther.setParameter("manageSoId", manageSoId);

            Object notApprovedSoOtherAmount = queryNotApprovedSoOther.getSingleResult();

            //over credit limit amount dikurangi dengan nilai SO yang belum APPROVED
            overCreditLimitAmount = overCreditLimitAmount.subtract(Double.valueOf(notApprovedSoOtherAmount.toString()));

            if(SalesConstants.DOCUMENT_MODIFY_HEADER_SALES_ORDER.equals(manageSoDocTypeId)){
                //SO yang sedang di-manage header
                QueryBuilder builderManageHeaderSo = new QueryBuilder();
                builderManageHeaderSo.add(" SELECT COALESCE(SUM(A.qty_so * (A.gross_sell_price - A.discount_amount)), 0)")
                        .add(" FROM ")
                        .add(SalesOrderItem.TABLE_NAME)
                        .add(" A ")
                        .add(" INNER JOIN ")
                        .add(ManageSalesOrder.TABLE_NAME)
                        .add(" B ON A.so_id = B.so_id ")
                        .add(" INNER JOIN ")
                        .add(ManageSalesOrderHeader.TABLE_NAME)
                        .add(" C ON B.manage_so_id = C.manage_so_id ")
                        .add(" WHERE C.tenant_id = :tenantId ")
                        .add(" AND B.ou_id = :ouId ")
                        .add(" AND C.partner_bill_to_id = :partnerId ")
                        .add(" AND B.manage_so_id = :manageSoId ");

                Query queryManageHeaderSo = salesOrderItemDao.createNativeQuery(builderManageHeaderSo.toString());

                queryManageHeaderSo.setParameter("tenantId", inputDto.getLong("tenantId"));
                queryManageHeaderSo.setParameter("ouId", inputDto.getLong("ouId"));
                queryManageHeaderSo.setParameter("partnerId", inputDto.getLong("partnerId"));
                queryManageHeaderSo.setParameter("manageSoId", manageSoId);

                Object manageHeaderSoAmount = queryManageHeaderSo.getSingleResult();

                //over credit limit amount dikurangi dengan nilai SO yang sedang di-manage header
                overCreditLimitAmount = overCreditLimitAmount.subtract(Double.valueOf(manageHeaderSoAmount.toString()));
            } else if(SalesConstants.DOCUMENT_MODIFY_ITEM_SALES_ORDER.equals(manageSoDocTypeId)){
                //SO yang sedang di-manage item
                QueryBuilder builderManageItemSo = new QueryBuilder();
                builderManageItemSo.add(" SELECT COALESCE(SUM(A.qty_so * (A.gross_sell_price - A.discount_amount)), 0)")
                        .add(" FROM ")
                        .add(ManageSalesOrderItem.TABLE_NAME)
                        .add(" A ")
                        .add(" INNER JOIN ")
                        .add(ManageSalesOrder.TABLE_NAME)
                        .add(" B ON A.manage_so_id = B.manage_so_id ")
                        .add(" INNER JOIN ")
                        .add(SalesOrder.TABLE_NAME)
                        .add(" C ON B.so_id = C.so_id ")
                        .add(" WHERE C.tenant_id = :tenantId ")
                        .add(" AND C.ou_id = :ouId ")
                        .add(" AND C.partner_bill_to_id = :partnerId ")
                        .add(" AND B.manage_so_id = :manageSoId ");

                Query queryManageItemSo = manageSalesOrderItemDao.createNativeQuery(builderManageItemSo.toString());

                queryManageItemSo.setParameter("tenantId", inputDto.getLong("tenantId"));
                queryManageItemSo.setParameter("ouId", inputDto.getLong("ouId"));
                queryManageItemSo.setParameter("partnerId", inputDto.getLong("partnerId"));
                queryManageItemSo.setParameter("manageSoId", manageSoId);

                Object manageItemSoAmount = queryManageItemSo.getSingleResult();

                //over credit limit amount dikurangi dengan nilai SO yang sedang di-manage item
                overCreditLimitAmount = overCreditLimitAmount.subtract(Double.valueOf(manageItemSoAmount.toString()));
            }
        }


        // return query result
        return new Dto().put("overCreditLimitAmount", overCreditLimitAmount.doubleValue());
    }

}