E-Business Suite

Hi Experts,

We are using R12.2.3

We created an invoice and invoice details are as below

After invoice is created I done create accounting using 'Actions' button, but selected as Create Accounting > Final. So, by this data will be moved to SLA tables, but not to GL. The distribution details are as below

After the accounting is done as I said before, the accounting details are as below (totally 6 lines)

So, we are trying to fetch the accounting details using a query. I used the query as below, but it is not working Can anyone please help me on how to achieve this

select DISTINCT

    _**poh.segment1 po\_number, poh.comments description,**_

            _**poh.creation\_date po\_date, ai.invoice\_num invoice\_number,**_

            _**ai.invoice\_date invoice\_date,**_

            _**ai.invoice\_amount invoice\_amount,**_

            _**xel.accounting\_class\_code accounting\_class,**_

            _**xel.accounting\_date,**_

            _**xel.entered\_dr, xel.entered\_cr, xel.accounted\_cr,**_

            _**xel.accounted\_dr, xel.currency\_code,**_

            _**xel.currency\_conversion\_date, xel.gain\_or\_loss\_flag**_

from ap_invoices_all ai,ap_invoice_lines_all ail,ap_invoice_distributions_all apid,po_headers_all poh

    _**,xla.xla\_transaction\_entities xte,xla\_ae\_headers xeh,xla\_ae\_lines xel,xla.xla\_events xe**_

-- ,xla.xla_distribution_links xdl

where 1=1

and ai.quick_po_header_id IS NULL

and ai.invoice_num='11Jun2019-Inv1'

and ai.invoice_id=ail.invoice_id

and ail.po_header_id=poh.po_header_id(+)

and ai.invoice_id=apid.invoice_id

--AND poh.segment1 = NVL(:P_PO_NUMBER,poh.segment1) --Lexi Param as 2=2

--AND trunc(poh.creation_date) between NVL(:P_PO_DATE_FROM,trunc(poh.creation_date)) and NVL(:P_PO_DATE_TO,trunc(poh.creation_date)) --Lexi Param as 2=2

AND NVL (xte.source_id_int_1, -99) = ai.invoice_id

AND xte.ledger_id = ai.set_of_books_id

AND xte.application_id = 200

AND xte.entity_code = 'AP_INVOICES'

AND xeh.application_id = 200

AND xte.entity_id = xeh.entity_id

AND xel.application_id = 200

AND xel.ae_header_id = xeh.ae_header_id

and xel.ae_line_num(+)=ail.line_number

AND xe.application_id = 200

and xte.entity_id=xe.entity_id

and xeh.event_id=xe.event_id

--and xeh.ae_header_id=xdl.ae_header_id

--and xeh.event_id = xdl.event_id

--and xte.application_id = xdl.application_id

--and xel.ae_line_num=xdl.ae_line_num

--and xdl.source_distribution_id_num_1=apid.invoice_distribution_id

--and xdl.applied_to_dist_id_num_1=apid.invoice_distribution_id

--order by ai.invoice_num,ail.line_number

;

Thanks in Advance,

Bommi