-- ---------------
-- Dump XLAT Table
-- ---------------
select XLAT.FIELDVALUE,
XLAT.XLATLONGNAME,
XLAT.XLATSHORTNAME
from XLATTABLE_VW XLAT
where XLAT.FIELDNAME = 'EMPL_STATUS'
and XLAT.EFFDT = (select max(XLAT1.EFFDT)
from XLATTABLE_VW XLAT1
where XLAT1.FIELDNAME = XLAT.FIELDNAME
and XLAT1.FIELDVALUE = XLAT.FIELDVALUE
and XLAT1.EFFDT <= SYSDATE)
and XLAT.EFF_STATUS = 'A'
Often you want to find out what the database is that you are logged in to. This can be useful when spooling the output of a SQL script to a log file or checking you are in the right environment before executing a script.
Oracle
In Oracle you can get the current database name by querying the table v_$database
i.e.
select name
from sys.v_$database
SQL Server
The equivalent way of finding it in SQL Server is to use the function DB_Name.
e.g.
select DB_Name()
To find a table name in SQL Server query the table sysobjects.
For example to list all PeopleSoft tables on the system, you could use: -
select *
from sysobjects
where name like 'PS_%'
/****************************************/
/* */
/* Dump the Payroll Interface Run Table */
/* */
/****************************************/
select *
from PS_PI_RUN_TBL
order by PI_RUN_NUM desc
/*************************/
/* */
/* Dump PI Partic table. */
/* */
/*************************/
select *
from PS_PI_PARTIC
/*************************************/
/* */
/* Dump PI Partic table, latest run. */
/* */
/*************************************/
select PIP.*
from PS_PI_RUN_TBL PUN,
PS_PI_PARTIC PIP
where PUN.PI_RUN_NUM = (select max(PUN1.PI_RUN_NUM)
from PS_PI_RUN_TBL PUN1)
and PUN.PI_RUN_NUM = PIP.PI_RUN_NUM
/*******************************/
/* */
/* Dump PI PARTIC detail table */
/* */
/*******************************/
select *
from PS_PI_PARTIC_EXPT
/********************************************/
/* */
/* Dump PI Partic detail table, latest run. */
/* */
/********************************************/
select PIE.*
from PS_PI_RUN_TBL PUN,
PS_PI_PARTIC_EXPT PIE
where PUN.PI_RUN_NUM = (select max(PUN1.PI_RUN_NUM)
from PS_PI_RUN_TBL PUN1)
and PUN.PI_RUN_NUM = PIE.PI_RUN_NUM
/***************************************************/
/* */
/* Dump Pay Calendars associated with a particular */
/* Payroll Interface run. */
/* */
/***************************************************/
select RUN.PI_RUN_NUM,
CAL.*
from PS_PI_RUN_TBL RUN,
PS_PAY_CALENDAR CAL
where RUN.PI_RUN_NUM = 999
and RUN.RUN_ID = CAL.RUN_ID
/******************************/
/* */
/* Dump the PI Empl ID Table. */
/* */
/******************************/
select *
from PS_PI_EMPLID_TBL
/********************************************/
/* */
/* List fields in a Payroll Interface File. */
/* */
/********************************************/
select DEF.PI_FIELD_SEQ,
DEF.PI_FIELD_ID,
FLD.DESCR,
FLD.PI_FIELD_LEN,
FLD.PS_FIELD1_NM,
DEF.PI_KEY_FIELD,
DEF.PI_GROUP_ID
from PS_PI_DEFN_FIELD DEF,
PS_PI_FIELD_TBL FLD
where DEF.PI_FILE_ID = 'XXX'
and DEF.EFFDT = (select max(DEF1.EFFDT)
from PS_PI_DEFN_FIELD DEF1
where DEF.PI_FILE_ID = DEF1.PI_FILE_ID
and DEF1.EFFDT <= SYSDATE) and DEF.PI_SYSTEM_ID = FLD.PI_SYSTEM_ID and DEF.PI_FIELD_ID = FLD.PI_FIELD_ID and FLD.EFFDT = (select max(FLD1.EFFDT) from PS_PI_FIELD_TBL FLD1 where FLD.PI_SYSTEM_ID = FLD1.PI_SYSTEM_ID and FLD.PI_FIELD_ID = FLD1.PI_FIELD_ID and FLD1.EFFDT <= SYSDATE)
/***************************************************/
/* */
/* List interface files that contain a given Field */
/* */
/***************************************************/
select DEF.PI_FILE_ID,
DEF.PI_FIELD_SEQ,
DEF.PI_FIELD_ID,
FLD.DESCR,
FLD.PI_FIELD_LEN,
FLD.PS_FIELD1_NM,
DEF.PI_KEY_FIELD,
DEF.PI_GROUP_ID
from PS_PI_DEFN_FIELD DEF,
PS_PI_FIELD_TBL FLD
where DEF.PI_SYSTEM_ID = 'CHR'
and DEF.PI_FIELD_ID IN ('E001')
and DEF.EFFDT = (select max(DEF1.EFFDT)
from PS_PI_DEFN_FIELD DEF1
where DEF.PI_FILE_ID = DEF1.PI_FILE_ID
and DEF1.EFFDT <= SYSDATE)
and DEF.PI_SYSTEM_ID = FLD.PI_SYSTEM_ID
and DEF.PI_FIELD_ID = FLD.PI_FIELD_ID
and FLD.EFFDT = (select max(FLD1.EFFDT)
from PS_PI_FIELD_TBL FLD1
where FLD.PI_SYSTEM_ID = FLD1.PI_SYSTEM_ID
and FLD.PI_FIELD_ID = FLD1.PI_FIELD_ID
and FLD1.EFFDT <= SYSDATE)
