1 PACKAGE hr_dynamic_approval_web AUTHID CURRENT_USER AS
2 /* $Header: hrdynapr.pkh 120.2.12010000.1 2008/07/28 03:13:49 appldev ship $ */
3
4 TYPE grt_wf_attributes IS RECORD (
5 dynamic_approval_mode varchar2 (200)
6 ,approval_level NUMBER
7 );
8
9
10 TYPE person_details IS RECORD (
11 full_name per_people_f.FULL_NAME%TYPE,
12 person_id per_people_f.person_id%TYPE,
13 job_title per_jobs.name%TYPE DEFAULT NULL,
14 default_approver VARCHAR2(10) DEFAULT 'N',
15 error_exists VARCHAR2(10) DEFAULT 'N'
16 );
17
18 TYPE t_person_table IS TABLE OF person_details INDEX BY BINARY_INTEGER;
19
20 TYPE approver_rec IS RECORD (
21 person_id per_people_f.person_id%TYPE,
22 default_approver VARCHAR2(10) DEFAULT 'N'
23 );
24 TYPE approver_rec_table IS TABLE OF approver_rec INDEX BY BINARY_INTEGER;
25
26 TYPE notifier_rec IS RECORD (
27 full_name per_people_f.FULL_NAME%TYPE,
28 person_id per_people_f.person_id%TYPE,
29 job_title per_jobs.name%TYPE DEFAULT NULL,
30 on_submit VARCHAR2(10) DEFAULT 'Y',
31 on_approval VARCHAR2(10) DEFAULT 'Y',
32 error_exists VARCHAR2(10) DEFAULT 'N'
33 );
34
35 TYPE notifier_rec_table IS TABLE OF notifier_rec INDEX BY BINARY_INTEGER;
36
37
38 TYPE ddl_record IS RECORD (
39 label hr_lookups.meaning%type,
40 code hr_lookups.lookup_code%type,
41 code_index NUMBER
42 );
43
44 TYPE ddl_data IS TABLE OF ddl_record INDEX BY BINARY_INTEGER;
45
46
47
48
49 PROCEDURE get_wf_attributes (
50 p_item_type in wf_items.item_type%TYPE
51 ,p_item_key in wf_items.item_key%TYPE
52 ,p_actid in number
53 );
54
55
56 PROCEDURE get_default_approvers(
57 p_approver_name OUT NOCOPY hr_util_misc_web.g_varchar2_tab_type,
58 p_approver_flag OUT NOCOPY hr_util_misc_web.g_varchar2_tab_type,
59 p_item_type IN wf_items.item_type%TYPE,
60 p_item_key IN wf_items.item_key%TYPE
61 );
62
63 PROCEDURE get_all_approvers(p_approver_name hr_util_misc_web.g_varchar2_tab_type
64 DEFAULT hr_util_misc_web.g_varchar2_tab_default,
65 p_approver_flag hr_util_misc_web.g_varchar2_tab_type
66 DEFAULT hr_util_misc_web.g_varchar2_tab_default,
67 p_item_type IN wf_items.item_type%TYPE,
68 p_item_key IN wf_items.item_key%TYPE,
69 p_effective_date IN DATE DEFAULT SYSDATE);
70
71
72
73 FUNCTION build_ddl(p_approver_name hr_util_misc_web.g_varchar2_tab_type
74 DEFAULT hr_util_misc_web.g_varchar2_tab_default,
75 p_approver_flag hr_util_misc_web.g_varchar2_tab_type
76 DEFAULT hr_util_misc_web.g_varchar2_tab_default,
77 p_item_type IN wf_items.item_type%TYPE,
78 p_item_key IN wf_items.item_key%TYPE ,
79 p_variable_name in varchar2,
80 p_variable_value in varchar2 DEFAULT NULL
81 ,p_attributes IN VARCHAR2 DEFAULT NULL) RETURN LONG ;
82
83 PROCEDURE add_approver(p_approver_name IN OUT NOCOPY hr_util_misc_web.g_varchar2_tab_type,
84 p_approver_flag IN OUT NOCOPY hr_util_misc_web.g_varchar2_tab_type,
85 p_item_type IN wf_items.item_type%TYPE,
86 p_item_key IN wf_items.item_key%TYPE,
87 p_approver_index IN NUMBER DEFAULT 0);
88
89 PROCEDURE delete_approver(p_approver_name IN OUT NOCOPY hr_util_misc_web.g_varchar2_tab_type,
90 p_approver_flag IN OUT NOCOPY hr_util_misc_web.g_varchar2_tab_type,
91 p_item_type IN wf_items.item_type%TYPE,
92 p_item_key IN wf_items.item_key%TYPE,
93 p_approver_index IN NUMBER DEFAULT 1);
94
95
96
97 PROCEDURE add_notifier(
98 p_notifier_name IN OUT NOCOPY hr_util_misc_web.g_varchar2_tab_type,
99 p_notify_onsubmit_flag IN OUT NOCOPY hr_util_misc_web.g_varchar2_tab_type,
100 p_notify_onapproval_flag IN OUT NOCOPY hr_util_misc_web.g_varchar2_tab_type,
101 p_item_type IN wf_items.item_type%TYPE
102 ,p_item_key IN wf_items.item_key%TYPE
103 ,P_PERSON_NAME IN per_all_people_f.full_name%TYPE
104 ,p_person_id IN per_all_people_f.person_id%TYPE
105 );
106
107
108
109 PROCEDURE Get_all_notifiers(
110 p_notifier_name IN hr_util_misc_web.g_varchar2_tab_type,
111 p_notify_onsubmit_flag IN hr_util_misc_web.g_varchar2_tab_type,
112 p_notify_onapproval_flag IN hr_util_misc_web.g_varchar2_tab_type,
113 p_item_type IN wf_items.item_type%TYPE,
114 p_item_key IN wf_items.item_key%TYPE,
115 p_effective_date IN DATE
116 );
117
118
119
120 PROCEDURE update_notifiers(
121 p_item_type IN WF_ITEMS.ITEM_TYPE%TYPE ,
122 p_item_key IN WF_ITEMS.ITEM_KEY%TYPE ,
123 p_act_id IN NUMBER ,
124 p_notifiers_num IN NUMBER DEFAULT 0,
125 p_Notify_On_Submit hr_util_misc_web.g_varchar2_tab_type DEFAULT
126 hr_util_misc_web.g_varchar2_tab_default,
127 p_Notify_On_Approval hr_util_misc_web.g_varchar2_tab_type DEFAULT
128 hr_util_misc_web.g_varchar2_tab_default
129
130 );
131
132 PROCEDURE clean_invalid_data( p_item_type IN WF_ITEMS.ITEM_TYPE%TYPE ,
133 p_item_key IN WF_ITEMS.ITEM_KEY%TYPE ,
134 p_act_id IN NUMBER ,
135 p_approvers_name IN hr_util_misc_web.g_varchar2_tab_type
136 );
137
138
139 --
140 -- ------------------------------------------------------------------------
141 -- |------------------------< Get_next_approver >-------------------------|
142 -- ------------------------------------------------------------------------
143 --
144 -- Description
145 --
146 -- Get the next approver in the chain
147 --
148 --
149 procedure Get_Next_Approver ( itemtype in varchar2,
150 itemkey in varchar2,
151 actid in number,
152 funmode in varchar2,
153 result out nocopy varchar2 );
154
155
156
157 -- ------------------------------------------------------------------------
158 -- |----------------------< Check_Final_Approver >-------------------------|
159 -- ------------------------------------------------------------------------
160 --
161 -- Description
162 --
163 -- Determine if this person is the final manager in the approval chain
164 --
165 --
166 procedure Check_Final_Approver( p_item_type in varchar2,
167 p_item_key in varchar2,
168 p_act_id in number,
169 funmode in varchar2,
170 result out nocopy varchar2 );
171
172 -- ------------------------------------------------------------------------
173 -- |----------------------< Check_Final_Notifier >-------------------------|
174 -- ------------------------------------------------------------------------
175 --
176 -- Description
177 --
178 -- Determine if this person is the final manager in the approval chain
179 --
180 --
181 procedure Check_OnSubmit_Notifier( itemtype in varchar2,
182 itemkey in varchar2,
183 actid in number,
184 funmode in varchar2,
185 result out nocopy varchar2 );
186
187 procedure Check_OnApproval_Notifier( itemtype in varchar2,
188 itemkey in varchar2,
189 actid in number,
190 funmode in varchar2,
191 result out nocopy varchar2 );
192
193 --
194 -- ------------------------------------------------------------------------
195 -- |------------------------< Get_OnSubmit_notifier >-------------------------|
196 -- ------------------------------------------------------------------------
197 --
198 -- Description
199 --
200 -- Get the next notifier in the chain
201 --
202 --
203 procedure Get_OnSubmit_Notifier ( itemtype in varchar2,
204 itemkey in varchar2,
205 actid in number,
206 funmode in varchar2,
207 result out nocopy varchar2 );
208
209 --
210 -- ------------------------------------------------------------------------
211 -- |------------------------< Get_OnApproval_notifier >-------------------------|
212 -- ------------------------------------------------------------------------
213 --
214 -- Description
215 --
216 -- Get the next notifier in the chain
217 --
218 --
219 procedure Get_OnApproval_Notifier ( itemtype in varchar2,
220 itemkey in varchar2,
221 actid in number,
222 funmode in varchar2,
223 result out nocopy varchar2 );
224
225
226
227 procedure set_first_onapproval_person
228 (itemtype in varchar2
229 ,itemkey in varchar2
230 ,actid in number
231 ,funmode in varchar2
232 ,result out nocopy varchar2);
233
234 procedure set_first_onsubmit_person
235 (itemtype in varchar2
236 ,itemkey in varchar2
237 ,actid in number
238 ,funmode in varchar2
239 ,result out nocopy varchar2);
240
241
242 procedure initialize_item_attributes
243 (itemtype in varchar2
244 ,itemkey in varchar2
245 ,actid in number
246 ,funmode in varchar2
247 ,result out nocopy varchar2);
248
249
250
251 procedure Notify(itemtype in varchar2,
252 itemkey in varchar2,
253 actid in number,
254 funcmode in varchar2,
255 resultout in out nocopy varchar2);
256
257
258 /*-----------------------------------------------------------------------
259
260 || PROCEDURE : get_default_approvers_list
261 ||
262 || This is a wrapper procedure to get_default_approvers to return
263 || the list of default approvers to a java oracle.sql.ARRAY object
264 ||
265 ||
266 ||
267 ||-----------------------------------------------------------------------*/
268
269 PROCEDURE get_default_approvers_list(
270 p_item_type IN wf_items.item_type%TYPE,
271 p_item_key IN wf_items.item_key%TYPE,
272 p_default_approvers_list OUT NOCOPY hr_dynamic_approver_list_ss);
273
274 PROCEDURE get_default_approvers_list(
275 p_item_type IN wf_items.item_type%TYPE,
276 p_item_key IN wf_items.item_key%TYPE,
277 p_default_approvers_list OUT NOCOPY hr_dynamic_approver_list_ss,
278 p_error_message OUT NOCOPY varchar);
279
280
281 /*-----------------------------------------------------------------------
282
283 || PROCEDURE : get_ame_approvers_list
284 ||
285 || This is a wrapper procedure to get_default_approvers to return
286 || the list of default approvers to a java oracle.sql.ARRAY object
287 ||
288 ||
289 ||
290 ||-----------------------------------------------------------------------*/
291 PROCEDURE get_ame_approvers_list(
292 p_item_type IN wf_items.item_type%TYPE,
293 p_item_key IN wf_items.item_key%TYPE,
294 p_default_approvers_list OUT NOCOPY hr_dynamic_approver_list_ss);
295
296
297 /*-----------------------------------------------------------------------
298
299 || PROCEDURE : set_ame_approvers_list
300 ||
301 || This is a wrapper procedure to get_default_approvers to update
302 || the list of default approvers to a java oracle.sql.ARRAY object
303 ||
304 ||
305 ||
306 ||-----------------------------------------------------------------------*/
307
308 PROCEDURE set_ame_approvers_list(
309 p_item_type IN wf_items.item_type%TYPE,
310 p_item_key IN wf_items.item_key%TYPE,
311 p_default_approvers_list IN hr_dynamic_approver_list_ss);
312
313 /*-----------------------------------------------------------------------
314
315 || PROCEDURE : get_additional_notifiers_list
316 ||
317 || This is a wrapper procedure to get_default_approvers to return
318 || the list of default approvers to a java oracle.sql.ARRAY object
319 ||
320 ||
321 ||
322 ||-----------------------------------------------------------------------*/
323
324 PROCEDURE get_additional_notifiers_list(
325 p_item_type IN wf_items.item_type%TYPE,
326 p_item_key IN wf_items.item_key%TYPE,
327 p_additional_notifiers_list OUT NOCOPY hr_dynamic_approver_list_ss);
328
329
330 end hr_dynamic_approval_web; -- Package spec