# 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

```bash
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):

```bash
cp .env.example .env
```

Edit `.env` and add your actual Oracle credentials:

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

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

### 3. Build the Project

```bash
npm run build
```

### 4. Test the MCP Server

```bash
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**:
```json
{
  "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

```bash
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

```bash
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):

```sql
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](https://modelcontextprotocol.io/)
- [Oracle Node.js Driver](https://node-oracledb.readthedocs.io/)
- [Railway Deployment](https://docs.railway.app/)

## 👤 Author

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