PROCESS_INSTANCETL_AWE_CHILDEMPLIDPrompt Table: TL_ADM_NAME_VW
EMPL_RCDPrompt Table: TL_ADM_EERCD_VW
EOAWDEFN_IDTL_AWE_RUNVW2(SQL View) |
Index Back |
|---|---|
Run control for AWE Approvals.Run control for AWE Approvals. |
| SELECT R.PROCESS_INSTANCE ,R.OPRID ,P1.RUNCNTLID ,X1.XLATLONGNAME ,R.PRCSINSTANCE ,P2.RUNCNTLID ,X2.XLATLONGNAME ,R.EMPLID ,R.EMPL_RCD ,R.EOAWDEFN_ID ,R.TRANSACTIONID ,R.BEGINDTTM ,R.ENDDTTM ,R.ENDDTTM - R.BEGINDTTM ,N.NAME ,J.SUPERVISOR_ID ,J.REPORTS_TO ,J.DEPTID ,R.APPROVAL_ROWS ,R.FROMDATE ,R.TO_DT FROM PS_TL_AWE_RUNCTL R ,PS_TL_AWE_PRCSRQST P1 ,PSXLATITEM X1 ,PS_TL_AWE_PRCSRQST P2 ,PSXLATITEM X2 ,PS_JOB J ,PS_NAMES N WHERE P1.PRCSINSTANCE = R.PROCESS_INSTANCE AND P2.PRCSINSTANCE = R.PRCSINSTANCE AND X1.FIELDNAME = 'RUNSTATUS' AND X1.FIELDVALUE = P1.RUNSTATUS AND X2.FIELDNAME = 'RUNSTATUS' AND X2.FIELDVALUE = P2.RUNSTATUS AND X1.EFFDT = ( SELECT MAX(M1.EFFDT) FROM PSXLATITEM M1 WHERE M1.FIELDNAME = X1.FIELDNAME AND M1.FIELDVALUE = X1.FIELDVALUE AND M1.EFFDT <= %DatePart(P1.RUNDTTM)) AND X1.EFF_STATUS = 'A' AND X2.EFFDT = ( SELECT MAX(M2.EFFDT) FROM PSXLATITEM M2 WHERE M2.FIELDNAME = X2.FIELDNAME AND M2.FIELDVALUE = X2.FIELDVALUE AND M2.EFFDT <= %DatePart(P2.RUNDTTM)) AND X2.EFF_STATUS = 'A' AND R.EMPLID = J.EMPLID AND R.EMPL_RCD = J.EMPL_RCD AND J.EFFDT = ( SELECT MAX(M.EFFDT) FROM PS_JOB M WHERE M.EMPLID = J.EMPLID AND M.EMPL_RCD = J.EMPL_RCD AND M.EFFDT <= %CurrentDateIn) AND J.EFFSEQ = ( SELECT MAX(M.EFFSEQ) FROM PS_JOB M WHERE M.EMPLID = J.EMPLID AND M.EMPL_RCD = J.EMPL_RCD AND M.EFFDT = J.EFFDT) AND R.EMPLID = N.EMPLID AND N.EFFDT = ( SELECT MAX(D_ED.EFFDT) FROM PS_NAMES D_ED WHERE N.EMPLID = D_ED.EMPLID AND D_ED.EFFDT <= %DatePart(P2.RUNDTTM)) UNION SELECT T.PROCESS_INSTANCE ,R.OPRID , 'TS_' %Concat T.EMPLID %Concat '0' , ' ' , R.PRCSINSTANCE , P2.RUNCNTLID , X2.XLATLONGNAME ,R.EMPLID ,R.EMPL_RCD ,R.EOAWDEFN_ID ,R.TRANSACTIONID ,R.BEGINDTTM ,R.ENDDTTM ,R.ENDDTTM - R.BEGINDTTM ,N.NAME ,J.SUPERVISOR_ID ,J.REPORTS_TO ,J.DEPTID ,R.APPROVAL_ROWS ,R.FROMDATE ,R.TO_DT FROM PS_TL_AWE_RUNCTL R ,PS_TL_EMPL_DATA T ,PS_TL_AWE_PRCSRQST P2 ,PSXLATITEM X2 ,PS_JOB J ,PS_NAMES N WHERE R.PROCESS_INSTANCE = T.PROCESS_INSTANCE AND T.EMPLID = R.EMPLID AND T.EMPL_RCD = R.EMPL_RCD AND T.EFFDT = ( SELECT MAX(M.EFFDT) FROM PS_TL_EMPL_DATA M WHERE M.EMPLID = T.EMPLID AND M.EMPL_RCD = T.EMPL_RCD AND M.EFFDT <= %CurrentDateIn) AND P2.PRCSINSTANCE = R.PRCSINSTANCE AND X2.FIELDNAME = 'RUNSTATUS' AND X2.FIELDVALUE = P2.RUNSTATUS AND X2.EFFDT = ( SELECT MAX(M2.EFFDT) FROM PSXLATITEM M2 WHERE M2.FIELDNAME = X2.FIELDNAME AND M2.FIELDVALUE = X2.FIELDVALUE AND M2.EFFDT <= %DatePart(P2.RUNDTTM)) AND X2.EFF_STATUS = 'A' AND R.EMPLID = J.EMPLID AND R.EMPL_RCD = J.EMPL_RCD AND J.EFFDT = ( SELECT MAX(M.EFFDT) FROM PS_JOB M WHERE M.EMPLID = J.EMPLID AND M.EMPL_RCD = J.EMPL_RCD AND M.EFFDT <= %CurrentDateIn) AND J.EFFSEQ = ( SELECT MAX(M.EFFSEQ) FROM PS_JOB M WHERE M.EMPLID = J.EMPLID AND M.EMPL_RCD = J.EMPL_RCD AND M.EFFDT = J.EFFDT) AND R.EMPLID = N.EMPLID AND N.EFFDT = ( SELECT MAX(D_ED.EFFDT) FROM PS_NAMES D_ED WHERE N.EMPLID = D_ED.EMPLID AND D_ED.EFFDT <= %DatePart(P2.RUNDTTM)) |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | PROCESS_INSTANCE |
Number(10,0) | DECIMAL(10) NOT NULL | Process Instance |
| 2 | OPRID | Character(30) | VARCHAR2(30) NOT NULL | A user's ID (see PSOPRDEFN). |
| 3 | TA_RUN_CNTL_ID | Character(30) | VARCHAR2(30) NOT NULL | Run Control ID |
| 4 | DESCR | Character(30) | VARCHAR2(30) NOT NULL | Description |
| 5 | TL_AWE_CHILD |
Number(10,0) | DECIMAL(10) NOT NULL | AWE Process Instance Parent |
| 6 | AWE_RUN_CNTL_ID | Character(30) | VARCHAR2(30) NOT NULL | Run Control ID |
| 7 | TL_AWE_DESCR1 | Character(30) | VARCHAR2(30) NOT NULL | Run Status |
| 8 | EMPLID |
Character(11) | VARCHAR2(11) NOT NULL |
Employee ID
Prompt Table: TL_ADM_NAME_VW |
| 9 | EMPL_RCD |
Number(3,0) | SMALLINT NOT NULL |
Empl Record
Prompt Table: TL_ADM_EERCD_VW |
| 10 | EOAWDEFN_ID |
Character(30) | VARCHAR2(30) NOT NULL | Approval Framework Definition ID |
| 11 | TRANSACTIONID | Number(10,0) | DECIMAL(10) NOT NULL | Transaction Identifier |
| 12 | BEGINDTTM | DateTime(26) | TIMESTAMP | Begin Date/Time |
| 13 | ENDDTTM | DateTime(26) | TIMESTAMP | End Date/Time |
| 14 | RUN_TIME | Character(30) | VARCHAR2(30) NOT NULL | AWE Run Time per employee |
| 15 | NAME | Character(50) | VARCHAR2(50) NOT NULL | Name |
| 16 | SUPERVISOR_ID | Character(11) | VARCHAR2(11) NOT NULL | Supervisor ID |
| 17 | REPORTS_TO | Character(8) | VARCHAR2(8) NOT NULL | Reports To Position Number |
| 18 | DEPTID | Character(10) | VARCHAR2(10) NOT NULL | Department |
| 19 | APPROVAL_ROWS | Number(5,0) | INTEGER NOT NULL | Approval Rows |
| 20 | FROMDATE | Date(10) | DATE | From Date |
| 21 | TO_DT | Date(10) | DATE | To Date |