To find the concurrent request program submitted through backend query in oracle apps r12

select * from apps.fnd_concurrent_requests
where concurrent_program_id in (select concurrent_program_id from apps.fnd_concurrent_programs where concurrent_program_name like 'XX_O2C_RAXINV')
order by request_id desc;

From the above query take the requested by and query below with user id to see the user name

select * from apps.fnd_user where user_id=1005316;

Take the responsibility id from above query and search below to find the responsibility name.

select * from apps.fnd_responsibility_vl where responsibility_id=54314;

The argument 1 to argument 15 from first query shows the parameters that are from querying the concurrent program name in system administrator ->program name-> parameters not while submitting passing parameters. Refer that with while submitting parameter names

PRAGMA in oracle

PRAGMA: 

To create exceptions with standard oracle errors by using the PRAGMA EXCEPTION_INIT function are called non- predefined exceptions.

PRAGMA( also called as pseudoinstructions) is the keyword that signifies that the statement is a compiler directive, which is not processed when the pl/sql block is executed.Rather it directs the pl/sql compiler to interpret all occurences of the exception name within the block as the associated oracle server error number.

SYNTAX:

declare 

exception EXCEPTION;

PRAGMA EXCEPTION_INIT(exception,error_number);

error_number is the standard oracle server error number

Non pre defined error i.e user defined error

To trap oracle server error number -01400

("Cannot insert null");

set serveroutput on

declare

insert_excep exception;

PRAGMA EXCEPTION_INIT

(insert_excep,-01400);

begin

insert into departments

(department_id,department_name) values (280,null);

EXCEPTION

WHEN insert_excep THEN

DBMS_OUTPUT.PUT_LINE('Insert Operation Failed');

DBMS_OUTPUT.PUT_LINE(SQLERRM);

END;

/

where department_name is not null column.

Cursors,parameter cursors

Cursors: A cursor is like a pointer in c. It holds the address location instead of item.

The cursor is of 2 types

a) implicit cursor
b) explicit cursor

• Implicit cursors:

Declared and managed by pl/sql for all dml and pl/sql select statements.

• Explicit cursors:

For queries return more than one row,explicit cursors are declared and managed by the programmer and manipulated through specific statements in the block's executable actions.

Explicit cursor attributes:

Attribute	Type	Description
%ISOPEN	Boolean	Evaluates to true if the cursor is open
%NOTFOUND	Boolean	Evaluates to true if the recent fetch does not return a row
%FOUND	Boolean	Evaluates to true if the recent fetch returns a row
%ROWCOUNT	Number	Evaluates to the total number of rows

ex-

Begin

open emp_cursor;

loop

fetch emp_cursor into empno,ename;

EXIT WHEN emp_cursor%ROWCOUNT > 10 OR  emp_cursor%NOTFOUND;

end loop;

close emp_cursor;

end;

PARAMETER CURSORS: 

We can pass values inside the cursor by using a parameterized cursors.

ex-

set serveroutput on

declare

dept_id number;

lname varchar2(15);

 cursor emp_cursor ( deptno NUMBER ) IS

select employee_id,last_name

from employees

where department_id=deptno;

BEGIN

open emp_cursor(10);

...

close emp_cursor;

open emp_cursor(20);

...

close emp_cursor;

...

end;

Difference between stored procedure and functions in oracle

Stored Procedures are pre-compile objects which are compiled for first time and its compiled format is saved which executes (compiled code) whenever it is called. But Function is compiled and executed every time when it is called. For more about stored procedure and function refer the articles Different types of Stored Procedure and Different types of Function.

Basic Difference

1. Function must return a value but in Stored Procedure it is optional( Procedure can return zero or n values).
2. Functions can have only input parameters for it whereas Procedures can have input/output parameters .
3. Functions can be called from Procedure whereas Procedures cannot be called from Function.

Advance Difference

1. Procedure allows SELECT as well as DML(INSERT/UPDATE/DELETE) statement in it whereas Function allows only SELECT statement in it.
2. Procedures can not be utilized in a SELECT statement whereas Function can be embedded in a SELECT statement.
3. Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section whereas Function can be.
4. The most important feature of stored procedures over function is to retention and reuse the execution plan while in case of function it will be compiled every time.
5. Functions that return tables can be treated as another rowset. This can be used in JOINs with other tables.
6. Inline Function can be though of as views that take parameters and can be used in JOINs and other Rowset operations.
7. Exception can be handled by try-catch block in a Procedure whereas try-catch block cannot be used in a Function.
8. We can go for Transaction Management in Procedure whereas we can't go in Function.

fndload - downloading valueset set with values

Fndload - downloading valueset set with values

FNDLOAD apps/pswapps 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XXPMI_PRICELISTS.ldt VALUE_SET FLEX_VALUE_SET_NAME='XXPMI_PRICELISTS'

Uploading Value Set-

FNDLOAD $P_APPS_USER/$P_APPS_PASSWD 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct XXPMI_PRICELISTS.ldt

Creating a function with cursor with parameter example

Creating a function with cursor with parameter example


create or replace
FUNCTION xx_get_serial_number(p_del_detail_ID IN NUMBER) RETURN VARCHAR2 IS
CURSOR get_ser_num(C_delivery_detail_ID NUMBER) IS
select fm_serial_number
from WSH_SERIAL_NUMBERS
where delivery_detail_ID = C_delivery_detail_ID; --32299178
l_result varchar2(32767);
l_value varchar2(32767) := NULL;
l_ser_num varchar2(120);

i         number :=1;
BEGIN
FOR GET_SER_NUM_REC IN GET_SER_NUM(p_del_detail_ID)
LOOP
l_ser_num := GET_SER_NUM_REC.fm_serial_number;

if l_ser_num is not null then
IF i=1 then
l_value:=l_ser_num;
elsif i mod 4 !=0 then

l_value := l_value ||' '||','|| l_ser_num ;
else
l_value := l_value ||' '||','|| l_ser_num||chr(9) ;

end if;
i := i +1;


end if;


END LOOP;
RETURN (l_value);
END;

Cursor showing first group by then having then order by clause (Order Clause)

CURSOR xx_docimg_receipts_in (P_PO_Line_ID IN VARCHAR2) IS
      SELECT rh.creation_date, rh.receipt_num, rh.packing_slip, rt.po_line_id, rt.po_line_location_id,
                            SUM(NVL(rt.quantity,0) *
                         DECODE(rt.transaction_type,'RECEIVE',1,'RETURN TO VENDOR', -1, 'REJECT', -1, 'CORRECT',DECODE(rt1.transaction_type, 'RETURN TO VENDOR', 1, -1),1)) -
                         ( SUM(NVL(rt.quantity_billed,0))
                       --  + SUM(NVL(xx.quantity_applied,0))  commented by Kiran
                         ) qty_not_billed
      FROM rcv_transactions rt,  rcv_shipment_headers rh, rcv_transactions rt1,
                 ( SELECT  xx.receipt_number,  sum(nvl((xx.amount/(pll.quantity*pll.unit_price)) * pll.quantity,0)) quantity_applied ----Added By Junaid Khan
                        --SUM(NVL(xx.quantity_invoiced,0)) quantity_applied -- Commented By Junaid Khan
                      FROM xx_ap_inv_lines_interface xx,
                     po_lines_all pll
                     WHERE xx.receipt_number IS NOT NULL
                     and xx.po_line_id = pll.po_line_id
                     GROUP BY xx.receipt_number ) xx
      WHERE rt.shipment_header_id = rh.shipment_header_id AND
                        rh.receipt_num = xx.receipt_number(+) AND
                        rt.parent_transaction_id = rt1.transaction_id(+)  AND
                        rt.transaction_type IN ('RECEIVE','RETURN TO VENDOR', 'CORRECT', 'REJECT') AND
                        rt.po_line_id = P_PO_Line_ID
      GROUP BY  rh.creation_date, rh.receipt_num, rh.packing_slip, rt.po_line_id, rt.po_line_location_id
         HAVING  SUM(NVL(rt.quantity,0) *
                         DECODE(rt.transaction_type,'RECEIVE',1,'RETURN TO VENDOR', -1, 'REJECT', -1, 'CORRECT',DECODE(rt1.transaction_type, 'RETURN TO VENDOR', 1, -1),1)) -
                         ( SUM(NVL(rt.quantity_billed,0))
                        -- + SUM(NVL(xx.quantity_applied,0))  commented by Kiran
                         ) <> 0
      ORDER BY rh.creation_date;