SHUNT'S Blog

Set Cursor Focus in Tabular Forms

2011-11-15T17:55:00.001Z

In tabular forms I like to add a javascript function that allows the user to navigate vertically using the up and down arrows on the keyboard (see previous posts), which is now out of the box in Apex 4. A nice little add-on to this is to automatically select the contents of the cell when the cursor enters it. This can be easily achieved by adding the following to the Execute when Page Loads region of the page details.

$("input[type=text]").focus(function () {

this.select();

});

Read all IDs from an Interactive Report

2011-11-12T21:55:00.004Z

So you like the functionality that interactive reports give you and would like to integrate it with a third party application such as Google Maps or an embedded java applet. The challenge is to read the currently filtered and sorted report rows and pass them into the third party application; a pretty straight forward task with JQuery, but how do you cope with the pagination?

This is a nice trick; hide the report, display all the rows, read them and return the report to the original state. Like this:

Demo

Here's how I did it.

Add a hidden item to a column in the report and uncheck the hide option in the iR settings so that it cannot be removed from the report. e.g.

apex_item.hidden(1,empno)||empno empno

Set Function and Global Variable Declaration:

function f_get_ids() {
// read the start pagination
var l_start_pag = $('#apexir_ROWS_PER_PAGE_MENU .dhtmlSubMenuSelected').children().attr('href');
// after refresh function
$('#report').one('apexafterrefresh', function () {
$s('P47_IDS', f_conc_ids());
$('#form_LOADER').hide();
window.location = l_start_pag;
});
// reset the text item
$s('P47_IDS', '');
f_show_form();
// show all rows in report
javascript: gReport.search('SEARCH', 100000);
}

// Function to read all ids on report
function f_conc_ids() {
var lv_ids;
$('[name="f01"]').each(function () {
if (lv_ids) {
lv_ids = lv_ids + $(this).val() + ':';
} else {
lv_ids = $(this).val() + ':';
}
});
return lv_ids.substring(0, lv_ids.length - 1);
}

function f_show_form() {
$("#report").hide();
$("#form").show();
$('#form_LOADER').show();
}

function f_hide_form() {
$("#form").hide();
$("#report").show();
}

Set HTML Header:

</span>

Display additional information for Shuttle Item

2011-11-11T19:09:00.004Z

Problem: user wants to display some additional information on a selected value in a shuttle menu

Forum post

solution

1. Add the following to "Execute when Page Loads":

$("#P44_SHUTTLE_LEFT,#P44_SHUTTLE_RIGHT").click(function(){

f_emp_detail($('option:selected').val());

});

2. Add the following to "Function and Global Variable Declaration":

function f_emp_detail(pThis) {

var get = new htmldb_Get(null, &APP_ID., 'APPLICATION_PROCESS=get_emp', 44);

get.add('P44_EMPNO', pThis)

gReturn = get.get();

get = null;

$s('P44_DEPT', gReturn);

}

3. Create an On demand application process called get_emp:

DECLARE

lv_dep VARCHAR2(4000);

BEGIN

SELECT deptno2

INTO lv_dep

FROM emp

WHERE empno = :p44_empno;

htp.p(lv_dep);

END;

Demo

(When the user clicks a value in the Shuttle the Department is displayed)

Reformat NULL fields in a report

2011-10-27T23:02:00.005+01:00

Forum Post

Add the following to the 'Execute when Page Loads' region of the Page (where the column name is JACK):

Apex Shuttle Extender Key LOV

2011-10-27T21:57:00.010+01:00

So you really like the Apex shuttle menu with its ease of selecting and ordering multiple values. The difficulty comes when you have a large number of values and the user has difficulty scrolling through the list trying to find the value they want. One solution is to use have a basic set of values in the shuttle item and use the Shuttle Extender plugin to add additional values. Great, but this only works where the user is able to freetext values and the display value is the same as the return value. My requirement was to be able to get the same effect using a Popup Key LOV to extend the shuttle list, and this is how I did it:

1. Create a Shuttle Item P44_SHUTTLE with a basic set of values e.g.

SELECT ename, empno FROM emp WHERE empno > 7782

However the extended shuttle menu will inevitably contain values that are not in the basic lov. Using the Display Additional Values, will only display the return value and not the display value. To get around this, UNION onto the end of the lov query a SELECT that will return the additional values entered.

Note the use of the split item function to convert the colon separated list into a table. The creation of this table function is described here.

UNION

SELECT ename,

empno

FROM emp,

TABLE(useful_functions.split_item(:P44_SHUTTLE))

WHERE empno = column_value

2. Crate another item of type POPUP KEY LOV, P44_SHUTTLE_EXTENDER, with an lov query that contains the full set of values.

SELECT ename, empno FROM emp WHERE empno <= 7782

3. In the Shuttle Extender item set the following.

Post Element Text =>

HTML Form Element Attributes =>

onchange="$(this).css('background-color','')"

4. Add the following code to the Function and Global Variable Declaration of the Page

Demo

Highlight Search Words in Apex Interactive Reports

2011-10-25T12:23:00.007+01:00

This was a problem raised in a Form Post

To implement:

1. Download the jquery.highlight-3.js from this website and add it to the page.

2. Create a style in a CSS sheet or between style tags in the page header

.highlight { background-color: yellow }

3. Create 2 x dynamic actions:

Before Refresh, Region, Execute JavaScript Code

gv_search = $("#apexir_SEARCH").val();

After Refresh, Region, Execute JavaScript Code

if (gv_search) {

$('.apexir_WORKSHEET_DATA td').highlight(gv_search);

}

Or instead of Dynamic Actions add the following to the Execute When Page Loads region

Demo

Prevent wrapping in Apex Interactive Reports

2011-10-25T12:01:00.003+01:00

To prevent wrapping in IR Columns, add the following code to the "Execute when Page Loads" region of the page details

$('td[headers="ADDRESS"]').attr("style","white-space:nowrap");

where "ADDRESS" is the name of the column to which it applies.

Simples!

NB. If the report has partial page refresh on the pagination scheme, then you will need to create a Dynamic Action that fires after Region Refresh and on Page Load with the above JS.

Copying Values using JQuery

2011-10-08T13:56:00.005+01:00

This is a nice way to copy Apex Item values using JQuery. The trick is using a parameter as an attribute name in the JQuery seletor.

function copyField(FieldFrom,FieldTo) {

// loop through items

$('input[name='+FieldTo+']').each(function(index) {

var rownum = $(this).attr('id').substr(3,5);

var FromVal = $v(FieldFrom+$(this).attr('id').substr(3,5))

$(this).val(FromVal);

});

}

This requires a 4 digit rownum to be appended to the item name in the sql to produce a unique id; e.g. name = "f01" and id = "f01_0001".

htmldb_get and Session State Protection

2011-10-04T10:27:00.002+01:00

On a number of occasions I've used the htmldb_get ajax call to embed the content of another Apex page into the div tag of the current page. The content can then be refreshed dynamically without submitting the entire page - Great :). to do this I create a js function something like

function AjaxReportRefresh(pThis){

var get = new htmldb_Get(null,$x('pFlowId').value,null,2);

get.add('P1_AJAX_ID',pThis.value)

gReturn = get.get(null,'','');

get = null;

$x('AjaxReport').innerHTML = gReturn;

return;

}

However, I like to secure EVERY page in my applications with at least URL checksum protection and the above causes problems if the page providing the dynamic content has any SSP.

The solution is simple, have a separate ajax call to set values and then call the content and then you can secure the page providing the content. The function then becomes:

function AjaxReportRefresh(pThis){

//set session values

var get = new htmldb_Get(null,&APP_ID.,'APPLICATION_PROCESS=dummy',1);

get.add('P1_AJAX_ID',pThis.value)

gReturn = get.get();

get = null;

//get content

var get = new htmldb_Get(null,$x('pFlowId').value,null,2);

gReturn = get.get(null,'','');

get = null;

$x('AjaxReport').innerHTML = gReturn;

return;

}

Adjusting Interactive Report column headings

2011-10-01T23:32:00.011+01:00

I have found a few situations where I've needed to add markup to the column heading of a tabular form. For instance if you added an apex_item.checkbox to an interactive report, it would be nice to add a multi-check checkbox to the column heading. The usual way to do this would be to add the following code to the column heading:

There are two issues with this approach. Firstly it can conflict with some of the "Out of the box" functionality you get with IR headings, such as sort, filter, hide etc. Secondly the full escaped markup appears in the 'Select Columns' in the actions menu of the IR. A better approach is to use JQuery to add the code to the column heading. The following code adds a multi-check checkbox to a column called BIN.

Create an Advanced Dynamic Action

Event: After Refresh

Selection Type: Region

Region: [your region]

Event: Scope: Bind

Action: Execute JS Code

Fire when result is: True

Fire on page load: Checked

Code:

  $("#BIN").html($("#BIN").html()+'<input type="checkbox" onclick="$f_CheckFirstColumn(this)">');

Make sure that the IR has a region template :)

Demo

Save Changes Reminder for IRs and Custom Tabular Forms with PPR

2011-09-30T23:36:00.025+01:00

I have been implementing the SkillBuilders Save Before Exit Apex Plugin, which has been working well for the majority of pages in my application. The two situation that it doesn't appear to cater for are when paginating in custom tabular forms where Partial Page Refresh (PPR) pagination has been used. In a standard report region this feature can be removed, but in IRs they cannot.

I have written a small piece of code that will intercept the pagination function for PPRs and and allow you add some of your own JS. An example can be found here.

And here's how I did it:

1. In the page header or in a region on page 0, add the following code:

var p_changes = false;

$(document).ready(function() {

$('.apexir_WORKSHEET_DATA,[id^="report"]').live('change',function(){

p_changes = true;

});

$('a[href^="javascript:gReport"],a[href^="javascript:$a_report_Split"]').live('click',

function(event) {

if (p_changes) {

if (confirm('"SAVE_CHANGES_MSG"')) {

p_changes = false;

}

else {

event.preventDefault();

}

});

</script>

2. Create a shortcut call SAVE_CHANGES_MSG

Are you sure you want to leave this page?\n\nYou have made changes to this page. If you navigate away from this page without first saving your data, the changes will be lost.\n\nClick OK to leave this page, or Cancel to stay on this page.

Building on Dunc's Tabular Form Solution

2011-09-17T17:27:00.005+01:00

I often use Dunc's javascript add/delete row solution when building tabular forms manually with the apex_item functions. For tabular forms where I think it will be useful for the user to use the up and down keys to navigate I use this solution from Roel Hartman.

The issue I have is that these two solutions don't work very well together. Each row added is a clone of the first row in the tabular form and therefore does not have an incremental id value. I have added two lines to Dunc's javascript function that will update the IDs for the added row and maintain the integrity of the navigation function. The addRow function now becomes:

function addRow(theRegionId)

{

var allTabs = document.getElementById(theRegionId).getElementsByTagName("table");

for(var i =0 ; i < allTabs.length ; i++)

{

if (allTabs[i].id.indexOf("datatable") >= 0)

{

table = document.getElementById(allTabs[i].id);

break;

}

var newRow = table.tBodies[0].rows[1].cloneNode(true);

table.tBodies[0].appendChild(newRow);

// Identifies how many rows there are in the Tabular Form

var oRows = document.getElementById('report_'+theRegionId).getElementsByTagName('tr').length

var inputElements = newRow.getElementsByTagName("input");

for (i = 0; i < inputElements.length; i++)

{

inputElements[i].value = "";

// Updates the ID value

inputElements[i].setAttribute('id',inputElements[i].getAttribute('name')+'_'+pad(oRows-3,4));

}

function delRow(theElement)

{

var rowToDelete = theElement.parentNode.parentNode;

var parentTable = theElement.parentNode.parentNode.parentNode.parentNode;

document.getElementById(parentTable.id).deleteRow(rowToDelete.rowIndex);

}

Building SAAS Applications

2009-12-30T14:26:00.003Z

I recently read this interesting article by Steve Bobrowski on creating a SAAS (software as a service) system. Of particular note is the process of creating apex item labels for flexi-fields using JS to seperate a colon seperated list. Throw VPD into the mix and you have an elegant solution. Enjoy:
http://www.oracle.com/technology/pub/articles/bobrowski-saas.html

Next and Prev from an Interactive Report 2

2009-12-07T13:37:00.011Z

I have found a bit of time to re-look at how to retrieve the sql being generated in an Interactive Report with a view to using it to provide a Next and Previous button in an interactive report. From my previous work, Stew Stryker rightly pointed out that I didn't cater for global searches or saved interactive reports. I have incorporated his suggestions into the solution and I think it's just about cracked. If you notice any problems, please let me know.

Demo

How to do it

1. Create the following function and procedure:

create or replace FUNCTION split_columns
(
p_cond_expr VARCHAR2,
p_column VARCHAR2,
p_date_cols VARCHAR2)
RETURN VARCHAR2
IS
l_sql VARCHAR2(4000);
l_vc_arr2 apex_application_global.vc_arr2;
l_vc_arr1 apex_application_global.vc_arr2;
l_date_y VARCHAR2(1);
BEGIN
l_vc_arr2 := apex_util.string_to_table(p_column);
l_vc_arr1 := apex_util.string_to_table(p_date_cols, ',');
FOR z IN 1 .. l_vc_arr2.COUNT
LOOP
l_date_y := NULL;
FOR Y IN 1 .. l_vc_arr1.COUNT
LOOP
IF l_vc_arr2(z) = l_vc_arr1(Y) THEN
l_date_y := 'Y';
END IF;
END LOOP;
IF l_date_y is null THEN
l_sql := l_sql || 'OR INSTR(UPPER("' || l_vc_arr2(z) || '"),UPPER(''' || p_cond_expr ||
'''))>0 ';
END IF;
END LOOP;
l_sql := 'WHERE (' || ltrim(l_sql, 'OR')||')';
RETURN(l_sql);
END;
/
create or replace PROCEDURE next_prev_values
(
p_column_id IN VARCHAR2,
p_value IN NUMBER,-- The id value of the selected record
p_page_id IN NUMBER,-- Page number of the interactive report
p_report_id IN NUMBER,
p_bvar1 IN VARCHAR2,-- Bind variable value1
p_bvar2 IN VARCHAR2,-- Bind variable value2
p_bvar3 IN VARCHAR2,-- Bind variable value3
p_bvar4 IN VARCHAR2,-- Bind variable value4
p_next OUT NUMBER,
p_prev OUT NUMBER,
p_top OUT NUMBER,
p_bot OUT NUMBER,
p_cur_tot OUT VARCHAR2,
p_debug OUT VARCHAR2
-- Returns the report query
)
AS
lv_sql VARCHAR2(32767);
lv_predicate VARCHAR2(4000);
lv_sql_sort VARCHAR2(4000);
lv_comb VARCHAR2(32767);
lv_condtion_type VARCHAR2(30);
lv_search VARCHAR2(4000);
BEGIN
/*set the deliminator to be used in the string_agg funtion*/
string_agg_control.g_delim := ' and ';
/*Return the report query and any addition sort and filters from the Interactive Report*/
SELECT
z.sql_query,
nvl2(a.predicate, ' where '
|| a.predicate, NULL) sql_predicate,
nvl2(x.sort_column_1, x.sort_column_1
|| ' '
|| x.sort_direction_1
|| nvl2(x.sort_column_2, ', '
|| x.sort_column_2
|| ' '
|| x.sort_direction_2, NULL)
|| nvl2(x.sort_column_3, ', '
|| x.sort_column_2
|| ' '
|| x.sort_direction_3, NULL)
|| nvl2(x.sort_column_4, ', '
|| x.sort_column_2
|| ' '
|| x.sort_direction_4, NULL)
|| nvl2(x.sort_column_5, ', '
|| x.sort_column_2
|| ' '
|| x.sort_direction_5, NULL)
|| nvl2(x.sort_column_6, ', '
|| x.sort_column_2
|| ' '
|| x.sort_direction_6, NULL)||', rownum ', ' rownum ') sql_sort,
nvl2(a.search, split_columns(a.search, x.report_columns, b.date_cols), NULL) search
INTO
lv_sql,
lv_predicate,
lv_sql_sort,
lv_search
FROM
/*This inline view aggregates any filters added to the Interactive Report*/
(
SELECT
report_id,
string_agg( DECODE(condition_type, 'Filter', REPLACE(REPLACE(y.condition_sql,
'#APXWS_EXPR2#', ''''
|| condition_expression2
|| ''''), '#APXWS_EXPR#', DECODE(SUBSTR(condition_operator, 1, 9), 'is in the',
condition_expression, ''''
|| condition_expression
|| '''')))) predicate,
MAX(DECODE(condition_type, 'Search',y.condition_expression)) search
FROM
apex_application_page_ir_cond y
WHERE
y.condition_type IN ('Filter', 'Search')

AND y.condition_enabled = 'Yes'
GROUP BY
report_id) a,
/*Returns any date columns*/
(
SELECT
interactive_report_id,
STRING_AGG(COLUMN_ALIAS) date_cols
FROM
APEX_APPLICATION_PAGE_IR_COL
WHERE
column_type = 'DATE'
GROUP BY
interactive_report_id) b,
/* The following view contains the report original report query*/
apex_application_page_ir z,
/*The following view provides any column sorting*/
apex_application_page_ir_rpt x
WHERE
x.report_id = a.report_id(+)
AND x.INTERACTIVE_REPORT_ID = b.INTERACTIVE_REPORT_ID
AND x.interactive_report_id = z.interactive_report_id(+)
AND x.session_id = nv('SESSION')
AND x.application_id = nv('APP_ID')
AND x.base_report_id = p_report_id
AND x.page_id = p_page_id;
/* Build the query used in the Interactive Report */
lv_comb := 'select prev, next, top, bot, cur||'' of '' ||tot from (
select
' ||p_column_id || '
,lag (' || p_column_id || ') over (order by ' || lv_sql_sort ||') prev
,lead (' || p_column_id || ') over (order by ' || lv_sql_sort ||') next
,first_value (' || p_column_id || ') over (order by ' || lv_sql_sort ||') top
,last_value (' || p_column_id || ') over (order by ' || lv_sql_sort ||
' RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) bot

,count (' || p_column_id ||') over (order by ' || lv_sql_sort || ') cur
,count (' || p_column_id ||') over () tot
from (select * from (' || lv_sql || ')'||lv_search||')' ||
lv_predicate || ') where ' || p_column_id || ' = ' || p_value;
IF p_bvar4 IS NOT NULL THEN
EXECUTE IMMEDIATE lv_comb INTO p_prev,
p_next,
p_top,
p_bot,
p_cur_tot USING p_bvar1,
p_bvar2,
p_bvar3,
p_bvar4;
ELSIF p_bvar3 IS NOT NULL THEN
EXECUTE IMMEDIATE lv_comb INTO p_prev,
p_next,
p_top,
p_bot,
p_cur_tot USING p_bvar1,
p_bvar2,
p_bvar3;
ELSIF p_bvar2 IS NOT NULL THEN
EXECUTE IMMEDIATE lv_comb INTO p_prev,
p_next,
p_top,
p_bot,
p_cur_tot USING p_bvar1,
p_bvar2;
ELSIF p_bvar1 IS NOT NULL THEN
EXECUTE IMMEDIATE lv_comb INTO p_prev,
p_next,
p_top,
p_bot,
p_cur_tot USING p_bvar1;
ELSE
EXECUTE IMMEDIATE lv_comb INTO p_prev,
p_next,
p_top,
p_bot,
p_cur_tot;
END IF;
p_debug := lv_comb;
END next_prev_values;
/

2. Add this Javascript function into you JS file or between script tags in the page HTML Header

function goto(url) {
var newUrl = url.replace("$report_id$", $v('apexir_REPORT_ID'));
location.href = newUrl;}

3. Use this link for the IR column link

javascript:goto('f?p=&APP_ID.:17:&SESSION.::&DEBUG.:17:P17_EMPNO,P17_IR_REPORT_ID:#EMPNO#,$report_id$');

4. Create an 'On Load before Header' process as follows:

next_prev_values(
p_column_id => 'EMPNO'
,p_value => :P17_EMPNO
,p_page_id => 16
,p_report_id => :P17_IR_REPORT_ID
,p_next => :P17_NEXT
,p_prev => :P17_PREVIOUS
,p_debug => :P17_DEBUG
,p_top => :P17_FIRST
,p_bot => :P17_LAST
,p_cur_tot => :P17_CURRENT
,p_bvar1 => :P16_JOB
,p_bvar2 => NULL
,p_bvar3 => NULL
,p_bvar4 => NULL
);

*Where p_bvar are items that are used in the main report sql.

5. Create items that correspond to the process created above. The Next, Prev, First & Last are buttons; the rest are hidden items. The debug will contain the sql being generated and is useful for debugging.

6. The above requires the StringAgg function to be installed. If you don't already have it, then download it from here and install.

Theatre Booking System

2009-12-03T16:45:00.007Z

This was an interesting little exercise that cropped up on the Apex forum:

Forum Post

The challenge was to create a theatre seat booking report that showed the booked and available seating. The report was to lay the seats out in the same configuration as in the theatre with the twist that the seats were staggered. A solution using a conventional HTML table layout would not work to well. Instead my solution used a background image as a container and then relative position for the seats. Rather than a report, my suggestion was to place a checkbox in each seat position so the user could easily select an available seat as part of a booking wizard. This solution could also be used for a restaurant, hotel, hospital etc booking system.

Lessons learnt:

You can format checkboxes with CSS but Safari and Firefox ignore most of the settings.
The X Y positions for the checkboxes would be better stored in the database. this would give you much more flexibility for complex layouts.
The background image should contain most of the details the user would need for orientation such as the Stage, Isles, stalls, balcony and exits.
The function could be adapted to use images or checkboxes depending on what it was being used for.
It would be clearer for booked seats to be replaced by an image rather than a checked, disabled checkbox.

Example and Code

Convert a colon separated function into a table

2009-11-28T18:19:00.007Z

The is a useful function that converts the output from the page items below into a table, so that you can manipulate the data using SQL.

List Manger
Multiple Select
Checkboxes
Shuttle

CREATE OR REPLACE PACKAGE useful_functions IS

-- Author : SHUNT
-- Created : 25/11/2009
-- Purpose : Lots of useful function and procedures

-- Public type declarations
TYPE ret_val_t IS TABLE OF VARCHAR(4000);

-- Public function and procedure declarations
FUNCTION checkbox_table(p_checkbox VARCHAR2) RETURN ret_val_t
PIPELINED;

END useful_functions;
/
CREATE OR REPLACE PACKAGE BODY useful_functions IS
FUNCTION checkbox_table(p_checkbox VARCHAR2) RETURN ret_val_t
PIPELINED IS
l_vc_arr2 apex_application_global.vc_arr2;
BEGIN
l_vc_arr2 := apex_util.string_to_table(p_checkbox);
FOR z IN 1 .. l_vc_arr2.COUNT LOOP
PIPE ROW(l_vc_arr2(z));
END LOOP;
END;
END useful_functions;
/

To convert the other way i.e. from a table into a colon separated string I would use the StringAgg function.

To return values from a table function use the following syntax:

SELECT

FROM

TABLE(useful_functions.checkbox_table('a:b:c:d:e'))

Forum Post

Copying Values

2009-11-27T10:47:00.004Z

I am still a novice with JavaScript so this will appear elementary to most readers. Following are two JavaScript functions that can be used in Apex to copy, firstly an address and secondly a column in a tabular form.

1. Addresses. It is often the case in a Contact Management database that the same address may need to be entered more than once on the same page. E.g the main address maybe followed by a postal address, work address, next of kin address etc, which may all be the same. An easy way to save the user having to retype the addresses is to use JavaScript to copy the fields, as follows:

Add the following code to the HTML Header section of the page:

function copyField (fromField, toField){
$x(toField).value = $x(fromField).value;
}

function copyAddress(fromAddress1, toAddress1, fromAddress2, toAddress2, fromAddress3, toAddress3, fromCounty, toCounty,fromPostcode, toPostcode){
copyField(fromAddress1, toAddress1);
copyField(fromAddress2, toAddress2);
copyField(fromAddress3, toAddress3);
copyField(fromCounty, toCounty);
copyField(fromPostcode, toPostcode);
}

add a checkbox with the following in the HTML Form Element Attributes:

onclick="copyAddress('P1_ADD1','P1_POSTAL_ADD1','P1_ADD2','P1_POSTAL_ADD2',etc)"

OR add this to the href of an image

href="javascript:copyAddress('P1_ADD1','P1_POSTAL_ADD1','P1_ADD2','P1_POSTAL_ADD2',etc);"

Where main address fields are:
P1_ADDRESS_1
P1_ADDRESS_2
P1_ADDRESS_3
P1_COUNTY
P1_POSTCODE

and Postal address fields are:
P1_POSTAL_ADD1
P1_POSTAL_ADD2
P1_POSTAL_ADD3
P1_POSTAL_COUNTY
P1_POSTAL_POSTCODE

2. Copy the column of a tabular form. I recently had to do this in a financial system that had ledger sheets where the values to be submitted in a column were often, but not always, the values in one of the other columns. Using the same principle as above, the trick is to ensure that each field in the tabular form has a unique ID. This can be done by adding the ROWNUM to the array number in the ID value of the APEX_ITEM API. e.g.

apex_item.text(3, NULL, 4,'','style="text-align:right"','f03_#ROWNUM#')

The copy function then changes to loop through the rows of the tabular form as follows:

function copyFields(fromField1,toField1) {
var cnt=1;
while (document.getElementById(fromField1+cnt) != null){
copyField(fromField1+cnt,toField1+cnt);
cnt+=1;}
}

Then use an 'on event' or url to run the function:

="copyFields('f05_','f03_')"
or
href="javascript:copyFields('f05_','f03_')"

Auditing Downloads from Interactive Reports

2009-02-22T20:28:00.004Z

Since the Data Protection Act (DPA) came into force auditing of the viewing and downloading of personnal information has become an issue. The DPA allows for the use of personal information so long as there is a valid buisiness reason and that appropriate measures are taken to protect that information. Auditing downloads is one way for an organisation to monitor who has taken information off-line and therefore out of their control. These are the basic steps:

1. Create an audit log table:

ID (NUMBER)
APP_USER (VARCHAR2(60))
DOWNLOAD_DATE (DATE)
QUERY (VARCHAR2(4000))

2. Create an Hidden and Protected item :P1_IR_REPORT_ID

3. Install the apex_ir_query package by Stew Styker at

http://stewstools.wordpress.com/apex_ir_query-package/

4. Create a Page Process

Process point - Onload Before Header
Condition Type - Request is Contained within Expression 1
Expression 1 - CSV,RTF,XLS,PDF
Source:

INSERT INTO DOANLOAD_AUDIT
(APP_USER
,DOWNLOAD_DATE
,QUERY)
VALUES
(:APP_USER
,SYSDATE
,apex_ir_query.ir_query_where(:APP_ID,<IR report page>,:SESSION,:P1_IR_REPORT_ID))

How to cope with Lists Of Values (LOVs)

2009-02-17T14:17:00.010Z

Another problem that has been perplexing me is what is the best way to manage LOVs. Apex provides a variety of mechanisms for managing LOVs including static or dynamic LOV which can be specified centrally or locally to an item. Best Practice dictates that centrally managing LOVs is preferred where possible as it provides a single point of update.

I have found a few issues with the Static LOVs as follows:
• Discoverer Reporting. Viewing LOV values in Discoverer is tricky as you have to query the flows table. This can be done with the following view but you have to remember to change the names of the flows tables after an upgrade of Apex.

SELECT
DISTINCT lov.lov_name
,lovd.lov_disp_value
,lovd.lov_return_value
,lovd.lov_disp_sequence disp_sequence
FROM flows_030100.WWV_FLOW_LISTS_OF_VALUES$ lov
,flows_030100.WWV_FLOW_LIST_OF_VALUES_DATA lovd
WHERE lov.id = lovd.lov_id

• Conditions. It is possible to add conditions to each static LOV item, which in effect writes nested sub-queries that are difficult to manage and probably not very performant.
• Maintenance. Changing the values of the LOVs can only be done from the workspace, so a minor change to an LOV values can only be done as part of a release.
• Deleting an LOV item causes the ID from the Flows table to be returned (if display additional items is checked) or a null value is returned (If display nulls is checked) or return the first value in the select list. None of these options are ideal.

My preferred method of managing LOVs is to create my own custom table along the following lines:

LOV_ID (NUMBER)
DISPLAY_VALUE (VARCHAR2)
ELEMENT_NAME(VARCHAR2)
LOV_NAME (VARCHAR2)
SORT_ORDER (NUMBER)
VALID_FROM (DATE)
VALID_TO (DATE)
NOTES (VARCHAR)
EDITABLE (VARCHAR2)
ATTRIBUTE1 (VARCHAR2)
ATTRIBUTE2 (VARCHAR2)
ATTRIBUTE3 (VARCHAR2)
ATTRIBUTE4 (VARCHAR2)

I have added a few additional attributes columns, which are useful for storing values for reporting or conditional displays. Each LOV item has a validity period, which can be used to expire values so they do not appear as a selectable items in an LOV. However, as they still exist in the table they can be displayed in reports and as additional items. The element name is useful for writing scripts to update attribute values, as the ID could differ from your development environment and the display value may change. The notes field is used for storing information about the values set in the additional attributes.

To get invalid items to display as additional item in a Select List or popup lov use the following for your LOV query.

SELECT display_value
,lov_id
FROM lov
WHERE SYSDATE BETWEEN valid_from AND
nvl(valid_to ,SYSDATE)
UNION
SELECT display_value
,lov_id
FROM lov
WHERE lov_id = :p1_colour_id
AND lov_name = 'COLOUR'

Its quite nice to let the user know that the value has expired and needs changing. I like to do this by appending an asterisk to the display value with the following lov query.

SELECT nvl2(lov2.lov_id
,lov1.display_value
,'(' lov1.display_value ')') display_value
,lov1.lov_id
FROM (SELECT display_value
,lov_id
FROM lov
WHERE SYSDATE BETWEEN valid_from AND
nvl(valid_to,SYSDATE)
UNION
SELECT display_value
,lov_id
FROM lov
WHERE lov_id = :p1_colour_id
AND lov_name = 'COLOUR') lov1
FULL OUTER JOIN (SELECT display_value
,lov_id
FROM lov
WHERE SYSDATE BETWEEN valid_from AND
nvl(valid_to,SYSDATE))lov2
ON lov1.lov_id = lov2.lov_id

Example.

Next and Previous from an Interactive Report

2009-02-16T14:04:00.001Z

The new feature of Interactive Reports (IR) in Apex provides the user with the ability to search, sort and filter reports easily, create their own versions of reports and save them for future use. One limitation with IRs is that I haven’t been able to find a satisfactory way of producing Next and Previous buttons on a custom details screen. The issue being how to best capture the Filters and Sorts that have been set in the IR. I have seen some very slick solutions using JS and AJAX techniques whereby the IDs of the records displayed in the IR are saved to an array and then referenced from the detail screen. Ajax is then used to re-query the main IR to get the next pagination set if required. The only issue I have with these techniques is that they are a bit involved to incorporate and require hidden items to be concatenated to existing fields. If those fields are then removed from the report by the user then that breaks Next Previous function.

I have written a procedure that reconstructs the query being run in the IR from the APEX views and then uses Analytics to return the Next, Previous, First, Last and Count values. It works pretty well and should be quick and easy to implement. The only failing I have found is when no sort is applied to the IR and the base query has no order by; in this case the order of the next and previous can be different to the order on the IR.

The complete solution can be viewed from the link below, which includes a demonstration and a copy of the procedure. The procedure references the string_agg function, a must for any Apex developer. I have added a link to a Blog which offers a good explanation of the function and downloads of the code.

Implementation should be self explanatory, but drop me a line if you need anymore help. I do not profess to being a PLSQL expert so I would be very interested in any ideas of a better way to do it.

Forum Post
String_agg function
SHUNTs solution

Merge into Duncs Tabular Form

2009-02-12T18:23:00.000Z

Duncs has created an excellent JS based method for generating tabular forms in Apex. The nice part is that additional rows can be added without the need to refresh the page. I try to avoid using separate insert, update and delete statements and prefer the more performant Merge statement.

Duncs method
Demo

The issue is identifying the rows to be deleted in the UPDATE part of the MERGE as they are deleted from the collection and not just flagged for delete. The answer is to use an outer join to join your update table with your tabular form collection. My first few attempts resulted in syntax errors, so after trying various combinations of inline views, I came up with the following:

MERGE INTO emp e
USING (SELECT *
FROM (SELECT to_number(c001) c001
     ,c002 ename
     ,c003 job
     ,c004 mgr
     ,c005 hiredate
     ,f.empno
     ,f.deptno
   FROM (SELECT *
FROM apex_collections
WHERE collection_name = 'EMP')
FULL OUTER JOIN emp f ON to_number(c001) = f.empno)
WHERE deptno = :p1_deptno) c
ON (e.empno = c.empno)
WHEN MATCHED THEN
UPDATE
SET e.ename = c.ename
   ,e.job = c.job
   ,e.mgr = c.mgr
   ,e.hiredate = c.heirdate DELETE
WHERE c.c001 IS NULL
WHEN NOT MATCHED THEN
INSERT
   (e.ename
   ,e.job
   ,e.mgr
   ,e.heirdate
   ,e.deptno)
VALUES
   (c.ename
   ,c.job
   ,c.mgr
   ,c.hiredate
   ,:p1_deptno);

Hiding Columns In Interactive Reports

2009-01-19T10:32:00.000Z

This problem has been bugging me for ages, how to hide columns when downloading with interactive reports. The answer is simple; for the column you wish to hide set the Conditional Display as follows:

Condition Type: Request Is NOT Contained within Expression 1

Expression1: CSV,PDF,RTF,XLS

Cally

2008-11-25T23:15:00.001Z

Cally is Chinchilla Persian, Blue Persian cross with a bit of tabby thrown in for good grace. I bought her from a lady in Portsmouth who was the girlfriend of a bloke I used to get a lift to work from. Cally was born as the second in a litter of three, the first one out was Kevin, a big cuddly lump of a cat; next was Cally and then came Jazmine a kind of ratty looking thing, not at all like the other two. I had been looking for a pet for some time and although I would consider myself a dog man these kittens looked gorgeous. I liked Cally and Nikki liked Kevin, and rather than argue about it we decided to take both. To cut a long story short I split with Nikki and was lucky enough to end up with both Kevin and Cally.

Kevin and Cally have been my companions for the last 16 years and during that time they have been constant source of joy and love for me. They usually lay on the road outside the house or sit on the driveway waiting for me to come home from work. Each year as winter approached, I have a constant fight with the dreaded knots between their legs caused by their long winter coats. No matter what I’m going though they are always there; always understanding and always have lots of love to give. Even when I slammed Cally’s head in a door, she just looked at me to say ‘what did you do that for’ and then continued on her way.

Cally particularly has always been a good bed friend, especially if she knows I’m down. Just a few days ago, after I fell out with the RAF at work, I awoke to find her curled up on my pillow, snoring away. Interestingly I have never had any problem sleeping with her snoring, but at boarding school and in army barracks it drives me up the wall. My 'happy thought' is to think of Cally curled up in my arms, both of us asleep. That thought has made some pretty miserable situations very bearable. Particularly useful at Sandhurst where it was pretty miserable the whole time.

Cally died in my arms at 6 o’clock tonight after severe kidney failure. The sense of loss I feel is indescribable and I have no idea how to move on with my life. I have had wonderful love and support from my friends, family and Gaily, which is very much appreciated, but at the end of the day I have lost the best friend a man could ever have. Bye bye baby I love you and will miss you terribly.

Drumming Hunt

2008-11-09T23:20:00.000Z

Wow, no blog for 2 years and then 2 in one day. Perhaps this is a sign of my commitment to spend less time working and more time having fun. Anyway, this is the best clip of my all-time favourite drummer, Mr Johnny Rabb, the free-hand master.

Editable Help

2008-11-08T22:37:00.003Z

I have now delivered a number of Apex applications into production and have to say that as much as I like the item help functionality in Apex, I have never actually taken the time to fill each item with anything particularly useful. The Apex team are as bad with their item help which usually re-words the the label text. A better solution would be to have the ability to edit the item help whilst the application is in production, thus allowing users to add their own help information when required. Forum question

Solution
This demo application has 2 users: a standard user and an admin users. The standard user is able to view the item help whereas the admin user can view and edit the help. This was achieved with the following steps:

1. Create an Optional label Template called 'Optional Label with Editable Help'.

<label for="#CURRENT_ITEM_NAME#"><a class="t13OptionalLabelwithHelp" href="javascript:popUp2('f?p=&APP_ID.:506:&SESSION.:::506:P506_ITEM_ID:#CURRENT_ITEM_NAME#',630,600)" tabindex="999">

2. Create an Required label Template called 'Required Label with Editable Help.

<label for="#CURRENT_ITEM_NAME#"><a class="t13RequiredLabelwithHelp" href="javascript:popUp2('f?p=&APP_ID.:506:&SESSION.:::506:P506_ITEM_ID:#CURRENT_ITEM_NAME#',630,600)" tabindex="999"><img src="#IMAGE_PREFIX#requiredicon_status2.gif" alt="" />

These label templates can be used for any item help that you wish to allow admin users to edit. If you want all item help to be editable then overwrite the existing templates with the above.

3. Create a Table in your target Schema to store the help information.

"ITEM_ID" NUMBER PK
"ITEM_NAME" VARCHAR2(60)
"PAGE_ID” NUMBER
"HELP" VARCHAR2(4000)

4. Create a help page in your application with a 'popup' template (e.g. 506).

5. Create an Insert/Update form Region based on the Help table. Set the help item to be of type HTML. Create an item :P506_EDlT Hidden and Protected and set the Form Region to conditionally display when item:P506_EDIT=' Y'.

6. Add another HTML region with an item of type Display Only to conditionally display when :P506_EDIT is null source of type SQL

SELECT help FROM help WHERE item =:P506_ITEM_ID

7. Add an edit button to the HTML region with a branch to change the :P506_EDit=' Y'.

8. Migration Script

INSERT INTO help
(item_id, item_name, page_id, help)
(SELECT
item_id, item_name, page_id, item_help_text
FROM
apex_application_page_items
WHERE application_id = 55349
AND item_label_template LIKE '%Editable%')

Postcode Plotting

2007-02-22T00:08:00.000Z

I have used the postcode to plot locations in a number of Apex applications. Although it’s not as function rich as other products like Google Earth/Maps or Spatial, it’s very quick and easy to do.
Example: http://apex.oracle.com/pls/otn/f?p=33715

It works by comparing the first half of the UK postcode and retrieving the X Y coordinates from a postcode database. The XY coordinates are applied to a scale and then Map Pin images are plotted onto a UK Map image using a CSS position function. I have added a feature that will ‘jog’ map pins for different items that have the same postcode. The calculations are based on Similar Triangles and the jogging feature uses Trigonometry.

Instructions can be found at:

http://apex.oracle.com/pls/otn/f?p=33715:4

There are a few configurations that need to be made to the procedure for it to work.
1. Work out the Offsets and Scale. You can see the affects of the offset and scale in the above example. It is possible to calculate the scale by cutting the scale out of you map image (if it has one) using an image editor. The size of the image in pixels can then be used to calculate the scale. I prefer to do the whole thing by trial and error.
2. Next add you Map and Map Pin images. I have used this procedure with over 40 different Map Pins named Symbol_1 …… Symbol_40, which are then dynamically assign in the procedure. A more simple method to change the Map Pin is using a DECODE or CASE in the cursor. For example:

SELECT decode(dname, ' ACCOUNTING', 'green_flag.gif', ' (RESEARCH)', 'red_flag.gif', ' SALES', 'white_flag.gif', 'g_can.gif') image_file,
location,
deptno id,
ba.post_code postcode,
(pc.x + left_offset) / left_scale x,
(top_offset -pc.y) / top_scale y
FROM dept,
postcode pc
WHERE SUBSTR(ba.post_code, 1, (instr(ba.post_code, ' ', 1, 1) -1)) = pc.postcode(+)
ORDER BY pc.postcode;

3. Configure the Cursor. Adjust the cursor so that it includes the locations that you wish to display. Above is a simple example that shows how to link your locations to the postcode table. Note the order by at the end. This is important if you want to jog Map Pins that have the same postcode.
4. Add a link. This is optional and can be removed, but it is a nice feature to link to details for that particular location.

Vassaliki 2006

2006-12-23T13:03:00.000Z

Hunt doing it!

Pasty white legs and balding head are not a good look.

Good example of Super 7 stance.