This project re-engineers the Loan Origination Process for BPR Bank, focusing on improving efficiency, reducing processing times, and enhancing customer satisfaction. By automating workflows and streamlining the loan application journey, we aim to reduce financial losses from abandoned applications and achieve better Customer Satisfaction Index (CSI) scores.
GitHub Repository: Thur_Falcons_Bank_Loan_Reingeering
- Project Overview
- Objectives
- Project Scope
- Entity Relationship and Data Model
- Database Creation and SQL Code
- Use Case Scenarios
- Future Enhancements
- Contributing
- License
The Bank Loan Origination Process Re-engineering project addresses customer dissatisfaction and financial losses caused by delays in loan application processing. The re-engineered process leverages automation to:
- Reduce the Average Time Between Loan Application and Funding (ATBLA).
- Increase the Customer Satisfaction Index (CSI).
- Minimize financial losses from abandoned applications.
- Reduce ATBLA: Process and approve loan applications within 2 hours.
- Enhance Customer Satisfaction: Achieve a CSI of 80% by improving process efficiency.
- Minimize Financial Losses: Reduce losses from abandoned applications, currently estimated at Rwf 40,000,000 annually.
- Manual, inefficient, and time-consuming processes.
- Low CSI scores due to prolonged delays.
- High financial losses from abandoned applications.
- Automated workflows reduce manual interventions.
- Faster application approvals with integrated credit checks and quality control.
- Enhanced customer satisfaction with real-time notifications and feedback.
The project employs a relational database model. Below are the core entities and their relationships:
- Customers: Submit loan applications.
- Loan Applications: Track details of customer loan requests.
- Loans: Store details of approved loans.
- Branches: Represent physical bank locations.
- Feedback: Collect customer feedback post-loan approval.
- Application Processing: Log each step of the loan application process.
- Financial Losses: Track abandoned applications causing financial losses.
- Credit Checks: Store results of credit evaluations.
(Include a link or image to an ERD diagram if possible)
Below are the SQL scripts for creating the database schema, inserting sample data, and setting up a pluggable Oracle database.
CREATE TABLE Branch (
branchID INT PRIMARY KEY,
branchName VARCHAR(100) NOT NULL,
location VARCHAR(100) NOT NULL,
phone VARCHAR(20) NOT NULL
);CREATE TABLE Customer (
customerID INT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
address VARCHAR(255) NOT NULL,
phone VARCHAR(20) NOT NULL,
email VARCHAR(100) NOT NULL,
nationalID VARCHAR(20) UNIQUE NOT NULL,
CSI_score INT NOT NULL
);CREATE TABLE LoanApplication (
applicationID INT PRIMARY KEY,
customerID INT NOT NULL,
loanAmount DECIMAL(15, 2) NOT NULL,
applicationDate DATE NOT NULL,
status VARCHAR(50) NOT NULL,
branchID INT NOT NULL,
documentLinks VARCHAR(255),
CONSTRAINT fk_LoanApplication_Customer FOREIGN KEY (customerID) REFERENCES Customer(customerID),
CONSTRAINT fk_LoanApplication_Branch FOREIGN KEY (branchID) REFERENCES Branch(branchID)
);CREATE TABLE Loan (
loanID INT PRIMARY KEY,
applicationID INT NOT NULL,
loanAmount DECIMAL(15, 2) NOT NULL,
interestRate FLOAT NOT NULL,
termMonths INT NOT NULL,
approvalDate DATE NOT NULL,
fundingDate DATE NOT NULL,
loanStatus VARCHAR(50) NOT NULL,
CONSTRAINT fk_Loan_Application FOREIGN KEY (applicationID) REFERENCES LoanApplication(applicationID)
);
CREATE TABLE Feedback (
feedbackID INT PRIMARY KEY,
customerID INT NOT NULL,
loanID INT NOT NULL,
score INT NOT NULL,
comments VARCHAR(255),
feedbackDate DATE NOT NULL,
CONSTRAINT fk_Feedback_Customer FOREIGN KEY (customerID) REFERENCES Customer(customerID),
CONSTRAINT fk_Feedback_Loan FOREIGN KEY (loanID) REFERENCES Loan(loanID)
);Other tables like ApplicationProcessing, CreditCheck, and FinancialLoss can be similarly defined. See full database schema.
INSERT INTO Branch (branchID, branchName, location, phone)
VALUES (1, 'Kigali Branch', 'Kigali, Rwanda', '+250788000000');
INSERT INTO Customer (customerID, name, address, phone, email, nationalID, CSI_score)
VALUES (1, 'Shema Ken', 'Kigali', '+250788111222', 'kenshema@gmail.com', '079023456789', 75);CREATE PLUGGABLE DATABASE Thur_Falcons_Bank
ADMIN USER admin IDENTIFIED BY Password123
FILE_NAME_CONVERT = ('/u01/app/oracle/oradata/pdbseed/',
'/u01/app/oracle/oradata/Thur_Falcons_Bank/');A cross join combines all rows from two tables, resulting in a Cartesian product.
-- Cross join between Customers and LoanApplications
SELECT Customers.customerID, Customers.name, LoanApplications.applicationID, LoanApplications.loanAmount
FROM Customers
CROSS JOIN LoanApplications;
-- Inner join between Customers and LoanApplications
SELECT Customers.customerID, Customers.name, LoanApplications.loanAmount
FROM Customers
INNER JOIN LoanApplications
ON Customers.customerID = LoanApplications.customerID;
Find loan applications submitted by specific customers.
-- Left join between Customers and LoanApplications
SELECT Customers.customerID, Customers.name, LoanApplications.loanAmount
FROM Customers
LEFT JOIN LoanApplications
ON Customers.customerID = LoanApplications.customerID;
-- Right join between Customers and LoanApplications
SELECT Customers.customerID, Customers.name, LoanApplications.loanAmount
FROM Customers
RIGHT JOIN LoanApplications
ON Customers.customerID = LoanApplications.customerID; -- Full outer join between Customers and LoanApplications
SELECT Customers.customerID, Customers.name, LoanApplications.loanAmount
FROM Customers
FULL OUTER JOIN LoanApplications
ON Customers.customerID = LoanApplications.customerID; Use Case: Find customers who have applied for loans and customers who haven’t, as well as applications with no associated customer records.
-- Self join to compare loan amounts within the same table
SELECT A.loanID AS Loan1, B.loanID AS Loan2, A.loanAmount, B.loanAmount
FROM Loan A, Loan B
WHERE A.loanAmount > B.loanAmount; -- Natural join between Customers and LoanApplications
SELECT *
FROM Customers
NATURAL JOIN LoanApplications;Ensuring Consistency and Reliability Transaction Example A transaction ensures atomicity, consistency, isolation, and durability (ACID).
-- Transaction to process a loan application and update customer status
BEGIN TRANSACTION;
-- Step 1: Insert a new loan application
INSERT INTO LoanApplications (applicationID, customerID, loanAmount, status)
VALUES (101, 1, 5000, 'Pending');
-- Update the customer’s account status
UPDATE Customers
SET accountStatus = 'Loan Processing'
WHERE customerID = 1;
-- Commit the transaction if successful
COMMIT;
-- Rollback the transaction if any error occurs
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END;
/BEGIN TRANSACTION;
-- Update customer account balance
UPDATE Customers
SET accountBalance = accountBalance - 200
WHERE customerID = 1;
-- Insert a new loan disbursement
INSERT INTO Loan (loanID, customerID, loanAmount, disbursementDate, loanStatus)
VALUES (201, 1, 5000, SYSDATE, 'Active');
-- Rollback to Step1 if Step2 fails
ROLLBACK TO Step1;
COMMIT;
/
Implementation Code
DDL Example: Creating Tables
sql
CREATE TABLE Customers (
customerID INT PRIMARY KEY,
name VARCHAR(100),
accountBalance NUMBER,
accountStatus VARCHAR(20)
);
CREATE TABLE LoanApplications (
applicationID INT PRIMARY KEY,
customerID INT,
loanAmount NUMBER,
status VARCHAR(20),
FOREIGN KEY (customerID) REFERENCES Customers(customerID)
);
CREATE TABLE Loan (
loanID INT PRIMARY KEY,
customerID INT,
loanAmount NUMBER,
disbursementDate DATE,
loanStatus VARCHAR(20),
FOREIGN KEY (customerID) REFERENCES Customers(customerID)
);
### Auditing table
```sql
CREATE TABLE AuditLog (
logID INT PRIMARY KEY,
action VARCHAR(50),
table_name VARCHAR(50),
old_value VARCHAR(200),
new_value VARCHAR(200),
modified_by VARCHAR(50),
timestamp DATE
);INSERT INTO Customers (customerID, name, accountBalance, accountStatus)
VALUES (1, 'John Doe', 1000, 'Active');
INSERT INTO LoanApplications (applicationID, customerID, loanAmount, status)
VALUES (1, 1, 5000, 'Pending');#PHASE 7
The loan origination process at BPR Bank is inefficient, leading to delays, financial losses, and data inconsistencies. Key challenges include:
#Features and Implementation
CREATE OR REPLACE TRIGGER before_loan_insert
BEFORE INSERT ON loan
FOR EACH ROW
BEGIN
IF NOT EXISTS (SELECT 1 FROM CreditCheck WHERE applicationID = :NEW.applicationID) THEN
RAISE_APPLICATION_ERROR(-20001, 'Loan cannot be processed without a credit check.');
END IF;
END;
/ A compound trigger ensures transactional consistency for multi-row operations in the LoanApplication table.
CREATE OR REPLACE TRIGGER loan_application_trigger
FOR INSERT OR UPDATE OR DELETE ON LoanApplication
COMPOUND TRIGGER
TYPE application_row IS RECORD (
applicationID LoanApplication.applicationID%TYPE,
status LoanApplication.status%TYPE
);BEGIN
-- Audit changes before any row modification
INSERT INTO AuditLog (action, applicationID, old_status, new_status, timestamp)
VALUES ('BEFORE UPDATE', :OLD.applicationID, :OLD.status, :NEW.status, SYSDATE);
END BEFORE EACH ROW; AFTER EACH ROW IS
BEGIN
-- Process status changes after the operation
IF :NEW.status = 'Approved' THEN
```
INSERT INTO Loan (applicationID, loanAmount, approvalDate, loanStatus)
VALUES (:NEW.applicationID, :NEW.loanAmount, SYSDATE, 'Active');
END IF;
END AFTER EACH ROW;
END loan_application_trigger;
/
Explicit cursors are implemented for batch updates and row-by-row processing scenarios.
CREATE OR REPLACE PROCEDURE update_loan_status IS
CURSOR loan_cursor IS
SELECT loanID, loanStatus FROM Loan WHERE loanStatus = 'Pending';
loan_rec loan_cursor%ROWTYPE;
BEGIN
OPEN loan_cursor;
LOOP
FETCH loan_cursor INTO loan_rec;
EXIT WHEN loan_cursor%NOTFOUND;
UPDATE Loan SET loanStatus = 'Processed' WHERE loanID = loan_rec.loanID;
END LOOP;
CLOSE loan_cursor;
END;
/ Functions encapsulate logic for specific tasks like interest rate calculation.
CREATE OR REPLACE FUNCTION calculate_interest(
p_loanAmount IN NUMBER,
p_interestRate IN FLOAT
) RETURN NUMBER IS
BEGIN
RETURN (p_loanAmount * p_interestRate) / 100;
END calculate_interest;
/ DECLARE
loan_rec Loan%ROWTYPE;
BEGIN
SELECT * INTO loan_rec FROM Loan WHERE loanID = 101;
DBMS_OUTPUT.PUT_LINE('Loan Amount: ' || loan_rec.loanAmount);
END;
/ Packages organize related procedures and functions.
CREATE OR REPLACE PACKAGE loan_package IS
PROCEDURE approve_loan(p_applicationID INT);
FUNCTION get_loan_status(p_loanID INT) RETURN VARCHAR2;
END loan_package;CREATE OR REPLACE PACKAGE BODY loan_package IS
PROCEDURE approve_loan(p_applicationID INT) IS
BEGIN
UPDATE LoanApplication
SET status = 'Approved'
WHERE applicationID = p_applicationID;
END;
FUNCTION get_loan_status(p_loanID INT) RETURN VARCHAR2 IS
loan_status VARCHAR2(50);
BEGIN
SELECT loanStatus INTO loan_status FROM Loan WHERE loanID = p_loanID;
RETURN loan_status;
END;
END loan_package;
/
Auditing ensures tracking of sensitive data changes.
CREATE OR REPLACE TRIGGER audit_trigger
AFTER UPDATE ON Customer
FOR EACH ROW
BEGIN
INSERT INTO AuditLog (action, table_name, old_value, new_value, modified_by, timestamp)
VALUES ('UPDATE', 'Customer', :OLD.name, :NEW.name, USER, SYSDATE);
END;
/ ##Limitations
####Full SQL scripts for table creation, data insertion, and PL/SQL components can be found on GitHub:
Author: Thur Falcons Team
Year: 2024
