Saving 200 000 with Application Express A presentation

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 n n 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

NATCA n n n n n 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

My IT experience n n 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 n n 8 i, 9 i Intros, PLSQL, SQL Queries, App Dev DBA, Backup & Recovery, Tuning

Grievances From the 1998 Collective Bargaining Agreement: Art. 9, Sec. 1. A grievance shall be defined as any complaint: n by any employee concerning any matter relating to the employment of the employee; n by the Union concerning any matter relating to the employment of any unit employee; or n 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.

Sample grievance

System requirements n n n n n 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!

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 brown text are links to other pages or javascript.

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

SQL report region: Attributes

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

HTML result (Automated link) This HTML is generated by the Ap. Ex engine for the column when the page is rendered. The image is the browser result. 4 Note: %2 C is a URL comma URL syntax f? p=App: Page: Session: Request: Debug: Clear. Cache : item. Names: item. Values: Printer. Friendly

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

Manual report link This code is used to generate the values in the column. select '' || g. natca || ' ' || g. faanum || '' "NATCA 2" from grievance g

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

HTML result (Manual link) This HTML is generated by the Ap. Ex engine for the column when the page is rendered. The image is the browser result. 06 -ZDC-13 NC-AEA-ZDC-001202

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) || ' ' || case when (g. u_action_2 - sysdate) > 7 then '' when (g. u_action_2 - sysdate) > 3 then '' when (g. u_action_2 - sysdate) > 0 then '' when g. u_action_2 is null and date_rec_1 is null then '' else '' end, to_char(g. date_sub_2, 'MM/DD/YY')), 'Closed') "DATE_SUB" from grievance g

3  3  HTML result (Manual conditions) 3 

Session state protection is set by a wizard.

Session state: Checksum

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

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

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

Upload to custom table: Page process

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

Upload: Server (Ap. Ex) validation A validation restricts the size of uploads.

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

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

PLSQL region: Sample code The following PLSQL code is used to create a form letter: if p_letterhead = 1 then msg : = '

NATCA ‘ || p_facid || ' ‘ || p_fac_address || '

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

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), ' '); if : F 168_SEARCH is not null and : P 9_HIGHLIGHT is not null then msg : = replace(replace(msg, : F 168_SEARCH, '' || : F 168_SEARCH || ''), initcap(: F 168_SEARCH), '' || initcap(: F 168_SEARCH) || ''), upper(: F 168_SEARCH), '' || upper(: F 168_SEARCH) || ''); end if; htp. p(msg); p_text_pos : = p_text_pos + 3000; end loop;

PLSQL region: Highlight

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

Manual row processing : P 8_CHECKSUM is calculated when the page is rendered. If it changes, the update does not execute. for c 1 in (select * from grievance where grid = : P 8_GRID) loop current_state : = utl_raw. cast_to_raw(dbms_obfuscation_toolkit. md 5(input_string => c 1. FAANUM||c 1. GRIEVANT||c 1. REP||c 1. TOPIC||c 1. ORAL)); end loop; if current_state = : P 8_CHECKSUM then select empid into p_empid from gr_emp where upper(username) = : APP_USER; case : P 8_GR_STATUS when 1 then update grievance set faanum = : P 8_FAANUM, rep = : P 8_REP, topic = : P 8_TOPIC, oral = : P 8_ORAL where grid = : P 8_GRID, userid = p_empid; : P 8_RETURN_PAGE : = 18; when 2 then update grievance set faanum = : P 8_FAANUM, rep = : P 8_REP, topic = : P 8_TOPIC, oral = : P 8_ORAL where grid = : P 8_GRID, userid = p_empid; : P 8_RETURN_PAGE : = 32; end case; else : P 8_RETURN_PAGE : = 39; end if;

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

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.

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.

Javascript: on. Change select

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

Calling javascript From a page button: Manually in an SQL Report Region (with Session State Protection): select '' || g. topic || '' "Topic“ from grievance g

Application Express skill set

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