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.

Classification of tables in Oracle Applications

1. Transaction Data table : They are normal tables, they are used to stored the data in all the modules in which any DML queries can be performed.

2.Seed data tables: The data in these tables are created at the time of installation. The records in these tables are read only.

3.Interim data table: These tables are used for validations purpose. For instance, Staging tables. These tables are used when we are transferring the data from the external application. to the system tables.

Data flow in oracle apps from flat file to base table- A brief overview

Flat File

The Structure of the flat file must match the structure of the Oracle standard interface tables. If in some cases, they do not match, then we need to get the values from the base tables using the interface programs (validations). These flat files generated may be in text form or a comma or space delimited, variable or fixed format data file.

Upload Program

Once the data has been extracted to a flat file, it is then moved / loaded into user defined staging tables in the target database using SQL Loader or UTL_FILE utilities.

Interface Program

Then PLSQL interface programs are written and run which validate the data in the staging tables and insert the same into the Oracle provided standard Interface tables. If the staging table columns are not matching with the Interface tables, the validations are carried out. For instance,

BEGIN

SELECT USER_JE_CATEGORY_NAME  --column name

INTO i_CATEGORY  --cursor variable name

FROM GL_JE_CATEGORIES --standard interface table name

WHERE USER_JE_CATEGORY_NAME = REC_CUR.CATEGORY;                                                                         


Suppose we have 'Category' column in the staging table, but we need the values of 'USER_JE_CATEGORY_NAME'. We need to get the values of 'USER_JE_CATEGORY_NAME' from the 'GL_JE_CATEGORIES' interface table and temporary store the values in 'l_CATEGORY' which is a cursor.

Later we can store the value of 'l_CATEGORY' to the interface table.

Standard Interface Program
Once the interface tables rae populated, the respective interface program (each data element interface has a specific interface program to run) is submitted. the Interface programs validate the data, derive and assign the default values and ultimately populated the production base tables.

About BI Publisher

1.XMLP, i.e., XML Publisher is the beginning of BIP. Today, BIP replaces XMLP. XMLP operates entirely within EBS and can only be used within EBS. On the other hand, BIP can only be installed as a standalone version running.

2. Oracle BIP has two components viz., Oracle BIP server & Oracle BIP Desktop

3.Oracle BIP Desktop is a MS word plug-in that we use to create a report template. A report template that includes layout information for a report, such as the fields that the reports conatins and the placement of these fields in the report.

4.It includes layout query and language functionality. It separates logic from the layout.

5. It recommended that we use the same version of oracle BIP server & Oracle BIP desktop in our environment.

6. Oracle BIP desktop also termed as Oracle BIP ADD-IN for MS word.  

Types of Testing in Oracle EBS

1. Unit Testing : This includes validation of each fields in oracle apps.

2. Integration Testing: This includes complete cycle testing, for instance, O2C and P2P cycles.

3. UAT Testing: Also termed as User Acceptance Testing, is carried out to test the complete transaction process along with cycles.

TOAD tip and shortcuts

1. After starting TOAD, select database from right side panel.

2. To view all the tables, procedures, triggers, views, etc. on the screen, Click "SCHEMA BROWSER" (A green colored button on the left-top).

3. Write any table name, select it and Press "F4" button to display all information about that table.

4. To execute any query in TOAD, press "Control + Enter" button.

Oracle ERP / APPS Instances

1. Instances : Development Stage 1 and Development Stage 2.

2. Testing : Developer testing or testing by professional oracle testers.

3. Production : Client Testing.

Types of Oracle Apps (ERP) projects

1. Implementation Projects.
2. Customization Projects.
3. Migration / Up-gradation Projects.
4. Supporting / Maintenance Projects.  

Tips to become a Oracle Technical consultant

1. Learn SQL : Write some complex SQL queries and try to analyse how data is processed in back end. Complex SQL queries involves, SQL important function such as DECODE, TRUNC, DATE, etc. along with different joins concepts. SQL is the base language for database.

Also try to focus on BIND variables and PARAMETER in SQL queries. They are the vital in Oracle apps.

2. Learn PLSQL : Once you are adapted with the SQL part, the PLSQL part becomes quite understandable. PLSQL are a sort of procedure to SQL program.

Triggers, Packages, Procedure: Interface & APIs.  are the components needs to be focused on to be an Oracle Technical consultant.

3. Know the basic Oracle apps modules : "Application Developer" is the sole module that is frequently used a technical professional. It is the module where the components such as Oracle reports, PLSQL programs are registered. Along with this module, try to be a bit familiar with AP, AR, GL, INV, PO, OM and more importantly O2C and P2P cycles.

4. Workout with Oracle Reports and Forms : Expertise in SQL and PLSQL makes Reports and forms quite understandable and easy. To create oracle Reports and forms, we just need to follow standard procedure making use of SQL and PLSQL.

4. Focus on RICE components : Viz., R-Reports (Oracle Reports), I- Interfaces (Programs in PLSQL), C- Conversions (Programs in PLSQL), E- Extensions (Oracle Forms).

BI Publisher vs XML Publisher

1. XMLP is the older version of BIP, in other words, XMLP is the beginning of BIP. Today, BIP replaces XMLP.

2.XMLP operates entirely within EBS and can only be used within EBS. On the other hand, BIP can only be installed as a standalone version running.

Types of Oracle database tables used in Oracle Apps (R12)

1. Staging table : The tables are created manually in oracle database to insert/store the flat file data. Staging table is basically a physical table like state record. It has a suffix like "_STG". It is for doing manipulations. Data will be inserted in those tables and when we need to process some validate those data and if validated successfully, we insert that data to oracle Interface tables or Oracle Base (standard) tables.

2. Interface table :  These are the tables which are present by default in the oracle database. The data from the staging tables after validations are moved to the Interface table. For example, GL_INTERFACE table.
The data are entered in interface tables either manually through SQL queries or by PLSQL programs.

3. Base (Standard) table : These are the standard tables present in oracle database. To store data from interface table to base table, we need to run oracle standard requests, for example "GL_IMPORT_JOURNALS".

4. Error table: These are the least priority table in oracle table. After validation in staging table, the failed data the allowed to stored in error table. 

A brief note about GL interface- PL/SQL procedure

The main use of an PLSQL interface program is to move the data from staging table to the interface table in oracle apps. A staging table in an Oracle apps is a table which is created manually to store data temporarily for validating before moving them to interface table.

Prerequisites:
==================
Before Going to develop the GL interface we should check the following
functional setups has completed or not.

1)Source and category Creation (Setup=>Journal=>Sources)
2)Period are defined and Open status(Setup=>open\Close)
3)Accounting structuere should be validated
4)Set of Books should be defined 1)currency 2)Calendar3)Chart of Accounts
5)Currency Conversion Rates should be defined.

Below steps are performed in GL interface.

1)We need to get the flat file from the client containing data (a flat file is a file which is in raw excel format containing data). 

2)We are then required to create Staging table as per flat file structure.

3)Once we create the Staging table as per the flat file structure, then we need to create Control file and uploaded data to the staging table through the SQL*LOADER.

4)Once we get the data into the stage(staging) table we have to transfer from stage table into
 Interface table by using PL/SQL Program.

Generally, inside this plsql program we write
a)Cursor   (A Cursor is a pointer used to fetch rows from a result set) Here by using cursor we select data from stage table and move them to interface table. Thus cursor acts as a temporary storage medium.

b)Validate Data  (The data need to be validate before moving to the interface table) 

c)Insert Statement (To Insert Into Interface table from temporary cursor variables)

Note:1) If Record is valid record then we will insert into interface table other wise
         we will insert into Error tables.
        2)Before Inserting the Data into Interface tables first we have to understand the
        Interface table structure and we should know what data is valid and what data
        is not valid for the all Mandatroy columns.

2)Once the data is Inserted into the Interface table . Then we will submit concurrent
  program to transfer the data from interface table to Base Tables.

In this Interface table is GL_INTERFACE
Some of the Mandatroy Columns:
==================
1)STATUS
2)ACCOUNTING_DATE
3)CURRENCY_CODE
4)CREATED_BY
5)CREATION_DATE
6)USER_JE_SOURCE_NAME
7)USER_JE_CATEGORY_NAME
8)ACTUAL_FLAG
9)ENTERED_DR
10)ENTERED_CR
11)GROUP_ID
12)PERIOD_NAME
13)SET_OF_BOOKS_ID


1)Status: Column will accept any Data. but we will insert always standard string called
         "NEW". It means that we are bringing new data into Oracle Applications.
2)ACCOUNTING_DATE  : Column will accept valid acc_date as per the accounting Periods
3)CURRENCY_CODE    : from FND_CURRENCIES table we can find out wether CUrrency Code is
            valid or Not if Currecny code is available in the Table and enabled it is
            valid.otherwise Invalid
4)CREATED_BY  : IS nothing but UserID we have to find wether  USerID is valid or not
               By using FND_USER table we can find out wether it is valid or not.
5)CREATION_DATE : Should be valid date
                   date should be <= SYSDATE
6)USER_JE_SOURCE_NAME: Valid Source name
                 By using GL_JE_SOURCES table  we can find wether valid source or not.
7)USER_JE_CATEGORY_NAME : Will accept valid cvategory name
        By using GL_JE_CATEGORIES table we can find out wether valid category or Not.
8)ACTUL_FLAG    : This Column will accept single Character
                  Either 'A' 'B' 'E'
                  A=Actuval Amounts
                  B=Budeget Amounts
                  E=Encumbrance Amounts
9)ENTERED_DR
10)ENTERED_CR : Both Columns we accept Numbers Only but same number we have to insert
                into both the columns.
           Always    ENTERED_CR = ENTERED_DR . Otherwise Suspense Account will be
               Created.
11)GROUP_ID   : Will accept any Number
12)period_name : Will Accept valid period and that period should be in the Open status
                By using GL_PERIODS we can find wether Period is there or not
                         GL_PERIOD_STATUSES table we can find wether it is in "Open"
                status or Not.
13)SET_OF_BOOKS_ID  : This column will accept valid set of Books ID. By using
              GL_SETS_OF_BOOKS table we can fnd out wether valuid set_of_books_id or
              not.