Unpaid Sales Report Documentation

Overview

The Unpaid Sales Report module (report.unpaid.sale) tracks confirmed sales orders that have not been fully paid. It provides accounts receivable (AR) aging analysis, identifying which customers have outstanding payments and monitoring collection effectiveness.

---

Model Information

Model Name: report.unpaid.sale Display Name: Unpaid Sales Report Type: Transient Report Model (_transient = True)

Features

• Tracks unpaid confirmed orders
• Payment status monitoring
• Customer payment history
• Payment method filtering
• Date range analysis
• AR aging support

---

Key Fields Reference

Report Parameters

Field	Type	Required	Default	Description
date_from	Date	No	First day of month	Start date for order filtering
date_to	Date	No	Last day of month	End date for order filtering
pay_method_id	Many2One	No	None	Filter by payment method

Output Fields

Field	Description
id	Sales order ID
number	Order number
related_id	Related document ID
related_number	Related document number
date	Order date
customer_name	Customer name
phone	Customer phone
pay_method	Payment method name
amount	Outstanding amount

---

Payment Status Logic

# Report only includes:
cond = [
    ["state", "=", "confirmed"],   # Only confirmed orders
    ["date", ">=", date_from],
    ["date", "<=", date_to]
]

# Then filters out paid orders
for sale in sales_orders:
    if not sale.is_paid:          # Only unpaid orders included
        lines.append(sale_data)

Key Behavior: - Only confirmed orders (excludes draft, voided) - is_paid flag determines inclusion - Outstanding = full order amount (no partial payment tracking in this report) - Use payment report for detailed payment tracking

---

API Methods

1. Generate Unpaid Sales Report

Method: get_report_data(ids, context)

Generates report of unpaid confirmed orders.

Parameters:

ids = [report_id]  # Report configuration ID
context = {}       # Optional context

Returns: dict - Report data:

{
    "company_name": "ABC Company",
    "date_from": "2024-01-01",
    "date_to": "2024-01-31",
    "lines": [
        {
            "id": 123,
            "number": "SO-2024-001",
            "related_id": 456,
            "related_number": "REL-001",
            "date": "2024-01-15",
            "customer_name": "ABC Corp",
            "phone": "555-1234",
            "pay_method": "Bank Transfer",
            "amount": 10000.00
        }
    ],
    "total": 50000.00
}

Example:

# Generate unpaid orders report for January
report_id = get_model("report.unpaid.sale").create({
    "date_from": "2024-01-01",
    "date_to": "2024-01-31"
})

data = get_model("report.unpaid.sale").get_report_data([report_id])

print(f"Unpaid Orders: {len(data['lines'])}")
print(f"Total Outstanding: ${data['total']:,.2f}")

# List orders
for line in data["lines"]:
    print(f"{line['number']} - {line['customer_name']}: ${line['amount']:,.2f}")

---

Related Models

Model	Relationship	Description
sale.order	Data Source	Sales orders with payment status
contact	Many2One	Customer information
payment.method	Many2One	Payment method
account.invoice	Indirect	Via invoicing and payment tracking

---

Common Use Cases

Use Case 1: Daily AR Follow-up

# Generate daily list of unpaid orders for collection calls

from datetime import date, timedelta

# Last 30 days of unpaid orders
date_from = (date.today() - timedelta(days=30)).strftime("%Y-%m-%d")
date_to = date.today().strftime("%Y-%m-%d")

report_id = get_model("report.unpaid.sale").create({
    "date_from": date_from,
    "date_to": date_to
})

data = get_model("report.unpaid.sale").get_report_data([report_id])

print("Collection Follow-up List:")
print(f"Total Outstanding: ${data['total']:,.2f}\n")

# Sort by amount descending
sorted_lines = sorted(data["lines"], key=lambda x: x["amount"], reverse=True)

for i, line in enumerate(sorted_lines, 1):
    print(f"{i}. {line['customer_name']}")
    print(f"   Order: {line['number']} ({line['date']})")
    print(f"   Amount: ${line['amount']:,.2f}")
    print(f"   Phone: {line.get('phone', 'N/A')}")
    print(f"   Method: {line.get('pay_method', 'N/A')}")
    print()

---

Use Case 2: Aging Analysis

# Categorize unpaid orders by age

from datetime import datetime, date

# Get all unpaid orders from past 6 months
date_from = (date.today() - timedelta(days=180)).strftime("%Y-%m-%d")
date_to = date.today().strftime("%Y-%m-%d")

report_id = get_model("report.unpaid.sale").create({
    "date_from": date_from,
    "date_to": date_to
})

data = get_model("report.unpaid.sale").get_report_data([report_id])

# Aging buckets
aging = {
    "Current (0-30 days)": {"count": 0, "amount": 0},
    "31-60 days": {"count": 0, "amount": 0},
    "61-90 days": {"count": 0, "amount": 0},
    "Over 90 days": {"count": 0, "amount": 0}
}

today = date.today()
for line in data["lines"]:
    order_date = datetime.strptime(line["date"], "%Y-%m-%d").date()
    days_old = (today - order_date).days

    if days_old <= 30:
        bucket = "Current (0-30 days)"
    elif days_old <= 60:
        bucket = "31-60 days"
    elif days_old <= 90:
        bucket = "61-90 days"
    else:
        bucket = "Over 90 days"

    aging[bucket]["count"] += 1
    aging[bucket]["amount"] += line["amount"]

print("Accounts Receivable Aging:")
for bucket, stats in aging.items():
    if stats["count"] > 0:
        print(f"{bucket}: {stats['count']} orders, ${stats['amount']:,.2f}")

---

Use Case 3: Payment Method Analysis

# Analyze unpaid orders by payment method

report_id = get_model("report.unpaid.sale").create({
    "date_from": "2024-01-01",
    "date_to": "2024-12-31"
})

data = get_model("report.unpaid.sale").get_report_data([report_id])

from collections import defaultdict
by_method = defaultdict(lambda: {"count": 0, "amount": 0})

for line in data["lines"]:
    method = line.get("pay_method") or "Not Specified"
    by_method[method]["count"] += 1
    by_method[method]["amount"] += line["amount"]

print("Unpaid Orders by Payment Method:")
for method, stats in sorted(by_method.items(), key=lambda x: x[1]["amount"], reverse=True):
    print(f"{method}: {stats['count']} orders, ${stats['amount']:,.2f}")

---

Use Case 4: Customer Credit Review

# Identify customers with high outstanding balances

report_id = get_model("report.unpaid.sale").create({
    "date_from": "2024-01-01",
    "date_to": "2024-12-31"
})

data = get_model("report.unpaid.sale").get_report_data([report_id])

from collections import defaultdict
by_customer = defaultdict(lambda: {"orders": [], "total": 0})

for line in data["lines"]:
    customer = line["customer_name"]
    by_customer[customer]["orders"].append(line)
    by_customer[customer]["total"] += line["amount"]

# Set credit limit threshold
credit_threshold = 50000.00

print(f"Customers Exceeding ${credit_threshold:,.0f} Outstanding:")
for customer, info in sorted(by_customer.items(), key=lambda x: x[1]["total"], reverse=True):
    if info["total"] > credit_threshold:
        print(f"\n{customer}: ${info['total']:,.2f}")
        print(f"  Number of unpaid orders: {len(info['orders'])}")
        print(f"  Oldest order: {min(o['date'] for o in info['orders'])}")

        # List individual orders
        for order in sorted(info["orders"], key=lambda x: x["date"]):
            print(f"    - {order['number']} ({order['date']}): ${order['amount']:,.2f}")

---

Use Case 5: Collection Effectiveness Tracking

# Track collection performance over time

from datetime import datetime
from dateutil.relativedelta import relativedelta

# Generate monthly unpaid snapshots
monthly_ar = []
start_date = datetime(2024, 1, 1)

for i in range(12):
    month_start = (start_date + relativedelta(months=i)).strftime("%Y-%m-01")
    month_end = (start_date + relativedelta(months=i, day=31)).strftime("%Y-%m-%d")

    report_id = get_model("report.unpaid.sale").create({
        "date_from": month_start,
        "date_to": month_end
    })

    data = get_model("report.unpaid.sale").get_report_data([report_id])

    monthly_ar.append({
        "month": (start_date + relativedelta(months=i)).strftime("%B %Y"),
        "count": len(data["lines"]),
        "amount": data["total"]
    })

print("Monthly Unpaid Orders Trend:")
for month_data in monthly_ar:
    if month_data["count"] > 0:
        avg_order = month_data["amount"] / month_data["count"]
        print(f"{month_data['month']}: {month_data['count']} orders, "
              f"${month_data['amount']:,.2f} (Avg: ${avg_order:,.2f})")

---

Performance Tips

1. Date Range Selection

# Good: Recent range for active collections
date_from = "2024-01-01"
date_to = "2024-03-31"

# Less optimal: Long historical range
date_from = "2020-01-01"  # May include very old unpaid orders

2. Payment Method Filtering

# Filter by specific payment method if analyzing collection issues
report_id = get_model("report.unpaid.sale").create({
    "date_from": "2024-01-01",
    "date_to": "2024-01-31",
    "pay_method_id": specific_method_id  # Faster query
})

3. Order State

• Report automatically filters to confirmed state only
• No need to check draft or voided orders
• is_paid flag efficiently excludes paid orders

---

Difference from Other AR Reports

vs report.sale.payment

• Unpaid Sale: Shows orders not yet paid (AR outstanding)
• Payment: Shows payments received (cash collected)

vs report.sale.unbill

• Unpaid Sale: Payment status (money owed)
• Unbill: Invoicing status (billing pipeline)
• An order can be unbilled but paid (prepayment)
• An order can be billed but unpaid (invoice sent, not collected)

Typical Workflow

Order Created → Unbilled Report (needs invoice)
    ↓
Invoice Created → Unpaid Report (needs payment)
    ↓
Payment Received → Payment Report (cash received)

---

Troubleshooting

"No unpaid orders found"

Cause: All orders paid, or no confirmed orders in date range Solution: - Verify orders exist in confirmed state - Check is_paid flag on orders - Expand date range - Verify orders in date range are actually unpaid

"Order shows as unpaid but payment exists"

Cause: Payment not properly allocated to order, or is_paid flag not updated Solution: - Check payment allocation to invoices - Verify invoice linked to order - Ensure payment is posted (not draft) - Check if order amount matches payment amount

"Customer phone missing"

Cause: Phone not set on contact or address records Solution: - Update contact with phone number - Add phone to customer address - Report checks both contact.phone and address.phone

---

Integration with Collections Process

# Automated collection workflow

report_id = get_model("report.unpaid.sale").create({
    "date_from": "2024-01-01",
    "date_to": "2024-03-31"
})

data = get_model("report.unpaid.sale").get_report_data([report_id])

from datetime import datetime, date, timedelta

today = date.today()

for line in data["lines"]:
    order_date = datetime.strptime(line["date"], "%Y-%m-%d").date()
    days_overdue = (today - order_date).days

    # Escalation logic
    if days_overdue > 90:
        # Send to collections agency
        print(f"URGENT: {line['number']} - {line['customer_name']}")
        print(f"  {days_overdue} days overdue, ${line['amount']:,.2f}")
        # send_to_collections(line)

    elif days_overdue > 60:
        # Manager follow-up
        print(f"ATTENTION: {line['number']} - {line['customer_name']}")
        print(f"  {days_overdue} days overdue, ${line['amount']:,.2f}")
        # assign_to_manager(line)

    elif days_overdue > 30:
        # Standard follow-up call
        print(f"Follow-up: {line['number']} - {line['customer_name']}")
        print(f"  {days_overdue} days overdue, ${line['amount']:,.2f}")
        # schedule_call(line)

    else:
        # Courtesy reminder
        print(f"Reminder: {line['number']} - {line['customer_name']}")
        print(f"  {days_overdue} days old, ${line['amount']:,.2f}")
        # send_reminder_email(line)

---

Version History

Last Updated: 2026-01-05 Model Version: report_unpaid_sale.py (98 lines) Framework: Netforce

---

Additional Resources

• Sales Order Documentation: sale.order
• Payment Report Documentation: report.sale.payment
• Unbilled Report Documentation: report.sale.unbill

---

This documentation is generated for developer onboarding and reference purposes.