SIS V8 PeopleSoft Technical Development

1
SIS V8 PeopleSoft Technical Development
Presenters Linda Silver Emily Clements Drew
Haslett Violet Martin Kathy Harcum

2
Student Financials Workgroup

• Overview
• VCCS Technical Standards
• Modifying Objects Programs
• Naming Conventions
• Hanging Programs on Menu
• Other Important Technical Info
• File Transfer Source Program Move Processes
• Change Control
• SQR Tips
• Questions

3
VCCS Technical Standards

• SIS V8 Technical Standards
• On VCCS Website, under IT, SIS V8 Upgrade
• Importance and Impact on Upgrades
• Refer to Standards Before Making Changes
• Use as Reference Document
• Contains Info on Technical Documentation, Move
  Instructions documentation location
• For Questions on Technical Standards
• Contact Drew Haslett or myself

4
Modifying Objects Programs

• Reusability -- Ask yourself, does a similar
  object or program exist that can be cloned?
• Whenever possible, use existing run control
  record
• To initiate a new Project, open an Issue Trak
  ticket
• Critical SAVE objects and programs under a new
  custom name, before making any changes
• Be sure to include all modified objects,
  peoplecode and process definitions in the project
• Add brief description of changes to Project
  Properties and Object Properties, include Project
  

5
Naming Conventions

• Save cloned objects and programs to a new name
  before modifying
• Projects and Application Objects
• Use VX prefix in the name
• Source Code Programs
• Pos. 1-2 VX
• Pos. 3 1-digit college code
• Pos. 4 functional abbrev. (R,F,A,V,M)
• Pos.5-8 process name (alpha or numeric)
• If program for all colleges, use X college code

6
Hanging Programs on Menu

• Component
• Contains page(s)
• Place under bar item on Menu (Use, Inquire,
  Process, Report)
• Process Definition
• Create one for any program launched from menu
• Specify Component ties program to menu
  navigation
• Page GUI interface
• Used to input data on run control record
• Program should read values entered
• Run Control Record
• Use existing run control records, when possible
• Data is stored in the database

7
Other Important Technical Info

• Importance of Process Logs
• Provides helpful info for resolving issues
• Evaluate enhance database performance
• Automatically purged after 10 days
• Please do not delete process logs
• Sqrw no longer available
• PeopleSoft doesnt support prompted input to
  processes
• Client application goes away in future versions

8
SQR Move Process

• Allows developers to move their custom SQR
  programs to development for testing
• Steps
• Move the custom SQR to the ps8data/SQR folder on
  the shared network drive. Note the file name an
  file extension must be in lower case
• Within the development PeopleSoft application go
  to VCCS Custom Menu/General Processes/ Process.
  Either use and existing run control if one exists
  or create a new one.
• Enter in the name of the custom SQR program you
  want to move and set the input/output type to I
  (input)
• Click the run button
• Make sure the server name is PSUNX and the
  process is checked
• Click OK
• Monitor the job and when finished review the log
  to make sure the program did move successfully. A
  status of success on the process monitor does not
  mean that the transfer was successful.

9
SQR Move Process
10
SQR Move Process

• This process will also allow you to check out a
  copy of a existing custom SQR program from
  Production. It will move the specified SQR to the
  ps8data/SQR directory on the shared file server.
• Steps
• Within the production PeopleSoft application go
  to VCCS Custom Menu/General Processes/ Process.
  Either use and existing run control if one exists
  or create a new one.
• Enter in the name of the custom SQR program you
  want to move and set the input/output type to O
  (output)
• Click the run button
• Make sure the server name is PSUNX and the
  process is checked
• Click OK
• Monitor the job and when finished review the log
  to make sure the program did move successfully. A
  status of success on the process monitor does not
  mean that the transfer was successful
• The program should be in the ps8data/SQR
  directory on the shared file server. It should be
  moved to your PC and deleted from the shared file
  server.

11
SQR Move Process

• The process executes a custom SQR that calls an
  FTP script which copies the program to the custom
  SQR directory on the UNIX box and deletes it from
  the ps8data/SQR directory on the file server.
  Always keep a copy of the program that you are
  developing or editing on your PC.

12
File Transfer Process

• Process is designed to move files between the
  UNIX box and the file server
• The process can either move files from UNIX to
  the file server or from the file server to UNIX
• PeopleSoft executed processes that create files
  need to create them in the /as01/psintf/ps8data_IN
  ST/testout or prodout directory on UNIX in order
  for the files to be transferred.
• PeopleSoft executed processes that read in a file
  need to get the file from /as01/psintf/ps8data_INS
  T/testin or prodin directory on UNIX
• The INST refers to your institution value in
  uppercase, ie BR291, NV280 etc.
• Note the testin and testout directories are used
  for the development, test, and training database
  instances
• Because UNIX is case sensitive, the case of the
  various components is very important. All file
  names should be in upper case.
• In most cases, delivered process that ask for a
  file path will require a / at the end of the file
  path
• The file transfer process can be run as a
  separate process or incorporated into a process
  that creates or reads the file. Running the
  transfer as a separate step eliminates the need
  to customize delivered or older custom processes.
  But both require the setup step to be completed.

13
File Transfer Process
14
File Transfer Process
15
File Transfer Process
16
File Transfer Process

• Example SQR program that creates and FTPs a flat
  file is VXXDEMO1.sqr
• Example SQR program that creates and FTPs a CSV
  file is VXXDEMO2.sqr
• Project that contains all the components for
  these 2 processes is VX69519
• The file transfer set was done for BR291 for
  testing. Look at the setup for these programs for
  BR291to see how everything was entered

17
Directory Listing Process

• The directory listing process was designed to
  allow colleges to see what is in their UNIX file
  transfer directories (/as01/psintf/ps8data_INST)
  or to see a listing of the custom SQRs in the
  various database instances
• To see what is in the file transfer directory the
  institution value is entered into the run control
  page.
• To see what is in the custom SQR directory a new
  run control is needed with the institution field
  blank.
• The report is in the log file.

18
Change Control

• Overview
• Refreshes
• Bundles and Fixes
• Project Moves

19
Change Control - Project Moves

• Queries
• Other Projects

20
Change Control - Queries

• Project follows IssueTrak naming
• Move to TEST, PROD and QERY
• Developing in S80QERY risky business

21
Change Control - Other Projects

• What and why a project
• Logical business unit
• IssueTrak and naming
• Move project to instances
• Move instructions
• Functional and technical documentation

22
Change Control - Other Projects

• Thursdays
• Why Thursdays
• Technical consideration
• Standardization
• One day refresher
• Even non-invasive objects
• Emergencies

23
Change Control - Miscellaneous

• Menus
• Prompts
• Multiple SQRs, Crystal reports
• Crystal versus SQR
• During upgrades
• Tuning

24
SQR Tips

• Develop all SQRs using Structured Programming
• A technique for organizing and coding computer
  programs in which a hierarch of modules issued,
  each having a single entry and a single exit
  point, and in which control is passed downward
  through the structure without unconditional
  branches to higher levels of the structure.
• Structured programming is often associated with a
  top-down approach to design. In this way
  designers map out the large scale structure of a
  program in terms of smaller operations, implement
  and test the smaller operations, and then tie
  them together into a whole program.

25
SQR Tips

• Joins SQL or Procedure Calls
• Limit Joins in a Select to 5 tables.
• Join on the Full Key
• It is generally more efficient to select from
  Tables not Views
• More Joins More SQR and Oracle Overhead
• Use procedures or delivered sqcs to obtain data
• SQC Benefits
• Speeds up execution
• Returns consistent results
• Automatically updated by PeopleSoft during Upgrade

26
SQR Tips

• Table Joins
• SQL Distinct Parameter
• Tuning SQL
• Ensure the table joins are correct and the proper
  criteria us used
• Optimize the SQL Statements
• Test in SQL Plus before coding SQR
• SQL Analysis

27
SQR Tips

• SQR Arrays
• Command Efficiency

28
SQR Tips

• Pseudo-Code
• Flowchart the program logic
• Research and ask a few questions
• How much data will be pulled? Will it be100
  rows or 1000 rows?
• How many rows will come from each table?
• What kind of data will be pulled? Student Data?
  Setup Data?
• What does the key structure look like for the
  tables being pulled?
• Is this table the parent or the child?
• How important is this table?
• What fields will be needed from this table? Are
  they available somewhere else?
• Write SQL and test in SQL Plus before coding SQR
• Review complicated or long running SQL with your
  local DBA or IT support staff
• Use Linear Programming easier to program and
  debug
• Comment

29
Questions