-- maps_users.sql   Create the MAPS Users Import File from Banner
--**Use this information and these scripts at your own risk. As a condition of using these scripts and information from this site, you agree to hold harmless both the University of Arkansas Cooperative Extension Service and Bruce Knox for any problems that they may cause or other situations that may arise from their use, and that neither the Extension Service nor I will be held liable for those consequences. The scripts and information are provided "as is" without warranty, implied or otherwise. Limitation of liability will be the amount paid to the University of Arkansas specifically for this information. (It was free:) 

SET ECHO OFF

-- Change Log
-- 02/08/07 bknox Created

-- The record layout for the Import File of MAPS Users
--   First field indicates the source of the users "MAPS" 
--   Second field represents the user name
--   Third field represents the password
--   Fourth field represents the description
--   Fifth field contains the email address
-- For example, a MAPS user record might look like:
-- MAPS,jaos,123,acct dept, jaos @t evisions.com

-- Users must be assigned to the Argos Security Class Object before this script will work
-- While Description is always Finance, that is easily changed.
-- The password for the Users' first logon to MAPS will be their Banner UserID + the last four digits of their SSN
-- Be sure to check mark the "User must password change at next login" when Importing the file.

SET SHOWMODE OFF
SET TIMING OFF
SET FEEDBACK Off
SET HEADING OFF
SET LINESIZE 256
SET TRIMSPOOL ON
SET PAGESIZE 0          
SET NEWPAGE 0
SET VERIFY OFF
SET TERMOUT OFF

-- SET ECHO      OFF Suppress Display of commands
-- SET FEEDBACK  OFF Suppress Display of record counts
-- SET LINESIZE  n   Set line length
-- SET PAGESIZE  n   Set lines per page
-- SET SHOWMODE  OFF Suppress Display of old and new setting of SQL*Plus system variables
-- SET NEWPAGE   0   Set no lines before TITLE and eject frist page
-- SET VERIFY    ON  Show each line of the file before and after substitution
-- SET TERMOUT   OFF Suppress Display of output
-- SET TRIMSPOOL ON  Trims End of Line Spaces

CLEAR COLUMNS

COLUMN Sort_Name NOPRINT

TTITLE OFF
BTITLE OFF

CLEAR BREAK
CLEAR COMPUTE

SET TERMOUT OFF
SET HEADING OFF
SET PAGESIZE 0   
-- no page breaks
SET WRAP OFF
SET SPACE 0      
-- no space(s) between columns
SET TIME OFF
SET TIMING OFF   
-- prevent time stamp at end of report

SET FEEDBACK OFF

SET LINESIZE 256
SET TRIMSPOOL ON

SPOOL maps_users.csv

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;

SPOOL OFF

CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
SET NUMWIDTH 10
SET HEADING ON
SET SPACE 1
SET FEEDBACK ON
SET VERIFY OFF
SET TERMOUT ON

-- maps_users.sql end