GB_GROUP_IDPrompt Table: GROUP_INCR_CNTL
CURRENCY_CDBUDGET_START_DTGRP_SAL_VW(SQL View) |
Index Back |
|---|---|
Grp Salary Totals/Bdgt StartGRP_SAL_VW is a view which retrieves the annual salaries, headcount, and FTE of each employee as of the budget start date in the Salary Planning application. |
| SELECT A.GB_GROUP_ID ,B.CURRENCY_CD ,A.BUDGET_START_DT ,SUM(B.ANNUAL_RT) ,SUM(B.FTE) ,COUNT(*) FROM PS_GROUP_INCR_CNTL A ,PS_SAL_PLN_GRP_RES R ,PS_JOB B ,PS_PER_ORG_ASGN_VW C ,PS_PERSON P WHERE A.GB_GROUP_ID = R.GB_GROUP_ID AND R.VERSIONGBQDM = ( SELECT MAX(R2.VERSIONGBQDM) FROM PS_SAL_PLN_GRP_RES R2 WHERE R.GB_GROUP_ID = R2.GB_GROUP_ID) AND R.EMPLID = B.EMPLID AND R.EMPL_RCD = B.EMPL_RCD AND B.EFFDT = R.JOB_EFFDT AND B.EFFSEQ = R.JOB_EFFSEQ AND B.EFFDT = ( SELECT MAX(R1.EFFDT) FROM PS_JOB R1 WHERE B.EMPLID = R1.EMPLID AND B.EMPL_RCD = R1.EMPL_RCD AND R1.EFFDT <= A.SAL_PLAN_START_DT) AND B.EFFSEQ = ( SELECT MAX(R3.EFFSEQ) FROM PS_JOB R3 WHERE B.EMPLID = R3.EMPLID AND B.EMPL_RCD = R3.EMPL_RCD AND B.EFFDT = R3.EFFDT) AND B.EMPLID = P.EMPLID AND C.PER_ORG = 'EMP' AND B.HR_STATUS = 'A' AND C.EMPLID = B.EMPLID AND C.EMPL_RCD = B.EMPL_RCD AND ( (A.ANNIVERSARY_SW = 'N' AND C.LAST_HIRE_DT <=A.NEW_HIRE_ELIG_DT) OR (A.ANNIVERSARY_SW = 'Y' AND C.LAST_HIRE_DT < A.BUDGET_START_DT ) ) GROUP BY A.GB_GROUP_ID, B.CURRENCY_CD,A.BUDGET_START_DT |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | GB_GROUP_ID |
Character(15) | VARCHAR2(15) NOT NULL |
Group Build ID.
Prompt Table: GROUP_INCR_CNTL |
| 2 | CURRENCY_CD |
Character(3) | VARCHAR2(3) NOT NULL | Currency Code |
| 3 | BUDGET_START_DT |
Date(10) | DATE NOT NULL | Budget Start Date |
| 4 | TOTAL_SALARIES | Signed Number(20,3) | DECIMAL(18,3) NOT NULL | Total Annual Salaries |
| 5 | TOTAL_FTE_UNROUND | Number(16,11) | DECIMAL(15,11) NOT NULL | Total Unrounded FTE |
| 6 | TOTAL_COUNT | Number(7,0) | INTEGER NOT NULL | Total Count |