I am attempting to configure a custom event that cancels customer order lines on an order when that order enters Delivered or Partially Delivered status.
To that end I have created a custom event that triggers correctly:
The custom event action contains the code below, but the first issue I’m ran into is that the Customer_Order_Line_Api.Set_Cancelled method throws an error when attempting to run it on Reserved lines:
The above error led me to believe that I needed to find an API method to unreserve customer order lines before trying to cancel them. That led me to the Customer_Order_Reservation.Remove method, which sets the reserved qty to zero but doesn’t change the status of the line to something other than ‘Reserved’.
So at this point I’m stuck trying to find a way to accomplish the unreserve process via custom event. If anyone has any insight on how to correct my event, or if there is an accepted alternative way to accomplish my overall goal, I would much appreciate any insight.
Thank you
Scott
The sql in the custom event action is as follows:
DECLARE
attr_ VARCHAR2(32000);
sql_msg_ VARCHAR2(32000);
stmt_ VARCHAR2(32000);
BEGIN
stmt_ := 'DECLARE
-- Customer Order info
rowstate_ VARCHAR2(200) := ''&NEW:ROWSTATE'';
order_no_ VARCHAR2(200) := ''&NEW:ORDER_NO'';
-- Customer Order Line info
objstate_ VARCHAR2(200);
line_no_ VARCHAR2(200);
rel_no_ VARCHAR2(200);
line_item_no_ NUMBER;
contract_ VARCHAR2(200);
part_no_ VARCHAR2(200);
location_no_ VARCHAR2(200);
configuration_id_ VARCHAR2(200);
-- Customer Order Reservation info
lot_batch_no_ VARCHAR2(200);
serial_no_ VARCHAR2(200);
eng_chg_level_ VARCHAR2(200);
waiv_dev_rej_no_ VARCHAR2(200);
activity_seq_ NUMBER;
handling_unit_id_ NUMBER;
shipment_id_ NUMBER;
reassignment_type_ VARCHAR2(200);
move_to_ship_location_ VARCHAR2(200);
CURSOR get_open_lines IS
select objstate, line_no, rel_no, line_item_no, contract, part_no, location_no, configuration_id from customer_order_line where order_no = order_no_ and objstate NOT IN (''Delivered'',''Cancelled'',''PartiallyDelivered'');
CURSOR get_order_res_info IS
select location_no, lot_batch_no, serial_no, eng_chg_level, waiv_dev_rej_no, activity_seq, handling_unit_id, shipment_id, reassignment_type, move_to_ship_location from customer_order_reservation where order_no = order_no_ and line_no = line_no_ and rel_no = rel_no_ and contract = contract_ and part_no = part_no_ and configuration_id = configuration_id_ and pick_list_no = ''*'';
BEGIN
IF rowstate_ IN (''Delivered'',''PartiallyDelivered'') THEN
OPEN get_open_lines;
-- Loop through all the Customer Order Lines returned by the cursor
LOOP
FETCH get_open_lines INTO objstate_, line_no_, rel_no_, line_item_no_, contract_, part_no_, location_no_, configuration_id_;
EXIT when get_open_lines%NOTFOUND;
-- Unreserve the CO Line if it is in a reserved state
IF objstate_ = ''Reserved'' THEN
-- Fetch some Customer Order Reservation information if the line is in a reserved state
OPEN get_order_res_info;
FETCH get_order_res_info INTO location_no_, lot_batch_no_, serial_no_, eng_chg_level_, waiv_dev_rej_no_, activity_seq_, handling_unit_id_, shipment_id_, reassignment_type_, move_to_ship_location_;
CLOSE get_order_res_info;
-- API method to unreserve the customer order line
CUSTOMER_ORDER_RESERVATION_API.Remove(order_no_, line_no_, rel_no_, line_item_no_, contract_, part_no_, location_no_, lot_batch_no_, serial_no_, eng_chg_level_, waiv_dev_rej_no_, activity_seq_, handling_unit_id_, ''*'', configuration_id_, shipment_id_, reassignment_type_, move_to_ship_location_);
END IF;
-- API method to set lines as cancelled and then set a cancel reason
CUSTOMER_ORDER_LINE_API.Set_Cancelled(order_no_, line_no_, rel_no_, line_item_no_);
CUSTOMER_ORDER_LINE_API.Set_Cancel_Reason(order_no_, line_no_, rel_no_, line_item_no_, ''LOST SALE'');
END LOOP;
CLOSE get_open_lines;
END IF;
END;';
sql_msg_ := Message_SYS.Construct('Auto Cancel CO Lines');
Message_SYS.Add_Attribute(sql_msg_, 'SQL', stmt_);
Client_SYS.Clear_Attr(attr_);
Client_SYS.Add_To_Attr('SQL_DATA_', sql_msg_, attr_);
Client_SYS.Add_To_Attr('MSG_', '', attr_);
Transaction_SYS.Deferred_Call('Fnd_Event_Action_API.Action_Executeonlinesql',
'PARAMETER',
attr_,
'Auto Cancel CO Lines');
END;
