[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: --
2499: -- Added the following code as a part of Zero Downtime Patching Project.
2500: -- Code Starts Here.
2500: -- Code Starts Here.
2501: --
2502:
2503: PER_RIC_PKG.chk_integrity (
2504: p_entity_name=>'PQH_ATTRIBUTE_RANGES',
2505: p_ref_entity_info=>
2506: PER_RIC_PKG.ref_entity_tbl(
2507: PER_RIC_PKG.ref_info_rec('PQH_ATTRIBUTES',PER_RIC_PKG.column_info_tbl(
2508: PER_RIC_PKG.col_info_rec('ATTRIBUTE_ID', NULL,rng_rec.attribute_id, NULL))),
2515: --
2516: -- Code Ends Here
2517: --
2518:
2519: insert into pqh_attribute_ranges(
2520: attribute_range_id,
2521: approver_flag,
2522: enable_flag,
2523: assignment_id,
2596: RETURN BOOLEAN is
2597: type cur_type IS REF CURSOR;
2598: range_name_cur cur_type;
2599: sql_stmt varchar2(1000);
2600: exist_range_name pqh_attribute_ranges.range_name%type;
2601: --
2602: l_proc varchar2(72) := 'chk_range_name_unique';
2603: --
2604: Begin
2605: --
2606: hr_utility.set_location('Entering:'||l_proc, 5);
2607: --
2608:
2609: 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 || ') ';
2610:
2611: if p_primary_flag = 'Y' then
2612: sql_stmt := sql_stmt ||' AND routing_list_member_id is NULL AND position_id is NULL AND assignment_id is NULL';
2613: else
2807: and nvl(default_flag,'N') = 'Y';
2808: --
2809: Cursor csr_def_approvers (p_routing_category_id in number) is
2810: Select null
2811: from pqh_attribute_ranges_v3
2812: Where routing_category_id = p_routing_category_id
2813: and nvl(approver_flag,'N') = 'Y'
2814: and nvl(enable_flag,'Y') = 'Y';
2815: --
2927: and rct.enable_flag = 'Y'
2928: and rct.routing_list_id IS NOT NULL;
2929: --
2930: Cursor csr_rules(p_routing_category_id in number) is
2931: Select 'x' from pqh_attribute_ranges rng
2932: Where rng.routing_category_id = p_routing_category_id
2933: and rng.enable_flag = 'Y'
2934: and nvl(delete_flag,'N') <> 'Y'
2935: and rng.routing_list_member_id IS NULL
3030: RETURN TRUE;
3031: --
3032: /**
3033: sql_stmt := 'Select rct.routing_category_id, count(rng.range_name)'
3034: || ' from pqh_routing_categories rct,pqh_attribute_ranges rng'
3035: || ' Where rct.transaction_category_id = :p_transaction_category_id'
3036: || ' and rct.enable_flag = :p_enable_flag'
3037: || ' and nvl(rct.default_flag,:null1) <> :p_default_flag'
3038: || ' and nvl(rct.delete_flag,:null2) <> :p_delete_flag';
3131: --
3132: l_error_code number(10);
3133: --
3134: --
3135: l_overlap_range_name pqh_attribute_ranges.range_name%type;
3136: l_error_range_name pqh_attribute_ranges.range_name%type;
3137: l_error_routing_category varchar2(200);
3138: l_proc varchar2(72) := 'chk_overlap_on_freeze_cat';
3139: --
3132: l_error_code number(10);
3133: --
3134: --
3135: l_overlap_range_name pqh_attribute_ranges.range_name%type;
3136: l_error_range_name pqh_attribute_ranges.range_name%type;
3137: l_error_routing_category varchar2(200);
3138: l_proc varchar2(72) := 'chk_overlap_on_freeze_cat';
3139: --
3140: /** Perf changes
3187: Close csr_chk_rule_overlap;
3188: exit;
3189: End if;
3190: --
3191: l_error_code := pqh_attribute_ranges_pkg.chk_enable_routing_category
3192: (p_routing_category_id => l_routing_category_id,
3193: p_transaction_category_id => p_transaction_category_id,
3194: p_overlap_range_name => l_overlap_range_name,
3195: p_error_routing_category => l_error_routing_category,
3224: RETURN BOOLEAN is
3225: --
3226: l_error_routing_category varchar2(200);
3227: l_member_name varchar2(300);
3228: l_overlap_range_name pqh_attribute_ranges.range_name%type;
3229: l_error_range_name pqh_attribute_ranges.range_name%type;
3230: --
3231: l_prev_range_name pqh_attribute_ranges.range_name%type;
3232: l_prev_routing_category_id pqh_routing_categories.routing_category_id%type;
3225: --
3226: l_error_routing_category varchar2(200);
3227: l_member_name varchar2(300);
3228: l_overlap_range_name pqh_attribute_ranges.range_name%type;
3229: l_error_range_name pqh_attribute_ranges.range_name%type;
3230: --
3231: l_prev_range_name pqh_attribute_ranges.range_name%type;
3232: l_prev_routing_category_id pqh_routing_categories.routing_category_id%type;
3233: l_prev_member_id number(30);
3227: l_member_name varchar2(300);
3228: l_overlap_range_name pqh_attribute_ranges.range_name%type;
3229: l_error_range_name pqh_attribute_ranges.range_name%type;
3230: --
3231: l_prev_range_name pqh_attribute_ranges.range_name%type;
3232: l_prev_routing_category_id pqh_routing_categories.routing_category_id%type;
3233: l_prev_member_id number(30);
3234: --
3235: cnt number(10);
3236: l_attribute_range_id_list varchar2(2000);
3237: l_no_mem_identifiers number(10);
3238: --
3239: l_routing_category_id pqh_routing_categories.routing_category_id%type;
3240: l_range_name pqh_attribute_ranges.range_name%type;
3241: l_member_id number(30);
3242: l_attribute_range_id pqh_attribute_ranges.attribute_range_id %type;
3243: l_attribute_id pqh_attribute_ranges.attribute_id%type;
3244: l_column_type pqh_attributes.column_type%type;
3238: --
3239: l_routing_category_id pqh_routing_categories.routing_category_id%type;
3240: l_range_name pqh_attribute_ranges.range_name%type;
3241: l_member_id number(30);
3242: l_attribute_range_id pqh_attribute_ranges.attribute_range_id %type;
3243: l_attribute_id pqh_attribute_ranges.attribute_id%type;
3244: l_column_type pqh_attributes.column_type%type;
3245: l_from_char pqh_attribute_ranges.from_char%type;
3246: l_to_char pqh_attribute_ranges.to_char%type;
3239: l_routing_category_id pqh_routing_categories.routing_category_id%type;
3240: l_range_name pqh_attribute_ranges.range_name%type;
3241: l_member_id number(30);
3242: l_attribute_range_id pqh_attribute_ranges.attribute_range_id %type;
3243: l_attribute_id pqh_attribute_ranges.attribute_id%type;
3244: l_column_type pqh_attributes.column_type%type;
3245: l_from_char pqh_attribute_ranges.from_char%type;
3246: l_to_char pqh_attribute_ranges.to_char%type;
3247: l_from_date pqh_attribute_ranges.from_date%type;
3241: l_member_id number(30);
3242: l_attribute_range_id pqh_attribute_ranges.attribute_range_id %type;
3243: l_attribute_id pqh_attribute_ranges.attribute_id%type;
3244: l_column_type pqh_attributes.column_type%type;
3245: l_from_char pqh_attribute_ranges.from_char%type;
3246: l_to_char pqh_attribute_ranges.to_char%type;
3247: l_from_date pqh_attribute_ranges.from_date%type;
3248: l_to_date pqh_attribute_ranges.to_date%type;
3249: l_from_number pqh_attribute_ranges.from_number%type;
3242: l_attribute_range_id pqh_attribute_ranges.attribute_range_id %type;
3243: l_attribute_id pqh_attribute_ranges.attribute_id%type;
3244: l_column_type pqh_attributes.column_type%type;
3245: l_from_char pqh_attribute_ranges.from_char%type;
3246: l_to_char pqh_attribute_ranges.to_char%type;
3247: l_from_date pqh_attribute_ranges.from_date%type;
3248: l_to_date pqh_attribute_ranges.to_date%type;
3249: l_from_number pqh_attribute_ranges.from_number%type;
3250: l_to_number pqh_attribute_ranges.to_number%type;
3243: l_attribute_id pqh_attribute_ranges.attribute_id%type;
3244: l_column_type pqh_attributes.column_type%type;
3245: l_from_char pqh_attribute_ranges.from_char%type;
3246: l_to_char pqh_attribute_ranges.to_char%type;
3247: l_from_date pqh_attribute_ranges.from_date%type;
3248: l_to_date pqh_attribute_ranges.to_date%type;
3249: l_from_number pqh_attribute_ranges.from_number%type;
3250: l_to_number pqh_attribute_ranges.to_number%type;
3251: --
3244: l_column_type pqh_attributes.column_type%type;
3245: l_from_char pqh_attribute_ranges.from_char%type;
3246: l_to_char pqh_attribute_ranges.to_char%type;
3247: l_from_date pqh_attribute_ranges.from_date%type;
3248: l_to_date pqh_attribute_ranges.to_date%type;
3249: l_from_number pqh_attribute_ranges.from_number%type;
3250: l_to_number pqh_attribute_ranges.to_number%type;
3251: --
3252: l_error_code number(10) := NULL;
3245: l_from_char pqh_attribute_ranges.from_char%type;
3246: l_to_char pqh_attribute_ranges.to_char%type;
3247: l_from_date pqh_attribute_ranges.from_date%type;
3248: l_to_date pqh_attribute_ranges.to_date%type;
3249: l_from_number pqh_attribute_ranges.from_number%type;
3250: l_to_number pqh_attribute_ranges.to_number%type;
3251: --
3252: l_error_code number(10) := NULL;
3253: --
3246: l_to_char pqh_attribute_ranges.to_char%type;
3247: l_from_date pqh_attribute_ranges.from_date%type;
3248: l_to_date pqh_attribute_ranges.to_date%type;
3249: l_from_number pqh_attribute_ranges.from_number%type;
3250: l_to_number pqh_attribute_ranges.to_number%type;
3251: --
3252: l_error_code number(10) := NULL;
3253: --
3254: type cur_type IS REF CURSOR;
3254: type cur_type IS REF CURSOR;
3255: csr_mem_overlap cur_type;
3256: sql_stmt varchar2(2000);
3257: --
3258: all_routing_rules pqh_attribute_ranges_pkg.rule_attr_tab;
3259: all_attributes_tab pqh_attribute_ranges_pkg.rule_attr_tab;
3260: --
3261: Cursor csr_mem_ident_cnt is
3262: Select count(*)
3255: csr_mem_overlap cur_type;
3256: sql_stmt varchar2(2000);
3257: --
3258: all_routing_rules pqh_attribute_ranges_pkg.rule_attr_tab;
3259: all_attributes_tab pqh_attribute_ranges_pkg.rule_attr_tab;
3260: --
3261: Cursor csr_mem_ident_cnt is
3262: Select count(*)
3263: from pqh_txn_category_attributes
3291: End if;
3292: --
3293: 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 ';
3294: --
3295: sql_stmt := sql_stmt ||' From pqh_routing_categories rct,pqh_attribute_ranges rng,pqh_attributes att ';
3296: --
3297: 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 ';
3298: --
3299: -- If a routing category is passed, process only this routing category.
3381: -- overlaps with any other routing rules under that
3382: -- transaction category.
3383: --
3384: hr_utility.set_location('Calling chk_member_range_overlap:'||l_proc, 6);
3385: l_error_code := pqh_attribute_ranges_pkg.chk_member_range_overlap
3386: (tab1 => all_routing_rules ,
3387: tab2 => all_attributes_tab,
3388: p_transaction_category_id => p_transaction_category_id,
3389: p_routing_category_id => l_prev_routing_category_id,
3404: pqh_tct_bus.get_routing_category_name(
3405: p_routing_category_id => l_prev_routing_category_id,
3406: p_routing_category_name => l_error_routing_category);
3407: --
3408: pqh_attribute_ranges_pkg.get_member_name
3409: (p_member_id => l_prev_member_id,
3410: p_routing_type => p_routing_type,
3411: p_member_name => l_member_name);
3412: --
3463: -- transaction category.
3464: --
3465: hr_utility.set_location('Calling chk_routing_range_overlap:'||l_proc, 6);
3466: --
3467: l_error_code := pqh_attribute_ranges_pkg.chk_member_range_overlap
3468: (tab1 => all_routing_rules ,
3469: tab2 => all_attributes_tab,
3470: p_transaction_category_id => p_transaction_category_id,
3471: p_routing_category_id => l_prev_routing_category_id,
3489: pqh_tct_bus.get_routing_category_name(
3490: p_routing_category_id => l_prev_routing_category_id,
3491: p_routing_category_name => l_error_routing_category);
3492: --
3493: pqh_attribute_ranges_pkg.get_member_name
3494: (p_member_id => l_prev_member_id,
3495: p_routing_type => p_routing_type,
3496: p_member_name => l_member_name);
3497: --
3525: l_routing_type pqh_transaction_categories.member_cd%type;
3526: --
3527: l_error_routing_category varchar2(200);
3528: l_member_name varchar2(300);
3529: l_overlap_range_name pqh_attribute_ranges.range_name%type;
3530: l_error_range_name pqh_attribute_ranges.range_name%type;
3531: --
3532: --
3533: Cursor csr_routing_type is
3526: --
3527: l_error_routing_category varchar2(200);
3528: l_member_name varchar2(300);
3529: l_overlap_range_name pqh_attribute_ranges.range_name%type;
3530: l_error_range_name pqh_attribute_ranges.range_name%type;
3531: --
3532: --
3533: Cursor csr_routing_type is
3534: Select member_Cd
3682: -- The following cursor deletes the rules under the routing hierarchies
3683: -- that were selected for deletion for the passed transaction category
3684: -- and its current routing style.
3685: --
3686: sql_stmt1 := 'Delete From pqh_attribute_ranges rng Where';
3687: --
3688: sql_stmt1 := sql_stmt1 || ' rng.routing_category_id in ('
3689: || ' Select routing_category_id '
3690: || ' from pqh_routing_categories rct '
3731: --
3732: -- Finally we need to delete just the rules that were selected for deletion
3733: -- under this transaction category and routing style.
3734: --
3735: sql_stmt1 := 'Delete From pqh_attribute_ranges rng '
3736: ||' Where nvl(rng.delete_flag,:null1) = :delete_flag ';
3737: --
3738: sql_stmt1 := sql_stmt1 ||' and rng.routing_category_id in ('
3739: ||' Select routing_category_id '
3768: type cur_type IS REF CURSOR;
3769: csr_appr cur_type;
3770: sql_stmt1 varchar2(2000);
3771: --
3772: l_approver_flag pqh_attribute_ranges.approver_flag%type;
3773: --
3774: l_proc varchar2(72) := 'return_approver_status';
3775: --
3776: BEGIN
3780: --
3781: -- The following cursor selects the approver flag on all the rules for the
3782: -- approver under the passed routing category.
3783: --
3784: sql_stmt1 := 'Select approver_flag From pqh_attribute_ranges rng '
3785: || ' Where rng.routing_category_id = :routing_category_id';
3786: --
3787: If p_routing_style = 'R' then
3788: --