Trip Creation API

Procedure to create Trip in Oracle WMS.

CREATE OR REPLACE PROCEDURE xx_om_autocreate_trip (
   p_master_trip   IN       VARCHAR2,
   p_trip_no       OUT      VARCHAR2,
   p_trip_name     OUT      VARCHAR2,
   p_err_msg       OUT      VARCHAR2
)
IS
   lv_return_status   VARCHAR2 (1);
   ln_msg_count       NUMBER;
   lv_msg_data        VARCHAR2 (400);
   ln_trip_id         VARCHAR2 (20);
   lv_trip_name       VARCHAR2 (30);
   l_line_tbl         wsh_util_core.id_tab_type;
   l_del_rows_tbl     wsh_util_core.id_tab_type;
   ln_line_no         NUMBER                    := 0;
   l_msg_index_out    NUMBER;
   CURSOR cur_trip
   IS   
      SELECT DISTINCT wdv.delivery_detail_id
                 FROM oe_order_headers_v oeh,
                      oe_order_lines_v ool,
                      xx_om_mtrip_mastertrip_v xxo,
                      wsh_deliverables_v wdv,
                      xx_om_mtrip_lines_v xxl
                WHERE oeh.header_id = ool.header_id
                  AND oeh.ship_from = xxo.warehouse_code
                  AND oeh.shipping_method_code = xxo.shipping_method_code
                  AND TRIM (ool.schedule_ship_date)
                         BETWEEN NVL (xxo.ss_date_from,
                                      TRIM (ool.schedule_ship_date)
                                     )
                             AND NVL (xxo.ss_date_to,
                                      TRIM (ool.schedule_ship_date)
                                     )
                  AND UPPER (ool.flow_status_code) = 'AWAITING_SHIPPING'
                  AND wdv.source_header_id = ool.header_id
                  AND wdv.source_line_id = ool.line_id
                  AND xxo.master_trip_no = p_master_trip
                  AND TO_CHAR (xxl.sales_order) = TO_CHAR (oeh.order_number)
                  AND NOT EXISTS (
                         SELECT sales_order
                           FROM xx_om_mtrip_lines_v xxo
                          WHERE xxo.sales_order = oeh.order_number
                            AND xxo.add_to_trip = 'Y')
                  AND NOT EXISTS (
                         SELECT 1
                           FROM oe_holds_history_v ohh
                          WHERE ohh.header_id = oeh.header_id
                            AND UPPER (hold_name) <> 'HOLD ORDER - WAREHOUSE'
                            AND released_flag = 'N')
             ORDER BY 1;
BEGIN
   FOR i IN cur_trip
   LOOP
      ln_line_no := NVL (ln_line_no, 0) + 1;
      l_line_tbl (ln_line_no) := i.delivery_detail_id;
      DBMS_OUTPUT.put_line (l_line_tbl (ln_line_no));
   END LOOP;
   wsh_delivery_details_pub.autocreate_del_trip
      (p_api_version_number      => 1.0                       ---IN     NUMBER
                                       ,
       p_init_msg_list           => fnd_api.g_false
                                     ---IN   VARCHAR2  DEFAULT Fnd_Api.G_FALSE
                                                   ,
       p_commit                  => fnd_api.g_false
                                      --IN   VARCHAR2  DEFAULT Fnd_Api.G_FALSE
                                                   ,
       x_return_status           => lv_return_status,
       x_msg_count               => ln_msg_count,
       x_msg_data                => lv_msg_data,
       p_line_rows               => l_line_tbl,
       x_del_rows                => l_del_rows_tbl,
       x_trip_id                 => ln_trip_id,
       x_trip_name               => lv_trip_name
      );
   COMMIT;
   IF NVL (ln_msg_count, 0) > 0
   THEN
      FOR j IN 1 .. ln_msg_count
      LOOP
         fnd_msg_pub.get (p_msg_index          => j,
                          p_encoded            => 'F',
                          p_data               => lv_msg_data,
                          p_msg_index_out      => l_msg_index_out
                         );
      END LOOP;
   END IF;
   IF NVL (lv_return_status, 'X') = 'S'
   THEN
      p_trip_name := lv_trip_name;
      p_trip_no := ln_trip_id;
      UPDATE xx_om_confirm_trip_v
         SET trip_created_flag = 'Y',
             oracle_trip_id = ln_trip_id,
             oracle_trip_name = lv_trip_name
       WHERE master_trip_no = p_master_trip;
   ELSIF NVL (lv_return_status, 'X') = 'F'
   THEN
      p_err_msg := lv_msg_data;
   END IF;
   COMMIT;
END;
/