TRX_LOCUNDOM_VW

(SQL View)
 Index Back

Line of Credit Maturity Amount

View of Line of Credit (LOC) principal payments for LOC Deals issued on interest bearing basis and subsequently de-activated.

SELECT p.business_unit , p.tr_source_cd , p.tr_source_id , MIN(p.period_end_dt) AS period_end_dt FROM ps_trx_position_tr p , ( SELECT a.source_bus_unit , a.tr_source_cd , a.tr_source_id , b.begin_date , a.end_date FROM ( SELECT source_bus_unit , tr_source_cd , tr_source_id , MIN(business_date) AS end_date FROM PS_CASH_FLOW_TR WHERE cash_type = '04' GROUP BY source_bus_unit, tr_source_cd, tr_source_id ) a, ( SELECT source_bus_unit , tr_source_cd , tr_source_id , MAX(business_date) AS begin_date FROM PS_CASH_FLOW_TR WHERE cash_type <> '04' GROUP BY source_bus_unit, tr_source_cd, tr_source_id ) b WHERE a.source_bus_unit = b.source_bus_unit AND a.tr_source_cd = b.tr_source_cd AND a.tr_source_id = b.tr_source_id) c, ( SELECT business_unit , treas_header_id , nominal_dt , interest_dt FROM ps_trx_interest_tr ) d WHERE p.business_unit = c.source_bus_unit AND p.tr_source_cd = c.tr_source_cd AND p.tr_source_id = c.tr_source_id AND p.business_unit = d.business_unit AND p.tr_source_id = d.treas_header_id AND (d.interest_dt = c.end_date OR d.interest_dt = c.begin_date) AND d.nominal_dt > c.begin_date AND p.period_end_dt <= c.end_date AND p.period_end_dt > c.begin_date GROUP BY p.business_unit, p.tr_source_cd, p.tr_source_id UNION SELECT DISTINCT source_bus_unit , tr_source_cd , tr_source_id , MIN(business_date) FROM PS_CASH_FLOW_TR A WHERE CASH_TYPE = '04' AND NOT EXISTS ( SELECT 'X' FROM PS_CASH_FLOW_TR B WHERE B.SOURCE_BUS_UNIT = A.SOURCE_BUS_UNIT AND B.TR_SOURCE_CD = A.TR_SOURCE_CD AND B.TR_SOURCE_ID = A.TR_SOURCE_ID AND CASH_TYPE <> '04' ) GROUP BY source_bus_unit, tr_source_cd, tr_source_id UNION SELECT DISTINCT source_bus_unit , tr_source_cd , tr_source_id , MAX(business_date) FROM PS_CASH_FLOW_TR A WHERE CASH_TYPE <> '04' AND NOT EXISTS ( SELECT 'X' FROM PS_CASH_FLOW_TR B WHERE B.SOURCE_BUS_UNIT = A.SOURCE_BUS_UNIT AND B.TR_SOURCE_CD = A.TR_SOURCE_CD AND B.TR_SOURCE_ID = A.TR_SOURCE_ID AND CASH_TYPE = '04' ) GROUP BY source_bus_unit, tr_source_cd, tr_source_id

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
2 TR_SOURCE_CD Character(1) VARCHAR2(1) NOT NULL Note: Changes to Translate Values must also be made to TRA_SOURCE_CD and SOURCE_TYPE. These 3 fields must be kept in sync. Values that are inactive are for use in TR Accounting only
B=BSP
D=Deals
E=Deal Fees
F=Facility Fees
H=Hedges
I=Interest
M=Deal: Estimated Maturity Accr
N=Netted Deal
O=Other
P=Investment Pools
R=EFT Fees
S=Securities
T=LC Fees
W=EFT Requests
X=Bank Transfers
3 TREAS_HEADER_ID Character(12) VARCHAR2(12) NOT NULL The unique key identifier for a given deal transaction.
4 PERIOD_END_DT Date(10) DATE Represents the date termination point for a given time set utilized in cash worksheet processing.