Interview Questions and answers- PL SQL, Oracle Apps, Technical, Functional

 1. Cursors

PL/SQL allows the programmer to control the context area through the cursor. 

A cursor holds the rows returned by the SQL statement. The set of rows the cursor holds is referred as active set.

Cursor curser_name is (parameter1 datatype)

select e.name , c.salary

from employee e,

perks c,

where e.eid  = c.pid

and c.salary >= 1000 ;

===============================

1.1 implicit cursors

Curser used by system to complete DML (Delete, insert, update) transaction is using this type of cursor.

===============================

1.2 explicit cursors

this is mainly used to get required data set to execute further actions, using Select clause. This needs definition, call for opening and closing.

=================================

1.3 cursor attributes 

Cursor Attribute Description

%FOUND   -It returns the Boolean result 'TRUE' if the most recent fetch operation fetched a record successfully, else it will return FALSE.

%NOTFOUND -This works oppositely to %FOUND it will return 'TRUE' if the most recent fetch operation could not able to fetch any record.

%ISOPEN   - It returns Boolean result 'TRUE' if the given cursor is already opened, else it returns 'FALSE'

%ROWCOUNT -It returns the numerical value. It gives the actual count of records that got affected by the DML activity.

=================================

1.4 for loop cursor application

"FOR LOOP" statement can be used for working with cursors. We can give the cursor name instead of range limit in the FOR loop statement so that the loop will work from the first record of the cursor to the last record of the cursor. The cursor variable, opening of cursor, fetching and closing of the cursor will be done implicitly by the FOR loop.

===================================

1.5 Difference between static and dynamic cursors

1.6 Ref cursors

PL/SQL Cursor Variables with REF CURSOR (oracletutorial.com) 

1.7 dynamic sql

Oracle PL/SQL Dynamic SQL Tutorial: Execute Immediate & DBMS_SQL (guru99.com)

1. execute immediate

EXECUTE IMMEDIATE(<SQL>)
[INTO<variable>]
[USING <bind_variable_value>]

2. DBMS _sql

PL/SQL provide the DBMS_SQL package that allows you to work with dynamic SQL. The process of creating and executing the dynamic SQL contains the following process.
OPEN CURSOR: The dynamic SQL will execute in the same way as a cursor. So in order to execute the SQL statement, we must open the cursor.
PARSE SQL: The next step is to parse the dynamic SQL. This process will just check the syntax and keep the query ready to execute.
BIND VARIABLE Values: The next step is to assign the values for bind variables if any.
DEFINE COLUMN: The next step is to define the column using their relative positions in the select statement.
EXECUTE: The next step is to execute the parsed query.
FETCH VALUES: The next step is to fetch the executed values.
CLOSE CURSOR: Once the results are fetched, the cursor should be closed.

==================================================================

2. Triggers

==================================================================

3. Exceptions

Oracle PL/SQL Exception Handling: Examples to Raise User-defined Exception (guru99.com)

==================================================================

4. what is NO COPY

5. Nested tables

6. Pragma autonomous transactions and its application

7.  Global temporary tables and transactions

8. Views

9. Collections, Bulk collect

Oracle PL/SQL BULK COLLECT: FORALL Example (guru99.com)

10. NVL, NVL2

11. what is RICEW , explain  with each example

- Reports, Interfaces, Conversions, Extensions, Workflow

12. Reports, triggers in reports 

13. Forms, why do you need Custom.pll , how and where do you use it, give example

14. Form personalization steps, what is profile option for this

15. wht is profile options

16. what is flex fields, dff, kff its differences

17. how to use OAF, can we change value run time, development steps of OAF. what is OAF MVC architecture.

18. what is a Workflow, how to customize workflow, development steps

19. what is XML report, define steps to build the same

20.  XML publisher, xml parser, ZPL with example, discoverer reports

21. Mandatory parameters in API

22. Seeded APIs - create customers, create orders, create PO etc

23. what is tkprof

24. performance tunning with examples, how to tune given query

25. what is business events in oracle, how to write code for the same

26. what is hook, what are types of hooks. Difference between hooks and business events. where do you use hooks

27. what is processformdata, what is controller

28. how to add picture in report

29. how to change language for report or program specific to country. explain with example.

30. explain Order to Cash flow with table details.

Drop ship orders- 

Items which are mot produced but procured from supplier and fulfilled.

the main setup here is the Item setup --> OM tab having source type as EXTERNAL

OM : Create sales order --> Book the order --> status - booked

click on Progress order --> Purchase Release eligible --> click OK --> now status - Awaiting shipping

Go to purchasing resp --> run program 'Purchase Release' sales order number as parameter --> run

once complete then

run program 'Requisition Import ' where parameter import source - Order Import 

important link - oracle drop ship order flow (erpschools.com)

Order management -->  Shipped --> awaiting billing --> AR interface --> AR invoice -->amount recieved --> create reciept  --> complete

here system automatically places order on supplier 

( approved supplier list, sourcing rules, assigning to sourcing rules, sourcing rule assignment sets, blanket purchase agreement)

31. How do you define a new order type

32. explain orders - CTO, PTO, ATO, Drop ship with examples

33. explain P2P cycle with table details

Oracle Apps: P2P(Procure-to-Pay) Cycle Tables with Joins (kommaoracleapplications.blogspot.com)

34. what are types of purchase orders, requisition types

35. what is match recipt, which tables do you check for this

36. can we define item on warehouse without defining in master org? tables involved in item definition.

37. how to check on hand quantity, tables for the same

38. what is bursting ? its application

39. WMS what is it ? its application

40. difference between WMS and inventory systems

41. what are month end closure activities (list all)

42. what are 3 Cs , define all different types of calendars.

43. when do you need to run inventory interface, what does it do

44. how do you define request set, explain steps. Can we hide mandatory parameters

45. what is XML gateway, how to set it up, explain tables.

48. what is FTP, how do you use it, what file can you read.

49. tables for AP invoices, Recipts

50. Finance cycle and its tables, cogs accounting what is it.

51. how to find out a program is run though which responsibility, explain by writing query. 

Ans - 

52. How to set Organization Context in R12

mo_global.init ('AR');

mo_globa.set_policy_context ('S', '&org_id') ;