1) Write a query to get the second maximum salary in a table?

A) We will use

Select max (sal) from EMP where sal < (Select max (sal) from EMP)

2) Write a query to get output in the format given below

Deptno Maxsal Employ

10 1500 scott

20 2000 empname

30 3000 empname

A) We will use

select deptno,ename,sal from emp e where sal = (select MAX(sal) from emp where

deptno = e. deptno)

3) Write a query to get EMPNAME,MANAGER,DEPTNO write single query?

A) Select w.empno,w.ename,e.ename,e.deptno from emp w,emp e where w.mgr = e.empno

4) Write a query for deleting DUPLICATEROWS

A) delete from table where(rowid, columnname) not in

(select min(rowid),columnname from table group by columnname);

5) What are the rules when you are dealing with Set operations?

A) We have 4 set operations are like 1.union :With out duplicates

2.union all:with duplicates

3.intersect:common rows from two or more queries

4.minus:present in first query and not in second query.

in set operations we are going to perform operations on select statements.

6) What are the types of indexes?

A) Indexes are used when we have more records in a table and we are searching records most of the time that indexes are usefull.

There are two types of INDEXES.

Benefits of indexes are

q Index is used to search for required rows quickly.

q Index occupies extra space. Index is stored separately from table.

q Index contains the values of key – column on which index is created – in the ascending order.

q Just like the page number in book index, Oracle index stores ROWID – a unique value to internally identify each row of the table. For each entry in the index a key and corresponding ROWID are stored.

q Oracle uses index only when it feels the index is going to improve performance of the query.

Note: ROWID is a unique value assigned to each row created in a table. Once ROWID is assigned to a row it doesn’t change during the lifetime of the row. Oracle access rows internally using ROWID.

1)B-TREE Index:For high cordinality fields these indexes will be created in index file all the key values are in sorting order.

2)BIT MAP Index: For low cordinality fields we will create bit map index.

CREATE [UNIQUE][BITMAP] INDEX ON TABLE(FNAME)

7) What are constraints what are table level constraints and column level constraints?

A)CONSTRAINTS ARE USED TO CHECK VALIDATIONS BEFORE INSERTIN DATA INTO TABLE.AND ALSO PROVIDE RELATIONSHIP BETWEEN TABLES.

TABLE LEVEL CONSTRAINTS:1. UNIQUE CONSTRAINT

2. PRiMARY KEY CONSTRAINT

3.CHECK CONSTRAINT

4.FOREIGN KEY CONSTRAINT

COLUMNLEVEL CONSTRAINTS: 1. UNIQUE CONSTRAINT

2. PRiMARY KEY CONSTRAINT

3.CHECK CONSTRAINT

4.FOREIGN KEY CONSTRAINT

5.NOT NULL CONSTRAINT

6.DEFAULT CONSTRAINT.

9) How can you disable an INDEX?

A) alter index indexname disable;

10) How can you find out View is updatable or not?

A) We can find out the view is up datable or not by seeing the definition of that view.

if the view is create base on a single table and it doesn't contain any aggregate functions

Then that view is updateable

If the is created based on a join stmt, or it contains any aggregate functions then that view

is not updateable.

11) What are the types of views?

A)There are 4 types of views

1) Inline view: When we write a query inside of another query from clause then we will call it as inline view.

2) Updateable view: if the view is created from a single table and if doesn't contain any aggregate functions then that view is called as updateable view.

3) Non updateable view: if the view is created from two or more tables and if it contains any aggregate functions, joins, union commands then that view is called as non updateable view.

4) Materialized view: MATERIALISED view definition includes any no of aggregates as well as any no of joins in several ways. behaves like an index. it is used to increase the query execution performance.

12) What are the snap shots and materialized views?

A) Snap shots: a snap shot is a replication of master table from a single point-in-time

Snapshots are updated by one or more master tables via individual batch updates are known as refresh.

MATERIALIZED VIEWS: materialized view will be created to access the base table information and it is not possible to do DML actions on that

The validations of the base table will be reflected in two ways

1980 Automatically using COMMITT option.

1981 Manually executing the refresh method.

SYNTAX: CREATE MATERIALIZED VIEW MVNAME

REFRESH [FAST/COMPLETE]

ON [COMMIT/DEMAND] AS

SELECT STMT;

13) What is the syntax for seaquence Cycle?

SEQUENCES ARE CREATED TO GENERATE SEQUENCE NO’S on primary key or unique keys IN ORACLE.

SYNTAX: CREATE SEQUENCE SEQNAME

[INCREMENT BY VALUE

START WITH VALUE

MAXVALUE VALUE/MAXVALUE

MINVALUE VALUE/MINVALUE

CYCLE/NOCYCLE

CACHE N/NO CACHE];

14) What are CBO and RBO modes?

A) CBO - costbased optimizer

RBO - rule based optimizer

It is like a comment which we will use to pass a message to engine where mentioning optimizer = RBO or CBO

15) What is sub query and correlated subquery and difference between these two?

A) Subquery: it is a type of query we will mention a query inside of another query where clause. In This main query will execute based on the result of the sub query.

Sub query is used to filtering data in select statement

B)Correlated subquery : It is also like a subquery .In the ordinary subquery the subquery will execute only once but in the correlated subquery sub query will executes how many times the main query executes.

16) What is the difference between IN & Exist?

A) IN: We will use this IN operator if we wants to find out a value in a list of values.

EXIST: We will use exist operator when we want to find out a value from Result of the subquery.

17) What is MUTATING ERRORS?

A) 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.

      ORA-04091: table APP is mutating, trigger/function may not see it
      ORA-06512: at "TRG_GET_ID_APP", line 4
      ORA-04088: error during execution of trigger 'TRG_GET_ID_APP'

18) GRANT and REVOKE syntaxes?

A) Grant syntax:

Grant privileges on tablename to username;

Revoke Syntax:

Revoke privileges From username on tablename;

19) What is the syntax of VIEW, SEQUENCE, INDEX, and SYNONYMS?

A) syntax view: create or replace view viewname as select * from tablename where condition

Sequence: create sequence sequencename

incremented by incrementor ,

start with startno,

maxvalue,

no cycle,

no cache;

index:create index indexname on tablename(columnname);

synonym:create (public/private) synonym synonymname for tablename.

20) Write query to get output given below?

Year count

1982 10

1983 12

A) select count(empno),to_char(hiredate,'yyyy') year from emp group by to_char(hiredate,'yyyy')

21) What are TO_DATE, TO_CHAR, NVL, DECODE, SUBSTR, LTRIM, and RTRIM?

A) To_date:

to char: used to format the DATE in the desired way.

NVL: NVL is used when we are dealing with null values if we want to show any another value isted of NULL then we will use NVL.

DECODE: decode is a function in sql it works like a IF-ELSE statement .

SUBSTR: If we want to get particular part of a string we will use substr.

SUBSTR('STRING',STARTINGPOS,LENGTH);

LTRIM: is used to remove the spaces before a string while camparing two strings.

RTRIM: is used to remove spaces after the string.

22) What four back ground process?

These are the processors running behind the scene and are being to perform certain maintainence activities or to deal with the abnormal conditions arising th lifetime of instance.

A) DATABASE WRITER : (DBW0 THROUGH DBW9 AND DBWA THROUGH DBWJ): This process writes dirty buffers present in the database buffer cache to data files.

B) LOGWRITER(LGWR):this process wrotes data from redo log buffers to redo log files on disk.

C) ARCHIVER:

D) SYSTEMMONITOR

E) PROCESSMONITOR

F) CHECKPOINT

G) LOCKMONITOR

23) If we delete the base table wether the synonym will there or not?if we use that synonym what type of error we will get?

A) IF we delete the base table even through synonym will exist.

If we use that synonym the following errors will come

ORA-00980: synonym translation is no longer valid

ORA-00903: invalid table name

ORA-04043: object SCOTT.T1 does not exist

24) How to create a view with out a table? After creation of view can we insert the data or not?

A) we can create view with out a table by using force command.

Syntax: create force view viewname as select * from t1;

Here t1 table not exit;

25) How to select definition of procedure from sql which has alredy created?

A) we can get the data by using SPOOL command in the following way

SPOOL d:\oracle\data.txt

Select text from user_source where name like 'proc name';

SPOOL OFF;

26) What Rollback,Savepoint and Commit?

A) ROLLBACK: used to cancel the all previous unsaved transactions.

COMMIT: used to save the data perminently.

SAVEPOINT: if we want to save upto the particular transactions then we will divide the transactions with savepoints.

27)Can we change the datatype and size in a table when data is there and when data is not there?

A) Yes we can change the data type and size when there is no data

When there is data we can change the size but we cannot change the data type.

Yes we can add a column to a table.

PL/SQL DOCUMENTATION

1. EXPLAIN PL/SQL, PL/SQLBLOCK?

PL/SQL is a procedural language that has both interactive SQL and procedural language constructs such as iterations, conditional branching.

PL/SQL block is a block-structured language. Each block is supposed to perform one logical unit of job.

2. MAIN BLOCKS OF PL/SQL?

PL/SQL block is having 3 parts

· Declaration part

· Executable part

· Exception handling part

Declaration part: PL/SQL enforces the declaration of variables before their use in executable portion.

All variables (or) constants have to be declared in this block.

Syntax: variable name data type;

Executable part: this is main section of the block, all the procedural and SQL statements are defined here.

Exception handling part: this is used for responding to runtime errors encountered by a program.

MANDATORY BLOCKS IN PL/SQL: executable block in PL/SQL is the mandatory block other two blocks declarative and exception blocks are optional blocks.

3. WHAT IS NAMED BLOCK?

Named blocks are the blocks that have a name associated with 3 types

· Labeled blocks: blocks with a label that gives the block name.

· Sub program: consists of procedures and functions.

· Triggers: consists of pl/sql block that is associated with an event that occurs in the database.

4. WHAT IS ANONYMOUS BLOCK?

These blocks are generally constructed dynamically and execute only once.

Block is often issued from a client program to call a sub program in the database.

5. EXPLAIN % TYPE, %ROWTYPE IN PL/SQL?

% TYPE: it is used to give data type of predefined variable and database column.

Ex: declare

Item code number (10);

I code item code% type;

%ROWTYPE: it is used to provide record data type to a variable.

The variable can stop row of the table (or) row fetched from the cursor.

6. DATA TYPES IN PL/SQL?

· Scalar data type: number, character, Boolean, date/time.

· Composite data type: table, record.

· Reference data type: ref cursor.

· Lob types: (large objects) Bfile (variable stores locator of the file)

BLob (for storing large raw data like graphics or sound data)

Clob(stores location, which provides location of data)

7. EXPLIAIN PL/SQL TABLES, VARRAYS, NESTED TABLES?

PL/SQL TABLES: these are temporary array like objects used in pl/sql block.

These can have one column & a primary key.

These are declared in the declarative part of any block, sub program

Or package.

Syntax: STEP1: type<name of type> is table of<col def> index by binary-integer.

STEP2: <pl/sql-tablename> <type name>

NESTED TABLES: similarly to PL/SQL block along with adding the ability to store

nested tables within a database table will be there.

Syntax: type table name is table of table type;

VARRAYS: this are implemented differently, elements are inserted into varray starting at index1 upto maximum length declared in varray.

Syntax: type type-name is varry(max-size) of element-type(not null);

8. CAN WE PASS PL/SQL TABLE AS APARAMETER TO ANOTHER PROCEDURE OR NOT, IF SOHOW WILL IT BE PASSED? GIVE THE SYNTAX?

9. EXPLAIN AUTONOMOUS TRANSACTION, RESTRICT-REFERENCE AND EXCEPTION-INIT?

AUTONOMOUS TRANSACTION: it is used when ever in a transaction with in another transaction should be committed or rollback irrespective of parent transaction commit or rollback.

RESTRICT-REFERENCE: it is used to assert the purity level for the user-defined functions.

Syntax: PRAGMA RESTRICT-REFERENCES (function-name, [rnds], [wnds], [rnps], [wnps])

EXCEPTION-INIT: used for associating a named exception with in a particular oracle error

Syntax: PRAGMA EXCEPTION-INIT (exception-name, oracle error number)

10. WHAT IS PRAGMA?

Pragmas are compiler directives, it serves as instructions to the pl/sql compiler.

The compiler will act on the pragma during the compilation of the block.

11. WHAT IS EXCEPTION HANDLING IN PL/SQL& TYPES OF EXCEPTION?

Exception handling is used to handle the errors according to users way and functions

It will be used to generate error messages and replacing default messages.

These are 2 types standard & user defined exceptions.

STANDARD EXCEPTIONS 0R BUILT IN EXCEPTIONS: -

Oracle package standard had defined exceptions for certain common errors

Some of them are:

· Too-many-rows (ora-01422)

· No-data-found (ora-1403)

· Value-error (ora-06502)

· Zero-divide (ora-01476)

· Invalid-number (ora-01722)

· Dup-value-on-index (ora-00001)

· Program-error (ora-06501)

USER DEFINED EXCEPTIONS: The user defines these exceptions and these are used to take care of abnormal conditions that are application specific.

12. EXPLAIN SQL CODE & SQLERRM?

These are functions, which return error code and error message of the recent error.

SQL CODE: it returns error code as negative number.

For NO-DATA-FOUND it returns ‘+100’.

SQL ERRRM: it returns length of the messages in 512 characters, which includes code, message, tablename&column name.

Syntax: ercode: =sqlcode;

Er msg: =sqlerrm;

Insert into error table values (errcode, ermsg);

13. WHAT IS RAISE-APPLICATION-ERROR?

This is a procedure used to generate user-defined errors.

Syntax: raise-application-error (errorcode, errormessage, true/false);

Error code- (range is –20000 to-20999).

Error message (length is 2048 bytes).

True/false- true indicates error is put in stack

False is mentioned then the error replace all the previous errors.

14. What is a cursor?

Cursor is a named private SQL area from where information can be accessed. Cursors are required to process rows individually for queries returning multiple rows.

15. What is a cursor for loop?

Cursor for loop implicitly declares %ROWTYPE as loop index, opens a cursor, fetches rows of values from active set into fields in the record and closes when all the records have been processed.

16. For Update of Clause: -when declaring the cursor itself we will be mentioning for update clause then we can update the records inside of the cursor.

17. Where Current Of Clause: -

18. PROCEDURE: -A procedure is a logically grouped set of SQL and PL/SQL statements that perform a specific task. it may or may not return a value.

Procedures are made up of

· Declaration part

· Executable part

· Exceptional part

Here declarative part and executable part are mandatory while exceptional part is optional.

Syntax: -CREATE OR REPLACE PROCEDURE procedure name {IN, OUT, INOUT}

{IS, AS}

Variable declaration;

Constant declaration;

Begin

PL/SQL subprogram body;

Exception

Exception block;

End;

19. FUNCTION: - A function is a logically grouped set of SQL and PL/SQL statements that perform a specific task. It returns a value.

Functions having

Declaration block

Executable block

Exception block

Syntax: - CREATE OR REPLACE FUNCTION function name {IN}

Return data type {IS, AS}

Variable declaration;

Constant declaration;

Begin

PL/SQL subprogram body;

Exception

Exception block;

End;

20. Difference between procedure and function?

· Procedures may or may not return a value but function should return a value

· Procedures we cannot use inside of select statement

Functions are used inside of select statement

21. PARAMETER MODES (IN, OUT, INOUT)

IN: - when we pass the parameter in IN mode that will work like a constant inside a procedure.

OUT: -this is used to return a value.

INOUT: -this will be using in both way.

22. ACTUAL PARAMETERS: -while calling the procedure will pass the values this will be calling it as actual parameters

The procedure declaration variables will be receiving these values called FORMAL parameters

23. PROCEDURE OVERLOADING: -multiple procedures that are declared with the same name are called overloading procedures.

24. FUNCTION OVERLOADING: - multiple functions that are declared with the same name are called overloading functions.

25. PROCEDURE, FUNCTION FORWARD DECLARATION: -

26. PACKAGE: -A package is an oracle object, which holds other objects with in it

these objects may be

Procedures

· Functions

· Cursors , which are logically related.

· Variables

· Constants

27. COMPONENTS OF PACKAGE: -

A package has usually two components

Specification

Body

A package specification declares the types, memory variables, constants, exceptions, cursors and subprograms that are a variable for use.

A package body fully defines cursors, procedures and thus implements the specification.

28. Package body with out specification is possible or not?

29. Can we define cursor inside with out package? If so how to call the cursor?

30. What is cursor variable?

31. We created specification and body, if we delete specification whether the body will present or not?

32.we have package body and specification inside of the package we are writing procedure to insert some thing to a table, if we delete the table, the above package will valid or not?

33.we have package and we have grants to execute that package inside of that we have table, here we don’t have privileges to this table? Whether this table will execute or not?

34. TRIGGERS:

Trigger is a pl/sql block, which will fire automatically whenever some event occurs like insert, update and delete.

Types of triggers: -

Event

Insert

Update level

Delete

Row level

Time statement level

Before

After

Row level triggers: -it will fire for each row

Statement level triggers: -it will fire only once for the whole statements.

35. Can we use DDL command inside of trigger? If not then what is the alternative.

36. TRIGGER PREDICATES?

These are 3 types

· Inserting

· Updating

· Deleting

Whenever we want to do inserting inside of the trigger that time we will be using inserting predicate trigger.llly for updating and deleting.

37. : NEW and :OLD

Both will be used in triggers to get the new and old values.

In case of update both NEW and OLD are valid.

In case of delete only OLD is valid.

In case of insert only NEW is valid.

Both: NEW and :OLD will work only for row level triggers.

REPORTS:-

1) What is ANCHOR?

A) These are used to determine the vertical and horizontal positioning of the child object relative to its position.

Tool which we will use in the layout to destroy the field horizontally or vertically whenever field gets null value.

When we have multiple fields in layout if any one of the field gets null value the next field should gets automatically adjusted in that place that time we will use anchor.

2) What is frame?

A) Frame is the place in the layout model where we place an object to display only once in the report output.

3) What is repeating frame?

A) It is also a place in the layout editor where we place an object to display repeatedly

In report output.

4) What are action, format and validation triggers?

A) ACTION TRIGGERS: action triggers are pl/sql procedures executed when button is selected

EX: calling a report

FORMAT TRIGGERS: format triggers are pl/sql functions which we will use to display layout object in the report.

The return type is always true or false.

EX: no data found we will write a validation trigger for this boiler plate text.

VLIDTION TRIGGERS: we will use to validate the lov values in the parameter form.

5) What are Report Triggers?

A) Report triggers are 5 types. They are

1) Before parameter form.

2) After parameter form.

3) Before report trigger.

4) Between pages.

5) After Report trigger

6) What is the difference between after parameter form and before report trigger?

A) After parameter for will fire in Parsed time.

Before report trigger will fire in the execution time.

7) What are Bind and Lexical parameters?

A Bind parameter: Bind parameter is a variable which we will use to pass the value.

‘:’ before any variable in a query is called as bind variable.

Lexical parameters: Lexical parameter is a parameter which we will use inside of a query. These parameters we can use any where inside of query.

EX: select, from, where, order by

8) What are system parameters?

A) There are around 78 system parameters. Some of them are

1) Background

2) Currency

3) Copies

4) Decimal

5) Desformat

6) Desname

7) Destype

8) Mode

9) Orientation

10) Destintion

9) What are formula, summary and placeholder columns?

A) Formula column: It is a pl/sql block which will calculate a value based on our logic and it will return one value.

Summary column: It is a pl/sql block which we will use to print the result of aggregate functions like sum, avg and count either at page level or at report level.

Placeholder column: It is a column which will have the data type and value. It works like global variable in reports.

If we want to return more than one value then we will use placeholder column in formula column.

10) How to run a report from a report?

A) We will place button in the repeating frame of layout editor. And in that button code we will write code as follows

Srw.run_report (‘path of .rdf file’, paramform = no, dept = “‘||:deptno||’”’);

Save the report and generate it.

11) What is user exit?

A) User exit is an program which will transfer the control from report execution to another third generation language it will the required data & it will complete the remaining report execution process.

12) What is srw.do_sql, srw.message, srw.referene.srw.program_abort?

A) srw.do_sql: used to execute DDL commands in reports.

Syntax: srw.do_sql (‘create table tname’);

Srw.message: used to display message in reports.

Srw.reference: used to refer the variables in formula columns.

Srw.program_abort: This exception stops report execution and raises the following exceptions.

Rep-1419 pl/sql program aborted

13) How to execute DDL commands in reports?

A) We will execute the DDL commands in reports by using the following user exit.

Srw.do_sql (‘create table tname ()’);

14) How to change the layout dynamically?

A) A lay out can be changed dynamically by passing the parameters.

15) How to implement lexical parameters in reports?

A) & before any parameter is called as lexical parameters. We can use these parameters in any class of the query

Ex: select, where, order by

16) What is the report global variable?

A) Place holder column is the report global variable. We can return more than one value by using place holder column.

17) What is matrix report?

A) Display information row ,column and cell format

18) The report output is 10 pages then how many times between pages report trigger will fire?

A) It will fire 8 times. Between pages report trigger will not fire for 1^st and last page.

19) Report will not have any parameters then before and after parameters will fire or not?

A) yes

REPORTS

1. What is bind variable and lexical variable?

Bind variable in report are parameters that are referenced with a colon in front of them, for example, :P_customer_no. This is considered a Bind reference. If you reference the same parameter proceeded by an ampersand, as & p_customer_no. Then its considered to be a lexical reference. A bind paramerter can only contain one value, while a lexical parameter can replace an entire clause example order by where clause.

2. What is the use of n anchor in the report?

Anchors are used for determining the vertical and horizontal positioning of a child object relative to its parent.

3. What is placeholder column?

Placeholder column datatype and a value can be set in PL/SQL like reports triggers or formula column.

4. What is a data link?

Data link relate the results of multiple queries. A data link causes the child query to be executed once for instance of its parent group.

5. What are the report triggers?

a) Report triggers execute at specific times during the execution and formatting of your report

b) After parameter from trigger

c) After report trigger

d) Before parameter form trigger

e) Before report trigger

f) Between pages trigger.

6. What is the validation trigger in report parameters property

Validation triggers are executed when parameter values are specified on the command line and when u accept the runtime parameter form. This is used for validating parameter foe example “Todate” can’t be earlier than “from date” etc.

7. What is confine mode?

In layout model if confine mode is on child objects can’t be moved outside their enclosing parent object and when it is off child object can be moved outside their enclosing parent objects. Toolbar it shows locks symbol.

8. What is flex mode?

In layout model when it is on parent borders “stretch” when child object are moved against them. And when it is off parent borders remain fixed when child object are moved against them.

FORMS

1. What are alert in D2k forms?

An alert is a model window that displays a message notifying of some application condition. For example, do you want to save changes? Are you sure u want to exit? Or customer name can’t be bland ….

2. What is property class?

A property class is an object that contains a list of properties and their settings. Other objects can be based on property class. An object based on a property class can inherit the setting of any property in the class that makes sense for that object.

3. What is the difference b/w property class and visual attribute?

Visual attributes only for the visual properties of the item like font, color, whereas property class is for all the possible properties of objects.

4. What is an LOV?

LOV is a scrollable popup window that provides the use with selection list.

5. What are record groups and what are its types?

a) A record group is an internal form builder data structure that has a column/row framework similar to a database table.

b) Query record group – associated select statement

c) Non query Record group – doesn’t have query but can be changed programmatically.

d) Static record group – cant be changed programmatically.

6. What is restricted built_ins?

Any built_ins subprogram that initiates navigation is restricted. This includes subprograms that move the input focus from one item to another and those that involve database transactions. Restricted build_ins are not allowed in trigger that fire in response to internal navigation.

7. What are categories of trigger?

a) block – processing triggers. Related to record mgmt in a block eg: when clear block.

b) Interface event triggers : interface event triggers fire in response to events that occur in the form interface eg: when button pressed. When checkbox changed.

c) Master_detail triggers : from builder generates master/detail triggers automatically when a master/detail relation is defined b/w blocks. This is to enforce co-ordination b/w 2 blocks. For example, on clear details.

d) Message handling triggers : message handling triggers fire in response to default messaging events. To trap or recover an error. Eg on_error, on_message

e) Navigational triggers : navigational triggers fire in response to navigational events. For instance clicking on a text item from another block eg: pre block, post text item, when new item instance.

f) Query_item trigger : Eg; pre query and post query

g) Transactional triggers : Fire in response to events that occur as a form interacts with the database. Eg: on_delete, pre_commit

h) Validation trigger : when form validated data in item or record e: when validate item.

8. What is the sequence of events fired while cursor moves from an item from i1st block to an item in 2^ndblock?

When validate item of that item A

Post_text_item of A

When validate record

Post record

Post block

Pre block

Pre record

Pre text item

When_new_block_instance

When_new_Record_Instance

When_new_item_instance

9. what are types of canvas?

a) Content : The default specifies that the canvas should occupy the entire content area of the window to which it is assigned.

b) Stacked : Specifies that the canvas should be displayed in its window at the same time as the window’s content canvas. They are usually displayed programmatically and overlay some portion of the content view displayed in the same window

c) Vertical toolbar canvas : specifies that the canvas should be displayed as a vertical toolbar under the menu bar of the window.

d) Horizontal toolbar canvas : specifies that the canvas should be displayed as a horizontal toolbar at the left side of the window to which it is assigned.

10. How do I attach menu to a form?

Form à properties àfunctional à menu source àFile

Forms à Properties àfunctional à menu module àname of menu (main menu

1. What are 2 types of data blocks object?

a) Data blocks : data blocks are associated with data within a database. Data blocks can be based on database table, views, procedure or transactional triggers.

b) Control blocks : in contrast a control block is not associated with the database and the item in or control block do not relate to table columns within a database.

2. How do I dynamically change the title of window?

a) set_window_property built-in

3. Name few system variables in forms?

A system variable is a form builder variable that keeps track of an internal form builder state. For example, system.Block_status, system, mode, system.from_status.

1. How to attach reports in oracle application?

The steps are as follows:

a) Design you report

b) Generate the executable file of the report.

c) Move the executable as well as source file to the appropriate products folder.

d) Register the report as concurrent executable

e) Define the concurrent program for the executable registered

f) Add the concurrent program to the request group of the responsibility

2. What are different report triggers and what is their firing sequence?

There are fiver report trigger:

a) Before report

b) After report

c) Before parameter form

d) After parameter form

e) Between pages

The firing sequence for report trigger is

Before parameter form – After parameter form – before report – between pages – after report.

APPS FAQ’S

How to register a report?

a) Register concurrent àProgram à executable

b) Register concurrent àprogram à Define

c) Attach it to appropriate request group security à responsibility àRequest

d) FTP RDF in respective top/report/US

2. How to CREATE a VALUE SET? What are the different types of value sets?

Enter Application à Validation àSet

Types of value set

a) Independent

b) Dependent

c) Table

d) Pair

e) SPECIAL DELIVERY translate dependent

f) Translatable independent

3. Name few types of execution method in concurrent program executable

a) Oracle reports

b) Sql * Plus

c) Host

d) Java stored procedure

e) Pl/sql stored procedure

f) SQL * Loader

g) Spawned

h) Java CCP

i) Multilanguage function

j) Immediate

k) Request set stage function

4. How to register a form?

a) Define Application àform

b) Define Application àfunction à give link to form defined in step I

c) Go to Application àmenu à Attach function to menu

d) FTP from to AU_TOP, generate it and copy to respective TOP

5. What are the steps to develp a form>

a) Copy appstand.fmh, Template.FMB, required pll in local directory

b) Change the registry for pll path. Save template.fmb as the new form name from name and start developing the form

c) FTP form in AU_TOP and generate fmb using f60gen and copy.fmx in respective top/forms/us directory

6. what is the use of custom.pll?

Custom.pll is used for customizations such as form, enforcing business rules and disabling fields that are not required for site without modifying standard apps forms.

7. How to PROGRAMATICALLY submit the request?

a) with the help of standard API

Find_Request. Submit_request

8. What is request set?

With the help of request set we can submit several requests together using multiple execution paths. Its collection of concurrent programs like reports procedures grouped together.

9. What are user exists in reports? What are user exist available in apps?

a) A user exit is a program written and then linked in to the report builder executable user exist are written when content need to be passed from report builder to that pgm, which performs some function and then returns control to report builder.

1. FND SRWINIT, FND SRWEXIT, FND FORMAT_CURRENCY, FND FLEXIDVAL, FND FLEXSQL.

10 What is the API used for file I/o operation ? or which API is used to write to request log and request output?

a) fnd_file.put_line (Fnd_file.log, ‘message’);

b) fnd_file.put_line (fnd_file.out. ‘message’);

11. how do I programmatically capture application user_id?

Fnd_profile.value (‘user_id’) or fnd_global.user_id.

12. what are flexfields?

A flexfield is a field made up of segments . each segment has a name and a set of valid values. There are two types of ff’s: key ff, DFF

13. Which are the 2 parameters required to be defined when a program is registered as pl/sql stored procedure

ERRBUF, RETCODE

14. can we register executable/concurrent program programmatically then how?

Yes we can. It can be done with standard package fnd_program, fnd_program.executable, fnd_program.register.

15. what changes need to be made if a setup is changed from a single org to multi org?

Org_id should be added as a parameter in a report and report should be run for specific org_id only

16. What are sub functions? How is it different from form?

A sub function is a securable subset of a forms functionally

a) forms appear in a navigator window and can be navigated to sub functions do not appear in the navigator window and cant be navigated to

b) forms can exist on this own sub functions can only be called by ____ embodied within a form, they cant exist on their own

17. what is message dictionary?

Message dictionary allows defining messages which can be used in application without hard coding them into forms or programs.

18. What is the token in concurrent à program àparameters window?

For a parameter in an oracle report program, they keyword is parameter specified here. The value is case sensitive for example P_CUSTOMER_NO

19. What are different validation defaults types and default value in current àprogram à parameter window?

a) constant

b) profile

c) SQL statement

d) Segment

20 I have a concurrent program that involves lot of inserts and updates on a huge basis where do I specify rollback segment etc

concurrent à program àsession control

21. How do I change the priority of my concurrent program?

Log on as system admin concurrent à program à program àpriority, enter numerical value in this field so it will take the request which has higher priority.

22. What is incompatibility?

When a program or list of programs is defined as incompatible means that defined program is not compatible with incompatible program list and cant run simultaneously in same conflict domain.

23. What is data group?

A data group defines the mapping b/w oracle applications and oracle ID’s. A data group determines oracle database accounts a responsibilities forms, concurrent programs, and reports connect to

SQL /DATABASE

1. What is the difference b/w subquery and correlated subquery?

a) In a normal subquery, the inner query is executed first and then the result are passed off to the parent query.

b) In a correlated subquery, the outer query is executed first and process each row, the subquery is using. The info supplies by the result of the outer query.

2. How to delete duplicate rows from a table?

Delete test t1 where rowed< (Select max(rowed) from test t2 where t2.col1 = t1.col and t2.col2 = t1.col1.

3. what are the types of database triggers?

Ro. Total no of database triggers are 12. They are

a) Row level : once for every row affected by the triggering statement such as a trigger fired by an update statement that updates many rows.

b) Statement level : once for the triggering statement, no matters be many rows it affects.

c) Before : before triggering statement is executed.

d) After: After triggering statement is executed.

e) Instead of : triggers provider a transparent way of modifying that cant be modified through DML statement.

4. what is mutating error on a table?

It happens when a trigger on a table tries to insert, update or ever select the table of where trigger is being executed.

5. What is synonym? What is view?

A synonym is an alias for any table, sequence, procedure function or package. It requires no storage other than its definitions in the data dictionary.

A view is stored query, from one or more tables.

6. What is database link?

A database link is a pointer that defines a one_way communication path from an oracle database server to another database server

7. What is a dynamic SQL?

DDL statement can’t be used within PL/SQL using “Execute Immediate”

8. What are set operator?

Set operators combine the result of two component queries into a single result queries containing set operator are called components queries

UNION : all rows selected by either query

UNION ALL : All rows selected by either query, including all duplicate

INTERSECT : All distinct rows selected by both queries

MINUS : All distinct rows selected by the first query but not the second.

9, What is savepoint?

Savepoint are intermediate markers within the context of a transaction savepoint divide a long transaction into smaller parts we then have to option later of rolling back work performed before the current point in the transaction but after a declared savepoint within the transaction.

10. What are the benefits of using package? Name few oracle supplied package

An entire package is loaded into memory when a procedure within the package is called for the first time. This load is completed in one operation, as opposed to the separate loads required for stand alone procedure. A package body can be replaced and recompiled without affecting the specification. Definition of procedure/variable can be private or public. For example I have 5 procedure out of 3 procedures can be used by a DBMS_SQL, DBMS_JOB, UTL_FILE.

11. What is deadlock?

A deadlock can occur when two or more user are waiting for data locked by each other. Deadlocks prevent some transactions from continuing to work.

3. What is the use of cursors in PL/SQL? What is REF Cursor?

The cursor is used to handle multiple row query in PL/SQL. Oracle uses implicit cursors to handle its queries. Oracle uses unnamed memory spaces to store data used in implicit cursors, with REF cursors you can define a cursor variable, which will point to that memory space and can be used like pointers in our 3GL’s

4. What is record group?

Record group are used with LOV’s to hold SQL query for your list of values. The record group can contain static data as well it can access dates from database tables through sql queries

________________________________________________________________________

1. What is flexfield? What are DFF& KFF?

In oracle application field made up of segments each segment has Assigned name and a set of valid values .oracle application uses flexfield to capture into about your organization

2. What are Autonomous Transaction? Give Scenario where you have used Autonomous transaction In your report ?

An Autonomous transaction is an independent transaction started by another transaction , the main transaction ,Autonomous transaction lets you suspend the main transaction do SQL operations ,commit or rollback those operation then resume the main transaction Once started an autonomous transaction fully independent .It shares no locks ,resources .or commit dependencies with the main transaction so ,you can log events increment retry counters ,and soon even if the main transaction rolls back More important, Autonomous transaction help you build modules reusable software component . for example Stored procedures can start and finish autonomous transaction there own .A calling application need not know about a procedures autonomous operations, and the procedures need not know about the application transaction content.

Scenario: you can use autonomous transaction in your report for writing error message in your database table.

3. What is the use of trigger in the form?

Triggers are used in forms for event handling u can write PL/SQL code in trigger to respond to a particular event occurred in your forms like when user presses a button or when he commits the form

The different types to triggers available I forms are

a)Key Trigger

b)Navigational Trigger

c)Transaction triggers

d)Message Triggers

e)Error triggers

f)Query triggers

4. What is the use of temp table and interface table ?

Temporary table are used in I/F programs to hold the intermediate data. The data is loaded into temporary first and then after validation through the PL/SQL program, the data is loaded into the interface table

5. What are the steps to register concurrent program in APPS?

The steps to register the concurrent prom in APPS are follows

a)Register the prom as concurrent prom for the executable

b) Define the concurrent prom for the executable registered

C)Add the concurrent program to the request group of the responsibilities

6. How to pass parameters to a report ? Do you have to register them with AOL?

U can define parameters in the define concurrent prom form there is no need to register the parameters with AOL .But you may have to register the value sets for those parameters

Do you have to register feeder program of interface to AOL?

Yes, U have to register the feeder program as concurrent prom to APPS

7. What are the forms customization steps

Steps are as follows

a)Copy the “Template fmb”1 and “Appstand.fmb” from AU_top/Forms/us. Put it in custom directory .

the Libraries(FNDSQF,APPCORE,APPDAYPK,GLOBE,CUSTOM,JE,JA,VERT)are automatically attached

b)Create/open new forms .then customise

c)save this form in corresponding module.

8. How to use flexfield In report?

There are two ways to use flexfield in reports one is to use the views(table name+`_kfv`or `_dfv`) created by apps, and use the concatenated segment column that holds the concatenated segments of the key or descriptive flexfield

(or)

To use the FND user exits provided by apps

9. what is KFF, DFF?

KFF : # unique identifiers, storing key into # used for entering and displaying key into

For example oracle general uses a KFF called Accounting flex field to uniquely identify a general account.

DFF : # to capture additional info # to provide expansion space on your form with the help of []. [] represents DFF

10 Difference b/w KFF and DFF

KFF	DFF
Unique identifiers	To capture extra info
KFF are stored in segments	Stored in attributes
For KFF there are FF qualifier and segment qualifiers	Context_sensitive ff is a feature of dff. (DFF)

_______________________________________________________________________

1. Will you attach reports in apps?

2. How will you attach forms in apps?

3. what is the use of token in reports?

4, what are various execution methods in reports?

5. How will you get set of books id dynamically in reports?

By using profile option called GL_SET_Of_Books_id

6. How will u capture AFF in reports?

By using user exits

7. What are dynamic insertion?

8. What is code combination Id?

9. Custom.Pll various events in custom.PLL

Zoom_available, custom.standard, Custom.event.

10. When u defined concurrent program u defined in compatibilities

what is the meaning of incompatibilities

simultaneously cant allow running programs

11. What is hierarchy of multi_org?

Business groups à sob àle à operating unit à inv organisation

12. What is difference b/w org_id, organisation_ID

ORG_ID is an operatin unit

Organisation_ID is inventory organisation.

13. What are profile options?

Defines the way application behaves ( more than 200 types)

14. Value set. And validation types

value set define suitable values for your segments

table, none, dependent, independent, special, pair

15. What is flexfield qualifiers?

Additional properties for your segment

16. What is your structure of AFF?

17. How will u enable DFF?

18. How many segments are in AFF?

Minimum, maximum

19. What are user exits?

20. When u defined CCP there is one checkbox use in SRS what is meaning of this suppose I do now want to call report through SRS how will I call report then?

SRS:= (Standard Request submission)

21. What is difference b/w request group and data group?

Request group : group is set of CCP and request sets

Data Group : integrates all your oracle apps modules

22. What is meaning of $flex $dollar

using this we call a value set with another value set.

_______________________________________________________________________

TRIGGERS: is a Stored Procedure that is fired when a DML operation is performed.

Types: before, after, for each row, for each statement

Instead of Triggers: This trigger is defined on a view rather than on a table.

INDEX: It is a database object used to improve the performance of data retrieval.

VIEW: It is a virtual table based on the actual table.

PACKAGE: is a PL/SQL construct that allows related objects to be stored together. Contains package specification and body. Ex: DBMS_SQL, DBMS_JOBS

FUNCTION: is a object that takes one or more arguments and returns only one value.

PROCEDURES: Can return more than one parameters. Function always returns one value whereas procedure may or may not return a value.

TYPES OF BLOCKS: 1.Anonymous 2.Named 3.Subprograms (Procedures, Functions) 4.Triggers

TYPES OF TRIGGERS:

Insert ---| |- Before -| |-- each row

Update ---|---| After |---|

Delete ---| |_ -| |-- statement level

PURITY LEVEL: What data structure the function can read or modify is based on purity level.

WNDS -> No Database State WNPS -> Write no package State

DELETE DUPLICATE ROWS: Delete from emp where rowid not in (select max(rowid) from emp group by empno);

EXCEPTIONS: is an identifier in PL/SQL that is raised during the execution of block that terminates its main body of actions 1.Oracle error occurs 2.User defined error

PREDEFINED EXCEPTION: 1.NO_DATA_FOUND 2.TOO_MANY_ROWS 3.INVALID_CURSOR 4.ZERO_DEVIDE

Function for trapping the user defined exception: sqlcode & sqlerrm

USERDEFINED EXCEPTION: sql%notfound, Raise exception

If SQL%NOTFOUND THEN

RAISE APPLICATION_ERROR('Not Valid');

END IF;

PL/SQL CURSOR: Two types 1.Implicit 2.Explisit

In order to execution of sql statement Oracle will allocate some memory area called as context area.

Cursor is pointer to this context area.

Cursor Attributes:

%ISOPEN %ROWCOUNT %FOUND %NOTFOUND

DIFFERENCE BETWEEN %TYPW AND %ROWTYPE:

%TYPE provides datatype of variable or a database column to that variable.

%ROWTYPE provides record type that represents an entire row of a table or view or column selected in the cursor.

PRAGMA: Tells the compiler to associate an exception with an Oracle error.

NORMALIZATION: is the process of removing redundant data from relational tables by splitting it to smaller tanbles.

DATAGROUP: is a collection of modules which can integrate with each other through transfer reference and reporting data.

SELF JOIN: Join the table with itself.

EQUI JOIN: Joining two tables by equating two common columns.

NONEQUI JOIN: Joining two tables by NOT equating two common columns.

OUTER JOIN: Joining 2 tables so that the query can retrieve rows that do not have corresponding join value in the other table. (+ is included in the join)

SUBQUERIES: is a query who's return values are used in filtering condition of the main query.

CORRELATED SUBQUERIES: is a query that has the reference to the main query.

INSTR: Returns the n th position.

SUBSTTR: returns character.

CARTESIAN: results from fault query, it is a row in the result.

BIND VARIABLES: are variables to which we can pass data at runtime either character or number.

COMPOSITE DATATYPES: are Table/record/nested table/varray also known as collections. And has internal components that can be manipulated individually.

APPLICTIONS

CONCURRENT MANAGER: 4 Types

1.Internal Manager 2.Standard Manager 3.Conflict Resolution Manager 4.Specialized concurrent Manager.

BACKEND: FND_CONCURRENT_REQUESTS

FND_CONCURRENT_PROGRAM

FND_PROGRAM_REGISTER:

---------------------

Application Program: Application

Executable name: Name of the registered Executable

Executable Application: Name of the app where executable ins registered.

FND_PROGRAM_EXECUTABLE:

-----------------------

Executable Name: Name of the executable.

Application: Name of the executable application.

Short Name: Short name of the exe

Execution Method: 'Flex Rpt' 'Flex SQL' 'Host' 'Immediate' 'Oracle Reports' 'PL/SQL stored procedure' 'Sql*loader' 'Sql*Plus' 'Sql*report'

Execution File Name: Regd. for all but immediate prog.

FND_REQUEST.SUBMIT_REQUEST

---------------------------

Name: Submit Request

Application: Short name for the application under which the program is registered.

Program: Concurrent program name for which the request has to be submitted.

REGISTERING TABLE: AD_DD.REGISTER_TABLE

AD_DD.REGISTER_COLUMN

CONCURRENT MANAGER: runs concurrent process allowing multiple tasks simultaneously.

CONCURRENT PROCESS: runs simultaneously with other tasks to help complete multiple tasks at once without interruption.

GENERAL AIM: AIM 10, 20, 30, 40 -> DBA

AIM 50 -> Automatic column mapping

AIM 60 -> Manual column mapping

AIM 70 -> Default values, data assignments to be included.

AIM 80 -> Unit Testing & results of UAT

AIM 90 -> Coding

IF -> Interface, RD -> Requirement Definitions, BR -> Business requirement,

MD -> Module Design, CV -> Conversion

PROCESSING CONSTRAINTS: Restricting particular responsibility on an entered information.

PROFILE LEVEL: 1.Site 2.Application 3.User 4.Responsibility

KEY FLEX FIELDS: A flexible data field made up of segments, each segment has a name we define and a set of values that we specify.

DESCRIPTIVE FLEX FIELD: A flexfield that our organization can customize to capture additional information regd. by our business.

CV 10 -> Define Conversion Scope, objectives and approach

CV 20 -> Prepare Conversion Strategy

CV 30 -> Prepare Conversion Standards

CV 40 -> Prepare Conversion Statements

CV 50 -> Perform Conversion Data Mapping

CV 60 -> Define Manual Conversion Strategy

CV 70 -> Design Conversion Programs

CV 80 -> Prepare Conversion Test Plans

CV 90 -> Develop Conversion Program

CV 100 -> Perform Conversion Unit Test

CV 110 -> Perform Conversion business objects Tests

CV 120 -> Perform Conversion Integration Tests

CV 130 -> Install Conversion Software

CV 140 -> Convert & Verify Data

FORMS

Registering Forms:

We Take Template from .fmb table from AV_TOP/11.50/Forms/us

CHANGE THE RESPONSIBILITY TO Application Developer

Goto Application-Register-Register it

Goto Application -Function Give

Function User Function Give

Click on Form Tab.

Function Form Application

Than goto Application- menu & give submenu function description.

Change responsibility to

Security- Define -User

GLOBAL VARIABLE in forms is declared in when new forms instance

Trigger for LOV in forms : Key-list-value

FORM MODULES : 4 types- 1)Alerts 2)Form Modules 3)Menu modules 4) PL/SQL libraries

Order of TRIGGER FIRING : When new form instance pre-form, when-new-block-instance, when new item interface post form

OBJECT LIBRARY : can be used to store function, procedure, package.

ATTACHED LIBRARY : is used to avoid any change in source code

FORMS IN GL :

FNDFFMDC-Descriptive Flexfield segment

GLXSTBKS-Set of books

GLXSTCAL-Accounting calendar

Alert is a model window which has predefined manager

REPORTS

3 types of Reports-SQL,RDF,RXI

2 Parameters in the reports - 1)user (Lexical, Bind) 2) system

LEXICAL PARAMETERS: Are always Characters type used with the where clause used to facilitate the range parameters i.e., if the wishes to se a list of invoices between two given dates.

DECODE : To Achieve dynamic column order or column constant, we use decode statement in reports

FORMAT TRIGGER/ANCHOR : Allows a developer to show or hide objects using PL/SQL I the simplest form a trigger can be placed on certain columns to show or hide based on user parameters

If two reports differ by say one col the decode statement con be used to consolidate these two reports into one

TOKEN :For user parameters we define a token

REPORT REGISTRATION :

CUS_TOP/REPORTS?US-Object script

GOTO Application Developer

Concurrent -Define Executable.

Give Execution Method -Oracle Reports

Executio filename

THEN goto PROM

Attach the executable to the Application

Click on parameters window

Give seq.parameters & Token

Types of Reports generated-HTML,PCL,PDF

post script, Text& HTML.

CUSTOM REPORTS : Made use of PRIS(Property records information system)which is PACE existing Asset accounting system .An ASCII file will be extracted from PRIS balance after all the month end processing is done for the business period prior to conversion. All the data will be extracted from the legacy source file to load into oracle fixed Asset using interface program FA_MASS_ADDITIONS. The Table that receives data in Orapps are FA_MASS_ADITIONS FA_CATAGORIES FA_LOCATION FA_ASSET_KEY_WORDS. which is used to regenerate reports

5TRIGGER in the reports : Before report ,After report before parameters ,After parameters form between pages.

Before Report -SRW.USER_EXIT(FND,USER_INIT)

After Report -SRW.USER_EXIT(FND.USER_EXIT)

COLUMNS :Summary Column, Formula & Place holders columns

POXPOBPS: Blanket Purchase Order Status Report.

Parameters : PO Numbers from to

Buyer Name Vendors From to

Sort by PO Number Or Buyer

POXPOSTD: PO Detail Report.

Title, Buyer Name, Items from to

PO Number From to Vender

Status : Approved Or in process

User Exit -A user exit is a function written in a 3rd Generation Language Like C, Cobol, FORTRAN Etc, to do special purpose processing which is linked into the SQL* Forms Executable files SQL Statements and Pl/SQL Block can be embedded into a user exit

When the user exit gets called from the from by a SQL* from trigger, processing control is temporarily paused onto the user exit when a user exit is done, it reaches an integer value to the SQL* from which indicates success, failure or Fatal error.

Types of Reports

Tabular

Master detail

Form

Mailing Label

Matrix

FREQUENTLY ASKED QUESTIONS AND ANSWERS FOR SRW

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

1. What do I need to do to upgrade reports from SRW 1.0 to 1.1?

User must export all the 1.0 SRW tables and import them into database

with SRW 1.1. Then he should use 'moverep' to move all the data in

these 1.0 tables to 1.1 tables. 1.0 tables prefixed 'FR_' while

1.1 tables prefixed 'SRW_'.

2. Can I use 'dumprep' to dump my 1.0 reports and load them into 1.1 SRW

by using 'loadrep'?

Even though SRW provides utilities called 'dumprep' dumping reports

from database into .rex files and 'loadrep' to load these ascii .rex

on other hosts and operating systems, user should not use them to

upgrade their SRW from 1.0 to 1.1, due to different table structures

and definitions between these 2 versions.

3. When installing SRW, it asks if I want to install one central set of

tables or each user has its own set of tables, which one should

I choose? Why?

All the report data and format info are stored in tables.

These so called 'SRW_' tables may be created under the 'system' account

such that they may be shared by all SRW users, or these tables may be

created under individual accounts - each user has his own set of

tables.

In order to use one set of central tables, the user must have TPO

installed. Having one set of central tables makes managing SRW easier

and causes all the reports be shared by all users.

4. Why do I get message 'Unable to access the SQL*ReportWriter V1.1 tables.'

when logging in SRW, even though the user is a valid database account?

If SRW is installed with one set of central tables, then all SRW users

except 'system' need to be added by running a script called 'srw.admin'

under ?/sqlreport/admin.

If SRW is installed with local tables for each user, then the 'SRW_'

tables must be created under the user account. The script needs to be run

is called 'srw_iloc.sql' under ?/sqlreport/admin or the 'srw.admin' and

choose the right option.

5. What is srw.admin? How should I make use of it?

Srw.admin is a script for SRW administrative uses. It adds, drops a user,

relinks user exit into the executables, etc.

One should go to ?/sqlreport/admin directory, set enviornment variable

by 'setenv LOG test' for error logs when running the script. Then type

'srw.admin' and choose the appropriate actions.

'srw.admin' runs other sql script within the same directory, such as

srw_icen.sql, srw_grnt.sql, etc.

6. How do I relink user exits into SRW executables?

In the IUG there is a section regarding creating user exits, but not very

clear. The steps for creating forms user exits in the IUG will be also

helpful.

After the iapxtb.o is created, one should use the 'srw.admin' script

and choose the 'Relink the executables with user exits'. Remember to

set the 'LOG' enviornment variable for error checking and move your

own copy of iapxtb.o to the directory you desire.

7. Why do I get 'ORA-2112 pcc: select...into returns too many rows' when

running SRW?

It is likely that user has duplicate rows in the system.product_profile

table. The reason may due to duplicate import or install of SRW system

tables.

The way to deal with the problem is to remove all the duplicate rows

in this table.

8. What is the product_profile table for?

The product_profile table gives DBA control of application usage and

resouce limit, such as restricting the page limit for SRW users, or

removing the host command from normal SQL*Plus users.

Currently only SRW and SQL*Plus make use of this table.

Refer 'SQL*Plus User's Guide and Reference 3.0' Appendix E and sql

script 'pupbld.sql' under ?/sqlplus/admin for more info.

9. When looking at the system SRW table, I find 'SRW_SUMMARY' and

'SRW__SUMMARY', what is the difference?

The difference is that 'SRW_SUMMARY' is a view of 'SRW__SUMMARY'.

Overall, 'SRW_' are views with slight modification of the original

tables 'SRW__'.

10. Why do I get 'Out of space on device' when I try to print out a

report on printer?

The 'Out of space' message usually comes from /tmp being full.

The reason is that SRW first generates a temporary file in the

/tmp directory, then spool it out through 'spoolcmd' under

?/sqlreport/admin/printer.

The workaround to this problem is to set enviornment variable

SRW_TMP to directory with more disk space.

11. Why does SRW generate a temporary file in /tmp?

SRW uses a two-pass scheme for generating report. For example, in order

to print out '1 of 10 pages' at the top of each page, the first pass

gives the total number of pages, while the second pass fill in the

number '1' at the appropriate page.

The temporary file generated in /tmp with file name such as 'SRW06536001'

makes the second pass possible.

12. What is the difference between spoollp.sh, spoolpr.sh and spoolcmd?

'Spoolcmd' is usually linked with either spoollp.sh or spoolpr.sh,

depending on the version of unix system (att or bsd). 'spoollp.sh'

uses 'lp' to spool reports, while 'spoollpr.sh' uses 'lpr'.

13. I tried to print out reports on a DEC printer and have specified

the printer definition as 'decland' for landscape mode. However,

the report still came out in protrait mode. What did I do wrong?

User who wants to print out landscape report should first find

the escape sequence which will change the printer from protrait

mode to landscape mode. Then he should modify the ':is=' cause

within the 'printdef.dat' file under ?/sqlreport/admin/printer

by adding the escape sequence.

Generate a new 'decland' printer definition file using SRW

utility 'printdef' (refer SRW User's Guide for the usage).

14. Is it possible to mail a report to an Oracle*Mail user within SRW?

'Mail' is one of the options for 'DESTYPE' parameter. User should

also specify the 'DESNAME' as Oracl*Mail userid whom the report is

supposed to be sent.

ORACLE PURCHASING TABLES

segment1 - is the system–assigned number you use to identify in forms and reports.

Table Name Columns

PO_REQUISITION_HEADERS_ALL REQUISITION_HEADER_ID, PREPARER_ID, SEGMENT1, SUMMARY_FLAG, ENABLED_FLAG

stores information about requisition headers. You need one row for each requisition header you

create. Each row contains the requisition number, preparer, status, and description.SEGMENT1 is the number you use to identify the requisition in forms and reports(unique).

PO_REQUISITION_LINES_ALL REQUISITION_LINE_ID,REQUISITION_HEADER_ID,

LINE_NUM,LINE_TYPE_ID,CATEGORY_ID,

ITEM_DESCRIPTION,UNIT_MEAS_LOOKUP_CODE ,

UNIT_PRICE, QUANTITY, DELIVER_TO_LOCATION_ID,

TO_PERSON_ID, SOURCE_TYPE_CODE

stores information about requisition lines.line number, item number, item category, item description,

need–by date, deliver–to location, item quantities, units, prices, requestor, notes, and suggested supplier information for the requisition line.

LINE_LOCATION_ID - purchase order shipment line on which you placed the requisition. it is null if you

have not placed the requisition line on a purchase order.

BLANKET_PO_HEADER_ID and BLANKET_PO_LINE_NUM store the suggested blanket purchase agreement

or catalog quotation line information for the requisition line.

PARENT_REQ_LINE_ID contains the REQUISITION_LINE_ID from the original requisition line if you

exploded or multisourced this requisition line.

PO_HEADERS_ALL PO_HEADER_ID, AGENT_ID, TYPE_LOOKUP_CODE,

SEGMENT1, SUMMARY_FLAG, ENABLED_FLAG

information for your purchasing documents.There are six types of documents that use PO_HEADERS_ALL

RFQs, Quotations, Standard purchase orders, Planned purchase orders, Blanket purchase orders, Contracts

can uniquely identify a row in PO_HEADERS_ALL using SEGMENT1 and TYPE_LOOKUP_CODE or using

PO_HEADER_ID.BLANKET_TOTAL_AMOUNT for blanket purchase orders or contract purchase orders.

if we use copy document Oracle Purchasing stores the foreign key to your original RFQ in FROM_HEADER_ID.

PO_LINES_ALL PO_LINE_ID, PO_HEADER_ID, LINE_TYPE_ID, LINE_NUM

stores current information about each purchase order line. CONTRACT_NUM reference a contract

purchase order from a standard purchase order line.

PO_VENDORS VENDOR_ID, VENDOR_NAME, SEGMENT1,

SUMMARY_FLAG, ENABLED_FLAG

information about your suppliers.purchasing, receiving, payment, accounting, tax, classification, and general information.

PO_VENDOR_SITES_ALL VENDOR_SITE_ID, VENDOR_ID, VENDOR_SITE_CODE

information about your supplier sites.a row for each supplier site you define. Each row includes the site address, supplier reference, purchasing, payment, bank, and general information. Oracle Purchasing uses this

information to store supplier address information.

PO_DISTRIBUTIONS_ALL PO_DISTRIBUTION_ID, PO_HEADER_ID, PO_LINE_ID,LINE_LOCATION_ID, SET_OF_BOOKS_ID,

CODE_COMBINATION_ID,QUANTITY_ORDERED,

DISTRIBUTION_NUM

contains accounting distribution information fora purchase order shipment line.You need one row for

each distribution line you attach to a purchase order shipment.

There are four types of documents using distributions in Oracle Purchasing:

Standard Purchase Orders, Planned Purchase Orders, Planned Purchase Order Releases, Blanket Purchase Order Releases

includes the destination type, requestor ID, quantity ordered and deliver–to location for the distribution.

PO_RELEASES_ALL PO_RELEASE_ID, PO_HEADER_ID, RELEASE_NUM,

AGENT_ID, RELEASE_DATE

contains information about blanket and planned purchase order releases. You need one row for each release you issue

for a blanket or planned purchase order. Each row includes the buyer, date, release status, and release number. Each release must have at least one purchase order shipment

PO_VENDOR_CONTACTS VENDOR_CONTACT_ID, VENDOR_SITE_ID

stores information about contacts for a supplier site. You need one row for each supplier contact you define.Each row includes the contact name and site.

PO_ACTION_HISTORY OBJECT_ID, OBJECT_TYPE_CODE , OBJECT_SUB_TYPE_CODE, SEQUENCE_NUM

information about the approval and control history of your purchasing documents. There is one record in

this table for each approval or control action an employee takes on a purchase order, purchase agreement, release, or requisition.

stores object_id -- Document header identifier,OBJECT_TYPE_CODE --- Document type, OBJECT_SUB_TYPE_CODE --Document subtype SEQUENCE_NUM --Sequence of the approval or control action for a document

PO_REQ_DISTRIBUTIONS_ALL DISTRIBUTION_ID, REQUISITION_LINE_ID, SET_OF_BOOKS_ID,

CODE_COMBINATION_ID,REQ_LINE_QUANTITY,

DISTRIBUTION_NUM

stores information about the accounting distributions associated with each requisition line.

PO_LINE_LOCATIONS_ALL LINE_LOCATION_ID, LAST_UPDATE_DATE, LAST_UPDATED_BY, PO_HEADER_ID, PO_LINE_ID, SHIPMENT_TYPE

contains information about purchase order shipment schedules and blanket agreement price breaks. You need one row for each schedule or price break you attach to a document line. There are seven types of documents that use shipment schedules:

RFQs,Quotations,Standard purchase orders,Planned purchase orders,Planned purchase order releases,Blanket purchase orders, Blanket purchase order releases

Each row includes the location, quantity, and dates for each shipment schedule. Oracle Purchasing uses

this information to record delivery schedule information for purchase orders, and price break information for

blanket purchase orders, quotations and RFQs.