OPT_FEE_SEL_VW

(SQL View)
 Index Back

Optional Fee Selection View


SELECT a.emplid , a.institution , a.strm , c.fee_cd , f.valid_value , a.billing_career , f.effdt , f.amt_per_unit , f.flat_amt , f.max_amount , f.currency_cd 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 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

# 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 VALID_VALUE Character(6) VARCHAR2(6) NOT NULL Valid Value
6 BILLING_CAREER Character(4) VARCHAR2(4) NOT NULL Billing Career
7 EFFDT Date(10) DATE Effective Date

Default Value: %date

8 AMT_PER_UNIT Number(17,2) DECIMAL(16,2) NOT NULL Amount Per Unit
9 FLAT_AMT Number(17,2) DECIMAL(16,2) NOT NULL Flat Amount
10 MAX_AMOUNT Number(17,2) DECIMAL(16,2) NOT NULL Maximum Amount
11 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code