EMPLIDEMPL_RCDPAYGROUPERN_PROGRAMERNCDERNCD_SRCH_VW(SQL View) |
Index Back |
|---|---|
View for Earning Code prompt tView for Earning Code prompt table in order to show the earning codes in the earning program table. |
| SELECT DISTINCT A.EMPLID , A.EMPL_RCD , B.PAYGROUP , B.ERN_PROGRAM , D.ERNCD , E.DESCR , E.DESCRSHORT FROM PS_JOB A , PS_PAYGROUP_TBL B , PS_ERN_PROGRAM_TBL C , PS_ERN_PROGRAM_DEF D , PS_EARNINGS_TBL E WHERE A.HR_STATUS = 'A' AND A.COMPANY = B.COMPANY AND A.PAYGROUP = B.PAYGROUP AND B.ERN_PROGRAM = C.ERN_PROGRAM AND C.ERN_PROGRAM = D.ERN_PROGRAM AND C.EFFDT = ( SELECT MAX(F.EFFDT) FROM PS_ERN_PROGRAM_TBL F WHERE F.ERN_PROGRAM = C.ERN_PROGRAM) AND C.EFFDT = D.EFFDT AND D.ERNCD = E.ERNCD AND B.EFFDT <= ( SELECT MAX(J.EFFDT) FROM PS_JOB J WHERE J.EMPLID = A.EMPLID AND J.EMPL_RCD = A.EMPL_RCD AND J.EFFDT <= A.EFFDT ) |
| # | 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 Rcd Nbr |
| 3 | PAYGROUP |
Character(3) | VARCHAR2(3) NOT NULL | Pay Group |
| 4 | ERN_PROGRAM |
Character(3) | VARCHAR2(3) NOT NULL | Earnings Program ID |
| 5 | ERNCD |
Character(3) | VARCHAR2(3) NOT NULL | Earnings Code |
| 6 | DESCR | Character(30) | VARCHAR2(30) NOT NULL | Description |
| 7 | DESCRSHORT | Character(10) | VARCHAR2(10) NOT NULL | Short Description |