670 likes | 1.04k Vues
Argos - Moving into the Community Presented by: Bruce Knox University of Arkansas Division of Agriculture, Cooperative Extension Service BAS S274 October 13, 2008 Introduction: Purpose and Benefits of this Presentation Purpose: Discuss the Argos User Community
E N D
Argos - Moving into the Community Presented by: Bruce Knox University of Arkansas Division of Agriculture, Cooperative Extension Service BAS S274 October 13, 2008
Introduction: Purpose and Benefits of this Presentation Purpose: • Discuss the Argos User Community • Converting from MS Access to Argos Benefits: • Free Software • Conversion Tips • Tools for Ad Hoc Banner Reporting
Our Agenda • What’s an Argos? • Trying the Product • The Argos Community • Converting from MS Access • Banner Record Selection Criteria
What’s an Argos? • Argos is a Web Based • Evisions • Ad Hoc Reporting Tool • Designed for Banner
A.R.G.O.S. Ad hoc Report Generation and Output Solution • Secure • Easy to Use • Fast
What’s an Argos • MS Access++ Built for the SQL Database
Get a password Trying the Product: Get a Password
Trying the Product: DBA and SysAdmin • You will need IT for early parts of the Server Install
the Argos Sample Datablocks for SCT Banner Trying the Product: Download and install
Community is a big part of Argos Sharing in Argos’ secured repository is encouraged. The Argos Community
Converting from MS Access • We have been using MS Access with Banner for 8 years • How to move from our existing MS Access?
Converting from MS Access • Build Datablocks via the Query Design GUI?
Converting from MS Access • Enter the Oracle Code?
Converting from MS Access • Either way will work for you, but • you still need to know the Banner Record Selection Criteria
Determining which Banner Tables are actually used A Handy Find Query Banner Record Selection Criteria
Banner Record Selection Criteria • Determining which Banner Tables are Actually Used • A Handy Find Query
Banner Record Selection Criteria: Tables Actually Used • Collect the ones that look like Banner Tables into a file • argos_tables.txt
Banner Record Selection Criteria: Tables Actually Used • argos_tables.txt • containing: @table_to_argos FABBKTP @table_to_argos FABCHKA @table_to_argos FABCHKS @table_to_argos FABINCK @table_to_argos FABINVH @table_to_argos FARDIRD @table_to_argos FARINTX … @table_to_argos TURVERS
Banner Record Selection Criteria: Tables Actually Used • Then in SQL*Plus: 10:32:39 BKNOX: PROD> START argostables.txt • This runs table_to_argos.sql for each Table in the file. • The script concatenates each result into a single text file containing the Table information required for constructing Queries or determining Record Selection Criteria.
-- **FTVORGN** Organization Validation Table SELECT -- Created from TABLE FTVORGN Organization Validation Table FTVORGN_COAS_CODE, FTVORGN_ORGN_CODE, TRUNC(FTVORGN_EFF_DATE) FTVORGN_EFF_DATE, TRUNC(FTVORGN_ACTIVITY_DATE) FTVORGN_ACTIVITY_DATE, FTVORGN_USER_ID, TRUNC(FTVORGN_NCHG_DATE) FTVORGN_NCHG_DATE, TRUNC(FTVORGN_TERM_DATE) FTVORGN_TERM_DATE, FTVORGN_TITLE, FTVORGN_STATUS_IND, FTVORGN_ORGN_CODE_PRED, FTVORGN_FUND_CODE_DEF, FTVORGN_PROG_CODE_DEF, FTVORGN_ACTV_CODE_DEF, FTVORGN_LOCN_CODE_DEF, FTVORGN_DATA_ENTRY_IND, FTVORGN_FMGR_CODE_PIDM, FTVORGN_ENCB_POLICY_IND, FTVORGN_ORGN_CODE_NSF, FTVORGN_HIERARCHY_TABLE_IND, FTVORGN_ALT_POOL_IND FROM FTVORGN WHERE TRUNC(FTVORGN_EFF_DATE) <= SYSDATE AND (FTVORGN_NCHG_DATE > TRUNC(SYSDATE) OR FTVORGN_NCHG_DATE IS NULL) AND (FTVORGN_TERM_DATE > TRUNC(SYSDATE) OR FTVORGN_TERM_DATE IS NULL) … Banner Record Selection Criteria: Here is a snippet
-- AND FTVORGN_STATUS_IND = '?' -- AND FTVORGN_DATA_ENTRY_IND = '?' -- AND FTVORGN_ENCB_POLICY_IND = '?' -- AND FTVORGN_HIERARCHY_TABLE_IND = '?' -- AND FTVORGN_ALT_POOL_IND = '?' -- AND FTVORGN_ACTIVITY_DATE > TO_DATE('06/30/2006 00:00:00','MM/DD/YYYY HH24:MI:SS') -- AND TRUNC(FTVORGN_ACTIVITY_DATE) >= TO_DATE('01/26/2007 00:00:00','MM/DD/YYYY HH24:MI:SS') -- AND TRUNC(FTVORGN_ACTIVITY_DATE) = TO_DATE('01/26/2007 00:00:00','MM/DD/YYYY HH24:MI:SS') -- AND TRUNC(FTVORGN_ACTIVITY_DATE) < TO_DATE('01/26/2007 00:00:00','MM/DD/YYYY HH24:MI:SS') -- AND TRUNC(FTVORGN_ACTIVITY_DATE) BETWEEN TO_DATE('07/01/2006 00:00:00','MM/DD/YYYY HH24:MI:SS') AND TO_DATE('06/30/2007 23:59:59','MM/DD/YYYY HH24:MI:SS')' --FTVORGN_COAS_CODE NOT NULLABLE is Probably Key or Indexed Field --FTVORGN_ORGN_CODE NOT NULLABLE is Probably Key or Indexed Field --FTVORGN_EFF_DATE NOT NULLABLE is Probably Key or Indexed Field --FTVORGN_ACTIVITY_DATE NOT NULLABLE is Probably Key or Indexed Field --FTVORGN_USER_ID NOT NULLABLE is Probably Key or Indexed Field --FTVORGN_NCHG_DATE NOT NULLABLE is Probably Key or Indexed Field --FTVORGN_TITLE NOT NULLABLE is Probably Key or Indexed Field --FTVORGN_STATUS_IND NOT NULLABLE is Probably Key or Indexed Field --FTVORGN_DATA_ENTRY_IND NOT NULLABLE is Probably Key or Indexed Field --ORDER BY FTVORGN_ORGN_CODE -- **FTVORGN** Organization Validation Table Banner Record Selection Criteria: Here is a snippet part2
Banner Record Selection Criteria: Reports • You will need a sample of the Report
Banner Record Selection Criteria: Reports to Queries • Double-Click the Report Selector • The Dark Square within the Gray Square left of the Ruler Line. • Alternately, Right-Click and Select Properties, then • Select Data.
Banner Record Selection Criteria: Reports to Queries • Record Source is the MS Access Query behind the Report • Double-Click on the Ellipsis button and you have the Query
Banner Record Selection Criteria: Reports to Queries • MS Access Query behind the Report
Banner Record Selection Criteria: Reports to Queries • MS Access Query behind the Report
Banner Record Selection Criteria: Reports to Queries • MS Access Query behind the Report
SELECT IIf([FGBGENL_FUND_CODE]="11100","U", IIf([FGBGENL_FUND_CODE]="11200","U", IIf([FGBGENL_FUND_CODE]="14000","U", IIf([FGBGENL_FUND_CODE]="21110","U", IIf([FGBGENL_FUND_CODE]="21120","U", IIf([FGBGENL_FUND_CODE]="21160","U", IIf([FGBGENL_FUND_CODE] Between "13000" And "13199","U", IIf([FGBGENL_FUND_CODE] Between "13250" And "13999","U", IIf([FGBGENL_FUND_CODE] Between "22000" And "22999","U", IIf([FGBGENL_FUND_CODE] Between "24000" And "24999","U", IIf([FGBGENL_FUND_CODE] Between "26000" And "26999","U", IIf([FGBGENL_FUND_CODE] Between "29000" And "29999","U", IIf([FGBGENL_FUND_CODE]="27000","U", IIf([FGBGENL_FUND_CODE]>"30000","P","R")))))))))))))) AS [Group], [FGBGENL 04].FGBGENL_ACCT_CODE AS GLACCT, FTVACCT.FTVACCT_TITLE AS [ACCT TITLE], FTVACCT.FTVACCT_ATYP_CODE AS [ACCT TYPE], FTVATYP.FTVATYP_TITLE AS [ACCT TYPE TITLE], Sum([FGBGENL_SUM_PERIODIC_DR]-[FGBGENL_SUM_PERIODIC_CR]) AS Balance FROM (([FGBGENL 04] LEFT JOIN (FTVACCT LEFT JOIN FTVATYP ON FTVACCT.FTVACCT_ATYP_CODE = FTVATYP.FTVATYP_ATYP_CODE) ON [FGBGENL 04].FGBGENL_ACCT_CODE = FTVACCT.FTVACCT_ACCT_CODE) LEFT JOIN [FTVFUND any status] ON [FGBGENL 04].FGBGENL_FUND_CODE = [FTVFUND any status].FTVFUND_FUND_CODE) LEFT JOIN FTVFTYP ON [FTVFUND any status].FTVFUND_FTYP_CODE = FTVFTYP.FTVFTYP_FTYP_CODE WHERE ((([FGBGENL 04].FGBGENL_PERIOD)<=[Select a period (pp)]) AND (([FTVFUND any status].FTVFUND_FTYP_CODE) Not Like "BF")) GROUP BY IIf([FGBGENL_FUND_CODE]="11100","U",IIf([FGBGENL_FUND_CODE]="11200","U",IIf([FGBGENL_FUND_CODE]="14000","U",IIf([FGBGENL_FUND_CODE]="21110","U",IIf([FGBGENL_FUND_CODE]="21120","U",IIf([FGBGENL_FUND_CODE]="21160","U",IIf([FGBGENL_FUND_CODE] Between "13000" And "13199","U",IIf([FGBGENL_FUND_CODE] Between "13250" And "13999","U",IIf([FGBGENL_FUND_CODE] Between "22000" And "22999","U",IIf([FGBGENL_FUND_CODE] Between "24000" And "24999","U",IIf([FGBGENL_FUND_CODE] Between "26000" And "26999","U",IIf([FGBGENL_FUND_CODE] Between "29000" And "29999","U",IIf([FGBGENL_FUND_CODE]="27000","U",IIf([FGBGENL_FUND_CODE]>"30000","P","R")))))))))))))), [FGBGENL 04].FGBGENL_ACCT_CODE, FTVACCT.FTVACCT_TITLE, FTVACCT.FTVACCT_ATYP_CODE, FTVATYP.FTVATYP_TITLE HAVING (((Sum([FGBGENL_SUM_PERIODIC_DR]-[FGBGENL_SUM_PERIODIC_CR]))<>0)) ORDER BY IIf([FGBGENL_FUND_CODE]="11100","U",IIf([FGBGENL_FUND_CODE]="11200","U",IIf([FGBGENL_FUND_CODE]="14000","U",IIf([FGBGENL_FUND_CODE]="21110","U",IIf([FGBGENL_FUND_CODE]="21120","U",IIf([FGBGENL_FUND_CODE]="21160","U",IIf([FGBGENL_FUND_CODE] Between "13000" And "13199","U",IIf([FGBGENL_FUND_CODE] Between "13250" And "13999","U",IIf([FGBGENL_FUND_CODE] Between "22000" And "22999","U",IIf([FGBGENL_FUND_CODE] Between "24000" And "24999","U",IIf([FGBGENL_FUND_CODE] Between "26000" And "26999","U",IIf([FGBGENL_FUND_CODE] Between "29000" And "29999","U",IIf([FGBGENL_FUND_CODE]="27000","U",IIf([FGBGENL_FUND_CODE]>"30000","P","R")))))))))))))) DESC , [FGBGENL 04].FGBGENL_ACCT_CODE; Banner Record Selection Criteria: Reports to Queries
But, note that this code is MS Access SQL; Not, Oracle SQL. Group: IIf([FGBGENL_FUND_CODE]="11100","U", IIf([FGBGENL_FUND_CODE]="11200","U", IIf([FGBGENL_FUND_CODE]="14000","U", IIf([FGBGENL_FUND_CODE]="21110","U", IIf([FGBGENL_FUND_CODE]="21120","U", IIf([FGBGENL_FUND_CODE]="21160","U", IIf([FGBGENL_FUND_CODE] Between "13000" And "13199","U", IIf([FGBGENL_FUND_CODE] Between "13250" And "13999","U", IIf([FGBGENL_FUND_CODE] Between "22000" And "22999","U", IIf([FGBGENL_FUND_CODE] Between "24000" And "24999","U", IIf([FGBGENL_FUND_CODE] Between "26000" And "26999","U", IIf([FGBGENL_FUND_CODE] Between "29000" And "29999","U", IIf([FGBGENL_FUND_CODE]="27000","U", IIf([FGBGENL_FUND_CODE]>"30000","P", "R")))))))))))))) Banner Record Selection Criteria: Reports to Queries
CASE WHEN FGBGENL_FUND_CODE = '11100' THEN 'U' WHEN FGBGENL_FUND_CODE = '11200' THEN 'U' WHEN FGBGENL_FUND_CODE = '14000' THEN 'U' WHEN FGBGENL_FUND_CODE = '21110' THEN 'U' WHEN FGBGENL_FUND_CODE = '21120' THEN 'U' WHEN FGBGENL_FUND_CODE = '21160' THEN 'U' WHEN FGBGENL_FUND_CODE Between '13000' And '13199' THEN 'U' WHEN FGBGENL_FUND_CODE Between '13250' And '13999' THEN 'U' WHEN FGBGENL_FUND_CODE Between '22000' And '22999' THEN 'U' WHEN FGBGENL_FUND_CODE Between '24000' And '24999' THEN 'U' WHEN FGBGENL_FUND_CODE Between '26000' And '26999' THEN 'U' WHEN FGBGENL_FUND_CODE Between '29000' And '29999' THEN 'U' WHEN FGBGENL_FUND_CODE = '27000' THEN 'U' WHEN FGBGENL_FUND_CODE > '30000' THEN 'P' ELSE 'R' END AS FUNDGROUP Banner Record Selection Criteria: Reports to Queries
The FROM and WHERE need to lose the [ ] and Double Quotes. FROM (([FGBGENL] LEFT JOIN (FTVACCT INNER JOIN FTVATYP ON FTVACCT.FTVACCT_ATYP_CODE = FTVATYP.FTVATYP_ATYP_CODE) ON [FGBGENL].FGBGENL_ACCT_CODE = FTVACCT.FTVACCT_ACCT_CODE) LEFT JOIN [FTVFUND] ON [FGBGENL].FGBGENL_FUND_CODE = [FTVFUND].FTVFUND_FUND_CODE) INNER JOIN FTVFTYP ON [FTVFUND].FTVFUND_FTYP_CODE = FTVFTYP.FTVFTYP_FTYP_CODE WHERE ((([FGBGENL].FGBGENL_PERIOD)<=[Select a period (pp)]) AND (([FTVFUND].FTVFUND_FTYP_CODE) Not Like "BF")) FROM ((FGBGENL LEFT JOIN (FTVACCT INNER JOIN FTVATYP ON FTVACCT.FTVACCT_ATYP_CODE = FTVATYP.FTVATYP_ATYP_CODE) ON FGBGENL.FGBGENL_ACCT_CODE = FTVACCT.FTVACCT_ACCT_CODE) LEFT JOIN FTVFUND ON FGBGENL.FGBGENL_FUND_CODE = FTVFUND.FTVFUND_FUND_CODE) INNER JOIN FTVFTYP ON FTVFUND.FTVFUND_FTYP_CODE = FTVFTYP.FTVFTYP_FTYP_CODE WHERE FGBGENL.FGBGENL_PERIOD<=‘&pp‘ AND FTVFUND.FTVFUND_FTYP_CODE Not Like 'BF' Banner Record Selection Criteria: Reports to Queries