CREATE OR REPLACE PACKAGE BODY cor_gui AS FUNCTION get_version RETURN VARCHAR2 AS BEGIN RETURN '24.00.01'; END; PROCEDURE check_order_lock ( in_custorder_id IN VARCHAR2, in_owner IN VARCHAR2, out_lock_status OUT VARCHAR2, out_return_code OUT VARCHAR2, out_return_desc OUT VARCHAR2 ) AS ex_cor_gui EXCEPTION; l_rc INT; l_locked_status VARCHAR(30); BEGIN -- checks the status of the order lock --------------------------------------- cor_order_locking.check_custorder_ownership(in_custorder_id, in_owner, out_return_code, out_return_desc); CASE out_return_code WHEN '0' THEN SELECT COUNT(*) INTO l_rc FROM customer_order co, line l, suborder s WHERE co.custorder_id = l.custorder_id AND co.custorder_id = s.custorder_id -- AND (co.ownership IS NOT NULL OR l.ownership IS NOT NULL OR s.ownership IS NOT NULL) -- AND co.custorder_id = in_custorder_id; IF l_rc = 0 THEN l_locked_status := 'UNLOCKED'; ELSE l_locked_status := 'LOCKED_BY_ME'; END IF; WHEN '5040' THEN -- cor_order_locking.check_custorder_ownership(in_custorder_id, 'Delivery Layer', out_return_code, out_return_desc); IF out_return_code = '5040' THEN cor_order_locking.check_custorder_ownership(in_custorder_id, 'Order Validation', out_return_code, out_return_desc); IF out_return_code = '5040' THEN l_locked_status := 'LOCKED_BY_OTHER'; ELSE l_locked_status := 'LOCKED_BY_ORDER_VALIDATION'; END IF; ELSE l_locked_status := 'LOCKED_BY_DELIVERY_LAYER'; END IF; -- WHEN '5049' THEN RAISE ex_cor_gui; END CASE; -- set return values ----------------------------------------------------------- out_lock_status := l_locked_status; out_return_code := 0; out_return_desc := 'OK'; -------------------------------------------------------------------------------- EXCEPTION WHEN ex_cor_gui THEN NULL; WHEN OTHERS THEN out_return_code := '9069'; out_return_desc := 'An error occurred during execution of stored procedure CHECK_ORDER_LOCK' || ' SQL error code: ' || SQLCODE || '.' || ' SQL error message: ' || SQLERRM; END check_order_lock; -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- PROCEDURE check_order_treatment ( in_custorder_id IN VARCHAR2, out_treating OUT VARCHAR2, out_return_code OUT VARCHAR2, out_return_desc OUT VARCHAR2 ) AS BEGIN -- checks whether the order is currently being treated by an agent ------------- out_treating := 'false'; SELECT treating INTO out_treating FROM customer_order WHERE custorder_id = in_custorder_id; -- set return values ----------------------------------------------------------- out_return_code := 0; out_return_desc := 'OK'; -------------------------------------------------------------------------------- EXCEPTION WHEN OTHERS THEN out_return_code := '9059'; out_return_desc := 'An error occurred during execution of stored procedure CHECK_ORDER_TREATMENT' || ' SQL error code: ' || SQLCODE || '.' || ' SQL error message: ' || SQLERRM; END check_order_treatment; -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- PROCEDURE log_action_history ( in_custorder_id IN VARCHAR2, in_suborder_id IN VARCHAR2, -- in_owner IN VARCHAR2, in_action_hist_desc IN VARCHAR2, out_gui_action_hist_id OUT NUMBER, -- out_return_code OUT VARCHAR2, out_return_desc OUT VARCHAR2 ) AS BEGIN SELECT seq_action_hist_id.NEXTVAL INTO out_gui_action_hist_id FROM dual; -- INSERT INTO gui_action_history (custorder_id, suborder_id, -- action_hist_id, action_hist_desc, action_dttm, action_user) VALUES (in_custorder_id, in_suborder_id, -- out_gui_action_hist_id, -- in_action_hist_desc, SYSDATE, in_owner); -- set return values ----------------------------------------------------------- out_return_code := 0; out_return_desc := 'OK'; -------------------------------------------------------------------------------- EXCEPTION WHEN OTHERS THEN out_return_code := '9019'; out_return_desc := 'An error occurred during execution of stored procedure LOG_ACTION_HISTORY' || ' SQL error code: ' || SQLCODE || '.' || ' SQL error message: ' || SQLERRM; END log_action_history; -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- PROCEDURE action_lock_orders ( in_custorder_ids IN custorder_id_cursor, in_owner IN VARCHAR2, in_member_groups IN user_group_cursor, out_return_code OUT VARCHAR2, out_return_desc OUT VARCHAR2 ) AS ex_cor_gui EXCEPTION; l_custorder_id customer_order.custorder_id%TYPE; l_out_gui_action_hist_id gui_action_history.action_hist_id%TYPE; -- BEGIN FOR i IN in_custorder_ids.FIRST .. in_custorder_ids.LAST LOOP -- lock order ------------------------------------------------------------------ l_custorder_id := in_custorder_ids(i); -- -- authorize action ----------------------------------------------------------- cor_gui.authorize_action(in_custorder_ids(i), 'LOCK_ORDERS', in_owner, in_member_groups, out_return_code, out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; ---- -- lock order ------------------------------------------------------------------ l_custorder_id := in_custorder_ids(i); cor_order_locking.take_custorder_ownership(in_custorder_ids(i), in_owner, 'N', out_return_code, out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; -- log the action in the action history ---------------------------------------- cor_gui.log_action_history(in_custorder_ids(i), NULL, in_owner, 'Locked Order', l_out_gui_action_hist_id, -- out_return_code, out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; END LOOP; -- set return values ----------------------------------------------------------- out_return_code := 0; out_return_desc := 'OK'; -------------------------------------------------------------------------------- EXCEPTION WHEN ex_cor_gui THEN IF out_return_code = '5020' THEN out_return_code := '9020'; out_return_desc := 'Ownership error: Customer Order ' || l_custorder_id || ' or one of its Order Lines owned by somebody else.'; ELSE NULL; END IF; WHEN OTHERS THEN out_return_code := '9029'; out_return_desc := 'An error occurred during execution of stored procedure ACTION_LOCK_ORDERS' || ' SQL error code: ' || SQLCODE || '.' || ' SQL error message: ' || SQLERRM; END action_lock_orders; -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- PROCEDURE action_unlock_orders ( in_custorder_ids IN custorder_id_cursor, in_owner IN VARCHAR2, in_member_groups IN user_group_cursor, out_return_code OUT VARCHAR2, out_return_desc OUT VARCHAR2 ) AS ex_cor_gui EXCEPTION; ex_order_locked EXCEPTION; ex_order_unlocked EXCEPTION; l_custorder_id customer_order.custorder_id%TYPE; l_profile enum_user_group_profile.rank%TYPE; l_out_gui_action_hist_id gui_action_history.action_hist_id%TYPE; -- l_cancel_profile VARCHAR(1); -- BEGIN FOR i IN in_custorder_ids.FIRST .. in_custorder_ids.LAST LOOP l_custorder_id := in_custorder_ids(i); -- -- authorize action ----------------------------------------------------------- cor_gui.authorize_action(in_custorder_ids(i), 'UNLOCK_ORDERS', in_owner, in_member_groups, out_return_code, out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; -- get profile rank ----------------------------------------------------------- cor_gui.check_profile_rank(in_member_groups, l_profile, l_cancel_profile, out_return_code, out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; --IF OUT_PROFILE is in ('Admin', 'Superuser') force release----------------- cor_order_locking.release_order_ownership_gui(in_custorder_ids(i), in_owner, CASE l_profile WHEN 1 THEN 'Y' WHEN 2 THEN 'Y' ELSE 'N' END, out_return_code, out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; -- -- in_owner, 'Unlocked Order', l_out_gui_action_hist_id, -- out_return_code, out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; END LOOP; -- set return values ----------------------------------------------------------- out_return_code := 0; out_return_desc := 'OK'; -------------------------------------------------------------------------------- EXCEPTION WHEN ex_order_locked THEN out_return_code := '9030'; out_return_desc := 'Order ' || l_custorder_id || ' is locked by someone else.'; WHEN ex_order_unlocked THEN out_return_code := '9031'; out_return_desc := 'Order ' || l_custorder_id || ' is already unlocked.'; WHEN ex_cor_gui THEN NULL; WHEN OTHERS THEN out_return_code := '9039'; out_return_desc := 'An error occurred during execution of stored procedure ACTION_UNLOCK_ORDERS' || ' SQL error code: ' || SQLCODE || '.' || ' SQL error message: ' || SQLERRM; END action_unlock_orders; -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- PROCEDURE action_treat_orders_start ( in_custorder_ids IN custorder_id_cursor, in_owner IN VARCHAR2, in_member_groups IN user_group_cursor, out_return_code OUT VARCHAR2, out_return_desc OUT VARCHAR2 ) AS ex_cor_gui EXCEPTION; ex_order_locked EXCEPTION; ex_order_unlocked EXCEPTION; l_custorder_id customer_order.custorder_id%TYPE; l_out_gui_action_hist_id gui_action_history.action_hist_id%TYPE; -- l_suborder_ids suborder_id_cursor; -- BEGIN FOR i IN in_custorder_ids.FIRST .. in_custorder_ids.LAST LOOP l_custorder_id := in_custorder_ids(i); -- -- authorize action ----------------------------------------------------------- cor_gui.authorize_action(in_custorder_ids(i), 'TREAT_ORDERS_START', in_owner, in_member_groups, out_return_code, out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; -- -- l_suborder_ids := cor_gui.get_suborder_ids(in_custorder_id => in_custorder_ids(i)); cor_gui.action_treat_suborders_start(in_suborder_ids => l_suborder_ids, in_owner => in_owner, in_member_groups => in_member_groups, out_return_code => out_return_code, out_return_desc => out_return_desc); -- /*IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF;*/ -- -- -- Mark order for treatment ---------------------------------------------------- UPDATE customer_order SET treating = 'true' WHERE custorder_id = in_custorder_ids(i); -- log the action in the action history ---------------------------------------- cor_gui.log_action_history(in_custorder_ids(i), NULL, in_owner, 'Order treatment started', l_out_gui_action_hist_id, -- out_return_code, out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; END LOOP; -- set return values ----------------------------------------------------------- out_return_code := 0; out_return_desc := 'OK'; -------------------------------------------------------------------------------- EXCEPTION WHEN ex_order_locked THEN out_return_code := '9040'; out_return_desc := 'Order ' || l_custorder_id || ' is locked by someone else.'; WHEN ex_order_unlocked THEN out_return_code := '9041'; out_return_desc := 'Order ' || l_custorder_id || ' must first be locked.'; WHEN ex_cor_gui THEN NULL; WHEN OTHERS THEN out_return_code := '9049'; out_return_desc := 'An error occurred during execution of stored procedure ACTION_TREAT_ORDERS_START' || ' SQL error code: ' || SQLCODE || '.' || ' SQL error message: ' || SQLERRM; END action_treat_orders_start; -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- PROCEDURE treat_orders_stop ( in_custorder_id IN VARCHAR2, in_owner IN VARCHAR2, in_log_hist IN VARCHAR2, out_return_code OUT VARCHAR2, out_return_desc OUT VARCHAR2 ) AS ex_cor_gui EXCEPTION; l_treatment customer_order.treating%TYPE; l_out_gui_action_hist_id gui_action_history.action_hist_id%TYPE; -- BEGIN -- FOR i IN (SELECT s.suborder_id FROM suborder s WHERE s.custorder_id = in_custorder_id) LOOP cor_gui.treat_suborders_stop(in_suborder_id => i.suborder_id, in_owner => in_owner, in_log_hist => in_log_hist, out_return_code => out_return_code, out_return_desc => out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; END LOOP; -- -- check order treatment ------------------------------------------------------- cor_gui.check_order_treatment(in_custorder_id, l_treatment, out_return_code, out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; IF l_treatment = 'true' THEN -- Unmark order for treatment ---------------------------------------------------- UPDATE customer_order SET treating = 'false' WHERE custorder_id = in_custorder_id; -- log the action in the action history ---------------------------------------- IF in_log_hist = 'Y' THEN cor_gui.log_action_history(in_custorder_id, NULL, in_owner, 'Order treatment ended', l_out_gui_action_hist_id, -- out_return_code, out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; END IF; END IF; -- set return values ----------------------------------------------------------- out_return_code := 0; out_return_desc := 'OK'; -------------------------------------------------------------------------------- EXCEPTION WHEN ex_cor_gui THEN NULL; WHEN OTHERS THEN out_return_code := '9079'; out_return_desc := 'An error occurred during execution of stored procedure TREAT_ORDERS_STOP' || ' SQL error code: ' || SQLCODE || '.' || ' SQL error message: ' || SQLERRM; END treat_orders_stop; -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- PROCEDURE authorize_action ( in_custorder_id IN VARCHAR2, in_action IN VARCHAR2, in_owner IN VARCHAR2, in_member_groups IN user_group_cursor, out_return_code OUT VARCHAR2, out_return_desc OUT VARCHAR2 ) AS ex_cor_gui EXCEPTION; ex_order_locked EXCEPTION; ex_order_unlocked EXCEPTION; ex_group_not_found EXCEPTION; ex_treatment EXCEPTION; ex_illegal_status EXCEPTION; ex_error_code EXCEPTION; ex_unknown_action EXCEPTION; ex_department_not_assigned EXCEPTION; ex_delivery_validation_lock EXCEPTION; l_rc INT; l_groups INT := 0; l_lock_status VARCHAR(30); l_treatment customer_order.treating%TYPE; l_error_code error_message.ERROR_CODE%TYPE; l_profile enum_user_group_profile.rank%TYPE; l_department_found BOOLEAN := FALSE; l_action INT; l_cancel_profile VARCHAR(1); -- BEGIN -- check gui_action ----------------------------------------------------------- SELECT COUNT(*) INTO l_rc FROM gui_actions WHERE action_label = in_action; IF l_rc = 0 THEN RAISE ex_unknown_action; END IF; -- get profile rank ----------------------------------------------------------- cor_gui.check_profile_rank(in_member_groups, l_profile, l_cancel_profile, out_return_code, out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; IF l_profile != 1 THEN -- check user department ------------------------------------------------------ FOR i IN in_member_groups.FIRST .. in_member_groups.LAST LOOP SELECT COUNT(*) INTO l_rc FROM gui_user_groups WHERE group_id = in_member_groups(i) AND in_member_groups(i) = (SELECT assigned_user_group_id FROM customer_order WHERE custorder_id = in_custorder_id); IF l_rc != 0 THEN l_department_found := TRUE; EXIT; END IF; END LOOP; -- IF in_action IN ('TREAT_ORDERS_START', 'TREAT_ORDERS_STOP', 'LOCK_ORDERS', 'UNLOCK_ORDERS', 'CANCEL_ORDER') AND l_cancel_profile = 'Y' THEN NULL; ELSIF l_department_found = FALSE THEN RAISE ex_department_not_assigned; END IF; -- IF in_action = 'UNLOCK_ORDERS' THEN -- check order lock ------------------------------------------------------------ cor_gui.check_order_lock(in_custorder_id, in_owner, l_lock_status, out_return_code, out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; IF l_profile = 2 THEN CASE l_lock_status WHEN 'LOCKED_BY_DELIVERY_LAYER' THEN RAISE ex_delivery_validation_lock; WHEN 'LOCKED_BY_ORDER_VALIDATION' THEN RAISE ex_delivery_validation_lock; WHEN 'UNLOCKED' THEN RAISE ex_order_unlocked; ELSE NULL; END CASE;ELSE CASE l_lock_status WHEN 'LOCKED_BY_OTHER' THEN RAISE ex_order_locked; WHEN 'LOCKED_BY_DELIVERY_LAYER' THEN RAISE ex_delivery_validation_lock; WHEN 'LOCKED_BY_ORDER_VALIDATION' THEN RAISE ex_delivery_validation_lock; WHEN 'UNLOCKED' THEN RAISE ex_order_unlocked; ELSE NULL; END CASE; END IF; END IF; END IF; --l_profile != 1 SELECT decode(in_action, 'LOCK_ORDERS', 4, 'UNLOCK_ORDERS', 3, 'TREAT_ORDERS_START', 1, 'TREAT_ORDERS_STOP', 1, 'EXPORT_LIST', 3, 'RESUBMIT_ORDER_WO_VAL', 2, 'RESUBMIT_ORDER', 2, 'CANCEL_ORDER', 2, 'INSPECT_ORDER', 2, 'REJECT_ORDER', 2, 'MANUALLY_DONE', 2, 'REASSIGN_ORDER', 1, 'SET_DUE_DATE', 3, 'SAVE_NOTE', 3) INTO l_action FROM dual; IF (l_action = 1 OR l_action = 2) THEN -- check order lock ------------------------------------------------------------ cor_gui.check_order_lock(in_custorder_id, in_owner, l_lock_status, out_return_code, out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; CASE l_lock_status WHEN 'LOCKED_BY_OTHER' THEN RAISE ex_order_locked; WHEN 'LOCKED_BY_DELIVERY_LAYER' THEN RAISE ex_delivery_validation_lock; WHEN 'LOCKED_BY_ORDER_VALIDATION' THEN RAISE ex_delivery_validation_lock; WHEN 'UNLOCKED' THEN RAISE ex_order_unlocked; ELSE NULL; END CASE; END IF; IF l_action = 2 THEN -- check order treatment ------------------------------------------------------- cor_gui.check_order_treatment(in_custorder_id, l_treatment, out_return_code, out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; IF l_treatment != 'true' THEN RAISE ex_treatment; END IF; END IF; -- check if status is legal ---------------------------------------------------- SELECT COUNT(*) INTO l_rc FROM gui_action_show_status WHERE action_id IN (SELECT action_id FROM gui_actions WHERE action_label = in_action) AND status_id IN (SELECT status_internal FROM customer_order WHERE custorder_id = in_custorder_id); IF l_rc = 0 THEN RAISE ex_illegal_status; END IF; -- check if none of the user groups the user belongs to is configured for requested action FOR i IN in_member_groups.FIRST .. in_member_groups.LAST LOOP SELECT COUNT(*) INTO l_rc FROM gui_action_show_user_group WHERE group_id = in_member_groups(i); l_groups := l_groups + l_rc; END LOOP; IF l_groups = 0 THEN RAISE ex_group_not_found; END IF; -- check error code ------------------------------------------------------------ BEGIN SELECT ERROR_CODE INTO l_error_code FROM gui_action_hide_error WHERE action_id IN (SELECT action_id FROM gui_actions WHERE action_label = in_action) AND ERROR_CODE IN (SELECT ERROR_CODE FROM error_message WHERE custorder_id = in_custorder_id AND deleted = 'N') AND rownum = 1; RAISE ex_error_code; EXCEPTION WHEN no_data_found THEN NULL; END; -- check action type ----------------------------------------------------------- SELECT COUNT(*) INTO l_rc FROM gui_actions WHERE action_label = in_action; IF l_rc = 0 THEN RAISE ex_unknown_action; END IF; -- set return values ----------------------------------------------------------- out_return_code := 0; out_return_desc := 'OK'; -------------------------------------------------------------------------------- EXCEPTION WHEN ex_delivery_validation_lock THEN out_return_code := '9088'; out_return_desc := 'Order ' || in_custorder_id || ' is locked by the delivery layer/order validation'; WHEN ex_department_not_assigned THEN out_return_code := '9087'; out_return_desc := 'Order ' || in_custorder_id || ' is not assigned to your department'; WHEN ex_unknown_action THEN out_return_code := '9086'; out_return_desc := 'Unknown action type ' || in_action || '.'; WHEN ex_error_code THEN out_return_code := '9085'; out_return_desc := 'The error ' || l_error_code || ' does not allow to perform the requested action.'; WHEN ex_illegal_status THEN out_return_code := '9084'; out_return_desc := 'Order ' || in_custorder_id || ' is in illegal status for requested action.'; WHEN ex_treatment THEN out_return_code := '9083'; out_return_desc := 'Order ' || in_custorder_id || ' must be marked for treatment in order to perform requested action.'; WHEN ex_group_not_found THEN out_return_code := '9082'; out_return_desc := 'You are not allowed to perform the requested action.'; WHEN ex_order_locked THEN out_return_code := '9080'; out_return_desc := 'Order ' || in_custorder_id || ' is locked by someone else.'; WHEN ex_order_unlocked THEN out_return_code := '9081'; out_return_desc := 'Order ' || in_custorder_id || ' must first be locked.'; WHEN ex_cor_gui THEN NULL; WHEN OTHERS THEN out_return_code := '9089'; out_return_desc := 'An error occurred during execution of stored procedure AUTHORIZE_ACTION' || ' SQL error code: ' || SQLCODE || '.' || ' SQL error message: ' || SQLERRM; END authorize_action; -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- PROCEDURE action_resubmit_order_wo_val ( in_custorder_ids IN custorder_id_cursor, in_owner IN VARCHAR2, in_member_groups IN user_group_cursor, out_return_code OUT VARCHAR2, out_return_desc OUT VARCHAR2 ) AS ex_cor_gui EXCEPTION; l_out_gui_action_hist_id gui_action_history.action_hist_id%TYPE; -- l_attribute_empty_cursor attribute_array;-- l_status_internal suborder.status_internal%TYPE;--23.01.12 l_custorder_type_id customer_order.custorder_type_id%TYPE; -- BEGIN -- create empty attribute cursor - is used on mor places l_attribute_empty_cursor:= attribute_array(); -- FOR i IN in_custorder_ids.FIRST .. in_custorder_ids.LAST LOOP --authorize action ------------------------------------------------------------- cor_gui.authorize_action(in_custorder_ids(i), 'RESUBMIT_ORDER_WO_VAL', in_owner, in_member_groups, out_return_code, out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; -- update status to 'Accepted' ------------------------------------------------- cor_order_update.set_customer_order_status(in_custorder_ids(i), NULL, 'N', NULL, -- 'In Process', 'Accepted', l_attribute_empty_cursor,-- out_return_code, out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; -- clear error message when 'Accepted' ------------------------------------------------- cor_order_update.clear_header_error_messages(in_custorder_ids(i), out_return_code, out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; -- log the action in the action history ---------------------------------------- cor_gui.log_action_history(in_custorder_ids(i), NULL, -- in_owner, 'Order resubmitted without validation', l_out_gui_action_hist_id, -- out_return_code, out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; -- FOR m IN (SELECT suborder_id FROM suborder WHERE custorder_id = in_custorder_ids(i)) LOOP --23.01.12 SELECT s.status_internal INTO l_status_internal FROM suborder s WHERE s.suborder_id = m.suborder_id; -- SELECT co.custorder_type_id INTO l_custorder_type_id FROM customer_order co WHERE co.custorder_id = in_custorder_ids(i); -- IF (l_status_internal IN ( 'Awaiting Agent', 'Order Validation','Suborder Validation') AND (l_custorder_type_id != 'Combined Order' or l_custorder_type_id IS NULL)) THEN -- --23.01.12 --FOR EACH SUBORDER with CUSTORDER_ID from input, Cal the SP SET_SUBORDER_STATUS(IN_INTERNAL_STATUS = �Accepted�) cor_order_update.set_suborder_status(in_suborder_id => m.suborder_id, in_modifier => NULL, in_force => 'N', in_internal_status => 'Accepted', in_external_status => NULL,-- in_attribute_cursor => l_attribute_empty_cursor,-- out_return_code => out_return_code, out_return_desc => out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; -- select suborder where treating = true FOR n IN (SELECT l.order_line_id FROM line l WHERE l.suborder_id = m.suborder_id) LOOP -- FOR EACH LINE with SUBORDER_ID treated -- Call the SP SET_CUSTOMER_ORDER_LINE_STATUS(IN_INTERNAL_STATUS = �Accepted�) cor_order_update.set_customer_order_line_status(in_custorder_id => in_custorder_ids(i), in_suborder_id => m.suborder_id, in_line_id => n.order_line_id, in_modifier => NULL, in_force => 'N', in_public_status => 'In Process', in_internal_status => 'Accepted', in_external_status => NULL, in_attribute_cursor => l_attribute_empty_cursor,-- out_return_code => out_return_code, out_return_desc => out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; END LOOP; --23.01.12 END IF; --23.01.12 END LOOP; -- -- unlock orders --------------------------------------------------------------- cor_gui.action_unlock_orders_id(in_custorder_ids(i), in_owner, in_member_groups, out_return_code, out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; END LOOP; -- set return values ----------------------------------------------------------- out_return_code := 0; out_return_desc := 'OK'; -------------------------------------------------------------------------------- EXCEPTION WHEN ex_cor_gui THEN NULL; WHEN OTHERS THEN out_return_code := '9099'; out_return_desc := 'An error occurred during execution of stored procedure ACTION_RESUBMIT_ORDER_WO_VAL' || ' SQL error code: ' || SQLCODE || '.' || ' SQL error message: ' || SQLERRM; END action_resubmit_order_wo_val; -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- PROCEDURE action_resubmit_order ( in_custorder_ids IN custorder_id_cursor, in_owner IN VARCHAR2, in_member_groups IN user_group_cursor, out_return_code OUT VARCHAR2, out_return_desc OUT VARCHAR2 ) AS ex_cor_gui EXCEPTION; ex_combined_order EXCEPTION; -- l_out_gui_action_hist_id gui_action_history.action_hist_id%TYPE; -- l_attribute_empty_cursor attribute_array;-- l_in_member_groups_unlock user_group_cursor; --23.01.04 l_assigned_user_group_id customer_order.assigned_user_group_id%TYPE; --23.01.04 l_tmp_cnt NUMBER;--23.01.04 l_countsubvaloraccstatus NUMBER; -- l_custorder_type_id customer_order.custorder_type_id%TYPE; -- BEGIN -- create empty attribute cursor - is used on mor places l_attribute_empty_cursor:= attribute_array(); -- FOR i IN in_custorder_ids.FIRST .. in_custorder_ids.LAST LOOP --authorize action ------------------------------------------------------------- cor_gui.authorize_action(in_custorder_ids(i), 'RESUBMIT_ORDER', in_owner, in_member_groups, out_return_code, out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; -- SELECT custorder_type_id INTO l_custorder_type_id FROM customer_order co WHERE co.custorder_id = in_custorder_ids(i); IF (l_custorder_type_id = 'Combined Order') THEN SELECT COUNT(1) INTO l_countsubvaloraccstatus FROM status_history WHERE (custorder_id = in_custorder_ids(i) AND (status_internal = 'Suborder Validation' OR status_internal = 'Accepted')); IF (l_countsubvaloraccstatus > 0) THEN RAISE ex_combined_order; END IF; END IF; -- -- -- update status to 'To Validation' ------------------------------------------------- cor_order_update.set_customer_order_status(in_custorder_ids(i), NULL, 'N', NULL, -- 'Acceptance', 'To Validation', l_attribute_empty_cursor,-- out_return_code, out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; -- log the action in the action history ---------------------------------------- cor_gui.log_action_history(in_custorder_ids(i), NULL, -- in_owner, 'Order resubmitted', l_out_gui_action_hist_id, -- out_return_code, out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; -- unlock orders --------------------------------------------------------------- --extend memeber groups with the current assigned department of the request (assigned_user_group_id in customer_order table). --23.01.04 l_in_member_groups_unlock := in_member_groups; select assigned_user_group_id into l_assigned_user_group_id from customer_order where custorder_id = in_custorder_ids(i); select count(1) into l_tmp_cnt from TABLE(l_in_member_groups_unlock) where column_value = l_assigned_user_group_id; IF (l_tmp_cnt = 0) THEN l_in_member_groups_unlock.extend; l_in_member_groups_unlock(l_in_member_groups_unlock.LAST) := l_assigned_user_group_id; END IF; --23.01.04 cor_gui.action_unlock_orders_id(in_custorder_ids(i), in_owner, l_in_member_groups_unlock,--23.01.04 out_return_code, out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; END LOOP; -- set return values ----------------------------------------------------------- out_return_code := 0; out_return_desc := 'OK'; -------------------------------------------------------------------------------- EXCEPTION WHEN ex_cor_gui THEN NULL; -- WHEN ex_combined_order THEN out_return_code := '9110'; out_return_desc := 'After the order validation, the resubmit action should be done on suborder level for the combined order (on not on order level). If you want to resubmit a suborder, click the button resubmit in the provisioning view'; -- WHEN OTHERS THEN out_return_code := '9109'; out_return_desc := 'An error occurred during execution of stored procedure ACTION_RESUBMIT_ORDER' || ' SQL error code: ' || SQLCODE || '.' || ' SQL error message: ' || SQLERRM; END action_resubmit_order; -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- PROCEDURE action_cancel_order ( in_custorder_ids IN custorder_id_cursor, in_owner IN VARCHAR2, in_member_groups IN user_group_cursor, out_return_code OUT VARCHAR2, out_return_desc OUT VARCHAR2 ) AS ex_cor_gui EXCEPTION; l_out_gui_action_hist_id gui_action_history.action_hist_id%TYPE; -- l_attribute_empty_cursor attribute_array;-- BEGIN -- create empty attribute cursor - is used on mor places l_attribute_empty_cursor:= attribute_array(); -- FOR i IN in_custorder_ids.FIRST .. in_custorder_ids.LAST LOOP --authorize action ------------------------------------------------------------- cor_gui.authorize_action(in_custorder_ids(i), 'CANCEL_ORDER', in_owner, in_member_groups, out_return_code, out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; -- update status to 'Canceled' ------------------------------------------------- cor_order_update.set_customer_order_status(in_custorder_ids(i), NULL, 'N', NULL, -- 'Abandoned', 'Canceled', l_attribute_empty_cursor,-- out_return_code, out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; -- log the action in the action history ---------------------------------------- cor_gui.log_action_history(in_custorder_ids(i), NULL, -- in_owner, 'Order canceled', l_out_gui_action_hist_id, -- out_return_code, out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; -- unlock orders --------------------------------------------------------------- cor_gui.action_unlock_orders_id(in_custorder_ids(i), in_owner, in_member_groups, out_return_code, out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; END LOOP; -- set return values ----------------------------------------------------------- out_return_code := 0; out_return_desc := 'OK'; -------------------------------------------------------------------------------- EXCEPTION WHEN ex_cor_gui THEN NULL; WHEN OTHERS THEN out_return_code := '9119'; out_return_desc := 'An error occurred during execution of stored procedure ACTION_CANCEL_ORDER' || ' SQL error code: ' || SQLCODE || '.' || ' SQL error message: ' || SQLERRM; END action_cancel_order; -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- PROCEDURE action_reject_order ( in_custorder_ids IN custorder_id_cursor, -- in_reject_reason_id IN gui_reject_reason.reject_reason_id%TYPE, -- in_reject_reason_ids IN reject_reason_id_cursor, -- in_comment IN VARCHAR2, -- in_owner IN VARCHAR2, in_member_groups IN user_group_cursor, out_return_code OUT VARCHAR2, out_return_desc OUT VARCHAR2 ) AS ex_cor_gui EXCEPTION; l_out_gui_action_hist_id gui_action_history.action_hist_id%TYPE; -- l_error_code gui_reject_reason.ERROR_CODE%TYPE; -- l_error_desc gui_reject_reason.error_description%TYPE; -- l_error_severity gui_reject_reason.error_severity%TYPE; -- l_out_error_message_id error_message.error_message_id%TYPE; l_attribute_empty_cursor attribute_array;-- l_first INTEGER; -- BEGIN -- create empty attribute cursor - is used on mor places l_attribute_empty_cursor:= attribute_array(); -- FOR i IN in_custorder_ids.FIRST .. in_custorder_ids.LAST LOOP --authorize action ------------------------------------------------------------- cor_gui.authorize_action(in_custorder_ids(i), 'REJECT_ORDER', in_owner, in_member_groups, out_return_code, out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; -- -- l_first := in_reject_reason_ids.FIRST; -- IF l_first is not null THEN -- FOR j IN in_reject_reason_ids.FIRST .. in_reject_reason_ids.LAST LOOP SELECT gr.ERROR_CODE, gr.error_description, gr.error_severity INTO l_error_code, l_error_desc, l_error_severity FROM gui_reject_reason gr WHERE gr.reject_reason_id = in_reject_reason_ids(j); cor_order_update.add_header_error_message(in_custorder_id => in_custorder_ids(i), in_suborder_id => NULL, out_error_message_id => l_out_error_message_id, in_error_code => l_error_code, in_error_description => l_error_desc, in_error_severity => l_error_severity, in_error_origin => 'Validation and Feasibility', out_return_code => out_return_code, out_return_desc => out_return_desc); END LOOP; END IF; -- IF in_comment IS NOT NULL THEN cor_order_update.add_header_error_message(in_custorder_id => in_custorder_ids(i), in_suborder_id => NULL, out_error_message_id => l_out_error_message_id, in_error_code => '8001', in_error_description => in_comment, in_error_severity => 'Medium', in_error_origin => 'Validation and Feasibility', out_return_code => out_return_code, out_return_desc => out_return_desc); END IF; -- -- -- update status to 'Rejected' ------------------------------------------------- cor_order_update.set_customer_order_status(in_custorder_ids(i), NULL, 'N', NULL, -- 'Rejected', 'Rejected', l_attribute_empty_cursor,-- out_return_code, out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; -- log the action in the action history ---------------------------------------- cor_gui.log_action_history(in_custorder_ids(i), NULL, -- in_owner, 'Order rejected', l_out_gui_action_hist_id, -- out_return_code, out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; -- unlock orders --------------------------------------------------------------- cor_gui.action_unlock_orders_id(in_custorder_ids(i), in_owner, in_member_groups, out_return_code, out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; END LOOP; -- set return values ----------------------------------------------------------- out_return_code := 0; out_return_desc := 'OK'; -------------------------------------------------------------------------------- EXCEPTION WHEN ex_cor_gui THEN NULL; WHEN OTHERS THEN out_return_code := '9129'; out_return_desc := 'An error occurred during execution of stored procedure ACTION_REJECT_ORDER' || ' SQL error code: ' || SQLCODE || '.' || ' SQL error message: ' || SQLERRM; END action_reject_order; -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- PROCEDURE action_manually_done ( in_custorder_ids IN custorder_id_cursor, in_owner IN VARCHAR2, in_member_groups IN user_group_cursor, out_return_code OUT VARCHAR2, out_return_desc OUT VARCHAR2 ) AS ex_cor_gui EXCEPTION; l_out_gui_action_hist_id gui_action_history.action_hist_id%TYPE; -- l_attribute_empty_cursor attribute_array;-- BEGIN -- create empty attribute cursor - is used on mor places l_attribute_empty_cursor:= attribute_array(); -- FOR i IN in_custorder_ids.FIRST .. in_custorder_ids.LAST LOOP --authorize action ------------------------------------------------------------- cor_gui.authorize_action(in_custorder_ids(i), 'MANUALLY_DONE', in_owner, in_member_groups, out_return_code, out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; -- update status to 'Closed' ------------------------------------------------- cor_order_update.set_customer_order_status(in_custorder_ids(i), NULL, 'N', NULL, -- 'Terminated', 'Closed', l_attribute_empty_cursor,-- out_return_code, out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; -- log the action in the action history ---------------------------------------- cor_gui.log_action_history(in_custorder_ids(i), NULL, -- in_owner, 'Order manually done', l_out_gui_action_hist_id, -- out_return_code, out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; -- unlock orders --------------------------------------------------------------- cor_gui.action_unlock_orders_id(in_custorder_ids(i), in_owner, in_member_groups, out_return_code, out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; END LOOP; -- set return values ----------------------------------------------------------- out_return_code := 0; out_return_desc := 'OK'; -------------------------------------------------------------------------------- EXCEPTION WHEN ex_cor_gui THEN NULL; WHEN OTHERS THEN out_return_code := '9139'; out_return_desc := 'An error occurred during execution of stored procedure ACTION_MANUALLY_DONE' || ' SQL error code: ' || SQLCODE || '.' || ' SQL error message: ' || SQLERRM; END action_manually_done; -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- PROCEDURE action_reassign_order ( in_custorder_ids IN custorder_id_cursor, in_department IN VARCHAR2, in_owner IN VARCHAR2, in_member_groups IN user_group_cursor, out_return_code OUT VARCHAR2, out_return_desc OUT VARCHAR2 ) AS ex_cor_gui EXCEPTION; l_out_gui_action_hist_id gui_action_history.action_hist_id%TYPE; -- BEGIN FOR i IN in_custorder_ids.FIRST .. in_custorder_ids.LAST LOOP --authorize action ------------------------------------------------------------- cor_gui.authorize_action(in_custorder_ids(i), 'REASSIGN_ORDER', in_owner, in_member_groups, out_return_code, out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; -- update group ---------------------------------------------------------------- -- --- Update suborder table for assigned_department_id based on custorder_id ------- UPDATE suborder SET assigned_department_id = in_department WHERE custorder_id = in_custorder_ids(i); -- -- log the action in the action history ---------------------------------------- cor_gui.log_action_history(in_custorder_ids(i), NULL, -- in_owner, 'Order re-assigned', l_out_gui_action_hist_id, -- out_return_code, out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; -- Not assigned to my department flag will raise---- cor_order_locking.release_order_ownership_gui(in_custorder_ids(i), in_owner, 'N', out_return_code, out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; -- log unlock action in the action history ---------------------------------------- cor_gui.log_action_history(in_custorder_ids(i), NULL, in_owner, 'Unlocked Order', l_out_gui_action_hist_id, -- out_return_code, out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; END LOOP; -- set return values ----------------------------------------------------------- out_return_code := 0; out_return_desc := 'OK'; -------------------------------------------------------------------------------- EXCEPTION WHEN ex_cor_gui THEN NULL; WHEN OTHERS THEN out_return_code := '9149'; out_return_desc := 'An error occurred during execution of stored procedure ACTION_REASSIGN_ORDER' || ' SQL error code: ' || SQLCODE || '.' || ' SQL error message: ' || SQLERRM; END action_reassign_order; -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- PROCEDURE action_set_due_date ( in_custorder_ids IN custorder_id_cursor, in_due_date IN VARCHAR2, in_owner IN VARCHAR2, in_member_groups IN user_group_cursor, out_return_code OUT VARCHAR2, out_return_desc OUT VARCHAR2 ) AS ex_cor_gui EXCEPTION; l_out_gui_action_hist_id gui_action_history.action_hist_id%TYPE; -- BEGIN FOR i IN in_custorder_ids.FIRST .. in_custorder_ids.LAST LOOP -- -- authorize action ----------------------------------------------------------- cor_gui.authorize_action(in_custorder_ids(i), 'SET_DUE_DATE', in_owner, in_member_groups, out_return_code, out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; -- -- update agent_due_date ------------------------------------------------------- UPDATE customer_order SET agent_due_date = in_due_date WHERE custorder_id = in_custorder_ids(i); -- log the action in the action history ---------------------------------------- cor_gui.log_action_history(in_custorder_ids(i), NULL, -- in_owner, 'Due date set', l_out_gui_action_hist_id, -- out_return_code, out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; END LOOP; -- set return values ----------------------------------------------------------- out_return_code := 0; out_return_desc := 'OK'; -------------------------------------------------------------------------------- EXCEPTION WHEN ex_cor_gui THEN NULL; WHEN OTHERS THEN out_return_code := '9159'; out_return_desc := 'An error occurred during execution of stored procedure ACTION_SET_DUE_DATE' || ' SQL error code: ' || SQLCODE || '.' || ' SQL error message: ' || SQLERRM; END action_set_due_date; -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- PROCEDURE action_save_note ( in_custorder_ids IN custorder_id_cursor, in_note IN VARCHAR2, in_owner IN VARCHAR2, in_member_groups IN user_group_cursor, out_return_code OUT VARCHAR2, out_return_desc OUT VARCHAR2 ) AS ex_cor_gui EXCEPTION; l_note_id note.note_id%TYPE; -- out_gui_action_hist_id gui_action_history.action_hist_id%TYPE; -- l_suborder_id VARCHAR2(5) := NULL; -- BEGIN -- l_suborder_id := null; FOR i IN in_custorder_ids.FIRST .. in_custorder_ids.LAST LOOP -- -- authorize action ----------------------------------------------------------- cor_gui.authorize_action(in_custorder_ids(i), 'SAVE_NOTE', in_owner, in_member_groups, out_return_code, out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; -- -- -- log the action in the action history ---------------------------------------- cor_gui.log_action_history(in_custorder_ids(i), NULL, -- in_owner, 'Note saved', out_gui_action_hist_id, out_return_code, out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; -- insert note ----------------------------------------------------------------- cor_order_creation.insert_note(in_custorder_ids(i), l_suborder_id, -- l_note_id, 'GUI NOTE', in_note, out_gui_action_hist_id, out_return_code, out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; -- END LOOP; -- set return values ----------------------------------------------------------- out_return_code := 0; out_return_desc := 'OK'; -------------------------------------------------------------------------------- EXCEPTION WHEN ex_cor_gui THEN NULL; WHEN OTHERS THEN out_return_code := '9169'; out_return_desc := 'An error occurred during execution of stored procedure ACTION_SAVE_NOTE' || ' SQL error code: ' || SQLCODE || '.' || ' SQL error message: ' || SQLERRM; END action_save_note; -- PROCEDURE check_profile_rank ( in_member_groups IN user_group_cursor, out_profile OUT VARCHAR2, out_cancel_profile OUT VARCHAR2, -- out_return_code OUT VARCHAR2, out_return_desc OUT VARCHAR2 ) AS l_rank NUMBER; l_usergroup_profile gui_user_groups.usergroup_profile%TYPE; BEGIN -- Run through the profiles configured in GUI_USER_GROUPS for all the user groups --the user belongs to remembering the profile with the lowest number in ENUM_USER_GROUP_PROFILE out_cancel_profile := 'N'; IF in_member_groups.exists(1) THEN -- fixed array check FOR i IN in_member_groups.FIRST .. in_member_groups.LAST LOOP SELECT gp.rank, g.usergroup_profile INTO l_rank, l_usergroup_profile FROM enum_user_group_profile gp, gui_user_groups g WHERE gp.id = g.usergroup_profile AND g.group_id IN in_member_groups(i); -- IF l_usergroup_profile = 'Cancel' THEN out_cancel_profile := 'Y'; END IF; -- IF out_profile > l_rank OR out_profile IS NULL THEN out_profile := l_rank; END IF; END LOOP; END IF; -- fixed array check -- set return values ----------------------------------------------------------- out_return_code := 0; out_return_desc := 'OK'; -------------------------------------------------------------------------------- EXCEPTION WHEN OTHERS THEN out_return_code := '9179'; out_return_desc := 'An error occurred during execution of stored procedure CHECK_PROFILE_RANK' || ' SQL error code: ' || SQLCODE || '.' || ' SQL error message: ' || SQLERRM; END check_profile_rank; -- -- PROCEDURE get_sms_notif_msg ( in_action_label IN VARCHAR, in_custorder_id IN VARCHAR, out_msisdn OUT VARCHAR, out_sms_msg OUT VARCHAR, -- out_operator OUT VARCHAR, -- -- out_language OUT VARCHAR, -- out_return_code OUT VARCHAR, out_return_desc OUT VARCHAR ) AS ex_type_not_found EXCEPTION; ex_line_action_not_found EXCEPTION; ex_msisdn_not_found EXCEPTION; ex_multiple_sms EXCEPTION; ex_sms_not_found EXCEPTION; l_count INT; l_custorder_type_id customer_order.custorder_type_id%TYPE; l_orderline_action line.order_line_action%TYPE; l_language customer_person.LANGUAGE%TYPE; l_order_line_id line.order_line_id%TYPE; BEGIN BEGIN SELECT co.custorder_type_id INTO l_custorder_type_id FROM customer_order co WHERE co.custorder_id = in_custorder_id; EXCEPTION WHEN no_data_found THEN RAISE ex_type_not_found; END; SELECT MIN(order_line_action), MIN(order_line_id), COUNT(*) INTO l_orderline_action, l_order_line_id, l_count FROM line WHERE line.custorder_id = in_custorder_id; IF l_count = 0 THEN RAISE ex_line_action_not_found; END IF; BEGIN SELECT LANGUAGE INTO l_language FROM customer_order co, customer cu, customer_person cp WHERE co.order_placing_customer_id = cu.customer_id AND cp.person_id = cu.customer_id AND co.custorder_id = in_custorder_id; EXCEPTION WHEN no_data_found THEN l_language := 'EN'; END; -- BEGIN out_language := l_language; END; -- BEGIN SELECT attribute_value INTO out_msisdn FROM line_attribute WHERE order_line_id = l_order_line_id AND attribute_name = 'MSISDN' AND rownum = 1; EXCEPTION WHEN no_data_found THEN -- BEGIN SELECT co.src_operator_id INTO out_operator FROM customer_order co WHERE co.custorder_id = in_custorder_id; EXCEPTION WHEN no_data_found THEN NULL; END; -- SELECT COUNT(*) INTO l_count FROM gui_sms_msg_cfg WHERE custorder_type_id = l_custorder_type_id; IF l_count > 0 THEN BEGIN SELECT sms_msg INTO out_sms_msg FROM gui_sms_msg_cfg WHERE action_label = in_action_label AND custorder_type_id = l_custorder_type_id AND order_line_action = l_orderline_action; -- -- AND LANGUAGE = l_language; -- EXCEPTION WHEN no_data_found THEN RAISE ex_sms_not_found; WHEN too_many_rows THEN RAISE ex_multiple_sms; END; END IF; -- set return values ----------------------------------------------------------- out_return_code := 0; out_return_desc := 'OK'; -------------------------------------------------------------------------------- EXCEPTION WHEN ex_sms_not_found THEN out_return_code := '9184'; out_return_desc := 'An error occurred during execution of stored procedure GET_SMS_NOTIF_MSG. Error code: ' || SQLCODE || '. Error description: No SMSes configured for given input: CUSTORDER_ID: ' || in_custorder_id || ' ,CUSTORDER_TYPE: ' || l_custorder_type_id || ', ACTION_LABEL: ' || in_action_label || ', ORDER_LINE_ACTION: ' || l_orderline_action || ', LANGUAGE: ' || l_language || '.'; WHEN ex_multiple_sms THEN out_return_code := '9183'; out_return_desc := 'An error occurred during execution of stored procedure GET_SMS_NOTIF_MSG. Error code: ' || SQLCODE || '. Error description: Multiple SMSes possible for given input: CUSTORDER_ID: ' || in_custorder_id || ' ,CUSTORDER_TYPE: ' || l_custorder_type_id || ', ACTION_LABEL: ' || in_action_label || ', ORDER_LINE_ACTION: ' || l_orderline_action || ', LANGUAGE: ' || l_language || '.'; WHEN ex_msisdn_not_found THEN out_return_code := '9182'; out_return_desc := 'An error occurred during execution of stored procedure GET_SMS_NOTIF_MSG. Error code: ' || SQLCODE || '. Error description: MSISDN could not be found for CUSTORDER_ID ' || in_custorder_id || '.'; WHEN ex_line_action_not_found THEN out_return_code := '9181'; out_return_desc := 'An error occurred during execution of stored procedure GET_SMS_NOTIF_MSG. Error code: ' || SQLCODE || '. Error description: ORDERLINE_ACTION could not be found for CUSTORDER_ID ' || in_custorder_id || '.'; WHEN ex_type_not_found THEN out_return_code := '9180'; out_return_desc := 'An error occurred during execution of stored procedure GET_SMS_NOTIF_MSG. Error code: ' || SQLCODE || '. Error description: CUSTORDER_TYPE_ID could not be found for CUSTORDER_ID ' || in_custorder_id || '.'; WHEN OTHERS THEN out_return_code := '9189'; out_return_desc := 'An error occurred during execution of stored procedure GET_SMS_NOTIF_MSG' || ' SQL error code: ' || SQLCODE || '.' || ' SQL error message: ' || SQLERRM || ' for CUSTORDER_ID ' || in_custorder_id || '.'; END get_sms_notif_msg; -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- PROCEDURE action_unlock_orders_id ( in_custorder_id IN VARCHAR2, in_owner IN VARCHAR2, in_member_groups IN user_group_cursor, out_return_code OUT VARCHAR2, out_return_desc OUT VARCHAR2 ) AS ex_cor_gui EXCEPTION; ex_order_locked EXCEPTION; ex_order_unlocked EXCEPTION; l_profile enum_user_group_profile.rank%TYPE; l_out_gui_action_hist_id gui_action_history.action_hist_id%TYPE; -- l_cancel_profile VARCHAR(1); -- BEGIN -- -- authorize action ----------------------------------------------------------- cor_gui.authorize_action(in_custorder_id, 'UNLOCK_ORDERS', in_owner, in_member_groups, out_return_code, out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; -- get profile rank ----------------------------------------------------------- cor_gui.check_profile_rank(in_member_groups, l_profile, l_cancel_profile, out_return_code, out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; --IF OUT_PROFILE is in ('Admin', 'Superuser') force release----------------- cor_order_locking.release_order_ownership_gui(in_custorder_id, in_owner, CASE l_profile WHEN 1 THEN 'Y' WHEN 2 THEN 'Y' ELSE 'N' END, out_return_code, out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; -- -- out_return_code, out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; -- set return values ----------------------------------------------------------- out_return_code := 0; out_return_desc := 'OK'; -------------------------------------------------------------------------------- EXCEPTION WHEN ex_order_locked THEN out_return_code := '9190'; out_return_desc := 'Order ' || in_custorder_id || ' is locked by someone else.'; WHEN ex_order_unlocked THEN out_return_code := '9191'; out_return_desc := 'Order ' || in_custorder_id || ' is already unlocked.'; WHEN ex_cor_gui THEN NULL; WHEN OTHERS THEN out_return_code := '9199'; out_return_desc := 'An error occurred during execution of stored procedure ACTION_UNLOCK_ORDERS' || ' SQL error code: ' || SQLCODE || '.' || ' SQL error message: ' || SQLERRM; END action_unlock_orders_id; -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -- PROCEDURE cor_gui_centre_refresh ( in_date_from IN DATE, in_date_to IN DATE, out_return_code OUT VARCHAR, out_return_desc OUT VARCHAR ) AS CURSOR cur_custorder_id IS SELECT custorder_id FROM customer_order WHERE creation_dttm >= in_date_from AND creation_dttm < in_date_to; CURSOR cur_error(l_this_custorder_id VARCHAR2) IS SELECT em.error_message_id, nvl(en.priority, -1) priority, em.ERROR_CODE, em.error_description FROM error_message em, enum_error_code en WHERE em.custorder_id = l_this_custorder_id AND en.ERROR_CODE(+) = em.ERROR_CODE AND em.deleted = 'N' ORDER BY priority DESC, error_message_id DESC; CURSOR cur_offer_desc(l_this_custorder_id VARCHAR2) IS SELECT order_line_id, offer_desc FROM line WHERE custorder_id = l_this_custorder_id ORDER BY order_line_id; l_this_custorder_id customer_order.custorder_id%TYPE := NULL; l_current_priority enum_error_code.priority%TYPE := NULL; l_current_error_message_id error_message.error_message_id%TYPE := NULL; l_current_error_code error_message.ERROR_CODE%TYPE := NULL; l_current_error_description error_message.error_description%TYPE := NULL; l_latest_message note.message%TYPE := NULL; l_current_offer_desc line.offer_desc%TYPE := NULL; l_current_main_order_line line.order_line_id%TYPE := NULL; l_current_num_contracts NUMBER := NULL; l_custorder_id customer_order.custorder_id%TYPE := NULL; l_bscs_cn cor_gui_centre.bscs_cn%TYPE := NULL; l_msisdn cor_gui_centre.msisdn%TYPE := NULL; l_sim_num cor_gui_centre.sim_num%TYPE := NULL; l_legal_entity cor_gui_centre.legal_entity%TYPE := NULL; l_last_name cor_gui_centre.last_name%TYPE := NULL; l_first_name cor_gui_centre.first_name%TYPE := NULL; l_channel cor_gui_centre.channel%TYPE := NULL; l_operator cor_gui_centre.operator%TYPE := NULL; l_cust_order_ownership cor_gui_centre.ownership%TYPE := NULL; l_department cor_gui_centre.department%TYPE := NULL; l_cust_order_auto_delivery cor_gui_centre.is_automated_delivery%TYPE := NULL; l_bill_cycle_date cor_gui_centre.bill_cycle_date%TYPE := NULL; l_number_true NUMBER := NULL; l_number_of_cust NUMBER := NULL; l_tmp_number_of_custorder NUMBER := NULL; l_number_of_err NUMBER := NULL; l_max_note_id note.note_id%TYPE := NULL; l_num_mnp NUMBER; --<9934 TOH 16/10/2007> l_num_fnp NUMBER; --<9934 TOH 16/10/2007> l_xnp VARCHAR2(32767) := NULL; --<9934 TOH 16/10/2007> l_ownership customer_order.ownership%TYPE; l_auto_delivery customer_order.is_automated_delivery%TYPE; l_num_false INT; l_num_true INT; exc_wrong_date EXCEPTION; exc_success EXCEPTION; BEGIN --If IN_DATE_TO<=IN_DATE_FROM exit and throw error 9201. IF in_date_to > in_date_from THEN OPEN cur_custorder_id; LOOP FETCH cur_custorder_id INTO l_this_custorder_id; EXIT WHEN cur_custorder_id%NOTFOUND; --Delete all records from COR_GUI_CENTRE where CUSTORDER_ID in CUSTORDER_IDS (THIS_CUSTORDER_ID). SELECT COUNT(1) INTO l_tmp_number_of_custorder FROM cor_gui_centre WHERE custorder_id = l_this_custorder_id; IF l_tmp_number_of_custorder > 0 THEN DELETE FROM cor_gui_centre WHERE custorder_id = l_this_custorder_id; END IF; --Execute and store the results in COR_GUI_CENTRE------------------------------- SELECT co.custorder_id custorder_id, (SELECT bscscc_key.VALUE bscs_cn FROM customer_retrieval_key bscscc_key WHERE bscscc_key.custorder_id(+) = co.custorder_id AND bscscc_key.key_type(+) = 'BSCS Customer Number' AND rownum = 1) AS bscs_cn, (SELECT msisdn_key.VALUE msisdn FROM customer_retrieval_key msisdn_key WHERE msisdn_key.custorder_id(+) = co.custorder_id AND msisdn_key.key_type(+) = 'MSISDN' AND rownum = 1) AS msisdn, (SELECT simnum_key.VALUE sim_num FROM customer_retrieval_key simnum_key WHERE simnum_key.custorder_id(+) = co.custorder_id AND simnum_key.key_type(+) = 'SIM Card' AND rownum = 1) AS sim_num, (SELECT upper(legal_entity.NAME) legal_entity FROM customer_legal_entity legal_entity WHERE legal_entity.legal_entity_id(+) = co.order_placing_customer_id AND rownum = 1) AS legal_entity, (SELECT upper(cust.last_name) last_name FROM customer_person cust WHERE cust.person_id(+) = co.order_placing_customer_id AND rownum = 1) AS l_last_name, (SELECT upper(cust.first_name) first_name FROM customer_person cust WHERE cust.person_id(+) = co.order_placing_customer_id AND rownum = 1) AS l_first_name, (SELECT en_channel.label channel FROM enum_channel en_channel WHERE en_channel.id(+) = co.src_channel_id AND rownum = 1) AS l_channel, (SELECT en_operator.label operator FROM enum_src_operator en_operator WHERE en_operator.id(+) = co.src_operator_id AND rownum = 1) AS l_operator, upper(co.ownership), (SELECT en_department.group_desc department FROM gui_user_groups en_department WHERE en_department.group_id(+) = co.assigned_user_group_id AND rownum = 1) AS department, co.is_automated_delivery cust_order_auto_delivery, (SELECT bill.bill_cycle_date bill_cycle_date FROM billing_account bill WHERE bill.custorder_id(+) = co.custorder_id AND rownum = 1) AS bill_cycle_date INTO l_custorder_id, l_bscs_cn, l_msisdn, l_sim_num, l_legal_entity, l_last_name, l_first_name, l_channel, l_operator, l_cust_order_ownership, l_department, l_cust_order_auto_delivery, l_bill_cycle_date FROM customer_order co WHERE co.custorder_id = l_this_custorder_id; INSERT INTO cor_gui_centre (custorder_id, bscs_cn, msisdn, sim_num, legal_entity, last_name, first_name, channel, operator, cust_order_ownership, department, cust_order_auto_delivery, bill_cycle_date) VALUES (l_custorder_id, l_bscs_cn, l_msisdn, l_sim_num, l_legal_entity, l_last_name, l_first_name, l_channel, l_operator, l_cust_order_ownership, l_department, l_cust_order_auto_delivery, l_bill_cycle_date); --Error Message----------------------------------------------------------------- --Select the ERROR_CODE and ERROR_DESCRIPTION from the first record of the statement (Maximal Priority) into CURRENT_ERROR_CODE and CURRENT_ERROR_DESCRIPTION. --If no records were found set CURRENT_ERROR_CODE=NULL and CURRENT_ERROR_ DESCRIPTION =NULL. OPEN cur_error(l_this_custorder_id); FETCH cur_error INTO l_current_error_message_id, l_current_priority, l_current_error_code, l_current_error_description; IF cur_error%ROWCOUNT = 0 THEN BEGIN l_current_error_code := NULL; l_current_error_description := NULL; END; END IF; CLOSE cur_error; SELECT COUNT(1) INTO l_number_of_err FROM error_message WHERE custorder_id = l_this_custorder_id AND deleted = 'N'; UPDATE cor_gui_centre SET ERROR_CODE = l_current_error_code, error_description = l_current_error_description, number_of_errors = l_number_of_err WHERE custorder_id = l_this_custorder_id; --Note-------------------------------------------------------------------------- --Find the latest record in NOTE where CUSTORDER_ID=THIS_CUSTORDER_ID identified as LATEST_MESSAGE. --Update COR_GUI_CENTRE --If no data found do nothing l_max_note_id := NULL; -- l_latest_message := NULL; -- SELECT MAX(note_id) INTO l_max_note_id FROM note WHERE custorder_id = l_this_custorder_id; IF l_max_note_id IS NOT NULL THEN SELECT message INTO l_latest_message FROM note WHERE note_id = l_max_note_id; END IF; IF l_latest_message IS NOT NULL THEN UPDATE cor_gui_centre SET note_message = l_latest_message WHERE custorder_id = l_this_custorder_id; END IF; --Customer---------------------------------------------------------------------- SELECT COUNT(1) INTO l_number_of_cust FROM customer WHERE custorder_id = l_this_custorder_id; IF (l_number_of_cust) > 0 THEN SELECT COUNT(1) INTO l_number_true FROM customer WHERE custorder_id = l_this_custorder_id AND is_new_customer = 'true'; IF l_number_true = 0 THEN UPDATE cor_gui_centre SET is_new_customer = 'false' WHERE custorder_id = l_this_custorder_id; ELSE UPDATE cor_gui_centre SET is_new_customer = 'true' WHERE custorder_id = l_this_custorder_id; END IF; END IF; --Order Line-------------------------------------------------------------------- l_current_main_order_line := NULL; -- l_current_offer_desc := NULL; -- l_current_num_contracts := NULL; -- OPEN cur_offer_desc(l_this_custorder_id); FETCH cur_offer_desc INTO l_current_main_order_line, l_current_offer_desc; IF cur_offer_desc%ROWCOUNT = 0 THEN l_current_main_order_line := NULL; l_current_offer_desc := NULL; -- END IF; CLOSE cur_offer_desc; SELECT COUNT(1) INTO l_current_num_contracts FROM line WHERE custorder_id = l_this_custorder_id AND offer_id = offer_desc; UPDATE cor_gui_centre SET main_order_line = l_current_offer_desc, number_of_contracts = l_current_num_contracts WHERE custorder_id = l_this_custorder_id; -- BEGIN SELECT ownership INTO l_ownership FROM line WHERE order_line_id = (SELECT MIN(order_line_id) FROM line WHERE ownership IS NOT NULL AND custorder_id = l_this_custorder_id); EXCEPTION WHEN no_data_found THEN l_ownership := NULL; END; -- SELECT COUNT(1) INTO l_num_false FROM line WHERE is_automatic_delivery = 'false' AND custorder_id = l_this_custorder_id; SELECT COUNT(1) INTO l_num_true FROM line WHERE is_automatic_delivery = 'true' AND custorder_id = l_this_custorder_id; IF (l_num_false > 0) THEN l_auto_delivery := 'false'; ELSIF (l_num_true > 0) THEN l_auto_delivery := 'true'; END IF; UPDATE cor_gui_centre SET line_auto_delivery = l_auto_delivery, line_ownership = l_ownership WHERE custorder_id = l_this_custorder_id; -- -- SELECT COUNT(1) INTO l_num_mnp FROM line_attribute WHERE custorder_id = l_this_custorder_id AND attribute_name = 'Origin of the number' AND attribute_value = 'MNP'; SELECT COUNT(1) INTO l_num_fnp FROM line_attribute WHERE custorder_id = l_this_custorder_id AND attribute_name = 'Customer Type at other operator'; IF l_num_mnp = 0 AND l_num_fnp = 0 THEN l_xnp := NULL; END IF; IF l_num_mnp > 0 AND l_num_fnp = 0 THEN l_xnp := 'MNP'; END IF; IF l_num_mnp = 0 AND l_num_fnp > 0 THEN l_xnp := 'FNP'; END IF; IF l_num_mnp > 0 AND l_num_fnp > 0 THEN l_xnp := 'MNP/FNP'; END IF; UPDATE cor_gui_centre SET xnp = l_xnp WHERE custorder_id = l_this_custorder_id; -- -- -- IF (l_auto_delivery IS NULL OR l_cust_order_auto_delivery IS NULL) THEN UPDATE cor_gui_centre SET is_automated_delivery = NULL WHERE custorder_id = l_this_custorder_id; ELSE -- UPDATE cor_gui_centre SET is_automated_delivery = decode(l_auto_delivery, 'true', 'true', 'false', decode(l_cust_order_auto_delivery, 'false', 'false', 'true', 'partial')) WHERE custorder_id = l_this_custorder_id; END IF; -- -- UPDATE cor_gui_centre SET ownership = nvl(cust_order_ownership, line_ownership) WHERE custorder_id = l_this_custorder_id; -- END LOOP; CLOSE cur_custorder_id; -- set return values ----------------------------------------------------------- out_return_code := 0; out_return_desc := 'Success'; -------------------------------------------------------------------------------- ELSE RAISE exc_wrong_date; END IF; EXCEPTION WHEN exc_wrong_date THEN BEGIN out_return_code := 9201; out_return_desc := 'During COR_GUI_CENTRE_REFRESH an invalid date range was provided.'; END; WHEN OTHERS THEN BEGIN out_return_code := 9209; out_return_desc := 'An error occurred during execution of stored procedure COR_GUI_CENTRE_REFRESH. Error code: ' || SQLCODE || '. Error description: ' || SQLERRM || '.'; END; END cor_gui_centre_refresh; -- -- PROCEDURE action_lock_suborders ( in_suborder_ids IN suborder_id_cursor, in_owner IN VARCHAR, in_member_groups IN user_group_cursor, out_return_code OUT VARCHAR, out_return_desc OUT VARCHAR ) AS ex_cor_gui EXCEPTION; l_suborder_id suborder.suborder_id%TYPE; l_out_gui_action_hist_id gui_action_history.action_hist_id%TYPE; l_custorder_id customer_order.custorder_id%TYPE; BEGIN FOR i IN in_suborder_ids.FIRST .. in_suborder_ids.LAST LOOP l_suborder_id := in_suborder_ids(i); l_custorder_id := substr(l_suborder_id, 0, 11); -- authorize action -------------------- cor_gui.authorize_suborder_action(in_custorder_id => l_custorder_id, in_suborder_id => l_suborder_id, in_action => 'LOCK_SUBORDER', in_owner => in_owner, in_member_groups => in_member_groups, out_return_code => out_return_code, out_return_desc => out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; -- lock order --------------------------------------------- cor_order_locking.take_suborder_ownership(l_suborder_id, in_owner, 'N', out_return_code, out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; -- log the action in the action history -------------------- cor_gui.log_action_history(l_custorder_id, l_suborder_id, in_owner, 'Locked Suborder', l_out_gui_action_hist_id, out_return_code, out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; END LOOP; -- set return values -------------------------- out_return_code := 0; out_return_desc := 'OK'; ---------------------------------------------- EXCEPTION WHEN ex_cor_gui THEN IF out_return_code = '5051' THEN out_return_code := '9021'; out_return_desc := 'Ownership error: Suborder ' || l_suborder_id || ' or one of its Suborder owned by somebody else.'; ELSE out_return_code := out_return_code; END IF; WHEN OTHERS THEN out_return_code := '9028'; out_return_desc := 'An error occurred during execution of stored procedure ACTION_LOCK_SUBORDERS' || ' SQL error code: ' || SQLCODE || '.' || ' SQL error message: ' || SQLERRM; END action_lock_suborders; -- PROCEDURE action_unlock_suborder ( in_suborder_id IN VARCHAR2, in_owner IN VARCHAR2, in_member_groups IN user_group_cursor, out_return_code OUT VARCHAR2, out_return_desc OUT VARCHAR2 ) AS ex_cor_gui EXCEPTION; ex_order_locked EXCEPTION; ex_order_unlocked EXCEPTION; l_profile enum_user_group_profile.rank%TYPE; l_out_gui_action_hist_id gui_action_history.action_hist_id%TYPE; l_cancel_profile VARCHAR(1); l_custorder_id customer_order.custorder_id%TYPE; BEGIN -- get custorder_id l_custorder_id := substr(in_suborder_id, 0, 11); -- authorize action -------------------- cor_gui.authorize_suborder_action(in_custorder_id => l_custorder_id, in_suborder_id => in_suborder_id, in_action => 'UNLOCK_SUBORDER', in_owner => in_owner, in_member_groups => in_member_groups, out_return_code => out_return_code, out_return_desc => out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; -- in_suborder_id, in_owner => in_owner, in_force => CASE l_profile WHEN 1 THEN 'Y' WHEN 2 THEN 'Y' ELSE 'N' END, out_return_code => out_return_code, out_return_desc => out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; -- log the action in the action history ---------------------------------------- cor_gui.log_action_history(l_custorder_id, in_suborder_id, in_owner, 'Unlocked Suborder', l_out_gui_action_hist_id, out_return_code, out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; -- set return values ----------------------------------------------------------- out_return_code := 0; out_return_desc := 'OK'; -------------------------------------------------------------------------------- EXCEPTION WHEN ex_order_locked THEN out_return_code := '9032'; out_return_desc := 'SubOrder ' || in_suborder_id || ' is locked by someone else.'; WHEN ex_order_unlocked THEN out_return_code := '9033'; out_return_desc := 'SubOrder ' || in_suborder_id || ' is already unlocked.'; WHEN ex_cor_gui THEN NULL; WHEN OTHERS THEN out_return_code := '9034'; out_return_desc := 'An error occurred during execution of stored procedure ACTION_UNLOCK_SUBORDER' || ' SQL error code: ' || SQLCODE || '.' || ' SQL error message: ' || SQLERRM; END action_unlock_suborder; -- PROCEDURE action_unlock_suborders ( in_suborder_ids IN suborder_id_cursor, in_owner IN VARCHAR2, in_member_groups IN user_group_cursor, out_return_code OUT VARCHAR2, out_return_desc OUT VARCHAR2 ) AS ex_cor_gui EXCEPTION; ex_order_locked EXCEPTION; ex_order_unlocked EXCEPTION; l_profile enum_user_group_profile.rank%TYPE; l_out_gui_action_hist_id gui_action_history.action_hist_id%TYPE; l_cancel_profile VARCHAR(1); l_custorder_id customer_order.custorder_id%TYPE; l_suborder_id suborder.suborder_id%TYPE; BEGIN FOR i IN in_suborder_ids.FIRST .. in_suborder_ids.LAST LOOP l_suborder_id := in_suborder_ids(i); -- get custorder_id l_custorder_id := substr(l_suborder_id, 0, 11); -- authorize action -------------------- cor_gui.authorize_suborder_action(in_custorder_id => l_custorder_id, in_suborder_id => l_suborder_id, in_action => 'UNLOCK_SUBORDER', in_owner => in_owner, in_member_groups => in_member_groups, out_return_code => out_return_code, out_return_desc => out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; -- l_suborder_id, in_owner => in_owner, in_force => CASE l_profile WHEN 1 THEN 'Y' WHEN 2 THEN 'Y' ELSE 'N' END, out_return_code => out_return_code, out_return_desc => out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; -- log the action in the action history ---------------------------------------- cor_gui.log_action_history(l_custorder_id, l_suborder_id, in_owner, 'Unlocked Suborder', l_out_gui_action_hist_id, out_return_code, out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; END LOOP; -- set return values ----------------------------------------------------------- out_return_code := 0; out_return_desc := 'OK'; -------------------------------------------------------------------------------- EXCEPTION WHEN ex_order_locked THEN out_return_code := '9035'; out_return_desc := 'SubOrder ' || l_suborder_id || ' is locked by someone else.'; WHEN ex_order_unlocked THEN out_return_code := '9036'; out_return_desc := 'SubOrder ' || l_suborder_id || ' is already unlocked.'; WHEN ex_cor_gui THEN NULL; WHEN OTHERS THEN out_return_code := '9038'; out_return_desc := 'An error occurred during execution of stored procedure ACTION_UNLOCK_SUBORDERS' || ' SQL error code: ' || SQLCODE || '.' || ' SQL error message: ' || SQLERRM; END action_unlock_suborders; PROCEDURE treat_suborders_stop ( in_suborder_id IN VARCHAR2, in_owner IN VARCHAR2, in_log_hist IN VARCHAR2, out_return_code OUT VARCHAR2, out_return_desc OUT VARCHAR2 ) AS ex_cor_gui EXCEPTION; l_treatment suborder.treating%TYPE; l_out_gui_action_hist_id gui_action_history.action_hist_id%TYPE; l_custorder_id customer_order.custorder_id%TYPE; BEGIN -- check order treatment ------------------------------------------------------- cor_gui.check_suborder_treatment(in_suborder_id => in_suborder_id, out_treating => l_treatment, out_return_code => out_return_code, out_return_desc => out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; IF l_treatment = 'true' THEN -- Unmark order for treatment ---------------------------------------------------- UPDATE suborder SET treating = 'false' WHERE suborder.suborder_id = in_suborder_id; -- log the action in the action history ---------------------------------------- IF in_log_hist = 'Y' THEN -- get custorder_id l_custorder_id := substr(in_suborder_id, 0, 11); cor_gui.log_action_history(l_custorder_id, in_suborder_id, in_owner, 'SubOrder treatment ended', l_out_gui_action_hist_id, out_return_code, out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; END IF; END IF; -- set return values ----------------------------------------------------------- out_return_code := 0; out_return_desc := 'OK'; -------------------------------------------------------------------------------- EXCEPTION WHEN ex_cor_gui THEN NULL; WHEN OTHERS THEN out_return_code := '9078'; out_return_desc := 'An error occurred during execution of stored procedure TREAT_SUBORDERS_STOP' || ' SQL error code: ' || SQLCODE || '.' || ' SQL error message: ' || SQLERRM; END treat_suborders_stop; PROCEDURE action_treat_suborders_start ( in_suborder_ids IN suborder_id_cursor, in_owner IN VARCHAR2, in_member_groups IN user_group_cursor, out_return_code OUT VARCHAR2, out_return_desc OUT VARCHAR2 ) AS ex_cor_gui EXCEPTION; ex_order_locked EXCEPTION; ex_order_unlocked EXCEPTION; l_suborder_id suborder.suborder_id%TYPE; l_out_gui_action_hist_id gui_action_history.action_hist_id%TYPE; l_custorder_id customer_order.custorder_id%TYPE; BEGIN FOR i IN in_suborder_ids.FIRST .. in_suborder_ids.LAST LOOP l_suborder_id := in_suborder_ids(i); -- get custorder_id l_custorder_id := substr(l_suborder_id, 0, 11); -- authorize action ----------------------------------------------------------- cor_gui.authorize_suborder_action(in_custorder_id => l_custorder_id, in_suborder_id => l_suborder_id, in_action => 'TREAT_SUBORDERS_START', in_owner => in_owner, in_member_groups => in_member_groups, out_return_code => out_return_code, out_return_desc => out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; -- Mark suborder for treatment ---------------------------------------------------- UPDATE suborder SET treating = 'true' WHERE suborder_id = l_suborder_id; -- log the action in the action history ---------------------------------------- cor_gui.log_action_history(l_custorder_id, l_suborder_id, in_owner, 'SubOrder treatment started', l_out_gui_action_hist_id, out_return_code, out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; END LOOP; -- set return values ----------------------------------------------------------- out_return_code := 0; out_return_desc := 'OK'; -------------------------------------------------------------------------------- EXCEPTION WHEN ex_order_locked THEN out_return_code := '9045'; out_return_desc := 'Order ' || l_suborder_id || ' is locked by someone else.'; WHEN ex_order_unlocked THEN out_return_code := '9046'; out_return_desc := 'Order ' || l_suborder_id || ' must first be locked.'; WHEN ex_cor_gui THEN NULL; WHEN OTHERS THEN out_return_code := '9048'; out_return_desc := 'An error occurred during execution of stored procedure ACTION_TREAT_SUBORDERS_START' || ' SQL error code: ' || SQLCODE || '.' || ' SQL error message: ' || SQLERRM; END action_treat_suborders_start; PROCEDURE authorize_suborder_action ( in_custorder_id IN VARCHAR2, in_suborder_id IN VARCHAR2, in_action IN VARCHAR2, in_owner IN VARCHAR2, in_member_groups IN user_group_cursor, out_return_code OUT VARCHAR2, out_return_desc OUT VARCHAR2 ) AS ex_cor_gui EXCEPTION; ex_order_locked EXCEPTION; ex_order_unlocked EXCEPTION; ex_group_not_found EXCEPTION; ex_treatment EXCEPTION; ex_illegal_status EXCEPTION; ex_error_code EXCEPTION; ex_unknown_action EXCEPTION; ex_department_not_assigned EXCEPTION; ex_delivery_validation_lock EXCEPTION; ex_not_allowed EXCEPTION; l_rc INT; l_groups INT := 0; l_lock_status VARCHAR(30); l_treatment customer_order.treating%TYPE; l_error_code error_message.ERROR_CODE%TYPE; l_profile enum_user_group_profile.rank%TYPE; l_department_found BOOLEAN := FALSE; l_action INT; l_cancel_profile VARCHAR(1); BEGIN -- check gui_action ----------------------------------------------------------- SELECT COUNT(*) INTO l_rc FROM gui_actions WHERE action_label = in_action; IF l_rc = 0 THEN RAISE ex_unknown_action; END IF; -- get profile rank ----------------------------------------------------------- cor_gui.check_profile_rank(in_member_groups, l_profile, l_cancel_profile, out_return_code, out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; IF l_profile != 1 THEN -- check user department ------------------------------------------------------ FOR i IN in_member_groups.FIRST .. in_member_groups.LAST LOOP SELECT COUNT(*) INTO l_rc FROM gui_user_groups WHERE group_id = in_member_groups(i) AND in_member_groups(i) = (SELECT assigned_user_group_id FROM customer_order WHERE custorder_id = in_custorder_id); IF l_rc != 0 THEN l_department_found := TRUE; EXIT; END IF; END LOOP; IF in_action IN ('TREAT_SUBORDERS_START', 'TREAT_SUBORDERS_STOP', 'LOCK_SUBORDERS', 'UNLOCK_SUBORDERS', 'CANCEL_SUBORDER') AND l_cancel_profile = 'Y' THEN NULL; ELSIF l_department_found = FALSE THEN RAISE ex_department_not_assigned; END IF; IF in_action = 'UNLOCK_SUBORDERS' -- 'UNLOCK_ORDERS' THEN -- check order lock ------------------------------------------------------------ cor_gui.check_suborder_lock(in_suborder_id => in_suborder_id, in_owner => in_owner, out_lock_status => l_lock_status, out_return_code => out_return_code, out_return_desc => out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; IF l_profile = 2 THEN CASE l_lock_status WHEN 'LOCKED_BY_DELIVERY_LAYER' THEN RAISE ex_delivery_validation_lock; WHEN 'LOCKED_BY_ORDER_VALIDATION' THEN RAISE ex_delivery_validation_lock; WHEN 'UNLOCKED' THEN RAISE ex_order_unlocked; ELSE NULL; END CASE;ELSE CASE l_lock_status WHEN 'LOCKED_BY_OTHER' THEN RAISE ex_order_locked; WHEN 'LOCKED_BY_DELIVERY_LAYER' THEN RAISE ex_delivery_validation_lock; WHEN 'LOCKED_BY_ORDER_VALIDATION' THEN RAISE ex_delivery_validation_lock; WHEN 'UNLOCKED' THEN RAISE ex_order_unlocked; ELSE NULL; END CASE; END IF; END IF; END IF; --l_profile != 1 SELECT decode(in_action, 'LOCK_SUBORDERS', 4, 'UNLOCK_SUBORDERS', 3, 'TREAT_SUBORDERS_START', 1, 'TREAT_SUBORDERS_STOP', 1, 'EXPORT_LIST', 3, 'RESUBMIT_SUBORDER_WO_VAL', 2, 'RESUBMIT_SUBORDER', 2, 'CANCEL_SUBORDER', 2, 'REJECT_SUBORDER', 2, 'MANUALLY_DONE', 2, 'REASSIGN_SUBORDER', 1, 'SET_DUE_DATE', 3, 'SAVE_NOTE', 3, 'RESUBMIT_SUBORDER', -- 2) INTO l_action FROM dual; IF (l_action = 1 OR l_action = 2) THEN -- check order lock ------------------------------------------------------------ cor_gui.check_suborder_lock(in_suborder_id => in_suborder_id, in_owner => in_owner, out_lock_status => l_lock_status, out_return_code => out_return_code, out_return_desc => out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; CASE l_lock_status WHEN 'LOCKED_BY_OTHER' THEN RAISE ex_order_locked; WHEN 'LOCKED_BY_DELIVERY_LAYER' THEN RAISE ex_delivery_validation_lock; WHEN 'LOCKED_BY_ORDER_VALIDATION' THEN RAISE ex_delivery_validation_lock; WHEN 'UNLOCKED' THEN RAISE ex_order_unlocked; ELSE NULL; END CASE; END IF; IF l_action = 2 THEN -- check suborder treatment ------------------------------------------------------- cor_gui.check_suborder_treatment(in_suborder_id => in_suborder_id, out_treating => l_treatment, out_return_code => out_return_code, out_return_desc => out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; IF l_treatment != 'true' THEN RAISE ex_treatment; END IF; END IF; -- check if status is legal ---------------------------------------------------- SELECT COUNT(*) INTO l_rc FROM gui_action_show_status WHERE action_id IN (SELECT action_id FROM gui_actions WHERE action_label = in_action) AND status_id IN (SELECT status_internal FROM suborder WHERE suborder_id = in_suborder_id); IF l_rc = 0 THEN RAISE ex_illegal_status; END IF; -- check if none of the user groups the user belongs to is configured for requested action FOR i IN in_member_groups.FIRST .. in_member_groups.LAST LOOP SELECT COUNT(*) INTO l_rc FROM gui_action_show_user_group WHERE group_id = in_member_groups(i); l_groups := l_groups + l_rc; END LOOP; IF l_groups = 0 THEN RAISE ex_group_not_found; END IF; -- check error code ---------------------------------------------------------- BEGIN SELECT ERROR_CODE INTO l_error_code FROM gui_action_hide_error WHERE action_id IN (SELECT action_id FROM gui_actions WHERE action_label = in_action) AND ERROR_CODE IN (SELECT ERROR_CODE FROM error_message WHERE custorder_id = in_custorder_id AND deleted = 'N') AND rownum = 1; RAISE ex_error_code; EXCEPTION WHEN no_data_found THEN NULL; END; -- check action type ----------------------------------------------------------- SELECT COUNT(*) INTO l_rc FROM gui_actions WHERE action_label = in_action; IF l_rc = 0 THEN RAISE ex_unknown_action; END IF; -- set return values ----------------------------------------------------------- out_return_code := 0; out_return_desc := 'OK'; -------------------------------------------------------------------------------- EXCEPTION WHEN ex_delivery_validation_lock THEN out_return_code := '9098'; out_return_desc := 'SubOrder ' || in_suborder_id || ' is locked by the delivery layer/order validation'; WHEN ex_department_not_assigned THEN out_return_code := '9097'; out_return_desc := 'SubOrder ' || in_suborder_id || ' is not assigned to your department'; WHEN ex_unknown_action THEN out_return_code := '9096'; out_return_desc := 'Unknown action type ' || in_action || '.'; WHEN ex_error_code THEN out_return_code := '9095'; out_return_desc := 'The error ' || l_error_code || ' does not allow to perform the requested action.'; WHEN ex_illegal_status THEN out_return_code := '9094'; out_return_desc := 'SubOrder ' || in_suborder_id || ' is in illegal status for requested action.'; WHEN ex_treatment THEN out_return_code := '9093'; out_return_desc := 'SubOrder ' || in_suborder_id || ' must be marked for treatment in order to perform requested action.'; WHEN ex_group_not_found THEN out_return_code := '9082'; out_return_desc := 'You are not allowed to perform the requested action.'; WHEN ex_order_locked THEN out_return_code := '9090'; out_return_desc := 'SubOrder ' || in_suborder_id || ' is locked by someone else.'; WHEN ex_not_allowed THEN out_return_code := '9092'; out_return_desc := ' You are not allowed to perform the requested action.'; WHEN ex_order_unlocked THEN out_return_code := '9091'; out_return_desc := 'SubOrder ' || in_suborder_id || ' must first be locked.'; WHEN ex_cor_gui THEN NULL; WHEN OTHERS THEN out_return_code := '10000'; out_return_desc := 'An error occurred during execution of stored procedure AUTHORIZE_SUBORDER_ACTION' || ' SQL error code: ' || SQLCODE || '.' || ' SQL error message: ' || SQLERRM; END authorize_suborder_action; PROCEDURE check_suborder_lock ( in_suborder_id IN VARCHAR2, in_owner IN VARCHAR2, out_lock_status OUT VARCHAR2, out_return_code OUT VARCHAR2, out_return_desc OUT VARCHAR2 ) AS ex_cor_gui EXCEPTION; l_rc INT; l_locked_status VARCHAR(30); BEGIN -- checks the status of the order lock --------------------------------------- cor_order_locking.check_suborder_ownership(in_suborder_id => in_suborder_id, in_owner => in_owner, out_return_code => out_return_code, out_return_desc => out_return_desc); CASE out_return_code WHEN '0' THEN SELECT COUNT(*) INTO l_rc FROM line l, suborder s WHERE s.suborder_id = l.suborder_id(+) -- AND (s.ownership IS NOT NULL OR l.ownership IS NOT NULL) AND s.suborder_id = in_suborder_id; IF l_rc = 0 THEN l_locked_status := 'UNLOCKED'; ELSE l_locked_status := 'LOCKED_BY_ME'; END IF; WHEN '5071' THEN cor_order_locking.check_suborder_ownership(in_suborder_id => in_suborder_id, in_owner => 'Delivery Layer', out_return_code => out_return_code, out_return_desc => out_return_desc); IF out_return_code = '5071' THEN cor_order_locking.check_suborder_ownership(in_suborder_id => in_suborder_id, in_owner => 'Order Validation', out_return_code => out_return_code, out_return_desc => out_return_desc); IF out_return_code = '5071' THEN l_locked_status := 'LOCKED_BY_OTHER'; ELSE l_locked_status := 'LOCKED_BY_ORDER_VALIDATION'; END IF; ELSE l_locked_status := 'LOCKED_BY_DELIVERY_LAYER'; END IF; END CASE; -- set return values ----------------------------------------------------------- out_lock_status := l_locked_status; out_return_code := 0; out_return_desc := 'OK'; -------------------------------------------------------------------------------- EXCEPTION WHEN ex_cor_gui THEN NULL; WHEN OTHERS THEN out_return_code := '9068'; out_return_desc := 'An error occurred during execution of stored procedure CHECK_SUBORDER_LOCK' || ' SQL error code: ' || SQLCODE || '.' || ' SQL error message: ' || SQLERRM; END check_suborder_lock; PROCEDURE check_suborder_treatment ( in_suborder_id IN VARCHAR2, out_treating OUT VARCHAR2, out_return_code OUT VARCHAR2, out_return_desc OUT VARCHAR2 ) AS BEGIN -- checks whether the order is currently being treated by an agent ------------- out_treating := 'false'; SELECT treating INTO out_treating FROM suborder WHERE suborder_id = in_suborder_id; -- set return values ----------------------------------------------------------- out_return_code := 0; out_return_desc := 'OK'; -------------------------------------------------------------------------------- EXCEPTION WHEN OTHERS THEN out_return_code := '9058'; out_return_desc := 'An error occurred during execution of stored procedure CHECK_SUBORDER_TREATMENT' || ' SQL error code: ' || SQLCODE || '.' || ' SQL error message: ' || SQLERRM; END check_suborder_treatment; -- PROCEDURE retrieve_tooltip_description ( in_label IN VARCHAR2, out_return_description OUT VARCHAR2, out_return_code OUT VARCHAR2, out_return_desc OUT VARCHAR2 ) AS BEGIN SELECT description INTO out_return_description FROM gui_tooltip_description WHERE label = in_label; -- set return values ----------------------------------------------------------- out_return_code := 0; out_return_desc := 'Success'; -------------------------------------------------------------------------------- EXCEPTION WHEN OTHERS THEN out_return_code := '9057'; out_return_desc := 'An error occurred during execution of stored procedure RETRIEVE_TOOLTIP_DESCRIPTION' || ' SQL error code: ' || SQLCODE || '.' || ' SQL error message: ' || SQLERRM; END retrieve_tooltip_description; -- PROCEDURE action_cancel_suborder ( in_suborder_ids IN suborder_id_cursor, in_owner IN VARCHAR2, in_member_groups IN user_group_cursor, out_return_code OUT VARCHAR2, out_return_desc OUT VARCHAR2 ) AS ex_cor_gui EXCEPTION; l_custorder_id customer_order.custorder_id%TYPE; l_suborder_id suborder.suborder_id%TYPE; l_out_gui_action_hist_id gui_action_history.action_hist_id%TYPE; l_attribute_empty_cursor attribute_array;-- BEGIN -- create empty attribute cursor - is used on mor places l_attribute_empty_cursor:= attribute_array(); -- FOR i IN in_suborder_ids.FIRST .. in_suborder_ids.LAST LOOP -- get custorder_id l_suborder_id := in_suborder_ids(i); l_custorder_id := substr(l_suborder_id, 0, 11); --take first 8 char --authorize action ------------------------------------------------------------- cor_gui.authorize_suborder_action(in_custorder_id => l_custorder_id, in_suborder_id => l_suborder_id, in_action => 'CANCEL_SUBORDER', in_owner => in_owner, in_member_groups => in_member_groups, out_return_code => out_return_code, out_return_desc => out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; -- update status to 'Canceled' ------------------------------------------------- cor_order_update.set_suborder_status(in_suborder_id => l_suborder_id, in_modifier => NULL, in_force => 'N', in_internal_status => 'Canceled', in_external_status => NULL,-- in_attribute_cursor => l_attribute_empty_cursor,-- out_return_code => out_return_code, out_return_desc => out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; -- log the action in the action history ---------------------------------------- cor_gui.log_action_history(in_custorder_id => l_custorder_id, in_suborder_id => l_suborder_id, in_owner => in_owner, in_action_hist_desc => 'Suborder cancelled', out_gui_action_hist_id => l_out_gui_action_hist_id, out_return_code => out_return_code, out_return_desc => out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; -- unlock suborders --------------------------------------------------------------- cor_gui.action_unlock_suborder(in_suborder_id => l_suborder_id, -- in_owner => in_owner, in_member_groups => in_member_groups, out_return_code => out_return_code, out_return_desc => out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; END LOOP; -- set return values ----------------------------------------------------------- out_return_code := 0; out_return_desc := 'OK'; -------------------------------------------------------------------------------- EXCEPTION WHEN ex_cor_gui THEN NULL; WHEN OTHERS THEN out_return_code := '9118'; out_return_desc := 'An error occurred during execution of stored procedure ACTION_CANCEL_SUBORDER' || ' SQL error code: ' || SQLCODE || '.' || ' SQL error message: ' || SQLERRM; END action_cancel_suborder; PROCEDURE action_reject_suborder ( in_suborder_ids IN suborder_id_cursor, in_owner IN VARCHAR2, in_member_groups IN user_group_cursor, out_return_code OUT VARCHAR2, out_return_desc OUT VARCHAR2 ) AS ex_cor_gui EXCEPTION; l_out_gui_action_hist_id gui_action_history.action_hist_id%TYPE; l_custorder_id customer_order.custorder_id%TYPE; l_suborder_id suborder.suborder_id%TYPE; l_attribute_empty_cursor attribute_array;-- BEGIN -- create empty attribute cursor - is used on mor places l_attribute_empty_cursor:= attribute_array(); -- FOR i IN in_suborder_ids.FIRST .. in_suborder_ids.LAST LOOP -- get custorder_id l_suborder_id := in_suborder_ids(i); l_custorder_id := substr(l_suborder_id, 0, 11); --take first 11 char --authorize action ------------------------------------------------------------- cor_gui.authorize_suborder_action(in_custorder_id => l_custorder_id, in_suborder_id => l_suborder_id, in_action => 'REJECT_SUBORDER', in_owner => in_owner, in_member_groups => in_member_groups, out_return_code => out_return_code, out_return_desc => out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; --Call procedure SET_SUBORDER_STATUS(IN_INTERNAL_STATUS = �Rejected�) cor_order_update.set_suborder_status(in_suborder_id => l_suborder_id, in_modifier => NULL, in_force => 'N', in_internal_status => 'Rejected', in_external_status => NULL,-- in_attribute_cursor => l_attribute_empty_cursor,-- out_return_code => out_return_code, out_return_desc => out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; -- log the action in the action history ---------------------------------------- cor_gui.log_action_history(in_custorder_id => l_custorder_id, in_suborder_id => l_suborder_id, in_owner => in_owner, in_action_hist_desc => 'Suborder rejected', out_gui_action_hist_id => l_out_gui_action_hist_id, out_return_code => out_return_code, out_return_desc => out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; -- unlock SubOrders --------------------------------------------------------------- cor_gui.action_unlock_suborder(in_suborder_id => l_suborder_id, -- in_owner => in_owner, in_member_groups => in_member_groups, out_return_code => out_return_code, out_return_desc => out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; END LOOP; -- set return values ----------------------------------------------------------- out_return_code := 0; out_return_desc := 'OK'; -------------------------------------------------------------------------------- EXCEPTION WHEN ex_cor_gui THEN NULL; WHEN OTHERS THEN out_return_code := '9140'; out_return_desc := 'An error occurred during execution of stored procedure ACTION_REJECT_SUBORDER' || ' SQL error code: ' || SQLCODE || '.' || ' SQL error message: ' || SQLERRM; END action_reject_suborder; PROCEDURE action_suborder_manually_done ( in_suborder_ids IN suborder_id_cursor, in_owner IN VARCHAR2, in_member_groups IN user_group_cursor, out_return_code OUT VARCHAR2, out_return_desc OUT VARCHAR2 ) AS ex_cor_gui EXCEPTION; l_out_gui_action_hist_id gui_action_history.action_hist_id%TYPE; l_custorder_id customer_order.custorder_id%TYPE; l_suborder_id suborder.suborder_id%TYPE; l_attribute_empty_cursor attribute_array;-- BEGIN -- create empty attribute cursor - is used on mor places l_attribute_empty_cursor:= attribute_array(); -- FOR i IN in_suborder_ids.FIRST .. in_suborder_ids.LAST LOOP -- get custorder_id l_suborder_id := in_suborder_ids(i); l_custorder_id := substr(l_suborder_id, 0, 11); --take first 11 char --authorize action ------------------------------------------------------------- cor_gui.authorize_suborder_action(in_custorder_id => l_custorder_id, in_suborder_id => l_suborder_id, in_action => 'SUBORDER_MANUALLY_DONE', in_owner => in_owner, in_member_groups => in_member_groups, out_return_code => out_return_code, out_return_desc => out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; -- update status to 'Closed' ------------------------------------------------- cor_order_update.set_suborder_status(in_suborder_id => l_suborder_id, in_modifier => NULL, in_force => 'N', in_internal_status => 'Closed', in_external_status => NULL,-- in_attribute_cursor => l_attribute_empty_cursor,-- out_return_code => out_return_code, out_return_desc => out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; -- log the action in the action history ---------------------------------------- cor_gui.log_action_history(in_custorder_id => l_custorder_id, in_suborder_id => l_suborder_id, in_owner => in_owner, in_action_hist_desc => 'Suborder manually done', out_gui_action_hist_id => l_out_gui_action_hist_id, out_return_code => out_return_code, out_return_desc => out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; -- unlock SubOrders --------------------------------------------------------------- cor_gui.action_unlock_suborder(in_suborder_id => l_suborder_id, -- in_owner => in_owner, in_member_groups => in_member_groups, out_return_code => out_return_code, out_return_desc => out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; END LOOP; -- set return values ----------------------------------------------------------- out_return_code := 0; out_return_desc := 'OK'; -------------------------------------------------------------------------------- EXCEPTION WHEN ex_cor_gui THEN NULL; WHEN OTHERS THEN out_return_code := '9140'; out_return_desc := 'An error occurred during execution of stored procedure ACTION_SUBORDER_MANUALLY_DONE' || ' SQL error code: ' || SQLCODE || '.' || ' SQL error message: ' || SQLERRM; END action_suborder_manually_done; PROCEDURE action_save_suborder_note ( in_suborder_ids IN suborder_id_cursor, in_note IN VARCHAR2, in_owner IN VARCHAR2, in_member_groups IN user_group_cursor, out_return_code OUT VARCHAR2, out_return_desc OUT VARCHAR2 ) AS ex_cor_gui EXCEPTION; l_note_id note.note_id%TYPE; l_out_gui_action_hist_id gui_action_history.action_hist_id%TYPE; l_custorder_id customer_order.custorder_id%TYPE; l_suborder_id suborder.suborder_id%TYPE; BEGIN FOR i IN in_suborder_ids.FIRST .. in_suborder_ids.LAST LOOP -- get custorder_id l_suborder_id := in_suborder_ids(i); l_custorder_id := substr(l_suborder_id, 0, 11); --take first 11 char --authorize action ------------------------------------------------------------- cor_gui.authorize_suborder_action(in_custorder_id => l_custorder_id, in_suborder_id => l_suborder_id, in_action => 'SAVE_SUBORDER_NOTE', in_owner => in_owner, in_member_groups => in_member_groups, out_return_code => out_return_code, out_return_desc => out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; -- log the action in the action history ---------------------------------------- cor_gui.log_action_history(in_custorder_id => l_custorder_id, in_suborder_id => l_suborder_id, in_owner => in_owner, in_action_hist_desc => 'Suborder note saved', out_gui_action_hist_id => l_out_gui_action_hist_id, out_return_code => out_return_code, out_return_desc => out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; --Call procedure INSERT_NOTE cor_order_creation.insert_note(in_custorder_id => l_custorder_id, in_suborder_id => l_suborder_id, out_note_id => l_note_id, in_short_description => 'GUI NOTE', in_message => in_note, in_gui_action_hist_id => l_out_gui_action_hist_id, out_return_code => out_return_code, out_return_desc => out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; END LOOP; -- set return values ----------------------------------------------------------- out_return_code := 0; out_return_desc := 'OK'; -------------------------------------------------------------------------------- EXCEPTION WHEN ex_cor_gui THEN NULL; WHEN OTHERS THEN out_return_code := '9170'; out_return_desc := 'An error occurred during execution of stored procedure ACTION_SAVE_SUBORDER_NOTE' || ' SQL error code: ' || SQLCODE || '.' || ' SQL error message: ' || SQLERRM; END action_save_suborder_note; PROCEDURE action_cancel_line ( in_line_ids IN line_id_cursor, in_owner IN VARCHAR2, in_member_groups IN user_group_cursor, out_return_code OUT VARCHAR2, out_return_desc OUT VARCHAR2 ) AS ex_cor_gui EXCEPTION; l_line_id line.order_line_id%TYPE; l_custorder_id customer_order.custorder_id%TYPE; l_suborder_id suborder.suborder_id%TYPE; l_out_gui_action_hist_id gui_action_history.action_hist_id%TYPE; l_act_hist gui_action_history.action_hist_desc%TYPE; l_attribute_empty_cursor attribute_array;-- BEGIN -- create empty attribute cursor - is used on mor places l_attribute_empty_cursor:= attribute_array(); -- FOR i IN in_line_ids.FIRST .. in_line_ids.LAST LOOP -- get custorder_id and suborder_id l_line_id := in_line_ids(i); SELECT l.suborder_id INTO l_suborder_id FROM line l WHERE l.order_line_id = l_line_id; l_custorder_id := substr(l_suborder_id, 0, 11); --take first 11 char --authorize action ------------------------------------------------------------- cor_gui.authorize_suborder_action(in_custorder_id => l_custorder_id, in_suborder_id => l_suborder_id, in_action => 'CANCEL_LINE', in_owner => in_owner, in_member_groups => in_member_groups, out_return_code => out_return_code, out_return_desc => out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; -- update status to 'Canceled' ------------------------------------------------- cor_order_update.set_customer_order_line_status(in_custorder_id => l_custorder_id, in_suborder_id => l_suborder_id, in_line_id => l_line_id, in_modifier => NULL, in_force => 'N', in_public_status => 'Abandoned', in_internal_status => 'Canceled', in_external_status => NULL, -- in_attribute_cursor => l_attribute_empty_cursor,-- out_return_code => out_return_code, out_return_desc => out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; -- log the action in the action history ---------------------------------------- l_act_hist := 'Line ' || l_line_id || ' cancelled'; cor_gui.log_action_history(in_custorder_id => l_custorder_id, in_suborder_id => l_suborder_id, in_owner => in_owner, in_action_hist_desc => l_act_hist, out_gui_action_hist_id => l_out_gui_action_hist_id, out_return_code => out_return_code, out_return_desc => out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; -- unlock suborder --------------------------------------------------------------- cor_gui.action_unlock_suborder(in_suborder_id => l_suborder_id, -- in_owner => in_owner, in_member_groups => in_member_groups, out_return_code => out_return_code, out_return_desc => out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; END LOOP; -- set return values ----------------------------------------------------------- out_return_code := 0; out_return_desc := 'OK'; -------------------------------------------------------------------------------- EXCEPTION WHEN ex_cor_gui THEN NULL; WHEN OTHERS THEN out_return_code := '9117'; out_return_desc := 'An error occurred during execution of stored procedure ACTION_CANCEL_LINE' || ' SQL error code: ' || SQLCODE || '.' || ' SQL error message: ' || SQLERRM; END action_cancel_line; PROCEDURE action_line_manually_done ( in_line_ids IN line_id_cursor, in_owner IN VARCHAR2, in_member_groups IN user_group_cursor, out_return_code OUT VARCHAR2, out_return_desc OUT VARCHAR2 ) AS ex_cor_gui EXCEPTION; l_out_gui_action_hist_id gui_action_history.action_hist_id%TYPE; l_custorder_id customer_order.custorder_id%TYPE; l_suborder_id suborder.suborder_id%TYPE; l_line_id line.order_line_id%TYPE; l_act_hist gui_action_history.action_hist_desc%TYPE; l_attribute_empty_cursor attribute_array;-- BEGIN -- create empty attribute cursor - is used on mor places l_attribute_empty_cursor:= attribute_array(); -- FOR i IN in_line_ids.FIRST .. in_line_ids.LAST LOOP -- get custorder_id and suborder_id l_line_id := in_line_ids(i); SELECT l.suborder_id INTO l_suborder_id FROM line l WHERE l.order_line_id = l_line_id; l_custorder_id := substr(l_suborder_id, 0, 11); --take first 11 char --authorize action ------------------------------------------------------------- cor_gui.authorize_suborder_action(in_custorder_id => l_custorder_id, in_suborder_id => l_suborder_id, in_action => 'LINE_MANUALLY_DONE', in_owner => in_owner, in_member_groups => in_member_groups, out_return_code => out_return_code, out_return_desc => out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; -- update status to 'Closed' ------------------------------------------------- cor_order_update.set_customer_order_line_status(in_custorder_id => l_custorder_id, in_suborder_id => l_suborder_id, in_line_id => l_line_id, in_modifier => NULL, in_force => 'N', in_public_status => 'Terminated', in_internal_status => 'Delivered', in_external_status => NULL, -- in_attribute_cursor => l_attribute_empty_cursor,-- out_return_code => out_return_code, out_return_desc => out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; -- log the action in the action history ---------------------------------------- l_act_hist := 'Line ' || l_line_id || ' manually done'; cor_gui.log_action_history(in_custorder_id => l_custorder_id, in_suborder_id => l_suborder_id, in_owner => in_owner, in_action_hist_desc => l_act_hist, out_gui_action_hist_id => l_out_gui_action_hist_id, out_return_code => out_return_code, out_return_desc => out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; -- unlock SubOrder ------------------------------------------------------------- cor_gui.action_unlock_suborder(in_suborder_id => l_suborder_id, -- in_owner => in_owner, in_member_groups => in_member_groups, out_return_code => out_return_code, out_return_desc => out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; END LOOP; -- set return values ----------------------------------------------------------- out_return_code := 0; out_return_desc := 'OK'; -------------------------------------------------------------------------------- EXCEPTION WHEN ex_cor_gui THEN NULL; WHEN OTHERS THEN out_return_code := '9140'; out_return_desc := 'An error occurred during execution of stored procedure ACTION_LINE_MANUALLY_DONE' || ' SQL error code: ' || SQLCODE || '.' || ' SQL error message: ' || SQLERRM; END action_line_manually_done; -- -- PROCEDURE action_inspect_order_manually ( in_custorder_ids IN custorder_id_cursor, in_owner IN VARCHAR2, in_member_groups IN user_group_cursor, out_return_code OUT VARCHAR2, out_return_desc OUT VARCHAR2 ) AS ex_cor_gui EXCEPTION; l_out_gui_action_hist_id NUMBER; l_attribute_empty_cursor attribute_array;-- l_in_member_groups_unlock user_group_cursor; --23.01.04 l_assigned_user_group_id customer_order.assigned_user_group_id%TYPE; --23.01.04 l_tmp_cnt NUMBER;--23.01.04 BEGIN -- create empty attribute cursor - is used on mor places l_attribute_empty_cursor:= attribute_array(); -- FOR i IN in_custorder_ids.FIRST .. in_custorder_ids.LAST LOOP --authorize action ------------------------------------------------------------- cor_gui.authorize_action(in_custorder_id => in_custorder_ids(i), in_action => 'INSPECT_ORDER', in_owner => in_owner, in_member_groups => in_member_groups, out_return_code => out_return_code, out_return_desc => out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; -- update customer_order table column manual_inspection UPDATE customer_order SET manual_inspection = 'Inspected' WHERE custorder_id = in_custorder_ids(i); -- log the action in the action history -------------------------------------- cor_gui.log_action_history(in_custorder_id => in_custorder_ids(i), in_suborder_id => NULL, in_owner => in_owner, in_action_hist_desc => 'Order inspected manually', out_gui_action_hist_id => l_out_gui_action_hist_id, out_return_code => out_return_code, out_return_desc => out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; cor_order_update.set_customer_order_status(in_custorder_id => in_custorder_ids(i), in_modifier => NULL, in_force => 'N', in_origin => NULL, -- in_public_status => 'Acceptance', in_internal_status => 'To Validation', in_attribute_cursor => l_attribute_empty_cursor,-- out_return_code => out_return_code, out_return_desc => out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; -- unlock orders --------------------------------------------------------------- --extend memeber groups with the current assigned department of the request (assigned_user_group_id in customer_order table). --23.01.04 l_in_member_groups_unlock := in_member_groups; select assigned_user_group_id into l_assigned_user_group_id from customer_order where custorder_id = in_custorder_ids(i); select count(1) into l_tmp_cnt from TABLE(l_in_member_groups_unlock) where column_value = l_assigned_user_group_id; IF (l_tmp_cnt = 0) THEN l_in_member_groups_unlock.extend; l_in_member_groups_unlock(l_in_member_groups_unlock.LAST) := l_assigned_user_group_id; END IF; --23.01.04 cor_gui.action_unlock_orders_id(in_custorder_id => in_custorder_ids(i), in_owner => in_owner, in_member_groups => l_in_member_groups_unlock,--23.01.04 out_return_code => out_return_code, out_return_desc => out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; END LOOP; -- set return values ----------------------------------------------------------- out_return_code := 0; out_return_desc := 'OK'; -------------------------------------------------------------------------------- EXCEPTION WHEN ex_cor_gui THEN NULL; WHEN OTHERS THEN out_return_code := '9133'; out_return_desc := 'An error occurred during execution of stored procedure ACTION_INSPECTED_ORDER' || ' SQL error code: ' || SQLCODE || '.' || ' SQL error message: ' || SQLERRM; END action_inspect_order_manually; -- -- PROCEDURE action_resubmit_suborder -- action_accept_suborder ( in_suborder_ids IN suborder_id_cursor, in_custorder_id IN VARCHAR2, -- in_owner IN VARCHAR2, in_member_groups IN user_group_cursor, out_return_code OUT VARCHAR2, out_return_desc OUT VARCHAR2 ) AS ex_cor_gui EXCEPTION; --9211 --ex_valid_resubmit EXCEPTION; --9212 --23.01.22 WTR648 - COR DB - a check should be delete in action_resubmit_suborder -- ex_no_automated_del EXCEPTION; --9213 -- ex_lock EXCEPTION; --9214 -- ex_valid EXCEPTION; --9215 -- ex_must_status EXCEPTION; --9216 ex_never_pass_validation EXCEPTION; --9217 -- --l_count INT; --23.01.22 WTR648 - COR DB - a check should be delete in action_resubmit_suborder l_suborder_id suborder.suborder_id%TYPE; l_out_gui_action_hist_id gui_action_history.action_hist_id%TYPE; -- l_lock_status customer_order.ownership%TYPE; -- --l_suborders INT; cleanup l_cust_status customer_order.status_internal%TYPE; -- l_attribute_empty_cursor attribute_array;-- l_countsubvaloraccstatus NUMBER; -- l_custorder_type_id customer_order.custorder_type_id%TYPE; -- BEGIN -- create empty attribute cursor - is used on mor places l_attribute_empty_cursor:= attribute_array(); -- -- SELECT custorder_type_id INTO l_custorder_type_id FROM customer_order co WHERE co.custorder_id = in_custorder_id; IF (l_custorder_type_id = 'Combined Order') THEN SELECT COUNT(1) INTO l_countsubvaloraccstatus FROM status_history WHERE (custorder_id = in_custorder_id AND (status_internal = 'Suborder Validation' OR status_internal = 'Accepted')); IF (l_countsubvaloraccstatus = 0) THEN RAISE ex_never_pass_validation; END IF; END IF; -- -- code disabled -- -- -- SELECT COUNT(*) -- INTO l_count -- FROM status_history -- WHERE custorder_id = in_custorder_id -- AND suborder_id IS NULL -- AND order_line_id IS NULL -- AND status_internal = 'Accepted'; -- IF l_count = 0 -- THEN -- RAISE ex_valid; --Error_Code = 9215 -- END IF; -- -- -- SELECT ownership INTO l_lock_status FROM customer_order WHERE custorder_id = in_custorder_id; IF (l_lock_status IS NULL OR l_lock_status = '') THEN NULL; ELSE RAISE ex_lock; -- ErrorCode = 9214 END IF; -- -- -- 4. FOR EACH SUBORDER_ID in the input cursor: FOR i IN in_suborder_ids.FIRST .. in_suborder_ids.LAST LOOP -- get custorder_id l_suborder_id := in_suborder_ids(i); --authorize action ------------------------------------------------------------- cor_gui.authorize_suborder_action(in_custorder_id => in_custorder_id, in_suborder_id => l_suborder_id, in_action => 'RESUBMIT_SUBORDER', in_owner => in_owner, in_member_groups => in_member_groups, out_return_code => out_return_code, out_return_desc => out_return_desc); IF out_return_code != 0 THEN ROLLBACK; -- RAISE ex_cor_gui; END IF; /* If there is no record in the status_history table with suborder_id = current suborder_id, line is null and status_inernal = Accepted Exit with error code 9212 (SubOrder did not pass validation successfully) */ --23.01.22 WTR648 - COR DB - a check should be delete in action_resubmit_suborder -- SELECT COUNT(*) -- INTO l_count -- FROM status_history -- WHERE suborder_id = l_suborder_id -- AND status_internal = 'Accepted' -- AND order_line_id IS NULL; -- THEN -- ROLLBACK; -- -- RAISE ex_valid_resubmit; --Error_Code = 9212 -- END IF; --23.01.22 WTR648 - COR DB - a check should be delete in action_resubmit_suborder -- code disabled -- cor_order_update.set_suborder_status(in_suborder_id => l_suborder_id, -- in_modifier => NULL, -- -- in_force => 'N', -- in_internal_status => 'Accepted', -- in_external_status => NULL,-- -- in_attribute_cursor => l_attribute_empty_cursor,-- -- out_return_code => out_return_code, -- out_return_desc => out_return_desc); -- IF out_return_code != 0 -- THEN -- ROLLBACK; -- -- RAISE ex_cor_gui; -- END IF; -- -- log the action in the action history ---------------------------------------- cor_gui.log_action_history(in_custorder_id => in_custorder_id, in_suborder_id => l_suborder_id, in_owner => in_owner, in_action_hist_desc => 'SubOrder resubmitted',-- out_gui_action_hist_id => l_out_gui_action_hist_id, out_return_code => out_return_code, out_return_desc => out_return_desc); IF out_return_code != 0 THEN ROLLBACK; -- RAISE ex_cor_gui; END IF; -- call procedure ACTION_UNLOCK_SUBORDER cor_gui.action_unlock_suborder(in_suborder_id => l_suborder_id, in_owner => in_owner, in_member_groups => in_member_groups, out_return_code => out_return_code, out_return_desc => out_return_desc); IF out_return_code != 0 THEN ROLLBACK; -- RAISE ex_cor_gui; END IF; END LOOP; -- code disabled -- -- 5. Select into L_SubOrders the following: count(1) from suborder -- -- -- SELECT COUNT(1) -- INTO l_suborders -- FROM suborder -- WHERE custorder_id = in_custorder_id -- AND status_internal = 'Accepted' -- AND is_automatic_delivery = 'true' -- AND suborder_purpose_id = 'Execution'; -- IF l_suborders = 0 -- THEN -- ROLLBACK; -- RAISE ex_no_automated_del; --9213 -- END IF; -- --Else l_suborders!= 0 SELECT status_internal INTO l_cust_status FROM customer_order WHERE custorder_id = in_custorder_id; IF l_cust_status IN ('Awaiting Agent', 'Delivery Failed', 'Accepted', 'Delivering') THEN NULL; ELSE ROLLBACK; RAISE ex_must_status; --9216 END IF; -- call check_order_lock SELECT c.ownership INTO l_lock_status FROM customer_order c WHERE c.custorder_id = in_custorder_id; -- IF (l_lock_status IS NULL OR l_lock_status = '') THEN l_lock_status := 'UNLOCKED'; ELSE RAISE ex_cor_gui; END IF; /*cor_gui.check_order_lock(in_custorder_id => in_custorder_id, in_owner => in_owner, out_lock_status => l_lock_status, out_return_code => out_return_code, out_return_desc => out_return_desc); IF out_return_code != 0 THEN ROLLBACK; RAISE ex_cor_gui; END IF;*/ -- IF l_lock_status = 'UNLOCKED' THEN COMMIT; cor_order_update.set_suborder_status(in_suborder_id => l_suborder_id, in_modifier => NULL, in_force => 'N', in_internal_status => 'To Validation',-- in_external_status => NULL,-- in_attribute_cursor => l_attribute_empty_cursor,-- out_return_code => out_return_code, out_return_desc => out_return_desc); IF out_return_code != 0 THEN ROLLBACK; RAISE ex_cor_gui; END IF; ELSE ROLLBACK; RAISE ex_lock; -- ErrorCode = 9214 END IF; -- -- set return values ----------------------------------------------------------- out_return_code := 0; out_return_desc := 'OK'; -------------------------------------------------------------------------------- EXCEPTION WHEN ex_cor_gui THEN NULL; --23.01.22 WTR648 - COR DB - a check should be delete in action_resubmit_suborder -- WHEN ex_valid_resubmit THEN -- out_return_code := '9212'; -- out_return_desc := 'One or more of the selected SubOrders did never pass validation successfully; resubmit the order to the order validation module at order level'; --23.01.22 -- -- WHEN ex_no_automated_del THEN -- out_return_code := '9213'; -- out_return_desc := 'None of the selected SubOrders is eligible for automated delivery'; -- WHEN ex_lock THEN out_return_code := '9214'; out_return_desc := 'Resubmitting SubOrders is only possible in case there is no lock on order level.';-- -- -- WHEN ex_valid THEN -- out_return_code := '9215'; -- out_return_desc := 'Order did never pass validation successfully'; -- WHEN ex_must_status THEN out_return_code := '9216'; out_return_desc := 'Order must be in status Awaiting Agent, Delivery Failed, Accepted or Delivering to resubmit SubOrders to the Delivery Layer'; -- WHEN ex_never_pass_validation THEN out_return_code := '9216'; out_return_desc := 'Order must be in status Awaiting Agent, Delivery Failed, Accepted or Delivering to resubmit SubOrders to the Delivery Layer'; -- WHEN OTHERS THEN out_return_code := '9211'; out_return_desc := 'An error occurred during execution of stored procedure ACTION_RESUBMIT_SUBORDER' || ' SQL error code: ' || SQLCODE || '.' || ' SQL error message: ' || SQLERRM; END action_resubmit_suborder; -- --23.01.12 PROCEDURE action_accept_suborder ( in_suborder_ids IN suborder_id_cursor, in_owner IN VARCHAR2, in_member_groups IN user_group_cursor, out_return_code OUT VARCHAR2, out_return_desc OUT VARCHAR2 ) AS ex_cor_gui EXCEPTION; l_suborder_id suborder.suborder_id%TYPE; l_out_gui_action_hist_id gui_action_history.action_hist_id%TYPE; l_attribute_empty_cursor attribute_array; l_custorder_id customer_order.custorder_id%TYPE; BEGIN -- create empty attribute cursor - is used on mor places l_attribute_empty_cursor := attribute_array(); -- FOR EACH SUBORDER_ID in the input cursor: FOR i IN in_suborder_ids.FIRST .. in_suborder_ids.LAST LOOP -- get custorder_id l_suborder_id := in_suborder_ids(i); -- derive custorder_id ----------------------------------------------------------- l_custorder_id := substr(l_suborder_id, 0, 11); --authorize action ------------------------------------------------------------- cor_gui.authorize_suborder_action(in_custorder_id => l_custorder_id, in_suborder_id => l_suborder_id, in_action => 'ACCEPT_SUBORDER', in_owner => in_owner, in_member_groups => in_member_groups, out_return_code => out_return_code, out_return_desc => out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; -- update suborder status cor_order_update.set_suborder_status(in_suborder_id => l_suborder_id, in_modifier => NULL, in_force => 'N', in_internal_status => 'Accepted', in_external_status => NULL, in_attribute_cursor => l_attribute_empty_cursor, out_return_code => out_return_code, out_return_desc => out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; -- cor_order_update.clear_suborder_error_messages(in_custorder_id => l_custorder_id, in_suborder_id => l_suborder_id, out_return_code => out_return_code, out_return_desc => out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; -- --23.01.28 cor_order_update.clear_header_error_messages(in_custorder_id => l_custorder_id, out_return_code => out_return_code, out_return_desc => out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; --23.01.28 -- cascade status to ordelines FOR k IN (SELECT order_line_id FROM line WHERE suborder_id = l_suborder_id) LOOP cor_order_update.set_customer_order_line_status(in_custorder_id => l_custorder_id, in_suborder_id => l_suborder_id, in_line_id => k.order_line_id, in_modifier => NULL, in_force => 'N', in_public_status => 'In Process', in_internal_status => 'Accepted', in_external_status => NULL, in_attribute_cursor => l_attribute_empty_cursor, -- out_return_code => out_return_code, out_return_desc => out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; -- cor_order_update.clear_line_error_messages(in_custorder_id => l_custorder_id, in_order_line_id => k.order_line_id, in_suborder_id => l_suborder_id, out_return_code => out_return_code, out_return_desc => out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; -- END LOOP; -- log the action in the action history ---------------------------------------- cor_gui.log_action_history(in_custorder_id => l_custorder_id, in_suborder_id => l_suborder_id, in_owner => in_owner, in_action_hist_desc => 'SubOrder Accepted', out_gui_action_hist_id => l_out_gui_action_hist_id, out_return_code => out_return_code, out_return_desc => out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; -- call procedure ACTION_UNLOCK_SUBORDER cor_gui.action_unlock_suborder(in_suborder_id => l_suborder_id, in_owner => in_owner, in_member_groups => in_member_groups, out_return_code => out_return_code, out_return_desc => out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; END LOOP; -- set return values ----------------------------------------------------------- out_return_code := 0; out_return_desc := 'OK'; -------------------------------------------------------------------------------- EXCEPTION WHEN ex_cor_gui THEN NULL; WHEN OTHERS THEN out_return_code := '9219'; out_return_desc := 'An error occurred during execution of stored procedure ACTION_ACCEPT_SUBORDER' || ' SQL error code: ' || SQLCODE || '.' || ' SQL error message: ' || SQLERRM; END action_accept_suborder; --23.01.12 -- PROCEDURE set_gui_fields ( in_custorder_id IN VARCHAR2, out_return_code OUT VARCHAR2, out_return_desc OUT VARCHAR2 ) AS l_sub_subordertype suborder.suborder_type%TYPE; l_gui_type line.gui_type%TYPE; l_attribute_value_card line_attribute.attribute_value%TYPE; l_attribute_value_origin line_attribute.attribute_value%TYPE; l_attribute_value_old_oper line_attribute.attribute_value%TYPE; l_attribute_value_other line_attribute.attribute_value%TYPE; l_row VARCHAR2(10); l_offer_desc line.offer_desc%TYPE; l_offer_id line.offer_id%TYPE; --l_desired_delivery_date line.desired_delivery_dttm%TYPE; -- -- l_num_contracts INT; -- l_main_order_line line.order_line_id%TYPE; -- s_gui_type line.gui_type%TYPE; -- BEGIN FOR i IN (SELECT order_line_id FROM line l WHERE custorder_id = in_custorder_id) LOOP BEGIN SELECT suborder_type INTO l_sub_subordertype FROM suborder WHERE suborder_id = (SELECT suborder_id FROM line WHERE order_line_id = i.order_line_id); EXCEPTION WHEN no_data_found THEN l_sub_subordertype := NULL; l_gui_type := 'Other'; END; IF l_sub_subordertype = 'New Activation' THEN SELECT offer_desc, offer_id INTO l_offer_desc, l_offer_id FROM line WHERE order_line_id = i.order_line_id; IF l_offer_desc = l_offer_id THEN -- select line_attribute in Name is 'Type of card', 'Origin of the number', -- 'Type of card (at old operator)', 'Customer Type at other operator' BEGIN SELECT attribute_value INTO l_attribute_value_card FROM line_attribute WHERE attribute_name = 'Type of card' AND order_line_id = i.order_line_id; EXCEPTION WHEN no_data_found THEN l_attribute_value_card := NULL; WHEN too_many_rows THEN l_row := 'Many'; END; BEGIN SELECT attribute_value INTO l_attribute_value_origin FROM line_attribute WHERE attribute_name = 'Origin of the number' AND order_line_id = i.order_line_id; EXCEPTION WHEN no_data_found THEN l_attribute_value_origin := NULL; WHEN too_many_rows THEN l_row := 'Many'; END; BEGIN SELECT attribute_value INTO l_attribute_value_old_oper FROM line_attribute WHERE attribute_name = 'Type of card (at old operator)' AND order_line_id = i.order_line_id; EXCEPTION WHEN no_data_found THEN l_attribute_value_old_oper := NULL; WHEN too_many_rows THEN l_row := 'Many'; END; BEGIN SELECT attribute_value INTO l_attribute_value_other FROM line_attribute WHERE attribute_name = 'Customer Type at other operator' AND order_line_id = i.order_line_id; EXCEPTION WHEN no_data_found THEN l_attribute_value_other := NULL; WHEN too_many_rows THEN l_row := 'Many'; END; IF l_row = 'Many' --if one of select fails because of too many rows l_gui_type => "Other" THEN l_attribute_value_card := 'Too many rows'; END IF; -- check attribute_value_card IF (l_attribute_value_card = 'POSTPAID' OR l_attribute_value_card = 'PREPAID' OR l_attribute_value_card = 'Tempo Recharging') THEN -- check attribute_value_origin SELECT decode(l_attribute_value_origin, NULL, -- else if value_origin doesn't exist 'Mobile', 'NEW NUMBER', 'Mobile - New Number', 'EXISTING NUMBER', 'Mobile - Existing Number', 'TEMPO MIGRATION', 'Mobile - Tempo Migration', 'Pseudo-MNP', 'Mobile - MNP Pseudo', 'MNP', -- check attribute_value_old_oper decode(l_attribute_value_old_oper, NULL, -- else if old operator doesn't exist 'Mobile - MNP', 'PRE-PAID', 'Mobile - MNP Prepaid', 'PREPAID', 'Mobile - MNP Prepaid', 'POST-PAID', 'Mobile - MNP Postpaid', 'POSTPAID', 'Mobile - MNP Postpaid', 'Mobile - MNP'), -- default for value_old_oper 'Mobile') -- default for value_origin INTO l_gui_type FROM dual; ELSIF l_attribute_value_card = 'FIX' THEN -- check attribute_value_origin SELECT decode(l_attribute_value_origin, NULL, --else, if doesn't exist --l_gui_type 'Fix', 'NEW NUMBER', 'Fix - New Number', 'EXISTING NUMBER', -- check attribute_value_other decode(l_attribute_value_other, NULL, -- else if value_other doesn't exist 'Fix - Existing Number', 'Fix - FNP'), -- default for value_other (if exists) 'Fix') --default for value_origin INTO l_gui_type FROM dual; ELSIF l_attribute_value_card = 'DATA' THEN l_gui_type := 'Data'; ELSIF l_attribute_value_card = 'VOIP' THEN -- check attribute_value_origin SELECT decode(l_attribute_value_origin, NULL, --- else for origin VOIP (doesn't exist) decode(l_attribute_value_other, -- NULL, -- else for value_other 'VoIP', 'VoIP - FNP'), -- default for value_other, 'NEW NUMBER', 'VoIP - New Number', 'EXISTING NUMBER', -- check attribute_value_other decode(l_attribute_value_other, NULL, -- else for value_other 'VoIP - Existing Number', 'VoIP - FNP'), -- default for value_other 'VoIP') --default for origin INTO l_gui_type FROM dual; ELSE -- default (if not DATA, FIX, VOIP, POSTPAI, PREPAID, Tempo Recharging) -- esle if l_attribute_value_card doesn't exist l_gui_type := 'Other'; END IF; -- end if l_attribute_value_card -- UPDATE line SET gui_type = l_gui_type WHERE order_line_id = i.order_line_id; -- END IF; -- end if g_line_tab(i).offer_id = g_line_tab(i).offer_desc END IF; -- end if l_sub_subordertype = 'New Activation' -- iii. Update RECORD.GUI_TYPE with L_GUI_TYPE END LOOP; -- --- SELECT LINE.GUI_TYPE FROM LINE based on CUSTORDER_ID and gui_type --- SELECT LINE.GUI_TYPE INTO l_gui_type FROM LINE WHERE CUSTORDER_ID = in_custorder_id AND ROWNUM = 1 AND GUI_TYPE is not null ORDER BY ORDER_LINE_ID ASC; --- SELECT ID FROM ENUM_PRODUCT_TYPE based on GROUP_LABEL --- SELECT ID INTO s_gui_type FROM ENUM_PRODUCT_TYPE WHERE GROUP_LABEL = l_gui_type; --- Update suborder table with gui_type based on custorder_id --- UPDATE SUBORDER SET GUI_TYPE = s_gui_type WHERE CUSTORDER_ID = in_custorder_id; -- -- BEGIN SELECT offer_desc INTO l_main_order_line FROM (SELECT offer_desc FROM line WHERE custorder_id = in_custorder_id ORDER BY order_line_id) t WHERE rownum = 1; SELECT COUNT(*) INTO l_num_contracts FROM line WHERE custorder_id = in_custorder_id AND offer_id = offer_desc; EXCEPTION WHEN no_data_found THEN l_main_order_line := NULL; l_num_contracts := 0; END; -- UPDATE cor_gui_centre SET installed_base_owner_bscs_code = (SELECT MIN(VALUE) FROM customer_retrieval_key WHERE key_type = 'BSCS Customer Number' AND customer_id IN (SELECT DISTINCT installed_base_owner_id FROM line WHERE custorder_id = in_custorder_id) AND custorder_id = in_custorder_id), main_order_line = l_main_order_line, -- number_of_contracts = l_num_contracts -- WHERE custorder_id = in_custorder_id; -- /* -- -- 3. Determine Desired Delivery Date l_desired_delivery_date := NULL; BEGIN SELECT desired_delivery_dttm INTO l_desired_delivery_date FROM line l WHERE l.custorder_id = in_custorder_id AND l.desired_delivery_dttm IS NOT NULL AND l.gui_type LIKE '%Mobile%' AND rownum = 1 ORDER BY l.ORDER_LINE_ID ASC; EXCEPTION WHEN no_data_found THEN NULL; END; IF (l_desired_delivery_date IS NULL) THEN BEGIN SELECT desired_delivery_dttm INTO l_desired_delivery_date FROM line l WHERE l.custorder_id = in_custorder_id AND l.desired_delivery_dttm IS NOT NULL AND rownum = 1 ORDER BY l.ORDER_LINE_ID ASC; EXCEPTION WHEN no_data_found THEN NULL; END; END IF; UPDATE cor_gui_centre SET desired_delivery_date = l_desired_delivery_date WHERE custorder_id = in_custorder_id; -- */ -- -- set return values ----------------------------------------------------------- out_return_code := 0; out_return_desc := 'OK'; -------------------------------------------------------------------------------- EXCEPTION WHEN OTHERS THEN out_return_code := '9071'; out_return_desc := 'An error occurred during execution of stored procedure SET_GUI_FIELDS' || ' SQL error code: ' || SQLCODE || '.' || ' SQL error message: ' || SQLERRM; END set_gui_fields; -- -- FUNCTION get_suborder_ids(in_custorder_id IN VARCHAR2) RETURN suborder_id_cursor IS p_cur suborder_id_cursor := suborder_id_cursor(); BEGIN FOR i IN (SELECT s.suborder_id FROM suborder s WHERE s.custorder_id = in_custorder_id) LOOP p_cur.EXTEND; p_cur(p_cur.LAST) := i.suborder_id; END LOOP; RETURN(p_cur); END get_suborder_ids; -- -- ---This stored procedure reassigns the selected Suborder to a different department ----- PROCEDURE action_reassign_suborder ( in_suborder_id IN VARCHAR2, in_custorder_id IN VARCHAR2, in_department IN VARCHAR2, in_owner IN VARCHAR2, in_member_groups IN user_group_cursor, out_return_code OUT VARCHAR2, out_return_desc OUT VARCHAR2 )AS ex_cor_gui EXCEPTION; l_out_gui_action_hist_id gui_action_history.action_hist_id%TYPE; BEGIN --authorize action ------------------------------------------------------------- cor_gui.authorize_suborder_action(in_custorder_id => in_custorder_id, in_suborder_id => in_suborder_id, in_action => 'REASSIGN_SUBORDER', in_owner => in_owner, in_member_groups => in_member_groups, out_return_code => out_return_code, out_return_desc => out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; -- update group ---------------------------------------------------------------- UPDATE suborder SET assigned_department_id = in_department WHERE suborder_id = in_suborder_id AND custorder_id = in_custorder_id; -- log the action in the action history ---------------------------------------- cor_gui.log_action_history(in_custorder_id => in_custorder_id, in_suborder_id => in_suborder_id, in_owner => in_owner, in_action_hist_desc => 'Suborder re-assigned', out_gui_action_hist_id => l_out_gui_action_hist_id, out_return_code => out_return_code, out_return_desc => out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; -- stop order treatment -------------------------------------------------------- cor_gui.treat_suborders_stop(in_suborder_id => in_suborder_id, in_owner => in_owner, in_log_hist =>'Y', out_return_code =>out_return_code, out_return_desc =>out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; -- unlock order ------------------------------------------------------------------- cor_order_locking.release_suborder_ownership_gui(in_suborder_id => in_suborder_id, in_owner => in_owner, in_force => 'N', out_return_code => out_return_code, out_return_desc => out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; -- log unlock action in the action history ---------------------------------------- cor_gui.log_action_history(in_custorder_id => in_custorder_id, in_suborder_id => in_suborder_id, in_owner => in_owner, in_action_hist_desc => 'Unlocked Suborder', out_gui_action_hist_id => l_out_gui_action_hist_id, out_return_code => out_return_code, out_return_desc => out_return_desc); IF out_return_code != 0 THEN RAISE ex_cor_gui; END IF; -- set return values ----------------------------------------------------------- out_return_code := 0; out_return_desc := 'OK'; -------------------------------------------------------------------------------- EXCEPTION WHEN ex_cor_gui THEN NULL; WHEN OTHERS THEN out_return_code := '9149'; out_return_desc := 'An error occurred during execution of stored procedure ACTION_REASSIGN_SUBORDER' || ' SQL error code: ' || SQLCODE || '.' || ' SQL error message: ' || SQLERRM; END action_reassign_suborder; -- END cor_gui; /