/* 
 * Bamboo version: 5.7-
 * Database: MySQL
 * Triggers: 'Repository polling' > 'Polling strategy' > 'Scheduled'
 */
select B.FULL_KEY as PLAN_KEY,
       B.TITLE as TITLE,
       VL.PLUGIN_KEY as REPOSITORY_TYPE,
       ExtractValue(BD.XML_DEFINITION_DATA,
        '//buildStrategies/buildStrategy/key[text()="poll"]/../config/item/key[text()="repository.change.poll.type"]/../value[text()="CRON"]/../../item/key[text()="repository.change.poll.cronExpression"]/../value'
       ) as SCHEDULED,
       BD.BUILD_DEFINITION_ID as BUILD_DEFINITION_ID
  from VCS_LOCATION as VL
  join PLAN_VCS_LOCATION as PVL on PVL.VCS_LOCATION_ID = VL.VCS_LOCATION_ID
  join BUILD as B on PVL.PLAN_ID = B.BUILD_ID
  join BUILD_DEFINITION as BD on B.BUILD_ID = BD.BUILD_ID
 where B.SUSPENDED_FROM_BUILDING = false
 order by SCHEDULED desc, PLAN_KEY, TITLE;