摘自:https://blog.csdn.net/chosen1brain/article/details/46561451
- 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;