OPRIDTR_LIQ_BNKAC_VW(SQL View) |
Index Back |
|---|
| SELECT C.OPRID , A.BANK_SETID , A.BANK_CD , A.BANK_CD , A.BANK_ACCT_KEY , A.VALUE_DT , 0 , D2.STMT_BAL_AVAIL , SUM(A.POS_IMPCT_ORIG) -D2.STMT_BAL_AVAIL , (SUM(A.POS_IMPCT_ORIG)-D2.STMT_BAL_AVAIL)*100 / NULLIF(SUM(A.POS_IMPCT_ORIG) ,0) , SUM(A.POS_IMPCT_ORIG) , A.POSITION_CURRENCY , D2.BANK_ACCOUNT_NUM1 FROM PS_CSH_FCST_SUM_VW A , PS_TR_CASH_POS_CFG C , PS_BANK_CD_TBL E LEFT OUTER JOIN PS_BANK_BAL_DAILY D1 ON E.BNK_ID_NBR = D1.BNK_ID_NBR , PS_BANK_ACCT_DEFN F LEFT OUTER JOIN PS_BANK_BAL_DAILY D2 ON F.BANK_ACCOUNT_NUM = D2.BANK_ACCOUNT_NUM WHERE A.SETID = C.SETID AND A.SETID = E.SETID AND A.BANK_CD = E.BANK_CD AND A.SETID = F.SETID AND A.BANK_CD = F.BANK_CD AND A.BANK_CD = F.BANK_CD_CPTY AND A.BANK_ACCT_KEY = F.BANK_ACCT_KEY AND A.WORKSHEET_ID = C.WORKSHEET_ID AND A.GROUP_BY_FIELD = 'BAC' AND A.BNK_ACCT_DFLT_CURR = 'Y' AND A.SOURCE_SET = C.SOURCE_SET AND D1.BNK_ID_NBR = D2.BNK_ID_NBR AND D1.BANK_ACCOUNT_NUM = D2.BANK_ACCOUNT_NUM AND A.CALC_DT IN ( SELECT MAX(B.CALC_DT) FROM PS_CSH_FCST_SUM_VW B WHERE B.SETID = A.SETID AND B.WORKSHEET_ID = A.WORKSHEET_ID AND B.SOURCE_SET = A.SOURCE_SET AND B.WRKSHEET_LINE_ITEM = A.WRKSHEET_LINE_ITEM AND B.VALUE_DT = A.VALUE_DT AND B.POSITION_CURRENCY = A.POSITION_CURRENCY ) GROUP BY C.OPRID, A.BANK_SETID , A.BANK_CD , A.BANK_ACCT_KEY, A.VALUE_DT, D2.STMT_BAL_AVAIL, A.POSITION_CURRENCY, D2.BANK_ACCOUNT_NUM1 |
| # | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
|---|---|---|---|---|
| 1 | OPRID |
Character(30) | VARCHAR2(30) NOT NULL | A user's ID (see PSOPRDEFN). |
| 2 | SETID | Character(5) | VARCHAR2(5) NOT NULL | SetID |
| 3 | BANK_CD | Character(5) | VARCHAR2(5) NOT NULL | Bank Code |
| 4 | BANK_CD_CPTY | Character(5) | VARCHAR2(5) NOT NULL | An internal key field convention that facilitates the system distinction between banks and counterparties. |
| 5 | 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 |
| 6 | VALUE_DT | Date(10) | DATE | Equivalent to a deal transaction's settlement date. |
| 7 | AMOUNT | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Amount |
| 8 | TARGET_BALANCE | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | The target bank account balance assigned to a given bank account. |
| 9 | VARIANCE | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Variance Amount |
| 10 | VARIANCE_PCT | Signed Number(7,2) | DECIMAL(5,2) NOT NULL | Variance Percent |
| 11 | POS_IMPCT_ORIG | Signed Number(28,3) | DECIMAL(26,3) NOT NULL | Represents the calculated monetary value for a specific position tree node; the result |
| 12 | CURRENCY_CD | Character(3) | VARCHAR2(3) NOT NULL | Currency Code |
| 13 | BANK_ACCOUNT_NUM1 | Character(35) | VARCHAR2(35) NOT NULL | The literal unique identifier associated with a given internal partner bank/counterparty acco |