public Dto execute(Dto inputDto) throws Exception {
        ValidationUtil.valDtoContainsKey(inputDto, "soId");
        ValidationUtil.valDtoContainsKey(inputDto, "datetime");
        ValidationUtil.valDatetime(inputDto, "datetime");

        Long soId = inputDto.getLong("soId");

        Dto outputDto = new Dto();

        //get type so
        String typeSo = GeneralConstants.EMPTY_VALUE;

        //@formatter:off
        QueryBuilder builderSoType = new QueryBuilder();
        builderSoType.add(" SELECT  C.code AS so_type")
            .add(" FROM ").add(SalesOrder.TABLE_NAME).add(" A ")
            .add(" INNER JOIN ").add(SoInfo.TABLE_NAME).add(" B ON A.so_id = B.so_id " )
            .add(" INNER JOIN ").add(ComboValue.TABLE_NAME).add(" C ON B.flg_type_so = C.code AND C.combo_id = :soType ")
            .add(" WHERE A.so_id = :soId ");
        //@formatter:on

        Query querySoType = salesOrderDao.createNativeQuery(builderSoType.toString());
        querySoType.setParameter("soId", soId);
        querySoType.setParameter("soType", SalesConstantsForKtmt.COMBO_SO_TYPE);

        Object resultQuerySoType = querySoType.getSingleResult();
        if (resultQuerySoType != null) {
            typeSo = resultQuerySoType.toString();
        }

        //get group brand code
        String groupBrandCode = GeneralConstants.EMPTY_VALUE;

        //@formatter:off
        QueryBuilder builderGroupBrand = new QueryBuilder();
        builderGroupBrand.add(" SELECT  C.group_brand ")
            .add(" FROM ").add(SoBrandItem.TABLE_NAME).add(" A ")
            .add(" INNER JOIN ").add(Brand.TABLE_NAME).add(" B ON A.brand_id = B.brand_id " )
            .add(" INNER JOIN ").add(BrandExt.TABLE_NAME).add(" C ON B.brand_id = C.brand_id ")
            .add(" WHERE A.so_id = :soId ")
            .add(" GROUP BY C.group_brand ");
        //@formatter:on

        Query queryGroupBrand = salesOrderDao.createNativeQuery(builderGroupBrand.toString());
        queryGroupBrand.setParameter("soId", soId);

        Object resultQueryGroupBrand = queryGroupBrand.getSingleResult();
        if (resultQueryGroupBrand != null) {
            groupBrandCode = resultQueryGroupBrand.toString();
        }

        // get curr code so
        String currCode = GeneralConstants.EMPTY_VALUE;
        String docDate = GeneralConstants.EMPTY_VALUE;

        //@formatter:off
        QueryBuilder builderCurrCode = new QueryBuilder();
        builderCurrCode.add(" SELECT  A.curr_code, A.doc_date ")
            .add(" FROM ").add(SalesOrder.TABLE_NAME).add(" A ")
            .add(" WHERE A.so_id = :soId ");
        //@formatter:on

        Query queryCurrCode = salesOrderDao.createNativeQuery(builderCurrCode.toString());
        queryCurrCode.setParameter("soId", soId);

        List<Object[]> resultQueryCurrCode = queryCurrCode.getResultList();
        if (resultQueryCurrCode != null && resultQueryCurrCode.size() > 0) {
            List<Dto> resultCurrCodeList = new ArrayList<Dto>();
            resultCurrCodeList = DtoUtil.createDtoListFromArray(resultQueryCurrCode, "currCode", "docDate");

            for (Dto dto : resultCurrCodeList) {
                currCode = dto.getString("currCode");
                docDate = dto.getString("docDate");
            }
        }

        // get total amount SO
        //@formatter:off
        QueryBuilder builderNettAmount = new QueryBuilder();
        builderNettAmount.add(" WITH temp_amount_so AS ( ")
            .add(" SELECT ((A.nett_sell_price + A.tax_price) * A.qty) AS amount, A.qty, B.partner_id, B.tenant_id, B.ou_id, A.gross_sell_price ")
            .add(" FROM ").add(SoBrandItem.TABLE_NAME).add(" A ")
            .add(" INNER JOIN ").add(SalesOrder.TABLE_NAME).add(" B ON A.so_id = B.so_id ")
            .add(" WHERE A.so_id = :soId ")
            .add(" ) ")
            .add(" SELECT SUM(amount) AS total_nett_amount, SUM(qty) AS total_qty, partner_id, tenant_id, ou_id, gross_sell_price ")
            .add(" FROM temp_amount_so ")
            .add(" GROUP BY partner_id, tenant_id, ou_id, gross_sell_price ");
        //@formatter:on

        Query queryNettAmount = salesOrderDao.createNativeQuery(builderNettAmount.toString());
        queryNettAmount.setParameter("soId", soId);

        List<Object[]> resultNettAmount = queryNettAmount.getResultList();
        Double totalAmountSo = 0d;
        Double grossSellPrice = 0d;
        Double totalQty = 0d;
        Long partnerId = GeneralConstants.NULL_REF_VALUE_LONG;
        Long tenantId = GeneralConstants.NULL_REF_VALUE_LONG;
        Long ouId = GeneralConstants.NULL_REF_VALUE_LONG;

        if (resultNettAmount != null && resultNettAmount.size() > 0) {
            List<Dto> resultNettAmountList = new ArrayList<Dto>();
            resultNettAmountList = DtoUtil.createDtoListFromArray(resultNettAmount, "totalNettAmount", "totalQty", "partnerId",
                    "tenantId", "ouId", "grossSellPrice");

            totalAmountSo = resultNettAmountList.get(0).getDouble("totalNettAmount");
            totalQty = resultNettAmountList.get(0).getDouble("totalQty");
            partnerId = resultNettAmountList.get(0).getLong("partnerId");
            tenantId = resultNettAmountList.get(0).getLong("tenantId");
            ouId = resultNettAmountList.get(0).getLong("ouId");
            grossSellPrice = resultNettAmountList.get(0).getDouble("grossSellPrice");
        }

        totalAmountSo = new Calc(totalAmountSo).doubleValue();
        totalQty = new Calc(totalQty).doubleValue();

        // get Saldo AR yang belum dibayarkan padahal sudah jatuh tempo
        Connection conn = jdbcTemplate.getDataSource().getConnection();
        conn.setAutoCommit(false);
        PreparedStatement stm = null;

        try {

            String sessionId = inputDto.getString("sessionId");
            Long userId = inputDto.getLong("userId");
            Long roleId = inputDto.getLong("roleId");
            String dueDateType = GeneralConstants.EMPTY_VALUE;
            String dueDateFrom = GeneralConstants.START_DATE;
            String dueDateTo = GeneralConstants.END_DATE;
            String dateNow = DateUtil.dateNow();

            stm = conn.prepareStatement("SELECT f_get_summary_aging_ar_list_for_inquiry_progress_ar(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
            stm.setString(1, sessionId);
            stm.setLong(2, tenantId);
            stm.setLong(3, ouId);
            stm.setLong(4, userId);
            stm.setLong(5, roleId);
            stm.setLong(6, partnerId);
            stm.setString(7, dueDateType);
            stm.setString(8, dueDateFrom);
            stm.setString(9, dueDateTo);
            stm.setString(10, dateNow);

            ResultSet resultSetFromFunction = stm.executeQuery();
            ResultSet resultSetFromQuery = null;
            if (resultSetFromFunction.next()) {
                resultSetFromQuery = (ResultSet) resultSetFromFunction.getObject(1);
            }

            resultSetFromFunction.close();

            Dto detailDto = new Dto();

            while (resultSetFromQuery != null && resultSetFromQuery.next()) {
                detailDto.put("saldoArCurrCode", resultSetFromQuery.getString(1));
                detailDto.put("saldoAr", resultSetFromQuery.getBigDecimal(5).doubleValue());
            }

            log.debug("summary ar : "+detailDto);

            stm.close();
            conn.commit();
            conn.close();

            if(detailDto.isEmpty()){
                detailDto.put("saldoArCurrCode", currCode);
                detailDto.put("saldoAr", new Double(0));
            }

            outputDto.putAll(detailDto);
            log.debug("add summary ar : "+outputDto);
        } catch (Exception e) {
            if (stm != null)
                stm.close();

            if (conn != null && !conn.isClosed())
                conn.rollback();

            throw e;
        }

        // get flg_due_date
        String flgDueDate = GeneralConstants.NO;

        if (Double.valueOf(outputDto.get("saldoAr").toString()) > new Double(0)) {
            flgDueDate = GeneralConstants.YES;
        }

        // get promoId dan promo
        String promo = GeneralConstants.EMPTY_VALUE;
        Long promoId = GeneralConstants.NULL_REF_VALUE_LONG;
        Long productLaunchingId = GeneralConstants.NULL_REF_VALUE_LONG;
        Long mouId = GeneralConstants.NULL_REF_VALUE_LONG;

        //@formatter:off
        QueryBuilder builderPromo = new QueryBuilder();
        builderPromo.add(" SELECT  A.promo_id, C.promo_code, A.product_launching_id, A.mou_id ")
            .add(" FROM ").add(SoInfo.TABLE_NAME).add(" A ")
            .add(" INNER JOIN ").add(SalesOrder.TABLE_NAME).add(" B ON A.so_id = B.so_id " )
            .add(" INNER JOIN ").add(Promo.TABLE_NAME).add(" C ON A.promo_id = C.promo_id ")
            .add(" WHERE A.so_id = :soId ");
        //@formatter:on

        Query queryPromo = salesOrderDao.createNativeQuery(builderPromo.toString());
        queryPromo.setParameter("soId", soId);

        List<Object[]> resultPromo = queryPromo.getResultList();

        if (resultPromo != null && resultPromo.size() > 0) {
            List<Dto> resultPromoList = new ArrayList<Dto>();
            resultPromoList = DtoUtil.createDtoListFromArray(resultPromo, "promoId", "promo", "productLaunchingId", "mouId");

            promoId = resultPromoList.get(0).getLong("promoId");
            promo = resultPromoList.get(0).getString("promo");
            productLaunchingId = resultPromoList.get(0).getLong("productLaunchingId");
            mouId = resultPromoList.get(0).getLong("mouId");
        }

        // get flgDiffSellPrice
        Connection connDiffPrice = jdbcTemplate.getDataSource().getConnection();
        connDiffPrice.setAutoCommit(false);
        PreparedStatement stmDiffPrice = null;

        Double basicGrossSellPrice = 0d;

        try {

            String sessionId = inputDto.getString("sessionId");
            Long userId = inputDto.getLong("userId");
            Long roleId = inputDto.getLong("roleId");

            stmDiffPrice = connDiffPrice.prepareStatement("SELECT f_get_detail_info_price_for_sob(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
            stmDiffPrice.setString(1, sessionId);
            stmDiffPrice.setLong(2, tenantId);
            stmDiffPrice.setLong(3, ouId);
            stmDiffPrice.setLong(4, userId);
            stmDiffPrice.setLong(5, roleId);
            stmDiffPrice.setLong(6, soId);
            stmDiffPrice.setLong(7, partnerId);
            stmDiffPrice.setLong(8, productLaunchingId);
            stmDiffPrice.setLong(9, promoId);
            stmDiffPrice.setLong(10, mouId);
            stmDiffPrice.setString(11, docDate);

            ResultSet resultSetFromFunction = stmDiffPrice.executeQuery();
            ResultSet resultSetFromQuery = null;
            if (resultSetFromFunction.next()) {
                resultSetFromQuery = (ResultSet) resultSetFromFunction.getObject(1);
            }

            resultSetFromFunction.close();

            Dto detailDto = new Dto();

            while (resultSetFromQuery != null && resultSetFromQuery.next()) {
                detailDto.put("grossSellPrice", resultSetFromQuery.getBigDecimal(5).doubleValue());
            }

            log.debug("detail dto basic sell price "+detailDto);

            stmDiffPrice.close();
            connDiffPrice.commit();
            connDiffPrice.close();

            basicGrossSellPrice = detailDto.getDouble("grossSellPrice");

        } catch (Exception e) {
            if (stmDiffPrice != null)
                stmDiffPrice.close();

            if (connDiffPrice != null && !connDiffPrice.isClosed())
                connDiffPrice.rollback();

            throw e;
        }

        if (!grossSellPrice.equals(basicGrossSellPrice)) {
            outputDto.put("flgDiffSellPrice", GeneralConstants.YES);
        } else {
            outputDto.put("flgDiffSellPrice", GeneralConstants.NO);
        }

        outputDto.put("soId", soId);
        outputDto.put("typeSo", typeSo);
        outputDto.put("groupBrandCode", groupBrandCode);
        outputDto.put("currCode", currCode);
        outputDto.put("totalAmountSo", totalAmountSo);
        outputDto.put("qtySo", totalQty);
        outputDto.put("flgDueDate", flgDueDate);
        // flg indent dilihat ketika nilai qty saat SOS dan DO < dari nilai qty per brand SOB
        outputDto.put("flgIndent", GeneralConstants.NO);
        outputDto.put("promoId", promoId);
        outputDto.put("promo", promo);

        log.debug("output dto : " + outputDto);
        return outputDto;
    }