SELECT je_lines.
ae_header_id,
je_lines.ae_line_num,
/* NVL(je_lines.accounted_dr, 0) AS amount_dr,
NVL(je_lines.accounted_cr, 0) as amount_cr,
NVL(je_lines.entered_dr, 0) entered_dr,
NVL(je_lines.entered_cr, 0) entered_cr,*/
je_lines.description,
je_lines.currency_code,
je_headers.PERIOD_NAME,
gl_periods.PERIOD_YEAR,
gl_periods.PERIOD_NUM,
gl_periods.QUARTER_NUM,
je_headers.GL_TRANSFER_DATE,
je_lines.code_combination_id,
cc.segment1 AS company,
gl_flexfields_pkg.get_description_sql(cc.chart_of_accounts_id,
1,
cc.segment1) AS company_desc,
cc.segment2 AS cost_center,
gl_flexfields_pkg.get_description_sql(cc.chart_of_accounts_id,
2,
cc.segment2) AS cost_center_desc,
cc.segment3 AS account,
gl_flexfields_pkg.get_description_sql(cc.chart_of_accounts_id,
3,
cc.segment3) AS account_desc,
cc.segment4 AS Branch,
gl_flexfields_pkg.get_description_sql(cc.chart_of_accounts_id,
4,
cc.segment4) AS Branch_desc,
cc.segment5 AS Channel,
gl_flexfields_pkg.get_description_sql(cc.chart_of_accounts_id,
5,
cc.segment5) AS Channel_desc,
cc.segment6 AS LOB,
gl_flexfields_pkg.get_description_sql(cc.chart_of_accounts_id,
6,
cc.segment6) AS LOB_desc,
cc.segment7 AS Product,
gl_flexfields_pkg.get_description_sql(cc.chart_of_accounts_id,
7,
cc.segment7) AS Product_desc,
cc.segment8 AS Intercompany,
gl_flexfields_pkg.get_description_sql(cc.chart_of_accounts_id,
8,
cc.segment8) AS Intercompany_desc,
cc.segment9 AS future_1,
cc.segment10 AS future_2,
je_headers.application_id,
ent.entity_id,
ent.entity_code,
ent.source_id_int_1,
invs.invoice_id AS sub_trx_id,
TO_CHAR(invs.invoice_num) AS sub_trx_number,
invs.vendor_id AS sub_third_part_id,
NVL(prt.party_name, persons.display_name) AS sub_third_part_name,
ven.segment1 AS sub_third_part_num,
app.product_code,
je_headers.je_category_name,
je_headers.description AS je_header_description,
je_headers.accounting_date,
invs.created_by,
(SELECT distinct gjh.POSTED_DATE
FROM gl_je_batches GLB,
gl_je_headers gjh,
gl_je_lines gjl,
gl_import_references gir,
xla_ae_lines xlal,
xla_ae_headers xlah,
xla_events xlae,
xla_transaction_entities xlate
WHERE GLB.je_batch_id = gjh.je_batch_id
AND gjh.je_header_id = gjl.je_header_id
AND gjl.je_header_id = gir.je_header_id
AND gjl.je_line_num = gir.je_line_num
AND gir.gl_sl_link_table = xlal.gl_sl_link_table
AND gir.gl_sl_link_id = xlal.gl_sl_link_id
AND xlal.ae_header_id = xlah.ae_header_id
AND xlah.event_id = xlae.event_id
AND xlae.entity_id = xlate.entity_id
AND xlae.application_id = xlate.application_id
and xlal.ae_header_id = je_lines.ae_header_id
and xlal.gl_sl_link_id = je_lines.gl_sl_link_id
and xlal.gl_sl_link_table = je_lines.gl_sl_link_table) POSTED_DATE,
(SELECT distinct [Link] je_batch_name
FROM gl_je_batches b,
gl_je_headers h,
gl_je_lines l,
gl_import_references gir,
xla_ae_lines xlal,
xla_ae_headers xlah,
xla_events xlae
WHERE b.je_batch_id = h.je_batch_id
AND h.je_header_id = l.je_header_id
AND l.je_header_id = gir.je_header_id
AND l.je_line_num = gir.je_line_num
AND gir.gl_sl_link_table = xlal.gl_sl_link_table
AND gir.gl_sl_link_id = xlal.gl_sl_link_id
AND xlal.application_id = xlah.application_id
AND xlal.ae_header_id = xlah.ae_header_id
AND xlah.application_id = xlae.application_id
AND xlah.event_id = xlae.event_id
and xlal.ae_header_id = je_lines.ae_header_id
and xlal.gl_sl_link_id = je_lines.gl_sl_link_id
and xlal.gl_sl_link_table = je_lines.gl_sl_link_table) Journal_Batch,
(SELECT distinct h.POSTING_ACCT_SEQ_VALUE
FROM gl_je_batches b,
gl_je_headers h,
gl_je_lines l,
gl_import_references gir,
xla_ae_lines xlal,
xla_ae_headers xlah,
xla_events xlae
WHERE b.je_batch_id = h.je_batch_id
AND h.je_header_id = l.je_header_id
AND l.je_header_id = gir.je_header_id
AND l.je_line_num = gir.je_line_num
AND gir.gl_sl_link_table = xlal.gl_sl_link_table
AND gir.gl_sl_link_id = xlal.gl_sl_link_id
AND xlal.application_id = xlah.application_id
AND xlal.ae_header_id = xlah.ae_header_id
AND xlah.application_id = xlae.application_id
AND xlah.event_id = xlae.event_id
and xlal.ae_header_id = je_lines.ae_header_id
and xlal.gl_sl_link_id = je_lines.gl_sl_link_id
and xlal.gl_sl_link_table = je_lines.gl_sl_link_table) Journal_Number,
pha.segment1 po_number,
adsa.DISTRIBUTION_SET_NAME,
aila.LCM_ENABLED_FLAG,
(select REFERENCE_TYPE_VARCHAR
from CML_INVOICE_CHARGE_REF_TYPES CICRT
where CICRT.invoice_id = invs.invoice_id
and CICRT.invoice_line_number = aila.line_number) reference,
xdl.UNROUNDED_ENTERED_DR,
xdl.UNROUNDED_ENTERED_CR,
xdl.unrounded_accounted_dr,
xdl.unrounded_accounted_cr
FROM xla_ae_lines je_lines,
xla_ae_headers je_headers,
xla_distribution_links xdl,
ap_invoice_distributions_all aida,
AP_INVOICE_LINES_ALL aila,
ap_distribution_sets_all adsa,
po_headers_all pha,
fnd_application app,
gl_code_combinations cc,
xla_transaction_entities ent,
ap_invoices_all invs,
(SELECT * FROM per_person_names_f WHERE name_type = 'GLOBAL') persons,
poz_suppliers ven,
hz_parties prt,
gl_import_references gl_link,
gl_periods
WHERE je_lines.ae_header_id = je_headers.ae_header_id
and je_headers.ae_header_id = xdl.ae_header_id
and je_headers.event_id = xdl.event_id
and je_lines.ae_header_id = xdl.ae_header_id
and je_lines.ae_line_num = xdl.ae_line_num
and je_lines.application_id = xdl.application_id
and xdl.source_distribution_id_num_1 = aida.invoice_distribution_id
and xdl.source_distribution_type = 'AP_INV_DIST'
and aida.invoice_id = invs.invoice_id
AND je_headers.gl_transfer_status_code = 'Y'
AND je_headers.PERIOD_NAME = gl_periods.PERIOD_NAME
and gl_periods.PERIOD_SET_NAME = 'Macro Calendar'
AND gl_periods.PERIOD_YEAR = :P_PERIOD_YEAR
AND gl_periods.PERIOD_NUM between :P_STAR_PERIOD_NUM AND
:P_END_PERIOD_NUM
AND je_headers.application_id = app.application_id
AND je_lines.code_combination_id = cc.code_combination_id
AND je_headers.entity_id = ent.entity_id
AND ent.entity_code = 'AP_INVOICES'
AND ent.source_id_int_1 = invs.invoice_id(+)
AND invs.vendor_id = ven.vendor_id(+)
AND ven.party_id = prt.party_id(+)
AND invs.paid_on_behalf_employee_id = persons.person_id(+)
AND je_lines.gl_sl_link_id = gl_link.gl_sl_link_id
AND je_lines.gl_sl_link_table = gl_link.gl_sl_link_table
and aila.invoice_id = invs.invoice_id
and aila.invoice_id = aida.invoice_id
and aila.LINE_NUMBER = aida.INVOICE_LINE_NUMBER
and aila.DISTRIBUTION_SET_ID = adsa.DISTRIBUTION_SET_ID
and aila.PO_HEADER_ID = pha.PO_HEADER_ID(+)
and cc.segment3 = 901010206
-- AND INVS.invoice_num = '1035'
