[Home] [Help]
PACKAGE BODY: APPS.PO_DOCUMENT_ACTION_UTIL
Source
1 PACKAGE BODY PO_DOCUMENT_ACTION_UTIL AS
2 -- $Header: POXDAULB.pls 120.4.12010000.1 2008/07/24 14:12:17 appldev ship $
3
4 -- Private package constants
5
6 g_pkg_name CONSTANT varchar2(30) := 'PO_DOCUMENT_ACTION_UTIL';
7 g_log_head CONSTANT VARCHAR2(50) := 'po.plsql.'|| g_pkg_name || '.';
8
9
10 -- Forward Declare Private Methods
11
12 PROCEDURE insert_auth_action_history(
13 p_document_id IN NUMBER
14 , p_revision_num IN NUMBER
15 , p_document_type IN VARCHAR2
16 , p_document_subtype IN VARCHAR2
17 , p_action IN VARCHAR2
18 , p_employee_id IN NUMBER
19 , p_offline_code IN VARCHAR2
20 , p_approval_path_id IN NUMBER
21 , p_note IN VARCHAR2
22 , p_user_id IN NUMBER
23 , p_login_id IN NUMBER
24 , x_return_status OUT NOCOPY VARCHAR2
25 );
26
27 PROCEDURE update_auth_action_history(
28 p_document_id IN NUMBER
29 , p_revision_num IN NUMBER
30 , p_document_type IN VARCHAR2
31 , p_action IN VARCHAR2
32 , p_approval_path_id IN NUMBER
33 , p_note IN VARCHAR2
34 , p_user_id IN NUMBER
35 , x_return_status OUT NOCOPY VARCHAR2
36 );
37
38 PROCEDURE handle_auth_action_history(
39 p_document_id IN NUMBER
40 , p_revision_num IN NUMBER
41 , p_document_type IN VARCHAR2
42 , p_document_subtype IN VARCHAR2
43 , p_action IN VARCHAR2
44 , p_fwd_to_id IN NUMBER
45 , p_offline_code IN VARCHAR2
46 , p_approval_path_id IN NUMBER
47 , p_note IN VARCHAR2
48 , p_employee_id IN NUMBER
49 , p_user_id IN NUMBER
50 , p_login_id IN NUMBER
51 , p_old_status IN VARCHAR2
52 , x_return_status OUT NOCOPY VARCHAR2
53 );
54
55 PROCEDURE update_doc_auth_status(
56 p_document_id IN NUMBER
57 , p_document_type IN VARCHAR2
58 , p_document_subtype IN VARCHAR2
59 , p_new_status IN VARCHAR2
60 , p_user_id IN NUMBER
61 , p_login_id IN NUMBER
62 , x_return_status OUT NOCOPY VARCHAR2
63 );
64
65 PROCEDURE update_doc_notifications(
66 p_document_id IN NUMBER
67 , p_document_type IN VARCHAR2
68 , p_document_subtype IN VARCHAR2
69 , p_notify_action IN VARCHAR2
70 , p_notify_employee IN NUMBER
71 , p_doc_creation_date IN DATE
72 , p_user_id IN NUMBER
73 , p_login_id IN NUMBER
74 , x_return_status OUT NOCOPY VARCHAR2
75 );
76
77
78 -- Public Methods
79
80 FUNCTION check_doc_state(
81 p_document_id IN NUMBER
82 , p_document_type IN VARCHAR2
83 , p_line_id IN NUMBER DEFAULT NULL
84 , p_shipment_id IN NUMBER DEFAULT NULL
85 , p_allowed_states IN PO_DOCUMENT_ACTION_UTIL.DOC_STATE_REC_TYPE
86 , x_return_status OUT NOCOPY VARCHAR2
87 ) RETURN BOOLEAN
88 IS
89
90 i BINARY_INTEGER;
91
92 l_fully_res_flag financials_system_parameters.req_encumbrance_flag%TYPE;
93 l_auth_status po_releases.authorization_status%TYPE;
94 l_head_closed po_releases.closed_code%TYPE;
95
96 l_user_hold_flag po_releases.hold_flag%TYPE;
97 l_ship_closed po_line_locations.closed_code%TYPE;
98 l_line_closed po_lines.closed_code%TYPE;
99 l_closed_code VARCHAR2(26);
100 l_frozen_flag po_releases.frozen_flag%TYPE;
101
102 l_state_found BOOLEAN;
103
104 d_progress NUMBER;
105 d_module VARCHAR2(70) := 'po.plsql.PO_DOCUMENT_ACTION_UTIL.check_doc_state';
106
107 l_ret_sts VARCHAR2(1);
108 l_ret_val BOOLEAN;
109
110 CURSOR state_rel(docid NUMBER) IS
111 SELECT nvl(por.authorization_status, 'INCOMPLETE'),
112 nvl(por.closed_code, 'OPEN'),
113 nvl(por.frozen_flag, 'N'),
114 nvl(por.hold_flag, 'N')
115 FROM po_releases por
116 WHERE por.po_release_id = docid;
117
118 CURSOR state_po(docid NUMBER) IS
119 SELECT nvl(poh.authorization_status, 'INCOMPLETE'),
120 nvl(poh.closed_code, 'OPEN'),
121 nvl(poh.frozen_flag, 'N'),
122 nvl(poh.user_hold_flag, 'N')
123 FROM po_headers poh
124 WHERE poh.po_header_id = docid;
125
126 CURSOR state_req(docid NUMBER) IS
127 SELECT nvl(prh.authorization_status, 'INCOMPLETE'),
128 nvl(prh.closed_code, 'OPEN')
129 FROM po_requisition_headers prh
130 WHERE prh.requisition_header_id = docid;
131
132 CURSOR ship_closed(shipid NUMBER) IS
133 SELECT nvl(poll.closed_code, 'OPEN')
134 FROM po_line_locations poll
135 WHERE poll.line_location_id = shipid;
136
137 CURSOR line_closed(lineid NUMBER) is
138 SELECT nvl(pol.closed_code, 'OPEN')
139 FROM po_lines pol
140 WHERE pol.po_line_id = lineid;
141
142
143 BEGIN
144
145 d_progress := 0;
146 IF (PO_LOG.d_proc) THEN
147 PO_LOG.proc_begin(d_module);
148 PO_LOG.proc_begin(d_module, 'p_document_id', p_document_id);
149 PO_LOG.proc_begin(d_module, 'p_document_type', p_document_type);
150 PO_LOG.proc_begin(d_module, 'p_line_id', p_line_id);
151 PO_LOG.proc_begin(d_module, 'p_shipment_id', p_shipment_id);
152 END IF;
153
154 l_ret_val := FALSE;
155
156 d_progress := 10;
157
158 BEGIN
159
160 IF (p_document_type = 'RELEASE')
161 THEN
162
163 d_progress := 20;
164
165 OPEN state_rel(p_document_id);
166 FETCH state_rel
167 INTO l_auth_status, l_head_closed, l_frozen_flag, l_user_hold_flag;
168 CLOSE state_rel;
169
170 d_progress := 30;
171 IF (PO_LOG.d_stmt) THEN
172 PO_LOG.stmt(d_module, d_progress, 'l_auth_status', l_auth_status);
173 PO_LOG.stmt(d_module, d_progress, 'l_head_closed', l_head_closed);
174 PO_LOG.stmt(d_module, d_progress, 'l_frozen_flag', l_frozen_flag);
175 PO_LOG.stmt(d_module, d_progress, 'l_user_hold_flag', l_user_hold_flag);
176
177 END IF;
178
179 IF (p_shipment_id IS NOT NULL)
180 THEN
181
182 d_progress := 40;
183
184 OPEN ship_closed(p_shipment_id);
185 FETCH ship_closed INTO l_ship_closed;
186 CLOSE ship_closed;
187
188 d_progress := 50;
189 IF (PO_LOG.d_stmt) THEN
190 PO_LOG.stmt(d_module, d_progress, 'l_ship_closed', l_ship_closed);
191 END IF;
192
193 END IF;
194
195 ELSIF (p_document_type in ('PO', 'PA'))
196 THEN
197
198 d_progress := 60;
199
200 OPEN state_po(p_document_id);
201 FETCH state_po
202 INTO l_auth_status, l_head_closed, l_frozen_flag, l_user_hold_flag;
203 CLOSE state_po;
204
205 d_progress := 70;
206
207 IF (PO_LOG.d_stmt) THEN
208 PO_LOG.stmt(d_module, d_progress, 'l_auth_status', l_auth_status);
209 PO_LOG.stmt(d_module, d_progress, 'l_head_closed', l_head_closed);
210 PO_LOG.stmt(d_module, d_progress, 'l_frozen_flag', l_frozen_flag);
211 PO_LOG.stmt(d_module, d_progress, 'l_user_hold_flag', l_user_hold_flag);
212
213 END IF;
214
215 IF (p_shipment_id IS NOT NULL) THEN
216
217 d_progress := 80;
218
219 OPEN ship_closed(p_shipment_id);
220 FETCH ship_closed INTO l_ship_closed;
221 CLOSE ship_closed;
222
223 d_progress := 90;
224 IF (PO_LOG.d_stmt) THEN
225 PO_LOG.stmt(d_module, d_progress, 'l_ship_closed', l_ship_closed);
226 END IF;
227
228 END IF;
229
230 IF (p_line_id IS NOT NULL) THEN
231
232 d_progress := 100;
233
234 OPEN line_closed(p_line_id);
235 FETCH line_closed INTO l_line_closed;
236 CLOSE line_closed;
237
238 d_progress := 110;
239 IF (PO_LOG.d_stmt) THEN
240 PO_LOG.stmt(d_module, d_progress, 'l_line_closed', l_line_closed);
241 END IF;
242
243 END IF;
244
245 ELSIF (p_document_type = 'REQUISITION') THEN
246
247 d_progress := 120;
248
249 OPEN state_req(p_document_id);
250 FETCH state_req INTO l_auth_status, l_head_closed;
251 CLOSE state_req;
252
253 d_progress := 130;
254 IF (PO_LOG.d_stmt) THEN
255 PO_LOG.stmt(d_module, d_progress, 'l_auth_status', l_auth_status);
256 PO_LOG.stmt(d_module, d_progress, 'l_head_closed', l_head_closed);
257 END IF;
258
259 ELSE
260
261 d_progress := 140;
262 l_ret_sts := 'U';
263 PO_DOCUMENT_ACTION_PVT.error_msg_append(d_module, d_progress, 'Bad Document Type');
264 IF (PO_LOG.d_exc) THEN
265 PO_LOG.exc(d_module, d_progress, 'Bad Document Type');
266 END IF;
267 RAISE PO_CORE_S.g_early_return_exc;
268
269 END IF;
270
271 l_state_found := FALSE;
272 d_progress := 150;
273
274 FOR i in p_allowed_states.auth_states.FIRST .. p_allowed_states.auth_states.LAST
275
276 LOOP
277 IF p_allowed_states.auth_states(i) = l_auth_status
278 THEN
279 l_state_found := TRUE;
280 EXIT;
281 END IF;
282 END LOOP;
283
284 d_progress := 160;
285 IF (PO_LOG.d_stmt) THEN
286 PO_LOG.stmt(d_module, d_progress, 'l_state_found', l_state_found);
287 END IF;
288
289 IF NOT l_state_found
290 THEN
291
292 d_progress := 170;
293 IF (PO_LOG.d_stmt) THEN
294 PO_LOG.stmt(d_module, d_progress, 'Current Authorization Status Not Allowed.');
295
296 END IF;
297
298 l_ret_sts := 'S';
299 RAISE PO_CORE_S.g_early_return_exc;
300
301 END IF;
302
303
304 l_closed_code := NVL(l_ship_closed, l_line_closed);
305 l_closed_code := NVL(l_closed_code, l_head_closed);
306
307 d_progress := 180;
308 IF (PO_LOG.d_stmt) THEN
309 PO_LOG.stmt(d_module, d_progress, 'l_closed_code', l_closed_code);
310 END IF;
311
312 l_state_found := FALSE;
313
314 FOR i in p_allowed_states.closed_states.FIRST .. p_allowed_states.closed_states.LAST
315
316 LOOP
317 IF p_allowed_states.closed_states(i) = l_closed_code
318 THEN
319 l_state_found := TRUE;
320 EXIT;
321 END IF;
322 END LOOP;
323
324 d_progress := 190;
325 IF (PO_LOG.d_stmt) THEN
326 PO_LOG.stmt(d_module, d_progress, 'l_state_found', l_state_found);
327 END IF;
328
329 IF NOT l_state_found
330 THEN
331
332 d_progress := 200;
333 IF (PO_LOG.d_stmt) THEN
334 PO_LOG.stmt(d_module, d_progress, 'Current Closed Status Not Allowed.');
335
336 END IF;
337
338 l_ret_sts := 'S';
339 RAISE PO_CORE_S.g_early_return_exc;
340
341 END IF;
342
343
344 IF ((p_allowed_states.fully_reserved_flag IS NOT NULL)
345 AND ( PO_CORE_S.is_encumbrance_on(p_doc_type => p_document_type, p_org_id => NULL)))
346
347 THEN
348
349 d_progress := 210;
350 IF (PO_LOG.d_stmt) THEN
351 PO_LOG.stmt(d_module, d_progress, 'Encumbrance is on. Checking reserved state');
352
353 END IF;
354
355 PO_CORE_S.is_fully_reserved(
356 p_doc_type => p_document_type
357 , p_doc_level => PO_CORE_S.g_doc_level_HEADER
358 , p_doc_level_id => p_document_id
359 , x_fully_reserved_flag => l_fully_res_flag
360 );
361
362 d_progress := 220;
363 IF (PO_LOG.d_stmt) THEN
364 PO_LOG.stmt(d_module, d_progress, 'l_fully_res_flag', l_fully_res_flag);
365
366 END IF;
367
368 IF (p_allowed_states.fully_reserved_flag <> l_fully_res_flag)
369 THEN
370
371 d_progress := 230;
372 IF (PO_LOG.d_stmt) THEN
373 PO_LOG.stmt(d_module, d_progress, 'Current Encumbrance reservation state not allowed.');
374
375 END IF;
376
377 l_ret_sts := 'S';
378 RAISE PO_CORE_S.g_early_return_exc;
379
380 END IF; -- IF p_allowed_states.fully_reserved_flag <> l_fully_res_flag
381
382 END IF; -- IF p_allowed_states.fully_reserved_flag IS NOT NULL
383
384 IF (p_document_type IN ('PO', 'PA', 'RELEASE'))
385 THEN
386
387 d_progress := 240;
388
389 IF ((p_allowed_states.frozen_flag IS NOT NULL)
390 AND (p_allowed_states.frozen_flag <> l_frozen_flag))
391 THEN
392
393 d_progress := 250;
394 IF (PO_LOG.d_stmt) THEN
395 PO_LOG.stmt(d_module, d_progress, 'Frozen flags do not match.');
396 PO_LOG.stmt(d_module, d_progress, 'p_allowed_states.frozen_flag', p_allowed_states.frozen_flag);
397
398 PO_LOG.stmt(d_module, d_progress, 'l_frozen_flag', l_frozen_flag);
399 END IF;
400
401 l_ret_sts := 'S';
402 RAISE PO_CORE_S.g_early_return_exc;
403
404 END IF; -- p_allowed_states.frozen_flag IS NOT NULL
405
406 IF ((p_allowed_states.hold_flag IS NOT NULL)
407 AND (p_allowed_states.hold_flag <> l_user_hold_flag))
408 THEN
409
410 d_progress := 260;
411 IF (PO_LOG.d_stmt) THEN
412 PO_LOG.stmt(d_module, d_progress, 'Hold flags do not match.');
413 PO_LOG.stmt(d_module, d_progress, 'p_allowed_states.hold_flag', p_allowed_states.hold_flag);
414
415 PO_LOG.stmt(d_module, d_progress, 'l_user_hold_flag', l_user_hold_flag);
416 END IF;
417
418 l_ret_sts := 'S';
419 RAISE PO_CORE_S.g_early_return_exc;
420
421 END IF; -- p_allowed_states.hold_flag IS NOT NULL
422
423 END IF; -- IF p_document_type IN ('PO', 'PA', 'RELEASE')
424
425 d_progress := 270;
426 l_ret_sts := 'S';
427 l_ret_val := TRUE;
428
429 EXCEPTION
430 WHEN PO_CORE_S.g_early_return_exc THEN
431 NULL;
432 END;
433
434 x_return_status := l_ret_sts;
435
436 d_progress := 280;
437 IF (PO_LOG.d_proc) THEN
438 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
439 PO_LOG.proc_return(d_module, l_ret_val);
440 PO_LOG.proc_end(d_module);
441 END IF;
442
443 RETURN (l_ret_val);
444
445 EXCEPTION
446
447 WHEN OTHERS THEN
448
449 IF state_rel%ISOPEN THEN
450 CLOSE state_rel;
451 END IF;
452
453 IF state_po%ISOPEN THEN
454 CLOSE state_po;
455 END IF;
456
457 IF state_req%ISOPEN THEN
458 CLOSE state_req;
459 END IF;
460
461 IF ship_closed%ISOPEN THEN
462 CLOSE ship_closed;
463 END IF;
464
465 IF line_closed%ISOPEN THEN
466 CLOSE line_closed;
467 END IF;
468
469 x_return_status := 'U';
470
471 PO_DOCUMENT_ACTION_PVT.error_msg_append(d_module, d_progress, SQLCODE, SQLERRM);
472 IF (PO_LOG.d_exc) THEN
473 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
474 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
475 PO_LOG.proc_return(d_module, l_ret_val);
476 PO_LOG.proc_end(d_module);
477 END IF;
478
479 return FALSE;
480
481 END check_doc_state;
482
483
484
485 PROCEDURE get_doc_preparer_id(
486 p_document_id IN NUMBER
487 , p_document_type IN VARCHAR2
488 , x_return_status OUT NOCOPY VARCHAR2
489 , x_preparer_id OUT NOCOPY NUMBER
490 )
491 IS
492
493 d_progress NUMBER;
494 d_module VARCHAR2(70) := 'po.plsql.PO_DOCUMENT_ACTION_UTIL.get_doc_preparer_id';
495
496 l_ret_sts VARCHAR2(1);
497
498 BEGIN
499
500 d_progress := 0;
501 IF (PO_LOG.d_proc) THEN
502 PO_LOG.proc_begin(d_module);
503 PO_LOG.proc_begin(d_module, 'p_document_id', p_document_id);
504 PO_LOG.proc_begin(d_module, 'p_document_type', p_document_type);
505 END IF;
506
507 l_ret_sts := 'S';
508
509 IF (p_document_type = 'RELEASE')
510 THEN
511
512 d_progress := 20;
513
514 SELECT por.agent_id
515 INTO x_preparer_id
516 FROM po_releases_all por
517 WHERE por.po_release_id = p_document_id;
518
519 d_progress := 30;
520 IF (PO_LOG.d_stmt) THEN
521 PO_LOG.stmt(d_module, d_progress, 'x_preparer_id', x_preparer_id);
522 END IF;
523
524 ELSIF (p_document_type in ('PO', 'PA'))
525 THEN
526
527 d_progress := 40;
528
529 SELECT poh.agent_id
530 INTO x_preparer_id
531 FROM po_headers_all poh
532 WHERE poh.po_header_id = p_document_id;
533
534 d_progress := 50;
535 IF (PO_LOG.d_stmt) THEN
536 PO_LOG.stmt(d_module, d_progress, 'x_preparer_id', x_preparer_id);
537 END IF;
538
539 ELSIF (p_document_type = 'REQUISITION') THEN
540
541 d_progress := 60;
542
543 SELECT porh.preparer_id
544 INTO x_preparer_id
545 FROM po_requisition_headers_all porh
546 WHERE porh.requisition_header_id = p_document_id;
547
548 d_progress := 70;
549 IF (PO_LOG.d_stmt) THEN
550 PO_LOG.stmt(d_module, d_progress, 'x_preparer_id', x_preparer_id);
551 END IF;
552
553 ELSE
554
555 l_ret_sts := 'U';
556
557 d_progress := 80;
558 PO_DOCUMENT_ACTION_PVT.error_msg_append(d_module, d_progress, 'Bad Document Type');
559 IF (PO_LOG.d_exc) THEN
560 PO_LOG.exc(d_module, d_progress, 'Bad Document Type');
561 END IF;
562
563 END IF;
564
565 x_return_status := l_ret_sts;
566
567 d_progress := 100;
568 IF (PO_LOG.d_proc) THEN
569 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
570 PO_LOG.proc_end(d_module, 'x_preparer_id', x_preparer_id);
571 PO_LOG.proc_end(d_module);
572 END IF;
573
574 RETURN;
575
576 EXCEPTION
577
578 WHEN OTHERS THEN
579 x_return_status := 'U';
580
581 PO_DOCUMENT_ACTION_PVT.error_msg_append(d_module, d_progress, SQLCODE, SQLERRM);
582 IF (PO_LOG.d_exc) THEN
583 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
584 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
585 PO_LOG.proc_end(d_module);
586 END IF;
587
588 RETURN;
589
590 END get_doc_preparer_id;
591
592
593
594 PROCEDURE get_employee_info(
595 p_user_id IN NUMBER
596 , x_return_status OUT NOCOPY VARCHAR2
597 , x_employee_flag OUT NOCOPY BOOLEAN
598 , x_employee_id OUT NOCOPY NUMBER
599 , x_employee_name OUT NOCOPY VARCHAR2
600 , x_location_id OUT NOCOPY NUMBER
601 , x_location_code OUT NOCOPY VARCHAR2
602 , x_is_buyer_flag OUT NOCOPY BOOLEAN
603 )
604 IS
605
606 d_progress NUMBER;
607 d_module VARCHAR2(70) := 'po.plsql.PO_DOCUMENT_ACTION_UTIL.get_employee_info';
608
609 l_temp_var VARCHAR2(1);
610
611 BEGIN
612
613 d_progress := 0;
614 IF (PO_LOG.d_proc) THEN
615 PO_LOG.proc_begin(d_module);
616 PO_LOG.proc_begin(d_module, 'p_user_id', p_user_id);
617 END IF;
618
619 BEGIN
620 d_progress := 10;
621
622 SELECT hr.person_id, hr.full_name, hr.location_id
623 INTO x_employee_id, x_employee_name, x_location_id
624 FROM FND_USER fnd, PO_WORKFORCE_CURRENT_X hr -- <BUG 6615913>
625 WHERE fnd.user_id = p_user_id
626 AND fnd.employee_id = hr.person_id;
627
628 x_employee_flag := TRUE;
629
630 d_progress := 20;
631 IF (PO_LOG.d_stmt) THEN
632 PO_LOG.stmt(d_module, d_progress, 'x_employee_id', x_employee_id);
633 PO_LOG.stmt(d_module, d_progress, 'x_employee_name', x_employee_name);
634 PO_LOG.stmt(d_module, d_progress, 'x_location_id', x_location_id);
635 END IF;
636
637 EXCEPTION
638 WHEN no_data_found THEN
639 x_employee_flag := FALSE;
640 x_location_id := NULL;
641 END;
642
643 d_progress := 30;
644 IF (PO_LOG.d_stmt) THEN
645 PO_LOG.stmt(d_module, d_progress, 'x_employee_flag', x_employee_flag);
646 END IF;
647
648 IF (x_location_id IS NOT NULL)
649 THEN
650
651 d_progress := 40;
652
653 BEGIN
654
655 SELECT hr.location_code
656 INTO x_location_code
657 FROM HR_LOCATIONS hr,
658 FINANCIALS_SYSTEM_PARAMETERS fsp,
659 ORG_ORGANIZATION_DEFINITIONS ood
660 WHERE hr.location_id = x_location_id
661 AND hr.inventory_organization_id = ood.organization_id (+)
662 AND nvl(ood.set_of_books_id, fsp.set_of_books_id) = fsp.set_of_books_id;
663
664 d_progress := 50;
665 IF (PO_LOG.d_stmt) THEN
666 PO_LOG.stmt(d_module, d_progress, 'x_location_code', x_location_code);
667 END IF;
668
669 EXCEPTION
670 WHEN no_data_found THEN
671 x_location_id := NULL;
672 END;
673
674 END IF; -- x_location_id IS NOT NULL
675
676 d_progress := 60;
677
678 IF (x_employee_flag)
679 THEN
680 BEGIN
681
682 SELECT 'X'
683 INTO l_temp_var
684 FROM po_agents poa
685 WHERE poa.agent_id = x_employee_id
686 AND SYSDATE between nvl(poa.start_date_active, SYSDATE - 1) and NVL(poa.end_date_active, SYSDATE + 1);
687
688 x_is_buyer_flag := TRUE;
689
690 d_progress := 70;
691 IF (PO_LOG.d_stmt) THEN
692 PO_LOG.stmt(d_module, d_progress, 'x_is_buyer_flag', x_is_buyer_flag);
693 END IF;
694
695 EXCEPTION
696 WHEN no_data_found THEN
697 x_is_buyer_flag := FALSE;
698 d_progress := 75;
699 IF (PO_LOG.d_stmt) THEN
700 PO_LOG.stmt(d_module, d_progress, 'x_is_buyer_flag', x_is_buyer_flag);
701 END IF;
702 END;
703
704 END IF; -- if x_employee_flag
705
706 x_return_status := 'S';
707 d_progress := 100;
708 IF (PO_LOG.d_proc) THEN
709 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
710 PO_LOG.proc_end(d_module, 'x_employee_flag', x_employee_flag);
711 PO_LOG.proc_end(d_module, 'x_employee_id', x_employee_id);
712 PO_LOG.proc_end(d_module, 'x_employee_name', x_employee_name);
713 PO_LOG.proc_end(d_module, 'x_location_id', x_location_id);
714 PO_LOG.proc_end(d_module, 'x_location_code', x_location_code);
715 PO_LOG.proc_end(d_module, 'x_is_buyer_flag', x_is_buyer_flag);
716 PO_LOG.proc_end(d_module);
717 END IF;
718
719 RETURN;
720
721 EXCEPTION
722 WHEN OTHERS THEN
723 x_return_status := 'U';
724
725 PO_DOCUMENT_ACTION_PVT.error_msg_append(d_module, d_progress, SQLCODE, SQLERRM);
726 IF (PO_LOG.d_exc) THEN
727 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
728 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
729 PO_LOG.proc_end(d_module);
730 END IF;
731
732 RETURN;
733
734 END get_employee_info;
735
736
737 PROCEDURE get_employee_id(
738 p_user_id IN NUMBER
739 , x_return_status OUT NOCOPY VARCHAR2
740 , x_employee_flag OUT NOCOPY BOOLEAN
741 , x_employee_id OUT NOCOPY NUMBER
742 )
743 IS
744
745 d_progress NUMBER;
746 d_module VARCHAR2(70) := 'po.plsql.PO_DOCUMENT_ACTION_UTIL.get_employee_id';
747
748 BEGIN
749
750 d_progress := 0;
751 IF (PO_LOG.d_proc) THEN
752 PO_LOG.proc_begin(d_module);
753 PO_LOG.proc_begin(d_module, 'p_user_id', p_user_id);
754 END IF;
755
756 BEGIN
757
758 d_progress := 10;
759
760 SELECT hr.person_id
761 INTO x_employee_id
762 FROM FND_USER fnd, PER_WORKFORCE_CURRENT_X hr --R12 CWK Enhancement
763 WHERE fnd.user_id = p_user_id
764 AND fnd.employee_id = hr.person_id;
765
766 x_employee_flag := TRUE;
767
768 d_progress := 20;
769 IF (PO_LOG.d_stmt) THEN
770 PO_LOG.stmt(d_module, d_progress, 'x_employee_id', x_employee_id);
771 END IF;
772
773 EXCEPTION
774 WHEN no_data_found THEN
775 x_employee_flag := FALSE;
776 x_employee_id := NULL;
777 END;
778
779
780 x_return_status := 'S';
781 d_progress := 100;
782 IF (PO_LOG.d_proc) THEN
783 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
784 PO_LOG.proc_end(d_module, 'x_employee_flag', x_employee_flag);
785 PO_LOG.proc_end(d_module, 'x_employee_id', x_employee_id);
786 PO_LOG.proc_end(d_module);
787 END IF;
788
789 RETURN;
790
791 EXCEPTION
792 WHEN OTHERS THEN
793 x_return_status := 'U';
794
795 PO_DOCUMENT_ACTION_PVT.error_msg_append(d_module, d_progress, SQLCODE, SQLERRM);
796 IF (PO_LOG.d_exc) THEN
797 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
798 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
799 PO_LOG.proc_end(d_module);
800 END IF;
801
802 RETURN;
803
804 END get_employee_id;
805
806
807 PROCEDURE change_doc_auth_state(
808 p_document_id IN NUMBER
809 , p_document_type IN VARCHAR2
810 , p_document_subtype IN VARCHAR2
811 , p_action IN VARCHAR2
812 , p_fwd_to_id IN NUMBER
813 , p_offline_code IN VARCHAR2
814 , p_approval_path_id IN NUMBER
815 , p_note IN VARCHAR2
816 , p_new_status IN VARCHAR2
817 , p_notify_action IN VARCHAR2
818 , p_notify_employee IN NUMBER
819 , x_return_status OUT NOCOPY VARCHAR2
820 )
821 IS
822
823 l_user_id NUMBER;
824 l_login_id NUMBER;
825
826 l_ret_sts VARCHAR2(1);
827 l_err_msg VARCHAR2(200);
828
829 l_emp_flag BOOLEAN;
830 l_emp_id PER_EMPLOYEES_CURRENT_X.employee_id%TYPE;
831
832 l_old_status PO_HEADERS.authorization_status%TYPE;
833 l_creation_date PO_HEADERS.creation_date%TYPE;
834 l_revision_num PO_HEADERS.revision_num%TYPE;
835
836 d_progress NUMBER;
837 d_module VARCHAR2(70) := 'po.plsql.PO_DOCUMENT_ACTION_UTIL.change_doc_auth_state';
838
839 BEGIN
840
841 d_progress := 0;
842 IF (PO_LOG.d_proc) THEN
843 PO_LOG.proc_begin(d_module);
844 PO_LOG.proc_begin(d_module, 'p_document_id', p_document_id);
845 PO_LOG.proc_begin(d_module, 'p_document_type', p_document_type);
846 PO_LOG.proc_begin(d_module, 'p_document_subtype', p_document_subtype);
847 PO_LOG.proc_begin(d_module, 'p_action', p_action);
848 PO_LOG.proc_begin(d_module, 'p_fwd_to_id', p_fwd_to_id);
849 PO_LOG.proc_begin(d_module, 'p_offline_code', p_offline_code);
850 PO_LOG.proc_begin(d_module, 'p_approval_path_id', p_approval_path_id);
851 PO_LOG.proc_begin(d_module, 'p_note', p_note);
852 PO_LOG.proc_begin(d_module, 'p_new_status', p_new_status);
853 PO_LOG.proc_begin(d_module, 'p_notify_action', p_notify_action);
854 PO_LOG.proc_begin(d_module, 'p_notify_employee', p_notify_employee);
855 END IF;
856
857 l_user_id := FND_GLOBAL.USER_ID;
858 l_login_id := FND_GLOBAL.LOGIN_ID;
859
860 d_progress := 10;
861 IF (PO_LOG.d_stmt) THEN
862 PO_LOG.stmt(d_module, d_progress, 'l_user_id', l_user_id);
863 PO_LOG.stmt(d_module, d_progress, 'l_login_id', l_login_id);
864 END IF;
865
866 BEGIN
867
868 get_employee_id(
869 p_user_id => l_user_id
870 , x_return_status => l_ret_sts
871 , x_employee_flag => l_emp_flag
872 , x_employee_id => l_emp_id
873 );
874
875 IF (l_ret_sts <> 'S')
876 THEN
877
878 d_progress := 20;
879 l_err_msg := 'get_employee_id not successful';
880 RAISE PO_CORE_S.g_early_return_exc;
881
882 END IF;
883
884 d_progress := 30;
885
886 IF (NOT l_emp_flag)
887 THEN
888
889 l_emp_id := NULL;
890
891 IF (PO_LOG.d_stmt) THEN
892 PO_LOG.stmt(d_module, d_progress, 'user is not employee');
893 END IF;
894
895 END IF;
896
897 d_progress := 40;
898
899 IF (p_document_type = 'REQUISITION')
900 THEN
901
902 d_progress := 50;
903
904 SELECT porh.authorization_status, porh.creation_date, 0
905 INTO l_old_status, l_creation_date, l_revision_num
906 FROM PO_REQUISITION_HEADERS porh
907 WHERE porh.requisition_header_id = p_document_id;
908
909 ELSIF (p_document_type IN ('PA', 'PO'))
910 THEN
911
912 d_progress := 60;
913
914 SELECT NVL(poh.authorization_status, PO_DOCUMENT_ACTION_PVT.g_doc_status_INCOMPLETE),
915 poh.creation_date,
916 poh.revision_num
917 INTO l_old_status,
918 l_creation_date,
919 l_revision_num
920 FROM PO_HEADERS poh
921 WHERE poh.po_header_id = p_document_id;
922
923 ELSIF (p_document_type = 'RELEASE')
924 THEN
925
926 d_progress := 70;
927
928 SELECT NVL(por.authorization_status, PO_DOCUMENT_ACTION_PVT.g_doc_status_INCOMPLETE),
929 por.creation_date,
930 por.revision_num
931 INTO l_old_status,
932 l_creation_date,
933 l_revision_num
934 FROM PO_RELEASES por
935 WHERE por.po_release_id = p_document_id; -- <Bug 4118145 - Issue 2>
936
937 ELSE
938
939 d_progress := 80;
940 l_err_msg := 'Bad Document Type';
941 RAISE PO_CORE_S.g_early_return_exc;
942
943 END IF;
944
945 d_progress := 90;
946 IF (PO_LOG.d_stmt) THEN
947 PO_LOG.stmt(d_module, d_progress, 'l_old_status', l_old_status);
948 PO_LOG.stmt(d_module, d_progress, 'l_creation_date', l_creation_date);
949 PO_LOG.stmt(d_module, d_progress, 'l_revision_num', l_revision_num);
950 END IF;
951
952 handle_auth_action_history(
953 p_document_id => p_document_id
954 , p_revision_num => l_revision_num
955 , p_document_type => p_document_type
956 , p_document_subtype => p_document_subtype
957 , p_action => p_action
958 , p_fwd_to_id => p_fwd_to_id
959 , p_offline_code => p_offline_code
960 , p_approval_path_id => p_approval_path_id
961 , p_note => p_note
962 , p_employee_id => l_emp_id
963 , p_user_id => l_user_id
964 , p_login_id => l_login_id
965 , p_old_status => l_old_status
966 , x_return_status => l_ret_sts
967 );
968
969 IF (l_ret_sts <> 'S')
970 THEN
971
972 d_progress := 100;
973 l_err_msg := 'handle_auth_action_history not successful';
974 RAISE PO_CORE_S.g_early_return_exc;
975
976 END IF;
977
978 d_progress := 110;
979
980 IF (p_new_status IS NOT NULL)
981 THEN
982
983 update_doc_auth_status(
984 p_document_id => p_document_id
985 , p_document_type => p_document_type
986 , p_document_subtype => p_document_subtype
987 , p_new_status => p_new_status
988 , p_user_id => l_user_id
989 , p_login_id => l_login_id
990 , x_return_status => l_ret_sts
991 );
992
993 IF (l_ret_sts <> 'S')
994 THEN
995
996 d_progress := 120;
997 l_err_msg := 'update_doc_auth_status not successful';
998 RAISE PO_CORE_S.g_early_return_exc;
999
1000 END IF;
1001
1002 END IF; -- p_new_status IS NOT NULL
1003
1004 l_ret_sts := 'S';
1005
1006 EXCEPTION
1007 WHEN PO_CORE_S.g_early_return_exc THEN
1008 l_ret_sts := 'U';
1009 PO_DOCUMENT_ACTION_PVT.error_msg_append(d_module, d_progress, l_err_msg);
1010 IF (PO_LOG.d_exc) THEN
1011 PO_LOG.exc(d_module, d_progress, l_err_msg);
1012 END IF;
1013
1014 END;
1015
1016 x_return_status := l_ret_sts;
1017 d_progress := 130;
1018 IF (PO_LOG.d_proc) THEN
1019 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
1020 PO_LOG.proc_end(d_module);
1021 END IF;
1022
1023 RETURN;
1024
1025 EXCEPTION
1026 WHEN OTHERS THEN
1027 x_return_status := 'U';
1028
1029 PO_DOCUMENT_ACTION_PVT.error_msg_append(d_module, d_progress, SQLCODE, SQLERRM);
1030 IF (PO_LOG.d_exc) THEN
1031 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
1032 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
1033 PO_LOG.proc_end(d_module);
1034 END IF;
1035
1036 RETURN;
1037
1038 END change_doc_auth_state;
1039
1040
1041
1042 PROCEDURE handle_ctl_action_history(
1043 p_document_id IN NUMBER
1044 , p_document_type IN VARCHAR2
1045 , p_document_subtype IN VARCHAR2
1046 , p_line_id IN NUMBER
1047 , p_shipment_id IN NUMBER
1048 , p_action IN VARCHAR2
1049 , p_reason IN VARCHAR2
1050 , p_user_id IN NUMBER
1051 , p_login_id IN NUMBER
1052 , x_return_status OUT NOCOPY VARCHAR2
1053 )
1054 IS
1055
1056 d_progress NUMBER;
1057 d_module VARCHAR2(70) := 'po.plsql.PO_DOCUMENT_ACTION_UTIL.handle_ctl_action_history';
1058 d_msg VARCHAR2(200);
1059
1060 l_ret_sts VARCHAR2(1);
1061
1062 l_emp_flag BOOLEAN;
1063 l_emp_id NUMBER;
1064
1065 l_rollup_msg VARCHAR2(256);
1066 l_ctl_replaced_null_entry BOOLEAN := FALSE;
1067 l_count_hist NUMBER;
1068
1069 l_revision_num PO_HEADERS_ALL.revision_num%TYPE;
1070
1071 BEGIN
1072
1073 d_progress := 0;
1074 IF (PO_LOG.d_proc) THEN
1075 PO_LOG.proc_begin(d_module);
1076 PO_LOG.proc_begin(d_module, 'p_document_id', p_document_id);
1077 PO_LOG.proc_begin(d_module, 'p_document_type', p_document_type);
1078 PO_LOG.proc_begin(d_module, 'p_document_subtype', p_document_subtype);
1079 PO_LOG.proc_begin(d_module, 'p_action', p_action);
1080 PO_LOG.proc_begin(d_module, 'p_reason', p_reason);
1081 PO_LOG.proc_begin(d_module, 'p_line_id', p_line_id);
1082 PO_LOG.proc_begin(d_module, 'p_shipment_id', p_shipment_id);
1083 PO_LOG.proc_begin(d_module, 'p_user_id', p_user_id);
1084 PO_LOG.proc_begin(d_module, 'p_login_id', p_login_id);
1085 END IF;
1086
1087 d_progress := 10;
1088
1089 BEGIN
1090
1091 get_employee_id(
1092 p_user_id => p_user_id
1093 , x_return_status => l_ret_sts
1094 , x_employee_flag => l_emp_flag
1095 , x_employee_id => l_emp_id
1096 );
1097
1098 IF (l_ret_sts <> 'S')
1099 THEN
1100
1101 d_progress := 20;
1102 d_msg := 'get_employee_id not successful';
1103 RAISE PO_CORE_S.g_early_return_exc;
1104
1105 END IF;
1106
1107 d_progress := 30;
1108
1109 IF (l_emp_flag IS NULL) THEN
1110 l_emp_id := NULL;
1111 END IF;
1112
1113 IF (PO_LOG.d_stmt) THEN
1114 PO_LOG.stmt(d_module, d_progress, 'l_emp_flag', l_emp_flag);
1115 PO_LOG.stmt(d_module, d_progress, 'l_emp_id', l_emp_id);
1116 END IF;
1117
1118 d_progress := 40;
1119
1120 l_rollup_msg := substr(FND_MESSAGE.GET_STRING('PO', 'PO_CLOSE_ROLLUP'), 1, 256);
1121
1122 IF (p_action IN ('CANCEL', 'FINALLY CLOSE'))
1123 THEN
1124
1125 d_progress := 50;
1126
1127 UPDATE po_action_history poah
1128 SET poah.action_code = p_action
1129 , poah.action_date = SYSDATE
1130 , poah.offline_code = NULL
1131 , poah.employee_id = l_emp_id
1132 , poah.note = DECODE(p_shipment_id, NULL,
1133 DECODE(p_line_id, NULL, p_reason, l_rollup_msg),
1134 l_rollup_msg)
1135 , poah.last_updated_by = p_user_id
1136 , poah.last_update_date = SYSDATE
1137 WHERE poah.object_id = p_document_id
1138 AND poah.object_type_code = p_document_type
1139 AND poah.object_sub_type_code = p_document_subtype
1140 AND poah.action_code IS NULL;
1141
1142 d_progress := 60;
1143
1144 IF (NOT SQL%NOTFOUND)
1145 THEN
1146
1147 l_ctl_replaced_null_entry := TRUE;
1148
1149 END IF;
1150
1151 END IF; -- if (p_action IN ('CANCEL', 'FINALLY CLOSE'))
1152
1153 d_progress := 100;
1154 IF (PO_LOG.d_stmt) THEN
1155 PO_LOG.stmt(d_module, d_progress, 'l_ctl_repaced_null_entry', l_ctl_replaced_null_entry);
1156 END IF;
1157
1158 IF (NOT l_ctl_replaced_null_entry)
1159 THEN
1160
1161 d_progress := 110;
1162
1163 UPDATE po_action_history poah
1164 SET poah.sequence_num = poah.sequence_num + 1
1165 WHERE poah.object_id = p_document_id
1166 AND poah.object_type_code = p_document_type
1167 AND poah.object_sub_type_code = p_document_subtype
1168 AND poah.action_code IS NULL;
1169
1170 d_progress := 120;
1171
1172 -- Bug 3136474: Was in Pro*C, but not in PL/SQL API
1173 -- Ported it over, as it will fix a PDOI issue.
1174
1175 SELECT count(1)
1176 INTO l_count_hist
1177 FROM po_action_history poah
1178 WHERE poah.object_id = p_document_id
1179 AND poah.object_type_code = p_document_type
1180 AND poah.object_sub_type_code = p_document_subtype
1181 AND poah.action_code IS NOT NULL;
1182
1183 d_progress := 130;
1184
1185 IF (PO_LOG.d_stmt) THEN
1186 PO_LOG.stmt(d_module, d_progress, 'l_count_hist', l_count_hist);
1187 END IF;
1188
1189 IF (l_count_hist > 0)
1190 THEN
1191
1192 d_progress := 140;
1193
1194 -- Used Pro*C insert statement, as it was more accurate
1195 -- This SQL comes from: pocah.lpc
1196 -- The one in old POXPOACB.pls was incorrect.
1197
1198 INSERT INTO po_action_history(
1199 object_id
1200 , object_type_code
1201 , object_sub_type_code
1202 , sequence_num
1203 , last_update_date
1204 , last_updated_by
1205 , creation_date
1206 , created_by
1207 , action_code
1208 , action_date
1209 , employee_id
1210 , note
1211 , object_revision_num
1212 , last_update_login
1213 , request_id
1214 , program_application_id
1215 , program_id
1216 , program_update_date
1217 , approval_path_id
1218 , offline_code
1219 )
1220 SELECT
1221 poah.object_id
1222 , poah.object_type_code
1223 , poah.object_sub_type_code
1224 , max(poah.sequence_num) + 1
1225 , SYSDATE
1226 , p_user_id
1227 , SYSDATE
1228 , p_user_id
1229 , p_action
1230 , SYSDATE
1231 , l_emp_id
1232 , DECODE(p_shipment_id,
1233 NULL, DECODE(p_line_id, NULL, p_reason, l_rollup_msg),
1234 l_rollup_msg)
1235 , max(poah.object_revision_num)
1236 , p_login_id
1237 , 0
1238 , 0
1239 , 0
1240 , ''
1241 , 0
1242 , ''
1243 FROM po_action_history poah
1244 WHERE poah.object_id = p_document_id
1245 AND poah.object_type_code = p_document_type
1246 AND poah.object_sub_type_code = p_document_subtype
1247 AND poah.action_code IS NOT NULL
1248 GROUP BY poah.object_id
1249 , poah.object_type_code
1250 , poah.object_sub_type_code
1251 ;
1252
1253 ELSE
1254
1255 d_progress := 150;
1256
1257 -- only PDOI should come here
1258 -- so we're safe using headers_all
1259
1260 SELECT max(poh.revision_num)
1261 INTO l_revision_num
1262 FROM po_headers_all poh
1263 WHERE poh.po_header_id = p_document_id;
1264
1265 d_progress := 160;
1266 IF (PO_LOG.d_stmt) THEN
1267 PO_LOG.stmt(d_module, d_progress, 'l_revision_num', l_revision_num);
1268 END IF;
1269
1270 INSERT INTO po_action_history(
1271 object_id
1272 , object_type_code
1273 , object_sub_type_code
1274 , sequence_num
1275 , last_update_date
1276 , last_updated_by
1277 , creation_date
1278 , created_by
1279 , action_code
1280 , action_date
1281 , employee_id
1282 , note
1283 , object_revision_num
1284 , last_update_login
1285 , request_id
1286 , program_application_id
1287 , program_id
1288 , program_update_date
1289 , approval_path_id
1290 , offline_code
1291 ) VALUES (
1292 p_document_id
1293 , p_document_type
1294 , p_document_subtype
1295 , 0 -- Bug 4118145, Issue 3: sequence_num starts at 0
1296 , SYSDATE
1297 , p_user_id
1298 , SYSDATE
1299 , p_user_id
1300 , p_action
1301 , SYSDATE
1302 , l_emp_id
1303 , DECODE(p_shipment_id,
1304 NULL, DECODE(p_line_id, NULL, p_reason, l_rollup_msg),
1305 l_rollup_msg)
1306 , l_revision_num
1307 , p_login_id
1308 , 0
1309 , 0
1310 , 0
1311 , ''
1312 , 0
1313 , ''
1314 );
1315
1316 END IF;
1317
1318 END IF; -- if (not l_ctl_replaced_null_entry)
1319
1320 l_ret_sts := 'S';
1321
1322 EXCEPTION
1323 WHEN PO_CORE_S.g_early_return_exc THEN
1324 l_ret_sts := 'U';
1325 PO_DOCUMENT_ACTION_PVT.error_msg_append(d_module, d_progress, d_msg);
1326 IF (PO_LOG.d_exc) THEN
1327 PO_LOG.exc(d_module, d_progress, d_msg);
1328 END IF;
1329 END;
1330
1331 x_return_status := l_ret_sts;
1332
1333 IF (PO_LOG.d_proc) THEN
1334 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
1335 PO_LOG.proc_end(d_module);
1336 END IF;
1337
1338 RETURN;
1339
1340 EXCEPTION
1341 WHEN OTHERS THEN
1342 x_return_status := 'U';
1343
1344 PO_DOCUMENT_ACTION_PVT.error_msg_append(d_module, d_progress, SQLCODE, SQLERRM);
1345 IF (PO_LOG.d_exc) THEN
1346 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
1347 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
1348 PO_LOG.proc_end(d_module);
1349 END IF;
1350
1351 RETURN;
1352
1353 END handle_ctl_action_history;
1354
1355 -- Private Methods
1356
1357 PROCEDURE handle_auth_action_history(
1358 p_document_id IN NUMBER
1359 , p_revision_num IN NUMBER
1360 , p_document_type IN VARCHAR2
1361 , p_document_subtype IN VARCHAR2
1362 , p_action IN VARCHAR2
1363 , p_fwd_to_id IN NUMBER
1364 , p_offline_code IN VARCHAR2
1365 , p_approval_path_id IN NUMBER
1366 , p_note IN VARCHAR2
1367 , p_employee_id IN NUMBER
1368 , p_user_id IN NUMBER
1369 , p_login_id IN NUMBER
1370 , p_old_status IN VARCHAR2
1371 , x_return_status OUT NOCOPY VARCHAR2
1372 )
1373 IS
1374
1375 l_ret_sts VARCHAR2(1);
1376
1377 d_progress NUMBER;
1378 d_module VARCHAR2(70) := 'po.plsql.PO_DOCUMENT_ACTION_UTIL.handle_auth_action_history';
1379
1380 BEGIN
1381
1382 d_progress := 0;
1383 IF (PO_LOG.d_proc) THEN
1384 PO_LOG.proc_begin(d_module);
1385 PO_LOG.proc_begin(d_module, 'p_document_id', p_document_id);
1386 PO_LOG.proc_begin(d_module, 'p_document_type', p_document_type);
1387 PO_LOG.proc_begin(d_module, 'p_document_subtype', p_document_subtype);
1388 PO_LOG.proc_begin(d_module, 'p_action', p_action);
1389 PO_LOG.proc_begin(d_module, 'p_fwd_to_id', p_fwd_to_id);
1390 PO_LOG.proc_begin(d_module, 'p_offline_code', p_offline_code);
1391 PO_LOG.proc_begin(d_module, 'p_approval_path_id', p_approval_path_id);
1392 PO_LOG.proc_begin(d_module, 'p_note', p_note);
1393 PO_LOG.proc_begin(d_module, 'p_employee_id', p_employee_id);
1394 PO_LOG.proc_begin(d_module, 'p_user_id', p_user_id);
1395 PO_LOG.proc_begin(d_module, 'p_login_id', p_login_id);
1396 PO_LOG.proc_begin(d_module, 'p_old_status', p_old_status);
1397 END IF;
1398
1399 d_progress := 10;
1400
1401 BEGIN
1402
1403 IF (p_old_status IN (PO_DOCUMENT_ACTION_PVT.g_doc_status_RETURNED,
1404 PO_DOCUMENT_ACTION_PVT.g_doc_status_REJECTED,
1405 PO_DOCUMENT_ACTION_PVT.g_doc_status_INCOMPLETE,
1406 PO_DOCUMENT_ACTION_PVT.g_doc_status_REAPPROVAL))
1407 THEN
1408
1409 insert_auth_action_history(
1410 p_document_id => p_document_id
1411 , p_revision_num => p_revision_num
1412 , p_document_type => p_document_type
1413 , p_document_subtype => p_document_subtype
1414 , p_action => 'SUBMIT'
1415 , p_employee_id => p_employee_id
1416 , p_offline_code => NULL
1417 , p_approval_path_id => p_approval_path_id
1418 , p_note => p_note
1419 , p_user_id => p_user_id
1420 , p_login_id => p_login_id
1421 , x_return_status => l_ret_sts
1422 );
1423
1424 d_progress := 20;
1425 IF (l_ret_sts <> 'S') THEN
1426 RAISE PO_CORE_S.g_early_return_exc;
1427 END IF;
1428
1429 IF (p_action <> 'SUBMIT')
1430 THEN
1431
1432 insert_auth_action_history(
1433 p_document_id => p_document_id
1434 , p_revision_num => p_revision_num
1435 , p_document_type => p_document_type
1436 , p_document_subtype => p_document_subtype
1437 , p_action => p_action
1438 , p_employee_id => p_employee_id
1439 , p_offline_code => NULL
1440 , p_approval_path_id => p_approval_path_id
1441 , p_note => p_note
1442 , p_user_id => p_user_id
1443 , p_login_id => p_login_id
1444 , x_return_status => l_ret_sts
1445 );
1446
1447 d_progress := 30;
1448 IF (l_ret_sts <> 'S') THEN
1449 RAISE PO_CORE_S.g_early_return_exc;
1450 END IF;
1451
1452 END IF; -- p_action <> 'SUBMIT'
1453
1454 ELSIF (p_old_status IN (PO_DOCUMENT_ACTION_PVT.g_doc_status_INPROCESS,
1455 PO_DOCUMENT_ACTION_PVT.g_doc_status_PREAPPROVED))
1456 THEN
1457
1458 update_auth_action_history(
1459 p_document_id => p_document_id
1460 , p_revision_num => p_revision_num
1461 , p_document_type => p_document_type
1462 , p_action => p_action
1463 , p_approval_path_id => p_approval_path_id
1464 , p_note => p_note
1465 , p_user_id => p_user_id
1466 , x_return_status => l_ret_sts
1467 );
1468
1469 d_progress := 40;
1470 IF (l_ret_sts <> 'S') THEN
1471 RAISE PO_CORE_S.g_early_return_exc;
1472 END IF;
1473
1474 ELSIF (p_old_status IN (PO_DOCUMENT_ACTION_PVT.g_doc_status_APPROVED))
1475 THEN
1476
1477 insert_auth_action_history(
1478 p_document_id => p_document_id
1479 , p_revision_num => p_revision_num
1480 , p_document_type => p_document_type
1481 , p_document_subtype => p_document_subtype
1482 , p_action => p_action
1483 , p_employee_id => p_employee_id
1484 , p_offline_code => NULL
1485 , p_approval_path_id => p_approval_path_id
1486 , p_note => p_note
1487 , p_user_id => p_user_id
1488 , p_login_id => p_login_id
1489 , x_return_status => l_ret_sts
1490 );
1491
1492 d_progress := 50;
1493 IF (l_ret_sts <> 'S') THEN
1494 RAISE PO_CORE_S.g_early_return_exc;
1495 END IF;
1496
1497 END IF; -- p_old_status IN ...
1498
1499 IF (p_fwd_to_id IS NOT NULL)
1500 THEN
1501
1502 -- bug4363736
1503 -- when inserting NULL action row, we should populate revision_num
1504 -- with the latest revision
1505 insert_auth_action_history(
1506 p_document_id => p_document_id
1507 , p_revision_num => p_revision_num
1508 , p_document_type => p_document_type
1509 , p_document_subtype => p_document_subtype
1510 , p_action => NULL
1511 , p_employee_id => p_fwd_to_id
1512 , p_offline_code => p_offline_code
1513 , p_approval_path_id => NULL
1514 , p_note => NULL
1515 , p_user_id => p_user_id
1516 , p_login_id => p_login_id
1517 , x_return_status => l_ret_sts
1518 );
1519
1520 d_progress := 60;
1521 IF (l_ret_sts <> 'S') THEN
1522 RAISE PO_CORE_S.g_early_return_exc;
1523 END IF;
1524
1525 IF ((p_old_status = PO_DOCUMENT_ACTION_PVT.g_doc_status_PREAPPROVED)
1526 AND (p_action = PO_DOCUMENT_ACTION_PVT.g_doc_action_RESERVE))
1527 THEN
1528
1529 update_auth_action_history(
1530 p_document_id => p_document_id
1531 , p_revision_num => p_revision_num
1532 , p_document_type => p_document_type
1533 , p_action => PO_DOCUMENT_ACTION_PVT.g_doc_action_APPROVE
1534 , p_approval_path_id => p_approval_path_id
1535 , p_note => p_note
1536 , p_user_id => p_user_id
1537 , x_return_status => l_ret_sts
1538 );
1539
1540 d_progress := 70;
1541 IF (l_ret_sts <> 'S') THEN
1542 RAISE PO_CORE_S.g_early_return_exc;
1543 END IF;
1544
1545 END IF; -- p_old_status = ... and p_action =
1546
1547 ELSIF ((p_old_status = PO_DOCUMENT_ACTION_PVT.g_doc_status_INPROCESS)
1548 AND (p_action in (PO_DOCUMENT_ACTION_PVT.g_doc_action_RESERVE,
1549 PO_DOCUMENT_ACTION_PVT.g_doc_action_UNRESERVE)))
1550 THEN
1551
1552 -- bug4363736
1553 -- when inserting NULL action row, we should populate revision_num
1554 -- with the latest revision
1555 insert_auth_action_history(
1556 p_document_id => p_document_id
1557 , p_revision_num => p_revision_num
1558 , p_document_type => p_document_type
1559 , p_document_subtype => p_document_subtype
1560 , p_action => NULL
1561 , p_employee_id => p_employee_id
1562 , p_offline_code => NULL
1563 , p_approval_path_id => NULL
1564 , p_note => NULL
1565 , p_user_id => p_user_id
1566 , p_login_id => p_login_id
1567 , x_return_status => l_ret_sts
1568 );
1569
1570 d_progress := 80;
1571 IF (l_ret_sts <> 'S') THEN
1572 RAISE PO_CORE_S.g_early_return_exc;
1573 END IF;
1574
1575 END IF; -- p_fwd_to_id IS NOT NULL
1576
1577 l_ret_sts := 'S';
1578
1579 EXCEPTION
1580 WHEN PO_CORE_S.g_early_return_exc THEN
1581 l_ret_sts := 'U';
1582 PO_DOCUMENT_ACTION_PVT.error_msg_append(d_module, d_progress, 'Insert or update action history not successful');
1583 IF (PO_LOG.d_exc) THEN
1584 PO_LOG.exc(d_module, d_progress, 'Insert or update action history not successful');
1585 END IF;
1586
1587 END;
1588
1589 x_return_status := l_ret_sts;
1590 d_progress := 100;
1591 IF (PO_LOG.d_proc) THEN
1592 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
1593 PO_LOG.proc_end(d_module);
1594 END IF;
1595
1596 RETURN;
1597
1598 EXCEPTION
1599 WHEN OTHERS THEN
1600 x_return_status := 'U';
1601
1602 PO_DOCUMENT_ACTION_PVT.error_msg_append(d_module, d_progress, SQLCODE, SQLERRM);
1603 IF (PO_LOG.d_exc) THEN
1604 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
1605 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
1606 PO_LOG.proc_end(d_module);
1607 END IF;
1608
1609 RETURN;
1610
1611 END handle_auth_action_history;
1612
1613
1614 PROCEDURE update_doc_auth_status(
1615 p_document_id IN NUMBER
1616 , p_document_type IN VARCHAR2
1617 , p_document_subtype IN VARCHAR2
1618 , p_new_status IN VARCHAR2
1619 , p_user_id IN NUMBER
1620 , p_login_id IN NUMBER
1621 , x_return_status OUT NOCOPY VARCHAR2
1622 )
1623 IS
1624
1625 l_conterms_exist PO_HEADERS.conterms_exist_flag%TYPE;
1626 l_pending_signature PO_HEADERS.pending_signature_flag%TYPE;
1627
1628 l_ret_sts VARCHAR2(1);
1629 l_err_msg VARCHAR2(200);
1630 l_msg_count NUMBER;
1631 l_msg_data VARCHAR2(2000);
1632
1633 d_progress NUMBER;
1634 d_module VARCHAR2(70) := 'po.plsql.PO_DOCUMENT_ACTION_UTIL.update_doc_auth_status';
1635
1636 BEGIN
1637
1638 d_progress := 0;
1639 IF (PO_LOG.d_proc) THEN
1640 PO_LOG.proc_begin(d_module);
1641 PO_LOG.proc_begin(d_module, 'p_document_id', p_document_id);
1642 PO_LOG.proc_begin(d_module, 'p_document_type', p_document_type);
1643 PO_LOG.proc_begin(d_module, 'p_document_subtype', p_document_subtype);
1644 PO_LOG.proc_begin(d_module, 'p_new_status', p_new_status);
1645 PO_LOG.proc_begin(d_module, 'p_user_id', p_user_id);
1646 END IF;
1647
1648 BEGIN
1649
1650 IF (p_document_type = 'REQUISITION')
1651 THEN
1652
1653 d_progress := 10;
1654 --Bug 5151097 : Update Approved Date when Approved
1655 UPDATE PO_REQUISITION_HEADERS porh
1656 SET porh.authorization_status = p_new_status
1657 , porh.approved_date = DECODE (p_new_status,
1658 PO_DOCUMENT_ACTION_PVT.g_doc_status_APPROVED,
1659 SYSDATE,null)
1660 , porh.last_update_date = SYSDATE
1661 , porh.last_updated_by = p_user_id
1662 , porh.last_update_login = p_login_id
1663 WHERE porh.requisition_header_id = p_document_id;
1664
1665 -- <REQINPOOL Start>
1666 d_progress := 15;
1667
1668 PO_REQ_LINES_SV.update_reqs_in_pool_flag(
1669 x_req_line_id => NULL
1670 , x_req_header_id => p_document_id
1671 , x_return_status => l_ret_sts
1672 );
1673
1674 IF (l_ret_sts <> FND_API.G_RET_STS_SUCCESS)
1675 THEN
1676 d_progress := 17;
1677 l_err_msg := 'update_reqs_in_pool_flag not successful';
1678 RAISE PO_CORE_S.g_early_return_exc;
1679 END IF;
1680 -- <REQINPOOL End>
1681
1682 ELSIF (p_document_type IN ('PO', 'PA'))
1683 THEN
1684
1685 d_progress := 20;
1686
1687 UPDATE PO_HEADERS poh
1688 SET poh.authorization_status = p_new_status,
1689 poh.approved_flag = DECODE(p_new_status,
1690 PO_DOCUMENT_ACTION_PVT.g_doc_status_APPROVED, 'Y',
1691 PO_DOCUMENT_ACTION_PVT.g_doc_status_REAPPROVAL, 'R',
1692 PO_DOCUMENT_ACTION_PVT.g_doc_status_REJECTED, 'F',
1693 PO_DOCUMENT_ACTION_PVT.g_doc_status_RETURNED, 'F',
1694 PO_DOCUMENT_ACTION_PVT.g_doc_status_INCOMPLETE, 'N',
1695 PO_DOCUMENT_ACTION_PVT.g_doc_status_INPROCESS, 'N',
1696 PO_DOCUMENT_ACTION_PVT.g_doc_status_PREAPPROVED, 'N'),
1697 poh.approved_date = DECODE(p_new_status,
1698 PO_DOCUMENT_ACTION_PVT.g_doc_status_APPROVED, SYSDATE,
1699 poh.approved_date),
1700 poh.last_update_date = SYSDATE,
1701 poh.last_updated_by = p_user_id,
1702 poh.last_update_login = p_login_id,
1703 poh.pending_signature_flag = DECODE(p_new_status,
1704 PO_DOCUMENT_ACTION_PVT.g_doc_status_APPROVED,
1705 DECODE(poh.acceptance_required_flag, 'S', 'Y', 'N'),
1706 poh.pending_signature_flag)
1707 WHERE poh.po_header_id = p_document_id;
1708
1709
1710 IF (p_new_status = PO_DOCUMENT_ACTION_PVT.g_doc_status_APPROVED)
1711 THEN
1712
1713 d_progress := 30;
1714
1715 SELECT NVL(poh.pending_signature_flag, 'N'), NVL(poh.conterms_exist_flag, 'N')
1716 INTO l_pending_signature, l_conterms_exist
1717 FROM PO_HEADERS poh
1718 WHERE po_header_id = p_document_id;
1719
1720 d_progress := 40;
1721
1722 PO_CONTERMS_WF_PVT.UPDATE_CONTERMS_DATES(
1723 p_po_header_id => p_document_id
1724 , p_po_doc_type => p_document_type
1725 , p_po_doc_subtype => p_document_subtype
1726 , p_conterms_exist_flag => l_conterms_exist
1727 , x_return_status => l_ret_sts
1728 , x_msg_count => l_msg_count
1729 , x_msg_data => l_msg_data
1730 );
1731
1732 IF ((l_ret_sts = FND_API.G_RET_STS_UNEXP_ERROR)
1733 OR (l_ret_sts = FND_API.G_RET_STS_ERROR))
1734 THEN
1735 d_progress := 50;
1736 l_err_msg := 'update_conterms_dates not successful';
1737 RAISE PO_CORE_S.g_early_return_exc;
1738 END IF;
1739
1740 IF (l_pending_signature = 'Y')
1741 THEN
1742
1743 d_progress := 60;
1744
1745 PO_DOCUMENT_ARCHIVE_GRP.ARCHIVE_PO(
1746 p_api_version => 1.0
1747 , p_document_id => p_document_id
1748 , p_document_type => p_document_type
1749 , p_document_subtype => p_document_subtype
1750 , x_return_status => l_ret_sts
1751 , x_msg_count => l_msg_count
1752 , x_msg_data => l_msg_data
1753 );
1754
1755 IF ((l_ret_sts = FND_API.G_RET_STS_UNEXP_ERROR)
1756 OR (l_ret_sts = FND_API.G_RET_STS_ERROR))
1757 THEN
1758 d_progress := 70;
1759 l_err_msg := 'archive_po not successful';
1760 RAISE PO_CORE_S.g_early_return_exc;
1761 END IF;
1762
1763 d_progress := 80;
1764
1765 UPDATE PO_HEADERS poh
1766 SET poh.authorization_status = PO_DOCUMENT_ACTION_PVT.g_doc_status_PREAPPROVED
1767 , poh.approved_flag = 'N'
1768 , poh.last_update_date = SYSDATE
1769 , poh.last_updated_by = p_user_id
1770 , poh.last_update_login = p_login_id
1771 WHERE poh.po_header_id = p_document_id;
1772
1773
1774 END IF; -- l_pending_signature = 'Y'
1775
1776 d_progress := 90;
1777
1778 UPDATE PO_LINE_LOCATIONS_ALL poll
1779 SET poll.approved_flag = 'Y'
1780 , poll.approved_date = SYSDATE
1781 , poll.last_update_date = SYSDATE
1782 , poll.last_updated_by = p_user_id
1783 , poll.last_update_login = p_login_id
1784 WHERE poll.po_header_id = p_document_id
1785 AND poll.po_release_id IS NULL
1786 AND NVL(poll.approved_flag, 'N') <> 'Y'
1787 AND EXISTS ( SELECT 'PO Does not require signature'
1788 FROM PO_HEADERS_ALL poh
1789 WHERE poh.po_header_id = poll.po_header_id
1790 AND NVL(poh.pending_signature_flag, 'N') <> 'Y');
1791
1792 END IF; -- p_new_status = g_doc_status_APPROVED
1793
1794 IF (p_new_status = PO_DOCUMENT_ACTION_PVT.g_doc_status_REAPPROVAL)
1795 THEN
1796
1797 d_progress := 100;
1798
1799 UPDATE PO_LINE_LOCATIONS_ALL poll
1800 SET poll.approved_flag = 'R'
1801 , poll.last_update_date = SYSDATE
1802 , poll.last_updated_by = p_user_id
1803 , poll.last_update_login = p_login_id
1804 WHERE poll.po_header_id = p_document_id
1805 AND poll.po_release_id IS NULL
1806 AND NVL(poll.approved_flag, 'N') = 'Y';
1807
1808 END IF; -- p_new_status = g_doc_status_REAPPROVAL
1809
1810 ELSIF (p_document_type = 'RELEASE')
1811 THEN
1812
1813 d_progress := 110;
1814
1815 UPDATE PO_RELEASES por
1816 SET por.authorization_status = p_new_status,
1817 por.approved_flag = DECODE(p_new_status,
1818 PO_DOCUMENT_ACTION_PVT.g_doc_status_APPROVED, 'Y',
1819 PO_DOCUMENT_ACTION_PVT.g_doc_status_REAPPROVAL, 'R',
1820 PO_DOCUMENT_ACTION_PVT.g_doc_status_REJECTED, 'F',
1821 PO_DOCUMENT_ACTION_PVT.g_doc_status_RETURNED, 'F',
1822 PO_DOCUMENT_ACTION_PVT.g_doc_status_INCOMPLETE, 'N',
1823 PO_DOCUMENT_ACTION_PVT.g_doc_status_INPROCESS, 'N',
1824 PO_DOCUMENT_ACTION_PVT.g_doc_status_PREAPPROVED, 'N'),
1825 por.approved_date = DECODE(p_new_status,
1826 PO_DOCUMENT_ACTION_PVT.g_doc_status_APPROVED, SYSDATE,
1827 por.approved_date),
1828 por.last_update_date = SYSDATE,
1829 por.last_updated_by = p_user_id,
1830 por.last_update_login = p_login_id
1831 WHERE por.po_release_id = p_document_id;
1832
1833 IF (p_new_status = PO_DOCUMENT_ACTION_PVT.g_doc_status_APPROVED)
1834 THEN
1835
1836 d_progress := 120;
1837
1838 UPDATE PO_LINE_LOCATIONS_ALL poll
1839 SET poll.approved_flag = 'Y'
1840 , poll.approved_date = SYSDATE
1841 , poll.last_update_date = SYSDATE
1842 , poll.last_updated_by = p_user_id
1843 , poll.last_update_login = p_login_id
1844 WHERE poll.po_release_id = p_document_id
1845 AND NVL(poll.approved_flag, 'N') <> 'Y';
1846
1847 END IF; -- p_new_status = g_doc_status_APPROVED
1848
1849 IF (p_new_status = PO_DOCUMENT_ACTION_PVT.g_doc_status_REAPPROVAL)
1850 THEN
1851
1852 d_progress := 130;
1853
1854 UPDATE PO_LINE_LOCATIONS_ALL poll
1855 SET poll.approved_flag = 'R'
1856 , poll.last_update_date = SYSDATE
1857 , poll.last_updated_by = p_user_id
1858 , poll.last_update_login = p_login_id
1859 WHERE poll.po_release_id = p_document_id
1860 AND NVL(poll.approved_flag, 'N') = 'Y';
1861
1862 END IF; -- p_new_status = g_doc_status_REAPPROVAL
1863
1864 ELSE
1865
1866 d_progress := 140;
1867 l_err_msg := 'Bad Document Type';
1868 RAISE PO_CORE_S.g_early_return_exc;
1869
1870 END IF; -- p_document_type = 'REQUISITION'
1871
1872 d_progress := 150;
1873 l_ret_sts := 'S';
1874
1875 EXCEPTION
1876 WHEN PO_CORE_S.g_early_return_exc THEN
1877 l_ret_sts := 'U';
1878 PO_DOCUMENT_ACTION_PVT.error_msg_append(d_module, d_progress, l_err_msg);
1879 IF (PO_LOG.d_exc) THEN
1880 PO_LOG.exc(d_module, d_progress, l_err_msg);
1881 END IF;
1882 END;
1883
1884
1885 x_return_status := l_ret_sts;
1886 d_progress := 200;
1887 IF (PO_LOG.d_proc) THEN
1888 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
1889 PO_LOG.proc_end(d_module);
1890 END IF;
1891
1892 RETURN;
1893
1894 EXCEPTION
1895 WHEN OTHERS THEN
1896 x_return_status := 'U';
1897
1898 PO_DOCUMENT_ACTION_PVT.error_msg_append(d_module, d_progress, SQLCODE, SQLERRM);
1899 IF (PO_LOG.d_exc) THEN
1900 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
1901 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
1902 PO_LOG.proc_end(d_module);
1903 END IF;
1904
1905 RETURN;
1906
1907 END update_doc_auth_status;
1908
1909
1910 -- Equivalent to: podusnotif
1911 -- Does not appear to be used anymore
1912 -- Translated just in case
1913 PROCEDURE update_doc_notifications(
1914 p_document_id IN NUMBER
1915 , p_document_type IN VARCHAR2
1916 , p_document_subtype IN VARCHAR2
1917 , p_notify_action IN VARCHAR2
1918 , p_notify_employee IN NUMBER
1919 , p_doc_creation_date IN DATE
1920 , p_user_id IN NUMBER
1921 , p_login_id IN NUMBER
1922 , x_return_status OUT NOCOPY VARCHAR2
1923 )
1924 IS
1925
1926 d_progress NUMBER;
1927 d_module VARCHAR2(70) := 'po.plsql.PO_DOCUMENT_ACTION_UTIL.update_doc_notifications';
1928
1929 BEGIN
1930
1931 d_progress := 0;
1932 IF (PO_LOG.d_proc) THEN
1933 PO_LOG.proc_begin(d_module);
1934 PO_LOG.proc_begin(d_module, 'p_document_id', p_document_id);
1935 PO_LOG.proc_begin(d_module, 'p_document_type', p_document_type);
1936 PO_LOG.proc_begin(d_module, 'p_document_subtype', p_document_subtype);
1937 PO_LOG.proc_begin(d_module, 'p_notify_action', p_notify_action);
1938 PO_LOG.proc_begin(d_module, 'p_notify_employee', p_notify_employee);
1939 PO_LOG.proc_begin(d_module, 'p_doc_creation_date', p_doc_creation_date);
1940 PO_LOG.proc_begin(d_module, 'p_user_id', p_user_id);
1941 END IF;
1942
1943 d_progress := 10;
1944
1945 DELETE FROM PO_NOTIFICATIONS pon
1946 WHERE pon.object_type_lookup_code = DECODE(p_document_type,
1947 'PO', p_document_subtype,
1948 'PA', p_document_subtype,
1949 p_document_type)
1950 AND pon.object_id = p_document_id
1951 AND pon.employee_id > -1;
1952
1953
1954 IF (p_notify_action IS NOT NULL)
1955 THEN
1956
1957 d_progress := 20;
1958
1959 INSERT INTO PO_NOTIFICATIONS(
1960 employee_id
1961 , object_type_lookup_code
1962 , object_id
1963 , last_update_date
1964 , last_updated_by
1965 , last_update_login
1966 , creation_date
1967 , created_by
1968 , object_creation_date
1969 , action_lookup_code
1970 )
1971 VALUES(
1972 p_notify_employee
1973 , DECODE(p_document_type,
1974 'PO', p_document_subtype,
1975 'PA', p_document_subtype,
1976 p_document_type)
1977 , p_document_id
1978 , SYSDATE
1979 , p_user_id
1980 , p_login_id
1981 , SYSDATE
1982 , p_user_id
1983 , SYSDATE
1984 , p_notify_action
1985 );
1986
1987 END IF; -- p_notify_action IS NOT NULL
1988
1989 x_return_status := 'S';
1990 d_progress := 100;
1991 IF (PO_LOG.d_proc) THEN
1992 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
1993 PO_LOG.proc_end(d_module);
1994 END IF;
1995
1996 RETURN;
1997
1998 EXCEPTION
1999 WHEN OTHERS THEN
2000 x_return_status := 'U';
2001
2002 PO_DOCUMENT_ACTION_PVT.error_msg_append(d_module, d_progress, SQLCODE, SQLERRM);
2003 IF (PO_LOG.d_exc) THEN
2004 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
2005 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
2006 PO_LOG.proc_end(d_module);
2007 END IF;
2008
2009 RETURN;
2010
2011 END update_doc_notifications;
2012
2013
2014
2015 PROCEDURE insert_auth_action_history(
2016 p_document_id IN NUMBER
2017 , p_revision_num IN NUMBER
2018 , p_document_type IN VARCHAR2
2019 , p_document_subtype IN VARCHAR2
2020 , p_action IN VARCHAR2
2021 , p_employee_id IN NUMBER
2022 , p_offline_code IN VARCHAR2
2023 , p_approval_path_id IN NUMBER
2024 , p_note IN VARCHAR2
2025 , p_user_id IN NUMBER
2026 , p_login_id IN NUMBER
2027 , x_return_status OUT NOCOPY VARCHAR2
2028 )
2029 IS
2030
2031 l_sequence_num PO_ACTION_HISTORY.sequence_num%TYPE;
2032
2033 d_progress NUMBER;
2034 d_module VARCHAR2(70) := 'po.plsql.PO_DOCUMENT_ACTION_UTIL.insert_auth_action_history';
2035
2036 BEGIN
2037
2038 d_progress := 0;
2039 IF (PO_LOG.d_proc) THEN
2040 PO_LOG.proc_begin(d_module);
2041 PO_LOG.proc_begin(d_module, 'p_document_id', p_document_id);
2042 PO_LOG.proc_begin(d_module, 'p_revision_num', p_revision_num);
2043 PO_LOG.proc_begin(d_module, 'p_document_type', p_document_type);
2044 PO_LOG.proc_begin(d_module, 'p_document_subtype', p_document_subtype);
2045 PO_LOG.proc_begin(d_module, 'p_action', p_action);
2046 PO_LOG.proc_begin(d_module, 'p_employee_id', p_employee_id);
2047 PO_LOG.proc_begin(d_module, 'p_offline_code', p_offline_code);
2048 PO_LOG.proc_begin(d_module, 'p_approval_path_id', p_approval_path_id);
2049 PO_LOG.proc_begin(d_module, 'p_note', p_note);
2050 PO_LOG.proc_begin(d_module, 'p_user_id', p_user_id);
2051 PO_LOG.proc_begin(d_module, 'p_login_id', p_login_id);
2052 END IF;
2053
2054 d_progress := 10;
2055
2056
2057 SELECT max(poah.sequence_num) + 1
2058 INTO l_sequence_num
2059 FROM PO_ACTION_HISTORY poah
2060 WHERE poah.object_type_code = p_document_type
2061 AND poah.object_id = p_document_id;
2062
2063
2064 -- <Bug 4118145 - Issue 1 Start>
2065
2066 d_progress := 15;
2067
2068 IF (l_sequence_num IS NULL)
2069 THEN
2070 l_sequence_num := 0;
2071 END IF;
2072
2073 IF (PO_LOG.d_stmt) THEN
2074 PO_LOG.stmt(d_module, d_progress, 'l_sequence_num', l_sequence_num);
2075 END IF;
2076 -- <Bug 4118145 - Issue 1 End>
2077
2078 d_progress := 20;
2079
2080 INSERT INTO PO_ACTION_HISTORY
2081 ( object_id
2082 , object_type_code
2083 , object_sub_type_code
2084 , sequence_num
2085 , last_update_date
2086 , last_updated_by
2087 , creation_date
2088 , created_by
2089 , action_code
2090 , action_date
2091 , employee_id
2092 , note
2093 , object_revision_num
2094 , last_update_login
2095 , request_id
2096 , program_application_id
2097 , program_id
2098 , program_update_date
2099 , approval_path_id
2100 , offline_code
2101 )
2102 VALUES
2103 ( p_document_id
2104 , p_document_type
2105 , p_document_subtype
2106 , l_sequence_num
2107 , SYSDATE
2108 , p_user_id
2109 , SYSDATE
2110 , p_user_id
2111 , p_action
2112 , DECODE(p_action, '', to_date(NULL), SYSDATE)
2113 , p_employee_id
2114 , p_note
2115 , p_revision_num
2116 , p_login_id
2117 , 0
2118 , 0
2119 , 0
2120 , ''
2121 , p_approval_path_id
2122 , p_offline_code
2123 );
2124
2125 x_return_status := 'S';
2126 d_progress := 100;
2127 IF (PO_LOG.d_proc) THEN
2128 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
2129 PO_LOG.proc_end(d_module);
2130 END IF;
2131
2132 RETURN;
2133
2134 EXCEPTION
2135 WHEN OTHERS THEN
2136 x_return_status := 'U';
2137
2138 PO_DOCUMENT_ACTION_PVT.error_msg_append(d_module, d_progress, SQLCODE, SQLERRM);
2139 IF (PO_LOG.d_exc) THEN
2140 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
2141 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
2142 PO_LOG.proc_end(d_module);
2143 END IF;
2144
2145 RETURN;
2146
2147 END insert_auth_action_history;
2148
2149 PROCEDURE update_auth_action_history(
2150 p_document_id IN NUMBER
2151 , p_revision_num IN NUMBER
2152 , p_document_type IN VARCHAR2
2153 , p_action IN VARCHAR2
2154 , p_approval_path_id IN NUMBER
2155 , p_note IN VARCHAR2
2156 , p_user_id IN NUMBER
2157 , x_return_status OUT NOCOPY VARCHAR2
2158 )
2159 IS
2160
2161 d_progress NUMBER;
2162 d_module VARCHAR2(70) := 'po.plsql.PO_DOCUMENT_ACTION_UTIL.update_auth_action_history';
2163
2164 BEGIN
2165
2166 d_progress := 0;
2167 IF (PO_LOG.d_proc) THEN
2168 PO_LOG.proc_begin(d_module);
2169 PO_LOG.proc_begin(d_module, 'p_document_id', p_document_id);
2170 PO_LOG.proc_begin(d_module, 'p_revision_num', p_revision_num);
2171 PO_LOG.proc_begin(d_module, 'p_document_type', p_document_type);
2172 PO_LOG.proc_begin(d_module, 'p_action', p_action);
2173 PO_LOG.proc_begin(d_module, 'p_approval_path_id', p_approval_path_id);
2174 PO_LOG.proc_begin(d_module, 'p_note', p_note);
2175 PO_LOG.proc_begin(d_module, 'p_user_id', p_user_id);
2176 END IF;
2177
2178 d_progress := 10;
2179
2180 UPDATE PO_ACTION_HISTORY
2181 SET action_code = p_action
2182 , action_date = SYSDATE
2183 , note = p_note
2184 , last_updated_by = p_user_id
2185 , last_update_date = SYSDATE
2186 , object_revision_num = p_revision_num
2187 , approval_path_id = p_approval_path_id
2188 WHERE object_id = p_document_id
2189 AND object_type_code = p_document_type
2190 AND action_code IS NULL;
2191
2192 x_return_status := 'S';
2193 d_progress := 100;
2194 IF (PO_LOG.d_proc) THEN
2195 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
2196 PO_LOG.proc_end(d_module);
2197 END IF;
2198
2199 RETURN;
2200
2201 EXCEPTION
2202 WHEN OTHERS THEN
2203 x_return_status := 'U';
2204
2205 PO_DOCUMENT_ACTION_PVT.error_msg_append(d_module, d_progress, SQLCODE, SQLERRM);
2206 IF (PO_LOG.d_exc) THEN
2207 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
2208 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
2209 PO_LOG.proc_end(d_module);
2210 END IF;
2211
2212 RETURN;
2213
2214 END update_auth_action_history;
2215
2216
2217 END PO_DOCUMENT_ACTION_UTIL;