Monday 19 June 2017

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;

No comments:

Post a Comment