PMT_ORG_EMPL_VW

(SQL View)
 Index Back

Payment Item Org VW


SELECT A.BUSINESS_UNIT ,A.COMMON_ID ,A.SA_ID_TYPE ,A.EXT_ORG_ID ,B.ITEM_NBR ,A.PAYMENT_ID_NBR ,A.EMPLID ,A.STRM ,G.DESCRSHORT ,A.ITEM_TYPE ,B.ITEM_AMT ,C.ITEM_TYPE_CD ,C.DESCR ,A.POSTED_DATE ,A.SF_REVERSAL_IND ,A.CURRENCY_CD ,Z.CONTRACT_NUM ,Z.CONTRACT_EMPLID ,B.ACCOUNT_TERM ,B.ACCOUNT_NBR ,B.ITEM_BALANCE ,B.ITEM_STATUS ,B.LAST_ACTIVITY_DATE ,B.ITEM_TERM ,B.APPLIED_AMT ,B.ACAD_YEAR ,B.ENCUMBERED_AMT ,B.REF1_DESCR ,B.ACAD_CAREER ,B.STDNT_CAR_NBR ,B.SESSION_CODE ,B.CLASS_NBR ,B.FEE_CD ,B.SEL_GROUP ,B.ADM_APPL_NBR ,B.REFUND_NBR ,B.RECEIPT_NBR ,B.ITEM_NBR_SOURCE ,B.CUR_RT_TYPE ,B.RATE_MULT ,B.RATE_DIV ,B.ORIGNL_CURRENCY_CD ,B.ORIGNL_ITEM_AMT ,B.REFUND_EMPLID ,B.REFUND_EXT_ORG_ID ,B.TAX_CD ,B.TAX_AUTHORITY_CD ,A.PAYMENT_AMT FROM PS_PAYMENT_TBL A ,PS_ITEM_SF B ,PS_ITEM_TYPE_TBL C ,PS_BUS_UNIT_TBL_SF D ,PS_TERM_VAL_TBL G ,PS_ORG_EMP_PAY_VW Z 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.PAYMENT_ID_NBR=B.PAYMENT_ID_NBR AND A.BUSINESS_UNIT=D.BUSINESS_UNIT AND A.STRM=G.STRM AND A.BUSINESS_UNIT = Z.BUSINESS_UNIT AND A.COMMON_ID = Z.COMMON_ID AND A.SA_ID_TYPE = Z.SA_ID_TYPE AND A.PAYMENT_ID_NBR = Z.PAYMENT_ID_NBR AND B.ITEM_NBR = Z.ITEM_NBR AND C.SETID=( SELECT E.SETID FROM PS_SET_CNTRL_REC E WHERE E.SETCNTRLVALUE=D.INSTITUTION AND E.RECNAME='ITEM_TYPE_TBL') AND A.ITEM_TYPE=C.ITEM_TYPE AND C.EFFDT=( SELECT MAX(F.EFFDT) FROM PS_ITEM_TYPE_TBL F WHERE C.SETID=F.SETID AND C.ITEM_TYPE=F.ITEM_TYPE AND F.EFFDT <= %CurrentDateIn AND F.EFF_STATUS='A')

  • Related Language Record: PMTORGEMPLVWLNG

    • # 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 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 EXT_ORG_ID Character(11) VARCHAR2(11) NOT NULL External Org ID
    5 ITEM_NBR Character(15) VARCHAR2(15) NOT NULL Item Nbr
    6 PAYMENT_ID_NBR Number(12,0) DECIMAL(12) NOT NULL Payment ID Nbr
    7 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
    8 STRM Character(4) VARCHAR2(4) NOT NULL Term
    9 DESCRSHORT Character(10) VARCHAR2(10) NOT NULL Short Description
    10 ITEM_TYPE Character(12) VARCHAR2(12) NOT NULL Item Type
    11 ITEM_AMT Signed Number(18,2) DECIMAL(16,2) NOT NULL Item Amount
    12 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
    13 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    14 POSTED_DATE Date(10) DATE Date Posted
    15 SF_REVERSAL_IND Character(1) VARCHAR2(1) NOT NULL Reversal Indicator
    16 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
    17 CONTRACT_NUM Character(25) VARCHAR2(25) NOT NULL Contract Number
    18 CONTRACT_EMPLID Character(11) VARCHAR2(11) NOT NULL Contract ID
    19 ACCOUNT_TERM Character(4) VARCHAR2(4) NOT NULL Account Term
    20 ACCOUNT_NBR Character(10) VARCHAR2(10) NOT NULL Account Nbr
    21 ITEM_BALANCE Signed Number(18,2) DECIMAL(16,2) NOT NULL Item Balance
    22 ITEM_STATUS Character(1) VARCHAR2(1) NOT NULL Item Status
    A=Active
    C=Completed
    I=Initiated
    N=Notified
    O=Ordered
    P=Paid Off
    R=Received
    S=Second Notification
    T=Returned
    W=Waived
    X=Cancelled
    23 LAST_ACTIVITY_DATE Date(10) DATE Last Activity Date
    24 ITEM_TERM Character(4) VARCHAR2(4) NOT NULL Item Term
    25 APPLIED_AMT Signed Number(18,2) DECIMAL(16,2) NOT NULL Applied Amount
    26 ACAD_YEAR Character(4) VARCHAR2(4) NOT NULL Academic Year
    27 ENCUMBERED_AMT Signed Number(18,2) DECIMAL(16,2) NOT NULL Amount Encumbered
    28 REF1_DESCR Character(30) VARCHAR2(30) NOT NULL Reference Nbr
    29 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
    30 STDNT_CAR_NBR Number(3,0) SMALLINT NOT NULL Student Career Nbr
    31 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
    32 CLASS_NBR Number(5,0) INTEGER NOT NULL Class Nbr
    33 FEE_CD Character(6) VARCHAR2(6) NOT NULL Fee Code
    34 SEL_GROUP Character(10) VARCHAR2(10) NOT NULL Tuition Group
    35 ADM_APPL_NBR Character(8) VARCHAR2(8) NOT NULL Application Nbr
    36 REFUND_NBR Number(12,0) DECIMAL(12) NOT NULL Refund Nbr
    37 RECEIPT_NBR Number(12,0) DECIMAL(12) NOT NULL Receipt Number
    38 ITEM_NBR_SOURCE Character(15) VARCHAR2(15) NOT NULL Item Nbr - Src Charge Nbr
    39 CUR_RT_TYPE Character(5) VARCHAR2(5) NOT NULL Currency Rate Type
    40 RATE_MULT Signed Number(17,8) DECIMAL(15,8) NOT NULL Rate Multiplier
    41 RATE_DIV Number(16,8) DECIMAL(15,8) NOT NULL Rate Divisor
    42 ORIGNL_CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
    43 ORIGNL_ITEM_AMT Signed Number(18,2) DECIMAL(16,2) NOT NULL Item Amount
    44 REFUND_EMPLID Character(11) VARCHAR2(11) NOT NULL Refund ID
    45 REFUND_EXT_ORG_ID Character(11) VARCHAR2(11) NOT NULL Refund External Org ID
    46 TAX_CD Character(8) VARCHAR2(8) NOT NULL Tax Code
    47 TAX_AUTHORITY_CD Character(3) VARCHAR2(3) NOT NULL Tax Authority
    48 PAYMENT_AMT Number(19,3) DECIMAL(18,3) NOT NULL Payment Amount