How to define a Chart of Accounts in Oracle Apps R12

Chart of Accounts is an Accounting Structure of an organization. It is used to record the transaction activity of an organization. It records the financial and accounting transactions of an organization and helps to maintain accounting balances.

Chart of Account is a key flexfield having one or more data segments as defined by the user.

An oracle Chart of Account can have up to 30 segments in a flexfields. 2 segments are mandatory; Balancing Segment and Natural Accounts.

This means, a flexfield can have minimum of 2 segments and maximum of 30 flexfields.

1. Define value sets for the segemnts.
2. Define segments and assign value sets (created above).
3. Define Qualifiers to the value sets.
4. Freeze and compile.

TOKEN in oracle apps

1. Token acts as an intermediate between oracle report user parameter and concurrent program parameters. It is an interface between concurrent program and report builder. It is used to map the parameters which we have created in the report.
2. It is used for mapping the report builder parameter to concurrent program parameters with same variables.
3. Tokens are case sensitive. So, it should be in the same case as given in the report builder.

How to delete a registered concurrent program

The concurrent program can be deleted from the back-end.
From front end we cannot delete the program, we can only enable or disable the program.

begin
FND_PROGRAM.delete_executable(executable_short_name=>xxexe, application =>XX_Custom);
commit;
end;
/


begin
FND_PROGRAM.delete_program(program_short_name=>XXCONC, application =>XX_custom);
commit;
end;
/

Functional or Technical apps consultant

Its a very common confusion for those who want to switch their career to oracle apps consultant.
There is always a contradiction at the back of the mind whether to be a functional apps consultant of a technical apps consultant.  Its really becomes important to decide the answer to the above question. What's important is finding the right answer can be a huge turnaround in your life from career point of view.   It can possibly can the course of your life.

You must be a functional apps consultant if,

1.  You are from accounting background (not necessary, though). Being from a accounting  background,  will make the functional accounting concept much easier to learn and understand  quickly.
2.  You must have a super user experience in the oracle apps module(s). Super user experience is  advantageous as you can access all the functional components in an apps without any restrictions.   
3. You are technically weak in programming concepts and coding.
4. You like to spend most of your time in meeting and writing documents.
5. You understand the concept and functionality of overall ERP apps modules very well and also the dependencies between the various modules.

You must be a technical apps consultant if,

1. You are technically strong in programming skills and passionate about coding.and preferable from technical background and qualifications.
2. You don't bother the relationships between the apps modules, for instance say between GL and OM module.
3. You are ready to accept new challenges every time and .you like to get a feeling of having achieved something each day.

Make parameters in oracle reports as mandatory

To make parameters in oracle report as mandatory:

Navigate to 'Application developer' in concurrent Program -> parameters window -> "Required field" just check that.

Get contents of DBC file without access to server

1. If we need to develop OA framework, then we need to have the DBC file of the environment in which we want to work on.
For 11i instance, the file is located in $FND_TOP/Secure
For R12 instance, the file is located in $FND_SECURE.

But if we don't have the access to the server, then we have a convenient way to get the DBC file.
Go the homepage url of the web browser, append "/OA_HTML/jsp/fnd/aoljtest.jsp" to it.

2. 'AOL/J Diagnostic Tests' form will open. Enter all the details and click 'Test'.

3. If all the entered details are OK, a overview page will open with details. Click on the link ' Enter AOL/J Setup Test'.

4. From this point find the menu entry 'Locate DBC file' and click it.

5. On the right side we will get the content of the DBC file. We need to copy paste it in <SID>.dbc file and
place the file in Jdeveloper 'JDEV_USER_HOME/dbc_files/secure.

Following are the common error message during oracle xml report.

1. XML document must have a top level element. Error processing resource.
2. The file should be in XML-DATA-TEMPLATE format.
3. ora-00911 invalid character in oracle xml.
4.The following tags were not closed.

Points to resolve the above issue:

1. write the first line as "<?xml version="1.0" encoding="UTF-8"?>". Write it in the same format without changing the case of any words.

2. write 'dataTemplate' as 'dataTemplate'. It may give error during generating output xml file if we write like, Datatemplate or DATATEMPLATE or datatemplate.

3.Close the tags properly. Take care of  /> closing tags.

4.<sqlStatement name> and <group source> must have same value.

5.The parameter name must be exactly like token name or bind variable name. (including the case of the variables).

6.The <element value > must be same to the column name.

7.Check the dataType of the parameter.

Following image will provide the proper format for data template xml file:

What is XDODTEXE used in the Executable section of Concurrent Program ?

XDODTEXE is java concurrent program used as a BI Publisher Data template Executable. The main purpose of this executable is to identify data template file (.xml) and execute the data template to generate the raw (output) xml data, which is later used by BI Publisher formatting engine to format as per the layout (RTF, PDF, etc).

This executable will be used by all the BI Publisher reports ( Concurrent Program) which are using Data Template to generate the xml data.

Value sets in Oracle Apps

1. Value sets represents set of values. Through value sets, oracle apps validates that correct data is being entered in the fields in screen.  Value sets are mainly used when specifying the list of data which can be entered as parameters when running a concurrent program.

2.Value sets are attached to segments in flex fields. Though value sets are attached to the parameter of concurrent program, but in reality oracle treats parameter as descriptive flex fields.

3. Types of value sets:;

• Independent: The value entered is validated against a predefined set of values. 
• Dependent: The value is validated against a set of values which depends on an independent value sets.
• Table: The value entered is validated against a list of values which are dependent of tables.
• None: The values are only validated against the format type specified. For example, DATE is validated only when it is entered as MM/DD/YYYY, DD/MM/YY, etc. whatever format is specified.  The values are not specified any predefined set of values.
• Special and pair: The value is validated against a set of values in another flex field.

4. The base table where name of the value sets is stored, then it is :

    fnd_flex_value_sets.

    The name of the table where values stored:

   fnd_flex_values,

   fnd_flex_values_tl

AR Tables: A diagrammatic Relation

  The above diagram shows the list of couple of important tables in AR and their joins.

       However this is not a complete list of AR Tables and their joins.

                           

How to execute PLSQL procedures, packages or any other related code for oracle apps

1. Form the query in Toad.

2. Compile the query by either pressing 'F9' or 'right click =>execute =>complie all'.

3.Once compile successfully, Navigate and log in to oracle Apps responsibility,
   Application developer => Executable.

4. Create the executable program and select 'executable name' as package or procedure name (as per requirement).

5. Copy the short name and navigate to concurrent => Program.

6. Paste the short name as executable name.

7. Give/forward the program name to the DBA, who will attach the program to the request group to a particular responsibility.

8.Once attached, we can run the request from SRS window on the attached responsibility.

Date parameters in Oracle Report Query

1.If the date is already in the format 'mm/dd/yyyy' in database, then at the time of defining date as parameter, no need to write as;

 XXX_TRX_DATE BETWEEN NVL(TO_DATE(:P_DATE_FROM,'mm/dd/yyyy'),XXX_TRX_DATE)
AND NVL(TO_DATE(:P_DATE_TO,'mm/dd/yyyy'),XXX_TRX_DATE)

instead following can be written:

 XXX_TRX_DATE BETWEEN NVL(:P_DATE_FROM, XXX_TRX_DATE)
AND NVL(:P_DATE_TO, XXX_TRX_DATE)

No need to convert to TO_DATE format, as its already in 'mm/dd/yyyy' format.
If its not in above format, then we need to convert into TO_DATE format.


2. While executing the parameter date query, always change the Type of the bind variables to 'Date' for the date parameters in TOAD as shown below.

3. If you are creating xml data template for the parameter query. Always  define the parameter datatype as 'Date' as shown below;

<parameter name="p_XXX_date" dataType="Date" />

Oracle Apps explained through real time example with technical terms

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

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.

David gets an order from a retail shop owner of Fresh 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), Fresh Mart is called CUSTOMER and the order is referred to as SALES ORDER. David maintains a book called

ORDER MANAGEMENT where he writes down all the details of the SALES ORDERS that he gets from his customers.

Say the fruits have been shipped to the customer Fresh Mart. David 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. David’s customer, Fresh Mart pays him in installments (partial payments). So David has to make a note of the details like date received, amount received, amount remaining, amount received for what goods/shipments/invoice etc, when David receives the payments. This detail is called RECEIPT, which will be compared to the invoice by David to find how much Fresh 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.

David’s fruit business has begun to improve and has attracted more and more customers. As a result, David 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, David 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, David pays the salary for all his employees through Checks. These checks are nothing but PAYROLL in Apps.

At the end of every month, David 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, 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 report 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.

Oracle Apps  Important Terminology and Tools:

Invoice

Receipt

Customer

Vendor

Buyer

Supplier

Purchase Order

Requisition

ACH: Account Clearance House

Sales Order

Pack Slip

Pick Slip

Drop Ship

Back Order

ASN: Advance Shipping Notice

ASBN: Advance Shipping Billing Notice

ATP: Available to Promise

Lot/Serial Number

DFF: Descriptive Flex Fields

KFF: Key Flex Fields

Value Sets

Organization

Business Unit

Multi Org

Folders

WHO Columns

Oracle Reports

Oracle Form

Workflow Builder

Toad

SQL Developer

SQL Navigator

Discoverer Reports

XML/BI Publisher

ADI: Application Desktop Integrator

Winscp

Putty

Important Oracle Apps Interview questions

Important Oracle Apps Interview questions

Especially questions are solely asked based on your CV. Following are some of the questions for oracle apps interviews.

1. What are the types of oracle reports?

      Ans: Tabular,
              Group left,
              Group Above,
              Matrix,
              Matrix with group,
              Form,
              Form letter,
              Mailing label.

     2.  What are the table used during report creation? (Questions based on reports created or mention in     CV)

     3. What are the types of report triggers? Mention sequentially.
 
     Ans: Before parameter form,
             After Parameter form,
             Before Report,
             Between pages,
             After report.
     
     4. Mention the mandatory user parameter.

     Ans: P_CONC_REQUEST_ID

     5. What is legacy system of your client?
  
     6. What is the connection or relation between 'concurrent request' and 'data definition' ?

     Ans: 'Short Name' to be copied in 'Code' field of the data definition window.

     7. Date when the project started and went live. And the your role during the supporting phase of the project.

     8. Explain the complete flow of SCM with tables include.

     9.Explain the Interface programs steps.

     10. Name all the TCA tables.

     11.  What are the ways to create Order in Oracle apps.

     12. Give the difference between test plan and test scripts.

     13. Questions related to AIM documentations.

     14. How to Copy the existing order entry data to a new in Order creation window.

     15.How to make changes to an existing standard report. Explain whole process.

     16.Name any 5 tables from PO and AR modules.

     17.What are the types of triggers in PLSQL. Explain.

     18. Primary keys between tables oe_order_headers_all and oe_order_lines_all.

     19. Difference bet VARCHAR(30) and CHAR(30).

     20. State Bulk collect in PLSQL with example.

     21. Collection in PLSQL.

     22. Difference between ROWNUM and ROWID.

     23. What is System Profile options in apps.

     24. How to attach concurrent program to a request group.

* Please comment if you have some different important interview questions.

Important Points for Oracle standard Reports

Important Points for Oracle standard Reports

Following are the points that are mandatory every time when we edit or create report in Oracle standard Report. 

1. For a sql query having parameter and binding variables, the parameters will be added automatically on the "USER PARAMETERS" on the left side of Object navigator window. 

     If date parameter is used then right click on the date parameter, go to -> Property inspector. 

     Change the 'Input Mask' to any desired input value. And change the 'Datatype' -> Date.

2. Click the '+' button (green button) and add a parameter with naming as .P_CONC_REQUEST_ID'.

3. Edit the 'Before trigger' and 'After Trigger' option.

Complete cycle from Creating to registering Oracle report using .RTF and XML Data Template.

1.Client supply the report layout in any format (excel).

2.Create and write the query in TOAD (or any other IDE). The output of the query must be similar to the
   client data format. Knowing the important apps tables with columns names and join condition between          them is a key for creating queries for report. In most of the cases, the client provide the tables
   names and column names to be used.

3.Create a data template (XML) using the query. If you have sample data template file, then just modify the
   existing ones.

4.Generally, during report creating using data template, we use 'XDODTEXE' as an executable program.
   'XDODTEXE' is an default executable java concurrent program. So, no need to create executable
   program if you are using data template file to create report.

5. To create the concurrent program. Go to 'Application Developer' in apps -> Concurrent -> Program
   Enter Program ->(Any valid report name. The report name will be used later and will be seen in report          output).
   Short Name->(enter any name)
   Application->(XX_CUSTOM)

   Executable Name-> XDODTEXE (As already mentioned above).
   Format-> (xml).

6. Copy the report name and pass it to DBA. So that the DBA can add rresponsibility in the resquest group)

7. Copy the short name and close the form.

8. In the apps, Go to th eXM: publisher Administrator -> Home -> data Definition.

9. Click 'Create data template'.

10.Paste the short name to 'code' field.

11. Enter name-> (any), Application->XX_Custom

12.Click apply.

13.In data template -> click add file and browse the data template xml file.

14.Run the request in SRS window.

15. View the output in XML format.

16.save the ouput xml file in .xml format. (this will be used for importing in BI Publisher)

17. Create and design the layout in BI Publisher.

18.REGISTERING THE .RTF FILE:

19. Go to the data definition and copy the name.

20.Go to template -> Create 'Template'.

21.Paste the name, Application as 'XX-CUSTOM' and type as 'RTF'.

22.In the file -> Browse the .RTF file.

23.Go to the SRS window of the particular responsibility of AR (Let us consider the DBA
   has attached the AR responsibility) and see the output of the report.

Oracle reports - Reports Creation to reports registration in oracle apps.

Query and requirement analysis
1. Analyse the tables involved in the requirement and write the required sql query in toad and run it to see view the desired output. Once we get the desired output, Copy the query.

Connect report to database and design 'data model'
2. Open report and select 'build a new report manually' and then Select the 'SQL' button from the
    left panel. paste the query in the pop up box. Connect to the desired database.

3. Form the desired 'data model' structure of the report.If required, do the parent-child structure as per the       requirement.
    For instance, if we join dept table with emp table, such that dept.deptno= emp.deptno,
    then we have to the place the fields of the emp columns inside another repeating frame.
    Thus, the resultant will have the dept columns inside a repeating frame and repeating
    frame of the emp columns will be inside the repeating frame of the dept. So here, dept will act
    as a parent and emp as a child.

Design of report layout
4. Click on the 'paper layout' option on the top and draw the necessary fields as per the
    requirements.

5. Sequence of choosing the options from the left navigation panels in the 'paper layout' window.

    a. Select and draw frames. Double click the area to open the
        "property inspector" window. Change the "vertical elasticity" and
        "horizontal elasticity" to "variable" or "expand".
    b. Select and draw the text and write as per column name or as per requirement.

    c. select and draw "repeating frame".Double click the area to open the
        "property inspector" window. Change the "vertical elasticity" and
        "horizontal elasticity" to "variable" or "expand" and change the 'source'
        option to the repeating field name.

    d.  select and draw 'field'. The total no of fields must be same to the
         total no of columns.

6. Once all the above is done, save the report in .rdf file.

Adding user parameters and necessary triggers
7. Expand the 'data model' option of the left object navigation panel. Select 'User parameter'
    click the '+' sign on the left side and enter in the Property-inspector
    window as well as the parameter name (default value is P_1) as follows-

    Name: P_CONC_REQUEST_ID
    width: 15

8. Edit the 'Report triggers' as follows-
 
    Before Report(right click and edit in plsql editor): begin
    srw.user_exit('FND_SWRWINIT');
    return (TRUE);
    end;
    After Report (right click and edit in plsql editor): begin
    srw.user_exit('FND_SRWEXIT');
    return (TRUE);
    end;

9. Save the form.

Compilation of the report
10. Go to (on the top) Program -> Compile -> All
      The report must compile successfully.

Copy the .rdf file to server folder
11. Once all the above process is done, including the report layout
      and defining the triggers and User parameters.
      Move or copy the .rdf file to the server folder through WINSCP
      (For instance, let the folder name be CUSTOM_TOP).

Registering the report in apps.
12. Open Oracle app, Go to the Application developer module -> Concurrent

13. Once the form opens, enter 'Executable', 'Short name' (The short is important as the same
      short name used be used later).

14. Enter 'application' - Folder name to which we moved the .rdf file thro' winSCP.

15. Enter Execution method as - 'Oracle Reports'.

16. Enter 'execution file name' - EMP_detail (enter same as the .rdf file name).

17. Copy the short name and save the form and close the form.

18. Navigate to the 'Program' under concurrent.

19. Enter Program, short name, Application (enter any name).

20. Enter 'name (executable)' - paste the copied short name.

21. Select the desired output format. Generally, we select .pdf option.

22. Save the form.

Flavors of oracle applications

1. Production Oracle Applications: Used for implementing in any organization.

2. Vision Oracle Applications: used for demonstration or training purpose.

3. Test Oracle Applications: Used for R&D purposes

Interfaces

Interfaces are the tables which acts as a medium to transfer data from one module to another.or transfer data from legacy system to oracle applications. There are 352 tables provided by the oracle package. Each module having its own interface tables.

Interfacing
It is the process of converting the records from one format to another. The main components are:

1.Transfer Program: Transfer programs are generally developed using JAVA, PLSQL or SQL LOADER. When source modules are implemented in Oracle applications, then Transfer program comes by default with the package. Otherwise, we have to developer it.

Logic of interface programs:
It maps the columns of the source table with the columns of the interface tables.
It performs row level and column level validations.
It transfer data from the source to the interface tables.

2.Interface tables: These tables are used to store data coming form the staging table before moving to Oracle base tables.

3.Import program:  These programs are designed using PLSQL, Java, C, C++, etc. They are generally registered with destination modules.For all the interface tables, Oracle application package is going to provide Import programs..

Flexfields : A brief note

A flexfield is made up of sub-fields or segments. There are two types of flexfields:

1. Key Flexfield (KFF) :  It appears on our apps screen as a normal text field with an appropriate prompt. They are used to capture mandatory an key business information of the organization. Each KFF has its own base table.

There are 31 KFF supplied with the oracle package.

2. Descriptive Flex Field (DFF): They are used to capture additional or extra business information of the organization. They are used for adding extra accounts depends as per business requirements.

DFF has two different segments, Global DFF and context sensitive DFF.