[Home] [Help]
PACKAGE BODY: APPS.PO_APPROVALLIST_S1
Source
1 PACKAGE BODY PO_APPROVALLIST_S1 AS
2 /* $Header: POXAPL1B.pls 120.22.12020000.3 2013/02/10 11:42:10 vegajula ship $*/
3
4 -- Private function prototypes:
5
6 --Changes Made For Bug 2605927. Declared a table to store the Superior/Supervisor id's.
7 TYPE SupervisorListType IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
8
9 --Changes Made For Bug 2605927. Changed variable and type passed to the procedure.
10 PROCEDURE GetMgrPOHier(p_employee_id IN NUMBER,
11 p_approval_path_id IN NUMBER,
12 p_return_code OUT NOCOPY NUMBER,
13 p_error_stack IN OUT NOCOPY ErrorStackType,
14 p_superior_list OUT NOCOPY SupervisorListType, -- 2605927
15 p_document_id IN NUMBER,
16 p_document_type IN VARCHAR2,
17 p_document_subtype IN VARCHAR2);
18
19 PROCEDURE GetMgrHRHier(p_employee_id IN NUMBER,
20 p_business_group_id IN NUMBER,
21 p_return_code OUT NOCOPY NUMBER,
22 p_error_stack IN OUT NOCOPY ErrorStackType,
23 p_supervisor_list OUT NOCOPY SupervisorListType); -- 2605927
24
25 PROCEDURE VerifyAuthority(p_document_id IN NUMBER,
26 p_document_type IN VARCHAR2,
27 p_document_subtype IN VARCHAR2,
28 p_employee_id IN NUMBER,
29 p_return_code OUT NOCOPY NUMBER,
30 p_error_stack IN OUT NOCOPY ErrorStackType,
31 p_has_authority OUT NOCOPY BOOLEAN);
32
33 PROCEDURE PushMessage(p_error_stack IN OUT NOCOPY ErrorStackType,
34 p_message_name IN VARCHAR2,
35 p_token1 IN VARCHAR2 DEFAULT NULL,
36 p_value1 IN VARCHAR2 DEFAULT NULL,
37 p_token2 IN VARCHAR2 DEFAULT NULL,
38 p_value2 IN VARCHAR2 DEFAULT NULL,
39 p_token3 IN VARCHAR2 DEFAULT NULL,
40 p_value3 IN VARCHAR2 DEFAULT NULL,
41 p_token4 IN VARCHAR2 DEFAULT NULL,
42 p_value4 IN VARCHAR2 DEFAULT NULL,
43 p_token5 IN VARCHAR2 DEFAULT NULL,
44 p_value5 IN VARCHAR2 DEFAULT NULL);
45
46 PROCEDURE SetMessage(p_error_stack_elt IN ErrorStackEltType);
47
48 -- End of Private function prototypes
49
50 PROCEDURE SetMessage(p_error_stack_elt IN ErrorStackEltType) IS
51 l_num_tokens NUMBER;
52 BEGIN
53 fnd_message.set_name('PO', p_error_stack_elt.message_name);
54 l_num_tokens := p_error_stack_elt.number_of_tokens;
55 IF (l_num_tokens >= 1) THEN
56 fnd_message.set_token(p_error_stack_elt.token1, p_error_stack_elt.value1);
57 IF (l_num_tokens >= 2) THEN
58 fnd_message.set_token(p_error_stack_elt.token2, p_error_stack_elt.value2);
59 IF (l_num_tokens >= 3) THEN
60 fnd_message.set_token(p_error_stack_elt.token3, p_error_stack_elt.value3);
61 IF (l_num_tokens >= 4) THEN
62 fnd_message.set_token(p_error_stack_elt.token4, p_error_stack_elt.value4);
63 IF (l_num_tokens >= 5) THEN
64 fnd_message.set_token(p_error_stack_elt.token5, p_error_stack_elt.value5);
65 END IF;
66 END IF;
67 END IF;
68 END IF;
69 END IF;
70 END SetMessage;
71
72
73 PROCEDURE PushMessage(p_error_stack IN OUT NOCOPY ErrorStackType,
74 p_message_name IN VARCHAR2,
75 p_token1 IN VARCHAR2 DEFAULT NULL,
76 p_value1 IN VARCHAR2 DEFAULT NULL,
77 p_token2 IN VARCHAR2 DEFAULT NULL,
78 p_value2 IN VARCHAR2 DEFAULT NULL,
79 p_token3 IN VARCHAR2 DEFAULT NULL,
80 p_value3 IN VARCHAR2 DEFAULT NULL,
81 p_token4 IN VARCHAR2 DEFAULT NULL,
82 p_value4 IN VARCHAR2 DEFAULT NULL,
83 p_token5 IN VARCHAR2 DEFAULT NULL,
84 p_value5 IN VARCHAR2 DEFAULT NULL) IS
85
86 l_index NUMBER;
87 l_count NUMBER;
88
89 BEGIN
90 IF (p_message_name IS NOT NULL) THEN
91 l_index := p_error_stack.LAST;
92 IF (l_index IS NULL) THEN
93 l_index := 1;
94 ELSE
95 l_index := l_index + 1;
96 END IF;
97 l_count := 0;
98 p_error_stack(l_index).message_name := p_message_name;
99 IF (p_token1 IS NOT NULL) THEN
100 p_error_stack(l_index).token1 := p_token1;
101 p_error_stack(l_index).value1 := p_value1;
102 l_count := l_count + 1;
103 IF (p_token2 IS NOT NULL) THEN
104 p_error_stack(l_index).token2 := p_token2;
105 p_error_stack(l_index).value2 := p_value2;
106 l_count := l_count + 1;
107 IF (p_token3 IS NOT NULL) THEN
108 p_error_stack(l_index).token3 := p_token3;
109 p_error_stack(l_index).value3 := p_value3;
110 l_count := l_count + 1;
111 IF (p_token4 IS NOT NULL) THEN
112 p_error_stack(l_index).token4 := p_token4;
113 p_error_stack(l_index).value4 := p_value4;
114 l_count := l_count + 1;
115 IF (p_token5 IS NOT NULL) THEN
116 p_error_stack(l_index).token5 := p_token5;
117 p_error_stack(l_index).value5 := p_value5;
118 l_count := l_count + 1;
119 END IF;
120 END IF;
121 END IF;
122 END IF;
123 END IF;
124 p_error_stack(l_index).number_of_tokens := l_count;
125 END IF;
126
127 END PushMessage;
128
129
130 PROCEDURE get_default_approval_list(p_first_approver_id IN NUMBER,
131 p_approval_path_id IN NUMBER,
132 p_document_id IN NUMBER,
133 p_document_type IN VARCHAR2,
134 p_document_subtype IN VARCHAR2,
135 p_rebuild_code IN VARCHAR2 DEFAULT 'INITIAL_BUILD',
136 p_return_code OUT NOCOPY NUMBER,
137 p_error_stack IN OUT NOCOPY ErrorStackType,
138 p_approval_list OUT NOCOPY ApprovalListType,
139 p_approver_id IN VARCHAR2 DEFAULT NULL) IS
140
141 l_progress VARCHAR2(10) := '000';
142 l_authorization_status VARCHAR2(25);
143 l_preparer_id NUMBER;
144 l_approval_path_id NUMBER;
145 l_forwarding_mode_code VARCHAR2(25);
146 l_can_preparer_approve_flag VARCHAR2(1);
147 l_use_positions_flag VARCHAR2(1);
148 --Bug 9362974
149 l_is_prepare_not_terminated VARCHAR2(1);
150 l_business_group_id NUMBER;
151 l_document_type_code VARCHAR2(25);
152 l_document_subtype VARCHAR2(25);
153 l_employee_id NUMBER;
154 l_mgr_id NUMBER;
155 l_return_code NUMBER;
156 l_has_authority BOOLEAN;
157 l_count NUMBER := 0;
158 l_index NUMBER := 0;
159 l_username VARCHAR2(100);
160 l_disp_name VARCHAR2(240);
161 l_approval_list_elt ApprovalListEltType;
162 l_approver_type VARCHAR2(30);
163 l_mandatory_flag VARCHAR2(1);
164 l_get_next_approver BOOLEAN := TRUE;
165 l_include_first_approver BOOLEAN := TRUE;
166 --Changes Made For Bug 2605927. Added New variables.
167 l_superior_list SupervisorListType;
168 l_sup_index NUMBER;
169 l_orig_preparer NUMBER;
170 --Bug : 5728521
171 l_saved_first_approver_id NUMBER;
172 --Bug6853017
173 l_approver_id NUMBER;
174 BEGIN
175
176 p_approval_list.DELETE;
177
178 IF (p_rebuild_code = 'FORWARD_RESPONSE' AND
179 p_first_approver_id IS NULL) THEN
180 p_return_code := E_INVALID_FIRST_APPROVER_ID;
181 RETURN;
182 END IF;
183 IF (p_document_type = 'REQUISITION') THEN
184 BEGIN
185 l_progress := '001';
186
187 SELECT preparer_id,
188 authorization_status
189 INTO l_preparer_id,
190 l_authorization_status
191 FROM po_requisition_headers
192 WHERE requisition_header_id = p_document_id;
193
194 EXCEPTION
195 WHEN NO_DATA_FOUND THEN
196 p_return_code := E_INVALID_DOCUMENT_ID;
197 PushMessage(p_error_stack, 'PO_ALIST_INVALID_DOC_ID', 'DOC_ID', p_document_id);
198 RETURN;
199 END;
200 ELSE
201 p_return_code := E_UNSUPPORTED_DOCUMENT_TYPE;
202 PushMessage(p_error_stack, 'PO_ALIST_UNSUPPORTED_DOC_TYPE', 'DOC_TYPE', p_document_type, 'DOC_SUBTYPE', p_document_subtype);
203 RETURN;
204 END IF;
205
206 -- Bug 3246530: The approver id should be considered, if provided.
207 l_orig_preparer := l_preparer_id;
208 l_preparer_id := nvl(p_approver_id,l_preparer_id);
209
210 l_progress := '002';
211 -- Bug 9362974
212 if ( l_preparer_id is not null ) then
213 begin
214 SELECT 'Y'
215 INTO l_is_prepare_not_terminated
216 FROM po_workforce_current_x
217 WHERE person_id = l_preparer_id ;
218
219 EXCEPTION
220 WHEN NO_DATA_FOUND THEN
221 l_is_prepare_not_terminated := 'N';
222 END;
223
224 end if;
225 --Bug 9362974
226
227 SELECT NVL(p_approval_path_id, default_approval_path_id),
228 forwarding_mode_code,
229 NVL(can_preparer_approve_flag, 'N')
230 INTO l_approval_path_id,
231 l_forwarding_mode_code,
232 l_can_preparer_approve_flag
233 FROM po_document_types podt
234 WHERE podt.document_type_code = p_document_type
235 AND podt.document_subtype = p_document_subtype;
236
237 l_progress := '003';
238
239 SELECT NVL(use_positions_flag, 'N'),
240 business_group_id
241 INTO l_use_positions_flag,
242 l_business_group_id
243 FROM financials_system_parameters;
244
245
246 IF (l_authorization_status = 'PRE-APPROVED'
247 AND p_rebuild_code = 'FORWARD_RESPONSE') THEN
248 l_get_next_approver := FALSE;
249 ELSE
250 IF ( ( ( l_can_preparer_approve_flag = 'Y') OR (nvl(p_approver_id,l_orig_preparer) <> l_orig_preparer) ) and l_is_prepare_not_terminated ='Y' )THEN -- Bug 3246530 , 9362974
251
252 l_progress := '004';
253 VerifyAuthority(p_document_id=>p_document_id,
254 p_document_type=>p_document_type,
255 p_document_subtype=>p_document_subtype,
256 p_employee_id=>l_preparer_id,
257 p_return_code=>l_return_code,
258 p_error_stack=>p_error_stack,
259 p_has_authority=>l_has_authority);
260 IF (l_return_code <> E_SUCCESS) THEN
261 p_return_code := l_return_code;
262 RETURN;
263 END IF;
264 IF (l_has_authority) THEN
265 IF (p_first_approver_id IS NOT NULL) THEN
266 l_get_next_approver := FALSE;
267 l_include_first_approver := TRUE;
268 ELSE
269 p_return_code := E_SUCCESS;
270 RETURN;
271 END IF;
272 ELSE
273 l_get_next_approver := TRUE;
274 END IF;
275 ELSE
276 l_get_next_approver := TRUE;
277 END IF;
278 END IF;
279 --Begin bug 13843060
280 IF (p_first_approver_id IS NOT NULL AND p_first_approver_id<>l_orig_preparer ) THEN
281 --End bug 13843060
282 -- check if we are doing forward or the preparer, who has the authority to approve the
283 -- document, forward the document to someone else
284 IF (p_rebuild_code = 'FORWARD_RESPONSE' OR
285 (p_rebuild_code = 'INITIAL_BUILD' AND
286 p_first_approver_id IS NOT NULL AND
287 l_get_next_approver = FALSE)) THEN
288 l_approver_type := 'FORWARD';
289 ELSE
290 l_approver_type := 'SYSTEM';
291 END IF;
292 IF (is_approver_valid(p_document_id=>p_document_id,
293 p_document_type=>p_document_type,
294 p_document_subtype=>p_document_subtype,
295 p_approver_id=>p_first_approver_id,
296 p_approver_type=>l_approver_type) = FALSE) THEN
297 p_return_code := E_INVALID_FIRST_APPROVER_ID;
298 PushMessage(p_error_stack, 'PO_ALIST_INVALID_FIRST_APPR', 'APPROVER', l_saved_first_approver_id);
299 RETURN;
300 END IF;
301
302 l_progress := '007';
303
304 IF (l_get_next_approver = TRUE) THEN
305 VerifyAuthority(p_document_id=>p_document_id,
306 p_document_type=>p_document_type,
307 p_document_subtype=>p_document_subtype,
308 p_employee_id=>p_first_approver_id,
309 p_return_code=>l_return_code,
310 p_error_stack=>p_error_stack,
311 p_has_authority=>l_has_authority);
312 IF (l_return_code <> E_SUCCESS) THEN
313 -- p_approval_list.DELETE;
314 p_return_code := l_return_code;
315 RETURN;
316 END IF;
317
318 IF (l_has_authority = TRUE ) THEN
319 l_get_next_approver := FALSE;
320 ELSE
321 IF (l_forwarding_mode_code = 'HIERARCHY') THEN
322 l_include_first_approver := TRUE;
323 ELSE
324 l_include_first_approver := FALSE;
325 END IF;
326 END IF;
327 END IF;
328
329 -- Note that we check the rebuild_code prior to l_include_first_approver
330 IF (p_rebuild_code = 'FORWARD_RESPONSE' OR
331 l_include_first_approver = TRUE) THEN
332 l_index := l_index + 1;
333 l_approval_list_elt.id := NULL;
334 l_approval_list_elt.sequence_num := l_index;
335
336 wf_directory.getusername('PER', p_first_approver_id, l_username, l_disp_name);
337 l_approval_list_elt.approver_id := p_first_approver_id;
338 l_approval_list_elt.approver_disp_name := l_disp_name;
339
340 l_approval_list_elt.responder_id := NULL;
341 l_approval_list_elt.responder_disp_name := NULL;
342
343 l_approval_list_elt.forward_to_id := NULL;
344 l_approval_list_elt.forward_to_disp_name := NULL;
345
346 l_approval_list_elt.status := NULL;
347 l_approval_list_elt.approver_type := l_approver_type;
348
349 IF (is_approver_mandatory(p_document_id=>p_document_id,
350 p_document_type=>p_document_type,
351 p_document_subtype=>p_document_subtype,
352 p_preparer_id=>l_preparer_id,
353 p_approver_id=>l_approval_list_elt.approver_id,
354 p_approver_type=>l_approval_list_elt.approver_type) = TRUE) THEN
355 l_approval_list_elt.mandatory_flag := 'Y';
356 ELSE
357 l_approval_list_elt.mandatory_flag := 'N';
358 END IF;
359
360 p_approval_list(l_index) := l_approval_list_elt;
361 END IF;
362 END IF;
363
364 IF (l_get_next_approver = FALSE) THEN
365 p_return_code := E_SUCCESS;
366 RETURN;
367 END IF;
368
369 l_progress := '010';
370
371 l_employee_id := NVL(p_first_approver_id, l_preparer_id);
372
373 IF (l_use_positions_flag = 'Y') THEN
374 l_progress := '011.'||to_char(l_count);
375 --Changes Made For Bug 2605927. Changed the calls and now getting TABLE in return.
376 GetMgrPOHier(p_employee_id=>l_employee_id,
377 p_approval_path_id=>l_approval_path_id,
378 p_return_code=>l_return_code,
379 p_error_stack=>p_error_stack,
380 p_superior_list=>l_superior_list, -- 2605927
381 p_document_id=>p_document_id,
382 p_document_type=>p_document_type,
383 p_document_subtype=>p_document_subtype);
384 ELSE
385 l_progress := '012.'||to_char(l_count);
386 GetMgrHRHier(p_employee_id=>l_employee_id,
387 p_business_group_id=>l_business_group_id,
388 p_return_code=>l_return_code,
389 p_error_stack=>p_error_stack,
390 p_supervisor_list=>l_superior_list); -- 2605927
391 END IF;
392
393 IF (l_return_code = E_NO_SUPERVISOR_FOUND) THEN
394 p_return_code := E_NO_ONE_HAS_AUTHORITY;
395 RETURN;
396 ELSIF (l_return_code <> E_SUCCESS) THEN
397 -- p_approval_list.DELETE;
398 p_return_code := l_return_code;
399 RETURN;
400 END IF;
401 --Changes Made For Bug 2605927. Loop thru the number of records in the table and access
402 --the superior_id from the list.
403
404 l_sup_index := l_superior_list.FIRST;
405 WHILE (l_sup_index IS NOT NULL) LOOP
406 l_progress := '013.'||to_char(l_count);
407 IF (is_approver_valid(p_document_id=>p_document_id,
408 p_document_type=>p_document_type,
409 p_document_subtype=>p_document_subtype,
410 p_approver_id=>l_superior_list(l_sup_index), -- 2605927
411 p_approver_type=>'SYSTEM') = TRUE) THEN
412
413 l_progress := '014.'||to_char(l_count);
414 VerifyAuthority(p_document_id=>p_document_id,
415 p_document_type=>p_document_type,
416 p_document_subtype=>p_document_subtype,
417 p_employee_id=>l_superior_list(l_sup_index), -- 2605927
418 p_return_code=>l_return_code,
419 p_error_stack=>p_error_stack,
420 p_has_authority=>l_has_authority);
421 IF (l_return_code <> E_SUCCESS) THEN
422 -- p_approval_list.DELETE;
423 p_return_code := l_return_code;
424 RETURN;
425 END IF;
426
427 IF (l_has_authority = TRUE OR
428 l_forwarding_mode_code = 'HIERARCHY') THEN
429 l_progress := '015.'||to_char(l_count);
430
431 l_index := l_index + 1;
432
433 --Changes Made For Bug 2605927. Passing superior_id using the table.
434 wf_directory.getusername('PER',l_superior_list(l_sup_index), l_username, l_disp_name);
435 -- Make sure every field is reset since we reuse the same record
436 l_approval_list_elt.id := NULL;
437 l_approval_list_elt.sequence_num := l_index;
438
439 l_approval_list_elt.approver_id := l_superior_list(l_sup_index);
440 l_approval_list_elt.approver_disp_name := l_disp_name;
441
442 l_approval_list_elt.responder_id := NULL;
443 l_approval_list_elt.responder_disp_name := NULL;
444
445 l_approval_list_elt.forward_to_id := NULL;
446 l_approval_list_elt.forward_to_disp_name := NULL;
447
448 l_approval_list_elt.status := NULL;
449 l_approval_list_elt.approver_type := 'SYSTEM';
450
451 IF (is_approver_mandatory(p_document_id=>p_document_id,
452 p_document_type=>p_document_type,
453 p_document_subtype=>p_document_subtype,
454 p_preparer_id=>l_preparer_id,
455 p_approver_id=>l_approval_list_elt.approver_id,
456 p_approver_type=>l_approval_list_elt.approver_type) = TRUE) THEN
457 l_approval_list_elt.mandatory_flag := 'Y';
458 ELSE
459 l_approval_list_elt.mandatory_flag := 'N';
460 END IF;
461
462 p_approval_list(l_index) := l_approval_list_elt;
463 END IF;
464 IF (l_has_authority = TRUE) THEN
465 p_return_code := E_SUCCESS;
466 RETURN;
467 END IF;
468 END IF;
469 l_count := l_count+1;
470 --Changes Made For Bug 2605927. Increment the index.
471 l_employee_id := l_superior_list(l_sup_index);
472 l_sup_index := l_superior_list.NEXT(l_sup_index);
473 END LOOP;
474
475 p_return_code := E_NO_ONE_HAS_AUTHORITY;
476
477 EXCEPTION
478 WHEN OTHERS THEN
479 -- p_approval_list.DELETE;
480 p_return_code := SQLCODE;
481 PushMessage(p_error_stack, 'PO_ALL_SQL_ERROR', 'ROUTINE', 'GET_DEFAULT_APPROVAL_LIST', 'ERR_NUMBER', l_progress, 'SQL_ERR', SQLERRM(SQLCODE));
482 END get_default_approval_list;
483
484
485 --Changes Made For Bug 2605927. Changed the parameter variable and the type.
486 PROCEDURE GetMgrPOHier(p_employee_id IN NUMBER,
487 p_approval_path_id IN NUMBER,
488 p_return_code OUT NOCOPY NUMBER,
489 p_error_stack IN OUT NOCOPY ErrorStackType,
490 p_superior_list OUT NOCOPY SupervisorListType, -- 2605927
491 p_document_id IN NUMBER,
492 p_document_type IN VARCHAR2,
493 p_document_subtype IN VARCHAR2) IS
494
495 /* Bug 2437175
496 Added the LEADING(POEH) hint to get better execution plan */
497
498 CURSOR c_po_hier(p_employee_id NUMBER, p_approval_path_id NUMBER) IS
499 SELECT /*+ LEADING(POEH) */ poeh.superior_id, poeh.superior_level, hrec.full_name
500 FROM hr_employees_current_v hrec,
501 po_employee_hierarchies poeh
502 WHERE poeh.position_structure_id = p_approval_path_id
503 AND poeh.employee_id = p_employee_id
504 AND hrec.employee_id = poeh.superior_id
505 AND poeh.superior_level > 0
506 UNION ALL
507 SELECT /*+ LEADING(POEH) */ poeh.superior_id, poeh.superior_level, cwk.full_name
508 FROM per_cont_workers_current_x cwk,
509 po_employee_hierarchies poeh
510 WHERE poeh.position_structure_id = p_approval_path_id
511 AND poeh.employee_id = p_employee_id
512 AND cwk.person_id = poeh.superior_id
513 AND poeh.superior_level > 0
514 AND nvl(fnd_profile.value('HR_TREAT_CWK_AS_EMP'),'N') = 'Y'
515 ORDER BY superior_level, full_name;
516
517 --Changes Made For Bug 2605927. Added new variables.
518
519 -- bug3608697: increased the size of l_progress
520 l_progress VARCHAR2(300) := '000';
521 l_superior_id NUMBER := NULL;
522 l_superior_level NUMBER := NULL;
523 l_full_name VARCHAR2(240) := '000';
524
525 l_previous_superior_level NUMBER := -1;
526 l_count NUMBER := 0;
527 l_ind NUMBER := 1;
528
529 BEGIN
530
531 l_progress := '001';
532 p_superior_list.DELETE;
533
534 OPEN c_po_hier(p_employee_id, p_approval_path_id);
535 LOOP
536 FETCH c_po_hier INTO l_superior_id, l_superior_level, l_full_name;
537 EXIT WHEN c_po_hier%NOTFOUND;
538
539 -- bug3608697
540 -- removed the line that concatenates '002' and to_char(l_superior_id)
541
542 l_progress := '003 ' || to_char(l_superior_level);
543 IF (is_approver_valid(p_document_id=>p_document_id,
544 p_document_type=>p_document_type,
545 p_document_subtype=>p_document_subtype,
546 p_approver_id=>l_superior_id,
547 p_approver_type=>'SYSTEM') = TRUE) THEN
548 l_progress := '004 ' || to_char(l_previous_superior_level);
549 IF (l_superior_level > l_previous_superior_level) then
550 p_superior_list(l_ind) := l_superior_id;
551 l_previous_superior_level := l_superior_level;
552 l_ind := l_ind+1;
553 p_return_code := E_SUCCESS;
554 END IF;
555 END IF;
556 END LOOP;
557 l_progress := '005';
558 CLOSE c_po_hier;
559 --check if all the superiors are not valid users then give error ???
560 --Changes Made For Bug 2605927. If no superior then........
561 IF(p_superior_list.COUNT = 0) THEN
562 l_progress := '006';
563 p_return_code := E_NO_SUPERVISOR_FOUND;
564 PushMessage(p_error_stack, 'PO_ALIST_NO_SUPERVISOR', 'EMP_ID', p_employee_id);
565 END IF;
566
567 EXCEPTION
568 WHEN OTHERS THEN
569 IF (c_po_hier%ISOPEN) THEN
570 CLOSE c_po_hier;
571 END IF;
572 p_superior_list.DELETE;
573 p_return_code := SQLCODE;
574 PushMessage(p_error_stack, 'PO_ALL_SQL_ERROR', 'ROUTINE', 'GetMgrPOHier', 'ERR_NUMBER', l_progress, 'SQL_ERR', SQLERRM(SQLCODE));
575 END GetMgrPOHier;
576
577 PROCEDURE GetLoopHRHier(p_employee_id IN NUMBER,
578 p_business_group_id IN NUMBER,
579 p_return_code OUT NOCOPY NUMBER,
580 p_error_stack IN OUT NOCOPY ErrorStackType,
581 p_supervisor_list OUT NOCOPY SupervisorListType) IS
582 CURSOR c_hr_hier(p_employee_id NUMBER, p_business_group_id NUMBER) IS
583 SELECT pera.supervisor_id
584 FROM per_assignments_f pera
585 WHERE trunc(SYSDATE) BETWEEN pera.effective_start_date AND pera.effective_end_date
586 AND pera.person_id = p_employee_id
587 AND pera.primary_flag = 'Y'
588 AND pera.ASSIGNMENT_TYPE IN ('E','C')-- bug 12388225
589 AND EXISTS
590 (SELECT '1'
591 FROM per_people_f PERF, per_assignments_f PERA1
592 WHERE trunc(sysdate) BETWEEN PERF.effective_start_date
593 AND PERF.effective_end_date
594 AND PERF.person_id = PERA.supervisor_id
595 AND PERA1.person_id = PERF.person_id
596 AND trunc(SYSDATE) BETWEEN PERA1.effective_start_date
597 AND PERA1.effective_end_date
598 AND PERA1.primary_flag = 'Y'
599 AND PERA1.ASSIGNMENT_TYPE IN ('E','C')-- bug 12388225
600 AND EXISTS
601 (SELECT '1'
602 FROM per_person_types PPT
603 WHERE PPT.system_person_type IN ('EMP','EMP_APL','CWK') --<R12 CWK Enhancemment>
604 AND PPT.person_type_id = PERF.person_type_id));
605
606 l_progress VARCHAR2(10) := '000';
607 l_supervisor_id NUMBER := NULL;
608 l_employee_id NUMBER := p_employee_id;
609 l_index NUMBER :=1;
610 l_loop_index NUMBER :=1;
611 l_found_loop BOOLEAN := false;
612
613 BEGIN
614
615 l_progress := '001';
616
617 p_supervisor_list.DELETE;
618
619 LOOP
620 OPEN c_hr_hier(l_employee_id, p_business_group_id);
621 FETCH c_hr_hier INTO l_supervisor_id;
622 EXIT WHEN c_hr_hier%NOTFOUND;
623 l_progress := '002';
624
625 IF c_hr_hier%FOUND AND l_supervisor_id IS NOT NULL THEN
626 l_loop_index := p_supervisor_list.FIRST;
627
628 WHILE (l_loop_index IS NOT NULL) LOOP
629
630 IF (l_supervisor_id = p_supervisor_list(l_loop_index)) THEN
631 l_found_loop := true;
632 exit;
633 END IF;
634 l_loop_index := p_supervisor_list.NEXT(l_loop_index);
635
636 END LOOP;
637
638 IF l_found_loop THEN
639 exit;
640
641 END IF;
642 p_supervisor_list(l_index) := l_supervisor_id;
643 l_employee_id := l_supervisor_id;
644
645 l_index := l_index +1;
646 p_return_code := E_SUCCESS;
647 END IF;
648 CLOSE c_hr_hier;
649 END LOOP;
650
651 IF l_index <= 1 THEN
652 p_return_code := E_NO_SUPERVISOR_FOUND;
653 PushMessage(p_error_stack, 'PO_ALIST_NO_SUPERVISOR', 'EMP_ID', p_employee_id);
654 END IF;
655
656 l_progress := '003';
657 CLOSE c_hr_hier;
658
659 EXCEPTION
660
661 WHEN OTHERS THEN
662 IF (c_hr_hier%ISOPEN) THEN
663 CLOSE c_hr_hier;
664 END IF;
665 p_supervisor_list.DELETE;
666 p_return_code := SQLCODE;
667 PushMessage(p_error_stack, 'PO_ALL_SQL_ERROR', 'ROUTINE', 'GetMgrHRHier', 'ERR_NUMBER', l_progress, 'SQL_ERR', SQLERRM(SQLCODE));
668 END GetLoopHRHier;
669
670
671 --Changes Made For Bug 2605927.Changed the parameter variable and the type.
672 PROCEDURE GetMgrHRHier(p_employee_id IN NUMBER,
673 p_business_group_id IN NUMBER,
674 p_return_code OUT NOCOPY NUMBER,
675 p_error_stack IN OUT NOCOPY ErrorStackType,
676 p_supervisor_list OUT NOCOPY SupervisorListType) IS -- 2605927
677
678 /* Bug# 1775520:
679 ** Desc: Changed the cursor c_hr_hier to check that the primary
680 ** assignment of the supervisor is valid and that the system_person_type
681 ** is an 'EMP'
682 */
683 /* Bug# 2460162: kagarwal
684 ** Desc: When we get the supervisor of an employee, we should be choosing
685 ** the supervisor from the currently active primary assignment.
686 **
687 ** Added condition pera.person_id = p_employee_id to SQL
688 */
689 /* Bug 2605927. Changed the SQL and to construct the list */
690
691 /* Bug 2794501. When selecting the records from per_assignments_f only the
692 records corresponding to assignment_type 'E' should be selected */
693
694 CURSOR c_hr_hier(p_employee_id NUMBER, p_business_group_id NUMBER) IS
695 SELECT pera.supervisor_id
696 FROM per_assignments_f pera
697 WHERE
698 EXISTS
699 (SELECT '1'
700 FROM per_people_f PERF, per_assignments_f PERA1
701 WHERE trunc(sysdate) BETWEEN PERF.effective_start_date
702 AND PERF.effective_end_date
703 AND PERF.person_id = PERA.supervisor_id
704 AND PERA1.person_id = PERF.person_id
705 AND trunc(SYSDATE) BETWEEN PERA1.effective_start_date
706 AND PERA1.effective_end_date
707 AND PERA1.primary_flag = 'Y'
708 AND PERA1.ASSIGNMENT_TYPE IN ('E','C') --<R12 CWK Enhancemment>
709 AND EXISTS
710 (SELECT '1'
711 FROM per_person_types PPT ,per_person_type_usages_f pptu
712 WHERE PPT.system_person_type IN ('EMP','EMP_APL','CWK') --<R12 CWK Enhancemment>
713 AND PPT.person_type_id = pptu.person_type_id
714 AND pptu.Person_Id = PERF.person_id
715 ))
716 START WITH pera.person_id = p_employee_id
717 AND trunc(SYSDATE) BETWEEN pera.effective_start_date
718 AND pera.effective_end_date
719 AND pera.primary_flag = 'Y'
720 AND PERA.ASSIGNMENT_TYPE IN ('E','C') --<R12 CWK Enhancemment>
721 CONNECT BY PRIOR pera.supervisor_id = pera.person_id
722 AND trunc(SYSDATE) BETWEEN pera.effective_start_date
723 AND pera.effective_end_date
724 AND pera.primary_flag = 'Y'
725 AND PERA.ASSIGNMENT_TYPE IN ('E','C'); --<R12 CWK Enhancemment>
726
727 l_progress VARCHAR2(10) := '000';
728 l_supervisor_id NUMBER := NULL;
729 l_index NUMBER :=1;
730
731 --Handle exception
732 --ORA-01436: CONNECT BY loop in user data
733 loop_in_hierarchy EXCEPTION;
734 PRAGMA EXCEPTION_INIT(loop_in_hierarchy, -01436);
735
736 BEGIN
737
738 l_progress := '001';
739 p_supervisor_list.DELETE;
740
741 OPEN c_hr_hier(p_employee_id, p_business_group_id);
742 LOOP
743 FETCH c_hr_hier INTO l_supervisor_id;
744 EXIT WHEN c_hr_hier%NOTFOUND;
745 l_progress := '002';
746 IF c_hr_hier%FOUND AND l_supervisor_id IS NOT NULL THEN
747 p_supervisor_list(l_index) := l_supervisor_id;
748 l_index := l_index +1;
749 p_return_code := E_SUCCESS;
750 END IF;
751 END LOOP;
752
753 IF l_index <= 1 THEN
754 p_return_code := E_NO_SUPERVISOR_FOUND;
755 PushMessage(p_error_stack, 'PO_ALIST_NO_SUPERVISOR', 'EMP_ID', p_employee_id);
756 END IF;
757
758 l_progress := '003';
759 CLOSE c_hr_hier;
760
761 EXCEPTION
762 WHEN loop_in_hierarchy THEN
763 IF (c_hr_hier%ISOPEN) THEN
764 CLOSE c_hr_hier;
765 END IF;
766 GetLoopHRHier(p_employee_id=>p_employee_id,
767 p_business_group_id=>p_business_group_id,
768 p_return_code=>p_return_code,
769 p_error_stack=>p_error_stack,
770 p_supervisor_list=>p_supervisor_list);
771 WHEN OTHERS THEN
772 IF (c_hr_hier%ISOPEN) THEN
773 CLOSE c_hr_hier;
774 END IF;
775 p_supervisor_list.DELETE;
776 p_return_code := SQLCODE;
777 PushMessage(p_error_stack, 'PO_ALL_SQL_ERROR', 'ROUTINE', 'GetMgrHRHier', 'ERR_NUMBER', l_progress, 'SQL_ERR', SQLERRM(SQLCODE));
778 END GetMgrHRHier;
779
780 PROCEDURE VerifyAuthority(p_document_id IN NUMBER,
781 p_document_type IN VARCHAR2,
782 p_document_subtype IN VARCHAR2,
783 p_employee_id IN NUMBER,
784 p_return_code OUT NOCOPY NUMBER,
785 p_error_stack IN OUT NOCOPY ErrorStackType,
786 p_has_authority OUT NOCOPY BOOLEAN) IS
787
788 l_progress VARCHAR2(10) := '000';
789 l_return_value NUMBER;
790 l_return_code VARCHAR2(25);
791 l_error_msg VARCHAR2(2000);
792
793 -- <Doc Manager Rewrite 11.5.11>
794 l_ret_sts VARCHAR2(1);
795 l_exc_msg VARCHAR2(2000);
796
797 BEGIN
798
799 -- <Doc Manager Rewrite 11.5.11 Start>
800 -- Use PO_DOCUMENT_ACTION_PVT method instead of po_document_actions_sv
801
802 PO_DOCUMENT_ACTION_PVT.verify_authority(
803 p_document_id => p_document_id
804 , p_document_type => p_document_type
805 , p_document_subtype => p_document_subtype
806 , p_employee_id => p_employee_id
807 , x_return_status => l_ret_sts
808 , x_return_code => l_return_code
809 , x_exception_msg => l_exc_msg
810 , x_auth_failed_msg => l_error_msg
811 );
812
813 IF (l_ret_sts = 'S')
814 THEN
815 l_return_value := 0;
816 ELSE
817 l_return_value := 3;
818 END IF;
819
820 -- <Doc Manager Rewrite 11.5.11 End>
821
822 IF (l_return_value = 0) THEN
823 IF (l_return_code IS NULL) THEN
824 p_has_authority := TRUE;
825 ELSE
826 p_has_authority := FALSE;
827 END IF;
828 p_return_code := 0;
829 ELSE
830 IF (l_return_value = 1) THEN
831 p_return_code := E_DOC_MGR_TIMEOUT;
832 PushMessage(p_error_stack, 'PO_ALIST_DOC_MGR_FAIL', 'ERR_CODE', l_return_value);
833 ELSIF (l_return_value = 2) THEN
834 p_return_code := E_DOC_MGR_NOMGR;
835 PushMessage(p_error_stack, 'PO_ALIST_DOC_MGR_FAIL', 'ERR_CODE', l_return_value);
836 ELSE
837 p_return_code := E_DOC_MGR_OTHER;
838 PushMessage(p_error_stack, 'PO_ALIST_DOC_MGR_FAIL', 'ERR_CODE', l_return_value);
839 END IF;
840 END IF;
841
842 EXCEPTION
843 WHEN OTHERS THEN
844 p_return_code := SQLCODE;
845 PushMessage(p_error_stack, 'PO_ALL_SQL_ERROR', 'ROUTINE', 'VerifyAuthority', 'ERR_NUMBER', l_progress, 'SQL_ERR', SQLERRM(SQLCODE));
846 END VerifyAuthority;
847
848
849 PROCEDURE get_latest_approval_list(p_document_id IN NUMBER,
850 p_document_type IN VARCHAR2,
851 p_document_subtype IN VARCHAR2,
852 p_return_code OUT NOCOPY NUMBER,
853 p_error_stack OUT NOCOPY ErrorStackType,
854 p_approval_list_header_id OUT NOCOPY NUMBER,
855 p_last_update_date OUT NOCOPY DATE,
856 p_approval_list OUT NOCOPY ApprovalListType) IS
857
858 CURSOR c_approval_list_lines (p_approval_list_header_id NUMBER) IS
859 SELECT approval_list_line_id,
860 sequence_num,
861 approver_id,
862 responder_id,
863 forward_to_id,
864 status,
865 response_date,
866 mandatory_flag,
867 approver_type
868 FROM po_approval_list_lines
869 WHERE approval_list_header_id = p_approval_list_header_id
870 ORDER BY sequence_num;
871
872 l_progress VARCHAR2(10) := '000';
873 l_index NUMBER;
874 l_approval_list_header_id NUMBER;
875 l_current_sequence_num NUMBER;
876 l_last_update_date DATE;
877 l_approval_list_elt ApprovalListEltType;
878 l_username VARCHAR2(100);
879
880 BEGIN
881
882 l_progress := '001';
883
884 SELECT approval_list_header_id,
885 NVL(current_sequence_num, 0),
886 last_update_date
887 INTO l_approval_list_header_id,
888 l_current_sequence_num,
889 l_last_update_date
890 FROM po_approval_list_headers
891 WHERE document_id = p_document_id
892 AND document_type = p_document_type
893 AND document_subtype = p_document_subtype
894 AND latest_revision = 'Y';
895
896 p_approval_list.DELETE;
897 l_index := 1;
898
899 l_progress := '002';
900 OPEN c_approval_list_lines(l_approval_list_header_id);
901 LOOP
902
903 l_progress := '003.'||to_char(l_index);
904
905 FETCH c_approval_list_lines INTO
906 l_approval_list_elt.id,
907 l_approval_list_elt.sequence_num,
908 l_approval_list_elt.approver_id,
909 l_approval_list_elt.responder_id,
910 l_approval_list_elt.forward_to_id,
911 l_approval_list_elt.status,
912 l_approval_list_elt.response_date,
913 l_approval_list_elt.mandatory_flag,
914 l_approval_list_elt.approver_type;
915 EXIT WHEN c_approval_list_lines%NOTFOUND;
916
917 IF (l_approval_list_elt.approver_id IS NOT NULL) THEN
918 l_progress := '004.'||to_char(l_index);
919 wf_directory.getusername('PER',
920 l_approval_list_elt.approver_id,
921 l_username,
922 l_approval_list_elt.approver_disp_name);
923 END IF;
924 IF (l_approval_list_elt.responder_id IS NOT NULL) THEN
925 l_progress := '005.'||to_char(l_index);
926 wf_directory.getusername('PER',
927 l_approval_list_elt.responder_id,
928 l_username,
929 l_approval_list_elt.responder_disp_name);
930 END IF;
931 IF (l_approval_list_elt.forward_to_id IS NOT NULL) THEN
932 l_progress := '006.'||to_char(l_index);
933 wf_directory.getusername('PER',
934 l_approval_list_elt.forward_to_id,
935 l_username,
936 l_approval_list_elt.forward_to_disp_name);
937 END IF;
938 IF (l_approval_list_elt.status IS NULL AND
939 l_approval_list_elt.sequence_num = l_current_sequence_num) THEN
940 l_approval_list_elt.status := 'PENDING';
941 END IF;
942
943 p_approval_list(l_index) := l_approval_list_elt;
944 l_index := l_index + 1;
945
946 END LOOP;
947
948 CLOSE c_approval_list_lines;
949 p_approval_list_header_id := l_approval_list_header_id;
950 p_last_update_date := l_last_update_date;
951 p_return_code := E_SUCCESS;
952
953 EXCEPTION
954 WHEN OTHERS THEN
955 IF (c_approval_list_lines%ISOPEN) THEN
956 CLOSE c_approval_list_lines;
957 END IF;
958 p_approval_list.DELETE;
959 p_approval_list_header_id := NULL;
960 p_last_update_date := NULL;
961 p_return_code := SQLCODE;
962 PushMessage(p_error_stack, 'PO_ALL_SQL_ERROR', 'ROUTINE', 'GET_LATEST_APPROVAL_LIST', 'ERR_NUMBER', l_progress, 'SQL_ERR', SQLERRM(SQLCODE));
963 END get_latest_approval_list;
964
965
966
967 PROCEDURE save_approval_list(p_document_id IN NUMBER,
968 p_document_type IN VARCHAR2,
969 p_document_subtype IN VARCHAR2,
970 p_first_approver_id IN NUMBER,
971 p_approval_path_id IN NUMBER,
972 p_approval_list IN ApprovalListType,
973 p_last_update_date IN DATE,
974 p_approval_list_header_id IN OUT NOCOPY NUMBER,
975 p_return_code OUT NOCOPY NUMBER,
976 p_error_stack OUT NOCOPY ErrorStackType) IS
977
978 CURSOR c_lock_approval_list_lines(p_approval_list_header_id NUMBER) IS
979 SELECT approval_list_line_id
980 FROM po_approval_list_lines
981 WHERE approval_list_header_id = p_approval_list_header_id
982 FOR UPDATE NOWAIT;
983
984 l_progress VARCHAR2(10) := '000';
985 l_return_code NUMBER;
986 l_old_approval_list_header_id NUMBER := NULL;
987 l_old_revision NUMBER := NULL;
988 l_old_current_sequence_num NUMBER := NULL;
989 l_old_first_approver_id NUMBER := NULL;
990 l_old_approval_path_id NUMBER := NULL;
991 l_old_wf_item_type VARCHAR2(8) := NULL;
992 l_old_wf_item_key VARCHAR2(240) := NULL;
993 l_old_last_update_date DATE := NULL;
994 l_new_approval_list_header_id NUMBER;
995 l_index NUMBER;
996 l_flag VARCHAR2(1);
997
998 BEGIN
999
1000 l_progress := '001';
1001
1002 IF (p_approval_list_header_id IS NOT NULL) THEN
1003 BEGIN
1004 SELECT last_update_date
1005 INTO l_old_last_update_date
1006 FROM po_approval_list_headers
1007 WHERE document_id = p_document_id
1008 AND document_type = p_document_type
1009 AND document_subtype = p_document_subtype
1010 AND approval_list_header_id = p_approval_list_header_id
1011 AND latest_revision = 'Y';
1012
1013 IF (l_old_last_update_date <> p_last_update_date) THEN
1014 p_return_code := E_LIST_MODIFIED_SINCE_RETRIEVE;
1015 PushMessage(p_error_stack, 'PO_ALIST_LIST_MODIFIED', 'LIST_ID', p_approval_list_header_id);
1016 RETURN;
1017 END IF;
1018
1019 EXCEPTION
1020 WHEN NO_DATA_FOUND THEN
1021 p_return_code := E_INVALID_LIST_HEADER_ID;
1022 PushMessage(p_error_stack, 'PO_ALIST_INVALID_LIST_HDR_ID', 'LIST_ID', p_approval_list_header_id);
1023 RETURN;
1024 END;
1025 END IF;
1026
1027 l_progress := '002';
1028
1029 validate_approval_list(p_document_id=>p_document_id,
1030 p_document_type=>p_document_type,
1031 p_document_subtype=>p_document_subtype,
1032 p_approval_list=>p_approval_list,
1033 p_current_sequence_num=>NVL(l_old_current_sequence_num, 0),
1034 p_return_code=>l_return_code,
1035 p_error_stack=>p_error_stack);
1036 IF (l_return_code <> E_SUCCESS) THEN
1037 p_return_code := E_INVALID_APPROVAL_LIST;
1038 RETURN;
1039 END IF;
1040
1041 BEGIN
1042
1043 SAVEPOINT SAVE_APPROVAL_LIST;
1044
1045 BEGIN
1046 l_progress := '010';
1047
1048 SELECT approval_list_header_id,
1049 NVL(revision, 0),
1050 NVL(current_sequence_num, 0),
1051 first_approver_id,
1052 approval_path_id,
1053 last_update_date,
1054 wf_item_type,
1055 wf_item_key
1056 INTO l_old_approval_list_header_id,
1057 l_old_revision,
1058 l_old_current_sequence_num,
1059 l_old_first_approver_id,
1060 l_old_approval_path_id,
1061 l_old_last_update_date,
1062 l_old_wf_item_type,
1063 l_old_wf_item_key
1064 FROM po_approval_list_headers
1065 WHERE document_id = p_document_id
1066 AND document_type = p_document_type
1067 AND document_subtype = p_document_subtype
1068 AND latest_revision = 'Y'
1069 FOR UPDATE NOWAIT;
1070
1071 -- Checking last_update_date again since validate_approval_list() might have taken
1072 -- a while.
1073 IF (p_approval_list_header_id IS NOT NULL) AND
1074 ((l_old_approval_list_header_id <> p_approval_list_header_id) OR
1075 (l_old_last_update_date <> p_last_update_date)) THEN
1076 p_return_code := E_LIST_MODIFIED_SINCE_RETRIEVE;
1077 PushMessage(p_error_stack, 'PO_ALIST_LIST_MODIFIED', 'LIST_ID', p_approval_list_header_id);
1078 ROLLBACK TO SAVE_APPROVAL_LIST;
1079 RETURN;
1080 END IF;
1081
1082 l_progress := '011';
1083 OPEN c_lock_approval_list_lines(l_old_approval_list_header_id);
1084
1085 EXCEPTION
1086 WHEN NO_DATA_FOUND THEN
1087 IF (p_approval_list_header_id IS NOT NULL) THEN
1088 ROLLBACK TO SAVE_APPROVAL_LIST;
1089 p_return_code := E_INVALID_LIST_HEADER_ID;
1090 PushMessage(p_error_stack, 'PO_ALIST_INVALID_LIST_HDR_ID', 'LIST_ID', p_approval_list_header_id);
1091 RETURN;
1092 ELSE
1093 l_old_approval_list_header_id := NULL;
1094 l_old_revision := NULL;
1095 l_old_current_sequence_num := NULL;
1096 l_old_first_approver_id := NULL;
1097 l_old_approval_path_id := NULL;
1098 l_old_last_update_date := NULL;
1099 l_old_wf_item_type := NULL;
1100 l_old_wf_item_key := NULL;
1101 END IF;
1102 WHEN OTHERS THEN
1103 IF (SQLCODE = -54) THEN
1104 p_return_code := E_FAIL_TO_ACQUIRE_LOCK;
1105 PushMessage(p_error_stack, 'PO_ALIST_LOCK_FAIL');
1106 ELSE
1107 p_return_code := SQLCODE;
1108 PushMessage(p_error_stack, 'PO_ALL_SQL_ERROR', 'ROUTINE', 'SAVE_APPROVAL_LIST', 'ERR_NUMBER', l_progress, 'SQL_ERR', SQLERRM(SQLCODE));
1109 END IF;
1110 ROLLBACK TO SAVE_APPROVAL_LIST;
1111 RETURN;
1112 END;
1113
1114 l_progress := '012';
1115
1116 SELECT po_approval_list_headers_s.nextval
1117 INTO l_new_approval_list_header_id
1118 FROM sys.dual;
1119
1120 l_progress := '013';
1121
1122 INSERT INTO po_approval_list_headers (
1123 approval_list_header_id,
1124 document_id,
1125 document_type,
1126 document_subtype,
1127 revision,
1128 current_sequence_num,
1129 latest_revision,
1130 first_approver_id,
1131 approval_path_id,
1132 wf_item_type,
1133 wf_item_key,
1134 created_by,
1135 creation_date,
1136 last_update_login,
1137 last_updated_by,
1138 last_update_date)
1139 VALUES(
1140 l_new_approval_list_header_id,
1141 p_document_id,
1142 p_document_type,
1143 p_document_subtype,
1144 decode(p_approval_list_header_id, NULL, 1, l_old_revision+1),
1145 decode(p_approval_list_header_id, NULL, NULL, l_old_current_sequence_num),
1146 'Y',
1147 decode(p_approval_list_header_id, NULL, p_first_approver_id, l_old_first_approver_id),
1148 decode(p_approval_list_header_id, NULL, p_approval_path_id, l_old_approval_path_id),
1149 decode(p_approval_list_header_id, NULL, NULL, l_old_wf_item_type),
1150 decode(p_approval_list_header_id, NULL, NULL, l_old_wf_item_key),
1151 fnd_global.user_id,
1152 SYSDATE,
1153 fnd_global.login_id,
1154 fnd_global.user_id,
1155 SYSDATE);
1156
1157 IF (p_approval_list_header_id IS NOT NULL) THEN
1158 BEGIN
1159 l_progress := '014';
1160
1161 INSERT INTO po_approval_list_lines (
1162 approval_list_header_id,
1163 approval_list_line_id,
1164 next_element_id,
1165 approver_id,
1166 sequence_num,
1167 notification_id,
1168 notification_role,
1169 responder_id,
1170 forward_to_id,
1171 mandatory_flag,
1172 requires_reapproval_flag,
1173 approver_type,
1174 status,
1175 response_date,
1176 comments,
1177 created_by,
1178 creation_date,
1179 last_update_login,
1180 last_updated_by,
1181 last_update_date)
1182 SELECT l_new_approval_list_header_id,
1183 po_approval_list_lines_s.nextval,
1184 NULL,
1185 approver_id,
1186 sequence_num,
1187 notification_id,
1188 notification_role,
1189 responder_id,
1190 forward_to_id,
1191 mandatory_flag,
1192 requires_reapproval_flag,
1193 approver_type,
1194 status,
1195 response_date,
1196 comments,
1197 fnd_global.user_id,
1198 SYSDATE,
1199 fnd_global.login_id,
1200 fnd_global.user_id,
1201 SYSDATE
1202 FROM po_approval_list_lines
1203 WHERE approval_list_header_id = p_approval_list_header_id
1204 AND sequence_num <= l_old_current_sequence_num;
1205
1206 EXCEPTION
1207 WHEN NO_DATA_FOUND THEN
1208 NULL;
1209 END;
1210 END IF;
1211
1212 l_progress := '016';
1213
1214 IF (p_approval_list.COUNT > 0) THEN
1215 l_index := p_approval_list.FIRST;
1216 WHILE (l_index IS NOT NULL) LOOP
1217
1218 IF (p_approval_list_header_id IS NULL OR
1219 p_approval_list(l_index).sequence_num > NVL(l_old_current_sequence_num, 0)) THEN
1220
1221 l_progress := '017.'||to_char(l_index);
1222
1223 INSERT INTO po_approval_list_lines (
1224 approval_list_header_id,
1225 approval_list_line_id,
1226 next_element_id,
1227 approver_id,
1228 sequence_num,
1229 notification_id,
1230 notification_role,
1231 responder_id,
1232 forward_to_id,
1233 mandatory_flag,
1234 requires_reapproval_flag,
1235 approver_type,
1236 status,
1237 response_date,
1238 comments,
1239 created_by,
1240 creation_date,
1241 last_update_login,
1242 last_updated_by,
1243 last_update_date)
1244 SELECT l_new_approval_list_header_id,
1245 po_approval_list_lines_s.nextval,
1246 NULL,
1247 p_approval_list(l_index).approver_id,
1248 p_approval_list(l_index).sequence_num,
1249 NULL,
1250 NULL,
1251 NULL,
1252 NULL,
1253 p_approval_list(l_index).mandatory_flag,
1254 'N',
1255 p_approval_list(l_index).approver_type,
1256 NULL, -- status
1257 NULL, -- response_date
1258 NULL, -- comments
1259 fnd_global.user_id,
1260 SYSDATE,
1261 fnd_global.login_id,
1262 fnd_global.user_id,
1263 SYSDATE
1264 FROM sys.dual;
1265 END IF;
1266 l_index := p_approval_list.NEXT(l_index);
1267 END LOOP;
1268 END IF;
1269
1270 IF (l_old_approval_list_header_id IS NOT NULL) THEN
1271 l_progress := '020';
1272 UPDATE po_approval_list_headers
1273 SET latest_revision = 'N',
1274 last_update_date = SYSDATE,
1275 last_updated_by = fnd_global.user_id,
1276 last_update_login = fnd_global.login_id
1277 WHERE document_id = p_document_id
1278 AND document_type = p_document_type
1279 AND document_subtype = p_document_subtype
1280 AND approval_list_header_id = l_old_approval_list_header_id;
1281 END IF;
1282
1283 COMMIT;
1284 IF (c_lock_approval_list_lines%ISOPEN) THEN
1285 CLOSE c_lock_approval_list_lines;
1286 END IF;
1287
1288 EXCEPTION
1289 WHEN OTHERS THEN
1290 p_return_code := SQLCODE;
1291 PushMessage(p_error_stack, 'PO_ALL_SQL_ERROR', 'ROUTINE', 'SAVE_APPROVAL_LIST', 'ERR_NUMBER', l_progress, 'SQL_ERR', SQLERRM(SQLCODE));
1292 ROLLBACK TO SAVE_APPROVAL_LIST;
1293 IF (c_lock_approval_list_lines%ISOPEN) THEN
1294 CLOSE c_lock_approval_list_lines;
1295 END IF;
1296 RETURN;
1297 END;
1298
1299 p_approval_list_header_id := l_new_approval_list_header_id;
1300
1301 p_return_code := E_SUCCESS;
1302
1303
1304 EXCEPTION
1305 WHEN OTHERS THEN
1306 p_return_code := SQLCODE;
1307 PushMessage(p_error_stack, 'PO_ALL_SQL_ERROR', 'ROUTINE', 'SAVE_APPROVAL_LIST', 'ERR_NUMBER', l_progress, 'SQL_ERR', SQLERRM(SQLCODE));
1308 END save_approval_list;
1309
1310 PROCEDURE rebuild_approval_list(p_document_id IN NUMBER,
1311 p_document_type IN VARCHAR2,
1312 p_document_subtype IN VARCHAR2,
1313 p_rebuild_code IN VARCHAR2,
1314 p_return_code OUT NOCOPY NUMBER,
1315 p_error_stack OUT NOCOPY ErrorStackType,
1316 p_approval_list_header_id OUT NOCOPY NUMBER) IS
1317
1318 CURSOR c_lock_approval_list_lines(p_approval_list_header_id NUMBER) IS
1319 SELECT approval_list_line_id
1320 FROM po_approval_list_lines
1321 WHERE approval_list_header_id = p_approval_list_header_id
1322 FOR UPDATE;
1323
1324 CURSOR c_find_last_forward_to(p_approval_list_header_id NUMBER,
1325 p_current_sequence_num NUMBER) IS
1326 SELECT forward_to_id,
1327 sequence_num
1328 FROM po_approval_list_lines
1329 WHERE approval_list_header_id = p_approval_list_header_id
1330 AND forward_to_id IS NOT NULL
1331 AND sequence_num <= p_current_sequence_num
1332 ORDER BY sequence_num DESC;
1333
1334 CURSOR c_find_last_sys_approver(p_approval_list_header_id NUMBER,
1335 p_current_sequence_num NUMBER) IS
1336 SELECT approver_id,
1337 sequence_num,
1338 approver_type
1339 FROM po_approval_list_lines
1340 WHERE approval_list_header_id = p_approval_list_header_id
1341 AND approver_type IN ('SYSTEM', 'FORWARD')
1342 AND sequence_num <= p_current_sequence_num
1343 AND approval_list_line_id <> (select min(l2.approval_list_line_id)
1344 from po_approval_list_lines l2
1345 where l2.approval_list_header_id = p_approval_list_header_id)
1346 ORDER BY sequence_num DESC;
1347
1348 CURSOR c_future_approver(p_approval_list_header_id NUMBER,
1349 p_current_sequence_num NUMBER) IS
1350 SELECT approval_list_line_id,
1351 approver_id,
1352 sequence_num,
1353 approver_type,
1354 status,
1355 mandatory_flag
1356 FROM po_approval_list_lines
1357 WHERE approval_list_header_id = p_approval_list_header_id
1358 AND sequence_num > NVL(p_current_sequence_num, 0)
1359 ORDER BY sequence_num;
1360
1361 l_progress VARCHAR2(10) := '000';
1362 l_old_approval_list_header_id1 NUMBER;
1363 l_old_approval_list_header_id2 NUMBER;
1364 l_old_first_approver_id NUMBER;
1365 l_old_approval_path_id NUMBER;
1366 l_old_current_sequence_num NUMBER;
1367 l_old_revision NUMBER;
1368 l_old_wf_item_type1 VARCHAR2(8);
1369 l_old_wf_item_key1 VARCHAR2(240);
1370 l_old_wf_item_type2 VARCHAR2(8);
1371 l_old_wf_item_key2 VARCHAR2(240);
1372 l_old_last_update_date1 DATE;
1373 l_old_last_update_date2 DATE;
1374 l_new_approval_list_header_id NUMBER;
1375 l_preparer_id NUMBER;
1376 l_authorization_status VARCHAR2(25);
1377 l_start_approver_id NUMBER;
1378 l_default_approval_list ApprovalListType;
1379 l_complete_approval_list ApprovalListType;
1380 l_return_code NUMBER;
1381 l_max_sequence_num NUMBER;
1382 l_flag VARCHAR2(1);
1383 l_last_forward_to_id NUMBER;
1384 l_last_forward_to_sequence NUMBER;
1385 l_last_sys_approver_id NUMBER;
1386 l_last_sys_approver_sequence NUMBER;
1387 l_last_sys_approver_type VARCHAR2(30);
1388 l_need_to_update_list BOOLEAN;
1389 l_num_system_approvers NUMBER;
1390 l_index1 NUMBER;
1391 l_index2 NUMBER;
1392 L_CONTINUE_LOOP EXCEPTION;
1393 l_count NUMBER;
1394 l_id NUMBER;
1395 l_approver_id NUMBER;
1396 l_sequence_num NUMBER;
1397 l_mandatory_flag VARCHAR2(1);
1398 l_status VARCHAR2(30);
1399 l_approver_type VARCHAR2(30);
1400 L_MAX_TRIALS CONSTANT NUMBER := 5;
1401 l_trial NUMBER := 0;
1402 l_forwardto_dup BOOLEAN;
1403 l_can_preparer_approve_flag VARCHAR2(1);
1404 l_last_update_date date;
1405 l_is_request_change_order VARCHAR2(1); /* Bug 3912354 */
1406 l_first_approver_type VARCHAR2(100);
1407 l_increment number :=0; --bug 13843060
1408 BEGIN
1409
1410 IF (p_rebuild_code NOT IN ('FORWARD_RESPONSE', 'DOCUMENT_CHANGED', 'INVALID_APPROVER')) THEN
1411 p_return_code := E_INVALID_REBUILD_CODE;
1412 PushMessage(p_error_stack, 'PO_ALIST_INVALID_REB_CODE', 'REB_CODE', p_rebuild_code);
1413 RETURN;
1414 END IF;
1415
1416 <<BEGINNING>>
1417 l_trial := l_trial + 1;
1418 l_default_approval_list.DELETE;
1419 l_complete_approval_list.DELETE;
1420
1421 BEGIN
1422 IF (p_document_type = 'REQUISITION') THEN
1423 BEGIN
1424 l_progress := '001';
1425
1426 SELECT preparer_id,
1427 NVL(authorization_status, 'INCOMPLETE'),
1428 change_pending_flag,
1429 wf_item_type,
1430 wf_item_key
1431 INTO l_preparer_id,
1432 l_authorization_status,
1433 l_is_request_change_order,
1434 l_old_wf_item_type1,
1435 l_old_wf_item_key1
1436 FROM po_requisition_headers
1437 WHERE requisition_header_id = p_document_id;
1438
1439 /**
1440 ** Bug 3912354: add code to include RCOs as possible candidates for
1441 ** a list rebuild
1442 */
1443 IF (l_is_request_change_order = 'Y') THEN
1444 l_progress := '0012';
1445
1446 SELECT max(wf_item_type),
1447 max(wf_item_key)
1448 INTO l_old_wf_item_type1,
1449 l_old_wf_item_key1
1450 FROM po_change_requests
1451 WHERE document_header_id = p_document_id
1452 AND document_type = 'REQ'
1453 AND action_type NOT IN ('DERIVED')
1454 AND request_status NOT IN ('ACCEPTED', 'REJECTED');
1455 END IF;
1456
1457 EXCEPTION
1458 WHEN NO_DATA_FOUND THEN
1459 p_return_code := E_INVALID_DOCUMENT_ID;
1460 PushMessage(p_error_stack, 'PO_ALIST_INVALID_DOC_ID', 'DOC_ID', p_document_id);
1461 RETURN;
1462 END;
1463
1464 ELSE
1465 p_return_code := E_UNSUPPORTED_DOCUMENT_TYPE;
1466 PushMessage(p_error_stack, 'PO_ALIST_UNSUPPORTED_DOC_TYPE', 'DOC_TYPE', p_document_type, 'DOC_SUBTYPE', p_document_subtype);
1467 RETURN;
1468 END IF;
1469
1470 l_progress := '002';
1471
1472 SELECT approval_list_header_id,
1473 first_approver_id,
1474 approval_path_id,
1475 current_sequence_num,
1476 last_update_date
1477 INTO l_old_approval_list_header_id1,
1478 l_old_first_approver_id,
1479 l_old_approval_path_id,
1480 l_old_current_sequence_num,
1481 l_old_last_update_date1
1482 FROM po_approval_list_headers
1483 WHERE document_id = p_document_id
1484 AND document_type = p_document_type
1485 AND document_subtype = p_document_subtype
1486 AND wf_item_key = l_old_wf_item_key1
1487 AND wf_item_type = l_old_wf_item_type1
1488 AND latest_revision = 'Y';
1489
1490 EXCEPTION
1491 WHEN NO_DATA_FOUND THEN
1492 p_return_code := E_NO_APPROVAL_LIST_FOUND;
1493 PushMessage(p_error_stack, 'PO_ALIST_NO_LIST_FOUND');
1494 RETURN;
1495 END;
1496
1497 IF (p_rebuild_code = 'FORWARD_RESPONSE') THEN
1498 l_progress := '003';
1499
1500 SELECT forward_to_id
1501 INTO l_start_approver_id
1502 FROM po_approval_list_lines
1503 WHERE approval_list_header_id = l_old_approval_list_header_id1
1504 AND sequence_num = l_old_current_sequence_num;
1505
1506 IF (l_start_approver_id IS NULL OR
1507 is_approver_valid(p_document_id=>p_document_id,
1508 p_document_type=>p_document_type,
1509 p_document_subtype=>p_document_subtype,
1510 p_approver_id=>l_start_approver_id,
1511 p_approver_type=>'FORWARD') = FALSE) THEN
1512 p_return_code := E_INVALID_FORWARD_TO_ID;
1513 PushMessage(p_error_stack, 'PO_ALIST_INVALID_FORWARD_TO', 'FORWARD_ID', l_start_approver_id);
1514 RETURN;
1515 END IF;
1516
1517 l_progress := '004';
1518 get_default_approval_list(p_first_approver_id=>l_start_approver_id,
1519 p_approval_path_id=>l_old_approval_path_id,
1520 p_document_id=>p_document_id,
1521 p_document_type=>p_document_type,
1522 p_document_subtype=>p_document_subtype,
1523 p_rebuild_code=>p_rebuild_code,
1524 p_return_code=>l_return_code,
1525 p_error_stack=>p_error_stack,
1526 p_approval_list=>l_default_approval_list);
1527 IF (l_return_code <> E_SUCCESS) THEN
1528
1529 UPDATE po_approval_list_lines
1530 SET sequence_num = sequence_num + 1
1531 WHERE sequence_num > l_old_current_sequence_num
1532 AND approval_list_header_id = l_old_approval_list_header_id1;
1533
1534
1535 INSERT INTO po_approval_list_lines (
1536 approval_list_header_id,
1537 approval_list_line_id,
1538 next_element_id,
1539 approver_id,
1540 sequence_num,
1541 mandatory_flag,
1542 requires_reapproval_flag,
1543 approver_type,
1544 created_by,
1545 creation_date,
1546 last_update_login,
1547 last_updated_by,
1548 last_update_date)
1549 SELECT l_old_approval_list_header_id1,
1550 po_approval_list_lines_s.nextval,
1551 NULL, -- next_element_id
1552 l_start_approver_id,
1553 l_old_current_sequence_num+1,
1554 'N',
1555 'N',
1556 'FORWARD',
1557 fnd_global.user_id,
1558 SYSDATE,
1559 fnd_global.login_id,
1560 fnd_global.user_id,
1561 SYSDATE
1562 FROM dual;
1563
1564
1565 get_latest_approval_list
1566 (p_document_id=>p_document_id,
1567 p_document_type=>p_document_type,
1568 p_document_subtype=>p_document_subtype,
1569 p_return_code=>l_return_code,
1570 p_error_stack=>p_error_stack,
1571 p_approval_list_header_id=>l_old_approval_list_header_id1,
1572 p_last_update_date=>l_last_update_date,
1573 p_approval_list=>l_complete_approval_list);
1574
1575 validate_approval_list(p_document_id=>p_document_id,
1576 p_document_type=>p_document_type,
1577 p_document_subtype=>p_document_subtype,
1578 p_approval_list=>l_complete_approval_list,
1579 p_current_sequence_num=>null,
1580 p_return_code=>l_return_code,
1581 p_error_stack=>p_error_stack);
1582
1583 p_return_code := l_return_code;
1584 RETURN;
1585 END IF;
1586
1587 ELSIF (p_rebuild_code <> 'INVALID_APPROVER' OR
1588 l_authorization_status <> 'PRE-APPROVED') THEN
1589
1590 l_last_forward_to_id := NULL;
1591 l_last_forward_to_sequence := NULL;
1592
1593 l_progress := '005';
1594 OPEN c_find_last_forward_to(l_old_approval_list_header_id1,
1595 l_old_current_sequence_num);
1596 LOOP
1597 FETCH c_find_last_forward_to INTO l_last_forward_to_id, l_last_forward_to_sequence;
1598 EXIT WHEN c_find_last_forward_to%NOTFOUND;
1599
1600 -- Could have incorporated this into the cursor
1601 IF (is_approver_valid(p_document_id=>p_document_id,
1602 p_document_type=>p_document_type,
1603 p_document_subtype=>p_document_subtype,
1604 p_approver_id=>l_last_forward_to_id,
1605 p_approver_type=>'FORWARD') = TRUE) THEN
1606 EXIT;
1607 ELSE
1608 l_last_forward_to_id := NULL;
1609 l_last_forward_to_sequence := NULL;
1610 END IF;
1611 END LOOP;
1612 CLOSE c_find_last_forward_to;
1613
1614 l_last_sys_approver_id := NULL;
1615 l_last_sys_approver_sequence := NULL;
1616 l_last_sys_approver_type := NULL;
1617
1618 l_progress := '006';
1619 OPEN c_find_last_sys_approver(l_old_approval_list_header_id1,
1620 l_old_current_sequence_num);
1621 LOOP
1622 FETCH c_find_last_sys_approver INTO l_last_sys_approver_id,
1623 l_last_sys_approver_sequence,
1624 l_last_sys_approver_type;
1625 EXIT WHEN c_find_last_sys_approver%NOTFOUND;
1626
1627 IF (is_approver_valid(p_document_id=>p_document_id,
1628 p_document_type=>p_document_type,
1629 p_document_subtype=>p_document_subtype,
1630 p_approver_id=>l_last_sys_approver_id,
1631 p_approver_type=>l_last_sys_approver_type) = TRUE) THEN
1632 EXIT;
1633 ELSE
1634 l_last_sys_approver_id := NULL;
1635 l_last_sys_approver_sequence := NULL;
1636 l_last_sys_approver_type := NULL;
1637 END IF;
1638 END LOOP;
1639 CLOSE c_find_last_sys_approver;
1640
1641 IF (l_last_forward_to_id IS NULL AND l_last_sys_approver_id IS NULL) THEN
1642 --Bug:8793063 If the first approver is manuall added approver
1643 -- then build the list from preparer
1644 begin
1645 SELECT APPROVER_TYPE
1646 INTO l_first_approver_type
1647 FROM po_approval_list_lines
1648 WHERE APPROVAL_LIST_HEADER_ID = l_old_approval_list_header_id1
1649 AND SEQUENCE_NUM=1;
1650 exception
1651 when others then
1652 l_first_approver_type := 'SYSTEM';
1653 end;
1654
1655 IF (l_first_approver_type <> 'SYSTEM') THEN
1656 l_start_approver_id := l_preparer_id;
1657 ELSE
1658 l_start_approver_id := l_old_first_approver_id;
1659 END IF;
1660 ELSIF (l_last_forward_to_id IS NULL AND l_last_sys_approver_id IS NOT NULL) THEN
1661 l_start_approver_id := l_last_sys_approver_id;
1662 ELSIF (l_last_forward_to_id IS NOT NULL AND l_last_sys_approver_id IS NULL) THEN
1663 l_start_approver_id := l_last_forward_to_id;
1664 ELSIF (l_last_forward_to_id IS NOT NULL AND l_last_sys_approver_id IS NOT NULL) THEN
1665 IF (NVL(l_last_forward_to_sequence, -1) >= NVL(l_last_sys_approver_sequence, -2)) THEN
1666 l_start_approver_id := l_last_forward_to_id;
1667 ELSE
1668 l_start_approver_id := l_last_sys_approver_id;
1669 END IF;
1670 END IF;
1671
1672 l_progress := '007';
1673 get_default_approval_list(p_first_approver_id=>l_start_approver_id,
1674 p_approval_path_id=>l_old_approval_path_id,
1675 p_document_id=>p_document_id,
1676 p_document_type=>p_document_type,
1677 p_document_subtype=>p_document_subtype,
1678 p_rebuild_code=>p_rebuild_code,
1679 p_return_code=>l_return_code,
1680 p_error_stack=>p_error_stack,
1681 p_approval_list=>l_default_approval_list);
1682
1683 IF (l_return_code = E_NO_ONE_HAS_AUTHORITY) THEN
1684
1685 SELECT NVL(can_preparer_approve_flag, 'N')
1686 INTO l_can_preparer_approve_flag
1687 FROM po_document_types podt
1688 WHERE podt.document_type_code = p_document_type
1689 AND podt.document_subtype = p_document_subtype;
1690 IF (l_can_preparer_approve_flag <> 'N') THEN
1691 p_return_code := l_return_code;
1692 RETURN;
1693 END IF;
1694 ELSIF (l_return_code <> E_SUCCESS) THEN
1695 p_return_code := l_return_code;
1696 RETURN;
1697 END IF;
1698 END IF;
1699
1700 -- Check to see if we need to remove persons from the new list
1701 IF (l_default_approval_list.COUNT > 0) THEN
1702 l_index1 := l_default_approval_list.FIRST;
1703 WHILE (l_index1 IS NOT NULL) LOOP
1704 BEGIN
1705 IF (l_default_approval_list(l_index1).approver_type = 'SYSTEM') THEN
1706 l_progress := '008.'||to_char(l_index1);
1707
1708 IF (p_rebuild_code = 'FORWARD_RESPONSE') THEN
1709 -- We dont remove end approver who has the authority.
1710 IF (l_index1 = l_default_approval_list.LAST) THEN
1711 RAISE L_CONTINUE_LOOP;
1712 END IF;
1713
1714 -- remove user add/forward approvers in the future
1715 SELECT COUNT(*)
1716 INTO l_count
1717 FROM po_approval_list_lines
1718 WHERE approval_list_header_id = l_old_approval_list_header_id1
1719 AND approver_id = l_default_approval_list(l_index1).approver_id
1720 AND (sequence_num >= l_old_current_sequence_num AND approver_type <> 'SYSTEM' AND approver_type <> 'FORWARD');
1721
1722 IF (l_count > 0) THEN
1723 l_default_approval_list.DELETE(l_index1);
1724 RAISE L_CONTINUE_LOOP;
1725 END IF;
1726
1727 ElSE
1728 SELECT COUNT(*)
1729 INTO l_count
1730 FROM po_approval_list_lines
1731 WHERE approval_list_header_id = l_old_approval_list_header_id1
1732 AND approver_id = l_default_approval_list(l_index1).approver_id
1733 AND ((sequence_num = l_old_current_sequence_num) OR
1734 (sequence_num >= l_old_current_sequence_num AND approver_type <> 'SYSTEM'));
1735
1736 --Bug:8793063 When rebuilded from preparer the list contains preparer also
1737 -- Hence knock off preparer as he isnt present in po_approval_list_lines
1738
1739 IF ((l_count > 0) OR (l_default_approval_list(l_index1).approver_id=l_preparer_id)) THEN
1740 l_default_approval_list.DELETE(l_index1);
1741 RAISE L_CONTINUE_LOOP;
1742 END IF;
1743
1744 -- We dont remove end approver who has the authority.
1745 IF (l_index1 = l_default_approval_list.LAST) THEN
1746 RAISE L_CONTINUE_LOOP;
1747 END IF;
1748
1749 END IF;
1750
1751 -- Find out whether or not the person has already responded
1752 l_progress := '009.'||to_char(l_index1);
1753 SELECT COUNT(*)
1754 INTO l_count
1755 FROM po_approval_list_lines
1756 WHERE approval_list_header_id = l_old_approval_list_header_id1
1757 AND approver_id = l_default_approval_list(l_index1).approver_id
1758 AND sequence_num <= l_old_current_sequence_num;
1759
1760 IF (l_count > 0) THEN
1761 l_progress := '010.'||to_char(l_index1);
1762 SELECT COUNT(*)
1763 INTO l_count
1764 FROM po_approval_list_lines
1765 WHERE approval_list_header_id = l_old_approval_list_header_id1
1766 AND approver_id = l_default_approval_list(l_index1).approver_id
1767 AND sequence_num <= l_old_current_sequence_num
1768 AND requires_reapproval_flag = 'Y';
1769
1770 IF (l_count = 0) THEN
1771 IF (p_rebuild_code = 'DOCUMENT_CHANGED') THEN
1772 -- If approver responded
1773 -- and all requires_reapproval_flag <> 'Y'
1774 -- and all status not in ('APPROVE', 'APPROVE_AND_FORWARD')
1775 -- then we remove him.
1776 l_progress := '011.'||to_char(l_index1);
1777 SELECT COUNT(*)
1778 INTO l_count
1779 FROM po_approval_list_lines
1780 WHERE approval_list_header_id = l_old_approval_list_header_id1
1781 AND approver_id = l_default_approval_list(l_index1).approver_id
1782 AND sequence_num <= l_old_current_sequence_num
1783 AND status in ('APPROVE', 'APPROVE_AND_FORWARD');
1784
1785 IF (l_count = 0) THEN
1786 l_default_approval_list.DELETE(l_index1);
1787 RAISE L_CONTINUE_LOOP;
1788 END IF;
1789 ELSIF (p_rebuild_code = 'FORWARD_RESPONSE') THEN
1790 -- If approver responded
1791 -- and all requires_reapproval_flag <> 'Y'
1792 -- and all status not in ('APPROVE', 'APPROVE_AND_FORWARD')
1793 -- then we remove him.
1794
1795 SELECT COUNT(*)
1796 INTO l_count
1797 FROM po_approval_list_lines
1798 WHERE approval_list_header_id = l_old_approval_list_header_id1
1799 AND approver_id = l_default_approval_list(l_index1).approver_id
1800 AND sequence_num <= l_old_current_sequence_num
1801 AND status in ('FORWARD');
1802
1803 IF (l_count = 0) THEN
1804 l_default_approval_list.DELETE(l_index1);
1805 RAISE L_CONTINUE_LOOP;
1806 END IF;
1807
1808 ELSE
1809 -- So all the rows have requires_reapproval_flag <> Y
1810 l_default_approval_list.DELETE(l_index1);
1811 RAISE L_CONTINUE_LOOP;
1812 END IF;
1813 END IF;
1814 END IF;
1815 END IF;
1816
1817 EXCEPTION
1818 WHEN L_CONTINUE_LOOP THEN
1819 NULL;
1820 END;
1821 l_index1 := l_default_approval_list.NEXT(l_index1);
1822 END LOOP;
1823 END IF;
1824
1825 -- Need to build the entire list? FIXME!
1826 l_index1 := 1;
1827 --begin bug 13843060
1828 l_index2 := l_default_approval_list.FIRST;
1829 WHILE (l_index2 IS NOT NULL) LOOP
1830 l_complete_approval_list(l_index1) := l_default_approval_list(l_index2);
1831 l_index1 := l_index1 + 1;
1832 l_increment := l_increment+1;
1833 l_index2 := l_default_approval_list.NEXT(l_index2);
1834 END LOOP;
1835
1836 l_index1 := l_index1 + 1;
1837 --end bug 13843060
1838 OPEN c_future_approver(l_old_approval_list_header_id1, l_old_current_sequence_num);
1839 LOOP
1840 FETCH c_future_approver INTO l_id, l_approver_id, l_sequence_num,
1841 l_approver_type, l_status, l_mandatory_flag;
1842 EXIT WHEN c_future_approver%NOTFOUND;
1843
1844 IF (is_approver_valid(p_document_id=>p_document_id,
1845 p_document_type=>p_document_type,
1846 p_document_subtype=>p_document_subtype,
1847 p_approver_id=>l_approver_id,
1848 p_approver_type=>l_approver_type) = TRUE) THEN
1849
1850 -- if an user added approver happens to be the foward-to approver, then donot add this approver twice.
1851
1852 l_forwardto_dup := FALSE;
1853
1854 IF (l_default_approval_list.COUNT > 0) THEN
1855 IF ((l_default_approval_list(l_default_approval_list.FIRST).approver_id = l_approver_id) AND (l_default_approval_list(l_default_approval_list.FIRST).approver_type) = 'FORWARD') THEN
1856 l_forwardto_dup := TRUE;
1857 END IF;
1858 END IF;
1859
1860 -- IF (l_approver_type <> 'SYSTEM' OR
1861 IF (((l_approver_type <> 'SYSTEM') AND (NOT (( l_approver_type ='USER') AND l_forwardto_dup))) OR
1862
1863 (p_rebuild_code = 'INVALID_APPROVER' AND
1864 l_authorization_status = 'PRE-APPROVED' AND
1865 l_sequence_num = l_old_current_sequence_num)) THEN
1866 l_complete_approval_list(l_index1).id := l_id;
1867 l_sequence_num := l_sequence_num+l_increment; --bug 13843060
1868 l_complete_approval_list(l_index1).sequence_num := l_sequence_num;
1869
1870 l_complete_approval_list(l_index1).approver_id := l_approver_id;
1871 l_complete_approval_list(l_index1).approver_disp_name := NULL;
1872
1873 l_complete_approval_list(l_index1).responder_id := NULL;
1874 l_complete_approval_list(l_index1).responder_disp_name := NULL;
1875
1876 l_complete_approval_list(l_index1).forward_to_id := NULL;
1877 l_complete_approval_list(l_index1).forward_to_disp_name := NULL;
1878
1879 l_complete_approval_list(l_index1).status := l_status;
1880 l_complete_approval_list(l_index1).approver_type := l_approver_type;
1881 l_complete_approval_list(l_index1).mandatory_flag := l_mandatory_flag;
1882
1883 l_index1 := l_index1 + 1;
1884 END IF;
1885 END IF;
1886 END LOOP;
1887 CLOSE c_future_approver;
1888
1889 --begin bug 13843060
1890 /*
1891 l_index2 := l_default_approval_list.FIRST;
1892 WHILE (l_index2 IS NOT NULL) LOOP
1893 l_complete_approval_list(l_index1) := l_default_approval_list(l_index2);
1894 l_index1 := l_index1 + 1;
1895 l_index2 := l_default_approval_list.NEXT(l_index2);
1896 END LOOP;*/
1897 --end bug 13843060
1898
1899 validate_approval_list(p_document_id=>p_document_id,
1900 p_document_type=>p_document_type,
1901 p_document_subtype=>p_document_subtype,
1902 p_approval_list=>l_complete_approval_list,
1903 p_current_sequence_num=>null,
1904 p_return_code=>l_return_code,
1905 p_error_stack=>p_error_stack);
1906 IF (l_return_code <> E_SUCCESS) THEN
1907 p_return_code := l_return_code;
1908 RETURN;
1909 END IF;
1910
1911
1912 BEGIN
1913
1914 SAVEPOINT REBUILD_APPROVAL_LIST;
1915
1916 BEGIN
1917 l_progress := '013';
1918
1919 SELECT approval_list_header_id,
1920 NVL(revision, 0),
1921 NVL(current_sequence_num, 0),
1922 first_approver_id,
1923 approval_path_id,
1924 last_update_date,
1925 wf_item_type,
1926 wf_item_key
1927 INTO l_old_approval_list_header_id2,
1928 l_old_revision,
1929 l_old_current_sequence_num,
1930 l_old_first_approver_id,
1931 l_old_approval_path_id,
1932 l_old_last_update_date2,
1933 l_old_wf_item_type2,
1934 l_old_wf_item_key2
1935 FROM po_approval_list_headers
1936 WHERE document_id = p_document_id
1937 AND document_type = p_document_type
1938 AND document_subtype = p_document_subtype
1939 AND wf_item_type = l_old_wf_item_type1
1940 AND wf_item_key = l_old_wf_item_key1
1941 AND latest_revision = 'Y'
1942 FOR UPDATE;
1943
1944 IF (l_old_approval_list_header_id1 <> l_old_approval_list_header_id2) OR
1945 (l_old_last_update_date1 <> l_old_last_update_date2) THEN
1946 ROLLBACK TO REBUILD_APPROVAL_LIST;
1947 IF (l_trial >= L_MAX_TRIALS) THEN
1948 p_return_code := E_LIST_MODIFIED_SINCE_RETRIEVE;
1949 PushMessage(p_error_stack, 'PO_ALIST_LIST_MODIFIED', 'LIST_ID', l_old_approval_list_header_id1);
1950 RETURN;
1951 ELSE
1952 GOTO BEGINNING;
1953 END IF;
1954 END IF;
1955
1956 l_progress := '014';
1957 OPEN c_lock_approval_list_lines(l_old_approval_list_header_id2);
1958
1959 EXCEPTION
1960 WHEN NO_DATA_FOUND THEN
1961 ROLLBACK TO REBUILD_APPROVAL_LIST;
1962 IF (c_lock_approval_list_lines%ISOPEN) THEN
1963 CLOSE c_lock_approval_list_lines;
1964 END IF;
1965 p_return_code := E_NO_APPROVAL_LIST_FOUND;
1966 PushMessage(p_error_stack, 'PO_ALIST_NO_LIST_FOUND');
1967 RETURN;
1968 WHEN OTHERS THEN
1969 p_return_code := SQLCODE;
1970 PushMessage(p_error_stack, 'PO_ALL_SQL_ERROR', 'ROUTINE', 'REBUILD_APPROVAL_LIST', 'ERR_NUMBER', l_progress, 'SQL_ERR', SQLERRM(SQLCODE));
1971 ROLLBACK TO REBUILD_APPROVAL_LIST;
1972 IF (c_lock_approval_list_lines%ISOPEN) THEN
1973 CLOSE c_lock_approval_list_lines;
1974 END IF;
1975 RETURN;
1976 END;
1977
1978 l_progress := '017';
1979
1980 SELECT po_approval_list_headers_s.nextval
1981 INTO l_new_approval_list_header_id
1982 FROM sys.dual;
1983
1984 l_progress := '018';
1985
1986 INSERT INTO po_approval_list_headers (
1987 approval_list_header_id,
1988 document_id,
1989 document_type,
1990 document_subtype,
1991 revision,
1992 current_sequence_num,
1993 latest_revision,
1994 first_approver_id,
1995 approval_path_id,
1996 wf_item_type,
1997 wf_item_key,
1998 created_by,
1999 creation_date,
2000 last_update_login,
2001 last_updated_by,
2002 last_update_date)
2003 VALUES(
2004 l_new_approval_list_header_id,
2005 p_document_id,
2006 p_document_type,
2007 p_document_subtype,
2008 l_old_revision+1,
2009 l_old_current_sequence_num,
2010 'Y',
2011 l_old_first_approver_id,
2012 l_old_approval_path_id,
2013 l_old_wf_item_type2,
2014 l_old_wf_item_key2,
2015 fnd_global.user_id,
2016 SYSDATE,
2017 fnd_global.login_id,
2018 fnd_global.user_id,
2019 SYSDATE);
2020
2021 BEGIN
2022 l_progress := '019';
2023
2024 INSERT INTO po_approval_list_lines (
2025 approval_list_header_id,
2026 approval_list_line_id,
2027 next_element_id,
2028 approver_id,
2029 sequence_num,
2030 notification_id,
2031 notification_role,
2032 responder_id,
2033 forward_to_id,
2034 mandatory_flag,
2035 requires_reapproval_flag,
2036 approver_type,
2037 status,
2038 response_date,
2039 comments,
2040 created_by,
2041 creation_date,
2042 last_update_login,
2043 last_updated_by,
2044 last_update_date)
2045 SELECT l_new_approval_list_header_id,
2046 po_approval_list_lines_s.nextval,
2047 NULL, -- next_element_id
2048 approver_id,
2049 sequence_num,
2050 notification_id,
2051 notification_role,
2052 responder_id,
2053 forward_to_id,
2054 mandatory_flag,
2055 decode(p_rebuild_code, 'DOCUMENT_CHANGED',
2056 decode(status, 'APPROVE', 'Y', 'APPROVE_AND_FORWARD', 'Y', requires_reapproval_flag),
2057 requires_reapproval_flag),
2058 approver_type,
2059 status,
2060 response_date,
2061 comments,
2062 fnd_global.user_id,
2063 SYSDATE,
2064 fnd_global.login_id,
2065 fnd_global.user_id,
2066 SYSDATE
2067 FROM po_approval_list_lines
2068 WHERE approval_list_header_id = l_old_approval_list_header_id2
2069 AND sequence_num <= l_old_current_sequence_num;
2070
2071 EXCEPTION
2072 WHEN NO_DATA_FOUND THEN
2073 NULL;
2074 END;
2075
2076 -- Get the max sequence number in new lines
2077 l_progress := '020';
2078
2079 SELECT NVL(max(sequence_num), 0)
2080 INTO l_max_sequence_num
2081 FROM po_approval_list_lines
2082 WHERE approval_list_header_id = l_new_approval_list_header_id;
2083
2084 IF (l_complete_approval_list.COUNT > 0) THEN
2085 l_index1 := l_complete_approval_list.FIRST;
2086 WHILE (l_index1 IS NOT NULL) LOOP
2087 l_progress := '021.'||to_char(l_index1);
2088
2089 IF (l_complete_approval_list(l_index1).id IS NULL OR
2090 l_complete_approval_list(l_index1).sequence_num > l_old_current_sequence_num) THEN
2091 IF (l_complete_approval_list(l_index1).id IS NULL) THEN
2092 l_complete_approval_list(l_index1).sequence_num := l_max_sequence_num + 1;
2093 END IF;
2094 IF (l_complete_approval_list(l_index1).sequence_num > l_max_sequence_num) THEN
2095 l_max_sequence_num := l_complete_approval_list(l_index1).sequence_num;
2096 END IF;
2097 l_progress := '022.'||to_char(l_index1);
2098 INSERT INTO po_approval_list_lines (
2099 approval_list_header_id,
2100 approval_list_line_id,
2101 next_element_id,
2102 approver_id,
2103 sequence_num,
2104 notification_id,
2105 notification_role,
2106 responder_id,
2107 forward_to_id,
2108 mandatory_flag,
2109 requires_reapproval_flag,
2110 approver_type,
2111 status,
2112 response_date,
2113 comments,
2114 created_by,
2115 creation_date,
2116 last_update_login,
2117 last_updated_by,
2118 last_update_date)
2119 SELECT l_new_approval_list_header_id,
2120 po_approval_list_lines_s.nextval,
2121 NULL, -- next_element_id
2122 l_complete_approval_list(l_index1).approver_id,
2123 l_complete_approval_list(l_index1).sequence_num,
2124 NULL, -- notification_id
2125 NULL, -- notification_role
2126 NULL, -- responder_id
2127 NULL, -- forward_to_id
2128 l_complete_approval_list(l_index1).mandatory_flag,
2129 'N',
2130 l_complete_approval_list(l_index1).approver_type,
2131 NULL, -- status
2132 NULL, -- response_date
2133 NULL, -- comments
2134 fnd_global.user_id,
2135 SYSDATE,
2136 fnd_global.login_id,
2137 fnd_global.user_id,
2138 SYSDATE
2139 FROM sys.dual;
2140 l_index1 := l_complete_approval_list.NEXT(l_index1);
2141 END IF;
2142 END LOOP;
2143 END IF;
2144
2145 l_progress := '023';
2146
2147 UPDATE po_approval_list_headers
2148 SET latest_revision = 'N',
2149 last_update_date = SYSDATE,
2150 last_updated_by = fnd_global.user_id,
2151 last_update_login = fnd_global.login_id
2152 WHERE document_id = p_document_id
2153 AND document_type = p_document_type
2154 AND document_subtype = p_document_subtype
2155 AND approval_list_header_id = l_old_approval_list_header_id2;
2156
2157 l_progress := '024';
2158
2159 UPDATE po_approval_list_headers
2160 SET last_update_date = SYSDATE
2161 WHERE document_id = p_document_id
2162 AND document_type = p_document_type
2163 AND document_subtype = p_document_subtype
2164 AND approval_list_header_id = l_new_approval_list_header_id;
2165
2166 COMMIT;
2167 CLOSE c_lock_approval_list_lines;
2168
2169 EXCEPTION
2170 WHEN OTHERS THEN
2171 p_return_code := SQLCODE;
2172 PushMessage(p_error_stack, 'PO_ALL_SQL_ERROR', 'ROUTINE', 'REBUILD_APPROVAL_LIST', 'ERR_NUMBER', l_progress, 'SQL_ERR', SQLERRM(SQLCODE));
2173 ROLLBACK TO REBUILD_APPROVAL_LIST;
2174 IF (c_lock_approval_list_lines%ISOPEN) THEN
2175 CLOSE c_lock_approval_list_lines;
2176 END IF;
2177 RETURN;
2178 END;
2179
2180 p_return_code := E_SUCCESS;
2181 p_approval_list_header_id := l_new_approval_list_header_id;
2182
2183 EXCEPTION
2184 WHEN OTHERS THEN
2185 IF (c_find_last_forward_to%ISOPEN) THEN
2186 CLOSE c_find_last_forward_to;
2187 END IF;
2188 IF (c_find_last_sys_approver%ISOPEN) THEN
2189 CLOSE c_find_last_sys_approver;
2190 END IF;
2191 IF (c_future_approver%ISOPEN) THEN
2192 CLOSE c_future_approver;
2193 END IF;
2194 p_return_code := SQLCODE;
2195 PushMessage(p_error_stack, 'PO_ALL_SQL_ERROR', 'ROUTINE', 'REBUILD_APPROVAL_LIST', 'ERR_NUMBER', l_progress, 'SQL_ERR', SQLERRM(SQLCODE));
2196 END rebuild_approval_list;
2197
2198
2199
2200
2201 PROCEDURE validate_approval_list(p_document_id IN NUMBER,
2202 p_document_type IN VARCHAR2,
2203 p_document_subtype IN VARCHAR2,
2204 p_approval_list IN ApprovalListType,
2205 p_current_sequence_num IN NUMBER,
2206 p_return_code OUT NOCOPY NUMBER,
2207 p_error_stack IN OUT NOCOPY ErrorStackType) IS
2208 l_progress VARCHAR2(10) := '000';
2209 l_index NUMBER;
2210 l_return_code NUMBER := NULL;
2211 BEGIN
2212
2213 IF (p_approval_list.COUNT > 0) THEN
2214 l_index := p_approval_list.FIRST;
2215 WHILE (l_index IS NOT NULL) LOOP
2216 IF (NVL(p_approval_list(l_index).sequence_num, 1) > NVL(p_current_sequence_num, 0)) THEN
2217 l_progress := '001.'||to_char(l_index);
2218 IF (is_approver_valid(p_document_id=>p_document_id,
2219 p_document_type=>p_document_type,
2220 p_document_subtype=>p_document_subtype,
2221 p_approver_id=>p_approval_list(l_index).approver_id,
2222 p_approver_type=>p_approval_list(l_index).approver_type) = FALSE) THEN
2223 IF (p_approval_list(l_index).approver_disp_name IS NOT NULL) THEN
2224 PushMessage(p_error_stack, 'PO_ALIST_INVALID_APPR', 'APPROVER', p_approval_list(l_index).approver_disp_name);
2225 ELSE
2226 PushMessage(p_error_stack, 'PO_ALIST_INVALID_APPR', 'APPROVER', p_approval_list(l_index).approver_id);
2227 END IF;
2228 l_return_code := E_INVALID_APPROVAL_LIST;
2229 END IF;
2230 l_progress := '002.'||to_char(l_index);
2231 IF (p_approval_list(l_index).sequence_num IS NULL) THEN
2232 IF (p_approval_list(l_index).approver_disp_name IS NOT NULL) THEN
2233 PushMessage(p_error_stack, 'PO_ALIST_INVALID_SEQ', 'APPROVER', p_approval_list(l_index).approver_disp_name,
2234 'SEQ_NUM', p_approval_list(l_index).sequence_num, 'CUR_NUM', NVL(p_current_sequence_num, 0));
2235 ELSE
2236 PushMessage(p_error_stack, 'PO_ALIST_INVALID_SEQ', 'APPROVER', p_approval_list(l_index).approver_id,
2237 'SEQ_NUM', p_approval_list(l_index).sequence_num, 'CUR_NUM', NVL(p_current_sequence_num, 0));
2238 END IF;
2239 l_return_code := E_INVALID_APPROVAL_LIST;
2240 END IF;
2241 END IF;
2242 l_index := p_approval_list.NEXT(l_index);
2243 END LOOP;
2244 END IF;
2245 -- Dont want to default the code to E_SUCCESS at the beginning
2246 IF (l_return_code IS NULL) THEN
2247 p_return_code := E_SUCCESS;
2248 ELSE
2249 p_return_code := l_return_code;
2250 END IF;
2251 EXCEPTION
2252 WHEN OTHERS THEN
2253 p_return_code := SQLCODE;
2254 PushMessage(p_error_stack, 'PO_ALL_SQL_ERROR', 'ROUTINE', 'VALIDATE_APPROVAL_LIST', 'ERR_NUMBER', l_progress, 'SQL_ERR', SQLERRM(SQLCODE));
2255 END validate_approval_list;
2256
2257
2258
2259
2260 FUNCTION is_approver_valid(p_document_id IN NUMBER,
2261 p_document_type IN VARCHAR2,
2262 p_document_subtype IN VARCHAR2,
2263 p_approver_id IN NUMBER,
2264 p_approver_type IN VARCHAR2) return BOOLEAN IS
2265
2266 l_flag VARCHAR2(1);
2267
2268 BEGIN
2269
2270 IF (p_approver_type IS NULL) THEN
2271 RETURN FALSE;
2272 END IF;
2273
2274 SELECT 'Y'
2275 INTO l_flag
2276 FROM wf_users
2277 WHERE orig_system = 'PER' and orig_system_id = p_approver_id and rownum=1;
2278
2279 RETURN TRUE;
2280
2281 EXCEPTION
2282 WHEN OTHERS THEN
2283 RETURN FALSE;
2284 END is_approver_valid;
2285
2286
2287 FUNCTION is_approver_mandatory(p_document_id IN NUMBER,
2288 p_document_type IN VARCHAR2,
2289 p_document_subtype IN VARCHAR2,
2290 p_preparer_id IN NUMBER,
2291 p_approver_id IN NUMBER,
2292 p_approver_type IN VARCHAR2) RETURN BOOLEAN IS
2293 l_profile_value VARCHAR2(240) := NULL;
2294 BEGIN
2295 fnd_profile.get('POR_SYS_GENERATED_APPROVERS_MANDATORY', l_profile_value);
2296 RETURN (p_approver_type = 'SYSTEM' AND (l_profile_value IS NULL OR l_profile_value = 'Y'));
2297 END is_approver_mandatory;
2298
2299
2300
2301 PROCEDURE get_next_approver(p_document_id IN NUMBER,
2302 p_document_type IN VARCHAR2,
2303 p_document_subtype IN VARCHAR2,
2304 p_return_code OUT NOCOPY NUMBER,
2305 p_next_approver_id OUT NOCOPY NUMBER,
2306 p_sequence_num OUT NOCOPY NUMBER,
2307 p_approver_type OUT NOCOPY VARCHAR2) IS
2308
2309 CURSOR c_lock_approval_list_lines(p_approval_list_header_id NUMBER) IS
2310 SELECT approval_list_line_id
2311 FROM po_approval_list_lines
2312 WHERE approval_list_header_id = p_approval_list_header_id
2313 FOR UPDATE;
2314
2315 CURSOR c_find_next_approver(p_approval_list_header_id NUMBER,
2316 p_current_sequence_num NUMBER) IS
2317 SELECT approver_id,
2318 sequence_num,
2319 approver_type
2320 FROM po_approval_list_lines
2321 WHERE approval_list_header_id = p_approval_list_header_id
2322 AND sequence_num > p_current_sequence_num
2323 ORDER BY sequence_num;
2324
2325 l_progress VARCHAR2(10) := '000';
2326 l_old_approval_list_header_id NUMBER;
2327 l_old_current_sequence_num NUMBER;
2328 l_next_approver_id NUMBER := NULL;
2329 l_sequence_num NUMBER := NULL;
2330 l_approver_type VARCHAR2(30) := NULL;
2331
2332 l_current_approver_user_name VARCHAR2(100);
2333 l_current_approver_disp_name VARCHAR2(240);
2334 l_orig_system VARCHAR2(48):='PER';
2335 l_current_approver_id NUMBER := NULL;
2336 l_old_wf_item_type VARCHAR2(8) := NULL;
2337 l_old_wf_item_key VARCHAR2(240) := NULL;
2338
2339 BEGIN
2340
2341 SAVEPOINT GET_NEXT_APPROVER;
2342 BEGIN
2343 l_progress := '001';
2344
2345 SELECT approval_list_header_id,
2346 NVL(current_sequence_num, 0),
2347 wf_item_type,
2348 wf_item_key
2349 INTO l_old_approval_list_header_id,
2350 l_old_current_sequence_num,
2351 l_old_wf_item_type,
2352 l_old_wf_item_key
2353 FROM po_approval_list_headers
2354 WHERE document_id = p_document_id
2355 AND document_type = p_document_type
2356 AND document_subtype = p_document_subtype
2357 AND latest_revision = 'Y'
2358 FOR UPDATE;
2359
2360 /* bug#1639030: kagarwal
2361 ** set appropriate value for workflow attribute
2362 ** FORWARD_FROM_ID,
2363 ** FORWARD_FROM_USER_NAME,
2364 ** FORWARD_FROM_DISP_NAME
2365 ** before we update the po_approval_list_headers
2366 */
2367
2368 IF (l_old_current_sequence_num > 0 ) THEN
2369
2370 SELECT approver_id
2371 INTO l_current_approver_id
2372 FROM po_approval_list_lines
2373 WHERE approval_list_header_id = l_old_approval_list_header_id
2374 AND sequence_num = l_old_current_sequence_num;
2375
2376
2377 wf_engine.SetItemAttrNumber ( itemtype => l_old_wf_item_type,
2378 itemkey => l_old_wf_item_key,
2379 aname => 'FORWARD_FROM_ID',
2380 avalue => l_current_approver_id);
2381
2382 WF_DIRECTORY.GetUserName(l_orig_system,
2383 l_current_approver_id,
2384 l_current_approver_user_name,
2385 l_current_approver_disp_name);
2386
2387 wf_engine.SetItemAttrText( itemtype => l_old_wf_item_type,
2388 itemkey => l_old_wf_item_key,
2389 aname => 'FORWARD_FROM_USER_NAME' ,
2390 avalue => l_current_approver_user_name);
2391
2392
2393 wf_engine.SetItemAttrText( itemtype => l_old_wf_item_type,
2394 itemkey => l_old_wf_item_key,
2395 aname => 'FORWARD_FROM_DISP_NAME' ,
2396 avalue => l_current_approver_disp_name);
2397
2398 END IF;
2399
2400
2401 EXCEPTION
2402 WHEN NO_DATA_FOUND THEN
2403 ROLLBACK TO GET_NEXT_APPROVER;
2404 p_return_code := E_NO_APPROVAL_LIST_FOUND;
2405 RETURN;
2406 END;
2407
2408 OPEN c_lock_approval_list_lines(l_old_approval_list_header_id);
2409
2410 l_progress := '002';
2411
2412 OPEN c_find_next_approver(l_old_approval_list_header_id, l_old_current_sequence_num);
2413 FETCH c_find_next_approver INTO l_next_approver_id, l_sequence_num, l_approver_type;
2414
2415 IF (c_find_next_approver%NOTFOUND) THEN
2416 ROLLBACK TO GET_NEXT_APPROVER;
2417 CLOSE c_find_next_approver;
2418 p_next_approver_id := NULL;
2419 p_sequence_num := NULL;
2420 p_approver_type := NULL;
2421 p_return_code := E_NO_NEXT_APPROVER_FOUND;
2422 RETURN;
2423 END IF;
2424
2425 CLOSE c_find_next_approver;
2426
2427 IF (is_approver_valid(p_document_id=>p_document_id,
2428 p_document_type=>p_document_type,
2429 p_document_subtype=>p_document_subtype,
2430 p_approver_id=>l_next_approver_id,
2431 p_approver_type=>l_approver_type) = FALSE) THEN
2432 ROLLBACK TO GET_NEXT_APPROVER;
2433 p_next_approver_id := NULL;
2434 p_sequence_num := NULL;
2435 p_approver_type := NULL;
2436 p_return_code := E_INVALID_APPROVER;
2437 RETURN;
2438 END IF;
2439
2440 l_progress := '003';
2441
2442 UPDATE po_approval_list_headers
2443 SET current_sequence_num = l_sequence_num,
2444 last_update_date = SYSDATE,
2445 last_updated_by = fnd_global.user_id,
2446 last_update_login = fnd_global.login_id
2447 WHERE document_id = p_document_id
2448 AND document_type = p_document_type
2449 AND document_subtype = p_document_subtype
2450 AND approval_list_header_id = l_old_approval_list_header_id;
2451
2452 COMMIT;
2453 CLOSE c_lock_approval_list_lines;
2454
2455 p_next_approver_id := l_next_approver_id;
2456 p_sequence_num := l_sequence_num;
2457 p_approver_type := l_approver_type;
2458 p_return_code := E_SUCCESS;
2459
2460 EXCEPTION
2461 WHEN OTHERS THEN
2462 p_return_code := SQLCODE;
2463 ROLLBACK TO GET_NEXT_APPROVER;
2464 IF (c_lock_approval_list_lines%ISOPEN) THEN
2465 CLOSE c_lock_approval_list_lines;
2466 END IF;
2467 IF (c_find_next_approver%ISOPEN) THEN
2468 CLOSE c_find_next_approver;
2469 END IF;
2470 p_next_approver_id := NULL;
2471 p_sequence_num := NULL;
2472 p_approver_type := NULL;
2473 END get_next_approver;
2474
2475
2476
2477
2478 PROCEDURE does_approval_list_exist(p_document_id IN NUMBER,
2479 p_document_type IN VARCHAR2,
2480 p_document_subtype IN VARCHAR2,
2481 p_itemtype IN VARCHAR2,
2482 p_itemkey IN VARCHAR2,
2483 p_return_code OUT NOCOPY NUMBER,
2484 p_approval_list_header_id OUT NOCOPY NUMBER) IS
2485
2486 l_progress VARCHAR2(10) := '000';
2487
2488 BEGIN
2489
2490 l_progress := '001';
2491
2492 SELECT approval_list_header_id
2493 INTO p_approval_list_header_id
2494 FROM po_approval_list_headers
2495 WHERE document_id = p_document_id
2496 AND document_type = p_document_type
2497 AND document_subtype = p_document_subtype
2498 AND latest_revision = 'Y'
2499 AND ((wf_item_type IS NULL AND p_itemtype IS NULL) OR
2500 (wf_item_type = p_itemtype))
2501 AND ((wf_item_key IS NULL AND p_itemkey IS NULL) OR
2502 (wf_item_key = p_itemkey));
2503
2504 p_return_code := E_SUCCESS;
2505
2506 EXCEPTION
2507 WHEN NO_DATA_FOUND THEN
2508 p_approval_list_header_id := NULL;
2509 p_return_code := E_SUCCESS;
2510 WHEN OTHERS THEN
2511 p_approval_list_header_id := NULL;
2512 p_return_code := SQLCODE;
2513 END does_approval_list_exist;
2514
2515 PROCEDURE update_approval_list_itemkey(p_approval_list_header_id IN NUMBER,
2516 p_itemtype IN VARCHAR2,
2517 p_itemkey IN VARCHAR2,
2518 p_return_code OUT NOCOPY NUMBER) IS
2519 l_progress VARCHAR2(10) := '000';
2520 BEGIN
2521
2522 l_progress := '001';
2523
2524 UPDATE po_approval_list_headers
2525 SET wf_item_type = p_itemtype,
2526 wf_item_key = p_itemkey,
2527 last_update_date = SYSDATE,
2528 last_updated_by = fnd_global.user_id,
2529 last_update_login = fnd_global.login_id
2530 WHERE approval_list_header_id = p_approval_list_header_id;
2531
2532 p_return_code := E_SUCCESS;
2533
2534 EXCEPTION
2535 WHEN OTHERS THEN
2536 p_return_code := SQLCODE;
2537 END update_approval_list_itemkey;
2538
2539 PROCEDURE update_approval_list_response(p_document_id IN NUMBER,
2540 p_document_type IN VARCHAR2,
2541 p_document_subtype IN VARCHAR2,
2542 p_itemtype IN VARCHAR2,
2543 p_itemkey IN VARCHAR2,
2544 p_approver_id IN NUMBER,
2545 p_responder_id IN NUMBER,
2546 p_forward_to_id IN NUMBER,
2547 p_response IN VARCHAR2,
2548 p_response_date IN DATE,
2549 p_comments IN VARCHAR2,
2550 p_return_code OUT NOCOPY NUMBER) IS
2551 pragma AUTONOMOUS_TRANSACTION;
2552
2553 l_progress VARCHAR2(10) := '000';
2554 l_approval_list_header_id NUMBER;
2555 l_current_sequence_num NUMBER;
2556 l_approval_list_line_id NUMBER;
2557 l_return_code NUMBER;
2558
2559 l_line_found BOOLEAN;
2560
2561 BEGIN
2562
2563 l_progress := '005';
2564
2565 SELECT approval_list_header_id,
2566 NVL(current_sequence_num, 0)
2567 INTO l_approval_list_header_id,
2568 l_current_sequence_num
2569 FROM po_approval_list_headers
2570 WHERE document_id = p_document_id
2571 AND document_type = p_document_type
2572 AND document_subtype = p_document_subtype
2573 AND wf_item_type = p_itemtype
2574 AND wf_item_key = p_itemkey
2575 AND latest_revision = 'Y'
2576 FOR UPDATE;
2577
2578 BEGIN
2579 l_progress := '006';
2580
2581 SELECT approval_list_line_id
2582 INTO l_approval_list_line_id
2583 FROM po_approval_list_lines
2584 WHERE approval_list_header_id = l_approval_list_header_id
2585 AND approver_id = p_approver_id
2586 AND sequence_num = l_current_sequence_num
2587 FOR UPDATE;
2588
2589 l_line_found := TRUE;
2590
2591 EXCEPTION
2592 WHEN NO_DATA_FOUND THEN
2593
2594 /* Bug 2092663 by dkfchan
2595 * If there is no line to update. Add a new one
2596 */
2597
2598 l_line_found := FALSE;
2599
2600 END;
2601
2602 l_progress := '007';
2603
2604 IF l_line_found THEN
2605
2606 UPDATE po_approval_list_lines
2607 SET status = p_response,
2608 forward_to_id = p_forward_to_id,
2609 responder_id = p_responder_id,
2610 response_date = p_response_date,
2611 comments = substrb(p_comments,1,480),
2612 last_update_date = SYSDATE,
2613 last_updated_by = fnd_global.user_id,
2614 last_update_login = fnd_global.login_id
2615 WHERE approval_list_line_id = l_approval_list_line_id;
2616
2617 UPDATE po_approval_list_headers
2618 SET last_update_date = SYSDATE,
2619 last_updated_by = fnd_global.user_id,
2620 last_update_login = fnd_global.login_id
2621 WHERE approval_list_header_id = l_approval_list_header_id;
2622
2623 ELSE
2624
2625 /* Bug 2092663: Add a new line if there is header but no line */
2626
2627 INSERT INTO po_approval_list_lines (
2628 approval_list_header_id,
2629 approval_list_line_id,
2630 next_element_id,
2631 approver_id,
2632 sequence_num,
2633 notification_id,
2634 notification_role,
2635 responder_id,
2636 forward_to_id,
2637 mandatory_flag,
2638 requires_reapproval_flag,
2639 approver_type,
2640 status,
2641 response_date,
2642 comments,
2643 created_by,
2644 creation_date,
2645 last_update_login,
2646 last_updated_by,
2647 last_update_date)
2648 VALUES
2649 (
2650 l_approval_list_header_id,
2651 po_approval_list_lines_s.nextval,
2652 NULL,
2653 p_responder_id,
2654 1,
2655 null,
2656 null,
2657 p_responder_id,
2658 p_forward_to_id,
2659 'N',
2660 'N',
2661 'FORWARD',
2662 'FORWARD',
2663 SYSDATE,
2664 '',
2665 fnd_global.user_id,
2666 SYSDATE,
2667 fnd_global.login_id,
2668 fnd_global.user_id,
2669 SYSDATE
2670 );
2671
2672 UPDATE po_approval_list_headers
2673 SET current_sequence_num = 1,
2674 last_update_date = SYSDATE,
2675 last_updated_by = fnd_global.user_id,
2676 last_update_login = fnd_global.login_id
2677 WHERE approval_list_header_id = l_approval_list_header_id;
2678
2679 END IF;
2680
2681 l_progress := '008';
2682
2683 COMMIT;
2684
2685 p_return_code := E_SUCCESS;
2686
2687 EXCEPTION
2688 WHEN OTHERS THEN
2689 p_return_code := SQLCODE;
2690 ROLLBACK TO UPDATE_RESPONSE;
2691 END update_approval_list_response;
2692
2693
2694
2695 PROCEDURE is_approval_list_exhausted(p_document_id IN VARCHAR2,
2696 p_document_type IN VARCHAR2,
2697 p_document_subtype IN VARCHAR2,
2698 p_itemtype IN VARCHAR2,
2699 p_itemkey IN VARCHAR2,
2700 p_return_code OUT NOCOPY NUMBER,
2701 p_result OUT NOCOPY BOOLEAN) IS
2702 l_progress VARCHAR2(10) := '000';
2703 l_approval_list_header_id NUMBER;
2704 l_current_sequence_num NUMBER;
2705 l_count NUMBER;
2706
2707 BEGIN
2708
2709 l_progress := '001';
2710
2711 SELECT approval_list_header_id,
2712 NVL(current_sequence_num, 0)
2713 INTO l_approval_list_header_id,
2714 l_current_sequence_num
2715 FROM po_approval_list_headers
2716 WHERE document_id = p_document_id
2717 AND document_type = p_document_type
2718 AND document_subtype = p_document_subtype
2719 AND wf_item_type = p_itemtype
2720 AND wf_item_key = p_itemkey
2721 AND latest_revision = 'Y';
2722
2723 l_progress := '002';
2724
2725 SELECT COUNT(*)
2726 INTO l_count
2727 FROM po_approval_list_lines
2728 WHERE approval_list_header_id = l_approval_list_header_id
2729 AND sequence_num > l_current_sequence_num;
2730
2731 p_return_code := E_SUCCESS;
2732 IF (l_count > 0) THEN
2733 p_result := FALSE;
2734 ELSE
2735 p_result := TRUE;
2736 END IF;
2737
2738 EXCEPTION
2739 WHEN OTHERS THEN
2740 p_return_code := SQLCODE;
2741 p_result := NULL;
2742 END is_approval_list_exhausted;
2743
2744
2745 PROCEDURE print_approval_list(p_approval_list IN ApprovalListType) IS
2746 l_index NUMBER;
2747 BEGIN
2748 IF (p_approval_list.COUNT > 0) THEN
2749 -- dbms_output.put_line('Count = '||to_char(p_approval_list.COUNT));
2750 -- dbms_output.put_line('-- Approval List: -------------------------------------');
2751 l_index := p_approval_list.FIRST;
2752 WHILE (l_index IS NOT NULL) LOOP
2753 -- dbms_output.put_line('id = ' || to_char(p_approval_list(l_index).id));
2754 -- dbms_output.put_line('sequence_num = ' || to_char(p_approval_list(l_index).sequence_num));
2755 -- dbms_output.put_line('approver_id = ' || to_char(p_approval_list(l_index).approver_id));
2756 -- dbms_output.put_line('approver_disp_name = ' || p_approval_list(l_index).approver_disp_name);
2757 -- dbms_output.put_line('responder_id = ' || to_char(p_approval_list(l_index).responder_id));
2758 -- dbms_output.put_line('responder_disp_name = ' || p_approval_list(l_index).responder_disp_name);
2759 -- dbms_output.put_line('forward_to_id = ' || to_char(p_approval_list(l_index).forward_to_id));
2760 -- dbms_output.put_line('forward_to_disp_name = ' || p_approval_list(l_index).forward_to_disp_name);
2761 -- dbms_output.put_line('status = ' || p_approval_list(l_index).status);
2762 -- dbms_output.put_line('approver_type = ' || p_approval_list(l_index).approver_type);
2763 -- dbms_output.put_line('mandatory_flag = ' || p_approval_list(l_index).mandatory_flag);
2764 -- dbms_output.put_line('-------------------------------------------------------');
2765 l_index := p_approval_list.NEXT(l_index);
2766 END LOOP;
2767 ELSE
2768 NULL;
2769 -- dbms_output.put_line('-- Approval List is empty -----------------------------');
2770 END IF;
2771 END print_approval_list;
2772
2773
2774 PROCEDURE retrieve_messages(p_error_stack IN ErrorStackType,
2775 p_return_code OUT NOCOPY NUMBER,
2776 p_message_stack OUT NOCOPY MessageStackType) IS
2777 l_progress VARCHAR2(10) := '000';
2778 l_index NUMBER;
2779 l_num_tokens NUMBER;
2780 BEGIN
2781
2782 IF (p_error_stack.COUNT <= 0) THEN
2783 p_return_code := E_EMPTY_ERROR_STACK;
2784 RETURN;
2785 END IF;
2786
2787 l_index := p_error_stack.FIRST;
2788 WHILE (l_index IS NOT NULL) LOOP
2789 l_progress := '001.'||to_char(l_index);
2790 SetMessage(p_error_stack(l_index));
2791 l_progress := '002.'||to_char(l_index);
2792 p_message_stack(l_index) := fnd_message.get;
2793 l_index := p_error_stack.NEXT(l_index);
2794 END LOOP;
2795 p_return_code := E_SUCCESS;
2796
2797 EXCEPTION
2798 WHEN OTHERS THEN
2799 p_return_code := SQLCODE;
2800 END retrieve_messages;
2801
2802 PROCEDURE print_error_stack(p_error_stack IN ErrorStackType) IS
2803 l_index NUMBER;
2804 l_return_code NUMBER;
2805 l_message_stack MessageStackType;
2806 BEGIN
2807
2808 IF (p_error_stack.COUNT > 0) THEN
2809 retrieve_messages(p_error_stack=>p_error_stack,
2810 p_return_code=>l_return_code,
2811 p_message_stack=>l_message_stack);
2812 IF (l_return_code <> E_SUCCESS) THEN
2813 RETURN;
2814 END IF;
2815 -- dbms_output.put_line('-- Error Stack: ---------------------------------------');
2816 l_index := l_message_stack.FIRST;
2817 WHILE (l_index IS NOT NULL) LOOP
2818 -- dbms_output.put_line(substr(l_message_stack(l_index), 1, 250));
2819 -- dbms_output.put_line('-------------------------------------------------------');
2820 l_index := l_message_stack.NEXT(l_index);
2821 END LOOP;
2822 ELSE
2823 NULL;
2824 -- dbms_output.put_line('-- Error Stack is empty -------------------------------');
2825 END IF;
2826 END print_error_stack;
2827
2828
2829 PROCEDURE forms_rebuild_approval_list(p_document_id IN NUMBER,
2830 p_document_type IN VARCHAR2,
2831 p_document_subtype IN VARCHAR2,
2832 p_rebuild_code IN VARCHAR2,
2833 p_return_code OUT NOCOPY NUMBER,
2834 p_approval_list_header_id OUT NOCOPY NUMBER) IS
2835 l_return_code NUMBER;
2836 l_error_stack ErrorStackType;
2837 BEGIN
2838
2839 rebuild_approval_list(p_document_id=>p_document_id,
2840 p_document_type=>p_document_type,
2841 p_document_subtype=>p_document_subtype,
2842 p_rebuild_code=>p_rebuild_code,
2843 p_return_code=>l_return_code,
2844 p_error_stack=>l_error_stack,
2845 p_approval_list_header_id=>p_approval_list_header_id);
2846
2847 IF (l_return_code <> E_SUCCESS) THEN
2848 IF (l_error_stack.COUNT > 0) THEN
2849 SetMessage(l_error_stack(l_error_stack.FIRST));
2850 END IF;
2851 END IF;
2852
2853 p_return_code := l_return_code;
2854
2855 EXCEPTION
2856 WHEN OTHERS THEN
2857 p_return_code := SQLCODE;
2858 END forms_rebuild_approval_list;
2859
2860 END PO_APPROVALLIST_S1;