EX_EXC5600_L_VW

(SQL View)
 Index Back

EX ADV Transactions Dept. View

Expenses Cash Advance Transactions by Department View. Used by queries for Crystal Report generation

SELECT A.ADVANCE_ID , E1.LANGUAGE_CD , A.EMPLID , A.REFERENCE_ID , A.BUSINESS_PURPOSE , B.ADVANCE_SOURCE , C.BUSINESS_UNIT_GL , C.ACCOUNT , C.ALTACCT , C.DEPTID , %subrec(CF16_AN_SBR,C) , C.PROJECT_ID , C.MONETARY_AMOUNT , C.CURRENCY_CD , D.NAME , E1.DESCR , F1.DESCRSHORT , G1.DESCR FROM PS_EX_ADV_HDR A , PS_EX_ADV_LINE B , PS_EX_ADV_DIST C , PS_PERSONAL_DATA D , PS_DEPT_TBL E , PS_DEPT_TBL_LANG E1 , PS_SET_CNTRL_REC E2 , PS_GL_ACCOUNT_TBL F , PS_GL_ACCOUNT_LANG F1 , PS_SET_CNTRL_REC F2 , PS_EX_PURPOSE_TBL G , PS_EX_PURPOSE_LNG G1 WHERE E.DEPTID = C.DEPTID AND E1.DEPTID=C.DEPTID AND E1.SETID=E.SETID AND E2.SETCNTRLVALUE = C.BUSINESS_UNIT_GL AND E2.RECNAME = 'DEPT_TBL' AND E2.SETID = E.SETID AND F.ACCOUNT = C.ACCOUNT AND F1.ACCOUNT=C.ACCOUNT AND F1.SETID=F.SETID AND F2.SETCNTRLVALUE = C.BUSINESS_UNIT_GL AND F2.RECNAME = 'GL_ACCOUNT_TBL' AND F2.SETID = F.SETID AND G.BUSINESS_PURPOSE = A.BUSINESS_PURPOSE AND G.SETID = A.SETID AND G1.BUSINESS_PURPOSE=A.BUSINESS_PURPOSE AND G1.SETID=A.SETID AND A.ADVANCE_ID = B.ADVANCE_ID AND B.ADVANCE_ID = C.ADVANCE_ID AND B.LINE_NBR = C.LINE_NBR AND A.EMPLID = D.EMPLID AND E.EFFDT = ( SELECT MAX(EFFDT) FROM PS_DEPT_TBL WHERE E.SETID = SETID AND E.DEPTID = DEPTID AND EFFDT <= %CurrentDateIn) AND E.EFF_STATUS = 'A' AND F.EFFDT = ( SELECT MAX(EFFDT) FROM PS_GL_ACCOUNT_TBL WHERE F.SETID = SETID AND F.ACCOUNT = ACCOUNT AND EFFDT <= %CurrentDateIn) AND F.EFF_STATUS = 'A' AND G.EFFDT = ( SELECT MAX(EFFDT) FROM PS_EX_PURPOSE_TBL WHERE G.SETID = SETID AND G.BUSINESS_PURPOSE = BUSINESS_PURPOSE AND EFFDT <= %CurrentDateIn) AND G.EFF_STATUS = 'A'

  • Related Language Record for EX_EXC5600_VW

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 ADVANCE_ID Character(10) VARCHAR2(10) NOT NULL Advance ID
    2 LANGUAGE_CD Character(3) VARCHAR2(3) NOT NULL Language Code
    3 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
    4 REFERENCE_ID Character(10) VARCHAR2(10) NOT NULL Reference ID
    5 BUSINESS_PURPOSE Character(5) VARCHAR2(5) NOT NULL Business Purpose
    6 ADVANCE_SOURCE Character(4) VARCHAR2(4) NOT NULL Advance Source
    7 BUSINESS_UNIT_GL Character(5) VARCHAR2(5) NOT NULL GL Business Unit
    8 ACCOUNT Character(10) VARCHAR2(10) NOT NULL Account
    9 ALTACCT Character(10) VARCHAR2(10) NOT NULL Alternate Account
    10 DEPTID Character(10) VARCHAR2(10) NOT NULL Department

    Prompt Table: DEPTID_BUGL_VW

    11 OPERATING_UNIT Character(8) VARCHAR2(8) NOT NULL Operating Unit ChartField

    Prompt Table: OPERUNT_BUGL_VW

    12 PRODUCT Character(6) VARCHAR2(6) NOT NULL Product ChartField

    Prompt Table: PRODUCT_BUGL_VW

    13 FUND_CODE Character(5) VARCHAR2(5) NOT NULL Fund Code

    Prompt Table: FUND_BUGL_VW

    14 CLASS_FLD Character(5) VARCHAR2(5) NOT NULL Class Field

    Prompt Table: CLASSCF_BUGL_VW

    15 PROGRAM_CODE Character(5) VARCHAR2(5) NOT NULL Program Code ChartField

    Prompt Table: PROGRAM_BUGL_VW

    16 BUDGET_REF Character(8) VARCHAR2(8) NOT NULL Budget Reference

    Prompt Table: BUD_REF_BUGL_VW

    17 AFFILIATE Character(5) VARCHAR2(5) NOT NULL Affiliate

    Prompt Table: AFFILIATE_VW

    18 AFFILIATE_INTRA1 Character(10) VARCHAR2(10) NOT NULL IntraUnit Affiliate1

    Prompt Table: %EDIT_INTRA01

    19 AFFILIATE_INTRA2 Character(10) VARCHAR2(10) NOT NULL Operating Unit Affiliate

    Prompt Table: %EDIT_INTRA02

    20 CHARTFIELD1 Character(10) VARCHAR2(10) NOT NULL Expansion chartfield 1

    Prompt Table: CF1_BUGL_VW

    21 CHARTFIELD2 Character(10) VARCHAR2(10) NOT NULL Expansion Chartfield 2

    Prompt Table: CF2_BUGL_VW

    22 CHARTFIELD3 Character(10) VARCHAR2(10) NOT NULL Expansion Chartfield 3

    Prompt Table: CF3_BUGL_VW

    23 PROJECT_ID Character(15) VARCHAR2(15) NOT NULL Project Id ChartField
    24 MONETARY_AMOUNT Signed Number(28,3) DECIMAL(26,3) NOT NULL Specifies the monetary amount of a debit or credit in the business unit base currency. Debit entries are positive and credit entries are negative. This amount is only zero if associated with a statistical account.
    25 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
    26 NAME Character(50) VARCHAR2(50) NOT NULL Name
    27 DESCR_DEPTID Character(30) VARCHAR2(30) NOT NULL (Department Name)
    28 DESCRSHORT Character(10) VARCHAR2(10) NOT NULL Short Description
    29 DESCR Character(30) VARCHAR2(30) NOT NULL Description