Partial Shipments of a Sales Order Using API

Partial Shipments of a Sales Order Using API:==================================For partial shipment of the sales order, we need to call the WSH_DELIVERY_DETAILS_PUB.Update_Shipping_Attributes API to update the corresponding delivery details to ship all/entered quantity in the delivery details.-- OM - Script to Ship Partial Quantities in a SO using WSH_DELIVERY_PUB API ----===========================================================DECLAREp_sales_order NUMBER := 10014445;p_line_number NUMBER := 1.1;p_org_id NUMBER := 308;l_shipped_quantity NUMBER := 5;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_PUBp_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_detailsISSELECT 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_segmentsFROM mtl_system_items_kfvWHERE inventory_item_id =det.inventory_item_idAND 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_flagFROM wsh_delivery_details det,wsh_delivery_assignments asn,wsh_new_deliveries delWHERE 1 = 1AND det.delivery_detail_id = asn.delivery_detail_idAND asn.delivery_id = del.delivery_id(+)AND det.source_header_number = p_sales_orderAND det.source_line_number = p_line_numberAND det.org_id = p_org_idAND shipped_quantity IS NULLAND NVL (del.status_code, 'OP') <> 'CL'AND det.released_status = 'Y';BEGIN-- Initializing the ApplicationsSELECT user_idINTO l_user_idFROM fnd_userWHERE user_name = 'A42485';SELECT responsibility_id, application_idINTO l_resp_id, l_appl_idFROM fnd_responsibility_vlWHERE responsibility_name = 'Order Management Super User';fnd_global.apps_initialize (l_user_id, l_resp_id, l_appl_id);FOR i IN c_ord_detailsLOOPDBMS_OUTPUT.put_line('Initializing the Application for Shipping Transactions');-- Mandatory initialization for R12mo_global.set_policy_context ('S', i.org_id);mo_global.init ('ONT');-- Ship Confirmingp_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)THENRAISE fail_api;DBMS_OUTPUT.put_line ('Failed to Update the Shipping Attributes');ELSEDBMS_OUTPUT.put_line ('Successfully Updated the Shipping Attributes');END IF;BEGINSELECT shipping_method_codeINTO l_ship_method_codeFROM oe_order_headers_allWHERE order_number = i.sales_order AND org_id = i.org_id;EXCEPTIONWHEN OTHERSTHENl_ship_method_code := NULL;END;p_action_code := 'CONFIRM'; -- The action code for ship confirmp_sc_action_flag := 'S'; -- Ship entered quantity.p_sc_intransit_flag := 'Y';--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 confirmp_sc_trip_ship_method := l_ship_method_code; -- The ship method codep_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 ConfirmationDBMS_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)THENDBMS_OUTPUT.put_line('Ship confirm has not been Completed For SO => ');ROLLBACK;RAISE fail_api;ELSEDBMS_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_flagINTO v_source_code, v_released_status,v_inv_interfaced_flag, v_oe_interfaced_flag,v_pending_interface_flagFROM wsh_trips wtr,wsh_trip_stops wts,wsh_delivery_legs wlg,wsh_new_deliveries wnd,wsh_delivery_assignments wda,wsh_delivery_details wddWHERE wtr.trip_id = wts.trip_idAND wts.stop_id = wlg.pick_up_stop_idAND wts.pending_interface_flag = 'Y'AND wdd.inv_interfaced_flag <> 'Y'AND wlg.delivery_id = wnd.delivery_idAND wnd.delivery_id = wda.delivery_idAND wda.delivery_detail_id = wdd.delivery_detail_idAND wnd.delivery_id = p_delivery_nameAND 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')THENDBMS_OUTPUT.put_line('The Delivery has been Shipped & the Next Step is - Run Interface');DBMS_OUTPUT.put_line('===========================================');-- API Call for Submitting Interface Trip Stopwsh_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);ELSEDBMS_OUTPUT.put_line ('The Delivery has not Shipped Properly');END IF;END IF;END LOOP;EXCEPTIONWHEN fail_apiTHENDBMS_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 > 1THENx_msg_data := x_msg_summary x_msg_details;DBMS_OUTPUT.put_line (x_msg_data);ELSEx_msg_data := x_msg_summary x_msg_details;DBMS_OUTPUT.put_line (x_msg_data);END IF;END;
/