Requisition Cancel API Script : Oracle EBS R12

Oracle has provided a standard API to cancel Requisition and Requisition Lines.

Below mentioned script is the custom pl/sql procedure which we can use to cancel Requisition and Requisition Lines.

PROCEDURE REQUISITION_CANCEL(
    P_REQ_NUM IN VARCHAR2,
    P_STATUS_MSG OUT VARCHAR2)
AS
  v_return_status VARCHAR2 (1000);
  v_msg_count     NUMBER;
  v_msg_data      VARCHAR2 (1000);
  v_header_id     NUMBER;
  v_line_id       NUMBER;
  v_req_line_id req_line_type;
  v_msg       NUMBER := NULL;
  v_msg_dummy VARCHAR2 (2000);
  v_output    VARCHAR2 (2000);
BEGIN
------Fetching Requisition Header id for corresponding Requisition Number-----
  BEGIN
    SELECT requisition_header_id
    into v_header_id
    FROM APPS.PO_REQUISITION_HEADERS_ALL
    WHERE segment1=p_req_num;
  EXCEPTION
  WHEN NO_DATA_FOUND THEN
    P_STATUS_MSG:='ERROR(ND):Requisition not found for Requisition Number :'||P_REQ_NUM;
  WHEN OTHERS THEN
    P_STATUS_MSG:='ERROR(OTHERS) :'||SQLERRM;
  END;

  ------Bulk Collect all the Requisition Lines-------------

 SELECT requisition_line_id bulk collect
  INTO v_req_line_id
  FROM APPS.PO_REQUISITION_LINES_ALL
  WHERE requisition_header_id=v_header_id;

  --------------------------------------------------------
  FOR i IN v_req_line_id.first ..v_req_line_id.last
  LOOP
    BEGIN
      v_msg:=1;
      APPS.PO_REQ_DOCUMENT_CANCEL_GRP.CANCEL_REQUISITION (P_API_VERSION => 1.0,
                                                          P_REQ_HEADER_ID => APPS.PO_TBL_NUMBER(V_HEADER_ID),
                                                          p_req_line_id => apps.po_tbl_number(v_req_line_id(i)),
                                                          p_cancel_date => SYSDATE,
                                                          p_cancel_reason => 'Cancelled Requisition',
                                                          P_SOURCE => 'REQUISITION',
                                                          x_return_status => v_return_status,
                                                          X_MSG_COUNT => V_MSG_COUNT,
                                                          x_msg_data => v_msg_data );

      IF v_return_status <> 'S' THEN
        apps.fnd_msg_pub.get (v_msg, apps.fnd_api.g_false, v_msg_data, v_msg_dummy);
        v_output := (TO_CHAR (v_msg) || ': ' || v_msg_data);
        DBMS_OUTPUT.put_line (v_output);
        P_STATUS_MSG:=v_output;
      ELSE
        P_STATUS_MSG:=v_return_status;
      END IF;

    END;
  END LOOP;
END;

Anonymous Block to test the procedure:-

Let us know your valuable feedback.