# Reading Prophet 21 data with SQL, safely

> Query a Prophet 21 database without risking data or slowing users, with a read-only login, locking on a busy ERP and short starter queries on orders and items.

Source: https://docs.lumina-erp.com/prophet-21/reading-p21-data-with-sql/

**In short.** Use a dedicated read-only login, understand what your queries do to locking on a busy system, switch to the API when you need to write and filter out deleted and other-company rows.

Direct SQL is the fastest way to answer questions about Prophet 21 data, and the fastest way to cause an incident if it is done carelessly. The risks are not exotic. Someone runs an update they meant to be a select, or a heavy report query holds locks while the order desk is trying to save. The setup and habits below make reading P21 data routine.

Everything here is about **reading**. Changing Prophet 21 data should go through the application, its import tools or its APIs, so that business logic and related tables stay consistent.

:::note[Hosted systems]
If your system is hosted, direct database access depends on your hosting agreement. Confirm what is allowed before planning around it.
:::

## Create a dedicated read-only login

Do not query with an administrator account, a shared application account or the account Prophet 21 itself uses. Create a login that can only read, and use it for reporting tools, analysts and ad hoc queries.

A minimal version, run by a database administrator:

```sql
CREATE LOGIN p21_reader WITH PASSWORD = 'use-a-generated-secret-here';

USE [YourP21Database];
CREATE USER p21_reader FOR LOGIN p21_reader;
ALTER ROLE db_datareader ADD MEMBER p21_reader;
```

Notes on doing this well:

- Prefer Windows or directory authentication where your environment supports it, so the password stays out of connection strings.
- Create one login per purpose. A reporting tool, a data warehouse job and a person each get their own. When something misbehaves, you can see which one it was.
- Remember that `db_datareader` reads every table. That includes sensitive data such as payment or payroll-adjacent tables if your install has them. If that is too broad, grant `SELECT` on specific tables or on views you create for reporting.
- Store the credential in a secrets manager, never in a spreadsheet or a report file on a shared drive.

A read-only login removes the worst outcome, an accidental write. It does not remove the second-worst outcome, which is a read that slows everyone down.

## Understand what your queries do to locking

SQL Server's default isolation level, read committed, takes short-lived shared locks as a query reads rows. On a quiet database you never notice. On a busy ERP, a long-running query that scans a large table can block writers, and writers can block your query. To users, that looks like order entry hanging.

You have a few tools, each with a trade-off:

| Approach | What it does | Trade-off |
|---|---|---|
| Keep queries small and selective | Less data read, locks held briefly | Requires filtering on indexed columns |
| Row versioning (read committed snapshot or snapshot isolation) | Readers see a consistent snapshot without blocking writers | A database-level setting that uses tempdb. A DBA decision, made once for the database |
| `NOLOCK` or read uncommitted | Readers take no shared locks | Can return uncommitted rows, skip rows or count rows twice while data moves |
| A reporting copy | Queries run somewhere else entirely | Data is only as fresh as the last copy |

Our guidance:

- For **ad hoc questions**, write selective queries and run them outside peak hours when they are large.
- For **recurring reports and dashboards**, point them at a reporting copy if you have one (a restored backup, a replica or a warehouse).
- Treat **`NOLOCK` as a known compromise**, not a performance switch. It is acceptable for a rough count on a live system. It is not acceptable for anything financial.
- Do not change database-level isolation settings on a production ERP without testing and without your vendor's guidance.

You can check which row-versioning options a database has enabled with a harmless query:

```sql
SELECT name,
       is_read_committed_snapshot_on,
       snapshot_isolation_state_desc
FROM sys.databases
WHERE name = DB_NAME();
```

## Habits that keep queries safe

These habits apply to every query you run against P21, ad hoc or scheduled.

| Habit | Why |
|---|---|
| Always filter out logically deleted rows | Many P21 tables use a flag to mark deleted records rather than removing them |
| Join on the company as well as the key | Multi-company databases share tables |
| Select columns, not `*` | Wide tables are expensive to read in full |
| Put a `TOP` on exploratory queries | Until you know how many rows come back |
| Handle flags defensively | Yes or no columns hold `'Y'` or `'N'`, but a `NULL` can appear. Compare in a way that treats `NULL` deliberately |
| Never leave a transaction open in your query tool | An uncommitted `BEGIN TRAN` holds locks until you close the window |

## Starter queries

These are short, original examples on well-known tables. Column names and flag values can vary between versions, so check them in your own database before relying on the results.

### Open orders for one customer

```sql
DECLARE @customer_id decimal(19, 0) = 100123;

SELECT TOP (200)
       h.order_no,
       h.order_date,
       h.po_no,
       c.customer_name
FROM dbo.oe_hdr AS h
JOIN dbo.customer AS c
  ON c.customer_id = h.customer_id
 AND c.company_id = h.company_id
WHERE h.customer_id = @customer_id
  AND COALESCE(h.delete_flag, 'N') <> 'Y'
  AND COALESCE(h.completed, 'N') <> 'Y'
  AND COALESCE(h.cancel_flag, 'N') <> 'Y'
ORDER BY h.order_date DESC;
```

### Lines on an order with what is still open

```sql
DECLARE @order_no varchar(8) = '1000123';

SELECT l.line_no,
       m.item_id,
       m.item_desc,
       l.qty_ordered,
       l.qty_invoiced,
       l.qty_canceled,
       l.qty_ordered - l.qty_invoiced - l.qty_canceled AS qty_open,
       l.unit_price
FROM dbo.oe_line AS l
JOIN dbo.inv_mast AS m
  ON m.inv_mast_uid = l.inv_mast_uid
WHERE l.order_no = @order_no
  AND COALESCE(l.delete_flag, 'N') <> 'Y'
ORDER BY l.line_no;
```

:::caution[Check the unit of measure basis]
Before using quantities in money calculations, confirm the unit of measure basis for the quantity and price columns in your version. Mixing a quantity in one unit with a price in another is a common source of wrong extended values.
:::

### Stock for an item across locations

```sql
DECLARE @item_id varchar(40) = 'ABC-123';

SELECT il.location_id,
       il.qty_on_hand,
       il.qty_allocated,
       il.qty_on_hand - il.qty_allocated AS qty_free
FROM dbo.inv_mast AS m
JOIN dbo.inv_loc AS il
  ON il.inv_mast_uid = m.inv_mast_uid
WHERE m.item_id = @item_id
  AND COALESCE(m.delete_flag, 'N') <> 'Y'
ORDER BY il.location_id;
```

:::caution[qty_free is not available quantity]
`qty_free` here is an illustration, not the Prophet 21 definition of available quantity. The availability figure P21 shows can account for more than allocations, so do not present this number to users as "available" without checking it against what P21 shows.
:::

## When SQL is the wrong tool

For some needs, a different path is safer than a query.

| Need | Use instead |
|---|---|
| To change data | The application, an import or the API |
| Real-time integration | Polling tables for changes is fragile and puts constant load on the database. See [Prophet 21 integration options](/prophet-21/integration-options/) |
| Numbers finance will sign off | The report finance already trusts, or reconcile your query to it first |

A good pattern is to keep a small library of reviewed queries in version control, each with a short note on what it answers and which version of P21 it was checked against. New questions start from a query that is known to be right.

---

Epicor, Prophet 21, P21 and DynaChange are trademarks or registered trademarks of Epicor Software Corporation registered in the United States and other countries. Kinetic is a trademark of Epicor Software Corporation. Lumina ERP is an independent consultancy and is not affiliated with, sponsored by or endorsed by Epicor.