Documentation: Missing Review Option in OnDemand for Legacy Projects Post-Upgrade

Issue Description

After performing a system upgrade in the Venio platform, users may encounter a scenario where the "Review" option is unavailable within the OnDemand module for certain existing projects. This issue primarily affects projects created in older versions of Venio, specifically those originating from v.7.30.0.0 or earlier (e.g., v.6.5.0.15). The problem manifests as the Review functionality being completely absent from the interface, as evidenced by user-submitted screenshots showing the missing option.

Symptoms

• The "Review" option does not appear in the OnDemand section of the project dashboard.
• This occurs selectively for legacy projects (created before or in v.7.30.0.0), while newer projects function normally.
• No error messages are typically displayed; the option is simply not present.
• Multiple projects from the same era may exhibit the same behavior, indicating a systemic configuration issue rather than an isolated incident.

Affected Components

• Projects: Those created in Venio v.7.30.0.0 or earlier.
• Module: OnDemand.
• Potential Impact: High priority, as it prevents access to review workflows in active or archived projects.

Cause

The root cause appears to be incompatible or outdated settings stored in the database, specifically within the tbl_VoD_VoDRInputSettings table. These settings include an ExportTemplateName value of 'Analyze and Review Service', which conflicts with the upgraded system configuration. This legacy template is not compatible post-upgrade, leading to the Review option being hidden or disabled.

Troubleshooting Steps

1. Verify the Issue:
   • Confirm the project creation date and version (e.g., via project metadata or database query).
   • Check if the issue affects multiple projects from the same version range.
   • Reproduce the problem by navigating to the OnDemand section in the affected project.
2. Initial Checks:
   • Ensure the upgrade was completed successfully and no other modules are impacted.
   • Review user permissions to rule out access-related restrictions.
   • If possible, compare settings between affected and unaffected projects using database queries.
3. Escalation:
   • Involve the development team for deeper analysis if initial checks do not resolve.
   • Schedule a collaborative session (e.g., via Zoom) to inspect the project in real-time.

4. Diagnostic Query:

   Use the following SQL query to identify affected projects by checking for the problematic ExportTemplateName in the settings XML:

   SELECT pv.*, p.*
   FROM tbl_pj_projectsetup p
   INNER JOIN (
       SELECT MAX(settingid) AS SettingId, ProjectId 
       FROM tbl_VoD_VoDRInputSettings 
       GROUP BY projectid
   ) v ON p.projectid = v.projectid
   INNER JOIN tbl_VoD_VoDRInputSettings pv ON v.projectid = pv.projectid AND v.SettingId = pv.SettingId
   WHERE pv.SettingXML.value('(SettingsInfo/ExportTemplateName)[1]','nvarchar(100)') = 'Analyze and Review Service'
   ORDER BY p.projectid DESC;

   For a specific project (replace 89946 with the actual ProjectId):

   SELECT * FROM tbl_VoD_VoDRInputSettings WHERE projectid = 89946;

Resolution

The resolution involves removing the incompatible settings from the database. This is achieved by deleting the relevant entries in tbl_VoD_VoDRInputSettings for affected projects. After deletion, the Review option should reappear in OnDemand.

Warning: Always back up the database before executing delete operations. Use transactions to allow rollback if needed.

Resolution SQL (execute in a transaction for safety):

BEGIN TRAN;

DELETE s 
FROM tbl_VoD_VoDRInputSettings s
INNER JOIN (
    SELECT DISTINCT v.ProjectId 
    FROM tbl_pj_projectsetup p, tbl_VoD_VoDRInputSettings v
    WHERE p.projectid = v.projectid 
    AND SettingXML.value('(SettingsInfo/ExportTemplateName)[1]','nvarchar(100)') = 'Analyze and Review Service'
) a ON s.projectid = a.projectid;

COMMIT;

• After running the query, verify the fix by reloading the project and checking for the Review option.
• Test in a staging environment first if possible.

Prevention for Future Upgrades

• During upgrades, scan for legacy settings using the diagnostic query and proactively clean them up.
• Document project versions at creation to flag potential compatibility issues.
• Consider automating checks for outdated templates in the upgrade script.

Related Tickets/References

• Original Ticket: [Link to Zendesk ticket if available].
• Attachments: Screenshots of missing Review option (e.g., "Review Option missing.png").

This documentation should be updated if similar issues arise in future upgrades or if additional causes are identified.