PO XLA GL and RCV XLA GL queries in Oracle Applications

/* Formatted on 2017/08/08 14:31 (Formatter Plus v4.8.8) */
SELECT poh.org_id, poh.segment1 po_num, pol.line_num, poll.shipment_num, por.release_num, pod.distribution_num, NVL (poll.price_override, pol.unit_price) unit_price, pod.quantity_ordered, pod.quantity_delivered, pod.quantity_billed,
       pod.quantity_cancelled, xdl.accounting_line_code, xdl.line_definition_code, xdl.event_class_code, xal.accounted_dr, xal.accounted_cr, jeh.ledger_id, jeb.NAME, jeb.creation_date, jeh.je_source, jeh.je_category, jel.je_line_num,
       NVL (jel.accounted_dr, 0) accounted_dr, NVL (jel.accounted_cr, 0) accounted_cr, glcc.segment1, glcc.segment2, glcc.segment3, glcc.segment4, glcc.segment5, glcc.segment6
  FROM apps.po_headers_all poh,
       apps.po_lines_all pol,
       apps.po_line_locations_all poll,
       apps.po_distributions_all pod,
       apps.po_releases_all por,
       apps.xla_distribution_links xdl,
       apps.xla_ae_lines xal,
       apps.gl_import_references gif,
       apps.gl_je_lines jel,
       apps.gl_je_headers jeh,
       apps.gl_je_batches jeb,
       apps.gl_code_combinations glcc
 WHERE 1 = 1
   AND xdl.ae_line_num = xal.ae_line_num
   AND xdl.ae_header_id = xal.ae_header_id
   AND xal.gl_sl_link_table = gif.gl_sl_link_table
   AND xal.gl_sl_link_id = gif.gl_sl_link_id
   AND gif.je_header_id = jel.je_header_id
   AND gif.je_line_num = jel.je_line_num
   AND jel.code_combination_id = glcc.code_combination_id
   AND jel.je_header_id = jeh.je_header_id
   AND jeh.je_batch_id = jeb.je_batch_id
   AND xdl.source_distribution_type = 'PO_DISTRIBUTIONS_ALL'
   AND xdl.application_id = 201
   AND pod.po_distribution_id = xdl.source_distribution_id_num_1
   AND pod.line_location_id = poll.line_location_id
   AND poll.po_line_id = pol.po_line_id
   AND pol.po_header_id = poh.po_header_id
   AND pod.po_release_id = por.po_release_id(+)

Query to find GL XLA AP Link invoices in Oracle Applications

SELECT   SUM (xal.accounted_cr), SUM (xal.accounted_dr), aia.invoice_amount,
         invoice_num
    FROM ap_invoices_all aia,
         xla.xla_transaction_entities xte,
         xla_ae_headers xah,
         xla_ae_lines xal,
         gl.gl_import_references gir,
         gl_je_lines gjl,
         gl_je_headers gjh
   WHERE 1 = 1
     AND aia.invoice_id = NVL ("SOURCE_ID_INT_1", (-99))
     AND xte.entity_code = 'AP_INVOICES'
     AND xte.application_id = 200
     AND xte.entity_id = xah.entity_id
     AND xah.ae_header_id = xal.ae_header_id
     AND xal.gl_sl_link_id = gir.gl_sl_link_id
     AND xal.gl_sl_link_table = gir.gl_sl_link_table
     AND gir.je_header_id = gjl.je_header_id
     AND gir.je_line_num = gjl.je_line_num
     AND gjl.je_header_id = gjh.je_header_id
     AND aia.invoice_num = 'XXXXXX'                           --Invoice Number
GROUP BY aia.invoice_num, aia.invoice_amount

Supplier Accrual Balance Query in Oracle Applications

SELECT   fv.flex_value_meaning company, fv.description company_description,
         cc.segment4 ACCOUNT,
         NVL (sp.vendor_name, gs.user_je_source_name) supplier,
         sp.segment1 supplier_number,
         sp.vendor_type_lookup_code supplier_type, pt.NAME payment_terms,
         SUM (  NVL (NVL (xal.accounted_dr, gjl.accounted_dr), 0)
              - NVL (NVL (xal.accounted_cr, gjl.accounted_cr), 0)
             ) net
    FROM xla.xla_transaction_entities xte,
         xla.xla_ae_headers xah,
         xla.xla_ae_lines xal,
         gl.gl_import_references gir,
         gl.gl_je_lines gjl,
         gl.gl_je_headers gjh,
         gl.gl_code_combinations cc,
         gl.gl_je_batches gb,
         apps.gl_je_sources_vl gs,
         apps.ap_suppliers sp,
         apps.fnd_flex_value_sets vs,
         apps.fnd_flex_values_vl fv,
         apps.ap_terms_vl pt,
         apps.gl_je_categories_vl gc,
         gl.gl_periods gp
   WHERE 1 = 1
     -- AND xte.entity_code = 'AP_INVOICES'         --'AP_INVOICES' --'AP_PAYMENTS'
     AND NVL (xte.application_id, 200) = 200
     AND xte.entity_id(+) = xah.entity_id
     AND xah.ae_header_id(+) = xal.ae_header_id
     AND xal.gl_sl_link_id(+) = gir.gl_sl_link_id
     AND xal.gl_sl_link_table(+) = gir.gl_sl_link_table
     AND sp.vendor_id(+) = xal.party_id
     AND gir.je_header_id = gjl.je_header_id
     AND gir.je_line_num = gjl.je_line_num
     AND gjl.je_header_id = gjh.je_header_id
     AND gjh.je_source = gs.je_source_name
     AND gjh.period_name = gp.period_name
     AND gp.start_date >= '01-JAN-2015'
     AND gp.end_date < '01-MAR-2017'
--            AND  gp.period_name = 'FEB 2017'
--            and gp.period_set_name = 'USG CALENDAR'
     AND gjh.je_category = gc.je_category_name
     AND gc.je_category_name IN ('Treasury', 'Purchase Invoices', 'Payments')
--   AND xal.accounting_date(+) < TO_DATE ('01-APR-2017', 'DD-MON-YYYY')
--   AND xal.accounting_date(+) >= TO_DATE ('01-JAN-2017', 'DD-MON-YYYY')
--   AND xal.code_combination_id(+) = cc.code_combination_id
--   AND xal.ledger_id(+) = gjh.ledger_id
     AND cc.chart_of_accounts_id = 50241
     AND cc.segment4 = '201001'
     AND cc.segment1 = '101'
     AND gjh.je_batch_id = gb.je_batch_id
     AND gjl.code_combination_id = cc.code_combination_id
     AND vs.flex_value_set_name = 'XXX_CO'
     AND fv.flex_value_set_id = vs.flex_value_set_id
     AND fv.flex_value = cc.segment1
     AND pt.term_id(+) = sp.terms_id
GROUP BY fv.flex_value_meaning,
         fv.description,
         NVL (sp.vendor_name, gs.user_je_source_name),
         sp.segment1,
         cc.segment4,
         sp.vendor_type_lookup_code,
         pt.NAME
ORDER BY 2, 3, 4;