Implementation Guide: Auto-Track CDRL Deadlines & Grant Reporting — Trigger Internal Review Workflows & Draft Progress Reports

# Create CDRL tracking list via PnP PowerShell (GCC High)
Connect-PnPOnline -Url "https://[tenant].sharepoint.us/sites/ContractsManagement" -UseWebLogin

# Create the CDRL tracking list
New-PnPList -Title "CDRL Deliverable Tracker" -Template GenericList

# Add required columns
$list = "CDRL Deliverable Tracker"

Add-PnPField -List $list -DisplayName "Contract Number" -InternalName "ContractNumber" -Type Text -Required
Add-PnPField -List $list -DisplayName "Contract Title" -InternalName "ContractTitle" -Type Text
Add-PnPField -List $list -DisplayName "CDRL Item Number" -InternalName "CDRLItem" -Type Text -Required
Add-PnPField -List $list -DisplayName "DID Number" -InternalName "DIDNumber" -Type Text
Add-PnPField -List $list -DisplayName "DID Title" -InternalName "DIDTitle" -Type Text
Add-PnPField -List $list -DisplayName "Frequency" -InternalName "Frequency" -Type Choice `
    -Choices @("One Time","Monthly","Quarterly","Semi-Annual","Annual","Event-Driven","As Required")
Add-PnPField -List $list -DisplayName "Due Date" -InternalName "DueDate" -Type DateTime -Required
Add-PnPField -List $list -DisplayName "Distribution" -InternalName "Distribution" -Type Text
Add-PnPField -List $list -DisplayName "Government Customer" -InternalName "GovCustomer" -Type Text
Add-PnPField -List $list -DisplayName "Contracting Officer" -InternalName "CO" -Type Text
Add-PnPField -List $list -DisplayName "COR Name" -InternalName "COR" -Type Text
Add-PnPField -List $list -DisplayName "COR Email" -InternalName "COREmail" -Type Text
Add-PnPField -List $list -DisplayName "Internal Owner" -InternalName "InternalOwner" -Type User
Add-PnPField -List $list -DisplayName "Status" -InternalName "DeliverableStatus" -Type Choice `
    -Choices @("Not Started","In Progress","In Review","Delivered","Accepted","Late","Waived")
Add-PnPField -List $list -DisplayName "Delivery Method" -InternalName "DeliveryMethod" -Type Choice `
    -Choices @("CARTS","WAWF","Email to COR","SharePoint","Other")
Add-PnPField -List $list -DisplayName "Draft Location" -InternalName "DraftLocation" -Type URL
Add-PnPField -List $list -DisplayName "Delivery Confirmation" -InternalName "DeliveryConfirmation" -Type Text
Add-PnPField -List $list -DisplayName "Government Acceptance Date" -InternalName "AcceptanceDate" -Type DateTime
Add-PnPField -List $list -DisplayName "Notes" -InternalName "Notes" -Type Note

# Add calculated columns for days remaining and status color
Add-PnPFieldFromXml -List $list -FieldXml @"
<Field Type='Calculated' Name='DaysRemaining' DisplayName='Days Remaining'
  ResultType='Number' ReadOnly='TRUE'>
  <Formula>=IF([DeliverableStatus]="Delivered",0,INT([DueDate]-TODAY()))</Formula>
  <FieldRefs>
    <FieldRef Name='DueDate'/>
    <FieldRef Name='DeliverableStatus'/>
  </FieldRefs>
</Field>
"@

Add-PnPFieldFromXml -List $list -FieldXml @"
<Field Type='Calculated' Name='AlertLevel' DisplayName='Alert Level'
  ResultType='Text' ReadOnly='TRUE'>
  <Formula>=IF([DeliverableStatus]="Delivered","Complete",IF(INT([DueDate]-TODAY())&lt;0,"OVERDUE",IF(INT([DueDate]-TODAY())&lt;=5,"RED",IF(INT([DueDate]-TODAY())&lt;=15,"AMBER",IF(INT([DueDate]-TODAY())&lt;=30,"YELLOW","GREEN")))))</Formula>
  <FieldRefs>
    <FieldRef Name='DueDate'/>
    <FieldRef Name='DeliverableStatus'/>
  </FieldRefs>
</Field>
"@

Write-Host "CDRL Tracker list created successfully."

# Create grant reporting tracker
New-PnPList -Title "Grant Reporting Tracker" -Template GenericList

$glist = "Grant Reporting Tracker"

Add-PnPField -List $glist -DisplayName "Award Number" -InternalName "AwardNumber" -Type Text -Required
Add-PnPField -List $glist -DisplayName "Award Title" -InternalName "AwardTitle" -Type Text
Add-PnPField -List $glist -DisplayName "Federal Agency" -InternalName "FederalAgency" -Type Text
Add-PnPField -List $glist -DisplayName "CFDA/Assistance Listing Number" -InternalName "CFDANumber" -Type Text
Add-PnPField -List $glist -DisplayName "Award Amount" -InternalName "AwardAmount" -Type Currency
Add-PnPField -List $glist -DisplayName "Period of Performance Start" -InternalName "POPStart" -Type DateTime
Add-PnPField -List $glist -DisplayName "Period of Performance End" -InternalName "POPEnd" -Type DateTime
Add-PnPField -List $glist -DisplayName "Report Type" -InternalName "ReportType" -Type Choice `
    -Choices @("SF-PPR (Progress)","SF-425 (Financial)","RPPR (NIH/NSF)","Performance Report","Final Report","Invention Disclosure","Other")
Add-PnPField -List $glist -DisplayName "Reporting Period" -InternalName "ReportingPeriod" -Type Text
Add-PnPField -List $glist -DisplayName "Due Date" -InternalName "DueDate" -Type DateTime -Required
Add-PnPField -List $glist -DisplayName "Submission Portal" -InternalName "Portal" -Type Choice `
    -Choices @("Grants.gov","Research.gov (NSF)","eRA Commons (NIH)","Grants Solutions","Email to GPO","Other")
Add-PnPField -List $glist -DisplayName "Grants Program Officer" -InternalName "GPO" -Type Text
Add-PnPField -List $glist -DisplayName "GPO Email" -InternalName "GPOEmail" -Type Text
Add-PnPField -List $glist -DisplayName "Internal PI/PM" -InternalName "InternalPM" -Type User
Add-PnPField -List $glist -DisplayName "Status" -InternalName "ReportStatus" -Type Choice `
    -Choices @("Not Started","Data Collection","Draft in Progress","Under Review","Submitted","Accepted","Late","Extension Granted")
Add-PnPField -List $glist -DisplayName "Extension Granted Until" -InternalName "ExtensionDate" -Type DateTime
Add-PnPField -List $glist -DisplayName "Draft Location" -InternalName "DraftLocation" -Type URL
Add-PnPField -List $glist -DisplayName "Submission Confirmation Number" -InternalName "ConfirmationNumber" -Type Text

Write-Host "Grant Reporting Tracker created successfully."

# Generates SF-PPR and RPPR narrative sections from program status inputs

# grant_progress_report_generator.py
# Generates SF-PPR and RPPR narrative sections from program status inputs

from openai import AzureOpenAI
import os, json, datetime

client = AzureOpenAI(
    azure_endpoint=os.environ["AZURE_OPENAI_ENDPOINT"],
    api_key=os.environ["AZURE_OPENAI_KEY"],
    api_version="2024-08-01-preview"
)

SF_PPR_PROMPT = """You are a federal grant reporting specialist. Generate a draft
SF-PPR (Standard Form - Performance Progress Report) narrative from the following
program status inputs.

The SF-PPR requires reporting on:
1. Comparison of actual accomplishments with established goals
2. Reasons for slippage if goals were not met
3. Objectives and timetable for the next reporting period
4. Any changes in key project personnel, approach, or scope

GRANT INFORMATION:
Award Number: {award_number}
Award Title: {award_title}
Federal Agency: {federal_agency}
Reporting Period: {reporting_period_start} to {reporting_period_end}
Total Award Amount: {award_amount}
Period of Performance: {pop_start} to {pop_end}

PROGRAM STATUS INPUTS (provided by PI/PM):
Accomplishments This Period: {accomplishments}
Milestones Completed: {milestones}
Milestones Missed (with reasons): {missed_milestones}
Expenditures This Period: {expenditures}
Cumulative Expenditures: {cumulative_expenditures}
Planned Activities Next Period: {next_period_plans}
Personnel Changes: {personnel_changes}
Scope or Approach Changes: {scope_changes}
Issues or Challenges: {issues}
Publications or Outputs: {publications}

Generate a complete SF-PPR narrative with these sections:

## SECTION 1: COMPARISON OF ACTUAL ACCOMPLISHMENTS TO GOALS
[Narrative comparing what was accomplished against the approved project timeline and objectives.
Use specific, measurable language. Cite milestone numbers if the award has a milestone schedule.]

## SECTION 2: REASONS FOR SLIPPAGE (IF APPLICABLE)
[If any milestones were missed or are behind schedule, provide honest explanations.
Describe corrective actions being taken. If all milestones are on track, state clearly.]

## SECTION 3: OBJECTIVES AND TIMETABLE — NEXT REPORTING PERIOD
[Describe specific, measurable objectives and activities planned for the next reporting period.
Include target completion dates.]

## SECTION 4: SPECIAL REPORTING REQUIREMENTS (IF APPLICABLE)
[Address any grant-specific reporting requirements — new inventions, human subjects updates,
data management plan updates, subrecipient monitoring, etc.]

NOTES FOR PI/PM REVIEW:
- Verify all milestone numbers and dates against the approved project timeline
- Confirm all cited publications include correct citation information
- Verify expenditure figures against financial records before submission
- [DATA REQUIRED] flags indicate where specific data was not provided

[DRAFT — AI GENERATED — REQUIRES PI/PM REVIEW, AUTHORIZED ORGANIZATIONAL REPRESENTATIVE APPROVAL, AND FINANCIAL CERTIFICATION BEFORE SUBMISSION]"""

def generate_sf_ppr(grant_data: dict, status_inputs: dict) -> str:
    """Generate a draft SF-PPR narrative."""

    combined_data = {**grant_data, **status_inputs}

    response = client.chat.completions.create(
        model=os.environ["AZURE_OPENAI_DEPLOYMENT"],
        messages=[
            {"role": "system", "content": "You are a federal grants management specialist. Generate accurate, complete grant progress report narratives that meet 2 CFR Part 200 requirements. Flag missing data clearly rather than fabricating information."},
            {"role": "user", "content": SF_PPR_PROMPT.format(**combined_data)}
        ],
        temperature=0.1,
        max_tokens=3000
    )

    return response.choices[0].message.content


def generate_status_input_template(award_number: str, reporting_period: str) -> str:
    """Generate a structured status input template for PI/PM to complete."""

    template = f"""# Grant Status Input Template
## Award: {award_number} | Reporting Period: {reporting_period}
## Complete all sections and return to Grants Manager by [DUE DATE - 5 business days]

### ACCOMPLISHMENTS THIS PERIOD
What was accomplished during this reporting period?
(Describe specific activities completed, data collected, analyses performed, etc.)

[PI/PM RESPONSE HERE]

### MILESTONES COMPLETED
List each milestone from the project timeline that was completed this period:
- Milestone [X]: [Description] — Completed [Date]

[PI/PM RESPONSE HERE]

### MILESTONES BEHIND SCHEDULE (if any)
List any milestones not completed as planned:
- Milestone [X]: [Description] — Originally due [Date], now expected [Date]
  Reason for delay: [Explanation]
  Corrective action: [What you are doing to get back on track]

[PI/PM RESPONSE HERE — Enter "None — all milestones on track" if applicable]

### EXPENDITURES
Expenditures this reporting period: $ [AMOUNT]
Cumulative expenditures to date: $ [AMOUNT]
Remaining award balance: $ [AMOUNT]
(Confirm with Finance before submission)

### NEXT PERIOD PLANNED ACTIVITIES
What do you plan to accomplish in the next reporting period?
- [Activity 1] — Target: [Date]
- [Activity 2] — Target: [Date]

[PI/PM RESPONSE HERE]

### PERSONNEL CHANGES
Any changes to key personnel since last report? (Yes/No)
If yes: [Who changed, what position, has sponsor approval been obtained?]

[PI/PM RESPONSE HERE]

### SCOPE OR APPROACH CHANGES
Any changes to project scope, objectives, or approach? (Yes/No)
If yes: [Describe change and whether sponsor approval is required/has been obtained]

[PI/PM RESPONSE HERE]

### ISSUES OR CHALLENGES
Any significant issues, risks, or challenges encountered? (Yes/No)
If yes: [Describe and provide mitigation approach]

[PI/PM RESPONSE HERE]

### PUBLICATIONS, PRESENTATIONS, OR OUTPUTS
List any publications, presentations, datasets, or other outputs from this period:
- [Citation or description]

[PI/PM RESPONSE HERE — Enter "None this period" if applicable]

---
Return completed template to: [GRANTS MANAGER NAME] by [DUE DATE]
Questions: [Contact info]"""

    return template