GL Transactions for Payments

SELECT DISTINCT aca.payment_method_lookup_code trans_type,
                TO_CHAR (aca.check_number) invoice_num,
                TO_CHAR (aca.check_date) invoice_date,
                xle.event_type_code ponum_inv_linenum,
                ael.currency_code line_desc,
                apps.imie_remove_special_chars.remove_special_chars
                   (TO_CHAR (CASE
                                WHEN xle.event_type_code =
                                                        'PAYMENT CANCELLATION'
                                   THEN (aca.amount * -1)
                                ELSE aca.amount
                             END
                            )
                   ) requestor_qty_inv,
                NULL appr_date_unitprice, NULL doc_seq_revamt,
                NULL acct_class, NULL PERCENT,
                  DECODE (ael.entered_dr, NULL, 0, ael.entered_dr)
                - DECODE (ael.entered_cr, NULL, 0, ael.entered_cr) amount,
                  DECODE (ael.accounted_dr,
                          NULL, 0,
                          ael.accounted_dr
                         )
                - DECODE (ael.accounted_cr, NULL, 0, ael.accounted_cr)
                                                                     dist_amt
           FROM xla_ae_headers aeh,
                xla_ae_lines ael,
                xla_events xle,
                xla.xla_transaction_entities ent,              
                xla_distribution_links xdl,
                ap_checks_all aca,
                ap_payment_hist_dists aphd,
                ap_payment_history_all aph
          WHERE 1 = 1
            AND ael.application_id = aeh.application_id
            AND ael.ae_header_id = aeh.ae_header_id
            AND xle.application_id = aeh.application_id
            AND xle.event_id = aeh.event_id          
            AND ent.application_id = xle.application_id
            AND ent.entity_id = xle.entity_id
            AND xdl.ae_header_id = aeh.ae_header_id
            AND xdl.ae_line_num = ael.ae_line_num
            AND aphd.payment_hist_dist_id = xdl.source_distribution_id_num_1
            AND xdl.source_distribution_type = 'AP_PMT_DIST'
            AND ent.transaction_number = aca.check_number
            AND xdl.application_id = 200
            AND aphd.payment_history_id = aph.payment_history_id
            AND aph.check_id = aca.check_id
            AND ael.ae_header_id = p_hdrid
            AND ael.ae_line_num = p_linenum;

GL Transactions for AR

SELECT rctt.description trans_type, TO_CHAR (rct.trx_number) invoice_num,
       TO_CHAR (rct.trx_date) invoice_date,
       TO_CHAR (rcl.line_number) ponum_inv_linenum, rcl.description line_desc,
       TO_CHAR (rcl.quantity_invoiced) requestor_qty_inv,
       TO_CHAR (rcl.unit_selling_price) appr_date_unitprice,
       TO_CHAR (rcl.revenue_amount) doc_seq_revamt,
       rctg.account_class acct_class, TO_CHAR (rctg.PERCENT) PERCENT,
       (rctg.amount * -1) amount, (rctg.acctd_amount * -1) dist_amt
  FROM xla_ae_headers aeh,
       xla_ae_lines ael,
       xla_events xle,
       xla.xla_transaction_entities ent,
       xla_distribution_links xdl,
       ra_customer_trx_all rct,
       ra_customer_trx_lines_all rcl,
       ra_cust_trx_types_all rctt,
       ra_cust_trx_line_gl_dist_all rctg
 WHERE 1 = 1
   AND ael.application_id = aeh.application_id
   AND ael.ae_header_id = aeh.ae_header_id
   AND xle.application_id = aeh.application_id
   AND xle.event_id = aeh.event_id
   AND ent.application_id = xle.application_id
   AND ent.entity_id = xle.entity_id
   AND xdl.ae_header_id = aeh.ae_header_id
   AND xdl.ae_line_num = ael.ae_line_num
   AND rcl.customer_trx_line_id = rctg.customer_trx_line_id
   AND rct.customer_trx_id = rcl.customer_trx_id
   AND rctt.cust_trx_type_id = rct.cust_trx_type_id
   AND rct.trx_number = ent.transaction_number
   AND xdl.source_distribution_id_num_1 = rctg.cust_trx_line_gl_dist_id
   AND xdl.source_distribution_type = 'RA_CUST_TRX_LINE_GL_DIST_ALL'
   AND ael.ae_header_id = p_hdrid
   AND ael.ae_line_num = p_linenum;

GL Transactions for AR Adjustments

SELECT NULL trans_type, ent.transaction_number invoice_num,
       (SELECT TO_CHAR (trx_date)
          FROM ra_customer_trx_all
         WHERE customer_trx_id = aaa.customer_trx_id) invoice_date,
       NULL ponum_inv_linenum, art.description line_desc,
       NULL requestor_qty_inv, NULL appr_date_unitprice, NULL doc_seq_revamt,
       NULL acct_class, NULL PERCENT,
       (  DECODE (ada.amount_dr, NULL, 0, ada.amount_dr)
        - DECODE (ada.amount_cr, NULL, 0, ada.amount_cr)
       ) amount,
       (  DECODE (ada.acctd_amount_dr, NULL, 0, ada.acctd_amount_dr)
        - DECODE (ada.acctd_amount_cr, NULL, 0, ada.acctd_amount_cr)
       ) dist_amt
  FROM xla_ae_headers aeh,
       xla_ae_lines ael,
       xla_events xle,
       xla.xla_transaction_entities ent,
       xla_distribution_links xdl,
       ar_distributions_all ada,
       ar_payment_schedules_all aps,
       ar_adjustments_all aaa,
       ar_receivables_trx_all art
 WHERE 1 = 1
   AND ael.application_id = aeh.application_id
   AND ael.ae_header_id = aeh.ae_header_id
   AND xle.application_id = aeh.application_id
   AND xle.event_id = aeh.event_id
   AND ent.application_id = xle.application_id
   AND ent.entity_id = xle.entity_id
   AND xdl.ae_header_id = aeh.ae_header_id
   AND xdl.ae_line_num = ael.ae_line_num
   AND aaa.payment_schedule_id = aps.payment_schedule_id
   AND aaa.receivables_trx_id = art.receivables_trx_id
   AND xdl.source_distribution_id_num_1 = ada.line_id
   AND ent.source_id_int_1 = aaa.adjustment_id
   AND ael.ae_header_id = p_hdrid
   AND ael.ae_line_num = p_linenum;

GL Transactions for AP

SELECT /*+ leading (ael aeh) */
       aia.invoice_type_lookup_code trans_type,
       ent.transaction_number invoice_num,
       TO_CHAR (aia.invoice_date) invoice_date,
       TO_CHAR (pod.po_number) ponum_inv_linenum,
       CASE
          WHEN aia.SOURCE = 'RECURRING INVOICE'
             THEN aia.description
          ELSE aid.description
       END line_desc,
       TO_CHAR (pod.requested_by) requestor_qty_inv,
       TO_CHAR (pod.approved_date) appr_date_unitprice,
       TO_CHAR (aia.doc_sequence_value) doc_seq_revamt, NULL acct_class,
       NULL PERCENT, NULL amount,
       DECODE (aid.base_amount, NULL, aid.amount, aid.base_amount) dist_amt
  FROM xla_ae_headers aeh,
       xla_ae_lines ael,
       xla_events xle,
       xla.xla_transaction_entities ent,
       xla_distribution_links xdl,
       apps.ap_invoices_all aia,
       apps.ap_invoice_distributions_all aid,
       (SELECT poh.segment1 po_number, poh.approved_date approved_date,
               pod.po_distribution_id po_dist_id, pod.code_combination_id,
               pap.full_name requested_by
          FROM apps.po_headers_all poh,
               apps.po_distributions_all pod,
               (SELECT pap1.person_id, pap2.full_name
                  FROM (SELECT   MAX (effective_end_date) end_date,
                                 person_id person_id
                            FROM apps.per_all_people_f pap
                        GROUP BY person_id) pap1,
                       (SELECT full_name, person_id,
                               effective_end_date end_date
                          FROM apps.per_all_people_f) pap2
                 WHERE pap1.person_id = pap2.person_id
                   AND pap1.end_date = pap2.end_date) pap
         WHERE poh.po_header_id = pod.po_header_id
           AND pod.deliver_to_person_id = pap.person_id) pod
 WHERE 1 = 1
   AND ael.application_id = aeh.application_id
   AND ael.ae_header_id = aeh.ae_header_id
   AND xle.application_id = aeh.application_id
   AND xle.event_id = aeh.event_id
   AND ent.application_id = xle.application_id
   AND ent.entity_id = xle.entity_id
   AND xdl.ae_header_id = aeh.ae_header_id
   AND xdl.ae_line_num = ael.ae_line_num
   AND ent.transaction_number = aia.invoice_num
   AND xdl.source_distribution_id_num_1 = aid.invoice_distribution_id
   AND xdl.source_distribution_type = 'AP_INV_DIST'
   AND aid.invoice_id = aia.invoice_id
   AND ent.entity_code = 'AP_INVOICES'
   AND NVL (aid.amount, 0) <> 0
   AND aid.po_distribution_id = pod.po_dist_id(+)
   AND ael.ae_header_id = p_hdrid
   AND ael.ae_line_num = p_linenum;