- Internal & Purchase Requisitions created by users
- Purchase Orders created for the requisitions (inventory and non inventory items)
- Receiving transactions with PO and Requisition information
1. Back ordering unspecified quantities
2. Closing the delivery automatically by submitting the Trip stop program after ship confirm is successful
CREATE OR REPLACE PROCEDURE erps_ship_confirm_delivery (
v_delivery_name IN VARCHAR2, — delivery number
v_action IN VARCHAR2, — Pass ‘B’ to backorder the unspecified quantity
p_ship_conf_status OUT VARCHAR2,
x_msg_data OUT VARCHAR2)
IS
p_api_version_number NUMBER;
init_msg_list VARCHAR2 (30);
x_msg_count NUMBER;
x_msg_details VARCHAR2 (32000);
x_msg_summary VARCHAR2 (32000);
p_validation_level NUMBER;
p_commit VARCHAR2 (30);
x_return_status VARCHAR2 (15);
source_code VARCHAR2 (15);
changed_attributes wsh_delivery_details_pub.changedattributetabtype;
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_defer_iface 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);
p_msg_data VARCHAR2 (32000);
fail_api EXCEPTION;
BEGIN
x_return_status := wsh_util_core.g_ret_sts_success;
p_action_code := ‘CONFIRM’;
p_delivery_name := v_delivery_name;
p_sc_action_flag := v_action;
p_sc_close_trip_flag := ‘Y’; — Trip stop concurrent program will be submitted automatically
p_defer_iface := ‘N’;
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 => p_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_sc_defer_interface_flag => p_defer_iface,
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
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;
ELSE
x_msg_data := x_msg_summary;
END IF;
p_ship_conf_status := ‘E’;
ELSE
p_ship_conf_status := ‘S’;
END IF;
END erps_ship_confirm_delivery;
SHIP CONFIRMATION THROUGH FORMS
Navigate to Shipping responsibility >> Shipping >> Transactions
Query the delivery that need to be ship confirmed
click ship confirm button.
SELECT DISTINCT
fmep.menu_id,
DECODE (
fmep.function_id,
NULL, DECODE (
fmec.function_id,
NULL, DECODE (fmec1.function_id,
NULL, ‘No Func’,
fmec1.function_id),
fmec.function_id),
fmep.function_id)
funcID,
fff.user_function_name,
fff.description
FROM fnd_form_functions_tl fff,
fnd_menu_entries fmec1,
fnd_menu_entries fmec,
fnd_menu_entries fmep
WHERE fmep.menu_id = (SELECT menu_id
FROM fnd_menus
WHERE menu_name = ‘INV_NAVIGATE’ –Change the menu according to your requirement
AND ROWNUM = 1)
AND fmep.sub_menu_id = fmec.menu_id(+)
AND fmec.sub_menu_id = fmec1.menu_id(+)
AND fff.function_id =
DECODE (
fmep.function_id,
NULL, DECODE (
fmec.function_id,
NULL, DECODE (fmec1.function_id,
NULL, -999,
fmec1.function_id),
fmec.function_id),
fmep.function_id)
ORDER BY DECODE (
fmep.function_id,
NULL, DECODE (
fmec.function_id,
NULL, DECODE (fmec1.function_id,
NULL, ‘No Func’,
fmec1.function_id),
fmec.function_id),
fmep.function_id)
DECLARE l_category_rec INV_ITEM_CATEGORY_PUB.CATEGORY_REC_TYPE; l_return_status VARCHAR2(80); l_error_code NUMBER; l_msg_count NUMBER; l_msg_data VARCHAR2(80); l_out_category_id NUMBER; BEGIN l_category_rec.segment1 := 'RED'; SELECT f.ID_FLEX_NUM INTO l_category_rec.structure_id FROM FND_ID_FLEX_STRUCTURES f WHERE f.ID_FLEX_STRUCTURE_CODE = 'INV_COLORS'; l_category_rec.description := 'Red'; INV_ITEM_CATEGORY_PUB.Create_Category ( p_api_version => 1.0, p_init_msg_list => FND_API.G_FALSE, p_commit => FND_API.G_TRUE, x_return_status => l_return_status, x_errorcode => l_error_code, x_msg_count => l_msg_count, x_msg_data => l_msg_data, p_category_rec => l_category_rec, x_category_id => l_out_category_id ); IF l_return_status = fnd_api.g_ret_sts_success THEN COMMIT; DBMS_OUTPUT.put_line ('Creation of Item Category is Successful : '||l_out_category_id); ELSE DBMS_OUTPUT.put_line ('Creation of Item Category Failed with the error :'||l_error_code); ROLLBACK; END IF; END ;
2. INV_ITEM_CATEGORY_PUB. Delete_Category:
DECLARE l_return_status VARCHAR2(80); l_error_code NUMBER; l_msg_count NUMBER; l_msg_data VARCHAR2(80); l_category_id NUMBER; BEGIN SELECT mcb.CATEGORY_ID INTO l_category_id FROM mtl_categories_b mcb WHERE mcb.SEGMENT1='RED' AND mcb.STRUCTURE_ID = (SELECT mcs_b.STRUCTURE_ID FROM mtl_category_sets_b mcs_b WHERE mcs_b.CATEGORY_SET_ID = (SELECT mcs_tl.CATEGORY_SET_ID FROM mtl_category_sets_tl mcs_tl WHERE CATEGORY_SET_NAME ='INV_COLORS_SET' ) ); INV_ITEM_CATEGORY_PUB.Delete_Category ( p_api_version => 1.0, p_init_msg_list => FND_API.G_FALSE, p_commit => FND_API.G_TRUE, x_return_status => l_return_status, x_errorcode => l_error_code, x_msg_count => l_msg_count, x_msg_data => l_msg_data, p_category_id => l_category_id); IF l_return_status = fnd_api.g_ret_sts_success THEN COMMIT; DBMS_OUTPUT.put_line ('Deletion of Item Category is Successful : '||l_category_id); ELSE DBMS_OUTPUT.put_line ('Deletion of Item Category Failed with the error :'||l_error_code); ROLLBACK; END IF; END ;
3. INV_ITEM_CATEGORY_PUB.Update_Category_Description Updates the category description.
DECLARE l_return_status VARCHAR2(80); l_error_code NUMBER; l_msg_count NUMBER; l_msg_data VARCHAR2(80); l_category_id NUMBER; l_description VARCHAR2(80); BEGIN select mcb.CATEGORY_ID into l_category_id from mtl_categories_b mcb where mcb.SEGMENT1='BLACK' and mcb.STRUCTURE_ID = (select mcs_b.STRUCTURE_ID from mtl_category_sets_b mcs_b where mcs_b.CATEGORY_SET_ID = (select mcs_tl.CATEGORY_SET_ID from mtl_category_sets_tl mcs_tl where CATEGORY_SET_NAME ='INV_COLORS_SET')); l_description := 'new black color'; INV_ITEM_CATEGORY_PUB.Update_Category_Description ( p_api_version => 1.0, p_init_msg_list => FND_API.G_FALSE, p_commit => FND_API.G_TRUE, x_return_status => l_return_status, x_errorcode => l_error_code, x_msg_count => l_msg_count, x_msg_data => l_msg_data, p_category_id => l_category_id, p_description => l_description); IF l_return_status = fnd_api.g_ret_sts_success THEN COMMIT; DBMS_OUTPUT.put_line ('Update of Item Category Description is Successful : '||l_category_id); ELSE DBMS_OUTPUT.put_line ('Update of Item Category Description Failed with the error :'||l_error_code); ROLLBACK; END IF; END ;
DECLARE l_return_status VARCHAR2(80); l_error_code NUMBER; l_msg_count NUMBER; l_msg_data VARCHAR2(80); l_category_set_id NUMBER; l_category_id NUMBER; BEGIN select mcs_tl.CATEGORY_SET_ID into l_category_set_id from mtl_category_sets_tl mcs_tl where mcs_tl.CATEGORY_SET_NAME ='INV_COLORS_SET'; select mcb.CATEGORY_ID into l_category_id from mtl_categories_b mcb where mcb.SEGMENT1='RED' and mcb.STRUCTURE_ID = (select mcs_b.STRUCTURE_ID from mtl_category_sets_b mcs_b where mcs_b.CATEGORY_SET_ID = (select mcs_tl.CATEGORY_SET_ID from mtl_category_sets_tl mcs_tl where CATEGORY_SET_NAME ='INV_COLORS_SET')); INV_ITEM_CATEGORY_PUB.Create_Valid_Category ( p_api_version => 1.0, p_init_msg_list => FND_API.G_FALSE, p_commit => FND_API.G_TRUE, x_return_status => l_return_status, x_errorcode => l_error_code, x_msg_count => l_msg_count, x_msg_data => l_msg_data, p_category_set_id => l_category_set_id, p_category_id => l_category_id, p_parent_category_id => NULL ); IF l_return_status = fnd_api.g_ret_sts_success THEN COMMIT; DBMS_OUTPUT.put_line ('Create Valid Category is Successful : '||l_category_id); ELSE DBMS_OUTPUT.put_line ('Create Valid Category Failed with the error :'||l_error_code); ROLLBACK; END IF; END ;
DECLARE l_return_status VARCHAR2(80); l_error_code NUMBER; l_msg_count NUMBER; l_msg_data VARCHAR2(80); l_category_set_id NUMBER; l_category_id NUMBER; BEGIN select mcs_tl.CATEGORY_SET_ID into l_category_set_id from mtl_category_sets_tl mcs_tl where mcs_tl.CATEGORY_SET_NAME ='INV_COLORS_SET'; select mcb.CATEGORY_ID into l_category_id from mtl_categories_b mcb where mcb.SEGMENT1='RED' and mcb.STRUCTURE_ID = (select mcs_b.STRUCTURE_ID from mtl_category_sets_b mcs_b where mcs_b.CATEGORY_SET_ID = (select mcs_tl.CATEGORY_SET_ID from mtl_category_sets_tl mcs_tl where CATEGORY_SET_NAME ='INV_COLORS_SET')); INV_ITEM_CATEGORY_PUB.Delete_Valid_Category ( p_api_version => 1.0, p_init_msg_list => FND_API.G_FALSE, p_commit => FND_API.G_TRUE, x_return_status => l_return_status, x_errorcode => l_error_code, x_msg_count => l_msg_count, x_msg_data => l_msg_data, p_category_set_id => l_category_set_id, p_category_id => l_category_id); IF l_return_status = fnd_api.g_ret_sts_success THEN COMMIT; DBMS_OUTPUT.put_line ('Delete Valid Category is Successful : '||l_category_id); ELSE DBMS_OUTPUT.put_line ('Delete Valid Category Failed with the error :'||l_error_code); ROLLBACK; END IF; END ;
The procedure definition is:
PROCEDURE Assign_Item_To_Org( p_api_version IN NUMBER ,p_init_msg_list IN VARCHAR2 DEFAULT G_FALSE ,p_commit IN VARCHAR2 DEFAULT G_FALSE ,p_Inventory_Item_Id IN NUMBER DEFAULT G_MISS_NUM ,p_Item_Number IN VARCHAR2 DEFAULT G_MISS_CHAR ,p_Organization_Id IN NUMBER DEFAULT G_MISS_NUM ,p_Organization_Code IN VARCHAR2 DEFAULT G_MISS_CHAR ,p_Primary_Uom_Code IN VARCHAR2 DEFAULT G_MISS_CHAR ,x_return_status OUT NOCOPY VARCHAR2 ,x_msg_count OUT NOCOPY NUMBER);
- P_API_VERSION – A decimal number indicating major and minor revisions to the API. Pass 1.0 unless otherwise indicated in the API parameter list.
- P_INIT_MSG_LIST – A one-character flag indicating whether to initialize the FND_MSG_PUB package’s message stack at the beginning of API processing (and thus remove any messages that may exist on the stack from prior processing in the same session). Valid values are FND_API.G_TRUE and FND_API.G_FALSE.
- P_COMMIT – A one-character flag indicating whether to commit work at the end of API processing. Valid values are FND_API.G_TRUE and FND_API.G_FALSE.
- P_INVENTORY_ITEM_ID – Inventory Item Id of the Item
- P_ITEM_NUMBER – Segment1 of the Item
- P_ORGANIZATION_ID – Organization Id of the Organization to whom Item must be assigned
- P_ORGANIZATION_CODE – 3 character Organization Code of the Organization to whom Item must be assigned
- P_PRIMARY_UOM_CODE – Primary Unit of Measure of the item.
- X_RETURN_STATUS – A one-character code indicating whether any errors occurred during processing (in which case error messages will be present on the FND_MSG_PUB package’s message stack). Valid values are FND_API.G_RET_STS_SUCCESS, FND_API.G_RET_STS_ERROR, and FND_API.G_RET_STS_UNEXP_ERROR.
- X_MSG_COUNT – An integer indicating the number of messages on the FND_MSG_PUB package’s message stack at the end of API processing.
DECLARE g_user_id fnd_user.user_id%TYPE :=NULL; l_appl_id fnd_application.application_id%TYPE; l_resp_id fnd_responsibility_tl.responsibility_id%TYPE; l_api_version NUMBER := 1.0; l_init_msg_list VARCHAR2(2) := fnd_api.g_false; l_commit VARCHAR2(2) := FND_API.G_FALSE; x_message_list error_handler.error_tbl_type; x_return_status VARCHAR2(2); x_msg_count NUMBER := 0; BEGIN SELECT fa.application_id INTO l_appl_id FROM fnd_application fa WHERE fa.application_short_name = 'INV'; SELECT fr.responsibility_id INTO l_resp_id FROM fnd_application fa, fnd_responsibility_tl fr WHERE fa.application_short_name = 'INV' AND fa.application_id = fr.application_id AND UPPER (fr.responsibility_name) = 'INVENTORY'; fnd_global.apps_initialize (g_user_id, l_resp_id, l_appl_id); EGO_ITEM_PUB.ASSIGN_ITEM_TO_ORG( P_API_VERSION => l_api_version , P_INIT_MSG_LIST => l_init_msg_list , P_COMMIT => l_commit , P_INVENTORY_ITEM_ID => 1004 , p_item_number => TEST1010 , p_organization_id => 11047 , P_ORGANIZATION_CODE => 'DXN' , P_PRIMARY_UOM_CODE => 'EA' , X_RETURN_STATUS => x_return_status , X_MSG_COUNT => x_msg_count ); DBMS_OUTPUT.PUT_LINE('Status: '||x_return_status); IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN DBMS_OUTPUT.PUT_LINE('Error Messages :'); Error_Handler.GET_MESSAGE_LIST(x_message_list=>x_message_list); FOR j IN 1..x_message_list.COUNT LOOP DBMS_OUTPUT.PUT_LINE(x_message_list(j).message_text); END LOOP; END IF; EXCEPTION WHEN OTHERS THEN dbms_output.put_line('Exception Occured :'); DBMS_OUTPUT.PUT_LINE(SQLCODE ||':'||SQLERRM); END;
Latest Posts
- R12 – How to Handle NULL for :$FLEX$.VALUE_SET_NAME In Oracle ERPAugust 25, 2023 - 1:20 pm
- R12 – How to Delete Oracle AR TransactionsMarch 22, 2019 - 8:37 pm
- How to Define Custom Key Flexfield (KFF) in R12January 19, 2018 - 5:43 pm
- AutoLock Box Concepts In R12November 10, 2017 - 8:30 am
- R12 – java.sql.SQLException: Invalid column type in OAFSeptember 15, 2017 - 9:39 am
Recent Comments