GPES_ADLNDED_VW

(SQL View)
 Index Back

Adv/Loan pending repayments

Retrieves all repayments that have not been already deducted.

SELECT A.EMPLID , B.SEQNUM , B.GPES_YEAR , B.GPES_MONTH , B.GPES_TAKE_XTRPD , A.GPES_ADVLOAN_OPT , A.GPES_ADLN_AMT , A.CURRENCY_CD , B.GPES_DED_AMT , B.GPES_DED_AMT_INT , B.GPES_KIND_AMT , %Coalesce(X.GPES_DED_AMT, 0) FROM PS_GPES_ADV_LOAN A , PS_GPES_ADLN_SCHED B LEFT OUTER JOIN PS_GPES_LN_RSLT_VW X ON X.EMPLID = B.EMPLID AND X.SEQNUM = B.SEQNUM AND X.GPES_YEAR = B.GPES_YEAR AND X.GPES_MONTH = B.GPES_MONTH AND X.GPES_TAKE_XTRPD = B.GPES_TAKE_XTRPD WHERE A.EMPLID = B.EMPLID AND A.SEQNUM = B.SEQNUM

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

Prompt Table: PERSON

2 SEQNUM Number(3,0) SMALLINT NOT NULL Sequence Number
3 GPES_YEAR Number(4,0) SMALLINT NOT NULL Year
4 GPES_MONTH Number(2,0) SMALLINT NOT NULL Month
5 GPES_TAKE_XTRPD Character(1) VARCHAR2(1) NOT NULL Advance/Loan repayment at extra periods option

Y/N Table Edit

Default Value: N

6 GPES_ADVLOAN_OPT Character(1) VARCHAR2(1) NOT NULL Advance/Loan option
1=Advance
4=Loan

Default Value: 1

7 GPES_ADLN_AMT Number(11,2) DECIMAL(10,2) NOT NULL Advance/loan amount
8 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
9 GPES_DED_AMT Number(11,2) DECIMAL(10,2) NOT NULL Advance/Loan repayment deduction amount
10 GPES_DED_AMT_INT Number(9,2) DECIMAL(8,2) NOT NULL Advance/Loan interest deduction amount
11 GPES_KIND_AMT Number(9,2) DECIMAL(8,2) NOT NULL Inkind Amount.
12 GPES_REPAID_AMT Number(10,2) DECIMAL(9,2) NOT NULL Advance/Loan repaid amount