SSRS Subscriptions and looking up their SQL Server Agent job names and next scheduled run times

USE ReportServer
GO
WITH S AS
(
    SELECT
        SubscriptionID,
        ExtensionSettings = CAST(ExtensionSettings as xml)
    FROM ReportServer..Subscriptions
),
s2 AS (
SELECT
	S.SubscriptionID,
	TransmissionMethod = CASE ExtensionSettings.value('(/ParameterValues/ParameterValue/Name)[1]', 'varchar(50)')
		WHEN 'TO' THEN 'Email'
		WHEN 'PATH' THEN 'File Share'
		ELSE 'Unknown' END,
	EmailTo = CAST(ExtensionSettings.query('
		for $parameter in /ParameterValues/ParameterValue
		let $name := $parameter/Name
		let $value := $parameter/Value
		where $name = "TO"
		return data($value)') as nvarchar(4000)),
	FileSharePath = CAST(ExtensionSettings.query('
		for $parameter in /ParameterValues/ParameterValue
		let $name := $parameter/Name
		let $value := $parameter/Value
		where $name = "PATH"
		return data($value)') as nvarchar(4000)),
	[FileName] = CAST(ExtensionSettings.query('
		for $parameter in /ParameterValues/ParameterValue
		let $name := $parameter/Name
		let $value := $parameter/Value
		where $name = "FILENAME"
		return data($value)') as nvarchar(4000)),
    RenderFormat = CAST(ExtensionSettings.query('
		for $parameter in /ParameterValues/ParameterValue
		let $name := $parameter/Name
		let $value := $parameter/Value
		where $name = "RENDER_FORMAT" or $name = "RenderFormat"
		return data($value)') as nvarchar(4000)),
	EmailSubject = CAST(ExtensionSettings.query('
		for $parameter in /ParameterValues/ParameterValue
		let $name := $parameter/Name
		let $value := $parameter/Value
		where $name = "Subject"
		return data($value)') as nvarchar(4000))
	,ExtensionSettings
FROM S
)
SELECT *
INTO #es
FROM s2


SELECT
    Schedule.ScheduleID AS JobName,
    cat.[Name] AS ReportName,
    sub.[Description] AS SubDesc,
    --sub.ExtensionSettings,
    es.RenderFormat,
    sub.SubscriptionID,
    cat.[Path] AS ReportPath,
    StartDate,
    Schedule.LastRunTime,
    ja.next_scheduled_run_date
FROM Schedule
JOIN ReportSchedule ON Schedule.ScheduleID = ReportSchedule.ScheduleID
JOIN Subscriptions sub ON ReportSchedule.SubscriptionID = sub.SubscriptionID
JOIN [Catalog] cat ON ReportSchedule.ReportID = cat.ItemID
                  AND sub.Report_OID = cat.ItemID
LEFT JOIN #es es ON sub.SubscriptionId = es.SubscriptionId
LEFT JOIN msdb.dbo.sysjobs job ON CAST(Schedule.ScheduleID as sysname) = job.[name]
LEFT JOIN msdb.dbo.sysjobactivity ja ON ja.job_id = job.job_id
                                    AND ja.run_requested_date IS NOT NULL
                                    AND ja.start_execution_date IS NOT NULL
--WHERE RenderFormat LIKE 'Excel%'  -- Only subscriptions that generate Excel files
--WHERE Schedule.ScheduleID = '239DF50E-66F4-4F38-8378-426573E808B0'  -- SQL Agent Job Name