Sample script for Pick Release
PROCEDURE xx_bdg_sin_delv_picrelease (
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) := fnd_api.g_false;
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_line_count NUMBER := 0;
CURSOR c_ord_details (p_header_id NUMBER)
IS
SELECT oha.header_id, oha.order_number sales_order, oha.org_id,
ola.line_number, ola.shipment_number, ola.flow_status_code,
wdd.delivery_detail_id, wdd.inv_interfaced_flag,
wdd.oe_interfaced_flag, wdd.released_status,
wdd.inventory_item_id, wdd.lot_number
FROM apps.oe_order_headers_all oha,
apps.oe_order_lines_all ola,
apps.wsh_delivery_details wdd
WHERE oha.header_id = ola.header_id
AND oha.org_id = ola.org_id
AND oha.header_id = wdd.source_header_id
AND ola.line_id = wdd.source_line_id
AND oha.booked_flag = ‘Y’
AND NVL (ola.cancelled_flag, ‘N’) ‘Y’
AND wdd.released_status IN (‘R’, ‘B’)
AND ola.flow_status_code = ‘AWAITING_SHIPPING’
AND oha.header_id IN (p_header_id);
CURSOR c1
IS
SELECT DISTINCT so_header_id, delivery_id
FROM xxx_bdg_so_stg
WHERE 1=1
and interface_status = ‘S’
AND status = ‘SO BOOKED’
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;
FND_FILE.PUT_LINE(FND_FILE.LOG,’before loop’);
dbms_output.PUT_LINE(‘before loop’);
FOR c1_rec IN c1
LOOP
i := 0;
l_line_count := 0;
IF c1_rec.delivery_id IS NULL
THEN
FOR i IN c_ord_details (c1_rec.so_header_id)
LOOP
mo_global.set_policy_context (‘S’, i.org_id);
mo_global.init (‘ONT’);
l_line_count := l_line_count + 1;
p_line_rows (l_line_count) := i.delivery_detail_id;
FND_FILE.PUT_LINE(FND_FILE.LOG,’Delivery Detail id:’ || i.delivery_detail_id);
dbms_output.PUT_LINE(‘Delivery Detail id:’ || i.delivery_detail_id);
END LOOP;
fnd_file.put_line
(fnd_file.LOG,
‘====================================================’
);
wsh_delivery_details_pub.autocreate_deliveries
(p_api_version_number => 1.0,
p_init_msg_list => apps.fnd_api.g_true,
p_commit => l_commit,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_line_rows => p_line_rows,
x_del_rows => x_del_rows
);
dbms_output.PUT_LINE(‘x_return_status’|| x_return_status);
FND_FILE.PUT_LINE(FND_FILE.LOG,x_return_status);
FND_FILE.PUT_LINE(FND_FILE.LOG,x_msg_data);
IF (x_return_status wsh_util_core.g_ret_sts_success)
THEN
ROLLBACK;
UPDATE xxx_bdg_so_stg
SET interface_status = ‘E’,
error_message = x_msg_data
WHERE so_header_id = c1_rec.so_header_id;
COMMIT;
fnd_file.put_line
(fnd_file.LOG,
‘Failed to Auto create delivery for Sales Order’
);
RAISE exep_api;
END IF;
UPDATE xxx_bdg_so_stg
SET delivery_creation_date = SYSDATE,
delivery_id = x_del_rows (1)
WHERE so_header_id = c1_rec.so_header_id;
p_delivery_id := x_del_rows (1);
p_delivery_name := TO_CHAR (x_del_rows (1));
ELSE
p_delivery_id := c1_rec.delivery_id;
p_delivery_name := c1_rec.delivery_id;
END IF;
FND_FILE.PUT_LINE(FND_FILE.LOG,’BEfore call delivery_action’ );
dbms_output.PUT_LINE(‘BEfore call delivery_action’);
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 => ‘PICK-RELEASE’,
p_delivery_id => p_delivery_id,
p_delivery_name => p_delivery_name,
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 => ‘N’,
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 => ‘Y’,
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
);
FND_FILE.PUT_LINE(FND_FILE.LOG,x_return_status);
FND_FILE.PUT_LINE(FND_FILE.LOG,x_msg_data);
dbms_output.PUT_LINE(‘After call delivery_actionx_return_status:’ || x_return_status);
IF (x_return_status wsh_util_core.g_ret_sts_success)
THEN
ROLLBACK;
UPDATE xxx_bdg_so_stg
SET interface_status = ‘E’,
error_message = ‘PICK RELEASED ISSUE’ || x_msg_data
WHERE so_header_id = c1_rec.so_header_id;
COMMIT;
fnd_file.put_line (fnd_file.LOG,
‘Failed to Pick Release the sales order’
);
RAISE exep_api;
ELSE
UPDATE xxx_bdg_so_stg
SET interface_status = ‘S’,
status = ‘PICK RELEASED’,
pick_release_date = SYSDATE
WHERE so_header_id = c1_rec.so_header_id
and status = ‘SO BOOKED’;
COMMIT;
DBMS_OUTPUT.put_line (‘Sales Order has successfully Pick Released’);
fnd_file.put_line (fnd_file.LOG,
‘Sales Order has successfully Pick Released’
);
fnd_file.put_line (fnd_file.LOG, ‘==============================’);
END IF;
END LOOP;
EXCEPTION
WHEN exep_api
THEN
fnd_file.put_line (fnd_file.LOG, ‘==============’);
fnd_file.put_line (fnd_file.LOG, ‘Error Details If Any’);
fnd_file.put_line (fnd_file.LOG, ‘==============’);
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;
fnd_file.put_line (fnd_file.LOG, x_msg_data);
ELSE
x_msg_data := x_msg_summary || x_msg_details;
fnd_file.put_line (fnd_file.LOG, x_msg_data);
END IF;
END;
