How Your Oracle DBA or Programmer Can Improve Your Argos Reporting

1
How Your Oracle DBA or Programmer Can Improve
Your Argos Reporting!

• Presented By
• Bruce Knox
• University of Arkansas
• Division of Agriculture
• Cooperative Extension Service

2

• Please remember to silence your
• cell phones.

3
Argos provides THE missing Banner Reporting Tool
4
Even with Argos, there remains a need for further
simplification
5
Even with Argos, there remains a need for further
simplification

• Current Banner Record Sets

6
Even with Argos, there remains a need for further
simplification

• Current Banner Record Sets
• Flattened Banner Data

7
Even with Argos, there remains a need for further
simplification

• Current Banner Record Sets
• Flattened Banner Data
• Using Desktop Data with Banner

8
Views, Views, and Banner Views
9
Views, Views, and Banner Views

• To Deal with Effective Dated Records

10
Views, Views, and Banner Views

• To Deal with Effective Dated Records
• To Flatten The Database

11
Views, Views, and Banner Views

• To Deal with Effective Dated Records
• To Flatten The Database
• Using Existing Banner Provided Views

12
Effective Dated Records
13
Effective Dated Records

• The 1 Bane of Ad Hoc Banner Reporting

14
Effective Dated Records

• 99 of all Ad Hoc Reports are for Current Banner
  Records

15
Effective Dated Records

• 99 of all Ad Hoc Reports are for Current
  Records
• Oracle Views can Deliver them without the need
  for Additional Selection Criteria

16
Effective Dated Records

• 99 of all Ad Hoc Reports are for Current
  Records
• Oracle Views can Deliver them without the need
  for Additional Selection Criteria
• Just JOIN the Views and the Query is Done!

17
(No Transcript)
18
(No Transcript)
19
(No Transcript)
20
What Was Hidden Here?
21
What Was Hidden Here?

• FROM FTVORGN
• WHERE
• TRUNC(FTVORGN_EFF_DATE)
• AND (FTVORGN_NCHG_DATE SYSDATE OR
  FTVORGN_NCHG_DATE IS NULL)
• AND (FTVORGN_TERM_DATE SYSDATE OR
  FTVORGN_TERM_DATE IS NULL)
• AND FTVORGN_STATUS_IND 'A'

22
Thousands of Related Tables
23
Thousands of Related Tables

• The 2 Bane of Ad Hoc Banner Reporting

24
Thousands of Related Tables

• The 2 Bane of Ad Hoc Banner Reporting
• Oracle Views Can Flatten the Database

25
Oracle Views
26
(No Transcript)
27
Oracle Views
28
Oracle Views
29
(No Transcript)
30
(No Transcript)
31
Oracle Views
32
From the Users Point of View
33
(No Transcript)
34
(No Transcript)
35
(No Transcript)
36
Oracle Views
37
Banner Views
38
Banner Views

• Banner Actually Includes Useful Views

39
Banner Views

• PEVEMPL Employee Information View

40
Banner Views

• PEVEMPL Employee Information View
• A Commonly Used View

41
Banner Views

• PEVEMPL Employee Information View
• A Commonly Used View
• Flattens 16 Tables

42
Banner Views

• PEVEMPL Employee Information View
• A Commonly Used View
• Flattens 16 Tables
• Obscures the Source Column Names

43
(No Transcript)
44
(No Transcript)
45
Banner Views
46
Banner Views
47
Banner Views
48
Importing Desktop Data
49
Importing Desktop Data

• Oracle Operates Best with Data in Oracle

50
Importing Desktop Data

• Oracle Operates Best with Data in Oracle
• Upload Data Interactively with APEX

51
Importing Desktop Data

• Oracle Operates Best with Data in Oracle
• Upload Data Interactively with APEX
• Upload Using Batch Scripts

52
Oracle Application Express
53
Oracle Application Express
54
Oracle Application Express
55
Oracle Application Express
56
Oracle Application Express
57
Oracle Application Express
58
Oracle Application Express
59
Oracle Application Express
60
Oracle Application Express
61
Oracle Application Express
62
Oracle Application Express
63
Oracle Application Express
64
Oracle Application Express
65
Oracle Application Express
66
Oracle Application Express
67
Batch Scripts
68
Batch Scripts
69
SQLPlus Batch Script

• -- publication_approval.sql Test Script for
  SQLPlus External Table Loads
• -- SET ECHO OFF
• -- 01/29/08 bknox Created
• --this script named publication_approval.sql is
  to be run by a matching publication_approval.bat
  for the Application.
• --If your file to load is publication_approval.c
  sv
• --then filename1 'D\orcl_ext\Application\publi
  cation_approval.log'
• SET FEEDBACK OFF
• SET TIMING OFF
• COLUMN Variable HEADING "Variable" FORMAT A16
• COLUMN Value HEADING "Value" FORMAT A30
• HOST DEL D\orcl_ext\Scripts\publication_approval_
  run.log
• -- ONCE THE TESTING IS DONE HOST DEL
  D\orcl_ext\Publications\publication_approval.log

70
SQLPlus Batch Script

• SET FEEDBACK ON
• TRUNCATE TABLE pub_approval
• -- TRUNCATE TABLE is the preferred way to empty
  the Table, but only works when run by the Table
  Owner.
• --DELETE FROM pub_approval
• INSERT INTO pub_approval
• ( FUND,
• ORGN,
• PIDM )
• SELECT
• FUND,
• ORGN,
• PIDM
• FROM pub_approval_ext
• SET FEEDBACK OFF

71
Windows Script

• REM Windows Batch Script for running
  publication_approval.sql
• REM which loads Internal
  Table from External Table.
• D
• cd oracle_ext
• cd Scripts
• DEL D\ORACLE_EXT\Publications\publication_approva
  l.log
• DEL D\ORACLE_EXT\Publications\publication_approva
  l.bad
• DEL D\ORACLE_EXT\Publications\publication_approva
  l.dsc
• sqlplus pubsid/pwd_at_APPS _at_D\oracle_ext\Scripts\pub
  lication_approval.sql
• EXIT

72
Bonus Report Template
73
Bonus Report Template
74
Conclusions

• Use Oracle Views to Simplify Banner
• Use Banner Views Whenever Available
• Use Batch Scripts to Upload Spreadsheets

75
Questions?

• Bruce Knox bknox _at_t uaex.edu
• http//www.uaex.edu/bknox
• http//www.uaex.edu/bknox/BannerArgos.htm
• University of Arkansas Division of
  Agriculture Cooperative Extension Service

76
Contact Information

• Bruce Knox bknox _at_t uaex.edu
• http//www.uaex.edu/bknox
• http//www.uaex.edu/bknox/BannerArgos.htm
• University of Arkansas Division of
  Agriculture Cooperative Extension Service

77
Argos Resources

• Training classes http//www.evisions.com/calendar
• Documentation and videos
  http//www.evisions.com/support/argos
• Argos Listserve
  http//www.evisions.com/community
• Argos Coop http//datablocks.evisi
  ons.com
• Evisions HelpDesk
  http//helpdesk.evisions.com