Hello,
Currently, on our Oracle DB (19c), we have a View with a query something like below:
SELECT
c_id,case_status,data_ind,
ffy1,ffy1_case_status,ffy1_age,
ffy2,ffy2_case_status,ffy2_age,
ffy3,ffy3_case_status,ffy3_age,
ffy4,ffy4_case_status,ffy4_age,
ffy5,ffy5_case_status,ffy5_age
FROM ( SELECT
*
FROM
(
SELECT
c_id,case_status,
'NEW_CASE_DATA' data_ind,
ffy_of_activity,burndown_case_status,
cases_age_per_year
FROM
test_apl_base a
WHERE
data_ind = 'NEW_CASES'
) pivot
(MAX (FFY_OF_ACTIVITY) , LISTAGG (distinct burndown_case_status, ', ') within group (order by burndown_case_status desc) as case_Status, max(cases_age_per_year) as Age
for ffy_of_activity in ( 2021 AS ffy1,2022 AS ffy2,2023 AS ffy3 , 2024 AS FFY4 , 2025 AS FFY5)
)
)
As you can see, in the pivot section, I have 5 most recent years listed as respective FFYs (Federal Fiscal Years). I am trying to find a way to update this query, yearly once, where the these 5 years get updated.
For example: When Oct 1, 2025 hits, the line must be updated as
ffy_of_activity in ( 2022 AS ffy1,2023 AS ffy2,2024 AS ffy3 , 2025 AS FFY4 , 2026 AS FFY5)
Can this be done via a Stored Proc, which can be called by a Cron job? Any inputs, are greatly appreciated.
Thanks.