AE Training days Utrecht

I attended the Apex Evangelists training days this week in Utrecht. From tuesday till friday I listened to John and Dimitri, who were presenting a variation of topics.

First off, let me thank Dimitri and John for the great organization! Unfortunately I couldn't attend the bowling trip, not that I would have made any difference with the bowling itself (I kinda suck at it ;) )

The best thing with this training was the extent of topics covered, for starters and more advanced developers. I know from first hand experience it's very hard to create an allround presentation about a topic.
It also shows the expertise and flexibility that John and Dimitri have.

I especially liked the presentations on the APEX Dictionary, Best practices, the security one and the 3d party integration! I really got some new things I'm gonna look in to or use!

Oh...and if some of my future blog posts contain a topic that looks like a topic on the training I'll certainly reference the training :)

Finally I can really recommend this training to anyone who wants to know more in depth information on APEX.

P.S.
From a very reliable source, I heard John's book will be finished before Open World 2008 ;) Sorry John, but you'll not get the question anymore after that :)

Drag&Drop Planboard

Creating out of the box functionality for web apps with APEX is as fun as it is challenging. Some time ago Roel Hartman and me started working on a transport and logistics application in APEX. (Roel allready blogged about it once here)
One specific part of this application was a planboard which used dragging and dropping with orders in sort of a truck calendar. It was build in Java and had all the swingy features you'd expect.
The client wanted to know if this was possible with APEX. Well, sure it is. We'll just use the full pallete available for us with the web browser! CLICK HERE TO SEE IT.
Obviously their are many paths to take when considering the design for this sort of functionality, especially when you have APEX at your disposal to create it.

Should we use the available calendar?
Could we use a tabular form?
Do we use standard APEX DML report/forms?

Well, this is what we did ;)

The design


1. Collect the data for the planboard view, it will show the truck number (equipment number) and the orders in the days of the week:

Datamodel

Viewquery

SELECT trucks.truck_id
,      to_char(to_date(load_date), 'YYYYIW') load_week
,      max(decode( rtrim(to_char(load_date,'day', 'nls_date_language = DUTCH')), 'maandag'  , order_id, null )) mon
,      max(decode( rtrim(to_char(load_date,'day', 'nls_date_language = DUTCH')), 'dinsdag'  , order_id, null )) tue
,      max(decode( rtrim(to_char(load_date,'day', 'nls_date_language = DUTCH')), 'woensdag' , order_id, null )) wed
,      max(decode( rtrim(to_char(load_date,'day', 'nls_date_language = DUTCH')), 'donderdag', order_id, null )) thu
,      max(decode( rtrim(to_char(load_date,'day', 'nls_date_language = DUTCH')), 'vrijdag'  , order_id, null )) fri
,      max(decode( rtrim(to_char(load_date,'day', 'nls_date_language = DUTCH')), 'zaterdag' , order_id, null )) sat
,      max(decode( rtrim(to_char(load_date,'day', 'nls_date_language = DUTCH')), 'zondag'   , order_id, null )) sun
FROM   trucks,
     orders
WHERE  orders.truck_id (+) = trucks.truck_id
GROUP BY trucks.truck_id,
to_char(to_date(load_date), 'YYYYIW')
ORDER BY trucks.truck_id

2. The report region has the planboard view as source. For every column box (value or not) a unique id is created . These id’s make sure that a column box is a container where you can drop items in. The column boxes with a value, should show the item with its value.

Report buildup

The column formatting for every day column

3. The report region must also call a javascript function CreateDragContainer, for every container in the region. While doing this, all items inside a container (or just the values selected with the view) become draggable. With the unique id’s, movement can be tracked, and when an order item is dropped in a container, the rest of the handling can start. This is all done by Javascript, where credit must go out for a part of the dragging & dropping to Mark Kahn at webreference.
The most important part is that the id's are filtered, and passed to application items, and that an AJAX call is made to an application process. Here's the AJAX call code:

var ajaxRequest = new htmldb_Get(null,&APP_ID.,'APPLICATION_PROCESS=updatePlanbord',null);
if (curTarget.id.substr(0,3) == 'new'){
  ajaxRequest.add('P_TARGET', curTarget.id.substring(13, curTarget.id.length));
  ajaxRequest.add('P_ADDACTION', 'true');
  var cDay = curTarget.id.substring(9,12);
}else{
  ajaxRequest.add('P_TARGET', curTarget.id.substring(9, curTarget.id.length));
  ajaxRequest.add('P_ADDACTION', 'false');

  var cDay = curTarget.id.substring(5,8);
}
          var cDayDate
          if (cDay == 'mon'){
           cDayDate = html_GetElement('P1_MON_DAT').value;
          }else if (cDay == 'tue'){
           cDayDate = html_GetElement('P1_TUE_DATE').value;
          }else if (cDay == 'wed'){

           cDayDate = html_GetElement('P1_WED_DATE').value;
          }else if (cDay == 'thu'){
           cDayDate = html_GetElement('P1_THU_DATE').value;
          }else if (cDay == 'fri'){
           cDayDate = html_GetElement('P1_FRI_DATE').value;
          }else if (cDay == 'sat'){
          cDayDate = html_GetElement('P1_SAT_DATE').value;
          }else if (cDay == 'sun'){
           cDayDate = html_GetElement('P1_SUN_DATE').value;
          }

ajaxRequest.add('P_PARENT', rootContainer.id.substring(9, rootContainer.id.length));
ajaxRequest.add('P_LOADDATE', cDayDate);

ajaxRequest.GetAsync(f_AsyncReturn);

4. The application process can do two things, UPDATE an existing order when it is dropped in another truck, or UPDATE an existing order when it had no truck (open action) and was dropped in another truck. Here's the code:

DECLARE
type_action varchar2(10);

BEGIN
type_action := ''||:P_ADDACTION||'';

IF type_action= 'false' THEN

UPDATE orders SET truck_id = :P_PARENT WHERE order_id = :P_TARGET AND to_date(load_date, 'dd-mm-yyyy') = to_date(''||:P_LOADDATE||'', 'dd-mm-yyyy');

ELSIF type_action = 'true' THEN

UPDATE orders SET truck_id = :P_PARENT WHERE order_id = :P_TARGET;

INSERT INTO orders (ORDER_ID, DESTINATION, LOAD_DATE, TRUCK_ID) VALUES (ODERS_SEQ.nextval, NULL, to_date(''||:P_LOADDATE||'', 'dd-mm-yyyy'), NULL);

END IF;
COMMIT;
EXCEPTION WHEN OTHERS THEN
htp.p(SQLERRM);
END;

After some CSS tricks and custom theming, you get the result you see in the browser. Needless to say, the client was quite impressed with the result, and I think it is another example of the flexibility APEX has to offer being in a web browser!

If you'd like to know more on this application and you're at the Oracle Open World this year, vote for this presentation on Oracle Mix by clicking HERE

APEX rocks --> APEX workshop @ Logica Arnhem

Well, yesterday I hosted my first APEX workshop from a teaching perspective, and I must say it is a whole different experience then being the attendee. Luckily Roel Hartman (we hosted the workshop together) and I had prepared the workshop properly.

First off, I gave a presentation about APEX. My focus was especially set on "seeing the point" about what APEX is, does and what it is intended to do. This combined with a healthy summary of features and architectures made the presentation quite long (about 50 min) but it was the minimal version, because I wanted to put so much fun stuff in that APEX has to offer. I just couldn't decide what to scrap!
I must say, the presentation went as expected, and the reaction afterwards were very positive, so thanks for the compliments.

Next up, was the workshop. We created a beginners workshop, for people who had never before worked with the magic of APEX. There was little or no experience with HTML_DB or APEX in the room among the attendees, so I was a bit nervous about the complexity of the workshop.

Apparently I forgot how easy it was to start working with APEX, because people were flying through the workshop! Luckily, somewhere in the middle they reached some more complex material, which provided the attendees with some more challenges.
The feedback we received on the workshop was again, very positive.

Finally, after some quick&clean application building, Roel gave his presentation about the ODTUG'08 Kaleidoscope where he attended earlier this month. A lot of cool new features / directions came to light, creating a bit of a buzz followed by a good discussion among the attendees.

Yes it was happening, the healthy addiction of APEX found some more hosts once more!!

This workshop created a lot of enthousiasm among the attendees, and I think almost everyone wanted a follow up workshop with more details. This is (again) a clear case of how low the threshold is that APEX has when people are just starting to create some application with it.

In case you haven't noticed yet, APEX rocks!