BI CVW Transaction Totl – Overview

Purpose:

This view consolidates detailed transaction-level information for all contracts, combining rental, sale, damage waiver, and other charges with associated customer, store, and payment details. It also includes aging, AR, and sales rep assignment for reporting and analytics.

What’s Included:

• Contract & Store Details:
  

  • Contract No – Unique identifier for each transaction/contract.

  • Store No – Store where the transaction occurred.

  • Customer No – Customer identifier.

  • Contract Date / Contract Time – Date and time the transaction was created.

  • Last Modified Date, Close Date, Billed Date, Completed Date – Key dates in the contract lifecycle.
    
    

• Transaction Amounts & Financials:
  

  • Rent Amt, Sale Amt, Tax Amt, Damage Waiver Amt, Other Amt – Amounts per revenue type.

  • Total, Total Paid, Total Owed – Aggregate financials per contract.

  • Deposit Paid Amt / Deposit Required – Payment and deposit details.

  • AssetSales – Total asset sales applied to a transaction.

  • Account_Cash / PaymentMethod / PYMT – Payment type classification.
    
    

• Contract & Transaction Type Info:
  

  • ContractType – Categorized as Contract-Credit, Contract-Num, or Contract-Alpha.

  • TransactionType – Customer vs. Internal transaction.

  • CreditContract / CreditAmt – Flags and amount for credit contracts.

  • SaleType – Sales rep assignment type: Contract, Customer, or JobSite.
    
    

• Customer & Contact Info:
  

  • Customer, Customer Type – Customer name and classification.

  • Contact, Contact Phone – Primary contact info.

  • ARClerk – Assigned accounts receivable clerk.
    
    

• Delivery & Pickup Details:
  

  • Promised / Actual Delivery and Pickup dates.

  • Truck numbers, trip numbers, delivered-to address, city, and state.
    
    

• Contract Status:
  

  • Maps internal status codes to business-friendly labels:

    • Open, Closed, Completed, Work Order, Reservation, Quote, Adjustment, Finance Charge, Transferred.

  • Status Desc – Optional description from TransactionStatus.
    
    

• Aging Information:
  

  • Aging – Days since contract closed (or date used for aging).

  • Age Buckets – Categorized into <30, 31–60, 61–90, >90 days.

  • AgeUnder30, Age30to60, Age60to90, AgeOver90 – Outstanding balances per aging bucket.
    
    

• Temporal Calculations for Reporting:
  

  • Contract Month / Contract Year – Extracted for period reporting.

  • TodaysDate, ContractRealDate, AgeDate – Useful for date calculations and analytics.

How It Works:

• Combines all transactions from Transactions table with:

  • Payment method mapping (PaymentMethod).

  • Transaction type details (TransactionType).

  • Sales rep assignment (Contract, Customer, JobSite).

  • Customer info (CustomerFile and CustomerType).

  • AR information (AccountsReceivableAssigned).

  • Transaction line-level asset sales (TransactionItems).
    
    

• Allows the selection of sales reps (Contract, Customer, JobSite).

• Calculates financial metrics including aging, AR balances, asset sales, and totals owed.

Uses:

• Detailed AR reporting and aging analysis per contract.

• Comprehensive financial and operational dashboards.

• Insights into sales rep performance across contract, customer, and jobsite categories.

• Supports audit, reconciliation, and revenue analysis.

POR Tables

• Transactions 

• PaymentMethod

• TransactionType 

• CustomerFile 

• ParameterFile

• TransactionStatus 

• CustomerJobSite 

• Salesman SalesCust

• Salesman SalesJob 

• Salesman SalesContract

• CustomerType 

• AccountsReceivableAssigned 

• Operatorid