Payment Processing Logic - ProKure Database Documentation

Payment Workflow Overview

Payments are processed after invoices pass three-way matching and approval. A single payment can cover multiple invoices, and partial payments are supported.

Approved Invoice

Create Payment

Allocate to Invoices

PENDING

APPROVED

PROCESSED

COMPLETED

VOIDED

5

Payment Methods

5

Status States

Multi

Invoice Allocation

Credit

Note Support

Payment Methods

Method	Description	Processing Time	Use Case
CHECK	Physical check issued to vendor	3-5 business days	Traditional vendors, large amounts
WIRE	Bank wire transfer	Same day - 2 days	International payments, urgent needs
ACH	Automated Clearing House transfer	1-3 business days	Domestic recurring payments
CREDIT_CARD	Corporate credit card payment	Immediate	Small purchases, vendor accepts cards
CASH	Cash payment (petty cash)	Immediate	Small local purchases

Database Schema

finance.payments

id, payment_number - Primary key and unique reference
company_id, vendor_id - Key relationships
payment_date - Date of payment
payment_method - CHECK, WIRE, ACH, CREDIT_CARD, CASH
payment_reference - Check number, wire reference, etc.
bank_account_id - Source bank account
total_amount, currency_code, exchange_rate
bank_name, bank_account_number, bank_routing_number - Snapshot
status - PENDING, APPROVED, PROCESSED, COMPLETED, VOIDED
approved_by, approved_at
processed_at - When payment was executed
voided_at, void_reason - If voided

finance.payment_allocations

payment_id - Parent payment reference
invoice_id - Invoice being paid
allocated_amount - Amount applied to this invoice
discount_taken - Early payment discount captured
Unique constraint on (payment_id, invoice_id)

finance.credit_notes

credit_note_number, vendor_id, invoice_id
credit_note_date
reason_code - RETURN, PRICE_ADJUSTMENT, QUALITY_ISSUE, SHORT_SHIPMENT
reason_description
subtotal, tax_amount, total_amount
applied_amount - Amount applied against invoices
balance_amount - Remaining credit balance
status - DRAFT, APPROVED, APPLIED

finance.debit_notes

debit_note_number, vendor_id, invoice_id
debit_note_date
reason_code - Additional charges, corrections
reason_description
subtotal, tax_amount, total_amount
status - DRAFT, APPROVED

Payment Processing Steps

1

Select Invoices for Payment

Select approved, unpaid invoices for a vendor. System shows due dates and available early payment discounts.

-- Get payable invoices for vendor
SELECT
    vi.id,
    vi.invoice_number,
    vi.vendor_invoice_number,
    vi.total_amount,
    vi.paid_amount,
    vi.total_amount - vi.paid_amount AS balance_due,
    vi.due_date,
    pt.discount_percent,
    pt.discount_days,
    CASE
        WHEN vi.invoice_date + pt.discount_days >= CURRENT_DATE
        THEN ROUND((vi.total_amount - vi.paid_amount) * pt.discount_percent / 100, 2)
        ELSE 0
    END AS available_discount
FROM finance.vendor_invoices vi
LEFT JOIN master.payment_terms pt ON vi.payment_terms_id = pt.id
WHERE vi.vendor_id = :vendor_id
  AND vi.status = 'APPROVED'
  AND vi.payment_status IN ('UNPAID', 'PARTIAL')
ORDER BY vi.due_date;

2

Create Payment with Allocations

Create payment header and allocate amounts to selected invoices. Apply credit notes if available.

-- Create payment
INSERT INTO finance.payments (
    payment_number, company_id, vendor_id, payment_date,
    payment_method, total_amount, currency_code, status
) VALUES (
    :payment_number, :company_id, :vendor_id, CURRENT_DATE,
    :payment_method, :total_amount, 'USD', 'PENDING'
) RETURNING id;

-- Create allocations
INSERT INTO finance.payment_allocations (
    payment_id, invoice_id, allocated_amount, discount_taken
) VALUES
    (:payment_id, :invoice_id_1, :amount_1, :discount_1),
    (:payment_id, :invoice_id_2, :amount_2, :discount_2);

3

Payment Approval

Payments above threshold require approval. Amount-based approval matrix determines required approvers.

Segregation of Duties

The payment creator cannot be the payment approver. System enforces this control automatically.

4

Process Payment

Execute the payment through banking system. Update payment status and record processing timestamp.

-- Process payment
UPDATE finance.payments
SET
    status = 'PROCESSED',
    processed_at = CURRENT_TIMESTAMP,
    payment_reference = :bank_reference
WHERE id = :payment_id;

5

Update Invoice Status

Update invoice paid amounts and payment status based on allocations.

-- Update invoice after payment
UPDATE finance.vendor_invoices vi
SET
    paid_amount = (
        SELECT COALESCE(SUM(pa.allocated_amount + pa.discount_taken), 0)
        FROM finance.payment_allocations pa
        JOIN finance.payments p ON pa.payment_id = p.id
        WHERE pa.invoice_id = vi.id
          AND p.status IN ('PROCESSED', 'COMPLETED')
    ),
    payment_status = CASE
        WHEN paid_amount >= total_amount THEN 'PAID'
        WHEN paid_amount > 0 THEN 'PARTIAL'
        ELSE 'UNPAID'
    END,
    balance_amount = total_amount - paid_amount
WHERE vi.id IN (
    SELECT invoice_id FROM finance.payment_allocations
    WHERE payment_id = :payment_id
);

Early Payment Discounts

Capture early payment discounts based on payment terms (e.g., 2/10 Net 30 = 2% discount if paid within 10 days):

Discount Calculation

discount_percent from payment_terms
discount_days from invoice_date
If payment_date <= invoice_date + discount_days
Then discount = balance * discount_percent%

Recording

payment_allocations.discount_taken
Reduces amount owed by vendor
Invoice considered fully paid
Discount tracked for reporting

Credit & Debit Notes

Credit Notes (From Vendor)

Reduce amount owed to vendor:

RETURN - Goods returned
PRICE_ADJUSTMENT - Price reduction
QUALITY_ISSUE - Defect allowance
SHORT_SHIPMENT - Quantity shortage

Credit balance can be applied to future payments.

Debit Notes (To Vendor)

Increase amount owed by vendor:

PENALTY - Late delivery charges
CORRECTION - Invoice errors
RECOVERY - Recoverable costs

Debit notes added to vendor's payable balance.

Payment Voiding

Void Conditions

Payments can only be voided if: (1) Check not yet cashed, (2) Wire can be recalled, (3) Within void window. Voiding requires approval and reason.

-- Void payment and reverse allocations
BEGIN;

-- Void the payment
UPDATE finance.payments
SET
    status = 'VOIDED',
    voided_at = CURRENT_TIMESTAMP,
    void_reason = :reason
WHERE id = :payment_id;

-- Reverse invoice paid amounts
UPDATE finance.vendor_invoices vi
SET
    paid_amount = paid_amount - pa.allocated_amount - pa.discount_taken,
    payment_status = CASE
        WHEN paid_amount - pa.allocated_amount - pa.discount_taken = 0 THEN 'UNPAID'
        ELSE 'PARTIAL'
    END
FROM finance.payment_allocations pa
WHERE pa.payment_id = :payment_id
  AND pa.invoice_id = vi.id;

COMMIT;

Query Examples

Vendor Payment Summary

SELECT
    v.vendor_name,
    COUNT(DISTINCT vi.id) AS invoices_paid,
    SUM(pa.allocated_amount) AS total_paid,
    SUM(pa.discount_taken) AS discounts_captured,
    MAX(p.payment_date) AS last_payment_date
FROM finance.payments p
JOIN finance.payment_allocations pa ON p.id = pa.payment_id
JOIN finance.vendor_invoices vi ON pa.invoice_id = vi.id
JOIN vendor.vendors v ON p.vendor_id = v.id
WHERE p.status = 'COMPLETED'
  AND p.payment_date >= :start_date
GROUP BY v.id, v.vendor_name
ORDER BY total_paid DESC;

Aging of Unpaid Invoices

SELECT
    v.vendor_name,
    vi.invoice_number,
    vi.total_amount - vi.paid_amount AS balance_due,
    vi.due_date,
    CURRENT_DATE - vi.due_date AS days_overdue,
    CASE
        WHEN CURRENT_DATE <= vi.due_date THEN 'CURRENT'
        WHEN CURRENT_DATE - vi.due_date <= 30 THEN '1-30 DAYS'
        WHEN CURRENT_DATE - vi.due_date <= 60 THEN '31-60 DAYS'
        WHEN CURRENT_DATE - vi.due_date <= 90 THEN '61-90 DAYS'
        ELSE '90+ DAYS'
    END AS aging_bucket
FROM finance.vendor_invoices vi
JOIN vendor.vendors v ON vi.vendor_id = v.id
WHERE vi.payment_status IN ('UNPAID', 'PARTIAL')
  AND vi.status = 'APPROVED'
ORDER BY vi.due_date;

Best Practices

Timely Processing

Process payments to capture early payment discounts. Monitor discount expiry dates and prioritize accordingly.

Reconciliation

Regularly reconcile payment records with bank statements. Investigate and resolve any discrepancies promptly.

Fraud Prevention

Verify vendor bank details before processing. Watch for duplicate payments. Implement segregation of duties.

Payment Processing & Allocation

Payment Workflow Overview

Payment Methods

Database Schema

finance.payments

finance.payment_allocations

finance.credit_notes

finance.debit_notes

Payment Processing Steps

Select Invoices for Payment

Create Payment with Allocations

Payment Approval

Segregation of Duties

Process Payment

Update Invoice Status

Early Payment Discounts

Discount Calculation

Recording

Credit & Debit Notes

Credit Notes (From Vendor)

Debit Notes (To Vendor)

Payment Voiding

Void Conditions

Query Examples

Vendor Payment Summary

Aging of Unpaid Invoices

Best Practices

Timely Processing

Reconciliation

Fraud Prevention