Package com.tbg.wms.db

Class OracleDbQueryRepository

java.lang.Object
com.tbg.wms.db.OracleDbQueryRepository
All Implemented Interfaces:
DbQueryRepository
public final class OracleDbQueryRepository extends Object implements DbQueryRepository
Oracle implementation of DbQueryRepository.

This class executes Oracle WMS queries against the WMSP schema to retrieve shipment data with all related information (orders, lines, pallets, lots, etc.).

Uses prepared statements to prevent SQL injection and proper resource management with try-with-resources.

Database: Oracle WMS at site-specific location (e.g., TBG3002 at 10.19.68.61:1521/WMSP) Schema: WMSP (schema name is prepended to all table references) User: RPTADM (read-only reporting user)

  • Constructor Details

    • OracleDbQueryRepository

      public OracleDbQueryRepository(DataSource dataSource)
      Creates a new OracleDbQueryRepository.
      Parameters:
      dataSource - the connection pool providing database connections
      Throws:
      IllegalArgumentException - if dataSource is null

  • Method Details

    • findShipmentWithLpnsAndLineItems

      public Shipment findShipmentWithLpnsAndLineItems(String shipmentId)
      Description copied from interface: DbQueryRepository
      Retrieves a complete shipment with all LPNs and line items.

      This is a read-only operation that gathers: - Shipment header information (destination, carrier, dates) - All LPNs (pallets) tied to this shipment - All line items within each LPN - Staging location for routing decisions

      Specified by:
      findShipmentWithLpnsAndLineItems in interface DbQueryRepository
      Parameters:
      shipmentId - the shipment identifier
      Returns:
      the complete shipment with all associated data

    • shipmentExists

      public boolean shipmentExists(String shipmentId)
      Description copied from interface: DbQueryRepository
      Validates that a shipment exists and has at least one LPN.

      Lightweight check to verify shipment is printable without fetching all associated data.

      Specified by:
      shipmentExists in interface DbQueryRepository
      Parameters:
      shipmentId - the shipment identifier
      Returns:
      true if shipment exists and has at least one LPN, false otherwise

    • getStagingLocation

      public String getStagingLocation(String shipmentId)
      Description copied from interface: DbQueryRepository
      Returns the staging location for a shipment.

      Used for printer routing decisions. All LPNs in a shipment should be in the same staging location.

      Specified by:
      getStagingLocation in interface DbQueryRepository
      Parameters:
      shipmentId - the shipment identifier
      Returns:
      the staging location (e.g., "ROSSI", "OFFICE"), or null if not found

    • findShipmentSkuFootprints

      public List<ShipmentSkuFootprint> findShipmentSkuFootprints(String shipmentId)
      Description copied from interface: DbQueryRepository
      Retrieves shipment-level SKU totals with footprint maintenance data.

      Data is sourced from shipment lines plus PRTFTP / PRTFTP_DTL to provide units-per-case, units-per-pallet, and pallet dimensions for planning.

      Specified by:
      findShipmentSkuFootprints in interface DbQueryRepository
      Parameters:
      shipmentId - the shipment identifier
      Returns:
      one row per SKU in the shipment

    • findCarrierMoveStops

      public List<CarrierMoveStopRef> findCarrierMoveStops(String carrierMoveId)
      Description copied from interface: DbQueryRepository
      Resolves shipment rows for a carrier move using stop assignments.

      Rows are expected to be sorted by stop sequence and then shipment ID.

      Specified by:
      findCarrierMoveStops in interface DbQueryRepository
      Parameters:
      carrierMoveId - carrier move identifier
      Returns:
      stop-mapped shipment rows for the carrier move

    • findRailStopsByTrainId

      public List<RailStopRecord> findRailStopsByTrainId(String trainId)
      Description copied from interface: DbQueryRepository
      Resolves train/load rows for rail label generation from live WMS rail receive tables.
      Specified by:
      findRailStopsByTrainId in interface DbQueryRepository
      Parameters:
      trainId - full train ID (example: JC03032026)
      Returns:
      flattened rail stop rows with short-code items

    • findRailFootprintsByShortCode

      public Map<String,List<RailFootprintCandidate>> findRailFootprintsByShortCode(List<String> shortCodes)
      Description copied from interface: DbQueryRepository
      Resolves WMS footprint candidates keyed by short code.

      Some short codes may map to multiple item numbers; callers are expected to apply deterministic conflict handling and diagnostics.

      Specified by:
      findRailFootprintsByShortCode in interface DbQueryRepository
      Parameters:
      shortCodes - short code values from rail rows
      Returns:
      map of short code to candidate rows

    • close

      public void close()
      Description copied from interface: DbQueryRepository
      Closes the repository and releases any resources.

      Implementors must ensure underlying connections are closed properly.

      Specified by:
      close in interface DbQueryRepository