Implementation Guide: Auto-Generate DD-250/WAWF Shipping & Acceptance Documents & Trigger MILSTRIP Requisitions

# Extracts DD-250 data elements from Deltek Costpoint

# erp_data_extractor.py
# Extracts DD-250 data elements from Deltek Costpoint

import requests
import os
from datetime import datetime

COSTPOINT_BASE = os.environ["COSTPOINT_URL"]
COSTPOINT_AUTH = (os.environ["COSTPOINT_USER"], os.environ["COSTPOINT_PASS"])

# DD Form 250 required data elements (per DFARS 252.246-7000)
DD250_FIELD_MAPPING = {
    "contract_number":      "Costpoint Contract Master > Contract Number",
    "delivery_order":       "Costpoint Contract Master > Delivery Order Number",
    "clin":                 "Costpoint Contract Lines > CLIN Number",
    "prime_contract":       "Costpoint Contract Master > Prime Contract (if subcontractor)",
    "shipment_number":      "Costpoint Shipment Master > Shipment Number",
    "date_shipped":         "Costpoint Shipment Master > Ship Date",
    "ship_from_address":    "Costpoint Company Master > Shipping Address",
    "ship_to_address":      "Costpoint Contract Master > Ship To (f.o.b. destination)",
    "mark_for":             "Costpoint Contract Master > Mark For (if applicable)",
    "item_description":     "Costpoint Item Master > Description",
    "nsn":                  "Costpoint Item Master > NSN (if applicable)",
    "quantity_shipped":     "Costpoint Shipment Lines > Quantity",
    "unit_of_issue":        "Costpoint Item Master > Unit of Issue",
    "unit_price":           "Costpoint Contract Lines > Unit Price",
    "total_price":          "Calculated: Quantity × Unit Price",
    "inspection_authority": "Costpoint QA Module > Inspector Name",
    "inspection_date":      "Costpoint QA Module > Inspection Date",
    "cage_code":            "Company Registration > CAGE Code",
    "invoice_number":       "Costpoint AR > Invoice Number (if combined with invoice)"
}

def get_shipment_data(shipment_id: str) -> dict:
    """Retrieve all DD-250 relevant data for a shipment from Costpoint."""
    headers = {"Accept": "application/json", "Content-Type": "application/json"}

    # Get shipment header
    ship_resp = requests.get(
        f"{COSTPOINT_BASE}/api/v1/shipments/{shipment_id}",
        auth=COSTPOINT_AUTH, headers=headers, verify=True
    )
    ship_resp.raise_for_status()
    shipment = ship_resp.json()

    # Get associated contract data
    contract_id = shipment.get("contractId")
    contract_resp = requests.get(
        f"{COSTPOINT_BASE}/api/v1/contracts/{contract_id}",
        auth=COSTPOINT_AUTH, headers=headers, verify=True
    )
    contract = contract_resp.json() if contract_resp.status_code == 200 else {}

    # Get quality inspection data
    qi_resp = requests.get(
        f"{COSTPOINT_BASE}/api/v1/quality/inspections",
        auth=COSTPOINT_AUTH, headers=headers,
        params={"shipmentId": shipment_id, "status": "Approved"}
    )
    inspection = qi_resp.json()[0] if qi_resp.status_code == 200 and qi_resp.json() else {}

    if not inspection:
        raise ValueError(f"No approved quality inspection found for shipment {shipment_id}. "
                         f"DD-250 cannot be generated without inspection approval.")

    # Assemble DD-250 data package
    return {
        # Block 1: Contract No.
        "contract_number": contract.get("contractNumber", ""),
        # Block 2: Delivery Order No.
        "delivery_order": contract.get("deliveryOrderNumber", ""),
        # Block 3: Date Shipped
        "date_shipped": shipment.get("shipDate", datetime.today().strftime("%Y-%m-%d")),
        # Block 4: B/L or GBL
        "bill_of_lading": shipment.get("billOfLadingNumber", ""),
        # Block 5: Discount Terms
        "discount_terms": contract.get("paymentTerms", "Net 30"),
        # Block 6: Invoice No./Date
        "invoice_number": shipment.get("invoiceNumber", ""),
        # Block 8: Prime Contract No.
        "prime_contract": contract.get("primeContractNumber", "N/A"),
        # Block 9: Order/LOT No.
        "lot_number": shipment.get("lotNumber", ""),
        # Block 11: Ship From
        "ship_from": {
            "name": os.environ.get("COMPANY_NAME", ""),
            "cage": os.environ.get("CAGE_CODE", ""),
            "address": os.environ.get("SHIPPING_ADDRESS", "")
        },
        # Block 12: Payment Will Be Made By
        "paying_office": contract.get("payingOffice", "DFAS Columbus"),
        # Block 13: Ship To
        "ship_to": contract.get("shipToAddress", {}),
        # Block 14: Mark For
        "mark_for": contract.get("markFor", ""),
        # Block 15: Item/Article
        "line_items": [
            {
                "clin": line.get("clinNumber", ""),
                "stock_number": line.get("nsn", line.get("partNumber", "")),
                "description": line.get("itemDescription", ""),
                "quantity": line.get("quantityShipped", 0),
                "unit": line.get("unitOfIssue", "EA"),
                "unit_price": line.get("unitPrice", 0.0),
                "amount": line.get("quantityShipped", 0) * line.get("unitPrice", 0.0)
            }
            for line in shipment.get("lineItems", [])
        ],
        # Block 21: Signature of Authorized Government Representative
        "inspection_authority": inspection.get("inspectorName", ""),
        "inspection_date": inspection.get("inspectionDate", ""),
        "inspection_location": inspection.get("inspectionLocation", ""),
        "acceptance_point": contract.get("acceptancePoint", "Destination"),
        # Total
        "total_amount": sum(
            line.get("quantityShipped", 0) * line.get("unitPrice", 0.0)
            for line in shipment.get("lineItems", [])
        )
    }


def get_inventory_levels(nsn_list: list) -> list:
    """Check inventory levels for MILSTRIP threshold monitoring."""
    inventory_status = []

    for nsn in nsn_list:
        inv_resp = requests.get(
            f"{COSTPOINT_BASE}/api/v1/inventory",
            auth=COSTPOINT_AUTH,
            params={"nsn": nsn, "warehouse": "ALL"}
        )
        if inv_resp.status_code == 200:
            inv_data = inv_resp.json()
            inventory_status.append({
                "nsn": nsn,
                "description": inv_data.get("description", ""),
                "on_hand_qty": inv_data.get("onHandQuantity", 0),
                "on_order_qty": inv_data.get("onOrderQuantity", 0),
                "reorder_point": inv_data.get("reorderPoint", 0),
                "reorder_quantity": inv_data.get("reorderQuantity", 0),
                "unit_of_issue": inv_data.get("unitOfIssue", "EA"),
                "requires_requisition": inv_data.get("onHandQuantity", 0) <= inv_data.get("reorderPoint", 0)
            })

    return inventory_status

# Generates WAWF-formatted DD-250 documents

# dd250_generator.py
# Generates WAWF-formatted DD-250 documents

import xml.etree.ElementTree as ET
from xml.dom import minidom
import json, os, datetime

def generate_wawf_dd250_xml(dd250_data: dict) -> str:
    """
    Generate WAWF-compatible XML for DD-250 submission.
    Format based on WAWF EDI/XML interface specification.
    Note: Actual WAWF XML schema varies by WAWF version — validate against
    current WAWF XML Interface Control Document (ICD) before production use.
    """

    root = ET.Element("WAWF_DD250")
    root.set("xmlns", "http://wawf.eb.mil/schemas/dd250")
    root.set("version", "1.0")
    root.set("created", datetime.datetime.utcnow().isoformat())

    # Document header
    header = ET.SubElement(root, "Header")
    ET.SubElement(header, "DocumentType").text = "DD250"
    ET.SubElement(header, "ContractNumber").text = dd250_data.get("contract_number", "")
    ET.SubElement(header, "DeliveryOrderNumber").text = dd250_data.get("delivery_order", "")
    ET.SubElement(header, "ShipmentNumber").text = dd250_data.get("shipment_number", "")
    ET.SubElement(header, "DateShipped").text = dd250_data.get("date_shipped", "")
    ET.SubElement(header, "InvoiceNumber").text = dd250_data.get("invoice_number", "")

    # Contractor information
    contractor = ET.SubElement(root, "Contractor")
    ship_from = dd250_data.get("ship_from", {})
    ET.SubElement(contractor, "CAGECode").text = ship_from.get("cage", os.environ.get("CAGE_CODE", ""))
    ET.SubElement(contractor, "CompanyName").text = ship_from.get("name", "")
    ET.SubElement(contractor, "Address").text = ship_from.get("address", "")

    # Ship to information
    ship_to = ET.SubElement(root, "ShipTo")
    ship_to_data = dd250_data.get("ship_to", {})
    ET.SubElement(ship_to, "DoDAAC").text = ship_to_data.get("dodaac", "")
    ET.SubElement(ship_to, "Name").text = ship_to_data.get("name", "")
    ET.SubElement(ship_to, "Address").text = ship_to_data.get("address", "")

    # Line items
    line_items = ET.SubElement(root, "LineItems")
    for i, item in enumerate(dd250_data.get("line_items", []), 1):
        line = ET.SubElement(line_items, "LineItem")
        line.set("sequence", str(i))
        ET.SubElement(line, "CLIN").text = item.get("clin", "")
        ET.SubElement(line, "StockNumber").text = item.get("stock_number", "")
        ET.SubElement(line, "Description").text = item.get("description", "")
        ET.SubElement(line, "QuantityShipped").text = str(item.get("quantity", 0))
        ET.SubElement(line, "UnitOfIssue").text = item.get("unit", "EA")
        ET.SubElement(line, "UnitPrice").text = f"{item.get('unit_price', 0.0):.4f}"
        ET.SubElement(line, "TotalAmount").text = f"{item.get('amount', 0.0):.2f}"

    # Totals
    totals = ET.SubElement(root, "Totals")
    ET.SubElement(totals, "TotalAmount").text = f"{dd250_data.get('total_amount', 0.0):.2f}"

    # Inspection/Acceptance
    acceptance = ET.SubElement(root, "InspectionAcceptance")
    ET.SubElement(acceptance, "AcceptancePoint").text = dd250_data.get("acceptance_point", "Destination")
    ET.SubElement(acceptance, "InspectorName").text = dd250_data.get("inspection_authority", "")
    ET.SubElement(acceptance, "InspectionDate").text = dd250_data.get("inspection_date", "")
    ET.SubElement(acceptance, "InspectionResult").text = "Accepted"

    # Certifications
    certifications = ET.SubElement(root, "Certifications")
    ET.SubElement(certifications, "ContractorCertification").text = (
        f"I certify that this voucher is correct and proper for payment, and that "
        f"the materials or services have been delivered in accordance with the contract."
    )
    ET.SubElement(certifications, "CertificationDate").text = datetime.date.today().isoformat()
    ET.SubElement(certifications, "GeneratedBy").text = "Automated DD-250 Pipeline — Human Review Required"

    # Pretty-print XML
    xml_str = minidom.parseString(ET.tostring(root, encoding='unicode')).toprettyxml(indent="  ")
    return xml_str


def validate_dd250_data(dd250_data: dict) -> list:
    """Validate DD-250 data completeness before document generation."""
    errors = []
    required_fields = [
        ("contract_number", "Contract Number (Block 1)"),
        ("date_shipped", "Date Shipped (Block 3)"),
        ("ship_from", "Ship From / CAGE Code (Block 11)"),
        ("ship_to", "Ship To (Block 13)"),
        ("line_items", "Line Items (Block 15)"),
        ("inspection_authority", "Inspector Name (Block 21)"),
        ("inspection_date", "Inspection Date (Block 21)")
    ]

    for field, label in required_fields:
        value = dd250_data.get(field)
        if not value or (isinstance(value, list) and len(value) == 0):
            errors.append(f"MISSING REQUIRED FIELD: {label} ({field})")

    # Validate line item math
    for i, item in enumerate(dd250_data.get("line_items", [])):
        calculated = item.get("quantity", 0) * item.get("unit_price", 0)
        if abs(calculated - item.get("amount", 0)) > 0.01:
            errors.append(f"LINE ITEM {i+1} MATH ERROR: qty × price ({calculated:.2f}) ≠ amount ({item.get('amount', 0):.2f})")

    return errors

# Generates MILSTRIP requisitions and 80-column card images per DoD
# 4000.25-1-M

# milstrip_generator.py
# Generates MILSTRIP requisitions when inventory drops below reorder point

import os, datetime, random

def generate_milstrip_requisition(item: dict, urgency: str = "Routine") -> dict:
    """
    Generate a MILSTRIP requisition document (DD Form 1348-6 equivalent).
    MILSTRIP format: 80-column card image format per DoD 4000.25-1-M.
    """

    # MILSTRIP urgency codes
    urgency_codes = {
        "Routine": "B",      # Priority Group III — Routine (8+ day delivery)
        "Priority": "A",     # Priority Group II — Priority (3-7 day delivery)
        "Emergency": "1"     # Priority Group I — Urgent (ASAP)
    }

    # MILSTRIP document identifier (format: CAGE + Julian date + serial)
    julian_date = datetime.date.today().strftime("%y%j")
    serial = f"{random.randint(0, 9999):04d}"
    doc_number = f"{os.environ.get('CAGE_CODE', 'XXXXX')}{julian_date}{serial}"

    # Determine priority designator based on urgency and force activity designator
    force_activity = os.environ.get("FORCE_ACTIVITY_DESIGNATOR", "F")
    urgency_code = urgency_codes.get(urgency, "B")

    # Priority designator table lookup (simplified)
    priority_matrix = {
        ("A", "A"): "01", ("A", "B"): "03", ("A", "F"): "07",
        ("B", "A"): "02", ("B", "B"): "05", ("B", "F"): "11",
        ("1", "A"): "01", ("1", "B"): "02", ("1", "F"): "06"
    }
    priority = priority_matrix.get((urgency_code, force_activity), "13")

    requisition = {
        "document_number": doc_number,
        "requisition_date": datetime.date.today().isoformat(),
        "nsn": item.get("nsn", ""),
        "description": item.get("description", ""),
        "quantity": item.get("reorder_quantity", 0),
        "unit_of_issue": item.get("unit_of_issue", "EA"),
        "priority_designator": priority,
        "urgency_code": urgency_code,
        "required_delivery_date": (
            datetime.date.today() + datetime.timedelta(
                days={"01": 3, "03": 5, "07": 8, "11": 10, "13": 21}.get(priority, 21)
            )
        ).isoformat(),
        "supplementary_address": os.environ.get("SUPPLEMENTARY_ADDRESS", ""),
        "fund_code": item.get("fund_code", ""),
        "signal_code": "A",  # A = Ship to requisitioner, bill to requisitioner
        "demand_code": "N",  # N = Non-recurring demand
        "current_on_hand": item.get("on_hand_qty", 0),
        "reorder_point": item.get("reorder_point", 0),
        "shortage_qty": item.get("reorder_point", 0) - item.get("on_hand_qty", 0),
        "justification": (
            f"Stock level ({item.get('on_hand_qty', 0)} {item.get('unit_of_issue', 'EA')}) "
            f"is at or below reorder point ({item.get('reorder_point', 0)} "
            f"{item.get('unit_of_issue', 'EA')}). "
            f"Automated requisition generated per approved inventory management parameters."
        ),
        "generated_by": "Automated MILSTRIP Pipeline",
        "requires_approval": True,
        "approval_status": "PENDING — Logistics Manager Review Required"
    }

    # Generate 80-column MILSTRIP card image for DAAS submission
    requisition["milstrip_card_image"] = generate_milstrip_card_image(requisition)

    return requisition


def generate_milstrip_card_image(req: dict) -> str:
    """
    Generate the 80-column MILSTRIP card image format per DoD 4000.25-1-M.
    Column positions are standardized — deviations cause rejection.
    """
    # Simplified 80-column format (full format per DoD 4000.25-1-M Chapter 4)
    nsn = req.get("nsn", "").replace("-", "").ljust(15)[:15]
    doc_num = req.get("document_number", "").ljust(14)[:14]
    qty = str(req.get("quantity", 0)).rjust(5).ljust(5)
    priority = req.get("priority_designator", "13").ljust(2)[:2]
    rdd = req.get("required_delivery_date", "").replace("-", "")[-5:]  # Julian date format
    signal = req.get("signal_code", "A")
    fund = req.get("fund_code", "").ljust(2)[:2]
    suppl = req.get("supplementary_address", "").ljust(6)[:6]

    # 80-column MILSTRIP requisition (format code "A" — standard)
    card = f"{'A':<1}{nsn:<15}{' ':<4}{qty:<5}{' ':<14}{doc_num:<14}{priority:<2}{rdd:<5}{signal:<1}{fund:<2}{suppl:<6}{'':>11}"
    return card[:80].ljust(80)

// Azure Logic App definition (Azure Government)
// Trigger: HTTP webhook from Costpoint shipment ready event
// Or: Scheduled polling of Costpoint API for shipments in "Ready to Ship" status

{
  "definition": {
    "$schema": "https://schema.management.azure.com/providers/Microsoft.Logic/schemas/2016-06-01/workflowdefinition.json#",
    "triggers": {
      "Shipment_Ready_Webhook": {
        "type": "Request",
        "kind": "Http",
        "inputs": {
          "schema": {
            "properties": {
              "shipment_id": {"type": "string"},
              "shipment_status": {"type": "string"},
              "contract_number": {"type": "string"}
            }
          }
        }
      }
    },
    "actions": {
      "Check_Inspection_Status": {
        "type": "Http",
        "inputs": {
          "method": "GET",
          "uri": "@{concat(parameters('CostpointBaseUrl'), '/api/v1/quality/inspections?shipmentId=', triggerBody()?['shipment_id'], '&status=Approved')}",
          "authentication": {
            "type": "Basic",
            "username": "@{parameters('CostpointUser')}",
            "password": "@{parameters('CostpointPass')}"
          }
        }
      },
      "Condition_Inspection_Approved": {
        "type": "If",
        "expression": {
          "and": [{"greater": ["@length(body('Check_Inspection_Status'))", 0]}]
        },
        "actions": {
          "Generate_DD250": {
            "type": "Http",
            "inputs": {
              "method": "POST",
              "uri": "@{parameters('AzureFunctionUrl')}/api/generate_dd250",
              "body": {"shipment_id": "@{triggerBody()?['shipment_id']}"}
            }
          },
          "Save_to_SharePoint": {
            "type": "ApiConnection",
            "inputs": {
              "host": {"connection": {"name": "@parameters('SharePointConnection')"}},
              "method": "post",
              "path": "/datasets/@{encodeURIComponent('https://tenant.sharepoint.us/sites/Logistics')}/files",
              "body": "@body('Generate_DD250')?['xml_document']",
              "queries": {
                "folderPath": "/Shared Documents/DD250s/@{triggerBody()?['contract_number']}",
                "name": "@{concat('DD250_', triggerBody()?['shipment_id'], '_', formatDateTime(utcNow(), 'yyyyMMdd'), '.xml')}"
              }
            }
          },
          "Notify_Logistics_Manager": {
            "type": "ApiConnection",
            "inputs": {
              "host": {"connection": {"name": "@parameters('OutlookConnection')"}},
              "method": "post",
              "path": "/v2/Mail",
              "body": {
                "To": "@parameters('LogisticsManagerEmail')}",
                "Subject": "REVIEW REQUIRED: DD-250 Generated for Shipment @{triggerBody()?['shipment_id']}",
                "Body": "A DD-250 has been automatically generated for shipment @{triggerBody()?['shipment_id']} on contract @{triggerBody()?['contract_number']}. Please review and submit to WAWF within 5 business days of shipment. Document location: [SharePoint link]"
              }
            }
          }
        },
        "else": {
          "actions": {
            "Alert_No_Inspection": {
              "type": "ApiConnection",
              "inputs": {
                "host": {"connection": {"name": "@parameters('OutlookConnection')"}},
                "method": "post",
                "path": "/v2/Mail",
                "body": {
                  "To": "@parameters('QualityManagerEmail')}",
                  "Subject": "HOLD: DD-250 Blocked — No Inspection Approval for Shipment @{triggerBody()?['shipment_id']}",
                  "Body": "DD-250 generation was blocked because no approved quality inspection record was found for this shipment. Please complete inspection and update Costpoint before DD-250 can be generated."
                }
              }
            }
          }
        }
      }
    }
  }
}

SYSTEM PROMPT:
You are a defense contract administration specialist. A discrepancy has been detected
during automated DD-250 generation. Generate a clear explanation of the discrepancy
and specific recommended resolution actions.

DISCREPANCY DETECTED:
Type: {discrepancy_type}
Contract: {contract_number}
Shipment: {shipment_id}
Expected Value: {expected}
Actual Value: {actual}
Source Systems: {systems_compared}

Generate:
1. PLAIN LANGUAGE DESCRIPTION: What is the discrepancy and why it matters
2. LIKELY CAUSE: Most probable reasons for this type of discrepancy (list 2-3)
3. REGULATORY IMPACT: Does this discrepancy prevent DD-250 submission? (Yes/No — why)
4. RESOLUTION STEPS: Specific steps to resolve, in order
5. ESCALATION: Who should be notified and when
6. PREVENTIVE ACTION: How to prevent this discrepancy in future shipments

[LOGISTICS MANAGER REVIEW REQUIRED — Do not submit DD-250 until discrepancy resolved]