TL_AWE_RUNVW1

(SQL View)
 Index Back

Run control for AWE Approvals.

Run control for AWE Approvals.

SELECT DISTINCT R.PROCESS_INSTANCE ,R.OPRID ,P1.RUNCNTLID ,R.PRCSINSTANCE ,P2.RUNCNTLID ,X2.XLATLONGNAME ,( SELECT MAX(MX.ENDDTTM) FROM PS_TL_AWE_RUNCTL MX WHERE MX.PRCSINSTANCE = P2.PRCSINSTANCE ) - ( SELECT MIN(MN.BEGINDTTM) FROM PS_TL_AWE_RUNCTL MN WHERE MN.PRCSINSTANCE = P2.PRCSINSTANCE ) FROM PS_TL_AWE_RUNCTL R ,PS_TL_AWE_PRCSRQST P1 ,PS_TL_AWE_PRCSRQST P2 ,PSXLATITEM X2 WHERE P1.PRCSINSTANCE = R.PROCESS_INSTANCE 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' UNION SELECT T.PROCESS_INSTANCE ,R.OPRID ,'TS_' %Concat T.EMPLID %Concat '0' ,R.PRCSINSTANCE ,R.RUN_CNTL_ID ,X2.XLATLONGNAME ,( SELECT MAX(MX.ENDDTTM) FROM PS_TL_AWE_RUNCTL MX WHERE MX.PRCSINSTANCE = R.PRCSINSTANCE ) - ( SELECT MIN(MN.BEGINDTTM) FROM PS_TL_AWE_RUNCTL MN WHERE MN.PRCSINSTANCE = R.PRCSINSTANCE ) FROM PS_TL_AWE_RUNCTL R ,PS_TL_EMPL_DATA T ,PS_TL_AWE_PRCSRQST P2 ,PSXLATITEM X2 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.RUNCNTLID = R.RUN_CNTL_ID 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'

# 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 TL_AWE_CHILD Number(10,0) DECIMAL(10) NOT NULL AWE Process Instance Parent
5 AWE_RUN_CNTL_ID Character(30) VARCHAR2(30) NOT NULL Run Control ID
6 TL_AWE_DESCR1 Character(30) VARCHAR2(30) NOT NULL Run Status
7 RUN_TIME Character(30) VARCHAR2(30) NOT NULL AWE Run Time per employee