STDNT_PY_VW

(SQL View)
 Index Back

Payment Item VW


SELECT a.payment_id_nbr ,a.business_unit ,a.common_id ,a.item_term ,b.posted_date ,d.descr ,a.currency_cd ,SUM(a.item_amt) FROM ps_item_sf a , ps_payment_tbl b , ps_bus_unit_tbl_sf c , PS_ITEM_TYPE_TBL d WHERE a.sa_id_type = 'P' AND a.business_unit = c.business_unit AND a.item_type = d.item_type AND a.item_type_cd IN ('D','I','P','Z') AND a.business_unit = b.business_unit AND a.common_id = b.common_id AND a.sa_id_type = b.sa_id_type AND a.payment_id_nbr = b.payment_id_nbr AND d.setid = ( SELECT e.setid FROM ps_set_cntrl_rec e WHERE e.setcntrlvalue = c.institution AND e.recname = 'ITEM_TYPE_TBL') AND d.effdt = ( SELECT MAX(f.effdt) FROM PS_ITEM_TYPE_TBL f WHERE f.setid = d.setid AND f.item_type = d.item_type AND f.effdt <= %CurrentDateIn) AND d.eff_status = 'A' GROUP BY a.payment_id_nbr, a.business_unit, a.common_id, a.item_term, c.institution, b.posted_date, d.descr, a.currency_cd

  • Related Language Record: STDNT_PY_VWLNG

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 PAYMENT_ID_NBR Number(12,0) DECIMAL(12) NOT NULL Payment ID Nbr
    2 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

    Default Value: OPR_DEF_TBL_CS.BUSINESS_UNIT

    Prompt Table: SP_BU_SF_NONVW

    3 COMMON_ID Character(11) VARCHAR2(11) NOT NULL Common ID to store Personal ID / Ext Org ID value
    4 ITEM_TERM Character(4) VARCHAR2(4) NOT NULL Item Term
    5 POSTED_DATE Date(10) DATE Date Posted
    6 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    7 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
    8 ITEM_AMT Signed Number(18,2) DECIMAL(16,2) NOT NULL Item Amount