FA-SLA-GL Linking in R12

FA-SLA-GL Linking in R12:

 select * from apps.fnd_application_vl where application_name like '%Asset%'

   
   1> Fixed Asset Tables :      
  
      select * from  apps.FA_Transaction_Headers where BOOK_TYPE_CODE= 'OPS CORP' and  asset_id=110279--event_id=4229742
     
      select * from apps.FA_ADDITIONS_B where ASSET_ID=110279
     
      select * from apps.FA_BOOKS where asset_id=110279
     
      select * from apps.FA_DEPRN_SUMMARY where ASSET_ID=110279
     
      select * from apps.FA_DEPRN_DETAIL where ASSET_ID=110279
      
      select * from apps.FA_DEPRN_PERIODS where BOOK_TYPE_CODE= 'OPS CORP' and FISCAL_YEAR=2008 and PERIOD_NUM=3
      
 
     
   2> SLA Tables:  
      select * from apps.xla_ae_headers xah where application_id=140 and event_id=4229742 --Get Event_id From FA_Transaction_Headers Tables And Note Down AE_HEADER_ID
            
      select * from apps.xla_ae_lines xal where application_id=140 and AE_HEADER_ID=4968977 --Get AE_HEADER_ID from xla_ae_headers and Note Down GL_SL_LINK_TABLE='XLAJEL' GL_SL_LINK_ID=925293,925292
     
      select * from apps.xla_distribution_links   xdl where application_id=140 and AE_HEADER_ID=4968977 --Get AE_HEADER_ID from xla_ae_headers and SOURCE_DISTRIBUTION_ID_NUM_1=186213 and SOURCE_DISTRIBUTION_ID_NUM_2=24082 and SOURCE_DISTRIBUTION_ID_NUM_3=13009
     
      select * from apps.xla_events xe where application_id=140 and event_id=4229742 --Get Event_id From FA_Transaction_Headers Tables
     
      select * from apps.XLA_TRANSACTION_ENTITIES  where application_id=140 and ENTITY_ID=4162281  --and SOURCE_ID_INT_1=186213 and SOURCE_ID_INT_2=24082 and SOURCE_ID_INT_3=13009
     
      --Note:apps.XLA_TRANSACTION_ENTITIES.SOURCE_ID_INT_1=apps.xla_distribution_links.SOURCE_DISTRIBUTION_ID_NUM_1
     
   
      3>GL Tables
     
       select * from apps.gl_import_references where GL_SL_LINK_TABLE='XLAJEL' and GL_SL_LINK_ID IN(6891055 ,6891056)
      
       select * from apps.GL_je_headers where JE_HEADER_ID=4711169
      
       select * from apps.gl_je_lines gll where JE_HEADER_ID=4711169
      
       select * from apps.gl_periods ps
      
       select * from apps.gl_ledgers gl where LEDGER_ID=1
      
       select * from apps.gl_code_combinations  where CHART_OF_ACCOUNTS_ID=101
      

Project Accounting Mismatch SQL Queries

1.) AP-NOPROJ-ENTRIES-JAN TO MAR

SELECT   d.ACCOUNTING_DATE, i.invoice_num, i.invoice_date, i.invoice_amount, d.amount,
         d.accounting_date, g.segment4, g.segment6, d.project_id , g.concatenated_segments, d.invoice_distribution_id,
         i.creation_date, d.status, d.posted_status_disp
    FROM ap_invoices_v i,
         ap_invoice_lines_all l,
         ap_invoice_distributions_v d,
         gl_code_combinations_kfv g
   WHERE i.invoice_id = l.invoice_id
     AND i.invoice_id = d.invoice_id
     AND l.line_number = d.invoice_line_number
     AND d.dist_code_combination_id = g.code_combination_id
     AND d.project_id IS NULL
     AND i.vendor_id != 313296
     AND g.segment6 != '0000'
     AND g.segment4 LIKE '13%'
     AND d.ACCOUNTING_DATE BETWEEN '01-Jan-2015' AND '31-MAR-2015'
ORDER BY d.status

2.) INV-NOPROJ-ENTRIES- JAN TO MAR

SELECT  MMT.ORGANIZATION_ID, MP.ORGANIZATION_CODE, MTA.TRANSACTION_SOURCE_TYPE_ID, MTT.TRANSACTION_TYPE_NAME,MMT.TRANSACTION_DATE,
        MTA.BASE_TRANSACTION_VALUE, MTA.REFERENCE_ACCOUNT, GLCC.CONCATENATED_SEGMENTS, GLCC.SEGMENT4, GLCC.SEGMENT6
FROM MTL_TRANSACTION_ACCOUNTS MTA,
GL_CODE_COMBINATIONS_KFV GLCC,
MTL_MATERIAL_TRANSACTIONS MMT,
MTL_TRANSACTION_TYPES MTT,
MTL_PARAMETERS MP
WHERE MTA.REFERENCE_ACCOUNT = GLCC.CODE_COMBINATION_ID
  AND MTA.TRANSACTION_ID = MMT.TRANSACTION_ID
  AND MMT.TRANSACTION_TYPE_ID = MTT.TRANSACTION_TYPE_ID
  AND MMT.ORGANIZATION_ID = MP.ORGANIZATION_ID
  AND TRUNC(MTA.TRANSACTION_DATE) BETWEEN '01-JAN-2015' AND '31-MAR-2015'
  AND GLCC.SEGMENT6 != '0000'
  AND MMT.SOURCE_PROJECT_ID IS NULL


3.) INV-PROJ-MM-COA-ENTRIES- JAN TO MAR

select TRANSACTION_SOURCE,  glcc.segment6, a.project_number, SUM(A.BURDENED_COST)
from PA_EXPEND_ITEMS_ADJUST2_V a,
PA_COST_DISTRIBUTION_LINES_ALL pd,
GL_CODE_COMBINATIONS_KFV glcc
where a.expenditure_item_id = pd.expenditure_item_id
  and pd.dr_code_combination_id = glcc.code_combination_id
and a.expenditure_category = 'Material'
and a.project_number != glcc.segment6
and pd.gl_date between '01-JAN-2015' AND '31-MAR-2015'
group by TRANSACTION_SOURCE,  glcc.segment6, a.project_number
ORDER BY 3 ASC NULLS LAST

4.) INV-MISC-PROJ ERROR- JAN TO MAR

select O.ORGANIZATION_ID, o.NAME,(mmt.ACTUAL_COST *mmt.TRANSACTION_QUANTITY) Amount, g.CONCATENATED_SEGMENTS Account ,mmt.ERROR_CODE, mmt.ERROR_EXPLANATION,mmt.PROJECT_ID, mmt.*
from MTL_MATERIAL_TRANSACTIONS MMT, HR_ORGANIZATION_UNITS O, gl_code_combinations_kfv g
where 
mmt.ORGANIZATION_ID = o.ORGANIZATION_ID
and mmt.DISTRIBUTION_ACCOUNT_ID = g.CODE_COMBINATION_ID
and mmt.ERROR_CODE is not null
and mmt.TRANSACTION_DATE between '01-JAN-2015' AND '31-MAR-2015'

5.) PO-NOPROJ-ENTRIES – JAN TO MAR

SELECT
B.TRANSACTION_ID, B.TRANSACTION_TYPE,  rcv_sub.CODE_COMBINATION_ID RCV_CCID, C.CONCATENATED_SEGMENTS RCV_ACCTS, C.SEGMENT4, C.SEGMENT6,
B.PROJECT_ID, B.TASK_ID, B.TRANSACTION_DATE,
po_dist.CODE_COMBINATION_ID POD_CCID, C1.CONCATENATED_SEGMENTS PO_ACCTS,
 po_dist.project_ID PO_PROJECT_ID, po_dist.accrue_on_receipt_flag, po_dist.expenditure_type,
D.SEGMENT1 PO_NUMBER, E.ORGANIZATION_CODE INV_ORG, F.RECEIPT_NUM, 
rcv_sub.PA_ADDITION_FLAG, rcv_sub.ACTUAL_FLAG, rcv_sub.JE_SOURCE_NAME, rcv_sub.JE_CATEGORY_NAME,
 (SELECT 'X' FROM pa_expenditure_items_all ei WHERE  ei.document_distribution_id = rcv_sub.rcv_transaction_id) RCV_SUB,
 (SELECT 'EXISTS' FROM PA_EXPEND_ITEMS_ADJUST2_V D WHERE  D.ORIG_USER_EXP_TXN_REFERENCE = TO_CHAR(B.TRANSACTION_ID) ) PA_TRNS,
(NVL(rcv_sub.ENTERED_DR,0)-NVL(rcv_sub.ENTERED_CR,0)) VALUE
FROM RCV_TRANSACTIONS B,
     rcv_receiving_sub_ledger rcv_sub,
     po_distributions_all po_dist,
     GL_CODE_COMBINATIONS_KFV C,
     GL_CODE_COMBINATIONS_KFV C1,
     PO_HEADERS_ALL D,
     MTL_PARAMETERS E,
     RCV_SHIPMENT_HEADERS F
WHERE B.TRANSACTION_ID = rcv_sub.RCV_TRANSACTION_ID
  AND rcv_sub.CODE_COMBINATION_ID = C.CODE_COMBINATION_ID
  AND po_dist.CODE_COMBINATION_ID = C1.CODE_COMBINATION_ID
  AND B.PO_HEADER_ID = D.PO_HEADER_ID
  AND B.ORGANIZATION_ID = E.ORGANIZATION_ID
  AND B.SHIPMENT_HEADER_ID = F.SHIPMENT_HEADER_ID
  AND B.DESTINATION_TYPE_CODE = 'EXPENSE'
  AND B.PO_DISTRIBUTION_ID = po_dist.PO_DISTRIBUTION_ID
  AND B.PROJECT_ID IS NULL
  AND C.SEGMENT4 != '13310'  -- Exclude the Contra of Receiving Accounting
  AND (C.SEGMENT6 != '0000' OR C1.SEGMENT6 != '0000') -- Exclude where accounting done for common Project
AND TRUNC(B.TRANSACTION_DATE) BETWEEN '01-JAN-2015' AND '31-MAR-2015'
AND NOT EXISTS (SELECT 1 FROM PA_EXPEND_ITEMS_ADJUST2_V D WHERE  D.ORIG_USER_EXP_TXN_REFERENCE = TO_CHAR(B.TRANSACTION_ID))
ORDER BY F.RECEIPT_NUM, B.TRANSACTION_DATE

6.) Pur-Missing Receipt Accr-ProjID-JAN TO MAR

SELECT
B.TRANSACTION_ID, B.TRANSACTION_TYPE,  rcv_sub.CODE_COMBINATION_ID RCV_CCID, C.CONCATENATED_SEGMENTS RCV_ACCTS, C.SEGMENT4, C.SEGMENT6,
B.PROJECT_ID, B.TASK_ID, B.TRANSACTION_DATE,
po_dist.CODE_COMBINATION_ID POD_CCID, C1.CONCATENATED_SEGMENTS PO_ACCTS,
 po_dist.project_ID PO_PROJECT_ID, po_dist.accrue_on_receipt_flag, po_dist.expenditure_type,
D.SEGMENT1 PO_NUMBER, E.ORGANIZATION_CODE INV_ORG, F.RECEIPT_NUM, 
rcv_sub.PA_ADDITION_FLAG, rcv_sub.ACTUAL_FLAG, rcv_sub.JE_SOURCE_NAME, rcv_sub.JE_CATEGORY_NAME,
 (SELECT 'X' FROM pa_expenditure_items_all ei WHERE  ei.document_distribution_id = rcv_sub.rcv_transaction_id) RCV_SUB,
 (SELECT 'EXISTS' FROM PA_EXPEND_ITEMS_ADJUST2_V D WHERE  D.ORIG_USER_EXP_TXN_REFERENCE = TO_CHAR(B.TRANSACTION_ID) ) PA_TRNS,
(NVL(rcv_sub.ENTERED_DR,0)-NVL(rcv_sub.ENTERED_CR,0)) VALUE
FROM RCV_TRANSACTIONS B,
     rcv_receiving_sub_ledger rcv_sub,
     po_distributions_all po_dist,
     GL_CODE_COMBINATIONS_KFV C,
     GL_CODE_COMBINATIONS_KFV C1,
     PO_HEADERS_ALL D,
     MTL_PARAMETERS E,
     RCV_SHIPMENT_HEADERS F
WHERE B.TRANSACTION_ID = rcv_sub.RCV_TRANSACTION_ID
  AND rcv_sub.CODE_COMBINATION_ID = C.CODE_COMBINATION_ID
  AND po_dist.CODE_COMBINATION_ID = C1.CODE_COMBINATION_ID
  AND B.PO_HEADER_ID = D.PO_HEADER_ID
  AND B.ORGANIZATION_ID = E.ORGANIZATION_ID
  AND B.SHIPMENT_HEADER_ID = F.SHIPMENT_HEADER_ID
  AND B.DESTINATION_TYPE_CODE = 'EXPENSE'
  AND B.PO_DISTRIBUTION_ID = po_dist.PO_DISTRIBUTION_ID
  AND B.PROJECT_ID IS NOT NULL
  AND C.SEGMENT4 != '13310'  -- Exclude the Contra of Receiving Accounting
  AND (C.SEGMENT6 != '0000' OR C1.SEGMENT6 != '0000') -- Exclude where accounting done for common Project
AND TRUNC(B.TRANSACTION_DATE) BETWEEN '01-JAN-2015' AND '31-MAR-2015'
AND NOT EXISTS (SELECT 1 FROM PA_EXPEND_ITEMS_ADJUST2_V D WHERE  D.ORIG_USER_EXP_TXN_REFERENCE = TO_CHAR(B.TRANSACTION_ID))
ORDER BY F.RECEIPT_NUM, B.TRANSACTION_DATE


7.) AP Item date & GL date mismatch – JAN TO MAR

select p.SEGMENT1 Prj_no,g.segment4 Acct, d.AMOUNT,d.BASE_AMOUNT Func_amt, i.INVOICE_NUM,i.INVOICE_CURRENCY_CODE CUR, i.INVOICE_AMOUNT,d.ACCOUNTING_DATE, d.EXPENDITURE_ITEM_DATE, g.CONCATENATED_SEGMENTS Combination
from ap_invoice_distributions d, ap_invoices i,  pa_projects p, gl_code_combinations_kfv g
where d.INVOICE_ID = i.INVOICE_ID
and d.PROJECT_ID = p.PROJECT_ID
and d.DIST_CODE_COMBINATION_ID = g.CODE_COMBINATION_ID
and g.segment4 NOT in (25470,25480)
and d.ACCOUNTING_DATE between '01-JAN-2015' AND '31-MAR-2015'
and d.EXPENDITURE_ITEM_DATE >'31-MAR-2015'
UNION
select p.SEGMENT1 Prj_no,g.segment4 Acct,d.AMOUNT, d.BASE_AMOUNT Func_amt, i.INVOICE_NUM,i.INVOICE_CURRENCY_CODE CUR,i.INVOICE_AMOUNT,d.ACCOUNTING_DATE, d.EXPENDITURE_ITEM_DATE, g.CONCATENATED_SEGMENTS Combination
from ap_invoice_distributions d, ap_invoices i,  pa_projects p, gl_code_combinations_kfv g
where d.INVOICE_ID = i.INVOICE_ID
and d.PROJECT_ID = p.PROJECT_ID
and d.DIST_CODE_COMBINATION_ID = g.CODE_COMBINATION_ID
and g.segment4 NOT in (25470,25480)
and d.EXPENDITURE_ITEM_DATE between '01-JAN-2015' AND '31-MAR-2015'
and d.ACCOUNTING_DATE >'31-MAR-2015'

Link Between AP(Account Payable),GL(General Ledger) and SLA(Sub Ledger Accounting _XLA)

Complete relation Query for SLA(Sub Ledger Accounting _XLA),AP(Account Payable) and GL(General Ledger) Links in oracle apps r12 

Or 

Link Between AP(Account Payable),GL(General Ledger) and SLA(Sub Ledger Accounting _XLA)

SELECT DISTINCT GJH.PERIOD_NAME, TRUNC (GJH.CREATION_DATE) GL_DATE,

                GJH.JE_SOURCE, GJH.JE_CATEGORY, GJH.CURRENCY_CODE,

                GJL.DESCRIPTION, GJL.REFERENCE_5, GJL.REFERENCE_10,

                GJL.ACCOUNTED_CR, GJL.ACCOUNTED_DR, GJL.EFFECTIVE_DATE,

                GJL.REFERENCE_1, GCC.SEGMENT1, GCC.SEGMENT2, GCC.SEGMENT3,

                GCC.SEGMENT4, GCC.SEGMENT5, GCC.SEGMENT6, GCC.SEGMENT7,

                AERLA.JUSTIFICATION, AERLA.PROJECT_NUMBER,

                AERLA.EXPENDITURE_ITEM_DATE, AERLA.RECEIPT_CURRENCY_CODE

           FROM APPS.GL_JE_HEADERS GJH,

                APPS.GL_JE_LINES GJL,

                APPS.GL_CODE_COMBINATIONS GCC,

                APPS.GL_IMPORT_REFERENCES GIR,

                APPS.XLA_AE_LINES XAL,

                APPS.XLA_AE_HEADERS XAH,

                APPS.AP_INVOICES_ALL AIA,

                APPS.AP_INVOICE_LINES_ALL AILA,

                APPS.AP_INVOICE_DISTRIBUTIONS_ALL AIDA,

                APPS.AP_EXPENSE_REPORT_HEADERS_ALL AERHA,

                APPS.AP_EXPENSE_REPORT_LINES_ALL AERLA

--APPS.XLA_EVENTS XE,

--XLA.XLA_TRANSACTION_ENTITIES XTE,

--APPS.XLA_DISTRIBUTION_LINKS XDL,

WHERE           GJH.JE_HEADER_ID = GJL.JE_HEADER_ID

            AND GJL.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID

            AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID

            AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM

            AND GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID

            AND GIR.GL_SL_LINK_TABLE = XAL.GL_SL_LINK_TABLE

            AND XAL.APPLICATION_ID = XAH.APPLICATION_ID

            AND AIA.INVOICE_ID = AILA.INVOICE_ID

            AND AIA.INVOICE_ID = AIDA.INVOICE_ID

            AND AILA.LINE_NUMBER = AIDA.DISTRIBUTION_LINE_NUMBER

            AND AERHA.REPORT_HEADER_ID = AERLA.REPORT_HEADER_ID

            AND AIA.INVOICE_ID = AERHA.VOUCHNO

            AND AIDA.INVOICE_LINE_NUMBER = AERLA.DISTRIBUTION_LINE_NUMBER

            AND AIDA.ACCOUNTING_EVENT_ID = XAH.EVENT_ID

            AND XAL.AE_HEADER_ID = XAH.AE_HEADER_ID

            AND (    GJH.JE_SOURCE = 'Payables'

                 AND GJH.JE_CATEGORY = 'Purchase Invoices'

                )

            AND GJH.CURRENCY_CODE = 'INR'

--AND XAH.EVENT_ID=XE.EVENT_ID

--AND XAH.APPLICATION_ID=XE.APPLICATION_ID

--AND XE.ENTITY_ID=XTE.ENTITY_ID

--AND XE.APPLICATION_ID=XTE.APPLICATION_ID

--AND XTE.SOURCE_ID_INT_1=AIA.INVOICE_ID

--AND XAH.APPLICATION_ID=XTE.APPLICATION_ID

--AND XAH.ENTITY_ID=XTE.ENTITY_ID

--AND XDL.APPLICATION_ID=XAH.APPLICATION_ID

--AND AIDA.INVOICE_DISTRIBUTION_ID=XDL.SOURCE_DISTRIBUTION_ID_NUM_1

--AND XDL.AE_HEADER_ID=XAH.AE_HEADER_ID

--AND XE.EVENT_ID=AIDA.ACCOUNTING_EVENT_ID

--AND AERHA.INVOICE_NUM LIKE '%12345%'

--AND GJH.PERIOD_NAME = 'AUG-15'

Find AP invoice Data in AP, XLA and GL tables by using Invoice_ID

AP:

Find AP Invoice Summary Data:

SELECT ai.invoice_id,
  SUBSTR(ai.invoice_num,1,25) invoice_num,
  SUBSTR(aps.vendor_name,1,25) vendor_name,
  ai.invoice_date,
  ai.invoice_amount,
  ai.base_amount,
  SUBSTR(ai.invoice_type_lookup_code,1,15) invoice_type_lookup_code,
  SUBSTR(ai.invoice_currency_code,1,3) invoice_currency_code,
  SUBSTR(ai.payment_currency_code,1,3) payment_currency_code,
  ai.legal_entity_id,
  ai.org_id
FROM AP_INVOICES_ALL ai,
  AP_SUPPLIERS aps,
  AP_SUPPLIER_SITES_ALL avs
WHERE ai.invoice_id   = '&Invoice_ID'
AND ai.vendor_id      = aps.vendor_id(+)
and AI.VENDOR_SITE_ID = AVS.VENDOR_SITE_ID(+)

ORDER BY ai.invoice_id ASC;

Find AP Invoice Lines Summary:

SELECT LINE_NUMBER,
  LINE_TYPE_LOOKUP_CODE,
  LINE_SOURCE,
  ACCOUNTING_DATE,
  PERIOD_NAME,
  AMOUNT,
  SUMMARY_TAX_LINE_ID,
  DEFERRED_ACCTG_FLAG,
  ORG_ID
FROM AP_INVOICE_LINES_ALL
where INVOICE_ID = '&Invoice_ID'
ORDER BY LINE_NUMBER ASC;

Find AP Invoice Distributions Summary:

SELECT INVOICE_ID,
  INVOICE_LINE_NUMBER,
  SUBSTR(DISTRIBUTION_LINE_NUMBER,1,8) DISTRIBUTION_LINE_NUMBER,
  SUBSTR(LINE_TYPE_LOOKUP_CODE,1,9) LINE_TYPE_LOOKUP_CODE,
  ACCOUNTING_DATE,
  PERIOD_NAME,
  AMOUNT,
  BASE_AMOUNT,
  POSTED_FLAG,
  MATCH_STATUS_FLAG,
  ENCUMBERED_FLAG,
  HISTORICAL_FLAG,
  SUBSTR(DIST_CODE_COMBINATION_ID,1,15) DIST_CODE_COMBINATION_ID,
  SUBSTR(ACCOUNTING_EVENT_ID,1,15) ACCOUNTING_EVENT_ID,
  SUBSTR(BC_EVENT_ID,1,15) BC_EVENT_ID,
  SUBSTR(INVOICE_DISTRIBUTION_ID,1,15) INVOICE_DISTRIBUTION_ID,
  SUBSTR(PARENT_REVERSAL_ID,1,15) PARENT_REVERSAL_ID,
  SUBSTR(PO_DISTRIBUTION_ID,1,15) PO_DISTRIBUTION_ID,
  SUMMARY_TAX_LINE_ID,
  DETAIL_TAX_DIST_ID,
  ORG_ID
FROM AP_INVOICE_DISTRIBUTIONS_ALL
WHERE INVOICE_ID = '&Invoice_ID'
order by INVOICE_LINE_NUMBER,
  DISTRIBUTION_LINE_NUMBER ASC;

XLA

Find AP Invoice data in XLA_EVENTS table:

SELECT DISTINCT XE.*
FROM AP_INVOICES_ALL AI ,
  XLA_EVENTS XE ,
  XLA.XLA_TRANSACTION_ENTITIES XTE
WHERE XTE.APPLICATION_ID        =200
AND XE.APPLICATION_ID           =200
AND AI.INVOICE_ID               ='&Invoice_ID'
AND XTE.LEDGER_ID               =AI.SET_OF_BOOKS_ID
AND XTE.ENTITY_CODE             ='AP_INVOICES'
AND NVL(XTE.SOURCE_ID_INT_1,-99)=AI.INVOICE_ID
AND XTE.ENTITY_ID               =XE.ENTITY_ID
order by XE.ENTITY_ID ,
  XE.EVENT_NUMBER;

Find AP Invoice data in XLA_AE_HEADERS table:

SELECT DISTINCT XEH.*
FROM XLA_AE_HEADERS XEH ,
  AP_INVOICES_ALL AI ,
  XLA_EVENTS XE ,
  XLA.XLA_TRANSACTION_ENTITIES XTE
WHERE XTE.APPLICATION_ID        =200
AND XEH.APPLICATION_ID          =200
AND XE.APPLICATION_ID           =200
AND XE.ENTITY_ID                =XTE.ENTITY_ID
AND XE.EVENT_ID                 =XEH.EVENT_ID
AND XTE.ENTITY_ID               =XEH.ENTITY_ID
AND AI.INVOICE_ID               ='&Invoice_ID'
AND XTE.LEDGER_ID               =AI.SET_OF_BOOKS_ID
AND XTE.ENTITY_CODE             ='AP_INVOICES'
AND NVL(XTE.SOURCE_ID_INT_1,-99)=AI.INVOICE_ID
order by XEH.EVENT_ID ,
  XEH.AE_HEADER_ID ASC;

Find AP Invoice data in XLA_AE_LINES table:

 select distinct XEL.*
  FROM XLA_AE_LINES XEL ,
  XLA_AE_HEADERS XEH ,
  AP_INVOICES_ALL AI ,
  XLA_EVENTS XE ,
  XLA.XLA_TRANSACTION_ENTITIES XTE
WHERE XTE.APPLICATION_ID        =200
AND XEL.APPLICATION_ID          =200
AND XEH.APPLICATION_ID          =200
AND XE.APPLICATION_ID           =200
AND AI.INVOICE_ID               ='&Invoice_ID'
AND XE.ENTITY_ID                =XTE.ENTITY_ID
AND XE.EVENT_ID                 =XEH.EVENT_ID
AND XEL.AE_HEADER_ID            =XEH.AE_HEADER_ID
AND XTE.ENTITY_CODE             ='AP_INVOICES'
AND NVL(XTE.SOURCE_ID_INT_1,-99)=AI.INVOICE_ID
AND XTE.ENTITY_ID               =XEH.ENTITY_ID
AND XTE.LEDGER_ID               =AI.SET_OF_BOOKS_ID
order by XEL.AE_HEADER_ID ,
  XEL.AE_LINE_NUM ASC;

General Ledger:

Find AP Invoice Data in GL_JE_BATCHES table:

SELECT DISTINCT GJB.*
FROM GL_IMPORT_REFERENCES GIR,
  GL_JE_BATCHES GJB,
  XLA_AE_LINES AEL,
  XLA_AE_HEADERS AEH,
  XLA_EVENTS AEA
WHERE AEA.EVENT_ID IN
  (SELECT AID.ACCOUNTING_EVENT_ID
  FROM AP_INVOICE_DISTRIBUTIONS_ALL AID
  WHERE AID.INVOICE_ID = '&Invoice_ID'
  )
AND AEL.GL_SL_LINK_ID    = GIR.GL_SL_LINK_ID
AND AEL.GL_SL_LINK_TABLE =GIR.GL_SL_LINK_TABLE
AND AEA.APPLICATION_ID   = 200
AND AEH.APPLICATION_ID   = 200
AND AEL.APPLICATION_ID   = 200
AND AEA.EVENT_ID         = AEH.EVENT_ID
AND AEH.AE_HEADER_ID     = AEL.AE_HEADER_ID
AND GJB.JE_BATCH_ID      = GIR.JE_BATCH_ID;

Find AP Invoice data in GL_JE_HEDAERS Table:

SELECT DISTINCT GJH.*
FROM GL_IMPORT_REFERENCES GIR,
  GL_JE_HEADERS GJH,
  XLA_AE_LINES AEL,
  XLA_AE_HEADERS AEH,
  XLA_EVENTS AEA
WHERE AEA.EVENT_ID IN
  (SELECT AID.ACCOUNTING_EVENT_ID
  FROM AP_INVOICE_DISTRIBUTIONS_ALL AID
  WHERE AID.INVOICE_ID = '&Invoice_ID'
  )
AND AEL.GL_SL_LINK_ID    = GIR.GL_SL_LINK_ID
AND AEL.GL_SL_LINK_TABLE =GIR.GL_SL_LINK_TABLE
AND AEA.APPLICATION_ID   = 200
AND AEH.APPLICATION_ID   = 200
AND AEL.APPLICATION_ID   = 200
AND AEA.EVENT_ID         = AEH.EVENT_ID
and AEH.AE_HEADER_ID     = AEL.AE_HEADER_ID
AND GJH.JE_HEADER_ID     = GIR.JE_HEADER_ID;

Find AP Invoice Data in GL_JE_LINES Table:

SELECT DISTINCT GLL.*
FROM GL_IMPORT_REFERENCES GIR,
  GL_JE_LINES GLL,
  XLA_AE_LINES AEL,
  XLA_AE_HEADERS AEH,
  XLA_EVENTS AEA
WHERE AEA.EVENT_ID IN
  (SELECT AID.ACCOUNTING_EVENT_ID
  FROM AP_INVOICE_DISTRIBUTIONS_ALL AID
  WHERE AID.INVOICE_ID = '&Invoice_ID'
  )
AND AEL.GL_SL_LINK_ID    = GIR.GL_SL_LINK_ID
AND AEL.GL_SL_LINK_TABLE =GIR.GL_SL_LINK_TABLE
AND AEA.APPLICATION_ID   = 200
AND AEH.APPLICATION_ID   = 200
AND AEL.APPLICATION_ID   = 200
AND AEA.EVENT_ID         = AEH.EVENT_ID
AND AEH.AE_HEADER_ID     = AEL.AE_HEADER_ID
and GLL.JE_HEADER_ID     = GIR.JE_HEADER_ID
AND GLL.JE_LINE_NUM      = GIR.JE_LINE_NUM;

Find AP Invoice Data in GL_IMPORT_REFERENCES Table:

SELECT DISTINCT GIR.*
FROM GL_IMPORT_REFERENCES GIR,
  XLA_AE_LINES AEL,
  XLA_AE_HEADERS AEH,
  XLA_EVENTS AEA
WHERE AEA.EVENT_ID IN
  (SELECT AID.ACCOUNTING_EVENT_ID
  FROM AP_INVOICE_DISTRIBUTIONS_ALL AID
  WHERE AID.INVOICE_ID = '&Invoice_ID'
  )
AND AEL.GL_SL_LINK_ID    = GIR.GL_SL_LINK_ID
AND AEL.GL_SL_LINK_TABLE =GIR.GL_SL_LINK_TABLE
AND AEA.APPLICATION_ID   = 200
AND AEH.APPLICATION_ID   = 200
AND AEL.APPLICATION_ID   = 200
and AEA.EVENT_ID         = AEH.EVENT_ID
AND AEH.AE_HEADER_ID     = AEL.AE_HEADER_ID;

Find The Account Code Combinations used for a specific AP Invoice:

SELECT DISTINCT GCC.*
FROM GL_CODE_COMBINATIONS GCC
WHERE GCC.CODE_COMBINATION_ID IN
  ( SELECT DISTINCT XEL.CODE_COMBINATION_ID
  FROM XLA_AE_LINES XEL ,
    XLA_AE_HEADERS XEH ,
    AP_INVOICES_ALL AI ,
    XLA.XLA_TRANSACTION_ENTITIES XTE
  WHERE XTE.APPLICATION_ID        =200
  AND XEH.APPLICATION_ID          =200
  AND XEL.APPLICATION_ID          =200
  AND AI.INVOICE_ID               ='1317327'
  AND XEL.AE_HEADER_ID            =XEH.AE_HEADER_ID
  AND XTE.ENTITY_CODE             ='AP_INVOICES'
  AND NVL(XTE.SOURCE_ID_INT_1,-99)=AI.INVOICE_ID
  AND XTE.LEDGER_ID               =AI.SET_OF_BOOKS_ID
  AND XTE.ENTITY_ID               =XEH.ENTITY_ID
  UNION ALL
  SELECT DISTINCT XEL.CODE_COMBINATION_ID
  FROM XLA_AE_LINES XEL ,
    XLA_AE_HEADERS XEH ,
    AP_INVOICE_PAYMENTS_ALL AIP ,
    XLA.XLA_TRANSACTION_ENTITIES XTE
  WHERE XTE.APPLICATION_ID        =200
  AND XEL.APPLICATION_ID          =200
  AND XEH.APPLICATION_ID          =200
  AND AIP.INVOICE_ID              ='1317327'
  AND XEL.AE_HEADER_ID            =XEH.AE_HEADER_ID
  AND XTE.ENTITY_CODE             ='AP_PAYMENTS'
  AND XTE.LEDGER_ID               =AIP.SET_OF_BOOKS_ID
  AND NVL(XTE.SOURCE_ID_INT_1,-99)=AIP.CHECK_ID
  AND XTE.ENTITY_ID               =XEH.ENTITY_ID
  UNION ALL
  SELECT DISTINCT PO.CODE_COMBINATION_ID
  FROM AP_INVOICE_DISTRIBUTIONS_ALL AID ,
    PO_DISTRIBUTIONS_ALL PO
  WHERE AID.INVOICE_ID        ='1317327'
  AND AID.PO_DISTRIBUTION_ID IS NOT NULL
  and PO.PO_DISTRIBUTION_ID   =AID.PO_DISTRIBUTION_ID
  );

AP-SLA-GL Link Query

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80

SELECT     aia.INVOICE_ID "Invoice Id",     aia.INVOICE_NUM "Invoice Number",     aia.INVOICE_DATE "Invoice Date",     aia.INVOICE_AMOUNT "Amount",     xal.ENTERED_DR "Entered DR in SLA",     xal.ENTERED_CR "Entered CR in SLA",     xal.ACCOUNTED_DR "Accounted DR in SLA",     xal.ACCOUNTED_CR "Accounted CR in SLA",     gjl.ENTERED_DR "Entered DR in GL",     gjl.ACCOUNTED_DR "Accounted DR in GL",     xal.ACCOUNTING_CLASS_CODE "Accounting Class",     gcc.SEGMENT1||'.'||gcc.SEGMENT2||'.'         ||gcc.SEGMENT3||'.'||gcc.SEGMENT4||'.'         ||gcc.SEGMENT5||'.'||gcc.SEGMENT6||'.'         ||gcc.SEGMENT7 "Code Combination",     aia.INVOICE_CURRENCY_CODE "Inv Curr Code",     aia.PAYMENT_CURRENCY_CODE "Pay Curr Code",     aia.GL_DATE "GL Date",     xah.PERIOD_NAME "Period",     aia.PAYMENT_METHOD_CODE "Payment Method",     aia.VENDOR_ID "Vendor Id",     aps.VENDOR_NAME "Vendor Name",     xah.JE_CATEGORY_NAME "JE Category Name" FROM     apps.ap_invoices_all aia,     xla.xla_transaction_entities XTE,     apps.xla_events xev,     apps.xla_ae_headers XAH,     apps.xla_ae_lines XAL,     apps.GL_IMPORT_REFERENCES gir,     apps.gl_je_headers gjh,     apps.gl_je_lines  gjl,     apps.gl_code_combinations gcc,     apps.ap_suppliers aps,     (select aid1.invoice_id,             pa.project_id,             nvl(pa.segment1,'NO PROJECT') Project     from    apps.ap_invoice_distributions_all aid1,             apps.PA_PROJECTS_ALL pa     where aid1.rowid in         (select MAx(rowid)         from apps.ap_invoice_distributions_all aid2         where aid1.INvoice_ID=aid2.INvoice_ID         group by aid1.invoice_id)     and aid1.project_id=pa.project_id(+)) sql1,     (select aid1.invoice_id,             pt.task_id,             nvl(pt.task_number,'NO TASK') Task     from    apps.ap_invoice_distributions_all aid1,             apps.PA_TASKS pt     where aid1.rowid in         (select MAx(rowid)         from apps.ap_invoice_distributions_all aid2         where aid1.INvoice_ID=aid2.INvoice_ID         group by aid1.invoice_id)     and aid1.task_id=pt.task_id(+)) sql2 WHERE     aia.INVOICE_ID = xte.source_id_int_1     and aia.INVOICE_ID=sql1.Invoice_ID     and aia.INVOICE_ID=sql2.Invoice_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 XAH.gl_transfer_status_code= 'Y'     and XAL.GL_SL_LINK_ID=gir.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 gjh.JE_HEADER_ID=gir.JE_HEADER_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.STATUS='P'     and gjh.Actual_flag='A'     and gjh.CURRENCY_CODE='USD'     and aia.Invoice_id=&Invoice_Id;