AP-SLA-GL Link Query

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80

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",     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;