Below is the standard API to update sales order deliver to information based on shipment method. Shipment method & deliver to information are maintaining in custom table.
oe_order_pub.Process_Order is the standard API
CREATE OR REPLACE PROCEDURE APPS.”UPDATE_DELIVER_TO_SALES_ORDER” (
errbuf OUT VARCHAR2,
retcode OUT NUMBER,
p_order_number IN NUMBER
)
IS
l_api_version_number NUMBER := 1;
l_return_status VARCHAR2 (2000);
l_msg_count NUMBER;
l_msg_data VARCHAR2 (4000);
l_debug_level NUMBER := 1;
i_line_tbl oe_order_pub.line_tbl_type;
l_header_rec oe_order_pub.header_rec_type;
l_line_tbl oe_order_pub.line_tbl_type;
l_action_request_tbl oe_order_pub.Request_Tbl_Type;
l_header_rec_out oe_order_pub.header_rec_type;
l_header_val_rec_out oe_order_pub.header_val_rec_type;
l_header_adj_tbl_out oe_order_pub.header_adj_tbl_type;
l_header_adj_val_tbl_out oe_order_pub.header_adj_val_tbl_type;
l_header_price_att_tbl_out oe_order_pub.header_price_att_tbl_type;
l_header_adj_att_tbl_out oe_order_pub.header_adj_att_tbl_type;
l_header_adj_assoc_tbl_out oe_order_pub.header_adj_assoc_tbl_type;
l_header_scredit_tbl_out oe_order_pub.header_scredit_tbl_type;
l_header_scredit_val_tbl_out oe_order_pub.header_scredit_val_tbl_type;
l_line_tbl_out oe_order_pub.line_tbl_type;
l_line_val_tbl_out oe_order_pub.line_val_tbl_type;
l_line_adj_tbl_out oe_order_pub.line_adj_tbl_type;
l_line_adj_val_tbl_out oe_order_pub.line_adj_val_tbl_type;
l_line_price_att_tbl_out oe_order_pub.line_price_att_tbl_type;
l_line_adj_att_tbl_out oe_order_pub.line_adj_att_tbl_type;
l_line_adj_assoc_tbl_out oe_order_pub.line_adj_assoc_tbl_type;
l_line_scredit_tbl_out oe_order_pub.line_scredit_tbl_type;
l_line_scredit_val_tbl_out oe_order_pub.line_scredit_val_tbl_type;
l_lot_serial_tbl_out oe_order_pub.lot_serial_tbl_type;
l_lot_serial_val_tbl_out oe_order_pub.lot_serial_val_tbl_type;
l_action_request_tbl_out oe_order_pub.request_tbl_type;
l_msg_index NUMBER;
l_data VARCHAR2 (2000);
l_deliver_to NUMBER;
l_payment_term_id NUMBER;
lv_interface_name VARCHAR2 (15) := ‘XXXXX’;
ld_from_date DATE;
ld_to_date DATE;
l_scheduled_flag VARCHAR2 (10);
l_index NUMBER := 0;
l_order_number NUMBER;
CURSOR c_order_header
IS
SELECT DISTINCT ooha.order_number,
ooha.header_id,
ooha.deliver_to_org_id,
ooha.shipping_method_code,
ooha.sold_to_org_id
FROM oe_order_headers_all ooha, oe_order_lines_all oola
WHERE 1 = 1
AND ooha.header_id = oola.header_id
AND ooha.org_id = FND_PROFILE.VALUE (‘ORG_ID’)
AND ooha.flow_status_code IN (‘ENTERED’, ‘BOOKED’)
AND (GREATEST (ooha.last_update_date, oola.last_update_date) BETWEEN (ld_from_date)
AND (ld_to_date))
AND ooha.order_number = NVL (p_order_number, ooha.order_number);
CURSOR c_order_line (
p_header_id IN NUMBER
)
IS
SELECT oola.header_id, oola.line_id,deliver_to_org_id
FROM oe_order_lines_all oola
WHERE 1 = 1
AND oola.flow_status_code NOT IN (‘CLOSED’, ‘CANCELLED’)
AND oola.header_id = p_header_id
AND NOT EXISTS
(SELECT 1
FROM wsh_delivery_details wdd
WHERE source_line_id = oola.line_id
AND source_header_id = oola.header_id
AND released_status = ‘Y’);
BEGIN
oe_msg_pub.initialize;
IF p_order_number IS NULL
THEN
l_scheduled_flag := ‘Y’;
ld_from_date :=
custom_package.get_last_run_date (lv_interface_name);
ld_to_date := SYSDATE;
ELSE
l_scheduled_flag := ‘N’;
ld_from_date := custom_package.gv_default_date;
ld_to_date := SYSDATE;
END IF;
fnd_file.put_line (fnd_file.LOG, ‘p_order_number’ || p_order_number);
fnd_file.put_line (fnd_file.LOG, ‘l_scheduled_flag’ || l_scheduled_flag);
fnd_file.put_line (
fnd_file.LOG,
‘ld_from_date’ || TO_CHAR (ld_from_date, ‘DD-MM-YYYY HH24:MI:SS’)
);
fnd_file.put_line (
fnd_file.LOG,
‘ld_to_date’ || TO_CHAR (ld_to_date, ‘DD-MM-YYYY HH24:MI:SS’)
);
FOR lc_header_rec IN c_order_header
LOOP
l_index := 0;
l_header_rec := oe_order_pub.G_MISS_HEADER_REC;
l_header_rec.operation := OE_GLOBALS.G_OPR_UPDATE;
l_header_rec.header_id := lc_header_rec.header_id;
l_order_number := lc_header_rec.order_number;
BEGIN
SELECT dlrshp.deliver_to
INTO l_deliver_to
FROM CUSTOM_TABLE dlrshp,
fnd_lookup_VALUES flv,
hz_cust_accounts hca
WHERE 1 = 1
AND dlrshp.SHIP_METHOD_MEANING = flv.MEANING
AND hca.ACCOUNT_NUMBER = dlrshp.CUSTOMER_NO
AND flv.LOOKUP_TYPE = ‘SHIP_METHOD’
AND flv.LANGUAGE = ‘US’
AND dlrshp.ORG_ID = FND_PROFILE.VALUE (‘ORG_ID’)
AND dlrshp.deliver_to IS NOT NULL
AND flv.lookup_code = lc_header_rec.SHIPPING_METHOD_CODE
AND hca.cust_account_id = lc_header_rec.SOLD_TO_ORG_ID;
IF l_deliver_to <> NVL (lc_header_rec.deliver_to_org_id, 0)
THEN
fnd_file.put_line (
fnd_file.LOG,
‘Updating Deliver_To for Order Number : ‘
|| lc_header_rec.order_number
);
l_header_rec.deliver_to_org_id := l_deliver_to;
i_line_tbl.DELETE;
FOR l_line_rec IN c_order_line (lc_header_rec.header_id)
LOOP
fnd_file.put_line (
fnd_file.LOG,
‘Updating Deliver_To for Line ID : ‘ || l_line_rec.line_id
);
l_index := l_index + 1;
i_line_tbl (l_index) := OE_ORDER_PUB.G_MISS_LINE_REC;
i_line_tbl (l_index).operation := oe_globals.g_opr_update;
i_line_tbl (l_index).header_id := lc_header_rec.header_id;
i_line_tbl (l_index).line_id := l_line_rec.line_id;
i_line_tbl (l_index).deliver_to_org_id := l_deliver_to;
END LOOP;
oe_msg_pub.delete_msg;
oe_order_pub.Process_Order (
p_api_version_number => l_api_version_number,
p_header_rec => l_header_rec,
p_line_tbl => i_line_tbl,
p_action_request_tbl => l_action_request_tbl,
x_header_rec => l_header_rec_out,
x_header_val_rec => l_header_val_rec_out,
x_header_adj_tbl => l_header_adj_tbl_out,
x_header_adj_val_tbl => l_header_adj_val_tbl_out,
x_header_price_att_tbl => l_header_price_att_tbl_out,
x_header_adj_att_tbl => l_header_adj_att_tbl_out,
x_header_adj_assoc_tbl => l_header_adj_assoc_tbl_out,
x_header_scredit_tbl => l_header_scredit_tbl_out,
x_header_scredit_val_tbl => l_header_scredit_val_tbl_out,
x_line_tbl => l_line_tbl_out,
x_line_val_tbl => l_line_val_tbl_out,
x_line_adj_tbl => l_line_adj_tbl_out,
x_line_adj_val_tbl => l_line_adj_val_tbl_out,
x_line_price_att_tbl => l_line_price_att_tbl_out,
x_line_adj_att_tbl => l_line_adj_att_tbl_out,
x_line_adj_assoc_tbl => l_line_adj_assoc_tbl_out,
x_line_scredit_tbl => l_line_scredit_tbl_out,
x_line_scredit_val_tbl => l_line_scredit_val_tbl_out,
x_lot_serial_tbl => l_lot_serial_tbl_out,
x_lot_serial_val_tbl => l_lot_serial_val_tbl_out,
x_action_request_tbl => l_action_request_tbl_out,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data
);
IF l_return_status = FND_API.G_RET_STS_SUCCESS
THEN
fnd_file.put_line (fnd_file.LOG, ‘Return status is success ‘);
COMMIT;
ELSE
fnd_file.put_line (fnd_file.LOG, ‘Return status failure….. ‘);
retcode := 1;
fnd_file.put_line (
fnd_file.LOG,
‘Process Order Return Status is: ‘ || l_return_status
);
fnd_file.put_line (
fnd_file.LOG,
‘Failed to Update the Order Number: ‘
|| lc_header_rec.order_number
);
FOR i IN 1 .. l_msg_count
LOOP
l_data := oe_msg_pub.get (p_msg_index => i, p_encoded => ‘F’);
fnd_file.put_line (fnd_file.LOG, (i || ‘) ‘ || l_data));
END LOOP;
ROLLBACK;
END IF;
END IF;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (
fnd_file.LOG,
‘Deliver To Value is not Exists in CUSTOM_TABLE: ‘
|| lc_header_rec.order_number
);
retcode := 1;
END;
END LOOP;
IF l_scheduled_flag = ‘Y’
THEN
custom_package.update_last_run_date (
lv_interface_name,
ld_to_date,
fnd_global.conc_request_id
);
END IF;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG, ‘ERROR : For Order Number : ‘|| l_order_number);
END;
/
