TRX_POSITION_VW

(SQL View)
 Index Back

TR Deal Position Monitor View

Position change due to Treasury Deal. This view used by the Position Monitor / Manager

SELECT D.BUSINESS_UNIT , P.TR_SOURCE_ID , P.TRANSACTION_LEG , D.TRANSACTION_LINE , T.CALENDAR_DATE , H.TREAS_MASTER_ID , P.PD_INT_START_DT , P.POSITION_CURRENCY , P.PRINCIPAL_BALANCE , P.PERIOD_INT_ACCRUAL , P.START_INT_ACCRUAL , P.SPOT_RATE_MULT , P.SPOT_RATE_DIV , P.FORWARD_RATE_MULT , P.FORWARD_RATE_DIV , P.RESET_RATE_SET , P.TRANSACTION_RATE , P.RATE_RESET_DT , H.INSTRUMENT_TYPE , D.INSTRMNT_BASE_TYPE , P.ASSET_LIABILITY , H.COUNTERPARTY , B.BANK_CD , H.TRANSACTION_ISSUER , H.TRANSACTION_BROKER , H.DEALER_OPRID , H.TRANSACTION_DT , H.TREASURY_PORTFOLIO , H.CPTY_DEALER , H.APPROVAL_OPRID , H.DEAL_CONF_STATUS , B.BANK_TYPE , C.COUNTRY , D.TRANSACTION_AMT , D.TRANSACTION_AMT_2 , %Round(( %DecMult( %DECDIV( %DATEDIFF( PD_INT_START_DT, CALENDAR_DATE ), %DATEDIFF( PD_INT_START_DT, PERIOD_END_DT ) ) , PERIOD_INT_ACCRUAL ) ),3) + START_INT_ACCRUAL , %Round(( %DecMult( %DECDIV( %DATEDIFF(PERIOD_START_DT, CALENDAR_DATE ), %DATEDIFF(PERIOD_START_DT, PERIOD_END_DT) ), DISCOUNT_AMT )),3 ) + P.START_DISCOUNT_AMT , PRINCIPAL_BALANCE + (%Round( ( %DecMult( %DECDIV( %DATEDIFF(PD_INT_START_DT, CALENDAR_DATE ), %DATEDIFF(PD_INT_START_DT, PERIOD_END_DT ) ), PERIOD_INT_ACCRUAL ) ),3) + START_INT_ACCRUAL ) + ( %Round(( %DecMult( %DECDIV( %DATEDIFF(PERIOD_END_DT, CALENDAR_DATE) , %DATEDIFF(PERIOD_START_DT, PERIOD_END_DT ) ) , DISCOUNT_AMT ) ) ,3) + P.START_DISCOUNT_AMT) , H.LIMIT_RESERVED , H.DEAL_STATUS , H.EXCL_FROM_POSITION , %DateDiff( D.SETTLEMENT_DT, D.MATURITY_DT) , %DateDiff(D.ISSUE_DT, D.MATURITY_DT) , D.ANCHOR_AMOUNT , D.SETTLEMENT_DT , D.MATURITY_DT , H.FCLTY_ID , D.TRANSACTION_YIELD , D.TRANSACT_PROCEEDS , D.COMMODITY_CD , D.COMMODITY_RT_INDEX , D.CONTINGENT_UPON , D.DELIVERY_YEAR , D.MONTHCD , D.EXCHG_CD , D.FLOATING_MKT_CD , D.FLOATING_MKT_CD_2 , D.ISSUE_DT , D.NBR_OF_CONTRACTS , D.OPT_DELTA , D.OPT_EXERCISE_DT , D.OPT_INT_RATE_CAP , D.OPT_OPTION_STATUS , D.OPT_PURCHASE_WRITE , D.OPT_PUT_CALL , D.OPT_TRANSACT_LINE , D.QUANTITY , D.SECURITY_ID , D.TRANSACTION_PRICE , D.UNDERLYING_CCY , D.UNIT_OF_MEASURE FROM PS_POS_DAYS_TBL T , PS_TRX_POSITION_TR P , PS_TRX_DETAIL_TR D , PS_TRX_HEADER_TR H , PS_BANK_CD_TBL B , PS_CURRENCY_CD_TBL C WHERE P.TR_SOURCE_CD = 'D' AND D.BUSINESS_UNIT = P.BUSINESS_UNIT AND D.TREAS_HEADER_ID = P.TR_SOURCE_ID AND D.TRANSACTION_LINE = P.TRANSACTION_LINE AND H.BUSINESS_UNIT = P.BUSINESS_UNIT AND H.TREAS_HEADER_ID = P.TR_SOURCE_ID AND B.SETID = H.COUNTERPARTY_SETID AND B.BANK_CD = H.COUNTERPARTY AND C.CURRENCY_CD = P.POSITION_CURRENCY AND C.EFFDT = ( SELECT MAX(EFFDT) FROM PS_CURRENCY_CD_TBL WHERE CURRENCY_CD = P.POSITION_CURRENCY AND EFFDT <= %CurrentDateIn ) AND C.EFF_STATUS = 'A' AND PERIOD_START_DT <= T.CALENDAR_DATE AND PERIOD_END_DT > T.CALENDAR_DATE

  • 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 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
    4 TRANSACTION_LINE Number(3,0) SMALLINT NOT NULL The separate and distinct base instrument type components of a given deal transaction.
    5 CALENDAR_DATE Date(10) DATE An internal work field utilized to store calendar dates for a given calendar year. The dates are displa
    6 TREAS_MASTER_ID Character(12) VARCHAR2(12) NOT NULL Master ID for a given deal header.
    7 PD_INT_START_DT Date(10) DATE Represents the commencement date for calculating accrued interest for a given deal transa
    8 POSITION_CURRENCY Character(3) VARCHAR2(3) NOT NULL The transaction currency of a given position or exposure.
    9 PRINCIPAL_BALANCE Signed Number(28,3) DECIMAL(26,3) NOT NULL The notional principal balance of a given deal transaction at commencement.
    10 PERIOD_INT_ACCRUAL Signed Number(28,3) DECIMAL(26,3) NOT NULL Represents the amount of accrued interest attributable to an interest period for a given
    11 START_INT_ACCRUAL Signed Number(28,3) DECIMAL(26,3) NOT NULL The initial start amount for a deal transaction's next interest accrual.
    12 SPOT_RATE_MULT Number(16,8) DECIMAL(15,8) NOT NULL An internal convention utilized for determing whether a currency exchange spot rate is stored ei
    13 SPOT_RATE_DIV Number(16,8) DECIMAL(15,8) NOT NULL An internal convention utilized for determing whether a currency exchange spot rate is stored either
    14 FORWARD_RATE_MULT Number(16,8) DECIMAL(15,8) NOT NULL An internal convention utilized for determing whether a currency exchange forward rate is
    15 FORWARD_RATE_DIV Number(16,8) DECIMAL(15,8) NOT NULL An internal convention utilized for determing whether a currency exchange forward rate is stor
    16 RESET_RATE_SET Character(1) VARCHAR2(1) NOT NULL Allows the user to indicate that a floating rate for a particular cash flow has been ascertained and e
    N=Rate Not Set
    Y=Reset Rate Set
    17 TRANSACTION_RATE Number(16,8) DECIMAL(15,8) NOT NULL The applicable interest rate for a given deal transaction.
    18 RATE_RESET_DT Date(10) DATE The date that an interest rate quoted in the market is captured for purposes of calculating interest
    19 INSTRUMENT_TYPE Character(10) VARCHAR2(10) NOT NULL Instrument type
    20 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
    21 ASSET_LIABILITY Character(1) VARCHAR2(1) NOT NULL Whether the deal transaction leg represents an asset or a liability
    A=Asset
    L=Liability
    22 COUNTERPARTY Character(5) VARCHAR2(5) NOT NULL Represents a treasury dealing counterpart.
    23 BANK_CD Character(5) VARCHAR2(5) NOT NULL Bank Code
    24 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
    25 TRANSACTION_BROKER Character(5) VARCHAR2(5) NOT NULL "The deal transaction intermediary
    26 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
    27 TRANSACTION_DT Date(10) DATE Transaction date (often used as trade date)
    28 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.
    29 CPTY_DEALER Character(10) VARCHAR2(10) NOT NULL The assigned external dealer or trader associated with a given dealing counterparty.
    30 APPROVAL_OPRID Character(30) VARCHAR2(30) NOT NULL A system generated value that reflects the operator that approved a given transaction.
    31 DEAL_CONF_STATUS Character(2) VARCHAR2(2) NOT NULL Reflects the confirmation status of a given deal transaction at any particular point in time.
    01=Pending
    02=Confirmed
    03=Invalid
    32 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
    33 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country
    34 TRANSACTION_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL The nominal or actual deal transaction amount.
    35 TRANSACTION_AMT_2 Signed Number(28,3) DECIMAL(26,3) NOT NULL The nominal transaction amount for the second leg of a deal transacation.
    36 ACCRUED_INT Signed Number(28,3) DECIMAL(26,3) NOT NULL The periodic amount of accrued interest for a given deal transaction.
    37 AMORTIZED_DISC Signed Number(28,3) DECIMAL(26,3) NOT NULL Amount of Bond Premium or Discount that is slated to be amortized over the life of a deal.
    38 BOOK_VALUE Signed Number(28,3) DECIMAL(26,3) NOT NULL Book Value
    39 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
    40 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
    41 EXCL_FROM_POSITION Character(1) VARCHAR2(1) NOT NULL Allows the user to designate that a given deal transaction is not to be included in a subse
    N=Include in Position
    Y=Exclude From Position
    42 TR_TERM Signed Number(6,0) DECIMAL(5) NOT NULL "Negotiated length of a deal transaction; also
    43 TR_ORIGINAL_TERM Signed Number(5,0) DECIMAL(4) NOT NULL Initial stated term for a deal transaction.
    44 ANCHOR_AMOUNT Signed Number(28,3) DECIMAL(26,3) NOT NULL Principal amount of a given deal transaction denominated in the anchor currency.
    45 SETTLEMENT_DT Date(10) DATE The settlement date for a given cash -based transaction, or the start date for a Treasury Deal.
    46 MATURITY_DT Date(10) DATE The maturity date for a deal transaction.
    47 FCLTY_ID Character(12) VARCHAR2(12) NOT NULL A unique key identifier that represents a given counterparty facility.
    48 TRANSACTION_YIELD Signed Number(15,8) DECIMAL(13,8) NOT NULL The applicable yield for an interest rate physical deal transaction.
    49 TRANSACT_PROCEEDS Signed Number(28,3) DECIMAL(26,3) NOT NULL The amount of funds that changes hands when a deal is complete.
    50 COMMODITY_CD Character(10) VARCHAR2(10) NOT NULL Commodity Code

    Prompt Table: COMMOD_CODE

    51 COMMODITY_RT_INDEX Character(10) VARCHAR2(10) NOT NULL The code used as a key for obtaining market rates for Commodity Prices

    Prompt Table: RT_INDEX_COM_VW

    52 CONTINGENT_UPON Character(2) VARCHAR2(2) NOT NULL Instrument field for future. Indicates its line is currently 'underlying' another line. That is this security, say a T-Bond, will not be in position as a live instrument unless (06) the future (T-Bond Future) that it underlies is settled physically or (04) the option (Option on T-Bond) is exercised.
    04=Option
    06=Future
    53 DELIVERY_YEAR Number(4,0) SMALLINT NOT NULL Delivery Year
    54 MONTHCD Character(2) VARCHAR2(2) NOT NULL Month
    01=01 - January
    02=02 - February
    03=03 - March
    04=04 - April
    05=05 - May
    06=06 - June
    07=07 - July
    08=08 - August
    09=09 - September
    10=10 - October
    11=11 - November
    12=12 - December
    55 EXCHG_CD Character(6) VARCHAR2(6) NOT NULL Trading exchange code

    Prompt Table: TRX_EXCHANGE_CD

    56 FLOATING_MKT_CD Character(10) VARCHAR2(10) NOT NULL The code used as a key for obtaining market rates for interest rates and futures prices.

    Prompt Table: TRX_RT_RESET_CD

    57 FLOATING_MKT_CD_2 Character(10) VARCHAR2(10) NOT NULL A unique key identifier that describes a floating rate index basis associated with the second leg of a

    Prompt Table: TRX_RT_RESET_CD

    58 ISSUE_DT Date(10) DATE Issue Dt
    59 NBR_OF_CONTRACTS Signed Number(18,4) DECIMAL(16,4) NOT NULL This is the number of future contracts associated with a deal
    60 OPT_DELTA Signed Number(11,8) DECIMAL(9,8) NOT NULL An analytic valuation calculation that represents the proportion of an outstanding option that is regarded as

    Default Value: 1

    61 OPT_EXERCISE_DT Date(10) DATE The option exercise date.
    62 OPT_INT_RATE_CAP Character(1) VARCHAR2(1) NOT NULL An indicator for the type of option. Cap/floor or an option on a swap (swaption).
    B=Binary Cap/Floor
    N=Swaption
    Y=Cap / Floor

    Y/N Table Edit

    63 OPT_OPTION_STATUS Character(1) VARCHAR2(1) NOT NULL The current state for a given option transaction.
    A=Active
    C=Exercised
    I=Inactive
    P=Expired

    Default Value: A

    64 OPT_PURCHASE_WRITE Character(1) VARCHAR2(1) NOT NULL Describes whether the dealer is purchasing or writing a given option transaction.
    P=Purchase
    W=Write
    65 OPT_PUT_CALL Character(1) VARCHAR2(1) NOT NULL Describes whether a given option transaction reflects a short or long position.
    C=Call
    P=Put
    66 OPT_TRANSACT_LINE Number(3,0) SMALLINT NOT NULL Points to the line in a deal which the current line underlies. That is, the line number pointed to by this field must be either exercised or settled physically for the line containing this field to become 'real'.
    67 QUANTITY Signed Number(17,4) DECIMAL(15,4) NOT NULL Qty Interface
    68 SECURITY_ID Character(10) VARCHAR2(10) NOT NULL A reference identifier for a given deal transaction.
    69 TRANSACTION_PRICE Signed Number(28,3) DECIMAL(26,3) NOT NULL The preferred business price for a given deal transaction.
    70 UNDERLYING_CCY Character(3) VARCHAR2(3) NOT NULL Underlying Currency
    71 UNIT_OF_MEASURE Character(3) VARCHAR2(3) NOT NULL Used on an approval rule set.
    MHR=Muti Hourly
    PER=Percentage
    SQF=Square Footage

    Prompt Table: UNITS_TBL