FellaHost Company Limited

Project Developers & Software Engineering Specialists

Document Reference: Section 24 Deliverables for Software Development Team

System Designation: Integrated Clearing, Forwarding & Logistics System (ICFLS)

Corporate Entity: FellaHost Company Limited

Headquarters: P.O. Box 80079, Dar es Salaam, Tanzania

7. Report Catalogue and Production SQL Queries

Executive Overview

ICFLS incorporates 12 mission-critical business intelligence reports designed for executive decision-making, regulatory compliance, client account reconciliation, and operational audit readiness.

7.1 Production SQL Report Specifications

Report 01: Monthly Job Profitability & Net Margin Realization

SELECT 
    j.job_number,
    c.company_name AS customer,
    j.destination_city,
    COALESCE(i.total_amount, 0) AS total_invoiced_tzs,
    COALESCE(exp.total_expenses, 0) AS total_direct_expenses_tzs,
    (COALESCE(i.total_amount, 0) - COALESCE(exp.total_expenses, 0)) AS gross_profit_tzs,
    ROUND(((COALESCE(i.total_amount, 0) - COALESCE(exp.total_expenses, 0)) / NULLIF(i.total_amount, 0)) * 100, 2) AS margin_percentage
FROM jobs j
JOIN customers c ON j.customer_id = c.id
LEFT JOIN (
    SELECT job_id, SUM(total_amount) AS total_amount 
    FROM invoices WHERE payment_status != 'Cancelled' GROUP BY job_id
) i ON j.id = i.job_id
LEFT JOIN (
    SELECT job_id, SUM(amount_tzs) AS total_expenses 
    FROM job_expenses GROUP BY job_id
) exp ON j.id = exp.job_id
WHERE j.created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
ORDER BY gross_profit_tzs DESC;

Report 02: Maritime Container Demurrage Risk & Aging Analysis

SELECT 
    cnt.container_number,
    j.job_number,
    cnt.shipping_line,
    cnt.discharge_date,
    cnt.free_days_allowed,
    cnt.demurrage_commences_at,
    DATEDIFF(NOW(), cnt.demurrage_commences_at) AS days_overdue,
    CASE 
        WHEN DATEDIFF(NOW(), cnt.demurrage_commences_at) > 0 
        THEN DATEDIFF(NOW(), cnt.demurrage_commences_at)  45.00  2550.00
        ELSE 0.00 
    END AS estimated_demurrage_tzs,
    cnt.status
FROM containers cnt
JOIN jobs j ON cnt.job_id = j.id
WHERE cnt.status != 'Returned_Empty'
ORDER BY days_overdue DESC;

Report 05: Customer Escrow Custody Account Reconciliation

SELECT 
    c.customer_code,
    c.company_name,
    COALESCE(dep.total_deposited, 0) AS total_custody_deposits_tzs,
    COALESCE(duties.total_duties_paid, 0) AS total_customs_disbursements_tzs,
    (COALESCE(dep.total_deposited, 0) - COALESCE(duties.total_duties_paid, 0)) AS net_escrow_holding_tzs
FROM customers c
LEFT JOIN (
    SELECT customer_id, SUM(amount_tzs) AS total_deposited 
    FROM customer_deposits GROUP BY customer_id
) dep ON c.id = dep.customer_id
LEFT JOIN (
    SELECT j.customer_id, SUM(d.total_duties_tzs) AS total_duties_paid
    FROM declarations d 
    JOIN jobs j ON d.job_id = j.id
    WHERE d.assessment_status IN ('Duty_Paid', 'Release_Order_Issued')
    GROUP BY j.customer_id
) duties ON c.id = duties.customer_id
ORDER BY net_escrow_holding_tzs DESC;

Regulatory Notice

We're Top leading Company Registering under Commercial & Technical ICT Services TIN NO: 189-723-717

FellaHost Company Limited — Project Developers & Engineering Specialists.

Confidential & Proprietary Enterprise Asset — All Rights Reserved.