DEPT_TBL_EFF_VW

(SQL View)
 Index Back

Dept - Current Eff Dt View

View to select current effective rows record DEPT_TBL.

SELECT A.SETID , A.DEPTID , A.EFFDT , A.EFF_STATUS , A.DESCR , A.DESCRSHORT ,COMPANY ,SETID_LOCATION ,LOCATION ,TAX_LOCATION_CD ,MANAGER_ID , MANAGER_POSN ,BUDGET_YR_END_DT ,BUDGET_LVL ,GL_EXPENSE , %subrec(DEPT_TBLUSA_SBR,a) , %subrec(DEPT_TBLCAN_SBR,a) , %subrec(DEPT_TBLGER_SBR,a) , %subrec(DEPT_TBLFRA_SBR,a) , %subrec(DEPT_TBLFED_SBR,a) ,FTE_EDIT_INDC , DEPT_TENURE_FLG , TL_DISTRIB_INFO , USE_BUDGETS , USE_ENCUMBRANCES , USE_DISTRIBUTION , BUDGET_DEPTID , DIST_PRORATE_OPTN , HP_STATS_DEPT_CD , HP_STATS_FACULTY , %subrec(DEPT_TBLERP_SBR,a) FROM PS_DEPT_TBL A WHERE A.EFFDT = ( SELECT MAX(B.EFFDT) FROM PS_DEPT_TBL B WHERE B.SETID = A.SETID AND B.DEPTID = A.DEPTID AND B.EFFDT <= %CurrentDateIn) AND A.EFF_STATUS = 'A'

  • Related Language Record: DEPT_TBL_LANG

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 SETID Character(5) VARCHAR2(5) NOT NULL This field is used to store the value of SetID on various setup tables.

    Prompt Table: SP_SETID_NONVW

    2 DEPTID Character(15) VARCHAR2(15) NOT NULL Department
    3 EFFDT Date(10) DATE NOT NULL Effective Date

    Default Value: %date

    4 EFF_STATUS Character(1) VARCHAR2(1) NOT NULL Effective Status
    A=Active
    I=Inactive

    Default Value: A

    5 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    6 DESCRSHORT Character(10) VARCHAR2(10) NOT NULL Short Description
    7 COMPANY Character(3) VARCHAR2(3) NOT NULL Company
    8 SETID_LOCATION Character(5) VARCHAR2(5) NOT NULL Location SetID

    Prompt Table: SP_SETID_NONVW

    9 LOCATION Character(15) VARCHAR2(15) NOT NULL This field stores the Location Code.

    Prompt Table: SET_LOC_VW

    10 TAX_LOCATION_CD Character(10) VARCHAR2(10) NOT NULL Tax Location Code field used in HD_360_RESPONSE_SYNC message.
    11 MANAGER_ID Character(11) VARCHAR2(11) NOT NULL Manager ID

    Prompt Table: RB_EMPLID_VW

    12 MANAGER_POSN Character(8) VARCHAR2(8) NOT NULL Manager Position
    13 BUDGET_YR_END_DT Number(4,0) SMALLINT NOT NULL Budget Year End Date
    14 BUDGET_LVL Character(1) VARCHAR2(1) NOT NULL Budget Level
    D=Department
    J=Job Code
    L=Line Item
    N=None
    P=Position Number

    Default Value: N

    15 GL_EXPENSE Character(35) VARCHAR2(35) NOT NULL GL Account #-Expense
    16 EEO4_FUNCTION Character(2) VARCHAR2(2) NOT NULL U.S. EEO4 Function
    17 CAN_IND_SECTOR Character(3) VARCHAR2(3) NOT NULL Canadian Industrial Sector
    000=Federal Crowns
    031=Fishing Industries
    061=Metal Mines
    071=Crude Petroleum & Natural Gas
    091=Service to Petro and Gas
    105=Flour Cereal Food & Feed
    451=Air Transport Industries
    452=Service to Air Transportation
    453=Railway Transport & Related
    454=Water Transport Industries
    455=Servc to Water Transportation
    456=Truck Transport Industries
    457=Public Passenger Transit
    461=Pipeline Transport Industries
    471=Grain Elevator Industry
    481=Telecommunication Broadcasting
    482=Telecommunications Carriers
    484=Postal & Courier Service
    701=Central Bank
    702=Chartered Banks, Other Banking
    779=Other Business Services
    18 ACCIDENT_INS Character(3) VARCHAR2(3) NOT NULL Accident Insurance
    19 SI_ACCIDENT_NUM Character(15) VARCHAR2(15) NOT NULL Social Insurance Accident #
    20 HAZARD Character(4) VARCHAR2(4) NOT NULL Hazard
    21 ESTABID Character(12) VARCHAR2(12) NOT NULL Establishment ID
    22 RISKCD Character(6) VARCHAR2(6) NOT NULL Risk Code
    23 GVT_DESCR40 Character(40) VARCHAR2(40) NOT NULL Description
    24 GVT_SUB_AGENCY Character(2) VARCHAR2(2) NOT NULL Sub-Agency
    25 GVT_PAR_LINE2 Character(10) VARCHAR2(10) NOT NULL PAR Line 2 Department ID

    Prompt Table: DEPT_TBL

    26 GVT_PAR_LINE3 Character(10) VARCHAR2(10) NOT NULL PAR Line 3 Department ID

    Prompt Table: DEPT_TBL

    27 GVT_PAR_LINE4 Character(10) VARCHAR2(10) NOT NULL PAR Line 4 Department ID

    Prompt Table: DEPT_TBL

    28 GVT_PAR_LINE5 Character(10) VARCHAR2(10) NOT NULL PAR Line 5 Department ID

    Prompt Table: DEPT_TBL

    29 GVT_PAR_DESCR2 Character(40) VARCHAR2(40) NOT NULL PAR Line 2 Description
    30 GVT_PAR_DESCR3 Character(40) VARCHAR2(40) NOT NULL PAR Line 3 Description
    31 GVT_PAR_DESCR4 Character(40) VARCHAR2(40) NOT NULL PAR Line 4 Description
    32 GVT_PAR_DESCR5 Character(40) VARCHAR2(40) NOT NULL PAR Line 5 Description
    33 FTE_EDIT_INDC Character(1) VARCHAR2(1) NOT NULL FTE Edit Indicator
    H=Error
    N=No Edit
    S=Warning

    Default Value: N

    34 DEPT_TENURE_FLG Character(1) VARCHAR2(1) NOT NULL Can Grant Tenure

    Y/N Table Edit

    Default Value: N

    35 TL_DISTRIB_INFO Character(1) VARCHAR2(1) NOT NULL Use TL Distribution?

    Y/N Table Edit

    Default Value: N

    36 USE_BUDGETS Character(1) VARCHAR2(1) NOT NULL Use Budgeting Feature?

    Y/N Table Edit

    Default Value: N

    37 USE_ENCUMBRANCES Character(1) VARCHAR2(1) NOT NULL Use Encumbrance Processing?

    Y/N Table Edit

    Default Value: N

    38 USE_DISTRIBUTION Character(1) VARCHAR2(1) NOT NULL Use Paycheck Distribution?

    Y/N Table Edit

    Default Value: N

    39 BUDGET_DEPTID Character(15) VARCHAR2(15) NOT NULL Budget with Department
    40 DIST_PRORATE_OPTN Character(1) VARCHAR2(1) NOT NULL Distribution Prorate Option
    C=Calendar Days
    N=Not Applicable
    W=Work Days

    Default Value: N

    41 HP_STATS_DEPT_CD Character(3) VARCHAR2(3) NOT NULL Stats Can Dept Code
    42 HP_STATS_FACULTY Character(5) VARCHAR2(5) NOT NULL Faculty Code
    43 MANAGER_NAME Character(30) VARCHAR2(30) NOT NULL Manager Name
    44 ACCOUNTING_OWNER Character(30) VARCHAR2(30) NOT NULL Accounting Owner
    45 COUNTRY_GRP Character(18) VARCHAR2(18) NOT NULL Country Group