GP_ABS_APPR_VW(SQL View) |
Index Back |
|---|---|
Absence SS according to AWEView to retrieve Absence Events belong to a Manager using AWE tables. |
| SELECT DISTINCT R.EMPLID , R.EMPL_RCD , Z.NAME , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' , 'N' , R.TRANSACTION_NBR , R.BGN_DT , R.PIN_TAKE_NUM , R.END_DT , R.ORIG_EMPLID , R.ORIG_EMPL_RCD , R.ORIG_BEGIN_DT , R.ORIG_PIN_TAKE_NUM , R.ORIG_END_DT , R.ELIG_GRP , C.JOBTITLE , R.WF_STATUS , R.DURATION_ABS , R.ACTION_DT_SS , S.EOAWSTEP_STATUS , R.ABS_ENTRY_SRC , S.OPRID , R.MANAGER_APPR_IND , 'REQ' , X.EOAWPRCS_ID FROM PS_GP_ABSSS_V_XREF X , PS_EOAW_STEP_VW S , PS_GP_ABSSS_PNDG_2 R , PS_GP_ABS_EEJOB_VW C , PS_PERSON_NAME Z WHERE X.EOAWTHREAD_ID = S.EOAWTHREAD_ID AND X.EOAWPRCS_ID = S.EOAWPRCS_ID AND R.TRANSACTION_NBR = X.TRANSACTION_NBR AND R.EMPLID = Z.EMPLID AND R.EMPLID = C.EMPLID AND R.EMPL_RCD = C.EMPL_RCD AND X.EOAWPARENT_THREAD = ( SELECT MAX(Y.EOAWPARENT_THREAD) FROM PS_GP_ABSSS_V_XREF Y WHERE X.TRANSACTION_NBR = Y.TRANSACTION_NBR AND Y.EOAWTHREAD_STATUS <> 'T') UNION SELECT DISTINCT A.EMPLID , A.EMPL_RCD , NM.NAME , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' , 'N' , A.TRANSACTION_NBR , A.BGN_DT , A.PIN_TAKE_NUM , A.END_DT , B.ORIG_EMPLID , B.ORIG_EMPL_RCD , B.ORIG_BEGIN_DT , B.ORIG_PIN_TAKE_NUM , B.ORIG_END_DT , B.ELIG_GRP , EJ.JOBTITLE , A.WF_STATUS , A.DURATION_ABS , A.ACTION_DT_SS , ES.EOAWSTEP_STATUS , A.ABS_ENTRY_SRC , ES.OPRID , A.MANAGER_APPR_IND , 'REQ' , XRF.EOAWPRCS_ID FROM PS_GP_ABS_EVENT A , PS_GP_ABS_SS_DAT B , PS_TL_APP_RPT_LINE C , PS_PERSON_NAME NM , PS_GP_ABS_EEJOB_VW EJ , PS_TL_APP_RPTD_XRF XRF , PS_EOAW_STEP_VW ES WHERE A.WF_STATUS NOT IN ('C') AND A.PRC_EVT_ACTN_OPTN <> 'V' AND A.VOIDED_IND <> 'Y' AND B.ORIG_EMPLID = A.EMPLID AND B.ORIG_EMPL_RCD = A.EMPL_RCD AND B.ORIG_BEGIN_DT = A.BGN_DT AND B.ORIG_END_DT = A.END_DT AND B.ORIG_PIN_TAKE_NUM = A.PIN_TAKE_NUM AND C.EMPLID = A.EMPLID AND C.EMPL_RCD = A.EMPL_RCD AND C.DUR = A.BGN_DT AND C.SEQ_NBR = A.PIN_TAKE_NUM AND C.END_DT = A.END_DT AND C.TRANSACTION_NBR = A.TRANSACTION_NBR AND C.TRANSACTIONID = ( SELECT MAX(C_ID.TRANSACTIONID) FROM PS_TL_APP_RPT_LINE C_ID WHERE C_ID.EMPLID = C.EMPLID AND C_ID.EMPL_RCD = C.EMPL_RCD AND C_ID.DUR = C.DUR AND C_ID.SEQ_NBR = C.SEQ_NBR AND C_ID.END_DT = C.END_DT AND C_ID.TRANSACTION_NBR = C.TRANSACTION_NBR) AND A.EMPLID = NM.EMPLID AND A.EMPLID = EJ.EMPLID AND A.EMPL_RCD = EJ.EMPL_RCD AND XRF.TRANSACTIONID = C.TRANSACTIONID AND XRF.EMPLID = A.EMPLID AND XRF.EMPL_RCD = A.EMPL_RCD AND XRF.DUR = C.DUR AND XRF.SEQ_NBR = C.SEQ_NBR AND XRF.EOAWTHREAD_ID = ES.EOAWTHREAD_ID AND XRF.EOAWPRCS_ID = ES.EOAWPRCS_ID AND XRF.EOAWPARENT_THREAD = ( SELECT MAX(XRF1.EOAWPARENT_THREAD) FROM PS_TL_APP_RPTD_XRF XRF1 WHERE XRF.TRANSACTIONID = XRF1.TRANSACTIONID AND XRF1.EMPLID = XRF.EMPLID AND XRF1.EMPL_RCD = XRF.EMPL_RCD AND XRF1.DUR = XRF.DUR AND XRF1.SEQ_NBR = XRF.SEQ_NBR AND XRF1.EOAWTHREAD_STATUS <> 'T') AND EXISTS ( SELECT 'X' FROM PS_INSTALLATION WHERE TL = 'Y') |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | EMPLID | Character(11) | VARCHAR2(11) NOT NULL |
Employee ID
Prompt Table: TL_EMPL_NAME_VW |
| 2 | EMPL_RCD | Number(3,0) | SMALLINT NOT NULL | Empl Record |
| 3 | NAME | Character(50) | VARCHAR2(50) NOT NULL | Name |
| 4 | LAST_NAME | Character(30) | VARCHAR2(30) NOT NULL | Last Name |
| 5 | FIRST_NAME | Character(30) | VARCHAR2(30) NOT NULL | First Name |
| 6 | BUSINESS_UNIT | Character(5) | VARCHAR2(5) NOT NULL |
Business Unit
Prompt Table: BUS_UNIT_TBL_HR |
| 7 | JOBCODE | Character(6) | VARCHAR2(6) NOT NULL | Job Code |
| 8 | JOB_DESCR | Character(30) | VARCHAR2(30) NOT NULL |
Job Description
Prompt Table: TL_JOB_DESC_VW |
| 9 | DEPTID | Character(10) | VARCHAR2(10) NOT NULL |
Department
Prompt Table: DEPT_TBL |
| 10 | DEPT_DESCR | Character(30) | VARCHAR2(30) NOT NULL | Department Description |
| 11 | SUPERVISOR_ID | Character(11) | VARCHAR2(11) NOT NULL |
Supervisor ID
Prompt Table: TL_SUPRVSR_VW |
| 12 | REPORTS_TO | Character(8) | VARCHAR2(8) NOT NULL |
Reports To Position Number
Prompt Table: TL_RPTS_TO_VW |
| 13 | LOCATION | Character(10) | VARCHAR2(10) NOT NULL |
Location Code
Prompt Table: LOCATION_TBL |
| 14 | LOC_DESCR_SS | Character(30) | VARCHAR2(30) NOT NULL | Location Description |
| 15 | COMPANY | Character(3) | VARCHAR2(3) NOT NULL |
Company
Prompt Table: COMPANY_TBL |
| 16 | PAYGROUP | Character(3) | VARCHAR2(3) NOT NULL |
North American Pay Group
Prompt Table: PAYGROUP_TBL |
| 17 | GP_PAYGROUP | Character(10) | VARCHAR2(10) NOT NULL | Global Payroll pay group |
| 18 | WORKGROUP | Character(10) | VARCHAR2(10) NOT NULL |
Workgroup
Prompt Table: TL_WRKGRP_TBL |
| 19 | TASKGROUP | Character(10) | VARCHAR2(10) NOT NULL |
Taskgroup
Prompt Table: TL_TASKGRP_TBL |
| 20 | POSITION_NBR | Character(8) | VARCHAR2(8) NOT NULL | Position Number |
| 21 | SCHEDULE_GRP | Character(10) | VARCHAR2(10) NOT NULL | Scheduling Organizational Group Code |
| 22 | PROCESSED | Character(1) | VARCHAR2(1) NOT NULL |
Processed?
I=In Process N=Not Processed Y=Processed Y/N Table Edit Default Value: N |
| 23 | TRANSACTION_NBR | Number(15,0) | DECIMAL(15) NOT NULL | 08/02/99 NAS: Changed the size of this field from 10 to 15. Based on Jim Hillman's decision to make the field at a standard size. |
| 24 | BGN_DT | Date(10) | DATE | Begin Date |
| 25 | PIN_TAKE_NUM | Number(8,0) | INTEGER NOT NULL |
Absence Take
Prompt Table: GP_PIN_ABSTK_VW |
| 26 | END_DT | Date(10) | DATE | end date |
| 27 | ORIG_EMPLID | Character(11) | VARCHAR2(11) NOT NULL | Originating Emplid |
| 28 | ORIG_EMPL_RCD | Number(3,0) | SMALLINT NOT NULL | Empl Record |
| 29 | ORIG_BEGIN_DT | Date(10) | DATE | original begin date |
| 30 | ORIG_PIN_TAKE_NUM | Number(8,0) | INTEGER NOT NULL | Absence Take |
| 31 | ORIG_END_DT | Date(10) | DATE | Unadjusted End Date |
| 32 | ELIG_GRP | Character(10) | VARCHAR2(10) NOT NULL | eligibility group |
| 33 | JOBTITLE | Character(30) | VARCHAR2(30) NOT NULL | Job Title |
| 34 | 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 |
| 35 | DURATION_ABS | Number(7,2) | DECIMAL(6,2) NOT NULL | Duration field containing 2 decimals to handle absence requirements |
| 36 | ACTION_DT_SS | Date(10) | DATE | Termination Date |
| 37 | AWSTEP_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
Step Instance Status
A=Approved B=Pushed back C=Auto Approved D=Denied E=Administrator denied F=Deleted G=Bypassed K=Skipped L=Terminated M=Administrator approved N=Not Active P=Pending S=Skipped T=Terminated U=Auto approved X=Deleted Y=Bypassed |
| 38 | ABS_ENTRY_SRC | Character(1) | VARCHAR2(1) NOT NULL |
Absence Entry Source
1=Administrator Absence Event 2=Time & Labor 3=Third Party 4=Employee Absence Request 5=Manager Absence Request 6=Employee Timesheet 7=Manager Timesheet A=Employee Extended Absence B=Manager Extended Absence C=Administrator Extended Absence D=Employee Desktop Integration |
| 39 | OPRID | Character(30) | VARCHAR2(30) NOT NULL |
A user's ID (see PSOPRDEFN).
Prompt Table: PSOPRDEFN |
| 40 | MANAGER_APPR_IND | Character(1) | VARCHAR2(1) NOT NULL | Manager Approved |
| 41 | HGA_APPR_PRCS | Character(3) | VARCHAR2(3) NOT NULL |
Absence Approval Process Type
REQ - Absence Request
CAN - Absence Cancellation
CAN=Absence Cancelation REQ=Absence Request |
| 42 | EOAWPRCS_ID | Character(30) | VARCHAR2(30) NOT NULL | Approval Process ID |