[Home] [Help]
60: l_freeze_status_cd varchar2(30);
61: begin
62: select freeze_status_cd,setup_type_cd
63: into l_freeze_status_cd,l_setup_type_cd
64: from pqh_transaction_categories
65: where transaction_category_id = p_tran_cat_id
66: and nvl(enable_flag,'Y') = 'Y';
67: if l_freeze_status_cd <>'FREEZE_CATEGORY' then
68: l_setup_type_cd := 'INCOMPLETE' ;
113: end;
114:
115: function get_transaction_category_name(p_transaction_category_id in number) return varchar2 is
116: cursor c1 is select name
117: from pqh_transaction_categories
118: where transaction_category_id = p_transaction_category_id;
119: l_transaction_category_name pqh_transaction_categories.name%type;
120: begin
121: open c1;
115: function get_transaction_category_name(p_transaction_category_id in number) return varchar2 is
116: cursor c1 is select name
117: from pqh_transaction_categories
118: where transaction_category_id = p_transaction_category_id;
119: l_transaction_category_name pqh_transaction_categories.name%type;
120: begin
121: open c1;
122: fetch c1 into l_transaction_category_name;
123: close c1;
162: p_transaction_category_id in number,
163: p_user_id in number,
164: p_person_id out nocopy number,
165: p_role_id out nocopy number) is
166: l_member_cd pqh_transaction_categories.member_cd%type;
167: l_position_id number;
168: l_person_name varchar2(240);
169: l_position_name hr_all_positions_f.name%type;
170: l_assignment_id number;
167: l_position_id number;
168: l_person_name varchar2(240);
169: l_position_name hr_all_positions_f.name%type;
170: l_assignment_id number;
171: l_workflow_enable_flag pqh_transaction_categories.workflow_enable_flag%type;
172: l_proc varchar2(256) := g_package||'get_default_role';
173: l_user_name fnd_user.user_name%type := fnd_profile.value('USERNAME');
174:
175: cursor c_get_employee(p_user_id number) is
178: where user_id = p_user_id;
179:
180: cursor c_get_txn_cat (p_transaction_category_id number) IS
181: select member_cd, workflow_enable_flag
182: from pqh_transaction_categories tct
183: where transaction_category_id = p_transaction_category_id;
184:
185: cursor c_get_assignment (p_user_id number) IS
186: select asg.assignment_id,asg.position_id
282: and transaction_category_id = p_transaction_category_id
283: and enable_flag = 'Y';
284: l_template_id pqh_role_templates.template_id%type;
285: l_role_name pqh_roles.role_name%type;
286: l_transaction_category_name pqh_transaction_categories.name%type;
287: begin
288: open c1;
289: fetch c1 into l_template_id;
290: if c1%notfound then
598: hr_utility.set_location('business_group_id'||p_business_group_id,13);
599: -- only local transaction category can be disabled
600: select transaction_category_id
601: into l_local_txncat_id
602: from pqh_transaction_categories
603: where business_group_id = nvl(p_business_group_id,-1)
604: and short_name = p_short_name
605: and nvl(enable_flag,'Y') = 'Y';
606: hr_utility.set_location('local tcat is'||l_local_txncat_id||l_proc,15);
611: begin
612: hr_utility.set_location('bg tcat does not exist'||l_proc,20);
613: select transaction_category_id
614: into l_global_txncat_id
615: from pqh_transaction_categories
616: where business_group_id is null
617: and short_name = p_short_name;
618: hr_utility.set_location('global tcat is'||l_global_txncat_id||l_proc,30);
619: return l_global_txncat_id ;
1031: exception when others then
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,
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,
1041: p_routing_list_id in pqh_routing_categories.routing_list_id%type,
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,
1139: p_assignment_id out nocopy per_all_assignments_f.assignment_id%type,
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,
1218: p_used_for in varchar2,
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,
1316: p_used_for in varchar2,
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,
1415: p_applicable_flag out nocopy boolean)
1538: raise;
1539: end person_has_user;
1540:
1541: procedure applicable_next_user(p_trans_id in pqh_routing_history.transaction_id%type,
1542: p_tran_cat_id in pqh_transaction_categories.transaction_category_id%type,
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,
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,
1552: p_person_id out nocopy fnd_user.employee_id%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,
1552: p_person_id out nocopy fnd_user.employee_id%type,
1553: p_old_member_id out nocopy pqh_routing_list_members.routing_list_member_id%type,
1823:
1824: procedure rl_member_check(p_routing_list_id in pqh_routing_lists.routing_list_id%type,
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,
1993: end rl_member_check ;
1994:
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,
2173: raise;
2174: end ps_element_check ;
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,
2424: return l_attribute_name;
2425: hr_utility.set_location('Exiting '||l_proc,10000);
2426: end;
2427:
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,
2426: end;
2427:
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
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;
2438: cursor c1 is
2439: select routing_list_id ,routing_category_id
2440: from pqh_routing_categories
2516: hr_utility.set_location('some error'||substr(sqlerrm,1,50),100);
2517: hr_utility.raise_error;
2518: end list_range_check;
2519:
2520: procedure list_range_check(p_tran_cat_id in pqh_transaction_categories.transaction_category_id%type,
2521: p_trans_id in pqh_routing_history.transaction_id%type,
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,
2520: procedure list_range_check(p_tran_cat_id in pqh_transaction_categories.transaction_category_id%type,
2521: p_trans_id in pqh_routing_history.transaction_id%type,
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,
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
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';
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' ;
2875: p_status_flag := null;
2876: raise;
2877: end list_range_check;
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,
3150: -- End of Added for Tar 4085705.996
3151:
3152:
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,
3389: p_can_approve := null;
3390: raise;
3391: end ps_element_applicable;
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,
3831: p_assignment_id := null;
3832: raise;
3833: end user_position_and_assignment ;
3834:
3835: procedure prepare_from_clause(p_tran_cat_id in pqh_transaction_categories.transaction_category_id%type,
3836: p_trans_id in pqh_routing_history.transaction_id%type,
3837: p_from_clause out nocopy pqh_table_route.from_clause%type ) as
3838: cursor c1 is select rou.from_clause,rou.where_clause
3839: from pqh_transaction_categories cat ,pqh_table_route rou
3835: procedure prepare_from_clause(p_tran_cat_id in pqh_transaction_categories.transaction_category_id%type,
3836: p_trans_id in pqh_routing_history.transaction_id%type,
3837: p_from_clause out nocopy pqh_table_route.from_clause%type ) as
3838: cursor c1 is select rou.from_clause,rou.where_clause
3839: from pqh_transaction_categories cat ,pqh_table_route rou
3840: where cat.transaction_category_id = p_tran_cat_id
3841: and cat.consolidated_table_route_id = rou.table_route_id ;
3842: l_from_clause pqh_table_route.from_clause%type ;
3843: l_where_clause_in pqh_table_route.where_clause%type ;
4357: p_member_flag := null;
4358: raise;
4359: end rlm_user_seq;
4360:
4361: procedure routing_current (p_tran_cat_id in pqh_transaction_categories.transaction_category_id%type,
4362: p_trans_id in pqh_routing_history.transaction_id%type,
4363: p_history_flag out nocopy boolean,
4364: p_old_member_cd out nocopy pqh_transaction_categories.member_cd%type,
4365: p_position_id out nocopy pqh_routing_history.forwarded_to_position_id%type,
4360:
4361: procedure routing_current (p_tran_cat_id in pqh_transaction_categories.transaction_category_id%type,
4362: p_trans_id in pqh_routing_history.transaction_id%type,
4363: p_history_flag out nocopy boolean,
4364: p_old_member_cd out nocopy pqh_transaction_categories.member_cd%type,
4365: p_position_id out nocopy pqh_routing_history.forwarded_to_position_id%type,
4366: p_member_id out nocopy pqh_routing_history.forwarded_to_member_id%type,
4367: p_role_id out nocopy number,
4368: p_user_id out nocopy number,
4617: l_session_date date;
4618:
4619: cursor c_tcat_details(p_transaction_category_id number) is
4620: select member_cd,freeze_status_cd,name
4621: from pqh_transaction_categories
4622: where transaction_category_id = p_transaction_category_id
4623: and nvl(enable_flag,'Y') = 'Y';
4624: cursor c_get_person_id(p_user_id number) is
4625: select employee_id