Interview Questions Related to GL SET OF BOOKS (GL LEDGERS in R12)

1. What are the different types of value set (or) validation types? Explain each in brief?

    

The validation types are 8 they are:

SNO	Value Set (or) Validation Type	Explanation
1.	Independent	the nature of segment is independent
2.	Dependent	this segment depends on independent segment
3.	None	none is free field
4.	Pair	pair means range of segments
5.	Table	table means calculation purpose
6.	Special	special means based on conditions
7.	Translatable independent	we use it other than English language
8.	Translatable dependent	we use it other than English language

2. Can I increase/decrease the size of value set?

No, only we can increase the value set size but not decrease

3. What are the difference between KFF and DFF?

SNO

Key Flex field

Descriptive Flex field

1	This field is Intelligent Flex field and Mandatory and there are 33 KFF in Oracle finance out of which accounting structure is the one of the KFF in GL module	This flex field is optional to capture the additional information. There is no such limit
2	KFF is application Specific	DFF is form Specific
3.	Cross Validation Rules are Specific to KFF	Cross validation are not applicable to DFF
4.	Aliases are applicable to KFF	Not applicable to DFF
5.	KFF is Inbuilt (Oracle Provides)	We have to define DFF.

4. How can we identify whether DFF is allowed for a specific form or not?

The DFF is available to a form where the symbol  “ [ ] ” (square bracket) is displayed

5. Explain the difference between flex field qualifiers and segment qualifiers.

Flex Field Qualifier identifies the segment (to which it is assigned) with a specific property. There are 4 types of flexfield qualifiers that can be assigned to segments, namely,    

1. Balancing segment Qualifier
2. Cost center segment Qualifier
3. Natural account segment Qualifier
4. Intercompany segment Qualifier

è1 & 3 are mandatory flexfield qualifiers

èOne Segment can have more than one qualifier not vice versa.

   

Segment Qualifier identifies the segment values with a specific property.

There are five types of segment qualifiers assigned to Natural account segment qualifier, namely,    

1. Allow budgeting
2. Allow posting
3. Control account
4. Account type
5. Reconciliation flag

 Other then above flexfield qualifier others show only two segment qualifiers i.e.

1. Allow budgeting
2. Allow posting

6. What are the components of set of books?

The components of set of books are 3C’s + 6 mandatory accounts + future period

     

The 3c’s are

1. Chart of accounts
2. Calendar
3. Currency

The 6 mandatory accounts are

1. Retained earnings
2. Net income
3. Suspense account
4. Translation adjustment account
5. Rounding difference
6. Reserve for encumbrance

                 And Future period  

7. What are the different types of set of books?

Two types of set of books

1. Primary set of books
2. Reporting set of books  

8. What are different Year types?

Two types of Year types

1. Calendar year
2. Fiscal year

9. What are the different statuses of accounting periods?

The statuses of accounting periods are

1. Open
2. Closed
3. Permanently Closed
4. Future Enterable
5. Never Open

10. Why does an accounting period does not appear in the accounting periods list of values on the journal entry form?

The periods which have the status of closed, permanently closed and/or never open will not appear in accounting periods list of values on the Journal entry form.

11. Whether is it allowed for entering negative amount in journal lines?

Yes, it is possible but after posting it will be shown in brackets “< >”

12. Is it allowed to reverse, a reversed journal entry?

Yes, it is allowed to reverse journal entry. We can reverse a Journal entry only once.

13. Can I post the reversal entry in before period of actual journal entry?

No, reversal entry should be in current date or future date in the accounting periods of actual Journal entry.

14. What is the formula for funds available?

Funds available = Budget – Encumbrance - Actual

15. Whether system allows the reversing journal to posted even if the original journal is posted or not?

Yes, The original journal is posted or not. The reversal entry can be posted. Reversal meaning is to nullify the particular account balance.

16. How will it display if I use my segment separator in description?

It will appear as “?“ mark

Ex: my segment values are company1, dept1, A/C

We can select the segment separator is “ / “ in value set at the time of journal entry   

posting the A/C in between “/ “ is appear ? in description.

17. What do you mean by Translation?

1. Translation means we can translate actual and budget account balances from functional currency to Reporting currency. 

2. Actual we perform translation for reporting purpose.  We need to define average, period end and historical rates. Average rate will be applicable to expenses and revenues, Period end rate will be applicable to Assets and Liabilities and Historical rate for Owners equity. 

3. Run translation after we have completed all journal activity for an accounting period. 

4. If you post additional journal entries or change your translation rates after running translation for a period, you must re-translate. 

5. Additionally, if you change the account type for an account Segment value and want to re-translate your actual account balances, you must re-enter or change the period-end and average exchange rates for the periods you want to re-translate.

18. What are the rules for Translation?

Rule for translation

1. Prior period and future period should be open
2. Define Average and Period end rates

We cannot perform translation for the first period.

19. How can I deactivate the segment value?

We can deactivate the segment value by uncheck the enable box of the particular segment value in value set of the particular segment.

20. What type of accounts will display in list of values while entering retained earnings account in set of books?

Only Balance sheet items are display like Assets, Liabilities and Owner’s equity.

21. Briefly list the steps for foreign currency journals.

Before entering the foreign currency journals we need

1. Conversion type
2. Conversion rate
3. Conversion Effective date

22. An entire batch was reversed and posted, while trying to reverse and post just one journal entry in the batch. How can this be corrected?

Leave the wrong reverse entry and reverse all other reversed entries manually and post them.

23. Can a Flex field Qualifiers be changed after it has been created?

Cannot change.

24. Can different set of books share the same value set?

Yes, the value set is an independent because the value set is share different set of books.

25. What are the three options available in oracle application with respect to foreign currencies?

Three options are

1. Conversion
2. Translation
3. Revaluation

AP --> Subledger Accounting (XLA) --> GL --> PA --> FA Query Links

Step1:      AP  --> XLA --> GL links

SELECT invoice_id

  FROM ap_invoices_all 

WHERE invoice_num = <invoice_number>;

SELECT amount,

               period_name,

              accounting_date,

              posted_flag,

              accrual_posted_flag,

              accounting_event_id,

              dist_code_combination_id,

               invoice_distribution_id

  FROM ap_invoice_distributions_all

WHERE invoice_id = < invoice_id >;

SELECT entity_id

   FROM xla_events

 WHERE event_id = <accounting_event_id >;

SELECT ae_header_id

   FROM xla_ae_headers

 WHERE entity_id = <entity_id>;

SELECT gl_sl_link_id

   FROM xla_ae_lines

 WHERE ae_header_id = < ae_header_id >

     AND code_combination_id = < dist_code_combination_id >;

SELECT je_batch_id,

               je_header_id

   FROM gl_import_references

 WHERE gl_sl_link_id = <gl_sl_link_id>;

By using the below query we can view journal entry batches from which the invoice is posted.

SELECT name Batch_name, Status, Default_period_name,

               Posted_date, Posting_run_id

   FROM gl_je_batches

 WHERE je_batch_id = < je_batch_id >;

By using the below query we can view the Journal entry headers.

SELECT name Journal_name, Je_category, Je_source, Ledger_id, Period_name, Je_from_SLA_flag,

               Status

  FROM gl_je_headers

WHERE je_header_id = < je_header_id >;

By using the below query we can view the Journal entry lines of a particular Journal entry header

SELECT Je_line_num, code_combination_id, Period_name,

               Entered_dr, Entered_cr, Ledger_id

   FROM gl_je_lines

 WHERE je_header_id = < je_header_id >;

 If we want to see the Journal entry lines of a particular invoice distribution line, use the below query as shown:

SELECT Je_line_num, code_combination_id, Period_name,

               Entered_dr, Entered_cr, Ledger_id

   FROM gl_je_lines

 WHERE je_header_id = < je_header_id >

      AND code_combination_id = < dist_code_combination_id from Ap_invoice_distributions_all >                                        

      AND Period_name = < Period_name from Ap_invoice_distributions_all >;

XLA_DISTRIBUTION_LINKS stores the link between transactions and Subledger journal entry lines.

This is the SLA Distributions table. It captures the lowest level granularity of a transaction’s accounting. It is important to understand the information captured in XDL. This table stores the details at the distribution level for both source and applied transactions because AP utilizes the business flow feature of SLA. Applied Transaction means a transaction such as Payments, which is applied to an Invoice, or it could also mean a transaction, such as a Prepayment.

Xla_distribution_links Table Column Name	Description	Reference to AP Table Column Name
Source_distribution_id_num_1	DIST_ID of the current transaction (For invoice – invoice_distribution_id column of Ap_invoice_distributions_all table. For payment – payment_hist_dist_id column of ap_payment_hist_dists table)
Source_distribution_type	AP_PMT_DIST (For Payments), AP_INV_DIST (For Invoice), AP_PREPAY (For Prepayment)
Applied_to_entity_id	Entity_id of the invoice/upstream transaction
Applied_to_source_id_num_1	Invoice_id in case of LIABILITY line of the payment
Applied_to_dist_id_num_1	Invoice_distribution_id against which this payment_distribution_id is applied	Ap_invoice_payments_all & Ap_payment_hist_dists  tables
Ref_ae_header_id	Invoice_id of the invoice/upstream transaction
Ref_temp_line_num	Temp_line_num of the invoice/upstream transaction
Ref_event_id	Event_id of the invoice/upstream transaction

SELECT Source_distribution_id_num_1,

               Source_distribution_type,

               Applied_to_entity_id,

               Applied_to_source_id_num_1,

               Applied_to_dist_id_num_1,

               Ref_ae_header_id,

               Ref_temp_line_num,

               Ref_event_id

  FROM xla_distribution_links

WHERE ae_header_id = < ae_header_id >;

The tables’ xla_distribution_links and Ap_invoice_distributions_all are linked by

Source_distribution_id_num_1 = Invoice_distribution_id

Source_distribution_type = ‘AP_INV_DIST’

SELECT Source_distribution_id_num_1,

               Source_distribution_type,

               Applied_to_entity_id,

               Applied_to_source_id_num_1,

               Applied_to_dist_id_num_1,

               Ref_ae_header_id,

               Ref_temp_line_num,

               Ref_event_id

   FROM xla_distribution_links

 WHERE ae_header_id = < ae_header_id >

      AND source_distribution_type = 'AP_INV_DIST'

      AND source_distribution_id_num_1 = (SELECT invoice_distribution_id

                                                                     FROM Ap_invoice_distributions_all

                                                                   WHERE invoice_id = < Invoice_id >);

Step2: GL --> SLA --> PA Links

SELECT *
   FROM gl_je_lines

 WHERE je_header_id = < je_header_id >

      AND Je_line_num = < Je_line_num >;

SELECT *

  FROM gl_import_references

WHERE je_header_id = < je_header_id >
     AND Je_line_num = < Je_line_num >;

SELECT *

  FROM xla_ae_lines

WHERE gl_sl_link_id in (SELECT gl_sl_link_id

                                          FROM gl_import_references

                                       WHERE je_header_id = < je_header_id >

                                            AND Je_line_num = < Je_line_num >;

SELECT *

  FROM xla_distribution_links

WHERE ae_header_id in (SELECT ae_header_id

                                           FROM xla_ae_lines

                                         WHERE gl_sl_link_id in (SELECT gl_sl_link_id

                                                                                  FROM gl_import_references

                                                                                WHERE je_header_id = < je_header_id >

                                                                                     AND Je_line_num  = < Je_line_num >))

     AND ae_line_num = < ae_line_num>;

SELECT source_distribution_id_num_1

   FROM xla_distribution_links

WHERE ae_header_id in (SELECT ae_header_id

                                            FROM xla_ae_lines

                                          WHERE gl_sl_link_id in (SELECT gl_sl_link_id

                                                                                   FROM gl_import_references

                                                                                 WHERE je_header_id = < je_header_id >

                                                                                      AND  Je_line_num  = < Je_line_num >) )

SELECT * 
  FROM pa_cost_distribution_lines_all

WHERE expenditure_item_id in

      (    SELECT source_distribution_id_num_1

              FROM xla_distribution_links

            WHERE ae_header_id in (SELECT ae_header_id

                                                       FROM xla_ae_lines

                                                     WHERE gl_sl_link_id in (SELECT gl_sl_link_id

                                                                                               FROM gl_import_references

                                                                                             WHERE je_header_id = < je_header_id >

                                                                                                  AND  Je_line_num  = < Je_line_num >) )
      );

Step3: GL --> SLA --> FA Links

SELECT *
  FROM gl_je_headers
 WHERE NAME = <JOURNAL_NAME>;

SELECT *
  FROM gl_je_lines
 WHERE je_header_id = <JE_HEADER_ID>
   AND code_combination_iD = <CCID>;

SELECT *
  FROM gl_import_references
 WHERE je_header_id = <JE_HEADER_ID>
   AND je_line_num IN (1,2);

SELECT *
  FROM xla_ae_lines
 WHERE gl_sl_link_id IN (SELECT gl_sl_link_id
                           FROM gl_import_references
                          WHERE je_header_id = <JE_HEADER_ID>
                            AND je_line_num IN (1,2));

SELECT *
  FROM xla_distribution_links
 WHERE ae_header_id IN (SELECT ae_header_id
                          FROM xla_ae_lines
                         WHERE gl_sl_link_id IN
                                 (SELECT gl_sl_link_id
                                    FROM gl_import_references
                                   WHERE je_header_id = <JE_HEADER_ID>
                                     AND je_line_num IN (1,2)));


SELECT *
  FROM fa_deprn_detail
 WHERE event_id IN
       (  SELECT event_id
            FROM xla_distribution_links
           WHERE ae_header_id IN (SELECT ae_header_id
                                    FROM xla_ae_lines
                                   WHERE gl_sl_link_id IN
                                           (SELECT gl_sl_link_id
                                              FROM gl_import_references
                                             WHERE je_header_id =  <JE_HEADER_ID>
                                               AND je_line_num IN (1,2)))
       );

Technical Terms in Oracle Apps explained through real time example Apps Story

                   

                   The below example explains a few of the important terms and concepts used in the Oracle E-Business Suite. This would be a good starting point for the beginners to better understand the concepts behind Oracle Applications. 

                Say Vinoth is the owner of a wholesale fruit shop. He buys various fruits like apples, oranges, mangos and grapes etc from farmers directly and sells them to retail shop owners and also to the direct customers. 

                The farmers are referred to as VENDORS/SUPPLIERS in Oracle Applications. Vinoth keeps track of all his vendors’ information like addresses, bank account and the amount he owes to them for the fruits that he bought etc, in a book named PAYABLES.  

                 Vinoth gets an order from a retail shop owner of Fruit Mart, for a shipment of 11 bags of apples, 25 bags of oranges and 32 kgs of grapes. In Oracle Apps, bags and kgs are referred to as UOM (unit of measure), Fruit Mart is called CUSTOMER and the order is referred to as SALES ORDER. Vinoth maintains a book called ORDER MANAGEMENT where he writes down all the details of the SALES ORDERS that he gets from his customers.  

          Vinoth will raise a PURCHASE ORDER to his VENDORS/SUPPLIERS for purchasing fruits which is referred to as PURCHASING in Oracle Applications.

                 Say the fruits have been shipped to the customer Fruit Mart. Vinoth now sends him the details like cost of each bag/fruit, the total amount that the customer has to pay etc on a piece of paper which is called INVOICE / TRANSACTION. Once the INVOICE has been sent over, the customer then validates this against the actual quantity of fruits that he received and will process the payments accordingly. The invoice amount could be paid as a single amount or could be paid in installments. Vinoth’s customer, Fruit Mart pays him in installments (partial payments). So Vinoth has to make a note of the details like date received, amount received, amount remaining, amount received for what goods/shipments/invoice etc, when Vinoth receives the payments. This detail is called RECEIPT, which will be compared to the invoice by Vinoth to find how much Fruit Mart has paid to him and how much has to be paid yet. This information is maintained in a book named RECEIVABLES to keep track of all the customers, their addresses (to ship the items), what and how much he has shipped to his customers and the amount his customers owe him etc. 

              Vinoth’s fruit business has begun to improve and has attracted more and more customers. As a result, Vinoth decided to buy a cold storage unit where he could stock more fruits. In Apps, this cold storage unit is known as WAREHOUSE and all the fruits are referred to as INVENTORY.  Due to increase in customers, Vinoth needs to hire more people to help him out in his business without any hiccups. These workers are called EMPLOYEES. At the end of every month, Vinoth pays the salary for all his employees through Checks. These checks are nothing but PAYROLL in Apps.  

            At the end of every month, Vinoth prepares a balance sheet in a book called GENERAL LEDGER to determine how much profit/loss he got and keeps track of the money going  out and going in.  

                  As the business grows, it becomes impossible to record everything on a paper. To make everybody’s life easier, we have very good tools in the market, which help the business men to keep track of everything. One such tool is Oracle E-Business Suite. 

               Oracle Applications is not a single application, but is a collection of integrated applications. Each application is referred to as a module and has it own functionality trying to serve a business purpose.

            Few of the modules are Purchasing, Accounts Payables, Accounts Receivables, Inventory, Order Management, Human Resources, General Ledger, and Fixed Assets etc.

Here is a high level business use of various modules:

Oracle Purchasing handles all the requisitions and purchase orders to the vendors.

Oracle Accounts Payables handles all the payments to the vendors.

Oracle Inventory deals with the items you maintain in stock, warehouse etc.

Order Management helps you collect all the information that your customers order.

Oracle Receivables help you collect the money for the orders that are delivered to the customers.

Oracle Human Resources helps maintain the Employee information, helps run paychecks etc. 

Oracle General Ledger receives information from all the different transaction modules or sub ledgers and summarizes them in order to help you create profit and loss statements, reports for paying Taxes etc. For Example: when you pay your employees that payment is reported back to General Ledgers as cost i.e. money going out, when you purchase inventory items and the information is transferred to GL as money going out, and so is the case when you pay your vendors. Similarly when you receive items into your inventory, it is transferred to GL as money coming in, when your customer sends payment, it is transferred to GL as money coming in. So all the different transaction modules are reported to GL (General Ledger) as either “money going in” or “money going out”, the net result will tell you if you are making a profit or loss.

All the equipment, shops, warehouses, computers can be termed as ASSETS and they are managed by Oracle Fixed Assets.

There is a lot more in Oracle applications. This is the very basic explanation just to give an idea of the flow in ERP for the beginners.

Terminology often used in Oracle Applications:

1. Invoice
2. Receipt
3. Customer
4. Vendor
5. Buyer
6. Supplier
7. Purchase Order
8. Requisition
9. ACH: Account Clearance House
10. Sales Order
11. Pack Slip
12. Pick Slip
13. Drop Ship
14. Back Order
15. ASN: Advance Shipping Notice
16. ASBN: Advance Shipping Billing Notice
17. ATP: Available to Promise
18. Lot/Serial Number
19. DFF: Descriptive Flex Fields
20. KFF: Key Flex Fields
21. Value Sets
22. Organization
23. Business Unit
24. Multi Org
25. Folders
26. WHO Columns
27. Oracle Reports
28. Oracle Form
29. Workflow Builder
30. Toad
31. SQL Developer
32. SQL Navigator
33. Discoverer Reports
34. XML/BI Publisher
35. ADI: Application Desktop Integrator
36. Winscp
37. Putty