GM_PRJ_DPH2_VW

(SQL View)
 Index Back

Project Dept History View

This view is based on the GM_PRJ_DEPT_HIS. This has been created to dynamically handle the related descriptions of the field values changed.This value handles the new value field.

SELECT BUSINESS_UNIT ,PROJECT_ID ,LASTUPDDTTM ,LASTUPDOPRID ,FIELDNAME ,NEWVALUE , CASE WHEN HIS.FIELDNAME='EMPLID' THEN ( SELECT A.NAME FROM PS_PERS_DATA_EFFDT A WHERE A.EMPLID = HIS.NEWVALUE AND %EffdtCheck(PERS_DATA_EFFDT A1,A,%CurrentDateIn)) WHEN (HIS.FIELDNAME ='DEPTID' OR HIS.FIELDNAME ='MAJOR_SUBDIVISION') THEN ( SELECT DESCR FROM PS_DEPT_TBL D WHERE D.SETID = ( SELECT SETID FROM PS_SET_CNTRL_REC WHERE RECNAME='DEPT_TBL' AND SETCNTRLVALUE =HIS.BUSINESS_UNIT) AND D.DEPTID= HIS.NEWVALUE AND %EffdtCheck(DEPT_TBL D1,D,%CurrentDateIn)) WHEN HIS.FIELDNAME='INSTID' THEN ( SELECT DESCR FROM PS_GM_INSTITUTION WHERE INSTID = HIS.NEWVALUE AND SETID = ( SELECT SETID FROM PS_SET_CNTRL_REC WHERE RECNAME='GM_INSTITUTION' AND SETCNTRLVALUE =HIS.BUSINESS_UNIT)) END FROM PS_GM_PRJ_DEPT_HIS HIS

  • Related Language Record: GM_PRJ_DPH2_LVW

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
    2 PROJECT_ID Character(15) VARCHAR2(15) NOT NULL Project Id ChartField
    3 LASTUPDDTTM DateTime(26) TIMESTAMP Specifies the date and time of the last update to an entry. This field is maintained by PeopleSoft and is used in a variety of contexts.
    4 LASTUPDOPRID Character(30) VARCHAR2(30) NOT NULL Specifies the User ID which made the last update to an entry. This field is maintained by PeopleSoft and is used in a variety of contexts.
    5 FIELDNAME Character(18) VARCHAR2(18) NOT NULL Field Name (see PSDBFIELD).
    6 NEWVALUE Character(65) VARCHAR2(65) NOT NULL New Value
    7 DESCR_LN2 Character(50) VARCHAR2(50) NOT NULL Description Line 2