GPGB_P11D_LN_VW

(SQL View)
 Index Back

P11D Employee Loan View

Selects only "P11D Reportable" rows from GPGB_LOAN_WA

SELECT A.EMPLID ,A.EMPL_RCD ,A.GPGB_LOAN_ID ,A.GPGB_TAX_END_YR ,A.PYMT_DT ,A.GPGB_AMT_BAL FROM PS_GPGB_LOAN_WA A WHERE EXISTS ( SELECT B.GPGB_P11D_REPORT FROM PS_GPGB_EE_LOAN_IN B WHERE B.EMPLID = A.EMPLID AND B.EMPL_RCD = A.EMPL_RCD AND B.GPGB_LOAN_ID = A.GPGB_LOAN_ID AND B.GPGB_P11D_REPORT = 'Y') OR (NOT EXISTS ( SELECT C.GPGB_P11D_REPORT FROM PS_GPGB_EE_LOAN_IN C WHERE C.EMPLID = A.EMPLID AND C.EMPL_RCD = A.EMPL_RCD AND C.GPGB_LOAN_ID = A.GPGB_LOAN_ID AND C.GPGB_P11D_REPORT = 'Y') AND EXISTS ( SELECT D.GPGB_P11D_REPORT FROM PS_GPGB_EE_LOAN_DT D WHERE D.EMPLID = A.EMPLID AND D.EMPL_RCD = A.EMPL_RCD AND D.GPGB_LOAN_ID = A.GPGB_LOAN_ID AND D.GPGB_P11D_REPORT = 'Y' AND D.EFFDT = ( SELECT MAX(E.EFFDT) FROM PS_GPGB_EE_LOAN_DT E WHERE E.EMPLID = A.EMPLID AND E.EMPL_RCD = A.EMPL_RCD AND E.GPGB_LOAN_ID = A.GPGB_LOAN_ID AND E.EFFDT <= A.PYMT_DT)))

# 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 GPGB_LOAN_ID Character(20) VARCHAR2(20) NOT NULL Unique ID for employee loans
4 GPGB_TAX_END_YR Number(4,0) SMALLINT NOT NULL Tax Year Ends In This Calendar Year
5 PYMT_DT Date(10) DATE Payment Date
6 GPGB_AMT_BAL Signed Number(20,6) DECIMAL(18,6) NOT NULL GP UK - Balance Amount