GPDE_LN_BAL_VW

(SQL View)
 Index Back

Ger Loans Balance View

Balance view for German loans

SELECT A.EMPLID , A.EMPL_RCD , A.INSTANCE , SUM(A.GPDE_LN_DED_AMT) FROM PS_GPDE_LN_SCHED A , PS_GPDE_LN_SETUP B WHERE A.EMPLID = B.EMPLID AND A.EMPL_RCD = B.EMPL_RCD AND A.INSTANCE = B.INSTANCE AND NOT EXISTS ( SELECT 1 FROM PS_GPDE_LN_RSLT SUB WHERE SUB.EMPLID = A.EMPLID AND SUB.EMPL_RCD = A.EMPL_RCD AND SUB.INSTANCE = A.INSTANCE AND SUB.GPDE_LN_YEAR = A.GPDE_LN_YEAR AND SUB.GPDE_LN_MONTH = A.GPDE_LN_MONTH AND SUB.GPDE_AL_CPAY_ENDDT = ( SELECT MAX(sub2.GPDE_AL_CPAY_ENDDT) FROM PS_GPDE_LN_RSLT sub2 WHERE sub2.EMPLID = A.EMPLID AND sub2.EMPL_RCD = A.EMPL_RCD AND sub2.GPDE_LN_YEAR = A.GPDE_LN_YEAR AND sub2.GPDE_LN_MONTH = A.GPDE_LN_MONTH ) AND SUB.GPDE_LN_ACTIVE = 'Y') GROUP BY A.EMPLID, A.EMPL_RCD, A.INSTANCE

# 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 INSTANCE Number(3,0) SMALLINT NOT NULL Instance Number
4 GPDE_LN_REM_BAL Number(11,2) DECIMAL(10,2) NOT NULL Loans Remaining Balance. Used to keep track of loan balances for reporting and/or to figure out whether income tax/si has to be/was deducted for this loan instance