[Home] [Help]
PACKAGE BODY: APPS.PO_DOCUMENT_ACTION_UTIL
Source
1 PACKAGE BODY PO_DOCUMENT_ACTION_UTIL AS
2 -- $Header: POXDAULB.pls 120.15.12020000.7 2013/04/09 10:12:47 inagdeo 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_draft_id IN NUMBER --CLM Apprvl
18 , p_action IN VARCHAR2
19 , p_employee_id IN NUMBER
20 , p_offline_code IN VARCHAR2
21 , p_approval_path_id IN NUMBER
22 , p_note IN VARCHAR2
23 , p_user_id IN NUMBER
24 , p_login_id IN NUMBER
25 , x_return_status OUT NOCOPY VARCHAR2
26 );
27
28 PROCEDURE update_auth_action_history(
29 p_document_id IN NUMBER
30 , p_revision_num IN NUMBER
31 , p_document_type IN VARCHAR2
32 , p_draft_id IN NUMBER --CLM Apprvl
33 , p_action IN VARCHAR2
34 , p_approval_path_id IN NUMBER
35 , p_note IN VARCHAR2
36 , p_user_id IN NUMBER
37 , x_return_status OUT NOCOPY VARCHAR2
38 );
39
40 PROCEDURE handle_auth_action_history(
41 p_document_id IN NUMBER
42 , p_revision_num IN NUMBER
43 , p_document_type IN VARCHAR2
44 , p_document_subtype IN VARCHAR2
45 , p_draft_id IN NUMBER --CLM Apprvl
46 , p_action IN VARCHAR2
47 , p_fwd_to_id IN NUMBER
48 , p_offline_code IN VARCHAR2
49 , p_approval_path_id IN NUMBER
50 , p_note IN VARCHAR2
51 , p_employee_id IN NUMBER
52 , p_user_id IN NUMBER
53 , p_login_id IN NUMBER
54 , p_old_status IN VARCHAR2
55 , x_return_status OUT NOCOPY VARCHAR2
56 );
57 --<Bug 14254141 :Cancel Refactoring Project>
58 -- Made the procedure "update_doc_auth_status" public
59 -- as the same code logic was need while updating the doucmnet
60 -- during Cancel [Called from po_document_cancel_pvt.approve_entity(..)].
61 -- Cannot use "change_doc_auth_state" as it updates the action history table
62 -- For Cancel, action history will be stamped with action='CANCEL'
63 -- and not 'APPROVE' and 'SUBMIT'.
64 -- Action Histoy update is handled in Cancel code itself.
65
66 PROCEDURE update_doc_notifications(
67 p_document_id IN NUMBER
68 , p_document_type IN VARCHAR2
69 , p_document_subtype IN VARCHAR2
70 , p_notify_action IN VARCHAR2
71 , p_notify_employee IN NUMBER
72 , p_doc_creation_date IN DATE
73 , p_user_id IN NUMBER
74 , p_login_id IN NUMBER
75 , x_return_status OUT NOCOPY VARCHAR2
76 );
77
78 -- Public Methods
79 FUNCTION check_doc_state(
80 p_document_id IN NUMBER
81 , p_document_type IN VARCHAR2
82 , p_line_id IN NUMBER DEFAULT NULL
83 , p_shipment_id IN NUMBER DEFAULT NULL
84 , p_allowed_states IN PO_DOCUMENT_ACTION_UTIL.DOC_STATE_REC_TYPE
85 , x_return_status OUT NOCOPY VARCHAR2
86 ) RETURN BOOLEAN
87 IS
88 BEGIN
89 return(check_doc_state(p_document_id,
90 p_document_type,
91 -1,
92 p_line_id,
93 p_shipment_id,
94 p_allowed_states,
95 x_return_status));
96
97 END;
98
99 FUNCTION check_doc_state(
100 p_document_id IN NUMBER
101 , p_document_type IN VARCHAR2
102 , p_draft_id IN NUMBER --CLM Apprvl
103 , p_line_id IN NUMBER DEFAULT NULL
104 , p_shipment_id IN NUMBER DEFAULT NULL
105 , p_allowed_states IN PO_DOCUMENT_ACTION_UTIL.DOC_STATE_REC_TYPE
106 , x_return_status OUT NOCOPY VARCHAR2
107 ) RETURN BOOLEAN
108 IS
109
110 i BINARY_INTEGER;
111
112 l_fully_res_flag financials_system_parameters.req_encumbrance_flag%TYPE;
113 l_auth_status po_releases.authorization_status%TYPE;
114 l_head_closed po_releases.closed_code%TYPE;
115
116 l_user_hold_flag po_releases.hold_flag%TYPE;
117 l_ship_closed po_line_locations.closed_code%TYPE;
118 l_line_closed po_lines.closed_code%TYPE;
119 l_closed_code VARCHAR2(26);
120 l_frozen_flag po_releases.frozen_flag%TYPE;
121
122 l_state_found BOOLEAN;
123
124 d_progress NUMBER;
125 d_module VARCHAR2(70) := 'po.plsql.PO_DOCUMENT_ACTION_UTIL.check_doc_state';
126
127 l_ret_sts VARCHAR2(1);
128 l_ret_val BOOLEAN;
129
130 CURSOR state_rel(docid NUMBER) IS
131 SELECT nvl(por.authorization_status, 'INCOMPLETE'),
132 nvl(por.closed_code, 'OPEN'),
133 nvl(por.frozen_flag, 'N'),
134 nvl(por.hold_flag, 'N')
135 FROM po_releases por
136 WHERE por.po_release_id = docid;
137
138 CURSOR state_po(docid NUMBER) IS
139 SELECT nvl(poh.authorization_status, 'INCOMPLETE'),
140 nvl(poh.closed_code, 'OPEN'),
141 nvl(poh.frozen_flag, 'N'),
142 nvl(poh.user_hold_flag, 'N')
143
144 FROM po_headers poh
145 WHERE poh.po_header_id = docid;
146
147 -- CLM Apprvl
148 CURSOR state_mod(docid NUMBER, draftId NUMBER) IS
149 SELECT nvl(phm.status, 'DRAFT'),
150 nvl(phm.closed_code, 'OPEN'),
151 nvl(phm.frozen_flag, 'N'),
152 nvl(phm.user_hold_flag, 'N')
153 FROM po_headers_merge_v phm
154 WHERE phm.po_header_id = docid
155 and phm.draft_id= draftId;
156
157 CURSOR state_req(docid NUMBER) IS
158 SELECT nvl(prh.authorization_status, 'INCOMPLETE'),
159 nvl(prh.closed_code, 'OPEN')
160 FROM po_requisition_headers prh
161 WHERE prh.requisition_header_id = docid;
162
163 CURSOR ship_closed(shipid NUMBER) IS
164 SELECT nvl(poll.closed_code, 'OPEN')
165 FROM po_line_locations poll
166 WHERE poll.line_location_id = shipid;
167
168 -- CLM Apprvl
169 CURSOR ship_closed_mod(shipid NUMBER, draftId NUMBER) IS
170 SELECT nvl(poll.closed_code, 'OPEN')
171 FROM po_line_locations_merge_v poll
172 WHERE poll.line_location_id = shipid
173 AND poll.draft_id = draftId;
174
175 CURSOR line_closed(lineid NUMBER) is
176 SELECT nvl(pol.closed_code, 'OPEN')
177 FROM po_lines pol
178 WHERE pol.po_line_id = lineid;
179
180 -- CLM Apprvl
181 CURSOR line_closed_mod(lineid NUMBER, draftId NUMBER) is
182 SELECT nvl(pol.closed_code, 'OPEN')
183 FROM po_lines_merge_v pol
184 WHERE pol.po_line_id = lineid
185 AND pol.draft_id = draftId;
186
187
188
189 BEGIN
190
191 d_progress := 0;
192 IF (PO_LOG.d_proc) THEN
193 PO_LOG.proc_begin(d_module);
194 PO_LOG.proc_begin(d_module, 'p_document_id', p_document_id);
195 PO_LOG.proc_begin(d_module, 'p_document_type', p_document_type);
196 PO_LOG.proc_begin(d_module, 'p_line_id', p_line_id);
197 PO_LOG.proc_begin(d_module, 'p_shipment_id', p_shipment_id);
198 PO_LOG.proc_begin(d_module, 'p_draft_id', p_draft_id); --CLM Apprvl
199 END IF;
200
201 l_ret_val := FALSE;
202
203 d_progress := 10;
204
205 BEGIN
206
207 IF (p_document_type = 'RELEASE')
208 THEN
209
210 d_progress := 20;
211
212 OPEN state_rel(p_document_id);
213 FETCH state_rel
214 INTO l_auth_status, l_head_closed, l_frozen_flag, l_user_hold_flag;
215 CLOSE state_rel;
216
217 d_progress := 30;
218 IF (PO_LOG.d_stmt) THEN
219 PO_LOG.stmt(d_module, d_progress, 'l_auth_status', l_auth_status);
220 PO_LOG.stmt(d_module, d_progress, 'l_head_closed', l_head_closed);
221 PO_LOG.stmt(d_module, d_progress, 'l_frozen_flag', l_frozen_flag);
222 PO_LOG.stmt(d_module, d_progress, 'l_user_hold_flag', l_user_hold_flag);
223
224 END IF;
225
226 IF (p_shipment_id IS NOT NULL)
227 THEN
228
229 d_progress := 40;
230
231 OPEN ship_closed(p_shipment_id);
232 FETCH ship_closed INTO l_ship_closed;
233 CLOSE ship_closed;
234
235 d_progress := 50;
236 IF (PO_LOG.d_stmt) THEN
237 PO_LOG.stmt(d_module, d_progress, 'l_ship_closed', l_ship_closed);
238 END IF;
239
240 END IF;
241
242 ELSIF (p_document_type in ('PO', 'PA'))
243 THEN
244
245 d_progress := 60;
246
247 -- CLM Apprvl
248 IF p_draft_id IS NOT NULL AND p_draft_id <> -1 THEN
249 OPEN state_mod(p_document_id,p_draft_id);
250 FETCH state_mod
251 INTO l_auth_status, l_head_closed, l_frozen_flag, l_user_hold_flag;
252 CLOSE state_mod;
253 ELSE
254 OPEN state_po(p_document_id);
255 FETCH state_po
256 INTO l_auth_status, l_head_closed, l_frozen_flag, l_user_hold_flag;
257 CLOSE state_po;
258 END IF;
259
260 d_progress := 70;
261
262 IF (PO_LOG.d_stmt) THEN
263 PO_LOG.stmt(d_module, d_progress, 'l_auth_status', l_auth_status);
264 PO_LOG.stmt(d_module, d_progress, 'l_head_closed', l_head_closed);
265 PO_LOG.stmt(d_module, d_progress, 'l_frozen_flag', l_frozen_flag);
266 PO_LOG.stmt(d_module, d_progress, 'l_user_hold_flag', l_user_hold_flag);
267
268 END IF;
269
270 IF (p_shipment_id IS NOT NULL) THEN
271
272 d_progress := 80;
273
274 -- CLM Apprvl
275 IF p_draft_id IS NOT NULL AND p_draft_id <> -1 THEN
276 OPEN ship_closed_mod(p_shipment_id,p_draft_id);
277 FETCH ship_closed_mod INTO l_ship_closed;
278 CLOSE ship_closed;
279 ELSE
280 OPEN ship_closed(p_shipment_id);
281 FETCH ship_closed INTO l_ship_closed;
282 CLOSE ship_closed;
283 END IF;
284
285 d_progress := 90;
286 IF (PO_LOG.d_stmt) THEN
287 PO_LOG.stmt(d_module, d_progress, 'l_ship_closed', l_ship_closed);
288 END IF;
289
290 END IF;
291
292 IF (p_line_id IS NOT NULL) THEN
293
294 d_progress := 100;
295
296 -- CLM Apprvl
297 IF p_draft_id IS NOT NULL AND p_draft_id <> -1 THEN
298 OPEN line_closed_mod(p_line_id,p_draft_id);
299 FETCH line_closed_mod INTO l_line_closed;
300 CLOSE line_closed;
301 ELSE
302 OPEN line_closed(p_line_id);
303 FETCH line_closed INTO l_line_closed;
304 CLOSE line_closed;
305 END IF;
306
307 d_progress := 110;
308 IF (PO_LOG.d_stmt) THEN
309 PO_LOG.stmt(d_module, d_progress, 'l_line_closed', l_line_closed);
310 END IF;
311
312 END IF;
313
314 ELSIF (p_document_type = 'REQUISITION') THEN
315
316 d_progress := 120;
317
318 OPEN state_req(p_document_id);
319 FETCH state_req INTO l_auth_status, l_head_closed;
320 CLOSE state_req;
321
322 d_progress := 130;
323 IF (PO_LOG.d_stmt) THEN
324 PO_LOG.stmt(d_module, d_progress, 'l_auth_status', l_auth_status);
325 PO_LOG.stmt(d_module, d_progress, 'l_head_closed', l_head_closed);
326 END IF;
327
328 ELSE
329
330 d_progress := 140;
331 l_ret_sts := 'U';
332 PO_DOCUMENT_ACTION_PVT.error_msg_append(d_module, d_progress, 'Bad Document Type');
333 IF (PO_LOG.d_exc) THEN
334 PO_LOG.exc(d_module, d_progress, 'Bad Document Type');
335 END IF;
336 RAISE PO_CORE_S.g_early_return_exc;
337
338 END IF;
339
340 l_state_found := FALSE;
341 d_progress := 150;
342
343 FOR i in p_allowed_states.auth_states.FIRST .. p_allowed_states.auth_states.LAST
344
345 LOOP
346 PO_LOG.stmt(d_module, d_progress, 'p_allowed_states.auth_states(i): '||p_allowed_states.auth_states(i)||', l_auth_status: '||l_auth_status );
347 IF p_allowed_states.auth_states(i) = l_auth_status
348 THEN
349
350 l_state_found := TRUE;
351 EXIT;
352 END IF;
353 END LOOP;
354
355 d_progress := 160;
356 IF (PO_LOG.d_stmt) THEN
357 PO_LOG.stmt(d_module, d_progress, 'l_state_found', l_state_found);
358 END IF;
359
360 IF NOT l_state_found
361 THEN
362
363 d_progress := 170;
364 IF (PO_LOG.d_stmt) THEN
365 PO_LOG.stmt(d_module, d_progress, 'Current Authorization Status Not Allowed.');
366
367 END IF;
368
369 l_ret_sts := 'S';
370 RAISE PO_CORE_S.g_early_return_exc;
371
372 END IF;
373
374
375 l_closed_code := NVL(l_ship_closed, l_line_closed);
376 l_closed_code := NVL(l_closed_code, l_head_closed);
377
378 d_progress := 180;
379 IF (PO_LOG.d_stmt) THEN
380 PO_LOG.stmt(d_module, d_progress, 'l_closed_code', l_closed_code);
381 END IF;
382
383 l_state_found := FALSE;
384
385 FOR i in p_allowed_states.closed_states.FIRST .. p_allowed_states.closed_states.LAST
386
387 LOOP
388 IF p_allowed_states.closed_states(i) = l_closed_code
389 THEN
390 l_state_found := TRUE;
391 EXIT;
392 END IF;
393 END LOOP;
394
395 d_progress := 190;
396 IF (PO_LOG.d_stmt) THEN
397 PO_LOG.stmt(d_module, d_progress, 'l_state_found', l_state_found);
398 END IF;
399
400 IF NOT l_state_found
401 THEN
402
403 d_progress := 200;
404 IF (PO_LOG.d_stmt) THEN
405 PO_LOG.stmt(d_module, d_progress, 'Current Closed Status Not Allowed.');
406
407 END IF;
408
409 l_ret_sts := 'S';
410 RAISE PO_CORE_S.g_early_return_exc;
411
412 END IF;
413
414
415 IF ((p_allowed_states.fully_reserved_flag IS NOT NULL)
416 AND ( PO_CORE_S.is_encumbrance_on(p_doc_type => p_document_type, p_org_id => NULL)))
417
418 THEN
419
420 d_progress := 210;
421 IF (PO_LOG.d_stmt) THEN
422 PO_LOG.stmt(d_module, d_progress, 'Encumbrance is on. Checking reserved state');
423
424 END IF;
425
426 PO_CORE_S.is_fully_reserved(
427 p_doc_type => p_document_type
428 , p_doc_level => PO_CORE_S.g_doc_level_HEADER
429 , p_doc_level_id => p_document_id
430 , x_fully_reserved_flag => l_fully_res_flag
431 );
432
433 d_progress := 220;
434 IF (PO_LOG.d_stmt) THEN
435 PO_LOG.stmt(d_module, d_progress, 'l_fully_res_flag', l_fully_res_flag);
436
437 END IF;
438
439 IF (p_allowed_states.fully_reserved_flag <> l_fully_res_flag)
440 THEN
441
442 d_progress := 230;
443 IF (PO_LOG.d_stmt) THEN
444 PO_LOG.stmt(d_module, d_progress, 'Current Encumbrance reservation state not allowed.');
445
446 END IF;
447
448 l_ret_sts := 'S';
449 RAISE PO_CORE_S.g_early_return_exc;
450
451 END IF; -- IF p_allowed_states.fully_reserved_flag <> l_fully_res_flag
452
453 END IF; -- IF p_allowed_states.fully_reserved_flag IS NOT NULL
454
455 IF (p_document_type IN ('PO', 'PA', 'RELEASE'))
456 THEN
457
458 d_progress := 240;
459
460 IF ((p_allowed_states.frozen_flag IS NOT NULL)
461 AND (p_allowed_states.frozen_flag <> l_frozen_flag))
462 THEN
463
464 d_progress := 250;
465 IF (PO_LOG.d_stmt) THEN
466 PO_LOG.stmt(d_module, d_progress, 'Frozen flags do not match.');
467 PO_LOG.stmt(d_module, d_progress, 'p_allowed_states.frozen_flag', p_allowed_states.frozen_flag);
468
469 PO_LOG.stmt(d_module, d_progress, 'l_frozen_flag', l_frozen_flag);
470 END IF;
471
472 l_ret_sts := 'S';
473 RAISE PO_CORE_S.g_early_return_exc;
474
475 END IF; -- p_allowed_states.frozen_flag IS NOT NULL
476
477 IF ((p_allowed_states.hold_flag IS NOT NULL)
478 AND (p_allowed_states.hold_flag <> l_user_hold_flag))
479 THEN
480
481 d_progress := 260;
482 IF (PO_LOG.d_stmt) THEN
483 PO_LOG.stmt(d_module, d_progress, 'Hold flags do not match.');
484 PO_LOG.stmt(d_module, d_progress, 'p_allowed_states.hold_flag', p_allowed_states.hold_flag);
485
486 PO_LOG.stmt(d_module, d_progress, 'l_user_hold_flag', l_user_hold_flag);
487 END IF;
488
489 l_ret_sts := 'S';
490 RAISE PO_CORE_S.g_early_return_exc;
491
492 END IF; -- p_allowed_states.hold_flag IS NOT NULL
493
494 END IF; -- IF p_document_type IN ('PO', 'PA', 'RELEASE')
495
496 d_progress := 270;
497 l_ret_sts := 'S';
498 l_ret_val := TRUE;
499
500 EXCEPTION
501 WHEN PO_CORE_S.g_early_return_exc THEN
502 NULL;
503 END;
504
505 x_return_status := l_ret_sts;
506
507 d_progress := 280;
508 IF (PO_LOG.d_proc) THEN
509 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
510 PO_LOG.proc_return(d_module, l_ret_val);
511 PO_LOG.proc_end(d_module);
512 END IF;
513
514 RETURN (l_ret_val);
515
516 EXCEPTION
517
518 WHEN OTHERS THEN
519
520 IF state_rel%ISOPEN THEN
521 CLOSE state_rel;
522 END IF;
523
524 IF state_po%ISOPEN THEN
525 CLOSE state_po;
526 END IF;
527
528 IF state_mod%ISOPEN THEN
529 CLOSE state_mod;
530 END IF;
531
532 IF state_req%ISOPEN THEN
533 CLOSE state_req;
534 END IF;
535
536 IF ship_closed%ISOPEN THEN
537 CLOSE ship_closed;
538 END IF;
539
540 IF ship_closed_mod%ISOPEN THEN
541 CLOSE ship_closed_mod;
542 END IF;
543
544 IF line_closed%ISOPEN THEN
545 CLOSE line_closed;
546 END IF;
547
548 IF line_closed_mod%ISOPEN THEN
549 CLOSE line_closed_mod;
550 END IF;
551
552 x_return_status := 'U';
553
554 PO_DOCUMENT_ACTION_PVT.error_msg_append(d_module, d_progress, SQLCODE, SQLERRM);
555 IF (PO_LOG.d_exc) THEN
556 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
557 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
558 PO_LOG.proc_return(d_module, l_ret_val);
559 PO_LOG.proc_end(d_module);
560 END IF;
561
562 return FALSE;
563
564 END check_doc_state;
565
566
567
568 PROCEDURE get_doc_preparer_id(
569 p_document_id IN NUMBER
570 , p_document_type IN VARCHAR2
571 , x_return_status OUT NOCOPY VARCHAR2
572 , x_preparer_id OUT NOCOPY NUMBER
573 )
574 IS
575 BEGIN
576 get_doc_preparer_id(
577 p_document_id
578 , p_document_type
579 , -1
580 , x_return_status
581 , x_preparer_id);
582 END;
583
584 PROCEDURE get_doc_preparer_id(
585 p_document_id IN NUMBER
586 , p_document_type IN VARCHAR2
587 , p_draft_id IN NUMBER -- CLM Apprvl
588 , x_return_status OUT NOCOPY VARCHAR2
589 , x_preparer_id OUT NOCOPY NUMBER
590 )
591 IS
592
593 d_progress NUMBER;
594 d_module VARCHAR2(70) := 'po.plsql.PO_DOCUMENT_ACTION_UTIL.get_doc_preparer_id';
595
596 l_ret_sts VARCHAR2(1);
597
598 BEGIN
599
600 d_progress := 0;
601 IF (PO_LOG.d_proc) THEN
602 PO_LOG.proc_begin(d_module);
603 PO_LOG.proc_begin(d_module, 'p_document_id', p_document_id);
604 PO_LOG.proc_begin(d_module, 'p_document_type', p_document_type);
605 PO_LOG.proc_begin(d_module, 'p_draft_id', p_draft_id); -- CLM Apprvl
606
607 END IF;
608
609 l_ret_sts := 'S';
610
611 IF (p_document_type = 'RELEASE')
612 THEN
613
614 d_progress := 20;
615
616 SELECT por.agent_id
617 INTO x_preparer_id
618 FROM po_releases_all por
619 WHERE por.po_release_id = p_document_id;
620
621 d_progress := 30;
622 IF (PO_LOG.d_stmt) THEN
623 PO_LOG.stmt(d_module, d_progress, 'x_preparer_id', x_preparer_id);
624 END IF;
625
626 ELSIF (p_document_type in ('PO', 'PA'))
627 THEN
628
629 d_progress := 40;
630
631 -- CLM Apprvl
632 IF p_draft_id IS NOT NULL AND p_draft_id <> -1 THEN
633 BEGIN
634 SELECT poh.agent_id
635 INTO x_preparer_id
636 FROM po_headers_merge_v poh
637 WHERE poh.po_header_id = p_document_id
638 AND poh.draft_id = p_draft_id;
639 EXCEPTION
640 WHEN OTHERS THEN
641 IF (PO_LOG.d_stmt) THEN
642 PO_LOG.stmt(d_module, d_progress, sqlcode||sqlerrm);
643 END IF;
644 END;
645 ELSE
646 BEGIN
647 SELECT poh.agent_id
648 INTO x_preparer_id
649 FROM po_headers_all poh
650 WHERE poh.po_header_id = p_document_id;
651 EXCEPTION
652 WHEN OTHERS THEN
653 IF (PO_LOG.d_stmt) THEN
654 PO_LOG.stmt(d_module, d_progress, sqlcode||sqlerrm);
655 END IF;
656 END;
657 END IF;
658
659 d_progress := 50;
660 IF (PO_LOG.d_stmt) THEN
661 PO_LOG.stmt(d_module, d_progress, 'x_preparer_id', x_preparer_id);
662 END IF;
663
664 ELSIF (p_document_type = 'REQUISITION') THEN
665
666 d_progress := 60;
667
668 SELECT porh.preparer_id
669 INTO x_preparer_id
670 FROM po_requisition_headers_all porh
671 WHERE porh.requisition_header_id = p_document_id;
672
673 d_progress := 70;
674 IF (PO_LOG.d_stmt) THEN
675 PO_LOG.stmt(d_module, d_progress, 'x_preparer_id', x_preparer_id);
676 END IF;
677
678 ELSE
679
680 l_ret_sts := 'U';
681
682 d_progress := 80;
683 PO_DOCUMENT_ACTION_PVT.error_msg_append(d_module, d_progress, 'Bad Document Type');
684 IF (PO_LOG.d_exc) THEN
685 PO_LOG.exc(d_module, d_progress, 'Bad Document Type');
686 END IF;
687
688 END IF;
689
690 x_return_status := l_ret_sts;
691
692 d_progress := 100;
693 IF (PO_LOG.d_proc) THEN
694 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
695 PO_LOG.proc_end(d_module, 'x_preparer_id', x_preparer_id);
696 PO_LOG.proc_end(d_module);
697 END IF;
698
699 RETURN;
700
701 EXCEPTION
702
703 WHEN OTHERS THEN
704 x_return_status := 'U';
705
706 PO_DOCUMENT_ACTION_PVT.error_msg_append(d_module, d_progress, SQLCODE, SQLERRM);
707 IF (PO_LOG.d_exc) THEN
708 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
709 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
710 PO_LOG.proc_end(d_module);
711 END IF;
712
713 RETURN;
714
715 END get_doc_preparer_id;
716
717
718
719 PROCEDURE get_employee_info(
720 p_user_id IN NUMBER
721 , x_return_status OUT NOCOPY VARCHAR2
722 , x_employee_flag OUT NOCOPY BOOLEAN
723 , x_employee_id OUT NOCOPY NUMBER
724 , x_employee_name OUT NOCOPY VARCHAR2
725 , x_location_id OUT NOCOPY NUMBER
726 , x_location_code OUT NOCOPY VARCHAR2
727 , x_is_buyer_flag OUT NOCOPY BOOLEAN
728 )
729 IS
730
731 d_progress NUMBER;
732 d_module VARCHAR2(70) := 'po.plsql.PO_DOCUMENT_ACTION_UTIL.get_employee_info';
733
734 l_temp_var VARCHAR2(1);
735
736 BEGIN
737
738 d_progress := 0;
739 IF (PO_LOG.d_proc) THEN
740 PO_LOG.proc_begin(d_module);
741 PO_LOG.proc_begin(d_module, 'p_user_id', p_user_id);
742 END IF;
743
744 BEGIN
745 d_progress := 10;
746
747 SELECT hr.person_id, hr.full_name, hr.location_id
748 INTO x_employee_id, x_employee_name, x_location_id
749 FROM FND_USER fnd, PO_WORKFORCE_CURRENT_X hr -- <BUG 6615913>
750 WHERE fnd.user_id = p_user_id
751 AND fnd.employee_id = hr.person_id;
752
753 x_employee_flag := TRUE;
754
755 d_progress := 20;
756 IF (PO_LOG.d_stmt) THEN
757 PO_LOG.stmt(d_module, d_progress, 'x_employee_id', x_employee_id);
758 PO_LOG.stmt(d_module, d_progress, 'x_employee_name', x_employee_name);
759 PO_LOG.stmt(d_module, d_progress, 'x_location_id', x_location_id);
760 END IF;
761
762 EXCEPTION
763 WHEN no_data_found THEN
764 x_employee_flag := FALSE;
765 x_location_id := NULL;
766 END;
767
768 d_progress := 30;
769 IF (PO_LOG.d_stmt) THEN
770 PO_LOG.stmt(d_module, d_progress, 'x_employee_flag', x_employee_flag);
771 END IF;
772
773 IF (x_location_id IS NOT NULL)
774 THEN
775
776 d_progress := 40;
777
778 BEGIN
779
780 SELECT hr.location_code
781 INTO x_location_code
782 FROM HR_LOCATIONS hr,
783 FINANCIALS_SYSTEM_PARAMETERS fsp,
784 ORG_ORGANIZATION_DEFINITIONS ood
785 WHERE hr.location_id = x_location_id
786 AND hr.inventory_organization_id = ood.organization_id (+)
787 AND nvl(ood.set_of_books_id, fsp.set_of_books_id) = fsp.set_of_books_id;
788
789 d_progress := 50;
790 IF (PO_LOG.d_stmt) THEN
791 PO_LOG.stmt(d_module, d_progress, 'x_location_code', x_location_code);
792 END IF;
793
794 EXCEPTION
795 WHEN no_data_found THEN
796 x_location_id := NULL;
797 END;
798
799 END IF; -- x_location_id IS NOT NULL
800
801 d_progress := 60;
802
803 IF (x_employee_flag)
804 THEN
805 BEGIN
806
807 SELECT 'X'
808 INTO l_temp_var
809 FROM po_agents poa
810 WHERE poa.agent_id = x_employee_id
811 AND SYSDATE between nvl(poa.start_date_active, SYSDATE - 1) and NVL(poa.end_date_active, SYSDATE + 1);
812
813 x_is_buyer_flag := TRUE;
814
815 d_progress := 70;
816 IF (PO_LOG.d_stmt) THEN
817 PO_LOG.stmt(d_module, d_progress, 'x_is_buyer_flag', x_is_buyer_flag);
818 END IF;
819
820 EXCEPTION
821 WHEN no_data_found THEN
822 x_is_buyer_flag := FALSE;
823 d_progress := 75;
824 IF (PO_LOG.d_stmt) THEN
825 PO_LOG.stmt(d_module, d_progress, 'x_is_buyer_flag', x_is_buyer_flag);
826 END IF;
827 END;
828
829 END IF; -- if x_employee_flag
830
831 x_return_status := 'S';
832 d_progress := 100;
833 IF (PO_LOG.d_proc) THEN
834 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
835 PO_LOG.proc_end(d_module, 'x_employee_flag', x_employee_flag);
836 PO_LOG.proc_end(d_module, 'x_employee_id', x_employee_id);
837 PO_LOG.proc_end(d_module, 'x_employee_name', x_employee_name);
838 PO_LOG.proc_end(d_module, 'x_location_id', x_location_id);
839 PO_LOG.proc_end(d_module, 'x_location_code', x_location_code);
840 PO_LOG.proc_end(d_module, 'x_is_buyer_flag', x_is_buyer_flag);
841 PO_LOG.proc_end(d_module);
842 END IF;
843
844 RETURN;
845
846 EXCEPTION
847 WHEN OTHERS THEN
848 x_return_status := 'U';
849
850 PO_DOCUMENT_ACTION_PVT.error_msg_append(d_module, d_progress, SQLCODE, SQLERRM);
851 IF (PO_LOG.d_exc) THEN
852 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
853 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
854 PO_LOG.proc_end(d_module);
855 END IF;
856
857 RETURN;
858
859 END get_employee_info;
860
861
862 PROCEDURE get_employee_id(
863 p_user_id IN NUMBER
864 , x_return_status OUT NOCOPY VARCHAR2
865 , x_employee_flag OUT NOCOPY BOOLEAN
866 , x_employee_id OUT NOCOPY NUMBER
867 )
868 IS
869
870 d_progress NUMBER;
871 d_module VARCHAR2(70) := 'po.plsql.PO_DOCUMENT_ACTION_UTIL.get_employee_id';
872
873 BEGIN
874
875 d_progress := 0;
876 IF (PO_LOG.d_proc) THEN
877 PO_LOG.proc_begin(d_module);
878 PO_LOG.proc_begin(d_module, 'p_user_id', p_user_id);
879 END IF;
880
881 BEGIN
882
883 d_progress := 10;
884
885 SELECT hr.person_id
886 INTO x_employee_id
887 FROM FND_USER fnd, PER_WORKFORCE_CURRENT_X hr --R12 CWK Enhancement
888 WHERE fnd.user_id = p_user_id
889 AND fnd.employee_id = hr.person_id;
890
891 x_employee_flag := TRUE;
892
893 d_progress := 20;
894 IF (PO_LOG.d_stmt) THEN
895 PO_LOG.stmt(d_module, d_progress, 'x_employee_id', x_employee_id);
896 END IF;
897
898 EXCEPTION
899 WHEN no_data_found THEN
900 x_employee_flag := FALSE;
901 x_employee_id := NULL;
902 END;
903
904
905 x_return_status := 'S';
906 d_progress := 100;
907 IF (PO_LOG.d_proc) THEN
908 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
909 PO_LOG.proc_end(d_module, 'x_employee_flag', x_employee_flag);
910 PO_LOG.proc_end(d_module, 'x_employee_id', x_employee_id);
911 PO_LOG.proc_end(d_module);
912 END IF;
913
914 RETURN;
915
916 EXCEPTION
917 WHEN OTHERS THEN
918 x_return_status := 'U';
919
920 PO_DOCUMENT_ACTION_PVT.error_msg_append(d_module, d_progress, SQLCODE, SQLERRM);
921 IF (PO_LOG.d_exc) THEN
922 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
923 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
924 PO_LOG.proc_end(d_module);
925 END IF;
926
927 RETURN;
928
929 END get_employee_id;
930
931
932
933 PROCEDURE change_doc_auth_state(
934 p_document_id IN NUMBER
935 , p_document_type IN VARCHAR2
936 , p_document_subtype IN VARCHAR2
937 , p_action IN VARCHAR2
938 , p_fwd_to_id IN NUMBER
939 , p_offline_code IN VARCHAR2
940 , p_approval_path_id IN NUMBER
941 , p_note IN VARCHAR2
942 , p_new_status IN VARCHAR2
943 , p_notify_action IN VARCHAR2
944 , p_notify_employee IN NUMBER
945 , x_return_status OUT NOCOPY VARCHAR2
946 )
947 IS
948 BEGIN
949 change_doc_auth_state(
950 p_document_id
951 , p_document_type
952 , p_document_subtype
953 , -1
954 , p_action
955 , p_fwd_to_id
956 , p_offline_code
957 , p_approval_path_id
958 , p_note
959 , p_new_status
960 , p_notify_action
961 , p_notify_employee
962 , x_return_status
963 );
964 END;
965
966 PROCEDURE change_doc_auth_state(
967 p_document_id IN NUMBER
968 , p_document_type IN VARCHAR2
969 , p_document_subtype IN VARCHAR2
970 , p_draft_id IN NUMBER -- CLM Apprvl
971 , p_action IN VARCHAR2
972 , p_fwd_to_id IN NUMBER
973 , p_offline_code IN VARCHAR2
974 , p_approval_path_id IN NUMBER
975 , p_note IN VARCHAR2
976 , p_new_status IN VARCHAR2
977 , p_notify_action IN VARCHAR2
978 , p_notify_employee IN NUMBER
979 , x_return_status OUT NOCOPY VARCHAR2
980 )
981 IS
982
983 l_user_id NUMBER;
984 l_login_id NUMBER;
985
986 l_ret_sts VARCHAR2(1);
987 l_err_msg VARCHAR2(200);
988
989 l_emp_flag BOOLEAN;
990 l_emp_id PER_EMPLOYEES_CURRENT_X.employee_id%TYPE;
991
992 l_old_status PO_HEADERS.authorization_status%TYPE;
993 l_creation_date PO_HEADERS.creation_date%TYPE;
994 l_revision_num PO_HEADERS.revision_num%TYPE;
995
996 d_progress NUMBER;
997 d_module VARCHAR2(70) := 'po.plsql.PO_DOCUMENT_ACTION_UTIL.change_doc_auth_state';
998
999 BEGIN
1000
1001 d_progress := 0;
1002 IF (PO_LOG.d_proc) THEN
1003 PO_LOG.proc_begin(d_module);
1004 PO_LOG.proc_begin(d_module, 'p_document_id', p_document_id);
1005 PO_LOG.proc_begin(d_module, 'p_document_type', p_document_type);
1006 PO_LOG.proc_begin(d_module, 'p_document_subtype', p_document_subtype);
1007 PO_LOG.proc_begin(d_module, 'p_draft_id', p_draft_id); -- CLM Apprvl
1008 PO_LOG.proc_begin(d_module, 'p_action', p_action);
1009 PO_LOG.proc_begin(d_module, 'p_fwd_to_id', p_fwd_to_id);
1010 PO_LOG.proc_begin(d_module, 'p_offline_code', p_offline_code);
1011 PO_LOG.proc_begin(d_module, 'p_approval_path_id', p_approval_path_id);
1012 PO_LOG.proc_begin(d_module, 'p_note', p_note);
1013 PO_LOG.proc_begin(d_module, 'p_new_status', p_new_status);
1014 PO_LOG.proc_begin(d_module, 'p_notify_action', p_notify_action);
1015 PO_LOG.proc_begin(d_module, 'p_notify_employee', p_notify_employee);
1016 END IF;
1017
1018 l_user_id := FND_GLOBAL.USER_ID;
1019 l_login_id := FND_GLOBAL.LOGIN_ID;
1020
1021 d_progress := 10;
1022 IF (PO_LOG.d_stmt) THEN
1023 PO_LOG.stmt(d_module, d_progress, 'l_user_id', l_user_id);
1024 PO_LOG.stmt(d_module, d_progress, 'l_login_id', l_login_id);
1025 END IF;
1026
1027 BEGIN
1028
1029 get_employee_id(
1030 p_user_id => l_user_id
1031 , x_return_status => l_ret_sts
1032 , x_employee_flag => l_emp_flag
1033 , x_employee_id => l_emp_id
1034 );
1035
1036 IF (l_ret_sts <> 'S')
1037 THEN
1038
1039 d_progress := 20;
1040 l_err_msg := 'get_employee_id not successful';
1041 RAISE PO_CORE_S.g_early_return_exc;
1042
1043 END IF;
1044
1045 d_progress := 30;
1046
1047 IF (NOT l_emp_flag)
1048 THEN
1049
1050 l_emp_id := NULL;
1051
1052 IF (PO_LOG.d_stmt) THEN
1053 PO_LOG.stmt(d_module, d_progress, 'user is not employee');
1054 END IF;
1055
1056 END IF;
1057
1058 d_progress := 40;
1059
1060 IF (p_document_type = 'REQUISITION')
1061 THEN
1062
1063 d_progress := 50;
1064
1065 SELECT porh.authorization_status, porh.creation_date, 0
1066 INTO l_old_status, l_creation_date, l_revision_num
1067 FROM PO_REQUISITION_HEADERS porh
1068 WHERE porh.requisition_header_id = p_document_id;
1069
1070 ELSIF (p_document_type IN ('PA', 'PO'))
1071 THEN
1072
1073 d_progress := 60;
1074
1075 -- CLM Apprvl-- for Modification the status is recorded in the po_drafts.status column.
1076 IF p_draft_id IS NOT NULL AND p_draft_id <> -1 THEN
1077 SELECT NVL(poh.status, 'DRAFT'),
1078 poh.creation_date,
1079 poh.revision_num
1080 INTO l_old_status,
1081 l_creation_date,
1082 l_revision_num
1083 FROM PO_HEADERS_MERGE_V poh
1084 WHERE poh.po_header_id = p_document_id
1085 AND poh.draft_id = p_draft_id;
1086
1087 ELSE
1088 SELECT NVL(poh.authorization_status, PO_DOCUMENT_ACTION_PVT.g_doc_status_INCOMPLETE),
1089 poh.creation_date,
1090 poh.revision_num
1091 INTO l_old_status,
1092 l_creation_date,
1093 l_revision_num
1094 FROM PO_HEADERS poh
1095 WHERE poh.po_header_id = p_document_id;
1096
1097 END IF;
1098
1099 ELSIF (p_document_type = 'RELEASE')
1100 THEN
1101
1102 d_progress := 70;
1103
1104 SELECT NVL(por.authorization_status, PO_DOCUMENT_ACTION_PVT.g_doc_status_INCOMPLETE),
1105 por.creation_date,
1106 por.revision_num
1107 INTO l_old_status,
1108 l_creation_date,
1109 l_revision_num
1110 FROM PO_RELEASES por
1111 WHERE por.po_release_id = p_document_id; -- <Bug 4118145 - Issue 2>
1112
1113 ELSE
1114
1115 d_progress := 80;
1116 l_err_msg := 'Bad Document Type';
1117 RAISE PO_CORE_S.g_early_return_exc;
1118
1119 END IF;
1120
1121 d_progress := 90;
1122 IF (PO_LOG.d_stmt) THEN
1123 PO_LOG.stmt(d_module, d_progress, 'l_old_status', l_old_status);
1124 PO_LOG.stmt(d_module, d_progress, 'l_creation_date', l_creation_date);
1125 PO_LOG.stmt(d_module, d_progress, 'l_revision_num', l_revision_num);
1126 END IF;
1127
1128 handle_auth_action_history(
1129 p_document_id => p_document_id
1130 , p_revision_num => l_revision_num
1131 , p_document_type => p_document_type
1132 , p_document_subtype => p_document_subtype
1133 , p_draft_id => p_draft_id -- CLM Apprvl
1134 , p_action => p_action
1135 , p_fwd_to_id => p_fwd_to_id
1136 , p_offline_code => p_offline_code
1137 , p_approval_path_id => p_approval_path_id
1138 , p_note => p_note
1139 , p_employee_id => l_emp_id
1140 , p_user_id => l_user_id
1141 , p_login_id => l_login_id
1142 , p_old_status => l_old_status
1143 , x_return_status => l_ret_sts
1144 );
1145
1146 IF (l_ret_sts <> 'S')
1147 THEN
1148
1149 d_progress := 100;
1150 l_err_msg := 'handle_auth_action_history not successful';
1151 RAISE PO_CORE_S.g_early_return_exc;
1152
1153 END IF;
1154
1155 d_progress := 110;
1156
1157 IF (p_new_status IS NOT NULL)
1158 THEN
1159
1160 update_doc_auth_status(
1161 p_document_id => p_document_id
1162 , p_document_type => p_document_type
1163 , p_document_subtype => p_document_subtype
1164 , p_draft_id => p_draft_id --CLM Apprvl
1165 , p_new_status => p_new_status
1166 , p_user_id => l_user_id
1167 , p_login_id => l_login_id
1168 , x_return_status => l_ret_sts
1169 );
1170
1171 IF (l_ret_sts <> 'S')
1172 THEN
1173
1174 d_progress := 120;
1175 l_err_msg := 'update_doc_auth_status not successful';
1176 RAISE PO_CORE_S.g_early_return_exc;
1177
1178 END IF;
1179
1180 END IF; -- p_new_status IS NOT NULL
1181
1182 l_ret_sts := 'S';
1183
1184 EXCEPTION
1185 WHEN PO_CORE_S.g_early_return_exc THEN
1186 l_ret_sts := 'U';
1187 PO_DOCUMENT_ACTION_PVT.error_msg_append(d_module, d_progress, l_err_msg);
1188 IF (PO_LOG.d_exc) THEN
1189 PO_LOG.exc(d_module, d_progress, l_err_msg);
1190 END IF;
1191
1192 END;
1193
1194 x_return_status := l_ret_sts;
1195 d_progress := 130;
1196 IF (PO_LOG.d_proc) THEN
1197 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
1198 PO_LOG.proc_end(d_module);
1199 END IF;
1200
1201 RETURN;
1202
1203 EXCEPTION
1204 WHEN OTHERS THEN
1205 x_return_status := 'U';
1206
1207 PO_DOCUMENT_ACTION_PVT.error_msg_append(d_module, d_progress, SQLCODE, SQLERRM);
1208 IF (PO_LOG.d_exc) THEN
1209 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
1210 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
1211 PO_LOG.proc_end(d_module);
1212 END IF;
1213
1214 RETURN;
1215
1216 END change_doc_auth_state;
1217
1218
1219
1220 PROCEDURE handle_ctl_action_history(
1221 p_document_id IN NUMBER
1222 , p_document_type IN VARCHAR2
1223 , p_document_subtype IN VARCHAR2
1224 , p_line_id IN NUMBER
1225 , p_shipment_id IN NUMBER
1226 , p_action IN VARCHAR2
1227 , p_reason IN VARCHAR2
1228 , p_user_id IN NUMBER
1229 , p_login_id IN NUMBER
1230 , x_return_status OUT NOCOPY VARCHAR2
1231 )
1232 IS
1233
1234 d_progress NUMBER;
1235 d_module VARCHAR2(70) := 'po.plsql.PO_DOCUMENT_ACTION_UTIL.handle_ctl_action_history';
1236 d_msg VARCHAR2(200);
1237
1238 l_ret_sts VARCHAR2(1);
1239
1240 l_emp_flag BOOLEAN;
1241 l_emp_id NUMBER;
1242
1243 l_rollup_msg VARCHAR2(256);
1244 l_ctl_replaced_null_entry BOOLEAN := FALSE;
1245 l_count_hist NUMBER;
1246
1247 l_revision_num PO_HEADERS_ALL.revision_num%TYPE;
1248
1249 BEGIN
1250
1251 d_progress := 0;
1252 IF (PO_LOG.d_proc) THEN
1253 PO_LOG.proc_begin(d_module);
1254 PO_LOG.proc_begin(d_module, 'p_document_id', p_document_id);
1255 PO_LOG.proc_begin(d_module, 'p_document_type', p_document_type);
1256 PO_LOG.proc_begin(d_module, 'p_document_subtype', p_document_subtype);
1257 PO_LOG.proc_begin(d_module, 'p_action', p_action);
1258 PO_LOG.proc_begin(d_module, 'p_reason', p_reason);
1259 PO_LOG.proc_begin(d_module, 'p_line_id', p_line_id);
1260 PO_LOG.proc_begin(d_module, 'p_shipment_id', p_shipment_id);
1261 PO_LOG.proc_begin(d_module, 'p_user_id', p_user_id);
1262 PO_LOG.proc_begin(d_module, 'p_login_id', p_login_id);
1263 END IF;
1264
1265 d_progress := 10;
1266
1267 BEGIN
1268
1269 get_employee_id(
1270 p_user_id => p_user_id
1271 , x_return_status => l_ret_sts
1272 , x_employee_flag => l_emp_flag
1273 , x_employee_id => l_emp_id
1274 );
1275
1276 IF (l_ret_sts <> 'S')
1277 THEN
1278
1279 d_progress := 20;
1280 d_msg := 'get_employee_id not successful';
1281 RAISE PO_CORE_S.g_early_return_exc;
1282
1283 END IF;
1284
1285 d_progress := 30;
1286
1287 IF (l_emp_flag IS NULL) THEN
1288 l_emp_id := NULL;
1289 END IF;
1290
1291 IF (PO_LOG.d_stmt) THEN
1292 PO_LOG.stmt(d_module, d_progress, 'l_emp_flag', l_emp_flag);
1293 PO_LOG.stmt(d_module, d_progress, 'l_emp_id', l_emp_id);
1294 END IF;
1295
1296 d_progress := 40;
1297
1298 l_rollup_msg := substr(FND_MESSAGE.GET_STRING('PO', 'PO_CLOSE_ROLLUP'), 1, 256);
1299
1300 IF (p_action IN ('CANCEL', 'FINALLY CLOSE'))
1301 THEN
1302
1303 d_progress := 50;
1304
1305 UPDATE po_action_history poah
1306 SET poah.action_code = p_action
1307 , poah.action_date = SYSDATE
1308 , poah.offline_code = NULL
1309 , poah.employee_id = l_emp_id
1310 , poah.note = DECODE(p_shipment_id, NULL,
1311 DECODE(p_line_id, NULL, p_reason, l_rollup_msg),
1312 l_rollup_msg)
1313 , poah.last_updated_by = p_user_id
1314 , poah.last_update_date = SYSDATE
1315 WHERE poah.object_id = p_document_id
1316 AND poah.object_type_code = p_document_type
1317 AND poah.object_sub_type_code = p_document_subtype
1318 AND poah.action_code IS NULL;
1319
1320 d_progress := 60;
1321
1322 IF (NOT SQL%NOTFOUND)
1323 THEN
1324
1325 l_ctl_replaced_null_entry := TRUE;
1326
1327 END IF;
1328
1329 END IF; -- if (p_action IN ('CANCEL', 'FINALLY CLOSE'))
1330
1331 d_progress := 100;
1332 IF (PO_LOG.d_stmt) THEN
1333 PO_LOG.stmt(d_module, d_progress, 'l_ctl_repaced_null_entry', l_ctl_replaced_null_entry);
1334 END IF;
1335
1336 IF (NOT l_ctl_replaced_null_entry)
1337 THEN
1338
1339 d_progress := 110;
1340
1341 UPDATE po_action_history poah
1342 SET poah.sequence_num = poah.sequence_num + 1
1343 WHERE poah.object_id = p_document_id
1344 AND poah.object_type_code = p_document_type
1345 AND poah.object_sub_type_code = p_document_subtype
1346 AND poah.action_code IS NULL;
1347
1348 d_progress := 120;
1349
1350 -- Bug 3136474: Was in Pro*C, but not in PL/SQL API
1351 -- Ported it over, as it will fix a PDOI issue.
1352
1353 SELECT count(1)
1354 INTO l_count_hist
1355 FROM po_action_history poah
1356 WHERE poah.object_id = p_document_id
1357 AND poah.object_type_code = p_document_type
1358 AND poah.object_sub_type_code = p_document_subtype
1359 AND poah.action_code IS NOT NULL;
1360
1361 d_progress := 130;
1362
1363 IF (PO_LOG.d_stmt) THEN
1364 PO_LOG.stmt(d_module, d_progress, 'l_count_hist', l_count_hist);
1365 END IF;
1366
1367 IF (l_count_hist > 0)
1368 THEN
1369
1370 d_progress := 140;
1371
1372 -- Used Pro*C insert statement, as it was more accurate
1373 -- This SQL comes from: pocah.lpc
1374 -- The one in old POXPOACB.pls was incorrect.
1375
1376 INSERT INTO po_action_history(
1377 object_id
1378 , object_type_code
1379 , object_sub_type_code
1380 , sequence_num
1381 , last_update_date
1382 , last_updated_by
1383 , creation_date
1384 , created_by
1385 , action_code
1386 , action_date
1387 , employee_id
1388 , note
1389 , object_revision_num
1390 , last_update_login
1391 , request_id
1392 , program_application_id
1393 , program_id
1394 , program_update_date
1395 , approval_path_id
1396 , offline_code
1397 )
1398 SELECT
1399 poah.object_id
1400 , poah.object_type_code
1401 , poah.object_sub_type_code
1402 , max(poah.sequence_num) + 1
1403 , SYSDATE
1404 , p_user_id
1405 , SYSDATE
1406 , p_user_id
1407 , p_action
1408 , SYSDATE
1409 , l_emp_id
1410 , DECODE(p_shipment_id,
1411 NULL, DECODE(p_line_id, NULL, p_reason, l_rollup_msg),
1412 l_rollup_msg)
1413 , max(poah.object_revision_num)
1414 , p_login_id
1415 , 0
1416 , 0
1417 , 0
1418 , ''
1419 , 0
1420 , ''
1421 FROM po_action_history poah
1422 WHERE poah.object_id = p_document_id
1423 AND poah.object_type_code = p_document_type
1424 AND poah.object_sub_type_code = p_document_subtype
1425 AND poah.action_code IS NOT NULL
1426 GROUP BY poah.object_id
1427 , poah.object_type_code
1428 , poah.object_sub_type_code
1429 ;
1430
1431 ELSE
1432
1433 d_progress := 150;
1434
1435 -- only PDOI should come here
1436 -- so we're safe using headers_all
1437
1438 SELECT max(poh.revision_num)
1439 INTO l_revision_num
1440 FROM po_headers_all poh
1441 WHERE poh.po_header_id = p_document_id;
1442
1443 d_progress := 160;
1444 IF (PO_LOG.d_stmt) THEN
1445 PO_LOG.stmt(d_module, d_progress, 'l_revision_num', l_revision_num);
1446 END IF;
1447
1448 INSERT INTO po_action_history(
1449 object_id
1450 , object_type_code
1451 , object_sub_type_code
1452 , sequence_num
1453 , last_update_date
1454 , last_updated_by
1455 , creation_date
1456 , created_by
1457 , action_code
1458 , action_date
1459 , employee_id
1460 , note
1461 , object_revision_num
1462 , last_update_login
1463 , request_id
1464 , program_application_id
1465 , program_id
1466 , program_update_date
1467 , approval_path_id
1468 , offline_code
1469 ) VALUES (
1470 p_document_id
1471 , p_document_type
1472 , p_document_subtype
1473 , 1 -- Bug 13579433, sequence_num starts at 1
1474 , SYSDATE
1475 , p_user_id
1476 , SYSDATE
1477 , p_user_id
1478 , p_action
1479 , SYSDATE
1480 , l_emp_id
1481 , DECODE(p_shipment_id,
1482 NULL, DECODE(p_line_id, NULL, p_reason, l_rollup_msg),
1483 l_rollup_msg)
1484 , l_revision_num
1485 , p_login_id
1486 , 0
1487 , 0
1488 , 0
1489 , ''
1490 , 0
1491 , ''
1492 );
1493
1494 END IF;
1495
1496 END IF; -- if (not l_ctl_replaced_null_entry)
1497
1498 l_ret_sts := 'S';
1499
1500 EXCEPTION
1501 WHEN PO_CORE_S.g_early_return_exc THEN
1502 l_ret_sts := 'U';
1503 PO_DOCUMENT_ACTION_PVT.error_msg_append(d_module, d_progress, d_msg);
1504 IF (PO_LOG.d_exc) THEN
1505 PO_LOG.exc(d_module, d_progress, d_msg);
1506 END IF;
1507 END;
1508
1509 x_return_status := l_ret_sts;
1510
1511 IF (PO_LOG.d_proc) THEN
1512 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
1513 PO_LOG.proc_end(d_module);
1514 END IF;
1515
1516 RETURN;
1517
1518 EXCEPTION
1519 WHEN OTHERS THEN
1520 x_return_status := 'U';
1521
1522 PO_DOCUMENT_ACTION_PVT.error_msg_append(d_module, d_progress, SQLCODE, SQLERRM);
1523 IF (PO_LOG.d_exc) THEN
1524 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
1525 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
1526 PO_LOG.proc_end(d_module);
1527 END IF;
1528
1529 RETURN;
1530
1531 END handle_ctl_action_history;
1532
1533 -- Private Methods
1534
1535 PROCEDURE handle_auth_action_history(
1536 p_document_id IN NUMBER
1537 , p_revision_num IN NUMBER
1538 , p_document_type IN VARCHAR2
1539 , p_document_subtype IN VARCHAR2
1540 , p_draft_id IN NUMBER --CLM Apprvl
1541 , p_action IN VARCHAR2
1542 , p_fwd_to_id IN NUMBER
1543 , p_offline_code IN VARCHAR2
1544 , p_approval_path_id IN NUMBER
1545 , p_note IN VARCHAR2
1546 , p_employee_id IN NUMBER
1547 , p_user_id IN NUMBER
1548 , p_login_id IN NUMBER
1549 , p_old_status IN VARCHAR2
1550 , x_return_status OUT NOCOPY VARCHAR2
1551 )
1552 IS
1553
1554 l_ret_sts VARCHAR2(1);
1555
1556 d_progress NUMBER;
1557 d_module VARCHAR2(70) := 'po.plsql.PO_DOCUMENT_ACTION_UTIL.handle_auth_action_history';
1558
1559 BEGIN
1560
1561 d_progress := 0;
1562 IF (PO_LOG.d_proc) THEN
1563 PO_LOG.proc_begin(d_module);
1564 PO_LOG.proc_begin(d_module, 'p_document_id', p_document_id);
1565 PO_LOG.proc_begin(d_module, 'p_document_type', p_document_type);
1566 PO_LOG.proc_begin(d_module, 'p_document_subtype', p_document_subtype);
1567 PO_LOG.proc_begin(d_module, 'p_draft_id ', p_draft_id ); --CLM Apprvl
1568 PO_LOG.proc_begin(d_module, 'p_action', p_action);
1569 PO_LOG.proc_begin(d_module, 'p_fwd_to_id', p_fwd_to_id);
1570 PO_LOG.proc_begin(d_module, 'p_offline_code', p_offline_code);
1571 PO_LOG.proc_begin(d_module, 'p_approval_path_id', p_approval_path_id);
1572 PO_LOG.proc_begin(d_module, 'p_note', p_note);
1573 PO_LOG.proc_begin(d_module, 'p_employee_id', p_employee_id);
1574 PO_LOG.proc_begin(d_module, 'p_user_id', p_user_id);
1575 PO_LOG.proc_begin(d_module, 'p_login_id', p_login_id);
1576 PO_LOG.proc_begin(d_module, 'p_old_status', p_old_status);
1577 END IF;
1578
1579 d_progress := 10;
1580
1581 BEGIN
1582
1583 IF (p_old_status IN (PO_DOCUMENT_ACTION_PVT.g_doc_status_RETURNED,
1584 PO_DOCUMENT_ACTION_PVT.g_doc_status_REJECTED,
1585 PO_DOCUMENT_ACTION_PVT.g_doc_status_INCOMPLETE,
1586 PO_DOCUMENT_ACTION_PVT.g_doc_status_DRAFT, -- CLM apprvl
1587 PO_DOCUMENT_ACTION_PVT.g_doc_status_REAPPROVAL))
1588 THEN
1589
1590 insert_auth_action_history(
1591 p_document_id => p_document_id
1592 , p_revision_num => p_revision_num
1593 , p_document_type => p_document_type
1594 , p_document_subtype => p_document_subtype
1595 , p_draft_id => p_draft_id --CLM Apprvl
1596 , p_action => 'SUBMIT'
1597 , p_employee_id => p_employee_id
1598 , p_offline_code => NULL
1599 , p_approval_path_id => p_approval_path_id
1600 , p_note => p_note
1601 , p_user_id => p_user_id
1602 , p_login_id => p_login_id
1603 , x_return_status => l_ret_sts
1604 );
1605
1606 d_progress := 20;
1607 IF (l_ret_sts <> 'S') THEN
1608 RAISE PO_CORE_S.g_early_return_exc;
1609 END IF;
1610
1611 IF (p_action <> 'SUBMIT')
1612 THEN
1613
1614 insert_auth_action_history(
1615 p_document_id => p_document_id
1616 , p_revision_num => p_revision_num
1617 , p_document_type => p_document_type
1618 , p_document_subtype => p_document_subtype
1619 , p_draft_id => p_draft_id --CLM Apprvl
1620 , p_action => p_action
1621 , p_employee_id => p_employee_id
1622 , p_offline_code => NULL
1623 , p_approval_path_id => p_approval_path_id
1624 , p_note => p_note
1625 , p_user_id => p_user_id
1626 , p_login_id => p_login_id
1627 , x_return_status => l_ret_sts
1628 );
1629
1630 d_progress := 30;
1631 IF (l_ret_sts <> 'S') THEN
1632 RAISE PO_CORE_S.g_early_return_exc;
1633 END IF;
1634
1635 END IF; -- p_action <> 'SUBMIT'
1636
1637 ELSIF (p_old_status IN (PO_DOCUMENT_ACTION_PVT.g_doc_status_INPROCESS,
1638 PO_DOCUMENT_ACTION_PVT.g_doc_status_PREAPPROVED,
1639 PO_DOCUMENT_ACTION_PVT.g_doc_status_COMPLETED)) --Bug 13444730
1640 THEN
1641
1642 update_auth_action_history(
1643 p_document_id => p_document_id
1644 , p_revision_num => p_revision_num
1645 , p_document_type => p_document_type
1646 , p_draft_id => p_draft_id --CLM Apprvl
1647 , p_action => p_action
1648 , p_approval_path_id => p_approval_path_id
1649 , p_note => p_note
1650 , p_user_id => p_user_id
1651 , x_return_status => l_ret_sts
1652 );
1653
1654 d_progress := 40;
1655 IF (l_ret_sts <> 'S') THEN
1656 RAISE PO_CORE_S.g_early_return_exc;
1657 END IF;
1658 -- CLM apprvl, for mods the status becomes 'completed' after approval
1659 ELSIF (p_old_status IN (PO_DOCUMENT_ACTION_PVT.g_doc_status_APPROVED, PO_DOCUMENT_ACTION_PVT.g_doc_status_APP_AND_PROCESSED))
1660 THEN
1661
1662 insert_auth_action_history(
1663 p_document_id => p_document_id
1664 , p_revision_num => p_revision_num
1665 , p_document_type => p_document_type
1666 , p_document_subtype => p_document_subtype
1667 , p_draft_id => p_draft_id --CLM Apprvl
1668 , p_action => p_action
1669 , p_employee_id => p_employee_id
1670 , p_offline_code => NULL
1671 , p_approval_path_id => p_approval_path_id
1672 , p_note => p_note
1673 , p_user_id => p_user_id
1674 , p_login_id => p_login_id
1675 , x_return_status => l_ret_sts
1676 );
1677
1678 d_progress := 50;
1679 IF (l_ret_sts <> 'S') THEN
1680 RAISE PO_CORE_S.g_early_return_exc;
1681 END IF;
1682
1683 END IF; -- p_old_status IN ...
1684
1685 IF (p_fwd_to_id IS NOT NULL)
1686 THEN
1687
1688 -- bug4363736
1689 -- when inserting NULL action row, we should populate revision_num
1690 -- with the latest revision
1691 insert_auth_action_history(
1692 p_document_id => p_document_id
1693 , p_revision_num => p_revision_num
1694 , p_document_type => p_document_type
1695 , p_document_subtype => p_document_subtype
1696 , p_draft_id => p_draft_id --CLM Apprvl
1697 , p_action => NULL
1698 , p_employee_id => p_fwd_to_id
1699 , p_offline_code => p_offline_code
1700 , p_approval_path_id => NULL
1701 , p_note => NULL
1702 , p_user_id => p_user_id
1703 , p_login_id => p_login_id
1704 , x_return_status => l_ret_sts
1705 );
1706
1707 d_progress := 60;
1708 IF (l_ret_sts <> 'S') THEN
1709 RAISE PO_CORE_S.g_early_return_exc;
1710 END IF;
1711
1712 IF ((p_old_status = PO_DOCUMENT_ACTION_PVT.g_doc_status_PREAPPROVED)
1713 AND (p_action = PO_DOCUMENT_ACTION_PVT.g_doc_action_RESERVE))
1714 THEN
1715
1716 update_auth_action_history(
1717 p_document_id => p_document_id
1718 , p_revision_num => p_revision_num
1719 , p_document_type => p_document_type
1720 , p_draft_id => p_draft_id --CLM Apprvl
1721 , p_action => PO_DOCUMENT_ACTION_PVT.g_doc_action_APPROVE
1722 , p_approval_path_id => p_approval_path_id
1723 , p_note => p_note
1724 , p_user_id => p_user_id
1725 , x_return_status => l_ret_sts
1726 );
1727
1728 d_progress := 70;
1729 IF (l_ret_sts <> 'S') THEN
1730 RAISE PO_CORE_S.g_early_return_exc;
1731 END IF;
1732
1733 END IF; -- p_old_status = ... and p_action =
1734
1735 ELSIF ((p_old_status = PO_DOCUMENT_ACTION_PVT.g_doc_status_INPROCESS)
1736 AND (p_action in (PO_DOCUMENT_ACTION_PVT.g_doc_action_RESERVE,
1737 PO_DOCUMENT_ACTION_PVT.g_doc_action_UNRESERVE)))
1738 THEN
1739
1740 -- bug4363736
1741 -- when inserting NULL action row, we should populate revision_num
1742 -- with the latest revision
1743 insert_auth_action_history(
1744 p_document_id => p_document_id
1745 , p_revision_num => p_revision_num
1746 , p_document_type => p_document_type
1747 , p_document_subtype => p_document_subtype
1748 , p_draft_id => p_draft_id --CLM Apprvl
1749 , p_action => NULL
1750 , p_employee_id => p_employee_id
1751 , p_offline_code => NULL
1752 , p_approval_path_id => NULL
1753 , p_note => NULL
1754 , p_user_id => p_user_id
1755 , p_login_id => p_login_id
1756 , x_return_status => l_ret_sts
1757 );
1758
1759 d_progress := 80;
1760 IF (l_ret_sts <> 'S') THEN
1761 RAISE PO_CORE_S.g_early_return_exc;
1762 END IF;
1763
1764 END IF; -- p_fwd_to_id IS NOT NULL
1765
1766 l_ret_sts := 'S';
1767
1768 EXCEPTION
1769 WHEN PO_CORE_S.g_early_return_exc THEN
1770 l_ret_sts := 'U';
1771 PO_DOCUMENT_ACTION_PVT.error_msg_append(d_module, d_progress, 'Insert or update action history not successful');
1772 IF (PO_LOG.d_exc) THEN
1773 PO_LOG.exc(d_module, d_progress, 'Insert or update action history not successful');
1774 END IF;
1775
1776 END;
1777
1778 x_return_status := l_ret_sts;
1779 d_progress := 100;
1780 IF (PO_LOG.d_proc) THEN
1781 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
1782 PO_LOG.proc_end(d_module);
1783 END IF;
1784
1785 RETURN;
1786
1787 EXCEPTION
1788 WHEN OTHERS THEN
1789 x_return_status := 'U';
1790
1791 PO_DOCUMENT_ACTION_PVT.error_msg_append(d_module, d_progress, SQLCODE, SQLERRM);
1792 IF (PO_LOG.d_exc) THEN
1793 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
1794 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
1795 PO_LOG.proc_end(d_module);
1796 END IF;
1797
1798 RETURN;
1799
1800 END handle_auth_action_history;
1801
1802
1803
1804 PROCEDURE update_doc_auth_status(
1805 p_document_id IN NUMBER
1806 , p_document_type IN VARCHAR2
1807 , p_document_subtype IN VARCHAR2
1808 , p_draft_id IN NUMBER --CLM Apprvl
1809 , p_new_status IN VARCHAR2
1810 , p_user_id IN NUMBER
1811 , p_login_id IN NUMBER
1812 , x_return_status OUT NOCOPY VARCHAR2
1813 )
1814 IS
1815
1816 l_conterms_exist PO_HEADERS.conterms_exist_flag%TYPE;
1817 l_pending_signature PO_HEADERS.pending_signature_flag%TYPE;
1818 l_conformed_id PO_REQUISITION_HEADERS_ALL.conformed_header_id%TYPE;
1819
1820 l_ret_sts VARCHAR2(1);
1821 l_err_msg VARCHAR2(200);
1822 l_msg_count NUMBER;
1823 l_msg_data VARCHAR2(2000);
1824
1825 d_progress NUMBER;
1826 d_module VARCHAR2(70) := 'po.plsql.PO_DOCUMENT_ACTION_UTIL.update_doc_auth_status';
1827
1828 l_is_clm_document VARCHAR2(1);
1829
1830 BEGIN
1831
1832 d_progress := 0;
1833 IF (PO_LOG.d_proc) THEN
1834 PO_LOG.proc_begin(d_module);
1835 PO_LOG.proc_begin(d_module, 'p_document_id', p_document_id);
1836 PO_LOG.proc_begin(d_module, 'p_document_type', p_document_type);
1837 PO_LOG.proc_begin(d_module, 'p_document_subtype', p_document_subtype);
1838 PO_LOG.proc_begin(d_module, 'p_draft_id', p_draft_id); -- CLM Apprvl
1839 PO_LOG.proc_begin(d_module, 'p_new_status', p_new_status);
1840 PO_LOG.proc_begin(d_module, 'p_user_id', p_user_id);
1841 END IF;
1842
1843 BEGIN
1844
1845 IF (p_document_type = 'REQUISITION')
1846 THEN
1847
1848 d_progress := 10;
1849 --Bug 5151097 : Update Approved Date when Approved
1850 UPDATE PO_REQUISITION_HEADERS porh
1851 SET porh.authorization_status = p_new_status
1852 , porh.approved_date = DECODE (p_new_status,
1853 PO_DOCUMENT_ACTION_PVT.g_doc_status_APPROVED,
1854 SYSDATE,null)
1855 , porh.last_update_date = SYSDATE
1856 , porh.last_updated_by = p_user_id
1857 , porh.last_update_login = p_login_id
1858 WHERE porh.requisition_header_id = p_document_id;
1859
1860 -- <REQINPOOL Start>
1861 d_progress := 15;
1862
1863 BEGIN
1864 SELECT conformed_header_id INTO l_conformed_id
1865 FROM po_requisition_headers_all
1866 WHERE requisition_header_id = p_document_id;
1867 EXCEPTION
1868 WHEN No_Data_Found THEN
1869 l_conformed_id := NULL;
1870 END;
1871
1872 IF l_conformed_id IS NULL THEN
1873 PO_REQ_LINES_SV.update_reqs_in_pool_flag(
1874 x_req_line_id => NULL
1875 , x_req_header_id => p_document_id
1876 , x_return_status => l_ret_sts
1877 );
1878 ELSE
1879 PO_REQ_LINES_SV.update_reqs_in_pool_flag(
1880 x_req_line_id => NULL
1881 , x_req_header_id => l_conformed_id
1882 , x_return_status => l_ret_sts
1883 );
1884 END IF;
1885
1886 IF (l_ret_sts <> FND_API.G_RET_STS_SUCCESS)
1887 THEN
1888 d_progress := 17;
1889 l_err_msg := 'update_reqs_in_pool_flag not successful';
1890 RAISE PO_CORE_S.g_early_return_exc;
1891 END IF;
1892 -- <REQINPOOL End>
1893
1894 ELSIF (p_document_type IN ('PO', 'PA'))
1895 THEN
1896
1897 d_progress := 20;
1898
1899 -- CLM Apprvl
1900 IF p_draft_id IS NOT NULL AND p_draft_id <> -1 THEN
1901
1902 /* UPDATE PO_HEADERS_DRAFT_ALL poh
1903 SET poh.authorization_status = p_new_status,
1904 poh.approved_flag = DECODE(p_new_status,
1905 PO_DOCUMENT_ACTION_PVT.g_doc_status_APPROVED, 'Y',
1906 PO_DOCUMENT_ACTION_PVT.g_doc_status_REAPPROVAL, 'R',
1907 PO_DOCUMENT_ACTION_PVT.g_doc_status_REJECTED, 'F',
1908 PO_DOCUMENT_ACTION_PVT.g_doc_status_RETURNED, 'F',
1909 PO_DOCUMENT_ACTION_PVT.g_doc_status_INCOMPLETE, 'N',
1910 PO_DOCUMENT_ACTION_PVT.g_doc_status_INPROCESS, 'N',
1911 PO_DOCUMENT_ACTION_PVT.g_doc_status_PREAPPROVED, 'N'),
1912 poh.approved_date = DECODE(p_new_status,
1913 PO_DOCUMENT_ACTION_PVT.g_doc_status_APPROVED, SYSDATE,
1914 poh.approved_date),
1915 poh.last_update_date = SYSDATE,
1916 poh.last_updated_by = p_user_id,
1917 poh.last_update_login = p_login_id,
1918 poh.pending_signature_flag = DECODE(p_new_status,
1919 PO_DOCUMENT_ACTION_PVT.g_doc_status_APPROVED,
1920 DECODE(poh.acceptance_required_flag, 'S', 'Y', 'N'),
1921 poh.pending_signature_flag)
1922 WHERE poh.po_header_id = p_document_id
1923 AND poh.draft_id = p_draft_id;*/
1924 UPDATE PO_DRAFTS
1925 SET status = decode(p_new_status,'APPROVED','COMPLETED',p_new_status)
1926 WHERE draft_id = p_draft_id;
1927 ELSE
1928
1929 -- PO AME Project : need to retain pending_signature_flag as E in case of
1930 -- E-Signers do exists on the document
1931
1932 UPDATE PO_HEADERS poh
1933 SET poh.authorization_status = p_new_status,
1934 poh.approved_flag = DECODE(p_new_status,
1935 PO_DOCUMENT_ACTION_PVT.g_doc_status_APPROVED, 'Y',
1936 PO_DOCUMENT_ACTION_PVT.g_doc_status_REAPPROVAL, 'R',
1937 PO_DOCUMENT_ACTION_PVT.g_doc_status_REJECTED, 'F',
1938 PO_DOCUMENT_ACTION_PVT.g_doc_status_RETURNED, 'F',
1939 PO_DOCUMENT_ACTION_PVT.g_doc_status_INCOMPLETE, 'N',
1940 PO_DOCUMENT_ACTION_PVT.g_doc_status_INPROCESS, 'N',
1941 PO_DOCUMENT_ACTION_PVT.g_doc_status_PREAPPROVED, 'N'),
1942 poh.approved_date = DECODE(p_new_status,
1943 PO_DOCUMENT_ACTION_PVT.g_doc_status_APPROVED, SYSDATE, poh.approved_date),
1944 poh.clm_effective_date = DECODE(poh.clm_effective_date,
1945 NULL,DECODE(p_new_status,
1946 PO_DOCUMENT_ACTION_PVT.g_doc_status_APPROVED, SYSDATE,
1947 poh.approved_date),
1948 poh.clm_effective_date),
1949 poh.last_update_date = SYSDATE,
1950 poh.last_updated_by = p_user_id,
1951 poh.last_update_login = p_login_id,
1952 poh.pending_signature_flag = DECODE(p_new_status,
1953 PO_DOCUMENT_ACTION_PVT.g_doc_status_APPROVED,
1954 DECODE(poh.acceptance_required_flag, 'S', 'Y',
1955 DECODE( poh.pending_signature_flag, 'E', 'E','N')),
1956 poh.pending_signature_flag)
1957 WHERE poh.po_header_id = p_document_id;
1958 END IF;
1959
1960 -- CLM Apprvl end
1961
1962 IF (p_new_status = PO_DOCUMENT_ACTION_PVT.g_doc_status_APPROVED)
1963 THEN
1964
1965 d_progress := 30;
1966
1967 SELECT NVL(poh.pending_signature_flag, 'N'), NVL(poh.conterms_exist_flag, 'N')
1968 INTO l_pending_signature, l_conterms_exist
1969 FROM PO_HEADERS poh
1970 WHERE po_header_id = p_document_id;
1971
1972
1973 d_progress := 40;
1974
1975 PO_CONTERMS_WF_PVT.UPDATE_CONTERMS_DATES(
1976 p_po_header_id => p_document_id
1977 , p_po_doc_type => p_document_type
1978 , p_po_doc_subtype => p_document_subtype
1979 , p_conterms_exist_flag => l_conterms_exist
1980 , x_return_status => l_ret_sts
1981 , x_msg_count => l_msg_count
1982 , x_msg_data => l_msg_data
1983 );
1984
1985 IF ((l_ret_sts = FND_API.G_RET_STS_UNEXP_ERROR)
1986 OR (l_ret_sts = FND_API.G_RET_STS_ERROR))
1987 THEN
1988 d_progress := 50;
1989 l_err_msg := 'update_conterms_dates not successful';
1990 RAISE PO_CORE_S.g_early_return_exc;
1991 END IF;
1992
1993 -- PO AME Project : Check for 'E', pending_signature_flag in case of
1994 -- E-Signers and set doc to PreApproved status
1995 IF (l_pending_signature in ('Y', 'E'))
1996 THEN
1997
1998 d_progress := 60;
1999
2000 PO_DOCUMENT_ARCHIVE_GRP.ARCHIVE_PO(
2001 p_api_version => 1.0
2002 , p_document_id => p_document_id
2003 , p_document_type => p_document_type
2004 , p_document_subtype => p_document_subtype
2005 , x_return_status => l_ret_sts
2006 , x_msg_count => l_msg_count
2007 , x_msg_data => l_msg_data
2008 );
2009
2010 IF ((l_ret_sts = FND_API.G_RET_STS_UNEXP_ERROR)
2011 OR (l_ret_sts = FND_API.G_RET_STS_ERROR))
2012 THEN
2013 d_progress := 70;
2014 l_err_msg := 'archive_po not successful';
2015 RAISE PO_CORE_S.g_early_return_exc;
2016 END IF;
2017
2018 d_progress := 80;
2019
2020 UPDATE PO_HEADERS poh
2021 SET poh.authorization_status = PO_DOCUMENT_ACTION_PVT.g_doc_status_PREAPPROVED
2022 , poh.approved_flag = 'N'
2023 , poh.last_update_date = SYSDATE
2024 , poh.last_updated_by = p_user_id
2025 , poh.last_update_login = p_login_id
2026 WHERE poh.po_header_id = p_document_id;
2027
2028
2029 END IF; -- l_pending_signature = 'Y'
2030
2031 BEGIN
2032 SELECT clm_flag
2033 INTO l_is_clm_document
2034 FROM po_doc_style_headers psh,
2035 po_headers poh
2036 WHERE psh.style_id = poh.style_id
2037 AND poh.po_header_id = p_document_id;
2038 EXCEPTION
2039 WHEN no_data_found THEN
2040 l_is_clm_document := 'N';
2041 END;
2042
2043 IF (l_is_clm_document = 'Y') THEN
2044 --UCA Project - CLM R4 Changes Begin
2045 -- Stamping conforming details for PO_LINES_UCAs
2046 UPDATE po_line_ucas
2047 SET undef_approved_date =
2048 Decode (p_draft_id,
2049 undef_draft_id, nvl(undef_approved_date, SYSDATE),
2050 undef_approved_date),
2051 def_approved_date =
2052 Decode (p_draft_id,
2053 def_draft_id, SYSDATE,
2054 def_approved_date),
2055 undef_clm_document_number =
2056 Decode (p_draft_id,
2057 def_draft_id, undef_clm_document_number,
2058 -1, (SELECT clm_document_number
2059 FROM po_headers_all poh
2060 WHERE poh.po_header_id = p_document_id),
2061 (SELECT modification_number
2062 FROM po_drafts
2063 WHERE document_id = p_document_id
2064 AND draft_id = p_draft_id)),
2065 last_update_date = SYSDATE,
2066 last_update_login = p_login_id,
2067 last_updated_by = p_user_id
2068 WHERE po_header_id = p_document_id
2069 AND (def_draft_id = p_draft_id OR undef_draft_id = p_draft_id);
2070 --UCA Project - CLM R4 Changes End
2071 END IF;
2072
2073 d_progress := 90;
2074
2075 UPDATE PO_LINE_LOCATIONS_ALL poll
2076 SET poll.approved_flag = 'Y'
2077 , poll.approved_date = SYSDATE
2078 , poll.last_update_date = SYSDATE
2079 , poll.last_updated_by = p_user_id
2080 , poll.last_update_login = p_login_id
2081 WHERE poll.po_header_id = p_document_id
2082 AND poll.po_release_id IS NULL
2083 AND NVL(poll.approved_flag, 'N') <> 'Y'
2084 AND EXISTS ( SELECT 'PO Does not require signature'
2085 FROM PO_HEADERS_ALL poh
2086 WHERE poh.po_header_id = poll.po_header_id
2087 AND NVL(poh.pending_signature_flag, 'N') <> 'Y');
2088
2089 IF ((l_ret_sts = FND_API.G_RET_STS_UNEXP_ERROR)
2090 OR (l_ret_sts = FND_API.G_RET_STS_ERROR))
2091 THEN
2092 d_progress := 95;
2093 l_err_msg := 'update IDV header and line for total qty / amt not successful';
2094 RAISE PO_CORE_S.g_early_return_exc;
2095 END IF;
2096
2097 END IF; -- p_new_status = g_doc_status_APPROVED
2098
2099 IF (p_new_status = PO_DOCUMENT_ACTION_PVT.g_doc_status_REAPPROVAL)
2100 THEN
2101
2102 d_progress := 100;
2103
2104 UPDATE PO_LINE_LOCATIONS_ALL poll
2105 SET poll.approved_flag = 'R'
2106 , poll.last_update_date = SYSDATE
2107 , poll.last_updated_by = p_user_id
2108 , poll.last_update_login = p_login_id
2109 WHERE poll.po_header_id = p_document_id
2110 AND poll.po_release_id IS NULL
2111 AND NVL(poll.approved_flag, 'N') = 'Y';
2112
2113 END IF; -- p_new_status = g_doc_status_REAPPROVAL
2114
2115 ELSIF (p_document_type = 'RELEASE')
2116 THEN
2117
2118 d_progress := 110;
2119
2120 UPDATE PO_RELEASES por
2121 SET por.authorization_status = p_new_status,
2122 por.approved_flag = DECODE(p_new_status,
2123 PO_DOCUMENT_ACTION_PVT.g_doc_status_APPROVED, 'Y',
2124 PO_DOCUMENT_ACTION_PVT.g_doc_status_REAPPROVAL, 'R',
2125 PO_DOCUMENT_ACTION_PVT.g_doc_status_REJECTED, 'F',
2126 PO_DOCUMENT_ACTION_PVT.g_doc_status_RETURNED, 'F',
2127 PO_DOCUMENT_ACTION_PVT.g_doc_status_INCOMPLETE, 'N',
2128 PO_DOCUMENT_ACTION_PVT.g_doc_status_INPROCESS, 'N',
2129 PO_DOCUMENT_ACTION_PVT.g_doc_status_PREAPPROVED, 'N'),
2130 por.approved_date = DECODE(p_new_status,
2131 PO_DOCUMENT_ACTION_PVT.g_doc_status_APPROVED, SYSDATE,
2132 por.approved_date),
2133 por.last_update_date = SYSDATE,
2134 por.last_updated_by = p_user_id,
2135 por.last_update_login = p_login_id
2136 WHERE por.po_release_id = p_document_id;
2137
2138 IF (p_new_status = PO_DOCUMENT_ACTION_PVT.g_doc_status_APPROVED)
2139 THEN
2140
2141 d_progress := 120;
2142
2143 UPDATE PO_LINE_LOCATIONS_ALL poll
2144 SET poll.approved_flag = 'Y'
2145 , poll.approved_date = SYSDATE
2146 , poll.last_update_date = SYSDATE
2147 , poll.last_updated_by = p_user_id
2148 , poll.last_update_login = p_login_id
2149 WHERE poll.po_release_id = p_document_id
2150 AND NVL(poll.approved_flag, 'N') <> 'Y';
2151
2152 END IF; -- p_new_status = g_doc_status_APPROVED
2153
2154 IF (p_new_status = PO_DOCUMENT_ACTION_PVT.g_doc_status_REAPPROVAL)
2155 THEN
2156
2157 d_progress := 130;
2158
2159 UPDATE PO_LINE_LOCATIONS_ALL poll
2160 SET poll.approved_flag = 'R'
2161 , poll.last_update_date = SYSDATE
2162 , poll.last_updated_by = p_user_id
2163 , poll.last_update_login = p_login_id
2164 WHERE poll.po_release_id = p_document_id
2165 AND NVL(poll.approved_flag, 'N') = 'Y';
2166
2167 END IF; -- p_new_status = g_doc_status_REAPPROVAL
2168 ELSE
2169
2170 d_progress := 140;
2171 l_err_msg := 'Bad Document Type';
2172 RAISE PO_CORE_S.g_early_return_exc;
2173
2174 END IF; -- p_document_type = 'REQUISITION'
2175
2176 d_progress := 150;
2177 l_ret_sts := 'S';
2178
2179 EXCEPTION
2180 WHEN PO_CORE_S.g_early_return_exc THEN
2181 l_ret_sts := 'U';
2182 PO_DOCUMENT_ACTION_PVT.error_msg_append(d_module, d_progress, l_err_msg);
2183 IF (PO_LOG.d_exc) THEN
2184 PO_LOG.exc(d_module, d_progress, l_err_msg);
2185 END IF;
2186 END;
2187
2188
2189 x_return_status := l_ret_sts;
2190 d_progress := 200;
2191 IF (PO_LOG.d_proc) THEN
2192 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
2193 PO_LOG.proc_end(d_module);
2194 END IF;
2195
2196 RETURN;
2197
2198 EXCEPTION
2199 WHEN OTHERS THEN
2200 x_return_status := 'U';
2201
2202 PO_DOCUMENT_ACTION_PVT.error_msg_append(d_module, d_progress, SQLCODE, SQLERRM);
2203 IF (PO_LOG.d_exc) THEN
2204 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
2205 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
2206 PO_LOG.proc_end(d_module);
2207 END IF;
2208
2209 RETURN;
2210
2211 END update_doc_auth_status;
2212
2213
2214 -- Equivalent to: podusnotif
2215 -- Does not appear to be used anymore
2216 -- Translated just in case
2217 PROCEDURE update_doc_notifications(
2218 p_document_id IN NUMBER
2219 , p_document_type IN VARCHAR2
2220 , p_document_subtype IN VARCHAR2
2221 , p_notify_action IN VARCHAR2
2222 , p_notify_employee IN NUMBER
2223 , p_doc_creation_date IN DATE
2224 , p_user_id IN NUMBER
2225 , p_login_id IN NUMBER
2226 , x_return_status OUT NOCOPY VARCHAR2
2227 )
2228 IS
2229
2230 d_progress NUMBER;
2231 d_module VARCHAR2(70) := 'po.plsql.PO_DOCUMENT_ACTION_UTIL.update_doc_notifications';
2232
2233 BEGIN
2234
2235 d_progress := 0;
2236 IF (PO_LOG.d_proc) THEN
2237 PO_LOG.proc_begin(d_module);
2238 PO_LOG.proc_begin(d_module, 'p_document_id', p_document_id);
2239 PO_LOG.proc_begin(d_module, 'p_document_type', p_document_type);
2240 PO_LOG.proc_begin(d_module, 'p_document_subtype', p_document_subtype);
2241 PO_LOG.proc_begin(d_module, 'p_notify_action', p_notify_action);
2242 PO_LOG.proc_begin(d_module, 'p_notify_employee', p_notify_employee);
2243 PO_LOG.proc_begin(d_module, 'p_doc_creation_date', p_doc_creation_date);
2244 PO_LOG.proc_begin(d_module, 'p_user_id', p_user_id);
2245 END IF;
2246
2247 d_progress := 10;
2248
2249 DELETE FROM PO_NOTIFICATIONS pon
2250 WHERE pon.object_type_lookup_code = DECODE(p_document_type,
2251 'PO', p_document_subtype,
2252 'PA', p_document_subtype,
2253 p_document_type)
2254 AND pon.object_id = p_document_id
2255 AND pon.employee_id > -1;
2256
2257
2258 IF (p_notify_action IS NOT NULL)
2259 THEN
2260
2261 d_progress := 20;
2262
2263 INSERT INTO PO_NOTIFICATIONS(
2264 employee_id
2265 , object_type_lookup_code
2266 , object_id
2267 , last_update_date
2268 , last_updated_by
2269 , last_update_login
2270 , creation_date
2271 , created_by
2272 , object_creation_date
2273 , action_lookup_code
2274 )
2275 VALUES(
2276 p_notify_employee
2277 , DECODE(p_document_type,
2278 'PO', p_document_subtype,
2279 'PA', p_document_subtype,
2280 p_document_type)
2281 , p_document_id
2282 , SYSDATE
2283 , p_user_id
2284 , p_login_id
2285 , SYSDATE
2286 , p_user_id
2287 , SYSDATE
2288 , p_notify_action
2289 );
2290
2291 END IF; -- p_notify_action IS NOT NULL
2292
2293 x_return_status := 'S';
2294 d_progress := 100;
2295 IF (PO_LOG.d_proc) THEN
2296 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
2297 PO_LOG.proc_end(d_module);
2298 END IF;
2299
2300 RETURN;
2301
2302 EXCEPTION
2303 WHEN OTHERS THEN
2304 x_return_status := 'U';
2305
2306 PO_DOCUMENT_ACTION_PVT.error_msg_append(d_module, d_progress, SQLCODE, SQLERRM);
2307 IF (PO_LOG.d_exc) THEN
2308 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
2309 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
2310 PO_LOG.proc_end(d_module);
2311 END IF;
2312
2313 RETURN;
2314
2315 END update_doc_notifications;
2316
2317
2318
2319 PROCEDURE insert_auth_action_history(
2320 p_document_id IN NUMBER
2321 , p_revision_num IN NUMBER
2322 , p_document_type IN VARCHAR2
2323 , p_document_subtype IN VARCHAR2
2324 , p_draft_id IN NUMBER --CLM Apprvl
2325 , p_action IN VARCHAR2
2326 , p_employee_id IN NUMBER
2327 , p_offline_code IN VARCHAR2
2328 , p_approval_path_id IN NUMBER
2329 , p_note IN VARCHAR2
2330 , p_user_id IN NUMBER
2331 , p_login_id IN NUMBER
2332 , x_return_status OUT NOCOPY VARCHAR2
2333 )
2334 IS
2335
2336 l_sequence_num PO_ACTION_HISTORY.sequence_num%TYPE;
2337
2338 d_progress NUMBER;
2339 d_module VARCHAR2(70) := 'po.plsql.PO_DOCUMENT_ACTION_UTIL.insert_auth_action_history';
2340
2341 BEGIN
2342
2343 d_progress := 0;
2344 IF (PO_LOG.d_proc) THEN
2345 PO_LOG.proc_begin(d_module);
2346 PO_LOG.proc_begin(d_module, 'p_document_id', p_document_id);
2347 PO_LOG.proc_begin(d_module, 'p_revision_num', p_revision_num);
2348 PO_LOG.proc_begin(d_module, 'p_document_type', p_document_type);
2349 PO_LOG.proc_begin(d_module, 'p_document_subtype', p_document_subtype);
2350 PO_LOG.proc_begin(d_module, 'p_draft_id', p_draft_id); -- CLM Apprvl
2351 PO_LOG.proc_begin(d_module, 'p_action', p_action);
2352 PO_LOG.proc_begin(d_module, 'p_employee_id', p_employee_id);
2353 PO_LOG.proc_begin(d_module, 'p_offline_code', p_offline_code);
2354 PO_LOG.proc_begin(d_module, 'p_approval_path_id', p_approval_path_id);
2355 PO_LOG.proc_begin(d_module, 'p_note', p_note);
2356 PO_LOG.proc_begin(d_module, 'p_user_id', p_user_id);
2357 PO_LOG.proc_begin(d_module, 'p_login_id', p_login_id);
2358 END IF;
2359
2360 d_progress := 10;
2361
2362 -- CLM Apprvl
2363 IF p_draft_id IS NOT NULL AND p_draft_id <> -1 THEN
2364 SELECT max(poah.sequence_num) + 1
2365 INTO l_sequence_num
2366 FROM PO_ACTION_HISTORY poah
2367 WHERE poah.object_type_code = p_document_type
2368 AND poah.object_sub_type_code = 'MODIFICATION'
2369 AND poah.object_id = p_draft_id;
2370 ELSE
2371 SELECT max(poah.sequence_num) + 1
2372 INTO l_sequence_num
2373 FROM PO_ACTION_HISTORY poah
2374 WHERE poah.object_type_code = p_document_type
2375 AND poah.object_id = p_document_id;
2376 END IF;
2377
2378
2379 -- <Bug 4118145 - Issue 1 Start>
2380
2381 d_progress := 15;
2382
2383 IF (l_sequence_num IS NULL)
2384 THEN
2385 l_sequence_num := 1; -- Bug 13579433
2386 END IF;
2387
2388 IF (PO_LOG.d_stmt) THEN
2389 PO_LOG.stmt(d_module, d_progress, 'l_sequence_num', l_sequence_num);
2390 END IF;
2391 -- <Bug 4118145 - Issue 1 End>
2392
2393 d_progress := 20;
2394
2395 INSERT INTO PO_ACTION_HISTORY
2396 ( object_id
2397 , object_type_code
2398 , object_sub_type_code
2399 , sequence_num
2400 , last_update_date
2401 , last_updated_by
2402 , creation_date
2403 , created_by
2404 , action_code
2405 , action_date
2406 , employee_id
2407 , note
2408 , object_revision_num
2409 , last_update_login
2410 , request_id
2411 , program_application_id
2412 , program_id
2413 , program_update_date
2414 , approval_path_id
2415 , offline_code
2416 )
2417 VALUES
2418 ( DECODE(p_draft_id,null,p_document_id,-1,p_document_id,p_draft_id) --CLM Apprvl
2419 , p_document_type
2420 , DECODE(p_draft_id,null,p_document_subtype,-1,p_document_subtype,'MODIFICATION')
2421 , l_sequence_num
2422 , SYSDATE
2423 , p_user_id
2424 , SYSDATE
2425 , p_user_id
2426 , p_action
2427 , DECODE(p_action, '', to_date(NULL), SYSDATE)
2428 , p_employee_id
2429 , p_note
2430 , p_revision_num
2431 , p_login_id
2432 , 0
2433 , 0
2434 , 0
2435 , ''
2436 , p_approval_path_id
2437 , p_offline_code
2438 );
2439
2440 x_return_status := 'S';
2441 d_progress := 100;
2442 IF (PO_LOG.d_proc) THEN
2443 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
2444 PO_LOG.proc_end(d_module);
2445 END IF;
2446
2447 RETURN;
2448
2449 EXCEPTION
2450 WHEN OTHERS THEN
2451 x_return_status := 'U';
2452
2453 PO_DOCUMENT_ACTION_PVT.error_msg_append(d_module, d_progress, SQLCODE, SQLERRM);
2454 IF (PO_LOG.d_exc) THEN
2455 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
2456 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
2457 PO_LOG.proc_end(d_module);
2458 END IF;
2459
2460 RETURN;
2461
2462 END insert_auth_action_history;
2463
2464 PROCEDURE update_auth_action_history(
2465 p_document_id IN NUMBER
2466 , p_revision_num IN NUMBER
2467 , p_document_type IN VARCHAR2
2468 , p_draft_id IN NUMBER --CLM Apprvl
2469 , p_action IN VARCHAR2
2470 , p_approval_path_id IN NUMBER
2471 , p_note IN VARCHAR2
2472 , p_user_id IN NUMBER
2473 , x_return_status OUT NOCOPY VARCHAR2
2474 )
2475 IS
2476
2477 d_progress NUMBER;
2478 d_module VARCHAR2(70) := 'po.plsql.PO_DOCUMENT_ACTION_UTIL.update_auth_action_history';
2479
2480 BEGIN
2481
2482 d_progress := 0;
2483 IF (PO_LOG.d_proc) THEN
2484 PO_LOG.proc_begin(d_module);
2485 PO_LOG.proc_begin(d_module, 'p_document_id', p_document_id);
2486 PO_LOG.proc_begin(d_module, 'p_revision_num', p_revision_num);
2487 PO_LOG.proc_begin(d_module, 'p_document_type', p_document_type);
2488 PO_LOG.proc_begin(d_module, 'p_draft_id', p_draft_id); --CLM Apprvl
2489 PO_LOG.proc_begin(d_module, 'p_action', p_action);
2490 PO_LOG.proc_begin(d_module, 'p_approval_path_id', p_approval_path_id);
2491 PO_LOG.proc_begin(d_module, 'p_note', p_note);
2492 PO_LOG.proc_begin(d_module, 'p_user_id', p_user_id);
2493 END IF;
2494
2495 d_progress := 10;
2496
2497 -- CLM Apprvl
2498 IF p_draft_id IS NOT NULL AND p_draft_id <> -1 THEN
2499 UPDATE PO_ACTION_HISTORY
2500 SET action_code = p_action
2501 , action_date = SYSDATE
2502 , note = p_note
2503 , last_updated_by = p_user_id
2504 , last_update_date = SYSDATE
2505 , object_revision_num = p_revision_num
2506 , approval_path_id = p_approval_path_id
2507 WHERE object_id = p_draft_id
2508 AND object_type_code = p_document_type
2509 AND action_code IS NULL;
2510 ELSE
2511 UPDATE PO_ACTION_HISTORY
2512 SET action_code = p_action
2513 , action_date = SYSDATE
2514 , note = p_note
2515 , last_updated_by = p_user_id
2516 , last_update_date = SYSDATE
2517 , object_revision_num = p_revision_num
2518 , approval_path_id = p_approval_path_id
2519 WHERE object_id = p_document_id
2520 AND object_type_code = p_document_type
2521 AND action_code IS NULL;
2522 END IF;
2523
2524 x_return_status := 'S';
2525 d_progress := 100;
2526 IF (PO_LOG.d_proc) THEN
2527 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
2528 PO_LOG.proc_end(d_module);
2529 END IF;
2530
2531 RETURN;
2532
2533 EXCEPTION
2534 WHEN OTHERS THEN
2535 x_return_status := 'U';
2536
2537 PO_DOCUMENT_ACTION_PVT.error_msg_append(d_module, d_progress, SQLCODE, SQLERRM);
2538 IF (PO_LOG.d_exc) THEN
2539 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
2540 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
2541 PO_LOG.proc_end(d_module);
2542 END IF;
2543
2544 RETURN;
2545
2546 END update_auth_action_history;
2547
2548 --------------------------------------------------------------------------------
2549 --Start of Comments
2550
2551 --Name: upd_idv_qty_amt
2552
2553 --Function:
2554 -- This will calculate the release amount for a line and updates the IDV.
2555 -- This procedure is CLM specific.
2556
2557 --Parameters:
2558
2559 --IN:
2560 -- p_po_line_id NUMBER
2561 -- p_draft_id NUMBER
2562 -- p_po_mode VARCHAR2
2563
2564 --OUT:
2565 -- x_return_status VARCHAR2
2566
2567 --End of Comments
2568 --------------------------------------------------------------------------------
2569 PROCEDURE upd_idv_qty_amt(p_po_line_id IN NUMBER,
2570 p_draft_id IN NUMBER,
2571 p_po_mode IN VARCHAR2,
2572 x_return_status OUT NOCOPY VARCHAR2
2573 ) IS
2574
2575 d_api_name CONSTANT VARCHAR2(30) := 'upd_idv_qty_amt';
2576 d_module CONSTANT VARCHAR2(100) := g_pkg_name || d_api_name;
2577 d_progress NUMBER := 0;
2578
2579 l_po_header_id NUMBER;
2580 l_idv_header_id NUMBER;
2581 l_idv_line_id NUMBER;
2582 l_clm_amt_released_hdr NUMBER;
2583 l_line_amount NUMBER;
2584 l_amt_ordered_idv_line NUMBER;
2585 l_qty_ordered_idv_line NUMBER ;
2586 l_line_quantity NUMBER;
2587 l_line_type VARCHAR2(25);
2588 l_mod_amount NUMBER;
2589 l_mod_quantity NUMBER;
2590 l_dist_qty_cancel NUMBER;
2591 l_dist_amt_cancel NUMBER;
2592 l_tot_dist_can_amt NUMBER;
2593 l_tot_dist_can_qty NUMBER;
2594 l_diff_amt NUMBER;
2595 l_diff_qty NUMBER;
2596
2597 l_return_status VARCHAR2(2);
2598
2599 CURSOR c_get_dist(p_po_line_id NUMBER) IS
2600 SELECT po_distribution_id FROM po_distributions_all
2601 WHERE po_line_id = p_po_line_id;
2602
2603 BEGIN
2604
2605 d_progress := 0;
2606
2607 IF (PO_LOG.d_proc) THEN
2608 PO_LOG.proc_begin(d_module);
2609 PO_LOG.proc_begin(d_module, 'p_po_line_id', p_po_line_id);
2610 PO_LOG.proc_begin(d_module, 'p_draft_id', p_draft_id);
2611 PO_LOG.proc_begin(d_module, 'p_po_mode', p_po_mode);
2612 END IF;
2613
2614 -- Changed the above line to handle the CPA case also
2615 SELECT po_header_id, from_line_id, nvl(from_header_id, contract_id), amount, quantity, matching_basis
2616 INTO l_po_header_id, l_idv_line_id, l_idv_header_id, l_line_amount, l_line_quantity, l_line_type
2617 FROM po_lines_merge_v
2618 WHERE po_line_id = p_po_line_id
2619 AND draft_id = p_draft_id;
2620
2621 /*get Amount FROM po_document_totals_pvt.getAmountOrdered and not from line*/
2622 BEGIN
2623 l_line_amount := po_document_totals_pvt.getAmountOrdered(p_doc_level => po_document_totals_pvt.g_doc_level_LINE,
2624 p_doc_level_id => p_po_line_id,
2625 p_data_source => po_document_totals_pvt.g_data_source_TRANSACTION,
2626 p_draft_id => -1);
2627 EXCEPTION
2628 --Added No_Data_Found exception because, the above method will throw this excpetion when a new line is added in mod
2629 WHEN No_Data_Found THEN
2630 l_line_amount := 0;
2631 END;
2632 -- Changed the above line to handle the CPA case also
2633 IF ( l_idv_header_id IS NOT NULL) THEN
2634
2635 SELECT clm_amount_released INTO l_clm_amt_released_hdr
2636 FROM po_headers_all
2637 WHERE po_header_id = l_idv_header_id;
2638
2639 -- Changed the above line to handle the BPA case only
2640 IF ( l_idv_line_id IS NOT NULL) THEN
2641 SELECT clm_total_amount_ordered, clm_total_quantity_ordered INTO l_amt_ordered_idv_line, l_qty_ordered_idv_line
2642 FROM po_lines_all
2643 WHERE po_line_id = l_idv_line_id;
2644 END IF;
2645
2646 d_progress := 10;
2647 IF (PO_LOG.d_stmt) THEN
2648 PO_LOG.stmt(d_module, d_progress, 'l_idv_header_id', l_idv_header_id);
2649 PO_LOG.stmt(d_module, d_progress, 'l_amt_ordered_idv_line', l_amt_ordered_idv_line);
2650 PO_LOG.stmt(d_module, d_progress, 'l_clm_amt_released_hdr', l_clm_amt_released_hdr);
2651 PO_LOG.stmt(d_module, d_progress, 'l_line_quantity', l_line_quantity);
2652 PO_LOG.stmt(d_module, d_progress, 'l_line_amount', l_line_amount);
2653
2654 END IF;
2655 IF p_po_mode = po_document_action_pvt.g_doc_action_APPROVE THEN
2656
2657 d_progress := 20;
2658 IF (p_draft_id IS NULL OR p_draft_id = -1) THEN -- Main PO
2659 -- Changed the above line to handle the BPA case only
2660 IF l_idv_line_id IS NOT NULL THEN
2661 -- updates idv lines total_amt_ordered
2662 l_amt_ordered_idv_line := nvl(l_amt_ordered_idv_line,0) + l_line_amount;
2663 END IF;
2664 -- update idv headers total amt released
2665 l_clm_amt_released_hdr := nvl(l_clm_amt_released_hdr,0) + l_line_amount;
2666
2667 d_progress := 30;
2668 -- Changed the above line to handle the BPA case only
2669 IF l_idv_line_id IS NOT NULL THEN
2670 --update idv_lines total_qty
2671 IF(l_line_type = 'QUANTITY') THEN
2672 l_qty_ordered_idv_line := nvl(l_qty_ordered_idv_line,0) + l_line_quantity;
2673 ELSIF (l_line_type = 'AMOUNT') THEN
2674 -- to do for complex pricing
2675 IF (PO_LOG.d_stmt) THEN
2676 PO_LOG.stmt(d_module, d_progress, 'l_line_type ', l_line_type);
2677 END IF;
2678
2679 END IF;
2680 END IF;
2681
2682 ELSE -- Mod
2683 d_progress := 40;
2684 BEGIN
2685 SELECT quantity, amount INTO l_mod_quantity, l_mod_amount
2686 FROM po_lines_draft_all
2687 WHERE po_line_id = p_po_line_id
2688 AND draft_id = p_draft_id;
2689
2690 /*get Amount FROM po_document_totals_pvt.getAmountOrdered and not from line*/
2691 BEGIN
2692 l_mod_amount := po_document_totals_pvt.getAmountOrdered(p_doc_level => po_document_totals_pvt.g_doc_level_LINE,
2693 p_doc_level_id => p_po_line_id,
2694 p_data_source => po_document_totals_pvt.g_data_source_TRANSACTION,
2695 p_draft_id => p_draft_id);
2696
2697 EXCEPTION
2698 WHEN No_Data_Found THEN
2699 l_mod_amount := 0;
2700 END;
2701 l_diff_amt := l_mod_amount - l_line_amount;
2702 -- Changed the above line to handle the BPA case only
2703 IF l_idv_line_id IS NOT NULL THEN
2704 -- updates idv lines total_amt_ordered
2705 l_amt_ordered_idv_line := l_amt_ordered_idv_line + l_diff_amt;
2706 END IF;
2707 -- update idv headers total amt released
2708 l_clm_amt_released_hdr := l_clm_amt_released_hdr + l_diff_amt;
2709
2710 d_progress := 50;
2711 -- Changed the above line to handle the BPA case only
2712 IF l_idv_line_id IS NOT NULL THEN
2713 --update idv_lines total_qty
2714 IF(l_line_type = 'QUANTITY') THEN
2715 l_diff_qty := l_mod_quantity - l_line_quantity;
2716 l_qty_ordered_idv_line := l_qty_ordered_idv_line + l_diff_qty;
2717 ELSIF (l_line_type = 'AMOUNT') THEN
2718 IF (PO_LOG.d_stmt) THEN
2719 PO_LOG.stmt(d_module, d_progress, 'l_line_type ', l_line_type);
2720 END IF;
2721
2722 END IF;
2723 END IF;
2724 EXCEPTION
2725 WHEN No_Data_Found THEN
2726 PO_LOG.stmt(d_module, d_progress, 'Line is not modified ', p_po_line_id);
2727 END;
2728 END IF;
2729 /*ELSIF (p_po_mode = po_document_action_pvt.g_doc_action_CANCEL) THEN
2730
2731 d_progress := 60;
2732 FOR c_get_dist_rec IN c_get_dist(p_po_line_id) LOOP
2733 SELECT quantity_cancelled, amount_cancelled INTO l_dist_qty_cancel, l_dist_amt_cancel FROM po_distributions_all
2734 WHERE po_distribution_id = c_get_dist_rec.po_distribution_id;
2735
2736 l_tot_dist_can_amt := l_tot_dist_can_amt + l_dist_amt_cancel;
2737 l_tot_dist_can_qty := l_tot_dist_can_qty + l_dist_qty_cancel;
2738 END LOOP;
2739 -- Changed the above line to handle the BPA case only
2740 IF l_idv_line_id IS NOT NULL THEN
2741 -- updates idv lines total_amt_ordered
2742 l_amt_ordered_idv_line := l_amt_ordered_idv_line - l_tot_dist_can_amt;
2743 END IF;
2744
2745 -- update idv headers total amt released
2746 l_clm_amt_released_hdr := l_clm_amt_released_hdr - l_tot_dist_can_amt;
2747
2748 d_progress := 70;
2749 -- Changed the above line to handle the BPA case only
2750 IF l_idv_line_id IS NOT NULL THEN
2751 --update idv_lines total_qty
2752 IF(l_line_type = 'QUANTITY') THEN
2753 l_qty_ordered_idv_line := l_qty_ordered_idv_line - l_tot_dist_can_qty;
2754 ELSIF (l_line_type = 'AMOUNT') THEN
2755 -- to do for complex pricing
2756 IF (PO_LOG.d_stmt) THEN
2757 PO_LOG.stmt(d_module, d_progress, 'l_line_type ', l_line_type);
2758 END IF;
2759 END IF;
2760 END IF;*/
2761
2762 END IF;
2763
2764 d_progress := 70;
2765
2766 IF (PO_LOG.d_stmt) THEN
2767 PO_LOG.stmt(d_module, d_progress, 'l_clm_amt_released_hdr ', l_clm_amt_released_hdr);
2768 PO_LOG.stmt(d_module, d_progress, 'l_amt_ordered_idv_line ', l_amt_ordered_idv_line);
2769 PO_LOG.stmt(d_module, d_progress, 'l_qty_ordered_idv_line ', l_qty_ordered_idv_line);
2770 END IF;
2771
2772 UPDATE po_headers_all SET CLM_AMOUNT_RELEASED = l_clm_amt_released_hdr
2773 WHERE po_header_id = l_idv_header_id;
2774
2775 d_progress := 80;
2776 -- Changed the above line to handle the BPA case only
2777 IF l_idv_line_id IS NOT NULL THEN
2778 UPDATE po_lines_all SET clm_total_amount_ordered = l_amt_ordered_idv_line, clm_total_quantity_ordered = l_qty_ordered_idv_line
2779 WHERE po_line_id = l_idv_line_id;
2780 END IF;
2781
2782 END IF; -- l_idv_header_id is not null
2783
2784 x_return_status := FND_API.g_ret_sts_success;
2785
2786 d_progress := 90;
2787
2788 IF (PO_LOG.d_proc) THEN
2789 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
2790 PO_LOG.proc_end(d_module);
2791 END IF;
2792
2793 EXCEPTION
2794 WHEN OTHERS THEN
2795 x_return_status := 'U';
2796 IF PO_LOG.d_stmt THEN
2797 PO_LOG.stmt(d_module, d_progress, 'x_return_status', x_return_status);
2798 END IF;
2799
2800 END upd_idv_qty_amt;
2801
2802 --------------------------------------------------------------------------------
2803 --Start of Comments
2804
2805 --Name: upd_idv_qty_amt_hdr
2806
2807 --Function:
2808 -- This will calculate the release amount for an IDV.
2809 -- This procedure is CLM specific.
2810
2811 --Parameters:
2812
2813 --IN:
2814 -- p_po_header_id NUMBER
2815 -- p_draft_id NUMBER
2816 -- p_po_mode VARCHAR2
2817
2818 --OUT:
2819 -- x_return_status VARCHAR2
2820
2821 --End of Comments
2822 --------------------------------------------------------------------------------
2823 PROCEDURE upd_idv_qty_amt_hdr(
2824 p_po_header_id IN NUMBER
2825 , p_draft_id IN NUMBER
2826 , p_po_mode IN VARCHAR2
2827 , x_return_status OUT NOCOPY VARCHAR2
2828 ) IS
2829
2830 d_api_name CONSTANT VARCHAR2(30) := 'upd_idv_qty_amt_hdr';
2831 d_module CONSTANT VARCHAR2(100) := g_pkg_name || d_api_name;
2832 d_progress NUMBER := 0;
2833
2834 CURSOR c_get_po_line_dtls(p_po_header_id NUMBER) IS
2835 SELECT po_line_id FROM po_lines_merge_v
2836 WHERE po_header_id = p_po_header_id
2837 AND draft_id = p_draft_id
2838 AND (from_header_id IS NOT NULL or contract_id is not null);
2839 --AND from_line_id IS NOT null;
2840 -- Changed the above line to handle the CPA case also
2841
2842 BEGIN
2843
2844 d_progress := 0;
2845
2846 IF (PO_LOG.d_proc) THEN
2847 PO_LOG.proc_begin(d_module);
2848 PO_LOG.proc_begin(d_module, 'p_po_header_id', p_po_header_id);
2849 PO_LOG.proc_begin(d_module, 'p_draft_id', p_draft_id);
2850 PO_LOG.proc_begin(d_module, 'p_po_mode', p_po_mode);
2851 END IF;
2852
2853 FOR c_get_po_line_dtls_rec IN c_get_po_line_dtls(p_po_header_id) LOOP
2854 upd_idv_qty_amt(p_po_line_id => c_get_po_line_dtls_rec.po_line_id
2855 , p_draft_id => p_draft_id
2856 , p_po_mode => p_po_mode
2857 , x_return_status => x_return_status);
2858 END LOOP;
2859
2860 d_progress := 10;
2861
2862 IF (PO_LOG.d_proc) THEN
2863 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
2864 PO_LOG.proc_end(d_module);
2865 END IF;
2866
2867 END upd_idv_qty_amt_hdr;
2868
2869 END PO_DOCUMENT_ACTION_UTIL;