[Home] [Help]
775: p_position_id in number default null,
776: p_assignment_id in number default null
777: ) return boolean is
778: cursor c1 is select override_role_id,override_user_id,override_position_id,override_assignment_id
779: from pqh_routing_categories
780: where routing_category_id = p_routing_category_id
781: and nvl(enable_flag,'X') = 'Y'
782: and nvl(delete_flag,'N') = 'N';
783: l_proc varchar2(256) := g_package||'override_approver';
851: forwarded_by_member_id,routing_category_id
852: from pqh_routing_history
853: where routing_history_id = l_max_routing_history_id;
854: cursor c2 is select rlm.role_id
855: from pqh_routing_categories rc,pqh_routing_list_members rlm
856: where rc.routing_category_id = l_rh_routing_category_id
857: and rc.routing_list_id = rlm.routing_list_id
858: and rlm.routing_list_member_id = l_member_id
859: and nvl(rc.enable_flag,'X') = 'Y'
1032: p_old_approver_valid := null;
1033: raise;
1034: end old_approver_valid;
1035: procedure next_applicable(p_member_cd in pqh_transaction_categories.member_cd%type,
1036: p_routing_category_id in pqh_routing_categories.routing_category_id%type,
1037: p_tran_cat_id in pqh_transaction_categories.transaction_category_id%type,
1038: p_trans_id in pqh_routing_history.transaction_id%type,
1039: p_cur_assignment_id in per_all_assignments_f.assignment_id%type,
1040: p_cur_member_id in pqh_routing_list_members.routing_list_member_id%type,
1037: p_tran_cat_id in pqh_transaction_categories.transaction_category_id%type,
1038: p_trans_id in pqh_routing_history.transaction_id%type,
1039: p_cur_assignment_id in per_all_assignments_f.assignment_id%type,
1040: p_cur_member_id in pqh_routing_list_members.routing_list_member_id%type,
1041: p_routing_list_id in pqh_routing_categories.routing_list_id%type,
1042: p_cur_position_id in pqh_position_transactions.position_id%type,
1043: p_pos_str_ver_id in per_pos_structure_elements.pos_structure_version_id%type,
1044: p_next_position_id out nocopy pqh_position_transactions.position_id%type,
1045: p_next_member_id out nocopy pqh_routing_list_members.routing_list_member_id%type,
1130: p_next_assignment_id := null;
1131: p_status_flag := null;
1132: raise;
1133: end next_applicable;
1134: procedure next_applicable_assignment(p_routing_category_id in pqh_routing_categories.routing_category_id%type,
1135: p_tran_cat_id in pqh_transaction_categories.transaction_category_id%type,
1136: p_trans_id in pqh_routing_history.transaction_id%type,
1137: p_cur_assignment_id in per_all_assignments_f.assignment_id%type,
1138: p_used_for in varchar2,
1209:
1210: raise;
1211: end next_applicable_assignment;
1212:
1213: procedure next_applicable_member(p_routing_category_id in pqh_routing_categories.routing_category_id%type,
1214: p_tran_cat_id in pqh_transaction_categories.transaction_category_id%type,
1215: p_trans_id in pqh_routing_history.transaction_id%type,
1216: p_cur_member_id in pqh_routing_list_members.routing_list_member_id%type,
1217: p_routing_list_id in pqh_routing_categories.routing_list_id%type,
1213: procedure next_applicable_member(p_routing_category_id in pqh_routing_categories.routing_category_id%type,
1214: p_tran_cat_id in pqh_transaction_categories.transaction_category_id%type,
1215: p_trans_id in pqh_routing_history.transaction_id%type,
1216: p_cur_member_id in pqh_routing_list_members.routing_list_member_id%type,
1217: p_routing_list_id in pqh_routing_categories.routing_list_id%type,
1218: p_used_for in varchar2,
1219: p_member_id out nocopy pqh_routing_list_members.routing_list_member_id%type,
1220: p_role_id out nocopy number,
1221: p_user_id out nocopy number,
1307: p_applicable_flag := null;
1308: raise;
1309: end next_applicable_member;
1310:
1311: procedure next_applicable_position(p_routing_category_id in pqh_routing_categories.routing_category_id%type,
1312: p_tran_cat_id in pqh_transaction_categories.transaction_category_id%type,
1313: p_trans_id in pqh_routing_history.transaction_id%type,
1314: p_cur_position_id in pqh_position_transactions.position_id%type,
1315: p_pos_str_ver_id in per_pos_structure_elements.pos_structure_version_id%type,
1321: l_value_date date := trunc(sysdate);
1322: l_from_clause varchar2(2000) ;
1323: l_can_approve boolean := FALSE;
1324: l_error_flag boolean := FALSE;
1325: l_pos_str_id pqh_routing_categories.position_structure_id%type;
1326: l_cur_position_id pqh_position_transactions.position_id%type;
1327: l_proc varchar2(256) := g_package||'next_applicable_position' ;
1328: begin
1329: p_applicable_flag := FALSE;
1406: end next_applicable_position;
1407:
1408: procedure position_applicable(p_position_id in pqh_position_transactions.position_id%type,
1409: p_pos_str_ver_id in per_pos_structure_versions.pos_structure_version_id%type,
1410: p_routing_category_id in pqh_routing_categories.routing_category_id%type,
1411: p_tran_cat_id in pqh_transaction_categories.transaction_category_id%type,
1412: p_trans_id in pqh_position_transactions.position_transaction_id%type,
1413: p_status_flag out nocopy number,
1414: p_can_approve out nocopy boolean,
1543: p_cur_user_id in out nocopy fnd_user.user_id%type,
1544: p_cur_user_name in out nocopy fnd_user.user_name%type,
1545: p_user_active_role_id in out nocopy pqh_roles.role_id%type,
1546: p_user_active_role_name in out nocopy pqh_roles.role_name%type,
1547: p_routing_category_id out nocopy pqh_routing_categories.routing_category_id%type,
1548: p_member_cd out nocopy pqh_transaction_categories.member_cd%type,
1549: p_old_member_cd out nocopy pqh_transaction_categories.member_cd%type,
1550: p_routing_history_id out nocopy pqh_routing_history.routing_history_id%type,
1551: p_member_id out nocopy pqh_routing_list_members.routing_list_member_id%type,
1559: p_cur_member_id out nocopy pqh_routing_list_members.routing_list_member_id%type,
1560: p_position_id out nocopy pqh_position_transactions.position_id%type,
1561: p_old_position_id out nocopy pqh_position_transactions.position_id%type,
1562: p_cur_position_id out nocopy pqh_position_transactions.position_id%type,
1563: p_pos_str_id out nocopy pqh_routing_categories.position_structure_id%type,
1564: p_old_pos_str_id out nocopy pqh_routing_categories.position_structure_id%type,
1565: p_pos_str_ver_id out nocopy pqh_routing_history.pos_structure_version_id%type,
1566: p_old_pos_str_ver_id out nocopy pqh_routing_categories.position_structure_id%type,
1567: p_assignment_id out nocopy per_all_assignments_f.assignment_id%type,
1560: p_position_id out nocopy pqh_position_transactions.position_id%type,
1561: p_old_position_id out nocopy pqh_position_transactions.position_id%type,
1562: p_cur_position_id out nocopy pqh_position_transactions.position_id%type,
1563: p_pos_str_id out nocopy pqh_routing_categories.position_structure_id%type,
1564: p_old_pos_str_id out nocopy pqh_routing_categories.position_structure_id%type,
1565: p_pos_str_ver_id out nocopy pqh_routing_history.pos_structure_version_id%type,
1566: p_old_pos_str_ver_id out nocopy pqh_routing_categories.position_structure_id%type,
1567: p_assignment_id out nocopy per_all_assignments_f.assignment_id%type,
1568: p_cur_assignment_id out nocopy per_all_assignments_f.assignment_id%type,
1562: p_cur_position_id out nocopy pqh_position_transactions.position_id%type,
1563: p_pos_str_id out nocopy pqh_routing_categories.position_structure_id%type,
1564: p_old_pos_str_id out nocopy pqh_routing_categories.position_structure_id%type,
1565: p_pos_str_ver_id out nocopy pqh_routing_history.pos_structure_version_id%type,
1566: p_old_pos_str_ver_id out nocopy pqh_routing_categories.position_structure_id%type,
1567: p_assignment_id out nocopy per_all_assignments_f.assignment_id%type,
1568: p_cur_assignment_id out nocopy per_all_assignments_f.assignment_id%type,
1569: p_old_assignment_id out nocopy per_all_assignments_f.assignment_id%type,
1570: p_status_flag out nocopy number,
1825: p_old_routing_list_id in pqh_routing_lists.routing_list_id%type,
1826: p_history_flag in boolean,
1827: p_tran_cat_id in pqh_transaction_categories.transaction_category_id%type,
1828: p_from_clause in pqh_table_route.from_clause%type,
1829: p_routing_category_id in pqh_routing_categories.routing_category_id%type,
1830: p_old_member_id in pqh_routing_list_members.routing_list_member_id%type,
1831: p_old_user_id in number,
1832: p_old_role_id in number,
1833: p_user_active_role_id in out nocopy pqh_roles.role_id%type,
1995: procedure ps_element_check(p_history_flag in boolean,
1996: p_value_date in date,
1997: p_tran_cat_id in pqh_transaction_categories.transaction_category_id%type,
1998: p_from_clause in pqh_table_route.from_clause%type,
1999: p_routing_category_id in pqh_routing_categories.routing_category_id%type,
2000: p_old_position_id in pqh_position_transactions.position_id%type,
2001: p_pos_str_id in per_pos_structure_versions.position_structure_id%type,
2002: p_cur_user_id in out nocopy fnd_user.user_id%type,
2003: p_cur_user_name in out nocopy fnd_user.user_name%type,
2175:
2176: procedure assignment_check(p_history_flag in boolean,
2177: p_tran_cat_id in pqh_transaction_categories.transaction_category_id%type,
2178: p_from_clause in pqh_table_route.from_clause%type,
2179: p_routing_category_id in pqh_routing_categories.routing_category_id%type,
2180: p_old_assignment_id in per_all_assignments_f.assignment_id%type,
2181: p_value_date in date,
2182: p_cur_user_id in out nocopy fnd_user.user_id%type,
2183: p_cur_user_name in out nocopy fnd_user.user_name%type,
2428: procedure list_range_check(p_tran_cat_id in pqh_transaction_categories.transaction_category_id%type,
2429: p_used_for in varchar2 default null,
2430: p_member_cd out nocopy pqh_transaction_categories.member_cd%type,
2431: p_routing_list_id out nocopy pqh_routing_lists.routing_list_id%type,
2432: p_pos_str_id out nocopy pqh_routing_categories.position_structure_id%type,
2433: p_routing_category_id out nocopy pqh_routing_categories.routing_category_id%type,
2434: p_status_flag out nocopy number ) is
2435: cursor c0 is select member_cd
2436: from pqh_transaction_categories
2429: p_used_for in varchar2 default null,
2430: p_member_cd out nocopy pqh_transaction_categories.member_cd%type,
2431: p_routing_list_id out nocopy pqh_routing_lists.routing_list_id%type,
2432: p_pos_str_id out nocopy pqh_routing_categories.position_structure_id%type,
2433: p_routing_category_id out nocopy pqh_routing_categories.routing_category_id%type,
2434: p_status_flag out nocopy number ) is
2435: cursor c0 is select member_cd
2436: from pqh_transaction_categories
2437: where transaction_category_id = p_tran_cat_id;
2436: from pqh_transaction_categories
2437: where transaction_category_id = p_tran_cat_id;
2438: cursor c1 is
2439: select routing_list_id ,routing_category_id
2440: from pqh_routing_categories
2441: where transaction_category_id = p_tran_cat_id
2442: and nvl(enable_flag,'N') ='Y'
2443: and nvl(default_flag,'N') = 'Y'
2444: and nvl(delete_flag,'N') = 'N'
2445: and routing_list_id is not null;
2446:
2447: cursor c2 is
2448: select position_structure_id ,routing_category_id
2449: from pqh_routing_categories
2450: where transaction_category_id = p_tran_cat_id
2451: and nvl(enable_flag,'N') ='Y'
2452: and nvl(default_flag,'N') = 'Y'
2453: and nvl(delete_flag,'N') = 'N'
2454: and position_structure_id is not null;
2455:
2456: cursor c3 is
2457: select routing_category_id
2458: from pqh_routing_categories
2459: where transaction_category_id = p_tran_cat_id
2460: and nvl(enable_flag,'N') ='Y'
2461: and nvl(default_flag,'N') = 'Y'
2462: and nvl(delete_flag,'N') = 'N'
2522: p_from_clause in pqh_table_route.from_clause%type,
2523: p_used_for in varchar2 default null,
2524: p_member_cd out nocopy pqh_transaction_categories.member_cd%type,
2525: p_routing_list_id out nocopy pqh_routing_lists.routing_list_id%type,
2526: p_pos_str_id out nocopy pqh_routing_categories.position_structure_id%type,
2527: p_routing_category_id out nocopy pqh_routing_categories.routing_category_id%type,
2528: p_range_name out nocopy pqh_attribute_ranges.range_name%type,
2529: p_status_flag out nocopy number )
2530: as
2523: p_used_for in varchar2 default null,
2524: p_member_cd out nocopy pqh_transaction_categories.member_cd%type,
2525: p_routing_list_id out nocopy pqh_routing_lists.routing_list_id%type,
2526: p_pos_str_id out nocopy pqh_routing_categories.position_structure_id%type,
2527: p_routing_category_id out nocopy pqh_routing_categories.routing_category_id%type,
2528: p_range_name out nocopy pqh_attribute_ranges.range_name%type,
2529: p_status_flag out nocopy number )
2530: as
2531: cursor c1 is
2536: and ar.assignment_id is null
2537: and nvl(ar.enable_flag,'X') = 'Y'
2538: and nvl(ar.delete_flag,'N') = 'N'
2539: and routing_category_id in (select routing_category_id
2540: from pqh_routing_categories rc, pqh_transaction_categories tc
2541: where rc.transaction_category_id = p_tran_cat_id
2542: and tc.transaction_category_id = rc.transaction_category_id
2543: and ((tc.member_cd = 'R' and rc.routing_list_id is not null) or
2544: (tc.member_cd = 'P' and rc.Position_structure_id is not null) or
2555: and nvl(att.enable_flag,'X') = 'Y';
2556: cursor c3(p_attribute_id varchar2) is
2557: select ar.from_char,ar.to_char,ar.from_date,ar.to_date,ar.from_number,
2558: ar.to_number,rc.routing_category_id,ar.range_name
2559: from pqh_attribute_ranges ar,pqh_routing_categories rc
2560: where ar.attribute_id = p_attribute_id
2561: and ar.routing_category_id = rc.routing_category_id
2562: and rc.transaction_category_id = p_tran_cat_id
2563: and ar.routing_list_member_id is null
2569: and nvl(rc.enable_flag,'X') = 'Y'
2570: and nvl(rc.default_flag,'X') <> 'Y';
2571: cursor c4 is
2572: select tc.member_cd,rc.routing_list_id,rc.position_structure_id
2573: from pqh_routing_categories rc, pqh_transaction_categories tc
2574: where rc.routing_category_id = p_routing_category_id
2575: and tc.transaction_category_id = rc.transaction_category_id
2576: and nvl(rc.delete_flag,'N') = 'N'
2577: and nvl(rc.enable_flag,'X') = 'Y';
2583: l_attribute_name varchar2(2000);
2584: l_in_range boolean ;
2585: l_rule_cnt number ;
2586: type list_rec is record (
2587: routing_category_id pqh_routing_categories.routing_category_id%type,
2588: range_name pqh_attribute_ranges.range_name%type,
2589: selected_flag boolean) ;
2590: type list_tab is table of list_rec
2591: index by binary_integer;
2810: list_rout_crit;
2811: hr_utility.set_location('no range applicable'||p_status_flag||l_proc,192);
2812: -- check standard setup can be taken or not if yes then call it.
2813: select count(*) into l_standard_setup
2814: from pqh_routing_categories
2815: where transaction_category_id = p_tran_cat_id
2816: and nvl(default_flag,'X') ='Y'
2817: and nvl(delete_flag,'N') = 'N'
2818: and nvl(enable_flag,'X') = 'Y';
2837: else
2838: -- no ranges are defined.
2839: hr_utility.set_location('no range defined'||l_proc,210);
2840: select count(*) into l_rout_cat
2841: from pqh_routing_categories
2842: where transaction_category_id = p_tran_cat_id
2843: and nvl(delete_flag,'N') = 'N'
2844: and nvl(enable_flag,'X') = 'Y';
2845: if l_rout_cat =1 then
2848: g_list_range := '';
2849: begin
2850: select tc.member_cd,rc.routing_list_id,rc.position_structure_id,rc.routing_category_id
2851: into p_member_cd,p_routing_list_id,p_pos_str_id,p_routing_category_id
2852: from pqh_routing_categories rc, pqh_transaction_categories tc
2853: where tc.transaction_category_id = p_tran_cat_id
2854: and tc.transaction_category_id = rc.transaction_category_id
2855: and nvl(rc.enable_flag,'X') = 'Y'
2856: and nvl(rc.delete_flag,'N') = 'N' ;
2878:
2879: procedure assignment_applicable(p_tran_cat_id in pqh_transaction_categories.transaction_category_id%type,
2880: p_from_clause in pqh_table_route.from_clause%type,
2881: p_assignment_id in per_all_assignments_f.assignment_id%type,
2882: p_routing_category_id in pqh_routing_categories.routing_category_id%type,
2883: p_value_date in date,
2884: p_used_for in varchar2 default null,
2885: p_applicable_flag out nocopy boolean,
2886: p_status_flag out nocopy number,
3153:
3154: procedure ps_element_applicable(p_tran_cat_id in pqh_transaction_categories.transaction_category_id%type,
3155: p_from_clause in pqh_table_route.from_clause%type,
3156: p_position_id in pqh_position_transactions.position_id%type,
3157: p_routing_category_id in pqh_routing_categories.routing_category_id%type,
3158: p_value_date in date,
3159: p_used_for in varchar2 default null,
3160: p_applicable_flag out nocopy boolean,
3161: p_status_flag out nocopy number,
3392:
3393: procedure rl_member_applicable(p_tran_cat_id in pqh_transaction_categories.transaction_category_id%type,
3394: p_from_clause in pqh_table_route.from_clause%type,
3395: p_member_id in pqh_routing_list_members.routing_list_member_id%type,
3396: p_routing_category_id in pqh_routing_categories.routing_category_id%type,
3397: p_used_for in varchar2 default null,
3398: p_applicable_flag out nocopy boolean,
3399: p_status_flag out nocopy number,
3400: p_can_approve out nocopy boolean) as
4377: and transaction_id = p_trans_id
4378: and user_action_cd <> 'APPLY';
4379: cursor c2 is select rh.forwarded_to_position_id,rh.forwarded_to_role_id,rh.forwarded_to_user_id,rh.forwarded_to_member_id,
4380: rh.forwarded_to_assignment_id,rh.pos_structure_version_id,rc.routing_list_id
4381: from pqh_routing_history rh,pqh_routing_categories rc
4382: where routing_history_id = p_routing_history_id
4383: and rh.routing_category_id = rc.routing_category_id;
4384: l_error_flag boolean := FALSE;
4385: l_proc varchar2(256) := g_package||'routing_current';