Posted By

theonlyalterego on 01/21/12

Tagged

Oracle

Versions (?)

Last Edited at 01/21/12 09:04am

Statistics

Viewed 945 times

Favorited by 0 user(s)

Related snippets

Oracle AP-SLA-GL Link Query

/ Published in: SQL

AP-SLA-GL Link Query

Highlighted TextExpand

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
    ap.ap_invoices_all aia,
    xla.xla_transaction_entities XTE,
    xla.xla_events xev,
    xla.xla_ae_headers XAH,
    xla.xla_ae_lines XAL,
    GL_IMPORT_REFERENCES gir,
    gl_je_headers gjh,
    gl_je_lines  gjl,
    gl_code_combinations gcc,
    ap.ap_suppliers aps,
    (select aid1.invoice_id,
            pa.project_id,
            nvl(pa.segment1,'NO PROJECT') Project
    from    ap_invoice_distributions_all aid1,
            PA_PROJECTS_ALL pa
    where aid1.rowid in
        (select MAx(rowid)
        from 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    ap_invoice_distributions_all aid1,
            PA_TASKS pt
    where aid1.rowid in
        (select MAx(rowid)
        from 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;

URL: http://imdjkoch.wordpress.com/2010/12/25/ap-sla-gl-link-query/

Report this snippet Tweet

Comments

Subscribe to comments

Comment:

You need to login to post a comment.