EMPLIDEMPL_RCDCAL_RUN_IDGP_PAYGROUPCAL_IDORIG_CAL_RUN_IDRSLT_SEG_NUMHD_360_GPAC_VW(SQL View) |
Index Back |
|---|---|
HRHD 360 GP Rslt AccumulatorSelects the GP accumulator balances tied to a single GP segment, using GP_ELN_TMP to get list of elements to select. 1) Uses same SQL to retrieve absence or payroll accumulators depending upon the bind variable, gp_eln_set. 2) Accumulator's acm_thru_dt must be >= slice_bgn_dt or null (open ended). This is to get rid of previous years results. 3) GROUP BY at the element level, slice level. |
| SELECT R.EMPLID ,R.EMPL_RCD ,R.CAL_RUN_ID ,R.GP_PAYGROUP ,R.CAL_ID ,R.ORIG_CAL_RUN_ID ,R.RSLT_SEG_NUM ,ELN.COUNTRY ,ELN.GP_ELN_SET ,ELN.PROCESS_INSTANCE ,ELN.GP_ORDER ,P.PIN_NUM ,P.PIN_NM ,P.DESCR ,SUM(R.CALC_RSLT_VAL) ,R.SLICE_END_DT ,ELN.GP_ELN_PIN_ATTR1 ,ELN.GP_ELN_PIN_ATTR2 FROM PS_GP_RSLT_ACUM R , PS_GP_PIN P , PS_GP_ELN_TMP ELN WHERE (R.ACM_THRU_DT>=R.SLICE_BGN_DT OR R.ACM_THRU_DT IS NULL) AND P.PIN_NUM=R.PIN_NUM AND ELN.PIN_NUM=R.PIN_NUM GROUP BY R.EMPLID,R.EMPL_RCD,R.CAL_RUN_ID,R.GP_PAYGROUP,R.CAL_ID,R.ORIG_CAL_RUN_ID, R.RSLT_SEG_NUM, ELN.COUNTRY,ELN.GP_ELN_SET, ELN.PROCESS_INSTANCE, ELN.GP_ORDER, P.PIN_NUM,P.PIN_NM, P.DESCR, R.SLICE_END_DT, ELN.GP_ELN_PIN_ATTR1, ELN.GP_ELN_PIN_ATTR2 |
| # | 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 Rcd Nbr |
| 3 | CAL_RUN_ID |
Character(18) | VARCHAR2(18) NOT NULL | Calendar Run Id |
| 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 | COUNTRY | Character(3) | VARCHAR2(3) NOT NULL | Country |
| 9 | GP_ELN_SET | Character(15) | VARCHAR2(15) NOT NULL | Element Set |
| 10 | PROCESS_INSTANCE | Number(10,0) | DECIMAL(10) NOT NULL | Process Instance |
| 11 | GP_ORDER | Number(3,0) | SMALLINT NOT NULL | Utility field for data ordering |
| 12 | PIN_NUM | Number(8,0) | INTEGER NOT NULL | PIN Number |
| 13 | PIN_NM | Character(18) | VARCHAR2(18) NOT NULL | Element Name field |
| 14 | DESCR | Character(30) | VARCHAR2(30) NOT NULL | Description |
| 15 | CALC_RSLT_VAL | Signed Number(20,6) | DECIMAL(18,6) NOT NULL | Calculation Numeric Result |
| 16 | SLICE_END_DT | Date(10) | DATE | Element Slice End Date |
| 17 | GP_ELN_PIN_ATTR1 | Character(20) | VARCHAR2(20) NOT NULL | ELN Attribute |
| 18 | GP_ELN_PIN_ATTR2 | Character(20) | VARCHAR2(20) NOT NULL | ELN Attribute |