Accounts Receivable and Cash Management Integration FAQ

1. Why is the message “The Clearing/Reconciliation GL Date cannot be earlier than the unclearing/unreconciliation GL date of this receipt” raised when reconciling a receipt after it is uncleared?

This is an intended functionality where system validates whether the clearing/reconciliation GL date is earlier or later than the unclearing/ unreconciliation GL date.

After unclearing/un-reconciliation of  a receipt, if an attempt is made to clear/unreconcile the receipt again with a date earlier than the GL date of the unclearing/unreconciliation date, then the message appears as part of standard validation.  To avoid this error, clearing/reconciling the receipt with GL dates should be later than the GL date of unclearing or unreconciling GL dates only.

2. Can auto reconciliation handle receipts with Non Sufficient Funds (NSF)?

When submitting Autoreconciliation, a parameter can be passed to control NSF handling.
Based on the value being passed, the system will determine the actions to be performed.  The available values are:

No Action: Resubmit the check or request for transfer later.  This is the default action
Reverse: Open the invoice.
Debit Memo Reverse: Create a debit memo that replaces the item closed by the original receipt.

Note: This option can also be selected when submitting Bank Statement Loader Program

3. How to reconcile receipts reversed as STOP payment?

When you reverse a receipt as STOP payment, both the original Remitted line and the Reversal line show up in Cash Management for reconciliation. In order for this receipt not to show in Transactions Available for Reconciliation Report, you need to reconcile the original statement line to the Remitted record of the receipt and enter a new Statement line of type ‘Rejected’ and reconcile it to the Reversed receipt line so the receipt does not show up on the report.

Cash in Transit, GLDATE Next Month, How Do You Reconcile Cash Clearing?

Problem Description
-------------------
How do you reconcile Cash in Transit when Clearing in next month, statement in this month, and this month is closed?

In Oracle Receivables, Cash Management, you have entered a July statement, when July was closed, and clearances occurred with GL date of August, but Cash in Transit Report (July) does not include any items cleared on this statement.   The clearance account in GL will not contain entries till August.
How do you reconcile Cash in Transit to the GL clearing account?


Solution Description
--------------------
Lets clear up a few things.  
- There is reconciliation, and there is clearing.
- You can have cleared transactions.    
- Cleared transactions in AR would contain a row on ar_cash_receipt_history_all with a status of ‘CLEARED’.   This transaction would contain a GLDATE.   That is the date that this transaction would be reflected in General Ledger, when it has been posted.   The account used isthe one on AR_DISTRIBUTIONS_ALL that is tied to the AR_CASH_RECEIPT_HISTORY_ALL row by the AR_CASH_RECEIPT_HISTORY_ALL.cash_receipt_history_id to AR_DISTRIBUTIONS_ALL.source_id.

When you talk about reconciling in Cash Management, you are talking about reconciling your bank statement to transactions in your AR, AP, and Payroll system.   You match the AR check number to the check number on the Bank Statement.   Transactions that have not been ‘reconciled’ are available to be matched to the bank statement.   These transaction may have already been ‘CLEARED’ or they may be ‘CLEARED’ and reconciled in the same step. You can
clear payments in Oracle Payables and receipts in Oracle Receivables prior to reconciliationto maintain more up-to-date cash account balances. When you clear a transaction, you assign a cleared date, GLDATE and status to the transaction, and accounting entries for cash clearing are created. When you reconcile a transaction, the transaction is first cleared (if uncleared) and then matched to bank statements.


When transactions are created in AR, they are created with a status of:
Approved: This receipt has been approved for automatic receipt creation. 
This status is only valid for automatic receipts. 

Confirmed: The customer has approved the application of this receipt and their account balances have been updated within Receivables. This status is only valid for automatic receipts.

Remitted: This receipt has been remitted. This status is valid for both automatic and manually entered receipts.

Cleared: The payment of this receipt was transferred to your bank account and the bank statement has been reconciled within Receivables. This status is valid for both automatic and manually entered receipts.

Reversed: This receipt has been reversed. You can reverse a receipt when your customer stopspayment on a receipt, if a receipt comes from an account with non-sufficient funds or if yo want to re-enter and reapply it in Receivables. You can reverse cash receipts and miscellaneous transactions.

In AR, you define receipt classes to determine the required processing steps for receipts towhich you assign payment methods with this class. These steps include confirmation, remittance, and reconciliation. For example, you must create and remit a direct debit, but you must create, confirm, and remit a bill of exchange. You can specify any combination of  these processing steps with one exception: if you confirm and reconcile, then you must also remit. 
If you enter No for  all three of these steps, Receivables automatically creates your receipts as reconciled.

You would create an Automatic Receipt first waiting to be Confirmed, then Remitted, then Cleared.   If you Reversed it, it would undo all of the other 3.


Confirming Automatic Receipts:
------------------------------ 

Confirming automatic receipts involves sending the receipts to your customers for review andapproval. Depending on the agreement you have with your customer, certain types of automatic receipts require confirmation from your customer before they can be considered as payments and remitted to the bank.
Once your customers approve these receipts, you can make any necessary changes,then confirm the receipts in your system. To indicate that a receipt requires confirmation, you assign a receipt class that has the Require Confirmation option set to Yes. See: Receipt Classes in Oracle Receivables User Manual. 
An example of receipts that require confirmation are Signed Bills of Exchange.
Examples of receipts that do not require confirmation are direct Debits and Unsigned Bills of Exchange. Receipts that do not require confirmation are created as confirmed.

If the receipt class assigned to an automatic receipt or automatic receipt batch requires confirmation, you must confirm the receipt or batch once it has been approved. If the receipt class does not require confirmation, Receivables automatically confirms all of the receipts within the batch when you approve the batch. See: Approving Automatic Receipts. 

You can update a batch of automatic receipts before you confirm it. You can review and update the invoices you have selected to apply to the receipt as well as modify the receipt maturity date, remittance bank, and customer bank information. However, you can only change the approved amounts for your receipt applications if the receipt is not confirmed. After confirmation, Receivables automatically updates the invoice balance.

Reconciliation:
---------------
Use Oracle Cash Management to reconcile your bank statements with your outstanding balances,transactions, and receipts in Receivables. Oracle Cash Management improves bank reconciliation by automating the processing of bank statements and by providing appropriate management and exception reporting.

Receivables also lets you periodically reconcile customer balances with your receivables accounts. By generating various Receivables reports, you can reconcile outstanding customer balances at the beginning of any period with the ending balances for that period.

In AR Reconcile the account balances for transactions and receipts using the Journal Entries report and the Sales and Receipt Journals.
Periodically check that Receivables transactions balance with themselves by running the Sales Journal by GL Account and the Transaction Register for the same GL Date range. This will ensure that all postable items are reflected on your Sales Journal.

The total on the Sales Journal by GL Account should equal the total of postable items in the Transaction Register. In case of any discrepancies, view your customer balances using the Sales Journal by Customer report to find which total does not balance. Reconcile Receipts

Periodically check that Receivables receipts balance by running the Receipt Journal report and the Receipt Register for the same GL Date range. 

The total of the Receipt Journal should equal the total of all receipts in the Receipt Register. Both reports display invoice related receipts and miscellaneous receipts.   Submit the two reports from either the Print Accounting Reports or Submit Requests window.   Select the same GL Dates for the two reports and choose a Report Mode of 'Transaction' to run the Receipt Journal.   Transaction mode gives you full details of all the accounts debited
or credited during the receipt creation, remittance, and clearance processes. 
The alternative, 'Balance' mode, gives details of the final account balance only.

Now the reason I got into the status and reconciliation issues, is because Cash Management which is used to reconcile the receipts in AR to the bank statement,  uses ‘Cash In Transit Report’  and ‘Receipts Available for Reconciliation - Transaction Report’.
The ‘Cash in Transit’ uses an as of cut off date, and shows transactions that have not been remitted to the bank.   It also excludes reversed transactions.   In other words, it only shows transactions with a status of Confirmed, which have been entered (Receipt Date), no later than the as of date.   

Based on this, in order for your receipts to show on this report, they would have a status of Confirmed, and a receipt date of no later than July 31.   

The Transactions Available for Reconciliation Report shows all transactions available for reconciliation for a specific bank account, and it groups them by status.   So, this report will show all transactions, ‘REMITTED’, ‘CLEARED’,’REVERSED’.   So it is possible that both the Cash in Transit, and Transactions Available for Reconciliation Report will not match.

Now, since your receipts have a Cleared date of Aug 01, which I am assuming that is the gldate, but may well have a receipt date of July,  this would exclude them from the possibility of being included in a July report, because at the time of the running of the report, the transactions have a status of Cleared.   I don’t believe the report restricts itself to transactions that were waiting to be Cleared in a previous period, but instead looks at the 
current status of the receipt, in which case, it would have been  ‘CLEARED’.   

The whole purpose of a Cash in Transit report is to find out what you have to complete, Cash that is not done yet.   So, it wouldn’t make sense to run this looking back at what things used to look like.   Instead it is used as a current picture of your account.   

So, what you are seeing is correct.   I would use the AR process to satisfy the balance in the Cleared account in GL with the Cleared account in AR. These would be the August dates.   This would be for posted transactions for August.   If you use the same report to reconcile Receivables for July GL dates, you will see the ‘Cash’ in the Remitted, or Confirmed accounts. 
It would move out of those account to the Clearing account in July.

Link between Oracle General Ledger Journal Line and Cash Management Reconciliation Line

In Oracle Cash Management Reconciliation when we select available transaction then select Journals (General Ledger Journals) what the journals will appear.

The main view which illustrate the link between General Ledger Journal Lines and Cash Management is CE_101_TRANSACTIONS_V which contains the following tables and views:

1. CE_STATEMENT_RECONCILIATIONS.
2. GL_JE_LINES.
3. GL_JE_HEADERS.
4. AP_BANK_ACCOUNTS.

The nature of the link between General Ledger Journals Lines and Cash Management Reconciliation is divided to the following relationships:

Relation between AP_BANK_ACCOUNTS and the GL_JE_LINES:

This is the main relation which define the bank account related to the journal line:

WHERE AP_BANK_ACCOUNTS.ASSET_CODE_COMBINATION_ID = GL_JE_LINES.CODE_COMBINATION_ID

You can use the following statement to get journal lines and its bank accounts:

SELECT H.JE_SOURCE,
       H.JE_CATEGORY, 
       H.JE_HEADER_ID,
       H.DOC_SEQUENCE_VALUE,
       H.DEFAULT_EFFECTIVE_DATE,             
       D.JE_LINE_NUM,
       D.DESCRIPTION,
       D.ACCOUNTED_CR,
       D.ACCOUNTED_DR,
       B.BANK_ACCOUNT_NAME,
       B.BANK_ACCOUNT_NUM,
       B.BANK_ACCOUNT_TYPE
  FROM AP_BANK_ACCOUNTS B, 
       GL.GL_JE_LINES   D,
       GL.GL_JE_HEADERS H
 WHERE B.ASSET_CODE_COMBINATION_ID = D.CODE_COMBINATION_ID
   AND D.JE_HEADER_ID              = H.JE_HEADER_ID

Relation between CE_STATEMENT_RECONCILIATIONS and the GL_JE_LINES:

This relation show the Journal line reconciled or not.

WHERE CE_STATEMENT_RECONCILIATIONS.JE_HEADER_ID   = GL_JE_LINES.JE_HEADER_ID
  AND CE_STATEMENT_RECONCILIATIONS.REFERENCE_ID   = GL_JE_LINES.JE_LINE_NUM
  AND CE_STATEMENT_RECONCILIATIONS.REFERENCE_TYPE = 'JE_LINE'

You can use the following SQL statements to get a list of Reconcilated journal lines and reconcilation bank statement information:

SELECT H.JE_SOURCE,
       H.JE_CATEGORY, 
       H.JE_HEADER_ID,
       H.DOC_SEQUENCE_VALUE,
       H.DEFAULT_EFFECTIVE_DATE,             
       D.JE_LINE_NUM,
       D.DESCRIPTION,
       D.ACCOUNTED_CR,
       D.ACCOUNTED_DR,
       SH.STATEMENT_HEADER_ID,
       SH.STATEMENT_NUMBER,
       SH.STATEMENT_DATE,
       SD.STATEMENT_LINE_ID,
       SD.LINE_NUMBER          
  FROM CE_STATEMENT_RECONCILIATIONS S ,
       GL.GL_JE_LINES               D ,
       GL.GL_JE_HEADERS             H ,
       CE_STATEMENT_HEADERS_V       SH,
       CE_STATEMENT_LINES_V         SD     
 WHERE SH.STATEMENT_HEADER_ID = SD.STATEMENT_HEADER_ID
   AND S.STATEMENT_LINE_ID    = SD.STATEMENT_LINE_ID
   AND D.JE_HEADER_ID         = H.JE_HEADER_ID
   AND S.JE_HEADER_ID         = D.JE_HEADER_ID
   AND S.REFERENCE_ID         = D.JE_LINE_NUM
   AND S.REFERENCE_TYPE       = 'JE_LINE'

Now you can use the following SQL statements to get the Journal lines which are not reconciled (Available to reconciliation):

SELECT H.JE_SOURCE,
       H.JE_CATEGORY, 
       H.JE_HEADER_ID,
       H.DOC_SEQUENCE_VALUE,
       H.DEFAULT_EFFECTIVE_DATE,             
       D.JE_LINE_NUM,
       D.DESCRIPTION,
       D.ACCOUNTED_CR,
       D.ACCOUNTED_DR,
       B.BANK_ACCOUNT_NAME,
       B.BANK_ACCOUNT_NUM,
       B.BANK_ACCOUNT_TYPE
  FROM AP_BANK_ACCOUNTS B, 
       GL.GL_JE_LINES   D,
       GL.GL_JE_HEADERS H
 WHERE B.ASSET_CODE_COMBINATION_ID = D.CODE_COMBINATION_ID
   AND D.JE_HEADER_ID              = H.JE_HEADER_ID
   AND NOT EXISTS (SELECT 0
                     FROM CE_STATEMENT_RECONCILIATIONS P,
                          GL.GL_JE_LINES               K                            
                    WHERE P.JE_HEADER_ID         = K.JE_HEADER_ID
                      AND P.REFERENCE_ID         = K.JE_LINE_NUM
                      AND P.REFERENCE_TYPE       = 'JE_LINE'  
                      AND K.JE_HEADER_ID         = H.JE_HEADER_ID
                      AND K.JE_LINE_NUM          = D.JE_LINE_NUM)

Create new Bank Statement Mapping Template(BAI2, SWIFT940, EDIFACT, MT940 and Non Standard Format)

How to create a new Bank Statement Mapping Template to meet your requirements:

1. BAI2 
2. SWIFT940
3. EDIFACT
4. MT940
5. Non Standard Format
   

(1).Entering Bank Statements
Before you can reconcile transactions against a bank statement, you need to enter the bank statement information into Oracle Cash Management. You can accomplish this task by doing one of the following:
– Using the Bank Statement Loader program
– Using a custom loader program
– Manually entering the information

The seeded Bank Statement Mapping Formats of BAI2 or SWIFT940 or EDIFACT can not be updated. To meet the Bank Data requirements mapping you need to create a new mapping templates which defaults the current seeded template. Once the new template is created with defaults setups you can update the setups to your requirement.

Navigate: CE>Setup>Bank>Bank Statement Mapping:

1. Mapping Data Structure:BAI2:

Example Data File:

01,121345678,7777777,100119,1431,1431,80,1,2/
02,7777777,121345678,1,100118,0000,USD,/
03,<YOUR BANK ACCOUNT>,USD,10,100.00,15,1163083,400,1153083,4,/
16,100,76995,V,011020,,Bank Reference Text,26446,Office Supplies/
16,100,812213,V,011015,,323532,A39599,Travel expenses/
16,100,242500,V,011017,,5434634N,46400-333,Rent expense/
16,100,21375,V,011019,,264,83832,Team Building Event/
49,2306166,6/
98,2306166,1/
99,2306166,1,10/

1. Open the Bank Statement Mappings window.
2. If you want to review a mapping template, choose one from the list of values. Otherwise, cancel
the list of values.
3. Create a new record for the new mapping template.
4. In the Name field, enter the name of the new mapping template. > TEST_BAI2
5. In the Description field, enter the description for the new mapping template.>TEST BAI2
6. Control File: select: BAI2.ctl

7. Precision: 2
8. Date Format: YYMMDD
9. Time Stamp Format: HH24MI *

*Please note: If Time Stamp is not available in data file at Rec_Id 02 then this is not required example:

02,ANSER420,121000248,1,110826,,,/  This line do not have Time Stamp.

02,<bank customer ID>,<bank originator ID>,<group status>,<as of date>, <as of time>,<currency code>,<as of date modifier>/

10. Bank File Format Type: BAI2
11. Press Button: Populate.. it should populate default Header and Lines mapping.
12. This you can correct as per your requirements:
BANK_TRX_NUMBER 16 -2
Insure to Remove: Example(xxx)

Column Name                               Rec id           Position

HEADER
STATEMENT_NUMBER                02              4
BANK_ACCOUNT_NUM             03              1
STATEMENT_DATE                      02              4
CURRENCY_CODE                       03              2
STATEMENT_TIMES TAMP         02              5

LINES
TRX_CODE                                     16            1
AMOUNT                                        16            2
TRX_TEXT                                      16           -1
INVOICE_TEXT                             16           -2
BANK_TRX_NUMBER                  16           -2
CUSTOMER_TEXT                         16          -3
12. Save
13. Recheck by :
14. Open the Bank Statement Mappings window.
15. Choose the one from the list of values.. if you see your TEST_BAI2 that means this is created and now you can use it.

Please Note: As per CE UG: Bank Statement Mapping Templates D-5

Since the number of fields in record ID 16 varies, depending upon information such as funds type, the location of these fields in the intermediate table cannot be determined in advance.

However, Text,Customer Ref No, and Bank Ref No are always located at the end of the record, where Text is the last field, Customer Ref No is the second to the last field, and Bank Ref No is the third to the last field.

They are represented by negative positions.

– Text field is mapped to the BANK_TRX_NUMBER column and the TRX_TEXT column as position -1.
– Customer Ref No field is mapped to the INVOICE_TEXT column as position -2.
– Bank Ref No field is mapped to the CUSTOMER_TEXT column as position -3.

If your bank uses different locations for these fields, you need to modify the mapping accordingly.
In addition, the BANK_TRX_NUMBER column is populated with data from the Text field that matches the default format of Example (xxx). You must change the format to the actual format used by your bank.

Review: Official specifications for BAI2 available @ http://www.bai.org
https://businessonline.huntington.com/common/cms/help/BAI1Technical%20Ref%20Manual.pdf

Note:1281394.1 – Mapping Of Balance for BAI2 Bank Statemant Data files
Note:752532.1 – When Mapping BAI2 Bank Statement Format Masking And BANK_TRX_NUMBER

2. Mapping Data Structure:  SWIFT940: 
1. Open the Bank Statement Mappings window.
2. If you want to review a mapping template, choose one from the list of values. Otherwise, cancel the list of values.
3. Create a new record for the new mapping template.
4. In the Name field, enter the name of the new mapping template. > SWIFT940_NEW
5. In the Description field, enter the description for the new mapping template.>SWIFT940_NEW
6. Control File: select: SWIFT940.ctl
7. DATE FORMAT: YYMMDD
8. TIME STAMP FORMAT : HH24MI
9. BANK FILE FORMAT TYPE:SWIFT940
10. Press Button: Populate.. it should populate default Header and Lines mapping.
11. This you can updated as per your requirements.

Column Name                                            Rec id            Position

HEADER
STATEMENT_NUMBER                           28                    1

BANK_ACCOUNT_NUM                        25                     1
CONTROL_BEGIN_BALANCE               60                     5
STATEMENT_DATE                                 62                     3
CURRENCY_CODE                                  60                    4
CONTROL_END_BALANCE                   62                    5

LINES
TRX_DATE                                              61                     1
BANK_ACCOUNT_TEXT                     61                      8
AMOUNT                                                61                      5
CUSTOMER_TEXT                                 61                     7
BANK_TRX_NUMBER                          61A                   1
TRX_TEXT                                              61A                   1
TRX_CODE                                             61                     6

12. Save
13. Recheck by:
14. Open the Bank Statement Mappings window.
15. Choose the one from the list of values.. if you see your SWIFT940_NEW that means this is created and now you can use it.
3. Mapping Data Structure: EDIFACT:

You can create new mapping templates by copying the existing templates.
1. Open the Bank Statement Mappings window.
2. If you want to review a mapping template, choose one from the list of values. Otherwise, cancel the list of values.
3. Create a new record for the new mapping template.
4. In the Name field, enter the name of the new mapping template. > TEST_EDIFACT
5. In the Description field, enter the description for the new mapping template.>Mapping template for TEST_EDIFACT
6. Control File: select: EDIFACT.ctl
7. DATE FORMAT: DDMMYY
8. BANK FILE FORMAT TYPE: EDIFACT_FR

9. Press Button: Populate.. it should populate default Header and Lines mapping.
10. This you can updated as per your requirements.

Column Name                              Rec id         Position

HEADER
STATEMENT_NUMBER                 01              9
BANK_ACCOUNT_NUM              01              7
CONTROL_BEGIN_BALANCE     01              11
PRECISION                                     01              5
STATEMENT_DATE                       07             9
CURRENCY_CODE                        01            4
CONTROL_END_BALANCE         07           11

LINES
TRX_DATE                                      04            9
TRX_CODE                                     04            8
AMOUNT                                        04           17
PRECISION                                     04           5
BANK_TRX_NUMBER                  04          14
TRX_TEXT                                      04          12
CURRENCY_CODE                       04           4
EFFECTIVE_DATE                        04          11

11. Save
12. Recheck by :
13. Open the Bank Statement Mappings window.
14. Choose the one from the list of values.. if you see your TEST_EDIFACT that means this is created and now you can use it.

4. Mapping Data Structure: MT940:

1. Open the Bank Statement Mappings window.
2. If you want to review a mapping template, choose one from the list of values. Otherwise, cancel the list of values.
3. Create a new record for the new mapping template.
4. In the Name field, enter the name of the new mapping template. > MT940_NEW
5. In the Description field, enter the description for the new mapping template.>SWIFT940_NEW
6. Control File: select: SWIFT940 **
7. DATE FORMAT: YYMMDD
8. TIME STAMP FORMAT :
9. BANK FILE FORMAT TYPE:MT940
10. Press Button: Populate.. it should populate default Header and Lines mapping.
11. This you can updated as per your requirements.

** there is no MT940.ctl file exist. You have to use: swift940.ctl

Column Name                                Rec id       Position Format

HEADER

STATEMENT_NUMBER                28 1
BANK_ACCOUNT_NUM              25 1
CONTROL_BEGIN_BALANCE     60 5
STATEMENT_DATE                       62 3
CURRENCY_CODE                        60 4
CONTROL_END_BALANCE         62 5

LINES
TRX_DATE                                       61 1
BANK_ACCOUNT_TEXT              61 9
AMOUNT                                         61 5
CUSTOMER_TEXT                         61 1 /BENM/NAME/(~)
BANK_TRX_NUMBER                  61A 7
TRX_TEXT                                      61A 1
TRX_CODE                                     61 6
CURRENCY_CODE                       61A 1 /OCMT/(aaa)
Exchange_Rate                                 61A 1 /EXCH(aaa)
INVOICE_TEXT                             61A 1 /INV/(~),/REMI/(~)
ORIGINAL_AMOUNT                   61A 1 /OCMT/EUR(~),…. MORE SUCH IF ANY
CHARGES_AMOUNT                    61A 1 /CHG1/EUR(~), ….. MORE SUCH IF ANY

12. Save
13. Recheck by:
14. Open the Bank Statement Mappings window.
15. Choose the one from the list of values.. if you see your MT940_NEW that means this is created and now you can use it.

5. Mapping Data structure: Non Standard Format eg.: Bank Statement in format CBI (Italian format), etc.

The Non Standard bank statement can be uploaded into Cash Management using a custom loader program. First, create your custom loader program. Then:
1. Run the custom loader program against the bank statement file to populate the Bank Statement Open Interface tables.
2. Verify that the bank statement information was loaded into the open interface tables correctly.

You can use the Bank Statement Interface window to ensure that bank statement header information is correctly inserted into the CE_STATEMENT_HEADERS_INT table.
You can use the Bank Statement Lines Interface window to ensure that bank statement transaction lines are correctly inserted into the CE_STATEMENT_LINES_INTERFACE table.

The Bank Statement Interface and Bank Statement Lines Interface windows show you the statement header and lines information in the open interface tables. You can correct the information using these windows, or you can correct the original bank statement file and reload it using your custom loader program.

Bank Account Access Options And Bank Account Grant Access Organizations

A1: When we define Bank account we assign: Bank Account: Account Owner and Use

1. Bank Account Owner and Use:
1). Bank Account Owner: The name of the source account holder.
2). Account Use: Select the checkboxes that apply to your account: Payables, Payroll,
Receivables, Treasury.

2. Bank Account: Account Access:
If the bank account has to be defined for specific OU then we Navigate to:
Bank Account: Organization Access:ADD Organizatio Access:
to Assign the Bank account to several organizations (for example ORG1 and ORG2)

Also here in options for each organizations assign a different cash account. For example for ORG1 enter FF1, for ORG2 enter FF2.
– (N) Cash Management> Setups> Bank Account >query the internal bank account.
– Update Bank Account > select “Account Access”, here you have: Access and Options

3. Update Bank Account: Account Access: particular Operating Unit.
Assign this account to several organizations (for example ORG1 and ORG2)
under Account Access.

4. Bank Account: Account Access Options:
In options for each of the organizations assign / setup:

(1). Bank Account: Account Access Options for: Payable Option:

1). Payables Options, following additional information is setup:
Cash, Bank Charges, Realized Gain , Future Dated Payment, Payment Document Category
Cash Clearing, Bank Errors , Realized Loss, Default Settlement Account, Payment Document Category

2). Payment Document Categories by Payment Method

Payment Method Code
Payment Document Category

(2). For Receivables Options, following additional information is setup:

1). GL Accounts:
Manage Bank Accounts > Manage Bank Accounts > Account Access >
Cash, Cash Clearing , Bank Charges, Factored Receipts, BR Short Term Debt, Unidentified Receipts
Confirmed Receipts, Remitted Receipts, AR Short Term Debt, Unapplied Receipts
On-Account Receipts

2). Receivables Activities
Unearned Discounts, Claim Investigations, Earned Discounts, Receivable Activity

(3). For Oracle Treasury Enabled Bank account following additional information is setup:
1). Treasury Options
Default Account Flag , Default Settlement Account, Pricing Model , Cash Account ,
Funding Limit Code , Authorized for Deal Entry, Portfolio Code , Settlement Script
Investment Limit Code

2). Intermediary Bank Accounts
Country, City , Branch Number , Account Number , IBAN , Bank Name , Bank Code , BIC
Check Digits , Comments
—————————————————————————-
A2: Wherever these bank accounts are used the Setup data is used for that Organization of the Bank account.
——————————————————————

A3:
Q3: Bank accounts can be given Organization Access to other organizations under any Legal Entities as long as those Legal Entities are owned by the same Ledger of the Legal Entity that owns the bank account? What it means?

Yes..The LE and OU’s are indirectly tied with the ledger.. The ledger of the operating unit must be same as the primarly ledger of the Legal entity. Additionally, the user should have access to the operating units.

A4:
Q4. why an Organization is not listed in the LOV of the Bank Account Grant Access Organizations window? Is it because the LE owning the bank account has its Ledger and that ledger do not have other OU since those are not linked to this ledger?

Yes, that is one reason.. another reason could be that user might not have access to that OU.

A5: Results in excel with column headings:
select * from xle_fp_ou_ledger_v;
also screen shots to show under which legal entity you are not able to see an OU by giving a specific example.

Cash Management Overview

Cash Management Overview

————————–

Cash Management is designed  to manage and control cash cycle.

This allows you to enter bank statements and reconcile the statement transactions against payments in Accounts Payable (AP), receipts in Accounts Receivable (AR), and journal entries in General Ledger. Cash Management (CE) can also reconcile payments and receipts from external/legacy systems. CE can also create and reconcile misc. transactions to record bank transaction entries not entered in AP or AR.

Integration with AR and AP

—————————

You use CE to reverse, clear, reconcile receipts,and create misc transactions for bank transactions not entered in AP or AR.

All these functions are shared and integrated with AR.

You can only use all the CE functions when AP and AR are fully installed.

You must have AR fully installed if you want to record MISC payments.

If you use CE with AP and AR, you must define the system parameters, such as banks, cash clearing, bank charges, and bank errors in AP.

Then define system parameters, such as remittance and cash accounts for each bank, and create misc transactions in AR. When this is done all the functionality in CE can be utilised.

Miscellaneous transactions are misc receipts and misc payments.

Misc payments are handled as negative misc receipts in AR, rather than as payments in AP.

The Allow Reconciliation Accounting option must be enabled if you want to use a cash clearing account and create accounting entries for bank charges, errors, gain/losses. If not enabled, you can still reconcile payments in CE, but accounting entries will not be created in GL.

Cash Management System Parameters

———————————-

Select Set of Books  and enter a date for the begin date.

Then check the following if required:

· Check Add Lines to Automatic Statements checkbox if you want to add lines to automatically loaded bank statements.

· Check Show Cleared Transactions checkbox if you want CE to display the cleared transactions that are available for reconciliation.

· Check Use Reconciliation Open Interface checkbox if you plan to use the open.                                                                                                                    interface to reconcile bank statement lines to transactions in external systems.

The following steps show you how to set up CE system parameters using the system parameters setup function. This is an example of a typical setup and options available:

· Select Setup. Then the System Parameters option.

· In the Set of Books field, specify the Set of Books name for CE transactions.

· In the Begin Date field, specify the date of the first bank statement entered.

· Check Show Cleared Transactions checkbox to display cleared transactions

available for reconciliation. Optional.

· Check Add Lines to Automatic Statements checkbox to add lines to automatically loaded bank statements. Optional.

· Check Use Reconciliation Open Interface checkbox to reconcile transactions in external systems. Optional .

· In the General region*, specify a tax code that applies to misc payments in the Liability field.

· Specify a tax code that applies to misc receipts in the Asset field.

· The Receivable Activity field is the default for the Misc Receipts window and the default receivable activity to which Auto-Reconciliation charges differences between the amount cleared and the original amount for remittance batches.

· In the Float Handling field, select Ignore if you want to reconcile statement lines with the effective dates later than the current date.

· Access the Automatic Reconciliation* region from the drop down list.

The Automatic Reconciliation region screen appears. You can accept the defaults if you wish.

· Then commit..

* The General parameters options are also used in the manual reconciliation windows and can be overridden.

* The Automatic reconciliation region has no effect when doing manual reconciliation.

Defining Bank Transaction Codes

——————————–

Auto-Reconciliation being used to load electronic bank statements.

CE requires transaction codes to be defined that are used by the bank.

This allows CE to identify the different transaction types on the statement, such as receipts, and payments. i.e:

· Misc Receipt

· Misc Payment

· NSF

· Payment

· Receipt

· Rejected

· Stopped

If you use a transaction code for both misc transactions and correcting statement errors, you can specify the sequence of matching.

Choose from the LOV to indicate how to use the bank transaction code.

· Misc

· Stmt

· Misc, Stmt

· Stmt, Misc

The following shows you how to define bank account transaction codes :

· Select  Setup. Then: Bank Transaction Codes.

· Select the bank you require. Click the OK button.

· The Bank Transaction Codes window appears. In Type field select a transaction type for the transaction code you are defining.

· In Code Field, specify the code used by the bank. I.e. 100.

· In the Description field, specify a description for the transaction code you are defining. i.e. payment.

· Enter start and end dates.

· In the Float Days field, enter the number of days that CE will add to the statement date to create an effective date for the transactions.

· Optional, select a transaction source for payments and receipts transactions.

· Use the Matching Against field to determine the order of matching and the type of transactions to match if the transaction Type is Misc Receipt or Misc Payment.

· In the Correction Method field, select the correction method your bank uses when correcting errors.

· Select the Create checkbox if you want to create misc transactions for any payments or receipts reported on the bank statement when no transaction code is provided. If you select the Create checkbox, specify the activity type and the payment method you want to assign to misc transactions.

· Commit.

Defining Automatic Reconciliation Parameters

——————————————–

The automatic reconciliation parameters controls several aspects of Auto-Reconciliation, this allows reconciliation of bank statements automatically. These options have no effect on manual reconciliations.

The tolerances region includes a variance amount and a variance percent,they are used by Auto-Reconciliation to match bank statement lines from AR and AP. If a transaction amount falls within ranges defined in the tolerance region, plus or minus the reconciliation tolerance, a match is made.

The Tolerance Differences – AP field controls whether the Auto-Reconciliation posts differences to bank charges or errors account.

The Tolerance Differences – Foreign field controls how Auto-Reconciliation handles bank errors or differences that are caused by exchange rates from foreign currency transactions in a multi-currency bank account.

There are 3 ways to treat the tolerances:

Gain/Loss: which takes the difference as exchange rate gain or loss;

Charges/Errors: which handles differences as bank charges or a bank errors, and No Action: in this case no reconciliation will occur and the statement line will have a status of error.

Automatic document sequence numbers. CE creates the document number for the statement automatically. You can’t change an automatically assigned document number.

Loading Bank Statement Info

—————————

To load bank statement information, including header information and line information, into CE select the Bank Statement Interface window and the Bank Statement Lines Interface window to load the information.

The table used is called :- CE_STATEMENT_HEADERS_INT_ALL

Validating payment information. The information loaded, including the bank statement header validation, control total validation, statement line validation, and multi-currency validation, go into the bank statement open interface tables.

If the data does not pass the validations and cannot be imported into the main tables the totals can modified, and then reconciled successfully.

The data can be imported and automatically reconciled at the same time.

It is just a case of navigating to the SRS window and submit a request for the Bank Statements Import and Auto-Reconciliation program.

The system generates the result and prints an Auto-Reconciliation Execution Report.

The following steps show you how to import a bank statement without Auto-Reconciliation in CE.

· Click the Bank Statement Import* option to request the report in SRS.

Click the OK button.

· The Parameters window appears. Specify the bank account number for the statement you want to import in the Bank Account Number field. Specify the range of statement numbers in the Statement Number From and To fields.

Enter dates for the Statement Date From and Statement Date To fields.

In the GL Date field, enter the GL date. Click the OK button.

* If Auto Reconciliation is required then select the Bank Statements Import and Auto-Reconciliation program

Interface errors occur for various reasons.

There are 3 common causes of bank statement interface errors:

Erroneous information in setup of CE, errors in bank transmission file, and more commonly problems with the SQL* Loader file.

Reviewing Bank Statement Errors

——————————–

The following steps show you how to review bank statement interface errors.

· Select Bank Reconciliation. Click the Bank Statement Interface option.

· Query the bank statement with the interface errors you want to review.

The applicable fields populate according to the account number you have specified. Click the Lines button to see the statement line detail.

· The Bank Statement Interface Lines window appears. Select the line with the error(s) review. Click the Errors button.

· The Bank Statement Interface Line Errors window appears, displaying the error message for the line selected. When finished reviewing the error messages, click the Done button.

Modifying the Statement Interface Table

—————————————-

The following explains how to modify the bank statement interface tables.

· Select Bank Statement. Click Bank Statement Interface. Query the bank statement that requires modification. Edit the bank statement and header transaction information as necessary, including the control balances, in the Opening field.

· Edit the receipts in the Receipts field. Edit the payments in the Payments field. Edit the closing number in the Closing field. Click the Lines button.

· Bank Statement Interface Lines window appears. Edit the bank statement transaction information. CE allows the editing of the line number, transaction code, transaction number, and date.

· In the Amounts region, CE allows the editing of the transaction amount, bank charges, and original amount.

· In the Exchange region, CE allows the editing of the exchange rate information, including currency code, exchange rate type, date, and exchange rate.

· In the Reference region, CE allows the editing of the agent (customer or supplier), invoice number, and  the agent bank account associated with each line.

· In the Description region, CE allows the editing of the effective date and description for each line.

· Commit.

Auto-Reconciliation and Manual Reconciliation

———————————————-

The Auto Reconciliation program fully matches AP AR GL and MISC transactions against bank statement lines if the transactions meet the following criteria:

· AR Remittance batches such as direct debit batches, the remittance batch deposit number or receipt batch name matches statement line transaction number, and the amount is within the tolerance.

· MISC Reversal corrections, the statement line has the same transaction number, same amount, and opposite transaction type (for example, misc receipt and misc payment)

· MISC Adjustment corrections, the statement line has the same transaction number, and has a transaction type of payment, receipt, misc payment, or misc receipt, and the net amount is within tolerance.

· AR Detail remittance transactions, NSF, and rejected transactions, the matching sequence is the same; match is against the invoice number and customer bank account, and then just the invoice number, and finally the

receipt number; the amount is within the  tolerance.

· AR NSF or rejected transactions, the receipt must first be reversed in AR

· MISC Receipt number matches the statement line transaction number

· AR GL AP MISC Transaction currency is the same as the bank statement line currency

· AP Payment batches, such as EFT batches, the reference or payment batch name matches the statement line transaction number, and  the amount is within tolerance.

· AP Detail payment transactions and stopped transactions, the matching sequence is the same; the program tries to match against the invoice number and supplier bank account, then just the invoice number, and finally the payment number; the amount is within the  tolerance

· AP For stopped transactions, the payment must first be stopped or voided.

· GL Journal line is posted

· GL Journal line accounting flexfield matches the bank account Asset Accounting flexfield

· GL Journal line description matches the statement line transaction number

*Bank statements can be entered Manually see the CE manuals.

*Bank statements can also be updated even if they are reconciled by

using the review button The Auto-Reconciliation program does four validations, bank statement header validation, control total validation, statement line validation, and multi-currency validation. The  program requires the imported bank statement header information has the bank account defined as an internal bank account. If a currency code is in the header, it must be the same as the currency code defined for the bank account in AP or AR.

There is an Auto-Reconciliation execution report.

It is printed automatically when the Auto-Reconciliation program is ran, and can be reviewed for all import validation errors for a statement or a specific statement line.

*After reconciling a payment, CE will generate journal entries as debit cash clearing accounts and credit cash accounts.

Bank statement lines must include an exchange rate when the transaction currency is foreign. Depending on the bank account currency, the Auto Reconciliation program handles statement line matching to transactions differently. If the bank account currency is foreign, the bank statement line amount must match the transaction line amount within the tolerance limit for automatic validation to be successful. If the difference exceeds the tolerance amount, CE displays an error message and  manual reconciliation must take place.

The tolerance validation is done in two stages, firstly it converts the tolerance into the bank account currency using the bank statement exchange rate, and it verifies the difference between the statement line amount and the transaction amount is within the tolerance.

The second stage uses the original tolerance amount to compare the bank statement line amount with the transaction amount (functional currency), verifying again that the difference is within the tolerance.

The Auto Reconciliation program references the tolerances that have been defined for example.

The tolerances defined are: amount = GBP £70 and percent = 10%.

Run the Auto Reconciliation program, and it encounters a statement line with an amount of GBP £1,000. The program firstly calculates the tolerance percentage amount (10% of GBP £1,000 = GBP £100). The program selects the smallest of the amounts and the defined tolerance amount which is GBP £70.

The program then matches the statement line against a payment or receipt transaction in the range GBP £930 to GBP £1,070, which is GBP £1,000 plus or minus GBP £70.

Regardless of bank account and transaction currency, the tolerance amount in the system parameters is always in the functional currency.

NOTE : Reconciled transactions can also be unreconciled as can statement lines.

Control Total Validation

————————–

Some banks supply statements that have control totals in the statement or account header for error checking . The bank statement header interface table has 7 control total columns. If you enter values in any of these, the Auto-Reconciliation uses the values for validation, except for CONTROL_BEGIN_BALANCE, which is the statement opening balance.

Other control total validations are:

· CONTROL_END_BALANCE  the statement closing balance.

· CONTROL_TOTAL_DR  the payment control total in the statement header this must match the total payment amount on the statement lines.

· CONTROL_TOTAL_CR the receipt control total in the statement header must match the total receipt amount on the statement lines.

· CONTROL_DR_LINE_COUNT  the number of payment lines in the statement must match the payment line control total in the statement header.

· CONTROL_CR_LINE_COUNT  the number of receipt lines in the statement must match the receipt line control total in the statement header

· CONTROL_LINE_COUNT the number of lines in the statement must match the line control total in the statement header

Statement Line Validation

————————-

Auto-Reconciliation needs the imported bank statement line to pass these checks.

The currency code on the statement line must be defined in the system.

The bank transactions code must be defined.

The exchange rate type must be of  a valid type defined.

The amount must be entered for the statement.

Multi-Currency Validation

————————-

Auto-Reconciliation checks each statement line for valid currency, and exchange rate type. When a matching transaction is found during reconciliation several checks are made.

For transactions, the bank statement line currency must be the same as the transaction currency (the bank statement currency can differ).

For batches, all transactions in the batch must use the same currency and statement line currency as the batch currency.

For both transactions and batches, if User exchange rates, each transaction must include the exchange rate, and the rate type must be User.

If it is for predefined rates, such as Corporate or Spot, each transaction must include the rate type and exchange rate date.

Incorrect CE setup information can include wrong transaction codes, currency, or bank account information. The solution is update CE setup information and then run the Auto-Reconciliation Import program again.

For bank transmission errors, the solution is to obtain a correct transmission file from the bank or alternatively update the statement using the Bank Statement Interface window. For SQL Loader file problems, modify Loader file and then rerun the Auto-Reconciliation Import program.

In CE you can also create AR transactions and AP transactions and you can also reverse these transactions.

When NSF or rejected receipts appear you can handle them manually by reversing the receipt manually and then reconciling the reversed receipt.

When a stopped or voided payment is done then void the payment in AP to mark the supplier invoice as unpaid then reconcile the stopped/voided payment statement line to the voided payment

Automatic Clearing

——————-

You can use CE or Automatic Clearing to clear receipts, as well as payments using the future-dated payment method. If you use Automatic Clearing within AR, you can choose to clear remitted receipts after they have matured.

If you use Automatic Clearing to clear payments and receipts rather than using CE, the GL balance may not match CE reports.

In AP, a future dated payment instructs the bank to disburse funds to the supplier’s bank on specific dates. If you use the AP Automatic Clearing for Future Payments utility to clear future-dated payments

made with the future-dated payment method, AP performs the accounting for cash clearing. You can match payments cleared in this manner with bank statement lines.

CE and Multi-Org

—————-

For each operating unit you must set up a CE responsibility very similar to what is setup in AP.

Each responsibility is tied to a SOB and an operating unit.

FAQ

—–

Q. How do we treat a BAC’s payment that has been reconciled and then voided

AP three payments: £10,£20, £30

Bank statement1    £60

It has been reconciled in full.

Now  need to void an amount of £10 therefore unreconcile £60 and reconcile £50 leaving £10 as unreconciled/available. Void payment of 10 ok.

Bank statement states the -£10 amount for the voided payment they have this as stopped and reconcile the voided amount in AP to the -£10.

Problem is cannot have the original unreconciled as stopped as it is part of the £60 how can this be  reconciled ? There are lines on two statements so  cannot contra each other?

A. The correct way this should have been dealt with in this situation is:

The voided element of -£10 on the bank statement should in fact be treated as an misc receipt. Then create a no tax invoice for £10 in AR and match this to the receipt of £10. The distribution can go to e.g. pay on a/c or suspense. Then in AP create an invoice for £10 use the same distribution as used for the AR invoice so they nett off to each other. Pay the invoice in AP as you would normally.

In this case as they voided the payment create a dummy statement.

It can then be reconciled using the stopped functionality.

Q Why are there no entries going to GL?

A  Check they are using reconcilition accounting . Accounting entries below With Reconcilitaion Accounting enabled:-

One set of entries for the unreconciled transaction

DR liability

CR cash clearing

One set of entries for the reconciled transactions

DR cash clearing

CR cash

If  Allow Reconciliation is not enabled, only one entry is created:

DR Accounts Payable

CR cash

Q  Unable to select available Journal Entries to reconcile a bank Statement.

A You must define a bank account with default GL account codes.

One of which must be your cash account.

CE will only look for journals with the GL account code that matches

the cash account of the b

Oracle Apps – Financials – Get Banks and Bank Branches Data

Very valuable query.

SELECT

BankOrgProfile.HOME_COUNTRY Bank_Home_Country,

BankOrgProfile.PARTY_ID Bank_Party_Id,

BankOrgProfile.ORGANIZATION_NAME Bank_Name,

BankOrgProfile.ORGANIZATION_NAME_PHONETIC Bank_Name_Alt,

BankOrgProfile.KNOWN_AS Short_Bank_Name,

BankOrgProfile.BANK_OR_BRANCH_NUMBER Bank_Number,

BranchParty.PARTY_ID Branch_Party_Id,

BranchParty.PARTY_NAME Bank_Branch_Name,

BranchParty.ORGANIZATION_NAME_PHONETIC Bank_Branch_Name_Alt,

BranchOrgProfile.BANK_OR_BRANCH_NUMBER Branch_Number,

BranchCA.start_date_active Start_Date,

BranchCA.end_date_active End_Date,

BranchParty.address1 Address_Line1,

BranchParty.address2 Address_Line2,

BranchParty.address3 Address_Line3,

BranchParty.address4 Address_Line4,

BranchParty.city City,

BranchParty.state State,

BranchParty.province Province,

BranchParty.postal_code Zip,

BranchParty.country Country,

BankCA.class_code Bank_Institution_Type,

BranchTypeCA.class_code Bank_Branch_Type,

BranchParty.mission_statement Description,

BranchCP.eft_swift_code EFT_Swift_Code,

BranchCP.eft_user_number EFT_User_Number,

EdiCP.edi_id_number EDI_ID_Number,

BranchParty.PARTY_ID

FROM   HZ_ORGANIZATION_PROFILES BankOrgProfile,

HZ_CODE_ASSIGNMENTS BankCA,

HZ_PARTIES BranchParty,

HZ_ORGANIZATION_PROFILES BranchOrgProfile,

HZ_CODE_ASSIGNMENTS BranchCA,

HZ_RELATIONSHIPS BRRel,

HZ_CODE_ASSIGNMENTS BranchTypeCA,

HZ_CONTACT_POINTS BranchCP,

HZ_CONTACT_POINTS EdiCP

WHERE   SYSDATE BETWEEN TRUNC (BankOrgProfile.effective_start_date)

AND  NVL (TRUNC (BankOrgProfile.effective_end_date),

SYSDATE + 1)

AND BankCA.CLASS_CATEGORY = ‘BANK_INSTITUTION_TYPE’

AND BankCA.CLASS_CODE IN (‘BANK’, ‘CLEARINGHOUSE’)

AND BankCA.OWNER_TABLE_NAME = ‘HZ_PARTIES’

AND (BankCA.STATUS = ‘A’ OR BankCA.STATUS IS NULL)

AND BankCA.OWNER_TABLE_ID = BankOrgProfile.PARTY_ID

AND BranchParty.PARTY_TYPE = ‘ORGANIZATION’

AND BranchParty.status = ‘A’

AND BranchOrgProfile.PARTY_ID = BranchParty.PARTY_ID

AND SYSDATE BETWEEN TRUNC (BranchOrgProfile.effective_start_date)

AND  NVL (

TRUNC (

BranchOrgProfile.effective_end_date

),

SYSDATE + 1

)

AND BranchCA.CLASS_CATEGORY = ‘BANK_INSTITUTION_TYPE’

AND BranchCA.CLASS_CODE IN

(‘BANK_BRANCH’, ‘CLEARINGHOUSE_BRANCH’)

AND BranchCA.OWNER_TABLE_NAME = ‘HZ_PARTIES’

AND (BranchCA.STATUS = ‘A’ OR BranchCA.STATUS IS NULL)

AND BranchCA.OWNER_TABLE_ID = BranchParty.PARTY_ID

AND BankOrgProfile.PARTY_ID = BRRel.OBJECT_ID

AND BRRel.RELATIONSHIP_TYPE = ‘BANK_AND_BRANCH’

AND BRRel.RELATIONSHIP_CODE = ‘BRANCH_OF’

AND BRRel.STATUS = ‘A’

AND BRRel.SUBJECT_TABLE_NAME = ‘HZ_PARTIES’

AND BRRel.SUBJECT_TYPE = ‘ORGANIZATION’

AND BRRel.OBJECT_TABLE_NAME = ‘HZ_PARTIES’

AND BRRel.OBJECT_TYPE = ‘ORGANIZATION’

AND BRRel.SUBJECT_ID = BranchParty.PARTY_ID

AND BranchTypeCA.CLASS_CATEGORY(+) = ‘BANK_BRANCH_TYPE’

AND BranchTypeCA.PRIMARY_FLAG(+) = ‘Y’

AND BranchTypeCA.OWNER_TABLE_NAME(+) = ‘HZ_PARTIES’

AND BranchTypeCA.OWNER_TABLE_ID(+) = BranchParty.PARTY_ID

AND BranchTypeCA.STATUS(+) = ‘A’

AND BranchCP.owner_table_name(+) = ‘HZ_PARTIES’

AND BranchCP.owner_table_id(+) = BranchParty.party_id

AND BranchCP.contact_point_type(+) = ‘EFT’

AND BranchCP.status(+) = ‘A’

AND EdiCP.owner_table_name(+) = ‘HZ_PARTIES’

AND EdiCP.owner_table_id(+) = BranchParty.party_id

AND EdiCP.contact_point_type(+) = ‘EDI’

AND EdiCP.status(+) = ‘A’

AND BankOrgProfile.ORGANIZATION_NAME = ‘ELC-Audi Bank – Syria’

SELECT               BankOrgProfile.HOME_COUNTRY Bank_Home_Country,            BankOrgProfile.PARTY_ID Bank_Party_Id,            BankOrgProfile.ORGANIZATION_NAME Bank_Name,            BankOrgProfile.ORGANIZATION_NAME_PHONETIC Bank_Name_Alt,            BankOrgProfile.KNOWN_AS Short_Bank_Name,            BankOrgProfile.BANK_OR_BRANCH_NUMBER Bank_Number,            BranchParty.PARTY_ID Branch_Party_Id,            BranchParty.PARTY_NAME Bank_Branch_Name,            BranchParty.ORGANIZATION_NAME_PHONETIC Bank_Branch_Name_Alt,            BranchOrgProfile.BANK_OR_BRANCH_NUMBER Branch_Number,            BranchCA.start_date_active Start_Date,            BranchCA.end_date_active End_Date,            BranchParty.address1 Address_Line1,            BranchParty.address2 Address_Line2,            BranchParty.address3 Address_Line3,            BranchParty.address4 Address_Line4,            BranchParty.city City,            BranchParty.state State,            BranchParty.province Province,            BranchParty.postal_code Zip,            BranchParty.country Country,            BankCA.class_code Bank_Institution_Type,            BranchTypeCA.class_code Bank_Branch_Type,            BranchParty.mission_statement Description,            BranchCP.eft_swift_code EFT_Swift_Code,            BranchCP.eft_user_number EFT_User_Number,            EdiCP.edi_id_number EDI_ID_Number,            BranchParty.PARTY_ID     FROM   HZ_ORGANIZATION_PROFILES BankOrgProfile,            HZ_CODE_ASSIGNMENTS BankCA,            HZ_PARTIES BranchParty,            HZ_ORGANIZATION_PROFILES BranchOrgProfile,            HZ_CODE_ASSIGNMENTS BranchCA,            HZ_RELATIONSHIPS BRRel,            HZ_CODE_ASSIGNMENTS BranchTypeCA,            HZ_CONTACT_POINTS BranchCP,            HZ_CONTACT_POINTS EdiCP    WHERE   SYSDATE BETWEEN TRUNC (BankOrgProfile.effective_start_date)                        AND  NVL (TRUNC (BankOrgProfile.effective_end_date),                                  SYSDATE + 1)            AND BankCA.CLASS_CATEGORY = ‘BANK_INSTITUTION_TYPE’            AND BankCA.CLASS_CODE IN (‘BANK’, ‘CLEARINGHOUSE’)            AND BankCA.OWNER_TABLE_NAME = ‘HZ_PARTIES’            AND (BankCA.STATUS = ‘A’ OR BankCA.STATUS IS NULL)            AND BankCA.OWNER_TABLE_ID = BankOrgProfile.PARTY_ID            AND BranchParty.PARTY_TYPE = ‘ORGANIZATION’            AND BranchParty.status = ‘A’            AND BranchOrgProfile.PARTY_ID = BranchParty.PARTY_ID            AND SYSDATE BETWEEN TRUNC (BranchOrgProfile.effective_start_date)                            AND  NVL (                                    TRUNC (                                       BranchOrgProfile.effective_end_date                                    ),                                    SYSDATE + 1                                 )            AND BranchCA.CLASS_CATEGORY = ‘BANK_INSTITUTION_TYPE’            AND BranchCA.CLASS_CODE IN                     (‘BANK_BRANCH’, ‘CLEARINGHOUSE_BRANCH’)            AND BranchCA.OWNER_TABLE_NAME = ‘HZ_PARTIES’            AND (BranchCA.STATUS = ‘A’ OR BranchCA.STATUS IS NULL)            AND BranchCA.OWNER_TABLE_ID = BranchParty.PARTY_ID            AND BankOrgProfile.PARTY_ID = BRRel.OBJECT_ID            AND BRRel.RELATIONSHIP_TYPE = ‘BANK_AND_BRANCH’            AND BRRel.RELATIONSHIP_CODE = ‘BRANCH_OF’            AND BRRel.STATUS = ‘A’            AND BRRel.SUBJECT_TABLE_NAME = ‘HZ_PARTIES’            AND BRRel.SUBJECT_TYPE = ‘ORGANIZATION’            AND BRRel.OBJECT_TABLE_NAME = ‘HZ_PARTIES’            AND BRRel.OBJECT_TYPE = ‘ORGANIZATION’            AND BRRel.SUBJECT_ID = BranchParty.PARTY_ID            AND BranchTypeCA.CLASS_CATEGORY(+) = ‘BANK_BRANCH_TYPE’            AND BranchTypeCA.PRIMARY_FLAG(+) = ‘Y’            AND BranchTypeCA.OWNER_TABLE_NAME(+) = ‘HZ_PARTIES’            AND BranchTypeCA.OWNER_TABLE_ID(+) = BranchParty.PARTY_ID            AND BranchTypeCA.STATUS(+) = ‘A’            AND BranchCP.owner_table_name(+) = ‘HZ_PARTIES’            AND BranchCP.owner_table_id(+) = BranchParty.party_id            AND BranchCP.contact_point_type(+) = ‘EFT’            AND BranchCP.status(+) = ‘A’            AND EdiCP.owner_table_name(+) = ‘HZ_PARTIES’            AND EdiCP.owner_table_id(+) = BranchParty.party_id            AND EdiCP.contact_point_type(+) = ‘EDI’            AND EdiCP.status(+) = ‘A’