Oracle APEX applications almost always require date-based filtering.
Whether you’re building dashboards, reports, KPIs, invoices, or analytics pages, users expect a seamless and intuitive way to filter data by day, week, month, quarter, or year.
Whether you’re building dashboards, reports, KPIs, invoices, or analytics pages, users expect a seamless and intuitive way to filter data by day, week, month, quarter, or year.
The Challenge
Common issues in APEX apps:
- Date range logic is duplicated across multiple pages
- Week handling is not ISO-compliant
- Navigation (previous/next) is implemented inconsistently
- Custom date ranges break the UI context
- JavaScript is used where PL/SQL would be more reliable
- Timeframe selection and date pickers are not synchronized
Goal:
Provide a single, reusable engine that handles all of this centrally and predictably.
Provide a single, reusable engine that handles all of this centrally and predictably.
Solution Overview
The Smart Timeframe Engine provides:
- Day selection (single date)
- ISO Week selection (Monday to Sunday)
- Month selection
- Quarter selection
- Year selection
- Previous/next navigation
- Intelligent auto-detection for custom date ranges
- A clean server-side architecture without JavaScript
All logic is implemented in PL/SQL, making it easy to reuse across multiple APEX applications and pages.
Core Design Principles
- The UI only displays options and triggers submits
- A single submit process delegates all logic
- One PL/SQL package acts as the single source of truth
- No date calculations are performed in the page
- No JavaScript is required
This ensures predictable behavior and long-term maintainability.
Repository Structure
All files are located directly in the root of the GitHub repository for easy access:
- README.md
Documentation and setup instructions - apex_timeframe_package.sql
Central PL/SQL package containing all timeframe logic
[SQL] - apex_timeframe_view.sql
View used as List of Values for timeframe detail navigation
[SQL] - apex_page_rendering_process.sql
Rendering process used to initialize the default state
[SQL] - apex_page_submit_process.sql
Submit process that calls the central package
[SQL]
Required APEX Page Items
To use the engine, the following page items must exist with exactly these names:
- P1410_START_DATE
Date Picker (format DD.MM.YYYY) - P1410_END_DATE
Date Picker (format DD.MM.YYYY) - P1410_TIMEFRAME
Radio Group with values: D, W, M, Q, Y - P1410_TIMEFRAME_DETAIL
Radio Group using the timeframe detail view as LOV source
Optional item for predefined start behavior:
- P1410_FILTER_START_OPTION
Hidden item or Select List (e.g., START_CURRENT_MONTH)
Rendering Process (Initialization)
On page render, an optional rendering process can initialize the date range based on a predefined start option, such as “start with current month.”
This process runs before the page is displayed and prepares the initial timeframe context.
This process runs before the page is displayed and prepares the initial timeframe context.
SQL
xxxxxxxxxx46
1
DECLARE2
-- Anchor dates for initial rendering3
v_start_date VARCHAR2(10);4
v_end_date VARCHAR2(10);5
BEGIN6
------------------------------------------------------------------7
-- Initial start filter handling (Rendering / Before Header)8
--9
-- Delegates the initial date setup to the central10
-- timeframe engine using a custom search request.11
------------------------------------------------------------------12
13
IF :P1410_FILTER_START_OPTION = 'START_CURRENT_MONTH' THEN14
15
------------------------------------------------------------------16
-- Prepare initial date range (current month)17
------------------------------------------------------------------18
v_start_date := TO_CHAR(TRUNC(SYSDATE, 'MM'), 'DD.MM.YYYY');19
v_end_date := TO_CHAR(LAST_DAY(SYSDATE), 'DD.MM.YYYY');20
21
------------------------------------------------------------------22
-- Delegate logic to central timeframe engine23
------------------------------------------------------------------24
ADMI_UTIL_PKG.pr_get_DATE_RANGE(25
pi_timeframe_item_name => 'P1410_TIMEFRAME'26
, pi_timeframe_detail_item_name => 'P1410_TIMEFRAME_DETAIL'27
, pi_request => 'CUSTOM_SEARCH'28
, pio_start_date => v_start_date29
, pio_end_date => v_end_date30
, pio_timeframe => :P1410_TIMEFRAME31
, pio_timeframe_detail => :P1410_TIMEFRAME_DETAIL32
);33
34
------------------------------------------------------------------35
-- Write back calculated dates36
------------------------------------------------------------------37
:P1410_START_DATE := v_start_date;38
:P1410_END_DATE := v_end_date;39
40
------------------------------------------------------------------41
-- Reset start option to avoid re-triggering42
------------------------------------------------------------------43
:P1410_FILTER_START_OPTION := NULL;44
45
END IF;46
END;Submit Process (Central Entry Point)
All user interactions are handled by a single submit process:
- Clicking Day, Week, Month, Quarter, or Year
- Clicking a specific timeframe detail
- Using previous or next navigation
- Submitting a custom date range
The submit process simply calls the central PL/SQL package and passes the current request and page items.
SQL
xxxxxxxxxx24
1
BEGIN2
------------------------------------------------------------------3
-- Central submit handler for timeframe and date range logic4
--5
-- This process delegates all calculations to the6
-- ADMI_UTIL_PKG.pr_get_DATE_RANGE procedure.7
--8
-- It handles:9
-- - Timeframe clicks (Day / Week / Month / Quarter / Year)10
-- - Timeframe detail navigation (including PREV / NEXT)11
-- - Manual date range input via CUSTOM_SEARCH12
------------------------------------------------------------------13
14
ADMI_UTIL_PKG.pr_get_DATE_RANGE(15
pi_timeframe_item_name => 'P1410_TIMEFRAME'16
, pi_timeframe_detail_item_name => 'P1410_TIMEFRAME_DETAIL'17
, pi_request => :REQUEST18
, pio_start_date => :P1410_START_DATE19
, pio_end_date => :P1410_END_DATE20
, pio_timeframe => :P1410_TIMEFRAME21
, pio_timeframe_detail => :P1410_TIMEFRAME_DETAIL22
);23
24
END;Central Timeframe Package
All timeframe logic is implemented in a single, reusable PL/SQL package.
SQL
xxxxxxxxxx784
1
create or replace package util_pkg as2
3
procedure pr_get_date_range (4
pi_timeframe_item_name in varchar25
, pi_timeframe_detail_item_name in varchar2 6
, pi_request in varchar27
, pio_start_date in out varchar28
, pio_end_date in out varchar29
, pio_timeframe in out varchar210
, pio_timeframe_detail in out varchar211
);12
13
end util_pkg;14
/15
16
17
create or replace package body util_pkg as18
19
PROCEDURE pr_get_date_range (20
pi_timeframe_item_name IN VARCHAR221
, pi_timeframe_detail_item_name IN VARCHAR222
, pi_request IN VARCHAR223
, pio_start_date IN OUT VARCHAR224
, pio_end_date IN OUT VARCHAR225
, pio_timeframe IN OUT VARCHAR226
, pio_timeframe_detail IN OUT VARCHAR227
)28
IS29
------------------------------------------------------------------30
-- Session / Input values31
------------------------------------------------------------------32
v_timeframe VARCHAR2(10); -- D / W / M / Q / Y33
v_detail VARCHAR2(50); -- canonical detail (e.g. 01.2026)34
35
------------------------------------------------------------------36
-- Derived / working dates37
------------------------------------------------------------------38
v_anchor_date DATE; -- anchor derived from detail39
v_start_date DATE;40
v_end_date DATE;41
42
------------------------------------------------------------------43
-- Parsed components (used per timeframe)44
------------------------------------------------------------------45
v_year NUMBER;46
v_month NUMBER;47
v_week NUMBER;48
v_quarter NUMBER;49
50
------------------------------------------------------------------51
-- Navigation helpers (← / →)52
------------------------------------------------------------------53
v_direction VARCHAR2(20); -- PREV / NEXT (optional)54
v_step NUMBER; -- step size (days/months)55
56
------------------------------------------------------------------57
-- Range helpers58
------------------------------------------------------------------59
v_days NUMBER;60
BEGIN61
-- RAISE_APPLICATION_ERROR(-20001, 'value: ' || pi_request);62
63
------------------------------------------------------------------64
-- 1) Read current session state65
------------------------------------------------------------------66
v_timeframe := apex_util.get_session_state(pi_timeframe_item_name);67
v_detail := apex_util.get_session_state(pi_timeframe_detail_item_name);68
69
------------------------------------------------------------------70
-- 2) TIMEFRAME clicked (Day / Week / Month / ...)71
------------------------------------------------------------------72
IF pi_request = pi_timeframe_item_name THEN73
74
------------------------------------------------------------------75
-- RULES:76
-- - Never use SYSDATE77
-- - Prefer existing DETAIL if present78
-- - If DETAIL is NULL → build a default detail79
------------------------------------------------------------------80
81
CASE v_timeframe82
83
WHEN 'D' THEN84
85
------------------------------------------------------------------86
-- DAY selected via TIMEFRAME click 87
-- - Always reset to TODAY88
-- - Ignore any existing timeframe detail89
------------------------------------------------------------------90
91
v_anchor_date := TRUNC(SYSDATE);92
93
v_start_date := v_anchor_date;94
v_end_date := v_anchor_date;95
96
v_detail := TO_CHAR(v_anchor_date, 'DD.MM.YYYY');97
98
------------------------------------------------------------------99
-- Output100
------------------------------------------------------------------101
pio_start_date := TO_CHAR(v_start_date, 'DD.MM.YYYY');102
pio_end_date := TO_CHAR(v_end_date, 'DD.MM.YYYY');103
pio_timeframe_detail := v_detail;104
105
------------------------------------------------------------------106
-- Persist detail107
------------------------------------------------------------------108
apex_util.set_session_state(109
pi_timeframe_detail_item_name110
, v_detail111
);112
113
WHEN 'W' THEN114
115
------------------------------------------------------------------116
-- WEEK selected via TIMEFRAME click 117
-- - Always reset to CURRENT ISO WEEK118
-- - Ignore any existing timeframe detail119
------------------------------------------------------------------120
121
-- Anchor = today122
v_anchor_date := TRUNC(SYSDATE);123
124
-- Normalize to Monday of ISO week125
v_start_date := TRUNC(v_anchor_date, 'IW');126
v_end_date := v_start_date + 6;127
128
-- Build timeframe detail: WW.YYYY129
v_week := TO_NUMBER(TO_CHAR(v_anchor_date, 'IW'));130
v_year := TO_NUMBER(TO_CHAR(v_anchor_date, 'YYYY'));131
132
v_detail := LPAD(v_week, 2, '0') || '.' || v_year;133
134
------------------------------------------------------------------135
-- Output136
------------------------------------------------------------------137
pio_start_date := TO_CHAR(v_start_date, 'DD.MM.YYYY');138
pio_end_date := TO_CHAR(v_end_date, 'DD.MM.YYYY');139
pio_timeframe_detail := v_detail;140
141
------------------------------------------------------------------142
-- Persist detail143
------------------------------------------------------------------144
apex_util.set_session_state(145
pi_timeframe_detail_item_name146
, v_detail147
);148
149
WHEN 'M' THEN150
151
------------------------------------------------------------------152
-- MONTH selected via TIMEFRAME click 153
-- - Always reset to CURRENT MONTH154
-- - Ignore any existing timeframe detail155
------------------------------------------------------------------156
157
-- Anchor = today158
v_anchor_date := TRUNC(SYSDATE);159
160
-- Month boundaries161
v_start_date := TRUNC(v_anchor_date, 'MM');162
v_end_date := LAST_DAY(v_anchor_date);163
164
-- Build timeframe detail: MM.YYYY165
v_month := TO_NUMBER(TO_CHAR(v_anchor_date, 'MM'));166
v_year := TO_NUMBER(TO_CHAR(v_anchor_date, 'YYYY'));167
168
v_detail := LPAD(v_month, 2, '0') || '.' || v_year;169
170
------------------------------------------------------------------171
-- Output172
------------------------------------------------------------------173
pio_start_date := TO_CHAR(v_start_date, 'DD.MM.YYYY');174
pio_end_date := TO_CHAR(v_end_date, 'DD.MM.YYYY');175
pio_timeframe_detail := v_detail;176
177
------------------------------------------------------------------178
-- Persist detail179
------------------------------------------------------------------180
apex_util.set_session_state(181
pi_timeframe_detail_item_name182
, v_detail183
);184
185
WHEN 'Q' THEN186
187
------------------------------------------------------------------188
-- QUARTER selected via TIMEFRAME click 189
-- - Always reset to CURRENT QUARTER190
-- - Ignore any existing timeframe detail191
------------------------------------------------------------------192
193
-- Anchor = today194
v_anchor_date := TRUNC(SYSDATE);195
196
-- Determine current quarter and year197
v_quarter := TO_NUMBER(TO_CHAR(v_anchor_date, 'Q'));198
v_year := TO_NUMBER(TO_CHAR(v_anchor_date, 'YYYY'));199
200
-- Quarter boundaries201
CASE v_quarter202
WHEN 1 THEN203
v_start_date := TO_DATE('01.01.' || v_year, 'DD.MM.YYYY');204
v_end_date := TO_DATE('31.03.' || v_year, 'DD.MM.YYYY');205
WHEN 2 THEN206
v_start_date := TO_DATE('01.04.' || v_year, 'DD.MM.YYYY');207
v_end_date := TO_DATE('30.06.' || v_year, 'DD.MM.YYYY');208
WHEN 3 THEN209
v_start_date := TO_DATE('01.07.' || v_year, 'DD.MM.YYYY');210
v_end_date := TO_DATE('30.09.' || v_year, 'DD.MM.YYYY');211
WHEN 4 THEN212
v_start_date := TO_DATE('01.10.' || v_year, 'DD.MM.YYYY');213
v_end_date := TO_DATE('31.12.' || v_year, 'DD.MM.YYYY');214
END CASE;215
216
-- Build timeframe detail: Q.YYYY217
v_detail := v_quarter || '.' || v_year;218
219
------------------------------------------------------------------220
-- Output221
------------------------------------------------------------------222
pio_start_date := TO_CHAR(v_start_date, 'DD.MM.YYYY');223
pio_end_date := TO_CHAR(v_end_date, 'DD.MM.YYYY');224
pio_timeframe_detail := v_detail;225
226
------------------------------------------------------------------227
-- Persist detail228
------------------------------------------------------------------229
apex_util.set_session_state(230
pi_timeframe_detail_item_name231
, v_detail232
);233
234
WHEN 'Y' THEN235
------------------------------------------------------------------236
-- YEAR selected via TIMEFRAME click 237
-- - Always reset to CURRENT YEAR238
-- - Ignore any existing timeframe detail239
------------------------------------------------------------------240
241
-- Anchor = today242
v_anchor_date := TRUNC(SYSDATE);243
244
-- Determine current year245
v_year := TO_NUMBER(TO_CHAR(v_anchor_date, 'YYYY'));246
247
-- Year boundaries248
v_start_date := TO_DATE('01.01.' || v_year, 'DD.MM.YYYY');249
v_end_date := TO_DATE('31.12.' || v_year, 'DD.MM.YYYY');250
251
-- Build timeframe detail: YYYY252
v_detail := TO_CHAR(v_year);253
254
------------------------------------------------------------------255
-- Output256
------------------------------------------------------------------257
pio_start_date := TO_CHAR(v_start_date, 'DD.MM.YYYY');258
pio_end_date := TO_CHAR(v_end_date, 'DD.MM.YYYY');259
pio_timeframe_detail := v_detail;260
261
------------------------------------------------------------------262
-- Persist detail263
------------------------------------------------------------------264
apex_util.set_session_state(265
pi_timeframe_detail_item_name266
, v_detail267
);268
269
ELSE270
NULL;271
END CASE;272
273
------------------------------------------------------------------274
-- Write back session state if needed275
------------------------------------------------------------------276
-- apex_util.set_session_state(pi_timeframe_detail_item_name, ...);277
278
RETURN;279
END IF;280
281
------------------------------------------------------------------282
-- 3) TIMEFRAME DETAIL clicked (radio below)283
------------------------------------------------------------------284
IF pi_request = pi_timeframe_detail_item_name THEN285
286
------------------------------------------------------------------287
-- RULES:288
-- - v_detail is the single source of truth289
-- - Derive anchor/start/end ONLY from v_detail290
------------------------------------------------------------------291
292
CASE v_timeframe293
294
WHEN 'D' THEN295
------------------------------------------------------------------296
-- DAY selected via TIMEFRAME DETAIL click 297
-- Detail can be:298
-- - DD.MM.YYYY (direct day pick)299
-- - PREV_WEEK (navigate -7 days)300
-- - NEXT_WEEK (navigate +7 days)301
--302
-- Anchor = current START_DATE303
------------------------------------------------------------------304
305
------------------------------------------------------------------306
-- Navigation: PREV / NEXT307
------------------------------------------------------------------308
IF v_detail IN ('PREV_WEEK', 'NEXT_WEEK') THEN309
310
-- Use current start date as anchor311
IF pio_start_date IS NULL THEN312
RETURN;313
END IF;314
315
v_anchor_date := TO_DATE(pio_start_date, 'DD.MM.YYYY');316
317
IF v_detail = 'PREV_WEEK' THEN318
v_anchor_date := v_anchor_date - 7;319
ELSE320
v_anchor_date := v_anchor_date + 7;321
END IF;322
323
-- Build new detail324
v_detail := TO_CHAR(v_anchor_date, 'DD.MM.YYYY');325
326
------------------------------------------------------------------327
-- Direct day pick328
------------------------------------------------------------------329
ELSIF REGEXP_LIKE(v_detail, '^[0-9]{2}\.[0-9]{2}\.[0-9]{4}$') THEN330
331
v_anchor_date := TO_DATE(v_detail, 'DD.MM.YYYY');332
333
ELSE334
-- Invalid detail value335
RETURN;336
END IF;337
338
------------------------------------------------------------------339
-- Output (DAY = single date)340
------------------------------------------------------------------341
v_start_date := v_anchor_date;342
v_end_date := v_anchor_date;343
344
pio_start_date := TO_CHAR(v_start_date, 'DD.MM.YYYY');345
pio_end_date := TO_CHAR(v_end_date, 'DD.MM.YYYY');346
pio_timeframe_detail := v_detail;347
348
WHEN 'W' THEN349
350
------------------------------------------------------------------351
-- WEEK selected via TIMEFRAME DETAIL click 352
-- Detail can be:353
-- - WW.YYYY (direct week pick)354
-- - PREV_WEEK (navigate -1 week)355
-- - NEXT_WEEK (navigate +1 week)356
--357
-- Anchor = current START_DATE (Monday)358
------------------------------------------------------------------359
360
------------------------------------------------------------------361
-- Navigation: PREV / NEXT362
------------------------------------------------------------------363
IF v_detail IN ('PREV_WEEK', 'NEXT_WEEK') THEN364
365
-- Use current start date as anchor (Monday of current week)366
IF pio_start_date IS NULL THEN367
RETURN;368
END IF;369
370
v_anchor_date := TO_DATE(pio_start_date, 'DD.MM.YYYY');371
372
IF v_detail = 'PREV_WEEK' THEN373
v_anchor_date := v_anchor_date - 7;374
ELSE375
v_anchor_date := v_anchor_date + 7;376
END IF;377
378
-- Normalize to Monday of ISO week379
v_anchor_date := TRUNC(v_anchor_date, 'IW');380
381
-- Build new detail WW.YYYY382
v_week := TO_NUMBER(TO_CHAR(v_anchor_date, 'IW'));383
v_year := TO_NUMBER(TO_CHAR(v_anchor_date, 'IYYY'));384
385
v_detail := LPAD(v_week, 2, '0') || '.' || v_year;386
387
------------------------------------------------------------------388
-- Direct week pick: WW.YYYY389
------------------------------------------------------------------390
ELSIF REGEXP_LIKE(v_detail, '^[0-9]{2}\.[0-9]{4}$') THEN391
392
v_week := TO_NUMBER(SUBSTR(v_detail, 1, 2));393
v_year := TO_NUMBER(SUBSTR(v_detail, 4, 4));394
395
v_anchor_date :=396
TRUNC(397
TO_DATE(v_year || '0104', 'YYYYMMDD')398
, 'IW'399
) + (v_week - 1) * 7;400
401
ELSE402
-- Invalid detail value403
RETURN;404
END IF;405
406
------------------------------------------------------------------407
-- Output (ISO week = Mon .. Sun)408
------------------------------------------------------------------409
v_start_date := v_anchor_date;410
v_end_date := v_anchor_date + 6;411
412
pio_start_date := TO_CHAR(v_start_date, 'DD.MM.YYYY');413
pio_end_date := TO_CHAR(v_end_date, 'DD.MM.YYYY');414
pio_timeframe_detail := v_detail;415
416
WHEN 'M' THEN417
------------------------------------------------------------------418
-- MONTH selected via TIMEFRAME DETAIL click419
-- Detail can be:420
-- - MM.YYYY (direct month pick)421
-- - PREV_MONTH (navigate -1 month)422
-- - NEXT_MONTH (navigate +1 month)423
--424
-- Anchor = current START_DATE (first day of month)425
------------------------------------------------------------------426
427
------------------------------------------------------------------428
-- Navigation: PREV / NEXT429
------------------------------------------------------------------430
IF v_detail IN ('PREV_MONTH', 'NEXT_MONTH') THEN431
432
-- Use current start date as anchor433
IF pio_start_date IS NULL THEN434
RETURN;435
END IF;436
437
v_anchor_date := TO_DATE(pio_start_date, 'DD.MM.YYYY');438
439
IF v_detail = 'PREV_MONTH' THEN440
v_anchor_date := ADD_MONTHS(v_anchor_date, -1);441
ELSE442
v_anchor_date := ADD_MONTHS(v_anchor_date, 1);443
END IF;444
445
-- Normalize to first day of month446
v_anchor_date := TRUNC(v_anchor_date, 'MM');447
448
-- Build new detail MM.YYYY449
v_month := TO_NUMBER(TO_CHAR(v_anchor_date, 'MM'));450
v_year := TO_NUMBER(TO_CHAR(v_anchor_date, 'YYYY'));451
452
v_detail := LPAD(v_month, 2, '0') || '.' || v_year;453
454
------------------------------------------------------------------455
-- Direct month pick: MM.YYYY456
------------------------------------------------------------------457
ELSIF REGEXP_LIKE(v_detail, '^[0-9]{2}\.[0-9]{4}$') THEN458
459
v_month := TO_NUMBER(SUBSTR(v_detail, 1, 2));460
v_year := TO_NUMBER(SUBSTR(v_detail, 4, 4));461
462
-- First day of selected month463
v_anchor_date :=464
TO_DATE(465
'01.' || LPAD(v_month, 2, '0') || '.' || v_year466
, 'DD.MM.YYYY'467
);468
469
ELSE470
-- Invalid detail value471
RETURN;472
END IF;473
474
------------------------------------------------------------------475
-- Output (MONTH = first .. last day)476
------------------------------------------------------------------477
v_start_date := v_anchor_date;478
v_end_date := LAST_DAY(v_anchor_date);479
480
pio_start_date := TO_CHAR(v_start_date, 'DD.MM.YYYY');481
pio_end_date := TO_CHAR(v_end_date, 'DD.MM.YYYY');482
pio_timeframe_detail := v_detail;483
484
WHEN 'Q' THEN485
486
------------------------------------------------------------------487
-- QUARTER selected via TIMEFRAME DETAIL click488
-- Detail can be:489
-- - Q.YYYY (direct quarter pick)490
-- - PREV_QUARTER (navigate -1 quarter)491
-- - NEXT_QUARTER (navigate +1 quarter)492
--493
-- Anchor = current START_DATE (first day of quarter)494
------------------------------------------------------------------495
496
------------------------------------------------------------------497
-- Navigation: PREV / NEXT498
------------------------------------------------------------------499
IF v_detail IN ('PREV_QUARTER', 'NEXT_QUARTER') THEN500
501
-- Use current start date as anchor502
IF pio_start_date IS NULL THEN503
RETURN;504
END IF;505
506
v_anchor_date := TO_DATE(pio_start_date, 'DD.MM.YYYY');507
508
IF v_detail = 'PREV_QUARTER' THEN509
v_anchor_date := ADD_MONTHS(v_anchor_date, -3);510
ELSE511
v_anchor_date := ADD_MONTHS(v_anchor_date, 3);512
END IF;513
514
-- Normalize to first day of quarter515
v_anchor_date :=516
TRUNC(517
ADD_MONTHS(518
TRUNC(v_anchor_date, 'YYYY')519
, (TO_NUMBER(TO_CHAR(v_anchor_date, 'Q')) - 1) * 3520
)521
, 'MM'522
);523
524
-- Build new detail Q.YYYY525
v_quarter := TO_NUMBER(TO_CHAR(v_anchor_date, 'Q'));526
v_year := TO_NUMBER(TO_CHAR(v_anchor_date, 'YYYY'));527
528
v_detail := v_quarter || '.' || v_year;529
530
------------------------------------------------------------------531
-- Direct quarter pick: Q.YYYY532
------------------------------------------------------------------533
ELSIF REGEXP_LIKE(v_detail, '^[1-4]\.[0-9]{4}$') THEN534
535
v_quarter := TO_NUMBER(SUBSTR(v_detail, 1, 1));536
v_year := TO_NUMBER(SUBSTR(v_detail, 3, 4));537
538
-- First day of selected quarter539
v_anchor_date :=540
ADD_MONTHS(541
TO_DATE('01.01.' || v_year, 'DD.MM.YYYY')542
, (v_quarter - 1) * 3543
);544
545
ELSE546
-- Invalid detail value547
RETURN;548
END IF;549
550
------------------------------------------------------------------551
-- Output (QUARTER = first .. last day)552
------------------------------------------------------------------553
v_start_date := v_anchor_date;554
v_end_date := ADD_MONTHS(v_anchor_date, 3) - 1;555
556
pio_start_date := TO_CHAR(v_start_date, 'DD.MM.YYYY');557
pio_end_date := TO_CHAR(v_end_date, 'DD.MM.YYYY');558
pio_timeframe_detail := v_detail;559
560
WHEN 'Y' THEN561
562
------------------------------------------------------------------563
-- YEAR selected via TIMEFRAME DETAIL click564
-- Detail can be:565
-- - YYYY (direct year pick)566
-- - PREV_YEAR (navigate -1 year)567
-- - NEXT_YEAR (navigate +1 year)568
--569
-- Anchor = current START_DATE (01.01.YYYY)570
------------------------------------------------------------------571
572
------------------------------------------------------------------573
-- Navigation: PREV / NEXT574
------------------------------------------------------------------575
IF v_detail IN ('PREV_YEAR', 'NEXT_YEAR') THEN576
577
-- Use current start date as anchor578
IF pio_start_date IS NULL THEN579
RETURN;580
END IF;581
582
v_anchor_date := TO_DATE(pio_start_date, 'DD.MM.YYYY');583
584
IF v_detail = 'PREV_YEAR' THEN585
v_anchor_date := ADD_MONTHS(v_anchor_date, -12);586
ELSE587
v_anchor_date := ADD_MONTHS(v_anchor_date, 12);588
END IF;589
590
-- Normalize to first day of year591
v_year := TO_NUMBER(TO_CHAR(v_anchor_date, 'YYYY'));592
v_anchor_date := TO_DATE('01.01.' || v_year, 'DD.MM.YYYY');593
594
-- Build new detail YYYY595
v_detail := TO_CHAR(v_year);596
597
------------------------------------------------------------------598
-- Direct year pick: YYYY599
------------------------------------------------------------------600
ELSIF REGEXP_LIKE(v_detail, '^[0-9]{4}$') THEN601
602
v_year := TO_NUMBER(v_detail);603
604
-- First day of selected year605
v_anchor_date := TO_DATE('01.01.' || v_year, 'DD.MM.YYYY');606
607
ELSE608
-- Invalid detail value609
RETURN;610
END IF;611
612
------------------------------------------------------------------613
-- Output (YEAR = full year)614
------------------------------------------------------------------615
v_start_date := v_anchor_date;616
v_end_date := TO_DATE('31.12.' || TO_CHAR(v_year), 'DD.MM.YYYY');617
618
pio_start_date := TO_CHAR(v_start_date, 'DD.MM.YYYY');619
pio_end_date := TO_CHAR(v_end_date, 'DD.MM.YYYY');620
pio_timeframe_detail := v_detail;621
622
ELSE623
NULL;624
END CASE;625
626
------------------------------------------------------------------627
-- Output handling (common)628
------------------------------------------------------------------629
-- pio_start_date := TO_CHAR(v_start_date,'DD.MM.YYYY');630
-- pio_end_date := TO_CHAR(v_end_date,'DD.MM.YYYY');631
632
RETURN;633
END IF;634
635
636
637
------------------------------------------------------------------638
-- CUSTOM SEARCH (manual date range → auto-detect best timeframe)639
------------------------------------------------------------------640
IF pi_request LIKE '%CUSTOM_SEARCH%' THEN641
642
------------------------------------------------------------------643
-- Validate input644
------------------------------------------------------------------645
IF pio_start_date IS NULL646
OR pio_end_date IS NULL647
THEN648
RETURN;649
END IF;650
651
v_start_date := TO_DATE(pio_start_date, 'DD.MM.YYYY');652
v_end_date := TO_DATE(pio_end_date, 'DD.MM.YYYY');653
654
-- ensure correct order655
IF v_end_date < v_start_date THEN656
RETURN;657
END IF;658
659
v_days := v_end_date - v_start_date + 1;660
661
------------------------------------------------------------------662
-- 1) EXACT MATCHES (highest priority)663
------------------------------------------------------------------664
665
-- Single day666
IF v_days = 1 THEN667
668
pio_timeframe := 'D';669
pio_timeframe_detail := TO_CHAR(v_start_date, 'DD.MM.YYYY');670
671
-- Exact ISO week (Mon–Sun)672
ELSIF v_start_date = TRUNC(v_start_date, 'IW')673
AND v_end_date = TRUNC(v_start_date, 'IW') + 6674
THEN675
pio_timeframe := 'W';676
677
pio_timeframe_detail :=678
LPAD(TO_CHAR(v_start_date,'IW'),2,'0') || '.' ||679
TO_CHAR(v_start_date,'IYYY');680
681
-- Exact month682
ELSIF v_start_date = TRUNC(v_start_date, 'MM')683
AND v_end_date = LAST_DAY(v_start_date)684
THEN685
pio_timeframe := 'M';686
pio_timeframe_detail := TO_CHAR(v_start_date,'MM.YYYY');687
688
-- Exact quarter689
ELSIF v_start_date =690
ADD_MONTHS(691
TRUNC(v_start_date,'YYYY')692
, (TO_NUMBER(TO_CHAR(v_start_date,'Q')) - 1) * 3693
)694
AND v_end_date =695
ADD_MONTHS(696
ADD_MONTHS(697
TRUNC(v_start_date,'YYYY')698
, (TO_NUMBER(TO_CHAR(v_start_date,'Q')) - 1) * 3699
)700
, 3701
) - 1702
THEN703
pio_timeframe := 'Q';704
pio_timeframe_detail :=705
TO_CHAR(v_start_date,'Q') || '.' ||706
TO_CHAR(v_start_date,'YYYY');707
708
-- Exact year709
ELSIF v_start_date = TO_DATE('01.01.' || TO_CHAR(v_start_date,'YYYY'),'DD.MM.YYYY')710
AND v_end_date = TO_DATE('31.12.' || TO_CHAR(v_start_date,'YYYY'),'DD.MM.YYYY')711
THEN712
pio_timeframe := 'Y';713
pio_timeframe_detail := TO_CHAR(v_start_date,'YYYY');714
715
------------------------------------------------------------------716
-- 2) BEST-FIT HEURISTIC (UX-friendly)717
------------------------------------------------------------------718
ELSE719
720
------------------------------------------------------------------721
-- Inside a single ISO week → WEEK722
------------------------------------------------------------------723
IF TRUNC(v_start_date,'IW') = TRUNC(v_end_date,'IW') THEN724
725
pio_timeframe := 'W';726
pio_timeframe_detail :=727
LPAD(TO_CHAR(v_start_date,'IW'),2,'0') || '.' ||728
TO_CHAR(v_start_date,'IYYY');729
730
------------------------------------------------------------------731
-- Inside a single month → MONTH732
------------------------------------------------------------------733
ELSIF TRUNC(v_start_date,'MM') = TRUNC(v_end_date,'MM') THEN734
735
pio_timeframe := 'M';736
pio_timeframe_detail := TO_CHAR(v_start_date,'MM.YYYY');737
738
------------------------------------------------------------------739
-- Inside a single quarter → QUARTER740
------------------------------------------------------------------741
ELSIF TO_CHAR(v_start_date,'YYYYQ') = TO_CHAR(v_end_date,'YYYYQ') THEN742
743
pio_timeframe := 'Q';744
pio_timeframe_detail :=745
TO_CHAR(v_start_date,'Q') || '.' ||746
TO_CHAR(v_start_date,'YYYY');747
748
------------------------------------------------------------------749
-- Spans multiple quarters but within one year → YEAR750
------------------------------------------------------------------751
ELSIF TO_CHAR(v_start_date,'YYYY') = TO_CHAR(v_end_date,'YYYY') THEN752
753
pio_timeframe := 'Y';754
pio_timeframe_detail := TO_CHAR(v_start_date,'YYYY');755
756
------------------------------------------------------------------757
-- Spans multiple years → YEAR (use start year as anchor)758
------------------------------------------------------------------759
ELSE760
pio_timeframe := 'Y';761
pio_timeframe_detail := TO_CHAR(v_start_date,'YYYY');762
END IF;763
764
END IF;765
766
------------------------------------------------------------------767
-- Persist session state768
------------------------------------------------------------------769
apex_util.set_session_state(pi_timeframe_item_name, pio_timeframe);770
apex_util.set_session_state(pi_timeframe_detail_item_name, pio_timeframe_detail);771
772
RETURN;773
END IF;774
775
776
777
778
779
END pr_get_date_range;780
781
782
783
end UTIL_PKG;784
/Timeframe Detail View
This view is used as the List of Values (LOV) source for timeframe navigation.
SQL
xxxxxxxxxx225
1
CREATE OR REPLACE FORCE EDITIONABLE VIEW "V_P1410_TIMEFRAME_DETAIL" ("DISPLAY_VALUE", "RETURN_VALUE") AS 2
WITH params AS (3
SELECT4
v('P1410_TIMEFRAME') AS tf5
, TRUNC(6
TO_DATE(7
v('P1410_START_DATE')8
, 'DD.MM.YYYY'9
)10
) AS v_start_date11
, ( SELECT value12
FROM nls_session_parameters13
WHERE parameter = 'NLS_DATE_LANGUAGE'14
) AS nls_lang15
FROM dual16
),17
days AS (18
SELECT19
TRUNC(p.v_start_date, 'IW') + (LEVEL - 1) AS day_ref20
, LEVEL AS sort_order21
FROM params p22
CONNECT BY LEVEL <= 723
),24
week_base AS (25
SELECT TRUNC(v_start_date, 'IW') AS base_week26
FROM params27
),28
month_base AS (29
SELECT TRUNC(v_start_date, 'MM') AS base_month30
FROM params31
),32
levels7 AS (33
SELECT LEVEL AS lvl34
FROM dual35
CONNECT BY LEVEL <= 736
)37
SELECT38
display_value39
, return_value40
FROM (41
42
------------------------------------------------------------------43
-- DAY (DD.MM.YYYY)44
------------------------------------------------------------------45
SELECT46
'⮜' AS display_value47
, 'PREV_WEEK' AS return_value48
, 0 AS sort_order49
FROM params50
WHERE tf = 'D'51
52
UNION ALL53
54
SELECT55
INITCAP(56
TO_CHAR(day_ref,'Day','NLS_DATE_LANGUAGE='||p.nls_lang)57
) || ' (' || TO_CHAR(day_ref,'DD.MM.YYYY') || ')'58
, TO_CHAR(day_ref,'DD.MM.YYYY')59
, d.sort_order60
FROM days d61
CROSS JOIN params p62
WHERE p.tf = 'D'63
64
UNION ALL65
66
SELECT67
'⮞'68
, 'NEXT_WEEK'69
, 870
FROM params71
WHERE tf = 'D'72
73
74
------------------------------------------------------------------75
-- WEEK (WW.YYYY)76
------------------------------------------------------------------77
UNION ALL78
79
SELECT80
'⮜'81
, 'PREV_WEEK'82
, 083
FROM params84
WHERE tf = 'W'85
86
UNION ALL87
88
SELECT89
'Week ' ||90
TO_CHAR(base_week + ((lvl - 4) * 7),'IW') ||91
' (' || TO_CHAR(base_week + ((lvl - 4) * 7),'IYYY') || ')'92
, TO_CHAR(base_week + ((lvl - 4) * 7),'IW') || '.' ||93
TO_CHAR(base_week + ((lvl - 4) * 7),'IYYY')94
, lvl95
FROM week_base96
, levels797
, params98
WHERE tf = 'W'99
100
UNION ALL101
102
SELECT103
'⮞'104
, 'NEXT_WEEK'105
, 8106
FROM params107
WHERE tf = 'W'108
109
110
------------------------------------------------------------------111
-- MONTH (MM.YYYY)112
------------------------------------------------------------------113
UNION ALL114
115
SELECT116
'⮜'117
, 'PREV_MONTH'118
, 0119
FROM params120
WHERE tf = 'M'121
122
UNION ALL123
124
SELECT125
TO_CHAR(126
ADD_MONTHS(base_month, lvl - 4)127
, 'Mon'128
, 'NLS_DATE_LANGUAGE='||p.nls_lang129
) || ' ' ||130
TO_CHAR(ADD_MONTHS(base_month, lvl - 4),'YYYY')131
, TO_CHAR(ADD_MONTHS(base_month, lvl - 4),'MM.YYYY')132
, lvl133
FROM month_base134
, levels7135
, params p136
WHERE tf = 'M'137
138
UNION ALL139
140
SELECT141
'⮞'142
, 'NEXT_MONTH'143
, 8144
FROM params145
WHERE tf = 'M'146
147
148
------------------------------------------------------------------149
-- QUARTER (Q.YYYY)150
------------------------------------------------------------------151
UNION ALL152
153
SELECT154
'⮜'155
, 'PREV_QUARTER'156
, 0157
FROM params158
WHERE tf = 'Q'159
160
UNION ALL161
162
SELECT163
'Q' || q.lvl || ' / ' || y.base_year164
, q.lvl || '.' || y.base_year165
, q.lvl166
FROM (167
SELECT TO_NUMBER(TO_CHAR(v_start_date,'YYYY')) AS base_year168
FROM params169
) y170
, (171
SELECT LEVEL AS lvl FROM dual CONNECT BY LEVEL <= 4172
) q173
, params174
WHERE tf = 'Q'175
176
UNION ALL177
178
SELECT179
'⮞'180
, 'NEXT_QUARTER'181
, 5182
FROM params183
WHERE tf = 'Q'184
185
186
------------------------------------------------------------------187
-- YEAR (YYYY)188
------------------------------------------------------------------189
UNION ALL190
191
SELECT192
'⮜'193
, 'PREV_YEAR'194
, 0195
FROM params196
WHERE tf = 'Y'197
198
UNION ALL199
200
SELECT201
TO_CHAR(base_year + (lvl - 4))202
, TO_CHAR(base_year + (lvl - 4))203
, lvl204
FROM (205
SELECT TO_NUMBER(TO_CHAR(v_start_date,'YYYY')) AS base_year206
FROM params207
)208
, (209
SELECT LEVEL AS lvl FROM dual CONNECT BY LEVEL <= 7210
)211
, params212
WHERE tf = 'Y'213
214
UNION ALL215
216
SELECT217
'⮞'218
, 'NEXT_YEAR'219
, 8220
FROM params221
WHERE tf = 'Y'222
223
)224
ORDER BY225
sort_order;Custom Date Range Auto-Detection
When a user manually enters a start date and end date and clicks the search button, the engine automatically determines the most suitable timeframe:
- A single day is treated as Day
- A full ISO week as Week
- A full month as Month
- A full quarter as Quarter
- A full year as Year
- Any other range is mapped to the best matching context
This keeps the UI consistent and avoids confusing states.
Why This Works Well in APEX
- Relies entirely on server-side PL/SQL
- Uses standard APEX processes and page items
- Avoids JavaScript complexity
- Easy to debug and extend
- Reusable across multiple pages and applications
Once integrated, the same logic can be used for reports, charts, KPIs, and dashboards without any duplication.
Use Cases
- Management dashboards
- Reporting applications
- Analytics pages
- Invoice and billing systems
- Monitoring and operational views
Anywhere a consistent date filter is required, this engine can be applied.
Conclusion
Handling timeframes in Oracle APEX does not need to be complex or fragile.
By centralizing all logic in a reusable PL/SQL package and keeping the page implementation clean, you get a robust, predictable, and maintainable solution that scales with your application.
By centralizing all logic in a reusable PL/SQL package and keeping the page implementation clean, you get a robust, predictable, and maintainable solution that scales with your application.
Explore the full implementation:
GitHub: oracle-apex-smart-timeframe
GitHub: oracle-apex-smart-timeframe
- Try the Engine: Download the code from GitHub and integrate it into your APEX app for instant, consistent date filtering.
- Customize as Needed: Adjust the styles and page item names to fit your application’s theme and requirements.
- Share Your Experience: Let us know how it works for your use case or if you have improvements!
If you want further customization or more visual elements, just let me know!
{fullWidth}