R12 AR Month End Close and Reconciliation

As requested by some of reader , Here are steps and checklist for R12 AR Month End Close and
Reconciliation.
1. Complete All Transactions for the Period Being Closed
2. Complete and review your unapplied receipts
3. Reconcile Receipts to Bank Statement Activity for the Period
4. Change period status to Close Pending
Navigate ..Control>Accounting>Open/Close Periods

Do not get confused with various status of periods:

 Closed: Journal entry, posting, and transaction entry are not allowed unless the accounting
period is reopened. Receivables verifies that there are no unposted items in this period.
Receivables does not let you close a period that contains unposted items.
 Close Pending: Similar to Closed, but does not validate for Unposted items. Journal entry,
posting, and transaction entry are not allowed unless the accounting period is reopened.
 Future: This period is not yet open, but you can enter transactions in this period. However, you
cannot post in this period until you open it.
 Not Opened: This period has never been opened and journal entry and posting are not allowed.
 Open: Journal entry and posting are allowed.
[Link] accounting
Due to the introduction of the Subledger Accounting Architecture in Release 12 this step is new, as it
enables you to create accounting for your completed transactions, which will be feed into General
[Link] it out old Notes (here, here, here )for SLA Process.
[Link] Unposted Items Report
You can review the Unposted Items Report to determine if any exceptions have been encountered, that
will need to be resolved, to ensure all accounting has been successful for all transactions.
[Link] Transaction Activity for the Period (Transaction & Receipt)
 Run Journal Entries Report
Transaction Register Total for Postable Items = Sales Journal by GL Account for the
Receivable Account Type (Total DR–Total CR)

 Receipt Register
 Receipt Journals Report

In theory this step is unchanged between Release 11i and Release 12. It’s just important to note that you
should not be trying to reconcile distributions on transactions to account balances, as these are not
necessarily the final accounting for these transactions.
[Link] that Receivables Receipts Balance by running the Receipt Journal report and the Receipt
Register for the same GL Date range.

2. Use the Receipt Journal to View information about Receipts that appear in your Journal
Entries report. Use the Receipt Register to Review a list of receipts for the date range that
you specify. The total of the Receipt Journal should equal the total of all receipts in the
Receipt Register. These reports display information about both Invoice–Related and
Miscellaneous Receipts.

[Link] outstanding customer balances

 Aging Report (last period)
 Transaction Register
 Adjustments Register
 Invoice Exceptions
 Applied Receipts Register
 Unapplied Receipts Register
 Aging Report (this period)
Most of these reports haven’t changed between Release 11i and Release 12, except that they look at the
SLA tables instead of the AR tables.
If you are coming from 11i to R12, you should note that there is change in Aging Repots(Aging - 7
Buckets ) Behaviour .
Few High Points of Changes are:[Adpoted User Guide]
 In Release11i, the Aging Reports pull the data directly from the ar_payment_schedules_all table
while in Release 12 the data is pulled from the SLA tables after when Create Accounting is run.
 In release 11i, all the transactions used to appear on all the Aging Reports.
 The transactions are classified into three sections, namely:
1. Those transactions, which were accounted for in GL through the GL interface
program.
2. Those transactions, for which accounting could not be generated because they were
created with the flag “Post to GL” as un-checked.
3. Those transactions, which were created with transaction type having the flag “Post to
GL” as checked but were however, not interfaced to GL.
[Link] Receivables transactions in General Ledger
[Link] AR and GL Balances
The following is a list of the Critical Reports required for Reconciliation between AR and GL
 Journal Entries Report (AR)
 Sales Journal by GL Account Report (AR)
 Receipt Journal Report (AR)
 Account Analysis Subledger Detail-180 Char (GL)
 Third Party Balance Report (New to R12)
 AR Reconciliation Report (AR)
The Total Activity in a period is calculated as follows:
Transaction Register for the Period
(-) Applied Receipts Register for the Period
(-) Un-Applied Receipts Register for the Period
(+) Adjustments Register for the Period
(-) Invoice Exceptions for the Period
(+) Rounding Differences for the Period
(+) Credit Memo gain/loss for the Period
[Link] AR Period
 Subledger Close Exception Period
From Receivables Responsibilities.
Navigate to Control --> Requests --> Run
Subledger Period Close Exceptions Report

 Change Period status to Closed

 Open next Period
That's all about R12 Month End Close and Reconciliation of AR.

AR to GL Transfer
Oracle Financials Accounts Receivables Transfer Process
The AR Transfer process is a SRS based Concurrent Process which can be used for transfer process
from
to GL . This Program knows as ARGLTP.
Posting Journals to GL
Your Navigation for transfer process would be
Interfaces> General ledger(11i)

You can run the General Ledger Interface program to transfer Receivables Transaction Accounting
Distributions to the GL
Interface table
(GL_INTERFACE) and
create either Detailed
Journal batches or
summary Journal batches
. Receivables lets you
create Unposted Journal
Entries in GL when you
run General Ledger
Interface.
Here is five simple step
to complete the process:
 1. Navigate to the Run General Ledger Interface window.
2. Choose the Posting Detail or Summary. Chose Detail in the Posting Detail field. If you transfer
transactions in detail, the General Ledger Journal Import Program creates at least one journal
entry for each transaction in your posting submission. (If you transfer in summary, it creates one
journal entry for each general ledger account)
3. Enter the GL Posted Date for this submission. The default is the current date, but you can
change it. Receivables updates all of the posted transactions that you transfer to the general
ledger interface area with the GL posted date you enter.
4. Enter the range of GL Dates for your submission. The dates must be within both an open
receivables period and an open or future General Ledger period. When you enter a start date, the
default GL end date is the last day of the period that you entered for the GL start date.
5. Receivables creates the Posting Execution Report. Use this report to see a summary of
transactions that are imported into the GL_INTERFACE table. Transactions that failed
validation appear in the Unposted Items Report.
A note on Posting Execution Report
You can use this report to view a Summary of all Transactions by category and currency that make up
your Entries to general ledger.
The good is that AR automatically generates this report when you run General Ledger Interface.
The sum of the entries in the General Ledger Journal Report is equal to the sum of all of the categories
of transactions that the Posting Execution Report includes for the same period. The report tells you if
posting discovered Errors in your Journals or in your Journal lines.
Vanila Oracle AR Transfer Process : Addressing user Need
AR to General ledger Transfer Program(ARGLTP) does not have any provision to run with specific
categories like Invoices, Receipts, etc.
In real time scenarios finance user need to transfer AR's datas outside the sales invoice category any
time. Because The sales invoice category can not be transferred before reconciliation with customer ,
So user need to transfer other datas outside of sales invoice category, thus they may request you for
category options for that you need to modify the General Ledger interface Program by adding some
new parameters Category From /Category To.
What Category we have in GL
 Trade Receipts
 Misc Receipts
 Adjustments
 Sales Invoices
 Debit Memos
 Chargebacks
 Credit Memos
 CM Applications
Sales Invoices ,Debit Memos ,Chargebacks and Credit Memos are tracked back with Customer Num
CM Applications are tracked backed with Inv Num
Trade Receipts or Misc Receipt are tracked back with Receipt Number.
Connecting World :Link between GL to AR
When you run AR Transfer to GL , GL_INTERFACE table get first populated with reference columns
that is then pushed the detailes in GL_JE_LINES table. Here are the details for Refrence columns.
Adjustments

 REFERENCE21 :posting_control_id
 REFERENCE22 :adjustment_id
 REFERENCE23 :line_id
 REFERENCE24 :trx_number
 REFERENCE25 :adjustment_number
 REFERENCE26 :cust_trx_type
 REFERENCE27 :bill_to_customer_id
 REFERENCE28 :ADJ
 REFERENCE29 :source_type prefixed by 'ADJ'
 REFERENCE30 :AR_ADJUSTMENTS

Transactions

 REFERENCE21 :posting_control_id
 REFERENCE22 :customer_trx_id
 REFERENCE23 :cust_trx_line_gl_dist_id
 REFERENCE24 :trx_number
 REFERENCE25 :cust.account_number
 REFERENCE26 :CUSTOMER
 REFERENCE27 :bill_to_customer_id
 REFERENCE28 :type(CM/DM/CB/INV)
 REFERENCE29 :type||account_class
 REFERENCE30 :RA_CUST_TRX_LINE_GL_DIST

Applications

 REFERENCE21 :posting_control_id
 REFERENCE22 :cash_receipt_id||receivable_application_id for CASH
/receivable_application_id for CM
 REFERENCE23 :line_id
 REFERENCE24 :receipt_number for CASH / trx_number for CM
 REFERENCE25 :trx_number if status = ¿APP¿ / NULL for
unapplied records
 REFERENCE26 :cust_trx_type
 REFERENCE27 :pay_from_customer for CASH / bill_to_customer_id for CM
 REFERENCE28 :application_type (TRADE or CCURR for CASH / CMAPP for
CM)
 REFERENCE29 :application_type||source_type
 REFERENCE30 :AR_RECEIVABLE_APPLICATIONS
 Bills Receivable

 REFERENCE21 :posting_control_id
 REFERENCE22 :transaction_history_id
 REFERENCE23 :line_id
 REFERENCE24 :trx_number
 REFERENCE25 :customer_Trx_id
 REFERENCE26 :cust_trx_type
 REFERENCE27 :drawee_id
 REFERENCE28 :cust_trx_type
 REFERENCE29 :BR_||source_type
 REFERENCE30 :AR_TRANSACTION_HISTORY

Key Tables
In order to obtain what is going to be tranfered to GL you can these Major AR table to drill down
information.
 ra_customer_trx_all: Transactions accounting
 ra_cust_trx_line_gl_dist_all: Transactions accounting
 ar_adjustments_all:Adjustments accounting
 ar_distributions_all:Adjustments accounting
 ar_cash_receipt_history_all:Receitps accounting
 ar_distributions_all: Receitps accounting
 ar_receivable_applications_all: Receipt applications accounting
 ar_distributions_all:Receipt applications accounting & misc receipts accounting
 ar_misc_cash_distributions_all:Misc receipts accounting
Query for Subledger Transfer to GL
If you want to get details of different journals transferred to GL, use this to get the result. You can also
fine tune with period , currency or clearing company code or Journal [Link] is the query for
Transaction.

SELECT gjjlv.period_name "Period Name"

, [Link] "Batch Name"
, gjjlv.header_name "Journal Entry For"
, gjjlv.je_source "Source"
,glcc.concatenated_segments "Accounts"
, NVL(gjjlv.line_entered_dr,0) "Entered Debit"
, NVL(gjjlv.line_entered_cr,0) "Entered Credit"
, NVL(gjjlv.line_accounted_dr,0) "Accounted Debit"
, NVL(gjjlv.line_accounted_cr,0) "Accounted Credit"
, gjjlv.currency_code "Currency"
, [Link] "Trx type"
, rcta.trx_number "Trx Number"
, rcta.trx_date "Trx Date"
, RA.CUSTOMER_NAME "Trx Reference"
, [Link] "Posting Status"
, TRUNC(gjh.DATE_CREATED) "GL Transfer Dt"
 , gjjlv.created_by "Transfer By"
FROM apps.GL_JE_JOURNAL_LINES_V gjjlv
, gl_je_lines gje
, gl_je_headers gjh
, gl_je_batches gjb
, ra_customer_trx_all rcta
, apps.ra_customers ra
, apps.gl_code_combinations_kfv glcc
, ra_cust_trx_types_all rctype
WHERE gjh.period_name IN ('OCT-2008','NOV-2008')
AND glcc.code_combination_id = gje.code_combination_id
AND gjh.je_batch_id = gjb.je_batch_id
AND gjh.je_header_id = gje.je_header_id
AND gjh.period_name = gjb.default_period_name
AND gjh.period_name = gje.period_name
AND gjjlv.period_name = gjh.period_name
AND gjjlv.je_batch_id = gjh.je_batch_id
AND gjjlv.je_header_id = gjh.je_header_id
AND gjjlv.line_je_line_num = gje.je_line_num
AND gjjlv.line_code_combination_id = glcc.code_combination_id
AND gjjlv.line_reference_4 = rcta.trx_number
AND rcta.cust_trx_type_id = rctype.cust_trx_type_id
AND rcta.org_id = rctype.org_id
AND ra.customer_id = rcta.bill_to_customer_id
--and glcc.segment1 ='30D

Read This for AR to GL Transfer part 2

AR to GL Transfer
GL Transfer Can Capture the Fingerprints (Link between GL to AR )

When you run AR Transfer to GL

, GL_INTERFACE table get first
populated with reference
columns that is then pushed the
detailes in GL_JE_LINES table.
Here are the details for Reference
columns
 REFERENCE21 :posting_control_id
 REFERENCE22 :cash_receipt_id||cash_receipt_history_id or cash_receipt_id for MISC
 REFERENCE23 :line_id
 REFERENCE24 :receipt_number
 REFERENCE25 :null for CASH / cash_receipt_history_id for MISC
  REFERENCE26 :null
 REFERENCE27 :pay_from_customer
 REFERENCE28 :MISC / TRADE
 REFERENCE29 :MISC_source type or TRADE_source_type
 REFERENCE30 :AR_CASH_RECEIPT_HISTORY
Key Tables
In order to obtain what is going to be tranfered to GL you can these Major AR table to drill down
information.
 ar_cash_receipt_history_all:Receitps accounting
 ar_distributions_all: Receitps accounting
 ar_receivable_applications_all: Receipt applications accounting
 ar_distributions_all:Receipt applications accounting & misc receipts accounting
 ar_misc_cash_distributions_all:Misc receipts accounting
Query for Subledger Transfer to GL
If you want to get details of different journals transferred to GL, use this to get the result. You can also
fine tune with period , currency or clearing company code or Journal [Link] is the query for
Transaction.

SELECT
gjjlv.period_name "Period"
, [Link] "Batch name"
, gjjlv.header_name "Journal Entry For"
, gjjlv.je_source "Source"
, glcc.concatenated_segments "Accounts"
, gjjlv.line_entered_dr "Entered Debit"
, gjjlv.line_entered_cr "Entered Credit"
, gjjlv.line_accounted_dr "Accounted Debit"
, gjjlv.line_accounted_cr "Accounted Credit"
, gjjlv.currency_code "Currency"
, [Link] "Payment Method"
, acra.receipt_number "Receipt Num"
, acra.receipt_date "Receipt Date"
, RA.CUSTOMER_NAME "Reference"
, gjjlv.created_by "Gl Transfer By"
FROM apps.gl_je_journal_lines_v gjjlv
, gl_je_lines gje
, gl_je_headers gjh
, gl_je_batches gjb
, ar_cash_receipts_all acra
, apps.ra_customers ra
, apps.gl_code_combinations_kfv glcc
, ar_receipt_methods arm
WHERE gjh.period_name IN ('OCT-2007','NOV-2007')
AND glcc.code_combination_id = gje.code_combination_id
AND gjh.JE_BATCH_ID = gjb.JE_BATCH_ID
AND gjh.JE_HEADER_ID = gje.JE_HEADER_ID
AND gjh.period_name = gjb.default_period_name
AND gjh.period_name = gje.period_name
AND gjjlv.period_name = gjh.period_name
AND gjjlv.je_batch_id = gjh.je_batch_id
AND gjjlv.je_header_id = gjh.je_header_id
AND gjjlv.LINE_JE_LINE_NUM = gje.je_line_num
AND gjjlv.line_code_combination_id = glcc.code_combination_id
AND gjjlv.line_reference_4 = acra.receipt_number
AND ra.customer_id = acra.pay_from_customer
AND acra.receipt_method_id = arm.receipt_method_id
AND gjjlv.SUBLEDGER_DOC_SEQUENCE_VALUE = acra.DOC_SEQUENCE_VALUE
AND gjjlv.SUBLEDGER_DOC_SEQUENCE_id = acra.DOC_SEQUENCE_ID
--and glcc.segment1 ='30D'

Run ARGLTP module: General Ledger Transfer Program

If your program is running bit
slow , you can run this in debug
mode and investigate why
performance is done.
Based out of experince , the
performance hit would be related to
the number of records. If you are
trying to determine why records are
not imported you could run a smaller
range than a week and run that in
degbug mode. If you are trying to
determine performance issues the
debug is likely not going to help.
Here are the steps to run in the debug mode.
1)Go to Run General Ledger Interface Screen:
Interfaces -> General Ledger
2) Activate DEBUG_FLAG parameter:
Menu: Help -> Tools -> Examine
Block: Control
Field: DEBUG_FLAG
Value: Y
3) Run ARGLTP module: General Ledger Transfer Program.
After activating the debug mode the ARGLTP log file will show you more detailed information about
this process. In you are unable to debug you can ask Oracle support they will help you in addressing
slow performance.
Hope this will be helpful for rootcausing any issue during month end or addressing daily
[Link] back soon for PO and PRJ transfer details.
AND gjjlv.je_header_id = gjh.je_header_id
AND gjjlv.LINE_JE_LINE_NUM = gje.je_line_num
AND gjjlv.line_code_combination_id = glcc.code_combination_id
AND gjjlv.line_reference_4 = acra.receipt_number
AND ra.customer_id = acra.pay_from_customer
AND acra.receipt_method_id = arm.receipt_method_id
AND gjjlv.SUBLEDGER_DOC_SEQUENCE_VALUE = acra.DOC_SEQUENCE_VALUE
AND gjjlv.SUBLEDGER_DOC_SEQUENCE_id = acra.DOC_SEQUENCE_ID
--and glcc.segment1 ='30D'

Run ARGLTP module: General Ledger Transfer Program

If your program is running bit
slow , you can run this in debug
mode and investigate why
performance is done.
Based out of experince , the
performance hit would be related to
the number of records. If you are
trying to determine why records are
not imported you could run a smaller
range than a week and run that in
degbug mode. If you are trying to
determine performance issues the
debug is likely not going to help.
Here are the steps to run in the debug mode.
1)Go to Run General Ledger Interface Screen:
Interfaces -> General Ledger
2) Activate DEBUG_FLAG parameter:
Menu: Help -> Tools -> Examine
Block: Control
Field: DEBUG_FLAG
Value: Y
3) Run ARGLTP module: General Ledger Transfer Program.
After activating the debug mode the ARGLTP log file will show you more detailed information about
this process. In you are unable to debug you can ask Oracle support they will help you in addressing
slow performance.
Hope this will be helpful for rootcausing any issue during month end or addressing daily
[Link] back soon for PO and PRJ transfer details.