, , , ,

Oracle WorkFlow Interview Questions

Oracle WorkFlow Interview Questions ?

1. How to find the latest version of a current workflow process, and how to revert to the old version of workflow

2. Name five very important base tables of oracle workflow and their significance.

3. Name five oracle workflow apis and their significance and use

4. How would you clear stuck or deferred workflows and what is meant by a deferred workflow

5. What is the difference between workflow wait activity and block activity, what is the api which is used in this regard to put a wait and to put a block.

6. How would you continue the workflow which is in wait and which is in block mode, name the api which is responsible for that

7. What is access level in oracle workflow and how it is significant.

8. Apart from loading the workflow from workflow builder is there any other method of loading the workflow from database to local machine.

9. How many types of attributes are present in workflow and what is a document type attribute

10. How would you force a user to not re-assign the workflow to other user.

/by
, , , ,

Oracle 11i Apps General Questions

What is Flexfield?
A flexfield is a field made up of segments. A flex field is a flexible data field that your organization can customize to your business needs without programming. Oracle Applications uses two types of flexfields, key flexfields and descriptive flexfields.
What is a Set of Books (SOB)?
Set of Books is a financial entity that shares a common Chart of Accounts, Functional Currency and Accounting Calendar (3Cs).
What is Segment?
A segment is a single sub–field within a flexfield. You define the appearance and meaning of individual segments when customizing a flexfield. A segment is represented in your database as a single table column.
What is a Value Set?
The flexfield validates each segment against a set of valid values that are usually predefined are called Value Sets.
What is Cross Validation?
A cross-validation rule defines whether a value of a particular segment can be combined with specific values of other segments
What are the different types of tables in Oracle 11i Apps?
B – The Main base tables
_ALL – Contains multi-org data
_V – View created on base table
_TL – Table that supports multi language
_VL – View created on multi language tables
_F – Data tracking tables. Used in HRMS
_S – Sequence related table
_DFV / _KFV – The DFF/KFF table created on the base table
What is the difference between Request Group and Request Set?
A request group is a collection of reports or concurrent programs.  A System Administrator defines report groups in order to control user access to reports and concurrent programs.  Only a System Administrator can create a request group.
Request sets define run and print options, and possibly, parameter values, for a collection of reports or concurrent program. End users and System Administrators can define request sets. A System Administrator has request set privileges beyond those of an end user. 
What are different types of validations of Value Sets?
None (not validated at all)
Independent
Dependent
Table
Special (advanced)
Pair (advanced)
Can you change the validation type of the existing Value set?
No.
What are the validation types supported by Accounting Flexfield?
Dependent, Independent, Table

What is the difference between Discrete Manufacturing and Process Manufacturing?
Discrete manufacturing is a manufacturing process where distinct products are built or manufactured in discrete batches on manufacturing floor. The resulting product is what you can count. It uses Bills of Materials (BOMs) and assembles along a routing.
Eg: Computer Manufacturing, Machinery Manufacturing, Automobiles, etc
Process manufacturing is a manufacturing process in which the end product manufactured are undifferentiated. It uses formulations or recipes and blends in a batch.
Eg: Pharmaceutical, Food and beverages, Refineries, etc
What are Shared Entities in Oracle 11i Apps?
Shared entities enable one-time definition of object and are accessed by several products/Modules.
Few shared entities and the owned applications are
Set of Books – General Ledger
Items – Inventory
Unit of Measure – Inventory
Suppliers – Purchasing
Customers – Receivables
Locations – Human Resource
Employee – Human Resource
Organization – Human Resource
How data are secured (partitioned) in Oracle 11i Applications?
General Ledger and Fixed Assets – Set of Books
Human Resource – Business Group
Order Management, Accounts Receivables, Accounts Payables, Purchasing, Cash Management, Projects, Service, Sales Compensation, Sales and Marketing – Operating Unit
Inventory, Manufacturing – Inventory Unit
/by
, , , , ,

Global Temporary Table

Create Temporary Table in Oracle

To create a table named test with column col1 type varchar2 length 10, col2 type number. col3 type clob we can use CREATE TABLE statement as,
CREATE TABLE TEST(col1 VARCHAR2(10), col2 NUMBER, col3 CLOB);
Now if I insert data into the table the data is visible and accessible to all users. In many cases it is needed the data inside a table will be reside temporarily. In that case we can use temporary tables. Temporary tables are useful in applications where a result set is to be buffered. To create temporary table we have to issue CREATE GLOBAL TEMPORARY clause.
Temporary table can be of two types based on ON COMMIT clause settings.
1)ON COMMIT DELETE ROWS specifies temporary table would be transaction specific. Data persist within table up to transaction ending time. If you end the transaction the database truncates the table (delete all rows). Suppose if you issue commit or run ddl then data inside the temporary table will be lost. It is by default option.
Example:
(i)This statement creates a temporary table that is transaction specific:
CREATE GLOBAL TEMPORARY TABLE test_temp(col1 number, col2 number) ON COMMIT DELETE ROWS;
Table created.
(ii)Insert row in to the temporary table.
insert into test_temp values(3,7);
1 row created.
(iii)Look at the data in the table.
select * from test_temp;
COL1 COL2
———- ———-
3 7
(iv)Issue Commit.
commit;
Commit complete.
(v)Now look at the data in the temporary table. As I created transaction specific temporary table(on commit delete rows) so data is lost after commit.
SQL> select * from test_temp;
no rows selected
2)ON COMMIT PRESERVE ROWS specifies temporary table would be session specific. Data persist within table up to session ending time. If you end the session the database truncates the table (delete all rows). Suppose you type exit in SQL*Plus then data inside the temporary table will be lost.
Example of Session Specific Temporary Tables:
1)Create Session Specific Temporary Table test_temp2.
CREATE GLOBAL TEMPORARY TABLE test_temp2 (col1 number, col2 number)
ON COMMIT PRESERVE ROWS;
(ii)Insert data into it and look at data both before commit and after commit.
insert into test_temp2 values(3,7);
1 row created.
SQL>select * from test_temp2;
COL1 COL2
———- ———-
3 7
(iii) commit;
Commit Complete
(iv)select * from test_temp2;
COL1 COL2
———- ———-
3 7

(iv)End the Session.
exit;


Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – Production
With the Partitioning, OLAP and Data Mining options
(v)Connect in a new session and look at data again.
$ sqlplus apps/[email protected]
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 – Production
With the Partitioning, OLAP and Data Mining options

SQL> select * from test_temp2;
no rows selected

This is how Global Temporary Tables are used.
Feature of Temporary Table
1.Indexes can be created on temporary tables. They are also temporary and the data in the index has the same session or transaction scope as the data in the underlying table.
2.Unlike permanent tables, temporary tables and their indexes do not automatically allocate a segment when they are created. Instead, segments are allocated when the first INSERT (or CREATE TABLE AS SELECT) is performed. This means that if a SELECT, UPDATE, or DELETE is performed before the first INSERT, the table appears to be empty.
3.DDL operations (except TRUNCATE) are allowed on an existing temporary table only if no session is currently bound to that temporary table.
4.If you rollback a transaction, the data you entered is lost, although the table definition persists.
5.A transaction-specific temporary table allows only one transaction at a time. If there are several autonomous transactions in a single transaction scope, each autonomous transaction can use the table only as soon as the previous one commits.
6.Because the data in a temporary table is, by definition, temporary, backup and recovery of temporary table data is not available in the event of a system failure.
7.It is good to know about that temporary table itself is not temporary, the data within it is temporary.
Restriction of Temporary Table
1.Temporary tables cannot be partitioned, clustered, or index organized.
2.You cannot specify any foreign key constraints on temporary tables.
3.Temporary tables cannot contain columns of nested table.
4.You cannot specify the following clauses of the LOB_storage_clause: TABLESPACE, storage_clause, or logging_clause.
5.Parallel DML and parallel queries are not supported for temporary tables. Parallel hints are ignored. Specification of the parallel_clause returns an error.
6.You cannot specify the segment_attributes_clause, nested_table_col_properties, or parallel_clause.
7.Distributed transactions are not supported for temporary tables.

/by
, , , , , ,

Common Reporting Tools in Oracle

Oracle Reports: Fixed format reports delivered with the 11i release were built on this tool. This is the most used tool for reporting on Oracle Applications. Most of reports customizations are built with this tool. Once customized the output of the report can be in Excel (Not group By Report), word, Acrobat documents or text format.

Oracle Discoverer: is an intuitive tool for creating reports and performing on-line analysis. Discoverer uses the EUL (End User Layer), a meta data definition, which hides the complexity of the database from the end user and provides easy to use wizards for creating reports to suit individual needs. The flexibility of this tool allows the user to create cross tab reports that perform like pivot tables in Excel.

Oracle XML Publisher: is a new Oracle tool for reporting. It enables users to utilize a familiar desktop tool, like MS Word or MS Excel, to create and maintain their own report. At runtime, XML Publisher merges the custom templates with the concurrent request extracts data to generate output in RTF, PDF, HTML and EXCEL.

RXi Report: (Variable reports) – variable format reports delivered with the E-Business 11i. With this tool a user has the ability to print the same report with multiple layouts. The user can also choose which columns he requires on a particular report. This tool is most used on Oracle Financials Applications.

FSG Reports (Financial Statement Generator): is a powerful report building tool for Oracle General Ledger. Some of benefits of using this tool are that a user can generate financial reports, and schedule reports to run automatically. The only drawback of this tool is that it is only available for the general ledger responsibility and can be used to see only financial account balances.

Business Intelligence System (BI): is a set of tools to provide high level information for the managers (decision makers) to run their business such as the profitability of a particular business unit. The information this tool provides helps managers to take the right decision with the daily data that is uploaded on their systems

/by
, , , ,

Difference Between Lexical and Bind Variable

Bind references are used to replace a single value in SQL or PL/SQL. Specifically, bind references may be used to replace expressions in SELECT, WHERE, GROUP BY, ORDER BY, HAVING,CONNECT BY, and START WITH clauses of queries. Binds may not be referenced in the FROM clause.
An example is:
SELECT Col1,Col2
FROM XX_table
WHERE Col1 = :P_col1

Lexical references are placeholders for text that you embed in a SELECT statement. You can use lexical references to replace the clauses appearing after SELECT, FROM, WHERE, GROUP BY , ORDER BY , HAVING, CONNECT BY, and START WITH. You cannot make lexical references in PL/SQL. Before you reference a lexical parameter in a query you must have predefined the parameter and given it an initial value.
An example is:
SELECT Col1,Col2
FROM &ATABLE

/by