Oracle Unshipped Orders MCP Server

MCP (Model Context Protocol) server for retrieving unshipped orders from Oracle/Red Prairie Blue Yonder WMS database.

🔒 Security Features

• Environment Variables: All credentials stored in .env (git-ignored)
• Connection Pooling: Efficient database connection management
• No Hardcoded Credentials: Zero credentials in source code
• Oracle Best Practices: Uses oracledb official driver with secure connection handling

🚀 Quick Start

1. Install Dependencies

cd oracle-unshipped-orders-mcp
npm install

2. Configure Database Credentials

IMPORTANT: Create a .env file (this file is git-ignored and will NOT be committed):

cp .env.example .env

Edit .env and add your actual Oracle credentials:

ORACLE_USER=your_actual_username
ORACLE_PASSWORD=your_actual_password
ORACLE_CONNECTION_STRING=your_host:1521/your_service_name

Example:

ORACLE_USER=wmsuser
ORACLE_PASSWORD=SecureP@ssw0rd123
ORACLE_CONNECTION_STRING=db.bolthousefresh.com:1521/REDPRAIRIE

3. Build the Project

npm run build

4. Test the MCP Server

npm test

The server will:

• ✅ Connect to your Oracle database
• ✅ Initialize connection pool
• ✅ Start the MCP server on stdio
• ✅ Be ready to accept tool calls

🛠️ Available Tools

get_unshipped_orders

Retrieves all unshipped orders for a specific customer.

Input:

• stcust (string, required): Ship-to Customer ID

Output:

{
  "success": true,
  "customer": "CUST001",
  "total_orders": 5,
  "orders": [
    {
      "ORDNUM": "ORD12345",
      "WH_ID": "DC01",
      "STCUST": "CUST001",
      "BTCUST": "CUST001",
      "BT_ADR_ID": "ADDR001",
      "ADDDTE": "2026-06-25",
      "EXPSHIP": "2026-07-02",
      "APPTTIME": "10:00",
      "CPONUM": "PO98765",
      "PRTNUM": "PART123",
      "ORDQTY": 100,
      "ORDLIN": 1,
      "INPQTY": 100,
      "PCKQTY": 100,
      "STGQTY": 100,
      "SHPQTY": 0
    }
  ]
}

Query Logic:

• Filters orders with shpqty = 0 (zero shipped quantity)
• Includes orders with dispatch_dte is null (not yet dispatched)
• Excludes alcohol items (non_alc_flg = 0)
• Joins truck dispatch details for expected ship dates and appointment times

📁 Project Structure

oracle-unshipped-orders-mcp/
├── src/
│   └── index.ts          # Main MCP server code
├── dist/                 # Compiled JavaScript (generated)
├── package.json          # Dependencies and scripts
├── tsconfig.json         # TypeScript configuration
├── .env.example          # Template for credentials (safe to commit)
├── .env                  # YOUR ACTUAL CREDENTIALS (git-ignored)
├── .gitignore            # Protects sensitive files
└── README.md             # This file

🔐 Security Checklist

Before committing to GitHub:

• ✅ .env file is listed in .gitignore
• ✅ No credentials in source code
• ✅ Only .env.example (with placeholders) is committed
• ✅ Verify: git status should NOT show .env

🚢 Deployment to Railway

Option 1: Railway CLI

railway login
railway init
railway up

In Railway dashboard, add environment variables:

• ORACLE_USER
• ORACLE_PASSWORD
• ORACLE_CONNECTION_STRING

Option 2: GitHub Integration

1. Push code to GitHub (credentials are git-ignored)
2. Connect Railway to your GitHub repo
3. Add environment variables in Railway dashboard
4. Deploy automatically on push

🧪 Testing Locally with MCP Inspector

npx @modelcontextprotocol/inspector node dist/index.js

This opens a web interface where you can:

• Test the get_unshipped_orders tool
• Inspect requests/responses
• Debug connection issues

📝 SQL Query Details

The MCP executes this query (with customer filter):

SELECT o.ordnum, o.wh_id, o.stcust, o.btcust, o.bt_adr_id,
       o.adddte, dtl.expship, mst.appttime, o.cponum,
       ol.prtnum, ol.ordqty, ol.ordlin,
       sl.inpqty, sl.pckqty, sl.stgqty, sl.shpqty
FROM ord o
INNER JOIN ord_line ol ON o.wh_id = ol.wh_id AND o.ordnum = ol.ordnum
INNER JOIN shipment_line sl ON ol.wh_id = sl.wh_id AND ol.ordnum = sl.ordnum 
                            AND ol.ordlin = sl.ordlin AND ol.ordsln = sl.ordsln
INNER JOIN shipment shp ON sl.wh_id = shp.wh_id AND sl.ship_id = shp.ship_id
INNER JOIN stop stp ON shp.stop_id = stp.stop_id
INNER JOIN car_move car ON stp.car_move_id = car.car_move_id
INNER JOIN trlr trl ON car.trlr_id = trl.trlr_id
INNER JOIN usrtruckdspdetail dtl ON dtl.ordnum = o.ordnum AND dtl.wh_id = o.wh_id
INNER JOIN usrtruckdspmaster mst ON mst.wh_id = dtl.wh_id AND mst.mstordnum = dtl.mstordnum
WHERE trl.dispatch_dte IS NULL
  AND sl.shpqty = 0
  AND ol.non_alc_flg = 0
  AND o.stcust = :stcust
ORDER BY o.ordnum, ol.ordlin

🐛 Troubleshooting

"Database pool not initialized"

• Check .env file exists and has correct credentials
• Verify Oracle connection string format: host:port/service_name

"ORA-12154: TNS:could not resolve the connect identifier"

• Verify ORACLE_CONNECTION_STRING format
• Check network connectivity to Oracle server
• Ensure firewall allows connection on Oracle port (usually 1521)

"ORA-01017: invalid username/password"

• Double-check credentials in .env
• Verify user has SELECT privileges on required tables

No results returned

• Verify customer ID (stcust) exists in database
• Check if customer has unshipped orders matching the criteria
• Test query directly in SQL*Plus or SQL Developer

📚 References

• Model Context Protocol
• Oracle Node.js Driver
• Railway Deployment

👤 Author

Rakesh - Sr. Systems Programmer/Analyst
New Carrot Farms LLC dba Bolthouse Fresh Foods