Tuesday, November 21, 2017

API to Book the Sales Order in Oracle Apps

DECLARE
   v_api_version_number           NUMBER := 1;
   v_return_status                VARCHAR2 (2000);
   v_msg_count                    NUMBER;
   v_msg_data                     VARCHAR2 (2000);

   -- IN Variables --
   v_header_rec                   oe_order_pub.header_rec_type;
   v_line_tbl                     oe_order_pub.line_tbl_type;
   v_action_request_tbl           oe_order_pub.request_tbl_type;
   v_line_adj_tbl                 oe_order_pub.line_adj_tbl_type;

   -- OUT Variables --
   v_header_rec_out               oe_order_pub.header_rec_type;
   v_header_val_rec_out           oe_order_pub.header_val_rec_type;
   v_header_adj_tbl_out           oe_order_pub.header_adj_tbl_type;
   v_header_adj_val_tbl_out       oe_order_pub.header_adj_val_tbl_type;
   v_header_price_att_tbl_out     oe_order_pub.header_price_att_tbl_type;
   v_header_adj_att_tbl_out       oe_order_pub.header_adj_att_tbl_type;
   v_header_adj_assoc_tbl_out     oe_order_pub.header_adj_assoc_tbl_type;
   v_header_scredit_tbl_out       oe_order_pub.header_scredit_tbl_type;
   v_header_scredit_val_tbl_out   oe_order_pub.header_scredit_val_tbl_type;
   v_line_tbl_out                 oe_order_pub.line_tbl_type;
   v_line_val_tbl_out             oe_order_pub.line_val_tbl_type;
   v_line_adj_tbl_out             oe_order_pub.line_adj_tbl_type;
   v_line_adj_val_tbl_out         oe_order_pub.line_adj_val_tbl_type;
   v_line_price_att_tbl_out       oe_order_pub.line_price_att_tbl_type;
   v_line_adj_att_tbl_out         oe_order_pub.line_adj_att_tbl_type;
   v_line_adj_assoc_tbl_out       oe_order_pub.line_adj_assoc_tbl_type;
   v_line_scredit_tbl_out         oe_order_pub.line_scredit_tbl_type;
   v_line_scredit_val_tbl_out     oe_order_pub.line_scredit_val_tbl_type;
   v_lot_serial_tbl_out           oe_order_pub.lot_serial_tbl_type;
   v_lot_serial_val_tbl_out       oe_order_pub.lot_serial_val_tbl_type;
   v_action_request_tbl_out       oe_order_pub.request_tbl_type;
  
   l_user_id        NUMBER;
   l_resp_id        NUMBER;
   l_resp_appl_id   NUMBER;
   l_line_tbl_index NUMBER := 0;
   
  CURSOR book_order
  IS
   SELECT header_id, order_number
     FROM oe_order_headers_all
    WHERE order_number IN ('1001001','1001002');


BEGIN
  
   BEGIN
    SELECT user_id
      INTO l_user_id
      FROM fnd_user
     WHERE user_name = 'XXUSER';    
   END;
  
   BEGIN
    SELECT responsibility_id, application_id
      INTO l_resp_id, l_resp_appl_id
      FROM fnd_responsibility_tl
     WHERE responsibility_name = 'Order Management Super User'  ; 
   END;
  
   DBMS_OUTPUT.PUT_LINE ('Starting of script');

   -- Setting the Enviroment --
  
   mo_global.init ('ONT');
  
   fnd_global.apps_initialize (l_user_id
                              ,l_resp_id
                              ,l_resp_appl_id
                              ) ; -- pass in user_id, responsibility_id, and application_id

   mo_global.set_policy_context ('S', 121);
 
    FOR i IN book_order
     LOOP
           l_line_tbl_index := l_line_tbl_index +1;
           v_action_request_tbl(l_line_tbl_index) := oe_order_pub.g_miss_request_rec;
           v_action_request_tbl(l_line_tbl_index).request_type := OE_GLOBALS.G_BOOK_ORDER;
           v_action_request_tbl(l_line_tbl_index).entity_code  := OE_GLOBALS.G_ENTITY_HEADER;
           v_action_request_tbl(l_line_tbl_index).entity_id    := i.header_id;

           DBMS_OUTPUT.PUT_LINE ('Starting of API');

           -- Calling the API to to Book an Existing Order --

           OE_ORDER_PUB.PROCESS_ORDER (
              p_api_version_number       => v_api_version_number,
              p_header_rec               => v_header_rec,
              p_line_tbl                 => v_line_tbl,
              p_action_request_tbl       => v_action_request_tbl,
              p_line_adj_tbl             => v_line_adj_tbl-- OUT variables
              ,
              x_header_rec               => v_header_rec_out,
              x_header_val_rec           => v_header_val_rec_out,
              x_header_adj_tbl           => v_header_adj_tbl_out,
              x_header_adj_val_tbl       => v_header_adj_val_tbl_out,
              x_header_price_att_tbl     => v_header_price_att_tbl_out,
              x_header_adj_att_tbl       => v_header_adj_att_tbl_out,
              x_header_adj_assoc_tbl     => v_header_adj_assoc_tbl_out,
              x_header_scredit_tbl       => v_header_scredit_tbl_out,
              x_header_scredit_val_tbl   => v_header_scredit_val_tbl_out,
              x_line_tbl                 => v_line_tbl_out,
              x_line_val_tbl             => v_line_val_tbl_out,
              x_line_adj_tbl             => v_line_adj_tbl_out,
              x_line_adj_val_tbl         => v_line_adj_val_tbl_out,
              x_line_price_att_tbl       => v_line_price_att_tbl_out,
              x_line_adj_att_tbl         => v_line_adj_att_tbl_out,
              x_line_adj_assoc_tbl       => v_line_adj_assoc_tbl_out,
              x_line_scredit_tbl         => v_line_scredit_tbl_out,
              x_line_scredit_val_tbl     => v_line_scredit_val_tbl_out,
              x_lot_serial_tbl           => v_lot_serial_tbl_out,
              x_lot_serial_val_tbl       => v_lot_serial_val_tbl_out,
              x_action_request_tbl       => v_action_request_tbl_out,
              x_return_status            => v_return_status,
              x_msg_count                => v_msg_count,
              x_msg_data                 => v_msg_data);

           DBMS_OUTPUT.PUT_LINE ('Completion of API');


           IF v_return_status = fnd_api.g_ret_sts_success
           THEN
              COMMIT;
              DBMS_OUTPUT.put_line ('Order '||i.order_number||' is Success..');
           ELSE
              DBMS_OUTPUT.put_line ('Order '||i.order_number||' is Failed..' || v_msg_data);
              ROLLBACK;

              FOR i IN 1 .. v_msg_count
              LOOP
                 v_msg_data := oe_msg_pub.get (p_msg_index => i
                                              ,p_encoded => 'F'
                                              );
                 DBMS_OUTPUT.put_line (i || ') ' || v_msg_data);
              END LOOP;
           END IF;
     END LOOP;
    
END;
/

Commit

No comments:

Post a Comment

SupplierAddressImportTemplate.xlsm South Africa Suburb Field mapping in POZ_SUPPLIER_ADDRESSES_INT

Suburb mpping in Supplier Address Import Template will be mapped to Address Element Attribute2 (HZ_LOCATIONS. ADDR_ELEMENT_ATTRIBUTE2)