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.