Get Item On Hand Quantities of the item through API in Oracle Inventory

This script can be used to get the below quantities.

1. On-hand Quantity

2. Available to Reserve

3. Quantity Reserved

4. Quantity Suggested

5. Available to Transact

6. Available to Reserve

You can also get the On-hand quantities from the table mtl_onhand_quantities

GET ON-HAND QUANTITIES API

DECLARE

   x_return_status         VARCHAR2 (50);

   x_msg_count             VARCHAR2 (50);

   x_msg_data              VARCHAR2 (50);

   v_item_id               NUMBER;

   v_org_id                NUMBER;

   v_qoh                   NUMBER;

   v_rqoh                  NUMBER;

   v_atr                   NUMBER;

   v_att                   NUMBER;

   v_qr                    NUMBER;

   v_qs                    NUMBER;

   v_lot_control_code      BOOLEAN;

   v_serial_control_code   BOOLEAN;

BEGIN

   -- Set the variable values

   v_item_id := '6566';

   v_org_id := 61;

   v_qoh := NULL;

   v_rqoh := NULL;

   v_atr := NULL;

   v_lot_control_code := FALSE;

   v_serial_control_code := FALSE;

   -- Set the org context

   fnd_client_info.set_org_context (1);

   -- Call API

   inv_quantity_tree_pub.query_quantities

   (p_api_version_number       => 1.0,

    p_init_msg_lst             => 'F',

    x_return_status            => x_return_status,

    x_msg_count                => x_msg_count,

    x_msg_data                 => x_msg_data,

    p_organization_id          => v_org_id,

    p_inventory_item_id        => v_item_id,

    p_tree_mode                => apps.inv_quantity_tree_pub.g_transaction_mode,

    -- or 3

    p_is_revision_control      => FALSE,

    p_is_lot_control           => v_lot_control_code,

    -- is_lot_control,

    p_is_serial_control        => v_serial_control_code,

    p_revision                 => NULL,        -- p_revision,

    p_lot_number               => NULL,           -- p_lot_number,

    p_lot_expiration_date      => SYSDATE,

    p_subinventory_code        => NULL,    -- p_subinventory_code,

    p_locator_id               => NULL,           -- p_locator_id,

    -- p_cost_group_id            => NULL,       -- cg_id,

    p_onhand_source            => 3,

    x_qoh                      => v_qoh,      -- Quantity on-hand

    x_rqoh                     => v_rqoh,           --reservable quantity on-hand

    x_qr                       => v_qr,

    x_qs                       => v_qs,

    x_att                      => v_att,  -- available to transact

    x_atr                      => v_atr    -- available to reserve

   );

   DBMS_OUTPUT.put_line ('On-Hand Quantity: ' || v_qoh);

   DBMS_OUTPUT.put_line ('Available to reserve: ' || v_atr);

   DBMS_OUTPUT.put_line ('Quantity Reserved: ' || v_qr);

   DBMS_OUTPUT.put_line ('Quantity Suggested: ' || v_qs);

   DBMS_OUTPUT.put_line ('Available to Transact: ' || v_att);

   DBMS_OUTPUT.put_line ('Available to Reserve: ' || v_atr);

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.put_line ('ERROR: ' || SQLERRM);

END;

GET ON-HAND QUANTITIES FROM TABLE

SELECT * FROM MTL_ONHAND_QUANTITIES;

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 ;
/