1 package pqh_workflow AUTHID CURRENT_USER as
2 /* $Header: pqwrkflw.pkh 120.0.12010000.1 2008/07/28 13:19:08 appldev ship $ */
3 type t_attid_ranges is record (
4 Attribute_id pqh_attributes.attribute_id%type,
5 rule_name varchar2(240),
6 attribute_type varchar2(1),
7 from_char varchar2(240),
8 to_char varchar2(240),
9 from_num number(15,0),
10 to_num number(15,0),
11 from_date date,
12 to_date date,
13 value_num number(15,0),
14 value_char varchar2(240),
15 value_date date,
16 used_for varchar2(30) );
17
18 type t_attribute_ranges is table of t_attid_ranges
19 index by binary_integer ;
20
21 -- global variable to hold the result and return row one by one
22 g_routing_criterion t_attribute_ranges;
23 g_list_range pqh_attribute_ranges.range_name%type;
24 g_current_member_range pqh_attribute_ranges.range_name%type;
25 g_next_member_range pqh_attribute_ranges.range_name%type;
26
27 -- function get_routinghistory_role(p_routing_history_id number) return number ;
28
29 procedure get_primary_asg_details(p_person_id in number,
30 p_effective_date in date,
31 p_assignment_id out nocopy number,
32 p_position_id out nocopy number) ;
33
34 procedure next_applicable(p_member_cd in pqh_transaction_categories.member_cd%type,
35 p_routing_category_id in pqh_routing_categories.routing_category_id%type,
36 p_tran_cat_id in pqh_transaction_categories.transaction_category_id%type,
37 p_trans_id in pqh_routing_history.transaction_id%type,
38 p_cur_assignment_id in per_all_assignments_f.assignment_id%type,
39 p_cur_member_id in pqh_routing_list_members.routing_list_member_id%type,
40 p_routing_list_id in pqh_routing_categories.routing_list_id%type,
41 p_cur_position_id in pqh_position_transactions.position_id%type,
42 p_pos_str_ver_id in per_pos_structure_elements.pos_structure_version_id%type,
43 p_next_position_id out nocopy pqh_position_transactions.position_id%type,
44 p_next_member_id out nocopy pqh_routing_list_members.routing_list_member_id%type,
45 p_next_role_id out nocopy number,
46 p_next_user_id out nocopy number,
47 p_next_assignment_id out nocopy per_all_assignments_f.assignment_id%type,
48 p_status_flag out nocopy number);
49
50 procedure old_approver_valid(p_transaction_category_id in number,
51 p_transaction_id in number,
52 p_transaction_status in varchar2,
53 p_old_approver_valid out nocopy varchar2 ) ;
54 procedure next_applicable_member(p_routing_category_id in pqh_routing_categories.routing_category_id%type,
55 p_tran_cat_id in pqh_transaction_categories.transaction_category_id%type,
56 p_trans_id in pqh_routing_history.transaction_id%type,
57 p_cur_member_id in pqh_routing_list_members.routing_list_member_id%type,
58 p_routing_list_id in pqh_routing_categories.routing_list_id%type,
59 p_used_for in varchar2,
60 p_member_id out nocopy pqh_routing_list_members.routing_list_member_id%type,
61 p_role_id out nocopy number,
62 p_user_id out nocopy number,
63 p_status_flag out nocopy number,
64 p_applicable_flag out nocopy boolean) ;
65
66 procedure next_applicable_assignment(p_routing_category_id in pqh_routing_categories.routing_category_id%type,
67 p_tran_cat_id in pqh_transaction_categories.transaction_category_id%type,
68 p_trans_id in pqh_routing_history.transaction_id%type,
69 p_cur_assignment_id in per_all_assignments_f.assignment_id%type,
70 p_used_for in varchar2,
71 p_assignment_id out nocopy per_all_assignments_f.assignment_id%type,
72 p_status_flag out nocopy number,
73 p_applicable_flag out nocopy boolean);
74
75 procedure next_applicable_position(p_routing_category_id in pqh_routing_categories.routing_category_id%type,
76 p_tran_cat_id in pqh_transaction_categories.transaction_category_id%type,
77 p_trans_id in pqh_routing_history.transaction_id%type,
78 p_cur_position_id in pqh_position_transactions.position_id%type,
79 p_pos_str_ver_id in per_pos_structure_elements.pos_structure_version_id%type,
80 p_used_for in varchar2,
81 p_position_id out nocopy pqh_position_transactions.position_id%type,
82 p_status_flag out nocopy number,
83 p_applicable_flag out nocopy boolean) ;
84
85 procedure position_applicable(p_position_id in pqh_position_transactions.position_id%type,
86 p_pos_str_ver_id in per_pos_structure_versions.pos_structure_version_id%type,
87 p_routing_category_id in pqh_routing_categories.routing_category_id%type,
88 p_tran_cat_id in pqh_transaction_categories.transaction_category_id%type,
89 p_trans_id in pqh_position_transactions.position_transaction_id%type,
90 p_status_flag out nocopy number,
91 p_can_approve out nocopy boolean,
92 p_applicable_flag out nocopy boolean);
93
94 procedure position_occupied(p_position_id in pqh_position_transactions.position_id%type,
95 p_value_date in date,
96 p_applicable_flag out nocopy boolean);
97
98 procedure person_has_user(p_person_id in fnd_user.employee_id%type,
99 p_value_date in date,
100 p_applicable_flag out nocopy boolean);
101
102 procedure person_on_assignment(p_assignment_id in per_all_assignments_f.assignment_id%type,
103 p_value_date in date,
104 p_person_id out nocopy fnd_user.employee_id%type ) ;
105
106 procedure applicable_next_user(p_trans_id in pqh_routing_history.transaction_id%type,
107 p_tran_cat_id in pqh_transaction_categories.transaction_category_id%type,
108 p_cur_user_id in out nocopy fnd_user.user_id%type,
109 p_cur_user_name in out nocopy fnd_user.user_name%type,
110 p_user_active_role_id in out nocopy pqh_roles.role_id%type,
111 p_user_active_role_name in out nocopy pqh_roles.role_name%type,
112 p_routing_category_id out nocopy pqh_routing_categories.routing_category_id%type,
113 p_member_cd out nocopy pqh_transaction_categories.member_cd%type,
114 p_old_member_cd out nocopy pqh_transaction_categories.member_cd%type,
115 p_routing_history_id out nocopy pqh_routing_history.routing_history_id%type,
116 p_member_id out nocopy pqh_routing_list_members.routing_list_member_id%type,
117 p_person_id out nocopy fnd_user.employee_id%type,
118 p_old_member_id out nocopy pqh_routing_list_members.routing_list_member_id%type,
119 p_routing_list_id out nocopy pqh_routing_lists.routing_list_id%type,
120 p_old_routing_list_id out nocopy pqh_routing_lists.routing_list_id%type,
121 p_member_role_id out nocopy pqh_roles.role_id%type,
122 p_member_user_id out nocopy fnd_user.user_id%type,
123 p_cur_person_id out nocopy fnd_user.employee_id%type,
124 p_cur_member_id out nocopy pqh_routing_list_members.routing_list_member_id%type,
125 p_position_id out nocopy pqh_position_transactions.position_id%type,
126 p_old_position_id out nocopy pqh_position_transactions.position_id%type,
127 p_cur_position_id out nocopy pqh_position_transactions.position_id%type,
128 p_pos_str_id out nocopy pqh_routing_categories.position_structure_id%type,
129 p_old_pos_str_id out nocopy pqh_routing_categories.position_structure_id%type,
130 p_pos_str_ver_id out nocopy pqh_routing_history.pos_structure_version_id%type,
131 p_old_pos_str_ver_id out nocopy pqh_routing_categories.position_structure_id%type,
132 p_assignment_id out nocopy per_all_assignments_f.assignment_id%type,
133 p_cur_assignment_id out nocopy per_all_assignments_f.assignment_id%type,
134 p_old_assignment_id out nocopy per_all_assignments_f.assignment_id%type,
135 p_status_flag out nocopy number,
136 p_history_flag out nocopy boolean,
137 p_range_name out nocopy pqh_attribute_ranges.range_name%type,
138 p_can_approve out nocopy boolean);
139
140 procedure rl_member_check(p_routing_list_id in pqh_routing_lists.routing_list_id%type,
141 p_old_routing_list_id in pqh_routing_lists.routing_list_id%type,
142 p_history_flag in boolean,
143 p_tran_cat_id in pqh_transaction_categories.transaction_category_id%type,
144 p_from_clause in pqh_table_route.from_clause%type,
145 p_routing_category_id in pqh_routing_categories.routing_category_id%type,
146 p_old_member_id in pqh_routing_list_members.routing_list_member_id%type,
147 p_old_user_id in number,
148 p_old_role_id in number,
149 p_user_active_role_id in out nocopy pqh_roles.role_id%type,
150 p_user_active_role_name in out nocopy pqh_roles.role_name%type,
151 p_cur_user_id in out nocopy fnd_user.user_id%type,
152 p_cur_user_name in out nocopy fnd_user.user_name%type,
153 p_cur_member_id out nocopy pqh_routing_list_members.routing_list_member_id%type,
154 p_member_id out nocopy pqh_routing_list_members.routing_list_member_id%type,
155 p_member_role_id out nocopy pqh_routing_list_members.role_id%type,
156 p_member_user_id out nocopy pqh_routing_list_members.user_id%type,
157 p_status_flag out nocopy number,
158 p_applicable_flag out nocopy boolean,
159 p_old_can_approve out nocopy boolean,
160 p_can_approve out nocopy boolean );
161
162 procedure ps_element_check(p_history_flag in boolean,
163 p_value_date in date,
164 p_tran_cat_id in pqh_transaction_categories.transaction_category_id%type,
165 p_from_clause in pqh_table_route.from_clause%type,
166 p_routing_category_id in pqh_routing_categories.routing_category_id%type,
167 p_old_position_id in pqh_position_transactions.position_id%type,
168 p_pos_str_id in per_pos_structure_versions.position_structure_id%type,
169 p_cur_user_id in out nocopy fnd_user.user_id%type,
170 p_cur_user_name in out nocopy fnd_user.user_name%type,
171 p_pos_str_ver_id out nocopy per_pos_structure_elements.pos_structure_version_id%type,
172 p_cur_position_id out nocopy per_all_assignments_f.position_id%type,
173 p_cur_person_id out nocopy fnd_user.employee_id%type,
174 p_cur_assignment_id out nocopy per_all_assignments_f.assignment_id%type,
175 p_old_pos_str_id out nocopy per_pos_structure_versions.position_structure_id%type,
176 p_position_id out nocopy pqh_position_transactions.position_id%type,
177 p_status_flag out nocopy number,
178 p_can_approve out nocopy boolean,
179 p_old_can_approve out nocopy boolean,
180 p_applicable_flag out nocopy boolean );
181
182 procedure assignment_check(p_history_flag in boolean,
183 p_tran_cat_id in pqh_transaction_categories.transaction_category_id%type,
184 p_from_clause in pqh_table_route.from_clause%type,
185 p_routing_category_id in pqh_routing_categories.routing_category_id%type,
186 p_old_assignment_id in per_all_assignments_f.assignment_id%type,
187 p_value_date in date,
188 p_cur_user_id in out nocopy fnd_user.user_id%type,
189 p_cur_user_name in out nocopy fnd_user.user_name%type,
190 p_cur_person_id out nocopy fnd_user.employee_id%type,
191 p_assignment_id out nocopy per_all_assignments_f.assignment_id%type,
192 p_person_id out nocopy per_all_assignments_f.person_id%type,
193 p_status_flag out nocopy number,
194 p_cur_assignment_id out nocopy per_all_assignments_f.assignment_id%type,
195 p_old_can_approve out nocopy boolean,
196 p_can_approve out nocopy boolean,
197 p_applicable_flag out nocopy boolean );
198
199 procedure list_range_check(p_tran_cat_id in pqh_transaction_categories.transaction_category_id%type,
200 p_used_for in varchar2 default null,
201 p_member_cd out nocopy pqh_transaction_categories.member_cd%type,
202 p_routing_list_id out nocopy pqh_routing_lists.routing_list_id%type,
203 p_pos_str_id out nocopy pqh_routing_categories.position_structure_id%type,
204 p_routing_category_id out nocopy pqh_routing_categories.routing_category_id%type,
205 p_status_flag out nocopy number ) ;
206
207 procedure list_range_check(p_tran_cat_id in pqh_transaction_categories.transaction_category_id%type,
208 p_trans_id in pqh_routing_history.transaction_id%type,
209 p_from_clause in pqh_table_route.from_clause%type,
210 p_used_for in varchar2 default null,
211 p_member_cd out nocopy pqh_transaction_categories.member_cd%type,
212 p_routing_list_id out nocopy pqh_routing_lists.routing_list_id%type,
213 p_pos_str_id out nocopy pqh_routing_categories.position_structure_id%type,
214 p_routing_category_id out nocopy pqh_routing_categories.routing_category_id%type,
215 p_range_name out nocopy pqh_attribute_ranges.range_name%type,
216 p_status_flag out nocopy number );
217
221 p_routing_category_id in pqh_routing_categories.routing_category_id%type,
218 procedure assignment_applicable(p_tran_cat_id in pqh_transaction_categories.transaction_category_id%type,
219 p_from_clause in pqh_table_route.from_clause%type,
220 p_assignment_id in per_all_assignments_f.assignment_id%type,
222 p_value_date in date,
223 p_used_for in varchar2 default null,
224 p_applicable_flag out nocopy boolean,
225 p_status_flag out nocopy number,
226 p_can_approve out nocopy boolean);
227
228 procedure ps_element_applicable(p_tran_cat_id in pqh_transaction_categories.transaction_category_id%type,
229 p_from_clause in pqh_table_route.from_clause%type,
230 p_position_id in pqh_position_transactions.position_id%type,
231 p_routing_category_id in pqh_routing_categories.routing_category_id%type,
232 p_value_date in date,
233 p_used_for in varchar2 default null,
234 p_applicable_flag out nocopy boolean,
235 p_status_flag out nocopy number,
236 p_can_approve out nocopy boolean);
237
238 procedure rl_member_applicable(p_tran_cat_id in pqh_transaction_categories.transaction_category_id%type,
239 p_from_clause in pqh_table_route.from_clause%type,
240 p_member_id in pqh_routing_list_members.routing_list_member_id%type,
241 p_routing_category_id in pqh_routing_categories.routing_category_id%type,
242 p_used_for in varchar2 default null,
243 p_applicable_flag out nocopy boolean,
244 p_status_flag out nocopy number,
245 p_can_approve out nocopy boolean) ;
246
247 procedure su_next_user(p_cur_assignment_id in number,
248 p_value_date in date,
249 p_assignment_id out nocopy per_all_assignments_f.assignment_id%type,
250 p_status_flag out nocopy number);
251
252 procedure user_assignment(p_value_date in date,
253 p_user_id in out nocopy fnd_user.user_id%type,
254 p_user_name in out nocopy fnd_user.user_name%type,
255 p_person_id out nocopy fnd_user.employee_id%type,
256 p_assignment_id out nocopy per_all_assignments_f.assignment_id%type);
257 procedure user_position_and_assignment(p_value_date in date,
258 p_user_id in out nocopy fnd_user.user_id%type,
259 p_user_name in out nocopy fnd_user.user_name%type,
260 p_person_id out nocopy fnd_user.employee_id%type,
261 p_position_id out nocopy pqh_position_transactions.position_id%type,
262 p_assignment_id out nocopy per_all_assignments_f.assignment_id%type);
263
264 procedure prepare_from_clause(p_tran_cat_id in pqh_transaction_categories.transaction_category_id%type,
265 p_trans_id in pqh_routing_history.transaction_id%type,
266 p_from_clause out nocopy pqh_table_route.from_clause%type );
267
268 procedure check_value_range(p_from_char in pqh_attribute_ranges.from_char%type,
269 p_to_char in pqh_attribute_ranges.to_char%type,
270 p_value_char in pqh_attribute_ranges.to_char%type,
271 p_in_range out nocopy boolean ,
272 p_can_approve out nocopy boolean ) ;
273
274 procedure check_value_range(p_from_num in pqh_attribute_ranges.from_number%type,
275 p_to_num in pqh_attribute_ranges.to_number%type,
276 p_value_num in pqh_attribute_ranges.to_number%type,
277 p_in_range out nocopy boolean ,
278 p_can_approve out nocopy boolean ) ;
279
280 procedure check_value_range(p_from_date in pqh_attribute_ranges.from_date%type,
281 p_to_date in pqh_attribute_ranges.to_date%type,
282 p_value_date in pqh_attribute_ranges.to_date%type,
283 p_in_range out nocopy boolean ,
284 p_can_approve out nocopy boolean ) ;
285
286 procedure rlm_user_seq(p_routing_list_id in pqh_routing_lists.routing_list_id%type,
287 p_old_user_id in number default null,
288 p_old_role_id in number default null,
289 p_old_member_id in number default null,
290 p_role_id in out nocopy pqh_roles.role_id%type,
291 p_role_name in out nocopy pqh_roles.role_name%type,
292 p_user_id in out nocopy fnd_user.user_id%type,
293 p_user_name in out nocopy fnd_user.user_name%type,
294 p_member_id out nocopy pqh_routing_list_members.routing_list_member_id%type,
295 p_member_flag out nocopy boolean) ;
296
300 p_old_member_cd out nocopy pqh_transaction_categories.member_cd%type,
297 procedure routing_current(p_tran_cat_id in pqh_transaction_categories.transaction_category_id%type,
298 p_trans_id in pqh_routing_history.transaction_id%type,
299 p_history_flag out nocopy boolean,
301 p_position_id out nocopy pqh_routing_history.forwarded_to_position_id%type,
302 p_member_id out nocopy pqh_routing_history.forwarded_to_member_id%type,
303 p_role_id out nocopy number,
304 p_user_id out nocopy number,
305 p_assignment_id out nocopy pqh_routing_history.forwarded_to_assignment_id%type,
306 p_pos_str_ver_id out nocopy pqh_routing_history.pos_structure_version_id%type,
307 p_routing_list_id out nocopy pqh_routing_lists.routing_list_id%type,
308 p_routing_history_id out nocopy pqh_routing_history.routing_history_id%type,
309 p_status_flag out nocopy number) ;
310
311 procedure rl_next_user (p_routing_list_id in pqh_routing_list_members.routing_list_id%type,
312 p_cur_member_id in pqh_routing_list_members.routing_list_member_id%type,
313 p_member_id out nocopy pqh_routing_list_members.routing_list_member_id%type,
314 p_role_id out nocopy pqh_routing_list_members.role_id%type,
315 p_user_id out nocopy pqh_routing_list_members.user_id%type,
316 p_status_flag out nocopy number) ;
317
318 procedure ph_next_user(p_cur_position_id in pqh_position_transactions.position_id%type,
319 p_pos_str_ver_id in pqh_routing_history.pos_structure_version_id%type,
320 p_position_id out nocopy pqh_position_transactions.position_id%type,
321 p_status_flag out nocopy number ) ;
322
323 function pos_str_version(p_pos_str_id in per_pos_structure_versions.position_structure_id%type) return number;
324
325 function find_pos_structure(p_pos_str_ver_id in per_pos_structure_versions.pos_structure_version_id%type) return number;
326
327 function get_txn_cat(p_short_name in varchar2,
328 p_business_group_id in number default null) return number ;
329 procedure get_role_user(p_member_id in number,
330 p_role_id out nocopy number,
331 p_user_id out nocopy number ) ;
332 procedure valid_user_opening(p_business_group_id in number default null,
333 p_short_name in varchar2 ,
334 p_transaction_id in number default null,
335 p_routing_history_id in number default null,
336 p_wf_transaction_category_id out nocopy number,
337 p_glb_transaction_category_id out nocopy number,
338 p_role_id out nocopy number,
339 p_role_template_id out nocopy number,
340 p_status_flag out nocopy varchar2);
341 function get_user_default_role(p_user_id in number)
342 return Number;
343 --
344 end pqh_workflow;
[Home] [Help]