TP_CONTRACT_VW2

(SQL View)
 Index Back

Payment Plan Contract View

This view will be used to display the valid Payment Plan Contracts - TP_CON_TYPE = 'P'

SELECT a.business_unit ,a.ext_org_id ,b.emplid ,a.contract_num ,b.tp_status ,a.tp_con_date_type ,a.tp_contract_type ,a.start_dt ,a.end_dt ,a.item_type_group ,a.acad_year ,a.strm ,a.descr ,a.term_from ,a.term_to ,a.course_list ,b.tp_cap_student ,SUM(c.tp_amt) FROM ps_tp_contract a , PS_TP_STUDENT b , ps_tp_stdnt_chrg c WHERE a.business_unit = b.business_unit AND a.contract_num = b.contract_num AND c.business_unit = b.business_unit AND c.contract_num = b.contract_num AND c.emplid = b.emplid AND a.tp_con_type = 'T' GROUP BY a.business_unit ,a.ext_org_id ,b.emplid ,a.contract_num ,b.tp_status ,a.tp_con_date_type ,a.tp_contract_type ,a.start_dt ,a.end_dt ,a.item_type_group ,a.acad_year ,a.strm ,a.descr ,a.term_from ,a.term_to ,a.course_list,b.tp_cap_student

  • Related Language Record: TPCONTRACTVW2LN

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

    Default Value: OPR_DEF_TBL_CS.BUSINESS_UNIT

    Prompt Table: SP_BU_SF_NONVW

    2 EXT_ORG_ID Character(11) VARCHAR2(11) NOT NULL External Org ID
    3 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
    4 CONTRACT_NUM Character(25) VARCHAR2(25) NOT NULL Contract Number
    5 TP_STATUS Character(1) VARCHAR2(1) NOT NULL Contract Status
    A=Active
    C=Cancelled
    H=Hold
    I=Inactive
    U=Not Posted
    6 TP_CON_DATE_TYPE Character(1) VARCHAR2(1) NOT NULL Contract Date Types
    A=Academic Year
    L=Course List
    R=Date Range
    T=Term
    7 TP_CONTRACT_TYPE Character(1) VARCHAR2(1) NOT NULL Third Party Contract Type
    C=Specific Course
    L=Specific Course List
    P=Specific Year or Period
    T=Specific Term
    8 START_DT Date(10) DATE Start Date
    9 END_DT Date(10) DATE end date
    10 ITEM_TYPE_GROUP Character(10) VARCHAR2(10) NOT NULL Item Type Group
    11 ACAD_YEAR Character(4) VARCHAR2(4) NOT NULL Academic Year
    12 STRM Character(4) VARCHAR2(4) NOT NULL Term
    13 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    14 TERM_FROM Character(4) VARCHAR2(4) NOT NULL Term From
    15 TERM_TO Character(4) VARCHAR2(4) NOT NULL Term To
    16 COURSE_LIST Character(9) VARCHAR2(9) NOT NULL Course List
    17 TP_CAP_STUDENT Number(15,2) DECIMAL(14,2) NOT NULL Third Party Student Maximum
    18 TOTAL_AMOUNT_SF Signed Number(17,2) DECIMAL(15,2) NOT NULL Total Amount