AR Transactions - including lines and distributions

SELECT rcta.trx_number

     , rcta.customer_trx_id

     , rba.name batch

     , rcta.creation_date

     , rctta.name tx_type

     , hp.party_number party

     , hca.account_number act_no    

     , hp.party_name

     , hps.party_site_number site_num

     , rcta.request_id -- Autoinvoice Import Program

     , rbsa.name source

     , rbsa.description

     , rcta.trx_date

     , rctla.description line_descr

     , rctla.unit_selling_price

     , rctla.line_type

     , rctla.interface_line_context

     , rctla.tax_rate

     , gcc.segment1 || '*' || gcc.segment2 || '*' || gcc.segment3 || '*' || gcc.segment4 cgh_acct

  FROM ar.ra_customer_trx_all rcta

     , ar.ra_customer_trx_lines_all rctla

     , ar.ra_cust_trx_line_gl_dist_all rctlgda

     , ar.ra_batches_all rba

     , ar.ra_cust_trx_types_all rctta

     , apps.hz_cust_accounts hca

     , apps.hz_parties hp

     , ar.hz_party_sites hps

     , apps.hz_cust_acct_sites_all hcasa

     , apps.hz_cust_site_uses_all hcsua

     , ar.hz_locations hl

     , applsys.fnd_user fu1

     , applsys.fnd_user fu2

     , ar.ra_batch_sources_all rbsa

     , gl.gl_code_combinations gcc

 WHERE rcta.bill_to_customer_id = hca.cust_account_id

   AND hp.party_id = hca.party_id

   AND hp.party_id = hps.party_id

   AND hcasa.party_site_id = hps.party_site_id

   AND hca.cust_account_id = hcasa.cust_account_id

   AND hcasa.cust_acct_site_id = hcsua.cust_acct_site_id

   AND rcta.created_by = fu1.user_id

   AND rcta.last_updated_by = fu2.user_id

   AND hcsua.site_use_id = rcta.bill_to_site_use_id

   AND hps.location_id = hl.location_id

   AND rcta.batch_source_id = rbsa.batch_source_id(+)

   AND rcta.customer_trx_id = rctla.customer_trx_id

   AND rctla.customer_trx_line_id = rctlgda.customer_trx_line_id

   AND rctlgda.code_combination_id = gcc.code_combination_id

   AND rcta.cust_trx_type_id = rctta.cust_trx_type_id

   AND RCTA.BATCH_ID = RBA.BATCH_ID

--   AND RCTA.TRX_NUMBER = '123'

   AND rcta.creation_date >= '01-SEP-2012'

   AND rctla.line_type = 'LINE'

   AND rctla.description = 'Internal Award'

   AND gcc.segment1 = tbl_activity.flex_value

   AND gcc.segment2 = tbl_detail.flex_value

   AND 1 = 1;