Declare
x_return_status VARCHAR2 (3000);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (3000);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (2000);
l_msg_index NUMBER;
--Standard Parameters.
p_api_version_number NUMBER;
init_msg_list VARCHAR2 (30);
x_msg_details VARCHAR2 (3000);
x_msg_summary VARCHAR2 (3000);
p_validation_level NUMBER;
p_commit VARCHAR2 (30);
--Parameters for WSH_DELIVERIES_PUB.Delivery_Action.
p_action_code VARCHAR2 (15);
p_delivery_id NUMBER;
p_delivery_name VARCHAR2 (30);
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_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_wv_override_flag VARCHAR2 (10);
x_trip_id VARCHAR2 (30);
x_trip_name VARCHAR2 (30);
/*Handle exceptions*/
fail_api EXCEPTION;
CURSOR lc_get_ship_data
IS
Select wdd.delivery_Detail_id,
ooh.order_source_id,
ooh.header_id,
NULL Line_id,
wdd.source_header_id,
sot.name,
wdd.source_line_id,
wdd.subinventory whse_code,
ooh.order_number,
ooh.attribute5 po_expire_date,
DECODE (wdd.released_status, 'Y', 'Staged/Pick Confirmed')
Shipping_Status,
wda.delivery_id
FROM oe_order_headers_all ooh,
oe_transaction_types_v sot,
wsh_delivery_details wdd,
wsh_delivery_assignments wda
WHERE 1 = 1
AND ooh.header_id = wdd.source_header_id
AND wdd.delivery_detail_id = wda.delivery_detail_id
AND wda.delivery_id IS NOT NULL
AND sot.transaction_type_id = ooh.order_type_id
AND wdd.released_status = 'Y';
ln_user_id NUMBER;
ln_resp_id NUMBER;
ln_resp_app_id NUMBER;
ln_reason_id NUMBER;
BEGIN
/* Initialize return status*/
x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
/* Call this procedure to initialize applications parameters. To determine
parameter values, refer to the Application Paramater Initialization section of
this chapter. */
--BEGIN
--fnd_file.put_line(fnd_file.output,'STATUS'||' '||'ORDER NUMBER'||' '||'DATE'||' '||' '||'WAREHOUSE'||' '||'CANCELLED QTY');
ln_user_id := APPS.FND_PROFILE.VALUE ('USER_ID');
IF ln_user_id IS NULL
THEN
ln_user_id := -1;
END IF;
ln_resp_id := APPS.FND_PROFILE.VALUE ('RESP_ID');
ln_resp_app_id := APPS.FND_PROFILE.VALUE ('RESP_APPL_ID');
BEGIN
FND_GLOBAL.apps_initialize (ln_user_id, ln_resp_id, ln_resp_app_id);
END;
IF v_delivery_id is NULL THEN
FOR lc_ship_dt IN lc_get_ship_data
LOOP
--exit when lc_get_ship_data%notfound;
/* Values to be set for action code 3 are */
p_action_code := 'CONFIRM'; -- The action code for ship confirm
p_delivery_id := lc_ship_dt.delivery_id; --620600; -- The delivery that needs to be confirmed
p_delivery_name := lc_ship_dt.delivery_id; --'620600'; -- The delivery name,
p_sc_action_flag := 'C'; -- Ship entered quantity.
P_sc_stage_del_flag := 'N'; --
--p_sc_trip_ship_method := Null; -- The ship method code
/*Call to WSH_DELIVERIES_PUB.Delivery_Action. */
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 => p_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_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_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_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_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 -- Commented By Samba on 21-JAN-2013
IF (x_return_status IN (WSH_UTIL_CORE.G_RET_STS_SUCCESS, 'W'))
THEN -- Added By Samba on 21-JAN-2013
fnd_file.put_line (
fnd_file.LOG,
'Successfully BackOrdered for Order Number - '
|| lc_ship_dt.order_number
|| ', and for whse - '
|| lc_ship_dt.whse_code);
COMMIT;
ELSE
fnd_file.put_line (
fnd_file.LOG,
'Could not able to Back Order Line Due to Following Reasons -'
|| x_return_status
|| ' for header id - '
|| lc_ship_dt.header_id);
ROLLBACK;
FOR j IN 1 .. x_msg_count
LOOP
fnd_msg_pub.get (p_msg_index => j,
p_encoded => fnd_api.g_false,
p_data => l_msg_data,
p_msg_index_out => l_msg_index);
fnd_file.put_line (fnd_file.LOG,
'Error Message is=> ' || l_msg_data);
END LOOP;
END IF;
END LOOP;
Else
FOR i IN c2(v_delivery_id)
LOOP
--exit when lc_get_ship_data%notfound;
/* Values to be set for action code 3 are */
p_action_code := 'CONFIRM'; -- The action code for ship confirm
p_delivery_id := i.delivery_id; --620600; -- The delivery that needs to be confirmed
p_delivery_name := i.delivery_id; --'620600'; -- The delivery name,
p_sc_action_flag := 'C'; -- Ship entered quantity.
P_sc_stage_del_flag := 'N'; --
--p_sc_trip_ship_method := Null; -- The ship method code
/*Call to WSH_DELIVERIES_PUB.Delivery_Action. */
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 => p_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_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_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_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_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 -- Commented By Samba on 21-JAN-2013
IF (x_return_status IN (WSH_UTIL_CORE.G_RET_STS_SUCCESS, 'W'))
THEN -- Added By Samba on 21-JAN-2013
fnd_file.put_line (
fnd_file.LOG,
'Successfully BackOrdered for Order Number - '
|| i.order_number
|| ', and for whse - '
|| i.whse_code);
COMMIT;
ELSE
fnd_file.put_line (
fnd_file.LOG,
'Could not able to Back Order Line Due to Following Reasons -'
|| l_msg_data
|| ' for header id - '
|| i.header_id);
fnd_file.put_line (
fnd_file.LOG,'msg count is - '||x_msg_count);
FOR j IN 1 .. x_msg_count
LOOP
fnd_msg_pub.get (p_msg_index => j,
p_encoded => fnd_api.g_false,
p_data => l_msg_data,
p_msg_index_out => l_msg_index);
fnd_file.put_line (fnd_file.LOG,
'Error Message is=> ' || l_msg_data);
END LOOP;
ROLLBACK;
END IF;
END LOOP;
END IF;
END;
x_return_status VARCHAR2 (3000);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (3000);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (2000);
l_msg_index NUMBER;
--Standard Parameters.
p_api_version_number NUMBER;
init_msg_list VARCHAR2 (30);
x_msg_details VARCHAR2 (3000);
x_msg_summary VARCHAR2 (3000);
p_validation_level NUMBER;
p_commit VARCHAR2 (30);
--Parameters for WSH_DELIVERIES_PUB.Delivery_Action.
p_action_code VARCHAR2 (15);
p_delivery_id NUMBER;
p_delivery_name VARCHAR2 (30);
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_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_wv_override_flag VARCHAR2 (10);
x_trip_id VARCHAR2 (30);
x_trip_name VARCHAR2 (30);
/*Handle exceptions*/
fail_api EXCEPTION;
CURSOR lc_get_ship_data
IS
Select wdd.delivery_Detail_id,
ooh.order_source_id,
ooh.header_id,
NULL Line_id,
wdd.source_header_id,
sot.name,
wdd.source_line_id,
wdd.subinventory whse_code,
ooh.order_number,
ooh.attribute5 po_expire_date,
DECODE (wdd.released_status, 'Y', 'Staged/Pick Confirmed')
Shipping_Status,
wda.delivery_id
FROM oe_order_headers_all ooh,
oe_transaction_types_v sot,
wsh_delivery_details wdd,
wsh_delivery_assignments wda
WHERE 1 = 1
AND ooh.header_id = wdd.source_header_id
AND wdd.delivery_detail_id = wda.delivery_detail_id
AND wda.delivery_id IS NOT NULL
AND sot.transaction_type_id = ooh.order_type_id
AND wdd.released_status = 'Y';
ln_user_id NUMBER;
ln_resp_id NUMBER;
ln_resp_app_id NUMBER;
ln_reason_id NUMBER;
BEGIN
/* Initialize return status*/
x_return_status := WSH_UTIL_CORE.G_RET_STS_SUCCESS;
/* Call this procedure to initialize applications parameters. To determine
parameter values, refer to the Application Paramater Initialization section of
this chapter. */
--BEGIN
--fnd_file.put_line(fnd_file.output,'STATUS'||' '||'ORDER NUMBER'||' '||'DATE'||' '||' '||'WAREHOUSE'||' '||'CANCELLED QTY');
ln_user_id := APPS.FND_PROFILE.VALUE ('USER_ID');
IF ln_user_id IS NULL
THEN
ln_user_id := -1;
END IF;
ln_resp_id := APPS.FND_PROFILE.VALUE ('RESP_ID');
ln_resp_app_id := APPS.FND_PROFILE.VALUE ('RESP_APPL_ID');
BEGIN
FND_GLOBAL.apps_initialize (ln_user_id, ln_resp_id, ln_resp_app_id);
END;
IF v_delivery_id is NULL THEN
FOR lc_ship_dt IN lc_get_ship_data
LOOP
--exit when lc_get_ship_data%notfound;
/* Values to be set for action code 3 are */
p_action_code := 'CONFIRM'; -- The action code for ship confirm
p_delivery_id := lc_ship_dt.delivery_id; --620600; -- The delivery that needs to be confirmed
p_delivery_name := lc_ship_dt.delivery_id; --'620600'; -- The delivery name,
p_sc_action_flag := 'C'; -- Ship entered quantity.
P_sc_stage_del_flag := 'N'; --
--p_sc_trip_ship_method := Null; -- The ship method code
/*Call to WSH_DELIVERIES_PUB.Delivery_Action. */
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 => p_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_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_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_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_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 -- Commented By Samba on 21-JAN-2013
IF (x_return_status IN (WSH_UTIL_CORE.G_RET_STS_SUCCESS, 'W'))
THEN -- Added By Samba on 21-JAN-2013
fnd_file.put_line (
fnd_file.LOG,
'Successfully BackOrdered for Order Number - '
|| lc_ship_dt.order_number
|| ', and for whse - '
|| lc_ship_dt.whse_code);
COMMIT;
ELSE
fnd_file.put_line (
fnd_file.LOG,
'Could not able to Back Order Line Due to Following Reasons -'
|| x_return_status
|| ' for header id - '
|| lc_ship_dt.header_id);
ROLLBACK;
FOR j IN 1 .. x_msg_count
LOOP
fnd_msg_pub.get (p_msg_index => j,
p_encoded => fnd_api.g_false,
p_data => l_msg_data,
p_msg_index_out => l_msg_index);
fnd_file.put_line (fnd_file.LOG,
'Error Message is=> ' || l_msg_data);
END LOOP;
END IF;
END LOOP;
Else
FOR i IN c2(v_delivery_id)
LOOP
--exit when lc_get_ship_data%notfound;
/* Values to be set for action code 3 are */
p_action_code := 'CONFIRM'; -- The action code for ship confirm
p_delivery_id := i.delivery_id; --620600; -- The delivery that needs to be confirmed
p_delivery_name := i.delivery_id; --'620600'; -- The delivery name,
p_sc_action_flag := 'C'; -- Ship entered quantity.
P_sc_stage_del_flag := 'N'; --
--p_sc_trip_ship_method := Null; -- The ship method code
/*Call to WSH_DELIVERIES_PUB.Delivery_Action. */
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 => p_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_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_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_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_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 -- Commented By Samba on 21-JAN-2013
IF (x_return_status IN (WSH_UTIL_CORE.G_RET_STS_SUCCESS, 'W'))
THEN -- Added By Samba on 21-JAN-2013
fnd_file.put_line (
fnd_file.LOG,
'Successfully BackOrdered for Order Number - '
|| i.order_number
|| ', and for whse - '
|| i.whse_code);
COMMIT;
ELSE
fnd_file.put_line (
fnd_file.LOG,
'Could not able to Back Order Line Due to Following Reasons -'
|| l_msg_data
|| ' for header id - '
|| i.header_id);
fnd_file.put_line (
fnd_file.LOG,'msg count is - '||x_msg_count);
FOR j IN 1 .. x_msg_count
LOOP
fnd_msg_pub.get (p_msg_index => j,
p_encoded => fnd_api.g_false,
p_data => l_msg_data,
p_msg_index_out => l_msg_index);
fnd_file.put_line (fnd_file.LOG,
'Error Message is=> ' || l_msg_data);
END LOOP;
ROLLBACK;
END IF;
END LOOP;
END IF;
END;
No comments:
Post a Comment