1 / 42

Saving $200,000 with Application Express

Saving $200,000 with Application Express. A presentation for ODTUG Kaleidoscope 2006 June 21, 2006 Bill Holtzman National Air Traffic Controllers Association. Air Traffic Controllers. Most work for the Federal Aviation Administration (FAA), part of the US Government

griffith-newton
Download Presentation

Saving $200,000 with Application Express

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Saving $200,000 with Application Express A presentation for ODTUG Kaleidoscope 2006 June 21, 2006 Bill Holtzman National Air Traffic Controllers Association

  2. Air Traffic Controllers • Most work for the Federal Aviation Administration (FAA), part of the US Government • National Air Traffic Controllers Association (NATCA) is the exclusive and official bargaining representative of air traffic controllers

  3. NATCA • 15,000 member organization • 600 official representatives • 400 offices across US • $20 million budget • High volume paperwork filings • No internal network • No programming staff • One IT employee • Headquartered in Washington, DC

  4. My IT experience • Fortran, Drexel University, 1985 • 3 static HTML web sites, 1997-2002 • Several MS Access apps, 2002-2003 • 7 one week classes at Learning Tree Int’l, 2003-04 • 8i, 9i Intros, PLSQL, SQL Queries, App Dev • DBA, Backup & Recovery, Tuning

  5. Grievances From the 1998 Collective Bargaining Agreement: Art. 9, Sec. 1. A grievance shall be defined as any complaint: • by any employee concerning any matter relating to the employment of the employee; • by the Union concerning any matter relating to the employment of any unit employee; or • by a unit employee or either Party concerning any claimed violation, misinterpretation, or misapplication of any law, rule or regulation affecting conditions of employment as provided in the Civil Service Reform Act of 1978 or this Agreement.

  6. Sample grievance

  7. System requirements • Secure, internet-based • Multiple access levels • Event-based processing • Timeline tracking • Document storage • Search • Accelerated data entry • Online reference library • E-mail notifications • Much more!

  8. Grievance listing A typical listing from the application. From this listing users can access all information associated with each grievance and add new information. The light browntext are links to other pages or javascript.

  9. SQL report region: Definition Note: The image has been altered and the SQL simplified for this presentation.

  10. SQL report region: Attributes

  11. Automated report link The link for the column is created below. An image link:

  12. HTML result (Automated link) This HTML is generated by the ApEx engine for the column when the page is rendered. The image is the browser result. <td class="alt" align="CENTER" ><a href="f?p=104:27:11611713660565463579::NO:: P27_GRID,P27_RETURN_PAGE:5360%2C32&cs=339E2700710A71397BE8B4014F66659CE" onMouseOver="return window.status='View or add attachments to this grievance'" onMouseOut="return window.status=''"><span style="padding:16px">4</span></a></td> Note: %2C is a URL comma URL syntax f?p=App:Page:Session:Request:Debug:ClearCache:itemNames:itemValues:PrinterFriendly

  13. Grievance listing The NATCA/FAA column is more difficult to generate. The light browntext is a link but the black text is not.

  14. Manual report link This code is used to generate the values in the column. select '<a href="' || htmldb_util.prepare_URL ('f?p=&APP_ID.:8:' || :APP_SESSION || ':::8:P8_DUP_GRID,P8_RETURN_PAGE,P8_ARTICLE:' || g.grid || ',32,' || g.article) || '">' || g.natca || '</a> <br><span style="font-size:8pt">' || g.faanum || '</span>' "NATCA2" from grievance g

  15. SQL report region: Definition Note: The image has been altered and the SQL simplified for this presentation.

  16. HTML result (Manual link) This HTML is generated by the ApEx engine for the column when the page is rendered. The image is the browser result. <td class="alt" align="CENTER" > <a href="f?p=104:8:11611713660565463579:::8: P8_DUP_GRID,P8_RETURN_PAGE,P8_ARTICLE:5380%2C32%2C1&cs=3AFB9E7AE3C788204B13372F5C05793B7" onMouseOver="return window.status='Duplicate this grievance'" onMouseOut="return window.status=''"> 06-ZDC-13</a> <br><span style="font-size:8pt">NC-AEA-ZDC-00120-2</span></td>

  17. Manual display conditions The column shows different elements depending on date data. select decode(g.status_id, 1, decode(g.date_sub_2, null, trunc(g.u_action_2) - trunc(sysdate) || '&nbsp' || case when (g.u_action_2 - sysdate) > 7 then '<img src="#FLOW_IMAGES#greenN.gif" alt="NATCA must submit this by ' || to_char(g.u_action_2, 'MM/DD/YY') || '">' when (g.u_action_2 - sysdate) > 3 then '<img src="#FLOW_IMAGES#yellowN.gif" alt="NATCA must submit this by ' || to_char(g.u_action_2, 'MM/DD/YY') || '">' when (g.u_action_2 - sysdate) > 0 then '<img src="#FLOW_IMAGES#redN.gif" alt="NATCA must submit this by ' || to_char(g.u_action_2, 'MM/DD/YY') || '">' when g.u_action_2 is null and date_rec_1 is null then '<img src="#FLOW_IMAGES#greenN.gif" alt="FAA has not replied at the E1 level">' else '<img src="#FLOW_IMAGES#past.gif" border="0" alt="NATCA failed to submit this by ' || to_char(g.u_action_2, 'MM/DD/YY') || '">' end, to_char(g.date_sub_2, 'MM/DD/YY')), 'Closed') "DATE_SUB" from grievance g

  18. HTML result (Manual conditions) <td class="alt" align="CENTER" >3&nbsp <img src="wwv_flow_file_mgr.get_file?p_security_group_id=19526826517643043&p_flow_id=104&p_fname=redN.gif" alt="NATCA must submit this by 05/27/06"> </td>

  19. Session state protection Session state protection is set by a wizard.

  20. Session state: Checksum

  21. Session state: Violation Tampering with values in the URL produces this error message.

  22. Upload/download Users upload and download documents associated with each grievance. The process is analogous to a legal case.

  23. Upload/download: Custom tables The custom tables are tied to individual grievances by the primary key GRID.

  24. Upload to custom table: Page process

  25. Upload: Javascript validation Javascript restricts the length of the file name. Application Express will not accept more than 78 characters.

  26. Upload: Server (ApEx) validation A validation restricts the size of uploads.

  27. Download from custom table: Report The SQL report region lists all stored files associated with the grievance whose primary key (GRID) is passed into :P27_GRID.

  28. Download from custom table: Link An automated column link uses the DOWNLOAD_MY_FILE procedure to make the File Name column a download link. Note: DOWNLOAD_MY_FILE is available on OTN

  29. PLSQL region: Sample code The following PLSQL code is used to create a form letter: if p_letterhead = 1 then msg := '<table width="90%" border="0"> <tr><td width="50%" align="center"><img src = "#WORKSPACE_IMAGES#natca.gif"></td> <td><i><font size="4"> NATCA ‘ || p_facid || '</font></i><br><font size="3">‘ || p_fac_address || '</font> </td></tr></table><br><br>'; else msg := '<br><br><br><br><br><br><br><br><br>'; end if; msg := msg || to_char(p_date_sub, 'FMMonth dd, yyyy') || '<br><br>' || p_address || '<br><br>Grievance Number ' || nvl(gr_cursor.faanum, '______________________') || '<br><br><b>Re: Grievance Regarding ' || gr_cursor.topic || '</b><br><br>Dear ' || p_dear || ',<br><br>' || p_preamble || '<br><br><b>Name of Grievant: </b>' || gr_cursor.grievant || '<br><b>Name of Representative: </b>' || gr_cursor.rep || '<br><b>Date of Violation: </b>' || to_char(gr_cursor.violation_date, 'FMMonth dd, yyyy') || '<br><b>Request Oral Presentation: </b>' || p_oral_text; htp.p(msg);

  30. PSQL region: Display result The form letter is an HTML file that can be printed.

  31. PLSQL region: CLOB data This code is used to push CLOB text (gr_cursor.nature) onto the HTML page. Since this page may also be called from the Search page, code is included to highlight search words. for j in 1..trunc(DBMS_LOB.GETLENGTH(gr_cursor.nature)/3000) + 1 loop DBMS_LOB.READ(gr_cursor.nature, p_text_amt, p_text_pos, p_nature_sub); msg := replace(p_nature_sub, chr(10), '<br>'); if :F168_SEARCH is not null and :P9_HIGHLIGHT is not null then msg := replace(replace(replace(msg, :F168_SEARCH, '<SPAN style="background-color:yellow">' || :F168_SEARCH || '</span>'), initcap(:F168_SEARCH), '<SPAN style="background-color:yellow">' || initcap(:F168_SEARCH) || '</span>'), upper(:F168_SEARCH), '<SPAN style="background-color:yellow">' || upper(:F168_SEARCH) || '</span>'); end if; htp.p(msg); p_text_pos := p_text_pos + 3000; end loop;

  32. PLSQL region: Highlight

  33. Automatic row processing Row processing can be handled automatically by the ApEx engine. This provides optimistic locking behind the scenes without input from the developer.

  34. Manual row processing :P8_CHECKSUM is calculated when the page is rendered. If it changes, the update does not execute. for c1 in (select * from grievance where grid = :P8_GRID) loop current_state := utl_raw.cast_to_raw(dbms_obfuscation_toolkit.md5(input_string => c1.FAANUM||c1.GRIEVANT||c1.REP||c1.TOPIC||c1.ORAL)); end loop; if current_state = :P8_CHECKSUM then select empid into p_empid from gr_emp where upper(username) = :APP_USER; case :P8_GR_STATUS when 1 then update grievance set faanum = :P8_FAANUM, rep = :P8_REP, topic = :P8_TOPIC, oral = :P8_ORAL where grid = :P8_GRID, userid = p_empid; :P8_RETURN_PAGE := 18; when 2 then update grievance set faanum = :P8_FAANUM, rep = :P8_REP, topic = :P8_TOPIC, oral = :P8_ORAL where grid = :P8_GRID, userid = p_empid; :P8_RETURN_PAGE := 32; end case; else :P8_RETURN_PAGE := 39; end if;

  35. Optimistic locking error When the checksums do not agree, conditional processing prevents the update and conditional branching takes the user to this page.

  36. Javascript Javascript greatly enhances the functionality of the application and the experience of the user. Javascript can be included on individual pages or on a template.

  37. Javascript: User tools Sample javascript as it would appear in the page attributes is shown here. The first function opens a new window and the second populates a field based on a drop-down menu selection.

  38. Javascript: onChange select

  39. Javascript: Client validation Since most browsers have a maximum content size of 32K, a graceful error message is needed should the user exceed that. This javascript provides a pop-up and halts processing.

  40. Calling javascript From a page button: Manually in an SQL Report Region (with Session State Protection): select '<a href="javascript:myPopUp(''' || htmldb_util.prepare_URL('f?p=&APP_ID.:9:' || :APP_SESSION || '::::P9_GRID:' || g.GRID) || ''')">' || g.topic || '</a>' "Topic“ from grievance g

  41. Application Express skill set

  42. Thank you! For more information: Bill Holtzman bholtzman@natca.net 703-403-0139

More Related