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