[Home] [Help]
PACKAGE BODY: APPS.POR_APPROVAL_LIST
Source
1 PACKAGE BODY por_approval_list AS
2 /* $Header: PORAPRLB.pls 120.3 2006/06/28 06:27:30 mkohale noship $ */
3
4 -- Private global variables
5 g_quote_char CONSTANT VARCHAR2(1) := '\';
6 g_field_delimiter CONSTANT VARCHAR2(1) := ';';
7 g_date_format_mask CONSTANT VARCHAR2(50) := 'DD-MON-YY HH24:MI:SS';
8
9 g_approval_list_string VARCHAR2(32767) := NULL;
10
11 -- Private routine prototypes
12
13 --------------------------------------------------------------------------------
14 --Start of Comments
15 --Function:
16 -- If a document is a previously saved requisition,
17 -- append the user-added approvers to the current approver list.
18
19 --Parameters:
20 --IN:
21 --p_document_id requisition header ID
22 --p_approval_list approval list table
23 --OUT:
24 --p_approval_list approval list table
25 --End of Comments
26 --------------------------------------------------------------------------------
27 procedure append_saved_adhoc_approver(
28 p_document_id IN NUMBER,
29 p_approval_list IN OUT NOCOPY po_approvallist_s1.ApprovalListType);
30
31 PROCEDURE PushMessage(p_error_stack IN OUT NOCOPY /* file.sql.39 change */ po_approvallist_s1.ErrorStackType,
32 p_message_name IN VARCHAR2,
33 p_token1 IN VARCHAR2 DEFAULT NULL,
34 p_value1 IN VARCHAR2 DEFAULT NULL,
35 p_token2 IN VARCHAR2 DEFAULT NULL,
36 p_value2 IN VARCHAR2 DEFAULT NULL,
37 p_token3 IN VARCHAR2 DEFAULT NULL,
38 p_value3 IN VARCHAR2 DEFAULT NULL,
39 p_token4 IN VARCHAR2 DEFAULT NULL,
40 p_value4 IN VARCHAR2 DEFAULT NULL,
41 p_token5 IN VARCHAR2 DEFAULT NULL,
42 p_value5 IN VARCHAR2 DEFAULT NULL);
43
44 PROCEDURE VerifyAuthority(p_document_id IN NUMBER,
45 p_document_type IN VARCHAR2,
46 p_document_subtype IN VARCHAR2,
47 p_employee_id IN NUMBER,
48 p_return_code OUT NOCOPY /* file.sql.39 change */ NUMBER,
49 p_error_stack IN OUT NOCOPY /* file.sql.39 change */ po_approvallist_s1.ErrorStackType,
50 p_has_authority OUT NOCOPY /* file.sql.39 change */ BOOLEAN);
51
52 PROCEDURE MarshalField(p_string IN VARCHAR2,
53 p_quote_char IN VARCHAR2,
54 p_delimiter IN VARCHAR2);
55
56 FUNCTION GetNextToken(p_start_pos IN OUT NOCOPY NUMBER,
57 p_quote_char IN VARCHAR2,
58 p_delimiter IN VARCHAR2,
59 p_remove_quotes IN BOOLEAN,
60 p_result OUT NOCOPY VARCHAR2) RETURN BOOLEAN;
61
62 procedure get_doc_subtype(p_document_id in number) is
63
64 begin
65
66 select type_lookup_code
67 into g_document_subtype
68 from po_requisition_headers_all
69 where requisition_header_id = p_document_id;
70
71 end;
72
73 -- Public routines
74 PROCEDURE get_approval_list(p_document_id IN NUMBER,
75 p_first_approver_id IN NUMBER DEFAULT NULL,
76 p_default_flag IN NUMBER DEFAULT NULL,
77 p_rebuild_flag IN NUMBER DEFAULT NULL,
78 p_approval_list_header_id OUT NOCOPY NUMBER,
79 p_last_update_date OUT NOCOPY VARCHAR2,
80 p_approval_list_string OUT NOCOPY VARCHAR2,
81 p_approval_list_count OUT NOCOPY NUMBER,
82 p_quote_char OUT NOCOPY VARCHAR2,
83 p_field_delimiter OUT NOCOPY VARCHAR2,
84 p_return_code OUT NOCOPY NUMBER,
85 p_error_stack_string OUT NOCOPY VARCHAR2,
86 p_preparer_can_approve OUT NOCOPY NUMBER,
87 p_append_saved_approver_flag IN NUMBER DEFAULT NULL,
88 p_checkout_flow_type IN VARCHAR2 DEFAULT NULL) IS
89
90 l_approval_list po_approvallist_s1.ApprovalListType;
91 l_error_stack po_approvallist_s1.ErrorStackType;
92 l_return_code NUMBER;
93 l_last_update_date DATE;
94 l_approval_list_header_id NUMBER;
95 l_index NUMBER;
96 l_tmp_string VARCHAR2(2000);
97 l_initial_build BOOLEAN := false;
98 l_approval_return_code NUMBER;
99 l_has_authority BOOLEAN;
100 l_preparer_id NUMBER;
101 l_can_preparer_approve_flag VARCHAR2(1);
102
103 BEGIN
104
105 PO_APPROVALLIST_S1.g_checkout_flow_type := p_checkout_flow_type;
106
107 get_doc_subtype(p_document_id);
108
109 p_approval_list_string := NULL;
110
111 IF (p_default_flag = 1) THEN
112 l_initial_build := true;
113 po_approvallist_s1.get_default_approval_list(
114 p_first_approver_id=>p_first_approver_id,
115 p_approval_path_id=>NULL,
116 p_document_id=>p_document_id,
117 p_document_type=>g_document_type,
118 p_document_subtype=>g_document_subtype,
119 p_rebuild_code=>'INITIAL_BUILD',
120 p_return_code=>l_return_code,
121 p_error_stack=>l_error_stack,
122 p_approval_list=>l_approval_list);
123
124 IF (p_append_saved_approver_flag = 1) THEN
125 append_saved_adhoc_approver(
126 p_document_id=>p_document_id,
127 p_approval_list=>l_approval_list);
128 END IF;
129
130 ELSIF (p_rebuild_flag = 1) THEN
131 po_approvallist_s1.rebuild_approval_list(
132 p_document_id=>p_document_id,
133 p_document_type=>g_document_type,
134 p_document_subtype=>g_document_subtype,
135 p_rebuild_code=>'DOCUMENT_CHANGED',
136 p_return_code=>l_return_code,
137 p_error_stack=>l_error_stack,
138 p_approval_list_header_id=>l_approval_list_header_id);
139
140 IF (l_return_code <> po_approvallist_s1.E_SUCCESS) THEN
141 GOTO HANDLE_ERROR; -- bad style huh?
142 END IF;
143 po_approvallist_s1.get_latest_approval_list
144 (p_document_id=>p_document_id,
145 p_document_type=>g_document_type,
146 p_document_subtype=>g_document_subtype,
147 p_return_code=>l_return_code,
148 p_error_stack=>l_error_stack,
149 p_approval_list_header_id=>l_approval_list_header_id,
150 p_last_update_date=>l_last_update_date,
151 p_approval_list=>l_approval_list);
152
153 p_last_update_date := to_char(l_last_update_date, g_date_format_mask);
154 p_approval_list_header_id := l_approval_list_header_id;
155 ELSE
156 po_approvallist_s1.get_latest_approval_list
157 (p_document_id=>p_document_id,
158 p_document_type=>g_document_type,
159 p_document_subtype=>g_document_subtype,
160 p_return_code=>l_return_code,
161 p_error_stack=>l_error_stack,
162 p_approval_list_header_id=>l_approval_list_header_id,
163 p_last_update_date=>l_last_update_date,
164 p_approval_list=>l_approval_list);
165
166 p_last_update_date := to_char(l_last_update_date, g_date_format_mask);
167 p_approval_list_header_id := l_approval_list_header_id;
168
169 IF (l_return_code <> po_approvallist_s1.E_SUCCESS) THEN
170
171 IF (l_error_stack.COUNT > 0) THEN
172 l_error_stack.delete;
173 END IF;
174
175 l_initial_build := true;
176 po_approvallist_s1.get_default_approval_list
177 ( p_first_approver_id=>p_first_approver_id,
178 p_approval_path_id=>NULL,
179 p_document_id=>p_document_id,
180 p_document_type=>g_document_type,
181 p_document_subtype=>g_document_subtype,
182 p_rebuild_code=>'INITIAL_BUILD',
183 p_return_code=>l_return_code,
184 p_error_stack=>l_error_stack,
185 p_approval_list=>l_approval_list);
186
187 END IF;
188 END IF;
189
190 IF (l_return_code = po_approvallist_s1.E_SUCCESS) THEN
191
192 g_approval_list_string := NULL;
193
194 IF (l_approval_list.COUNT > 0) THEN
195 l_index := l_approval_list.FIRST;
196 WHILE (l_index IS NOT NULL) LOOP
197 MarshalField(to_char(l_approval_list(l_index).id), g_quote_char, g_field_delimiter);
198 MarshalField(to_char(l_approval_list(l_index).approver_id), g_quote_char, g_field_delimiter);
199 MarshalField(l_approval_list(l_index).approver_disp_name, g_quote_char, g_field_delimiter);
200 MarshalField(to_char(l_approval_list(l_index).responder_id), g_quote_char, g_field_delimiter);
201 MarshalField(l_approval_list(l_index).responder_disp_name, g_quote_char, g_field_delimiter);
202 MarshalField(to_char(l_approval_list(l_index).forward_to_id), g_quote_char, g_field_delimiter);
203 MarshalField(l_approval_list(l_index).forward_to_disp_name, g_quote_char, g_field_delimiter);
204 MarshalField(l_approval_list(l_index).status, g_quote_char, g_field_delimiter);
205 MarshalField(l_approval_list(l_index).approver_type, g_quote_char, g_field_delimiter);
206 MarshalField(l_approval_list(l_index).mandatory_flag, g_quote_char, g_field_delimiter);
207 MarshalField(to_char(l_approval_list(l_index).sequence_num), g_quote_char, g_field_delimiter);
208 MarshalField(to_char(l_approval_list(l_index).response_date), g_quote_char, g_field_delimiter);
209
210 /* Add this when POXAPL1B is modified
211 MarshalField(l_approval_list(l_index).attribute_category, g_quote_char, g_field_delimiter);
212 MarshalField(l_approval_list(l_index).attribute1, g_quote_char, g_field_delimiter);
213 MarshalField(l_approval_list(l_index).attribute2, g_quote_char, g_field_delimiter);
214 MarshalField(l_approval_list(l_index).attribute3, g_quote_char, g_field_delimiter);
215 MarshalField(l_approval_list(l_index).attribute4, g_quote_char, g_field_delimiter);
216 MarshalField(l_approval_list(l_index).attribute5, g_quote_char, g_field_delimiter);
217 MarshalField(l_approval_list(l_index).attribute6, g_quote_char, g_field_delimiter);
218 MarshalField(l_approval_list(l_index).attribute7, g_quote_char, g_field_delimiter);
219 MarshalField(l_approval_list(l_index).attribute8, g_quote_char, g_field_delimiter);
220 MarshalField(l_approval_list(l_index).attribute9, g_quote_char, g_field_delimiter);
221 MarshalField(l_approval_list(l_index).attribute10, g_quote_char, g_field_delimiter);
222 MarshalField(l_approval_list(l_index).attribute11, g_quote_char, g_field_delimiter);
223 MarshalField(l_approval_list(l_index).attribute12, g_quote_char, g_field_delimiter);
224 MarshalField(l_approval_list(l_index).attribute13, g_quote_char, g_field_delimiter);
225 MarshalField(l_approval_list(l_index).attribute14, g_quote_char, g_field_delimiter);
226 MarshalField(l_approval_list(l_index).attribute15, g_quote_char, g_field_delimiter);
227 */
228 l_index := l_approval_list.NEXT(l_index);
229 END LOOP;
230 END IF;
231
232 p_approval_list_count := l_approval_list.COUNT;
233 p_approval_list_string := g_approval_list_string;
234 p_quote_char := g_quote_char;
235 p_field_delimiter := g_field_delimiter;
236 IF (NOT l_initial_build) THEN
237 p_return_code := 0;
238 ELSE
239 /** Bug 1001039
240 * bgu, Sept. 27, 1999
241 * This code should not be 1, because 1 is used by the scenario
242 * when approval list is built for the first time, and with some
243 * error. buildApprovalList precedure in RealOrder.java will not
244 * show message of which the approver has authority to approve
245 * the req if return code is 1.
246 */
247 p_return_code := 2;
248 END IF;
249
250 -- This section is used solely to determine if the preparer of the requisition
251 -- can approve the requisition.
252
253 BEGIN
254
255 SELECT preparer_id
256 INTO l_preparer_id
257 FROM po_requisition_headers
258 WHERE requisition_header_id = p_document_id;
259
260 VerifyAuthority(p_document_id=>p_document_id,
261 p_document_type=>g_document_type,
262 p_document_subtype=>g_document_subtype,
263 p_employee_id=>l_preparer_id,
264 p_return_code=>l_approval_return_code,
265 p_error_stack=>l_error_stack,
266 p_has_authority=>l_has_authority);
267
268 IF (l_approval_return_code <> po_approvallist_s1.E_SUCCESS) THEN
269 p_preparer_can_approve := 0;
270 ELSIF (l_has_authority) THEN
271 p_preparer_can_approve := 1;
272 ELSE
273 p_preparer_can_approve := 0;
274 END IF;
275
276 EXCEPTION
277 WHEN NO_DATA_FOUND THEN
278 p_preparer_can_approve := 0;
279 END;
280
281 RETURN;
282
283
284 ELSIF (p_default_flag = 1 AND l_return_code = po_approvallist_s1.E_NO_ONE_HAS_AUTHORITY) THEN
285
286 SELECT NVL(can_preparer_approve_flag, 'N')
287 INTO l_can_preparer_approve_flag
288 FROM po_document_types podt
289 WHERE podt.document_type_code = g_document_type
290 AND podt.document_subtype = g_document_subtype;
291
292 IF (l_can_preparer_approve_flag = 'N') THEN
293
294
295 BEGIN
296
297 SELECT preparer_id
298 INTO l_preparer_id
299 FROM po_requisition_headers
300 WHERE requisition_header_id = p_document_id;
301
302 VerifyAuthority(p_document_id=>p_document_id,
303 p_document_type=>g_document_type,
304 p_document_subtype=>g_document_subtype,
305 p_employee_id=>l_preparer_id,
306 p_return_code=>l_approval_return_code,
307 p_error_stack=>l_error_stack,
308 p_has_authority=>l_has_authority);
309
310 IF (l_approval_return_code <> po_approvallist_s1.E_SUCCESS) THEN
311 p_preparer_can_approve := 0;
312 ELSIF (l_has_authority) THEN
313 p_preparer_can_approve := 1;
314 ELSE
315 p_preparer_can_approve := 0;
316 END IF;
317
318 EXCEPTION
319 WHEN NO_DATA_FOUND THEN
320 p_preparer_can_approve := 0;
321 END;
322
323 -- RETURN;
324 END IF; --approver_flag
325 END IF;
326
327
328 <<HANDLE_ERROR>>
329
330 IF (l_error_stack.COUNT > 0 and NOT l_initial_build ) THEN
331 p_error_stack_string := '';
332 l_index := l_error_stack.FIRST;
333 WHILE (l_index IS NOT NULL) LOOP
334 p_error_stack_string := p_error_stack_string || l_error_stack(l_index).message_name || g_quote_char;
335 p_error_stack_string := p_error_stack_string || l_error_stack(l_index).token1 || g_quote_char;
336 p_error_stack_string := p_error_stack_string || l_error_stack(l_index).value1 || g_quote_char;
337 p_error_stack_string := p_error_stack_string || l_error_stack(l_index).token2 || g_quote_char;
338 p_error_stack_string := p_error_stack_string || l_error_stack(l_index).value2 || g_quote_char;
339 p_error_stack_string := p_error_stack_string || l_error_stack(l_index).token3 || g_quote_char;
340 p_error_stack_string := p_error_stack_string || l_error_stack(l_index).value3 || g_quote_char;
341 p_error_stack_string := p_error_stack_string || l_error_stack(l_index).token4 || g_quote_char;
342 p_error_stack_string := p_error_stack_string || l_error_stack(l_index).value4 || g_quote_char;
343 p_error_stack_string := p_error_stack_string || l_error_stack(l_index).token5 || g_quote_char;
344 p_error_stack_string := p_error_stack_string || l_error_stack(l_index).value5 || g_field_delimiter;
345
346 l_index := l_error_stack.NEXT(l_index);
347 END LOOP;
348 l_error_stack.delete;
349 END IF;
350 p_approval_list_count := 0;
351 p_approval_list_string := null;
352 p_quote_char := g_quote_char;
353 p_field_delimiter := g_field_delimiter;
354 p_preparer_can_approve := 0;
355
356 IF (NOT l_initial_build) THEN
357 p_return_code := -1;
358 ELSE
359 p_return_code := 1;
360 END IF;
361
362 PO_APPROVALLIST_S1.g_checkout_flow_type := '';
363
364 END get_approval_list;
365
366 PROCEDURE save_approval_list(p_document_id IN NUMBER,
367 p_approval_list_string IN VARCHAR2,
368 p_approval_list_header_id IN OUT NOCOPY NUMBER,
369 p_first_approver_id IN NUMBER,
370 p_last_update_date IN OUT NOCOPY VARCHAR2,
371 p_quote_char IN VARCHAR2,
372 p_field_delimiter IN VARCHAR2,
373 p_return_code OUT NOCOPY NUMBER,
374 p_error_stack_string OUT NOCOPY VARCHAR2) IS
375 l_approval_list po_approvallist_s1.ApprovalListType;
376 l_approval_list_elt po_approvallist_s1.ApprovalListEltType;
377 l_error_stack po_approvallist_s1.ErrorStackType;
378 l_index NUMBER;
379 l_pos NUMBER;
380 l_string VARCHAR2(32767);
381 l_last_update_date DATE;
382 l_return_code NUMBER;
383 BEGIN
384
385 get_doc_subtype(p_document_id);
386
387 p_return_code := 0;
388 p_error_stack_string := null;
389
390 IF (p_approval_list_header_id = -9999) THEN
391 p_approval_list_header_id := NULL;
392 END IF;
393
394 -- Sanity check:
395 IF (p_approval_list_header_id = NULL) THEN
396 l_last_update_date := NULL;
397 ELSE
398 IF (p_last_update_date = NULL) THEN
399 RETURN;
400 ELSE
401 l_last_update_date := to_date(p_last_update_date, g_date_format_mask);
402 END IF;
403 END IF;
404
405 g_approval_list_string := p_approval_list_string;
406 l_index := 0;
407 l_pos := 1;
408
409 LOOP
410 l_approval_list_elt := NULL;
411
412 IF (NOT GetNextToken(l_pos, p_quote_char, p_field_delimiter, TRUE,
413 l_string)) THEN
414 EXIT;
415 END IF;
416 l_approval_list_elt.id := to_number(l_string);
417
418 IF (NOT GetNextToken(l_pos, p_quote_char, p_field_delimiter, TRUE, l_string)) THEN
419 -- handle datacorruption: set error code (?)
420 RETURN;
421 END IF;
422 l_approval_list_elt.approver_id := to_number(l_string);
423
424 IF (NOT GetNextToken(l_pos, p_quote_char, p_field_delimiter, TRUE, l_string)) THEN
425 -- handle datacorruption: set error code (?)
426 RETURN;
427 END IF;
428 l_approval_list_elt.approver_disp_name := l_string;
429
430 IF (NOT GetNextToken(l_pos, p_quote_char, p_field_delimiter, TRUE, l_string)) THEN
431 -- handle datacorruption: set error code (?)
432 RETURN;
433 END IF;
434 l_approval_list_elt.responder_id := to_number(l_string);
435
436 IF (NOT GetNextToken(l_pos, p_quote_char, p_field_delimiter, TRUE, l_string)) THEN
437 -- handle datacorruption: set error code (?)
438 RETURN;
439 END IF;
440 l_approval_list_elt.responder_disp_name := l_string;
441
442 IF (NOT GetNextToken(l_pos, p_quote_char, p_field_delimiter, TRUE, l_string)) THEN
443 -- handle datacorruption: set error code (?)
444 RETURN;
445 END IF;
446 l_approval_list_elt.forward_to_id := to_number(l_string);
447
448 IF (NOT GetNextToken(l_pos, p_quote_char, p_field_delimiter, TRUE, l_string)) THEN
449 -- handle datacorruption: set error code (?)
450 RETURN;
451 END IF;
452 l_approval_list_elt.forward_to_disp_name := l_string;
453
454 IF (NOT GetNextToken(l_pos, p_quote_char, p_field_delimiter, TRUE, l_string)) THEN
455 -- handle datacorruption: set error code (?)
456 RETURN;
457 END IF;
458 l_approval_list_elt.status := l_string;
459
460 IF (NOT GetNextToken(l_pos, p_quote_char, p_field_delimiter, TRUE, l_string)) THEN
461 -- handle datacorruption: set error code (?)
462 RETURN;
463 END IF;
464 l_approval_list_elt.approver_type := l_string;
465
466 IF (NOT GetNextToken(l_pos, p_quote_char, p_field_delimiter, TRUE, l_string)) THEN
467 -- handle datacorruption: set error code (?)
468 RETURN;
469 END IF;
470 l_approval_list_elt.mandatory_flag := l_string;
471
472 IF (NOT GetNextToken(l_pos, p_quote_char, p_field_delimiter, TRUE, l_string)) THEN
473 -- handle datacorruption: set error code (?)
474 RETURN;
475 END IF;
476 l_approval_list_elt.sequence_num := to_number(l_string);
477
478 IF (NOT GetNextToken(l_pos, p_quote_char, p_field_delimiter, TRUE, l_string)) THEN
479
480 RETURN;
481 END IF;
482 --l_approval_list_elt.response_date := to_number(l_string);
483
484 /* Add this when POXAPL1B is modified
485
486 IF (NOT GetNextToken(l_pos, p_quote_char, p_field_delimiter, TRUE, l_string)) THEN
487 -- handle datacorruption: set error code (?)
488 RETURN;
489 END IF;
490 l_approval_list_elt.attribute_category := to_number(l_string);
491
492 IF (NOT GetNextToken(l_pos, p_quote_char, p_field_delimiter, TRUE, l_string)) THEN
493 -- handle datacorruption: set error code (?)
494 RETURN;
495 END IF;
496 l_approval_list_elt.attribute1 := to_number(l_string);
497
498 IF (NOT GetNextToken(l_pos, p_quote_char, p_field_delimiter, TRUE, l_string)) THEN
499 -- handle datacorruption: set error code (?)
500 RETURN;
501 END IF;
502 l_approval_list_elt.attribute2 := to_number(l_string);
503
504 IF (NOT GetNextToken(l_pos, p_quote_char, p_field_delimiter, TRUE, l_string)) THEN
505 -- handle datacorruption: set error code (?)
506 RETURN;
507 END IF;
508 l_approval_list_elt.attribute3 := to_number(l_string);
509
510 IF (NOT GetNextToken(l_pos, p_quote_char, p_field_delimiter, TRUE, l_string)) THEN
511 -- handle datacorruption: set error code (?)
512 RETURN;
513 END IF;
514 l_approval_list_elt.attribute4 := to_number(l_string);
515
516 IF (NOT GetNextToken(l_pos, p_quote_char, p_field_delimiter, TRUE, l_string)) THEN
517 -- handle datacorruption: set error code (?)
518 RETURN;
519 END IF;
520 l_approval_list_elt.attribute5 := to_number(l_string);
521
522 IF (NOT GetNextToken(l_pos, p_quote_char, p_field_delimiter, TRUE, l_string)) THEN
523 -- handle datacorruption: set error code (?)
524 RETURN;
525 END IF;
526 l_approval_list_elt.attribute6 := to_number(l_string);
527
528 IF (NOT GetNextToken(l_pos, p_quote_char, p_field_delimiter, TRUE, l_string)) THEN
529 -- handle datacorruption: set error code (?)
530 RETURN;
531 END IF;
532 l_approval_list_elt.attribute7 := to_number(l_string);
533
534 IF (NOT GetNextToken(l_pos, p_quote_char, p_field_delimiter, TRUE, l_string)) THEN
535 -- handle datacorruption: set error code (?)
536 RETURN;
537 END IF;
538 l_approval_list_elt.attribute8 := to_number(l_string);
539
540 IF (NOT GetNextToken(l_pos, p_quote_char, p_field_delimiter, TRUE, l_string)) THEN
541 -- handle datacorruption: set error code (?)
542 RETURN;
543 END IF;
544 l_approval_list_elt.attribute9 := to_number(l_string);
545
546 IF (NOT GetNextToken(l_pos, p_quote_char, p_field_delimiter, TRUE, l_string)) THEN
547 -- handle datacorruption: set error code (?)
548 RETURN;
549 END IF;
550 l_approval_list_elt.attribute10 := to_number(l_string);
551
552 IF (NOT GetNextToken(l_pos, p_quote_char, p_field_delimiter, TRUE, l_string)) THEN
553 -- handle datacorruption: set error code (?)
554 RETURN;
555 END IF;
556 l_approval_list_elt.attribute11 := to_number(l_string);
557
558 IF (NOT GetNextToken(l_pos, p_quote_char, p_field_delimiter, TRUE, l_string)) THEN
559 -- handle datacorruption: set error code (?)
560 RETURN;
561 END IF;
562 l_approval_list_elt.attribute12 := to_number(l_string);
563
564 IF (NOT GetNextToken(l_pos, p_quote_char, p_field_delimiter, TRUE, l_string)) THEN
565 -- handle datacorruption: set error code (?)
566 RETURN;
567 END IF;
568 l_approval_list_elt.attribute13 := to_number(l_string);
569
570 IF (NOT GetNextToken(l_pos, p_quote_char, p_field_delimiter, TRUE, l_string)) THEN
571 -- handle datacorruption: set error code (?)
572 RETURN;
573 END IF;
574 l_approval_list_elt.attribute14 := to_number(l_string);
575
576 IF (NOT GetNextToken(l_pos, p_quote_char, p_field_delimiter, TRUE, l_string)) THEN
577 -- handle datacorruption: set error code (?)
578 RETURN;
579 END IF;
580 l_approval_list_elt.attribute15 := to_number(l_string);
581
582 */
583
584 l_index := l_index + 1;
585 l_approval_list(l_index) := l_approval_list_elt;
586
587 END LOOP;
588
589 -- debug: po_approvallist_s1.print_approval_list(l_approval_list);
590
591 po_approvallist_s1.save_approval_list(
592 p_document_id=>p_document_id,
593 p_document_type=>g_document_type,
594 p_document_subtype=>g_document_subtype,
595 p_first_approver_id=>p_first_approver_id,
596 p_approval_path_id=>NULL,
597 p_approval_list=>l_approval_list,
598 p_last_update_date=>l_last_update_date,
599 p_approval_list_header_id=> p_approval_list_header_id,
600 p_return_code=>l_return_code,
601 p_error_stack=>l_error_stack);
602
603 IF (l_return_code = po_approvallist_s1.E_SUCCESS) THEN
604 p_return_code := 1;
605
606 select to_char(last_update_date, g_date_format_mask) into p_last_update_date
607 from po_approval_list_headers
608 where approval_list_header_id = p_approval_list_header_id;
609 ELSE
610 -- Deal with the specific error codes if needed
611 -- Handle the error stack
612 IF (l_error_stack.COUNT > 0) THEN
613 p_error_stack_string := '';
614 l_index := l_error_stack.FIRST;
615 WHILE (l_index IS NOT NULL) LOOP
616 p_error_stack_string := p_error_stack_string || l_error_stack(l_index).message_name || g_quote_char;
617 p_error_stack_string := p_error_stack_string || l_error_stack(l_index).token1 || g_quote_char;
618 p_error_stack_string := p_error_stack_string || l_error_stack(l_index).value1 || g_quote_char;
619 p_error_stack_string := p_error_stack_string || l_error_stack(l_index).token2 || g_quote_char;
620 p_error_stack_string := p_error_stack_string || l_error_stack(l_index).value2 || g_quote_char;
621 p_error_stack_string := p_error_stack_string || l_error_stack(l_index).token3 || g_quote_char;
622 p_error_stack_string := p_error_stack_string || l_error_stack(l_index).value3 || g_quote_char;
623 p_error_stack_string := p_error_stack_string || l_error_stack(l_index).token4 || g_quote_char;
624 p_error_stack_string := p_error_stack_string || l_error_stack(l_index).value4 || g_quote_char;
625 p_error_stack_string := p_error_stack_string || l_error_stack(l_index).token5 || g_quote_char;
626 p_error_stack_string := p_error_stack_string || l_error_stack(l_index).value5 || g_field_delimiter;
627
628 l_index := l_error_stack.NEXT(l_index);
629 END LOOP;
630 l_error_stack.delete;
631 END IF;
632 END IF;
633
634 END save_approval_list;
635
636
637
638
639
640 PROCEDURE temp_get_rebuild_to_work(p_document_id IN NUMBER) IS
641
642 l_wf_item_key VARCHAR2(32767) := to_char(p_document_id) || '-1';
643 l_return NUMBER := 0;
644 BEGIN
645
646 UPDATE po_requisition_headers
647 SET wf_item_type = 'REQAPPRV',
648 wf_item_key = l_wf_item_key
649 WHERE requisition_header_id = p_document_id;
650
651 PO_APPROVALLIST_S1.UPDATE_APPROVAL_LIST_ITEMKEY(p_document_id,
652 'REQAPPRV',
653 l_wf_item_key,
654 l_return);
655
656 COMMIT;
657
658 END temp_get_rebuild_to_work;
659
660
661
662
663 -- Private routines.
664
665 PROCEDURE MarshalField(p_string IN VARCHAR2,
666 p_quote_char IN VARCHAR2,
667 p_delimiter IN VARCHAR2) IS
668 l_string VARCHAR2(32767) := NULL;
669 BEGIN
670 l_string := p_string;
671 l_string := REPLACE(l_string, p_quote_char, p_quote_char || p_quote_char);
672 l_string := REPLACE(l_string, p_delimiter, p_quote_char || p_delimiter);
673 g_approval_list_string := g_approval_list_string || l_string || p_delimiter;
674 END MarshalField;
675
676 FUNCTION GetNextToken(p_start_pos IN OUT NOCOPY NUMBER,
677 p_quote_char IN VARCHAR2,
678 p_delimiter IN VARCHAR2,
679 p_remove_quotes IN BOOLEAN,
680 p_result OUT NOCOPY VARCHAR2) RETURN BOOLEAN IS
681 l_pos NUMBER;
682 l_start_pos NUMBER;
683 l_max_pos NUMBER;
684 l_string VARCHAR2(32767);
685 BEGIN
686 l_start_pos := p_start_pos;
687 l_max_pos := LENGTH(g_approval_list_string);
688 l_pos := p_start_pos;
689
690 WHILE (l_start_pos <= l_max_pos) LOOP
691 l_pos := INSTR(g_approval_list_string, p_delimiter, l_start_pos);
692 IF (l_pos > 0) THEN
693 IF (l_pos = p_start_pos) THEN
694 p_start_pos := l_pos + 1;
695 p_result := NULL;
696 RETURN TRUE;
697 END IF;
698 IF (substr(g_approval_list_string, l_pos-1, 1) <> p_quote_char) THEN
699 IF (p_remove_quotes) THEN
700 l_string := substr(g_approval_list_string, p_start_pos, l_pos-p_start_pos);
701 l_string := REPLACE(l_string, p_quote_char, NULL);
702 p_result := l_string;
703 ELSE
704 p_result := substr(g_approval_list_string, p_start_pos, l_pos-p_start_pos);
705 END IF;
706
707 p_start_pos := l_pos + 1;
708 RETURN TRUE;
709 ELSE
710 l_start_pos := l_pos + 2;
711 END IF;
712 ELSE
713 RETURN FALSE;
714 END IF;
715 END LOOP;
716
717 p_start_pos := l_start_pos;
718 RETURN FALSE;
719 END GetNextToken;
720
721 PROCEDURE VerifyAuthority(p_document_id IN NUMBER,
722 p_document_type IN VARCHAR2,
723 p_document_subtype IN VARCHAR2,
724 p_employee_id IN NUMBER,
725 p_return_code OUT NOCOPY NUMBER,
726 p_error_stack IN OUT NOCOPY po_approvallist_s1.ErrorStackType,
727 p_has_authority OUT NOCOPY BOOLEAN) IS
728
729 l_progress VARCHAR2(10) := '000';
730 l_return_value NUMBER;
731 l_return_code VARCHAR2(25);
732 l_error_msg VARCHAR2(2000);
733
734 -- <Doc Manager Rewrite 11.5.11>
735 l_ret_sts VARCHAR2(1);
736 l_exc_msg VARCHAR2(2000);
737
738 BEGIN
739
740 PO_DOCUMENT_ACTION_PVT.verify_authority(
741 p_document_id => p_document_id
742 , p_document_type => p_document_type
743 , p_document_subtype => p_document_subtype
744 , p_employee_id => p_employee_id
745 , x_return_status => l_ret_sts
746 , x_return_code => l_return_code
747 , x_exception_msg => l_exc_msg
748 , x_auth_failed_msg => l_error_msg
749 );
750
751 IF (l_ret_sts = 'S')
752 THEN
753 l_return_value := 0;
754 ELSE
755 l_return_value := 3;
756 END IF;
757
758 -- <Doc Manager Rewrite 11.5.11 End>
759
760 IF (l_return_value = 0) THEN
761 IF (l_return_code IS NULL) THEN
762 p_has_authority := TRUE;
763 ELSE
764 p_has_authority := FALSE;
765 END IF;
766 p_return_code := 0;
767 ELSE
768 IF (l_return_value = 1) THEN
769 p_return_code := po_approvallist_s1.E_DOC_MGR_TIMEOUT;
770 PushMessage(p_error_stack, 'PO_ALIST_DOC_MGR_FAIL', 'ERR_CODE', l_return_value);
771 ELSIF (l_return_value = 2) THEN
772 p_return_code := po_approvallist_s1.E_DOC_MGR_NOMGR;
773 PushMessage(p_error_stack, 'PO_ALIST_DOC_MGR_FAIL', 'ERR_CODE', l_return_value);
774 ELSE
775 p_return_code := po_approvallist_s1.E_DOC_MGR_OTHER;
776 PushMessage(p_error_stack, 'PO_ALIST_DOC_MGR_FAIL', 'ERR_CODE', l_return_value);
777 END IF;
778 END IF;
779
780 EXCEPTION
781 WHEN OTHERS THEN
782 p_return_code := SQLCODE;
783 PushMessage(p_error_stack, 'PO_ALL_SQL_ERROR', 'ROUTINE', 'VerifyAuthority', 'ERR_NUMBER', l_progress, 'SQL_ERR', SQLERRM(SQLCODE));
784 END VerifyAuthority;
785
786 PROCEDURE PushMessage(p_error_stack IN OUT NOCOPY po_approvallist_s1.ErrorStackType,
787 p_message_name IN VARCHAR2,
788 p_token1 IN VARCHAR2 DEFAULT NULL,
789 p_value1 IN VARCHAR2 DEFAULT NULL,
790 p_token2 IN VARCHAR2 DEFAULT NULL,
791 p_value2 IN VARCHAR2 DEFAULT NULL,
792 p_token3 IN VARCHAR2 DEFAULT NULL,
793 p_value3 IN VARCHAR2 DEFAULT NULL,
794 p_token4 IN VARCHAR2 DEFAULT NULL,
795 p_value4 IN VARCHAR2 DEFAULT NULL,
796 p_token5 IN VARCHAR2 DEFAULT NULL,
797 p_value5 IN VARCHAR2 DEFAULT NULL) IS
798
799 l_index NUMBER;
800 l_count NUMBER;
801
802 BEGIN
803 IF (p_message_name IS NOT NULL) THEN
804 l_index := p_error_stack.LAST;
805 IF (l_index IS NULL) THEN
806 l_index := 1;
807 ELSE
808 l_index := l_index + 1;
809 END IF;
810 l_count := 0;
811 p_error_stack(l_index).message_name := p_message_name;
812 IF (p_token1 IS NOT NULL) THEN
813 p_error_stack(l_index).token1 := p_token1;
814 p_error_stack(l_index).value1 := p_value1;
815 l_count := l_count + 1;
816 IF (p_token2 IS NOT NULL) THEN
817 p_error_stack(l_index).token2 := p_token2;
818 p_error_stack(l_index).value2 := p_value2;
819 l_count := l_count + 1;
820 IF (p_token3 IS NOT NULL) THEN
821 p_error_stack(l_index).token3 := p_token3;
822 p_error_stack(l_index).value3 := p_value3;
823 l_count := l_count + 1;
824 IF (p_token4 IS NOT NULL) THEN
825 p_error_stack(l_index).token4 := p_token4;
826 p_error_stack(l_index).value4 := p_value4;
827 l_count := l_count + 1;
828 IF (p_token5 IS NOT NULL) THEN
829 p_error_stack(l_index).token5 := p_token5;
830 p_error_stack(l_index).value5 := p_value5;
831 l_count := l_count + 1;
832 END IF;
833 END IF;
834 END IF;
835 END IF;
836 END IF;
837 p_error_stack(l_index).number_of_tokens := l_count;
838 END IF;
839
840 END PushMessage;
841
842
843 --------------------------------------------------------------------------------
844 --Start of Comments
845 --Function:
846 -- Find the latest records in po_approval_list_headers and
847 -- po_approval_list_lines for p_existing_requisition_id and
848 -- copy into p_new_requisition_id
849 -- the attributes are not duplicated as they are currently not used in iP approval list
850 -- note, the approval list is re-sequenced if necessary
851 -- so that the sequence number is always starting from 1,2,3...
852
853 --Parameters:
854 --IN:
855 --p_existing_requisition_id
856 -- from requisition header ID
857 --p_new_requisition_id
858 -- to requisition header ID
859 --OUT:
860 --None
861 --End of Comments
862 --------------------------------------------------------------------------------
863 procedure copy_approval_list(p_existing_requisition_id IN NUMBER,
864 p_new_requisition_id IN NUMBER) IS
865
866 l_old_approval_list_header_id NUMBER;
867 l_new_approval_list_header_id NUMBER;
868 l_progress VARCHAR2(100) := '000';
869 l_sequence_number number := 0;
870 l_old_current_sequence_num number := 0;
871 l_new_current_sequence_num number := 0;
872
873 cursor old_approver_c(p_approval_list_header_id NUMBER) IS
874 select approver_id,
875 sequence_num,
876 notification_id,
877 notification_role,
878 responder_id,
879 forward_to_id,
880 mandatory_flag,
881 requires_reapproval_flag,
882 approver_type,
883 status,
884 response_date,
885 comments
886 FROM po_approval_list_lines
887 WHERE approval_list_header_id = p_approval_list_header_id
888 order by approval_list_line_id;
889
890 begin
891
892 l_progress := '001' || p_existing_requisition_id ||'; '|| p_new_requisition_id;
893
894 if (p_new_requisition_id is null or p_existing_requisition_id is null) then
895 return;
896 end if;
897
898 SELECT po_approval_list_headers_s.nextval
899 INTO l_new_approval_list_header_id
900 FROM sys.dual;
901
902 SELECT approval_list_header_id, current_sequence_num
903 INTO l_old_approval_list_header_id, l_old_current_sequence_num
904 FROM po_approval_list_headers
905 WHERE document_id = p_existing_requisition_id
906 AND document_type = 'REQUISITION'
907 AND latest_revision = 'Y';
908
909 DELETE FROM po_approval_list_lines
910 WHERE approval_list_header_id in
911 (select approval_list_header_id
912 FROM po_approval_list_headers
913 WHERE document_id = p_new_requisition_id
914 AND document_type = 'REQUISITION');
915
916 DELETE FROM po_approval_list_headers
917 WHERE document_id = p_new_requisition_id
918 AND document_type = 'REQUISITION';
919
920 l_progress := '002:'||l_old_approval_list_header_id ||';'|| l_old_current_sequence_num;
921
922 SELECT COUNT(1)
923 into l_new_current_sequence_num
924 FROM po_approval_list_lines
925 WHERE sequence_num <= l_old_current_sequence_num and
926 approval_list_header_id = l_old_approval_list_header_id;
927
928 l_progress := '003:'|| l_new_current_sequence_num;
929
930 INSERT INTO po_approval_list_headers (
931 approval_list_header_id,
932 document_id,
933 document_type,
934 document_subtype,
935 revision,
936 current_sequence_num,
937 latest_revision,
938 first_approver_id,
939 approval_path_id,
940 wf_item_type,
941 wf_item_key,
942 created_by,
943 creation_date,
944 last_update_login,
945 last_updated_by,
946 last_update_date)
947 SELECT l_new_approval_list_header_id,
948 p_new_requisition_id,
949 document_type,
950 document_subtype,
951 0, --revision,
952 decode (l_new_current_sequence_num, 0, current_sequence_num, l_new_current_sequence_num),
953 latest_revision,
954 first_approver_id,
955 approval_path_id,
956 wf_item_type,
957 wf_item_key,
958 fnd_global.user_id,
959 SYSDATE,
960 fnd_global.login_id,
961 fnd_global.user_id,
962 SYSDATE
963 FROM po_approval_list_headers
964 WHERE document_id = p_existing_requisition_id
965 AND document_type = 'REQUISITION'
966 AND latest_revision = 'Y';
967
968 l_progress := '004'|| l_new_approval_list_header_id;
969
970 BEGIN
971 FOR approver_rec IN old_approver_c(l_old_approval_list_header_id) LOOP
972 l_sequence_number := l_sequence_number +1;
973 INSERT INTO po_approval_list_lines (
974 approval_list_header_id,
975 approval_list_line_id,
976 next_element_id,
977 approver_id,
978 sequence_num,
979 notification_id,
980 notification_role,
981 responder_id,
982 forward_to_id,
983 mandatory_flag,
984 requires_reapproval_flag,
985 approver_type,
986 status,
987 response_date,
988 comments,
989 created_by,
990 creation_date,
991 last_update_login,
992 last_updated_by,
993 last_update_date)
994 VALUES ( l_new_approval_list_header_id,
995 po_approval_list_lines_s.nextval,
996 null,
997 approver_rec.approver_id,
998 l_sequence_number,
999 approver_rec.notification_id,
1000 approver_rec.notification_role,
1001 approver_rec.responder_id,
1002 approver_rec.forward_to_id,
1003 approver_rec.mandatory_flag,
1004 approver_rec.requires_reapproval_flag,
1005 approver_rec.approver_type,
1006 approver_rec.status,
1007 approver_rec.response_date,
1008 approver_rec.comments,
1009 fnd_global.user_id,
1010 SYSDATE,
1011 fnd_global.login_id,
1012 fnd_global.user_id,
1013 SYSDATE
1014 );
1015 END LOOP;
1016 commit;
1017
1018 EXCEPTION
1019 WHEN NO_DATA_FOUND THEN
1020 NULL;
1021 END;
1022 exception
1023 when others then
1024 l_progress := '005' || SQLERRM(SQLCODE);
1025 raise;
1026 end;
1027
1028 procedure append_saved_adhoc_approver(
1029 p_document_id IN NUMBER,
1030 p_approval_list IN OUT NOCOPY po_approvallist_s1.ApprovalListType) IS
1031
1032 l_approver_id number;
1033
1034 cursor adhoc_approver_c IS
1035 select pal.approver_id
1036 from po_approval_list_lines pal, po_approval_list_headers pah,
1037 po_requisition_headers_all prh
1038 where pal.approval_list_header_id = pah.approval_list_header_id
1039 and pah.latest_revision = 'Y'
1040 and pal.mandatory_flag = 'N'
1041 and approver_type ='USER'
1042 and pal.status is null
1043 and pah.document_id = prh.requisition_header_id
1044 and pah.document_type = 'REQUISITION'
1045 and prh.requisition_header_id = p_document_id
1046 and prh.authorization_status = 'INCOMPLETE'
1047 order by pal.sequence_num asc;
1048
1049 l_index NUMBER;
1050 l_username wf_users.name%TYPE;
1051 l_disp_name wf_users.display_name%TYPE;
1052 l_approval_list_elt po_approvallist_s1.ApprovalListEltType;
1053
1054 BEGIN
1055 l_index := p_approval_list.LAST;
1056 IF (l_index IS NULL) THEN
1057 l_index := 1;
1058 ELSE
1059 l_index := l_index + 1;
1060 END IF;
1061
1062 FOR approver_rec IN adhoc_approver_c LOOP
1063
1064 l_approval_list_elt.id := NULL;
1065 l_approval_list_elt.sequence_num := l_index;
1066
1067 l_approval_list_elt.approver_id := approver_rec.approver_id;
1068 wf_directory.getusername('PER', approver_rec.approver_id, l_username, l_disp_name);
1069 l_approval_list_elt.approver_disp_name := l_disp_name;
1070 l_approval_list_elt.responder_id := NULL;
1071 l_approval_list_elt.responder_disp_name := NULL;
1072
1073 l_approval_list_elt.forward_to_id := NULL;
1074 l_approval_list_elt.forward_to_disp_name := NULL;
1075
1076 l_approval_list_elt.status := NULL;
1077 l_approval_list_elt.approver_type := 'USER';
1078 l_approval_list_elt.mandatory_flag := 'N';
1079
1080 p_approval_list(l_index) := l_approval_list_elt;
1081 l_index := l_index + 1;
1082 END LOOP;
1083
1084 END;
1085
1086
1087 END por_approval_list;