BUSINESS_UNITPROPOSAL_IDVERSION_IDEFFDTEMPLIDGM_IN_CONTAC_VW(SQL View) |
Index Back |
|---|---|
Institution Contact viewThis record is used to retrieve the officials that are associated with the primary institution of a primary project. There can be many officials for one Institution. |
| SELECT C.BUSINESS_UNIT, C.PROPOSAL_ID, C.VERSION_ID, B.EFFDT, B.EMPLID, D.NAME FROM PS_GM_IN_CONTACTS B, PS_GM_PROP_PROJ C, PS_PERS_DATA_EFFDT D WHERE C.PRIMARY_FLAG='Y' AND B.INSTID = C.INSTID AND B.SETID = (SELECT SETID FROM PS_SET_CNTRL_REC G WHERE G.SETCNTRLVALUE = C.BUSINESS_UNIT AND G.RECNAME='GM_IN_CONTACTS') AND D.EMPLID = B.EMPLID AND D.EFFDT=(SELECT MAX(EFFDT) FROM PS_PERS_DATA_EFFDT D1 WHERE D1.EMPLID=D.EMPLID AND D1.EFFDT <= B.EFFDT) |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | BUSINESS_UNIT |
Character(5) | VARCHAR2(5) NOT NULL | Business Unit |
| 2 | PROPOSAL_ID |
Character(25) | VARCHAR2(25) NOT NULL | Proposal ID |
| 3 | VERSION_ID |
Character(7) | VARCHAR2(7) NOT NULL | Version ID |
| 4 | EFFDT |
Date(10) | DATE | Effective Date |
| 5 | EMPLID |
Character(11) | VARCHAR2(11) NOT NULL | Employee ID |
| 6 | NAME | Character(50) | VARCHAR2(50) NOT NULL | Name |