TRX_POS_DEAL_VW

(SQL View)
 Index Back

Transaction Position View

Position change due to Treasury Deal

SELECT D.BUSINESS_UNIT , P.TR_SOURCE_ID , D.TRANSACTION_LINE , P.SEQ_NBR , P.TRANSACTION_LEG , %CurrentDateIn , 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, %CURRENTDATEIN ), %DATEDIFF( PD_INT_START_DT, PERIOD_END_DT ) ) , PERIOD_INT_ACCRUAL ) ),3) + START_INT_ACCRUAL , %Round(( %DecMult( %DECDIV( %DATEDIFF(PERIOD_START_DT, %CURRENTDATEIN ), %DATEDIFF(PERIOD_START_DT, PERIOD_END_DT) ), DISCOUNT_AMT ) ),3) + P.START_DISCOUNT_AMT , PRINCIPAL_BALANCE + (%Round( ( %DecMult( %DECDIV( %DATEDIFF(PD_INT_START_DT, %CURRENTDATEIN ), %DATEDIFF(PD_INT_START_DT, PERIOD_END_DT ) ), PERIOD_INT_ACCRUAL ) ),3) + START_INT_ACCRUAL ) + ( %Round(( %DecMult( %DECDIV( %DATEDIFF(PERIOD_END_DT, %CURRENTDATEIN) , %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 FROM 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_END_DT > %CurrentDateIn AND PERIOD_START_DT <= %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 TRANSACTION_LINE Number(3,0) SMALLINT NOT NULL The separate and distinct base instrument type components of a given deal transaction.
    4 SEQ_NBR Number(15,0) DECIMAL(15) NOT NULL Sequence Number
    5 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
    6 CALENDAR_DATE Date(10) DATE An internal work field utilized to store calendar dates for a given calendar year. The dates are displa
    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.