The Other Side


The Other Side

A Father was reading a magazine and his little daughter every now and then distracted him. To keep her busy, he tore one page on which was printed the map of the world. He tore it into pieces and asked her to go to her room and put them together to make the map again. He was sure she would take the whole day to get it done. But the little one came back within minutes with perfect map. When he asked how she could do it so quickly, she said, ‘Oh Dad, there is a man’s face on the other side of the paper. I made the face perfect to get the map right.”” she ran outside to play leaving the father surprised.. “

Moral : There is always the other side to whatever we experience in this world.This story indirectly teaches a lesson..whenever we come across a challenge or a puzzling situation, look at the other side…

You will be surprised to see an easy way to tackle the problem…

Always have a positive attitude in life. Even a stopped watch is right twice a day…

Few Concepts on General Ledger


Few Concepts on General Ledger

What is General Ledger?

The Oracle General Ledger is the central repository of accounting information. The main purpose of a general ledger system is to record financial activity of a company and to produce financial and management reports to help people inside and outside the organization make decisions.

 General Ledger Accounting Cycle:

  1. Open period
  2. Create/reverse journal entries
  3. Post
  4. Review
  5. Revalue
  6. Translate
  7. Consolidate
  8. Review/correct balances
  9. Run accounting reports
  10. Close accounting period

What are Set of Books?

A set of books determines the functional currency, account structure, and accounting calendar for each company or group of companies. It is replaced by the Ledger Sets in R12.

Set of Books consists of the following Three elements

  • Chart of Accounts: COA can be designed to match the Organizational Structure and dimensions of the business.
  • Currency:  GL enables to define one currency as Functional Currency and use other currencies for transactions.
  • Calendar: Calendar has to be defined to control the accounting year and its periods.

Types of Journal Entries:

Within Oracle General Ledger, you can work with the following types of journal entries:

Manual Journal Entries

The basic journal entry type is used for most accounting transactions. Examples include adjustments and reclassifications.

Reversing Journal Entries

Reversing journal entries are created by reversing an existing journal entry. You can reverse any journal entry and post it to the current or any future open accounting period.

Recurring Journal Entries

Recurring journal entries are defined once, then are repeated for each subsequent accounting period you generate. You can use recurring journal entries to define automatic consolidating and eliminating entries. Examples include intercompany debt, bad debt expense, and periodic accruals.

Mass Allocations

Mass Allocations are journal entries that utilize a single journal entry formula to allocate balances across a group of cost centers, departments, divisions or other segments. Examples include rent expense allocated by headcount or administrative costs allocated by machine labor hours.

Foreign Currency Concepts:

The three key foreign currency concepts in Oracle General Ledger are:

Conversion

Conversion refers to foreign currency transactions that are immediately converted at the time of entry to the functional currency of the set of books in which the transaction takes place.

Revaluation

Revaluation adjusts liability or asset accounts that may be materially understated or overstated at the end of a period due to a fluctuation in the exchange rate between the time the transaction was entered and the end of the period.

Translation

Translation refers to the act of restating an entire set of books or balances for a company from the functional currency to a foreign currency.

What are Financial Statement Generator Reports (FSG)?

Oracle General Ledger’s Financial Statement Generator (FSG) is a powerful and flexible tool you can use to build your own custom reports without programming. You can define custom financial reports, such as income statements and balance sheets, online with complete control over the rows, columns, and content of your report. You can control account assignments, headings, descriptions, format, and calculations in addition to the actual content. The reusable report components make building reports quick and easy. You can copy a report component from one report, make minor edits, then apply the report component to a new report without having to create a new report from scratch.

What is Applications Desktop Integrator(ADI)?

Applications Desktop Integrator combines the power of Oracle General Ledger journal entry, budgeting, and report creation, submission, publishing, and analysis within an Excel spreadsheet environment.

Journal Components:

Every journal entry in Oracle General Ledger has three components.

  • Every journal entry belongs to a batch. You create a batch of journal entries by entering a name, control total and description for the batch.
  • This step is optional. If you do not enter batch information, Oracle General Ledger automatically creates one batch for each journal entry, defaulting the name and the latest open period.
  • All journal entries in a batch share the same period.
  • Entering a batch control total and description are optional.
  • If you do not enter a batch name, you must recall the journal entry by date.
  • Batch information is stored in the GL_JE_BATCHES table.

Journal Header Information

  • The header information identifies common details for a single journal entry, such as name, effective date, source, category, currency, description, and control total.
  • Group related lines into journal entries
  • All lines in a journal entry must share the same currency and category.
  • If no journal entry-level information is entered, Oracle General Ledger assigns a default name, category, and the functional currency.
  • Header information is stored in the GL_JE_HEADERS table.

Journal Line Information

  • Journal lines specify the accounting information for the journal entry.
  • Total debits must equal total credits for a journal entry for all journal entries except budget journal entries and statistical journal entries.
  • Description for each line can be entered optionally.
  • Information for journal entry lines is stored in the GL_JE_LINES table.

Journal Posting Methods:

You have three methods to post journal batches.

Batch Posting: Navigate to the Post Journals window to post a group of journal batches.

(N) Journals > Post

Manual Posting: Select the More Actions button from either the Journals window or the Batch window to post a journal batch at the time of entry. This option is available only if the profile option Journals: Allow Posting During Journal Entry has been set to Yes.

When you post journals, Oracle General Ledger posts all journals in a batch. You cannot post individual journal entries in a batch.

(N) Journals > Enter (B) More Actions

Automatic Posting: Run the AutoPost program to post journal batches automatically based on a schedule you define.

(N) Setup > Journals > AutoPost

PL/SQL Collections


PL/SQL Collections

These are composite variables in PL/SQL and have internal components that you can treat as individual variables. You can pass these composite variables to subprograms as a parameters.

To create a collection or record variable, you first define a collection or record type, and then you declare a variable of that type.

  • In a collection, the internal components are always of the same data type, and are called elements. You access each element by its unique subscript. Lists and arrays are classic examples of collections.
  • In a record, the internal components can be of different data types, and are called fields. You access each field by its name. A record variable can hold a table row, or some columns from a table row. Each record field corresponds to a table column.

PL/SQL Collection Types:

PL/SQL has three collection types, whose characteristics are summarized below.

1] Associative array (or index-by table)

  • Number of Elements: Unbounded
  • Subscript Type: String or integer
  • Dense or Sparse: Either
  • Where Created: Only in PL/SQL block

2] Nested Table

  • Number of Elements: Unbounded
  • Subscript Type: Integer
  • Dense or Sparse: Starts dense, can become sparse
  • Where Created: Either in PL/SQL block or at schema level

3] Variable size Array (Varray)

  • Number of Elements: Bounded
  • Subscript Type: Integer
  • Dense or Sparse: Always Dense
  • Where Created: Either in PL/SQL block or at schema level

Note:

Unbounded means that, theoretically, there is no limit to the number of elements in the collection. Actually, there are limits, but they are very high.

Dense means that the collection has no gaps between elements—every element between the first and last element is defined and has a value (which can be NULL).

A collection that is created in a PL/SQL block is available only in that block. A nested table type or varray type that is created at schema level is stored in the database, and you can manipulate it with SQL statements.

A collection has only one dimension, but you can model a multidimensional collection by creating a collection whose elements are also collections.

Associative Arrays (Index-By Tables):

An associative array (also called an index-by table) is a set of key-value pairs. Each key is unique, and is used to locate the corresponding value.

DECLARE
  -- Associative array indexed by string:

     TYPE ODI_RUNS IS TABLE OF NUMBER  -- Associative array type
  INDEX BY VARCHAR2(64);

     odi_batsman_runs  ODI_RUNS;        -- Associative array variable
  i VARCHAR2(64);

  BEGIN
    -- Add new elements to associative array:

     odi_batsman_runs('Virender Sehwag')  := 7380;
     odi_batsman_runs('Ricky Ponting')    := 13082;
     odi_batsman_runs('Sachin Tendulkar') := 17629;

   -- Print associative array:

  i := odi_batsman_runs.FIRST;

  WHILE i IS NOT NULL LOOP
      DBMS_Output.PUT_LINE
  ('Total ODI Runs on Jan 2010 by ' || i || ' is ' || TO_CHAR(odi_batsman_runs(i)));
  i := odi_batsman_runs.NEXT(i);
    END LOOP;
    END;

Output:

Total ODI Runs on Jan 2010 by Ricky Ponting is 13082
Total ODI Runs on Jan 2010 by Sachin Tendulkar is 17629
Total ODI Runs on Jan 2010 by Virender Sehwag is 7380

  • Like a database table, an associative array holds a data set of arbitrary size, and you can access its elements without knowing their positions in the array.
  • An associative array does not need the disk space or network operations of a database table, but an associative array cannot be manipulated by SQL statements (such as INSERT and DELETE).
  • An associative array is intended for temporary data storage.
  • To make an associative array persistent for the life of a database session, declare the associative array (the type and the variable of that type) in a package, and assign values to its elements in the package body.

Nested Tables: 

A nested table is like a one-dimensional array with an arbitrary number of elements.

Within the database, a nested table is a column type that holds a set of values. The database stores the rows of a nested table in no particular order. When you retrieve a nested table from the database into a PL/SQL variable, the rows are given consecutive subscripts starting at 1. These subscripts give you array-like access to individual rows.

A nested table differs from an array in these important ways:

  • An array has a declared number of elements, but a nested table does not. The size of a nested table can increase dynamically.
  • An array is always dense (that is, it always has consecutive subcripts). A nested array is dense initially, but it can become sparse, because you can delete elements from it.

Variable-Size Arrays (Varrays):

A variable-size array (varray) is an item of the data type VARRAY. A varray has a maximum size, which you specify in its type definition. A varray can contain a varying number of elements, from zero (when empty) to the maximum size. A varray index has a fixed lower bound of 1 and an extensible upper bound. To access an element of a varray, you use standard subscripting syntax.

DECLARE
  TYPE nested_type IS TABLE OF VARCHAR2(30);
  TYPE varray_type IS VARRAY(5) OF INTEGER;
  v1 nested_type;
  v2 varray_type;
BEGIN
  v1 := nested_type('Shipping','Sales','Finance','Payroll');
  v2 := varray_type(1, 2, 3, 4, 5); -- Up to 5 integers
FOR i IN v1.FIRST .. v1.LAST
   LOOP
  DBMS_OUTPUT.PUT_LINE('Element #' || i || 'in the nested table = ' || v1(i));
   END LOOP;

FOR j IN v2.FIRST .. v2.LAST
   LOOP
  DBMS_OUTPUT.PUT_LINE('Element #' || j || 'in the varray = ' || v2(j));
   END LOOP;
END;

Nested tables Vs. Varrays:

  • Nested tables are unbounded and are initially dense but can become sparse through deletions. Varrays are always bounded and never sparse.
  • When stored in the database, the order and subscripts of Nested tables are not preserved while varrays keep their ordering and subscripts.
  • Nested table data is stored in a separate store table, a system-generated database table while a  varray is stored as a single object in the database.