Friday, December 19, 2014

AR Invoice Details Query (Till GL)



SELECT trx.trx_number,
       trx.cust_trx_type_id,

       trx.bill_to_customer_id,
       trx_dist.amount ar_inv_dist_amount,
       trx_dist.code_combination_id ar_inv_dist_ccid,
          gcc.segment1
       || '.'
       || gcc.segment2
       || '.'
       || gcc.segment3
       || '.'
       || gcc.segment4
       || '.'
       || gcc.segment5
       || '.'
       || gcc.segment6
       || '.'
       || gcc.segment7
       || '.'
       || gcc.segment8
          ar_inv_dist_code_combination,
       gjh.name gl_je_header_name,
       gjh.description gl_je_header_desc,
       gjl.je_line_num,
       gjl.code_combination_id gl_je_line_ccid
  FROM ar.ra_customer_trx_all trx,
       ar.ra_cust_trx_line_gl_dist_all trx_dist,
       gl.gl_code_combinations gcc,
       gl.gl_ledgers gl,
       xla.xla_transaction_entities xte,
       xla.xla_ae_headers xah,
       xla.xla_ae_lines xal,
       xla.xla_distribution_links xdl,
       gl.gl_import_references gir,
       gl.gl_je_headers gjh,
       gl.gl_je_lines gjl
 WHERE 1 = 1
   -- AND trx.org_id = :P_ORG_ID
   -- AND trx.batch_source_id = :P_BATCH_SOURCE_ID
   AND trx.customer_trx_id = :P_CUSTOMER_TRX_ID
   AND trx.customer_trx_id = trx_dist.customer_trx_id
   AND gcc.code_combination_id = trx_dist.code_combination_id
   --  AND gl.short_name = :P_LEDGER_SHORT_NAME
   AND gl.chart_of_accounts_id = gcc.chart_of_accounts_id
   AND xte.ledger_id = gl.ledger_id
   AND xte.entity_code = 'TRANSACTIONS'
   AND xte.application_id = 222
   AND xte.source_id_int_1 = trx.customer_trx_id
   AND xah.entity_id = xte.entity_id
   AND xal.ae_header_id = xah.ae_header_id
   AND xal.accounting_class_code = 'REVENUE'
   AND xdl.ae_header_id = xah.ae_header_id
   AND xdl.ae_line_num = xal.ae_line_num
   AND xdl.source_distribution_id_num_1 = trx_dist.cust_trx_line_gl_dist_id
   AND xdl.application_id = 222
   AND gir.gl_sl_link_id = xal.gl_sl_link_id
   AND gjh.je_header_id = gir.je_header_id
   AND gjl.je_header_id = gjh.je_header_id
   AND gjl.je_line_num = gir.je_line_num;

Pass / Comment based on available values.

No comments:

Post a Comment