BI_ITEMSFORG_VW

(SQL View)
 Index Back

Billing Item View


SELECT a.business_unit , a.common_id , a.item_nbr , a.emplid , a.account_nbr , a.account_term , a.item_amt , a.item_balance , a.acad_year , c.posted_datetime , c.posted_date , c.session_code , c.due_dt , c.line_reason_cd , c.ref1_descr , c.item_term , d.descr , d.effdt , d.item_type , d.item_type_cd , c.line_seq_nbr , c.line_amt , e.institution , d.setid ,a.applied_amt ,a.item_amt-a.applied_amt ,a.contract_emplid ,a.ext_org_id ,a.payment_id_nbr ,a.refund_nbr ,' ' ,a.contract_num ,f.descr50 FROM ps_item_sf a , ps_item_line_sf c , PS_ITEM_TYPE_TBL d , ps_bus_unit_tbl_sf e , ps_ext_org_tbl f WHERE c.business_unit = a.business_unit AND c.common_id = a.common_id AND c.sa_id_type = a.sa_id_type AND c.item_nbr = a.item_nbr AND a.sa_id_type = 'O' AND d.item_type = a.item_type AND e.business_unit = a.business_unit AND a.common_id = f.ext_org_id AND f.effdt = ( SELECT MAX(g.effdt) FROM ps_ext_org_tbl g WHERE g.ext_org_id = f.ext_org_id AND g.effdt <= %CurrentDateIn) AND d.setid= ( SELECT h.setid FROM ps_set_cntrl_rec h WHERE h.setcntrlvalue = e.institution AND h.recname='ITEM_TYPE_TBL') AND d.effdt = ( SELECT MAX(i.effdt) FROM PS_ITEM_TYPE_TBL i WHERE i.setid = d.setid AND i.item_type = d.item_type AND i.effdt <= %CurrentDateIn)

  • Related Language Record: BIITEMSFORGVWLN

    • # 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 ITEM_NBR Character(15) VARCHAR2(15) NOT NULL Item Nbr
    4 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
    5 ACCOUNT_NBR Character(10) VARCHAR2(10) NOT NULL Account Nbr
    6 ACCOUNT_TERM Character(4) VARCHAR2(4) NOT NULL Account Term
    7 ITEM_AMT Signed Number(18,2) DECIMAL(16,2) NOT NULL Item Amount
    8 ITEM_BALANCE Signed Number(18,2) DECIMAL(16,2) NOT NULL Item Balance
    9 ACAD_YEAR Character(4) VARCHAR2(4) NOT NULL Academic Year
    10 POSTED_DATETIME DateTime(26) TIMESTAMP Posted Date & Time
    11 POSTED_DATE Date(10) DATE Date Posted
    12 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
    13 DUE_DT Date(10) DATE Due Date
    14 LINE_REASON_CD Character(4) VARCHAR2(4) NOT NULL Line Reason Code
    15 REF1_DESCR Character(30) VARCHAR2(30) NOT NULL Reference Nbr
    16 ITEM_TERM Character(4) VARCHAR2(4) NOT NULL Item Term
    17 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    18 EFFDT Date(10) DATE NOT NULL Effective Date

    Default Value: %date

    19 ITEM_TYPE Character(12) VARCHAR2(12) NOT NULL Item Type
    20 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
    21 LINE_SEQ_NBR Number(3,0) SMALLINT NOT NULL Line Sequence Nbr
    22 LINE_AMT Signed Number(18,2) DECIMAL(16,2) NOT NULL Line Amount
    23 INSTITUTION Character(5) VARCHAR2(5) NOT NULL Academic Institution

    Prompt Table: INSTITUTION_TBL

    24 SETID Character(5) VARCHAR2(5) NOT NULL SetID
    25 APPLIED_AMT Signed Number(18,2) DECIMAL(16,2) NOT NULL Applied Amount
    26 BALANCE_AMT Signed Number(18,2) DECIMAL(16,2) NOT NULL Charge Balance
    27 CONTRACT_EMPLID Character(11) VARCHAR2(11) NOT NULL Contract ID
    28 EXT_ORG_ID Character(11) VARCHAR2(11) NOT NULL External Org ID
    29 PAYMENT_ID_NBR Number(12,0) DECIMAL(12) NOT NULL Payment ID Nbr
    30 REFUND_NBR Number(12,0) DECIMAL(12) NOT NULL Refund Nbr
    31 NAME Character(50) VARCHAR2(50) NOT NULL Name
    32 CONTRACT_NUM Character(25) VARCHAR2(25) NOT NULL Contract Number
    33 DESCR50 Character(50) VARCHAR2(50) NOT NULL Description of length 50