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;