package com.gpc.server.command.order; import java.sql.CallableStatement; import java.sql.Connection; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.sql.Timestamp; import java.text.MessageFormat; import java.util.ArrayList; import java.util.Date; import java.util.HashMap; import java.util.List; import org.apache.log4j.Logger; import com.gpc.backofficecommon.constants.schema.Sch_POItem; import com.gpc.backofficecommon.constants.schema.Sch_PO_Header; import com.gpc.backofficecommon.constants.schema.Sch_PoItemStore; import com.gpc.backofficecommon.constants.schema.Sch_Tables; import com.gpc.common.ApplicationContext; import com.gpc.common.Profile; import com.gpc.common.constants.refvalues.RefCommunicationType; import com.gpc.common.exception.ApplicationException; import com.gpc.common.exception.ServiceExecutorException; import com.gpc.common.util.StringUtils; import com.gpc.server.command.Command; import com.gpc.server.dataaccess.DAOException; import com.gpc.server.dataaccess.inventory.MultiStoreOrderDAO; import com.gpc.server.message.dataaccess.Message; import com.gpc.server.util.DVDPartReaderUtil; import com.gpc.server.util.InventoryPartUtil; import com.gpc.server.util.JDBCUtil; import com.gpc.server.util.ServerUtil; import com.gpc.server.util.TamsIIUblServiceHelper; import com.gpc.service.comm.proxy.MS_Exec_SQLService; import com.gpc.service.multistore.executor.MultiStoreSQLServiceExecutor; import com.gpc.service.tams.TAMSIIServiceHolder; import com.gpc.tams.comm.io.CommConstants; import com.gpc.tams.comm.io.Transin2Router; import com.gpc.tams.transmit.TsoTransmit; import com.gpc.valueobjects.common.CommandVO; import com.gpc.valueobjects.common.ResultsVO; import com.gpc.valueobjects.dvd.InventoryPartVO; import com.gpc.valueobjects.dvd.StockPartVO; import com.gpc.valueobjects.message.MessageDTO; import com.gpc.valueobjects.message.MessageHeaderVO; import com.gpc.valueobjects.message.MessageTextVO; import com.gpc.valueobjects.ordering.PoItemStoreVo; import com.gpc.valueobjects.ordering.PostReceiptsVO; import com.gpc.valueobjects.profile.MultiStoreProfileVO; import com.gpc.valueobjects.profile.StoreProfileVO; /** * Command for the PostReceipts module. * * @author Copyright © 2005 Genuine Parts Company */ public class PostReceipts extends Command { private static final String PROCESS_MS_FAILURE_TEMPLATE = "An error happened either processing order {0} as multi store or determining if we should. Posting of this order to inventory did occur but nothing was sent to MultiStore."; private static int UBL_REF_FAPS_ID = -1; private static final Logger logger = Logger.getLogger(PostReceipts.class); public static final String POST_RECEIPTS_CHECKINNUM_STMT = "{ Call sp_post_receipts(?,?,?,?,?,?,?,?,?,?) }"; public static final String POST_RECEIPTS_STMT = "{ Call sp_post_receipts(?,?,?,?,?,?,?,?,?) }"; private static final String SP_CONSOL_ORDERS_TEMPLATE = "'{' CALL sp_consol_orders2({0,number,#},{1,number,#},\"{2}\"') '}'"; private static final String UBL_REF_FAPS_ID_SQL = "SELECT id FROM ref_file_activity_program_source WHERE program_source = 'FrmOR_RecGds_EditPost'"; private static final String UBL_REF_FAPS_ID_COLUMN = "id"; // Query to fetch Q4 record type details private static final String SQL_GET_BRANCH_ORDER_DETAILS = "SELECT " + "POIS." + Sch_PoItemStore.ORDER_FOR_STORE_NUMBER + ", " + "POIS." + Sch_PoItemStore.PURCHASE_ORDER_NUMBER + ", " + "POI." + Sch_POItem.LINE_ABBREV + ", " + "POI." + Sch_POItem.PART_NUMBER + ", " + "POIS." + Sch_PoItemStore.RECEIVED_QTY + ", " + "POIS." + Sch_PoItemStore.PO_ITEM_ID + ", " + "POI." + Sch_POItem.POSTED_DATE + ", " + "POIS." + Sch_PoItemStore.RECEIVED_QTY + "," + "POIS." + Sch_PoItemStore.DISTRIBUTED_QTY + " FROM " + Sch_Tables.PO_HEADER + " AS POH INNER JOIN " + Sch_Tables.PO_ITEM + " AS POI ON " + "POH." + Sch_PO_Header.ID + "= POI." + Sch_POItem.PO_HEADER_ID + " AND " + "POH." + Sch_PO_Header.LOC + " = POI." + Sch_POItem.LOC + " INNER JOIN " + Sch_Tables.PO_ITEM_STORE + " POIS ON POIS." + Sch_PoItemStore.PO_ITEM_ID + "= POI." + Sch_POItem.ID + " AND " + "POIS." + Sch_PoItemStore.LOC + " = POI." + Sch_POItem.LOC + " WHERE POH." + Sch_PO_Header.PURCHASE_ORDER_NUMBER + " = ''{0}'' AND " + " POIS." + Sch_PoItemStore.RECEIVED_QTY + " > 0 "; // Query to update distributed quantity value private static final String SQL_UPDATE_BRANCH_STORE_DISTRIBUTED_QTY = "UPDATE " + Sch_Tables.PO_ITEM_STORE + " SET " + Sch_PoItemStore.DISTRIBUTED_QTY + " = ? , " + Sch_PoItemStore.RECEIVED_QTY + " = 0 WHERE " + Sch_PoItemStore.ORDER_FOR_STORE_NUMBER + " = ? " + "AND " + Sch_PoItemStore.PO_ITEM_ID + " = ? AND " + Sch_PoItemStore.PURCHASE_ORDER_NUMBER + " = ? "; // Transmission message to be sent after Q4 record creation. private static final String STORE_TRANSMISSION_MESSAGE = "You have received a branch store transmission for the PO(s) " + " {0} . This can be processed via Process Received Transmission or by End of Day."; /** Constant used for empty string */ private static final String EMPTY_STRING = ""; /** Constant used for space */ private static final String SPACE = " "; /** Constant used for database directory */ private static final String DATABASE_DIRECTORY = "/usr/tams/database/"; /** Constant used for TRANSIN */ private static final String TRANSIN = "TRANSIN"; /** Constant used for delimiter */ private static final char DELIMITER = '*'; /** Constant used for comma */ private static final String COMMA = ", "; /** Constant used for message */ private static final String MESSAGE = "M"; // private static final private DVDPartReaderUtil dvdPartReaderUtil = null; private InventoryPartUtil inventoryPartUtil = null; public ResultsVO execute(CommandVO vo) { Connection conn = null; ResultsVO res = new ResultsVO(1); // fail try { conn = ServerUtil.getConnection(); if (UBL_REF_FAPS_ID == -1){ UBL_REF_FAPS_ID = getRefFapsId(conn); logger.debug("UBL_REF_FAPS_ID is now "+UBL_REF_FAPS_ID); } return execute((PostReceiptsVO) vo, conn); } catch (Exception e) { logger.error(e.getMessage(), e); res = new ResultsVO(new ApplicationException(e)); } finally { ServerUtil.releaseConnection(conn); } return res; } ResultsVO execute(PostReceiptsVO vo, Connection conn) { ResultsVO res = new ResultsVO(1); // failure if (conn != null && vo != null) { Integer orderId = vo.getOrderId(); Integer loc = vo.getLoc(); String poNumber = vo.getPoNumber(); String finalShipFlag = vo.getFinalShipFlag(); String landedFlag = vo.getLandedFlag(); Integer salesYear = vo.getSalesYear(); Integer salesMonth = vo.getSalesMonth(); Integer employeeID = vo.getEmployeeId(); Timestamp timeStarted = vo.getTimestamp(); Integer checkInNum = vo.getCheckInNum(); String keepBackorder = vo.getKeepBackorder(); logger.debug("Parameters received in invoked object are:"); logger.debug("Order Id = " + orderId); logger.debug("Loc = " + loc); logger.debug("PO Number = " + poNumber); logger.debug("Final Ship Flag = " + finalShipFlag); logger.debug("Landed flag = " + landedFlag); logger.debug("Sales year = " + salesYear); logger.debug("Sales Month = " + salesMonth); logger.debug("Employee ID = " + employeeID); logger.debug("timeStarted = " + timeStarted); logger.debug("Check in Group = " + checkInNum); logger.debug("keep Backorder = " + keepBackorder); Statement statement = null; ResultSet rs = null; ResultSet sp_rset = null; CallableStatement postReceiptsCall = null; try { dvdPartReaderUtil = new DVDPartReaderUtil(conn, loc.intValue()); inventoryPartUtil = new InventoryPartUtil(conn, loc.intValue()); StringBuffer sb = new StringBuffer(); sb .append("SELECT ID, PO_HEADER_ID , LINE_ABBREV, PART_NUMBER "); sb.append(" FROM PO_ITEM WHERE "); sb.append(" PO_HEADER_ID = ").append(orderId); sb.append(" AND LOC = ").append(loc); logger.debug("getSpecificPOHeader = " + sb.toString()); statement = conn.createStatement(); rs = statement.executeQuery(sb.toString()); while (rs.next()) { String line = rs.getString("LINE_ABBREV"); String part = rs.getString("PART_NUMBER"); logger.debug("line = " + line); logger.debug("part = " + part); StockPartVO stockVO = new StockPartVO(part, line, loc, employeeID); int[] invStkIds = inventoryPartUtil.isStocked(stockVO); // if invStkIds = null then...add to inv and invStk if (invStkIds == null) { Integer invId = addPartToInventory(loc, line, part, employeeID, dvdPartReaderUtil, inventoryPartUtil, timeStarted); logger.debug("invId = " + invId); inventoryPartUtil.addPartToStock(stockVO, timeStarted); } else if (invStkIds.length > 1 && invStkIds[1] == -1) { // if invStkIds = zero then...already in inv so add to // invStk inventoryPartUtil.addPartToStock(stockVO, timeStarted); } } if (checkInNum != null) { postReceiptsCall = conn .prepareCall(POST_RECEIPTS_CHECKINNUM_STMT); postReceiptsCall.setInt(1, orderId.intValue()); postReceiptsCall.setInt(2, loc.intValue()); postReceiptsCall.setString(3, poNumber); postReceiptsCall.setString(4, landedFlag); postReceiptsCall.setInt(5, salesYear.intValue()); postReceiptsCall.setInt(6, salesMonth.intValue()); postReceiptsCall.setInt(7, employeeID.intValue()); postReceiptsCall.setTimestamp(8, timeStarted); postReceiptsCall.setString(9, keepBackorder); postReceiptsCall.setInt(10, checkInNum.intValue()); } else { postReceiptsCall = conn.prepareCall(POST_RECEIPTS_STMT); postReceiptsCall.setInt(1, orderId.intValue()); postReceiptsCall.setInt(2, loc.intValue()); postReceiptsCall.setString(3, poNumber); postReceiptsCall.setString(4, landedFlag); postReceiptsCall.setInt(5, salesYear.intValue()); postReceiptsCall.setInt(6, salesMonth.intValue()); postReceiptsCall.setInt(7, employeeID.intValue()); postReceiptsCall.setTimestamp(8, timeStarted); postReceiptsCall.setString(9, keepBackorder); } sp_rset = postReceiptsCall.executeQuery(); String vald = null; boolean success = false; while (sp_rset.next()){ vald = sp_rset.getString(1); success = true; } // JDBCUtil.closeResultSet(rs); if (success) { res = new ResultsVO(0);//successful } logger.debug("Creating MultiStoreOrderDAO"); MultiStoreOrderDAO msdao = new MultiStoreOrderDAO(); if (msdao.poIsMultiStoreCreated(conn.createStatement(), vo.getOrderId(), vo.getLoc())) { logger.debug("poIsMultiStoreCreated = TRUE"); processMultiStoreIfNecessary(vo, conn); } else if (checkInNum != null) { // Create Q4 records if PO has spoke store orders. createBranchStoreOrderRecord(vo, conn); } } catch (DAOException e) { logger.error(e, e); res = new ResultsVO(new ApplicationException(e)); } catch (SQLException e) { logger.error(e, e); res = new ResultsVO(new ApplicationException(e)); } catch (ApplicationException e) { logger.error(e, e); res = new ResultsVO(e); } finally { JDBCUtil.closeResultSet(rs); JDBCUtil.closeResultSet(sp_rset); JDBCUtil.closeStatement(statement); JDBCUtil.closeCallableStatement(postReceiptsCall); } } return res; } private Integer addPartToInventory(Integer loc, String lineAbbrev, String partNumber, Integer employeeID, DVDPartReaderUtil dvdPartReaderUtil, InventoryPartUtil inventoryPartUtil, Timestamp timeStarted) { try { InventoryPartVO inventoryVO = new InventoryPartVO(partNumber, lineAbbrev, loc, employeeID); return inventoryPartUtil.addInventoryPart(inventoryVO, dvdPartReaderUtil, timeStarted); } catch (ApplicationException e) { logger.debug("something bad happened here"); logger.error(e, e); return null; } } private int getRefFapsId(Connection conn) throws SQLException { ResultSet rs = null; Statement statement = null; try { statement = conn.createStatement(); rs = statement.executeQuery(UBL_REF_FAPS_ID_SQL); rs.next(); return rs.getInt(UBL_REF_FAPS_ID_COLUMN); } finally { JDBCUtil.closeResultSet(rs); JDBCUtil.closeStatement(statement); } } /** * Method is used to create branch store order [Q4] record. * * @param {@link * PostReceiptsVO} command vo for Post Receipts module. * * @param {@link * Connection} object used to get connection to database * * @throws {@link * DAOException} if any error occurs. */ private void createBranchStoreOrderRecord(final PostReceiptsVO postReceipts, final Connection connection) throws DAOException { final String methodSpec = " PostReceipts.createBranchStoreOrderRecord - "; logger.debug(methodSpec + " PO Num = " + postReceipts.getPoNumber()); if (postReceipts != null) { final String branchStoreOrderRecordInfoSql = MessageFormat.format(SQL_GET_BRANCH_ORDER_DETAILS, new Object[] { postReceipts.getPoNumber() }); logger.debug(methodSpec + " SQL_GET_BRANCH_ORDER_DETAILS = " + branchStoreOrderRecordInfoSql); final Object recordData[] = new Object[5]; final StringBuffer lineAbbrevPart = new StringBuffer(150); final StringBuffer spokeStorePONumbers = new StringBuffer(150); final List poItemStoreValues = new ArrayList(); String lineAbbrev = EMPTY_STRING; String storePurchaseOrderNumber = null; boolean poHasMutipleStores = false; PoItemStoreVo poItemStore = null; String orderForStoreNumber = null; Short receivedQuantity = null; Short distributedQuantity = null; String postedDate = null; final TsoTransmit tsoTransmit = new TsoTransmit(TRANSIN); Statement statment = null; ResultSet resultSet = null; try { statment = connection.createStatement(); resultSet = statment.executeQuery(branchStoreOrderRecordInfoSql.toString()); final StoreProfileVO storeProfile = ApplicationContext.getInstance().getProfile( Profile.SERVER, postReceipts.getLoc().intValue()).getStoreProfile(); logger.debug(methodSpec + " Q4 Record Info [Branch Order] \n "); while (resultSet.next()) { poItemStore = new PoItemStoreVo(); orderForStoreNumber = checkIsEmptyOrNull( resultSet.getString(Sch_PoItemStore.ORDER_FOR_STORE_NUMBER)); postedDate = resultSet.getString(Sch_POItem.POSTED_DATE); poItemStore.setOrderForStoreNumber(orderForStoreNumber); receivedQuantity = new Short(resultSet.getShort(Sch_PoItemStore.RECEIVED_QTY)); poItemStore.setReceivedQty(receivedQuantity); distributedQuantity = new Short(resultSet.getShort(Sch_PoItemStore.DISTRIBUTED_QTY)); poItemStore.setDistributedQty(distributedQuantity); poItemStore.setPoItemId(new Integer(resultSet.getInt(Sch_PoItemStore.PO_ITEM_ID))); storePurchaseOrderNumber = checkIsEmptyOrNull( resultSet.getString(Sch_PoItemStore.PURCHASE_ORDER_NUMBER)); storePurchaseOrderNumber = storePurchaseOrderNumber.trim().length() > 0 ? storePurchaseOrderNumber : postReceipts.getPoNumber(); poItemStore.setPurchaseOrderNumber(storePurchaseOrderNumber); poItemStoreValues.add(poItemStore); if(!storeProfile.getStoreNum().equalsIgnoreCase(orderForStoreNumber) && postedDate != null && postedDate.trim().length() > 0){ recordData[0] = CommConstants.RECORD_TYPE_Q4; recordData[1] = orderForStoreNumber; recordData[2] = storePurchaseOrderNumber; lineAbbrev = checkIsEmptyOrNull(resultSet.getString(Sch_POItem.LINE_ABBREV)); lineAbbrevPart.append(lineAbbrev.trim().length() == 2 ? lineAbbrev + SPACE : lineAbbrev); lineAbbrevPart.append(checkIsEmptyOrNull(resultSet.getString(Sch_POItem.PART_NUMBER))); recordData[3] = lineAbbrevPart.toString(); lineAbbrevPart.delete(0, lineAbbrevPart.length()); recordData[4] = receivedQuantity; tsoTransmit.setDelimiter(DELIMITER); tsoTransmit.appendLine(recordData); poHasMutipleStores = true; logger.debug(recordData); if (spokeStorePONumbers.toString().indexOf(storePurchaseOrderNumber) < 0) { spokeStorePONumbers.append(storePurchaseOrderNumber); spokeStorePONumbers.append(COMMA); } } } if (poHasMutipleStores) { tsoTransmit.writeTransmitFile(false, DATABASE_DIRECTORY); // Create transmission message if (spokeStorePONumbers.length() > 0) { final String poNumbers = spokeStorePONumbers.toString(); logger.debug(methodSpec + " Create transmission message ... "); createTransmissionMessage(storeProfile, poNumbers.substring(0, poNumbers.trim().length() - 1)); } } // 'po_item_store.received_qty' value is copied to 'po_item_store.distributed_qty' // and 'po_item_store.received_qty' value is set to zero. updateDistributedQuantity(poItemStoreValues, connection); } catch (SQLException sqlException) { logger.error(sqlException, sqlException); throw new DAOException(sqlException); } finally { JDBCUtil.closeStatement(statment); } } } /** * Method is used to check whether given text is empty or null * * @param inputText is the value to be verified for empty & null condition check. * * @return a input text value if it is not null else empty string. */ private String checkIsEmptyOrNull(final String inputText) { return inputText != null && inputText.trim().length() > 0 ? inputText : EMPTY_STRING; } /** * Method is used to copy the 'po_item_store.received_qty' value to 'po_item_store.distributed_qty' * while posting to inventory. * * @param {@link List} list contains the {@link PoItemStoreVo} value object. * * @param {@link Connection} is used to connection to database. * * @throws {@link DAOException} if any error occurs. */ private void updateDistributedQuantity(final List poItemStoreValues, Connection connection) throws DAOException { final String methodSpec = " PostReceipts.updateDistributedQuantity - "; logger.debug(methodSpec + " SQL_UPDATE_BRANCH_STORE_DISTRIBUTED_QTY = " + SQL_UPDATE_BRANCH_STORE_DISTRIBUTED_QTY); PoItemStoreVo poItemStore = null; PreparedStatement preparedStatement = null; final int poItemStoreListSize = poItemStoreValues.size(); Short totalDistributedQuantity = null; try { for (int index = 0; index < poItemStoreListSize; index++) { poItemStore = (PoItemStoreVo) poItemStoreValues.get(index); preparedStatement = connection.prepareStatement(SQL_UPDATE_BRANCH_STORE_DISTRIBUTED_QTY); totalDistributedQuantity = new Short((short) (poItemStore.getReceivedQty().shortValue() + poItemStore.getDistributedQty().shortValue())); logger.debug(methodSpec + "Order For StoreNumber = " + poItemStore.getOrderForStoreNumber() + " totalDistributedQuantity = " + totalDistributedQuantity); preparedStatement.setObject(1, totalDistributedQuantity); preparedStatement.setObject(2, poItemStore.getOrderForStoreNumber()); preparedStatement.setObject(3, poItemStore.getPoItemId()); preparedStatement.setObject(4, poItemStore.getPurchaseOrderNumber()); preparedStatement.executeUpdate(); } } catch (SQLException sqlException) { logger.error(sqlException, sqlException); throw new DAOException(sqlException); } finally { JDBCUtil.closePreparedStatement(preparedStatement); } logger.debug(methodSpec + "'po_item_store.received_qty' value is copied to " + "'po_item_store.distributed_qty' successfully "); } /** * Method is used to create transmission message * * @param {@link StoreProfileVO} value object containing store profile details. * * @param spokeStorePODetails contains the purchase order numbers of branch stores. * * @throws {@link DAOException} if any error occurs. */ private void createTransmissionMessage(final StoreProfileVO storeProfile, final String spokeStorePODetails) throws DAOException { logger.debug("in PostReceipts.createTransmissionMessage method : Spoke Store PO Details = " + spokeStorePODetails); final MessageDTO messageDto = new MessageDTO(); // Create Message Header Details messageDto.setMessageHeaderVO(createMessageHeader(storeProfile)); // Create Message Text Details createMessageText(messageDto, spokeStorePODetails, storeProfile); final Message message = new Message(); try { message.insertMessage(messageDto); } catch (SQLException sqlException) { throw new DAOException(sqlException); } logger.debug(" Transmission message details successfully created in MESSAGE_HEADER & " + " MESSAGE_TEXT tables "); } /** * Method is used to create message header details * * @param {StoreProfileVO} value object containing store profile details. * * @return {MessageHeaderVO} value object containing message header details. */ private MessageHeaderVO createMessageHeader(final StoreProfileVO storeProfile){ logger.debug("in PostReceipts.createMessageHeader method "); final MessageHeaderVO messageHeader = new MessageHeaderVO(); messageHeader.setLOC(storeProfile.getLocation()); messageHeader.setRefCommunicationTypeID(new Integer(RefCommunicationType.INTERSTORE)); messageHeader.setStoreNumber(storeProfile.getStoreNum()); messageHeader.setLogOnDate(new Date()); messageHeader.setLogOffDate(new Date()); messageHeader.setMessageType(MESSAGE); return messageHeader; } /** * Method is used to create transmission message text details. * * @param {MessageDTO} value object transmission message details. * * @param spokeStorePODetails contains the purchase order numbers of branch stores. * * @param {StoreProfileVO} value object containing store profile details. */ private void createMessageText(MessageDTO messageDto, final String spokeStorePODetails, final StoreProfileVO storeProfile){ logger.debug("in PostReceipts.createMessageText method "); final String storeTransmissionMessage = MessageFormat.format(STORE_TRANSMISSION_MESSAGE, new Object[] {spokeStorePODetails}); final List splitLines = StringUtils.breakLines(storeTransmissionMessage, 80); String messageText = null; MessageTextVO messageTextVO = null; int splitLinesSize = splitLines.size(); for(int index = 0 ; index < splitLinesSize; index++){ messageText = (String) splitLines.get(index); messageTextVO = new MessageTextVO(); messageTextVO.setLOC(storeProfile.getLocation()); messageTextVO.setSequence(new Short((short) (index + 1))); messageTextVO.setText(messageText); messageDto.addMessageTextVO(messageTextVO); } } private void processMultiStoreIfNecessary(PostReceiptsVO vo, Connection conn) throws DAOException { try { MultiStoreProfileVO msVo = ApplicationContext.getInstance() .getProfile(Profile.SERVER, vo.getLoc().intValue()).getMulitStoreProfile(); MultiStoreOrderDAO msdao = new MultiStoreOrderDAO(); if (msdao.isMultiStoreConsolidatedOrderPost( vo.getOrderId(),vo.getLoc()).booleanValue()){ logger.debug("Processsing " + vo.getPoNumber() + " as multi store."); String sqlString = getConsolOrdersSpcString(vo, msVo); logger.debug("UBL SQL is: "+sqlString); createFileActivityRecord(sqlString); logger.debug("SQL sent."); } //catch any exception and log it to prevent exceptions processing //as multistore to inhibit the normal post receipts process. Multiple catches //were used to prevent bubbling a generic exception from this method. } catch (DAOException daoe){ logger.error(MessageFormat.format(PROCESS_MS_FAILURE_TEMPLATE, new Object[]{vo.getOrderId()}),daoe); throw daoe; } } protected String getConsolOrdersSpcString(PostReceiptsVO prVo, MultiStoreProfileVO msVo) { Object[] obs = new Object[] { msVo.getServerCompanyIDNumber(), msVo.getServerStoreIDNumber(), prVo.getPoNumber() }; return MessageFormat.format(SP_CONSOL_ORDERS_TEMPLATE, obs); } /** * This method adds a record to FileActivity, which in turn will add * the sp_consol_orders2 procedure call to T2MSTRANS.DB. */ private void createFileActivityRecord(String spString) throws DAOException { Connection connection = null; Statement stmt = null; StringBuffer sqlFA = new StringBuffer(); sqlFA.append(" INSERT INTO file_activity ( "); sqlFA.append(" loc, ref_file_activity_table_name_id, table_cd_entry, "); sqlFA.append(" ref_file_activity_action_cd, after_value, "); sqlFA.append(" ref_file_activity_program_source_id) "); sqlFA.append(" VALUES ("); sqlFA.append(" 1, 152, 'SP_CONSOL',"); sqlFA.append(" 'A', '" + spString + "',"); sqlFA.append(" 148 )"); try { logger.debug("Creating FileActivity Record, sqlFA= " + sqlFA.toString()); connection = ServerUtil.getConnection(); stmt = connection.createStatement();; stmt.execute(sqlFA.toString()); } catch (SQLException sqlException) { throw new DAOException(sqlException); } finally { JDBCUtil.closeStatement(stmt); ServerUtil.releaseConnection(connection); } } }