- 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
- AR_RECEIVABLE_APPLICATIONS_ALL stores all accounting entries for cash and credit memo applications.
- 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.
- 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
- 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.
- 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';
Comments
Post a Comment