Creating Function

Functions: Function is similar to a procedure except that a function must return a value
to the statement from which it is called. The syntax for creating a function is given
below.
Syntax: SQL> CREATE [OR REPLACE] FUNCTION function_name
[(parameter_name[IN | OUT | IN OUT] type [,….])]
RETURN type;
{IS | AS}
BEGIN
Function_body
END function_name;
Ex: create or replace function haritha_func(p_empno number)
return varchar2 is
v_ename varchar2(20);
v_sal number;
begin
select ename,sal into v_ename, v_sal from emp where empno=p_empno;
if v_sal>=6000 then
return 'TRUE';
else
return 'FALSE';
end if;
end haritha_func;
Execution: SQL> select haritha_func(7788) from dual;
Ex: SQL> create or replace function haritha_fun(p_radius number)
return number as
v_pi number := 3.141;
v_area number;
begin
v_area := v_pi * POWER(p_radius,2);
return v_area;
end haritha_fun;

Execution: SQL> select haritha_fun(7) from dual;
Dropping Function and Procedure: When we want to drop the function or procedure
then we use this DROP statement the syntax for the DROP is given below.
Syntax for Dropping Function:
DROP FUNCTION function_name;
Syntax for Dropping Procedure:
DROP PROCEDURE procedure_name;

Creating Procedure

Oracle Application 11.5.9 Bias Technologies

10

Procedures: Procedures are also known as subprograms. Given below is the syntax for

the creation of the procedure.

Syntax: CREATE [OR REPLACE] PROCEDURE procedure_name

[(argument [{IN | OUT | IN OUT}] type,

…

[(argument [{IN | OUT | IN OUT}] type) ] {IS | AS}

BEGIN

procedure_body

END procedure_name;

Ex: SQL> create or replace procedure razia_proc as

v_sal number;

cursor razia_cursor is select * from emp order by deptno;

begin

v_sal := &v_s;

for i in razia_cursor

loop

if(i.sal > v_sal) then

dbms_output.put_line( 'Employee Name: ' || i.ename);

end if;

end loop;

exception

when others then

dbms_output.put_line( 'YOU HAVE PERFORMED AN ILLEGAL OPERATION');

dbms_output.put_line( 'THE PROGRAM MAY TERMINATE NOW');

end razia_proc;

Execution: exec razia_proc;

Passing IN Parameter to the Procedures:

Ex: SQL> create or replace procedure kanthi_proc(p_inpar in number) as

v_name varchar2(30);

begin

select ename into v_name from emp where empno=p_inpar;

dbms_output.put_line( 'Employee Name: ' || v_name);

exception

when others then

dbms_output.put_line( 'YOU HAVE PERFORMED ILLEGAL OPERATION');

end kanthi_proc;

Using IN and OUT parameters to Procedures:

Ex: SQL> create or replace procedure

shabbir_proc(p_outpar out varchar2,p_inpar in number) as

begin

select ename into p_outpar from emp where empno=p_inpar;

end;

--To Catch the Output variable out side the procedure in the SQL

declare

v_name varchar2(20);

v_num number;

begin

mypro(v_name,&v_num);

dbms_output.put_line( 'Employee Name: ' || v_name);

end shabbir_proc;

PO Types

Purchase Order Types
Oracle Purchasing provides the following purchase order types: Standard Purchase Order, Planned Purchase Order, Blanket Purchase Agreement and Contract Purchase Agreement. You can use the Document Name field in the Document Types window to change the names of these documents. For example, if you enter Regular Purchase Order in the Document Name field for the Standard Purchase Order type, your choices in the Type field in the Purchase Orders window will be Regular Purchase Order, Planned Purchase Order, Blanket Purchase Agreement and Contract Purchase Agreement

•         Standard Purchase Orders
You generally create standard purchase orders for one–time purchase of various items. You create standard purchase orders when you know the details of the goods or services you require, estimated costs, quantities, delivery schedules, and accounting distributions. If you use encumbrance accounting, the purchase order may be encumbered since the required information is known

•         Blanket Purchase Agreements (BPA)
You create blanket purchase agreements when you know the detail of the goods or services you plan to buy from a specific supplier in a period, but you do not yet know the detail of your delivery schedules. You can use blanket purchase agreements to specify negotiated prices for your items before actually purchasing them. BPA are widely used in product manufacturing companies.

•         You can issue a blanket release against a blanket purchase agreement to place the actual order (as long as the release is within the blanket agreement effectivety dates). If you use encumbrance accounting, you can encumber each release

Contract Purchase Agreements
You create contract purchase agreements with your suppliers to agree on specific terms and conditions without indicating the goods and services that you will be purchasing. You can later issue standard purchase orders referencing your contracts, and you can encumber these purchase orders if you use encumbrance accounting

•         Planned Purchase Order
You create a planned purchase order when you want to establish a long term agreement with a single source supplier with a commitment to buy goods or services. Planned purchase orders include tentative delivery schedules and accounting distributions. You then create scheduled releases against the planned purchase order to actually order the goods or services.

•         A planned purchase order is a type of purchase order you issue before you order actual delivery of goods and services for specific dates and locations. A scheduled release is issued against a planned purchase order to place the actual order. You can also change the accounting distributions on each release and the system will reverse the encumbrance for the planned purchase order and create a new encumbrance for the release

•         Standard purchase orders:

•         While creating the standard purchase orders we will specify the item,price,quantity,delivery schedule and so on.

•         Planned purchase orders:

•         While creating the planned purchase orders we will specify the item,quantity but the delivery schedule may or may not be there.

•         Blanket purchase orders:

•         Company will have the agreement with the supplier for a specific item(s) and agreed amount. Whenever the company requires they will release the agreement

•         Contract Purchase Orders:

•         Contract purchase order is also like the blanket agreement but the items will not be specified. Company could be purchased any item from the supplier which are available with them.

•         Open the PO Form

•         Create PO by selecting PO type as Blanket

•         Enter the agreed amount

•         Enter the line level details

•         Click on approve button.

•         Once blanket po is approved it goes to the releases form

•         Go to releases form and enter po no system automatically gives you the supplier and release details

•         Enter the information at line level

•         Select the distribution button and enter the dist details

•         Save the transactions and click approve for approval

•         Select the agreement button to findout the agreement totals and release total amount

Match Approval option:

•         While creating the PO at the shipment level in the more tab we will specify the ‘Match approval option’. As per the selected option receipt and invoice will be generated.

–        2 – Way                      POàInvoice

–        3 – Way                      POàReceiptàInvoice

–        4 – Way                      POàReceiptàInspectionàInvoice

•         Direct delivery                                               à 2-Way

•         Standard                                                                    à 3-Way

•         Inspection revision                à 4-Way

Types of Receipts:

•         Autocreate:

–        Autocreate process will be useful in generating the RFQ Doc/PO Doc automatically instead of the manual option.

–        Once the requisition is approved we take the requisition number by using the ‘autocreate’ option we will select the RFQ/PO.