, , , , , ,

How to find INV Onhand Quantity at given date?

–The query inputs the Item ID, organization ID and date.

  SELECT   SUM (target_qty), item_id
    FROM   (  SELECT   moqv.subinventory_code subinv,
                       moqv.inventory_item_id item_id,
                       SUM (transaction_quantity) target_qty
                FROM   mtl_onhand_qty_cost_v moqv
               WHERE   moqv.organization_id = :org_id
                       AND moqv.inventory_item_id = :item_id
            GROUP BY   moqv.subinventory_code,
                       moqv.inventory_item_id,
                       moqv.item_cost
            UNION
              SELECT   mmt.subinventory_code subinv,
                       mmt.inventory_item_id item_id,
                       -SUM (primary_quantity) target_qty
                FROM   mtl_material_transactions mmt, mtl_txn_source_types mtst
               WHERE   mmt.organization_id = :org_id
                       AND transaction_date >= TO_DATE (:hist_date) + 1
                       AND mmt.transaction_source_type_id =
                             mtst.transaction_source_type_id
                       AND mmt.inventory_item_id = :item_id
            GROUP BY   mmt.subinventory_code, mmt.inventory_item_id) oq
GROUP BY   oq.item_id
/by
, , , , , , ,

Cursors In Oracle

CURSOR : A cursors is a pointer used to fetch rows from a result set 
Two types of classification s:


I.STATIC CURSOR S: 
Static : Normal cursor (implicit or explicit)

Cursor attributes  for implicit and explicit:

%FOUND – records fetched successfully
%NOTFOUND – no records fetched
%ROWCOUNT – Number of records fetched
%ISOPEN – returns TRUE if cursor is open

a. Implicit : 
Cannot be opened outside the statement
More fast and less coding effort.
Will never raise INVALID_CURSOR error
Raises NO_DATA_FOUND and TOO_MANY_ROWS exceptions (eg: select <stmt>)

Example Implicit Cursor:

select * from emp

If SQL%FOUND then

v_count:= SQL%ROWCOUNT

end if;


b. Explicit : 2 network round trips. Store data first then retrieve data. 
More programmatic control.
Programmer could open; fetch data, close, check attributes etc.

Syntax:
open c1; — cursor c1 is select <stmt>

fetch <>

exit when c1%NOTFOUND

Example Explicit cursor:

Without Using Loop s
Declare

Cursor cur1 is

select ename,empno,sal from emp

where sal<50000 and deptno=50

begin

open cur1;

fetch cur1 into v_ename,v_empno,v_sal;

exit when cur1%notfound;

—<do processing>

close cur1;

end;

Using Loops:

Declare

Cursor cur1 is

select ename,empno,sal from emp

where sal<50000 and deptno=50

begin

For rec in cur1
loop
dbms_output.put_line(‘Employee Number ‘||rec.empno);
end loop;

end;

Using Loops with Cursor Parameters:

Declare

Cursor cur1( cp_deptNo Number) 
is
select ename,empno,sal from emp
where sal<50000 and deptno=cp_deptNo

l_deptNo Number :=50;
begin

For rec in cur1(l_deptNo)
loop
dbms_output.put_line(‘Employee Number ‘||rec.empno);
end loop;

end;

II. DYNAMIC CURSOR s : 

Oracle REF CURSOR Types:
With the REF_CURSOR you can return a recordset/cursor from a stored procedure
(i.e Ref Cursors can have Record/s as return types.)
Could be declared once and defined many times in different procedures. 

a)Strong : For the strong ref cursor the returning columns with data type and length need to be known at compile time.
b)Weak :For the weak ref cursor the structure does not need to be known at compile time.

Example For the Ref Cursor :

–SPECK PACKAGE 
CREATE OR REPLACE PACKAGE REFCURSOR_PKG
 AS
  TYPE WEAK_REF_CURSOR IS REF CURSOR; — Until 9i
  TYPE STRONG_REF_CURSOR IS REF CURSOR RETURN EMP%ROWTYPE;

END REFCURSOR_PKG;

The pl/sql procedure that returns a ref-cursor looks like this:


–BODY PACKAGE 
CREATE OR REPLACE PACKAGE BODY REFCURSOR_PKG
AS
— For Weak Ref Cursor: 
PROCEDURE 
WEAK_REF_CUR_PRC( p_deptno IN number,
                  p_cursor OUT REFCURSOR_PKG.WEAK_REF_CURSOR — Until 9i
                  —- From 9i (p_cursor OUT SYS_REFCURSOR )—-
                 )
IS

BEGIN

  OPEN p_cursor FOR
  SELECT *  FROM   emp
  WHERE  deptno = p_deptno;
end WEAK_REF_CUR_PRC;

— For Strong Ref Cursor: 
PROCEDURE 
STRONG_REF_CUR_PRC( p_deptno IN number,
                    p_cursor OUT REFCURSOR_PKG.STRONG_REF_CURSOR
                  )
IS

BEGIN
  SELECT *  FROM   emp
  WHERE  deptno = p_deptno;
  end STRONG_REF_CUR_PRC;
 END REFCURSOR_PKG;
/by
, , , ,

ERRBUF and RETCODE in Concurrent Program

For each Concurrent program we must use mandatory parameters : ERRBUF and RETCODE.

ERRBUF: It return the error message. 
For you program if you get any error in exception block you can assign the error message to this parameter. 
This error message you can see after concurrent program run go to details button it will open details in that Completion Text filed will show your errbuf.

RETCODE: This parameter returns the status of the concurrent program.
0- Success –Completed
1- Warning — Yellow color
2- Error — Red

These parameters we call as a first parameters for the program.

Ex:
Create procedure  CProgram(ERRBUF out varchar2, RETCODE  out varchar2, person_id in NUmber)
as
begin
<Declaration Goes  Here>
begin
<Code Block1 Here>

exception
when no_data_found then
retcode := 1;
errbuf:= ‘RetCode : Warning’;
end;
…
<Code Block2 Here>
…
retcode:= 0;
commit;
exception
when others then

retcode := 2;

errbuf:= ‘errbuf:= ‘RetCode : Error’;

end;
/by
, , , ,

What are the different types of Oracle Exceptions?

The main classification is Oracle Defined and User defined Exceptions

User Defined:

User defined exceptions are defined in many ways.

a.New user defined Exceptions can be created using existing Oracle defined Exceptions

b.Entirely new Exceptions can be according to a users need. 
Eg: Raise an exception if employee salry should not be negative value.

There are mainly 3 ways to use User Defined Exceptions

A. RAISE EXCEPTION

declare
exc_user Exception; –declare exception
begin
–code–
exception when others then
raise exc_user;
–exception raised
exception
when exc_user then
–handler for exc_user. exception handled
when others then
–handler for others
end;

B. RAISE_APPLICATION_ERROR

declare
exc_user Exception; –declare exception
begin
if (<logic>) then
RAISE_APPLICATION_ERROR(-20001, exc_user);
–code greater than -20000
exception
when exc_user then
 –handled when error occurs with the specified Oracle Code
when others then
–handler for others

C. PRAGMA EXCEPTION_INIT

declare
exc_user Exception; –declare exception
PRAGMA EXCEPTION_INIT(exc_user, -<oracle_error_code>); 
— Oracle code with ‘-‘sign
begin
–code–
exception
when exc_user then
–handler for exc_user –handled when error occurs with the specified Oracle Code
when others then
–handler for others

Oracle defined:

There are many built in exceptions which we can use. Most commonly used ones are:

a.NO_DATA_FOUND
b.TOO_MANY_ROWS_FOUND
c.ZERO_DIVIDE
d.CURSOR_ALREADY_OPEN
e.INVALID_CURSOR
f.DUP_VALUE_ON_INDEX
g.VALUE_ERROR
h.INVALID_NUMBER

Difference between Value Error and Invalid Number
See below are the examples:

Eg1:
SQL> select to_number (‘a’)
  from dual

Error :ORA-01722: invalid number

Eg2:

SQL>   declare
       n number;
   begin
      n := ‘a’;
    exception
     when value_error
    then
     dbms_output.put_line (‘Value Error’);
   end;

 
Error: Value Error

Eg3:

SQL> declare
     n number;
    begin
         select ‘a’ into n
         from dual ;
     exception
     when value_error then
     dbms_output.put_line (‘Value Error’);
    end;


Error: Value Error

Eg4:
SQL> declare
     n number;
    begin
        select to_number(‘a’) into n
        from dual;
    exception
    when value_error  then
    dbms_output.put_line (‘Value Error’);
    when invalid_number then
    dbms_output.put_line (‘Invalid Number’);
 end;

Error:Invalid Number
/by
, , , , , ,

Query to get Concurrent program name and its parameter

Below query can be used to get concurrent program name and its parameter.

SELECT fcpl.user_concurrent_program_name
     , fcp.concurrent_program_name
     , par.end_user_column_name
     , par.form_left_prompt prompt
     , par.enabled_flag
     , par.required_flag
     , par.display_flag
FROM   fnd_concurrent_programs fcp
     , fnd_concurrent_programs_tl fcpl
     , fnd_descr_flex_col_usage_vl par
WHERE  fcp.concurrent_program_id = fcpl.concurrent_program_id
AND    fcpl.user_concurrent_program_name = &conc_prg_name
AND    fcpl.LANGUAGE = ‘US’
AND    par.descriptive_flexfield_name = ‘$SRS$.’ || fcp.concurrent_program_name

/by