AR TABLE

 

1. The AR_DISTRIBUTIONS_ALL table stores the accounting distributions for cash receipts, miscellaneous receipts, adjustments, credit memo applications, cash receipt applications, and bills receivable transactions
2. AR_RECEIVABLE_APPLICATIONS_ALL stores all accounting entries for cash and credit memo applications.
3.  AR_CASH_RECEIPTS_ALL table stores one record for each receipt that you enter.Oracle Receivables concurrently creates records in the AR_CASH_RECEIPT_HISTORY_ALL,AR_PAYMENT_SCHEDULES_ALL, and AR_RECEIVABLE_APPLICATIONS_ALL tables for invoice–related receipts.
4. MTL_PARAMETERS maintains a set of default options like general ledger accounts; locator, lot, and serial controls; inter–organization options; costing method; etc. for each organization defined in Oracle Inventory
5. AR_RECEIVABLES_TRX_ALL table links the receivables activities with accounting information.Possible types of activities include Adjustment, Miscellaneous Cash, and Finance Charge. If you select a Miscellaneous Cash activity type, you can associate either a distribution set or a standard accounting flexfield with your receivables activity.  Oracle Receivables uses one row for each activity.
6. The AR_CASH_RECEIPT_HISTORY_ALL table contains each step in areceipt’s lifecycle. Each row in this table represents one step. TheSTATUS column indicates which step the receipt has reached:  • APPROVED – This status is valid only for an automaticallycreated receipt and indicates that the receipt was approved forautomatic creation.• CONFIRMED – This status is valid only for an automaticallycreated receipt and indicates that the receipt was confirmed bythe customer.• REMITTED – This status is valid for both automatically and manually created receipts and indicates that the receipt was remitted.• CLEARED – This status is valid for both automatically and manually created receipts and indicates that the receipt was cleared.• REVERSED – This status is valid for both automatically and manually created receipts and indicates that the receipt was reversed.

AR_DISTRIBUTIONS_ALL

For cash receipt headers, the SOURCE_TABLE is CRH and the

SOURCE_ID is

AR_CASH_RECEIPT_HISTORY_ALL.CASH_RECEIPT_HISTORY_ID.

The SOURCE_TYPE can be:

• CONFIRMATION

• REMITTANCE

• CASH

• FACTOR

• SHORT_TERM_DEBT

• BANK_CHARGES

For receivable applications records, the SOURCE_TABLE is RA and the

SOURCE_ID is AR_RECEIVABLE_APPLICATIONS_

ALL.RECEIVABLE_APPLICATION_ID. The SOURCE_TYPE can be:

• REC for receivable accounts

• EDISC for earned discounts

• UNEDISC for unearned discounts

• TAX for collectable tax

• DEFERRED_TAX for deferred tax,

• EDISC_NON_REC for non–recoverable earned discounts

• UNEDISC_NON_REC for non–recoverable unearned discounts

• EXCH_GAIN for exchange gain

• EXCH_LOSS for exchange loss

• CURR_ROUND for currency rounding

For adjustment records, the SOURCE_TABLE is ADJ and the

SOURCE_ID is AR_ADJUSTMENTS_ALL.ADJUSTMENT_ID. The

SOURCE_TYPE can be:

• REC for receivable accounts

• ADJ for adjustment accounts

• FINCHRG for finance charges

• ADJ_NON_REC for non–recoverable adjustments

• FINCHRG_NON_REC for non–recoverable finance charges

For miscellaneous receipt records, the SOURCE_TABLE is MCD and the

SOURCE_ID is AR_MISC_CASH_DISTRIBUTIONS_ALL.MISC_CASH_

DISTRIBUTIONS_ID. The SOURCE_TYPE can be:

• MISCCASH for cash accounts

• TAX for tax

For bills receivable records, the SOURCE_TABLE is TH and the

SOURCE_ID is

AR_TRANSACTION_HISTORY_ALL.TRANSACTION_HISTORY_ID.

The SOURCE_TYPE can be:

• DEFERRED_TAX for deferred tax

• FACTOR for factor

• REC for receivable

• REMITTANCE for remittance

• TAX for tax

• UNPAIDREC for unpaid bills

Query

SELECT  rcta.customer_trx_id,
ada.source_type,
       arra.receivable_application_id,
       acrh.gl_date,
       acrh.gl_posted_date,
       acr.amount,
       ada.acctd_amount_dr,
       ada.amount_cr,
       arra.amount_applied,
       ada.currency_code,
      acr.receipt_date,
       acr.receipt_number,
       gcc.segment7,
       acr.pay_from_customer,
       rcta.trx_date,
       rcta.trx_number,
       --aa.CUSTOMER_NAME,
       rtt.name,
       arm.name,
       ada.source_table
  FROM ar.ar_distributions_all ada,
       ar.ar_cash_receipts_all acr,
       gl.gl_code_combinations gcc,
       ar.ar_receivable_applications_all arra,
       ar.ra_customer_trx_all rcta,
       ar.ra_cust_trx_types_all rtt,
       ar.ar_receipt_methods arm,
       ar.ar_cash_receipt_history_all acrh
 --apps.ar_customers_v aa
 WHERE     arra.cash_receipt_id = acr.cash_receipt_id
       AND acr.cash_receipt_id = acrh.cash_receipt_id
       AND acrh.cash_receipt_history_id = ada.source_id
       --and acr.pay_from_customer=aa.CUSTOMER_ID
       AND ada.code_combination_id = gcc.code_combination_id
       AND arra.applied_customer_trx_id = rcta.customer_trx_id
       AND rcta.cust_trx_type_id = rtt.cust_trx_type_id
       AND acr.receipt_method_id = arm.receipt_method_id
       AND ada.source_table = 'CRH'                    -- CRH for cash account
       AND acr.TYPE = 'CASH'
       AND arra.status = 'APP'


Another Query

SELECT amount_dr,
       amount_cr,
       acctd_amount_dr,
       acctd_amount_cr,
          gcc.segment1
       || '.'
       || gcc.segment2
       || '.'
       || gcc.segment3
       || '.'
       || gcc.segment4
       || '.'
       || gcc.segment5
          ACCOUNT
  FROM ar.ar_distributions_all ad, gl.gl_code_combinations gcc
 WHERE     source_table = 'CRH'
       AND EXISTS
              (SELECT 'T'
                 FROM ar.ar_cash_receipt_history_all a,
                      ar.ar_cash_receipts_all b
                WHERE     a.cash_receipt_id = b.cash_receipt_id
                      AND source_id = cash_receipt_history_id
                      AND b.org_id = '&org_id'
                      AND b.org_id = a.org_id
                      AND b.receipt_number LIKE '%&receipt_number%')
       AND ad.code_combination_id = gcc.code_combination_id
UNION ALL
SELECT amount_dr,
       amount_cr,
       acctd_amount_dr,
       acctd_amount_cr,
          gcc.segment1
       || '.'
       || gcc.segment2
       || '.'
       || gcc.segment3
       || '.'
       || gcc.segment4
       || '.'
       || gcc.segment5
          ACCOUNT
  FROM ar.ar_distributions_all ad, gl.gl_code_combinations gcc
 WHERE     source_table = 'RA'
       AND EXISTS
              (SELECT 'T'
                 FROM ar.ar_receivable_applications_all a,
                      ar.ar_cash_receipts_all b
                WHERE     a.cash_receipt_id = b.cash_receipt_id
                      AND a.receivable_application_id = source_id
                      AND b.org_id = '&org_id'
                      AND b.org_id = a.org_id
                      AND b.receipt_number LIKE '%&receipt_number%')
       AND ad.code_combination_id = gcc.code_combination_id

--Customer Info


SELECT a.cust_account_id customer_id,
       p.party_id,
       a.account_number customer_number,
       p.party_number registry_id,
       p.party_name customer_name,
       l.address1,
       l.address2,
       l.address3,
       l.address4,
       l.city,
       l.country,
       l.po_box_number,
       sa.cust_acct_site_id,
       su.site_use_id,
       su.site_use_code,
       l.location_id,
       su.LOCATION location_code,
       sa.org_id,
       t.territory_short_name country_name,
       t.description country_description,
       p.orig_system_reference,
       a.status cust_status,
       sa.status cust_site_status,
       NVL (
          (SELECT overall_credit_limit
             FROM hz_cust_profile_amts cl
            WHERE     cl.cust_account_id = a.cust_account_id
                  AND cl.site_use_id = su.site_use_id),
          0)
          credit_limit,
       (SELECT tu.payment_term_id
          FROM hz_cust_site_uses_all tu
         WHERE tu.site_use_id = su.site_use_id)
          term_id,
       (SELECT tm.NAME
          FROM hz_cust_site_uses_all tu, ra_terms_vl tm
         WHERE     tm.term_id = tu.payment_term_id
               AND tu.site_use_id = su.site_use_id)
          term_name,
       s.party_site_name,
       hcp.phone_area_code areacode,
       hcp.phone_country_code country_code,
       hcp.phone_extension phone_extension,
       hcp.phone_number telephone,
       hcp1.phone_country_code fax_country_code,
       hcp1.phone_area_code fax_areacode,
       hcp1.phone_number fax,
       hcp2.email_address email
  FROM hz_locations l,
       hz_party_sites s,
       hz_parties p,
       hz_cust_accounts a,
       hz_cust_acct_sites_all sa,
       hz_cust_site_uses_all su,
       fnd_territories_vl t,
       hz_contact_points hcp,
       hz_contact_points hcp1,
       hz_contact_points hcp2
 WHERE     l.location_id = s.location_id
       AND s.party_id = p.party_id
       AND a.party_id = p.party_id
       AND sa.cust_account_id = a.cust_account_id
       AND sa.party_site_id = s.party_site_id
       AND sa.cust_acct_site_id = su.cust_acct_site_id
       AND t.territory_code = l.country
       AND a.status = 'A'
       AND sa.status = 'A'
       AND p.party_id = hcp.owner_table_id ()
       AND hcp.owner_table_name () = 'HZ_PARTIES'
       AND hcp.contact_point_type () = 'PHONE'
       AND hcp.phone_line_type () = 'GEN'
       AND p.party_id = hcp1.owner_table_id ()
       AND hcp1.owner_table_name () = 'HZ_PARTIES'
       AND hcp1.contact_point_type () = 'PHONE'
       AND hcp1.phone_line_type () = 'FAX'
       AND p.party_id = hcp2.owner_table_id ()
       AND hcp2.owner_table_name () = 'HZ_PARTIES'
       AND hcp2.contact_point_type () = 'EMAIL'
       AND a.account_number LIKE '23643%';
-- and a.cust_account_id=1043


SELECT --+  use_nl(rctt rct)
       *
  FROM xla_ae_lines xal,
       xla_ae_headers xah,
       xla_distribution_links xdl,
       apps.ra_cust_trx_line_gl_dist_all rctlgd,
       apps.ra_customer_trx_all rct,
       apps.ra_customer_trx_lines_all rctl,
       apps.ra_cust_trx_types_all rctt
WHERE 1 = 1
   AND rct.org_id = rctl.org_id
   AND rct.org_id = rctlgd.org_id
   AND rct.org_id = rctt.org_id
   AND rct.complete_flag = 'Y'
   AND rct.trx_date BETWEEN  :p_date_from AND :p_date_to
   AND rct.cust_trx_type_id = rctt.cust_trx_type_id
   AND rct.customer_trx_id = rctl.customer_trx_id
   AND rctl.customer_trx_line_id = rctlgd.customer_trx_line_id
   AND rct.customer_trx_id = rctlgd.customer_trx_id
   AND rctlgd.account_set_flag = 'N'
   AND rctlgd.posting_control_id <> -3
   AND rctlgd.PERCENT <> 100
   AND NVL (rctlgd.amount, 0) <> 0
   AND rctlgd.event_id = xah.event_id
   AND xah.accounting_date > :p_date_to
   AND xah.application_id = 222
   --AND xah.gl_transfer_status_code = 'N'
   AND xah.ae_header_id = xal.ae_header_id
   AND xal.accounting_class_code = 'UNEARNED_REVENUE'
   AND xal.ae_header_id = xdl.ae_header_id
   AND xal.ae_line_num = xdl.ae_line_num
   AND xdl.ae_header_id = xah.ae_header_id
   AND xdl.event_id = xah.event_id
   AND rct.org_id = :p_org_id
   AND xdl.source_distribution_id_num_1 = rctlgd.cust_trx_line_gl_dist_id
   AND xdl.source_distribution_type = 'RA_CUST_TRX_LINE_GL_DIST_ALL';

-- AR Transactions


SELECT rct.trx_number,
       rct.customer_trx_id,
       rctd.cust_trx_line_gl_dist_id,
       xal.entered_dr,
       xal.entered_cr,
       xal.code_combination_id,
       xah.event_id,
       xal.ae_header_id
  FROM ra_customer_trx_all rct,
       ra_customer_trx_lines_all rctl,
       ra_cust_trx_line_gl_dist_all rctd,
       xla_ae_headers xah,
       xla_ae_lines xal,
       xla_events xet,
       xla_distribution_links xdl
WHERE     xal.ae_header_id = xah.ae_header_id
       AND xet.event_id = xah.event_id
       AND xdl.event_id = xet.event_id
       AND xdl.ae_header_id = xah.ae_header_id
       AND xdl.ae_line_num = xal.ae_line_num
       AND xdl.application_id = 222
       AND xdl.source_distribution_type IN ('RA_CUST_TRX_LINE_GL_DIST_ALL',
                                            'MFAR_DISTRIBUTIONS_ALL')
       AND xdl.source_distribution_id_num_1 = rctd.CUST_TRX_LINE_GL_DIST_ID
       AND rctl.customer_trx_line_id = rctd.customer_trx_line_id
       AND rct.customer_trx_id = rctl.customer_trx_id
       AND rct.trx_number = :p_trx_number;

       -- AR adjustments

SELECT rct.trx_number,
       adj.customer_trx_id,
       adj.adjustment_id,
       xal.entered_dr,
       xal.entered_cr,
       xal.code_combination_id,
       xah.event_id,
       xal.ae_header_id
  FROM ra_customer_trx_all rct,
       ar_adjustments_all adj,
       ar_distributions_all ard,
       xla_ae_headers xah,
       xla_ae_lines xal,
       xla_events xet,
       xla_distribution_links xdl
WHERE     xal.ae_header_id = xah.ae_header_id
       AND xet.event_id = xah.event_id
       AND xdl.event_id = xet.event_id
       AND xdl.ae_header_id = xah.ae_header_id
       AND xdl.ae_line_num = xal.ae_line_num
       AND xdl.application_id = 222
       AND xdl.source_distribution_type IN ('AR_DISTRIBUTIONS_ALL',
                                            'MFAR_DISTRIBUTIONS_ALL')
       AND xdl.source_distribution_id_num_1 = ard.line_id
       AND ard.source_table = 'ADJ'
       AND ard.source_id = adj.adjustment_id
       AND adj.adjustment_id = :p_adj_id
       AND rct.customer_trx_id = adj.customer_trx_id
       AND rct.trx_number = :p_trx_number;

-- Misc Receipts

SELECT cr.receipt_number,
       cr.cash_receipt_id,
       arm.misc_cash_distribution_id,
       xal.entered_dr,
       xal.entered_cr,
       xal.code_combination_id,
       xah.event_id,
       xal.ae_header_id
  FROM ar_cash_receipts_all cr,
       ar_misc_cash_distributions_all arm,
       ar_distributions_all ard,
       xla_ae_headers xah,
       xla_ae_lines xal,
       xla_events xet,
       xla_distribution_links xdl
WHERE     xal.ae_header_id = xah.ae_header_id
       AND xet.event_id = xah.event_id
       AND xdl.event_id = xet.event_id
       AND xdl.ae_header_id = xah.ae_header_id
       AND xdl.ae_line_num = xal.ae_line_num
       AND xdl.source_distribution_type IN ('AR_DISTRIBUTIONS_ALL',
                                            'MFAR_DISTRIBUTIONS_ALL')
       AND xdl.source_distribution_id_num_1 = ard.line_id
       AND ard.source_id = arm.misc_cash_distribution_id
       AND ard.source_table = 'MCD'
       AND xdl.application_id = 222
       AND arm.cash_receipt_id = cr.cash_receipt_id
       AND cr.cash_receipt_id = :receipt_id;

       -- CASH Receipts

SELECT cr.receipt_number,
       cr.cash_receipt_id,
       arp.receivable_application_id,
       xal.entered_dr,
       xal.entered_cr,
       xal.code_combination_id,
       xah.event_id,
       xal.ae_header_id
  FROM ar_cash_receipts_all cr,
       ar_receivable_applications_all arp,
       ar_distributions_all ard,
       xla_ae_headers xah,
       xla_ae_lines xal,
       xla_events xet,
       xla_distribution_links xdl,
       gl_code_combinations gcc
WHERE     xal.ae_header_id = xah.ae_header_id
       AND xet.event_id = xah.event_id
       AND xdl.event_id = xet.event_id
       AND xdl.ae_header_id = xah.ae_header_id
       AND xdl.ae_line_num = xal.ae_line_num
       AND xdl.source_distribution_type IN ('AR_DISTRIBUTIONS_ALL',
                                            'MFAR_DISTRIBUTIONS_ALL')
       AND xdl.source_distribution_id_num_1 = ard.line_id
       AND ard.source_id = arp.receivable_application_id
       AND ard.source_table = 'RA'
       AND xdl.application_id = 222
       AND arp.cash_receipt_id = cr.cash_receipt_id
       AND cr.cash_receipt_id = :receipt_id
       AND gcc.code_combination_id = xal.code_combination_id
       AND gcc.segment1 != '00000000000000';


-- OM and AR Link

SELECT DISTINCT
    oel.ship_from_org_id,
    rchl.org_id, --rchl.*,
    oel.ship_to_org_id,
    oeh.freight_terms_code,
    substr(oeh.attribute7,1,3)
FROM
    apps.ra_customer_trx_lines_all rchl,
    apps.oe_order_headers_all oeh,
    apps.oe_order_lines_all oel
WHERE
                        --rchl.customer_trx_line_id = c_rec.user_element_attribute41 AND
    rchl.line_type = 'LINE'
    AND   oeh.order_number = rchl.sales_order
    AND   rchl.interface_line_attribute6 = TO_CHAR(oel.line_id) --added by Nisha for INC5811278
    AND   oeh.header_id = oel.header_id
    AND   oeh.order_number = '19130544';