Sales Order Import using API

The code below creates a sales order in entered status with one line record. Also the price adjustment is done for the line.

DECLARE
   l_api_version_number           NUMBER                                 := 1;
   l_return_status                VARCHAR2 (2000);
   l_msg_count                    NUMBER;
   l_msg_data                     VARCHAR2 (2000);
/*****************PARAMETERS****************************************************/
   l_debug_level                  NUMBER                                 := 1;
                                                    -- OM DEBUG LEVEL (MAX 5)
   l_org                          NUMBER                              := 5283;
                                                            -- OPERATING UNIT
   l_no_orders                    NUMBER                                 := 1;
                                                              -- NO OF ORDERS
   l_user                         NUMBER                             := 28573;
                                                                      -- USER
   l_resp                         NUMBER                             := 53073;
                                                            -- RESPONSIBLILTY
   l_appl                         NUMBER                               := 660;
                                                          -- ORDER MANAGEMENT
/*****************INPUT VARIABLES FOR PROCESS_ORDER API*************************/
   l_header_rec                   oe_order_pub.header_rec_type;
   l_line_tbl                     oe_order_pub.line_tbl_type;
   l_action_request_tbl           oe_order_pub.request_tbl_type;
   l_line_adj_tbl                 oe_order_pub.line_adj_tbl_type;
/*****************OUT VARIABLES FOR PROCESS_ORDER API***************************/
   l_header_rec_out               oe_order_pub.header_rec_type;
   l_header_val_rec_out           oe_order_pub.header_val_rec_type;
   l_header_adj_tbl_out           oe_order_pub.header_adj_tbl_type;
   l_header_adj_val_tbl_out       oe_order_pub.header_adj_val_tbl_type;
   l_header_price_att_tbl_out     oe_order_pub.header_price_att_tbl_type;
   l_header_adj_att_tbl_out       oe_order_pub.header_adj_att_tbl_type;
   l_header_adj_assoc_tbl_out     oe_order_pub.header_adj_assoc_tbl_type;
   l_header_scredit_tbl_out       oe_order_pub.header_scredit_tbl_type;
   l_header_scredit_val_tbl_out   oe_order_pub.header_scredit_val_tbl_type;
   l_line_tbl_out                 oe_order_pub.line_tbl_type;
   l_line_val_tbl_out             oe_order_pub.line_val_tbl_type;
   l_line_adj_tbl_out             oe_order_pub.line_adj_tbl_type;
   l_line_adj_val_tbl_out         oe_order_pub.line_adj_val_tbl_type;
   l_line_price_att_tbl_out       oe_order_pub.line_price_att_tbl_type;
   l_line_adj_att_tbl_out         oe_order_pub.line_adj_att_tbl_type;
   l_line_adj_assoc_tbl_out       oe_order_pub.line_adj_assoc_tbl_type;
   l_line_scredit_tbl_out         oe_order_pub.line_scredit_tbl_type;
   l_line_scredit_val_tbl_out     oe_order_pub.line_scredit_val_tbl_type;
   l_lot_serial_tbl_out           oe_order_pub.lot_serial_tbl_type;
   l_lot_serial_val_tbl_out       oe_order_pub.lot_serial_val_tbl_type;
   l_action_request_tbl_out       oe_order_pub.request_tbl_type;
   l_msg_index                    NUMBER;
   l_data                         VARCHAR2 (2000);
   l_loop_count                   NUMBER;
   l_debug_file                   VARCHAR2 (200);
   b_return_status                VARCHAR2 (200);
   b_msg_count                    NUMBER;
   b_msg_data                     VARCHAR2 (2000);
BEGIN
   DBMS_APPLICATION_INFO.set_client_info (l_org);
   mo_global.set_policy_context ('S', l_org);
   mo_global.init ('ONT');

/*****************INITIALIZE DEBUG INFO*************************************/
   IF (l_debug_level > 0)
   THEN
      l_debug_file := oe_debug_pub.set_debug_mode ('FILE');
      oe_debug_pub.initialize;
      oe_debug_pub.setdebuglevel (l_debug_level);
      oe_msg_pub.initialize;
   END IF;

/*****************INITIALIZE ENVIRONMENT*************************************/
   fnd_global.apps_initialize (l_user, l_resp, l_appl);
                     -- pass in user_id, responsibility_id, and application_id
/*****************INITIALIZE HEADER RECORD******************************/
   l_header_rec := oe_order_pub.g_miss_header_rec;
/*****************POPULATE REQUIRED ATTRIBUTES **********************************/
   l_header_rec.operation := oe_globals.g_opr_create;
   l_header_rec.order_type_id := 2159;                      -- domestic return
   l_header_rec.sold_to_org_id := 659018;
   l_header_rec.ship_to_org_id := 635775;
   l_header_rec.invoice_to_org_id := 635776;
   l_header_rec.order_source_id := 9;
   l_header_rec.booked_flag := 'N';
   l_header_rec.price_list_id := 39825;
   l_header_rec.pricing_date := SYSDATE;
   l_header_rec.transactional_curr_code := 'USD';
   l_header_rec.flow_status_code := 'ENTERED';
   l_header_rec.cust_po_number := '1211314AFA';
/*****************INITIALIZE ACTION REQUEST RECORD*************************************/
   l_action_request_tbl (1) := oe_order_pub.g_miss_request_rec;
/*****************INITIALIZE LINE RECORD********************************/
   l_line_tbl (1) := oe_order_pub.g_miss_line_rec;
   l_line_tbl (1).operation := oe_globals.g_opr_create;
   l_line_tbl (1).inventory_item_id := 826543;
   l_line_tbl (1).ordered_quantity := 1;
--l_line_tbl(1).unit_selling_price := 2000; -- The price is done using adjustments
--l_line_tbl(1).unit_list_price := 2000;
   l_line_tbl (1).calculate_price_flag := 'Y';
   l_line_tbl (1).return_reason_code := 'B2';
--l_line_tbl(1).line_number := 1;
   l_line_adj_tbl (1) := oe_order_pub.g_miss_line_adj_rec;
   l_line_adj_tbl (1).operation := oe_globals.g_opr_create;
   l_line_adj_tbl (1).list_header_id := 148129;
   l_line_adj_tbl (1).list_line_id := 651550;
   l_line_adj_tbl (1).change_reason_code := 'MANUAL';
   l_line_adj_tbl (1).change_reason_text := 'Manually applied adjustments';
   l_line_adj_tbl (1).operand := 2000;
   l_line_adj_tbl (1).pricing_phase_id := 2;
   l_line_adj_tbl (1).updated_flag := 'Y';
   l_line_adj_tbl (1).applied_flag := 'Y';
   l_line_adj_tbl (1).line_index := 1;

   FOR i IN 1 .. l_no_orders
   LOOP
/*****************CALLTO PROCESS ORDER API*********************************/
      oe_order_pub.process_order
                   (p_api_version_number          => l_api_version_number,
                    p_header_rec                  => l_header_rec,
                    p_line_tbl                    => l_line_tbl,
                    p_action_request_tbl          => l_action_request_tbl,
                    p_line_adj_tbl                => l_line_adj_tbl
-- OUT variables
      ,
                    x_header_rec                  => l_header_rec_out,
                    x_header_val_rec              => l_header_val_rec_out,
                    x_header_adj_tbl              => l_header_adj_tbl_out,
                    x_header_adj_val_tbl          => l_header_adj_val_tbl_out,
                    x_header_price_att_tbl        => l_header_price_att_tbl_out,
                    x_header_adj_att_tbl          => l_header_adj_att_tbl_out,
                    x_header_adj_assoc_tbl        => l_header_adj_assoc_tbl_out,
                    x_header_scredit_tbl          => l_header_scredit_tbl_out,
                    x_header_scredit_val_tbl      => l_header_scredit_val_tbl_out,
                    x_line_tbl                    => l_line_tbl_out,
                    x_line_val_tbl                => l_line_val_tbl_out,
                    x_line_adj_tbl                => l_line_adj_tbl_out,
                    x_line_adj_val_tbl            => l_line_adj_val_tbl_out,
                    x_line_price_att_tbl          => l_line_price_att_tbl_out,
                    x_line_adj_att_tbl            => l_line_adj_att_tbl_out,
                    x_line_adj_assoc_tbl          => l_line_adj_assoc_tbl_out,
                    x_line_scredit_tbl            => l_line_scredit_tbl_out,
                    x_line_scredit_val_tbl        => l_line_scredit_val_tbl_out,
                    x_lot_serial_tbl              => l_lot_serial_tbl_out,
                    x_lot_serial_val_tbl          => l_lot_serial_val_tbl_out,
                    x_action_request_tbl          => l_action_request_tbl_out,
                    x_return_status               => l_return_status,
                    x_msg_count                   => l_msg_count,
                    x_msg_data                    => l_msg_data
                   );

/*****************CHECK RETURN STATUS***********************************/
      IF l_return_status = fnd_api.g_ret_sts_success
      THEN
         IF (l_debug_level > 0)
         THEN
            DBMS_OUTPUT.put_line ('success');
         END IF;

         COMMIT;
      ELSE
         IF (l_debug_level > 0)
         THEN
            DBMS_OUTPUT.put_line ('failure');
         END IF;

         ROLLBACK;
      END IF;
   END LOOP;                                                       -- END LOOP

/*****************DISPLAY RETURN STATUS FLAGS******************************/
   IF (l_debug_level > 0)
   THEN
      DBMS_OUTPUT.put_line ('process ORDER ret status IS: ' || l_return_status
                           );
      DBMS_OUTPUT.put_line ('process ORDER msg data IS: ' || l_msg_data);
      DBMS_OUTPUT.put_line ('process ORDER msg COUNT IS: ' || l_msg_count);
      DBMS_OUTPUT.put_line (   'header.order_number IS: '
                            || TO_CHAR (l_header_rec_out.order_number)
                           );
      DBMS_OUTPUT.put_line (   'adjustment.return_status IS: '
                            || l_line_adj_tbl_out (1).return_status
                           );
      DBMS_OUTPUT.put_line (   'header.header_id IS: '
                            || l_header_rec_out.header_id
                           );
      DBMS_OUTPUT.put_line (   'line.unit_selling_price IS: '
                            || l_line_tbl_out (1).unit_selling_price
                           );
   END IF;

/*****************DISPLAY ERROR MSGS*************************************/
   IF (l_debug_level > 0)
   THEN
      FOR i IN 1 .. l_msg_count
      LOOP
         oe_msg_pub.get (p_msg_index          => i,
                         p_encoded            => fnd_api.g_false,
                         p_data               => l_data,
                         p_msg_index_out      => l_msg_index
                        );
         DBMS_OUTPUT.put_line ('message is: ' || l_data);
         DBMS_OUTPUT.put_line ('message index is: ' || l_msg_index);
      END LOOP;
   END IF;

   IF (l_debug_level > 0)
   THEN
      DBMS_OUTPUT.put_line ('Debug = ' || oe_debug_pub.g_debug);
      DBMS_OUTPUT.put_line (   'Debug Level = '
                            || TO_CHAR (oe_debug_pub.g_debug_level)
                           );
      DBMS_OUTPUT.put_line (   'Debug File = '
                            || oe_debug_pub.g_dir
                            || '/'
                            || oe_debug_pub.g_file
                           );
      DBMS_OUTPUT.put_line
                       ('****************************************************');
      oe_debug_pub.debug_off;
   END IF;
END;

/








No comments:

Post a Comment

How to find all cancel Requisitions

SELECT prha . *   FROM po_Requisition_headers_all prha , po_action_history pah   WHERE      1 = 1        AND pah . object_id ...