AP_Distribution_With_GL_Batch_Names Query

select *
from (
SELECT org.name,
  v.vendor_name,
  v.segment1 vendor_num,
  vs.vendor_site_code,
  api.invoice_num,
  api.doc_sequence_value voucher_num,
  api.invoice_type_lookup_code,
  api.invoice_date ,
  api.tax_invoice_recording_date fulfillment_date,
  api.invoice_currency_code,
  apd.accounting_date,
  'DIST' acc_source,
  apd.distribution_line_number line_number,
  apd.line_type_lookup_code,
  zxc.tax_rate_code,
  u1.user_name created_by,
  u2.user_name last_updated_by,
  glv.concatenated_segments gl_flexfield,
  glv.segment1 company,
  glv.segment2 location,
  glv.segment3 product,
  glv.segment4 FUNCTION,
  glv.segment5 gl_account,
  glv.segment6 intercompany,
  glv.segment7 subaccount,
  apd.description,
  apd.posted_flag,
  apd.amount,
  apd.base_amount,
  gl_link1.gl_batch_name primary_gl_batch,
  gl_link2.gl_batch_Name secondary_gl_batch
FROM
  apps.ap_invoices_all api ,
  apps.ap_suppliers v ,
  apps.ap_supplier_sites_all  vs ,
  apps.ap_invoice_distributions_all apd ,
  apps.hr_organization_units org ,
  apps.gl_code_combinations_kfv glv ,
  apps.fnd_user u1 ,
  apps.fnd_user u2 ,
  apps.zx_rates_vl zxc,
  (
  select
gle.name ledger_name,
gle.ledger_category_code,
glb.name gl_batch_name,
xdl.source_distribution_id_num_1
from apps.xla_distribution_links xdl,
 apps.ap_invoice_distributions_all apd,
 apps.xla_ae_lines xal,
 apps.xla_ae_headers xah,
 apps.gl_import_references gli,
 apps.gl_je_lines gll,
 apps.gl_je_headers glh,
 apps.gl_je_batches glb,
 apps.gl_ledgers gle
where 1=1
and xal.ae_header_id = xah.ae_header_id
and xah.ledger_id = gll.ledger_id
and xdl.source_distribution_type = 'AP_INV_DIST'
and xdl.accounting_line_code <> 'AP_LIAB_INV'
and xdl.application_id = 200
and xdl.source_distribution_id_num_1 = apd.invoice_distribution_id
-- and apd.invoice_id = 4726023
and xdl.ae_header_id = xal.ae_header_id
and xdl.ae_line_num = xal.ae_line_num
and xal.gl_sl_link_id = gli.gl_sl_link_id
and gli.je_header_id = gll.je_header_id
and gli.je_line_num = gll.je_line_num
and gll.je_header_id = glh.je_header_id
and glh.je_batch_id = glb.je_batch_id
and gle.ledger_id = gll.ledger_id
) gl_link1,
 (
  select
gle.name ledger_name,
gle.ledger_category_code,
glb.name gl_batch_name,
xdl.source_distribution_id_num_1
from apps.xla_distribution_links xdl,
 apps.ap_invoice_distributions_all  apd,
 apps.xla_ae_lines xal,
 apps.xla_ae_headers xah,
 apps.gl_import_references gli,
 apps.gl_je_lines gll,
 apps.gl_je_headers glh,
 apps.gl_je_batches glb,
 apps.gl_ledgers gle
where 1=1
and xal.ae_header_id = xah.ae_header_id
and xah.ledger_id = gll.ledger_id
and xdl.source_distribution_type = 'AP_INV_DIST'
and xdl.accounting_line_code <> 'AP_LIAB_INV'
and xdl.application_id = 200
and xdl.source_distribution_id_num_1 = apd.invoice_distribution_id
-- and apd.invoice_id = 4726023
and xdl.ae_header_id = xal.ae_header_id
and xdl.ae_line_num = xal.ae_line_num
and xal.gl_sl_link_id = gli.gl_sl_link_id
and gli.je_header_id = gll.je_header_id
and gli.je_line_num = gll.je_line_num
and gll.je_header_id = glh.je_header_id
and glh.je_batch_id = glb.je_batch_id
and gle.ledger_id = gll.ledger_id
) gl_link2
WHERE 1                =1
and gl_link1.source_distribution_id_num_1(+)  = apd.invoice_distribution_id
and gl_link1.ledger_category_code = 'PRIMARY'
and gl_link2.source_distribution_id_num_1(+)  = apd.invoice_distribution_id
and gl_link2.ledger_category_code = 'SECONDARY'
AND api.invoice_id     = apd.invoice_id
AND api.org_id         = org.organization_id
AND api.vendor_site_id = vs.vendor_site_id(+)
AND vs.vendor_id       = v.vendor_id
AND apd.dist_code_combination_id = glv.code_combination_id(+)
  --and apd.amount <> 0
AND apd.created_by      = u1.user_id(+)
AND apd.last_updated_by = u2.user_id(+)
AND apd.tax_code_id     = zxc.tax_rate_id(+)
UNION
SELECT org.name,
  v.vendor_name,
  v.segment1 vendor_num,
  vs.vendor_site_code,
  api.invoice_num,
  api.doc_sequence_value voucher_num,
  api.invoice_type_lookup_code,
  api.invoice_date ,
  api.tax_invoice_recording_date fulfillment_date,
  api.invoice_currency_code,
  api.gl_date accounting_date,
  'LINE',
  apl.line_number,
  apl.line_type_lookup_code,
  zxl.tax_rate_code,
  u1.user_name created_by,
  u2.user_name last_updated_by,
  glv.concatenated_segments gl_flexfield,
  glv.segment1 company,
  glv.segment2 location,
  glv.segment3 product,
  glv.segment4 FUNCTION,
  glv.segment5 gl_account,
  glv.segment6 intercompany,
  glv.segment7 subaccount,
  apI.description,
  'N' posted_flag,
  apl.amount + nvl(zxl.tax_amt, 1),
  decode(api.invoice_currency_code,
              gll.currency_code, apl.amount+ nvl(zxl.tax_amt, 1),
              (apl.amount * api.exchange_rate) + nvl(zxl.tax_amt_funcl_curr,0)
              ) base_amount ,
 NULL, NULL
FROM
  apps.ap_invoices_all api ,
  apps.ap_invoice_lines_all apl,
  apps.ap_suppliers v ,
  apps.ap_supplier_sites_all vs ,
  apps.hr_organization_units org ,
  apps.gl_code_combinations_kfv glv ,
  apps.fnd_user u1 ,
  apps.fnd_user u2 , apps.zx_lines_v zxl, apps.financials_system_params_all p, apps.gl_ledgers gll
WHERE 1                =1
AND api.invoice_id     = apl.invoice_id
and apl.line_type_lookup_code = 'ITEM'
AND not exists (select 'x' from apps.ap_invoice_distributions_all apd where apd.invoice_id = api.invoice_id)
AND api.org_id         = org.organization_id
AND api.vendor_site_id = vs.vendor_site_id(+)
AND vs.vendor_id       = v.vendor_id
AND apl.default_dist_ccid = glv.code_combination_id(+)
  --and apd.amount <> 0
and 2=2
and apl.invoice_id=zxl.trx_id(+)
and zxl.application_id(+) = 200
and zxl.entity_code(+) = 'AP_INVOICES'
and zxl.event_class_code(+) in ('STANDARD INVOICES', 'PREPAYMENT INVOICES', 'EXPENSE REPORTS')
and apl.line_number=zxl.trx_line_number(+)
and 3=3
AND apl.created_by      = u1.user_id(+)
AND apl.last_updated_by = u2.user_id(+)
and 4=4
and p.set_of_books_id = gll.ledger_id
and p.org_id = apl.org_id
) invdist
where 1=1
AND invdist.accounting_date BETWEEN to_date(:P_GL_Date_From, 'DD-MON-RR hh24:mi:ss')
                                              AND to_date(:P_GL_Date_To, 'DD-MON-RR hh24:mi:ss')

ORDER BY 1,  5,  11;