1 PACKAGE PO_APPROVALLIST_S1 AS
2 /* $Header: POXAPL1S.pls 120.1 2006/06/28 06:32:14 mkohale noship $*/
3
4
5 -- Record to hold the approval list elements
6 TYPE ApprovalListEltType IS RECORD (
7 id NUMBER, -- unique identifier
8 sequence_num NUMBER, -- sequence number
9 approver_id NUMBER, -- approver employee id
10 approver_disp_name VARCHAR2(240), -- approver display name
11 responder_id NUMBER, -- responder employee id
12 responder_disp_name VARCHAR2(240), -- responder display name
13 forward_to_id NUMBER, -- forward-to employee id
14 forward_to_disp_name VARCHAR2(240), -- forward-to display name
15 status VARCHAR2(30), -- status of approver: APPROVE, FORWARD,
16 -- APPROVE_AND_FORWARD, REJECT, PENDING,
17 -- NULL
18 response_date DATE,
19 approver_type VARCHAR2(30), -- type of approver: SYSTEM, USER, FORWARD
20 mandatory_flag VARCHAR2(1) -- Y: approver is mandatory
21 -- N: approver is not mandatory
22 );
23
24
25 -- Table of ApprovalListEltType records
26 TYPE ApprovalListType IS TABLE OF ApprovalListEltType INDEX BY BINARY_INTEGER;
27
28 -- Record to hold the error stack entries
29 TYPE ErrorStackEltType IS RECORD (
30 message_name VARCHAR2(30),
31 number_of_tokens NUMBER,
32 token1 VARCHAR2(30),
33 value1 VARCHAR2(300),
34 token2 VARCHAR2(30),
35 value2 VARCHAR2(300),
36 token3 VARCHAR2(30),
37 value3 VARCHAR2(300),
38 token4 VARCHAR2(30),
39 value4 VARCHAR2(300),
40 token5 VARCHAR2(30),
41 value5 VARCHAR2(300)
42 );
43
44 -- Table of ErrorStackEltType records
45 TYPE ErrorStackType IS TABLE OF ErrorStackEltType INDEX BY BINARY_INTEGER;
46
47 -- Table of ErrorStackEltType records
48 TYPE MessageStackType IS TABLE OF VARCHAR2(500) INDEX BY BINARY_INTEGER;
49
50 -- Global error codes
51 E_SUCCESS CONSTANT NUMBER := 0; -- Dont change this one!
52 -- forms rely on value 0.
53 E_LIST_MODIFIED_SINCE_RETRIEVE CONSTANT NUMBER := 1.01;
54 E_INVALID_LIST_HEADER_ID CONSTANT NUMBER := 1.02;
55 E_INVALID_APPROVAL_LIST CONSTANT NUMBER := 1.03;
56 E_NO_SUPERVISOR_FOUND CONSTANT NUMBER := 1.04;
57 E_NO_ONE_HAS_AUTHORITY CONSTANT NUMBER := 1.05;
58 E_FAIL_TO_ACQUIRE_LOCK CONSTANT NUMBER := 1.06;
59 E_NO_APPROVAL_LIST_FOUND CONSTANT NUMBER := 1.07;
60 E_NO_NEXT_APPROVER_FOUND CONSTANT NUMBER := 1.08;
61 E_DOCUMENT_ALREADY_PREAPPROVED CONSTANT NUMBER := 1.09;
62 E_INVALID_APPROVER CONSTANT NUMBER := 1.10;
63 E_FAIL_TO_UPDATE_RESPONSE CONSTANT NUMBER := 1.11;
64 E_UNSUPPORTED_DOCUMENT_TYPE CONSTANT NUMBER := 1.12;
65 E_INVALID_FORWARD_TO_ID CONSTANT NUMBER := 1.13;
66 E_INVALID_DOCUMENT_ID CONSTANT NUMBER := 1.14;
67 E_INVALID_REBUILD_CODE CONSTANT NUMBER := 1.15;
68 E_INVALID_FIRST_APPROVER_ID CONSTANT NUMBER := 1.16;
69 E_EMPTY_APPROVAL_LIST CONSTANT NUMBER := 1.17;
70 E_EMPTY_ERROR_STACK CONSTANT NUMBER := 1.18;
71 E_DOC_MGR_TIMEOUT CONSTANT NUMBER := 1.91;
72 E_DOC_MGR_NOMGR CONSTANT NUMBER := 1.92;
73 E_DOC_MGR_OTHER CONSTANT NUMBER := 1.93;
74
75 g_checkout_flow_type VARCHAR2(30) := '';
76
77 -- Description: Retrieve the default approval list needed at the time the
78 -- requisition is first submitted.
79 --
80 -- Arguments:
81 -- In: p_first_approver_id ... employee id of first approver [optional]
82 -- p_approval_path_id ... approval path id [optional]
83 -- p_document_id ... header_id of new document
84 -- p_document_type ... document type
85 -- p_document_subtype ... document subtype
86 -- Out: p_return_code ... E_SUCCESS
87 -- - operation is successful
88 -- E_NO_ONE_HAS_AUTHORITY
89 -- - no one has the authority to approve the
90 -- document (NOTE: p_approval_list still
91 -- contains the default approval list)
92 -- E_DOC_MGR_TIMEOUT
93 -- - document manager timed out
94 -- E_DOC_MGR_NOMGR
95 -- - document manager not available
96 -- E_DOC_MGR_OTHER
97 -- - document manager errored out
98 -- others
99 -- - a sqlcode in case in case of sql errors
100 -- p_error_stack ... a stack of error message codes and tokens
101 -- p_approval_list ... default approval list
102 --
103 -- Algorithm:
104 -- (1) Get approval path id, use position flag, can preparer approve flag
105 -- (2) If preparer can and has authority to approve, return success with
106 -- empty approval list
107 -- (3) Get forward method: Direct or Hierarchy
108 -- (4) Get next approver base on the preparers employee id, position
109 -- flag and approval path
110 -- (5) Verify approver's approval authority (** NOTE: This calls the doc
111 -- manager which has COMMIT and ROLLBACK statements. **)
112 -- (6) If approver has authority to approve:
113 -- (a) Add approver to approval list
114 -- (b) Find out if approver will be on vacation (from wf_route pkg)
115 -- (c) Goto (7)
116 -- else (doesn't not have authority):
117 -- (c) If using Hierarchy forward method: Add approver to list
118 -- Find out vacation info (?)
119 -- else (using Direct) : No op
120 -- (d) Goto (4)
121 -- (7) Mark all approvers as type System default
122 -- (8) Return
123 --
124 PROCEDURE get_default_approval_list(p_first_approver_id IN NUMBER,
125 p_approval_path_id IN NUMBER,
126 p_document_id IN NUMBER,
127 p_document_type IN VARCHAR2,
128 p_document_subtype IN VARCHAR2,
129 p_rebuild_code IN VARCHAR2 DEFAULT 'INITIAL_BUILD',
130 p_return_code OUT NOCOPY NUMBER,
131 p_error_stack IN OUT NOCOPY ErrorStackType,
132 p_approval_list OUT NOCOPY ApprovalListType,
133 p_approver_id IN VARCHAR2 DEFAULT NULL);
134
135
136 -- Description: Retrieve the latest approval list needed at the time of
137 -- each approval.
138 --
139 -- Arguments:
140 -- In: p_document_id ... header_id of new document
141 -- p_document_type ... document type
142 -- p_document_subtype ... document subtype
143 -- Out: p_return_code ... E_SUCCESS
144 -- - operation is successful
145 -- others
146 -- - a sqlcode in case in case of sql errors
147 -- p_error_stack ... a stack of error message codes and tokens
148 -- p_approval_list_header_id ... approval list header id
149 -- p_last_update_date ... last update date of the approval list tables
150 -- (for lock checking)
151 -- p_approval_list ... latest approval list
152 --
153 -- Algorithm:
154 -- (1) Read from the approval list and delegation tables
155 --
156 PROCEDURE get_latest_approval_list(p_document_id IN NUMBER,
157 p_document_type IN VARCHAR2,
158 p_document_subtype IN VARCHAR2,
159 p_return_code OUT NOCOPY NUMBER,
160 p_error_stack OUT NOCOPY ErrorStackType,
161 p_approval_list_header_id OUT NOCOPY NUMBER,
162 p_last_update_date OUT NOCOPY DATE,
163 p_approval_list OUT NOCOPY ApprovalListType);
164
165
166
167
168 -- Description: Save the approval list.
169 --
170 -- Arguments:
171 -- In: p_document_id ... header_id of new document
172 -- p_document_type ... document type
173 -- p_document_subtype ... document subtype
174 -- p_first_approver_id ... first approver id [optional]
175 -- If first approver id is passed when calling
176 -- get_default_approval_list(), should pass
177 -- the same first approver id here
178 -- p_approval_list ... new approval list
179 -- From either get_default_approval_list() or
180 -- get_latest_approval_list()
181 -- p_last_update_date ... last update date of approval list
182 -- If p_approval_list is obtained by calling
183 -- get_latest_approval_list(), pass the
184 -- corresponding p_last_update_date in one of
185 -- the out parameters
186 -- If p_approval_list is from get_default_
187 -- approval_list(), pass NULL
188 -- In Out: p_approval_list_header_id ... approval list header id
189 -- If p_approval_list is from get_latest_
190 -- approval_list(), pass the corresponding
191 -- p_approval_list_header_id in one of the
192 -- out parameters.
193 -- If p_approval_list is from get_default_
194 -- approval_list(), pass NULL
195 -- In either case, it with contain the new
196 -- approval_list_header_id of the saved list
197 -- if operation is successful
198 -- Out: p_return_code ... E_SUCCESS
199 -- - operation is successful
200 -- E_LIST_MODIFIED_SINCE_RETRIEVE
201 -- - approval list is modified since last
202 -- get_latest_approval_list()
203 -- E_INVALID_LIST_HEADER_ID
204 -- - the p_approval_list_header_id passed
205 -- is not valid
206 -- E_INVALID_APPROVAL_LIST
207 -- - p_approval_list is not valid
208 -- E_FAIL_TO_ACQUIRE_LOCK
209 -- - someone else is locking the approval
210 -- list tables
211 -- others
212 -- - a sqlcode in case in case of sql errors
213 -- p_error_stack ... a stack of error message codes and tokens
214 --
215 -- Algorithm:
216 --
217 PROCEDURE save_approval_list(p_document_id IN NUMBER,
218 p_document_type IN VARCHAR2,
219 p_document_subtype IN VARCHAR2,
220 p_first_approver_id IN NUMBER,
221 p_approval_path_id IN NUMBER,
222 p_approval_list IN ApprovalListType,
223 p_last_update_date IN DATE,
224 p_approval_list_header_id IN OUT NOCOPY NUMBER,
225 p_return_code OUT NOCOPY NUMBER,
226 p_error_stack OUT NOCOPY ErrorStackType);
227
228
229 -- Description: Rebuild the existing approval list. (NOTE: a approval list must
230 -- exist, otherwise will return E_NO_APPROVAL_LIST_FOUND)
231 --
232 -- Arguments:
233 -- In: p_document_id ... header_id of new document
234 -- p_document_type ... document type
235 -- p_document_subtype ... document subtype
236 -- p_rebuild_code ... DOCUMENT_CHANGED - used by Req front end
237 -- FORWARD_RESPONSE - used by Workflow
238 -- INVALID_APPROVER - used by Workflow
239 -- Out: p_return_code ... E_SUCCESS
240 -- - operation is successful
241 -- E_UNSUPPORTED_DOCUMENT_TYPE
242 -- - document type/subtype is not supported
243 -- E_NO_APPROVAL_LIST_FOUND
244 -- - could not find the associated approval
245 -- list
246 -- E_INVALID_FORWARD_TO_ID
247 -- - the most recent forward_to_id is not valid
248 -- E_DOCUMENT_ALREADY_PREAPPROVED
249 -- - the document is already preapproved so
250 -- no rebulid is needed
251 -- E_INVALID_APPROVAL_LIST
252 -- - approval list is invalid
253 -- E_LIST_MODIFIED_SINCE_RETRIEVE
254 -- - failed to lock the list tables
255 -- others
256 -- - a sqlcode in case in case of sql errors
257 -- p_error_stack ... a stack of error message codes and tokens
258 -- p_approval_list_header_id ... new approval_list_header_id as a result of
259 -- rebuild
260 --
261 -- Algorithm:
262 --
263 PROCEDURE rebuild_approval_list(p_document_id IN NUMBER,
264 p_document_type IN VARCHAR2,
265 p_document_subtype IN VARCHAR2,
266 p_rebuild_code IN VARCHAR2,
267 p_return_code OUT NOCOPY NUMBER,
268 p_error_stack OUT NOCOPY ErrorStackType,
269 p_approval_list_header_id OUT NOCOPY NUMBER);
270
271
272 -- Description: Validate the approval list.
273 --
274 -- Arguments:
275 -- In: p_document_id ... header_id of new document
276 -- p_document_type ... document type
277 -- p_document_subtype ... document subtype
278 -- p_approval_list ... new approval list
279 -- p_current_sequence_num ... the current sequence number [optional]
280 -- Out: p_return_code ... E_SUCCESS
281 -- - operation is successful
282 -- E_INVALID_APPROVAL_LIST
283 -- - approval list is invalid
284 -- others
285 -- - a sqlcode in case in case of sql errors
286 -- p_error_stack ... a stack of error message codes and tokens
287 --
288 -- Algorithm:
289 -- (1) Loop throught every approver on the approval list
290 -- (2) Check if approver is valid by calling is_approver_valid()
291 --
292 PROCEDURE validate_approval_list(p_document_id IN NUMBER,
293 p_document_type IN VARCHAR2,
294 p_document_subtype IN VARCHAR2,
295 p_approval_list IN ApprovalListType,
296 p_current_sequence_num IN NUMBER,
297 p_return_code OUT NOCOPY NUMBER,
301 -- Description: Determine whether or not an approver is valid.
298 p_error_stack IN OUT NOCOPY ErrorStackType);
299
300
302 --
303 -- Arguments:
304 -- In: p_document_id ... header_id of new document
305 -- p_document_type ... document type
306 -- p_document_subtype ... document subtype
307 -- p_approver_id ... employee_id of approver
308 -- p_approver_type ... type of approver
309 --
310 -- Return:
311 -- TRUE ... if the approver can be put on the approval list
312 -- FALSE ... otherwise
313 --
314 -- Algorithm:
315 -- (1) check if approver_type is valid
316 -- (2) check if approver is valid employee
317 -- (3) check if he/she is in wf_users
318 --
319 FUNCTION is_approver_valid(p_document_id IN NUMBER,
320 p_document_type IN VARCHAR2,
321 p_document_subtype IN VARCHAR2,
322 p_approver_id IN NUMBER,
323 p_approver_type IN VARCHAR2) return BOOLEAN;
324
325
326
327
328 -- Description: Determine whether or not an approver is mandatory
329 --
330 -- Arguments:
331 -- In: p_document_id ... header_id of new document
332 -- p_document_type ... document type
333 -- p_document_subtype ... document subtype
334 -- p_preparer_id ... employee_id of preparer
335 -- p_approver_id ... employee_id of approver
336 -- p_approver_type ... type of approver
337 --
338 -- Return:
339 -- TRUE ... if the approver is mandatory
340 -- FALSE ... if not
341 --
342 -- Algorithm:
343 -- (1) If p_approver_type is system default, return TRUE
344 -- else return FALSE
345 --
346 FUNCTION is_approver_mandatory(p_document_id IN NUMBER,
347 p_document_type IN VARCHAR2,
348 p_document_subtype IN VARCHAR2,
349 p_preparer_id IN NUMBER,
350 p_approver_id IN NUMBER,
351 p_approver_type IN VARCHAR2) RETURN BOOLEAN;
352
353
354
355 -- Procedures used by workflow
356
357 PROCEDURE get_next_approver(p_document_id IN NUMBER,
358 p_document_type IN VARCHAR2,
359 p_document_subtype IN VARCHAR2,
360 p_return_code OUT NOCOPY NUMBER,
361 p_next_approver_id OUT NOCOPY NUMBER,
362 p_sequence_num OUT NOCOPY NUMBER,
363 p_approver_type OUT NOCOPY VARCHAR2);
364
365 PROCEDURE does_approval_list_exist(p_document_id IN NUMBER,
369 p_itemkey IN VARCHAR2,
366 p_document_type IN VARCHAR2,
367 p_document_subtype IN VARCHAR2,
368 p_itemtype IN VARCHAR2,
370 p_return_code OUT NOCOPY NUMBER,
371 p_approval_list_header_id OUT NOCOPY NUMBER);
372
373 PROCEDURE update_approval_list_itemkey(p_approval_list_header_id IN NUMBER,
374 p_itemtype IN VARCHAR2,
375 p_itemkey IN VARCHAR2,
379 p_document_type IN VARCHAR2,
376 p_return_code OUT NOCOPY NUMBER);
377
378 PROCEDURE update_approval_list_response(p_document_id IN NUMBER,
380 p_document_subtype IN VARCHAR2,
381 p_itemtype IN VARCHAR2,
382 p_itemkey IN VARCHAR2,
383 p_approver_id IN NUMBER,
384 p_responder_id IN NUMBER,
385 p_forward_to_id IN NUMBER,
386 p_response IN VARCHAR2,
387 p_response_date IN DATE,
388 p_comments IN VARCHAR2,
389 p_return_code OUT NOCOPY NUMBER);
390
391 PROCEDURE is_approval_list_exhausted(p_document_id IN VARCHAR2,
392 p_document_type IN VARCHAR2,
393 p_document_subtype IN VARCHAR2,
394 p_itemtype IN VARCHAR2,
395 p_itemkey IN VARCHAR2,
396 p_return_code OUT NOCOPY NUMBER,
397 p_result OUT NOCOPY BOOLEAN);
398
399 PROCEDURE print_approval_list(p_approval_list IN ApprovalListType);
400
401 PROCEDURE retrieve_messages(p_error_stack IN ErrorStackType,
402 p_return_code OUT NOCOPY NUMBER,
403 p_message_stack OUT NOCOPY MessageStackType);
404
405 PROCEDURE print_error_stack(p_error_stack IN ErrorStackType);
406
407 PROCEDURE forms_rebuild_approval_list(p_document_id IN NUMBER,
408 p_document_type IN VARCHAR2,
409 p_document_subtype IN VARCHAR2,
410 p_rebuild_code IN VARCHAR2,
411 p_return_code OUT NOCOPY NUMBER,
412 p_approval_list_header_id OUT NOCOPY NUMBER);
413
414
415 END PO_APPROVALLIST_S1;
416
[Home] [Help]