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.