Aging Reports in Oracle Apps Receivables - Quick Overview

AGING REPORTS IN ORACLE APPS RECEIVABLES - QUICK OVERVIEW

OVERVIEW

Aging reports are used to review information about your open items so as to know how much funds have been held up and with whom. Aging reports display the customers who have outstanding balances and the amount each customer owes us which also helps to expedite the collection process.  Receivables aging reports Do Not include customers with a zero outstanding balance.

These reports can print both detail and summary information about your customer’s current and past due invoices, debit memos, and charge backs.  Receivables also gives the option to see credit memos, on–account credits, unidentified payments, and on–account and unapplied cash amounts.

As mentioned above, Oracle Receivables ages the transactions according to due date. The aging reports however select transactions according to gl date. 

Receivables aging reports includes all open items whose gl_date is before or the same as the “as of date” entered, and whose gl_date_closed is after the “as of date”. The default value for “as of date” is the current date.

 CATEGORIES OF AGING REPORTS 

Receivables aging reports are categorized as follows:

• Aging – By Account
• Aging – By Amount
• Aging – By Collector
• Aging – By Salesperson 

 REPORT PARAMETERS FOR AGING REPORTS 

Aging Bucket Name:  

Specify the bucket set from which the report information needs to be printed. The default bucket set is 'Standard'.

As of Date:

Specify the date as of which the transactions need to be aged. Receivables includes all open items whose GL date is before or the same as this date. The default is the current date.

Order By:

The option you want Receivables to use to sort your information. For example, you can sort by:

·        Customer Name (Aging - 4 and 7 Bucket reports)

·         Transaction Type (Aging - 4 and 7 Bucket reports)

·        Balance Due (only for 7 Buckets - By Amount report)

·        Salesperson (only for 7 Buckets - By Salesperson report)

Report Format:

The “Brief” format prints customer name and customer number with item information while the “Detailed” format prints address and contact addressas well.

Report Summary:

The “Invoice Summary” option prints information on all customers' debit items.

The “Customer Summary” option prints customers' names with their total debit item balances.

Show on Account:

Specify whether to print credit items for your customers.

·        Do Not Show: Receivables does not display any of your identified or unidentified payments, or on-account credit memos.

·        Age: Receivables ages your credit items and includes the credit amounts in the appropriate aging bucket columns.

·        Summarize: Receivables displays the sum of your credit items in the Customer Credit Memos, Customer Payments, and the Customer Balance rows. This is the default option.

Show Receipts at Risk:

Receipts at Risk are receipts that have either not been cleared or factored receipts that have not been risk eliminated. Select one of the following values for your report:

·        Age:- Include receipts at risk in this report. Receivables displays the receipts at risk with other open receipts in the appropriate bucket and includes them when determining the customer's balance.

·        Summarize:- Receivables displays the sum of your receipts at risk in the Customer Credit Memos, Customer Payments, and the Customer Balance rows.

Do Not Show :- Receipts at risk will not be included in this report. This value is used as the default.

Aging 7 Bucket Analysis Report

Aging 7 Bucket Analysis Report

'Aging 7 Bucket Analysis Report', is one of the very good standard reports in Oracle. This report comes in different variants. Each variant has different parameter options.

There are the following versions of this report

1. Aging - 7 Buckets Report

2. Aging - 7 Buckets - By Account Report

3. Aging - 7 Buckets - By Amount Report

4. Aging - 7 Buckets - By Collector Report

5. Aging - 7 Bucket - By Salesperson / Agent report

There is another report 'Aging- 7 Buckets - By Account - Multi-Fund Accounts Receivable', which I am not clear about since I have not used.

Each of these reports have the following Options.

1. Order by Customer or Transaction Type 

2. Report can be summarized as Customer Summary / Invoice Summary

3. Report Format can be Brief or Detailed

This means that there are 6*8 = 48 Variants of this report.

How can you use this report.

a. If you run the report by Amount, you can quickly see your top receivables and you can quickly take corrective action.

b. If you run this report by Account, you can quickly use this report to reconcile with GL

c. Since this give you Aging information, you have much better control over your receivables.

What are the drawbacks?

Main drawback is that this report output is in Text Mode. For Finance managers accustomed to Excel, this change can be a problem.

Other than the above, this is a great report. Very stable, and very accurate and give you very good information that can help decision making.

Hope you get value out of that report

AP Trail Balances SQL Query for R12

AP Trail Balances SQL Query for R12

SELECT  aia.invoice_num, aia.description inv_description,aia.attribute2 "Dept", aps.segment1 vendor#,
       aps.vendor_name, aia.invoice_currency_code, aia.invoice_amount,tb.diff "Amount Remain",
          gcc.segment1
       || '.'
       || gcc.segment2
       || '.'
       || gcc.segment3
       || '.'
       || gcc.segment4
       || '.'
       || gcc.segment5
       || '.'
       || gcc.segment6
       || '.'
       || gcc.segment7 ACCOUNT,
       aia.invoice_type_lookup_code,
       aia.invoice_date, aia.attribute5 status,
       ap.NAME terms
  FROM xla.xla_transaction_entities xte,
       (SELECT   tb.code_combination_id,
                 NVL (tb.applied_to_entity_id, tb.source_entity_id) entity_id,
                 SUM (NVL (tb.acctd_rounded_cr, 0)),
                 SUM (NVL (tb.acctd_rounded_dr, 0)),
                   SUM (NVL (tb.acctd_rounded_cr, 0))
                 - SUM (NVL (tb.acctd_rounded_dr, 0)) diff,
                 party_id
            FROM xla_trial_balances tb
           WHERE tb.definition_code =:definition_code  -- Ex :  'AP_200_1001'
               and trunc(tb.gl_date) <=TO_DATE ('1-NOV-2011')
                       GROUP BY tb.code_combination_id,
                 NVL (tb.applied_to_entity_id, tb.source_entity_id),
                 tb.party_id
          HAVING SUM (NVL (tb.acctd_rounded_cr, 0)) <>
                                            SUM (NVL (tb.acctd_rounded_dr, 0))) tb,
       ap_invoices_all aia,
       ap_suppliers aps,
       gl_code_combinations gcc,
       ap_terms ap
 WHERE tb.entity_id = xte.entity_id
   AND xte.application_id =:application_id   --Ex :  200
   AND xte.source_id_int_1 = aia.invoice_id
   AND aia.vendor_id = aps.vendor_id
   AND tb.code_combination_id = gcc.code_combination_id
   AND ap.term_id = aia.terms_id



-----------------------

AP Invoice liability (Trial Balance) Query: R12 AP GL PO RCV XLA

SELECT   NVL (f.currency_code, d.invoice_currency_code) currency_code,
         d.invoice_amount, d.wfapproval_status, d.invoice_num, d.invoice_id,
         i.segment1 v_code, i.vendor_name,
         NVL (d.invoice_amount, 0) - NVL (d.amount_paid, 0) amt,
         SUM (a.acctd_rounded_cr) - SUM (a.acctd_rounded_dr) amount,
         b.accounting_date, f.segment1 po_num, f.comments descr,
         h.receipt_num rct_num, c.segment6 loc, c.segment2 cost_centre,
         d.doc_sequence_value ap_voucher, j.employee_number, j.full_name
    FROM xla_transaction_entities xte,
         ap.ap_invoice_distributions_all b,
         ap.ap_invoice_lines_all k,
         gl.gl_code_combinations c,
         ap.ap_invoices_all d,
         po.po_distributions_all e,
         po.po_headers_all f,
         apps.rcv_transactions g,
         apps.rcv_shipment_headers h,
         apps.po_vendors i,
         apps.per_all_people_f j,
         (SELECT   /*+ index(tb XLA_TRIAL_BALANCES_N2) */
                   tb.code_combination_id, tb.ledger_id, tb.party_id party_id,
                   tb.gl_date, tb.ae_header_id,
                   NVL (tb.applied_to_entity_id,
                        tb.source_entity_id
                       ) entity_id,
                   tb.definition_code, tb.record_type_code,
                   SUM (NVL (tb.acctd_rounded_cr, 0)) acctd_rounded_cr,
                   SUM (NVL (tb.acctd_rounded_dr, 0)) acctd_rounded_dr,
                     SUM (NVL (tb.acctd_rounded_cr, 0))
                   - SUM (NVL (tb.acctd_rounded_dr, 0)) diff,
                   party_id
              FROM xla_trial_balances tb
             WHERE tb.definition_code = 'AP_200_1001'
               AND tb.gl_date <= :acc_date --GL Date Value               
               AND tb.code_combination_id IN (SELECT code_combination_id
                                              FROM gl_code_combinations a
                                              WHERE a.segment3 = :acc_code)--GL CC Value
          GROUP BY tb.code_combination_id,
                   tb.ledger_id,
                   tb.party_id,
                   tb.party_id,
                   NVL(tb.applied_to_entity_id,tb.source_entity_id),
                   tb.record_type_code,
                   tb.party_id,
                   tb.gl_date,
                   tb.definition_code,
                   tb.ae_header_id) a
   WHERE a.code_combination_id = c.code_combination_id
     AND d.INVOICE_NUM = :invoice_num   --Invoice Num    
     AND a.definition_code = 'AP_200_1001'
     AND a.entity_id = xte.entity_id
     AND xte.application_id = 200
     AND xte.ledger_id = a.ledger_id
     AND NVL (source_id_int_1, (-99)) = d.invoice_id
     AND d.invoice_id = k.invoice_id
     AND k.line_number = b.invoice_line_number
     AND c.segment3 = :acc_code               --GL CC Code     
     AND a.gl_date <= :acc_date               --Gl Date 
     AND k.invoice_id = b.invoice_id
     AND b.distribution_line_number = '1'
     AND k.line_number = '1'
     AND b.po_distribution_id = e.po_distribution_id(+)
     AND e.po_header_id = f.po_header_id(+)
     AND b.rcv_transaction_id = g.transaction_id(+)
     AND g.shipment_header_id = h.shipment_header_id(+)
     AND f.agent_id = j.person_id(+)
     AND i.vendor_id = d.vendor_id
     AND d.set_of_books_id = a.ledger_id
     AND d.org_id = :l_org_id           --Invoice Org Id   
     AND TRUNC (SYSDATE) BETWEEN j.effective_start_date(+) 
     AND j.effective_end_date(+)
  HAVING SUM (a.acctd_rounded_cr) - SUM (a.acctd_rounded_dr) <> 0
  GROUP BY f.currency_code,
         d.invoice_amount,
         b.project_id,
         d.wfapproval_status,
         d.invoice_num,
         d.invoice_id,
         b.accounting_date,
         f.segment1,
         h.receipt_num,
         i.segment1,
         i.vendor_name,
         c.segment6,
         d.doc_sequence_value,
         c.segment2,
         j.employee_number,
         j.full_name,
         f.agent_id,
         f.comments,
         d.invoice_currency_code,
         b.posted_flag,
         d.amount_paid,
         d.invoice_id

GL Trial Balance Query -Summary

GL Trial Balance Query -Summary
In this query segment3 is gl account against which credit , debit and balance value show. To get Gl segment3 i.e account description i used gl_flexfield_pkg.get_description_sql API.

-----------
select
GCC.SEGMENT3 account,
gl_flexfields_pkg.get_description_sql(gcc.chart_of_accounts_id,3,gcc.segment3)description,
SUM(NVL(GJL.ACCOUNTED_DR,0))ACCOUNTED_DR,
SUM(NVL(GJL.ACCOUNTED_CR,0))ACCOUNTED_CR,
SUM(NVL(GJL.ACCOUNTED_DR,0)-NVL(GJL.ACCOUNTED_CR,0))BALANCE
from gl_je_headers gjh,
gl_je_lines gjl,
gl_ledgers gl,
gl_code_combinations_kfv gcc,
GL_JE_BATCHES GJB
where gjl.je_header_id = gjh.je_header_id
AND gjh.je_batch_id=gjb.je_batch_id
and gjl.code_combination_id=gcc.code_combination_id
and gjh.ledger_id=gl.ledger_id
and gjh.status='P'
AND gjh.actual_flag='A'
AND gjh.period_name='DEC-2014'
AND GL.name= 'US Vision Operation'
GROUP BY
GCC.SEGMENT3,
gcc.chart_of_accounts_id
order by 1;


Output
-------------------------------------------------------------------------------------------------
Account     Description                                  accounted_dr   accounted_cr    Balance(Total)
12536        Sales account                                             3000                1000          

Query to find DFF

Let's say, we need to find Descriptive Flexfield (DFF) called, "Further Job Information". In the following example, I am trying to get all the information for "US" context code.

The following query will display the DFF related information. You can try changing "fdfv.title" value too see different DFF.

-----------------------------------------------------------------------------

-- Query to find DFF information

-----------------------------------------------------------------------------

SELECT

       fdf.title                             "DFF Title",

       fdf.application_table_name            "Application Table",

       fdf.context_column_name               "Context Column Name",

       --

       fdfcu.descriptive_flex_context_code   "DFF Context Code",

       fdfcu.column_seq_num                  "Sequence",

       fdfcu.end_user_column_name            "Segment Name",

       fdfcu.application_column_name         "Column Name",

       --

       ffv.flex_value_set_name               "Value Set Name"

  FROM

       fnd_descr_flex_col_usage_vl   fdfcu,

       fnd_descriptive_flexs_vl      fdf,

       fnd_flex_value_sets           ffv

 WHERE

       1 = 1

   --

   AND fdf.title = 'Further Job Information'        -- <change it>

   AND fdfcu.descriptive_flex_context_code = 'US'   -- <change it>

   AND fdfcu.enabled_flag = 'Y'

   --

   AND fdfcu.flex_value_set_id = ffv.flex_value_set_id

   AND fdfcu.descriptive_flexfield_name = fdf.descriptive_flexfield_name

   AND fdfcu.application_id = fdf.application_id

   --

 ORDER BY

       fdfcu.descriptive_flexfield_name,

       fdfcu.descriptive_flex_context_code,

       fdfcu.column_seq_num;

PO: Tips and useful Query

The consultant life while working at client site is not easy during ERP transformation projects, many times it's required to provide some adhoc query for extract to ends users, therefore it is important to have a cheat sheet so that such untimely things can be easily handled in sort span. Hope these query and tips useful to all Inhouse IT personals who is part of Implementation Project team.

1. You need to list out all Internal Requisitions that do not have an associated Internal Sales order.

Internal Requisitions without Sales order

2. You want to display what requisition and PO are linked(Relation with Requisition and PO )

Requisition and PO

3. You need to list out all cancel Requisitions

Cancel Requisition

4. You need to list those PR which havn't auto created to PO.(Purchase Requisition without a Purchase Order)

PR without PO

5. You need to list all information form PR to PO ...as a requisition moved from different stages till converting into PR. This query capture all details related to that PR to PO.

PR to PO 

6.Identifying all PO's which does not have any PR's

PO without Requisition

7. Relation between Requisition and PO tables

Here is link:

PO_DISTRIBUTIONS_ALL =>PO_HEADER_ID, REQ_DISTRIBUTION_ID
PO_HEADERS_ALL=>PO_HEADER_ID, SEGMENT1
PO_REQ_DISTRIBUTIONS_ALL =>DISTRIBUTION_ID, REQUISITION_LINE_ID
PO_REQUISITION_LINES_ALL =>REQUISITION_LINE_ID)
PO_REQUISITION_HEADERS_ALL =>REQUISITION_HEADER_ID, REQUISITION_LINE_ID, SEGMENT1

What you have to make a join on PO_DISTRIBUTIONS_ALL (REQ_DISTRIBUTION_ID) and PO_REQ_DISTRIBUTIONS_ALL (DISTRIBUTION_ID) to see if there is a PO for the req.

8.You need to find table which hold PO Approval path...

These two table keeps the data:

• PO_APPROVAL_LIST_HEADERS
• PO_APPROVAL_LIST_LINES

9. List all the PO's with there approval ,invoice and Payment Details

List PO's with Approval , invoice and Payment info

10.You need to know the link to GL_JE_LINES table for purchasing accrual and budgetary control actions..

The budgetary (encumbrance) and accrual actions in the purchasing module generate records that will be imported into GL for the corresponding accrual and budgetary journals.

The following reference fields are used to capture and keep PO information in the GL_JE_LINES table.

These reference fields are populated when the Journal source (JE_SOURCE in GL_JE_HEADERS) is
Purchasing.

Budgetary Records from PO (These include reservations, reversals and cancellations):

• REFERENCE_1- Source (PO or REQ)
• REFERENCE_2- PO Header ID or Requisition Header ID (from po_headers_all.po_header_id or
  po_requisition_headers_all.requisition_header_id)
• REFERENCE_3- Distribution ID (from po_distributions_all.po_distribution_id or
  po_req_distributions_all.distribution_id)
• REFERENCE_4- Purchase Order or Requisition number (from po_headers_all.segment1 or
  po_requisition_headers_all.segment1)
• REFERENCE_5- (Autocreated Purchase Orders only) Backing requisition number (from po_requisition_headers_all.segment1)

Accrual Records from PO:

• REFERENCE_1- Source (PO)
• REFERENCE_2- PO Header ID (from po_headers_all.po_header_id)
• REFERENCE_3- Distribution ID (from po_distributions_all.po_distribution_id
• REFERENCE_4- Purchase Order number (from po_headers_all.segment1)
• REFERENCE_5- (ON LINE ACCRUALS ONLY) Receiving Transaction ID (from rcv_receiving_sub_ledger.rcv_transaction_id)

Take a note for Period end accruals, the REFERENCE_5 column is not used.

11. List me all open PO's

List me all Open PO'S

12.There are different authorization_status can a requisition have.

• Approved
• Cancelled
• In Process
• Incomplete
• Pre-Approved
• Rejected

and you should note: When we finally close the requisition from Requisition Summary form the authorization_status of the requisition does not change. Instead it's closed_code becomes 'FINALLY CLOSED'.

13. A standard Quotations one that you can tie back to a PO.
Navigate to RFQ -> Auto create -> enter a PO and reference it back.

14. I want to debug for a PO , where should I start.

Thats is possible, your PO get stuck somewhere, so what you have to do is to analyze which stage it stucked.Get po_header_id first and run each query and then analyze the data.For better understanding this is splited into 5 major stages.

Stage 1: PO Creation :

PO_HEADERS_ALL

select po_header_id from po_headers_all where segment1 =<po_number>;

select * from po_headers_all where po_header_id =<po_header_id>;

po_lines_all

select * from po_lines_all where po_header_id =<po_header_id>;

po_line_locations_all

select * from po_line_locations_all where po_header_id =<po_header_id>;

po_distributions_all

select * from po_distributions_all where po_header_id =<po_header_id>;

po_releases_all

SELECT * FROM po_releases_all WHERE po_header_id =<po_header_id>;

Stage 2: Once PO is received data is moved to respective receving tables and inventory tables

RCV_SHIPMENT_HEADERS

select * from rcv_shipment_headers where shipment_header_id in
(select shipment_header_id from rcv_shipment_lines
where po_header_id =<po_header_id>);

RCV_SHIPMENT_LINES

select * from rcv_shipment_lines where po_header_id =<po_header_id>;

RCV_TRANSACTIONS

select * from rcv_transactions where po_header_id =<po_header_id>;

RCV_ACCOUNTING_EVENTS

SELECT * FROM rcv_Accounting_Events WHERE rcv_transaction_id IN
(select transaction_id from rcv_transactions
where po_header_id =<po_header_id>);

RCV_RECEIVING_SUB_LEDGER

select * from rcv_receiving_sub_ledger where rcv_transaction_id in (select transaction_id from rcv_transactions where po_header_id =<po_header_id>);

RCV_SUB_LEDGER_DETAILS

select * from rcv_sub_ledger_details
where rcv_transaction_id in (select transaction_id from rcv_transactions where po_header_id =<po_header_id>);

MTL_MATERIAL_TRANSACTIONS

select * from mtl_material_transactions where transaction_source_id =<po_header_id>;

MTL_TRANSACTION_ACCOUNTS

select * from mtl_transaction_accounts where transaction_id in ( select transaction_id from mtl_material_transactions where transaction_source_id = =<po_header_id>);

Stage 3: Invoicing details

AP_INVOICE_DISTRIBUTIONS_ALL

select * from ap_invoice_distributions_all where po_distribution_id in ( select po_distribution_id from po_distributions_all where po_header_id =<po_header_id>);

AP_INVOICES_ALL

select * from ap_invoices_all where invoice_id in
(select invoice_id from ap_invoice_distributions_all where po_distribution_id in
( select po_distribution_id from po_distributions_all where po_header_id =<po_header_id>));

Stage 4 : Many Time there is tie up with Project related PO

PA_EXPENDITURE_ITEMS_ALL

select * from pa_expenditure_items_all peia where peia.orig_transaction_reference in
( select to_char(transaction_id) from mtl_material_transactions
where transaction_source_id = <po_header_id> );

Stage 5 : General Ledger

Prompt 17. GL_BC_PACKETS ..This is for encumbrances

SELECT * FROM gl_bc_packets WHERE reference2 IN ('<po_header_id>');

GL_INTERFACE

SELECT *
FROM GL_INTERFACE GLI
WHERE user_je_source_name ='Purchasing'
AND gl_sl_link_table ='RSL'
AND reference21='PO'
AND EXISTS
( SELECT 1
FROM rcv_receiving_sub_ledger RRSL
WHERE GLI.reference22 =RRSL.reference2
AND GLI.reference23 =RRSL.reference3
AND GLI.reference24 =RRSL.reference4
AND RRSL.rcv_transaction_id in
(select transaction_id from rcv_transactions
where po_header_id <po_header_id>));

GL_IMPORT_REFERENCES

SELECT *
FROM gl_import_references GLIR
WHERE reference_1='PO'
AND gl_sl_link_table ='RSL'
AND EXISTS
( SELECT 1
FROM rcv_receiving_sub_ledger RRSL
WHERE GLIR.reference_2 =RRSL.reference2
AND GLIR.reference_3 =RRSL.reference3
AND GLIR.reference_4 =RRSL.reference4
AND RRSL.rcv_transaction_id in
(select transaction_id from rcv_transactions
where po_header_id =<po_header_id>))

Gl to Ap Drill down Query R12, General ledger link to Account payables R12,AP To Gl

General Ledger to Payables Drilldown

This time I am here with General Ledger to Payables Drilldown in R12.

We know that we have two types of transactions in Payables

• Invoices
• Payment

Let’s start with Invoices.

Tables involved for Journal Entry to Invoice drilldown are 

• GL_JE_HEADERS
• GL_IMPORT_REFERENCES
• XLA_AE_LINES
• XLA_AE_HEADERS 
• XLA_TRANSACTION_ENTITIES 
• AP_INVOICES_ALL

Now, when we are given a Journal entry with JE_SOURCE as ‘Payables’ and JE_CATEGORY as ‘Purchase Invoice’, we got to track its JE_HEADER_ID. Here is the step by step process. 

Step 1: For instance, we are going to track the invoice against the maximum JE_HEADER_ID with Payables source and Purchase Invoices Category. Here is the query for it;

SELECT MAX(JE_HEADER_ID )

INTO V_JE_HEADER_ID

FROM GL_JE_HEADERS

WHERE JE_SOURCE = ‘Payables’

AND JE_CATEGORY= ‘Purchase Invoices’;

Step 2: Now we are going to check the corresponding GL_SL_LINK_ID against this JE_HEADER_ID. We can get these link ids through the query,

SELECT GL_SL_LINK_ID

FROM GL_IMPORT_REFERENCES

WHERE JE_HEADER_ID = V_JE_HEADER_ID;

Step 3: And to get to the relevant lines in sub-ledger modules we need to find lines against the identified GL_SL_LINK_IDs from XLA_AE_LINES table. Here is the query to get to the sub-ledger lines

SELECT DISTINCT AE_HEADER_ID

INTO V_AE_HEADER_ID

FROM XLA_AE_LINES

WHERE GL_SL_LINK_ID IN (SELECT GL_SL_LINK_ID

FROM GL_IMPORT_REFERENCES

WHERE JE_HEADER_ID = V_JE_HEADER_ID

);

Step 4: Now we will find ENTITY_ID against the identified header_id. Here is the query,

SELECT ENTITY_ID

INTO V_ENTITY_ID

FROM XLA_AE_HEADERS

WHERE AE_HEADER_ID = V_AE_HEADER_ID;

Step 5: Now, we will get the SOURCE_ID_INT_1 against identified XLA_TRANSACTION to get to the Payables transaction.

SELECT SOURCE_ID_INT_1

INTO V_SOURCE_ID

FROM XLA_TRANSACTION_ENTITIES

WHERE ENTITY_ID = V_ENTITY_ID;

Step 6: Now is the last step, we will use this source id as invoice id to get to the invoice in payables.

SELECT *

FROM AP_INVOICES_ALL

WHERE INVOICE_ID = V_SOURCE_ID;

So, here is the drilldown from General Ledger to Payables Invoices. We got to change in step 1 for JE_CATEGORY as ‘Payments’ and the query becomes

SELECT MAX(JE_HEADER_ID )

INTO V_JE_HEADER_ID

FROM GL_JE_HEADERS

WHERE JE_SOURCE = ‘Payables’

AND JE_CATEGORY= ‘Payments’;

 And in step 6, we got to find check details against the identified SOURCE_ID, which makes the SQL as

SELECT *

FROM AP_CHECKS_ALL

WHERE CHECK_ID = V_SOURCE_ID;

This was all about GL to Payables Drilldown.

Link Between AP and GL with XLA tables in R12 ,AP to GL Link in oracle apps, Data Flow from APto XLA to GL

Technical details of Drilldown from GL to Sub Modules

Ap invoices  invoice is is stored in .xla_transaction_entities  as  xte.source_id_int_1

and ACCTS_PAY_CODE_COMBINATION_ID = code_combination_id in gl code combination 

.xla_transaction_entities  entity_id is linked with xla events and xla  ae headers and xla ae lines

                                                          AP to GL link

  SELECT distinct 

           aia.INVOICE_ID "Invoice_Id", ---IN R12

           AIA.DOC_SEQUENCE_VALUE,

           aia.INVOICE_NUM ,

           aia.attribute6 "INVOICE_ID",--IN11i

           aia.GL_DATE,

           aia.INVOICE_AMOUNT,

           xal.ACCOUNTED_DR "Accounted DR IN SLA",

           xal.ACCOUNTED_CR "Accounted CR IN SLA",

           gjl.ACCOUNTED_CR "ACCOUNTED_CR IN GL",

           gjl.ACCOUNTED_DR "Accounted DR IN GL",         

           xev.event_type_code,

              gcc.SEGMENT1

           || '.'

           || gcc.SEGMENT2

           || '.'

           || gcc.SEGMENT3

           || '.'

           || gcc.SEGMENT4

           || '.'

           || gcc.SEGMENT5

           || '.'

           || gcc.SEGMENT6

           || '.'

           || gcc.SEGMENT7

              "CODE_COMBINATION",

           aia.GL_DATE,

           xah.PERIOD_NAME,

           aia.VENDOR_ID "Vendor Id",

           aps.VENDOR_NAME "Vendor Name",

           xah.JE_CATEGORY_NAME "JE Category Name",

                      GJH.JE_SOURCE

    FROM   ap_invoices_all aia,

           xla.xla_transaction_entities XTE,

           xla_events xev,

           xla_ae_headers XAH,

           xla_ae_lines XAL,

           GL_IMPORT_REFERENCES gir,

           gl_je_headers gjh,

           gl_je_lines gjl,

           gl_code_combinations gcc,

           ap_suppliers aps

   WHERE       aia.INVOICE_ID = xte.source_id_int_1

           and aia.ACCTS_PAY_CODE_COMBINATION_ID = gcc.code_combination_id

           AND xev.entity_id = xte.entity_id

           AND xah.entity_id = xte.entity_id

           AND xah.event_id = xev.event_id

           AND XAH.ae_header_id = XAL.ae_header_id

              and XAH.je_category_name = 'Purchase Invoices'

           AND GJH.JE_SOURCE = 'Payables'

           AND XAL.GL_SL_LINK_ID = gir.GL_SL_LINK_ID

           and gir.GL_SL_LINK_ID = gjl.GL_SL_LINK_ID

           AND gir.GL_SL_LINK_TABLE = xal.GL_SL_LINK_TABLE

           AND gjl.JE_HEADER_ID = gjh.JE_HEADER_ID

           AND gjl.ledger_id = gjh.ledger_id

           and xah.ledger_id = gjh.ledger_id

           AND gjh.JE_HEADER_ID = gir.JE_HEADER_ID

           and aia.set_of_books_id = gjh.ledger_id

           AND gjl.JE_HEADER_ID = gir.JE_HEADER_ID

           AND gir.JE_LINE_NUM = gjl.JE_LINE_NUM

           AND gcc.CODE_COMBINATION_ID = XAL.CODE_COMBINATION_ID

           AND gcc.CODE_COMBINATION_ID = gjl.CODE_COMBINATION_ID

           AND aia.VENDOR_ID = aps.VENDOR_ID

           AND gjh.PERIOD_NAME BETWEEN NVL (:PERIOD_FROM, gjh.PERIOD_NAME)

                                   AND  NVL (:PERIOD_TO, gjh.PERIOD_NAME)               

           AND gcc.SEGMENT1 = NVL (:seg1, gcc.SEGMENT1)

           AND gcc.SEGMENT3 = NVL (:seg, gcc.SEGMENT3)

ORDER BY   1, aia.GL_DATE