ASGN_CMP_SRCH (SQL View)	Index Back
Search Vw-EE Assign Compensatn ASGN_CMP_SRCH is the search view for employee compensation data for an employee on a job assignments away from home base. This functionality is part of the International Assignment application. This view invokes departmental security. It restricts operator access to compensation records for employees only in departments the operator is authorized to access.

ASGN_CMP_SRCH

(SQL View)

Search Vw-EE Assign Compensatn

ASGN_CMP_SRCH is the search view for employee compensation data for an employee on a job assignments away from home base. This functionality is part of the International Assignment application. This view invokes departmental security. It restricts operator access to compensation records for employees only in departments the operator is authorized to access.

SELECT H.PAY_INSTR_TYPE , %Sql(SCRTY_SEL_PKEY,SEC,SEC) , %Sql(SCRTY_SEL_PERSBR,SEC,SEC) , %Sql(SCRTY_SEL_FLDSBR,SEC) , SEC.NAME_AC FROM PS_ASGN_CMP_EFFDT H , PS_PERS_SRCH_GBL SEC WHERE H.EMPLID = SEC.EMPLID

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 PAY_INSTR_TYPE Character(1) VARCHAR2(1) NOT NULL Payment Instruction Type
I=International Compensation
O=Onetime payment

2 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).

3 ROWSECCLASS Character(30) VARCHAR2(30) NOT NULL The class used to determine row level security

4 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

5 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Rcd Nbr

6 NAME Character(50) VARCHAR2(50) NOT NULL Name

7 NAME_DISPLAY_SRCH Character(50) VARCHAR2(50) NOT NULL Uppercase version of Name to improve performance on searches.

8 NAME_PSFORMAT Character(50) VARCHAR2(50) NOT NULL A Person's name in the Peoplesoft Format (basically LN,FN MI

9 LAST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL Last Name

10 SECOND_LAST_SRCH Character(30) VARCHAR2(30) NOT NULL Second Name

11 FIRST_NAME Character(30) VARCHAR2(30) NOT NULL First Name

12 LAST_NAME Character(30) VARCHAR2(30) NOT NULL Last Name

13 SECOND_LAST_NAME Character(30) VARCHAR2(30) NOT NULL Second Last Name

14 NAME_AC Character(50) VARCHAR2(50) NOT NULL Alternate Character Name

15 MIDDLE_NAME Character(30) VARCHAR2(30) NOT NULL MIDDLE_NAME

16 NAME_DISPLAY Character(50) VARCHAR2(50) NOT NULL Display Name - name formatted for Display based on the Country

17 PER_ORG Character(3) VARCHAR2(3) NOT NULL Defines the Organizational Relationship(s) that a Person has to the Organization. These are Employee, Contingent Worker, and Persons of Interest.
CWR=Contingent Worker
EMP=Employee
POI=Person of Interest

18 POI_TYPE Character(5) VARCHAR2(5) NOT NULL Defines the Transactional usage of POIs in HRMS.

19 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

20 SETID_DEPT Character(5) VARCHAR2(5) NOT NULL Department SetID
Prompt Table: SETID_TBL

21 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
Prompt Table: PERS_DEPT_VW

22 SETID_LOCATION Character(5) VARCHAR2(5) NOT NULL Location SetID

23 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code

24 COMPANY Character(3) VARCHAR2(3) NOT NULL Company

25 REG_REGION Character(5) VARCHAR2(5) NOT NULL Regulatory Region

26 INSTITUTION Character(5) VARCHAR2(5) NOT NULL Academic Institution

27 NATIONAL_ID Character(20) VARCHAR2(20) NOT NULL National ID

28 HR_STATUS Character(1) VARCHAR2(1) NOT NULL HR Status
A=Active
I=Inactive

29 MILITARY_SERVICE Character(8) VARCHAR2(8) NOT NULL Military Service
Prompt Table: MIL_SERVICE_TBL

30 MIL_RANK Character(5) VARCHAR2(5) NOT NULL Military Rank
Prompt Table: MIL_RANK_TBL

31 MIL_WORN_RANK Character(5) VARCHAR2(5) NOT NULL Worn Rank
Prompt Table: MIL_RANK_TBL

32 APPT_TYPE Character(1) VARCHAR2(1) NOT NULL This field tracks whether a row is a Main Appointment (which all rows outside Japan are), or an Additional Appointment, which is a feature only used by Japanese customers.
0=Core Appointment
1=Japan Kenmu Appointment
2=French Public Sector Appt
3=Staffing

33 EMPL_STATUS Character(1) VARCHAR2(1) NOT NULL Payroll Status
A=Active
D=Deceased
L=Leave of Absence
P=Leave With Pay
Q=Retired With Pay
R=Retired
S=Suspended
T=Terminated
U=Terminated With Pay
V=Terminated Pension Pay Out
W=Short Work Break
X=Retired-Pension Administration

34 OTHER_ID_JPN Character(11) VARCHAR2(11) NOT NULL Other Identifier

35 FUTURE_FLG Character(1) VARCHAR2(1) NOT NULL Flag for the SJT_*** record that indicates whether this is a future dated row or not. Y = Future N = Current

36 REPORTS_TO Character(8) VARCHAR2(8) NOT NULL Reports To Position Number

37 SUPERVISOR_ID Character(11) VARCHAR2(11) NOT NULL Supervisor ID

38 SETID_JOBCODE Character(5) VARCHAR2(5) NOT NULL Job Code SetID

39 JOBCODE Character(6) VARCHAR2(6) NOT NULL Job Code

40 NAME_AC_SEC Character(50) VARCHAR2(50) NOT NULL Alternate Character Name

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	PAY_INSTR_TYPE	Character(1)	VARCHAR2(1) NOT NULL	Payment Instruction Type I=International Compensation O=Onetime payment
2	OPRID	Character(30)	VARCHAR2(30) NOT NULL	A user's ID (see PSOPRDEFN).
3	ROWSECCLASS	Character(30)	VARCHAR2(30) NOT NULL	The class used to determine row level security
4	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID
5	EMPL_RCD	Number(3,0)	SMALLINT NOT NULL	Empl Rcd Nbr
6	NAME	Character(50)	VARCHAR2(50) NOT NULL	Name
7	NAME_DISPLAY_SRCH	Character(50)	VARCHAR2(50) NOT NULL	Uppercase version of Name to improve performance on searches.
8	NAME_PSFORMAT	Character(50)	VARCHAR2(50) NOT NULL	A Person's name in the Peoplesoft Format (basically LN,FN MI
9	LAST_NAME_SRCH	Character(30)	VARCHAR2(30) NOT NULL	Last Name
10	SECOND_LAST_SRCH	Character(30)	VARCHAR2(30) NOT NULL	Second Name
11	FIRST_NAME	Character(30)	VARCHAR2(30) NOT NULL	First Name
12	LAST_NAME	Character(30)	VARCHAR2(30) NOT NULL	Last Name
13	SECOND_LAST_NAME	Character(30)	VARCHAR2(30) NOT NULL	Second Last Name
14	NAME_AC	Character(50)	VARCHAR2(50) NOT NULL	Alternate Character Name
15	MIDDLE_NAME	Character(30)	VARCHAR2(30) NOT NULL	MIDDLE_NAME
16	NAME_DISPLAY	Character(50)	VARCHAR2(50) NOT NULL	Display Name - name formatted for Display based on the Country
17	PER_ORG	Character(3)	VARCHAR2(3) NOT NULL	Defines the Organizational Relationship(s) that a Person has to the Organization. These are Employee, Contingent Worker, and Persons of Interest. CWR=Contingent Worker EMP=Employee POI=Person of Interest
18	POI_TYPE	Character(5)	VARCHAR2(5) NOT NULL	Defines the Transactional usage of POIs in HRMS.
19	BUSINESS_UNIT	Character(5)	VARCHAR2(5) NOT NULL	Business Unit
20	SETID_DEPT	Character(5)	VARCHAR2(5) NOT NULL	Department SetID Prompt Table: SETID_TBL
21	DEPTID	Character(10)	VARCHAR2(10) NOT NULL	Department Prompt Table: PERS_DEPT_VW
22	SETID_LOCATION	Character(5)	VARCHAR2(5) NOT NULL	Location SetID
23	LOCATION	Character(10)	VARCHAR2(10) NOT NULL	Location Code
24	COMPANY	Character(3)	VARCHAR2(3) NOT NULL	Company
25	REG_REGION	Character(5)	VARCHAR2(5) NOT NULL	Regulatory Region
26	INSTITUTION	Character(5)	VARCHAR2(5) NOT NULL	Academic Institution
27	NATIONAL_ID	Character(20)	VARCHAR2(20) NOT NULL	National ID
28	HR_STATUS	Character(1)	VARCHAR2(1) NOT NULL	HR Status A=Active I=Inactive
29	MILITARY_SERVICE	Character(8)	VARCHAR2(8) NOT NULL	Military Service Prompt Table: MIL_SERVICE_TBL
30	MIL_RANK	Character(5)	VARCHAR2(5) NOT NULL	Military Rank Prompt Table: MIL_RANK_TBL
31	MIL_WORN_RANK	Character(5)	VARCHAR2(5) NOT NULL	Worn Rank Prompt Table: MIL_RANK_TBL
32	APPT_TYPE	Character(1)	VARCHAR2(1) NOT NULL	This field tracks whether a row is a Main Appointment (which all rows outside Japan are), or an Additional Appointment, which is a feature only used by Japanese customers. 0=Core Appointment 1=Japan Kenmu Appointment 2=French Public Sector Appt 3=Staffing
33	EMPL_STATUS	Character(1)	VARCHAR2(1) NOT NULL	Payroll Status A=Active D=Deceased L=Leave of Absence P=Leave With Pay Q=Retired With Pay R=Retired S=Suspended T=Terminated U=Terminated With Pay V=Terminated Pension Pay Out W=Short Work Break X=Retired-Pension Administration
34	OTHER_ID_JPN	Character(11)	VARCHAR2(11) NOT NULL	Other Identifier
35	FUTURE_FLG	Character(1)	VARCHAR2(1) NOT NULL	Flag for the SJT_*** record that indicates whether this is a future dated row or not. Y = Future N = Current
36	REPORTS_TO	Character(8)	VARCHAR2(8) NOT NULL	Reports To Position Number
37	SUPERVISOR_ID	Character(11)	VARCHAR2(11) NOT NULL	Supervisor ID
38	SETID_JOBCODE	Character(5)	VARCHAR2(5) NOT NULL	Job Code SetID
39	JOBCODE	Character(6)	VARCHAR2(6) NOT NULL	Job Code
40	NAME_AC_SEC	Character(50)	VARCHAR2(50) NOT NULL	Alternate Character Name