GPDE_RP_0001VW1

(SQL View)
 Index Back

WA Main Reporting Record

WA Record : Main Reporting Rec

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.SEG_END_DT ,A.SEG_BGN_DT ,A.PRD_END_DT ,A.PRD_BGN_DT ,A.PAY_ENTITY ,A.COMPANY ,A.LOCATION ,A.DEPTID ,A.ACTION ,A.REASON ,A.SAL_ADMIN_PLAN ,A.GRADE ,A.STEP ,A.STD_HOURS ,A.EMPL_STATUS ,A.EMPL_CTG ,A.EMPL_CTG_L1 ,A.PERFORM_GROUP_GER ,A.JOB_ENTRY_DT ,A.HIRE_DT ,A.TERMINATION_DT ,A.BIRTHDATE ,A.MAR_STATUS ,A.SEX ,A.GPDE_AL_CPAY_ENDDT ,A.GPDE_TX_PAY_EFFDT ,A.GPDE_TX_SPCL_EXMPT ,A.GPDE_TX_INC_TX_CLS ,A.GPDE_TX_SCHEDULE ,A.GPDE_TX_NUM_CHILD ,A.GPDE_TX_EXEMPT_ANN ,A.GPDE_TX_EXEMPT_MON ,A.GPDE_TX_ADDGRS_ANN ,A.GPDE_TX_ADDGRS_MON ,A.GPDE_TX_RELIGION ,A.GPDE_TX_SPOUSE_REL ,A.GPDE_TX_CHRCHSTATE ,A.GPDE_TX_CHAMBSTATE ,A.GPDE_TX_ACCM_TRANS ,A.GPDE_TX_NOPRV_EMPL ,A.GPDE_TX_RECALC_OVD ,A.GPDE_TX_METHOD_OVD ,A.GPDE_TX_CHRCH_OVRD ,A.GPDE_KU_BEN_GRP ,A.GPDE_KU_BEN_STP ,A.GPDE_TX_UNIT ,A.GPDE_TX_CHAMBER_BR ,A.GPDE_TX_CHAMBER_SA ,A.CURRENCY_CD ,A.CUR_RT_TYPE ,A.GPDE_ASOF_DT_EX_RT ,A.FTE ,A.GPDE_TX_HOME_CD ,A.GPDE_TX_AGS_ID ,A.GPDE_AL_FINANCE_CD ,A.NATIONALITY_CD ,A.NATIONALITY ,A.SETID_DEPT ,A.SETID_LOCATION ,A.WC_COUNCIL_ID ,A.GPDE_TX_VBEZ_DT ,A.GPDE_TX_VBEZM ,A.GPDE_TX_VBEZS ,A.GPDE_TX_VBEZB ,A.GPDE_TX_ZMVB ,B.RSLT_VER_NUM FROM PS_GPDE_RP_0001 A , PS_GP_PYE_SEG_STAT B WHERE B.EMPLID=A.EMPLID AND B.CAL_RUN_ID=A.CAL_RUN_ID AND B.EMPL_RCD=A.EMPL_RCD AND B.GP_PAYGROUP=A.GP_PAYGROUP AND B.CAL_ID=A.CAL_ID AND B.ORIG_CAL_RUN_ID=A.ORIG_CAL_RUN_ID AND B.RSLT_SEG_NUM=A.RSLT_SEG_NUM AND B.RSLT_SEG_NUM=( SELECT MAX(S2.RSLT_SEG_NUM) FROM PS_GP_PYE_SEG_STAT S2 WHERE S2.EMPLID=B.EMPLID AND S2.EMPL_RCD=B.EMPL_RCD AND S2.CAL_ID=B.CAL_ID AND S2.SEG_END_DT=B.SEG_END_DT) AND B.RSLT_VER_NUM=( SELECT MAX(S3.RSLT_VER_NUM) FROM PS_GP_PYE_SEG_STAT S3 WHERE S3.EMPLID=B.EMPLID AND S3.EMPL_RCD=B.EMPL_RCD AND S3.CAL_ID=B.CAL_ID AND S3.RSLT_SEG_NUM=B.RSLT_SEG_NUM)

  • Parent record: PERSON

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

    Prompt Table: PERSON

    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 SEG_END_DT Date(10) DATE Payee Process Segment's End Date
    9 SEG_BGN_DT Date(10) DATE Payee Process Segment's Begin Date
    10 PRD_END_DT Date(10) DATE Pay Period End Date
    11 PRD_BGN_DT Date(10) DATE Period Begin Date
    12 PAY_ENTITY Character(10) VARCHAR2(10) NOT NULL Pay Entity
    13 COMPANY Character(3) VARCHAR2(3) NOT NULL Company
    14 LOCATION Character(10) VARCHAR2(10) NOT NULL Location Code
    15 DEPTID Character(10) VARCHAR2(10) NOT NULL Department
    16 ACTION Character(3) VARCHAR2(3) NOT NULL Action

    Prompt Table: ACTION_TBL

    17 REASON Character(30) VARCHAR2(30) NOT NULL Reason
    18 SAL_ADMIN_PLAN Character(4) VARCHAR2(4) NOT NULL Salary Administration Plan
    19 GRADE Character(3) VARCHAR2(3) NOT NULL Salary Grade
    20 STEP Number(2,0) SMALLINT NOT NULL Step
    21 STD_HOURS Number(7,2) DECIMAL(6,2) NOT NULL Standard Hours
    22 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
    23 EMPL_CTG Character(6) VARCHAR2(6) NOT NULL Employee Category
    24 EMPL_CTG_L1 Character(6) VARCHAR2(6) NOT NULL Employee Subcategory
    25 PERFORM_GROUP_GER Character(2) VARCHAR2(2) NOT NULL This field is used for German employees to track the Performance Group associated to the Job Labor agreement.
    A1=Blue Collar Performance Grp. 1
    A2=Blue Collar Performance Grp. 2
    A3=Blue Collar Performance Grp. 3
    K1=Commercial White Collar Grp. 1
    K2=Commercial White Collar Grp. 2
    K3=Commercial White Collar Grp. 3
    K4=Commercial White Collar Grp. 4
    K5=Commerical White Collar Grp. 5
    T1=Technical White Collar Grp. 1
    T2=Technical White Collar Grp. 2
    T3=Technical White Collar Grp. 3
    T4=Technical White Collar Grp. 4
    T5=Technical White Collar Grp. 5
    26 JOB_ENTRY_DT Date(10) DATE Job Entry Date
    27 HIRE_DT Date(10) DATE First Start Date
    28 TERMINATION_DT Date(10) DATE Termination Date
    29 BIRTHDATE Date(10) DATE Date of Birth
    30 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
    31 SEX Character(1) VARCHAR2(1) NOT NULL Gender
    F=Female
    M=Male
    U=Unknown
    32 GPDE_AL_CPAY_ENDDT Date(10) DATE Current Pay End Date
    33 GPDE_TX_PAY_EFFDT Date(10) DATE Pay Date
    34 GPDE_TX_SPCL_EXMPT Character(1) VARCHAR2(1) NOT NULL Tax Status
    1=Unlimited Tax Liability
    2=Short-Term Employment
    3=Casual Worker
    4=Non Taxable
    5=Swiss Border Worker
    6=Limited Tax Liability
    7=Unlimited Tax Liability (DBA)
    8=Unlimitied Tax Liability (ATE)
    9=Insig Emplymnt Lmpsm 2% ER
    9=Insig Emplymnt Lmpsm 2% ER
    A=Insig Emplymnt Lmpsm 2% EE
    A=Insig Emplymnt Lmpsm 2% EE
    B=Border Worker Belgium
    G=Border Worker (non Taxable)
    35 GPDE_TX_INC_TX_CLS Character(2) VARCHAR2(2) NOT NULL Tax Class
    0=None
    1=Tax Class I
    2=Tax Class II
    3=Tax Class III
    4=Tax Class IV
    5=Tax Class V
    6=Tax Class VI
    36 GPDE_TX_SCHEDULE Character(1) VARCHAR2(1) NOT NULL Tax Schedule
    A=Tax Schedule A
    B=Tax Schedule B
    C=Tax Schedule B(on Application)
    37 GPDE_TX_NUM_CHILD Number(4,1) DECIMAL(3,1) NOT NULL Number of Children
    38 GPDE_TX_EXEMPT_ANN Signed Number(14,2) DECIMAL(12,2) NOT NULL Exemption Annually
    39 GPDE_TX_EXEMPT_MON Signed Number(14,2) DECIMAL(12,2) NOT NULL Exemption Monthly
    40 GPDE_TX_ADDGRS_ANN Number(13,2) DECIMAL(12,2) NOT NULL Additional Taxgross for the Employee from another Employment for Bonus and Adjustment Taxcalculation
    41 GPDE_TX_ADDGRS_MON Number(13,2) DECIMAL(12,2) NOT NULL Additional Taxgross for the Employee from another Employment for Monthly Tax Calculation
    42 GPDE_TX_RELIGION Character(3) VARCHAR2(3) NOT NULL Religion
    00=No confession
    01=Roman Catholic
    02=Old Catholic
    03=Evangical Lutheran
    04=Evangical Ref.
    05=French Reform i.0.
    06=Free Religion Baden
    07=Israelite
    08=Religion Tax Rheinland Pfalz
    09=Free Religion Offenbach
    10=Free Religion Mainz
    11=Israelite Religion Baden
    12=Israelite Religion Wurttemberg
    13=Israelite Religion Frankfurt/M
    14=Israelite Religion Country
    15=Free Religion Pfalz
    16=Free Religion Comunity Alzey
    17=Jewish Culture Tax HH
    18=Jewish Culture Tax SH
    43 GPDE_TX_SPOUSE_REL Character(3) VARCHAR2(3) NOT NULL Religion Spouse
    00=No confession
    01=Roman Catholic
    02=Old Catholic
    03=Evangical Lutheran
    04=Evangical Ref
    05=French Reform i.O.
    06=Free Religion Baden
    07=Israelite
    08=Religion Tax Rheinland Pfalz
    09=Free Religion Offenbach
    10=Free Religion Mainz
    11=Israelite Religion Baden
    12=Israelite Religion Wurttemberg
    13=Israelite Religion Frankfurt/M
    14=Israelite Religion Country
    15=Free Religion Pfalz
    16=Free Religion Comunity Alzey
    17=Jewish Culture Tax HH
    18=Jewish Culture Tax SH
    44 GPDE_TX_CHRCHSTATE Character(2) VARCHAR2(2) NOT NULL Church State
    01=Schleswig-Holstein
    02=Hamburg
    03=Niedersachsen 9 %
    04=Bremen
    05=Nordrhein-Westfalen
    06=Hessen
    07=Rheinland-Pfalz
    08=Baden-Wurtemberg
    09=Bayern
    10=Saarland
    11=Berlin
    12=Brandenburg
    13=Mecklenburg-Vorpommern
    14=Sachsen
    15=Sachsen-Anhalt
    16=Thuringen
    17=Stadt Bremerhaven
    18=Oberfinanzdirektion Kiel
    93=Niedersachsen 8 %
    45 GPDE_TX_CHAMBSTATE Character(2) VARCHAR2(2) NOT NULL Chamber State
    00=none
    01=Bremen
    02=Saarland
    03=Bremen BC
    46 GPDE_TX_ACCM_TRANS Character(1) VARCHAR2(1) NOT NULL Tax Card Received
    47 GPDE_TX_NOPRV_EMPL Character(1) VARCHAR2(1) NOT NULL Tax Card Received
    48 GPDE_TX_RECALC_OVD Character(1) VARCHAR2(1) NOT NULL Annual Adjustment
    49 GPDE_TX_METHOD_OVD Character(1) VARCHAR2(1) NOT NULL Monthly Adjustment
    50 GPDE_TX_CHRCH_OVRD Character(1) VARCHAR2(1) NOT NULL Church Annual
    51 GPDE_KU_BEN_GRP Character(1) VARCHAR2(1) NOT NULL KUG Benefit Group
    A=Tax Class I and IV
    B=Tax Class II
    C=Tax Class III
    D=Tax Class V
    E=Tax Class VI
    52 GPDE_KU_BEN_STP Character(1) VARCHAR2(1) NOT NULL KUG Benefit Step
    1=With Children
    2=Without Children
    53 GPDE_TX_UNIT Character(6) VARCHAR2(6) NOT NULL Tax Unit

    Prompt Table: GPDE_TX_UNIT

    54 GPDE_TX_CHAMBER_BR Character(6) VARCHAR2(6) NOT NULL Tax Chamber Bremen

    Prompt Table: GPDE_TX_UNIT

    55 GPDE_TX_CHAMBER_SA Character(6) VARCHAR2(6) NOT NULL Tax Chamber Saarland

    Prompt Table: GPDE_TX_UNIT

    56 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
    57 CUR_RT_TYPE Character(5) VARCHAR2(5) NOT NULL Currency Rate Type
    58 GPDE_ASOF_DT_EX_RT Date(10) DATE Exchange Rate Date
    59 FTE Number(8,6) DECIMAL(7,6) NOT NULL This field represent Full Time Equivalence
    60 GPDE_TX_HOME_CD Character(30) VARCHAR2(30) NOT NULL Locality
    61 GPDE_TX_AGS_ID Character(8) VARCHAR2(8) NOT NULL Municipality Code for GP Germany.
    62 GPDE_AL_FINANCE_CD Character(4) VARCHAR2(4) NOT NULL Tax Office

    Prompt Table: GPDE_TX_FINOFC

    63 NATIONALITY_CD Character(3) VARCHAR2(3) NOT NULL Nationality Code
    64 NATIONALITY Character(30) VARCHAR2(30) NOT NULL Nationality
    65 SETID_DEPT Character(5) VARCHAR2(5) NOT NULL Department Set ID
    66 SETID_LOCATION Character(5) VARCHAR2(5) NOT NULL Location Set ID
    67 WC_COUNCIL_ID Character(9) VARCHAR2(9) NOT NULL Works Council ID
    68 GPDE_TX_VBEZ_DT Date(10) DATE Begin Date
    69 GPDE_TX_VBEZM Signed Number(14,2) DECIMAL(12,2) NOT NULL Exemption Monthly
    70 GPDE_TX_VBEZS Signed Number(14,2) DECIMAL(12,2) NOT NULL Exemption Monthly
    71 GPDE_TX_VBEZB Signed Number(14,2) DECIMAL(12,2) NOT NULL Exemption Monthly
    72 GPDE_TX_ZMVB Number(2,0) SMALLINT NOT NULL Exemption Monthly
    73 RSLT_VER_NUM Number(4,0) SMALLINT NOT NULL Result Version Number