A report should be provided to know which file has not been locked yet (after being set as completed 2 weeks ago) including files that has been Unlocked ( with last locked date and reason/person who unlocked it)

Note: 26 June 2026

Team, Please refer to below for updated US.

In MFreight, once a file reference reaches Completed status, it should normally be locked to prevent further changes to operational or financial information.

The current Lock and Unlock Report needs to be improved so that business users can clearly see:

• Which completed files are locked.
• Which completed files are not locked.
• When the file was completed.
• How many days have passed since completion.
• When the file was last locked.
• Whether the file was unlocked after being locked.
• Who last unlocked the file.
• The reason for the last unlock action.

The report will act as a control and audit report for completed file references.

Scope¶

This enhancement applies to the Lock and Unlock File Reference Report under:

Reports > Lock and Unlock Report

The report shall cover completed file references from applicable MFreight modules, including but not limited to:

• Ocean Import
• Ocean Export
• Air Import
• Air Export
• Clearing, where applicable

The report shall only consider files that are already in Completed status.

Functional Requirements¶

1: Display completed files¶

The system shall display file references that are in Completed status.

Once a file is in Completed status, it shall be eligible to appear in the Lock and Unlock Report, regardless of the number of days since completion.

The previous rule to display only files completed for more than 2 weeks ago shall no longer apply.

2: Display required report columns¶

The report shall display the following columns:

Column	Description
File Reference	The unique file reference number
Module	Ocean Import, Ocean Export, Air Import, Air Export, Clearing, etc.
Confirmed Date	Date on which the file was set as Completed/final status
Days Since Completed	Number of days from completed date until current date
Lock Status	Indicates whether the file is locked or not. Values: Yes / No
Last Locked Date	Last date the file was locked, if applicable
Last Unlocked Date	Last date the file was unlocked, if applicable
Last Unlocked By	User who last unlocked the file
Last Unlock Reason	Latest reason entered when the file was unlocked

3: Display Summary Cards / KPIs¶

The system shall display summary cards at the top of the report to provide management users with a quick overview of completed file references and their lock/unlock status.

The summary cards shall be refreshed whenever the user applies or clears filters.

The summary cards shall include:

1. Total Completed Files
2. Total Locked Files
3. Total Files not Locked
4. Files Previously Unlocked
5. Average Days Since Completed
6. Oldest Completed File Not Locked

The summary values shall always reflect the same dataset displayed in the report grid.

Summary Card / KPI	Description
Total Completed Files	Total number of completed file references matching the selected filters
Total Locked Files	Total number of completed file references where Lock Status = Yes
Total Files not Locked	Total number of completed file references where Lock Status = No
Files Previously Unlocked	Total number of completed file references that have at least one unlock activity
Average Days Since Completed	Average number of days since files were completed
Oldest Completed File Not Locked	File with the highest Days Since Completed where Lock Status = No

KPI Calculation Rules¶

KPI	Calculation Rule
Total Completed Files	Count of all completed files matching selected filters
Total Locked Files	Count where Lock Status = Yes
Total Files not Locked	Count where Lock Status = No
Files Previously Unlocked	Count where Last Unlocked Date is not N/A
Average Days Since Completed	Average of Days Since Completed for all records matching filters
Oldest Completed File Not Locked	Record with highest Days Since Completed and Lock Status = No

4: Calculate Days Since Completed¶

The system shall calculate Days Since Completed using the following rule:

Days Since Completed = Current Date - Confirmed Date

Example:

Confirmed Date	Current Date	Days Since Completed
01-May-2026	10-May-2026	9
15-May-2026	20-May-2026	5

5: Display Lock Status¶

The system shall display the lock status of each completed file.

The values shall be:

Lock Status	Meaning
Yes	The file is currently locked
No	The file is currently not locked

The Lock Status should reflect the current lock condition of the file at the time the report is generated.

6: Display latest lock and unlock information¶

For each completed file reference, the system shall display the latest available lock and unlock details.

The system shall display:

• Last Locked Date
• Last Unlocked Date
• Last Unlocked By
• Last Unlock Reason

If the file was never locked or never unlocked, the system shall display N/A.

Example:

Scenario	Display
File was never locked	Last Locked Date = N/A
File was never unlocked	Last Unlocked Date = N/A, Last Unlocked By = N/A, Last Unlock Reason = N/A

7: Provide report filters¶

The report shall provide the following filters:

Filter	Description
File Reference	Search by full or partial file reference
Module	Filter by module, e.g. Ocean Import, Ocean Export, Air Import, Air Export, Clearing
Lock Status	Filter by Yes / No
Completed Date From / To	Filter by Confirmed Date range
Unlocked By	Filter by the user who last unlocked the file

8: Default filter by Completed Date¶

The Completed Date From / To filter shall be the default filter for this report.

When the user opens the report, the system shall load records based on the default Completed Date range.

Suggested behaviour:

• Completed Date From: first day of the current month
• Completed Date To: current date

This is to avoid loading too many completed file references by default.

The user shall be able to change the date range and apply filters again.

9: Filter by File Reference¶

The system shall allow the user to search by full or partial file reference.

Example:

User Input	Expected Result
MACCS25SFE04112	Exact matching file reference
SFE041	File references containing SFE041
MACCS25	File references starting with or containing MACCS25

The search should be case-insensitive.

10: Filter by Module¶

The system shall allow the user to filter the report by module.

Example values:

• Ocean Import
• Ocean Export
• Air Import
• Air Export
• Clearing

If no module is selected, the system shall display records from all applicable modules, subject to the other selected filters.

11: Filter by Lock Status¶

The system shall allow the user to filter by Lock Status.

Available values:

• Yes
• No

If no Lock Status is selected, both locked and unlocked completed files shall be displayed.

12: Filter by Unlocked By¶

The system shall allow the user to filter by the user who last unlocked the file.

This filter shall return completed files where the selected user is the latest user who performed the unlock action.

If no user is selected, records unlocked by any user shall be displayed, subject to the other selected filters.

13: Sorting¶

The report shall allow sorting on key columns.

Recommended sortable columns:

• File Reference
• Module
• Confirmed Date
• Days Since Completed
• Lock Status
• Last Locked Date
• Last Unlocked Date
• Last Unlocked By

Default sorting should be:

Confirmed Date descending

This will show the most recently completed files first.

14: Export to Excel¶

The system shall allow the user to export the report to Excel.

The Excel export shall include all report columns:

• File Reference
• Module
• Confirmed Date
• Days Since Completed
• Lock Status
• Last Locked Date
• Last Unlocked Date
• Last Unlocked By
• Last Unlock Reason

If filters are applied, the Excel export shall reflect the selected filters.

15: Handling of missing data¶

The system shall display N/A where data is not available.

Examples:

Scenario	Display
File was never locked	Last Locked Date = N/A
File was never unlocked	Last Unlocked Date = N/A
Last unlocked user is not available	Last Unlocked By = N/A
Unlock reason is not available	Last Unlock Reason = N/A

16: Access control¶

The report shall only be accessible to authorised users based on the existing MFreight report access rights.

Acceptance Criteria¶

A. Data Display Acceptance Criteria¶

1: Display completed files only¶

Given that a file reference is in Completed status,
when the user opens the Lock and Unlock File Reference Report,
then the file shall be eligible to appear in the report based on the selected filters.

2: Exclude non-completed files¶

Given that a file reference is not in Completed status,
when the report is generated,
then the file shall not be displayed in the report.

3: Do not apply the 14-day rule¶

Given that a file reference has been set to Completed status,
when the report is generated,
then the file shall be displayed based on the selected filters regardless of whether it was completed today, within 14 days, or more than 14 days ago.

4: Display File Reference¶

Given that a completed file reference matches the selected filters,
when the report is generated,
then the system shall display the unique File Reference number.

5: Display Module¶

Given that a completed file reference matches the selected filters,
when the report is generated,
then the system shall display the related Module, such as Ocean Import, Ocean Export, Air Import, Air Export, or Clearing.

6: Display Confirmed Date¶

Given that a file reference has been set to Completed status,
when the report is generated,
then the system shall display the date on which the file was set to completed/final status in the Confirmed Date column.

7: Calculate Days Since Completed¶

Given that a completed file reference has a valid Confirmed Date,
when the report is generated,
then the system shall calculate and display Days Since Completed based on the difference between the current date and the Confirmed Date.

8: Display Lock Status as Yes¶

Given that a completed file reference is currently locked,
when the report is generated,
then the system shall display Yes in the Lock Status column.

9: Display Lock Status as No¶

Given that a completed file reference is currently not locked,
when the report is generated,
then the system shall display No in the Lock Status column.

10: Display Last Locked Date¶

Given that a completed file reference was previously locked,
when the report is generated,
then the system shall display the latest lock date in the Last Locked Date column.

11: Handle file never locked¶

Given that a completed file reference was never locked,
when the report is generated,
then the system shall display N/A in the Last Locked Date column.

12: Display Last Unlocked Date¶

Given that a completed file reference was previously unlocked,
when the report is generated,
then the system shall display the latest unlock date in the Last Unlocked Date column.

13: Handle file never unlocked¶

Given that a completed file reference was never unlocked,
when the report is generated,
then the system shall display N/A in the Last Unlocked Date column.

14: Display Last Unlocked By¶

Given that a completed file reference was previously unlocked,
when the report is generated,
then the system shall display the user who last unlocked the file in the Last Unlocked By column.

15: Handle Last Unlocked By¶

Given that a completed file reference was never unlocked, or the user information is not available,
when the report is generated,
then the system shall display N/A in the Last Unlocked By column.

16: Display Last Unlock Reason¶

Given that a completed file reference was previously unlocked and a reason was captured,
when the report is generated,
then the system shall display the latest unlock reason in the Last Unlock Reason column.

B. Summary Cards / KPI Acceptance Criteria¶

17: Display summary cards¶

Given that the user opens the Lock and Unlock File Reference Report,
when the report loads,
then the system shall display summary cards / KPIs above the report grid.

18: Summary cards follow selected filters¶

Given that the user applies or clears filters,
when the report results are refreshed,
then the summary cards shall be recalculated based on the same dataset displayed in the grid.

19: Total Completed Files KPI¶

Given that completed files exist within the selected filter criteria,
when the report is generated,
then the Total Completed Files card shall display the total number of completed file references matching the selected filters.

20: Total Locked Files KPI¶

Given that completed files exist with Lock Status = Yes,
when the report is generated,
then the Total Locked Files card shall display the total number of completed file references that are currently locked.

21: Total Files not Locked KPI¶

Given that completed files exist with Lock Status = No,
when the report is generated,
then the Total Files not Locked card shall display the total number of completed file references that are currently not locked.

22: Files Previously Unlocked KPI¶

Given that completed files have a Last Unlocked Date,
when the report is generated,
then the Files Previously Unlocked card shall display the total number of completed file references that have at least one unlock activity.

23: Average Days Since Completed KPI¶

Given that completed files exist within the selected filters and have a valid Confirmed Date,
when the report is generated,
then the Average Days Since Completed card shall display the average number of days since completion.

24: Oldest Completed File Not Locked KPI¶

Given that completed files exist with Lock Status = No,
when the report is generated,
then the Oldest Completed File Not Locked card shall display the file reference with the highest Days Since Completed among files that are currently not locked.

25: No data for summary cards¶

Given that no records match the selected filters,
when the report is generated,
then the summary cards shall display zero values or N/A where applicable.

Expected display:

KPI	Expected Display
Total Completed Files	0
Total Locked Files	0
Total Files Not Locked	0
Files Previously Unlocked	0
Average Days Since Completed	N/A
Oldest Completed File Not Locked	N/A

C. Filter Acceptance Criteria¶

26: Default Completed Date filter¶

Given that the user opens the report,
when the screen loads,
then the system shall apply the default Completed Date From / To filter.

Suggested default:

• Completed Date From = 1 month from current date
• Completed Date To = current date

27: Filter by File Reference¶

Given that the user enters a full or partial File Reference,
when the user clicks Apply Filters,
then the report shall display only completed file references matching the entered value.

28: File Reference search is case-insensitive¶

Given that the user enters a File Reference in uppercase, lowercase, or mixed case,
when the user clicks Apply Filters,
then the system shall return matching completed file references regardless of letter case.

29: Filter by Module¶

Given that the user selects a Module,
when the user clicks Apply Filters,
then the report shall display only completed file references belonging to the selected Module.

30: Display all modules when Module filter is blank¶

Given that no Module is selected,
when the report is generated,
then the system shall display completed file references from all applicable modules, subject to the other selected filters.

31: Filter by Lock Status = Yes¶

Given that the user selects Lock Status = Yes,
when the user clicks Apply Filters,
then the report shall display only completed file references that are currently locked.

32: Filter by Lock Status = No¶

Given that the user selects Lock Status = No,
when the user clicks Apply Filters,
then the report shall display only completed file references that are currently not locked.

33: Display all lock statuses when Lock Status filter is blank¶

Given that no Lock Status is selected,
when the report is generated,
then the system shall display both locked and unlocked completed file references, subject to the other selected filters.

24: Filter by Completed Date From / To¶

Given that the user selects a Completed Date From and Completed Date To,
when the user clicks Apply Filters,
then the report shall display only completed file references where the Confirmed Date falls within the selected date range.

25: Validate Completed Date range¶

Given that the user enters a Completed Date From later than the Completed Date To,
when the user clicks Apply Filters,
then the system shall display a validation message and shall not generate the report until the date range is corrected.

Suggested message:

Completed Date From cannot be later than Completed Date To.

26: Filter by Unlocked By¶

Given that the user selects or enters a value in the Unlocked By filter,
when the user clicks Apply Filters,
then the report shall display only completed file references where the selected user is the latest user who unlocked the file.

27: Clear filters¶

Given that filters have been applied,
when the user clicks Clear All,
then all filters shall be reset.

The Completed Date From / To filter shall return to the default date range.

D. Sorting and Display Acceptance Criteria¶

28: Default sorting¶

Given that the report is generated,
when records are displayed,
then the default sorting shall be by Confirmed Date descending, showing the most recently completed files first.

---

29: Sort by columns header¶

Given that the user clicks the column header - File Reference/Module/etc..,
when the report is displayed,
then the system shall sort the records by selected column.

30: No records found¶

Given that no completed file references match the selected filters,
when the report is generated,
then the system shall display the message:

No records found for the selected criteria.

E. Excel Export Acceptance Criteria¶

31: Export to Excel¶

Given that the report contains data,
when the user clicks Export to Excel,
then the system shall generate an Excel file containing the report results based on the selected filters.

32: Excel export includes all columns¶

Given that the user exports the report to Excel,
when the Excel file is generated,
then the file shall include all the following columns:

• File Reference
• Module
• Confirmed Date
• Days Since Completed
• Lock Status
• Last Locked Date
• Last Unlocked Date
• Last Unlocked By
• Last Unlock Reason

33: Excel export reflects selected filters¶

Given that filters have been applied,
when the user exports the report to Excel,
then the Excel file shall contain only records matching the selected filters.

F. Access Control Acceptance Criteria¶

34: Report access rights¶

Given that a user has permission to access the Lock and Unlock File Reference Report,
when the user logs in,
then the system shall allow the user to see the report in the Reports Menu.

Given that a user does not have permission to access the Lock and Unlock File Reference Report,
when the user logs in,
then the system shall restrict access based on existing authority,user should not see the report under the Reports Menu.