AOL Refresh

Concurrent Program WHO Columns:
REQUEST_ID
PROGRAM_ID
PROGRAM_APPLICATION_ID
PROGRAM_UPDATE_DATE

Table Registration API:
Package Name : AD_DD
Procedure Names :
register_table
Register_column
Delete_table
Delete_column

USER PROFILE: The changeable options which affects the way your applications runs.
System Administrator sets the values for each users profile option.
FND_PROFILE.PUT(name, value)
FND_PROFILE.GET(name, value)

Multiple segment structure for single flexfield:
FF lets you define multiple segment structures for same flexfield.
Your FF can display different prompts and different structure depending on end user.
FF Structure is the particular arrangement of segments.

Concurrent program information:
select concurrent_program_id,
concurrent_program_name
from fnd_concurrent_programs;

select
request_id,
status_code,
concurrent_program_id,
number_of_arguments,
output_file_type
from
fnd_concurrent_requests;

Stored procedure as a concurrent program:
create procedure proc_name
( errbuf out varchar2,
retcode out varchar2,
arg1 in varchar2,
arg2 in varchar2…)
Restricted to 100 parameters.
ERRBUF: to return any error message.
RETCODDE: to return 0 for success, 1 for success with warning and 2 for error.

Submitting concurrent request:
Call FND_REQUEST.SUBMIT_REQUEST

To check request status:
Call FND_CONCURRENT.GET_REQUEST_STATUS
Steps for concurrent program:
1 executable
2 define
3 register/attach
4 SRS
NOTE:
On program define form there is a check box called “Run in SRS” which is checked by default.
If it is unchecked then your concurrent program will not appear in submit request window.

LOG/OUTPUT file locations:
PROD_TOP/log lxxxxxx.req
PROD_TOP/out oxxxxxx.req


FND_REQUEST.SUBMIT_REQUEST(application, prog_name, desc)

To submit REQUEST SET through API:
FND_SUBMIT_SET.REQUEST_SET(application, req_set_name) return boolean;

Request Set:
Request set allows you to submit several requests together using more than one execution path. [conditional execution of programs]. Is divided in stages, each stage can contain one or more concurrent request. One stage at a time is executed. All the concurrent programs in one stage are executed parallel. Depending on the result [completion status: SUCCESS, WARNING, ERROR] of first stage the next stage is launched for execution.

F L E X F I E L D
KFF:
Let me give you an example for KFF,
Take an example of mobile phone call. You make a call from your cell phone from Mumbai. Consider the data which is being associated with your cell phone location.
Your location will be identified as follows:
Country – Circle – State – City - Zone.
And the possible values would be.
India - West - MH - Mumbai - CST
This is nothing but a KFF. The collection of meaningful fields [Called SEGMENT] which provides KEY INFORMATION.

DFF:
Now additional [descriptive] information about you/ who is making call which is not as much important as the above one for making a call like your address, account number, payment details, unbilled amount and other will be captured in DFF.

Value Set:
The possible values for KFF field will be validated using value sets.
The types of value set are:

None
Independent
Dependent
Table type
Translatable dependent
Translatable independent
Special
Pair

FF Structure: is the arrangement of the fields [segment] used in FF. You can have multiple FF structure.

NOTES:
Every FF has at least one base table associated with it known as Combination Table which stores all the valid combinations of values in your KFF.

FF Qualifier: To identify particular segment/field of KFF.
Segment Qualifier: To identify value in particular segment.
Dynamic Insertion: If you allow the dynamic inserts [checkbox on FF form] while creating the KFF, you can directly insert the valid combination in combination base table using any other form than the main combination form.

Table validated value set: You can use your own table or any other table for valid set of values.

$FLEX$.value_set_name is used in where/order by clause where the value of current segment depends on value of previous segment. Ex WHERE user_id = :$FLEX$.sales_person_id

:block.field represents the form where your DFF appears.

$PROFILES$.profile_option_name can be used to pick value of user profile.
This cant be used with Accounting FF. Steps to create this value set:
1 create table if you want to use your own custom table. Shd must have PK. No duplicate records.
2 register this table using AD_DD.register_table API.
3 Give grants and create synonym in APPS.
4 Create value set using value set form.
5 In this form you can provide sql statement in TABLE NAME [separated by comma] field. [But if you are using some complex sql on table then better to create a view and provide the name of this view here]
6 If you provide column name in hidden ID field then FF saves the value of this column you specified instead of the displayed value.

Reporting on FF Data:
FF Views: Once you create and freeze your KFF a view is created with suffix of “_KFV” and “_DFV” in case of DFF.

Coding for Oracle Reports as a Concurrent Program:
USER EXITS: A program to be called from with in the oracle application, which when called takes the control out of the oracle application temporarily, performs some operation and returns the control to Oracle application.
In case of coding for Oracle Reports following are some of the User exits we will use:
FND SRWINIT
FND SRWEXIT : To reset the profile option values and de allocate the memory occupied by user exits.
FND FLEXSQL
FND FLEXIDVAL
FND FORMAT_CURRENCY

FND SRWINIT: must be the first USER EXIT called from Reports and should be called from before report trigger of Report. You need to create P_CONC_REQUEST_ID number (15) user parameter in Report.
- To set required profile option values.
- Also it allows the Report to use the correct organization [for multi org].
FND FLEXSQL: This allows you to use the flex field in your report.
FND FORMAT_CURRENCY: This formats the currency amount dynamically depending upon the precision of actual currency value.

Steps are as follows:
Define the parameter P_CONC_REQUEST_ID number(15)
Call srw.user_exit(‘FND SRWINIT’); from before report trigger of report.
Call srw.user_exit(‘FND SRWEXIT’); from after report trigger of report.

PLSQL Refresh

## What is the significance of the & and && operators in PL SQL ?
The & operator means that the PL SQL block requires user input for a variable. The && operator means that the value of this variable should be the same as inputted by the user previously for this same variable.

## Is there a PL/SQL Engine in SQL*Plus?
No. Unlike Oracle Forms, SQL*Plus does not have a PL/SQL engine. Thus, all your PL/SQL are send directly to the database engine for execution. This makes it much more efficient as SQL statements are not stripped off and send to the database individually.

## Describe Oracle database's physical and logical structure ?
Physical : Data files, Redo Log files, Control file.
Logical : Tables, Views, Table spaces, etc.

## What are the Pct Free and Pct Used?
Pct Free is used to denote the percentage of the free space that is to be left when creating a table. Similarly Pct Used is used to denote the percentage of the used space that is to be used when creating a tableeg.:: Pctfree 20, Pctused 40

## What is Row Chaining?
The data of a row in a table may not be able to fit the same data block. Data for row is stored in a chain of data blocks .

## SET SERVEROUTPUT
One can use the DBMS_OUTPUT package to write information to an output buffer. This buffer can be displayed on the screen from SQL*Plus by using the SET SERVEROUTPUT ON; command.
However, if you print too much, the output buffer will overflow. In that case, set the buffer size to a larger value, eg.: set serveroutput on size 200000

## Autonomous transactions:
As workaround, one can use autonomous transactions. Autonomous transactions execute separate from the current transaction.

Unlike regular triggers, autonomous triggers can contain COMMIT and ROLLBACK statements. Example:

SQL> CREATE OR REPLACE TRIGGER tab1_trig
AFTER insert ON tab1
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO log VALUES (SYSDATE, 'Insert on TAB1');
COMMIT; -- only allowed in autonomous triggers
END;
/

Trigger created.

SQL> INSERT INTO tab1 VALUES (1);

1 row created.

## What are the Back ground processes in Oracle and what are they?
1) This is one of the most frequently asked questions. There are basically 9 Processes but in a general system we need to mention the first five background processes. They do the house keeping activities for the Oracle and are common in any system.The various background processes in oracle are
a) Data Base Writer(DBWR) :: Data Base Writer Writes Modified blocks from Database buffer cache to Data Files. This is required since the data is not written whenever a transaction is committed.
b)LogWriter(LGWR) :: LogWriter writes the redo log entries to disk. Redo Log data is generated in redo log buffer of SGA. As transaction commits and log buffer fills, LGWR writes log entries into a online redo log file.
c) System Monitor(SMON) :: The System Monitor performs instance recovery at instance startup. This is useful for recovery from system failure.
d)Process Monitor(PMON) :: The Process Monitor performs process recovery when user Process fails. Pmon clears and Frees resources that process was using.
e) CheckPoint(CKPT) :: At Specified times, all modified database buffers in SGA are written to data files by DBWR at Checkpoints and Updating all data files and control files of database to indicate the most recent checkpoint.
f)Archieves(ARCH) :: The Archiver copies online redo log files to archival storal when they are busy.
g) Recoveror(RECO) :: The Recoveror is used to resolve the distributed transaction in network.
h) Dispatcher (Dnnn) :: The Dispatcher is useful in Multi Threaded Architecturei) Lckn :: We can have up to 10 lock processes for inter instance locking in parallel sql.

## Can one use dynamic SQL within PL/SQL? OR Can you use a DDL in a procedure ? How ?
From PL/SQL V2.1 one can use the DBMS_SQL package to execute dynamic SQL statements. Eg:
CREATE OR REPLACE PROCEDURE DYNSQLAS
cur integer;
rc integer;
BEGIN
cur := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cur,'CREATE TABLE X (Y DATE)', DBMS_SQL.NATIVE);
rc := DBMS_SQL.EXECUTE(cur);
DBMS_SQL.CLOSE_CURSOR(cur);
END;

## How can I protect my PL/SQL source code?
PL/SQL V2.2, available with Oracle7.2, implements a binary wrapper for PL/SQL programs to protect the source code. This is done via a standalone utility that transforms the PL/SQL source code into portable binary object code (somewhat larger than the original). This way you can distribute software without having to worry about exposing your proprietary algorithms and methods. SQL*Plus and SQL*DBA will still understand and know how to execute such scripts. Just be careful, there is no "decode" command available.The syntax is: wrap iname=myscript.sql oname=xxxx.yyy

## What is the difference between foreign key and reference key ?
Foreign key is the key i.e. attribute which refers to another table primary key.Reference key is the primary key of table referred by another table.

## Can dual table be deleted, dropped or altered or updated or inserted ?
Yes

## When to create indexes ?
To be created when table is queried for less than 2% or 4% to 25% of the table rows.

## What are the parts of a database trigger ?
The parts of a trigger are: A triggering event or statement A trigger restriction A trigger action15.What are the various types of database triggers ?There are 12 types of triggers, they are combination of :Insert, Delete and Update Triggers.Before and After Triggers.Row and Statement Triggers.(3*2*2=12)

## What are the values of :new and :old in Insert/Delete/Update Triggers ?
INSERT : new = new value, old = NULL
DELETE : new = NULL, old = old value
UPDATE : new = new value, old = old value

## What are mutating tables ?
When a table is in state of transition it is said to be mutating.
eg :: If a row has been deleted then the table is said to be mutating and no operations can be done on the table except select.

## What is Normalization ?
Normalization is the process of organizing the tables to remove the redundancy. There are mainly 5 Normalization rules.
a) 1 Normal Form :: A table is said to be in 1st Normal Form when the attributes are atomic
b) 2 Normal Form :: A table is said to be in 2nd Normal Form when all the candidate keys are dependant on the primary key
c) 3rd Normal Form :: A table is said to be third Normal form when it is not dependant transitively

## What’s a PL/SQL table? Its purpose and Advantages?
A. A PL/SQL table is one dimensional, indexed, unbounded sparsed collection of homogeneous
Data.
PLSQL tables are used to move data into and out of the database and between client side applications and stored sub-programs. They have attributes such as exits, prior, first, last, delete ,next . These attributes make PLSQL tables easier to use and applications easier to maintain.
Advantages:
PL\SQL tables give you the ability to hold multiple values in a structure in memory so that a PL\SQL block does not have to go to the database every time it needs to retrieve one of these values - it can retrieve it directly from the PL\SQL table in memory.
Global temporary tables act as performance enhancers when compared to standard tables as they greatly reduce the disk IO.
They also offer the ease-of-use of standard tables, since standard SQL can be used with them; no special array-processing syntax is required.

## What is a Cursor? How many types of Cursor are there?
A) Cursor is an identifier/name to a work area that we can interact with to access its information. A cursor points to the current row in the result set fetched. There are three types of cursors. They are
· Implicit cursors – created automatically by PL/SQL for all SQL-DML statements such as
Insert Update, delete and Select
· Explicit cursors – Created explicitly. They create a storage area where the set of rows
Returned by a query are placed.
· Dynamic Cursors – Ref Cursors( used for the runtime modification of the select querry).
Declaring the cursor, Opening the cursor, Fetching data , Closing the cursor(Releasing the work area) are the steps involved when using explicit cursors.

## What is the difference between Function and Procedure?
· Procedure is a sub program written to perform a set of actions and returns multiple values
Using out parameters or return no value at all.
· Function is a subprogram written to perform certain computations and return a single value.

## What are the modes for passing parameters to Oracle?
A) There are three modes for passing parameters to subprograms
· IN - An In-parameter lets you pass values to the subprogram being called.
In the subprogram it acts like a constant and cannot be assigned a value.
· OUT – An out-parameter lets you return values to the caller of the subprogram.
It acts like an initialized variable its value cannot be assigned to another variable or to itself.
· INOUT – An in-out parameter lets you pass initial values to the subprogram being called and returns updated values to the caller.

## What is a Pragma Exception_Init? Explain its usage?
A) Pragma Exception_Init is used to handle undefined exceptions. It issues a directive to the compiler asking it to associate an exception to the oracle error. There by displaying a specific error message pertaining to the error occurred.
Pragma Exception_Init (exception_name, oracle_error_name).

## What is a Raise and Raise Application Error?
A) Raise statement is used to raise a user defined exception.
B) A raise application error is a procedure belonging to dbms_standard package. It allows to display a user defined error message from a stored subprogram.

## How to display messages in Log file and Output file?
Using FND_FILE.PUT_LINE

## What is Explain Plan? How do u use Explain Plan in TOAD?
It is a utility provided by toad that gives the statistics about the performance of the query. It gives information such as number of full table scans occurred, cost, and usage of indexes

## What is a TK-PROF and its usage?
Tk-Prof is a utility that reads the trace files and generates more readable data that gives the statistics about the performance of the query on a line to line basis.

## What is Optimization? How many types of Optimization are there?
Rule based Optimization and Cost Based Optimization.

## What is the default optimization chosen by Oracle?
Cost based Optimization.

## What is the difference between When no data Found and cursor attribute % DATA FOUND?
When no Data Found is a predefined internal exception in PLSQL. Where as % Data found is a cursor attribute that returns YES when zero rows are retrieved and returns NO when at least one row is retrieved.

## Items are imported from the legacy system using the item import interface using the SRS. How are items imported using the UNIX /PLSQL commands with out using SRS?
From the operating system, use CONCSUB to submit a concurrent program. It's an easiest way to test a concurrent program.
Normally, CONCSUB submits a concurrent request and returns control to the OS prompt/shell script without waiting for the request to complete. The CONCSUB WAIT parameter can be used to make CONCSUB wait until the request has completed before returning control to the OS prompt/shell script
By using the WAIT token, the utility checks the request status every 60 seconds and returns to the operating system prompt upon completion of the request. concurrent manager does not abort, shut down, or start up until the concurrent request completes. If your concurrent program is compatible with itself, we can check it for data integrity and deadlocks by submitting it many times so that it runs concurrently with itself.

Syntax: CONCSUB [WAIT= [START=] [REPEAT_DAYS=] [REPEAT_END=]
To pass null parameters to CONCSUB, use '""' without spaces for each null parameter.
In words: single quote double quote double quote single quote
Following is an example of CONCSUB syntax with null parameters:
CONCSUB oe/oe OE 'Order Entry Super User' JWALSH CONCURRENT XOE XOEPACK 4 3 '""' 3

B) To Invoke a Concurrent Program using PL/SQL:
i) Just insert a row in FND_CONCURRENT_REQUESTS with the apropriate parameters and commit.
ii) Invoke the SUBMIT_REQUEST procedure in FND_REQUEST package.
FND_REQUEST.SUBMIT_REQUEST( 'AR', 'RAXMTR', '', '', FALSE, 'Autoinvoice Master Program', sc_time, FALSE, 1, 1020, 'VRP', '01-JAN-00', chr(0), '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '');

## What is the significance of _all tables?
_ALL tables are multi-org tables which are associated with the company as a whole. Multiple Organizations is enabled in Oracle Applications by partitioning some database tables by the Operating Unit. Other tables are shared across Operating Units (and therefore across set of books). Examples of Applications with partitioned tables are Oracle Payables, Oracle Purchasing, Oracle Receivables, Oracle Projects, Oracle Sales & Marketing etc. The name of each corresponding partitioned table is the view name appended by '_ALL'

## What are mutating tables? And what is mutating error?
A mutating table is a table that is currently being modified by an UPDATE, DELETE, or INSERT statement, or it is a table that might need to be updated by the effects of a declarative DELETE CASCADE referential integrity constraint.
A mutating error occurs when a trigger which fires when updation/deletion/insertion is done on a table A performs insertion/updation/deletion on the same table A. This error results in an infinite loop which is termed as a mutating error.

## What is a trace file?
when ever an internal error is detected by a process in oracle it dumps the information about the error into a trace file.
Alter session set sql_trace=TRUE

## When do you use Ref Cursors?
We base a query on a ref cursor when you want to:
i) More easily administer SQL
ii) Avoid the use of lexical parameters in your reports
iii) Share data sources with other applications, such as Form Builder
iv) Increase control and security
v) Encapsulate logic within a subprogram