Time and Labor

TL_DIAGNOSTICS_TA_EMPL_PAY

Payable time for employees are displayed for the given date range. The exceptions created (if any) for that date range are also displayed.

The TL_DIAGNOSTICS_TA_EMPL_PAY plug-in:

Uses the following parameters:
- EmplID
- Employee Record
- Start Date
- End Date
Provides a diagnosis of:
- SQL for the View: TL_DU_TA_PT_VW
  
  SELECT A.EMPLID, A.EMPL_RCD, A.DUR, A.TRC, A.TL_QUANTITY, A.PAYABLE_STATUS FROM PS_TL_PAYABLE_TIME A
- SQL for the View: TL_DU_TA_EX_VW
  
  SELECT A.EXCEPTION_ID, A.EMPLID, A.EMPL_RCD, A.DUR, A.EXCEPTION_SOURCE, A.MSG_DATA1 FROM PS_TL_EXCEPTION A
- Create SQL:
  
  SELECT %DateOut(EARLIEST_CHGDT), TA_STATUS FROM PS_TL_TR_STATUS WHERE EMPLID= :1 AND EMPL_RCD = :2

TL_DIAGNOSTICS_TA_EMPL_GRP

If the Run Control ID has a dynamic group included, the list of employees in the group is resolved. These employees are checked if they are Active between the Process Through Date and Process Through Date – 31 days.

The TL_DIAGNOSTICS_TA_EMPL_GRP plug-in:

Uses the Run Control ID as a parameter.
Provides a diagnosis of:
- SQL for the View: TL_DU_TA_GRP_VW
  
  SELECT A.EMPLID, A.EMPL_RCD, A.EFFDT, A.EMPL_STATUS FROM PS_JOB A
- SQLExec:
  
  SELECT %DATEOUT(A.THRUDATE) FROM PS_TL_TA_RUNCTL A WHERE A.RUN_CNTL_ID=:1 AND A.OPRID=:2
- CreateSQL:
  
  SELECT DISTINCT A.EMPLID, A.EMPL_RCD FROM PS_JOB A WHERE (EXISTS (SELECT 'X' FROM PS_TL_RUN_CTRL_GRP B WHERE B.RUN_CNTL_ID=:1 AND B.GROUP_ID='Z' AND B.INCLUD_EXCLUDE_IND='+' AND A.EMPLID=B.EMPLID AND A.EMPL_RCD=B.EMPL_RCD ) OR EXISTS(SELECT 'X' FROM PS_TL_GROUP_DTL B1 WHERE B1.GROUP_ID IN (SELECT B.GROUP_ID FROM PS_TL_RUN_CTRL_GRP B WHERE B.RUN_CNTL_ID=:1 AND B.GROUP_ID <> 'Z') AND A.EMPLID=B1.EMPLID AND A.EMPL_RCD=B1.EMPL_RCD)) AND NOT EXISTS(SELECT 'X' FROM PS_TL_RUN_CTRL_GRP B2 WHERE B2.RUN_CNTL_ID=:1 AND B2.GROUP_ID='Z' AND B2.INCLUD_EXCLUDE_IND='-' AND A.EMPLID=B2.EMPLID AND A.EMPL_RCD=B2.EMPL_RCD) AND EMPL_STATUS='A' AND (A.EFFDT =(SELECT MAX(EFFDT) FROM PS_JOB J1 WHERE A.EMPLID=J1.EMPLID AND A.EMPL_RCD= J1.EMPL_RCD AND J1.EFFDT <=%DATEIN(:2) ) OR A.EFFDT =(SELECT MAX(EFFDT) FROM PS_JOB J2 WHERE A.EMPLID=J2.EMPLID AND A.EMPL_RCD=J2.EMPL_RCD AND J2.EFFDT >%DATEIN(:2) AND J2.EFFDT <=%DATEIN(:3) AND J2.EMPL_STATUS='A'))

TL_DIAGNOSTICS_TA_TACODE

The TL_DIAGNOSTICS_TA_TACODE plug-in (listing of SQL Object IDs and SQL statements used in the time administration process):

Uses the SQL Object ID as parameter.
Provides a diagnosis of the SQL for the View: TL_DU_TA_SQL_VW.

SELECT A.SQLID, A.SQLTEXT FROM PSSQLTEXTDEFN A

TL_DIAGNOSTICS_TA_RPTD_TIME

This lists the reported time details of an employee in a give date range based on the reported status (input parameter). If the EmplID is not given as input this lists all the employees with the specific reported status in the given date range.

The TL_DIAGNOSTICS_TA_RPTD_TIME plug-in:

Uses the following parameters:
- EmplID
- Reported Time Status
- Start Date
- End Date
Provides a diagnosis of:
- SQL for the View: TL_DU_TA_RPT_VW
  
  SELECT A.EMPLID, A.EMPL_RCD, A.DUR, A.PUNCH_TYPE, A.PUNCH_DTTM, A.TASKGROUP, A.TASK_PROFILE_ID, A.TRC, A.TL_QUANTITY, B.XLATLONGNAME FROM PS_TL_RPTD_TIME A, XLATTABLE_VW B WHERE B.FIELDNAME = 'REPORTED_STATUS' AND B. FIELDVALUE = A.REPORTED_STATUS
- SQL for the View: TL_DU_TA_RPL_VW
  
  SELECT A.EMPLID, A.EMPL_RCD, A.DUR, A.PUNCH_TYPE, A.PUNCH_DTTM, A.TASKGROUP, A.TASK_PROFILE_ID, A.TRC, A.TL_QUANTITY, B.XLATLONGNAME FROM PS_TL_RPTD_TIME A, XLATTABLE_LNG B WHERE B.FIELDNAME = 'REPORTED_STATUS' AND B. FIELDVALUE = A.REPORTED_STATUS
- SQLExec:
  
  SELECT A.XLATLONGNAME FROM XLATTABLE_VW A WHERE A.FIELDNAME='REPORTED_STATUS' AND %Upper(A.XLATLONGNAME) = :1
- SQLExec:
  
  SELECT A.XLATLONGNAME FROM XLATTABLE_LNG A WHERE A.FIELDNAME='REPORTED_STATUS' AND %Upper(A.XLATLONGNAME) = :1
- Create SQL:
  
  SELECT A.XLATLONGNAME FROM XLATTABLE_VW A WHERE A.FIELDNAME='REPORTED_STATUS'
- Create SQL:
  
  SELECT A.XLATLONGNAME FROM XLATTABLE_LNG A WHERE A.FIELDNAME='REPORTED_STATUS'

TL_DIAGNOSTICS_PT_INV_PAYTIME

The TL_DIAGNOSTICS_PT_INV_PAYTIME plug-in (lists payable time where payable status is rejected by payroll for a date range):

Uses the following parameters:
- Start Date
- End Date
Provides a diagnosis of:
- SQL for the View: TL_DU_PT_PT_VW
  
  SELECT A.EMPLID, A.EMPL_RCD, A.DUR, A.TL_QUANTITY, B.XLATSHORTNAME FROM PS_TL_PAYABLE_TIME A, XLATTABLE_VW B WHERE ((A.PAYABLE_STATUS ='RP' AND A.PAYROLL_REQ_NUM =0) OR (A.PAYABLE_STATUS ='CL' AND A.PAYROLL_REQ_NUM =0) OR (A.PAYABLE_STATUS ='TP' AND A.PAYROLL_REQ_NUM =0) OR (A.PAYABLE_STATUS ='SP' AND A.PAYROLL_REQ_NUM <>0) ) AND B.FIELDNAME='PAYABLE_STATUS' AND B. FIELDVALUE = A.PAYABLE_STATUS
- SQL for the View: TL_DU_PT_PTL_VW
  
  SELECT A.EMPLID, A.EMPL_RCD, A.DUR, A.TL_QUANTITY, B.XLATSHORTNAME FROM PS_TL_PAYABLE_TIME A, XLATTABLE_LNG B WHERE ((A.PAYABLE_STATUS ='RP' AND A.PAYROLL_REQ_NUM =0) OR (A.PAYABLE_STATUS ='CL' AND A.PAYROLL_REQ_NUM =0) OR (A.PAYABLE_STATUS ='TP' AND A.PAYROLL_REQ_NUM =0) OR (A.PAYABLE_STATUS ='SP' AND A.PAYROLL_REQ_NUM <>0) ) AND B.FIELDNAME = 'PAYABLE_STATUS' AND B. FIELDVALUE = A.PAYABLE_STATUS

TL_DIAGNOSTICS_PT_DUP_SEQ

The TL_DIAGNOSTICS_PT_DUP_SEQ plug-in (lists duplicate sequence numbers for the offsets) provides a diagnosis of SQL for the View: TL_DU_PT_SEQ_VW:

SELECT A.SEQ_NBR, COUNT(*) FROM PS_TL_PAYABLE_TIME A GROUP BY A.SEQ_NBR HAVING COUNT(*) > 1

TL_DIAGNOSTICS_PT_INV_OFFSET

The TL_DIAGNOSTICS_PT_INV_OFFSET plug-in (lists the Payable time offsets rejected by Payroll) provides a diagnosis of SQL for the View: TL_DU_PT_OFF_VW:

SELECT A.EMPLID, A.EMPL_RCD, A.DUR, A.SEQ_NBR, A.TL_QUANTITY, A.PAYABLE_STATUS, A.RECORD_ONLY_ADJ, A.ORIG_SEQ_NBR FROM PS_TL_PAYABLE_TIME A WHERE EXISTS (SELECT 'X' FROM PS_TL_PAYABLE_TIME C WHERE A.EMPLID =C.EMPLID AND A.EMPL_RCD =C.EMPL_RCD AND A.DUR=C.DUR AND A.ORIG_SEQ_NBR=C.SEQ_NBR AND C.ORIG_SEQ_NBR <> 0 AND C.RECORD_ONLY_ADJ = 'N' AND C.PAYABLE_STATUS NOT IN ('ES','NA') AND C.TL_QUANTITY > 0 AND NOT EXISTS (SELECT 'X' FROM PS_TL_PAYABLE_TIME A2 WHERE A2.EMPLID =C.EMPLID AND A2.EMPL_RCD =C.EMPL_RCD AND A2.DUR=C.DUR AND A2.ORIG_SEQ_NBR=C.SEQ_NBR AND A2.TL_QUANTITY < 0)) OR EXISTS (SELECT 'X' FROM PS_TL_PAYABLE_TIME C1 WHERE A.EMPLID =C1.EMPLID AND A.EMPL_RCD =C1.EMPL_RCD AND A.DUR=C1.DUR AND A.ORIG_SEQ_NBR=C1.SEQ_NBR AND C1.ORIG_SEQ_NBR =0 AND C1.RECORD_ONLY_ADJ = 'N' AND C1.PAYABLE_STATUS NOT IN ('ES','NA') AND EXISTS (SELECT 'X' FROM PS_TL_PAYABLE_TIME A1 WHERE A1.EMPLID=C1.EMPLID AND A1.EMPL_RCD =C1.EMPL_RCD AND A1.DUR=C1.DUR AND A1.TL_QUANTITY < 0 AND A1.ORIG_SEQ_NBR=C1.SEQ_NBR))

TL_DIAGNOSTICS_SETUP_TIMEPRD

The plug-in displays the Period IDs that are not built over the given date range, along with the workgroup from PS_TL_WRKGRP_TBL.

The TL_DIAGNOSTICS_SETUP_TIMEPRD plug-in:

Uses the following parameters:
- Start Date
- End Date
Provides a diagnosis of SQL for the View: TL_DU_ST_TP_VW:

SELECT PERIOD_ID, WORKGROUP FROM PS_TL_WRKGRP_TBLCreateSQL:SELECT DISTINCT A.PERIOD_ID FROM PS_TL_TIME_PERIODS A WHERE A.PERIOD_ID NOT IN (SELECT DISTINCT B.PERIOD_ID FROM PS_TL_CALENDAR B WHERE ((%DateIn(:1)>B.START_DT AND %DateIn(:2)<B.END_DT) OR (%DateIn(:1)<B.END_DT AND %DateIn(:2) >B.START_DT)))

TL_DIAGNOSTICS_SETUP_TIMEZONE

The Plug-in obtains the time zone of the employee using the input EmplID and displays the time zone offsets within the given date range.

The TL_DIAGNOSTICS_SETUP_TIMEZONE plug-in:

Uses the following parameters:
- EmplID
- Start Date
- End Date
Provides a diagnosis of SQLExec:

SELECT B.TIMEZONE FROM PS_TL_EMPL_DATA B WHERE B.EMPLID= :1 AND B.EFFDT=(SELECT MAX(EFFDT) FROM PS_TL_EMPL_DATA A WHERE A.EMPLID=:1 AND A.EFFDT<=%DateIn(:2) AND A.TIME_RPTG_STATUS='A')

TL_DIAGNOSTICS_SETUP_RULEPGM

The plug-in is used to display the list of workgroups that do not have Rule Programs associated to them.

The TL_DIAGNOSTICS_SETUP_RULEPGM plug-in provides a diagnosis of SQL for the View: TL_DU_ST_RUL_VW.

SELECT A.WORKGROUP FROM PS_TL_WRKGRP_TBL A WHERE A.RULE_PGM_ID = ' '

TL_DIAGNOSTICS_SETUP_ EXWRKGRP

The plug-in displays the list of all exception time reporter type workgroups with missing Schedule ID. Workgroups of exception time reporter type are displayed if a Schedule ID is not attached to them.

The TL_DIAGNOSTICS_SETUP_ EXWRKGRP plug-in provides a diagnosis of SQL for the View: TL_DU_ST_SCH_VW.

SELECT A.WORKGROUP FROM PS_TL_WRKGRP_TBL A WHERE A.TIME_RPTG_TYPE = 'E' AND A.SCHEDULE_ID=' '

TL_DIAGNOSTICS_SETUP_TCD

The plug-in verifies the TCD setup data. For the given TCD ID input, the plug-in traces the TCD (message node name), message name, message status, transaction status and type of transaction. It also verifies checks for the Integration type. Displays whether the Inbound, outbound directories are specified for Flat file type of integration.

The TL_DIAGNOSTICS_SETUP_TCD plug-in:

Uses the TCD ID as a parameter.
Provides a diagnosis of:
- SQL for the View: TL_DU_ST_TCD_VW
  
  SELECT A.RQSTMSGNAME, A.MSGNODENAME, B.MSGSTATUS, C.XLATSHORTNAME, A.EFF_STATUS FROM PSNODETRX A, PSMSGDEFN B, XLATTABLE_VW C WHERE A.RQSTMSGNAME=B.MSGNAME AND C.FIELDNAME='TRXTYPE' AND C.FIELDVALUE=A.TRXTYPE
- CreateSQL:
  
  SELECT A.XLATSHORTNAME, A.FIELDVALUE FROM XLATTABLE_VW A WHERE A.FIELDNAME = 'INTEGRATION_TYPE' AND A.FIELDVALUE=(SELECT B.INTEGRATION_TYPE FROM PS_TL_TCDDEF_TBL B WHERE B.TCD_TYPE_ID=:1 AND B.EFFDT = (SELECT MAX(B1.EFFDT) FROM PS_TL_TCDDEF_TBL B1 WHERE B1.TCD_TYPE_ID=:1))
- CreateSQL:
  
  SELECT A.XLATSHORTNAME, A.FIELDVALUE FROM XLATTABLE_LNG A WHERE A.FIELDNAME = 'INTEGRATION_TYPE' AND A.FIELDVALUE=(SELECT B.INTEGRATION_TYPE FROM PS_TL_TCDDEF_TBL B WHERE B.TCD_TYPE_ID =:1 AND B.EFFDT=(SELECT MAX(B1.EFFDT) FROM PS_TL_TCDDEF_TBL B1 WHERE B1.TCD_TYPE_ID=:1))CreateSQL:SELECT FILE_OUTPUT_DIR, FILE_ARCHIVE_DIR FROM PS_TL_INSTALLATION