You are given a ledger of posted transactions and a bank feed of cleared transactions. Write a PostgreSQL query that helps you reconcile the two sources by returning every ledger transaction that is either missing from the bank feed or has a different cleared amount. Include the ledger amount, bank amount, and a discrepancy status so you can investigate exceptions quickly.
| Column | Type | Description |
|---|---|---|
| ledger_txn_idPK | INT | Primary key for the ledger record |
| account_id | INT | Account tied to the transaction |
| txn_date | DATE | Date the transaction was posted |
| reference_code | VARCHAR(20) | Reconciliation reference code |
| ledger_amount | NUMERIC(12,2) | Amount recorded in the ledger |
| status | VARCHAR(20) | Posting status such as posted or pending |
| Column | Type | Description |
|---|---|---|
| bank_txn_idPK | INT | Primary key for the bank record |
| account_id | INT | Account tied to the cleared transaction |
| cleared_date | DATE | Date the bank cleared the transaction |
| reference_code | VARCHAR(20) | Reference code used to match the ledger |
| bank_amount | NUMERIC(12,2) | Amount cleared by the bank |
| source_system | VARCHAR(20) | Feed source for the bank record |