/** Retrieve item types and fill in the item types combo box. */
 private void init() {
   Response res = ClientUtils.getData("loadItemTypes", new GridParams());
   Domain d = new Domain("ITEM_TYPES");
   if (!res.isError()) {
     ItemTypeVO vo = null;
     list = ((VOListResponse) res).getRows();
     for (int i = 0; i < list.size(); i++) {
       vo = (ItemTypeVO) list.get(i);
       d.addDomainPair(vo.getProgressiveHie02ITM02(), vo.getDescriptionSYS10());
     }
   }
   controlHierarchy.setDomain(d);
   controlHierarchy
       .getComboBox()
       .addItemListener(
           new ItemListener() {
             public void itemStateChanged(ItemEvent e) {
               if (e.getStateChange() == e.SELECTED) {
                 ItemTypeVO typeVO = (ItemTypeVO) list.get(controlHierarchy.getSelectedIndex());
                 treePanel.setCompanyCode(typeVO.getCompanyCodeSys01ITM02());
                 treePanel.setProgressiveHIE02((BigDecimal) controlHierarchy.getValue());
                 DetailSupplierVO vo =
                     (DetailSupplierVO) supplierPanel.getVOModel().getValueObject();
                 treePanel.setCompanyCode(vo.getCompanyCodeSys01REG04());
                 treePanel.reloadTree();
                 itemsGrid.clearData();
               }
             }
           });
   if (d.getDomainPairList().length == 1) controlHierarchy.getComboBox().setSelectedIndex(0);
   else controlHierarchy.getComboBox().setSelectedIndex(-1);
 }
 /**
  * Method invoked when pressing import all items button: it add all items to the supplier items
  * collection.
  */
 void impAllItemsButton_actionPerformed(ActionEvent e) {
   DetailSupplierVO vo = (DetailSupplierVO) supplierPanel.getVOModel().getValueObject();
   SupplierItemVO itemVO = new SupplierItemVO();
   itemVO.setCompanyCodeSys01PUR02(vo.getCompanyCodeSys01REG04());
   itemVO.setProgressiveReg04PUR02(vo.getProgressiveREG04());
   itemVO.setProgressiveHie02PUR02((BigDecimal) controlHierarchy.getValue());
   Response res = ClientUtils.getData("importAllItemsToSupplier", itemVO);
   if (!res.isError()) itemsGrid.reloadData();
 }
  void printButton_actionPerformed(ActionEvent e) {
    if (controlFromDate.getValue() == null || controlToDate.getValue() == null) {
      JOptionPane.showMessageDialog(
          ClientUtils.getParentFrame(this),
          ClientSettings.getInstance().getResources().getResource("please set a from/to date"),
          ClientSettings.getInstance().getResources().getResource("Attention"),
          JOptionPane.WARNING_MESSAGE);
      return;
    }
    if (controlCompaniesCombo.getValue() == null) {
      JOptionPane.showMessageDialog(
          ClientUtils.getParentFrame(this),
          ClientSettings.getInstance().getResources().getResource("please select a company"),
          ClientSettings.getInstance().getResources().getResource("Attention"),
          JOptionPane.WARNING_MESSAGE);
      return;
    }

    HashMap params = new HashMap();
    params.put("COMPANY_CODE", controlCompaniesCombo.getValue());
    params.put("START_DATE", controlFromDate.getValue());
    params.put("END_DATE", controlToDate.getValue());
    if (controlFromItemNr.getValue() != null)
      params.put(
          "FROM_PROGRESSIVE",
          " and ACC05_JOURNAL_HEADER.PROGRESSIVE>=" + controlFromItemNr.getValue());
    else params.remove("FROM_PROGRESSIVE");

    HashMap map = new HashMap();
    map.put(ApplicationConsts.COMPANY_CODE_SYS01, controlCompaniesCombo.getValue());
    map.put(ApplicationConsts.FUNCTION_CODE_SYS06, "ACC05");
    map.put(ApplicationConsts.EXPORT_PARAMS, params);
    Response res = ClientUtils.getData("getJasperReport", map);
    if (!res.isError()) {
      JasperPrint print = (JasperPrint) ((VOResponse) res).getVo();
      JRViewer viewer = new JRViewer(print);
      JFrame frame = new JFrame();
      frame.setSize(MDIFrame.getInstance().getSize());
      frame.setContentPane(viewer);
      frame.setTitle(this.getTitle());
      frame.setIconImage(MDIFrame.getInstance().getIconImage());
      frame.setVisible(true);
    } else
      JOptionPane.showMessageDialog(
          ClientUtils.getParentFrame(this),
          res.getErrorMessage(),
          ClientSettings.getInstance().getResources().getResource("print journal"),
          JOptionPane.ERROR_MESSAGE);
  }
  /**
   * Method invoked when the user has clicked on save button and the grid is in EDIT mode.
   *
   * @param rowNumbers row indexes related to the changed rows
   * @param oldPersistentObjects old value objects, previous the changes
   * @param persistentObjects value objects relatied to the changed rows
   * @return an ErrorResponse value object in case of errors, VOListResponse if the operation is
   *     successfully completed
   */
  public Response updateRecords(
      int[] rowNumbers, ArrayList oldPersistentObjects, ArrayList persistentObjects)
      throws Exception {
    Response response = null;
    ItemDiscountVO vo = null;
    for (int i = 0; i < persistentObjects.size(); i++) {
      vo = (ItemDiscountVO) persistentObjects.get(i);
      response = validateDiscount(vo);
      if (response.isError()) return response;
    }

    response =
        ClientUtils.getData(
            "updateItemDiscounts", new ArrayList[] {oldPersistentObjects, persistentObjects});
    return response;
  }
  /**
   * Method invoked when the user has clicked on save button and the grid is in INSERT mode.
   *
   * @param rowNumbers row indexes related to the new rows to save
   * @param newValueObjects list of new value objects to save
   * @return an ErrorResponse value object in case of errors, VOListResponse if the operation is
   *     successfully completed
   */
  public Response insertRecords(int[] rowNumbers, ArrayList newValueObjects) throws Exception {
    ItemDiscountVO vo = null;
    Response response = null;
    DetailItemVO itemVO = (DetailItemVO) frame.getFormPanel().getVOModel().getValueObject();

    for (int i = 0; i < newValueObjects.size(); i++) {
      vo = (ItemDiscountVO) newValueObjects.get(i);
      response = validateDiscount(vo);
      if (response.isError()) return response;

      vo.setCompanyCodeSys01SAL03(itemVO.getCompanyCodeSys01ITM01());
      vo.setItemCodeItm01SAL04(itemVO.getItemCodeITM01());
    }

    response = ClientUtils.getData("insertItemDiscounts", newValueObjects);
    return response;
  }
  /**
   * Callback method invoked when the user has clicked on the insert button
   *
   * @param valueObject empty value object just created: the user can manage it to fill some
   *     attribute values
   */
  public void createValueObject(ValueObject valueObject) throws Exception {
    ItemDiscountVO vo = (ItemDiscountVO) valueObject;
    vo.setMinQtySAL03(new BigDecimal(1));
    vo.setMultipleQtySAL03(Boolean.FALSE);

    DetailItemVO itemVO = (DetailItemVO) frame.getFormPanel().getVOModel().getValueObject();
    itemVO.setCompanyCodeSys01ITM01(itemVO.getCompanyCodeSys01());
    Response res = ClientUtils.getData("loadCompany", itemVO.getCompanyCodeSys01());
    if (!res.isError()) {
      OrganizationVO compVO = (OrganizationVO) ((VOResponse) res).getVo();
      if (compVO.getCurrencyCodeReg03() != null && !compVO.getCurrencyCodeReg03().equals("")) {
        vo.setCurrencyCodeReg03SAL03(compVO.getCurrencyCodeReg03());
        frame
            .getColCurrencyCode()
            .forceValidate(
                frame.getDiscountsGrid().getSelectedRow() == -1
                    ? 0
                    : frame.getDiscountsGrid().getSelectedRow());
      }
    }
  }
  void printButton_actionPerformed(ActionEvent e) {
    DetailSaleDocVO vo = (DetailSaleDocVO) headerFormPanel.getVOModel().getValueObject();

    HashMap params = new HashMap();
    params.put("COMPANY_CODE", vo.getCompanyCodeSys01DOC01());
    params.put("DOC_TYPE", vo.getDocTypeDOC01());
    params.put("DOC_YEAR", vo.getDocYearDOC01());
    params.put("DOC_NUMBER", vo.getDocNumberDOC01());

    HashMap map = new HashMap();
    map.put(ApplicationConsts.COMPANY_CODE_SYS01, vo.getCompanyCodeSys01DOC01());
    map.put(ApplicationConsts.FUNCTION_CODE_SYS06, headerFormPanel.getFunctionId());
    map.put(ApplicationConsts.EXPORT_PARAMS, params);
    Response res = ClientUtils.getData("getJasperReport", map);
    if (!res.isError()) {
      JasperPrint print = (JasperPrint) ((VOResponse) res).getVo();
      JRViewer viewer = new JRViewer(print);
      JFrame frame = new JFrame();
      frame.setSize(MDIFrame.getInstance().getSize());
      frame.setContentPane(viewer);
      frame.setTitle(this.getTitle());
      frame.setIconImage(MDIFrame.getInstance().getIconImage());
      frame.setVisible(true);
    } else
      JOptionPane.showMessageDialog(
          ClientUtils.getParentFrame(this),
          res.getErrorMessage(),
          ClientSettings.getInstance().getResources().getResource("print document"),
          JOptionPane.ERROR_MESSAGE);
  }
  void confirmButton_actionPerformed(ActionEvent e) {
    // view close dialog...
    if (JOptionPane.showConfirmDialog(
            ClientUtils.getParentFrame(this),
            ClientSettings.getInstance().getResources().getResource("close credit note?"),
            ClientSettings.getInstance().getResources().getResource("credit note closing"),
            JOptionPane.YES_NO_OPTION)
        == JOptionPane.YES_OPTION) {

      Response res = ClientUtils.getData("closeSaleDoc", controller.getPk());
      if (!res.isError()) {
        confirmButton.setEnabled(false);
        headerFormPanel.setMode(Consts.READONLY);
        headerFormPanel.executeReload();
        if (getInvoices() != null) getInvoices().reloadCurrentBlockOfData();
      } else
        JOptionPane.showMessageDialog(
            ClientUtils.getParentFrame(this),
            res.getErrorMessage(),
            ClientSettings.getInstance().getResources().getResource("credit note closing"),
            JOptionPane.ERROR_MESSAGE);
    }
  }
  /**
   * Check if components required by specified products are available.
   *
   * @param products list of ProdOrderProductVO objects
   * @params compAltComps collection of <component item code,HashSet of alternative component item
   *     codes>; filled by this method (and given back by reference)
   * @return VOListResponse of ProdOrderComponentVO objects
   */
  public final Response checkComponentsAvailability(
      Connection conn,
      Hashtable compAltComps,
      ArrayList products,
      UserSessionParameters userSessionPars,
      HttpServletRequest request,
      HttpServletResponse response,
      HttpSession userSession,
      ServletContext context) {
    String serverLanguageId = ((JAIOUserSessionParameters) userSessionPars).getServerLanguageId();
    try {

      // retrieve internationalization settings (Resources object)...
      ServerResourcesFactory factory =
          (ServerResourcesFactory) context.getAttribute(Controller.RESOURCES_FACTORY);
      Resources resources = factory.getResources(userSessionPars.getLanguageId());

      if (products.size() == 0) {
        return new VOListResponse(new ArrayList(), false, 0);
      }

      // fill in comps hashtable with the collection of required components...
      ItemPK pk = null;
      ProdOrderProductVO prodVO = null;
      ArrayList components = null;
      MaterialVO compVO = null;
      Response res = null;
      ProdOrderComponentVO componentVO = null;
      Hashtable comps =
          new Hashtable(); // collection of <component item code,ProdOrderComponentVO object>
      for (int i = 0; i < products.size(); i++) {
        // retrieve bill of materials for each product...
        prodVO = (ProdOrderProductVO) products.get(i);
        pk = new ItemPK(prodVO.getCompanyCodeSys01DOC23(), prodVO.getItemCodeItm01DOC23());
        res =
            bean.getBillOfMaterials(
                conn, pk, userSessionPars, request, response, userSession, context);
        if (res.isError()) {
          return res;
        }

        // extract components only (leaf nodes)...
        components =
            getComponents(
                (DefaultMutableTreeNode) ((TreeModel) ((VOResponse) res).getVo()).getRoot());
        for (int j = 0; j < components.size(); j++) {
          compVO = (MaterialVO) components.get(j);
          componentVO = (ProdOrderComponentVO) comps.get(compVO.getItemCodeItm01ITM03());
          if (componentVO == null) {
            componentVO = new ProdOrderComponentVO();
            comps.put(compVO.getItemCodeItm01ITM03(), componentVO);
            componentVO.setAvailableQty(new BigDecimal(0));
            componentVO.setCompanyCodeSys01DOC24(compVO.getCompanyCodeSys01ITM03());
            componentVO.setDescriptionSYS10(compVO.getDescriptionSYS10());
            componentVO.setDocNumberDOC24(prodVO.getDocNumberDOC23());
            componentVO.setDocYearDOC24(prodVO.getDocYearDOC23());
            componentVO.setItemCodeItm01DOC24(compVO.getItemCodeItm01ITM03());
            componentVO.setMinSellingQtyUmCodeReg02ITM01(compVO.getMinSellingQtyUmCodeReg02ITM01());
            componentVO.setQtyDOC24(new BigDecimal(0));
          }
          componentVO.setQtyDOC24(
              componentVO.getQtyDOC24().add(compVO.getQtyITM03().multiply(prodVO.getQtyDOC23())));
        }
      }

      // check components availability in the specified warehouse...
      Enumeration en = comps.keys();
      GridParams gridParams = new GridParams();
      gridParams
          .getOtherGridParams()
          .put(ApplicationConsts.COMPANY_CODE_SYS01, prodVO.getCompanyCodeSys01DOC23());
      gridParams
          .getOtherGridParams()
          .put(ApplicationConsts.WAREHOUSE_CODE, prodVO.getWarehouseCodeWar01DOC22());
      gridParams.getOtherGridParams().put(ApplicationConsts.LOAD_ALL, Boolean.TRUE);
      ItemAvailabilityVO availVO = null;
      BigDecimal availability, altAvailability, delta;
      String itemCode = null;
      ArrayList list, availList;
      AltComponentVO altVO = null;
      ArrayList alternativeComps = new ArrayList();
      ArrayList compsToRemove = new ArrayList();
      ProdOrderComponentVO altComponentVO = null;
      HashSet altCodes = null; // list of alternative component item codes...
      BigDecimal altQty = null;
      while (en.hasMoreElements()) {
        itemCode = en.nextElement().toString();
        componentVO = (ProdOrderComponentVO) comps.get(itemCode);

        gridParams
            .getOtherGridParams()
            .put(
                ApplicationConsts.ITEM_PK, new ItemPK(prodVO.getCompanyCodeSys01DOC23(), itemCode));
        res =
            avail.executeCommand(
                gridParams, userSessionPars, request, response, userSession, context);
        if (res.isError()) return res;

        availList = ((VOListResponse) res).getRows();
        componentVO.setAvailabilities(availList);
        availability = new BigDecimal(0);
        for (int i = 0; i < availList.size(); i++) {
          availVO = (ItemAvailabilityVO) availList.get(i);
          availability = availability.add(availVO.getAvailableQtyWAR03());
        }
        componentVO.setAvailableQty(availability);

        if (componentVO.getQtyDOC24().doubleValue() > componentVO.getAvailableQty().doubleValue()) {
          // check if there exist some alternative component...
          res =
              altComps.executeCommand(
                  gridParams, userSessionPars, request, response, userSession, context);
          if (res.isError()) return res;
          list = ((VOListResponse) res).getRows();
          for (int i = 0; i < list.size(); i++) {
            altVO = (AltComponentVO) list.get(i);
            gridParams
                .getOtherGridParams()
                .put(
                    ApplicationConsts.ITEM_PK,
                    new ItemPK(prodVO.getCompanyCodeSys01DOC23(), altVO.getItemCodeItm01ITM04()));
            res =
                avail.executeCommand(
                    gridParams, userSessionPars, request, response, userSession, context);
            if (res.isError()) return res;
            availList = ((VOListResponse) res).getRows();
            altAvailability = new BigDecimal(0);
            for (int j = 0; j < availList.size(); j++) {
              availVO = (ItemAvailabilityVO) availList.get(j);
              altAvailability = altAvailability.add(availVO.getAvailableQtyWAR03());
            }
            if (altAvailability.doubleValue() > 0) {
              altComponentVO = new ProdOrderComponentVO();
              altComponentVO.setAvailabilities(availList);
              altComponentVO.setAvailableQty(altAvailability);
              altComponentVO.setCompanyCodeSys01DOC24(altVO.getCompanyCodeSys01ITM04());
              altComponentVO.setDescriptionSYS10(altVO.getDescriptionSYS10());
              altComponentVO.setDocNumberDOC24(prodVO.getDocNumberDOC23());
              altComponentVO.setDocYearDOC24(prodVO.getDocYearDOC23());
              altComponentVO.setItemCodeItm01DOC24(altVO.getItemCodeItm01ITM04());
              altComponentVO.setMinSellingQtyUmCodeReg02ITM01(
                  altVO.getMinSellingQtyUmCodeReg02ITM01());
              altQty =
                  conv.convertQty(
                      altVO.getMinSellingQtyUmCodeReg02ITM01(),
                      componentVO.getMinSellingQtyUmCodeReg02ITM01(),
                      altAvailability,
                      userSessionPars,
                      request,
                      response,
                      userSession,
                      context);
              if (componentVO.getQtyDOC24().subtract(availability).doubleValue()
                  > altQty.doubleValue()) {
                delta = altQty;
                altComponentVO.setQtyDOC24(altAvailability);
              } else {
                delta = componentVO.getQtyDOC24();
                altComponentVO.setQtyDOC24(
                    conv.convertQty(
                        componentVO.getMinSellingQtyUmCodeReg02ITM01(),
                        altVO.getMinSellingQtyUmCodeReg02ITM01(),
                        delta,
                        userSessionPars,
                        request,
                        response,
                        userSession,
                        context));
              }
              componentVO.setQtyDOC24(componentVO.getQtyDOC24().subtract(delta));
              alternativeComps.add(altComponentVO);

              altCodes = (HashSet) compAltComps.get(itemCode);
              if (altCodes == null) {
                altCodes = new HashSet();
                compAltComps.put(itemCode, altCodes);
              }
              altCodes.add(altVO.getItemCodeItm01ITM04());

              if (componentVO.getQtyDOC24().doubleValue() == 0) {
                compsToRemove.add(componentVO);
                break;
              }
              if (componentVO.getQtyDOC24().subtract(availability).doubleValue() == 0) break;
            }
          }
        }
      }

      list = new ArrayList(comps.values());
      list.addAll(alternativeComps);
      list.removeAll(compsToRemove);
      return new VOListResponse(list, false, list.size());
    } catch (Throwable ex) {
      Logger.error(
          userSessionPars.getUsername(),
          this.getClass().getName(),
          "checkComponentsAvailability",
          "Error while retrieving components availability for the specified production order",
          ex);
      return new ErrorResponse(ex.getMessage());
    }
  }
  /** Business logic to execute. */
  public VOListResponse loadOutDeliveryNotesForSaleDoc(
      GridParams pars, String serverLanguageId, String username, ArrayList companiesList)
      throws Throwable {
    PreparedStatement pstmt = null;
    Connection conn = null;
    try {
      if (this.conn == null) conn = getConn();
      else conn = this.conn;

      // retrieve companies list...
      String companies = "";
      for (int i = 0; i < companiesList.size(); i++)
        companies += "'" + companiesList.get(i).toString() + "',";
      companies = companies.substring(0, companies.length() - 1);

      String sql =
          "select DOC08_DELIVERY_NOTES.COMPANY_CODE_SYS01,DOC08_DELIVERY_NOTES.DOC_TYPE,"
              + "DOC08_DELIVERY_NOTES.DOC_YEAR,DOC08_DELIVERY_NOTES.DOC_NUMBER,DOC08_DELIVERY_NOTES.DOC_DATE, "
              + "DOC08_DELIVERY_NOTES.DESTINATION_CODE_REG18,DOC08_DELIVERY_NOTES.DESCRIPTION,"
              + "DOC08_DELIVERY_NOTES.DOC_SEQUENCE "
              + " from DOC08_DELIVERY_NOTES where "
              + "DOC08_DELIVERY_NOTES.COMPANY_CODE_SYS01 in ("
              + companies
              + ") and "
              + "DOC08_DELIVERY_NOTES.ENABLED='Y' and "
              + "DOC08_DELIVERY_NOTES.DOC_TYPE=? and "
              + "DOC08_DELIVERY_NOTES.DOC_STATE=? and "
              + "(DOC08_DELIVERY_NOTES.COMPANY_CODE_SYS01,DOC08_DELIVERY_NOTES.DOC_TYPE,DOC08_DELIVERY_NOTES.DOC_YEAR,DOC08_DELIVERY_NOTES.DOC_NUMBER) "
              + " in (select DOC10_OUT_DELIVERY_NOTE_ITEMS.COMPANY_CODE_SYS01,DOC10_OUT_DELIVERY_NOTE_ITEMS.DOC_TYPE,DOC10_OUT_DELIVERY_NOTE_ITEMS.DOC_YEAR,DOC10_OUT_DELIVERY_NOTE_ITEMS.DOC_NUMBER "
              + " from DOC10_OUT_DELIVERY_NOTE_ITEMS where "
              + " DOC10_OUT_DELIVERY_NOTE_ITEMS.COMPANY_CODE_SYS01=? and "
              + " DOC10_OUT_DELIVERY_NOTE_ITEMS.DOC_TYPE_DOC01=? and "
              + " DOC10_OUT_DELIVERY_NOTE_ITEMS.DOC_YEAR_DOC01=? and "
              + " DOC10_OUT_DELIVERY_NOTE_ITEMS.DOC_NUMBER_DOC01=? ";

      DetailSaleDocVO docVO =
          (DetailSaleDocVO)
              pars.getOtherGridParams().get(ApplicationConsts.SALE_DOC_VO); // invoice document...
      if (docVO.getDocNumberDOC01() == null)
        sql +=
            " and DOC10_OUT_DELIVERY_NOTE_ITEMS.QTY-DOC10_OUT_DELIVERY_NOTE_ITEMS.INVOICE_QTY>0)";
      else sql += ")";

      Map attribute2dbField = new HashMap();
      attribute2dbField.put("companyCodeSys01DOC08", "DOC08_DELIVERY_NOTES.COMPANY_CODE_SYS01");
      attribute2dbField.put("docTypeDOC08", "DOC08_DELIVERY_NOTES.DOC_TYPE");
      attribute2dbField.put("docYearDOC08", "DOC08_DELIVERY_NOTES.DOC_YEAR");
      attribute2dbField.put("docNumberDOC08", "DOC08_DELIVERY_NOTES.DOC_NUMBER");
      attribute2dbField.put("docDateDOC08", "DOC08_DELIVERY_NOTES.DOC_DATE");
      attribute2dbField.put(
          "destinationCodeReg18DOC08", "DOC08_DELIVERY_NOTES.DESTINATION_CODE_REG18");
      attribute2dbField.put("descriptionDOC08", "DOC08_DELIVERY_NOTES.DESCRIPTION");
      attribute2dbField.put("docSequenceDOC08", "DOC08_DELIVERY_NOTES.DOC_SEQUENCE");

      ArrayList values = new ArrayList();
      values.add(ApplicationConsts.OUT_DELIVERY_NOTE_DOC_TYPE);
      values.add(ApplicationConsts.CLOSED);
      values.add(docVO.getCompanyCodeSys01DOC01());
      values.add(docVO.getDocTypeDoc01DOC01());
      values.add(docVO.getDocYearDoc01DOC01());
      values.add(docVO.getDocNumberDoc01DOC01());

      // read from DOC08 table...
      Response res =
          QueryUtil.getQuery(
              conn,
              new UserSessionParameters(username),
              sql,
              values,
              attribute2dbField,
              OutDeliveryNotesVO.class,
              "Y",
              "N",
              null,
              pars,
              true);

      if (res.isError()) throw new Exception(res.getErrorMessage());

      // check if the invoice document has been already created and there exists delivery notes
      // linked to it...
      if (docVO.getDocNumberDOC01() != null) {
        pstmt =
            conn.prepareStatement(
                "select DOC08_DELIVERY_NOTES.DOC_NUMBER from DOC08_DELIVERY_NOTES where "
                    + "DOC08_DELIVERY_NOTES.COMPANY_CODE_SYS01 in ("
                    + companies
                    + ") and "
                    + "DOC08_DELIVERY_NOTES.ENABLED='Y' and "
                    + "DOC08_DELIVERY_NOTES.DOC_TYPE=? and "
                    + "DOC08_DELIVERY_NOTES.DOC_STATE=? and "
                    + "(DOC08_DELIVERY_NOTES.COMPANY_CODE_SYS01,DOC08_DELIVERY_NOTES.DOC_TYPE,DOC08_DELIVERY_NOTES.DOC_YEAR,DOC08_DELIVERY_NOTES.DOC_NUMBER) "
                    + " in (select DOC10_OUT_DELIVERY_NOTE_ITEMS.COMPANY_CODE_SYS01,DOC10_OUT_DELIVERY_NOTE_ITEMS.DOC_TYPE,DOC10_OUT_DELIVERY_NOTE_ITEMS.DOC_YEAR,DOC10_OUT_DELIVERY_NOTE_ITEMS.DOC_NUMBER "
                    + " from DOC10_OUT_DELIVERY_NOTE_ITEMS,DOC02_SELLING_ITEMS where "
                    + " DOC10_OUT_DELIVERY_NOTE_ITEMS.COMPANY_CODE_SYS01=? and "
                    + " DOC10_OUT_DELIVERY_NOTE_ITEMS.DOC_TYPE_DOC01=? and "
                    + " DOC10_OUT_DELIVERY_NOTE_ITEMS.DOC_YEAR_DOC01=? and "
                    + " DOC10_OUT_DELIVERY_NOTE_ITEMS.DOC_NUMBER_DOC01=? and "
                    + " DOC10_OUT_DELIVERY_NOTE_ITEMS.COMPANY_CODE_SYS01=DOC02_SELLING_ITEMS.COMPANY_CODE_SYS01 and "
                    + " DOC02_SELLING_ITEMS.DOC_TYPE=? and "
                    + " DOC02_SELLING_ITEMS.DOC_YEAR=? and "
                    + " DOC02_SELLING_ITEMS.DOC_NUMBER=? and "
                    + " DOC10_OUT_DELIVERY_NOTE_ITEMS.ITEM_CODE_ITM01=DOC02_SELLING_ITEMS.ITEM_CODE_ITM01)");

        pstmt.setString(1, ApplicationConsts.OUT_DELIVERY_NOTE_DOC_TYPE);
        pstmt.setString(2, ApplicationConsts.CLOSED);
        pstmt.setString(3, docVO.getCompanyCodeSys01DOC01());
        pstmt.setString(4, docVO.getDocTypeDoc01DOC01());
        pstmt.setBigDecimal(5, docVO.getDocYearDoc01DOC01());
        pstmt.setBigDecimal(6, docVO.getDocNumberDoc01DOC01());
        pstmt.setString(7, docVO.getDocTypeDOC01());
        pstmt.setBigDecimal(8, docVO.getDocYearDOC01());
        pstmt.setBigDecimal(9, docVO.getDocNumberDOC01());

        HashSet docNumberDOC08s = new HashSet();
        ResultSet rset = pstmt.executeQuery();
        while (rset.next()) docNumberDOC08s.add(rset.getBigDecimal(1));
        rset.close();

        java.util.List rows = ((VOListResponse) res).getRows();
        OutDeliveryNotesVO vo = null;
        for (int i = 0; i < rows.size(); i++) {
          vo = (OutDeliveryNotesVO) rows.get(i);
          if (docNumberDOC08s.contains(vo.getDocNumberDOC08())) vo.setSelected(Boolean.TRUE);
        }
      }

      Response answer = res;
      if (answer.isError()) throw new Exception(answer.getErrorMessage());
      else return (VOListResponse) answer;
    } catch (Throwable ex) {
      Logger.error(
          username,
          this.getClass().getName(),
          "executeCommand",
          "Error while fetching out delivery notes list, related to the specified sale document",
          ex);
      throw new Exception(ex.getMessage());
    } finally {
      try {
        pstmt.close();
      } catch (Exception exx) {
      }
      try {
        if (this.conn == null && conn != null) {
          // close only local connection
          conn.commit();
          conn.close();
        }

      } catch (Exception exx) {
      }
    }
  }
  /** Business logic to execute. */
  public VOListResponse loadCharges(
      GridParams gridParams,
      String serverLanguageId,
      String username,
      ArrayList companiesList,
      ArrayList customizedFields)
      throws Throwable {
    PreparedStatement pstmt = null;
    Connection conn = null;
    try {
      if (this.conn == null) conn = getConn();
      else conn = this.conn;

      // retrieve companies list...
      String companies =
          (String)
              gridParams
                  .getOtherGridParams()
                  .get(ApplicationConsts.COMPANY_CODE_SYS01); // used in lookup grid...
      if (companies == null) {
        companies = "";
        for (int i = 0; i < companiesList.size(); i++)
          companies += "'" + companiesList.get(i).toString() + "',";
        companies = companies.substring(0, companies.length() - 1);
      } else companies = "'" + companies + "'";

      String sql =
          "select SAL06_CHARGES.COMPANY_CODE_SYS01,SAL06_CHARGES.CHARGE_CODE,SAL06_CHARGES.PROGRESSIVE_SYS10,"
              + "SYS10_TRANSLATIONS.DESCRIPTION,SAL06_CHARGES.VALUE,SAL06_CHARGES.PERC,SAL06_CHARGES.VAT_CODE_REG01,"
              + "SAL06_CHARGES.CURRENCY_CODE_REG03,SAL06_CHARGES.ENABLED"
              + " from SAL06_CHARGES,SYS10_TRANSLATIONS where "
              + "SAL06_CHARGES.PROGRESSIVE_SYS10=SYS10_TRANSLATIONS.PROGRESSIVE and "
              + "SYS10_TRANSLATIONS.LANGUAGE_CODE=? and "
              + "SAL06_CHARGES.ENABLED='Y' and "
              + "SAL06_CHARGES.COMPANY_CODE_SYS01 in ("
              + companies
              + ")";

      Map attribute2dbField = new HashMap();
      attribute2dbField.put("companyCodeSys01SAL06", "SAL06_CHARGES.COMPANY_CODE_SYS01");
      attribute2dbField.put("chargeCodeSAL06", "SAL06_CHARGES.CHARGE_CODE");
      attribute2dbField.put("descriptionSYS10", "SYS10_TRANSLATIONS.DESCRIPTION");
      attribute2dbField.put("progressiveSys10SAL06", "SAL06_CHARGES.PROGRESSIVE_SYS10");
      attribute2dbField.put("valueSAL06", "SAL06_CHARGES.VALUE");
      attribute2dbField.put("percSAL06", "SAL06_CHARGES.PERC");
      attribute2dbField.put("vatCodeReg01SAL06", "SAL06_CHARGES.VAT_CODE_REG01");
      attribute2dbField.put("currencyCodeReg03SAL06", "SAL06_CHARGES.CURRENCY_CODE_REG03");
      attribute2dbField.put("enabledSAL06", "SAL06_CHARGES.ENABLED");

      ArrayList values = new ArrayList();
      values.add(serverLanguageId);

      // read from SAL06 table...
      Response res =
          CustomizeQueryUtil.getQuery(
              conn,
              new UserSessionParameters(username),
              sql,
              values,
              attribute2dbField,
              ChargeVO.class,
              "Y",
              "N",
              null,
              gridParams,
              50,
              true,
              customizedFields);
      if (res.isError()) throw new Exception(res.getErrorMessage());

      java.util.List list = ((VOListResponse) res).getRows();
      ChargeVO vo = null;
      sql =
          "select SYS10_TRANSLATIONS.DESCRIPTION,REG01_VATS.VALUE,REG01_VATS.DEDUCTIBLE "
              + "from SYS10_TRANSLATIONS,REG01_VATS where "
              + "REG01_VATS.PROGRESSIVE_SYS10=SYS10_TRANSLATIONS.PROGRESSIVE and "
              + "SYS10_TRANSLATIONS.LANGUAGE_CODE=? and "
              + "REG01_VATS.VAT_CODE=?";
      pstmt = conn.prepareStatement(sql);
      ResultSet rset = null;
      for (int i = 0; i < list.size(); i++) {
        vo = (ChargeVO) list.get(i);
        if (vo.getVatCodeReg01SAL06() != null) {
          // retrieve vat data from REG01...
          pstmt.setString(1, serverLanguageId);
          pstmt.setString(2, vo.getVatCodeReg01SAL06());
          rset = pstmt.executeQuery();
          if (rset.next()) {
            vo.setVatDescriptionSYS10(rset.getString(1));
            vo.setVatValueREG01(rset.getBigDecimal(2));
            vo.setVatDeductibleREG01(rset.getBigDecimal(3));
          }
          rset.close();
        }
      }

      Response answer = res;
      if (answer.isError()) throw new Exception(answer.getErrorMessage());
      else return (VOListResponse) answer;
    } catch (Throwable ex) {
      Logger.error(
          username,
          this.getClass().getName(),
          "executeCommand",
          "Error while fetching charges list",
          ex);
      throw new Exception(ex.getMessage());
    } finally {
      try {
        pstmt.close();
      } catch (Exception exx) {
      }
      try {
        if (this.conn == null && conn != null) {
          // close only local connection
          conn.commit();
          conn.close();
        }

      } catch (Exception exx) {
      }
    }
  }
  /** Business logic to execute. */
  public VOListResponse updateCharges(
      ArrayList oldVOs,
      ArrayList newVOs,
      String serverLanguageId,
      String username,
      ArrayList customizedFields)
      throws Throwable {
    Connection conn = null;
    try {
      if (this.conn == null) conn = getConn();
      else conn = this.conn;

      ChargeVO oldVO = null;
      ChargeVO newVO = null;
      Response res = null;

      for (int i = 0; i < oldVOs.size(); i++) {
        oldVO = (ChargeVO) oldVOs.get(i);
        newVO = (ChargeVO) newVOs.get(i);

        // update SYS10 table...
        TranslationUtils.updateTranslation(
            oldVO.getDescriptionSYS10(),
            newVO.getDescriptionSYS10(),
            newVO.getProgressiveSys10SAL06(),
            serverLanguageId,
            conn);

        HashSet pkAttrs = new HashSet();
        pkAttrs.add("companyCodeSys01SAL06");
        pkAttrs.add("chargeCodeSAL06");

        HashMap attr2dbFields = new HashMap();
        attr2dbFields.put("companyCodeSys01SAL06", "COMPANY_CODE_SYS01");
        attr2dbFields.put("chargeCodeSAL06", "CHARGE_CODE");
        attr2dbFields.put("progressiveSys10SAL06", "PROGRESSIVE_SYS10");
        attr2dbFields.put("valueSAL06", "VALUE");
        attr2dbFields.put("percSAL06", "PERC");
        attr2dbFields.put("vatCodeReg01SAL06", "VAT_CODE_REG01");
        attr2dbFields.put("currencyCodeReg03SAL06", "CURRENCY_CODE_REG03");

        res =
            new CustomizeQueryUtil()
                .updateTable(
                    conn,
                    new UserSessionParameters(username),
                    pkAttrs,
                    oldVO,
                    newVO,
                    "SAL06_CHARGES",
                    attr2dbFields,
                    "Y",
                    "N",
                    null,
                    true,
                    customizedFields);
        if (res.isError()) {
          throw new Exception(res.getErrorMessage());
        }
      }

      return new VOListResponse(newVOs, false, newVOs.size());
    } catch (Throwable ex) {
      Logger.error(
          username,
          this.getClass().getName(),
          "executeCommand",
          "Error while updating existing charges",
          ex);
      try {
        if (this.conn == null && conn != null)
          // rollback only local connection
          conn.rollback();
      } catch (Exception ex3) {
      }

      throw new Exception(ex.getMessage());
    } finally {
      try {
        if (this.conn == null && conn != null) {
          // close only local connection
          conn.commit();
          conn.close();
        }

      } catch (Exception exx) {
      }
    }
  }
  /**
   * Method called by the Form panel to update existing data.
   *
   * @param oldPersistentObject original value object, previous to the changes
   * @param persistentObject value object to save
   * @return an ErrorResponse value object in case of errors, VOResponse if the operation is
   *     successfully completed
   */
  public Response updateRecord(ValueObject oldPersistentObject, ValueObject persistentObject)
      throws Exception {
    // mapping between attributes and database fields...
    Map attribute2dbField = new HashMap();
    attribute2dbField.put("empCode", "EMP_CODE");
    attribute2dbField.put("firstName", "FIRST_NAME");
    attribute2dbField.put("lastName", "LAST_NAME");
    attribute2dbField.put("deptCode", "DEPT_CODE");
    attribute2dbField.put("taskCode", "TASK_CODE");
    attribute2dbField.put("sex", "SEX");
    attribute2dbField.put("hireDate", "HIRE_DATE");
    attribute2dbField.put("salary", "SALARY");
    attribute2dbField.put("note", "NOTE");

    HashSet pk = new HashSet();
    pk.add("empCode");

    Response res =
        QueryUtil.updateTable(
            conn,
            pk,
            oldPersistentObject,
            persistentObject,
            "EMP",
            attribute2dbField,
            "Y",
            "N",
            true);
    if (res.isError()) conn.rollback();
    else conn.commit();
    return res;

    /*
        // an alternative way: you can define your own business logic to store data at hand...
        PreparedStatement stmt = null;
        try {
          stmt = conn.prepareStatement("update EMP set EMP_CODE=?,FIRST_NAME=?,LAST_NAME=?,DEPT_CODE=?,TASK_CODE=?,SEX=?,HIRE_DATE=?,SALARY=?,NOTE=? where EMP_CODE=?");
          EmpVO vo = (EmpVO)persistentObject;
          stmt.setString(1,vo.getEmpCode());
          stmt.setString(2,vo.getFirstName());
          stmt.setString(3,vo.getLastName());
          stmt.setString(4,vo.getDeptCode());
          stmt.setString(5,vo.getTaskCode());
          stmt.setString(6,vo.getSex());
          stmt.setDate(7,vo.getHireDate());
          stmt.setBigDecimal(8,vo.getSalary());
          stmt.setString(9,vo.getNote());
          stmt.setString(10,vo.getEmpCode());
          stmt.execute();
          gridFrame.reloadData();
          return new VOResponse(vo);
        }
        catch (SQLException ex) {
          ex.printStackTrace();
          return new ErrorResponse(ex.getMessage());
        }
        finally {
          try {
            stmt.close();
            conn.commit();
          }
          catch (SQLException ex1) {
          }
        }
    */
  }
  /** Business logic to execute. */
  public final Response executeCommand(
      Object inputPar,
      UserSessionParameters userSessionPars,
      HttpServletRequest request,
      HttpServletResponse response,
      HttpSession userSession,
      ServletContext context) {
    String serverLanguageId = ((JAIOUserSessionParameters) userSessionPars).getServerLanguageId();
    Connection conn = null;
    PreparedStatement pstmt = null;
    try {
      conn = ConnectionManager.getConnection(context);

      // fires the GenericEvent.CONNECTION_CREATED event...
      EventsManager.getInstance()
          .processEvent(
              new GenericEvent(
                  this,
                  getRequestName(),
                  GenericEvent.CONNECTION_CREATED,
                  (JAIOUserSessionParameters) userSessionPars,
                  request,
                  response,
                  userSession,
                  context,
                  conn,
                  inputPar,
                  null));
      DetailSaleDocVO docVO = (DetailSaleDocVO) inputPar;

      // retrieve internationalization settings (Resources object)...
      ServerResourcesFactory factory =
          (ServerResourcesFactory) context.getAttribute(Controller.RESOURCES_FACTORY);
      Resources resources = factory.getResources(userSessionPars.getLanguageId());

      // insert header...
      docVO.setDocStateDOC01(ApplicationConsts.HEADER_BLOCKED);
      Response res =
          insDocBean.insertSaleDoc(
              conn, docVO, userSessionPars, request, response, userSession, context);
      if (res.isError()) {
        conn.rollback();
        return res;
      }

      SaleDocPK refPK =
          new SaleDocPK(
              docVO.getCompanyCodeSys01Doc01DOC01(),
              docVO.getDocTypeDoc01DOC01(),
              docVO.getDocYearDoc01DOC01(),
              docVO.getDocNumberDoc01DOC01());

      // retrieve ref. document item rows...
      GridParams gridParams = new GridParams();
      gridParams.getOtherGridParams().put(ApplicationConsts.SALE_DOC_PK, refPK);
      res =
          rowsAction.executeCommand(
              gridParams, userSessionPars, request, response, userSession, context);
      if (res.isError()) {
        conn.rollback();
        return res;
      }
      java.util.List rows = ((VOListResponse) res).getRows();

      // create rows..
      GridSaleDocRowVO gridRowVO = null;
      DetailSaleDocRowVO rowVO = null;
      java.util.List discRows = null;
      SaleDocRowPK docRowPK = null;
      SaleItemDiscountVO itemDiscVO = null;
      gridParams = new GridParams();
      for (int i = 0; i < rows.size(); i++) {
        gridRowVO = (GridSaleDocRowVO) rows.get(i);

        // retrieve row detail...
        docRowPK =
            new SaleDocRowPK(
                gridRowVO.getCompanyCodeSys01DOC02(),
                gridRowVO.getDocTypeDOC02(),
                gridRowVO.getDocYearDOC02(),
                gridRowVO.getDocNumberDOC02(),
                gridRowVO.getItemCodeItm01DOC02(),
                gridRowVO.getVariantTypeItm06DOC02(),
                gridRowVO.getVariantCodeItm11DOC02(),
                gridRowVO.getVariantTypeItm07DOC02(),
                gridRowVO.getVariantCodeItm12DOC02(),
                gridRowVO.getVariantTypeItm08DOC02(),
                gridRowVO.getVariantCodeItm13DOC02(),
                gridRowVO.getVariantTypeItm09DOC02(),
                gridRowVO.getVariantCodeItm14DOC02(),
                gridRowVO.getVariantTypeItm10DOC02(),
                gridRowVO.getVariantCodeItm15DOC02());

        res =
            rowAction.executeCommand(
                docRowPK, userSessionPars, request, response, userSession, context);
        if (res.isError()) {
          conn.rollback();
          return res;
        }
        rowVO = (DetailSaleDocRowVO) ((VOResponse) res).getVo();
        rowVO.setDocTypeDOC02(docVO.getDocTypeDOC01());
        rowVO.setDocNumberDOC02(docVO.getDocNumberDOC01());
        if (rowVO.getInvoiceQtyDOC02().doubleValue() < rowVO.getQtyDOC02().doubleValue()
            && rowVO.getQtyDOC02().doubleValue() == rowVO.getOutQtyDOC02().doubleValue()) {
          rowVO.setQtyDOC02(
              rowVO
                  .getQtyDOC02()
                  .subtract(
                      rowVO
                          .getInvoiceQtyDOC02()
                          .setScale(
                              rowVO.getDecimalsReg02DOC02().intValue(), BigDecimal.ROUND_HALF_UP)));
          rowVO.setTaxableIncomeDOC02(
              rowVO
                  .getQtyDOC02()
                  .multiply(rowVO.getValueSal02DOC02())
                  .setScale(docVO.getDecimalsREG03().intValue(), BigDecimal.ROUND_HALF_UP));
          rowVO.setTotalDiscountDOC02(new BigDecimal(0));

          // calculate row vat...
          double vatPerc =
              rowVO.getValueReg01DOC02().doubleValue()
                  * (1d - rowVO.getDeductibleReg01DOC02().doubleValue() / 100d)
                  / 100;
          rowVO.setVatValueDOC02(
              rowVO
                  .getTaxableIncomeDOC02()
                  .multiply(new BigDecimal(vatPerc))
                  .setScale(docVO.getDecimalsREG03().intValue(), BigDecimal.ROUND_HALF_UP));

          // calculate row total...
          rowVO.setValueDOC02(rowVO.getTaxableIncomeDOC02().add(rowVO.getVatValueDOC02()));

          res =
              insRowBean.insertSaleItem(
                  conn, rowVO, userSessionPars, request, response, userSession, context);
          if (res.isError()) {
            conn.rollback();
            return res;
          }

          // create item discounts...
          gridParams.getOtherGridParams().put(ApplicationConsts.SALE_DOC_ROW_PK, docRowPK);
          res =
              itemDiscAction.executeCommand(
                  gridParams, userSessionPars, request, response, userSession, context);
          if (res.isError()) {
            conn.rollback();
            return res;
          }
          discRows = ((VOListResponse) res).getRows();
          for (int j = 0; j < discRows.size(); j++) {
            itemDiscVO = (SaleItemDiscountVO) discRows.get(j);
            itemDiscVO.setDocTypeDOC04(docVO.getDocTypeDOC01());
            itemDiscVO.setDocNumberDOC04(docVO.getDocNumberDOC01());
            res =
                insItemDiscBean.insertSaleDocRowDiscount(
                    conn, itemDiscVO, userSessionPars, request, response, userSession, context);
            if (res.isError()) {
              conn.rollback();
              return res;
            }
          }
        }
      }

      // create charges...
      gridParams = new GridParams();
      gridParams.getOtherGridParams().put(ApplicationConsts.SALE_DOC_PK, refPK);
      res =
          chargesAction.executeCommand(
              gridParams, userSessionPars, request, response, userSession, context);
      SaleDocChargeVO chargeVO = null;
      if (res.isError()) {
        conn.rollback();
        return res;
      }
      rows = ((VOListResponse) res).getRows();
      for (int i = 0; i < rows.size(); i++) {
        chargeVO = (SaleDocChargeVO) rows.get(i);
        chargeVO.setDocTypeDOC03(docVO.getDocTypeDOC01());
        chargeVO.setDocNumberDOC03(docVO.getDocNumberDOC01());
        if (chargeVO.getValueDOC03() == null
            || chargeVO.getValueDOC03() != null
                && chargeVO.getInvoicedValueDOC03().doubleValue()
                    < chargeVO.getValueDOC03().doubleValue()) {
          if (chargeVO.getValueDOC03() != null)
            chargeVO.setValueDOC03(
                chargeVO
                    .getValueDOC03()
                    .subtract(chargeVO.getInvoicedValueDOC03())
                    .setScale(docVO.getDecimalsREG03().intValue(), BigDecimal.ROUND_HALF_UP));

          res =
              insChargeBean.insertSaleDocCharge(
                  conn, chargeVO, userSessionPars, request, response, userSession, context);
          if (res.isError()) {
            conn.rollback();
            return res;
          }
        }
      }

      // create activities...
      res =
          actAction.executeCommand(
              gridParams, userSessionPars, request, response, userSession, context);
      if (res.isError()) {
        conn.rollback();
        return res;
      }
      SaleDocActivityVO actVO = null;
      rows = ((VOListResponse) res).getRows();
      for (int i = 0; i < rows.size(); i++) {
        actVO = (SaleDocActivityVO) rows.get(i);
        actVO.setDocTypeDOC13(docVO.getDocTypeDOC01());
        actVO.setDocNumberDOC13(docVO.getDocNumberDOC01());
        if (actVO.getInvoicedValueDOC13().doubleValue() < actVO.getValueDOC13().doubleValue()) {
          actVO.setValueDOC13(
              actVO
                  .getValueDOC13()
                  .subtract(actVO.getInvoicedValueDOC13())
                  .setScale(docVO.getDecimalsREG03().intValue(), BigDecimal.ROUND_HALF_UP));
          res =
              insActBean.insertSaleActivity(
                  conn, actVO, userSessionPars, request, response, userSession, context);
          if (res.isError()) {
            conn.rollback();
            return res;
          }
        }
      }

      // create header discounts...
      res =
          discAction.executeCommand(
              gridParams, userSessionPars, request, response, userSession, context);
      if (res.isError()) {
        conn.rollback();
        return res;
      }
      SaleDocDiscountVO discVO = null;
      rows = ((VOListResponse) res).getRows();
      for (int i = 0; i < rows.size(); i++) {
        discVO = (SaleDocDiscountVO) rows.get(i);
        discVO.setDocTypeDOC05(docVO.getDocTypeDOC01());
        discVO.setDocNumberDOC05(docVO.getDocNumberDOC01());
        res =
            insDiscBean.insertSaleDocDiscount(
                conn, discVO, userSessionPars, request, response, userSession, context);
        if (res.isError()) {
          conn.rollback();
          return res;
        }
      }

      // recalculate all taxable incomes, vats, totals...
      SaleDocPK pk =
          new SaleDocPK(
              docVO.getCompanyCodeSys01DOC01(),
              docVO.getDocTypeDOC01(),
              docVO.getDocYearDOC01(),
              docVO.getDocNumberDOC01());
      res =
          totals.updateTaxableIncomes(
              conn, pk, userSessionPars, request, response, userSession, context);
      if (res.isError()) {
        conn.rollback();
        return res;
      }

      // reload doc header with updated totals...
      Response answer =
          docAction.loadSaleDoc(conn, pk, userSessionPars, request, response, userSession, context);

      // fires the GenericEvent.BEFORE_COMMIT event...
      EventsManager.getInstance()
          .processEvent(
              new GenericEvent(
                  this,
                  getRequestName(),
                  GenericEvent.BEFORE_COMMIT,
                  (JAIOUserSessionParameters) userSessionPars,
                  request,
                  response,
                  userSession,
                  context,
                  conn,
                  inputPar,
                  answer));

      conn.commit();

      // fires the GenericEvent.AFTER_COMMIT event...
      EventsManager.getInstance()
          .processEvent(
              new GenericEvent(
                  this,
                  getRequestName(),
                  GenericEvent.AFTER_COMMIT,
                  (JAIOUserSessionParameters) userSessionPars,
                  request,
                  response,
                  userSession,
                  context,
                  conn,
                  inputPar,
                  answer));

      return answer;
    } catch (Throwable ex) {
      Logger.error(
          userSessionPars.getUsername(),
          this.getClass().getName(),
          "executeCommand",
          "Error while creating a sale invoice from a sale document",
          ex);
      try {
        conn.rollback();
      } catch (Exception ex3) {
      }
      return new ErrorResponse(ex.getMessage());
    } finally {
      try {
        pstmt.close();
      } catch (Exception ex2) {
      }

      try {
        ConnectionManager.releaseConnection(conn, context);
      } catch (Exception ex1) {
      }
    }
  }
  /** Business logic to execute. */
  public static VOResponse loadItemImplosion(
      Connection conn, ItemPK pk, String langId, String username) throws Throwable {
    PreparedStatement pstmt = null;
    PreparedStatement pstmt2 = null;
    try {

      String sql1 =
          "select ITM01_ITEMS.COMPANY_CODE_SYS01,ITM01_ITEMS.ITEM_CODE,ITM01_ITEMS.MIN_SELLING_QTY_UM_CODE_REG02,"
              + "ITM03_COMPONENTS.ITEM_CODE_ITM01,ITM03_COMPONENTS.QTY,SYS10_COMPANY_TRANSLATIONS.DESCRIPTION "
              + "from ITM03_COMPONENTS,ITM01_ITEMS,SYS10_COMPANY_TRANSLATIONS "
              + "where "
              + "ITM03_COMPONENTS.COMPANY_CODE_SYS01 = ITM01_ITEMS.COMPANY_CODE_SYS01 and "
              + "ITM03_COMPONENTS.PARENT_ITEM_CODE_ITM01 = ITM01_ITEMS.ITEM_CODE and "
              + "ITM01_ITEMS.COMPANY_CODE_SYS01 = SYS10_COMPANY_TRANSLATIONS.COMPANY_CODE_SYS01 and "
              + "ITM01_ITEMS.PROGRESSIVE_SYS10 = SYS10_COMPANY_TRANSLATIONS.PROGRESSIVE and "
              + "SYS10_COMPANY_TRANSLATIONS.LANGUAGE_CODE=? and ITM03_COMPONENTS.ENABLED='Y' and "
              + "ITM03_COMPONENTS.COMPANY_CODE_SYS01=? and "
              + "ITM03_COMPONENTS.ITEM_CODE_ITM01 in (";
      String sql3 = ") ";

      Map attribute2dbField = new HashMap();
      attribute2dbField.put("companyCodeSys01ITM03", "ITM01_ITEMS.COMPANY_CODE_SYS01");
      attribute2dbField.put("itemCodeItm01ITM03", "ITM03_COMPONENTS.ITEM_CODE_ITM01");
      attribute2dbField.put("parentItemCodeItm01ITM03", "ITM01_ITEMS.ITEM_CODE");
      attribute2dbField.put("descriptionSYS10", "SYS10_COMPANY_TRANSLATIONS.DESCRIPTION");
      attribute2dbField.put("qtyITM03", "ITM03_COMPONENTS.QTY");
      attribute2dbField.put(
          "minSellingQtyUmCodeReg02ITM01", "ITM01_ITEMS.MIN_SELLING_QTY_UM_CODE_REG02");

      ArrayList values = new ArrayList();
      values.add(langId);
      values.add(pk.getCompanyCodeSys01ITM01());

      // retrieve the whole tree...
      MaterialVO rootVO = new MaterialVO();
      rootVO.setCompanyCodeSys01ITM03(pk.getCompanyCodeSys01ITM01());
      rootVO.setDescriptionSYS10("");
      rootVO.setParentItemCodeItm01ITM03("");
      rootVO.setItemCodeItm01ITM03("");
      DefaultMutableTreeNode root = new OpenSwingTreeNode(rootVO);
      DefaultTreeModel model = new DefaultTreeModel(root);

      String sql2 = "'" + pk.getItemCodeITM01() + "'";

      // read from ITM03 table...
      Response res = null;
      java.util.List rows = null;
      DefaultMutableTreeNode auxnode, auxnode2;
      MaterialVO auxVO = null;
      Hashtable nodes = new Hashtable(); // collections of <item component code,related node>
      Hashtable auxnodes = new Hashtable(); // collections of <item component code,related node>
      Enumeration en = null;

      do {
        res =
            QueryUtil.getQuery(
                conn,
                new UserSessionParameters(username),
                sql1 + sql2 + sql3,
                values,
                attribute2dbField,
                MaterialVO.class,
                "Y",
                "N",
                null,
                new GridParams(),
                true);
        if (!res.isError()) {
          rows = ((VOListResponse) res).getRows();

          sql2 = "";

          for (int i = 0; i < rows.size(); i++) {
            auxVO = (MaterialVO) rows.get(i);
            sql2 += "'" + auxVO.getParentItemCodeItm01ITM03() + "',";
            auxnode = new OpenSwingTreeNode(auxVO);
            auxnode2 = (DefaultMutableTreeNode) nodes.get(auxVO.getItemCodeItm01ITM03());
            if (auxnode2 != null) auxnode.add(auxnode2);
            nodes.remove(auxVO.getItemCodeItm01ITM03());
            auxnodes.put(auxVO.getParentItemCodeItm01ITM03(), auxnode);
          }

          en = nodes.keys();
          while (en.hasMoreElements()) {
            root.add((DefaultMutableTreeNode) nodes.get(en.nextElement()));
          }

          nodes = auxnodes;
          if (sql2.length() > 0) sql2 = sql2.substring(0, sql2.length() - 1);
        }
      } while (!res.isError() && rows.size() > 0);
      if (res.isError()) throw new Exception(res.getErrorMessage());

      return new VOResponse(model);
    } catch (Exception ex1) {
      Logger.error(
          username,
          BillOfMaterialsUtil.class.getName(),
          "executeCommand",
          "Error while fetching products that use the specified component",
          ex1);
      throw new Exception(ex1.getMessage());
    }
  }
  /**
   * Navigate tree and fetch item purchase price and production costs.
   *
   * @param node current analyzed node
   */
  private static Response expandNode(
      Connection conn,
      CurrencyVO currVO,
      PreparedStatement pstmt,
      PreparedStatement pstmt2,
      DefaultMutableTreeNode node)
      throws Exception {
    ResultSet rset = null;
    try {
      MaterialVO vo = (MaterialVO) node.getUserObject();
      if (node.getChildCount() == 0) {
        // the current node is a leaf, so it cannot be constructed: it will be purchased...
        pstmt.setString(1, vo.getCompanyCodeSys01ITM03());
        pstmt.setString(2, vo.getItemCodeItm01ITM03());
        pstmt.setDate(3, new java.sql.Date(System.currentTimeMillis()));
        pstmt.setDate(4, new java.sql.Date(System.currentTimeMillis() + 86400 * 1000 - 1));
        rset = pstmt.executeQuery();
        BigDecimal totPrice = new BigDecimal(0);
        BigDecimal totNum = new BigDecimal(0);
        BigDecimal price = null;
        BigDecimal num = null;
        String currencyCode = null;
        while (rset.next()) {
          price = rset.getBigDecimal(1);
          num = rset.getBigDecimal(2);
          currencyCode = rset.getString(3);
          price =
              CurrencyConversionUtils.convertCurrencyToCurrency(
                  new java.sql.Date(System.currentTimeMillis()),
                  price,
                  currencyCode,
                  currVO.getCurrencyCodeREG03(),
                  conn);
          if (price != null && num != null) {
            totPrice = totPrice.add(price);
            totNum = totNum.add(num);
          }
        }
        rset.close();
        if (totPrice.doubleValue() > 0) {
          vo.setValuePUR04(
              vo.getQtyITM03()
                  .multiply(totPrice)
                  .divide(num, BigDecimal.ROUND_HALF_UP)
                  .setScale(5, BigDecimal.ROUND_HALF_UP));
          vo.setTotalPrices(vo.getValuePUR04());
        }
      } else {
        // the current node is not a leaf, so it will be constructed: now it will be calculated
        // production costs...
        pstmt2.setString(1, vo.getCompanyCodeSys01ITM03());
        pstmt2.setString(2, vo.getItemCodeItm01ITM03());
        rset = pstmt2.executeQuery();
        BigDecimal cost = null;
        if (rset.next()) {
          cost = rset.getBigDecimal(1);
        }
        rset.close();
        if (cost != null && cost.doubleValue() > 0) {
          vo.setValuePRO02(vo.getQtyITM03().multiply(cost).setScale(5, BigDecimal.ROUND_HALF_UP));
        }

        // navigate through children nodes...
        Response res = null;
        for (int i = 0; i < node.getChildCount(); i++) {
          res =
              expandNode(conn, currVO, pstmt, pstmt2, (DefaultMutableTreeNode) node.getChildAt(i));
          if (res.isError()) return res;
        }
        if (res.isError()) return res;

        // calculate total costs + total prices...
        MaterialVO childVO = null;
        BigDecimal totalPrices = new BigDecimal(0);
        BigDecimal totalCosts = vo.getValuePRO02();
        for (int i = 0; i < node.getChildCount(); i++) {
          childVO = (MaterialVO) ((DefaultMutableTreeNode) node.getChildAt(i)).getUserObject();
          if (childVO.getTotalPrices() != null && totalPrices != null)
            //            totalPrices =
            // totalPrices.add(childVO.getTotalPrices().multiply(childVO.getQtyITM03()));
            totalPrices = totalPrices.add(childVO.getTotalPrices());
          else totalPrices = null;
          if (childVO.getTotalCosts() != null && totalCosts != null)
            //            totalCosts =
            // totalCosts.add(childVO.getTotalCosts().multiply(childVO.getQtyITM03()));
            totalCosts = totalCosts.add(childVO.getTotalCosts());
        }
        if (totalCosts != null) vo.setTotalCosts(totalCosts.setScale(5, BigDecimal.ROUND_HALF_UP));
        if (totalPrices != null && totalPrices != null)
          vo.setTotalPrices(totalPrices.setScale(5, BigDecimal.ROUND_HALF_UP));
      }

      return new VOResponse(Boolean.TRUE);

    } catch (Exception ex) {
      ex.printStackTrace();
      throw new Exception(ex.getMessage());
    } finally {
      try {
        if (rset != null) rset.close();
      } catch (Exception ex1) {
      }
    }
  }
  /** Business logic to execute. */
  public final Response executeCommand(
      Object inputPar,
      UserSessionParameters userSessionPars,
      HttpServletRequest request,
      HttpServletResponse response,
      HttpSession userSession,
      ServletContext context) {
    Connection conn = null;
    try {
      conn = ConnectionManager.getConnection(context);

      // fires the GenericEvent.CONNECTION_CREATED event...
      EventsManager.getInstance()
          .processEvent(
              new GenericEvent(
                  this,
                  getRequestName(),
                  GenericEvent.CONNECTION_CREATED,
                  (JAIOUserSessionParameters) userSessionPars,
                  request,
                  response,
                  userSession,
                  context,
                  conn,
                  inputPar,
                  null));
      CallOutTaskVO vo = null;

      ArrayList list = (ArrayList) inputPar;

      Map attribute2dbField = new HashMap();
      attribute2dbField.put("callOutCodeSch10SCH12", "CALL_OUT_CODE_SCH10");
      attribute2dbField.put("companyCodeSys01SCH12", "COMPANY_CODE_SYS01");
      attribute2dbField.put("taskCodeReg07SCH12", "TASK_CODE_REG07");

      Response res = null;
      for (int i = 0; i < list.size(); i++) {
        vo = (CallOutTaskVO) list.get(i);

        // insert into SCH12...
        res =
            QueryUtil.insertTable(
                conn,
                userSessionPars,
                vo,
                "SCH12_CALL_OUT_TASKS",
                attribute2dbField,
                "Y",
                "N",
                context,
                true);
        if (res.isError()) {
          conn.rollback();
          return res;
        }
      }

      Response answer = new VOListResponse(list, false, list.size());

      // fires the GenericEvent.BEFORE_COMMIT event...
      EventsManager.getInstance()
          .processEvent(
              new GenericEvent(
                  this,
                  getRequestName(),
                  GenericEvent.BEFORE_COMMIT,
                  (JAIOUserSessionParameters) userSessionPars,
                  request,
                  response,
                  userSession,
                  context,
                  conn,
                  inputPar,
                  answer));

      conn.commit();

      // fires the GenericEvent.AFTER_COMMIT event...
      EventsManager.getInstance()
          .processEvent(
              new GenericEvent(
                  this,
                  getRequestName(),
                  GenericEvent.AFTER_COMMIT,
                  (JAIOUserSessionParameters) userSessionPars,
                  request,
                  response,
                  userSession,
                  context,
                  conn,
                  inputPar,
                  answer));

      return answer;
    } catch (Throwable ex) {
      Logger.error(
          userSessionPars.getUsername(),
          this.getClass().getName(),
          "executeCommand",
          "Error while inserting new call-out tasks",
          ex);
      try {
        conn.rollback();
      } catch (Exception ex3) {
      }
      return new ErrorResponse(ex.getMessage());
    } finally {
      try {
        ConnectionManager.releaseConnection(conn, context);
      } catch (Exception ex1) {
      }
    }
  }
  /** Business logic to execute. */
  public VOListResponse loadItemVariants(GridParams pars, String serverLanguageId, String username)
      throws Throwable {

    PreparedStatement pstmt = null;

    Connection conn = null;
    try {
      if (this.conn == null) conn = getConn();
      else conn = this.conn;

      String tableName = (String) pars.getOtherGridParams().get(ApplicationConsts.TABLE_NAME);
      ItemPK pk = (ItemPK) pars.getOtherGridParams().get(ApplicationConsts.ITEM_PK);
      String productVariant = (String) productVariants.get(tableName);
      String variantType = (String) variantTypes.get(tableName);
      String variantTypeJoin = (String) variantTypeJoins.get(tableName);
      String variantCodeJoin = (String) variantCodeJoins.get(tableName);

      String sql =
          "select "
              + tableName
              + "."
              + variantTypeJoin
              + ","
              + tableName
              + ".VARIANT_CODE,A.DESCRIPTION,B.DESCRIPTION, "
              + tableName
              + ".PROGRESSIVE_SYS10,"
              + variantType
              + ".PROGRESSIVE_SYS10 "
              + "from "
              + tableName
              + ","
              + variantType
              + ",SYS10_COMPANY_TRANSLATIONS A,SYS10_COMPANY_TRANSLATIONS B "
              + "where "
              + tableName
              + ".COMPANY_CODE_SYS01=? and "
              + tableName
              + ".COMPANY_CODE_SYS01="
              + variantType
              + ".COMPANY_CODE_SYS01 and "
              + tableName
              + "."
              + variantTypeJoin
              + "="
              + variantType
              + ".VARIANT_TYPE and "
              + tableName
              + ".COMPANY_CODE_SYS01=A.COMPANY_CODE_SYS01 and "
              + tableName
              + ".PROGRESSIVE_SYS10=A.PROGRESSIVE and A.LANGUAGE_CODE=? and "
              + variantType
              + ".COMPANY_CODE_SYS01=B.COMPANY_CODE_SYS01 and "
              + variantType
              + ".PROGRESSIVE_SYS10=B.PROGRESSIVE and B.LANGUAGE_CODE=? and "
              + tableName
              + ".ENABLED='Y' and "
              + variantType
              + ".ENABLED='Y' and "
              + // and not "+tableName+"."+variantTypeJoin+"=? and "+
              "not "
              + tableName
              + ".VARIANT_CODE=? "
              + "order by "
              + tableName
              + "."
              + variantTypeJoin
              + ","
              + tableName
              + ".CODE_ORDER";

      Map attribute2dbField = new HashMap();
      attribute2dbField.put("variantType", tableName + "." + variantTypeJoin);
      attribute2dbField.put("variantCode", tableName + ".VARIANT_CODE");
      attribute2dbField.put("variantDesc", "A.DESCRIPTION");
      attribute2dbField.put("variantTypeDesc", "B.DESCRIPTION");
      attribute2dbField.put("variantProgressiveSys10", tableName + ".PROGRESSIVE_SYS10");
      attribute2dbField.put("variantTypeProgressiveSys10", variantType + ".PROGRESSIVE_SYS10");

      ArrayList values = new ArrayList();
      values.add(pk.getCompanyCodeSys01ITM01());
      values.add(serverLanguageId);
      values.add(serverLanguageId);
      // values.add(ApplicationConsts.JOLLY);
      values.add(ApplicationConsts.JOLLY);

      // read from ITMxxx table...
      Response answer =
          QueryUtil.getQuery(
              conn,
              new UserSessionParameters(username),
              sql,
              values,
              attribute2dbField,
              ItemVariantVO.class,
              "Y",
              "N",
              null,
              pars,
              50,
              true);

      if (!answer.isError()) {
        java.util.List vos = ((VOListResponse) answer).getRows();
        HashMap map = new HashMap();
        ItemVariantVO vo = null;
        for (int i = 0; i < vos.size(); i++) {
          vo = (ItemVariantVO) vos.get(i);
          vo.setCompanyCodeSys01(pk.getCompanyCodeSys01ITM01());
          vo.setItemCodeItm01(pk.getItemCodeITM01());
          vo.setTableName(tableName);
          map.put(vo.getVariantType() + "." + vo.getVariantCode(), vo);
        }

        pstmt =
            conn.prepareStatement(
                "select "
                    + productVariant
                    + "."
                    + variantTypeJoin
                    + ","
                    + productVariant
                    + "."
                    + variantCodeJoin
                    + " "
                    + "from "
                    + productVariant
                    + " "
                    + "where "
                    + productVariant
                    + ".COMPANY_CODE_SYS01=? and "
                    + productVariant
                    + ".ITEM_CODE_ITM01=? and "
                    + productVariant
                    + ".ENABLED='Y' ");
        pstmt.setString(1, pk.getCompanyCodeSys01ITM01());
        pstmt.setString(2, pk.getItemCodeITM01());
        ResultSet rset = pstmt.executeQuery();

        while (rset.next()) {
          vo = (ItemVariantVO) map.get(rset.getString(1) + "." + rset.getString(2));
          if (vo != null) vo.setSelected(Boolean.TRUE);
        }
        rset.close();
        pstmt.close();
      }

      if (answer.isError()) throw new Exception(answer.getErrorMessage());
      else return (VOListResponse) answer;

    } catch (Throwable ex) {
      Logger.error(
          username,
          this.getClass().getName(),
          "getItemVariants",
          "Error while fetching item variants list",
          ex);
      throw new Exception(ex.getMessage());
    } finally {
      try {
        pstmt.close();
      } catch (Exception ex2) {
      }
      try {
        if (this.conn == null && conn != null) {
          // close only local connection
          conn.commit();
          conn.close();
        }

      } catch (Exception exx) {
      }
    }
  }
  /** Business logic to execute. */
  public final Response executeCommand(
      Object inputPar,
      UserSessionParameters userSessionPars,
      HttpServletRequest request,
      HttpServletResponse response,
      HttpSession userSession,
      ServletContext context) {
    String serverLanguageId = ((JAIOUserSessionParameters) userSessionPars).getServerLanguageId();

    Connection conn = null;
    PreparedStatement pstmt = null;
    try {
      conn = ConnectionManager.getConnection(context);

      // fires the GenericEvent.CONNECTION_CREATED event...
      EventsManager.getInstance()
          .processEvent(
              new GenericEvent(
                  this,
                  getRequestName(),
                  GenericEvent.CONNECTION_CREATED,
                  (JAIOUserSessionParameters) userSessionPars,
                  request,
                  response,
                  userSession,
                  context,
                  conn,
                  inputPar,
                  null));

      // retrieve companies list...
      GridParams gridParams = (GridParams) inputPar;
      String companies =
          (String)
              gridParams
                  .getOtherGridParams()
                  .get(ApplicationConsts.COMPANY_CODE_SYS01); // used in lookup grid...
      if (companies == null) {
        ArrayList companiesList =
            ((JAIOUserSessionParameters) userSessionPars).getCompanyBa().getCompaniesList("SAL06");
        companies = "";
        for (int i = 0; i < companiesList.size(); i++)
          companies += "'" + companiesList.get(i).toString() + "',";
        companies = companies.substring(0, companies.length() - 1);
      } else companies = "'" + companies + "'";

      String sql =
          "select SAL06_CHARGES.COMPANY_CODE_SYS01,SAL06_CHARGES.CHARGE_CODE,SAL06_CHARGES.PROGRESSIVE_SYS10,"
              + "SYS10_TRANSLATIONS.DESCRIPTION,SAL06_CHARGES.VALUE,SAL06_CHARGES.PERC,SAL06_CHARGES.VAT_CODE_REG01,"
              + "SAL06_CHARGES.CURRENCY_CODE_REG03,SAL06_CHARGES.ENABLED"
              + " from SAL06_CHARGES,SYS10_TRANSLATIONS where "
              + "SAL06_CHARGES.PROGRESSIVE_SYS10=SYS10_TRANSLATIONS.PROGRESSIVE and "
              + "SYS10_TRANSLATIONS.LANGUAGE_CODE=? and "
              + "SAL06_CHARGES.ENABLED='Y' and "
              + "SAL06_CHARGES.COMPANY_CODE_SYS01 in ("
              + companies
              + ")";

      Map attribute2dbField = new HashMap();
      attribute2dbField.put("companyCodeSys01SAL06", "SAL06_CHARGES.COMPANY_CODE_SYS01");
      attribute2dbField.put("chargeCodeSAL06", "SAL06_CHARGES.CHARGE_CODE");
      attribute2dbField.put("descriptionSYS10", "SYS10_TRANSLATIONS.DESCRIPTION");
      attribute2dbField.put("progressiveSys10SAL06", "SAL06_CHARGES.PROGRESSIVE_SYS10");
      attribute2dbField.put("valueSAL06", "SAL06_CHARGES.VALUE");
      attribute2dbField.put("percSAL06", "SAL06_CHARGES.PERC");
      attribute2dbField.put("vatCodeReg01SAL06", "SAL06_CHARGES.VAT_CODE_REG01");
      attribute2dbField.put("currencyCodeReg03SAL06", "SAL06_CHARGES.CURRENCY_CODE_REG03");
      attribute2dbField.put("enabledSAL06", "SAL06_CHARGES.ENABLED");

      ArrayList values = new ArrayList();
      values.add(serverLanguageId);

      // read from SAL06 table...
      Response res =
          CustomizeQueryUtil.getQuery(
              conn,
              userSessionPars,
              sql,
              values,
              attribute2dbField,
              ChargeVO.class,
              "Y",
              "N",
              context,
              gridParams,
              50,
              true,
              new BigDecimal(292) // window identifier...
              );
      if (res.isError()) return res;

      ArrayList list = ((VOListResponse) res).getRows();
      ChargeVO vo = null;
      sql =
          "select SYS10_TRANSLATIONS.DESCRIPTION,REG01_VATS.VALUE,REG01_VATS.DEDUCTIBLE "
              + "from SYS10_TRANSLATIONS,REG01_VATS where "
              + "REG01_VATS.PROGRESSIVE_SYS10=SYS10_TRANSLATIONS.PROGRESSIVE and "
              + "SYS10_TRANSLATIONS.LANGUAGE_CODE=? and "
              + "REG01_VATS.VAT_CODE=?";
      pstmt = conn.prepareStatement(sql);
      ResultSet rset = null;
      for (int i = 0; i < list.size(); i++) {
        vo = (ChargeVO) list.get(i);
        if (vo.getVatCodeReg01SAL06() != null) {
          // retrieve vat data from REG01...
          pstmt.setString(1, serverLanguageId);
          pstmt.setString(2, vo.getVatCodeReg01SAL06());
          rset = pstmt.executeQuery();
          if (rset.next()) {
            vo.setVatDescriptionSYS10(rset.getString(1));
            vo.setVatValueREG01(rset.getBigDecimal(2));
            vo.setVatDeductibleREG01(rset.getBigDecimal(3));
          }
          rset.close();
        }
      }

      Response answer = res;

      // fires the GenericEvent.BEFORE_COMMIT event...
      EventsManager.getInstance()
          .processEvent(
              new GenericEvent(
                  this,
                  getRequestName(),
                  GenericEvent.BEFORE_COMMIT,
                  (JAIOUserSessionParameters) userSessionPars,
                  request,
                  response,
                  userSession,
                  context,
                  conn,
                  inputPar,
                  answer));

      return answer;
    } catch (Throwable ex) {
      Logger.error(
          userSessionPars.getUsername(),
          this.getClass().getName(),
          "executeCommand",
          "Error while fetching charges list",
          ex);
      return new ErrorResponse(ex.getMessage());
    } finally {
      try {
        pstmt.close();
      } catch (Exception ex2) {
      }
      try {
        ConnectionManager.releaseConnection(conn, context);
      } catch (Exception ex1) {
      }
    }
  }
  /** Business logic to execute. */
  public final Response executeCommand(
      Object inputPar,
      UserSessionParameters userSessionPars,
      HttpServletRequest request,
      HttpServletResponse response,
      HttpSession userSession,
      ServletContext context) {
    Connection conn = null;
    PreparedStatement pstmt = null;
    try {
      conn = ConnectionManager.getConnection(context);

      // fires the GenericEvent.CONNECTION_CREATED event...
      EventsManager.getInstance()
          .processEvent(
              new GenericEvent(
                  this,
                  getRequestName(),
                  GenericEvent.CONNECTION_CREATED,
                  (JAIOUserSessionParameters) userSessionPars,
                  request,
                  response,
                  userSession,
                  context,
                  conn,
                  inputPar,
                  null));

      Response responseVO =
          bean.insertItem(
              conn,
              (JournalHeaderVO) inputPar,
              userSessionPars,
              request,
              response,
              userSession,
              context);
      if (responseVO.isError()) {
        conn.rollback();
        return responseVO;
      }

      if (inputPar instanceof JournalHeaderWithVatVO) {
        JournalHeaderWithVatVO vo = (JournalHeaderWithVatVO) inputPar;

        // insert vat rows in the specified vat register...
        Response regRes =
            vatRegisterAction.insertVatRows(
                conn, vo.getVats(), userSessionPars, request, response, userSession, context);
        if (regRes.isError()) {
          conn.rollback();
          return regRes;
        }

        // retrieve payment instalments...
        Response payRes =
            payAction.executeCommand(
                new LookupValidationParams(vo.getPaymentCodeREG10(), new HashMap()),
                userSessionPars,
                request,
                response,
                userSession,
                context);
        if (payRes.isError()) {
          conn.rollback();
          return payRes;
        }
        PaymentVO payVO = (PaymentVO) ((VOListResponse) payRes).getRows().get(0);
        GridParams gridParams = new GridParams();
        gridParams
            .getOtherGridParams()
            .put(ApplicationConsts.PAYMENT_CODE_REG10, vo.getPaymentCodeREG10());
        payRes =
            paysAction.executeCommand(
                gridParams, userSessionPars, request, response, userSession, context);
        if (payRes.isError()) {
          conn.rollback();
          return payRes;
        }
        java.util.List rows = ((VOListResponse) payRes).getRows();

        // create expirations in DOC19 ONLY if:
        // - there are more than one instalment OR
        // - there is only one instalment and this instalment has more than 0 instalment days
        if (rows.size() > 1
            || (rows.size() == 1
                && ((PaymentInstalmentVO) rows.get(0)).getInstalmentDaysREG17().intValue() > 0)) {

          // retrieve internationalization settings (Resources object)...
          ServerResourcesFactory factory =
              (ServerResourcesFactory) context.getAttribute(Controller.RESOURCES_FACTORY);
          Resources resources = factory.getResources(userSessionPars.getLanguageId());

          PaymentInstalmentVO inVO = null;
          pstmt =
              conn.prepareStatement(
                  "insert into DOC19_EXPIRATIONS(COMPANY_CODE_SYS01,DOC_TYPE,DOC_YEAR,DOC_NUMBER,DOC_SEQUENCE,PROGRESSIVE,DOC_DATE,EXPIRATION_DATE,NAME_1,NAME_2,VALUE,PAYED,DESCRIPTION,CUSTOMER_SUPPLIER_CODE,PROGRESSIVE_REG04,CURRENCY_CODE_REG03) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
          long startTime = vo.getItemDateACC05().getTime(); // item date...
          if (payVO.getStartDayREG10().equals(ApplicationConsts.START_DAY_END_MONTH)) {
            Calendar cal = Calendar.getInstance();
            if (cal.get(cal.MONTH) == 10
                || cal.get(cal.MONTH) == 3
                || cal.get(cal.MONTH) == 5
                || cal.get(cal.MONTH) == 8) cal.set(cal.DAY_OF_MONTH, 30);
            else if (cal.get(cal.MONTH) == 1) {
              if (cal.get(cal.YEAR) % 4 == 0) cal.set(cal.DAY_OF_MONTH, 29);
              else cal.set(cal.DAY_OF_MONTH, 28);
            } else cal.set(cal.DAY_OF_MONTH, 31);
            startTime = cal.getTime().getTime();
          }
          BigDecimal amount = null;

          for (int i = 0; i < rows.size(); i++) {
            inVO = (PaymentInstalmentVO) rows.get(i);
            pstmt.setString(1, vo.getCompanyCodeSys01ACC05());
            pstmt.setString(2, vo.getDocTypeDOC19());
            pstmt.setBigDecimal(3, vo.getItemYearACC05());
            pstmt.setBigDecimal(4, null);
            pstmt.setBigDecimal(5, vo.getDocSequenceDOC19());
            pstmt.setBigDecimal(
                6,
                ProgressiveUtils.getConsecutiveProgressive(
                    "DOC19_EXPIRATIONS", "PROGRESSIVE", conn));
            pstmt.setDate(7, vo.getItemDateACC05());
            pstmt.setDate(
                8,
                new java.sql.Date(
                    startTime
                        + inVO.getInstalmentDaysREG17().longValue()
                            * 86400
                            * 1000)); // expiration date
            pstmt.setString(9, vo.getName_1REG04());
            pstmt.setString(10, vo.getName_2REG04());
            amount =
                vo.getTotalValue()
                    .multiply(inVO.getPercentageREG17())
                    .divide(new BigDecimal(100), BigDecimal.ROUND_HALF_UP)
                    .setScale(vo.getTotalValue().scale(), BigDecimal.ROUND_HALF_UP); // value

            pstmt.setBigDecimal(11, amount);
            pstmt.setString(12, "N");

            if (vo.getDocTypeDOC19().equals(ApplicationConsts.SALE_GENERIC_INVOICE))
              pstmt.setString(
                  13,
                  resources.getResource("sale generic document")
                      + " "
                      + vo.getDocSequenceDOC19()
                      + "/"
                      + vo.getItemYearACC05()
                      + " - "
                      + resources.getResource("valueREG01")
                      + " "
                      + resources.getResource("rateNumberREG17")
                      + " "
                      + (i + 1)
                      + " - "
                      + inVO.getPaymentTypeDescriptionSYS10()); // description
            else
              pstmt.setString(
                  13,
                  resources.getResource("purchase generic document")
                      + " "
                      + vo.getDocSequenceDOC19()
                      + "/"
                      + vo.getItemYearACC05()
                      + " - "
                      + resources.getResource("valueREG01")
                      + " "
                      + resources.getResource("rateNumberREG17")
                      + " "
                      + (i + 1)
                      + " - "
                      + inVO.getPaymentTypeDescriptionSYS10()); // description
            pstmt.setString(14, vo.getCustomerCodeSAL07());
            pstmt.setBigDecimal(15, vo.getProgressiveREG04());
            pstmt.setString(16, vo.getCurrencyCodeREG01());
            pstmt.execute();
          }
          pstmt.close();
        }

        // create an item registration for proceeds, according to expiration settings (e.g. retail
        // selling):
        // there must be only one instalment and this instalment has 0 instalment days
        if (rows.size() == 1
            && ((PaymentInstalmentVO) rows.get(0)).getInstalmentDaysREG17().intValue() == 0) {

          // retrieve internationalization settings (Resources object)...
          ServerResourcesFactory factory =
              (ServerResourcesFactory) context.getAttribute(Controller.RESOURCES_FACTORY);
          Resources resources = factory.getResources(userSessionPars.getLanguageId());

          HashMap map = new HashMap();
          map.put(ApplicationConsts.COMPANY_CODE_SYS01, vo.getCompanyCodeSys01ACC05());
          map.put(ApplicationConsts.PARAM_CODE, ApplicationConsts.CASE_ACCOUNT);
          Response res =
              userParamAction.executeCommand(
                  map, userSessionPars, request, response, userSession, context);
          if (res.isError()) {
            conn.rollback();
            return res;
          }
          String caseAccountCode = ((VOResponse) res).getVo().toString();

          JournalHeaderVO jhVO = new JournalHeaderVO();
          jhVO.setCompanyCodeSys01ACC05(vo.getCompanyCodeSys01ACC05());
          if (vo.getDocTypeDOC19().equals(ApplicationConsts.SALE_GENERIC_INVOICE)) {
            jhVO.setDescriptionACC05(
                resources.getResource("sale generic document")
                    + " "
                    + vo.getDocSequenceDOC19()
                    + "/"
                    + vo.getItemYearACC05()
                    + " - "
                    + resources.getResource("customer")
                    + " "
                    + vo.getName_1REG04()
                    + " "
                    + (vo.getName_2REG04() == null ? "" : vo.getName_2REG04()));
            jhVO.setAccountingMotiveCodeAcc03ACC05(ApplicationConsts.MOTIVE_INVOICE_PROCEEDS);
          } else {
            jhVO.setDescriptionACC05(
                resources.getResource("purchase generic document")
                    + " "
                    + vo.getDocSequenceDOC19()
                    + "/"
                    + vo.getItemYearACC05()
                    + " - "
                    + resources.getResource("supplier")
                    + " "
                    + vo.getName_1REG04()
                    + " "
                    + (vo.getName_2REG04() == null ? "" : vo.getName_2REG04()));
            jhVO.setAccountingMotiveCodeAcc03ACC05(ApplicationConsts.MOTIVE_PURCHASE_INVOICE_PAYED);
          }

          jhVO.setItemDateACC05(new java.sql.Date(System.currentTimeMillis()));
          jhVO.setItemYearACC05(new BigDecimal(Calendar.getInstance().get(Calendar.YEAR)));

          JournalRowVO jrVO = new JournalRowVO();
          jrVO.setCompanyCodeSys01ACC06(jhVO.getCompanyCodeSys01ACC05());
          if (vo.getDocTypeDOC19().equals(ApplicationConsts.SALE_GENERIC_INVOICE)) {
            jrVO.setAccountCodeAcc02ACC06(vo.getCreditAccountCodeAcc02SAL07());
            jrVO.setAccountCodeACC06(vo.getCustomerCodeSAL07());
            jrVO.setAccountCodeTypeACC06(ApplicationConsts.ACCOUNT_TYPE_CUSTOMER);
            jrVO.setCreditAmountACC06(vo.getTotalValue());
          } else {
            jrVO.setAccountCodeAcc02ACC06(vo.getDebitAccountCodeAcc02PUR01());
            jrVO.setAccountCodeACC06(vo.getSupplierCodePUR01());
            jrVO.setAccountCodeTypeACC06(ApplicationConsts.ACCOUNT_TYPE_SUPPLIER);
            jrVO.setDebitAmountACC06(vo.getTotalValue());
          }
          jrVO.setDescriptionACC06("");
          jrVO.setItemYearAcc05ACC06(jhVO.getItemYearACC05());
          jrVO.setProgressiveAcc05ACC06(jhVO.getProgressiveACC05());
          jhVO.addJournalRow(jrVO);

          jrVO = new JournalRowVO();
          jrVO.setCompanyCodeSys01ACC06(jhVO.getCompanyCodeSys01ACC05());
          jrVO.setAccountCodeAcc02ACC06(caseAccountCode);
          jrVO.setAccountCodeACC06(caseAccountCode);
          jrVO.setAccountCodeTypeACC06(ApplicationConsts.ACCOUNT_TYPE_ACCOUNT);
          if (vo.getDocTypeDOC19().equals(ApplicationConsts.SALE_GENERIC_INVOICE)) {
            jrVO.setDebitAmountACC06(vo.getTotalValue());
          } else {
            jrVO.setCreditAmountACC06(vo.getTotalValue());
          }
          jrVO.setDescriptionACC06("");
          jrVO.setItemYearAcc05ACC06(jhVO.getItemYearACC05());
          jrVO.setProgressiveAcc05ACC06(jhVO.getProgressiveACC05());
          jhVO.addJournalRow(jrVO);
          Response proceedsRes =
              bean.insertItem(conn, jhVO, userSessionPars, request, response, userSession, context);
          if (proceedsRes.isError()) {
            conn.rollback();
            return proceedsRes;
          }
        }
      }

      Response answer = responseVO;

      // fires the GenericEvent.BEFORE_COMMIT event...
      EventsManager.getInstance()
          .processEvent(
              new GenericEvent(
                  this,
                  getRequestName(),
                  GenericEvent.BEFORE_COMMIT,
                  (JAIOUserSessionParameters) userSessionPars,
                  request,
                  response,
                  userSession,
                  context,
                  conn,
                  inputPar,
                  answer));

      conn.commit();

      // fires the GenericEvent.AFTER_COMMIT event...
      EventsManager.getInstance()
          .processEvent(
              new GenericEvent(
                  this,
                  getRequestName(),
                  GenericEvent.AFTER_COMMIT,
                  (JAIOUserSessionParameters) userSessionPars,
                  request,
                  response,
                  userSession,
                  context,
                  conn,
                  inputPar,
                  answer));

      return answer;
    } catch (Throwable ex) {
      Logger.error(
          userSessionPars.getUsername(),
          this.getClass().getName(),
          "executeCommand",
          "Error while inserting a new item in the journal",
          ex);
      try {
        conn.rollback();
      } catch (Exception ex3) {
      }
      return new ErrorResponse(ex.getMessage());
    } finally {
      try {
        pstmt.close();
      } catch (Exception ex2) {
      }
      try {
        ConnectionManager.releaseConnection(conn, context);
      } catch (Exception ex1) {
      }
    }
  }
  /**
   * Insert a new accounting item in ACC05/ACC06 tables.
   *
   * @return JournalHeaderVO object with the progressive attribute filled
   */
  public VOResponse insertJournalItem(JournalHeaderVO vo, String serverLanguageId, String username)
      throws Throwable {
    PreparedStatement pstmt = null;
    Connection conn = null;
    try {
      if (this.conn == null) conn = getConn();
      else conn = this.conn;

      // generate progressive for journal item number...
      vo.setProgressiveACC05(
          CompanyProgressiveUtils.getConsecutiveProgressive(
              vo.getCompanyCodeSys01ACC05(),
              "ACC05_JOURNAL_HEADER_YEAR=" + vo.getItemYearACC05(),
              "PROGRESSIVE",
              conn));

      // insert into ACC05...
      Map attribute2dbField = new HashMap();
      attribute2dbField.put("companyCodeSys01ACC05", "COMPANY_CODE_SYS01");
      attribute2dbField.put("accountingMotiveCodeAcc03ACC05", "ACCOUNTING_MOTIVE_CODE_ACC03");
      attribute2dbField.put("progressiveACC05", "PROGRESSIVE");
      attribute2dbField.put("itemYearACC05", "ITEM_YEAR");
      attribute2dbField.put("itemDateACC05", "ITEM_DATE");
      attribute2dbField.put("descriptionACC05", "DESCRIPTION");
      Response res =
          org.jallinone.commons.server.QueryUtilExtension.insertTable(
              conn,
              new UserSessionParameters(username),
              vo,
              "ACC05_JOURNAL_HEADER",
              attribute2dbField,
              "Y",
              "N",
              null,
              true);
      if (res.isError()) throw new Exception(res.getErrorMessage());

      // insert into ACC06...
      attribute2dbField.clear();

      attribute2dbField.put("companyCodeSys01ACC06", "COMPANY_CODE_SYS01");
      attribute2dbField.put("progressiveAcc05ACC06", "PROGRESSIVE_ACC05");
      attribute2dbField.put("itemYearAcc05ACC06", "ITEM_YEAR_ACC05");
      attribute2dbField.put("progressiveACC06", "PROGRESSIVE");
      attribute2dbField.put("debitAmountACC06", "DEBIT_AMOUNT");
      attribute2dbField.put("creditAmountACC06", "CREDIT_AMOUNT");
      attribute2dbField.put("accountCodeTypeACC06", "ACCOUNT_CODE_TYPE");
      attribute2dbField.put("accountCodeACC06", "ACCOUNT_CODE");
      attribute2dbField.put("accountCodeAcc02ACC06", "ACCOUNT_CODE_ACC02");
      attribute2dbField.put("descriptionACC06", "DESCRIPTION");
      JournalRowVO rowVO = null;
      for (int i = 0; i < vo.getJournalRows().size(); i++) {
        rowVO = (JournalRowVO) vo.getJournalRows().get(i);
        rowVO.setProgressiveAcc05ACC06(vo.getProgressiveACC05());
        rowVO.setProgressiveACC06(
            CompanyProgressiveUtils.getConsecutiveProgressive(
                vo.getCompanyCodeSys01ACC05(), "ACC06_JOURNAL_ROWS", "PROGRESSIVE", conn));
        res =
            org.jallinone.commons.server.QueryUtilExtension.insertTable(
                conn,
                new UserSessionParameters(username),
                rowVO,
                "ACC06_JOURNAL_ROWS",
                attribute2dbField,
                "Y",
                "N",
                null,
                true);
        if (res.isError()) throw new Exception(res.getErrorMessage());
      }

      return new VOResponse(vo);
    } catch (Throwable ex) {
      Logger.error(
          username,
          this.getClass().getName(),
          "insertItem",
          "Error while inserting a new item in the journal",
          ex);
      try {
        if (this.conn == null && conn != null)
          // rollback only local connection
          conn.rollback();
      } catch (Exception ex3) {
      }
      throw new Exception(ex.getMessage());
    } finally {
      try {
        pstmt.close();
      } catch (Exception ex2) {
      }

      try {
        if (this.conn == null && conn != null) {
          // close only local connection
          conn.commit();
          conn.close();
        }

      } catch (Exception exx) {
      }
    }
  }
  /**
   * Retrieve a DefaultTreeModel object that contains the bill of materials (MaterialVO objects) of
   * the specified product.
   */
  public static VOResponse getBillOfMaterials(
      Connection conn,
      CurrenciesBean compCurr,
      ItemPK pk,
      String langId,
      String username,
      ArrayList customizedFields)
      throws Throwable {
    PreparedStatement pstmt = null;
    PreparedStatement pstmt2 = null;
    try {

      String sql1 =
          "select ITM03_COMPONENTS.COMPANY_CODE_SYS01,ITM03_COMPONENTS.ITEM_CODE_ITM01,ITM01_ITEMS.MIN_SELLING_QTY_UM_CODE_REG02,"
              + "ITM03_COMPONENTS.PARENT_ITEM_CODE_ITM01,ITM03_COMPONENTS.QTY,SYS10_COMPANY_TRANSLATIONS.DESCRIPTION "
              + "from ITM03_COMPONENTS,ITM01_ITEMS,SYS10_COMPANY_TRANSLATIONS "
              + "where "
              + "ITM03_COMPONENTS.COMPANY_CODE_SYS01 = ITM01_ITEMS.COMPANY_CODE_SYS01 and "
              + "ITM03_COMPONENTS.ITEM_CODE_ITM01 = ITM01_ITEMS.ITEM_CODE and "
              + "ITM01_ITEMS.COMPANY_CODE_SYS01 = SYS10_COMPANY_TRANSLATIONS.COMPANY_CODE_SYS01 and "
              + "ITM01_ITEMS.PROGRESSIVE_SYS10 = SYS10_COMPANY_TRANSLATIONS.PROGRESSIVE and "
              + "SYS10_COMPANY_TRANSLATIONS.LANGUAGE_CODE=? and ITM03_COMPONENTS.ENABLED='Y' and "
              + "ITM03_COMPONENTS.COMPANY_CODE_SYS01=? and "
              + "ITM03_COMPONENTS.PARENT_ITEM_CODE_ITM01 in (";
      String sql3 = ") order by ITM03_COMPONENTS.PARENT_ITEM_CODE_ITM01,ITM03_COMPONENTS.SEQUENCE";

      Map attribute2dbField = new HashMap();
      attribute2dbField.put("companyCodeSys01ITM03", "ITM03_COMPONENTS.COMPANY_CODE_SYS01");
      attribute2dbField.put("itemCodeItm01ITM03", "ITM03_COMPONENTS.ITEM_CODE_ITM01");
      attribute2dbField.put("parentItemCodeItm01ITM03", "ITM03_COMPONENTS.PARENT_ITEM_CODE_ITM01");
      attribute2dbField.put("descriptionSYS10", "SYS10_COMPANY_TRANSLATIONS.DESCRIPTION");
      attribute2dbField.put("qtyITM03", "ITM03_COMPONENTS.QTY");
      attribute2dbField.put(
          "minSellingQtyUmCodeReg02ITM01", "ITM01_ITEMS.MIN_SELLING_QTY_UM_CODE_REG02");

      ArrayList values = new ArrayList();
      values.add(langId);
      values.add(pk.getCompanyCodeSys01ITM01());

      // retrieve the whole tree...
      MaterialVO compVO = new MaterialVO();
      compVO.setCompanyCodeSys01ITM03(pk.getCompanyCodeSys01ITM01());
      compVO.setDescriptionSYS10("");
      compVO.setItemCodeItm01ITM03(pk.getItemCodeITM01());
      compVO.setQtyITM03(new BigDecimal(1));
      DefaultMutableTreeNode node = new OpenSwingTreeNode(compVO);
      DefaultTreeModel model = new DefaultTreeModel(node);

      String sql2 = "'" + pk.getItemCodeITM01() + "'";
      ArrayList nodes = new ArrayList();
      ArrayList nodes2 = new ArrayList(); // current nodes...
      Hashtable auxnodes = new Hashtable();
      nodes.add(node);

      // read from ITM03 table...
      Response res = null;
      java.util.List rows = null;
      java.util.List auxlist = null;
      DefaultMutableTreeNode auxnode, auxnode2 = null;
      String currentItemCode = null;

      do {
        res =
            QueryUtil.getQuery(
                conn,
                new UserSessionParameters(username),
                sql1 + sql2 + sql3,
                values,
                attribute2dbField,
                MaterialVO.class,
                "Y",
                "N",
                null,
                new GridParams(),
                true);
        if (!res.isError()) {
          rows = ((VOListResponse) res).getRows();
          auxnodes.clear();
          nodes2 = new ArrayList();
          sql2 = "";

          // group items per item code parent...
          for (int i = 0; i < rows.size(); i++) {
            compVO = (MaterialVO) rows.get(i);
            sql2 += "'" + compVO.getItemCodeItm01ITM03() + "',";
            auxnode = new OpenSwingTreeNode(compVO);
            auxlist = (ArrayList) auxnodes.get(compVO.getParentItemCodeItm01ITM03());
            if (auxlist == null) {
              auxlist = new ArrayList();
              auxnodes.put(compVO.getParentItemCodeItm01ITM03(), auxlist);
            }
            auxlist.add(auxnode);
            nodes2.add(auxnode);
          }

          if (sql2.length() > 0) sql2 = sql2.substring(0, sql2.length() - 1);

          // add items to the corresponding parent...
          for (int i = 0; i < nodes.size(); i++) {
            auxnode = (DefaultMutableTreeNode) nodes.get(i);
            compVO = (MaterialVO) auxnode.getUserObject();
            auxlist = (ArrayList) auxnodes.get(compVO.getItemCodeItm01ITM03());
            if (auxlist != null)
              for (int j = 0; j < auxlist.size(); j++) {
                auxnode.add((DefaultMutableTreeNode) auxlist.get(j));
                ((MaterialVO) ((DefaultMutableTreeNode) auxlist.get(j)).getUserObject())
                    .setQtyITM03(
                        ((MaterialVO) ((DefaultMutableTreeNode) auxlist.get(j)).getUserObject())
                            .getQtyITM03()
                            .multiply(((MaterialVO) auxnode.getUserObject()).getQtyITM03())
                            .setScale(5, BigDecimal.ROUND_HALF_UP));
              }
          }

          nodes = nodes2;
        }

      } while (!res.isError() && rows.size() > 0);
      if (res.isError()) throw new Exception(res.getErrorMessage());

      res =
          compCurr.loadCompanyCurrency(
              pk.getCompanyCodeSys01ITM01(), langId, username, customizedFields);
      if (res.isError()) throw new Exception(res.getErrorMessage());
      CurrencyVO currVO = (CurrencyVO) ((VOResponse) res).getVo();

      // navigate through the tree and fetch item prices...
      pstmt =
          conn.prepareStatement(
              "select sum(PUR04_SUPPLIER_PRICES.VALUE),count(PUR04_SUPPLIER_PRICES.VALUE),PUR03_SUPPLIER_PRICELISTS.CURRENCY_CODE_REG03 "
                  + "from PUR04_SUPPLIER_PRICES,PUR03_SUPPLIER_PRICELISTS "
                  + "where "
                  + "PUR04_SUPPLIER_PRICES.COMPANY_CODE_SYS01=PUR03_SUPPLIER_PRICELISTS.COMPANY_CODE_SYS01 and "
                  + "PUR04_SUPPLIER_PRICES.PROGRESSIVE_REG04=PUR03_SUPPLIER_PRICELISTS.PROGRESSIVE_REG04 and "
                  + "PUR04_SUPPLIER_PRICES.PRICELIST_CODE_PUR03=PUR03_SUPPLIER_PRICELISTS.PRICELIST_CODE and "
                  + "PUR04_SUPPLIER_PRICES.COMPANY_CODE_SYS01=? and "
                  + "PUR04_SUPPLIER_PRICES.ITEM_CODE_ITM01=? and "
                  + "PUR04_SUPPLIER_PRICES.START_DATE<=? and "
                  + "PUR04_SUPPLIER_PRICES.END_DATE>=? "
                  + "group by PUR03_SUPPLIER_PRICELISTS.CURRENCY_CODE_REG03");
      pstmt2 =
          conn.prepareStatement(
              "select sum(PRO02_MANUFACTURE_PHASES.VALUE) "
                  + "from PRO02_MANUFACTURE_PHASES,ITM01_ITEMS "
                  + "where "
                  + "PRO02_MANUFACTURE_PHASES.COMPANY_CODE_SYS01=ITM01_ITEMS.COMPANY_CODE_SYS01 and "
                  + "PRO02_MANUFACTURE_PHASES.MANUFACTURE_CODE_PRO01=ITM01_ITEMS.MANUFACTURE_CODE_PRO01 and "
                  + "ITM01_ITEMS.COMPANY_CODE_SYS01=? and "
                  + "ITM01_ITEMS.ITEM_CODE=?");
      res = expandNode(conn, currVO, pstmt, pstmt2, (DefaultMutableTreeNode) model.getRoot());
      if (res.isError()) throw new Exception(res.getErrorMessage());

      return new VOResponse(model);
    } catch (Exception ex1) {
      Logger.error(
          username, "", "getBillOfMaterials", "Error while fetching bill of materials", ex1);
      throw new Exception(ex1.getMessage());
    } finally {
      try {
        pstmt.close();
      } catch (Exception ex) {
      }
      try {
        pstmt2.close();
      } catch (Exception ex) {
      }
    }
  }
  /**
   * Method called by the Form panel to insert new data.
   *
   * @param newValueObject value object to save
   * @return an ErrorResponse value object in case of errors, VOResponse if the operation is
   *     successfully completed
   */
  public Response insertRecord(ValueObject newPersistentObject) throws Exception {
    // mapping between attributes and database fields...
    Map attribute2dbField = new HashMap();
    attribute2dbField.put("empCode", "EMP_CODE");
    attribute2dbField.put("firstName", "FIRST_NAME");
    attribute2dbField.put("lastName", "LAST_NAME");
    attribute2dbField.put("deptCode", "DEPT_CODE");
    attribute2dbField.put("taskCode", "TASK_CODE");
    attribute2dbField.put("sex", "SEX");
    attribute2dbField.put("hireDate", "HIRE_DATE");
    attribute2dbField.put("salary", "SALARY");
    attribute2dbField.put("note", "NOTE");

    Response res =
        QueryUtil.insertTable(conn, newPersistentObject, "EMP", attribute2dbField, "Y", "N", true);
    if (res.isError()) {
      conn.rollback();
      return res;
    } else {
      // insert 7 records in WORKING_DAYS, one for each day of week...
      PreparedStatement pstmt = null;
      try {
        pstmt = conn.prepareStatement("insert into WORKING_DAYS(EMP_CODE,DAY) values(?,?)");

        pstmt.setString(1, ((EmpVO) newPersistentObject).getEmpCode());
        pstmt.setInt(2, Calendar.SUNDAY);
        pstmt.execute();

        pstmt.setString(1, ((EmpVO) newPersistentObject).getEmpCode());
        pstmt.setInt(2, Calendar.MONDAY);
        pstmt.execute();

        pstmt.setString(1, ((EmpVO) newPersistentObject).getEmpCode());
        pstmt.setInt(2, Calendar.TUESDAY);
        pstmt.execute();

        pstmt.setString(1, ((EmpVO) newPersistentObject).getEmpCode());
        pstmt.setInt(2, Calendar.WEDNESDAY);
        pstmt.execute();

        pstmt.setString(1, ((EmpVO) newPersistentObject).getEmpCode());
        pstmt.setInt(2, Calendar.THURSDAY);
        pstmt.execute();

        pstmt.setString(1, ((EmpVO) newPersistentObject).getEmpCode());
        pstmt.setInt(2, Calendar.FRIDAY);
        pstmt.execute();

        pstmt.setString(1, ((EmpVO) newPersistentObject).getEmpCode());
        pstmt.setInt(2, Calendar.SATURDAY);
        pstmt.execute();

        conn.commit();

        frame
            .getGrid()
            .getOtherGridParams()
            .put("empCode", ((EmpVO) newPersistentObject).getEmpCode());
        frame.getGrid().reloadData();

        return res;
      } catch (Exception ex) {
        conn.rollback();
        return new ErrorResponse(ex.getMessage());
      } finally {
        try {
          pstmt.close();
        } catch (Exception ex1) {
        }
      }
    }

    /*
        // an alternative way: you can define your own business logic to store data at hand...
        PreparedStatement stmt = null;
        try {
          stmt = conn.prepareStatement("insert into EMP(EMP_CODE,FIRST_NAME,LAST_NAME,DEPT_CODE,TASK_CODE,SEX,HIRE_DATE,SALARY,NOTE) values(?,?,?,?,?,?,?,?,?)");
          EmpVO vo = (EmpVO)newPersistentObject;
          stmt.setString(1,vo.getEmpCode());
          stmt.setString(2,vo.getFirstName());
          stmt.setString(3,vo.getLastName());
          stmt.setString(4,vo.getDeptCode());
          stmt.setString(5,vo.getTaskCode());
          stmt.setString(6,vo.getSex());
          stmt.setDate(7,vo.getHireDate());
          stmt.setBigDecimal(8,vo.getSalary());
          stmt.setString(9,vo.getNote());

          stmt.execute();
          pk = vo.getEmpCode();
          gridFrame.reloadData();
          return new VOResponse(vo);
        }
        catch (SQLException ex) {
          ex.printStackTrace();
          return new ErrorResponse(ex.getMessage());
        }
        finally {
          try {
            stmt.close();
            conn.commit();
          }
          catch (SQLException ex1) {
          }
        }
    */
  }
  /** Business logic to execute. */
  public VOListResponse loadSupplierPriceItems(
      GridParams pars, String serverLanguageId, String username, ArrayList customizedFields)
      throws Throwable {
    PreparedStatement pstmt = null;
    Connection conn = null;
    try {
      if (this.conn == null) conn = getConn();
      else conn = this.conn;

      BigDecimal rootProgressiveHIE01 =
          (BigDecimal) pars.getOtherGridParams().get(ApplicationConsts.ROOT_PROGRESSIVE_HIE01);
      BigDecimal progressiveHIE01 =
          (BigDecimal) pars.getOtherGridParams().get(ApplicationConsts.PROGRESSIVE_HIE01);
      BigDecimal progressiveHIE02 =
          (BigDecimal) pars.getOtherGridParams().get(ApplicationConsts.PROGRESSIVE_HIE02);
      BigDecimal progressiveREG04 =
          (BigDecimal) pars.getOtherGridParams().get(ApplicationConsts.PROGRESSIVE_REG04);
      String companyCodeSYS01 =
          (String) pars.getOtherGridParams().get(ApplicationConsts.COMPANY_CODE_SYS01);
      String pricelistCodePUR03 =
          (String) pars.getOtherGridParams().get(ApplicationConsts.PRICELIST);

      CompanyHierarchyLevelVO vo =
          (CompanyHierarchyLevelVO) pars.getOtherGridParams().get(ApplicationConsts.TREE_FILTER);
      if (vo != null) {
        progressiveHIE01 = vo.getProgressiveHIE01();
        progressiveHIE02 = vo.getProgressiveHie02HIE01();
      }

      String sql =
          "select PUR02_SUPPLIER_ITEMS.COMPANY_CODE_SYS01,PUR02_SUPPLIER_ITEMS.ITEM_CODE_ITM01,PUR02_SUPPLIER_ITEMS.SUPPLIER_ITEM_CODE,PUR02_SUPPLIER_ITEMS.PROGRESSIVE_REG04,"
              + "PUR02_SUPPLIER_ITEMS.PROGRESSIVE_HIE02,PUR02_SUPPLIER_ITEMS.PROGRESSIVE_HIE01,PUR02_SUPPLIER_ITEMS.MIN_PURCHASE_QTY,PUR02_SUPPLIER_ITEMS.MULTIPLE_QTY,"
              + "PUR02_SUPPLIER_ITEMS.UM_CODE_REG02,PUR02_SUPPLIER_ITEMS.ENABLED,SYS10_COMPANY_TRANSLATIONS.DESCRIPTION,REG02_MEASURE_UNITS.DECIMALS,"
              + "ITM01_ITEMS.VAT_CODE_REG01,SYS10_VAT.DESCRIPTION,REG01_VATS.DEDUCTIBLE,REG01_VATS.VALUE,"
              + "PUR04_SUPPLIER_PRICES.VALUE,PUR04_SUPPLIER_PRICES.START_DATE,PUR04_SUPPLIER_PRICES.END_DATE,"
              + "ITM01_ITEMS.USE_VARIANT_1,ITM01_ITEMS.USE_VARIANT_2,ITM01_ITEMS.USE_VARIANT_3,ITM01_ITEMS.USE_VARIANT_4,ITM01_ITEMS.USE_VARIANT_5, "
              + "ITM01_ITEMS.NO_WAREHOUSE_MOV "
              + " from PUR02_SUPPLIER_ITEMS,SYS10_COMPANY_TRANSLATIONS,ITM01_ITEMS,REG02_MEASURE_UNITS,SYS10_TRANSLATIONS SYS10_VAT,REG01_VATS,PUR04_SUPPLIER_PRICES where "
              + "PUR02_SUPPLIER_ITEMS.PROGRESSIVE_HIE02=? and "
              + "PUR02_SUPPLIER_ITEMS.UM_CODE_REG02=REG02_MEASURE_UNITS.UM_CODE and "
              + "PUR02_SUPPLIER_ITEMS.COMPANY_CODE_SYS01=ITM01_ITEMS.COMPANY_CODE_SYS01 and "
              + "PUR02_SUPPLIER_ITEMS.ITEM_CODE_ITM01=ITM01_ITEMS.ITEM_CODE and "
              + "ITM01_ITEMS.COMPANY_CODE_SYS01=SYS10_COMPANY_TRANSLATIONS.COMPANY_CODE_SYS01 and "
              + "ITM01_ITEMS.PROGRESSIVE_SYS10=SYS10_COMPANY_TRANSLATIONS.PROGRESSIVE and "
              + "SYS10_COMPANY_TRANSLATIONS.LANGUAGE_CODE=? and "
              + "PUR02_SUPPLIER_ITEMS.COMPANY_CODE_SYS01 = ? and "
              + "PUR02_SUPPLIER_ITEMS.PROGRESSIVE_REG04=? and "
              + "PUR02_SUPPLIER_ITEMS.ENABLED='Y' and "
              + "ITM01_ITEMS.VAT_CODE_REG01=REG01_VATS.VAT_CODE and "
              + "REG01_VATS.PROGRESSIVE_SYS10=SYS10_VAT.PROGRESSIVE and "
              + "SYS10_VAT.LANGUAGE_CODE=? and "
              + "PUR02_SUPPLIER_ITEMS.COMPANY_CODE_SYS01=PUR04_SUPPLIER_PRICES.COMPANY_CODE_SYS01 and "
              + "PUR02_SUPPLIER_ITEMS.PROGRESSIVE_REG04=PUR04_SUPPLIER_PRICES.PROGRESSIVE_REG04 and "
              + "PUR02_SUPPLIER_ITEMS.ITEM_CODE_ITM01=PUR04_SUPPLIER_PRICES.ITEM_CODE_ITM01 and "
              + "PUR04_SUPPLIER_PRICES.PRICELIST_CODE_PUR03=? and "
              + "PUR04_SUPPLIER_PRICES.START_DATE<=? and "
              + "PUR04_SUPPLIER_PRICES.END_DATE>? ";

      if (rootProgressiveHIE01 == null || !rootProgressiveHIE01.equals(progressiveHIE01)) {
        // retrieve all subnodes of the specified node...
        pstmt =
            conn.prepareStatement(
                "select HIE01_COMPANY_LEVELS.PROGRESSIVE,HIE01_COMPANY_LEVELS.PROGRESSIVE_HIE01,HIE01_COMPANY_LEVELS.LEV from HIE01_COMPANY_LEVELS "
                    + "where COMPANY_CODE_SYS01='"
                    + companyCodeSYS01
                    + "' and ENABLED='Y' and PROGRESSIVE_HIE02=? and PROGRESSIVE>=? "
                    + "order by LEV,PROGRESSIVE_HIE01,PROGRESSIVE");
        pstmt.setBigDecimal(1, progressiveHIE02);
        pstmt.setBigDecimal(2, progressiveHIE01);
        ResultSet rset = pstmt.executeQuery();

        HashSet currentLevelNodes = new HashSet();
        HashSet newLevelNodes = new HashSet();
        String nodes = "";
        int currentLevel = -1;
        while (rset.next()) {
          if (currentLevel != rset.getInt(3)) {
            // next level...
            currentLevel = rset.getInt(3);
            currentLevelNodes = newLevelNodes;
            newLevelNodes = new HashSet();
          }
          if (rset.getBigDecimal(1).equals(progressiveHIE01)) {
            newLevelNodes.add(rset.getBigDecimal(1));
            nodes += rset.getBigDecimal(1) + ",";
          } else if (currentLevelNodes.contains(rset.getBigDecimal(2))) {
            newLevelNodes.add(rset.getBigDecimal(1));
            nodes += rset.getBigDecimal(1) + ",";
          }
        }
        rset.close();
        pstmt.close();
        if (nodes.length() > 0) nodes = nodes.substring(0, nodes.length() - 1);
        if (rootProgressiveHIE01 != null || nodes.length() > 0)
          sql += " and PUR02_SUPPLIER_ITEMS.PROGRESSIVE_HIE01 in (" + nodes + ")";
      }

      Map attribute2dbField = new HashMap();
      attribute2dbField.put("companyCodeSys01PUR02", "PUR02_SUPPLIER_ITEMS.COMPANY_CODE_SYS01");
      attribute2dbField.put("itemCodeItm01PUR02", "PUR02_SUPPLIER_ITEMS.ITEM_CODE_ITM01");
      attribute2dbField.put("supplierItemCodePUR02", "PUR02_SUPPLIER_ITEMS.SUPPLIER_ITEM_CODE");
      attribute2dbField.put("progressiveReg04PUR02", "PUR02_SUPPLIER_ITEMS.PROGRESSIVE_REG04");
      attribute2dbField.put("progressiveHie02PUR02", "PUR02_SUPPLIER_ITEMS.PROGRESSIVE_HIE02");
      attribute2dbField.put("progressiveHie01PUR02", "PUR02_SUPPLIER_ITEMS.PROGRESSIVE_HIE01");
      attribute2dbField.put("minPurchaseQtyPUR02", "PUR02_SUPPLIER_ITEMS.MIN_PURCHASE_QTY");
      attribute2dbField.put("multipleQtyPUR02", "PUR02_SUPPLIER_ITEMS.MULTIPLE_QTY");
      attribute2dbField.put("umCodeReg02PUR02", "PUR02_SUPPLIER_ITEMS.UM_CODE_REG02");
      attribute2dbField.put("enabledPUR02", "PUR02_SUPPLIER_ITEMS.ENABLED");
      attribute2dbField.put("descriptionSYS10", "SYS10_COMPANY_TRANSLATIONS.DESCRIPTION");
      attribute2dbField.put("decimalsREG02", "REG02_MEASURE_UNITS.DECIMALS");

      attribute2dbField.put("vatCodeReg01ITM01", "ITM01_ITEMS.VAT_CODE_REG01");
      attribute2dbField.put("vatDescriptionSYS10", "SYS10_VAT.DESCRIPTION");
      attribute2dbField.put("deductibleREG01", "REG01_VATS.DEDUCTIBLE");
      attribute2dbField.put("valueREG01", "REG01_VATS.VALUE");
      attribute2dbField.put("valuePUR04", "PUR04_SUPPLIER_PRICES.VALUE");
      attribute2dbField.put("startDatePUR04", "PUR04_SUPPLIER_PRICES.START_DATE");
      attribute2dbField.put("endDatePUR04", "PUR04_SUPPLIER_PRICES.END_DATE");

      attribute2dbField.put("useVariant1ITM01", "ITM01_ITEMS.USE_VARIANT_1");
      attribute2dbField.put("useVariant2ITM01", "ITM01_ITEMS.USE_VARIANT_2");
      attribute2dbField.put("useVariant3ITM01", "ITM01_ITEMS.USE_VARIANT_3");
      attribute2dbField.put("useVariant4ITM01", "ITM01_ITEMS.USE_VARIANT_4");
      attribute2dbField.put("useVariant5ITM01", "ITM01_ITEMS.USE_VARIANT_5");

      attribute2dbField.put("noWarehouseMovITM01", "ITM01_ITEMS.NO_WAREHOUSE_MOV");

      ArrayList values = new ArrayList();
      values.add(progressiveHIE02);
      values.add(serverLanguageId);
      values.add(companyCodeSYS01);
      values.add(progressiveREG04);
      values.add(serverLanguageId);
      values.add(pricelistCodePUR03);
      values.add(new java.sql.Date(System.currentTimeMillis()));
      values.add(new java.sql.Date(System.currentTimeMillis()));

      // read from PUR02 table...
      Response answer =
          CustomizeQueryUtil.getQuery(
              conn,
              new UserSessionParameters(username),
              sql,
              values,
              attribute2dbField,
              SupplierPriceItemVO.class,
              "Y",
              "N",
              null,
              pars,
              50,
              true,
              customizedFields);
      if (answer.isError()) throw new Exception(answer.getErrorMessage());
      else return (VOListResponse) answer;

    } catch (Throwable ex) {
      Logger.error(
          username,
          this.getClass().getName(),
          "executeCommand",
          "Error while fetching supplier items list",
          ex);
      throw new Exception(ex.getMessage());
    } finally {
      try {
        pstmt.close();
      } catch (Exception exx) {
      }
      try {
        if (this.conn == null && conn != null) {
          // close only local connection
          conn.commit();
          conn.close();
        }

      } catch (Exception exx) {
      }
    }
  }
  /** Load data from server and construct panel content. */
  public final void reloadData(DocumentsFrame frame) {
    try {
      // remove all panel content...
      clearData();
      frame.getGrid().getOtherGridParams().put(ApplicationConsts.PROPERTIES_FILTER, filters);

      // retrieve customized input controls list...
      GridParams gridParams = new GridParams();
      DefaultMutableTreeNode node = frame.getHierarTreePanel().getSelectedNode();
      if (node != null) {
        HierarchyLevelVO vo = (HierarchyLevelVO) node.getUserObject();
        HierarchyLevelVO root =
            (HierarchyLevelVO) ((DefaultMutableTreeNode) node.getRoot()).getUserObject();
        gridParams
            .getOtherGridParams()
            .put(ApplicationConsts.PROGRESSIVE_HIE01, vo.getProgressiveHIE01());
        gridParams
            .getOtherGridParams()
            .put(ApplicationConsts.ROOT_PROGRESSIVE_HIE01, root.getProgressiveHIE01());
      } else gridParams.getOtherGridParams().remove(ApplicationConsts.PROGRESSIVE_HIE01);
      gridParams
          .getOtherGridParams()
          .put(
              ApplicationConsts.PROGRESSIVE_HIE02,
              frame.getHierarTreePanel().getProgressiveHIE02());
      gridParams.getOtherGridParams().put(ApplicationConsts.LOAD_ANCIENTS, Boolean.TRUE);
      Response res = ClientUtils.getData("loadLevelProperties", gridParams);
      if (res.isError()) {
        JOptionPane.showMessageDialog(
            ClientUtils.getParentFrame(this),
            ClientSettings.getInstance().getResources().getResource("Error while loading data")
                + ":\n"
                + res.getErrorMessage(),
            ClientSettings.getInstance().getResources().getResource("Loading Data Error"),
            JOptionPane.ERROR_MESSAGE);
        return;
      }
      ArrayList rows = ((VOListResponse) res).getRows();
      if (rows.size() > 0) {
        // adding customized input controls...
        LevelPropertyVO inputControlInfo = null;
        int row = 0;
        int col = 0;
        LabelControl labelControl = null;
        TextControl textControl = null;
        DateControl dateControl = null;
        NumericControl numericControl = null;
        for (int i = 0; i < rows.size(); i++) {
          inputControlInfo = (LevelPropertyVO) rows.get(i);
          labelControl = new LabelControl();
          labelControl.setText(inputControlInfo.getDescriptionSYS10());
          innerPanel.add(
              labelControl,
              new GridBagConstraints(
                  col++,
                  row,
                  1,
                  1,
                  0.0,
                  0.0,
                  GridBagConstraints.WEST,
                  GridBagConstraints.NONE,
                  new Insets(5, 5, 5, 5),
                  0,
                  0));

          if (inputControlInfo.getPropertyTypeDOC21().equals(ApplicationConsts.TYPE_TEXT)) {
            textControl = new TextControl();
            textControl.setMaxCharacters(255);
            textControl.setLinkLabel(labelControl);
            innerPanel.add(
                textControl,
                new GridBagConstraints(
                    col,
                    row,
                    1,
                    1,
                    1.0,
                    0.0,
                    GridBagConstraints.WEST,
                    GridBagConstraints.HORIZONTAL,
                    new Insets(5, 5, 5, 15),
                    0,
                    0));
            textControl.addFocusListener(new ControlFocusListener(inputControlInfo, textControl));
          } else if (inputControlInfo.getPropertyTypeDOC21().equals(ApplicationConsts.TYPE_DATE)) {
            dateControl = new DateControl();
            dateControl.setLinkLabel(labelControl);
            innerPanel.add(
                dateControl,
                new GridBagConstraints(
                    col,
                    row,
                    1,
                    1,
                    1.0,
                    0.0,
                    GridBagConstraints.WEST,
                    GridBagConstraints.NONE,
                    new Insets(5, 5, 5, 15),
                    0,
                    0));
            dateControl.addFocusListener(new ControlFocusListener(inputControlInfo, dateControl));
          } else if (inputControlInfo.getPropertyTypeDOC21().equals(ApplicationConsts.TYPE_NUM)) {
            numericControl = new NumericControl();
            numericControl.setDecimals(5);
            numericControl.setLinkLabel(labelControl);
            innerPanel.add(
                numericControl,
                new GridBagConstraints(
                    col,
                    row,
                    1,
                    1,
                    1.0,
                    0.0,
                    GridBagConstraints.WEST,
                    GridBagConstraints.HORIZONTAL,
                    new Insets(5, 5, 5, 15),
                    0,
                    0));
            numericControl.addFocusListener(
                new ControlFocusListener(inputControlInfo, numericControl));
          }

          col++;
          if (col >= 2) {
            innerPanel.add(
                new JPanel(),
                new GridBagConstraints(
                    col,
                    row,
                    1,
                    1,
                    1.0,
                    0.0,
                    GridBagConstraints.WEST,
                    GridBagConstraints.HORIZONTAL,
                    new Insets(5, 5, 5, 5),
                    0,
                    0));
            row++;
            col = 0;
          }
        }
        row++;
        innerPanel.add(
            new JPanel(),
            new GridBagConstraints(
                col,
                row,
                1,
                1,
                0.0,
                0.0,
                GridBagConstraints.WEST,
                GridBagConstraints.NONE,
                new Insets(5, 5, 5, 5),
                0,
                0));

        innerPanel.add(
            new JPanel(),
            new GridBagConstraints(
                0,
                row,
                1,
                1,
                0.0,
                1.0,
                GridBagConstraints.WEST,
                GridBagConstraints.VERTICAL,
                new Insets(5, 5, 5, 5),
                0,
                0));
        this.revalidate();
        this.repaint();
      }
    } catch (Exception ex) {
      ex.printStackTrace();
    }
  }
  public VOResponse payImmediately(
      String companyCode,
      String docType,
      BigDecimal docYear,
      BigDecimal docNumber,
      BigDecimal docSequence,
      String t1,
      String t2,
      String serverLanguageId,
      String username)
      throws Throwable {
    PreparedStatement pstmt = null;
    ResultSet rset = null;
    Connection conn = null;
    try {
      if (this.conn == null) conn = getConn();
      else conn = this.conn;
      insJornalItemAction.setConn(conn); // use same transaction...
      userParamAction.setConn(conn); // use same transaction...

      pstmt =
          conn.prepareStatement(
              "UPDATE DOC19_EXPIRATIONS SET PAYED_VALUE=VALUE,PAYED_DATE=EXPIRATION_DATE,PAYED='Y',REAL_PAYMENT_TYPE_CODE_REG11=PAYMENT_TYPE_CODE_REG11 WHERE "
                  + "COMPANY_CODE_SYS01=? AND DOC_TYPE=? AND DOC_YEAR=? AND DOC_NUMBER=? ");
      pstmt.setString(1, companyCode);
      pstmt.setString(2, docType);
      pstmt.setBigDecimal(3, docYear);
      pstmt.setBigDecimal(4, docNumber);
      int rows = pstmt.executeUpdate();
      if (rows != 1) throw new Exception("invalid number of payments");
      pstmt.close();

      pstmt =
          conn.prepareStatement(
              "SELECT DESCRIPTION,NAME_1,NAME_2,PROGRESSIVE_REG04,VALUE,CUSTOMER_SUPPLIER_CODE "
                  + "FROM DOC19_EXPIRATIONS WHERE "
                  + "COMPANY_CODE_SYS01=? AND DOC_TYPE=? AND DOC_YEAR=? AND DOC_NUMBER=?");
      pstmt.setString(1, companyCode);
      pstmt.setString(2, docType);
      pstmt.setBigDecimal(3, docYear);
      pstmt.setBigDecimal(4, docNumber);
      rset = pstmt.executeQuery();
      rset.next();
      String descriptionDOC19 = rset.getString(1);
      String name_1DOC19 = rset.getString(2);
      String name_2DOC19 = rset.getString(3);
      BigDecimal progressiveReg04DOC19 = rset.getBigDecimal(4);
      BigDecimal valueDOC19 = rset.getBigDecimal(5);
      String customerSupplierCodeDOC19 = rset.getString(6);
      rset.close();
      pstmt.close();

      HashMap map = new HashMap();
      map.put(ApplicationConsts.COMPANY_CODE_SYS01, companyCode);
      map.put(ApplicationConsts.PARAM_CODE, ApplicationConsts.BANK_ACCOUNT);
      Response res = userParamAction.loadUserParam(map, serverLanguageId, username);
      if (res.isError()) {
        throw new Exception(res.getErrorMessage());
      }
      String bankAccountCode = ((VOResponse) res).getVo().toString();

      // generate an accounting item, since the row has been payed...
      JournalHeaderVO jhVO = new JournalHeaderVO();
      jhVO.setCompanyCodeSys01ACC05(companyCode);
      String creditDebitAccountCode = null;
      String accountCodeTypeACC06 = null;
      if (docType.equals(ApplicationConsts.SALE_DESK_DOC_TYPE)
          || docType.equals(ApplicationConsts.SALE_INVOICE_DOC_TYPE)
          || docType.equals(ApplicationConsts.SALE_INVOICE_FROM_DN_DOC_TYPE)
          || docType.equals(ApplicationConsts.SALE_INVOICE_FROM_SD_DOC_TYPE)
          || docType.equals(ApplicationConsts.SALE_CREDIT_NOTE_DOC_TYPE)
          || docType.equals(ApplicationConsts.SALE_GENERIC_INVOICE)) {
        jhVO.setAccountingMotiveCodeAcc03ACC05(ApplicationConsts.MOTIVE_INVOICE_PROCEEDS);
        jhVO.setDescriptionACC05(
            descriptionDOC19
                + " - "
                + t1
                + " "
                + name_1DOC19
                + " "
                + (name_2DOC19 == null ? "" : name_2DOC19));

        // determine account codes defined for the current customer...
        pstmt =
            conn.prepareStatement(
                "select CREDIT_ACCOUNT_CODE_ACC02 from SAL07_CUSTOMERS where COMPANY_CODE_SYS01=? and PROGRESSIVE_REG04=?");
        pstmt.setString(1, companyCode);
        pstmt.setBigDecimal(2, progressiveReg04DOC19);
        rset = pstmt.executeQuery();
        if (!rset.next()) {
          rset.close();
          throw new Exception("customer not found");
        }
        creditDebitAccountCode = rset.getString(1);
        rset.close();
        pstmt.close();

        accountCodeTypeACC06 = ApplicationConsts.ACCOUNT_TYPE_CUSTOMER;
      } else {
        jhVO.setAccountingMotiveCodeAcc03ACC05(ApplicationConsts.MOTIVE_PURCHASE_INVOICE_PAYED);
        jhVO.setDescriptionACC05(
            descriptionDOC19
                + " - "
                + t2
                + " "
                + name_1DOC19
                + " "
                + (name_2DOC19 == null ? "" : name_2DOC19));

        // determine account codes defined for the current supplier...
        pstmt =
            conn.prepareStatement(
                "select DEBIT_ACCOUNT_CODE_ACC02 from PUR01_SUPPLIERS where COMPANY_CODE_SYS01=? and PROGRESSIVE_REG04=?");
        pstmt.setString(1, companyCode);
        pstmt.setBigDecimal(2, progressiveReg04DOC19);
        rset = pstmt.executeQuery();
        if (!rset.next()) {
          rset.close();
          throw new Exception("supplier not found");
        }
        creditDebitAccountCode = rset.getString(1);
        rset.close();
        pstmt.close();

        accountCodeTypeACC06 = ApplicationConsts.ACCOUNT_TYPE_SUPPLIER;
      }
      jhVO.setItemDateACC05(new java.sql.Date(System.currentTimeMillis()));
      jhVO.setItemYearACC05(new BigDecimal(Calendar.getInstance().get(Calendar.YEAR)));

      JournalRowVO jrVO = new JournalRowVO();
      jrVO.setCompanyCodeSys01ACC06(jhVO.getCompanyCodeSys01ACC05());
      jrVO.setAccountCodeAcc02ACC06(creditDebitAccountCode);
      jrVO.setAccountCodeACC06(customerSupplierCodeDOC19);
      jrVO.setAccountCodeTypeACC06(accountCodeTypeACC06);
      jrVO.setCreditAmountACC06(valueDOC19);
      jrVO.setDescriptionACC06("");
      jrVO.setItemYearAcc05ACC06(jhVO.getItemYearACC05());
      jrVO.setProgressiveAcc05ACC06(jhVO.getProgressiveACC05());
      jhVO.addJournalRow(jrVO);

      jrVO = new JournalRowVO();
      jrVO.setCompanyCodeSys01ACC06(jhVO.getCompanyCodeSys01ACC05());
      jrVO.setAccountCodeAcc02ACC06(bankAccountCode);
      jrVO.setAccountCodeACC06(bankAccountCode);
      jrVO.setAccountCodeTypeACC06(ApplicationConsts.ACCOUNT_TYPE_ACCOUNT);
      jrVO.setDebitAmountACC06(valueDOC19);
      jrVO.setDescriptionACC06("");
      jrVO.setItemYearAcc05ACC06(jhVO.getItemYearACC05());
      jrVO.setProgressiveAcc05ACC06(jhVO.getProgressiveACC05());
      jhVO.addJournalRow(jrVO);

      res = insJornalItemAction.insertJournalItem(jhVO, serverLanguageId, username);
      if (res.isError()) {
        throw new Exception(res.getErrorMessage());
      }

      return new VOResponse(Boolean.TRUE);
    } catch (Throwable ex) {
      Logger.error(
          username,
          this.getClass().getName(),
          "executeCommand",
          "Error while updating existing expiration",
          ex);
      try {
        if (this.conn == null && conn != null)
          // rollback only local connection
          conn.rollback();
      } catch (Exception ex3) {
      }
      throw new Exception(ex.getMessage());
    } finally {
      try {
        if (rset != null) rset.close();
      } catch (Exception ex1) {
      }
      try {
        if (pstmt != null) pstmt.close();
      } catch (Exception ex1) {
      }

      try {
        insJornalItemAction.setConn(null);
        userParamAction.setConn(null);
      } catch (Exception ex) {
      }
      try {
        if (this.conn == null && conn != null) {
          // close only local connection
          conn.commit();
          conn.close();
        }

      } catch (Exception exx) {
      }
    }
  }
  /** Business logic to execute. */
  public VOListResponse insertCharges(
      ArrayList list, String serverLanguageId, String username, ArrayList customizedFields)
      throws Throwable {
    Connection conn = null;
    try {
      if (this.conn == null) conn = getConn();
      else conn = this.conn;
      ChargeVO vo = null;

      Map attribute2dbField = new HashMap();
      attribute2dbField.put("companyCodeSys01SAL06", "COMPANY_CODE_SYS01");
      attribute2dbField.put("chargeCodeSAL06", "CHARGE_CODE");
      attribute2dbField.put("chargeCodeSAL06", "CHARGE_CODE");
      attribute2dbField.put("progressiveSys10SAL06", "PROGRESSIVE_SYS10");
      attribute2dbField.put("valueSAL06", "VALUE");
      attribute2dbField.put("percSAL06", "PERC");
      attribute2dbField.put("vatCodeReg01SAL06", "VAT_CODE_REG01");
      attribute2dbField.put("currencyCodeReg03SAL06", "CURRENCY_CODE_REG03");
      attribute2dbField.put("enabledSAL06", "ENABLED");

      BigDecimal progressiveSYS10 = null;
      Response res = null;
      for (int i = 0; i < list.size(); i++) {
        vo = (ChargeVO) list.get(i);
        vo.setEnabledSAL06("Y");

        // insert record in SYS10...
        progressiveSYS10 =
            TranslationUtils.insertTranslations(
                vo.getDescriptionSYS10(), vo.getCompanyCodeSys01SAL06(), conn);
        vo.setProgressiveSys10SAL06(progressiveSYS10);

        // insert into SAL06...
        res =
            CustomizeQueryUtil.insertTable(
                conn,
                new UserSessionParameters(username),
                vo,
                "SAL06_CHARGES",
                attribute2dbField,
                "Y",
                "N",
                null,
                true,
                customizedFields);
        if (res.isError()) {
          throw new Exception(res.getErrorMessage());
        }
      }

      return new VOListResponse(list, false, list.size());
    } catch (Throwable ex) {
      Logger.error(
          username,
          this.getClass().getName(),
          "executeCommand",
          "Error while inserting new charges",
          ex);
      try {
        if (this.conn == null && conn != null)
          // rollback only local connection
          conn.rollback();
      } catch (Exception ex3) {
      }

      throw new Exception(ex.getMessage());
    } finally {
      try {
        if (this.conn == null && conn != null) {
          // close only local connection
          conn.commit();
          conn.close();
        }

      } catch (Exception exx) {
      }
    }
  }
  /** Business logic to execute. */
  public final Response executeCommand(
      Object inputPar,
      UserSessionParameters userSessionPars,
      HttpServletRequest request,
      HttpServletResponse response,
      HttpSession userSession,
      ServletContext context) {
    Connection conn = null;
    PreparedStatement pstmt = null;
    try {
      conn = ConnectionManager.getConnection(context);

      // fires the GenericEvent.CONNECTION_CREATED event...
      EventsManager.getInstance()
          .processEvent(
              new GenericEvent(
                  this,
                  getRequestName(),
                  GenericEvent.CONNECTION_CREATED,
                  (JAIOUserSessionParameters) userSessionPars,
                  request,
                  response,
                  userSession,
                  context,
                  conn,
                  inputPar,
                  null));

      Object[] pars = (Object[]) inputPar;
      DetailPurchaseDocRowVO voTemplate = (DetailPurchaseDocRowVO) pars[0];
      VariantsMatrixVO matrixVO = (VariantsMatrixVO) pars[1];
      Object[][] cells = (Object[][]) pars[2];
      BigDecimal currencyDecimals = (BigDecimal) pars[3];

      Map attribute2dbField = new HashMap();
      attribute2dbField.put("companyCodeSys01DOC07", "COMPANY_CODE_SYS01");
      attribute2dbField.put("docTypeDOC07", "DOC_TYPE");
      attribute2dbField.put("docYearDOC07", "DOC_YEAR");
      attribute2dbField.put("docNumberDOC07", "DOC_NUMBER");
      attribute2dbField.put("rowNumberDOC07", "ROW_NUMBER");
      attribute2dbField.put("itemCodeItm01DOC07", "ITEM_CODE_ITM01");
      attribute2dbField.put("supplierItemCodePur02DOC07", "SUPPLIER_ITEM_CODE_PUR02");
      attribute2dbField.put("vatCodeItm01DOC07", "VAT_CODE_ITM01");
      attribute2dbField.put("valuePur04DOC07", "VALUE_PUR04");
      attribute2dbField.put("valueDOC07", "VALUE");
      attribute2dbField.put("qtyDOC07", "QTY");
      attribute2dbField.put("discountValueDOC07", "DISCOUNT_VALUE");
      attribute2dbField.put("discountPercDOC07", "DISCOUNT_PERC");
      attribute2dbField.put("vatValueDOC07", "VAT_VALUE");

      attribute2dbField.put("vatDescriptionDOC07", "VAT_DESCRIPTION");
      attribute2dbField.put("startDatePur04DOC07", "START_DATE_PUR04");
      attribute2dbField.put("endDatePur04DOC07", "END_DATE_PUR04");
      attribute2dbField.put("umCodePur02DOC07", "UM_CODE_PUR02");
      attribute2dbField.put("decimalsReg02DOC07", "DECIMALS_REG02");
      attribute2dbField.put("minPurchaseQtyPur02DOC07", "MIN_PURCHASE_QTY_PUR02");
      attribute2dbField.put("multipleQtyPur02DOC07", "MULTIPLE_QTY_PUR02");
      attribute2dbField.put("valueReg01DOC07", "VALUE_REG01");
      attribute2dbField.put("deductibleReg01DOC07", "DEDUCTIBLE_REG01");
      attribute2dbField.put("taxableIncomeDOC07", "TAXABLE_INCOME");
      attribute2dbField.put("progressiveHie02DOC07", "PROGRESSIVE_HIE02");
      attribute2dbField.put("deliveryDateDOC07", "DELIVERY_DATE");
      attribute2dbField.put("inQtyDOC07", "IN_QTY");
      attribute2dbField.put("orderQtyDOC07", "ORDER_QTY");
      attribute2dbField.put("invoiceQtyDOC07", "INVOICE_QTY");

      attribute2dbField.put("variantTypeItm06DOC07", "VARIANT_TYPE_ITM06");
      attribute2dbField.put("variantCodeItm11DOC07", "VARIANT_CODE_ITM11");
      attribute2dbField.put("variantTypeItm07DOC07", "VARIANT_TYPE_ITM07");
      attribute2dbField.put("variantCodeItm12DOC07", "VARIANT_CODE_ITM12");
      attribute2dbField.put("variantTypeItm08DOC07", "VARIANT_TYPE_ITM08");
      attribute2dbField.put("variantCodeItm13DOC07", "VARIANT_CODE_ITM13");
      attribute2dbField.put("variantTypeItm09DOC07", "VARIANT_TYPE_ITM09");
      attribute2dbField.put("variantCodeItm14DOC07", "VARIANT_CODE_ITM14");
      attribute2dbField.put("variantTypeItm10DOC07", "VARIANT_TYPE_ITM10");
      attribute2dbField.put("variantCodeItm15DOC07", "VARIANT_CODE_ITM15");

      DetailPurchaseDocRowVO vo = null;
      VariantsMatrixColumnVO colVO = null;
      VariantsMatrixRowVO rowVO = null;
      Response res = null;
      for (int i = 0; i < cells.length; i++) {
        rowVO = (VariantsMatrixRowVO) matrixVO.getRowDescriptors().get(i);

        if (matrixVO.getColumnDescriptors().size() == 0) {

          if (cells[i][0] != null) {
            vo = (DetailPurchaseDocRowVO) voTemplate.clone();

            if (!containsVariant(matrixVO, "ITM11_VARIANTS_1")) {
              // e.g. color but not no size...
              vo.setVariantCodeItm11DOC07(ApplicationConsts.JOLLY);
              vo.setVariantTypeItm06DOC07(ApplicationConsts.JOLLY);
            } else {
              vo.setVariantCodeItm11DOC07(rowVO.getVariantCodeITM11());
              vo.setVariantTypeItm06DOC07(rowVO.getVariantTypeITM06());
            }
            if (!containsVariant(matrixVO, "ITM12_VARIANTS_2")) {
              vo.setVariantCodeItm12DOC07(ApplicationConsts.JOLLY);
              vo.setVariantTypeItm07DOC07(ApplicationConsts.JOLLY);
            } else {
              vo.setVariantCodeItm12DOC07(rowVO.getVariantCodeITM11());
              vo.setVariantTypeItm07DOC07(rowVO.getVariantTypeITM06());
            }
            if (!containsVariant(matrixVO, "ITM13_VARIANTS_3")) {
              vo.setVariantCodeItm13DOC07(ApplicationConsts.JOLLY);
              vo.setVariantTypeItm08DOC07(ApplicationConsts.JOLLY);
            } else {
              vo.setVariantCodeItm13DOC07(rowVO.getVariantCodeITM11());
              vo.setVariantTypeItm08DOC07(rowVO.getVariantTypeITM06());
            }
            if (!containsVariant(matrixVO, "ITM14_VARIANTS_4")) {
              vo.setVariantCodeItm14DOC07(ApplicationConsts.JOLLY);
              vo.setVariantTypeItm09DOC07(ApplicationConsts.JOLLY);
            } else {
              vo.setVariantCodeItm14DOC07(rowVO.getVariantCodeITM11());
              vo.setVariantTypeItm09DOC07(rowVO.getVariantTypeITM06());
            }
            if (!containsVariant(matrixVO, "ITM15_VARIANTS_5")) {
              vo.setVariantCodeItm15DOC07(ApplicationConsts.JOLLY);
              vo.setVariantTypeItm10DOC07(ApplicationConsts.JOLLY);
            } else {
              vo.setVariantCodeItm15DOC07(rowVO.getVariantCodeITM11());
              vo.setVariantTypeItm10DOC07(rowVO.getVariantTypeITM06());
            }

            vo.setQtyDOC07((BigDecimal) cells[i][0]);

            PurchaseUtils.updateTotals(vo, currencyDecimals.intValue());

            /*
                        vo.setVariantCodeItm12DOC07(ApplicationConsts.JOLLY);
                        vo.setVariantCodeItm13DOC07(ApplicationConsts.JOLLY);
                        vo.setVariantCodeItm14DOC07(ApplicationConsts.JOLLY);
                        vo.setVariantCodeItm15DOC07(ApplicationConsts.JOLLY);

                        vo.setVariantTypeItm07DOC07(ApplicationConsts.JOLLY);
                        vo.setVariantTypeItm08DOC07(ApplicationConsts.JOLLY);
                        vo.setVariantTypeItm09DOC07(ApplicationConsts.JOLLY);
                        vo.setVariantTypeItm10DOC07(ApplicationConsts.JOLLY);
            */
            vo.setInQtyDOC07(new BigDecimal(0));
            vo.setOrderQtyDOC07(vo.getQtyDOC07());
            if (vo.getInvoiceQtyDOC07() == null) vo.setInvoiceQtyDOC07(new BigDecimal(0));
            vo.setRowNumberDOC07(
                progBean.getInternalProgressive("DOC07_PURCHASE_ITEMS", "ROW_NUMBER", conn));

            // insert into DOC07...
            res =
                QueryUtil.insertTable(
                    conn,
                    userSessionPars,
                    vo,
                    "DOC07_PURCHASE_ITEMS",
                    attribute2dbField,
                    "Y",
                    "N",
                    context,
                    true);

            if (res.isError()) {
              conn.rollback();
              return res;
            }
          } // end if on not null

        } else
          for (int k = 0; k < matrixVO.getColumnDescriptors().size(); k++) {

            colVO = (VariantsMatrixColumnVO) matrixVO.getColumnDescriptors().get(k);
            if (cells[i][k] != null) {
              vo = (DetailPurchaseDocRowVO) voTemplate.clone();

              vo.setQtyDOC07((BigDecimal) cells[i][k]);

              PurchaseUtils.updateTotals(vo, currencyDecimals.intValue());

              vo.setVariantCodeItm11DOC07(rowVO.getVariantCodeITM11());
              vo.setVariantTypeItm06DOC07(rowVO.getVariantTypeITM06());

              vo.setVariantCodeItm12DOC07(
                  colVO.getVariantCodeITM12() == null
                      ? ApplicationConsts.JOLLY
                      : colVO.getVariantCodeITM12());
              vo.setVariantCodeItm13DOC07(
                  colVO.getVariantCodeITM13() == null
                      ? ApplicationConsts.JOLLY
                      : colVO.getVariantCodeITM13());
              vo.setVariantCodeItm14DOC07(
                  colVO.getVariantCodeITM14() == null
                      ? ApplicationConsts.JOLLY
                      : colVO.getVariantCodeITM14());
              vo.setVariantCodeItm15DOC07(
                  colVO.getVariantCodeITM15() == null
                      ? ApplicationConsts.JOLLY
                      : colVO.getVariantCodeITM15());

              vo.setVariantTypeItm07DOC07(
                  colVO.getVariantTypeITM07() == null
                      ? ApplicationConsts.JOLLY
                      : colVO.getVariantTypeITM07());
              vo.setVariantTypeItm08DOC07(
                  colVO.getVariantTypeITM08() == null
                      ? ApplicationConsts.JOLLY
                      : colVO.getVariantTypeITM08());
              vo.setVariantTypeItm09DOC07(
                  colVO.getVariantTypeITM09() == null
                      ? ApplicationConsts.JOLLY
                      : colVO.getVariantTypeITM09());
              vo.setVariantTypeItm10DOC07(
                  colVO.getVariantTypeITM10() == null
                      ? ApplicationConsts.JOLLY
                      : colVO.getVariantTypeITM10());

              vo.setInQtyDOC07(new BigDecimal(0));
              vo.setOrderQtyDOC07(vo.getQtyDOC07());
              if (vo.getInvoiceQtyDOC07() == null) vo.setInvoiceQtyDOC07(new BigDecimal(0));
              vo.setRowNumberDOC07(
                  progBean.getInternalProgressive("DOC07_PURCHASE_ITEMS", "ROW_NUMBER", conn));

              // insert into DOC07...
              res =
                  QueryUtil.insertTable(
                      conn,
                      userSessionPars,
                      vo,
                      "DOC07_PURCHASE_ITEMS",
                      attribute2dbField,
                      "Y",
                      "N",
                      context,
                      true);

              if (res.isError()) {
                conn.rollback();
                return res;
              }
            } // end if on not null
          } // end inner for
      } // end outer for

      // recalculate totals...
      PurchaseDocPK pk =
          new PurchaseDocPK(
              vo.getCompanyCodeSys01DOC07(),
              vo.getDocTypeDOC07(),
              vo.getDocYearDOC07(),
              vo.getDocNumberDOC07());
      Response docResponse =
          docBean.loadPurchaseDoc(
              conn, pk, userSessionPars, request, response, userSession, context);
      if (docResponse.isError()) {
        conn.rollback();
        return docResponse;
      }
      DetailPurchaseDocVO docVO = (DetailPurchaseDocVO) ((VOResponse) docResponse).getVo();
      Response totalResponse =
          totalBean.calcDocTotals(
              conn, docVO, userSessionPars, request, response, userSession, context);
      if (totalResponse.isError()) {
        conn.rollback();
        return totalResponse;
      }

      pstmt =
          conn.prepareStatement(
              "update DOC06_PURCHASE set TAXABLE_INCOME=?,TOTAL_VAT=?,TOTAL=?,DOC_STATE=? where COMPANY_CODE_SYS01=? and DOC_TYPE=? and DOC_YEAR=? and DOC_NUMBER=?");
      pstmt.setBigDecimal(1, docVO.getTaxableIncomeDOC06());
      pstmt.setBigDecimal(2, docVO.getTotalVatDOC06());
      pstmt.setBigDecimal(3, docVO.getTotalDOC06());
      pstmt.setString(4, ApplicationConsts.HEADER_BLOCKED);
      pstmt.setString(5, vo.getCompanyCodeSys01DOC07());
      pstmt.setString(6, vo.getDocTypeDOC07());
      pstmt.setBigDecimal(7, vo.getDocYearDOC07());
      pstmt.setBigDecimal(8, vo.getDocNumberDOC07());
      pstmt.execute();

      Response answer = new VOResponse(vo);

      // fires the GenericEvent.BEFORE_COMMIT event...
      EventsManager.getInstance()
          .processEvent(
              new GenericEvent(
                  this,
                  getRequestName(),
                  GenericEvent.BEFORE_COMMIT,
                  (JAIOUserSessionParameters) userSessionPars,
                  request,
                  response,
                  userSession,
                  context,
                  conn,
                  inputPar,
                  answer));

      conn.commit();

      // fires the GenericEvent.AFTER_COMMIT event...
      EventsManager.getInstance()
          .processEvent(
              new GenericEvent(
                  this,
                  getRequestName(),
                  GenericEvent.AFTER_COMMIT,
                  (JAIOUserSessionParameters) userSessionPars,
                  request,
                  response,
                  userSession,
                  context,
                  conn,
                  inputPar,
                  answer));

      return answer;
    } catch (Throwable ex) {
      Logger.error(
          userSessionPars.getUsername(),
          this.getClass().getName(),
          "executeCommand",
          "Error while inserting new purchase order rows",
          ex);
      try {
        conn.rollback();
      } catch (Exception ex3) {
      }
      return new ErrorResponse(ex.getMessage());
    } finally {
      try {
        pstmt.close();
      } catch (Exception ex2) {
      }
      try {
        ConnectionManager.releaseConnection(conn, context);
      } catch (Exception ex1) {
      }
    }
  }
  /** Business logic to execute. */
  public VOListResponse updateExpirations(
      String t1,
      String t2,
      ArrayList oldVOs,
      ArrayList newVOs,
      String serverLanguageId,
      String username)
      throws Throwable {
    PreparedStatement pstmt = null;

    Connection conn = null;
    try {
      if (this.conn == null) conn = getConn();
      else conn = this.conn;
      insJornalItemAction.setConn(conn); // use same transaction...
      userParamAction.setConn(conn); // use same transaction...

      ExpirationVO oldVO = null;
      ExpirationVO newVO = null;
      Response res = null;

      HashSet pkAttrs = new HashSet();
      pkAttrs.add("companyCodeSys01DOC19");
      pkAttrs.add("progressiveDOC19");

      HashMap attribute2dbField = new HashMap();
      attribute2dbField.put("progressiveDOC19", "PROGRESSIVE");
      attribute2dbField.put("companyCodeSys01DOC19", "COMPANY_CODE_SYS01");
      attribute2dbField.put("docTypeDOC19", "DOC_TYPE");
      attribute2dbField.put("docNumberDOC19", "DOC_NUMBER");
      attribute2dbField.put("docYearDOC19", "DOC_YEAR");
      attribute2dbField.put("docSequenceDOC19", "DOC_SEQUENCE");
      attribute2dbField.put("name_1DOC19", "NAME_1");
      attribute2dbField.put("name_2DOC19", "NAME_2");
      attribute2dbField.put("descriptionDOC19", "DESCRIPTION");
      attribute2dbField.put("valueDOC19", "VALUE");
      attribute2dbField.put("payedDOC19", "PAYED");
      attribute2dbField.put("docDateDOC19", "DOC_DATE");
      attribute2dbField.put("expirationDateDOC19", "EXPIRATION_DATE");
      attribute2dbField.put("progressiveReg04DOC19", "PROGRESSIVE_REG04");
      attribute2dbField.put("customerSupplierCodeDOC19", "CUSTOMER_SUPPLIER_CODE");

      attribute2dbField.put("payedDateDOC19", "PAYED_DATE");
      attribute2dbField.put("payedValueDOC19", "PAYED_VALUE");
      attribute2dbField.put("realPaymentTypeCodeReg11DOC19", "REAL_PAYMENT_TYPE_CODE_REG11");
      attribute2dbField.put("paymentTypeCodeReg11DOC19", "PAYMENT_TYPE_CODE_REG11");

      JournalHeaderVO jhVO = null;
      HashMap map = new HashMap();
      String bankAccountCode = null;

      for (int i = 0; i < oldVOs.size(); i++) {
        oldVO = (ExpirationVO) oldVOs.get(i);
        newVO = (ExpirationVO) newVOs.get(i);

        res =
            new QueryUtil()
                .updateTable(
                    conn,
                    new UserSessionParameters(username),
                    pkAttrs,
                    oldVO,
                    newVO,
                    "DOC19_EXPIRATIONS",
                    attribute2dbField,
                    "Y",
                    "N",
                    null,
                    true);
        if (res.isError()) {
          throw new Exception(res.getErrorMessage());
        }

        map.put(ApplicationConsts.COMPANY_CODE_SYS01, newVO.getCompanyCodeSys01DOC19());
        map.put(ApplicationConsts.PARAM_CODE, ApplicationConsts.BANK_ACCOUNT);
        res = userParamAction.loadUserParam(map, serverLanguageId, username);
        if (res.isError()) {
          throw new Exception(res.getErrorMessage());
        }
        bankAccountCode = ((VOResponse) res).getVo().toString();

        // generate an accounting item if the row has been payed...
        if (!oldVO.getPayedDOC19().booleanValue() && newVO.getPayedDOC19().booleanValue()) {

          jhVO = new JournalHeaderVO();
          jhVO.setCompanyCodeSys01ACC05(newVO.getCompanyCodeSys01DOC19());
          String creditDebitAccountCode = null;
          String accountCodeTypeACC06 = null;
          if (newVO.getDocTypeDOC19().equals(ApplicationConsts.SALE_DESK_DOC_TYPE)
              || newVO.getDocTypeDOC19().equals(ApplicationConsts.SALE_INVOICE_DOC_TYPE)
              || newVO.getDocTypeDOC19().equals(ApplicationConsts.SALE_INVOICE_FROM_DN_DOC_TYPE)
              || newVO.getDocTypeDOC19().equals(ApplicationConsts.SALE_INVOICE_FROM_SD_DOC_TYPE)
              || newVO.getDocTypeDOC19().equals(ApplicationConsts.SALE_CREDIT_NOTE_DOC_TYPE)
              || newVO.getDocTypeDOC19().equals(ApplicationConsts.SALE_GENERIC_INVOICE)) {
            jhVO.setAccountingMotiveCodeAcc03ACC05(ApplicationConsts.MOTIVE_INVOICE_PROCEEDS);
            jhVO.setDescriptionACC05(
                newVO.getDescriptionDOC19()
                    + " - "
                    + t1
                    + " "
                    + newVO.getName_1DOC19()
                    + " "
                    + (newVO.getName_2DOC19() == null ? "" : newVO.getName_2DOC19()));

            // determine account codes defined for the current customer...
            pstmt =
                conn.prepareStatement(
                    "select CREDIT_ACCOUNT_CODE_ACC02 from SAL07_CUSTOMERS where COMPANY_CODE_SYS01=? and PROGRESSIVE_REG04=?");
            pstmt.setString(1, newVO.getCompanyCodeSys01DOC19());
            pstmt.setBigDecimal(2, newVO.getProgressiveReg04DOC19());
            ResultSet rset = pstmt.executeQuery();
            if (!rset.next()) {
              rset.close();
              throw new Exception("customer not found");
            }
            creditDebitAccountCode = rset.getString(1);
            rset.close();
            pstmt.close();

            accountCodeTypeACC06 = ApplicationConsts.ACCOUNT_TYPE_CUSTOMER;
          } else {
            jhVO.setAccountingMotiveCodeAcc03ACC05(ApplicationConsts.MOTIVE_PURCHASE_INVOICE_PAYED);
            jhVO.setDescriptionACC05(
                newVO.getDescriptionDOC19()
                    + " - "
                    + t2
                    + " "
                    + newVO.getName_1DOC19()
                    + " "
                    + (newVO.getName_2DOC19() == null ? "" : newVO.getName_2DOC19()));

            // determine account codes defined for the current supplier...
            pstmt =
                conn.prepareStatement(
                    "select DEBIT_ACCOUNT_CODE_ACC02 from PUR01_SUPPLIERS where COMPANY_CODE_SYS01=? and PROGRESSIVE_REG04=?");
            pstmt.setString(1, newVO.getCompanyCodeSys01DOC19());
            pstmt.setBigDecimal(2, newVO.getProgressiveReg04DOC19());
            ResultSet rset = pstmt.executeQuery();
            if (!rset.next()) {
              rset.close();
              throw new Exception("supplier not found");
            }
            creditDebitAccountCode = rset.getString(1);
            rset.close();
            pstmt.close();

            accountCodeTypeACC06 = ApplicationConsts.ACCOUNT_TYPE_SUPPLIER;
          }
          jhVO.setItemDateACC05(newVO.getPayedDateDOC19());
          Calendar cal = Calendar.getInstance();
          cal.setTime(newVO.getPayedDateDOC19());
          jhVO.setItemYearACC05(new BigDecimal(cal.get(Calendar.YEAR)));

          JournalRowVO jrVO = new JournalRowVO();
          jrVO.setCompanyCodeSys01ACC06(jhVO.getCompanyCodeSys01ACC05());
          jrVO.setAccountCodeAcc02ACC06(creditDebitAccountCode);
          jrVO.setAccountCodeACC06(newVO.getCustomerSupplierCodeDOC19());
          jrVO.setAccountCodeTypeACC06(accountCodeTypeACC06);
          jrVO.setCreditAmountACC06(newVO.getPayedValueDOC19());
          jrVO.setDescriptionACC06("");
          jrVO.setItemYearAcc05ACC06(jhVO.getItemYearACC05());
          jrVO.setProgressiveAcc05ACC06(jhVO.getProgressiveACC05());
          jhVO.addJournalRow(jrVO);

          jrVO = new JournalRowVO();
          jrVO.setCompanyCodeSys01ACC06(jhVO.getCompanyCodeSys01ACC05());
          jrVO.setAccountCodeAcc02ACC06(bankAccountCode);
          jrVO.setAccountCodeACC06(bankAccountCode);
          jrVO.setAccountCodeTypeACC06(ApplicationConsts.ACCOUNT_TYPE_ACCOUNT);
          jrVO.setDebitAmountACC06(newVO.getPayedValueDOC19());
          jrVO.setDescriptionACC06("");
          jrVO.setItemYearAcc05ACC06(jhVO.getItemYearACC05());
          jrVO.setProgressiveAcc05ACC06(jhVO.getProgressiveACC05());
          jhVO.addJournalRow(jrVO);

          res = insJornalItemAction.insertJournalItem(jhVO, serverLanguageId, username);
          if (res.isError()) {
            throw new Exception(res.getErrorMessage());
          }
        }
      }

      return new VOListResponse(newVOs, false, newVOs.size());
    } catch (Throwable ex) {
      Logger.error(
          username,
          this.getClass().getName(),
          "executeCommand",
          "Error while updating existing expirations",
          ex);
      try {
        if (this.conn == null && conn != null)
          // rollback only local connection
          conn.rollback();
      } catch (Exception ex3) {
      }
      throw new Exception(ex.getMessage());
    } finally {
      try {
        if (pstmt != null) pstmt.close();
      } catch (Exception ex1) {
      }

      try {
        insJornalItemAction.setConn(null);
        userParamAction.setConn(null);
      } catch (Exception ex) {
      }
      try {
        if (this.conn == null && conn != null) {
          // close only local connection
          conn.commit();
          conn.close();
        }

      } catch (Exception exx) {
      }
    }
  }
  /** Business logic to execute. */
  public final Response executeCommand(
      Object inputPar,
      UserSessionParameters userSessionPars,
      HttpServletRequest request,
      HttpServletResponse response,
      HttpSession userSession,
      ServletContext context) {
    Connection conn = null;
    PreparedStatement pstmt = null;
    try {
      String serverLanguageId = ((JAIOUserSessionParameters) userSessionPars).getServerLanguageId();
      conn = ConnectionManager.getConnection(context);

      // fires the GenericEvent.CONNECTION_CREATED event...
      EventsManager.getInstance()
          .processEvent(
              new GenericEvent(
                  this,
                  getRequestName(),
                  GenericEvent.CONNECTION_CREATED,
                  (JAIOUserSessionParameters) userSessionPars,
                  request,
                  response,
                  userSession,
                  context,
                  conn,
                  inputPar,
                  null));

      ArrayList oldVOs = ((ArrayList[]) inputPar)[0];
      ArrayList newVOs = ((ArrayList[]) inputPar)[1];

      Map attribute2dbField = new HashMap();
      attribute2dbField.put("companyCodeSys01DOC20", "COMPANY_CODE_SYS01");
      attribute2dbField.put("progressiveDoc14DOC20", "PROGRESSIVE_DOC14");
      attribute2dbField.put("progressiveSys10DOC20", "PROGRESSIVE_SYS10");
      attribute2dbField.put("textValueDOC20", "TEXT_VALUE");
      attribute2dbField.put("numValueDOC20", "NUM_VALUE");
      attribute2dbField.put("dateValueDOC20", "DATE_VALUE");

      HashSet pkAttributes = new HashSet();
      pkAttributes.add("companyCodeSys01DOC20");
      pkAttributes.add("progressiveDoc14DOC20");
      pkAttributes.add("progressiveSys10DOC20");

      Response res = null;
      DocPropertyVO oldVO = null;
      DocPropertyVO newVO = null;

      pstmt =
          conn.prepareStatement(
              "select PROGRESSIVE_DOC14 from DOC20_DOC_PROPERTIES where "
                  + "COMPANY_CODE_SYS01=? and PROGRESSIVE_DOC14=? and PROGRESSIVE_SYS10=?");
      ResultSet rset = null;

      for (int i = 0; i < oldVOs.size(); i++) {
        oldVO = (DocPropertyVO) oldVOs.get(i);
        newVO = (DocPropertyVO) newVOs.get(i);

        // check if the record already exists: if it does not exist, then insert it...
        pstmt.setString(1, newVO.getCompanyCodeSys01DOC20());
        pstmt.setBigDecimal(2, newVO.getProgressiveDoc14DOC20());
        pstmt.setBigDecimal(3, newVO.getProgressiveSys10DOC20());
        rset = pstmt.executeQuery();
        if (rset.next()) {
          // the record exixts: it will be updated...
          res =
              QueryUtil.updateTable(
                  conn,
                  userSessionPars,
                  pkAttributes,
                  oldVO,
                  newVO,
                  "DOC20_DOC_PROPERTIES",
                  attribute2dbField,
                  "Y",
                  "N",
                  context,
                  true);
          if (res.isError()) {
            conn.rollback();
            return res;
          }
        } else {
          // the record does not exixt: it will be inserted...
          res =
              QueryUtil.insertTable(
                  conn,
                  userSessionPars,
                  newVO,
                  "DOC20_DOC_PROPERTIES",
                  attribute2dbField,
                  "Y",
                  "N",
                  context,
                  true);
          if (res.isError()) {
            conn.rollback();
            return res;
          }
        }
        rset.close();
      }

      Response answer = new VOListResponse(newVOs, false, newVOs.size());

      // fires the GenericEvent.BEFORE_COMMIT event...
      EventsManager.getInstance()
          .processEvent(
              new GenericEvent(
                  this,
                  getRequestName(),
                  GenericEvent.BEFORE_COMMIT,
                  (JAIOUserSessionParameters) userSessionPars,
                  request,
                  response,
                  userSession,
                  context,
                  conn,
                  inputPar,
                  answer));

      conn.commit();

      // fires the GenericEvent.AFTER_COMMIT event...
      EventsManager.getInstance()
          .processEvent(
              new GenericEvent(
                  this,
                  getRequestName(),
                  GenericEvent.AFTER_COMMIT,
                  (JAIOUserSessionParameters) userSessionPars,
                  request,
                  response,
                  userSession,
                  context,
                  conn,
                  inputPar,
                  answer));

      return answer;
    } catch (Throwable ex) {
      Logger.error(
          userSessionPars.getUsername(),
          this.getClass().getName(),
          "executeCommand",
          "Error while updating property values for the specified document",
          ex);
      try {
        conn.rollback();
      } catch (Exception ex3) {
      }
      return new ErrorResponse(ex.getMessage());
    } finally {
      try {
        pstmt.close();
      } catch (Exception ex2) {
      }
      try {
        ConnectionManager.releaseConnection(conn, context);
      } catch (Exception ex1) {
      }
    }
  }