AR Receipt and Bank Statement Reconciliation

When the AR Receipt is Created and Applied to the Invoice based on the receipt class setup for the receipt method used in the AR transaction the Receipt status will change to Remitted or cleared.
If the Receipt class Matching setup is labelled as By Matching : In that case the Cash receipt status will change to Remitted,
The cash receipt needs to be reconciled against a bank statement to clear it off.

In order for this to happen we need to

• make an entry for the bank statement
• Select the receipt from the list of available receipts available for reconciliation
• Once the bank statement is reconciled with the receipt the receipt status changes to Cleared and the bank statement line is reconciled.

Below SQL can be used to find out details of a AR cash receipt being reconciled with a bank statement

select cba.BANK_ACCOUNT_NAME,cba.bank_account_num,csh.statement_number,csl.STATUS,csl.amount,csl.trx_type,acr.receipt_number,arm.name

from CE_STATEMENT_HEADERS csh,

     ce_statement_lines csl,

     ce_bank_accounts  cba,

     ce_statement_reconcils_all csr,

     apps.ar_cash_receipts_all acr,

     ar_receivable_applications_all ara,

     ar_receipt_methods arm,

     hr_operating_units hou,

     AR_CASH_RECEIPT_HISTORY_ALL ach

where csh.STATEMENT_HEADER_ID = csl.STATEMENT_HEADER_ID

and     csh.BANK_ACCOUNT_ID = cba.BANK_ACCOUNT_ID

and csl.STATEMENT_LINE_ID = csr.STATEMENT_LINE_ID

and csr.reference_id = ach.CASH_RECEIPT_HISTORY_ID

and csh.statement_number = :statement_number

and bank_account_num = :bank_account_num

and csr.REFERENCE_TYPE = 'RECEIPT'

and acr.cash_receipt_id = ara.cash_receipt_id

      and acr.receipt_method_id = arm.receipt_method_id

      and ara.org_id = hou.organization_id

      and hou.name  = :operating_unit

      and receipt_number = :receipt_number

      and acr.cash_receipt_id = ach.cash_receipt_id  

Descriptive flexfields SQL to get DFF Details

Descriptive flex fields have always been used on Standard Oracle forms to provide the business additional fields for information representation.

From the front end information of the DFF can be seen from the Application Developer Responsibility

Flexfields --> Descriptive --> Register

For a technical developer if needed the DFF information can be checked from the Data base too via an SQL.

The below SQL gives the DFF details defined for AR Invoices :
Line transaction flexfield

select fdf.application_id,fdf.APPLICATION_TABLE_NAME,fdf.TITLE,fdfc.DESCRIPTIVE_FLEX_CONTEXT_CODE,fdfc.DESCRIPTION--fdfc.*
       ,col.APPLICATION_COLUMN_NAME,col.end_user_column_name,col.ENABLED_FLAG,col.FLEX_VALUE_SET_ID
from FND_DESCRIPTIVE_FLEXS_VL fdf,
     FND_DESCR_FLEX_CONTEXTS_VL fdfc ,
     FND_DESCR_FLEX_COL_USAGE_VL col
where fdfc.DESCRIPTIVE_FLEXFIELD_NAME =fdf.DESCRIPTIVE_FLEXFIELD_NAME
and upper(fdf.TITLE) like 'LINE TRANSACTION FLEXFIELD%'
and fdf.application_id = col.application_id
and col.DESCRIPTIVE_FLEXFIELD_NAME =fdf.DESCRIPTIVE_FLEXFIELD_NAME
and fdfc.descriptive_flex_context_code = col.descriptive_flex_context_code ;

Changing the Title we can get details of Order flexfield Structures too