[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: --
2561: --
2562: p_transaction_category_id := tct_id;
2563: --
2564: --
2565: hr_utility.set_location('Leaving:'||l_proc, 10);
2566: --
2567: END;
2568: -----------------------------------------------------------------------------
2569: PROCEDURE freeze_category (p_transaction_category_id in number,
2573: l_proc varchar2(72) := 'freeze_category';
2574: --
2575: BEGIN
2576: --
2577: hr_utility.set_location('Entering:'||l_proc, 5);
2578: --
2579: --
2580: Update pqh_transaction_categories
2581: set freeze_status_cd = p_freeze_status_cd
2583: where transaction_category_id = p_transaction_category_id;
2584: --
2585: --
2586: --
2587: hr_utility.set_location('Leaving:'||l_proc, 10);
2588: --
2589: END;
2590: -----------------------------------------------------------------------------
2591:
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:
2624: end if;
2625: End loop;
2626: Return TRUE;
2627: --
2628: hr_utility.set_location('Leaving:'||l_proc, 10);
2629: --
2630: End;
2631: --------------------------------------------------------------------------------------------
2632:
2842: --
2843: -- set error
2844: --
2845: l_error_index := l_error_index + 1;
2846: hr_utility.set_message(8302,'PQH_TCW_STD_ERROR1');
2847: p_error_messages(l_error_index).message_text := hr_utility.get_message;
2848: --
2849: l_error_index := l_error_index + 1;
2850: hr_utility.set_message(8302,'PQH_TCW_STD_ERROR2');
2843: -- set error
2844: --
2845: l_error_index := l_error_index + 1;
2846: hr_utility.set_message(8302,'PQH_TCW_STD_ERROR1');
2847: p_error_messages(l_error_index).message_text := hr_utility.get_message;
2848: --
2849: l_error_index := l_error_index + 1;
2850: hr_utility.set_message(8302,'PQH_TCW_STD_ERROR2');
2851: p_error_messages(l_error_index).message_text := hr_utility.get_message;
2846: hr_utility.set_message(8302,'PQH_TCW_STD_ERROR1');
2847: p_error_messages(l_error_index).message_text := hr_utility.get_message;
2848: --
2849: l_error_index := l_error_index + 1;
2850: hr_utility.set_message(8302,'PQH_TCW_STD_ERROR2');
2851: p_error_messages(l_error_index).message_text := hr_utility.get_message;
2852: --
2853: l_error_status := FALSE;
2854: Else
2847: p_error_messages(l_error_index).message_text := hr_utility.get_message;
2848: --
2849: l_error_index := l_error_index + 1;
2850: hr_utility.set_message(8302,'PQH_TCW_STD_ERROR2');
2851: p_error_messages(l_error_index).message_text := hr_utility.get_message;
2852: --
2853: l_error_status := FALSE;
2854: Else
2855: --
2859: Fetch csr_def_approvers into l_dummy;
2860: If csr_def_approvers%notfound then
2861: --
2862: l_error_index := l_error_index + 1;
2863: hr_utility.set_message(8302,'PQH_TCW_STD_ERROR2');
2864: p_error_messages(l_error_index).message_text := hr_utility.get_message;
2865: l_error_status := FALSE;
2866: --
2867: End if;
2860: If csr_def_approvers%notfound then
2861: --
2862: l_error_index := l_error_index + 1;
2863: hr_utility.set_message(8302,'PQH_TCW_STD_ERROR2');
2864: p_error_messages(l_error_index).message_text := hr_utility.get_message;
2865: l_error_status := FALSE;
2866: --
2867: End if;
2868: Close csr_def_approvers;
2937: and rng.assignment_id IS NULL;
2938: --
2939: Begin
2940: --
2941: hr_utility.set_location('Entering:'||l_proc, 5);
2942: --
2943: --
2944: -- The foll cursor selects the no of enabled routing categories exist for a
2945: -- transaction category,and how many routing rules exists under each routing
2974: If l_cnt > 0 then
2975: For l_dummy in 1..l_cnt loop
2976: --
2977: --
2978: hr_utility.set_location('Getting rules for'||to_char(l_rct_tab(l_dummy).routing_category_id), 100);
2979: Open csr_rules(l_rct_tab(l_dummy).routing_category_id);
2980: Fetch csr_rules into l_x;
2981: If csr_rules%notfound then
2982: hr_utility.set_location('No routing rules ', 100);
2978: hr_utility.set_location('Getting rules for'||to_char(l_rct_tab(l_dummy).routing_category_id), 100);
2979: Open csr_rules(l_rct_tab(l_dummy).routing_category_id);
2980: Fetch csr_rules into l_x;
2981: If csr_rules%notfound then
2982: hr_utility.set_location('No routing rules ', 100);
2983: l_no_of_rules := 0;
2984: else
2985: hr_utility.set_location('Exist routing rules ', 100);
2986: l_no_of_rules := 1;
2981: If csr_rules%notfound then
2982: hr_utility.set_location('No routing rules ', 100);
2983: l_no_of_rules := 0;
2984: else
2985: hr_utility.set_location('Exist routing rules ', 100);
2986: l_no_of_rules := 1;
2987: End if;
2988: Close csr_rules;
2989: --
2992: -- No rules were defined for this routing category.
2993: --
2994: If l_no_of_rules = 0 then
2995: --
2996: hr_utility.set_location('rules =0', 100);
2997: l_no_of_errors := l_no_of_errors + 1;
2998: pqh_tct_bus.get_routing_category_name
2999: (p_routing_category_id => l_rct_tab(l_dummy).routing_category_id,
3000: p_routing_category_name => l_list_name);
2998: pqh_tct_bus.get_routing_category_name
2999: (p_routing_category_id => l_rct_tab(l_dummy).routing_category_id,
3000: p_routing_category_name => l_list_name);
3001: --
3002: hr_utility.set_message(8302,'PQH_NO_RULES_IN_ROUTING_CAT');
3003: hr_utility.set_message_token('LIST_NAME', l_list_name);
3004: p_error_messages(l_no_of_errors).message_text :='* '|| hr_utility.get_message;
3005: --
3006: p_no_errors := l_no_of_errors;
2999: (p_routing_category_id => l_rct_tab(l_dummy).routing_category_id,
3000: p_routing_category_name => l_list_name);
3001: --
3002: hr_utility.set_message(8302,'PQH_NO_RULES_IN_ROUTING_CAT');
3003: hr_utility.set_message_token('LIST_NAME', l_list_name);
3004: p_error_messages(l_no_of_errors).message_text :='* '|| hr_utility.get_message;
3005: --
3006: p_no_errors := l_no_of_errors;
3007: --
3000: p_routing_category_name => l_list_name);
3001: --
3002: hr_utility.set_message(8302,'PQH_NO_RULES_IN_ROUTING_CAT');
3003: hr_utility.set_message_token('LIST_NAME', l_list_name);
3004: p_error_messages(l_no_of_errors).message_text :='* '|| hr_utility.get_message;
3005: --
3006: p_no_errors := l_no_of_errors;
3007: --
3008: RETURN FALSE;
3017: --
3018: If l_rec_count = 0 then
3019: --
3020: l_no_of_errors := l_no_of_errors + 1;
3021: hr_utility.set_message(8302,'PQH_NO_ROUTING_CAT_IN_TCT');
3022: p_error_messages(l_no_of_errors).message_text :='* '|| hr_utility.get_message;
3023: --
3024: p_no_errors := l_no_of_errors;
3025: --
3018: If l_rec_count = 0 then
3019: --
3020: l_no_of_errors := l_no_of_errors + 1;
3021: hr_utility.set_message(8302,'PQH_NO_ROUTING_CAT_IN_TCT');
3022: p_error_messages(l_no_of_errors).message_text :='* '|| hr_utility.get_message;
3023: --
3024: p_no_errors := l_no_of_errors;
3025: --
3026: RETURN FALSE;
3083: pqh_tct_bus.get_routing_category_name
3084: (p_routing_category_id =>l_routing_category_id,
3085: p_routing_category_name=> l_list_name);
3086: --
3087: hr_utility.set_message(8302,'PQH_NO_RULES_IN_ROUTING_CAT');
3088: hr_utility.set_message_token('LIST_NAME', l_list_name);
3089: p_error_messages(l_no_of_errors).message_text :='* '|| hr_utility.get_message;
3090: --
3091: p_no_errors := l_no_of_errors;
3084: (p_routing_category_id =>l_routing_category_id,
3085: p_routing_category_name=> l_list_name);
3086: --
3087: hr_utility.set_message(8302,'PQH_NO_RULES_IN_ROUTING_CAT');
3088: hr_utility.set_message_token('LIST_NAME', l_list_name);
3089: p_error_messages(l_no_of_errors).message_text :='* '|| hr_utility.get_message;
3090: --
3091: p_no_errors := l_no_of_errors;
3092: --
3085: p_routing_category_name=> l_list_name);
3086: --
3087: hr_utility.set_message(8302,'PQH_NO_RULES_IN_ROUTING_CAT');
3088: hr_utility.set_message_token('LIST_NAME', l_list_name);
3089: p_error_messages(l_no_of_errors).message_text :='* '|| hr_utility.get_message;
3090: --
3091: p_no_errors := l_no_of_errors;
3092: --
3093: RETURN FALSE;
3102: --
3103: If l_rec_count = 0 then
3104: --
3105: l_no_of_errors := l_no_of_errors + 1;
3106: hr_utility.set_message(8302,'PQH_NO_ROUTING_CAT_IN_TCT');
3107: p_error_messages(l_no_of_errors).message_text :='* '|| hr_utility.get_message;
3108: --
3109: p_no_errors := l_no_of_errors;
3110: --
3103: If l_rec_count = 0 then
3104: --
3105: l_no_of_errors := l_no_of_errors + 1;
3106: hr_utility.set_message(8302,'PQH_NO_ROUTING_CAT_IN_TCT');
3107: p_error_messages(l_no_of_errors).message_text :='* '|| hr_utility.get_message;
3108: --
3109: p_no_errors := l_no_of_errors;
3110: --
3111: RETURN FALSE;
3146: **/
3147:
3148: Begin
3149: --
3150: hr_utility.set_location('Entering:'||l_proc, 5);
3151: --
3152: -- Select all routing categories under the transaction category that
3153: -- belong to the current routing type
3154: --
3194: p_overlap_range_name => l_overlap_range_name,
3195: p_error_routing_category => l_error_routing_category,
3196: p_error_range_name => l_error_range_name);
3197: --
3198: hr_utility.set_location('Leaving:'||l_proc, 10);
3199: --
3200: If l_error_code = 1 then
3201: --
3202: Close csr_chk_rule_overlap;
3267: l_proc varchar2(72) := 'chk_mem_overlap_on_freeze';
3268: --
3269: Begin
3270: --
3271: hr_utility.set_location('Entering:'||l_proc, 5);
3272: --
3273: Open csr_mem_ident_cnt;
3274: Fetch csr_mem_ident_cnt into l_no_mem_identifiers;
3275: Close csr_mem_ident_cnt;
3360: l_from_char,l_to_char,
3361: l_from_number,l_to_number,
3362: l_from_date,l_to_date;
3363: If csr_mem_overlap%notfound then
3364: hr_utility.set_location('Closing cursor',100);
3365: Close csr_mem_overlap;
3366: exit;
3367: End if;
3368: --
3371: --
3372: If l_routing_category_id <> l_prev_routing_category_id OR
3373: nvl(l_range_name,'xXx') <> nvl(l_prev_range_name,hr_api.g_varchar2) then
3374: --
3375: hr_utility.set_location('New rule:'||l_range_name ||l_proc, 6);
3376: --
3377: If cnt > 0 then
3378: hr_utility.set_location('Rules exist '||l_proc, 6);
3379: --
3374: --
3375: hr_utility.set_location('New rule:'||l_range_name ||l_proc, 6);
3376: --
3377: If cnt > 0 then
3378: hr_utility.set_location('Rules exist '||l_proc, 6);
3379: --
3380: -- call chk_routing_range_overlap procedure to check if this rule
3381: -- overlaps with any other routing rules under that
3382: -- transaction category.
3380: -- call chk_routing_range_overlap procedure to check if this rule
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,
3418: End if;
3419: --
3420: End if;
3421: -- Reset counters
3422: hr_utility.set_location('Reset counter'||l_proc, 6);
3423: --
3424: cnt := 1;
3425: l_prev_routing_category_id := l_routing_category_id;
3426: l_prev_range_name := l_range_name;
3431: l_error_range_name := NULL;
3432: l_attribute_range_id_list := NULL;
3433: --
3434: Else
3435: hr_utility.set_location('Increment counter'||l_proc, 6);
3436: -- If we are processing same rule , increment counter
3437: cnt := cnt + 1;
3438: l_attribute_range_id_list := l_attribute_range_id_list || ',';
3439:
3455: End loop;
3456: --
3457: If cnt > 0 then
3458: --
3459: hr_utility.set_location('Rules exist '||l_proc, 6);
3460: --
3461: -- call chk_routing_range_overlap procedure to check if this rule
3462: -- overlaps with any other routing rules under that
3463: -- transaction category.
3461: -- call chk_routing_range_overlap procedure to check if this rule
3462: -- overlaps with any other routing rules under that
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,
3503: End if;
3504: --
3505: End if;
3506: --
3507: hr_utility.set_location('Leaving'||l_proc, 10);
3508: --
3509: RETURN TRUE;
3510: --
3511: End;
3570: --
3571: If NOT l_status then
3572: --
3573: l_error_index := l_error_index + 1;
3574: hr_utility.set_message(8302,'PQH_TCW_ADV_ERROR1');
3575: p_error_messages(l_error_index).message_text := hr_utility.get_message;
3576: --
3577: l_setup_status := FALSE;
3578: --
3571: If NOT l_status then
3572: --
3573: l_error_index := l_error_index + 1;
3574: hr_utility.set_message(8302,'PQH_TCW_ADV_ERROR1');
3575: p_error_messages(l_error_index).message_text := hr_utility.get_message;
3576: --
3577: l_setup_status := FALSE;
3578: --
3579: End if;
3590: --
3591: If NOT l_status then
3592: --
3593: l_error_index := l_error_index + 1;
3594: hr_utility.set_message(8302,'PQH_TCW_ADV_ERROR2');
3595: p_error_messages(l_error_index).message_text := hr_utility.get_message;
3596: --
3597: l_setup_status := FALSE;
3598: --
3591: If NOT l_status then
3592: --
3593: l_error_index := l_error_index + 1;
3594: hr_utility.set_message(8302,'PQH_TCW_ADV_ERROR2');
3595: p_error_messages(l_error_index).message_text := hr_utility.get_message;
3596: --
3597: l_setup_status := FALSE;
3598: --
3599: End if;
3630: --
3631: --
3632: Begin
3633: --
3634: hr_utility.set_location('Entering'||l_proc, 5);
3635: --
3636: -- Obtain the routing style of the transcation category.
3637: --
3638: Open csr_member_cd(p_transaction_category_id => p_transaction_category_id);
3656: End if;
3657: --
3658: Close csr_hier;
3659: --
3660: hr_utility.set_location('Leaving'||l_proc, 10);
3661: --
3662: RETURN l_error_status;
3663: --
3664: END;
3676: --
3677: BEGIN
3678: --
3679: --
3680: hr_utility.set_location('Entering'||l_proc, 5);
3681: --
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.
3755: End if;
3756: --
3757: Execute immediate sql_stmt1 using 'N','Y',p_transaction_category_id;
3758: --
3759: hr_utility.set_location('Leaving'||l_proc, 10);
3760: --
3761: END delete_hierarchy_and_rules;
3762: ---------------------------------------------------------------------------------------------
3763: FUNCTION return_approver_status(p_routing_category_id in number,
3775: --
3776: BEGIN
3777: --
3778: --
3779: hr_utility.set_location('Entering'||l_proc, 5);
3780: --
3781: -- The following cursor selects the approver flag on all the rules for the
3782: -- approver under the passed routing category.
3783: --
3815: --
3816: End loop;
3817:
3818: --
3819: hr_utility.set_location('Leaving'||l_proc, 10);
3820: --
3821: RETURN 'N';
3822: --
3823: END return_approver_status;
3839: l_proc varchar2(72) := 'return_person_name';
3840: --
3841: BEGIN
3842: --
3843: hr_utility.set_location('Entering'||l_proc, 5);
3844: --
3845: Open csr_name;
3846: Fetch csr_name into l_name;
3847: Close csr_name;
3845: Open csr_name;
3846: Fetch csr_name into l_name;
3847: Close csr_name;
3848: --
3849: hr_utility.set_location('Leaving'||l_proc, 10);
3850: --
3851: RETURN l_name;
3852: --
3853: End return_person_name;