[Home] [Help]
65: Cursor csr_next_rule is
66: Select pqh_system_rule_s.nextval
67: from dual;
68: --
69: l_range_name pqh_attribute_ranges.range_name%type;
70: l_next_sequence number;
71: --
72: l_proc varchar2(72) := 'generate_rule_name';
73: --
144: -- Create a rule with the above generated rule name and attribute
145: -- ranges value null
146: -- File : pqrngapi.pkh/pkb
147: --
148: pqh_attribute_ranges_api.create_attribute_range(
149: p_validate => p_validate
150: ,p_attribute_range_id => l_attribute_range_id
151: ,p_approver_flag => NULL
152: ,p_enable_flag => p_enable_flag
196: )
197: is
198: Cursor csr_get_attribute_range(p_routing_category_id number) is
199: Select *
200: from pqh_attribute_ranges
201: where routing_category_id = p_routing_category_id;
202: --
203: l_rule_name varchar2(200);
204: l_attribute_range_id number;
202: --
203: l_rule_name varchar2(200);
204: l_attribute_range_id number;
205: --
206: l_rng_record pqh_attribute_ranges%ROWTYPE;
207: --
208: Cursor csr_old_def_hierarchy is
209: Select * from pqh_routing_categories
210: Where routing_category_id = p_old_routing_category_id;
228: open csr_get_attribute_range(p_routing_category_id => p_old_routing_category_id);
229: loop
230: fetch csr_get_attribute_range into l_rng_record;
231: exit when csr_get_attribute_range%notfound;
232: pqh_attribute_ranges_api.update_attribute_range
233: (
234: p_validate => p_validate
235: ,p_attribute_range_id => l_rng_record.attribute_range_id
236: ,p_approver_flag => l_rng_record.approver_flag
295: open csr_get_attribute_range(p_routing_category_id => p_routing_category_id);
296: loop
297: fetch csr_get_attribute_range into l_rng_record;
298: exit when csr_get_attribute_range%notfound;
299: pqh_attribute_ranges_api.update_attribute_range
300: (
301: p_validate => p_validate
302: ,p_attribute_range_id => l_rng_record.attribute_range_id
303: ,p_approver_flag => l_rng_record.approver_flag
373: --
374: -- Create a rule with the above generated rule name and attribute
375: -- ranges value null
376: --
377: pqh_attribute_ranges_api.create_attribute_range
378: (p_validate => p_validate
379: ,p_attribute_range_id => l_attribute_range_id
380: ,p_approver_flag => NULL
381: ,p_enable_flag => p_enable_flag
429: --
430: --
431: Cursor csr_chk_already_approver is
432: Select attribute_range_id,object_version_number
433: from pqh_attribute_ranges
434: Where routing_category_id = p_routing_category_id
435: And attribute_id is null
436: And nvl(routing_list_member_id,-99) = nvl(p_routing_list_member_id,-99)
437: And nvl(position_id,-99) = nvl(p_position_id,-99)
454: p_range_name := generate_rule_name;
455: --
456: -- Create a rule with the above generated rule name and attribute
457: -- ranges value null
458: pqh_attribute_ranges_api.create_attribute_range(
459: p_validate => p_validate
460: ,p_attribute_range_id => p_attribute_range_id
461: ,p_approver_flag => p_approver_flag
462: ,p_enable_flag => p_enable_flag
477: ,p_effective_date => p_effective_date);
478: --
479: Else
480: --
481: pqh_attribute_ranges_api.update_attribute_range(
482: p_validate => p_validate
483: ,p_attribute_range_id => p_attribute_range_id
484: ,p_approver_flag => 'Y'
485: ,p_enable_flag => 'Y'
525: hr_utility.set_location('Entering:'||l_proc, 5);
526: --
527: --
528: --
529: pqh_attribute_ranges_api.update_attribute_range
530: (
531: p_validate => p_validate
532: ,p_attribute_range_id => p_attribute_range_id
533: ,p_approver_flag => p_approver_flag
571: --
572: --
573: -- Delete the authorization rule for the default approver.
574: --
575: pqh_attribute_ranges_api.delete_attribute_range
576: (
577: p_validate => p_validate
578: ,p_attribute_range_id => p_attribute_range_id
579: ,p_object_version_number => p_object_version_number
940: Where transaction_category_id = p_transaction_category_id
941: and list_identifying_flag = 'Y'
942: and not exists
943: (Select null
944: From pqh_attribute_ranges rng,pqh_routing_categories rct
945: Where rct.transaction_category_id = p_transaction_category_id
946: and nvl(rct.default_flag,'N') <> 'Y'
947: and rct.routing_category_id = rng.routing_category_id
948: and routing_list_member_id IS NULL
953: -- Cursor to return all routing rule names
954: --
955: Cursor csr_rout_rule(p_routing_category_id in number) is
956: Select distinct rng.range_name,rng.enable_flag, nvl(rng.delete_flag,'N') delete_flag
957: From pqh_attribute_ranges rng
958: Where rng.routing_category_id = p_routing_category_id
959: and routing_list_member_id IS NULL
960: and position_id IS NULL
961: and assignment_id IS NULL
969: Where transaction_category_id = p_transaction_category_id
970: and member_identifying_flag = 'Y'
971: and not exists
972: (Select null
973: From pqh_attribute_ranges rng,pqh_routing_categories rct
974: Where rct.transaction_category_id = p_transaction_category_id
975: and nvl(rct.default_flag,'N') <> 'Y'
976: and rct.routing_category_id = rng.routing_category_id
977: and (routing_list_member_id IS NOT NULL or
988: rng.range_name,
989: rng.approver_flag,
990: rng.enable_flag,
991: nvl(rng.delete_flag,'N') delete_flag
992: From pqh_attribute_ranges rng
993: Where rng.routing_category_id = p_routing_category_id
994: and (routing_list_member_id IS NOT NULL or
995: position_id IS NOT NULL or
996: assignment_id IS NOT NULL)
995: position_id IS NOT NULL or
996: assignment_id IS NOT NULL)
997: and attribute_id IS NOT NULL;
998: --
999: l_attribute_range_id pqh_attribute_ranges.attribute_range_id%type;
1000: l_ovn pqh_attribute_ranges.object_version_number%type;
1001: l_routing_list_member_id pqh_attribute_ranges.routing_list_member_id%type;
1002: l_position_id pqh_attribute_ranges.position_id%type;
1003: l_assignment_id pqh_attribute_ranges.assignment_id%type;
996: assignment_id IS NOT NULL)
997: and attribute_id IS NOT NULL;
998: --
999: l_attribute_range_id pqh_attribute_ranges.attribute_range_id%type;
1000: l_ovn pqh_attribute_ranges.object_version_number%type;
1001: l_routing_list_member_id pqh_attribute_ranges.routing_list_member_id%type;
1002: l_position_id pqh_attribute_ranges.position_id%type;
1003: l_assignment_id pqh_attribute_ranges.assignment_id%type;
1004: l_routing_category_id pqh_attribute_ranges.routing_category_id%type;
997: and attribute_id IS NOT NULL;
998: --
999: l_attribute_range_id pqh_attribute_ranges.attribute_range_id%type;
1000: l_ovn pqh_attribute_ranges.object_version_number%type;
1001: l_routing_list_member_id pqh_attribute_ranges.routing_list_member_id%type;
1002: l_position_id pqh_attribute_ranges.position_id%type;
1003: l_assignment_id pqh_attribute_ranges.assignment_id%type;
1004: l_routing_category_id pqh_attribute_ranges.routing_category_id%type;
1005: --
998: --
999: l_attribute_range_id pqh_attribute_ranges.attribute_range_id%type;
1000: l_ovn pqh_attribute_ranges.object_version_number%type;
1001: l_routing_list_member_id pqh_attribute_ranges.routing_list_member_id%type;
1002: l_position_id pqh_attribute_ranges.position_id%type;
1003: l_assignment_id pqh_attribute_ranges.assignment_id%type;
1004: l_routing_category_id pqh_attribute_ranges.routing_category_id%type;
1005: --
1006: --
999: l_attribute_range_id pqh_attribute_ranges.attribute_range_id%type;
1000: l_ovn pqh_attribute_ranges.object_version_number%type;
1001: l_routing_list_member_id pqh_attribute_ranges.routing_list_member_id%type;
1002: l_position_id pqh_attribute_ranges.position_id%type;
1003: l_assignment_id pqh_attribute_ranges.assignment_id%type;
1004: l_routing_category_id pqh_attribute_ranges.routing_category_id%type;
1005: --
1006: --
1007: l_proc varchar2(72) := 'Add_criteria_to_rules';
1000: l_ovn pqh_attribute_ranges.object_version_number%type;
1001: l_routing_list_member_id pqh_attribute_ranges.routing_list_member_id%type;
1002: l_position_id pqh_attribute_ranges.position_id%type;
1003: l_assignment_id pqh_attribute_ranges.assignment_id%type;
1004: l_routing_category_id pqh_attribute_ranges.routing_category_id%type;
1005: --
1006: --
1007: l_proc varchar2(72) := 'Add_criteria_to_rules';
1008: --
1018: For rct_rec in csr_adv_rct loop
1019: --
1020: For rule_rec in csr_rout_rule(rct_rec.routing_category_id) loop
1021: --
1022: pqh_attribute_ranges_api.create_ATTRIBUTE_RANGE
1023: (
1024: p_validate => false
1025: ,p_attribute_range_id => l_attribute_range_id
1026: ,p_enable_flag => rule_rec.enable_flag
1059: l_position_id := NULL;
1060: l_assignment_id := rule_rec.member_id;
1061: End if;
1062: --
1063: pqh_attribute_ranges_api.create_ATTRIBUTE_RANGE
1064: (
1065: p_validate => false
1066: ,p_attribute_range_id => l_attribute_range_id
1067: ,p_enable_flag => rule_rec.enable_flag
1118: --
1119: /**
1120: Cursor csr_old (p_routing_category_id in number) is
1121: Select rng.attribute_range_id,rng.object_version_number
1122: From pqh_attribute_ranges rng
1123: Where rng.routing_category_id = p_routing_category_id
1124: and routing_list_member_id IS NULL
1125: and position_id IS NULL
1126: and assignment_id IS NULL
1138: WHERE TRANSACTION_CATEGORY_ID = p_transaction_category_id AND LIST_IDENTIFYING_FLAG = 'Y';
1139: --
1140: Cursor csr_old(p_routing_category_id in number) is
1141: SELECT RNG.ATTRIBUTE_RANGE_ID,RNG.OBJECT_VERSION_NUMBER,RNG.ATTRIBUTE_ID
1142: FROM PQH_ATTRIBUTE_RANGES RNG
1143: WHERE RNG.ROUTING_CATEGORY_ID = p_routing_category_id
1144: and routing_list_member_id IS NULL
1145: and position_id IS NULL
1146: and assignment_id IS NULL
1183: End loop;
1184: --
1185: If not l_found then
1186: hr_utility.set_location('calling delete_ATTRIBUTE_RANGE', 5);
1187: pqh_ATTRIBUTE_RANGES_api.delete_ATTRIBUTE_RANGE
1188: (p_validate => false
1189: ,p_attribute_range_id => old_rec.attribute_range_id
1190: ,p_object_version_number => old_rec.object_version_number
1191: ,p_effective_date => sysdate);
1236: --
1237: /**
1238: Cursor csr_old (p_routing_category_id in number) is
1239: Select rng.attribute_range_id,rng.object_version_number
1240: From pqh_attribute_ranges rng
1241: Where rng.routing_category_id = p_routing_category_id
1242: and (routing_list_member_id IS NOT NULL or
1243: position_id IS NOT NULL or
1244: assignment_id IS NOT NULL)
1255: WHERE TRANSACTION_CATEGORY_ID = p_transaction_category_id AND MEMBER_IDENTIFYING_FLAG = 'Y';
1256: --
1257: Cursor csr_auth(p_routing_category_id in number) is
1258: SELECT RNG.ATTRIBUTE_RANGE_ID,RNG.OBJECT_VERSION_NUMBER,RNG.ATTRIBUTE_ID
1259: FROM PQH_ATTRIBUTE_RANGES RNG
1260: WHERE RNG.ROUTING_CATEGORY_ID = p_routing_category_id
1261: AND (ROUTING_LIST_MEMBER_ID IS NOT NULL OR POSITION_ID IS NOT NULL OR ASSIGNMENT_ID IS NOT NULL)
1262: AND ATTRIBUTE_ID IS NOT NULL ;
1263: --
1293: End if;
1294: End loop;
1295: --
1296: If not l_found then
1297: pqh_ATTRIBUTE_RANGES_api.delete_ATTRIBUTE_RANGE
1298: (p_validate => false
1299: ,p_attribute_range_id => old_rec.attribute_range_id
1300: ,p_object_version_number => old_rec.object_version_number
1301: ,p_effective_date => sysdate);
1385: -- Only non-default rules are taken into consideration.
1386: --
1387: Cursor csr_rules_exist is
1388: Select null
1389: From pqh_attribute_ranges
1390: Where routing_category_id = p_routing_category_id
1391: and attribute_range_id IS NOT NULL;
1392: --
1393: l_dummy varchar2(1);
1473: p_all_attribute_range_id out nocopy varchar2) is
1474: --
1475: Cursor csr_rout_rule is
1476: Select attribute_range_id
1477: From pqh_attribute_ranges
1478: Where routing_category_id = p_routing_category_id
1479: and range_name = p_range_name
1480: and routing_list_member_id is NULL
1481: and position_id IS NULL
1482: and assignment_id IS NULL;
1483: --
1484: Cursor csr_auth_rule is
1485: Select attribute_range_id
1486: From pqh_attribute_ranges
1487: Where routing_category_id = p_routing_category_id
1488: and range_name = p_range_name
1489: and (routing_list_member_id is NOT NULL or
1490: position_id IS NOT NULL or
1554: Where transaction_category_id = p_transaction_category_id
1555: and list_identifying_flag = 'Y';
1556: --
1557: --
1558: l_attribute_range_id pqh_attribute_ranges.attribute_range_id%type;
1559: l_ovn pqh_attribute_ranges.object_version_number%type;
1560: --
1561: l_proc varchar2(72) := 'create_routing_rule';
1562: --
1555: and list_identifying_flag = 'Y';
1556: --
1557: --
1558: l_attribute_range_id pqh_attribute_ranges.attribute_range_id%type;
1559: l_ovn pqh_attribute_ranges.object_version_number%type;
1560: --
1561: l_proc varchar2(72) := 'create_routing_rule';
1562: --
1563: Begin
1571: p_all_attribute_range_id := NULL;
1572: --
1573: For attr_rec in csr_rout_attr loop
1574: --
1575: pqh_attribute_ranges_api.create_ATTRIBUTE_RANGE
1576: (
1577: p_validate => false
1578: ,p_attribute_range_id => l_attribute_range_id
1579: ,p_enable_flag => p_enable_flag
1620: csr_update_rule cur_type;
1621: sql_stmt varchar2(2000);
1622: --
1623: l_all_attribute_range_id varchar2(2000);
1624: att_range_rec pqh_attribute_ranges%ROWTYPE;
1625: --
1626: l_proc varchar2(72) := 'update_rule';
1627: --
1628: Begin
1635: l_all_attribute_range_id := '-999';
1636: End if;
1637: --
1638: --
1639: sql_stmt := 'Select * from pqh_attribute_ranges where attribute_range_id in ('
1640: || l_all_attribute_range_id
1641: ||') for update nowait';
1642: --
1643: -- We have the sql_stmt that we can execute.
1652: If csr_update_rule%NOTFOUND then
1653: Exit;
1654: End if;
1655: --
1656: pqh_attribute_ranges_api.update_ATTRIBUTE_RANGE
1657: (
1658: p_validate => false
1659: ,p_attribute_range_id => att_range_rec.attribute_range_id
1660: ,p_approver_flag => p_approver_flag
1726: type cur_type IS REF CURSOR;
1727: csr_delete_rule cur_type;
1728: sql_stmt varchar2(2000);
1729: --
1730: l_id pqh_attribute_ranges.attribute_range_id%TYPE;
1731: l_ovn pqh_attribute_ranges.object_version_number%TYPE;
1732: --
1733: l_all_attribute_range_id varchar2(2000);
1734: --
1727: csr_delete_rule cur_type;
1728: sql_stmt varchar2(2000);
1729: --
1730: l_id pqh_attribute_ranges.attribute_range_id%TYPE;
1731: l_ovn pqh_attribute_ranges.object_version_number%TYPE;
1732: --
1733: l_all_attribute_range_id varchar2(2000);
1734: --
1735: l_proc varchar2(72) := 'delete_rule';
1743: if l_all_attribute_range_id IS NULL then
1744: l_all_attribute_range_id := '-999';
1745: End if;
1746: --
1747: sql_stmt := 'Select attribute_range_id,object_version_number from pqh_attribute_ranges where routing_category_id = :routing_category_id and attribute_range_id in ('
1748: || l_all_attribute_range_id
1749: ||') for update nowait';
1750: --
1751: -- We have the sql_stmt that we can execute.
1760: If csr_delete_rule%NOTFOUND then
1761: Exit;
1762: End if;
1763: --
1764: pqh_attribute_ranges_api.delete_ATTRIBUTE_RANGE
1765: (
1766: p_validate => false
1767: ,p_attribute_range_id => l_id
1768: ,p_object_version_number => l_ovn
1838: and member_identifying_flag = 'Y';
1839: --
1840: Cursor csr_chk_already_approver is
1841: Select attribute_range_id,object_version_number
1842: from pqh_attribute_ranges
1843: Where routing_category_id = p_routing_category_id
1844: And nvl(routing_list_member_id,-99) = nvl(p_routing_list_member_id,-99)
1845: And nvl(position_id,-99) = nvl(p_position_id,-99)
1846: And nvl(p_assignment_id,-99) = nvl(p_assignment_id,-99)
1846: And nvl(p_assignment_id,-99) = nvl(p_assignment_id,-99)
1847: And attribute_id is not null
1848: And enable_flag = 'N';
1849: --
1850: l_gen_sys_rule_name pqh_attribute_ranges.range_name%type;
1851: l_attribute_range_id pqh_attribute_ranges.attribute_range_id%type;
1852: l_ovn pqh_attribute_ranges.object_version_number%type;
1853: l_create_appr boolean := true;
1854: --
1847: And attribute_id is not null
1848: And enable_flag = 'N';
1849: --
1850: l_gen_sys_rule_name pqh_attribute_ranges.range_name%type;
1851: l_attribute_range_id pqh_attribute_ranges.attribute_range_id%type;
1852: l_ovn pqh_attribute_ranges.object_version_number%type;
1853: l_create_appr boolean := true;
1854: --
1855: l_proc varchar2(72) := 'create_approver';
1848: And enable_flag = 'N';
1849: --
1850: l_gen_sys_rule_name pqh_attribute_ranges.range_name%type;
1851: l_attribute_range_id pqh_attribute_ranges.attribute_range_id%type;
1852: l_ovn pqh_attribute_ranges.object_version_number%type;
1853: l_create_appr boolean := true;
1854: --
1855: l_proc varchar2(72) := 'create_approver';
1856: --
1861: For exist_appr_rec in csr_chk_already_approver loop
1862: --
1863: l_create_appr := false;
1864: --
1865: pqh_attribute_ranges_api.update_attribute_range(
1866: p_validate => false
1867: ,p_attribute_range_id => exist_appr_rec.attribute_range_id
1868: ,p_approver_flag => 'Y'
1869: ,p_enable_flag => 'Y'
1884: -- authorization attributes with attribute ranges value null
1885: --
1886: For attr_rec in csr_auth_attr loop
1887: --
1888: pqh_attribute_ranges_api.create_ATTRIBUTE_RANGE
1889: (
1890: p_validate => false
1891: ,p_attribute_range_id => l_attribute_range_id
1892: ,p_routing_category_id => p_routing_category_id
1938: type cur_type IS REF CURSOR;
1939: csr_update_approver cur_type;
1940: sql_stmt varchar2(2000);
1941: --
1942: l_id pqh_attribute_ranges.attribute_range_id%TYPE;
1943: l_ovn pqh_attribute_ranges.object_version_number%TYPE;
1944: --
1945: l_dummy_id number(10);
1946: l_proc varchar2(72) := 'update_approver';
1939: csr_update_approver cur_type;
1940: sql_stmt varchar2(2000);
1941: --
1942: l_id pqh_attribute_ranges.attribute_range_id%TYPE;
1943: l_ovn pqh_attribute_ranges.object_version_number%TYPE;
1944: --
1945: l_dummy_id number(10);
1946: l_proc varchar2(72) := 'update_approver';
1947: --
1953: -- For the input a routing category , and authorizer
1954: -- Delete all non-default rules for the authorizer under the
1955: -- routing category
1956: --
1957: sql_stmt := 'Select attribute_range_id,object_version_number from pqh_attribute_ranges where routing_category_id = :routing_category_id and attribute_id is not null and ';
1958: --
1959: If p_routing_style = 'R' then
1960: --
1961: l_dummy_id := p_routing_list_member_id;
1985: If csr_update_approver%notfound then
1986: exit;
1987: End if;
1988: --
1989: pqh_attribute_ranges_api.update_ATTRIBUTE_RANGE
1990: (
1991: p_validate => false
1992: ,p_attribute_range_id => l_id
1993: ,p_approver_flag => p_approver_flag
2029: type cur_type IS REF CURSOR;
2030: csr_delete_approver cur_type;
2031: sql_stmt varchar2(2000);
2032: --
2033: l_id pqh_attribute_ranges.attribute_range_id%TYPE;
2034: l_ovn pqh_attribute_ranges.object_version_number%TYPE;
2035: --
2036: l_dummy_id number(10);
2037: l_proc varchar2(72) := 'delete_approver';
2030: csr_delete_approver cur_type;
2031: sql_stmt varchar2(2000);
2032: --
2033: l_id pqh_attribute_ranges.attribute_range_id%TYPE;
2034: l_ovn pqh_attribute_ranges.object_version_number%TYPE;
2035: --
2036: l_dummy_id number(10);
2037: l_proc varchar2(72) := 'delete_approver';
2038: --
2044: -- For the input a routing category , and authorizer
2045: -- Delete all non-default rules for the authorizer under the
2046: -- routing category
2047: --
2048: sql_stmt := 'Select attribute_range_id,object_version_number from pqh_attribute_ranges where routing_category_id = :routing_category_id and attribute_id is not null and ';
2049: --
2050: If p_routing_style = 'R' then
2051: --
2052: l_dummy_id := p_routing_list_member_id;
2075: If csr_delete_approver%notfound then
2076: exit;
2077: End if;
2078: --
2079: pqh_attribute_ranges_api.delete_ATTRIBUTE_RANGE
2080: (
2081: p_validate => false
2082: ,p_attribute_range_id => l_id
2083: ,p_object_version_number => l_ovn
2103: type cur_type IS REF CURSOR;
2104: csr_delete_approver cur_type;
2105: sql_stmt varchar2(2000);
2106: --
2107: l_id pqh_attribute_ranges.attribute_range_id%TYPE;
2108: l_ovn pqh_attribute_ranges.object_version_number%TYPE;
2109: l_appr_flag pqh_attribute_ranges.approver_flag%type;
2110: --
2111: l_dummy_id number(10);
2104: csr_delete_approver cur_type;
2105: sql_stmt varchar2(2000);
2106: --
2107: l_id pqh_attribute_ranges.attribute_range_id%TYPE;
2108: l_ovn pqh_attribute_ranges.object_version_number%TYPE;
2109: l_appr_flag pqh_attribute_ranges.approver_flag%type;
2110: --
2111: l_dummy_id number(10);
2112: l_proc varchar2(72) := 'update_approver_flag';
2105: sql_stmt varchar2(2000);
2106: --
2107: l_id pqh_attribute_ranges.attribute_range_id%TYPE;
2108: l_ovn pqh_attribute_ranges.object_version_number%TYPE;
2109: l_appr_flag pqh_attribute_ranges.approver_flag%type;
2110: --
2111: l_dummy_id number(10);
2112: l_proc varchar2(72) := 'update_approver_flag';
2113: --
2119: -- For the input a routing category , and authorizer
2120: -- Delete all non-default rules for the authorizer under the
2121: -- routing category
2122: --
2123: sql_stmt := 'Select attribute_range_id,object_version_number,approver_flag from pqh_attribute_ranges where routing_category_id = :routing_category_id and attribute_id is not null and ';
2124: --
2125: --
2126: If p_routing_style = 'R' then
2127: --
2152: exit;
2153: End if;
2154: --
2155: --
2156: pqh_attribute_ranges_api.update_ATTRIBUTE_RANGE
2157: (p_validate => false
2158: ,p_attribute_range_id => l_id
2159: ,p_approver_flag => p_approver_flag
2160: ,p_object_version_number => l_ovn
2203: and member_identifying_flag = 'Y';
2204: --
2205: Cursor csr_sys_rule is
2206: Select attribute_range_id,object_version_number
2207: From pqh_attribute_ranges
2208: Where routing_category_id = p_routing_category_id
2209: and range_name like 'PQH_$$SYS$$%'
2210: and attribute_id is NOT NULL
2211: and nvl(routing_list_member_id,-99) = nvl(p_routing_list_member_id,-99)
2212: and nvl(position_id,-99) = nvl(p_position_id,-99)
2213: and nvl(assignment_id ,-99) = nvl(p_assignment_id,-99)
2214: For update nowait;
2215: --
2216: l_attribute_range_id pqh_attribute_ranges.attribute_range_id%type;
2217: l_ovn pqh_attribute_ranges.object_version_number%type;
2218: l_proc varchar2(72) := 'create_authorization_rule';
2219: --
2220: --
2213: and nvl(assignment_id ,-99) = nvl(p_assignment_id,-99)
2214: For update nowait;
2215: --
2216: l_attribute_range_id pqh_attribute_ranges.attribute_range_id%type;
2217: l_ovn pqh_attribute_ranges.object_version_number%type;
2218: l_proc varchar2(72) := 'create_authorization_rule';
2219: --
2220: --
2221: Begin
2229: p_all_attribute_range_id := NULL;
2230: --
2231: For attr_rec in csr_auth_attr loop
2232: --
2233: pqh_attribute_ranges_api.create_ATTRIBUTE_RANGE
2234: (
2235: p_validate => false
2236: ,p_attribute_range_id => l_attribute_range_id
2237: ,p_routing_category_id => p_routing_category_id
2259: -- information , we can delete those rules.
2260: --
2261: For sys_rec in csr_sys_rule loop
2262: --
2263: pqh_attribute_ranges_api.delete_ATTRIBUTE_RANGE
2264: (
2265: p_validate => false
2266: ,p_attribute_range_id => sys_rec.attribute_range_id
2267: ,p_object_version_number => sys_rec.object_version_number
2357:
2358: --- Cursor to copy default approver detail
2359: CURSOR csr_default_approver(p_routing_category_id NUMBER) IS
2360: SELECT *
2361: FROM pqh_attribute_ranges
2362: WHERE ROUTING_CATEGORY_ID = p_routing_category_id
2363: AND NVL(ENABLE_FLAG, 'N')= 'Y'
2364: AND NVL(APPROVER_FLAG,'N')= 'Y';
2365: --
2379: rct_rec pqh_routing_categories%ROWTYPE;
2380: rct_id pqh_routing_categories.transaction_category_id%type;
2381: rct_ovn pqh_routing_categories.object_version_number%type;
2382: --
2383: rng_id pqh_attribute_ranges.attribute_range_id%type;
2384: rng_ovn pqh_attribute_ranges.object_version_number%type;
2385: --
2386: l_proc varchar2(72) := 'create_local_setup';
2387: --
2380: rct_id pqh_routing_categories.transaction_category_id%type;
2381: rct_ovn pqh_routing_categories.object_version_number%type;
2382: --
2383: rng_id pqh_attribute_ranges.attribute_range_id%type;
2384: rng_ovn pqh_attribute_ranges.object_version_number%type;
2385: --
2386: l_proc varchar2(72) := 'create_local_setup';
2387: --
2388: BEGIN
2492: --
2493: for rng_rec in csr_default_approver(p_routing_category_id => rct_rec.routing_category_id)
2494: loop
2495:
2496: Select pqh_attribute_ranges_s.nextval into rng_id from dual;
2497:
2498: insert into pqh_attribute_ranges(
2499: attribute_range_id,
2500: approver_flag,
2494: loop
2495:
2496: Select pqh_attribute_ranges_s.nextval into rng_id from dual;
2497:
2498: insert into pqh_attribute_ranges(
2499: attribute_range_id,
2500: approver_flag,
2501: enable_flag,
2502: assignment_id,
2575: RETURN BOOLEAN is
2576: type cur_type IS REF CURSOR;
2577: range_name_cur cur_type;
2578: sql_stmt varchar2(1000);
2579: exist_range_name pqh_attribute_ranges.range_name%type;
2580: --
2581: l_proc varchar2(72) := 'chk_range_name_unique';
2582: --
2583: Begin
2584: --
2585: hr_utility.set_location('Entering:'||l_proc, 5);
2586: --
2587:
2588: sql_stmt := 'Select distinct range_name from pqh_attribute_ranges where routing_category_id = :r AND attribute_id IS NOT NULL AND attribute_range_id not in ( ' || p_attribute_id_list || ') ';
2589:
2590: if p_primary_flag = 'Y' then
2591: sql_stmt := sql_stmt ||' AND routing_list_member_id is NULL AND position_id is NULL AND assignment_id is NULL';
2592: else
2786: and nvl(default_flag,'N') = 'Y';
2787: --
2788: Cursor csr_def_approvers (p_routing_category_id in number) is
2789: Select null
2790: from pqh_attribute_ranges_v3
2791: Where routing_category_id = p_routing_category_id
2792: and nvl(approver_flag,'N') = 'Y'
2793: and nvl(enable_flag,'Y') = 'Y';
2794: --
2906: and rct.enable_flag = 'Y'
2907: and rct.routing_list_id IS NOT NULL;
2908: --
2909: Cursor csr_rules(p_routing_category_id in number) is
2910: Select 'x' from pqh_attribute_ranges rng
2911: Where rng.routing_category_id = p_routing_category_id
2912: and rng.enable_flag = 'Y'
2913: and nvl(delete_flag,'N') <> 'Y'
2914: and rng.routing_list_member_id IS NULL
3009: RETURN TRUE;
3010: --
3011: /**
3012: sql_stmt := 'Select rct.routing_category_id, count(rng.range_name)'
3013: || ' from pqh_routing_categories rct,pqh_attribute_ranges rng'
3014: || ' Where rct.transaction_category_id = :p_transaction_category_id'
3015: || ' and rct.enable_flag = :p_enable_flag'
3016: || ' and nvl(rct.default_flag,:null1) <> :p_default_flag'
3017: || ' and nvl(rct.delete_flag,:null2) <> :p_delete_flag';
3110: --
3111: l_error_code number(10);
3112: --
3113: --
3114: l_overlap_range_name pqh_attribute_ranges.range_name%type;
3115: l_error_range_name pqh_attribute_ranges.range_name%type;
3116: l_error_routing_category varchar2(200);
3117: l_proc varchar2(72) := 'chk_overlap_on_freeze_cat';
3118: --
3111: l_error_code number(10);
3112: --
3113: --
3114: l_overlap_range_name pqh_attribute_ranges.range_name%type;
3115: l_error_range_name pqh_attribute_ranges.range_name%type;
3116: l_error_routing_category varchar2(200);
3117: l_proc varchar2(72) := 'chk_overlap_on_freeze_cat';
3118: --
3119: /** Perf changes
3166: Close csr_chk_rule_overlap;
3167: exit;
3168: End if;
3169: --
3170: l_error_code := pqh_attribute_ranges_pkg.chk_enable_routing_category
3171: (p_routing_category_id => l_routing_category_id,
3172: p_transaction_category_id => p_transaction_category_id,
3173: p_overlap_range_name => l_overlap_range_name,
3174: p_error_routing_category => l_error_routing_category,
3203: RETURN BOOLEAN is
3204: --
3205: l_error_routing_category varchar2(200);
3206: l_member_name varchar2(300);
3207: l_overlap_range_name pqh_attribute_ranges.range_name%type;
3208: l_error_range_name pqh_attribute_ranges.range_name%type;
3209: --
3210: l_prev_range_name pqh_attribute_ranges.range_name%type;
3211: l_prev_routing_category_id pqh_routing_categories.routing_category_id%type;
3204: --
3205: l_error_routing_category varchar2(200);
3206: l_member_name varchar2(300);
3207: l_overlap_range_name pqh_attribute_ranges.range_name%type;
3208: l_error_range_name pqh_attribute_ranges.range_name%type;
3209: --
3210: l_prev_range_name pqh_attribute_ranges.range_name%type;
3211: l_prev_routing_category_id pqh_routing_categories.routing_category_id%type;
3212: l_prev_member_id number(30);
3206: l_member_name varchar2(300);
3207: l_overlap_range_name pqh_attribute_ranges.range_name%type;
3208: l_error_range_name pqh_attribute_ranges.range_name%type;
3209: --
3210: l_prev_range_name pqh_attribute_ranges.range_name%type;
3211: l_prev_routing_category_id pqh_routing_categories.routing_category_id%type;
3212: l_prev_member_id number(30);
3213: --
3214: cnt number(10);
3215: l_attribute_range_id_list varchar2(2000);
3216: l_no_mem_identifiers number(10);
3217: --
3218: l_routing_category_id pqh_routing_categories.routing_category_id%type;
3219: l_range_name pqh_attribute_ranges.range_name%type;
3220: l_member_id number(30);
3221: l_attribute_range_id pqh_attribute_ranges.attribute_range_id %type;
3222: l_attribute_id pqh_attribute_ranges.attribute_id%type;
3223: l_column_type pqh_attributes.column_type%type;
3217: --
3218: l_routing_category_id pqh_routing_categories.routing_category_id%type;
3219: l_range_name pqh_attribute_ranges.range_name%type;
3220: l_member_id number(30);
3221: l_attribute_range_id pqh_attribute_ranges.attribute_range_id %type;
3222: l_attribute_id pqh_attribute_ranges.attribute_id%type;
3223: l_column_type pqh_attributes.column_type%type;
3224: l_from_char pqh_attribute_ranges.from_char%type;
3225: l_to_char pqh_attribute_ranges.to_char%type;
3218: l_routing_category_id pqh_routing_categories.routing_category_id%type;
3219: l_range_name pqh_attribute_ranges.range_name%type;
3220: l_member_id number(30);
3221: l_attribute_range_id pqh_attribute_ranges.attribute_range_id %type;
3222: l_attribute_id pqh_attribute_ranges.attribute_id%type;
3223: l_column_type pqh_attributes.column_type%type;
3224: l_from_char pqh_attribute_ranges.from_char%type;
3225: l_to_char pqh_attribute_ranges.to_char%type;
3226: l_from_date pqh_attribute_ranges.from_date%type;
3220: l_member_id number(30);
3221: l_attribute_range_id pqh_attribute_ranges.attribute_range_id %type;
3222: l_attribute_id pqh_attribute_ranges.attribute_id%type;
3223: l_column_type pqh_attributes.column_type%type;
3224: l_from_char pqh_attribute_ranges.from_char%type;
3225: l_to_char pqh_attribute_ranges.to_char%type;
3226: l_from_date pqh_attribute_ranges.from_date%type;
3227: l_to_date pqh_attribute_ranges.to_date%type;
3228: l_from_number pqh_attribute_ranges.from_number%type;
3221: l_attribute_range_id pqh_attribute_ranges.attribute_range_id %type;
3222: l_attribute_id pqh_attribute_ranges.attribute_id%type;
3223: l_column_type pqh_attributes.column_type%type;
3224: l_from_char pqh_attribute_ranges.from_char%type;
3225: l_to_char pqh_attribute_ranges.to_char%type;
3226: l_from_date pqh_attribute_ranges.from_date%type;
3227: l_to_date pqh_attribute_ranges.to_date%type;
3228: l_from_number pqh_attribute_ranges.from_number%type;
3229: l_to_number pqh_attribute_ranges.to_number%type;
3222: l_attribute_id pqh_attribute_ranges.attribute_id%type;
3223: l_column_type pqh_attributes.column_type%type;
3224: l_from_char pqh_attribute_ranges.from_char%type;
3225: l_to_char pqh_attribute_ranges.to_char%type;
3226: l_from_date pqh_attribute_ranges.from_date%type;
3227: l_to_date pqh_attribute_ranges.to_date%type;
3228: l_from_number pqh_attribute_ranges.from_number%type;
3229: l_to_number pqh_attribute_ranges.to_number%type;
3230: --
3223: l_column_type pqh_attributes.column_type%type;
3224: l_from_char pqh_attribute_ranges.from_char%type;
3225: l_to_char pqh_attribute_ranges.to_char%type;
3226: l_from_date pqh_attribute_ranges.from_date%type;
3227: l_to_date pqh_attribute_ranges.to_date%type;
3228: l_from_number pqh_attribute_ranges.from_number%type;
3229: l_to_number pqh_attribute_ranges.to_number%type;
3230: --
3231: l_error_code number(10) := NULL;
3224: l_from_char pqh_attribute_ranges.from_char%type;
3225: l_to_char pqh_attribute_ranges.to_char%type;
3226: l_from_date pqh_attribute_ranges.from_date%type;
3227: l_to_date pqh_attribute_ranges.to_date%type;
3228: l_from_number pqh_attribute_ranges.from_number%type;
3229: l_to_number pqh_attribute_ranges.to_number%type;
3230: --
3231: l_error_code number(10) := NULL;
3232: --
3225: l_to_char pqh_attribute_ranges.to_char%type;
3226: l_from_date pqh_attribute_ranges.from_date%type;
3227: l_to_date pqh_attribute_ranges.to_date%type;
3228: l_from_number pqh_attribute_ranges.from_number%type;
3229: l_to_number pqh_attribute_ranges.to_number%type;
3230: --
3231: l_error_code number(10) := NULL;
3232: --
3233: type cur_type IS REF CURSOR;
3233: type cur_type IS REF CURSOR;
3234: csr_mem_overlap cur_type;
3235: sql_stmt varchar2(2000);
3236: --
3237: all_routing_rules pqh_attribute_ranges_pkg.rule_attr_tab;
3238: all_attributes_tab pqh_attribute_ranges_pkg.rule_attr_tab;
3239: --
3240: Cursor csr_mem_ident_cnt is
3241: Select count(*)
3234: csr_mem_overlap cur_type;
3235: sql_stmt varchar2(2000);
3236: --
3237: all_routing_rules pqh_attribute_ranges_pkg.rule_attr_tab;
3238: all_attributes_tab pqh_attribute_ranges_pkg.rule_attr_tab;
3239: --
3240: Cursor csr_mem_ident_cnt is
3241: Select count(*)
3242: from pqh_txn_category_attributes
3270: End if;
3271: --
3272: sql_stmt := sql_stmt ||' rng.attribute_range_id, rng.attribute_id, att.column_type, rng.from_char, rng.to_char, rng.from_number, rng.to_number, rng.from_date, rng.to_date ';
3273: --
3274: sql_stmt := sql_stmt ||' From pqh_routing_categories rct,pqh_attribute_ranges rng,pqh_attributes att ';
3275: --
3276: sql_stmt := sql_stmt ||' Where rct.transaction_category_id = :p_transaction_category_id and rct.enable_flag = :enable_flag and nvl(rct.default_flag,:null_value) <> :default_flag and nvl(rct.delete_flag,:null2) <> :delete_flag ';
3277: --
3278: -- If a routing category is passed, process only this routing category.
3360: -- overlaps with any other routing rules under that
3361: -- transaction category.
3362: --
3363: hr_utility.set_location('Calling chk_member_range_overlap:'||l_proc, 6);
3364: l_error_code := pqh_attribute_ranges_pkg.chk_member_range_overlap
3365: (tab1 => all_routing_rules ,
3366: tab2 => all_attributes_tab,
3367: p_transaction_category_id => p_transaction_category_id,
3368: p_routing_category_id => l_prev_routing_category_id,
3383: pqh_tct_bus.get_routing_category_name(
3384: p_routing_category_id => l_prev_routing_category_id,
3385: p_routing_category_name => l_error_routing_category);
3386: --
3387: pqh_attribute_ranges_pkg.get_member_name
3388: (p_member_id => l_prev_member_id,
3389: p_routing_type => p_routing_type,
3390: p_member_name => l_member_name);
3391: --
3442: -- transaction category.
3443: --
3444: hr_utility.set_location('Calling chk_routing_range_overlap:'||l_proc, 6);
3445: --
3446: l_error_code := pqh_attribute_ranges_pkg.chk_member_range_overlap
3447: (tab1 => all_routing_rules ,
3448: tab2 => all_attributes_tab,
3449: p_transaction_category_id => p_transaction_category_id,
3450: p_routing_category_id => l_prev_routing_category_id,
3468: pqh_tct_bus.get_routing_category_name(
3469: p_routing_category_id => l_prev_routing_category_id,
3470: p_routing_category_name => l_error_routing_category);
3471: --
3472: pqh_attribute_ranges_pkg.get_member_name
3473: (p_member_id => l_prev_member_id,
3474: p_routing_type => p_routing_type,
3475: p_member_name => l_member_name);
3476: --
3504: l_routing_type pqh_transaction_categories.member_cd%type;
3505: --
3506: l_error_routing_category varchar2(200);
3507: l_member_name varchar2(300);
3508: l_overlap_range_name pqh_attribute_ranges.range_name%type;
3509: l_error_range_name pqh_attribute_ranges.range_name%type;
3510: --
3511: --
3512: Cursor csr_routing_type is
3505: --
3506: l_error_routing_category varchar2(200);
3507: l_member_name varchar2(300);
3508: l_overlap_range_name pqh_attribute_ranges.range_name%type;
3509: l_error_range_name pqh_attribute_ranges.range_name%type;
3510: --
3511: --
3512: Cursor csr_routing_type is
3513: Select member_Cd
3661: -- The following cursor deletes the rules under the routing hierarchies
3662: -- that were selected for deletion for the passed transaction category
3663: -- and its current routing style.
3664: --
3665: sql_stmt1 := 'Delete From pqh_attribute_ranges rng Where';
3666: --
3667: sql_stmt1 := sql_stmt1 || ' rng.routing_category_id in ('
3668: || ' Select routing_category_id '
3669: || ' from pqh_routing_categories rct '
3710: --
3711: -- Finally we need to delete just the rules that were selected for deletion
3712: -- under this transaction category and routing style.
3713: --
3714: sql_stmt1 := 'Delete From pqh_attribute_ranges rng '
3715: ||' Where nvl(rng.delete_flag,:null1) = :delete_flag ';
3716: --
3717: sql_stmt1 := sql_stmt1 ||' and rng.routing_category_id in ('
3718: ||' Select routing_category_id '
3747: type cur_type IS REF CURSOR;
3748: csr_appr cur_type;
3749: sql_stmt1 varchar2(2000);
3750: --
3751: l_approver_flag pqh_attribute_ranges.approver_flag%type;
3752: --
3753: l_proc varchar2(72) := 'return_approver_status';
3754: --
3755: BEGIN
3759: --
3760: -- The following cursor selects the approver flag on all the rules for the
3761: -- approver under the passed routing category.
3762: --
3763: sql_stmt1 := 'Select approver_flag From pqh_attribute_ranges rng '
3764: || ' Where rng.routing_category_id = :routing_category_id';
3765: --
3766: If p_routing_style = 'R' then
3767: --