Oracle Apps FAQS

1. What is Ref Cursor?

Ref cursor is the dynamic cursor where we can pass select stmt at run time

2. What r the collections (row type, collections)

    Row  type

   It allows you to treat tha variables as a unit , all the variables are of same data          type or diff data type

Collections

It allows you to treat tha variables as a unit , all the variables are of same data

3. What is bulk collect and use?

Executing sql statements in plsql programs causes a context switch between the plsql engine and the sql engine. Too many context switches may degrade performance dramatically. In order to reduce the number of these context switches we can use a feature named bulk binding. Bulk binding lets us to transfer rows between the sql engine and the plsql engine as collections. Bulk binding is available for select, insert, delete and update statements.

4. Can we set Index for primary key? If so what is the name of the Index?

When you create a PRIMARY KEY constraint, a unique clustered index on the column or columns is automatically created if a clustered index on the table does not already exist and you do not specify a unique nonclustered index. The primary key column cannot allow NULL values.

When you create a UNIQUE constraint, a unique nonclustered index is created to enforce a UNIQUE constraint by default. You can specify a unique clustered index if a clustered index on the table does not already exist.

5.  How to find how many ‘a’ are there in the specified string ‘aasfdhhjkgjaaaa’?

     Select length(‘aasfdhhjkgjaaaa’)-length(translate(‘aasfdhhjkgjaaaa’’,’xa’,’x’)

6. Can we pass Pl/sql table as a parameter to another procedure or not if so how to pass?

create or replace package package_demo
is
type EmpRecType is RECORD (ID EMP.EMPNO%type,
NAME EMP.ENAME%type);

type EmpTabType is TABLE of EmpRecType;

procedure call_getRecords;

procedure getRecords(emp_table EmpTabType);

end package_demo;
/

create or replace package body package_demo
is
employees EmpTabType;

procedure call_getRecords
is
begin
getRecords(employees);
end call_getRecords;

procedure getRecords(emp_table in EmpTabType)
is
begin
null;
-- perform all you want here...
end getRecords;

end package_demo;

7.      What is Mutating Table?

Table under transition is called Mutating Table. 

8. What are the type of Synonyms?

  2 types

 Public

private

9. When is ‘RAISE_APPLICATION_ERROR’ used?

he RAISE_APPLICATION_ERROR is a procedure defined by Oracle that allows to raise an exception and associate an error number and message with the procedure.

10. Wat are psuedo colomns

Pseudocolumns are not actual columns in a table but they behave like columns. For example, you can select values from a pseudocolumn. However, you cannot insert into, update, or delete from a pseudocolumn. Also note that pseudocolumns are allowed in SQL statements, but not in procedural statements.

Apps

1. What will happen when pick release and pick confirm

pick release: This is only soft reservations.

No physical movement of stock...           

  Pick release is the process by which the items on the sales order are taken out from inventory. The flow is as follows:

1) When an order is booked, based on the setups, the lines are scheduled and they are ready to release. This means the inventory is informed that there is requirement of certain quantity of certain items on certain date.

2) The inventory person ,according to availability allocates the items to those SO Lines and reserves them. 

pick confirm :Hard Reservations. Picked the stock.

Physical movement of stock

3) Then on the day of delivery or somewhere between these items are taken out of inventory and moved to staging subinventory. This means that the items are now ready for shipping and are out of inventory.

When the items reach staging subinventory, they are said to be picked and shipping execution can now ship these items.

2. what is token filed in conc program

TOKEN is an Interface between Concurrent Program and Report builder. This will map the concurrent program parameter values to the report builder parameters(User and System parameters).

3. Where the sql stmt fires in triggers

    After Parameter Form trigger

4. can we call conc prog from report

yes

5. validation trigger

Validation Triggers are PL/SQL functions that are executed when parameter values are specified on the command line and when you accept the Runtime Parameter Form. (Notice that this means each Validation Trigger may fire twice when you execute the report). Validation Triggers are also used to validate the Initial Value of the parameter in the Parameter property sheet

6. Can you use placeholder column with out using formula column?

No

7. When you will go for XML reports and when you will go for Discoverer reports.

8. How can you use DDL stmt  in report?

     Using SRW.DO_SQL

9. If you have one company in France and one in Germany using the same currency will you use different set of books ?

        No

 

10. What are the different types of documents in PO ?

PO, Requisitions, Quotations, RFQs

AIM Documents

First Stage : Analysis
Document used :
RD 120 - Requirement Gathering - As Is To Be Process
BR 150 - Fit GAP Analysis (Its been done on the basis of above doc)

Second Stage : Designing
Document used :
BR 100 : Set up Document for the Functional Consultants
MD 200 : Set up Document for the Technical Consultants

Third Stage : Build - DEMO / PROTO TYPE
Document used :
BR 050

Fourth Stage : Testing
Document used :
TE 050

Fifth Stage : Go Live

Six Stage : Post Production

All these documents are the part of AIM (Oracle Application Implementation Methodology) but not all companies are using the same AIM instead they are using their own giving different names but the formats of all the documents are more or less same.

BR Documents : Business Requirement Documents, which is primafaciely done by the Functional Persons of the Implementation Team like Funtional Project Leads / Managers. These documents are the Set up Documents, which is 100% based on the BR 120 - Business Requirement Gatherings as provided by the business. 

 MD Documents : Modular Designing Documents, which are is primafaciely done by the Technical Persons of the Implementation Team like Technical Project Leads / Project Manager. These documents are the Design Documents, which is again based on the BR 120 - Business Requirement Gathering as provided by the business.

These MD's are of basically discussed any customization needs or any special behaviaour oracle system should work which is not the Standard Oracle Funtionality. These also discussed about the tables and the Interface Tables or forms which are going to be used in the particular modules. Thses also discussed about the High Level Designs like Flows of the Business and all.

These MD's are basically made after you all Functional Design and if there is no work around Oracle System provides for a particular Test Scenario and there is no other way other than to go for the Customization.

MD is Module Design these r u used for for forms and reports.MD-50 is funcational Document,MD-60,70 are the technical Documents.

MD60 is created by a module expert containing the requirements for a problem or program to be developed.

MD70 is created by a technical guy containing a design solution to the requirements.

. How often should one COMMIT in a PL/SQL loop? / What is the best commit strategy?

Contrary to popular believe, one should COMMIT less frequently within a PL/SQL loop

to prevent ORA-1555 (Snapshot too old) errors. The higher the frequency of commit,

the sooner the extents in the rollback segments will be cleared for new transactions,

causing ORA-1555 errors.

To fix this problem one can easily rewrite code like this:

            FOR records IN my_cursor LOOP

               ...do some stuff...

               COMMIT;

            END LOOP;

... to ...

            FOR records IN my_cursor LOOP

               ...do some stuff...

               i := i+1;

               IF mod(i, 10000) THEN    -- Commit every 10000 records

                  COMMIT;

               END IF;

            END LOOP;

If you still get ORA-1555 errors, contact your DBA to increase the rollback segments.

NOTE: Although fetching across COMMITs work with Oracle, is not supported by the ANSI standard.

Bulk Collect

One method of fetching data is an Oracle bulk collect. With Oracle bulk collect, the PL/SQL engine tells the SQL engine to collect many rows at once and place them in a collection.  During an Oracle bulk collect, the SQL engine retrieves all the rows and loads them into the collection and switches back to the PL/SQL engine.  When rows are retrieved using Oracle bulk collect, they are retrieved with only 2 context switches.  The larger the number of rows you would like to collect with Oracle bulk collect, the more performance improvement you will see using an Oracle bulk collect.

Executing sql statements in plsql programs causes a context switch between the plsql engine and the sql engine. Too many context switches may degrade performance dramatically. In order to reduce the number of these context switches we can use a feature named bulk binding. Bulk binding lets us to transfer rows between the sql engine and the plsql engine as collections. Bulk binding is available for select, insert, delete and update statements.

When querying multiple rows of data from Oracle, don't use the cursor FOR loop. Instead, assuming you are running at least Oracle8i, start using the wonderful, amazing BULK COLLECT query, which improve query response time very dramatically. The following statement, for example, retrieves all the rows in the employee table and deposits them directly into a collection of records:

DECLARE

   TYPE employee_aat IS TABLE OF employee%ROWTYPE

      INDEX BY BINARY_INTEGER;



   l_employees employee_aat;

BEGIN

   SELECT *

     BULK COLLECT INTO l_employees

     FROM employee;

END;

Of course, if your table has 1,000,000 rows in it, the above block of code will consume enormous amounts of memory. In this case, you will want to take advantage of the LIMIT clause of BULK COLLECT as follows:

DECLARE

    TYPE employee_aat IS TABLE OF employee%ROWTYPE

       INDEX BY BINARY_INTEGER;

    l_employees employee_aat;
    CURSOR employees_cur IS SELECT * FROM employee;

BEGIN

    OPEN employees_cur;

    LOOP

        FETCH employees_cur 

         BULK COLLECT INTO l_employees LIMIT 100;

        EXIT WHEN l_employees.COUNT = 0;

        -- Process these 100 rows and then

        -- move on to next 100.

    END LOOP;

END;Important! When you use BULK COLLECT, Oracle will not
raise NO_DATA_FOUND even if no rows are found by the implicit query. Also,
within the loop (using LIMIT), you cannot rely on cursor%FOUND to determine
if the last fetch returned any rows. Instead, check the contents of the
collection. If empty, then you are done.