OPT_FEE_TVAL_VW

(SQL View)
 Index Back

Optional Fee Term Values View


SELECT a.emplid , a.institution , a.strm , c.fee_cd , a.billing_career , d.sf_allow_self_srvc , f.valid_value , f.effdt , f.amt_per_unit , f.flat_amt , f.max_amount , f.currency_cd , f.initial_value , h.fa_primacy_nbr FROM PS_STDNT_CAR_TERM a , ps_set_cntrl_rec b , ps_opt_fee_car c , ps_opt_fee_tbl d , ps_opt_fee_trm_eff e , ps_opt_fee_trm_val f , ps_term_tbl g , ps_acad_car_tbl h WHERE a.acad_career = a.billing_career AND b.setcntrlvalue = a.institution AND b.recname = 'OPT_FEE_TRM_VAL' AND c.setid = b.setid AND c.acad_career = a.acad_career AND c.effdt = d.effdt AND d.setid = b.setid AND d.fee_cd = c.fee_cd AND d.effdt = ( SELECT MAX(dd.effdt) FROM ps_opt_fee_tbl dd WHERE dd.setid = d.setid AND dd.fee_cd = d.fee_cd AND dd.effdt <= g.term_begin_dt) AND d.eff_status = 'A' AND e.setid = b.setid AND e.fee_cd = c.fee_cd AND e.strm = a.strm AND (e.academic_load = a.academic_load OR (NOT EXISTS ( SELECT 'X' FROM PS_OPT_FEE_TRM_EFF E1 WHERE E1.SETID = B.SETID AND E1.FEE_CD = C.FEE_CD AND E1.STRM = A.STRM AND E1.ACADEMIC_LOAD = A.ACADEMIC_LOAD) AND e.academic_load = ' ')) AND e.effdt = ( SELECT MAX(ee.effdt) FROM ps_opt_fee_trm_eff ee WHERE ee.setid = e.setid AND ee.fee_cd = e.fee_cd AND ee.strm = e.strm AND ee.academic_load = e.academic_load AND ee.effdt <= g.term_begin_dt) AND e.eff_status = 'A' AND f.setid = b.setid AND f.fee_cd = c.fee_cd AND f.strm = a.strm AND f.academic_load = e.academic_load AND f.effdt = e.effdt AND g.institution = a.institution AND g.acad_career = a.acad_career AND g.strm = a.strm AND h.institution = a.institution AND h.acad_career = a.billing_career AND h.effdt = ( SELECT MAX(hh.effdt) FROM ps_acad_car_tbl hh WHERE hh.institution = h.institution AND hh.acad_career = h.acad_career AND hh.effdt <= g.term_begin_dt) AND h.eff_status = 'A'

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
2 INSTITUTION Character(5) VARCHAR2(5) NOT NULL Academic Institution

Prompt Table: INSTITUTION_TBL

3 STRM Character(4) VARCHAR2(4) NOT NULL Term
4 FEE_CD Character(6) VARCHAR2(6) NOT NULL Fee Code
5 BILLING_CAREER Character(4) VARCHAR2(4) NOT NULL Billing Career
6 SF_ALLOW_SELF_SRVC Character(1) VARCHAR2(1) NOT NULL Allow Self Service
7 VALID_VALUE Character(6) VARCHAR2(6) NOT NULL Valid Value
8 EFFDT Date(10) DATE Effective Date

Default Value: %date

9 AMT_PER_UNIT Number(17,2) DECIMAL(16,2) NOT NULL Amount Per Unit
10 FLAT_AMT Number(17,2) DECIMAL(16,2) NOT NULL Flat Amount
11 MAX_AMOUNT Number(17,2) DECIMAL(16,2) NOT NULL Maximum Amount
12 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
13 INITIAL_VALUE Character(1) VARCHAR2(1) NOT NULL Initial Value
14 FA_PRIMACY_NBR Number(3,0) SMALLINT NOT NULL Primacy Nbr