AP-SLA-GL QUERY
SELECT
aia.INVOICE_ID "Invoice Id",
aia.INVOICE_NUM "Invoice Number",
aia.INVOICE_DATE "Invoice Date",
aia.INVOICE_AMOUNT "Amount",
xal.ENTERED_DR "Entered DR in SLA",
xal.ENTERED_CR "Entered CR in SLA",
xal.ACCOUNTED_DR "Accounted DR in SLA",
xal.ACCOUNTED_CR "Accounted CR in SLA",
gjh.je_source ,
gjl.ENTERED_DR "Entered DR in GL",
gjl.ACCOUNTED_DR "Accounted DR in GL",
xal.ACCOUNTING_CLASS_CODE "Accounting Class",
gcc.SEGMENT1||'.'||gcc.SEGMENT2||'.'
||gcc.SEGMENT3||'.'||gcc.SEGMENT4||'.'
||gcc.SEGMENT5||'.'||gcc.SEGMENT6||'.'
||gcc.SEGMENT7 "Code Combination",
aia.INVOICE_CURRENCY_CODE "Inv Curr Code",
aia.PAYMENT_CURRENCY_CODE "Pay Curr Code",
aia.GL_DATE "GL Date",
xah.PERIOD_NAME "Period",
aia.PAYMENT_METHOD_CODE "Payment Method",
aia.VENDOR_ID "Vendor Id",
aps.VENDOR_NAME "Vendor Name",
xah.JE_CATEGORY_NAME "JE Category Name"
FROM
apps.ap_invoices_all aia,
xla.xla_transaction_entities XTE,
apps.xla_events xev,
apps.xla_ae_headers XAH,
apps.xla_ae_lines XAL,
apps.GL_IMPORT_REFERENCES gir,
apps.gl_je_headers gjh,
apps.gl_je_lines gjl,
apps.gl_code_combinations gcc,
apps.ap_suppliers aps,
(select aid1.invoice_id,
pa.project_id,
nvl(pa.segment1,'NO PROJECT') Project
from apps.ap_invoice_distributions_all aid1,
apps.PA_PROJECTS_ALL pa
where aid1.rowid in
(select MAx(rowid)
from apps.ap_invoice_distributions_all aid2
where aid1.INvoice_ID=aid2.INvoice_ID
group by aid1.invoice_id)
and aid1.project_id=pa.project_id(+)) sql1,
(select aid1.invoice_id,
pt.task_id,
nvl(pt.task_number,'NO TASK') Task
from apps.ap_invoice_distributions_all aid1,
apps.PA_TASKS pt
where aid1.rowid in
(select MAx(rowid)
from apps.ap_invoice_distributions_all aid2
where aid1.INvoice_ID=aid2.INvoice_ID
group by aid1.invoice_id)
and aid1.task_id=pt.task_id(+)) sql2
WHERE
aia.INVOICE_ID = xte.source_id_int_1
and aia.INVOICE_ID=sql1.Invoice_ID
and aia.INVOICE_ID=sql2.Invoice_ID
and xev.entity_id= xte.entity_id
and xah.entity_id= xte.entity_id
and xah.event_id= xev.event_id
and XAH.ae_header_id = XAL.ae_header_id
-- and XAH.je_category_name = 'Purchase Invoices'
and XAH.gl_transfer_status_code= 'Y'
and XAL.GL_SL_LINK_ID=gir.GL_SL_LINK_ID
and gir.GL_SL_LINK_TABLE = xal.GL_SL_LINK_TABLE
and gjl.JE_HEADER_ID=gjh.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 gcc.CODE_COMBINATION_ID=XAL.CODE_COMBINATION_ID
and gcc.CODE_COMBINATION_ID=gjl.CODE_COMBINATION_ID
and aia.VENDOR_ID=aps.VENDOR_ID
and gjh.STATUS='P'
and gjh.Actual_flag='A'
and gjh.CURRENCY_CODE='USD'
and aia.Invoice_id=&Invoice_Id;
