1 / 28

ApEx PLSQL Report Regions and Apps that Build Themselves

ApEx PLSQL Report Regions and Apps that Build Themselves. Bill Holtzman and Steve Schreck National Air Traffic Controllers Association. NATCA. National Air Traffic Controllers Association Federal Aviation Administration employees Mountains of regulations and business rules

lavender
Download Presentation

ApEx PLSQL Report Regions and Apps that Build Themselves

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. ApEx PLSQL Report Regions and Apps that Build Themselves Bill Holtzman and Steve Schreck National Air Traffic Controllers Association

  2. NATCA National Air Traffic Controllers Association • Federal Aviation Administration employees • Mountains of regulations and business rules • 400 US facilities • 15,000 members • 1000 Reps 2

  3. Air Traffic Control Display 3

  4. All US Flights 4

  5. Major Apps Grievance Tracking: GATS • PLSQL generated SQL report regions • Survey Tool: Survey Junkie • App creates new pages, items, etc. 5

  6. Participate in the Demo http://demo.natca.net

  7. Grievance Tracking 7

  8. Manual Column Link select '<a href="' || apex_util.prepare_URL('f?p= &APP_ID.:8:' || :APP_SESSION || ':::8: P8_DUP_GRID,P8_RET_PAGE:' || g.grid || ',32') || '">' || g.natca || '</a><br /><span style="font-size:8pt">' || g.faanum || '</span>' "NATCA/FAA" from grievance g target URL in blue –HTML link in red 8

  9. Manual Link with Javascript select '<a href= "javascript:myPopUp(''' || apex_util.prepare_URL( 'f?p=&APP_ID.:9:' || :APP_SESSION || '::::P9_GRID:' || g.GRID)|| ''')">' || g.topic || '</a>' “Grievance Regarding” from grievance g 9

  10. Composite Data Column 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">' when (g.u_action_2 - sysdate) > 3 then '<img src = "#FLOW_IMAGES#yellowN.gif">' when (g.u_action_2 - sysdate) > 0 then '<img src = "#FLOW_IMAGES#redN.gif">' else '<img src="#FLOW_IMAGES#past.gif">' end, to_char(g.date_sub_2, 'MM/DD/YY')),'Closed') "DATE_SUB" from grievance g 10

  11. Sorting Issue 11

  12. WHERE Clauses where g.gr_status = 2 and g.status_id = p.id and ((g.faanum like '%'||:P32_FAANUM||'%' and :P32_FAANUM is not null) or :P32_FAANUM is null) and ((:P32_FACILITY_ID != 0 and g.facility_id = :P32_FACILITY_ID) or (:P32_FACILITY_ID = 0 and (g.facility_id in (select id from gr_facility_lookup where region_id = (select y.region_id from gr_facility_lookup y, gr_emp z where upper(z.username) = :APP_USER and z.facility_id = y.id)) or g.facility_id = 3))) and (g.status_id = 1 or g.close_date > sysdate - :P32_DAYS) and ((g.date_sub_2 is not null and :P32_NF = 1) or (g.date_sub_2 is null and :P32_NF = 2) or (:P32_NF = 0)) and g.status_id != 2 and (:P32_REP is null or lower(g.rep) like '%' || lower(:P32_REP) || '%') and (:P32_NATCA is null or upper(:P32_NATCA) = g.natca) and (:P32_GRIEVANT is null or upper(g.grievant) like '%'||upper(:P32_GRIEVANT)||'%') and (:P32_TOPIC is null or lower(g.topic) like '%' || lower(:P32_TOPIC) || '%') order by g.status_id, trunc(g.reply_by_2), trunc(g.date_sub_2) nulls last, trunc(g.u_action_2) nulls last, trunc(g.date_rec_1) nulls last, trunc(g.date_sub_1) nulls last, substr(g.natca,4,3), g.natcasub 12

  13. Converting to PLSQL declare p_sql varchar2(32767); begin p_sql := q'! select * from grievance where $P7_SHOW = 1 !'; return replace(p_sql, '$', ':'); end; 13

  14. Stripping WHERE Clauses declare p_sql varchar2(32767); begin p_sql := q'! select g.grid, !'; p_sql := p_sql || q'! '<a href="javascript$myPopUp(''f?p=&APP_ID.$9$' || $APP_SESSION || '$$$$P9_GRID$' || g.grid || ''')">' || g.topic || '</a>' || gr_groupid(g.grid) "Topic“ !'; p_sql := p_sql || q'! from grievance g, gr_status_lookup p, gr_bu b where g.gr_status = 3 and g.status_id = p.id and g.bu_id = b.id (+) !'; if :P35_FAANUM is not null then p_sql := p_sql || q'! and lower(g.faanum) like '%' || lower($P35_FAANUM) || '%' !'; end if; return replace(p_sql,'$',':'); end; No user text = no where clause! 14

  15. Complex Sorting w/PLSQL declare p_sql varchar2(32767); begin p_sql := q'! select g.GRID, !'; p_sql := p_sql || q'! '<a href="javascript$myPopUp(''f?p=&APP_ID.$9$' || $APP_SESSION || '$$$$P9_GRID$' || g.GRID || ''')" &F168_PRINT.>' || g.topic || '</a>' || gr_groupid(g.grid) "Topic" !'; p_sql := p_sql || q'! from grievance g, gr_status_lookup p, gr_bu b where g.gr_status = 3 and g.status_id = p.id and g.bu_id = b.id (+) !'; case when :P35_SORT = 1 then p_sql := p_sql || q'! order by trunc(g.reply_by_3), trunc(g.date_sub_3) nulls last !'; when :P35_SORT = 2 then p_sql := p_sql || q'! order by trunc(g.date_sub_3), trunc(g.u_action_3) nulls last !'; else null; end case; return replace(p_sql,'$',':'); end; 15

  16. Result 16

  17. Database-driven Javascript declare p_java varchar2(4000); cursor c1 is select bu_id, bplate from gr_bu; begin p_java := 'function insertBP(p_region_id) { var p_bu_id = document.getElementById("P8_BU_ID").value;'; for a1 in c1 loop p_java := p_java || chr(10) || 'if (p_bu_id == ' || a1.bu_id || ')' || chr(10) || 'document.getElementById("P8_BPLATE").value = "' || a1.bplate || '";'; end loop; p_java := p_java || chr(10) || '}'; :F168_BPLATE_JAVA := p_java; end;

  18. Apps that Build Themselves 18

  19. Subscription Tools 19

  20. In-house Advantages • Direct integration with internal database • Information stays on your server • App can be customized for future needs • No reliance on outside support 20

  21. Concept Development • Individual survey construction? • Substitute user-supplied questions into existing item labels and user-supplied answers into Select Lists? 21

  22. Export File Components wwv_flow_api.create_page_item( p_id =>7422425059996976 + wwv_flow_api.g_id_offset, p_flow_id => wwv_flow.g_flow_id, p_flow_step_id => 1, p_name =>'P1_DEMO', p_display_as => 'RADIOGROUP', p_item_sequence => 10, p_item_plug_id => 7401108705976868 +wwv_flow_api.g_id_offset, p_begin_on_new_line => 'YES', p_begin_on_new_field => 'YES', p_colspan => 1, p_rowspan => 1, -- etc.) wwv_flow_id.next_val 22

  23. The Question Can we use ApEx’ own export code to create ApEx objects from within an app? 23

  24. Concept Demo 24

  25. Online Demo

  26. Caveats • Use of wwv_flow_api not specifically supported by Oracle • Do not over-write your app in PROD! • The app cannot in some cases be imported successfully.

  27. For more information: Bill Holtzman bholtzman@natca.net 703-403-0139 Steve Schreck stlsjschreck@natca.net 636-399-4549 27

More Related