Seuraava dokumentaatio kuvaa taulun

production.invoices, joka on keskeinen osa Eläinlääketieteellisen käytännön tietopohjaa. Tämä taulu tarjoaa suoran pääsyn vakioituihin, testattuihin ja rikastettuihin laskutietoihin, jotta voit nopeuttaa analytiikkaa ja talousraportointia.

Tarkka sarakeviite

Ydin-tunnisteet

Column	Type	Description	Examples	Business Logic
provet_id	STRING	Your unique practice identifier	"pcus_1"	Tenant isolation key - always filter by this for performance
invoice_id	BIGINT	Unique invoice identifier within practice	12345, 67890	System-generated, immutable once created
invoice_number	STRING	Business invoice number shown to clients	"1001-2024-00123", "DEPT2-240915-456"	Format: {department_id}-{date}-{sequence}. NULL until invoice is finalized (status=3)

Laskun luokittelu

Column	Type	Description	Examples	Business Logic
invoice_type	STRING	Derived invoice category	"consultation", "countersale", "credit_note"	Logic:CASE WHEN countersale=1 THEN 'Countersale' WHEN credit_note=0 THEN 'Consultation' WHEN credit_note=1 THEN 'Credit Note' ELSE 'Unknown' END
consultation_id	BIGINT	Related consultation reference	98765, NULL	Links to consultation record. NULL for countersales and credit notes
invoice_status	STRING	Current invoice state	"pending", "paid", "cancelled"	Mapping: 0=Draft, 1=Sent, 2=Open, 3=Finalized, 4=Invoicing, 99=Voided. Note: "pending"/"paid"/"cancelled" are simplified business terms

Tiedot taloudesta

Column	Type	Description	Examples	Business Logic
total_incl_vat	DECIMAL	Total amount including VAT/tax	125.50, 89.99	Final billable amount including all taxes
total_excl_vat	DECIMAL	Total amount excluding VAT/tax	100.40, 74.99	Base amount before tax calculations
amount_due	DECIMAL	Outstanding balance remaining	125.50, 0.00, 25.00	Reduces as payments are applied. 0.00 = fully paid
currency_code	STRING	ISO 4217 currency code	"EUR", "USD", "GBP", "CAD"	Auto-mapped from department country
currency_name	STRING	Full currency name	"Euro", "US Dollar", "British Pound Sterling"	Human-readable currency description

Asiakas- ja eläinlääkäritiedot

Column	Type	Description	Examples	Business Logic
client_name	STRING	Client/payer name	"John Smith", "Pet Insurance Co", "Sarah Johnson"	From payer_name field in source data
supervising_veterinarian_name	STRING	Full name of supervising vet	"Dr. Emily Watson", "Michael Chen DVM", NULL	Logic: `first_name

Maksutavan tiedot

Column	Type	Description	Examples	Business Logic
payment_method_type	STRING	Standardized payment category	"Card", "Cash or Check", "Digital Wallet", "Financing"	Auto-categorization logic: Card flag → "Card", Cash/Check keywords → "Cash or Check", CareCredit/Scratchpay → "Financing", etc.
payment_method_name	STRING	Original payment method name	"Visa Credit Card", "Cash", "CareCredit"	Raw name from practice management system
payment_method_name_standardized	STRING	Standardized payment name	"Visa", "Cash", "CareCredit"	Standardization logic: Pattern matching (e.g., '%visa%' → 'Visa', '%mastercard%' → 'Mastercard')

Toimipiste- ja sijaintitiedot

Column	Type	Description	Examples	Business Logic
department_id	BIGINT	Department/clinic identifier	1001, 2005	Links to specific clinic location
department_name	STRING	Department/clinic name	"Main Street Veterinary", "Emergency Clinic Downtown"	Business name for the location
department_timezone	STRING	IANA timezone identifier	"America/New_York", "Europe/London", "Australia/Sydney"	Used for local time conversions

Päivämääräkentät (liiketoimintapäivät)

Column	Type	Description	Examples	Business Logic
created_date	DATE	Invoice creation date	2024-09-15, 2024-08-22	Date portion of creation timestamp
invoice_date	DATE	Official invoice date	2024-09-15, 2024-08-22	Business date shown on invoice
invoice_due_date	DATE	Payment due date	2024-10-15, 2024-09-22	When payment is expected
invoice_paid_date	DATE	Date payment was received	2024-09-20, NULL	NULL for unpaid invoices

UTC-aikaleimat (järjestelmäajat)

Column	Type	Description	Examples	Business Logic
created_ts_utc	TIMESTAMP	Creation timestamp (UTC)	2024-09-15 14:30:25.123	System creation time in UTC
invoice_date_ts_utc	TIMESTAMP	Invoice timestamp (UTC)	2024-09-15 12:00:00.000	Invoice date converted to UTC
invoice_due_date_ts_utc	TIMESTAMP	Due timestamp (UTC)	2024-10-15 23:59:59.000	Due date converted to UTC
invoice_paid_date_ts_utc	TIMESTAMP	Payment timestamp (UTC)	2024-09-20 16:45:30.456, NULL	Payment received time in UTC

Paikalliset aikaleimat (toimipisteen aikavyöhyke)

Column	Type	Description	Examples	Business Logic
created_ts_local	TIMESTAMP	Creation timestamp (local)	2024-09-15 10:30:25.123
invoice_date_ts_local	TIMESTAMP	Invoice timestamp (local)	2024-09-15 08:00:00.000
invoice_due_date_ts_local	TIMESTAMP	Due timestamp (local)	2024-10-15 19:59:59.000
invoice_paid_date_ts_local	TIMESTAMP	Payment timestamp (local)	2024-09-20 12:45:30.456, NULL

Yleiset kyselymallit

Tulojen analyysi

-- Monthly revenue by payment methodSELECT     DATE_TRUNC('month', invoice_date) as month,    payment_method_type,    COUNT(*) as invoice_count,    SUM(total_incl_vat) as revenue,    currency_codeFROM production.invoices WHERE provet_id = 'your_practice_id'    AND invoice_status IN ('paid', 'finalized')    AND invoice_date >= '2024-01-01'GROUP BY 1, 2, 5ORDER BY 1 DESC, 4 DESC;

Maksamattomat saatavat

-- Aging analysis of unpaid invoicesSELECT     CASE         WHEN DATEDIFF(CURRENT_DATE(), invoice_due_date) <= 0 THEN 'Current'        WHEN DATEDIFF(CURRENT_DATE(), invoice_due_date) <= 30 THEN '1-30 days'        WHEN DATEDIFF(CURRENT_DATE(), invoice_due_date) <= 60 THEN '31-60 days'        ELSE 'Over 60 days'    END as aging_bucket,    COUNT(*) as invoice_count,    SUM(amount_due) as total_outstanding,    currency_codeFROM production.invoicesWHERE provet_id = 'your_practice_id'    AND amount_due > 0GROUP BY 1, 4ORDER BY 1;

Eläinlääkärin suorituskyky

-- Top performing veterinarians by consultation revenueSELECT     supervising_veterinarian_name,    COUNT(DISTINCT invoice_id) as consultations,    SUM(total_incl_vat) as total_revenue,    AVG(total_incl_vat) as avg_consultation_value,    currency_codeFROM production.invoicesWHERE provet_id = 'your_practice_id'    AND invoice_type = 'consultation'    AND supervising_veterinarian_name IS NOT NULL    AND invoice_date >= DATE_SUB(CURRENT_DATE(), 30)GROUP BY 1, 5ORDER BY 3 DESC;

Tärkeät käyttöhuomiot

Usean valuutan käsittely

Kun yhdistät taloustietoja toimipisteiden yli, sisällytä aina currency_code:

-- CORRECT: Group by currencySELECT currency_code, SUM(total_incl_vat) as revenueFROM production.invoices GROUP BY currency_code;-- INCORRECT: Mixing currenciesSELECT SUM(total_incl_vat) as revenue  -- May mix EUR + USD + GBPFROM production.invoices;

Aikavyöhykkeen parhaat käytännöt

Käytä *_ts_local-kenttiä liiketoimintaraportointiin ja käyttäjän käyttöliittymiin
Käytä *_ts_utc-kenttiä järjestelmäintegraatioihin ja datan käsittelyyn
Ota aina huomioon toimipisteen aikavyöhyke tulkittaessa paikallisia aikaleimoja