Sponsored Research Grant Reconciliation SQL Query Specification

Role: Principal Research Analytics Architect with deep expertise in sponsored project financial compliance and audit data modeling.

Context

• Sponsoring institution: {{research_institution}}
• Granting body / sponsor: {{funding_agency}}
• Active reporting period: {{grant_fiscal_year}}
• Regulatory audit standard: {{compliance_framework}}
• Target relational engine: {{sql_engine}}
• Expense transaction level: {{ledger_granularity}}

Task

Construct a rigorous SQL specification that details ledger reconciliation views, effort certification joins, and variance detection logic for sponsored academic research grants at {{research_institution}} under {{compliance_framework}} guidelines.

Method

1. Identify transaction boundaries across general ledger and sponsored program sub-ledgers for {{grant_fiscal_year}}.
2. Normalize project expenditure line items down to {{ledger_granularity}} granularity.
3. Construct temporal join predicates aligning faculty effort percentages with payroll disbursement periods.
4. Define direct versus indirect cost calculations adhering to {{funding_agency}} recovery guidelines.
5. Build CTE-driven reconciliation views to flag unallowable expense codes and milestone budget overruns.
6. Apply window functions to track cumulative burn rates against awarded milestone budgets.
7. Optimize query execution for {{sql_engine}} using partition pruning on grant account keys.
8. Establish deterministic audit logging views that record historical modifications to allocation ratios.

Constraints

• MUST use standard, deterministic SQL syntax compatible with {{sql_engine}}.
• MUST calculate variance down to the exact monetary unit specified by {{ledger_granularity}}.
• MUST NOT drop or coalesce historical adjustment records that provide necessary audit trails.
• All CTEs and interim temp tables MUST include explicit column typing and zero-safe arithmetic.

Output format

1. Architectural Scope & Audit Objective (1 concise section)
2. Source Mapping & Entity Relationship Table (Markdown table of entities and foreign keys)
3. Production SQL View Definitions (Commented SQL code blocks for ledger reconciliation and effort tracking)
4. Audit Trail & Variance Validation Suite (3-5 validation queries with expected outputs)

Self-review

• Ensure all 6 context variables appear correctly in the prompt body.
• Verify that calculations for direct and indirect costs comply with {{funding_agency}} requirements.
• Check that audit immutability constraints are satisfied across all view definitions.