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