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