Task #18562
Updated by Redmine Admin 3 months ago
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** **Note:** Team, Please refer to below comments 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.