Oracle Query for AP to GL Transfer

AP Invoice distributions to GL transfer

    SELECT
        aia.vendor_id, aid.invoice_distribution_id,
        aia.invoice_num,
        aia.invoice_date,
        aia.creation_date,
        aid.accounting_date GL_Date,
        aid.PERIOD_NAME,
        gl.segment3 Account,
        gl.segment4 Subaccount,
        aila.po_header_id, aila.po_line_id, aila.po_release_id,aila.po_line_location_id, ACCOUNTING_LINE_CODE,
        xdl.UNROUNDED_ACCOUNTED_CR, xdl.UNROUNDED_ACCOUNTED_DR,
        aid.amount
        + NVL ((SELECT SUM (amount)
            FROM apps.ap_self_assessed_tax_dist_all
            WHERE charge_applicable_to_dist_id =aid.invoice_distribution_id),0) AP_invoice_line_amount
    FROM apps.ap_invoices_all aia,
        apps.ap_invoice_lines_all aila,
        apps.ap_invoice_distributions_all aid,
        apps.GL_CODE_COMBINATIONS gl,
        apps.XLA_DISTRIBUTION_LINKS xdl,
        apps.XLA_AE_LINES xal,
        apps.GL_IMPORT_REFERENCES GIR,
        apps.GL_JE_HEADERS GJH
    WHERE aila.invoice_id = aia.invoice_id
        and aila.line_number = aid.invoice_line_number
         and aid.period_name = '01-2019'
         and aia.invoice_num = '6017455-6-1-37-16430206'
        AND aid.invoice_id = aia.invoice_id
        AND xal.code_combination_id = gl.code_combination_id
--        AND gl.segment3 = '01101'
        AND xdl.source_distribution_id_num_1 = aid.invoice_distribution_id
        AND xdl.source_distribution_type = 'AP_INV_DIST'
        AND xdl.ae_header_id = xal.ae_header_id
        AND xdl.ae_line_num = xal.ae_line_num
        AND xdl.event_id = aid.accounting_event_id
        AND gir.gl_sl_link_id = xal.gl_sl_link_id
        AND gir.je_header_id = gjh.je_header_id
       

AP Payment distributions to GL transfer

    SELECT 

        aia.vendor_id, aid.invoice_distribution_id,

        aia.invoice_num,

        aia.invoice_date,

        aia.creation_date,

        ACCOUNTING_LINE_CODE,

        gl.concatenated_segments,

        gl.segment1 corp,

        gl.segment2 cost_center,

        gl.segment3 Account,

        gl.segment4 Subaccount,

        xdl.UNROUNDED_ACCOUNTED_CR, xdl.UNROUNDED_ACCOUNTED_DR

    FROM apps.ap_invoices_all aia, ap_invoice_payments_all aipa, 

        apps.AP_PAYMENT_HIST_DISTS aid, 

        apps.GL_CODE_COMBINATIONS_kfv gl,

        apps.XLA_DISTRIBUTION_LINKS xdl,

        apps.XLA_AE_LINES xal,

        apps.GL_IMPORT_REFERENCES GIR,

        apps.GL_JE_HEADERS GJH

    WHERE aipa.invoice_id = aia.invoice_id

         and aia.invoice_num = '6017455-6-1-37-16430206'

        AND aipa.invoice_payment_id = aid.invoice_payment_id 

        AND xal.code_combination_id = gl.code_combination_id

--        AND gl.segment3 = '01101'

        AND xdl.source_distribution_id_num_1 = aid.payment_hist_dist_id 

        AND xdl.source_distribution_type = 'AP_PMT_DIST'

        AND xdl.ae_header_id = xal.ae_header_id

        AND xdl.ae_line_num = xal.ae_line_num

        AND xdl.event_id = aid.accounting_event_id

        AND gir.gl_sl_link_id = xal.gl_sl_link_id

        AND gir.je_header_id = gjh.je_header_id