Showing posts with label Oracle Purchasing (PO). Show all posts
Showing posts with label Oracle Purchasing (PO). Show all posts

Wednesday, April 16, 2014

Brief notes about Purchasing in Oracle APPS R12

Create requisition to procure goods and service with supplier information, delivery instructions, multiple accounting distributions, and notes to buyers, approvers, and receivers. A request for quotation (RFQ) is sent to a supplier to request pricing and other information for an item. A quotation is the supplier’s response to that RFQ. Identify requisitions that require supplier quotations and automatically create a RFQ Or create manually and send it thru’ Fax or iSupplier portal. Record supplier quotations from a catalog, telephone conversation, or response from your RFQ. You can also receive quotations electronically and import as Quotations (catalog). Review, analyze, evaluate and approve supplier quotations. Create standard purchase order, BPA and blanket releases. Inform your suppliers of your shipment schedule requirements. Record supplier acceptances of your purchase order’s terms and conditions. Provide a quantity and price for each item you are ordering. Alternatively, you should also be able to create your purchase order simply by providing an amount if you are ordering a service that you cannot break down by price and quantity. Enter goods and service receipt information against the PO using routing controls viz: Direct delivery, standard receipt or standard receipt with inspection. Transfer and deliver goods using the Receiving Transactions window. If you want to perform an inspection transaction, you can open the Inspections window to specify accepted and rejected quantities.
This cycle involves following steps from creating a requisition to transfer the details to GL.
1.       Create Requisition
Approve requisition
2.       Create Purchase Order
Approve Purchase Order
3.       Create Receipt after receiving the goods
4.       Create an Invoice in AP
5.       Pay the invoice
6.       Transfer, Import and Post Journal to GL

Basic Components of Procurement

Requisition

Requisition is nothing but a formal request to buy something (like Inventory material, office supplies etc) needed for the enterprise. Only an employee can create one.
With on-line requisitions, you can centralize your purchasing department, source your requisitions with the best suppliers, and ensure that you obtain the appropriate management approval before creating purchase orders from requisitions.

Purchasing provides you with the features you need to satisfy the following basic requisition needs. You should be able to:

·         Create, edit, and review requisition information on-line. You should also be able to enter suggested supplier information, delivery instructions, multiple accounting distributions, and notes to buyers, approvers, and receivers.
·         Review the current status and action history of your requisitions. You should always know who approves requisitions and whether they are in the approval, purchasing, receiving, or delivery stage.
·         Route requisitions according to your approval structure. You should also be able to set authorization limits by amount, charge account, item category, and location.
·         Review and approve requisitions that need your approval. You should also be able to see the full requisition detail and review the action history before you approve a requisition.
·         Print requisitions (with status Approved, Cancelled, Rejected, In Process, Pre-Approved, and Returned) for off-line review and approval. You should always be able to track the status of requisitions through the approval process.
·         Import requisitions from other systems such as material or distributions requirement planning applications
·         Perform on-line funds checking before creating requisitions. You should always know how your planned expenses compare to your budget.
·         Automatically source requisitions from outstanding blanket purchase agreements or quotations you have received from suppliers
·         Create requisitions quickly and easily for commonly purchased items
·         Provide attachments as notes on requisition headers and lines
·         Assign requisition lines to buyers and review buyer assignments for requisition lines
·         Forward all requisitions awaiting approval from one approver to an alternate approver. Within your security and approval constraints, you should be able to reroute requisitions from one approver to another whenever you want.
·         Record suggested foreign currency information for each requisition line

Requisition Types:

1.       Purchasing Requisition: Purchase requisitions are used for requesting material from suppliers.
2.       Internal Requisition: Internal requisitions provide the mechanism for requesting and transferring material from one inventory to other inventory

RFQ

A request for quotation (RFQ) is sent to a supplier to request pricing and other information for an item. A quotation is the supplier’s response to that RFQ. You send an RFQ to a supplier by fax, making a phone call, or using Oracle iSupplier Portal. A supplier can send a quotation, whether or not in response to an RFQ, is through the Purchasing Documents Open Interface.If you don’t receive quotations electronically from your supplier, you can create the quotation manually using the Quotations window, or copy the quotation from an RFQ.

Using Quotation for Purchase Order

When you create a purchase order (manually or from requisitions), you can use the Supplier Item Catalog window to retrieve quotation information. (The Supplier Item Catalog window can include quotations sent to you by your supplier through the Purchasing Documents Open Interface.) Purchasing provides all your approved quotation shipment information for a specific item or manufacturing category. You can copy this quotation shipment to an existing blanket purchase agreement or standard purchase order when you add this item or purchasing category to a purchase order line. You can sort this quotation information according to your needs, using criteria such as price or quantity. You can easily evaluate the source that is best for an item.
After you select the quotation shipment you want to use, Purchasing copies the item unit price, quantity, unit of measure, supplier product number, inspection required status, receipt required status, quotation number, quotation type, and supplier quotation number on your purchase order. Purchasing also copies the quotation item description on your purchase order if you define your items to do so. Purchasing automatically warns you when the terms and conditions of the quotation are different from the terms and conditions of your purchase order. The original purchase order terms and conditions remain unchanged.

Types of Quotations

There are three types of quotations and RFQs that come with Purchasing by default:

Bid: Used for a specific, fixed quantity, location, and date. For example, a Bid would be used for a large or expensive piece of equipment that you’ve never ordered before, or for an item that incurs transportation or other special costs. You cannot specify price breaks for a Bid quotation or RFQ.
Standard: Used for items you’ll need only once or not very often, but not necessarily for a specific, fixed quantity, location, and date. For example, you could use a Catalog quotation or RFQ for office supplies, but use a Standard quotation or RFQ for a special type of pen you don’t order very often. A Standard quotation or RFQ also includes price breaks at different quantity levels.
Catalog: Used for high–volume items or items for which your supplier sends you information regularly. A Catalog quotation or RFQ also includes price breaks at different quantity levels.

For all three types, you can define effectivity dates at the header level.
For Catalog and Standard quotations, you can also specify effectivity dates for individual price breaks. (For a Bid, you cannot specify effectivity dates at the shipment level.) You can also define your own RFQ or quotation types using the
Document Types window.

Purchase Order

Purchasing provides the Purchase Orders window that you can use to enter Standard and planned purchase orders as well as Blanket and Contract purchase agreements. You must be defined as a buyer to use this window.
Purchasing provides you the features you need to satisfy the following purchasing needs. You should be able to:
·         Review all of your purchases with your suppliers to negotiate better discounts
·         Create purchase orders simply by entering a supplier and item details
·         Create standard purchase orders and blanket releases from both on-line and paper requisitions
·         Create accurate and detailed accounting information so that you charge purchases to the appropriate departments
·         Check your funds availability while creating purchase orders.
·         Review the status and history of your purchase orders at any time for all the information you need
·         Print purchase orders flexibly by using a number of print options
·         Inform your suppliers of your shipment schedule requirements
·         Record supplier acceptances of your purchase orders. You always know whether your suppliers have received and accepted your purchase order terms and conditions
·         Create your purchase orders by providing a quantity and price for each item you are ordering. Alternatively, you should also be able to create your purchase order simply by providing an amount if you are ordering a service that you cannot break down by price and quantity

Purchase Order Types

There are mainly 4 types of Purchase Orders
·         Standard Purchase Order
·         Blanket Purchase Agreements
·         Contract Purchase Agreements
·         Planned Purchase Orders

Standard Purchase Order

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.

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.
You can issue a Blanket release against a BPA to place the actual order (as long as the release is within the blanket agreement effectively dates).

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.

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.
You can issue scheduled releases against a planned purchase order to place the actual orders. If you use encumbrance accounting, you can use the planned purchase order to reserve funds for long term agreements.

Wednesday, January 15, 2014

Delete PO by using API

API "po_headers_sv1.delete_po" can be used to delete POs.

Parameters : 
            X_po_header_id     => PO Header ID
            X_type_lookup_code => Type Lookup Code
            -- STANDARD/PLANNED/BLANKET/CONTRACT
            p_skip_validation  => N/Y
p_skip_validation -> By default it is 'N', if it is 'Y', It checks the encumbered flag in po distribution table against the po header.

Note:- INCOMPLETE POs can be deleted from Purchase Order form itself.
       Though this API can delete Approved POs as well, it is recomended that use this API to delete only INCOMPLETE Pos, because APPROVED POs have entry in inventory and archive tables and this API does not delete the related entries from these tables. Records will be deleted only from base tables.

R12 tested Sample Script :
DECLARE
   l_result                     BOOLEAN                                      ;
   l_po_header_id        NUMBER          := <po_header_id> ;
   l_type_lookup_code VARCHAR2(8) := <lookup_code>  ;
   l_validation               VARCHAR2(1) := <validation_flag> ;
BEGIN
   DBMS_OUTPUT.put_line ( 'API PO_HEADERS_SV1 Call to delete PO with header :'||l_po_header_id);
   l_result := po_headers_sv1.delete_po (X_po_header_id         => l_po_header_id
                                                             ,X_type_lookup_code => l_type_lookup_code
                                                             ,p_skip_validation        => l_validation);

   IF l_result = TRUE THEN
      COMMIT;
      DBMS_OUTPUT.put_line ( 'PO with header: '||l_po_header_id||',Deleted Successfully');
   ELSE
      ROLLBACK;
      DBMS_OUTPUT.put_line ( 'PO with header: '||l_po_header_id||',Failed to Delete');
   END IF;
   DBMS_OUTPUT.put_line ( 'Deletion Process Over');
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.put_line ( 'Error : '|| SQLERRM);
END;

Cancelling PO by Using API called PO_Document_Control_PUB.control_document

PO_Document_Control_PUB.control_document ? 
  • The PL/SQL procedure, PO_Document_Control_PUB.control_document , provides the ability to cancel Oracle Purchasing documents directly through an API.
  • The API will perform all of the same processing that would be done if a cancellation was requested through the PO Summary Control Window.
  • Prior to calling the API we should set our global context to reflect the application, user and responsibility used to perform the cancel action. If we do not set this context, the API will not be able to identify or update your data.
--R12 - PO - Sample Script to cancel PO using po_document_control_pub API.sql

DECLARE

l_return_status VARCHAR2 (10);

CURSOR C_PO_CANCEL is

SELECT pha.po_header_id,
pha.org_id,
pha.segment1 po_number,
pha.type_lookup_code,
pha.cancel_flag,
pha.closed_code
FROM po_headers_all pha
WHERE 1=1
AND pha.segment1 = '376729' -- Enter The Purchase Order Number
AND nvl(pha.closed_code,'OPEN') = 'OPEN'
AND nvl(pha.cancel_flag, 'N') = 'N'
AND approved_flag = 'Y';

BEGIN

fnd_global.apps_initialize (user_id => 1804,
resp_id => 20707,
resp_appl_id => 201);

FOR i IN c_po_cancel

LOOP

mo_global.init ('PO');
mo_global.set_policy_context ('S',i.org_id );

DBMS_OUTPUT.PUT_LINE ('Calling API PO_DOCUMENT_CONTROL_PUB.CONTROL_DOCUMENT For Cancelling Documents');

po_document_control_pub.control_document
(p_api_version => 1.0, -- p_api_version
p_init_msg_list => fnd_api.g_true, -- p_init_msg_list
p_commit => fnd_api.g_true, -- p_commit
x_return_status => l_return_status, -- x_return_status
p_doc_type => 'PO', -- p_doc_type
p_doc_subtype => 'STANDARD', -- p_doc_subtype
p_doc_id => i.po_header_id, -- p_doc_id
p_doc_num => NULL, -- p_doc_num
p_release_id => NULL, -- p_release_id
p_release_num => NULL, -- p_release_num
p_doc_line_id => NULL, -- p_doc_line_id
p_doc_line_num => NULL, -- p_doc_line_num
p_doc_line_loc_id => NULL, -- p_doc_line_loc_id
p_doc_shipment_num => NULL, -- p_doc_shipment_num
p_action => 'CANCEL', -- p_action
p_action_date => SYSDATE, -- p_action_date
p_cancel_reason => NULL, -- p_cancel_reason
p_cancel_reqs_flag => 'N', -- p_cancel_reqs_flag
p_print_flag => NULL, -- p_print_flag
p_note_to_vendor => NULL, -- p_note_to_vendor
p_use_gldate =>NULL ,
p_org_id => i.org_id
);

COMMIT;

DBMS_OUTPUT.PUT_LINE('The Return Status of the API is => ' l_return_status);
If l_return_status = 'S' Then

DBMS_OUTPUT.PUT_LINE('The Purchase Order Which is Cancelled Now => ' i.po_number);
Else

DBMS_OUTPUT.PUT_LINE('The Purchase Order =>' i.po_number 'Failed to cancel Due To Following Reason');

-- Get any messages returned by the Cancel API

FOR j IN 1 .. fnd_msg_pub.count_msg
LOOP
DBMS_OUTPUT.put_line (fnd_msg_pub.get
(p_msg_index => j,
p_encoded => 'F'));
END LOOP;
END IF;
END LOOP;
END;

=============================

PO Cancel API Script:

CREATE OR REPLACE PROCEDURE APPS.XXPO_CANCEL_PO
(ERRBUFF VARCHAR2, RETCODE NUMBER, P_DOC_NUM VARCHAR2)
IS
CURSOR FETCH_PO_CUR IS
SELECT * FROM PO_HEADERS_ALL
WHERE SEGMENT1 = P_DOC_NUM
AND ORG_ID = FND_PROFILE.VALUE('ORG_ID');

V_USER_ID NUMBER ;
V_RESPONSIBILITY_ID NUMBER ;
V_RESPONSIBILITY_APPL_ID NUMBER ;
V_ORG_ID VARCHAR2(20) ;
L_RETURN_STATUS VARCHAR2(1) := NULL;
L_MSG_DATA VARCHAR2(2000);
V_SUCCESS_COUNT NUMBER;
V_FAILURE_COUNT NUMBER;

BEGIN
V_SUCCESS_COUNT := 0;
V_FAILURE_COUNT := 0;

FOR FETCH_PO_REC IN FETCH_PO_CUR
LOOP
FND_FILE.PUT_LINE(FND_FILE.LOG , 'START OF CANCEL PO PROCESS API'SYSDATE );
BEGIN
V_USER_ID := FND_GLOBAL.USER_ID ;
V_RESPONSIBILITY_ID := FND_GLOBAL.RESP_ID ;
V_RESPONSIBILITY_APPL_ID := FND_GLOBAL.RESP_APPL_ID;
FND_GLOBAL.APPS_INITIALIZE( V_USER_ID ,V_RESPONSIBILITY_ID,V_RESPONSIBILITY_APPL_ID);
V_ORG_ID:=FND_PROFILE.VALUE('ORG_ID');
MO_GLOBAL.INIT('PO');
MO_GLOBAL.SET_POLICY_CONTEXT('S',V_ORG_ID);
EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG , 'ERROR CODE FOR INITIALIZATION IS :'TO_NUMBER( SQLCODE )' ' 'ERROR MSG FOR INITIALIZATION IS :'SUBSTR( SQLERRM , 1 , 50) ) ;
END ;

FND_MSG_PUB.INITIALIZE ;
FND_FILE.PUT_LINE(FND_FILE.LOG , 'PO NUMBER TO CANCEL IS :'FETCH_PO_REC.SEGMENT1 ) ;

IF FETCH_PO_REC.AUTHORIZATION_STATUS <> 'APPROVED'
THEN
UPDATE PO_HEADERS_ALL
SET AUTHORIZATION_STATUS = 'APPROVED'
WHERE SEGMENT1 = FETCH_PO_REC.SEGMENT1 ;
END IF;

PO_DOCUMENT_CONTROL_PUB.CONTROL_DOCUMENT
(1.0, -- P_API_VERSION
FND_API.G_TRUE, -- P_INIT_MSG_LIST
FND_API.G_TRUE, -- P_COMMIT
L_RETURN_STATUS,-- X_RETURN_STATUS
'PO', -- P_DOC_TYPE
'STANDARD', -- P_DOC_SUBTYPE
NULL, -- P_DOC_ID
FETCH_PO_REC.SEGMENT1, -- P_DOC_NUM
NULL, -- P_RELEASE_ID
NULL, -- P_RELEASE_NUM
NULL, -- P_DOC_LINE_ID
NULL, -- P_DOC_LINE_NUM
NULL, -- P_DOC_LINE_LOC_ID
NULL, -- P_DOC_SHIPMENT_NUM
'CANCEL', -- P_ACTION
SYSDATE, -- P_ACTION_DATE
NULL, -- P_CANCEL_REASON
'N', -- P_CANCEL_REQS_FLAG
NULL, -- P_PRINT_FLAG
NULL, -- P_NOTE_TO_VENDOR
'N' ); --P_USE_GLDATE

FND_FILE.PUT_LINE(FND_FILE.LOG,'API RETURN STATUS IS-' L_RETURN_STATUS);

IF L_RETURN_STATUS = 'U' THEN --( FND_API.G_RET_STS_UNEXP_ERROR)
FND_FILE.PUT_LINE(FND_FILE.LOG, 'UNEXPECTED ERROR IN CANCEL PO PROCESS API');
FND_MSG_PUB.ADD_EXC_MSG('QP_PRICE_LIST_PUB',' UNEXPECTED ERROR IN CANCEL PO PROCESS API');
ELSIF
L_RETURN_STATUS ='E' THEN --- ( FND_API.G_RET_STS_ERROR )
FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR IN CANCEL PO PROCESS API');
FND_MSG_PUB.ADD_EXC_MSG('QP_PRICE_LIST_PUB','ERROR IN CANCEL PO PROCESS API');
END IF;

IF L_RETURN_STATUS != 'S' THEN
V_FAILURE_COUNT := V_FAILURE_COUNT + 1;
FND_FILE.PUT_LINE(FND_FILE.LOG, 'TOTAL FAIL RECORD COUNT'V_FAILURE_COUNT);
FOR ERR_CODE IN 1..FND_MSG_PUB.COUNT_MSGLOOP
L_MSG_DATA := SUBSTR(FND_MSG_PUB.GET(P_MSG_INDEX => 1,P_ENCODED=>'F'),1,200);
FND_FILE.PUT_LINE(FND_FILE.LOG,'CANCEL PO PROCESS API FAILED FOR REASON-'L_MSG_DATA ) ;
END LOOP;
END IF;

IF L_RETURN_STATUS = 'S' THEN --- ( FND_API.G_RET_STS_SUCESS)
FND_FILE.PUT_LINE (FND_FILE.LOG, 'CANCEL PO PROCESS API PASSED' ) ;
V_SUCCESS_COUNT := V_SUCCESS_COUNT + 1;
FND_FILE.PUT_LINE(FND_FILE.LOG,'TOTAL SUCCESS RECORD COUNT-'V_SUCCESS_COUNT);

-- RENAME PO NUMBER AFTER CANCELLATION
UPDATE PO_HEADERS_ALL
SET SEGMENT1 = 'XX-'FETCH_PO_REC.SEGMENT1
WHERE SEGMENT1 = FETCH_PO_REC.SEGMENT1 ;

UPDATE PO_HEADERS_ARCHIVE_ALL
SET SEGMENT1 = 'XX-'FETCH_PO_REC.SEGMENT1
WHERE SEGMENT1 = FETCH_PO_REC.SEGMENT1 ;

END IF ;
END LOOP ;

EXCEPTION
WHEN OTHERS THEN
FND_FILE.PUT_LINE(FND_FILE.LOG, 'ERROR CODE IN THE EXECUTION OF CANCEL API IS :'TO_NUMBER( SQLCODE )' 'SUBSTR( SQLERRM , 1 , 50) ) ;
END XXPO_CANCEL_PO ;
/