SSF_ITCHRG_VW

(SQL View)
 Index Back

Item Charge Due View


SELECT a.business_unit , a.common_id , a.sa_id_type , a.item_nbr , a.emplid , a.item_amt , a.applied_amt , a.item_type , a.account_term , a.item_term , a.account_nbr , a.acad_year , a.refund_nbr , a.item_amt - a.applied_amt , a.ref1_descr , a.account_type_sf , a.ext_org_id , 0.0 , 0.0 , a.contract_emplid , a.currency_cd , a.session_code ,a.class_nbr FROM ps_item_sf a , PS_ITEM_TYPE_TBL c , ps_bus_unit_tbl_sf d , ps_item_acctyp_vw i WHERE a.business_unit = d.business_unit AND a.item_type = c.item_type AND C.ITEM_TYPE_CD IN ('C', 'A') AND a.item_amt - a.applied_amt > 0.0 AND c.setid = ( SELECT e.setid FROM ps_set_cntrl_rec e WHERE e.recname = 'ITEM_TYPE_TBL' AND e.setcntrlvalue = d.institution) AND c.effdt = ( SELECT MAX(f.effdt) FROM PS_ITEM_TYPE_TBL f WHERE f.setid = c.setid AND f.item_type = c.item_type AND f.effdt <= %CurrentDateIn AND f.eff_status = 'A') AND i.business_unit = d.business_unit AND i.item_type = a.item_type AND i.effdt = c.effdt AND i.account_type_sf = a.account_type_sf

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
2 COMMON_ID Character(11) VARCHAR2(11) NOT NULL Common ID to store Personal ID / Ext Org ID value
3 SA_ID_TYPE Character(1) VARCHAR2(1) NOT NULL ID Type
O=Organization
P=Person
4 ITEM_NBR Character(15) VARCHAR2(15) NOT NULL Item Nbr
5 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
6 ITEM_AMT Signed Number(18,2) DECIMAL(16,2) NOT NULL Item Amount
7 APPLIED_AMT Signed Number(18,2) DECIMAL(16,2) NOT NULL Applied Amount
8 ITEM_TYPE Character(12) VARCHAR2(12) NOT NULL Item Type
9 ACCOUNT_TERM Character(4) VARCHAR2(4) NOT NULL Account Term
10 STRM Character(4) VARCHAR2(4) NOT NULL Term
11 ACCOUNT_NBR Character(10) VARCHAR2(10) NOT NULL Account Nbr
12 ACAD_YEAR Character(4) VARCHAR2(4) NOT NULL Academic Year
13 REFUND_NBR Number(12,0) DECIMAL(12) NOT NULL Refund Nbr
14 ITEM_BALANCE Signed Number(18,2) DECIMAL(16,2) NOT NULL Item Balance
15 REF1_DESCR Character(30) VARCHAR2(30) NOT NULL Reference Nbr
16 ACCOUNT_TYPE_SF Character(3) VARCHAR2(3) NOT NULL Account Type
17 EXT_ORG_ID Character(11) VARCHAR2(11) NOT NULL External Org ID
18 BALANCE_REMAIN Signed Number(18,2) DECIMAL(16,2) NOT NULL Remaining Balance
19 PAY_CHARGE_AMT Number(17,2) DECIMAL(16,2) NOT NULL Pay Charge Amount
20 CONTRACT_EMPLID Character(11) VARCHAR2(11) NOT NULL Contract ID
21 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
22 SESSION_CODE Character(3) VARCHAR2(3) NOT NULL Session
1=Regular Academic Session
10W=10 Week
12W=Twelve Week
4W1=Four Week - First
4W2=Four Week - Second
4W3=Four Week - Third
4W4=Four Week - Fourth
6W1=Six Week - First
6W2=Six Week - Second
8W1=Eight Week - First
8W2=Eight Week - Second
A11=Medical Period 1, 4 Weeks
A12=Medical Period 1, 8 Weeks
A13=Medical Pd 1, 12 Weeks
A21=Medical Period 2, 4 Weeks
A22=Medical Period 2, 8 Weeks
A23=Medical Period 2, 12 Week
FYR=Full Year
MIN=Mini Session
OEE=Open Entry/Open Exit
SM1=Semester 1
SM2=Semester 2
23 CLASS_NBR Number(5,0) INTEGER NOT NULL Class Nbr