FCST_DEALS_VW

(SQL View)
 Index Back

Forecast Deal amounts

Forecast Deal amounts

SELECT H.BUSINESS_UNIT , H.TREAS_HEADER_ID , H.TREAS_MASTER_ID , C.CASH_FLOW_LEG , C.TRANSACTION_LINE , C.BUSINESS_DATE , C.BANK_ACCOUNT_NUM , C.BNK_ID_NBR , H.COUNTERPARTY , B.BANK_CD , Y.COUNTRY , C.AMOUNT , C.CURRENCY_CD , H.INSTRUMENT_TYPE , D.INSTRMNT_BASE_TYPE , H.TRANSACTION_ISSUER , H.TRANSACTION_BROKER , H.DEALER_OPRID , H.TRANSACTION_DT , H.TREASURY_PORTFOLIO , B.BANK_TYPE , H.LIMIT_RESERVED , H.DEAL_STATUS , %DateDiff( D.SETTLEMENT_DT, D.MATURITY_DT) , %DateDiff(D.ISSUE_DT, D.MATURITY_DT) , D.SETTLEMENT_DT , D.MATURITY_DT , H.FCLTY_ID , C.BANK_SETID , C.BANK_ACCT_KEY , C.SEQ_NBR , E.CALENDAR_DATE FROM PS_CASH_FLOW_TR C , PS_BANK_CD_TBL B , PS_CURRENCY_CD_TBL Y , PS_TRX_DETAIL_TR D , PS_TRX_HEADER_TR H , PS_POS_DAYS_TBL E WHERE C.TR_SOURCE_CD = 'D' AND C.CASH_FLOW_LINE <> 0 AND C.RECON_STATUS = 'UNR' AND C.NET_STATUS <> 'D' AND B.SETID = C.BANK_SETID AND B.BANK_CD = C.BANK_CD AND C.TR_SOURCE_ID = H.TREAS_HEADER_ID AND C.SOURCE_BUS_UNIT = H.BUSINESS_UNIT AND C.SOURCE_BUS_UNIT = D.BUSINESS_UNIT AND C.TR_SOURCE_ID = D.TREAS_HEADER_ID AND C.TRANSACTION_LINE = D.TRANSACTION_LINE AND Y.CURRENCY_CD = C.CURRENCY_CD AND y.effdt = ( SELECT MAX(effdt) FROM ps_currency_cd_tbl WHERE currency_cd = y.currency_cd AND effdt <= %CurrentDateIn ) AND Y.eff_status = 'A' AND E.CALENDAR_DATE = %CurrentDateIn

  • Parent record: TRX_DETAIL_TR

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
    2 TREAS_HEADER_ID Character(12) VARCHAR2(12) NOT NULL The unique key identifier for a given deal transaction.
    3 TREAS_MASTER_ID Character(12) VARCHAR2(12) NOT NULL Master ID for a given deal header.
    4 TRANSACTION_LEG Number(1,0) SMALLINT NOT NULL The separate and distinct business positions of a given deal transaction line.
    1=Pay Leg
    2=Receive Leg
    5 TRANSACTION_LINE Number(3,0) SMALLINT NOT NULL The separate and distinct base instrument type components of a given deal transaction.
    6 BUSINESS_DATE Date(10) DATE Represents a date upon which business is conducted within a given company in a given country.
    7 BANK_ACCOUNT_NUM Character(35) VARCHAR2(35) NOT NULL The literal unique identifier associated with a given bank/counterparty account.
    8 BNK_ID_NBR Character(20) VARCHAR2(20) NOT NULL A literal alphanumeric attribute that uniquely identifies a given bank or counterparty.
    9 COUNTERPARTY Character(5) VARCHAR2(5) NOT NULL Represents a treasury dealing counterpart.
    10 BANK_CD Character(5) VARCHAR2(5) NOT NULL Bank Code
    11 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country
    12 AMOUNT Signed Number(28,3) DECIMAL(26,3) NOT NULL Amount
    13 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL Currency Code
    14 INSTRUMENT_TYPE Character(10) VARCHAR2(10) NOT NULL Instrument type
    15 INSTRMNT_BASE_TYPE Character(2) VARCHAR2(2) NOT NULL Instrument base type used as a building block for Treasury deals
    01=Interest Rate Physical
    02=Interest Rate Swap
    03=FX Deal Physical
    04=Option
    05=Option - Binary Payoff
    06=Futures Contract
    07=Commodity
    08=Generic Instrument
    09=Equity
    16 TRANSACTION_ISSUER Character(5) VARCHAR2(5) NOT NULL The entity or organization that sponsors the issuance of a given financial instrument on behalf of the tre
    17 TRANSACTION_BROKER Character(5) VARCHAR2(5) NOT NULL "The deal transaction intermediary
    18 DEALER_OPRID Character(30) VARCHAR2(30) NOT NULL A system generated value that reflects the operator that originally created and saved a given deal tran
    19 TRANSACTION_DT Date(10) DATE Transaction date (often used as trade date)
    20 TREASURY_PORTFOLIO Character(15) VARCHAR2(15) NOT NULL A unique key identifier for a position portfolio that may be associated with a given deal transaction.
    21 BANK_TYPE Character(1) VARCHAR2(1) NOT NULL "An attribute that determines whether a bank or counterparty is defined as being internal or external
    E=External
    I=Internal
    N=Netting
    O=Origin
    22 LIMIT_RESERVED Character(1) VARCHAR2(1) NOT NULL An internal workfield that indicates whether a portion of a given treasury position's available exposu
    N=UnReserved
    Y=Limit Reserved
    23 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
    24 TR_TERM Signed Number(6,0) DECIMAL(5) NOT NULL "Negotiated length of a deal transaction; also
    25 TR_ORIGINAL_TERM Signed Number(5,0) DECIMAL(4) NOT NULL Initial stated term for a deal transaction.
    26 SETTLEMENT_DT Date(10) DATE The settlement date for a given cash -based transaction, or the start date for a Treasury Deal.
    27 MATURITY_DT Date(10) DATE The maturity date for a deal transaction.
    28 FCLTY_ID Character(12) VARCHAR2(12) NOT NULL A unique key identifier that represents a given counterparty facility.
    29 BANK_SETID Character(5) VARCHAR2(5) NOT NULL The PeopleSoft tableset ID associated with a given bank/counterparty.
    30 BANK_ACCT_KEY Character(4) VARCHAR2(4) NOT NULL A user defined unique identifier that facilitates the identification of a given account with a given bank
    31 SEQ_NBR Number(15,0) DECIMAL(15) NOT NULL Sequence Number
    32 CALENDAR_DATE Date(10) DATE An internal work field utilized to store calendar dates for a given calendar year. The dates are displa