TERM_SUMM2_SF

(SQL View)
 Index Back

Term Summary SF


SELECT a.business_unit ,a.common_id ,a.sa_id_type ,a.emplid ,a.ext_org_id ,Z.DESCRSHORT ,b.item_type_cd ,A.ITEM_TERM ,SUM(a.item_amt) FROM ps_item_sf a ,PS_ITEM_TYPE_TBL b ,ps_bus_unit_tbl_sf c ,PS_TERM_VAL_TBL Z WHERE a.business_unit=c.business_unit AND A.ITEM_TERM=Z.STRM AND a.item_type = b.item_type AND b.setid= ( SELECT d.setid FROM ps_set_cntrl_rec d WHERE d.recname='ITEM_TYPE_TBL' AND d.setcntrlvalue=c.institution) AND b.effdt= ( SELECT MAX(e.effdt) FROM PS_ITEM_TYPE_TBL e WHERE b.setid=e.setid AND b.item_type=e.item_type AND e.effdt <= a.item_effective_dt) AND b.eff_status='A' GROUP BY a.business_unit ,a.common_id ,a.sa_id_type ,a.emplid ,a.ext_org_id ,Z.DESCRSHORT ,b.item_type_cd ,A.ITEM_TERM

  • Related Language Record: TERM_SUMM2SFLNG

    • # 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 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
    5 EXT_ORG_ID Character(11) VARCHAR2(11) NOT NULL External Org ID
    6 DESCRSHORT Character(10) VARCHAR2(10) NOT NULL Short Description
    7 ITEM_TYPE_CD Character(1) VARCHAR2(1) NOT NULL Item Type Code
    A=Application Fee
    B=Billing Only
    C=Charge
    D=Deposit
    F=Financial Aid
    G=GL Interface Only
    H=Withholding
    I=Interest
    L=Pre-Paid Tuition
    P=Payment
    R=Refund
    T=Transfers
    V=Contributor Relations
    W=Waiver
    X=Write-off
    Z=Pay Plan Credit
    8 ITEM_TERM Character(4) VARCHAR2(4) NOT NULL Item Term
    9 ITEM_AMT Signed Number(18,2) DECIMAL(16,2) NOT NULL Item Amount