Sample script for Ship confirm
PROCEDURE xx_bdg_ship_confirm (
errbuf OUT VARCHAR2,
retcode OUT VARCHAR2,
p_org_id NUMBER
)
AS
x_return_status VARCHAR2 (2);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (2000);
p_api_version_number NUMBER := 1.0;
init_msg_list VARCHAR2 (200);
x_msg_details VARCHAR2 (3000);
x_msg_summary VARCHAR2 (3000);
p_line_rows wsh_util_core.id_tab_type;
x_del_rows wsh_util_core.id_tab_type;
l_ship_method_code VARCHAR2 (100);
i NUMBER;
l_commit VARCHAR2 (30);
p_delivery_id NUMBER;
p_delivery_name VARCHAR2 (30);
x_trip_id VARCHAR2 (30);
x_trip_name VARCHAR2 (30);
exep_api EXCEPTION;
l_picked_flag VARCHAR2 (10);
l_return_status VARCHAR2 (1000);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (1000);
l_user_id NUMBER;
l_resp_id NUMBER;
l_appl_id NUMBER;
l_int_flag VARCHAR2 (1);
l_arcount NUMBER;
l_so_line_count NUMBER;
l_int_line_count NUMBER;
CURSOR c2
IS
SELECT DISTINCT so_header_id, order_number
FROM x.xxx_so_stg
WHERE status = ‘SHIP CONFIRMED’
AND organization_id = p_org_id;
CURSOR c1
IS
SELECT DISTINCT delivery_id
FROM xxx_bdg_so_stg
WHERE status = ‘PICK RELEASED’
AND organization_id = p_org_id;
BEGIN
SELECT user_id
INTO l_user_id
FROM fnd_user
WHERE user_name = ‘XAVIER’;
SELECT responsibility_id, application_id
INTO l_resp_id, l_appl_id
FROM fnd_responsibility_vl
WHERE responsibility_name = ‘India Local Order Management’
AND NVL (TRUNC (end_date), TRUNC (SYSDATE)) >= TRUNC (SYSDATE);
fnd_global.apps_initialize (l_user_id, l_resp_id, l_appl_id);
x_return_status := wsh_util_core.g_ret_sts_success;
FOR c1_rec IN c1
LOOP
wsh_deliveries_pub.delivery_action
(p_api_version_number => 1.0,
p_init_msg_list => NULL,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_action_code => ‘CONFIRM’,
p_delivery_id => c1_rec.delivery_id,
p_delivery_name => c1_rec.delivery_id,
p_asg_trip_id => NULL,
p_asg_trip_name => NULL,
p_asg_pickup_stop_id => NULL,
p_asg_pickup_loc_id => NULL,
p_asg_pickup_stop_seq => NULL,
p_asg_pickup_loc_code => NULL,
p_asg_pickup_arr_date => NULL,
p_asg_pickup_dep_date => NULL,
p_asg_dropoff_stop_id => NULL,
p_asg_dropoff_loc_id => NULL,
p_asg_dropoff_stop_seq => NULL,
p_asg_dropoff_loc_code => NULL,
p_asg_dropoff_arr_date => NULL,
p_asg_dropoff_dep_date => NULL,
p_sc_action_flag => ‘S’,
p_sc_intransit_flag => ‘N’,
p_sc_close_trip_flag => ‘Y’,
p_sc_create_bol_flag => ‘N’,
p_sc_stage_del_flag => ‘Y’,
p_sc_trip_ship_method => NULL,
p_sc_actual_dep_date => NULL,
p_sc_report_set_id => NULL,
p_sc_report_set_name => NULL,
p_sc_defer_interface_flag => ‘N’,
p_sc_send_945_flag => NULL,
p_sc_rule_id => NULL,
p_sc_rule_name => NULL,
p_wv_override_flag => ‘N’,
x_trip_id => x_trip_id,
x_trip_name => x_trip_name
);
IF (x_return_status wsh_util_core.g_ret_sts_success)
THEN
ROLLBACK;
DBMS_OUTPUT.put_line (‘Failed to Pick Confirm the sales order’);
wsh_util_core.get_messages (‘Y’,
x_msg_summary,
x_msg_details,
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;
UPDATE xxx_bdg_so_stg
SET interface_status = ‘E’,
error_message = ‘SHIP CONFIRM ISSUE’ || x_msg_data
WHERE delivery_id = c1_rec.delivery_id;
COMMIT;
ELSE
UPDATE xxx_bdg_so_stg
SET interface_status = ‘S’,
status = ‘SHIP CONFIRMED’,
ship_confirm_date = SYSDATE,
error_message = NULL
WHERE delivery_id = c1_rec.delivery_id
and status = ‘PICK RELEASED’;
COMMIT;
DBMS_OUTPUT.put_line
( ‘Sales Order has successfully Ship Confirmed, ‘
|| x_trip_id
|| ‘ ‘
|| x_trip_name
);
DBMS_OUTPUT.put_line (‘==============================’);
END IF;
END LOOP;
END;
