TRX_LOCDEBTF_VW (SQL View)	Index Back
Line of Credit Paydowns View of Line of Credit (LOC) principal payments for LOC Deals issued on interest bearing basis.

TRX_LOCDEBTF_VW

(SQL View)

Line of Credit Paydowns

View of Line of Credit (LOC) principal payments for LOC Deals issued on interest bearing basis.

SELECT A.SETID , A.INSTRUMENT_TYPE , B.BUSINESS_UNIT , B.TREAS_HEADER_ID , A.TR_LOC_DEBT_FLG , B.DEAL_STATUS , B.FCLTY_ID , C.TRANSACTION_AMT , C.MATURITY_DT , C.INT_CALCULATION , E.TRX_PAYMENT_TYPE , E.PRINCIPAL_BALANCE , E.INTEREST_DT , E.PRINCIPAL_PAYMENT , E.CURRENCY_CD FROM PS_INSTR_HEADER_TR A , PS_TRX_HEADER_TR B , PS_TRX_DETAIL_TR C , PS_TRX_INTEREST_TR E , PS_SET_CNTRL_REC F WHERE A.TR_LOC_DEBT_FLG = 'Y' AND B.DEAL_STATUS IN ('03', '04', '07', '09', '05') AND C.INT_CALCULATION = 'AR' AND F.SETCNTRLVALUE = B.BUSINESS_UNIT AND F.REC_GROUP_ID = 'TR_01' AND F.RECNAME = 'INSTR_HEADER_TR' AND F.SETID= A.SETID AND B.INSTRUMENT_TYPE = A.INSTRUMENT_TYPE AND B.INSTRMNT_TEMPLATE = A.INSTRMNT_TEMPLATE AND C.BUSINESS_UNIT = B.BUSINESS_UNIT AND C.TREAS_HEADER_ID = B.TREAS_HEADER_ID AND E.TREAS_HEADER_ID = B.TREAS_HEADER_ID AND E.TRX_PAYMENT_TYPE <> 'I' AND C.MATURITY_DT > E.NOMINAL_DT AND B.TREAS_HEADER_ID NOT IN ( SELECT TREAS_HEADER_ID FROM PS_TRX_UNDO_DL_LST) UNION SELECT A.SETID , A.INSTRUMENT_TYPE , B.BUSINESS_UNIT , B.TREAS_HEADER_ID , A.TR_LOC_DEBT_FLG , B.DEAL_STATUS , B.FCLTY_ID , C.TRANSACTION_AMT , C.MATURITY_DT , C.INT_CALCULATION , E.TRX_PAYMENT_TYPE , E.PRINCIPAL_BALANCE , E.INTEREST_DT , E.PRINCIPAL_PAYMENT , E.CURRENCY_CD FROM PS_INSTR_HEADER_TR A , PS_TRX_HEADER_TR B , PS_TRX_DETAIL_TR C , PS_TRX_INTEREST_TR E , PS_SET_CNTRL_REC F , PS_TRX_UNDO_DL_LST D WHERE A.TR_LOC_DEBT_FLG = 'Y' AND B.DEAL_STATUS = '05' AND C.INT_CALCULATION = 'AR' AND F.SETCNTRLVALUE = B.BUSINESS_UNIT AND F.REC_GROUP_ID = 'TR_01' AND F.RECNAME = 'INSTR_HEADER_TR' AND F.SETID = A.SETID AND B.INSTRUMENT_TYPE = A.INSTRUMENT_TYPE AND B.INSTRMNT_TEMPLATE = A.INSTRMNT_TEMPLATE AND C.BUSINESS_UNIT = B.BUSINESS_UNIT AND C.TREAS_HEADER_ID = B.TREAS_HEADER_ID AND E.TREAS_HEADER_ID = B.TREAS_HEADER_ID AND E.TRX_PAYMENT_TYPE <> 'I' AND D.BUSINESS_UNIT = E.BUSINESS_UNIT AND D.TREAS_HEADER_ID = E.TREAS_HEADER_ID AND D.AS_OF_DATE > E.NOMINAL_DT

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 SETID Character(5) VARCHAR2(5) NOT NULL SetID
Default Value: OPR_DEF_TBL_FS.SETID
Prompt Table: SP_SETID_NONVW

2 INSTRUMENT_TYPE Character(10) VARCHAR2(10) NOT NULL Instrument type
Prompt Table: INSTR_INST_H_VW

3 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

4 TREAS_HEADER_ID Character(12) VARCHAR2(12) NOT NULL The unique key identifier for a given deal transaction.

5 TR_LOC_DEBT_FLG Character(1) VARCHAR2(1) NOT NULL Flag to mark instrument as being drawn on Line of Credit facility

6 DEAL_STATUS Character(2) VARCHAR2(2) NOT NULL Represents the specific state or point in the life cycle of a deal transaction.
01=Under Negotiation
02=Sample
03=Open
04=Matured
05=Deactivated
06=Rejected
07=Sold / Bought back
08=Forecasted
09=Partially Sold/Bought Back

7 FCLTY_ID Character(12) VARCHAR2(12) NOT NULL A unique key identifier that represents a given counterparty facility.

8 TRANSACTION_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL The nominal or actual deal transaction amount.

9 MATURITY_DT Date(10) DATE The maturity date for a deal transaction.

10 INT_CALCULATION Character(2) VARCHAR2(2) NOT NULL Interest calculation method for financial instruments.
AR=Interest Bearing
CD=Canadian Discount
DS=Straight Discount
DY=Discount to Yield

11 TRX_PAYMENT_TYPE Character(1) VARCHAR2(1) NOT NULL Decides that the payment is towards the interest on the loan or a part of the principal amount of the loan. For a future the code indicates the margin type.
I=Interest Payment
P=Paydown
R=Drawdown
X=Exchange at Rate Reset

12 PRINCIPAL_BALANCE Signed Number(28,3) DECIMAL(26,3) NOT NULL The notional principal balance of a given deal transaction at commencement.

13 INTEREST_DT Date(10) DATE The business date for an interest period in a deal transaction.

14 PRINCIPAL_PAYMENT Signed Number(28,3) DECIMAL(26,3) NOT NULL Payment that goes towards paying the principal.

15 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	SETID	Character(5)	VARCHAR2(5) NOT NULL	SetID Default Value: OPR_DEF_TBL_FS.SETID Prompt Table: SP_SETID_NONVW
2	INSTRUMENT_TYPE	Character(10)	VARCHAR2(10) NOT NULL	Instrument type Prompt Table: INSTR_INST_H_VW
3	BUSINESS_UNIT	Character(5)	VARCHAR2(5) NOT NULL	Business Unit
4	TREAS_HEADER_ID	Character(12)	VARCHAR2(12) NOT NULL	The unique key identifier for a given deal transaction.
5	TR_LOC_DEBT_FLG	Character(1)	VARCHAR2(1) NOT NULL	Flag to mark instrument as being drawn on Line of Credit facility
6	DEAL_STATUS	Character(2)	VARCHAR2(2) NOT NULL	Represents the specific state or point in the life cycle of a deal transaction. 01=Under Negotiation 02=Sample 03=Open 04=Matured 05=Deactivated 06=Rejected 07=Sold / Bought back 08=Forecasted 09=Partially Sold/Bought Back
7	FCLTY_ID	Character(12)	VARCHAR2(12) NOT NULL	A unique key identifier that represents a given counterparty facility.
8	TRANSACTION_AMT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	The nominal or actual deal transaction amount.
9	MATURITY_DT	Date(10)	DATE	The maturity date for a deal transaction.
10	INT_CALCULATION	Character(2)	VARCHAR2(2) NOT NULL	Interest calculation method for financial instruments. AR=Interest Bearing CD=Canadian Discount DS=Straight Discount DY=Discount to Yield
11	TRX_PAYMENT_TYPE	Character(1)	VARCHAR2(1) NOT NULL	Decides that the payment is towards the interest on the loan or a part of the principal amount of the loan. For a future the code indicates the margin type. I=Interest Payment P=Paydown R=Drawdown X=Exchange at Rate Reset
12	PRINCIPAL_BALANCE	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	The notional principal balance of a given deal transaction at commencement.
13	INTEREST_DT	Date(10)	DATE	The business date for an interest period in a deal transaction.
14	PRINCIPAL_PAYMENT	Signed Number(28,3)	DECIMAL(26,3) NOT NULL	Payment that goes towards paying the principal.
15	CURRENCY_CD	Character(3)	VARCHAR2(3) NOT NULL	Currency Code