I have received a couple of questions per email, similar to this one
on limiting the value of a date picker item. That shows this is an issue that needs to be solved from the development team. Whereby this is an easy issue. If you look behind the code, you will se there are one procedure and one view involved. The procedure code is generating a small pop-up window showing a calendar based on the number of the years the view is returning. I managed to get that customized by doing the following:
First of all, create a package like this:
1.
CREATE OR REPLACE PACKAGE limit_datepicker
AS
x NUMBER;
y NUMBER;
FUNCTION get_x
RETURN NUMBER;
FUNCTION get_y
RETURN NUMBER;
PROCEDURE show_as_popup_calendar (
p_request IN VARCHAR2 DEFAULT NULL,
p_title IN VARCHAR2 DEFAULT NULL,
p_yyyy IN VARCHAR2 DEFAULT NULL,
p_mm IN VARCHAR2 DEFAULT NULL,
p_dd IN VARCHAR2 DEFAULT NULL,
p_hh IN VARCHAR2 DEFAULT NULL,
p_mi IN VARCHAR2 DEFAULT NULL,
p_pm IN VARCHAR2 DEFAULT NULL,
p_element_index IN VARCHAR2 DEFAULT NULL,
p_form_index IN VARCHAR2 DEFAULT NULL,
p_date_format IN VARCHAR2 DEFAULT 'MM/DD/YYYY',
p_bgcolor IN VARCHAR2 DEFAULT '#336699',
p_white_foreground IN VARCHAR2 DEFAULT 'Y',
p_application_format IN VARCHAR2 DEFAULT 'N',
p_lang IN VARCHAR2 DEFAULT NULL,
p_application_id IN VARCHAR2 DEFAULT NULL,
p_security_group_id IN VARCHAR2 DEFAULT NULL,
p_start_year IN NUMBER DEFAULT 1919,
p_end_year IN NUMBER DEFAULT 2050
);
END limit_datepicker;
/
CREATE OR REPLACE PACKAGE BODY limit_datepicker
AS
FUNCTION get_x
RETURN NUMBER
IS
BEGIN
RETURN limit_datepicker.x;
END get_x;
FUNCTION get_y
RETURN NUMBER
IS
BEGIN
RETURN limit_datepicker.y;
END get_y;
PROCEDURE show_as_popup_calendar (
p_request IN VARCHAR2 DEFAULT NULL,
p_title IN VARCHAR2 DEFAULT NULL,
p_yyyy IN VARCHAR2 DEFAULT NULL,
p_mm IN VARCHAR2 DEFAULT NULL,
p_dd IN VARCHAR2 DEFAULT NULL,
p_hh IN VARCHAR2 DEFAULT NULL,
p_mi IN VARCHAR2 DEFAULT NULL,
p_pm IN VARCHAR2 DEFAULT NULL,
p_element_index IN VARCHAR2 DEFAULT NULL,
p_form_index IN VARCHAR2 DEFAULT NULL,
p_date_format IN VARCHAR2 DEFAULT 'MM/DD/YYYY',
p_bgcolor IN VARCHAR2 DEFAULT '#336699',
p_white_foreground IN VARCHAR2 DEFAULT 'Y',
p_application_format IN VARCHAR2 DEFAULT 'N',
p_lang IN VARCHAR2 DEFAULT NULL,
p_application_id IN VARCHAR2 DEFAULT NULL,
p_security_group_id IN VARCHAR2 DEFAULT NULL,
p_start_year IN NUMBER DEFAULT 1919,
p_end_year IN NUMBER DEFAULT 2050
)
IS
BEGIN
limit_datepicker.x := p_start_year;
limit_datepicker.y := p_end_year;
wwv_flow_utilities.show_as_popup_calendar (p_request,
p_title,
p_yyyy,
p_mm,
p_dd,
p_hh,
p_mi,
p_pm,
p_element_index,
p_form_index,
p_date_format,
p_bgcolor,
p_white_foreground,
p_application_format,
p_lang,
p_application_id,
p_security_group_id
);
END;
END limit_datepicker;
/
2. GRANT EXECUTE ON limit_datepicker TO PUBLIC;
3. CREATE OR REPLACE PUBLIC SYNONYM limit_datepicker FOR <<your_schema>>.limit_datepicker;
To be able to limit the values the view returns you need to do the following:
1. log in a SYS
2. ALTER SESSION SET current_schema=FLOWS_030000 (yor flows schema, FLOWS_030000 in my case)
3.
CREATE OR REPLACE VIEW wwv_flow_years
AS
SELECT NVL (b.start_year - 1, 1918) + LEVEL
FROM DUAL a,
(SELECT limit_datepicker.get_x start_year
FROM DUAL) b,
(SELECT limit_datepicker.get_y end_year
FROM DUAL) c
CONNECT BY LEVEL < NVL ((c.end_year + 1) - (b.start_year - 1),
2051 - 1918)
The view will now return the same result for the standard date picker or the value you request if you use the custom date picker.
And finaly, create the following on your page, which will contain the date picker item, limiting the years to the values you determine;
1. Create a hidden item on your page (Page 1 in my case)
P1_SECURITY_GROUP_ID
with a source PL/SQL Expression or Function
htmldb_custom_auth.get_security_group_id
2. Create the javascript and put it in the header of your page. Please note, you need to take care of the parameters yourself - for example p_yyyy or p_mm:
<script type="text/javascript">
function f_popup_date(p_this)
{
var item_name = $x(p_this).name
var app_id = &APP_ID.
var sec_gr_id = $x('P1_SECURITY_GROUP_ID').value
w = open("limit_datepicker.show_as_popup_calendar" +
"?p_element_index=" + escape(item_name) +
"&p_form_index=" + escape('0') +
"&p_date_format=" + escape('DD-MON-RR') +
"&p_bgcolor=" + escape('#666666') +
"&p_dd=" + escape('') +
"&p_hh=" + escape('') +
"&p_mi=" + escape('') +
"&p_pm=" +
"&p_yyyy=" + escape('2008') +
"&p_lang=" + escape('en') +
"&p_application_format=" + escape('N') +
"&p_application_id=" + escape(app_id) +
"&p_security_group_id=" + escape(sec_gr_id) +
"&p_start_year=" + escape('2007') +
"&p_end_year=" + escape('2008') +
"&p_mm=" + escape('01'),
"winLov",
"Scrollbars=no,resizable=yes,width=258,height=210");
if (w.opener == null)
w.opener = self;
w.focus();
}
</script>
You will need to replace the values to what you need (2007 to 2008 in my case).
3. Date-Picker Item (P1_DATE_FROM in my case) is a normal text item with the following code in the Post Element Text:
<a href="javascript:f_popup_date('P1_DATE_FROM');">
<img src="/i/asfdcldr.gif"
style="cursor:pointer;valign:bottom" /></a>
The downside of this approach is that you need to modify one of the apex views and take care you recreate it after the next update. I still didn't have time to find out why this doesn't work on XE. I will have a look into that issue soon and post the results here.
