[Home] [Help]
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)
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 ';
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