Back to ER Diagram
Purchase Requisition

Purchase Requisition Processing

Complete documentation of the PR workflow from creation through approval to conversion into RFQs and Purchase Orders. Includes status transitions, approval routing, and item lifecycle management.

PostgreSQL
10 Tables
Schema: pr
Multi-Level Approval

PR Lifecycle Overview

A Purchase Requisition (PR) is the formal internal document that initiates the procurement process. It flows through creation, approval, and conversion stages before goods are ultimately ordered.

DRAFT
PENDING_APPROVAL
APPROVED
Convert to RFQ
Create PO
CLOSED
REJECTED
CANCELLED
4
PR Types
3
Priority Levels
6
Status States
N
Approval Levels

PR Status States

Status Description Allowed Actions Next States
DRAFT Initial state. PR is being prepared by requester. Edit, Add Items, Delete, Submit PENDING_APPROVAL
PENDING_APPROVAL Submitted for approval. Waiting at current_approval_level. Approve, Reject, Recall (by requester) APPROVED, REJECTED, DRAFT (recall)
APPROVED Fully approved. Ready for sourcing/ordering. Create RFQ, Create PO, Cancel CLOSED, CANCELLED
REJECTED Rejected by an approver. Requires revision. Revise & Resubmit, Cancel DRAFT, CANCELLED
CANCELLED Permanently cancelled. No further actions. None (Terminal) -
CLOSED All items ordered. PR lifecycle complete. View Only (Terminal) -

PR Types

STANDARD

Regular purchase requisition for routine procurement needs. Follows standard approval workflow based on amount thresholds.

URGENT

Time-sensitive requirement. May bypass certain approval levels or have expedited workflow. Requires justification.

BLANKET

Pre-approved requisition for recurring needs over a period. Items can be released against the blanket PR as needed.

CAPITAL

For capital expenditure items (assets). Requires additional approval levels and may need budget committee sign-off.

Database Schema

pr.purchase_requests

  • id - UUID primary key
  • pr_number - Unique PR number (auto-generated)
  • company_id, department_id - Organizational context
  • requester_id - User who created the PR
  • cost_center_id, project_id - Budget allocation
  • pr_date, required_date - Key dates
  • priority - LOW, MEDIUM, HIGH, CRITICAL
  • pr_type - STANDARD, URGENT, BLANKET, CAPITAL
  • status - Current workflow status
  • current_approval_level - Tracks approval progress
  • total_amount - Sum of all line items
  • custom_data - JSONB for extensibility

pr.pr_items

  • pr_id - Parent PR reference
  • line_number - Sequential line number
  • item_id - Reference to inventory.items (optional)
  • item_description - Free text description
  • quantity, uom_id - Quantity and unit
  • estimated_unit_price - Budget estimate
  • suggested_vendor_id - Preferred vendor (optional)
  • status - PENDING, IN_RFQ, ORDERED, CANCELLED
  • rfq_id, po_id - Conversion references
  • gl_account_id, cost_center_id - Accounting

pr.pr_approvals

  • pr_id - Parent PR reference
  • approval_level - Level in approval chain
  • approver_id - Assigned approver
  • delegate_id - Delegated approver (if any)
  • action - APPROVED, REJECTED, RETURNED
  • action_date - When action was taken
  • comments - Approver's notes
  • status - PENDING, APPROVED, REJECTED
  • due_date - SLA deadline for approval
  • reminded_at - Last reminder sent

pr.pr_attachments

File attachments associated with purchase requests, such as specifications, quotes, or supporting documents.

  • id - UUID primary key
  • pr_id - Parent PR reference
  • file_name - Original uploaded file name
  • file_path - Storage path or URL to the file
  • file_type - MIME type (e.g., application/pdf, image/png)
  • file_size - Size in bytes
  • uploaded_by - User who uploaded the attachment
  • uploaded_at - Timestamp of upload
  • description - Optional description or label for the file
  • is_deleted - Soft-delete flag for attachment removal

pr.pr_revisions

Tracks revision history when a PR is modified after rejection or recalled for changes. Preserves an audit trail of all previous versions.

  • id - UUID primary key
  • pr_id - Parent PR reference
  • revision_number - Sequential revision counter
  • revised_by - User who made the revision
  • revised_at - Timestamp of revision
  • change_summary - Description of what changed
  • previous_total_amount - Total amount before revision
  • new_total_amount - Total amount after revision
  • previous_status - PR status before revision
  • snapshot_data - JSONB snapshot of the PR state before changes

pr.pr_budget_checks

Records budget availability check results performed during PR submission or approval. Ensures requested amounts fall within allocated budgets.

  • id - UUID primary key
  • pr_id - Parent PR reference
  • cost_center_id - Cost center checked against
  • budget_period_id - Budget period reference
  • allocated_amount - Total budget allocated for the period
  • committed_amount - Amount already committed from prior PRs
  • requested_amount - Amount requested in this PR
  • available_amount - Remaining budget after commitments
  • check_result - PASS, FAIL, WARNING
  • checked_at - Timestamp of the budget check
  • checked_by - User or system that performed the check
  • override_approved_by - Approver who authorized budget override (if any)

pr.pr_consolidations

Consolidation records for batching multiple approved PRs into a single sourcing event. Groups similar items across PRs to achieve volume discounts.

  • id - UUID primary key
  • consolidation_number - Unique consolidation reference number
  • company_id - Company context for the consolidation
  • created_by - Procurement officer who initiated consolidation
  • created_at - Timestamp of consolidation creation
  • status - DRAFT, CONFIRMED, CONVERTED, CANCELLED
  • pr_ids - Array of PR IDs included in this consolidation
  • total_items - Count of consolidated line items
  • total_amount - Combined value of consolidated items
  • rfq_id - Resulting RFQ created from consolidation (if any)
  • notes - Consolidation notes or rationale

pr.pr_short_closures

Records short closure of partially fulfilled PRs. When remaining items are no longer needed, a short closure finalizes the PR without ordering the full quantity.

  • id - UUID primary key
  • pr_id - Parent PR reference
  • pr_item_id - Specific line item being short-closed (optional)
  • original_quantity - Originally requested quantity
  • ordered_quantity - Quantity already ordered
  • closed_quantity - Remaining quantity being short-closed
  • reason - Justification for short closure
  • closed_by - User who initiated the short closure
  • closed_at - Timestamp of short closure
  • approved_by - Approver who authorized the short closure
  • approved_at - Timestamp of short closure approval
  • status - PENDING, APPROVED, REJECTED

Approval Workflow

1

PR Submission

When a PR is submitted, the system determines the approval chain based on the approval matrix configuration. Factors include: total amount, department, cost center, and PR type.

-- On PR submission, determine approval levels
UPDATE pr.purchase_requests
SET status = 'PENDING_APPROVAL',
    submitted_at = CURRENT_TIMESTAMP,
    current_approval_level = 1
WHERE id = :pr_id;
2

Create Approval Records

For each required approval level, a pr_approvals record is created with the designated approver and SLA due date.

-- Create approval records for each level
INSERT INTO pr.pr_approvals (pr_id, approval_level, approver_id, due_date, status)
SELECT
    :pr_id,
    al.level_number,
    CASE al.approver_type
        WHEN 'DEPARTMENT_HEAD' THEN d.head_id
        WHEN 'COST_CENTER_OWNER' THEN cc.owner_id
        ELSE al.approver_id
    END,
    CURRENT_TIMESTAMP + (al.sla_hours || ' hours')::INTERVAL,
    'PENDING'
FROM approval.approval_levels al
JOIN approval.approval_matrix am ON al.matrix_id = am.id
WHERE am.document_type = 'PR'
  AND :total_amount BETWEEN am.min_amount AND am.max_amount;
3

Level-by-Level Approval

Each approver reviews and takes action. When approved, current_approval_level increments. When all levels complete, PR status changes to APPROVED.

-- On approval action
UPDATE pr.pr_approvals
SET action = 'APPROVED',
    action_date = CURRENT_TIMESTAMP,
    status = 'APPROVED',
    comments = :comments
WHERE pr_id = :pr_id AND approval_level = :current_level;

-- Check if more levels exist
UPDATE pr.purchase_requests
SET current_approval_level = current_approval_level + 1,
    status = CASE
        WHEN current_approval_level >= :max_level THEN 'APPROVED'
        ELSE 'PENDING_APPROVAL'
    END,
    approved_at = CASE
        WHEN current_approval_level >= :max_level THEN CURRENT_TIMESTAMP
        ELSE NULL
    END
WHERE id = :pr_id;
4

Rejection Handling

If any approver rejects, the entire PR is rejected. The rejection_reason is captured and the requester is notified to revise and resubmit.

Rejection Rules

A rejection at any level halts the workflow. The PR returns to REJECTED status and must be revised before resubmission. Previous approval records are preserved for audit.

PR Item Status Tracking

Each PR line item has its own status to track progression through sourcing and ordering:

PENDING
IN_RFQ
ORDERED
CANCELLED
Item Status Description Reference Updated
PENDING Item approved but not yet sourced -
IN_RFQ Item included in an RFQ for quotations rfq_id populated
ORDERED Item converted to a Purchase Order po_id populated
CANCELLED Item cancelled (not needed) -

PR Closure Logic

A PR automatically transitions to CLOSED status when all line items have status = ORDERED or CANCELLED. This is evaluated after each PO creation or item cancellation.

Conversion to RFQ / PO

PR to RFQ

Selected PR items can be grouped into an RFQ for competitive bidding:

  • Select items from approved PR
  • Create RFQ with pr_id reference
  • Copy item details to rfq_items
  • Update pr_items.rfq_id
  • Set pr_items.status = 'IN_RFQ'

PR to PO (Direct)

For known vendors or contract items, skip RFQ:

  • Select items from approved PR
  • Create PO with pr_id reference
  • Copy item details to po_items
  • Update pr_items.po_id
  • Set pr_items.status = 'ORDERED'

Query Examples

Pending Approvals for User

SELECT pr.pr_number, pr.total_amount, pr.pr_date,
       u.full_name AS requester,
       pa.approval_level, pa.due_date
FROM pr.pr_approvals pa
JOIN pr.purchase_requests pr ON pa.pr_id = pr.id
JOIN admin.users u ON pr.requester_id = u.id
WHERE pa.approver_id = :user_id
  AND pa.status = 'PENDING'
  AND pa.approval_level = pr.current_approval_level
ORDER BY pa.due_date;

PR Items Ready for Ordering

SELECT pr.pr_number, pi.line_number, pi.item_description,
       pi.quantity, uom.symbol, pi.estimated_total,
       v.vendor_name AS suggested_vendor
FROM pr.pr_items pi
JOIN pr.purchase_requests pr ON pi.pr_id = pr.id
JOIN master.units_of_measure uom ON pi.uom_id = uom.id
LEFT JOIN vendor.vendors v ON pi.suggested_vendor_id = v.id
WHERE pr.status = 'APPROVED'
  AND pi.status = 'PENDING'
ORDER BY pr.required_date;

PR Conversion Summary

SELECT
    pr.pr_number,
    pr.status AS pr_status,
    COUNT(*) AS total_items,
    SUM(CASE WHEN pi.status = 'PENDING' THEN 1 ELSE 0 END) AS pending,
    SUM(CASE WHEN pi.status = 'IN_RFQ' THEN 1 ELSE 0 END) AS in_rfq,
    SUM(CASE WHEN pi.status = 'ORDERED' THEN 1 ELSE 0 END) AS ordered,
    SUM(CASE WHEN pi.status = 'CANCELLED' THEN 1 ELSE 0 END) AS cancelled
FROM pr.purchase_requests pr
JOIN pr.pr_items pi ON pr.id = pi.pr_id
WHERE pr.company_id = :company_id
GROUP BY pr.id, pr.pr_number, pr.status;

Best Practices

Requester Guidance

Provide detailed item descriptions and specifications. Include required_date for urgency prioritization. Suggest vendors when known to speed sourcing.

Approval SLAs

Configure reasonable SLA hours per approval level. Enable escalation for overdue approvals. Send reminders before SLA breach.

Budget Validation

Validate budget availability before PR submission. Track committed amounts from approved PRs against cost center budgets.

Purchase Order RFQ & Quotation Approval Workflow ER Diagrams