GPGB_CTGRYID_VW

(SQL View)
 Index Back

GPUK Payslip View

GPUK Payslip View

SELECT c.emplid , c.empl_rcd , c.prd_bgn_dt , c.prd_end_dt , c.pymt_dt , a.gp_catgry_type , CASE WHEN a.gp_catgry_type = 'EAR' THEN 'Earnings' WHEN a.gp_catgry_type = 'DED' THEN 'Deductions' WHEN a.gp_catgry_type = 'ACM' THEN 'Acumulators' END AS ele , a.gp_category_id , d.descr , a.country , a.gp_element_type , b.pin_nm , CASE WHEN a.descr = ' ' THEN CASE WHEN b.pin_nm ='LOA DD LOAN' THEN %Coalesce( (SELECT descr FROM ps_gpgb_loan_type WHERE gpgb_loan_type=c.user_fld2 ) , ( SELECT descr FROM ps_gp_pin WHERE pin_num = b.pin_num) ) WHEN b.pin_nm = 'COURT ORDER' THEN CASE WHEN ( SELECT gpgb_co_leg FROM ps_gpgb_ee_co_dtl WHERE emplid = c.emplid AND gpgb_co_ord_ref = c.user_fld1 ) = 'EW' THEN ( SELECT descr FROM ps_gpgb_co_ew_tbl WHERE gpgb_co_order = c.user_fld2 ) WHEN ( SELECT gpgb_co_leg FROM ps_gpgb_ee_co_dtl WHERE emplid = c.emplid AND gpgb_co_ord_ref = c.user_fld1 ) = 'NI' THEN ( SELECT descr FROM ps_gpgb_co_ni_tbl WHERE gpgb_co_order = c.user_fld2 ) WHEN ( SELECT gpgb_co_leg FROM ps_gpgb_ee_co_dtl WHERE emplid = c.emplid AND gpgb_co_ord_ref = c.user_fld1 ) = 'SC' THEN ( SELECT descr FROM ps_gpgb_co_sc_tbl WHERE gpgb_co_order = c.user_fld2 ) ELSE ( SELECT descr FROM ps_gp_pin WHERE pin_num = b.pin_num) END ELSE b.descr END ELSE a.descr END AS description, c.cal_run_id, c.gp_paygroup, c.run_type, c.calc_rslt_val, c.unit_rslt_val, c.user_fld1, c.user_fld2, CASE WHEN c.unit_rslt_val > 0 THEN CASE WHEN a.descr = ' ' THEN b.descr ELSE a.descr END || ' Units' ELSE '' END AS description ,e.JOBCODE_DESCR FROM ps_gp_cat_code_tbl a, ps_gp_pin b, ps_gp_mpslp_erdd c, PS_GP_CTGYSEL_TBL d ,PS_JOB_CURR_VW1 E WHERE a.pin_num = b.pin_num AND c.pin_num = a.pin_num AND a.gp_catgry_type = d.gp_catgry_type AND a.gp_category_id = d.gp_category_id AND a.country = d.country AND c.emplid=e.emplid AND c.empl_rcd=e.empl_rcd AND c.calc_rslt_Val > 0 AND d.gp_catid_status='A'

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
2 EMPL_RCD Number(3,0) SMALLINT NOT NULL Empl Record
3 PRD_BGN_DT Date(10) DATE Period Begin Date
4 PRD_END_DT Date(10) DATE Pay Period End Date
5 PYMT_DT Date(10) DATE Payment Date
6 GP_CATGRY_TYPE Character(3) VARCHAR2(3) NOT NULL Category type for Chatbot category setup
ACC=Accumulator
DED=Deductions
EAR=Earnings
7 DESCR Character(30) VARCHAR2(30) NOT NULL Description
8 GP_CATEGORY_ID Character(3) VARCHAR2(3) NOT NULL Category ID for Chatbot category setup
9 DESCR1 Character(30) VARCHAR2(30) NOT NULL Descr
10 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country
11 GP_ELEMENT_TYPE Character(4) VARCHAR2(4) NOT NULL Element Type
AC=Accumulator
DD=Deductions
ER=Earnings
12 PIN_NM Character(18) VARCHAR2(18) NOT NULL Element Name field
13 DESCR2 Character(30) VARCHAR2(30) NOT NULL Descr2
14 CAL_RUN_ID Character(18) VARCHAR2(18) NOT NULL Calendar Run Id
15 GP_PAYGROUP Character(10) VARCHAR2(10) NOT NULL Global Payroll pay group
16 RUN_TYPE Character(10) VARCHAR2(10) NOT NULL Run Type Name
17 CALC_RSLT_VAL Signed Number(20,6) DECIMAL(18,6) NOT NULL Calculation Numeric Result
18 UNIT_RSLT_VAL Signed Number(20,6) DECIMAL(18,6) NOT NULL Number of units used in result calculation
19 USER_FLD1 Character(25) VARCHAR2(25) NOT NULL User Field 1
20 USER_FLD2 Character(25) VARCHAR2(25) NOT NULL User Field 2
21 DESCR35_A Character(35) VARCHAR2(35) NOT NULL Job Offer
22 JOBTITLE Character(30) VARCHAR2(30) NOT NULL Job Title