/****************************************/
/* */
/* 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)
Thursday 5 July 2007
PeopleSoft Payroll Interface SQL
Subscribe to:
Post Comments (Atom)
3 comments:
Payroll companies can handle worker related issues along with centralized and state regulatory issues as well. Payroll will also eliminate the necessity to become an expert on topics such as withholding amounts.
Managing company records and payroll with a practical application like Excel spreadsheets for Windows makes sense for a small, single office company just starting out. Outsource payroll services fulfill all these requirements.
Thank you very much. I agreee with your article, this really helped me. I appreciate your help. Thanks a lot. Good website.
International Payroll Providers
Post a Comment