Payables to General Ledger AP GL Reconciliation Process in Oracle Fusion

Payables to General Ledger AP GL Reconciliation Process in Oracle Fusion

 

In this post , we will discuss Payables(AP) and General Ledger(GL)  reconciliation in Oracle Fusion. We do reconciliation in Oracle applications to Match the Transactions between Sub-ledger and General ledger. In EBS , We need to do lot of efforts in AP and GL reconciliation to run many reports and then find the difference between AP and GL account. Some time we need to put lot of efforts to find the missing transactions between AP and GL.

 

Oracle fusion has made the reconciliation process so simple we don't need to run the many reports in Payables and General ledger and find the difference between them and then find the missing transactions. Oracle fusion has provided very simple process to do the reconciliation between sub-ledger and general ledger and we can easily find the missing transactions between the difference between AP and GL. This is really an great feature in Oracle fusion application.

 

Payables to General Ledger AP GL Reconciliation Process in Oracle Fusion

Step1:- Receivables and General Ledger AP GL Reconciliation in Oracle fusion

Log into Oracle Fusion Applications
Navigator-->Tools-->Schedule Process

 

 

 

 

Step2:- Step2:- Submit 'Prepare Payables to General Ledger Reconciliation'

 

This program extracts and prepares transaction and accounting information for reconciling Payables to the General Ledger.

 

We need to run this process before start the process of Payables to General Ledger Reconciliation.

Verify the process and sub-processes end with Status ‘Succeeded’

 

 

Write down the process ID and Request Name highlighted below for the Prepare Payables to General Ledger Reconciliation because we need this name and id in further reconciliation report.

 

 

 

Step3:- Run the Payables to General Ledger Reconciliation Report

Navigator > Payables  > Invoices

 

 

 

 From the Task List: Accounting > Payables to Ledger Reconciliation

Step4:- Review the Summary Reconciliation Results

 

 For any row that represents period activity (like Invoices, Payments, Prepayments), you can click on the Payables or Accounting Amount, to display the detail of such activity.

 

 

 

Payables to General Ledger AP GL Reconciliation Process in Oracle Fusion

 

Reconciliation of AP and GL in Oracle Apps R12

Reconciliation of  AP and GL in Oracle Apps R12

In this post , We will be discussed about Reconciliation of  AP and GL in Oracle Apps R12. We will be discuss about reconciliation method uses in oracle apps r12 to reconcile the ap and gl. We will go step by step for the Reconciliation of  AP and GL in Oracle Apps R12. In AP and GL reconciliation , we do reconcile the liability accounts balances between the ap and gl. Here below is the detail explanation about Reconciliation of  AP and GL in Oracle Apps R12.

In Friends , in this post I will share you the SQL queries by which you can easily reconcile from AP and GL in Oracle Apps R12.

Steps for the Reconciliation of  AP and GL in Oracle Apps R12

Step1:- First you need to run this below query from Oracle Apps DB for your AP Liability accounts.In this example I am doing reconciliation of my AP Liability Natural account 24103390 and you can select any dates of the month for which you want to do reconciliation.


select JE_SOURCE,sum(nvl(accounted_dr,0))-sum(nvl(accounted_cr,0)) Balanace from gl_je_headers a1,gl_je_lines a2,gl_code_combinations gcc
 where a1.JE_HEADER_ID=a2.JE_HEADER_ID
 and a2.CODE_COMBINATION_ID=gcc.CODE_COMBINATION_ID
 and gcc.segment5=24103390
 and effective_date between :p_from_date and :p_to_date
 and A2.status='P'
 GROUP BY JE_SOURCE

This above query will give you the source from which your data is coming in GL. You have to put Manual Invoice amount separate in the sheet to do the reco between AP and GL because manual entry will not come in AP and this entry is directly entered in GL so when you will do the reco you will always get a difference of that manual entry value but by this query you will know this difference.


Step2:- Now that you need to run this query with Account wise and put that in Excel.

select JE_SOURCE,GCC.CONCATENATED_SEGMENTS,sum(nvl(accounted_dr,0))-sum(nvl(accounted_cr,0)) Balanace from gl_je_headers a1,gl_je_lines a2,gl_code_combinations_kfv gcc
 where a1.JE_HEADER_ID=a2.JE_HEADER_ID
 and a2.CODE_COMBINATION_ID=gcc.CODE_COMBINATION_ID
 and gcc.segment5=24103390
 and effective_date between :p_from_date and :p_to_date
 and A2.status='P'
 GROUP BY JE_SOURCE,GCC.CONCATENATED_SEGMENTS



Step3:- Now run this query which will fetch the Payables/AP data for you Liability accounts.In this example I am doing reconciliation of my AP Liability Natural account 24103390 and you can select any dates of the month for which you want to do reconciliation.

 select CONCATENATED_SEGMENTS,sum(T_LEDGER_dr)-sum(T_LEDGER_Cr) bal from (
 SELECT
CASE WHEN
        XAL.PARTY_ID = (SELECT MAX(DUPLICATE_VENDOR_ID) FROM AP_DUPLICATE_VENDORS_ALL WHERE DUPLICATE_VENDOR_ID = XAL.PARTY_ID) THEN
        (SELECT MAX(VENDOR_ID) FROM AP_DUPLICATE_VENDORS_ALL WHERE DUPLICATE_VENDOR_ID = XAL.PARTY_ID)
        ELSE XAL.PARTY_ID
        END PARTY_ID,
        NVL(CASE WHEN
        XAL.PARTY_SITE_ID = (SELECT MAX(DUPLICATE_VENDOR_SITE_ID) FROM AP_DUPLICATE_VENDORS_ALL WHERE DUPLICATE_VENDOR_ID = XAL.PARTY_ID AND DUPLICATE_VENDOR_SITE_ID = XAL.PARTY_SITE_ID) THEN
        (SELECT MAX(VENDOR_SITE_ID) FROM AP_DUPLICATE_VENDORS_ALL WHERE DUPLICATE_VENDOR_ID = XAL.PARTY_ID AND DUPLICATE_VENDOR_SITE_ID = XAL.PARTY_SITE_ID)
        ELSE XAL.PARTY_SITE_ID
        END,XAL.PARTY_SITE_ID) PARTY_SITE_ID,
 GCC.CONCATENATED_SEGMENTS,GCC.CODE_COMBINATION_ID,
 0 O_LEDGER_CR,0 O_LEDGER_DR,
 NVL(XAL.ACCOUNTED_CR,0) T_LEDGER_CR,
 NVL(XAL.ACCOUNTED_DR,0) T_LEDGER_DR
 FROM
 XLA.XLA_TRANSACTION_ENTITIES XTE,
 XLA.XLA_AE_HEADERS XAH,
 XLA.XLA_AE_LINES XAL,
 GL_CODE_COMBINATIONS_KFV GCC
 WHERE 1 = 1
 AND :P_APPLICATION_ID=200
 AND XAL.APPLICATION_ID=200
 AND XTE.LEDGER_ID = XAH.LEDGER_ID
 AND XTE.ENTITY_ID = XAH.ENTITY_ID
 AND XTE.APPLICATION_ID = XAL.APPLICATION_ID
 AND XAH.AE_HEADER_ID = XAL.AE_HEADER_ID
 AND XAL.GL_SL_LINK_TABLE = 'XLAJEL'
 AND ACCOUNTING_CLASS_CODE IN ('LIABILITY','PREPAID_EXPENSE')
 AND XAL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID
 and xal.CODE_COMBINATION_ID in  (SELECT CODE_COMBINATION_ID FROM GL_CODE_COMBINATIONS
 WHERE 1=1
 AND SEGMENT5=24103390)
-- AND XAL.PARTY_ID=&VENDOR_ID
 AND NVL(XAL.GL_TRANSFER_MODE_CODE,'N')='S'
 AND TRUNC(XAL.ACCOUNTING_DATE) >= :FROM_DATE
 AND TRUNC(XAL.ACCOUNTING_DATE) <= :TO_DATE
 AND SECURITY_ID_INT_1=:P_ORG_ID)
 group by CONCATENATED_SEGMENTS


Step4:- Now match the accounts fetched with Step 2 and 3 and find the difference.



If you Want to Learn Oracle Fusion , Please Follow the Oracle Fusion Tutorial

SQL Query to Find Lock Sessions in Oracle Apps R12

SELECT s.inst_id,
NVL (s.username, 'Internal') "Database User",
m.SID,
s.serial#,
p.spid "DB OS Process",
m.TYPE,
DECODE (m.lmode,
0, 'None',
1, 'Null',
2, 'Row Share',
3, 'Row Excl.',
4, 'Share',
5, 'S/Row Excl.',
6, 'Exclusive',
lmode, LTRIM (TO_CHAR (lmode, '990'))
) "Lock Type",
DECODE (m.request,
0, 'None',
1, 'Null',
2, 'Row Share',
3, 'Row Excl.',
4, 'Share',
5, 'S/Row Excl.',
6, 'Exclusive',
request, LTRIM (TO_CHAR (m.request, '990'))
) "Lock Request",
DECODE (command,
0, 'None',
DECODE (m.id2,
0, dusr.username || '.' || SUBSTR (dobj.NAME, 1, 30),
'Rollback Segment'
)
) "Object",
s.machine "Application Server",
s.process "Apps OS process",
m.ctime,
NVL (NVL (usr.description, s.action),
'Database Session'
) "Online User,Concurrent",
NVL (fnd.responsibility_name, s.module) "Responsibility,Module",
fnd.user_form_name "Form Name",
SQL.sql_text "Statement"
FROM gv$session s,
gv$lock m,
gv$process p,
apps.fnd_form_sessions_v fnd,
apps.fnd_user usr,
gv$sqlarea SQL,
dba_users dusr,
SYS.obj$ dobj
WHERE m.id1 IN (SELECT il.id1
FROM gv$lock il
WHERE il.request <> 0)
AND m.SID = s.SID
AND s.paddr = p.addr
AND s.inst_id = p.inst_id
AND SQL.inst_id(+) = s.inst_id
AND SQL.address(+) = s.sql_address
AND SQL.hash_value(+) = s.sql_hash_value
AND s.username != 'SYS'
AND m.lmode != 4
AND fnd.audsid(+) = s.audsid
AND m.inst_id = s.inst_id
AND fnd.user_name = usr.user_name(+)
AND fnd.user_id = usr.user_id(+)
AND dobj.obj#(+) = DECODE (m.id2, 0, m.id1, 1)
AND dusr.user_id(+) = dobj.owner#
ORDER BY m.id1, m.request ASC, m.SID;