EMPLIDEMPL_RCDEFFDTEFFSEQCAN=Cancelled
COM=Completed
COR=Corrected
IRR=IRR Reported
WIP=Work-in-progress
A=Applied at NFC
I=Auto Action Inserted
N=Not Ready to Process
P=In Process
R=Ready To Process
S=SINQ Error
Z=Other
GVT_PI_AWD_VW(SQL View) |
Index Back |
|---|---|
Awards View for Federal PYI |
| SELECT B.EMPLID ,B.EMPL_RCD ,B.EFFDT ,B.EFFSEQ ,D.OPRID ,A.NAME ,NID.NATIONAL_ID ,C.GVT_NOA_CODE ,C.GVT_LEG_AUTH_1 ,C.GVT_LEG_AUTH_2 ,C.ACTION_DT ,C.COMPANY ,C.GVT_SUB_AGENCY ,C.PAYGROUP ,C.GVT_POI ,C.GVT_STATUS_TYPE ,B.GVT_SUGGESTION_NBR ,B.GVT_TANG_BEN_AMT ,B.GVT_INTANG_BEN_AMT ,B.GOAL_AMT ,B.GVT_AWARD_GROUP ,B.ACCT_CD ,C.ACTION_REASON ,B.OTH_HRS ,C.GVT_PI_UPD_IND FROM PS_GVT_NM_DT_VW A ,PS_GVT_AWD_DATA B ,PS_GVT_JOB C ,PS_GVT_EE_DATA_TRK D ,PS_GVT_PERS_NID NID WHERE B.EMPLID = A.EMPLID AND B.EMPL_RCD = A.EMPL_RCD AND B.EFFDT = A.EFFDT AND B.EFFSEQ = A.EFFSEQ AND B.EMPLID = C.EMPLID AND B.EMPL_RCD = C.EMPL_RCD AND B.EFFDT = C.EFFDT AND B.EFFSEQ = C.EFFSEQ AND C.GVT_STATUS_TYPE IN ('CAN','COR','COM') AND C.EMPLID = D.EMPLID AND C.EMPL_RCD = D.EMPL_RCD AND C.EFFDT = D.EFFDT AND C.EFFSEQ = D.EFFSEQ AND D.GVT_TRK_SEQUENCE = ( SELECT MAX(GVT_TRK_SEQUENCE) FROM PS_GVT_EE_DATA_TRK E WHERE E.EMPLID = C.EMPLID AND E.EMPL_RCD = C.EMPL_RCD AND E.EFFDT = C.EFFDT AND E.EFFSEQ = C.EFFSEQ) AND B.EMPLID = NID.EMPLID AND B.EMPL_RCD = NID.EMPL_RCD AND B.EFFDT = NID.EFFDT AND B.EFFSEQ = NID.EFFSEQ |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | EMPLID |
Character(11) | VARCHAR2(11) NOT NULL | Employee ID |
| 2 | EMPL_RCD |
Number(3,0) | SMALLINT NOT NULL | Empl Record |
| 3 | EFFDT |
Date(10) | DATE | Effective Date |
| 4 | EFFSEQ |
Number(3,0) | SMALLINT NOT NULL | Effective Sequence |
| 5 | OPRID | Character(30) | VARCHAR2(30) NOT NULL | A user's ID (see PSOPRDEFN). |
| 6 | NAME | Character(50) | VARCHAR2(50) NOT NULL | Name |
| 7 | NATIONAL_ID | Character(20) | VARCHAR2(20) NOT NULL | National ID |
| 8 | GVT_NOA_CODE | Character(3) | VARCHAR2(3) NOT NULL | Nature of Action Code |
| 9 | GVT_LEG_AUTH_1 | Character(3) | VARCHAR2(3) NOT NULL | Legal Authority (1) |
| 10 | GVT_LEG_AUTH_2 | Character(3) | VARCHAR2(3) NOT NULL | Legal Authority (2) |
| 11 | ACTION_DT | Date(10) | DATE | Action Date |
| 12 | COMPANY | Character(3) | VARCHAR2(3) NOT NULL | Company |
| 13 | GVT_SUB_AGENCY | Character(2) | VARCHAR2(2) NOT NULL | Sub-Agency |
| 14 | PAYGROUP | Character(3) | VARCHAR2(3) NOT NULL | Pay Group |
| 15 | GVT_POI | Character(4) | VARCHAR2(4) NOT NULL | Personnel Office ID |
| 16 | GVT_STATUS_TYPE | Character(3) | VARCHAR2(3) NOT NULL |
Status Type
CAN=Cancelled COM=Completed COR=Corrected IRR=IRR Reported WIP=Work-in-progress |
| 17 | GVT_SUGGESTION_NBR | Character(10) | VARCHAR2(10) NOT NULL | Suggestion number. |
| 18 | GVT_TANG_BEN_AMT | Number(8,0) | INTEGER NOT NULL | Tangible benefit amount. |
| 19 | GVT_INTANG_BEN_AMT | Number(8,0) | INTEGER NOT NULL | Intangible benefit amount |
| 20 | GOAL_AMT | Signed Number(12,2) | DECIMAL(10,2) NOT NULL | Goal Amount |
| 21 | GVT_AWARD_GROUP | Character(4) | VARCHAR2(4) NOT NULL | Award group |
| 22 | ACCT_CD | Character(25) | VARCHAR2(25) NOT NULL | Combination Code |
| 23 | ACTION_REASON | Character(3) | VARCHAR2(3) NOT NULL | Reason Code |
| 24 | OTH_HRS | Signed Number(8,2) | DECIMAL(6,2) NOT NULL | Other hours. |
| 25 | GVT_PI_UPD_IND | Character(1) | VARCHAR2(1) NOT NULL |
PI upd ind
A=Applied at NFC I=Auto Action Inserted N=Not Ready to Process P=In Process R=Ready To Process S=SINQ Error Z=Other |