AR Transactions - Header Only

-- TRANSACTION HEADERS ONLY

 

SELECT rcta.trx_number

     , rcta.complete_flag complete

     , rcta.request_id

     , fu1.description created_by    

     , '## SOURCE ##'

     , rbsa.name source_name

     , rbsa.description

     , '## STATS ##'

     , fu2.description updated_by

     , '## ACCOUNT ##'

     , hp.party_name

     , hca.account_number act_no

     , hps.party_site_number site_num

     , rcta.org_id

     , '## DATES ##'

     , rcta.trx_date

     , rcta.creation_date

     , rcta.last_update_date

     , '## PRINT ##'

     , rcta.printing_original_date print_first

     , rcta.printing_last_printed print_last

     , '## ADDRESS ##'

     , hcsua.location

     , hl.address1

     , hl.address2

     , hl.address3

     , hl.address4

     , hl.city

     , hl.state

     , hl.postal_code

     , hl.country

  FROM apps.ra_customer_trx_all rcta

     , 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

 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.creation_date > '10-DEC-2012'

   AND 1 = 1;