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