Appendix: PeopleSoft HRMS Application Diagnostic Plug-ins

This chapter provides an overview of application diagnostics and lists the diagnostic plug-ins delivered with HRMS.

Understanding Application Diagnostics

Enterprise PeopleTools Diagnostic Framework provides an interface enabling you to execute queries designed to investigate application problems and present the data in a standardized format that you can then share with PeopleSoft's Global Support Centre.

The diagnostic framework provides:

• Dynamic prompting, enabling you to restrict queries and include transactional data.

• Output in XML, in addition to HTML.

• Send functionality, enabling you to send the output directly to the email address of the global support center analyst working with you.

• Support for rowset retrieval.

HRMS has delivered a number of diagnostic plug-ins, which are application-specific queries, with this version. We will post any plug-ins we develop post-GA on Customer Connection.

See Also

Enterprise PeopleTools PeopleBook: Data Management, “Running Diagnostics with Diagnostic Framework”

Delivered Application Diagnostic HRMS Plug-Ins

These tables list the diagnostic plug-ins delivered by application.

Human Resources Manage Base Benefits

Manage Base Benefits delivers the one plug-in.

BA_EVENT_DIAGNOSTICS

The BA_EVENT_DIAGNOSTICS plug-in (Benefits Event Definition and Rules Setup Information):

• Uses the following additional parameters:

  • Event Rule ID

  • Effective Date

• Provides a diagnosis of the Benefit Event Setup and Coverage Code Rules associated with it.

  Data is extracted from the three primary tables: BAS_EVENT_RULE, BAS_EVENT_CLASS, and BAS_CVG_CD_RULE. Use this diagnostic to view the underlying data as it is defined in the database for the processing to be used by the Benefits Administration processing.

Human Resources Manage Base Compensation and Budgeting

Manage Base Compensation and Budgeting delivers the one plug-in.

SP_DIAGNOSTICS_SALARY_STEP

The SP_DIAGNOSTICS_SALARY_STEP plug-in (Salary Step Details):

• Uses the following additional parameters:

  • SetID

  • Sal Admin Plan

  • Grade

  • Effective Date

• Provides a diagnosis of salary step details for a given plan.

ePerformance

ePerformance delivers two plug-ins:

• Document Templates

  Note. This diagnostic script can also be used with Manage Employee Reviews.

• Debug/Trace Results

Document Templates

The Document Templates plug-in:

• Uses the following parameters:

  • Template ID

  • Effective Date

• Provides a diagnosis of the structure of the document template that created a particular document. This provides critical information to the support analyst for resolving an issue with a document. These tables, all keyed by template ID and effective date, are dumped:

  • Template Header (EP_TMPL_DEFN)

  • Process Participant Roles (EP_TMPL_PARTIC)

  • Document Sections (EP_TMPL_SECTION)

  • Role-Level Rules (EP_TMPL_ROLE)

  • Common Content: Items (EP_TMPL_ITEM)

  • Common Content: SubItems (EP_TMPL_SUBITEM)

Debug/Trace Results

The Debug/Trace Results plug-in:

• Uses the following parameters:

  • Document ID

  • User Role

• Provides a diagnosis of the Debug/Trace results table view (EP_DBG_DIAG_VW) that is created by the built-in Debug/Trace facility. This information is keyed by document ID and user role.

  Additionally, a Send to PeopleSoft link on the Calculation Debug/Trace inquiry page enables the user to navigate to the Application Diagnostics launch page and send this data to PeopleSoft.

Global Payroll

Global Payroll provides the following plug-ins:

• GP_DIAG_000

• GP_DIAG_005

• GP_DIAG_010

• GP_DIAG_020

• GP_DIAG_030

• GP_DIAG_040

• GP_DIAG_100

• GP_DIAG_200

GP_DIAG_000

The GP_DIAG_000 plug-in (Return General Information):

• Uses the following parameters:

  • Calendar Group ID

  • Operating System

  • Country Code

• Provides a diagnosis of the following tables and information:

  • PSRELEASE: Release information.

  • PSOPTION: License information.

  • PS_INSTALLATION: HRMS installation information.

  • PS_GP_PIN: Last element number.

  • PS_GP_INSTALLATION: Global Payroll installation information.

  • PS_MAINTENANCE_LOG: Bundles and fixes applied to the database.

  • PS_GP_COUNTRY: Country information.

    Note. If COUNTRY parameter is empty, the system returns country information for the calendar group. If country parameter is equal to ALL, the system returns all countries.

GP_DIAG_005

The GP_DIAG_005 plug-in (Return Log File):

• Uses the full file path and file name to the location of file as a parameters.

• Provides a diagnosis of the file specified in the “Full Path + the log file name” field.

GP_DIAG_010

The GP_DIAG_010 plug-in (Return SQL Store Statement):

• Uses the COBOL Program Name as a parameter.

• Provides a diagnosis of PSSQLSTMT_TBL, which returns all SQL statements used for the COBOL name specified in the COBOL PROGRAM Name field.

GP_DIAG_020

The GP_DIAG_020 plug-in (Return AE Program Definition):

• Uses the Application Engine Name as a parameter.

• Provides a diagnosis of the following Application Engine information:

  • PSAEAPPLDEFN

  • PSAEAPPLSTATE

  • PSAESECTDTLDEFN

  • PSAESTEPDEFN

  • PSAESTEPMSGDEFN

  • PSAESTMTDEFN

  • PSSQLTEXTDEFN

GP_DIAG_030

The GP_DIAG_030 plug-in (Return Element Definition):

• Uses Element Name 1 - 5 as a parameter.

• Provides a diagnosis of the following tables, based on the element type returned, for each of up to five element definitions:

  • Element name information: PS_GP_PIN

  • Take Element: GP_ABS_TAKE, GP_ABS_TAKE_CFG, GP_ABS_TAKE_DAY, GP_ABS_TAKE_ELM, GP_ABS_TK_FCST

  • Accumulator Element: GP_ACCUMULATOR, GP_ACM_MBR

  • Array Element: GP_ARRAY, GP_ARRAY_FLD, GP_ARRAY_KEY, GP_ARRAY_PRC

  • Bracket Element: GP_BRACKET

  • Count Element: GP_COUNT

  • Date Element: GP_DATE

  • Duration Element: GP_DURATION, GP_DUR_GNRN

  • Earning - Deduction Element: GP_ERN_DED, GP_ELM_DFN_SOVR, GP_PIN_CMPNT, GP_RCP_DED

  • Element Group Name: GP_ELEM_GRP, GP_ELEM_GRP_MBR

  • Fictitious Calculation Element: GP_FC_IN, GP_FC_OUT, GP_FC_OUT_DTL, GP_FC_SEG, GP_FC_TBL

  • Formula Element: GP_FORMULA, GP_FORMULA_CLUE, GP_FORMULA_DTL, GP_FORMULA_VAR

  • Generation Control Element: GP_GCTL, GP_GCTL_DTL

  • Historical Rule Element: GP_HIST_ELEM, GP_HIST_RULE

  • Process Element: GP_PROCESS, GP_PROCESS_DTL

  • Proration Element: GP_PRORATION

  • Rate Code Element: GP_RATE_CODE

  • Section Element: GP_SECTION, GP_SECTION_DTL

  • System Element: GP_SYSTEM_PIN

  • Variable Element: GP_VARIABLE

  • Write Array Element: GP_WA_ARRAY, GP_WA_FLD

GP_DIAG_040

The GP_DIAG_040 plug-in (Return Array/WA Audit):

• Uses the Country Code as a parameter.

• Provides a diagnosis of the following tables and information:

  • PS_GP_DIAG_ARRAY_D: All arrays of a country not synchronized with the record definition.

  • PS_GP_DIAG_WA_D: All write arrays of a country not synchronized with the record definition.

GP_DIAG_100

The GP_DIAG_100 plug-in (Return Set-up Data):

• Uses the following parameters:

  • Calendar Group ID

  • Calendar ID

• Provides a diagnosis of the following tables and information:

  • PS_GP_PYENT: Pay entity information.

  • PS_GP_PYENT_DTL: Pay entity detail information.

  • PS_GP_CAL_RUN: Calendar group information.

  • PS_GP_CAL_RUN_DTL: Calendar group detail information.

  • PS_GP_CALENDAR: Calendar information.

  • PS_GP_RUN_TYPE: Run type information.

GP_DIAG_200

The GP_DIAG_200 plug-in (Return Employee Information):

• Uses the following parameters:

  • EmplID

  • Employee Record Number

  • Calendar Group ID

  • Calendar ID

• Provides a diagnosis of the following tables and information:

  • PS_GP_DIAG_JOB_D: Payroll information (but no compensation data).

  • PS_GP_DIAG_EMPL_D: Dates.

  • PS_GP_DIAG_CTR1_D: Contract information.

  • PS_WKFCNT_TYPE: Related contract information.

  • PS_GP_DIAG_CTR2_D: Related contract information.

  • PS_GP_PAYEE_DATA: Payee information.

  • PS_GP_DIAG_PI_D: Positive input (but no amount data).

  • PS_GP_NET_DIST_DTL: Banking and payment information.

  • PS_GP_RTO_TRGR: Trigger information.

  • PS_GP_SEG_TRGR: Related trigger information.

  • PS_GP_DIAG_P_SEG_D: Payroll result headers (but no gross and net data).

  • PS_GP_DIAG_MSG_D: Payroll error messages.

Time and Labor

Time and Labor provides the following plug-ins:

• TL_DIAGNOSTICS_TA_EMPL_PAY

• TL_DIAGNOSTICS_TA_EMPL_GRP

• TL_DIAGNOSTICS_TA_TACODE

• TL_DIAGNOSTICS_TA_RPTD_TIME

• TL_DIAGNOSTICS_PT_INV_PAYTIME

• TL_DIAGNOSTICS_PT_DUP_SEQ

• TL_DIAGNOSTICS_PT_INV_OFFSET

• TL_DIAGNOSTICS_SETUP_TIMEPRD

• TL_DIAGNOSTICS_SETUP_TIMEZONE

• TL_DIAGNOSTICS_SETUP_RULEPGM

• TL_DIAGNOSTICS_SETUP_ EXWRKGRP

• TL_DIAGNOSTICS_SETUP_TCD

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