Result stored procedures: jobStatus filter uses bitwise subset match instead of exact match

[Giannoudis]

Summary

The payroll result stored procedures filter by @jobStatus using a bitwise subset
comparison rather than an exact comparison. Requesting a single status (e.g.
Complete) therefore also returns results from every job whose status value is a
bit-subset of the requested value. This makes it impossible to retrieve "only
completed" (or "only forecast") results, which reporting consumers require.

Affected code

PayrunJobStatus is a sequential (non-[Flags]) enum
(PayrollEngine.Core/Core/PayrunJobStatus.cs):

Value	Name
0	Draft
1	Release
2	Process
3	Complete
4	Forecast
5	Abort
6	Cancel

The filter expression (SQL Server form):

AND (@jobStatus IS NULL
     OR r.[PayrunJobId] IN (
         SELECT pj.[Id] FROM dbo.[PayrunJob] pj
         WHERE pj.[JobStatus] & @jobStatus = pj.[JobStatus]))

MySQL form (identical semantics, parameter p_jobStatus):

... WHERE (pj.JobStatus & p_jobStatus) = pj.JobStatus

Because & evaluates a subset relation on sequential integers:

• @jobStatus = Complete (3 = 0b011) matches statuses {Draft, Release, Process, Complete}
• @jobStatus = Forecast (4 = 0b100) matches statuses {Draft, Forecast}

Abort (5) and Cancel (6) are correctly excluded; the leak is limited to the
in-progress legal statuses (Draft/Release/Process) and, for forecast queries, to
Draft legal rows.

Impact

The result procedures return only the most recent row per (key, period) via
ROW_NUMBER() OVER (PARTITION BY <key>, Start ORDER BY Created DESC, Id DESC).
Combined with the permissive status filter, an in-progress correction job
(Draft/Release/Process) for a period overrides the last completed job, because it
has a newer Created. A consumer requesting jobStatus = Complete can therefore
receive preliminary, not-yet-finalized values while a correction run is open. The
analogous leak applies to forecast queries (jobStatus = Forecast).

Affected stored procedures

The pattern is NOT limited to the consolidated procedures. It is also present in the
non-consolidated single-period procedures (twice each, in separate query branches).

SQL Server (Persistence/Persistence.SqlServer/StoredProcedures/):

• GetConsolidatedWageTypeResults.sql (1x)
• GetConsolidatedWageTypeCustomResults.sql (1x)
• GetConsolidatedCollectorResults.sql (1x)
• GetConsolidatedCollectorCustomResults.sql (1x)
• GetConsolidatedPayrunResults.sql (1x)
• GetWageTypeResults.sql (2x)
• GetWageTypeCustomResults.sql (2x)
• GetCollectorResults.sql (2x)
• GetCollectorCustomResults.sql (2x)

MySQL (Persistence/Persistence.MySql/StoredProcedures/):

• GetConsolidatedWageTypeResults.mysql.sql
• GetConsolidatedWageTypeCustomResults.mysql.sql
• GetConsolidatedCollectorResults.mysql.sql
• GetConsolidatedCollectorCustomResults.mysql.sql
• GetConsolidatedPayrunResults.mysql.sql
• GetWageTypeResults.mysql.sql
• GetWageTypeCustomResults.mysql.sql
• GetCollectorResults.mysql.sql
• GetCollectorCustomResults.mysql.sql

Generated/merged build scripts that must be regenerated in sync:

• Database/Create-Model.sql
• Database/Create-Model.mysql.sql
• Database/MySql-Routines.merged.sql

Historical migration snapshots (Database/History/**) must NOT be modified.

Proposed fix

Replace the bitwise subset comparison with an exact comparison in every affected
procedure (both branches in the non-consolidated ones):

-- SQL Server: before
WHERE pj.[JobStatus] & @jobStatus = pj.[JobStatus]
-- SQL Server: after
WHERE pj.[JobStatus] = @jobStatus

-- MySQL: before
WHERE (pj.JobStatus & p_jobStatus) = pj.JobStatus
-- MySQL: after
WHERE pj.JobStatus = p_jobStatus

No change is needed in the API/parameter binding layer; PayrunJobStatus? is already
passed through correctly.

Considerations

• The same procedures are used by payrun-time period-total (YTD) calculations, not
  only by reports. The change must be verified against that path.
• SQL Server and MySQL variants plus the merged Create-Model build scripts must be
  updated in sync.
• Add an integration test asserting that an open Draft correction does not override
  the last Completed result when querying jobStatus = Complete.