Bank Transaction Processing System
Here's a **Bank Transaction Processing System** project that combines COBOL, PL/SQL, and Unix/Linux. This project demonstrates legacy system integration, batch processing, and database management.
### **Project Overview**
A banking system that processes transactions in batch mode, updates account balances in an Oracle database, and generates reports. Components:
1. **COBOL** - Batch transaction processing
2. **PL/SQL** - Database operations and stored procedures
3. **Unix Shell Scripting** - Automation and file management
---
### **1. System Architecture**
```
[Transaction Files] →
Unix Script (File Validation) →
COBOL Program (Processing) →
PL/SQL Procedures (DB Update) →
Unix Report Generator
```
---
### **2. Technology Stack**
- **COBOL** (GnuCOBOL on Unix)
- **Oracle Database** (PL/SQL)
- **Unix Shell Scripting**
- **SQL*Plus** for DB connectivity
- **cron** for job scheduling
---
### **3. Database Setup (PL/SQL)**
**Tables:**
```sql
CREATE TABLE accounts (
account_id NUMBER PRIMARY KEY,
balance NUMBER(15,2),
last_updated DATE
);
CREATE TABLE transactions (
txn_id NUMBER PRIMARY KEY,
account_id NUMBER,
txn_type VARCHAR2(10), -- 'DEPOSIT'/'WITHDRAWAL'
amount NUMBER(15,2),
txn_date DATE
);
```
**Stored Procedure:**
```sql
CREATE OR REPLACE PROCEDURE update_account(
p_account_id IN NUMBER,
p_amount IN NUMBER,
p_txn_type IN VARCHAR2
) IS
BEGIN
IF p_txn_type = 'DEPOSIT' THEN
UPDATE accounts
SET balance = balance + p_amount
WHERE account_id = p_account_id;
ELSIF p_txn_type = 'WITHDRAWAL' THEN
UPDATE accounts
SET balance = balance - p_amount
WHERE account_id = p_account_id;
END IF;
COMMIT;
END;
```
---
### **4. COBOL Program (transaction_processor.cbl)**
```cobol
IDENTIFICATION DIVISION.
PROGRAM-ID. TRANSACTION-PROCESSOR.
ENVIRONMENT DIVISION.
INPUT-OUTPUT SECTION.
FILE-CONTROL.
SELECT TRANSACTION-FILE ASSIGN TO 'transactions.dat'
ORGANIZATION IS LINE SEQUENTIAL.
SELECT ERROR-FILE ASSIGN TO 'errors.dat'.
DATA DIVISION.
FILE SECTION.
FD TRANSACTION-FILE.
01 TXN-RECORD.
88 END-OF-FILE VALUE HIGH-VALUES.
02 TXN-ACCOUNT-ID PIC 9(10).
02 TXN-TYPE PIC X(10).
02 TXN-AMOUNT PIC 9(10)V99.
FD ERROR-FILE.
01 ERROR-RECORD PIC X(100).
WORKING-STORAGE SECTION.
01 WS-CURRENT-DATE PIC X(10) VALUE '2023-10-01'.
PROCEDURE DIVISION.
MAIN-LOGIC.
OPEN INPUT TRANSACTION-FILE
OPEN OUTPUT ERROR-FILE
PERFORM PROCESS-RECORDS UNTIL END-OF-FILE
CLOSE TRANSACTION-FILE ERROR-FILE
STOP RUN.
PROCESS-RECORDS.
READ TRANSACTION-FILE
AT END SET END-OF-FILE TO TRUE
END-READ
IF NOT END-OF-FILE
PERFORM VALIDATE-TRANSACTION
IF VALID-TRANSACTION
CALL 'UPDATE_DB' USING TXN-ACCOUNT-ID, TXN-AMOUNT, TXN-TYPE
ELSE
WRITE ERROR-RECORD FROM TXN-RECORD
END-IF
END-IF.
VALIDATE-TRANSACTION.
... (Add validation logic) ...
```
---
### **5. Unix Components**
**Main Controller Script (process_transactions.sh):**
```sh
#!/bin/bash
# 1. Move incoming file to processing directory
mv /incoming/txns.dat /processing/transactions_$(date +%Y%m%d%H%M).dat
# 2. Run COBOL program
cobc -x transaction_processor.cbl
./transaction_processor
# 3. Call PL/SQL procedure using SQL*Plus
sqlplus -s user/password@db <<EOF
EXEC process_transactions;
COMMIT;
EOF
# 4. Generate report
awk '{print $1,$2,$3}' processed.dat | mail -s "Daily Transaction Report" admin@bank.com
# 5. Cleanup
mv /processing/*.dat /archive/
```
**cron Job (daily at 2 AM):**
```
0 2 * * * /path/to/process_transactions.sh > /var/log/txn_processing.log 2>&1
```
---
### **6. Key Features**
1. **Batch Processing**: Nightly processing of transactions
2. **Data Validation**:
- COBOL validates record structure
- PL/SQL validates account existence
3. **Error Handling**:
- COBOL writes invalid records to error file
- Shell script sends email alerts for errors
4. **Audit Trail**:
- All transactions stored in database
- File archiving with timestamps
---
### **7. Portfolio Presentation Tips**
1. **Documentation**:
- Architecture diagram
- Data flow explanation
- Error handling strategy
2. **Code Samples**:
- COBOL validation logic
- PL/SQL stored procedure
- Shell script automation
3. **Demo Setup**:
- Sample transaction file
- Database initialization script
- Test cases with expected outputs
4. **Technologies Highlight**:
- COBOL/Unix integration
- Database optimization in PL/SQL
- Shell scripting automation
---
### **8. Execution Flow**
1. New transactions arrive via SFTP to Unix server
2. Nightly cron job triggers shell script
3. COBOL program processes transactions
4. PL/SQL procedures update database
5. Reports generated and archived
6. System ready for next day's operations
This project demonstrates your ability to integrate legacy systems (COBOL) with modern databases (PL/SQL) through Unix automation - a valuable skill for financial systems modernization projects.