[JIRAALIGN-1518] Feature and Capability Backlog Show Duplicate Items at the Solution Level

Issue Summary

When Selecting a Solution in Tier 1, then navigating to either the Feature Backlog or Capability Backlog, the user may see duplicated work items in the backlog list. See screenshot for example:

Our developers have reproduced this and determined this is a SQL issue

Steps to Reproduce

• Create a feature and assign an epic to it.
• Assign multiple programs to the Epic.
• select each program, one at a time in tier 1, go to the epic backlog, and rank each epic within those programs. Repeat for each program.
• Select all applicable programs in tier 1 and go to Feature backlog
  OR
• Select a Solution in Tier 1, that includes the programs that the epic belongs to.

Expected Results

We should only see single instances of each work item (Feature or Capability)

Actual Results

The sql issue will cause duplicate work items to show in the backlog as the Parent Epic has multiple rankings within the selected tier one programs or solutions

Notes from Development team:

To fix this , here is the current sql: - Relevant file is AjaxPrioritize.asp

	select 1 as ItemType, tblAgileFeatureGroup.FeatureGroupID as ItemID, MIN(tblAgileFeatureGroup.Rank) as ParentProgramRank
		from tblAgileFeatureGroup
		where InRecycleBin is null
		and IterationGroup in (?,?,?,?)
		and tblAgileFeatureGroup.Rank is not null
		group by tblAgileFeatureGroup.FeatureGroupID
	union
	select 2 as ItemType, t1.FeatureGroupID as ItemID, MIN(t2.Rank) from tblAgileFeatureGroup t1 inner join tblFeatureGroupMultiProgramRel t2 on t1.FeatureGroupID = t2.FeatureGroupID
		where InRecycleBin is null
		and t2.ProgramID in (?,?,?,?)
		and t1.IterationGroup not in (?,?,?,?)
		and t2.Rank is not null
		group by t1.FeatureGroupID

Replace the above with the sql highlighted below (screenshot):

Workaround

Currently there is no known workaround for this behavior, as a fix needs to be made in the database.