The Accounts Receivable function is responsible for managing outgoing
invoices to customers who purchased goods or services, and the
collection and application of all payments, including payments for
invoices. The Oracle Receivables module (a part of the Oracle EBS
Financials Suite) helps the Accounts Receivable departments to manage
this function effectively and efficiently.
This post describes a summary of the Oracle Receivables data model and data flow. Some of these tables are source tables for the Oracle Business Intelligence Applications – Financial Analytics module, specifically providing information for the Payabales dashboard.
To be in the position where you need to handle and process a payment in Receivables, you need to have a buyer/payer (most times this is a customer but there are exceptions). Customer records are stored in the HZ_CUST_ACCOUNTS and HZ_PARTIES tables. Each customer needs to have a site (a location/address of business) for which information is stored in HZ_CUST_ACCT_SITES_ALL and HZ_PARTY_SITES_ALL.
When a customer purchases goods or services from your company, an invoice is generated for the customer. These invoice transactions are recorded in RA_CUSTOMER_TRX_ALL (invoice headers) and RA_CUSTOMER_TRX_LINES_ALL (invoice lines).
When the customer makes a payment, this generates new transactions. These are recorded in AR_CASH_RECEIPTS_ALL and AR_CASH_RECEIPT_HISTORY. If there is adjustment to an invoice, this is recorded in AR_ADJUSTMENTS.
Sometimes payments are received in batches, where a single payment is for multiple invoices. These batch payments have records in AR_BATCHES.
The AR_PAYMENT_SCHEDULE table holds one record per payment. Therefore, for payments that pay an invoice in full, there will only be one record related to that invoice. However, if payments for an invoice are broken up into a payment plan, or if a partial payment is received for an invoice, additional records will be generated in this table for each payment.
I mentioned above that “most times payments are from customers, but there are exceptions”. An example of an exception is “payment from a bank for interest earned”. The payment is not from a customer and it’s not for goods/services provided. These types of payments are recorded in AR_MISC_CASH_DISTRIBUTIONS.
These transactions affect accounting which will eventually make their way to the GL (when the Receivables Transfer to GL program is run). The accounting transactions are generated in RA_CUST_TRX_LINE_GL_DIST and AR_RECEIVABLE_APPLICATIONS.
1. What is TCA? Tables?
A) Trading Community Architecture. It is a centralized repository of business entities such as Partners, Customers, and Organizations etc. It is a new framework developed in Oracle 11i.
HZ_PARTIES: The HZ_PARTIES table stores basic information about parties that can be shared with any relationship that the party might establish with another party. Although a record in the HZ_PARTIES table represents a unique party, multiple parties can have the same name. The parties can be one of four types:
Organization for example, Oracle CorporationPerson for example, Jane DoeGroup for example, World Wide Web ConsortiumRelationship for example, Jane Doe at Oracle Corporation.
HZ_LOCATIONS: The HZ_LOCATIONS table stores information about a delivery or postal address such as building number, street address, postal code, and directions to a location. This table provides physical location information about parties (organizations and people) and customer accounts.
HZ_PARTY_SITES: The HZ_PARTY_SITES table links a party (see HZ_PARTIES) and a location (see HZ_LOCATIONS) and stores location-specific party information. One party can optionally have one or more party sites. One location can optionally be used by one or more parties. This party site can then be used for multiple customer accounts within the same party.
HZ_CUST_ACCT_SITES_ALL
HZ_CUST_SITE_USES_ALL
HZ_CUST_CONTACT_POINTS etc.
2. What are Base Tables or Interface Tables for Customer Conversions, Autolockbox, Auto Invoice?
A) Customer Conversion:
Interface Tables : RA_CUSTOMERS_INTERFACE_ALL, RA_CUSTOMER_PROFILES_INT_ALL,
RA_CONTACT_PHONES_INT_ALL,
RA_CUSTOMER_BANKS_INT_ALL,
RA_CUST_PAY_METHOD_INT_ALL
Base Tables : RA_CUSTOMERS, RA_ADDRESSES, RA_SITE_USES_ALL,
RA_CUSTOMER_PROFILES_ALL, RA_PHONES etc
B) Auto Invoice:
Interface Tables : RA_INTERFACE_LINES_ALL, RA_INTERFACE_DISTRIBUTIONS_ALL
RA_INTERFACE_SALESCREDITS_ALL, RA_INTERFACE_ERRORS_ALL
Base Tables : RA_CUSTOMER_TRX_ALL, RA_CUSTOMER_TRX_LINES_ALL,
RA_CUST_TRX_LINE_GL_DIST_ALL, RA_CUST_TRX_LINE_SALESREPS_ALL, RA_CUST_TRX_TYPES_ALL
C) AutoLockBox:
Interface Tables : AR_PAYMENTS_INTERFACE_ALL (POPULATED BY IMPORT PROCESS)
Interim tables : AR_INTERIM_CASH_RECEIPTS_ALL (All Populated by Submit Validation)
: AR_INTERIM_CASH_RCPT_LINES_ALL,
AR_INTERIM_POSTING
Base Tables : AR_CASH_RECEIPTS_ALL, AR_RECEIVABLE_APPLICATIONS_ALL,
AR_PAYMENT_SCHEDULES_ALL ( All Populated by post quick cash)
3. What are the tables in which Invoices/transactions information is stored?
A) RA_CUSTOMER_TRX_ALL, The RA_CUSTOMER_TRX_ALL table stores invoice, debit memo, commitment, bills receivable, and credit memo header information. Each row in this table includes general invoice information such as customer, transaction type, and printing instructions.
RA_CUSTOMER_TRX_LINES_ALL, The RA_CUSTOMER_TRX_LINES_ALL table stores information about invoice, debit memo, credit memo, bills receivable, and commitment lines (LINE, FREIGHT and TAX).
RA_CUST_TRX_LINE_SALESREPS_ALL, The RA_CUST_TRX_LINE_SALESREPS_ALL table stores sales credit assignments for invoice lines. If Receivables bases your invoice distributions on sales credits, a mapping exists between the sales credit assignments in this table with the RA_CUST_TRX_LINE_GL_DIST_ALL table.
The RA_CUST_TRX_LINE_GL_DIST_ALL table stores the accounting records for revenue, unearned revenue, and unbilled receivables for each invoice or credit memo line. Oracle Receivables creates one row for each accounting distribution, and at least one accounting distribution must exist for each invoice or credit memo line. Each row in this table includes the General Ledger account and the amount of the accounting entry.
The RA_CUST_TRX_LINE_SALESREPS_ALL table stores sales credit assignments for invoice lines. If Receivables bases your invoice distributions on sales credits, a mapping exists between the sales credit assignments in this table with the RA_CUST_TRX_LINE_GL_DIST_ALL table.
4. What are the tables In which Receipt information is stored?
A) AR_PAYMENT_SCHEDULES_ALL, The AR_PAYMENT_SCHEDULES_ALL table stores all transactions except adjustments and miscellaneous cash receipts. Oracle Receivables updates this table when activity occurs against an invoice, debit memo, chargeback, credit memo, on-account credit, or receipt.
Transaction classes determine if a transaction relates to either the RA_CUSTOMER_TRX_ALL table or the AR_CASH_RECEIPTS_ALL table. Using the CUSTOMER_TRX_ID foreign key column, the AR_PAYMENT_SCHEDULES_ALL table joins to the RA_CUSTOMER_TRX_ALL table for non-payment transaction entries, such as the creation of credit memos, debit memos, invoices, chargebacks, or deposits. Using the CASH_RECEIPT_ID foreign key column, the AR_PAYMENT_SCHEDULES_ALL table joins to the AR_CASH_RECEIPTS_ALL table for invoice-related payment transactions.
AR_CASH_RECEIPTS_ALL, The 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. For receipts that are not related to invoices, such as miscellaneous receipts, Receivables creates records in the AR_MISC_CASH_DISTRIBUTIONS_ALL table instead of the AR_RECEIVABLE_APPLICATIONS_ALL table.
AR_RECEIVABLE_APPLICATIONS_ALL, The 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. For receipts that are not related to invoices, such as miscellaneous receipts, Receivables creates records in the AR_MISC_CASH_DISTRIBUTIONS_ALL table instead of the AR_RECEIVABLE_APPLICATIONS_ALL table. Cash receipts proceed through the confirmation, remittance, and clearance steps. Each step creates rows in the AR_CASH_RECEIPT_HISTORY table.
5. What are the tables in which Accounts information is stored?
RA_CUST_TRX_LINE_GL_DIST_ALL
6. What are the different statuses for Receipts?
A) Unidentified – Lack of Customer Information
Unapplied – Lack of Transaction/Invoice specific information (Ex- Invoice Number)
Applied – When all the required information is provided.
On-Account, Non-Sufficient Funds, Stop Payment, and Reversed receipt.
8. What is Autolockbox?
A) Auto lockbox is a service that commercial banks offer corporate customers to enable them to out source their account receivable payment processing. Auto lockbox can also be used to transfer receivables from previous accounting systems into current receivables. It eliminates manual data entry by automatically processing receipts that are sent directly to banks. It involves three steps
1 Import (Formats data from bank file and populates the Interface Table),
2 Validation(Validates the data and then Populates data into Interim Tables),
3 Post Quick Cash(Applies Receipts and updates Balances in BaseTables).
9. What is Transmission Format?
A) Transmission Format specifies how data in the lockbox bank file should be organized such that it can be successfully imported into receivables interface tables. Example, Default, Convert, Cross Currency, Zengen are some of the standard formats provided by oracle.
10. What is Auto Invoice?
A) Autoinvoice is a tool used to import and validate transaction data from other financial systems and create invoices, debit-memos, credit memos, and on account credits in Oracle receivables. Using Custom Feeder programs transaction data is imported into the autoinvoice interface tables.
Autoinvoice interface program then selects data from interface tables and creates transactions in receivables (Populates receivable base tables) . Transactions with invalid information are rejected by receivables and are stored in RA_INTERFACE_ERRORS_ALL interface table.
11. What are the Mandatory Interface Tables in Auto Invoice?RA_INTERFACE_LINES_ALL, RA_INTERFACE_DISTRIBUTIONS_ALL
RA_INTERFACE_SALESCREDITS_ALL.
12. What are the Set up required for Custom Conversion, Autolockbox and Auto Invoice?
A) Autoinvoice program Needs AutoAccounting to be defined prior to its execution.
13. What is AutoAccounting?A) By defining AutoAccounting we specify how the receivables should determine the general ledger accounts for transactions manually entered or imported using Autoinvoice. Receivables automatically creates default accounts(Accounting Flex field values) for revenue, tax, freight, financial charge, unbilled receivable, and unearned revenue accounts using the AutoAccounting information.
14. What are Autocash rules?A) Autocash rules are used to determine how to apply the receipts to the customers outstanding debit items. Autocash Rule Sets are used to determine the sequence of Autocash rules that Post Quickcash uses to update the customers account balances.
15. What are Grouping Rules? (Used by Autoinvoice)
A) Grouping rules specify the attributes that must be identical for lines to appear on the same transaction. After the grouping rules are defined autoinvoice uses them to group revenues and credit transactions into invoices debit memos, and credit memos.
16. What are Line Ordering Rules? (Used by Autoinvoice)A) Line ordering rules are used to order transaction lines when grouping the transactions into invoices, debit memos and credit memos by autoinvoice program. For instance if transactions are being imported from oracle order management , and an invoice line ordering rule for sales_order _line is created then the invoice lists the lines in the same order of lines in sales order.
17. In which table you can see the amount due of a customer?
A) AR_PAYMENT_SCHEDULES_ALL
18. How do you tie Credit Memo to the Invoice?
At table level, In RA_CUSTOMER_TRX_ALL, If you entered a credit memo, the PREVIOUS_CUSTOMER_TRX_ID column stores the customer transaction ID of the invoice that you credited. In the case of on-account credits, which are not related to any invoice when the credits are created, the PREVIOUS_CUSTOMER_TRX_ID column is null.
19. What are the available Key Flex Fields in Oracle Receivables?A) Sales Tax Location Flex field, It’s used for sales tax calculations.
Territory Flex field is used for capturing address information.
20. What are Transaction types? Types of Transactions in AR?
A) Transaction types are used to define accounting for different transactions such as Debit Memo, Credit Memo, On-Account Credits, Charge Backs, Commitments and invoices.
This post describes a summary of the Oracle Receivables data model and data flow. Some of these tables are source tables for the Oracle Business Intelligence Applications – Financial Analytics module, specifically providing information for the Payabales dashboard.
To be in the position where you need to handle and process a payment in Receivables, you need to have a buyer/payer (most times this is a customer but there are exceptions). Customer records are stored in the HZ_CUST_ACCOUNTS and HZ_PARTIES tables. Each customer needs to have a site (a location/address of business) for which information is stored in HZ_CUST_ACCT_SITES_ALL and HZ_PARTY_SITES_ALL.
When a customer purchases goods or services from your company, an invoice is generated for the customer. These invoice transactions are recorded in RA_CUSTOMER_TRX_ALL (invoice headers) and RA_CUSTOMER_TRX_LINES_ALL (invoice lines).
When the customer makes a payment, this generates new transactions. These are recorded in AR_CASH_RECEIPTS_ALL and AR_CASH_RECEIPT_HISTORY. If there is adjustment to an invoice, this is recorded in AR_ADJUSTMENTS.
Sometimes payments are received in batches, where a single payment is for multiple invoices. These batch payments have records in AR_BATCHES.
The AR_PAYMENT_SCHEDULE table holds one record per payment. Therefore, for payments that pay an invoice in full, there will only be one record related to that invoice. However, if payments for an invoice are broken up into a payment plan, or if a partial payment is received for an invoice, additional records will be generated in this table for each payment.
I mentioned above that “most times payments are from customers, but there are exceptions”. An example of an exception is “payment from a bank for interest earned”. The payment is not from a customer and it’s not for goods/services provided. These types of payments are recorded in AR_MISC_CASH_DISTRIBUTIONS.
These transactions affect accounting which will eventually make their way to the GL (when the Receivables Transfer to GL program is run). The accounting transactions are generated in RA_CUST_TRX_LINE_GL_DIST and AR_RECEIVABLE_APPLICATIONS.
1. What is TCA? Tables?
A) Trading Community Architecture. It is a centralized repository of business entities such as Partners, Customers, and Organizations etc. It is a new framework developed in Oracle 11i.
HZ_PARTIES: The HZ_PARTIES table stores basic information about parties that can be shared with any relationship that the party might establish with another party. Although a record in the HZ_PARTIES table represents a unique party, multiple parties can have the same name. The parties can be one of four types:
Organization for example, Oracle CorporationPerson for example, Jane DoeGroup for example, World Wide Web ConsortiumRelationship for example, Jane Doe at Oracle Corporation.
HZ_LOCATIONS: The HZ_LOCATIONS table stores information about a delivery or postal address such as building number, street address, postal code, and directions to a location. This table provides physical location information about parties (organizations and people) and customer accounts.
HZ_PARTY_SITES: The HZ_PARTY_SITES table links a party (see HZ_PARTIES) and a location (see HZ_LOCATIONS) and stores location-specific party information. One party can optionally have one or more party sites. One location can optionally be used by one or more parties. This party site can then be used for multiple customer accounts within the same party.
HZ_CUST_ACCT_SITES_ALL
HZ_CUST_SITE_USES_ALL
HZ_CUST_CONTACT_POINTS etc.
2. What are Base Tables or Interface Tables for Customer Conversions, Autolockbox, Auto Invoice?
A) Customer Conversion:
Interface Tables : RA_CUSTOMERS_INTERFACE_ALL, RA_CUSTOMER_PROFILES_INT_ALL,
RA_CONTACT_PHONES_INT_ALL,
RA_CUSTOMER_BANKS_INT_ALL,
RA_CUST_PAY_METHOD_INT_ALL
Base Tables : RA_CUSTOMERS, RA_ADDRESSES, RA_SITE_USES_ALL,
RA_CUSTOMER_PROFILES_ALL, RA_PHONES etc
B) Auto Invoice:
Interface Tables : RA_INTERFACE_LINES_ALL, RA_INTERFACE_DISTRIBUTIONS_ALL
RA_INTERFACE_SALESCREDITS_ALL, RA_INTERFACE_ERRORS_ALL
Base Tables : RA_CUSTOMER_TRX_ALL, RA_CUSTOMER_TRX_LINES_ALL,
RA_CUST_TRX_LINE_GL_DIST_ALL, RA_CUST_TRX_LINE_SALESREPS_ALL, RA_CUST_TRX_TYPES_ALL
C) AutoLockBox:
Interface Tables : AR_PAYMENTS_INTERFACE_ALL (POPULATED BY IMPORT PROCESS)
Interim tables : AR_INTERIM_CASH_RECEIPTS_ALL (All Populated by Submit Validation)
: AR_INTERIM_CASH_RCPT_LINES_ALL,
AR_INTERIM_POSTING
Base Tables : AR_CASH_RECEIPTS_ALL, AR_RECEIVABLE_APPLICATIONS_ALL,
AR_PAYMENT_SCHEDULES_ALL ( All Populated by post quick cash)
3. What are the tables in which Invoices/transactions information is stored?
A) RA_CUSTOMER_TRX_ALL, The RA_CUSTOMER_TRX_ALL table stores invoice, debit memo, commitment, bills receivable, and credit memo header information. Each row in this table includes general invoice information such as customer, transaction type, and printing instructions.
RA_CUSTOMER_TRX_LINES_ALL, The RA_CUSTOMER_TRX_LINES_ALL table stores information about invoice, debit memo, credit memo, bills receivable, and commitment lines (LINE, FREIGHT and TAX).
RA_CUST_TRX_LINE_SALESREPS_ALL, The RA_CUST_TRX_LINE_SALESREPS_ALL table stores sales credit assignments for invoice lines. If Receivables bases your invoice distributions on sales credits, a mapping exists between the sales credit assignments in this table with the RA_CUST_TRX_LINE_GL_DIST_ALL table.
The RA_CUST_TRX_LINE_GL_DIST_ALL table stores the accounting records for revenue, unearned revenue, and unbilled receivables for each invoice or credit memo line. Oracle Receivables creates one row for each accounting distribution, and at least one accounting distribution must exist for each invoice or credit memo line. Each row in this table includes the General Ledger account and the amount of the accounting entry.
The RA_CUST_TRX_LINE_SALESREPS_ALL table stores sales credit assignments for invoice lines. If Receivables bases your invoice distributions on sales credits, a mapping exists between the sales credit assignments in this table with the RA_CUST_TRX_LINE_GL_DIST_ALL table.
4. What are the tables In which Receipt information is stored?
A) AR_PAYMENT_SCHEDULES_ALL, The AR_PAYMENT_SCHEDULES_ALL table stores all transactions except adjustments and miscellaneous cash receipts. Oracle Receivables updates this table when activity occurs against an invoice, debit memo, chargeback, credit memo, on-account credit, or receipt.
Transaction classes determine if a transaction relates to either the RA_CUSTOMER_TRX_ALL table or the AR_CASH_RECEIPTS_ALL table. Using the CUSTOMER_TRX_ID foreign key column, the AR_PAYMENT_SCHEDULES_ALL table joins to the RA_CUSTOMER_TRX_ALL table for non-payment transaction entries, such as the creation of credit memos, debit memos, invoices, chargebacks, or deposits. Using the CASH_RECEIPT_ID foreign key column, the AR_PAYMENT_SCHEDULES_ALL table joins to the AR_CASH_RECEIPTS_ALL table for invoice-related payment transactions.
AR_CASH_RECEIPTS_ALL, The 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. For receipts that are not related to invoices, such as miscellaneous receipts, Receivables creates records in the AR_MISC_CASH_DISTRIBUTIONS_ALL table instead of the AR_RECEIVABLE_APPLICATIONS_ALL table.
AR_RECEIVABLE_APPLICATIONS_ALL, The 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. For receipts that are not related to invoices, such as miscellaneous receipts, Receivables creates records in the AR_MISC_CASH_DISTRIBUTIONS_ALL table instead of the AR_RECEIVABLE_APPLICATIONS_ALL table. Cash receipts proceed through the confirmation, remittance, and clearance steps. Each step creates rows in the AR_CASH_RECEIPT_HISTORY table.
5. What are the tables in which Accounts information is stored?
RA_CUST_TRX_LINE_GL_DIST_ALL
6. What are the different statuses for Receipts?
A) Unidentified – Lack of Customer Information
Unapplied – Lack of Transaction/Invoice specific information (Ex- Invoice Number)
Applied – When all the required information is provided.
On-Account, Non-Sufficient Funds, Stop Payment, and Reversed receipt.
8. What is Autolockbox?
A) Auto lockbox is a service that commercial banks offer corporate customers to enable them to out source their account receivable payment processing. Auto lockbox can also be used to transfer receivables from previous accounting systems into current receivables. It eliminates manual data entry by automatically processing receipts that are sent directly to banks. It involves three steps
1 Import (Formats data from bank file and populates the Interface Table),
2 Validation(Validates the data and then Populates data into Interim Tables),
3 Post Quick Cash(Applies Receipts and updates Balances in BaseTables).
9. What is Transmission Format?
A) Transmission Format specifies how data in the lockbox bank file should be organized such that it can be successfully imported into receivables interface tables. Example, Default, Convert, Cross Currency, Zengen are some of the standard formats provided by oracle.
10. What is Auto Invoice?
A) Autoinvoice is a tool used to import and validate transaction data from other financial systems and create invoices, debit-memos, credit memos, and on account credits in Oracle receivables. Using Custom Feeder programs transaction data is imported into the autoinvoice interface tables.
Autoinvoice interface program then selects data from interface tables and creates transactions in receivables (Populates receivable base tables) . Transactions with invalid information are rejected by receivables and are stored in RA_INTERFACE_ERRORS_ALL interface table.
11. What are the Mandatory Interface Tables in Auto Invoice?RA_INTERFACE_LINES_ALL, RA_INTERFACE_DISTRIBUTIONS_ALL
RA_INTERFACE_SALESCREDITS_ALL.
12. What are the Set up required for Custom Conversion, Autolockbox and Auto Invoice?
A) Autoinvoice program Needs AutoAccounting to be defined prior to its execution.
13. What is AutoAccounting?A) By defining AutoAccounting we specify how the receivables should determine the general ledger accounts for transactions manually entered or imported using Autoinvoice. Receivables automatically creates default accounts(Accounting Flex field values) for revenue, tax, freight, financial charge, unbilled receivable, and unearned revenue accounts using the AutoAccounting information.
14. What are Autocash rules?A) Autocash rules are used to determine how to apply the receipts to the customers outstanding debit items. Autocash Rule Sets are used to determine the sequence of Autocash rules that Post Quickcash uses to update the customers account balances.
15. What are Grouping Rules? (Used by Autoinvoice)
A) Grouping rules specify the attributes that must be identical for lines to appear on the same transaction. After the grouping rules are defined autoinvoice uses them to group revenues and credit transactions into invoices debit memos, and credit memos.
16. What are Line Ordering Rules? (Used by Autoinvoice)A) Line ordering rules are used to order transaction lines when grouping the transactions into invoices, debit memos and credit memos by autoinvoice program. For instance if transactions are being imported from oracle order management , and an invoice line ordering rule for sales_order _line is created then the invoice lists the lines in the same order of lines in sales order.
17. In which table you can see the amount due of a customer?
A) AR_PAYMENT_SCHEDULES_ALL
18. How do you tie Credit Memo to the Invoice?
At table level, In RA_CUSTOMER_TRX_ALL, If you entered a credit memo, the PREVIOUS_CUSTOMER_TRX_ID column stores the customer transaction ID of the invoice that you credited. In the case of on-account credits, which are not related to any invoice when the credits are created, the PREVIOUS_CUSTOMER_TRX_ID column is null.
19. What are the available Key Flex Fields in Oracle Receivables?A) Sales Tax Location Flex field, It’s used for sales tax calculations.
Territory Flex field is used for capturing address information.
20. What are Transaction types? Types of Transactions in AR?
A) Transaction types are used to define accounting for different transactions such as Debit Memo, Credit Memo, On-Account Credits, Charge Backs, Commitments and invoices.
AR TO GL LINK IN R12
AR TO GL LINK IN R12( THROUGH XLA) or ( WITH XLA )
/* Formatted on 11/29/2013 4:46:26 PM (QP5 v5.114.809.3010) */
SELECT ooha.order_number,
ooha.org_id,
hca.account_name,
hp.party_name "Customer Name",
hcasab.orig_system_reference BILL_TO_ORIG_REF,
hpsb.status BILL_TO_STATUS,
'ADDRESS1 - '
|| bill_loc.address1
|| ','
|| CHR (10)
|| 'ADDRESS2 - '
|| bill_loc.address2
|| ','
|| CHR (10)
|| 'ADDRESS3 - '
|| bill_loc.address3
|| ','
|| CHR (10)
|| 'CITY - '
|| bill_loc.city
|| ','
|| CHR (10)
|| 'POSTAL CD- '
|| bill_loc.postal_code
|| ','
|| CHR (10)
|| 'COUNTRY - '
|| bill_loc.country
BILL_TO_ADDRESS,
hcasas.orig_system_reference SHIP_TO_ORIG_REF,
hpss.status SHIP_TO_STATUS,
'ADDRESS1 - '
|| ship_loc.address1
|| ','
|| CHR (10)
|| 'ADDRESS2 - '
|| ship_loc.address2
|| ','
|| CHR (10)
|| 'ADDRESS3 - '
|| ship_loc.address3
|| ','
|| CHR (10)
|| 'CITY - '
|| ship_loc.city
|| ','
|| CHR (10)
|| 'POSTAL CD- '
|| ship_loc.postal_code
|| ','
|| CHR (10)
|| 'COUNTRY - '
|| ship_loc.country
SHIP_TO_ADDRESS,
oola.inventory_item_id,
oola.ordered_item,
msib.description item_description,
wnd.name delivery_number,
rct.trx_number "AR Invoice Number",
acr.receipt_number "AR Receipt Number",
gjh.ledger_id,
gjh.name
FROM oe_order_headers_all ooha,
oe_order_lines_all oola,
hz_parties hp,
hz_cust_accounts hca,
hz_party_sites hpss,
hz_party_sites hpsb,
hz_locations bill_loc,
hz_locations ship_loc,
hz_cust_acct_sites_all hcasab,
hz_cust_acct_sites_all hcasas,
hz_cust_site_uses_all hzsuab,
hz_cust_site_uses_all hzsuas,
mtl_system_items_b msib,
wsh_delivery_details wdd,
wsh_new_deliveries wnd,
wsh_delivery_assignments wda,
ra_customer_trx_all rct,
ra_customer_trx_lines_all rctl,
ra_cust_trx_line_gl_dist_all rctld,
ar_cash_receipts_all acr,
xla.xla_transaction_entities xte,
xla_events xe,
xla_ae_headers xah,
xla_ae_lines xal,
xla_distribution_links xdl,
gl_import_references gir,
gl_je_batches gjb,
gl_je_headers gjh,
gl_je_lines gjl
WHERE ooha.order_number = :SalesOrderNumber--Right click :RequitionNumber from Toad Enable Prompt For Substitution Variables
AND ooha.org_id = 204
AND hca.cust_account_id = ooha.sold_to_org_id
AND hp.party_id = hca.party_id
AND hpss.party_id = hca.party_id
AND hpsb.party_id = hca.party_id
AND bill_loc.location_id = hpss.location_id
AND ship_loc.location_id = hpsb.location_id
AND hcasas.cust_account_id = hca.cust_account_id
AND hcasab.cust_account_id = hca.cust_account_id
AND hcasas.party_site_id = hpss.party_site_id
AND hcasab.party_site_id = hpsb.party_site_id
AND hzsuas.cust_acct_site_id = hcasas.cust_acct_site_id
AND hzsuab.cust_acct_site_id = hcasab.cust_acct_site_id
AND hzsuas.site_use_id = ooha.ship_to_org_id
AND hzsuab.site_use_id = ooha.invoice_to_org_id
AND wda.delivery_id = wnd.delivery_id(+)
AND wdd.delivery_detail_id = wda.delivery_detail_id
AND wdd.source_header_id = ooha.header_id
AND wdd.source_line_id = oola.line_id
AND wdd.organization_id = msib.organization_id(+)
AND wdd.inventory_item_id = msib.inventory_item_id(+)
AND rct.interface_header_attribute1 = TO_CHAR (ooha.order_number)
AND rct.org_id = ooha.org_id
AND rctl.customer_trx_id = rct.customer_trx_id
AND rctl.sales_order = TO_CHAR (ooha.order_number)
AND rctld.customer_trx_id = rct.customer_trx_id
AND rctld.customer_trx_line_id = rctl.customer_trx_line_id
AND acr.receipt_number = 'G-1001'
AND acr.pay_from_customer = rct.sold_to_customer_id
AND acr.org_id = ooha.org_id
AND acr.customer_site_use_id = rct.bill_to_site_use_id
AND xte.transaction_number = acr.receipt_number
AND xte.entity_code = 'RECEIPTS'
AND xe.entity_id = xte.entity_id
AND xah.event_id = xe.event_id
AND xal.ae_header_id = xah.ae_header_id
AND xal.accounting_class_code = 'CASH'
AND xdl.ae_header_id = xah.ae_header_id
AND xdl.ae_line_num = xal.ae_line_num
--and xdl.source_distribution_id_num_1
AND gir.reference_5 = xte.entity_id -- Entity Id
AND gir.reference_6 = TO_CHAR (xe.event_id) --Event Id
AND gir.reference_7 = TO_CHAR (xah.ae_header_id) -- AE Header Id
AND gir.gl_sl_link_id = xal.gl_sl_link_id
AND gir.created_by = 1318
AND gjb.je_batch_id = gir.je_batch_id
AND gjh.je_batch_id = gjb.je_batch_id
AND gjh.je_header_id = gir.je_header_id
AND gjl.je_header_id = gjh.je_header_id
AND gjl.je_line_num = gir.je_line_num
/* Formatted on 11/29/2013 4:46:26 PM (QP5 v5.114.809.3010) */
SELECT ooha.order_number,
ooha.org_id,
hca.account_name,
hp.party_name "Customer Name",
hcasab.orig_system_reference BILL_TO_ORIG_REF,
hpsb.status BILL_TO_STATUS,
'ADDRESS1 - '
|| bill_loc.address1
|| ','
|| CHR (10)
|| 'ADDRESS2 - '
|| bill_loc.address2
|| ','
|| CHR (10)
|| 'ADDRESS3 - '
|| bill_loc.address3
|| ','
|| CHR (10)
|| 'CITY - '
|| bill_loc.city
|| ','
|| CHR (10)
|| 'POSTAL CD- '
|| bill_loc.postal_code
|| ','
|| CHR (10)
|| 'COUNTRY - '
|| bill_loc.country
BILL_TO_ADDRESS,
hcasas.orig_system_reference SHIP_TO_ORIG_REF,
hpss.status SHIP_TO_STATUS,
'ADDRESS1 - '
|| ship_loc.address1
|| ','
|| CHR (10)
|| 'ADDRESS2 - '
|| ship_loc.address2
|| ','
|| CHR (10)
|| 'ADDRESS3 - '
|| ship_loc.address3
|| ','
|| CHR (10)
|| 'CITY - '
|| ship_loc.city
|| ','
|| CHR (10)
|| 'POSTAL CD- '
|| ship_loc.postal_code
|| ','
|| CHR (10)
|| 'COUNTRY - '
|| ship_loc.country
SHIP_TO_ADDRESS,
oola.inventory_item_id,
oola.ordered_item,
msib.description item_description,
wnd.name delivery_number,
rct.trx_number "AR Invoice Number",
acr.receipt_number "AR Receipt Number",
gjh.ledger_id,
gjh.name
FROM oe_order_headers_all ooha,
oe_order_lines_all oola,
hz_parties hp,
hz_cust_accounts hca,
hz_party_sites hpss,
hz_party_sites hpsb,
hz_locations bill_loc,
hz_locations ship_loc,
hz_cust_acct_sites_all hcasab,
hz_cust_acct_sites_all hcasas,
hz_cust_site_uses_all hzsuab,
hz_cust_site_uses_all hzsuas,
mtl_system_items_b msib,
wsh_delivery_details wdd,
wsh_new_deliveries wnd,
wsh_delivery_assignments wda,
ra_customer_trx_all rct,
ra_customer_trx_lines_all rctl,
ra_cust_trx_line_gl_dist_all rctld,
ar_cash_receipts_all acr,
xla.xla_transaction_entities xte,
xla_events xe,
xla_ae_headers xah,
xla_ae_lines xal,
xla_distribution_links xdl,
gl_import_references gir,
gl_je_batches gjb,
gl_je_headers gjh,
gl_je_lines gjl
WHERE ooha.order_number = :SalesOrderNumber--Right click :RequitionNumber from Toad Enable Prompt For Substitution Variables
AND ooha.org_id = 204
AND hca.cust_account_id = ooha.sold_to_org_id
AND hp.party_id = hca.party_id
AND hpss.party_id = hca.party_id
AND hpsb.party_id = hca.party_id
AND bill_loc.location_id = hpss.location_id
AND ship_loc.location_id = hpsb.location_id
AND hcasas.cust_account_id = hca.cust_account_id
AND hcasab.cust_account_id = hca.cust_account_id
AND hcasas.party_site_id = hpss.party_site_id
AND hcasab.party_site_id = hpsb.party_site_id
AND hzsuas.cust_acct_site_id = hcasas.cust_acct_site_id
AND hzsuab.cust_acct_site_id = hcasab.cust_acct_site_id
AND hzsuas.site_use_id = ooha.ship_to_org_id
AND hzsuab.site_use_id = ooha.invoice_to_org_id
AND wda.delivery_id = wnd.delivery_id(+)
AND wdd.delivery_detail_id = wda.delivery_detail_id
AND wdd.source_header_id = ooha.header_id
AND wdd.source_line_id = oola.line_id
AND wdd.organization_id = msib.organization_id(+)
AND wdd.inventory_item_id = msib.inventory_item_id(+)
AND rct.interface_header_attribute1 = TO_CHAR (ooha.order_number)
AND rct.org_id = ooha.org_id
AND rctl.customer_trx_id = rct.customer_trx_id
AND rctl.sales_order = TO_CHAR (ooha.order_number)
AND rctld.customer_trx_id = rct.customer_trx_id
AND rctld.customer_trx_line_id = rctl.customer_trx_line_id
AND acr.receipt_number = 'G-1001'
AND acr.pay_from_customer = rct.sold_to_customer_id
AND acr.org_id = ooha.org_id
AND acr.customer_site_use_id = rct.bill_to_site_use_id
AND xte.transaction_number = acr.receipt_number
AND xte.entity_code = 'RECEIPTS'
AND xe.entity_id = xte.entity_id
AND xah.event_id = xe.event_id
AND xal.ae_header_id = xah.ae_header_id
AND xal.accounting_class_code = 'CASH'
AND xdl.ae_header_id = xah.ae_header_id
AND xdl.ae_line_num = xal.ae_line_num
--and xdl.source_distribution_id_num_1
AND gir.reference_5 = xte.entity_id -- Entity Id
AND gir.reference_6 = TO_CHAR (xe.event_id) --Event Id
AND gir.reference_7 = TO_CHAR (xah.ae_header_id) -- AE Header Id
AND gir.gl_sl_link_id = xal.gl_sl_link_id
AND gir.created_by = 1318
AND gjb.je_batch_id = gir.je_batch_id
AND gjh.je_batch_id = gjb.je_batch_id
AND gjh.je_header_id = gir.je_header_id
AND gjl.je_header_id = gjh.je_header_id
AND gjl.je_line_num = gir.je_line_num
It was very usefull. Thnk- you
ReplyDeletenice one
ReplyDeleteVery good description :)
ReplyDeletevery good and short description :)
ReplyDeleteOverall ts Excellent,
ReplyDeleteI feel little correction in the last query
AND bill_loc.location_id = hpss.location_id
AND ship_loc.location_id = hpsb.location_id
I think it should be
AND bill_loc.location_id = hpsb.location_id
AND ship_loc.location_id = hpss.location_id
How Lemeridian funding service grant me a loan!!!
ReplyDeleteHello everyone, I'm Lea Paige Matteo from Zurich Switzerland and want to use this medium to express gratitude to lemeridian funding service for fulfilling his promise by granting me a loan, I was stuck in a financial situation and needed to refinance and pay my bills as well as start up a Business. I tried seeking for loans from various loan firms both private and corporate organisations but never succeeded and most banks declined my credit request. But as God would have it, I was introduced by a friend named Lisa Rice to Le_meridian funding service and undergone the due process of obtaining a loan from the company, to my greatest surprise within 48hrs just like my friend Lisa, I was also granted a loan of $216,000.00 So my advise to everyone who desires a loan, "if you must contact any firm with reference to securing a loan online with low interest rate of 1.9% and better repayment plans/schedule, please contact Le_meridian funding service. Besides, he doesn't know that am doing this but due to the joy in me, I'm so happy and wish to let people know more about this great company whom truly give out loans, it is my prayer that GOD should bless them more as they put smiles on peoples faces. You can contact them via email on {lfdsloans@lemeridianfds.com Or lfdsloans@outlook.com} or Text through Whatsapp +1-989 394 3740.