XLA events XLA headers , XLA lines and AP payments join

with event as (
                  select /*+ materialize */
                       xe.entity_id ,
                       sum(xl.accounted_cr) accounted_dr,
             avg(case when xe.event_type_code in ('PAYMENT CREATED','PAYMENT CANCELLED') then 1 else 0 end) ex_type,
                         max(xh.accounting_date) event_date
                  from
                       xla_events xe,
                       xla_ae_headers xh,
                       xla_ae_lines xl,
                       gl_ledgers lg
                  where /*xe.event_date <= to_date('30/04/2018', 'dd/mm/yyyy')
                        and */xh.accounting_date <= to_date('30/04/2018', 'dd/mm/yyyy')
                        and xe.event_status_code= decode(xe.event_type_code, 'PAYMENT CANCELLED', xe.event_status_code, 'P')
                        and xe.process_status_code='P'
                        and xh.event_id=xe.event_id
                        and xh.ae_header_id=xl.ae_header_id
                        and xl.accounting_class_code='CASH'
                        and xh.ledger_id=lg.ledger_id
                        and lg.ledger_category_code='PRIMARY'
                      --  and xe.entity_id=64139354
                  group by xe.entity_id
                  )
select /*+ materialize */
                   aip.invoice_id,
                 
                  xe.entity_id,
                   xe.event_id,
                   xe.event_date,
                   e.event_date,
                   aip.amount,
                   accounted_dr
 
                 
                 
              from
                    xla_events xe inner join ap_invoice_payments_all aip
                                             on xe.event_id=aip.accounting_event_id
                     
                                  inner join event e
                                             on  xe.entity_id=e.entity_id
                                           
                                       
                                           and e.event_date<=to_date('30/04/2018', 'dd/mm/yyyy')
                                  inner join ap_checks_all ca       
                                             on aip.check_id=ca.check_id
                                  left join  gl_daily_rates dr
                                             on ca.currency_code=dr.from_currency
                                             and dr.to_currency='USD'
                                             and xe.event_date=dr.conversion_date

How to get AP invoice payments using XLA events

with event as (
select /*+ materialize */
entity_id ,
max(xe.event_date) event_date
from
xla_events xe

where xe.event_date <= sysdate
and xe.event_status_code= decode(xe.event_type_code, 'PAYMENT CANCELLED', xe.event_status_code, 'P')
and xe.process_status_code='P'
group by entity_id




)
select
aip.invoice_id,
sum(aip.amount) payment_amount






from
xla_events xe inner join ap_invoice_payments_all aip
on xe.event_id=aip.accounting_event_id
inner join event e
on xe.entity_id=e.entity_id
and xe.event_date<=e.event_date
inner join ap_checks_all ca
on aip.check_id=ca.check_id


group by aip.invoice_id

AP PAYMENT TABLES TO XLA TABLES LINK

XLA_EVENTS
SELECT
DISTINCT xe.*
FROM ap_invoice_payments_all aip,
xla_events xe,
xla.xla_transaction_entities xte
WHERE xte.application_id = 200
AND xte.application_id = xe.application_id
AND aip.invoice_id = '166014'
AND xte.entity_code = 'AP_PAYMENTS'
AND xte.source_id_int_1 = aip.check_id
AND xte.entity_id = xe.entity_id
ORDER BY
xe.entity_id,
xe.event_number;

XLA_AE_HEADERS
SELECT DISTINCT xeh.*
FROM xla_ae_headers xeh,
ap_invoice_payments_all aip,
xla.xla_transaction_entities xte
WHERE xte.application_id = 200
AND xte.application_id = xeh.application_id
AND aip.invoice_id = '166014'
AND xte.entity_code = 'AP_PAYMENTS'
AND xte.source_id_int_1 = aip.check_id
AND xte.entity_id = xeh.entity_id
ORDER BY
xeh.event_id,
xeh.ae_header_id ASC;

XLA_AE_LINES
SELECT DISTINCT xel.*,
fnd_flex_ext.get_segs('SQLGL','GL#', '50577' ,xel.code_combination_id) "Account"
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 = xeh.application_id
AND xte.application_id = xeh.application_id
AND aip.invoice_id = '166014'
AND xel.ae_header_id = xeh.ae_header_id
AND xte.entity_code = 'AP_PAYMENTS'
AND xte.source_id_int_1 = aip.check_id
AND xte.entity_id = xeh.entity_id
ORDER BY
xel.ae_header_id,
xel.ae_line_num ASC;

XLA_DISTRIBUTION_LINKS

SELECT DISTINCT xdl.*
FROM xla_distribution_links xdl,
xla_ae_headers xeh,
ap_invoice_payments_all aip,
xla.xla_transaction_entities xte
WHERE xte.application_id = 200
AND xdl.application_id = xeh.application_id
AND xte.application_id = xeh.application_id
AND aip.invoice_id = '166014'
AND xdl.ae_header_id = xeh.ae_header_id
AND xte.entity_code = 'AP_PAYMENTS'
AND xte.source_id_int_1 = aip.check_id
AND xte.entity_id = xeh.entity_id