1 Reply Latest reply on Jun 6, 2013 5:01 PM by GVRanjith

    Creating PO Requisition using PO_CREATE_REQUISITION_SV.PROCESS_REQUISITION

    Ram-EBS
      Hi there,

      I have written the following simple code to create a PO requisition using API.... but when I execute, it fails saying "ORA-01400: cannot insert NULL". Could anyone please help me by identifying which data I am missing

      Here's the code:

      CREATE OR REPLACE PROCEDURE xxxx_req_creation
      IS
      l_int_req_ret_sts VARCHAR2 (1);
      l_req_header_rec po_create_requisition_sv.header_rec_type;
      l_req_line_tbl po_create_requisition_sv.line_tbl_type;
      l_msg_count NUMBER;
      l_msg_data VARCHAR2 (2000);
      j NUMBER := 1;
      BEGIN
      fnd_global.apps_initialize (user_id => 6685,
      resp_id => 50965,
      resp_appl_id => 201);

      DBMS_OUTPUT.put_line ('Passing Values for header');

      l_req_header_rec.requisition_header_id := po_requisition_headers_s.NEXTVAL;
      l_req_header_rec.preparer_id := 6685;
      l_req_header_rec.last_update_date := SYSDATE;
      l_req_header_rec.last_updated_by := 6685;
      l_req_header_rec.summary_flag := 'N';
      l_req_header_rec.enabled_flag := 'Y';
      l_req_header_rec.last_update_login := -1;
      l_req_header_rec.creation_date := SYSDATE;
      l_req_header_rec.created_by := 6685;
      l_req_header_rec.authorization_status := 'APPROVED';
      l_req_header_rec.type_lookup_code := 'PURCHASE';
      l_req_header_rec.org_id := 342;

      DBMS_OUTPUT.put_line ('Passing Values for Line');

      l_req_line_tbl (j).requisition_line_id := po_requisition_headers_s.NEXTVAL;
      l_req_line_tbl (j).requisition_header_id :=
      po_requisition_headers_s.CURRVAL;
      l_req_line_tbl (j).line_num := 1;
      l_req_line_tbl (j).line_type_id := 1;
      l_req_line_tbl (j).category_id := 2163;
      l_req_line_tbl (j).item_description := 'WATER BBL';
      l_req_line_tbl (j).unit_meas_lookup_code := 'BARREL'; --ORDER_QUANTITY_UOM
      l_req_line_tbl (j).unit_price := 10;
      l_req_line_tbl (j).quantity := 2; --ORDERED_QUANTITY
      l_req_line_tbl (j).deliver_to_location_id := 900; --DELIVER_TO_LOCN_ID
      l_req_line_tbl (j).destination_type_code := 'EXPENSE';
      l_req_line_tbl (j).destination_organization_id := 406; --SHIP_FROM_ORG_ID
      l_req_line_tbl (j).to_person_id := 6685;
      l_req_line_tbl (j).source_type_code := 'VENDOR';
      l_req_line_tbl (j).item_id := 3029; --INVENTORY_ITEM_ID
      l_req_line_tbl (j).need_by_date := SYSDATE + 1;
      l_req_line_tbl (j).last_update_date := SYSDATE;
      l_req_line_tbl (j).last_updated_by := 6685;
      l_req_line_tbl (j).last_update_login := -1;
      l_req_line_tbl (j).creation_date := SYSDATE;
      l_req_line_tbl (j).created_by := 6685;
      l_req_line_tbl (j).org_id := 342;
      l_req_line_tbl (j).order_type_lookup_code := 'QUANTITY';
      l_req_line_tbl (j).purchase_basis := 'GOODS';
      l_req_line_tbl (j).matching_basis := 'QUANTITY';

      DBMS_OUTPUT.put_line ('Calling the API');

      po_create_requisition_sv.process_requisition (
      px_header_rec => l_req_header_rec,
      px_line_table => l_req_line_tbl,
      x_return_status => l_int_req_ret_sts,
      x_msg_count => l_msg_count,
      x_msg_data => l_msg_data);

      DBMS_OUTPUT.put_line ('End of the API');


      IF l_int_req_ret_sts = fnd_api.g_ret_sts_success
      THEN
      DBMS_OUTPUT.put_line ('Success');
      END IF;

      DBMS_OUTPUT.put_line (l_msg_data);
      EXCEPTION
      WHEN OTHERS
      THEN
      raise_application_error (-20001, SUBSTR (SQLERRM, 1, 100));
      END xxxx_req_creation;


      --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

      Cheers!

      RayR

      Edited by: 1005300 on May 10, 2013 1:18 PM