Use API for shipment confirmation and run Interface Trip Stop, apiinterface

Use API for shipment confirmation and run 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); Limit NUMBER; v_errbuf VARCHAR2 (2000); v_retcode VARCHAR2 (20); v_released_status variable % TYPE; v_source_code variable % TYPE; l_changed_attributes variable; l_source_code VARCHAR2 (30): = 'oe '; -- Parameters for parameter p_delivery_name VARCHAR2 (30); p_action_code VARCHAR2 (15); p_asg_trip_id NUMBER; p_asg_trip_name VARCHAR2 (30); Comment NUMBER; comment NUMBER; Comment VARCHAR2 (30); Comment DATE; p_asg_pickup_dep_date DATE; p_asg_dropoff_stop_id NUMBER; Comment VARCHAR2 (30); Comment 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); then 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 = 'note スパユーザ (SC) 'AND f. language = 'ja '; -- fnd_global.pai_initialize (user_id => v_user_id, resp_id => v_resp_id, resp_appl_id => v_resp_appl_id ); values (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 ('====================================== ========= '); rochelle changed_attributes (1 ). delivery_detail_id: = I. delivery_detail_id; l_changed_attributes (1 ). shipped_quantity: = bytes; bytes (Bytes => 1.0, bytes => bytes, 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 <> condition) then raise fail_api; condition ('failed' to Update the Shipping Attributes '); ELSE dbms_output.put_line ('successfully Updated the Shipping bubutes '); 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. authorization: = 'y'; -- Close the trip after ship confirm p_ SC _trip_ship_method: = l_ship_method_code; -- The ship method code usage: = 'y'; authorization: = 'y'; p_ SC _create_bol_flag: = 'n'; p_wv_override_flag: = 'n'; -- API Call for Ship Confirmation dbms_output.put_line ('calling wsh_delipolices_pub to Perform Ship Confirmation '); dbms_output.put_line ('====================================== ========= '); export (response => 1.0, response => 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, response => 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, region => Users, region => Users, region => users, values => p_asg_pickup_arr_date, p_asg_pickup_dep_date => p_asg_pickup_dep_date, values => values, p_ SC _action_flag => p_ SC _action_flag, response => response, p_ SC _report_set_id => p_ SC _report_set_id, p_ SC _report_set_name => rules, rules => rules, rules => rules, p_ SC _rule_id => p_ SC _rule_id, p_ SC _rule_name => p_ SC _rule_name, rules => rules, x_trip_id => x_trip_id, x_trip_name => x_trip_name); IF (x_return_status <> condition) 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, success, fail, fail FROM wsh_trips wtr, wsh_trip_stops wts, mongowlg, mongownd, mongowda, mongowdd 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 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 slave (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 loop; exception when fail_api THEN dbms_output.put_line ('================'); dbms_output.put_line ('error Details If any '); dbms_output.put_line ('==============='); values (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 | consumed; consumed (x_msg_data); ELSE x_msg_data: = x_msg_summary | consumed; consumed (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;