Purchase order api in oracle apps r12

Purchase order api in oracle apps r12

 

In this post, we will discuss about api to create the Purchase order in oracle apps. As such oracle has not provided any api to create the purchase order through API but we have the oracle po interface through which we can create the Purchase orders from the external system. As below I will share the sample code of using Purchase order interface which can be use same as purchase order api in oracle apps r12 to create the purchase orders from the externa system.

 

As such Oracle has not provided purchase order api in oracle apps r12 to create the purchase orders but Oracle has provided the API to update  or make any new changes in the Purchase order

 

Create purchase order api in oracle apps r12

 

Oracle has provided these below three interface tables in the PO conversion.

 

1.po_headers_interface

2.po_lines_interface

3.po_distributions_interface

INSERT INTO po.po_headers_interface
 (interface_header_id,
 comments,
 process_code,
 action,
 org_id,
 document_type_code,
 currency_code,
 agent_id,
 vendor_id,
 vendor_name,
 vendor_site_code,
 ship_to_location_id,
 bill_to_location_id,
 effective_date,
 reference_num,
 last_update_date
 )
SELECT apps.po_headers_interface_s.NEXTVAL,
 INFORMATION,
 'PENDING',
 'ORIGINAL',
 85,
 'STANDARD',
 'INR',
 81,
 SUPPLIER_ID,
 SUPPLIER,
 VENDOR_SITE,
 SHIP_ID,
 BILL_ID
 TRUNC(SYSDATE),
 'PO'||apps.po_headers_interface_s.NEXTVAL,
 SYSDATE
 FROM
 XX_PO_HDR_STAGING;

/****** INSERTING DATA INTO PO_LINES_INTERFACE ******/

TRUNCATE TABLE po.po_lines_interface
INSERT INTO po.po_lines_interface
 (interface_header_id,
 interface_line_id,
 line_num,
 shipment_num,
 line_type,
 item,
 item_description,
 uom_code,
 quantity,
 unit_price,
 organization_id,
 need_by_date,
 ship_to_organization_id,
 ship_to_location
 )
 SELECT
 apps.po_headers_interface_s.CURRVAL,
 APPS.PO_LINES_INTERFACE_S.NEXTVAL,
 LINE_NUMBER,
SHIP_NUMBER,
 'Goods',
 ICODE,
 ITEM,
 UOM,
 QUANTITY,
 PRICE,
 SHIP_TO_ORGID,
 TRUNC(SYSDATE),
 M.SHIP_ORGID,
 M.SHIP_TO_LOC
 FROM
 XX_PO_LINE_STAGING;

/******* INSERTING DATA INTO PO_DISTRIBUTIONS_INTERFACE ******/


 INSERT INTO po.po_distributions_interface
 (interface_header_id,
 interface_line_id,
 interface_distribution_id,
 distribution_num,
 quantity_ordered
 )
 -- CHARGE_ACCOUNT_ID)
 SELECT apps.po_headers_interface_s.CURRVAL
 APPS.PO_LINES_INTERFACE_S.CURRVAL,
 po.po_distributions_interface_s.NEXTVAL,
 SHIPMENT_NUM,
 QUANTITY
 FROM
 XX_PO_SHIP_STAGING;
To check the rejected records in the PO conversion we can see in this below table.

 po.po_interface_errors

 

 

Update purchase order api in oracle apps r12

 

DECLARE

v_result        NUMBER;

v_api_errors    po_api_errors_rec_type;

 

v_revision_num  po_headers_all.revision_num%TYPE;

v_price         po_lines_all.unit_price%TYPE;

v_quantity      po_line_locations_all.quantity%TYPE;

v_po_number     po_headers_all.segment1%TYPE;

v_line_num      po_lines_all.line_num%TYPE;

v_shipment_num  po_line_locations_all.shipment_num%TYPE;

v_promised_date DATE;

v_need_by_date  DATE;

v_org_id        NUMBER;

 

v_context       VARCHAR2(10);

 

BEGIN

 

v_context := set_context ('&user', '&responsibility', 2038);

 

IF v_context = 'F'

   THEN

   DBMS_OUTPUT.PUT_LINE ('Error in the context');

END IF;

 

MO_GLOBAL.INIT ('PO');

 

v_po_number     :=’10000678’;

v_line_num      := 1;

v_shipment_num  := 1;

v_revision_num  := 0;

v_promised_date := '01-APR-2016';

v_need_by_date  := '11-MAY-2016';

v_quantity      := 456;

v_price         := 12;

v_org_id        := 85;

 

DBMS_OUTPUT.put_line ('Calling API To Update PO');

 

v_result :=

 

    PO_CHANGE_API1_S.UPDATE_PO

         (x_po_number          => v_po_number,

          x_release_number     => NULL,

          x_revision_number    => v_revision_num,

          x_line_number        => v_line_num,

          x_shipment_number    => v_shipment_num,

          new_quantity         => v_quantity,

          new_price            => v_price,

          new_promised_date    => v_promised_date,

          new_need_by_date     => v_need_by_date,

          launch_approvals_flag=> 'Y',

          update_source        => NULL,

          VERSION              => '1.0',

          x_override_date      => NULL,

          x_api_errors         => v_api_errors,

          p_buyer_name         => NULL,

          p_secondary_quantity => NULL,

          p_preferred_grade    => NULL,

          p_org_id             => v_org_id

         );

 

DBMS_OUTPUT.put_line ('RESULT :' ||v_result);

 

IF (v_result = 1)

THEN

 DBMS_OUTPUT.put_line('Updating PO is Successful ');

ELSE

 DBMS_OUTPUT.put_line ('Updating PO failed');

 

 FOR j IN 1 .. v_api_errors.MESSAGE_TEXT.COUNT

 LOOP

 DBMS_OUTPUT.put_line (v_api_errors.MESSAGE_TEXT (j));

 END LOOP;

END IF;

 

END;

Update purchase requisition api in oracle apps r12

 

DECLARE

  l_req_hdr      PO_REQUISITION_UPDATE_PUB.req_hdr;

  l_req_line_tbl PO_REQUISITION_UPDATE_PUB.req_line_tbl;

  l_req_dist_dtl PO_REQUISITION_UPDATE_PUB.req_dist_tbl;

  l_return_status  VARCHAR2(1);

  l_msg_count      NUMBER;

  l_msg_data       VARCHAR2(2000);

 BEGIN

   l_req_hdr.requisition_header_id             := 35554;

  l_req_hdr.org_id                            := 85;

  l_req_line_tbl(1).requisition_header_id     := 35554;

  l_req_line_tbl(1).requisition_line_id       := 6778995;

  l_req_line_tbl(1).suggested_vendor_name     := ‘TPT';

  l_req_line_tbl(1).suggested_vendor_location :=’DELHI’;

 

  PO_REQUISITION_UPDATE_PUB.update_requisition

  (p_init_msg_list   => 'T',

   p_commit          => 'F',

   x_return_status   => l_return_status,

   x_msg_count       => l_msg_count,

   x_msg_data        => l_msg_data,

   p_submit_approval => 'N',

   p_req_hdr         => l_req_hdr,

   p_req_line_tbl    => l_req_line_tbl,

   p_req_dist_tbl    => l_req_dist_dtl

   );

  DBMS_OUTPUT.PUT_LINE('Return Status :' || l_return_status);

  DBMS_OUTPUT.PUT_LINE('Msg Count :' || l_msg_count);

  DBMS_OUTPUT.PUT_LINE('Msg Data :' || l_msg_data);

 

END;