1 PACKAGE csr_rules_pvt AUTHID CURRENT_USER AS
2 /* $Header: CSRVRULS.pls 120.0.12010000.10 2009/04/27 11:01:17 venjayar noship $ */
3
4 /**
5 * Creates a Rule in the system after validating the eligibility levels
6 * provided for the rule.
7 * <br>
8 * Following Validations are done
9 * 1. Atleast one Eligibility Level is provided
10 * 2. Rule Document cannot be Empty
11 * 3. No Presence of a Duplicate Rule
12 *
13 * <br>
14 * Note that Base Rule ID population is mandatory for certain rules and the
15 * API ensures that the proper hierarchy is established.
16 *
17 * @param p_api_version API Version (1.0)
18 * @param p_init_msg_list Initialize Message List
19 * @param p_commit Commit at the end of the API Call
20 * @param x_return_status Return Status of the Procedure.
21 * @param x_msg_data Stack of Error Messages.
22 * @param x_msg_count Number of Messages in the Stack.
23 * @param p_rule_name Name of the Rule to be created
24 * @param p_description Description of the Rule to be created (Optional)
25 * @param p_base_rule_id ID of the Base Rule (Optional)
26 * @param p_appl_id ID of the Application for Eligibility (Optional)
27 * @param p_resp_id ID of the Responsibility for Eligibility (Optional)
28 * @param p_user_id ID of the User for Eligibility (Optional)
29 * @param p_terr_id ID of the Territory for Eligibility (Optional)
30 * @param p_resource_type Type of the Resource for Eligibility (Optional)
31 * @param p_resource_id ID of the Resource for Eligibility (Optional)
32 * @param p_enabled_flag Rule is Enabled or not (Optional)
33 * @param p_rule_doc XML Document containing the Rule Spec (Optional)
34 * @param p_window_names Contains the name of the Windows defined in the rule (Optional)
35 * @param p_window_descriptions Contains the descriptions of the Windows defined in the rule (Optional)
36 * @param x_rule_id ID of the Rule Created
37 * @param x_new_rule_doc Rule Document might be changed by the API. This returns the new Document
38 **/
39 PROCEDURE create_rule(
40 p_api_version IN NUMBER
41 , p_init_msg_list IN VARCHAR2 DEFAULT NULL
42 , p_commit IN VARCHAR2 DEFAULT NULL
43 , x_return_status OUT NOCOPY VARCHAR2
44 , x_msg_data OUT NOCOPY VARCHAR2
45 , x_msg_count OUT NOCOPY NUMBER
46 , p_rule_name IN VARCHAR2
47 , p_description IN VARCHAR2 DEFAULT NULL
48 , p_base_rule_id IN NUMBER DEFAULT NULL
49 , p_appl_id IN NUMBER DEFAULT NULL
50 , p_resp_id IN NUMBER DEFAULT NULL
51 , p_user_id IN NUMBER DEFAULT NULL
52 , p_terr_id IN NUMBER DEFAULT NULL
53 , p_resource_type IN VARCHAR2 DEFAULT NULL
54 , p_resource_id IN NUMBER DEFAULT NULL
55 , p_enabled_flag IN VARCHAR2 DEFAULT NULL
56 , p_rule_doc IN XMLTYPE
57 , p_window_names IN jtf_varchar2_table_300 DEFAULT NULL
58 , p_window_descriptions IN jtf_varchar2_table_1500 DEFAULT NULL
59 , x_rule_id OUT NOCOPY NUMBER
60 , x_new_rule_doc OUT NOCOPY CLOB
61 );
62
63 /**
64 * Updates the given rule with the given new peramter values. Note that the
65 * optional parameters take the value NULL and not FND_API.G_MISS and NULL
66 * retains the old value and FND_API.G_MISS clears the old value.
67 *
68 * <br>
69 * Following Validations are done
70 * 1. Atleast one Eligibility Level is provided unless it is a site level rule
71 * being updated.
72 * 2. Rule Document cannot be Empty
73 * 3. No Presence of a Duplicate Rule
74 *
75 * <br>
76 * Note that Base Rule ID population is mandatory for certain rules and the
77 * API ensures that the proper hierarchy is established. Also the updated
78 * parameter values are propagated automatically to the child rules.
79 *
80 * @param p_api_version API Version (1.0)
81 * @param p_init_msg_list Initialize Message List
82 * @param p_commit Commit at the end of the API Call
83 * @param x_return_status Return Status of the Procedure.
84 * @param x_msg_data Stack of Error Messages.
85 * @param x_msg_count Number of Messages in the Stack.
86 * @param p_rule_id ID of the Rule to be updated
87 * @param p_object_version_number Version of the Object being updated
88 * @param p_rule_name Name of the Rule to be updated
89 * @param p_description Description of the Rule to be updated (Optional)
90 * @param p_base_rule_id ID of the Base Rule (Optional)
91 * @param p_appl_id ID of the Application for Eligibility (Optional)
92 * @param p_resp_id ID of the Responsibility for Eligibility (Optional)
93 * @param p_user_id ID of the User for Eligibility (Optional)
94 * @param p_terr_id ID of the Territory for Eligibility (Optional)
95 * @param p_resource_type Type of the Resource for Eligibility (Optional)
96 * @param p_resource_id ID of the Resource for Eligibility (Optional)
97 * @param p_enabled_flag Rule is Enabled or not (Optional)
98 * @param p_rule_doc XML Document containing the Rule Spec (Optional)
99 * @param p_window_names Contains the name of the Windows defined in the rule (Optional)
100 * @param p_window_descriptions Contains the descriptions of the Windows defined in the rule (Optional)
101 * @param p_version_msg Informative Messages indicating the actual change that has happened
102 * @param x_new_rule_doc Rule Document might be changed by the API. This returns the new Document
103 **/
104 PROCEDURE update_rule(
105 p_api_version IN NUMBER
106 , p_init_msg_list IN VARCHAR2 DEFAULT NULL
107 , p_commit IN VARCHAR2 DEFAULT NULL
108 , x_return_status OUT NOCOPY VARCHAR2
109 , x_msg_data OUT NOCOPY VARCHAR2
110 , x_msg_count OUT NOCOPY NUMBER
111 , p_rule_id IN NUMBER
112 , p_object_version_number IN OUT NOCOPY NUMBER
113 , p_rule_name IN VARCHAR2 DEFAULT NULL
114 , p_description IN VARCHAR2 DEFAULT NULL
115 , p_base_rule_id IN NUMBER DEFAULT NULL
116 , p_appl_id IN NUMBER DEFAULT NULL
117 , p_resp_id IN NUMBER DEFAULT NULL
118 , p_user_id IN NUMBER DEFAULT NULL
119 , p_terr_id IN NUMBER DEFAULT NULL
120 , p_resource_type IN VARCHAR2 DEFAULT NULL
121 , p_resource_id IN NUMBER DEFAULT NULL
122 , p_enabled_flag IN VARCHAR2 DEFAULT NULL
123 , p_rule_doc IN XMLTYPE DEFAULT NULL
124 , p_window_names IN jtf_varchar2_table_300 DEFAULT NULL
125 , p_window_descriptions IN jtf_varchar2_table_1500 DEFAULT NULL
126 , p_version_msgs IN jtf_varchar2_table_4000
127 , p_force_propagation IN VARCHAR2 DEFAULT NULL
128 , x_new_rule_doc OUT NOCOPY CLOB
129 );
130
131 /**
132 * Deletes the given rule and delinks the rules based on the current rule.
133 *
134 * <br>
135 * Note that Base Rule ID is mandatory for certain rules and if the respective
136 * Base Rule is deleted then the child rules should also deleted.
137 * If Base Rule is not mandatory, the child rules are just delinked.
138 *
139 * @param p_api_version API Version (1.0)
140 * @param p_init_msg_list Initialize Message List
141 * @param p_commit Commit at the end of the API Call
142 * @param x_return_status Return Status of the Procedure.
143 * @param x_msg_data Stack of Error Messages.
144 * @param x_msg_count Number of Messages in the Stack.
145 * @param p_rule_id ID of the Rule to be updated
146 **/
147 PROCEDURE delete_rule(
148 p_api_version IN NUMBER
149 , p_init_msg_list IN VARCHAR2 DEFAULT NULL
150 , p_commit IN VARCHAR2 DEFAULT NULL
151 , x_return_status OUT NOCOPY VARCHAR2
152 , x_msg_data OUT NOCOPY VARCHAR2
153 , x_msg_count OUT NOCOPY NUMBER
154 , p_rule_id IN NUMBER
155 );
156
157 /**
158 * Retrieves the value of the given Scheduler Parameter based on the
159 * eligibility levels given.
160 *
161 * @param p_parameter_name Name of the Parameter
162 * @param p_appl_id ID of the Application for Eligibility (Optional)
163 * @param p_resp_id ID of the Responsibility for Eligibility (Optional)
164 * @param p_user_id ID of the User for Eligibility (Optional)
165 * @param p_terr_id ID of the Territory for Eligibility (Optional)
166 * @param p_resource_type Type of the Resource for Eligibility (Optional)
167 * @param p_resource_id ID of the Resource for Eligibility (Optional)
168 **/
169 FUNCTION get_sch_parameter_value(
170 p_parameter_name IN VARCHAR2
171 , p_appl_id IN NUMBER DEFAULT NULL
172 , p_resp_id IN NUMBER DEFAULT NULL
173 , p_user_id IN NUMBER DEFAULT NULL
174 , p_terr_id IN NUMBER DEFAULT NULL
175 , p_resource_type IN VARCHAR2 DEFAULT NULL
176 , p_resource_id IN NUMBER DEFAULT NULL
177 )
178 RETURN VARCHAR2;
179
180 /**
181 * Retrieves all the rules valid for specified eligibility. Optionally the caller
182 * can specify the list of resources and their associated territory to further
183 * return the rules eligibile for those resources in addition to the session
184 * criteria.
185 *
186 * If there is no resource specified, there will be one record in the output
187 * variable which inturn will a list of session specific rules.
188 *
189 * However if Resource(s) is(are) specified as part of the Resource List, then
190 * the output variable will contain one record for each of the resource and each
191 * record will inturn be a table of rules applicable for the Resource, Territory
192 * cum Session combination.
193 *
194 * @param p_api_version API Version (1.0)
195 * @param p_init_msg_list Initialize Message List
196 * @param x_return_status Return Status of the Procedure.
197 * @param x_msg_data Stack of Error Messages.
201 * @param p_user_id ID of the User for Eligibility (Optional)
198 * @param x_msg_count Number of Messages in the Stack.
199 * @param p_appl_id ID of the Application for Eligibility (Optional)
200 * @param p_resp_id ID of the Responsibility for Eligibility (Optional)
202 * @param p_res_tbl List of Resources along with their Territory (Optional)
203 * @param x_res_rules_tbl Session Specific or Resource Specified Rules
204 **/
205 PROCEDURE get_scheduler_rules(
206 p_api_version IN NUMBER
207 , p_init_msg_list IN VARCHAR2 DEFAULT NULL
208 , x_return_status OUT NOCOPY VARCHAR2
209 , x_msg_data OUT NOCOPY VARCHAR2
210 , x_msg_count OUT NOCOPY NUMBER
211 , p_appl_id IN NUMBER DEFAULT NULL
212 , p_resp_id IN NUMBER DEFAULT NULL
213 , p_user_id IN NUMBER DEFAULT NULL
214 , p_res_tbl IN csf_resource_tbl DEFAULT NULL
215 , x_res_rules_tbl OUT NOCOPY csr_resource_rules_tbl
216 );
217
218 /**
219 * Processes the given WebADI Action and processes the parameters.
220 * In case the action is 'UPDATE', all the passed parameters are updated into
221 * the Rule as given by P_RULE_ID. Moreover, only those parameters which
222 * are valid for the current Eligibility are updated.
223 */
224 PROCEDURE process_webadi_action(
225 p_action IN VARCHAR2
226 , p_rule_id IN NUMBER
227 , p_object_version_number IN NUMBER
228 , p_rule_name IN VARCHAR2 DEFAULT NULL
229 , p_description IN VARCHAR2 DEFAULT NULL
230 , p_base_rule_id IN NUMBER DEFAULT NULL
231 , p_appl_id IN NUMBER DEFAULT NULL
232 , p_resp_id IN NUMBER DEFAULT NULL
233 , p_user_id IN NUMBER DEFAULT NULL
234 , p_terr_id IN NUMBER DEFAULT NULL
235 , p_resource_type IN VARCHAR2 DEFAULT NULL
236 , p_resource_id IN NUMBER DEFAULT NULL
237 , p_enabled_flag IN VARCHAR2 DEFAULT NULL
238 , p_sp_plan_scope IN NUMBER DEFAULT NULL
239 , p_sp_max_plan_options IN NUMBER DEFAULT NULL
240 , p_sp_max_resources IN NUMBER DEFAULT NULL
241 , p_sp_max_calc_time IN NUMBER DEFAULT NULL
242 , p_sp_max_overtime IN NUMBER DEFAULT NULL
243 , p_sp_wtp_threshold IN NUMBER DEFAULT NULL
244 , p_sp_enforce_plan_window IN VARCHAR2 DEFAULT NULL
245 , p_sp_consider_standby_shifts IN VARCHAR2 DEFAULT NULL
246 , p_sp_spares_mandatory IN VARCHAR2 DEFAULT NULL
247 , p_sp_spares_source IN VARCHAR2 DEFAULT NULL
248 , p_sp_min_task_length IN NUMBER DEFAULT NULL
249 , p_sp_default_shift_duration IN NUMBER DEFAULT NULL
250 , p_sp_dist_last_child_effort IN VARCHAR2 DEFAULT NULL
251 , p_sp_pick_contract_resources IN VARCHAR2 DEFAULT NULL
252 , p_sp_pick_ib_resources IN VARCHAR2 DEFAULT NULL
253 , p_sp_pick_territory_resources IN VARCHAR2 DEFAULT NULL
257 , p_sp_auto_reject_sts_id_others IN NUMBER DEFAULT NULL
254 , p_sp_pick_skilled_resources IN VARCHAR2 DEFAULT NULL
255 , p_sp_auto_sch_default_query IN NUMBER DEFAULT NULL
256 , p_sp_auto_reject_sts_id_spares IN NUMBER DEFAULT NULL
258 , p_sp_force_optimizer_to_group IN VARCHAR2 DEFAULT NULL
259 , p_sp_optimizer_success_perc IN NUMBER DEFAULT NULL
260 , p_sp_commutes_position IN VARCHAR2 DEFAULT NULL
261 , p_sp_commute_excluded_time IN NUMBER DEFAULT NULL
262 , p_sp_commute_home_empty_trip IN VARCHAR2 DEFAULT NULL
263 , p_sp_router_mode IN VARCHAR2 DEFAULT NULL
264 , p_sp_travel_time_extra IN NUMBER DEFAULT NULL
265 , p_sp_default_router_enabled IN VARCHAR2 DEFAULT NULL
266 , p_sp_default_travel_distance IN NUMBER DEFAULT NULL
267 , p_sp_default_travel_duration IN NUMBER DEFAULT NULL
268 , p_sp_max_distance_in_group IN NUMBER DEFAULT NULL
269 , p_sp_max_dist_to_skip_actual IN NUMBER DEFAULT NULL
270 , p_rc_router_calc_type IN VARCHAR2 DEFAULT NULL
271 , p_rc_consider_toll_roads IN VARCHAR2 DEFAULT NULL
272 , p_rc_route_func_delay_0 IN NUMBER DEFAULT NULL
273 , p_rc_route_func_delay_1 IN NUMBER DEFAULT NULL
274 , p_rc_route_func_delay_2 IN NUMBER DEFAULT NULL
275 , p_rc_route_func_delay_3 IN NUMBER DEFAULT NULL
276 , p_rc_route_func_delay_4 IN NUMBER DEFAULT NULL
277 , p_rc_estimate_first_boundary IN NUMBER DEFAULT NULL
278 , p_rc_estimate_second_boundary IN NUMBER DEFAULT NULL
279 , p_rc_estimate_first_avg_speed IN NUMBER DEFAULT NULL
280 , p_rc_estimate_second_avg_speed IN NUMBER DEFAULT NULL
281 , p_rc_estimate_third_avg_speed IN NUMBER DEFAULT NULL
282 , p_cp_task_per_day_delayed IN NUMBER DEFAULT NULL
283 , p_cp_task_per_min_early IN NUMBER DEFAULT NULL
284 , p_cp_task_per_min_late IN NUMBER DEFAULT NULL
285 , p_cp_tls_per_day_extra IN NUMBER DEFAULT NULL
286 , p_cp_tls_per_child_extra IN NUMBER DEFAULT NULL
287 , p_cp_parts_violation IN NUMBER DEFAULT NULL
288 , p_cp_res_per_min_overtime IN NUMBER DEFAULT NULL
289 , p_cp_res_assigned_not_pref IN NUMBER DEFAULT NULL
290 , p_cp_res_skill_level IN NUMBER DEFAULT NULL
291 , p_cp_standby_shift_usage IN NUMBER DEFAULT NULL
292 , p_cp_travel_per_unit_distance IN NUMBER DEFAULT NULL
293 , p_cp_travel_per_unit_duration IN NUMBER DEFAULT NULL
294 , p_cp_defer_same_site IN NUMBER DEFAULT NULL
295 );
296
297 PROCEDURE add_language;
298
299 END csr_rules_pvt;