[Home] [Help]
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;
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:
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
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
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)
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
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:
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
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)
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);
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:
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:
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:
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;
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 ';
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
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
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
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);
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;
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:
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');
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;
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);
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);
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;
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;
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;
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;
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)
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
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: );
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);
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);
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;
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
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
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;
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
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;
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
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
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
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
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
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
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
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;
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
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
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
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;
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) = (
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
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
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 (
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
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
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 (
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:
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) = (
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
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
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 (
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
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
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 (
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;
2001: END add_language;
2002:
2003: BEGIN
2004: init_package;
2005: END csr_rules_pvt;