EMPLIDEMPL_RCDACTION_DT_SSEFFSEQHR_GSS_MASAL_VW(SQL View) |
Index Back |
|---|---|
HTML Email - GSSContains HTML Email Approval new information for the GSS transactions. |
| SELECT DISTINCT A.EMPLID , A.EMPL_RCD , A.ACTION_DT_SS , A.EFFSEQ , EMP.NAME_DISPLAY , REQ.NAME_DISPLAY , A.COMPRATE , A.ANNUAL_RT , B.COMPRATE , B.ANNUAL_RT , RSN.DESCR , B.JOBCODE , CAST(STA.COMMENTS AS VARCHAR(4000)) ,B.CURRENCY_CD , B.BUSINESS_UNIT FROM PS_HR_MSS_CT_DAT A , PS_PERSON_NAME EMP , PS_PERSON_NAME REQ , PSOPRDEFN OPR , PS_ACTN_REASON_TBL RSN ,PS_HR_MSS_CT_STA STA , PS_JOB B WHERE A.TRANSACTION_NAME='HR_SALARY_CHANGE' AND A.EMPLID = EMP.EMPLID AND A.ORIGINATORID = OPR.OPRID AND OPR.EMPLID =REQ.EMPLID AND RSN.ACTION =A.ACTION AND RSN.ACTION_REASON =A.ACTION_REASON_SS AND %EffdtCheck(ACTN_REASON_TBL RSN1, RSN, %Currentdatein) AND RSN.EFF_STATUS = 'A' AND A.EMPLID = B.EMPLID AND A.EMPL_RCD = B.EMPL_RCD AND B.EFFDT = ( SELECT MAX(B1.EFFDT) FROM PS_JOB B1 WHERE B.EMPLID = B1.EMPLID AND B.EMPL_RCD = B1.EMPL_RCD AND B1.EFFDT=B1.EFFDT AND B1.EFFDT <= %CurrentDateIn) AND B.EFFSEQ = ( SELECT MAX(B2.EFFSEQ) FROM PS_JOB B2 WHERE B2.EMPLID = B.EMPLID AND B2.EFFDT = B.EFFDT) AND A.EMPLID = STA.EMPLID AND A.EMPL_RCD = STA.EMPL_RCD AND A.ACTION_DT_SS = STA.ACTION_DT_SS AND A.EFFSEQ = STA.EFFSEQ AND STA.SEQNO=1 |
| # | 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 | ACTION_DT_SS |
Date(10) | DATE | Termination Date |
| 4 | EFFSEQ |
Number(3,0) | SMALLINT NOT NULL | Effective Sequence |
| 5 | EMPLNAME | Character(50) | VARCHAR2(50) NOT NULL | EmplName |
| 6 | NAME_DESCR | Character(50) | VARCHAR2(50) NOT NULL | Description |
| 7 | COMPRATE | Number(19,6) | DECIMAL(18,6) NOT NULL | Compensation Rate |
| 8 | ANNUAL_RT | Number(19,3) | DECIMAL(18,3) NOT NULL | Annual Rate |
| 9 | COMPRATE2 | Number(22,6) | DECIMAL(21,6) NOT NULL | Comp Rate field for discounts and perks eligibility file. |
| 10 | ANNUAL_RT2 | Number(19,3) | DECIMAL(18,3) NOT NULL | Annual Rate |
| 11 | DESCR | Character(30) | VARCHAR2(30) NOT NULL | Description |
| 12 | DESCR_HCM5 | Character(30) | VARCHAR2(30) NOT NULL | This field is used display description |
| 13 | COMMENTS | Long Character | CLOB | Comment |
| 14 | CURRENCY_CD | Character(3) | VARCHAR2(3) NOT NULL | Currency Code |
| 15 | BUSINESS_UNIT | Character(5) | VARCHAR2(5) NOT NULL | Business Unit |