1 PACKAGE BODY PO_Document_Control_PVT AS
2 /* $Header: POXVDCOB.pls 120.49.12020000.4 2013/04/10 06:02:13 shithang ship $ */
3
4 --< Bug 3194665 Start >
5 -- Refactored debugging
6 g_debug_stmt CONSTANT BOOLEAN := PO_DEBUG.is_debug_stmt_on;
7 g_debug_unexp CONSTANT BOOLEAN := PO_DEBUG.is_debug_unexp_on;
8
9 -- Moved table type declaration to spec file
10 --< Bug 3194665 End >
11
12 g_module_prefix CONSTANT VARCHAR2(50) := 'po.plsql.' || g_pkg_name || '.';
13 g_approval_initiated_flag BOOLEAN := FALSE;
14 g_cancel_flag_reset_flag BOOLEAN := FALSE;
15 /**
16 * Private Procedure: lock_doc_row
17 * Requires: API message list has been initialized if p_init_msg_list is false.
18 * Modifies: API message list
19 * Effects: Locks the row with ID p_doc_id for this document. Appends to API
20 * message list on error.
21 * Returns:
22 * x_return_status - FND_API.G_RET_STS_SUCCESS if successfully locked row
23 * FND_API.G_RET_STS_ERROR if lock failed
24 * FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
25 */
26 PROCEDURE lock_doc_row
27 (p_api_version IN NUMBER,
28 x_return_status OUT NOCOPY VARCHAR2,
29 p_doc_type IN PO_DOCUMENT_TYPES.document_type_code%TYPE,
30 p_doc_id IN NUMBER)
31 IS
32
33 l_api_name CONSTANT VARCHAR2(30) := 'lock_doc_row';
34 l_api_version CONSTANT NUMBER := 1.0;
35 l_lock_row VARCHAR2(30);
36 RESOURCE_BUSY exception;
37 pragma exception_init (RESOURCE_BUSY, -54 ); --<HTML Agreements R12>
38 BEGIN
39 -- Start standard API initialization
40 IF NOT FND_API.compatible_api_call(l_api_version, p_api_version,
41 l_api_name, g_pkg_name)
42 THEN
43 RAISE FND_API.g_exc_unexpected_error;
44 END IF;
45 x_return_status := FND_API.g_ret_sts_success;
46 -- End standard API initialization
47
48 IF (g_debug_stmt) THEN
49 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
50 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix || l_api_name ||
51 '.invoked', 'Type: ' || NVL(p_doc_type,'null') ||
52 ', ID: ' || NVL(TO_CHAR(p_doc_id),'null'));
53 END IF;
54 END IF;
55
56
57 IF (p_doc_type IN ('PO','PA')) THEN
58 SELECT 'Lock header'
59 INTO l_lock_row
60 FROM po_headers poh
61 WHERE poh.po_header_id = p_doc_id
62 FOR UPDATE NOWAIT;
63 ELSIF (p_doc_type = 'RELEASE') THEN
64 SELECT 'Lock release'
65 INTO l_lock_row
66 FROM po_releases por
67 WHERE por.po_release_id = p_doc_id
68 FOR UPDATE NOWAIT;
69 ELSIF (p_doc_type = 'REQUISITION') THEN
70 SELECT 'Lock req'
71 INTO l_lock_row
72 FROM po_requisition_headers porh
73 WHERE porh.requisition_header_id = p_doc_id
74 FOR UPDATE NOWAIT;
75 ELSE
76 -- This document type is not supported
77 FND_MESSAGE.set_name('PO','PO_INVALID_DOC_TYPE');
78 FND_MESSAGE.set_token('TYPE',p_doc_type);
79 IF (g_debug_stmt) THEN
80 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR) THEN
81 FND_LOG.message(FND_LOG.level_error, g_module_prefix || l_api_name
82 || '.invalid_doc_type', FALSE);
83 END IF;
84 END IF;
85 FND_MSG_PUB.add;
86 RAISE FND_API.g_exc_error;
87 END IF; --<if p_doc_type ...>
88
89 EXCEPTION
90 --<HTML Agreements R12 Start>
91 --Handling deadlock with proper error message
92 WHEN RESOURCE_BUSY THEN
93 x_return_status := FND_API.g_ret_sts_error;
94 FND_MESSAGE.set_name('PO','PO_ALL_CANNOT_RESERVE_RECORD');
95 FND_MSG_PUB.add;
96 --<HTML Agreements R12 End>
97 WHEN NO_DATA_FOUND OR TOO_MANY_ROWS THEN
98 x_return_status := FND_API.g_ret_sts_error;
99 FND_MESSAGE.set_name('PO','PO_CONTROL_LOCK_FAILED');
100 IF (g_debug_stmt) THEN
101 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR) THEN
102 FND_LOG.message(FND_LOG.level_error, g_module_prefix||l_api_name||
103 '.lock_failed', FALSE);
104 END IF;
105 END IF;
106 FND_MSG_PUB.add;
107 WHEN FND_API.g_exc_error THEN
108 x_return_status := FND_API.g_ret_sts_error;
109 WHEN FND_API.g_exc_unexpected_error THEN
110 x_return_status := FND_API.g_ret_sts_unexp_error;
111 WHEN OTHERS THEN
112 x_return_status := FND_API.g_ret_sts_unexp_error;
113 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
114 IF (g_debug_unexp) THEN
115 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
116 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
117 l_api_name || '.others_exception', 'Exception');
118 END IF;
119 END IF;
120 END lock_doc_row;
121
122
123
124
125
126 --------------------------------------------------------------------------------
127 --<Bug 14254141 :Cancel Refactoring Project
128 --Start of Comments
129 --Name: do_approve_on_cancel
130 --Function:
131 -- called after the successful cancel action
132 -- Approve the document if the document's current status is Requires Reapproval
133 -- This will be called if p_launch_approval_flag is 'Y'
134 -- And the docuemnts original status was 'Approved'
135 -- These checks are handled in the caller of this routine
136 --Parameters:
137 --IN:
138 -- p_doc_type
139 -- p_doc_subtype
140 -- p_doc_id
141 -- p_communication_method_option
142 -- p_communication_method_value
143 -- p_source
144 -- p_note_to_vendor
145
146 --
147 --IN OUT :
148 --OUT :
149
150 -- x_return_status
151 -- FND_API.G_RET_STS_SUCCESS if communicate action succeeds
152 -- FND_API.G_RET_STS_ERROR if communicate action fails
153 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
154 --
155 --End of Comments
156 --------------------------------------------------------------------------------
157
158
159 PROCEDURE do_approve_on_cancel(
160 p_doc_type IN VARCHAR2,
161 p_doc_subtype IN VARCHAR2,
162 p_doc_id IN NUMBER,
163 p_communication_method_option IN VARCHAR2,
164 p_communication_method_value IN VARCHAR2,
165 p_source IN VARCHAR2,
166 p_note_to_vendor IN VARCHAR2,
167 x_exception_msg OUT NOCOPY VARCHAR2,
168 x_return_status OUT NOCOPY VARCHAR2
169 )
170 IS
171
172 d_api_name CONSTANT VARCHAR2(30) := 'do_approve_on_cancel';
173 d_api_version CONSTANT NUMBER := 1.0;
174 d_module CONSTANT VARCHAR2(100) := g_pkg_name|| d_api_name;
175 l_progress VARCHAR2(3) := '000' ;
176
177 l_auth_status po_headers.authorization_status%TYPE;
178 l_sub_check_status VARCHAR2(1);
179 l_online_report_id NUMBER;
180 l_check_asl BOOLEAN;
181 l_approval_path_id NUMBER;
182
183
184
185 BEGIN
186 x_return_status := FND_API.G_RET_STS_SUCCESS;
187
188
189 IF g_debug_stmt THEN
190 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
191 PO_DEBUG.debug_begin(d_module);
192 PO_DEBUG.debug_var(d_module, l_progress, 'p_doc_type', p_doc_type);
193 PO_DEBUG.debug_var(d_module, l_progress, 'p_doc_subtype', p_doc_subtype);
194 PO_DEBUG.debug_var(d_module, l_progress, 'p_doc_id', p_doc_id);
195 PO_DEBUG.debug_var(d_module, l_progress, 'p_communication_method_value', p_communication_method_value);
196 PO_DEBUG.debug_var(d_module, l_progress, 'p_communication_method_option', p_communication_method_option);
197 PO_DEBUG.debug_var(d_module, l_progress, 'p_source', p_source);
198
199 END IF;
200 END IF;
201
202 IF p_doc_subtype in ('BLANKET', 'STANDARD') THEN
203 l_check_asl := TRUE;
204 ELSE
205 l_check_asl := FALSE;
206
207 END IF ;
208
209 IF g_debug_stmt THEN
210 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
211 PO_DEBUG.debug_var(d_module, l_progress, 'l_check_asl', l_check_asl);
212 END IF;
213 END IF;
214
215 BEGIN
216
217 IF p_doc_type <> 'RELEASE' THEN
218
219 l_progress :='001';
220
221 SELECT authorization_status
222 INTO l_auth_status
223 FROM po_headers_all
224 WHERE po_header_id=p_doc_id;
225
226 ELSE
227
228 l_progress :='002';
229
230 SELECT authorization_status
231 INTO l_auth_status
232 FROM po_releases_all
233 WHERE po_release_id=p_doc_id;
234 END IF;
235
236 EXCEPTION
237 WHEN No_Data_Found THEN
238 l_auth_status:=NULL;
239 WHEN OTHERS THEN
240 RAISE;
241 END;
242
243 IF g_debug_stmt THEN
244 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
245 PO_DEBUG.debug_var(d_module, l_progress, 'l_auth_status', l_auth_status);
246 END IF;
247 END IF;
248
249 l_progress :='003';
250
251 IF l_auth_status = po_document_action_pvt.g_doc_status_REAPPROVAL THEN
252
253 PO_DOCUMENT_CHECKS_GRP.po_submission_check(
254 p_api_version => 1.0,
255 p_action_requested => 'DOC_SUBMISSION_CHECK',
256 p_document_type => p_doc_type,
257 p_document_subtype => p_doc_subtype,
258 p_document_id => p_doc_id,
259 p_check_asl => l_check_asl,
260 x_return_status => x_return_status,
261 x_sub_check_status => l_sub_check_status,
262 x_msg_data => x_exception_msg,
263 x_online_report_id => l_online_report_id
264 );
265
266 IF (x_return_status = FND_API.g_ret_sts_error) THEN
267 RAISE FND_API.g_exc_error;
268 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
269 RAISE FND_API.g_exc_unexpected_error;
270 END IF;
271
272 IF g_debug_stmt THEN
273 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
274 PO_DEBUG.debug_var(d_module, l_progress, 'l_online_report_id', l_online_report_id);
275 PO_DEBUG.debug_var(d_module, l_progress, 'l_sub_check_status', l_sub_check_status);
276 PO_DEBUG.debug_var(d_module, l_progress, 'x_exception_msg', x_exception_msg);
277 END IF;
278 END IF;
279
280
281 l_progress :='004';
282
283 --Add all the messages to the message list
284 IF x_return_status =FND_API.G_RET_STS_SUCCESS
285 AND l_sub_check_status = FND_API.G_RET_STS_ERROR
286 AND l_online_report_id IS NOT NULL THEN
287
288 PO_Document_Control_PVT.add_online_report_msgs(
289 p_api_version => 1.0
290 ,p_init_msg_list => FND_API.G_FALSE
291 ,x_return_status => x_return_status
292 ,p_online_report_id => l_online_report_id);
293
294 RAISE FND_API.g_exc_error;
295 END IF;
296
297 IF (x_return_status = FND_API.g_ret_sts_error) THEN
298 RAISE FND_API.g_exc_error;
299 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
300 RAISE FND_API.g_exc_unexpected_error;
301 END IF;
302
303
304
305 l_progress := '005' ;
306
307 SELECT podt.default_approval_path_id
308 INTO l_approval_path_id
309 FROM po_document_types podt
310 WHERE podt.document_type_code = p_doc_type
311 AND podt.document_subtype = p_doc_subtype;
312
313 IF g_debug_stmt THEN
314 PO_DEBUG.debug_var(d_module, l_progress, 'l_approval_path_id', l_approval_path_id);
315 END IF;
316
317 l_progress :='006';
318
319 PO_DOCUMENT_ACTION_PVT.do_approve(
320 p_document_id => p_doc_id,
321 p_document_type => p_doc_type,
322 p_document_subtype => p_doc_subtype,
323 p_note => p_note_to_vendor,
324 p_approval_path_id => l_approval_path_id,
325 x_return_status => x_return_status,
326 x_exception_msg => x_exception_msg
327 );
328
329 IF (x_return_status = FND_API.g_ret_sts_error) THEN
330 RAISE FND_API.g_exc_error;
331 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
332 RAISE FND_API.g_exc_unexpected_error;
333 END IF;
334
335 g_approval_initiated_flag := TRUE;
336
337
338 END IF;
339
340
341
342 EXCEPTION
343 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
344 FND_MSG_PUB.add_exc_msg(g_module_prefix, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
345 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
346
347
348 WHEN FND_API.G_EXC_ERROR THEN
349 x_return_status := FND_API.G_RET_STS_ERROR;
350 FND_MSG_PUB.add_exc_msg(g_module_prefix, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
351
352
353 WHEN OTHERS THEN
354 FND_MSG_PUB.add_exc_msg(g_module_prefix, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
355 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
356
357
358 END do_approve_on_cancel;
359
360 ---------------------------------------------------------------------------
361 --Start of Comments
362 --Name: control_document
363 --Pre-reqs:
364 -- FND_MSG_PUB has been initialized if p_init_msg_list is false.
365 --Modifies:
366 -- All columns related to the control action, and WHO columns.
367 -- PO_ONLINE_REPORT_TEXT.
368 -- FND_MSG_PUB.
369 --Locks:
370 -- Document at header level, and at entity level(s) specified.
371 --Function:
372 -- Performs the control action p_action on the specified document. Currently,
373 -- only the 'CANCEL' action is supported. If the control action was
374 -- successful, the document will be updated at the specified entity level.
375 -- Executes at shipment level if p_doc_id, p_doc_line_id, and
376 -- p_doc_line_loc_id are not NULL. Executes at line level if only p_doc_id
377 -- and p_doc_line_id are not NULL. Executes at header level if only p_doc_id
378 -- is not NULL. The document will be printed if it is a PO, PA, or RELEASE,
379 -- and the p_print_flag is 'Y'. All changes will be committed upon success if
380 -- p_commit is FND_API.G_TRUE. Appends to FND_MSG_PUB message list on error
381 --Parameters:
382 --IN:
383 --p_api_version
384 --p_init_msg_list
385 --p_commit
386 --p_doc_type
387 -- 'PO', 'PA', or 'RELEASE'.
388 --p_doc_subtype
389 -- 'STANDARD', 'PLANNED', 'BLANKET', 'CONTRACT', 'SCHEDULED'.
390 --p_doc_id
391 --p_doc_line_id
392 --p_doc_line_loc_id
393 --p_source
394 --p_action
395 -- Only supports 'CANCEL' action.
396 --p_action_date
397 --p_cancel_reason
398 --p_cancel_reqs_flag
399 -- 'Y' or 'N'. NULL is handled as 'N'. This value is validated against the
400 -- current OU's Purchasing Setup, and may be overridden.
401 --p_print_flag
402 -- 'Y' or 'N'. NULL is handled as 'N'.
403 --p_note_to_vendor
404 --p_communication_method_option
405 -- Communicattion Method to be used
406 --p_communication_method_value
407 -- Email Address or Fax Number
408 --OUT:
409 --x_return_status
410 -- FND_API.g_ret_sts_success - on success
411 -- FND_API.g_ret_sts_error - expected error
412 -- FND_API.g_ret_sts_unexp_error - unexpected error
413 --Testing:
414 --End of Comments
415 ---------------------------------------------------------------------------
416
417 PROCEDURE control_document
418 (p_api_version IN NUMBER,
419 p_init_msg_list IN VARCHAR2,
420 p_commit IN VARCHAR2,
421 x_return_status OUT NOCOPY VARCHAR2,
422 p_doc_type IN VARCHAR2,
423 p_doc_subtype IN VARCHAR2,
424 p_doc_id IN NUMBER,
425 p_doc_line_id IN NUMBER,
426 p_doc_line_loc_id IN NUMBER,
427 p_source IN VARCHAR2,
428 p_action IN VARCHAR2,
429 p_action_date IN DATE,
430 p_cancel_reason IN VARCHAR2,
431 p_cancel_reqs_flag IN VARCHAR2,
432 p_print_flag IN VARCHAR2,
433 p_note_to_vendor IN VARCHAR2,
434 p_use_gldate IN VARCHAR2, -- <ENCUMBRANCE FPJ>
435 p_launch_approvals_flag IN VARCHAR2 := 'Y', -- <CancelPO FPJ>
436 p_communication_method_option IN VARCHAR2 , --<HTML Agreements R12>
437 p_communication_method_value IN VARCHAR2, --<HTML Agreements R12>
438 p_online_report_id OUT NOCOPY NUMBER, -- Bug 8831247
439 p_caller IN VARCHAR2 --Bug 6603493
440 )
441 IS
442
443 l_api_name CONSTANT VARCHAR2(30) := 'control_document';
444 l_api_version CONSTANT NUMBER := 1.0;
445 l_progress VARCHAR2(3) :='000';
446
447 d_module CONSTANT VARCHAR2(100) := g_pkg_name|| l_api_name;
448
449 l_msg_data VARCHAR2(2000);
450 l_msg_count NUMBER;
451 l_old_auth_status po_headers.authorization_status%TYPE; --Bug5142892
452 -- <Bug 14207546 :Cancel Refactoring Project>
453 l_entity_dtl_rec_tbl po_document_action_pvt.entity_dtl_rec_type_tbl;
454 l_exc_msg VARCHAR2(2000);
455 l_return_code VARCHAR2(25);
456 l_communication_method_option VARCHAR2(30);
457 l_communication_method_value VARCHAR2(2000); --Bug 15984307
458
459
460 BEGIN
461 -- Start standard API initialization
462 SAVEPOINT control_document_PVT;
463 IF FND_API.to_boolean(p_init_msg_list) THEN
464 FND_MSG_PUB.initialize;
465 END IF;
466 IF NOT FND_API.compatible_api_call(l_api_version, p_api_version,
467 l_api_name, g_pkg_name)
468 THEN
469 RAISE FND_API.g_exc_unexpected_error;
470 END IF;
471
472 IF g_debug_stmt THEN
473 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
474 PO_DEBUG.debug_begin(d_module);
475 PO_DEBUG.debug_var(d_module, l_progress, 'p_commit', p_commit);
476 PO_DEBUG.debug_var(d_module, l_progress, 'p_doc_type', p_doc_type);
477 PO_DEBUG.debug_var(d_module, l_progress, 'p_doc_subtype', p_doc_subtype);
478 PO_DEBUG.debug_var(d_module, l_progress, 'p_doc_id', p_doc_id);
479 PO_DEBUG.debug_var(d_module, l_progress, 'p_doc_line_id', p_doc_line_id);
480 PO_DEBUG.debug_var(d_module, l_progress, 'p_doc_line_loc_id', p_doc_line_loc_id);
481 PO_DEBUG.debug_var(d_module, l_progress, 'p_source', p_source);
482 PO_DEBUG.debug_var(d_module, l_progress, 'p_action', p_action);
483 PO_DEBUG.debug_var(d_module, l_progress, 'p_action_date', p_action_date);
484 PO_DEBUG.debug_var(d_module, l_progress, 'p_cancel_reason', p_cancel_reason);
485 PO_DEBUG.debug_var(d_module, l_progress, 'p_cancel_reqs_flag', p_cancel_reqs_flag);
486 PO_DEBUG.debug_var(d_module, l_progress, 'p_print_flag', p_print_flag);
487 PO_DEBUG.debug_var(d_module, l_progress, 'p_note_to_vendor', p_note_to_vendor);
488 PO_DEBUG.debug_var(d_module, l_progress, 'p_use_gldate', p_use_gldate);
489 PO_DEBUG.debug_var(d_module, l_progress, 'p_launch_approvals_flag', p_launch_approvals_flag);
490 PO_DEBUG.debug_var(d_module, l_progress, 'p_caller', p_caller);
491 PO_DEBUG.debug_var(d_module, l_progress, 'p_communication_method_value', p_communication_method_value);
492 PO_DEBUG.debug_var(d_module, l_progress, 'p_communication_method_option', p_communication_method_option);
493
494 END IF;
495 END IF;
496
497
498 x_return_status := FND_API.g_ret_sts_success;
499 l_communication_method_option :=p_communication_method_option;
500 l_communication_method_value :=p_communication_method_value;
501
502
503 --Resetting the global variables which keep track of whether
504 --approval is submitted
505 --g_approval_initiated_flag flag will only be
506 --be toggled if the document is submitted for approval below.
507 g_approval_initiated_flag := FALSE;
508
509
510 BEGIN
511 IF p_doc_type <> 'RELEASE' THEN
512
513 l_progress :='001';
514
515 SELECT authorization_status
516 INTO l_old_auth_status
517 FROM po_headers_all
518 WHERE po_header_id=p_doc_id;
519
520 ELSE
521
522 l_progress :='002';
523
524 SELECT authorization_status
525 INTO l_old_auth_status
526 FROM po_releases_all
527 WHERE po_release_id=p_doc_id;
528 END IF;
529
530 EXCEPTION
531 WHEN No_Data_Found THEN
532 l_old_auth_status:=NULL;
533 WHEN OTHERS THEN
534 RAISE;
535 END;
536
537 IF g_debug_stmt THEN
538 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
539 PO_DEBUG.debug_var(d_module, l_progress, 'l_old_auth_status', l_old_auth_status);
540 END IF;
541 END IF;
542
543
544 l_progress :='003';
545
546 IF (p_action = 'CANCEL') THEN
547
548 -- <Bug 14254141 :Cancel Refactoring Project Starts>
549 l_entity_dtl_rec_tbl := po_document_action_pvt.entity_dtl_rec_type_tbl();
550 l_entity_dtl_rec_tbl.extend;
551 l_entity_dtl_rec_tbl(1).doc_id :=p_doc_id;
552 l_entity_dtl_rec_tbl(1).document_type :=p_doc_type;
553 l_entity_dtl_rec_tbl(1).document_subtype :=p_doc_subtype;
554
555 IF p_doc_line_loc_id IS NOT NULL THEN
556 l_entity_dtl_rec_tbl(1).entity_level :=PO_Document_Cancel_PVT.c_entity_level_SHIPMENT;
557 l_entity_dtl_rec_tbl(1).entity_id := p_doc_line_loc_id;
558
559 ELSIF p_doc_line_id IS NOT NULL THEN
560 l_entity_dtl_rec_tbl(1).entity_level :=PO_Document_Cancel_PVT.c_entity_level_LINE;
561 l_entity_dtl_rec_tbl(1).entity_id := p_doc_line_id;
562
563 ELSE
564 l_entity_dtl_rec_tbl(1).entity_level :=PO_Document_Cancel_PVT.c_entity_level_HEADER;
565 l_entity_dtl_rec_tbl(1).entity_id := p_doc_id;
566 END IF;
567
568 l_entity_dtl_rec_tbl(1).entity_action_date :=p_action_date;
569 l_entity_dtl_rec_tbl(1).process_entity_flag :='Y';
570 l_entity_dtl_rec_tbl(1).recreate_demand_flag :='N';
571
572 PO_DOCUMENT_ACTION_PVT.do_cancel(
573 p_entity_dtl_rec => l_entity_dtl_rec_tbl,
574 p_reason => p_cancel_reason,
575 p_action => PO_DOCUMENT_ACTION_PVT.g_doc_action_CANCEL,
576 p_action_date => p_action_date,
577 p_use_gl_date => p_use_gldate,
578 p_cancel_reqs_flag => p_cancel_reqs_flag,
579 p_note_to_vendor => p_note_to_vendor,
580 p_caller => p_source,
581 x_online_report_id => p_online_report_id,
582 x_return_status => x_return_status,
583 x_exception_msg => l_exc_msg,
584 x_return_code => l_return_code);
585
586
587
588 IF g_debug_stmt THEN
589 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
590 PO_DEBUG.debug_var(d_module,l_progress,'l_return_code',l_return_code);
591 PO_DEBUG.debug_var(d_module,l_progress,'x_return_status',x_return_status);
592 PO_DEBUG.debug_var(d_module,l_progress,'p_online_report_id',p_online_report_id);
593 END IF;
594 END IF;
595
596 -- If the procedure does not complete successfully raise the
597 -- appropriate exception
598 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
599 RAISE FND_API.g_exc_error;
600 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
601 RAISE FND_API.g_exc_unexpected_error;
602 END IF;
603
604
605 l_progress :='004';
606
607 --Add all the messages to the message list
608 IF l_return_code ='F' AND p_online_report_id IS NOT NULL THEN
609 PO_Document_Control_PVT.add_online_report_msgs(
610 p_api_version => 1.0
611 ,p_init_msg_list => FND_API.G_FALSE
612 ,x_return_status => x_return_status
613 ,p_online_report_id => p_online_report_id);
614
615 RAISE FND_API.g_exc_error;
616 END IF;
617
618 l_progress :='005';
619
620 IF (p_launch_approvals_flag = 'Y'
621 AND l_old_auth_status ='APPROVED') THEN
622
623 do_approve_on_cancel(
624 p_doc_type => p_doc_type,
625 p_doc_subtype => p_doc_subtype,
626 p_doc_id => p_doc_id,
627 p_communication_method_option => p_communication_method_option,
628 p_communication_method_value => p_communication_method_value,
629 p_note_to_vendor => p_note_to_vendor,
630 p_source => p_source,
631 x_exception_msg => l_exc_msg,
632 x_return_status => x_return_status
633
634 );
635
636 IF (x_return_status = FND_API.g_ret_sts_error) THEN
637 RAISE FND_API.g_exc_error;
638 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
639 RAISE FND_API.g_exc_unexpected_error;
640 END IF;
641
642 END IF;
643
644 l_progress :='006';
645
646 -- Bug 14254141 :Cancel Refactoring Project <Communicate>
647 -- If the Cancel action is successful
648 -- Communicate the same to the supplier
649 IF p_print_flag ='Y' THEN
650 l_communication_method_option := 'PRINT';
651 l_communication_method_value :=NULL;
652 END IF;
653
654 doc_communicate_oncancel(
655 p_doc_type => p_doc_type,
656 p_doc_subtype => p_doc_subtype,
657 p_doc_id => p_doc_id,
658 p_communication_method_option => l_communication_method_option,
659 p_communication_method_value => l_communication_method_value,
660 x_return_status => x_return_status
661 );
662
663 -- If the procedure does not complete successfully raise the
664 -- appropriate exception
665 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
666 RAISE FND_API.g_exc_error;
667 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
668 RAISE FND_API.g_exc_unexpected_error;
669 END IF;
670 ELSE
671
672 l_progress :='007';
673
674 FND_MESSAGE.set_name('PO','PO_CONTROL_INVALID_ACTION');
675 FND_MESSAGE.set_token('ACTION',p_action);
676
677 IF g_debug_stmt THEN
678 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
679 PO_DEBUG.debug_stmt(d_module,l_progress,'invalid_action');
680 END IF;
681 END IF;
682
683 FND_MSG_PUB.add;
684 RAISE FND_API.g_exc_error;
685
686 END IF; --<if p_action CANCEL>
687
688 IF g_debug_stmt THEN
689 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
690 PO_DEBUG.debug_stmt(d_module,l_progress,'Before PO_DELREC_PVT.create_update_delrec Call');
691 END IF;
692 END IF;
693
694
695
696 PO_DELREC_PVT.create_update_delrec(
697 p_api_version => 1.0,
698 x_return_status => x_return_status,
699 x_msg_count => l_msg_count,
700 x_msg_data => l_msg_data,
701 p_action => 'CANCEL',
702 p_doc_type => p_doc_type,
703 p_doc_subtype => p_doc_subtype,
704 p_doc_id => p_doc_id,
705 p_line_id => p_doc_line_id,
706 p_line_location_id => p_doc_line_loc_id);
707
708 IF g_debug_stmt THEN
709 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
710 PO_DEBUG.debug_stmt(d_module,l_progress,'After PO_DELREC_PVT.create_update_delrec Call');
711 PO_DEBUG.debug_var(d_module,l_progress,'x_return_status',x_return_status);
712 END IF;
713
714 END IF;
715
716 IF (x_return_status = FND_API.g_ret_sts_error) THEN
717 RAISE FND_API.g_exc_error;
718 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
719 RAISE FND_API.g_exc_unexpected_error;
720 END IF;
721
722 --Standard API check of p_commit
723 IF FND_API.to_boolean(p_commit) THEN
724 COMMIT WORK;
725 END IF;
726 EXCEPTION
727 WHEN FND_API.g_exc_error THEN
728 ROLLBACK TO control_document_PVT;
729 x_return_status := FND_API.g_ret_sts_error;
730 WHEN FND_API.g_exc_unexpected_error THEN
731 ROLLBACK TO control_document_PVT;
732 x_return_status := FND_API.g_ret_sts_unexp_error;
733 WHEN OTHERS THEN
734 ROLLBACK TO control_document_PVT;
735 x_return_status := FND_API.g_ret_sts_unexp_error;
736 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
737 IF (g_debug_unexp) THEN
738 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
739 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
740 l_api_name || '.others_exception', 'Exception');
741 END IF;
742 END IF;
743 END control_document;
744
745
746 /**
747 * Public Procedure: init_action_date
748 * Requires: API message list has been initialized if p_init_msg_list is false.
749 * Modifies: API message list.
750 * Effects: Checks if CBC is enabled, storing the result in x_cbc_enabled. If
751 * x_action_date is NULL, then sets it to a valid CBC accounting date if CBC
752 * is enabled. Otherwise, sets it to the current system date. Appends message
753 * to API message list on error.
754 * Returns:
755 * x_return_status - FND_API.G_RET_STS_SUCCESS if initialization is successful
756 * FND_API.G_RET_STS_ERROR if error initializing action date
757 * FND_API.G_RET_STS_UNEXP_ERROR if unexpected error
758 * x_action_date - The action date, truncated.
759 * x_cbc_enabled - 'Y' if CBC is enabled, 'N' otherwise.
760 */
761 PROCEDURE init_action_date
762 (p_api_version IN NUMBER,
763 p_init_msg_list IN VARCHAR2,
764 x_return_status OUT NOCOPY VARCHAR2,
765 p_doc_type IN PO_DOCUMENT_TYPES.document_type_code%TYPE,
766 p_doc_subtype IN PO_DOCUMENT_TYPES.document_subtype%TYPE,
767 p_doc_id IN NUMBER,
768 x_action_date IN OUT NOCOPY DATE,
769 x_cbc_enabled OUT NOCOPY VARCHAR2)
770 IS
771
772 l_api_name CONSTANT VARCHAR2(30) := 'init_action_date';
773 l_api_version CONSTANT NUMBER := 1.0;
774 l_msg_count NUMBER;
775 l_msg_data VARCHAR2(2000);
776
777 BEGIN
778 -- Start standard API initialization
779 IF FND_API.to_boolean(p_init_msg_list) THEN
780 FND_MSG_PUB.initialize;
781 END IF;
782 IF NOT FND_API.compatible_api_call(l_api_version, p_api_version,
783 l_api_name, g_pkg_name)
784 THEN
785 RAISE FND_API.g_exc_unexpected_error;
786 END IF;
787 x_return_status := FND_API.g_ret_sts_success;
788 -- End standard API initialization
789
790 IF (g_debug_stmt) THEN
791 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
792 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix || l_api_name ||
793 '.invoked', 'Type: ' || NVL(p_doc_type,'null') ||
794 ', ID: ' || NVL(TO_CHAR(p_doc_id),'null'));
795 END IF;
796 END IF;
797
798 IGC_CBC_PO_GRP.is_cbc_enabled
799 ( p_api_version => 1.0,
800 p_init_msg_list => FND_API.G_FALSE,
801 p_commit => FND_API.G_FALSE,
802 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
803 x_return_status => x_return_status,
804 x_msg_count => l_msg_count,
805 x_msg_data => l_msg_data,
806 x_cbc_enabled => x_cbc_enabled );
807 IF (x_return_status = FND_API.g_ret_sts_error) THEN
808 RAISE FND_API.g_exc_error;
809 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
810 RAISE FND_API.g_exc_unexpected_error;
811 END IF;
812
813 IF (g_debug_stmt) THEN
814 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
815 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, g_module_prefix || l_api_name ||
816 '.is_cbc_enabled', 'Is CBC enabled: ' ||
817 NVL(x_cbc_enabled,'N'));
818 END IF;
819 END IF;
820
821 -- Set the action date if it was not passed in
822 IF (x_action_date IS NULL) THEN
823
824 get_action_date( p_api_version => 1.0,
825 p_init_msg_list => FND_API.G_FALSE,
826 x_return_status => x_return_status,
827 p_doc_type => p_doc_type,
828 p_doc_subtype => p_doc_subtype,
829 p_doc_id => p_doc_id,
830 p_cbc_enabled => x_cbc_enabled,
831 x_action_date => x_action_date );
832 IF (x_return_status = FND_API.g_ret_sts_error) THEN
833 RAISE FND_API.g_exc_error;
834 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
835 RAISE FND_API.g_exc_unexpected_error;
836 END IF;
837
838 ELSE
839
840 x_action_date := TRUNC(x_action_date);
841
842 END IF; --<if x_action_date is null ...>
843
844 EXCEPTION
845 WHEN FND_API.g_exc_error THEN
846 x_return_status := FND_API.g_ret_sts_error;
847 WHEN FND_API.g_exc_unexpected_error THEN
848 x_return_status := FND_API.g_ret_sts_unexp_error;
849 WHEN OTHERS THEN
850 x_return_status := FND_API.g_ret_sts_unexp_error;
851 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
852 IF (g_debug_unexp) THEN
853 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
854 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
855 l_api_name || '.others_exception', 'Exception');
856 END IF;
857 END IF;
858 END init_action_date;
859
860
861 /**
862 * Public Procedure: get_action_date
863 * Requires: API message list has been initialized if p_init_msg_list is false.
864 * Modifies: API message list
865 * Effects: If p_cbc_enabled is 'Y', then sets x_action_date to a valid CBC
866 * accounting date for this document. Otherwise, sets x_action_date to
867 * the current system date. Appends to API message list on error.
868 * Returns:
869 * x_return_status - FND_API.G_RET_STS_SUCCESS if date is set successfully
870 * FND_API.G_RET_STS_ERROR if error occurs getting date
871 * FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
872 * x_action_date - A truncated date that is either a valid CBC accounting date
873 * or the current system date.
874 */
875 PROCEDURE get_action_date
876 (p_api_version IN NUMBER,
877 p_init_msg_list IN VARCHAR2,
878 x_return_status OUT NOCOPY VARCHAR2,
879 p_doc_type IN PO_DOCUMENT_TYPES.document_type_code%TYPE,
880 p_doc_subtype IN PO_DOCUMENT_TYPES.document_subtype%TYPE,
881 p_doc_id IN NUMBER,
882 p_cbc_enabled IN VARCHAR2,
883 x_action_date OUT NOCOPY DATE)
884 IS
885
886 l_api_name CONSTANT VARCHAR2(30) := 'get_action_date';
887 l_api_version CONSTANT NUMBER := 1.0;
888 l_msg_count NUMBER;
889 l_msg_data VARCHAR2(2000);
890
891 BEGIN
892 -- Start standard API initialization
893 IF FND_API.to_boolean(p_init_msg_list) THEN
894 FND_MSG_PUB.initialize;
895 END IF;
896 IF NOT FND_API.compatible_api_call(l_api_version, p_api_version,
897 l_api_name, g_pkg_name)
898 THEN
899 RAISE FND_API.g_exc_unexpected_error;
900 END IF;
901 x_return_status := FND_API.g_ret_sts_success;
902 -- End standard API initialization
903
904 IF (g_debug_stmt) THEN
905 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
906 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix || l_api_name ||
907 '.invoked', 'Type: ' || NVL(p_doc_type,'null') ||
908 ', ID: ' || NVL(TO_CHAR(p_doc_id),'null'));
909 END IF;
910 END IF;
911
912 -- Initialize date to be null
913 x_action_date := NULL;
914
915 IF (p_cbc_enabled = 'Y') THEN
916
917 IF (g_debug_stmt) THEN
918 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
919 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix||l_api_name
920 ||'.get_cbc_date', 'IGC_CBC_PO_GRP.get_cbc_acct_date');
921 END IF;
922 END IF;
923
924 IGC_CBC_PO_GRP.get_cbc_acct_date
925 ( p_api_version => 1.0,
926 p_init_msg_list => FND_API.G_FALSE,
927 p_commit => FND_API.G_FALSE,
928 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
929 x_return_status => x_return_status,
930 x_msg_count => l_msg_count,
931 x_msg_data => l_msg_data,
932 p_document_id => p_doc_id,
933 p_document_type => p_doc_type,
934 p_document_sub_type => p_doc_subtype,
935 p_default => 'Y',
936 x_cbc_acct_date => x_action_date );
937 IF (x_return_status = FND_API.g_ret_sts_error) THEN
938 RAISE FND_API.g_exc_error;
939 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
940 RAISE FND_API.g_exc_unexpected_error;
941 END IF;
942
943 x_action_date := TRUNC(x_action_date);
944
945 END IF; --<if p_cbc_enabled ...>
946
947 IF (x_action_date IS NULL) THEN
948 x_action_date := TRUNC(SYSDATE);
949 END IF;
950
951 EXCEPTION
952 WHEN FND_API.g_exc_error THEN
953 x_return_status := FND_API.g_ret_sts_error;
954 x_action_date := TRUNC(SYSDATE);
955 WHEN FND_API.g_exc_unexpected_error THEN
956 x_return_status := FND_API.g_ret_sts_unexp_error;
957 x_action_date := TRUNC(SYSDATE);
958 WHEN OTHERS THEN
959 x_return_status := FND_API.g_ret_sts_unexp_error;
960 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
961 IF (g_debug_unexp) THEN
962 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
963 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
964 l_api_name || '.others_exception', 'Exception');
965 END IF;
966 END IF;
967 END get_action_date;
968
969
970 /**
971 * Public Procedure: val_action_date
972 * Requires: API message list has been initialized if p_init_msg_list is false.
973 * Modifies: API message list
974 * Effects: If encumbrance is on, checks that p_date lies in an open GL period
975 * for requisitions or for cancel or finally closing a purchase order. Also
976 * checks that p_date is a valid CBC accounting date for cancel or finally
977 * close actions if CBC is enabled. Appends to API message list on error.
978 * Returns:
979 * x_return_status - FND_API.G_RET_STS_SUCCESS if validation succeeds
980 * FND_API.G_RET_STS_ERROR if validation fails
981 * FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
982 */
983 PROCEDURE val_action_date
984 (p_api_version IN NUMBER,
985 p_init_msg_list IN VARCHAR2,
986 x_return_status OUT NOCOPY VARCHAR2,
987 p_doc_type IN PO_DOCUMENT_TYPES.document_type_code%TYPE,
988 p_doc_subtype IN PO_DOCUMENT_TYPES.document_subtype%TYPE,
989 p_doc_id IN NUMBER,
990 p_action IN VARCHAR2,
991 p_action_date IN DATE,
992 p_cbc_enabled IN VARCHAR2,
993 p_po_encumbrance_flag IN VARCHAR2,
994 p_req_encumbrance_flag IN VARCHAR2,
995 p_skip_valid_cbc_acct_date IN VARCHAR2)
996 IS
997
998 l_api_name CONSTANT VARCHAR2(30) := 'val_action_date';
999 l_api_version CONSTANT NUMBER := 1.0;
1000 l_msg_count NUMBER;
1001 l_msg_data VARCHAR2(2000);
1002
1003 BEGIN
1004 -- Start standard API initialization
1005 IF FND_API.to_boolean(p_init_msg_list) THEN
1006 FND_MSG_PUB.initialize;
1007 END IF;
1008 IF NOT FND_API.compatible_api_call(l_api_version, p_api_version,
1009 l_api_name, g_pkg_name)
1010 THEN
1011 RAISE FND_API.g_exc_unexpected_error;
1012 END IF;
1013 x_return_status := FND_API.g_ret_sts_success;
1014 -- End standard API initialization
1015
1016 IF (g_debug_stmt) THEN
1017 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1018 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix || l_api_name ||
1019 '.invoked', 'Type: ' || NVL(p_doc_type,'null') ||
1020 ', ID: ' || NVL(TO_CHAR(p_doc_id),'null') ||
1021 ', Date: ' || NVL(TO_CHAR(p_action_date,'DD-MON-RR'),'null'));
1022 END IF;
1023 END IF;
1024
1025 IF (p_doc_type = 'REQUISITION' AND p_req_encumbrance_flag = 'Y') OR
1026 ((p_doc_type IN ('PO','PA','RELEASE')) AND
1027 (p_action IN ('CANCEL','FINALLY CLOSE')) AND
1028 (p_po_encumbrance_flag = 'Y'))
1029 THEN
1030
1031 IF NOT in_open_gl_period( p_api_version => 1.0,
1032 p_init_msg_list => FND_API.G_FALSE,
1033 x_return_status => x_return_status,
1034 p_date => p_action_date )
1035 THEN
1036 IF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
1037 RAISE FND_API.g_exc_unexpected_error;
1038 END IF;
1039
1040 -- No error, so add a message saying open gl period check failed
1041 FND_MESSAGE.set_name('PO','PO_INV_CR_INVALID_GL_PERIOD');
1042 FND_MESSAGE.set_token('GL_DATE',
1043 TO_CHAR(p_action_date,'DD-MON-YYYY'));
1044 IF (g_debug_stmt) THEN
1045 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR) THEN
1046 FND_LOG.message(FND_LOG.level_error, g_module_prefix ||
1047 l_api_name || '.gl_period', FALSE);
1048 END IF;
1049 END IF;
1050 FND_MSG_PUB.add;
1051 RAISE FND_API.g_exc_error;
1052 END IF; -- if not in_open_gl_period ...
1053
1054 END IF; -- if p_doc_type ...
1055
1056 --As CBC not supported from html pages, we would skip the cbc date validation
1057 --If the procedure is invoked by HTML pages.
1058 --See Bug#4569120
1059
1060 /* Bug 6507195 : PO CBC Integration
1061 Only when the Parameter P_SKIP_VALID_CBC_ACCT_DATE is FND_API.G_TRUE, we should skip validation
1062 Hence Changed condition from FND_API.G_TRUE to FND_API.G_FALSE
1063 */
1064
1065 IF(nvl(p_skip_valid_cbc_acct_date, FND_API.G_FALSE) = FND_API.G_FALSE) THEN
1066 -- Validate with CBC accounting date if enabled for cancel or finally close
1067 IF (p_cbc_enabled = 'Y') AND (p_action IN ('CANCEL','FINALLY CLOSE')) THEN
1068
1069 IF (g_debug_stmt) THEN
1070 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1071 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix||l_api_name
1072 || '.val_cbc_date', 'IGC_CBC_PO_GRP.valid_cbc_acct_date');
1073 END IF;
1074 END IF;
1075
1076 IGC_CBC_PO_GRP.valid_cbc_acct_date
1077 ( p_api_version => 1.0,
1078 p_init_msg_list => FND_API.G_FALSE,
1079 p_commit => FND_API.G_FALSE,
1080 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1081 x_return_status => x_return_status,
1082 x_msg_count => l_msg_count,
1083 x_msg_data => l_msg_data,
1084 p_document_id => p_doc_id,
1085 p_document_type => p_doc_type,
1086 p_document_sub_type => p_doc_subtype,
1087 p_cbc_acct_date => p_action_date );
1088 IF (x_return_status = FND_API.g_ret_sts_error) THEN
1089 RAISE FND_API.g_exc_error;
1090 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
1091 RAISE FND_API.g_exc_unexpected_error;
1092 END IF;
1093
1094 END IF; -- if p_cbc_enabled ...
1095 END IF;--p_skip_valid_cbc_acct_date = FND_API.G_FALSE
1096
1097 EXCEPTION
1098 WHEN FND_API.g_exc_error THEN
1099 x_return_status := FND_API.g_ret_sts_error;
1100 WHEN FND_API.g_exc_unexpected_error THEN
1101 x_return_status := FND_API.g_ret_sts_unexp_error;
1102 WHEN OTHERS THEN
1103 x_return_status := FND_API.g_ret_sts_unexp_error;
1104 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1105 IF (g_debug_unexp) THEN
1106 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
1107 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
1108 l_api_name || '.others_exception', 'Exception');
1109 END IF;
1110 END IF;
1111 END val_action_date;
1112
1113 --< Bug 3194665 > Changed signature.
1114 --------------------------------------------------------------------------------
1115 --Start of Comments
1116 --Name: get_header_actions
1117 --Pre-reqs:
1118 -- None.
1119 --Modifies:
1120 -- FND_MSG_PUB
1121 --Locks:
1122 -- None.
1123 --Function:
1124 -- Retrieves all allowable control actions that p_agent_id can perform on the
1125 -- document at header level. Appends to API msg list upon unexpected error.
1126 --Parameters:
1127 --IN:
1128 --p_doc_subtype
1129 --p_doc_id
1130 -- The document header ID.
1131 --p_agent_id
1132 -- The person attempting to perform the control action. If this ID is NULL,
1133 -- then document security checks are skipped.
1134 --OUT:
1135 --x_lookup_code_tbl
1136 -- Table storing the lookup_code values for each allowable control action.
1137 -- These elements are in sync with the elements in x_displayed_field_tbl.
1138 --x_displayed_field_tbl
1139 -- Table storing the displayed_field values for each allowable control action.
1140 -- These elements are in sync with the elements in x_lookup_code_tbl.
1141 --x_return_status
1142 -- FND_API.g_ret_sts_success - if 1 or more actions were found
1143 -- FND_API.g_ret_sts_error - if no control actions were found
1144 -- FND_API.g_ret_sts_unexp_error - if unexpected error occurs
1145 --End of Comments
1146 --------------------------------------------------------------------------------
1147 PROCEDURE get_header_actions
1148 ( p_doc_subtype IN VARCHAR2
1149 , p_doc_id IN NUMBER
1150 , p_agent_id IN NUMBER
1151 , x_lookup_code_tbl OUT NOCOPY g_lookup_code_tbl_type
1152 , x_displayed_field_tbl OUT NOCOPY g_displayed_field_tbl_type
1153 , x_return_status OUT NOCOPY VARCHAR2
1154 , p_mode IN VARCHAR2 --<HTML Agreements R12>
1155 )
1156 IS
1157
1158 l_api_name CONSTANT VARCHAR2(30) := 'get_header_actions';
1159
1160 -- bug5353337
1161 -- Improve cursor performance by using _ALL tables
1162
1163
1164 --< Bug 3194665 Start >
1165 -- Removed unnecessary std api var.
1166 -- Now select displayed field in cursor.
1167 CURSOR l_get_actions_csr IS
1168 -- SQL What: Querying for control actions
1169 -- SQL Why: Find all allowable header level control actions for this doc
1170 SELECT polc.displayed_field,
1171 polc.lookup_code
1172 FROM po_lookup_codes polc,
1173 po_headers poh
1174 WHERE poh.po_header_id = p_doc_id
1175 AND polc.lookup_type = 'CONTROL ACTIONS'
1176 AND NVL(poh.closed_code, 'OPEN') <> 'FINALLY CLOSED'
1177 AND ( NVL(poh.cancel_flag, 'N') IN ('N','I')
1178 OR polc.lookup_code = 'FINALLY CLOSE'
1179 ) /** <Encumbrance FPJ> FC of cancelled PO **/
1180 /** Bug 3231524 Removed restrictions for drop ship PO. **/
1181 AND ( ( ( ( polc.lookup_code = 'FREEZE'
1182 AND NVL(poh.frozen_flag, 'N') = 'N'
1183 )
1184 OR ( polc.lookup_code = 'UNFREEZE'
1185 AND poh.frozen_flag = 'Y'
1186 )
1187 )
1188 AND NVL(poh.user_hold_flag, 'N') = 'N'
1189 AND NVL(poh.authorization_status, 'INCOMPLETE') = 'APPROVED'
1190 AND NVL(poh.consigned_consumption_flag, 'N') <> 'Y' /* CONSIGNED FPI */
1191 )
1192 OR ( polc.lookup_code = 'HOLD'
1193 AND NVL(poh.user_hold_flag, 'N') = 'N'
1194 AND NVL(poh.frozen_flag, 'N') = 'N'
1195 AND NVL(poh.consigned_consumption_flag, 'N') <> 'Y' /* CONSIGNED FPI */
1196 )
1197 OR ( polc.lookup_code = 'RELEASE HOLD'
1198 AND poh.user_hold_flag = 'Y'
1199 AND NVL(poh.consigned_consumption_flag, 'N') <> 'Y' /* CONSIGNED FPI */
1200 )
1201 OR ( polc.lookup_code = 'CANCEL PO'
1202 AND NVL(poh.consigned_consumption_flag, 'N') <> 'Y' /* CONSIGNED FPI */
1203 AND NVL(poh.user_hold_flag, 'N') = 'N'
1204 AND NVL(poh.closed_code, 'OPEN') NOT IN ('FINALLY CLOSED', 'CLOSED')
1205 AND poh.approved_date IS NOT NULL -- bug 12347143
1206 AND NVL(poh.authorization_status, 'INCOMPLETE') NOT IN
1207 /* <CancelPO FPJ> 'REQUIRES REAPPROVAL', */
1208 ('IN PROCESS', 'PRE-APPROVED', 'INCOMPLETE')
1209 -- AND NVL(poh.conterms_exist_flag, 'N') <> 'Y' /* <CancelPO FPJ> */ --Bug 7309989
1210 AND ( (p_agent_id IS NULL)
1211 OR (poh.agent_id = p_agent_id)
1212 OR EXISTS (SELECT 'security_level is full'
1213 FROM po_document_types podt
1214 WHERE podt.document_type_code IN ('PO', 'PA')
1215 AND podt.document_subtype = p_doc_subtype
1216 AND podt.access_level_code = 'FULL')
1217 )
1218 )
1219 OR ( poh.approved_flag = 'Y'
1220 AND ( ( polc.lookup_code = 'CLOSE'
1221 AND NVL(poh.closed_code, 'OPEN') <> 'CLOSED'
1222 )
1223 OR ( polc.lookup_code = 'FINALLY CLOSE'
1224 AND ( (p_agent_id IS NULL)
1225 OR (poh.agent_id = p_agent_id)
1226 OR EXISTS (SELECT 'security_level = full'
1227 FROM po_document_types podt
1228 WHERE podt.document_type_code IN ('PO', 'PA')
1229 AND podt.document_subtype = p_doc_subtype
1230 AND podt.access_level_code = 'FULL')
1231 )
1232 )
1233 OR ( polc.lookup_code = 'OPEN'
1234 /* CONSIGNED FPI START */
1235 AND NVL(poh.consigned_consumption_flag, 'N') <> 'Y'
1236 AND ( ( poh.type_lookup_code IN ('BLANKET', 'CONTRACT') /* <GC FPJ> */
1237 AND NVL(poh.closed_code, 'OPEN') <> 'OPEN'
1238 )
1239 OR ( poh.type_lookup_code NOT IN ('BLANKET', 'CONTRACT')
1240 AND EXISTS (SELECT 'Ship exists not OPEN'
1241 FROM po_line_locations poll
1242 WHERE poll.po_header_id = p_doc_id
1243 AND NVL(poll.consigned_flag, 'N') <> 'Y' /* CONSIGNED FPI */
1244 AND NVL(poll.closed_code, 'OPEN') <> 'OPEN')
1245 )
1246 )
1247 )
1248 /* CONSIGNED FPI END */
1249 OR ( polc.lookup_code = 'RECEIVE CLOSE'
1250 AND NVL(poh.consigned_consumption_flag, 'N') <> 'Y' /* CONSIGNED FPI */
1251 AND poh.type_lookup_code NOT IN ('BLANKET', 'CONTRACT')
1252 AND EXISTS (SELECT 'Ships exist OPEN'
1253 FROM po_line_locations poll
1254 WHERE poll.po_header_id = p_doc_id
1255 AND NVL(poll.closed_code, 'OPEN') NOT IN ('FINALLY CLOSED', 'CLOSED', 'CLOSED FOR RECEIVING')) --Bug l
1256 )
1257 OR ( polc.lookup_code = 'INVOICE CLOSE'
1258 AND NVL(poh.consigned_consumption_flag, 'N') <> 'Y' /* CONSIGNED FPI */
1259 AND poh.type_lookup_code NOT IN ('BLANKET', 'CONTRACT')
1260 AND EXISTS(SELECT 'Ships exist OPEN'
1261 FROM po_line_locations poll
1262 WHERE poll.po_header_id = p_doc_id
1263 AND NVL(poll.closed_code, 'OPEN') NOT IN ('FINALLY CLOSED', 'CLOSED', 'CLOSED FOR INVOICE')) --Bug 954
1264 )
1265 OR ( polc.lookup_code = 'RECEIVE OPEN'
1266 AND NVL(poh.consigned_consumption_flag, 'N') <> 'Y' /* CONSIGNED FPI */
1267 AND poh.type_lookup_code NOT IN ('BLANKET', 'CONTRACT')
1268 AND EXISTS (SELECT 'Ships exist RCLOSED'
1269 FROM po_line_locations poll
1270 WHERE poll.po_header_id = p_doc_id
1271 AND poll.closed_code IN ('CLOSED FOR RECEIVING', 'CLOSED'))
1272 )
1273 OR ( polc.lookup_code = 'INVOICE OPEN'
1274 AND poh.type_lookup_code NOT IN ('BLANKET', 'CONTRACT')
1275 AND EXISTS (SELECT 'Ships exits IC/CLOSED'
1276 FROM po_line_locations poll
1277 WHERE poll.po_header_id = p_doc_id
1278 AND NVL(poll.consigned_flag, 'N') <> 'Y' /* CONSIGNED FPI */
1279 AND poll.closed_code IN ('CLOSED FOR INVOICE', 'CLOSED'))
1280 )
1281 )
1282 )
1283 )
1284 ORDER BY polc.displayed_field;
1285 --< Bug 3194665 End >
1286
1287 BEGIN
1288 --< Bug 3194665 > Removed unnecessary std api work
1289 x_return_status := FND_API.g_ret_sts_success;
1290
1291 IF (g_debug_stmt) THEN
1292 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1293 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix || l_api_name ||
1294 '.invoked', 'Subtype: ' || NVL(p_doc_subtype,'null') ||
1295 ', ID: ' || NVL(TO_CHAR(p_doc_id),'null'));
1296 END IF;
1297 END IF;
1298
1299 OPEN l_get_actions_csr;
1300 --< Bug 3194665 Start >
1301 -- Select displayed_field and lookup_code
1302 FETCH l_get_actions_csr BULK COLLECT INTO x_displayed_field_tbl,
1303 x_lookup_code_tbl;
1304
1305 IF (l_get_actions_csr%ROWCOUNT = 0) THEN
1306 -- No data found, so just return error status without a msg
1307 IF (g_debug_stmt) THEN
1308 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR) THEN
1309 FND_LOG.message(FND_LOG.level_error, g_module_prefix||l_api_name||
1310 '.no_data_found', FALSE);
1311 END IF;
1312 END IF;
1313 x_return_status := FND_API.g_ret_sts_error;
1314 END IF;
1315 --< Bug 3194665 End >
1316 CLOSE l_get_actions_csr;
1317
1318 EXCEPTION
1319 --< Bug 3194665 > Removed unnecessary std api exception blocks
1320 WHEN OTHERS THEN
1321 x_return_status := FND_API.g_ret_sts_unexp_error;
1322 IF l_get_actions_csr%ISOPEN THEN
1323 CLOSE l_get_actions_csr;
1324 END IF;
1325 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1326 IF (g_debug_unexp) THEN
1327 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
1328 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
1329 l_api_name || '.others_exception', 'Exception');
1330 END IF;
1331 END IF;
1332 END get_header_actions;
1333
1334
1335 --< Bug 3194665 > Changed signature.
1336 --------------------------------------------------------------------------------
1337 --Start of Comments
1338 --Name: get_line_actions
1339 --Pre-reqs:
1340 -- None.
1341 --Modifies:
1342 -- FND_MSG_PUB
1343 --Locks:
1344 -- None.
1345 --Function:
1346 -- Retrieves all allowable control actions that p_agent_id can perform on the
1347 -- document at line level. Appends to API msg list upon unexpected error.
1348 --Parameters:
1349 --IN:
1350 --p_doc_subtype
1351 --p_doc_line_id
1352 -- The document line ID.
1353 --p_agent_id
1354 -- The person attempting to perform the control action. If this ID is NULL,
1355 -- then document security checks are skipped.
1356 --OUT:
1357 --x_lookup_code_tbl
1358 -- Table storing the lookup_code values for each allowable control action.
1359 -- These elements are in sync with the elements in x_displayed_field_tbl.
1360 --x_displayed_field_tbl
1361 -- Table storing the displayed_field values for each allowable control action.
1362 -- These elements are in sync with the elements in x_lookup_code_tbl.
1363 --x_return_status
1364 -- FND_API.g_ret_sts_success - if 1 or more actions were found
1365 -- FND_API.g_ret_sts_error - if no control actions were found
1366 -- FND_API.g_ret_sts_unexp_error - if unexpected error occurs
1367 --End of Comments
1368 --------------------------------------------------------------------------------
1369 PROCEDURE get_line_actions
1370 ( p_doc_subtype IN VARCHAR2
1371 , p_doc_line_id IN NUMBER
1372 , p_agent_id IN NUMBER
1373 , x_lookup_code_tbl OUT NOCOPY g_lookup_code_tbl_type
1374 , x_displayed_field_tbl OUT NOCOPY g_displayed_field_tbl_type
1375 , x_return_status OUT NOCOPY VARCHAR2
1376 , p_mode IN VARCHAR2 --<HTML Agreements R12>
1377 )
1378 IS
1379
1380 l_api_name CONSTANT VARCHAR2(30) := 'get_line_actions';
1381
1382 -- bug5353337
1383 -- Improve performance by using _ALL tables
1384
1385 --< Bug 3194665 Start >
1386 -- Removed unnecessary std api var.
1387 -- Now select displayed field in cursor.
1388 CURSOR l_get_actions_csr IS
1389 -- SQL What: Querying for control actions
1390 -- SQL Why: Find all allowable line level control actions for this doc
1391 SELECT polc.displayed_field,
1392 polc.lookup_code
1393 FROM po_lookup_codes polc,
1394 po_lines pol,
1395 po_headers poh
1396 WHERE pol.po_line_id = p_doc_line_id
1397 AND pol.po_header_id = poh.po_header_id
1398 AND polc.lookup_type = 'CONTROL ACTIONS'
1399 AND NVL(pol.closed_code, 'OPEN') <> 'FINALLY CLOSED'
1400 AND ( NVL(pol.cancel_flag, 'N') IN ('N','I')
1401 OR polc.lookup_code = 'FINALLY CLOSE'
1402 ) /** <Encumbrance FPJ> FC of cancelled PO **/
1403 /** Bug 3231524 Removed restrictions for drop ship PO. **/
1404 AND ( ( polc.lookup_code = 'CANCEL PO LINE'
1405 AND NVL(poh.consigned_consumption_flag, 'N') <> 'Y' /* CONSIGNED FPI */
1406 AND NVL(poh.user_hold_flag, 'N') = 'N'
1407 AND NVL(poh.closed_code, 'OPEN') NOT IN ('FINALLY CLOSED', 'CLOSED')
1408 AND poh.approved_date IS NOT NULL -- bug 12347143
1409 AND NVL(poh.authorization_status, 'INCOMPLETE') NOT IN
1410 /* <CancelPO FPJ> ('REQUIRES REAPPROVAL', */
1411 ('INCOMPLETE', 'IN PROCESS', 'PRE-APPROVED')
1412 -- AND NVL(poh.conterms_exist_flag, 'N') <> 'Y' /* <CancelPO FPJ> */ --Bug 7309989*/
1413 AND ( (p_agent_id IS NULL)
1414 OR (poh.agent_id = p_agent_id)
1415 OR EXISTS (SELECT 'security_level is full'
1416 FROM po_document_types podt
1417 WHERE podt.document_type_code IN ('PO', 'PA')
1418 AND podt.document_subtype = p_doc_subtype
1419 AND podt.access_level_code = 'FULL')
1420 )
1421 )
1422 OR ( poh.approved_flag = 'Y'
1423 AND ( ( polc.lookup_code = 'CLOSE'
1424 AND NVL(pol.closed_code, 'OPEN') <> 'CLOSED'
1425 )
1426 OR ( polc.lookup_code = 'FINALLY CLOSE'
1427 AND ( (p_agent_id IS NULL)
1428 OR (poh.agent_id = p_agent_id)
1429 OR EXISTS (SELECT 'security_level is= full'
1430 FROM po_document_types podt
1431 WHERE podt.document_type_code IN ('PO', 'PA')
1432 AND podt.document_subtype = p_doc_subtype
1433 AND podt.access_level_code = 'FULL')
1434 )
1435 )
1436 OR ( polc.lookup_code = 'OPEN'
1437 AND NVL(poh.consigned_consumption_flag, 'N') <> 'Y' /* CONSIGNED FPI */
1438 AND ( (poh.type_lookup_code = 'BLANKET')
1439 OR ( poh.type_lookup_code NOT IN ('BLANKET', 'CONTRACT')
1440 AND EXISTS (SELECT 'Ships exist not OPEN'
1441 FROM po_line_locations poll
1442 WHERE poll.po_line_id = p_doc_line_id
1443 AND NVL(poll.consigned_flag, 'N') <> 'Y' /* CONSIGNED FPI */
1444 AND NVL(poll.closed_code, 'OPEN') <> 'OPEN')
1445 )
1446 )
1447 )
1448 OR ( polc.lookup_code = 'RECEIVE CLOSE'
1449 AND NVL(poh.consigned_consumption_flag, 'N') <> 'Y' /* CONSIGNED FPI */
1450 AND poh.type_lookup_code NOT IN ('BLANKET', 'CONTRACT')
1451 AND EXISTS (SELECT 'Ships exist that are OPEN'
1452 FROM po_line_locations poll
1453 WHERE poll.po_line_id = p_doc_line_id
1454 AND NVL(poll.closed_code, 'OPEN') NOT IN ('FINALLY CLOSED', 'CLOSED', 'CLOSED FOR RECEIVING')) --Bug 5113609
1455 )
1456 OR ( polc.lookup_code = 'INVOICE CLOSE'
1457 AND NVL(poh.consigned_consumption_flag, 'N') <> 'Y' /* CONSIGNED FPI */
1458 AND poh.type_lookup_code NOT IN ('BLANKET', 'CONTRACT')
1459 AND EXISTS (SELECT 'Ships exist OPEN'
1460 FROM po_line_locations poll
1461 WHERE poll.po_line_id = p_doc_line_id
1462 AND NVL(poll.closed_code, 'OPEN') NOT IN ('FINALLY CLOSED', 'CLOSED', 'CLOSED FOR INVOICE')) --Bug 5113609
1463 )
1464 OR ( polc.lookup_code = 'RECEIVE OPEN'
1465 AND NVL(poh.consigned_consumption_flag, 'N') <> 'Y' /* CONSIGNED FPI */
1466 AND poh.type_lookup_code NOT IN ('BLANKET', 'CONTRACT')
1467 AND EXISTS (SELECT 'Ships exist RCLOSED'
1468 FROM po_line_locations poll
1469 WHERE poll.po_line_id = p_doc_line_id
1470 AND poll.closed_code IN ('CLOSED FOR RECEIVING', 'CLOSED'))
1471 )
1472 OR ( polc.lookup_code = 'INVOICE OPEN'
1473 AND poh.type_lookup_code NOT IN ('BLANKET', 'CONTRACT')
1474 AND EXISTS (SELECT 'Ships exits IC/CLOSED'
1475 FROM po_line_locations poll
1476 WHERE poll.po_line_id = p_doc_line_id
1477 AND NVL(poll.consigned_flag, 'N') <> 'Y' /* CONSIGNED FPI */
1478 AND poll.closed_code IN ('CLOSED FOR INVOICE', 'CLOSED'))
1479 )
1480 )
1481 )
1482 )
1483 ORDER BY polc.displayed_field;--< Bug 3194665 End >
1484
1485 BEGIN
1486 --< Bug 3194665 > Removed unnecessary std api work
1487 x_return_status := FND_API.g_ret_sts_success;
1488
1489 IF (g_debug_stmt) THEN
1490 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1491 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix || l_api_name ||
1492 '.invoked', 'Subtype: ' || NVL(p_doc_subtype,'null') ||
1493 ', Line ID: ' || NVL(TO_CHAR(p_doc_line_id),'null'));
1494 END IF;
1495 END IF;
1496
1497 OPEN l_get_actions_csr;
1498 --< Bug 3194665 Start >
1499 -- Select displayed_field and lookup_code
1500 FETCH l_get_actions_csr BULK COLLECT INTO x_displayed_field_tbl,
1501 x_lookup_code_tbl;
1502
1503 IF (l_get_actions_csr%ROWCOUNT = 0) THEN
1504 -- No data found, so just return error status without a msg
1505 IF (g_debug_stmt) THEN
1506 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR) THEN
1507 FND_LOG.message(FND_LOG.level_error, g_module_prefix||l_api_name||
1508 '.get_failed', FALSE);
1509 END IF;
1510 END IF;
1511 x_return_status := FND_API.g_ret_sts_error;
1512 END IF;
1513 --< Bug 3194665 End >
1514
1515 CLOSE l_get_actions_csr;
1516
1517 EXCEPTION
1518 --< Bug 3194665 > Removed unnecessary std api exception blocks
1519 WHEN OTHERS THEN
1520 x_return_status := FND_API.g_ret_sts_unexp_error;
1521 IF l_get_actions_csr%ISOPEN THEN
1522 CLOSE l_get_actions_csr;
1523 END IF;
1524 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1525 IF (g_debug_unexp) THEN
1526 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
1527 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
1528 l_api_name || '.others_exception', 'Exception');
1529 END IF;
1530 END IF;
1531 END get_line_actions;
1532
1533
1534 --< Bug 3194665 > Changed signature.
1535 --------------------------------------------------------------------------------
1536 --Start of Comments
1537 --Name: get_shipment_actions
1538 --Pre-reqs:
1539 -- None.
1540 --Modifies:
1541 -- FND_MSG_PUB
1542 --Locks:
1543 -- None.
1544 --Function:
1545 -- Retrieves all allowable control actions that p_agent_id can perform on the
1546 -- document at shipment level. Appends to API msg list upon unexpected error.
1547 --Parameters:
1548 --IN:
1549 --p_doc_type
1550 --p_doc_subtype
1551 --p_doc_line_loc_id
1552 -- The document shipment ID.
1553 --p_agent_id
1554 -- The person attempting to perform the control action. If this ID is NULL,
1555 -- then document security checks are skipped.
1556 --OUT:
1557 --x_lookup_code_tbl
1558 -- Table storing the lookup_code values for each allowable control action.
1559 -- These elements are in sync with the elements in x_displayed_field_tbl.
1560 --x_displayed_field_tbl
1561 -- Table storing the displayed_field values for each allowable control action.
1562 -- These elements are in sync with the elements in x_lookup_code_tbl.
1563 --x_return_status
1564 -- FND_API.g_ret_sts_success - if 1 or more actions were found
1565 -- FND_API.g_ret_sts_error - if no control actions were found
1566 -- FND_API.g_ret_sts_unexp_error - if unexpected error occurs
1567 --End of Comments
1568 --------------------------------------------------------------------------------
1569 PROCEDURE get_shipment_actions
1570 ( p_doc_type IN VARCHAR2
1571 , p_doc_subtype IN VARCHAR2
1572 , p_doc_line_loc_id IN NUMBER
1573 , p_agent_id IN NUMBER
1574 , x_lookup_code_tbl OUT NOCOPY g_lookup_code_tbl_type
1575 , x_displayed_field_tbl OUT NOCOPY g_displayed_field_tbl_type
1576 , x_return_status OUT NOCOPY VARCHAR2
1577 , p_mode IN VARCHAR2 --<HTML Agreements R12>
1578 )
1579 IS
1580
1581 l_api_name CONSTANT VARCHAR2(30) := 'get_shipment_actions';
1582
1583 -- bug5353337
1584 -- Improve performance by using _ALL tables
1585
1586 --< Bug 3194665 Start >
1587 -- Removed unnecessary std api var.
1588 -- Now select displayed field in cursor.
1589 CURSOR l_get_actions_csr IS
1590 -- SQL What: Querying for control actions
1591 -- SQL Why: Find all allowable shipment level control actions for this doc
1592 SELECT polc.displayed_field,
1593 polc.lookup_code
1594 FROM po_lookup_codes polc,
1595 po_line_locations_all poll,
1596 po_headers_all poh
1597 WHERE poll.line_location_id = p_doc_line_loc_id
1598 AND poll.po_header_id = poh.po_header_id
1599 AND polc.lookup_type = 'CONTROL ACTIONS'
1600 AND NVL(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
1601 AND ( NVL(poll.cancel_flag, 'N') IN ('N','I')
1602 OR polc.lookup_code = 'FINALLY CLOSE'
1603 ) /** <Encumbrance FPJ> FC of cancelled PO **/
1604 AND poll.shipment_type <> 'PRICE BREAK' /*<bug 3323045>*/
1605 /** Bug 3231524 Removed restrictions for drop ship PO. **/
1606 AND ( ( polc.lookup_code = 'CANCEL PO SHIPMENT'
1607 AND NVL(poh.consigned_consumption_flag, 'N') <> 'Y' /* CONSIGNED FPI */
1608 AND NVL(poh.user_hold_flag, 'N') = 'N'
1609 AND poh.approved_date IS NOT NULL -- bug 12347143
1610 AND NVL(poh.authorization_status, 'INCOMPLETE') NOT IN
1611 /* <CancelPO FPJ> 'REQUIRES REAPPROVAL', */
1612 ('INCOMPLETE', 'IN PROCESS', 'PRE-APPROVED')
1613 --<HTML Agreements R12 Start>
1614 /*
1615 * Bug 12334616 : Allowing the Cancel action on Po shipments even if there are contract
1616 * terms associated with the PO [Partial Fix of :7309989]
1617 * Condition(p_mode = 'UPDATE' OR NVL(poh.conterms_exist_flag,'N')<> 'Y') means the cancel action on Po shipments
1618 * is allowed from the Details/Entry pages[p_mode=Update]
1619 * and from summary page[p_mode=summary] only if there are no contract terms associated with the PO.
1620 * So on allowing Cancel action on Po shipments even if there are contract terms associated with the PO
1621 * will enable cancel action on Summary/Update page both,hence commenting the entire condition.
1622 *
1623 */
1624 /* AND ( p_mode = 'UPDATE'
1625 OR NVL(poh.conterms_exist_flag,'N')<> 'Y')*/
1626 --AND NVL(poh.conterms_exist_flag, 'N') <> 'Y' /* <CancelPO FPJ> */
1627 --<HTML Agreements R12 End>
1628 AND NVL(poll.closed_code, 'OPEN') NOT IN ('FINALLY CLOSED', 'CLOSED')
1629 AND ( (p_agent_id IS NULL)
1630 OR (poh.agent_id = p_agent_id)
1631 OR EXISTS (SELECT 'security_level is full'
1632 FROM po_document_types_all_b podt
1633 WHERE podt.document_type_code = p_doc_type
1634 AND podt.document_subtype = p_doc_subtype
1635 AND podt.access_level_code = 'FULL'
1636 AND podt.org_id = poh.org_id)
1637 )
1638 --<Complex Work R12>: Can not cancel a Milestone Pay Item
1639 --if it has been executed against (cancel line/header instead)
1640 AND( NVL(poll.payment_type, 'NULL') <> 'MILESTONE'
1641 OR
1642 (coalesce(poll.quantity_billed, poll.amount_billed,
1643 poll.quantity_financed, poll.amount_financed,
1644 poll.quantity_shipped, poll.amount_shipped,
1645 poll.quantity_received, poll.amount_received, 0) = 0
1646 )
1647 )
1648 )
1649 OR ( poh.approved_flag = 'Y'
1650 AND NVL(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
1651 AND ( ( polc.lookup_code = 'CLOSE'
1652 AND NVL(poll.closed_code, 'OPEN') <> 'CLOSED'
1653 )
1654 OR ( polc.lookup_code = 'FINALLY CLOSE'
1655 AND ( (p_agent_id IS NULL)
1656 OR (poh.agent_id = p_agent_id)
1657 OR EXISTS (SELECT 'security_level is full'
1658 FROM po_document_types_all_b podt
1659 WHERE podt.document_type_code = p_doc_type
1660 AND podt.document_subtype = p_doc_subtype
1661 AND podt.access_level_code = 'FULL'
1662 AND podt.org_id = poh.org_id)
1663 )
1664 -- <Complex Work R12 Start>: Can't FC with open recoup/retain balance.
1665 AND (
1666 NVL(poll.retainage_released_amount, 0) >=
1667 NVL(poll.retainage_withheld_amount, 0)
1668 )
1669 AND (
1670 (poll.shipment_type <> 'PREPAYMENT')
1671 OR (coalesce(poll.quantity_recouped,
1672 poll.amount_recouped, 0) >=
1673 coalesce(poll.quantity_financed,
1674 poll.amount_financed, 0))
1675 )
1676 -- <Complex Work R12 End>
1677 )
1678 OR ( polc.lookup_code = 'OPEN'
1679 AND NVL(poh.consigned_consumption_flag, 'N') <> 'Y' /* CONSIGNED FPI */
1680 AND NVL(poll.consigned_flag, 'N') <> 'Y' /* CONSIGNED FPI */
1681 AND NVL(poll.closed_code, 'OPEN') <> 'OPEN'
1682 )
1683 OR ( polc.lookup_code = 'INVOICE CLOSE'
1684 --<Bug#4534587: Removed the check for consigned_consumption_flag/>
1685 AND NVL(poll.closed_code, 'OPEN') NOT IN ('CLOSED', 'CLOSED FOR INVOICE')
1686 AND NVL(poll.consigned_flag, 'N') <> 'Y' --<Bug#4534587: Added Check for Consigned Shipment/>
1687 )
1688 OR ( polc.lookup_code = 'RECEIVE CLOSE'
1689 AND NVL(poh.consigned_consumption_flag, 'N') <> 'Y' /* CONSIGNED FPI */
1690 AND NVL(poll.closed_code, 'OPEN') NOT IN ('CLOSED', 'CLOSED FOR RECEIVING')
1691 )
1692 OR ( polc.lookup_code = 'INVOICE OPEN'
1693 AND NVL(poll.consigned_flag, 'N') <> 'Y' /* CONSIGNED FPI */
1694 AND NVL(poll.closed_code, 'OPEN') NOT IN ('OPEN', 'OPEN FOR INVOICE', 'CLOSED FOR RECEIVING')
1695 )
1696 OR ( polc.lookup_code = 'RECEIVE OPEN'
1697 AND NVL(poh.consigned_consumption_flag, 'N') <> 'Y' /* CONSIGNED FPI */
1698 AND NVL(poll.closed_code, 'OPEN') NOT IN ('OPEN', 'OPEN FOR RECEIVING', 'CLOSED FOR INVOICE')
1699 )
1700 )
1701 )
1702 )
1703 ORDER BY polc.displayed_field;
1704 --< Bug 3194665 End >
1705
1706 BEGIN
1707 --< Bug 3194665 > Removed unnecessary std api work
1708 x_return_status := FND_API.g_ret_sts_success;
1709
1710 IF (g_debug_stmt) THEN
1711 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1712 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix || l_api_name ||
1713 '.invoked', 'Type: ' || NVL(p_doc_type,'null') ||
1714 ', Subtype: ' || NVL(p_doc_subtype,'null') ||
1715 ', Line Loc ID: ' || NVL(TO_CHAR(p_doc_line_loc_id),'null'));
1716 END IF;
1717 END IF;
1718
1719 OPEN l_get_actions_csr;
1720 --< Bug 3194665 Start >
1721 -- Select displayed_field and lookup_code
1722 FETCH l_get_actions_csr BULK COLLECT INTO x_displayed_field_tbl,
1723 x_lookup_code_tbl;
1724
1725 IF (l_get_actions_csr%ROWCOUNT = 0) THEN
1726 -- No data found, so just return error status without a msg
1727 IF (g_debug_stmt) THEN
1728 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR) THEN
1729 FND_LOG.message(FND_LOG.level_error, g_module_prefix||l_api_name||
1730 '.get_failed', FALSE);
1731 END IF;
1732 END IF;
1733 x_return_status := FND_API.g_ret_sts_error;
1734 END IF;
1735 --< Bug 3194665 End >
1736 CLOSE l_get_actions_csr;
1737
1738 EXCEPTION
1739 --< Bug 3194665 > Removed unnecessary std api exception blocks
1740 WHEN OTHERS THEN
1741 x_return_status := FND_API.g_ret_sts_unexp_error;
1742 IF l_get_actions_csr%ISOPEN THEN
1743 CLOSE l_get_actions_csr;
1744 END IF;
1745 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1746 IF (g_debug_unexp) THEN
1747 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
1748 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
1749 l_api_name || '.others_exception', 'Exception');
1750 END IF;
1751 END IF;
1752 END get_shipment_actions;
1753
1754
1755 --< Bug 3194665 > Changed signature.
1756 --------------------------------------------------------------------------------
1757 --Start of Comments
1758 --Name: get_rel_header_actions
1759 --Pre-reqs:
1760 -- None.
1761 --Modifies:
1762 -- FND_MSG_PUB
1763 --Locks:
1764 -- None.
1765 --Function:
1766 -- Retrieves all allowable control actions that p_agent_id can perform on the
1767 -- document at release header level. Appends to API msg list upon unexpected
1768 -- error.
1769 --Parameters:
1770 --IN:
1771 --p_doc_subtype
1772 --p_doc_id
1773 -- The document release ID.
1774 --p_agent_id
1775 -- The person attempting to perform the control action. If this ID is NULL,
1776 -- then document security checks are skipped.
1777 --OUT:
1778 --x_lookup_code_tbl
1779 -- Table storing the lookup_code values for each allowable control action.
1780 -- These elements are in sync with the elements in x_displayed_field_tbl.
1781 --x_displayed_field_tbl
1782 -- Table storing the displayed_field values for each allowable control action.
1783 -- These elements are in sync with the elements in x_lookup_code_tbl.
1784 --x_return_status
1785 -- FND_API.g_ret_sts_success - if 1 or more actions were found
1786 -- FND_API.g_ret_sts_error - if no control actions were found
1787 -- FND_API.g_ret_sts_unexp_error - if unexpected error occurs
1788 --End of Comments
1789 --------------------------------------------------------------------------------
1790 PROCEDURE get_rel_header_actions
1791 ( p_doc_subtype IN VARCHAR2
1792 , p_doc_id IN NUMBER
1793 , p_agent_id IN NUMBER
1794 , x_lookup_code_tbl OUT NOCOPY g_lookup_code_tbl_type
1795 , x_displayed_field_tbl OUT NOCOPY g_displayed_field_tbl_type
1796 , x_return_status OUT NOCOPY VARCHAR2
1797 )
1798 IS
1799
1800 l_api_name CONSTANT VARCHAR2(30) := 'get_rel_header_actions';
1801 --< Bug 3194665 Start >
1802 -- Removed unnecessary std api var.
1803 -- Now select displayed field in cursor.
1804 CURSOR l_get_actions_csr IS
1805 -- SQL What: Querying for control actions
1806 -- SQL Why: Find all allowable release header level control actions for this
1807 -- release.
1808 SELECT polc.displayed_field,
1809 polc.lookup_code
1810 FROM po_lookup_codes polc,
1811 po_releases por
1812 WHERE por.po_release_id = p_doc_id
1813 AND polc.lookup_type = 'CONTROL ACTIONS'
1814 AND NVL(por.closed_code, 'OPEN') <> 'FINALLY CLOSED'
1815 AND ( NVL(por.cancel_flag, 'N') IN ('N','I')
1816 OR polc.lookup_code = 'FINALLY CLOSE'
1817 ) /** <Encumbrance FPJ> FC of cancelled Rel **/
1818 /** Bug 3231524 Removed restrictions for drop ship release. **/
1819 AND ( ( ( ( polc.lookup_code = 'FREEZE'
1820 AND NVL(por.consigned_consumption_flag, 'N') <> 'Y' /* CONSIGNED FPI */
1821 AND NVL(por.frozen_flag, 'N') = 'N'
1822 )
1823 OR ( polc.lookup_code = 'UNFREEZE'
1824 AND NVL(por.consigned_consumption_flag, 'N') <> 'Y' /* CONSIGNED FPI */
1825 AND por.frozen_flag = 'Y'
1826 )
1827 )
1828 AND NVL(por.hold_flag, 'N') = 'N'
1829 AND NVL(por.authorization_status, 'INCOMPLETE') = 'APPROVED'
1830 )
1831 OR ( polc.lookup_code = 'HOLD'
1832 AND NVL(por.consigned_consumption_flag, 'N') <> 'Y' /* CONSIGNED FPI */
1833 AND NVL(por.hold_flag, 'N') = 'N'
1834 )
1835 OR ( polc.lookup_code = 'RELEASE HOLD'
1836 AND NVL(por.consigned_consumption_flag, 'N') <> 'Y' /* CONSIGNED FPI */
1837 AND por.hold_flag = 'Y'
1838 )
1839 OR ( polc.lookup_code = 'CANCEL REL'
1840 AND NVL(por.consigned_consumption_flag, 'N') <> 'Y' /* CONSIGNED FPI */
1841 AND NVL(por.hold_flag, 'N') = 'N'
1842 AND NVL(por.closed_code, 'OPEN') NOT IN ('FINALLY CLOSED', 'CLOSED')
1843 AND por.approved_date IS NOT NULL -- bug 12347143
1844 AND NVL(por.authorization_status, 'INCOMPLETE') NOT IN
1845 /* <CancelPO FPJ> 'REQUIRES REAPPROVAL', */
1846 ('IN PROCESS', 'PRE-APPROVED', 'INCOMPLETE')
1847 AND ( (p_agent_id IS NULL)
1848 OR (por.agent_id = p_agent_id)
1849 OR EXISTS (SELECT 'security_level is full'
1850 FROM po_document_types podt
1851 WHERE podt.document_type_code = 'RELEASE'
1852 AND podt.document_subtype = p_doc_subtype
1853 AND podt.access_level_code = 'FULL')
1854 )
1855 )
1856 OR ( por.approved_flag = 'Y'
1857 AND ( ( polc.lookup_code = 'CLOSE'
1858 AND NVL(por.closed_code, 'OPEN') <> 'CLOSED'
1859 )
1860 OR ( polc.lookup_code = 'FINALLY CLOSE'
1861 AND ( (p_agent_id IS NULL)
1862 OR (por.agent_id = p_agent_id)
1863 OR EXISTS (SELECT 'security_level is full'
1864 FROM po_document_types podt
1865 WHERE podt.document_type_code = 'RELEASE'
1866 AND podt.document_subtype = p_doc_subtype
1867 AND podt.access_level_code = 'FULL')
1868 )
1869 )
1870 OR ( polc.lookup_code = 'OPEN'
1871 AND NVL(por.consigned_consumption_flag, 'N') <> 'Y' /* CONSIGNED FPI */
1872 AND NVL(por.closed_code, 'OPEN') <> 'OPEN'
1873 )
1874 OR ( polc.lookup_code = 'RECEIVE CLOSE'
1875 AND NVL(por.consigned_consumption_flag, 'N') <> 'Y' /* CONSIGNED FPI */
1876 AND EXISTS (SELECT 'Ships exist that are OPEN'
1877 FROM po_line_locations poll
1878 WHERE poll.po_release_id = p_doc_id
1879 AND NVL(poll.closed_code, 'OPEN') NOT IN ('FINALLY CLOSED', 'CLOSED', 'CLOSED FOR RECEIVING')) --Bug 5113609
1880 )
1881 OR ( polc.lookup_code = 'INVOICE CLOSE'
1882 AND NVL(por.consigned_consumption_flag, 'N') <> 'Y' /* CONSIGNED FPI */
1883 AND EXISTS (SELECT 'Ships exist that are OPEN'
1884 FROM po_line_locations poll
1885 WHERE poll.po_release_id = p_doc_id
1886 AND NVL(poll.closed_code, 'OPEN') NOT IN ('FINALLY CLOSED', 'CLOSED', 'CLOSED FOR INVOICE')) --Bug 5113609
1887 )
1888 OR ( polc.lookup_code = 'RECEIVE OPEN'
1889 AND NVL(por.consigned_consumption_flag, 'N') <> 'Y' /* CONSIGNED FPI */
1890 AND EXISTS (SELECT 'Ships exist that are RCLOSED'
1891 FROM po_line_locations poll
1892 WHERE poll.po_release_id = p_doc_id
1893 AND poll.closed_code IN ('CLOSED FOR RECEIVING', 'CLOSED'))
1894 )
1895 OR ( polc.lookup_code = 'INVOICE OPEN'
1896 AND EXISTS (SELECT 'Ships exits that are IC/CLOSED'
1897 FROM po_line_locations poll
1898 WHERE poll.po_release_id = p_doc_id
1899 AND poll.closed_code IN ('CLOSED FOR INVOICE', 'CLOSED'))
1900 )
1901 )
1902 )
1903 )
1904 ORDER BY polc.displayed_field;
1905 --< Bug 3194665 End >
1906
1907 BEGIN
1908 --< Bug 3194665 > Removed unnecessary std api work
1909 x_return_status := FND_API.g_ret_sts_success;
1910
1911 IF (g_debug_stmt) THEN
1912 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
1913 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix || l_api_name ||
1914 '.invoked', 'Subtype: ' || NVL(p_doc_subtype,'null') ||
1915 ', ID: ' || NVL(TO_CHAR(p_doc_id),'null'));
1916 END IF;
1917 END IF;
1918
1919 OPEN l_get_actions_csr;
1920 --< Bug 3194665 Start >
1921 -- Select displayed_field and lookup_code
1922 FETCH l_get_actions_csr BULK COLLECT INTO x_displayed_field_tbl,
1923 x_lookup_code_tbl;
1924
1925 IF (l_get_actions_csr%ROWCOUNT = 0) THEN
1926 -- No data found, so just return error status without a msg
1927 IF (g_debug_stmt) THEN
1928 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR) THEN
1929 FND_LOG.message(FND_LOG.level_error, g_module_prefix||l_api_name||
1930 '.get_failed', FALSE);
1931 END IF;
1932 END IF;
1933 x_return_status := FND_API.g_ret_sts_error;
1934 END IF;
1935 --< Bug 3194665 End >
1936 CLOSE l_get_actions_csr;
1937
1938 EXCEPTION
1939 --< Bug 3194665 > Removed unnecessary std api exception blocks
1940 WHEN OTHERS THEN
1941 x_return_status := FND_API.g_ret_sts_unexp_error;
1942 IF l_get_actions_csr%ISOPEN THEN
1943 CLOSE l_get_actions_csr;
1944 END IF;
1945 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1946 IF (g_debug_unexp) THEN
1947 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
1948 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
1949 l_api_name || '.others_exception', 'Exception');
1950 END IF;
1951 END IF;
1952 END get_rel_header_actions;
1953
1954
1955 --< Bug 3194665 > Changed signature.
1956 --------------------------------------------------------------------------------
1957 --Start of Comments
1958 --Name: get_rel_shipment_actions
1959 --Pre-reqs:
1960 -- None.
1961 --Modifies:
1962 -- FND_MSG_PUB
1963 --Locks:
1964 -- None.
1965 --Function:
1966 -- Retrieves all allowable control actions that p_agent_id can perform on the
1967 -- document at release shipment level. Appends to API msg list upon unexpected
1968 -- error.
1969 --Parameters:
1970 --IN:
1971 --p_doc_subtype
1972 --p_doc_line_loc_id
1973 -- The document release shipment ID.
1974 --p_agent_id
1975 -- The person attempting to perform the control action. If this ID is NULL,
1976 -- then document security checks are skipped.
1977 --OUT:
1978 --x_lookup_code_tbl
1979 -- Table storing the lookup_code values for each allowable control action.
1980 -- These elements are in sync with the elements in x_displayed_field_tbl.
1981 --x_displayed_field_tbl
1982 -- Table storing the displayed_field values for each allowable control action.
1983 -- These elements are in sync with the elements in x_lookup_code_tbl.
1984 --x_return_status
1985 -- FND_API.g_ret_sts_success - if 1 or more actions were found
1986 -- FND_API.g_ret_sts_error - if no control actions were found
1987 -- FND_API.g_ret_sts_unexp_error - if unexpected error occurs
1988 --End of Comments
1989 --------------------------------------------------------------------------------
1990 PROCEDURE get_rel_shipment_actions
1991 ( p_doc_subtype IN VARCHAR2
1992 , p_doc_line_loc_id IN NUMBER
1993 , p_agent_id IN NUMBER
1994 , x_lookup_code_tbl OUT NOCOPY g_lookup_code_tbl_type
1995 , x_displayed_field_tbl OUT NOCOPY g_displayed_field_tbl_type
1996 , x_return_status OUT NOCOPY VARCHAR2
1997 )
1998 IS
1999
2000 l_api_name CONSTANT VARCHAR2(30) := 'get_rel_shipment_actions';
2001 --< Bug 3194665 Start >
2002 -- Removed unnecessary std api var.
2003 -- Now select displayed field in cursor.
2004 CURSOR l_get_actions_csr IS
2005 -- SQL What: Querying for control actions
2006 -- SQL Why: Find all allowable shipment level control actions for this
2007 -- release.
2008 SELECT polc.displayed_field,
2009 polc.lookup_code
2010 FROM po_lookup_codes polc,
2011 po_line_locations poll,
2012 po_releases por
2013 WHERE poll.line_location_id = p_doc_line_loc_id
2014 AND poll.po_release_id = por.po_release_id
2015 AND polc.lookup_type = 'CONTROL ACTIONS'
2016 AND NVL(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
2017 AND ( NVL(poll.cancel_flag, 'N') IN ('N','I')
2018 OR polc.lookup_code = 'FINALLY CLOSE'
2019 ) /** <Encumbrance FPJ> FC of cancelled Rel **/
2020 /** Bug 3231524 Removed restrictions for drop ship release. **/
2021 AND ( ( polc.lookup_code = 'CANCEL REL SHIPMENT'
2022 AND NVL(por.consigned_consumption_flag, 'N') <> 'Y' /* CONSIGNED FPI */
2023 AND NVL(por.hold_flag, 'N') = 'N'
2024 AND por.approved_date IS NOT NULL -- bug 12347143
2025 /* Bug no 5388496 cancelling is allowed for releases with status 'REQUIRES REAPPROVAL'*/
2026 AND NVL(por.authorization_status, 'INCOMPLETE') NOT IN ('INCOMPLETE', 'IN PROCESS', 'PRE-APPROVED')
2027 AND NVL(poll.closed_code, 'OPEN') NOT IN ('FINALLY CLOSED', 'CLOSED')
2028 AND ( (p_agent_id IS NULL)
2029 OR (por.agent_id = p_agent_id)
2030 OR EXISTS (SELECT 'security_level is full'
2031 FROM po_document_types podt
2032 WHERE podt.document_type_code = 'RELEASE'
2033 AND podt.document_subtype = p_doc_subtype
2034 AND podt.access_level_code = 'FULL')
2035 )
2036 )
2037 OR ( por.approved_flag = 'Y'
2038 AND NVL(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
2039 AND ( ( polc.lookup_code = 'CLOSE'
2040 AND NVL(poll.closed_code, 'OPEN') <> 'CLOSED'
2041 )
2042 OR ( polc.lookup_code = 'FINALLY CLOSE'
2043 AND ( (p_agent_id IS NULL)
2044 OR (por.agent_id = p_agent_id)
2045 OR EXISTS(SELECT 'security_level is full'
2046 FROM po_document_types podt
2047 WHERE podt.document_type_code = 'RELEASE'
2048 AND podt.document_subtype = p_doc_subtype
2049 AND podt.access_level_code = 'FULL')
2050 )
2051 )
2052 OR ( polc.lookup_code = 'OPEN'
2053 AND NVL(por.consigned_consumption_flag, 'N') <> 'Y' /* CONSIGNED FPI */
2054 AND NVL(poll.closed_code, 'OPEN') <> 'OPEN'
2055 )
2056 OR ( polc.lookup_code = 'INVOICE CLOSE'
2057 AND NVL(por.consigned_consumption_flag, 'N') <> 'Y' /* CONSIGNED FPI */
2058 AND NVL(poll.closed_code, 'OPEN') NOT IN ('CLOSED', 'CLOSED FOR INVOICE')
2059 )
2060 OR ( polc.lookup_code = 'RECEIVE CLOSE'
2061 AND NVL(por.consigned_consumption_flag, 'N') <> 'Y' /* CONSIGNED FPI */
2062 AND NVL(poll.closed_code, 'OPEN') NOT IN ('CLOSED', 'CLOSED FOR RECEIVING')
2063 )
2064 OR ( polc.lookup_code = 'INVOICE OPEN'
2065 AND NVL(poll.closed_code, 'OPEN') NOT IN ('OPEN', 'CLOSED FOR RECEIVING') /* <GC FPJ>: bug2749001 */
2066 )
2067 OR ( polc.lookup_code = 'RECEIVE OPEN'
2068 AND NVL(por.consigned_consumption_flag, 'N') <> 'Y' /* CONSIGNED FPI */
2069 AND NVL(poll.closed_code, 'OPEN') NOT IN ('OPEN', 'CLOSED FOR INVOICE') /* <GC FPJ>: bug2749001 */
2070 )
2071 )
2072 )
2073 )
2074 ORDER BY polc.displayed_field;
2075 --< Bug 3194665 End >
2076
2077 BEGIN
2078 --< Bug 3194665 > Removed unnecessary std api work
2079 x_return_status := FND_API.g_ret_sts_success;
2080
2081 IF (g_debug_stmt) THEN
2082 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
2083 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix || l_api_name ||
2084 '.invoked', 'Subtype: ' || NVL(p_doc_subtype,'null') ||
2085 ', Line Loc ID: ' || NVL(TO_CHAR(p_doc_line_loc_id),'null'));
2086 END IF;
2087 END IF;
2088
2089 OPEN l_get_actions_csr;
2090 --< Bug 3194665 Start >
2091 -- Select displayed_field and lookup_code
2092 FETCH l_get_actions_csr BULK COLLECT INTO x_displayed_field_tbl,
2093 x_lookup_code_tbl;
2094
2095 IF (l_get_actions_csr%ROWCOUNT = 0) THEN
2096 -- No data found, so just return error status without a msg
2097 IF (g_debug_stmt) THEN
2098 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR) THEN
2099 FND_LOG.message(FND_LOG.level_error, g_module_prefix||l_api_name||
2100 '.get_failed', FALSE);
2101 END IF;
2102 END IF;
2103 x_return_status := FND_API.g_ret_sts_error;
2104 END IF;
2105 --< Bug 3194665 End >
2106 CLOSE l_get_actions_csr;
2107
2108 EXCEPTION
2109 --< Bug 3194665 > Removed unnecessary std api exception block
2110 WHEN OTHERS THEN
2111 x_return_status := FND_API.g_ret_sts_unexp_error;
2112 IF l_get_actions_csr%ISOPEN THEN
2113 CLOSE l_get_actions_csr;
2114 END IF;
2115 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
2116 IF (g_debug_unexp) THEN
2117 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
2118 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
2119 l_api_name || '.others_exception', 'Exception');
2120 END IF;
2121 END IF;
2122 END get_rel_shipment_actions;
2123
2124 /**
2125 * Public Procedure: val_control_action
2126 * Requires: API message list has been initialized if p_init_msg_list is false.
2127 * Modifies: API message list.
2128 * Effects: Validates that p_action is an allowable control action to be
2129 * executed by p_agent_id on the document entity level specified. If
2130 * p_agent_id is NULL, then user authority and document access level checks
2131 * are skipped. Validates at shipment level if p_doc_line_loc_id is not NULL.
2132 * Else, validates at line level if p_doc_line_id is not NULL. Else, validates
2133 * at header level if p_doc_id is not NULL. Control actions supported for
2134 * p_action are: 'CANCEL'. Requisitions are currently not supported. Appends
2135 * to API message list on error.
2136 * Returns:
2137 * x_return_status - FND_API.G_RET_STS_SUCCESS if validation succeeds
2138 * FND_API.G_RET_STS_ERROR if validation fails
2139 * FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
2140 * x_control_level - g_header_level
2141 * g_line_level
2142 * g_shipment_level
2143 * g_rel_header_level
2144 * g_rel_shipment_level
2145 */
2146 PROCEDURE val_control_action
2147 (p_api_version IN NUMBER,
2148 p_init_msg_list IN VARCHAR2,
2149 x_return_status OUT NOCOPY VARCHAR2,
2150 p_doc_type IN PO_DOCUMENT_TYPES.document_type_code%TYPE,
2151 p_doc_subtype IN PO_DOCUMENT_TYPES.document_subtype%TYPE,
2152 p_doc_id IN NUMBER,
2153 p_doc_line_id IN NUMBER,
2154 p_doc_line_loc_id IN NUMBER,
2155 p_action IN VARCHAR2,
2156 p_agent_id IN PO_HEADERS.agent_id%TYPE,
2157 x_control_level OUT NOCOPY NUMBER)
2158 IS
2159
2160 l_api_name CONSTANT VARCHAR2(30) := 'val_control_action';
2161 l_api_version CONSTANT NUMBER := 1.0;
2162 l_allowable_actions_tbl g_lookup_code_tbl_type; --< Bug 3194665 >
2163 l_displayed_field_tbl g_displayed_field_tbl_type; --< Bug 3194665 >
2164 l_action PO_LOOKUP_CODES.lookup_code%TYPE := p_action;
2165 l_action_ok BOOLEAN;
2166 l_current_entity_changed VARCHAR2(1); --<CancelPO FPJ>
2167 -- <SERVICES OTL FPJ START>
2168 l_progress VARCHAR2(3) := '000';
2169 l_otl_field_name VARCHAR2(20);
2170 l_otl_field_value NUMBER;
2171 l_timecard_exists BOOLEAN;
2172 l_return_status VARCHAR2(1);
2173 -- <SERVICES OTL FPJ END>
2174
2175 BEGIN
2176 -- Start standard API initialization
2177 IF FND_API.to_boolean(p_init_msg_list) THEN
2178 FND_MSG_PUB.initialize;
2179 END IF;
2180 IF NOT FND_API.compatible_api_call(l_api_version, p_api_version,
2181 l_api_name, g_pkg_name)
2182 THEN
2183 RAISE FND_API.g_exc_unexpected_error;
2184 END IF;
2185 x_return_status := FND_API.g_ret_sts_success;
2186 -- End standard API initialization
2187
2188 IF (g_debug_stmt) THEN
2189 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
2190 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix || l_api_name ||
2191 '.invoked', 'Action: ' || NVL(p_action,'null') ||
2192 ', Type: ' || NVL(p_doc_type,'null') ||
2193 ', ID: ' || NVL(TO_CHAR(p_doc_id),'null'));
2194 END IF;
2195 END IF;
2196
2197 --<CancelPO FPJ Start>
2198 PO_DOCUMENT_REVISION_GRP.Compare(
2199 p_api_version => 1.0,
2200 p_doc_id => p_doc_id,
2201 p_doc_subtype => p_doc_subtype,
2202 p_doc_type => p_doc_type,
2203 p_line_id => p_doc_line_id,
2204 p_line_location_id => p_doc_line_loc_id,
2205 x_different => l_current_entity_changed,
2206 x_return_status => x_return_status);
2207
2208 IF (x_return_status = FND_API.g_ret_sts_error) THEN
2209 RAISE FND_API.g_exc_error;
2210 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
2211 RAISE FND_API.g_exc_unexpected_error;
2212 END IF;
2213
2214 -- If the current entity: Header/Line/Shipent changed, return error
2215 IF l_current_entity_changed = 'Y' THEN
2216 FND_MESSAGE.set_name('PO','PO_CHANGED_CANT_CANCEL');
2217 FND_MSG_PUB.add;
2218 RAISE FND_API.g_exc_error;
2219 END IF;
2220 --<CancelPO FPJ End>
2221
2222 IF (p_doc_type = 'RELEASE') THEN
2223
2224 IF (p_doc_line_loc_id IS NOT NULL) THEN
2225 IF (l_action = 'CANCEL') THEN
2226 l_action := 'CANCEL REL SHIPMENT';
2227 END IF;
2228 x_control_level := g_rel_shipment_level;
2229 --< Bug 3194665 Start > New signature
2230 get_rel_shipment_actions
2231 ( p_doc_subtype => p_doc_subtype
2232 , p_doc_line_loc_id => p_doc_line_loc_id
2233 , p_agent_id => p_agent_id
2234 , x_lookup_code_tbl => l_allowable_actions_tbl
2235 , x_displayed_field_tbl => l_displayed_field_tbl
2236 , x_return_status => x_return_status
2237 );
2238 --< Bug 3194665 End >
2239 ELSIF (p_doc_id IS NOT NULL) THEN
2240 IF (l_action = 'CANCEL') THEN
2241 l_action := 'CANCEL REL';
2242 END IF;
2243 x_control_level := g_rel_header_level;
2244 --< Bug 3194665 Start > New signature
2245 get_rel_header_actions
2246 ( p_doc_subtype => p_doc_subtype
2247 , p_doc_id => p_doc_id
2248 , p_agent_id => p_agent_id
2249 , x_lookup_code_tbl => l_allowable_actions_tbl
2250 , x_displayed_field_tbl => l_displayed_field_tbl
2251 , x_return_status => x_return_status
2252 );
2253 --< Bug 3194665 End >
2254 ELSE
2255 FND_MESSAGE.set_name('PO','PO_INVALID_DOC_IDS');
2256 IF (g_debug_stmt) THEN
2257 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR) THEN
2258 FND_LOG.message(FND_LOG.level_error, g_module_prefix ||
2259 l_api_name || '.invalid_doc_ids', FALSE);
2260 END IF;
2261 END IF;
2262 FND_MSG_PUB.add;
2263 RAISE FND_API.g_exc_error;
2264 END IF; --<if p_doc_line_loc_id ...>
2265
2266 ELSIF (p_doc_type IN ('PO','PA')) THEN
2267
2268 IF (p_doc_line_loc_id IS NOT NULL) THEN
2269 IF (l_action = 'CANCEL') THEN
2270 l_action := 'CANCEL PO SHIPMENT';
2271 END IF;
2272 x_control_level := g_shipment_level;
2273 --< Bug 3194665 Start > New signature
2274 get_shipment_actions
2275 ( p_doc_type => p_doc_type
2276 , p_doc_subtype => p_doc_subtype
2277 , p_doc_line_loc_id => p_doc_line_loc_id
2278 , p_agent_id => p_agent_id
2279 , x_lookup_code_tbl => l_allowable_actions_tbl
2280 , x_displayed_field_tbl => l_displayed_field_tbl
2281 , x_return_status => x_return_status
2282 );
2283 --< Bug 3194665 End >
2284 ELSIF (p_doc_line_id IS NOT NULL) THEN
2285 IF (l_action = 'CANCEL') THEN
2286 l_action := 'CANCEL PO LINE';
2287 END IF;
2288 x_control_level := g_line_level;
2289 --< Bug 3194665 Start > New signature
2290 get_line_actions
2291 ( p_doc_subtype => p_doc_subtype
2292 , p_doc_line_id => p_doc_line_id
2293 , p_agent_id => p_agent_id
2294 , x_lookup_code_tbl => l_allowable_actions_tbl
2295 , x_displayed_field_tbl => l_displayed_field_tbl
2296 , x_return_status => x_return_status
2297 );
2298 --< Bug 3194665 End >
2299 ELSIF (p_doc_id IS NOT NULL) THEN
2300 IF (l_action = 'CANCEL') THEN
2301 l_action := 'CANCEL PO';
2302 END IF;
2303 x_control_level := g_header_level;
2304 --< Bug 3194665 Start > New signature
2305 get_header_actions
2306 ( p_doc_subtype => p_doc_subtype
2307 , p_doc_id => p_doc_id
2308 , p_agent_id => p_agent_id
2309 , x_lookup_code_tbl => l_allowable_actions_tbl
2310 , x_displayed_field_tbl => l_displayed_field_tbl
2311 , x_return_status => x_return_status
2312 );
2313 --< Bug 3194665 End >
2314 ELSE
2315 FND_MESSAGE.set_name('PO','PO_INVALID_DOC_IDS');
2316 IF (g_debug_stmt) THEN
2317 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR) THEN
2318 FND_LOG.message(FND_LOG.level_error, g_module_prefix ||
2319 l_api_name || '.invalid_doc_ids', FALSE);
2320 END IF;
2321 END IF;
2322 FND_MSG_PUB.add;
2323 RAISE FND_API.g_exc_error;
2324 END IF; --<if p_doc_line_loc_id ...>
2325
2326 ELSE
2327 -- This document type is not supported
2328 FND_MESSAGE.set_name('PO','PO_INVALID_DOC_TYPE');
2329 FND_MESSAGE.set_token('TYPE',p_doc_type);
2330 IF (g_debug_stmt) THEN
2331 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR) THEN
2332 FND_LOG.message(FND_LOG.level_error, g_module_prefix||l_api_name||
2333 '.invalid_doc_type', FALSE);
2334 END IF;
2335 END IF;
2336 FND_MSG_PUB.add;
2337 RAISE FND_API.g_exc_error;
2338 END IF; --<if p_doc_type = RELEASE>
2339
2340 -- Check if the get action procedure had an error
2341 IF (x_return_status = FND_API.g_ret_sts_error) THEN
2342 FND_MESSAGE.set_name('PO','PO_CONTROL_INVALID_ACTION');
2343 FND_MESSAGE.set_token('ACTION',p_action);
2344 IF (g_debug_stmt) THEN
2345 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR) THEN
2346 FND_LOG.message(FND_LOG.level_error, g_module_prefix||l_api_name||
2347 '.get_action_failed', FALSE);
2348 END IF;
2349 END IF;
2350 FND_MSG_PUB.add;
2351 RAISE FND_API.g_exc_error;
2352 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
2353 RAISE FND_API.g_exc_unexpected_error;
2354 END IF;
2355
2356 -- Loop through allowable actions to see if this action is in the set
2357 l_action_ok := FALSE;
2358 FOR i IN l_allowable_actions_tbl.first..l_allowable_actions_tbl.last
2359 LOOP
2360 IF (l_action = l_allowable_actions_tbl(i)) THEN
2361 l_action_ok := TRUE;
2362 EXIT;
2363 END IF;
2364 END LOOP;
2365
2366 -- If not in the set, return error
2367 IF NOT l_action_ok THEN
2368 FND_MESSAGE.set_name('PO','PO_CONTROL_INVALID_ACTION');
2369 FND_MESSAGE.set_token('ACTION',p_action);
2370 IF (g_debug_stmt) THEN
2371 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR) THEN
2372 FND_LOG.message(FND_LOG.level_error, g_module_prefix||l_api_name||
2373 '.status_failed', FALSE);
2374 END IF;
2375 END IF;
2376 FND_MSG_PUB.add;
2377 RAISE FND_API.g_exc_error;
2378 END IF;
2379
2380 -- <SERVICES OTL FPJ START>
2381 l_progress := '100';
2382 -- For cancel or finally close on Standard POs, call the OTL API to check
2383 -- if there are any submitted/approved timecards associated with the
2384 -- PO header/line. If so, prevent the control action by returning an error.
2385 IF (p_doc_type = 'PO') AND (p_doc_subtype = 'STANDARD')
2386 AND (p_action IN ('CANCEL', 'FINALLY CLOSE')) THEN
2387
2388 IF (x_control_level = g_header_level) THEN
2389 l_otl_field_name := PO_HXC_INTERFACE_PVT.field_PO_HEADER_ID;
2390 l_otl_field_value := p_doc_id;
2391 ELSE -- line or shipment level
2392 l_otl_field_name := PO_HXC_INTERFACE_PVT.field_PO_LINE_ID;
2393 l_otl_field_value := p_doc_line_id;
2394 END IF; -- x_control_level
2395
2396 -- Bug 3537441 Call the new interface package.
2397 PO_HXC_INTERFACE_PVT.check_timecard_exists (
2398 p_api_version => 1.0,
2399 x_return_status => l_return_status,
2400 p_field_name => l_otl_field_name,
2401 p_field_value => l_otl_field_value,
2402 p_end_date => NULL,
2403 x_timecard_exists => l_timecard_exists
2404 );
2405 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2406 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2407 END IF;
2408
2409 IF (l_timecard_exists) THEN
2410 FND_MESSAGE.set_name('PO','PO_CONTROL_OTL_INVALID_ACTION');
2411 FND_MSG_PUB.add;
2412 RAISE FND_API.g_exc_error;
2413 END IF;
2414
2415 END IF; -- p_doc_type = 'PO'
2416 -- <SERVICES OTL FPJ END>
2417
2418 EXCEPTION
2419 WHEN FND_API.g_exc_error THEN
2420 x_return_status := FND_API.g_ret_sts_error;
2421 WHEN FND_API.g_exc_unexpected_error THEN
2422 x_return_status := FND_API.g_ret_sts_unexp_error;
2423 WHEN OTHERS THEN
2424 x_return_status := FND_API.g_ret_sts_unexp_error;
2425 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
2426 IF (g_debug_unexp) THEN
2427 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
2428 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
2429 l_api_name || '.others_exception', 'Exception');
2430 END IF;
2431 END IF;
2432 END val_control_action;
2433
2434 /**
2435 * Public Procedure: po_stop_wf_process
2436 * Requires: API message list has been initialized if p_init_msg_list is false.
2437 * Modifies: Workflow processes for this document, API message list.
2438 * Effects: Stops any pending workflow process and respond notification for
2439 * this document. Also does the same for any unapproved releases against this
2440 * document with authorization status INCOMPLETE, REJECTED, or REQUIRES
2441 * APPROVAL. Appends to API message list on error.
2442 * Returns:
2443 * x_return_status - FND_API.G_RET_STS_SUCCESS if no errors occur
2444 * FND_API.G_RET_STS_ERROR if error occurs
2445 * FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
2446 */
2447 PROCEDURE po_stop_wf_process
2448 (p_api_version IN NUMBER,
2449 p_init_msg_list IN VARCHAR2,
2450 x_return_status OUT NOCOPY VARCHAR2,
2451 p_doc_type IN PO_DOCUMENT_TYPES.document_type_code%TYPE,
2452 p_doc_subtype IN PO_DOCUMENT_TYPES.document_subtype%TYPE,
2453 p_doc_id IN NUMBER)
2454 IS
2455
2456 CURSOR l_po_wf_csr IS
2457 SELECT poh.wf_item_type, poh.wf_item_key
2458 FROM po_headers poh
2459 WHERE poh.po_header_id = p_doc_id;
2460 CURSOR l_unapproved_releases_csr IS
2461 -- SQL What: Querying PO_HEADERS, PO_RELEASES for unapproved releases
2462 -- SQL Why: Need to stop wf processes for unapproved releases
2463 -- SQL Join: po_header_id
2464 SELECT por.po_release_id, poh.type_lookup_code,
2465 por.wf_item_type, por.wf_item_key
2466 FROM po_releases por,
2467 po_headers poh
2468 WHERE por.po_header_id = p_doc_id AND
2469 por.po_header_id = poh.po_header_id AND
2470 NVL(por.authorization_status,'INCOMPLETE') IN
2471 ('INCOMPLETE','REJECTED','REQUIRES REAPPROVAL') AND
2472 NVL(por.cancel_flag,'N') = 'N' AND
2473 NVL(por.closed_code,'OPEN') <> 'FINALLY CLOSED';
2474
2475 -- Bulk processing types and variables
2476 TYPE release_id_tbl_type IS TABLE OF PO_RELEASES.po_release_id%TYPE
2477 INDEX BY BINARY_INTEGER;
2478 TYPE doc_subtype_tbl_type IS TABLE OF PO_HEADERS.type_lookup_code%TYPE
2479 INDEX BY BINARY_INTEGER;
2480 TYPE wf_item_type_tbl_type IS TABLE OF PO_RELEASES.wf_item_type%TYPE
2481 INDEX BY BINARY_INTEGER;
2482 TYPE wf_item_key_tbl_type IS TABLE OF PO_RELEASES.wf_item_key%TYPE
2483 INDEX BY BINARY_INTEGER;
2484 l_release_id_tbl release_id_tbl_type;
2485 l_doc_subtype_tbl doc_subtype_tbl_type;
2486 l_wf_item_type_tbl wf_item_type_tbl_type;
2487 l_wf_item_key_tbl wf_item_key_tbl_type;
2488
2489 l_api_name CONSTANT VARCHAR2(30) := 'po_stop_wf_process';
2490 l_api_version CONSTANT NUMBER := 1.0;
2491 l_wf_item_type PO_HEADERS.wf_item_type%TYPE;
2492 l_wf_item_key PO_HEADERS.wf_item_key%TYPE;
2493 l_num_fetched NUMBER := 0; -- number of rows fetched at each iteration
2494
2495 BEGIN
2496 -- Start standard API initialization
2497 IF FND_API.to_boolean(p_init_msg_list) THEN
2498 FND_MSG_PUB.initialize;
2499 END IF;
2500 IF NOT FND_API.compatible_api_call(l_api_version, p_api_version,
2501 l_api_name, g_pkg_name)
2502 THEN
2503 RAISE FND_API.g_exc_unexpected_error;
2504 END IF;
2505 x_return_status := FND_API.g_ret_sts_success;
2506 -- End standard API initialization
2507
2508 IF (g_debug_stmt) THEN
2509 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
2510 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix || l_api_name ||
2511 '.invoked', 'Type: ' || NVL(p_doc_type,'null') ||
2512 ', ID: ' || NVL(TO_CHAR(p_doc_id),'null'));
2513 END IF;
2514 END IF;
2515
2516 OPEN l_po_wf_csr;
2517 FETCH l_po_wf_csr INTO l_wf_item_type, l_wf_item_key;
2518 IF l_po_wf_csr%NOTFOUND THEN
2519 FND_MESSAGE.set_name('PO','PO_INVALID_DOC_IDS');
2520 IF (g_debug_stmt) THEN
2521 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR) THEN
2522 FND_LOG.message(FND_LOG.level_error, g_module_prefix||l_api_name||
2523 '.invalid_doc_ids', FALSE);
2524 END IF;
2525 END IF;
2526 FND_MSG_PUB.add;
2527 RAISE FND_API.g_exc_error;
2528 END IF;
2529 CLOSE l_po_wf_csr;
2530
2531 -- Cancel any approval reminder notifications. 'N' means not a release
2532 PO_APPROVAL_REMINDER_SV.cancel_notif(p_doc_subtype, p_doc_id, 'N');
2533
2534 IF (l_wf_item_type IS NOT NULL) AND (l_wf_item_key IS NOT NULL) THEN
2535 -- Stop any active workflows for this document
2536 PO_APPROVAL_REMINDER_SV.stop_process(l_wf_item_type, l_wf_item_key);
2537 END IF;
2538
2539 -- Blankets and Planned PO's: check if there are any unapproved releases
2540 IF (p_doc_subtype IN ('BLANKET','PLANNED')) THEN
2541 OPEN l_unapproved_releases_csr;
2542 LOOP
2543 FETCH l_unapproved_releases_csr
2544 BULK COLLECT INTO l_release_id_tbl, l_doc_subtype_tbl,
2545 l_wf_item_type_tbl, l_wf_item_key_tbl LIMIT 1000;
2546
2547 -- Loop through the unapproved releases tables to stop wf
2548 -- processes for each release found
2549 FOR i IN 1..l_release_id_tbl.count LOOP
2550 IF (l_doc_subtype_tbl(i) = 'PLANNED') THEN
2551 l_doc_subtype_tbl(i) := 'SCHEDULED';
2552 END IF;
2553
2554 -- Cancel any approval reminder notifications. 'Y' = release
2555 PO_APPROVAL_REMINDER_SV.cancel_notif
2556 (l_doc_subtype_tbl(i), l_release_id_tbl(i), 'Y');
2557
2558 IF (l_wf_item_type_tbl(i) IS NOT NULL) AND
2559 (l_wf_item_key_tbl(i) IS NOT NULL)
2560 THEN
2561 -- Stop any active workflows for this document
2562 PO_APPROVAL_REMINDER_SV.stop_process
2563 (l_wf_item_type_tbl(i), l_wf_item_key_tbl(i));
2564 END IF; --<if l_wf_item_type_tbl ...>
2565 END LOOP; --<for loop>
2566
2567 EXIT WHEN l_unapproved_releases_csr%NOTFOUND;
2568 END LOOP;
2569
2570 CLOSE l_unapproved_releases_csr;
2571
2572 END IF; --<if p_doc_subtype in ...>
2573
2574 EXCEPTION
2575 WHEN FND_API.g_exc_error THEN
2576 x_return_status := FND_API.g_ret_sts_error;
2577 IF l_po_wf_csr%ISOPEN THEN
2578 CLOSE l_po_wf_csr;
2579 END IF;
2580 IF l_unapproved_releases_csr%ISOPEN THEN
2581 CLOSE l_unapproved_releases_csr;
2582 END IF;
2583 WHEN FND_API.g_exc_unexpected_error THEN
2584 x_return_status := FND_API.g_ret_sts_unexp_error;
2585 IF l_po_wf_csr%ISOPEN THEN
2586 CLOSE l_po_wf_csr;
2587 END IF;
2588 IF l_unapproved_releases_csr%ISOPEN THEN
2589 CLOSE l_unapproved_releases_csr;
2590 END IF;
2591 WHEN OTHERS THEN
2592 x_return_status := FND_API.g_ret_sts_unexp_error;
2593 IF l_po_wf_csr%ISOPEN THEN
2594 CLOSE l_po_wf_csr;
2595 END IF;
2596 IF l_unapproved_releases_csr%ISOPEN THEN
2597 CLOSE l_unapproved_releases_csr;
2598 END IF;
2599 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
2600 IF (g_debug_unexp) THEN
2601 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
2602 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
2603 l_api_name || '.others_exception', 'Exception');
2604 END IF;
2605 END IF;
2606 END po_stop_wf_process;
2607
2608 /**
2609 * Public Procedure: rel_stop_wf_process
2610 * Requires: API message list has been initialized if p_init_msg_list is false.
2611 * Modifies: Workflow processes for this document, API message list.
2612 * Effects: Stops any pending workflow process and respond notification for
2613 * this document. Appends to API message list on error.
2614 * Returns:
2615 * x_return_status - FND_API.G_RET_STS_SUCCESS if there are no errors
2616 * FND_API.G_RET_STS_ERROR if error occurs
2617 * FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
2618 */
2619 PROCEDURE rel_stop_wf_process
2620 (p_api_version IN NUMBER,
2621 p_init_msg_list IN VARCHAR2,
2622 x_return_status OUT NOCOPY VARCHAR2,
2623 p_doc_type IN PO_DOCUMENT_TYPES.document_type_code%TYPE,
2624 p_doc_subtype IN PO_DOCUMENT_TYPES.document_subtype%TYPE,
2625 p_doc_id IN NUMBER)
2626 IS
2627
2628 CURSOR l_rel_wf_csr IS
2629 SELECT por.wf_item_type, por.wf_item_key
2630 FROM po_releases por
2631 WHERE por.po_release_id = p_doc_id;
2632
2633 l_api_name CONSTANT VARCHAR2(30) := 'rel_stop_wf_process';
2634 l_api_version CONSTANT NUMBER := 1.0;
2635 l_wf_item_type PO_RELEASES.wf_item_type%TYPE;
2636 l_wf_item_key PO_RELEASES.wf_item_key%TYPE;
2637
2638 BEGIN
2639 -- Start standard API initialization
2640 IF FND_API.to_boolean(p_init_msg_list) THEN
2641 FND_MSG_PUB.initialize;
2642 END IF;
2643 IF NOT FND_API.compatible_api_call(l_api_version, p_api_version,
2644 l_api_name, g_pkg_name)
2645 THEN
2646 RAISE FND_API.g_exc_unexpected_error;
2647 END IF;
2648 x_return_status := FND_API.g_ret_sts_success;
2649 -- End standard API initialization
2650
2651 IF (g_debug_stmt) THEN
2652 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
2653 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix || l_api_name ||
2654 '.invoked', 'Type: ' || NVL(p_doc_type,'null') ||
2655 ', ID: ' || NVL(TO_CHAR(p_doc_id),'null'));
2656 END IF;
2657 END IF;
2658
2659 OPEN l_rel_wf_csr;
2660 FETCH l_rel_wf_csr INTO l_wf_item_type, l_wf_item_key;
2661 IF l_rel_wf_csr%NOTFOUND THEN
2662 FND_MESSAGE.set_name('PO','PO_INVALID_DOC_IDS');
2663 IF (g_debug_stmt) THEN
2664 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR) THEN
2665 FND_LOG.message(FND_LOG.level_error, g_module_prefix||l_api_name||
2666 '.invalid_doc_ids', FALSE);
2667 END IF;
2668 END IF;
2669 FND_MSG_PUB.add;
2670 RAISE FND_API.g_exc_error;
2671 END IF;
2672 CLOSE l_rel_wf_csr;
2673
2674 -- Cancel any approval reminder notifications. 'Y' means this is a release
2675 PO_APPROVAL_REMINDER_SV.cancel_notif(p_doc_subtype, p_doc_id, 'Y');
2676
2677 IF (l_wf_item_type IS NOT NULL) AND (l_wf_item_key IS NOT NULL) THEN
2678 -- Stop any active workflows for this release
2679 PO_APPROVAL_REMINDER_SV.stop_process(l_wf_item_type, l_wf_item_key);
2680 END IF;
2681
2682 EXCEPTION
2683 WHEN FND_API.g_exc_error THEN
2684 x_return_status := FND_API.g_ret_sts_error;
2685 IF l_rel_wf_csr%ISOPEN THEN
2686 CLOSE l_rel_wf_csr;
2687 END IF;
2688 WHEN FND_API.g_exc_unexpected_error THEN
2689 x_return_status := FND_API.g_ret_sts_unexp_error;
2690 IF l_rel_wf_csr%ISOPEN THEN
2691 CLOSE l_rel_wf_csr;
2692 END IF;
2693 WHEN OTHERS THEN
2694 x_return_status := FND_API.g_ret_sts_unexp_error;
2695 IF l_rel_wf_csr%ISOPEN THEN
2696 CLOSE l_rel_wf_csr;
2697 END IF;
2698 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
2699 IF (g_debug_unexp) THEN
2700 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
2701 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
2702 l_api_name || '.others_exception', 'Exception');
2703 END IF;
2704 END IF;
2705 END rel_stop_wf_process;
2706
2707
2708 /**
2709 * Private Procedure: submit_po_print_request
2710 * Requires: API message list has been initialized if p_init_msg_list is false.
2711 * Modifies: API message list.
2712 * Effects: Submits a concurrent request to print the document specified.
2713 * Appends to API message list on error.
2714 * Returns:
2715 * x_return_status - FND_API.G_RET_STS_SUCCESS if request was created
2716 * FND_API.G_RET_STS_ERROR if request was not created
2717 * FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
2718 * x_request_id - The ID of the print request
2719 */
2720 PROCEDURE submit_po_print_request
2721 (p_api_version IN NUMBER,
2722 x_return_status OUT NOCOPY VARCHAR2,
2723 p_doc_num IN VARCHAR2,
2724 p_user_id IN VARCHAR2,
2725 p_qty_precision IN VARCHAR2,
2726 x_request_id OUT NOCOPY NUMBER)
2727 IS
2728
2729 l_api_name CONSTANT VARCHAR2(30) := 'submit_po_print_request';
2730 l_api_version CONSTANT NUMBER := 1.0;
2731 l_set_lang boolean;
2732
2733 BEGIN
2734 -- Start standard API initialization
2735 IF NOT FND_API.compatible_api_call(l_api_version, p_api_version,
2736 l_api_name, g_pkg_name)
2737 THEN
2738 RAISE FND_API.g_exc_unexpected_error;
2739 END IF;
2740 x_return_status := FND_API.g_ret_sts_success;
2741 -- End standard API initialization
2742
2743 IF (g_debug_stmt) THEN
2744 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
2745 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix || l_api_name ||
2746 '.invoked', 'Doc num: ' || NVL(p_doc_num,'null'));
2747 END IF;
2748 END IF;
2749
2750 -- Only pass in necessary params. After the last necessary param, pass in
2751 -- FND_GLOBAL.local_chr(0) to signify end of param list, allowing the rest
2752 -- to be skipped. Defaulting the remainders would be more expensive to do.
2753
2754 --<R12 MOAC START>
2755 po_moac_utils_pvt.set_request_context(po_moac_utils_pvt.get_current_org_id);
2756 --<R12 MOAC END>
2757 l_set_lang := fnd_request.set_options('NO', 'NO', NULL,NULL, NULL, FND_PROFILE.VALUE('ICX_NUMERIC_CHARACTERS'));
2758 x_request_id := FND_REQUEST.submit_request
2759 ( 'PO',
2760 'POXPPO',
2761 NULL,
2762 NULL,
2763 FALSE,
2764 'P_REPORT_TYPE=R',
2765 'P_TEST_FLAG=N',
2766 'P_PO_NUM_FROM=' || p_doc_num,
2767 'P_PO_NUM_TO=' || p_doc_num,
2768 'P_USER_ID=' || p_user_id,
2769 'P_QTY_PRECISION=' || p_qty_precision,
2770 FND_GLOBAL.local_chr(0),
2771 NULL, NULL,
2772 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
2773 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
2774 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
2775 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
2776 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
2777 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
2778 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
2779 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
2780 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
2781 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
2782 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
2783 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
2784 NULL, NULL, NULL, NULL, NULL, NULL, NULL );
2785
2786 IF (x_request_id = 0) THEN
2787 -- The call to FND_REQUEST sets a message name on error
2788 IF (g_debug_stmt) THEN
2789 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR) THEN
2790 FND_LOG.message(FND_LOG.level_error, g_module_prefix||l_api_name||
2791 '.request_failed', FALSE);
2792 END IF;
2793 END IF;
2794 FND_MSG_PUB.add;
2795 RAISE FND_API.g_exc_error;
2796 END IF;
2797
2798 EXCEPTION
2799 WHEN FND_API.g_exc_error THEN
2800 x_return_status := FND_API.g_ret_sts_error;
2801 WHEN FND_API.g_exc_unexpected_error THEN
2802 x_return_status := FND_API.g_ret_sts_unexp_error;
2803 WHEN OTHERS THEN
2804 x_return_status := FND_API.g_ret_sts_unexp_error;
2805 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
2806 IF (g_debug_unexp) THEN
2807 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
2808 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
2809 l_api_name || '.others_exception', 'Exception');
2810 END IF;
2811 END IF;
2812 END submit_po_print_request;
2813
2814
2815 /**
2816 * Private Procedure: submit_rel_print_request
2817 * Requires: API message list has been initialized if p_init_msg_list is false.
2818 * Modifies: API message list
2819 * Effects: Submits a concurrent request to print the release specified.
2820 * Appends to API message list on error.
2821 * Returns:
2822 * x_return_status - FND_API.G_RET_STS_SUCCESS if request was created
2823 * FND_API.G_RET_STS_ERROR if request was not created
2824 * FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
2825 * x_request_id - The ID of the print request
2826 */
2827 PROCEDURE submit_rel_print_request
2828 (p_api_version IN NUMBER,
2829 x_return_status OUT NOCOPY VARCHAR2,
2830 p_doc_num IN VARCHAR2,
2831 p_rel_doc_num IN VARCHAR2,
2832 p_user_id IN VARCHAR2,
2833 p_qty_precision IN VARCHAR2,
2834 x_request_id OUT NOCOPY NUMBER)
2835 IS
2836
2837 l_api_name CONSTANT VARCHAR2(30) := 'submit_rel_print_request';
2838 l_api_version CONSTANT NUMBER := 1.0;
2839 l_set_lang boolean;
2840
2841 BEGIN
2842 -- Start standard API initialization
2843 IF NOT FND_API.compatible_api_call(l_api_version, p_api_version,
2844 l_api_name, g_pkg_name)
2845 THEN
2846 RAISE FND_API.g_exc_unexpected_error;
2847 END IF;
2848 x_return_status := FND_API.g_ret_sts_success;
2849 -- End standard API initialization
2850
2851 IF (g_debug_stmt) THEN
2852 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
2853 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix || l_api_name ||
2854 '.invoked', 'Doc num: ' || NVL(p_doc_num,'null') ||
2855 ', Rel num: ' || NVL(p_rel_doc_num,'null'));
2856 END IF;
2857 END IF;
2858
2859 -- Only pass in necessary params. After the last necessary param, pass in
2860 -- FND_GLOBAL.local_chr(0) to signify end of param list, allowing the rest
2861 -- to be skipped. Defaulting the remainders would be more expensive to do.
2862
2863 --<R12 MOAC START>
2864 po_moac_utils_pvt.set_request_context(po_moac_utils_pvt.get_current_org_id);
2865 --<R12 MOAC END>
2866 l_set_lang := fnd_request.set_options('NO', 'NO', NULL,NULL, NULL, FND_PROFILE.VALUE('ICX_NUMERIC_CHARACTERS'));
2867 x_request_id := FND_REQUEST.submit_request
2868 ( 'PO',
2869 'POXPPO',
2870 NULL,
2871 NULL,
2872 FALSE,
2873 'P_REPORT_TYPE=R',
2874 'P_TEST_FLAG=N',
2875 'P_PO_NUM_FROM=' || p_doc_num,
2876 'P_PO_NUM_TO=' || p_doc_num,
2877 'P_RELEASE_NUM_FROM=' || p_rel_doc_num,
2878 'P_RELEASE_NUM_TO=' || p_rel_doc_num,
2879 'P_USER_ID=' || p_user_id,
2880 'P_QTY_PRECISION=' || p_qty_precision,
2881 FND_GLOBAL.local_chr(0),
2882 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
2883 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
2884 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
2885 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
2886 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
2887 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
2888 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
2889 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
2890 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
2891 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
2892 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
2893 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
2894 NULL, NULL, NULL, NULL, NULL, NULL, NULL );
2895
2896 IF (x_request_id = 0) THEN
2897 -- The call to FND_REQUEST sets a message name on error
2898 IF (g_debug_stmt) THEN
2899 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR) THEN
2900 FND_LOG.message(FND_LOG.level_error, g_module_prefix||l_api_name||
2901 '.request_failed', FALSE);
2902 END IF;
2903 END IF;
2904 FND_MSG_PUB.add;
2905 RAISE FND_API.g_exc_error;
2906 END IF;
2907
2908 EXCEPTION
2909 WHEN FND_API.g_exc_error THEN
2910 x_return_status := FND_API.g_ret_sts_error;
2911 WHEN FND_API.g_exc_unexpected_error THEN
2912 x_return_status := FND_API.g_ret_sts_unexp_error;
2913 WHEN OTHERS THEN
2914 x_return_status := FND_API.g_ret_sts_unexp_error;
2915 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
2916 IF (g_debug_unexp) THEN
2917 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
2918 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
2919 l_api_name || '.others_exception', 'Exception');
2920 END IF;
2921 END IF;
2922 END submit_rel_print_request;
2923
2924
2925 /**
2926 * Private Procedure: submit_req_print_request
2927 * Requires: API message list has been initialized if p_init_msg_list is false.
2928 * Modifies: API message list
2929 * Effects: Submits a concurrent request to print the requistion specified.
2930 * Appends to API message list on error.
2931 * Returns:
2932 * x_return_status - FND_API.G_RET_STS_SUCCESS if request was created
2933 * FND_API.G_RET_STS_ERROR if request was not created
2934 * FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
2935 * x_request_id - The ID of the print request
2936 */
2937 PROCEDURE submit_req_print_request
2938 (p_api_version IN NUMBER,
2939 x_return_status OUT NOCOPY VARCHAR2,
2940 p_doc_num IN VARCHAR2,
2941 p_user_id IN VARCHAR2,
2942 p_qty_precision IN VARCHAR2,
2943 x_request_id OUT NOCOPY NUMBER)
2944 IS
2945
2946 l_api_name CONSTANT VARCHAR2(30) := 'submit_req_print_request';
2947 l_api_version CONSTANT NUMBER := 1.0;
2948
2949 BEGIN
2950 -- Start standard API initialization
2951 IF NOT FND_API.compatible_api_call(l_api_version, p_api_version,
2952 l_api_name, g_pkg_name)
2953 THEN
2954 RAISE FND_API.g_exc_unexpected_error;
2955 END IF;
2956 x_return_status := FND_API.g_ret_sts_success;
2957 -- End standard API initialization
2958
2959 IF (g_debug_stmt) THEN
2960 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
2961 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix || l_api_name ||
2962 '.invoked', 'Doc num: ' || NVL(p_doc_num,'null'));
2963 END IF;
2964 END IF;
2965
2966 -- Only pass in necessary params. After the last necessary param, pass in
2967 -- FND_GLOBAL.local_chr(0) to signify end of param list, allowing the rest
2968 -- to be skipped. Defaulting the remainders would be more expensive to do.
2969
2970
2971 --<R12 MOAC START>
2972 po_moac_utils_pvt.set_request_context(po_moac_utils_pvt.get_current_org_id);
2973 --<R12 MOAC END>
2974
2975 x_request_id := FND_REQUEST.submit_request
2976 ( 'PO',
2977 'PRINTREQ',
2978 NULL,
2979 NULL,
2980 FALSE,
2981 'P_REQ_NUM_FROM=' || p_doc_num,
2982 'P_REQ_NUM_TO=' || p_doc_num,
2983 'P_QTY_PRECISION=' || p_qty_precision,
2984 FND_GLOBAL.local_chr(0),
2985 NULL, NULL, NULL, NULL, NULL,
2986 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
2987 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
2988 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
2989 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
2990 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
2991 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
2992 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
2993 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
2994 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
2995 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
2996 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
2997 NULL, NULL, NULL, NULL, NULL, NULL, NULL,
2998 NULL, NULL, NULL, NULL, NULL, NULL, NULL );
2999
3000 IF (x_request_id = 0) THEN
3001 -- The call to FND_REQUEST sets a message name on error
3002 IF (g_debug_stmt) THEN
3003 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR) THEN
3004 FND_LOG.message(FND_LOG.level_error, g_module_prefix||l_api_name||
3005 '.request_failed', FALSE);
3006 END IF;
3007 END IF;
3008 FND_MSG_PUB.add;
3009 RAISE FND_API.g_exc_error;
3010 END IF;
3011
3012 EXCEPTION
3013 WHEN FND_API.g_exc_error THEN
3014 x_return_status := FND_API.g_ret_sts_error;
3015 WHEN FND_API.g_exc_unexpected_error THEN
3016 x_return_status := FND_API.g_ret_sts_unexp_error;
3017 WHEN OTHERS THEN
3018 x_return_status := FND_API.g_ret_sts_unexp_error;
3019 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
3020 IF (g_debug_unexp) THEN
3021 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
3022 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
3023 l_api_name || '.others_exception', 'Exception');
3024 END IF;
3025 END IF;
3026 END submit_req_print_request;
3027
3028
3029 /**
3030 * Public Procedure: create_print_request
3031 * Requires: API message list has been initialized if p_init_msg_list is false.
3032 * Modifies: API message list
3033 * Effects: Creates a request to print the document specified. Appends to API
3034 * message list on error.
3035 * Returns:
3036 * x_return_status - FND_API.G_RET_STS_SUCCESS if request was created
3037 * FND_API.G_RET_STS_ERROR if request was not created
3038 * FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
3039 * x_request_id - The ID of the print request
3040 */
3041 PROCEDURE create_print_request
3042 (p_api_version IN NUMBER,
3043 p_init_msg_list IN VARCHAR2,
3044 x_return_status OUT NOCOPY VARCHAR2,
3045 p_doc_type IN PO_DOCUMENT_TYPES.document_type_code%TYPE,
3046 p_doc_num IN VARCHAR2,
3047 p_rel_doc_num IN VARCHAR2,
3048 x_request_id OUT NOCOPY NUMBER)
3049 IS
3050
3051 l_api_name CONSTANT VARCHAR2(30) := 'create_print_request';
3052 l_api_version CONSTANT NUMBER := 1.0;
3053 l_qty_precision FND_PROFILE_OPTION_VALUES.profile_option_value%TYPE;
3054 l_user_id FND_PROFILE_OPTION_VALUES.profile_option_value%TYPE;
3055
3056 BEGIN
3057 -- Start standard API initialization
3058 IF FND_API.to_boolean(p_init_msg_list) THEN
3059 FND_MSG_PUB.initialize;
3060 END IF;
3061 IF NOT FND_API.compatible_api_call(l_api_version, p_api_version,
3062 l_api_name, g_pkg_name)
3063 THEN
3064 RAISE FND_API.g_exc_unexpected_error;
3065 END IF;
3066 x_return_status := FND_API.g_ret_sts_success;
3067 -- End standard API initialization
3068
3069 IF (g_debug_stmt) THEN
3070 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
3071 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix || l_api_name ||
3072 '.invoked', 'Type: ' || NVL(p_doc_type,'null') ||
3073 ', Doc num: ' || NVL(p_doc_num,'null') ||
3074 ', Rel num: ' || NVL(p_rel_doc_num,'null'));
3075 END IF;
3076 END IF;
3077
3078 FND_PROFILE.get('REPORT_QUANTITY_PRECISION', l_qty_precision);
3079 FND_PROFILE.get('USER_ID', l_user_id);
3080
3081 IF (p_doc_type IN ('PO','PA')) THEN
3082
3083 submit_po_print_request( p_api_version => 1.0,
3084 x_return_status => x_return_status,
3085 p_doc_num => p_doc_num,
3086 p_user_id => l_user_id,
3087 p_qty_precision => l_qty_precision,
3088 x_request_id => x_request_id);
3089
3090 ELSIF (p_doc_type = 'RELEASE') THEN
3091
3092 submit_rel_print_request( p_api_version => 1.0,
3093 x_return_status => x_return_status,
3094 p_doc_num => p_doc_num,
3095 p_rel_doc_num => p_rel_doc_num,
3096 p_user_id => l_user_id,
3097 p_qty_precision => l_qty_precision,
3098 x_request_id => x_request_id);
3099
3100 ELSIF (p_doc_type = 'REQUISITION') THEN
3101
3102 submit_req_print_request( p_api_version => 1.0,
3103 x_return_status => x_return_status,
3104 p_doc_num => p_doc_num,
3105 p_user_id => l_user_id,
3106 p_qty_precision => l_qty_precision,
3107 x_request_id => x_request_id);
3108
3109 ELSE
3110 -- This document type is not supported
3111 FND_MESSAGE.set_name('PO','PO_INVALID_DOC_TYPE');
3112 FND_MESSAGE.set_token('TYPE',p_doc_type);
3113 IF (g_debug_stmt) THEN
3114 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR) THEN
3115 FND_LOG.message(FND_LOG.level_error, g_module_prefix||l_api_name||
3116 '.invalid_doc_type', FALSE);
3117 END IF;
3118 END IF;
3119 FND_MSG_PUB.add;
3120 RAISE FND_API.g_exc_error;
3121 END IF;
3122
3123 EXCEPTION
3124 WHEN FND_API.g_exc_error THEN
3125 x_return_status := FND_API.g_ret_sts_error;
3126 WHEN FND_API.g_exc_unexpected_error THEN
3127 x_return_status := FND_API.g_ret_sts_unexp_error;
3128 WHEN OTHERS THEN
3129 x_return_status := FND_API.g_ret_sts_unexp_error;
3130 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
3131 IF (g_debug_unexp) THEN
3132 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
3133 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
3134 l_api_name || '.others_exception', 'Exception');
3135 END IF;
3136 END IF;
3137 END create_print_request;
3138
3139
3140 /**
3141 * Public Procedure: update_note_to_vendor
3142 * Requires: API message list has been initialized if p_init_msg_list is false.
3143 * Modifies: note_to_vendor in PO_HEADERS, PO_LINES, or PO_RELEASES. API message
3144 * list.
3145 * Effects: Updates the note_to_vendor column of PO_HEADERS, PO_LINES, or
3146 * PO_RELEASES depending upon p_doc_type. If p_doc_line_id is not NULL and the
3147 * document is not a RELEASE, then updates PO_LINES. All changes will be
3148 * committed upon success if p_commit is FND_API.G_TRUE. Appends to API
3149 * message list on error.
3150 * Returns:
3151 * x_return_status - FND_API.G_RET_STS_SUCCESS if the update was successful
3152 * FND_API.G_RET_STS_ERROR if no update was made
3153 * FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
3154 */
3155 PROCEDURE update_note_to_vendor
3156 (p_api_version IN NUMBER,
3157 p_init_msg_list IN VARCHAR2,
3158 p_commit IN VARCHAR2,
3159 x_return_status OUT NOCOPY VARCHAR2,
3160 p_doc_type IN PO_DOCUMENT_TYPES.document_type_code%TYPE,
3161 p_doc_id IN NUMBER,
3162 p_doc_line_id IN NUMBER,
3163 p_note_to_vendor IN PO_HEADERS.note_to_vendor%TYPE)
3164 IS
3165
3166 l_api_name CONSTANT VARCHAR2(30) := 'update_note_to_vendor';
3167 l_api_version CONSTANT NUMBER := 1.0;
3168
3169 BEGIN
3170 -- Start standard API initialization
3171 SAVEPOINT update_note_to_vendor_PVT;
3172 IF FND_API.to_boolean(p_init_msg_list) THEN
3173 FND_MSG_PUB.initialize;
3174 END IF;
3175 IF NOT FND_API.compatible_api_call(l_api_version, p_api_version,
3176 l_api_name, g_pkg_name)
3177 THEN
3178 RAISE FND_API.g_exc_unexpected_error;
3179 END IF;
3180 x_return_status := FND_API.g_ret_sts_success;
3181 -- End standard API initialization
3182
3183 IF (g_debug_stmt) THEN
3184 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
3185 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix || l_api_name ||
3186 '.invoked', 'Type: ' || NVL(p_doc_type,'null') ||
3187 ', ID: ' || NVL(TO_CHAR(p_doc_id),'null'));
3188 END IF;
3189 END IF;
3190
3191 IF (p_doc_type IN ('PO','PA')) THEN
3192
3193 IF (p_doc_line_id IS NOT NULL) THEN
3194
3195 UPDATE po_lines pol
3196 SET pol.note_to_vendor = p_note_to_vendor
3197 WHERE pol.po_line_id = p_doc_line_id AND
3198 pol.po_header_id = p_doc_id;
3199
3200 --<Bug 2843843 mbhargav START>
3201 -- Return error if no update was made.
3202 IF SQL%NOTFOUND THEN
3203 FND_MESSAGE.set_name('PO','PO_INVALID_DOC_IDS');
3204 IF (g_debug_stmt) THEN
3205 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR) THEN
3206 FND_LOG.message(FND_LOG.level_error, g_module_prefix||l_api_name||
3207 '.invalid_document', FALSE);
3208 END IF;
3209 END IF;
3210 FND_MSG_PUB.add;
3211 RAISE FND_API.g_exc_error;
3212 END IF;
3213 --<Bug 2843843 mbhargav END>
3214
3215 /* Bug 2781710: We should update the note_to_vendor column
3216 in the archive table also. */
3217
3218 UPDATE po_lines_archive pla
3219 SET pla.note_to_vendor = p_note_to_vendor
3220 WHERE pla.po_line_id = p_doc_line_id AND
3221 pla.po_header_id = p_doc_id AND
3222 pla.revision_num = (SELECT poh.revision_num
3223 FROM po_headers poh
3224 WHERE poh.po_header_id = p_doc_id);
3225
3226 ELSE
3227
3228 UPDATE po_headers poh
3229 SET poh.note_to_vendor = p_note_to_vendor
3230 WHERE poh.po_header_id = p_doc_id;
3231
3232 --<Bug 2843843 mbhargav START>
3233 -- Return error if no update was made.
3234 IF SQL%NOTFOUND THEN
3235 FND_MESSAGE.set_name('PO','PO_INVALID_DOC_IDS');
3236 IF (g_debug_stmt) THEN
3237 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR) THEN
3238 FND_LOG.message(FND_LOG.level_error, g_module_prefix||l_api_name||
3239 '.invalid_document', FALSE);
3240 END IF;
3241 END IF;
3242 FND_MSG_PUB.add;
3243 RAISE FND_API.g_exc_error;
3244 END IF;
3245 --<Bug 2843843 mbhargav END>
3246
3247 /* Bug 2781710: We should update the note_to_vendor column
3248 in the archive table also. */
3249
3250 UPDATE po_headers_archive pha
3251 SET pha.note_to_vendor = p_note_to_vendor
3252 WHERE pha.po_header_id = p_doc_id AND
3253 pha.revision_num = (SELECT poh.revision_num
3254 FROM po_headers poh
3255 WHERE poh.po_header_id = p_doc_id);
3256 END IF; --<if p_doc_line_id ...>
3257
3258 ELSIF (p_doc_type = 'RELEASE') THEN
3259
3260 UPDATE po_releases por
3261 SET por.note_to_vendor = p_note_to_vendor
3262 WHERE por.po_release_id = p_doc_id;
3263
3264 --<Bug 2843843 mbhargav START>
3265 -- Return error if no update was made.
3266 IF SQL%NOTFOUND THEN
3267 FND_MESSAGE.set_name('PO','PO_INVALID_DOC_IDS');
3268 IF (g_debug_stmt) THEN
3269 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR) THEN
3270 FND_LOG.message(FND_LOG.level_error, g_module_prefix||l_api_name||
3271 '.invalid_document', FALSE);
3272 END IF;
3273 END IF;
3274 FND_MSG_PUB.add;
3275 RAISE FND_API.g_exc_error;
3276 END IF;
3277 --<Bug 2843843 mbhargav END>
3278
3279 /* Bug 2781710: We should update the note_to_vendor column
3280 in the archive table also. */
3281
3282 UPDATE po_releases_archive pra
3283 SET pra.note_to_vendor = p_note_to_vendor
3284 WHERE pra.po_release_id = p_doc_id AND
3285 pra.revision_num = (SELECT por.revision_num
3286 FROM po_releases por
3287 WHERE por.po_release_id = p_doc_id);
3288 ELSE
3289 -- This document type is not supported
3290 FND_MESSAGE.set_name('PO','PO_INVALID_DOC_TYPE');
3291 FND_MESSAGE.set_token('TYPE',p_doc_type);
3292 IF (g_debug_stmt) THEN
3293 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR) THEN
3294 FND_LOG.message(FND_LOG.level_error, g_module_prefix||l_api_name||
3295 '.invalid_doc_type', FALSE);
3296 END IF;
3297 END IF;
3298 FND_MSG_PUB.add;
3299 RAISE FND_API.g_exc_error;
3300
3301 END IF; --<if p_doc_type ...>
3302
3303 -- Standard API check of p_commit
3304 IF FND_API.to_boolean(p_commit) THEN
3305 COMMIT WORK;
3306 END IF;
3307 EXCEPTION
3308 WHEN FND_API.g_exc_error THEN
3309 ROLLBACK TO update_note_to_vendor_PVT;
3310 x_return_status := FND_API.g_ret_sts_error;
3311 WHEN FND_API.g_exc_unexpected_error THEN
3312 ROLLBACK TO update_note_to_vendor_PVT;
3313 x_return_status := FND_API.g_ret_sts_unexp_error;
3314 WHEN OTHERS THEN
3315 ROLLBACK TO update_note_to_vendor_PVT;
3316 x_return_status := FND_API.g_ret_sts_unexp_error;
3317 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
3318 IF (g_debug_unexp) THEN
3319 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
3320 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
3321 l_api_name || '.others_exception', 'Exception');
3322 END IF;
3323 END IF;
3324 END update_note_to_vendor;
3325
3326
3327 /**
3328 * Public Function: pass_security_check
3329 * Requires: API message list has been initialized if p_init_msg_list is false.
3330 * Modifies: API message list
3331 * Effects: Checks if p_agent_id has the access and security clearance to modify
3332 * or act upon this document. Appends to API message list on error.
3333 * Returns:
3334 * TRUE - if the check passes
3335 * FALSE - otherwise, or if an error occurs
3336 * x_return_status - FND_API.G_RET_STS_SUCCESS if no error occurs
3337 * FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
3338 */
3339 FUNCTION pass_security_check
3340 (p_api_version IN NUMBER,
3341 p_init_msg_list IN VARCHAR2,
3342 x_return_status OUT NOCOPY VARCHAR2,
3343 p_doc_type IN PO_DOCUMENT_TYPES.document_type_code%TYPE,
3344 p_doc_subtype IN PO_DOCUMENT_TYPES.document_subtype%TYPE,
3345 p_doc_id IN NUMBER,
3346 p_agent_id IN PO_HEADERS.agent_id%TYPE)
3347 RETURN BOOLEAN
3348 IS
3349
3350 l_api_name CONSTANT VARCHAR2(30) := 'pass_security_check';
3351 l_api_version CONSTANT NUMBER := 1.0;
3352 l_doc_agent_id PO_HEADERS.agent_id%TYPE;
3353 l_return_value BOOLEAN;
3354
3355 BEGIN
3356 -- Start standard API initialization
3357 IF FND_API.to_boolean(p_init_msg_list) THEN
3358 FND_MSG_PUB.initialize;
3359 END IF;
3360 IF NOT FND_API.compatible_api_call(l_api_version, p_api_version,
3361 l_api_name, g_pkg_name)
3362 THEN
3363 RAISE FND_API.g_exc_unexpected_error;
3364 END IF;
3365 x_return_status := FND_API.g_ret_sts_success;
3366 -- End standard API initialization
3367
3368 IF (g_debug_stmt) THEN
3369 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
3370 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix || l_api_name ||
3371 '.invoked', 'Type: ' || NVL(p_doc_type,'null') ||
3372 ', ID: ' || NVL(TO_CHAR(p_doc_id),'null') ||
3373 ', Agent: ' || NVL(TO_CHAR(p_agent_id),'null'));
3374 END IF;
3375 END IF;
3376
3377 -- Find the original agent or preparer of the document depending on its type
3378 IF (p_doc_type = 'REQUISITION') THEN
3379 SELECT porh.preparer_id
3380 INTO l_doc_agent_id
3381 FROM po_requisition_headers porh
3382 WHERE porh.requisition_header_id = p_doc_id;
3383 ELSIF (p_doc_type = 'RELEASE') THEN
3384 SELECT por.agent_id
3385 INTO l_doc_agent_id
3386 FROM po_releases por
3387 WHERE por.po_release_id = p_doc_id;
3388 ELSE
3389 SELECT poh.agent_id
3390 INTO l_doc_agent_id
3391 FROM po_headers poh
3392 WHERE poh.po_header_id = p_doc_id;
3393 END IF; -- if p_doc_type = ...
3394
3395 -- Check if this agent has security clearance for this document
3396 RETURN PO_REQS_CONTROL_SV.val_doc_security(x_doc_agent_id => l_doc_agent_id,
3397 x_agent_id => p_agent_id,
3398 x_doc_type => p_doc_type,
3399 x_doc_subtype => p_doc_subtype);
3400 EXCEPTION
3401 WHEN FND_API.g_exc_unexpected_error THEN
3402 x_return_status := FND_API.g_ret_sts_unexp_error;
3403 RETURN FALSE;
3404 WHEN OTHERS THEN
3405 x_return_status := FND_API.g_ret_sts_unexp_error;
3406 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
3407 IF (g_debug_unexp) THEN
3408 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
3409 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
3410 l_api_name || '.others_exception', 'Exception');
3411 END IF;
3412 END IF;
3413 RETURN FALSE;
3414 END pass_security_check;
3415
3416
3417 /**
3418 * Public Function: has_shipments
3419 * Requires: API message list has been initialized if p_init_msg_list is false.
3420 * Modifies: API message list
3421 * Effects: Checks if the document has shipments that are not cancelled
3422 * or finally closed. Appends to API message list on error.
3423 * Returns:
3424 * TRUE - if the check passes
3425 * FALSE - otherwise, or if an error occurs
3426 * x_return_status - FND_API.G_RET_STS_SUCCESS if no error occurs
3427 * FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
3428 */
3429 FUNCTION has_shipments
3430 (p_api_version IN NUMBER,
3431 p_init_msg_list IN VARCHAR2,
3432 x_return_status OUT NOCOPY VARCHAR2,
3433 p_doc_type IN PO_DOCUMENT_TYPES.document_type_code%TYPE,
3434 p_doc_id IN NUMBER)
3435 RETURN BOOLEAN
3436 IS
3437
3438 CURSOR l_has_ship_csr IS
3439 -- SQL What: Query PO_LINE_LOCATIONS for shipments
3440 -- SQL Why: Check if this document has any shipments
3441 --<Complex Work R12>: include PREPAYMENT line locations
3442 SELECT 'Has shipments'
3443 FROM po_line_locations poll
3444 WHERE poll.po_header_id = p_doc_id AND
3445 poll.shipment_type IN
3446 ('STANDARD', 'PLANNED', 'BLANKET', 'PREPAYMENT') AND
3447 NVL(poll.cancel_flag, 'N') = 'N' AND
3448 NVL(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED';
3449 CURSOR l_has_rel_ship_csr IS
3450 -- SQL What: Query PO_LINE_LOCATIONS for shipments
3451 -- SQL Why: Check if this release has any shipments
3452 SELECT 'Has shipments'
3453 FROM po_line_locations poll
3454 WHERE poll.po_release_id = p_doc_id AND
3455 poll.shipment_type IN ('STANDARD', 'PLANNED', 'BLANKET') AND
3456 NVL(poll.cancel_flag, 'N') = 'N' AND
3457 NVL(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED';
3458
3459 l_api_name CONSTANT VARCHAR2(30) := 'has_shipments';
3460 l_api_version CONSTANT NUMBER := 1.0;
3461 l_has_shipments VARCHAR2(15);
3462 l_return_value BOOLEAN;
3463
3464 BEGIN
3465 -- Start standard API initialization
3466 IF FND_API.to_boolean(p_init_msg_list) THEN
3467 FND_MSG_PUB.initialize;
3468 END IF;
3469 IF NOT FND_API.compatible_api_call(l_api_version, p_api_version,
3470 l_api_name, g_pkg_name)
3471 THEN
3472 RAISE FND_API.g_exc_unexpected_error;
3473 END IF;
3474 x_return_status := FND_API.g_ret_sts_success;
3475 -- End standard API initialization
3476
3477 IF (g_debug_stmt) THEN
3478 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
3479 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix || l_api_name ||
3480 '.invoked', 'Type: ' || NVL(p_doc_type,'null') ||
3481 ', ID: ' || NVL(TO_CHAR(p_doc_id),'null'));
3482 END IF;
3483 END IF;
3484
3485 -- Choose cursor depending upon document type
3486 IF (p_doc_type = 'RELEASE') THEN
3487 OPEN l_has_rel_ship_csr;
3488 FETCH l_has_rel_ship_csr INTO l_has_shipments;
3489
3490 -- if the cursor fetched a row, then this release has shipments
3491 l_return_value := l_has_rel_ship_csr%FOUND;
3492 CLOSE l_has_rel_ship_csr;
3493 ELSE
3494 OPEN l_has_ship_csr;
3495 FETCH l_has_ship_csr INTO l_has_shipments;
3496
3497 -- if the cursor fetched a row, then this document has shipments
3498 l_return_value := l_has_ship_csr%FOUND;
3499 CLOSE l_has_ship_csr;
3500 END IF; -- if p_doc_type = RELEASE
3501
3502 RETURN l_return_value;
3503
3504 EXCEPTION
3505 WHEN FND_API.g_exc_unexpected_error THEN
3506 x_return_status := FND_API.g_ret_sts_unexp_error;
3507 IF l_has_ship_csr%ISOPEN THEN
3508 CLOSE l_has_ship_csr;
3509 END IF;
3510 IF l_has_rel_ship_csr%ISOPEN THEN
3511 CLOSE l_has_rel_ship_csr;
3512 END IF;
3513 RETURN FALSE;
3514 WHEN OTHERS THEN
3515 x_return_status := FND_API.g_ret_sts_unexp_error;
3516 IF l_has_ship_csr%ISOPEN THEN
3517 CLOSE l_has_ship_csr;
3518 END IF;
3519 IF l_has_rel_ship_csr%ISOPEN THEN
3520 CLOSE l_has_rel_ship_csr;
3521 END IF;
3522 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
3523 IF (g_debug_unexp) THEN
3524 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
3525 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
3526 l_api_name || '.others_exception', 'Exception');
3527 END IF;
3528 END IF;
3529 RETURN FALSE;
3530 END has_shipments;
3531
3532 /**
3533 * Public Function: has_unencumbered_shipments
3534 * Requires: API message list has been initialized if p_init_msg_list is false.
3535 * Modifies: API message list
3536 * Effects: Checks if the document has any unencumbered shipments that are not
3537 * cancelled or finally closed. Appends to API message list on error.
3538 * Returns:
3539 * TRUE - if the check passes
3540 * FALSE - otherwise, or if an error occurred
3541 * x_return_status - FND_API.G_RET_STS_SUCCESS if no error occurs
3542 * FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
3543 */
3544 FUNCTION has_unencumbered_shipments
3545 (p_api_version IN NUMBER,
3546 p_init_msg_list IN VARCHAR2,
3547 x_return_status OUT NOCOPY VARCHAR2,
3548 p_doc_type IN PO_DOCUMENT_TYPES.document_type_code%TYPE,
3549 p_doc_id IN NUMBER)
3550 RETURN BOOLEAN
3551 IS
3552
3553 l_api_name CONSTANT VARCHAR2(30) := 'has_unencumbered_shipments';
3554 l_api_version CONSTANT NUMBER := 1.0;
3555 l_return_value BOOLEAN;
3556
3557 --<Encumbrance FPJ>
3558 l_fully_reserved_flag VARCHAR2(1);
3559
3560 BEGIN
3561 -- Start standard API initialization
3562 IF FND_API.to_boolean(p_init_msg_list) THEN
3563 FND_MSG_PUB.initialize;
3564 END IF;
3565 IF NOT FND_API.compatible_api_call(l_api_version, p_api_version,
3566 l_api_name, g_pkg_name)
3567 THEN
3568 RAISE FND_API.g_exc_unexpected_error;
3569 END IF;
3570 x_return_status := FND_API.g_ret_sts_success;
3571 -- End standard API initialization
3572
3573 IF (g_debug_stmt) THEN
3574 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
3575 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix || l_api_name ||
3576 '.invoked', 'Type: ' || NVL(p_doc_type,'null') ||
3577 ', ID: ' || NVL(TO_CHAR(p_doc_id),'null'));
3578 END IF;
3579 END IF;
3580
3581 --<Encumbrance FPJ START>
3582 PO_CORE_S.is_fully_reserved(
3583 p_doc_type => p_doc_type
3584 , p_doc_level => PO_CORE_S.g_doc_level_header
3585 , p_doc_level_id => p_doc_id
3586 , x_fully_reserved_flag => l_fully_reserved_flag
3587 );
3588
3589 IF (l_fully_reserved_flag = 'N') THEN
3590 l_return_value := TRUE;
3591 ELSE
3592 l_return_value := FALSE;
3593 END IF;
3594 --<Encumbrance FPJ END>
3595
3596 RETURN l_return_value;
3597
3598 EXCEPTION
3599 WHEN FND_API.g_exc_unexpected_error THEN
3600 x_return_status := FND_API.g_ret_sts_unexp_error;
3601 RETURN FALSE;
3602 WHEN OTHERS THEN
3603 x_return_status := FND_API.g_ret_sts_unexp_error;
3604 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
3605 IF (g_debug_unexp) THEN
3606 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
3607 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
3608 l_api_name || '.others_exception', 'Exception');
3609 END IF;
3610 END IF;
3611 RETURN FALSE;
3612 END has_unencumbered_shipments;
3613
3614 /**
3615 * Public Function: in_open_gl_period
3616 * Requires: API message list has been initialized if p_init_msg_list is false.
3617 * Modifies: API message list
3618 * Effects: Checks if p_date lies within a valid open GL period. Appends to API
3619 * message list on error.
3620 * Returns:
3621 * TRUE - if the check passes
3622 * FALSE - otherwise, or if an error occurred
3623 * x_return_status - FND_API.G_RET_STS_SUCCESS if no error occurs
3624 * FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
3625 */
3626 FUNCTION in_open_gl_period
3627 (p_api_version IN NUMBER,
3628 p_init_msg_list IN VARCHAR2,
3629 x_return_status OUT NOCOPY VARCHAR2,
3630 p_date IN DATE)
3631 RETURN BOOLEAN
3632 IS
3633
3634 -- bug 5498063 <R12 GL PERIOD VALIDATION>
3635 l_validate_gl_period VARCHAR2(1);
3636
3637 CURSOR l_gl_period_csr IS
3638 -- SQL What: Querying GL_PERIOD_STATUSES and FINANCIALS_SYSTEM_PARAMETERS
3639 -- SQL Why: Check if p_date is in an open GL period
3640 -- SQL Join: set_of_books_id, period_name
3641 SELECT 'In open GL period'
3642 FROM gl_period_statuses gl_ps,
3643 gl_period_statuses po_ps,
3644 financials_system_parameters fsp
3645 WHERE gl_ps.application_id = 101 AND
3646 gl_ps.set_of_books_id = fsp.set_of_books_id AND
3647 -- bug 5498063 <R12 GL PERIOD VALIDATION>
3648 (( (l_validate_gl_period = 'Y' OR l_validate_gl_period = 'R')--Bug15874392
3649 and GL_PS.closing_status IN ('O', 'F'))
3650 OR
3651 (l_validate_gl_period = 'N')) AND
3652 -- gl_ps.closing_status IN ('O','F') AND
3653 -- bug 5498063 <R12 GL PERIOD VALIDATION>
3654 gl_ps.period_name = po_ps.period_name AND
3655 gl_ps.adjustment_period_flag = 'N' AND
3656 (TRUNC(p_date) BETWEEN
3657 TRUNC(gl_ps.start_date) AND TRUNC(gl_ps.end_date)) AND
3658 po_ps.application_id = 201 AND
3659 po_ps.closing_status = 'O' AND
3660 po_ps.adjustment_period_flag = 'N' AND
3661 po_ps.set_of_books_id = fsp.set_of_books_id;
3662
3663 l_api_name CONSTANT VARCHAR2(30) := 'in_open_gl_period';
3664 l_api_version CONSTANT NUMBER := 1.0;
3665 l_row_exists VARCHAR2(20);
3666 l_return_value BOOLEAN;
3667
3668 BEGIN
3669 -- Start standard API initialization
3670 IF FND_API.to_boolean(p_init_msg_list) THEN
3671 FND_MSG_PUB.initialize;
3672 END IF;
3673 IF NOT FND_API.compatible_api_call(l_api_version, p_api_version,
3674 l_api_name, g_pkg_name)
3675 THEN
3676 RAISE FND_API.g_exc_unexpected_error;
3677 END IF;
3678 x_return_status := FND_API.g_ret_sts_success;
3679 -- End standard API initialization
3680
3681 IF (g_debug_stmt) THEN
3682 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
3683 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix || l_api_name ||
3684 '.invoked', 'Date: ' ||
3685 NVL(TO_CHAR(p_date,'DD-MON-RR'),'null'));
3686 END IF;
3687 END IF;
3688
3689 -- bug 5498063 <R12 GL PERIOD VALIDATION>
3690 l_validate_gl_period := nvl(FND_PROFILE.VALUE('PO_VALIDATE_GL_PERIOD'),'Y');
3691
3692 OPEN l_gl_period_csr;
3693 FETCH l_gl_period_csr INTO l_row_exists;
3694
3695 -- Date is in an open GL period if a row was fetched
3696 l_return_value := l_gl_period_csr%FOUND;
3697
3698 CLOSE l_gl_period_csr;
3699
3700 RETURN l_return_value;
3701
3702 EXCEPTION
3703 WHEN FND_API.g_exc_unexpected_error THEN
3704 x_return_status := FND_API.g_ret_sts_unexp_error;
3705 IF l_gl_period_csr%ISOPEN THEN
3706 CLOSE l_gl_period_csr;
3707 END IF;
3708 RETURN FALSE;
3709 WHEN OTHERS THEN
3710 x_return_status := FND_API.g_ret_sts_unexp_error;
3711 IF l_gl_period_csr%ISOPEN THEN
3712 CLOSE l_gl_period_csr;
3713 END IF;
3714 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
3715 IF (g_debug_unexp) THEN
3716 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
3717 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
3718 l_api_name || '.others_exception', 'Exception');
3719 END IF;
3720 END IF;
3721 RETURN FALSE;
3722 END in_open_gl_period;
3723
3724
3725 /**
3726 * Public Procedure: add_online_report_msgs
3727 * Requires: API message list has been initialized if p_init_msg_list is false.
3728 * Modifies: API message list
3729 * Effects: Retrieves all text lines from PO_ONLINE_REPORT_TEXT for
3730 * p_online_report_id, and appends each one to the API message list. Does not
3731 * append to API message list upon expected error, just unexpected error.
3732 * Returns:
3733 * x_return_status - FND_API.G_RET_STS_SUCCESS if all messages are appended
3734 * FND_API.G_RET_STS_ERROR if an error occurs
3735 * FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
3736 */
3737 PROCEDURE add_online_report_msgs
3738 (p_api_version IN NUMBER,
3739 p_init_msg_list IN VARCHAR2,
3740 x_return_status OUT NOCOPY VARCHAR2,
3741 p_online_report_id IN NUMBER)
3742 IS
3743
3744 l_api_name CONSTANT VARCHAR2(30) := 'add_online_report_msgs';
3745 l_api_version CONSTANT NUMBER := 1.0;
3746 CURSOR l_get_online_report_csr IS
3747 SELECT poort.text_line
3748 FROM po_online_report_text poort
3749 WHERE poort.online_report_id = p_online_report_id;
3750 TYPE text_line_tbl_type IS TABLE OF PO_ONLINE_REPORT_TEXT.text_line%TYPE
3751 INDEX BY BINARY_INTEGER;
3752 l_text_line_tbl text_line_tbl_type;
3753
3754 BEGIN
3755 -- Start standard API initialization
3756 IF FND_API.to_boolean(p_init_msg_list) THEN
3757 FND_MSG_PUB.initialize;
3758 END IF;
3759 IF NOT FND_API.compatible_api_call(l_api_version, p_api_version,
3760 l_api_name, g_pkg_name)
3761 THEN
3762 RAISE FND_API.g_exc_unexpected_error;
3763 END IF;
3764 x_return_status := FND_API.g_ret_sts_success;
3765 -- End standard API initialization
3766
3767 IF (g_debug_stmt) THEN
3768 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
3769 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix || l_api_name ||
3770 '.invoked', 'Online report ID: ' ||
3771 NVL(TO_CHAR(p_online_report_id),'null'));
3772 END IF;
3773 END IF;
3774
3775 OPEN l_get_online_report_csr;
3776 LOOP
3777 FETCH l_get_online_report_csr
3778 BULK COLLECT INTO l_text_line_tbl LIMIT 1000;
3779 FOR i IN 1..l_text_line_tbl.count LOOP
3780 -- The text_line column contains translated messages
3781 FND_MESSAGE.set_name('PO','PO_CUSTOM_MSG');
3782 FND_MESSAGE.set_token('TRANSLATED_TOKEN', l_text_line_tbl(i));
3783 IF (g_debug_stmt) THEN
3784 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR) THEN
3785 FND_LOG.message(FND_LOG.level_error, g_module_prefix ||
3786 l_api_name || '.msg', FALSE);
3787 END IF;
3788 END IF;
3789 FND_MSG_PUB.add;
3790 END LOOP; --<for loop>
3791
3792 EXIT WHEN l_get_online_report_csr%NOTFOUND;
3793 END LOOP;
3794
3795 IF (l_get_online_report_csr%ROWCOUNT = 0) THEN
3796 RAISE FND_API.g_exc_error;
3797 END IF;
3798 EXCEPTION
3799 WHEN FND_API.g_exc_error THEN
3800 x_return_status := FND_API.g_ret_sts_error;
3801 WHEN FND_API.g_exc_unexpected_error THEN
3802 x_return_status := FND_API.g_ret_sts_unexp_error;
3803 WHEN OTHERS THEN
3804 x_return_status := FND_API.g_ret_sts_unexp_error;
3805 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
3806 IF (g_debug_unexp) THEN
3807 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
3808 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
3809 l_api_name || '.others_exception', 'Exception');
3810 END IF;
3811 END IF;
3812 END add_online_report_msgs;
3813 --<HTML Agreements R12 Start>
3814 ----------------------------------------------------------------------------
3815 --Start of Comments
3816 --Name: Val_Cancel_FinalClose_Action
3817 --Pre-reqs:
3818 -- None.
3819 --Modifies:
3820 -- FND_MSG_PUB.
3821 --Locks:
3822 -- None.
3823 --Function:
3824 -- This procedure would be responsible for validating the control action
3825 -- for the document. This code would be called when we cancel or
3826 -- finally close from HTML
3827 --Parameters:
3828 --IN:
3829 --p_control_action
3830 -- Document Control Action being executed
3831 --p_doc_level
3832 -- Document Level at which control Action was taken
3833 --p_doc_header_id
3834 -- Document Header Id
3835 --p_doc_line_id
3836 -- Document Line Id
3837 --p_doc_line_loc_id
3838 -- Document Line Location Id
3839 --p_doc_type
3840 -- Document Type
3841 --p_doc_subtype
3842 -- Document Sub Type (type_lookup_code)
3843 --OUT:
3844 --x_return_status
3845 -- Return Status of API .
3846 --Testing:
3847 -- Refer the Unit Test Plan for 'HTML Agreements R12'
3848 --End of Comments
3849 ----------------------------------------------------------------------------
3850 procedure Val_Cancel_FinalClose_Action( p_control_action IN VARCHAR2
3851 ,p_doc_level IN VARCHAR2
3852 ,p_doc_header_id IN NUMBER
3853 ,p_doc_line_id IN NUMBER
3854 ,p_doc_line_loc_id IN NUMBER
3855 ,p_doc_type IN VARCHAR2
3856 ,p_doc_subtype IN VARCHAR2
3857 ,x_return_status OUT NOCOPY VARCHAR2) IS
3858 l_timecard_exists BOOLEAN;
3859 l_otl_field_name VARCHAR2(20);
3860 l_otl_field_value NUMBER;
3861 l_current_entity_changed VARCHAR2(1);
3862 l_dummy PO_LINE_LOCATIONS_ALL.line_location_id%type := 0;
3863 d_pos NUMBER;
3864 l_api_name CONSTANT VARCHAR2(30) := 'Val_Cancel_FinalClose_Action';
3865 d_module CONSTANT VARCHAR2(70) := 'po.plsql.PO_Document_Control_PVT.Val_Cancel_FinalClose_Action' ;
3866 BEGIN
3867 IF (PO_LOG.d_proc) THEN
3868 PO_LOG.proc_begin(d_module);
3869 PO_LOG.proc_begin(d_module,'p_control_action',p_control_action);
3870 PO_LOG.proc_begin(d_module,'p_doc_level',p_doc_level);
3871 PO_LOG.proc_begin(d_module,'p_doc_header_id',p_doc_header_id);
3872 PO_LOG.proc_begin(d_module,'p_doc_line_id',p_doc_line_id);
3873 PO_LOG.proc_begin(d_module,'p_doc_line_loc_id',p_doc_line_loc_id);
3874 PO_LOG.proc_begin(d_module,'p_doc_type',p_doc_type);
3875 PO_LOG.proc_begin(d_module,'p_doc_subtype',p_doc_subtype);
3876 END IF;
3877 d_pos := 10;
3878 --Initialising the variables to default value
3879 x_return_status := FND_API.g_ret_sts_success;
3880 l_current_entity_changed := 'N';
3881
3882 -- <Bug 14254141 :Cancel Refactoring Project: removed the block>
3883 -- For cancel or finally close on Standard POs, call the OTL API to check
3884 -- if there are any submitted/approved timecards associated with the
3885 -- PO header/line. If so, return an error.
3886 IF (p_doc_subtype = PO_CONSTANTS_SV.STANDARD) THEN
3887 d_pos := 50;
3888 IF p_doc_level = PO_CORE_S.g_doc_level_HEADER THEN
3889 l_otl_field_name := PO_HXC_INTERFACE_PVT.field_PO_HEADER_ID;
3890 l_otl_field_value := p_doc_header_id;
3891 ELSE
3892 -- line or shipment level
3893 l_otl_field_name := PO_HXC_INTERFACE_PVT.field_PO_LINE_ID;
3894 l_otl_field_value := p_doc_line_id;
3895 END IF; --p_doc_level = 'HEADER'
3896 d_pos := 60;
3897 IF (PO_LOG.d_stmt) THEN
3898 PO_LOG.stmt(d_module,d_pos,'l_otl_field_name',l_otl_field_name);
3899 PO_LOG.stmt(d_module,d_pos,'l_otl_field_value',l_otl_field_value);
3900 END IF;
3901 PO_HXC_INTERFACE_PVT.check_timecard_exists(p_api_version => 1.0,
3902 x_return_status => x_return_status,
3903 p_field_name => l_otl_field_name,
3904 p_field_value => l_otl_field_value,
3905 p_end_date => NULL,
3906 x_timecard_exists => l_timecard_exists);
3907 d_pos := 70;
3908 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3909 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3910 END IF;
3911 d_pos := 80;
3912 IF (l_timecard_exists) THEN
3913 FND_MESSAGE.set_name('PO','PO_CONTROL_OTL_INVALID_ACTION');
3914 FND_MSG_PUB.add;
3915 IF PO_LOG.d_exc THEN
3916 PO_LOG.exc(d_module,d_pos,'Document with timecard cannot be Cancelled or Finally Closed');
3917 END IF;
3918 RAISE FND_API.G_EXC_ERROR;
3919 END IF; -- IF(l_timecard_exists) THEN
3920 END IF; --p_doc_subtype = 'STANDARD'
3921 d_pos := 90;
3922 IF (PO_LOG.d_proc) THEN
3923 PO_LOG.proc_end(d_module);
3924 END IF;
3925 EXCEPTION
3926 WHEN FND_API.g_exc_error THEN
3927 x_return_status := FND_API.g_ret_sts_error;
3928 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3929 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3930 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
3931 IF PO_LOG.d_exc THEN
3932 PO_LOG.exc(d_module,d_pos,'Unexpected Error in ' || d_module);
3933 END IF;
3934 WHEN OTHERS THEN
3935 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3936 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name||':'||d_pos);
3937 IF PO_LOG.d_exc THEN
3938 PO_LOG.exc(d_module,d_pos,'Unhandled Exception in' || d_module);
3939 END IF;
3940 END Val_Cancel_FinalClose_Action;
3941
3942 ------------------------------------------------------------------------
3943 --Start of Comments
3944 --Name: do_control_action
3945 --Pre-reqs:
3946 -- None.
3947 --Modifies:
3948 -- None.
3949 --Locks:
3950 -- None.
3951 --Function:
3952 -- This procedure is responsible for executing the control action and
3953 -- formatting the errors encountered show that user can be displayed the
3954 -- error message.
3955 --IN:
3956 --p_mode
3957 -- HTML mode EDIT/UPDATE/SUMMARY
3958 --p_control_action
3959 -- Document Control Action being executed
3960 --p_doc_level
3961 -- Document Level at which control Action was taken
3962 --p_doc_header_id
3963 -- Document Header Id
3964 --p_doc_line_id
3965 -- Document Line Id
3966 --p_doc_line_loc_id
3967 -- Document Line Location Id
3968 --p_doc_type
3969 -- Document Type
3970 --p_doc_sub_type
3971 -- Document Sub Type (type_lookup_code)
3972 --p_action_date
3973 -- GL Date value provided by the user
3974 --p_use_gldate
3975 -- Value of the Use GL Date to Unreserve Checkbox
3976 --p_reason
3977 -- Possible Reason for excuting the control Action
3978 --p_note_to_vendor
3979 -- Not for the Supplier in case a document is being cancelled
3980 --p_communication_method
3981 -- Communication method selected by the user {EMAIL/FAX/PRINT/EDI/XML}
3982 --p_communication_value
3983 -- Communication method value provided by the user {Email Address/ Fax Number}
3984 --p_cancel_reqs
3985 -- value of Cancel Requisitions checkbox
3986 --OUT:
3987 --x_approval_initiated
3988 -- Was approval initaited for the document?
3989 --x_cancel_req_flag_reset
3990 -- Was the cancel requisitions flag reset by the cancel code
3991 --x_return_status
3992 -- Return Status of API .
3993 --x_error_msg_tbl
3994 -- table for Error messages if any .
3995 --x_is_encumbrance_error
3996 -- whether the error (if any) was due to encumbrance - Bug 5000165
3997 --x_online_report_id
3998 -- determines the online report id generated during an encumbrance transaction - Bug 5055417
3999 --Testing:
4000 -- Refer the Unit Test Plan for 'HTML Agreements R12'
4001 --End of Comments
4002 ----------------------------------------------------------------------------
4003 procedure do_control_action( p_mode IN VARCHAR2
4004 ,p_control_action IN VARCHAR2
4005 ,p_doc_level IN VARCHAR2
4006 ,p_doc_header_id IN NUMBER
4007 ,p_doc_line_id IN NUMBER
4008 ,p_doc_line_loc_id IN NUMBER
4009 ,p_doc_type IN VARCHAR2
4010 ,p_doc_subtype IN VARCHAR2
4011 ,p_action_date IN DATE
4012 ,p_use_gldate IN VARCHAR2
4013 ,p_reason IN VARCHAR2
4014 ,p_note_to_vendor IN VARCHAR2
4015 ,p_communication_method IN VARCHAR2
4016 ,p_communication_value IN VARCHAR2
4017 ,p_cancel_reqs IN VARCHAR2
4018 ,x_return_status OUT NOCOPY VARCHAR2
4019 ,x_approval_initiated OUT NOCOPY VARCHAR2
4020 ,x_cancel_req_flag_reset OUT NOCOPY VARCHAR2
4021 ,x_is_encumbrance_error OUT NOCOPY VARCHAR2
4022 ,x_online_report_id OUT NOCOPY NUMBER--bug#5055417
4023 )
4024 IS
4025 l_doc_subtype PO_LINE_LOCATIONS_ALL.shipment_type%type;
4026 l_conterms_exist_flag PO_HEADERS_ALL.conterms_exist_flag%TYPE;
4027 l_document_start_date PO_HEADERS_ALL.start_date%TYPE;
4028 l_document_end_date PO_HEADERS_ALL.end_date%TYPE;
4029 l_document_version PO_HEADERS_ALL.revision_num%TYPE;
4030 l_cancel_flag PO_HEADERS_ALL.cancel_flag%TYPE;
4031 l_print_flag VARCHAR2(1);
4032 l_event_code VARCHAR2(30);
4033 SUBTYPE busdocdates_tbl_type IS
4034 okc_manage_deliverables_grp.busdocdates_tbl_type;
4035 l_busdocdates_tbl busdocdates_tbl_type;
4036 l_exc_msg VARCHAR2(2000);
4037 l_msg_count NUMBER;
4038 l_msg_data VARCHAR2(2000);
4039 l_online_report_id PO_ONLINE_REPORT_TEXT.online_report_id%type;
4040 l_return_code VARCHAR2(25);
4041 l_control_action_disp_name PO_LOOKUP_CODES.displayed_field%type;
4042 d_pos NUMBER;
4043 l_api_name CONSTANT VARCHAR2(30) := 'do_control_action';
4044 d_module CONSTANT VARCHAR2(70) := 'po.plsql.PO_Document_Control_PVT.do_control_action';
4045 BEGIN
4046 IF (PO_LOG.d_proc) THEN
4047 PO_LOG.proc_begin(d_module);
4048 PO_LOG.proc_begin(d_module,'p_mode',p_mode);
4049 PO_LOG.proc_begin(d_module,'p_control_action',p_control_action);
4050 PO_LOG.proc_begin(d_module,'p_doc_level',p_doc_level);
4051 PO_LOG.proc_begin(d_module,'p_doc_header_id',p_doc_header_id);
4052 PO_LOG.proc_begin(d_module,'p_doc_line_id',p_doc_line_id);
4053 PO_LOG.proc_begin(d_module,'p_doc_line_loc_id',p_doc_line_loc_id);
4054 PO_LOG.proc_begin(d_module,'p_doc_type',p_doc_type);
4055 PO_LOG.proc_begin(d_module,'p_doc_subtype',p_doc_subtype);
4056 PO_LOG.proc_begin(d_module,'p_action_date',p_action_date);
4057 PO_LOG.proc_begin(d_module,'p_use_gldate',p_use_gldate);
4058 PO_LOG.proc_begin(d_module,'p_reason',p_reason);
4059 PO_LOG.proc_begin(d_module,'p_note_to_vendor',p_note_to_vendor);
4060 PO_LOG.proc_begin(d_module,'p_communication_method',p_communication_method);
4061 PO_LOG.proc_begin(d_module,'p_communication_value',p_communication_value);
4062 PO_LOG.proc_begin(d_module,'p_cancel_reqs',p_cancel_reqs);
4063 END IF;
4064 d_pos := 10;
4065 --initialise the out variables to default value
4066 l_online_report_id := NULL;
4067 l_return_code := NULL;
4068 x_return_status := FND_API.g_ret_sts_success;
4069 x_approval_initiated := 'N';
4070 x_cancel_req_flag_reset := 'N';
4071 x_is_encumbrance_error := 'N'; -- Bug 5000165
4072
4073 d_pos := 15;
4074 SELECT displayed_field
4075 INTO l_control_action_disp_name
4076 FROM PO_LOOKUP_CODES
4077 WHERE lookup_type = 'CONTROL ACTIONS'
4078 AND lookup_code = p_control_action;
4079
4080 IF (PO_LOG.d_stmt) THEN
4081 PO_LOG.stmt(d_module,d_pos,'l_control_action_disp_name', l_control_action_disp_name);
4082 END IF;
4083
4084 IF p_control_action = PO_DOCUMENT_ACTION_PVT.g_doc_action_FINALLY_CLOSE THEN
4085 d_pos := 20;
4086 --When one finally close a PO, the req is NOT returned to the req pool.
4087 --Since you cannot generate a new PO from the same req, the req cannot be
4088 --considered supply, so we remove the reservation entirely.
4089
4090 PO_RESERVATION_MAINTAIN_SV.maintain_reservation(
4091 p_header_id => p_doc_header_id,
4092 p_action => 'CANCEL_PO_SUPPLY',
4093 p_recreate_demand_flag => 'N',
4094 x_return_status => x_return_status);
4095 IF (PO_LOG.d_stmt) THEN
4096 PO_LOG.stmt(d_module,d_pos,'x_return_status',x_return_status);
4097 END IF;
4098
4099 d_pos := 30;
4100 --PO_RESERVATION_MAINTAIN_SV.maintain_reservation nullifies the
4101 -- x_return_status
4102 IF(x_return_status is null) THEN
4103 x_return_status := FND_API.g_ret_sts_success;
4104 END IF;
4105 d_pos := 40;
4106 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
4107 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4108 END IF;
4109
4110 --Update cbc_accounting_date for Finally Close actions
4111 IF (IGC_CBC_PO_GRP.cbc_po_enabled_flag = 'Y') THEN
4112 d_pos := 50;
4113 IGC_CBC_PO_GRP.update_cbc_acct_date(
4114 p_api_version => 1.0
4115 ,p_init_msg_list => FND_API.G_FALSE
4116 ,p_commit => FND_API.G_FALSE
4117 ,p_validation_level => 100
4118 ,x_return_status => x_return_status
4119 ,x_msg_count => l_msg_count
4120 ,x_msg_data => l_msg_data
4121 ,p_document_id => p_doc_header_id
4122 ,p_document_type => p_doc_type
4123 ,p_document_sub_type => p_doc_subtype
4124 ,p_cbc_acct_date => p_action_date);
4125
4126 d_pos := 60;
4127 IF (PO_LOG.d_stmt) THEN
4128 PO_LOG.stmt(d_module,d_pos,'x_return_status',x_return_status);
4129 END IF;
4130 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
4131 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4132 END IF;
4133 END IF; --IGC_CBC_PO_GRP.cbc_po_enabled_flag = 'Y'
4134
4135 -- call to Contracts API to cancel deliverables only on the header level
4136 -- for finally close
4137 IF (p_doc_level = PO_CORE_S.g_doc_level_HEADER) THEN
4138 d_pos := 60;
4139 select conterms_exist_flag, start_date,
4140 end_date, decode(cancel_flag,'I',null,cancel_flag)
4141 into l_conterms_exist_flag, l_document_start_date,
4142 l_document_end_date, l_cancel_flag
4143 from po_headers_all
4144 where po_header_id = p_doc_header_id;
4145 d_pos := 70;
4146
4147 IF (PO_LOG.d_stmt) THEN
4148 PO_LOG.stmt(d_module,d_pos,'l_conterms_exist_flag',l_conterms_exist_flag);
4149 PO_LOG.stmt(d_module,d_pos,'l_document_start_date',l_document_start_date);
4150 PO_LOG.stmt(d_module,d_pos,'l_document_end_date',l_document_end_date);
4151 PO_LOG.stmt(d_module,d_pos,'l_cancel_flag',l_cancel_flag);
4152 END IF;
4153
4154 IF ((UPPER(NVL(l_conterms_exist_flag, 'N'))='Y') AND
4155 (UPPER(NVL(l_cancel_flag, 'N'))='N')) THEN
4156
4157 d_pos := 80;
4158
4159 l_event_code := 'PO_CLOSE';
4160 -- populate the records and the table with event names and dates.
4161 l_busdocdates_tbl(1).event_code := 'PO_START_DATE';
4162 l_busdocdates_tbl(1).event_date := l_document_start_date;
4163 l_busdocdates_tbl(2).event_code := 'PO_END_DATE';
4164 l_busdocdates_tbl(2).event_date := l_document_end_date;
4165
4166 d_pos := 90;
4167
4168 PO_CONTERMS_WF_PVT.cancel_deliverables (
4169 p_bus_doc_id => p_doc_header_id
4170 ,p_bus_doc_type => p_doc_type
4171 ,p_bus_doc_subtype => p_doc_subtype
4172 ,p_bus_doc_version => l_document_version
4173 ,p_event_code => l_event_code
4174 ,p_event_date => SYSDATE
4175 ,p_busdocdates_tbl => l_busdocdates_tbl
4176 ,x_return_status => x_return_status);
4177
4178 d_pos := 100;
4179 IF (PO_LOG.d_stmt) THEN
4180 PO_LOG.stmt(d_module,d_pos,'x_return_status',x_return_status);
4181 END IF;
4182 IF(x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
4183 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4184 END IF;
4185 END IF; -- conterms exist
4186 END IF;--p_doc_level = 'HEADER'
4187 END IF;--p_control_action = 'FINALLY CLOSE'
4188 d_pos := 110;
4189 IF p_control_action LIKE 'CANCEL%' THEN
4190 /* Bug 8831247 Start
4191 Assign the OUT Parameter Online_Report_Id to temp_online_report_id and assign the same to x_online_report_id */
4192
4193 PO_DOCUMENT_CONTROL_PVT.control_document(
4194 p_api_version => 1.0
4195 ,p_init_msg_list => FND_API.G_FALSE
4196 ,p_commit => FND_API.G_FALSE
4197 ,x_return_status => x_return_status
4198 ,p_doc_type => p_doc_type
4199 ,p_doc_subtype => p_doc_subtype
4200 ,p_doc_id => p_doc_header_id
4201 ,p_doc_line_id => p_doc_line_id
4202 ,p_doc_line_loc_id => p_doc_line_loc_id
4203 ,p_source => PO_DOCUMENT_CANCEL_PVT.c_HTML_CONTROL_ACTION
4204 ,p_action => PO_DOCUMENT_ACTION_PVT.g_doc_action_CANCEL
4205 ,p_action_date => p_action_date
4206 ,p_cancel_reason => p_reason
4207 ,p_cancel_reqs_flag => p_cancel_reqs
4208 ,p_print_flag => 'N'
4209 ,p_note_to_vendor => p_note_to_vendor
4210 ,p_use_gldate => p_use_gldate
4211 ,p_launch_approvals_flag => 'N'
4212 ,p_communication_method_option => p_communication_method
4213 ,p_communication_method_value => p_communication_value
4214 ,p_online_report_id => l_online_report_id
4215 );
4216
4217 /* Bug 8831247 ,Assigning the out parameter value to x_online_report_id */
4218 x_online_report_id:=l_online_report_id;
4219
4220 d_pos := 130;
4221 IF (PO_LOG.d_stmt) THEN
4222 PO_LOG.stmt(d_module,d_pos,'x_return_status',x_return_status);
4223 END IF;
4224 -- If the procedure does not complete successfully raise the
4225 -- appropriate exception
4226 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
4227 RAISE FND_API.g_exc_error;
4228 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
4229 RAISE FND_API.g_exc_unexpected_error;
4230 END IF;
4231
4232 ELSIF (p_control_action = PO_DOCUMENT_ACTION_PVT.g_doc_action_HOLD) THEN --Hold
4233 d_pos := 140;
4234 PO_DOCUMENT_ACTION_PVT.do_hold(
4235 p_document_id => p_doc_header_id
4236 ,p_document_type => p_doc_type
4237 ,p_document_subtype => p_doc_subtype
4238 ,p_reason => p_reason
4239 ,x_return_status => x_return_status
4240 ,x_return_code => l_return_code
4241 ,x_exception_msg => l_exc_msg);
4242
4243 ELSIF (p_control_action = PO_DOCUMENT_ACTION_PVT.g_doc_action_RELEASE_HOLD) THEN --Release Hold
4244 d_pos := 150;
4245 PO_DOCUMENT_ACTION_PVT.do_release_hold(
4246 p_document_id => p_doc_header_id
4247 ,p_document_type => p_doc_type
4248 ,p_document_subtype => p_doc_subtype
4249 ,p_reason => p_reason
4250 ,x_return_status => x_return_status
4251 ,x_return_code => l_return_code
4252 ,x_exception_msg => l_exc_msg);
4253
4254 ELSIF (p_control_action = PO_DOCUMENT_ACTION_PVT.g_doc_action_FREEZE) THEN --Freeze
4255 d_pos := 160;
4256 PO_DOCUMENT_ACTION_PVT.do_freeze(
4257 p_document_id => p_doc_header_id
4258 ,p_document_type => p_doc_type
4259 ,p_document_subtype => p_doc_subtype
4260 ,p_reason => p_reason
4261 ,x_return_status => x_return_status
4262 ,x_return_code => l_return_code
4263 ,x_exception_msg => l_exc_msg);
4264
4265 ELSIF (p_control_action = PO_DOCUMENT_ACTION_PVT.g_doc_action_UNFREEZE) THEN --Un Freeze
4266 d_pos := 170;
4267 PO_DOCUMENT_ACTION_PVT.do_unfreeze(
4268 p_document_id => p_doc_header_id
4269 ,p_document_type => p_doc_type
4270 ,p_document_subtype => p_doc_subtype
4271 ,p_reason => p_reason
4272 ,x_return_status => x_return_status
4273 ,x_return_code => l_return_code
4274 ,x_exception_msg => l_exc_msg);
4275
4276 ELSE -- closed-state related action
4277 d_pos := 180;
4278 IF (p_doc_level = PO_CORE_S.g_doc_level_SHIPMENT)
4279 THEN
4280 select shipment_type
4281 into l_doc_subtype
4282 from po_line_locations_all
4283 where line_location_id = p_doc_line_loc_id;
4284 ELSE
4285 l_doc_subtype := p_doc_subtype;
4286 END IF;
4287 d_pos := 190;
4288 IF (PO_LOG.d_stmt) THEN
4289 PO_LOG.stmt(d_module,d_pos,'l_doc_subtype',l_doc_subtype);
4290 END IF;
4291
4292 PO_DOCUMENT_ACTION_PVT.do_manual_close(
4293 p_action => p_control_action
4294 ,p_document_id => p_doc_header_id
4295 ,p_document_type => p_doc_type
4296 ,p_document_subtype => l_doc_subtype
4297 ,p_line_id => p_doc_line_id
4298 ,p_shipment_id => p_doc_line_loc_id
4299 ,p_reason => p_reason
4300 ,p_action_date => p_action_date
4301 ,p_calling_mode => 'PO'
4302 ,p_origin_doc_id => NULL
4303 ,p_called_from_conc => FALSE
4304 ,p_use_gl_date => p_use_gldate
4305 ,x_return_status => x_return_status
4306 ,x_return_code => l_return_code
4307 ,x_exception_msg => l_exc_msg
4308 ,x_online_report_id => l_online_report_id);
4309 END IF; -- IF p_control_action LIKE 'CANCEL%'
4310 d_pos := 200;
4311 IF (PO_LOG.d_stmt) THEN
4312 PO_LOG.stmt(d_module,d_pos,'x_return_status',x_return_status);
4313 PO_LOG.stmt(d_module,d_pos,'l_return_code',l_return_code);
4314 PO_LOG.stmt(d_module,d_pos,'l_exc_msg',l_exc_msg);
4315 PO_LOG.stmt(d_module,d_pos,'l_online_report_id',l_online_report_id);
4316 END IF;
4317
4318 IF(x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
4319 IF (l_return_code = 'STATE_FAILED') THEN
4320 d_pos := 210;
4321 -- The document state was not valid for this control action
4322 FND_MESSAGE.set_name('PO','PO_APP_APP_FAILED');
4323 FND_MSG_PUB.add;
4324 RAISE FND_API.g_exc_error;
4325 ELSIF (l_return_code = 'SUBMISSION_FAILED') THEN
4326 d_pos := 220;
4327 -- Submission check failed for final close action
4328 IF l_online_report_id IS NULL THEN
4329 FND_MESSAGE.set_name('PO','PO_CONTROL_USER_EXIT_FAILED');
4330 FND_MESSAGE.set_token('USER_EXIT', l_control_action_disp_name);
4331 FND_MESSAGE.set_token('RETURN_CODE',NVL(l_return_code, fnd_message.get_string('PO','PO_ERROR')));
4332 FND_MSG_PUB.add;
4333 ELSE
4334 d_pos := 230;
4335 --Add all the messages to the message list
4336 PO_Document_Control_PVT.add_online_report_msgs(
4337 p_api_version => 1.0
4338 ,p_init_msg_list => FND_API.G_FALSE
4339 ,x_return_status => x_return_status
4340 ,p_online_report_id => l_online_report_id);
4341 END IF; --l_online_report_id IS NULL
4342 RAISE FND_API.g_exc_error;
4343 -- Bug 5000165 START
4344 -- For Encumbrance errors, we do not need to put the messages into the
4345 -- online report table, since we will be showing the PSA Budgetary Control
4346 -- Results page.
4347 ELSIF (l_return_code in ('F', 'P', 'T')) THEN
4348 x_is_encumbrance_error := 'Y';
4349 x_online_report_id :=l_online_report_id;--bug#5055417
4350 -- Bug 5000165 END
4351 END IF;--l_return_code = 'STATE_FAILED'
4352 ELSE
4353 d_pos := 240;
4354 IF (l_exc_msg IS NOT NULL) THEN
4355 PO_LOG.exc(d_module,d_pos,l_exc_msg);
4356 END IF;
4357 RAISE FND_API.g_exc_unexpected_error;
4358 END IF;--x_return_status = FND_API.G_RET_STS_SUCCESS
4359 d_pos := 250;
4360
4361 --g_approval_initiated_flag global variable set to true after
4362 --PO_REQAPPROVAL_INIT1.start_wf_process is called
4363 IF g_approval_initiated_flag THEN
4364 x_approval_initiated := 'Y';
4365 END IF;
4366
4367 --g_cancel_flag_reset_flag is set to true after the cancel flag is
4368 --set to N by the cancel code in control_document procedure
4369 IF g_cancel_flag_reset_flag THEN
4370 x_cancel_req_flag_reset := 'Y';
4371 END IF;
4372 IF (PO_LOG.d_stmt) THEN
4373 PO_LOG.stmt(d_module,d_pos,'x_approval_initiated',x_approval_initiated);
4374 END IF;
4375 d_pos := 260;
4376 EXCEPTION
4377 WHEN FND_API.g_exc_error THEN
4378 x_return_status := FND_API.g_ret_sts_error;
4379 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4380 x_return_status := FND_API.g_ret_sts_unexp_error;
4381 IF PO_LOG.d_exc THEN
4382 PO_LOG.exc(d_module,d_pos,'Unexpected Error in ' || d_module);
4383 IF FND_MSG_PUB.count_msg = 0 THEN
4384 FND_MESSAGE.set_name('PO','PO_DOC_CONTROL_ACTION_FAILED');
4385 FND_MESSAGE.set_token('CONTROL_ACTION_NAME', l_control_action_disp_name);
4386 FND_MESSAGE.set_token('ERROR_TEXT',l_exc_msg);
4387 FND_MSG_PUB.add;
4388 END IF;
4389 END IF;
4390 WHEN OTHERS THEN
4391 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4392 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name||':'||d_pos);
4393 IF PO_LOG.d_exc THEN
4394 PO_LOG.exc(d_module,d_pos,'Unhandled Exception in' || d_module);
4395 END IF;
4396 END do_control_action;
4397 ------------------------------------------------------------------------
4398 --Start of Comments
4399 --Name: process_doc_control_action
4400 --Pre-reqs:
4401 -- None.
4402 --Modifies:
4403 -- None Directly.
4404 --Locks:
4405 -- None.
4406 --Function:
4407 -- This procedure is responsible for processing the document control actions
4408 -- That is it locks the record, check if that control action is valid for that
4409 -- document and finally updates the document according to the control action
4410 --IN:
4411 --p_mode
4412 -- HTML mode EDIT/UPDATE/SUMMARY
4413 --p_control_action
4414 -- Document Control Action being executed
4415 --p_doc_level
4416 -- Document Level at which control Action was taken
4417 --p_doc_header_id
4418 -- Document Header Id
4419 --p_doc_line_id
4420 -- Document Line Id
4421 --p_doc_line_loc_id
4422 -- Document Line Location Id
4423 --p_doc_type
4424 -- Document Type
4425 --p_doc_sub_type
4426 -- Document Sub Type (type_lookup_code)
4427 --p_action_date
4428 -- GL Date value provided by the user
4429 --p_use_gldate
4430 -- Value of the Use GL Date to Unreserve Checkbox
4431 --p_gl_date
4432 -- GL Date Entered by the User
4433 --p_po_encumbrance_flag
4434 -- PO Encumbrance is enabled for that org
4435 --p_req_encumbrance_flag
4436 -- Req Encumbrance is enabled for that org
4437 --p_reason
4438 -- Possible Reason for excuting the control Action
4439 --p_note_to_vendor
4440 -- Not for the Supplier in case a document is being cancelled
4441 --p_communication_method
4442 -- Communication method selected by the user {EMAIL/FAX/PRINT/EDI/XML}
4443 --p_communication_value
4444 -- Communication method value provided by the user {Email Address/ Fax Number}
4445 --p_cancel_reqs
4446 -- value of Cancel Requisitions checkbox
4447 --OUT:
4448 --x_approval_initiated
4449 -- Was approval initaited for the document
4450 --x_return_status
4451 -- Return Status of API .
4452 --x_error_msg_tbl
4453 -- table for Error messages if any .
4454 --x_is_encumbrance_error
4455 -- whether the error (if any) was due to encumbrance - Bug 5000165
4456 --x_online_report_id
4457 -- determines the online report id generated during an encumbrance transaction - Bug 5055417
4458 --Testing:
4459 -- Refer the Unit Test Plan for 'HTML Agreements R12'
4460 --End of Comments
4461 ----------------------------------------------------------------------------
4462 procedure process_doc_control_action( p_control_action IN VARCHAR2
4463 ,p_mode IN VARCHAR2
4464 ,p_doc_level IN VARCHAR2
4465 ,p_doc_header_id IN NUMBER
4466 ,p_doc_org_id IN NUMBER
4467 ,p_doc_line_id IN NUMBER
4468 ,p_doc_line_loc_id IN NUMBER
4469 ,p_doc_type IN VARCHAR2
4470 ,p_doc_subtype IN VARCHAR2
4471 ,p_gl_date IN DATE
4472 ,p_po_encumbrance_flag IN VARCHAR2
4473 ,p_req_encumbrance_flag IN VARCHAR2
4474 ,p_use_gldate IN VARCHAR2
4475 ,p_reason IN VARCHAR2
4476 ,p_note_to_vendor IN VARCHAR2
4477 ,p_communication_method IN VARCHAR2
4478 ,p_communication_value IN VARCHAR2
4479 ,p_cancel_reqs IN VARCHAR2
4480 ,x_return_status OUT NOCOPY VARCHAR2
4481 ,x_approval_initiated OUT NOCOPY VARCHAR2
4482 ,x_cancel_req_flag_reset OUT NOCOPY VARCHAR2
4483 ,x_error_msg_tbl OUT NOCOPY PO_TBL_VARCHAR2000
4484 ,x_is_encumbrance_error OUT NOCOPY VARCHAR2
4485 ,x_online_report_id OUT NOCOPY NUMBER --bug#5055417
4486 )
4487 IS
4488 d_pos NUMBER;
4489 l_api_name CONSTANT VARCHAR2(30) := 'process_doc_control_action';
4490 d_module CONSTANT VARCHAR2(70) := 'po.plsql.PO_Document_Control_PVT.process_doc_control_action';
4491 l_mode VARCHAR2(30);
4492 BEGIN
4493 IF (PO_LOG.d_proc) THEN
4494 PO_LOG.proc_begin(d_module,'p_mode',p_mode);
4495 PO_LOG.proc_begin(d_module,'p_control_action',p_control_action);
4496 PO_LOG.proc_begin(d_module,'p_doc_level',p_doc_level);
4497 PO_LOG.proc_begin(d_module,'p_doc_header_id',p_doc_header_id);
4498 PO_LOG.proc_begin(d_module,'p_doc_org_id',p_doc_org_id);
4499 PO_LOG.proc_begin(d_module,'p_doc_line_id',p_doc_line_id);
4500 PO_LOG.proc_begin(d_module,'p_doc_line_loc_id',p_doc_line_loc_id);
4501 PO_LOG.proc_begin(d_module,'p_doc_type',p_doc_type);
4502 PO_LOG.proc_begin(d_module,'p_doc_subtype',p_doc_subtype);
4503 PO_LOG.proc_begin(d_module,'p_gl_date',p_gl_date);
4504 PO_LOG.proc_begin(d_module,'p_use_gldate',p_use_gldate);
4505 PO_LOG.proc_begin(d_module,'p_reason',p_reason);
4506 PO_LOG.proc_begin(d_module,'p_note_to_vendor',p_note_to_vendor);
4507 PO_LOG.proc_begin(d_module,'p_communication_method',p_communication_method);
4508 PO_LOG.proc_begin(d_module,'p_communication_value',p_communication_value);
4509 PO_LOG.proc_begin(d_module,'p_cancel_reqs',p_cancel_reqs);
4510 END IF;
4511 x_return_status := FND_API.G_RET_STS_SUCCESS;
4512 l_mode := UPPER(p_mode);
4513
4514 -- Standard start of API savepoint
4515 SAVEPOINT PROCESS_DOC_CONTROL_ACTION;
4516 d_pos := 10;
4517 -- Validate the action date
4518 /* Bug 6507195 : PO CBC Integration
4519 Parameter P_SKIP_VALID_CBC_ACCT_DATE value should be FND_API.G_FALSE to Validate Acct Date
4520 */
4521 val_action_date( p_api_version => 1.0,
4522 p_init_msg_list => FND_API.G_TRUE,
4523 x_return_status => x_return_status,
4524 p_doc_type => p_doc_type,
4525 p_doc_subtype => p_doc_subtype,
4526 p_doc_id => p_doc_header_id,
4527 p_action => p_control_action,
4528 p_action_date => p_gl_date,
4529 p_cbc_enabled => IGC_CBC_PO_GRP.cbc_po_enabled_flag,
4530 p_po_encumbrance_flag => p_po_encumbrance_flag,
4531 p_req_encumbrance_flag => p_req_encumbrance_flag,
4532 p_skip_valid_cbc_acct_date => FND_API.G_FALSE); --Bug#4569120
4533 d_pos := 20;
4534 IF (PO_LOG.d_stmt) THEN
4535 PO_LOG.stmt(d_module,d_pos,'x_return_status',x_return_status);
4536 END IF;
4537 IF (x_return_status = FND_API.g_ret_sts_error) THEN
4538 RAISE FND_API.g_exc_error;
4539 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
4540 RAISE FND_API.g_exc_unexpected_error;
4541 END IF;
4542 d_pos := 30;
4543 --Do not lock the header record in case of CANCEL control Action
4544 --as it would be locked in the cancel_api
4545 IF(NOT(p_control_action LIKE 'CANCEL%' )) THEN
4546 PO_DOCUMENT_LOCK_GRP.lock_document( p_api_version => 1.0
4547 ,p_init_msg_list => FND_API.G_FALSE
4548 ,x_return_status => x_return_status
4549 ,p_document_type => p_doc_type
4550 ,p_document_id => p_doc_header_id);
4551 END IF;
4552 d_pos := 40;
4553 IF (PO_LOG.d_stmt) THEN
4554 PO_LOG.stmt(d_module,d_pos,'x_return_status',x_return_status);
4555 END IF;
4556 IF (x_return_status = FND_API.g_ret_sts_error) THEN
4557 RAISE FND_API.g_exc_error;
4558 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
4559 RAISE FND_API.g_exc_unexpected_error;
4560 END IF;
4561
4562 d_pos := 50;
4563 -- validating the Cancel or Finally Close Control Action
4564 IF(p_control_action LIKE 'CANCEL%'
4565 OR p_control_action = PO_DOCUMENT_ACTION_PVT.g_doc_action_FINALLY_CLOSE) THEN
4566
4567 val_cancel_finalclose_action( p_control_action => p_control_action
4568 ,p_doc_level => p_doc_level
4569 ,p_doc_header_id => p_doc_header_id
4570 ,p_doc_line_id => p_doc_line_id
4571 ,p_doc_line_loc_id => p_doc_line_loc_id
4572 ,p_doc_type => p_doc_type
4573 ,p_doc_subtype => p_doc_subtype
4574 ,x_return_status => x_return_status);
4575 d_pos := 60;
4576 IF (PO_LOG.d_stmt) THEN
4577 PO_LOG.stmt(d_module,d_pos,'x_return_status',x_return_status);
4578 END IF;
4579 IF (x_return_status = FND_API.g_ret_sts_error) THEN
4580 RAISE FND_API.g_exc_error;
4581 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
4582 RAISE FND_API.g_exc_unexpected_error;
4583 END IF;
4584 END IF;
4585
4586
4587 --Executing the Control Action
4588 d_pos := 70;
4589 do_control_action( p_mode => l_mode
4590 ,p_control_action => p_control_action
4591 ,p_doc_level => p_doc_level
4592 ,p_doc_header_id => p_doc_header_id
4593 ,p_doc_line_id => p_doc_line_id
4594 ,p_doc_line_loc_id => p_doc_line_loc_id
4595 ,p_doc_type => p_doc_type
4596 ,p_doc_subtype => p_doc_subtype
4597 ,p_action_date => p_gl_date
4598 ,p_use_gldate => p_use_gldate
4599 ,p_reason => p_reason
4600 ,p_note_to_vendor => p_note_to_vendor
4601 ,p_communication_method => p_communication_method
4602 ,p_communication_value => p_communication_value
4603 ,p_cancel_reqs => p_cancel_reqs
4604 ,x_return_status => x_return_status
4605 ,x_approval_initiated => x_approval_initiated
4606 ,x_cancel_req_flag_reset =>x_cancel_req_flag_reset
4607 ,x_is_encumbrance_error => x_is_encumbrance_error
4608 ,x_online_report_id => x_online_report_id);--bug#5055417
4609 d_pos := 80;
4610 IF (PO_LOG.d_stmt) THEN
4611 PO_LOG.stmt(d_module,d_pos,'x_return_status',x_return_status);
4612 PO_LOG.stmt(d_module,d_pos,'x_approval_initiated',x_approval_initiated);
4613 END IF;
4614
4615 IF (x_return_status = FND_API.g_ret_sts_error) THEN
4616 RAISE FND_API.g_exc_error;
4617 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
4618 RAISE FND_API.g_exc_unexpected_error;
4619 END IF;
4620
4621 IF (PO_LOG.d_proc) THEN
4622 PO_LOG.proc_end(d_module);
4623 END IF;
4624 EXCEPTION
4625 WHEN FND_API.g_exc_error THEN
4626 ROLLBACK TO SAVEPOINT PROCESS_DOC_CONTROL_ACTION;
4627 x_return_status := FND_API.g_ret_sts_error;
4628 x_error_msg_tbl := PO_TBL_VARCHAR2000();
4629 --Copy the messages on the list to the out parameter
4630 FOR i IN 1..FND_MSG_PUB.count_msg LOOP
4631 FND_MESSAGE.set_encoded(encoded_message => FND_MSG_PUB.get(p_msg_index => i));
4632 x_error_msg_tbl.extend;
4633 x_error_msg_tbl(i) := FND_MESSAGE.get;
4634 END LOOP;
4635 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4636 ROLLBACK TO SAVEPOINT PROCESS_DOC_CONTROL_ACTION;
4637 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4638 IF PO_LOG.d_exc THEN
4639 PO_LOG.exc(d_module,d_pos,'Unexpected Error in ' || d_module);
4640 END IF;
4641 WHEN OTHERS THEN
4642 ROLLBACK TO SAVEPOINT PROCESS_DOC_CONTROL_ACTION;
4643 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4644 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name||':'||d_pos);
4645 IF PO_LOG.d_exc THEN
4646 PO_LOG.exc(d_module,d_pos,'Unhandled Exception in' || d_module);
4647 END IF;
4648 END process_doc_control_action;
4649 -------------------------------------------------------------------------------
4650 --Start of Comments
4651 --Name: is_backing_req_labor_expense
4652 --Pre-reqs:
4653 -- Must be called from Document Control window.
4654 --Modifies:
4655 -- None.
4656 --Locks:
4657 -- None.
4658 --Function:
4659 -- For a PO line or shipment, determines if ANY backing Requisition line
4660 -- is an Expense line or a parent Temp Labor line.
4661 --Parameters:
4662 --IN:
4663 --p_doc_level_id
4664 -- The Id of the enitity on which control Action was taken
4665 --p_doc_level
4666 -- Document Level at which control Action was taken
4667 --Returns:
4668 -- TRUE if backing Requisition line(s) exist for the given line or shipment
4669 -- and it is an Expense or parent Temp Labor line. FALSE otherwise.
4670 --Notes:
4671 -- Any backing Requisition lines will be found by examining all distributions
4672 -- of the given PO line or shipment. If the current entity is not a PO line
4673 -- or shipment, return FALSE (i.e. this function does not apply at the
4674 -- PO header level).
4675 --Testing:
4676 -- Refer the Unit Test Plan for 'HTML Agreements R12'
4677 --End of Comments
4678 -------------------------------------------------------------------------------
4679 FUNCTION is_backing_req_labor_expense(p_doc_level_id IN NUMBER,
4680 p_doc_level IN VARCHAR2)
4681 RETURN BOOLEAN
4682 IS
4683 l_po_line_id PO_LINES_ALL.po_line_id%TYPE := NULL;
4684 l_line_location_id PO_LINE_LOCATIONS_ALL.line_location_id%TYPE := NULL;
4685 l_has_labor_expense_req BOOLEAN := FALSE;
4686 d_pos NUMBER := 0;
4687 l_api_name CONSTANT VARCHAR2(30) := 'is_backing_req_labor_expense';
4688 d_module CONSTANT VARCHAR2(70) := 'po.plsql.PO_Document_Control_PVT.is_backing_req_labor_expense';
4689
4690 -- Selects dummy string for each backing Requisition line that is an
4691 -- Expense or parent Temp Labor line.
4692 CURSOR shipment_labor_expense_req_csr ( p_line_location_id NUMBER ) IS
4693 SELECT 'Backing Temp Labor/Expense Req'
4694 FROM po_distributions_all pod
4695 , po_requisition_lines_all prl
4696 , po_req_distributions_all prd
4697 WHERE pod.line_location_id = p_line_location_id -- For each PO Distribution
4698 AND pod.req_distribution_id = prd.distribution_id -- join to backing Req Distribution
4699 AND prd.requisition_line_id = prl.requisition_line_id -- and then up to the Req Line.
4700 AND ( ( prl.labor_req_line_id IS NOT NULL ) -- That Req Line must be an Expense line
4701 OR ( EXISTS ( SELECT 'Parent Temp Labor Req Line' -- or a parent Temp Labor line
4702 FROM po_requisition_lines_all prl2 -- of some Expense line.
4703 WHERE prl2.labor_req_line_id = prl.requisition_line_id
4704 )
4705 )
4706 );
4707 -- Selects dummy string for each backing Requisition line that is an
4708 -- Expense or parent Temp Labor line.
4709 CURSOR line_labor_expense_req_csr ( p_po_line_id NUMBER ) IS
4710 SELECT 'Backing Temp Labor/Expense Req'
4711 FROM po_distributions_all pod
4712 , po_requisition_lines_all prl
4713 , po_req_distributions_all prd
4714 WHERE pod.po_line_id = p_po_line_id -- For each PO Distribution
4715 AND pod.req_distribution_id = prd.distribution_id -- join to backing Req Distribution
4716 AND prd.requisition_line_id = prl.requisition_line_id -- and then up to the Req Line.
4717 AND ( ( prl.labor_req_line_id IS NOT NULL ) -- That Req Line must be an Expense line
4718 OR ( EXISTS ( SELECT 'Parent Temp Labor Req Line' -- or a parent Temp Labor line
4719 FROM po_requisition_lines_all prl2 -- of some Expense line.
4720 WHERE prl2.labor_req_line_id = prl.requisition_line_id
4721 )
4722 )
4723 );
4724 l_dummy1 shipment_labor_expense_req_csr%ROWTYPE;
4725 l_dummy2 line_labor_expense_req_csr%ROWTYPE;
4726 BEGIN
4727 IF (PO_LOG.d_proc) THEN
4728 PO_LOG.proc_begin(d_module);
4729 PO_LOG.proc_begin(d_module,'p_doc_level_id',p_doc_level_id);
4730 PO_LOG.proc_begin(d_module,'p_doc_level',p_doc_level);
4731 END IF;
4732
4733 IF(p_doc_level = PO_CORE_S.g_doc_level_LINE) THEN
4734 l_po_line_id := p_doc_level_id;
4735 ELSIF(p_doc_level = PO_CORE_S.g_doc_level_SHIPMENT) THEN
4736 l_line_location_id := p_doc_level_id;
4737 END IF;
4738
4739 d_pos := 10;
4740 IF ( l_line_location_id IS NOT NULL ) THEN
4741 d_pos := 20;
4742 OPEN shipment_labor_expense_req_csr(l_line_location_id);
4743 FETCH shipment_labor_expense_req_csr INTO l_dummy1;
4744 l_has_labor_expense_req := shipment_labor_expense_req_csr%FOUND;
4745 CLOSE shipment_labor_expense_req_csr;
4746 d_pos := 30;
4747 ELSIF ( l_po_line_id IS NOT NULL ) THEN
4748 d_pos := 40;
4749 OPEN line_labor_expense_req_csr(l_po_line_id);
4750 FETCH line_labor_expense_req_csr INTO l_dummy2;
4751 l_has_labor_expense_req := line_labor_expense_req_csr%FOUND;
4752 CLOSE line_labor_expense_req_csr;
4753 d_pos := 50;
4754 END IF;
4755
4756 IF (PO_LOG.d_proc) THEN
4757 PO_LOG.proc_end(d_module,'l_has_labor_expense_req',l_has_labor_expense_req);
4758 PO_LOG.proc_end(d_module,'l_line_location_id',l_line_location_id);
4759 PO_LOG.proc_end(d_module,'l_po_line_id',l_po_line_id);
4760 PO_LOG.proc_end(d_module);
4761 END IF;
4762
4763 return (l_has_labor_expense_req);
4764 EXCEPTION
4765 WHEN OTHERS THEN
4766 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name||':'||d_pos);
4767 IF PO_LOG.d_exc THEN
4768 PO_LOG.exc(d_module,d_pos,'Unhandled Exception in' || d_module);
4769 END IF;
4770 RAISE;
4771 END is_backing_req_labor_expense;
4772 -----------------------------------------------------------------------------
4773 --Start of Comments
4774 --Name: get_cancel_req_chkbox_attributes
4775 --Pre-reqs:
4776 -- None.
4777 --Modifies:
4778 -- None.
4779 --Locks:
4780 -- None.
4781 --Function:
4782 -- This procedure would be responsible to fetch the various attributes, which
4783 -- would determine the UI features of the field. That is whether it should be
4784 -- Y or N and whether it should be enabled or disabled
4785 --Parameters:
4786 --IN:
4787 --p_doc_level_id
4788 -- The Id of the enitity on which control Action was taken
4789 --p_doc_level
4790 -- Document Level at which control Action was taken
4791 --p_doc_header_id
4792 -- Document header Id
4793 --p_doc_sub_type
4794 -- Document Sub Type (type_lookup_code)
4795 --p_cancel_req_on_cancel_po
4796 -- Cancel Requisition on Cancel PO Flag
4797 --OUT:
4798 --x_drop_ship_flag
4799 -- PO is a Drop Ship PO
4800 --x_labor_expense_req_flag
4801 -- Any backing Requisition line is an Expense line or a parent Temp Labor line
4802 --x_svc_line_with_req_flag
4803 -- If PO has at least one Services Line with a backing req
4804 --x_fps_line_ship_with_req_flag
4805 -- If any PO line or shipment of line type Fixed Price Service has
4806 -- a backing requisition.
4807 --x_return_status
4808 -- Return Status of API .
4809 --Testing:
4810 -- Refer the Unit Test Plan for 'HTML Agreements R12'
4811 --End of Comments
4812 ----------------------------------------------------------------------------
4813 procedure get_cancel_req_chkbox_attr(p_doc_level_id IN NUMBER,
4814 p_doc_header_id IN NUMBER,
4815 p_doc_level IN VARCHAR2,
4816 p_doc_subtype IN VARCHAR2,
4817 p_cancel_req_on_cancel_po IN VARCHAR2,
4818 x_drop_ship_flag OUT NOCOPY VARCHAR2,
4819 x_labor_expense_req_flag OUT NOCOPY VARCHAR2,
4820 x_svc_line_with_req_flag OUT NOCOPY VARCHAR2,
4821 x_fps_line_ship_with_req_flag OUT NOCOPY VARCHAR2,
4822 x_return_status OUT NOCOPY VARCHAR2)
4823 IS
4824 d_pos NUMBER;
4825 l_api_name CONSTANT VARCHAR2(30) := 'get_cancel_req_chkbox_attr';
4826 d_module CONSTANT VARCHAR2(70) := 'po.plsql.PO_Document_Control_PVT.get_cancel_req_chkbox_attr';
4827 BEGIN
4828 IF (PO_LOG.d_proc) THEN
4829 PO_LOG.proc_begin(d_module);
4830 PO_LOG.proc_begin(d_module,'p_doc_level_id',p_doc_level_id);
4831 PO_LOG.proc_begin(d_module,'p_doc_level',p_doc_level);
4832 PO_LOG.proc_begin(d_module,'p_doc_subtype',p_doc_subtype);
4833 PO_LOG.proc_begin(d_module,'p_cancel_req_on_cancel_po',p_cancel_req_on_cancel_po);
4834 END IF;
4835 d_pos := 10;
4836 --Initialise Out Variables
4837 x_drop_ship_flag := 'N';
4838 x_labor_expense_req_flag := 'N';
4839 x_svc_line_with_req_flag := 'N';
4840 x_fps_line_ship_with_req_flag := 'N';
4841 x_return_status := FND_API.g_ret_sts_success;
4842 --Check for dropship PO
4843 IF(PO_COPYDOC_S1.po_is_dropship(p_doc_header_id)) THEN
4844 x_drop_ship_flag := 'Y';
4845 RAISE PO_CORE_S.g_early_return_exc;
4846 END IF;
4847
4848 d_pos := 20;
4849
4850 IF p_cancel_req_on_cancel_po <> 'A' THEN
4851 -- Check if backing Requisition line(s) exist for the given line or shipment
4852 -- and it is an Expense or parent Temp Labor line.
4853 IF(is_backing_req_labor_expense(p_doc_level_id => p_doc_level_id,
4854 p_doc_level => p_doc_level)) THEN
4855 d_pos := 30;
4856 x_labor_expense_req_flag := 'Y';
4857 RAISE PO_CORE_S.g_early_return_exc;
4858 END IF;
4859
4860 d_pos := 40;
4861 -- Check if a given PO contains at least one Services line, i.e. if the
4862 -- value basis of any line is FIXED PRICE or RATE
4863 IF(p_doc_level = PO_CORE_S.g_doc_level_HEADER
4864 AND p_doc_subtype = PO_CONSTANTS_SV.STANDARD) THEN
4865
4866 IF (PO_SERVICES_PVT.check_po_has_svc_line_with_req(p_doc_header_id)) THEN
4867 d_pos := 50;
4868 x_svc_line_with_req_flag := 'Y';
4869 RAISE PO_CORE_S.g_early_return_exc;
4870 END IF;
4871 END IF; --p_doc_level = 'HEADER' AND docSubType = 'STANDARD'
4872
4873 d_pos := 60;
4874 -- Checks if the line type is Fixed Price Service AND a backing
4875 -- requisition exists for the given line or shipment
4876 IF(p_doc_subtype = PO_CONSTANTS_SV.STANDARD) THEN
4877 IF p_doc_level = PO_CORE_S.g_doc_level_LINE THEN
4878 IF PO_SERVICES_PVT.is_FPS_po_line_with_req(p_doc_level_id) THEN
4879 x_fps_line_ship_with_req_flag := 'Y';
4880 END IF;
4881 ELSIF p_doc_level = PO_CORE_S.g_doc_level_SHIPMENT THEN
4882 IF PO_SERVICES_PVT.is_FPS_po_shipment_with_req(p_doc_level_id) THEN
4883 x_fps_line_ship_with_req_flag := 'Y';
4884 END IF;
4885 END IF; --p_doc_level = 'LINE'
4886 END IF; --p_doc_subtype = 'STANDARD'
4887 END IF; --p_cancel_req_on_cancel_po = 'A'
4888
4889 IF (PO_LOG.d_proc) THEN
4890 PO_LOG.proc_end(d_module,'x_drop_ship_flag',x_drop_ship_flag);
4891 PO_LOG.proc_end(d_module,'x_labor_expense_req_flag',x_labor_expense_req_flag);
4892 PO_LOG.proc_end(d_module,'x_svc_line_with_req_flag',x_svc_line_with_req_flag);
4893 PO_LOG.proc_end(d_module,'x_fps_line_ship_with_req_flag',x_fps_line_ship_with_req_flag);
4894 PO_LOG.proc_end(d_module,'x_return_status',x_return_status);
4895 PO_LOG.proc_end(d_module);
4896 END IF;
4897
4898 EXCEPTION
4899 WHEN PO_CORE_S.g_early_return_exc THEN
4900 IF PO_LOG.d_stmt THEN
4901 PO_LOG.stmt(d_module,d_pos,'Early exit from ' || d_module);
4902 END IF;
4903 WHEN OTHERS THEN
4904 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4905 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name||':'||d_pos);
4906 IF PO_LOG.d_exc THEN
4907 PO_LOG.exc(d_module,d_pos,'Unhandled Exception in' || d_module);
4908 END IF;
4909 END get_cancel_req_chkbox_attr;
4910 ------------------------------------------------------------------------------
4911 --Start of Comments
4912 --Name: get_valid_control_actions
4913 --Pre-reqs:
4914 -- None.
4915 --Modifies:
4916 -- None.
4917 --Locks:
4918 -- None.
4919 --Function:
4920 -- This procedure would get us the list of valid Document Control Actions
4921 -- for a given
4922 -- document and the level for which it is requested.
4923 --Parameters:
4924 --IN:
4925 --p_mode
4926 -- The mode in which the document is <CREATE/UPDATE/SUMMARY/VIEW>
4927 --p_doc_type
4928 -- The document Type (PO/PA)
4929 --p_doc_level
4930 -- Document Level at which control Action was taken
4931 --p_doc_level_id
4932 -- The Id of the enitity on which control Action was taken
4933 --p_doc_type
4934 -- Document Type
4935 --p_doc_header_id
4936 -- Document Header Id
4937 --p_item_id
4938 -- If the p_doc_level is 'LINE', the Item ID of the Item on the line
4939 --OUT:
4940 --x_valid_ctrl_ctn_tbl
4941 -- List of valid Document Control Actions for the given entity .
4942 --x_return_status
4943 -- Return Status of API .
4944 --Testing:
4945 -- Refer the Unit Test Plan for 'HTML Agreements R12'
4946 --End of Comments
4947 -----------------------------------------------------------------------------
4948 procedure get_valid_control_actions( p_mode IN VARCHAR2
4949 ,p_doc_level IN VARCHAR2
4950 ,p_doc_type IN VARCHAR2
4951 ,p_doc_header_id IN NUMBER
4952 ,p_doc_level_id IN NUMBER
4953 ,p_draft_id IN NUMBER -- <Mod Project>
4954 ,x_return_status OUT NOCOPY VARCHAR2
4955 ,x_valid_ctrl_ctn_tbl OUT NOCOPY PO_TBL_VARCHAR30)
4956 IS
4957 l_valid_actions_tbl g_lookup_code_tbl_type;
4958 l_displayed_field_tbl g_displayed_field_tbl_type;
4959 l_doc_subtype PO_HEADERS_ALL.type_lookup_code%type;
4960 l_cons_trans_exist VARCHAR2(1);
4961 l_index NUMBER;
4962 l_agent_id NUMBER;
4963 l_item_id PO_LINES_ALL.item_Id%TYPE;
4964 l_po_line_id PO_LINES_ALL.po_line_id%TYPE;
4965 l_current_action PO_LOOKUP_CODES.lookup_code%TYPE;
4966 l_mode VARCHAR2(30);
4967 l_ship_invalid_for_ctrl_actn VARCHAR2(1) := 'N';
4968 d_pos NUMBER;
4969 l_api_name CONSTANT VARCHAR2(30) := 'get_valid_control_actions';
4970 d_module CONSTANT VARCHAR2(70) := 'po.plsql.PO_Document_Control_PVT.get_valid_control_actions';
4971 BEGIN
4972 IF (PO_LOG.d_proc) THEN
4973 PO_LOG.proc_begin(d_module);
4974 PO_LOG.proc_begin(d_module,'p_mode',p_mode);
4975 PO_LOG.proc_begin(d_module,'p_doc_level',p_doc_level);
4976 PO_LOG.proc_begin(d_module,'p_doc_type',p_doc_type);
4977 PO_LOG.proc_begin(d_module,'p_doc_header_id',p_doc_header_id);
4978 PO_LOG.proc_begin(d_module,'p_doc_level_id',p_doc_level_id);
4979 END IF;
4980 --Initialisation of local variables
4981 x_return_status := FND_API.g_ret_sts_success;
4982 l_cons_trans_exist := 'N';
4983 l_index := 1;
4984 l_mode := UPPER(p_mode);
4985 x_valid_ctrl_ctn_tbl := PO_TBL_VARCHAR30();
4986 d_pos := 10;
4987 --Get the Employee Id of the Current User
4988 l_agent_id := fnd_global.employee_id;
4989 --Get Document Sub type
4990 IF(p_doc_type IN (PO_CORE_S.g_doc_type_PO, PO_CORE_S.g_doc_type_PA)) THEN
4991 SELECT type_lookup_code
4992 INTO l_doc_subtype
4993 FROM po_headers_all
4994 WHERE po_header_id = p_doc_header_id;
4995 END IF;
4996 d_pos := 20;
4997 IF (PO_LOG.d_stmt) THEN
4998 PO_LOG.stmt(d_module,d_pos,'l_agent_id',l_agent_id);
4999 PO_LOG.stmt(d_module,d_pos,'l_doc_subtype',l_doc_subtype);
5000 END IF;
5001 IF (p_doc_level = PO_CORE_S.g_doc_level_HEADER) THEN --header level
5002 d_pos := 30;
5003 PO_DOCUMENT_CONTROL_PVT.get_header_actions
5004 ( p_doc_subtype => l_doc_subtype
5005 , p_doc_id => p_doc_level_id
5006 , p_agent_id => l_agent_id
5007 , x_lookup_code_tbl => l_valid_actions_tbl
5008 , x_displayed_field_tbl => l_displayed_field_tbl
5009 , x_return_status => x_return_status
5010 , p_mode => l_mode);
5011
5012 ELSIF (p_doc_level = PO_CORE_S.g_doc_level_LINE) THEN --line level
5013 d_pos := 40;
5014 --get the itme_id for the consumption transaction existence check
5015 SELECT item_id
5016 INTO l_item_id
5017 FROM po_lines_merge_v -- <Mod Project> Replaced po_lines_all
5018 WHERE po_line_id = p_doc_level_id
5019 AND draft_id = p_draft_id;
5020
5021 IF (PO_LOG.d_stmt) THEN
5022 PO_LOG.stmt(d_module,d_pos,'l_item_id',l_agent_id);
5023 END IF;
5024
5025 PO_DOCUMENT_CONTROL_PVT.get_line_actions
5026 ( p_doc_subtype => l_doc_subtype
5027 , p_doc_line_id => p_doc_level_id
5028 , p_agent_id => l_agent_id
5029 , x_lookup_code_tbl => l_valid_actions_tbl
5030 , x_displayed_field_tbl => l_displayed_field_tbl
5031 , x_return_status => x_return_status
5032 , p_mode => l_mode);
5033
5034 ELSIF (p_doc_level = PO_CORE_S.g_doc_level_SHIPMENT) THEN
5035 d_pos := 50;
5036 --shipment levl
5037 PO_DOCUMENT_CONTROL_PVT.get_shipment_actions
5038 ( p_doc_type => p_doc_type
5039 , p_doc_subtype => l_doc_subtype
5040 , p_doc_line_loc_id => p_doc_level_id
5041 , p_agent_id => l_agent_id
5042 , x_lookup_code_tbl => l_valid_actions_tbl
5043 , x_displayed_field_tbl => l_displayed_field_tbl
5044 , x_return_status => x_return_status
5045 , p_mode => l_mode);
5046
5047 END IF;
5048 IF (PO_LOG.d_stmt) THEN
5049 PO_LOG.stmt(d_module,d_pos,'x_return_status',x_return_status);
5050 END IF;
5051 d_pos := 60;
5052
5053 IF (x_return_status = FND_API.g_ret_sts_success)then
5054
5055 IF p_doc_level IN (PO_CORE_S.g_doc_level_HEADER, PO_CORE_S.g_doc_level_LINE) THEN
5056 -- Checks if there exists a consumption transaction that is in process for
5057 -- the passed in transaction source document ID and and item ID.
5058 l_cons_trans_exist := PO_INV_THIRD_PARTY_STOCK_MDTR.consumption_trans_exist(
5059 p_doc_header_id,
5060 l_item_id);
5061 END IF;
5062 d_pos := 70;
5063 IF (PO_LOG.d_stmt) THEN
5064 PO_LOG.stmt(d_module,d_pos,'l_cons_trans_exist',l_cons_trans_exist);
5065 END IF;
5066 --<Bug#4515762 Start>
5067 --Prevent cancel option for shipment if it is the only shipment on the
5068 --line that is not cancelled or finally closed.
5069 IF(p_doc_level = PO_CORE_S.g_doc_level_SHIPMENT) THEN
5070
5071 BEGIN
5072 d_pos := 75;
5073 SELECT 'N'
5074 INTO l_ship_invalid_for_ctrl_actn
5075 FROM DUAL
5076 WHERE EXISTS(
5077 SELECT 1
5078 FROM po_line_locations_all poll1,
5079 po_line_locations_all poll2
5080 WHERE poll1.line_location_id = p_doc_level_id
5081 AND poll1.po_line_id = poll2.po_line_id
5082 AND NVL(poll2.cancel_flag,'N') <> 'Y'
5083 AND NVL(poll2.payment_type, 'NULL') NOT IN ('ADVANCE', 'DELIVERY') --<Complex Work R12>
5084 AND NVL(poll2.closed_code, PO_DOCUMENT_ACTION_PVT.g_doc_action_OPEN)
5085 <> PO_DOCUMENT_ACTION_PVT.g_doc_closed_sts_FIN_CLOSED
5086 AND poll2.line_location_id <> p_doc_level_id);
5087 EXCEPTION
5088 WHEN NO_DATA_FOUND THEN
5089 --Current shipment is the only shipment on the line that is not cancelled or finally closed
5090 --OR there are no open, uncancelled shipments.
5091 IF PO_LOG.d_stmt THEN
5092 PO_LOG.stmt(d_module,d_pos,'Control Action cannot be performed on the shipment');
5093 END IF;
5094 l_ship_invalid_for_ctrl_actn := 'Y';
5095 END;
5096
5097 END IF; --p_doc_level = PO_CORE_S.g_doc_level_SHIPMENT
5098 --<Bug#4515762 End>
5099
5100 FOR i IN l_valid_actions_tbl.first..l_valid_actions_tbl.last
5101 LOOP
5102 d_pos := 80;
5103 l_current_action := l_valid_actions_tbl(i);
5104 IF (PO_LOG.d_stmt) THEN
5105 PO_LOG.stmt(d_module,d_pos,'l_current_action',l_current_action);
5106 END IF;
5107 -- If consumption transaction exist we don't allow Cancel and Finally
5108 -- Close actions
5109 IF (l_cons_trans_exist = 'Y'
5110 AND l_current_action in ('CANCEL PO','CANCEL PO LINE', PO_DOCUMENT_ACTION_PVT.g_doc_action_FINALLY_CLOSE)) THEN
5111 NULL;
5112 --If it is the only shipment then we dont allow the cancellation or finally closing of the shipment.
5113 ELSIF(l_ship_invalid_for_ctrl_actn = 'Y'
5114 AND l_current_action in ('CANCEL PO SHIPMENT', PO_DOCUMENT_ACTION_PVT.g_doc_action_FINALLY_CLOSE)) THEN
5115 NULL;
5116 ELSE
5117 -- For Update Mode only Cancel Related And Hold Related
5118 -- Control Actions are valid
5119 IF(l_mode = 'UPDATE'
5120 AND NOT (l_current_action LIKE 'CANCEL%'
5121 OR l_current_action LIKE '%HOLD%')) THEN
5122 NULL;
5123 ELSE
5124 x_valid_ctrl_ctn_tbl.extend;
5125 IF(l_mode = 'SUMMARY' AND l_current_action LIKE 'CANCEL%' ) THEN
5126 -- For Summary we Show Cancel Action as Cancel at all the Levels
5127 d_pos := 90;
5128 IF (PO_LOG.d_stmt) THEN
5129 PO_LOG.stmt(d_module,d_pos,l_current_action || 'is replaced by CANCEL for summary mode');
5130 END IF;
5131 x_valid_ctrl_ctn_tbl(l_index) := PO_DOCUMENT_ACTION_PVT.g_doc_action_CANCEL;
5132 ELSIF(l_mode = 'UPDATE' AND l_current_action = 'CANCEL PO') THEN
5133
5134 IF(p_doc_type = PO_CORE_S.g_doc_type_PO) THEN
5135 -- For Update mode we Show Cancel Action at Header as Cancel Order
5136 d_pos := 100;
5137 IF (PO_LOG.d_stmt) THEN
5138 PO_LOG.stmt(d_module,d_pos,l_current_action || 'is replaced by CANCEL ORDER for update mode');
5139 END IF;
5140 x_valid_ctrl_ctn_tbl(l_index) := 'CANCEL ORDER';
5141 ELSIF(p_doc_type = PO_CORE_S.g_doc_type_PA) THEN
5142 -- For Update mode we Show Cancel Action at Header as Cancel Agreement.
5143 d_pos := 110;
5144 IF (PO_LOG.d_stmt) THEN
5145 PO_LOG.stmt(d_module,d_pos,l_current_action || 'is replaced by CANCEL AGREEMENT for update mode');
5146 END IF;
5147 x_valid_ctrl_ctn_tbl(l_index) := 'CANCEL AGREEMENT';
5148 END IF; -- (p_doc_type = 'PO')
5149 ELSE
5150 d_pos := 120;
5151 IF (PO_LOG.d_stmt) THEN
5152 PO_LOG.stmt(d_module,d_pos,l_current_action || 'is directly placed');
5153 END IF;
5154 x_valid_ctrl_ctn_tbl(l_index) := l_valid_actions_tbl(i);
5155 END IF; --mode = 'SUMMARY' AND l_current_action LIKE 'CANCEL%'
5156 l_index := l_index+1;
5157 END IF; --l_mode = 'UPDATE'
5158 END IF; -- l_cons_trans_exist = 'Y'
5159 END LOOP;
5160
5161 ELSIF (x_return_status = FND_API.g_ret_sts_error)then
5162 d_pos := 130;
5163 IF (PO_LOG.d_stmt) THEN
5164 PO_LOG.stmt(d_module,d_pos,'No Valid Control Action Found');
5165 END IF;
5166 ELSE
5167 d_pos := 140;
5168 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5169 END IF; --x_return_status = FND_API.g_ret_sts_success
5170 IF (PO_LOG.d_proc) THEN
5171 PO_LOG.proc_end(d_module);
5172 END IF;
5173 EXCEPTION
5174 WHEN OTHERS THEN
5175 x_return_status := FND_API.g_ret_sts_unexp_error;
5176 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name||':'||d_pos);
5177 IF PO_LOG.d_exc THEN
5178 PO_LOG.exc(d_module,d_pos,'Unhandled Exception in' || d_module);
5179 END IF;
5180 END get_valid_control_actions;
5181
5182 --------------------------------------------------------------------------------
5183 --<Bug 14254141 :Cancel Refactoring Project (Communicate)>
5184 --Start of Comments
5185 --Name: doc_communicate_oncancel
5186 --Function:
5187 -- called after the successful cancel action
5188 -- method to communicate the docuemnt status to the Supplier
5189 --Parameters:
5190 --IN:
5191 -- p_doc_type
5192 -- p_doc_subtype
5193 -- p_doc_id
5194 -- p_communication_method_option
5195 -- p_communication_method_value
5196
5197 --
5198 --IN OUT :
5199 --OUT :
5200
5201 -- x_return_status
5202 -- FND_API.G_RET_STS_SUCCESS if communicate action succeeds
5203 -- FND_API.G_RET_STS_ERROR if communicate action fails
5204 -- FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
5205 --
5206 --End of Comments
5207 --------------------------------------------------------------------------------
5208
5209
5210 PROCEDURE doc_communicate_oncancel(
5211 p_doc_type IN VARCHAR2,
5212 p_doc_subtype IN VARCHAR2,
5213 p_doc_id IN NUMBER,
5214 p_communication_method_option IN VARCHAR2,
5215 p_communication_method_value IN VARCHAR2,
5216 x_return_status OUT NOCOPY VARCHAR2
5217 )
5218 IS
5219
5220 d_api_name CONSTANT VARCHAR2(30) := 'doc_communicate_oncancel';
5221 d_api_version CONSTANT NUMBER := 1.0;
5222 d_module CONSTANT VARCHAR2(100) := g_pkg_name|| d_api_name;
5223 l_progress VARCHAR2(3) := '000' ;
5224
5225
5226 l_conterms_exist_flag PO_HEADERS_ALL.conterms_exist_flag%TYPE;
5227 l_auth_status VARCHAR2(30);
5228 l_revision_num NUMBER;
5229 l_request_id NUMBER := 0;
5230 l_doc_type VARCHAR2(30);
5231 l_archive_count NUMBER;
5232
5233
5234
5235 BEGIN
5236 x_return_status := FND_API.G_RET_STS_SUCCESS;
5237
5238
5239 IF g_debug_stmt THEN
5240 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
5241 PO_DEBUG.debug_begin(d_module);
5242 PO_DEBUG.debug_var(d_module, l_progress, 'p_doc_type', p_doc_type);
5243 PO_DEBUG.debug_var(d_module, l_progress, 'p_doc_subtype', p_doc_subtype);
5244 PO_DEBUG.debug_var(d_module, l_progress, 'p_doc_id', p_doc_id);
5245 PO_DEBUG.debug_var(d_module, l_progress, 'p_communication_method_value', p_communication_method_value);
5246 PO_DEBUG.debug_var(d_module, l_progress, 'p_communication_method_option', p_communication_method_option);
5247 END IF;
5248 END IF;
5249
5250
5251
5252 IF p_communication_method_option IS NOT NULL THEN
5253
5254 IF p_doc_type <> 'RELEASE' THEN
5255
5256 l_progress :='001';
5257 l_doc_type := p_doc_subtype;
5258
5259 SELECT Nvl(conterms_exist_flag,'N'),
5260 authorization_status,
5261 revision_num
5262 INTO l_conterms_exist_flag,
5263 l_auth_status,
5264 l_revision_num
5265 FROM po_headers_all
5266 WHERE po_header_id = p_doc_id;
5267
5268 ELSE
5269
5270 l_progress :='002';
5271
5272 l_doc_type :=p_doc_type;
5273 select 'N',
5274 authorization_status,
5275 revision_num
5276 INTO l_conterms_exist_flag,
5277 l_auth_status,
5278 l_revision_num
5279 FROM po_releases_all
5280 WHERE po_release_id = p_doc_id;
5281
5282 END IF;
5283
5284 IF g_debug_stmt THEN
5285 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
5286 PO_DEBUG.debug_var(d_module,l_progress,'l_conterms_exist_flag',l_conterms_exist_flag);
5287 PO_DEBUG.debug_var(d_module,l_progress,'l_auth_status',l_auth_status);
5288 PO_DEBUG.debug_var(d_module,l_progress,'l_revision_num',l_revision_num);
5289 PO_DEBUG.debug_var(d_module,l_progress,'l_doc_type',l_doc_type);
5290 END IF;
5291 END IF;
5292 l_archive_count := 1;
5293
5294 -- When the document is not in Approved/Pre-Approved status, the
5295 -- latest revision FROM archive is used FOR communication
5296 -- So in case Archive entry does not exists for teh document, the
5297 -- po_communication_pvt.communicate routine will through No_data_Found exception
5298 -- So calling the communication routine only for document in Approved/Pre-Approved status
5299 -- Or for those, teh archive entry exists.
5300
5301 IF l_auth_status NOT IN (po_document_action_pvt.g_doc_status_APPROVED,
5302 po_document_action_pvt.g_doc_status_PREAPPROVED)
5303 THEN
5304 IF p_doc_type <> 'RELEASE' THEN
5305
5306 l_progress :='003';
5307 SELECT Count(1)
5308 INTO l_archive_count
5309 FROM po_headers_archive_all
5310 WHERE po_header_id = p_doc_id;
5311
5312 ELSE
5313 l_progress :='004';
5314 SELECT Count(1)
5315 INTO l_archive_count
5316 FROM po_releases_archive_all
5317 WHERE po_release_id = p_doc_id;
5318
5319 END IF;
5320
5321
5322 END IF;
5323
5324 IF g_debug_stmt THEN
5325 PO_DEBUG.debug_var(d_module,l_progress,'l_archive_count',l_archive_count);
5326 END IF;
5327
5328 IF l_archive_count >0 THEN
5329
5330 l_progress :='005';
5331 po_communication_pvt.communicate(
5332 p_authorization_status=>l_auth_status,
5333 p_with_terms=>l_conterms_exist_flag,
5334 p_language_code=>FND_GLOBAL.CURRENT_LANGUAGE,
5335 p_mode =>p_communication_method_option,
5336 p_document_id =>p_doc_id,
5337 p_revision_number =>l_revision_num,
5338 p_document_type =>l_doc_type,
5339 p_fax_number =>p_communication_method_value,
5340 p_email_address =>p_communication_method_value,
5341 p_request_id =>l_request_id);
5342
5343 IF g_debug_stmt
5344 AND (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
5345 PO_DEBUG.debug_var(d_module,l_progress,'l_request_id',l_request_id);
5346 END IF;
5347 END IF;
5348
5349
5350 END IF;
5351
5352 EXCEPTION
5353 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5354 FND_MSG_PUB.add_exc_msg(g_module_prefix, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
5355 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5356
5357
5358 WHEN FND_API.G_EXC_ERROR THEN
5359 x_return_status := FND_API.G_RET_STS_ERROR;
5360 FND_MSG_PUB.add_exc_msg(g_module_prefix, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
5361
5362
5363 WHEN OTHERS THEN
5364 FND_MSG_PUB.add_exc_msg(g_module_prefix, d_api_name||':'||l_progress||':'||SQLCODE||':'||SQLERRM);
5365 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5366
5367
5368 END doc_communicate_oncancel;
5369
5370
5371 END PO_Document_Control_PVT;
[Home] [Help]