JPJayraj Patel
← All projects
CASE STUDY · BHN LOGISTICS (TOLARAM GROUP) · NIGERIA

Approve from Anywhere: Fleet Parts Requests

When COVID kept managers out of the office, I replaced a paper sign-off with an Oracle APEX app the fleet manager could approve from Slack on his phone.

Oracle APEX 19PL/SQLSlack APIPHPEmail approvalsLive dashboard

CONTEXT

BHN Logistics is the logistics company of the Tolaram group in Nigeria. Its warehouse repairs trucks, and every repair needs parts from inventory.

THE CHALLENGE

Parts were released against a paper request signed by the fleet manager. During COVID he couldn't be in the office all the time, so every request waited for a signature and trucks stayed off the road.

WHAT I BUILT

  1. RequestRequesters raise a parts request in Oracle APEX and attach documents or photos, stored in the database.
  2. Slack and emailPL/SQL calls the Slack API, so the fleet manager gets an interactive message on his phone with Approve, Reject and View. The same request also arrives by email with matching buttons.
  3. Approve or rejectApprove or Reject (with remarks) calls a PHP script, which updates the record through a PL/SQL procedure. View opens the request in APEX.
  4. Warehouse boardA big-screen status board in the warehouse refreshes every 2 minutes, so requesters see the moment a request is approved and go straight to inventory.

APEX 19 had no built-in approvals, so the whole flow was built in PL/SQL packages.

RELIABILITY

ARCHITECTURE · AS BUILT

Approval flow, as builtArchitecture as built: a requester raises a parts request in the Oracle APEX app; a PL/SQL package in the Oracle database sends it to the fleet manager through the Slack API and by email. On his phone he approves or rejects, which calls a PHP endpoint that updates the request through one PL/SQL procedure; View opens the request in APEX. The warehouse TV board reads the status from the database every two minutes. Requesterrequest + photos Oracle APEX appAPEX 19 Oracle DBPL/SQL packagedocuments · status Slack APIinteractive message Emailmatching buttons Fleet manageron his phoneApprove · Reject · View PHP endpointtoken-checked link PL/SQL procedureevery status change Warehouse TV boardrefreshes every 2 minutes View opens the request in APEX approve / reject status Approval flow, as builtArchitecture as built: a requester raises a parts request in the Oracle APEX app; a PL/SQL package in the Oracle database sends it to the fleet manager through the Slack API and by email. On his phone he approves or rejects, which calls a PHP endpoint that updates the request through one PL/SQL procedure; View opens the request in APEX. The warehouse TV board reads the status from the database every two minutes. Requesterrequest + photosOracle APEX appAPEX 19Oracle DBPL/SQL packageSlack APIinteractiveEmailbuttonsFleet manageron his phoneApprove · Reject · ViewPHP endpointtoken-checked linkPL/SQL procedureevery status changeOracle DBrequest statusWarehouse TV boardrefreshes every 2 minutes
As built: Oracle parts in the accent colour; the dots trace a request out to the fleet manager and his decision back to the warehouse.

OUTCOME

Approvals no longer depended on the manager being on site, which saved significant time for the warehouse. The system was in use until the company moved to SAP.

SKILLS SHOWN

Oracle APEXPL/SQL packagesREST calls from PL/SQLSlack interactive messagesPHPWorkflow design without a workflow engineOperational dashboards
← Back to all projects
CONTACT

Let's talk.

Work, photography collaborations, or just hello.

LinkedIn Instagram

Prefer email? jayraj.patel1407 [at] gmail.com