[Home] [Help]
PACKAGE BODY: APPS.CSR_RULES_PVT
Source
1 PACKAGE BODY csr_rules_pvt AS
2 /* $Header: CSRVRULB.pls 120.26.12020000.2 2012/12/28 09:01:05 anangupt ship $ */
3
4 g_pkg_name CONSTANT VARCHAR2(30) := 'CSR_RULES_PVT';
5 g_not_specified CONSTANT NUMBER := -9999;
6
7 g_rules_ns CONSTANT VARCHAR2(100) := 'http://xmlns.oracle.com/CRM/Scheduler/Rules';
8
9 -- Parameter Name and XPath Map
10 g_rule_param_names_tbl jtf_varchar2_table_300;
11
12 TYPE node_tbl_type IS TABLE OF DBMS_XMLDOM.DOMNode;
13
14 PROCEDURE debug(p_message VARCHAR2, p_module VARCHAR2, p_level NUMBER) IS
15 BEGIN
16 IF p_level >= fnd_profile.value_specific('AFLOG_LEVEL') THEN
17 IF fnd_file.log > 0 THEN
18 IF p_message = ' ' THEN
19 fnd_file.put_line(fnd_file.log, '');
20 ELSE
21 fnd_file.put_line(fnd_file.log, rpad(p_module, 20) || ': ' || p_message);
22 END IF;
23 ELSE
24 fnd_log.string(p_level, 'csr.plsql.' || g_pkg_name || '.' || p_module, p_message);
25 END IF;
26 END IF;
27 END debug;
28
29 /**************************************************************************
30 * *
31 * Private PLSQL Functions and Procedures *
32 * *
33 *************************************************************************/
34
35 PROCEDURE init_package IS
36 BEGIN
37 g_rule_param_names_tbl := jtf_varchar2_table_300();
38 g_rule_param_names_tbl.extend(57);
39
40 g_rule_param_names_tbl(01) := 'spPlanScope';
41 g_rule_param_names_tbl(02) := 'spMaxPlanOptions';
42 g_rule_param_names_tbl(03) := 'spMaxResources';
43 g_rule_param_names_tbl(04) := 'spMaxCalcTime';
44 g_rule_param_names_tbl(05) := 'spMaxOvertime';
45 g_rule_param_names_tbl(06) := 'spWtpThreshold';
46 g_rule_param_names_tbl(07) := 'spEnforcePlanWindow';
47 g_rule_param_names_tbl(08) := 'spConsiderStandbyShifts';
48 g_rule_param_names_tbl(09) := 'spSparesMandatory';
49 g_rule_param_names_tbl(10) := 'spSparesSource';
50 g_rule_param_names_tbl(11) := 'spMinTaskLength';
51 g_rule_param_names_tbl(12) := 'spDefaultShiftDuration';
52 g_rule_param_names_tbl(13) := 'spDistLastChildEffort';
53 g_rule_param_names_tbl(14) := 'spPickContractResources';
54 g_rule_param_names_tbl(15) := 'spPickIbResources';
55 g_rule_param_names_tbl(16) := 'spPickTerritoryResources';
56 g_rule_param_names_tbl(17) := 'spPickSkilledResources';
57 g_rule_param_names_tbl(18) := 'spAutoSchDefaultQuery';
58 g_rule_param_names_tbl(19) := 'spAutoRejectStsIdSpares';
59 g_rule_param_names_tbl(20) := 'spAutoRejectStsIdOthers';
60 g_rule_param_names_tbl(21) := 'spForceOptimizerToGroup';
61 g_rule_param_names_tbl(22) := 'spOptimizerSuccessPerc';
62 g_rule_param_names_tbl(23) := 'spCommutesPosition';
63 g_rule_param_names_tbl(24) := 'spCommuteExcludedTime';
64 g_rule_param_names_tbl(25) := 'spCommuteHomeEmptyTrip';
65 g_rule_param_names_tbl(26) := 'spRouterMode';
66 g_rule_param_names_tbl(27) := 'spTravelTimeExtra';
67 g_rule_param_names_tbl(28) := 'spDefaultRouterEnabled';
68 g_rule_param_names_tbl(29) := 'spDefaultTravelDistance';
69 g_rule_param_names_tbl(30) := 'spDefaultTravelDuration';
70 g_rule_param_names_tbl(31) := 'spMaxDistanceInGroup';
71 g_rule_param_names_tbl(32) := 'spMaxDistToSkipActual';
72 g_rule_param_names_tbl(33) := 'rcRouterCalcType';
73 g_rule_param_names_tbl(34) := 'rcConsiderTollRoads';
74 g_rule_param_names_tbl(35) := 'rcRouteFuncDelay0';
75 g_rule_param_names_tbl(36) := 'rcRouteFuncDelay1';
76 g_rule_param_names_tbl(37) := 'rcRouteFuncDelay2';
77 g_rule_param_names_tbl(38) := 'rcRouteFuncDelay3';
78 g_rule_param_names_tbl(39) := 'rcRouteFuncDelay4';
79 g_rule_param_names_tbl(40) := 'rcEstimateFirstBoundary';
80 g_rule_param_names_tbl(41) := 'rcEstimateSecondBoundary';
81 g_rule_param_names_tbl(42) := 'rcEstimateFirstAvgSpeed';
82 g_rule_param_names_tbl(43) := 'rcEstimateSecondAvgSpeed';
83 g_rule_param_names_tbl(44) := 'rcEstimateThirdAvgSpeed';
84 g_rule_param_names_tbl(45) := 'cpTaskPerDayDelayed';
85 g_rule_param_names_tbl(46) := 'cpTaskPerMinEarly';
86 g_rule_param_names_tbl(47) := 'cpTaskPerMinLate';
87 g_rule_param_names_tbl(48) := 'cpTlsPerDayExtra';
88 g_rule_param_names_tbl(49) := 'cpTlsPerChildExtra';
89 g_rule_param_names_tbl(50) := 'cpPartsViolation';
90 g_rule_param_names_tbl(51) := 'cpResPerMinOvertime';
91 g_rule_param_names_tbl(52) := 'cpResAssignedNotPref';
92 g_rule_param_names_tbl(53) := 'cpResSkillLevel';
93 g_rule_param_names_tbl(54) := 'cpStandbyShiftUsage';
94 g_rule_param_names_tbl(55) := 'cpTravelPerUnitDistance';
95 g_rule_param_names_tbl(56) := 'cpTravelPerUnitDuration';
96 g_rule_param_names_tbl(57) := 'cpDeferSameSite';
97
98 END init_package;
99
100 FUNCTION handle_miss_num(p_value NUMBER, p_default_value NUMBER)
101 RETURN NUMBER IS
102 BEGIN
103 IF p_value = fnd_api.g_miss_num THEN
104 RETURN NULL;
105 ELSE
106 RETURN NVL(p_value, p_default_value);
107 END IF;
108 END handle_miss_num;
109
110 FUNCTION handle_miss_char(p_value VARCHAR2, p_default_value VARCHAR2)
111 RETURN VARCHAR2 IS
112 BEGIN
113 IF p_value = fnd_api.g_miss_char THEN
114 RETURN NULL;
115 ELSE
116 RETURN NVL(p_value, p_default_value);
117 END IF;
118 END handle_miss_char;
119
120 FUNCTION is_param_valid_for_eligibility(p_rule_rank NUMBER, p_param_index PLS_INTEGER)
121 RETURN BOOLEAN IS
122 BEGIN
123 IF p_rule_rank = 32 THEN
124 RETURN p_param_index IN (5, 24);
125 ELSIF p_rule_rank = 16 THEN
126 RETURN p_param_index IN (5, 7, 8) OR p_param_index >= 23;
127 ELSIF p_rule_rank IN (2, 4, 8) THEN
128 RETURN p_param_index <= 32;
129 ELSE
130 RETURN TRUE;
131 END IF;
132 END is_param_valid_for_eligibility;
133
134 FUNCTION get_param_grp_dom_node(
135 p_rule_dom DBMS_XMLDOM.DOMDocument
136 , p_tag_name VARCHAR2
137 , p_create BOOLEAN
138 )
139 RETURN DBMS_XMLDOM.DOMNode IS
140 l_dom_node DBMS_XMLDOM.DOMNode;
141 BEGIN
142 l_dom_node := DBMS_XMLDOM.item(DBMS_XMLDOM.getElementsByTagName(p_rule_dom, p_tag_name), 0);
143 IF DBMS_XMLDOM.isNull(l_dom_node) AND p_create THEN
144 l_dom_node :=
145 DBMS_XMLDOM.appendChild(
146 DBMS_XMLDOM.MAKENODE(DBMS_XMLDOM.getDocumentElement(p_rule_dom))
147 , DBMS_XMLDOM.MAKENODE(DBMS_XMLDOM.createElement(p_rule_dom, p_tag_name, g_rules_ns))
148 );
149 END IF;
150
151 RETURN l_dom_node;
152 END get_param_grp_dom_node;
153
154 FUNCTION get_rule_rank (
155 p_appl_id NUMBER DEFAULT NULL
156 , p_resp_id NUMBER DEFAULT NULL
157 , p_user_id NUMBER DEFAULT NULL
158 , p_terr_id NUMBER DEFAULT NULL
159 , p_resource_id NUMBER DEFAULT NULL
160 ) RETURN NUMBER IS
161 BEGIN
162 RETURN CASE WHEN NVL(p_appl_id, g_not_specified) <> g_not_specified THEN
163 POWER(2, 1) ELSE 0
164 END
165 + CASE WHEN NVL(p_resp_id, g_not_specified) <> g_not_specified THEN
166 POWER(2, 2) ELSE 0
167 END
168 + CASE WHEN NVL(p_user_id, g_not_specified) <> g_not_specified THEN
169 POWER(2, 3) ELSE 0
170 END
171 + CASE WHEN NVL(p_terr_id, g_not_specified) <> g_not_specified THEN
172 POWER(2, 4) ELSE 0
173 END
174 + CASE WHEN NVL(p_resource_id, g_not_specified) <> g_not_specified THEN
175 POWER(2, 5) ELSE 0
176 END;
177 END get_rule_rank;
178
179 FUNCTION insert_rule (
180 p_rule_name VARCHAR2
181 , p_description VARCHAR2
182 , p_base_rule_id NUMBER
183 , p_appl_id NUMBER
184 , p_resp_id NUMBER
185 , p_user_id NUMBER
186 , p_terr_id NUMBER
187 , p_resource_type VARCHAR2
188 , p_resource_id NUMBER
189 , p_rule_rank NUMBER
190 , p_enabled_flag VARCHAR2
191 , p_rule_doc XMLTYPE
192 )
193 RETURN NUMBER IS
194 l_new_rule_id NUMBER;
195 --
196 CURSOR c_lookups IS
197 SELECT meaning FROM fnd_lookups
198 WHERE lookup_type = 'JTF_NOTE_TYPE' AND lookup_code = 'CN_SYSGEN';
199 l_rule_name csr_rules_tl.rule_name%TYPE;
200 BEGIN
201 l_rule_name := p_rule_name;
202
203 IF p_enabled_flag = 'S' THEN
204 OPEN c_lookups;
205 FETCH c_lookups INTO l_rule_name;
206 CLOSE c_lookups;
207 END IF;
208
209 INSERT INTO csr_rules_b (
210 rule_id
211 , object_version_number
212 , base_rule_id
213 , appl_id
214 , resp_id
215 , user_id
216 , terr_id
217 , resource_type
218 , resource_id
219 , enabled_flag
220 , rule_rank
221 , rule_doc
222 , created_by
223 , creation_date
224 , last_updated_by
225 , last_update_date
226 , last_update_login
227 )
228 VALUES(
229 csr_rules_b_s.NEXTVAL
230 , 1
231 , NVL(p_base_rule_id, -1)
232 , NVL(p_appl_id, g_not_specified)
233 , NVL(p_resp_id, g_not_specified)
234 , NVL(p_user_id, g_not_specified)
235 , NVL(p_terr_id, g_not_specified)
236 , NVL(p_resource_type, '-')
237 , NVL(p_resource_id, g_not_specified)
238 , p_enabled_flag
239 , p_rule_rank
240 , p_rule_doc
241 , fnd_global.user_id
242 , SYSDATE
243 , fnd_global.user_id
244 , SYSDATE
245 , fnd_global.login_id
246 )
247 RETURNING rule_id INTO l_new_rule_id;
248
249 -- Insert the Rule's Translated Attributes
250 INSERT INTO csr_rules_tl (
251 rule_id
252 , language
253 , source_lang
254 , rule_name
255 , description
256 , created_by
257 , creation_date
258 , last_updated_by
259 , last_update_date
260 , last_update_login
261 )
262 SELECT l_new_rule_id
263 , l.language_code
264 , userenv('LANG')
265 , l_rule_name
266 , p_description
267 , fnd_global.user_id
268 , SYSDATE
269 , fnd_global.user_id
270 , SYSDATE
271 , fnd_global.login_id
272 FROM fnd_languages l
273 WHERE l.installed_flag in ('I','B')
274 AND NOT EXISTS (
275 SELECT 1 FROM csr_rules_tl tl
276 WHERE tl.rule_id = l_new_rule_id
277 AND tl.language = l.language_code
278 );
279
280 RETURN l_new_rule_id;
281 END insert_rule;
282
283 FUNCTION get_parent_territory_rule(p_terr_id NUMBER, p_rule_doc XMLTYPE) RETURN NUMBER IS
284 CURSOR c_terr_hier IS
285 SELECT DISTINCT t.terr_id, LEVEL terr_level, r.rule_id
286 FROM jtf_terr_all t, csr_rules_b r
287 WHERE t.terr_id = r.terr_id (+)
288 AND NVL(r.rule_rank, 16) = 16
289 START WITH t.terr_id = p_terr_id
290 CONNECT BY NOCYCLE PRIOR t.parent_territory_id = t.terr_id
291 ORDER BY terr_level DESC;
292
293 l_parent_terr_rule_id NUMBER;
294 BEGIN
295 l_parent_terr_rule_id := -1;
296
297 FOR v_terr IN c_terr_hier LOOP
298
299 -- We dont want to process the row corresponding to the passed Territory
300 IF v_terr.terr_id <> p_terr_id THEN
301
302 -- If the Rule already exists for the Territory, then use it.
303 IF v_terr.rule_id IS NULL THEN
304 v_terr.rule_id :=
305 insert_rule(
306 p_rule_name => NULL
307 , p_description => NULL
308 , p_base_rule_id => l_parent_terr_rule_id
309 , p_appl_id => NULL
310 , p_resp_id => NULL
311 , p_user_id => NULL
312 , p_terr_id => v_terr.terr_id
313 , p_resource_type => NULL
314 , p_resource_id => NULL
315 , p_rule_rank => 16
316 , p_enabled_flag => 'S'
317 , p_rule_doc => p_rule_doc
318 );
319 END IF;
320
321 l_parent_terr_rule_id := v_terr.rule_id;
322 END IF;
323 END LOOP;
324
325 RETURN l_parent_terr_rule_id;
326 END get_parent_territory_rule;
327
328 FUNCTION create_system_gen_base_rule(
329 p_rule_eligibility_type VARCHAR2
330 , p_eligibility_val1 NUMBER
331 , p_eligibility_val2 VARCHAR2
332 , p_rule_doc XMLTYPE
333 ) RETURN NUMBER IS
334 l_appl_id NUMBER;
335 l_resp_id NUMBER;
336 l_user_id NUMBER;
337 l_terr_id NUMBER;
338 l_resource_type VARCHAR2(30);
339 l_resource_id NUMBER;
340 l_base_rule_id NUMBER;
341 --
342 l_new_rule_id NUMBER;
343 l_rule_rank PLS_INTEGER;
344 BEGIN
345 l_base_rule_id := -1;
346
347 IF p_rule_eligibility_type = 'APPL' THEN
348 l_appl_id := p_eligibility_val1;
349 ELSIF p_rule_eligibility_type = 'RESP' THEN
350 l_resp_id := p_eligibility_val1;
351 ELSIF p_rule_eligibility_type = 'USER' THEN
352 l_user_id := p_eligibility_val1;
353 ELSIF p_rule_eligibility_type = 'TERR' THEN
354 l_terr_id := p_eligibility_val1;
355 l_base_rule_id := get_parent_territory_rule(l_terr_id, p_rule_doc);
356 ELSIF p_rule_eligibility_type = 'RES' THEN
357 l_resource_id := p_eligibility_val1;
358 l_resource_type := p_eligibility_val2;
359 END IF;
360
361 l_rule_rank := get_rule_rank(l_appl_id, l_resp_id, l_user_id, l_terr_id, l_resource_id);
362
363 l_new_rule_id :=
364 insert_rule(
365 p_rule_name => NULL
366 , p_description => NULL
367 , p_base_rule_id => l_base_rule_id
368 , p_appl_id => l_appl_id
369 , p_resp_id => l_resp_id
370 , p_user_id => l_user_id
371 , p_terr_id => l_terr_id
372 , p_resource_type => l_resource_type
373 , p_resource_id => l_resource_id
374 , p_rule_rank => l_rule_rank
375 , p_enabled_flag => 'S'
376 , p_rule_doc => p_rule_doc
377 );
378
379 RETURN l_new_rule_id;
380 END create_system_gen_base_rule;
381
382 PROCEDURE validate_rule(
383 p_rule_id IN NUMBER
384 , p_rule_name IN VARCHAR2
385 , p_base_rule_id IN OUT NOCOPY NUMBER
386 , p_appl_id IN NUMBER DEFAULT NULL
387 , p_resp_id IN NUMBER DEFAULT NULL
388 , p_user_id IN NUMBER DEFAULT NULL
389 , p_terr_id IN NUMBER DEFAULT NULL
390 , p_resource_type IN VARCHAR2 DEFAULT NULL
391 , p_resource_id IN NUMBER DEFAULT NULL
392 , p_rule_rank IN NUMBER DEFAULT NULL
393 , p_rule_doc IN XMLTYPE
394 ) IS
395 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_RULE';
396 l_debug CONSTANT VARCHAR2(1) := fnd_profile.value('AFLOG_ENABLED');
397 --
398 CURSOR c_duplicate_rules IS
399 SELECT rule_id, rule_name
400 FROM csr_rules_vl
401 WHERE rule_id <> NVL(p_rule_id, g_not_specified)
402 AND appl_id = NVL(p_appl_id, g_not_specified)
403 AND resp_id = NVL(p_resp_id, g_not_specified)
404 AND user_id = NVL(p_user_id, g_not_specified)
405 AND terr_id = NVL(p_terr_id, g_not_specified)
406 AND resource_type = NVL(p_resource_type, '-')
407 AND resource_id = NVL(p_resource_id, g_not_specified)
408 AND ROWNUM = 1;
409 l_duplicate_rule c_duplicate_rules%ROWTYPE;
410 --
411 l_base_rule_query VARCHAR2(500);
412 l_base_bind1 NUMBER;
413 l_base_bind2 VARCHAR2(30);
414 l_base_eligibilty_type VARCHAR2(10);
415 l_valid_base_rule_id NUMBER;
416 l_valid_base_rule_name csr_rules_tl.rule_name%TYPE;
417 c_rules SYS_REFCURSOR;
418 BEGIN
419 IF l_debug = 'Y' THEN
420 debug('Validating Rule', l_api_name, fnd_log.level_event);
421 debug(' --> Rule Rank = ' || p_rule_rank, l_api_name, fnd_log.level_statement);
422 debug(' --> Rule Name = ' || p_rule_name, l_api_name, fnd_log.level_statement);
423 debug(' --> Appl ID = ' || p_appl_id, l_api_name, fnd_log.level_statement);
424 debug(' --> Resp ID = ' || p_resp_id, l_api_name, fnd_log.level_statement);
425 debug(' --> User ID = ' || p_user_id, l_api_name, fnd_log.level_statement);
426 debug(' --> Terr ID = ' || p_terr_id, l_api_name, fnd_log.level_statement);
427 debug(' --> Res Type = ' || p_resource_type, l_api_name, fnd_log.level_statement);
428 debug(' --> Res ID = ' || p_resource_id, l_api_name, fnd_log.level_statement);
429 END IF;
430
431 IF p_rule_name IS NULL THEN
432 fnd_message.set_name('CSR', 'CSR_RULE_NAME_NOT_GIVEN');
433 fnd_msg_pub.ADD;
434 RAISE fnd_api.g_exc_error;
435 END IF;
436
437 IF p_rule_doc IS NULL THEN
438 fnd_message.set_name('CSR', 'CSR_RULE_DOC_NOT_GIVEN');
439 fnd_msg_pub.ADD;
440 RAISE fnd_api.g_exc_error;
441 END IF;
442
443 IF NVL(p_resource_id, -9999) <> -9999 AND NVL(p_resource_type, '-') = '-'
444 OR NVL(p_resource_id, -9999) = -9999 AND NVL(p_resource_type, '-') <> '-'
445 THEN
446 IF p_resource_id IS NULL THEN
447 fnd_message.set_name ('JTF', 'JTF_RS_RESOURCE_PARAM_ID_NULL');
448 ELSE
449 fnd_message.set_name ('JTF', 'JTF_RS_RESOURCE_CATEGORY_NULL');
450 END IF;
451 fnd_msg_pub.ADD;
452 RAISE fnd_api.g_exc_error;
453 END IF;
454
455 IF l_debug = 'Y' THEN
456 debug('Checking for Eligibility', l_api_name, fnd_log.level_statement);
457 END IF;
458
459 IF NVL(p_rule_id, g_not_specified) <> 0
460 AND (p_appl_id IS NULL OR p_appl_id = g_not_specified)
461 AND (p_resp_id IS NULL OR p_resp_id = g_not_specified)
462 AND (p_user_id IS NULL OR p_user_id = g_not_specified)
463 AND (p_terr_id IS NULL OR p_terr_id = g_not_specified)
464 AND (p_resource_id IS NULL OR p_resource_id = g_not_specified)
465 THEN
466 fnd_message.set_name('CSR', 'CSR_RULE_ELIGIBILITY_NOT_GIVEN');
467 fnd_msg_pub.ADD;
468 RAISE fnd_api.g_exc_error;
469 END IF;
470
471 IF l_debug = 'Y' THEN
472 debug('Checking for Duplicates', l_api_name, fnd_log.level_statement);
473 END IF;
474
475 -- There can be only one Rule with a particular Eligibility Criteria.
476 OPEN c_duplicate_rules;
477 FETCH c_duplicate_rules INTO l_duplicate_rule;
478 CLOSE c_duplicate_rules;
479
480 IF l_duplicate_rule.rule_id IS NOT NULL THEN
481 fnd_message.set_name('CSR', 'CSR_RULE_DUPLICATION');
482 fnd_message.set_token ('RULE_NAME', l_duplicate_rule.rule_name);
483 fnd_msg_pub.ADD;
484 RAISE fnd_api.g_exc_error;
485 END IF;
486
487 IF p_rule_rank NOT IN (0, 2, 4, 8, 16, 32) THEN
488 IF l_debug = 'Y' THEN
489 debug('Checking for Multiple Eligibility and Base Rule', l_api_name, fnd_log.level_statement);
490 END IF;
491
492 -- Multiple Eligibility Criteria given
493 l_base_rule_query := 'SELECT rule_id, rule_name FROM csr_rules_vl WHERE ';
494
495 l_base_bind2 := '-';
496 IF NVL(p_resource_id, g_not_specified) <> g_not_specified THEN
497 l_base_rule_query := l_base_rule_query || ' resource_id = :1 AND resource_type = :2 AND rule_rank = 32 ';
498 l_base_bind1 := p_resource_id;
499 l_base_bind2 := p_resource_type;
500 l_base_eligibilty_type := 'RES';
501 ELSIF NVL(p_terr_id, g_not_specified) <> g_not_specified THEN
502 l_base_rule_query := l_base_rule_query || ' terr_id = :1 AND ''-'' = :2 AND rule_rank = 16 ';
503 l_base_bind1 := p_terr_id;
504 l_base_eligibilty_type := 'TERR';
505 ELSIF NVL(p_user_id, g_not_specified) <> g_not_specified THEN
506 l_base_rule_query := l_base_rule_query || ' user_id = :1 AND ''-'' = :2 AND rule_rank = 8 ';
507 l_base_bind1 := p_user_id;
508 l_base_eligibilty_type := 'USER';
509 ELSIF NVL(p_resp_id, g_not_specified) <> g_not_specified THEN
510 l_base_rule_query := l_base_rule_query || ' resp_id = :1 AND ''-'' = :2 AND rule_rank = 4 ';
511 l_base_bind1 := p_resp_id;
512 l_base_eligibilty_type := 'RESP';
513 ELSIF NVL(p_appl_id, g_not_specified) <> g_not_specified THEN
514 l_base_rule_query := l_base_rule_query || ' appl_id = :1 AND ''-'' = :2 AND rule_rank = 2 ';
515 l_base_bind1 := p_appl_id;
516 l_base_eligibilty_type := 'APPL';
517 END IF;
518
519 IF l_debug = 'Y' THEN
520 debug('Querying for Base Rule through ' || l_base_rule_query, l_api_name, fnd_log.level_statement);
521 END IF;
522
523 OPEN c_rules FOR l_base_rule_query USING l_base_bind1, l_base_bind2;
524 FETCH c_rules INTO l_valid_base_rule_id, l_valid_base_rule_name;
525 CLOSE c_rules;
526
527 -- Required Base Rule doesnt exists. Create a System Generated Base Rule
528 IF l_valid_base_rule_id IS NULL THEN
529 IF l_debug = 'Y' THEN
530 debug('Creating Base Rule with Eligibility as ' || l_base_bind1, l_api_name, fnd_log.level_statement);
531 END IF;
532 l_valid_base_rule_id :=
533 create_system_gen_base_rule(
534 l_base_eligibilty_type
535 , l_base_bind1
536 , l_base_bind2
537 , p_rule_doc
538 );
539 END IF;
540
541 p_base_rule_id := l_valid_base_rule_id;
542 ELSIF p_terr_id IS NOT NULL AND p_terr_id <> g_not_specified THEN
543 IF l_debug = 'Y' THEN
544 debug('Checking for Territory Eligibility and Base Rule', l_api_name, fnd_log.level_statement);
545 END IF;
546
547 -- Territory Rule. Ensure that Base Rule is that of Parent Territory
548 p_base_rule_id := get_parent_territory_rule(p_terr_id, p_rule_doc);
549 END IF;
550 END validate_rule;
551
552 FUNCTION check_Element( p_list JTF_NUMBER_TABLE,element NUMBER)
553 RETURN BOOLEAN IS
554 BEGIN
555 FOR i in 1..P_LIST.count
556 LOOP
557 IF P_LIST(i) = element THEN
558 RETURN TRUE;
559 END IF;
560 END LOOP;
561 RETURN FALSE;
562 END check_Element;
563
564 PROCEDURE handle_rule_windows(
565 p_new_rule_doc XMLTYPE
566 , p_old_rule_doc XMLTYPE
567 , p_window_names jtf_varchar2_table_300
568 , p_window_descriptions jtf_varchar2_table_1500
569 ) IS
570 l_rule_document DBMS_XMLDOM.DOMDocument;
571 l_root_element DBMS_XMLDOM.DOMElement;
572 l_window_nodelist DBMS_XMLDOM.DOMNodelist;
573 l_num_of_windows PLS_INTEGER;
574 l_window_node DBMS_XMLDOM.DOMNode;
575 l_window_attrs DBMS_XMLDOM.DOMNamedNodeMap;
576 l_window_id_node DBMS_XMLDOM.DOMNode;
577 l_window_id PLS_INTEGER;
578 l_windows_id_list JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
579
580 l_debug CONSTANT VARCHAR2(1) := fnd_profile.value('AFLOG_ENABLED');
581 l_api_name CONSTANT VARCHAR2(30) := 'HANDLE_RULE_WINDOWS';
582 BEGIN
583
584 -- Loop through the New Document to find out whether we have Windows Defined.
585 l_rule_document := DBMS_XMLDOM.newDOMDocument(p_new_rule_doc);
586 l_root_element := DBMS_XMLDOM.getDocumentElement(l_rule_document);
587 l_window_nodelist := DBMS_XMLDOM.getElementsByTagName(l_root_element, 'window');
588 l_num_of_windows := DBMS_XMLDOM.GetLength(l_window_nodelist);
589 FOR i IN 1..l_num_of_windows LOOP
590 l_window_node := DBMS_XMLDOM.item(l_window_nodelist, i-1);
591 l_window_attrs := DBMS_XMLDOM.getAttributes(l_window_node);
592 l_window_id_node := DBMS_XMLDOM.getNamedItem(l_window_attrs, 'windowId');
593 l_window_id := DBMS_XMLDOM.getNodeValue(l_window_id_node);
594 --
595 --adding to list so that we can compare with old WTP windows
596 l_windows_id_list.extend;
597 l_windows_id_list(i):=l_window_id;
598 IF l_window_id < 0 THEN
599
600 SELECT csr_rule_windows_tl_s.NEXTVAL INTO l_window_id FROM dual;
601 l_windows_id_list(i):=l_window_id;
602
603 INSERT INTO csr_rule_windows_tl (
604 window_id
605 , language
606 , source_lang
607 , window_name
608 , description
609 , created_by
610 , creation_date
611 , last_updated_by
612 , last_update_date
613 , last_update_login
614 )
615 SELECT l_window_id
616 , l.language_code
617 , userenv('LANG')
618 , p_window_names(i)
619 , p_window_descriptions(i)
620 , fnd_global.user_id
621 , SYSDATE
622 , fnd_global.user_id
623 , SYSDATE
624 , fnd_global.login_id
625 FROM fnd_languages l
626 WHERE l.installed_flag in ('I','B');
627
628 DBMS_XMLDOM.setNodeValue(l_window_id_node, l_window_id);
629 ELSE
630 UPDATE csr_rule_windows_tl
631 SET window_name = p_window_names(i)
632 , description = p_window_descriptions(i)
633 , last_updated_by = fnd_global.user_id
634 , last_update_date = SYSDATE
635 , last_update_login = fnd_global.login_id
636 WHERE window_id = l_window_id
637 AND userenv('LANG') IN (language, source_lang);
638 END IF;
639 END LOOP;
640
641 -- If there is any Window not defined in the New Document, but defined in the Old
642 -- Document, then we have to delete the Window Names in CSR_RULE_WINDOWS_TL
643 -- to avoid Dangling References.
644 IF p_old_rule_doc IS NOT NULL THEN
645 l_rule_document := DBMS_XMLDOM.newDOMDocument(p_old_rule_doc);
646 l_root_element := DBMS_XMLDOM.getDocumentElement(l_rule_document);
647 l_window_nodelist := DBMS_XMLDOM.getElementsByTagName(l_root_element, 'window');
648 l_num_of_windows := DBMS_XMLDOM.GetLength(l_window_nodelist);
649
650 FOR i IN 1..l_num_of_windows LOOP
651 l_window_node := DBMS_XMLDOM.item(l_window_nodelist, i-1);
652 l_window_attrs := DBMS_XMLDOM.getAttributes(l_window_node);
653 l_window_id := DBMS_XMLDOM.getNodeValue(DBMS_XMLDOM.getNamedItem(l_window_attrs, 'windowId'));
654
655 IF l_debug = 'Y' THEN
656 debug('cheking for '||l_window_id||' with new doc window list ' , l_api_name, fnd_log.level_statement);
657 END IF;
658 IF NOT check_element(l_windows_id_list,l_window_id) THEN
659 IF l_debug = 'Y' THEN
660 debug('removing TL table records for window with window ID '||l_window_id , l_api_name, fnd_log.level_statement);
661 END IF;
662 DELETE csr_rule_windows_tl WHERE window_id = l_window_id;
663 END IF;
664 END LOOP;
665 END IF;
666 END handle_rule_windows;
667
668 FUNCTION propagate(
669 p_base_doc IN XMLTYPE
670 , p_child_doc IN OUT NOCOPY XMLTYPE
671 , p_child_rule_id IN NUMBER
672 , p_force IN VARCHAR2
673 ) RETURN BOOLEAN IS
674
675 l_baserule_doc XMLTYPE;
676 l_baserule_dom DBMS_XMLDOM.DOMDocument;
677 l_childrule_dom DBMS_XMLDOM.DOMDocument;
678 l_baserule_root DBMS_XMLDOM.DOMElement;
679
680
681 l_child DBMS_XMLDOM.DOMNode;
682 l_nodelist DBMS_XMLDOM.DOMNodelist;
683 l_node DBMS_XMLDOM.DOMNode;
684 l_child_text_node DBMS_XMLDOM.DOMNode;
685 l_base_text_node DBMS_XMLDOM.DOMNode;
686
687 l_child_node DBMS_XMLDOM.DOMNode;
688 l_child_attrs DBMS_XMLDOM.DOMNamedNodeMap;
689 l_inherited VARCHAR2(1);
690 l_wtp_nodename CONSTANT VARCHAR2(20) := 'wtpParameters';
691 l_child_modified BOOLEAN := FALSE;
692
693 i PLS_INTEGER;
694
695 l_baserule_node_list node_tbl_type := node_tbl_type();
696
697 l_api_name CONSTANT VARCHAR2(30) := 'PROPAGATE';
698 l_debug CONSTANT VARCHAR2(1) := fnd_profile.value('AFLOG_ENABLED');
699
700 BEGIN
701 -- If the Child Rule is NULL then reset it to Parent Rule
702 IF p_child_doc IS NULL THEN
703 p_child_doc := p_base_doc;
704 RETURN TRUE;
705 END IF;
706
707 l_baserule_doc := p_base_doc;
708 l_baserule_dom := DBMS_XMLDOM.newDOMDocument(l_baserule_doc);
709 l_baserule_root := DBMS_XMLDOM.getDocumentElement(l_baserule_dom);
710 l_child := DBMS_XMLDOM.getFirstChild(DBMS_XMLDOM.makeNode(l_baserule_root));
711
712 -- Parse the Base Rule and Store the Nodes of the
713 -- Base Rule ( schedulerParameters/costParameters/routerConfig)
714 WHILE NOT DBMS_XMLDOM.isNull(l_child) LOOP
715 IF DBMS_XMLDOM.getTagName(DBMS_XMLDOM.makeElement(l_child)) <> l_wtp_nodename THEN
716 l_nodelist := DBMS_XMLDOM.getChildNodes(l_child);
717 FOR i IN 0..DBMS_XMLDOM.getLength(l_nodelist) - 1 LOOP
718 l_baserule_node_list.EXTEND(1);
719 l_baserule_node_list(l_baserule_node_list.COUNT) := DBMS_XMLDOM.item(l_nodelist, i);
720 END LOOP;
721 END IF;
722 l_child := DBMS_XMLDOM.getNextSibling(l_child);
723 END LOOP;
724
725 l_childrule_dom := DBMS_XMLDOM.newDOMDocument(p_child_doc);
726
727 IF l_debug = 'Y' THEN
728 debug('Updating Rule#' || p_child_rule_id, l_api_name, fnd_log.level_event);
729 DECLARE
730 buffer VARCHAR2(8000);
731 BEGIN
732 DBMS_XMLDOM.WRITETOBUFFER(l_childrule_dom, buffer);
733 debug(' --> XML Doc#1 = ' || substr(buffer, 1, 3900), l_api_name, fnd_log.level_statement);
734 debug(' --> XML Doc#2 = ' || substr(buffer, 3901, 3900), l_api_name, fnd_log.level_statement);
735 END;
736 END IF;
737
738 i := l_baserule_node_list.FIRST;
739 WHILE i IS NOT NULL LOOP
740 l_node := l_baserule_node_list(i);
741 l_child_node := DBMS_XMLDOM.item(DBMS_XMLDOM.getElementsByTagName
742 (l_childrule_dom, DBMS_XMLDOM.getTagName(
743 DBMS_XMLDOM.makeElement(l_node)
744 )
745 ),0);
746 l_child_attrs := DBMS_XMLDOM.getAttributes(l_child_node);
747 -- Propagate the Changes from a Parent Rule's XML Element to Child Rule's Element Only
748 -- if it is either inherited OR its a force updation.
749 l_inherited := DBMS_XMLDOM.getNodeValue(DBMS_XMLDOM.getNamedItem(l_child_attrs, 'inherited'));
750 IF l_inherited = 'Y' OR p_force = 'Y' THEN
751 l_child_text_node := DBMS_XMLDOM.getFirstChild(l_child_node);
752 l_base_text_node := DBMS_XMLDOM.getFirstChild(l_node);
753 DBMS_XMLDOM.setNodeValue(l_child_text_node, DBMS_XMLDOM.getNodeValue(l_base_text_node));
754 l_child_modified := TRUE;
755 IF l_debug = 'Y' THEN
756 debug('Modified XML Element:' ||DBMS_XMLDOM.getNodeName(l_child_text_node)
757 , l_api_name, fnd_log.level_statement);
758 END IF;
759 END IF;
760 i := l_baserule_node_list.NEXT(i);
761 END LOOP;
762
763 -- Persist the changes to the DB only if something has changed.
764 IF l_child_modified = TRUE THEN
765 UPDATE csr_rules_b
766 SET rule_doc = p_child_doc
767 WHERE rule_id = p_child_rule_id;
768 END IF;
769
770 IF l_debug = 'Y' THEN
771 IF l_child_modified = TRUE THEN
772 debug('The Rule:'||p_child_rule_id ||' was modified', l_api_name, fnd_log.level_statement);
773 ELSE
774 debug('The Rule:'||p_child_rule_id ||' was NOT modified', l_api_name, fnd_log.level_statement);
775 END IF;
776 END IF;
777
778 -- Return whether the Child Rule is Modified or Not.
779 RETURN l_child_modified;
780
781 END propagate;
782
783 /**
784 * This method propagates the changes of parameters:
785 * 1. Scheduler Parameters
786 * 2. Cost Parameters
787 * 3. Router Configuration Parameters
788 * from an Updated Scheduler Rule to all its Children.
789 *
790 * The method exploits Depth First Search Traversal (DFS) approach to
791 * explore all the Child Rules which can be updated based on the changes
792 * done in the Base Rule.
793 * A XML Element in the Child Rule is modified if it has been inherited
794 * from the Base Rule and still the inheritance exists. But if the argument
795 * p_force is 'Y' then the Child Rule is updated irrespective of inheritance.
796 */
797 PROCEDURE propagate_to_child_rules(
798 p_rule_id NUMBER
799 , p_base_doc XMLType
800 , p_child_doc XMLTYPE
801 , p_force VARCHAR2
802 ) IS
803 CURSOR c_child_rules IS
804 SELECT rule_id, rule_doc
805 FROM csr_rules_b
806 WHERE base_rule_id = p_rule_id;
807
808 l_child_doc XMLTYPE;
809 l_debug CONSTANT VARCHAR2(1) := fnd_profile.value('AFLOG_ENABLED');
810 l_api_name CONSTANT VARCHAR2(30) := 'PROPAGATE_TO_CHILD_RULES';
811 BEGIN
812
813 l_child_doc := p_child_doc;
814 -- propagate the Changes from Base Rule to Child Rule. If there are
815 -- no updations in the Child Rule then skip going down the heirarchy.
816 IF NOT propagate ( p_base_doc, l_child_doc, p_rule_id, p_force ) THEN
817 RETURN;
818 END IF;
819
820 -- Propagate the Changes to the Child Rules Recursively
821 FOR v_child_rule IN c_child_rules LOOP
822 IF l_debug = 'Y' THEN
823 debug('Propogating the Changes From the Rule#'||p_rule_id||' to:'||v_child_rule.rule_id , l_api_name, fnd_log.level_statement);
824 END IF;
825 propagate_to_child_rules(v_child_rule.rule_id, l_child_doc, v_child_rule.rule_doc, p_force);
826 END LOOP;
827
828 -- If there are no child rules return to Parent Rule.
829 RETURN;
830 END propagate_to_child_rules;
831
832
833 /**************************************************************************
834 * *
835 * Public PLSQL Functions and Procedures *
836 * *
837 *************************************************************************/
838 PROCEDURE create_rule(
839 p_api_version IN NUMBER
840 , p_init_msg_list IN VARCHAR2 DEFAULT NULL
841 , p_commit IN VARCHAR2 DEFAULT NULL
842 , x_return_status OUT NOCOPY VARCHAR2
843 , x_msg_data OUT NOCOPY VARCHAR2
844 , x_msg_count OUT NOCOPY NUMBER
845 , p_rule_name IN VARCHAR2
846 , p_description IN VARCHAR2 DEFAULT NULL
847 , p_base_rule_id IN NUMBER DEFAULT NULL
848 , p_appl_id IN NUMBER DEFAULT NULL
849 , p_resp_id IN NUMBER DEFAULT NULL
850 , p_user_id IN NUMBER DEFAULT NULL
851 , p_terr_id IN NUMBER DEFAULT NULL
852 , p_resource_type IN VARCHAR2 DEFAULT NULL
853 , p_resource_id IN NUMBER DEFAULT NULL
854 , p_enabled_flag IN VARCHAR2 DEFAULT NULL
855 , p_rule_doc IN XMLTYPE
856 , p_window_names IN jtf_varchar2_table_300 DEFAULT NULL
857 , p_window_descriptions IN jtf_varchar2_table_1500 DEFAULT NULL
858 , x_rule_id OUT NOCOPY NUMBER
859 , x_new_rule_doc OUT NOCOPY CLOB
860 ) IS
861 l_api_version CONSTANT NUMBER := 1.0;
862 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_RULE';
863 l_debug CONSTANT VARCHAR2(1) := fnd_profile.value('AFLOG_ENABLED');
864 --
865 l_rule_rank PLS_INTEGER;
866 l_rule_doc XMLTYPE;
867 l_note_id NUMBER;
868 l_base_rule_id NUMBER;
869 BEGIN
870 SAVEPOINT csr_rule_create;
871
872 -- Check for API Compatibility
873 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
874 RAISE fnd_api.g_exc_unexpected_error;
875 END IF;
876
877 -- Initialize Message Stack if required
878 IF fnd_api.to_boolean(p_init_msg_list) THEN
879 fnd_msg_pub.initialize;
880 END IF;
881
882 -- Initialize Return Status
883 x_return_status := fnd_api.g_ret_sts_success;
884
885 IF l_debug = 'Y' THEN
886 debug('Creating Rule', l_api_name, fnd_log.level_event);
887 debug(' --> Rule Name = ' || p_rule_name, l_api_name, fnd_log.level_event);
888 debug(' --> Base Rule = ' || p_base_rule_id, l_api_name, fnd_log.level_statement);
889 debug(' --> Appl ID = ' || p_appl_id, l_api_name, fnd_log.level_statement);
890 debug(' --> Resp ID = ' || p_resp_id, l_api_name, fnd_log.level_statement);
891 debug(' --> User ID = ' || p_user_id, l_api_name, fnd_log.level_statement);
892 debug(' --> Terr ID = ' || p_terr_id, l_api_name, fnd_log.level_statement);
893 debug(' --> Res Type = ' || p_resource_type, l_api_name, fnd_log.level_statement);
894 debug(' --> Res ID = ' || p_resource_id, l_api_name, fnd_log.level_statement);
895 DECLARE
896 buffer VARCHAR2(8000);
897 BEGIN
898 DBMS_XMLDOM.WRITETOBUFFER(DBMS_XMLDOM.NEWDOMDOCUMENT(p_rule_doc), buffer);
899 debug(' --> XML Doc#1 = ' || substr(buffer, 1, 3900), l_api_name, fnd_log.level_statement);
900 debug(' --> XML Doc#2 = ' || substr(buffer, 3901, 3900), l_api_name, fnd_log.level_statement);
901 END;
902 END IF;
903
904 l_base_rule_id := p_base_rule_id;
905 l_rule_doc := p_rule_doc;
906
907 l_rule_rank :=
908 get_rule_rank(p_appl_id, p_resp_id, p_user_id, p_terr_id, p_resource_id);
909
910 validate_rule(
911 p_rule_id => x_rule_id
912 , p_rule_name => p_rule_name
913 , p_base_rule_id => l_base_rule_id
914 , p_appl_id => p_appl_id
915 , p_resp_id => p_resp_id
916 , p_user_id => p_user_id
917 , p_terr_id => p_terr_id
918 , p_resource_type => p_resource_type
919 , p_resource_id => p_resource_id
920 , p_rule_rank => l_rule_rank
921 , p_rule_doc => l_rule_doc
922 );
923
924 --commented out WTP windows handling when creating rule
925 -- only site rule can have WTP windows and not other rule need to worry
926 -- about WTP windows when creating.
927 --This line should be uncommented when we add functioanlity to define
928 -- WTP windows any lower level rules.
929 --handle_rule_windows(l_rule_doc, NULL, p_window_names, p_window_descriptions);
930
931 x_rule_id :=
932 insert_rule(
933 p_rule_name => p_rule_name
934 , p_description => p_description
935 , p_base_rule_id => l_base_rule_id
936 , p_appl_id => p_appl_id
937 , p_resp_id => p_resp_id
938 , p_user_id => p_user_id
939 , p_terr_id => p_terr_id
940 , p_resource_type => p_resource_type
941 , p_resource_id => p_resource_id
942 , p_rule_rank => l_rule_rank
943 , p_enabled_flag => p_enabled_flag
944 , p_rule_doc => l_rule_doc
945 );
946
947 x_new_rule_doc := l_rule_doc.getClobVal();
948
949 IF fnd_api.to_boolean(p_commit) THEN
950 COMMIT;
951 END IF;
952
953 EXCEPTION
954 WHEN fnd_api.g_exc_error THEN
955 ROLLBACK TO csr_rule_create;
956 x_return_status := fnd_api.g_ret_sts_error;
957 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
958 IF l_debug = 'Y' THEN
959 debug('Create Rule Errored with ' || x_msg_data, l_api_name, fnd_log.level_error);
960 END IF;
961 WHEN fnd_api.g_exc_unexpected_error THEN
962 ROLLBACK TO csr_rule_create;
963 x_return_status := fnd_api.g_ret_sts_unexp_error;
964 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
965 IF l_debug = 'Y' THEN
966 debug('Create Rule Errored with ' || x_msg_data, l_api_name, fnd_log.level_error);
967 END IF;
968 WHEN OTHERS THEN
969 x_return_status := fnd_api.g_ret_sts_unexp_error;
970 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error) THEN
971 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
972 END IF;
973 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
974 ROLLBACK TO csr_rule_create;
975 IF l_debug = 'Y' THEN
976 debug('Create Rule Errored with ' || x_msg_data, l_api_name, fnd_log.level_error);
977 END IF;
978 END create_rule;
979
980 PROCEDURE update_rule(
981 p_api_version IN NUMBER
982 , p_init_msg_list IN VARCHAR2 DEFAULT NULL
983 , p_commit IN VARCHAR2 DEFAULT NULL
984 , x_return_status OUT NOCOPY VARCHAR2
985 , x_msg_data OUT NOCOPY VARCHAR2
986 , x_msg_count OUT NOCOPY NUMBER
987 , p_rule_id IN NUMBER
988 , p_object_version_number IN OUT NOCOPY NUMBER
989 , p_rule_name IN VARCHAR2 DEFAULT NULL
990 , p_description IN VARCHAR2 DEFAULT NULL
991 , p_base_rule_id IN NUMBER DEFAULT NULL
992 , p_appl_id IN NUMBER DEFAULT NULL
993 , p_resp_id IN NUMBER DEFAULT NULL
994 , p_user_id IN NUMBER DEFAULT NULL
995 , p_terr_id IN NUMBER DEFAULT NULL
996 , p_resource_type IN VARCHAR2 DEFAULT NULL
997 , p_resource_id IN NUMBER DEFAULT NULL
998 , p_enabled_flag IN VARCHAR2 DEFAULT NULL
999 , p_rule_doc IN XMLTYPE DEFAULT NULL
1000 , p_window_names IN jtf_varchar2_table_300 DEFAULT NULL
1001 , p_window_descriptions IN jtf_varchar2_table_1500 DEFAULT NULL
1002 , p_version_msgs IN jtf_varchar2_table_4000
1003 , p_force_propagation IN VARCHAR2 DEFAULT NULL
1004 , x_new_rule_doc OUT NOCOPY CLOB
1005 ) IS
1006 l_api_version CONSTANT NUMBER := 1.0;
1007 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_RULE';
1008 l_debug CONSTANT VARCHAR2(1) := fnd_profile.value('AFLOG_ENABLED');
1009 --
1010 CURSOR c_rule_details IS
1011 SELECT b.rule_id
1012 , b.object_version_number
1013 , b.base_rule_id
1014 , b.appl_id
1015 , b.resp_id
1016 , b.user_id
1017 , b.terr_id
1018 , b.resource_type
1019 , b.resource_id
1020 , b.enabled_flag
1021 , b.rule_rank
1022 , b.rule_doc
1023 , t.rule_name
1024 , t.description
1025 FROM csr_rules_b b, csr_rules_tl t
1026 WHERE b.rule_id = p_rule_id
1027 AND b.rule_id = t.rule_id
1028 AND t.language = userenv('LANG')
1029 FOR UPDATE NOWAIT;
1030
1031 l_rule c_rule_details%ROWTYPE;
1032 l_old_rule_doc XMLTYPE;
1033 --
1034 i PLS_INTEGER;
1035 l_notes VARCHAR2(4000);
1036 l_notes_dtl VARCHAR2(4000);
1037 l_note_id NUMBER;
1038 --
1039 BEGIN
1040 SAVEPOINT csr_rule_update;
1041
1042 -- Check for API Compatibility
1043 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
1044 RAISE fnd_api.g_exc_unexpected_error;
1045 END IF;
1046
1047 -- Initialize Message Stack if required
1048 IF fnd_api.to_boolean(p_init_msg_list) THEN
1049 fnd_msg_pub.initialize;
1050 END IF;
1051
1052 -- Initialize Return Status
1053 x_return_status := fnd_api.g_ret_sts_success;
1054
1055 IF l_debug = 'Y' THEN
1056 debug('Updating Rule#' || p_rule_id, l_api_name, fnd_log.level_event);
1057 debug(' --> Rule Name = ' || p_rule_name, l_api_name, fnd_log.level_statement);
1058 debug(' --> OVN = ' || p_object_version_number, l_api_name, fnd_log.level_statement);
1059 debug(' --> Base Rule = ' || p_base_rule_id, l_api_name, fnd_log.level_statement);
1060 debug(' --> Appl ID = ' || p_appl_id, l_api_name, fnd_log.level_statement);
1061 debug(' --> Resp ID = ' || p_resp_id, l_api_name, fnd_log.level_statement);
1062 debug(' --> User ID = ' || p_user_id, l_api_name, fnd_log.level_statement);
1063 debug(' --> Terr ID = ' || p_terr_id, l_api_name, fnd_log.level_statement);
1064 debug(' --> Res Type = ' || p_resource_type, l_api_name, fnd_log.level_statement);
1065 debug(' --> Res ID = ' || p_resource_id, l_api_name, fnd_log.level_statement);
1066 DECLARE
1067 buffer VARCHAR2(8000);
1068 BEGIN
1069 DBMS_XMLDOM.WRITETOBUFFER(DBMS_XMLDOM.NEWDOMDOCUMENT(p_rule_doc), buffer);
1070 debug(' --> XML Doc#1 = ' || substr(buffer, 1, 3900), l_api_name, fnd_log.level_statement);
1071 debug(' --> XML Doc#2 = ' || substr(buffer, 3901, 3900), l_api_name, fnd_log.level_statement);
1072 END;
1073 END IF;
1074
1075 OPEN c_rule_details;
1076 FETCH c_rule_details INTO l_rule;
1077 CLOSE c_rule_details;
1078
1079 IF l_rule.rule_id IS NULL THEN
1080 fnd_message.set_name('CSR', 'CSR_RULE_NOT_FOUND');
1081 fnd_message.set_token('RULE_ID', p_rule_id);
1082 fnd_msg_pub.ADD;
1083 RAISE fnd_api.g_exc_error;
1084 END IF;
1085
1086 IF l_rule.object_version_number <> p_object_version_number THEN
1087 fnd_message.set_name ('JTF', 'JTF_API_RECORD_NOT_FOUND');
1088 fnd_msg_pub.ADD;
1089 RAISE fnd_api.g_exc_error;
1090 END IF;
1091
1092 l_old_rule_doc := l_rule.rule_doc;
1093 p_object_version_number := p_object_version_number + 1;
1094 l_rule.base_rule_id := handle_miss_num(p_base_rule_id, l_rule.base_rule_id);
1095 l_rule.appl_id := handle_miss_num(p_appl_id, l_rule.appl_id);
1096 l_rule.resp_id := handle_miss_num(p_resp_id, l_rule.resp_id);
1097 l_rule.user_id := handle_miss_num(p_user_id, l_rule.user_id);
1098 l_rule.terr_id := handle_miss_num(p_terr_id, l_rule.terr_id);
1099 l_rule.resource_type := handle_miss_char(p_resource_type, l_rule.resource_type);
1100 l_rule.resource_id := handle_miss_num(p_resource_id, l_rule.resource_id);
1101 l_rule.enabled_flag := handle_miss_char(p_enabled_flag, l_rule.enabled_flag);
1102
1103 l_rule.rule_rank :=
1104 get_rule_rank(l_rule.appl_id, l_rule.resp_id, l_rule.user_id, l_rule.terr_id, l_rule.resource_id);
1105
1106 IF p_rule_doc IS NOT NULL THEN
1107 l_rule.rule_doc := p_rule_doc;
1108 END IF;
1109
1110 validate_rule(
1111 p_rule_id => l_rule.rule_id
1112 , p_rule_name => l_rule.rule_name
1113 , p_base_rule_id => l_rule.base_rule_id
1114 , p_appl_id => l_rule.appl_id
1115 , p_resp_id => l_rule.resp_id
1116 , p_user_id => l_rule.user_id
1117 , p_terr_id => l_rule.terr_id
1118 , p_resource_type => l_rule.resource_type
1119 , p_resource_id => l_rule.resource_id
1120 , p_rule_rank => l_rule.rule_rank
1121 , p_rule_doc => l_rule.rule_doc
1122 );
1123
1124 handle_rule_windows(l_rule.rule_doc, l_old_rule_doc, p_window_names, p_window_descriptions);
1125
1126 -- Update the Rule's Base Attributes
1127 UPDATE csr_rules_b
1128 SET object_version_number = p_object_version_number
1129 , base_rule_id = NVL(l_rule.base_rule_id, -1)
1130 , appl_id = NVL(l_rule.appl_id, g_not_specified)
1131 , resp_id = NVL(l_rule.resp_id, g_not_specified)
1132 , user_id = NVL(l_rule.user_id, g_not_specified)
1133 , terr_id = NVL(l_rule.terr_id, g_not_specified)
1134 , resource_type = NVL(l_rule.resource_type, '-')
1135 , resource_id = NVL(l_rule.resource_id, g_not_specified)
1136 , enabled_flag = l_rule.enabled_flag
1137 , rule_rank = l_rule.rule_rank
1138 , rule_doc = l_rule.rule_doc
1139 , last_updated_by = fnd_global.user_id
1140 , last_update_date = SYSDATE
1141 , last_update_login = fnd_global.login_id
1142 WHERE rule_id = p_rule_id;
1143
1144 -- Update the Rule's Translatable Attributes
1145 IF l_rule.rule_name <> p_rule_name
1146 OR NVL(p_description, '@@') <> NVL(l_rule.description, '@@')
1147 THEN
1148 UPDATE csr_rules_tl
1149 SET rule_name = NVL(p_rule_name, rule_name)
1150 , description = NVL(p_description, description)
1151 , last_updated_by = fnd_global.user_id
1152 , last_update_date = SYSDATE
1153 , last_update_login = fnd_global.login_id
1154 WHERE rule_id = p_rule_id
1155 AND userenv('LANG') IN (language, source_lang);
1156 END IF;
1157
1158 -- Propagate the Document to all the child rules based on this Rule
1159 IF p_rule_doc IS NOT NULL THEN
1160 propagate_to_child_rules(p_rule_id, p_rule_doc, NULL, p_force_propagation);
1161 END IF;
1162
1163 -- Create the Note
1164 IF p_version_msgs IS NOT NULL THEN
1165 i := p_version_msgs.FIRST;
1166
1167 WHILE i IS NOT NULL LOOP
1168 l_notes := SUBSTR(p_version_msgs(i), 1, INSTR(p_version_msgs(i), '@$@')-1);
1169 l_notes_dtl := SUBSTR(p_version_msgs(i), INSTR(p_version_msgs(i), '@$@')+3);
1170 IF l_notes IS NULL THEN
1171 l_notes := p_version_msgs(i);
1172 l_notes_dtl := NULL;
1173 END IF;
1174
1175 jtf_notes_pub.create_note(
1176 p_api_version => 1.0
1177 , x_return_status => x_return_status
1178 , x_msg_count => x_msg_count
1179 , x_msg_data => x_msg_data
1180 , p_source_object_id => p_rule_id
1181 , p_source_object_code => 'CSR_RULES'
1182 , p_notes => l_notes
1183 , p_notes_detail => l_notes_dtl
1184 , x_jtf_note_id => l_note_id
1185 );
1186
1187 i := p_version_msgs.NEXT(i);
1188 END LOOP;
1189 END IF;
1190
1191 x_new_rule_doc := l_rule.rule_doc.getClobVal();
1192
1193 IF fnd_api.to_boolean(p_commit) THEN
1194 COMMIT;
1195 END IF;
1196
1197 EXCEPTION
1198 WHEN fnd_api.g_exc_error THEN
1199 ROLLBACK TO csr_rule_update;
1200 x_return_status := fnd_api.g_ret_sts_error;
1201 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1202 IF l_debug = 'Y' THEN
1203 debug('Update Rule Errored with ' || x_msg_data, l_api_name, fnd_log.level_error);
1204 END IF;
1205 WHEN fnd_api.g_exc_unexpected_error THEN
1206 ROLLBACK TO csr_rule_update;
1207 x_return_status := fnd_api.g_ret_sts_unexp_error;
1208 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1209 IF l_debug = 'Y' THEN
1210 debug('Update Rule Errored with ' || x_msg_data, l_api_name, fnd_log.level_error);
1211 END IF;
1212 WHEN OTHERS THEN
1213 x_return_status := fnd_api.g_ret_sts_unexp_error;
1214 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1215 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1216 END IF;
1217 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1218 ROLLBACK TO csr_rule_update;
1219 IF l_debug = 'Y' THEN
1220 debug('Update Rule Errored with ' || x_msg_data, l_api_name, fnd_log.level_error);
1221 END IF;
1222 END update_rule;
1223
1224 PROCEDURE delete_rule(
1225 p_api_version IN NUMBER
1226 , p_init_msg_list IN VARCHAR2 DEFAULT NULL
1227 , p_commit IN VARCHAR2 DEFAULT NULL
1228 , x_return_status OUT NOCOPY VARCHAR2
1229 , x_msg_data OUT NOCOPY VARCHAR2
1230 , x_msg_count OUT NOCOPY NUMBER
1231 , p_rule_id IN NUMBER
1232 ) IS
1233 l_api_version CONSTANT NUMBER := 1.0;
1234 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_RULE';
1235 l_debug CONSTANT VARCHAR2(1) := fnd_profile.value('AFLOG_ENABLED');
1236 --
1237 CURSOR c_child_rules IS
1238 SELECT rule_id, base_rule_id, LEVEL
1239 FROM csr_rules_b
1240 START WITH rule_id = p_rule_id
1241 CONNECT BY base_rule_id = PRIOR rule_id;
1242 --
1243 CURSOR c_rule_notes(v_rule_id NUMBER) IS
1244 SELECT n.jtf_note_id
1245 FROM jtf_notes_b n
1246 WHERE n.source_object_code = 'CSR_RULES'
1247 AND n.source_object_id = v_rule_id;
1248 --
1249 l_note_id NUMBER;
1250 BEGIN
1251 SAVEPOINT csr_rule_delete;
1252
1253 -- Check for API Compatibility
1254 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
1255 RAISE fnd_api.g_exc_unexpected_error;
1256 END IF;
1257
1258 -- Initialize Message Stack if required
1259 IF fnd_api.to_boolean(p_init_msg_list) THEN
1260 fnd_msg_pub.initialize;
1261 END IF;
1262
1263 -- Initialize Return Status
1264 x_return_status := fnd_api.g_ret_sts_success;
1265
1266 IF l_debug = 'Y' THEN
1267 debug('Deleting Rule#' || p_rule_id || ' and the entire hierarchy', l_api_name, fnd_log.level_event);
1268 END IF;
1269
1270
1271 FOR v_child_rule IN c_child_rules LOOP
1272 IF l_debug = 'Y' THEN
1273 debug('Deleting Rule#' || v_child_rule.rule_id || ' based on ' || v_child_rule.base_rule_id || ' at level ' || v_child_rule.LEVEL, l_api_name, fnd_log.level_statement);
1274 END IF;
1275
1276 -- Delete all the Dependent Objects like Notes.
1277 FOR v_note IN c_rule_notes(v_child_rule.rule_id) LOOP
1278 IF l_debug = 'Y' THEN
1279 debug(' Deleting Notes #' || v_note.jtf_note_id, l_api_name, fnd_log.level_statement);
1280 END IF;
1281 jtf_notes_pub.secure_delete_note(
1282 p_api_version => 1.0
1283 , x_return_status => x_return_status
1284 , x_msg_count => x_msg_count
1285 , x_msg_data => x_msg_data
1286 , p_jtf_note_id => v_note.jtf_note_id
1287 , p_use_AOL_security => fnd_api.g_false
1288 );
1289
1290 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1291 RAISE fnd_api.g_exc_error;
1292 END IF;
1293 END LOOP;
1294
1295 DELETE csr_rules_tl WHERE rule_id = v_child_rule.rule_id;
1296 DELETE csr_rules_b WHERE rule_id = v_child_rule.rule_id;
1297 END LOOP;
1298
1299 IF fnd_api.to_boolean(p_commit) THEN
1300 COMMIT;
1301 END IF;
1302
1303 EXCEPTION
1304 WHEN fnd_api.g_exc_error THEN
1305 ROLLBACK TO csr_rule_delete;
1306 x_return_status := fnd_api.g_ret_sts_error;
1307 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1308 WHEN fnd_api.g_exc_unexpected_error THEN
1309 ROLLBACK TO csr_rule_delete;
1310 x_return_status := fnd_api.g_ret_sts_unexp_error;
1311 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1312 WHEN OTHERS THEN
1313 x_return_status := fnd_api.g_ret_sts_unexp_error;
1314 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1315 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1316 END IF;
1317 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1318 ROLLBACK TO csr_rule_delete;
1319 END delete_rule;
1320
1321 FUNCTION get_sch_parameter_value(
1322 p_parameter_name IN VARCHAR2
1323 , p_appl_id IN NUMBER DEFAULT NULL
1324 , p_resp_id IN NUMBER DEFAULT NULL
1325 , p_user_id IN NUMBER DEFAULT NULL
1326 , p_terr_id IN NUMBER DEFAULT NULL
1327 , p_resource_type IN VARCHAR2 DEFAULT NULL
1328 , p_resource_id IN NUMBER DEFAULT NULL
1329 ) RETURN VARCHAR2 IS
1330 --
1331 l_param_value VARCHAR2(255);
1332 l_prefix VARCHAR2(2000) := '/schedulerRule/';
1333 l_debug CONSTANT VARCHAR2(1) := fnd_profile.value('AFLOG_ENABLED');
1334 l_api_name CONSTANT VARCHAR2(30) := 'get_sch_parameter_value';
1335
1336 --
1337 CURSOR c_matching_rules_noterr(v_param_path VARCHAR2) IS
1338 SELECT extractValue(r.rule_doc, v_param_path)
1339 FROM csr_rules_b r
1340 WHERE r.appl_id IN (p_appl_id, g_not_specified)
1341 AND r.resp_id IN (p_resp_id, g_not_specified)
1342 AND r.user_id IN (p_user_id, g_not_specified)
1343 AND r.terr_id = g_not_specified
1344 AND r.resource_type IN (p_resource_type, '-')
1345 AND r.resource_id IN (p_resource_id, g_not_specified)
1346 AND r.enabled_flag = 'Y'
1347 AND existsNode(r.rule_doc, v_param_path) = 1
1348 ORDER BY rule_rank DESC;
1349
1350 CURSOR c_matching_rules_terr(v_param_path VARCHAR2) IS
1351 SELECT extractValue(r.rule_doc, v_param_path)
1352 FROM csr_rules_b r
1353 , (
1354 SELECT terr_id, LEVEL terr_level
1355 FROM jtf_terr_all
1356 START WITH terr_id = p_terr_id
1357 CONNECT BY NOCYCLE PRIOR parent_territory_id = terr_id
1358 ) t
1359 WHERE r.appl_id IN (p_appl_id, g_not_specified)
1360 AND r.resp_id IN (p_resp_id, g_not_specified)
1361 AND r.user_id IN (p_user_id, g_not_specified)
1362 AND r.terr_id IN (t.terr_id, g_not_specified)
1363 AND r.resource_type IN (p_resource_type, '-')
1364 AND r.resource_id IN (p_resource_id, g_not_specified)
1365 AND r.enabled_flag = 'Y'
1366 AND existsNode(r.rule_doc, v_param_path) = 1
1367 ORDER BY rule_rank DESC;
1368 BEGIN
1369
1370 IF l_debug = 'Y' THEN
1371 debug('Getting Value of a Parameter:' || p_parameter_name, l_api_name, fnd_log.level_statement);
1372 END IF;
1373
1374 IF l_debug = 'Y' THEN
1375 debug('Input Parameters for the API are:' || p_parameter_name ||
1376 ' p_appl_id ' || p_appl_id ||
1377 ' p_resp_id' || p_resp_id ||
1378 ' p_user_id' || p_user_id ||
1379 ' p_terr_id' || p_terr_id ||
1380 ' p_resource_id' || p_resource_id ||
1381 ' p_resource_type' || p_resource_type , l_api_name, fnd_log.level_statement);
1382 END IF;
1383
1384 IF p_parameter_name IS NULL THEN
1385 RETURN NULL;
1386 END IF;
1387
1388 IF SUBSTR(p_parameter_name, 1, 2 ) = 'sp' THEN
1389 l_prefix := l_prefix || 'schedulerParameters/';
1390 ELSIF SUBSTR(p_parameter_name, 1, 2 ) = 'rc' THEN
1391 l_prefix := l_prefix || 'routerConfig/';
1392 END IF;
1393
1394 IF p_terr_id IS NULL THEN
1395 OPEN c_matching_rules_noterr( l_prefix || p_parameter_name);
1396 FETCH c_matching_rules_noterr INTO l_param_value;
1397 CLOSE c_matching_rules_noterr;
1398 ELSE
1399 OPEN c_matching_rules_terr( l_prefix || p_parameter_name);
1400 FETCH c_matching_rules_terr INTO l_param_value;
1401 CLOSE c_matching_rules_terr;
1402 END IF;
1403
1404 IF l_debug = 'Y' THEN
1405 debug('The Value of the Parameter:' || p_parameter_name || ' is: ' || l_param_value, l_api_name, fnd_log.level_statement);
1406 END IF;
1407
1408 RETURN l_param_value;
1409 END get_sch_parameter_value;
1410
1411 PROCEDURE get_scheduler_rules(
1412 p_api_version IN NUMBER
1413 , p_init_msg_list IN VARCHAR2
1414 , x_return_status OUT NOCOPY VARCHAR2
1415 , x_msg_data OUT NOCOPY VARCHAR2
1416 , x_msg_count OUT NOCOPY NUMBER
1417 , p_appl_id IN NUMBER
1418 , p_resp_id IN NUMBER
1419 , p_user_id IN NUMBER
1420 , p_res_tbl IN csf_resource_tbl
1421 , x_res_rules_tbl OUT NOCOPY csr_resource_rules_tbl
1422 ) IS
1423 l_api_version CONSTANT NUMBER := 1.0;
1424 l_api_name CONSTANT VARCHAR2(30) := 'GET_SCHEDULER_RULES';
1425 l_debug CONSTANT VARCHAR2(1) := fnd_profile.value('AFLOG_ENABLED');
1426 --
1427 l_rules_wo_res_query CONSTANT VARCHAR2(1000) :=
1428 'SELECT r.*
1429 FROM csr_rules_vl r
1430 WHERE r.appl_id IN (:appl_id, -9999)
1431 AND r.resp_id IN (:resp_id, -9999)
1432 AND r.user_id IN (:user_id, -9999)
1433 AND r.terr_id = -9999
1434 AND r.resource_type = ''-''
1435 AND r.resource_id = -9999
1436 AND r.enabled_flag = ''Y''
1437 ORDER BY r.rule_rank DESC';
1438
1439 l_rules_wo_terr_query CONSTANT VARCHAR2(1000) :=
1440 'SELECT r.*
1441 FROM csr_rules_vl r
1442 WHERE r.appl_id IN (:appl_id, -9999)
1443 AND r.resp_id IN (:resp_id, -9999)
1444 AND r.user_id IN (:user_id, -9999)
1445 AND r.terr_id = -9999
1446 AND r.resource_type IN (:res_type, ''-'')
1447 AND r.resource_id IN (:res_id, -9999)
1448 AND r.enabled_flag = ''Y''
1449 ORDER BY r.rule_rank DESC';
1450
1451 l_rules_with_terr_query CONSTANT VARCHAR2(1000) :=
1452 'SELECT r.*
1453 FROM csr_rules_vl r
1454 , (SELECT terr_id, LEVEL terr_level FROM jtf_terr_all
1455 START WITH terr_id = :terr_id
1456 CONNECT BY NOCYCLE PRIOR parent_territory_id = terr_id
1457 UNION ALL SELECT -9999, 9999999 FROM DUAL
1458 ) t
1459 WHERE r.appl_id IN (:appl_id, -9999)
1460 AND r.resp_id IN (:resp_id, -9999)
1461 AND r.user_id IN (:user_id, -9999)
1462 AND r.terr_id = t.terr_id
1463 AND r.resource_type IN (:res_type, ''-'')
1464 AND r.resource_id IN (:res_id, -9999)
1465 AND r.enabled_flag = ''Y''
1466 ORDER BY r.rule_rank DESC, t.terr_level ASC';
1467
1468 c_rules SYS_REFCURSOR;
1469 --
1470 i PLS_INTEGER;
1471 l_res csf_resource;
1472 l_rule csr_rules_vl%ROWTYPE;
1473 l_rule_tbl csr_rule_tbl;
1474 BEGIN
1475 -- Check for API Compatibility
1476 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
1477 RAISE fnd_api.g_exc_unexpected_error;
1478 END IF;
1479
1480 -- Initialize Message Stack if required
1481 IF fnd_api.to_boolean(p_init_msg_list) THEN
1482 fnd_msg_pub.initialize;
1483 END IF;
1484
1485 -- Initialize Return Status
1486 x_return_status := fnd_api.g_ret_sts_success;
1487
1488 IF l_debug = 'Y' THEN
1489 debug('Getting Rules for given eligibility', l_api_name, fnd_log.level_event);
1490 debug(' --> Appl ID = ' || p_appl_id, l_api_name, fnd_log.level_event);
1491 debug(' --> Resp ID = ' || p_resp_id, l_api_name, fnd_log.level_event);
1492 debug(' --> User ID = ' || p_user_id, l_api_name, fnd_log.level_event);
1493 END IF;
1494
1495 IF x_res_rules_tbl IS NULL THEN
1496 x_res_rules_tbl := csr_resource_rules_tbl();
1497 END IF;
1498
1499 l_rule_tbl := csr_rule_tbl();
1500
1501 IF p_res_tbl IS NOT NULL THEN
1502 i := p_res_tbl.FIRST;
1503 WHILE i IS NOT NULL LOOP
1504
1505 l_res := p_res_tbl(i);
1506 l_rule_tbl.DELETE;
1507
1508 IF l_debug = 'Y' THEN
1509 debug(' --> Terr ID = ' || l_res.terr_id, l_api_name, fnd_log.level_event);
1510 debug(' --> Res Type = ' || l_res.resource_type, l_api_name, fnd_log.level_event);
1511 debug(' --> Res ID = ' || l_res.resource_id, l_api_name, fnd_log.level_event);
1512 END IF;
1513
1514 IF l_res.terr_id IS NOT NULL AND l_res.terr_id <> g_not_specified THEN
1515 OPEN c_rules FOR l_rules_with_terr_query USING l_res.terr_id, p_appl_id, p_resp_id, p_user_id, l_res.resource_type, l_res.resource_id;
1516 ELSE
1517 OPEN c_rules FOR l_rules_wo_terr_query USING p_appl_id, p_resp_id, p_user_id, l_res.resource_type, l_res.resource_id;
1518 END IF;
1519
1520 LOOP
1521 FETCH c_rules INTO l_rule;
1522 EXIT WHEN c_rules%NOTFOUND;
1523
1524 IF l_debug = 'Y' THEN
1525 debug(' ---> Fetched Rule with ID = ' || l_rule.rule_id || ' : Rank = ' || l_rule.rule_rank, l_api_name, fnd_log.level_statement);
1526 END IF;
1527
1528 l_rule_tbl.EXTEND;
1529 l_rule_tbl(l_rule_tbl.COUNT) :=
1530 csr_rule(
1531 l_rule.rule_id
1532 , l_rule.rule_name
1533 , l_rule.object_version_number
1534 , l_rule.base_rule_id
1535 , l_rule.appl_id
1536 , l_rule.resp_id
1537 , l_rule.user_id
1538 , l_rule.terr_id
1539 , l_rule.resource_type
1540 , l_rule.resource_id
1541 , l_rule.rule_rank
1542 , l_rule.rule_doc.getClobVal()
1543 );
1544 END LOOP;
1545
1546 CLOSE c_rules;
1547
1548 x_res_rules_tbl.EXTEND;
1549 x_res_rules_tbl(x_res_rules_tbl.COUNT) := l_rule_tbl;
1550
1551 i := p_res_tbl.NEXT(i);
1552 END LOOP;
1553 ELSE
1554 OPEN c_rules FOR l_rules_wo_res_query USING p_appl_id, p_resp_id, p_user_id;
1555 LOOP
1556 FETCH c_rules INTO l_rule;
1557 EXIT WHEN c_rules%NOTFOUND;
1558
1559 IF l_debug = 'Y' THEN
1560 debug(' ---> Fetched Rule with ID = ' || l_rule.rule_id || ' : Rank = ' || l_rule.rule_rank, l_api_name, fnd_log.level_statement);
1561 END IF;
1562
1563 l_rule_tbl.EXTEND;
1564 l_rule_tbl(l_rule_tbl.COUNT) :=
1565 csr_rule(
1566 l_rule.rule_id
1567 , l_rule.rule_name
1568 , l_rule.object_version_number
1569 , l_rule.base_rule_id
1570 , l_rule.appl_id
1571 , l_rule.resp_id
1572 , l_rule.user_id
1573 , l_rule.terr_id
1574 , l_rule.resource_type
1575 , l_rule.resource_id
1576 , l_rule.rule_rank
1577 , l_rule.rule_doc.getClobVal()
1578 );
1579 END LOOP;
1580 CLOSE c_rules;
1581
1582 x_res_rules_tbl.EXTEND;
1583 x_res_rules_tbl(x_res_rules_tbl.COUNT) := l_rule_tbl;
1584 END IF;
1585 EXCEPTION
1586 WHEN fnd_api.g_exc_error THEN
1587 x_return_status := fnd_api.g_ret_sts_error;
1588 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1589 WHEN fnd_api.g_exc_unexpected_error THEN
1590 x_return_status := fnd_api.g_ret_sts_unexp_error;
1591 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1592 WHEN OTHERS THEN
1593 x_return_status := fnd_api.g_ret_sts_unexp_error;
1594 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error) THEN
1595 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1596 END IF;
1597 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1598 END get_scheduler_rules;
1599
1600 PROCEDURE process_webadi_action(
1601 p_action IN VARCHAR2
1602 , p_rule_id IN NUMBER
1603 , p_object_version_number IN NUMBER
1604 , p_rule_name IN VARCHAR2
1605 , p_description IN VARCHAR2
1606 , p_base_rule_id IN NUMBER
1607 , p_appl_id IN NUMBER
1608 , p_resp_id IN NUMBER
1609 , p_user_id IN NUMBER
1610 , p_terr_id IN NUMBER
1611 , p_resource_type IN VARCHAR2
1612 , p_resource_id IN NUMBER
1613 , p_enabled_flag IN VARCHAR2
1614 , p_sp_plan_scope IN NUMBER
1615 , p_sp_max_plan_options IN NUMBER
1616 , p_sp_max_resources IN NUMBER
1617 , p_sp_max_calc_time IN NUMBER
1618 , p_sp_max_overtime IN NUMBER
1619 , p_sp_wtp_threshold IN NUMBER
1620 , p_sp_enforce_plan_window IN VARCHAR2
1621 , p_sp_consider_standby_shifts IN VARCHAR2
1622 , p_sp_spares_mandatory IN VARCHAR2
1623 , p_sp_spares_source IN VARCHAR2
1624 , p_sp_min_task_length IN NUMBER
1625 , p_sp_default_shift_duration IN NUMBER
1626 , p_sp_dist_last_child_effort IN VARCHAR2
1627 , p_sp_pick_contract_resources IN VARCHAR2
1628 , p_sp_pick_ib_resources IN VARCHAR2
1629 , p_sp_pick_territory_resources IN VARCHAR2
1630 , p_sp_pick_skilled_resources IN VARCHAR2
1631 , p_sp_auto_sch_default_query IN NUMBER
1632 , p_sp_auto_reject_sts_id_spares IN NUMBER
1633 , p_sp_auto_reject_sts_id_others IN NUMBER
1634 , p_sp_force_optimizer_to_group IN VARCHAR2
1635 , p_sp_optimizer_success_perc IN NUMBER
1636 , p_sp_commutes_position IN VARCHAR2
1637 , p_sp_commute_excluded_time IN NUMBER
1638 , p_sp_commute_home_empty_trip IN VARCHAR2
1639 , p_sp_router_mode IN VARCHAR2
1640 , p_sp_travel_time_extra IN NUMBER
1641 , p_sp_default_router_enabled IN VARCHAR2
1642 , p_sp_default_travel_distance IN NUMBER
1643 , p_sp_default_travel_duration IN NUMBER
1644 , p_sp_max_distance_in_group IN NUMBER
1645 , p_sp_max_dist_to_skip_actual IN NUMBER
1646 , p_rc_router_calc_type IN VARCHAR2
1647 , p_rc_consider_toll_roads IN VARCHAR2
1648 , p_rc_route_func_delay_0 IN NUMBER
1649 , p_rc_route_func_delay_1 IN NUMBER
1650 , p_rc_route_func_delay_2 IN NUMBER
1651 , p_rc_route_func_delay_3 IN NUMBER
1652 , p_rc_route_func_delay_4 IN NUMBER
1653 , p_rc_estimate_first_boundary IN NUMBER
1654 , p_rc_estimate_second_boundary IN NUMBER
1655 , p_rc_estimate_first_avg_speed IN NUMBER
1656 , p_rc_estimate_second_avg_speed IN NUMBER
1657 , p_rc_estimate_third_avg_speed IN NUMBER
1658 , p_cp_task_per_day_delayed IN NUMBER
1659 , p_cp_task_per_min_early IN NUMBER
1660 , p_cp_task_per_min_late IN NUMBER
1661 , p_cp_tls_per_day_extra IN NUMBER
1662 , p_cp_tls_per_child_extra IN NUMBER
1663 , p_cp_parts_violation IN NUMBER
1664 , p_cp_res_per_min_overtime IN NUMBER
1665 , p_cp_res_assigned_not_pref IN NUMBER
1666 , p_cp_res_skill_level IN NUMBER
1667 , p_cp_standby_shift_usage IN NUMBER
1668 , p_cp_travel_per_unit_distance IN NUMBER
1669 , p_cp_travel_per_unit_duration IN NUMBER
1670 , p_cp_defer_same_site IN NUMBER
1671 ) IS
1672 l_api_version CONSTANT NUMBER := 1.0;
1673 l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_WEBADI_ACTION';
1674 l_debug CONSTANT VARCHAR2(1) := fnd_profile.value('AFLOG_ENABLED');
1675 --
1676 l_return_status VARCHAR2(1);
1677 l_msg_data VARCHAR2(2000);
1678 l_msg_count NUMBER;
1679 l_object_version_num NUMBER;
1680 --
1681 l_new_param_values jtf_varchar2_table_100;
1682 --
1683 l_rule_document DBMS_XMLDOM.DOMDocument;
1684 l_root_element DBMS_XMLDOM.DOMELEMENT;
1685 l_dom_node DBMS_XMLDOM.DOMNode;
1686 l_dom_text_node DBMS_XMLDOM.DOMNode;
1687 l_dom_node_value VARCHAR2(100);
1688 l_parent_dom_node DBMS_XMLDOM.DOMNode;
1689 --
1690 CURSOR c_rule_details IS
1691 SELECT b.rule_doc, b.rule_rank, t.rule_name, t.description
1692 FROM csr_rules_b b, csr_rules_tl t
1693 WHERE b.rule_id = p_rule_id
1694 FOR UPDATE OF b.rule_doc, t.rule_name NOWAIT;
1695 l_rule c_rule_details%ROWTYPE;
1696 l_new_rule_doc CLOB;
1697 BEGIN
1698 IF l_debug = 'Y' THEN
1699 debug('Processing WebADI Action ' || p_action || ' for RuleID = ' || p_rule_id || ' with OVN = ' || p_object_version_number, l_api_name, fnd_log.level_event);
1700 debug(' --> Parameter : Rule Name = ' || p_rule_name, l_api_name, fnd_log.level_statement);
1701 debug(' --> Parameter : Description = ' || p_description, l_api_name, fnd_log.level_statement);
1702 debug(' --> Parameter : Plan Scope = ' || p_sp_plan_scope, l_api_name, fnd_log.level_statement);
1703 debug(' --> Parameter : Max Plan Options = ' || p_sp_max_plan_options, l_api_name, fnd_log.level_statement);
1704 debug(' --> Parameter : Max Resources = ' || p_sp_max_resources, l_api_name, fnd_log.level_statement);
1705 debug(' --> Parameter : Max Calc Time = ' || p_sp_max_calc_time, l_api_name, fnd_log.level_statement);
1706 debug(' --> Parameter : Max Overtime = ' || p_sp_max_overtime, l_api_name, fnd_log.level_statement);
1707 debug(' --> Parameter : Wtp Threshold = ' || p_sp_wtp_threshold, l_api_name, fnd_log.level_statement);
1708 debug(' --> Parameter : Enforce Plan Window = ' || p_sp_enforce_plan_window, l_api_name, fnd_log.level_statement);
1709 debug(' --> Parameter : Consider Standby Shifts = ' || p_sp_consider_standby_shifts, l_api_name, fnd_log.level_statement);
1710 debug(' --> Parameter : Spares Mandatory = ' || p_sp_spares_mandatory, l_api_name, fnd_log.level_statement);
1711 debug(' --> Parameter : Spares Source = ' || p_sp_spares_source, l_api_name, fnd_log.level_statement);
1712 debug(' --> Parameter : Min Task Length = ' || p_sp_min_task_length, l_api_name, fnd_log.level_statement);
1713 debug(' --> Parameter : Default Shift Duration = ' || p_sp_default_shift_duration, l_api_name, fnd_log.level_statement);
1714 debug(' --> Parameter : Dist Last Child Effort = ' || p_sp_dist_last_child_effort, l_api_name, fnd_log.level_statement);
1715 debug(' --> Parameter : Pick Contract Resources = ' || p_sp_pick_contract_resources, l_api_name, fnd_log.level_statement);
1716 debug(' --> Parameter : Pick IB Resources = ' || p_sp_pick_ib_resources, l_api_name, fnd_log.level_statement);
1717 debug(' --> Parameter : Pick Territory Resources = ' || p_sp_pick_territory_resources, l_api_name, fnd_log.level_statement);
1718 debug(' --> Parameter : Pick Skilled Resources = ' || p_sp_pick_skilled_resources, l_api_name, fnd_log.level_statement);
1719 debug(' --> Parameter : Auto Sch Default Query = ' || p_sp_auto_sch_default_query, l_api_name, fnd_log.level_statement);
1720 debug(' --> Parameter : Auto Reject Sts Id Spares = ' || p_sp_auto_reject_sts_id_spares, l_api_name, fnd_log.level_statement);
1721 debug(' --> Parameter : Auto Reject Sts Id Others = ' || p_sp_auto_reject_sts_id_others, l_api_name, fnd_log.level_statement);
1722 debug(' --> Parameter : Force Optimizer To Group = ' || p_sp_force_optimizer_to_group, l_api_name, fnd_log.level_statement);
1723 debug(' --> Parameter : Optimizer Success Perc = ' || p_sp_optimizer_success_perc, l_api_name, fnd_log.level_statement);
1724 debug(' --> Parameter : Commutes Position = ' || p_sp_commutes_position, l_api_name, fnd_log.level_statement);
1725 debug(' --> Parameter : Commute Excluded Time = ' || p_sp_commute_excluded_time, l_api_name, fnd_log.level_statement);
1726 debug(' --> Parameter : Commute Home Empty Trip = ' || p_sp_commute_home_empty_trip, l_api_name, fnd_log.level_statement);
1727 debug(' --> Parameter : Router Mode = ' || p_sp_router_mode, l_api_name, fnd_log.level_statement);
1728 debug(' --> Parameter : Travel Time Extra = ' || p_sp_travel_time_extra, l_api_name, fnd_log.level_statement);
1729 debug(' --> Parameter : Default Router Enabled = ' || p_sp_default_router_enabled, l_api_name, fnd_log.level_statement);
1730 debug(' --> Parameter : Default Travel Distance = ' || p_sp_default_travel_distance, l_api_name, fnd_log.level_statement);
1731 debug(' --> Parameter : Default Travel Duration = ' || p_sp_default_travel_duration, l_api_name, fnd_log.level_statement);
1732 debug(' --> Parameter : Max Distance In Group = ' || p_sp_max_distance_in_group, l_api_name, fnd_log.level_statement);
1733 debug(' --> Parameter : Max Dist To Skip Actual = ' || p_sp_max_dist_to_skip_actual, l_api_name, fnd_log.level_statement);
1734 debug(' --> Parameter : Router Calc Type = ' || p_rc_router_calc_type, l_api_name, fnd_log.level_statement);
1735 debug(' --> Parameter : Consider Toll Roads = ' || p_rc_consider_toll_roads, l_api_name, fnd_log.level_statement);
1736 debug(' --> Parameter : Route Func Delay 0 = ' || p_rc_route_func_delay_0, l_api_name, fnd_log.level_statement);
1737 debug(' --> Parameter : Route Func Delay 1 = ' || p_rc_route_func_delay_1, l_api_name, fnd_log.level_statement);
1738 debug(' --> Parameter : Route Func Delay 2 = ' || p_rc_route_func_delay_2, l_api_name, fnd_log.level_statement);
1739 debug(' --> Parameter : Route Func Delay 3 = ' || p_rc_route_func_delay_3, l_api_name, fnd_log.level_statement);
1740 debug(' --> Parameter : Route Func Delay 4 = ' || p_rc_route_func_delay_4, l_api_name, fnd_log.level_statement);
1741 debug(' --> Parameter : Estimate First Boundary = ' || p_rc_estimate_first_boundary, l_api_name, fnd_log.level_statement);
1742 debug(' --> Parameter : Estimate Second Boundary = ' || p_rc_estimate_second_boundary, l_api_name, fnd_log.level_statement);
1743 debug(' --> Parameter : Estimate First Avg Speed = ' || p_rc_estimate_first_avg_speed, l_api_name, fnd_log.level_statement);
1744 debug(' --> Parameter : Estimate Second Avg Speed = ' || p_rc_estimate_second_avg_speed, l_api_name, fnd_log.level_statement);
1745 debug(' --> Parameter : Estimate Third Avg Speed = ' || p_rc_estimate_third_avg_speed, l_api_name, fnd_log.level_statement);
1746 debug(' --> Parameter : Task Per Day Delayed = ' || p_cp_task_per_day_delayed, l_api_name, fnd_log.level_statement);
1747 debug(' --> Parameter : Task Per Min Early = ' || p_cp_task_per_min_early, l_api_name, fnd_log.level_statement);
1748 debug(' --> Parameter : Task Per Min Late = ' || p_cp_task_per_min_late, l_api_name, fnd_log.level_statement);
1749 debug(' --> Parameter : Tls Per Day Extra = ' || p_cp_tls_per_day_extra, l_api_name, fnd_log.level_statement);
1750 debug(' --> Parameter : Tls Per Child Extra = ' || p_cp_tls_per_child_extra, l_api_name, fnd_log.level_statement);
1751 debug(' --> Parameter : Parts Violation = ' || p_cp_parts_violation, l_api_name, fnd_log.level_statement);
1752 debug(' --> Parameter : Res Per Min Overtime = ' || p_cp_res_per_min_overtime, l_api_name, fnd_log.level_statement);
1753 debug(' --> Parameter : Res Assigned Not Pref = ' || p_cp_res_assigned_not_pref, l_api_name, fnd_log.level_statement);
1754 debug(' --> Parameter : Res Skill Level = ' || p_cp_res_skill_level, l_api_name, fnd_log.level_statement);
1755 debug(' --> Parameter : Standby Shift Usage = ' || p_cp_standby_shift_usage, l_api_name, fnd_log.level_statement);
1756 debug(' --> Parameter : Travel Per Unit Distance = ' || p_cp_travel_per_unit_distance, l_api_name, fnd_log.level_statement);
1757 debug(' --> Parameter : Travel Per Unit Duration = ' || p_cp_travel_per_unit_duration, l_api_name, fnd_log.level_statement);
1758 debug(' --> Parameter : Defer Same Site = ' || p_cp_defer_same_site, l_api_name, fnd_log.level_statement);
1759 END IF;
1760
1761 IF p_action = 'UPDATE' THEN
1762 OPEN c_rule_details;
1763 FETCH c_rule_details INTO l_rule;
1764 CLOSE c_rule_details;
1765
1766 l_new_param_values := jtf_varchar2_table_100();
1767 l_new_param_values.extend(57);
1768 l_new_param_values(01) := p_sp_plan_scope;
1769 l_new_param_values(02) := p_sp_max_plan_options;
1770 l_new_param_values(03) := p_sp_max_resources;
1771 l_new_param_values(04) := p_sp_max_calc_time;
1772 l_new_param_values(05) := p_sp_max_overtime;
1773 l_new_param_values(06) := p_sp_wtp_threshold;
1774 l_new_param_values(07) := p_sp_enforce_plan_window;
1775 l_new_param_values(08) := p_sp_consider_standby_shifts;
1776 l_new_param_values(09) := p_sp_spares_mandatory;
1777 l_new_param_values(10) := p_sp_spares_source;
1778 l_new_param_values(11) := p_sp_min_task_length;
1779 l_new_param_values(12) := p_sp_default_shift_duration;
1780 l_new_param_values(13) := p_sp_dist_last_child_effort;
1781 l_new_param_values(14) := p_sp_pick_contract_resources;
1782 l_new_param_values(15) := p_sp_pick_ib_resources;
1783 l_new_param_values(16) := p_sp_pick_territory_resources;
1784 l_new_param_values(17) := p_sp_pick_skilled_resources;
1785 l_new_param_values(18) := p_sp_auto_sch_default_query;
1786 l_new_param_values(19) := p_sp_auto_reject_sts_id_spares;
1787 l_new_param_values(20) := p_sp_auto_reject_sts_id_others;
1788 l_new_param_values(21) := p_sp_force_optimizer_to_group;
1789 l_new_param_values(22) := p_sp_optimizer_success_perc;
1790 l_new_param_values(23) := p_sp_commutes_position;
1791 l_new_param_values(24) := p_sp_commute_excluded_time;
1792 l_new_param_values(25) := p_sp_commute_home_empty_trip;
1793 l_new_param_values(26) := p_sp_router_mode;
1794 l_new_param_values(27) := p_sp_travel_time_extra;
1795 l_new_param_values(28) := p_sp_default_router_enabled;
1796 l_new_param_values(29) := p_sp_default_travel_distance;
1797 l_new_param_values(30) := p_sp_default_travel_duration;
1798 l_new_param_values(31) := p_sp_max_distance_in_group;
1799 l_new_param_values(32) := p_sp_max_dist_to_skip_actual;
1800 l_new_param_values(33) := p_rc_router_calc_type;
1801 l_new_param_values(34) := p_rc_consider_toll_roads;
1802 l_new_param_values(35) := p_rc_route_func_delay_0;
1803 l_new_param_values(36) := p_rc_route_func_delay_1;
1804 l_new_param_values(37) := p_rc_route_func_delay_2;
1805 l_new_param_values(38) := p_rc_route_func_delay_3;
1806 l_new_param_values(39) := p_rc_route_func_delay_4;
1807 l_new_param_values(40) := p_rc_estimate_first_boundary;
1808 l_new_param_values(41) := p_rc_estimate_second_boundary;
1809 l_new_param_values(42) := p_rc_estimate_first_avg_speed;
1810 l_new_param_values(43) := p_rc_estimate_second_avg_speed;
1811 l_new_param_values(44) := p_rc_estimate_third_avg_speed;
1812 l_new_param_values(45) := p_cp_task_per_day_delayed;
1813 l_new_param_values(46) := p_cp_task_per_min_early;
1814 l_new_param_values(47) := p_cp_task_per_min_late;
1815 l_new_param_values(48) := p_cp_tls_per_day_extra;
1816 l_new_param_values(49) := p_cp_tls_per_child_extra;
1817 l_new_param_values(50) := p_cp_parts_violation;
1818 l_new_param_values(51) := p_cp_res_per_min_overtime;
1819 l_new_param_values(52) := p_cp_res_assigned_not_pref;
1820 l_new_param_values(53) := p_cp_res_skill_level;
1821 l_new_param_values(54) := p_cp_standby_shift_usage;
1822 l_new_param_values(55) := p_cp_travel_per_unit_distance;
1823 l_new_param_values(56) := p_cp_travel_per_unit_duration;
1824 l_new_param_values(57) := p_cp_defer_same_site;
1825
1826 l_rule_document := DBMS_XMLDOM.newDOMDocument(l_rule.rule_doc);
1827 l_root_element := DBMS_XMLDOM.getDocumentElement(l_rule_document);
1828
1829 FOR i IN 1..g_rule_param_names_tbl.COUNT LOOP
1830 l_dom_node := DBMS_XMLDOM.item(DBMS_XMLDOM.getElementsByTagName(l_rule_document, g_rule_param_names_tbl(i)), 0);
1831
1832 -- If the Node already exists in the RULE_DOC, then we just need to update the Value
1833 IF DBMS_XMLDOM.isNull(l_dom_node) = FALSE THEN
1834 l_dom_node_value := DBMS_XMLDOM.getNodeValue(DBMS_XMLDOM.getFirstChild(l_dom_node));
1835
1836 -- Delete the Node if the new value is NULL
1837 IF l_new_param_values(i) IS NULL THEN
1838 l_dom_node := DBMS_XMLDOM.removeChild(DBMS_XMLDOM.getParentNode(l_dom_node), l_dom_node);
1839 ELSE
1840 IF NVL(l_new_param_values(i), '-') <> NVL(l_dom_node_value, '-') THEN
1841 DBMS_XMLDOM.setAttribute(DBMS_XMLDOM.makeElement(l_dom_node), 'inherited', 'N');
1842 END IF;
1843 DBMS_XMLDOM.setNodeValue(DBMS_XMLDOM.getFirstChild(l_dom_node), l_new_param_values(i));
1844 END IF;
1845 ELSIF l_new_param_values(i) IS NOT NULL AND is_param_valid_for_eligibility(l_rule.rule_rank, i) THEN
1846 -- Node doesnt exists. If a value is provided in the parameter and the parameter is valid
1847 -- for the current eligibility, we have to create a Node.
1848 l_dom_node := DBMS_XMLDOM.makeNode(DBMS_XMLDOM.createElement(l_rule_document, g_rule_param_names_tbl(i), g_rules_ns));
1849 l_dom_text_node := DBMS_XMLDOM.appendChild(l_dom_node, DBMS_XMLDOM.MAKENODE(DBMS_XMLDOM.createTextNode(l_rule_document, l_new_param_values(i))));
1850 DBMS_XMLDOM.setAttribute(DBMS_XMLDOM.makeElement(l_dom_node), 'inherited', 'N');
1851 l_parent_dom_node := get_param_grp_dom_node(l_rule_document, CASE WHEN i<33 THEN 'schedulerParameters' WHEN i<45 THEN 'routerConfig' ELSE 'costParameters' END, TRUE);
1852 l_dom_node := DBMS_XMLDOM.appendChild(l_parent_dom_node, l_dom_node);
1853 END IF;
1854 END LOOP;
1855
1856 l_object_version_num := p_object_version_number;
1857
1858 update_rule(
1859 p_api_version => 1.0
1860 , p_init_msg_list => fnd_api.g_true
1861 , p_commit => fnd_api.g_true
1862 , x_return_status => l_return_status
1863 , x_msg_data => l_msg_data
1864 , x_msg_count => l_msg_count
1865 , p_rule_id => p_rule_id
1866 , p_object_version_number => l_object_version_num
1867 , p_rule_name => p_rule_name
1868 , p_description => p_description
1869 , p_enabled_flag => p_enabled_flag
1870 , p_rule_doc => l_rule.rule_doc
1871 , p_version_msgs => NULL
1872 , x_new_rule_doc => l_new_rule_doc
1873 );
1874
1875 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1876 IF l_debug = 'Y' THEN
1877 debug('Process WebADI Action Errored out. Message Count = ' || l_msg_count || ' : Message Data = ' || l_msg_data, l_api_name, fnd_log.level_error);
1878 END IF;
1879
1880 IF l_msg_count > 0 THEN
1881 fnd_message.set_encoded(fnd_msg_pub.get(fnd_msg_pub.g_last));
1882 fnd_message.raise_error;
1883 END IF;
1884 END IF;
1885 END IF;
1886 END process_webadi_action;
1887
1888 PROCEDURE add_language IS
1889 BEGIN
1890 DELETE FROM csr_rules_tl t
1891 WHERE NOT EXISTS (SELECT NULL FROM csr_rules_b b WHERE b.rule_id = t.rule_id);
1892
1893 UPDATE csr_rules_tl csrt
1894 SET (csrt.rule_name, csrt.description) = (
1895 SELECT csrtl.rule_name, csrtl.description
1896 FROM csr_rules_tl csrtl
1897 WHERE csrtl.rule_id = csrt.rule_id
1898 AND csrtl.language = csrt.source_lang
1899 )
1900 WHERE (csrt.rule_id, csrt.language) IN (
1901 SELECT subt.rule_id, subt.language
1902 FROM csr_rules_tl subb, csr_rules_tl subt
1903 WHERE subb.rule_id = subt.rule_id
1904 AND subb.language = subt.source_lang
1905 AND (
1906 subb.rule_name <> subt.rule_name
1907 OR subb.description <> subt.description
1908 OR (subb.description IS NULL AND subt.description IS NOT NULL)
1909 OR (subb.description IS NOT NULL AND subt.description IS NULL)
1910 )
1911 );
1912
1913 INSERT INTO csr_rules_tl (
1914 rule_id
1915 , rule_name
1916 , description
1917 , created_by
1918 , creation_date
1919 , last_updated_by
1920 , last_update_date
1921 , last_update_login
1922 , language
1923 , source_lang
1924 )
1925 SELECT csrt.rule_id
1926 , csrt.rule_name
1927 , csrt.description
1928 , csrt.created_by
1929 , csrt.creation_date
1930 , csrt.last_updated_by
1931 , csrt.last_update_date
1932 , csrt.last_update_login
1933 , l.language_code
1934 , csrt.source_lang
1935 FROM csr_rules_tl csrt
1936 , fnd_languages l
1937 WHERE l.installed_flag IN ('I', 'B')
1938 AND csrt.language = userenv('LANG')
1939 AND NOT EXISTS (
1940 SELECT NULL
1941 FROM csr_rules_tl t
1942 WHERE t.rule_id = csrt.rule_id
1943 AND t.language = l.language_code
1944 );
1945
1946 DELETE FROM CSR_RULE_WINDOWS_TL t
1947 WHERE NOT EXISTS (SELECT NULL FROM CSR_RULE_WINDOWS_TL b WHERE b.window_id = t.window_id);
1948
1949 UPDATE CSR_RULE_WINDOWS_TL csrt
1950 SET (csrt.window_name, csrt.description) = (
1951 SELECT csrtl.window_name, csrtl.description
1952 FROM CSR_RULE_WINDOWS_TL csrtl
1953 WHERE csrtl.window_id = csrt.window_id
1954 AND csrtl.language = csrt.source_lang
1955 )
1956 WHERE (csrt.window_id, csrt.language) IN (
1957 SELECT subt.window_id, subt.language
1958 FROM CSR_RULE_WINDOWS_TL subb, CSR_RULE_WINDOWS_TL subt
1959 WHERE subb.window_id = subt.window_id
1960 AND subb.language = subt.source_lang
1961 AND (
1962 subb.window_name <> subt.window_name
1963 OR subb.description <> subt.description
1964 OR (subb.description IS NULL AND subt.description IS NOT NULL)
1965 OR (subb.description IS NOT NULL AND subt.description IS NULL)
1966 )
1967 );
1968
1969 INSERT INTO CSR_RULE_WINDOWS_TL (
1970 window_id
1971 , window_name
1972 , description
1973 , created_by
1974 , creation_date
1975 , last_updated_by
1976 , last_update_date
1977 , last_update_login
1978 , language
1979 , source_lang
1980 )
1981 SELECT csrt.window_id
1982 , csrt.window_name
1983 , csrt.description
1984 , csrt.created_by
1985 , csrt.creation_date
1986 , csrt.last_updated_by
1987 , csrt.last_update_date
1988 , csrt.last_update_login
1989 , l.language_code
1990 , csrt.source_lang
1991 FROM CSR_RULE_WINDOWS_TL csrt
1992 , fnd_languages l
1993 WHERE l.installed_flag IN ('I', 'B')
1994 AND csrt.language = userenv('LANG')
1995 AND NOT EXISTS (
1996 SELECT NULL
1997 FROM CSR_RULE_WINDOWS_TL t
1998 WHERE t.window_id = csrt.window_id
1999 AND t.language = l.language_code
2000 );
2001 END add_language;
2002
2003 BEGIN
2004 init_package;
2005 END csr_rules_pvt;