2007 Pre-Summit Workshop
Download
1 / 27

MS Access Conversion to Argos - PowerPoint PPT Presentation


  • 690 Views
  • Uploaded on

2007 Pre-Summit Workshop & User Cooperative Tech Track Bruce Knox University of Arkansas Division of Agriculture Cooperative Extension Service. Argos Pre-Summit Tech Track. Morning: Tech Track Afternoon: Admin & Security

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'MS Access Conversion to Argos' - KeelyKia


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Slide1 l.jpg

2007 Pre-Summit Workshop

&

User Cooperative

Tech Track

Bruce Knox

University of Arkansas

Division of Agriculture

Cooperative Extension Service


Slide2 l.jpg

Argos Pre-Summit Tech Track

Morning: Tech Track

Afternoon: Admin & Security

Copyright 2002 Cision Studios Michelangelo Fubaloo


Slide3 l.jpg

Argos Pre-Summit Tech Track

How are you supporting your users?

What tools have you developed?

What works for you?

Got some code to share?


Slide4 l.jpg

Argos Pre-Summit Tech Track

Topics and Questions?

Convert the MS Access SQL to Argos

Create a CSV Import File for creating MAPS Users

Help specify the selection criteria for your Argos Query Design

What to do with MS Access SQL Pass-Through Queries

Help for creating Oracle Views for Banner Tables -- Current Records



Slide6 l.jpg

Some code to help convert the MS Access SQL to Argos (Oracle SQL).

This one is UNIX, tested on AIX.  access_to_argos.shlIt includes conversions for the most common MS Access Functions plus some hints on a few that need more complex code to convert.Automated Conversion includes:removes all [removes all  ]changes Double Quotes to Single QuotesInserts the CR, Carriage Returns or Newlines.  This is just to help visually format the code.  It makes the code more readable, IMO.   Iff            insert CR in front of IIf   FROM              CR after FROM   WHERE           CR after WHERE   HAVING           CR after HAVING


Slide7 l.jpg

Some code to help convert the MS Access SQL to Argos (Oracle SQL). -2

changes:   Chr(        to      CHAR(   Len(                  LENGTH(   Now()               SYSDATE   Nz(                   NVL(   UCase(            UPPER(   LCase(             LOWER(and since  I use Upper Case for Oracle Keywords:   Abs(       to       ABS(   LTrim(              LTRIM(   RTrim(             RTRIM(   Trim(                TRIM(   Round(             ROUND(


Slide8 l.jpg

Some code to help convert the MS Access SQL to Argos (Oracle SQL) - 3.

The following are too complex for sed and tr to convert, but here are some hints on making the changes:   Left(AnyString, n) SUBSTR(AnyString,1,n)   Right(AnyString, n) SUBSTR(AnyString,LENGTH(AnyString)-n+1,n)   IIf Can be replace with CASE or DECODE (use CASE if any IIf ... Between ... used)Warning:

One must be careful that the input contains no Lower Case Selection Criteria,

because the output is UPPER Case.

You open the MS Access Query in Design Mode.  Select View, then SQL View.  Copy the code and create a text file.  Then input it to the shell script.


Slide9 l.jpg

Some code to help convert the MS Access SQL to Argos (Oracle SQL) - 4.

cat access_code_in.txt | tr '[a-z]' '[A-Z]' > access_to_argos.txt

sed -e "s/\[//g" -e "s/\]//g" -e "s/\"/\'/g" -e "s/IIF/\\~IIf/g" -e "s/FROM/FROM\\~/g" -e "s/WHERE/WHERE\\~/g" -e "s/HAVING/HAVING\\~/g" -e "s/LEN(/LENGTH(/g" -e "s/NOW()/SYSDATE/g" -e "s/NZ(/NVL(/g" -e "s/UCASE(/UPPER(/g" -e "s/LCASE(/LOWER(/g" -e "s/ABS(/ABS(/g" -e "s/CHR(/CHAR(/g" -e "s/LTRIM(/LTRIM(/g" -e "s/RTRIM(/RTRIM(/g" -e "s/TRIM(/TRIM(/g" -e "s/ROUND(/ROUND(/g" access_to_argos.txt > access_to_argos_temp.sql

tr '~' '\n' < access_to_argos_temp.sql > access_to_argos.sql

http://www.uaex.edu/bknox/access_to_argos.shl



Slide11 l.jpg

Create a CSV Import File for creating MAPS Users SQL) - 4.

SQL*Plus script to create a CSV Import File for creating MAPS Users:maps_users.sql

To use this, one must have the Users already assigned to the Argos Security Class Object.  Which is a reasonable expectation, I think.Description is always Finance, that is easily changed.  (Determining Department assignments can be a difficult Banner issue.)

The password for the Users' first logon to MAPS will be their Banner User ID + the last four digits of their SSN

Be sure to check mark the "User must password change at next login" when Importing the file so that the User can sync their Banner and MAPS password. 

(Relates to using "Argos and Banner Security" option.)


Slide12 l.jpg

Create a CSV Import File for creating MAPS Users -2 SQL) - 4.

SELECT gurucls_userid Sort_Name,

'"MAPS","'||LOWER(gurucls_userid)||'","'||LOWER(gurucls_userid)||SUBSTR(spbpers_ssn,6,9)||'","Finance","'||TRIM(goremal_email_address)||'"'

FROM goremal, gurucls, spbpers, gobeacc

WHERE gurucls_class_code = 'ARGOS' -- Your Argos Security Class Object

AND goremal_pidm = spbpers_pidm

AND goremal_status_ind = 'A'

AND goremal_preferred_ind = 'Y'

AND gobeacc_pidm = goremal_pidm

AND gurucls_userid = gobeacc_username

ORDER BY Sort_Name;

http://www.uaex.edu/bknox/maps_users.sql



Slide14 l.jpg

SQL*Plus script to help convert from MS Access Design

table_to_argos.sql creates a SQL Query that can be used in Argos

or can be used to help specify the selection criteria for your Argos Query Design. 

(OK, it is a warmed over table_to_query.sql from my BannerAccess work, but this one concatenates the output and is useful for creating a single document for the most useful Tables.

http://www.uaex.edu/bknox/table_to_argos.sql


Slide15 l.jpg

Table Descriptions for Argos Use.txt Design

Just a few:

-- Table or View Descriptions -- Details Below

-- To add more Tables or Views run table_to_argos.sql TableNameToAdd

-- **FABBKTP** Bank Tape Table

-- **FABCHKA** Batch Check/Invoice Detail Table

-- **FABCHKS** Check Summary Table

-- **FABINCK** Invoice/Check Table

-- **FABINVH** Invoice Header Table

-- **FARDIRD** Direct Deposit Table.

-- **FARINTX** Invoice Tax Allocation Table

-- **FARINVA** Invoice Accounting Table

-- **FARINVC** Invoice Commodity Table

-- **FFBADJH** Adjustment to Fixed Assets Header Table. -- **FFBMAST

http://www.uaex.edu/bknox/Table Descriptions for Argos Use.txt



Slide17 l.jpg

What to do with MS Access SQL Pass-Through Queries? Design

I am currently dividing our existing MS Access SQL Pass-Through Queries into groups of objects for Argos.



Slide19 l.jpg

Our End-Users find it easier to “paste” together pre-defined Queries than to write their own.

Just Add the Objects, Click and Drag the Joins, Select the Columns to Display. Done!


Slide20 l.jpg

What to do with MS Access SQL Pass-Through Queries? pre-defined Queries than to write their own.


Slide21 l.jpg

This universally available Names Query is very helpful. pre-defined Queries than to write their own.

This one gets the Active Employees, drops Ms, Mrs., Mr., … but leaves the Dr., grabs the Preferred Name, and presents a short version of Last Name for that frequent Last Name First Sort Order, and presents the nearly ubiquitous PIDM for Joins.


Slide22 l.jpg

The question becomes which to make into Oracle Views? pre-defined Queries than to write their own.

Surely the Names Queries.

But, others are more difficult to replace. Such as the Budgeting Queries that need a Parameter to select the Period.


Slide23 l.jpg

The question becomes which to make into Argos DataBlock “Templates”?

If it is complex and needs a Parameter to select the records, then making a DataBlock Template might be the best solution.

Since the Template would have the most complex logic, one could just copy it and add the remaining required Tables or Views.



Slide25 l.jpg

Help for Creating the Oracle Views? Current Records?

I decided that I needed a View with a name very similar to the Banner Table names.

http://www.uaex.edu/bknox/table_to_v_view.sqlwill give you ahead-start.

-- pebempl_v_create.sql Current Records VIEW from TABLE PEBEMPL

CREATE OR REPLACE VIEW PEBEMPL_V (

. . .

Yes, there already is a PEVEMPL. This was just an example.


Slide26 l.jpg

Argos Pre-Summit Tech Track Current Records?

Topics and Questions?

Slide 5. Convert the MS Access SQL to Argos

Slide 10. Create a CSV Import File for creating MAPS Users

Slide 13. Help specify the selection criteria for your Argos Query Design

Slide 16. What to do with MS Access SQL Pass-Through Queries

Slide 24. Help for creating Oracle Views for Banner Tables -- Current Records


Slide27 l.jpg

2007 Pre-Summit Workshop Current Records?

&

User Cooperative

Tech Track

Bruce Knox bknox @t uaex.edu http://www.uaex.edu/bknox/BannerArgos.htm University of Arkansas

Division of Agriculture

Cooperative Extension Service

Cision Studios Images are used with the permission of J. Wilson Spence.


ad