Ap Invoice to XLA Ref

Ap Invoice to XLA Ref Link


SELECT distinct ai.invoice_num, ai.gl_date, xah.accounting_date,
             gjh.je_category, gjh.je_source, gjh.period_name, gjh.status,
             aid.invoice_line_number, aid.line_type_lookup_code,
             ail.description, aid.amount, aid.dist_code_combination_id
        FROM apps.gl_je_headers gjh,
             apps.gl_je_lines gjl,
             apps.gl_import_references gir,
             apps.xla_ae_lines xal,
             apps.xla_ae_headers xah,
             apps.xla_events xe,
             apps.xla_event_types_tl xet,
             apps.xla_event_classes_tl xect,
             apps.xla_distribution_links xdl,
             apps.ap_invoice_distributions_all aid,
             apps.ap_invoices_all ai,
             apps.ap_invoice_lines_all ail
       WHERE gjh.je_header_id = gjl.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 gir.gl_sl_link_id = xal.gl_sl_link_id
         AND xal.ae_header_id = xah.ae_header_id
         AND xah.event_id = xe.event_id
         AND xe.event_type_code = xet.event_type_code
         AND xe.application_id = xet.application_id
         AND xet.LANGUAGE = USERENV ('LANG')
         AND xect.event_class_code = xet.event_class_code
         AND xect.application_id = xe.application_id
         AND xect.LANGUAGE = USERENV ('LANG')
         AND xah.ae_header_id = xdl.ae_header_id
         AND xal.ae_line_num = xdl.ae_line_num
         AND xdl.source_distribution_type = 'AP_INV_DIST'
         AND xdl.source_distribution_id_num_1 = aid.invoice_distribution_id
         AND ai.invoice_id = aid.invoice_id
         AND ai.invoice_id = ail.invoice_id
         AND ail.invoice_id = aid.invoice_id
         AND aid.invoice_line_number = ail.line_number
         AND xah.event_type_code <> ' MANUAL'
         AND gjh.je_source = 'Payables'
         AND ai.org_id = p_org_id
         AND xah.accounting_date BETWEEN p_period_start_date AND p_period_end_date;