Purpose

It would help with my planning to know which Advanced Roadmaps Plans are Classic Plans, Live Plans, or Improved Planning Interface plans

From Advanced Roadmaps version 3.0, Classic Plans are no longer available. Therefore, I would to determine a list of Classic Plans before we upgrade, so that I can begin the process to Migrate classic plan data to a live plan to a newer plan type.  

Solution

Classic Plans

Execute the following SQL on your Jira database to produce a list of Classic plans, and a URL to access them (replace JIRA_URL with your Jira base URL)

SELECT "ID_OTHER", "TITLE", 'http://JIRA_URL/secure/PortfolioPlan.jspa?id=' || "ID_OTHER" ||'#backlog' AS "URL" FROM "AO_A415DF_AOPLAN";

If you have trouble with that query, you can use this simpler version:

SELECT "ID_OTHER", "TITLE" FROM "AO_A415DF_AOPLAN";

• ID_OTHER - ID of the plan
• TITLE - Title of the plan
• URL - URL to access the plan in your web browser

Live and Improved Planning Interface plans

Execute the following SQL on your Jira database to produce a list of Live Plans and Improved Planning Interface plans, and a URL to access them (replace JIRA_URL with your Jira base URL)

SELECT "TITLE", "CREATOR_ID" , "ROADMAPS_PLAN_VERSION", 'http://JIRA_URL/secure/PortfolioPlanView.jspa?id=' || "ID" AS "URL" FROM "AO_D9132D_PLAN";

If you have trouble with that query, you can use this simpler version:

SELECT "TITLE", "CREATOR_ID" , "ROADMAPS_PLAN_VERSION" FROM "AO_D9132D_PLAN";

• TITLE - Title of the Advanced Roadmaps plan

• CREATOR_ID - User key of the user who created the plan

• ROADMAPS_PLAN_VERSION - In versions prior to the depreciation of Live Plans, this column showed 0 for Live plans, or 1  for Improved Planning Interface plans

Via REST API

Perform a GET request to the endpoint https://JIRAURL/jira/rest/jpo-api/1.0/plan

The response body will return a list of Live Plans and Improved Planning Interface plans available for the login supplied. 

• id - ID of the plan
• title - Title of the plan
• portfolioPlanVersion - Set to 0 for Live plans, or Set to 1 for Improved Planning Interface plans

 Please note that Classic Plans are not able to be returned via REST