Tech Trainings

Payables to Sub Ledger to General Ledger Queries

April 19, 2023 by admin

--Header , Lines , Distributions , Sub Ledger and GL Data for AP Invoices 
SELECT
    hou.name         bu_name,
    aia.invoice_num,
    hp.party_name,
    hp.party_number,
    pos.segment1     supplier_num,
    pssam.vendor_site_code,
    aia.invoice_type_lookup_code,
    aila.line_number,
    aila.description line_desc,
    aila.amount,
    aila.base_amount fun_base_amount,
    aida.distribution_line_number,
    aida.amount      dist_amount,
    aila.cancelled_flag,
    aila.accounting_date,
    gjb.name         gl_batch_name,
    gjh.name         gl_header_name,
    gjl.accounted_cr,
    gjl.accounted_dr,
    xdl.unrounded_accounted_cr,
    xdl.unrounded_accounted_dr,
    gcc.segment1
    || '.'
    || gcc.segment2
    || '.'
    || gcc.segment3
    || '.'
    || gcc.segment4
    || '.'
    || gcc.segment5
    || '.'
    || gcc.segment6  coa_segm
FROM
    ap_invoices_all              aia,
    ap_invoice_lines_all         aila,
    ap_invoice_distributions_all aida,
    hr_operating_units           hou,
    poz_suppliers                pos,
    hz_parties                   hp,
    poz_supplier_sites_all_m     pssam,
    gl_code_combinations         gcc,
    xla_events                   xe,
    xla_distribution_links       xdl,
    xla_ae_lines                 xal,
    xla_ae_headers               xah,
    gl_import_references         gir,
    gl_je_batches                gjb,
    gl_je_headers                gjh,
    gl_je_lines                  gjl
WHERE
        1 = 1
    AND aia.invoice_id = aila.invoice_id
    AND aida.invoice_id = aila.invoice_id
    AND aida.invoice_line_number = aila.line_number
    AND aia.org_id = hou.organization_id
    AND pos.party_id = hp.party_id
    AND pos.vendor_id = aia.vendor_id
    AND aia.vendor_site_id = pssam.vendor_site_id
    AND xe.event_id = aida.accounting_event_id
    AND xdl.source_distribution_id_num_1 = aida.invoice_distribution_id
    AND xdl.ae_header_id = xah.ae_header_id
    AND xdl.ae_line_num = xal.ae_line_num
    AND xal.ae_header_id = xah.ae_header_id
    AND gir.gl_sl_link_id = xal.gl_sl_link_id
    AND gir.je_batch_id = gjb.je_batch_id
    AND gir.je_header_id = gjh.je_header_id
    AND gir.je_line_num = gjl.je_line_num
    AND gjb.je_batch_id = gjh.je_batch_id
    AND gjh.je_header_id = gjl.je_header_id
    AND gjh.ledger_id = gjl.ledger_id
    AND gjh.je_source = 'Payables'
    AND gjl.code_combination_id = gcc.code_combination_id
    AND aia.invoice_id = 1
ORDER BY
    aia.invoice_id

Comments |0|