/* 
 * Bamboo version: 5.7-
 * Database: PostgreSQL
 * Triggers: 'Scheduled'
 */
select B.FULL_KEY as PLAN_KEY, 
       B.TITLE as BRANCH,
       VL.PLUGIN_KEY as REPOSITORY_TYPE,
       unnest(xpath
        ('//buildStrategies/buildStrategy/key[text()="schedule"]/../config/item/key[text()="repository.change.schedule.cronExpression"]/../value/text()', BD.XML_DEFINITION_DATA::xml)
       ) 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 PLAN_KEY, BRANCH