AR Payment Schedules

-- ar payment_schedules

    SELECT rcta.trx_number 
         , rcta.customer_trx_id 
         , rcta.creation_date trx_created 
         , apsa.payment_schedule_id 
         , apsa.due_date 
         , apsa.amount_due_original 
         , apsa.amount_due_remaining 
         , apsa.amount_applied 
         , apsa.amount_credited 
         , apsa.customer_id 
      FROM ar.ra_customer_trx_all rcta 
      JOIN ar.ar_payment_schedules_all apsa ON apsa.customer_trx_id = rcta.customer_trx_id 
     WHERE 1 = 1 
       AND rcta.trx_number = '123456789';

AR Various

-- ##############################################################################
--      ar-transactions-requests.sql
-- ##############################################################################

SELECT DISTINCT acia.concurrent_request_id
              , acita.transaction_type
           FROM apps.ar_cons_inv_all acia
           JOIN apps.ar_cons_inv_trx_all acita ON acia.cons_inv_id = acita.cons_inv_id
          WHERE acia.concurrent_request_id IN('10078037', '10087459');

-- ##############################################################################
--      ar-resources
-- ##############################################################################

SELECT   jrdv.resource_name
       , source_number empno
       , source_job_title
       , role_v.role_type_name
       , role_v.role_name
       , group_v.group_name
    FROM apps.jtf_rs_defresources_v jrdv
    JOIN apps.jtf_rs_defresroles_vl role_v   ON jrdv.resource_id = role_v.role_resource_id
    JOIN apps.jtf_rs_defresgroups_vl group_v ON jrdv.resource_id = group_v.resource_id 
ORDER BY 1;
 
-- ##############################################################################
--      ar-customers-profile-classes.sql
-- ##############################################################################

-- ar customer profile classes
SELECT   acpcv.profile_class_name
       , acpcv.profile_class_description
       , acpcv.standard_terms_name bfb
       , acpcv.collector_name
       , acpcv.last_update_date
       , acpcv.creation_date
       , fu.description updated_by
       , '###################################'
       , acpcv.*
    FROM apps.ar_customer_profile_classes_v acpcv
    JOIN applsys.fnd_user fu ON acpcv.last_updated_by = fu.user_id 
ORDER BY acpcv.creation_date desc;

AR Transactions – Balance Summary

-- ##############################################################################
--      AR TRANSACTIONS - BALANCE SUMMARY
-- ############################################################################*/

    SELECT hca.account_number
         , hp.party_name
         , atbs.best_current_receivables
         , atbs.total_dso_days_credit
         , atbs.op_invoices_value
         , atbs.op_invoices_count
         , atbs.op_debit_memos_value
         , atbs.op_debit_memos_count
         , atbs.op_deposits_value
         , atbs.op_deposits_count
         , atbs.op_bills_receivables_value
         , atbs.op_bills_receivables_count
         , atbs.op_chargeback_value
         , atbs.op_chargeback_count
         , atbs.op_credit_memos_value
         , atbs.op_credit_memos_count
         , atbs.unresolved_cash_value
         , atbs.unresolved_cash_count
         , atbs.receipts_at_risk_value
         , atbs.inv_amt_in_dispute
         , atbs.disputed_inv_count
         , atbs.pending_adj_value
         , atbs.last_dunning_date
         , atbs.dunning_count
         , atbs.past_due_inv_value
         , atbs.past_due_inv_inst_count
         , atbs.last_payment_amount
         , atbs.last_payment_date
         , atbs.last_payment_number
         , atbs.reference_1
         , atbs.reference_2
         , atbs.reference_3
         , atbs.reference_4
         , atbs.reference_5
      FROM ar.hz_cust_accounts hca
      JOIN ar.hz_parties hp                 ON hca.party_id = hp.party_id
      JOIN ar.hz_party_sites hps            ON hp.party_id = hps.party_id
      JOIN ar.hz_cust_acct_sites_all hcasa  ON hcasa.party_site_id =        hps.party_site_id AND hca.cust_account_id = hcasa.cust_account_id
      JOIN ar.hz_cust_site_uses_all hcsua   ON hcsua.cust_acct_site_id =    hcasa.cust_acct_site_id
      JOIN ar.ar_trx_bal_summary atbs       ON hcsua.site_use_id =          atbs.site_use_id
     WHERE atbs.site_use_id = 1926016;

AR Transaction Types

-- ##############################################################################
--        AR TRANSACTION TYPES
-- ##############################################################################

    SELECT rctta.cust_trx_type_id
         , rctta.name
         , haou.name linked_hr_org
         , rctta.description
         , al2.meaning transaction_type
         , al1.meaning default_transaction_status
         , rctta.creation_date cr_dt
         , rctta.last_update_date
         , fu.description cr_by
         , rctta_cm.name credit_memo_type
         , rctta.signed_flag
         , '#############'
         , gcc_rec.concatenated_segments receivable_account
         , gcc_rev.concatenated_segments revenue_account
         , gcc_unbilled.concatenated_segments unbilled_account
         , gcc_tax.concatenated_segments tax_account
         , gcc_freight.concatenated_segments freight_account
         , gcc_clearing.concatenated_segments clearing_account
         , gcc_unearned.concatenated_segments unearned_account
      FROM ar.ra_cust_trx_types_all rctta
 LEFT JOIN hr.hr_all_organization_units haou ON rctta.name = haou.name
      JOIN applsys.fnd_user fu ON rctta.created_by = fu.user_id
 LEFT JOIN apps.ar_lookups al1 ON rctta.default_status = al1.lookup_code AND al1.lookup_type = 'INVOICE_TRX_STATUS'
 LEFT JOIN apps.ar_lookups al2 ON rctta.type = al2.lookup_code AND al2.lookup_type = 'INV/CM'
 LEFT JOIN ar.ra_cust_trx_types_all rctta_cm ON rctta.credit_memo_type_id = rctta_cm.cust_trx_type_id
 LEFT JOIN apps.gl_code_combinations_kfv gcc_rec ON gcc_rec.code_combination_id = rctta.gl_id_rec
 LEFT JOIN apps.gl_code_combinations_kfv gcc_rev ON gcc_rev.code_combination_id = rctta.gl_id_rev
 LEFT JOIN apps.gl_code_combinations_kfv gcc_unbilled ON gcc_unbilled.code_combination_id = rctta.gl_id_unbilled
 LEFT JOIN apps.gl_code_combinations_kfv gcc_tax ON gcc_tax.code_combination_id = rctta.gl_id_tax
 LEFT JOIN apps.gl_code_combinations_kfv gcc_freight ON gcc_freight.code_combination_id = rctta.gl_id_freight
 LEFT JOIN apps.gl_code_combinations_kfv gcc_clearing ON gcc_clearing.code_combination_id = rctta.gl_id_clearing
 LEFT JOIN apps.gl_code_combinations_kfv gcc_unearned ON gcc_unearned.code_combination_id = rctta.gl_id_unearned
     WHERE 1 = 1
       AND rctta.name = 'THIS'
--       AND NVL(rctta.end_date, SYSDATE + 1) > SYSDATE
--       AND rctta.creation_date > '01-JUL-2016'
--       AND rctta.last_update_date > '12-AUG-2016'
--       AND rctta.cust_trx_type_id = 14372
--       AND rctta.cust_trx_type_id IN (3277,3309)
--       AND rctta.status != 'A'
       AND 1 = 1;

AR Transactions – With Tax Info

-- ##############################################################################
--      AR TRANSACTIONS WITH TAX INFO
-- ############################################################################*/

     SELECT rcta.customer_trx_id
          , rcta.TRX_NUMBER
          , rcta.creation_date
          , rctta.name transaction_type
          , hca.ACCOUNT_NUMBER
          , hp.PARTY_NUMBER
          , hp.party_name
          , hp.party_type
          , rctla.line_type
          , rbsa.name source
          , fu.user_name
          , sum(rctla.unit_selling_price) fart
          , (select sum(extended_amount) from ar.ra_customer_trx_lines_all tx_info where rcta.customer_trx_id = tx_info.customer_trx_id and line_type = 'TAX') tax_value
       FROM ar.ra_customer_trx_all rcta
          , ar.ra_cust_trx_types_all rctta
          , ar.hz_cust_accounts hca
          , ar.hz_parties hp
          , ar.ra_customer_trx_lines_all rctla
          , ar.ra_batch_sources_all rbsa
          , applsys.fnd_user fu
      WHERE rcta.bill_to_customer_id = hca.cust_account_id
        AND hp.party_id = hca.party_id
        AND rcta.cust_trx_type_id = rctta.cust_trx_type_id
        AND rcta.customer_trx_id = rctla.customer_trx_id
        AND rcta.batch_source_id = rbsa.batch_source_id AND rcta.org_id = rbsa.org_id
        AND rcta.created_by = fu.user_id
--        AND rcta.trx_number = '123456789'
--        AND rcta.creation_date BETWEEN '27-JUN-2017' AND '30-JUN-2017'
        AND rcta.creation_date > '01-JUL-2017'
        AND hp.party_type = 'PERSON'
   group by rcta.customer_trx_id
          , rcta.TRX_NUMBER
          , rcta.creation_date
          , rctta.name
          , hca.ACCOUNT_NUMBER
          , hp.PARTY_NUMBER
          , hp.party_name
          , hp.party_type
          , rctla.line_type
          , rbsa.name
          , fu.user_name
   order by rcta.creation_date;
   

-- https://community.oracle.com/message/14434085
-- another version

     with my_data as
     (SELECT rcta.customer_trx_id
          , rcta.TRX_NUMBER
          , rcta.creation_date
          , rctta.name transaction_type
          , hca.ACCOUNT_NUMBER
          , hp.PARTY_NUMBER
          , hp.party_name
          , hp.party_type
          , rctla.line_type
          , rbsa.name source
          , fu.user_name
          , NVL(rctla.unit_selling_price, rctla.extended_amount) amt
       FROM ar.ra_customer_trx_all rcta
          , ar.ra_cust_trx_types_all rctta
          , ar.hz_cust_accounts hca
          , ar.hz_parties hp
          , ar.ra_customer_trx_lines_all rctla
          , ar.ra_batch_sources_all rbsa
          , applsys.fnd_user fu
      WHERE rcta.bill_to_customer_id = hca.cust_account_id
        AND hp.party_id = hca.party_id
        AND rcta.cust_trx_type_id = rctta.cust_trx_type_id
        AND rcta.customer_trx_id = rctla.customer_trx_id
        AND rcta.batch_source_id = rbsa.batch_source_id AND rcta.org_id = rbsa.org_id
        AND rcta.created_by = fu.user_id
        AND rctla.inventory_item_id = 23
        AND rcta.trx_number = '12345678'
--        AND rcta.creation_date BETWEEN '27-JUN-2017' AND '30-JUN-2017'
        AND rcta.creation_date > '01-JUN-2017'
        AND hp.party_name != 'Bob Nose'
        AND hp.party_type = 'PERSON'
        AND rbsa.name = 'Invoice - Non Trading'
        AND 1 = 1)
     select customer_trx_id
          , trx_number
          , source
          , creation_date
          , user_name created_bby
          , transaction_type
          , account_number
          , party_number
          , party_name
          , party_type
          , sum(case when line_type = 'LINE' then amt end) trx_total
          , sum(case when line_type = 'TAX' then amt end ) tax_total
       from my_data
   group by customer_trx_id
          , trx_number
          , source
          , creation_date
          , user_name
          , transaction_type
          , account_number
          , party_number
          , party_name
          , party_type
   order by creation_date
          , trx_number;

AR Transactions

Sections:

  1. Transaction Headers Only – Version 0
  2. Transaction Headers Only – Version 1
  3. Transaction Headers Only – Version 2
  4. Transactions Plus Lines
  5. Transactions Plus Distributions
  6. Source Summary

Transaction Headers Only – Version 0

-- ##############################################################################
--      TRANSACTION HEADERS ONLY - VERSION 0
-- ############################################################################*/

SELECT rcta.trx_number
     , rcta.customer_trx_id
     , (SELECT SUM(extended_amount) from ar.ra_customer_trx_lines_all rctla WHERE rcta.customer_trx_id = rctla.customer_trx_id) tx_value
     , (SELECT SUM(amount_due_remaining) FROM ar.ar_payment_schedules_all apsa WHERE apsa.customer_trx_id = rcta.customer_trx_id) amt_outstanding
     , rcta.complete_flag complete
     , hp.party_name
     , hca.account_number act_no
     , rcta.trx_date
     , rcta.creation_date
     , rcta.last_update_date
     , rcta.invoice_currency_code
     , TO_CHAR(ARPT_SQL_FUNC_UTIL.GET_FIRST_REAL_DUE_DATE (rcta.CUSTOMER_TRX_ID, rcta.term_id, rcta.trx_date), 'DD-MON-YYYY') due_date
  FROM ar.ra_customer_trx_all rcta
     , ar.hz_cust_accounts hca
     , ar.hz_parties hp
 WHERE rcta.bill_to_customer_id = hca.cust_account_id
   AND hp.party_id = hca.party_id
   AND rcta.trx_number = '12345678'
   AND rcta.creation_date > '01-NOV-2017'
   AND 1 = 1;

Transaction Headers Only – Version 1

-- ##############################################################################
--      TRANSACTION HEADERS ONLY - VERSION 1
-- ############################################################################*/

SELECT DISTINCT
       rcta.trx_number
     , rcta.customer_trx_id
     , rcta.complete_flag complete
--     , rcta.request_id
--     , hp.attribute2 crris_num
     , '## SOURCE ##'     
     , rbsa.name
     , rbsa.description
     , '## STATS ##'     
     , fu1.description created_by
     , 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
  FROM ar.ra_customer_trx_all rcta
     , ar.hz_cust_accounts hca
     , ar.hz_parties hp
     , ar.hz_party_sites hps
     , ar.hz_cust_acct_sites_all hcasa
     , ar.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.trx_number = dff_tbl.trx_number
--   AND rcta.attribute12 = flvl.lookup_code
   and rcta.batch_source_id = rbsa.batch_source_id(+)
--   AND rcta.customer_trx_id = 2859072
--   AND rcta.trx_number = '11111111'
--   AND rcta.trx_number IN ('11111111','11111112')
--   AND hp.tax_reference IS NOT NULL
--   AND rctta.name = 'THIS'
--   AND hca.account_number = 1234
--   AND (SELECT SUM(amount_due_remaining) FROM ar.ar_payment_schedules_all apsa WHERE apsa.customer_trx_id = rcta.customer_trx_id AND apsa.amount_due_remaining > 0) > 0
--   AND hp.party_name LIKE 'M%'
--   AND rcta.invoice_currency_code = 'GBP'
   AND rbsa.name = 'PROJECTS INVOICES'
   AND rcta.creation_date > '01-AUG-2015'
   AND rcta.creation_date < '05-AUG-2016'
--   AND rcta.customer_trx_id > 2681651
--   AND rcta.customer_trx_id IN (2472983,2471171)
   AND 1 = 1;

Transaction Headers Only – Version 2

-- ##############################################################################
--      TRANSACTION HEADERS ONLY - VERSION 2
-- ############################################################################*/

        SELECT '------ TRANSACTION HEADER -----'
             , rbsa.name source
             , rcta.trx_number "NUMBER"
             , rcta.customer_trx_id
             , al_trx_type.meaning class
             , rctta.name type
             , rcta.interface_header_attribute1 reference
             , xlolv.legal_entity_name legal_entity
             , rcta.trx_date
             , rctlgda.gl_date
             , rcta.invoice_currency_code currency
             , rcta.doc_sequence_value doc_num
             , rcta.interface_header_context context_value
             , rcta.complete_flag complete
             , rtt.name payment_term
--             , ARPT_SQL_FUNC_UTIL.GET_FIRST_REAL_DUE_DATE (rcta.CUSTOMER_TRX_ID, rcta.term_id, rcta.trx_date) due_date -- need apps to access this
             , '------ SETUP INFO -----'
             , rcta.creation_date
             , fu.user_name created_by
             , rcta.last_update_date
             , fu2.user_name updated_by
             , '------ MORE TAB -----'
             , haou.name operating_unit
             , al_trx_print.meaning print_option
             , rcta.printing_original_date print_date
             , al_trx_status.meaning status
             , rcta.purchase_order
             , rcta.special_instructions
             , rcta.comments
--             , (SELECT SUM(rctla.unit_selling_price) FROM ar.ra_customer_trx_lines_all rctla , ar.ra_cust_trx_line_gl_dist_all rctlgda WHERE rcta.customer_trx_id = rctla.customer_trx_id AND rctla.customer_trx_line_id = rctlgda.customer_trx_line_id) tx_value
--             , (SELECT SUM(amount_due_remaining) FROM ar.ar_payment_schedules_all apsa WHERE apsa.customer_trx_id = rcta.customer_trx_id AND apsa.amount_due_remaining > 0) balance_due
             , '------ NOTES TAB -----'
             , an.creation_date note_date
             , al_note_type.meaning note_type
             , an.text memo
             , '------ REFERENCE INFO TAB -----'
             , al_reason.meaning reason
             , rcta.customer_reference
             , rcta.customer_reference_date
             , rcta_prev.trx_number orig_transaction
             , '------ BILL TO CUSTOMER -----'
             , hp_bill.party_name bill_cust
             , hca_bill.account_number bill_acct
             , hca_bill.customer_class_code bill_cust_class
             , acpcvb.profile_class_description bill_profile_class
             , hp_bill.tax_reference ship_tax_ref
             , '------ SHIP TO CUSTOMER -----'
             , hp_ship.party_name ship_cust
             , hca_ship.account_number ship_acct
             , hca_ship.customer_class_code ship_cust_class
             , acpcvs.profile_class_description ship_profile_class
             , hp_ship.tax_reference bill_tax_ref
             , '------ PROJECT --------'
             , ppa.segment1 project
             , ppa.distribution_rule
             , pdia.draft_invoice_num
             , pdia.draft_invoice_num_credited
             , '------ BATCH -----'
             , rba.name batch_name
             , rba.batch_date
             , rba.gl_date batch_gl_date
             , rba.type batch_type
             , rba.control_count batch_count
             , rba.control_amount batch_control_amt
             , rba.request_id
             , rbsa.name batch_source
             , rbsa.batch_source_id
             , '------ OTHER -----'
             , rcta.attribute2 student_number
          FROM ar.ra_customer_trx_all rcta
          JOIN ar.ra_cust_trx_line_gl_dist_all rctlgda   ON rcta.customer_trx_id =          rctlgda.customer_trx_id AND rctlgda.latest_rec_flag = 'Y' 
          JOIN apps.xle_le_ou_ledger_v xlolv             ON xlolv.operating_unit_id =       rcta.legal_entity_id
          JOIN ar.ra_cust_trx_types_all rctta            ON rcta.cust_trx_type_id =         rctta.cust_trx_type_id AND rcta.org_id = rctta.org_id
          JOIN apps.ar_lookups al_trx_type               ON rctta.type =                    al_trx_type.lookup_code AND al_trx_type.lookup_type = 'INV/CM/ADJ'
          JOIN applsys.fnd_user fu                       ON rcta.created_by =               fu.user_id
          JOIN applsys.fnd_user fu2                      ON rcta.last_updated_by =          fu2.user_id
          JOIN hr.hr_all_organization_units haou         ON rcta.org_id =                   haou.organization_id
     LEFT JOIN ar.hz_cust_accounts hca_bill              ON rcta.bill_to_customer_id =      hca_bill.cust_account_id
     LEFT JOIN ar.hz_parties hp_bill                     ON hp_bill.party_id =              hca_bill.party_id
     LEFT JOIN apps.ar_customer_profile_classes_v acpcvb ON hca_bill.customer_class_code =  acpcvb.profile_class_name
     LEFT JOIN ar.hz_cust_accounts hca_ship              ON rcta.ship_to_customer_id =      hca_ship.cust_account_id
     LEFT JOIN ar.hz_parties hp_ship                     ON hp_ship.party_id =              hca_ship.party_id
     LEFT JOIN apps.ar_customer_profile_classes_v acpcvs ON hca_ship.customer_class_code =  acpcvs.profile_class_name
     LEFT JOIN ar.ar_notes an                            ON rcta.customer_trx_id =          an.customer_trx_id
     LEFT JOIN ar.ra_customer_trx_all rcta_prev          ON rcta.previous_customer_trx_id = rcta_prev.customer_trx_id
     LEFT JOIN ar.ra_batches_all rba                     ON rcta.batch_id =                 rba.batch_id
     LEFT JOIN ar.ra_batch_sources_all rbsa              ON rcta.batch_source_id =          rbsa.batch_source_id AND rcta.org_id = rbsa.org_id
     LEFT JOIN ar.ra_terms_tl rtt                        ON rcta.term_id =                  rtt.term_id                                                           -- sometimes AR transactions have no payment terms on them
     LEFT JOIN pa.pa_draft_invoices_all pdia             ON pdia.ra_invoice_number =        rcta.trx_number
     LEFT JOIN pa.pa_projects_all ppa                    ON pdia.project_id =               ppa.project_id
     LEFT JOIN apps.ar_lookups al_reason                 ON rcta.reason_code=               al_reason.lookup_code AND al_reason.lookup_type = 'CREDIT_MEMO_REASON'
     LEFT JOIN apps.ar_lookups al_trx_status             ON rcta.status_trx=                al_trx_status.lookup_code AND al_trx_status.lookup_type = 'INVOICE_TRX_STATUS'
     LEFT JOIN apps.ar_lookups al_trx_print              ON rcta.printing_option =          al_trx_print.lookup_code AND al_trx_print.lookup_type = 'INVOICE_PRINT_OPTIONS'
     LEFT JOIN apps.ar_lookups al_note_type              ON an.note_type =                  al_note_type.lookup_code AND al_note_type.lookup_type = 'NOTE_TYPE'
         WHERE 1 = 1
--           AND rcta.trx_number IN ('12345678')
--           AND rcta.customer_trx_id IN (2472983,2471171)
           AND rcta.creation_date > '01-MAY-2016'
--           AND rcta.creation_date > '07-FEB-2016'
--           AND rcta.customer_trx_id = 2578883
--           AND rcta.ship_to_customer_id IS NULL
--           AND hca_bill.account_number IN ('1234')
           AND 1 = 1;

Transactions Plus Lines

-- ##############################################################################
--      TRANSACTIONS PLUS LINES
-- ############################################################################*/

        SELECT '------ TRANSACTION HEADER -----'
             , rbsa.name source
             , rcta.trx_number "NUMBER"
             , rcta.customer_trx_id
             , al_trx_type.meaning class
             , rctta.name type
             , rcta.interface_header_attribute1 reference
             , xlolv.legal_entity_name legal_entity
             , rcta.trx_date
             , rctlgda_gl.gl_date
             , rcta.invoice_currency_code currency
             , rcta.doc_sequence_value doc_num
             , rcta.interface_header_context context_value
             , rcta.complete_flag complete
             , rtt.name payment_term
             , rcta.trx_date
             , '------ BILL TO CUSTOMER -----'
             , hp_bill.party_name bill_cust
             , hca_bill.account_number bill_acct
             , hca_bill.customer_class_code bill_cust_class
             , acpcvb.profile_class_description bill_profile_class
             , hp_bill.tax_reference ship_tax_ref
             , '------ SHIP TO CUSTOMER -----'
             , hp_ship.party_name ship_cust
             , hca_ship.account_number ship_acct
             , hca_ship.customer_class_code ship_cust_class
             , acpcvs.profile_class_description ship_profile_class
             , hp_ship.tax_reference bill_tax_ref
             , '------ LINES -----'
             , DECODE(rctla.link_to_cust_trx_line_id, NULL, rctla.line_number, rctla_line.line_number) line_number
             , al_type.meaning line_type
             , rctla.quantity_credited qty
             , rctla.unit_selling_price amount
             , rctla.tax_classification_code
             , rctla.tax_rate
             , rctla.taxable_amount
             , rctla.description
             , rctla.reason_code
          FROM ar.ra_customer_trx_all rcta
          JOIN apps.xle_le_ou_ledger_v xlolv               ON xlolv.operating_unit_id =        rcta.legal_entity_id
          JOIN ar.ra_cust_trx_types_all rctta              ON rcta.cust_trx_type_id =          rctta.cust_trx_type_id AND rcta.org_id = rctta.org_id
          JOIN apps.ar_lookups al_trx_type                 ON rctta.type =                     al_trx_type.lookup_code AND al_trx_type.lookup_type = 'INV/CM/ADJ'
          JOIN applsys.fnd_user fu                         ON rcta.created_by =                fu.user_id
          JOIN applsys.fnd_user fu2                        ON rcta.last_updated_by =           fu2.user_id
          JOIN hr.hr_all_organization_units haou           ON rcta.org_id =                    haou.organization_id
     LEFT JOIN ar.ra_customer_trx_lines_all rctla          ON rctla.customer_trx_id =          rcta.customer_trx_id
     LEFT JOIN ar.ra_cust_trx_line_gl_dist_all rctlgda_gl  ON rctla.customer_trx_id =          rctlgda_gl.customer_trx_id AND rctlgda_gl.latest_rec_flag = 'Y' 
     LEFT JOIN ar.ra_customer_trx_lines_all rctla_line     ON rctla.link_to_cust_trx_line_id = rctla_line.customer_trx_line_id
     LEFT JOIN ar.hz_cust_accounts hca_bill                ON rcta.bill_to_customer_id =       hca_bill.cust_account_id
     LEFT JOIN ar.hz_parties hp_bill                       ON hp_bill.party_id =               hca_bill.party_id
     LEFT JOIN apps.ar_customer_profile_classes_v acpcvb   ON hca_bill.customer_class_code =   acpcvb.profile_class_name
     LEFT JOIN ar.hz_cust_accounts hca_ship                ON rcta.ship_to_customer_id =       hca_ship.cust_account_id
     LEFT JOIN ar.hz_parties hp_ship                       ON hp_ship.party_id =               hca_ship.party_id
     LEFT JOIN apps.ar_customer_profile_classes_v acpcvs   ON hca_ship.customer_class_code =   acpcvs.profile_class_name
     LEFT JOIN ar.ra_customer_trx_all rcta_prev            ON rcta.previous_customer_trx_id =  rcta_prev.customer_trx_id
     LEFT JOIN ar.ra_batches_all rba                       ON rcta.batch_id =                  rba.batch_id
     LEFT JOIN ar.ra_batch_sources_all rbsa                ON rcta.batch_source_id =           rbsa.batch_source_id AND rcta.org_id = rbsa.org_id
     LEFT JOIN ar.ra_terms_tl rtt                          ON rcta.term_id =                   rtt.term_id
     LEFT JOIN pa.pa_draft_invoices_all pdia               ON pdia.ra_invoice_number =         rcta.trx_number
     LEFT JOIN pa.pa_projects_all ppa                      ON pdia.project_id =                ppa.project_id
     LEFT JOIN apps.ar_lookups al_type                     ON rctla.line_type =                al_type.lookup_code  AND al_type.lookup_type = 'STD_LINE_TYPE'
         WHERE 1 = 1
--           AND rcta.trx_number IN ('1234567')
           AND rcta.customer_trx_id IN (2472983,2471171)
--           AND hca_ship.cust_account_id <> hca_bill.cust_account_id
--           AND rcta.creation_date > '20-FEB-2015'
--           AND rcta.creation_date > '20-JAN-2016'
--           AND rcta.customer_trx_id = 2578883
--           AND rcta.ship_to_customer_id IS NULL
--           AND hca_bill.account_number IN ('123456')
           AND 1 = 1;

Transactions Plus Distributions

-- ##############################################################################
--      TRANSACTIONS PLUS DISTRIBUTIONS
-- ############################################################################*/

        SELECT '------ TRANSACTION HEADER -----'
             , rbsa.name source
             , rcta.trx_number "NUMBER"
             , rcta.customer_trx_id
             , al_trx_type.meaning class
             , rctta.name type
             , rcta.interface_header_attribute1 reference
             , xlolv.legal_entity_name legal_entity
             , rcta.trx_date
             , rctlgda.gl_date
             , rcta.invoice_currency_code currency
             , rcta.doc_sequence_value doc_num
             , rcta.interface_header_context context_value
             , rcta.complete_flag complete
             , rtt.name payment_term
             , rcta.trx_date
             , '------ BILL TO CUSTOMER -----'
             , hp_bill.party_name bill_cust
             , hca_bill.account_number bill_acct
             , hca_bill.customer_class_code bill_cust_class
             , acpcvb.profile_class_description bill_profile_class
             , hp_bill.tax_reference ship_tax_ref
             , '------ SHIP TO CUSTOMER -----'
             , hp_ship.party_name ship_cust
             , hca_ship.account_number ship_acct
             , hca_ship.customer_class_code ship_cust_class
             , acpcvs.profile_class_description ship_profile_class
             , hp_ship.tax_reference bill_tax_ref
             , '------ DISTRIBUTIONS -----'
             , rctlgda.account_class
             , gcc.segment1 || '*' || gcc.segment2 || '*' || gcc.segment3 || '*' || gcc.segment4 || '*' || gcc.segment5 || '*' || gcc.segment6 gl_account
             , rctlgda.latest_rec_flag
             , rctlgda.gl_date distrib_gl_date
             , rctlgda.gl_posted_date distrib_gl_posted
             , rctlgda.percent distrib_percent
             , rctlgda.amount distrib_amount
             , rctlgda.creation_date distrib_created
             , al_class.meaning distrib_class
             , rctlgda.comments
          FROM ar.ra_customer_trx_all rcta
          JOIN apps.xle_le_ou_ledger_v xlolv               ON xlolv.operating_unit_id =        rcta.legal_entity_id
          JOIN ar.ra_cust_trx_types_all rctta              ON rcta.cust_trx_type_id =          rctta.cust_trx_type_id AND rcta.org_id = rctta.org_id
          JOIN apps.ar_lookups al_trx_type                 ON rctta.type =                     al_trx_type.lookup_code AND al_trx_type.lookup_type = 'INV/CM/ADJ'
          JOIN applsys.fnd_user fu                         ON rcta.created_by =                fu.user_id
          JOIN applsys.fnd_user fu2                        ON rcta.last_updated_by =           fu2.user_id
          JOIN hr.hr_all_organization_units haou           ON rcta.org_id =                    haou.organization_id
     LEFT JOIN ar.ra_cust_trx_line_gl_dist_all rctlgda     ON rcta.customer_trx_id =           rctlgda.customer_trx_id
     LEFT JOIN ar.ra_cust_trx_line_gl_dist_all rctlgda_gl  ON rcta.customer_trx_id =           rctlgda_gl.customer_trx_id AND rctlgda_gl.latest_rec_flag = 'Y' 
     LEFT JOIN gl.gl_code_combinations gcc                 ON rctlgda.code_combination_id =    gcc.code_combination_id
     LEFT JOIN ar.hz_cust_accounts hca_bill                ON rcta.bill_to_customer_id =       hca_bill.cust_account_id
     LEFT JOIN ar.hz_parties hp_bill                       ON hp_bill.party_id =               hca_bill.party_id
     LEFT JOIN apps.ar_customer_profile_classes_v acpcvb   ON hca_bill.customer_class_code =   acpcvb.profile_class_name
     LEFT JOIN ar.hz_cust_accounts hca_ship                ON rcta.ship_to_customer_id =       hca_ship.cust_account_id
     LEFT JOIN ar.hz_parties hp_ship                       ON hp_ship.party_id =               hca_ship.party_id
     LEFT JOIN apps.ar_customer_profile_classes_v acpcvs   ON hca_ship.customer_class_code =   acpcvs.profile_class_name
     LEFT JOIN ar.ra_customer_trx_all rcta_prev            ON rcta.previous_customer_trx_id =  rcta_prev.customer_trx_id
     LEFT JOIN ar.ra_batches_all rba                       ON rcta.batch_id =                  rba.batch_id
     LEFT JOIN ar.ra_batch_sources_all rbsa                ON rcta.batch_source_id =           rbsa.batch_source_id AND rcta.org_id = rbsa.org_id
     LEFT JOIN ar.ra_terms_tl rtt                          ON rcta.term_id =                   rtt.term_id
     LEFT JOIN pa.pa_draft_invoices_all pdia               ON pdia.ra_invoice_number =         rcta.trx_number
     LEFT JOIN pa.pa_projects_all ppa                      ON pdia.project_id =                ppa.project_id
     LEFT JOIN apps.ar_lookups al_class                    ON rctlgda.account_class =          al_class.lookup_code AND al_class.lookup_type = 'AUTOGL_TYPE'
         WHERE 1 = 1
           AND rcta.trx_number IN ('1234567')
--           AND rcta.customer_trx_id IN (2472983,2471171)
--           AND rcta.creation_date > '06-MAY-2016'
--           AND rcta.creation_date > '20-JAN-2016'
--           AND rcta.customer_trx_id = 2578883
--           AND rcta.ship_to_customer_id IS NULL
--           AND hca_bill.account_number IN ('1234')
           AND 1 = 1;

Source Summary

-- ##############################################################################
--      SOURCE SUMMARY
-- ############################################################################*/

  SELECT rbsa.name source_name
       , TRUNC(rcta.creation_date) tx_date
       , COUNT (*) tx_ct
       , MAX (rcta.creation_date) recent_tx
    FROM apps.ra_customer_trx_all rcta
       , ar.ra_batch_sources_all rbsa
   WHERE rcta.batch_source_id = rbsa.batch_source_id
     AND rcta.creation_date >= '23-NOV-2015'
     AND 1 = 1
GROUP BY rbsa.name
       , TRUNC(rcta.creation_date)
ORDER BY TRUNC(rcta.creation_date) DESC;

AR Statement Cycles

-- ##############################################################################
--      AR STATEMENT CYCLES
-- ############################################################################*/

    SELECT ascc.name
         , ascc.description
         , ascc.interval
         , ascda.statement_date
         , ascda.creation_date
         , ascda.statement_cycle_id
         , fu.description
      FROM ar.ar_statement_cycle_dates_all ascda
      JOIN applsys.fnd_user fu                   ON fu.user_id =               ascda.created_by
      JOIN ar.ar_statement_cycles ascc           ON ascda.statement_cycle_id = ascc.statement_cycle_id 
  ORDER BY ascc.name
         , ascda.statement_date DESC
         , ascda.creation_date;

AR Receivables Activities

-- ##############################################################################
--      RECEIVABLES ACTIVITIES
-- ############################################################################*/

    SELECT arta.name
         , arta.description
         , DECODE(arta.status, 'A', 'Active', 'I', 'Inactive') status
         , arta.type
         , arta.creation_date cr_dr
         , fu.description cr_by
         , arta.asset_tax_code
         , gcc.segment1
         , arta.asset_tax_code
      FROM ar.ar_receivables_trx_all arta
      JOIN applsys.fnd_user fu         ON arta.created_by =          fu.user_id
      JOIN gl.gl_code_combinations gcc ON arta.code_combination_id = gcc.code_combination_id 
     WHERE arta.asset_tax_code LIKE '%.%';
 

AR Receipts

Sections:

  1. RECEIPTS EXCLUDING BANK ACCOUNTS INCLUDING DISTRIBS
  2. RECEIPTS EXCLUDING BANK ACCOUNTS
  3. RECEIPTS INC BANK ACCOUNTS
  4. BATCHES – BASIC INFO
  5. RECEIPTS LINKED TO BATCH
  6. BATCHES LINKED TO “Automatic Remittances Creation Program (API)” JOB

RECEIPTS EXCLUDING BANK ACCOUNTS INCLUDING DISTRIBS

-- ##############################################################################
--        RECEIPTS EXCLUDING BANK ACCOUNTS INCLUDING DISTRIBS
-- ##############################################################################

      SELECT arm.name receipt_method
           , acra.receipt_number
           , acra.creation_date
           , fu.user_name || ' (' || fu.description || ')' created_by
           , acra.last_update_date
           , fu2.user_name || ' (' || fu2.description || ')'  updated_by
           , acra.cash_receipt_id
           , acra.doc_sequence_value doc_number
           , acra.currency_code curr
           , DECODE(acra.type, 'CASH', 'Standard', 'MISC', 'Miscellaneous') receipt_type
           , al.meaning state
           , acra.receipt_date
           , acra.amount receipt_amount
           , ada.acctd_amount_cr distrib_cr
           , acra.amount - ada.acctd_amount_cr diff
           , ada.acctd_amount_dr distrib_dr
           , amcda.amount misc_cash_dist_amount
           , amcda.misc_cash_distribution_id
           , ada.source_id
           , ada.creation_date dist_created
           , ada.last_update_date dist_updated
           , gcck1.concatenated_segments account_code   
           , hca.account_number
           , hca.account_name
        FROM ar.ar_cash_receipts_all acra
   left JOIN ar.ar_receipt_methods arm          		ON acra.receipt_method_id =     arm.receipt_method_id
   left JOIN ar.ar_receipt_classes arc          		ON arm.receipt_class_id =       arc.receipt_class_id
   left JOIN apps.ar_lookups al                 		ON al.lookup_code =             acra.status AND al.lookup_type = 'CHECK_STATUS'
   left JOIN applsys.fnd_user fu                		ON acra.created_by =            fu.user_id
   left JOIN applsys.fnd_user fu2               		ON acra.last_updated_by =       fu2.user_id
   left JOIN ar.hz_cust_accounts hca            		ON acra.pay_from_customer =     hca.cust_account_id
   left join ar.ar_misc_cash_distributions_all amcda 	ON amcda.cash_receipt_id = acra.cash_receipt_id
   left join ar.ar_distributions_all ada 				ON ada.source_id = amcda.misc_cash_distribution_id and ada.source_table = 'MCD' AND ada.source_type = 'MISCCASH'
   left join apps.gl_code_combinations_kfv gcck1 		ON gcck1.code_combination_id = ada.code_combination_id
       WHERE 1 = 1
         AND acra.receipt_number IN ('111222333')
         AND acra.amount <> ada.acctd_amount_cr
         AND al.meaning = 'Applied'
--         AND acra.cash_receipt_id = 1234567
--         AND fu.user_name = 'BOBHOPE'
--         AND acra.creation_date > '01-AUG-2015'
--         AND acra.creation_date < '05-AUG-2015'
         AND 1 = 1
    ORDER BY acra.creation_date DESC;

RECEIPTS EXCLUDING BANK ACCOUNTS

-- ##############################################################################
--        RECEIPTS EXCLUDING BANK ACCOUNTS
-- ##############################################################################

      SELECT arm.name receipt_method
           , acra.receipt_number
           , acra.cash_receipt_id
           , acra.doc_sequence_value doc_number
           , acra.currency_code curr
           , acra.amount
           , DECODE(acra.type, 'CASH', 'Standard', 'MISC', 'Miscellaneous') receipt_type
           , al.meaning state
           , acra.receipt_date
           , acra.creation_date
           , acra.created_by
           , fu.user_name
           , fu.description created_by
           , acra.last_update_date
           , hca.account_number
           , hca.account_name
        FROM ar.ar_cash_receipts_all acra
   left JOIN ar.ar_receipt_methods arm          ON acra.receipt_method_id =     arm.receipt_method_id
   left JOIN ar.ar_receipt_classes arc          ON arm.receipt_class_id =       arc.receipt_class_id
   left JOIN apps.ar_lookups al                 ON al.lookup_code =             acra.status AND al.lookup_type = 'CHECK_STATUS'
   left JOIN applsys.fnd_user fu                ON acra.created_by =            fu.user_id
   left JOIN ar.hz_cust_accounts hca            ON acra.pay_from_customer =     hca.cust_account_id
       WHERE 1 = 1
         AND acra.receipt_number IN ('12345678')
--         AND acra.cash_receipt_id = 2452508
--         AND hca.account_number = 1234
--         AND fu.user_name = 'SYSADMIN'
--         AND acra.creation_date > '01-AUG-2015'
--         AND acra.creation_date < '05-AUG-2015'
         AND 1 = 1
    ORDER BY acra.creation_date DESC;

-- count

      SELECT fu.user_name
           , COUNT(*) ct
        FROM ar.ar_cash_receipts_all acra
        JOIN applsys.fnd_user fu ON acra.created_by = fu.user_id
       WHERE 1 = 1
         AND acra.creation_date > '01-MAR-2016'
         AND 1 = 1
    GROUP BY fu.user_name;

RECEIPTS INC BANK ACCOUNTS

-- ##############################################################################
--        RECEIPTS INC BANK ACCOUNTS
-- ##############################################################################

      SELECT arm.name receipt_method
           , acra.receipt_number
           , acra.cash_receipt_id
           , acra.doc_sequence_value doc_number
           , acra.currency_code curr
           , acra.amount
           , DECODE(acra.type, 'CASH', 'Standard', 'MISC', 'Miscellaneous') receipt_type
           , al.meaning state
           , acra.receipt_date
           , acra.creation_date
           , acra.created_by
           , fu.description created_by
           , acra.last_update_date
           , hca.account_number
           , hca.account_name
           , cba.bank_account_name
           , cba.bank_account_num
           , cbbv.bank_name remit_bank_name
           , cbbv.bank_branch_name remit_bank_branch
--           , DECODE (arc.creation_method_code, 'MANUAL', iebav.bank_account_number, NULL) customer_bank_account
--           , DECODE (arc.creation_method_code, 'MANUAL', iebav.bank_account_number, NULL) customer_bank_account_num
--           , DECODE (arc.creation_method_code, 'MANUAL', iebav.BANK_NAME, NULL) customer_bank_name
--           , DECODE (arc.creation_method_code, 'MANUAL', iebav.bank_branch_name, NULL) customer_bank_branch
        FROM ar.ar_cash_receipts_all acra
        JOIN ar.ar_receipt_methods arm          ON acra.receipt_method_id =     arm.receipt_method_id
        JOIN ar.ar_receipt_classes arc          ON arm.receipt_class_id =       arc.receipt_class_id
        JOIN apps.ar_lookups al                 ON al.lookup_code =             acra.status AND al.lookup_type = 'CHECK_STATUS'
        JOIN applsys.fnd_user fu                ON acra.created_by =            fu.user_id
   LEFT JOIN ar.hz_cust_accounts hca            ON acra.pay_from_customer =     hca.cust_account_id
   LEFT JOIN ce.ce_bank_acct_uses_all cbaua     ON cbaua.bank_acct_use_id =     acra.remit_bank_acct_use_id AND cbaua.org_id = acra.org_id
   LEFT JOIN ce.ce_bank_accounts cba            ON cbaua.bank_account_id =      cba.bank_account_id
   LEFT JOIN apps.ce_bank_branches_v cbbv       ON cbbv.branch_party_id =       cba.bank_branch_id
--   LEFT JOIN apps.iby_ext_bank_accounts_v iebav ON iebav.ext_bank_account_id =  acra.customer_bank_account_id
       WHERE 1 = 1
--         AND acra.receipt_number IN ('12345678')
--         AND acra.cash_receipt_id = 2452508
--         AND hca.account_number = 1234
--         AND fu.user_name = 'SYSADMIN'
         AND acra.creation_date > '09-MAY-2016'
         AND 1 = 1
    ORDER BY acra.creation_date DESC;

-- including receipt applications

       SELECT arm.name receipt_method
            , acra.receipt_number
            , acra.cash_receipt_id
            , acra.currency_code curr
            , acra.amount
            , acra.type
            , al1.meaning status
            , acra.receipt_date
--            , fdfcv.descriptive_flex_context_code payment_type
--            , fdfcv.description payment_type_descr
            , acrha.trx_date
            , acrha.gl_date
            , acrha.reversal_gl_date
            , gcc.segment1 || '*' || gcc.segment2 || '*' || gcc.segment3 || '*' || gcc.segment4 || '*' || gcc.segment5 || '*' || gcc.segment6 chg_acct
            , acrha.creation_date
            , cr.user_name cr_by
            , acra.last_update_date
            , up.user_name up_by
            , '####################'
            , acrha.*
     FROM ar.ar_cash_receipts_all acra
     JOIN ar.ar_cash_receipt_history_all acrha  ON acra.cash_receipt_id =    acrha.cash_receipt_id
     JOIN ar.ar_receipt_methods arm             ON acra.receipt_method_id =  arm.receipt_method_id
LEFT JOIN apps.fnd_descr_flex_contexts_vl fdfcv ON acra.attribute_category = fdfcv.descriptive_flex_context_name AND fdfcv.descriptive_flexfield_name = 'AR_CASH_RECEIPTS'
LEFT JOIN apps.ar_lookups al1                   ON al1.lookup_code = acrha.status AND al1.lookup_type = 'RECEIPT_CREATION_STATUS'
LEFT JOIN apps.ar_lookups al2                   ON al2.lookup_code = acra.status AND al2.lookup_type = 'RECEIPT_CREATION_STATUS'
     JOIN gl.gl_code_combinations gcc           ON gcc.code_combination_id = acrha.account_code_combination_id
     JOIN applsys.fnd_user cr                   ON acra.created_by = cr.user_id
     JOIN applsys.fnd_user up                   ON acra.last_updated_by = up.user_id
        WHERE 1 = 1
--          AND cr.user_name = 'SYSADMIN'
          AND acra.receipt_number = '12345678'
--          AND acra.receipt_number IN ('12345678')
--          AND acrha.current_record_flag = 'Y'
--          AND fdfcv.description = 'Web payments'
--          AND acra.status = 'UNAPP'
--          AND arm.name = 'THIS'
--          AND aba.name = '42890'
--          AND acra.creation_date > '01-MAR-2016'
--          AND acra.creation_date < '01-APR-2016'
--          AND acra.creation_date > '21-JUL-2016'
          AND 1 = 1
     ORDER BY acra.creation_date DESC;

-- applications

    SELECT acra.cash_receipt_id
         , acra.receipt_number
         , acra.creation_date
         , araa.creation_date app_cr
         , cr.description app_cr_by
         , araa.last_update_date app_ud
         , ud.description app_ud_by
         , araa.amount_applied
         , araa.apply_date
         , araa.application_type
         , araa.status
      FROM ar.ar_cash_receipts_all acra
      JOIN ar.ar_receivable_applications_all araa ON acra.cash_receipt_id = araa.cash_receipt_id
      JOIN applsys.fnd_user cr ON araa.created_by = cr.user_id
      JOIN applsys.fnd_user ud ON araa.last_updated_by = ud.user_id
     WHERE acra.cash_receipt_id IN (2472977, 2472970)
  ORDER BY acra.receipt_number
         , araa.creation_date;

BATCHES - BASIC INFO

-- ##############################################################################
--        BATCHES - BASIC INFO
-- ##############################################################################

SELECT   aba.name batch_num
       , aba.batch_date
       , aba.gl_date
       , aba.batch_id
       , aba.creation_date batch_created
       , aba.type
       , aba.status
       , aba.currency_code
       , aba.remit_method_code
       , arc.name receipt_class
       , arm.name receipt_method
       , aba.comments
       , aba.control_count count
       , aba.control_amount amount
       , aba.request_id
       , aba.batch_applied_status
    FROM ar.ar_batches_all aba
    JOIN ar.ar_receipt_classes arc ON aba.receipt_class_id =  arc.receipt_class_id
    JOIN ar.ar_receipt_methods arm ON aba.receipt_method_id = arm.receipt_method_id
   WHERE 1 =1
     AND aba.name = '45867'
ORDER BY aba.creation_date DESC;

RECEIPTS LINKED TO BATCH

-- ##############################################################################
--        RECEIPTS LINKED TO BATCH
-- ##############################################################################

 SELECT   aba.name batch_num
        , aba.batch_date
        , aba.batch_id
        , aba.gl_date
        , aba.currency_code
        , aba.remit_method_code
        , aba.creation_date batch_created
        , arc.name receipt_class
        , arm.name receipt_method
        , aba.comments
        , aba.control_count
        , aba.control_amount
        , '/////////'
        , acra.cash_receipt_id
        , acra.receipt_number rx_num
        , acra.creation_date rx_created
        , acra.receipt_date rx_date
        , acra.type rx_type
        , acra.amount rx_amt
        , acra.currency_code rx_curr
        , acra.pay_from_customer rx_cust
        , acra.status rx_status
        , '/// -- DFF -- //////'
        , acra.attribute_category payment_type
        , acra.attribute1 charge_type
        , acra.attribute2 prepayment
        , acra.attribute12 acad_year
        , acra.attribute15 Receipt_printed
        , acra.attribute5 payee_name
        , acra.attribute6 reference
     FROM ar.ar_batches_all aba
JOIN ar.ar_receipt_classes arc            ON aba.receipt_class_id =  arc.receipt_class_id
JOIN ar.ar_receipt_methods arm            ON aba.receipt_method_id = arm.receipt_method_id
JOIN ar.ar_cash_receipt_history_all acrha ON acrha.batch_id =        aba.batch_id
JOIN ar.ar_cash_receipts_all acra         ON acra.cash_receipt_id =  acrha.cash_receipt_id
    WHERE 1 = 1
      AND acra.receipt_number = 12345678
--      AND acrha.current_record_flag = 'Y'
--      AND aba.name = '1077'
--      AND aba.batch_id = 893443
--     AND acra.creation_date > '15-MAR-2016'
      AND 1 = 1;

BATCHES LINKED TO "Automatic Remittances Creation Program (API)" JOB

-- ##############################################################################
--        BATCHES LINKED TO "Automatic Remittances Creation Program (API)" JOB
-- ##############################################################################

SELECT   fcr.request_id id
       , DECODE (fcr.phase_code , 'P', 'Pending' , 'R', 'Running' , 'C', 'Complete' , 'I', 'Inactive') phase
       , DECODE (fcr.status_code , 'A', 'Waiting' , 'B', 'Resuming' , 'C', 'Normal' , 'D', 'Cancelled' , 'E', 'Error' , 'F', 'Scheduled' , 'G', 'Warning' , 'H', 'On Hold' , 'I', 'Normal' , 'M', 'No Manager' , 'Q', 'Standby' , 'R', 'Normal' , 'S', 'Suspended' , 'T', 'Terminating' , 'U', 'Disabled' , 'W', 'Paused' , 'X', 'Terminated' , 'Z', 'Waiting') status
       , TRUNC(fcr.actual_start_date) run_date
       , TO_CHAR((fcr.actual_start_date), 'HH24:MI:SS') start_
       , TO_CHAR((fcr.actual_completion_date), 'HH24:MI:SS') end_
       , to_char (fcr.actual_start_date, 'Dy') day
       , CASE WHEN TRUNC(fcr.actual_start_date) = TRUNC(SYSDATE) THEN '***' END today_
       , TRIM(REPLACE(REPLACE(TO_CHAR(numtodsinterval((fcr.actual_completion_date-fcr.actual_start_date),'day')),'+000000000',''),'.000000000','')) dur
       , fu.user_name || ' (' || fu.description || ')' submitted_by
       , fcr.completion_text
       , '##############'
       , aba.name batch_num
       , aba.batch_date
       , aba.gl_date
       , aba.currency_code
       , aba.remit_method_code
       , arc.name receipt_class
       , arm.name receipt_method
       , aba.comments
       , aba.control_count count
       , aba.control_amount amount
    FROM applsys.fnd_concurrent_requests fcr
    JOIN applsys.fnd_concurrent_programs_tl fcp ON fcr.concurrent_program_id =      fcp.concurrent_program_id AND fcr.program_application_id =     fcp.application_id
    JOIN applsys.fnd_application_tl fat         ON fcr.program_application_id =     fat.application_id
    JOIN applsys.fnd_responsibility_tl frt      ON fcr.responsibility_id =          frt.responsibility_id
    JOIN applsys.fnd_user fu                    ON fcr.requested_by =               fu.user_id
    JOIN ar.ar_batches_all aba                  ON fcr.argument7 =                  aba.batch_id
    JOIN ar.ar_receipt_classes arc              ON aba.receipt_class_id =           arc.receipt_class_id
    JOIN ar.ar_receipt_methods arm              ON aba.receipt_method_id =          arm.receipt_method_id
   WHERE NVL(fcr.description, fcp.user_concurrent_program_name) = 'Automatic Remittances Creation Program (API)'
     AND fcr.argument1 = 'REMIT'
     AND fcr.status_code NOT IN ('D','X')                 -- not cancelled (D) or teminated (X)
--     AND fcr.request_id = 19629744
ORDER BY fcr.actual_start_date DESC;

AR Payment Terms

-- ##############################################################################
--      AR PAYMENT TERMS
-- ##############################################################################

        select rtb.term_id
             , rtb.creation_date
             , fu.user_name || ' (' || fu.email_address || ')' created_by
             , to_char(rtb.start_date_active, 'DD-MON-YYYY') start_date
             , to_char(rtb.end_date_active, 'DD-MON-YYYY') end_date
             , rtt.name
             , rtt.description
          from ar.ra_terms_b rtb
          join ar.ra_terms_tl rtt on rtb.term_id = rtt.term_id
          join applsys.fnd_user fu on rtb.created_by = fu.user_id;

        select rtb.term_id
             , rtb.creation_date
             , fu.user_name || ' (' || fu.email_address || ')' created_by
             , to_char(rtb.start_date_active, 'DD-MON-YYYY') start_date
             , to_char(rtb.end_date_active, 'DD-MON-YYYY') end_date
             , rtt.name
             , rtt.description
             , rtl.sequence_num seq
             , rtl.relative_amount
             , rtl.due_days
             , rtl.due_date
             , rtl.due_day_of_month
             , rtl.due_months_forward
          from ar.ra_terms_b rtb
          join ar.ra_terms_tl rtt on rtb.term_id = rtt.term_id
          join ar.ra_terms_lines rtl on rtl.term_id = rtb.term_id
          join applsys.fnd_user fu on rtb.created_by = fu.user_id;