SSF_BI_LINE_VW

(SQL View)
 Index Back

Billing Item View

View fo the Billing Item - joins to PS_BI_BILLING_LINE B , ps_item_sf A , ps_item_line_sf c , ps_item_type_tbl d , ps_bus_unit_tbl_sf e , PS_SCC_INS_NAME_VW f , PS_BI_BILL_HEADER G

SELECT B.business_unit , B.common_id , B.INVOICE_ID , B.item_nbr , B.ITEM_LINE , 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 ,f.name ,C.DUE_DATE_BY FROM PS_BI_BILLING_LINE B , ps_item_sf A , ps_item_line_sf c , PS_ITEM_TYPE_TBL d , ps_bus_unit_tbl_sf e , PS_SCC_INS_NAME_VW f , PS_BI_BILL_HEADER G WHERE B.BUSINESS_UNIT = C.BUSINESS_UNIT AND B.COMMON_ID = C.COMMON_ID AND B.SA_ID_TYPE = C.SA_ID_TYPE AND B.ITEM_NBR = C.ITEM_NBR AND B.ITEM_LINE = C.LINE_SEQ_NBR AND 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 = 'P' AND d.item_type = a.item_type AND e.business_unit = a.business_unit AND a.common_id = f.emplid AND G.BUSINESS_UNIT = B.BUSINESS_UNIT AND G.INVOICE_ID = B.INVOICE_ID AND d.setid= ( SELECT setid FROM ps_set_cntrl_rec WHERE setcntrlvalue=e.institution AND recname='ITEM_TYPE_TBL') AND d.effdt = ( SELECT MAX(effdt) FROM PS_ITEM_TYPE_TBL WHERE d.setid = setid AND d.item_type = item_type AND effdt <= %CurrentDateIn)

  • Related Language Record: SSF_BILINEVWLNG

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
    2 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
    3 INVOICE_ID Character(22) VARCHAR2(22) NOT NULL Invoice Number
    4 ITEM_NBR Character(15) VARCHAR2(15) NOT NULL Item Nbr
    5 ITEM_LINE Number(3,0) SMALLINT NOT NULL Item Line
    6 ACCOUNT_NBR Character(10) VARCHAR2(10) NOT NULL Account Nbr
    7 ACCOUNT_TERM Character(4) VARCHAR2(4) NOT NULL Account Term
    8 ITEM_AMT Signed Number(18,2) DECIMAL(16,2) NOT NULL Item Amount
    9 ITEM_BALANCE Signed Number(18,2) DECIMAL(16,2) NOT NULL Item Balance
    10 ACAD_YEAR Character(4) VARCHAR2(4) NOT NULL Academic Year
    11 POSTED_DATETIME DateTime(26) TIMESTAMP Posted Date & Time
    12 POSTED_DATE Date(10) DATE Date Posted
    13 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
    14 DUE_DT Date(10) DATE Expected Birth Date
    15 LINE_REASON_CD Character(4) VARCHAR2(4) NOT NULL Line Reason Code
    16 REF1_DESCR Character(30) VARCHAR2(30) NOT NULL Reference Nbr
    17 ITEM_TERM Character(4) VARCHAR2(4) NOT NULL Item Term
    18 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    19 EFFDT Date(10) DATE NOT NULL Effective Date

    Default Value: %date

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

    Prompt Table: INSTITUTION_TBL

    25 SETID Character(5) VARCHAR2(5) NOT NULL SetID
    26 APPLIED_AMT Signed Number(18,2) DECIMAL(16,2) NOT NULL Applied Amount
    27 BALANCE_AMT Signed Number(18,2) DECIMAL(16,2) NOT NULL Charge Balance
    28 CONTRACT_EMPLID Character(11) VARCHAR2(11) NOT NULL Contract ID
    29 EXT_ORG_ID Character(11) VARCHAR2(11) NOT NULL External Org ID
    30 PAYMENT_ID_NBR Number(12,0) DECIMAL(12) NOT NULL Payment ID Nbr
    31 REFUND_NBR Number(12,0) DECIMAL(12) NOT NULL Refund Nbr
    32 NAME Character(50) VARCHAR2(50) NOT NULL Name
    33 DUE_DATE_BY Character(3) VARCHAR2(3) NOT NULL Due Date Enter By
    GNR=Generated by Billing
    GRP=Group Post Program
    QCK=Quick Post Program
    TUI=Tuition Calc Program