GPTH_RPT_PIT_VW

(SQL View)
 Index Back

PIT Report View

PIT Report 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_PREFIX , C1.GPTH_NAME1 , C1.FIRST_NAME , C1.MIDDLE_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 , 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 , D.GPTH_SPO_STATUS , D.GPTH_TX_SPO_IND , D.GPTH_TX_PAFA , D.GPTH_TX_PAMOT , D.GPTH_TX_SPO_FA , D.GPTH_TX_SPO_MOT , D.GPTH_TX_HI_PAFA , D.GPTH_TX_HI_PAMO , D.GPTH_TX_HI_SPOFA , D.GPTH_TX_HI_SPOMO , D.GPTH_FHOUSE_PRICE , %Substring(E1.NATIONAL_ID,1,13) , E2.NATIONAL_ID , E3.NATIONAL_ID , E4.NATIONAL_ID , A.MAR_STATUS , %Substring(E0.NATIONAL_ID,1,13) , %Substring(E0.GPTH_PER_TAX_ID,1,13) , E0.BIRTHDATE , E0.NAME_PREFIX , E0.GPTH_NAME1 , E0.FIRST_NAME , E0.LAST_NAME , A.PROCESS_INSTANCE , A.OFF_CYCLE , 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 , D.GPTH_SEQ_NUM , G.GPTH_TAX_ID , G.GPTH_TAX_BRANCH , G.GPTH_TAX_ID_L , F.GPTH_SI_ER_PER , F.GPTH_SI_ER_ACCT , H.GPTH_SI_BRANCH_NO , B.ACM_FROM_DT , B.ACM_THRU_DT , B.SLICE_BGN_DT , B.SLICE_END_DT , %Round(B.CALC_RSLT_VAL ,2) , 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.USER_KEY2 , B.PIN_TYPE , B.PIN_NM , B.PIN_CODE , B.PIN_CUSTOM1 ,D.GPTH_TX_PP ,D.GPTH_TX_PP_ID ,D.GPTH_TX_SPP ,D.GPTH_TX_SPP_ID ,J.DT_OF_DEATH ,K.GPTH_PF_TYPE FROM PS_GPTH_RPT_G_VW A LEFT OUTER JOIN PS_GPTH_SI_COMP_VW F ON (A.COMPANY=F.COMPANY AND A.EMPLID=F.EMPLID AND A.PROCESS_INSTANCE=F.PROCESS_INSTANCE) LEFT OUTER JOIN PS_GPTH_TAX_BRN_VW G ON (A.COMPANY=G.COMPANY AND A.EMPLID=G.EMPLID AND A.PROCESS_INSTANCE=G.PROCESS_INSTANCE) LEFT OUTER JOIN PS_GPTH_TX_ALOW_VW D ON (A.EMPLID=D.EMPLID AND A.PROCESS_INSTANCE=D.PROCESS_INSTANCE) LEFT OUTER JOIN PS_GPTH_SI_PYE_VW H ON (A.EMPLID=H.EMPLID AND A.PROCESS_INSTANCE=H.PROCESS_INSTANCE) 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_BENEF_VW0 E0 ON (A.EMPLID=E0.EMPLID AND A.PROCESS_INSTANCE=E0.PROCESS_INSTANCE) LEFT OUTER JOIN PS_GPTH_DEP_NID_VW E1 ON (A.EMPLID=E1.EMPLID AND E1.DEP_BENEF_TYPE IN ('B' ,'O') AND E1.RELATIONSHIP = 'P' AND E1.SEX = 'M' AND E1.NATIONAL_ID_TYPE = 'PID') LEFT OUTER JOIN PS_GPTH_DEP_NID_VW E2 ON (A.EMPLID=E2.EMPLID AND E2.DEP_BENEF_TYPE IN ('B' ,'O') AND E2.RELATIONSHIP = 'P' AND E2.SEX = 'F' AND E2.NATIONAL_ID_TYPE = 'PID') LEFT OUTER JOIN PS_GPTH_DEP_NID_VW E3 ON (A.EMPLID=E3.EMPLID AND E3.DEP_BENEF_TYPE IN ('B' ,'O') AND E3.RELATIONSHIP = 'PI' AND E3.SEX = 'M' AND E3.NATIONAL_ID_TYPE = 'PID') LEFT OUTER JOIN PS_GPTH_DEP_NID_VW E4 ON (A.EMPLID=E4.EMPLID AND E4.DEP_BENEF_TYPE IN ('B' ,'O') AND E4.RELATIONSHIP = 'PI' AND E4.SEX = 'F' AND E4.NATIONAL_ID_TYPE = 'PID') LEFT OUTER JOIN PS_GPTH_PF_PAYEE K ON (A.EMPLID=K.EMPLID) , PS_GPTH_RPT_D_VW B , PS_GPTH_PERSON_VW C3 , PS_PERSON J 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 AND A.EMPLID=J.EMPLID AND ((K.EFFDT IS NULL) OR (K.EFFDT= ( SELECT MAX(KD.EFFDT) FROM PS_GPTH_PF_PAYEE KD WHERE KD.EMPLID=K.EMPLID AND KD.EFFDT<=A.PRD_END_DT)))

# 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 Rcd Nbr
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_PREFIX Character(4) VARCHAR2(4) NOT NULL Name Prefix
13 GPTH_NAME1 Character(80) VARCHAR2(80) NOT NULL Tax-Payee's First Name
14 FIRST_NAME Character(30) VARCHAR2(30) NOT NULL First Name
15 MIDDLE_NAME Character(30) VARCHAR2(30) NOT NULL MIDDLE_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 COUNTRY_CODE Character(3) VARCHAR2(3) NOT NULL Country Code
28 PHONE Character(24) VARCHAR2(24) NOT NULL Telephone
29 EXTENSION Character(6) VARCHAR2(6) NOT NULL Phone number extension
30 GPTH_HOME_PHONE_TY Character(4) VARCHAR2(4) NOT NULL Phone Type
31 HOME_PHONE Character(24) VARCHAR2(24) NOT NULL Home Phone #
32 GPTH_HOME_EXT Character(6) VARCHAR2(6) NOT NULL Phone number extension
33 NATIONAL_ID Character(20) VARCHAR2(20) NOT NULL National ID
34 GPTH_PER_TAX_ID Character(20) VARCHAR2(20) NOT NULL Person Tax ID
35 GPTH_SPO_STATUS Character(3) VARCHAR2(3) NOT NULL Marriy Status
DIE=Died during tax year
DIV=Divorce during tax year
MAR=Married during tax year
NA=Not available
WHO=Married for the whole tax year
36 GPTH_TX_SPO_IND Number(1,0) SMALLINT NOT NULL Claim spouse allowance

Default Value: 0

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

Y/N Table Edit

Default Value: N

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

Y/N Table Edit

Default Value: N

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

Y/N Table Edit

Default Value: N

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

Y/N Table Edit

Default Value: N

41 GPTH_TX_HI_PAFA Character(1) VARCHAR2(1) NOT NULL Declare HI for Payee's Father
42 GPTH_TX_HI_PAMO Character(1) VARCHAR2(1) NOT NULL Declare HI for Payee's Mother
43 GPTH_TX_HI_SPOFA Character(1) VARCHAR2(1) NOT NULL Declare HI for Spouse's Father
44 GPTH_TX_HI_SPOMO Character(1) VARCHAR2(1) NOT NULL Declare HI for Spouse's Mother
45 GPTH_FHOUSE_PRICE Number(12,2) DECIMAL(11,2) NOT NULL First House, house price
46 GPTH_FA_ID Character(13) VARCHAR2(13) NOT NULL Father ID No
47 GPTH_M_ID Character(20) VARCHAR2(20) NOT NULL Mother's National ID
48 GPTH_FI_ID Character(20) VARCHAR2(20) NOT NULL National ID of Father in law
49 GPTH_MI_ID Character(20) VARCHAR2(20) NOT NULL National ID Mother in law
50 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
51 GPTH_SPIN Character(13) VARCHAR2(13) NOT NULL National ID of Spouse
52 GPTH_STIN Character(13) VARCHAR2(13) NOT NULL Tax ID No of Spouse
53 GPTH_SP_BIRTHDATE Date(10) DATE Spouse's Birthdate
54 GPTH_SPREFIX Character(10) VARCHAR2(10) NOT NULL Spouse Name Prefix
55 GPTH_NAME2 Character(80) VARCHAR2(80) NOT NULL Tax-Payee's First Name
56 GPTH_SPO_FIRSTNAME Character(30) VARCHAR2(30) NOT NULL Spouse's First Name
57 GPTH_SPO_LASTNAME Character(30) VARCHAR2(30) NOT NULL Spouse's Last Name
58 PROCESS_INSTANCE Number(10,0) DECIMAL(10) NOT NULL Process Instance
59 OFF_CYCLE Character(1) VARCHAR2(1) NOT NULL Off Cycle ?
60 PRD_BGN_DT Date(10) DATE Period Begin Date
61 PRD_END_DT Date(10) DATE Pay Period End Date
62 SEG_BGN_DT Date(10) DATE Payee Process Segment's Begin Date
63 SEG_END_DT Date(10) DATE Payee Process Segment's End Date
64 PYMT_DT Date(10) DATE Payment Date
65 CAL_PRD_ID Character(10) VARCHAR2(10) NOT NULL Calendar Period ID
66 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country
67 PIN_GROSS_VAL Signed Number(20,6) DECIMAL(18,6) NOT NULL Gross Pay Value
68 PIN_NET_VAL Signed Number(20,6) DECIMAL(18,6) NOT NULL Net Pay Value
69 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
70 CUR_RT_TYPE Character(5) VARCHAR2(5) NOT NULL Currency Rate Type
71 RUN_TYPE Character(10) VARCHAR2(10) NOT NULL Run Type Name
72 PAY_ENTITY Character(10) VARCHAR2(10) NOT NULL Pay Entity
73 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
74 COMPANY Character(3) VARCHAR2(3) NOT NULL Company
75 ESTABID Character(12) VARCHAR2(12) NOT NULL Establishment ID
76 SETID_DEPT Character(5) VARCHAR2(5) NOT NULL Department SetID
77 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
78 SETID_LOCATION Character(5) VARCHAR2(5) NOT NULL Location SetID
79 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code
80 GPTH_GOV Character(1) VARCHAR2(1) NOT NULL This field represents that if the report is an Government report
81 GPTH_SEQ_NUM Character(6) VARCHAR2(6) NOT NULL Sequence number for tax report
82 GPTH_TAX_ID Character(10) VARCHAR2(10) NOT NULL Tax identification number
83 GPTH_TAX_BRANCH Character(4) VARCHAR2(4) NOT NULL Tax branch number
84 GPTH_TAX_ID_L Character(13) VARCHAR2(13) NOT NULL Tax identification number 13
85 GPTH_SI_ER_PER Number(5,2) DECIMAL(4,2) NOT NULL Employer Contribution Rate
86 GPTH_SI_ACCT Character(10) VARCHAR2(10) NOT NULL This field will be used to store company social security account information.
87 GPTH_SI_BRANCH_NO Character(6) VARCHAR2(6) NOT NULL Social Security Branch Number
88 ACM_FROM_DT Date(10) DATE From Date
89 ACM_THRU_DT Date(10) DATE Through Date
90 SLICE_BGN_DT Date(10) DATE Element Slice Begin Date
91 SLICE_END_DT Date(10) DATE Element Slice End Date
92 CALC_RSLT_VAL Signed Number(20,6) DECIMAL(18,6) NOT NULL Calculation Numeric Result
93 BASE_RSLT_VAL Signed Number(20,6) DECIMAL(18,6) NOT NULL Calculated Base Value
94 RATE_RSLT_VAL Signed Number(20,6) DECIMAL(18,6) NOT NULL Rate Result Value
95 UNIT_RSLT_VAL Signed Number(20,6) DECIMAL(18,6) NOT NULL Number of units used in result calculation
96 PCT_RSLT_VAL Signed Number(20,6) DECIMAL(18,6) NOT NULL Percent Result Value
97 RSLT_ADD_ARR Signed Number(20,6) DECIMAL(18,6) NOT NULL Amount Added To Arrears
98 RSLT_NOT_TKN Signed Number(20,6) DECIMAL(18,6) NOT NULL Amount Not Taken
99 RSLT_PAYBK Signed Number(20,6) DECIMAL(18,6) NOT NULL Payback Amount Result
100 RSLT_XFER_VAL Signed Number(20,6) DECIMAL(18,6) NOT NULL Bank Transfer Value of Deduct
101 BASE_ADJ_VAL Signed Number(20,6) DECIMAL(18,6) NOT NULL Base Adjustment
102 CALC_ADJ_VAL Signed Number(20,6) DECIMAL(18,6) NOT NULL Calculation Adjustment Value
103 UNIT_ADJ_VAL Signed Number(20,6) DECIMAL(18,6) NOT NULL Unit Adjustment
104 USER_ADJ_VAL Signed Number(20,6) DECIMAL(18,6) NOT NULL User Adjustment Value
105 USER_KEY1 Character(25) VARCHAR2(25) NOT NULL User Key 1
106 USER_KEY2 Character(25) VARCHAR2(25) NOT NULL User Key 2
107 PIN_TYPE Character(2) VARCHAR2(2) NOT NULL Element Type
108 PIN_NM Character(18) VARCHAR2(18) NOT NULL Element Name field
109 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)
110 PIN_CUSTOM1 Character(20) VARCHAR2(20) NOT NULL Customer FIelds
111 GPTH_TX_PP Character(1) VARCHAR2(1) NOT NULL Payee's party donation
112 GPTH_TX_PP_ID Character(3) VARCHAR2(3) NOT NULL Payee's party donation - Party ID
113 GPTH_TX_SPP Character(1) VARCHAR2(1) NOT NULL Payee's spouse party donation
114 GPTH_TX_SPP_ID Character(3) VARCHAR2(3) NOT NULL Payee's spouse party donation - Party ID
115 DT_OF_DEATH Date(10) DATE Date of Death
116 GPTH_PF_TYPE Character(3) VARCHAR2(3) NOT NULL Thailand Provident Fund Type
CPF=Company Provident Fund
GPF=Goverment Provident Fund
PSF=Private School Teacher PF