使用API進行發運確認並運行Interface Trip Stop，apiinterface

使用API進行發運確認並運行Interface Trip Stop，apiinterface

DECLARE  p_sales_order            NUMBER := 1506764295;  p_line_number            NUMBER := 1.1;  p_org_id                 NUMBER := 121;  l_shipped_quantity       NUMBER := 1;  p_api_version_number     NUMBER := 1.0;  init_msg_list            VARCHAR2(200);  l_commit                 VARCHAR2(30);  x_msg_details            VARCHAR2(3000);  x_msg_summary            VARCHAR2(3000);  x_return_status          VARCHAR2(3);  x_msg_count              NUMBER;  x_msg_data               VARCHAR2(3000);  p_validation_level       NUMBER;  v_errbuf                 VARCHAR2(2000);  v_retcode                VARCHAR2(20);  v_released_status        wsh_delivery_details.released_status%TYPE;  v_inv_interfaced_flag    wsh_delivery_details.inv_interfaced_flag%TYPE;  v_oe_interfaced_flag     wsh_delivery_details.oe_interfaced_flag%TYPE;  v_source_code            wsh_delivery_details.source_code%TYPE;  v_pending_interface_flag wsh_trip_stops.pending_interface_flag%TYPE;  l_changed_attributes     wsh_delivery_details_pub.changedattributetabtype;  l_source_code            VARCHAR2(30) := 'OE';  -- Parameters for WSH_DELIVERIES_PUB  p_delivery_name           VARCHAR2(30);  p_action_code             VARCHAR2(15);  p_asg_trip_id             NUMBER;  p_asg_trip_name           VARCHAR2(30);  p_asg_pickup_stop_id      NUMBER;  p_asg_pickup_loc_id       NUMBER;  p_asg_pickup_loc_code     VARCHAR2(30);  p_asg_pickup_arr_date     DATE;  p_asg_pickup_dep_date     DATE;  p_asg_dropoff_stop_id     NUMBER;  p_asg_dropoff_loc_id      NUMBER;  p_asg_dropoff_loc_code    VARCHAR2(30);  p_asg_dropoff_arr_date    DATE;  p_asg_dropoff_dep_date    DATE;  p_sc_action_flag          VARCHAR2(10);  p_sc_intransit_flag       VARCHAR2(10);  p_sc_close_trip_flag      VARCHAR2(10);  p_sc_create_bol_flag      VARCHAR2(10);  p_sc_stage_del_flag       VARCHAR2(10);  p_sc_trip_ship_method     VARCHAR2(30);  p_sc_actual_dep_date      VARCHAR2(30);  p_sc_report_set_id        NUMBER;  p_sc_report_set_name      VARCHAR2(60);  p_sc_defer_interface_flag VARCHAR2(60);  p_sc_send_945_flag        VARCHAR2(60);  p_sc_rule_id              NUMBER;  p_sc_rule_name            VARCHAR2(60);  p_wv_override_flag        VARCHAR2(10);  p_asg_pickup_stop_seq     NUMBER;  p_asg_dropoff_stop_seq    NUMBER;  x_trip_id                 VARCHAR2(30);  x_trip_name               VARCHAR2(30);  fail_api EXCEPTION;  x_debug_file       VARCHAR2(100);  l_ship_method_code VARCHAR2(100);  l_user_id          NUMBER;  l_resp_id          NUMBER;  l_appl_id          NUMBER;  CURSOR c_ord_details IS      SELECT DISTINCT det.source_header_number sales_order                   ,det.org_id                   ,det.source_line_number                   ,det.source_header_id                   ,det.source_line_id                   ,det.source_header_type_name                   ,det.inventory_item_id                   ,det.requested_quantity                   ,det.delivery_detail_id                   ,(SELECT concatenated_segments                       FROM mtl_system_items_kfv                      WHERE inventory_item_id = det.inventory_item_id                        AND organization_id = det.organization_id) ordered_item                   ,det.organization_id                   ,det.src_requested_quantity                   ,det.shipped_quantity                   ,del.delivery_id                   ,del.status_code delivery_status_code                   ,det.released_status pick_release_status                   ,det.oe_interfaced_flag                   ,det.inv_interfaced_flag      FROM wsh_delivery_details     det          ,wsh_delivery_assignments asn          ,wsh_new_deliveries       del     WHERE 1 = 1       AND det.delivery_detail_id = asn.delivery_detail_id       AND asn.delivery_id = del.delivery_id(+)       AND det.source_header_number = p_sales_order       AND det.source_line_number = p_line_number       AND det.org_id = p_org_id       AND shipped_quantity IS NULL       AND nvl(del.status_code, 'OP') <> 'CL'       AND det.released_status = 'Y';  --  v_user_id      NUMBER;  v_resp_id      NUMBER;  v_resp_appl_id NUMBER;  --  BEGIN  --  /* To get the user id details */  SELECT user_id INTO v_user_id FROM fnd_user WHERE user_name = 'SETUP01';  /* To get the responsibility and responsibility application id */  SELECT f.responsibility_id, f.application_id    INTO v_resp_id, v_resp_appl_id    FROM fnd_responsibility_tl f   WHERE f.responsibility_name = '受注管理スーパーユーザー（SC）'     AND f.language = 'JA';  --  fnd_global.apps_initialize(user_id      => v_user_id,                             resp_id      => v_resp_id,                             resp_appl_id => v_resp_appl_id);  dbms_output.put_line(v_user_id || ' ' || v_resp_id || ' ' ||                       v_resp_appl_id);  --  FOR i IN c_ord_details LOOP    dbms_output.put_line('Initializing the Application for Shipping Transactions');    -- Mandatory initialization for R12    mo_global.set_policy_context('S', i.org_id);    mo_global.init('ONT');    -- Ship Confirming    p_delivery_name := to_char(i.delivery_id);      dbms_output.put_line('Before Shipping, Calling WSH_DELIVERY_DETAILS_PUB API to Update Shipping Attributes');    dbms_output.put_line('=============================================');    l_changed_attributes(1).delivery_detail_id := i.delivery_detail_id;    l_changed_attributes(1).shipped_quantity := l_shipped_quantity;    wsh_delivery_details_pub.update_shipping_attributes(p_api_version_number => 1.0,                                                        p_init_msg_list      => init_msg_list,                                                        p_commit             => l_commit,                                                        x_return_status      => x_return_status,                                                        x_msg_count          => x_msg_count,                                                        x_msg_data           => x_msg_data,                                                        p_changed_attributes => l_changed_attributes,                                                        p_source_code        => l_source_code);      IF (x_return_status <> wsh_util_core.g_ret_sts_success) THEN      RAISE fail_api;      dbms_output.put_line('Failed to Update the Shipping Attributes');    ELSE      dbms_output.put_line('Successfully Updated the Shipping Attributes');    END IF;      BEGIN      SELECT shipping_method_code        INTO l_ship_method_code        FROM oe_order_headers_all       WHERE order_number = i.sales_order         AND org_id = i.org_id;    EXCEPTION      WHEN OTHERS THEN        l_ship_method_code := NULL;    END;      p_action_code       := 'CONFIRM'; -- The action code for ship confirm    p_sc_action_flag    := 'S'; -- Ship entered quantity.    p_sc_intransit_flag := 'N';    --In transit flag is set to 'Y' closes the pickup stop and sets the delivery in transit.    p_sc_close_trip_flag      := 'Y'; -- Close the trip after ship confirm    p_sc_trip_ship_method     := l_ship_method_code; -- The ship method code    p_sc_defer_interface_flag := 'Y';    p_sc_stage_del_flag       := 'Y';    p_sc_create_bol_flag      := 'N';    p_wv_override_flag        := 'N';      -- API Call for Ship Confirmation    dbms_output.put_line('Calling WSH_DELIVERIES_PUB to Perform Ship Confirmation');    dbms_output.put_line('=============================================');      wsh_deliveries_pub.delivery_action(p_api_version_number      => 1.0,                                       p_init_msg_list           => init_msg_list,                                       x_return_status           => x_return_status,                                       x_msg_count               => x_msg_count,                                       x_msg_data                => x_msg_data,                                       p_action_code             => p_action_code,                                       p_delivery_id             => i.delivery_id,                                       p_delivery_name           => p_delivery_name,                                       p_asg_trip_id             => p_asg_trip_id,                                       p_asg_trip_name           => p_asg_trip_name,                                       p_asg_pickup_stop_id      => p_asg_pickup_stop_id,                                       p_asg_pickup_loc_id       => p_asg_pickup_loc_id,                                       p_asg_pickup_stop_seq     => p_asg_pickup_stop_seq,                                       p_asg_pickup_loc_code     => p_asg_pickup_loc_code,                                       p_asg_pickup_arr_date     => p_asg_pickup_arr_date,                                       p_asg_pickup_dep_date     => p_asg_pickup_dep_date,                                       p_asg_dropoff_stop_id     => p_asg_dropoff_stop_id,                                       p_asg_dropoff_loc_id      => p_asg_dropoff_loc_id,                                       p_asg_dropoff_stop_seq    => p_asg_dropoff_stop_seq,                                       p_asg_dropoff_loc_code    => p_asg_dropoff_loc_code,                                       p_asg_dropoff_arr_date    => p_asg_dropoff_arr_date,                                       p_asg_dropoff_dep_date    => p_asg_dropoff_dep_date,                                       p_sc_action_flag          => p_sc_action_flag,                                       p_sc_intransit_flag       => p_sc_intransit_flag,                                       p_sc_close_trip_flag      => p_sc_close_trip_flag,                                       p_sc_create_bol_flag      => p_sc_create_bol_flag,                                       p_sc_stage_del_flag       => p_sc_stage_del_flag,                                       p_sc_trip_ship_method     => p_sc_trip_ship_method,                                       p_sc_actual_dep_date      => p_sc_actual_dep_date,                                       p_sc_report_set_id        => p_sc_report_set_id,                                       p_sc_report_set_name      => p_sc_report_set_name,                                       p_sc_defer_interface_flag => p_sc_defer_interface_flag,                                       p_sc_send_945_flag        => p_sc_send_945_flag,                                       p_sc_rule_id              => p_sc_rule_id,                                       p_sc_rule_name            => p_sc_rule_name,                                       p_wv_override_flag        => p_wv_override_flag,                                       x_trip_id                 => x_trip_id,                                       x_trip_name               => x_trip_name);      IF (x_return_status <> wsh_util_core.g_ret_sts_success) THEN      dbms_output.put_line('Ship confirm has not been Completed For SO => ');      --ROLLBACK;      RAISE fail_api;    ELSE      dbms_output.put_line('Ship confirm Successfully Completed For SO => ');      --COMMIT;          dbms_output.put_line('Checking the Delivery Status after delivery action API Call');      dbms_output.put_line('==========================================');          SELECT wdd.source_code            ,wdd.released_status            ,wdd.inv_interfaced_flag            ,wdd.oe_interfaced_flag            ,wts.pending_interface_flag        INTO v_source_code            ,v_released_status            ,v_inv_interfaced_flag            ,v_oe_interfaced_flag            ,v_pending_interface_flag        FROM wsh_trips                wtr            ,wsh_trip_stops           wts            ,wsh_delivery_legs        wlg            ,wsh_new_deliveries       wnd            ,wsh_delivery_assignments wda            ,wsh_delivery_details     wdd       WHERE wtr.trip_id = wts.trip_id         AND wts.stop_id = wlg.pick_up_stop_id         AND wts.pending_interface_flag = 'Y'         AND wdd.inv_interfaced_flag <> 'Y'         AND wlg.delivery_id = wnd.delivery_id         AND wnd.delivery_id = wda.delivery_id         AND wda.delivery_detail_id = wdd.delivery_detail_id         AND wnd.delivery_id = p_delivery_name         AND wdd.source_line_id = i.source_line_id;          IF (v_source_code = 'OE' AND v_released_status = 'C' AND         v_inv_interfaced_flag <> 'Y' AND v_oe_interfaced_flag <> 'Y' AND         v_pending_interface_flag = 'Y') THEN              dbms_output.put_line('The Delivery has been Shipped & the Next Step is - Run Interface');        dbms_output.put_line('===========================================');        -- API Call for Submitting Interface Trip Stop              wsh_ship_confirm_actions.interface_all_wrp(errbuf            => v_errbuf,                                                   retcode           => v_retcode,                                                   p_mode            => 'ALL',                                                   p_stop_id         => NULL,                                                   p_delivery_id     => p_delivery_name,                                                   p_log_level       => 0,                                                   p_batch_id        => NULL,                                                   p_trip_type       => NULL,                                                   p_organization_id => i.organization_id,                                                   p_num_requests    => 1,                                                   p_stops_per_batch => 1);            ELSE        dbms_output.put_line('The Delivery has not Shipped Properly');      END IF;    END IF;  END LOOP;EXCEPTION  WHEN fail_api THEN      dbms_output.put_line('==============');    dbms_output.put_line('Error Details If Any');    dbms_output.put_line('==============');      wsh_util_core.get_messages(p_init_msg_list => 'Y',                               x_summary       => x_msg_summary,                               x_details       => x_msg_details,                               x_count         => x_msg_count);      IF x_msg_count > 1 THEN      x_msg_data := x_msg_summary || x_msg_details;      dbms_output.put_line(x_msg_data);    ELSE      x_msg_data := x_msg_summary || x_msg_details;      dbms_output.put_line(x_msg_data);    END IF;    --  --FOR i IN 1 .. x_msg_count LOOP    --  x_msg_data := oe_msg_pub.get(p_msg_index => i, p_encoded => 'F');    --  dbms_output.put_line(i || ') ' || x_msg_data);    --END LOOP;   --END;