AR: Receivables Important Information
Maybe your like
Tuesday, December 9, 2014
AR: Receivables Important Information
Accounts Receivables ==================== ACCOUNTING METHOD , ACCRUAL OR CASH : So do you set the accounting method only at the Payables,Receivables levels, not at the GL Level. I believe so,because of those settings,payables and receivables will generate the journal entries accordingly. /*Introduction : Companies can sell their products either for cash (which is checks, credit cards etc)or as invoiced sales on credit with specific payment terms. Invoiced sales create a receivable on the balance sheet (on GL) which represents the money due to the company. Receivables produce 3 legal docs which are invoice,statement and dunning letter. The different types of transactions that are available are Invoice (debit item) debit memo (debit item) credit memo adjustments chargebacks (debit item) commitments refunds Apart from the above transactions, the most important thing is Receipts */ /*Make sure for the period that you are defining an invoice batch, that period is either open or future enterable.*/ control=> accounting => open/close periods /*Actually in a system, we can know what is the set of books by running the command */ begin dbms_output.put_line(fnd_profile.value('gl_set_of_bks_id')) ; -- or name end; /* Once we get the set of books id and name, we can lookup the , Accounting flexfield structure, operational currency and the fiscal calendar. Another important thing is the accounts(like retained earnings etc). For successfully defining a batch we need to have all the setup data correctly defined like the currencies, accounting period, period types and set of books. SET THE ORG_ID TO 101 */ -- Now let us start with the first transaction "INVOICE" and see what ACCOUNTS --will get updated. select organization_id,name -- 82 for Netflix US from hr_all_organization_units begin fnd_client_info.set_org_context(fnd_profile.value('ORG_ID')); end; select set_of_books_id ,name from gl_sets_of_books where set_of_books_id = 1 /*Now let us say, we are trying to create an invoice, in a particular batch The source and currency information will default to the values specified in the batch. Now we set the class to Invoice. For each invoice class we can have different types of invoices. We can create different types of invoices in setup data in (setup, transactions, transaction type). For ex we can create 2 txn types both of type invoice but one with printing and one without printing option,or having different GL accounts for revenue, tax freight etc. All this information goes into "ra_cust_trx_types_all" table. A word about "Open Receivables" and "Transfer to GL" :In the transaction types set up from usually we have 2 check boxes apart from different fields and different accounts setups. They are Open receivables and transfer to GL. Open receivables means, whenever a transaction of this type is created, then the customer balance will get affected. That is,when a transaction is created, it finds an entry immediately in the payment schedules_all table once the transaction is 'complete'd.And since the customer balances are always calculated based on this important table, the balance will get affected. If 'Open Receivables' is not set, then even if we complete the transaction,it will not appear in the payment schedules table and hence the balances are not reflected. Transfer to GL : If this set then the transaction is transferred to GL, once the GL transfer program runs, otherwise not. Usually companies implement by creating a VOID transaction by not setting these flags in the transaction type. Now for the purpose of argument, let us say we have Open Receivables to Yes, and Transfer to GL set to No, then we are recording some transaction in AR, but not showing that up in GL which is not correct. Open Receivables to No, and Transfer to GL set to Yes,usually this can happen in conversion transactions. Usually, we can create a trx type with Open Rec to No for the transactions which you want to review initially and when you are satisfied, you can change the trx type to Final(with open rec to Yes). Usually changing the trx type will make the AutoAccounting rerun and create the correct gl entries. (post-to-gl checkbox is used for adjustment (whcih generally happen in small amounts) and need not be reflected in gl account balances) */ select a.cust_trx_type_id,a.name,a.description, a.type,a.org_id,a.* from ra_cust_trx_types_all a /* The invoice batch source is properly set up. The following query can be used to check that. FOR THE INVOICE BATCH SOURCE*/ SELECT rowid, auto_trx_numbering_flag, name, org_id, description, batch_source_type, batch_source_id, status, default_inv_trx_type, start_date, end_date,creation_date FROM ra_batch_sources WHERE batch_source_type = 'INV' AND batch_source_id NOT IN (11, 12) AND org_id = 82 AND status = 'A' /* The invoice batch Currency is properly set up. The following query can be used to check that. FOR THE INVOICE BATCH CURRENCY*/ SELECT fc.currency_code, fc.NAME, fc.description FROM fnd_currencies_vl fc, gl_sets_of_books gl, ar_system_parameters ar WHERE fc.currency_flag = 'Y' AND fc.enabled_flag = 'Y' AND fc.currency_code = gl.currency_code AND gl.set_of_books_id = ar.set_of_books_id -- For the invoice batch gl_date.The following query can be used to check the gl_date. SELECT period_name, closing_status, period_name FROM gl_period_statuses WHERE application_id = 222 AND set_of_books_id = 1 AND adjustment_period_flag = 'N' AND period_name = 'OCT-04' begin dbms_output.put_line('the value is ' ||fnd_profile.value('AR_SET_OF_BKS_ID')); end; /* Hence having created a transaction , we can look at the table ra_customer_trx_all. While creating an invoice transaction online, we can see that the reference number is null. Actually this is the order number(???)that would be populated when AutoInvoice process has pulled the orders from the Order Management to the Accounts Receivables. */ select batch_id,batch_source_id,customer_trx_id,sold_to_customer_id,bill_to_site_use_id, remit_to_address_id,status_trx,paying_customer_id,trx_number,cust_trx_type_id, previous_customer_trx_id,trx_date,creation_date from ra_customer_trx_all order by creation_date desc /* Trx_date, GL_date, Creation_date : the trx_date could be different from creation_date. The trx date could have happened yesterday , but you have not entered it,(say system down) and you are entering it now. Then in that case, the trx_date is yesterday and creation_date is today. The gl_date is not stored at the trx or line level. it is stored only in line dist level. The gl_date is required because when we transfer the trx to GL,it will pick all the records from the gl_distributions table,which fall with in the range specified in the GL transfer request form.*/ -- And the invoice lines can be seen from the query select customer_trx_line_id line_id,set_of_books_id,description,quantity_invoiced, unit_selling_price,line_type,org_id from ra_customer_trx_lines_all where customer_trx_id = 1034 /* The distributions of the INVOICE line are given by query below. Hence the two important accounts that will get affected by Invoice transaction are Receivables and Revenue.*/ select cust_trx_line_gl_dist_id dist_id, customer_trx_line_id line_id, code_combination_id, set_of_books_id ,amount,gl_date,account_class,customer_trx_id, org_id from ra_cust_trx_line_gl_dist_all where customer_trx_id = 1035 /* A useful query to give us the code_combination_id given an account number is given below. So for the invoice process, the receivables account will get debited and the revenue account (tax,freight etc) will be credited.*/ select a.segment1||'-'||a.segment2||'-'||a.segment3||'-'||a.segment4||'-'||a.segment5 ||'-'||a.segment6 acct_code,a.* from gl_code_combinations a where segment1 =01 and segment2 = 0000 and segment3 = 0000 and segment4 in (11100,40310) -- 11100(4587) is receivable and 40310(1386) is revenue account. and segment5 = 0000 and segment6 = 0000 and segment7 = 0000 and segment8 = 0000 /*REMIT TO Address: While creating a transaction, we may have to enter the remit to address. Basically remit to address is the address to which the customer should send his payment to. You can create remit to address using the following menu option setup => print => remit to This will pull up the Remit-To Address form where you will enter the remit-to addresses. Basically what we specify here is that for a range of zip codes(based on country and state), we can specify the payment to be sent to a particular address i.e a local address. */ -- COMPLETE THE TRANSACTION /INVOICE/CREDIT MEMO. /*Having created all the above, we need to COMPLETE the txn, and the important steps that we should look at for the completion process are given below. Validation for completing a standard transaction The invoice must have at least one line. The GL date of the invoice must be in an Open or Future period. The invoice sign must agree with the creation sign of the transaction type. The sum of distributions for each line must equal the invoice line amount. If the Calculate Tax field for the transaction type is set to Yes, tax is required for each line (except lines of type Charges). If freight was entered for this transaction, you must specify a freight account. If the system option Require Salesreps is Yes, salespersons must be assigned to each line. If salespeople are assigned to each line, the total revenue sales credit percentage must equal 100%. All the activity date ranges for the setup values (for example, payment terms) must be valid for the invoice date. If this transaction uses an automatic payment method, you must enter Customer bank, branch, and account information.*/ /* Once the invoice (or any) transaction is succesfully COMPLETE'd, then we can use that invoice i.e that invoice goes into the important table called ar_payment_schedules, so that we can apply a receipt to this invoice. Once an invoice is COMPLETE'd then the "Complete" check box is checked. We can try to Incomplete and Complete this particular invoice any number of times, until a receipt is applied against this invoice. Once a receipt is applied against this invoice, then the Complete/Incomplete button is disabled. Also if we want to transfer this transaction to the GL, we want the transaction to be complete. This table stores multiple kinds of information. (Also look at completing the receipt). And once this invoice is transferred then also we cannot incomplete the invoice,infact the Incomplete button will be disabled. */ select customer_trx_id,cash_receipt_id,payment_schedule_id, class,customer_id, trx_number,trx_date ,customer_site_use_id, selected_for_receipt_batch_id btc_id, acctd_amount_due_remaining amt_due ,org_id,reserved_value,status from ar_payment_schedules_all where customer_trx_id = 1034 /* For A CREDIT MEMO.(Another kind of transaction). Now for a credit memo, everything looks identical to that of a invoice, however as far as the accounting entries are concerned, Receivables account will be credited and the revenue accounts will be debited (because it is a credit to the customer. While entering a credit memo, make sure you enter the amount as negative value. */ -- A useful query to give us the code_combination_id given an account number is given below. select a.segment1||'-'||a.segment2||'-'||a.segment3||'-'||a.segment4||'-' ||a.segment5||'-'||a.segment6 acct_code ,a.* from gl_code_combinations a where segment1 =01 and segment2 = 0000 and segment3 = 0000 and segment4 in (11100,40230) -- 11100 is receivable and 40230 is revenue account. and segment5 = 0000 and segment6 = 0000 and segment7 = 0000 and segment8 = 0000 /* The distributions of the invoice line are given by query below. Hence the two important accounts that will get affected by Credit Memo transaction, and they are Receivables and Revenue(of a kind). There is a posting_control_id field in RA_CUST_TRX_LINE_GL_DIST_ALL table. If the posting fails or is unposted yet,you have a value of -3 otherwise if posting is successful you get the next value in the sequence. The moment we run the GL transfer program, these transactions are moved to the GL Interface table and at the end of that process, the "Update Posting Control" process will kick off and it will back populate the posting_control_id in this table. This does not mean that the gl posting is done for this transaction. */ select cust_trx_line_gl_dist_id dist_id, customer_trx_line_id line_id, code_combination_id, set_of_books_id ,amount,gl_date,account_class,customer_trx_id, org_id, posting_control_id from ra_cust_trx_line_gl_dist_all where code_combination_id in (4587,4590) /* There are two ways of associating a Credit Memo to an Invoice. Pull up the original invoice in the Invoice transactions form. From the menu item, Actions => Credit, Pull up the Credit Transaction from, Here in this form, we cannot associate a pre-created credit memo. Instead, we can specify the credit amount (or %) and save this transaction. This will internally create a credit memo. And this credit memo we can try to query again from the transactions form. Look for the column previous_customer_trx_id, which stores the original invoice transaction id. */ select customer_trx_id,previous_customer_trx_id,creation_date,sold_to_customer_id, bill_to_site_use_id,remit_to_address_id,status_trx,paying_customer_id, trx_number,cust_trx_type_id from ra_customer_trx_all where customer_trx_id = 1035 order by creation_date desc /* on-account credit : Alternatively create an credit memo from the transactions workbench which is called "on-account credit memo" or "on-account credits" by requerying the same credit memo from the menu Actions => Adjustments and provide the invoice number to which you can apply the credit memo.*/ -- We can see all the balances for a transaction by clicking on the Balances button. select customer_trx_line_id line_id,set_of_books_id,description,quantity_invoiced, unit_selling_price,line_type,org_id, extended_amount, revenue_amount from ra_customer_trx_lines_all where customer_trx_id = 1035 /* The distributions of the invoice line are given by query below. Hence the two important accounts that will get affected by Invoice transaction are Receivables and Revenue.*/ select cust_trx_line_gl_dist_id dist_id, customer_trx_line_id line_id, code_combination_id, set_of_books_id ,amount,gl_date,account_class,customer_trx_id, org_id from ra_cust_trx_line_gl_dist_all where customer_trx_id = 1035 /*Hence in the credit memo record we can see that we will have reference to the original invoice transaction number. This make sense, because one invoice can have multiple credit memos and each one can store the correct invoice number. It is difficult to store the credit memo information in invoice if there are more than one credit memo for an invoice.*/ /* Adjusting an Invoice transaction. When you adjust an invoice transaction, we can adjust in such a way that the invoice balance is 0. i.e If there is an invoice transaction for $100 and we have a receipt of $50, then we should make an adjustment equal to exactly $50 and not any amount less than that. It is important to note that once an adjustment is made, the adjustment needs to be approved, or the user should have the approval rights to approve the adjustment , otherwise we still see that there is a balance for that invoice transaction. */ select * from ar_adjustments_all /* Approval Limits for adjustments, Receipt Writeoff's and Credit Memos Generally when a user creates an adjustment,small balances write-off or create credit memos, he needs to have an privilege or approval authority to do that. This can be done by creating an approval limit for each of the above. Using the menu item setup => Transactions => approval limits.*/ /* You can associate a credit memo to an invoice which has already been paid.*/ /* when you create a credit memo you can either associate it with an item or with a memo line created in setup */ select memo_line_id,accounting_rule_id, line_type,uom_code, name,description,org_id from ar_memo_lines -- A useful query which will select the memo lines from lov is given below SELECT aml.memo_line_id, aml.description "aml.description", aml.name, al.meaning, aml.line_type FROM ar_memo_lines aml, ar_lookups al, mtl_units_of_measure uom,ra_rules rr, ra_rule_schedules rs WHERE al.lookup_type = 'STD_LINE_TYPE' and al.lookup_code = aml.line_type and aml.uom_code = uom.uom_code (+) and aml.accounting_rule_id = rr.rule_id (+) and rr.rule_id = rs.rule_id (+) /*PAYMENT TERMS : Payment terms indicate when the customer needs to pay the invoice. There are different kinds of payment terms that you can create,based on what you enter in the cutoff region and the detail region. First, simple one is say the invoice is due after 30 days of the invoice creation. (enter only Days field in details) Second one is the invoice is due, on a specific date. (enter only the Date field) Third on a specific day of the month like 15th. (enter only days of month and months ahead) /*Payment terms, some examples of the payment terms are like net15, net30 1% (which means that the invoice is due from the 30th day of the invoice creation date and if made with in that time, the customer gets 1% discount of the invoice total amount) */ -- The following 2 queries give info about the Payment terms. select term_id,name, description from ra_terms where name like 'NET 7' select * --term_id,relative_amount,due_days from ra_terms_lines where term_id = 1056 select * --term_id,relative_amount,due_days from ra_terms_lines where due_day_of_month > 0 -- And if there are any discounts for the terms,it will be here.You dont --find a term_line_id, but only a term_id. select * from ra_terms_lines_discounts -- Split Payment Terms and Installment Invoices in AR /**************************************************** -- simple query to find out the split payment terms in the system. select a.term_id,count(*) from ra_terms a, ra_terms_lines b where a.term_id = b.term_id group by a.term_id having count(*) > 1 select * from ra_terms where term_id = 1070 In general a transaction or an invoice will have a payment term like Net 15 which means that the invoice is due within 15 days from the invoice date( or gl_date). However we can create an installment invoice(for$300) with the payment term being specified as the Installment term(i.e we define a specific installment payment term with ,say four payment schedules as due in 15,30,45, 60 days. When a invoice is created in such a way and completed, then it will have four records in the payment schedules table with the same customer_trx_id, with each installment having an amount due_remaining and original as $75. Now a credit memo or receipt can be applied to any one specific installment driving that installment amount to negative. So to find such customer transactions from the payment schedules we can use the following query.*/ select distinct customer_trx_id ,payment_schedule_id, amount_due_original,amount_due_remaining from ar_payment_schedules_all a where status ='OP' and class ='INV' and amount_due_remaining >0 and exists (select 1 from ar_payment_schedules_all b where amount_due_remaining <0 and b.customer_trx_id = a.customer_trx_id) select * from ra_customer_trx_all where trx_number ='13352' select * from ra_cust_trx_line_gl_dist_all where customer_trx_id = 29936776 --,82584133, 364831854 select * from ar_distributions_all where source_id =364831856 /*Customer Profile : Each customer is associated with a customer profile. The profile tells what is the credit limit for the customer who is the collector for this customer what kind of dunning letter should be sent. what is the grace period before we sent dunning letter. whether a finance charge should be charged or not etc. Consolidated billing invoice can be sent or not. */ -- Payment Terms and Finance Charges in AR : Payment terms,finance charge,grace days are specified as part of a customer profile. Customer Standard > Profile: Document Printing screens. /*You must check/uncheck the flag at both the customer and site levels. Once an invoice is due, and after the grace period, the system starts sending the dunning letters to the customer and at the time of sending dunning letter or printing statements,if the finance charge option for a customer is set at the profile, then that customer is charged a finance charge. Usually Finance Charges are calcuated when running the Statements or printing Dunning Letters. */ /*Proxima Payment Terms : Proxima payment terms is one where the invoice is due on a specific day every month like phone bill,electricity bill,etc. Typically for the proxima payment terms, we enter the cutoff date, days of the month, months ahead fields. Bear in mind that cutoff date is at the header level while the day_of_month,b.months_ahead are at the detail level. */ select a.due_cutoff_day,b.day_of_month,b.months_ahead from ra_terms a, ra_terms_lines b where a.term_id = b.term_id /*Consolidated Billing Invoice (CBN) : A Consolidated Billing Invoice is also like a regular invoice,however it consists of all the activity i.e invoices, credit memos,debit memos, receipts,adjustments etc all consolidated and the net balance is shown on the invoice. You can only run a consolidated billing invoice once per period. That is why you have the facility to run a draft CBN,look at it and then reject it if you dont need it. Here are the following things/features that you need,to ensure so that you can successfully print a CBN. Usually you create a proxima payment term(explained above and associate it to a customer. The Consolidated Billing Invoices program ignores the payment terms assigned to individual debit items when selecting transactions.It looks at the payment terms at the customer bill-to site,address and customer level in that above specific order. When submitting the Print Consolidated Billing Invoices program, you must enter a Cutoff Date. For ex, if the current month is June, and if you enter as 12-JUN-2008, then the program will check for that specific customer, the cut off day is 12 or not.If it is ,then it will pick all the transactions for that customer, which are dated less than the 12-JUN-2008. If you provide a not-null payment terms in the parameter form when printing this consolidated billing invoice and if it does not match payment terms at the site or customer level,no transactions will be selected. */ /* If there are any transactions selected for consolidated billing invoice it would be in this temporary table. However if you reject this invoice, it will reject the CBN, then it would delete from this table. */ select * from ra_cons_inv_trx /* Statements : There are few prerequisites for a statement to be printed for a particular customer. Firstly,in the customer profile we should set option of sending the statements. Usually the statement are printed on a location by location basis. Hence for each location or address we should ensure that a language is being set,otherwise it will print for each language. Similarly whether a finance charge needs to be charged or not,it should be set at the profile option.*/ --If the Accrue Interest option IS SET at the System Options level , Setup => System => System Options => Miscellaneous. /*and if the Finance Charge is set at the Customer Profile level and also while running the statement, then the system will calculate the finance charge and will include that charge as part of the invoice balance. And the corresponding balancing entry is created for a pre-defined receivable activity. We can find a pre-defined receivable activity "Finance Charge" under the menu Setup => Receipts => Receivable Activity. However,if the Accrue Interest option is NOT SET and if the Finance Charge is set at the Customer Profile level and also while running the statement, then the system will calculate the finance charge and show it in the statement,but it will not be part of the invoice balance. Hence it is for only display purposes. If there are multiple bill-to sites, then it is better to create a statement site. */ /* Each transaction type belongs to a class (type) i.e we can have 2 types which are of type invoice class*/ select cust_trx_type_id,name,description,status,type,default_status,gl_id_rec, gl_id_rev,set_of_books_id,org_id from ra_cust_trx_types -- Invoice and accounting schedules. select rule_id, period_number, percent,creation_date,last_update_date from ra_rule_schedules /* -- FOR A DEBIT MEMO. (debit Item) Now for A debit memo, it is similar to that of a credit memo , however as far as the accounting entries are concerned, Receivables account will be debited and the revenue accounts will be credited (because the customer has to pay that much amount back to us). -- FOR A CHARGEBACK. (debit Item) Now for a chargeback, it is identical to that of a debit memo , as far as the accounting entries are concerned, Receivables account will be debited and the revenue accounts will be credited (because the customer has to pay that much amount back to us). -- FOR ADJUSTMENTS. Adjustments are alterations to the debit items. We can separately adjust the tax, frieght or receivables amount of an item and the adjustments can be either positive or negative. YOU NEED NOT INFORM THE CUSTOMER about the adjustment as they are internal corrections that do not affect the legal documents. The accounting entries that are generated in the case of an adjustment are Receivables account credited by the adjustment amount. Adjustment account debited by the adjustment amount. -- FOR COMMITMENTS : deposit commitment and guarantee commitment. A deposit commitment occurs when the customer agrees to pay a deposit for goods for they have not ordered yet. A guarantee commitment is a contractual guarantee of future purchases. Typical accounting entries for commitments will be Receivables debited by the commitment amount Unearned revenue will be credited by the commitment amount. */ -- For all of the above transactions, we can run the following query giving -- diff code combination id's below. select cust_trx_line_gl_dist_id dist_id, customer_trx_line_id line_id, code_combination_id, set_of_books_id ,amount,gl_date,account_class,customer_trx_id, org_id,posting_control_id from ra_cust_trx_line_gl_dist_all where code_combination_id in (4587,4590) /*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 paymenttransactions*/ --RECEIPTS /***************************************************************************/ /*Receipt Creation : After this, we proceed to create a receipt. Here too we first create a receipt batch and a receipt within. And when we create a receipt batch, we need to provide comprehensive receipt hierarchy information. The receipt hierarchy information is given below. receipt source ("ra_batch_sources") || receipt class ("ar_receipt_classes") // payment method bank information ("ap_bank_branches") ("ar_receipt_methods") (bank,bank branches) ("ar_reciept_method_accounts") || bank accounts ("ap_bank_accounts") (Here we use ap_bank_accounts, because banks are owned by AP). All the receipts fall into two main categories which are cash and miscellaneous and when a receipt is created,it goes into "ar_cash_receipts_all" with different types. */ /*Receipt Classes to Receipt Methods is a one-to-many relationship. That is,say if we have receipt class of type DISCOVER. then we can define multiple receipt methods(or payment methods), using the same screen. The ex of payment methods are DISCOVER-NT (Discover Northern Trust), DISCOVER-BOFA(Bank of America) etc. */ /*Banks : We can create banks from the menu item Setup => Receipts => Banks When we define the banks, we can create any type of bank, Internal ,Customer or Supplier. Internal bank is a remittance banks and it and means it is defined for your own company purposes. That is you use that bank for your remittance purposes. */ Each receipt is associated with a receipt class/payment method. When we create a receipt class/payment method, we always associate it with a bank and that is remittance bank. That is in that from, only banks that we see are the remittance banks(and not customer or supplier banks). /* COMPLETE A RECEIPT : Just as we complete a transaction(i.e invoice,credit memo etc) and then it would appear in the ar_payments_schedules_all table, even receipts can be completed. That is a receipt will also have a status of (OP,CL) etc. ie. if we have a receipt of amount say $10, then the receipt in ar_payment_schedules will look like below. Hence a receipt entry in payment schedules table will be exactly identical to a transaction. Hence as long as if there is any balance for a receipt(i.e unapplied balance), then that particular receipt will still be open OP.*/ select amount_due_original,amount_due_remaining,status,class,customer_id, gl_date_closed,trx_number,trx_date,gl_date from ar_payment_schedules_all where cash_receipt_id = 29925610 /*If the customer name is left empty , the status would be UNID (unidentified receipt) and if it is provided the status of the receipt is UNAPP (unapplied). Now if the receipt is also applied for a particular invoice,then the status is Applied. And when we distribute the invoice (or any trxn type), i.e when we mention, to which GL account this particular invoice amount should go to, and to which particulary receivable gl account this should go to, the information goes into the "ra_cust_txn_line_gl_dist" table. (Look for the spreadsheet explaining all the details of the accounting entries in AR in a flow). For applied receipts,ie. receipts for which we know the corresponding invoice and the customer An applied receipt will reduce the customer balance by that amt. The journal entries for say $100 would be Receivables : $100 (cr) $0(db) Cash (Bank Asset Account) : $100 (dr) $0(cr) (It is important to note that above account is cash account which is different from the cash clearing account that is used in the Accounts Payables). Hence the queries that we can use to see the data are given below. */ select a.segment1||'-'||a.segment2||'-'||a.segment3||'-'||a.segment4||'-' ||a.segment5||'-'||a.segment6 acct_code ,a.* from gl_code_combinations a where segment1 =01 and segment2 = 0000 and segment3 = 0000 and segment4 in (14300,10210) --14300(4586) cash account, 10210(4597) unapplied account. and segment5 = 0000 and segment6 = 0000 and segment7 = 0000 and segment8 = 0000 -- Any transaction batches can be obtainted from this query. select * from ra_batches select batch_source_id, name,description,org_id,status, batch_source_type from ra_batch_sources -- Any receipt will go into this table. select cash_receipt_id, amount, currency_code,pay_from_customer,status,type, receipt_number,receipt_date,org_id from ar_cash_receipts_all order by receipt_date desc -- The gl account distributions can be seen from this table. select * -- source_type, source_id, code_combination_id, amount_dr,amount_cr, creation_date,last_update_date,org_id from ar_distributions_all where code_combination_id = 4597 /*Difference between Unapplied and On-account receipts Both unapplied and on-account DO NOT reduce the customer balance. It does not impact a business, if you leave an amount in unapplied or onaccount. Both of them DO NOT reduce the customer balance. It is just a business decision, where in we can decide to have the amount either in unapplied or on account.For ex, if we do not know the customer name while we create a receipt, we can optionally leave that amount as unapplied(which is like that to start with). Similarly if you know the customer for a particular receipt, then you can optionally keep that amount in on-account by going to the applications screen. An ex of a On-Account receipt are prepayments and deposits. If the amount is in unapplied status, we can apply that amount to any debit items like invoice. However if the amount is in on-account, then we cannot apply to any debit items. We have to first unapply the on-account and then apply to any debit items. Unidentified receipts, receipts for which dont know both the invoice and customer information. */ -- APPLY A RECEIPT TO AN INVOICE select customer_trx_id,cash_receipt_id,payment_schedule_id, class, customer_id,trx_number,trx_date,customer_site_use_id, selected_for_receipt_batch_id btc_id,acctd_amount_due_remaining amt_due ,acctd_amount_due_remaining,amount_due_original, amount_due_remaining, amount_applied,amount_credited,org_id,reserved_value,status from ar_payment_schedules where customer_trx_id = 1034 /* Ar_payment_schedules will record both the transaction and receipts. In the case of the transactions,the cash_receipt_id and other receipt related columns are null. And in the case of the receipts, the customer_trx_id, trx_number and other transaction related columns are null.In either case, the status column will indicate whether the transaction or receipt is still open or not.*/ select * from ar_payment_schedules_all select amount, receipt_method_id, customer_bank_account_id, customer_bank_branch_id, customer_site_use_id, receivables_trx_id, receipt_number,comments, last_update_date from ar_cash_receipts_all order by last_update_date desc /* Once we APPLY A RECEIPT to a particular transaction like INVOICE, we can see it from the following table */ select cash_receipt_id, applied_payment_schedule_id, applied_customer_trx_id, payment_schedule_id acctd_amount_applied_from, amount_applied, amount_applied_from, set_of_books_id, customer_trx_id,status, acctd_amount_applied_to applied_customer_trx_line_id from ar_receivable_applications_all where status ='APP' and cash_receipt_id = 1327 /* Invoice to Receipts is a Many to Many relationship. From UI, if we need to know what are all the receipts that have paid for an invoice, then (we can get the receipt trx number). Transactions Summary => Installments => activities. If we need to know the all invoices that a receipt has paid for,then go to receipt => applications. */ /*Apart from the main table AR_PAYMENT_SCHEDULES, there are some other tables which might get updated. They are given by the following queries. */ select line_id, source_id, source_table, source_type, source_type_secondary, code_combination_id, amount_dr, amount_cr, acctd_amount_dr, acctd_amount_cr from ar_distributions_all order by last_update_date desc select * from ar_cash_receipt_history order by last_update_date desc /* Q: Unable to apply a receipt to an invoice. the following query does not give any records because dont know why the ar_payment_schedules table is storing the customer_trx_id as -1 while the ra_customer_trx table stores the actual transaction id and no way they can match. A: This problem can be solved once the transaction (i.e invoice) is complete and if it is complete it would definitely figure in the ar_payment_schedules. */ /* Very Important Point. In Payables, the payments are always tied to a bank account(and gl accounts). Similarly in Receivables, in the receipt batches we see the bank account to which the receipts go into. This information is useful for the reconciliation purposes.*/ /* The following query is very useful as it joins all the related tables and in fact used by one of the 11i forms*/ SELECT * FROM hz_cust_site_uses site_uses, hz_cust_accounts cust_acct, hz_parties party, ra_cust_trx_types ctt, ar_lookups lu, ar_payment_schedules ps, ra_customer_trx trx WHERE site_uses.site_use_id = ps.customer_site_use_id and cust_acct.cust_account_id = ps.customer_id and cust_acct.party_id = party.party_id and ctt.cust_trx_type_id = ps.cust_trx_type_id and ps.selected_for_receipt_batch_id is null and ps.reserved_type is null and ps.reserved_value is null and ps.class not in ('GUAR', 'PMT') and --ps.invoice_currency_code = decode(ps.class, 'CM',:2, ps.invoice_currency_code) and ps.status = 'OP' and ps.class = lu.lookup_code and lu.lookup_type = decode(ps.class, null, 'INV/CM', 'INV/CM') and ps.customer_trx_id = trx.customer_trx_id /* Now having created the Invoices , Receipts etc in Receivables, we can transfer these transactions and receipts to GL. Using the step Interfaces => General Ledger Here it is important to note , we have to give the GL period start and end dates , typically these are the month start and end dates. The above step will spawn the concurrent program "General Ledger Transfer Program" which will,in turn, spawn these programs, "Revenue Recognition" "Journal Import" (If the option is yes in the parameters window) "Updating Posting Control : The moment we run the GL transfer program, these transactions are moved to the GL Interface table and at the end of that process, the "Update Posting Control" process will kick off and it will back populate the posting_control_id in this table. This does not mean that the gl posting is done for this transaction. Similary in the case of receipts the ar_cash_receipt_history_all table will get updated with the corresponding posting_control_id */ -- So effectively the gl_date in the following tables will take of the gl transfer. Transactions ==>> ra_cust_trx_line_gl_dist_all Receipts ==>> ar_cash_receipt_history_all adjustments ==>> ar_adjustments_all select * from gl_je_batches where creation_date = (select max(creation_date) from gl_je_batches) /*At this point we have to post the data. This can be done in General Ledger application using the GL Super User responsibility and using the navigation path "Journals => Post". Find the right batch and post it. Interestingly, there are two ways we can do the GL Posting, 1) Journal => Post which kicks off a conc program 2) Run the "Program - Automatic Posting" which will take a predefined autopost set id (see GL notes) Once the posting process is succesfully completed, we can see the data from the below query. */ select a.segment1||'-'||a.segment2||'-'||a.segment3||'-'||a.segment4 ||'-'||a.segment5||'-'||a.segment6 acct_code ,a.* from gl_code_combinations a where segment1 =01 and segment2 = 0000 and segment3 = 0000 and segment4 in (11100,24100) -- 11100 is receivable, 24100 is revenue. (4587,4589) and segment5 = 0000 and segment6 = 0000 and segment7 = 0000 and segment8 = 0000 -- Watch for the above code_combination_id's in the below queries results. select * --set_of_books_id,code_combination_id, period_name from gl_balances where last_update_date = (select max(last_update_date) from gl_balances) --where set_of_books_id = 82 -- How to apply Discounts in AR: /*We can apply discounts in AR(with out using the OM) byusing the payment terms in AR. For ex let us say if we have a payment term like "2% 15 Net 30" which means that the payment is due with in 30 days from the invoice date and the customer will get 2% discount if the payment is applied within first 15 days(this is usually done by creating discount lines in that payment terms), then when we go to apply this receipt to the invoice and if the application date is with in 15 days, then the discount field is automatically populated with the discount amount and the remaining amount goes into the unapplied account. Now from an accounting standpoint, here the invoice is closed,with the same distributoin. However in the receipt distribution, we can see that there is a new distribution line which is Earned Discounts which is basically receivable activity , corresponding to a particular GL account. So there is an additional journal line. So think of it this way. The invoice balance of $100 has been closed by a customer receipt of $98 and a journal corresponding to receivable activity Earned discount has been applied to the additional $2. If the customer sent a $100 receipt, the $2 will be on unapplied amount. */ /* Customer OPEN Balance and Transactions. At any point of time, if we need to have all the customer transactions and any open balance, we can get it from the menu Collections => Customer Accounts /* AutoLockBox Feature : LockBox Functionality ------------------------------------------ Normally for any company doing business , they have a Accounts Receivable(AR) system. That is ,they receive all kinds of receipts like cash, checks, credit cards, direct debits etc. However the company by itself would not receive all these receipts. Generally all these receipts would go to a different PO box address typically known as Lockbox and from there, the bank would collect all these receipts, deposit money, summarize them and then send that information to the company. All these transactions go into the company's receivable system. So this information would come as a batch of records with count and amount. However for all these transactions, the actual cash is already remitted into their bank. Usually when we setup a lockbox in the AR system, we have to provide a lockbox number. This is a reference to the number of origin of the bank data file. Basically, you should be able to use any number you want, as long as the number is unique. So no, it's not a mandatory number that should be provided by your bank. In Oracle Receivables, the Lockbox process would mainly consist of three steps and they are... 1. Using the sqlloader, import the flat file obtained from bank in a specific format (ex EDI 820,BAI). Once the import process completes, the data will be loaded into AR_PAYMENTS_INTERFACE table. And the process also generates the Lockbox execution report. 2. QuickCash step: Lockbox Validation. The data that is loaded into AR_PAYMENTS_INTERFACE table is now validated by this process and the validated results are written to AR_INTERIM_CASH_RECEIPTS_ALL and AR_INTERIM_CASH_RCPT_LINES_ALL tables and the log is written to Lockbox execution report. If the validation fails, then the process will not write any data into the interim table and we need to fix the report errors. 3. Post QuickCash. Once the data is validated, this validated data is written into the actual AR Receivables tables(like AR_CASH_RECEIPTS etc) and we also get a QuickCash Execution Report. Also this program will look for the AutoCash Rule sets which (i.e whether the oldest invoice or the highest invoice etc). So we can summarize this as AR_PAYMENTS_INTERFACE ==> AR_INTERIM_CASH_RECEIPTS => AR_CASH_RECEIPTS_ALL etc. The Lockbox process, is where the bank gives us the statement periodically consisting of complete receipts and we try to apply to the debit items/on account using the autocash rules.(i.e whether the oldest invoice or the highest invoice etc). (However for reconciliation purposes, the bank can provide all the activity completely until that point, which includes the payments and receipts. And if you try to start the AutoReconciliation process in Cash Management, it will match it against the receipts in the receivables and invoices in payables systems). Lockbox processing is a little bit different from the regular receipt processing. In the case of lockbox,the receipts are created after we get the file from the bank. However in the case of regular receipt processing, first we create the receipt, remit and clear and then the cash is deposited in the bank. In the lockbox, the cash is already deposited in the bank and the bank sends the file to us and then we create the receipts in our AR system. The following are the steps involved in how the Autolockbox applies receipts : Receivables applies the receipts in a lockbox to the transactions during the PostQuickCash process. */ -- If you create a lockbox,then it would show up in this table select lockbox_id,lockbox_number, status,bank_origination_number, batch_size,telephone,receipt_method_id, org_id from ar_lockboxes_all order by lockbox_id /* Actually the hierarchy is that for each lockbox, we can multiple transmissions. And for each transmission, the bank can send in multiple batches. Actually we may not have any values for batch name and amount as it is not mandatory Lockbox => Transmission => Batch */ INSERT INTO ar_transmissions_all (transmission_request_id, transmission_id,transmission_name, trans_date, count, amount,status, requested_lockbox_id, requested_gl_date, org_id, requested_trans_format_id,created_by,creation_date,last_updated_by, last_update_date) VALUES (922,822,'MyTransmission22',sysdate,1,500, 'OP',1200,SYSDATE,44,1080, 1626,sysdate,1626,sysdate) select transmission_request_id,transmission_id, transmission_name,trans_date, time,count, amount,status, requested_lockbox_id, requested_gl_date, org_id , requested_trans_format_id,creation_date from ar_transmissions_all where requested_lockbox_id= 1200 and transmission_name ='MyTransmission22' --- order by trans_date desc -- where requested_lockbox_id in(1200,1020) and transmission_name in -- ('NTDP081202','MyTransmission3') --- order by trans_date desc AND count >0 ------ select * from ar_transmission_formats where transmission_format_id =1020 -- ar_trans_record_formats ar_payments_interface_v /* Just a word on the record types in Lockbox processing: Each lockbox will have specific file format which will be sent by the bank. Oracle provides some standard predefined transmission formats like DEFAULT (Which is of the type BAI - Bank Administration International format). This is made up of a set of record types. These record types are all predefined and when we create a transmission format we define the sequence of these record types according to what we want. So we can define any kind of transmission format that we want, that suits our business needs. Ex of the record types are Transmission Header, Transmission Trailer, Lockbox header, lockbox trailer, Overflow payment, Payment(or receipt), Service Header. Just as we have a set of predefined record types, we also have a set of predefined field types. What we do is we pick a record type ,say ,Payment and click on the tranmission fields and here we choose what fields and the sequence of those fields. Exs of field types are transit routing number,account,remittance amount,deposit date etc */ insert into ar_payments_interface_all (transmission_request_id, transmission_id,transmission_amount,record_type,org_id, customer_number,customer_id, --batch_name, batch_amount, lockbox_number,lockbox_batch_count,lockbox_amount,lockbox_record_count, receipt_date,receipt_method_id,check_number,item_number, remittance_amount,remittance_bank_name,remittance_bank_branch_name, --invoice1,amount_applied1, gl_date, creation_date, last_update_date, deposit_date, transmission_record_id,currency_code, transmission_record_count) values (999,888, 1000,1,44, 296577, 309319, 1200,1,500,1, '24-MAR-2006',1793,'CHK13',1, 500,'BOA','Mountain View', --'1190011566',500, sysdate, sysdate, sysdate , sysdate, 1,'USD',1) select batch_name, batch_amount, transmission_id,transmission_amount,record_type,org_id, customer_number,customer_id, lockbox_number,lockbox_batch_count,lockbox_amount,lockbox_record_count, receipt_date,receipt_method_id,check_number receipt_number, remittance_amount,remittance_bank_name,remittance_bank_branch_name,remittance_amount, invoice1,invoice2 , status, gl_date, creation_date, last_update_date, deposit_date ,transmission_record_id,record_type, currency_code, receipt_method_id,item_number,transmission_record_count from ar_payments_interface_all --where lockbox_number is not null where transmission_request_id = 902 COMMIT; /* During the Validation phase the lockbox processing will check for different things like , -- Ensure that the receipt number is there. (i.e the check number) -- Item number should be there , which should be unique, in a batch, transmission or lockbox. -- Receipt has invalid applications Once all the validation is complete , the rows are inserted into the ar_interim_cash tables. */ -- Now let us insert a row in ar_payments_interface_all with no customer number information or the combination -- of the (routing#,account#) and with the AutoAssociate being set to true. insert into ar_payments_interface_all (transmission_request_id, transmission_id,transmission_amount,record_type,org_id, --customer_number,customer_id, --transit_routing_number, account, --batch_name, batch_amount, lockbox_number,lockbox_batch_count,lockbox_amount,lockbox_record_count, receipt_date,receipt_method_id,check_number,item_number, remittance_amount,remittance_bank_name,remittance_bank_branch_name, invoice1,--amount_applied1, gl_date, creation_date, last_update_date, deposit_date, transmission_record_id,currency_code, transmission_record_count) values (922,822, 1000,1,44, --296577, 309319, 1200,1,400,1, '24-MAR-2006',1793,'CHK922',1, 400,'BOA','Mountain View', '1190011566',--400, sysdate, sysdate, sysdate , sysdate, 1,'USD',1) /* Now run the second step of Validation. Now since the customer information is missing and since the AutoAssociate is set to true, then it should go by the lockbox setting, "Match Receipt by" and if it is transaction number, then it associates this receipt to that particular transaction. The following 4 points summarize the complete functionality of how the lockboxes identifies and applied to receipts. If the customer# or MICR(routing#,account#) is provided, then the customer is identified. If the customer# or MICR is not provided, AND Autoassociate is set to YES (and say the invoice# is provided) then the lockbox will try to apply the matching rules. and apply the receipt amount to that particular invoice. So in this case, the customer is identified and the status of the receipt is APP. (If the invoice amount is already 0, and if the overapplication profile option is No, then the status of the receipt will be UNAPP), otherwise the receipt will be applied and the status will be APP. If the customer# or MICR is not provided, AND Autoassociate is set to YES (but invoice# is not provided) So in this case, the customer is NOT identified and the status of the receipt is UNAPP. If the customer# or MICR is not provided, AND Autoassociate is set to NO (so no matching rules applied etc) So in this case, the customer is not identified and the status of the receipt is UNAPP. -- If the profile option AR:OverApplication of Invoices is set to 'Yes', then the invoice balance can go into negative after application.If it is set to No,and if the invoice balance is already 0, then the receipt amount will be in UNAPP status. */ /* Here one important point is that even if the receipt is unidentified, the column "status" will show a value of UNAPP ,but the "special_type" column will have a value of UNIDENTIFIED.*/ select cash_receipt_id,amount,pay_from_customer,type,status, special_type, receipt_number,gl_date from ar_interim_cash_receipts_all /* There are 2 interim cash tables in lockbox. Once a receipt is validated it figures in the table ar_interim_cash_receipts_all and if there are any applications, then they go into the ar_interim_cash_rcpt_lines_all table. So if a particular receipt is applied against 2 invoices, then the lines table will have 2 lines,corresponding to header cash_receipt_id ar_interim_cash_receipts_all.*/ select * from ar_interim_cash_rcpt_lines_all /* Now after we run the post quickcash program, these receipts are transferred from the interim cash tables to the cash_receipt table ar_cash_receipts_all and ar_receivable_applications_all tables. Interestingly, the same cash_receipt_id in interim tables is preserved in the ar_cash_receipts_all table.*/ select * from ar_cash_receipts_all where receipt_date >= '24-MAR-2006' ORDER BY creation_date desc /* Generally sometimes in some of the columns in tables, some of the values might be strings like OOB, or constants like 1,2 and we dont know exactly what they mean. In such case, we can try to get the meaning of those from the lookup tables. For ex, */ select * from ar_lookups where meaning = '8' --lookup_code like '%TYPE%' --code = '8' /* Overflow Indicator indicates whether there are any further records for this particular receipt. Let us say a particular receipt is there,apart from the usual header and trailer,you might have the payment record type which will consist of fields like (lockbox#,routing%,customer bankacct#,amt,date,check# etc). Now the overflow record will consist of invoice information etc,i.e info like (receipt#, invoice#, amount applied,overflow indicator etc) Typically the overflow indicator value of 0 indicates that there are further overflow records and a value of -9 indicates that it is the last record. */ SELECT arm.NAME, arm.receipt_method_id, arc.creation_method_code, arm.NAME, arm.receipt_method_id, arc.creation_method_code FROM ar_receipt_methods arm, ra_cust_receipt_methods rcrm, ar_receipt_method_accounts arma, ap_bank_accounts aba, ar_receipt_classes arc WHERE arm.receipt_method_id = rcrm.receipt_method_id AND arm.receipt_method_id = arma.receipt_method_id AND arm.receipt_class_id = arc.receipt_class_id AND arma.bank_account_id = aba.bank_account_id AND aba.set_of_books_id = 1 AND arm.receipt_method_id = 1002 /*Before we talk about the Automatic receipt creation process let us talk about the Manual Receipts. Manual receipts are those which do not require any remittance. Let us explain this. Typically when a receipt is automatically generated i.e the Automatic Receipt Generation Program has generated that receipt. That kind of receipts will require the remittance, i.e the receipt has originated from the AR side. These receipts are called automatic receipts. Any other receipts are called Manual receipts; i.e the after the remittance has happened, the receipts are created either thru the lockbox or entered manually thru the form. See ,receipts for ex, checks, are never sent directly to the AR dept and they never enter manual checks in the form. Receipts typically checks are sent to a location called lockbox and from there they go to a bank and the bank prepares and sends the remittance advise to the customer banks,collects the money and then sends the lockbox file,containing the payment information to the company AR dept. This lockbox file is then loaded into our systems. All such receipts created are of payment type Manual.*/ -- Step by Step Lockbox Testing Process : ------------------------------------------ select transmission_request_id,transmission_id, transmission_name, trans_date,time, count, amount, status, requested_lockbox_id, requested_gl_date, org_id , requested_trans_format_id,creation_date from ar_transmissions_all where transmission_name = 'NTDP09142006' order by creation_date desc -- Get the transmission id from the above query. select * --count(*) -- 340 from ar_payments_interface_all WHERE transmission_id = 49302 /* The number of lines that are in the flat file is the number of records that we see in this table. The verisign flat file : The file itself is consisting of a different number of batches, with each batch consisting of fields like that batch amount, control count etc. That is for each set of records,called a batch, there will also be a record which gives the sum of that batch receipts. This record is preceded by a record identifier 7. Similarly for the entire transmission, there will be another record which will give the sum of all the receipts corresponding to the entire transmission.Similarly this record is preceded by a record identifier 7. */ select Batch_name, Batch_amount ,lockbox_number,lockbox_batch_count,lockbox_amount,lockbox_record_count ,Transmission_id,Transmission_amount,Record_type,Org_id ,customer_number,customer_id ,receipt_date,receipt_method_id,check_number receipt_number ,remittance_amount,remittance_bank_name,remittance_bank_branch_name,remittance_amount ,invoice1,invoice2 ,status ,gl_date, creation_date, last_update_date, deposit_date ,transmission_record_id,record_type, currency_code ,receipt_method_id,item_number,transmission_record_count from ar_payments_interface_all --where lockbox_number is not null where transmission_id = 49302 /* Some times you might get an error" invalid applications" which means the invoice information/number that appears in the overflow record in not there in the AR system and hence the lockbox process does not know to whom it should be applied. Also if the period is not open, you might get an error. */ /* The best way I believe is to open up the lockbox file,which is usually a text file and open up the transmission formats from and see what are the payment and overflow record identifiers. The payment record contains the receipt information while the overflow record contains the invoice information. Once we do that we need to see what is the starting and ending positions for these fields,pick up the invoice# and receipt# and then pull up those in the Oracle applications.*/ select sum(remittance_amount),sum(batch_amount), batch_name from ar_payments_interface_all where transmission_id = 49302 group by batch_name select remittance_amount, batch_amount ,batch_name from ar_payments_interface_all where transmission_id = 49302 and batch_name = 7 /* Even right after the first step is completed, the transmission table can show an error of OOB (out of balance) what this means is that the sum amounts are not adding up to the individual amounts. For ex,Batch amount column may not be adding up to the sum of the remittance amounts for a particular batch. Lockbox amount may not be adding up to the sum of all the receipt amounts. Transmission record count may not equal the total number of records,i.e let us say if the flat file has in total 340 lines, the transmission trailer line should show a value of 340. The above point can be simply verified by running the below query. */ select sum(remittance_amount) sum_rmt_amount, sum(batch_amount) sum_batch_amount, batch_name batch_name from ar_payments_interface_all where transmission_id = 49302 group by batch_name /* IMPORTANT: Receipt number and payment number is always part of the lockbox file. If the receipt number is already existing in the AR, then it fails. And receipt number should never be system generated (i.e it should not be generated by a document sequence etc)*/ ---- update ar_payments_interface_all set gl_date = gl_date + 30 where transmission_id = 49302 ---- Once the validation part completes,the records should be found here. select * from ar_interim_cash_receipts_all -- what kind of records are found here. select * from ar_interim_cash_rcpt_lines_all /* Now during the 3rd step, the post quick cash completes and records should go to AR and the applications should happen, in ar_receivable_applications_all */ select * from ar_cash_receipts_all where creation_date >= trunc(sysdate) -- 140 select * from ar_cash_receipt_history_all where creation_date >= trunc(sysdate) --140 -- We can find out which receipts are created by going to the Receipts => Batch Summary /*and there query by the Transmission Name which is coming from all along.Here we see that the receipt batches are created consisting of the unidentified and unapplied receipts.Each record corresponds to a transmission batch coming from the file. We can try to correlate the data here with the flat file and ,open the receipts and try to correct the data,like enterting the customer name etc. */ /* AUTOMATIC RECEIPT CREATION PROCESS -------------------------------------- The criteria for creating the Automatic receipts Firstly the paying customer information(like the bank account information) on the transaction form should be available. The transaction should be complete and for the associated customer, the currency information should be available. The payment term should be immediate (or only on the due date the auto receipt is created). Only after the Creation, Approval And Formatting the receipt appears in the ar_cash_receipts_all). The automatic receipt creation program will first create the receipt batch and then creates the receipt as part of that. The receipt history table will have the batch id. How is the PSON (payment server order number) populated in ar_cash_receipts_all What is the difference between the auto and manual creation of remittances. */ select rowid,a.* from ra_customer_trx_all a where trx_number = '11048' /* 1).Hence to create an automatic receipt, first go to the batches screen using the menu option Receipts => Batches Pick the automatic option And Click on the Create button. 2).Now here pick or enter the transaction number for which you want the automatic receipt to be created. This will kick off the "Automatic Receipt Creation Process" program. 3).A receipt has to go thru the Creation, Approval and Formatted option. Hence choose those options if required. and only after they are Approved and Formatted, they appear in the Cash Receipts table. Most important,the following query should yeild the record for the Automatic Receipt creation to create a record */ SELECT -- cust_cpa.*, cust_cpa.currency_code , site_cpa.currency_code site_cpa_cur,ps.payment_schedule_id, ps.cash_receipt_id, ct.paying_customer_id, ct.paying_site_use_id, ct.payment_server_order_num, ps.due_date, ps.amount_due_remaining, ct.customer_bank_account_id FROM hz_customer_profiles cust_cp, hz_customer_profiles site_cp, hz_cust_profile_amts cust_cpa, hz_cust_profile_amts site_cpa, ra_customer_trx ct, ar_payment_schedules ps WHERE ps.status = 'OP' AND PS.gl_date_closed = TO_DATE('4712/12/31', 'YYYY/MM/DD') AND ps.selected_for_receipt_batch_id IS NULL -- AND ps.due_date+0 <= TO_DATE('28-AUG-2005') + TO_NUMBER(0) AND ps.invoice_currency_code = 'USD' AND ps.customer_trx_id = ct.customer_trx_id AND ps.reserved_type IS NULL AND ps.reserved_value IS NULL --AND ct.receipt_method_id = 1035 AND ct.paying_customer_id = cust_cp.cust_account_id AND cust_cp.site_use_id IS NULL AND cust_cp.cust_account_profile_id = cust_cpa.cust_account_profile_id(+) AND cust_cpa.currency_code(+) = 'USD' AND ct.paying_site_use_id = site_cp.site_use_id(+) AND site_cp.cust_account_profile_id = site_cpa.cust_account_profile_id(+) AND site_cpa.currency_code(+) = 'USD' AND (NVL(ps.amount_in_dispute,0) = 0 OR (NVL(ps.amount_in_dispute,0) != 0 AND NVL(site_cp.auto_rec_incl_disputed_flag,cust_cp.auto_rec_incl_disputed_flag) = 'Y')) AND ps.trx_number = '444' FOR UPDATE OF ps.selected_for_receipt_batch_id; /* Most importantly,Once the automatic receipt process will pick a transaction for the receipt creation, then in the payment schedules table for that particular transaction, the select_for_receipt_batch_id will be populated with the batch id of the receipt batch. Also that transaction is closed (with the amount due remaining being made 0) after applying this receipt to that particular transaction. This can be checked from the below queries. */ select * from ar_payment_schedules_all where customer_trx_id = 2800398 select * from ar_receivable_applications_all where applied_customer_trx_id = 2800398 select * from ar_batches_all where 1=1 and batch_date >= trunc(sysdate) --and batch_id = '7810' select * from ar_cash_receipts_all where cash_receipt_id = 2195031 select * from ar_cash_receipt_history_all where batch_id = 7814 select * from ar_cash_receipt_history_all where cash_receipt_id = 2195031 select * from iby_trxn_summaries_all where tangibleid = 'AR_177807' /*Now the receipt is created, it needs to be remitted. Remittance is the process of going thru the payment processor and depositing the customer money into our bank. Interestingly there is a record in the iby table even before the receipt is remitted. And the selected_remittance_batch_id is populated in the ar_cash_receipts_all for that particular cash receipt. (ar_boe_auto_receipts_v). Just like the receipt, the remittance process also goes thru the Creation, Approval and Formatting options. Hence for that go to the Receipts => Remittances -- Enter the payment information and click on the AutoCreate. This will kick off the "Automatic Remittances Creation" program. The below query should be successful for the automatic remittance process to succeed.*/ SELECT selected_remittance_batch_id,a.* FROM ar_cash_receipts_all a where receipt_number = '11048' SELECT /*+ LEADING (crh) INDEX (crh AR_CASH_RECEIPT_HISTORY_N6) */ cr.cash_receipt_id, cr.amount FROM ar_cash_receipt_history crh, ar_cash_receipts cr, ar_payment_schedules ps, ar_receipt_classes rclass, ar_receipt_methods rm, ar_receipt_method_accounts rma1, ar_receipt_method_accounts rma2 WHERE crh.status = 'CONFIRMED' AND crh.current_record_flag = 'Y' AND crh.cash_receipt_id = cr.cash_receipt_id AND NOT EXISTS (SELECT 1 FROM ar_lookups l WHERE NVL(cr.reversal_category,'~') = l.lookup_code AND l.lookup_type = 'REVERSAL_CATEGORY_TYPE') AND cr.currency_code = 'USD' AND cr.cash_receipt_id = ps.cash_receipt_id(+) AND cr.receipt_method_id = rm.receipt_method_id AND cr.selected_remittance_batch_id is null AND (( cr.amount >= 0) OR (cr.type = 'MISC' and cr.amount < 0)) AND rm.receipt_class_id = rclass.receipt_class_id AND rma1.receipt_method_id = cr.receipt_method_id AND rma1.bank_account_id = cr.remittance_bank_account_id AND rma2.receipt_method_id = rma1.receipt_method_id -- AND rma2.bank_account_id = :bs_remit_account_id AND cr.receipt_number = '-2500' FOR UPDATE OF cr.selected_remittance_batch_id /*Once the remittance process completes, a 'ORAPMTCAPTURE' record will be created in the ipayment table i.e iby_trxn_summaries_all table, and the tangibleid from that table is back populated into the PSON of the cash receipts table. */ /* The typical next step in the standard oracle receivables workflow is to clear the receipts,which is done as Receipts => Clear/Risk Eliminate and after entering the right parameters, this will kick off the "Automatic Clearing for Receipts" program. */ select * from iby_trxn_summaries_all where tangibleid = 'AR_177807' select * from fnd_concurrent_requests where request_id = 1718284 /*Trouble shooting the Automatic Receipt Creation Process : What to check when a transaction is not selected during automatic receipt creation? and the following notes on metalink can help.293031.1 & 227025.1 */ CUSTOMER PAYMENT TYPES. Customers can pay by Bank Account => Cash, Check, ACH payment methods credit card => Credit Card payment method the receipts can be coming by Lockbox process.(No remittance) /* RECEIPT CREATION FROM CUSTOMER BANK ACCOUNT DETAILS. Before you create anything, ensure that the set of books and operating unit is specified correctly and to US. Define a Bank, Branch and Account (of Type Customer). This is a customer bank account. We can optionally assign this bank account to the customer at the customer bill-to site level. Define a receipt class which is Creation Method : Automatic Remittance Method : Standard Clearance Method : By Automatic Clearing Since this is for Bank account, give the payment type as "ACH Bank Account". Then go to the Bank Accounts form and provide the remittance bank information. This is the remittance bank account(i.e internal). Ensure you have Immediate payment terms in the system. For Immediate payment terms, the due days is 0. If you already have one, no need to create a new one. Now Create a transaction for the above customer,provide Immediate payment term, give bill-to details,USD currency and paying customer information is present. The most important fields are Payment method, Customer Bank,Branch, Account Number,Expiration date. Now provide the receipt class/method that you created in step 2. (It is important to understand that depending on the payment method you have chosen, only the corresponding customer payment type can be provided in the customer payment details feilds. For ex, if you chose credit card payment method, then the customer credit card acct information only can be provided. if you chose ACH payment method, then the customer bank account only can be provided) Come to the bank field and provide the bank that is created in step 4. Enter the account number. You dont need to enter any expiration date as this is not a credit card payment method. Enter the line detail with correct accounting distribution information. Complete the transaction. Now this transaction is ready for the Automatic Receipt Creation program. -- REFUNDING A BANK ACCOUNT RECEIPT. It is important to understand that you can only refund a receipt after it has been remitted (otherwise you can simply cancel or delete the receipt,since it has not been remitted). Refunding a receipt(generated from ACH), starts from the Credit memo. Pull up the invoice in the Transaction work bench. Issue a credit memo for this transaction. Assume that the invoice has been completely paid by the receipt and the invoice balance is 0. Since the invoice balance is 0, apply the credit memo to an Electronic Refund receivable activity,which immediately create a (-ve) miscellaneous receipt and the number is populated in the reference number etc,which can be pulled up in Receipt workbench. Now this refund (or negative miscellaneous receipt) is ready for remittance process etc. -- CREDIT CARD PROCESSING IN AR. Credit card payments : As far as credit card payments are concerned, there are different ways credit card payments are made, First, the invoice is already generated and the customer is making payment thru the credit card, which is thru automatic receipt creation,where the customer credit card payment information is available. Second, the transactions are coming directly as credit card transactions,where there is no invoice at all. -- RECEIPT CREATION FROM CREDIT CARD DETAILS. Before you create anything, ensure that the set of books and operating unit is specified correctly and to US. Define a bank by name "Credit Card" with the branch name as "Credit Card". Go to the Bank accounts screen and define an account by providing a credit card number etc. So for each credit card customer, we will have a credit card account. Define a receipt class which is (and also payment method) Creation Method : Automatic Remittance Method : Standard Clearance Method : By Automatic Clearing Since this is for Credit Card payment method, give the payment type as "Credit Card".Then go to the Bank Accounts form and provide the remittance bank information. This is the remittance bank account(i.e internal). Ensure you have Immediate payment terms in the system. For Immediate payment terms, the due days is 0. If you already have one, no need to create a new one. Now Create a transaction for the above customer,provide Immediate payment term,give bill-to details,USD currency and paying customer information is present. The most important fields are Payment method, Customer Bank,Branch, Account Number,Expiration date. Now provide the receipt class/method that you created in step 2. Now since you have given the payment type as Credit Card, immediately in the Bank,branch you will see the "Credit Card" and "Credit card" respectively. Come to the account number field and provide a credit card number created in step 4 or enter a new cc number. Also enter the expiration date of the credit card. (It is important to understand that depending on the payment method that you provide in that field, the corresponding customer payment type can be provided in the other field,i.e if you provide credit card payment method, then the customer credit card acct can be provide. if you provide ACH payment method, then the customer bank account only can be provided) Enter the line detail with correct accounting distribution information. Complete the transaction. Now this transaction is ready for the Automatic Receipt Creation program. So when the Automatic Receipt Creation program runs , this will create a receipt , which can be opened up in the Receipt workbench as well. -- Now another way of creating a credit card receipt is to go manually to the receipt work bench and pick the same payment method that was created above and provide all the customer account details. --- -- REFUNDING A CREDIT CARD RECEIPT. It is important to understand that you can only refund a receipt after it has been remitted (otherwise you can simply cancel or delete the receipt,since it has not been remitted). Refunding a receipt(generated from Credit Card), starts from the receipt itself. Just pull up the receipt in the receipt work bench and apply the receipt to a Credit Card Refund receivable activity,which immediately create a (-ve) miscellaneous receipt and the number is populated in the reference number etc, which can be pulled up in Receipt workbench. Now this refund (or negative miscellaneous receipt) is ready for remittance process etc. /* MISCELLANEOUS RECEIPT : Let us briefly talk about the miscellaneous receipts and the associated details in it. We can create a Miscellaneous Receipt from the form, but the activities that we can create against are limited to the one corresponding to the "Miscellaneous Cash"; that is we will see only activities that are created in the menu item, setup => receipts => Receivable Activities (Corr to Miscellaneous Cash Only). What this means is that if we create a receivable activity corresponding to, say, "Prepayment" using setup => receipts => Receivable Activities and if we want to enter a receipt against that activity using the form,it is not possible. This can be done only from the backend using the api's, but such kind of the receipts created from backend can be viewed from the receipts form. A Miscellaneous receipt can have a positive sign(+) or negative sign for the amount. Usually the miscellaneous receipts correspond to investment income for a company and hence they have a positive sign for the amount. */ /* REFUNDS (& CREDIT CARD REFUNDS) IN AR : A REFUND IS A NEGATIVE MISCELLANEOUS RECEIPT. When a receipt is applied to a receivable activity like credit card refund, then a negative miscellaneous receipt is automatically created and this negative miscellaneous receipt is called Refund. We can see this in the applications window itself in the fields "application reference type" and "application reference number" which will be the "Miscellaneous Receipt" text and the receipt number respectively. We can try to pull up this receipt separately from the receipts workbench as well. In AR, usually the customer balances are positive, that is customer needs to pay us. However due to a credit memo application or over receipt applications, the invoice balances can be driven negative as well. In that case, that amount needs to be returned to the customer. One way of doing is to identify all such invoices with negative balances and handle the refunds within the AR department(rather than AP). That is the AR department will take a print out of all the invoices and print checks and mail them to customers. Now from an accounting perspective, a neagtive miscellaneous receipt is created to offset the cash account. So the entries look like this. Cash $100 (cr) Negative Miscellaneous Receipt $100 (dr) (associated with a Receivable Activity) One other way of doing it is to let the AP (accounts payable) to handle it. In this case, for each customer, who needs to be refunded, a supplier account is dynamically created and then AP will handle the check printing and sending it. Remember that AP can only send payments if there is a supplier account available. But this can get cumbersome, if the number of refunds are more. Its a business-to -business decision. The first one is most commonly used approach. */ /*In the above example, we have manually applied the receipt to "Credit Card Refund" and then the refund is created behind the scenes. However usually the refunds are created automatically by the Automatic Receipt Creation program. When Automatic Receipt Creation program runs ,it converts the invoices into receipts and the credit memos(which are tied to invoices) are converted into refunds (i.e negative miscellaneous receipts) are created. However if there are on-account credits, (i.e credit memos which are not tied to invoices), then the Oracle Automatic Receipt Creation program does not create the refunds, because the sale receipt is not present in the system. Hence the key point, is that Oracle only performs refunds, when the sale receipt is present in the system. For on-account credits, we dont have the original sale receipt. */ /******************************************* CHARGEBACKS AND RECEIPT REVERSALS EXPLAINED ******************************************** Chargeback Scenario. -------------------- First create a receipt say for $45. Apply this receipt to an invoice of $26. Only after the invoice is applied, the chargeback button is enabled. The chargebacks can only be created from the receipt applications window and cannot be created directly from the transactions window.(even though you can query the chargeback from the transactions window). If the invoice amount is greater than the receipt amount, then the difference amount is defaulted in the amount field of the chargeback screen. If the receipt amount is greater,then the amount will not default. Reversing the Receipts ---------------------- 1) Let us consider a case where the receipt is having an application (with out any chargebacks or adjustments) ie. it is applied to an invoice. If you reverse such a receipt, then AR will try to unapply all the applications and opens up all the associated transactions.(Simplest case). (What happens to the receipt status?? The reverse journal entries will take care of the receipt amount and where these journal entries are stored???) When reversing a receipt all the reverse journal entries that are created will be in the gl_dist_all table. 2) Let us consider a case where the receipt is having an application related to a chargeback i.e. it is applied to an invoice and also a chargeback is created.(Note : The invoice is closed here and where is this balance amount for the invoice is coming from ?????). So what is happening in this case is that if you reverse this receipt, it will open up all the associated transactions,and reverses the associated chargebacks and adjustments.*/ select * from ar_cash_receipts_all where receipt_number ='myrcpt2' select * from ar_receivable_applications_all where cash_receipt_id = 29925249 select * from ar_cash_receipt_history_all where cash_receipt_id = 29925249 /*3). Let us consider a case where the receipt is having an application related to a chargebacks i.e. it is applied to an invoice and also a chargeback is created.(Note : The invoice is closed here). And there is an activity against this chargeback ie. say, a credit memo is applied against this chargeback. Then if you try to reverse the receipt,the system will not allow you to do a standard reversal of the receipt. (And so is the case, if we have a chargeback and this chargeback has been already posted to the GL. In that case too the system will not allow to do a standard reversal of the receipt). In this case, you will have to create a debit memo reversal. A debit memo reversal means that instead of creating reverse journal entries and then opening up all the associated transactions,it will create a debit memo for an amount which is the sum of the transaction balances.Hence you can still see the reversed receipts applications to the transactions. From the following query. we can trace the reversed receipt record. select max(date_created) from gl_interface --REPORTS : /*"Invoice Print Selected Invoices" Report : /******************************************* This will print the invoices for the customer. Usually if you print an invoice, the invoice balance is always the same, no matter when you print it. When you print Installment invoices this is how it works. if you have two installments it will print 2 pages, 1 for each installment in a separate page each specifying the corresponding due date.If you look at the printed invoice it will be very clear to you. Another thing you might notice here is that once you specify a split payment term on an invoice, the due date that shows on the invoice is first installment due date. */ /* Supplier Customer Netting Report : ************************************ This report is used when you are having a party who is both a customer as well as supplier. That is, you purchase goods from them and as well as you sell goods to them. So this report will basically tell what is the net balance i.e Receivables minus Payables. When you run this report, you can use the join criteria i.e whether you want to system to join by Name Tax ID NIF Code? Based on that it print the payables and receivables records in the report and then finally the net balance. -- Oracle "AR Reconciliation Report" and Oracle "Aging 7 Bucket report" (or 4 Bucket report) /************************************************************************************************* Ideally the "AR Reconciliation Report" and Oracle "Aging 7 Bucket report" should have the same open balance. The "AR Reconciliation Report" typically gives the opening balance for an "as of date" and computes the key metrics like the Transaction Register, Applied Receipts Register ,Unapplied Receipts Register etc and comes up with the total's for the period. And finally it also computes the closing balance for an "as of date". Now the Closing balance = Opening Balance + algebraic sum of (registers etc) The major difference between the AR Reconciliation Report and Aging Report is that, in the Aging Report, if there is a transaction which was created in that particular period and also closed in the same period, then it would not show up there. However the way the recon report works is that it picks up all the transactions in the transaction register and then in the Applied Register ,unapplied Register etc. /* The Aging Report will give the outstanding balance as of a particular date. It should always be same no matter when you run the report as long as you give the same as-of-date. As an ex, let us say there is an invoice for $100 in march which got closed on apr 10th(say by a receipt). So if you run the Aging Report with as of date as 31st Mar,it should give the same output no matter whether you run the report on Apr 1st or Apr 15th, because you are asking the balance of the invoice as of 31st March which is always $100. Now from a technical perspective, Oracle is able to provide this information because there is a column called gl_date_closed in the transaction table. I found that the unapplied receipt register will change its output based on when you run. */ select * from ar_cash_receipts_all where receipt_number like 't7' -- 29925249 select * from ar_receivable_applications_all where cash_receipt_id = 29925248 select /* index(a ra_cust_trx_line_gl_dist_n2) */ * --count(*) -- customer_trx_id,customer_trx_line_id,cust_trx_line_gl_dist_id from ra_cust_trx_line_gl_dist_all a where gl_date between to_date('05-SEP-2005','DD-MON-YYYY') and to_date('05-SEP-2005','DD-MON-YYYY') + 0.99999 --and creation_date >= trunc(to_date('05-SEP-2005','DD-MON-YYYY')) and cust_trx_line_gl_dist_id > 364834000 select max(cust_trx_line_gl_dist_id) from ra_cust_trx_line_gl_dist_all --364834116 select * from ar_distributions_all where line_id > 210341000 The difference between ra_cust_trx_line_gl_dist_all and ar_distributions_all is that in the "ar_distributions_all" table, the data is stored in the form of dr/cr format. try this out and see what are the differences.ar_distributions_all table will store the dist for all the types of items, trxns, reeipt adjustments -- Applied Receipts Register : /***************************** The applied receipts register will only print all the receipts that are applied to the invoices. Sources of Discrepancies : * This report prints the receipts for each receipt currency. That is it prints all the receipts and then it prints the receipts for each such receipt currency. Now even in the receipt currency USD receipts, you will see the records corresponding to the transaction currency,say, 'EUR' or 'GBP'. What this means is that the transaction currency is 'EUR' and the receipt currency is in 'USD'. Now for these kind of receipts, we might see the allocated receipt amount is different from the functional amount. This can happen when the loss/gain of dollar happens from the time the receipt was created to the time the receipt was applied. Hence it is always important to take the functional amount. * Check what are all kinds of currency transactions that the applied receipt register is printing and take that into consideration. * What we found is that when we run the Applied Receipts Register with the attribute set as 'CUSTOMER' it is summing up the functional amount correctly as opposed to running it with attribute set as 'DEFAULT' * VERY VERY IMPORTANT POINT FOR RECONCILIATION : When we run the standard Oracle reports, even though the reports might look jumbled, we can do the following (all at once, or in portions) and get the summations that we want. Copy all the transaction of the report into a spreadsheet and do these two simple steps. a). Data => Text to Columns b). Click on any amount column of interest, and do a Data => Sort. This would sort the data and put all the unwanted text either at the end/beginning,which can be deleted.Then we can easily sum or do any operation that we want. */ Applied Receipts Register,say,for June 2006, will give all the receipts created before June and got applied in June, (Case 1) all the receipts created and got applied in June 2006 (Case 2), all the receipts created in June and got applied in July 2006 and later,if so (Case 3). /*As of 11.5.10.2 the Applied Receipts Register is doing all the processing and dumping the information into the temp table and reading from it. So in order to see from the backend as to what is happening in each register do the following. Let us say we run the report "Applied Receipt register", then the table is populated with the data corresponding to that and it also populates the concurrent request id. We can use that id and go to the following table and get the data. */ select * from ar_receipts_rep_itf where request_id = 3851546 /* Similarly for the "Miscellaneous Receipts" register and "Other Receipt Applications report". Just use the corresponding concurrent request id's and get the results from that table. However for the unapplied receipts register we have to use the query below. */ -- Verisign process of Reconciliation : Each company canuse its own standard process of reconciliation. That is -- a check point whether everything is ok at the monthend. In Verisign, one such check point is Receipt Register = Applied Reg + Unapplied Reg + Miscellaneous Reg + Other Receipts Application Reg -- Unposted Items Report /*********************** The unposted items report is an important report for any finance person, because it gives a list of all the items which are not posted i.e transactions, receipts, adjustments etc which are not transferred to GL. The unposted items,shows which are the items which are still pending in the AR side. Once they are moved to GL, then we can close the period. For ex, in the case of ,say, transactions, they are the set of completed invoices, for which the revenue has been recognized,but they have not yet been pushed over to GL. Dont get confused with the gl posted, posting means here transferring them to GL. They all have a value of -3 for the posting_control_id. The following query would typically print the unposted items (transactions) in the system for AR. Similarly we have different queriers for printing different unposted items, like unposted receipts, adjustments etc(look for metalink note). */ SELECT gl.customer_trx_id trx_id, gl.customer_trx_line_id line_id, cust_trx_line_gl_dist_id dist_id, substr(account_class,1,3) acc, gl.amount, percent, gl.gl_date, gl.gl_posted_date, gl.acctd_amount, ct.invoice_currency_code currency FROM ra_customer_trx_all ct, ra_cust_trx_line_gl_dist_all gl WHERE gl.customer_trx_id = ct.customer_trx_id AND ct.complete_flag = 'Y' AND gl.account_set_flag = 'N' AND gl.gl_date BETWEEN to_date('15-MAR-2006', 'dd-mon-yyyy') AND to_date('16-MAR-2006', 'dd-mon-yyyy') AND gl.posting_control_id = -3 ORDER BY trx_id, line_id /*Another issue which can cause this is because of a known oracle bug which is generating incorrect distributions,when the amount on the credit memo line is positive.(for which there is a tar 5477432.993).This can be eliminated by restricting in the transaction type (by the creation sign). The MOST COMMON error for some items not being posted to GL are "UNBALANCED credit and debit entries". If you find that "Unposted Items" report is empty and you are still getting error, use Oracle Diagnostic tools and Select Receivables > Closing Period option. This will pin point you precisely which transactions or adjustments in corresponding tables is not posted and is causing the problem. */ Incomplete Invoices Report : /****************************** This is another simple report in which we have invoices which have not been completed at all. Now this is one report which functional people might run,before they close the period. The thing is even there are any incomplete invoices, you can still close the period, unlike in the case of "Unposted Items Report". In "Unposted Items Report" if there are any pending items, then you cannot close the period.*/ SELECT ct.* FROM ra_customer_trx_all ct where complete_flag='Y' -- Billing and History Report : /****************************** Many times it is convenient to know what are all the receipts that are applied to a specific invoice. One way of doing it is to run the Billing and History which is a very simple report which gives all the transactions on a customer by customer basis. Now for a single transaction we can do the following. Pull up the transaction, Click the Actions => Installments Now click on the Activities button to see the receipts that are applied against this invoice. */ -- Aging - 7 Buckets Report By Collector : /************************************** When I ran the AR aging by account and AR aging by collector, those two reports are not matching with each other on the "receipts and credit memos". This could be because of the unidentified receipts. If we run the unapplied receipts register, it will also print the unidentified receipts. These unidentified do not correspond to any customer and hence they do not correspond to any collector as such, so they may not be showing up in the "AR Aging By Collector". once they are cleared, it will also tally. Usually the collector information is present at the customer profile and this profile is associated to the customer.(You can define a profile at the customer site level). Hence in Summary, Aging by Account : will show the invoice balance and the unapplied, unidentified and creditmemos Aging by Collector : will show the invoice balance and the unidentified and creditmemos (not unapplied). */ -- Unapplied Receipts Register : Very Very Important Running Query : /********************************************************************/ SELECT gc.segment1 balancing_segment, NULL dcolsort, SUBSTRB (party.party_name, 1, 50) customer_name, cust.account_number customer_number, MAX (DECODE (UPPER (:p_in_format_option), 'SUMMARY', NULL, app.gl_date ) ) gl_date, NVL (ar_batch_sources.NAME, :nls_no_batch) batch_source_name, NVL (ar_batches.NAME, :nls_no_batch) batch_name, rm.NAME receipt_method, rcpt.receipt_number receipt_number, --,app.acctd_amount_applied_from --, app.amount_applied, rcpt.receipt_date receipt_date, SUM (DECODE (app.status, 'ACC', DECODE (UPPER ('USD'), NULL, app.acctd_amount_applied_from, app.amount_applied ), 'OTHER ACC', DECODE (app.applied_payment_schedule_id, -7, DECODE (UPPER ('USD'), NULL, app.acctd_amount_applied_from, app.amount_applied ), 0 ), 0 ) ) on_account_amt, SUM (DECODE (app.status, 'UNAPP', DECODE (UPPER ('USD'), NULL, app.acctd_amount_applied_from, app.amount_applied ), 'UNID', DECODE (UPPER ('USD'), NULL, app.acctd_amount_applied_from, app.amount_applied ), 0 ) ) unapplied_amt, SUM (DECODE (app.status, 'OTHER ACC', DECODE (app.applied_payment_schedule_id, -4, DECODE (UPPER ('USD'), NULL, app.acctd_amount_applied_from, app.amount_applied ), 0 ), 0 ) ) claim_amt -- NVL (cust.cust_account_id, 0) customer_id, -- DECODE (cust.cust_account_id, NULL, '*', NULL) unid_flag FROM ar_batch_sources, ar_batches, hz_cust_accounts cust, hz_parties party, ar_receipt_methods rm, gl_code_combinations gc, ar_receivable_applications app, ar_cash_receipt_history crh, ar_cash_receipts rcpt WHERE app.status IN ('ACC', 'UNAPP', 'UNID', 'OTHER ACC') AND NVL (app.confirmed_flag, 'Y') = 'Y' -- AND app.gl_date >= :p_in_gl_date_low -- AND app.gl_date <= :p_in_gl_date_high AND rcpt.cash_receipt_id = app.cash_receipt_id AND NVL (rcpt.confirmed_flag, 'Y') = 'Y' AND crh.cash_receipt_id = rcpt.cash_receipt_id AND crh.first_posted_record_flag = 'Y' AND cust.cust_account_id(+) = rcpt.pay_from_customer AND cust.party_id = party.party_id(+) AND rcpt.receipt_method_id = rm.receipt_method_id AND ar_batches.batch_id(+) = crh.batch_id AND ar_batch_sources.batch_source_id(+) = ar_batches.batch_source_id AND gc.code_combination_id = app.code_combination_id and app.gl_date >='01-JUN-2006' and app.gl_date <='30-JUN-2006' GROUP BY gc.segment1, NULL, party.party_name, cust.account_number, NVL (ar_batch_sources.NAME, :nls_no_batch), NVL (ar_batches.NAME, :nls_no_batch), rm.NAME, rcpt.receipt_number rcpt.receipt_date, NVL (cust.cust_account_id, 0), DECODE (cust.cust_account_id, NULL, '*', NULL) HAVING SUM (DECODE (app.status, 'ACC', app.acctd_amount_applied_from, 0)) != 0 OR SUM (DECODE (app.status, 'UNAPP', app.acctd_amount_applied_from, 'UNID', app.acctd_amount_applied_from, 0 ) ) != 0 OR SUM (DECODE (app.status, 'OTHER ACC', app.acctd_amount_applied_from, 0 ) ) != 0 ORDER BY 1 ASC, 3 ASC, 4 ASC, gc.segment1, party.party_name, cust.account_number, rcpt.receipt_number, MAX (DECODE (UPPER (:p_in_format_option), 'SUMMARY', NULL, app.gl_date ) ), NVL (ar_batch_sources.NAME, :nls_no_batch), NVL (ar_batches.NAME, :nls_no_batch), rm.NAME, rcpt.receipt_date, NVL (cust.cust_account_id, 0), DECODE (cust.cust_account_id, NULL, '*', NULL) -- AR To GL Reconciliation Report : This report can be run from the menu Control => Accounting => AR To GL Reconciliation Report. /* GL Transfer while the system is still up and running : And as per your earlier question if somebody is still doing transactions at that point of time - only those transactions that are completed and receipts that are saved at the time of interface will be interfaced. -- Can people be logged on to the system when run the transfers from AR to GL and AP to GL? YES -- If they are logged on, can they enter transactions? YES -- If they are logged on, can they perform inquiries? YES -- Can the transfer from AP to GL be scheduled?(I believe it can). YES -- Can the transfer from AR to GL be scheduled? YES -- If a big process like the transfer is running can the existing framework handle it with multiple users logged on? YES */ /***************************************************************/ /*Prepayment Process (Also Includes how Intuit handles it). Usually in a B2B busines-to-business environment, firstly a sales order is created and booked. Following that the invoice is generated out of that. And this invoice,along with the goods, is sent to the customer. Once an invoice reaches the customer, the customer will make the payment. Even in the case of the automatic receipt generation, the conventional process is that the first invoice is created,sent to the customer and only on the invoice due date,the automatic receipt is created. However in the case of the prepayments, BY DEFINITION ; THE RECEIPT IS CREATED EVEN BEFORE THE INVOICE IS GENERATED. The following is the process. Initially once a prepayment sales order is created,immediately a prepayment receipt is created. 1) Here one of the flexfields will determine whether the order is a prepayment or not. And if it is,then it will also record the amount etc. (Actually using the standard process it is related to the payment term,that is,if the payment term is classified as prepayment, then it should create a receipt, but how it is happening?) 2) A cash receipt is created immediately, from the backend. 3) And this receipt is applied to a prepayment activity. This receipt amount is applied to a prepayment receivable activity(predefined activity). Subsequently whenever a invoice is created, the previous prepayment application is unapplied and then applied to this invoice. */ /*************************************************** REVENUE RECOGNITION : CREDIT MEMO ACCOUNTING RULES : **************************************************** Interesting problem regarding the revenue distribution with respect to Credit Memos. Let us say we have an invoice for a product raised in Jan 2005 for $1000 and this invoice is associated with an accounting rule of ,say, (12 month equal distribution with 8.13% each month). Then the revenue that is recognized for each month until Dec 2005 is $81.3. Now in the month of May 2005, the product has been returned and an amount of -593.5 has been credited to the customer. However we can recognize this revenue in a couple of ways. Firstly, we can recognize -$81.3 for each successive month going forward until Dec 2005. That is we are going by the amounts of the invoice for each remaining month until Dec 2005. (called LIFO) Secondly, We can take the percentages for each successive month and ie get 8.13% of $593.5 = $48 for each month starting with the last month ie. Dec 2005 until Jul 2005 and in the last month i.e Jun 2005, we will recognize the remaining amount -$306. (called PRORATE) Currently it is doing the second method and what we want it to do is the first method. -- Generally companies want to push this (negative revenue i.e revenue due to the credit memos) towards the end of the accouting period, while the auditors, for precision, would like that negative revenue to be recognized as soon as possible so that it reflects the correct figures on part of the company. */ select * from ra_customer_trx_all --where creation_date >= trunc(sysdate) where customer_trx_id = 29485707 -- 29936462 select cash_receipt_id,customer_trx_id,applied_customer_trx_id from ar_receivable_applications_all -- 29936462 ,29485707 where customer_trx_id = 29936462 select * from ra_cust_trx_types_all where cust_trx_type_id = 1133 /*Revenue recognition is the process where by revenue is distributed in appropriate gl periods.For one off transaction we can use the following api to create the distributions.Before you run the rev rec below api, run the queries to get the user_id, resp_id and resp_appl_id is always 222*/ select * from fnd_user where user_name ='SETUPUSER' select * from fnd_responsibility_tl where responsibility_name like 'Receivables Manager' and language ='US' declare l_create_dist_count number := 0; begin fnd_global.apps_initialize (3724, 50385, 222); l_create_dist_count :=Arp_Auto_Rule.create_distributions (p_commit=>'Y', --P_COMMIT_AT_END p_debug =>'N', --Debug Flag p_trx_id=>1535592, --Customer TRX id p_suppress_round=>NULL, --Rounding Suppressed p_continue_on_error=>'Y'); --P_CONTINUE_ON_ERROR commit; dbms_output.put_line(' Dist Count -> '||l_create_dist_count); end; /* Just as the revenue recognition picks up by the gl_date on the ra_customer_trx_all table and puts it in different buckets in the ra_cust_trx_gl_dist_all. In the case of receipts, the receipts go into different accounts and it can be seen on the ar_cash_receipt_history_all based on different statuses. The revenue recognition program need not have to do any thing,the distribution are immediately generated once the receipt is created,remitted or cleared. */ select a.trx_number,a.creation_date from ra_customer_trx_all a, ra_cust_trx_types_all b where a.cust_trx_type_id = b.cust_trx_type_id and a.customer_trx_id in( select distinct customer_trx_id from ra_customer_trx_lines_all where accounting_rule_id = 1026 --and creation_date < to_date('01-JUL-2005') and creation_date > to_date('01-JUN-2005') and rownum < 100) and b.type ='INV' /*TAR 4430342.994 --------------- Hi We have a problem regarding the revenue distribution with respect to Credit Memos. Let us say we have an invoice raised in June 2005 for $329 and this invoice is associated with an accounting rule of 13 months (To summarize, the percentage and the revenue amount distribution are given in the attachment (INV_DIST.TXT) GL_DATE PERCENT AMOUNT -------- ------ ------ 6/8/2005 4.1672 13.71 8/1/2005 8.3343 27.42 8/8/2005 8.3343 27.42 9/8/2005 8.3343 27.42 10/8/2005 8.3343 27.42 11/8/2005 8.3343 27.42 12/8/2005 8.3343 27.42 1/8/2006 8.3343 27.42 2/8/2006 8.3343 27.42 3/8/2006 8.3343 27.42 4/8/2006 8.3343 27.42 5/8/2006 8.3343 27.42 6/8/2006 4.1555 13.67 Hence that revenue is recognized for each month until June 2006. Now in the month of Aug 2005, a credit memo has been generated for the amount of $200 and we have the credit memo accounting rule as LIFO. (The revenue distribution for this credit memo is given in the attachment CM_DIST.TXT). GL_DATE PERCENT AMOUNT -------- ------ ------ 11/8/2005 10.88 -21.76 12/8/2005 13.71 -27.42 1/8/2006 13.71 -27.42 2/8/2006 13.71 -27.42 3/8/2006 13.71 -27.42 4/8/2006 13.71 -27.42 5/8/2006 13.71 -27.42 6/8/2006 6.86 -13.72 According to us it is doing exactly what we expected it to do (for LIFO) ie. go to the farthest period and apportion the credit memo amounts to each period as it goes up the periods. However there is a small discrepancy in the period of June 2006 as you can see from those attachments. We expect the revenue amount for the credit memo to be -13.67 while the amount it is showing as -13.72. Our business is questioning as to why there is such a discrepancy. Is this a bug and if so could you please provide us with a fix. Your quick response is highly appreciated. Hi Tota, Thanks for the response. Let me make it clear for you. See the way LIFO is expected to work is that it should go to the farthest period ,which is Aug 2006 and put the same amount i.e -13.67 in that period and then come up the next period which is -27.42 and then keep doing same thing for each period going backwards until it is exhausted of that $200 amount. So in this case it ran out of that $200 amount by the time it came to the Nov 2005 period and it should put the remaining amount in that period. So according to our understanding it should put the remaining -21.81 amount in the November 2005. Instead for some unknown reason it is getting this amount of $13.72 (dont know how it got that amount) and putting it in June 2006 (which is incorrect). It should look at the invoice distribution for June2006 which is $13.67 and put the same amount of -$13.67 for the June 2006 period for the credit memo distribution. Just to summarize, we know that the credit memos follow the revenue distribution of the invoice and in the case of LIFO it should go by the amounts of the invoices (and NOT percentages). Hope I have explained the problem to you very clearly. Please get backto immediately as we need to close our books based on this bug as this has an financial impact. Thanks in advance. */ /* Accounting rules create the revenue recognition schedules for invoices. Accounting rules determine the number of periods and % of total revenue to record in each accounting period. When you run the revenue recognition program for an invoice that is associated with one or more accounting rules,Receivables creates the invoice's revenue distributions for the period or periods in which rules fall. The revenue recognition program does not pick the invoices with no accounting rule specified. Now after this, we can see that we have data in gl_interface, gl_je_batches,gl_je_headers,gl_je_lines as below. There is an exception to the above statement.If you set the profile option "Use Invoice Accounting for Credit Memo" to No, then the credit memos will have their own accounting rules. /*Receipt Write-Off Functionality : Small Balance Receipt Write-Off. -------------------------------------------------------------------- Some times we can have receipts in the AR with small balances which are in the Unapplied or Onaccount status.This could probably be because of the customer overpayments. Now we can write-off such small balances within certain limits defined for that user. That is a user can write off a specific receipt for an amount, if it is only within his limit. The important thing to note is that,receipt write-offs do not affect customer balances or cash account. Also we cannot write-off miscellaneous receipts and it can only done for cash receipts. Online receipt write-off : Receipts => Applications => Choose receipt write-off (inthe detailed block record),save it. Batch receipt write-off :Call the setup => Create receipt write off ,which in turn kicks off the Receipt write off batch program. (which can be run initially as a report and check and then actually run the program) So all these small balances of the receipts will go into a separate GL account, which is defined in the receivable activities. So receipt write off is a receivable activity. Typically once the receipt write-off completes the status of the receipt should be CL in payment schedules and the unapplied amount should be 0. Typically this program is run, before month end to close all those small receipts,so that they can close the period. Another important point about the Receipt Write-Off process is the write-off limit that is set in the systems option In the setup=> system options also, please make sure that the maximum write-off limit is properly set. This is the limit for all the users of the system (and hence should be very high and maximum). Make sure this amount is greater than any individual amounts. AutoAdjustment : Small Balance Invoice Adjustment : -------------------------------------------------- Just as we write off small balances of receipts, sometimes we might even small balances of Invoices ,which can be written off. If there are very few, then we can do it manually assign it to a receivable activity. Otherwise we can run the program, Control => Adjustments => Create AutoAdjustments /* This program takes some parameters and by clicking submit, it will submit a concurrent program which will write-off and close all the invoices which satisfy the criteria specified.*/ /*Global Billing Functionality - Intercompany Transactions from AR : -------------------------------------------------------------------- At sun, the global billing functionality does not mean that the bill is sent to a customer. But instead we are billing different OU's with in our company. Let us take this by example in the case of Sun Microsystems(SMI). Sun Operates in many countries around the world and hence has many Operating Units defined, Sun United States Sun United Kingdom Sun Argentina ,etc Now let us say Nortel Canada has placed a PO order for a service work to Sun Canada. In this case Sun Canada would be considered as host. So once the payment is fully made by Nortel Canada to Sun Canada, then the service fulfilment would start. Then Sun Canada might give that service to its different subsidiaries like Sun US,Sun India etc and get the service done. These subsidiaries like Sun US,Sun India etc are called receivers. Since the service is distributed, Sun US will have to pay its subsidiaries for the service they provided. So an invoice is created with each line being referring to one operating unit. "Sun United States" has a operating unit id = 203 (hr org id) "Sun United States" also has a company value = 110 "Sun United States" is defined with a subsidiary code which is a concat of LCO||999|||MCO = 110999110 what is the vanilla functionality for global billing. is this invoice being sent to the customer. why not put this in a DFF. /* AUTOINVOICE INTERFACE : ************************** select creation_date,credit_method_for_acct_rule,batch_source_name,ship_date_actual,a.* from ra_interface_lines_all a where batch_source_name = 'OM IMPORT' and creation_date >= trunc(sysdate) /*In the ra_interface_lines_all table, the interface_line_attribute1 would correspond to the order number from the Oracle OM i.e the autoinvoice process expects the order number in that column, but if it is not coming from OM and if we are directly populating it, it is a some sequence number And once the invoices are imported into AR tables, then the records are deleted from the interface tables. Actually when the Autoinvoice process runs, it imports all kinds of transactions i.e invoices, credit memos etc. While the credit memos are imported into AR, and if it finds the original invoice related information in the appropriate column, then it would go ahead and apply that credit memo to the particular transaction. In such case,we can go the table ar_receivable_applications_all table and look for that specific record; i.e. we can find that the customer_trx_id and the applied_customer_trx_id will correspond to the invoice and credit memo id. The different kinds of attributes that are stored in the ra_interface_lines_all table are given below. The 3 kinds of flex field attributes in the ra_interface_lines_all table are __ interface_line_attribute columns => contains the order related attributes __ reference_line_attribute columns => contains the original order related attributes. __ link_to_line_attribute columns => contains the tax,freight related attributes. /* Once the order is closed, the data goes into ra_interface_lines_all, ra_interface_sales_credits_all and ra_interface_distributions_all. When the Autoinvoice process runs and if it succeeds, the data goes into the ar receivables tables. If for any reason an order fails, then it goes into the ra_interface_errors_all table. Initially when a record is created in the ra_interface_lines_all table, the interface_line_id value is null for that record,when the Autoinvoice picks it up and processes it, it populates the interface_line_id column with some sequence value. (It is important to remember that when the records come from OM, they come in completed status. ** Ensure that payment terms, frieght, tax codes,salespersons,invoicing_rule_id, accounting_rule_id are present in the ra_interface_lines_all,otherwise the Autoinvoice will error out. ** Also ensure that both in AR and GL, the corresponding period is open. ** Ensure that the transaction source, has the autoinvoice and accounting options in a way that you want. i.e you want to match by the value or id. If it is value, then it will try to match by ref values. This could be one reason why we might end up with the interface line errors. This is very IMPORTANT. The starting point for the Autoinvoice is the ra_interface_lines_all table. This table can get the data from different sources. Typically users can populate this table from sql loader. However in general, whenever an order is closed,immediately and automatically this table will get populated with a record.If there are 2 lines in an order there will be 2 records in this table,and in this case the source will be called as 'OM IMPORT'. Hence we can see this batch source from the menu option setup => transactions => sources => AutoInvoice Options. Here we can see what are the grouping rule,gl_date options etc. Grouping rule is an important feature of the autoinvoice process. What this means is the Autoinvoice groups by all the columns that are mentioned in this grouping rule before it creates the invoices(or transactions) in the AR side. Ex 1: Let us say if there is an order which has got 2 lines (corresponding to 2 different inventory items). Corresponding to this,let us say there are 2 lines in the ra_interface_lines_all table. If the grouping rule says to group by (sales_order), then the Autoinvoice will create only 1 invoice since both the above lines correspond to only one sales order. Ex 2: Let us say if there is an order which has got 2 lines (corresponding to 2 different inventory items). Corresponding to this,let us say there are 2 lines in the ra_interface_lines_all table. If the grouping rule in this case says to group by (sales_order,inventory_item_id), then the Autoinvoice will create 2 invoices corresponding to two lines of the sales order. Similarly the line ordering rules. The grouping rules do a group by, while the ordering rules do an order by. That is,these rules ensure that the lines on the invoice are in the same order as they are in the sales order */ /* When the order is finally pushed from the interface table to the AR,the value of the gl_date that is populated in the lines table is obtained as follows.*/ ra_interface_lines_all.gl_date => (Check batch Source gl_date option) => YES => check the ra_interface_lines_all.ship_date_actual => NO => ra_interface_lines_all.sales_order_date => NO => default date on run autoinvoice SRS request window. -- The following query should give the information about the different available dates.*/ SELECT ship_date_actual,gl_date,sales_order_date,interface_line_id, batch_source_name, invoicing_rule_id, accounting_rule_id,interface_line_context FROM ra_interface_lines_all -- where interface_line_attribute1= '1100026568' WHERE interface_line_context = 'ORDER ENTRY' AND creation_date >= '28-MAR-2006' select rowid,gl_date, original_gl_date,interface_line_id, batch_source_name ,invoicing_rule_id, accounting_rule_id from ra_interface_lines_all where interface_line_attribute1='1100026562' -- 53984148 select * from ra_interface_distributions_all where interface_line_id = 53984148 /*The errors can be viewed from the menu option Control => AutoInvoice => Interface Lines */ select * from ra_interface_errors_all where interface_line_id = 53984155 select * from ra_customer_trx_all -- 11005 & 52365 where interface_header_context='ORDER ENTRY' and interface_header_attribute1='50915297' /* INVOICING RULE & ACCOUNTING RULES: The most important point to notice here is that, we have to define the invoicing rule, if we need to define the accounting rule. Unless we define the Invoicing rule ,we cannot define the Accounting rule successfully. Generally accounting rule is defined at the line level, that means even in the inventory for each master item we can define the accounting rule. Accounting Rules can be defined at the item level or at the memo lines. So when you create a transaction ,say an invoice,which consists of item. Now this item is associated with an accounting rule id(in inventory). If there is no accounting rule id, all the amount of the invoice is recognized in the current AR period,otherwise it is adjusted according to that rule. If you define an accounting rule both at the transaction header level and at the item level, then the item level will take the precedence. If a credit memo is created, in which case we need not give an item and choose a memo line. So the revenue is recognised according to the accounting rule mentioned in the memo line. In fact in a credit memo, We can even type in a value for the description in which case, the entire amount is recognized in the same period. */ select code_combination_id,percent, amount,gl_date,gl_posted_date,posting_control_id, account_class,acctd_amount from ra_cust_trx_line_gl_dist_all where customer_trx_line_id IN (10521857,10521856) and code_combination_id = 1047 select * from ar_memo_lines_all_tl where name like 'cm%' select * from ra_rules where name like '%12%' /*As mentioned earlier, if the invoicing rule is not specified, then you cannot specify the accounting rule. If the invoicing rule is "Bill in Advance" then you can specify any accounting rule, and the Unearned Revenue(UER) account will be hit ,when the revenue recognition program runs. If the invoicing rule is "Bill in Arrears" then you can specify any accounting rule, and the Unbilled Receivables(UBR) account will be hit ,when the revenue recognition program runs. Let us briefly understand how the accounting entries look like if we specify bill in advance and how Unearned Revenue entries will be : For example, a invoice was created on May 1 of USD 1200, entries will be 1-May-08: Receivables Dr 1200 Unearned Revenue Cr 1200 1-May-08: Unearned Revenue Dr 120 Revenue Cr 120 1-Jun-08: Unearned Revenue Dr 120 Revenue Cr 120 This way at the end of the 10 months, there will be "0" balance in the Unearned Revenue A/C and the Revenue A/C will be credited every month for equal amount and finally the total amount will be in revenue. */ /*Bill in Arrears Explanation : You can use this invoicing rule to recognize receivable (remember receivable not revenue) at the end of the revenue recognition schedule. Let us explain this with an example of an invoice with different invoicing rules, Invoice : $2000 Invoicing Rule : Bill in Advance Accounting Rule : 10 Month Invoice date : 10-JAN-2008; Payment term : Net 15 Due date : 25-JAN-2008 ----------------- Invoice : $2000 Invoicing Rule : Bill in Arrears Accounting Rule : 10 Month Invoice creation date = 10-JAN-2008; Payment term : Net 15 Invoice date is changed to 10-NOV-2008; Due date : 25-NOV-2008 (see the due date is 10 months + net 15) Hence if you see above, the invoice is having a invoice date as 10-NOV-2008, even though the invoice creation date was 10-JAN-2008. Now when the revenue recognition program completes, the account that is hit here is Unbilled Receivables (instead of unearned revenue),otherwise eveything remains the same. And to apply the same ex, we will have the accounting entries as, */ For example, a invoice was created on May 1 of USD 1200, entries will be 1-May-08: Revenue Cr 120 Unbilled Receivables Cr 1200 1-May-08: Unbilled Receivables Dr 120 Revenue Cr 120 1-Jun-08: Unbilled Receivables Dr 120 Revenue Cr 120 1-Feb-09: Unbilled Receivables Dr 120 Receivable Cr 1200 Revenue Cr 120 Unbilled Receivables cR 1200 This way at the end of the 10 months, there will be "0" balance in the Unearned Revenue A/C and the Revenue A/C will be credited every month for equal amount and finally the total amount will be in revenue. */ /*UNBILLED CREDITS : As explained earlier, unbilled credits are those credit memos which are having an invoicing rule of "Bill in Arrears". That means,the receviables */ /* DEFERRED REVENUE : To explain the Revenue recognition program, let us consider the example of the Gift Certificate. If you buy a Gift Certificate of $100 from a company X in a period ,say Q1, then the company cannot report this revenue of $100 for that period. It can only report the revenue when that gift certificate is redeemed, that is when somebody has used it, say may be in a different quarter Q2. So they can show revenue in Q2. When you use deferred accounting rules, the Revenue Recognition program creates a single distribution per line that posts to an unearned revenue GL account. You can use deferred accounting rules only for invoices that are assigned the Bill in Advance invoicing rule. If the invoicing rule on a transaction is Bill in Arrears, the Revenue Recognition program ignores the deferred flag. You can later earn the revenue using the Revenue Accounting feature So the essence is that you will not see any revenue lines, but there will be only one line corresponding to the unearned revenue account corresponding to the whole invoice amount. Later on, we can recognize the revenue amount as well from the Revenue Accounting Wizard from the menu item */ Control => Accounting => Revenue Accounting --Accounting Rules and First date in the Transaction Line.: /* Based on the first_date in the line item,I found that the trx_date and the gl date are automatically changing. Revenue recognition program is completing with warning,which I think is because of the first date specification in the rule.(I could not make out the message, as it is not clear). However when I saw the accounting entries for this particular transaction, it is not creating the accounting for the prior months, it is putting every thing under the first day of the current period,which is same as giving the first date as the first day of the current period. This is usually the case when let us say there is a service contract which actually was started some time back and has not been entered into the system till now. And since the prior periods are closed, all the revenue till now will fall in the current period and after that in the subsequent periods.*/ /* AutoAccounting: AutoAccounting is the tool which determines which GL account should be chosen when generating the accounting lines for the transactions. Whether the transactions are entered online or thru autoinvoice, Autoaccounting will generate the GL acounts for each account type. In the auto accounting we can specify from which source we need to pick the gl code combination for each account type ex, Receivable, Revenue, tax,frieght etc As an ex of autoaccounting, let us consider an accounting structure consisting of (company,Business Unit, dept, Nat account, IC segment1, line2,line3) Let us say we have an account "Unearned Revenue" ,where in the autoaccounting we have the setting as follows,i.e For Company,Business unit, Dept, Account ==>> transaction type. For Product Line ==>> Standard Line(i.e from Inventory setting). So in this case, when the autoaccounting generates the distributions, it will take the first four segments from Transaction types(ra_cust_trx_types_all), and take the product line segment from inventory and then concatenate and form a new GL account combination. I think the Autoaccounting will only decide the distributions, it will not generate the actual accounting entries, which is done by the Revenue Recognition Program. That means once the revenue recognition is complete you will find the entries in the GL distribution table. */ select * from ra_account_default_segments /* Just remember one important point : AutoInvoice => For invoices without rules; Revenue Recognition => For invoices with rules; What this means is that if you create an invoice, with out a invoice/accounting rule, once the invoice is completed, the distributions and accounting are created immediately after completion. No need to run the revenue recognition for generating accounting distributions. However if you have an invoice/accounting rule, then you need to run revenue recognition for generating accounting distributions. */ /* AUTOINVOICE AND AUTOACCOUNTING : In AutoAccounting, we specify for each account type like Receivable, Revenue, the source for each segment of the COA. Now when an order of a particular type is fulfilled it directly falls into the AR interface(ra_interface_lines_all) table. At this point we run the AutoInvoice to import the invoices,which internally runs the AutoAccounting process as well. Now if you want AutoAccounting to determine your general ledger accounts you must not enter values in ra_interface_distributions_all. If you enter values in this table, then Autoaccounting will NOT be run and the AutoInvoice will simply pick the values from this distribution table. Now let us say if you dont populate values in the distribution table and you use the AutoAccounting tool,which means it will find out the distribution for you. Then say for receivables,it will go to the autoaccounting setup and find out the sources. If the segment is based on transaction type, then the segment value is obtained from the transaction type. (remember the AR trx type is obtained from the OM trx type as each order type can be associated with a receivables transaction type). If the segment is based on standard lines, then the Autoinvoice will get the segemnt value from the Inventory item from the interface lines. If the segment is based on sales reps, then the Autoinvoice will get the segemnt value from the RA_INTERFACE_SALESCREDITS_ALL for each invoice line in RA_INTERFACE_LINES_ALL. This is actually obtained from the order entry information. */ /* Some of the contexts come out-of-the-box with Oracle Apps. For ex, the context code 'ORDER ENTRY' in the Line Transaction Flexfield (where each attribute corresponding to fields like order number,delivery waybill etc) is defined by Oracle apps by default.What this means is that if we go the transaction line and open up the DFF Line Transaction and if we choose the context value of 'ORDER ENTRY', then we can see all these fields. Likewise we can define as many context codes as possible and define corresponding segments for them. When a RMA is created and comes into the ra_interface_lines_all table, the reference_line_id will store the customer_trx_line_id of the original invoice. ie. ra_interface_lines_all.reference_line_id = ra_customer_trx_lines_all.customer_trx_line_id. */ select batch_source_name, interface_line_context,interface_line_id, creation_date ,interface_line_attribute1 ,interface_line_attribute2 ,interface_line_attribute3 ,interface_line_attribute4 ,interface_line_attribute5 ,interface_line_attribute6 ,interface_line_attribute7 ,interface_line_attribute8 ,interface_line_attribute9 ,interface_line_attribute10 ,interface_line_attribute11 ,interface_line_attribute12 ,interface_line_attribute13 ,interface_line_attribute14 ,interface_line_attribute15 from ra_interface_lines_all where interface_line_attribute1= '1100026568' select reference_line_attribute1 ,reference_line_attribute2 ,reference_line_attribute3 ,reference_line_attribute4 ,reference_line_attribute5 ,reference_line_attribute6 ,reference_line_attribute7 ,reference_line_attribute8 ,reference_line_attribute9 ,reference_line_attribute10 ,reference_line_attribute11 ,reference_line_attribute12 ,reference_line_attribute13 ,reference_line_attribute14 ,reference_line_attribute15 from ra_interface_lines_all where interface_line_attribute1= '1100026568' delete ra_interface_lines_all where interface_line_attribute1= '1100026567' select link_to_line_attribute1 ,link_to_line_attribute2 ,link_to_line_attribute3 ,link_to_line_attribute4 ,link_to_line_attribute5 ,link_to_line_attribute6 ,link_to_line_attribute7 ,link_to_line_attribute8 ,link_to_line_attribute9 ,link_to_line_attribute10 ,link_to_line_attribute11 ,link_to_line_attribute12 ,link_to_line_attribute13 ,link_to_line_attribute14 ,link_to_line_attribute15 from ra_interface_lines_all where interface_line_attribute1= '1100026568' select * from ra_customer_trx_all where interface_header_attribute1 = '1100026562' select * from ra_customer_trx_lines_all where customer_trx_id = 1407740 select b.type,a.trx_number from ra_customer_trx_all a , ra_cust_trx_types_all b where a.cust_trx_type_id = b.cust_trx_type_id and customer_trx_id = 1407739 select * from ra_customer_trx_all where trx_number = '1170028229' select * from ra_customer_trx_lines_all where customer_trx_id = 1407739 select * --rowid,invoicing_rule_id,accounting_rule_id,term_id from ra_interface_lines_all where interface_line_attribute1 = '1100026568' /*Once the autoinvoice completes, the exact set of columns in the ra_interface_lines_all are copied over to the lines table ra_customer_trx_lines_all.*/ update ra_interface_lines_all set reference_line_attribute1 = interface_line_attribute1, reference_line_attribute2 = interface_line_attribute2, reference_line_attribute3 = interface_line_attribute3, reference_line_attribute4 = interface_line_attribute4, reference_line_attribute5 = interface_line_attribute5, reference_line_attribute6 = interface_line_attribute6, reference_line_attribute7 = interface_line_attribute7, reference_line_attribute8 = interface_line_attribute8, reference_line_attribute9 = interface_line_attribute9, reference_line_attribute10 = interface_line_attribute10, reference_line_attribute11 = interface_line_attribute11, reference_line_attribute12 = interface_line_attribute12, reference_line_attribute13 = interface_line_attribute13, reference_line_attribute14 = interface_line_attribute14, reference_line_attribute15 = interface_line_attribute15 where interface_line_attribute1='1100026567' /*Intuit Process of Invoice Import XXINT_OM_ORDER_IMPORT_PUB (Imports Orders) They do not have the orders being progressed thru the steps of pick launch,pick release and ship confirm etc. Once the order is booked by this program and populated into the ra_interface_lines_all table. After this PRE-AR (-- ( PRE-AR) Intuit AR: Invoicing & Accounting Parallel Process (XXINT_AR_MULTI_INV_REV_PROCESS) process will run and will populate the key fields of the ra_interface_lines_all table. The key attributes in the ra_interface_lines_all are from interface_line_attribute1 thru interface_line_attribute15. If any of these fields are null, then standard AutoInvoice process will fail.(PRE-AR will populate these fields). Following this the (Intuit AR: Auto Invoice Master Program) will pick up these records and populate into the AR related table. Actually this program will inturn call the Oracle AutoInvoice program. */ /* The data is transferred into the GL,either detailed or Summary, If the data is pushed in detailed format, the reference columns reference_1,2 etc are populated with the feeder system ids. If in summary format, these columns are not populated with any values. */ select * from gl_je_batches where je_batch_id = 457618 select * from gl_je_headers where je_batch_id = 457618 -- je_source => Receivables, je_category => Sales Invoices, Credit Memos -- REFERENCE_1 PCID Posting Control ID -- REFERENCE_2 ID Customer Transaction Id -- REFERENCE_3 SOURCE_ID Cust Txn GL Dist ID -- REFERENCE_4 "TRX/REC_NUMBER" Trx Number -- REFERENCE_5 REF_25 Shipto number -- REFERENCE_6 CUSTOMER 'CUSTOMER' -- REFERENCE_7 BILL_TO_CUST Bill To customer -- REFERENCE_8 "TRX/REC_TYPE" 'CM' i.e Credit Memo -- REFERENCE_9 SOURCE_TYPE CM_REV -- REFERENCE_10 SOURCE_TABLE RA_CUST_TRX_LINE_GL_DIST select * -- reference_1,reference_2,reference_3,reference_4,reference_5 from gl_je_lines where je_header_id = 194295 -- and reference_4 = 1170028234 -- and reference_4='1170025015' -- The reference_1,2 etc attributes referred in gl_import_references and --gl_je_lines store same values. SELECT * FRom gl_import_references where je_batch_id = 457615 and je_header_id = 194283 --and reference_4 = 1170028235 /*-- Detail : So from the above column explanation, it seems clear that if the data is moved in a detailed format, then it stores the level from the gl_dist tables. -- Summary : In the case of summary, what is the level at which the data is stored, transaction, account? */ select * from ra_customer_trx_all --where customer_trx_id > 1407757 -30 WHERE trx_number= '1170025015' order by creation_date select * from ra_customer_trx_lines_all -- 53984190 where customer_trx_id = 1235368 select * from ra_cust_trx_line_gl_dist_all where customer_trx_line_id = 43799136 ---- select * from ar_payment_schedules_all where payment_schedule_id < 0 /* On-Account credit memo : not always a credit memo be tied to an invoice. Sometimes there could be a credit memo for a specific customer but which is not tied to any invoice as such, these kind of credit memos are called on-account credit memos.*/ /*AutoInvoice and Prepayment Matching : Usually once all the invoices are imported into the AR system,the autoaccounting process will try to "Complete" them and then try to run the program "Prepayment Matching Program" which applies any existing prepaid receipts to these just-imported invoices. So if you dont want AutoInvoice to run this program then you will have to disable this program from the Concurrent program setup from sysadmin responsibility. This is probably this program "Prepayment Matching Program" might be always run from Autoinvoice program. */ /* TAX INTERFACE : How TAXES are dealt with in Oracle Financials Usually companies use the most popular tax softwares that are available in market like Vertex,Taxware,Sabrix etc. Since Uncle Sam (US goverment) tax rules keep changing regularly i.e the sales tax percentage,vat tax etc vary from state to state. Similarly there are different kinds of taxes like state tax, city tax ,county tax etc. These tax softwares will keep track of these of all these changes regularly. That is, say if a customer is using the Vertex tax software, then the Vertex company will keep sending regularly the files to their customers so that they are up-to-date in terms of tax information. Typically Vertex deals with what is called geocode which identifies uniquely a particular geographical area. Just like Autoinvoice,Lockbox etc the "Sales Tax Rate Interface" will populate the tax information into this table ar_location_rates. So the way Vertex is integrated with Oracle apps is using the Tax interface. That is from the vertex system,the data is populated into the interface tables and after running the "Sales Tax Rate Interface" program, the data is populated into the ar_location_rates table where all the tax rates for different postal codes are stored and the triggers will immediately populate the data into ar_sales_tax. */ select location_rate_id,location_segment_id,from_postal_code,to_postal_code, tax_rate, attribute_category, attribute1,attribute2 from ar_location_rates where attribute_category='VERTEX' /* ar_location_rates is the source of all the sales tax rates. Any changes in this table are automatically (thru triggers) into a composite rate and a composite rate is stored in the ar_sales_tax. Here in this table,the tax rate is the sum of the sub rates that is stored in the location1_rate, location2_rate etc. So if your key flexfield includes something like state, county,city, then these 3 correspond to the location1_rate,location2_rate, location3_rate. We can also get the rate corresponding a particular location from the from Setup => Tax => Sales Tax Rate */ select * from ar_sales_tax where upper(substr(from_postal_code,1,5)) = lower(substr(from_postal_code,1,5)) and upper(substr(to_postal_code,1,5)) = lower(substr(to_postal_code,1,5)) and 94043 between to_number(substr(from_postal_code,1,5)) and to_number(substr(to_postal_code,1,5)) -- This table does not store any tax rate information,it only stores about the location information. select location_segment_value , location_segment_qualifier, attribute_category, attribute1,attribute2 from ar_location_values_v where location_segment_qualifier = 'STATE' /*DEFAULT TAX CODE:(HOW A TAX CODE IS CHOSEN): Usually we can define any number of tax codes that we want. However while entering a transaction at the line level, the tax code will default to a specific code. This is done as follows. When we go the System Options under the tab "Tax Defaults and Rules" there is a hierarchy mentioned under the tax code defaults,which mentions the precedence of choosing the tax codes i.e first the customer site,then customer, and product (i.e the inventory item level) and finally "System Options". If it comes to "System Options", since there is the location flexfield value there, it will choose the corresponding location flexfield. There is a tax code location defined in the tax code setups.That is the reason why you dont see any rate specified in the tax codes Location,because it is calculated on the fly (which is the sum of the sub segments) */ /* A word about Vertex software : The document "Integrating Oracle Receivables with Vertex Quantum" released by Oracle says to enable the debugging of the tax calculation we need to set the following profile options. Conveniently set the profile options mentioned in the note 279118.1 and get the tax debug file right from the sqlplus output. */ Finding the Vertex Geocode given a state,county,city combination or zip code. Let us say we have a zip code 95050 which corresponds to (CA,Santa Clara, santa clara city) --Now go to the screen, (to get the authority which state, county,city from the zip) Setup => Tax => Sales Tax Rates --From the above combination , go to screen Setup => Tax => Locations /* and choose city value in the Find list box and enter the county. Click on the required city. Now click on the DFF and get the Vertex gecode. Now in this case, the geocode for santa clara city is 050853180 Usually when vertex is installed it populates a DFF values of 'VERTEX'. Geocode, usually the first digit/2 digits of the geocode corresponds to the vertex state code, so in this case the state code for CA is 05. */ select rowid,a.* --invno,shiptogeocode,invtotaltax,citytax, cityrate,statetax,staterate, cntyrate,cntytax from vertex.regprereturnstbl a -- 30649222 where invdate = 20060824 and invno in (1190012439,1190012434) -- transtaxedgeocode=441136035 -- arp_tax_view_vertex, ra_tax_exemptions_all /* Typical Issue : One issue which arose is the tax calculation discrepancy. When we create a transaction for a specific particular customer based in (Texas,Dallas,Addison) then the tax rate is calculated as 6.25%. However when I lookup the tax rate for that particular city,county,state, the Vertex shows that as 8.25% which is the correct rate. This was caused because for that specific customer, the value of the flag "Inside City Limits" was not set at the customer ship-to site level,which is the reason why it was not calculating the city tax, for that particular customer. */ select customer_id, party_id from ra_addresses_all where sales_tax_inside_city_limits is not null select * from hz_locations -- CUSTOMER INTERFACE /******************************* delete ra_customers_interface_all delete ra_customer_profiles_interface delete ra_contact_phones_interface The Customer Import done using the standard customer interface. Alternatively it can also be done using the hz api, however,I believe the customer interface is much better(??). The customer import references the orig_system_customer_ref between interface tables. What i found is that at a bare minimum, we should have a record in profile interface table(it does not take any default profile). So if we know the profile name in AR, we need to put that in the customer_profile_class_name column. It does not matter whether we have the contacts,paymethods, banks etc interface information. Incidentally if there is a record in the ra_customer_profiles_interface which is not referenced by any of the records in the ra_customers_interface_all table, then the "customer interface CI" thinks that it is importing the profile. If you dont give the existing AR profile name, then you have to give a whole bunch of other information so that the CI will create a new profile for you. */ insert into ra_customers_interface_all (orig_system_customer_ref ,insert_update_flag ,customer_name ,customer_status, last_updated_by ,last_update_date ,created_by ,creation_date,validated_flag) values (2001,'I','MY IMPORTED CUST 3','A',-1,SYSDATE,-1,SYSDATE,NULL) insert into ra_customer_profiles_interface (customer_profile_class_name, orig_system_customer_ref,insert_update_flag ,credit_hold ,last_updated_by ,last_update_date ,creation_date ,created_by , validated_flag ) values('DEFAULT',2001,'I','N',-1,sysdate, sysdate,-1 ,NULL); insert into ra_customers_interface_all (orig_system_customer_ref ,insert_update_flag ,customer_name ,customer_status, address1,city,state,postal_code,country, orig_system_address_ref,last_updated_by ,last_update_date ,created_by , creation_date,validated_flag) values (2001,'U','MY IMPORTED CUST 3','A','870 E EL CAMINO REAL','MT VIEW','CA',95032,'US', 'Legacy System',-1,SYSDATE,-1,SYSDATE,NULL) commit; -- Request id is the back populated column value by the customer interface program, validated flag -- indicates whether the record is validated or not select orig_system_customer_ref ,insert_update_flag ,customer_name ,customer_status, validated_flag, request_id from ra_customers_interface_all select * from ra_customer_profiles_interface select * from ra_contact_phones_interface select * from ra_customers order by creation_date desc select * from hz_parties where orig_system_reference = '2001' select * from hz_cust_accounts where party_id = 1758 /* insert into ra_contact_phones_interface (orig_system_customer_ref ,insert_update_flag ,telephone,orig_system_telephone_ref, last_updated_by ,last_update_date ,created_by ,creation_date,validated_flag) values (2001,'U','6509409550','6509409550',-1,sysdate, -1,sysdate,'N'); */ -- Autoinvoice Query. select * from ra_interface_lines_all where rowid in (select min(rowid) from ra_interface_lines_all where trx_number is not null group by trx_number) order by trx_number /* For the cash receipts , the receivable activity or trx id will be null, */ SELECT NULL VID ,NULL PID ,rc.customer_number OracleAccountNumber ,rc.customer_name CompanyName ,acra.receipt_number PaymentNumber ,arm.name PaymentType ,acra.amount Amount ,arpa.amount_applied AmountApplied ,acra.receipt_date PaymentDate ,rcta.trx_number InvoiceNumber ,arpa.receivables_trx_id Rtrxid ,arta.name ReceivableActivity ,acra.currency_code ReceiptCurrency FROM ar_cash_receipts_all acra ,ar_receivable_applications_all arpa ,ra_customers rc ,ar_receipt_methods arm ,ra_customer_trx_all rcta ,ar_receivables_trx_all arta where acra.cash_receipt_id= arpa.cash_receipt_id and acra.receipt_method_id = arm.receipt_method_id and acra.receipt_date >= '18-NOV-2005' and rc.customer_id = acra.pay_from_customer -- and receipt_number = 'WTR113004A' and arpa.applied_customer_trx_id = rcta.customer_trx_id(+) and arpa.status <> 'UNAPP' and arpa.receivables_trx_id = arta.receivables_trx_id(+) order by 1,2,3,4,5,6,9 /* the account name in the hz_cust_accounts is for some reason null and hence the ra_customers view is looking at the hz_parties.party_name */ /* Deleting a transaction. Normally we would not be able to delete a transaction, however,if we set the system option in AR, we should be able to do that. Due Date(term_due_date) : The due date indicates when the invoice is due. There are due dates in the tables ra_customer_trx_all and ar_payment_schedules_all. But always pick it up from the payment schedules table. */ /*Receipts API vs Lockbox Once the receipts data comes from the bank, it can be loaded into the AR table, using the receipt api or for more simple lockbox. For receipts api, the file format needs to be understood, parsed and for each such record the receipts api needs to be invoked which inturn creates the receipts in AR. You can change the receipt amount regardless whether the receipt has been posted to gl or not (or regardless of the profile option AR: Bank Charges) /* Payment schedule with the payment schedule id <0: All the receivable activities that we define as the receivable activities for ex, prepayments, credit card refunds, will go into the ar_payment_schedules_all table as well, with payment schedule id < 0, so that way, some of them are available to be picked when we are applying a receipt to these activities. */ select payment_schedule_id, trx_number from ar_payment_schedules_all where payment_schedule_id < 0 /* Printing an Invoice : Also if you print an invoice, you cannot incomplete that invoice any more. No,however once we create a transaction of this type, then all the setting of this transaction type will go to that particular transaction. So for ex, if the print type is no, and if you create an invoice of this type, then the print flag of this invoice is no. So even if we change the print type =yes on the transaction type after that transaction is created, it does not help. i.e you still cannot print.*/ /* Payment Netting : Payment Netting is a functionality provided in 11.5.10. Payment Netting is something to do with when a Customer is also treated as supplier (for refunds or any other business requirements). Netting would work only if your customer and the supplier happens to be the same party That is we create transactions for a customer and if there are any refunds to be made , then we can use the same customer as a supplier and pay him. I heard from some one, by giving the same tax identification number for two parties they can effectively the same party. Is this true? (Is payment netting same as Customer Supplier Netting (is Payment netting a receipt applied to another receipt.) -- Incompleting an Transaction : To incomplete transactions in AR, the following things should be considered:- The transaction should not have been posted to GL. There should be no receipts for this transaction. The dunning letter program should not have run for that transaction. The main important thing is under System options, Trans and Customers tab, "Allow Transaction Deletion" check box should have been checked. So even though the payment terms are defined for installment types, there might be the different payment schedules for them,but the gl_date will still be following the accounting rule and hence all the revenue will be recognized in the same period, if the accounting rule is Immediate. /* Balancing Segment : Usually an accounting segment would have as structure like Company | Dept | Account | Line1 | Line2. When we set up the account, usually we mention what is the balancing segment. What a balancing segment means is that for each value of this segment, the credit and debit entries will cancel each other or balance each other. For ex, for any segment value ,say, '01', all the entries will balance each other. Usually it is recommended that if you have a company segment, then you should always set the company as the balancing segment. However for a specific dept ,it may not balance, because it could be possible that we post credit entry in one dept and debit entry in another dept account. However since both the depts will fall under the same company, at the company level, it should balance. */ /*Accounting Rules and Payment Schedules : Recognizing revenue is done using invoicing and accounting rules. Billing is done using payment schedules. You can setup a payment schedule to make 1/4 due at each of 4 dates of the year. These are two different animals. /*The transactions are coming from different sources,say from Order management, Projects, Service Contracts etc to AR. Let us say there are two transactions one coming from OM and another from Service Contracts(OKS) and both of them have the trx date and GL date as 30-AUG-2006. The August period is closed and the september period is open. However one of them has successfully gone thru the Autoinvoice while the other has not. This could be because the transaction source for each of them might have different setup values ie. Setup => Transactions => Sources => "GL Date in a Closed Period". */ /*Dunning Letter Generate : The Dunning Letter Generate program is the standard program provided by Oracle. The Dunning Letter Generate Program can be invoked from the menu option */ Print Documents => Dunning Letters /*The typical important parameters of this program are the letter set and the transaction types. Actually we can run this program even for a particular customers, so that it will print the dunning letter corr to the invoices of that particular customer only. The trans type low and high means, it will take all the transaction types which falls lexically between those two.*/ /* The standard program will spawn the program "Dunning Letter Print from Dunning Letter Generate". For testing the dunning process we can actually change the due date of a particular invoice even if it has been posted to the GL(or printed). This can be done from the Collections menu. */ Collections => Account Details /* So this particular function is only available for the collectors. Verisign Custom Process : In Verisign, the standard program Dunning Letter Generate has been modified to call another custom program which actually reads thru a profile value and get the different dunning buckets and based on that,it would send different kinds of email messages. /*One possible reason a particular customer might not get a dunning letter even though he might have the invoices due is because of the setting at the site level.*/ Customers => bill to site => "Profile: Document Printing" tab => "Send Letters" Check box /*When when the Dunning Program Is run with a specific Dunning Letter Set , It will pick up only those invoices whose dunning letter set matches the Letter Set Parameter.*/ Customers => Bill to site => "Profile: Document Printing" tab => Dunning Letter Set needs to be set. -- Look at the consolidated dunning check list document. --Receipt Amount Update : -------------------------- You need to set Menu Exculsion function of "Receipt: Update" to achieve this. An ex of error caused by updating the receipt amount after it has been posted to GL /* The original receipt was created for the amount of 119.70. The receipt was applied to invoice 99091272 for the amount of $ 39.90. There was $79.80 left unapplied. The left over of the payment was supposed to be going to Bad Debt reserve. In July, the amount on receipt number 3103 was changed from $119.70 to $39.90 and a miscellaneous receipt was created to bad debt for $70.90. The correct way to deal with this situation is: Unapply and Reverse the entire receipt ($119.70) Create one receipt for $39.90 and apply it to the open invoice. Create a second miscellaneous receipt for $79.80 for bad debt. I think if we reverse the entire thing and re-enter the receipts again the correct way, then will be fine. */ /*"AR: Allow Overapplication In Lockbox" and "Allowing the Overapplication" : Issue : If the profile option "AR: Allow Overapplication In Lockbox" is set and the transaction type is not set, the remainder of the receipt will be unapplied. If the transaction type allows overapplication, but the profile option does not, then you will still have the remainder of the receipt unapplied. Now our requirement is that the credit memos should be able to drive the invoices to zero or negative balances. However when the lockbox applies receipts to invoices, they should not be able to drive the invoice balance to negative and amount should be shown as Unapplied. Ideally this can be obtained by setting the profile option "AR: Allow Overapplication In Lockbox" to "No" with the transaction type "Allowing the Overappliction". However what I have seen is that even though in our production system this particular profile option is set to No, it is still going ahead and doing the Overapplication and driving the invoices to Negative balances. Fix : I have researced on this and found that, this is an unpublished Bug 4931731 with oracle. Oracle has identified it as a bug and released a Document "Lockbox Program Ignores Profile Option 'AR: Allow Overapplication In Lockbox' And Applies Receipts To Closed Transactions. Note:358321.1)" in Feb 2006. They also have a Standalone Patch (patch 4904833) ready for this particular one. */ -- Query giving the credit limits at the customer site level. select hca.account_number customer_number ,hcsua.location location ,hcpa.overall_credit_limit overall_credit_limit ,hcpa.trx_credit_limit order_credit_limit from hz_cust_accounts hca ,hz_cust_acct_sites_all hcas ,hz_cust_site_uses_all hcsua ,hz_cust_profile_amts hcpa where hca.cust_account_id = hcas.cust_account_id and hcas.cust_acct_site_id = hcsua.cust_acct_site_id and hcsua.site_use_id = hcpa.site_use_id and hca.cust_account_id = hcpa.cust_account_id and (hcpa.overall_credit_limit > 0 or hcpa.trx_credit_limit > 0) and hcsua.site_use_code = 'BILL_TO' --and account_number = '59402' and hcas.status = 'A' and hcsua.status ='A' and hca.status ='A' order by hca.account_number REVENUE MANAGEMENT AND REVENUE POLICY : --------------------------------------- There is a separate engine called Revenue Management Engine in AR. The timing of the revenue recognition program is primarily controlled by the Revenue Management Engine. That is its main functionality. - Use the revenue policy tab in the System Options window to specify your enterprise's revenue policy. - The revenue management engine uses the information you enter in this tabbed region to make automatic revenue recognition decisions for your imported invoices. - The Revenue Management engine compares each invoice that you import against the infromatoin that you enter in the revenue policy tab. The revenue Policy tab has mainly 5 fields. Standard Refund Policy Days : This field is related to invoice related to the service contracts. If the contract refund period > refund period specified here, the revenue Mgmt automatically defers the revenue on that line. Payment Term Threshold Days : This is the maximum days for the payment term. If an invoice payment terms(say net45) is greater than the payment term specified here (say, 40), then the Revenue Management engine defers the revenue for that particular invoice. Credit classifications for deferred Revenue : First ,second and third selection : These three fields are basically related to the noncreditworthy customers. If the Rev Mgmt recognizes an invoice corresonding to a customer with bad credit, then the engine automatically defers that invoice revenue. In all the above, we mentioned that Rev Mgmt is deferring the revenue for that line, what I think Revenue Management is doing is to update the interface lines with the contigency code accodingly. Event-Based Revenue Management, is said to be enabled if either one of them is enabled. Atleast one revenue policy option is being set OR Imported billing lines are associated with contigency codes. 11.5.9 & 11.5.10 Difference for AR : /************************************ 1) The receipt workbench screen in 11.5.9 (refer to Page 2) is different from receipt screen in 11.5.10 (Page 3). The screenshots of both of these are in the document. From Receipts => Receipts, The search and Apply button has been added in 11.5.10. The different tabs of the receipt workbench have been accommodated in only two tabs in 11.5.10. (Main & More) 2) In 11.5.10, in the setup => transactions=> transaction sources The “receipt handling for Credits” field has been added.(Page 4) which is not there in 11.5.9. 3). In 11.5.10, in the setup => receipts => receivable activities, A new type of receivable activity (Payment Netting) has been added which was not there in 11.5.9. 4). There is a difference in the screens in 11.5.9 and 11.5.10 for the freight carriers’ setup. From setup => System => Freight Carriers , the freight carrier screen is different in 11.5.9 (Page 6) and 11.5.10( Page 7) The number of tabs are different and more in 11.5.10 than 11.5.9. 5) There is a difference in the system Options screen in 11.5.9 and 11.5.10. There is an additional tab by name “Claims” in the System Options window in 11.5.10 (page 8 ) which is not there in 11.5.9(Page 9) 6) There is a difference in the layout of the locations form in 11.5.9 (Page 10) and 11.5.10( Page11) Setup => System => Organizations => Locations There is an additional field timezone in the locations form 11.5.10( Page11). 7) There is an additional function in 11.5.10 (Page 12) And it is “Correct Invalid GL Accounts”. (This function is not there in 11.5.9) RECEIVABLES ARCHIVE & PURGE PROCESS --------------------------- Archive Preview Archive Header Archive Header Report Archive Detail Archive Detail Report Archive Restart Archive Selection Archive Summary Report Archive and Purge New Archive and Purge Call New Archive and Purge Archive to File -- Usually the purge program will have a criteria. if there is a chanin of transactions, then the archive and purge program will delete the entire chain, if any one transaction does not satisfy the purge criteria. Clear archive tables, ar_archive_header, ar_archive_detail Ensure that no other concurrent programs are running and no users are accesssing the system. Runn the OSC sales compensation interface, to move the data from the trx hdr,line,lne_salesreps Intrastat ?? verify autoinvoice tables are empty (otional) verify lockbox tables are empty (optional),both ar_payments interface and ar_interim cash lines tables Run the tax reports and store them in file format backup the database. Archive and Purge Cycle : ------------------------- The cycle for the standard Archive and Purge program is divided into four separate processes: Selection and Validation, Archive, Purge, and optionally Copying to a file. General Questionnaire : ---------------------- 1. What are the issues with closing a period. Typically let us say you are trying to close a period in AR or AP. However when we try to do that the system will not let you do that. In that case, we can run the reports like Unposted Items Report and Incomplete Invoices Report etc. Unposted Items report ,as mentioned before, will print all the items that are not being posted to GL yet. These items can be because of the incorrect (cr,dr) distribution differences that exists. For ex, for a particular transaction,there could be cr entry($5.5) and debit entry($6). We need to resolve them ,post them to gl and try to close the period again. */ 2. How to get Customer Balances from backend: How to find a customer balance : Collections => Account Details Or select from this view. select balance,acctd_balance,location from ar_customer_accounts where customer_id = 671040 and currency_code = 'USD' 3. What happens when two consecutive periods are open,say June and July and you are trying to issue a credit memo on July 1st for a June Invoice. GL date would be the system date. However we would like to have the GL date of the CM to be the same as the GL date of invoice. So we have to manually go and change the GL date to be in the same month i.e in June. This is done for the purpose of revenue recognition process. 4.What is the difference between Bill in Advance and Bill in Arrears for the Invoice rule : Bill in Advance => Receivable is recognized immediately Bill in Arrears => Receivable is not recognized immediately and it is put in a Unbilled receivables initially and then in recognized in portions. 5. Difference between Invoice rule and Accounting rule : Invoice Rule determines how the receivable is recognized while, Accounting Rule determines how the revenue is recognized. And you cannot have accounting rules with out specifying the Invoice rules. 6. What is the difference between Invoices with rules and Invoices without Rules. The accounting is done by AutoAccounting and Revenue recognition for invoices without and with rules respectively. AutoAccounting => For invoices without rules; Revenue Recognition => For invoices with rules; so the bottomline is even autoaccounting can be used for recognizing revenue in the case of invoice without rules. 7. You have created a remittance batch for a receipt by providing a wrong bank name.Now what are we supposed to do as a first step? Should we delete the remittance batch? 8. What are the different steps that Autoinvoice does Import the invoices Try to complete them. Import the credit memos Try to apply the credit memos to the associated invoices. Try to run the Prepayment matching program so that if there are any prepaid receipts,they can be applied to the just imported invoices. Try to run the revenue recognition. 9.What is Revenue Accounting Wizard : Revenue accounting wizard is a tool which lets you make the adjustments to the accounting or the amounts for all those invoices and credit memos with defined accounting rules. Revenue is said to be scheduled if the distributions are created. Most generally the revenue accounting wizard is used to adjust the deferred revenue invoices. Or You can manually defer the revenue corresponding to any invoice using the Revenue Accounting wizard. 10. How to recognize deferred revenue : Receivables identifies deferred revenue for invoices with rules having deferred flag set. The only way to recognize revenue for such invoices is to go to the Revenue Accounting wizard and go to Actions wizard. 11. What items are processsed by Revenue Recognition. Interestingly Revenue Recognition only processes the Invoices and Credit memos (not debit memos, chargebacks, adjustments etc). Although this needs to be confirmed. 12. Use the revenue accounting feature to make revenue adjustments to completed invoices and credit memos. 13. Can I apply a receipt of USD or Credit memo of USD to an invoice of INR. Yes, cross currency receipt application is available,however we need to set the appropriate profile option. However if you are trying to apply a credit memo then the credit memo and transaction(Or invoice) currency should be the same as of R12(12.0.6). 14. Are receivable and revenue same as far as autoaccounting is concerned?? No. while setting up Autoaccounting, in receivable account, we cannot choose the standard line corresponding to inventory items, as the receivable account corresponds to the whole invoice and not the lines. However in the revenue account setting, we can choose all the values of standard lines, transaction type, sales person etc. 15. What is the difference between two accounting rule types?? Accounting, Fixed Schedule Accounting, Variable Schedule In the Accounting, Fixed Schedule, you specify the schedule at the time of the rule definition, i.e you candefine 12 monhths and the rev rec program will apportion the revenue accordingly. In the Accounting, Variable Schedule, you cannot specify the schedule at the time of rule definition. However youcan specify the scheduleat the time of the invoice creation or import. 15. What are the different types of transaction from Revenue Recognition stand point ? Recognition of revenue from four types of transactions: 1. Revenues from selling inventory are recognized at the date of sale often interpreted as the date of delivery. 2. Revenues from rendering services are recognized, when services are completed and billed. 3. Revenue from permission to use company’s assets (e.g. interests for using money, rent for using fixed assets, and royalties for using intangible assets) is recognized as time passes or as assets are used. 4. Revenue from selling an asset other than inventory is recognized at the point of sale, when it takes place. 16. What is the Revenue Recognition Principle. The revenue recognition principle states that Companies should recognize revenue when the revenue is realized and earned. Revenue is said to be realized,when the goods are exchanged for cash Revenue is said to be earned, when the earning process is complete, i.e if the acct rule is 12 months, after 12 months, the revenue is completely earned. The terms realizable and realized are used interchangeably. 17. What is Scheduled Revenue and Unscheduled Revenue?? Revenue is said to be scheduled for a line, if distribution records are created for all the periods corresponding to the accounting rule specified by that line item. Revenue is said to be unscheduled, if the line is associated with an accounting rule which is deferred, i.e every thing is associated with an unearned single distribution. 18. why would you post few things on deferred revenue account typically?? The following are the reasons why why you would put a particular transactions revenue on a deferred revenue and they are For ex, the collectibility of the line items like line charges, lease payments loan fees, other charges is in doubt and hence should not be considered as earned revenue until the payment is received. Hence such kind of invoice lines will be put under deferred revenue. However when the payment is received and when the payment is applied to this kind of line items, its no longer deferred revenue and will be considered as earned revenue. Receivables uses the Credit management module to check the customers credit worthiness. If the customer is not creditworthy, then the revenue corresponding to all the invoices lines for that customer will be deferred. The customers should have a PO(on their side),otherwise its not a good idea for us to put that in earned revenue, we should instead put it in a deferred revenue. 19. Are there any exceptions to the payment based revenue recognition. Yes. We have seen that application of a payment to an invoice can trigger the revenue recognition process. However if an invoice has been manually deferred then the application of receipt amount to that invoice will not trigger revenue recogniztion for that invoice. 20. WHAT are the privileges that a COLLECTOR can exercise ?? -A collector can change the due date of a transaction even after it has been posted to GL. - A collector can put a credit hold, so that no new orders are booked,but can be entered. - A collector can record as calls, any conversation that he has with thecustomers called the call log; a call should always have a contact -If your customer disagrees about the outstanding balance for an item, you can mark that item or a specific amount due as ’in dispute.’ Amounts that are in dispute appear in collections reports. Receivables does not prevent you from applying payments to disputed transactions. customer calls => actions => select transactions => save => actions => give a dispute reason and dispute amount(To remove the item from dispute put a 0 amount) - What I have seen is that you can select actions either directly from the customer calls form or select a specific trx, then choose the actions function. - A collector can use the scheduler window to "Complete" a call. Completing a call means that issue is closed. Disputes cannot be seen in the customer calls window. - He can record the customer correspondences which are typically, printing account statements printing dunning letters making customer calls. - View customer balances by summary,detail, by aging buckets - He can see dunning history in the collections workbench. 21. What are the two methods of dunning letter generation. The two methods are "days overdue" method and "staged dunning" method. days overdue : if a invoice is due by 10 to 20 days, first dunning letter will be sent, and if it is due by 20 to 30 days, second dunning letter is sent etc. staged dunning : if a invoice is picked by Dunning letter generate program ,then its dunning level goes up 1. And if the dunning level is say between 1 and 5, then first dunning letter will be sent etc. Usually once a dunning level is incremented, the program will wait for a certain days, before it increments the level for an item. 22. What is simple flow of Dunning program. Dunning letter generate program runs probably once in a month. The mandatory parameters it takes are letter sets from and to. -- For each letter set in the range From to To, it will find out all the customers that are tied to that particular letter set. Each customer is tied to a dunning letter set thru the profile. -- For each such customer it will check to see if any items are due and generate dunning letters appropriately. -- If you specify a customer name in the parameter as well, then it will just narrow down the search only for that customer name. 23. What is a statement cycle and statement site. Usually each customer will have multiple sites,with each site having a use or business purposes like bill-to,ship-to etc or there could be multiple bill-to sites. If a statement site is not specified, each customer site is sent a letter otherwise only that site is sent. A statement cycle is like a calendar where you specify the date on which you want to send the statement periodically. 24. What does a receipt class or a payment method say ? All customer payments of a particular payment type like credit card or bank account will go to a corresponding internal remittance bank account. For ex, All customer credit card payments should go to bank of america account one. All customer bank account payments should go to bank of america account two. 25. What is a prepayment ? A prepayment can be defined as a payment even before the goods or services are delivered, or its a payment even before an invoice is sent to the customer. Ex : downpayment; prepayment for consulting services. 26. What are cross currency receipts ?? A cross currency receipt is one,where a receipt of say GBP is used to pay the invoice of USD. AR handles this by posting the following difference to a gain/loss account receipt amount in func curr (at receipt date) - invoice amount in func curr(at invoice date) = foreign exchange gain or loss. 27. What are receipts at risk. The receipts for this risk which have not cleared the bank. when seeing the customer balance, we can choose to include/not include the receipts at risk. 28. Explain how the revenue entries are for an invoice will bill in advance. /*As mentioned earlier, if the invoicing rule is not specified, then you cannot specify the accounting rule. If the invoicing rule is "Bill in Advance" then you can specify any accounting rule, and the Unearned Revenue(UER) account will be hit ,when the revenue recognition program runs. If the invoicing rule is "Bill in Arrears" then you can specify any accounting rule, and the Unbilled Receivables(UBR) account will be hit ,when the revenue recognition program runs. Let us briefly understand how the accounting entries look like if we specify bill in advance and how Unearned Revenue entries will be : For example, a invoice was created on May 1 of USD 1200, entries will be 1-May-08: Receivables Dr 1200 Unearned Revenue Cr 1200 1-May-08: Unearned Revenue Dr 120 Revenue Cr 120 1-Jun-08: Unearned Revenue Dr 120 Revenue Cr 120 This way at the end of the 10 months, there will be "0" balance in the Unearned Revenue A/C and the Revenue A/C will be credited every month for equal amount and finally the total amount will be in revenue. */ 29. Explain how the revenue entries are for an invoice with bill in arrears. You can use this invoicing rule to recognize receivable (remember receivable not revenue) at the end of the revenue recognition schedule. Let us explain this with an example of an invoice with different invoicing rules, Invoice : $2000 Invoicing Rule : Bill in Advance Accounting Rule : 10 Month Invoice date : 10-JAN-2008; Payment term : Net 15 Due date : 25-JAN-2008 ----------------- Invoice : $2000 Invoicing Rule : Bill in Arrears Accounting Rule : 10 Month Invoice creation date = 10-JAN-2008; Payment term : Net 15 Invoice date is changed to 10-NOV-2008; Due date : 25-NOV-2008 (see the due date is 10 months + net 15) Hence if you see above, the invoice is having an invoice date as 10-NOV-2008, even though the invoice creation date was 10-JAN-2008. Now when the revenue recognition program completes, the account that is hit here is Unbilled Receivables (instead of unearned revenue),otherwise eveything remains the same. And to apply the same ex, we will have the accounting entries as, */ For example, a invoice was created on May 1 of USD 1200, entries will be 1-May-08: Revenue Cr 120 Unbilled Receivables Cr 1200 1-May-08: Unbilled Receivables Dr 120 Revenue Cr 120 1-Jun-08: Unbilled Receivables Dr 120 Revenue Cr 120 1-Feb-09: Unbilled Receivables Dr 120 Receivable Cr 1200 Revenue Cr 120 Unbilled Receivables cR 1200 This way at the end of the 10 months, there will be "0" balance in the Unbilled Receivables A/C and the Revenue A/C will be credited every month for equal amount and finally the total amount will be in revenue. */ 30. What is the most important point in the Receipts functionality. EACH SPECIFIC CUSTOMER PAYMENT METHOD IS ASSOCIATED WITH A SPECIFIC REMITTANCE BANK ACCOUNT. 31. What is the most important concept while defining the receipt classes, payment methods ? Firstly the three important components are Definition of Receipt classes, Payment methods Definition of banks, bank accounts. Definition of transactions which uses the above. Now the most important concept is ,again EACH SPECIFIC CUSTOMER PAYMENT METHOD IS ASSOCIATED WITH A SPECIFIC REMITTANCE BANK ACCOUNT. For ex; let us say customers use the credit cards to pay their invoices and let us say they use visa card and discover card. Then we can define a payment method as say DISCOVER CARD PAYMENT => all payments from DISCOVER should go to BOFA remittance account 154245. VISA CARD PAYMENT => all payments from VISA should go to BOFA remittance account 154245. 32). Does the dunning letter print for each due item,or per customer ?? Dunning letter is generated per one debit item. If a customer has 2 due items; the system prints two dunning letters. This makes sense as those two items might be under two different buckets. 33). What are late charges ?? late charges : Late charge functionality is not available in 11.5.10.2. That functionality is available only in R12. Basically think like this. If the customer pays early ,then he might get a discount (if the payment term is say net 15,5% discount). However if the customer pays late, then he might get charged. This will happen at the time of receipt application,just like the case of applying a discount. The system creates another line of type "CHARGE" if the invoice is due at the time of application. Autoinvoice also handles the late charges,however there are certain rules that need to be applied. That is certain attributes need to set properly and certain columns should be left null. The documentation should provide these details. You can set at the invoice header level (more tab) ,whether this invoice will have late charges. if yes, then the system will go look at the customer profile and apply the late charges. 34). What is an item in dispute ? Sometimes customer calls the company and disagrees with the invoice amount or something, then the collector can record that particular item as in dispute. He does that in customer calls form. 35). What are deductions and Claims ? Deductions are a functionality that is existing only in R12. In response to an invoice, a customer can make a short payment, which means the amount is less than the invoice amount, which could be because of the promotional deals, short shipments ,damages etc. OR he could make an over payment as well. If the remittance advice does not supply you with enough details like a promo code etc, AR lets you create a claim by specifying an amount in the claim feild for this deduction. The AR lets you interact with the Trade management to deal with these deductions. 36) can we import bank statements thru lockbox, and if so how? Not sure. However we can import the lockbox files thru the bank statement loader program which comes with the Cash management module. for bank account refund PAYMENT method should be there bank account details should be there credit memo approval limits should be there.
Labels: Oracle Receivables (AR) No comments:
Post a Comment
Newer Post Older Post Home Subscribe to: Post Comments (Atom)About Me
Balaji Piramala View my complete profileLabels
- Useful Queries (9)
- Tips & Tricks (7)
- Code Snippets (6)
- Oracle Foundation (FND) (6)
- Oracle Forms (5)
- APIs (3)
- Oracle Inventory (INV) (3)
- Oracle Receivables (AR) (3)
- Oracle Payables (AP) (2)
- BI Publisher (1)
- Oracle Order Management (OM) (1)
- Oracle Projects (PA) (1)
- Oracle Purchasing (PO) (1)
- Oracle Workflow (WF) (1)
- TCA (1)
Search This Blog
Blog Archive
- ► 2015 (2)
- ► January (2)
Tag » Ar_cash_receipts_all Status Meaning
-
TABLE - AR.AR_CASH_RECEIPTS_ALL
-
AR_CASH_RECEIPTS_ALL.STATUS In Oracle APPS
-
AR_CASH_RECEIPTS_ALL - Oracle Help Center
-
Does Anyone Have An Idea How To Query To Find Receipts In AR ...
-
AR_Receipt_Final_Qry - Oracle Concepts For You
-
AR Cash Receipt Query - Oracle APPS Consultancy
-
Ar_receivable_applications_all - ERP Great
-
Blitz Report™ - AR Unapplied Receipts Register - Enginatics
-
Oracle Receipt Batch Unapplied And Unidentified Amounts
-
CSB Related Table Info And Related Table Status & Queries - Doyensys
-
Oracle EBS Commonly Used Data Table - Katastros
-
Oracle Receivables : Useful AR Tables Informations
-
Some Links And Tables Of Oracle Apps Modules
-
Oracle Receivables(AR) Tables Information