Issue : In AR system having some unappllied/on account, receipts are accumaleted since from some time.Lockbox functionality is being used for receipts and auto cash rule set is defined with one rule that is "Match payment with Invoice" Suppose lock box is run for this month and receipts are created. While running quick cach, it will apply existing unapplied/on account receipts before applying new receipts or it will apply only new receipts? For that we have to change autocash rule set? If how?
Solution: Lockbox process creates New receipts and Post quick cash program applies receipts to right Invoices. Both programs works on file to file basis. Post quick cash does not look for all unapplied receipts. You need to select file or transmission id before running post quick cash, it is more localized to that file but not application of all unapplied receipts in general.
1) While running quick cash it will consider auto cach rules set or it will apply based on matching rule?
2) Exact requirement of me is - Auto apply of on account receipts/unapplied receipts/on credit memos to oldest invoices? Based on your answer it is not possible using auto cash rule set?
Issue: AutoInvoice is creating two Invoices for different customer PO numbers but having same Order no., Order Type and Delivery Id.
As per the setup done in Grouping rule where its set as ATTRIBURE1,ATTRIBURE2,ATTRIBURE3 means Order number, Order Type and Delivery Id resp. But system is creating 2 invoices for 2 lines in an sales order that is shipped togather but having different customer PO for these two lines in Sales Order.
Issue: How to link Receipts with Transactions?
How to get the receipt information for the Transaction(invoice etc) in Receivables. Which column need to be joined to get that information?
AR_CASH_RECEIPTS_ALL (CASH_RECEIPT_ID) -
>AR_RECEIVABLE_APPLICATIONS_ALL (PAYMENT_SCHEDULE_ID) -> AR_PAYMENT_SCHEDULES_ALL (PAYMENT_SCHEDULE_ID)
Another table which has both receipt information as well as invoice information is- AR_RECEIPTS_REP_ITF.
Issue: AR/AP Reconciliation
1. Customer A pays advance to us (he is also the supplier)
2. We ship goods to another company B who inturn ships to the end consumer
3. We pay to B from the advance received from customer
4. A being a supplier as well and he has to get paid for the goods so we pay him as well
Advance is created in AR while the payments are out of AP. How can the AR advance be offset against the AP payments?
Solution: AR/AP Netting functionality could help for the scenario explained above. If you have same person as customer and supplier at the end of each month or any month that you want to settle, you can run steps for AR/AP netting process. This process is specific to one customer & supplier.
1. Some balance payable in AP, some amount receivable in AR
2. Need to balance them and project outstanding balance to customer
3. AP/AR netting process is part of Americas localization and you can add this function in AR menu and AP menu.
4. System checks for same name or Tax id before processing netting process.
5. Create set ups as required
6. Run AR/AP netting process, process populates netting /contra entries in both AP and AR, user need to validate and account in AP and complete them in AR.
7. Report will showcase entries created in the system.
Issue: Sub-Customers with main Customer Account.
Is it possible to set up "sub customers" in Receivables, rolling up into a main account?
The req is to split our customers into different Faculties (thus ESCOM for example) must look as follows: - ESCOM 1 - FACULTY ONE - ESCOM 2 - FACULTY TWO
At the end of the month we want to send a statement for ESCOM (the main account), made up of the invoices to the sub accounts.
Issue: How to disable AutoAccounting?
We will pass distributions through interface table instead of replying on AutoAccounting. Is there anything we should do to disable autoaccounting, or the distribution lines will override autoaccounting automatically? Can we pass part of the segments and use autoaccounting definition for the rest of the segments?
Solution: If you populate the interface distributions table, autoaccounting will be ignored.
You can populate account combinations in your data feed or populate few from data feed and auto accounting fill balance from auto accounting set up. We cannot disable auto accounting, it is used to create or validate accounting combinations provided or defaulted into data feed.
Issue: Payment terms
How to setup payment term for a customer who agreed to make cash payment within 'n' number of days?
Solution: You can attach the required payment term to customer at the customer profile level.
Issue: LockBox - ERROR MICR
We are running the lockbox process and are receiving the following error:
ERROR MICR and Customer number do not refer to the same customer.
We understand why we are receiving this error and we know what to do to fix it.
My question is: How can we identify what other customers are already assigned to this MICR number? We are not able to query the "Account Name" field on the Customer Address screen across all customers.
Basically, we just want to be able to search for that MICR number to see what other customer it is already assigned to. Is there an easy way to do this?
Issue: SQL to find inactive customer sites.
Solution: SQL script to find inactive vendors:
SELECT vend.segment1 Vendor#
, vend.end_date_active VEND_INACTIVE
, sites.inactive_date SITE_INACTIVE
po_vendors vend, po_vendor_sites_all sites, ap_invoices_all inv, po_headers_all PO
vend.vendor_id = sites.vendor_id AND
sites.vendor_site_id = inv.vendor_site_id(+) AND
inv.vendor_site_id IS NULL AND
sites.vendor_site_id = po.vendor_site_id(+) AND
po.vendor_site_id IS NULL
The table HZ_CUST_ACCT_SITES has a column STATUS. Check the status 'I' there and link that to main table HZ_CUST_ACCOUNTS. To get the address of the site, link the table HZ_PARTY_SITES using PARTY_SITE_ID:
HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID = HZ_CUST_ACCT_SITES.CUST_ACCOUNT_ID AND HZ_CUST_ACCT_SITES.PARTY_SITE_ID = HZ_PARTY_SITES.PARTY_SITE_ID
[The sites that have been created and not contain any accounting transaction against it, for example invoices, advances, credit etc.
So I Think the req is to join the customers sites vs ra_customer_trx_lines_all.
The reason of this SQL is that I am planning to migrate Customer Headers and Customer Sites to another operating Unit, and I only want to transfer those sites that has activity.]
Issue: HZ_LOCATIONS / HZ_PARTY_SITES population through TCA Bulk Import?
I create an import batch using hz_imp_batch_summary_v2pub.create_import_batch and activate the batch using hz_imp_batch_summary_v2pub.activate_batch.
Then I populate the hz_imp_parties_int and hz_imp_addresses_int with details for a single customer.
Finally I submit the 'Import Batch to TCA Registry' concurrent request to import the data from my batch which completes without error.
Checking the TCA tables I find that I have a record in hz_parties with the address details from the hz_imp_addresses_int table but I do not have corresponding records in hz_locations or hz_party_sites.
Solution: It appears this is an environment config issue.
*I am trying to upload a single organization with one address. Below are the steps I have taken.
1. I created a batch and activated it.
2. Created an entry for one organization-
insert into HZ_IMP_PARTIES_INT (BATCH_ID, PARTY_ORIG_SYSTEM , PARTY_ORIG_SYSTEM_REFERENCE , INSERT_UPDATE_FLAG , PARTY_TYPE , PARTY_NUMBER, ORGANIZATION_NAME , ORGANIZATION_TYPE, PREF_FUNCTIONAL_CURRENCY, EMPLOYEES_TOTAL, LINE_OF_BUSINESS,CREATION_DATE )
3. But when I tryed to create an entry for address_int table I got confused on mapping . As how CDH will understand for which org this address is to be added. Here is my entry the empty '<>' are the values I am not sure about. Could you also tell me where can i find the "Import batch to TCA registry" concurrent program?
INSERT INTO HZ_IMP_ADDRESSES_INT( BATCH_ID, PARTY_ORIG_SYSTEM,PARTY_ORIG_SYSTEM_REFERENCE, SITE_ORIG_SYSTEM, SITE_ORIG_SYSTEM_REFERENCE, INSERT_UPDATE_FLAG, ADDRESS1,ADDRESSS2, CITY, POSTAL_CODE,COUNTRY )
Solution: Recently, we have imported around 70k records using TCA Bulk Import.
1) You will find the "Import Registry to TCA" program under the Trading Community Manager or Oracle Customers Online Super User Responsbility.
2) While inserting into HZ_IMP_ADDRESSES_INT table; the party_source_system and Party Source System Reference should match the values from HZ_IMP_PARTIES_INT table. Means, you are creating addrress for the party imported. SITE_SOURCE_SYSTEM and SITE_SOURCE_SYSTEM_REFERENCE are unique values from the source system for keeping source system references.
Issue: Missing Invoice numbers.
We in Our Instance are having Gapless numbering system. However we have found that we are having some invoice numbers are missing in the sequence. How to Find out the Missing invoice numbers and possible reason for the same?
Issue: Reconciliation of AR transactions with GL
Solution: Steps are:In Receivables
1. Complete All Transactions for the Period Being Closed
2. Reconcile Transaction Activity for the Period3. Reconcile Outstanding Customer Balances
4. Review the Unapplied Receipts Register
5. Reconcile receipts.
6. Reconcile Receipts to Bank Statement Activity for the Period
7. Post to the General Ledger
8. Reconcile the General Ledger Transfer Process
9. Reconcile the Journal Import Process
10. Print Invoices
11. Print Statements (Optional)
12. Print Dunning (Reminder) Letters (Optional)
13. Close the Current Oracle Receivables Period
14. Reconcile Posted Journal Entries
15. Review Unposted Items Report
16. Open the Next Oracle Receivables Period
17. Run Reports for Tax Reporting Purposes (Optional)
18. Run Archive and Purge programs (Optional)
AR Beginning AR Trial Balance + Transaction Register - Receipt Register +/- Adjustment Reqister - Prepayments Remittance Report* = Ending AR Balance
Issue: Authoriation Levels in AR:When working with cash, one of our user needs to be able to approve items $25 and under only. Is there an authorization level within Oracle Receivables where we can define a limit of $25 and under for specific users?
Solution: You can do by setting up in approval limits.
(N) Setup->Transactions->Approval Limits.
Issue: Customer Numbering.
In receivables, if I want to start my customer numbering from 00001 and site numbering starts from 10000. Suggest me the areas which I can able to do the setups according the above requirement?
Solution: To automatically assign a unique number to every new customer or site, check the Automatic Customer Numbering box or Automatic Site Numbering box . Do not check this box if you want to manually assign customer numbers. (setup->system->system options->trans and customer (click the tab)-> customer section below).
• Switch to the Application Developer responsibility.
• In the Navigator window select Application, then Database, and finally Sequence.
• In the Name field of the Sequences window, query for HZ_ACCOUNT_NUM_S and for site HZ_CUST_ACCT_SITES_S.
• In the Start Value field of the Sequences window, enter the initial number to be used for Automatic Customer Numbering or Automatic Site Numbering .
Issue: Default invoicing rule.
What is the default invoicing rule, if no rule is assigned to the transactions? If no rule is assigned do we still need to run the Revenue Recognition Program ?
Solution: Invoicing rule is first step if you want a Deferred revenue recognition process . If you don't want deferred revenue you can directly run revenue recognition program with out Invoicing rule.
If no invoice rule is not assigned to the transaction either in invoice work bench or in autoinvoice, what is the Oracle dafault that gets assigned to these transactions?
Issue: Revenue Recognition
We have a requirement-
a) To create a single customer invoice in oracle AR with multiple lines.
b) Each line with different items having different accounting rules.
Note: Orders are created in legacy system and imported into AR thro' autoinvoice.
Solution: You can assign separate Revenue recognition rules for each line in Invoice, you can assign this rule at Item level, Order lines level or at Invoice level. If you have different rules for multiple lines in one invoice, you can assign different rules and achive what you are aiming for.
* The business requirement is
1. Ability to change the revenue recognition rules in oracle AR whenever the contract period for a customer is changed, even after the invoice is created in AR the system should have the flexibility to manually change the rules.(manual intervention is required in oracle AR to adjust the revenue rules) Example: (from ‘Ratable’ to 50% ‘upfront’)
Note: The understanding from the business is, within the same invoice, the same product/item could have more than one accounting and invoicing rule attached based on the combination of brochures. The business requirement is to generate a single invoice.
My question is would an order with multiple lines imported from legacy system into oracle AR having different revenue recognition rules will result in a single invoice or multiple invoices based on the rules attached?
Autoinvoice uses, Grouping Rules. Invoicing and Accounting Rules are part of Mandatory Grouping rules.If these rules are different, multiple invoices will be created.
Invoicing rule id is mandatory which is related to Invoicing in advance or arreas ( just to create revenue at 1st of the month or last of the month) and Accounting duration or id is in Optional rules. I am not sure am I refering a old document How Autoinvoice Grouping Works [Doc ID: Note:98772.1 ]
The following columns on ra_interface_lines_all are mandatory grouping columns:
In addition, if you use the invoice transaction flexfield then the interface_line_attributes which are defined for your invoice transaction flexfield will become mandatory grouping columns.
Optional grouping columns include but are not limited to the following:
1)If Invoice rules are different, multiple invoices will be created.
2)Accounting rules are not part of grouping rules, hence if rest of the grouping rules are same, a single invoice will be created. However, if you need multiple invoices, you can do so by adding accounting rule in Optional Grouping rules.
Issue:Define a Business Purpose for a Customer
I want to define a new business Purpose for customer. This is where we define the customer as a "ship to" or "bill to" etc. I want to add values to that LOV. I know that it can be done from the Lookups but dont exactally know where.
Solution: Go to setup/system/quickcodes/receivables and do a search on SITE_USE_CODE and that is where you can add a new business purpose.