Limit or Extend a Datepicker in ApEx

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.