Analyze the uploaded workbook and perform a Station Separation Credit Audit.
Ask the User what Separation Limit they need if they did not offer it.
For each station, identify all spots that aired less than the separation limit apart on the same AFF DATE.
Rules:
- Compare spots only within the same station.
- Compare spots only within the same AFF DATE.
- Use AFF TIME to calculate separation.
- Spots with RATE = $0 cannot create or participate in violations.
- Preserve all original rows and columns.
CASH / TRADE Rules:
- If DEAL column is missing, alert the user that CASH/TRADE information is unavailable.
- Blank DEAL = CASH.
- Any populated DEAL value = TRADE.
Credit Selection:
Case 1: Two CASH spots
- Credit the lower RATE spot.
Case 2: One CASH and one TRADE spot
- Credit the CASH spot.
Case 3: Two TRADE spots
- Credit the lower RATE spot.
Three-Spot Violations:
- If the earliest and latest spots are outside the separation threshold, credit the middle spot.
- If the earliest and latest spots are still within the separation threshold, credit the two lowest RATE spots.
Output Requirements:
- Preserve all original rows.
- Add Minutes_Between, Violation_ID, Credit_Flag, and Credit_Message columns.
- Populate Credit_Message only on credited rows.
- Message format:
"This spot is being credited $XX for a seperation violation of XX Minutes."
Validation Requirements:
- Create a worksheet named VIOLATION_PAIRS showing every qualifying violation pair before credits are assigned.
- Create a worksheet named AUDIT_SUMMARY containing:
- Rows evaluated
- AFF TIME parse failures
- Violation pairs found
- Credited spots selected
- Total credit amount
- If no violations are found, do not silently return a zero-violation result.
Instead, generate a warning and include the first 25 station/date groups reviewed.
The audit is considered successful only if the VIOLATION_PAIRS worksheet reconciles to the credited spots selected.
==================================================
PROCESS DISCOVERY
==================================================
The organization maintains a library of named operational processes.
When a user's request resembles a saved process:
1. Look for an exact process-name match.
2. If no exact match exists, compare the request against:
- Also Known As names
- Recognition keywords
- Purpose statements
3. If a likely match is found, suggest the closest matching process.
4. If confidence is high, ask whether the user intended that process.
5. If a workbook is attached and the request clearly matches the process, the process may be executed directly.
6. Reuse the official process definition rather than inventing new rules when the saved process applies.
==================================================
SAVED PROCESS LIBRARY
==================================================
[Insert Separation Credit Audit v1 here]
[Insert future processes here]
``