POSN_IDXCTL_VW(SQL View) |
Index Back |
|---|---|
Position Index Control ViewPosition Index Control View |
| SELECT A.POSITION_NBR , A.EFFDT , A.EFFSEQ , A.EFF_STATUS , A.WF_STATUS , CASE WHEN A.WF_STATUS = 'A' THEN INC.COUNT1 ELSE 0 END , A.MAX_HEAD_COUNT ,CASE WHEN A.WF_STATUS = 'A' AND A.MAX_HEAD_COUNT >= INC.COUNT1 THEN A.MAX_HEAD_COUNT - INC.COUNT1 ELSE 0 END , CASE WHEN INC.COUNT1 = 0 THEN 'O' WHEN INC.COUNT1 = A.MAX_HEAD_COUNT THEN 'F' WHEN INC.COUNT1 > A.MAX_HEAD_COUNT THEN 'V' WHEN INC.COUNT1 < A.MAX_HEAD_COUNT THEN 'P' END , CASE WHEN A.REPORTS_TO IS NOT NULL THEN ( SELECT COUNT(*) FROM PS_POSN_INCUMB_WS3 WS WHERE WS.POSITION_NBR=A.REPORTS_TO) ELSE 0 END ,CASE WHEN A.REPORTS_TO IS NOT NULL THEN CASE WHEN ( SELECT COUNT(*) FROM PS_POSN_INCUMB_WS3 WS WHERE WS.POSITION_NBR=A.REPORTS_TO) = 0 THEN 'Y' ELSE 'N' END ELSE 'I' END ,FTE.TOTAL_POSN_FTE,A.REPORTS_TO,A.BUSINESS_UNIT,A.DEPTID, A.LASTUPDDTTM FROM PS_POSITION_DATA_E A , PS_POSN_CNT_INC_VW INC , PS_POSN_TOT_FTE_VW FTE WHERE (/* Fetch Approved */ (A.WF_STATUS = 'A' AND A.EFF_STATUS = 'A' AND A.EFFDT= ( SELECT MAX(B.EFFDT) FROM PS_POSITION_DATA_E B WHERE A.POSITION_NBR = B.POSITION_NBR AND A.WF_STATUS = B.WF_STATUS AND B.EFFDT <= %CurrentDateIn) AND A.EFFSEQ =( SELECT MAX(C.EFFSEQ) FROM PS_POSITION_DATA_E C WHERE A.POSITION_NBR = C.POSITION_NBR AND A.WF_STATUS = C.WF_STATUS AND C.EFFDT=A.EFFDT )) OR /* OR Fetch Rows above Approved (if any) which are In Progress/Rework */ ( A.EFFDT = ( SELECT MAX(B.EFFDT) FROM PS_POSITION_DATA_E B WHERE A.POSITION_NBR = B.POSITION_NBR) AND A.EFFSEQ =( SELECT MAX(C.EFFSEQ) FROM PS_POSITION_DATA_E C WHERE A.POSITION_NBR = C.POSITION_NBR AND C.EFFDT=A.EFFDT ) AND A.WF_STATUS IN ('I','P'))) AND A.POSITION_NBR = INC.POSITION_NBR AND A.EFFDT = INC.EFFDT AND A.EFFSEQ = INC.EFFSEQ AND A.POSITION_NBR = FTE.POSITION_NBR AND A.EFFDT = FTE.EFFDT AND A.EFFSEQ = FTE.EFFSEQ |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 |  POSITION_NBR |
Character(8) | VARCHAR2(8) NOT NULL |
Position Number
Default Value: 00000000 |
| 2 | EFFDT |
Date(10) | DATE NOT NULL |
Effective Date
Default Value: %date |
| 3 | EFFSEQ |
Number(3,0) | SMALLINT NOT NULL | Effective Sequence |
| 4 | EFF_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
Effective Status
A=Active I=Inactive Default Value: A |
| 5 | WF_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
Workflow Status
A=Approved C=Cancelled D=Denied E=Error. Contact Administrator. F=Awaiting final approval I=In Approval Process M=Administrator is Processing N=Not Available P=Rework S=Submitted V=Data Saved |
| 6 | POSN_HEADCOUNT | Number(4,0) | SMALLINT NOT NULL | Position Headcount |
| 7 | MAX_HEAD_COUNT | Number(4,0) | SMALLINT NOT NULL | Max Head Count |
| 8 | CURR_HEAD_COUNT | Number(4,0) | SMALLINT NOT NULL | Current Head Count |
| 9 | POSN_DATA_MESSAGE | Character(1) | VARCHAR2(1) NOT NULL |
Headcount Status
F=Filled O=Open P=Partially Filled V=Overallocated |
| 10 | POSN_TOT_REP_INCUM | Number(8,0) | INTEGER NOT NULL | Incumbents in Reports To |
| 11 | POSITION_VACANT | Character(1) | VARCHAR2(1) NOT NULL |
Position Vacant
I=Not Applicable N=No Y=Yes |
| 12 | TOTAL_POSN_FTE | Number(13,2) | DECIMAL(12,2) NOT NULL | Total Position FTE |
| 13 | REPORTS_TO | Character(8) | VARCHAR2(8) NOT NULL | Reports To Position Number |
| 14 | BUSINESS_UNIT | Character(5) | VARCHAR2(5) NOT NULL | Business Unit |
| 15 | DEPTID | Character(10) | VARCHAR2(10) NOT NULL | Department |
| 16 | LASTUPDDTTM | DateTime(26) | TIMESTAMP | Specifies the date and time of the last update to an entry. This field is maintained by PeopleSoft and is used in a variety of contexts. |