e~Print - Innovative Ways We Use It - PowerPoint PPT Presentation

e print innovative ways we use it n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
e~Print - Innovative Ways We Use It PowerPoint Presentation
Download Presentation
e~Print - Innovative Ways We Use It

play fullscreen
1 / 57
e~Print - Innovative Ways We Use It
135 Views
Download Presentation
tegan
Download Presentation

e~Print - Innovative Ways We Use It

- - - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript

  1. e~Print - Innovative Ways We Use It Presented by: Bruce Knox University of ArkansasDivision of AgricultureCooperative Extension Service April 24, 200610:00 am – 10:40 amFreedom Room ABUG 2006 - Harding April 24th Searcy, AR 1

  2. Introduction • Purpose: Automate Report Generation for e~Print • Benefits: Improved Reporting with Reduced Costs ABUG 2006 - Harding April 24th Searcy, AR 2

  3. Topics of Discussion/Agenda • Secure Distributed Reporting • Our First Efforts • Server Side Automation • A Month End Example • Monitoring the Automation ABUG 2006 - Harding April 24th Searcy, AR 3

  4. Secure Distributed Reporting • We needed a way to deliver timely reports to a geographically distributed management team. ABUG 2006 - Harding April 24th Searcy, AR 4

  5. We Have Faculty in Every County ABUG 2006 - Harding April 24th Searcy, AR 5

  6. e~Print Delivers Our Reports • A Secure Web Server • Page Security by Fund/Orgn (Banner Security) ABUG 2006 - Harding April 24th Searcy, AR 6

  7. Our First Efforts • At First, We Used Only MS Access Reports • Users Developed and Ran the Reports • WS-FTP Transferred the Report Files ABUG 2006 - Harding April 24th Searcy, AR 7

  8. This Was Great For IT • After Some Minor Setup, • We Handed It All to a Super User ABUG 2006 - Harding April 24th Searcy, AR 8

  9. Why We Replaced The Original Reports • Our Super User Created a Lot of Reports • More Reports Than She had Time to Run ABUG 2006 - Harding April 24th Searcy, AR 9

  10. Time For Server Side Automation • We Use Pass-Through Queries with MS Access • That Made Reverse Engineeringthe Reports Easy ABUG 2006 - Harding April 24th Searcy, AR 10

  11. Summary, Detail, Period End Reporting • YTD Summary Reports Run Every Hour • YTD Detail Reports Run Twice A Day • Summary, Detail, and Month End Each Evening ABUG 2006 - Harding April 24th Searcy, AR 11

  12. A Month End Example This is Typically all I see of this Application: An email Notice ABUG 2006 - Harding April 24th Searcy, AR 12

  13. A Month End Example This is Typically all I see of this Application: An email Notice ABUG 2006 - Harding April 24th Searcy, AR 13

  14. The Managers See Only Their Reports ABUG 2006 - Harding April 24th Searcy, AR 14

  15. Our Month End Reports Are Run Automatically ABUG 2006 - Harding April 24th Searcy, AR 15

  16. This is Done Using the Banner Calendar A cron run shell script runs a SQL*Plus script to check if the Month End Closed Today. SPOOL fspd_closed.run SELECT 'HOST fspd_run_closed_reports.shl', 'Closed: '||FTVFSPD_ACTIVITY_DATE||' for', 'FSPD', FTVFSPD_FSPD_CODE FSPD, 'FSYR', FTVFSYR_FSYR_CODE FSYR FROM FTVFSYR,FTVFSPD WHERE FTVFSPD_COAS_CODE = FTVFSYR_COAS_CODE AND FTVFSPD_FSYR_CODE = FTVFSYR_FSYR_CODE AND FTVFSPD_PRD_STATUS_IND = 'C' AND FTVFSPD_PRD_END_DATE < SYSDATE -- Keeps Closing Date in the Past AND TRUNC(FTVFSPD_ACTIVITY_DATE) = TRUNC(SYSDATE); SPOOL OFF START fspd_closed.run ABUG 2006 - Harding April 24th Searcy, AR 16

  17. Check if the Month End Closed Today AND FTVFSPD_PRD_STATUS_IND = 'C' AND FTVFSPD_PRD_END_DATE < SYSDATE -- Keeps Closing Date (and Time) in the Past AND TRUNC(FTVFSPD_ACTIVITY_DATE) = TRUNC(SYSDATE); ABUG 2006 - Harding April 24th Searcy, AR 17

  18. We LOG The CRON Job Tue Jan 17 21:32:00 CST 2006 fspd_closed.shl beginning fspd_closed.shl Control Month End Reporting Scripts Tue Jan 17 21:32:00 CST 2006 fspd_closed.shl ending18 (evening before the closing) ABUG 2006 - Harding April 24th Searcy, AR 18

  19. Then The Closing Wed Jan 18 21:32:00 CST 2006 fspd_closed.shl beginning fspd_closed.shl Control Month End Reporting Scripts HOST fspd_run_closed_reports.shl Closed: 18-JAN-2006 for FSPD 06 FSYR 06 Wed Jan 18 21:32:00 CST 2006 (evening of closing) ABUG 2006 - Harding April 24th Searcy, AR 19

  20. Finally, The Reports • fspd_run_closed_reports.shl beginning • Run The YTD Summary and The MTD Detail Reports for Month End • Wed Jan 18 21:32:00 CST 2006 • fspd_run_closed_reports.shl ending • Wed Jan 18 21:32:00 CST 2006 ABUG 2006 - Harding April 24th Searcy, AR 20

  21. The Drill Down Gives Each Month ABUG 2006 - Harding April 24th Searcy, AR 21

  22. Each Month End Report Stores Multiple Copies ABUG 2006 - Harding April 24th Searcy, AR 22

  23. Each Month End Report Stores Multiple Copies ABUG 2006 - Harding April 24th Searcy, AR 23

  24. YTD Summary ABUG 2006 - Harding April 24th Searcy, AR 24

  25. YTD Summary ABUG 2006 - Harding April 24th Searcy, AR 25

  26. YTD Summary ABUG 2006 - Harding April 24th Searcy, AR 26

  27. Run: Date-Time and Report Name ABUG 2006 - Harding April 24th Searcy, AR 27

  28. MTD Detail ABUG 2006 - Harding April 24th Searcy, AR 28

  29. MTD Detail ABUG 2006 - Harding April 24th Searcy, AR 29

  30. MTD Detail ABUG 2006 - Harding April 24th Searcy, AR 30

  31. e~Print – Defining Reports ABUG 2006 - Harding April 24th Searcy, AR 31

  32. Defining Reports ABUG 2006 - Harding April 24th Searcy, AR 32

  33. Defining Reports ABUG 2006 - Harding April 24th Searcy, AR 33

  34. Naming the Report for e~Print ABUG 2006 - Harding April 24th Searcy, AR 34

  35. Naming The Report for Users ABUG 2006 - Harding April 24th Searcy, AR 35

  36. Where is the Title on the Report? ABUG 2006 - Harding April 24th Searcy, AR 36

  37. Place the Date and Time on the Menus ABUG 2006 - Harding April 24th Searcy, AR 37

  38. What to do with the 1st Blank Page? ABUG 2006 - Harding April 24th Searcy, AR 38

  39. Page Security - ORGN ABUG 2006 - Harding April 24th Searcy, AR 39

  40. Page Security - FUND ABUG 2006 - Harding April 24th Searcy, AR 40

  41. Optional email Notification ABUG 2006 - Harding April 24th Searcy, AR 41

  42. The Shell Scripts – Let’s See How Simple • All Report Shell Scripts are Run as One Job • Each Report has a Shell Script ABUG 2006 - Harding April 24th Searcy, AR 42

  43. Report Shell Script Example– Part I Setup #!/bin/ksh # dbr01_cp.shl # Closed Period Reports Run 01: Orgn Summary Daily # Run orgsumm_dbr.sql to create extract, dbr01.pco to create report, # ftp the dbr01.lst report as 100gbsumm, then # ftp 100gbsumm.done to e~Print dbrd Repository print date print dbr01_cp.shl beginning PATH= ... export PATH export ORACLE_SID=PROD ORAENV_ASK=NO . /usr/lbin/oraenv ABUG 2006 - Harding April 24th Searcy, AR 43

  44. Report Shell Script - Part II Extract and Report # dbr01_cp.shl continued echo "START orgsumm_dbr.sql" #INSERT Extracted Records into Table DBR_01 run_pw_mgr.shl userid orgsumm_dbr.sql echo beginning dbr01.pco dbr01 echo dbr01.lst Report Complete ABUG 2006 - Harding April 24th Searcy, AR 44

  45. Report Shell Script – Part III Transfer to e~Print # put dbr01.lst e~Print Repository dbrd dbr01_cp.shl continued print ftp put dbr01.lst 100gbsumm echo > 100gbsumm.done ftp -n -i 170.94.62.000<<eof user dbrd pw ascii put dbr01.lst 100gbsumm put 100gbsumm.done 100gbsumm.done quit eof rm -f dbr01.lst rm -f 100gbsumm.done print date print dbr01_cp.shl ending # end of dbr01_cp.shl ABUG 2006 - Harding April 24th Searcy, AR 45

  46. Logging the Run • dbr01_cp.shl > dbr01_cp_cron.log 2>&1 • dbr01_cp_cron.log will contain any Run messages ABUG 2006 - Harding April 24th Searcy, AR 46

  47. Log Snippets - Part I Extract Wed Jan 18 21:32:01 CST 2006 dbr01_cp.shl beginning START orgsumm_dbr.sql FSPD 06 FSYR 06 Run Date 18-JAN-2006 orgsumm_dbr extract beginning 2987 rows created. Commit complete. orgsumm_dbr extract complete ABUG 2006 - Harding April 24th Searcy, AR 47

  48. Log Snippets - Part II Report and FTP beginning dbr01.pco dbr01 CONNECTED TO ORACLE. dbr01 completed dbr01.lst Report Complete ftp put dbr01.lst 100gbsumm Wed Jan 18 21:32:17 CST 2006 dbr01_cp.shl ending ABUG 2006 - Harding April 24th Searcy, AR 48

  49. Report Shell Scripts are Run as One Job #!/bin/ksh #fspd_run_closed_reports.shl Run The YTD Summary and The MTD Detail Reports for Month End # This .shl file is intended to be run ONLY by fspd_closed.shl/fspd_closed.sql print date print fspd_run_closed_reports.shl beginning echo Run The YTD Summary and The MTD Versions of Detail Reports for Month End PATH= ... export PATH export ORACLE_SID=PROD ORAENV_ASK=NO . /usr//ora # Set Values in Run_Dates Table was done in fspd_closed.sql run by fspd_closed.shl ABUG 2006 - Harding April 24th Searcy, AR 49

  50. Adding a New Report is Simple at -q b > fspd_closed_reports_at.log 2>&1 <<EOF dbr01_cp.shl > dbr01_cp_cron.log 2>&1 dbr02_cp.shl > dbr02_cp_cron.log 2>&1 dbr03_cp.shl > dbr03_cp_cron.log 2>&1 dbr04_cp.shl > dbr04_cp_cron.log 2>&1 dbr09_cp.shl > dbr09_cp_cron.log 2>&1 dbr05_cp.shl > dbr05_cp_cron.log 2>&1 dbr14_cp.shl > dbr14_cp_cron.log 2>&1 dbr13_cp.shl > dbr13_cp_cron.log 2>&1 dbr06m_cp.shl > dbr06m_cp_cron.log 2>&1 dbr07m_cp.shl > dbr07m_cp_cron.log 2>&1 dbr08m_cp.shl > dbr08m_cp_cron.log 2>&1 dbr10m_cp.shl > dbr10m_cp_cron.log 2>&1 dbr11m_cp.shl > dbr11m_cp_cron.log 2>&1 dbr12m_cp.shl > dbr12m_cp_cron.log 2>&1 dbr15m_cp.shl > dbr15m_cp_cron.log 2>&1 dbr16m_cp.shl > dbr16m_cp_cron.log 2>&1 dbr17m_cp.shl > dbr17m_cp_cron.log 2>&1 EOF ABUG 2006 - Harding April 24th Searcy, AR 50