PeopleSoft HR Tables
PS_EMPLOYEES
PS_EMPLOYEES_LNG
PS_EMPLOYMENT
PS_EMPLOYMENT_LNG
PS_EMPLOYMENT_LNG1
PS_EMPLOYMENT_LNG2
PS_EMPLOYMENT_LNG3
PS_HS_EMPLOYMENT
PS_HS_EMPL_REL_CAN
PS_HS_EMPL_REL_LNG
PS_HS_EMPL_SUB_CAN
PS_HS_EMPL_SUB_LNG
PS_INJ_CLMADDR_CAN
PS_JOB
PS_PERSONAL_DATA
PS_PERSONL_DTA_LNG
PS_PERS_DATA_AET
PS_PERS_DATA_EFFDT
PS_PERS_DTAEFF_LNG
PS_PERS_NID
PS_JOB_EARNS_DIST
PS_JOB_APPROVALS
PS_BEN_PROG_PARTIC
Tuesday, September 29, 2009
Complete PeopleSoft Tables
Complete PeopleSoft Tables
PS_EMPLOYEES
PS_EMPLOYEES_LNG
PS_EMPLOYMENT
PS_EMPLOYMENT_LNG
PS_EMPLOYMENT_LNG1
PS_EMPLOYMENT_LNG2
PS_EMPLOYMENT_LNG3
PS_HS_EMPLOYMENT
PS_HS_EMPL_REL_CAN
PS_HS_EMPL_REL_LNG
PS_HS_EMPL_SUB_CAN
PS_HS_EMPL_SUB_LNG
PS_INJ_CLMADDR_CAN
PS_JOB, PS_PERSONAL_DATA
PS_PERSONL_DTA_LNG
PS_PERS_DATA_AET
PS_PERS_DATA_EFFDT
PS_PERS_DTAEFF_LNG
PS_PERS_NID
PS_JOB_EARNS_DIST
PS_JOB_APPROVALS
PS_STATE_TAX_DATA
PS_GENL_DEDUCTION
PS_BAL_ADJ_UI_CAN
PS_CAN_CHECK_YTD
PS_CAN_DED_BALANCE
PS_CAN_ERN_BALANCE
PS_CAN_TAX_BALANCE
PS_CAN_TAX_DATA
PS_CHECK_YTD
PS_DEDUCTION_BAL
PS_EARNINGS_BAL
PS_FED_TAX_DATA
PS_TAX_BALANCE
PS_LOCAL_TAX_DATA
PS_BAL_ADJ_ARR
PS_BAL_ADJ_CHK
PS_BAL_ADJ_CN_CHK
PS_BAL_ADJ_CN_DED
PS_BAL_ADJ_CN_ERN
PS_BAL_ADJ_CN_TAX
PS_BAL_ADJ_DED
PS_BAL_ADJ_ERN
PS_BAL_ADJ_GRN
PS_BAL_ADJ_TAX
PS_DED_ARREARS
PS_GARN_RULE
PS_GARN_SCHED
PS_GARN_SPEC
PS_GENL_DED_CD
PS_BAS_PARTIC
PS_BAS_PARTIC_COST
PS_BAS_PARTIC_DPND
PS_BAS_PARTIC_INVT
PS_BAS_PARTIC_OPTN
PS_BAS_PARTIC_PLAN
PS_BENEF_COMMENT
PS_BEN_PROG_PARTIC
PS_DEPENDENT_BENEF
PS_DISABILITY_BEN
PS_FSA_BENEFIT
PS_FSA_PAYMENT
PS_HEALTH_BENEFIT
PS_HEALTH_DEPENDNT
PS_LEAVE_ACCRUAL
PS_LEAVE_PLAN
PS_LIFE_ADD_BEN
PS_LIFE_ADD_BENEFC
PS_PENSION_BENEFC
PS_PENSION_PLAN
PS_RTRMNT_PLAN
PS_SAVINGS_BENEFIC
PS_SAVINGS_INVEST
PS_SAVINGS_PLAN
PS_VACATION_BEN
PS_EMPLOYEES
PS_EMPLOYEES_LNG
PS_EMPLOYMENT
PS_EMPLOYMENT_LNG
PS_EMPLOYMENT_LNG1
PS_EMPLOYMENT_LNG2
PS_EMPLOYMENT_LNG3
PS_HS_EMPLOYMENT
PS_HS_EMPL_REL_CAN
PS_HS_EMPL_REL_LNG
PS_HS_EMPL_SUB_CAN
PS_HS_EMPL_SUB_LNG
PS_INJ_CLMADDR_CAN
PS_JOB, PS_PERSONAL_DATA
PS_PERSONL_DTA_LNG
PS_PERS_DATA_AET
PS_PERS_DATA_EFFDT
PS_PERS_DTAEFF_LNG
PS_PERS_NID
PS_JOB_EARNS_DIST
PS_JOB_APPROVALS
PS_STATE_TAX_DATA
PS_GENL_DEDUCTION
PS_BAL_ADJ_UI_CAN
PS_CAN_CHECK_YTD
PS_CAN_DED_BALANCE
PS_CAN_ERN_BALANCE
PS_CAN_TAX_BALANCE
PS_CAN_TAX_DATA
PS_CHECK_YTD
PS_DEDUCTION_BAL
PS_EARNINGS_BAL
PS_FED_TAX_DATA
PS_TAX_BALANCE
PS_LOCAL_TAX_DATA
PS_BAL_ADJ_ARR
PS_BAL_ADJ_CHK
PS_BAL_ADJ_CN_CHK
PS_BAL_ADJ_CN_DED
PS_BAL_ADJ_CN_ERN
PS_BAL_ADJ_CN_TAX
PS_BAL_ADJ_DED
PS_BAL_ADJ_ERN
PS_BAL_ADJ_GRN
PS_BAL_ADJ_TAX
PS_DED_ARREARS
PS_GARN_RULE
PS_GARN_SCHED
PS_GARN_SPEC
PS_GENL_DED_CD
PS_BAS_PARTIC
PS_BAS_PARTIC_COST
PS_BAS_PARTIC_DPND
PS_BAS_PARTIC_INVT
PS_BAS_PARTIC_OPTN
PS_BAS_PARTIC_PLAN
PS_BENEF_COMMENT
PS_BEN_PROG_PARTIC
PS_DEPENDENT_BENEF
PS_DISABILITY_BEN
PS_FSA_BENEFIT
PS_FSA_PAYMENT
PS_HEALTH_BENEFIT
PS_HEALTH_DEPENDNT
PS_LEAVE_ACCRUAL
PS_LEAVE_PLAN
PS_LIFE_ADD_BEN
PS_LIFE_ADD_BENEFC
PS_PENSION_BENEFC
PS_PENSION_PLAN
PS_RTRMNT_PLAN
PS_SAVINGS_BENEFIC
PS_SAVINGS_INVEST
PS_SAVINGS_PLAN
PS_VACATION_BEN
PeopleSoft Base Benefits Tables
Base Benefits Tables
====================
PS_BAS_PARTIC
PS_BAS_PARTIC_COST
PS_BAS_PARTIC_DPND
PS_BAS_PARTIC_INVT
PS_BAS_PARTIC_OPTN
PS_BAS_PARTIC_PLAN
PS_BENEF_COMMENT
PS_BEN_PROG_PARTIC
PS_DEPENDENT_BENEF
PS_DISABILITY_BEN
PS_FSA_BENEFIT
PS_FSA_PAYMENT
PS_HEALTH_BENEFIT
PS_HEALTH_DEPENDNT
PS_LEAVE_ACCRUAL
PS_LEAVE_PLAN
PS_LIFE_ADD_BEN
PS_LIFE_ADD_BENEFC
PS_PENSION_BENEFC
PS_PENSION_PLAN
PS_RTRMNT_PLAN
PS_SAVINGS_BENEFIC
PS_SAVINGS_INVEST
PS_SAVINGS_PLAN
PS_VACATION_BEN
====================
PS_BAS_PARTIC
PS_BAS_PARTIC_COST
PS_BAS_PARTIC_DPND
PS_BAS_PARTIC_INVT
PS_BAS_PARTIC_OPTN
PS_BAS_PARTIC_PLAN
PS_BENEF_COMMENT
PS_BEN_PROG_PARTIC
PS_DEPENDENT_BENEF
PS_DISABILITY_BEN
PS_FSA_BENEFIT
PS_FSA_PAYMENT
PS_HEALTH_BENEFIT
PS_HEALTH_DEPENDNT
PS_LEAVE_ACCRUAL
PS_LEAVE_PLAN
PS_LIFE_ADD_BEN
PS_LIFE_ADD_BENEFC
PS_PENSION_BENEFC
PS_PENSION_PLAN
PS_RTRMNT_PLAN
PS_SAVINGS_BENEFIC
PS_SAVINGS_INVEST
PS_SAVINGS_PLAN
PS_VACATION_BEN
People Payroll Tables
Payroll Tables
==============
PS_STATE_TAX_DATA
PS_GENL_DEDUCTION
PS_BAL_ADJ_UI_CAN
PS_CAN_CHECK_YTDPS_CAN_DED_BALANCE
PS_CAN_ERN_BALANCE
PS_CAN_TAX_BALANCE
PS_CAN_TAX_DATA
PS_CHECK_YTDPS_DEDUCTION_BAL
PS_EARNINGS_BAL
PS_FED_TAX_DATA
PS_TAX_BALANCE
PS_LOCAL_TAX_DATAPS_BAL_ADJ_ARR
PS_BAL_ADJ_CHK
PS_BAL_ADJ_CN_CHK
PS_BAL_ADJ_CN_DED
PS_BAL_ADJ_CN_ERN PS_BAL_ADJ_CN_TAX
PS_BAL_ADJ_DED
PS_BAL_ADJ_ERN
PS_BAL_ADJ_GRN
PS_BAL_ADJ_TAXPS_DED_ARREARS
PS_GARN_RULE
PS_GARN_SCHED
PS_GARN_SPEC
PS_GENL_DED_CD
==============
PS_STATE_TAX_DATA
PS_GENL_DEDUCTION
PS_BAL_ADJ_UI_CAN
PS_CAN_CHECK_YTDPS_CAN_DED_BALANCE
PS_CAN_ERN_BALANCE
PS_CAN_TAX_BALANCE
PS_CAN_TAX_DATA
PS_CHECK_YTDPS_DEDUCTION_BAL
PS_EARNINGS_BAL
PS_FED_TAX_DATA
PS_TAX_BALANCE
PS_LOCAL_TAX_DATAPS_BAL_ADJ_ARR
PS_BAL_ADJ_CHK
PS_BAL_ADJ_CN_CHK
PS_BAL_ADJ_CN_DED
PS_BAL_ADJ_CN_ERN PS_BAL_ADJ_CN_TAX
PS_BAL_ADJ_DED
PS_BAL_ADJ_ERN
PS_BAL_ADJ_GRN
PS_BAL_ADJ_TAXPS_DED_ARREARS
PS_GARN_RULE
PS_GARN_SCHED
PS_GARN_SPEC
PS_GENL_DED_CD
Tuesday, September 8, 2009
To get the process output location in SQR
To get the process output location in SQR
select PRCSOUTPUTDIR
from PSPRCSPARMS
where PRCSINSTANCE = '123456789'
select PRCSOUTPUTDIR
from PSPRCSPARMS
where PRCSINSTANCE = '123456789'
To know the Run Status when running through the Process Scheduler
To know the Run Status while running through the Process Scheduler
select FIELDVALUE, XLATLONGNAME
from PSXLATITEM
where FIELDNAME = 'RUNSTATUS'
The summary of the run status translates (from PeopleTools 8.49). Note that not all of these are active.
Value Status
===== =======
1 Cancel
2 Delete
3 Error
4 Hold
5 Queued
6 Initiated
7 Processing
8 Cancelled
9 Success
10 Not Successful
11 Posted
12 Unable to Post
13 resend
14 Posting
15 Content Generated
16 Pending
17 Success with Warning
18 Blocked
19 Restart
The following below query will give us the summary of the process run statuses in your process request table
select
RUNSTATUS,
(
select XLATSHORTNAME
from PSXLATITEM
where FIELDNAME = 'RUNSTATUS'
and FIELDVALUE = RUNSTATUS
) as RUNSTATUS_DESCR,
count(PRCSINSTANCE)
from
PSPRCSRQST
group by
RUNSTATUS
order by
RUNSTATUS;
select FIELDVALUE, XLATLONGNAME
from PSXLATITEM
where FIELDNAME = 'RUNSTATUS'
The summary of the run status translates (from PeopleTools 8.49). Note that not all of these are active.
Value Status
===== =======
1 Cancel
2 Delete
3 Error
4 Hold
5 Queued
6 Initiated
7 Processing
8 Cancelled
9 Success
10 Not Successful
11 Posted
12 Unable to Post
13 resend
14 Posting
15 Content Generated
16 Pending
17 Success with Warning
18 Blocked
19 Restart
The following below query will give us the summary of the process run statuses in your process request table
select
RUNSTATUS,
(
select XLATSHORTNAME
from PSXLATITEM
where FIELDNAME = 'RUNSTATUS'
and FIELDVALUE = RUNSTATUS
) as RUNSTATUS_DESCR,
count(PRCSINSTANCE)
from
PSPRCSRQST
group by
RUNSTATUS
order by
RUNSTATUS;
For getting a particular row number from a table
SELECT * FROM ( SELECT *, ROW_NUMBER() OVER (ORDER BY name) as row FROM sys.databases ) a WHERE row > 5 and row <= 10
The query above returns rows 6 through 10 from sys.databases as ordered by the "name" column. ROW_NUMBER() is the key function we're using here. It's one of a set of ranking functions introduced in 2005. Note that it's always accompanied by an OVER clause that specifies the ordering that the row_number should be based on.
U can find the above query in the blow link
http://blogs.msdn.com/sqlserver/archive/2006/10/25/limit-in-sql-server.aspx
The query above returns rows 6 through 10 from sys.databases as ordered by the "name" column. ROW_NUMBER() is the key function we're using here. It's one of a set of ranking functions introduced in 2005. Note that it's always accompanied by an OVER clause that specifies the ordering that the row_number should be based on.
U can find the above query in the blow link
http://blogs.msdn.com/sqlserver/archive/2006/10/25/limit-in-sql-server.aspx
Monday, September 7, 2009
Peoplesoft Hot Keys
We all probably know that “CTRL J” hot key gives us the system information. But did you know that there are a lot more available. But you do not have to trouble your brain to remember them, simply press navigate to a search or transaction page and invoke hot key “CTRL K” to get all the information. Below are the results from PeopleSoft “CTRL K”.
�
Accessing your application using the keyboard
Keyboard navigation is controlled by Hot keys and Access keys .
�
List of Hot Keys
Alt 1 — Executes different buttons depending on the page type
> Save button on the Toolbar in a page
> OK button on a secondary page
> Search or Add button on a Search or Lookup page
Alt 2 — Return to Search
Alt 3 — Next in List
Alt 4 — Previous in List
Alt 5 — Valid Lookup Values
Alt 6 — Related Links
Alt 7 — Insert Row in grid or scroll area
Alt 8 — Delete Row in grid or scroll area
Alt 0 — Refreshes the page by invoking the Refresh button on the Toolbar
Alt . — Next set of rows in grid or scroll area [e.g., Alt period]
Alt , — Previous set of rows in grid or scroll area [e.g., Alt comma]
Alt / — Find in grid or scroll area [e.g., Alt forward slash]
Alt ‘ — View All in grid or scroll area [e.g., Alt prime]
Alt \ — Toggle between Add and Update on the Search page [e.g., Alt backslash]
Ctrl J — System Information
Ctrl K — Keyboard Information
Ctrl Y — Toggle menu between collapse and expand.
Ctrl Tab — Toggles focus through the frame set
Enter — Invokes the following buttons where present: OK, Search, Lookup
Esc — Cancel
�
List of Access Keys
Alt 9 — Takes you to the Help line
Alt \ — Takes you to the Toolbar [e.g., Alt backslash Enter]
Ctrl Z — Takes you to the Search box of the Menu
�
Menu Access Keys
The Ctrl Z combination will focus your cursor onto the menuing system. From there, you can use your tab key (or shift-tab to reverse direction) to navigate through the menu hierarchy.
�
About Access keys and Hot keys
> An Access Key is an Alt key combination that moves focus to a specified field on the current page.
For example, Alt \ moves focus to first button on the Toolbar. Then pressing the Enter key would invoke that action. Or, you may use the Tab key to move you to the next Toolbar button.
> A Hot Key performs an immediate action. For example, when focus is in a field that has lookup processing, Alt 5 invokes the Lookup page without having to press the Enter key.
�
Accessing your application using the keyboard
Keyboard navigation is controlled by Hot keys and Access keys .
�
List of Hot Keys
Alt 1 — Executes different buttons depending on the page type
> Save button on the Toolbar in a page
> OK button on a secondary page
> Search or Add button on a Search or Lookup page
Alt 2 — Return to Search
Alt 3 — Next in List
Alt 4 — Previous in List
Alt 5 — Valid Lookup Values
Alt 6 — Related Links
Alt 7 — Insert Row in grid or scroll area
Alt 8 — Delete Row in grid or scroll area
Alt 0 — Refreshes the page by invoking the Refresh button on the Toolbar
Alt . — Next set of rows in grid or scroll area [e.g., Alt period]
Alt , — Previous set of rows in grid or scroll area [e.g., Alt comma]
Alt / — Find in grid or scroll area [e.g., Alt forward slash]
Alt ‘ — View All in grid or scroll area [e.g., Alt prime]
Alt \ — Toggle between Add and Update on the Search page [e.g., Alt backslash]
Ctrl J — System Information
Ctrl K — Keyboard Information
Ctrl Y — Toggle menu between collapse and expand.
Ctrl Tab — Toggles focus through the frame set
Enter — Invokes the following buttons where present: OK, Search, Lookup
Esc — Cancel
�
List of Access Keys
Alt 9 — Takes you to the Help line
Alt \ — Takes you to the Toolbar [e.g., Alt backslash Enter]
Ctrl Z — Takes you to the Search box of the Menu
�
Menu Access Keys
The Ctrl Z combination will focus your cursor onto the menuing system. From there, you can use your tab key (or shift-tab to reverse direction) to navigate through the menu hierarchy.
�
About Access keys and Hot keys
> An Access Key is an Alt key combination that moves focus to a specified field on the current page.
For example, Alt \ moves focus to first button on the Toolbar. Then pressing the Enter key would invoke that action. Or, you may use the Tab key to move you to the next Toolbar button.
> A Hot Key performs an immediate action. For example, when focus is in a field that has lookup processing, Alt 5 invokes the Lookup page without having to press the Enter key.
Subscribe to:
Posts (Atom)