BNK_RCN_SUMP

(SQL View)
 Index Back

Revaluation: AR Deposits

View that revalues AR Deposits into bank account's default currency

SELECT A.DEPOSIT_BU , A.DEPOSIT_ID , SUM(%Round(%DecMult(%DecDiv(A.PAYMENT_AMT, R.RATE_DIV), R.RATE_MULT), F.DECIMAL_POSITIONS)) , B.BNK_ID_NBR , B.BANK_ACCOUNT_NUM FROM PS_PAYMENT A , PS_DEPOSIT_CONTROL B , PS_BANK_ACCT_DEFN C , PS_BANK_ACCT_CURR D , PS_RT_RATE_TBL R , PS_CURRENCY_CD_TBL F WHERE B.DEPOSIT_BU = A.DEPOSIT_BU AND B.DEPOSIT_ID = A.DEPOSIT_ID AND B.RECON_TYPE = 'U' AND C.SETID = B.BANK_SETID AND C.BANK_CD = B.BANK_CD AND C.BANK_CD_CPTY = B.BANK_CD AND C.BANK_ACCT_KEY = B.BANK_ACCT_KEY AND D.SETID = C.SETID AND D.BANK_CD = C.BANK_CD AND D.BANK_CD_CPTY = C.BANK_CD_CPTY AND D.BANK_ACCT_KEY = C.BANK_ACCT_KEY AND D.CURRENCY_CD = A.PAYMENT_CURRENCY AND R.RT_RATE_INDEX = D.RATE_INDEX AND R.TERM = 0 AND R.FROM_CUR = D.CURRENCY_CD AND R.TO_CUR = C.CURRENCY_CD AND R.RT_TYPE = D.CUR_RT_TYPE AND R.EFFDT = ( SELECT MAX(R_ED.EFFDT) FROM PS_RT_RATE_TBL R_ED WHERE R.RT_RATE_INDEX = R_ED.RT_RATE_INDEX AND R.TERM = R_ED.TERM AND R.FROM_CUR = R_ED.FROM_CUR AND R.TO_CUR = R_ED.TO_CUR AND R.RT_TYPE = R_ED.RT_TYPE AND R_ED.EFFDT <= A.ACCOUNTING_DT) AND F.CURRENCY_CD = C.CURRENCY_CD AND F.EFFDT = ( SELECT MAX(F_ED.EFFDT) FROM PS_CURRENCY_CD_TBL F_ED WHERE F.CURRENCY_CD = F_ED.CURRENCY_CD AND F_ED.EFFDT <= %CurrentDateIn) GROUP BY A.DEPOSIT_BU, A.DEPOSIT_ID, B.BNK_ID_NBR, B.BANK_ACCOUNT_NUM

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 DEPOSIT_BU Character(5) VARCHAR2(5) NOT NULL Identifies the business unit to which a deposit is posted.
2 DEPOSIT_ID Character(15) VARCHAR2(15) NOT NULL Uniquely identifies a deposit of customer payments. The deposit ID can be manually assigned or system-generated.
3 BANK_ACCT_RVL_AMT Signed Number(28,3) DECIMAL(26,3) NOT NULL Field used to store the amount on a transaction revalued in the base currency of the transactions bank account.
4 BNK_ID_NBR Character(20) VARCHAR2(20) NOT NULL A literal alphanumeric attribute that uniquely identifies a given bank or counterparty.
5 BANK_ACCOUNT_NUM Character(35) VARCHAR2(35) NOT NULL The literal unique identifier associated with a given bank/counterparty account.