PSA_SUPPLY_VW(SQL View) |
Index Back |
|---|---|
Supply View by Jobcode, BU |
| SELECT A.BUSINESS_UNIT , A.JOBCODE , COUNT(*) AS NO_OF_SUPPLY FROM PS_JOB A WHERE A.EFFDT = ( SELECT MAX(A_ED.EFFDT) FROM PS_JOB A_ED WHERE A.EMPLID = A_ED.EMPLID AND A.EMPL_RCD = A_ED.EMPL_RCD AND A_ED.EFFDT <= %CurrentDateIn ) AND A.EFFSEQ = ( SELECT MAX(A_ES.EFFSEQ) FROM PS_JOB A_ES WHERE A.EMPLID = A_ES.EMPLID AND A.EMPL_RCD = A_ES.EMPL_RCD AND A.EFFDT = A_ES.EFFDT) GROUP BY A.BUSINESS_UNIT, A.JOBCODE |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | BUSINESS_UNIT | Character(5) | VARCHAR2(5) NOT NULL |
Business Unit
Prompt Table: BUS_UNIT_TBL_HR |
| 2 | JOBCODE | Character(6) | VARCHAR2(6) NOT NULL |
Job Code
Prompt Table:
JOBCODE_TBL
|
| 3 | NO_OF_SUPPLY | Number(3,0) | SMALLINT NOT NULL | Number of Supply |