Wednesday, August 31, 2016

Query to Get Oracle Payable Account Details

SELECT   gl.NAME ledger_name, xah.period_name AS gl_period, ai.SOURCE,
         accounting_class_code, ai.invoice_date AS trx_date,
         ai.invoice_num trx_num,
         (SELECT segment1
            FROM po_headers_all pha
           WHERE pha.po_header_id = aila1.po_header_id) po_number,
         aila1.po_header_id, aila1.po_line_id,
         (SELECT line_num
            FROM po_lines_all pla
           WHERE po_header_id = aila1.po_header_id
             AND po_line_id = aila1.po_line_id) po_line,
         aila1.po_distribution_id, gcc.concatenated_segments AS gl_account,
         ai.invoice_currency_code AS entered_currency,
         gl.currency_code functional_currency,
         SUM (NVL ((xdl1.unrounded_entered_dr), 0)) entered_dr,
         SUM (NVL ((xdl1.unrounded_entered_cr), 0)) entered_cr,
         SUM (NVL ((xdl1.unrounded_accounted_dr), 0)) accounted_dr,
         SUM (NVL ((xdl1.unrounded_accounted_cr), 0)) accounted_cr
    FROM gl_ledgers gl,
         hr_operating_units hou,
         ap_invoices_all ai,
         ap_invoice_lines_all aila1,
         ap_invoice_distributions_all aida1,
         xla.xla_ae_headers PARTITION (ap) xah,
         xla.xla_ae_lines PARTITION (ap) xal,
         xla.xla_transaction_entities PARTITION (ap) xte1,
         xla_distribution_links PARTITION (ap) xdl1,
         gl_code_combinations_kfv gcc
   WHERE 1 = 1
     AND gl.ledger_id = hou.set_of_books_id
     AND ai.org_id = hou.organization_id
     AND xdl1.ae_header_id = xal.ae_header_id
     AND xdl1.ae_line_num = xal.ae_line_num
     AND xdl1.source_distribution_id_num_1 = aida1.invoice_distribution_id
     AND aila1.invoice_id = ai.invoice_id
     AND aida1.invoice_id = aila1.invoice_id
     AND aida1.invoice_line_number = aila1.line_number
     AND xte1.source_id_int_1 = ai.invoice_id
     AND xte1.entity_id = xah.entity_id
     AND xah.ae_header_id = xal.ae_header_id
     AND xah.application_id = xte1.application_id
     AND xal.application_id = xah.application_id
     AND gcc.code_combination_id = xal.code_combination_id
     AND xte1.entity_code = 'AP_INVOICES'
--     and xal.accounting_class_code ='ACCRUAL'
--     AND aida1.line_tyPe_lookup_code = 'ACCRUAL'
     AND ai.SOURCE = 'Intercompany'
--     AND ai.org_id = 204
     AND xte1.application_id = 200
GROUP BY gl.NAME,
         xah.period_name,
         ai.SOURCE,
         accounting_class_code,
         ai.invoice_date,
         ai.invoice_num,
         gcc.concatenated_segments,
         ai.invoice_currency_code,
         gl.currency_code,
         aila1.po_header_id,
         aila1.po_line_id,

         aila1.po_distribution_id

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect