Thursday 14 July 2011

GL interface from legacy to GL

CREATE OR REPLACE PACKAGE xxfa_gl_camra_pkg
IS
PROCEDURE xxfa_gl_camra_proc (errbuf OUT VARCHAR2, retcode OUT NUMBER);
END xxfa_gl_camra_pkg;

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

CREATE OR REPLACE PACKAGE BODY xxfa_gl_camra_pkg
IS
l_n_user_id NUMBER := fnd_global.user_id;
l_n_request_id NUMBER := fnd_global.conc_request_id;
l_c_gl_app_short_name CONSTANT VARCHAR2 (10) := 'SQLGL';
l_c_gl_flex_code CONSTANT VARCHAR2 (10) := 'GL#';
l_c_gl_flex_structure_code CONSTANT VARCHAR2 (30)
:= 'ACCOUNTING_FLEXFIELD';
l_err_msg VARCHAR2 (4000);

------ Procedure -----------
PROCEDURE xxfa_gl_camra_proc (errbuf OUT VARCHAR2, retcode OUT NUMBER)
IS
v_chart_of_accounts_id NUMBER;
v_stg_status VARCHAR2 (100);
--v_gl_segment_value FND_FLEX_EXT.segmentarray;
v_x_oracle_string VARCHAR2 (4000);
v_segment_con_seg VARCHAR2 (4000);
v_n_code_combination_id VARCHAR2 (4000);
v_status_flag VARCHAR2 (3);
v_rows NUMBER (10) := 0;
v_insert_rows NUMBER (10) := 0;
v_error_rows NUMBER (10) := 0;
v_enter_cr NUMBER (10) := 0;
v_enter_dr NUMBER (10) := 0;
v_error_enter_dr NUMBER (10) := 0;
v_error_enter_cr NUMBER (10) := 0;
v_total_dr NUMBER (15) := 0;
v_total_cr NUMBER (15) := 0;

CURSOR c1
IS
SELECT xgcst.ROWID row_id, ROWNUM, xgcst.*
FROM xxfa_gl_camra_stg_tab xgcst
WHERE 1 = 1 AND stg_status IN ('New', 'Error');

TYPE gl_data_tbl IS TABLE OF xxfa_gl_camra_stg_tab_v%ROWTYPE
INDEX BY BINARY_INTEGER;

rec_gl_balances gl_data_tbl;
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN -- Procedure begin
SELECT gsob.chart_of_accounts_id -- To Get the Chart of Accounts id
INTO v_chart_of_accounts_id
FROM gl_sets_of_books gsob
WHERE gsob.set_of_books_id = fnd_profile.VALUE ('GL_SET_OF_BKS_ID');

OPEN c1; --Open The Cursor c1

LOOP
FETCH c1
BULK COLLECT INTO rec_gl_balances;
--Move the data from cursor c1to pl/sql table type variable.

EXIT WHEN c1%NOTFOUND;
END LOOP;

CLOSE c1; -- Close the Cursor c1

FOR i IN 1 .. rec_gl_balances.COUNT
LOOP
v_rows := v_rows + 1;
v_segment_con_seg :=
rec_gl_balances (i).segment1
|| '.'
|| -- Concatinated the all segments
rec_gl_balances (i).segment2
|| '.'
|| rec_gl_balances (i).segment3
|| '.'
|| rec_gl_balances (i).segment4
|| '.'
|| '00000'
|| '.'
|| '0'
|| '.'
|| '0000'
|| '.'
|| '00'
|| '.'
|| '00';
-- using api FND_FLEX_EXT.GET_CCID to check the valid combination id .
v_n_code_combination_id :=
fnd_flex_ext.get_ccid (l_c_gl_app_short_name,
'GL#',
v_chart_of_accounts_id,
TO_CHAR (SYSDATE, 'YYYY/MM/DD HH24:MI:SS'),
v_segment_con_seg
);

--Fnd_File.Put_Line(fnd_File.LOG, 'CCID ' || v_n_Code_Combination_Id);
IF v_n_code_combination_id = 0
THEN
l_err_msg := fnd_message.get;
v_stg_status := 'Error';
v_status_flag := 'E';
-- Set the Status flag E if it is invalid combination id .
fnd_file.put_line (fnd_file.LOG,
'Message:'
|| l_err_msg
|| '---'
|| 'Combinationid:'
|| v_segment_con_seg
);
ELSE -- Set the Status flag P if it is valid combination id .
--DBMS_OUTPUT.PUT_LINE( 'finalCCID not 0 ' || v_n_Code_Combination_Id);
v_stg_status := 'Process Completed';
v_status_flag := 'P';
END IF;

--fnd_file.put_line(fnd_file.log,v_x_Oracle_String);
--fnd_file.put_line(fnd_file.log,fnd_message.get);
IF v_status_flag = 'E'
THEN
-- IF Record is errout then update the stage table with error status.
v_error_rows := v_error_rows + 1;

UPDATE xxfa_gl_camra_stg_tab xgcst
SET stg_status = v_stg_status,
err_description = l_err_msg,
request_id = l_n_request_id,
last_updated_by = l_n_user_id,
last_updated_date = SYSDATE
WHERE xgcst.ROWID = rec_gl_balances (i).row_id;

v_error_enter_dr :=
v_error_enter_dr + rec_gl_balances (i).entered_dr;
v_error_enter_cr :=
v_error_enter_cr + rec_gl_balances (i).entered_cr;
ELSE
-- IF record is not errorout then Inserting data into interface table
INSERT INTO gl_interface
(status,
accounting_date,
segment1,
segment2,
segment3,
segment4, segment5, segment6, segment7,
segment8, segment9, entered_dr,
entered_cr,
set_of_books_id,
currency_code,
date_created, created_by,
actual_flag,
user_je_category_name,
user_je_source_name,
accounted_dr,
accounted_cr,
GROUP_ID, request_id,
status_description
)
VALUES (rec_gl_balances (i).status,
rec_gl_balances (i).accounting_date,
rec_gl_balances (i).segment1,
rec_gl_balances (i).segment2,
rec_gl_balances (i).segment3,
rec_gl_balances (i).segment4, '00000', '0', '0000',
'00', '00', rec_gl_balances (i).entered_dr,
rec_gl_balances (i).entered_cr,
fnd_profile.VALUE ('GL_SET_OF_BKS_ID'),
rec_gl_balances (i).currency_code,
rec_gl_balances (i).date_created, l_n_user_id,
rec_gl_balances (i).actual_flag,
rec_gl_balances (i).user_je_category_name,
rec_gl_balances (i).user_je_source_name,
rec_gl_balances (i).accounted_dr,
rec_gl_balances (i).accounted_cr,
rec_gl_balances (i).GROUP_ID, l_n_request_id,
rec_gl_balances (i).status_description
);

UPDATE xxfa_gl_camra_stg_tab xgcst
-- IF Record insertd into Gl interafce then update the stage table with 'Process Completed 'status.
SET stg_status = v_stg_status,
request_id = l_n_request_id,
last_updated_by = l_n_user_id,
last_updated_date = SYSDATE
WHERE xgcst.ROWID = rec_gl_balances (i).row_id;

v_insert_rows := v_insert_rows + 1;
v_enter_dr := v_enter_dr + rec_gl_balances (i).entered_dr;
v_enter_cr := v_enter_cr + rec_gl_balances (i).entered_cr;
END IF;

COMMIT;
END LOOP;

v_total_dr := v_enter_dr + v_error_enter_dr;
v_total_cr := v_enter_cr + v_error_enter_cr;
fnd_file.put_line
(fnd_file.output,
'--------------------------------------------------------------'
);
fnd_file.put_line (fnd_file.output,
'No of records Processed :'
|| v_rows
);
fnd_file.put_line (fnd_file.output,
'No of records inserted to GL interface table:'
|| v_insert_rows
);
fnd_file.put_line (fnd_file.output,
'No of records rejected :'
|| v_error_rows
);
fnd_file.put_line
(fnd_file.output,
'--------------------------------------------------------------'
);
fnd_file.put_line
(fnd_file.output,
'--------------------------------------------------------------'
);
fnd_file.put_line (fnd_file.output,
'Total Debit Amount :'
|| v_total_dr
);
fnd_file.put_line (fnd_file.output,
'Total Credit Amount :'
|| v_total_cr
);
fnd_file.put_line (fnd_file.output,
'dr_amount enetered into interface table :'
|| v_enter_dr
);
fnd_file.put_line (fnd_file.output,
'cr_amount enetered into interface table :'
|| v_enter_cr
);
fnd_file.put_line (fnd_file.output,
'Rejected dr_amount :'
|| v_error_enter_dr
);
fnd_file.put_line (fnd_file.output,
'Rejected cr_amount :'
|| v_error_enter_cr
);
fnd_file.put_line
(fnd_file.output,
'--------------------------------------------------------------'
);
fnd_file.put_line (fnd_file.output, ' ');
fnd_file.put_line
(fnd_file.output,
'** More Information about Rejected records check the stage table XXFA_GL_CAMRA_STG_TAB'
);
fnd_file.put_line
(fnd_file.output,
' Reason for rejecting check the Error Description column **'
);
------------
fnd_file.put_line
(fnd_file.LOG,
'------------------------------------------------------------'
);
fnd_file.put_line (fnd_file.LOG, 'Request id :' || l_n_request_id);
fnd_file.put_line (fnd_file.LOG, 'User id :' || l_n_user_id);
fnd_file.put_line
(fnd_file.LOG,
'------------------------------------------------------------'
);
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG, 'EXCEPTION');
fnd_file.put_line
(fnd_file.LOG,
'---------------------------------------------------'
);
fnd_file.put_line (fnd_file.LOG, 'Request id :' || l_n_request_id);
-- fnd_file.put_line (fnd_file.log,'CCID :'||v_n_Code_Combination_Id);
fnd_file.put_line (fnd_file.LOG,
'Errmsg :' || SQLCODE || ' ' || SQLERRM
);
END xxfa_gl_camra_proc; -- End procedure
END xxfa_gl_camra_pkg; -- End Pkg .

Bulk binding notes

Bulk Binding

This article begins a discussion of how to work with collections. Previous versions of Oracle had limitations for collection usage in regards to SQL statement processing. Bulk Bind and Collect features were introduced to reduce the SQL processing overhead by efficient use of collections in PL/SQL code.
The PL/SQL engine executes procedural statements and sends all SQL statements present in the code to the SQL engine. The SQL engine will parse and execute the query or DML statement and return the expected output back to the PL/SQL engine. This switch between the two engines is called context switching.
We mostly concentrate on the SQL statement to tune performance issues. It is worth noting that excessive context switching can affect performance. This would be substantially significant when we are carrying out SQL statements in a loop. The features discussed below were introduced to reduce this overhead of SQL processing. Introduced in Oracle 8i, these features are being improved on with every new release.
Two PL/SQL features, Bulk Bind and Bulk collect help in improving performance and utilizing resources effectively from within PL/SQL code. These features reduce context switching, (i.e., multiple SQL statements being executed from the code resulting in calls to SQL engine), and carry out the operation on the concerned object at one go. Since the SQL statements are fired in a bundle, frequent access to SQL engine is reduced.
In cases where the PL/SQL code is being executed on a different terminal than the server itself, this would also result in optimal network usage rather than too frequent network calls to access the SQL engine.

Bulk Collects (Reading data in bulk)

The bulk collect option instructs the SQL engine to bulk bind the output collections before returning them to the PL/SQL engine. This allows us to load data dynamically into collections at one shot for further processing. Bulk collect can be used with SELECT INTO, FETCH INTO and RETURNING INTO statements.
Syntax:

  ... bulk collect into collection...

For example, let us assume that we need to load all pending transactions into a temporary table and process them one by one. As part of validation, there is a need to refer to the data in the same table, from time to time, for each transaction being processed. One possible method to write the code would be to load all of the data in the temporary table to a collection type. This way, additional queries on the table could be avoided (context switch) and the processing could be carried out in PL/SQL itself. This idea is further improved on by the use of the bulk collect option, as all data is loaded into PL/SQL at the same time.

Examples of Bulk

Bulk used with Select into clause

declare

type emp_details is table of emp.ename%type index by binary_integer;

V emp_details;

begin

select ename bulk collect into V

from emp;

for i in V.first .. V.last

loop

dbms_output.put_line(V(i));

end loop;

end;

Bulk used in Cursors

declare

cursor cf is select * from emp;

type emp_tab is table of emp%rowtype index by binary_integer;

V emp_tab;

v_limit natural := 10;

begin

open cf;

fetch cf bulk collect into V limit v_limit;

for j in V.first .. V.last

loop

dbms_output.put_line(V(j).ename);

end loop;

end;

Bulk Insert

Create table BI (a number check(a between 5 and 45));

declare

type no_list is table of number index by binary_integer;

v no_list;

bulk_errors exception;

pragma exception_init ( bulk_errors, -24381 );

begin

for i in 5..50

loop

v(i) := i;

end loop;

forall j in V.first .. V.last save exceptions

insert into bi values (V(j));

dbms_output.put_line('Records inserted');

exception

when bulk_errors then

for j in 1..sql%bulk_exceptions.count

loop

Dbms_Output.Put_Line ( 'Error from element #' ||

To_Char(sql%bulk_exceptions(j).error_index) || ': ' ||

Sqlerrm(-sql%bulk_exceptions(j).error_code) );

end loop;

end;

Bulk Delete

declare

type emp_tab is table of emp%rowtype index by binary_integer;

V emp_tab;

begin

delete from emp

returning empno,ename,job,mgr,hiredate,sal,comm,deptno bulk collect into V;

for i in V.first .. v.last

loop

dbms_output.put_line(V(i).ename);

end loop;

end;

Plsql Questions

PL/SQL

1. 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.

2. 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.

3. 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.

4. 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.

5. What is the difference between Truncate and Delete Statement?

· Truncate – Data truncated by using truncate statement is lost permanently and cannot be retrieved even by rollback. Truncate command does not use rollback segment during its execution, hence it is fast.

· Delete – Data deleted by using the delete statement can be retrieved back by Rollback. Delete statement does not free up the table object allocated space.

6. What are Exceptions? How many types of Exceptions are there?

A) Exceptions are conditions that cause the termination of a block. There are two types of exceptions

· Pre-Defined – Predefined by PL/SQL and are associated with specific error codes.

· User-Defined – Declared by the users and are rose on deliberate request. (Breaking a condition etc.)

Exception handlers are used to handle the exceptions that are raised. They prevent exceptions from propagating out of the block and define actions to be performed when exception is raised.

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

8. 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.

8. What is the difference between Package, Procedure and Functions?

· A package is a database objects that logically groups related PL/SQL types, objects, and

Subprograms.

· Procedure is a sub program written to perform a set of actions and can return multiple values.

· Function is a subprogram written to perform certain computations and return a single value.

Unlike subprograms packages cannot be called, passed parameters or nested.

9. How do you make a Function and Procedure as a Private?

A) Functions and Procedures can be made private to a package by not mentioning their declaration in the package specification and by just mentioning them in the package body.

10. What is an Anonymous block?

A) Anonymous Block is a block of instructions in PL/SQL and SQL which is not saved under a name as an object in database schema. It is also not compiled and saved in server storage, so it needs to be parsed and executed each time it is run. However, this simple form of program can use variables, can have flow of control logic, can return query results into variables and can prompt the user for input using the SQL*Plus '&' feature as any stored procedure.

12. What are the two basic parameters that we have to pass while registering PL/SQL procedure?

A) Error code and Error Buffer.

11. How do you kick a Concurrent program from PL/SQL?

A) Using FND_SUBMIT.SUBMIT_REQUEST.

12. How to display messages in Log file and Output file?

A) Using FND_FILE.PUT_LINE

13. What is a Trigger ? How many types of Triggers are there?

A) Trigger is a procedure that gets implicitly executed when an insert/update/delete statement is issued against an associated table. Triggers can only be defined on tables not on views, how ever triggers on the base table of a view are fired if an insert/update/delete statement is issued against a view.

There are two types of triggers, Statement level trigger and Row level trigger.

Insert

After / For each row

Trigger is fired / Update /

Before / For Each statement

Delete

14. Can we use Commit in a Database Trigger, if ‘No’ then why?

A) No. Committing in a trigger will violate the integrity of the transaction.

15. What is Commit, Rollback and Save point?

Commit – Makes changes to the current transaction permanent. It Erases the savepoints and releases the transaction locks.

Savepoint –Savepoints allow to arbitrarily hold work at any point of time with option of later committing. They are used to divide transactions into smaller portions.

Rollback – This statement is used to undo work.

16. What is the difference between DDL, DML and DCL structures?

A) DDL statements are used for defining data. Ex: Create, Alter, Drop.

DML statements are used for manipulating data. Ex: Insert, update, truncate, delete, select.

DCL statements are used for to control the access of data. Ex; Grant, Revoke.

17. How can u create a table in PL/SQL procedure?

A) By using execute immediate statement we can create a table in PLSQL.

Begin

Execute immediate ‘create table amit as select * from emp’;

End;

All DDL,DML,DCL commands can be performed by using this command.

18. How do we Tune the Queries?

A) Queries can be tuned by Checking the logic (table joins), by creating Indexes on objects in the where clause, by avoiding full table scans. Finally use the trace utility to generate the trace file, use the TK-Prof utility to generate a statistical a nalysis about the query using which appropriate actions can be taken.

21. What is Explain Plan? How do u use Explain Plan in TOAD?

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

19. What is a TK-PROF and its usage?

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

20. What is Optimization? How many types of Optimization are there?

A) Rule based Optimization and Cost Based Optimization.

21. What is the default optimization chosen by Oracle?

A) Cost based Optimization.

22. What is the difference between When no data Found and cursor attribute % DATA FOUND?

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

23. What is the difference between the snapshot and synonym?

· A snapshot refers to read-only copies of a master table or tables located on a remote node. A snapshot can be queried, but not updated; only the master table can be updated. A snapshot is periodically refreshed to reflect changes made to the master table. In this sense, a snapshot is really a view with periodicity.

· A synonym is an alias for table, view, sequence or program unit. They are of two types private and public.

25. What is the difference between data types char and varchar?

A) Char reserves the number of memory locations mentioned in the variable declarations, even though not used (it can store a maximum of 255 bytes). Where as Varchar does not reserve any memory locations when the variable is declared, it stores the values only after they are assigned (it can store a maximum of 32767 bytes).

26. How can we place index to a second column in the table i.e. there is already one index and I want to place another index for the column on the table?

27. 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?

A) 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 <ORACLE ID> <Responsibility Application Short Name> <Responsibility Name> <User Name> [WAIT=<Wait Flag] CONCURRENT <Concurrent Program Application Short Name> <Concurrent Program Name> [START=<Requested Start Date>] [REPEAT_DAYS=<Repeat Interval>] [REPEAT_END=<Request Resubmission End Date>] <Concurrent Program Arguments ...>

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), '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '');

28. What is pipelining?

29) How can the duplicate records be from the table?

SQL> create table table_name2 as select distinct * from table_name1;

SQL> drop table_name1; SQL> rename table_name2 to table_name1;30) What is the significance of _all tables?

A) _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'

31)What are mutating tables? And what is mutating error?

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.

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.

32) What is difference between oracle 7 and oracle 8?

A) Oracle 7 is a simple RDBMS, where as Oracle 8 is ORDBMS i.e., RDBMS with Object Support.

The main add-ons in version 8 are…

· Abstract Data types

· Varrays

· PL/SQL Tables

· Nested Tables

· Partitioned Tables

33.What is Data cleaning and testing.

A) Data Cleaning: Transformation of data in its current state to a pre-defined, standardized format using packaged software or program modules.

Data Testing: The agreed upon conversion deliverables should be approved by the client representatives who are responsible for the success of the conversion. In addition, three levels of conversion testing have been identified and described in the prepare conversion test plans deliverables.

Eg: for Summary Balances in GL we set Test Criteria as Record Counts, Hash Totals, Balances, Journal Debit and Credit.

34. While registering a report and a pl/sql block we pass some parameters, for any pl/sql block we pass 2 additional parameters. Can u list them?

A) It requires two IN parameters for a PL/SQL procedure that's registered as a concurrent program in Apps. They are

1. Errcode IN VARCHAR2

2. Errbuff IN VARCHAR2

35) what is a trace file?

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

36 ) 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

Sandeep Oracle Apps Notes

Thursday 14 July 2011

GL interface from legacy to GL

Bulk binding notes

Bulk Collects (Reading data in bulk)

Examples of Bulk

Bulk used with Select into clause

Bulk used in Cursors

Bulk Insert

Bulk Delete

Plsql Questions

Total Pageviews