[Home] [Help]
PACKAGE BODY: APPS.PO_DOCUMENT_ACTION_CLOSE
Source
1 PACKAGE BODY PO_DOCUMENT_ACTION_CLOSE AS
2 -- $Header: POXDACLB.pls 120.9 2006/11/07 08:33:25 scolvenk noship $
3
4 -- Private package constants
5
6 g_pkg_name CONSTANT varchar2(30) := 'PO_DOCUMENT_ACTION_CLOSE';
7 g_log_head CONSTANT VARCHAR2(50) := 'po.plsql.'|| g_pkg_name || '.';
8
9 -- Private package types
10
11 TYPE g_tbl_number IS TABLE OF NUMBER;
12
13 TYPE g_tbl_closed_code IS TABLE OF PO_LINE_LOCATIONS.closed_code%TYPE;
14
15 -- Forward declare private methods
16
17 FUNCTION manual_close_submission_check(
18 p_document_id IN NUMBER
19 , p_document_type IN VARCHAR2
20 , p_document_subtype IN VARCHAR2
21 , p_action IN VARCHAR2
22 , p_calling_mode IN VARCHAR2
23 , p_line_id IN NUMBER
24 , p_shipment_id IN NUMBER
25 , p_origin_doc_id IN NUMBER -- Bug#5462677
26 , x_return_status OUT NOCOPY VARCHAR2
27 , x_online_report_id OUT NOCOPY NUMBER
28 ) RETURN BOOLEAN;
29
30 FUNCTION manual_close_state_check(
31 p_document_id IN NUMBER
32 , p_document_type IN VARCHAR2
33 , p_action IN VARCHAR2
34 , p_calling_mode IN VARCHAR2
35 , p_line_id IN NUMBER
36 , p_shipment_id IN NUMBER
37 , x_return_status OUT NOCOPY VARCHAR2
38 ) RETURN BOOLEAN;
39
40 FUNCTION auto_close_state_check(
41 p_document_id IN NUMBER
42 , p_document_type IN VARCHAR2
43 , p_line_id IN NUMBER
44 , p_shipment_id IN NUMBER
45 , x_return_status OUT NOCOPY VARCHAR2
46 ) RETURN BOOLEAN;
47
48 PROCEDURE handle_close_encumbrance(
49 p_document_id IN NUMBER
50 , p_document_type IN VARCHAR2
51 , p_document_subtype IN VARCHAR2
52 , p_action IN VARCHAR2
53 , p_calling_mode IN VARCHAR2
54 , p_line_id IN NUMBER
55 , p_shipment_id IN NUMBER
56 , p_origin_doc_id IN NUMBER
57 , p_action_date IN DATE
58 , p_use_gl_date IN VARCHAR2
59 , x_return_status OUT NOCOPY VARCHAR2
60 , x_return_code OUT NOCOPY VARCHAR2
61 , x_online_report_id OUT NOCOPY NUMBER
62 , x_enc_flag OUT NOCOPY BOOLEAN
63 );
64
65 PROCEDURE manual_update_closed_status(
66 p_document_id IN NUMBER
67 , p_document_type IN VARCHAR2
68 , p_document_subtype IN VARCHAR2
69 , p_action IN VARCHAR2
70 , p_calling_mode IN VARCHAR2
71 , p_line_id IN NUMBER
72 , p_shipment_id IN NUMBER
73 , p_user_id IN NUMBER
74 , p_login_id IN NUMBER
75 , p_employee_id IN NUMBER
76 , p_reason IN VARCHAR2
77 , p_enc_flag IN BOOLEAN
78 , x_return_status OUT NOCOPY VARCHAR2
79 );
80
81 PROCEDURE auto_update_closed_status(
82 p_document_id IN NUMBER
83 , p_document_type IN VARCHAR2
84 , p_calling_mode IN VARCHAR2
85 , p_line_id IN NUMBER
86 , p_shipment_id IN NUMBER
87 , p_employee_id IN NUMBER
88 , p_user_id IN NUMBER --bug4964600
89 , p_login_id IN NUMBER --bug4964600
90 , p_reason IN VARCHAR2
91 , x_return_status OUT NOCOPY VARCHAR2
92 );
93
94 PROCEDURE rollup_close_state(
95 p_document_id IN NUMBER
96 , p_document_type IN VARCHAR2
97 , p_document_subtype IN VARCHAR2
98 , p_action IN VARCHAR2
99 , p_line_id IN NUMBER
100 , p_shipment_id IN NUMBER
101 , p_user_id IN NUMBER
102 , p_login_id IN NUMBER
103 , p_employee_id IN NUMBER
104 , p_reason IN VARCHAR2
105 , p_action_date IN DATE
106 , p_calling_mode IN VARCHAR2
107 , x_return_status OUT NOCOPY VARCHAR2
108 );
109
110 PROCEDURE handle_manual_close_supply(
111 p_document_id IN NUMBER
112 , p_document_type IN VARCHAR2
113 , p_action IN VARCHAR2
114 , p_line_id IN NUMBER
115 , p_shipment_id IN NUMBER
116 , x_return_status OUT NOCOPY VARCHAR2
117 );
118
119 PROCEDURE handle_auto_close_supply(
120 p_document_id IN NUMBER
121 , p_document_type IN VARCHAR2
122 , p_line_id IN NUMBER
123 , p_shipment_id IN NUMBER
124 , x_return_status OUT NOCOPY VARCHAR2
125 );
126
127 -- Public methods
128
129 ------------------------------------------------------------------------------
130 --Start of Comments
131 --Name: manual_close_po
132 --Pre-reqs:
133 -- Document is locked.
134 -- Org context is set to that of document.
135 --Modifies:
136 -- None, directly.
137 --Locks:
138 -- None.
139 --Function:
140 -- This procedure applies the logic of an open or close action
141 -- onto a document entity.
142 -- The logic is:
143 -- 1. get user_id, login_id, and employee_id
144 -- 2. do a document state check, e.g. has proper authorization status
145 -- 3. do a document submission check; only relevant for final close
146 -- 4. for invoice open from AP, reopen finally closed shipments
147 -- these shipments are initially reopened to status closed.
148 -- 5. handle encumbrance for AP invoice open and final close
149 -- 6. update lowest level closed status
150 -- 7. rollup closed status changes as necessary to higher levels
151 -- this also handles the action history
152 -- 8. handle supply
153 --Replaces:
154 -- This method covers poccstatus in poccs.lpc.
155 --Parameters:
156 --IN:
157 -- p_action_ctl_rec
158 -- Record containing all necessary parameters for close action.
159 --OUT:
160 -- p_action_ctl_rec
161 -- Record contains variables that record output values depending
162 -- on the action. All actions will populate at least a return_status.
163 --End of Comments
164 -------------------------------------------------------------------------------
165 PROCEDURE manual_close_po(
166 p_action_ctl_rec IN OUT NOCOPY PO_DOCUMENT_ACTION_PVT.doc_action_call_rec_type
167 )
168 IS
169
170 d_module VARCHAR2(70) := 'po.plsql.PO_DOCUMENT_ACTION_CLOSE.manual_close_po';
171 d_progress NUMBER;
172 d_msg VARCHAR2(200);
173
174 l_ret_sts VARCHAR2(1);
175
176 l_user_id NUMBER;
177 l_login_id NUMBER;
178
179 l_emp_flag BOOLEAN;
180 l_emp_id NUMBER;
181
182 l_state_check_ok BOOLEAN;
183 l_sub_check_ok BOOLEAN;
184 l_enc_flag BOOLEAN;
185 l_ret_code VARCHAR2(25);
186
187 l_rollback_flag BOOLEAN := FALSE;
188
189
190 BEGIN
191
192 d_progress := 0;
193 IF (PO_LOG.d_proc) THEN
194 PO_LOG.proc_begin(d_module);
195 PO_LOG.proc_begin(d_module, 'p_action_ctl_rec.document_id', p_action_ctl_rec.document_id);
196 PO_LOG.proc_begin(d_module, 'p_action_ctl_rec.document_type', p_action_ctl_rec.document_type);
197 PO_LOG.proc_begin(d_module, 'p_action_ctl_rec.document_subtype', p_action_ctl_rec.document_subtype);
198 PO_LOG.proc_begin(d_module, 'p_action_ctl_rec.line_id', p_action_ctl_rec.line_id);
199 PO_LOG.proc_begin(d_module, 'p_action_ctl_rec.shipment_id', p_action_ctl_rec.shipment_id);
200 PO_LOG.proc_begin(d_module, 'p_action_ctl_rec.action', p_action_ctl_rec.action);
201 PO_LOG.proc_begin(d_module, 'p_action_ctl_rec.note', p_action_ctl_rec.note);
202 PO_LOG.proc_begin(d_module, 'p_action_ctl_rec.called_from_conc', p_action_ctl_rec.called_from_conc);
203 PO_LOG.proc_begin(d_module, 'p_action_ctl_rec.calling_mode', p_action_ctl_rec.calling_mode);
204 PO_LOG.proc_begin(d_module, 'p_action_ctl_rec.origin_doc_id', p_action_ctl_rec.origin_doc_id);
205 PO_LOG.proc_begin(d_module, 'p_action_ctl_rec.action_date', p_action_ctl_rec.action_date);
206 PO_LOG.proc_begin(d_module, 'p_action_ctl_rec.use_gl_date', p_action_ctl_rec.use_gl_date);
207 END IF;
208
209 SAVEPOINT DA_MANUAL_CLOSE_SP;
210
211 BEGIN
212
213 d_progress := 10;
214
215 l_user_id := FND_GLOBAL.USER_ID;
216
217 IF (l_user_id = -1)
218 THEN
219 d_progress := 20;
220 l_ret_sts := 'U';
221 d_msg := 'user id not found';
222 RAISE PO_CORE_S.g_early_return_exc;
223 END IF;
224
225 d_progress := 30;
226
227 IF (p_action_ctl_rec.called_from_conc)
228 THEN
229 l_login_id := FND_GLOBAL.CONC_LOGIN_ID;
230 ELSE
231 l_login_id := FND_GLOBAL.LOGIN_ID;
232 END IF;
233
234 -- <Bug 4118145: Issue 7>: From approval workflow,
235 -- login_id can be -1; this is now allowed.
236 -- Validation of login_id, if desired, it is left to the caller
237
238 IF (PO_LOG.d_stmt) THEN
239 PO_LOG.stmt(d_module, d_progress, 'l_login_id', l_login_id);
240 PO_LOG.stmt(d_module, d_progress, 'l_user_id', l_user_id);
241 END IF;
242
243 d_progress := 50;
244
245 PO_DOCUMENT_ACTION_UTIL.get_employee_id(
246 p_user_id => l_user_id
247 , x_return_status => l_ret_sts
248 , x_employee_flag => l_emp_flag
249 , x_employee_id => l_emp_id
250 );
251
252 IF (l_ret_sts <> 'S')
253 THEN
254 d_progress := 60;
255 l_ret_sts := 'U';
256 d_msg := 'get_employee_id not successful';
257 RAISE PO_CORE_S.g_early_return_exc;
258 END IF;
259
260 IF (NOT l_emp_flag)
261 THEN
262
263 -- See Bug 236640; in Pro*C, we just set to NULL
264 -- commenting out exception raising
265 -- l_ret_sts := 'U';
266 -- d_msg := 'user is not an employee';
267 -- RAISE PO_CORE_S.g_early_return_exc;
268
269 d_progress := 70;
270 l_emp_id := NULL;
271
272 IF (PO_LOG.d_stmt) THEN
273 PO_LOG.stmt(d_module, d_progress, 'user is not an employee');
274 END IF;
275
276 END IF;
277
278 d_progress := 80;
279
280 l_state_check_ok := manual_close_state_check(
281 p_document_id => p_action_ctl_rec.document_id
282 , p_document_type => p_action_ctl_rec.document_type
283 , p_action => p_action_ctl_rec.action
284 , p_calling_mode => p_action_ctl_rec.calling_mode
285 , p_line_id => p_action_ctl_rec.line_id
286 , p_shipment_id => p_action_ctl_rec.shipment_id
287 , x_return_status => l_ret_sts
288 );
289
290 IF (l_ret_sts <> 'S')
291 THEN
292 d_progress := 90;
293 l_ret_sts := 'U';
294 d_msg := 'manual_close_state_check not successful';
295 RAISE PO_CORE_S.g_early_return_exc;
296 END IF;
297
298 IF (NOT l_state_check_ok)
299 THEN
300 d_progress := 100;
301 l_ret_sts := 'S';
302 d_msg := 'document state is not valid for action';
303 p_action_ctl_rec.return_code := 'STATE_FAILED';
304 RAISE PO_CORE_S.g_early_return_exc;
305 END IF;
306
307 d_progress := 110;
308 IF (PO_LOG.d_stmt) THEN
309 PO_LOG.stmt(d_module, d_progress, 'State check passed.');
310 END IF;
311
312 l_sub_check_ok := manual_close_submission_check(
313 p_document_id => p_action_ctl_rec.document_id
314 , p_document_type => p_action_ctl_rec.document_type
315 , p_document_subtype => p_action_ctl_rec.document_subtype
316 , p_action => p_action_ctl_rec.action
317 , p_calling_mode => p_action_ctl_rec.calling_mode
318 , p_line_id => p_action_ctl_rec.line_id
319 , p_shipment_id => p_action_ctl_rec.shipment_id
320 , p_origin_doc_id => p_action_ctl_rec.origin_doc_id -- Bug#5462677
321 , x_return_status => l_ret_sts
322 , x_online_report_id => p_action_ctl_rec.online_report_id
323 );
324
325 IF (l_ret_sts <> 'S')
326 THEN
327 d_progress := 120;
328 l_ret_sts := 'U';
329 d_msg := 'manual_close_submission_check not successful';
330 RAISE PO_CORE_S.g_early_return_exc;
331 END IF;
332
333 IF (NOT l_sub_check_ok)
334 THEN
335 d_progress := 130;
336 l_ret_sts := 'S';
337 d_msg := 'document submission check failed';
338 p_action_ctl_rec.return_code := 'SUBMISSION_FAILED';
339 RAISE PO_CORE_S.g_early_return_exc;
340 END IF;
341
342 d_progress := 140;
343 IF (PO_LOG.d_stmt) THEN
344 PO_LOG.stmt(d_module, d_progress, 'Submission check passed.');
345 END IF;
346
347
348 d_progress := 200;
349
350 -- From this point forward, need to rollback on early return or exception
351 -- PO_DOCUMENT_ACTION_PVT.do_action, which normally handles rolling back for other actions,
352 -- only rolls back on return_status = 'U'. Since we return 'S' for any encumbrance
353 -- functional errors, we want to undo the following AP INVOICE OPEN update SQL in those cases
354 l_rollback_flag := TRUE;
355
356 IF ((p_action_ctl_rec.calling_mode = 'AP')
357 AND (p_action_ctl_rec.action = PO_DOCUMENT_ACTION_PVT.g_doc_action_OPEN_INV))
358 THEN
359
360 d_progress := 210;
361
362 UPDATE po_line_locations poll
363 SET closed_code = 'CLOSED'
364 WHERE poll.line_location_id = p_action_ctl_rec.shipment_id
365 AND poll.closed_code = 'FINALLY CLOSED';
366
367 END IF; -- if p_action_ctl_rec.calling_mode = 'AP' ...
368
369
370 IF ((p_action_ctl_rec.action = PO_DOCUMENT_ACTION_PVT.g_doc_action_FINALLY_CLOSE)
371 OR ((p_action_ctl_rec.calling_mode = 'AP') AND
372 (p_action_ctl_rec.action = PO_DOCUMENT_ACTION_PVT.g_doc_action_OPEN_INV)))
373 THEN
374
375 d_progress := 220;
376
377 handle_close_encumbrance(
378 p_document_id => p_action_ctl_rec.document_id
379 , p_document_type => p_action_ctl_rec.document_type
380 , p_document_subtype => p_action_ctl_rec.document_subtype
381 , p_action => p_action_ctl_rec.action
382 , p_calling_mode => p_action_ctl_rec.calling_mode
383 , p_line_id => p_action_ctl_rec.line_id
384 , p_shipment_id => p_action_ctl_rec.shipment_id
385 , p_origin_doc_id => p_action_ctl_rec.origin_doc_id
386 , p_action_date => p_action_ctl_rec.action_date
387 , p_use_gl_date => p_action_ctl_rec.use_gl_date
388 , x_return_status => l_ret_sts
389 , x_return_code => l_ret_code
390 , x_online_report_id => p_action_ctl_rec.online_report_id
391 , x_enc_flag => l_enc_flag
392 );
393
394 IF (l_ret_sts <> 'S')
395 THEN
396 d_progress := 230;
397 l_ret_sts := 'U';
398 d_msg := 'unexpected error in handle_close_encumbrance';
399 RAISE PO_CORE_S.g_early_return_exc;
400 END IF;
401
402 IF (l_ret_code IS NOT NULL)
403 THEN
404 d_progress := 240;
405 l_ret_sts := 'S';
406 p_action_ctl_rec.return_code := l_ret_code;
407 d_msg := 'encumbrance handling not fully successful';
408 RAISE PO_CORE_S.g_early_return_exc;
409 END IF;
410
411 END IF; -- if p_action = FINALLY CLOSE or ...
412
413 d_progress := 250;
414
415 manual_update_closed_status(
416 p_document_id => p_action_ctl_rec.document_id
417 , p_document_type => p_action_ctl_rec.document_type
418 , p_document_subtype => p_action_ctl_rec.document_subtype
419 , p_action => p_action_ctl_rec.action
420 , p_calling_mode => p_action_ctl_rec.calling_mode
421 , p_line_id => p_action_ctl_rec.line_id
422 , p_shipment_id => p_action_ctl_rec.shipment_id
423 , p_user_id => l_user_id
424 , p_login_id => l_login_id
425 , p_employee_id => l_emp_id
426 , p_reason => p_action_ctl_rec.note
427 , p_enc_flag => l_enc_flag
428 , x_return_status => l_ret_sts
429 );
430
431 IF (l_ret_sts <> 'S')
432 THEN
433 d_progress := 260;
434 l_ret_sts := 'U';
435 d_msg := 'unexpected error in updating closed status';
436 RAISE PO_CORE_S.g_early_return_exc;
437 END IF;
438
439 d_progress := 270;
440
441 rollup_close_state(
442 p_document_id => p_action_ctl_rec.document_id
443 , p_document_type => p_action_ctl_rec.document_type
444 , p_document_subtype => p_action_ctl_rec.document_subtype
445 , p_action => p_action_ctl_rec.action
446 , p_line_id => p_action_ctl_rec.line_id
447 , p_shipment_id => p_action_ctl_rec.shipment_id
448 , p_user_id => l_user_id
449 , p_login_id => l_login_id
450 , p_employee_id => l_emp_id
451 , p_reason => p_action_ctl_rec.note
452 , p_action_date => SYSDATE
453 , p_calling_mode => p_action_ctl_rec.calling_mode
454 , x_return_status => l_ret_sts
455 );
456
457 IF (l_ret_sts <> 'S')
458 THEN
459 d_progress := 280;
460 l_ret_sts := 'U';
461 d_msg := 'unexpected error in rolling up closed status';
462 RAISE PO_CORE_S.g_early_return_exc;
463 END IF;
464
465 d_progress := 290;
466
467 IF ((p_action_ctl_rec.document_type <> 'PA') AND
468 (p_action_ctl_rec.action NOT IN (PO_DOCUMENT_ACTION_PVT.g_doc_action_CLOSE_INV,
469 PO_DOCUMENT_ACTION_PVT.g_doc_action_OPEN_INV)))
470 THEN
471
472 d_progress := 300;
473
474 handle_manual_close_supply(
475 p_document_id => p_action_ctl_rec.document_id
476 , p_document_type => p_action_ctl_rec.document_type
477 , p_action => p_action_ctl_rec.action
478 , p_line_id => p_action_ctl_rec.line_id
479 , p_shipment_id => p_action_ctl_rec.shipment_id
480 , x_return_status => l_ret_sts
481 );
482
483 IF (l_ret_sts <> 'S')
484 THEN
485 d_progress := 310;
486 l_ret_sts := 'U';
487 d_msg := 'unexpected error in handling mtl supply';
488 RAISE PO_CORE_S.g_early_return_exc;
489 END IF;
490
491 END IF; -- p_document_type <> 'PA' and ...
492
493 p_action_ctl_rec.return_code := NULL;
494 l_ret_sts := 'S';
495
496 EXCEPTION
497 WHEN PO_CORE_S.g_early_return_exc THEN
498 IF (l_ret_sts = 'S') THEN
499 IF (PO_LOG.d_stmt) THEN
500 PO_LOG.stmt(d_module, d_progress, d_msg);
501 END IF;
502 ELSIF (l_ret_sts = 'U') THEN
503 IF (PO_LOG.d_exc) THEN
504 PO_LOG.exc(d_module, d_progress, d_msg);
505 END IF;
506 PO_DOCUMENT_ACTION_PVT.error_msg_append(d_module, d_progress, d_msg);
507 END IF;
508
509 IF (l_rollback_flag) THEN
510 ROLLBACK TO DA_MANUAL_CLOSE_SP;
511 END IF;
512 END;
513
514 -- <Bug 4118145: Issue 7>: Return l_ret_sts instead of a hardcoded 'S'.
515 p_action_ctl_rec.return_status := l_ret_sts;
516
517 IF (PO_LOG.d_proc) THEN
518 PO_LOG.proc_end(d_module, 'p_action_ctl_rec.return_status', p_action_ctl_rec.return_status);
519 PO_LOG.proc_end(d_module, 'p_action_ctl_rec.return_code', p_action_ctl_rec.return_code);
520 PO_LOG.proc_end(d_module, 'p_action_ctl_rec.online_report_id', p_action_ctl_rec.online_report_id);
521 END IF;
522
523 RETURN;
524
525 EXCEPTION
526 WHEN others THEN
527 p_action_ctl_rec.return_status := 'U';
528
529 PO_DOCUMENT_ACTION_PVT.error_msg_append(d_module, d_progress, SQLCODE, SQLERRM);
530
531 IF (PO_LOG.d_exc) THEN
532 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
533 PO_LOG.proc_end(d_module, 'p_action_ctl_rec.return_status', p_action_ctl_rec.return_status);
534 PO_LOG.proc_return(d_module, FALSE);
535 PO_LOG.proc_end(d_module);
536 END IF;
537
538 IF (l_rollback_flag) THEN
539 ROLLBACK TO DA_MANUAL_CLOSE_SP;
540 END IF;
541
542 RETURN;
543
544 END manual_close_po;
545
546 ------------------------------------------------------------------------------
547 --Start of Comments
548 --Name: auto_close_po
549 --Pre-reqs:
550 -- Document is locked.
551 -- Org context is set to that of document.
552 --Modifies:
553 -- None, directly.
554 --Locks:
555 -- None.
556 --Function:
557 -- This procedure handles the logic for automatically opening or closing
558 -- a document entity based on the quantity/amount received/billed.
559 -- The logic is:
560 -- 1. For purchase agreements, immediately return successfully.
561 -- 2. get user_id, login_id, and employee_id
562 -- 3. do a document state check, e.g. has proper authorization status
563 -- 4. update shipment closed statuses based on quantities/amounts
564 -- 5. rollup closed status changes as necessary to higher levels
565 -- this also handles the action history
566 -- 6. handle supply
567 --Replaces:
568 -- This method covers pocupdate_close in pocup.lpc.
569 --Parameters:
570 --IN:
571 -- p_action_ctl_rec
572 -- Record containing all necessary parameters for close action.
573 --OUT:
574 -- p_action_ctl_rec
575 -- Record contains variables that record output values depending
576 -- on the action. All actions will populate at least a return_status.
577 --End of Comments
578 -------------------------------------------------------------------------------
579 PROCEDURE auto_close_po(
580 p_action_ctl_rec IN OUT NOCOPY PO_DOCUMENT_ACTION_PVT.doc_action_call_rec_type
581 )
582 IS
583
584 d_module VARCHAR2(70) := 'po.plsql.PO_DOCUMENT_ACTION_CLOSE.auto_close_po';
585 d_progress NUMBER;
586 d_msg VARCHAR2(200);
587
588 l_ret_sts VARCHAR2(1);
589
590 l_user_id NUMBER;
591 l_login_id NUMBER;
592
593 l_emp_flag BOOLEAN;
594 l_emp_id NUMBER;
595
596 l_state_check_ok BOOLEAN;
597 l_reason VARCHAR2(256);
598
599 BEGIN
600
601 d_progress := 0;
602 IF (PO_LOG.d_proc) THEN
603 PO_LOG.proc_begin(d_module);
604 PO_LOG.proc_begin(d_module, 'p_action_ctl_rec.document_id', p_action_ctl_rec.document_id);
605 PO_LOG.proc_begin(d_module, 'p_action_ctl_rec.document_type', p_action_ctl_rec.document_type);
606 PO_LOG.proc_begin(d_module, 'p_action_ctl_rec.document_subtype', p_action_ctl_rec.document_subtype);
607 PO_LOG.proc_begin(d_module, 'p_action_ctl_rec.line_id', p_action_ctl_rec.line_id);
608 PO_LOG.proc_begin(d_module, 'p_action_ctl_rec.shipment_id', p_action_ctl_rec.shipment_id);
609 PO_LOG.proc_begin(d_module, 'p_action_ctl_rec.action', p_action_ctl_rec.action);
610 PO_LOG.proc_begin(d_module, 'p_action_ctl_rec.called_from_conc', p_action_ctl_rec.called_from_conc);
611 PO_LOG.proc_begin(d_module, 'p_action_ctl_rec.calling_mode', p_action_ctl_rec.calling_mode);
612 END IF;
613
614 d_progress := 3;
615 l_reason := FND_MESSAGE.GET_STRING('PO', 'PO_UPDATE_CLOSE_ROLLUP');
616
617 BEGIN
618
619 d_progress := 5;
620
621 IF (p_action_ctl_rec.document_type = 'PA')
622 THEN
623 d_progress := 7;
624 l_ret_sts := 'S';
625 d_msg := 'do nothing for a PA';
626 RAISE PO_CORE_S.g_early_return_exc;
627 END IF;
628
629 d_progress := 10;
630
631 l_user_id := FND_GLOBAL.USER_ID;
632
633 IF (l_user_id = -1)
634 THEN
635 d_progress := 20;
636 l_ret_sts := 'U';
637 d_msg := 'user id not found';
638 RAISE PO_CORE_S.g_early_return_exc;
639 END IF;
640
641 d_progress := 30;
642
643 IF (p_action_ctl_rec.called_from_conc)
644 THEN
645 l_login_id := FND_GLOBAL.CONC_LOGIN_ID;
646 ELSE
647 l_login_id := FND_GLOBAL.LOGIN_ID;
648 END IF;
649
650 d_progress := 40;
651
652
653 -- <Bug 4118145: Issue 4>: From approval workflow,
654 -- login_id can be -1; this is now allowed.
655
656 IF (PO_LOG.d_stmt) THEN
657 PO_LOG.stmt(d_module, d_progress, 'l_login_id', l_login_id);
658 PO_LOG.stmt(d_module, d_progress, 'l_user_id', l_user_id);
659 END IF;
660
661 d_progress := 50;
662
663 PO_DOCUMENT_ACTION_UTIL.get_employee_id(
664 p_user_id => l_user_id
665 , x_return_status => l_ret_sts
666 , x_employee_flag => l_emp_flag
667 , x_employee_id => l_emp_id
668 );
669
670 IF (l_ret_sts <> 'S')
671 THEN
672 d_progress := 60;
673 l_ret_sts := 'U';
674 d_msg := 'get_employee_id not successful';
675 RAISE PO_CORE_S.g_early_return_exc;
676 END IF;
677
678 IF (NOT l_emp_flag)
679 THEN
680
681 d_progress := 70;
682 l_emp_id := NULL;
683
684 IF (PO_LOG.d_stmt) THEN
685 PO_LOG.stmt(d_module, d_progress, 'user is not an employee');
686 END IF;
687
688 END IF;
689
690 d_progress := 80;
691
692 l_state_check_ok := auto_close_state_check(
693 p_document_id => p_action_ctl_rec.document_id
694 , p_document_type => p_action_ctl_rec.document_type
695 , p_line_id => p_action_ctl_rec.line_id
696 , p_shipment_id => p_action_ctl_rec.shipment_id
697 , x_return_status => l_ret_sts
698 );
699
700 IF (l_ret_sts <> 'S')
701 THEN
702 d_progress := 90;
703 l_ret_sts := 'U';
704 d_msg := 'auto_close_state_check not successful';
705 RAISE PO_CORE_S.g_early_return_exc;
706 END IF;
707
708 IF (NOT l_state_check_ok)
709 THEN
710 d_progress := 100;
711 l_ret_sts := 'S';
712 d_msg := 'document state is not valid for action';
713 p_action_ctl_rec.return_code := 'STATE_FAILED';
714 RAISE PO_CORE_S.g_early_return_exc;
715 END IF;
716
717 d_progress := 110;
718 IF (PO_LOG.d_stmt) THEN
719 PO_LOG.stmt(d_module, d_progress, 'State check passed.');
720 END IF;
721
722 auto_update_closed_status(
723 p_document_id => p_action_ctl_rec.document_id
724 , p_document_type => p_action_ctl_rec.document_type
725 , p_calling_mode => p_action_ctl_rec.calling_mode
726 , p_line_id => p_action_ctl_rec.line_id
727 , p_shipment_id => p_action_ctl_rec.shipment_id
728 , p_user_id => l_user_id --bug4964600
729 , p_login_id => l_login_id --bug4964600
730 , p_employee_id => l_emp_id
731 , p_reason => l_reason
732 , x_return_status => l_ret_sts
733 );
734
735 IF (l_ret_sts <> 'S')
736 THEN
737 d_progress := 120;
738 l_ret_sts := 'U';
739 d_msg := 'unexpected error in updating closed status';
740 RAISE PO_CORE_S.g_early_return_exc;
741 END IF;
742
743 d_progress := 130;
744
745 rollup_close_state(
746 p_document_id => p_action_ctl_rec.document_id
747 , p_document_type => p_action_ctl_rec.document_type
748 , p_document_subtype => p_action_ctl_rec.document_subtype
749 , p_action => p_action_ctl_rec.action
750 , p_line_id => p_action_ctl_rec.line_id
751 , p_shipment_id => p_action_ctl_rec.shipment_id
752 , p_user_id => l_user_id
753 , p_login_id => l_login_id
754 , p_employee_id => l_emp_id
755 , p_reason => l_reason
756 , p_action_date => SYSDATE
757 , p_calling_mode => p_action_ctl_rec.calling_mode
758 , x_return_status => l_ret_sts
759 );
760
761 IF (l_ret_sts <> 'S')
762 THEN
763 d_progress := 140;
764 l_ret_sts := 'U';
765 d_msg := 'unexpected error in rolling up closed status';
766 RAISE PO_CORE_S.g_early_return_exc;
767 END IF;
768
769
770 IF (p_action_ctl_rec.calling_mode <> 'AP')
771 THEN
772
773 d_progress := 150;
774
775 handle_auto_close_supply(
776 p_document_id => p_action_ctl_rec.document_id
777 , p_document_type => p_action_ctl_rec.document_type
778 , p_line_id => p_action_ctl_rec.line_id
779 , p_shipment_id => p_action_ctl_rec.shipment_id
780 , x_return_status => l_ret_sts
781 );
782
783 IF (l_ret_sts <> 'S')
784 THEN
785 d_progress := 160;
786 l_ret_sts := 'U';
787 d_msg := 'unexpected error in handling mtl supply';
788 RAISE PO_CORE_S.g_early_return_exc;
789 END IF;
790
791 END IF; -- if p_action_ctl_rec.calling_mode <> 'AP'
792
793 p_action_ctl_rec.return_code := NULL;
794 l_ret_sts := 'S';
795
796 EXCEPTION
797 WHEN PO_CORE_S.g_early_return_exc THEN
798 IF (l_ret_sts = 'S') THEN
799 IF (PO_LOG.d_stmt) THEN
800 PO_LOG.stmt(d_module, d_progress, d_msg);
801 END IF;
802 ELSIF (l_ret_sts = 'U') THEN
803 IF (PO_LOG.d_exc) THEN
804 PO_LOG.exc(d_module, d_progress, d_msg);
805 END IF;
806 PO_DOCUMENT_ACTION_PVT.error_msg_append(d_module, d_progress, d_msg);
807 END IF;
808 END;
809
810 p_action_ctl_rec.return_status := l_ret_sts;
811
812 IF (PO_LOG.d_proc) THEN
813 PO_LOG.proc_end(d_module, 'p_action_ctl_rec.return_status', p_action_ctl_rec.return_status);
814 PO_LOG.proc_end(d_module, 'p_action_ctl_rec.return_code', p_action_ctl_rec.return_code);
815 END IF;
816
817 RETURN;
818
819 EXCEPTION
820 WHEN others THEN
821 p_action_ctl_rec.return_status := 'U';
822
823 PO_DOCUMENT_ACTION_PVT.error_msg_append(d_module, d_progress, SQLCODE, SQLERRM);
824
825 IF (PO_LOG.d_exc) THEN
826 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
827 PO_LOG.proc_end(d_module, 'p_action_ctl_rec.return_status', p_action_ctl_rec.return_status);
828 PO_LOG.proc_return(d_module, FALSE);
829 PO_LOG.proc_end(d_module);
830 END IF;
831
832 RETURN;
833
834 END auto_close_po;
835
836
837
838 -- Private methods
839
840 ------------------------------------------------------------------------------
841 --Start of Comments
842 --Name: manual_close_state_check
843 --Pre-reqs:
844 -- Org context is set to that of document.
845 --Modifies:
846 -- None.
847 --Locks:
848 -- None.
849 --Function:
850 -- This function does the document state check for a manual
851 -- close action. Checks authorization status at the header level
852 -- and closed status at the lowest entity id passed in (e.g shipment over
853 -- header).
854 -- The logic is:
855 -- 1. for AP invoice open, check if a level higher than
856 -- the shipment being re-opened is finally closed. If so, state_failed.
857 -- 2. depending on action, build the allowed states record.
858 -- 3. call the doc state check utility method
859 --Replaces:
860 -- This logic is inside of poccstatus in poccs.lpc.
861 --Parameters:
862 --IN:
863 -- p_document_id
864 -- ID of the document's header (e.g. po_release_id, po_header_id, ...)
865 -- p_document_type
866 -- 'RELEASE', 'PO'
867 -- p_action
868 -- A manual close action. Use g_doc_action_<> constant where possible.
869 -- 'INVOICE OPEN', 'INVOICE CLOSE', 'OPEN', 'CLOSE', 'RECEIVE OPEN'
870 -- 'RECEIVE CLOSE', 'FINALLY CLOSE'
871 -- p_calling_mode
872 -- 'PO', 'RCV', or 'AP'
873 -- p_line_id
874 -- If acting on a header, pass NULL
875 -- If acting on a line, pass in the po_line_id of the line.
876 -- If acting on a shipment, pass in the po_line_id of the shipment's line.
877 -- p_shipment_id
878 -- If acting on a header, pass NULL
879 -- If acting on a line, pass NULL
880 -- If acting on a shipment, pass in the line_location_id of the shipment
881 --OUT:
882 -- x_return_status
883 -- 'S': state check had no unexpected errors
884 -- In this case, check return value of function
885 -- 'U': state check failed with unexpected errors
886 -- return value:
887 -- FALSE: Document state check failed
888 -- TRUE: state check was successful
889 --End of Comments
890 -------------------------------------------------------------------------------
891 FUNCTION manual_close_state_check(
892 p_document_id IN NUMBER
893 , p_document_type IN VARCHAR2
894 , p_action IN VARCHAR2
895 , p_calling_mode IN VARCHAR2
896 , p_line_id IN NUMBER
897 , p_shipment_id IN NUMBER
898 , x_return_status OUT NOCOPY VARCHAR2
899 ) RETURN BOOLEAN
900 IS
901
902 d_module VARCHAR2(70) := 'po.plsql.PO_DOCUMENT_ACTION_CLOSE.manual_close_state_check';
903 d_progress NUMBER;
904 d_msg VARCHAR2(200);
905
906 l_allowed_states PO_DOCUMENT_ACTION_UTIL.doc_state_rec_type;
907 l_line_finally_closed NUMBER;
908
909 l_state_check_ok BOOLEAN;
910
911 l_ret_sts VARCHAR2(1);
912 l_ret_val BOOLEAN;
913
914 BEGIN
915
916 d_progress := 0;
917 IF (PO_LOG.d_proc) THEN
918 PO_LOG.proc_begin(d_module);
919 PO_LOG.proc_begin(d_module, 'p_document_id', p_document_id);
920 PO_LOG.proc_begin(d_module, 'p_document_type', p_document_type);
921 PO_LOG.proc_begin(d_module, 'p_line_id', p_line_id);
922 PO_LOG.proc_begin(d_module, 'p_shipment_id', p_shipment_id);
923 PO_LOG.proc_begin(d_module, 'p_action', p_action);
924 PO_LOG.proc_begin(d_module, 'p_calling_mode', p_calling_mode);
925 END IF;
926
927 BEGIN
928
929 d_progress := 10;
930
931 IF ((p_calling_mode = 'AP') AND (p_action = PO_DOCUMENT_ACTION_PVT.g_doc_action_OPEN_INV))
932 THEN
933
934 -- bug 3454885
935 -- If a parent entity of the shipment is FINALLY CLOSED,
936 -- then do not update the shipment to CLOSED.
937 -- This would cause an inconsistent state, as the FINAL CLOSE
938 -- action would not be allowed on the parent if this shipment
939 -- was not FINALLY CLOSED.
940
941 -- The COUNT(*) will return either 0 or 1, and no exception handling
942 -- is necessary for NO_DATA_FOUND (0 will be returned).
943
944 --SQL What:
945 -- Determine if any parent entity of the shipment is finally closed.
946 --SQL Why:
947 -- The shipment cannot be re-opened if a parent entity is
948 -- finally closed.
949 --SQL Where:
950 -- Outer joins are used because POs do not have Release headers,
951 -- and only SRs have source shipments.
952
953 d_progress := 20;
954
955 SELECT count(*)
956 INTO l_line_finally_closed
957 FROM po_line_locations_all poll
958 , po_lines_all pol
959 , po_releases_all por
960 , po_line_locations_all ppo_ll
961 WHERE poll.line_location_id = p_shipment_id
962 AND pol.po_line_id = poll.po_line_id
963 AND por.po_release_id(+) = poll.po_release_id
964 AND ppo_ll.line_location_id(+) = poll.source_shipment_id
965 AND ( pol.closed_code = PO_DOCUMENT_ACTION_PVT.g_doc_closed_sts_FIN_CLOSED
966 OR por.closed_code = PO_DOCUMENT_ACTION_PVT.g_doc_closed_sts_FIN_CLOSED
967 OR ppo_ll.closed_code = PO_DOCUMENT_ACTION_PVT.g_doc_closed_sts_FIN_CLOSED
968 )
969 ;
970
971 IF (l_line_finally_closed > 0)
972 THEN
973 d_progress := 30;
974 l_ret_sts := 'S';
975 d_msg := 'line is finally closed for this shipment';
976 l_ret_val := FALSE;
977 RAISE PO_CORE_S.g_early_return_exc;
978 END IF;
979
980 END IF; -- p_calling_mode = 'AP' AND p_action = 'INVOICE OPEN'
981
982 d_progress := 40;
983
984 l_allowed_states.auth_states(1) := PO_DOCUMENT_ACTION_PVT.g_doc_status_APPROVED;
985 l_allowed_states.hold_flag := NULL;
986 l_allowed_states.frozen_flag := NULL;
987 l_allowed_states.fully_reserved_flag := NULL;
988
989 IF (p_action = PO_DOCUMENT_ACTION_PVT.g_doc_action_CLOSE_INV)
990 THEN
991
992 l_allowed_states.closed_states(1) := PO_DOCUMENT_ACTION_PVT.g_doc_closed_sts_OPEN;
993 l_allowed_states.closed_states(2) := PO_DOCUMENT_ACTION_PVT.g_doc_closed_sts_CLOSED_RCV;
994
995 ELSIF (p_action = PO_DOCUMENT_ACTION_PVT.g_doc_action_CLOSE_RCV)
996 THEN
997
998 l_allowed_states.closed_states(1) := PO_DOCUMENT_ACTION_PVT.g_doc_closed_sts_OPEN;
999 l_allowed_states.closed_states(2) := PO_DOCUMENT_ACTION_PVT.g_doc_closed_sts_CLOSED_INV;
1000
1001 ELSIF (p_action = PO_DOCUMENT_ACTION_PVT.g_doc_action_CLOSE)
1002 THEN
1003
1004 l_allowed_states.closed_states(1) := PO_DOCUMENT_ACTION_PVT.g_doc_closed_sts_OPEN;
1005 l_allowed_states.closed_states(2) := PO_DOCUMENT_ACTION_PVT.g_doc_closed_sts_CLOSED_RCV;
1006 l_allowed_states.closed_states(3) := PO_DOCUMENT_ACTION_PVT.g_doc_closed_sts_CLOSED_INV;
1007
1008 ELSIF (p_action = PO_DOCUMENT_ACTION_PVT.g_doc_action_FINALLY_CLOSE)
1009 THEN
1010
1011 l_allowed_states.closed_states(1) := PO_DOCUMENT_ACTION_PVT.g_doc_closed_sts_OPEN;
1012 l_allowed_states.closed_states(2) := PO_DOCUMENT_ACTION_PVT.g_doc_closed_sts_CLOSED_RCV;
1013 l_allowed_states.closed_states(3) := PO_DOCUMENT_ACTION_PVT.g_doc_closed_sts_CLOSED_INV;
1014 l_allowed_states.closed_states(4) := PO_DOCUMENT_ACTION_PVT.g_doc_closed_sts_CLOSED;
1015
1016 ELSIF (p_action = PO_DOCUMENT_ACTION_PVT.g_doc_action_OPEN)
1017 THEN
1018
1019 l_allowed_states.closed_states(1) := PO_DOCUMENT_ACTION_PVT.g_doc_closed_sts_OPEN;
1020 l_allowed_states.closed_states(2) := PO_DOCUMENT_ACTION_PVT.g_doc_closed_sts_CLOSED_RCV;
1021 l_allowed_states.closed_states(3) := PO_DOCUMENT_ACTION_PVT.g_doc_closed_sts_CLOSED_INV;
1022 l_allowed_states.closed_states(4) := PO_DOCUMENT_ACTION_PVT.g_doc_closed_sts_CLOSED;
1023
1024 ELSIF (p_action = PO_DOCUMENT_ACTION_PVT.g_doc_action_OPEN_INV)
1025 THEN
1026
1027 l_allowed_states.closed_states(1) := PO_DOCUMENT_ACTION_PVT.g_doc_closed_sts_OPEN;
1028 l_allowed_states.closed_states(2) := PO_DOCUMENT_ACTION_PVT.g_doc_closed_sts_CLOSED_INV;
1029 l_allowed_states.closed_states(3) := PO_DOCUMENT_ACTION_PVT.g_doc_closed_sts_CLOSED;
1030
1031 -- <Bug 4118145, Issue 9 Start>
1032 -- Allow finally closed for AP re-open finally closed document (AP invoice open)
1033
1034 IF (p_calling_mode = 'AP')
1035 THEN
1036 l_allowed_states.closed_states(4) := PO_DOCUMENT_ACTION_PVT.g_doc_closed_sts_FIN_CLOSED;
1037 END IF;
1038
1039 -- <Bug 4118145, Issue 9 End>
1040
1041 ELSIF (p_action = PO_DOCUMENT_ACTION_PVT.g_doc_action_OPEN_RCV)
1042 THEN
1043
1044 l_allowed_states.closed_states(1) := PO_DOCUMENT_ACTION_PVT.g_doc_closed_sts_OPEN;
1045 l_allowed_states.closed_states(2) := PO_DOCUMENT_ACTION_PVT.g_doc_closed_sts_CLOSED_RCV;
1046 l_allowed_states.closed_states(3) := PO_DOCUMENT_ACTION_PVT.g_doc_closed_sts_CLOSED;
1047
1048 ELSE
1049
1050 d_progress := 50;
1051 l_ret_sts := 'U';
1052 d_msg := 'unsupported close action';
1053 RAISE PO_CORE_S.g_early_return_exc;
1054
1055 END IF; -- p_action = ...
1056
1057 d_progress := 60;
1058
1059 l_state_check_ok := PO_DOCUMENT_ACTION_UTIL.check_doc_state(
1060 p_document_id => p_document_id
1061 , p_document_type => p_document_type
1062 , p_line_id => p_line_id
1063 , p_shipment_id => p_shipment_id
1064 , p_allowed_states => l_allowed_states
1065 , x_return_status => l_ret_sts
1066 );
1067
1068 IF (l_ret_sts <> 'S')
1069 THEN
1070 d_progress := 70;
1071 l_ret_sts := 'U';
1072 d_msg := 'check_doc_state not successful';
1073 RAISE PO_CORE_S.g_early_return_exc;
1074 END IF;
1075
1076 d_progress := 80;
1077 l_ret_val := l_state_check_ok;
1078 l_ret_sts := 'S';
1079
1080 EXCEPTION
1081 WHEN PO_CORE_S.g_early_return_exc THEN
1082 IF (l_ret_sts = 'S') THEN
1083 IF (PO_LOG.d_stmt) THEN
1084 PO_LOG.stmt(d_module, d_progress, d_msg);
1085 END IF;
1086 ELSIF (l_ret_sts = 'U') THEN
1087 IF (PO_LOG.d_exc) THEN
1088 PO_LOG.exc(d_module, d_progress, d_msg);
1089 END IF;
1090 PO_DOCUMENT_ACTION_PVT.error_msg_append(d_module, d_progress, d_msg);
1091 l_ret_val := FALSE;
1092 END IF;
1093 END;
1094
1095 x_return_status := l_ret_sts;
1096
1097 IF (PO_LOG.d_proc) THEN
1098 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
1099 PO_LOG.proc_return(d_module, l_ret_val);
1100 PO_LOG.proc_end(d_module);
1101 END IF;
1102
1103 RETURN l_ret_val;
1104
1105 EXCEPTION
1106 WHEN others THEN
1107 x_return_status := 'U';
1108
1109 PO_DOCUMENT_ACTION_PVT.error_msg_append(d_module, d_progress, SQLCODE, SQLERRM);
1110
1111 IF (PO_LOG.d_exc) THEN
1112 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
1113 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
1114 PO_LOG.proc_return(d_module, FALSE);
1115 PO_LOG.proc_end(d_module);
1116 END IF;
1117
1118 RETURN FALSE;
1119
1120 END manual_close_state_check;
1121
1122 ------------------------------------------------------------------------------
1123 --Start of Comments
1124 --Name: auto_close_state_check
1125 --Pre-reqs:
1126 -- Org context is set to that of document.
1127 --Modifies:
1128 -- None.
1129 --Locks:
1130 -- None.
1131 --Function:
1132 -- This function does the document state check for the auto
1133 -- close action. Checks authorization status at the header level
1134 -- and closed status at the lowest entity id passed in (e.g shipment over
1135 -- header).
1136 -- The logic is:
1137 -- 1. build the allowed states record.
1138 -- 2. call the doc state check utility method
1139 --Replaces:
1140 -- This logic is inside of pocupdate_close in pocup.lpc.
1141 --Parameters:
1142 --IN:
1143 -- p_document_id
1144 -- ID of the document's header (e.g. po_release_id, po_header_id, ...)
1145 -- p_document_type
1146 -- 'RELEASE', 'PO'
1147 -- p_line_id
1148 -- If acting on a header, pass NULL
1149 -- If acting on a line, pass in the po_line_id of the line.
1150 -- If acting on a shipment, pass in the po_line_id of the shipment's line.
1151 -- p_shipment_id
1152 -- If acting on a header, pass NULL
1153 -- If acting on a line, pass NULL
1154 -- If acting on a shipment, pass in the line_location_id of the shipment
1155 --OUT:
1156 -- x_return_status
1157 -- 'S': state check had no unexpected errors
1158 -- In this case, check return value of function
1159 -- 'U': state check failed with unexpected errors
1160 -- return value:
1161 -- FALSE: Document state check failed
1162 -- TRUE: state check was successful
1163 --End of Comments
1164 -------------------------------------------------------------------------------
1165 FUNCTION auto_close_state_check(
1166 p_document_id IN NUMBER
1167 , p_document_type IN VARCHAR2
1168 , p_line_id IN NUMBER
1169 , p_shipment_id IN NUMBER
1170 , x_return_status OUT NOCOPY VARCHAR2
1171 ) RETURN BOOLEAN
1172 IS
1173
1174 d_module VARCHAR2(70) := 'po.plsql.PO_DOCUMENT_ACTION_CLOSE.auto_close_state_check';
1175 d_progress NUMBER;
1176 d_msg VARCHAR2(200);
1177
1178 l_allowed_states PO_DOCUMENT_ACTION_UTIL.doc_state_rec_type;
1179
1180 l_state_check_ok BOOLEAN;
1181
1182 l_ret_sts VARCHAR2(1);
1183 l_ret_val BOOLEAN;
1184
1185 BEGIN
1186
1187 d_progress := 0;
1188 IF (PO_LOG.d_proc) THEN
1189 PO_LOG.proc_begin(d_module);
1190 PO_LOG.proc_begin(d_module, 'p_document_id', p_document_id);
1191 PO_LOG.proc_begin(d_module, 'p_document_type', p_document_type);
1192 PO_LOG.proc_begin(d_module, 'p_line_id', p_line_id);
1193 PO_LOG.proc_begin(d_module, 'p_shipment_id', p_shipment_id);
1194 END IF;
1195
1196 BEGIN
1197
1198 d_progress := 10;
1199
1200 l_allowed_states.auth_states(1) := PO_DOCUMENT_ACTION_PVT.g_doc_status_APPROVED;
1201 l_allowed_states.auth_states(2) := PO_DOCUMENT_ACTION_PVT.g_doc_status_INCOMPLETE;
1202 l_allowed_states.auth_states(3) := PO_DOCUMENT_ACTION_PVT.g_doc_status_INPROCESS;
1203 l_allowed_states.auth_states(4) := PO_DOCUMENT_ACTION_PVT.g_doc_status_REJECTED;
1204 l_allowed_states.auth_states(5) := PO_DOCUMENT_ACTION_PVT.g_doc_status_RETURNED;
1205 l_allowed_states.auth_states(6) := PO_DOCUMENT_ACTION_PVT.g_doc_status_REAPPROVAL;
1206 l_allowed_states.auth_states(7) := PO_DOCUMENT_ACTION_PVT.g_doc_status_PREAPPROVED;
1207 l_allowed_states.auth_states(8) := PO_DOCUMENT_ACTION_PVT.g_doc_status_SENT;
1208
1209 l_allowed_states.hold_flag := NULL;
1210 l_allowed_states.frozen_flag := NULL;
1211 l_allowed_states.fully_reserved_flag := NULL;
1212
1213 l_allowed_states.closed_states(1) := PO_DOCUMENT_ACTION_PVT.g_doc_closed_sts_OPEN;
1214 l_allowed_states.closed_states(2) := PO_DOCUMENT_ACTION_PVT.g_doc_closed_sts_CLOSED_RCV;
1215 l_allowed_states.closed_states(3) := PO_DOCUMENT_ACTION_PVT.g_doc_closed_sts_CLOSED_INV;
1216 l_allowed_states.closed_states(4) := PO_DOCUMENT_ACTION_PVT.g_doc_closed_sts_CLOSED;
1217
1218 d_progress := 20;
1219
1220 l_state_check_ok := PO_DOCUMENT_ACTION_UTIL.check_doc_state(
1221 p_document_id => p_document_id
1222 , p_document_type => p_document_type
1223 , p_line_id => p_line_id
1224 , p_shipment_id => p_shipment_id
1225 , p_allowed_states => l_allowed_states
1226 , x_return_status => l_ret_sts
1227 );
1228
1229 IF (l_ret_sts <> 'S')
1230 THEN
1231 d_progress := 30;
1232 l_ret_sts := 'U';
1233 d_msg := 'check_doc_state not successful';
1234 RAISE PO_CORE_S.g_early_return_exc;
1235 END IF;
1236
1237 d_progress := 40;
1238 l_ret_val := l_state_check_ok;
1239 l_ret_sts := 'S';
1240
1241 EXCEPTION
1242 WHEN PO_CORE_S.g_early_return_exc THEN
1243 IF (l_ret_sts = 'S') THEN
1244 IF (PO_LOG.d_stmt) THEN
1245 PO_LOG.stmt(d_module, d_progress, d_msg);
1246 END IF;
1247 ELSIF (l_ret_sts = 'U') THEN
1248 IF (PO_LOG.d_exc) THEN
1249 PO_LOG.exc(d_module, d_progress, d_msg);
1250 END IF;
1251 PO_DOCUMENT_ACTION_PVT.error_msg_append(d_module, d_progress, d_msg);
1252 l_ret_val := FALSE;
1253 END IF;
1254 END;
1255
1256 x_return_status := l_ret_sts;
1257
1258 IF (PO_LOG.d_proc) THEN
1259 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
1260 PO_LOG.proc_return(d_module, l_ret_val);
1261 PO_LOG.proc_end(d_module);
1262 END IF;
1263
1264 RETURN l_ret_val;
1265
1266 EXCEPTION
1267 WHEN others THEN
1268 x_return_status := 'U';
1269
1270 PO_DOCUMENT_ACTION_PVT.error_msg_append(d_module, d_progress, SQLCODE, SQLERRM);
1271
1272 IF (PO_LOG.d_exc) THEN
1273 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
1274 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
1275 PO_LOG.proc_return(d_module, FALSE);
1276 PO_LOG.proc_end(d_module);
1277 END IF;
1278
1279 RETURN FALSE;
1280
1281 END auto_close_state_check;
1282
1283 ------------------------------------------------------------------------------
1284 --Start of Comments
1285 --Name: manual_close_submission_check
1286 --Pre-reqs:
1287 -- Org context is set to that of document.
1288 --Modifies:
1289 -- None.
1290 --Locks:
1291 -- None.
1292 --Function:
1293 -- This function does the document submission check for a manual
1294 -- close action. Submission checks are only necessary for final close.
1295 -- The logic is:
1296 -- 1. if action is not final close, return successfully.
1297 -- 2. call PO_DOCUMENT_CHECKS_GRP api to run final close submission check.
1298 -- 3. for SPO and releases, run an extra drop ship check
1299 --Replaces:
1300 -- This logic merges code from manual_close in POXPOACB.pls
1301 -- and poccstatus in poccs.lpc.
1302 --Parameters:
1303 --IN:
1304 -- p_document_id
1305 -- ID of the document's header (e.g. po_release_id, po_header_id, ...)
1306 -- p_document_type
1307 -- 'RELEASE', 'PO'
1308 -- p_document_subtype
1309 -- 'RELEASE': 'BLANKET', 'SCHEDULED'
1310 -- 'PO': 'PLANNED', 'STANDARD'
1311 -- p_action
1312 -- A manual close action. Use g_doc_action_<> constant where possible.
1313 -- 'INVOICE OPEN', 'INVOICE CLOSE', 'OPEN', 'CLOSE', 'RECEIVE OPEN'
1314 -- 'RECEIVE CLOSE', 'FINALLY CLOSE'
1315 -- p_calling_mode
1316 -- 'PO', 'RCV', or 'AP'
1317 -- p_line_id
1318 -- If acting on a header, pass NULL
1319 -- If acting on a line, pass in the po_line_id of the line.
1320 -- If acting on a shipment, pass in the po_line_id of the shipment's line.
1321 -- p_shipment_id
1322 -- If acting on a header, pass NULL
1323 -- If acting on a line, pass NULL
1324 -- If acting on a shipment, pass in the line_location_id of the shipment
1325 --OUT:
1326 -- x_return_status
1327 -- 'S': submission check had no unexpected errors
1328 -- In this case, check return value of function
1329 -- 'U': state check failed with unexpected errors
1330 -- return value:
1331 -- TRUE: Document submission check passed without errors
1332 -- FALSE: submission check caught at least one error
1333 -- x_online_report_id:
1334 -- ID into online_report_text table to get submission check messages
1335 --End of Comments
1336 -------------------------------------------------------------------------------
1337 FUNCTION manual_close_submission_check(
1338 p_document_id IN NUMBER
1339 , p_document_type IN VARCHAR2
1340 , p_document_subtype IN VARCHAR2
1341 , p_action IN VARCHAR2
1342 , p_calling_mode IN VARCHAR2
1343 , p_line_id IN NUMBER
1344 , p_shipment_id IN NUMBER
1345 , p_origin_doc_id IN NUMBER -- Bug#5462677
1346 , x_return_status OUT NOCOPY VARCHAR2
1347 , x_online_report_id OUT NOCOPY NUMBER
1348 ) RETURN BOOLEAN
1349 IS
1350
1351 d_module VARCHAR2(70) := 'po.plsql.PO_DOCUMENT_ACTION_CLOSE.manual_close_submission_check';
1352 d_progress NUMBER;
1353 d_msg VARCHAR2(200);
1354
1355 l_ret_val BOOLEAN;
1356 l_ret_sts VARCHAR2(1);
1357
1358 l_sub_check_sts VARCHAR2(1);
1359 l_document_level VARCHAR2(25);
1360 l_document_level_id NUMBER;
1361 l_msg_data VARCHAR2(2000);
1362 l_doc_check_error_rec DOC_CHECK_RETURN_TYPE;
1363
1364 l_dropship_chk_succ BOOLEAN;
1365 l_dropship_chk_retcode VARCHAR2(25);
1366
1367 BEGIN
1368
1369 d_progress := 0;
1370 IF (PO_LOG.d_proc) THEN
1371 PO_LOG.proc_begin(d_module);
1372 PO_LOG.proc_begin(d_module, 'p_document_id', p_document_id);
1373 PO_LOG.proc_begin(d_module, 'p_document_type', p_document_type);
1374 PO_LOG.proc_begin(d_module, 'p_document_subtype', p_document_subtype);
1375 PO_LOG.proc_begin(d_module, 'p_shipment_id', p_shipment_id);
1376 PO_LOG.proc_begin(d_module, 'p_action', p_action);
1377 PO_LOG.proc_begin(d_module, 'p_calling_mode', p_calling_mode);
1378 END IF;
1379
1380 BEGIN
1381
1382 d_progress := 10;
1383
1384 IF (p_action <> PO_DOCUMENT_ACTION_PVT.g_doc_action_FINALLY_CLOSE)
1385 THEN
1386
1387 d_progress := 20;
1388 x_online_report_id := NULL;
1389 l_ret_sts := 'S';
1390 l_ret_val := TRUE;
1391 d_msg := 'No submission checks needed.';
1392 RAISE PO_CORE_S.g_early_return_exc;
1393
1394 END IF;
1395
1396 d_progress := 100;
1397
1398 IF (p_shipment_id IS NOT NULL)
1399 THEN
1400
1401 l_document_level := PO_DOCUMENT_CHECKS_GRP.g_document_level_SHIPMENT;
1402 l_document_level_id := p_shipment_id;
1403
1404 ELSIF (p_line_id IS NOT NULL)
1405 THEN
1406
1407 l_document_level := PO_DOCUMENT_CHECKS_GRP.g_document_level_LINE;
1408 l_document_level_id := p_line_id;
1409
1410 ELSE
1411
1412 l_document_level := PO_DOCUMENT_CHECKS_GRP.g_document_level_HEADER;
1413 l_document_level_id := p_document_id;
1414
1415 END IF;
1416
1417 d_progress := 110;
1418
1419 IF (PO_LOG.d_stmt) THEN
1420 PO_LOG.stmt(d_module, d_progress, 'l_document_level', l_document_level);
1421 PO_LOG.stmt(d_module, d_progress, 'l_document_level_id', l_document_level_id);
1422 END IF;
1423
1424 PO_DOCUMENT_CHECKS_GRP.po_submission_check(
1425 p_api_version => 1.0
1426 , p_action_requested => PO_DOCUMENT_CHECKS_GRP.g_action_FINAL_CLOSE_CHECK
1427 , p_document_type => p_document_type
1428 , p_document_subtype => p_document_subtype
1429 , p_document_level => l_document_level
1430 , p_document_level_id => l_document_level_id
1431 , p_org_id => NULL
1432 , p_requested_changes => NULL
1433 , p_check_asl => FALSE
1434 , p_origin_doc_id => p_origin_doc_id --Bug#5462677
1435 , x_return_status => l_ret_sts
1436 , x_sub_check_status => l_sub_check_sts
1437 , x_msg_data => l_msg_data
1438 , x_online_report_id => x_online_report_id
1439 , x_doc_check_error_record => l_doc_check_error_rec
1440 );
1441
1442 IF (l_ret_sts <> FND_API.G_RET_STS_SUCCESS)
1443 THEN
1444 d_progress := 120;
1445 l_ret_sts := 'U';
1446 d_msg := 'unexpected error in po_submission_check';
1447 RAISE PO_CORE_S.g_early_return_exc;
1448 END IF;
1449
1450 IF (l_sub_check_sts <> FND_API.G_RET_STS_SUCCESS)
1451 THEN
1452 d_progress := 130;
1453 l_ret_sts := 'S';
1454 l_ret_val := FALSE;
1455 d_msg := 'submission check failed';
1456 RAISE PO_CORE_S.g_early_return_exc;
1457 END IF;
1458
1459 d_progress := 140;
1460
1461 IF ((p_document_type = 'RELEASE')
1462 OR (p_document_type = 'PO' AND p_document_subtype = 'STANDARD'))
1463 THEN
1464
1465 d_progress := 150;
1466
1467 l_dropship_chk_succ := PO_CONTROL_CHECKS.chk_drop_ship(
1468 p_doctyp => p_document_type
1469 , p_docid => p_document_id
1470 , p_lineid => p_line_id
1471 , p_shipid => p_shipment_id
1472 , p_reportid => x_online_report_id
1473 , p_action => 'FINALLY CLOSE'
1474 , p_return_code => l_dropship_chk_retcode
1475 );
1476
1477 IF (NOT l_dropship_chk_succ)
1478 THEN
1479 d_progress := 160;
1480 l_ret_sts := 'U';
1481 d_msg := 'unexpected error in chk_drop_ship';
1482 RAISE PO_CORE_S.g_early_return_exc;
1483 END IF;
1484
1485 IF (l_dropship_chk_retcode = 'SUBMISSION_FAILED')
1486 THEN
1487 d_progress := 170;
1488 l_ret_sts := 'S';
1489 l_ret_val := FALSE;
1490 d_msg := 'dropship check failed';
1491 RAISE PO_CORE_S.g_early_return_exc;
1492 END IF;
1493
1494 END IF; -- if p_document_type = 'RELEASE' ...
1495
1496 d_progress := 180;
1497
1498 l_ret_sts := 'S';
1499 l_ret_val := TRUE;
1500
1501 EXCEPTION
1502 WHEN PO_CORE_S.g_early_return_exc THEN
1503 IF (l_ret_sts = 'S') THEN
1504 IF (PO_LOG.d_stmt) THEN
1505 PO_LOG.stmt(d_module, d_progress, d_msg);
1506 END IF;
1507 ELSIF (l_ret_sts = 'U') THEN
1508 IF (PO_LOG.d_exc) THEN
1509 PO_LOG.exc(d_module, d_progress, d_msg);
1510 END IF;
1511 PO_DOCUMENT_ACTION_PVT.error_msg_append(d_module, d_progress, d_msg);
1512 l_ret_val := FALSE;
1513 END IF;
1514 END;
1515
1516 x_return_status := l_ret_sts;
1517
1518 IF (PO_LOG.d_proc) THEN
1519 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
1520 PO_LOG.proc_end(d_module, 'x_online_report_id', x_online_report_id);
1521 PO_LOG.proc_return(d_module, l_ret_val);
1522 PO_LOG.proc_end(d_module);
1523 END IF;
1524
1525 RETURN l_ret_val;
1526
1527 EXCEPTION
1528 WHEN others THEN
1529 x_return_status := 'U';
1530
1531 PO_DOCUMENT_ACTION_PVT.error_msg_append(d_module, d_progress, SQLCODE, SQLERRM);
1532
1533 IF (PO_LOG.d_exc) THEN
1534 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
1535 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
1536 PO_LOG.proc_return(d_module, FALSE);
1537 PO_LOG.proc_end(d_module);
1538 END IF;
1539
1540 RETURN FALSE;
1541
1542 END manual_close_submission_check;
1543
1544
1545 ------------------------------------------------------------------------------
1546 --Start of Comments
1547 --Name: handle_close_encumbrance
1548 --Pre-reqs:
1549 -- Org context is set to that of document.
1550 --Modifies:
1551 -- None, directly.
1552 --Locks:
1553 -- None, directly.
1554 --Function:
1555 -- This procedure handles encumbrance for the manual close actions
1556 -- final close and AP invoice open.
1557 -- The logic is:
1558 -- 1. determine if an encumbrance situation applies. return successfully
1559 -- if not encumbrance action is required.
1560 -- 2. call appropriate PO_DOCUMENT_FUNDS_PVT api
1561 -- 3. wrap the return values of the API into our return values
1562 --Replaces:
1563 -- This logic is inside of poccstatus in poccs.lpc.
1564 --Parameters:
1565 --IN:
1566 -- p_document_id
1567 -- ID of the document's header (e.g. po_release_id, po_header_id, ...)
1568 -- p_document_type
1569 -- 'RELEASE', 'PO'
1570 -- p_action
1571 -- 'INVOICE OPEN', 'FINALLY CLOSE'
1572 -- p_calling_mode
1573 -- 'FINALLY CLOSE': 'PO', 'RCV', or 'AP'
1574 -- 'INVOICE OPEN': 'AP'
1575 -- p_line_id
1576 -- If acting on a header, pass NULL
1577 -- If acting on a line, pass in the po_line_id of the line.
1578 -- If acting on a shipment, pass in the po_line_id of the shipment's line.
1579 -- p_shipment_id
1580 -- If acting on a header, pass NULL
1581 -- If acting on a line, pass NULL
1582 -- If acting on a shipment, pass in the line_location_id of the shipment
1583 -- p_origin_doc_id
1584 -- For calling mode = 'AP', the id of the invoice
1585 -- Required for encumbrance/JFMIP purposes
1586 -- p_action_date
1587 -- passed to encumbrance APIs
1588 -- p_use_gl_date
1589 -- 'Y' or 'N'; passed to encumbrance APIs
1590 --OUT:
1591 -- x_return_status
1592 -- 'S': procedure had no unexpected errors
1593 -- In this case, check x_return_code of procedure
1594 -- 'U': procedure failed with unexpected errors
1595 -- x_return_code:
1596 -- 'P', 'F', 'T': Encumbrance call not fully successful.
1597 -- NULL: encumbrance action was fully successful
1598 -- x_online_report_id
1599 -- ID into online_report_text table to get encumbrance error messages
1600 -- x_enc_flag
1601 -- TRUE: encumbrance action was required
1602 -- FALSE: encumbrance action was not required
1603 --End of Comments
1604 -------------------------------------------------------------------------------
1605 PROCEDURE handle_close_encumbrance(
1606 p_document_id IN NUMBER
1607 , p_document_type IN VARCHAR2
1608 , p_document_subtype IN VARCHAR2
1609 , p_action IN VARCHAR2
1610 , p_calling_mode IN VARCHAR2
1611 , p_line_id IN NUMBER
1612 , p_shipment_id IN NUMBER
1613 , p_origin_doc_id IN NUMBER
1614 , p_action_date IN DATE
1615 , p_use_gl_date IN VARCHAR2
1616 , x_return_status OUT NOCOPY VARCHAR2
1617 , x_return_code OUT NOCOPY VARCHAR2
1618 , x_online_report_id OUT NOCOPY NUMBER
1619 , x_enc_flag OUT NOCOPY BOOLEAN
1620 )
1621 IS
1622
1623 d_module VARCHAR2(70) := 'po.plsql.PO_DOCUMENT_ACTION_CLOSE.handle_close_encumbrance';
1624 d_progress NUMBER;
1625 d_msg VARCHAR2(200);
1626
1627 l_ret_sts VARCHAR2(1);
1628
1629 l_enc_ret_code VARCHAR2(10);
1630 l_document_level VARCHAR2(25);
1631 l_document_level_id NUMBER;
1632
1633 l_enc_flag BOOLEAN;
1634 l_bpa_enc_required VARCHAR2(1);
1635
1636
1637
1638 BEGIN
1639
1640 d_progress := 0;
1641 IF (PO_LOG.d_proc) THEN
1642 PO_LOG.proc_begin(d_module);
1643 PO_LOG.proc_begin(d_module, 'p_document_id', p_document_id);
1644 PO_LOG.proc_begin(d_module, 'p_document_type', p_document_type);
1645 PO_LOG.proc_begin(d_module, 'p_document_subtype', p_document_subtype);
1646 PO_LOG.proc_begin(d_module, 'p_line_id', p_line_id);
1647 PO_LOG.proc_begin(d_module, 'p_shipment_id', p_shipment_id);
1648 PO_LOG.proc_begin(d_module, 'p_action', p_action);
1649 PO_LOG.proc_begin(d_module, 'p_calling_mode', p_calling_mode);
1650 PO_LOG.proc_begin(d_module, 'p_action_date', p_action_date);
1651 PO_LOG.proc_begin(d_module, 'p_origin_doc_id', p_origin_doc_id);
1652 END IF;
1653
1654 BEGIN
1655
1656 d_progress := 10;
1657
1658 l_enc_flag := PO_CORE_S.is_encumbrance_on(
1659 p_doc_type => p_document_type
1660 , p_org_id => NULL
1661 );
1662
1663 IF ((l_enc_flag) AND (p_document_type = 'PA') AND (p_document_subtype = 'CONTRACT'))
1664 THEN
1665
1666 d_progress := 20;
1667 l_enc_flag := FALSE;
1668
1669 ELSIF ((l_enc_flag) AND (p_document_type = 'PA') AND (p_document_subtype = 'BLANKET'))
1670 THEN
1671
1672 IF ((p_shipment_id IS NOT NULL) OR (p_line_id IS NOT NULL))
1673 THEN
1674
1675 d_progress := 30;
1676
1677 IF (PO_LOG.d_stmt) THEN
1678 PO_LOG.stmt(d_module, d_progress, 'Trying to finally close/open invoice a BPA, but not at header level.');
1679 END IF;
1680
1681 l_enc_flag := FALSE;
1682
1683 ELSE
1684
1685 d_progress := 40;
1686
1687 SELECT NVL(poh.encumbrance_required_flag, 'N')
1688 INTO l_bpa_enc_required
1689 FROM po_headers_all poh
1690 WHERE poh.po_header_id = p_document_id;
1691
1692 d_progress := 50;
1693 IF (PO_LOG.d_stmt) THEN
1694 PO_LOG.stmt(d_module, d_progress, 'l_bpa_enc_required', l_bpa_enc_required);
1695 END IF;
1696
1697 IF (l_bpa_enc_required = 'Y')
1698 THEN
1699 l_enc_flag := TRUE;
1700 ELSE
1701 l_enc_flag := FALSE;
1702 END IF; -- l_bpa_enc_required = 'Y'
1703
1704 END IF; -- if p_shipment_id is not null or...
1705
1706 END IF; -- if l_enc_flag and document_type = 'PA' ...
1707
1708 d_progress := 60;
1709 IF (PO_LOG.d_stmt) THEN
1710 PO_LOG.stmt(d_module, d_progress, 'l_enc_flag', l_enc_flag);
1711 END IF;
1712
1713 IF (NOT l_enc_flag) THEN
1714 d_progress := 70;
1715 d_msg := 'encumbrance action not required';
1716 x_return_code := NULL;
1717 x_online_report_id := NULL;
1718 l_ret_sts := 'S';
1719 RAISE PO_CORE_S.g_early_return_exc;
1720 END IF;
1721
1722 d_progress := 80;
1723
1724 IF (p_shipment_id IS NOT NULL)
1725 THEN
1726
1727 l_document_level := PO_DOCUMENT_FUNDS_PVT.g_doc_level_SHIPMENT;
1728 l_document_level_id := p_shipment_id;
1729
1730 ELSIF (p_line_id IS NOT NULL)
1731 THEN
1732
1733 l_document_level := PO_DOCUMENT_FUNDS_PVT.g_doc_level_LINE;
1734 l_document_level_id := p_line_id;
1735
1736 ELSE
1737
1738 l_document_level := PO_DOCUMENT_FUNDS_PVT.g_doc_level_HEADER;
1739 l_document_level_id := p_document_id;
1740
1741 END IF; -- if p_shipment_id is not null
1742
1743
1744 IF (p_action = PO_DOCUMENT_ACTION_PVT.g_doc_action_FINALLY_CLOSE)
1745 THEN
1746
1747 d_progress := 90;
1748
1749 PO_DOCUMENT_FUNDS_PVT.do_final_close(
1750 x_return_status => l_ret_sts
1751 , p_doc_type => p_document_type
1752 , p_doc_subtype => p_document_subtype
1753 , p_doc_level => l_document_level
1754 , p_doc_level_id => l_document_level_id
1755 , p_use_enc_gt_flag => PO_DOCUMENT_FUNDS_PVT.g_parameter_NO
1756 , p_use_gl_date => p_use_gl_date
1757 , p_override_date => p_action_date
1758 , p_invoice_id => p_origin_doc_id
1759 , x_po_return_code => l_enc_ret_code
1760 , x_online_report_id => x_online_report_id
1761 );
1762
1763 ELSE /* INVOICE OPEN */
1764
1765 d_progress := 100;
1766
1767 PO_DOCUMENT_FUNDS_PVT.undo_final_close(
1768 x_return_status => l_ret_sts
1769 , p_doc_type => p_document_type
1770 , p_doc_subtype => p_document_subtype
1771 , p_doc_level => l_document_level
1772 , p_doc_level_id => l_document_level_id
1773 , p_use_enc_gt_flag => PO_DOCUMENT_FUNDS_PVT.g_parameter_NO
1774 , p_override_funds => PO_DOCUMENT_FUNDS_PVT.g_parameter_USE_PROFILE
1775 , p_use_gl_date => p_use_gl_date
1776 , p_override_date => p_action_date
1777 , p_invoice_id => p_origin_doc_id
1778 , x_po_return_code => l_enc_ret_code
1779 , x_online_report_id => x_online_report_id
1780 );
1781
1782 END IF; -- if p_action = FINALLY CLOSE
1783
1784 IF (l_ret_sts <> FND_API.g_ret_sts_success)
1785 THEN
1786
1787 d_progress := 110;
1788 l_ret_sts := 'U';
1789 d_msg := 'unexpected error in encumbrance action';
1790 RAISE PO_CORE_S.g_early_return_exc;
1791
1792 END IF;
1793
1794 d_progress := 120;
1795
1796 IF (PO_LOG.d_stmt) THEN
1797 PO_LOG.stmt(d_module, d_progress, 'l_enc_ret_code', l_enc_ret_code);
1798 PO_LOG.stmt(d_module, d_progress, 'x_online_report_id', x_online_report_id);
1799 END IF;
1800
1801
1802 IF ((l_enc_ret_code = PO_DOCUMENT_FUNDS_PVT.g_return_SUCCESS)
1803 OR (l_enc_ret_code = PO_DOCUMENT_FUNDS_PVT.g_return_WARNING))
1804 THEN
1805
1806 d_progress := 125;
1807 d_msg := 'encumbrance action fully successful';
1808 x_return_code := NULL;
1809
1810 ELSIF (l_enc_ret_code = PO_DOCUMENT_FUNDS_PVT.g_return_PARTIAL)
1811 THEN
1812
1813 d_progress := 130;
1814 d_msg := 'encumbrance action partially successful';
1815 x_return_code := 'P';
1816
1817 ELSIF (l_enc_ret_code = PO_DOCUMENT_FUNDS_PVT.g_return_FAILURE)
1818 THEN
1819
1820 d_progress := 140;
1821 d_msg := 'encumbrance action failure';
1822 x_return_code := 'F';
1823
1824 ELSIF (l_enc_ret_code = PO_DOCUMENT_FUNDS_PVT.g_return_FATAL)
1825 THEN
1826
1827 d_progress := 150;
1828 d_msg := 'encumbrance action fatal';
1829 x_return_code := 'T';
1830
1831 ELSE
1832
1833 d_progress := 160;
1834 l_ret_sts := 'U';
1835 d_msg := 'Bad return code from encumbrance call';
1836 RAISE PO_CORE_S.g_early_return_exc;
1837
1838 END IF; -- if l_enc_ret_code IN (...)
1839
1840 IF (PO_LOG.d_stmt) THEN
1841 PO_LOG.stmt(d_module, d_progress, d_msg);
1842 END IF;
1843
1844 l_ret_sts := 'S';
1845
1846 EXCEPTION
1847 WHEN PO_CORE_S.g_early_return_exc THEN
1848 IF (l_ret_sts = 'S') THEN
1849 IF (PO_LOG.d_stmt) THEN
1850 PO_LOG.stmt(d_module, d_progress, d_msg);
1851 END IF;
1852 x_enc_flag := l_enc_flag;
1853 ELSIF (l_ret_sts = 'U') THEN
1854 IF (PO_LOG.d_exc) THEN
1855 PO_LOG.exc(d_module, d_progress, d_msg);
1856 END IF;
1857 PO_DOCUMENT_ACTION_PVT.error_msg_append(d_module, d_progress, d_msg);
1858 END IF;
1859 END;
1860
1861 x_return_status := l_ret_sts;
1862
1863 IF (PO_LOG.d_proc) THEN
1864 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
1865 PO_LOG.proc_end(d_module, 'x_online_report_id', x_online_report_id);
1866 PO_LOG.proc_end(d_module, 'x_return_code', x_return_code);
1867 PO_LOG.proc_end(d_module);
1868 END IF;
1869
1870 RETURN;
1871
1872 EXCEPTION
1873 WHEN others THEN
1874 x_return_status := 'U';
1875
1876 PO_DOCUMENT_ACTION_PVT.error_msg_append(d_module, d_progress, SQLCODE, SQLERRM);
1877
1878 IF (PO_LOG.d_exc) THEN
1879 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
1880 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
1881 PO_LOG.proc_end(d_module);
1882 END IF;
1883
1884 RETURN;
1885
1886 END handle_close_encumbrance;
1887
1888
1889 PROCEDURE manual_update_closed_status(
1890 p_document_id IN NUMBER
1891 , p_document_type IN VARCHAR2
1892 , p_document_subtype IN VARCHAR2
1893 , p_action IN VARCHAR2
1894 , p_calling_mode IN VARCHAR2
1895 , p_line_id IN NUMBER
1896 , p_shipment_id IN NUMBER
1897 , p_user_id IN NUMBER
1898 , p_login_id IN NUMBER
1899 , p_employee_id IN NUMBER
1900 , p_reason IN VARCHAR2
1901 , p_enc_flag IN BOOLEAN
1902 , x_return_status OUT NOCOPY VARCHAR2
1903 )
1904 IS
1905
1906 d_module VARCHAR2(70) := 'po.plsql.PO_DOCUMENT_ACTION_CLOSE.manual_update_close_status';
1907 d_progress NUMBER;
1908 d_msg VARCHAR2(200);
1909
1910 l_ret_sts VARCHAR2(1);
1911
1912 l_id_tbl g_tbl_number;
1913
1914 BEGIN
1915
1916 d_progress := 0;
1917 IF (PO_LOG.d_proc) THEN
1918 PO_LOG.proc_begin(d_module);
1919 PO_LOG.proc_begin(d_module, 'p_document_id', p_document_id);
1920 PO_LOG.proc_begin(d_module, 'p_document_type', p_document_type);
1921 PO_LOG.proc_begin(d_module, 'p_document_subtype', p_document_subtype);
1922 PO_LOG.proc_begin(d_module, 'p_line_id', p_line_id);
1923 PO_LOG.proc_begin(d_module, 'p_shipment_id', p_shipment_id);
1924 PO_LOG.proc_begin(d_module, 'p_action', p_action);
1925 PO_LOG.proc_begin(d_module, 'p_calling_mode', p_calling_mode);
1926 PO_LOG.proc_begin(d_module, 'p_user_id', p_user_id);
1927 PO_LOG.proc_begin(d_module, 'p_employee_id', p_employee_id);
1928 PO_LOG.proc_begin(d_module, 'p_login_id', p_login_id);
1929 PO_LOG.proc_begin(d_module, 'p_reason', p_reason);
1930 PO_LOG.proc_begin(d_module, 'p_enc_flag', p_enc_flag);
1931 END IF;
1932
1933 d_progress := 10;
1934
1935 IF ((p_document_type = 'PA') and (p_document_subtype = 'BLANKET'))
1936 THEN
1937
1938 d_progress := 20;
1939
1940 IF (p_line_id IS NOT NULL)
1941 THEN
1942
1943 d_progress := 21;
1944
1945 SELECT pol.po_line_id
1946 BULK COLLECT INTO l_id_tbl
1947 FROM po_lines pol
1948 WHERE pol.po_line_id = p_line_id;
1949
1950 ELSE
1951
1952 d_progress := 22;
1953
1954 SELECT pol.po_line_id
1955 BULK COLLECT INTO l_id_tbl
1956 FROM po_lines pol
1957 WHERE pol.po_header_id = p_document_id;
1958
1959 END IF;
1960
1961 d_progress := 25;
1962
1963 FORALL i IN 1..l_id_tbl.COUNT
1964 UPDATE po_lines pol
1965 SET pol.last_update_date = SYSDATE
1966 , pol.last_updated_by = p_user_id
1967 , pol.last_update_login = p_login_id
1968 , pol.closed_date = DECODE(p_action,
1969 'CLOSE', SYSDATE,
1970 'FINALLY CLOSE', SYSDATE, -- Bug 4369988
1971 NULL)
1972 , pol.closed_by = p_employee_id
1973 , pol.closed_reason = p_reason
1974 , pol.closed_code = DECODE(p_action,
1975 'CLOSE', 'CLOSED',
1976 'FINALLY CLOSE', 'FINALLY CLOSED',
1977 'OPEN', 'OPEN')
1978 WHERE pol.po_line_id = l_id_tbl(i)
1979 AND NVL(pol.closed_code, 'OPEN') <> 'FINALLY CLOSED'
1980 AND NVL(pol.unit_meas_lookup_code, 'X') =
1981 DECODE(pol.unit_meas_lookup_code,
1982 'A', p_document_type,
1983 'B', p_document_subtype,
1984 'C', p_document_subtype,
1985 NVL(pol.unit_meas_lookup_code, 'X'))
1986 ;
1987
1988 d_progress := 27;
1989
1990 ELSIF ((p_document_type = 'PA') and (p_document_subtype = 'CONTRACT')) then
1991
1992 d_progress := 30;
1993
1994 UPDATE po_headers poh
1995 SET poh.last_update_date = SYSDATE
1996 , poh.last_updated_by = p_user_id
1997 , poh.last_update_login = p_login_id
1998 , poh.closed_date = DECODE(p_action,
1999 'CLOSE', SYSDATE,
2000 'FINALLY CLOSE', SYSDATE, -- Bug 4369988
2001 NULL)
2002 , poh.closed_code = DECODE(p_action,
2003 'CLOSE', 'CLOSED',
2004 'FINALLY CLOSE', 'FINALLY CLOSED',
2005 'OPEN', 'OPEN')
2006 WHERE poh.po_header_id = p_document_id
2007 AND NVL(poh.closed_code, 'OPEN') <> 'FINALLY CLOSED'
2008 AND poh.type_lookup_code =
2009 DECODE(poh.type_lookup_code,
2010 'A', p_document_type,
2011 'B', p_document_subtype,
2012 'C', p_document_subtype,
2013 poh.type_lookup_code)
2014 ;
2015
2016 d_progress := 35;
2017
2018
2019 ELSE
2020
2021 d_progress := 40;
2022
2023 IF (p_shipment_id IS NOT NULL)
2024 THEN
2025
2026 d_progress := 41;
2027
2028 SELECT poll.line_location_id
2029 BULK COLLECT INTO l_id_tbl
2030 FROM po_line_locations poll
2031 WHERE poll.line_location_id = p_shipment_id;
2032
2033 ELSIF(p_line_id IS NOT NULL)
2034 THEN
2035
2036 d_progress := 42;
2037
2038 SELECT poll.line_location_id
2039 BULK COLLECT INTO l_id_tbl
2040 FROM po_line_locations poll
2041 WHERE poll.po_line_id = p_line_id;
2042
2043 ELSIF(p_document_type = 'RELEASE')
2044 THEN
2045
2046 d_progress := 43;
2047
2048 SELECT poll.line_location_id
2049 BULK COLLECT INTO l_id_tbl
2050 FROM po_line_locations poll
2051 WHERE poll.po_release_id = p_document_id;
2052
2053 ELSE
2054
2055 d_progress := 44;
2056
2057 SELECT poll.line_location_id
2058 BULK COLLECT INTO l_id_tbl
2059 FROM po_line_locations poll
2060 WHERE poll.po_header_id = p_document_id;
2061
2062 END IF;
2063
2064 d_progress := 45;
2065
2066 --<DBI Req Fulfillment 11.5.11 Start >
2067 -- Modifed the exisiting sql for shipment closure dates
2068
2069 FORALL i IN 1..l_id_tbl.COUNT
2070 UPDATE po_line_locations poll
2071 SET poll.last_update_date = SYSDATE
2072 , poll.last_updated_by = p_user_id
2073 , poll.last_update_login = p_login_id
2074 , poll.closed_date = DECODE(p_action,
2075 'CLOSE', SYSDATE,
2076 'FINALLY CLOSE', SYSDATE,
2077 'INVOICE CLOSE', DECODE(NVL(poll.closed_code, 'OPEN'),
2078 'CLOSED FOR RECEIVING', SYSDATE,
2079 NULL),
2080 'RECEIVE CLOSE', DECODE(NVL(poll.closed_code, 'OPEN'),
2081 'CLOSED FOR INVOICE', SYSDATE,
2082 NULL)
2083 )
2084 , poll.closed_by = DECODE(p_calling_mode,
2085 'AP', DECODE(p_action, 'INVOICE OPEN', NULL, p_employee_id),
2086 p_employee_id
2087 )
2088 , poll.closed_reason = DECODE(p_calling_mode,
2089 'AP', DECODE(p_action, 'INVOICE OPEN', NULL, p_reason),
2090 p_reason
2091 )
2092 , poll.closed_code = DECODE(p_action,
2093 'CLOSE', 'CLOSED',
2094 'FINALLY CLOSE', 'FINALLY CLOSED',
2095 'INVOICE CLOSE', DECODE(NVL(poll.closed_code, 'OPEN'),
2096 'CLOSED FOR RECEIVING', 'CLOSED',
2097 'OPEN', 'CLOSED FOR INVOICE',
2098 poll.closed_code), -- <Bug 4490151>
2099 'RECEIVE CLOSE', DECODE(NVL(poll.closed_code, 'OPEN'),
2100 'CLOSED FOR INVOICE', 'CLOSED',
2101 'OPEN', 'CLOSED FOR RECEIVING',
2102 poll.closed_code), -- <Bug 4490151>
2103 'OPEN', DECODE(poll.consigned_flag,
2104 'Y', 'CLOSED FOR INVOICE',
2105 'OPEN'),
2106 'INVOICE OPEN', DECODE(poll.consigned_flag,
2107 'Y', poll.closed_code,
2108 DECODE(NVL(poll.closed_code, 'OPEN'),
2109 'CLOSED FOR INVOICE', 'OPEN',
2110 'CLOSED', 'CLOSED FOR RECEIVING',
2111 poll.closed_code)), -- <Bug 4490151>
2112 'RECEIVE OPEN', DECODE(NVL(poll.closed_code, 'OPEN'),
2113 'CLOSED FOR RECEIVING', 'OPEN',
2114 'CLOSED', 'CLOSED FOR INVOICE',
2115 poll.closed_code) -- <Bug 4490151>
2116 )
2117 , poll.shipment_closed_date = DECODE(p_action,
2118 'CLOSE', SYSDATE,
2119 'INVOICE CLOSE', DECODE(NVL(poll.closed_code, 'OPEN'),
2120 'CLOSED FOR RECEIVING', SYSDATE,
2121 poll.shipment_closed_date), -- <Bug 4490151>
2122 'RECEIVE CLOSE', DECODE(NVL(poll.closed_code, 'OPEN'),
2123 'CLOSED FOR INVOICE', SYSDATE,
2124 poll.shipment_closed_date), -- <Bug 4490151>
2125 'OPEN', NULL,
2126 'INVOICE OPEN', NULL,
2127 'RECEIVE OPEN', NULL,
2128 'FINALLY CLOSE', NVL(poll.shipment_closed_date, SYSDATE)
2129 )
2130 , poll.closed_for_invoice_date = DECODE(p_action,
2131 'CLOSE', DECODE(NVL(poll.closed_code, 'OPEN'),
2132 'CLOSED FOR RECEIVING', SYSDATE,
2133 'OPEN', SYSDATE,
2134 poll.closed_for_invoice_date),
2135 'INVOICE CLOSE', SYSDATE,
2136 'OPEN', NULL,
2137 'INVOICE OPEN', NULL,
2138 'FINALLY CLOSE', NVL(poll.closed_for_invoice_date, SYSDATE),
2139 poll.closed_for_invoice_date
2140 )
2141 , poll.closed_for_receiving_date = DECODE(p_action,
2142 'CLOSE', DECODE(NVL(poll.closed_code, 'OPEN'),
2143 'CLOSED FOR INVOICE', SYSDATE,
2144 'OPEN', SYSDATE,
2145 poll.closed_for_receiving_date),
2146 'RECEIVE CLOSE', SYSDATE,
2147 'OPEN', NULL,
2148 'RECEIVE OPEN', NULL,
2149 'FINALLY CLOSE', NVL(poll.closed_for_receiving_date, SYSDATE),
2150 poll.closed_for_receiving_date
2151 )
2152 WHERE poll.line_location_id = l_id_tbl(i)
2153 AND NVL(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
2154 AND poll.shipment_type =
2155 DECODE(p_document_type,
2156 -- <Complex Work R12>: STANDARD doc subtype no longer implies
2157 -- 'STANDARD' shipptype; it can also be PREPAYMENT.
2158 'PO', DECODE(p_document_subtype, 'STANDARD', poll.shipment_type, 'PLANNED'),
2159 'RELEASE', DECODE(p_document_subtype, 'SCHEDULED', 'SCHEDULED', 'BLANKET'))
2160 ;
2161
2162 --<DBI Req Fulfillment 11.5.11 End >
2163
2164 d_progress := 46;
2165
2166 END IF; -- p_document_type = ..
2167
2168 IF (PO_LOG.d_stmt) THEN
2169 PO_LOG.stmt(d_module, d_progress, 'Updated' || SQL%ROWCOUNT || ' closed code rows.' );
2170 END IF;
2171
2172 d_progress := 100;
2173
2174
2175 IF ((p_action = 'FINALLY CLOSE')
2176 OR ((p_calling_mode = 'AP') AND (p_action = 'INVOICE OPEN')))
2177 AND (p_document_type <> 'PA')
2178 AND (p_enc_flag)
2179 THEN
2180
2181 d_progress := 140;
2182
2183 -- If Action is Finally Close and Encumbrance is ON and Document is not
2184 -- a PA, we need to update gl_closed_date in po_distributions to the
2185 -- System Date
2186
2187 -- <JFMIP:Re-open Finally Match Shipment FPI START>
2188 -- If Action is Invoice Open and Calling from AP, we need to null out
2189 -- update gl_closed_date in po_distributions
2190
2191 IF (p_shipment_id IS NOT NULL)
2192 THEN
2193
2194 d_progress := 141;
2195
2196 SELECT pod.po_distribution_id
2197 BULK COLLECT INTO l_id_tbl
2198 FROM po_distributions pod
2199 WHERE pod.line_location_id = p_shipment_id;
2200
2201 ELSIF(p_line_id IS NOT NULL)
2202 THEN
2203
2204 d_progress := 142;
2205
2206 SELECT pod.po_distribution_id
2207 BULK COLLECT INTO l_id_tbl
2208 FROM po_distributions pod
2209 WHERE pod.po_line_id = p_line_id
2210 AND pod.po_release_id IS NULL;
2211
2212 -- existing bug? the release_id = NULL filter was missing in POXPOACB.pls
2213
2214
2215 ELSIF(p_document_type = 'RELEASE')
2216 THEN
2217
2218 d_progress := 143;
2219
2220 SELECT pod.po_distribution_id
2221 BULK COLLECT INTO l_id_tbl
2222 FROM po_distributions pod
2223 WHERE pod.po_release_id = p_document_id;
2224
2225 ELSE
2226
2227 d_progress := 144;
2228
2229 SELECT pod.po_distribution_id
2230 BULK COLLECT INTO l_id_tbl
2231 FROM po_distributions pod
2232 WHERE pod.po_header_id = p_document_id
2233 AND pod.po_release_id IS NULL;
2234
2235 -- existing bug? the release_id = NULL filter was missing in POXPOACB.pls
2236
2237 END IF;
2238
2239
2240 d_progress := 150;
2241
2242 FORALL i IN 1..l_id_tbl.COUNT
2243 UPDATE po_distributions pod
2244 SET pod.gl_closed_date = DECODE(p_action, 'FINALLY CLOSE', SYSDATE, NULL)
2245 WHERE pod.po_distribution_id = l_id_tbl(i)
2246 ;
2247
2248 d_progress := 160;
2249
2250 IF (PO_LOG.d_stmt) THEN
2251 PO_LOG.stmt(d_module, d_progress, 'Updated' || SQL%ROWCOUNT || ' distribution gl_closed_dates' );
2252 END IF;
2253
2254 END IF; -- if p_action = 'FINALLY CLOSE' OR ...
2255
2256 x_return_status := 'S';
2257
2258 IF (PO_LOG.d_proc) THEN
2259 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
2260 PO_LOG.proc_end(d_module);
2261 END IF;
2262
2263 EXCEPTION
2264 WHEN others THEN
2265 x_return_status := 'U';
2266
2267 PO_DOCUMENT_ACTION_PVT.error_msg_append(d_module, d_progress, SQLCODE, SQLERRM);
2268
2269 IF (PO_LOG.d_exc) THEN
2270 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
2271 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
2272 PO_LOG.proc_end(d_module);
2273 END IF;
2274
2275 RETURN;
2276
2277 END manual_update_closed_status;
2278
2279
2280 PROCEDURE auto_update_closed_status(
2281 p_document_id IN NUMBER
2282 , p_document_type IN VARCHAR2
2283 , p_calling_mode IN VARCHAR2
2284 , p_line_id IN NUMBER
2285 , p_shipment_id IN NUMBER
2286 , p_employee_id IN NUMBER
2287 , p_user_id IN NUMBER --bug4964600
2288 , p_login_id IN NUMBER --bug4964600
2289 , p_reason IN VARCHAR2
2290 , x_return_status OUT NOCOPY VARCHAR2
2291 )
2292 IS
2293
2294 d_module VARCHAR2(70) := 'po.plsql.PO_DOCUMENT_ACTION_CLOSE.auto_update_close_status';
2295 d_progress NUMBER;
2296 d_msg VARCHAR2(200);
2297
2298 l_ret_sts VARCHAR2(1);
2299
2300 l_id_tbl g_tbl_number;
2301
2302 BEGIN
2303
2304 d_progress := 0;
2305 IF (PO_LOG.d_proc) THEN
2306 PO_LOG.proc_begin(d_module);
2307 PO_LOG.proc_begin(d_module, 'p_document_id', p_document_id);
2308 PO_LOG.proc_begin(d_module, 'p_document_type', p_document_type);
2309 PO_LOG.proc_begin(d_module, 'p_line_id', p_line_id);
2310 PO_LOG.proc_begin(d_module, 'p_shipment_id', p_shipment_id);
2311 PO_LOG.proc_begin(d_module, 'p_calling_mode', p_calling_mode);
2312 PO_LOG.proc_begin(d_module, 'p_employee_id', p_employee_id);
2313 END IF;
2314
2315 IF (p_shipment_id IS NOT NULL)
2316 THEN
2317
2318 d_progress := 10;
2319
2320 SELECT poll.line_location_id
2321 BULK COLLECT INTO l_id_tbl
2322 FROM po_line_locations poll
2323 WHERE poll.line_location_id = p_shipment_id;
2324
2325 ELSIF (p_line_id IS NOT NULL)
2326 THEN
2327
2328 d_progress := 20;
2329
2330 SELECT poll.line_location_id
2331 BULK COLLECT INTO l_id_tbl
2332 FROM po_line_locations poll
2333 WHERE poll.po_line_id = p_line_id
2334 AND poll.po_release_id IS NULL;
2335
2336 ELSIF (p_document_type = 'RELEASE')
2337 THEN
2338
2339 d_progress := 30;
2340
2341 SELECT poll.line_location_id
2342 BULK COLLECT INTO l_id_tbl
2343 FROM po_line_locations poll
2344 WHERE poll.po_release_id = p_document_id;
2345
2346 ELSE
2347
2348 d_progress := 40;
2349
2350 SELECT poll.line_location_id
2351 BULK COLLECT INTO l_id_tbl
2352 FROM po_line_locations poll
2353 WHERE poll.po_header_id = p_document_id
2354 AND poll.po_release_id IS NULL;
2355
2356 END IF;
2357
2358
2359 IF (p_calling_mode = 'PO')
2360 THEN
2361
2362 -- As part of bug# 3325173: Algorithmic logic for the decode statement below
2363 --
2364 -- IF some quantity is remaining to be BILLED (OPEN)
2365 -- (after adjusting quantity cancelled/billed and invoice close tolerance)
2366 -- THEN
2367 -- IF some quantity is remaining to be RECEIVED (OPEN)
2368 -- (after adjusting quantity cancelled/accepted/delivered/received
2369 -- and receive close tolerance)
2370 -- THEN
2371 -- CLOSED_CODE = 'OPEN';
2372 -- ELSE (CLOSED FOR RECEIVING)
2373 -- CLOSED_CODE = 'CLOSED FOR RECEIVING';
2374 -- END IF;
2375 -- ELSE (CLOSED FOR INVOICE)
2376 -- IF some quantity is remaining to be RECEIVED (OPEN)
2377 -- (after adjusting quantity cancelled/accepted/delivered/received
2378 -- and receive close tolerance)
2379 -- THEN
2380 -- CLOSED_CODE = 'CLOSED FOR INVOICE';
2381 -- ELSE (CLOSED FOR RECEIVING)
2382 -- CLOSED_CODE = 'CLOSED' -- comment was previously incorrect
2383 -- END IF;
2384 -- END IF;
2385 --
2386 -- Note: For Services Line types, where matching_basis is AMOUNT, the
2387 -- same logic as above applies, except that all quantity columns are
2388 -- replaced with respective amount columns.
2389
2390 -- <Complex Work R12>: Change query to check for greatest of financed and billed
2391
2392 d_progress := 50;
2393
2394 FORALL i IN 1..l_id_tbl.COUNT
2395 UPDATE po_line_locations poll
2396 SET poll.closed_code =
2397 (
2398 SELECT DECODE(poll.matching_basis,
2399 'AMOUNT',
2400 DECODE(
2401 DECODE(sign(
2402 ((poll.amount - NVL(poll.amount_cancelled, 0))
2403 * (1 - NVL(poll.invoice_close_tolerance,
2404 NVL(posp.invoice_close_tolerance, 0))/100))
2405 - GREATEST(NVL(poll.amount_financed, 0),
2406 NVL(poll.amount_billed, 0))),
2407 1, 'OPEN',
2408 'CLOSED FOR INVOICE'),
2409 'CLOSED FOR INVOICE',
2410 DECODE(
2411 DECODE(sign(
2412 ((poll.amount - NVL(poll.amount_cancelled, 0))
2413 * (1 - NVL(poll.receive_close_tolerance,
2414 NVL(posp.receive_close_tolerance, 0))/100))
2415 - DECODE(posp.receive_close_code,
2416 'ACCEPTED', NVL(poll.amount_accepted, 0),
2417 'DELIVERED', sum(NVL(pod.amount_delivered, 0)),
2418 NVL(poll.amount_received, 0))),
2419 1, 'OPEN',
2420 'CLOSED FOR RECEIVING'),
2421 'CLOSED FOR RECEIVING', 'CLOSED',
2422 'CLOSED FOR INVOICE'),
2423 'OPEN',
2424 DECODE(
2425 DECODE(sign(
2426 ((poll.amount - NVL(poll.amount_cancelled, 0))
2427 * (1 - NVL(poll.receive_close_tolerance,
2428 NVL(posp.receive_close_tolerance, 0))/100))
2429 - DECODE(posp.receive_close_code,
2430 'ACCEPTED', NVL(poll.amount_accepted, 0),
2431 'DELIVERED', sum(NVL(pod.amount_delivered, 0)),
2432 NVL(poll.amount_received, 0))),
2433 1, 'OPEN',
2434 'CLOSED FOR RECEIVING'),
2435 'CLOSED FOR RECEIVING', 'CLOSED FOR RECEIVING',
2436 'OPEN')),
2437 -- else QUANTITY BASIS
2438 DECODE(
2439 DECODE(sign(
2440 ((poll.quantity - NVL(poll.quantity_cancelled, 0))
2441 * (1 - NVL(poll.invoice_close_tolerance,
2442 NVL(posp.invoice_close_tolerance, 0))/100))
2443 - GREATEST(NVL(poll.quantity_financed, 0),
2444 NVL(poll.quantity_billed, 0))),
2445 1, 'OPEN',
2446 'CLOSED FOR INVOICE'),
2447 'CLOSED FOR INVOICE',
2448 DECODE(
2449 DECODE(sign(
2450 ((poll.quantity - NVL(poll.quantity_cancelled, 0))
2451 * (1 - NVL(poll.receive_close_tolerance,
2452 NVL(posp.receive_close_tolerance, 0))/100))
2453 - DECODE(posp.receive_close_code,
2454 'ACCEPTED', NVL(poll.quantity_accepted, 0),
2455 'DELIVERED', sum(NVL(pod.quantity_delivered, 0)),
2456 NVL(poll.quantity_received, 0))),
2457 1, 'OPEN',
2458 'CLOSED FOR RECEIVING'),
2459 'CLOSED FOR RECEIVING', 'CLOSED',
2460 'CLOSED FOR INVOICE'),
2461 'OPEN',
2462 DECODE(
2463 DECODE(sign(
2464 ((poll.quantity - NVL(poll.quantity_cancelled, 0))
2465 * (1 - NVL(poll.receive_close_tolerance,
2466 NVL(posp.receive_close_tolerance, 0))/100))
2467 - DECODE(posp.receive_close_code,
2468 'ACCEPTED', NVL(poll.quantity_accepted, 0),
2469 'DELIVERED', sum(NVL(pod.quantity_delivered, 0)),
2470 NVL(poll.quantity_received, 0))),
2471 1, 'OPEN',
2472 'CLOSED FOR RECEIVING'),
2473 'CLOSED FOR RECEIVING', 'CLOSED FOR RECEIVING',
2474 'OPEN')))
2475 FROM po_distributions pod
2476 , po_system_parameters posp
2477 WHERE poll.line_location_id = l_id_tbl(i)
2478 AND NVL(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
2479 AND pod.line_location_id = poll.line_location_id
2480 GROUP BY poll.quantity
2481 , poll.quantity_cancelled
2482 , poll.quantity_billed
2483 , poll.quantity_financed
2484 , poll.quantity_accepted
2485 , poll.quantity_received
2486 , poll.amount
2487 , poll.amount_cancelled
2488 , poll.amount_billed
2489 , poll.amount_financed
2490 , poll.amount_accepted
2491 , poll.amount_received
2492 , poll.matching_basis
2493 , poll.invoice_close_tolerance
2494 , poll.receive_close_tolerance
2495 , posp.receive_close_code
2496 , posp.receive_close_tolerance
2497 , posp.invoice_close_tolerance
2498 )
2499 WHERE poll.line_location_id = l_id_tbl(i)
2500 AND NVL(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED';
2501
2502 d_progress := 60;
2503
2504 ELSIF (p_calling_mode = 'RCV')
2505 THEN
2506
2507 -- As part of bug# 3325173: Algorithmic logic for the decode statement below
2508 --
2509 -- IF some quantity is remaining to be RECEIVED (OPEN)
2510 -- (after adjusting quantity cancelled/accepted/delivered/received
2511 -- and receive close tolerance)
2512 -- THEN
2513 -- IF Shipment closed_code is 'CLOSED'
2514 -- THEN
2515 -- CLOSED_CODE = 'CLOSED FOR INVOICE';
2516 -- ELSE IF Shipment closed_code is 'CLOSED FOR RECEIVING'
2517 -- CLOSED_CODE = 'OPEN';
2518 -- ELSE
2519 -- Don't modify the shipment closed code
2520 -- END IF;
2521 -- ELSE (CLOSED FOR RECEIVING)
2522 -- IF Shipment closed_code is 'OPEN'
2523 -- THEN
2524 -- CLOSED_CODE = 'CLOSED FOR RECEIVING';
2525 -- ELSE IF Shipment closed_code is 'CLOSED FOR INVOICE'
2526 -- CLOSED_CODE = 'CLOSED'; -- comment was previously incorrect
2527 -- ELSE
2528 -- Don't modify the shipment closed code
2529 -- END IF;
2530 -- END IF;
2531 --
2532 -- Note: For Services Line types, where matching_basis is AMOUNT, the
2533 -- same logic as above applies, except that all quantity columns are
2534 -- replaced with respective amount columns.
2535
2536 d_progress := 70;
2537
2538 FORALL i IN 1..l_id_tbl.COUNT
2539 UPDATE po_line_locations poll
2540 SET poll.closed_code =
2541 (
2542 SELECT DECODE(poll.matching_basis,
2543 'AMOUNT',
2544 DECODE(
2545 DECODE(sign(
2546 ((poll.amount - NVL(poll.amount_cancelled, 0))
2547 * (1 - NVL(poll.receive_close_tolerance,
2548 NVL(posp.receive_close_tolerance, 0))/100))
2549 - DECODE(posp.receive_close_code,
2550 'ACCEPTED', NVL(poll.amount_accepted, 0),
2551 'DELIVERED', sum(NVL(pod.amount_delivered, 0)),
2552 NVL(poll.amount_received, 0))),
2553 1, 'OPEN',
2554 'CLOSED FOR RECEIVING'),
2555 'CLOSED FOR RECEIVING',
2556 DECODE(NVL(poll.closed_code, 'OPEN'),
2557 'OPEN', 'CLOSED FOR RECEIVING',
2558 'CLOSED FOR INVOICE', 'CLOSED',
2559 poll.closed_code),
2560 'OPEN',
2561 DECODE(poll.closed_code,
2562 'CLOSED', 'CLOSED FOR INVOICE',
2563 'CLOSED FOR RECEIVING', 'OPEN',
2564 poll.closed_code)),
2565 -- else QUANTITY BASIS
2566 DECODE(
2567 DECODE(sign(
2568 ((poll.quantity - NVL(poll.quantity_cancelled, 0))
2569 * (1 - NVL(poll.receive_close_tolerance,
2570 NVL(posp.receive_close_tolerance, 0))/100))
2571 - DECODE(posp.receive_close_code,
2572 'ACCEPTED', NVL(poll.quantity_accepted, 0),
2573 'DELIVERED', sum(NVL(pod.quantity_delivered, 0)),
2574 NVL(poll.quantity_received, 0))),
2575 1, 'OPEN',
2576 'CLOSED FOR RECEIVING'),
2577 'CLOSED FOR RECEIVING',
2578 DECODE(NVL(poll.closed_code, 'OPEN'),
2579 'OPEN', 'CLOSED FOR RECEIVING',
2580 'CLOSED FOR INVOICE', 'CLOSED',
2581 poll.closed_code),
2582 'OPEN',
2583 DECODE(poll.closed_code,
2584 'CLOSED', 'CLOSED FOR INVOICE',
2585 'CLOSED FOR RECEIVING', 'OPEN',
2586 poll.closed_code)))
2587 FROM po_distributions pod
2588 , po_system_parameters posp
2589 WHERE poll.line_location_id = l_id_tbl(i)
2590 AND NVL(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
2591 AND pod.line_location_id = poll.line_location_id
2592 GROUP BY poll.quantity
2593 , poll.quantity_cancelled
2594 , poll.quantity_accepted
2595 , poll.quantity_received
2596 , poll.amount
2597 , poll.amount_cancelled
2598 , poll.amount_accepted
2599 , poll.amount_received
2600 , poll.matching_basis
2601 , poll.receive_close_tolerance
2602 , posp.receive_close_code
2603 , poll.closed_code
2604 , posp.receive_close_tolerance
2605 )
2606 WHERE poll.line_location_id = l_id_tbl(i)
2607 AND NVL(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED';
2608
2609 d_progress := 80;
2610
2611 ELSIF (p_calling_mode = 'AP')
2612 THEN
2613
2614 -- As part of bug# 3325173: Algorithmic logic for the decode statement below
2615 --
2616 -- IF some quantity is remaining to be INVOICED (OPEN)
2617 -- (after adjusting quantity cancelled/billed and invoice close tolerance)
2618 -- THEN
2619 -- IF Shipment closed_code is 'CLOSED'
2620 -- THEN
2621 -- CLOSED_CODE = 'CLOSED FOR RECEIVING';
2622 -- ELSE IF Shipment closed_code is 'CLOSED FOR INVOICE'
2623 -- CLOSED_CODE = 'OPEN';
2624 -- ELSE
2625 -- Don't modify the shipment closed code
2626 -- END IF;
2627 -- ELSE (CLOSED FOR INVOICE)
2628 -- IF Shipment closed_code is 'OPEN'
2629 -- THEN
2630 -- CLOSED_CODE = 'CLOSED FOR INVOICE';
2631 -- ELSE IF Shipment closed_code is 'CLOSED FOR RECEIVING'
2632 -- CLOSED_CODE = 'CLOSED';
2633 -- ELSE
2634 -- Don't modify the shipment closed code
2635 -- END IF;
2636 -- END IF;
2637 --
2638 -- Note: For Services Line types, where matching_basis is AMOUNT, the
2639 -- same logic as above applies, except that all quantity columns are
2640 -- replaced with respective amount columns.
2641
2642 -- <Complex Work R12>: Change query to check for greatest of financed and billed
2643
2644 d_progress := 90;
2645
2646 FORALL i IN 1..l_id_tbl.COUNT
2647 UPDATE po_line_locations poll
2648 SET poll.closed_code =
2649 (
2650 SELECT DECODE(poll.matching_basis,
2651 'AMOUNT',
2652 DECODE(
2653 DECODE(sign(
2654 ((poll.amount - NVL(poll.amount_cancelled, 0))
2655 * (1 - NVL(poll.invoice_close_tolerance,
2656 NVL(posp.invoice_close_tolerance, 0))/100))
2657 - GREATEST(NVL(poll.amount_financed, 0),
2658 NVL(poll.amount_billed, 0))),
2659 1, 'OPEN',
2660 'CLOSED FOR INVOICE'),
2661 'CLOSED FOR INVOICE',
2662 DECODE(NVL(poll.closed_code, 'OPEN'),
2663 'OPEN', 'CLOSED FOR INVOICE',
2664 'CLOSED FOR RECEIVING', 'CLOSED',
2665 poll.closed_code),
2666 'OPEN',
2667 DECODE(poll.closed_code,
2668 'CLOSED', 'CLOSED FOR RECEIVING',
2669 'CLOSED FOR INVOICE', 'OPEN',
2670 poll.closed_code)),
2671 -- else QUANTITY BASIS
2672 DECODE(
2673 DECODE(sign(
2674 ((poll.quantity - NVL(poll.quantity_cancelled, 0))
2675 * (1 - NVL(poll.invoice_close_tolerance,
2676 NVL(posp.invoice_close_tolerance, 0))/100))
2677 - GREATEST(NVL(poll.quantity_financed, 0),
2678 NVL(poll.quantity_billed, 0))),
2679 1, 'OPEN',
2680 'CLOSED FOR INVOICE'),
2681 'CLOSED FOR INVOICE',
2682 DECODE(NVL(poll.closed_code, 'OPEN'),
2683 'OPEN', 'CLOSED FOR INVOICE',
2684 'CLOSED FOR RECEIVING', 'CLOSED',
2685 poll.closed_code),
2686 'OPEN',
2687 DECODE(poll.closed_code,
2688 'CLOSED', 'CLOSED FOR RECEIVING',
2689 'CLOSED FOR INVOICE', 'OPEN',
2690 poll.closed_code)))
2691 FROM po_distributions pod
2692 , po_system_parameters posp
2693 WHERE poll.line_location_id = l_id_tbl(i)
2694 AND NVL(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
2695 AND pod.line_location_id = poll.line_location_id
2696 GROUP BY poll.quantity
2697 , poll.quantity_cancelled
2698 , poll.quantity_billed
2699 , poll.quantity_financed
2700 , poll.amount
2701 , poll.amount_cancelled
2702 , poll.amount_billed
2703 , poll.amount_financed
2704 , poll.matching_basis
2705 , poll.invoice_close_tolerance
2706 , poll.closed_code
2707 , posp.invoice_close_tolerance
2708 )
2709 WHERE poll.line_location_id = l_id_tbl(i)
2710 AND NVL(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED';
2711
2712 d_progress := 100;
2713
2714 END IF; -- if p_calling_mode = 'PO'
2715
2716 d_progress := 200;
2717
2718 -- combined two queries into one by using decodes
2719 -- previously, the closed_code <> CLOSED and = CLOSED were split up
2720 -- Bug 5480524: removed the closed date is null and <> open condition
2721 -- as thats not required.
2722 FORALL i IN 1..l_id_tbl.COUNT
2723 UPDATE po_line_locations poll
2724 SET poll.closed_date = DECODE(NVL(poll.closed_code, 'OPEN'), 'CLOSED', SYSDATE, NULL)
2725 , poll.closed_reason = DECODE(NVL(poll.closed_code, 'OPEN'), 'CLOSED', p_reason, NULL)
2726 , poll.closed_by = DECODE(NVL(poll.closed_code, 'OPEN'), 'CLOSED', p_employee_id, NULL)
2727 WHERE poll.line_location_id = l_id_tbl(i)
2728 AND NVL(poll.closed_code, 'OPEN') IN ('CLOSED','OPEN','CLOSED FOR INVOICING', 'CLOSED FOR RECEIVING');
2729
2730
2731
2732 d_progress := 210;
2733
2734 --<DBI Requisition Fulfillment 11.5.11 Start>
2735 -- update the shipment closure dates
2736 -- use po_line_locations instead of po_line_locations all
2737 -- this is to leverage l_id_tbl from above
2738
2739 FORALL i IN 1..l_id_tbl.COUNT
2740 UPDATE po_line_locations poll
2741 SET poll.shipment_closed_date = DECODE(poll.closed_code,
2742 'CLOSED', NVL(poll.shipment_closed_date,
2743 PO_ACTIONS.get_closure_dates('CLOSE', poll.line_location_id)),
2744 NULL)
2745 , poll.closed_for_receiving_date = DECODE(poll.closed_code,
2746 'CLOSED FOR RECEIVING', NVL(poll.closed_for_receiving_date,
2747 PO_ACTIONS.get_closure_dates('RECEIVE CLOSE', poll.line_location_id)),
2748 'CLOSED FOR INVOICE', NULL,
2749 'CLOSED', NVL(poll.closed_for_receiving_date,
2750 PO_ACTIONS.get_closure_dates('RECEIVE CLOSE', poll.line_location_id)),
2751 'OPEN', NULL)
2752 , poll.closed_for_invoice_date = DECODE(poll.closed_code,
2753 'CLOSED FOR RECEIVING', NULL,
2754 'CLOSED FOR INVOICE', NVL(poll.closed_for_invoice_date,
2755 PO_ACTIONS.get_closure_dates('INVOICE CLOSE', poll.line_location_id)),
2756 'CLOSED', NVL(poll.closed_for_invoice_date,
2757 PO_ACTIONS.get_closure_dates('INVOICE CLOSE', poll.line_location_id)),
2758 'OPEN', NULL)
2759 , poll.last_update_date = SYSDATE --bug4964600
2760 , poll.last_updated_by = p_user_id --bug4964600
2761 , poll.last_update_login = p_login_id --bug4964600
2762 WHERE poll.line_location_id = l_id_tbl(i)
2763 AND NVL(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED';
2764
2765 --<DBI Requisition Fulfillment 11.5.11 End>
2766
2767 d_progress := 220;
2768
2769 x_return_status := 'S';
2770
2771 IF (PO_LOG.d_proc) THEN
2772 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
2773 PO_LOG.proc_end(d_module);
2774 END IF;
2775
2776 EXCEPTION
2777 WHEN others THEN
2778 x_return_status := 'U';
2779
2780 PO_DOCUMENT_ACTION_PVT.error_msg_append(d_module, d_progress, SQLCODE, SQLERRM);
2781
2782 IF (PO_LOG.d_exc) THEN
2783 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
2784 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
2785 PO_LOG.proc_end(d_module);
2786 END IF;
2787
2788 RETURN;
2789
2790 END auto_update_closed_status;
2791
2792
2793 PROCEDURE rollup_close_state(
2794 p_document_id IN NUMBER
2795 , p_document_type IN VARCHAR2
2796 , p_document_subtype IN VARCHAR2
2797 , p_action IN VARCHAR2
2798 , p_line_id IN NUMBER
2799 , p_shipment_id IN NUMBER
2800 , p_user_id IN NUMBER
2801 , p_login_id IN NUMBER
2802 , p_employee_id IN NUMBER
2803 , p_reason IN VARCHAR2
2804 , p_action_date IN DATE
2805 , p_calling_mode IN VARCHAR2
2806 , x_return_status OUT NOCOPY VARCHAR2
2807 ) IS
2808
2809 d_module VARCHAR2(70) := 'po.plsql.PO_DOCUMENT_ACTION_CLOSE.rollup_close_state';
2810 d_progress NUMBER;
2811 d_msg VARCHAR2(200);
2812
2813 l_ret_sts VARCHAR2(1);
2814
2815 l_rollup_msg VARCHAR2(256);
2816 l_rollup_code PO_LINES.closed_code%TYPE;
2817
2818 l_lineid_tbl g_tbl_number;
2819
2820 l_hist_action VARCHAR2(30);
2821 l_update_action_hist BOOLEAN;
2822
2823 l_none_open_one_closed PO_LINE_LOCATIONS.closed_code%TYPE;
2824 l_all_finally_closed PO_LINE_LOCATIONS.closed_code%TYPE;
2825
2826
2827 -- we use cursors for performance reasons during create releases
2828 -- See: Bug 1834138 (perf issue) and Bug 2361826 (bug in cursor)
2829
2830 CURSOR rollup_rel_open(p_rel_id NUMBER) IS
2831 SELECT 'OPEN'
2832 FROM po_line_locations poll
2833 WHERE poll.po_release_id = p_rel_id
2834 AND NVL(poll.closed_code, 'OPEN') IN ('OPEN', 'CLOSED FOR INVOICE', 'CLOSED FOR RECEIVING')
2835 AND rownum = 1;
2836
2837 CURSOR rollup_rel_not_fc(p_rel_id NUMBER) IS
2838 SELECT 'CLOSED'
2839 FROM po_line_locations poll
2840 WHERE poll.po_release_id = p_rel_id
2841 AND NVL(poll.closed_code, 'CLOSED') = 'CLOSED'
2842 AND rownum = 1;
2843
2844
2845 BEGIN
2846
2847 d_progress := 0;
2848 IF (PO_LOG.d_proc) THEN
2849 PO_LOG.proc_begin(d_module);
2850 PO_LOG.proc_begin(d_module, 'p_document_id', p_document_id);
2851 PO_LOG.proc_begin(d_module, 'p_document_type', p_document_type);
2852 PO_LOG.proc_begin(d_module, 'p_document_subtype', p_document_subtype);
2853 PO_LOG.proc_begin(d_module, 'p_action', p_action);
2854 PO_LOG.proc_begin(d_module, 'p_line_id', p_line_id);
2855 PO_LOG.proc_begin(d_module, 'p_shipment_id', p_shipment_id);
2856 PO_LOG.proc_begin(d_module, 'p_user_id', p_user_id);
2857 PO_LOG.proc_begin(d_module, 'p_login_id', p_login_id);
2858 PO_LOG.proc_begin(d_module, 'p_employee_id', p_employee_id);
2859 PO_LOG.proc_begin(d_module, 'p_reason', p_reason);
2860 PO_LOG.proc_begin(d_module, 'p_action_date', p_action_date);
2861 PO_LOG.proc_begin(d_module, 'p_calling_mode', p_calling_mode);
2862 END IF;
2863
2864 d_progress := 10;
2865
2866 l_rollup_msg := substr(FND_MESSAGE.GET_STRING('PO', 'PO_CLOSE_ROLLUP'), 1, 256);
2867
2868 IF (p_document_type = 'PO')
2869 THEN
2870
2871 d_progress := 20;
2872
2873 IF (p_shipment_id IS NOT NULL)
2874 THEN
2875
2876 d_progress := 30;
2877
2878 SELECT pol.po_line_id
2879 BULK COLLECT INTO l_lineid_tbl
2880 FROM po_lines pol
2881 WHERE pol.po_line_id =
2882 ( SELECT poll.po_line_id
2883 FROM po_line_locations poll
2884 WHERE poll.line_location_id = p_shipment_id)
2885 ;
2886
2887 ELSIF (p_line_id IS NOT NULL)
2888 THEN
2889
2890
2891 d_progress := 40;
2892
2893 SELECT pol.po_line_id
2894 BULK COLLECT INTO l_lineid_tbl
2895 FROM po_lines pol
2896 WHERE pol.po_line_id = p_line_id;
2897
2898
2899 ELSE
2900
2901 d_progress := 50;
2902
2903 SELECT pol.po_line_id
2904 BULK COLLECT INTO l_lineid_tbl
2905 FROM po_lines pol
2906 WHERE pol.po_header_id = p_document_id;
2907
2908 END IF; -- p_shipment_id IS NOT NULL
2909
2910 d_progress := 60;
2911
2912 IF (PO_LOG.d_stmt) THEN
2913 PO_LOG.stmt(d_module, d_progress, 'Count of lines = ' || to_char(l_lineid_tbl.COUNT));
2914 END IF;
2915
2916 FOR i IN 1..l_lineid_tbl.COUNT
2917 LOOP
2918
2919 d_progress := 70;
2920
2921 IF ((p_shipment_id IS NULL) AND (p_action = 'FINALLY CLOSE'))
2922 THEN
2923
2924 d_progress := 71;
2925
2926 -- roll up 'FINALLY CLOSE' only if it was taken at the line or header level
2927 -- otherwise, we will roll up 'CLOSED' as set later.
2928
2929 l_none_open_one_closed := 'CLOSED';
2930 l_all_finally_closed := 'FINALLY CLOSED';
2931
2932
2933 ELSIF ((p_shipment_id IS NULL) AND (p_action = 'OPEN'))
2934 THEN
2935
2936 d_progress := 72;
2937
2938 l_none_open_one_closed := 'OPEN';
2939 l_all_finally_closed := 'OPEN';
2940
2941 ELSE
2942
2943 d_progress := 73;
2944
2945 l_none_open_one_closed := 'CLOSED';
2946 l_all_finally_closed := 'CLOSED';
2947
2948 END IF; -- if p_shipment_id is null and ...
2949
2950 d_progress := 75;
2951
2952 SELECT DECODE(max(DECODE(poll.closed_code,
2953 'CLOSED', 2,
2954 'FINALLY CLOSED', 1,
2955 3)),
2956 3, 'OPEN',
2957 2, l_none_open_one_closed,
2958 1, l_all_finally_closed )
2959 INTO l_rollup_code
2960 FROM po_line_locations poll
2961 WHERE poll.po_line_id = l_lineid_tbl(i)
2962 AND poll.po_release_id IS NULL
2963 AND poll.shipment_type <> 'PREPAYMENT'; -- <Complex Work R12>
2964
2965 d_progress := 76;
2966
2967 IF (PO_LOG.d_stmt) THEN
2968 PO_LOG.stmt(d_module, d_progress, 'l_lineid_tbl(i)', l_lineid_tbl(i));
2969 PO_LOG.stmt(d_module, d_progress, 'l_rollup_code', l_rollup_code);
2970 END IF;
2971
2972 d_progress := 80;
2973
2974 UPDATE po_lines pol
2975 SET pol.closed_code = l_rollup_code
2976 , pol.last_update_date = SYSDATE
2977 , pol.last_updated_by = p_user_id
2978 , pol.last_update_login = p_login_id
2979 , pol.closed_by = p_employee_id
2980 , pol.closed_date = DECODE(l_rollup_code,
2981 'CLOSED', SYSDATE,
2982 'FINALLY CLOSED', SYSDATE,
2983 NULL)
2984 , pol.closed_reason = DECODE(p_shipment_id, NULL, p_reason, l_rollup_msg)
2985 WHERE pol.po_line_id = l_lineid_tbl(i)
2986 AND NVL(pol.closed_code, 'OPEN') <> l_rollup_code
2987 AND (((p_action = 'INVOICE OPEN') AND (p_calling_mode = 'AP'))
2988 OR (NVL(pol.closed_code, 'OPEN') <> 'FINALLY CLOSED'));
2989
2990 -- Above, needed to incorporate the fix for bug 1837339 as in poccr.lpc, but also
2991 -- allow roll up of undo final close when called from AP, as in POXPOACB.pls
2992
2993 d_progress := 100;
2994
2995 -- bug 5142689, removed the fnd logging here.Otherwise,SQL%ROWCOUNT in the
2996 -- following IF part will refer to what happens inside logging.
2997
2998 --Bug 5574493: Removed code to roll down to financing Pay items
2999
3000 END LOOP;
3001
3002 END IF; -- (if p_document_type = 'PO')
3003
3004
3005 d_progress := 200;
3006
3007 -- If any Line is Open, Open the Header. If there are no Open Lines
3008 -- and there are Closed Lines then Close the Header. If there are no
3009 -- Open or Closed Lines but there are Finally Closed Lines then Close
3010 -- the Header
3011
3012 IF ((p_document_type IN ('PO', 'PA')) AND (p_document_subtype <> 'CONTRACT'))
3013 THEN
3014
3015 d_progress := 210;
3016
3017
3018 IF ((p_shipment_id IS NULL) AND (p_line_id IS NULL) AND (p_action = 'FINALLY CLOSE'))
3019 THEN
3020
3021 d_progress := 211;
3022
3023 -- roll up 'FINALLY CLOSE' only if it was taken at the header level
3024 -- otherwise, we will roll up 'CLOSED'
3025
3026 l_none_open_one_closed := 'CLOSED';
3027 l_all_finally_closed := 'FINALLY CLOSED';
3028
3029 ELSIF ((p_shipment_id IS NULL) AND (p_line_id IS NULL) AND (p_action = 'OPEN'))
3030 THEN
3031
3032 d_progress := 213;
3033
3034 l_none_open_one_closed := 'OPEN';
3035 l_all_finally_closed := 'OPEN';
3036
3037 ELSE
3038
3039 d_progress := 215;
3040
3041 l_none_open_one_closed := 'CLOSED';
3042 l_all_finally_closed := 'CLOSED';
3043
3044 END IF; -- if p_shipment_id is null and ...
3045
3046 d_progress := 218;
3047
3048 SELECT DECODE(max(DECODE(pol.closed_code,
3049 'CLOSED', 2,
3050 'FINALLY CLOSED', 1,
3051 3)),
3052 3, 'OPEN',
3053 2, l_none_open_one_closed,
3054 1, l_all_finally_closed)
3055 INTO l_rollup_code
3056 FROM po_lines pol
3057 WHERE pol.po_header_id = p_document_id;
3058
3059 d_progress := 220;
3060 IF (PO_LOG.d_stmt) THEN
3061 PO_LOG.stmt(d_module, d_progress, 'l_rollup_code', l_rollup_code);
3062 END IF;
3063
3064 UPDATE po_headers poh
3065 SET poh.closed_code = l_rollup_code
3066 , poh.last_update_date = SYSDATE
3067 , poh.last_updated_by = p_user_id
3068 , poh.last_update_login = p_login_id
3069 , poh.closed_date = decode(l_rollup_code,
3070 'CLOSED', SYSDATE,
3071 'FINALLY CLOSED', SYSDATE,
3072 NULL)
3073 WHERE poh.po_header_id = p_document_id
3074 AND NVL(poh.closed_code, 'OPEN') <> l_rollup_code;
3075
3076 d_progress := 230;
3077
3078 -- bug 5142689, removed the fnd logging here.Otherwise, NOT SQL%NOTFOUND in the
3079 -- following IF part will refer to what happens inside logging.
3080
3081 -- If a record was updated, we need to update the Action History
3082 IF (NOT SQL%NOTFOUND)
3083 THEN
3084 l_update_action_hist := TRUE;
3085 l_hist_action := NULL;
3086 ELSE
3087 l_update_action_hist := FALSE;
3088 END IF; -- if not sql%notfound
3089
3090 ELSIF (p_document_type = 'RELEASE')
3091 THEN
3092
3093 d_progress := 300;
3094
3095 OPEN rollup_rel_open(p_document_id);
3096 FETCH rollup_rel_open INTO l_rollup_code;
3097 CLOSE rollup_rel_open;
3098
3099 IF (l_rollup_code IS NULL)
3100 THEN
3101
3102 d_progress := 301;
3103
3104 IF ((p_shipment_id IS NULL) AND (p_action = 'FINALLY CLOSE'))
3105 THEN
3106
3107 d_progress := 302;
3108
3109 -- roll up 'FINALLY CLOSE' only if it was taken at the release header level
3110 -- and there all release shipments are finally closed.
3111
3112 OPEN rollup_rel_not_fc(p_document_id);
3113 FETCH rollup_rel_not_fc INTO l_rollup_code;
3114 CLOSE rollup_rel_not_fc;
3115
3116 l_rollup_code := NVL(l_rollup_code, 'FINALLY CLOSED');
3117
3118 ELSIF ((p_shipment_id IS NULL) AND (p_action = 'OPEN'))
3119 THEN
3120
3121 d_progress := 303;
3122
3123 l_rollup_code := 'OPEN';
3124
3125 ELSE
3126
3127 d_progress := 305;
3128
3129 l_rollup_code := 'CLOSED';
3130
3131 END IF; -- if p_shipment_id is null and ..
3132
3133 END IF; -- if l_rollup_code is null
3134
3135 d_progress := 310;
3136
3137 IF (PO_LOG.d_stmt) THEN
3138 PO_LOG.stmt(d_module, d_progress, 'l_rollup_code', l_rollup_code);
3139 END IF;
3140
3141 UPDATE po_releases por
3142 SET por.closed_code = l_rollup_code
3143 , por.last_update_date = SYSDATE
3144 , por.last_updated_by = p_user_id
3145 , por.last_update_login = p_login_id
3146 WHERE por.po_release_id = p_document_id
3147 AND NVL(por.closed_code, 'OPEN') <> l_rollup_code;
3148
3149 d_progress := 320;
3150
3151 -- bug 5142689, removed the fnd logging here.Otherwise,NOT SQL%NOTFOUND in the
3152 -- following IF part will refer to what happens inside logging.
3153
3154 -- If a record was updated, we need to update the Action History
3155 IF (NOT SQL%NOTFOUND)
3156 THEN
3157 l_update_action_hist := TRUE;
3158 l_hist_action := NULL;
3159 ELSE
3160 l_update_action_hist := FALSE;
3161 END IF; -- if not sql%notfound
3162
3163 ELSIF ((p_document_type = 'PA') AND (p_document_subtype = 'CONTRACT'))
3164 THEN
3165
3166 l_update_action_hist := TRUE;
3167 l_hist_action := p_action;
3168
3169 END IF; -- if (p_document_type IN 'PO', 'PA') AND ...
3170
3171
3172 d_progress := 400;
3173
3174 IF (PO_LOG.d_stmt) THEN
3175 PO_LOG.stmt(d_module, d_progress, 'l_update_action_hist', l_update_action_hist);
3176 END IF;
3177
3178 IF (l_update_action_hist)
3179 THEN
3180
3181 d_progress := 410;
3182
3183 IF (l_hist_action IS NULL)
3184 THEN
3185
3186 IF (l_rollup_code = 'CLOSED')
3187 THEN
3188
3189 l_hist_action := 'CLOSE';
3190
3191 ELSIF (l_rollup_code = 'FINALLY CLOSED')
3192 THEN
3193
3194 l_hist_action := 'FINALLY CLOSE';
3195
3196 ELSE
3197
3198 l_hist_action := 'OPEN';
3199
3200 END IF; -- if (l_rollup_code = 'CLOSE')
3201
3202 END IF; -- if (l_hist_action IS NULL)
3203
3204 d_progress := 420;
3205 IF (PO_LOG.d_stmt) THEN
3206 PO_LOG.stmt(d_module, d_progress, 'l_hist_action', l_hist_action);
3207 END IF;
3208
3209 PO_DOCUMENT_ACTION_UTIL.handle_ctl_action_history(
3210 p_document_id => p_document_id
3211 , p_document_type => p_document_type
3212 , p_document_subtype => p_document_subtype
3213 , p_line_id => p_line_id
3214 , p_shipment_id => p_shipment_id
3215 , p_action => l_hist_action
3216 , p_user_id => p_user_id
3217 , p_login_id => p_login_id
3218 , p_reason => p_reason
3219 , x_return_status => l_ret_sts
3220 );
3221
3222 IF (l_ret_sts <> 'S')
3223 THEN
3224
3225 d_progress := 430;
3226 l_ret_sts := 'U';
3227 PO_DOCUMENT_ACTION_PVT.error_msg_append(d_module, d_progress, 'handle_ctl_action_history not successful');
3228 IF (PO_LOG.d_exc) THEN
3229 PO_LOG.exc(d_module, d_progress, 'handle_ctl_action_history not successful');
3230 END IF;
3231
3232 END IF;
3233
3234 ELSE
3235
3236 l_ret_sts := 'S';
3237
3238 END IF; -- if (l_action_hist);
3239
3240 x_return_status := l_ret_sts;
3241
3242 IF (PO_LOG.d_proc) THEN
3243 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
3244 PO_LOG.proc_end(d_module);
3245 END IF;
3246
3247 RETURN;
3248
3249 EXCEPTION
3250 WHEN others THEN
3251
3252 IF rollup_rel_open%ISOPEN
3253 THEN
3254 close rollup_rel_open;
3255 END IF;
3256
3257 IF rollup_rel_not_fc%ISOPEN
3258 THEN
3259 close rollup_rel_not_fc;
3260 END IF;
3261
3262 x_return_status := 'U';
3263
3264 PO_DOCUMENT_ACTION_PVT.error_msg_append(d_module, d_progress, SQLCODE, SQLERRM);
3265
3266 IF (PO_LOG.d_exc) THEN
3267 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
3268 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
3269 PO_LOG.proc_end(d_module);
3270 END IF;
3271
3272 RETURN;
3273
3274 END rollup_close_state;
3275
3276 PROCEDURE handle_manual_close_supply(
3277 p_document_id IN NUMBER
3278 , p_document_type IN VARCHAR2
3279 , p_action IN VARCHAR2
3280 , p_line_id IN NUMBER
3281 , p_shipment_id IN NUMBER
3282 , x_return_status OUT NOCOPY VARCHAR2
3283 )
3284 IS
3285
3286 d_module VARCHAR2(70) := 'po.plsql.PO_DOCUMENT_ACTION_CLOSE.handle_manual_close_supply';
3287 d_progress NUMBER;
3288 d_msg VARCHAR2(200);
3289
3290 l_ret_sts VARCHAR2(1);
3291 l_supply_action VARCHAR2(30);
3292 l_supply_ret BOOLEAN;
3293
3294 BEGIN
3295
3296 d_progress := 0;
3297 IF (PO_LOG.d_proc) THEN
3298 PO_LOG.proc_begin(d_module);
3299 PO_LOG.proc_begin(d_module, 'p_document_id', p_document_id);
3300 PO_LOG.proc_begin(d_module, 'p_document_type', p_document_type);
3301 PO_LOG.proc_begin(d_module, 'p_line_id', p_line_id);
3302 PO_LOG.proc_begin(d_module, 'p_shipment_id', p_shipment_id);
3303 PO_LOG.proc_begin(d_module, 'p_action', p_action);
3304 END IF;
3305
3306 d_progress := 10;
3307
3308 IF (p_document_type = 'PO')
3309 THEN
3310
3311 IF p_action IN (PO_DOCUMENT_ACTION_PVT.g_doc_action_OPEN,
3312 PO_DOCUMENT_ACTION_PVT.g_doc_action_OPEN_RCV)
3313 THEN
3314
3315 IF (p_shipment_id IS NOT NULL)
3316 THEN
3317
3318 l_supply_action := 'Create_PO_Shipment_Supply';
3319
3320 ELSIF (p_line_id IS NOT NULL)
3321 THEN
3322
3323 l_supply_action := 'Create_PO_Line_Supply';
3324
3325 ELSE
3326
3327 l_supply_action := 'Create_PO_Supply';
3328
3329 END IF; -- p_shipment_id is not null
3330
3331 ELSIF p_action IN (PO_DOCUMENT_ACTION_PVT.g_doc_action_CLOSE,
3332 PO_DOCUMENT_ACTION_PVT.g_doc_action_CLOSE_RCV,
3333 PO_DOCUMENT_ACTION_PVT.g_doc_action_FINALLY_CLOSE)
3334 THEN
3335
3336 IF (p_shipment_id IS NOT NULL)
3337 THEN
3338
3339 l_supply_action := 'Remove_PO_Shipment_Supply';
3340
3341 ELSIF (p_line_id IS NOT NULL)
3342 THEN
3343
3344 l_supply_action := 'Remove_PO_Line_Supply';
3345
3346 ELSE
3347
3348 l_supply_action := 'Remove_PO_Supply';
3349
3350 END IF; -- p_shipment_id is not null
3351
3352 END IF; -- p_action IN ...
3353
3354 ELSIF (p_document_type = 'RELEASE')
3355 THEN
3356
3357 IF p_action IN (PO_DOCUMENT_ACTION_PVT.g_doc_action_OPEN,
3358 PO_DOCUMENT_ACTION_PVT.g_doc_action_OPEN_RCV)
3359 THEN
3360
3361 IF (p_shipment_id IS NOT NULL)
3362 THEN
3363
3364 l_supply_action := 'Create_Release_Shipment_Supply';
3365
3366 ELSE
3367
3368 l_supply_action := 'Create_Release_Supply';
3369
3370 END IF; -- p_shipment_id is not null
3371
3372
3373 ELSIF p_action IN (PO_DOCUMENT_ACTION_PVT.g_doc_action_CLOSE,
3374 PO_DOCUMENT_ACTION_PVT.g_doc_action_CLOSE_RCV,
3375 PO_DOCUMENT_ACTION_PVT.g_doc_action_FINALLY_CLOSE)
3376 THEN
3377
3378 IF (p_shipment_id IS NOT NULL)
3379 THEN
3380
3381 l_supply_action := 'Remove_Release_Shipment';
3382
3383 ELSE
3384
3385 l_supply_action := 'Remove_Release_Supply';
3386
3387 END IF; -- p_shipment_id is not null
3388
3389
3390 END IF; -- p_action IN...
3391
3392 END IF; -- p_document_type = PO
3393
3394 d_progress := 20;
3395 IF (PO_LOG.d_stmt) THEN
3396 PO_LOG.stmt(d_module, d_progress, 'l_supply_action', l_supply_action);
3397 END IF;
3398
3399 l_supply_ret := PO_SUPPLY.po_req_supply(
3400 p_docid => p_document_id
3401 , p_lineid => p_line_id
3402 , p_shipid => p_shipment_id
3403 , p_action => l_supply_action
3404 , p_recreate_flag => FALSE
3405 , p_qty => 0
3406 , p_receipt_date => SYSDATE
3407 );
3408
3409 IF (NOT l_supply_ret)
3410 THEN
3411
3412 d_progress := 30;
3413 x_return_status := 'U';
3414 IF (PO_LOG.d_exc) THEN
3415 PO_LOG.exc(d_module, d_progress, 'po_req_supply call not successful');
3416 END IF;
3417 PO_DOCUMENT_ACTION_PVT.error_msg_append(d_module, d_progress, 'po_req_supply call not successful');
3418
3419 ELSE
3420
3421 d_progress := 40;
3422 x_return_status := 'S';
3423 IF (PO_LOG.d_stmt) THEN
3424 PO_LOG.stmt(d_module, d_progress, 'po_req_supply call was successful');
3425 END IF;
3426
3427 END IF;
3428
3429 IF (PO_LOG.d_proc) THEN
3430 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
3431 PO_LOG.proc_end(d_module);
3432 END IF;
3433
3434 RETURN;
3435
3436 EXCEPTION
3437 WHEN others THEN
3438 x_return_status := 'U';
3439
3440 PO_DOCUMENT_ACTION_PVT.error_msg_append(d_module, d_progress, SQLCODE, SQLERRM);
3441
3442 IF (PO_LOG.d_exc) THEN
3443 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
3444 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
3445 PO_LOG.proc_end(d_module);
3446 END IF;
3447
3448 RETURN;
3449
3450 END handle_manual_close_supply;
3451
3452 PROCEDURE handle_auto_close_supply(
3453 p_document_id IN NUMBER
3454 , p_document_type IN VARCHAR2
3455 , p_line_id IN NUMBER
3456 , p_shipment_id IN NUMBER
3457 , x_return_status OUT NOCOPY VARCHAR2
3458 )
3459 IS
3460
3461 d_module VARCHAR2(70) := 'po.plsql.PO_DOCUMENT_ACTION_CLOSE.handle_auto_close_supply';
3462 d_progress NUMBER;
3463 d_msg VARCHAR2(200);
3464
3465 l_ret_sts VARCHAR2(1);
3466 l_supply_action VARCHAR2(30);
3467 l_supply_ret BOOLEAN;
3468
3469 l_ship_id_tbl g_tbl_number;
3470 l_closed_code_tbl g_tbl_closed_code;
3471
3472 BEGIN
3473
3474 d_progress := 0;
3475 IF (PO_LOG.d_proc) THEN
3476 PO_LOG.proc_begin(d_module);
3477 PO_LOG.proc_begin(d_module, 'p_document_id', p_document_id);
3478 PO_LOG.proc_begin(d_module, 'p_document_type', p_document_type);
3479 PO_LOG.proc_begin(d_module, 'p_line_id', p_line_id);
3480 PO_LOG.proc_begin(d_module, 'p_shipment_id', p_shipment_id);
3481 END IF;
3482
3483 IF (p_shipment_id IS NOT NULL)
3484 THEN
3485
3486 d_progress := 10;
3487
3488 SELECT poll.line_location_id, poll.closed_code
3489 BULK COLLECT INTO l_ship_id_tbl, l_closed_code_tbl
3490 FROM po_line_locations poll
3491 WHERE poll.line_location_id = p_shipment_id
3492 AND NVL(poll.approved_flag, 'N') = 'Y'
3493 AND NVL(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED';
3494
3495 ELSIF (p_line_id IS NOT NULL)
3496 THEN
3497
3498 d_progress := 20;
3499
3500 SELECT poll.line_location_id, poll.closed_code
3501 BULK COLLECT INTO l_ship_id_tbl, l_closed_code_tbl
3502 FROM po_line_locations poll
3503 WHERE poll.po_line_id = p_line_id
3504 AND NVL(poll.approved_flag, 'N') = 'Y'
3505 AND NVL(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED';
3506
3507 ELSIF (p_document_type = 'RELEASE')
3508 THEN
3509
3510 d_progress := 30;
3511
3512 SELECT poll.line_location_id, poll.closed_code
3513 BULK COLLECT INTO l_ship_id_tbl, l_closed_code_tbl
3514 FROM po_line_locations poll
3515 WHERE poll.po_release_id = p_document_id
3516 AND NVL(poll.approved_flag, 'N') = 'Y'
3517 AND NVL(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED';
3518
3519 ELSE
3520
3521 d_progress := 40;
3522
3523 SELECT poll.line_location_id, poll.closed_code
3524 BULK COLLECT INTO l_ship_id_tbl, l_closed_code_tbl
3525 FROM po_line_locations poll
3526 WHERE poll.po_header_id = p_document_id
3527 AND NVL(poll.approved_flag, 'N') = 'Y'
3528 AND NVL(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED';
3529
3530 END IF; -- if p_shipment_id is not null
3531
3532 d_progress := 50;
3533
3534 -- Initialize return status to success.
3535 -- We will fail if any supply call fails.
3536 l_ret_sts := 'S';
3537
3538 FOR i IN 1..l_ship_id_tbl.COUNT
3539 LOOP
3540
3541 d_progress := 60;
3542
3543 IF (p_document_type = 'PO')
3544 THEN
3545
3546 IF (l_closed_code_tbl(i) IN ('CLOSED', 'CLOSED FOR RECEIVING'))
3547 THEN
3548
3549 l_supply_action := 'Remove_PO_Shipment_Supply';
3550
3551 ELSE
3552
3553 l_supply_action := 'Create_PO_Shipment_Supply';
3554
3555 END IF; -- if l_closed_code_tbl(i) IN ...
3556
3557 ELSIF (p_document_type = 'RELEASE')
3558 THEN
3559
3560 IF (l_closed_code_tbl(i) IN ('CLOSED', 'CLOSED FOR RECEIVING'))
3561 THEN
3562
3563 l_supply_action := 'Remove_Release_Shipment';
3564
3565 ELSE
3566
3567 l_supply_action := 'Create_Release_Shipment_Supply';
3568
3569 END IF; -- if l_closed_code_tbl(i) IN ...
3570
3571 END IF; -- if p_document_type = 'PO'
3572
3573 d_progress := 70;
3574
3575 IF (PO_LOG.d_stmt) THEN
3576 PO_LOG.stmt(d_module, d_progress, 'l_ship_it_tbl(i)', l_ship_id_tbl(i));
3577 PO_LOG.stmt(d_module, d_progress, 'l_supply_action', l_supply_action);
3578 END IF;
3579
3580 l_supply_ret := PO_SUPPLY.po_req_supply(
3581 p_docid => p_document_id
3582 , p_lineid => p_line_id
3583 , p_shipid => l_ship_id_tbl(i)
3584 , p_action => l_supply_action
3585 , p_recreate_flag => FALSE
3586 , p_qty => 0
3587 , p_receipt_date => SYSDATE
3588 );
3589
3590 d_progress := 80;
3591
3592 IF (PO_LOG.d_stmt) THEN
3593 PO_LOG.stmt(d_module, d_progress, 'l_supply_ret', l_supply_ret);
3594 END IF;
3595
3596 IF (NOT l_supply_ret)
3597 THEN
3598
3599 d_progress := 90;
3600
3601 l_ret_sts := 'U';
3602 IF (PO_LOG.d_exc) THEN
3603 PO_LOG.exc(d_module, d_progress, 'supply action not successful');
3604 PO_LOG.stmt(d_module, d_progress, 'l_ship_it_tbl(i)', l_ship_id_tbl(i));
3605 PO_LOG.stmt(d_module, d_progress, 'l_supply_action', l_supply_action);
3606 END IF;
3607
3608 EXIT;
3609
3610 END IF;
3611
3612 END LOOP;
3613
3614 x_return_status := l_ret_sts;
3615
3616 IF (PO_LOG.d_proc) THEN
3617 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
3618 PO_LOG.proc_end(d_module);
3619 END IF;
3620
3621 RETURN;
3622
3623 EXCEPTION
3624 WHEN others THEN
3625 x_return_status := 'U';
3626
3627 PO_DOCUMENT_ACTION_PVT.error_msg_append(d_module, d_progress, SQLCODE, SQLERRM);
3628
3629 IF (PO_LOG.d_exc) THEN
3630 PO_LOG.exc(d_module, d_progress, SQLCODE || SQLERRM);
3631 PO_LOG.proc_end(d_module, 'x_return_status', x_return_status);
3632 PO_LOG.proc_end(d_module);
3633 END IF;
3634
3635 RETURN;
3636
3637 END handle_auto_close_supply;
3638
3639
3640 END PO_DOCUMENT_ACTION_CLOSE;