How to get Dynamic Default for Payroll Period LOV based on the system date?

Normally we can provide the default value on the parameters screen in Data Model, however we can only mention a constant value and it will not change based on data. So this option is ruled out.

For Payroll Period anyhow we will be having a List of values (LOV) from which the user needs to pick up the respective Payroll Period and then run the report.

If we dig deeper into the way that shows the List of Values, it would be based on the sorting order. Even if we put the sort order on the Period name, everytime the same value would get picked up.

For this, we would be using the lowest sequence for the current period and higher sequences for the rest of the periods (past and future) and then sort the list by sequence number. Every time the LOV is generated based on the run date, it picks up the current pay period.

SQL Query to pick default of Current Pay Period:

with periods as 
(select distinct ptp.period_name, start_date, end_date
from pay_time_periods ptp, pay_all_payrolls_f pp
where pp.payroll_id = ptp.payroll_id and ((COALESCE(null, :p_payroll_name) is null) OR (substr(pp.payroll_name,5) IN (:p_payroll_name)))
and sysdate between pp.effective_start_date and pp.effective_end_date
and ptp.period_category = 'E'
order by start_date asc)

select period_name from (
select period_name, 1 as rownumber
from periods 
where sysdate between start_date and end_date
UNION
select period_name, rownum * 10 + 100 as rownumber
from periods 
where to_char(start_date,'YYYY') = to_char(sysdate,'YYYY')
and period_name <> (select period_name from periods where sysdate between start_date and end_date)
UNION
select period_name, rownum * 10+1000 as rownumber
from periods 
where to_char(start_date,'YYYY') < to_char(sysdate,'YYYY')
UNION
select period_name, rownum * 100 + 10000 as rownumber
from periods 
where to_char(start_date,'YYYY') > to_char(sysdate,'YYYY')
) order by rownumber

Here we are getting all pay periods into a temp table by name periods using the with clause.

Then we are selecting the current pay period based on sysdate and assigning sequence of 1.

Next we are selecting all pay periods in current year with the next sequences and excluding the current pay period

Next we are selecting all pay periods less than current year, followed by pay periods greater than current year.

We are ensuring that we are not leaving any pay periods from the LOV.

The output of this would look like below for the sysdate of 26-May-2023:

Here is a look at the dropdown:

IMP NOTE: We need to ensure that the select All option is not selected for this parameter as this functionality will not work if All is selected.

We can modify the query to show the LOV in different ways based on our requirement.

Hope this helps.