Banner DATBASE Workshop-V

1
Banner DATBASE Workshop-V

• Girija Chavala
• Northwest Oracle/Banner DBA

2
FINANCE TABLES

• Common tables for all modules
• SPRIDEN -- Person/nonperson TABLE
• SPRADDR Vendor address TABLE
• SPRTELE -- Vendor telephone TABLE
• SPBPERS -- Vendor ssn/TIN table
• GOREMAL vendor email table
• FTVVEND -- Vendor Validation table
• FTVVENT -- Vendor type table
• FTVITYP -- Vendor income type table
• FTVVTYP -- Vendor type validation table

3
CFOPAL tables - heirarchial

• FTVCOAS legal entity
• FTVFUND - funding source
• FTVORGN - department
• FTVACCT - object/type of transaction
• FTVPROG - functional category
• FTVACTV type of activity (OPTIONAL)
• FTVACCI - Data entry aid combining elements
• FRBGRNT - Grants and contracts

4
Fund structure tables

• FTVSDAT -- Table of tables. (FOQSDLV form)
• FTVFTYP -- fund type validation table
• FTVFUND fund table

5
Account structure tables

• FTVSDAT
• FTVATYP -- account type validation
• FTVACCT account validation table

6
Fiscal year/PERIOID tables

• FTVFSYR fiscal year table
• FTVFSPD -- fiscal period table
• FTMFMGR finance manager table
• These tables work with transaction date to set
  the fiscal year code and fiscal period code
• They determine which fiscal year transactions
  should post to

7
Document sequence number tables

• FOBSEQN -- sets one-up number
• FOBFSEQ

8
List fund codes and titles in chart B

• Table ftvfund
• Column ftvfund_coas_code
• Column ftvfund_fund_code
• Column ftvfund_title
• SELECT ftvfund_coas_code Chart Code,
• ftvfund_fund_code FUND CODE,
• ftvfund_title FUND TITLE
• FROM ftvfund
• WHERE ftvfund_coas_code B AND
• ftvfund_eff_date lt sysdate AND
• (ftvfund_term_date gt sysdate OR
• ftvfund_term_date is null)
• ORDER BY ftvfund_coas_code

9
Vendor list using Banner tables

• SPRIDEN -- name, id
• SPRADDR address
• FTVVEND -- vendor validation table
• FTVVENT -- vendor type

10
What is PIDM?

• Internal identification number
• Generated uniquely at the time of ID creation
• PIDM is replicated where ever person data is
  populated
• No one can delete pidm except DBA

11
SPRIDEN table

• DESCRIBE spriden -- repeatable table
• Always use spriden_entity_ind P for person
  search
• Always use spriden_entity_ind C for company
  search
• If you leave out entity indicator, you will get
  both companies and people
• Always use spriden_change_ind is null

12
SPRADDR table -- repeatable table

• Search by address type. Example, BU, PR
• Always use spraddr_status_ind is null
• Inactive address
• Do not use phone fields from this table
• Finance addresses do not require from and to
  dates
• Sequence numbers are generated based on address
  type.

13
SPBPERS base table

• SSN/TIN is stored
• Not a primary key
• Warning is issued if some one already using same
  ssn
• Only one record per person

14
SPRTELE repeatable

• Telephone code
• Area code
• Phone number
• Extension
• Always use sprtele_status_ind is null
• Inactive phone numbers

15
FTVVEND validation table

• Vendor table
• Can contain persons or companies
• Spriden record has to exist before vendor is
  created
• Effective date
• Termination date
• Active vendors
• ftvvend_term_date is null

16
List active vendors from banner tables

• List vendor id,
• tax id,
• vendor name,
• vendor street,
• vendor city,
• vendor state,
• vendor zip,
• vendor contact name,
• vendor federal withholding,
• vendor state withholding

spriden
spbpers
spriden
Spraddr with atyp code VN
FTVVEND
17
SQL for banner vendor list

• select spriden_id "Vendor ID",
• spbpers_ssn "Tax ID",
• spriden_first_name' '
• spriden_last_name "Vendor name",
• spraddr_street_line1 "Street",
• spraddr_city "City",
• spraddr_stat_code "State",
• substr(spraddr_zip,1,5) "ZIP",
• ftvvend_contact "Contact name",
• ftvvend_fed_whold_pct "Fed witholding",
• ftvvend_st_whold_pct "State
  withholding"
• FROM spriden, spbpers, spraddr, ftVvend
• WHERE spriden_pidm spraddr_pidm and
• spriden_pidm ftvvend_pidm and
• spriden_pidm spbpers_pidm and
• spriden_change_ind is null and
• spraddr_status_ind is null and
• spraddr_atyp_code 'VN