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.

Purchase Orders(PO)

Purchase Orders

•         PO will be created after receiving the quotation based on the quote analysis

•         Depends on the requirement management will take the decision. We have 4 types of Pos

–        Standard

–        Planned

–        Blanket  Agrement

–        Contract Agrement

Types of Purchase Orders

Property	Standard	Planned	Blanket	Contract
Terms& Conditions known	Y	Y	Y	Y
Goods/Services known	Y	Y	Y	N
Pricing Known	Y	Y	May be	N
Quantity Known	Y	Y	N	N
A/C Distribution Known	Y	Y	N	N
Delivery schedule Known	Y	May be	N	N
Can be encumbered	Y	Y	N	N
Can encumber releases	N/A	Y	Y	N/A

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

•         Suggested Use: Choose a standard purchase order when you require vendor commitment to specific items/services, quantities and delivery schedules, but a long-term agreement is not appropriate.

•         Blanket Purchase Agreements: 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. Blanket purchase agreements can be created for a single organization or to be shared by different business units of your organization (global agreements).
Much organization it is sometimes called as "standing order" or an "open order".

•         Suggested Use: Choose a blanket agreement when you've negotiated volume discounts and want to create releases against these negotiated volumes, or when you commit to specific items, quantities or amounts

•         Blanket Releases: 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 affectivity dates).

•         Contract Purchase Agreements/Order: 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.

•         Suggested Use : Negotiate pricing on your entire volume of business. Use a contract to manage terms and conditions for this type of negotiation; couple it with a catalog quotation to reference pricing on a per item basis when you create standard purchase order lines.

•         Global Agreements: You may need to negotiate based on an enterprises' total global purchase volume to enable centralizing the buying activity across a broad and sometimes diverse set of businesses. Using global agreements (a special type of blanket purchase agreement), buyers can negotiate enterprise-wide pricing, business by business, then execute and manage those agreements in one central shared environment. Enterprise organizations can then access the agreement to create purchase orders that leverage pre-negotiated prices and terms.

•         Planned Purchase Orders: A planned purchase order is a long-term agreement committing to buy items or services from a single source. You must specify tentative delivery schedules and all details for goods or services that you want to buy, including charge account, quantities, and estimated cost.

•         Suggested Use: Choose a planned purchase order when you want to encumber the order before creating releases. Also use to provide vendor scheduling for capacity management while issuing releases to confirm order commitment.

•         Apart from these there are few more types may be defined as per there industry segment , but more or less they can be fit into any one of the above mentions type.

Quatations

Quotations

•         Once we send the RFQ to the supplier either by the mail/Fax we will receive quotations from the suppliers.

•         Quotations are again of 3 types

–        Bid

–        Catalog

–        Standard

–        For Bid RFQ we will receive the Bid Quotations

–        For Catalog RFQ we will receive the Catalog Quotations

–        For Standard RFQ we will receive the Standard Quotations

Request For Quotation (RFQ)

•         RFQ information will be entered at three levels.

–        Header level

–        Line level

–        Shipment (Bid RFQ)/Price Breaks(Catalog/standard)

–        Currency

–        Terms

–        Price breaks

–        Suppliers

–        We can generate RFQ No automatically.

•         Select * from po_headers_all where segment1=‘306’

•         PKàPo_header_id=11905

•         Select * from po_lines_all where po_header_id=11905

•         PKàPo_line_id=12193

•         Select * from po_line_locations_all where po_line_id=12913

•         PKàLine_location_id

•         Select * from fnd_currencies

•         Select * from ap_terms

•         Select * from ap_term_lines

•         Select * from hr_locations 

•         Select * from po_document_types_all

•         Open the RFQ Form

•         Select RFQ type at the header level

•         Ship_to and bill_to locations

•         Enter the item details at line level

•         Select the terms button and create terms & conditions.

•         Select the price breaks button enter price break details

•         Select supplier button

•         Enter the supplier details who are going to receive the RFQ documents.

•         Save the transaction.

•         Payment terms – Immediate/Installments

•         Freight terms    – Transportation charges

•         Carrier                                               – Transport Name

•         Foot on board(FOB) – Responsibility for the

                                                                                       material damage

•         Due date   -- last date for accepting the RFQ document

•         Close date– RFQ Close date where company will decide                                                                          the better quotation.

•         Ship to location– where supplier need to send the material.

•         Bill to location  -- where supplier need to send the bill.

•         Open the quotations form

•         Enter the quotation details by selecting quote type, supplier, supplier site and so on

•         Type_lookup_code=‘quotation/RFQ/PO’

Requisitions

Requisitions

•         Its one of the document that will be prepared by the employees whenever they need the materials

•         We will find 2 types of requisitions

–        INTERNAL

–        PURCHASE

•         If materials are available in other organization we will transfer/purchase the material from the internal requisition.

•         If material are not available then we will purchase it from suppliers by using purchase requisition.

•         Select the responsibility called “Purchasing Vision Operations (USA)”.

•         Requisitionsà Requisitions

•         Select * from PO_REQUISITION_HEADERS_ALL where segment1=‘1651’

•         PKà Requisition_Header_id =11438

•         Segment1àRequisition Number

•         Select * from PO_REQUISITION_LINES_ALL where Requisition_header_id =11438

•         PKà Requisition_line_id =9901

•         Select * from PO_REQ_DISTRIBUTIONS_ALL where Requisition_line_id =9901

•         PKà Distribution_id

•         PK-->REQUISITION_HEADER_ID

•         AUTHORIZATION_STATUS=INCOMPLETE

–                      IN PROCESS

•                                             APPROVED                           

•         TYPE_LOOKUP_CODE=PURCHASE/INTERNAL

•         Open the Requisition form

–        Enter the header level information

–        Enter line level information

–        Enter the item ,qty, price, need by date

•         Select the distributions button

–        Enter the distribution details

–        Save the transaction.

•         Select the approve button for approval

•         Go to requisition summary form

–        Enter the requisition number

–        Select find button

–        We can verify the Requisition Status to find whether approved or not approved.

•         We can also cancel the requisition after approval

–        Tools

•         Control options

•         Select the option called cancel Requisition

•         Enter the reason and press OK button

•         Again query the requisition we find it as “Cancelled”.

•         We can find out the action history of the requisition by using

–        Tools

•         View action history

•         It will show you the details like who has submitted ,approved and cancelled.

•         Requisition number will be generated by the system automatically.

•         If we want to enter it manually we have to set up the following

–        Setup àOraganizations àpurchasing Optionsà numbering Tab àselect requisition entry as manual àtype(alpha numeric/Numeric)

•         After requisition is approved as per the company requirement we will create RFQ doc.

•         Bid RFQ:

–        It will be used for large/expensive,piece of equipment that we have never ordered before .( Eg. diamond)

–        We cannot specify the price breaks for Bid RFQ.

•         Catalog RFQ:

–        It is used for high volume items for which we will receive information regularly where we will find the price breaks at different quantity levels. (nuts & bolts)

•         Standard RFQ:

–        It is used for items which are used only once not regularly. It also includes price breaks at the different qty. levels. (Furniture)