Ram appszone

Wednesday, 3 September 2014

General Ledger Module (GL) Explanation (General Ledger Useful Information)

GL Module USEFUL INFORMATION

/* HOW THE GL ACCOUNTS ARE SET UP STEP BY STEP :

Let us start from our basic accounting equation again i.e
Assets = Liabilities + Owners Equity + Revenues - Expenses.
As we said before , we define an accounting structure consisting of segments.
And then we define the values for each segment of this flexfield.

Define the Accounting Flexfield Segments Structure and Freeze it. At this time
of defining each segment,you will also set
what is the balancing segment, cost center/dept/BU segment, natural
account segment,intercompany account segment etc for each account
segment. This structure is also called as the Chart of Accounts Structure(COA)
Define the values for each segment of the above flexfield structure. Usually value
sets are defined for each segment and attached to each segment. Any new values that
you define for a particular segment will also go into that value set.
At this point of time, also set the attribute values like
Allow Posting, Allow budgeting etc, for each specific account segment value.
Define the security and cross-validation rules.
Define the account combinations. You have to do this manually (i.e you enter
these manually in the screen). While you do this, the system checks for the
security and cross validation rules. while you are doing this ,the system will
automatically qualify each account as the asset,liability etc based on
the natural account setting. The other option for this is to use the convenience
of WebADI. Using which we can define layout and then using that layout define
the combinations in a spreadsheet and then upload those combinations using the
ADI. This is the most common way of creating the combination.
Define the Currencies(rates etc)
Define the Calendar (after defining period types etc),i.e thru
setups => financials => calendars => accounting.
Define Now the Set of Books (using the above ,Chart Of Accounts, Currency and the
Calendar).While defining the SOB,set the proper values of the different tabs.
*/
select * from gl_sets_of_books
where set_of_books_id = fnd_profile.value('GL_SET_OF_BKS_ID')

Usually an accounting segment would have as structure like
Company | Dept | Account | Line1 | Line2.

/*Balancing Segment : When we set up the account, usually we mention what is the
balancing segment. What this means is that any account up to that level should have
a credit ,debit balances tallied. In this above example typically company is 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.
*/
/*Typically what happens is that, we define all the leaf level account values
and start defining the parent values or rollup groups (You can create new rollup
groups or edit existing rollup groups using the Account Hierarchy Manager.
Rollup groups are groups of parent segment values you use to create summary accounts)

What I have noticed is that, only the leaf level account values
are having the Allow Posting set to Yes. Each account value is associated
with some attributes,which you can see when you click on the Qualifier field.
These attributes are Allow Budgets, Allow Posting, Control Account
etc. You can set these attributes both at the account segment values level
or at the combinations level. Typically if you change the attributes
like enabled, begin date etc,we can propagate by running the
"Segment Value Inheritance Program".
For ex, if we find that a particular account segment value is no longer
required, then we can disable it, or change the end date. After this if
we run the above program, then it will inactivate all the code combinations
having that account segment value (as long as the preserved flag is not
checked for that particular combination). In either case, going forward no
combination will be generated with the account segment having that particular value.
*/

/*Undefined Department :Many times you see that there is an undefined dept,
typically the value 000 ?
An undefined department will consist of a statistical value corresponding
to all the departments. for ex we can define a statistical journal which
consists of the square footage of all the departments.
*/

-- CONCEPT OF POPULATING ACCOUNTING COMBINATIONS :
/*The first thing we might feel is after defining the set of books, accounting
structures & segments, cross validation ,why can't the system just go ahead
and define all possible combinations of values for the accounting structure.
That is for ex, let us say we have a 10 segment accounting structure and you
have defined few cross validation rules for each segment which is part of the
setup. Now if the system goes ahead creates the accounting combinations ,by
following the rules, then it would be a mind-boggling number of records that
would be generated. Now in our case, let us say if each segment has 200 values
and with 10 segments it would be
200 * 200 * 200 * 200 * 200 * 200 * 200 * 200 * 200 * 200

And that would be billions of records in this table.*/

select count(*) from gl_code_combinations

/*And for this reason of huge volume of records in this table, we use a
combination of two steps.
Firstly the functional experts will decide whatever is required in a
spreadsheet and use ADI/WebADI to upload those accounting combinations
from the spreadsheet into the system. And if you need anything else
the "Dynamic Insert" option of flexfield definition will allow them to
add a new combination. So as long as you dont enter any combination
which violates a cross-validation rule, you can enter a new accounting
combination. Hence to summarize, the combination of these two steps,
should work for every one,
ADI/WebADI to upload the initial accounts.
Dynamic Insert Feature to create new accounts.
The above should make it clear the concept about GL code combinations.

-- ALL ABOUT JOURNALS :

/* Most journal batches created are of type monetary, i.e, the control check of
debit/credit must balance. However for statistical journals,they need not balance.
A Journal batch always corresponds to one period only. All journal entries in
a batch must share the same period. You can create journal directly with out a
batch,but the system automatically assigns a Journal Batch behind the scenes.
You can create journals in(open or future enterable,but you can only post in
open periods. what is the functional meaning of future-enterable period?
If the Journal approval is enabled for a set of books,and if the journal source
is enabled for approval, then these journal batches need to be approved,
before they can be posted.
If you go to the Journal sources form, then you will see that for all the
feeder systems, the journal approval is not checked typically.
However for Manual(ie. those journal that are entered in GL) will require approval.
Once it is submitted, it will go thru the management approval hierarchy based on
the profile option(Journals: Find Approver Method).

-- Types of Journals. (actual, encumbrance,skeleton, statistical)
There are different kinds of journals and each journal,as we know will have
a source ,category and a balance type.
In GL, we can create two kinds of journals,
Manual => (source,balance type= Manual, Actual)
Encumbrance => (source,balance type = Encumbrance, Encumbrance)

Now from the UOM point of view, we can also have what are called as Monetary
and Statistical Journals.
If the currency of the Journal is USD(or any other currency), then it is
called Monetary Journals. If the currency value is set as STAT,
then it is called Statistical journal (Statistical Journal includes units,
like headcount, number of units sold etc).
I believe the Inventory will post the statistical journals to GL.
*/
select je_batch_id, name,status,default_period_name,posted_date,running_total_dr,
running_total_cr, org_id,set_of_books_id,description
from gl_je_batches

select je_batch_id,je_header_id, je_source source,period_name ,je_category ,status
from gl_je_headers
where je_batch_id = 283856

/* Recurring Journals :
Recurring Journals are typically used to create Accruals, Depreciation Charges ,
monthly rents, journals etc.

There are three types of Recurring Journals
Skeleton Recurring Journal
Standard Recurring Journal
Recurring Journal Formula Entry.

To create any kind of the recurring journals mentioned above,it involves two steps
and they are
Define Recurring Journal
Generate Recurring Journals.

---------
SKELETON RECURRING JOURNAL : To create a skeleton Recurring Journal,go to the
Define Recurring journal formula window,create a batch name,journal entry name
(we can enter as many journals for a specific batch name) and then click on the lines.
Journal => Define => Recurring
In here, enter the line# and account#,but do not enter the amount underneath
(We can enter as many lines as we want for a specific journal)
*/

select * from gl_recurring_batches
where name like 'Eliminate Intercompany Profit' -- 'myrecurringbatch'

select * from gl_recurring_headers where recurring_batch_id = 147

select * from gl_recurring_lines where recurring_header_id = 233

/* So the next step is to Generate the journals, which can be done using
Journal => Define => Recurring => Generate
This will spawn a concurrent program which will generate the actual journals
i.e it will create a journal batch ,header and lines. The journal batch and
the journal name will be appended with the system date. As an ex, if the
recurring journal batch name is test, then the generated journal batch name
would be "test 12-feb-09 09:20:00" and so is the case with the journal name
as well.
Now to enter the amounts for the generated skeleton journal ,pull up that
batch and journal using the menu
Journal => Entry => Batch Find
and then enter the amounts.

Hence a skeleton
journal is one for which no amounts are entered at the definition level,so they
are the recurring journals which affect the same account every period but the
amounts are different.

-------
STANDARD RECURRING JOURNAL : To create a standard Recurring Journal,go to the
define Recurring journal formula window;
Journal => Define => Recurring
Now create a batch name, journal entry name(we
can enter as many journals for a specific batch name) and then click on the lines.
In here, enter the line# and account#, and also enter the amount underneath(make
sure that the operator is "Enter" in the below.)

Following this the generation process is same as above for the skeleton recurring
journals.

-------
RECURRING JOURNAL FORMULA: To create a Recurring Journal batch,go to the define
Recurring journal formula window, create a batch name, journal entry name(we can
enter as many journals for a specific batch name) and then click on the lines.
In here, enter the line# and account#, and you can enter a formula consisting of
different operators and amounts. The important thing is that we can even enter an
account for each operand of the formula. This table will only have data ,if
there is any formula associated with the recurring journal.

Following this the generation process is same as above for the skeleton recurring
journals.
*/

select * from gl_recurring_line_calc_rules where recurring_header_id = 147

/*An example of Creating a Recurring Journal would be like
"Reduce the Inventory Balance by 15% every Period".
In this case once we create the recurring journal batch with the right formula,
we can schedule it to generate those journals periodically. The formula for this
would be some thing like
1 Enter .15
2 * 01-000-4150-0000-000
3 * -1
What the above means is ,we are multiplying the inventory balance which is stored
in the code combination 01-000-4150-0000-000 (say $1000) with 0.15 and then
multiplying the result by -1. So the result is we are creating a journal with a
negative value of -$150 for every period.

-------------------
MassAllocations : Mass allocations are typically used when we want to distribute
the balances across the child values of a parent segment value. As an ex, let us
say we have a dept 999 which has child departments like 101,102,103,104... 110.
And let us say ,we want to distribute the department expenses among these child
departments based on their square footage.
Then we can create a massallocation batch with the appropriate formula and
generate/schedule that formula. (look at the example given in the GL user guide
and it is very clear). In here, we also mention the target and offset accounts.

So basically, the Recurring Journal formula batches and Massallocation batches
both of them have two stages of defining/generation.
Both of them can be scheduled to generate journals periodically. However the
only difference is that in the case of Massallocation,we have the facility to
give a parent segment value and the balances can be automatically distributed
among all the children once we generate them.

AutoAllocation : this is a powerful tool, where we can combine the batches of
recurring journals,mass allocations and schedule them together.
*/

-- Encumbrance Accounting and Budgetary Control :
/* First let us define what is an Encumbrance => Pre-expenditure or an estimated expenditure.

Encumbrance Accounting is set to be enabled, if you specify an account for
the below field
Set Of Books => Budgetary Control tab => Reserve for Encumbrance : 01-000-1250-0000-000(ex value)

Now we can have Encumbrance Accounting enabled, with or without Budgetary Control feature enabled.
If budgetary control is checked, then the modules like iProcurement,
iexpense, PO will be able to create encumbrances.
If budgetary control is not checked, then the modules like iProcurement,
iexpense, PO will NOT be able to create encumbrances.
So in this case we can only enter or post Manual Encumbrances

Reserving funds: Also ,if you are using the budgetary control ,then every time there
is a journal created we have the option to reserve/check the funds. Actually
if the budgetary control is enabled ,then the journal form will have the check
funds, reserve funds,change period and change currency button are present, otherwise not.
*/
gl_fund_checker_pkg => is used to reserve/unreserve funds.
-- so for that particular period,the funds are reserved,but they go out of which pool ?

/*
Create a Journal Batch and it goes into gl_je_batches.Each batch consists of a
lot of journals and a journal goes into gl_je_headers and gl_je_lines. Each
journal should have net sum equal to 0 on the credit and debit side. i.e the two
accounts should be specified, one with credit and one with debit.

Initially the status flag ='U' standing for unposted and when the journal is posted,
it is 'P'.When a batch is posted all its journal are automatically posted.
*/

/*Also for the data that is being transferred from the feeders i.e payables and
receivabes which go into the GL_INTERFACE first, there is a column which records
which system they have come from, which is user_je_source_name i.e this column
which have value "Payables", if the data is coming from Payables and so on.
*/

select status,accounting_date, date_created, user_je_category_name,
user_je_source_name,entered_dr,entered_cr,reference1,
period_name,chart_of_accounts_id,date_created_in_gl,set_of_books_id
from gl_interface

/*Actually we have been seeing that the journals are coming from the feeder
systems i.e payables and receivables and then we are running the payables and
receivables transfers to push the data over to GL(where they come into
the GL_INTERFACE table) and then we run the journal import happens to push them
over the actual GL tables. Remember that running the Journal Import will delete
the records from gl_interface and push them into actual GL tables.However sometimes
we might even enter a journal independently into the GL. then the data goes directly GL
related tables i.e batch, header and lines tables.(no transfers and imports). From
now we can run the Posting to post those journals.
*/
select je_batch_id, name,status,default_period_name,posted_date,running_total_dr,
running_total_cr, org_id,set_of_books_id,description
from gl_je_batches
where name like 'My%'

/*Or conversely,Once the Journal Import is completed by a concurrent request 6178243,
then to find which batch got created,*/
select je_batch_id, name,status,default_period_name,posted_date,running_total_dr, running_total_cr,
org_id,set_of_books_id,description
from gl_je_batches
where name like '%Receivables%6178243%'

select je_batch_id,je_header_id, status,running_total_dr, running_total_cr,
description,je_source,period_name
,je_category,je_source,status,external_reference
from gl_je_headers
where je_batch_id = 283856

select je_header_id,je_line_num,period_name,status,code_combination_id, entered_dr,
entered_cr,description,creation_date
from gl_je_lines
where je_header_id = 7075

/*GL TRANSFER : SUMMARY OR DETAIL : The data is transferred into the GL from the
feeder systems either in Detailed or Summary format.

Every time there is a GL transfer happens, the system creates a GL batch. This batch
will consist of Journal headers and for each header there will be Journal lines. In
both the cases, the batch name will be a concatenation of the feeder system name ,the
concurrent request id ,batch id etc. As an ex,if the GL transfer initiates from Receivables
thru the request id 2866725, then the batch name might look like
"AR 56520 Receivables 2866910: A 56520".

Now let us see what happens if the GL transfer happens in SUMMARY format. Then there
will be a GL batch and GL Journal header created. Typically each Journal Header will
correspond to a source and category (the source and category information is contained
only at the journal header level ,and not at the line level). So from AR to GL, we will
have multiple Journals like (Receivables, Sales Invoices), (Receivables, Credit Memos),
(Receivables, Trade Receipts) etc. Now for the Summary, for each journal header
(say Sales Invoices),there will one summarized line corresponding to each account
which is the sum of all invoice amounts.

Now let us see what happens if the GL transfer happens in DETAILED format. Then there
will be a GL batch and GL Journal header created. Typically each Journal Header will
correspond to a source and category (the source and category information is contained
only at the journal header level not at line level). So from AR to GL, we will have
multiple Journals like (Receivables, Sales Invoices), (Receivables, Credit Memos),
(Receivables, Trade Receipts) etc. The only difference is at the Journal Line level,
where for every Journal Header, there will multiple Journal lines with each line referencing
the original AR transaction. So for ex, if there are 100 invoices transferred from AR to GL
then there will be multiple records in the Journal lines table,with each line referencing
the Invoice number/id. For more detailed,listing of what reference columns refer what
id's in AR, look below. So these reference id's refer different 'id's corresponding to
different feeder systems.
Hence 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.
*/

-- GL Batches.
select * from gl_je_batches where je_batch_id = 457618

-- GL Headers.
select * from gl_je_headers where je_batch_id = 457618

-- GL Lines.
-- 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

---
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

/*So the two important things that we might notice for the batches that are created
manually and those which have come as part of the Payables or Receivables transfers
are as follows. We notice that the name column in the table for those which have
been transferred from say, Payables, will have something like
401 Payables 1694705:A 39127
where 1694705 is the concurrent request id which transferred it. Similarly there
is also a request id which consists of the request id. Note that for the journal
that have been entered manually, the request id is null.
*/

select je_batch_id ,name,request_id,status
from gl_je_batches where default_period_name ='OCT-04'

--Now once the date is posted, we find the records in

select code_combination_id,period_name,actual_flag,last_update_date,period_type,
period_year,period_num, begin_balance_dr,begin_balance_cr
from gl_balances
where period_name ='OCT-04'
order by last_update_date desc

select max(last_update_date) from gl_balances

--Queries giving the Accounting Hierarchies

--In general there will be Balance Sheet account ,Income Statement and other accounts.

select sys_connect_by_path(a.flex_value,'/'), b.parent_flex_value , a.flex_value,
a.description,a.summary_flag
from
fnd_flex_values_vl a,fnd_flex_value_children_v b
WHERE a.flex_value_set_id = b.flex_value_set_id
and a.flex_value = b.flex_value
and a.flex_value_set_id=1006786
start with a.flex_value = '10002'
connect by prior a.flex_value = b.parent_flex_value

select sys_connect_by_path(a.flex_value,'/'), b.parent_flex_value , a.flex_value,
a.description,a.summary_flag
from
fnd_flex_values_vl a , fnd_flex_value_children_v b
WHERE
a.flex_value_set_id = b.flex_value_set_id
and a.flex_value = b.flex_value
and a.flex_value_set_id=1006786
start with a.flex_value = '40000'
connect by prior a.flex_value = b.parent_flex_value

--Another similar useful query is ..................

select account_path,
decode(
substr(account_path,
instr(account_path,'/',1,1)+1,
instr(account_path,'/',1,2)-instr(account_path,'/',1,1)-1
) ,flex_value,description),
substr(account_path,
instr(account_path,'/',1,2)+1,
instr(account_path,'/',1,3)-instr(account_path,'/',1,2)-1
) second
from
(
select sys_connect_by_path(a.flex_value,'/') account_path ,
b.parent_flex_value parent_flex_value, a.flex_value flex_value,
a.description description,a.summary_flag
-- ,substr(sys_connect_by_path(a.flex_value,'/')
from
fnd_flex_values_vl a , fnd_flex_value_children_v b
WHERE
a.flex_value_set_id = b.flex_value_set_id
and a.flex_value = b.flex_value
and a.flex_value_set_id=1006786
start with a.flex_value = '10002'
connect by prior a.flex_value = b.parent_flex_value
order by 1
)

/* The GL accounting heirarchy can be viewed from the values screen in the
setup => Financials => Flexfields => Key => Values
Pick any particular account, and press the UP/DOWN buttons to navigate
up/down the tree until you reach
the root/leaf of the tree. */

select * from fnd_flex_values_vl
where flex_value = 'A1000'

/* GIS : GLOBAL INTERCOMPANY SYSTEM :

GIS Application is part of standard General Ledger Application(GL).

Intercompany transactions are those where there will be a sender and a receiver
within your own company. Each company will have subsidiaries and in a intercompany
transaction one subsidiary will act as a sender and one company as a destination.
For ex you can do a cash transfer between two subsidiaries,which is classified
as a intercompany transaction.

HENCE AN INTERCOMPANY TRANSACTION IS SOME KIND OF MONEY TRANSFER BETWEEN
TWO INTERNAL COMPANIES(like insurance,cash, stock etc).

AN INTERCOMPANY TRANSACTION IS A MUTUAL AGREED TRANSACTION BETWEEN BOTH THE
PARTIES(COMPANIES) AND THEN THEY POST IT.

/*Now as part of the GL setup, we define the companies(or legal entities) i.e
when we define a company accounting structure, one segment(usually the first
segment,which is the balancing segment) will be a legal entity segment and
we define the values also for it in the Key flex field values form.*/
setup => Financials => Key => Flexfields => Values

/*So for each of the company values , we also define the subsidiaries,using the
menu option,*/
Setup => Intercompany => subsidiaries

/*Before defining an intercompany transaction,lets talk about the GIS responsibilities.
Usually when you set up GIS, one responsibility is associated with each of your GIS
subsidiaries i.e at the responsibility level, a subsidiary can be assigned. This is
done from the profile option screen.*/

-- This table gives all subsidiaries.
SELECT *
FROM gl_iea_subsidiaries sub
WHERE sub.enabled_flag = 'Y'
-- and description like 'Sun%'
and subsidiary_id =2

/* For the profile option "Intercompany/Interfund: Subsidiary" assign the subsidiary value
created in the above step at the responsibility level.
So as an ex, if there is a company value "105", you could create a subsidiary "105999105"
and assign that subsidiary value to a responsibility "US 105 GL GIS", so that the
responsibility name indicates the company that it is assigned to.
*/

/*Generally the different kinds of Intercompany transactions that exist are ,which
can be seen from
Setup => Intercompany => Transaction Types
are,
Cash Transfer
Commission
Dividends Receivables/Payables
Insurance Premium
Service Fees (fees paid for any service rendered)
Stock In Trade (stock exchange for company merges)

Hence the following query gives the different transaction types
*/

SELECT *
FROM gl_iea_transaction_types

/*To enter the GIS transaction with in GL, you can do it from the following menu
after logging into a GL responsibility,*/
Transactions => Enter

/* Anyone logs in with that responsiblity, the sender subsidiary automatically defaults
with the corresponding subsidiary value in the transaction form.*/

/* So when we are entering the intercompany transaction, we enter the receiver,
period, transaction type, GL date, Sender information (defaults) and also the
account information below in two tabs (sender and receiver). So lets say you
have initiated a "Cash Transfer" intercompany transaction. That would show
up in the following table.

In the detail block, we also will enter the account information and clearing account
information,debit and credit balances.
For the clearing account information, the GIS system uses the GIS autoaccounting tool,
which basically constructs the code combination by using the clearing rules,which are
defined here
setup => Intercompany => AutoAccounting Rules
So the account we can enter any combination that we want but the clearing account
will built by the autoaccounting tool.
*/

select transaction_number,sending_subsidiary_id,receiving_subsidiary_id,
sender_clearing_ccid,receiver_clearing_ccid,
gl_date,transaction_id,transaction_type_id, currency_code,status,
sender_period_name,receiver_period_name,creation_date
from gl_iea_transactions
where transaction_number = 'IC0000002140'
--order by creation_date desc

select *
from gl_iea_transaction_lines
where transaction_id = 2158
--order by creation_date desc

/*The next step is "Submit"ing the transaction. Once the transaction is submitted,
a notification is sent to the receiver by the workflow.

It is important to understand that a notification can be sent to a user or to a
responsibility. Once the receiver receives the notification, he needs to use the
GIS application to approve the transaction.*/

select * from wf_notifications
where trunc(begin_date) = trunc(sysdate-1)

/* The intercompany segment shares the same value set as the balancing
segment and is used in the account combination that Oracle General
Ledger creates to balance intercompany journals. By including an
intercompany segment in your accounting flexfield, you can track the
trading companies involved in an intercompany transaction.
Every time an intercompany transaction is generated, the intercompany
segment is automatically populated with the balancing segment of the
trading partner.

/*Importing intercompany transactions.

Just as we can enter the Intercompany transactions, we can import from different
source systems or from AR.*/

select * from gl_iea_interface
/*The following steps summarize the way the global billing transactions flow
from AR to GL in Sun Microsystems.

Create a transaction and the lines. Each line will correspond to a memo
line with the appropriate operating unit.

So an invoice is created with each line being referring to one operating unit.

Sun United States
Sun United Kingdom
Sun Argentina ,etc
*/

So all the transactions are created of a particular transaction type and once it
is done, these trasactions are paid by the receipts & closed. So only closed transactions
pulled from the AR and populated into a staging table.

select * from xxsun_global_billing_stg
where customer_trx_id =
(select customer_trx_id from ra_customer_trx_all
where trx_number = '481')

/*Once they are in the staging table, the program looks at a lookup code to find out
the accounting combination that needs to be generated for the receiver transaction.
So the reverse receiver ransactions are generated and populated in the same staging
table again. Following this the data is now pumped in the GL standard interface
table with the transaction type being mapped to "Cash Transfer Credit".
So we should find data in the following interface table, */

select * from gl_iea_interface

/*At this point, we can run the standard Interface program "Import Intercompany Transactions"
to transfer the data from the interface table to the GL and can be found here */

select * from gl_iea_transactions --and lines

-- FINANCIAL STATEMENT GENERATOR - FSG:
/**********************************************************/

/* The FSG will enable you to build your own custom reports without programming. Now
just as in a typical report where in you have rows and columns,here too we need to
decide what rows and columns we need in a typical report. For this we define what
is called as a row set and a column set.

A row set is a predefined set of row definitions which can be reused across reports.
A column set is a predefined set of columns which can be reused across reports. A
column set can have a maximum of 4 columns. Oracle provides some 14 predefined column
sets such as Comparative YTD, PTD Variance etc
The row and column sets are two important building blocks of FSG. Generally the accounts
are assigned to the row definitions and the amount types are assigned to column definitions.

FSG is like an engine which runs the FSG report. Its more like a workflow engine which
runs and does some specific tasks.

-- So the sequence of steps for defining and running a FSG report are given below.
Define the rowsets and column sets from
Reports => Define => Rowset (Columnset)
Define the FSG report and specify the desired rowset and columnset that are created
in the above step.
You will run the FSG reports not from the Standard SRS,but from the menu option
Reports => Request => Financial.
(Another interest point to note here is that, the FSG report requests
information is not stored in the standard FND tables like fnd_concurrent_requests
etc,but in a separate table which is rg_report_requests. For more details look
in the Oracle apps queries script).

If you want to see the sql behind the FSG, set the profile option FSG: Message Detail
to FULL and run the request again,then the log file will have some SQL's.
(see Note:108236.1) for more details.
*/

SELECT NVL (bal.period_type, ''), NVL (bal.period_year, -1), bal.period_name,
NVL (bal.period_num, -1), NVL (bal.period_num, -1), bal.actual_flag,
DECODE (cc.template_id, NULL, 'N', 'Y'), -1,
NVL (bal.budget_version_id, -1), -1, NVL (bal.encumbrance_type_id, -1),
bal.currency_code, bal.set_of_books_id, NVL (bal.translated_flag, ''),
NVL (bal.period_net_dr, 0) - NVL (bal.period_net_cr, 0),
NVL (bal.period_net_dr, 0), NVL (bal.period_net_cr, 0),
NVL (bal.quarter_to_date_dr, 0) - NVL (bal.quarter_to_date_cr, 0),
NVL (bal.quarter_to_date_dr, 0), NVL (bal.quarter_to_date_cr, 0),
NVL (bal.begin_balance_dr, 0) - NVL (bal.begin_balance_cr, 0),
NVL (bal.begin_balance_dr, 0), NVL (bal.begin_balance_cr, 0),
NVL (bal.project_to_date_dr, 0) - NVL (bal.project_to_date_cr, 0),
NVL (bal.project_to_date_dr, 0), NVL (bal.project_to_date_cr, 0),
NVL (segment3, ''), NVL (segment1, ''), NVL (segment2, ''),
NVL (segment4, ''), NVL (segment5, '')
FROM gl_balances bal, gl_code_combinations cc
WHERE bal.code_combination_id = cc.code_combination_id
AND cc.chart_of_accounts_id = 101
AND bal.set_of_books_id = 1
AND NVL (bal.translated_flag, 'x') in ('Y', 'N', 'x')
AND NVL (bal.currency_code, '') = 'USD'
AND cc.template_id is NULL
AND ( (NVL (segment3, '') >= '4001' AND NVL (segment3, '') <= '4999')
OR (NVL (segment3, '') >= '5001' AND NVL (segment3, '') <= '5999')
OR (NVL (segment3, '') >= '6001' AND NVL (segment3, '') <= '7230')
OR (NVL (segment3, '') >= '7300' AND NVL (segment3, '') <= '7360')
OR (NVL (segment3, '') >= '7401' AND NVL (segment3, '') <= '7869')
OR (NVL (segment3, '') >= '7901' AND NVL (segment3, '') <= '7998')
OR (NVL (segment3, '') >= '8100' AND NVL (segment3, '') <= '9999')
)
AND ((NVL (segment2, '') >= '000' AND NVL (segment2, '') <= 'zzz'))
AND ((NVL (segment1, '') = '01'))
AND (( bal.period_name in ('Mar-01')
AND (( NVL (bal.period_name, '') = 'Mar-01'
AND ( (bal.actual_flag = 'B' AND bal.budget_version_id = 1087
)
OR ((bal.actual_flag = 'A'))
)
)
)
)
)

/* SO THE MOST IMPORTANT POINT to note here is that, the summary accounts are used
only in the FSG reports. As an ex, in the Vision instance we have seen that there is
a summary account created (natural account#4000) which is defined as the summation of
the leaf level accounts 4110,4120,..4190,4200 etc. Now to know the balance of this
particular account we need to run the FSG report and specify account 4000. The FSG
would then internally find out all the children corresponding to that particular
summary account ,add them up and show it as the balance of 4000. Another point is
that when we run the report with the display type as 'T',we would get the above summary
balance as describe above. If we give the display type as 'E' ,stands for Expand, then
it would print individual account balances.
*/

-- SECURITY RULES AND CROSS-VALIDATION RULES :

Let us say the accounting structure looks like
"Company,Business Unit,Dept,Account,Int Comp, ProductLine".
And while defining the depts, generally they would define even '0000' as the
department number.

/* CROSS VALIDATION RULE would be something like
"CR101 - EXPENSE ACCOUNT MUST HAVE A VALID DEPARTMENT NUMBER".
What this means is that whenever we choose an expense account number in the Account
segment of the accounting structure, we cannot choose dept values as '0000'.

Usually when defining any Cross Validation rule, we first include all the accounting
segment combination and try to exclude the few accounts that we do not need. So
in the above case, we would exclude the combinations as
*/

Type From To
Include 000.0000.0000.000000.0000.000.000000 ZZZ.ZZZZ.ZZZZ.ZZZZZZ.ZZZZ.ZZZ.ZZZZZZ
Exclude 000.0000.0000.700000.0000.000.000000 ZZZ.ZZZZ.0000.830119.ZZZZ.ZZZ.ZZZZZZ

/* So the first line, would include all the possible combinations for all the
segments. In the second line, all the combinations like
"000.0000.0000.700000.0000.000.000000" and "001.0000.0000.830110.0000.000.000000", etc.*/

/*The best way to check for the cross validation rules for a particular segment
value is to go to the cross validation rules and for that particular value
query by the value '%156%' and see the rules. Since each type
of account will have its own range, you wont find duplicate records.
OR
Even more best way is to go by the error message, let us say you are trying
to create a combination, then the system might throw an error message like
"You cannot choose this sub-detail account for this account". Then you
can query in the cross-validation rules form for this error message and
see what is wrong.
*/

/* SECURITY RULES :
A securityrule can be defined for any segment of the Accounting Structure.
A security rule defined,say for the Company segment,would be something like this =>
A user with a specific responsibility ,say R1,can enter only specific company
codes ,like say 100, 200 & 300.
If any other company codes are entered with this responsibility then the system
would throw the message,
"Company Not Valid for this responsiblity" Or this particular responsibility
cannot enter this company code. This message and description is what you
define at the time of definition of the Security Rule.

Here are the steps required to create a sample Security Rule :

Go to the security Rules from using the menu
Flexfields => Key => Security => Define

Next, Define a security Rule and enter a message and description.
In the security rule elements, include all the companies that you want to
be part of this rule.For now, let us include only one company which is
115 in from and to as well.

Having created a security rule,you need to assign responsibilities
for these companies.
Flexfields => Key => Security => Assign
The most important thing to note in the Security Rule Assign form is
that for one responsibility you will have only one record in this form.
Hence if for a particular responsiblity you need to grant say 10
companies, then you need to define that properly in the security
rule definition. Hence you will only do the assignment.

HENCE IF THERE IS A SECURITY RULE DEFINED FOR A RESPONSIBILITY THEN THAT
RULE IS ENFORCED I.E ONLY THOSE CODES ARE AVAILABLED.IF THERE IS NO
SECURITY RULE, THEN ALL CODES ARE AVAILABLE.

The intercompany segment and the balancing segment will share the security
rules.?????

*/

The basic accounting equation is
ASSETS (A) = LIABILITIES (L) + OWNERS' EQUITY (O) + REVENUE (R) - EXPENSES (E)

Try to find out from the accounting heirarchy what is the range of accounts
defined for the REVENUE and EXPENSES.
That is when a functional person defines all the accounts with security and cross
validation rules,there will be a range of numbers for the revenue and expenses.
Generally each type of account will have its own range of numbers,i.e say for
Assets, you might you might have starting from 100 to 199 and for liabilities
from 300 to 499 etc.

/*
GL Standard Reports :
The different types of GL Standard reports are
Account Analysis Reports
Trial Balance Reports
Chart Of Accounts Reports
FSG Reports.

Account Analysis report : Account analysis report will give a line level journal
details for a specific period.

--- ACCOUNT ANALYSIS REPORT QUERY.

select /*+ ORDERED
USE_NL(cc bal)
INDEX(per GL_PERIOD_STATUSES_U2)
INDEX(bal GL_BALANCES_N1) */
max (bal.period_name) period_name,
sum (NVL (bal.begin_balance_dr, 0)) begin_dr,
sum (NVL (bal.begin_balance_cr, 0)) begin_cr,
sum (NVL (bal.begin_balance_dr, 0) + NVL (bal.period_net_dr, 0)
) end_dr,
sum (NVL (bal.begin_balance_cr, 0) + NVL (bal.period_net_cr, 0)
) end_cr
FROM gl_period_statuses per, gl_code_combinations cc, gl_balances bal
WHERE cc.segment1 = '01'
AND cc.segment2 = '410'
AND cc.segment3 = '4120'
AND cc.segment4 = '0000'
AND cc.segment5 = '000'
-- AND cc.chart_of_accounts_id = :struct_num
AND per.application_id = 101
AND per.set_of_books_id = 1
-- AND per.effective_period_num ='Dec-02'
AND bal.code_combination_id = cc.code_combination_id
AND bal.period_name = per.period_name
AND bal.actual_flag ='A'
AND 1 = 1
AND bal.currency_code = 'USD'
AND bal.set_of_books_id =1
AND bal.template_id is null
AND bal.period_name ='Dec-02'
GROUP BY per.period_year, per.period_num
ORDER BY per.period_year, per.period_num

Trial Balance report will give the accumulated balance as of a specific date and
not a particular period. Balance Sheet can be obtained by running the Trial Balance report.

-- Running report for a range of accounts :

/*Actually if you want to run any report for any specific account segment, we
can run by providing the values as*/
From => 103001 To => ZZZZZZ

ZZZZZZ indicates that any 6 digit value is ok.

/* Querying a gl code combination :
The GL code combination can be seen from the GL setup responsibility
using the form*/
setup => Financials => Accounts => Combination

/*Let us say if you have a 10 segment accounting flexfield structure, then
you query by entering the value as */
110-433-9999-%-%-%-%-9999-99999999-99

/*This gives all the records which have the first segment value of 110
,second segment value of 433 and any
value for the 4th,5th segment etc.*/

/*"Chart of Accounts- Account Hierarchy" report does not give
the parent child relationship between account,it
talks about the summary account and the detail accounts.*/

/************************************************************************************/

/* ACCOUNT GENERATOR AND AUTOACCOUNTING :
Some Oracle financial applications, such as Oracle Payables and Oracle Purchasing,
use the Account Generator to create accounting combinations that record detailed
information about each transaction.
However, Oracle Receivables uses AutoAccounting to create the accounting distributions
for both manually entered or imported transactions.

Account Generator uses the Oracle workflow and is known to be slower,performance wise.
*/

-- CURRENCIES ,TRANSLATION,REVALUATION & MRC :
/* The various things in currencies are Translation, Revaluation,Conversion and using MRC.
Translation is a feature which converts your actual balances from your
functional currency to any other currency that you want.The important thing
to note here is that translation acts on the account balances and not
at the transaction level.

There are different kinds of rates that you can define ie. you can define either
daily rates ,period rates or historical rates.

Daily rates means that for every single day in the calendar, we can define
the conversion rate of the functional currency.
Typically the daily rates are used by the MRC feature of the Oracle applications.
The daily conversion rates are stored in the GL_DAILY_RATES and are defined from the menu,
Setup => currencies => Rates => Daily
*/
SELECT * FROM gl_daily_rates

/* Period rates means for every single period, we specify what is the rate that should
be used. With in period rates there are two types like Period-Average and Period-End
rates. Typically the period rates are used the Translation program.
The period rates are stored in the GL_TRANSLATION_RATES and are defined from the
menu(similarly historical rates),
Setup => currencies => Rates => Period */
SELECT * FROM gl_translation_rates

/*So the Translation program basically takes the balances from the GL_BALANCES table
for a particular period, reads the rate that needs to be applied from the GL_TRANSLATION_RATES
table,converts that balance and populates the same table again. It is important to
understand the fact that, GL_BALANCES is having columns like code_combination_id, period etc.
Now based on the type of the account ,it will choose different kind of rates like
Period rates, Historical rates. (There are different types of GL accounts like Monetary
assets,liabilities;Non-monetary assets and liabilities,Income Stmts,Owners Equity etc).

So the Translation is used when companies will have to report the revenues in a foreign
currency on a yearly basis to their parent company.If companies have to regularly report
the revenues in a foreign currency, then it is better to use the MRC feature. MRC
converts and stores the foreign currency transactions at the transaction level. If
you are using MRC, at what point does the records will get created for the other
reporting currencies. MRC is not a single setting,it is a big setup and there are a
bunch of steps that needs to be done.

After translation, can we run any of the reports in that functional currency??????

Revaluation :
As mentioned above there are different kinds of GL accounts like Income
Statement accounts, balance sheet accounts etc.
Before we talk about the revaluation program, let us briefly dwell on the
GL_BALANCES table. As mentioned before, GL_BALANCES table has important
columns like Period,code_combination_id, currency_code,begin_balance_cr,
begin_balance_dr, period_net_cr,period_net_dr etc.

So as an ex, for a specific account, 01-000-3310-0000-000 (ccid =3465) in
the same, period Dec-06, will have two records corresponding to two
different currencies.

Revaluation can be launched from the form
Currency => Revaluation

Based on the above columns, the YTD balance for any specific account is calculated as
YTD := begin_balance_dr - begin_balance_cr + period_net_dr - period_net_cr

So if the functional currency and the transaction currency is USD, then the
columns BEGIN_BALANCE_DR_BEQ, BEGIN_BALANCE_CR_BEQ
will have the same respective values as BEGIN_BALANCE_DR, BEGIN_BALANCE_CR.

However if the functional currency is USD and the transaction currency is say
CAD, then the columns BEGIN_BALANCE_DR_BEQ, BEGIN_BALANCE_CR_BEQ will have the
USD value, while the the BEGIN_BALANCE_DR, BEGIN_BALANCE_CR will have transaction
currency values. Also for such records, the translated_flag is set to R. So the
revaluation program will only the pick the records which have the translated flag
value of R and will create new journals based on the new rate (obtained from the
new rate that we choose in the revaluation form window).

So basically, the revalution program will take the value
( begin_balance_dr * new rate from the form)
and subtract it from the existing value of begin_balance_dr_beq and a journal is
created with the amount equal to this difference. This journal will have a
code_combination_id corresponding to the unrealized gain/loss account that you
specify in the Revaluation window.

(Please note that it WILL NOT update the existing begin_balance_dr_beq balance,
it will create NEW journals). The most important outcome of the revaluation
program is that,it will generate the revaluation journals corresponding to the
unrealized gain/loss account.

You can find such journals using the following queries*/
-- First get the code combination id.
select *
from gl_code_combinations --20442 corr to unrealized gain/loss account.
where 1=1 and segment1 ='01'
and segment2='740'
and segment3='7846'
and segment4='0000'
and segment5='000'
and chart_of_accounts_id = 101

-- the new unposted journal created is given below
select * from gl_je_lines
where code_combination_id = 20442

-- Now this line can be posted so that it reflects on the gl balances.

/* So the difference between the Translation and Revaluation is that in Translation
it will create additional lines in gl_balances. Basically what it does is that
,for a particular period, for each code combination id, it will take the
begin balances, multiply it by the translation rate and create new gl_balances
lines. Remember translation works on the functional currency records only and
it converts into a different currency balance.

Whereas Revaluation ,basically works on foreign currency balances and create
new journals,it does not update gl_balances. Hence there is an additional
step of posting once the revaluation process completes.Usually Revaluation is
a scheduled concurrent process which is run typically after a period-end and
once it is completed, posting takes place.

Ex of Revaluation could be for goodwill valuation or for currency

if the revaluation journals are having the 0 value, then possible issue
could be exchange rates are not available for that particular transaction
or for that particular period.

Usually the revaluation program will run,lets say for every month and for all
the transactions/receipts which fall with in that particular account range
will be revalued and the corresponding revaluation journals are created.
Similarly for the next month when the revaluation program is run, there are
two options,where it might reverse the earlier journal and create new journals.
Or It might create new differential journals.
*/

/*You can use the profile option, GL Income Statement Accounts Revaluation
Rule, to specify whether you want to revalue income statement accounts
using period-to-date (PTD) or year-to-date (Y-T-D) balances. If you choose
to revalue PTD balances for income statement accounts, the program continues
to appropriately revalue YTD balances for balance sheet accounts.

--Budgetary Control :
You set the budgetary control at the set of books level. If the budgetary
control is set, then you can check whether the funds are available or not
before we actually save the transaction.

----
valid account segment combinations and the cross validation rules to be applied.?????????????
---
Any company can have multiple companies, these all are classified under legal entities.
--

Template_id : by the also write about the template id, it has to do with the summary account or not.

------------------
CONTROL ACCOUNT : what is a control account : Typically in a system, we want
certain accounts to be controlled, ie. we don't want the users not to enter
any journals directly in GL, but rather any transaction against them should
come from the subledgers. These kind of accounts are called control accounts.

Note 1050920.6 How to correct misclassified account in GL.

Posting GL journals from a different operating unit but same
SOBs : the org_id is set at the gl_batches,but still you can post any journals
corresponding to a different operating unit? interesting.

---
For example, suppose you enter journals every period that adjust
your budget balances for subsequent periods.
--
IMPORTANT IMPORTANT :
See what happens when you do posting. When you post, the summary accounts get updated.
It is important to note that out of the tables, gl_je_batches, gl_je_headers,
gl_je_lines only the gl_je_lines table will have the code_combination_id. gl_balances
table will have period_name,ccid, period balances etc, it does not talk anything about
the source, category etc,but it does specify the balance_type(i.e actual, budget or
encumbrance). Hence when you post in GL, the summary balances will get updated.
---
-- Summary templates are used in account inquiry and FSG reports.
If you do not use FSG reports then parents really won't do much for you.

--

Journal Reversals :

To mark a particular journal for reversal,do this..
Open up the journal and in the reverse area,enter a reversal period,
and that journal is said to be marked for reversal.

You could reverse a journal from 3 ways,

In the Enter Journal Window, using the Reverse button.
Here the reversal period is automatically defaulted if in the
journal reversal criteria is specified for that particular
category,otherwise it will be null.

Using the Reverse Journals window, from the menu item,
Journals => Generate => Reversal.
Here you could see all the journals marked for reversal
in the above step and choose to reverse any one you want.

Manually run the batch Program, "Program - Automatic Reversal",
which will basically pick all the journals marked for reversal
and all the journals which are selected for reversal in the
second step above.

Automatic Journal Reversal :
----------------------------

Automatic Journal reversal means,when a new period is opened
the journal reversal happens for all the journals belonging to
the categories specified in the Journal reversal criteria
window automatically. Basically it runs the program mentioned
above i.e "Program - Automatic Reversal" automatically.

Not all the categories are specified as Autoreverse
in the Journal Reversal criteria window are marked as Autoreverse, only specific
categories namely Accrual, budget etc.

Hence once the period is open,
If the profile option GL: Launch AutoReversal After Period Open"
is set, then
The system checks all the categories in Journal reversal criteria window,
which are marked as Autoreverse,
then for all such categories, it will pick all the journals
whose balance type is Actual,
which are posted, &
whose reversal period is Open or Future Enterable.

The profile option GL: Launch AutoReversal After Period Open tells you
whether "Program - Automatic Reversal" should be launched after
the period is open or not. Basically it is giving another control
for running that program.

AutoPost in Journal Reversal Criteria :

In the Journal Reversal criteria, there is another flag indicating
whether the automatica reversal journals that are generated will
need to be automatically posted immediately after the generation.

Autopost Program and Autopost Criteria Set.
-------------------------------------------
Basically Autopost is a concurrent program which will take the Autopost
criteria set as a parameter. So lets talk more about Autopost Criteria set.

Autopost Criteria set allows you to define a set of Sources and categories
of journal batches.
You can also say between which dates you want the journals to be
picked.i.e basically you are giving the from date and the to date.
In what sequence ,the posting of the journal categories should be
run is also specified.
Finally you can even schedule the run of Autopost program from the
Autopost Criteria set window.

Important Point : If you are creating a journal, and if you want it to be
reversed, then the automatic reversal criteria would have been there
and so would default in the journal window and hence would be picked
by the Autoreverse Journal program. It is important to note that the
autoreverse program only picks journals which are marked for reverse.

----
Account alias is a feature, where you can give a name to an account or a
range of account values. for ex, you can name the some thing like
totalexpenses to range of accounts between "01-000-1200-0000-000"
to "01-000-1500-0000-000".

----

Accruals :
We know that the two main accounts that get updated in Accounts payables
when an invoice is created are LIABILITY(and EXPENSE).

The following are the general rules regarding AP (expense) accruals:
Accounts payable accruals should be made for items where a good or
service has been received in the current fiscal year but will not be
paid for prior to year-end. This includes items for which an invoice
has been received but not paid, as well as items for which no invoice has
yet been received. If no invoice has been received, then the department
should process the accrual based either upon the known cost or
an estimated cost if one can reasonably be predicted. Any known costs
that are for a minimum of $1000 must be accrued. It is preferable
that items less than $1000 also be accrued, but it is not mandatory.
Departments should not delay processing these expenses because of
lack of funding. Departments should consult with their financial office
if there is a funding issue.

Accrual account get created at the time of receiving the PO. The
following account gets created at the time of receiving

Receiving A/c Dr
AP accrual Account Cr

Inventory Dr
Receiving Account Cr

Later AP invoice is created and matched.

AP Accurual Acount Dr
Trade Payables Cr

You need create receipt first and invoice later. If you created invoice and
match to po the invoice willbe on hold as no receipts are created.

-- Cash Clearing Process

Cash clearing accounting for payments is a two-step process. It focuses on the
use of a special cash-clearing control account to help provide an accurate cash
on hand balance by including the effect of payment transactions that have been
issued, but have not yet cleared the bank.

Pending and Clearing accounts are used as a temporary depository of funds until a
clear designation for the funds can be determined within the University general ledger.
Effective cash management and cash handling mandates the need for pending and
clearing accounts to avoid misplacing funds and to facilitate the management review
and follow up process for these deposits. Issues associated with the assignment
of a pending and clearing account to a deposit include the identification and
establishment of a new account number, matching a vendor credit to a specific
transaction, or development of an agreement associated with the deposit.

-- Petty Cash Fund

Businesses often need small amounts discretionary funds in the form of cash
known as petty cash for expenditures where it is not practical to
make the disbursement by check.

The most common way of accounting for these expenditures is to use the
imprest system. The initial fund would be created by issuing a check
for the desired amount. Usually $100 would be sufficient for most small
business needs, however larger businesses may have several
thousand dollars in discretionary funds available as petty cash. The
entry for this initial fund would be to debit Petty Cash and credit cash.

As expenditures are made, the custodian of the fund will reimburse employees
and secure a petty cash voucher in return. At any given time the total of
cash on hand plus reimbursed vouchers must equal the original fund.

When the fund gets low the custodian submits the vouchers for reimbursement.
Assuming the vouchers add up to $80 and that the majority of expenditures
were for office supplies, an $80 check is issued and an $80 debit towards
office expenses is marked. Once the check is cashed, the custodian has cash
at the original amount.

Oversight of petty cash is important because of the potential for abuse.
Examples of petty cash controls include a limit (such as 10% of the
total fund) on disbursements and monthly audits by someone other than
the custodian. Use of petty cash is sufficiently widespread that
vouchers for use in reimbursement are available at any office supply store.

-- Suspense Account

In accountancy, a suspense account is an account used temporarily to carry
doubtful receipts and disbursements or discrepancies pending their analysis
and permanent classification.

It can be a repository for dollar transactions (cash receipts, cash
disbursements & journal entries) entered with invalid account numbers.
The account specified may not exist, or it may be deleted/frozen. If one of
these conditions exist, the transaction should be directed to a suspense
account.

--AR to GL Reconciliation:
------------------------
We have seen that within AR, we have multiple methods of reconciliation.
That is we can run the AR Reconciliation report which tells us that
whether AR reconciles by itself. However there are ways to reconcile
the data between AR to GL.

At verisign,the reconciliation process is striped by companies.

Typically this is what has been in Verisign for the AR to GL reconciliation.

Run the account analysis report for all the known required accounts. This
includes the revalution or foreign currency as well.

now run the aging report.

These are the two main reports whose results are brought on to the
spreadsheet and tallied for AR to GL.

-- HOW TO CALCULATE REVENUE FROM BACKEND FOR A COMPANY.

--
CREATE TABLE temp_gl_data AS
SELECT /*+ index(rctld ra_cust_trx_line_gl_dist_n2) */
rcta.trx_number,rcta.customer_trx_id, rcta.org_id
,rctld.gl_date,rctla.customer_trx_line_id
, rctld.set_of_books_id,rctld.amount,rctld.acctd_amount
,rcta.invoice_currency_code,rctla.unit_selling_price,rctla.quantity_invoiced
FROM ra_customer_trx_all rcta
,ra_customer_trx_lines_all rctla
,ra_cust_trx_line_gl_dist_all rctld
WHERE rcta.customer_trx_id = rctla.customer_trx_id
AND rctla.customer_trx_line_id = rctld.customer_trx_line_id
AND rctld.gl_date >='01-JAN-2008' AND rctld.gl_date <='31-MAR-2008'
AND rctld.account_class ='REV'

-- create indexes on the above table to make performance better.

--
SELECT conversion_rate, conversion_date, conversion_type, from_currency,
to_currency
FROM gl_daily_rates
WHERE to_currency = 'USD'
AND TRUNC (conversion_date) = TRUNC (SYSDATE)
AND from_currency = 'JPY'
AND conversion_type = 'Spot'

--
SELECT ROUND (SUM (amount)) amt, invoice_currency_code
FROM temp_gl_data a
WHERE a.invoice_currency_code = 'USD'
GROUP BY invoice_currency_code
UNION ALL
SELECT ROUND (SUM (amount * conversion_rate)), invoice_currency_code
FROM temp_gl_data a, gl_daily_rates glr
WHERE a.invoice_currency_code = glr.from_currency
AND a.invoice_currency_code <> 'USD'
AND to_currency = 'USD'
AND TRUNC (conversion_date) = TRUNC (SYSDATE)
AND conversion_type = 'Spot'
GROUP BY invoice_currency_code

--
SELECT SUM (amt)
FROM (SELECT ROUND (SUM (amount * conversion_rate)) amt,
invoice_currency_code
FROM temp_gl_data a, gl_daily_rates glr
WHERE a.invoice_currency_code = glr.from_currency
AND a.invoice_currency_code <> 'USD'
AND to_currency = 'USD'
AND TRUNC (conversion_date) = TRUNC (SYSDATE)
AND conversion_type = 'Spot'
GROUP BY invoice_currency_code)
/*

Average Balance Processing :
----------------------------

Average Balance Processing is a feature in GL ,which you set at the Set of Books/Ledger level.
When you enable Average Balance Processing, you are asking the system for generating
to-date balances on a daily basis for each particular account, i.e you are asking
the system to generate the balances like
PTD(Period To Date)
QTD (Quarter To Date)
YTD (year to Date)

As an ex, consider an account alias A (01-000-1110-000-0000), and consider
the following table,

Activity | End of day | Aggregate | Avg Balance
-------------------------------------------------
Day 1 1000 1000 1000 1000
Day 2 100 1100 2100 1050
Day 3 10 1110 3210 2103

Each set of books, which has Average balance Processing enabled ,will
have with it associated a Transaction calendar. Typically a transaction
calendar specifies what are all the business days that you do the
business in. For ex, you could do business only on the Mon Thru friday
and not on the weekends and hence you dont want the To-date balances
to be transferred on the weekends.

_______________________________________________________

GL Questionnaire :

What are the different kinds of calendars in GL.
Accounting Calendar,
4-4-5 calendar (means for every quarter u will have 4weeks,4 weeks,5weeks months )
Transaction Calendar.

What is Trial Balance and Balance Sheet ??
You run the trial balance ,before you prepare the balance sheet. You call it
the trial balance because you first see what are all the balances and based on
that we come up with the adjustments which need to be made. And then finally
we use the balance sheet.

What is the difference between Primary Functional Currency, Reporting functional
currency and transactional currency?
For a Sun UK, Primary Functional Currency = GBP
Reporting functional Currency = USD
Transactional currency = YEN : if the transaction with a japanese order.

INTERCOMPANY STUFF
------------------

Intercompany Journals :
Generally when you create a journal you specify two accounts one debit
and credit which balance out each other. However the Company segment will
consist of the same value. That is both the journal lines will point to
the same segment, ex of that is
Cr 01-0000-1110-000-00 $500
Dr 01-0000-1210-000-00 $500

However in an intercompany journal, the two journal lines will point to
two different accounts. i.e the balancing out lines will have different
company values. This is called Intercompany Journals.
Cr 01-0000-1110-000-00 $500
Dr 02-0000-1210-000-00 $500

When you create an intercompany transaction, the General Ledger system will
automatically create the balancing entries. When it creates the balancing
entries it will look at the Intercompany Account setup and based on that
accordingly it will create the balancing entries. More details below.

---

When you are using the intercompany transctions, it is important to understand that
in your COA, you can have the same segment qualified as both balancing and intercompany
segment(usually the first segment)
Or
you can have two different segments for balancing segment and intercompany segment.

Use the Intercompany segment to track the intercompany transactions i.e qualify a
segment as intercompany so that you could track the intercompany ransactions.

Intercompany transactions are divided into two categories.
Intercompany transactions for multiple companies using single set of books
Intercompany transactions for multiple companies using multiple set of books

Some of the pre-requisistes are :

Ensure that you classify the company segment as the balancing segment. This
ensures that each company is always in balance which makes it easier
for youto maintain and report on multiple companies.

Ensure that the Balance Intercompany journals is checked in your set of books.

INTERCOMPANY ACCOUNTING BETWEEN COMPANIES USING SINGLE SET OF BOOKS.
--------------------------------------------------------------------

There are basically 5 different ways you can do the intercompany balancing.
Standard Intercompany Balancing.
Enhanced Intercompany Balancing.
Intercompany Segment Balancing.
Enhanced Intercompany Balancing(Many to Many Companies)
Clearing Companies.

1. Standard Intercompany Balancing :
a) No separate segment assigned for Intercompany segment. The company segment is
used as an intercompany segment as well here.

b) Create a intercompany account in the setup here,
Setup => Accounts => Intercompany
When you create an intercompany account in setup, you are actually creating a
intercompany template for a particular source and category.

c)In the Clearing company usage Choose Many to Many and In the Default options,
choose the "Use Default Balancing Account"

d) In the Detailed block, if you just tab out of the company field, "All Other"
text will default and choose due from and due to accounts.
What this means is that if we create an intercompany transaction for any company
the GL will automatically create the balancing entries using the above defn.

2. Enhanced Intercompany Balancing :
a) No separate segment assigned for Intercompany segment. The company segment is
used as an intercompany segment as well here.

b) Create a intercompany account in the setup here,
Setup => Accounts => Intercompany
When you create an intercompany account in setup, you are actually creating a
intercompany template for a particular source and category.

c)In the Clearing company usage Choose Many to Many and In the Default options,
choose the "Use Default Balancing Account"

d) In the Detailed block,what you do here is for each company in an intercompany
transaction, you define the due from and due to accounts. If this kind of
defn, is in place, then if we create an intercompany transaction for any company
then GL will lookup the defn and automatically create the balancing entries
accordingly.

3. Intercompany Segment Balancing :
a) Here there is a separate segment for Intercompany segment. The company segment is
different and the intercompany segment is different.

b) Create an intercompany account in the setup here,
Setup => Accounts => Intercompany
When you create an intercompany account in setup, you are actually creating a
intercompany template for a particular source and category.

c)In the Clearing company usage Choose Many to Many and In the Default options,
choose the "Use Default Balancing Account"

d) In the Detailed block,what you do here is for each company in an intercompany
transaction, you define the due from and due to accounts. This method is
very similar to the Enhanced Method.
However when the balancing entries are generated, just remember that the
system generates like for company 01,the intercompany segment value will have
02 and for company 02, the intercompany segment will have value 01.

4. Enhanced Intercompany Balancing(Many to Many Companies)
a) No separate segment assigned for Intercompany segment. The company segment is
used as an intercompany segment as well here.

b) Create a intercompany account in the setup here,
Setup => Accounts => Intercompany
When you create an intercompany account in setup, you are actually creating a
intercompany template for a particular source and category.

c)In the Clearing company usage Choose "Many to Many" and in the Default options,
choose the "Use Default Balancing Account"

d) Before we go into details remember one thing about the transaction here.
Herethe transaction might be like 01 and 02 will correspond to Credit and 03
and 04 will correspond to Debit. So we have to make sure that we define all
the four companies 01,02,03,04 in the setup and the GL system will automatically
create the balancing entries accordingly when you create an intercompany transaction.

5. Clearing Companies :
Your organization can designate one company to act as an operational unit for all
subsidiary companies in the org.

a) No separate segment assigned for Intercompany segment. The company segment is
used as an intercompany segment as well here.

b) Create a intercompany account in the setup here,
Setup => Accounts => Intercompany
When you create an intercompany account in setup, you are actually creating a
intercompany template for a particular source and category.

c)In the Clearing company usage Choose "Always use the Clearing Company " and in
the Default options, choose the "Use Default clearing Company"

d) In the detailed block,just enter the due from and due to for all possible
clearing account that you will use in your transaction.
Here when we enter the intercompany transaciton, we have an option of entering
the clearing company and you can enter any company value like 01 etc, then
the system will use that value and generate automiatically the balancing entries.

6. Clearing Companies with Intercompany segment.
we will see this later ?????????????

INTERCOMPANY ACCOUNTING BETWEEN COMPANIES USING MULTIPLE SETS OF BOOKS.
-----------------------------------------------------------------------

In the above scenarios, we were talking about the intercompany transactions between
companies using single set of books,however we can also have intercompany
transaction between companies using multiple sets of books. It is important to
remember that the companies using different set of books,might have either a
different chart of accounts,
different functional currency or
different accounting calendar.

So to enter the Intercompany transactions between companies using multiple sets
of books we have two options and they are,
either enter using GIS(Global Intercompany system) or
with out using GIS.

Although Oracle recommends using GIS for such intercompany transacitons.

So lets talk about intercompany transaction with out using GIS.

What is a subsidiary :
Any subset of an organization can be defined as a subsidiary. Examples of
subsidiary are
A US company '01' Bal segment value '01'
A US cost center '110' Bal segment value '01'
Europe Regiion Bal segment value '10'

So a subsidiary is something like a company, cost center or a group of
subsidiaries as long as they are associated with a balancing segment value.

Entering Intercompany transactions using GIS :

QUESTIONNAIRE :
***************

1). Why would the GL generate the balancing entries once an IC transaction is created/posted. ??

We know that in the Chart of accounts, one segment is balancing segment.That means the
net sum of the credit and debit entries should be equal to zero for each value of that segment
in the code combination. However as we know in an
intercompany transaction, the credit and debit entries will not sum to zero for a particular
balancing segment value. In order for this to happen, the GL will look at the IC setup and create
appropriate balancing entries. So the balancing entries will create a net sum zero.

2). What are the different things to consider when upgrading the 11i accounting
data to R12 SLA entries (as there is a fundemental difference in the model between 11i and R12 ?

Date from last open transaction or
Fiscal year for which all statutory/legal audits are pending or
Any random period - say last 3-4 years etc

Oracle's default period is six periods while upgrade and it can also be updated
later on post upgrade depending on downtime etc. However since this is an important
step in the upgrade process as SLA relies on previous accounting data, we would like
to select an ideal period for us considering the requirement of accounting data, downtime etc.
Oracle provides hot patches to reduce downtime and improve performance.

Account Recievables Module (AR) Explanation (Recievables Useful Information)

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.