SCC_SFP_I_LN3VW

(SQL View)
 Index Back

SFP Item Line SF View

This Item Line SF view used to processs disbursement adjustments from SFP.

SELECT A.BUSINESS_UNIT ,A.COMMON_ID ,A.SA_ID_TYPE ,A.ITEM_NBR ,B.LINE_SEQ_NBR ,B.REF1_DESCR ,A.COMMON_ID ,A.ACCOUNT_NBR ,A.ACCOUNT_TERM ,A.ITEM_TERM ,A.PAYMENT_ID_NBR ,B.DESCR ,A.ITEM_AMT ,A.APPLIED_AMT ,A.ITEM_TYPE ,A.ITEM_TYPE_CD ,B.DUE_DT ,B.BILLING_DT ,B.ITEM_EFFECTIVE_DT ,B.POSTED_DATETIME ,B.POSTED_DATE ,B.ACTUAL_BILLING_DT ,B.LINE_STATUS ,B.LINE_ACTION ,B.LINE_REASON_CD ,A.ACAD_YEAR ,B.LINE_AMT ,A.CURRENCY_CD ,A.ORIGNL_CURRENCY_CD ,A.ORIGNL_ITEM_AMT ,I.REFUNDABLE_IND ,A.ITEM_BALANCE ,I.DESCRSHORT , b.acad_career FROM PS_ITEM_SF A ,PS_ITEM_LINE_SF B ,PS_ITEM_TYPE_TBL I ,PS_BUS_UNIT_TBL_SF C WHERE A.BUSINESS_UNIT=B.BUSINESS_UNIT AND A.COMMON_ID = B.COMMON_ID AND A.SA_ID_TYPE = B.SA_ID_TYPE AND A.ITEM_NBR = B.ITEM_NBR AND A.BUSINESS_UNIT=C.BUSINESS_UNIT AND A.ITEM_TYPE=I.ITEM_TYPE AND I.SETID=( SELECT D.SETID FROM PS_SET_CNTRL_REC D WHERE D.RECNAME='ITEM_TYPE_TBL' AND D.SETCNTRLVALUE=C.INSTITUTION) AND I.EFFDT=( SELECT MAX(EFFDT) FROM PS_ITEM_TYPE_TBL Y WHERE Y.SETID=I.SETID AND Y.ITEM_TYPE=I.ITEM_TYPE AND Y.EFFDT <= %CurrentDateIn) AND B.LINE_AMT < 0 AND NOT EXISTS ( SELECT 'X' FROM PS_ITEM_LINE_SF REV WHERE REV.BUSINESS_UNIT = B.BUSINESS_UNIT AND REV.COMMON_ID = B.COMMON_ID AND REV.SA_ID_TYPE = B.SA_ID_TYPE AND REV.ITEM_NBR = B.ITEM_NBR AND REV.REF1_DESCR = B.REF1_DESCR AND (REV.LINE_AMT + B.LINE_AMT) = 0 )

  • Related Language Record: SCC_SFP_ILN_LNG

    • # 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 ITEM_NBR Character(15) VARCHAR2(15) NOT NULL Item Number
    5 LINE_SEQ_NBR Number(3,0) SMALLINT NOT NULL Line Sequence Number
    6 REF1_DESCR Character(30) VARCHAR2(30) NOT NULL Reference Nbr
    7 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
    8 ACCOUNT_NBR Character(10) VARCHAR2(10) NOT NULL Account Nbr
    9 ACCOUNT_TERM Character(4) VARCHAR2(4) NOT NULL Account Term
    10 ITEM_TERM Character(4) VARCHAR2(4) NOT NULL Item Term
    11 PAYMENT_ID_NBR Number(12,0) DECIMAL(12) NOT NULL Payment ID Nbr
    12 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    13 ITEM_AMT Signed Number(18,2) DECIMAL(16,2) NOT NULL Item Amount
    14 APPLIED_AMT Signed Number(18,2) DECIMAL(16,2) NOT NULL Applied Amount
    15 ITEM_TYPE Character(12) VARCHAR2(12) NOT NULL Item Type
    16 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
    17 DUE_DT Date(10) DATE Due Date
    18 BILLING_DT Date(10) DATE Billing Date
    19 ITEM_EFFECTIVE_DT Date(10) DATE Item Effective Date
    20 POSTED_DATETIME DateTime(26) TIMESTAMP Posted Date & Time
    21 POSTED_DATE Date(10) DATE Date Posted
    22 ACTUAL_BILLING_DT Date(10) DATE Actual Billing Date
    23 LINE_STATUS Character(1) VARCHAR2(1) NOT NULL Line Status
    A=Active
    C=Contingent
    D=Disputed
    H=Hold
    R=Reversed
    24 LINE_ACTION Character(3) VARCHAR2(3) NOT NULL Line Action
    APP=Application Generated
    BCK=Backing Out Term Fees
    CCP=Credit Card Payment
    CRV=Charge Reversal
    CSH=Cashiering
    GRP=Group Posting
    INT=Multi Year Interest Calc
    LAT=Late Fee Processing
    NRA=Tax Calculations
    PAY=Payment Posted
    PMT=Excess Payment
    QBK=Quick Post Back Up
    QCK=Posting Page
    REF=Refund Processing
    SSP=Self service payment
    SYS=System Generated
    TAX=Tax Adjustment Page
    TPA=Third Party - Online
    TUI=Tuition Calculation
    WRO=Writeoff Processing
    25 LINE_REASON_CD Character(4) VARCHAR2(4) NOT NULL Line Reason Code
    26 ACAD_YEAR Character(4) VARCHAR2(4) NOT NULL Academic Year
    27 LINE_AMT Signed Number(18,2) DECIMAL(16,2) NOT NULL Line Amount
    28 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
    29 ORIGNL_CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
    30 ORIGNL_ITEM_AMT Signed Number(18,2) DECIMAL(16,2) NOT NULL Item Amount
    31 REFUNDABLE_IND Character(1) VARCHAR2(1) NOT NULL Refundable Indicator
    32 ITEM_BALANCE Signed Number(18,2) DECIMAL(16,2) NOT NULL Item Balance
    33 DESCRSHORT Character(10) VARCHAR2(10) NOT NULL Short Description
    34 ACAD_CAREER Character(4) VARCHAR2(4) NOT NULL Academic Career
    BAC=Bachelor (NLD)
    BBL=Vocational Coaching (NLD)
    BOL=Vocational Training (NLD)
    BUSN=Graduate Business
    CNED=Continuing Education
    CRED=Semester Credit
    EDU=Education (NLD)
    EXED=Extended Education
    GRAD=Graduate
    LAW=Law
    MEDS=Medical School
    NONA=Non Award
    PGRD=Postgraduate
    RSCH=Research
    TECH=Technical
    UENG=Undergraduate Engineering
    UGRD=Undergraduate
    VAVO=Advanced General Educ. (NLD)
    VETM=Veterinary Medicine