# Product requirements — PernodIntegration

Evidence date: 2026-10-07.

Source: `git@bitbucket.org:overdrive/pernodintegration.git` at `ecbc272` (2026-08-27) on `main`.

Requirements below are recovered from the implementation and from `docs/integration-flow.md` and `docs/tests.md`. Each one is labelled:

- **Documented** — stated in those markdown files or in a code comment that describes intended behaviour.
- **Implemented** — present in code that runs.
- **Inferred** — a reasonable reading of that behaviour, not written down as a requirement.
- **Unknown** — not established.

## Product purpose

Move unresolved Pernod Ricard staff orders from `PSOrder` into JD Edwards by calling orchestrator `COR55ZA0012_AddEmployeeSales`, and stop sending orders that are from a previous calendar month or that would keep a person's failed bottles above a 12-bottle month.

Provenance: **Documented** in `docs/integration-flow.md` and **Implemented** in `PernodIntegration/Module1.vb`.

The job does not take orders from a person. The staff portal creates the rows. Provenance: **Documented** in `docs/integration-flow.md`. The portal repository is `pernodstaff`. Provenance: Silicon Ark `pernodstaff`, cross-referenced, not re-read as source in this pass.

## Users and interactions

| Actor | Interaction |
|---|---|
| Operator | Starts `PernodIntegration.exe`. The process prints progress and, at the end, waits on `Console.ReadLine()`. There is no other UI. |
| Staff member | Does not use this program. Their order is already a row in `ORDER_HEADER` / `ORDER_DETAIL`. |
| Staff portal | Writes orders at status `99`. Not implemented in this repository. |
| Azure AD | Issues the access token used as a bearer. |
| JD Edwards via the Pernod Ricard API | Accepts the AIS token request and the orchestrator post. |
| SQL Server `PSOrder` | Source of orders and destination of status updates and `ERROR_LOG` rows. |

No login, role, or permission check exists inside this executable. Whoever can run the exe and read the config can send every status-`99` order the connection string can see. Provenance: **Implemented**.

## Workflows

### Run

1. `Main` calls `GetJdeToken`.
2. That calls `GetOAuthToken` (form POST: `client_id`, `scope`, `client_secret`, `grant_type`).
3. On an OAuth HTTP 2xx it POSTs the JDE token URL with the bearer, subscription key, header `jde-AIS-Auth-Device: BRIDGE`, and a JSON body of username, password, environment, role, and device name.
4. The AIS token is `userInfo.token`.
5. `Main` always calls `GetOrders2Send`, including when step 1 returned nothing.
6. Expire prior-month status `99` to `22`.
7. For each distinct `userid` still on status `99`, cancel oldest failed orders that make the month exceed 12 bottles.
8. Load every remaining status `99` header and call `CreateOrder`.
9. Print `Done` and wait for a line of input.

Provenance: **Implemented**. Steps 6–8 and the "always send" behaviour are also **Documented** in `docs/integration-flow.md`.

If expiry's SQL statement throws, the error is logged and the run continues. Prior-month status `99` rows are then still eligible for `CreateOrder`. Provenance: **Implemented**. The flow doc's intent is that leftover prior-month rows are never posted. That intent does not hold when the expiry statement fails. **Conflict — Requires Confirmation.**

### One order

1. Load `ORDER_DETAIL` for the order id.
2. Build JSON: AIS `token`, `Business Unit`, `Long Address Number`, and `GridIn_1_2` lines (`Item Number`, `Quantity Ordered`, `Ln Ty` = `S`).
3. POST the order URL with a new OAuth bearer, the same subscription key, and `jde-AIS-Auth-Device: BRIDGE`.
4. HTTP 2xx: set header status to `20`. The JSON body is printed and not interpreted.
5. HTTP non-2xx: one `ERROR_LOG` row with the order id, then the catch does not write a second row. Status stays `99` unless the overflow step then sets `22`.
6. Any other exception: one `ERROR_LOG` row, then overflow cancel for that user when `userid` > 0.

Provenance: **Implemented** and **Documented** in `docs/integration-flow.md`.

HTTP 2xx with a business error inside the body is still stored as `20`. Provenance: **Documented** and **Implemented** (no body check).

## Functional requirements

### FR-001 — Select work

The run sends headers whose `status` is `99` after expiry and the pre-send overflow pass.

| | |
|---|---|
| Trigger | Process start |
| Input | `ORDER_HEADER` rows |
| Behaviour | `SELECT * FROM [ORDER_HEADER] WHERE [status] = '99'` with no `ORDER BY` |
| Output | One `CreateOrder` call per row |
| Evidence | `Module1.GetOrders2Send` |
| Provenance | **Implemented** |
| Confidence | High |

The send order is whatever SQL Server returns. Provenance: **Inferred** from the missing `ORDER BY`.

### FR-002 — Tokens

The JDE call is authorised with an Azure AD access token, then an AIS token.

| | |
|---|---|
| Trigger | `Main` |
| Input | App settings listed in technical documentation |
| Behaviour | OAuth form POST, then JSON POST to the JDE token URL. AIS token is `userInfo.token` |
| Failure | Console text plus `ERROR_LOG` operation `GetAccessToken` or `GetJdEdwardsToken`. Return nothing. The send loop still runs |
| Evidence | `GetOAuthToken`, `GetJdeToken`, `Main` |
| Provenance | **Implemented**. The continue-after-failure path is **Documented** |
| Confidence | High |

The order POST asks for a new OAuth token and puts the original AIS token in the body. Provenance: **Documented** and **Implemented**. If that second OAuth call fails, `GetOAuthToken` returns nothing, logs `GetAccessToken` without an order id, and the order POST still proceeds with an empty bearer. Provenance: **Inferred** from string interpolation of the return value. **Requires Confirmation** that this is acceptable.

`OAuthScope` and `OAuthGrantType` are not in the committed application config. A run that uses that file sends empty scope and grant type. The test config sets grant type `client_credentials`. Provenance: **Implemented**. **Requires Confirmation** for any deployed config that is not the committed file.

The OAuth request also sends a hardcoded `Cookie` header. Provenance: **Implemented**. It is not described in `docs/integration-flow.md`.

### FR-003 — Orchestrator payload

Each order is one JSON POST.

| Field | Source |
|---|---|
| `token` | AIS token from the start of the run |
| `Business Unit` | `ORDER_HEADER.BusinessUnit` |
| `Long Address Number` | `ORDER_HEADER.CustomerId` |
| `GridIn_1_2[].Item Number` | `ORDER_DETAIL.clientcode`, trimmed |
| `GridIn_1_2[].Quantity Ordered` | `ORDER_DETAIL.Quantity`, trimmed string |
| `GridIn_1_2[].Ln Ty` | Constant `S` |

Headers on that POST: `Authorization: Bearer` plus the new OAuth token, `Ocp-Apim-Subscription-Key`, `jde-AIS-Auth-Device: BRIDGE`.

Provenance: **Implemented** (`OrderClass.vb`, `CreateOrder`). The flow doc names the same business fields. What `S`, `CustomerId`, and `clientcode` mean in JDE and in the product catalogue is **Unknown / Requires Confirmation**. Silicon Ark `pernodstaff` maps catalogue lines through `DSVCode`; this repository does not say `clientcode` is that code.

An order with no detail rows still posts, with an empty grid. Provenance: **Inferred** from the loop adding nothing and still calling POST.

### FR-004 — Success status

HTTP 2xx sets `ORDER_HEADER.status` to `20` for that order id.

| | |
|---|---|
| Evidence | `CreateOrder` update statement; test `Success_SetsStatus20_AndPostsOnce` |
| Provenance | **Implemented** and **Documented** |
| Confidence | High |

`20` is not changed by expiry. Provenance: **Documented** and **Implemented** (expiry updates only status `99`).

### FR-005 — Failure stays retryable

A failed send that is not cancelled by the bottle rule stays `99` and has an `ERROR_LOG` row with `orderid`.

| | |
|---|---|
| Evidence | `CreateOrder` catch path; test `HttpFailure_LeavesStatus99_AndLogsError` |
| Provenance | **Implemented** and **Documented** |
| Confidence | High |

Non-2xx is logged once. `EnsureSuccessStatusCode` then throws, and the catch does not insert a second row. Provenance: **Documented** and **Implemented**.

A failed insert into `ERROR_LOG` is swallowed. Provenance: **Documented** and **Implemented** (empty `Catch` in `LogError`).

### FR-006 — Prior-month expiry

At the start of every run, status `99` whose `Orderdate` is before the first day of the current calendar month becomes `22`.

| | |
|---|---|
| Period | `DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1)` on the SQL Server clock |
| Evidence | `ExpirePriorPeriodUnresolvedOrders`; test `PriorPeriodUnresolved_SetsStatus22_AndDoesNotPost` |
| Provenance | **Implemented** and **Documented** |
| Confidence | High |

The month boundary uses SQL `GETDATE()`, not the machine clock of the console. Time zone is whatever the database uses. **Unknown / Requires Confirmation.**

### FR-007 — Monthly bottle cap for failed orders

After expiry, and again after each failed `CreateOrder` for a user id greater than 0, the job may set the oldest failed headers to `22`.

| Term | Rule in this job |
|---|---|
| Cap | 12. Constant `MonthlyBottleCap` |
| Bottles | `SUM(ORDER_DETAIL.Quantity)` |
| Period | `Orderdate` in `[MonthStart, NextMonthStart)` |
| Counted | Same user, this month, status not `22`, and no `ERROR_LOG` row for the order id |
| Failed | Same user, this month, status `99`, and at least one `ERROR_LOG` row. Oldest `Orderdate`, then `Orderid` |
| Action | If counted + failed > 12, set the oldest failed headers to `22` until the remainder would fit |

Provenance: **Implemented** and **Documented**. Tests cover 8+6, 8+3, and 4+4+6.

The flow doc also describes counted bottles as pending `99` and successful `20`. The SQL is wider: any status other than `22`, when there is no error row. Provenance: **Documented** interpretation versus **Implemented** predicate. **Requires Confirmation** if other status values exist.

`ERROR_LOG` is matched on `orderid` only. It is not filtered to component `PernodIntegration` or operation `CreateOrder`. This job's own `LogError` always writes component `PernodIntegration`, and `CreateOrder` failures write that operation. A row from another component would still mark the order failed here. Provenance: **Implemented**. The staff-portal rule recorded in Silicon Ark `pernodstaff` BR-023 filters component and operation. **Conflict — Requires Confirmation.**

`userid` null or not a positive integer skips overflow for that row. The row can still be posted. Provenance: **Implemented** (`ParseUserId`).

### FR-008 — Error log contents

`ERROR_LOG` columns written: `component` = `PernodIntegration`, `operation`, `message` (max 4000), `exceptionmessage` (max 2000), `stacktrace`, `useremail` (always null), `orderid` (on `CreateOrder` when the id parses as a positive integer).

Secrets in message and stack are redacted: JSON `token`, JSON `access_token`, and `Bearer` plus a token. Provenance: **Implemented** and **Documented**.

`docs/integration-flow.md` says long fields are truncated. Message and exception message are truncated. Stack trace is redacted and not truncated in code. The test column is `VARCHAR(MAX)`. Provenance: **Documented** versus **Implemented**.

Operations used: `GetAccessToken`, `GetJdEdwardsToken`, `ExpirePriorPeriodUnresolvedOrders`, `CancelOverflowFailedOrders`, `CreateOrder`.

## Integrations

| System | Direction | Trigger | Data | Auth | Failure |
|---|---|---|---|---|---|
| Azure AD token endpoint | Out | Every run, and again on each order POST | Form client id, scope, secret, grant type | Client secret in the form. A Cookie header is also sent | `ERROR_LOG` `GetAccessToken`, empty token |
| JDE token URL | Out | After OAuth succeeds | JSON username, password, environment, role, device name | Bearer plus `Ocp-Apim-Subscription-Key`, device header `BRIDGE` | `ERROR_LOG` `GetJdEdwardsToken`, empty AIS token |
| Orchestrator `COR55ZA0012_AddEmployeeSales` | Out | Each remaining status `99` | Order JSON above | New OAuth bearer, subscription key, AIS token in the body | Status stays `99` or becomes `22`; `ERROR_LOG` `CreateOrder` |
| SQL Server `PSOrder` | Read and write | Whole run | Headers, lines, error log | SQL login in the connection string | Expiry and overflow errors are logged and the run continues. A header/detail read failure fails that order's `CreateOrder` |

Committed hosts: `login.microsoftonline.com` and `api-staging.pernod-ricard.io`. Provenance: **Implemented** in config. Production hosts are **Unknown / Requires Confirmation**.

The test stub answers `jde-stub.invalid` and refuses to treat `pernod-ricard.io` as a successful contact. Provenance: **Implemented** in `PernodIntegration.Tests`.

## Constraints

- .NET Framework 4.7.2 console executable. Single instance is off (`Application.myapp`).
- Bottle cap 12 and calendar month are fixed in code and SQL, not configuration.
- The job does not create orders, send email, or enforce the portal cutoff time. Those belong to the staff portal. Provenance: **Documented** for the cap-at-cart statement; cutoff is not mentioned in this repository.
- No retry library, queue, or delay. The next run is the retry, for rows that are still `99`.
- `Console.ReadLine()` after `Done` keeps the process alive until stdin provides a line. Provenance: **Implemented**. Unsuitable for an unattended exit unless the host closes stdin. **Requires Confirmation.**

## Non-functional notes recovered from the repo

- SonarQube project key `overdrive_pernodintegration_2e67c903-cb43-492a-b11d-72f12b50a5bd`. The pipeline does not compile or run tests.
- Tests are MSTest 3.6.3 on `net472`. Nine methods. A run creates and later drops LocalDB database `PernodIntegrationTests`, and writes a gitignored markdown report under `docs/test-reports/`.
- `InternalsVisibleTo("PernodIntegration.Tests")` exposes friend members (`GetOrders2Send`, `SelectOldestFailedOrdersToCancel`, `HttpMessageHandler`).
- Detail select and the status-`20` update concatenate the order id into SQL text. Overflow updates use parameters. Provenance: **Implemented**.
- Credentials and a machine-specific connection string are committed. See technical documentation. Values are not repeated here.