GPTH_LYOR_VW (SQL View)	Index Back
Lyor Query View Lyor Query View

GPTH_LYOR_VW

(SQL View)

Lyor Query View

SELECT A.EMPLID , A.CAL_RUN_ID , A.EMPL_RCD , A.GP_PAYGROUP , A.CAL_ID , A.ORIG_CAL_RUN_ID , A.RSLT_SEG_NUM , A.GP_RPT_KEY , B.PIN_NUM , C3.PHONE_TYPE , B.INSTANCE , C1.NAME_TITLE , C1.NAME_PREFIX , C1.GPTH_NAME1 , C1.FIRST_NAME , C1.LAST_NAME , C3.BIRTHDATE , C2.ADDRESS1 , C2.ADDRESS2 , C2.ADDRESS3 , C2.ADDRESS4 , C2.CITY , C2.HOUSE_TYPE , C2.COUNTY , C2.STATE , C2.POSTAL , A.PROCESS_INSTANCE , C3.COUNTRY_CODE , C3.PHONE , C3.EXTENSION , C3.GPTH_HOME_PHONE_TY , C3.HOME_PHONE , C3.GPTH_HOME_EXT , C3.NATIONAL_ID , C3.GPTH_PER_TAX_ID , A.PRD_BGN_DT , A.PRD_END_DT , A.SEG_BGN_DT , A.SEG_END_DT , A.PYMT_DT , A.CAL_PRD_ID , A.COUNTRY , A.PIN_GROSS_VAL , A.PIN_NET_VAL , A.CURRENCY_CD , A.CUR_RT_TYPE , A.RUN_TYPE , A.PAY_ENTITY , A.BUSINESS_UNIT , A.COMPANY , A.ESTABID , A.SETID_DEPT , A.DEPTID , A.SETID_LOCATION , A.LOCATION , A.GPTH_GOV , B.ACM_FROM_DT , B.ACM_THRU_DT , B.SLICE_BGN_DT , B.SLICE_END_DT , B.CALC_RSLT_VAL , B.BASE_RSLT_VAL , B.RATE_RSLT_VAL , B.UNIT_RSLT_VAL , B.PCT_RSLT_VAL , B.RSLT_ADD_ARR , B.RSLT_NOT_TKN , B.RSLT_PAYBK , B.RSLT_XFER_VAL , B.BASE_ADJ_VAL , B.CALC_ADJ_VAL , B.UNIT_ADJ_VAL , B.USER_ADJ_VAL , B.USER_KEY1 , B.PIN_TYPE , B.PIN_NM , B.PIN_CODE , A.MAR_STATUS , G.GPTH_TX_SPO_IND , G.GPTH_TX_PAFA , G.GPTH_TX_PAMOT , G.GPTH_TX_SPO_MOT , G.GPTH_TX_SPO_FA , G.GPTH_TX_MF_NM , G.GPTH_TX_LTF_NAME , G.GPTH_TX_CHD_ALO_NO , B.PIN_CUSTOM1 , G.GPTH_TX_HI_PAFA , G.GPTH_TX_HI_PAMO , G.GPTH_TX_HI_SPOFA , G.GPTH_TX_HI_SPOMO FROM PS_GPTH_RPT_G_VW A LEFT OUTER JOIN PS_GPTH_PERSON_NM C1 ON (A.EMPLID = C1.EMPLID AND A.PROCESS_INSTANCE=C1.PROCESS_INSTANCE) LEFT OUTER JOIN PS_GPTH_PER_ADDR C2 ON (A.EMPLID = C2.EMPLID AND A.PROCESS_INSTANCE=C2.PROCESS_INSTANCE) LEFT OUTER JOIN PS_GPTH_TX_ALOW_VW G ON (A.EMPLID = G.EMPLID AND A.PROCESS_INSTANCE=G.PROCESS_INSTANCE) ,PS_GPTH_RPT_D_VW B ,PS_GPTH_PERSON_VW C3 WHERE A.PROCESS_INSTANCE = B.PROCESS_INSTANCE AND A.EMPLID = B.EMPLID AND A.CAL_RUN_ID = B.CAL_RUN_ID AND A.EMPL_RCD = B.EMPL_RCD AND A.GP_PAYGROUP = B.GP_PAYGROUP AND A.CAL_ID = B.CAL_ID AND A.ORIG_CAL_RUN_ID = B.ORIG_CAL_RUN_ID AND A.RSLT_SEG_NUM = B.RSLT_SEG_NUM AND A.GP_RPT_KEY = B.GP_RPT_KEY AND A.EMPLID=C3.EMPLID

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

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

2 CAL_RUN_ID Character(18) VARCHAR2(18) NOT NULL Calendar Run Id

3 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record

4 GP_PAYGROUP Character(10) VARCHAR2(10) NOT NULL Global Payroll pay group

5 CAL_ID Character(18) VARCHAR2(18) NOT NULL calendar id

6 ORIG_CAL_RUN_ID Character(18) VARCHAR2(18) NOT NULL Original Calendar Group ID

7 RSLT_SEG_NUM Number(4,0) SMALLINT NOT NULL Result Segment Number

8 GP_RPT_KEY Character(22) VARCHAR2(22) NOT NULL Output Result Key

9 PIN_NUM Number(8,0) INTEGER NOT NULL PIN Number

10 PHONE_TYPE Character(4) VARCHAR2(4) NOT NULL Phone Type
BUSN=Business
CAMP=Campus
CELL=Mobile
DORM=Dormitory
FAX=FAX
HOME=Home
MAIN=Main
OTR=Other
PGR1=Pager 1
PGR2=Pager 2
TELX=Telex
WORK=Work

11 INSTANCE Number(3,0) SMALLINT NOT NULL Instance Number

12 NAME_TITLE Character(30) VARCHAR2(30) NOT NULL Name Title

13 NAME_PREFIX Character(4) VARCHAR2(4) NOT NULL Name Prefix

14 GPTH_NAME1 Character(80) VARCHAR2(80) NOT NULL Tax-Payee's First Name

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

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

17 BIRTHDATE Date(10) DATE Date of Birth

18 ADDRESS1 Character(55) VARCHAR2(55) NOT NULL Address 1

19 ADDRESS2 Character(55) VARCHAR2(55) NOT NULL Address 2

20 ADDRESS3 Character(55) VARCHAR2(55) NOT NULL Address 3

21 ADDRESS4 Character(55) VARCHAR2(55) NOT NULL Address 4

22 CITY Character(30) VARCHAR2(30) NOT NULL City

23 HOUSE_TYPE Character(2) VARCHAR2(2) NOT NULL House Type
AB=House Boat Reference
WW=House Trailer Reference

24 COUNTY Character(30) VARCHAR2(30) NOT NULL County

25 STATE Character(6) VARCHAR2(6) NOT NULL State
Prompt Table: STATE_TBL

26 POSTAL Character(12) VARCHAR2(12) NOT NULL Postal

27 PROCESS_INSTANCE Number(10,0) DECIMAL(10) NOT NULL Process Instance

28 COUNTRY_CODE Character(3) VARCHAR2(3) NOT NULL Country Code

29 PHONE Character(24) VARCHAR2(24) NOT NULL Telephone

30 EXTENSION Character(6) VARCHAR2(6) NOT NULL Phone number extension

31 GPTH_HOME_PHONE_TY Character(4) VARCHAR2(4) NOT NULL Phone Type

32 HOME_PHONE Character(24) VARCHAR2(24) NOT NULL Home Phone #

33 GPTH_HOME_EXT Character(6) VARCHAR2(6) NOT NULL Phone number extension

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

35 GPTH_PER_TAX_ID Character(20) VARCHAR2(20) NOT NULL Person Tax ID

36 PRD_BGN_DT Date(10) DATE Period Begin Date

37 PRD_END_DT Date(10) DATE Pay Period End Date

38 SEG_BGN_DT Date(10) DATE Payee Process Segment's Begin Date

39 SEG_END_DT Date(10) DATE Payee Process Segment's End Date

40 PYMT_DT Date(10) DATE Payment Date

41 CAL_PRD_ID Character(10) VARCHAR2(10) NOT NULL Calendar Period ID

42 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country

43 PIN_GROSS_VAL Signed Number(20,6) DECIMAL(18,6) NOT NULL Gross Pay Value

44 PIN_NET_VAL Signed Number(20,6) DECIMAL(18,6) NOT NULL Net Pay Value

45 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code

46 CUR_RT_TYPE Character(5) VARCHAR2(5) NOT NULL Currency Rate Type

47 RUN_TYPE Character(10) VARCHAR2(10) NOT NULL Run Type Name

48 PAY_ENTITY Character(10) VARCHAR2(10) NOT NULL Pay Entity

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

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

51 ESTABID Character(12) VARCHAR2(12) NOT NULL Establishment ID

52 SETID_DEPT Character(5) VARCHAR2(5) NOT NULL Department Set ID

53 DEPTID Character(10) VARCHAR2(10) NOT NULL Department

54 SETID_LOCATION Character(5) VARCHAR2(5) NOT NULL Location Set ID

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

56 GPTH_GOV Character(1) VARCHAR2(1) NOT NULL This field represents that if the report is an Government report

57 ACM_FROM_DT Date(10) DATE From Date

58 ACM_THRU_DT Date(10) DATE Through Date

59 SLICE_BGN_DT Date(10) DATE Element Slice Begin Date

60 SLICE_END_DT Date(10) DATE Element Slice End Date

61 CALC_RSLT_VAL Signed Number(20,6) DECIMAL(18,6) NOT NULL Calculation Numeric Result

62 BASE_RSLT_VAL Signed Number(20,6) DECIMAL(18,6) NOT NULL Calculated Base Value

63 RATE_RSLT_VAL Signed Number(20,6) DECIMAL(18,6) NOT NULL Rate Result Value

64 UNIT_RSLT_VAL Signed Number(20,6) DECIMAL(18,6) NOT NULL Number of units used in result calculation

65 PCT_RSLT_VAL Signed Number(20,6) DECIMAL(18,6) NOT NULL Percent Result Value

66 RSLT_ADD_ARR Signed Number(20,6) DECIMAL(18,6) NOT NULL Amount Added To Arrears

67 RSLT_NOT_TKN Signed Number(20,6) DECIMAL(18,6) NOT NULL Amount Not Taken

68 RSLT_PAYBK Signed Number(20,6) DECIMAL(18,6) NOT NULL Payback Amount Result

69 RSLT_XFER_VAL Signed Number(20,6) DECIMAL(18,6) NOT NULL Bank Transfer Value of Deduct

70 BASE_ADJ_VAL Signed Number(20,6) DECIMAL(18,6) NOT NULL Base Adjustment

71 CALC_ADJ_VAL Signed Number(20,6) DECIMAL(18,6) NOT NULL Calculation Adjustment Value

72 UNIT_ADJ_VAL Signed Number(20,6) DECIMAL(18,6) NOT NULL Unit Adjustment

73 USER_ADJ_VAL Signed Number(20,6) DECIMAL(18,6) NOT NULL User Adjustment Value

74 USER_KEY1 Character(25) VARCHAR2(25) NOT NULL User Key 1

75 PIN_TYPE Character(2) VARCHAR2(2) NOT NULL Element Type

76 PIN_NM Character(18) VARCHAR2(18) NOT NULL Element Name field

77 PIN_CODE Character(22) VARCHAR2(22) NOT NULL Unique identifier of a PIN (alternate to PIN number). critical for use in merging packages of elements from one database onto another when PINs may or may not be renumbered as they are moved across. Constructed from PIN_NM + COUNTRY of the element definition (GP_PIN)

78 MAR_STATUS Character(1) VARCHAR2(1) NOT NULL Marital Status
C=Common-Law
D=Divorced
E=Separated
H=Head of Household
L=DissDeclLost Civil Partner
M=Married
P=Civil Partnership
S=Single
T=Surviving Civil Partner
U=Unknown
V=Dissolved Civil Partnership
W=Widowed

79 GPTH_TX_SPO_IND Number(1,0) SMALLINT NOT NULL Claim spouse allowance

80 GPTH_TX_PAFA Character(1) VARCHAR2(1) NOT NULL Declare for Payee's Father

81 GPTH_TX_PAMOT Character(1) VARCHAR2(1) NOT NULL Declare for Payee's Mother

82 GPTH_TX_SPO_MOT Character(1) VARCHAR2(1) NOT NULL Declare for Spouse's Mother

83 GPTH_TX_SPO_FA Character(1) VARCHAR2(1) NOT NULL Declare for Spouse's Father

84 GPTH_TX_MF_NM Character(200) VARCHAR2(200) NOT NULL Mutual Fund Name

85 GPTH_TX_LTF_NAME Character(200) VARCHAR2(200) NOT NULL LTF Name

86 GPTH_TX_CHD_ALO_NO Number(3,0) SMALLINT NOT NULL Number of child for Allowance

87 PIN_CUSTOM1 Character(20) VARCHAR2(20) NOT NULL Customer FIelds

88 GPTH_TX_HI_PAFA Character(1) VARCHAR2(1) NOT NULL Declare HI for Payee's Father

89 GPTH_TX_HI_PAMO Character(1) VARCHAR2(1) NOT NULL Declare HI for Payee's Mother

90 GPTH_TX_HI_SPOFA Character(1) VARCHAR2(1) NOT NULL Declare HI for Spouse's Father

91 GPTH_TX_HI_SPOMO Character(1) VARCHAR2(1) NOT NULL Declare HI for Spouse's Mother

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	EMPLID	Character(11)	VARCHAR2(11) NOT NULL	Employee ID
2	CAL_RUN_ID	Character(18)	VARCHAR2(18) NOT NULL	Calendar Run Id
3	EMPL_RCD	Number(3,0)	SMALLINT NOT NULL	Empl Record
4	GP_PAYGROUP	Character(10)	VARCHAR2(10) NOT NULL	Global Payroll pay group
5	CAL_ID	Character(18)	VARCHAR2(18) NOT NULL	calendar id
6	ORIG_CAL_RUN_ID	Character(18)	VARCHAR2(18) NOT NULL	Original Calendar Group ID
7	RSLT_SEG_NUM	Number(4,0)	SMALLINT NOT NULL	Result Segment Number
8	GP_RPT_KEY	Character(22)	VARCHAR2(22) NOT NULL	Output Result Key
9	PIN_NUM	Number(8,0)	INTEGER NOT NULL	PIN Number
10	PHONE_TYPE	Character(4)	VARCHAR2(4) NOT NULL	Phone Type BUSN=Business CAMP=Campus CELL=Mobile DORM=Dormitory FAX=FAX HOME=Home MAIN=Main OTR=Other PGR1=Pager 1 PGR2=Pager 2 TELX=Telex WORK=Work
11	INSTANCE	Number(3,0)	SMALLINT NOT NULL	Instance Number
12	NAME_TITLE	Character(30)	VARCHAR2(30) NOT NULL	Name Title
13	NAME_PREFIX	Character(4)	VARCHAR2(4) NOT NULL	Name Prefix
14	GPTH_NAME1	Character(80)	VARCHAR2(80) NOT NULL	Tax-Payee's First Name
15	FIRST_NAME	Character(30)	VARCHAR2(30) NOT NULL	First Name
16	LAST_NAME	Character(30)	VARCHAR2(30) NOT NULL	Last Name
17	BIRTHDATE	Date(10)	DATE	Date of Birth
18	ADDRESS1	Character(55)	VARCHAR2(55) NOT NULL	Address 1
19	ADDRESS2	Character(55)	VARCHAR2(55) NOT NULL	Address 2
20	ADDRESS3	Character(55)	VARCHAR2(55) NOT NULL	Address 3
21	ADDRESS4	Character(55)	VARCHAR2(55) NOT NULL	Address 4
22	CITY	Character(30)	VARCHAR2(30) NOT NULL	City
23	HOUSE_TYPE	Character(2)	VARCHAR2(2) NOT NULL	House Type AB=House Boat Reference WW=House Trailer Reference
24	COUNTY	Character(30)	VARCHAR2(30) NOT NULL	County
25	STATE	Character(6)	VARCHAR2(6) NOT NULL	State Prompt Table: STATE_TBL
26	POSTAL	Character(12)	VARCHAR2(12) NOT NULL	Postal
27	PROCESS_INSTANCE	Number(10,0)	DECIMAL(10) NOT NULL	Process Instance
28	COUNTRY_CODE	Character(3)	VARCHAR2(3) NOT NULL	Country Code
29	PHONE	Character(24)	VARCHAR2(24) NOT NULL	Telephone
30	EXTENSION	Character(6)	VARCHAR2(6) NOT NULL	Phone number extension
31	GPTH_HOME_PHONE_TY	Character(4)	VARCHAR2(4) NOT NULL	Phone Type
32	HOME_PHONE	Character(24)	VARCHAR2(24) NOT NULL	Home Phone #
33	GPTH_HOME_EXT	Character(6)	VARCHAR2(6) NOT NULL	Phone number extension
34	NATIONAL_ID	Character(20)	VARCHAR2(20) NOT NULL	National ID
35	GPTH_PER_TAX_ID	Character(20)	VARCHAR2(20) NOT NULL	Person Tax ID
36	PRD_BGN_DT	Date(10)	DATE	Period Begin Date
37	PRD_END_DT	Date(10)	DATE	Pay Period End Date
38	SEG_BGN_DT	Date(10)	DATE	Payee Process Segment's Begin Date
39	SEG_END_DT	Date(10)	DATE	Payee Process Segment's End Date
40	PYMT_DT	Date(10)	DATE	Payment Date
41	CAL_PRD_ID	Character(10)	VARCHAR2(10) NOT NULL	Calendar Period ID
42	COUNTRY	Character(3)	VARCHAR2(3) NOT NULL	Country
43	PIN_GROSS_VAL	Signed Number(20,6)	DECIMAL(18,6) NOT NULL	Gross Pay Value
44	PIN_NET_VAL	Signed Number(20,6)	DECIMAL(18,6) NOT NULL	Net Pay Value
45	CURRENCY_CD	Character(3)	VARCHAR2(3) NOT NULL	Currency Code
46	CUR_RT_TYPE	Character(5)	VARCHAR2(5) NOT NULL	Currency Rate Type
47	RUN_TYPE	Character(10)	VARCHAR2(10) NOT NULL	Run Type Name
48	PAY_ENTITY	Character(10)	VARCHAR2(10) NOT NULL	Pay Entity
49	BUSINESS_UNIT	Character(5)	VARCHAR2(5) NOT NULL	Business Unit
50	COMPANY	Character(3)	VARCHAR2(3) NOT NULL	Company
51	ESTABID	Character(12)	VARCHAR2(12) NOT NULL	Establishment ID
52	SETID_DEPT	Character(5)	VARCHAR2(5) NOT NULL	Department Set ID
53	DEPTID	Character(10)	VARCHAR2(10) NOT NULL	Department
54	SETID_LOCATION	Character(5)	VARCHAR2(5) NOT NULL	Location Set ID
55	LOCATION	Character(10)	VARCHAR2(10) NOT NULL	Location Code
56	GPTH_GOV	Character(1)	VARCHAR2(1) NOT NULL	This field represents that if the report is an Government report
57	ACM_FROM_DT	Date(10)	DATE	From Date
58	ACM_THRU_DT	Date(10)	DATE	Through Date
59	SLICE_BGN_DT	Date(10)	DATE	Element Slice Begin Date
60	SLICE_END_DT	Date(10)	DATE	Element Slice End Date
61	CALC_RSLT_VAL	Signed Number(20,6)	DECIMAL(18,6) NOT NULL	Calculation Numeric Result
62	BASE_RSLT_VAL	Signed Number(20,6)	DECIMAL(18,6) NOT NULL	Calculated Base Value
63	RATE_RSLT_VAL	Signed Number(20,6)	DECIMAL(18,6) NOT NULL	Rate Result Value
64	UNIT_RSLT_VAL	Signed Number(20,6)	DECIMAL(18,6) NOT NULL	Number of units used in result calculation
65	PCT_RSLT_VAL	Signed Number(20,6)	DECIMAL(18,6) NOT NULL	Percent Result Value
66	RSLT_ADD_ARR	Signed Number(20,6)	DECIMAL(18,6) NOT NULL	Amount Added To Arrears
67	RSLT_NOT_TKN	Signed Number(20,6)	DECIMAL(18,6) NOT NULL	Amount Not Taken
68	RSLT_PAYBK	Signed Number(20,6)	DECIMAL(18,6) NOT NULL	Payback Amount Result
69	RSLT_XFER_VAL	Signed Number(20,6)	DECIMAL(18,6) NOT NULL	Bank Transfer Value of Deduct
70	BASE_ADJ_VAL	Signed Number(20,6)	DECIMAL(18,6) NOT NULL	Base Adjustment
71	CALC_ADJ_VAL	Signed Number(20,6)	DECIMAL(18,6) NOT NULL	Calculation Adjustment Value
72	UNIT_ADJ_VAL	Signed Number(20,6)	DECIMAL(18,6) NOT NULL	Unit Adjustment
73	USER_ADJ_VAL	Signed Number(20,6)	DECIMAL(18,6) NOT NULL	User Adjustment Value
74	USER_KEY1	Character(25)	VARCHAR2(25) NOT NULL	User Key 1
75	PIN_TYPE	Character(2)	VARCHAR2(2) NOT NULL	Element Type
76	PIN_NM	Character(18)	VARCHAR2(18) NOT NULL	Element Name field
77	PIN_CODE	Character(22)	VARCHAR2(22) NOT NULL	Unique identifier of a PIN (alternate to PIN number). critical for use in merging packages of elements from one database onto another when PINs may or may not be renumbered as they are moved across. Constructed from PIN_NM + COUNTRY of the element definition (GP_PIN)
78	MAR_STATUS	Character(1)	VARCHAR2(1) NOT NULL	Marital Status C=Common-Law D=Divorced E=Separated H=Head of Household L=DissDeclLost Civil Partner M=Married P=Civil Partnership S=Single T=Surviving Civil Partner U=Unknown V=Dissolved Civil Partnership W=Widowed
79	GPTH_TX_SPO_IND	Number(1,0)	SMALLINT NOT NULL	Claim spouse allowance
80	GPTH_TX_PAFA	Character(1)	VARCHAR2(1) NOT NULL	Declare for Payee's Father
81	GPTH_TX_PAMOT	Character(1)	VARCHAR2(1) NOT NULL	Declare for Payee's Mother
82	GPTH_TX_SPO_MOT	Character(1)	VARCHAR2(1) NOT NULL	Declare for Spouse's Mother
83	GPTH_TX_SPO_FA	Character(1)	VARCHAR2(1) NOT NULL	Declare for Spouse's Father
84	GPTH_TX_MF_NM	Character(200)	VARCHAR2(200) NOT NULL	Mutual Fund Name
85	GPTH_TX_LTF_NAME	Character(200)	VARCHAR2(200) NOT NULL	LTF Name
86	GPTH_TX_CHD_ALO_NO	Number(3,0)	SMALLINT NOT NULL	Number of child for Allowance
87	PIN_CUSTOM1	Character(20)	VARCHAR2(20) NOT NULL	Customer FIelds
88	GPTH_TX_HI_PAFA	Character(1)	VARCHAR2(1) NOT NULL	Declare HI for Payee's Father
89	GPTH_TX_HI_PAMO	Character(1)	VARCHAR2(1) NOT NULL	Declare HI for Payee's Mother
90	GPTH_TX_HI_SPOFA	Character(1)	VARCHAR2(1) NOT NULL	Declare HI for Spouse's Father
91	GPTH_TX_HI_SPOMO	Character(1)	VARCHAR2(1) NOT NULL	Declare HI for Spouse's Mother