[Home] [Help]
28: where transaction_category_id = p_transaction_category_id;
29: --
30: Begin
31: --
32: hr_utility.set_location('Entering:'||l_proc, 5);
33: --
34: Open csr_setup;
35: Fetch csr_setup into l_set_up_flag,l_freeze_status_cd;
36: Close csr_setup;
49: --
50: return FALSE;
51: --
52: --
53: hr_utility.set_location('Leaving:'||l_proc, 10);
54: --
55: exception when others then
56: p_setup_type := null;
57: raise;
72: l_proc varchar2(72) := 'generate_rule_name';
73: --
74: Begin
75: --
76: hr_utility.set_location('Entering:'||l_proc, 5);
77: --
78: --
79: l_range_name := 'PQH_$$SYS$$_';
80: --
86: --
87: Return l_range_name;
88: --
89: --
90: hr_utility.set_location('Leaving:'||l_proc, 10);
91: --
92: End;
93: --
94: -----------------------------------------------------------------------------
113: l_proc varchar2(72) := 'create_default_hierarchy';
114: --
115: Begin
116: --
117: hr_utility.set_location('Entering:'||l_proc, 5);
118: --
119: --
120: -- Create a routing category and with default_flag 'Y'
121: -- File : pqrctapi.pkh/pkb
166: ,p_object_version_number => p_object_version_number
167: ,p_effective_date => p_effective_date);
168: --
169: --
170: hr_utility.set_location('Leaving:'||l_proc, 10);
171: --
172: exception when others then
173: p_routing_category_id := null;
174: p_object_version_number := null;
217: --
218: --
219: Begin
220: --
221: hr_utility.set_location('Entering:'||l_proc, 5);
222: --
223: -- Disable default approver and routing rules.
224: --
225: If p_old_routing_category_id IS NOT NULL then
397:
398: end if;
399: --
400: --
401: hr_utility.set_location('Leaving:'||l_proc, 10);
402: --
403: exception when others then
404:
405: p_routing_category_id := l_routing_category_id;
439: And enable_flag = 'N';
440: --
441: Begin
442: --
443: hr_utility.set_location('Entering:'||l_proc, 5);
444: --
445: -- Check if the member was previously selected as approver and then disabled.
446: --
447: Open csr_chk_already_approver;
490: End if;
491: --
492: Close csr_chk_already_approver;
493: --
494: hr_utility.set_location('Leaving:'||l_proc, 10);
495: --
496: exception when others then
497: p_range_name := l_range_name;
498: p_attribute_range_id := null;
521: l_object_version_number number := p_object_version_number;
522: --
523: Begin
524: --
525: hr_utility.set_location('Entering:'||l_proc, 5);
526: --
527: --
528: --
529: pqh_attribute_ranges_api.update_attribute_range
547: ,p_to_number => NULL
548: ,p_object_version_number => p_object_version_number
549: ,p_effective_date => p_effective_date);
550: --
551: hr_utility.set_location('Leaving:'||l_proc, 10);
552: --
553: exception when others then
554: p_object_version_number := l_object_version_number;
555: raise;
566: l_proc varchar2(72) := 'delete_default_approver';
567: --
568: Begin
569: --
570: hr_utility.set_location('Entering:'||l_proc, 5);
571: --
572: --
573: -- Delete the authorization rule for the default approver.
574: --
579: ,p_object_version_number => p_object_version_number
580: ,p_effective_date => p_effective_date
581: );
582: --
583: hr_utility.set_location('Leaving:'||l_proc, 10);
584: --
585: End;
586: --
587: --
633: l_proc varchar2(72) := 'select_routing_attribute';
634: --
635: Begin
636: --
637: hr_utility.set_location('Entering:'||l_proc, 5);
638: --
639: --
640: --
641: -- Mark the master as a routing attribute
675: End loop;
676: --
677:
678: --
679: hr_utility.set_location('Leaving:'||l_proc, 10);
680: --
681: End;
682: --
683: -----------------------------------------------------------------------------
718: l_proc varchar2(72) := 'unselect_routing_attribute';
719: --
720: Begin
721: --
722: hr_utility.set_location('Entering:'||l_proc, 5);
723: --
724: --
725: --
726: -- Select the master of the current routing attribute
749: End loop;
750: --
751:
752: --
753: hr_utility.set_location('Leaving:'||l_proc, 10);
754: --
755: End;
756: --
757: -----------------------------------------------------------------------------
798: l_proc varchar2(72) := 'select_authorization_attribute';
799: --
800: Begin
801: --
802: hr_utility.set_location('Entering:'||l_proc, 5);
803: --
804: --
805: --
806: -- Mark the master as a authorization attribute
839: --
840: End loop;
841: --
842: --
843: hr_utility.set_location('Leaving:'||l_proc, 10);
844: --
845: End;
846: --
847: -----------------------------------------------------------------------------
883: l_proc varchar2(72) := 'unselect_auth_attribute';
884: --
885: Begin
886: --
887: hr_utility.set_location('Entering:'||l_proc, 5);
888: --
889: --
890: --
891: -- Select the master of the current authorization attribute
913: --
914: End loop;
915: --
916: --
917: hr_utility.set_location('Leaving:'||l_proc, 10);
918: --
919: End;
920: --
921: ----------------------------------------------------------------------------
1007: l_proc varchar2(72) := 'Add_criteria_to_rules';
1008: --
1009: Begin
1010: --
1011: hr_utility.set_location('Entering:'||l_proc, 5);
1012: --
1013: --
1014: If p_identifier_type = 'ROUTING' then
1015: --
1084: --
1085: --
1086: End if;
1087: --
1088: hr_utility.set_location('Leaving:'||l_proc, 10);
1089: --
1090: End;
1091: --
1092: ----------------------------------------------------------------------------
1156: l_proc varchar2(72) := 'Refresh_routing_rules';
1157: --
1158: Begin
1159: --
1160: hr_utility.set_location('Entering:'||l_proc, 5);
1161: --
1162: --
1163: -- Remove from all routing rules under the transaction category, the part of
1164: -- the criteria containing the routing attribute that was now de-selected.
1165: --
1166: For attr_rec in csr_list_attr loop
1167: l_cnt := l_cnt + 1;
1168: l_attr_tab(l_cnt).attribute_id := attr_rec.attribute_id;
1169: hr_utility.set_location('List Identifier:'||to_char(l_attr_tab(l_cnt).attribute_id), 5);
1170: End loop;
1171: --
1172: For rct_rec in csr_rct loop
1173: hr_utility.set_location('Getting routing category', 5);
1169: hr_utility.set_location('List Identifier:'||to_char(l_attr_tab(l_cnt).attribute_id), 5);
1170: End loop;
1171: --
1172: For rct_rec in csr_rct loop
1173: hr_utility.set_location('Getting routing category', 5);
1174: For old_rec in csr_old(p_routing_category_id => rct_rec.routing_category_id) loop
1175: --
1176: hr_utility.set_location('List Identifier:'||to_char(old_rec.attribute_range_id), 5);
1177: l_found := false;
1172: For rct_rec in csr_rct loop
1173: hr_utility.set_location('Getting routing category', 5);
1174: For old_rec in csr_old(p_routing_category_id => rct_rec.routing_category_id) loop
1175: --
1176: hr_utility.set_location('List Identifier:'||to_char(old_rec.attribute_range_id), 5);
1177: l_found := false;
1178: For l_dummy in 1..l_cnt loop
1179: If old_rec.attribute_id = l_attr_tab(l_dummy).attribute_id then
1180: hr_utility.set_location('Inside If', 5);
1176: hr_utility.set_location('List Identifier:'||to_char(old_rec.attribute_range_id), 5);
1177: l_found := false;
1178: For l_dummy in 1..l_cnt loop
1179: If old_rec.attribute_id = l_attr_tab(l_dummy).attribute_id then
1180: hr_utility.set_location('Inside If', 5);
1181: l_found := true;
1182: End if;
1183: End loop;
1184: --
1182: End if;
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
1203: --
1204: disable_rout_hier_if_no_attr
1205: (p_transaction_category_id => p_transaction_category_id);
1206: --
1207: hr_utility.set_location('Leaving:'||l_proc, 10);
1208: --
1209: End;
1210: --
1211: -----------------------------------------------------------------------------
1271: l_proc varchar2(72) := 'Refresh_authorization_rules';
1272: --
1273: Begin
1274: --
1275: hr_utility.set_location('Entering:'||l_proc, 5);
1276: --
1277: For attr_rec in csr_mem_attr loop
1278: l_cnt := l_cnt + 1;
1279: l_attr_tab(l_cnt).attribute_id := attr_rec.attribute_id;
1310: --
1311: Add_criteria_to_rules(p_transaction_category_id => p_transaction_category_id,
1312: p_identifier_type => 'AUTHORIZATION');
1313: --
1314: hr_utility.set_location('Leaving:'||l_proc, 10);
1315: --
1316: End;
1317: --
1318: --------------------------------------------------------------------------------
1346: l_proc varchar2(72) := 'disable_rout_hier_if_no_attr';
1347: --
1348: Begin
1349: --
1350: hr_utility.set_location('Entering:'||l_proc, 5);
1351: --
1352: Open csr_attr;
1353: Fetch csr_attr into l_attribute_id;
1354: If csr_attr%notfound then
1367: End Loop;
1368: End if;
1369: Close csr_attr;
1370: --
1371: hr_utility.set_location('Leaving:'||l_proc, 10);
1372: --
1373: End;
1374: --
1375: ------------------------------------------------------------------------------
1394: l_proc varchar2(72) := 'chk_rules_exist';
1395: --
1396: Begin
1397: --
1398: hr_utility.set_location('Entering:'||l_proc, 5);
1399: --
1400: --
1401: Open csr_rules_exist;
1402: --
1415: --
1416: RETURN TRUE;
1417: --
1418: --
1419: hr_utility.set_location('Leaving:'||l_proc, 10);
1420: --
1421: End;
1422: --
1423: --
1441: l_proc varchar2(72) := 'chk_routing_history_exists';
1442: --
1443: Begin
1444: --
1445: hr_utility.set_location('Entering:'||l_proc, 5);
1446: --
1447: --
1448: Open csr_hist_exist;
1449: --
1462: --
1463: RETURN TRUE;
1464: --
1465: --
1466: hr_utility.set_location('Leaving:'||l_proc, 10);
1467: --
1468: End;
1469: -----------------------------------------------------------------------------
1470: PROCEDURE get_all_attribute_range_id(p_routing_category_id in number,
1493: l_proc varchar2(72) := 'get_all_attribute_range_id';
1494: --
1495: Begin
1496: --
1497: hr_utility.set_location('Entering:'||l_proc, 5);
1498: --
1499: --
1500: p_all_attribute_range_id := NULL;
1501: --
1521: --
1522: End if;
1523: --
1524: --
1525: hr_utility.set_location('Leaving:'||l_proc, 10);
1526: --
1527: exception when others then
1528: p_all_attribute_range_id := null;
1529: raise;
1561: l_proc varchar2(72) := 'create_routing_rule';
1562: --
1563: Begin
1564: --
1565: hr_utility.set_location('Entering:'||l_proc, 5);
1566: --
1567: --
1568: -- For the input a routing category and rule,insert all the selected
1569: -- routing attributes with attribute ranges value null
1594: p_all_attribute_range_id := substr(p_all_attribute_range_id,1,length(p_all_attribute_range_id) - 1);
1595: --
1596:
1597: --
1598: hr_utility.set_location('Leaving:'||l_proc, 10);
1599: --
1600: exception when others then
1601: p_all_attribute_range_id := null;
1602: raise;
1626: l_proc varchar2(72) := 'update_rule';
1627: --
1628: Begin
1629: --
1630: hr_utility.set_location('Entering:'||l_proc, 5);
1631: --
1632: l_all_attribute_range_id := p_all_attribute_range_id;
1633: --
1634: if l_all_attribute_range_id IS NULL then
1670: --
1671: Close csr_update_rule;
1672: --
1673: --
1674: hr_utility.set_location('Leaving:'||l_proc, 10);
1675: --
1676: End;
1677: --
1678: -------------------------------------------------------------------------------------
1699: l_proc varchar2(72) := 'update_routing_rule';
1700: --
1701: Begin
1702: --
1703: hr_utility.set_location('Entering:'||l_proc, 5);
1704: --
1705: --
1706: update_rule(p_routing_category_id => p_routing_category_id,
1707: p_range_name => p_range_name,
1710: p_approver_flag => p_approver_flag,
1711: p_all_attribute_range_id => p_all_attribute_range_id);
1712: --
1713: --
1714: hr_utility.set_location('Leaving:'||l_proc, 10);
1715: --
1716: End;
1717: --
1718: --------------------------------------------------------------------------------------
1735: l_proc varchar2(72) := 'delete_rule';
1736: --
1737: Begin
1738: --
1739: hr_utility.set_location('Entering:'||l_proc, 5);
1740: --
1741: l_all_attribute_range_id := p_all_attribute_range_id;
1742: --
1743: if l_all_attribute_range_id IS NULL then
1773: --
1774: Close csr_delete_rule;
1775: --
1776: --
1777: hr_utility.set_location('Leaving:'||l_proc, 10);
1778: --
1779: End;
1780: --
1781: ----------------------------------------------------------------------------------
1795: l_proc varchar2(72) := 'delete_routing_rule';
1796: --
1797: Begin
1798: --
1799: hr_utility.set_location('Entering:'||l_proc, 5);
1800: --
1801: --
1802: delete_rule(p_routing_category_id => p_routing_category_id,
1803: p_all_attribute_range_id => p_all_attribute_range_id);
1802: delete_rule(p_routing_category_id => p_routing_category_id,
1803: p_all_attribute_range_id => p_all_attribute_range_id);
1804: --
1805: --
1806: hr_utility.set_location('Leaving:'||l_proc, 10);
1807: --
1808: End;
1809: --
1810: -----------------------------------------------------------------------------
1855: l_proc varchar2(72) := 'create_approver';
1856: --
1857: Begin
1858: --
1859: hr_utility.set_location('Entering:'||l_proc, 5);
1860: --
1861: For exist_appr_rec in csr_chk_already_approver loop
1862: --
1863: l_create_appr := false;
1907: --
1908: --
1909: End if;
1910: --
1911: hr_utility.set_location('Leaving:'||l_proc, 10);
1912: --
1913: exception when others then
1914: p_gen_sys_rule_name := null;
1915: raise;
1946: l_proc varchar2(72) := 'update_approver';
1947: --
1948: Begin
1949: --
1950: hr_utility.set_location('Entering:'||l_proc, 5);
1951: --
1952: --
1953: -- For the input a routing category , and authorizer
1954: -- Delete all non-default rules for the authorizer under the
2002: --
2003: Close csr_update_approver;
2004: --
2005: --
2006: hr_utility.set_location('Leaving:'||l_proc, 10);
2007: --
2008: End;
2009: --
2010: --------------------------------------------------------------------------
2037: l_proc varchar2(72) := 'delete_approver';
2038: --
2039: Begin
2040: --
2041: hr_utility.set_location('Entering:'||l_proc, 5);
2042: --
2043: --
2044: -- For the input a routing category , and authorizer
2045: -- Delete all non-default rules for the authorizer under the
2088: --
2089: Close csr_delete_approver;
2090: --
2091: --
2092: hr_utility.set_location('Leaving:'||l_proc, 10);
2093: --
2094: End;
2095: --
2096: PROCEDURE update_approver_flag(p_routing_category_id in number,
2112: l_proc varchar2(72) := 'update_approver_flag';
2113: --
2114: Begin
2115: --
2116: hr_utility.set_location('Entering:'||l_proc, 5);
2117: --
2118: --
2119: -- For the input a routing category , and authorizer
2120: -- Delete all non-default rules for the authorizer under the
2166: --
2167: Close csr_delete_approver;
2168: --
2169: --
2170: hr_utility.set_location('Leaving:'||l_proc, 10);
2171: --
2172: End;
2173: --
2174: --
2219: --
2220: --
2221: Begin
2222: --
2223: hr_utility.set_location('Entering:'||l_proc, 5);
2224: --
2225: --
2226: -- For the input a routing category and rule,insert all the selected
2227: -- authorization attributes with attribute ranges value null
2270: --
2271: End loop;
2272: --
2273: --
2274: hr_utility.set_location('Leaving:'||l_proc, 10);
2275: --
2276: exception when others then
2277: p_all_attribute_range_id := null;
2278: raise;
2294: l_proc varchar2(72) := 'update_authorization_rule';
2295: --
2296: Begin
2297: --
2298: hr_utility.set_location('Entering:'||l_proc, 5);
2299: --
2300: --
2301: update_rule(p_routing_category_id => p_routing_category_id,
2302: p_range_name => p_range_name,
2305: p_delete_flag => p_delete_flag,
2306: p_all_attribute_range_id => p_all_attribute_range_id);
2307: --
2308: --
2309: hr_utility.set_location('Leaving:'||l_proc, 10);
2310: --
2311: End;
2312: --
2313: --
2322: l_proc varchar2(72) := 'delete_authorization_rule';
2323: --
2324: Begin
2325: --
2326: hr_utility.set_location('Entering:'||l_proc, 5);
2327: --
2328: --
2329: delete_rule(p_routing_category_id => p_routing_category_id,
2330: p_all_attribute_range_id => p_all_attribute_range_id);
2329: delete_rule(p_routing_category_id => p_routing_category_id,
2330: p_all_attribute_range_id => p_all_attribute_range_id);
2331: --
2332: --
2333: hr_utility.set_location('Leaving:'||l_proc, 10);
2334: --
2335: End;
2336: --
2337: --
2386: l_proc varchar2(72) := 'create_local_setup';
2387: --
2388: BEGIN
2389: --
2390: hr_utility.set_location('Entering:'||l_proc, 5);
2391: --
2392: --
2393: -- Copying the transaction category details
2394: --
2540: --
2541: p_transaction_category_id := tct_id;
2542: --
2543: --
2544: hr_utility.set_location('Leaving:'||l_proc, 10);
2545: --
2546: END;
2547: -----------------------------------------------------------------------------
2548: PROCEDURE freeze_category (p_transaction_category_id in number,
2552: l_proc varchar2(72) := 'freeze_category';
2553: --
2554: BEGIN
2555: --
2556: hr_utility.set_location('Entering:'||l_proc, 5);
2557: --
2558: --
2559: Update pqh_transaction_categories
2560: set freeze_status_cd = p_freeze_status_cd
2562: where transaction_category_id = p_transaction_category_id;
2563: --
2564: --
2565: --
2566: hr_utility.set_location('Leaving:'||l_proc, 10);
2567: --
2568: END;
2569: -----------------------------------------------------------------------------
2570:
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:
2603: end if;
2604: End loop;
2605: Return TRUE;
2606: --
2607: hr_utility.set_location('Leaving:'||l_proc, 10);
2608: --
2609: End;
2610: --------------------------------------------------------------------------------------------
2611:
2821: --
2822: -- set error
2823: --
2824: l_error_index := l_error_index + 1;
2825: hr_utility.set_message(8302,'PQH_TCW_STD_ERROR1');
2826: p_error_messages(l_error_index).message_text := hr_utility.get_message;
2827: --
2828: l_error_index := l_error_index + 1;
2829: hr_utility.set_message(8302,'PQH_TCW_STD_ERROR2');
2822: -- set error
2823: --
2824: l_error_index := l_error_index + 1;
2825: hr_utility.set_message(8302,'PQH_TCW_STD_ERROR1');
2826: p_error_messages(l_error_index).message_text := hr_utility.get_message;
2827: --
2828: l_error_index := l_error_index + 1;
2829: hr_utility.set_message(8302,'PQH_TCW_STD_ERROR2');
2830: p_error_messages(l_error_index).message_text := hr_utility.get_message;
2825: hr_utility.set_message(8302,'PQH_TCW_STD_ERROR1');
2826: p_error_messages(l_error_index).message_text := hr_utility.get_message;
2827: --
2828: l_error_index := l_error_index + 1;
2829: hr_utility.set_message(8302,'PQH_TCW_STD_ERROR2');
2830: p_error_messages(l_error_index).message_text := hr_utility.get_message;
2831: --
2832: l_error_status := FALSE;
2833: Else
2826: p_error_messages(l_error_index).message_text := hr_utility.get_message;
2827: --
2828: l_error_index := l_error_index + 1;
2829: hr_utility.set_message(8302,'PQH_TCW_STD_ERROR2');
2830: p_error_messages(l_error_index).message_text := hr_utility.get_message;
2831: --
2832: l_error_status := FALSE;
2833: Else
2834: --
2838: Fetch csr_def_approvers into l_dummy;
2839: If csr_def_approvers%notfound then
2840: --
2841: l_error_index := l_error_index + 1;
2842: hr_utility.set_message(8302,'PQH_TCW_STD_ERROR2');
2843: p_error_messages(l_error_index).message_text := hr_utility.get_message;
2844: l_error_status := FALSE;
2845: --
2846: End if;
2839: If csr_def_approvers%notfound then
2840: --
2841: l_error_index := l_error_index + 1;
2842: hr_utility.set_message(8302,'PQH_TCW_STD_ERROR2');
2843: p_error_messages(l_error_index).message_text := hr_utility.get_message;
2844: l_error_status := FALSE;
2845: --
2846: End if;
2847: Close csr_def_approvers;
2916: and rng.assignment_id IS NULL;
2917: --
2918: Begin
2919: --
2920: hr_utility.set_location('Entering:'||l_proc, 5);
2921: --
2922: --
2923: -- The foll cursor selects the no of enabled routing categories exist for a
2924: -- transaction category,and how many routing rules exists under each routing
2953: If l_cnt > 0 then
2954: For l_dummy in 1..l_cnt loop
2955: --
2956: --
2957: hr_utility.set_location('Getting rules for'||to_char(l_rct_tab(l_dummy).routing_category_id), 100);
2958: Open csr_rules(l_rct_tab(l_dummy).routing_category_id);
2959: Fetch csr_rules into l_x;
2960: If csr_rules%notfound then
2961: hr_utility.set_location('No routing rules ', 100);
2957: hr_utility.set_location('Getting rules for'||to_char(l_rct_tab(l_dummy).routing_category_id), 100);
2958: Open csr_rules(l_rct_tab(l_dummy).routing_category_id);
2959: Fetch csr_rules into l_x;
2960: If csr_rules%notfound then
2961: hr_utility.set_location('No routing rules ', 100);
2962: l_no_of_rules := 0;
2963: else
2964: hr_utility.set_location('Exist routing rules ', 100);
2965: l_no_of_rules := 1;
2960: If csr_rules%notfound then
2961: hr_utility.set_location('No routing rules ', 100);
2962: l_no_of_rules := 0;
2963: else
2964: hr_utility.set_location('Exist routing rules ', 100);
2965: l_no_of_rules := 1;
2966: End if;
2967: Close csr_rules;
2968: --
2971: -- No rules were defined for this routing category.
2972: --
2973: If l_no_of_rules = 0 then
2974: --
2975: hr_utility.set_location('rules =0', 100);
2976: l_no_of_errors := l_no_of_errors + 1;
2977: pqh_tct_bus.get_routing_category_name
2978: (p_routing_category_id => l_rct_tab(l_dummy).routing_category_id,
2979: p_routing_category_name => l_list_name);
2977: pqh_tct_bus.get_routing_category_name
2978: (p_routing_category_id => l_rct_tab(l_dummy).routing_category_id,
2979: p_routing_category_name => l_list_name);
2980: --
2981: hr_utility.set_message(8302,'PQH_NO_RULES_IN_ROUTING_CAT');
2982: hr_utility.set_message_token('LIST_NAME', l_list_name);
2983: p_error_messages(l_no_of_errors).message_text :='* '|| hr_utility.get_message;
2984: --
2985: p_no_errors := l_no_of_errors;
2978: (p_routing_category_id => l_rct_tab(l_dummy).routing_category_id,
2979: p_routing_category_name => l_list_name);
2980: --
2981: hr_utility.set_message(8302,'PQH_NO_RULES_IN_ROUTING_CAT');
2982: hr_utility.set_message_token('LIST_NAME', l_list_name);
2983: p_error_messages(l_no_of_errors).message_text :='* '|| hr_utility.get_message;
2984: --
2985: p_no_errors := l_no_of_errors;
2986: --
2979: p_routing_category_name => l_list_name);
2980: --
2981: hr_utility.set_message(8302,'PQH_NO_RULES_IN_ROUTING_CAT');
2982: hr_utility.set_message_token('LIST_NAME', l_list_name);
2983: p_error_messages(l_no_of_errors).message_text :='* '|| hr_utility.get_message;
2984: --
2985: p_no_errors := l_no_of_errors;
2986: --
2987: RETURN FALSE;
2996: --
2997: If l_rec_count = 0 then
2998: --
2999: l_no_of_errors := l_no_of_errors + 1;
3000: hr_utility.set_message(8302,'PQH_NO_ROUTING_CAT_IN_TCT');
3001: p_error_messages(l_no_of_errors).message_text :='* '|| hr_utility.get_message;
3002: --
3003: p_no_errors := l_no_of_errors;
3004: --
2997: If l_rec_count = 0 then
2998: --
2999: l_no_of_errors := l_no_of_errors + 1;
3000: hr_utility.set_message(8302,'PQH_NO_ROUTING_CAT_IN_TCT');
3001: p_error_messages(l_no_of_errors).message_text :='* '|| hr_utility.get_message;
3002: --
3003: p_no_errors := l_no_of_errors;
3004: --
3005: RETURN FALSE;
3062: pqh_tct_bus.get_routing_category_name
3063: (p_routing_category_id =>l_routing_category_id,
3064: p_routing_category_name=> l_list_name);
3065: --
3066: hr_utility.set_message(8302,'PQH_NO_RULES_IN_ROUTING_CAT');
3067: hr_utility.set_message_token('LIST_NAME', l_list_name);
3068: p_error_messages(l_no_of_errors).message_text :='* '|| hr_utility.get_message;
3069: --
3070: p_no_errors := l_no_of_errors;
3063: (p_routing_category_id =>l_routing_category_id,
3064: p_routing_category_name=> l_list_name);
3065: --
3066: hr_utility.set_message(8302,'PQH_NO_RULES_IN_ROUTING_CAT');
3067: hr_utility.set_message_token('LIST_NAME', l_list_name);
3068: p_error_messages(l_no_of_errors).message_text :='* '|| hr_utility.get_message;
3069: --
3070: p_no_errors := l_no_of_errors;
3071: --
3064: p_routing_category_name=> l_list_name);
3065: --
3066: hr_utility.set_message(8302,'PQH_NO_RULES_IN_ROUTING_CAT');
3067: hr_utility.set_message_token('LIST_NAME', l_list_name);
3068: p_error_messages(l_no_of_errors).message_text :='* '|| hr_utility.get_message;
3069: --
3070: p_no_errors := l_no_of_errors;
3071: --
3072: RETURN FALSE;
3081: --
3082: If l_rec_count = 0 then
3083: --
3084: l_no_of_errors := l_no_of_errors + 1;
3085: hr_utility.set_message(8302,'PQH_NO_ROUTING_CAT_IN_TCT');
3086: p_error_messages(l_no_of_errors).message_text :='* '|| hr_utility.get_message;
3087: --
3088: p_no_errors := l_no_of_errors;
3089: --
3082: If l_rec_count = 0 then
3083: --
3084: l_no_of_errors := l_no_of_errors + 1;
3085: hr_utility.set_message(8302,'PQH_NO_ROUTING_CAT_IN_TCT');
3086: p_error_messages(l_no_of_errors).message_text :='* '|| hr_utility.get_message;
3087: --
3088: p_no_errors := l_no_of_errors;
3089: --
3090: RETURN FALSE;
3125: **/
3126:
3127: Begin
3128: --
3129: hr_utility.set_location('Entering:'||l_proc, 5);
3130: --
3131: -- Select all routing categories under the transaction category that
3132: -- belong to the current routing type
3133: --
3173: p_overlap_range_name => l_overlap_range_name,
3174: p_error_routing_category => l_error_routing_category,
3175: p_error_range_name => l_error_range_name);
3176: --
3177: hr_utility.set_location('Leaving:'||l_proc, 10);
3178: --
3179: If l_error_code = 1 then
3180: --
3181: Close csr_chk_rule_overlap;
3246: l_proc varchar2(72) := 'chk_mem_overlap_on_freeze';
3247: --
3248: Begin
3249: --
3250: hr_utility.set_location('Entering:'||l_proc, 5);
3251: --
3252: Open csr_mem_ident_cnt;
3253: Fetch csr_mem_ident_cnt into l_no_mem_identifiers;
3254: Close csr_mem_ident_cnt;
3339: l_from_char,l_to_char,
3340: l_from_number,l_to_number,
3341: l_from_date,l_to_date;
3342: If csr_mem_overlap%notfound then
3343: hr_utility.set_location('Closing cursor',100);
3344: Close csr_mem_overlap;
3345: exit;
3346: End if;
3347: --
3350: --
3351: If l_routing_category_id <> l_prev_routing_category_id OR
3352: nvl(l_range_name,'xXx') <> nvl(l_prev_range_name,hr_api.g_varchar2) then
3353: --
3354: hr_utility.set_location('New rule:'||l_range_name ||l_proc, 6);
3355: --
3356: If cnt > 0 then
3357: hr_utility.set_location('Rules exist '||l_proc, 6);
3358: --
3353: --
3354: hr_utility.set_location('New rule:'||l_range_name ||l_proc, 6);
3355: --
3356: If cnt > 0 then
3357: hr_utility.set_location('Rules exist '||l_proc, 6);
3358: --
3359: -- call chk_routing_range_overlap procedure to check if this rule
3360: -- overlaps with any other routing rules under that
3361: -- transaction category.
3359: -- call chk_routing_range_overlap procedure to check if this rule
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,
3397: End if;
3398: --
3399: End if;
3400: -- Reset counters
3401: hr_utility.set_location('Reset counter'||l_proc, 6);
3402: --
3403: cnt := 1;
3404: l_prev_routing_category_id := l_routing_category_id;
3405: l_prev_range_name := l_range_name;
3410: l_error_range_name := NULL;
3411: l_attribute_range_id_list := NULL;
3412: --
3413: Else
3414: hr_utility.set_location('Increment counter'||l_proc, 6);
3415: -- If we are processing same rule , increment counter
3416: cnt := cnt + 1;
3417: l_attribute_range_id_list := l_attribute_range_id_list || ',';
3418:
3434: End loop;
3435: --
3436: If cnt > 0 then
3437: --
3438: hr_utility.set_location('Rules exist '||l_proc, 6);
3439: --
3440: -- call chk_routing_range_overlap procedure to check if this rule
3441: -- overlaps with any other routing rules under that
3442: -- transaction category.
3440: -- call chk_routing_range_overlap procedure to check if this rule
3441: -- overlaps with any other routing rules under that
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,
3482: End if;
3483: --
3484: End if;
3485: --
3486: hr_utility.set_location('Leaving'||l_proc, 10);
3487: --
3488: RETURN TRUE;
3489: --
3490: End;
3549: --
3550: If NOT l_status then
3551: --
3552: l_error_index := l_error_index + 1;
3553: hr_utility.set_message(8302,'PQH_TCW_ADV_ERROR1');
3554: p_error_messages(l_error_index).message_text := hr_utility.get_message;
3555: --
3556: l_setup_status := FALSE;
3557: --
3550: If NOT l_status then
3551: --
3552: l_error_index := l_error_index + 1;
3553: hr_utility.set_message(8302,'PQH_TCW_ADV_ERROR1');
3554: p_error_messages(l_error_index).message_text := hr_utility.get_message;
3555: --
3556: l_setup_status := FALSE;
3557: --
3558: End if;
3569: --
3570: If NOT l_status then
3571: --
3572: l_error_index := l_error_index + 1;
3573: hr_utility.set_message(8302,'PQH_TCW_ADV_ERROR2');
3574: p_error_messages(l_error_index).message_text := hr_utility.get_message;
3575: --
3576: l_setup_status := FALSE;
3577: --
3570: If NOT l_status then
3571: --
3572: l_error_index := l_error_index + 1;
3573: hr_utility.set_message(8302,'PQH_TCW_ADV_ERROR2');
3574: p_error_messages(l_error_index).message_text := hr_utility.get_message;
3575: --
3576: l_setup_status := FALSE;
3577: --
3578: End if;
3609: --
3610: --
3611: Begin
3612: --
3613: hr_utility.set_location('Entering'||l_proc, 5);
3614: --
3615: -- Obtain the routing style of the transcation category.
3616: --
3617: Open csr_member_cd(p_transaction_category_id => p_transaction_category_id);
3635: End if;
3636: --
3637: Close csr_hier;
3638: --
3639: hr_utility.set_location('Leaving'||l_proc, 10);
3640: --
3641: RETURN l_error_status;
3642: --
3643: END;
3655: --
3656: BEGIN
3657: --
3658: --
3659: hr_utility.set_location('Entering'||l_proc, 5);
3660: --
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.
3734: End if;
3735: --
3736: Execute immediate sql_stmt1 using 'N','Y',p_transaction_category_id;
3737: --
3738: hr_utility.set_location('Leaving'||l_proc, 10);
3739: --
3740: END delete_hierarchy_and_rules;
3741: ---------------------------------------------------------------------------------------------
3742: FUNCTION return_approver_status(p_routing_category_id in number,
3754: --
3755: BEGIN
3756: --
3757: --
3758: hr_utility.set_location('Entering'||l_proc, 5);
3759: --
3760: -- The following cursor selects the approver flag on all the rules for the
3761: -- approver under the passed routing category.
3762: --
3794: --
3795: End loop;
3796:
3797: --
3798: hr_utility.set_location('Leaving'||l_proc, 10);
3799: --
3800: RETURN 'N';
3801: --
3802: END return_approver_status;
3818: l_proc varchar2(72) := 'return_person_name';
3819: --
3820: BEGIN
3821: --
3822: hr_utility.set_location('Entering'||l_proc, 5);
3823: --
3824: Open csr_name;
3825: Fetch csr_name into l_name;
3826: Close csr_name;
3824: Open csr_name;
3825: Fetch csr_name into l_name;
3826: Close csr_name;
3827: --
3828: hr_utility.set_location('Leaving'||l_proc, 10);
3829: --
3830: RETURN l_name;
3831: --
3832: End return_person_name;