1 PACKAGE BODY PO_Document_Cancel_PVT AS
2 /* $Header: POXVDCAB.pls 120.3 2005/10/24 16:57:44 shsiung noship $ */
3
4 -- Read the profile option that enables/disables the debug log
5 g_fnd_debug CONSTANT VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
6
7 g_module_prefix CONSTANT VARCHAR2(50) := 'po.plsql.' || g_pkg_name || '.';
8
9 -- <Shared Proc FPJ START>
10
11 g_debug_stmt CONSTANT BOOLEAN := PO_DEBUG.is_debug_stmt_on;
12 g_debug_unexp CONSTANT BOOLEAN := PO_DEBUG.is_debug_unexp_on;
13
14 -- <Shared Proc FPJ END>
15
16 /**
17 * Public Procedure: cancel_document
18 * Requires: API message list has been initialized if p_init_msg_list is false.
19 * Modifies: All cancel columns and who columns for this document at the entity
20 * level of cancellation. API message list.
21 * Effects: Cancels the document at the header, line, or shipment level
22 * depending upon the document ID parameters after performing validations.
23 * Validations include state checks and cancel submission checks. If
24 * p_cbc_enabled is 'Y', then the CBC accounting date is updated to be
25 * p_action_date. If p_cancel_reqs_flag is 'Y', then backing requisitions will
26 * also be cancelled if allowable. Otherwise, they will be recreated.
27 * Encumbrance is recalculated for cancelled entities if enabled. If the
28 * cancel action is successful, the document's cancel and who columns will be
29 * updated at the specified entity level. Otherwise, the document will remain
30 * unchanged. All changes will be committed upon success if p_commit is
31 * FND_API.G_TRUE. Appends to API message list on error.
32 * Returns:
33 * x_return_status - FND_API.G_RET_STS_SUCCESS if cancel action succeeds
34 * FND_API.G_RET_STS_ERROR if cancel action fails
35 * FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
36 */
37 PROCEDURE cancel_document
38 (p_api_version IN NUMBER,
39 p_init_msg_list IN VARCHAR2,
40 p_commit IN VARCHAR2,
41 x_return_status OUT NOCOPY VARCHAR2,
42 p_doc_type IN PO_DOCUMENT_TYPES.document_type_code%TYPE,
43 p_doc_subtype IN PO_DOCUMENT_TYPES.document_subtype%TYPE,
44 p_doc_id IN NUMBER,
45 p_doc_line_id IN NUMBER,
46 p_doc_line_loc_id IN NUMBER,
47 p_cancel_agent_id IN PO_HEADERS.agent_id%TYPE,
48 p_action_date IN DATE,
49 p_cancel_reason IN PO_LINES.cancel_reason%TYPE,
50 p_cancel_reqs_flag IN VARCHAR2,
51 p_note_to_vendor IN PO_HEADERS.note_to_vendor%TYPE,
52 p_cbc_enabled IN VARCHAR2,
53 p_use_gldate IN VARCHAR2 -- <ENCUMBRANCE FPJ>
54 )
55 IS
56 PRAGMA AUTONOMOUS_TRANSACTION;
57
58 l_api_name CONSTANT VARCHAR2(30) := 'cancel_document';
59 l_api_version CONSTANT NUMBER := 1.0;
60 l_return_value NUMBER;
61 l_info_request VARCHAR2(25);
62 l_document_status VARCHAR2(240);
63 l_online_report_id NUMBER;
64 l_return_code VARCHAR2(25);
65 l_error_msg VARCHAR2(2000);
66
67 l_use_gldate VARCHAR2(1); -- <Encumbrance FPJ>
68
69 BEGIN
70 -- Start standard API initialization
71 IF FND_API.to_boolean(p_init_msg_list) THEN
72 FND_MSG_PUB.initialize;
73 END IF;
74 IF NOT FND_API.compatible_api_call(l_api_version, p_api_version,
75 l_api_name, g_pkg_name)
76 THEN
77 RAISE FND_API.g_exc_unexpected_error;
78 END IF;
79 x_return_status := FND_API.g_ret_sts_success;
80 -- End standard API initialization
81
82 IF (g_fnd_debug = 'Y') THEN
83 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
84 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix || l_api_name ||
85 '.invoked', 'Type: ' || NVL(p_doc_type,'null') ||
86 ', ID: ' || NVL(TO_CHAR(p_doc_id),'null'));
87 END IF;
88 END IF;
89
90
91 -- <Encumbrance FPJ START>
92
93 IF (p_use_gldate NOT IN ('Y', 'N')) THEN
94 l_use_gldate := PO_DOCUMENT_FUNDS_PVT.g_parameter_USE_PROFILE;
95 ELSE
96 l_use_gldate := p_use_gldate;
97 END IF;
98
99 -- <Encumbrance FPJ END>
100
101 -- <Encumbrance FPJ> : Passing USE_GLDATE as p_extra_arg4
102
103 l_return_value := PO_DOCUMENT_ACTIONS_SV.po_request_action
104 (Action => 'CANCEL',
105 Document_Type => p_doc_type,
106 Document_Subtype => p_doc_subtype,
107 Document_Id => p_doc_id,
108 Line_Id => p_doc_line_id,
109 Shipment_Id => p_doc_line_loc_id,
110 Distribution_Id => NULL,
111 Employee_id => p_cancel_agent_id,
112 New_Document_Status => NULL,
113 Offline_Code => NULL,
114 Note => NULL,
115 Approval_Path_Id => NULL,
116 Forward_To_Id => NULL,
117 Action_Date => p_action_date,
118 Override_Funds => NULL,
119 Info_Request => l_info_request,
120 Document_Status => l_document_status,
121 Online_Report_Id => l_online_report_id,
122 Return_Code => l_return_code,
123 Error_Msg => l_error_msg,
124 p_extra_arg1 => 'REASON=' || p_cancel_reason,
125 p_extra_arg2 => 'CANCEL_REQS='||p_cancel_reqs_flag,
126 p_extra_arg3 => 'CBC_FLAG=' || p_cbc_enabled,
127 p_extra_arg4 => 'USE_GLDATE=' || l_use_gldate);
128
129 IF (g_fnd_debug = 'Y') THEN
130 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
131 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, g_module_prefix || l_api_name ||
132 '.return_values',
133 'ret_val: ' || NVL(TO_CHAR(l_return_value),'null') ||
134 ', info_request: ' || NVL(l_info_request,'null') ||
135 ', document_status: ' || NVL(l_document_status,'null') ||
136 ', online_report_id: ' ||
137 NVL(TO_CHAR(l_online_report_id),'null') ||
138 ', return_code: ' || NVL(l_return_code,'null'));
139 END IF;
140 END IF;
141
142 -- Perform error handling
143 IF (l_return_value <> 0) THEN
144
145 IF (g_fnd_debug = 'Y') THEN
146 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
147 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, g_module_prefix ||
148 l_api_name || '.error_msg', NVL(l_error_msg,'null'));
149 END IF;
150 END IF;
151
152 IF (l_return_value = 3) THEN
153 -- Some other error occurred
154 FND_MESSAGE.set_name('PO','PO_CONTROL_USER_EXIT_FAILED');
155 FND_MESSAGE.set_token('USER_EXIT','DOCMGR-CANCEL');
156 FND_MESSAGE.set_token('RETURN_CODE',NVL(l_return_code,'OTHER'));
157 IF (g_fnd_debug = 'Y') THEN
158 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR) THEN
159 FND_LOG.message(FND_LOG.level_error, g_module_prefix ||
160 l_api_name || '.doc_mgr_failed_other', FALSE);
161 END IF;
162 END IF;
163 ELSE
164 -- The doc manager call failed
165 FND_MESSAGE.set_name('PO','PO_APP_NO_MANAGER');
166 IF (g_fnd_debug = 'Y') THEN
167 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR) THEN
168 FND_LOG.message(FND_LOG.level_error, g_module_prefix ||
169 l_api_name || '.doc_mgr_failed', FALSE);
170 END IF;
171 END IF;
172 END IF; --<if l_return_value = 3>
173
174 FND_MSG_PUB.add;
175 RAISE FND_API.g_exc_error;
176
177 END IF; --<if l_return_value ...>
178
179 -- Check for expected errors
180 IF (l_return_code IN ('SUBMISSION_FAILED', 'F', 'P', 'T')) THEN
181
182 IF (g_fnd_debug = 'Y') THEN
183 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
184 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, g_module_prefix||l_api_name
185 || '.error_msg2', NVL(l_error_msg,'null'));
186 END IF;
187 END IF;
188
189 IF l_online_report_id IS NOT NULL THEN
190 PO_Document_Control_PVT.add_online_report_msgs
191 (p_api_version => 1.0,
192 p_init_msg_list => FND_API.G_FALSE,
193 x_return_status => x_return_status,
194 p_online_report_id => l_online_report_id);
195 IF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
196 RAISE FND_API.g_exc_unexpected_error;
197 END IF;
198 ELSE
199 FND_MESSAGE.set_name('PO','PO_CONTROL_USER_EXIT_FAILED');
200 FND_MESSAGE.set_token('USER_EXIT','CANCEL');
201 FND_MESSAGE.set_token('RETURN_CODE',NVL(l_return_code,'FAIL'));
202 IF (g_fnd_debug = 'Y') THEN
203 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR) THEN
204 FND_LOG.message(FND_LOG.level_error, g_module_prefix ||
205 l_api_name || '.cancel_failed', FALSE);
206 END IF;
207 END IF;
208 FND_MSG_PUB.add;
209 END IF; -- if l_online_report_id IS ...
210
211 RAISE FND_API.g_exc_error;
212
213 ELSIF (l_return_code = 'STATE_FAILED') THEN
214
215 IF (g_fnd_debug = 'Y') THEN
216 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
217 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, g_module_prefix||l_api_name
218 || '.error_msg3', NVL(l_error_msg,'null'));
219 END IF;
220 END IF;
221
222 -- The document state was not valid for this cancel action
223 FND_MESSAGE.set_name('PO','PO_APP_APP_FAILED');
224 IF (g_fnd_debug = 'Y') THEN
225 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR) THEN
226 FND_LOG.message(FND_LOG.level_error, g_module_prefix ||
227 l_api_name || '.state_failed', FALSE);
228 END IF;
229 END IF;
230 FND_MSG_PUB.add;
231 RAISE FND_API.g_exc_error;
232
233 ELSIF (l_return_code = 'UPDATE_CBC_FAILED') THEN
234
235 IF (g_fnd_debug = 'Y') THEN
236 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_STATEMENT) THEN
237 FND_LOG.string(FND_LOG.LEVEL_STATEMENT, g_module_prefix ||
238 l_api_name||'.error_msg4', NVL(l_error_msg,'null'));
239 END IF;
240 END IF;
241
245 IF (g_fnd_debug = 'Y') THEN
242 -- update of cbc accounting date failed
243 FND_MESSAGE.set_name('IGC','IGC_DOCUMENT_NOT_FOUND');
244 FND_MESSAGE.set_token('DOC_ID',TO_CHAR(p_doc_id));
246 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR) THEN
247 FND_LOG.message(FND_LOG.level_error, g_module_prefix ||
248 l_api_name || '.igc_failed', FALSE);
249 END IF;
250 END IF;
251 FND_MSG_PUB.add;
252 RAISE FND_API.g_exc_error;
253
254 END IF; --<if l_return_code ...>
255
256
257 -- Only call update procedure when not at shipment level
258 IF (p_doc_line_loc_id IS NULL) THEN
259
260 PO_Document_Control_PVT.update_note_to_vendor
261 ( p_api_version => 1.0,
262 p_init_msg_list => FND_API.G_FALSE,
263 p_commit => FND_API.G_FALSE,
264 x_return_status => x_return_status,
265 p_doc_type => p_doc_type,
266 p_doc_id => p_doc_id,
267 p_doc_line_id => p_doc_line_id,
268 p_note_to_vendor => p_note_to_vendor );
269 IF (x_return_status = FND_API.g_ret_sts_error) THEN
270 RAISE FND_API.g_exc_error;
271 ELSIF (x_return_status = FND_API.g_ret_sts_unexp_error) THEN
272 RAISE FND_API.g_exc_unexpected_error;
273 END IF;
274
275 END IF; --<if p_doc_line_loc_id ...>
276
277
278 -- Must commit at the end of successful autonomous transaction
279 COMMIT;
280
281 EXCEPTION
282 WHEN FND_API.g_exc_error THEN
283 ROLLBACK;
284 x_return_status := FND_API.g_ret_sts_error;
285 WHEN FND_API.g_exc_unexpected_error THEN
286 ROLLBACK;
287 x_return_status := FND_API.g_ret_sts_unexp_error;
288 WHEN OTHERS THEN
289 ROLLBACK;
290 x_return_status := FND_API.g_ret_sts_unexp_error;
291 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
292 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
293 IF (g_fnd_debug = 'Y') THEN
294 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
295 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
296 l_api_name || '.others_exception', 'Exception');
297 END IF;
298 END IF;
299 END IF;
300 END cancel_document;
301
302
303 --<Bug 4571297 START>
304 /**
305 * Function : is_document_cto_order
306 * Description : Determines if the document is a CTO order.
307 * Parameters : p_doc_id - id of the document.
308 * p_doc_type - type of document, either 'PO' or 'RELEASE'
309 * Returns : TRUE if document is a CTO PO. FALSE otherwise.
310 * Notes : See Bug 4571297
311 */
312
313 FUNCTION is_document_cto_order
314 ( p_doc_id IN PO_HEADERS_ALL.po_header_id%TYPE,
315 p_doc_type IN PO_DOCUMENT_TYPES_ALL_B.document_type_code%TYPE
316 ) RETURN BOOLEAN
317 IS
318 l_cto_order NUMBER;
319 BEGIN
320 SELECT count(*)
321 INTO l_cto_order
322 FROM po_requisition_headers_all PRH,
323 po_requisition_lines_all PRL,
324 po_line_locations_all POLL
325 WHERE PRH.interface_source_code = 'CTO'
326 AND PRH.requisition_header_id = PRL.requisition_header_id
327 AND PRL.line_location_id = POLL.line_location_id
328 AND ((p_doc_type = 'PO') AND (POLL.po_header_id = p_doc_id)
329 OR
330 (p_doc_type = 'RELEASE') AND (POLL.po_release_id = p_doc_id));
331
332 IF (l_cto_order > 0) THEN
333 RETURN (TRUE);
334 ELSE
335 RETURN (FALSE);
336 END IF;
337
338 EXCEPTION
339 WHEN OTHERS THEN
340 RETURN (FALSE);
341 END is_document_cto_order;
342 --<Bug 4571297 END>
343
344 /**
345 * Public Procedure: val_cancel_backing_reqs
346 * Requires: API message list has been initialized if p_init_msg_list is false.
347 * PO and REQ encumbrance should be on.
348 * Modifies: API message list
349 * Effects: Ensures that the document has shipments that are not cancelled or
350 * finally closed, and that they are all fully encumbered. Appends to API
351 * message list on error.
352 * Returns:
353 * x_return_status - FND_API.G_RET_STS_SUCCESS if validation succeeds
354 * FND_API.G_RET_STS_ERROR if validation fails
355 * FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
356 */
357 PROCEDURE val_cancel_backing_reqs
358 (p_api_version IN NUMBER,
359 p_init_msg_list IN VARCHAR2,
360 x_return_status OUT NOCOPY VARCHAR2,
361 p_doc_type IN PO_DOCUMENT_TYPES.document_type_code%TYPE,
362 p_doc_id IN NUMBER)
363 IS
364
365 l_api_name CONSTANT VARCHAR2(30) := 'val_cancel_backing_reqs';
366 l_api_version CONSTANT NUMBER := 1.0;
367
368 BEGIN
369 --<Bug 4571297> Only perform validation if document is not CTO order
370 IF NOT PO_Document_Cancel_PVT.is_document_cto_order
371 (p_doc_type => p_doc_type,
375 IF FND_API.to_boolean(p_init_msg_list) THEN
372 p_doc_id => p_doc_id)
373 THEN
374 -- Start standard API initialization
376 FND_MSG_PUB.initialize;
377 END IF;
378 IF NOT FND_API.compatible_api_call(l_api_version, p_api_version,
379 l_api_name, g_pkg_name)
380 THEN
381 RAISE FND_API.g_exc_unexpected_error;
382 END IF;
383 x_return_status := FND_API.g_ret_sts_success;
384 -- End standard API initialization
385
386 IF (g_fnd_debug = 'Y') THEN
387 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
388 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix || l_api_name ||
389 '.invoked', 'Type: ' || NVL(p_doc_type,'null') ||
390 ', ID: ' || NVL(TO_CHAR(p_doc_id),'null'));
391 END IF;
392 END IF;
393
394 IF NOT PO_Document_Control_PVT.has_shipments
395 (p_api_version => 1.0,
396 p_init_msg_list => FND_API.G_FALSE,
397 x_return_status => x_return_status,
398 p_doc_type => p_doc_type,
399 p_doc_id => p_doc_id)
400 THEN
401 IF (x_return_status <> FND_API.g_ret_sts_success) THEN
402 RAISE FND_API.g_exc_unexpected_error;
403 END IF;
404
405 -- Document does not have any valid shipments.
406 RAISE FND_API.g_exc_error;
407 END IF; --<if has_shipments ...>
408
409
410 IF PO_Document_Control_PVT.has_unencumbered_shipments
411 (p_api_version => 1.0,
412 p_init_msg_list => FND_API.G_FALSE,
413 x_return_status => x_return_status,
414 p_doc_type => p_doc_type,
415 p_doc_id => p_doc_id)
416 THEN
417 -- Document has valid, unencumbered shipments. Cannot cancel reqs
418 -- because encumbrance is assumed to be on.
419 RAISE FND_API.g_exc_error;
420 END IF; --<if has_unencumbered_shipments ...>
421
422 -- Check that an error did not occur in previous procedure call
423 IF (x_return_status <> FND_API.g_ret_sts_success) THEN
424 RAISE FND_API.g_exc_unexpected_error;
425 END IF;
426
427 END IF;
428
429 EXCEPTION
430 WHEN FND_API.g_exc_error THEN
431 x_return_status := FND_API.g_ret_sts_error;
432 FND_MESSAGE.set_name('PO','PO_CANCEL_REQ_DISALLOWED');
433 IF (g_fnd_debug = 'Y') THEN
434 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_ERROR) THEN
435 FND_LOG.message(FND_LOG.level_error, g_module_prefix || l_api_name
436 || '.cancel_req_disallowed', FALSE);
437 END IF;
438 END IF;
439 FND_MSG_PUB.add;
440 WHEN FND_API.g_exc_unexpected_error THEN
441 x_return_status := FND_API.g_ret_sts_unexp_error;
442 WHEN OTHERS THEN
443 x_return_status := FND_API.g_ret_sts_unexp_error;
444 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
445 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
446 IF (g_fnd_debug = 'Y') THEN
447 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
448 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
449 l_api_name || '.others_exception', 'Exception');
450 END IF;
451 END IF;
452 END IF;
453 END val_cancel_backing_reqs;
454
455
456 /**
457 * Public Procedure: fetch_req_lines
458 * Requires: API message list has been initialized if p_init_msg_list is false.
459 * Modifies: API message list
460 * Effects: Fetches all requisition line IDs linked to the document at
461 * p_control_level, specified by p_control_level_id. Appends to the API
462 * message list upon unexpected errors only.
463 * Returns:
464 * x_return_status - FND_API.G_RET_STS_SUCCESS if found requisition line IDs
465 * FND_API.G_RET_STS_ERROR if found no requisition line IDs
466 * FND_API.G_RET_STS_UNEXP_ERROR if unexpected error occurs
467 * x_req_line_id_tbl - A PL/SQL table of requisition line IDs
468 */
469 PROCEDURE fetch_req_lines
470 (p_api_version IN NUMBER,
471 p_init_msg_list IN VARCHAR2,
472 x_return_status OUT NOCOPY VARCHAR2,
473 p_doc_id IN NUMBER,
474 p_doc_line_id IN NUMBER,
475 p_doc_line_loc_id IN NUMBER,
476 p_control_level IN NUMBER,
477 x_req_line_id_tbl OUT NOCOPY PO_ReqChangeRequestWF_PVT.ReqLineID_tbl_type)
478 IS
479
480 l_api_name CONSTANT VARCHAR2(30) := 'fetch_req_lines';
481 l_api_version CONSTANT NUMBER := 1.0;
482
483
484 CURSOR l_shipment_csr IS
485 -- SQL What: Querying backing requisiton line IDs of this shipment
486 -- SQL Why: Need to cancel or recreate these backing reqs
487 --<Complex Work R12>: rewrote this SQL state to use the tie between
488 --PO dist and Req dist instead of Req line to PO line loc.
489 SELECT DISTINCT(prd.requisition_line_id)
490 FROM po_req_distributions_all prd,
494 AND pod.line_location_id = poll.line_location_id
491 po_line_locations_all poll,
492 po_distributions_all pod
493 WHERE pod.line_location_id = p_doc_line_loc_id
495 AND pod.req_distribution_id = prd.distribution_id
496 AND NVL(poll.cancel_flag, 'N') = 'N'
497 AND NVL(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
498 AND poll.shipment_type IN ('STANDARD', 'PLANNED', 'BLANKET', 'PREPAYMENT')
499 ;
500
501 CURSOR l_line_csr IS
502 -- SQL What: Querying backing requisiton line IDs of this line
503 -- SQL Why: Need to cancel or recreate these backing reqs
504 --<Complex Work R12>: rewrote this SQL state to use the tie between
505 --PO dist and Req dist instead of Req line to PO line loc.
506 SELECT DISTINCT(prd.requisition_line_id)
507 FROM po_req_distributions_all prd,
508 po_line_locations_all poll,
509 po_distributions_all pod
510 WHERE pod.po_line_id = p_doc_line_id
511 AND pod.line_location_id = poll.line_location_id
512 AND pod.req_distribution_id = prd.distribution_id
513 AND NVL(poll.cancel_flag, 'N') = 'N'
514 AND NVL(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
515 AND poll.shipment_type IN ('STANDARD', 'PLANNED', 'BLANKET', 'PREPAYMENT')
516 ;
517
518 CURSOR l_header_csr IS
519 -- SQL What: Querying backing requisiton line IDs of this header
520 -- SQL Why: Need to cancel or recreate these backing reqs
521 --<Complex Work R12>: rewrote this SQL state to use the tie between
522 --PO dist and Req dist instead of Req line to PO line loc.
523 SELECT DISTINCT(prd.requisition_line_id)
524 FROM po_req_distributions_all prd,
525 po_line_locations_all poll,
526 po_distributions_all pod
527 WHERE pod.po_header_id = p_doc_id
528 AND pod.line_location_id = poll.line_location_id
529 AND pod.req_distribution_id = prd.distribution_id
530 AND NVL(poll.cancel_flag, 'N') = 'N'
531 AND NVL(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED'
532 AND poll.shipment_type IN ('STANDARD', 'PLANNED', 'BLANKET', 'PREPAYMENT')
533 ;
534
535 CURSOR l_rel_header_csr IS
536 -- SQL What: Querying backing requisiton line IDs of this release
537 -- SQL Why: Need to cancel or recreate these backing reqs
538 -- SQL Join: line_location_id
539 SELECT porl.requisition_line_id
540 FROM po_requisition_lines_all porl, -- <Shared Proc FPJ>
541 po_line_locations poll
542 WHERE poll.po_release_id = p_doc_id AND
543 poll.line_location_id = porl.line_location_id AND
544 NVL(poll.cancel_flag, 'N') = 'N' AND
545 NVL(poll.closed_code, 'OPEN') <> 'FINALLY CLOSED' AND
546 poll.shipment_type IN ('STANDARD', 'PLANNED', 'BLANKET');
547
548 BEGIN
549 -- Start standard API initialization
550 IF FND_API.to_boolean(p_init_msg_list) THEN
551 FND_MSG_PUB.initialize;
552 END IF;
553 IF NOT FND_API.compatible_api_call(l_api_version, p_api_version,
554 l_api_name, g_pkg_name)
555 THEN
556 RAISE FND_API.g_exc_unexpected_error;
557 END IF;
558 x_return_status := FND_API.g_ret_sts_success;
559 -- End standard API initialization
560
561 IF (g_fnd_debug = 'Y') THEN
562 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_PROCEDURE) THEN
563 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE, g_module_prefix || l_api_name ||
564 '.invoked', 'ID: ' || NVL(TO_CHAR(p_doc_id),'null') ||
565 ', Control level: ' || NVL(TO_CHAR(p_control_level),'null'));
566 END IF;
567 END IF;
568
569 IF (p_control_level = PO_Document_Control_PVT.g_header_level) THEN
570 OPEN l_header_csr;
571 FETCH l_header_csr BULK COLLECT INTO x_req_line_id_tbl;
572 IF (l_header_csr%ROWCOUNT = 0) THEN
573 RAISE FND_API.g_exc_error;
574 END IF;
575 CLOSE l_header_csr;
576 ELSIF (p_control_level = PO_Document_Control_PVT.g_line_level) THEN
577 OPEN l_line_csr;
578 FETCH l_line_csr BULK COLLECT INTO x_req_line_id_tbl;
579 IF (l_line_csr%ROWCOUNT = 0) THEN
580 RAISE FND_API.g_exc_error;
581 END IF;
582 CLOSE l_line_csr;
583 ELSIF (p_control_level IN (PO_Document_Control_PVT.g_shipment_level,
584 PO_Document_Control_PVT.g_rel_shipment_level))
585 THEN
586 OPEN l_shipment_csr;
587 FETCH l_shipment_csr BULK COLLECT INTO x_req_line_id_tbl;
588 IF (l_shipment_csr%ROWCOUNT = 0) THEN
589 RAISE FND_API.g_exc_error;
590 END IF;
591 CLOSE l_shipment_csr;
592 ELSIF (p_control_level = PO_Document_Control_PVT.g_rel_header_level) THEN
593 OPEN l_rel_header_csr;
594 FETCH l_rel_header_csr BULK COLLECT INTO x_req_line_id_tbl;
595 IF (l_rel_header_csr%ROWCOUNT = 0) THEN
596 RAISE FND_API.g_exc_error;
597 END IF;
598 CLOSE l_rel_header_csr;
599 ELSE
600 RAISE FND_API.g_exc_error;
601 END IF;
602
603 EXCEPTION
604 WHEN FND_API.g_exc_error THEN
605 x_return_status := FND_API.g_ret_sts_error;
606 IF (l_header_csr%ISOPEN) THEN
607 CLOSE l_header_csr;
608 ELSIF (l_line_csr%ISOPEN) THEN
609 CLOSE l_line_csr;
610 ELSIF (l_shipment_csr%ISOPEN) THEN
611 CLOSE l_shipment_csr;
612 ELSIF (l_rel_header_csr%ISOPEN) THEN
613 CLOSE l_rel_header_csr;
614 END IF; --<if l_header_csr ...>
615 WHEN FND_API.g_exc_unexpected_error THEN
616 x_return_status := FND_API.g_ret_sts_unexp_error;
617 IF (l_header_csr%ISOPEN) THEN
618 CLOSE l_header_csr;
619 ELSIF (l_line_csr%ISOPEN) THEN
620 CLOSE l_line_csr;
621 ELSIF (l_shipment_csr%ISOPEN) THEN
622 CLOSE l_shipment_csr;
623 ELSIF (l_rel_header_csr%ISOPEN) THEN
624 CLOSE l_rel_header_csr;
625 END IF; --<if l_header_csr ...>
626 WHEN OTHERS THEN
627 x_return_status := FND_API.g_ret_sts_unexp_error;
628 IF (l_header_csr%ISOPEN) THEN
629 CLOSE l_header_csr;
630 ELSIF (l_line_csr%ISOPEN) THEN
631 CLOSE l_line_csr;
632 ELSIF (l_shipment_csr%ISOPEN) THEN
633 CLOSE l_shipment_csr;
634 ELSIF (l_rel_header_csr%ISOPEN) THEN
635 CLOSE l_rel_header_csr;
636 END IF; --<if l_header_csr ...>
637 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error) THEN
638 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
639 IF (g_fnd_debug = 'Y') THEN
640 IF (FND_LOG.G_CURRENT_RUNTIME_LEVEL <= FND_LOG.LEVEL_UNEXPECTED) THEN
641 FND_LOG.string(FND_LOG.level_unexpected, g_module_prefix ||
642 l_api_name || '.others_exception', 'Exception');
643 END IF;
644 END IF;
645 END IF;
646 END fetch_req_lines;
647
648 -- <Shared Proc FPJ START>
649
650 ---------------------------------------------------------------------------
651 --Start of Comments
652 --Name: check_cancel_reqs_flag
653 --Pre-reqs:
654 -- None.
655 --Modifies:
656 -- FND_MSG_PUB
657 --Locks:
658 -- None.
659 --Function:
660 -- Compares x_cancel_reqs_flag to the current OU's purchasing options.
661 -- If the current OU option is Always cancel, then x_cancel_reqs_flag is set
662 -- to 'Y'.
663 -- If the current OU option is Never cancel, then x_cancel_reqs_flag is set
664 -- to 'N'.
665 -- Otherwise, x_cancel_reqs_flag is not modified.
666 -- A warning message is appended to the API message list if the caller passed
667 -- in a value for x_cancel_reqs_flag, and this was overwritten because it
668 -- conflicted with the current OU's purchasing options.
669 --Parameters:
670 --IN:
671 --IN OUT:
672 --x_cancel_reqs_flag
673 -- A 'Y' or 'N' indicating that cancelling backing reqs when PO's are
674 -- cancelled is desired.
675 --OUT:
676 --x_return_status
677 -- FND_API.G_RET_STS_SUCCESS if no error has occurred, otherwise an error
678 -- has occurred within the procedure
679 --Testing:
680 --End of Comments
681 ---------------------------------------------------------------------------
682 PROCEDURE check_cancel_reqs_flag
683 (
684 x_return_status OUT NOCOPY VARCHAR2,
685 x_cancel_reqs_flag IN OUT NOCOPY VARCHAR2
686 ) IS
687
688 l_progress VARCHAR2(3);
689 l_api_name VARCHAR2(50) := 'check_cancel_reqs_flag';
690
691 l_cancel_reqs_sys_val
692 PO_SYSTEM_PARAMETERS_ALL.cancel_reqs_on_po_cancel_flag%TYPE;
693
694 BEGIN
695 l_progress := '000';
696 x_return_status := FND_API.G_RET_STS_SUCCESS;
697
698 l_progress := '010';
699
700 SELECT cancel_reqs_on_po_cancel_flag
701 INTO l_cancel_reqs_sys_val
702 FROM po_system_parameters;
703
704 IF (l_cancel_reqs_sys_val = 'A' AND
705 NVL(x_cancel_reqs_flag, 'X') <> 'Y') THEN
706
707 l_progress := '020';
708
709 IF (x_cancel_reqs_flag IS NOT NULL) THEN
710 -- Bug 3229120: Only add warning message if user passed in a value
711 -- for the flag
712 FND_MESSAGE.set_name
713 ( application => 'PO',
714 name => 'PO_INVALID_CANCEL_REQS_FLAG'
715 );
716 FND_MSG_PUB.add;
717 END IF;
718
719 x_cancel_reqs_flag := 'Y';
720
721 ELSIF (l_cancel_reqs_sys_val = 'N' AND
722 NVL(x_cancel_reqs_flag, 'X') <> 'N') THEN
723
724 l_progress := '030';
725
726 IF (x_cancel_reqs_flag IS NOT NULL) THEN
727 -- Bug 3229120: Only add warning message if user passed in a value
728 -- for the flag
729 FND_MESSAGE.set_name
730 ( application => 'PO',
731 name => 'PO_INVALID_CANCEL_REQS_FLAG'
732 );
733 FND_MSG_PUB.add;
734 END IF;
735
736 x_cancel_reqs_flag := 'N';
737
738 END IF;
739
740 l_progress := '040';
741
742 EXCEPTION
743 WHEN NO_DATA_FOUND THEN
744 NULL;
745
746 WHEN OTHERS THEN
747 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
748
749 FND_MSG_PUB.add_exc_msg
750 ( p_pkg_name => g_pkg_name,
751 p_procedure_name => l_api_name);
752
753 IF (g_debug_unexp) THEN
754
755 PO_DEBUG.debug_exc
756 ( p_log_head => g_module_prefix || l_api_name,
757 p_progress => l_progress
758 );
759
760 END IF;
761
762 END check_cancel_reqs_flag;
763
764 -- <Shared Proc FPJ END>
765
766 END PO_Document_Cancel_PVT;
[Home] [Help]