This appendix contains the column mapping rules between the attributes of the XML bank statement and the Open Interface Tables in Oracle Cash Management.
This appendix covers the following topics:
The following table contains the column mapping rules between the <DepAcctStmtInqRs> in the XML bank statement and the CE_STATEMENT_HEADERS_INT table in Oracle Cash Management.
| Source Attributes in <DepAcctStmtInqRs> | Required in IFX? | Target (Columns in CE_STATEMENT_HEADERS_INT) | Required in Table? | Action |
|---|---|---|---|---|
| <DepAcctStmtId> Statement Number </DepAcctStmtId> |
No | STATEMENT_NUMBER | Yes | If Statement Number is NULL, use Statement Date |
| <EffDt> Statement Date </EffDt> |
No | STATEMENT_DATE | Yes | If Statement Date is NULL, use <EndDt> |
| <DepAcctId> <AcctId> Account Number </AcctId> </DepAcctId> |
Yes | BANK_ACCOUNT_NUM | Yes | |
| <DepAcctId> <AcctCur> Currency Code </AcctCur> </DepAcctId> |
No | CURRENCY_CODE | No | |
| <DepAcctId> <BankInfo> <Name> Bank Name </Name> </BankInfo> </DepAcctId> |
No | BANK_NAME | No | |
| <DepAcctId> <BankInfo> <BranchName> Branch Name </BranchName> </BankInfo> </DepAcctId> |
No | BANK_BRANCH_NAME | No | |
| <AcctBal> <BalType> OpeningLedger or BAI:010 </BalType> <CurAmt> <Amt> Begin Balance </Amt> </CurAmt> </AcctBal> |
No | CONTROL_BEGIN_BALANCE | No | Balance type must be 'OpeningLedger' or 'BAI:010' |
| <AcctBal> <BalType> ClosingLedger or BAI:015 </BalType> <CurAmt> <Amt> End Balance </Amt> </CurAmt> </AcctBal> |
No | CONTROL_END_BALANCE | No | Balance type must be 'ClosingLedger' or 'BAI:015' |
| <StmtSummAmt> <StmtSummType> Debits or Checks or OtherDebits or Fees or IntCharged or BAI:400 <StmtSummType> <CurAmt> <Amt> Total Debit </Amt> </CurAmt> </StmtSummAmt> |
No | CONTROL_TOTAL_DR | No | If <StmtSummType> is in ('Debit', 'Checks', 'OtherDebits', 'Fees', 'IntChaged'), then CONTROL_TOTAL_DR = Sum of the Amount. If <StmtSummType> is 'BAI:400', then CONTROL_TOTAL_DR = Amount. .' |
| <StmtSummAmt> <StmtSummType> Credits or Deposits or OtherCredits or IntEarned or BAI:100 </StmtSummType> <CurAmt> <Amt> Total Credit </Amt> </CurAmt> </StmtSummAmt> |
No | CONTROL_TOTAL_CR | No | If <StmtSummType> is in ('Credits', 'Deposits', 'OtherCredits', 'IntEarned'), then CONTROL_TOTAL_CR = Sum of the Amount. If <StmtSummType> is 'BAI:100', then CONTROL_TOTAL_CR = Amount. |
| <StmtSummAmt> <StmtSummType> Debits or Checks or OtherDebits or Fees or IntCharged </StmtSummType> <Count> Count </Count> </StmtSummAmt> |
No | CONTROL_DR_LINE_COUNT | No | If <StmtSummType> is in ('Debit', 'Checks', 'Other Debits', 'Fees', 'IntCharged'), then CONTROL_DR_LINE_COUNT = Sum of the Count. If <StmtSummType> is 'BAI:400', then CONTROL_DR_LINE_COUNT = Count. |
| <StmtSummAmt> <StmtSummType> Credits or Deposits or OtherCredits or IntEarned or BAI:400 </StmtSummType> <Count> Count </Count> </StmtSummAmt> |
No | CONTROL_CR_LINE_COUNT | No | If <StmtSummType> is in ('Credits', 'Deposits', 'OtherCredits', 'IntEarned'), then CONTROL_CR_LINE_COUNT = Sum of the Count. If <StmtSummType> is 'BAI:100', then CONTROL_CR_LINE_COUNT = Count. |
| No | CONTROL_LINE_COUNT | No | CONTROL_CR_LINE_COUNT + CONTROL_DR_LINE_COUNT | |
| <AcctBal> <BalType> BAI:045 </BalType> <CurAmt> <Amt> Int Calc Balance </Amt> </CurAmt> </AcctBal> |
No | INT_CALC_BALANCE | No | Balance type must be 'BAI:045' |
| <AcctBal> <BalType> BAI:045 </BalType> <CurAmt> <Amt> Cashflow Balance </Amt> </CurAmt> </AcctBal> |
No | CASHFLOW_BALANCE | No | Balance type must be 'BAI:045' |
| No | RECORD_STATUS_FLAG | No | Defaults to "N" | |
| No | ORG_ID | No | Org ID will be populated according to the bank account number. | |
| No | CHECK_DIGITS | No | ||
| No | WHO Columns | No | ||
| No | ATTRIBUTE Columns | No |
The following table contains the column mapping rules between the attributes in <DepAcctTrnRec> in the XML bank statement and the CE_STATEMENT_LINES_INTERFACE table in Oracle Cash Management. The <DepAcctTrnRec> is passed as one of the data elements of <DepAcctStmtInqRs>.
| Source Attributes in <DepAcctTrnRec> | Required in IFX? | Target (Columns in CE_STATEMENT_LINES) | Required in Table? | Action |
|---|---|---|---|---|
| No | STATEMENT_NUMBER | Yes | Passed from Header | |
| No | BANK_ACCOUNT_NUM | Yes | Passed from Header | |
| No | LINE_NUMBER | Yes | Generate Count | |
| <BankAcctTrnRec> <PostedDt> Posted Date </PostedDt> </BankAcctTrnRec> |
No | TRX_DATE | Yes | |
| <BankAcctTrnRec> <TrnType> Transaction Type </TrnType> </BankAcctTrnRec> |
No | TRX_CODE | No | |
| <BankAcctTrnRec> <EffDt> Effective Date </EffDt> </BankAcctTrnRec> |
No | EFFECTIVE_DATE | No | |
| <BankAcctTrnRec> <Memo> Description </Memo> </BankAcctTrnRec> |
No | TRX_TEXT | No | |
| <BankAcctTrnRec> <InvoiceInfo> <InvoiceNum> InvoiceNumber </InvoiceNum> </InvoiceInfo> </BankAcctTrnRec> |
No | INVOICE_TEXT | No | |
| <BankAcctTrnRec> <CounterpartyInfo> <DepAcctId> <AcctId> Account Number </AcctId> </DepAcctId> </CounterpartyInfo> </BankAcctTrnRec> |
No | BANK_ACCOUNT_TEXT | No | |
| <BankAcctTrnRec> <CurAmt> <Amt> Trx Amount </Amt> </CurAmt> </BankAcctTrnRec> |
No | AMOUNT | No | |
| <BankAcctTrnRec> <CurAmt> <CurCode> Currency Code </CurCode> </CurAmt> </BankAcctTrnRec> |
No | CURRENCY_CODE | No | |
| No | USER_EXCHANGE_RATE_TYPE | No | ||
| No | EXCHANGE_RATE_DATE | No | ||
| <BankAcctTrnRec> <CurAmt> <CurCode> Cur Rate </CurCode> </CurAmt> </BankAcctTrnRec> |
No | EXCHANGE_RATE | No | |
| <BankAcctTrnRec> <OrigCurAmt> <Amt> Original Amount </Amt> </OrigCurAmt> </BankAcctTrnRec> |
No | ORIGINAL_AMOUNT | No | |
| <BankAcctTrnRec> <CompositeCurAmt> <CurAmt> <Amt> Charge Amount </Amt> </CurAmt> </CompositeCurAmt> </BankAcctTrnRec> |
No | CHARGES_AMOUNT | No | |
| <ChkNum> Check Number </ChkNum> Or <XferId> Fund Transfer ID </XferId> Or <PmtId> Payment ID </PmtId> |
No | BANK_TRX_NUMBER | No | |
| <BankAcctTrnRec> <CounterpartyInfo> <RefInfo> <RefId> Reference ID </RefId> </RefInfo> </CounterpartyInfo> </BankAcctTrnRec> |
No | CUSTOMER_TEXT | No | |
| No | WHO Columns | No | ||
| No | ATTRIBUTE Columns | No |