The Create/Edit Plug-in page is the main page used in APEX while developing plug-ins and, as such, it's very important to become well acquainted with it. To find the page in APEX, click on the Plug-ins option in the Shared Components menu (under User Interface). Once there, you can either click the create button or drill down on an existing plug-in. The page contains 12 main regions. Some regions were designed specifically for plug-in developers to actually create and maintain the plug-in while other regions were designed for plug-in users to configure the plug-in in an application.

You may notice some subtle differences when viewing this page at different times. Your mind is not playing tricks on you; the page changes depending on a number of factors. For example, not all regions are available at all times or for all plug-in types. Also, the items in a region, as well as the times at which those items can be edited, can vary as well. All of this will be explained in the sections that follow.

Name

The Name region (see Figure 11-1) contains items that allow plug-in developers to identify a plug-in as well as the plug-in type.

Figure 11-1. The Name region of the Create/Edit page

The Name attribute can be thought of this as the "display name" for plug-in users. Plug-in users will see and select this name to use the plug-in via the native component interface provided by the Application Builder. Exactly where they see the name will depend on the plug-in type; for example, if you create an item plug-in, the Name will be displayed in the Display As attribute of items in the application.

The Internal Name attribute serves as a unique identifier for the plug-in within an application. The Internal Name should be all uppercase; lower- or mixed-case values will be converted to uppercase when creating or updating the plug-in. Note that this attribute cannot be modified if the plug-in is being used in the application.

When importing a plug-in into an application, the value of the Internal Name attribute will be checked against other plug-ins in the application. If a plug-in with the same Internal Name already exists, users will need to confirm that they wish to replace the existing plug-in. For this reason, uniqueness of the Internal Name attribute is much more important than uniqueness of the Name attribute.

Note

See the Plug-in Best Practices section at the end of this chapter for tips on naming and other topics.

The Type attribute, as I'm sure you've already guessed, defines the type of plug-in you are working with. This is obviously a major attribute of a plug-in and as such its value drives some of the other regions and items that are visible on the page. This attribute, like the Internal Name attribute, cannot be modified if the plug-in is being used in the application.

The Category attribute (not visible in the image) is only available for Dynamic Action plug-ins. This attribute is used to group similar dynamic actions together which makes selection a little easier for plug-in users; it has no other impact.

Subscription

The Subscription region (see Figure 11-2) is the same as it is throughout APEX. The Reference Master Plug-in From item allows you to link to a "master" copy of the plug-in. The master copy often resides in a centralized application used only for this purpose. Once established, the link between master and child allows for updates to be performed on a one-off basis from the child or in bulk from the master to all children.

Figure 11-2. The Subscription region of the Create/Edit page

Settings

The Settings region (see Figure 11-3) displays the custom application level attributes (covered later in this section) that have been added to the plug-in. The File Prefix attribute will exist by default. All other attributes displayed here are created by plug-in developers to allow plug-in users to declaratively configure various aspects of the plug-in.

Figure 11-3. The Settings region of the Create/Edit page

Source

Plug-in developers must write one or more PL/SQL functions for their plug-in to work. The PL/SQL Code attribute in this region (see Figure 11-4) is where the code for these functions typically resides; however, it is possible to use code compiled in the database instead. The number and type of functions required will vary by the plug-in type—see the next section on Callbacks for more details.

Figure 11-4. The Source region of the Create/Edit page

Callbacks

The Callbacks region (see Figure 11-5) is used to register the PL/SQL functions you have defined (see the previous section on Source) with what is expected by the framework for the given plug-in type. Simply add the function name and the framework will call the function when needed (except the AJAX function, which you will manually call when needed via JavaScript).

Figure 11-5. The Callbacks region of the Create/Edit page

You can use any valid Oracle name for the functions, but the signature must match that which is expected for a given function type. The easiest way to look up the expected signature is to click on the label for the function name. If the function is compiled in a package, simply prefix the function name with the package name followed by a period.

There are four different types of functions. The render function is used to render the plug-in on the page. The Ajax function is used to make asynchronous calls from the plug-in to the server for additional data and processing. The validation function is used to validate an item's value when the page is submitted. Finally, the execution function (not displayed) is used to perform some type of processing.

Exactly which functions are available, required, or optional will vary based the plug-in type, as outlined in Table 11-1.

Table 11-1. Callback Function Requirements by Plug-in Type

	Item Plug-in	Region Plug-in	Dynamic Action Plug-in	Process Plug-in
Render Function	required	required	required
Ajax Function	optional	optional	optional
Validation Function	optional
Execution Function				required

Standard Attributes

Plug-in developers can use two main types of attributes in plug-ins: standard (covered here) and custom (covered in the next section). Both standard and custom attributes are a plug-in developer's means to provide declarative options to plug-in users that can change the characteristics or behavior of a plug-in.

In APEX, many attributes for a given component type are the same. For example, the Select List and Check Box items both have a List of Values definition. Likewise, both the Interactive and Classic Report regions have a Source attribute that requires a SQL Query. Because plug-ins are simply extensions of these native component types, it's likely that most of them will use some of these same attributes. Standard attributes make it very easy to add many of these attributes to a plug-in.

Always try to use standard attributes before creating custom equivalents. This will save you time and reduce the number of custom attributes used by your plug-ins. More importantly, standard attributes will be displayed where APEX developers are already familiar with them and in a way that's consistent with other instances of a particular component type.

The standard attributes available vary by the plug-in type. Figure 11-6 shows the standard attributes available to Item type plug-ins, Figure 11-7 shows the attributes for Region type plug-ins, and Figure 11-8 shows the attributes for Dynamic Action–type plug-ins. Process-type plug-ins do not have any standard attributes.

Figure 11-6. The Standard Attributes region of the Create/Edit page (Item plug-in)

Figure 11-7. The Standard Attributes region of the Create/Edit page (Region plug-in)

Figure 11-8. The Standard Attributes region of the Create/Edit page (Dynamic Action plug-in)

Custom Attributes

The standard attributes, while extremely useful, could never include all the possible attributes you may want to add to a plug-in. To remedy this fact, the APEX plug-in architecture allows you to create custom attributes which are then exposed to plug-in users as settings (see Figure 11-9). To add an attribute to a plug-in, simply click the Add Attribute button. This will redirect you to the Edit Attribute page where the attribute can be configured.

Figure 11-9. The Custom Attributes region of the Create/Edit page

Custom attributes can be displayed as a number of different element types and be made to display conditionally (based on the value of another custom attribute). Currently you can create up to ten custom attributes at the application level and another ten custom attributes at the component level. The number of custom attributes available will likely increase with future releases of APEX—this will help make more sophisticated plug-ins with declarative options that are easy to use.

Placing attributes at the application level of a plug-in is a good way to reduce the number of decisions that plug-in users face each time they use a plug-in and it's a good way to enforce consistency. For example, an attribute that changes the color scheme of a plug-in may be best at the application level so that the plug-in is displayed the same way on different pages.

On the other hand, not all attributes are suited for the application level. An attribute used to identify a page level item may be better off as a component-level attribute. This would allow the plug-in to be used more than once in an application without different instances of the plug-in interfering with each other.

The Custom Attributes region is only available after the plug-in is created. Also, certain parts of custom attributes, such as the Scope, Attribute (number), and LOV return values, cannot be modified if the plug-in is being used in the application.

Files

Plug-ins often require the use of external files for various functionality. The most common types of files used in plug-ins include CSS, JavaScript, and image files (see Figure 11-10). Files uploaded here are stored in the database and directly associated with the plug-in. The substitution string #PLUGIN_PREFIX# can be used to reference these files from your code, much like #APP_IMAGES# is used to refer to files associated with an application. The Files region is only available after the plug-in is created.

Figure 11-10. The Files region of the Create/Edit page

Events

The Events region (see Figure 11-11) allows plug-in developers to add custom events to dynamic actions in an application. This ability would only be useful for plug-ins that trigger custom events. For example, if you created a countdown timer plug-in, creating a "timeout" event would allow plug-in users to create dynamic actions that are based on the event. It's up to plug-in developers to trigger custom events at the appropriate times. The Events region is only visible after the plug-in is created.

Figure 11-11. The Events region of the Create/Edit page

Information

The Information region (see Figure 11-12) allows you to add the version number of the plug-in as well as specify an About URL. The About URL should point to a location on the Web where plug-in users can learn more about the plug-in.

Figure 11-12. The Information region of the Create/Edit page

Help Text

The Help Text region (see Figure 11-13) allows plug-in developers to add built-in documentation for plug-in users. Any documentation added by plug-in developers will be visible to plug-in users via the same region.

Figure 11-13. The Help Text region of the Create/Edit page

Comments

The Comments region (see Figure 11-14) is the same as it is with other components throughout APEX. Plug-in users can use the Comments item to leave any notes they wish.

Figure 11-14. The Comments region of the Create/Edit page

Many tasks involved with developing plug-ins are common. Examples include

A number of PL/SQL APIs have been developed to help make these tasks easier to complete than they would otherwise be. Learning about these APIs is important, even if only at the most basic level, as it could save you from reinventing the wheel when you really only want to build a plug-in. This section provides an introduction to the PL/SQL APIs most commonly used for plug-in development in APEX.

FUNCTION password_render (
   p_item                IN APEX_PLUGIN.T_PAGE_ITEM,
   p_plugin              IN APEX_PLUGIN.T_PLUGIN,
   p_value               IN VARCHAR2,
   p_is_readonly         IN BOOLEAN,
   p_is_printer_friendly IN BOOLEAN
)

   RETURN APEX_PLUGIN.T_PAGE_ITEM_RENDER_RESULT

sys.htp.p(
   '<input type="password" name="'
   || apex_plugin.get_input_name_for_page_item(p_is_multi_value => FALSE)
   || '" id="' || p_item.name ||
   '" size="' || p_item.element_width || '" maxlength="'
   || p_item.element_max_length
   '" ' || p_item.element_attributes || '/>'),

l_onload_code :=
  'apex.jQuery("div#' || p_region.static_id || '").calendar({'
   || apex_javascript.add_attribute(
          p_name => 'ajaxIdentifier',
          p_value => apex_plugin.get_ajax_identifier(),
          p_omit_null => FALSE,
          p_add_comma => FALSE)
   || '});';

apex_javascript.add_onload_code (
   p_code => l_onload_code
);

IF apex_application.g_debug
THEN
   apex_plugin_util.debug_page_item (
      p_plugin              => p_plugin,
      p_page_item           => p_item,
      p_value               => p_value,
      p_is_readonly         => p_is_readonly,
      p_is_printer_friendly => p_is_printer_friendly
   );
END IF;

apex_css.add_file(
   p_name      => 'fullcalendar',
   p_directory => p_plugin.file_prefix,
   p_version   => NULL
);

<link rel="stylesheet" href="wwv_flow_file_mgr.get_file?p_plugin_id=1234

&p_security_group_id=1234&p_file_name=fullcalendar.css" type="text/css" />

l_onload_code :=
  'apex.jQuery("input#' || p_item.name || '").sbip_password({'
   || apex_javascript.add_attribute('warningMsgIcon', l_message_icon)
   || apex_javascript.add_attribute('warningMsgText', l_message_text)
   || apex_javascript.add_attribute('warningMsgWidth', l_message_width)
   || apex_javascript.add_attribute('warningMsgAlignment', l_message_alignment)
   || apex_javascript.add_attribute('passwordAlignment', l_password_alignment)
   || apex_javascript.add_attribute('offset', l_offset, TRUE, FALSE)
   || '});';

apex_javascript.add_onload_code(
  p_code => l_onload_code
);

Unless you're creating a process plug-in, JavaScript will likely be an important part of your plug-in. If you come from an Oracle background, you may be comfortable working with PL/SQL but a little apprehensive about JavaScript. Newcomers to JavaScript can spend a lot of time just figuring out a good way to organize their code. The jQuery UI Widget Factory provides just that: a systematic approach to writing JavaScript that provides organization as well as a number of other features.

Because this chapter is about writing plug-ins for APEX, I will use the term widget in place of plug-in when referring to the jQuery UI Widget Factory—although the terms are typically interchangeable. The jQuery UI Widget Factory allows you to create stateful jQuery widgets with minimal effort. Stateful means that the widget keeps track of its attribute values and even allows attribute updates and method calls after the widget has been initialized.

One of the best ways to learn about the jQuery UI Widget Factory is to see an example of how it is used. The following tutorial will walk you through the steps needed to build a widget.

This tutorial will create a widget named lengthLimit. The lengthLimit widget will be used to enhance standard input elements so that they warn users when the number of characters entered approaches a defined maximum length. The widget will set the text color to a warning color when the text reaches 80% of the maximum length and then to an error color when the limit is reached. The widget will also trim any characters entered in the input that exceed the maximum length.

The $.widget function is called to create the widget. The first parameter passed to the function is the namespace and name of the widget which are separated by a period. The "ui" namespace is used by all of the widgets in the jQuery UI library. The second parameter (currently just an empty object) is the prototype to be associated with the widget.

apex.jQuery.widget("ui.lengthLimit", {
   //widget code here
});

Before continuing to build out the widget, it is important to take care of one important issue: $. Most people who use jQuery are familiar with using the dollar sign as a shorthand reference to the jQuery object rather than spelling out jQuery, or as it's been exposed in APEX, apex.jQuery. While it's possible to use the dollar sign in your widget code, it's best to protect references to the dollar sign from collisions with other JavaScript libraries. The technique used to do this is to wrap your code in an anonymous function that passes the jQuery object as an actual parameter to itself as a formal parameter which uses the dollar sign for its name. If this sounds complex and confusing don't worry— it requires only two lines of code. Note that the previous reference to apex.jQuery has been replaced by the dollar sign now that it is safe to do so.

(function($){
$.widget("ui.lengthLimit", {
   //widget code here
});
})(apex.jQuery);

Let's continue building the widget by adding the options object. The options object is used to set default values for configuration options used by your widget.

(function($){
$.widget("ui.lengthLimit", {
   options: {
      warningColor: "yellow",
      errorColor: "red",
      maxLength: 50
   }
});
})(apex.jQuery);

Next we'll add a function named create which will be invoked automatically when the widget is first instantiated.

(function($){
$.widget("ui.lengthLimit", {
   options: {
      warningColor: "orange",
      errorColor: "red",
      maxLength: 50
   },
   _create: function() {
      var uiw = this;

      this.element.change(function() {
         var $textElmt = $(this);
         var currLength = $textElmt.val().length;
         var currPercent = currLength/uiw.options.maxLength;

         if (currPercent >= .9) {
            $textElmt
               .val($textElmt.val().substr(0, uiw.options.maxLength))
               .css('color', uiw.options.errorColor);
         } else if (currPercent >= .8) {
            $textElmt.css('color', uiw.options.warningColor);
         } else {
            $textElmt.css('color', 'black'),
         }
      });
   }
});
})(apex.jQuery);

At this point we have a fully functioning widget. We could add this code to the Function and Global Variable Declaration section of an APEX page and then instantiate the widget on a page item by adding the following code to the Execute when Page Loads section. Notice that default option values can be overridden during instantiation.

$('#P1_FIRST_NAME').lengthLimit({
   maxLength: 10
});

After a little testing, you may notice two issues. First, the main logic which sets the color and trims the text is not executed when the page loads and the widget is initialized. Also, that same logic is wrapped up in an event handler which was bound to the change event of the element. As a consequence, developers have no way to execute that code without manually triggering the change event on the item, which may be undesirable. With a little refactoring, we can solve both issues while keeping the code very maintainable.

(function($){
$.widget("ui.lengthLimit", {
   options: {
      warningColor: "orange",
      errorColor: "red",
      maxLength: 50
   },
   _create: function() {
      var uiw = this;

      this.element.change(function() {
         uiw.checkLength();
      });

      uiw.checkLength();
   },
   checkLength: function() {
      var uiw = this;
      var $textElmt = uiw.element;
      var currLength = $textElmt.val().length;
      var currPercent = currLength/uiw.options.maxLength;

      if (currPercent >= .9) {
         $textElmt
            .val($textElmt.val().substr(0, uiw.options.maxLength))
            .css('color', uiw.options.errorColor);
      } else if (currPercent >= .8) {
         $textElmt.css('color', uiw.options.warningColor);
      } else {
         $textElmt.css('color', 'black'),
      }
   }
});
})(apex.jQuery);

The majority of the logic has been moved to a new function named checkLength. This function is used twice in the _create function: once in the change event handler on the element and then later at the end of the function.

You may have noticed that the new checkLength function didn't have the same leading underscore that the _create function did. This is an important distinction. The underscore is used to create "private" functions within a widget. They can help modularize your code but cannot be invoked from outside the widget. Because the checkLength function name doesn't start with an underscore, it's registered as a public function and can be called at any time as follows:

$('#P1_FIRST_NAME').lengthLimit('checkLength'),

Also note the subtle yet important change in reference to this. With the majority of code in a widget, this will refer to the widget instance. But in the original _create function, an anonymous function was bound to the change event of the element. When that function executed, this referred to the element that changed. After the refactoring, the element property of the widget instance (available automatically as part of the factory) was needed to access the same element—the element on which the widget was instantiated. Additionally, a local variable is declared in each function to hold a reference to this. This technique is optional but can help to avoid confusion and to take advantage of closure in JavaScript.

This introduction to the jQuery UI Widget Factory is by no means complete, but you should now have a basic understanding of how it can be utilized to write well-structured JavaScript with minimal plumbing. To learn more about the Widget Factory, find the "official" documentation available on the jQuery and jQuery UI websites as well as the many tutorials found elsewhere on the Web.

APEX currently supplies over 20 themes out of the box and many developers and organizations have created custom themes as well. If your plug-in has a visual component to it, you'll need to take this into account as the plug-in should look as though it's a native part of the application. To achieve this you could develop your plug-in using neutral colors. Or you could attempt to support all of the existing APEX themes out there. But both of these approaches have some obvious drawbacks. A better approach would be to leverage the jQuery UI CSS Framework.

The following description, taken from the jQuery UI CSS Framework website, says it best:

Like APEX, jQuery UI ships with over 20 ThemeRoller themes ready to go and they are included with every APEX installation—you need only include the CSS file in your APEX page. A nice touch in any plug-in with a visual component is to allow the plug-in user to specify which theme they would like to use. The value, based on the theme name, can then be used to add the appropriate CSS file to the page. A select list can be used to show display values that better match the theme names seen on the jQuery UI website. The return value should be the all lowercase name of the theme with spaces replaced by dashes. Each directory is currently under images/libraries/jquery-ui/1.8/themes/.

This technique, while incredibly easy for plug-in users, has some potential downsides. For example, if multiple plug-ins exist on the same page, each using the same technique, it's possible that entirely different CSS files will be added to the page. This can both decrease performance and cause confusion as to which plug-in's theme is being used.

One way around these issues is to make the selection of theme optional. If no theme is selected then no CSS file should be added to the page by the plug-in. However, the HTML generated by the plug-in still includes the CSS classes, so all the plug-in user has to do is include the preferred CSS file in the optimal place once, say, in the page template.

The following HTML code was taken from the item plug-in tutorial found later in this chapter. The markup is used to display a warning message to end users. The code has been simplified a little so as not to distract from the important parts.

<div class="ui-state-highlight ui-corner-all">
   <table><tbody><tr><td>
         <span class="ui-icon ui-icon-alert"></span>
      </td><td>
         <p>Caps Lock is enabled.</p>
   </td></tr></tbody></table>
</div><

There are a total of four classes used from the framework:

If the content were displayed in a browser without a CSS file from ThemeRoller, the result would look like Figure 11-15.

Figure 11-15. The warning message without ThemeRoller CSS

When a CSS ThemeRoller file is added to the page the same content looks like Figure 11-16. The fact that this book is not printed in color prevents you from seeing the full effect, but the visible icon and rounded corners should be easy to notice.

Figure 11-16. The warning message with ThemeRoller CSS

APEX actually does add a ThemeRoller CSS file to pages as part of the standard CSS/JavaScript files that are added to every page. The link appears as follows:

<link type="text/css" href="/i/libraries/jquery-ui/1.8/themes/base/

jquery-ui-1.8.custom.min.css" rel="stylesheet">

The fact that the file name includes the word "custom" indicates that it doesn't include every CSS class used by the entire jQuery UI library. The default name for the file that contains all of the definitions is jquery-ui.css. Also, the file is located in a directory named base, which is the name of one of themes from the ThemeRoller gallery. Alongside that directory are 24 others which comprise the rest of the themes from the gallery. So if you wanted to add a completely different theme to the page—say, Cupertino—it is as simple as adding the following link after the first so that its rules overwrite any previous rules.

<link type="text/css" href="/i/libraries/jquery-ui/1.8/themes/cupertino/

jquery-ui.css" rel="stylesheet">

Like the introduction to the jQuery UI Widget Factory, this introduction to the jQuery UI CSS Framework is just that: an introduction. If you'd like to learn more I recommend you start by learning from the jQuery and jQuery UI websites. The time spent learning these technologies should easily pay you back time and time again when you develop plug-ins in APEX.

In its simplest form, geocoding can be defined as the process of finding the longitude and latitude coordinates of a location from its address. In this tutorial you will create a process plug-in that can be used for geocoding. The plug-in will serve as a wrapper for Yahoo's geocoding API which will do the heavy lifting.

Yahoo requires developers to specify a Yahoo Application ID when using their APIs. These Application IDs can be obtained for free after creating an account at http://developer.yahoo.com. Because the Application ID will differ for each plug-in user, a custom application level attribute will be added to the plug-in that allows plug-in users to specify their unique ID.

Three component level attributes will be added as well. One attribute will allow the plug-in user to specify the name of the item on the page that will contain the address to be geocoded. The other two items will be used to identify the items into which the latitude and longitude values will be returned when the geocoding operation has completed successfully.

The logic of the plug-in will be written in such a way that the longitude and latitude items will be cleared out if the address item is null, if multiple addresses were found based on the address supplied, or if the "quality" of the coordinates is less than 87 out of 100. To begin creating the plug-in, navigate to Shared Components Plug-ins and follow these steps, as shown in Figure 11-17.

Figure 11-17. Creating the Geocode plug-in

At this point the base of the plug-in has been created and all of the regions of the Create/Edit page should be visible. Before continuing with the plug-in, let's walk through the code entered in the execution function. First, the function header is defined. Note that the signature of the function matches what is expected for a process execution function.

FUNCTION geocode_execution (
   p_process IN APEX_PLUGIN.T_PROCESS,
   p_plugin  IN APEX_PLUGIN.T_PLUGIN
)

   RETURN APEX_PLUGIN.T_PROCESS_EXEC_RESULT

Next, local variables and an inline procedure are declared. Some of the local variables are simply used to provide better names for the attribute values that are passed into the function. The inline procedure will clear the values of the longitude and latitude items when appropriate.

IS

   l_retval           APEX_PLUGIN.T_PROCESS_EXEC_RESULT;
   l_yahoo_appid      VARCHAR2(500) := p_plugin.attribute_01;
   l_address_item     VARCHAR2(100) := p_process.attribute_01;
   l_lat_item         VARCHAR2(100) := p_process.attribute_02;
   l_long_item        VARCHAR2(100) := p_process.attribute_03;
   l_address_item_val VARCHAR2(32767);
   l_rest_result      XMLTYPE;
   l_parm_name_list   WWV_FLOW_GLOBAL.VC_ARR2;
   l_parm_value_list  WWV_FLOW_GLOBAL.VC_ARR2;
   l_error            VARCHAR2(32767);
   l_found_count      PLS_INTEGER;

   PROCEDURE clear_lat_long
   IS
   BEGIN
      apex_util.set_session_state(l_lat_item, ''),
      apex_util.set_session_state(l_long_item, ''),
   END;

The following lines begin the execution section of the function. If the application is running in debug mode then a call to APEX_PLUGIN_UTIL.DEBUG_PROCESS is made to log debug information.

BEGIN

   IF apex_application.g_debug
   THEN
      apex_plugin_util.debug_process(
         p_plugin  => p_plugin,
         p_process => p_process
      );
   END IF;

If the value of the address item is null then the latitude and longitude item values are cleared.

l_address_item_val := v(l_address_item);

IF l_address_item_val IS NULL
THEN
   clear_lat_long;

If the address item's session state value is not null, then processing continues. Parameters for the Yahoo API are set up. Because carriage returns can cause issues when making web service requests, they are stripped from the address value.

ELSE
   l_parm_name_list(1) := 'appid';
   l_parm_value_list(1) := l_yahoo_appid;
   l_parm_name_list(2) := 'flags';
   l_parm_value_list(2) := 'C'; --Only return coordinate data and match quality elements
   l_parm_name_list(3) := 'location';
   l_parm_value_list(3) := REPLACE(REPLACE(l_address_item_val, CHR(13)||CHR(10), ' '),

CHR(10), ' '),

The APEX_WEB_SERVICE.MAKE_REST_REQUEST function is used to make the call to Yahoo's API. Because the result of that function is a CLOB, XMLTYPE is used to convert the result to an XMLTYPE object.

l_rest_result := xmltype(
   apex_web_service.make_rest_request(
      p_url         => 'http://where.yahooapis.com/geocode',
      p_http_method => 'GET',
      p_parm_name   => l_parm_name_list,
      p_parm_value  => l_parm_value_list
   )
);

The resulting XML is first checked for errors. If no errors are found then the XML is checked for the number or results. If only one result was retrieved then it is checked for accuracy. If the accuracy of the result is equal to or above 87 out of 100 then the values of the latitude and longitude items are set from the result. If multiple results were retrieved or if the accuracy is too low then the values of the latitude and longitude items are cleared.

IF l_rest_result.extract('//Error/text()').getnumberval() = 0
THEN
   l_found_count := l_rest_result.extract('//Found/text()').getnumberval();

   IF l_found_count = 1
   THEN
      IF l_rest_result.extract('//Result/quality/text()').getstringval() >= 87 

 --Address match with street match (or better)
            THEN
               apex_util.set_session_state(l_lat_item, l_rest_result.extract 

('//Result/latitude/text()').getstringval());
               apex_util.set_session_state(l_long_item, l_rest_result.extract

('//Result/longitude/text()').getstringval());
            ELSE

clear_lat_long;
   END IF;
ELSE
   clear_lat_long;
END IF;

If an error is detected in the XML then RAISE_APPLICATION_ERROR is used to display the error message to the user.

ELSE
      l_error := l_rest_result.extract('//ErrorMessage/text()').getstringval();

      RAISE_APPLICATION_ERROR(−20001, 'Yahoo error: ' || l_error);
   END IF;
END IF;

Finally, the function's return value is returned and the function ends.

RETURN l_retval;

END geocode_execution;

The next step in creating the plug-in is to add the custom attributes. The first attribute will be for the Yahoo Application ID. This attribute will be an application level attribute so it only needs to be set once per application. If you're not already there, navigate to the Create/Edit page for the PlugGen Geocode plug-in. Complete the following steps as shown in Figure 11-18.

Figure 11-18. Adding the Yahoo Application ID attribute of the Geocode plug-in

The next attribute will be used to allow plug-in users to specify which page level item will contain the address. This will be a component-level attribute so that the plug-in can be used more than once in the application. Complete the following steps as shown in Figure 11-19.

Figure 11-19. Adding the Address Item attribute of the Geocode plug-in

The next attribute will be used to allow plug-in users to specify which page level item will store the latitude returned from the web service. Complete the following steps as shown in Figure 11-20.

Figure 11-20. Adding the Latitude Item attribute of the Geocode plug-in

The last attribute will be used to allow plug-in developers to specify which page level item will store the longitude returned from the web service. Complete the following steps as shown in Figure 11-21.

Figure 11-21. Adding the Longitude Item attribute of the Geocode plug-in

That completes the Geocode plug-in! You should now be able to set up a test page that uses the new process plug-in. Figure 11-22 shows an example test page. Don't forget to specify the Yahoo Application ID before testing. At this point you may consider modifying the code to better meet your needs.

Figure 11-22. Example usage of the Geocode plug-in

FUNCTION geocode_execution (
   p_process IN APEX_PLUGIN.T_PROCESS,
   p_plugin  IN APEX_PLUGIN.T_PLUGIN
)

   RETURN APEX_PLUGIN.T_PROCESS_EXEC_RESULT

IS

   l_retval           APEX_PLUGIN.T_PROCESS_EXEC_RESULT;
   l_yahoo_appid      VARCHAR2(500) := p_plugin.attribute_01;
   l_address_item     VARCHAR2(100) := p_process.attribute_01;
   l_lat_item         VARCHAR2(100) := p_process.attribute_02;
   l_long_item        VARCHAR2(100) := p_process.attribute_03;
   l_address_item_val VARCHAR2(32767);
   l_rest_result      XMLTYPE;
   l_parm_name_list   WWV_FLOW_GLOBAL.VC_ARR2;
   l_parm_value_list  WWV_FLOW_GLOBAL.VC_ARR2;
   l_error            VARCHAR2(32767);
   l_found_count      PLS_INTEGER;

   PROCEDURE clear_lat_long
   IS
   BEGIN
      apex_util.set_session_state(l_lat_item, ''),
      apex_util.set_session_state(l_long_item, ''),
   END;

BEGIN

   IF apex_application.g_debug
   THEN
      apex_plugin_util.debug_process(
         p_plugin  => p_plugin,
         p_process => p_process
      );
   END IF;

   l_address_item_val := v(l_address_item);

   IF l_address_item_val IS NULL
   THEN
      clear_lat_long;
   ELSE
      l_parm_name_list(1) := 'appid';
      l_parm_value_list(1) := l_yahoo_appid;
      l_parm_name_list(2) := 'flags';
      l_parm_value_list(2) := 'C'; --Only return coordinate data and match quality elements
      l_parm_name_list(3) := 'location';
      l_parm_value_list(3) := REPLACE(REPLACE(l_address_item_val, CHR(13)||CHR(10), ' '),

 CHR(10), ' '),

      l_rest_result := xmltype(
         apex_web_service.make_rest_request(
            p_url         => 'http://where.yahooapis.com/geocode',
            p_http_method => 'GET',
            p_parm_name   => l_parm_name_list,
            p_parm_value  => l_parm_value_list
         )

);

IF l_rest_result.extract('//Error/text()').getnumberval() = 0
THEN
   l_found_count := l_rest_result.extract('//Found/text()').getnumberval();

   IF l_found_count = 1
   THEN
      IF l_rest_result.extract('//Result/quality/text()').getstringval() >= 87

--Address match with street match (or better)
            THEN
               apex_util.set_session_state(l_lat_item,

 l_rest_result.extract('//Result/latitude/text()').getstringval());
               apex_util.set_session_state(l_long_item,

 l_rest_result.extract('//Result/longitude/text()').getstringval());
            ELSE
               clear_lat_long;
            END IF;
         ELSE
            clear_lat_long;
         END IF;
      ELSE
         l_error := l_rest_result.extract('//ErrorMessage/text()').getstringval();

         RAISE_APPLICATION_ERROR(−20001, 'Yahoo error: ' || l_error);
      END IF;
   END IF;

   RETURN l_retval;

END geocode_execution;

In this tutorial you will create a Dynamic Action plug-in that enhances the end-user experience while working with cascading select lists. APEX 4.0 introduced declarative support for cascading lists of values which greatly increased the likelihood they would be used when working with hierarchical data. Most of the time, hierarchical data will have a one-to-many relationship between parents and children, such as departments to employees (see Figure 11-23).

Figure 11-23. Standard one-to-many relationship

When developers need a user to select an employee with this model, they may decide to use two items rather than one to help simplify the selection process, as shown in Figure 11-24. This is known as a cascading list of values (LOV).

Figure 11-24. Example of one-to-many cascading select lists

The default functionality for cascading LOVs is such that when the parent value is changed, the child values are replaced with new values that reflect the new parent value. If a child value had been selected prior to the refresh then the selection would have been lost.

When the relationship is one to many, the default functionality for cascading LOVs works fine because any selected value will not exist in the new set of values. But sometimes the relationship can be many to many, for example, movies to actors (shown in Figure 11-25). With this type of relationship it's possible for a user to select different movies which have overlapping actors (think of the Indiana Jones series and Harrison Ford).

Figure 11-25. Many-to-many relationship

Even with such a change in the data model, the cascading select lists could appear the same as before, as in Figure 11-26.

Figure 11-26. Example of many-to-many cascading select lists

In this scenario developers may want to preserve child value selections if they existed in the new list of values when the parent changed. This would prevent the user from having to reselect the same value if that's what they intended to do. The default functionality for cascading LOVs does not support this but, with enough knowledge of how cascading LOVs work in APEX, the functionality can be added through a Dynamic Action plug-in.

The plug-in will be designed so that either the change event of a parent item or the load event of the page can be used as the driver of the dynamic action. Ultimately, the load event of the page will be used in either case.

To begin creating the plug-in, navigate to Shared Components Plug-ins and follow these steps as shown in Figure 11-27.

Figure 11-27. Creating the Save Value on Cascade plug-in

At this point the base of the plug-in has been created. Before continuing to build out the plug-in, let's do a walkthrough of the execution function. First, the function header is defined. Note that the signature of the function matches what is expected for a Dynamic Action render function.

FUNCTION save_value_render (
   p_dynamic_action IN APEX_PLUGIN.T_DYNAMIC_ACTION,
   p_plugin         IN APEX_PLUGIN.T_PLUGIN
)

   RETURN APEX_PLUGIN.T_DYNAMIC_ACTION_RENDER_RESULT

Next, local variables are declared. The majority of this plug-in's logic is in JavaScript. As a result, only the return variable needs to be declared.

IS

   l_result APEX_PLUGIN.T_DYNAMIC_ACTION_RENDER_RESULT;

The following lines begin the execution section of the function. If the application is running in debug mode then a call to APEX_PLUGIN_UTIL.DEBUG_DYNAMIC_ACTION is made to log debug information.

BEGIN

   IF apex_application.g_debug
   THEN
      apex_plugin_util.debug_dynamic_action(
         p_plugin         => p_plugin,
         p_dynamic_action => p_dynamic_action
      );
   END IF;

Much of the code for this plug-in will be stored in a JavaScript file which will be stored as part of the plug-in. The following lines of code will make that file available when the page loads.

apex_javascript.add_library(
   p_name      => 'com_pluggen_save_value_on_cascade',
   p_directory => p_plugin.file_prefix,
   p_version   => NULL
);

In addition to the JavaScript included above, a little bit of JavaScript needs to be added to the page and called when the page loads so that it can initialize the jQuery UI widget.

apex_javascript.add_onload_code(
   p_code => 'apex.jQuery(document).save_value_on_casdade();'
);

The following lines of code register an anonymous JavaScript function with framework. The function will be invoked automatically based on the driver configured in the dynamic action. The function checks to see if the event that triggered it was the load event of the page. If so, the initItem method of the widget is called and this is passed in as a parameter. In this context, this is a special object that will provide access to important attributes of the dynamic action in JavaScript.

l_result.javascript_function :=
   'function(){' ||
   '   if (this.browserEvent === ''load''){' ||
   '      apex.jQuery(document).save_value_on_casdade(''initItem'', this);' ||
   '   }' ||
   '}';

Finally the result variable is returned and the function ends.

RETURN l_result;

END save_value_render;

With the base of the plug-in created, the only other thing needed to complete the plug-in is to add the JavaScript file to it. You will first need to save the code from Listing 11-3 to a file named com_pluggen_save_value_on_cascade.js. Then, if you're not already there, navigate to the Create/Edit page for the Save Value on Cascade plug-in and complete the following steps as shown in Figure 11-28.

Click Upload New File.

Set File to the file you created: com_pluggen_save_value_on_cascade.js.

Click Upload.

Figure 11-28. Uploading the JavaScript file to the Save Value on Cascade plug-in

As was done with the render function, let's do a walkthrough of the code in the JavaScript file. The first lines of code both protect references to the $ object and start a jQuery UI widget named save_value_on_cascade. Although a UI widget was not truly needed in this case, this serves as a good way to introduce them and makes it easier to expand the plug-in in the future.

(function($){
$.widget("ui.save_value_on_casdade", {

The next couple lines create a function as part of the widget—also known as a method. The function will be called by the JavaScript in the PL/SQL code when the page loads. It is passed a parameter which is a reference to this from the Dynamic Action framework. In that context, this is a special object that provides access to various plug-in related attributes; in this case, the affectedElements attribute was needed to know which items the plug-in user wants to save the values of. In the beginning of the function, a local variable is declared to store a reference to this. In the context of a widget method, this represents the widget object. This is done to allow access to the object later via closure in JavaScript.

initItem: function(apexThis) {
   var uiw = this;

The each method is used to loop though the various affected elements. In each case, a local variable is declared to cache the selection of this which represents the current element in the loop. Again, this is done to allow for future references to the object via closure.

apexThis.affectedElements.each(function() {
   var $this = $(this);

The current value of the element is stored using the data method of jQuery. Note that $v is used to get the value. Afterward, an event handler is bound to the change event of the element that will update the saved value if the value changes. Finally, an event handler is bound to the custom apexafterrefresh event. This last event handler simply calls another method of the widget to restore the value and passes it a reference to the element that should be restored.

$this
        .data('value-saved', $v($this.attr('id')))
        .bind('change', function() {
           $this.data('value-saved', $v($this.attr('id')));
        })
        .bind('apexafterrefresh', function() {
           uiw._restoreValue($this);
        });
   });
},

The restoreValue function restores the value of the element passed in using the value last stored via the data method. Notice that $s is used to set the value of the element.

_restoreValue: function($affectedElement) {
      $s($affectedElement.attr('id'), $affectedElement.data('value-saved'));
   }
});
})(apex.jQuery);

At this point you should be able to set up a test page to see the dynamic action in action. As it stands, the plug-in would only work with cascading select lists. To make the plug-in more useful, consider modifying it to support other item types that use LOVs, such as check boxes and radio buttons.

FUNCTION save_value_render (
   p_dynamic_action IN APEX_PLUGIN.T_DYNAMIC_ACTION,
   p_plugin         IN APEX_PLUGIN.T_PLUGIN
)

   RETURN APEX_PLUGIN.T_DYNAMIC_ACTION_RENDER_RESULT

IS

l_result APEX_PLUGIN.T_DYNAMIC_ACTION_RENDER_RESULT;

BEGIN

   IF apex_application.g_debug
   THEN
      apex_plugin_util.debug_dynamic_action(
         p_plugin         => p_plugin,
         p_dynamic_action => p_dynamic_action
      );
   END IF;

   apex_javascript.add_library(
      p_name      => 'com_pluggen_save_value_on_cascade',
      p_directory => p_plugin.file_prefix,
      p_version   => NULL
   );

   apex_javascript.add_onload_code(
      p_code => 'apex.jQuery(document).save_value_on_casdade();'
   );

   l_result.javascript_function :=
      'function(){' ||
      '   if (this.browserEvent === ''load''){' ||
      '      apex.jQuery(document).save_value_on_casdade(''initItem'', this);' ||
      '   }' ||
      '}';

   RETURN l_result;

END save_value_render;

(function($){
$.widget("ui.save_value_on_casdade", {
   initItem: function(apexThis) {
      var uiw = this;

      apexThis.affectedElements.each(function() {
         var $this = $(this);

         $this
            .data('value-saved', $v($this.attr('id')))
            .bind('change', function() {
               $this.data('value-saved', $v($this.attr('id')));
            })
            .bind('apexafterrefresh', function() {
               uiw._restoreValue($this);
            });
      });

},
   _restoreValue: function($affectedElement) {
      $s($affectedElement.attr('id'), $affectedElement.data('value-saved'));
   }
});
})(apex.jQuery);

In this tutorial you will create a password item plug-in with Caps Lock detection. You may be familiar with Caps Lock detection from logging into your computer. If the password field obtains focus while the keyboard's Caps Lock is enabled, a message appears letting you know (see Figure 11-29). The message displayed is unobtrusive and you are not prevented from typing.

Figure 11-29. Caps Lock message for passwords fields in Windows

This is the same type of functionality that will be added to the plug-in. Unfortunately, it is not possible to directly detect whether or not Caps Lock is enabled from JavaScript. However, it is possible to detect if the Shift key is depressed while another key is pressed. Using that fact, you can deduce that if a character is entered in uppercase while the Shift key was not held down, then Caps Lock must be enabled.

The "shift key workaround" is the basis for nearly every Caps Lock detection algorithm written in JavaScript. The only drawback is that a user must type at least one character for the detection to work. The detection algorithm used in this tutorial was provided by Joe Liversedge via StackOverflow. It was chosen because it was based on jQuery and took into account some internationalization issues that plagued some of the other solutions.

With the Caps Lock detection algorithm in hand there's only one other major hurdle: displaying the message. Luckily, jQuery UI provides some excellent tools that make this very easy. This tutorial will use the Position utility to place the message in the correct location on the page. Additionally, the jQuery UI CSS Framework will be used to style the message.

The plug-in will need to include attributes that allow plug-in users to customize various parts of the message displayed, such as its text, colors, size, and position on the page. Having the ability to control the position on the page will allow plug-in users to prevent the warning message from covering other important items.

To begin creating the plug-in, navigate to the Shared Components Plug-ins and follow these steps as shown in Figure 11-30.

Figure 11-30. Creating the Password plug-in

At this point, the base of the plug-in has been created. Before continuing to build out the plug-in, let's walk through the code entered in the execution function. First the function header is defined. Note that the signature of the function matches what is expected for an item render function.

FUNCTION password_render (
   p_item                IN APEX_PLUGIN.T_PAGE_ITEM,
   p_plugin              IN APEX_PLUGIN.T_PLUGIN,
   p_value               IN VARCHAR2,
   p_is_readonly         IN BOOLEAN,
   p_is_printer_friendly IN BOOLEAN
)

   RETURN APEX_PLUGIN.T_PAGE_ITEM_RENDER_RESULT

Next, local variables are declared. Some of the local variables are used simply to provide better names for the attribute values that are passed into the function. Many of these values will simply be mapped through to the jQuery UI widget created later.

IS

   l_retval             APEX_PLUGIN.T_PAGE_ITEM_RENDER_RESULT;
   l_name               VARCHAR2(30);
   l_submit_on_enter    VARCHAR2(1) := NVL(p_item.attribute_01, 'Y'),
   l_message_icon       VARCHAR2(20) := NVL(p_item.attribute_02, 'ui-icon-alert'),
   l_message_text       VARCHAR2(500) := NVL(p_item.attribute_03, 'Caps Lock is enabled.'),
   l_message_width      PLS_INTEGER := NVL(p_item.attribute_04, 150);
   l_message_alignment  VARCHAR2(20) := NVL(p_item.attribute_05, 'center bottom'),
   l_password_alignment VARCHAR2(20) := NVL(p_item.attribute_06, 'center top'),
   l_offset             VARCHAR2(20) := NVL(p_item.attribute_07, '0'),
   l_jqueryui_theme     VARCHAR2(30) := p_plugin.attribute_01;
   l_onload_code        VARCHAR2(32767);
   l_crlf               CHAR(2) := CHR(13)||CHR(10);

The following lines begin the execution section of the function. If the application is running in debug mode then a call to APEX_PLUGIN_UTIL.DEBUG_PROCESS is made to log debug information.

BEGIN

   IF apex_application.g_debug
   THEN
      apex_plugin_util.debug_page_item (
         p_plugin              => p_plugin,
         p_page_item           => p_item
      );
   END IF;

Unlike most items, the value of a password item should not be displayed if the item is running as read only or the page is running in printer friendly mode.

IF p_is_readonly OR p_is_printer_friendly
THEN
   NULL;--Password should not be displayed

If the item is not running as read only and printer friendly mode is not on, then the main plug-in logic is executed. The first step is to include a JavaScript file that will later be stored as part of the plug-in.

Next, a local variable is checked to see if a user selected a jQuery UI theme. If so, the correct CSS file for the theme is added to the page.

ELSE
   apex_javascript.add_library(
      p_name      => 'com_pluggen_password',
      p_directory => p_plugin.file_prefix,
      p_version   => NULL
   );

   IF l_jqueryui_theme IS NOT NULL
   THEN
      apex_css.add_file(
         p_name      => 'jquery-ui',
         p_directory => apex_application.g_image_prefix || 'libraries/

jquery-ui/1.8/themes/' || l_jqueryui_theme || '/',
         p_version   => NULL
      );
   END IF;

A call to APEX_PLUGIN.GET_INPUT_NAME_FOR_PAGE_ITEM is used to obtain the value which should be assigned to the name attribute of the password element. This will ensure that the value of the item is saved in session state when the page is submitted. Then a call to SYS.HTP.P is used to add HTML output to the output buffer. Various attribute values set by the plug-in user are used to determine exactly what HTML is output.

l_name := apex_plugin.get_input_name_for_page_item(FALSE);

   sys.htp.p(
         '<input type="password" name="' || l_name || '" id="' || p_item.name
      || '" size="' || p_item.element_width || '" maxlength="' || p_item.element_max_length
      || '" ' || p_item.element_attributes || ' '
      || CASE
            WHEN l_submit_on_enter = 'Y'
            THEN 'onkeypress="return submitEnter(this,event)"'
         END
      || '/>'
  );

The following lines of code build up a string of JavaScript and then use APEX_JAVASCRIPT.ADD_ONLOAD_CODE to add the code to the page so that it will be executed when the DOM is ready. The JavaScript code initializes the password widget with the input element. APEX_JAVASCRIPT.ADD_ATTRIBUTE is used to add attributes to the options object of the widget.

l_onload_code := 'apex.jQuery("input#' || p_item.name || '").pg_password({' || l_crlf
         || '   ' || apex_javascript.add_attribute('warningMsgIcon', l_message_icon) || l_crlf
         || '   ' || apex_javascript.add_attribute('warningMsgText', l_message_text) || l_crlf
         || '   ' || apex_javascript.add_attribute('warningMsgWidth', l_message_width) ||

 l_crlf
         || '   ' || apex_javascript.add_attribute('warningMsgAlignment',

 l_message_alignment) || l_crlf

|| '   ' || apex_javascript.add_attribute('passwordAlignment',

 l_password_alignment) || l_crlf
         || '   ' || apex_javascript.add_attribute('offset', l_offset, TRUE, FALSE) || l_crlf
         || '});';

      apex_javascript.add_onload_code(
         p_code => l_onload_code
      );

Finally, the item is marked as navigable, the result variable is returned, and the function ends.

l_retval.is_navigable := TRUE;
   END IF;

   RETURN l_retval;

END password_render;

The next step is to add custom attributes to the plug-in. The first attribute will allow the plug-in user to select a jQuery UI theme. Although a text attribute is used to simplify this tutorial, a select list would be a better option to make theme selection more declarative. If you're not already there, navigate to the Create/Edit page for the Password plug-in and complete the following steps as shown in Figure 11-31.

Figure 11-31. Adding the Theme attribute of the Password plug-in

The next attribute will allow plug-in users to specify whether or not the password item should submit the page when Enter is pressed while it has focus. If set to yes, this setting will add a little JavaScript to the HTML output. Complete the following steps as shown in Figure 11-32.

Figure 11-32. Adding the Submit when Enter pressed attribute of the Password plug-in

The next attribute will allow plug-in users to select from one of three different icons which will be displayed on the left side of the warning message. The icons are just a few of those that are included as part of the jQuery UI CSS Framework. The selection here will simply add a CSS class to the HTML generated in the render function. Complete the following steps as shown in Figure 11-33.

Figure 11-33. Adding the Warning Message Icon attribute of the Password plug-in

The next attribute will allow plug-in users to set the actual text of the warning message. Complete the following steps as shown in Figure 11-34.

Figure 11-34. Adding the Warning Message Text attribute of the Password plug-in

The next attribute will allow plug-in users to set the width of the warning message that is displayed. Complete the following steps as shown in Figure 11-35.

Figure 11-35. Adding the Warning Message Width attribute of the Password plug-in

At this point you may need a break from creating attributes! This is probably a good time to create a test page to view the plug-in item "as is". If you do this, note that several parts of the plug-in will not be editable while the plug-in is being used in the application. When you're ready to continue adding attributes to the plug-in, navigate to the Create/Edit page for the plug-in and click the Add Attribute button to continue.

The next attribute is the first of two that will deal with alignment. This attribute will allow plug-in users to specify how the warning message should be aligned with the password element. The values specified in the LOV are all of the valid values that will work with the Position widget of jQuery UI. Complete the following steps as shown in Figure 11-36.

Figure 11-36. Adding the Warning Message Alignment attribute of the Password plug-in

The next attribute is the second of two that deal with alignment. This attribute will allow plug-in users to specify how the password element should be aligned with the warning message. Complete the following steps as shown in Figure 11-37.

Figure 11-37. Adding the Password Element Alignment attribute of the Password plug-in

The last attribute will allow plug-in users to specify any offset that should be used while positioning the message relative to the password element. One or two numbers can be specified (separated by a space) which represent the left and top offsets respectively in pixels. If one number is used it will apply to both the left and top offsets. Complete the following steps as shown in Figure 11-38.

Figure 11-38. Adding the Offset attribute of the Password plug-in

Now that all the attributes have been added the only remaining step is to include the JavaScript file that will contain the password widget. First, save the code from Listing 11-5 to a file named com_pluggen_password.js. Then, if you're not already there, navigate to the Create/Edit page for the Password plug-in and complete the following steps as shown in Figure 11-39.

Figure 11-39. Adding the JavaScript file for the Password plug-in

As was done with the render function, let's do a walkthrough of the code in the JavaScript file. The first few lines of code both protect references to the $ object and start the pg_password widget. The prefix pg_, based on the PlugGen company name, was added to the widget name to help ensure uniqueness and avoid conflicts with other widgets.

(function($){
$.widget("ui.pg_password", {

Next, the options object is defined for the widget. The options object, unlike other objects belonging to the widget, is unique to each instance of the widget. The options object is typically used to initialize variables that affect how the widget works. In the case of this plug-in, that logic is maintained in the PL/SQL code, so the options object serves more of a documentation role.

options: {
   warningMsgIcon: null,
   warningMsgText: null,
   warningMsgWidth: null,
   warningMsgAlignment: null,
   passwordAlignment: null,
   offset: null
},

The create function will be invoked automatically one time when the widget is first initialized. In the beginning of the function, a local variable is declared to store a reference to this. In the context of a widget method, this represents the widget object. This is done to allow access to the object later via closure in JavaScript.

_create: function() {
   var uiw = this;

Here the element property of the widget object is used to set up some event handlers. The element property is a jQuery object based on the element on which the widget was invoked—the password element in this case. The main event handler works on keypress and implements the logic to detect whether or not Caps Lock is enabled. Another handler works with the blur event of the element. Both handlers make calls to other private methods of the widget to hide and show the warning message when appropriate.

uiw.element
   .keypress(function(jQevent) {
     //Code based on Joe Liversedge's submission on http://stackoverflow.com
     var character = String.fromCharCode(jQevent.which);

     if (character.toUpperCase() === character.toLowerCase()) {
        return;
     }

     // SHIFT doesn't usually give us a lowercase character. Check for this
     // and for when we get a lowercase character when SHIFT is enabled.
     if (
        (jQevent.shiftKey && character.toLowerCase() === character) ||
        (!jQevent.shiftKey && character.toUpperCase() === character)
        ) {

uiw._showMessage();
            } else {
               uiw._hideMessage();
            }
         })
         .blur(function() {
            uiw._hideMessage();
         });

The _showMessage method contains the logic needed to place the warning message on the page. First a check is made to see if the warning message is already on the page. If not, the HTML to show the message is constructed and injected in the DOM.

_showMessage: function() {
      var uiw = this;
      var html;
      var warningId = uiw.element.attr('id') + '_CL_WARNING';

      if (!$('#' + warningId).length) {
         html =
            '<div class="ui-state-highlight ui-corner-all" style="width: ' +

 uiw.options.warningMsgWidth +
            'px; padding: 0pt 0.7em;" id="' + warningId + '"><table><tr><td>
' +
            '   <span class="ui-icon ' + uiw.options.warningMsgIcon +
            '" style="float: left; margin-right:0.3em;"></span></td><td>' +
            '   <p>' + uiw.options.warningMsgText +
            '</p></td></tr></table></div>';

         $('body').append(html);

Immediately after adding the warning message to the DOM, the position widget of jQuery UI is used to move it to the correct location in the page.

$('#' + warningId).position({
         of: uiw.element,
         my: uiw.options.warningMsgAlignment,
         at: uiw.options.passwordAlignment,
         offset: uiw.options.offset,
         collision: 'none'
     });
  }
},

The _hideMessage method simply removes the warning message from the DOM when called.

_hideMessage: function() {
   var uiw = this;
   var warningId = uiw.element.attr('id') + '_CL_WARNING';

   $('#' + warningId).remove();
}

});
})(apex.jQuery);

At this point you should be ready to test the plug-in. Try adjusting the various settings, including the alignment and offset settings, to see how they affect the plug-in at run time. Consider leveraging the Position widget of jQuery UI in the future if you're building a plug-in that requires similar functionality.

FUNCTION password_render (
   p_item                IN APEX_PLUGIN.T_PAGE_ITEM,
   p_plugin              IN APEX_PLUGIN.T_PLUGIN,
   p_value               IN VARCHAR2,
   p_is_readonly         IN BOOLEAN,
   p_is_printer_friendly IN BOOLEAN
)

   RETURN APEX_PLUGIN.T_PAGE_ITEM_RENDER_RESULT

IS

   l_retval             APEX_PLUGIN.T_PAGE_ITEM_RENDER_RESULT;
   l_name               VARCHAR2(30);
   l_submit_on_enter    VARCHAR2(1) := NVL(p_item.attribute_01, 'Y'),
   l_message_icon       VARCHAR2(20) := NVL(p_item.attribute_02, 'ui-icon-alert'),
   l_message_text       VARCHAR2(500) := NVL(p_item.attribute_03, 'Caps Lock is enabled.'),
   l_message_width      PLS_INTEGER := NVL(p_item.attribute_04, 150);
   l_message_alignment  VARCHAR2(20) := NVL(p_item.attribute_05, 'center bottom'),
   l_password_alignment VARCHAR2(20) := NVL(p_item.attribute_06, 'center top'),
   l_offset             VARCHAR2(20) := NVL(p_item.attribute_07, '0'),
   l_jqueryui_theme     VARCHAR2(30) := p_plugin.attribute_01;
   l_onload_code        VARCHAR2(32767);
   l_crlf               CHAR(2) := CHR(13)||CHR(10);

BEGIN

   IF apex_application.g_debug
   THEN
      apex_plugin_util.debug_page_item (
         p_plugin              => p_plugin,
         p_page_item           => p_item
      );
   END IF;

   IF p_is_readonly OR p_is_printer_friendly
   THEN
      NULL;--Password should not be displayed
   ELSE
      apex_javascript.add_library(
         p_name      => 'com_pluggen_password',
         p_directory => p_plugin.file_prefix,
         p_version   => NULL

);

      IF l_jqueryui_theme IS NOT NULL
      THEN
         apex_css.add_file(
            p_name      => 'jquery-ui',
            p_directory => apex_application.g_image_prefix || 'libraries/

jquery-ui/1.8/themes/' || l_jqueryui_theme || '/',
            p_version   => NULL
         );
      END IF;

      l_name := apex_plugin.get_input_name_for_page_item(FALSE);

      sys.htp.p(
            '<input type="password" name="' || l_name || '" id="' || p_item.name
         || '" size="' || p_item.element_width || '" maxlength="' || p_item.element_max_length
         || '" ' || p_item.element_attributes || ' '
         || CASE
               WHEN l_submit_on_enter = 'Y'
               THEN 'onkeypress="return submitEnter(this,event)"'
            END
         || '/>'
      );

      l_onload_code := 'apex.jQuery("input#' || p_item.name || '").pg_password({' || l_crlf
         || '   ' || apex_javascript.add_attribute('warningMsgIcon', l_message_icon) || l_crlf
         || '   ' || apex_javascript.add_attribute('warningMsgText', l_message_text) || l_crlf
         || '   ' || apex_javascript.add_attribute('warningMsgWidth', l_message_width) ||

 l_crlf
         || '   ' || apex_javascript.add_attribute('warningMsgAlignment',
 l_message_alignment) || l_crlf
         || '   ' || apex_javascript.add_attribute('passwordAlignment',
 l_password_alignment) || l_crlf
         || '   ' || apex_javascript.add_attribute('offset', l_offset, TRUE, FALSE) || l_crlf
         || '});';

      apex_javascript.add_onload_code(
         p_code => l_onload_code
      );

      l_retval.is_navigable := TRUE;
   END IF;

   RETURN l_retval;

END password_render;

(function($){
$.widget("ui.pg_password", {
   options: {
      warningMsgIcon: null,
      warningMsgText: null,
      warningMsgWidth: null,
      warningMsgAlignment: null,
      passwordAlignment: null,
      offset: null
   },
   _create: function() {
      var uiw = this;

      uiw.element
         .keypress(function(jQevent) {
            //Code based on Joe Liversedge's submission on http://stackoverflow.com
            var character = String.fromCharCode(jQevent.which);

            if (character.toUpperCase() === character.toLowerCase()) {
               return;
            }

            // SHIFT doesn't usually give us a lowercase character. Check for this
            // and for when we get a lowercase character when SHIFT is enabled.
            if (
               (jQevent.shiftKey && character.toLowerCase() === character) ||
               (!jQevent.shiftKey && character.toUpperCase() === character)
            ) {
               uiw._showMessage();
            } else {
               uiw._hideMessage();
            }
         })
         .blur(function() {
            uiw._hideMessage();
         });
   },
   _showMessage: function() {
      var uiw = this;
      var html;
      var warningId = uiw.element.attr('id') + '_CL_WARNING';

      if (!$('#' + warningId).length) {
         html =
            '<div class="ui-state-highlight ui-corner-all" style="width: ' +

 uiw.options.warningMsgWidth +
            'px; padding: 0pt 0.7em;" id="' + warningId + '"><table><tr><td>
' +
            '   <span class="ui-icon ' + uiw.options.warningMsgIcon +
            '" style="float: left; margin-right:0.3em;"></span></td><td>' +

'   <p>' + uiw.options.warningMsgText +
            '</p></td></tr></table></div>';

         $('body').append(html);

         $('#' + warningId).position({
            of: uiw.element,
            my: uiw.options.warningMsgAlignment,
            at: uiw.options.passwordAlignment,
            offset: uiw.options.offset,
            collision: 'none'
         });
      }
   },
   _hideMessage: function() {
      var uiw = this;
      var warningId = uiw.element.attr('id') + '_CL_WARNING';

      $('#' + warningId).remove();
   }
});
})(apex.jQuery);

In this tutorial you will create a Calendar region plug-in. The native Calendar region in APEX is great, but it leaves a little to be desired. A full-fledged JavaScript calendar would be attractive, but creating one could be a full-time job. Luckily, Adam Shaw created FullCalendar, a jQuery plug-in that provides a full-sized calendar with a lot of interactive functionality. Even better is the fact that FullCalendar is open source and dual licensed under MIT and GPL Version 2 licenses.

This tutorial will demonstrate how third-party solutions can be integrated with APEX as a plug-in. Third-party code can drastically reduce the amount of time needed to develop a plug-in as only enough code to complete the integration is required. Once complete, these plug-ins can easily be reused in many APEX applications.

To take advantage of FullCalendar, you will first need to download the latest FullCalendar package, which can be found at http://arshaw.com/fullcalendar/download/. (Exactly which files from the package will be used in the plug-in will be discussed later.) Exploring the FullCalendar documentation will help you understand why the APEX plug-in was developed the way that it was. It may also provide you with insight into how the APEX plug-in could be enhanced to better suit your own needs.

To begin creating the plug-in, navigate to Shared Components Plug-ins and follow these steps as shown in Figure 11-40.

Figure 11-40. Creating the Calendar plug-in

At this point the base of the plug-in has been created. The PL/SQL source contained code for both the render and Ajax functions. Let's do a code walkthrough before continuing with the plug-in. The first few lines start the render function. Note that the signature of the render function matches what is expected for a region render function.

FUNCTION calendar_render (
   p_region              IN APEX_PLUGIN.T_REGION,
   p_plugin              IN APEX_PLUGIN.T_PLUGIN,
   p_is_printer_friendly IN BOOLEAN
)

   RETURN APEX_PLUGIN.T_REGION_RENDER_RESULT

Next, local variables are declared. One of the local variables is used simply to provide a better name for the attribute value that is passed into the function. This value will be mapped through to the jQuery UI widget which will be created later.

IS

   l_retval         APEX_PLUGIN.T_REGION_RENDER_RESULT;
   l_onload_code    VARCHAR2(4000);
   l_jqueryui_theme VARCHAR2(30) := p_plugin.attribute_01;
   l_crlf           CHAR(2) := CHR(13)||CHR(10);

The following lines begin the execution section of the function. If the application is running in debug mode then a call to APEX_PLUGIN_UTIL.DEBUG_REGION is made to log debug information.

BEGIN

   IF apex_application.g_debug
   THEN
      apex_plugin_util.debug_region (
         p_plugin => p_plugin,
         p_region => p_region
      );
   END IF;

A call to SYS.HTP.P is used to add HTML output to the output buffer. The output in this case is very basic, just enough to give FullCalendar something to work with.

sys.htp.p(
   '<div id="' || p_region.static_id || '_FULL_CALENDAR"></div>'
);

Next, a number of JavaScript and CSS files are added to the page—these files will later be stored as part of the plug-in. A local variable is checked to see if a user selected a jQuery UI theme. If so, the correct CSS file for the theme is added to the page.

apex_javascript.add_library(
   p_name      => 'com_pluggen_calendar',
   p_directory => p_plugin.file_prefix,

p_version   => NULL
   );

   apex_javascript.add_library (
      p_name      => 'jquery.ui.button.min',
      p_directory => apex_application.g_image_prefix || 'libraries/

jquery-ui/1.8/ui/minified/',
      p_version   => NULL
   );

   apex_javascript.add_library (
      p_name      => 'fullcalendar.min',
      p_directory => p_plugin.file_prefix,
      p_version   => NULL
   );

   IF l_jqueryui_theme IS NOT NULL
   THEN
      apex_css.add_file(
         p_name      => 'jquery-ui',
         p_directory => apex_application.g_image_prefix || 'libraries/

jquery-ui/1.8/themes/' || l_jqueryui_theme || '/',
         p_version   => NULL
      );
   END IF;

   apex_css.add_file(
      p_name      => 'fullcalendar',
      p_directory => p_plugin.file_prefix,
      p_version   => NULL
   );

The following lines of code build up a string of JavaScript and then use APEX_JAVASCRIPT.ADD_ONLOAD_CODE to add the code to the page so that it will be executed when the DOM is ready. The JavaScript code initializes the calendar widget with the input element. APEX_JAVASCRIPT.ADD_ATTRIBUTE is used to add attributes to the options object of the widget.

l_onload_code := 'apex.jQuery("div#' || p_region.static_id || '").calendar({' || l_crlf
      || '   ' || apex_javascript.add_attribute('theme', l_jqueryui_theme, TRUE, TRUE) ||

 l_crlf
      || '   ' || apex_javascript.add_attribute('ajaxIdentifier',

 apex_plugin.get_ajax_identifier(), FALSE, FALSE) || l_crlf
      || '});';

   apex_javascript.add_onload_code (
      p_code => l_onload_code
   );

   RETURN l_retval;

END calendar_render;

With the render function complete, the Ajax function can be added. Note that the signature of the render function matches what is expected for a region Ajax function.

FUNCTION calendar_ajax (
   p_region IN APEX_PLUGIN.T_REGION,
   p_plugin IN APEX_PLUGIN.T_PLUGIN
)

   RETURN APEX_PLUGIN.T_REGION_AJAX_RESULT

Local variables are then declared. Some of the local variables are used simply to provide a better name for the attribute values that are passed into the function.

IS

   l_retval            APEX_PLUGIN.T_REGION_AJAX_RESULT;
   l_column_value_list APEX_PLUGIN_UTIL.T_COLUMN_VALUE_LIST;
   l_start_date_item   VARCHAR2(32767) := p_region.attribute_01;
   l_end_date_item     VARCHAR2(32767) := p_region.attribute_02;
   l_window_start      DATE;
   l_window_end        DATE;
   l_id                PLS_INTEGER;
   l_title             VARCHAR2(32767);
   l_all_day           BOOLEAN;
   l_start             VARCHAR2(50);
   l_end               VARCHAR2(50);
   l_url               VARCHAR2(32767);
   l_class_name        VARCHAR2(100);

In the first part of the execution section, the values of the start and end date items are checked. If the user supplied values for these then their session state values are set from g_x01 and g_x02. The values for the global variables come from the FullCalendar API and are passed through via the Ajax request.

BEGIN

   IF l_start_date_item IS NOT NULL
   THEN
      l_window_start := TO_DATE(apex_application.g_x01, 'YYYYMMDD'),
      apex_util.set_session_state(l_start_date_item, l_window_start);
   END IF;

   IF l_end_date_item IS NOT NULL
   THEN
      l_window_end := TO_DATE(apex_application.g_x02, 'YYYYMMDD'),
      apex_util.set_session_state(l_end_date_item, l_window_end);
   END IF;

APEX_PLUGIN_UTIL.GET_DATA is used to execute the query that was supplied by the plug-in user as the source for the region. The result set is returned to a local variable which will be processed later.

l_column_value_list := apex_plugin_util.get_data(
   p_sql_statement  => p_region.source,

p_min_columns    => 7,
      p_max_columns    => 7,
      p_component_name => p_region.name
   );

Because the Ajax request will be returning JSON, the PRINT_JSON_HTTP_HEADER procedure of the APEX_PLUGIN_UTIL package is used to output the correct HTTP header. Afterward, the start of a JSON object is output and a loop to build up the contents of the object is started. Data returned from the query is escaped via SYS.HTF.ESCAPE_SC and then added to the object using APEX_JAVASCRIPT.ADD_ATTRIBUTE.

apex_plugin_util.print_json_http_header;

   sys.htp.p('['),

   FOR x IN 1 .. l_column_value_list(1).count
   LOOP
      l_id := sys.htf.escape_sc(l_column_value_list(1)(x));
      l_title := sys.htf.escape_sc(l_column_value_list(2)(x));
      l_all_day :=
         CASE
            WHEN UPPER(sys.htf.escape_sc(l_column_value_list(3)(x))) = 'TRUE'
            THEN TRUE
            ELSE FALSE
         END;
      l_start := sys.htf.escape_sc(l_column_value_list(4)(x));
      l_end := sys.htf.escape_sc(l_column_value_list(5)(x));
      l_url := sys.htf.escape_sc(l_column_value_list(6)(x));
      l_class_name := sys.htf.escape_sc(l_column_value_list(7)(x));

      sys.htp.p(
            CASE
               WHEN x > 1 THEN ','
            END
         || '{'
         || apex_javascript.add_attribute('id', l_id, TRUE, TRUE)
         || apex_javascript.add_attribute('allDay', l_all_day, TRUE, TRUE)
         || apex_javascript.add_attribute('end', l_end, TRUE, TRUE)
         || apex_javascript.add_attribute('url', l_url, TRUE, TRUE)
         || apex_javascript.add_attribute('className', l_class_name, TRUE, TRUE)
         || apex_javascript.add_attribute('title', l_title, FALSE, TRUE)
         || apex_javascript.add_attribute('start', l_start, FALSE, FALSE)
         || '}'
      );
   END LOOP;

   sys.htp.p(']'),

   RETURN l_retval;

END calendar_ajax;

The next step is to add custom attributes to the plug-in. The first attribute will allow the plug-in user to select a jQuery UI theme. Although a text attribute is used to simplify this tutorial, a select list would be a better option to make theme selection more declarative. If you're not already there, navigate to the Create/Edit page for the Calendar plug-in and complete the following steps as shown in Figure 11-41.

Figure 11-41. Adding the Theme attribute for the Calendar plug-in

The next two attributes are used to specify item names that can be used to filter the dates returned by the source query. Specifying item values is optional. Complete the following steps as shown in Figures 11-42 and 11-43.

Figure 11-42. Adding the Start Date Item attribute for the Calendar plug-in

Figure 11-43. Adding the End Date Item attribute for the Calendar plug-in

With the attributes added, the only remaining steps are to include the JavaScript and CSS files that will contain the calendar widget and FullCalendar code. First, save the code from Listing 11-7 to a file named com_pluggen_calendar.js. Then, if you're not already there, navigate to the Create/Edit page for the Calendar plug-in and complete the following steps as shown in Figure 11-44.

Figure 11-44. Uploading the the custom JavaScript for the Calendar plug-in

The next file contains the JavaScript for the FullCalendar plug-in. Complete the following steps to add the file to the plug-in, as shown in Figure 11-45.

Figure 11-45. Uploading the FullCalendar JavaScript for the Calendar plug-in

The last file contains the CSS for the FullCalendar plug-in. Complete the following steps to add the file to the plug-in, as shown in Figure 11-46.

Figure 11-46. Uploading the FullCalendar CSS for the Calendar plug-in

As was done for the PL/SQL code, let's do a walkthrough of the JavaScript code to better understand how it works. The first lines of code both protect references to the $ object and start the calendar.

(function($){
$.widget("ui.calendar", {

The options object is defined next, more to serve as a reminder of which properties exist than to set any defaults. Default value logic is maintained in the attribute defaults as well as the PL/SQL code.

options: {
   theme: null,
   ajaxIdentifier: null
},

The create function will be invoked automatically one time when the widget is first initialized. In the beginning of the function, a local variable is declared to store a reference to this. In the context of a widget method, this represents the widget object. This is done to allow access to the object later via closure in JavaScript. FullCalendar, which does all the heavy lifting in this plug-in, is initialized at this time.

_create: function() {
      var uiw = this;

      $('#' + uiw.element.attr('id') + '_FULL_CALENDAR').fullCalendar({
         editable: false,
         theme: uiw.options.theme !== null,
         events: function(start, end, callback){uiw.getDates(start, end, callback)},
         ignoreTimeZone: true,
         weekends: true,
         header: {
            left:   'today prev,next',
            center: 'title',
            right:  'agendaDay agendaWeek month'
         },
         viewDisplay: function(view) {
            uiw.element.trigger('calendarviewdisplay'),
         }
      });

To make sure that the plug-in is compatible with the standard APEX framework events, a function is bound to the apexrefresh event that will refresh events displayed in the calendar.

uiw.element.bind('apexrefresh', function() {
         $('#' + uiw.element.attr('id') + '_FULL_CALENDAR').fullCalendar('refetchEvents'),
      });
   },

The getDates method is responsible for making the Ajax request to bring new dates to the calendar when requested. Notice that the Ajax function is called by referencing the ajaxIdentifier value that was passed through from the PL/SQL code. Also, apexbeforerefresh and apexafterrefresh are triggered at the appropriate times to allow dynamic actions to work with them.

getDates: function(start, end, callback) {
   var uiw = this;

   uiw.element.trigger('apexbeforerefresh'),
   $.ajax({

type: 'POST',
         url: 'wwv_flow.show',
         data: {
            p_flow_id: $('#pFlowId').val(),
            p_flow_step_id: $('#pFlowStepId').val(),
            p_instance: $('#pInstance').val(),
            p_request: 'PLUGIN=' + uiw.options.ajaxIdentifier,
            x01: $.fullCalendar.formatDate(start, 'yyyyMMdd'),
            x02: $.fullCalendar.formatDate(end, 'yyyyMMdd')
         },
         dateType: 'json',
         async: false,
         success: function(data) {
            callback(data);
            uiw.element.trigger('apexafterrefresh'),
         }
      });
   }
});
})(apex.jQuery);

At this point you should be ready to test the plug-in. For the source of the region, enter a query like the following:

SELECT id AS id,
   title AS title,
   'TRUE' AS all_day,
   TO_CHAR(start_date, 'YYYY-MM-DD"T"HH24:MI:SS') AS start_date,
   TO_CHAR(end_date, 'YYYY-MM-DD"T"HH24:MI:SS') AS end_date,
   NULL AS url,
   NULL AS class_name
FROM events

Because FullCalendar has so many options, this is a great plug-in to customize to better meet your needs.

FUNCTION calendar_render (
   p_region              IN APEX_PLUGIN.T_REGION,
   p_plugin              IN APEX_PLUGIN.T_PLUGIN,
   p_is_printer_friendly IN BOOLEAN
)

   RETURN APEX_PLUGIN.T_REGION_RENDER_RESULT

IS

   l_retval         APEX_PLUGIN.T_REGION_RENDER_RESULT;
   l_onload_code    VARCHAR2(4000);
   l_jqueryui_theme VARCHAR2(30) := p_plugin.attribute_01;
   l_crlf           CHAR(2) := CHR(13)||CHR(10);

BEGIN

   IF apex_application.g_debug
   THEN
      apex_plugin_util.debug_region (
         p_plugin => p_plugin,
         p_region => p_region
      );
   END IF;

   sys.htp.p(
      '<div id="' || p_region.static_id || '_FULL_CALENDAR"></div>'
   );

   apex_javascript.add_library(
      p_name      => 'com_pluggen_calendar',
      p_directory => p_plugin.file_prefix,
      p_version   => NULL
   );

   apex_javascript.add_library (
      p_name      => 'jquery.ui.button.min',
      p_directory => apex_application.g_image_prefix || 'libraries/

jquery-ui/1.8/ui/minified/',
      p_version   => NULL
   );

   apex_javascript.add_library (
      p_name      => 'fullcalendar.min',
      p_directory => p_plugin.file_prefix,
      p_version   => NULL
   );

   IF l_jqueryui_theme IS NOT NULL
   THEN
      apex_css.add_file(
         p_name      => 'jquery-ui',
         p_directory => apex_application.g_image_prefix || 'libraries/

jquery-ui/1.8/themes/' || l_jqueryui_theme || '/',
         p_version   => NULL
      );
   END IF;

   apex_css.add_file(
      p_name      => 'fullcalendar',
      p_directory => p_plugin.file_prefix,
      p_version   => NULL
   );

   l_onload_code := 'apex.jQuery("div#' || p_region.static_id || '").calendar({' || l_crlf
      || '   ' || apex_javascript.add_attribute('theme', l_jqueryui_theme, TRUE, TRUE) ||

l_crlf
      || '   ' || apex_javascript.add_attribute('ajaxIdentifier',

 apex_plugin.get_ajax_identifier(), FALSE, FALSE) || l_crlf
      || '});';

   apex_javascript.add_onload_code (
      p_code => l_onload_code
   );

   RETURN l_retval;

END calendar_render;

FUNCTION calendar_ajax (
   p_region IN APEX_PLUGIN.T_REGION,
   p_plugin IN APEX_PLUGIN.T_PLUGIN
)

   RETURN APEX_PLUGIN.T_REGION_AJAX_RESULT
IS

   l_retval            APEX_PLUGIN.T_REGION_AJAX_RESULT;
   l_column_value_list APEX_PLUGIN_UTIL.T_COLUMN_VALUE_LIST;
   l_start_date_item   VARCHAR2(32767) := p_region.attribute_01;
   l_end_date_item     VARCHAR2(32767) := p_region.attribute_02;
   l_window_start      DATE;
   l_window_end        DATE;
   l_id                PLS_INTEGER;
   l_title             VARCHAR2(32767);
   l_all_day           BOOLEAN;
   l_start             VARCHAR2(50);
   l_end               VARCHAR2(50);
   l_url               VARCHAR2(32767);
   l_class_name        VARCHAR2(100);

BEGIN

   IF l_start_date_item IS NOT NULL
   THEN
      l_window_start := TO_DATE(apex_application.g_x01, 'YYYYMMDD'),
      apex_util.set_session_state(l_start_date_item, l_window_start);
   END IF;

   IF l_end_date_item IS NOT NULL
   THEN
      l_window_end := TO_DATE(apex_application.g_x02, 'YYYYMMDD'),
      apex_util.set_session_state(l_end_date_item, l_window_end);
   END IF;

   l_column_value_list := apex_plugin_util.get_data(
      p_sql_statement  => p_region.source,

p_min_columns    => 7,
      p_max_columns    => 7,
      p_component_name => p_region.name
   );

   apex_plugin_util.print_json_http_header;

   sys.htp.p('['),

   FOR x IN 1 .. l_column_value_list(1).count
   LOOP
      l_id := sys.htf.escape_sc(l_column_value_list(1)(x));
      l_title := sys.htf.escape_sc(l_column_value_list(2)(x));
      l_all_day :=
         CASE
            WHEN UPPER(sys.htf.escape_sc(l_column_value_list(3)(x))) = 'TRUE'
            THEN TRUE
            ELSE FALSE
         END;
      l_start := sys.htf.escape_sc(l_column_value_list(4)(x));
      l_end := sys.htf.escape_sc(l_column_value_list(5)(x));
      l_url := sys.htf.escape_sc(l_column_value_list(6)(x));
      l_class_name := sys.htf.escape_sc(l_column_value_list(7)(x));

      sys.htp.p(
            CASE
               WHEN x > 1 THEN ','
            END
         || '{'
         || apex_javascript.add_attribute('id', l_id, TRUE, TRUE)
         || apex_javascript.add_attribute('allDay', l_all_day, TRUE, TRUE)
         || apex_javascript.add_attribute('end', l_end, TRUE, TRUE)
         || apex_javascript.add_attribute('url', l_url, TRUE, TRUE)
         || apex_javascript.add_attribute('className', l_class_name, TRUE, TRUE)
         || apex_javascript.add_attribute('title', l_title, FALSE, TRUE)
         || apex_javascript.add_attribute('start', l_start, FALSE, FALSE)
         || '}'
      );
   END LOOP;

   sys.htp.p(']'),

   RETURN l_retval;
END calendar_ajax;

(function($){
$.widget("ui.calendar", {
   options: {
      theme: null,

ajaxIdentifier: null
   },
   _create: function() {
      var uiw = this;

      $('#' + uiw.element.attr('id') + '_FULL_CALENDAR').fullCalendar({
         editable: false,
         theme: uiw.options.theme !== null,
         events: function(start, end, callback){uiw.getDates(start, end, callback)},
         ignoreTimeZone: true,
         weekends: true,
         header: {
            left:   'today prev,next',
            center: 'title',
            right:  'agendaDay agendaWeek month'

         },
         viewDisplay: function(view) {
            uiw.element.trigger('calendarviewdisplay'),
         }
      });

      uiw.element.bind('apexrefresh', function() {
         $('#' + uiw.element.attr('id') + '_FULL_CALENDAR').fullCalendar('refetchEvents'),
      });
   },
   getDates: function(start, end, callback) {
      var uiw = this;

      uiw.element.trigger('apexbeforerefresh'),

      $.ajax({
         type: 'POST',
         url: 'wwv_flow.show',
         data: {
            p_flow_id: $('#pFlowId').val(),
            p_flow_step_id: $('#pFlowStepId').val(),
            p_instance: $('#pInstance').val(),
            p_request: 'PLUGIN=' + uiw.options.ajaxIdentifier,
            x01: $.fullCalendar.formatDate(start, 'yyyyMMdd'),
            x02: $.fullCalendar.formatDate(end, 'yyyyMMdd')
         },
         dateType: 'json',
         async: false,
         success: function(data) {
            callback(data);
            uiw.element.trigger('apexafterrefresh'),
         }
      });
   }
});
})(apex.jQuery);