Hi All,
Recently i had a requirement where client had to get rid of one of the repeated activity for assigning Newly created item to any Ship from Warehouse (Organization) to enable it for Sale.
For example ,
Customer XYZ is asking new product from OU "XYZ_OU".
Steps involved here is to Create New SKU (Item segment) and assign to organization from where we have to ship the inventory following by creating a Sales order and then follow the shipping process.
2nd step i.e. Assigning Item to organization was Automated using Business Event.
So whenever any Item is used in any sales order line and that item is not assigned to Ship from Warehouse at line level, we have to automatically Assign item whenever order is booked.
Code to achieve this functionality
CREATE OR REPLACE function APPS.XXZYX_ITEM_ASSIGN_ACCT_ORG(
p_subscription_guid_in IN RAW,
p_event_in_out IN apps.wf_event_t)
RETURN VARCHAR2
IS
pvg_oe_header VARCHAR2 (25) := 'HEADER_ID';
pvg_oe_line VARCHAR2 (25) := 'LINE_ID';
pvg_event_key VARCHAR2 (50);
pvg_parameter_list apps.wf_parameter_list_t;
pvg_user_id_num fnd_user.user_id%TYPE := apps.fnd_global.user_id;
pvg_login_id_num NUMBER := apps.fnd_global.login_id;
pvg_resp_id_num NUMBER := apps.fnd_global.resp_id;
pvg_resp_appl_id_num NUMBER := apps.fnd_global.resp_appl_id;
lv_oe_header_id apps.oe_order_headers_all.headeR_id%type;
lv_org_id NUMBER;
lv_ord_num VARCHAR2(100);
lv_flag VARCHAR2(10);
l_getEventName varchar2(1000);
l_getEventKey varchar2(1000);
l_param_name VARCHAR2(100);
l_param_value varchar2(100);
-------------Item Assignment API----------
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
pvg_parameter_list := p_event_in_out.getparameterlist ();
l_getEventName := p_event_in_out.getEventName ();
l_getEventKey :=p_event_in_out.getEventKey ();
IF pvg_parameter_list IS NOT NULL
THEN
FOR i IN pvg_parameter_list.FIRST .. pvg_parameter_list.LAST
LOOP
IF wf_event.getvalueforparameter ('LINE_ID', pvg_parameter_list) IS NULL
THEN
--if the line id is null, then this event was fired from header level, insert the record into event table
IF (pvg_parameter_list (i).getname () = pvg_oe_header)
THEN
lv_oe_header_id :=
TO_NUMBER (pvg_parameter_list (i).getvalue ());
end if;
BEGIN
fnd_global.apps_initialize (USER_ID, RESP_ID, RESP_APPL_ID);
for i in
(select inventory_item_id from
oe_order_lines_all
where header_id=lv_oe_header_id)
loop
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 => i.inventory_item_id
, p_organization_id => 163-----Ship from warehouse ID
, 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;
end loop;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Exception Occured :');
DBMS_OUTPUT.PUT_LINE(SQLCODE ||':'||SQLERRM);
END;
--
l_param_name := pvg_parameter_list (i).getname;
l_param_value := pvg_parameter_list (i).getvalue;
--
INSERT INTO xx_be_debug_log_tmp
(text
)
VALUES (l_param_name || ' : ' || l_param_value);
COMMIT;
END IF;
--
END LOOP;
END IF;
COMMIT;
RETURN 'SUCCESS';
EXCEPTION
WHEN OTHERS
THEN
RETURN 'SUCCESS';
END;
/
Then This function needs to be add as Subscription on Sales order booking event i.e.
oracle.apps.ont.oip.statuschange.update.
Once Subscription is done properly, Test your case.
Recently i had a requirement where client had to get rid of one of the repeated activity for assigning Newly created item to any Ship from Warehouse (Organization) to enable it for Sale.
For example ,
Customer XYZ is asking new product from OU "XYZ_OU".
Steps involved here is to Create New SKU (Item segment) and assign to organization from where we have to ship the inventory following by creating a Sales order and then follow the shipping process.
2nd step i.e. Assigning Item to organization was Automated using Business Event.
So whenever any Item is used in any sales order line and that item is not assigned to Ship from Warehouse at line level, we have to automatically Assign item whenever order is booked.
Code to achieve this functionality
CREATE OR REPLACE function APPS.XXZYX_ITEM_ASSIGN_ACCT_ORG(
p_subscription_guid_in IN RAW,
p_event_in_out IN apps.wf_event_t)
RETURN VARCHAR2
IS
pvg_oe_header VARCHAR2 (25) := 'HEADER_ID';
pvg_oe_line VARCHAR2 (25) := 'LINE_ID';
pvg_event_key VARCHAR2 (50);
pvg_parameter_list apps.wf_parameter_list_t;
pvg_user_id_num fnd_user.user_id%TYPE := apps.fnd_global.user_id;
pvg_login_id_num NUMBER := apps.fnd_global.login_id;
pvg_resp_id_num NUMBER := apps.fnd_global.resp_id;
pvg_resp_appl_id_num NUMBER := apps.fnd_global.resp_appl_id;
lv_oe_header_id apps.oe_order_headers_all.headeR_id%type;
lv_org_id NUMBER;
lv_ord_num VARCHAR2(100);
lv_flag VARCHAR2(10);
l_getEventName varchar2(1000);
l_getEventKey varchar2(1000);
l_param_name VARCHAR2(100);
l_param_value varchar2(100);
-------------Item Assignment API----------
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
pvg_parameter_list := p_event_in_out.getparameterlist ();
l_getEventName := p_event_in_out.getEventName ();
l_getEventKey :=p_event_in_out.getEventKey ();
IF pvg_parameter_list IS NOT NULL
THEN
FOR i IN pvg_parameter_list.FIRST .. pvg_parameter_list.LAST
LOOP
IF wf_event.getvalueforparameter ('LINE_ID', pvg_parameter_list) IS NULL
THEN
--if the line id is null, then this event was fired from header level, insert the record into event table
IF (pvg_parameter_list (i).getname () = pvg_oe_header)
THEN
lv_oe_header_id :=
TO_NUMBER (pvg_parameter_list (i).getvalue ());
end if;
BEGIN
fnd_global.apps_initialize (USER_ID, RESP_ID, RESP_APPL_ID);
for i in
(select inventory_item_id from
oe_order_lines_all
where header_id=lv_oe_header_id)
loop
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 => i.inventory_item_id
, p_organization_id => 163-----Ship from warehouse ID
, 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;
end loop;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Exception Occured :');
DBMS_OUTPUT.PUT_LINE(SQLCODE ||':'||SQLERRM);
END;
--
l_param_name := pvg_parameter_list (i).getname;
l_param_value := pvg_parameter_list (i).getvalue;
--
INSERT INTO xx_be_debug_log_tmp
(text
)
VALUES (l_param_name || ' : ' || l_param_value);
COMMIT;
END IF;
--
END LOOP;
END IF;
COMMIT;
RETURN 'SUCCESS';
EXCEPTION
WHEN OTHERS
THEN
RETURN 'SUCCESS';
END;
/
Then This function needs to be add as Subscription on Sales order booking event i.e.
oracle.apps.ont.oip.statuschange.update.
Once Subscription is done properly, Test your case.
No comments:
Post a Comment