Monday, 19 June 2017

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;

No comments:

Post a Comment