Accounts Receivables useful 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.
I really like your writing style, great information, thankyou for posting. 신용카드 현금화
ReplyDelete