RETRO_SUMMRY_VW

(SQL View)
Index Back

Retro Pay Summary View

Retro Pay Summary View

SELECT DISTINCT b.emplid , b.EMPL_RCD , a.mass_retro_rqst_id , b.retropay_seq_no , b.retropay_effdt , a.retropay_rqst_type , b.retropay_erncd , a.retropay_pgm_id , b.retropay_prcs_flag , a.duplicate_flag , SUM(b.amount_diff) FROM ps_retropay_rqst a , PS_RETROPAY_EARNS b WHERE a.emplid = b.emplid AND a.EMPL_RCD = b.EMPL_RCD AND a.retropay_effdt = b.retropay_effdt AND a.retropay_seq_no = b.retropay_seq_no AND b.ok_to_pay = 'Y' AND b.RETROPAY_LOAD_SW = 'Y' GROUP BY b.emplid, b.EMPL_RCD, a.mass_retro_rqst_id, b.retropay_seq_no, b.retropay_effdt, a.retropay_rqst_type, b.retropay_erncd, a.retropay_pgm_id, b.retropay_prcs_flag, a.duplicate_flag

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

Prompt Table: PERSONAL_DTA_VW

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

Prompt Table: JOB

3 MASS_RETRO_RQST_ID Character(5) VARCHAR2(5) NOT NULL Mass Retro Request ID
4 RETROPAY_SEQ_NO Character(15) VARCHAR2(15) NOT NULL Retro Pay Sequence Number
5 RETROPAY_EFFDT Date(10) DATE Retro Pay Effective Date
6 RETROPAY_RQST_TYPE Character(1) VARCHAR2(1) NOT NULL If translate values are added/modified for RETROPAY_RQST_TYPE field, same changes must be done for RETROPAY_RQST_TYP1 field.
A=Additional Pay Request
J=Job Related Request
M=Mass Retro Pay Request
7 RETROPAY_ERNCD Character(3) VARCHAR2(3) NOT NULL Retro Pay Earnings Type
8 RETROPAY_PGM_ID Character(3) VARCHAR2(3) NOT NULL Retro Pay Program ID
9 RETROPAY_PRCS_FLAG Character(1) VARCHAR2(1) NOT NULL If translate values are added/modified for RETROPAY_PRCS_FLAG field, same changes must be done for RETROPAY_PRCS_FLG1 field.
A=Action Required
C=Calculated
F=Confirmed Payment
M=Manually Loaded to Paysheet
N=Not Processed
P=Loaded to Paysheets
R=Recalculate Request
S=Suspended
V=Paycheck Reversed
W=Cancelled / Withdrawn
X=Extracted
10 DUPLICATE_FLAG Character(1) VARCHAR2(1) NOT NULL Duplicate Flag

Y/N Table Edit

Default Value: N

11 AMOUNT_DIFF Signed Number(12,2) DECIMAL(10,2) NOT NULL Amount Difference